<%
Function Qpad(tval)
	IF len(tval)=1 THEN
		Qpad="0"&tval
	ELSE
		Qpad=tval
	END IF
End Function
Function checkRouteEnabled(sID,rNM)
	IF sID <> "" AND IsNumeric(sID) AND rNM <> "" THEN
		SET rsRC=dbCBK.Execute("SELECT routeID from cbk_routes WHERE schoolID='"&sID&"' AND routename='"&rNM&"' AND curSTATUS='on' limit 1")
		IF rsRC.EOF THEN
			checkRouteEnabled="false"
		ELSE
			checkRouteEnabled="true"
		END IF
		rsRC.close
		SET rsRC = Nothing
	ELSE
		'missing data
		checkRouteEnabled="false"
	END IF
End Function

Function checkNewScan(sID,nfcID,rID)
	IF sID <> "" AND IsNumeric(sID) AND nfcID <> "" AND rID <> "" AND IsNumeric(rID) THEN
		rstart=fetchRouteStartTimeByID(sID,rID)
		curdate=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now()))
		SET rsRC=dbCBK.Execute("SELECT touchin,touchindatetime,touchout,touchoutdatetime from cbk_touchdata WHERE schoolID='"&sID&"' AND routeID='"&rID&"' AND nfcID='"&nfcID&"' AND routeSTARTTIME='"&rstart&"' AND touchindatetime LIKE '"&curdate&"%' order by touchindatetime desc limit 1")
		IF rsRC.EOF THEN
			retStr="false"
		END IF
		DO WHILE NOT rsRC.EOF
			sTouchin=rsRC("touchin")
			sDate=rsRC("touchindatetime")
			sTouchout=rsRC("touchout")
			sDate2=rsRC("touchoutdatetime")
		rsRC.MoveNext
		loop
		rsRC.Close
		SET rsRC = Nothing
		IF retStr="false" THEN
			'no result must be a new scan so return false
			checkNewScan="false"
		ELSE
			IF sTouchin="1" AND sTouchout="1" THEN
				'user has already touched in and out so return recent
				checkNewScan="recent"
			ELSEIF sTouchin="1" AND sTouchout="0" THEN
				'there is a recent touch in result so make sure its more than 5 mins old incase of accidental double tap
					IF NOT(IsNull(sDate)) THEN
						tDate=formatDateTime(sDate,0)
						nDate=formatDateTime(now(),0)
						IF DateDiff("n",tDate,nDate) < 5 THEN
							'touchin scan from this card received less than 5 mins ago ignore it
							checkNewScan="recent"
						ELSE
							'touch in scan found is more than 5 mins old, so this must be scanout
							checkNewScan="true"
						END IF
					END IF
			END IF
		END IF
	ELSE
		'missing data
		checkNewScan="false"
	END IF
End Function

