<!--#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 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"


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


	IF StrError <> "None" THEN
		'an error occurred
		response.write(StrError)
	ELSE
				rdSQL="select routeID, routename, driverID, pupilIDS from cbk_routes WHERE pupilIDS <> '' AND pupilIDS <> 'none' AND"

				IF routeID <> "" AND routeID <> "all" THEN
					rdSQL=rdSQL&" routeID='"&routeID&"'"
				ELSE
					rdSQL=rdSQL&" routeID > '0'"
				END IF
				
				rdSQL=rdSQL&" AND schoolID='"&str_u_id&"'"
				
				rdSQL=rdSQL&" order by routeID asc limit 5000"
	
				dim RS1
				set RS1 = dbCBK.Execute(rdSQL)
				
				Response.ContentType = "text/csv"
				str_filename="test_data_"&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


					'''disabled auto column name writing below as we are querying 2 tables
			        '''Response.Write Separator & Field

			        Separator = ","
			    next

				response.write("Route ID, Name, Test Type, Test Date, Test Result, Test Code, Next Test Due")	

			    Response.Write vbNewLine


			

				do while not RS1.EOF
					
				t_rid=RS1("routeID")
				t_rnm=RS1("routename")
				t_did=RS1("driverID")
				t_pl=RS1("pupilIDS")
					
				response.write("Route Name: " & t_rnm & " [ID: " & t_rid & "],,,,,," & vbNewLine)
				
				IF t_did <> "0" THEN
					SET rsDrvSTATUS=dbCBK.Execute("SELECT driverNAME, healthtestT, healthtestDATE, healthtestRESULT, healthtestBARCODE, healthtestNEXTDATEDUE from cbk_drivers WHERE driverID='"&t_did&"' AND schoolID='"&str_u_id&"' limit 1")
					DO  WHILE NOT rsDrvSTATUS.EOF
						t_dnm=rsDrvSTATUS("driverNAME")
						t_dltstd=rsDrvSTATUS("healthtestDATE")
						t_dltrslt=rsDrvSTATUS("healthtestRESULT")
					rsDrvSTATUS.MoveNext
					loop
					rsDrvSTATUS.Close
					SET rsDrvSTATUS=Nothing
					
					response.write("Driver Name: " & t_dnm & "[ID: " & t_did & "],,,,,," & vbNewLine)
					response.write("Driver Tested: ")
						IF t_dltstd <> "" THEN response.write(formatDateTime(t_dltstd,vblongdate) & ",,,,,," & vbNewLine) ELSE response.write("-,,,,,," & vbNewLine) END IF
					response.write("Test Result: ")
						IF t_dltrslt <> "" THEN response.write(t_dltrslt & ",,,,,," & vbNewLine) ELSE response.write("-,,,,,," & vbNewLine) END IF

				END IF

				IF t_pl <> "" AND t_pl <> "none" THEN
						
						crl=split(t_pl,"||")	
						for each i in crl
							IF i <> "" AND IsNumeric(i) THEN
								strSQL = "SELECT pupilID, name, healthtestT, healthtestDATE, healthtestRESULT, healthtestBARCODE, healthtestNEXTDATEDUE FROM cbk_pupils WHERE pupilID='"&i&"' AND schoolID='"&str_u_id&"' order by name asc limit 1"
								

									SET rsB=dbCBK.Execute(strSQL)
								
									DO WHILE NOT rsB.EOF
									t_pid=rsB("pupilID")
									d_name=rsB("name")
									d_healthtestT=rsB("healthtestT")
									d_healthtestDATE=rsB("healthtestDATE")
									d_healthtestRESULT=rsB("healthtestRESULT")
									d_healthtestBARCODE=rsB("healthtestBARCODE")
									d_healthtestNEXTDATEDUE=rsB("healthtestNEXTDATEDUE")

									response.write(dataWrite(t_rid))
									response.write(","&dataWrite(d_name))
									IF d_healthtestT <> "" THEN response.write(","&dataWrite(d_healthtestT)) ELSE response.write(","&dataWrite("-")) END IF						
									IF d_healthtestDATE <> "" THEN response.write(","&dataWrite(formatDateTime(d_healthtestDATE,vblongdate))) ELSE response.write(","&dataWrite("-")) END IF
									IF d_healthtestRESULT <> "" THEN response.write(","&dataWrite(d_healthtestRESULT)) ELSE response.write(","&dataWrite("-")) END IF
									IF d_healthtestBARCODE <> "" THEN response.write(",test no: " & dataWrite(d_healthtestBARCODE)) ELSE response.write(","&dataWrite("-")) END IF
									IF d_healthtestNEXTDATEDUE <> "" THEN response.write(","&dataWrite(formatDateTime(d_healthtestNEXTDATEDUE,vblongdate))) ELSE response.write(","&dataWrite("-")) END IF
								
									Response.Write vbNewLine

									rsB.MoveNext
									loop
									rsB.Close
									SET rsB=Nothing
								
							END IF
						next
					END IF
				


				Response.Write vbNewLine


				
				
	
					
				RS1.MoveNext
				loop
				RS1.Close
				SET RS1 = Nothing
	
	END IF


END IF
%>