/*
This script requires the following package: findit latab,
and a spreadsheet exported from Studentportalen that contains the raw data.
To compile the result in Latex you need a tex file that uses content.tex as input.
A sample file is included in the zip package.
*/

* Change these three macros
local folder "D:\Dropbox\Kursvärdering\mappens namn"	/* Working folder */
local file "Excelfilens namn.xls"						/* The name of the exported spreadsheet (located above) */
local questions 29										/* The number of questions to import from the survey */
local cols C D E F G H I J K L							/* A vector of columns that include anwers */

* Step 1
set more off
cd "`folder'"
local range `=`questions'+4'
local macrolen: length local cols
local lastcol substr("`cols'",`macrolen',1) 
import excel "`file'", sheet("Kursvärdering") cellrange(A5:`=`lastcol''`range') clear

* Clean up line breaks
replace B = subinstr(B, char(10), "", .) 

	* Skriver en do-fil som sätter labels på alla variabelnamn och värden
	file open labels using labels.txt, write replace
	local obs = _N
	forvalues i = 1/`obs' {
 	  local label = B[`i']
	  local label`i' = B[`i']
	  file write labels `"char f`i'[one] "`label'" "' _n
	  file write labels `"lab var f`i' "`label'" "' _n
		foreach j in `cols'  {
		if missing(`j'[`i']) == 0 {
		local value substr(`j'[`i'],1,1)		
		local valuelabel substr(`j'[`i'],4,.)
		local text `value' + `" ""' + `valuelabel' + `"", add"'
		file write labels `"lab def l`i' `=`text'' "' _n
		}
		}
	* Assign the value labels to the variables (capture ignores error message for string variables, which cannot be labeled)
	file write labels `"capture lab values f`i' l`i' "' _n
	}
	file close labels
	filefilter labels.txt labels.do, from("") to("") replace

clear all
import excel "`file'", sheet("Resultat") firstrow clear
rename Frga* f*
rename f*kommentar k*
*drop if f32 == 0 | f32 > 2
forvalues i = 1/`questions' {
capture destring f`i', replace
}

do labels
erase labels.txt
erase labels.do


capture mkdir tex
local obs = _N
file open content using content.tex, write replace
forvalues i = 1/`questions' {
file write content "\section{"
file write content "`:char f`i'[one]'" 
file write content "}" _n

	* Skriv tabuleringen till en texfil

		* Ta ut medelvärde, median och redigera texfilen. Olika åtgärder beroende på om det är en string eller inte.
		capture confirm string variable f`i'
		if !_rc {
		* action for string variables
		}
		else {
		* action for numeric variables
			su f`i' if f`i' != 0, detail
			local mean = round(r(mean),.01)
			local mean : di %3.2f `mean'
			local median = r(p50)
			latab f`i', tf(tex/t`i') replace
			capture filefilter tex/t`i'.tex temp.tex, from("Item& Number & Per cent") to("& Antal & Procent") replace
			capture filefilter temp.tex tex/t`i'.tex, from("{\BSfootnotesize{\BSemph{Source:} }}") to("{\BSfootnotesize{\BSemph{Medelvärde: `mean'. Median: `median'.} }}") replace
			capture filefilter tex/t`i'.tex temp.tex, from("\BScaption") to("%\BScaption") replace
			capture filefilter temp.tex tex/t`i'.tex, from("\BStextbf") to("%\BStextbf") replace
			capture filefilter tex/t`i'.tex temp.tex, from("\BSbegin{table}[htbp]") to("\BSbegin{table}[H]") replace
			capture filefilter temp.tex tex/t`i'.tex, from("") to("") replace


		file write content "\input{tex/t`i'.tex}" _n 
		}


		* Döp stringvariabler som är frågor till k i stället för f
			capture confirm string variable f`i'
			if !_rc {
			rename f`i' k`i'
			}

	* Skriv kommentarer till en textfil

		file open k`i' using tex/k`i'.tex, write replace
		local n 0
		forvalues j = 1/`obs' {
		
		* If the variable exists, and its value is non-missing, write the comment to the tex file.

		capture confirm variable k`i'
			if !_rc {
			if missing(k`i'[`j']) == 0 {
				local `n++'
				local text k`i'[`j'] 
				capture file write k`i' `"\item `=`text'' "' _n
			}
			}
		}
		
	
		if `n' > 0 {
		capture file write content "\begin{itemize}\input{tex/k`i'.tex}\end{itemize}" _n 
		}

		file close k`i'	
		capture filefilter tex/k`i'.tex temp.tex, from(""") to("''") replace		
		capture filefilter temp.tex tex/k`i'.tex, from("&") to("&") replace	
		capture filefilter tex/k`i'.tex temp.tex, from("amp;") to("") replace		
		capture filefilter temp.tex tex/k`i'.tex, from("&") to("\BS&") replace	


}
file close content
capture erase temp.tex