Function registerNewTouch(sID,rNM,nfcID,sLat,sLon)
	IF sID <> "" AND IsNumeric(sID) AND nfcID <> "" AND rNM <> "" AND sLat <> "" AND sLon <> "" THEN
		'select pupil details by nfcid
		SET rsRC=dbCBK.Execute("SELECT `pupilID`, `roleID`, `name`, `healthtestT`, `healthtestDATE`, `healthtestRESULT`, `healthtestBARCODE`, `healthtestNEXTDATEDUE` from cbk_pupils WHERE schoolID='"&sID&"' AND rfID='"&nfcID&"' limit 1")
		IF rsRC.EOF THEN
			ret="failed"
		END IF
		DO WHILE NOT rsRC.EOF
			n_pupilID=rsRC("pupilID")
			n_roleID=rsRC("roleID")
			n_pupilname=rsRC("name")
			n_healthtestT=rsRC("healthtestT")
			n_healthtestDATE=rsRC("healthtestDATE")
				ht_DATE_posted=n_healthtestDATE
				IF ht_DATE_posted <> "" AND (IsDate(ht_DATE_posted)) THEN
				'ok	
					f=formatDateTime(CDate(ht_DATE_posted),2)
					'format for mysql
					ht_DATE=year(f)&"-"&Qpad(month(f))&"-"&Qpad(day(f))&" 23:59:59"
					ht_DATE_SQL1=", `healthtestDATE`"
					ht_DATE_SQL2=", '"&ht_DATE&"'"
				ELSE
					ht_DATE=""
					ht_DATE_SQL1=""
					ht_DATE_SQL2=""
				END IF
			n_healthtestRESULT=rsRC("healthtestRESULT")
			n_healthtestBARCODE=rsRC("healthtestBARCODE")
			n_healthtestNEXTDATEDUE=rsRC("healthtestNEXTDATEDUE")
				ht_DATENEXTDUE_posted=n_healthtestNEXTDATEDUE
				IF ht_DATENEXTDUE_posted <> "" AND (IsDate(ht_DATENEXTDUE_posted)) THEN
				'ok	
					t=formatDateTime(CDate(ht_DATENEXTDUE_posted),2)
					'format for mysql
					ht_DATENEXTDUE=year(t)&"-"&Qpad(month(t))&"-"&Qpad(day(t))&" 23:59:59"
					ht_DATENEXTDUE_SQL1=", `healthtestNEXTDATEDUE`"
					ht_DATENEXTDUE_SQL2=", '"&ht_DATENEXTDUE&"'"
				ELSE
					ht_DATENEXTDUE=""
					ht_DATENEXTDUE_SQL1=""
					ht_DATENEXTDUE_SQL2=""
				END IF
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		
		IF n_pupilID <> "" AND IsNumeric(n_pupilID) THEN
			rID=fetchRouteId(sID,rNM)
			rstrtTime=fetchRouteStartTime(sID,rNM)
			ti_date=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now())) & " " & Hour(now())&":"&Minute(now())&":"&Second(now())
			'get any waypoint data here
			nt_closest_wp=getClosestWaypointID(sLat,sLon,sID,rID)
			IF nt_closest_wp <> "" AND IsNumeric(nt_closest_wp) THEN
				nt_closest_wp=CInt(nt_closest_wp)
				SET rsWPD=dbCBK.Execute("SELECT label,ref,fmt_address FROM cbk_route_waypoints WHERE wpID='"&nt_closest_wp&"' limit 1")
				IF rsWPD.EOF THEN
					nt_closest_wp="0"
					nt_wp_label=""
					nt_wp_ref=""
					nt_wp_addr=""
				END IF
				DO WHILE NOT rsWPD.EOF
					nt_wp_label=rsWPD("label")
					nt_wp_ref=rsWPD("ref")
					nt_wp_addr=rsWPD("fmt_address")
				rsWPD.MoveNext
				loop
				rsWPD.Close
				SET rsWPD=Nothing
			ELSE
				nt_closest_wp="0"
				nt_wp_label=""
				nt_wp_ref=""
				nt_wp_addr=""
			END IF

			'insert new touch record
			dbCBK.Execute("INSERT into cbk_touchdata (`schoolID`,`routeID`,`pupilID`,`roleID`,`nfcID`,`pupilname`,`routeSTARTTIME`,`touchin`,`touchindatetime`,`touchinlat`,`touchinlong`,`touchin_wp_id`,`touchin_wp_label`,`touchin_wp_ref`,`touchin_wp_addr`,`touchout`,`healthtestT`"&ht_DATE_SQL1&",`healthtestRESULT`,`healthtestBARCODE`"&ht_DATENEXTDUE_SQL1&") VALUES ('"&sID&"','"&rID&"','"&n_pupilID&"','"&n_roleID&"','"&nfcID&"','"&n_pupilname&"','"&rstrtTime&"','1','"&ti_date&"','"&sLat&"','"&sLon&"','"&CInt(nt_closest_wp)&"','"&nt_wp_label&"','"&nt_wp_ref&"','"&nt_wp_addr&"','0','"&ht_type&"'"&ht_DATE_SQL2&",'"&ht_RESULT&"','"&ht_CODE&"'"&ht_DATENEXTDUE_SQL2&")")
			'registerNewTouch="success"
		ELSE
			registerNewTouch="failed"
		END IF
	ELSE
		'missing data
		registerNewTouch="failed"
	END IF
End Function

