capture program drop surveyspan

program surveyspan, rclass

    version 13

	* surveyspan needs to be an R class command because we are generating a new variable. 

    syntax varlist(min=2 max=2) [if] [, GENerate(string) MAXtime(real 0) MINtime(real 0) Cutoff(real 17) GROUPvar(varname) OUTfile(string)]
    
    quietly {
		
		********************************************************************************
		* Section 1: Standard set-up  
		********************************************************************************
		
        * Store if/in condition
		
        marksample touse, strok
        
		* Get the variable names for start and end time 
		
		local start_time: 	word 1 of `varlist'
		local end_time: 	word 2 of `varlist'
		
		* Check if start and end variables exist and are strings (in the right format)
		
		capture confirm variable `start_time'
		if _rc {
			di as error "	Error: Variable `start_time' not found in dataset"
			exit 111
		}

		capture confirm variable `end_time'
		if _rc {
			di as error "	Error: Variable `end_time' not found in dataset"
			exit 111
		}
		
		capture confirm string variable `start_time'
		if _rc {
			di as error "	Error: Variable `start_time' must be a string variable"
			exit 109
		}

		capture confirm string variable `end_time'
		if _rc {
			di as error "	Error: Variable `end_time' must be a string variable"
			exit 109
		}
		

		foreach var in `start_time' `end_time' {   
			
		count if !regexm(`var', "^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]?T[0-9][0-9]:[0-9][0-9]:[0-9][0-9]") & `touse'
		local invalid = r(N)  
    
		if `invalid' > 0 {  
			di as error "    Error: `var' contains `invalid' values not in YYYY-MM-DDThh:mm:ss format"
			exit 198
		}
		}

		* There needs to be at least one observation for this module to work 

		count if `touse'
		if r(N) == 0 {
			di as error "	No observations meet the current criteria"
			exit 2000
		}
			
	
		* Validate cutoff parameter
		
		if `cutoff' < 0 | `cutoff' > 23 {
			di as error "	Error: cutoff() must be between 0 and 23 (representing hours in a 24-hour format)"
			exit 198
		}
		
		* Min and max times cannot be negative
		
		if `mintime' < 0 {
			di as error "	Error: mintime() cannot be negative"
			exit 198
		}

		if `maxtime' < 0 {
			di as error "	Error: maxtime() cannot be negative"
			exit 198
		}
		
		if `maxtime' > 0 & `mintime' > 0 & `mintime' >= `maxtime' {
			di as error "	Error: mintime(`mintime') must be less than maxtime(`maxtime')"
			exit 198
		}		
						
        * Get the name for the output variable, default to "survey_span" if not provided
		
        local duration_var 		"survey_span"
			
		if "`generate'" != "" {
			
		capture confirm variable `generate'
		if !_rc {
			di as error "	Variable `generate' already exists in the dataset"
			exit 110
		}
		
		local duration_var "`generate'"
		}
		
		if "`groupvar'" != "" {
			
		capture confirm variable `groupvar'
		if _rc {
			di as error "	Error: Grouping variable `groupvar' does not exist"
			exit 110
		}
			
		* Create default output filename if none is specified
		
		if "`outfile'" == "" {
			local datetime = 	subinstr("`c(current_date)'"," ","",.)  
			local shortdate = 	substr("`datetime'", 1, 5) + substr("`datetime'", -2, .) 
			local time = 		subinstr("`c(current_time)'", ":", "-", .)
			local outfile "surveyspan_`shortdate'_`time'.xlsx"
		}
    
		* Add .xlsx extension if not present
		
		if !regexm("`outfile'", "\.xlsx$") {
			local outfile "`outfile'.xlsx"
		}
}
		
		********************************************************************************
		* Section 2: Correctly calculate the duration   
		********************************************************************************
        
        * Create temporary variables for intermediate calculations
		
        tempvar start_adj end_adj time_begins time_ends time_begins_dt after_cutoff
            
        * Convert timestamps to proper format
		
		gen `start_adj' = 	`start_time'
		gen `end_adj' = 	`end_time'

		replace `start_adj' = "" 		if `touse' != 1
		replace `end_adj' = "" 			if `touse' != 1
        
        * Fix single-digit days (convert 9T to 09T) - this is an identified bug with KoboToolbox
		
		replace `start_adj' = 	cond(substr(`start_adj',10,1) == "T", 	substr(`start_adj',1,8) + "0" + substr(`start_adj',9,.),`start_adj') 	if `touse'
		replace `end_adj' = 	cond(substr(`end_adj',10,1) == "T", 	substr(`end_adj',1,8) + "0" + 	substr(`end_adj',9,.),`end_adj') 		if `touse'
		     
        * Generate time variables
		
        gen `time_begins' = 	substr(`start_adj',1,10) + 	" " + substr(`start_adj',12,8) 	if `touse'
        gen `time_ends' = 		substr(`end_adj',1,10) + 	" " + substr(`end_adj',12,8) 	if `touse'
		     
        * Generate datetime format for start time and after cutoff indicator
		
        gen double `time_begins_dt' = clock(`time_begins', "YMDhms") if `touse'
        format `time_begins_dt' %tc
		
        gen `after_cutoff' = hh(`time_begins_dt') >= `cutoff' & !missing(`time_begins_dt') if `touse'
        
        * Calculate duration
		
        gen `duration_var' = . if `touse'
        replace `duration_var' = (clock(`time_ends',"YMDhms") - clock(`time_begins',"YMDhms"))/60000 ///
            if (!missing(`start_adj') & !missing(`end_adj') & `touse')
			
		label variable `duration_var' "`end_time' - `start_time' (in minutes)"
		
        * Count and report negative duration values
		
        local neg_count = 0
        count if `duration_var' < 0 & `touse'
        local neg_count = r(N)
        
        * Replace negative durations to missing
		
        if `neg_count' > 0 {
            replace `duration_var' = . if `duration_var' < 0 & `touse'
        }
        
        * Check and remove surveys longer than maximum time
		
        local above_max = 0
        if `maxtime' > 0 {
            count if `duration_var' > `maxtime' & !missing(`duration_var') & `touse'
            local above_max = r(N)
            replace `duration_var' = . if `duration_var' > `maxtime' & `touse'
        }
        
        * Check for surveys shorter than minimum time (but don't remove them)
		
        local below_min = 0
        if `mintime' > 0 {
            count if `duration_var' < `mintime' & !missing(`duration_var') & `touse'
            local below_min = r(N)
        }
        
        * Calculate statistics based on the cleaned data
		
		count if `touse'
		local total_obs = r(N)
		
		sum `duration_var' if `touse', detail
		local obs = r(N)
		local var_mean = r(mean)
		local var_median = r(p50)
		local var_sd = r(sd) 

		* Calculate observations within 1 SD
		count if (`duration_var' >= `var_mean' - `var_sd') & ///
			   (`duration_var' <= `var_mean' + `var_sd') & ///
			   !missing(`duration_var') & `touse'
			   
		local within_sd_count = r(N)
		local within_sd_pct = round(r(N) / `obs' * 100, 0.1)
		local within_sd_pct_display = string(round(r(N) / `obs' * 100, 0.01), "%5.2f")
		
        * Calculate after cutoff statistics (but only when the duration variable is also included)
		
		count if `after_cutoff' == 1 & !missing(`duration_var') & `touse'
        local after_cutoff_count = r(N)
        local after_cutoff_pct = round(`after_cutoff_count' / `obs' * 100, 0.1)
		local after_cutoff_pct_display = string(round(`after_cutoff_count' / `obs' * 100, 0.01), "%5.2f")
		
	
 		********************************************************************************
		* Section 3: Excel output 
		********************************************************************************
		
		* If groupvar option is specified, create Excel output
		
		if "`groupvar'" != "" {
		preserve
	   
		* First, create total observations indicator before any filtering
		
		gen total_obs = 1 if `touse'
		
		* Create binary indicators
		
		gen late_upload = `after_cutoff' == 1 if !missing(`duration_var') 
		
		* Create below threshold indicator
		
		gen below_threshold = 0 if !missing(`duration_var')
		
		if `mintime' > 0 {
			replace below_threshold = 1 if `duration_var' < `mintime' & !missing(`duration_var')
			local threshold_label "less than `mintime' minutes"
		}
		else {
			sum `duration_var' if `touse', detail
			local threshold = r(mean) - (2 * r(sd))
			replace below_threshold = 1 if `duration_var' < `threshold' & !missing(`duration_var')
			local threshold_label "more than 2 standard deviations below mean"
		}
	   
		* Now collapse with all indicators properly set up 
		
		collapse 	(sum) 	total_obs ///
					(mean) 	mean_duration=`duration_var' ///
					(p50) 	median_duration=`duration_var' ///
					(sd) 	sd_duration=`duration_var' ///
					(sum) 	late_uploads=late_upload ///
					(sum) 	flagged_short=below_threshold ///
					(count) obs=`duration_var' ///
				if `touse', by(`groupvar')
		
		* Calculate excluded as difference between total and valid observations      
		
		gen excluded = total_obs - obs
		
		* Generate percentage columns using total_obs as denominator
		
		gen pct_excluded = 	round(excluded/total_obs * 100, 0.01)
		
		* Generate percentage columns using obs as denominator
		
		gen pct_late = 		round(late_uploads/obs * 100, 0.01)
		gen pct_flagged = 	round(flagged_short/obs * 100, 0.01)

			
		* Add variable labels
		
		label variable total_obs 				"Total Observations"
		label variable obs 						"Observations Used for Analysis"
		label variable excluded 				"Excluded Observations"
		label variable pct_excluded 			"Percent of Observations Excluded"
		label variable mean_duration 			"Mean Duration (minutes)"
		label variable median_duration 			"Median Duration (minutes)"
		label variable sd_duration 				"Standard Deviation of Duration"
		label variable late_uploads 			"Number of Surveys After `cutoff':00 hours"
		label variable pct_late 				"Percent of Total Surveys After `cutoff':00 hours"
		label variable flagged_short 			"Number of Surveys `threshold_label'"
		label variable pct_flagged 				"Percent of Total Surveys `threshold_label'"
		label variable `groupvar' 				"Grouping variable"
		
	   * Format statistics
	   
	   format mean_duration median_duration sd_duration pct_excluded pct_flagged pct_late %9.2f
	   
	   order `groupvar' total_obs obs excluded pct_excluded ///
	   mean_duration median_duration sd_duration /// 
	   late_uploads pct_late flagged_short pct_flagged
	   
	   * Export to Excel with variable labels
	   
	   export excel using "`outfile'", sheet("Summary Stats") firstrow(varlabels) replace
		
	   * Set OS-appropriate path separator
	   
	   local sep = cond("`c(os)'" == "Windows", "\", "/")

	   restore
  
     }  
	 
} 	 
        * Store results in the return list

		return scalar total_obs = 			`total_obs'
		return scalar obs = 				`obs'
        return scalar mean = 				`var_mean'
        return scalar median = 				`var_median'
		return scalar sd = 					`var_sd' 
		return scalar within_sd_pct = 		`within_sd_pct' 
        return scalar aft_cutoff_count = 	`after_cutoff_count'
        return scalar aft_cutoff_pct = 		`after_cutoff_pct'	

	********************************************************************************
	* Section 4: Display text
	********************************************************************************
	
    * Display text - outside the quietly block
	
	display as result 	"	"
	display as result 	"	Total observations:               " %9.2f `total_obs'
	display as result 	"	Observations with valid durations:" %9.2f `obs'
    display as result	"	Mean:                             " %9.2f `var_mean' 
    display as result	"	Median:                           " %9.2f `var_median'
	display as result 	"	Standard deviation:               " %9.2f `var_sd'
	display as result 	"	`within_sd_count' (`within_sd_pct_display'%) of observations were within one standard deviation of the mean"
	display as result 	"	`after_cutoff_count' (`after_cutoff_pct_display'%) of observations started after `cutoff':00 hours"
	
    if `neg_count' > 0 {
	display as result 	"	"	
    display as result	"	Number of observations with negative duration: `neg_count'."
	display as result	"	These negative observations have not been used for the purposes of the calculations."
    }
    
    if `mintime' > 0 {
	display as result 	"	"	
    display as result 	"	Number of surveys completed in less than `mintime' minutes: `below_min'"
	display as result 	"	Percentage of surveys completed in less than `mintime' minutes: " %5.2f (`below_min'/`obs')*100
    }
    
    if `maxtime' > 0 {
	display as result 	"	"		
    display as result 	"	Surveys longer than `maxtime' minutes: `above_max'"
	
		if `above_max' > 0 {
		display as result	"	These observations above the maximum time duration have not been used for the purposes of the calculations."
    }

    }
	
    if "`groupvar'" != "" {

	display as result 	""
	display as result 	`"	Excel report has been generated: {browse "`c(pwd)'`sep'`outfile'"}"'
    }
    
    quietly {
        if "`generate'" == "" {
            drop `duration_var'
        }
    }
end