""); $totalWaypoints = array(); //$ridersArrayLeaderboard = array(); $cpArray = array(); $i = 0; $lastReportPingMins = ''; $lastPingReportMins = ''; $lastReportHours = ''; $lastReportPingHours = ''; $lastPingReportHours = ''; $lastReportDays = ''; $lastPingReportDays = ''; $startTimelog = ''; $result = $conn->query("SELECT COUNT(id) FROM `" . $eventTable . "`"); $row = $result->fetch_row(); $totalWaypoints = $row[0]; // riders db data loop if (!$debug) { $debugAmount = 0; } else { $totalWaypoints = $totalWaypoints * $debugAmount; } for ($x = 0; $x <= $debugAmount; $x++) { $sql = "SELECT DISTINCT imei FROM " . $eventTable . "_rc WHERE active = '1'"; $res = mysqli_query($conn, $sql); while ($data = mysqli_fetch_array($res)) { // firstfix unix here can't work? do we all need these variable? dtf ? check. $sql_name = "SELECT " . $eventTable . ".imei, " . $eventTable . "_rc.color, " . $eventTable . "_rc.nameExt, " . $eventTable . "_rc.startDelay, " . $eventTable . "_rc.finishOverrule, " . $eventTable . "_rc.groupId, " . $eventTable . "_rc.groupFinish, " . $eventTable . "_rc.teamNumber, " . $eventTable . "_rc.country, " . $eventTable . "_rc.scratched, " . $eventTable . "_rc.city, " . $eventTable . "_rc.website, latitude, longitude, weather, fixUnix, firstFixUnix, timeIdle, dateTime, deviceId, distanceToday, totalDistance, distanceOnTrack, altitude, elevation, dtf, speedJSON, speedAvgToday, speedAvgAlltime, attributes, address, course, speed, finished FROM (SELECT * FROM " . $eventTable . " WHERE imei = '" . $data['imei'] . "' ORDER BY fixUnix DESC LIMIT 1) " . $eventTable . " INNER JOIN " . $eventTable . "_rc ON " . $eventTable . "_rc.imei=" . $eventTable . ".imei "; $sql_devicedata = mysqli_query($conn, $sql_name); $deviceData = mysqli_fetch_array($sql_devicedata); if (mysqli_num_rows($sql_devicedata) != 0) { $raceData = true; if (!$deviceData['scratched']) { if($grouppedEvent && $groupColors[$deviceData['groupId']]){ $deviceData['color'] = $groupColors[$deviceData['groupId']]; // Groups get same color } } else { if ($deviceData['scratched'] == '4') { $deviceData['color'] = "#ff0000"; // Crew Color } else { $deviceData['color'] = "#000000"; // Scratched color } } $rider = preg_replace("/[^A-Za-z0-9]/", "", $deviceData['nameExt']); // put rider data into php array if (!empty($rider)) { if ($debug) { $rider = $rider . "" . $i; } // determine last report per rider with valid FIX $lastReportMins = ((time() - $deviceData['fixUnix']) / 60) + ($timezoneCorrection/60); $lastReport = lastReport($lastReportMins); // Get last device ping from rider (fix or not) $lastPingReportMins = ((time() - strtotime($deviceData['dateTime'])) / 60) + ($timezoneCorrection/60); $lastPingReport = lastReport($lastPingReportMins); // check if the last know unixtimestamp is todays otherwise todays data is null $date = date('Y-m-d', $deviceData['fixUnix']); $today = date('Y-m-d'); $attributes = json_decode($deviceData['attributes'], true); // create rider data array for mainpage view if ($debug) { $ridersArray[$rider]['latitude'] = '4.457832'; $ridersArray[$rider]['longitude'] = '52.147147'; } else { $ridersArray[$rider]['latitude'] = floatval($deviceData['latitude']); $ridersArray[$rider]['longitude'] = floatval($deviceData['longitude']); } $ridersArray[$rider]['lastReport'] = $lastReport; $ridersArray[$rider]['lastReportMins'] = round($lastReportMins); $ridersArray[$rider]['teamNumber'] = $deviceData['teamNumber']; $ridersArray[$rider]['startDelay'] = $deviceData['startDelay']; $ridersArray[$rider]['country'] = $deviceData['country']; $ridersArray[$rider]['scratched'] = floatval($deviceData['scratched']); $ridersArray[$rider]['dtf'] = ($deviceData['dtf'] * 1000); $ridersArray[$rider]['dtfKM'] = round($deviceData['dtf']); if(empty($deviceData['groupId']) ) { $deviceData['groupId'] = '1'; } if(!$grouppedEvent){ $deviceData['groupId'] = '1'; } // If no groupped event, force all riders in group 1 to perform ranking and sorting. $ridersArray[$rider]['groupId'] = $deviceData['groupId']; $ridersArray[$rider]['groupHeaderLabel'] = $groupHeaderLabels[$deviceData['groupId']]; $ridersArray[$rider]['groupFinish'] = $deviceData['groupFinish']; if ($date == $today) { $ridersArray[$rider]['avgSpeedToday'] = round($deviceData['speedAvgToday'], 2); $ridersArray[$rider]['todaysDistance'] = round(($deviceData['distanceToday'] * $correctionPercentage), 2); } else { $ridersArray[$rider]['avgSpeedToday'] = '0'; $ridersArray[$rider]['todaysDistance'] = '0'; } $ridersArray[$rider]['totalDistance'] = round(($deviceData['totalDistance'] * $correctionPercentage), 2); $ridersArray[$rider]['currentSpeed'] = round($deviceData['speed'], 2); $ridersArray[$rider]['dbtable'] = $eventTable; $ridersArray[$rider]['riderNameClean'] = $rider; $ridersArray[$rider]['riderColor'] = $deviceData['color']; $ridersArray[$rider]['attributes'] = $deviceData['attributes']; $ridersArray[$rider]['fixTimeUnix'] = $lastReport; $ridersArray[$rider]['fixUnix'] = $deviceData['fixUnix']; $ridersArray[$rider]['firstFixUnix'] = $deviceData['firstFixUnix']; $ridersArray[$rider]['timeIdle'] = $deviceData['timeIdle']; if ( ($deviceData['timeIdle']) && ($deviceData['fixUnix'] != $deviceData['firstFixUnix']) ) { $movingTime = ($deviceData['fixUnix'] - $deviceData['firstFixUnix']) - $deviceData['timeIdle']; $stoppedTime = $deviceData['timeIdle']; $stoppedPercentage = round(($deviceData['timeIdle'] * 100) / ($deviceData['fixUnix'] - $deviceData['firstFixUnix'])); $movingTimePercentage = round(100 - $stoppedPercentage); $ridersArray[$rider]['movingTime'] = $movingTime; $ridersArray[$rider]['movingTimeText'] = sec2elapsed($movingTime); $ridersArray[$rider]['timeIdleText'] = sec2elapsed($deviceData['timeIdle']); $ridersArray[$rider]['stoppedPercentage'] = $stoppedPercentage; $ridersArray[$rider]['movingTimePercentage'] = $movingTimePercentage; } elseif ($deviceData['fixUnix'] != $deviceData['firstFixUnix']) { // No idle time yet so 100% moving time. $movingTime = ($deviceData['fixUnix'] - $deviceData['firstFixUnix']); $stoppedTime = 0; $stoppedPercentage = round(($deviceData['timeIdle'] * 100) / ($deviceData['fixUnix'] - $deviceData['firstFixUnix'])); $movingTimePercentage = round(100 - $stoppedPercentage); $ridersArray[$rider]['movingTime'] = $movingTime; $ridersArray[$rider]['movingTimeText'] = sec2elapsed($movingTime); $ridersArray[$rider]['timeIdleText'] = sec2elapsed($deviceData['timeIdle']); $ridersArray[$rider]['stoppedPercentage'] = $stoppedPercentage; $ridersArray[$rider]['movingTimePercentage'] = $movingTimePercentage; } $ridersArray[$rider]['dateTimeUnix'] = $lastPingReport; $ridersArray[$rider]['address'] = $deviceData['address']; $ridersArray[$rider]['imei'] = $deviceData['imei']; $ridersArray[$rider]['deviceId'] = $deviceData['deviceId']; $ridersArray[$rider]['course'] = $deviceData['course']; /* Speed the new way */ $ridersArray[$rider]['speedJSON'] = $deviceData['speedJSON']; $speedJSON = json_decode($deviceData['speedJSON'], true); $ridersArray[$rider]['routeSpeed'] = $speedJSON['routeSpeed']; if ($deviceData['finishOverrule']) { $ridersArray[$rider]['finishTime'] = $deviceData['finishOverrule'] + $timezoneCorrection; $finishTime = $deviceData['finishOverrule'] + $timezoneCorrection; } else { $ridersArray[$rider]['finishTime'] = $deviceData['finished']; $finishTime = $deviceData['finished']; } $ridersArray[$rider]['altitude'] = $deviceData['altitude']; $ridersArray[$rider]['elevation'] = $deviceData['elevation']; $ridersArray[$rider]['weather'] = $deviceData['weather']; $ridersArray[$rider]['city'] = $deviceData['city']; $ridersArray[$rider]['website'] = $deviceData['website']; // Distance switch depending on data $ridersArray[$rider]['onTrackStatusBoolean'] = false; if ($trackTable || $groupDistanceOnTrack) { if($groupDistanceOnTrack) { $ridersArray[$rider]['totalTrackDistance'] = $totalTrackDistanceGroup[$deviceData['groupId']]; $totalTrackDistance = $totalTrackDistanceGroup[$deviceData['groupId']]; } else { $ridersArray[$rider]['totalTrackDistance'] = $totalTrackDistance; } if (($deviceData['distanceOnTrack'] != "false") && (!empty($deviceData['distanceOnTrack']))) { $ridersArray[$rider]['onTrackStatus'] = "On Track"; $ridersArray[$rider]['onTrackStatusBoolean'] = true; $ridersArray[$rider]['onTrackDistance'] = $deviceData['distanceOnTrack']; $ridersArray[$rider]['onTrackPercentage'] = round((($deviceData['distanceOnTrack'] / $totalTrackDistance) * 100), 2); if( is_infinite($ridersArray[$rider]['onTrackPercentage']) ) { // JSON Encode fails on INF values. $ridersArray[$rider]['onTrackPercentage'] = 0; } // push distance on track value into the totaldistance value for riders who are on track $ridersArray[$rider]['totalDistance'] = round(($deviceData['distanceOnTrack'] * $correctionPercentage), 3); } else { $ridersArray[$rider]['onTrackStatus'] = "Off Track"; $ridersArray[$rider]['onTrackStatusBoolean'] = false; $ridersArray[$rider]['onTrackDistance'] = ''; // off track - no distance known - fallback on calculated distance } } if ($finishTime) { $minutes = ($finishTime - $deviceData['firstFixUnix']) / 60; $day = floor($minutes / 1440); $hour = floor(($minutes - $day * 1440) / 60); $min = floor($minutes - ($day * 1440) - ($hour * 60)); $ridersArray[$rider]['finishedInString'] = $day . " d " . $hour . " h " . $min . " m"; } if ($debug) { $ridersArray[$rider]['riderName'] = $rider; } else { $ridersArray[$rider]['riderName'] = $deviceData['nameExt']; } } if ($config_sortActive == 'checkpointCount') { // default values if ($deviceData['scratched']) { $ridersArray[$rider]['cpLastHitUnix'] = $deviceData['teamNumber'] * 100000; // bring down the scratchers } else { $ridersArray[$rider]['cpLastHitUnix'] = $deviceData['teamNumber']; } $ridersArray[$rider]['cpCounter'] = 0; $ridersArray[$rider]['cpLastCP'] = ""; //$cp_sql = "SELECT DISTINCT cpName FROM " . $dataEventTable . " WHERE cpName != 'nocp' AND imei = '" . $data['imei'] . "'"; $cp_sql = "SELECT cpName, MAX(id) FROM " . $dataEventTable . " WHERE cpName != 'nocp' AND imei = '" . $ridersArray[$rider]['imei'] . "' GROUP BY cpName ORDER BY MAX(id)"; $cp_result = mysqli_query($conn, $cp_sql); $cp_checked_rider = array(); while ($data = mysqli_fetch_array($cp_result)) { $cp_checked_rider[] .= $data['cpName']; } $cpCounter = mysqli_num_rows($cp_result); $ridersArray[$rider]['cpCounter'] = $cpCounter; // Get latest CP hit: $last_cp_sql = "SELECT sub.* FROM ( SELECT cpName, cpDistance, fixUnix, fixTime FROM " . $dataEventTable . " WHERE cpName='".end($cp_checked_rider)."' AND imei = '" . $ridersArray[$rider]['imei'] . "' order by fixUnix ASC LIMIT 5) sub ORDER BY CAST(cpDistance as SIGNED INTEGER), fixUnix ASC LIMIT 1"; $last_cp_result = mysqli_query($conn, $last_cp_sql); while ($last_cpDataResult = mysqli_fetch_array($last_cp_result)) { $ridersArray[$rider]['cpLastCP'] = $last_cpDataResult['cpName']; $ridersArray[$rider]['cpLastHitUnix'] = $last_cpDataResult['fixUnix']; } } $i++; } else { $sql_name = "SELECT * FROM " . $eventTable . "_rc WHERE imei = '" . $data['imei'] . "'"; // echo $sql_name; $resultName = mysqli_query($conn, $sql_name); $deviceData = mysqli_fetch_assoc($resultName); $rider = preg_replace("/[^A-Za-z0-9]/", "", $deviceData['nameExt']); $randomOffset = rand(0, 1000) / 2000; if (!$deviceData['scratched']) { if($grouppedEvent && $groupColors[$deviceData['groupId']]){ $deviceData['color'] = $groupColors[$deviceData['groupId']]; // Groups get same color } } else { if ($deviceData['scratched'] == '4') { $deviceData['color'] = "#ff0000"; // Crew Color } else { $deviceData['color'] = "#000000"; // Scratched color } } if($grouppedEvent){ $startpointLat = $groupStartLocation[$deviceData['groupId']]['latitude']; $startpointLng = $groupStartLocation[$deviceData['groupId']]['longitude']; } $ridersArrayPreRace[$rider]['latitude'] = $startpointLat; $ridersArrayPreRace[$rider]['longitude'] = $startpointLng + ((($randomOffset - 0.5) / 350) * 1.1); $ridersArrayPreRace[$rider]['lastReport'] = 'Start '; $ridersArrayPreRace[$rider]['positionAbbrev'] = '-'; $ridersArrayPreRace[$rider]['teamNumber'] = $deviceData['teamNumber']; $ridersArrayPreRace[$rider]['country'] = $deviceData['country']; $ridersArrayPreRace[$rider]['riderName'] = $deviceData['nameExt']; $ridersArrayPreRace[$rider]['riderNameClean'] = $rider; $ridersArrayPreRace[$rider]['riderColor'] = $deviceData['color']; $ridersArrayPreRace[$rider]['dtf'] = floatval(0); $ridersArrayPreRace[$rider]['dtfKM'] = floatval(0); $ridersArrayPreRace[$rider]['groupId'] = $deviceData['groupId']; $ridersArrayPreRace[$rider]['groupFinish'] = $deviceData['groupFinish']; $ridersArrayPreRace[$rider]['deviceId'] = $deviceData['einsteinDeviceId']; $ridersArrayPreRace[$rider]['preraceCheck'] = $deviceData['preraceCheck']; $ridersArrayPreRace[$rider]['startDelay'] = $deviceData['startDelay']; $ridersArrayPreRace[$rider]['startTimeUnix'] = $groupStartTimes[$deviceData['groupId']] + ($deviceData['startDelay']*60); if(empty($deviceData['groupId']) ) { $deviceData['groupId'] = '1'; } $ridersArrayPreRace[$rider]['groupId'] = $deviceData['groupId']; $ridersArrayPreRace[$rider]['groupHeaderLabel'] = $groupHeaderLabels[$deviceData['groupId']]; $ridersArrayPreRace[$rider]['groupFinish'] = $deviceData['groupFinish']; } } $groupRidersArray = array(); foreach ($ridersArray as $key => $item) { $groupRidersArray[$item['groupId']][$key] = $item; } ksort($groupRidersArray, SORT_NUMERIC); // Splits riders into subarrays based on group ID $arr_counter = 1; foreach ($groupRidersArray as $ridersArray) { $finishers = []; $notFinishers = []; foreach ($ridersArray as $rider) { if (!empty($rider['finishTime'])) { $finishers[$rider['riderNameClean']] = $rider; } else { $notFinishers[$rider['riderNameClean']] = $rider; } } // sort all finishers on finish time $sortArray = array(); foreach ($finishers as $rider) { foreach ($rider as $key => $value) { if (!isset($sortArray[$key])) { $sortArray[$key] = array(); } $sortArray[$key][] = $value; } //$orderby = "finishTime"; //@array_multisort($sortArray[$orderby], SORT_ASC, $finishers); $orderby = $config_sortFinished; if($config_sortFinishedOrder == "SORT_DESC") { @array_multisort($sortArray[$orderby], SORT_DESC, $finishers); } else { @array_multisort($sortArray[$orderby], SORT_ASC, $finishers); } } if ($config_sortActive == 'checkpointCount') { foreach ($notFinishers as $key => $value) { if( empty($value['cpLastHitUnix']) ) { $notFinishers['riderNameClean']['cpLastHitUnix'] = $value['teamNumber']; // if no cp hit, keep sorting on team number $notFinishers['riderNameClean']['cpCounter'] = 0; $notFinishers['riderNameClean']['cpLastCP'] = ""; } $cp_count[$key] = $value['cpCounter']; $cp_lastFix[$key] = $value['cpLastHitUnix']; array_multisort($cp_count, SORT_DESC, $cp_lastFix, SORT_ASC, $notFinishers); // sort on amount of cp checks and then on unix fix time. } } else { $sortArray = array(); foreach ($notFinishers as $rider) { foreach ($rider as $key => $value) { // add all keys for sorting, is that really necessari ??? time load? ?? if (!isset($sortArray[$key])) { $sortArray[$key] = array(); } $sortArray[$key][] = $value; } $orderby = "cpCounter"; $orderby = $config_sortActive; if($config_sortActiveOrder == "SORT_DESC") { @array_multisort($sortArray[$orderby], SORT_DESC, $notFinishers); } else { @array_multisort($sortArray[$orderby], SORT_ASC, $notFinishers); } array_multisort($sortArray['cpCounter'], SORT_ASC, $sortArray['cpLastHitUnix'], SORT_DESC, $notFinishers); } } $ridersArray = array_merge($finishers, $notFinishers); foreach ($ridersArray as $rider) { if($rider['scratched'] == '2') { unset($ridersArray[ $rider['riderNameClean']]); // Remove crew rider $ridersArray[ $rider['riderNameClean']] = $rider; // re-add crew rider to array } } foreach ($ridersArray as $rider) { if($rider['scratched'] == '4') { unset($ridersArray[ $rider['riderNameClean']]); // Remove crew rider $ridersArray[ $rider['riderNameClean']] = $rider; // re-add crew rider to array } $arr_counter = $rider['groupId']; // HACK RADX } $groupRidersArray[$arr_counter] = $ridersArray; // put updated array back in group array $arr_counter++; } // Sort Pre Race riders to team number ascending $sortArray = array(); foreach ($ridersArrayPreRace as $rider) { foreach ($rider as $key => $value) { if (!isset($sortArray[$key])) { $sortArray[$key] = array(); } $sortArray[$key][] = $value; } } //$orderby = "teamNumber"; //@array_multisort($sortArray[$orderby], SORT_ASC, $ridersArrayPreRace); $orderby = $config_sortPreRace; if($config_sortPreRaceOrder == "SORT_DESC") { @array_multisort($sortArray[$orderby], SORT_DESC, $ridersArrayPreRace); } else { @array_multisort($sortArray[$orderby], SORT_ASC, $ridersArrayPreRace); } $leaderArray = array_shift(array_values($groupRidersArray[1])); // hack $leaderPosition = "" . $leaderArray['longitude'] . "," . $leaderArray['latitude'] . ""; foreach ($groupRidersArray as $ridersArray) { $i = 1; $iPos = 0; foreach ($ridersArray as $rider) { $ridersArray[$rider['riderNameClean']]['position'] = $i; $ends = array( 'th', 'st', 'nd', 'rd', 'th', 'th', 'th', 'th', 'th', 'th', ); $arr_counter = $rider['groupId']; // HACK RADX if (($i % 100) >= 11 && ($i % 100) <= 13) { $ridersArray[$rider['riderNameClean']]['positionAbbrev'] = $i . 'th'; } else { $ridersArray[$rider['riderNameClean']]['positionAbbrev'] = $i . $ends[$i % 10]; } $ridersArrayRebuild[] = $ridersArray[$rider['riderNameClean']]; $groupRidersArray[$arr_counter] = $ridersArray; // Put updated array back in the group array. $i++; $iPos++; } } } foreach ($ridersArrayRebuild as $value) { $ridersArrayRebuildNew[$value['riderNameClean']] = $value; } $ridersArray = $ridersArrayRebuildNew; // Rebuild the original riderArray again. // get the leader array to be used on the replay iframe include $riderKeys = array_keys($ridersArray); $groupPreRaceRidersArray = array(); foreach ($ridersArrayPreRace as $key => $item) { $groupPreRaceRidersArray[$item['groupId']][$key] = $item; } ksort($groupPreRaceRidersArray, SORT_NUMERIC); ?>