Function registerNewTouchOut(sID,rNM,nfcID,sLat,sLon)
	IF sID <> "" AND IsNumeric(sID) AND nfcID <> "" AND rNM <> "" AND sLat <> "" AND sLon <> "" THEN
		'select pupil details by nfcid
		SET rsRC=dbCBK.Execute("SELECT `pupilID`, `roleID`, `name` from cbk_pupils WHERE schoolID='"&sID&"' AND rfID='"&nfcID&"' limit 1")
		IF rsRC.EOF THEN
			ret="failed"
		END IF
		DO WHILE NOT rsRC.EOF
			n_pupilID=rsRC("pupilID")
			n_roleID=rsRC("roleID")
			n_pupilname=rsRC("name")
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		
		IF n_pupilID <> "" AND IsNumeric(n_pupilID) THEN
			rID=fetchRouteId(sID,rNM)
			ti_date=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now())) & " " & Hour(now())&":"&Minute(now())&":"&Second(now())
			curdate=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now()))

			'get any waypoint data here
			nto_closest_wp=getClosestWaypointID(sLat,sLon,sID,rID)
			IF nto_closest_wp <> "" AND IsNumeric(nto_closest_wp) THEN
				nto_closest_wp=CInt(nto_closest_wp)
				SET rsWPD=dbCBK.Execute("SELECT label,ref,fmt_address FROM cbk_route_waypoints WHERE wpID='"&nto_closest_wp&"' limit 1")
				IF rsWPD.EOF THEN
					nto_closest_wp="0"
					nto_wp_label=""
					nto_wp_ref=""
					nto_wp_addr=""
				END IF
				DO WHILE NOT rsWPD.EOF
					nto_wp_label=rsWPD("label")
					nto_wp_ref=rsWPD("ref")
					nto_wp_addr=rsWPD("fmt_address")
				rsWPD.MoveNext
				loop
				rsWPD.Close
				SET rsWPD=Nothing
			ELSE
				nto_closest_wp="0"
				nto_wp_label=""
				nto_wp_ref=""
				nto_wp_addr=""
			END IF


			'update touch record
			'note the order by clause is important to ensure latest route is updated incase of multiple routes on same day
			dbCBK.Execute("UPDATE cbk_touchdata SET touchout='1', touchoutdatetime='"&ti_date&"', touchoutlat='"&sLat&"', touchoutlong='"&sLon&"', touchout_wp_id='"&CInt(nto_closest_wp)&"', touchout_wp_label='"&nto_wp_label&"', touchout_wp_ref='"&nto_wp_ref&"', touchout_wp_addr='"&nto_wp_addr&"' WHERE nfcID='"&nfcID&"' AND touchindatetime LIKE '"&curdate&"%' AND touchout='0' order by touchindatetime desc limit 1")
			registerNewTouchOut="success"
		ELSE
			registerNewTouchOut="failed"
		END IF
	ELSE
		'missing data
		registerNewTouchOut="failed"
	END IF
End Function

Function fetchRouteId(sID,rNM)
	IF sID <> "" AND IsNumeric(sID) AND rNM <> "" THEN
		SET rsRC=dbCBK.Execute("SELECT routeID from cbk_routes WHERE schoolID='"&sID&"' AND routename='"&rNM&"' limit 1")
		IF rsRC.EOF THEN
			fetchRouteId="0"
			Exit Function
		END IF
		DO WHILE NOT rsRC.EOF
			f_rtid=rsRC("routeID")
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		fetchRouteId=f_rtid
	ELSE
		'missing data
		fetchRouteId="0"
	END IF
End Function

Function fetchRouteStartTime(sID,rNM)
	IF sID <> "" AND IsNumeric(sID) AND rNM <> "" THEN
		SET rsRC=dbCBK.Execute("SELECT timeSTART from cbk_routes WHERE schoolID='"&sID&"' AND routename='"&rNM&"' limit 1")
		IF rsRC.EOF THEN
			fetchRouteStartTime="--"
			Exit Function
		END IF
		DO WHILE NOT rsRC.EOF
			f_rtid=rsRC("timeSTART")
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		fetchRouteStartTime=f_rtid
	ELSE
		'missing data
		fetchRouteStartTime="--"
	END IF
End Function

