<% On Error Resume Next IF (checkPermission("location")="FAILED") THEN response.write("You do not have permission to view this page") response.end END IF Function Qpad(tval) IF len(tval)=1 THEN Qpad="0"&tval ELSE Qpad=tval END IF End Function Function dataWriteLatLong(fld) Set laloregEx = New RegExp laloregEx.Global = true laloregEx.Pattern = "[^0-9a-zA-Z\:\-\.\-]" IF IsNull(fld) OR IsEmpty(fld) THEN dataWriteLatLong=fld ELSE dataWriteLatLong=laloregEx.Replace(fld, "") END IF End Function Function fetchOnboardPassengerCount(sID,rID) IF sID <> "" AND IsNumeric(sID) THEN '********************************************************************************************************************** '******TO COUNT ALL PASS 0 and 0 as RID and SID, to filter by either or both, pass the routeID or schoolID values****** '****** NOTE pupils that dont touchout on early route will still be counted if route is running ********************** '********************************************************************************************************************** curdate=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now())) IF rID <> "" AND IsNumeric(rID) AND rID <> "0" THEN 'apply a route ID filter rfilt=" routeID='"&rID&"'" ELSE '***************************** MYSQL BUG ALERT*********************************************************************************************************** '**look for all route ID data USING IN and group_concat subquery, note we need to run the query seperately or it doesnt work correctly******************* '**seems we found a bug when using the GROUP_CONCAT inside the IN function as it only works when run seperately, subquery returns incorrect number ****** '******************************************************************************************************************************************************** SET rsFIX=dbCBK.Execute("SELECT GROUP_CONCAT(`routeID`) As route_string from cbk_routes WHERE curSTATUS='on'") IF rsFIX.EOF THEN str_rts="" END IF DO WHILE NOT rsFIX.EOF str_rts=rsFIX("route_string") rsFIX.MoveNext loop rsFIX.Close SET rsFIX=Nothing IF str_rts <> "" AND NOT(IsNull(str_rts)) THEN rfilt=" routeID IN("&str_rts&")" ELSE rfilt=" routeID > '0'" 'there are no running routes, so there can be no active passengers, so why are we here lets exit ;) fetchOnboardPassengerCount="0" Exit Function END IF END IF IF sID <> "0" THEN sfilt=" AND schoolID='"&sID&"'" ELSE sfilt="" END IF pc_sql="select COUNT(DISTINCT(pupilID)) As pcnt from cbk_touchdata WHERE "&rfilt&sfilt&" AND touchindatetime LIKE '"&curdate&"%' AND touchout='0'" SET rsRC=dbCBK.Execute(pc_sql) 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 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 fetchActiveLocationCountBySchool(scid) IF scid <> "" AND IsNumeric(scid) THEN curdate=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now())) SET rsRC=dbCBK.Execute("SELECT COUNT(*) As pcnt from cbk_touchdata WHERE schoolID='"&scid&"' AND touchindatetime LIKE '"&curdate&"%' 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 fetchActiveLocationCountBySchool=onbrd ELSE 'missing data fetchActiveLocationCountBySchool=0 END IF End Function Function fetchActiveRoutes(r_num) IF CStr(r_num)="1" THEN curdate=year(now())&"-"&Qpad(month(now()))&"-"&Qpad(day(now())) SET rsRC=dbCBK.Execute("SELECT COUNT(*) As pcnt from cbk_routes WHERE curSTATUS='on' limit 1") IF rsRC.EOF THEN ar="0" END IF DO WHILE NOT rsRC.EOF ar=rsRC("pcnt") rsRC.MoveNext loop rsRC.close SET rsRC = Nothing fetchActiveRoutes=CStr(ar) ELSE 'missing data fetchActiveRoutes="0" END IF End Function str_locations = "[" str_labels = "[" str_l_c = 1 strmapSQL="SELECT routeID, routeNAME, curLAT, curLONG, pupilIDS FROM cbk_routes WHERE curSTATUS='on'" SET rsSEARCH = dbCBK.Execute(strmapSQL) IF rsSEARCH.EOF THEN 'none found END IF DO WHILE NOT rsSEARCH.EOF str_route_id=rsSEARCH("routeID") strTIT=rsSEARCH("routeNAME") strMLT=rsSEARCH("curLAT") strMLN=rsSEARCH("curLONG") pupilIDS=rsSEARCH("pupilIDS") strPC=fetchOnboardPassengerCount("0",str_route_id) mapD=strPC & " pupils onboard" IF strMLT <> "" AND strMLN <> "" THEN str_locations = str_locations & "['" & "
View / Edit Itenary
" & strTIT & "
" & mapD & "..
" & "'," & strMLT & ", " & strMLN & ", " & str_l_c & "]," IF len(strTIT) > 11 THEN strPadDots=".." ELSE strPadDots="" END IF str_labels = str_labels & "['" & left(strTIT,11) & strPadDots & "']," str_l_c = str_l_c + 1 ELSE 'do nothing END IF rsSEARCH.MoveNext loop rsSEARCH.Close SET rsSEARCH = Nothing IF len(str_locations)>1 THEN str_locations = left(str_locations,len(str_locations)-1) str_labels = left(str_labels,len(str_labels)-1) ELSE str_locations="[" str_labels="[" END IF str_locations = str_locations & "];" str_labels = str_labels & "];" IF str_locations="];" THEN str_locations="[];" END IF IF str_labels="];" THEN str_labels="[];" END IF %>
<%IF request("display")<>"" THEN%> " /> <%END IF%>

Location Map

Keep track of bus locations Active Vehicles: <%=fetchActiveRoutes("1")%> Active Passengers: <%=fetchOnboardPassengerCount("0","0")%>

Search Map

by place, road or route
/> />
" style="<%IF REQUEST("v")="loc" THEN response.write("display:none;")%>width:78%;float:left;border-top-right-radius:0px;border-bottom-right-radius:0px;" /> " style="<%IF REQUEST("v")="loc" THEN response.write("") ELSE response.write("display:none;") END IF%>width:78%;float:left;border-top-right-radius:0px;border-bottom-right-radius:0px;" />

Map Tools

recheck location / plotting
<% IF REQUEST("v")="waypoints" THEN r=sanitizeRequest("routeID","querystring") IF r <> "" AND IsNumeric(r) THEN 'retrieve our start point for display SET rsEd=dbCBK.Execute("SELECT `lat`,`lon` FROM cbk_route_waypoints WHERE routeID='"&r&"' AND entry_type='start' AND lat <> '' limit 1") IF rsEd.Eof THEN 'no start found END IF DO WHILE NOT rsEd.Eof %> ,<%=rsEd("lon")%>" /> <% rsEd.MoveNext loop rsEd.Close SET rsEd= Nothing 'retrieve our way points for display wc=1 SET rsEd=dbCBK.Execute("SELECT `lat`,`lon` FROM cbk_route_waypoints WHERE routeID='"&r&"' AND entry_type='way' AND lat <> '' limit 20") IF rsEd.Eof THEN 'no ways found END IF DO WHILE NOT rsEd.Eof %> ,<%=rsEd("lon")%>" /> <% wc=wc+1 rsEd.MoveNext loop rsEd.Close SET rsEd= Nothing 'retrieve our end point for display SET rsEd=dbCBK.Execute("SELECT `lat`,`lon` FROM cbk_route_waypoints WHERE routeID='"&r&"' AND entry_type='end' AND lat <> '' limit 1") IF rsEd.Eof THEN 'no ways found END IF DO WHILE NOT rsEd.Eof %> ,<%=rsEd("lon")%>" /> <% rsEd.MoveNext loop rsEd.Close SET rsEd= Nothing 'fetch live location data for selected route SET RSLiveBus=dbCBK.Execute("select `routename`,`curLAT`,`curLONG`,`curSTATUS` FROM cbk_routes WHERE routeID='"&r&"' limit 1") DO WHILE NOT RSLiveBus.EOF %> " /> " /> " /> " /> <% RSLiveBus.MoveNext loop RSLiveBus.Close SET RSLiveBus = Nothing %> <% END IF END IF %>