<!--#include virtual="/_appDB/db_connection.asp" -->
<!--#include virtual="/_appDB/functions.asp" -->
<!--#include virtual="/schools/scripts/admin_functions.asp" -->
<!--#include virtual="/schools/scripts/enc_function.asp" -->
<!--#include virtual="/schools/scripts/adm_inst.asp" -->
<%
IF (checkPermission("reports")="FAILED") THEN
	response.write("You do not have permission to view this page")
	response.end
END IF
%>
<%
On Error Resume Next
Function Qpad(tval)
	IF len(tval)=1 THEN
		Qpad="0"&tval
	ELSE
		Qpad=tval
	END IF
End Function
Function fetchTouchCount(sID,rID,tdate,sT)
	IF sID <> "" AND IsNumeric(sID) THEN
		SET rsRC=dbCBK.Execute("SELECT COUNT(DISTINCT(pupilID)) As tdcnt from cbk_touchdata WHERE schoolID='"&sID&"' AND routeID='"&rID&"' AND touchindatetime LIKE '"&tdate&"%' AND routeSTARTTIME='"&sT&"' limit 1")
		IF rsRC.EOF THEN
			tc="0"
		END IF
		DO WHILE NOT rsRC.EOF
			tc=CStr(rsRC("tdcnt"))
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		fetchTouchCount=tc
	ELSE
		'missing data
		fetchTouchCount="0"
	END IF
End Function

Function dataWrite(fld)
	Set laloregEx = New RegExp 
    laloregEx.Global = true 
    laloregEx.Pattern = "[^0-9a-zA-Z\ \/\:\.\-]"
	IF IsNull(fld) OR IsEmpty(fld) THEN
		dataWrite="""--"""
	ELSE
		dataWrite=""""&laloregEx.Replace(fld, "")&""""
	END IF
End Function
Function capComments(txt)
	IF txt <> "" THEN
		capComments=replace(txt,",","")
	ELSE
		capComments=txt
	END IF
End Function
IF REQUEST("GO")="create" THEN
			sub Write_CSV_From_Recordset(RS)
			
			    '
			    ' Export Recordset to CSV
			    ' https://salman-w.blogspot.com/2009/07/export-recordset-data-to-csv-using.html
			    '
			    ' This sub-routine Response.Writes the content of an ADODB.RECORDSET in CSV format
			    ' The function closely follows the recommendations described in RFC 4180:
			    ' Common Format and MIME Type for Comma-Separated Values (CSV) Files
			    ' https://tools.ietf.org/html/rfc4180
			    '
			    ' @RS: A reference to an open ADODB.RECORDSET object
			    '
			
			    if RS.EOF then
			    
			        '
			        ' There is no data to be written
			        '
			        exit sub
			    
			    end if
			
			    dim RX
			    set RX = new RegExp
			        RX.Pattern = "\r|\n|,|"""
			
			    dim i
			    dim Field
			    dim Separator
			
			    '
			    ' Writing the header row (header row contains field names)
			    '
			
			    Separator = ""
			    for i = 0 to RS.Fields.Count - 1
			        Field = RS.Fields(i).Name
			        if RX.Test(Field) then
			            '
			            ' According to recommendations:
			            ' - Fields that contain CR/LF, Comma or Double-quote should be enclosed in double-quotes
			            ' - Double-quote itself must be escaped by preceeding with another double-quote
			            '
			            Field = """" & Replace(Field, """", """""") & """"
			        end if
			        Response.Write Separator & Field
			        Separator = ","
			    next
			    Response.Write vbNewLine
			
			    '
			    ' Writing the data rows
			    '
			
			    do until RS.EOF
			        Separator = ""
			        for i = 0 to RS.Fields.Count - 1
			            '
			            ' Note the concatenation with empty string below
			            ' This assures that NULL values are converted to empty string
			            '
			            Field = RS.Fields(i).Value & ""
			            if RX.Test(Field) then
			                Field = """" & Replace(Field, """", """""") & """"
			            end if
			            Response.Write Separator & Field
			            Separator = ","
			        next
			        Response.Write vbNewLine
			        RS.MoveNext
			    loop
				RS.Close
				SET RS = NOTHING
			end sub
		

StrError="None"
f=sanitizeRequest("f","querystring")
	IF (IsDate(f)) THEN
	'ok	
		f=formatDateTime(CDate(f),2)
		'deduct a day for search
		f=dateAdd("d",-1,formatDateTime(f,2))
		'format for mysql
		fdate=year(f)&"-"&Qpad(month(f))&"-"&Qpad(day(f))
	ELSE
		StrError="Start Date Appears Invalid >" & f
	END IF	