Function fetchRouteStartTimeByID(sID,rID)
	IF sID <> "" AND IsNumeric(sID) AND rID <> "" AND IsNumeric(rID) THEN
		SET rsRC=dbCBK.Execute("SELECT timeSTART from cbk_routes WHERE schoolID='"&sID&"' AND routeID='"&rID&"' limit 1")
		IF rsRC.EOF THEN
			fetchRouteStartTimeByID="--"
			Exit Function
		END IF
		DO WHILE NOT rsRC.EOF
			f_rstbyid=rsRC("timeSTART")
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		fetchRouteStartTimeByID=f_rstbyid
	ELSE
		'missing data
		fetchRouteStartTimeByID="--"
	END IF
End Function


Function fetchPupilIdbyNFC(nID)
	IF nID <> "" THEN
		SET rsRC=dbCBK.Execute("SELECT pupilID from cbk_pupils WHERE rfID='"&nID&"' limit 1")
		IF rsRC.EOF THEN
			f_rtid="0"
		END IF
		DO WHILE NOT rsRC.EOF
			f_rtid=rsRC("pupilID")
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		fetchPupilIdbyNFC=f_rtid
	ELSE
		'missing data
		fetchPupilIdbyNFC="0"
	END IF
End Function

Function fetchExpectedPassengerList(sID,rNM)
	IF sID <> "" AND IsNumeric(sID) AND rNM <> "" THEN
		SET rsRC=dbCBK.Execute("SELECT pupilIDS from cbk_routes WHERE schoolID='"&sID&"' AND routename='"&rNM&"' limit 1")
		IF rsRC.EOF THEN
			retList=""
		END IF
		DO WHILE NOT rsRC.EOF
			retList=rsRC("pupilIDS")
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		fetchExpectedPassengerList=retList
	ELSE
		'missing data
		fetchExpectedPassengerList=""
	END IF
End Function

Function fetchOnboardPassengerList(sID,rID)
	IF sID <> "" AND IsNumeric(sID) AND rID <> "" AND IsNumeric(rID) THEN
		rs=fetchRouteStartTimeByID(sID,rID)
		curdate=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now()))
		onbrdstrng="||"
		SET rsRC=dbCBK.Execute("SELECT pupilID from cbk_touchdata WHERE schoolID='"&sID&"' AND routeID='"&rID&"' AND routeSTARTTIME='"&rs&"' AND touchindatetime LIKE '"&curdate&"%' AND touchout='0' limit 1")
		IF rsRC.EOF THEN
			onbrdstrng="||"
		END IF
		DO WHILE NOT rsRC.EOF
			onbrdstrng=onbrdstrng&rsRC("pupilID")&"||"
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		IF onbrdstrng="||" THEN
			fetchOnboardPassengerList=""
		ELSE
			fetchOnboardPassengerList=onbrdstrng
		END IF
	ELSE
		'missing data
		fetchOnboardPassengerList=""
	END IF
End Function

Function fetchOnboardPassengerCount(sID,rID)
	IF sID <> "" AND IsNumeric(sID) AND rID <> "" AND IsNumeric(rID) THEN
		rs=fetchRouteStartTimeByID(sID,rID)
		curdate=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now()))
		SET rsRC=dbCBK.Execute("SELECT COUNT(*) As pcnt from cbk_touchdata WHERE schoolID='"&sID&"' AND routeID='"&rID&"' AND touchindatetime LIKE '"&curdate&"%' AND routeSTARTTIME='"&rs&"' AND touchout='0' limit 1")
		IF rsRC.EOF THEN
			onbrd=0
		END IF
		DO WHILE NOT rsRC.EOF
			onbrd=rsRC("pcnt")
		rsRC.MoveNext
		loop
		rsRC.close
		SET rsRC = Nothing
		fetchOnboardPassengerCount=onbrd
	ELSE
		'missing data
		fetchOnboardPassengerCount=0
	END IF
End Function


Function checkCorrectBus(sID,rID,pID)
	IF sID <> "" AND IsNumeric(sID) AND rID <> "" AND IsNumeric(rID) AND pID <> "" AND IsNumeric(pID) THEN
		srch_strng="||"&pID&"||"
		SET rsRC=dbCBK.Execute("SELECT routeID from cbk_routes WHERE routeID='"&rID&"' AND pupilIDS LIKE '%"&srch_strng&"%' limit 1")
		IF rsRC.EOF THEN
			rt="wrong"
		ELSE
			rt="ok"
		END IF
		rsRC.close
		SET rsRC = Nothing
		checkCorrectBus=rt
	ELSE
		'missing data
		checkCorrectBus="wrong"
	END IF
End Function


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  call the distance function and pass the 4 lat long vars and   :::
':::  the output type:  M for miles, K for kilometres, T for metres :::
':::  or N for nautical miles.									    :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
const pi = 3.14159265358979323846
Function distance(lat1, lon1, lat2, lon2, unit)
  Dim theta, dist
  If lat1 = lat2 And lon1 = lon2 Then
    distance = 0
  Else
    theta = lon1 - lon2
    dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1)) * cos(deg2rad(lat2)) * cos(deg2rad(theta))
    dist = acos(dist)
    dist = rad2deg(dist)
    distance = dist * 60 * 1.1515
    Select Case ucase(unit)
      Case "K"
        distance = distance * 1.609344
      Case "N"
        distance = distance * 0.8684
	  Case "T"
        distance = (distance * 1.609344) * 1000
    End Select
  End If
End Function
Function acos(rad)
  If Abs(rad) <> 1 Then
    acos = pi/2 - Atn(rad / Sqr(1 - rad * rad))
  ElseIf rad = -1 Then
    acos = pi
  End If
End function
Function deg2rad(Deg)
  deg2rad = cdbl(Deg * pi / 180)
End Function
Function rad2deg(Rad)
  rad2deg = cdbl(Rad * 180 / pi)
End Function

Function getClosestWaypointID(scn_lat,scn_lng,sch_id,rt_id)
	IF scn_lat <> "" AND scn_lng <> "" THEN
		IF sch_id <> "" AND IsNumeric(sch_id) AND rt_id <> "" AND IsNumeric(rt_id) THEN
			'create a random variable
			Dim rmax,rmin
			rmax=100
			rmin=1
			Randomize
		    pre_n1=Int((rmax-rmin+1)*Rnd+rmin)
			pre_n2=Int((rmax-rmin+1)*Rnd+rmin)
			str_uni_ref=pre_n1&pre_n2&year(now())&month(now())&day(now())&hour(now())&minute(now())&second(now())
			'all variables present start processing
			'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
			'::: first we select all waypoints coords and waypoint id :::
			'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
			SET rsFW=dbCBK.Execute("SELECT wpID, lat, lon FROM cbk_route_waypoints WHERE schoolID='"&sch_id&"' AND routeID='"&rt_id&"' limit 22")
			IF rsFW.EOF THEN
				getClosestWaypointID="None"
				Exit Function
			END IF
			DO WHILE NOT rsFW.EOF
				str_wp_id=rsFW("wpID")
				str_lat=rsFW("lat")
				str_lon=rsFW("lon")

					'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
					'::: now calc distance for each point and insert result into calc table :::
					'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

					IF str_wp_id <> "" AND str_lat <> "" AND str_lon <> "" THEN
						str_dis=distance(scn_lat, scn_lng, str_lat, str_lon, "T")
						dbCBK.Execute("INSERT into cbk_waypoint_calc (`wpID`,`uniREF`,`distance`) VALUES('"&str_wp_id&"','"&str_uni_ref&"','"&str_dis&"')")
					END IF
			rsFW.MoveNext
			loop
			rsFW.Close
			SET rsFW=Nothing

					'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
					'::: now select waypoint from calc table order by shortest distance     :::
					'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

					SET rsDistResult=dbCBK.Execute("SELECT `wpID` FROM cbk_waypoint_calc WHERE `uniREF`='"&str_uni_ref&"' order by distance asc limit 1")
					IF rsDistResult.EOF THEN
						getClosestWaypointID="None"
						Exit Function
					END IF
					DO WHILE NOT rsDistResult.EOF
						getClosestWaypointID=rsDistResult("wpID")
					rsDistResult.MoveNext
					loop
					rsDistResult.Close
					SET rsDistResult=Nothing

					'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
					'::: finally delete our calculations from the database table            :::
					'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
					dbCBK.Execute("DELETE FROM cbk_waypoint_calc WHERE `uniREF`='"&str_uni_ref&"' limit 22")
		ELSE
			getClosestWaypointID="None"
		END IF
	ELSE
		getClosestWaypointID="None"
	END IF
End Function
%>