t=sanitizeRequest("t","querystring")
	IF (IsDate(t)) THEN
		'ok
		t=formatDateTime(CDate(t),2)
		'add a day for search
		t=dateAdd("d",1,formatDateTime(t,2))
		'format for mysql
		tdate=year(t)&"-"&Qpad(month(t))&"-"&Qpad(day(t))	
	ELSE
		StrError="End Date Appears Invalid >" & t
	END IF
IF dateDiff("d",f,t) < 1 THEN
	StrError="End date must be after start date.."
END IF	
roleID=sanitizeRequest("roleID","querystring")
	IF roleID <> "" THEN
		IF IsNumeric(roleID) THEN
			'ok
		ELSE
			StrError="Role ID Appears Invalid"
		END IF
	END IF
routeID=sanitizeRequest("routeID","querystring")
	IF routeID <> "" THEN
		IF IsNumeric(routeID) THEN
			'ok
		ELSEIF routeID="all" THEN
		'ok
		ELSE
			StrError="route ID Appears Invalid"
		END IF
	END IF

'do we want to include covid data

IF REQUEST("icd")="yes" THEN
	icd="yes"
ELSE
	icd="no"
END IF

	IF StrError <> "None" THEN
		'an error occurred
		response.write(StrError)
	ELSE
				rdSQL="select cbk_touchdata.journeyID As journeyID, cbk_touchdata.schoolID As schoolID, cbk_routes.routename As routename, cbk_touchdata.routeID As routeID, cbk_touchdata.pupilID As pupilID, cbk_touchdata.roleID As roleID, cbk_touchdata.pupilname As pupilname, cbk_touchdata.routeSTARTTIME As rst, cbk_touchdata.touchin As touchin, cbk_touchdata.touchindatetime As touchindatetime, cbk_touchdata.touchinlat As touchinlat, cbk_touchdata.touchinlong As touchinlong, cbk_touchdata.touchin_wp_id As touchin_wp_id, cbk_touchdata.touchin_wp_label As touchin_wp_label, cbk_touchdata.touchin_wp_ref As touchin_wp_ref, cbk_touchdata.touchin_wp_addr As touchin_wp_addr, cbk_touchdata.touchout As touchout, cbk_touchdata.touchoutdatetime As touchoutdatetime, cbk_touchdata.touchoutlat As touchoutlat, cbk_touchdata.touchoutlong As touchoutlong, cbk_touchdata.touchout_wp_id As touchout_wp_id, cbk_touchdata.touchout_wp_label As touchout_wp_label, cbk_touchdata.touchout_wp_ref As touchout_wp_ref, cbk_touchdata.touchout_wp_addr As touchout_wp_addr, cbk_touchdata.healthtestT As healthtestT, cbk_touchdata.healthtestDATE As healthtestDATE, cbk_touchdata.healthtestRESULT As healthtestRESULT, cbk_touchdata.healthtestBARCODE As healthtestBARCODE, cbk_touchdata.healthtestNEXTDATEDUE As healthtestNEXTDATEDUE FROM cbk_touchdata LEFT JOIN cbk_routes ON cbk_touchdata.routeID = cbk_routes.routeID WHERE"
				rdSQL=rdSQL&" cbk_touchdata.touchindatetime > '"&fdate&"' AND cbk_touchdata.touchindatetime < '"&tdate&"'"
				IF routeID <> "" AND routeID <> "all" THEN
					rdSQL=rdSQL&" AND cbk_touchdata.routeID='"&routeID&"'"
				END IF
				IF roleID <> "" THEN
					rdSQL=rdSQL&" AND cbk_touchdata.roleID='"&roleID&"'"
				END IF
				rdSQL=rdSQL&" AND cbk_touchdata.schoolID='"&str_u_id&"'"
				rdSQL=rdSQL&" order by routeID asc, touchindatetime asc limit 5000"
	
				dim RS1
				set RS1 = dbCBK.Execute(rdSQL)
				
				Response.ContentType = "text/csv"
				str_filename=day(now()) & month(now()) & year(now()) & hour(now()) & minute(now()) & second(now()) & ".csv"
				Response.AddHeader "Content-Disposition", "attachment;filename="&str_filename
				'Write_CSV_From_Recordset RS1

				Separator = ""
			    for i = 0 to RS1.Fields.Count - 1
			        Field = RS1.Fields(i).Name
			        if RX.Test(Field) then
			            '
			            ' According to recommendations:
			            ' - Fields that contain CR/LF, Comma or Double-quote should be enclosed in double-quotes
			            ' - Double-quote itself must be escaped by preceeding with another double-quote
			            '
			            Field = """" & Replace(Field, """", """""") & """"
			        end if
			        Response.Write Separator & Field
			        Separator = ","
			    next
			    Response.Write vbNewLine


				prevDate=""
				prevTime=""
				prevRouteID=""

				do while not RS1.EOF
					

					journeyID=RS1("journeyID")
					schoolID=RS1("schoolID")
					routename=RS1("routename")
					routeID=RS1("routeID")
					pupilID=RS1("pupilID")
					roleID=RS1("roleID")
					pupilname=RS1("pupilname")
					rst=RS1("rst")
					touchin=RS1("touchin")
					touchindatetime=RS1("touchindatetime")
					touchinlat=RS1("touchinlat")
					touchinlong=RS1("touchinlong")
					touchin_wp_id=RS1("touchin_wp_id")
					touchin_wp_label=RS1("touchin_wp_label")
					touchin_wp_ref=RS1("touchin_wp_ref")
					touchin_wp_addr=RS1("touchin_wp_addr")
					touchout=RS1("touchout")
					touchoutdatetime=RS1("touchoutdatetime")
					touchoutlat=RS1("touchoutlat")
					touchoutlong=RS1("touchoutlong")
					touchout_wp_id=RS1("touchout_wp_id")
					touchout_wp_label=RS1("touchout_wp_label")
					touchout_wp_ref=RS1("touchout_wp_ref")
					touchout_wp_addr=RS1("touchout_wp_addr")
					td_healthtestT=RS1("healthtestT")
					td_healthtestDATE=RS1("healthtestDATE")
					td_healthtestRESULT=RS1("healthtestRESULT")
					td_healthtestBARCODE=RS1("healthtestBARCODE")
					td_healthtestNEXTDATEDUE=RS1("healthtestNEXTDATEDUE")
					curDate=formatDateTime(touchindatetime,2)
					curTime=rst
					tdate=year(curDate)&"-"&QPad(month(curDate))&"-"&QPad(day(curDate))
				    td_count=fetchTouchCount(schoolID,routeID,tdate,curTime)



					IF (prevDate <> curDate) THEN
						'first result or new date detected, check for a corresponding comments once.
						 fmtDTformMysql=year(curDate)&"-"&QPad(month(curDate))&"-"&QPad(day(curDate))
						 pdSQL="SELECT driverID, drivername, route_comments, healthtestT, healthtestRESULT, healthtestDATE FROM cbk_driver_log WHERE schoolID='"&str_u_id&"' AND routeID='"&routeID&"' AND entrydatetime LIKE '"&fmtDTformMysql&"%' AND routeSTARTTIME='"&rst&"' order by entrydatetime desc limit 1"
						 'response.write(pdSQL)
						 SET rsDC=dbCBK.Execute(pdSQL)
						 IF rsDC.EOF THEN
								str_comments="None"
								response.write("Summary for Route: "&routename&" started @ "&rst&" on "&dataWrite(formatDateTime(touchindatetime,vbshortdate))&" / Pupils Who Touched In: "&td_count&" / Driver comments: "&capComments(str_comments))
								response.write(",,,,,,,,,,,,,,,,,,,,,,,,,,")
								Response.Write vbNewLine
						 END IF
						 DO WHILE NOT rsDC.EOF
								x_did=rsDC("driverID")
								x_nme=rsDC("drivername")
								str_comments=rsDC("route_comments")
								response.write("Summary for Route: "&routename&" started @ "&rst&" on "&dataWrite(formatDateTime(touchindatetime,vbshortdate))&" / Pupils Who Touched In: "&td_count&" / Driver Name: "&x_nme&" [ID#"&x_did&"]<br />Driver comments: "&capComments(str_comments))
								response.write(",,,,,,,,,,,,,,,,,,,,,,,,,,")
								Response.Write vbNewLine
								IF icd="yes" THEN
									IF rsDC("healthtestT") <> "" THEN 
										response.write("Driver tested: ")
										IF rsDC("healthtestRESULT") <> "" THEN
											IF rsDC("healthtestRESULT")="positive" THEN response.write("positive") ELSE response.write(rsDC("healthtestRESULT")) END IF
										ELSE
											response.write(" ?")
										END IF
										response.write(" for "&rsDC("healthtestT")&" on ")
										IF rsDC("healthtestDATE") <> "" AND IsDate(rsDC("healthtestDATE")) THEN response.write(formatDateTime(rsDC("healthtestDATE"),2)) END IF
										response.write(",,,,,,,,,,,,,,,,,,,,,,,,,,")
										Response.Write vbNewLine
									END IF
								END IF
						 rsDC.MoveNext
						 loop
						 rsDC.Close
						 SET rsDC=Nothing
				ELSE
					'more than one result for day so check time has changed
					IF (prevTime <> curTime) THEN
						fmtDTformMysql=year(curDate)&"-"&QPad(month(curDate))&"-"&QPad(day(curDate))
						 fmtDTformMysqlCHK=fmtDTformMysql&" "&curTime&":00"
						 SET rsDC=dbCBK.Execute("SELECT driverID, drivername, route_comments, healthtestT, healthtestRESULT, healthtestDATE FROM cbk_driver_log WHERE schoolID='"&str_u_id&"' AND routeID='"&routeID&"' AND entrydatetime LIKE '"&fmtDTformMysql&"%' AND routeSTARTTIME='"&rst&"' order by entrydatetime desc limit 1")
						 IF rsDC.EOF THEN
								str_comments="None"
								response.write("Summary for Route: "&routename&" started @ "&rst&" on "&dataWrite(formatDateTime(touchindatetime,vbshortdate))&" / Pupils Who Touched In: "&td_count&" / Driver comments: "&capComments(str_comments))
								response.write(",,,,,,,,,,,,,,,,,,,,,,,,,,")
								Response.Write vbNewLine
						 END IF
						 DO WHILE NOT rsDC.EOF
								x_did=rsDC("driverID")
								x_nme=rsDC("drivername")
								str_comments=rsDC("route_comments")
								response.write("Summary for Route: "&routename&" started @ "&rst&" on "&dataWrite(formatDateTime(touchindatetime,vbshortdate))&" / Pupils Who Touched In: "&td_count&" / Driver Name: "&x_nme&" [ID#"&x_did&"]<br />Driver comments: "&capComments(str_comments))
								response.write(",,,,,,,,,,,,,,,,,,,,,,,,,,")
								Response.Write vbNewLine
								IF icd="yes" THEN
									IF rsDC("healthtestT") <> "" THEN 
										response.write("Driver tested: ")
										IF rsDC("healthtestRESULT") <> "" THEN
											IF rsDC("healthtestRESULT")="positive" THEN response.write("positive") ELSE response.write(rsDC("healthtestRESULT")) END IF
										ELSE
											response.write(" ?")
										END IF
										response.write(" for "&rsDC("healthtestT")&" on ")
										IF rsDC("healthtestDATE") <> "" AND IsDate(rsDC("healthtestDATE")) THEN response.write(formatDateTime(rsDC("healthtestDATE"),2)) END IF
										response.write(",,,,,,,,,,,,,,,,,,,,,,,,,,")
										Response.Write vbNewLine
									END IF
								END IF
						 rsDC.MoveNext
						 loop
						 rsDC.Close
						 SET rsDC=Nothing
					END IF
				END IF

					response.write(dataWrite(journeyID))
					response.write(","&dataWrite(schoolID))
					response.write(","&dataWrite(routename))
					response.write(","&dataWrite(routeID))
					response.write(","&dataWrite(pupilID))
					response.write(","&dataWrite(roleID))
					response.write(","&dataWrite(pupilname))
					response.write(","&dataWrite(rst))
					response.write(","&dataWrite(touchin))
					response.write(","&dataWrite(touchindatetime))
					response.write(","&dataWrite(touchinlat))
					response.write(","&dataWrite(touchinlong))
					response.write(","&dataWrite(touchin_wp_id))
					response.write(","&dataWrite(touchin_wp_label))
					response.write(","&dataWrite(touchin_wp_ref))
					response.write(","&dataWrite(touchin_wp_addr))
					response.write(","&dataWrite(touchout))
					response.write(","&dataWrite(touchoutdatetime))
					response.write(","&dataWrite(touchoutlat))
					response.write(","&dataWrite(touchoutlong))
					response.write(","&dataWrite(touchout_wp_id))
					response.write(","&dataWrite(touchout_wp_label))
					response.write(","&dataWrite(touchout_wp_ref))
					response.write(","&dataWrite(touchout_wp_addr))
					IF icd="yes" THEN
						response.write(","&dataWrite(td_healthtestT))
						response.write(","&dataWrite(td_healthtestDATE))
						response.write(","&dataWrite(td_healthtestRESULT))
						response.write(","&dataWrite(td_healthtestBARCODE))
						response.write(","&dataWrite(td_healthtestNEXTDATEDUE))
					ELSE
						response.write(",-,-,-,-,-")
					END IF
						
	
					Response.Write vbNewLine
				
				prevTime=rst
				prevDate=curDate
				prevRouteID=routeID
					
				RS1.MoveNext
				loop
				RS1.Close
				SET RS1 = Nothing
	
	END IF


END IF
%>