Category: Uncategorised
Written by x9494r
// INIT 7.15.21
// GET PARENT ID -----------------------------------------------------------------------------------------
$sheetID = '{top_records_events___sheet_id}';
// GET SELECTED EVENT ------------------------------------------------------------------------------------
$selectedEvent = '{top_records_events___selected_event}';
$selectedEventRaw = "," . $selectedEvent ; // add delimiter so we can check for partial strings (example: 5 vs 15)
// GET PAST EVENT PARAM ----------------------------------------------------------------------------------
$pastEventParams = '{top_records_events___event_params}';
$pastEventParams = strip_tags($pastEventParams);
// GET PAST LOG ------------------------------------------------------------------------------------------
$pastLog = '{top_records_events___log}';
// GET CURRENT TRACK -------------------------------------------------------------------------------------
$drillID = '{top_records_events___project_rounds_id}';
$db = FabrikWorker::getDbo(false, 2);
$query = $db->getQuery(true);
$query->SELECT('location');
$query->FROM('world_tracks_projects_rounds');
$query->WHERE ('id= ' . $db->quote($drillID));
$db->setQuery($query);
$currentTrack = $db->loadResult();
// CREATE NEW LOG ----------------------------------------------------------------------------------------
// get admin
$user = JFactory::getUser();
$adminID = $user->id;
$adminName = $user->name;
$adminUsername = $user->username;
$thisAdmin = $adminUsername;
// get mod date
date_default_timezone_set('New York');
$thisDate = date('n/j/Y@g:ia', time());
// get new log addition
$updatedLog = "[" . $thisAdmin . "]" . $thisDate . ":" . $selectedEvent . ", " . $pastLog;
// CHECK TO SEE IF SELECTED EVENT IS ALREADY IN EVENT PARAMS ---------------------------------------------
if (strpos($pastEventParams, $selectedEventRaw) !== false) {
// event is already in event params, no need to add it again
} else {
// add selected event to event params
// check if past event params is empty
if ($pastEventParams == "") {
// just add current params
$updatedEventParams = $selectedEvent;
} else {
// combine past with present
$updatedEventParams = $pastEventParams . "," . $selectedEvent;
}
// UPDATE EVENT PARAMS -----------------------------------------------
// write to log in top records generator
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->update('top_records_generator')
->set('event_params = ' . $myDb->quote($updatedEventParams))
->where('sheet_id = ' . $myDb->quote($sheetID));
$myDb->setQuery($myQuery);
$myDb->execute();
} // end if pastEventParams
// UPDATE LOG --------------------------------------------------------------------------------------------
// write to log in top records generator
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->update('top_records_generator')
->set('event_log = ' . $myDb->quote($updatedLog))
->where('sheet_id = ' . $myDb->quote($sheetID));
$myDb->setQuery($myQuery);
$myDb->execute();
// =========================================================================================
// OUTPUT RECORDS
// =========================================================================================
// INIT VARS
$sheetID = '{top_records_events___sheet_id}';
$thisProjectRoundID = '{top_records_events___project_rounds_id}';
$thisSeasonID = '{top_records_events___season_id}';
// GET SELECTED EVENT-RECORD TYPE
$selectedEventRecordType = '{top_records_events___selected_event}'; // format=> "1-All-Time"
$eventRecordArr = explode('-', $selectedEventRecordType);
// parse selected event record type
$recordEventID = $eventRecordArr[0];
$recordType = $eventRecordArr[1];
if ($recordType == "All") {
$recordType = "All-Time";
}
// GET RUNNING ORDER OF TEAMS IN THIS DRILL -----------------------------------
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->select('team, mro_num, team_id')
->from('fm_running_orders')
->where('projectround_id = ' . $myDb->quote($thisProjectRoundID));
$myQuery->order('mro_num asc');
$myDb->setQuery($myQuery);
$teamsInDrill = $myDb->loadObjectList();
// UPDATE TEAM LIST (used for checking for post-entries) --------------------------------------------------------------------------------------------
// write to team_list in top records generator
$teamList = "";
foreach ($teamsInDrill as $team)
{
$teamList .= $team->team_id . "," ;
}
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->update('top_records_generator')
->set('team_list = ' . $myDb->quote($teamList))
->where('sheet_id = ' . $myDb->quote($sheetID));
$myDb->setQuery($myQuery);
$myDb->execute();
// --------------------------------------------------------
// DELETE PREVIOUS RECORDS OF SAME SHEET ID, EVENT and RECORD TYPE
// --------------------------------------------------------
$db = JFactory::getDBO();
$q='DELETE FROM fm_top_records WHERE sheet_id = ' .$db->Quote($sheetID) .
'AND record_event = ' . $db->Quote($recordEventID) .
'AND record_type = ' . $db->Quote($recordType) ;
$db->setQuery($q);
$db->query();
// Get RECORDS FOR EACH TEAM ------------------------------------------------------------------------
foreach ($teamsInDrill as $team) { // ===============================================================
$thisTeamID = $team->team_id;
// ---------------------------------------------------------------------------------------------------
// GET ALL RESULTS BASED ON RECORD TYPE
if ($recordType == "Season") {
// GET TOP TIMES THIS SEASON ================================ SEASON ==================================
$db = FabrikWorker::getDbo(false, 2);
$query = $db->getQuery(true);
$query->SELECT('results.time,teams.last_name,projrounds.start_date_field,projrounds.location');
$query->FROM('fm_event_results AS results');
$query->WHERE ('seasons.id = ' . $db->quote($thisSeasonID) .
'and etypes.id= ' . $db->quote($recordEventID) .
'and teams.id= ' . $db->quote($thisTeamID) .
'and results.time <> ' . $db->quote('') . // don't include blank entries
'and results.time*1 != ' . $db->quote(0)); // don't include 0 times
$query->order('time*1 asc');
$query->setLimit('5');
$query->JOIN('LEFT','world_tracks_events AS events ON (results.event_id = events.id)');
$query->JOIN('LEFT','world_tracks_projects_rounds AS projrounds ON (events.projectround_id = projrounds.id)');
$query->JOIN('LEFT','world_tracks_projects AS projects ON (projrounds.project_id = projects.id)');
$query->JOIN('LEFT','world_tracks_seasons AS seasons ON (projects.season_id = seasons.id)');
$query->JOIN('LEFT','world_tracks_rounds AS rounds ON (projrounds.round_id = rounds.id)');
$query->JOIN('LEFT','world_tracks_eventtypes AS etypes ON (events.type = etypes.id)');
$query->JOIN('LEFT','world_tracks_individuals AS teams ON (results.individual_id = teams.id)');
$db->setQuery($query);
$recordsBest = $db->loadObjectList();
} elseif ($recordType == "Track") {
// GET OLD TRACK-NAMING CONVENTION
$currentTrackDEP = str_replace(" Track", "", $currentTrack);
// GET TOP TIMES AT THIS TRACK =================================== TRACK ==============================
$db = FabrikWorker::getDbo(false, 2);
$query = $db->getQuery(true);
$query->SELECT('results.time,teams.last_name,projrounds.start_date_field,projrounds.location');
$query->FROM('fm_event_results AS results');
$query->WHERE ('etypes.id= ' . $db->quote($recordEventID) .
'and projrounds.location= ' . $db->quote($currentTrack) .
'and teams.id= ' . $db->quote($thisTeamID) .
'and results.time <> ' . $db->quote('') .
'and results.time*1 != ' . $db->quote(0) .
'OR etypes.id= ' . $db->quote($recordEventID) .
'and projrounds.location= ' . $db->quote($currentTrackDEP) .
'and teams.id= ' . $db->quote($thisTeamID) .
'and results.time <> ' . $db->quote('') .
'and results.time*1 != ' . $db->quote(0));
$query->order('time*1 asc');
$query->setLimit('5');
$query->JOIN('LEFT','world_tracks_events AS events ON (results.event_id = events.id)');
$query->JOIN('LEFT','world_tracks_projects_rounds AS projrounds ON (events.projectround_id = projrounds.id)');
$query->JOIN('LEFT','world_tracks_projects AS projects ON (projrounds.project_id = projects.id)');
$query->JOIN('LEFT','world_tracks_seasons AS seasons ON (projects.season_id = seasons.id)');
$query->JOIN('LEFT','world_tracks_rounds AS rounds ON (projrounds.round_id = rounds.id)');
$query->JOIN('LEFT','world_tracks_eventtypes AS etypes ON (events.type = etypes.id)');
$query->JOIN('LEFT','world_tracks_individuals AS teams ON (results.individual_id = teams.id)');
$db->setQuery($query);
$recordsBest = $db->loadObjectList();
} elseif ($recordType == "All-Time") {
// GET TOP TIMES OF ALL-TIME ===================================== ALL-TIME ==============================
$db = FabrikWorker::getDbo(false, 2);
$query = $db->getQuery(true);
$query->SELECT('results.time,teams.last_name,projrounds.start_date_field,projrounds.location');
$query->FROM('fm_event_results AS results');
$query->WHERE ('etypes.id= ' . $db->quote($recordEventID) .
'and teams.id= ' . $db->quote($thisTeamID) .
'and results.time <> ' . $db->quote('') . // don't include blank entries
'and results.time*1 != ' . $db->quote(0)); // don't include 0 times
$query->order('time*1 asc');
$query->setLimit('5');
$query->JOIN('LEFT','world_tracks_events AS events ON (results.event_id = events.id)');
$query->JOIN('LEFT','world_tracks_projects_rounds AS projrounds ON (events.projectround_id = projrounds.id)');
$query->JOIN('LEFT','world_tracks_projects AS projects ON (projrounds.project_id = projects.id)');
$query->JOIN('LEFT','world_tracks_seasons AS seasons ON (projects.season_id = seasons.id)');
$query->JOIN('LEFT','world_tracks_rounds AS rounds ON (projrounds.round_id = rounds.id)');
$query->JOIN('LEFT','world_tracks_eventtypes AS etypes ON (events.type = etypes.id)');
$query->JOIN('LEFT','world_tracks_individuals AS teams ON (results.individual_id = teams.id)');
$db->setQuery($query);
$recordsBest = $db->loadObjectList();
} // end if recordType
// --------------------------------------------------------
// --------------------------------------------------------
// PROCESS RECORDS FOR EACH TEAM
// --------------------------------------------------------
$recordRank = 0;
foreach ($recordsBest as $best) {
$recordTeam = $best->last_name;
$recordTeamID = $best->individual_id;
$recordTime = $best->time;
$recordDate = $best->start_date_field; // date may be in multiple different formats
$recordTrack = $best->location;
$recordRank++;
// get exact date and time for tracking error checking
$currentDateTime = date('Y-m-d H:i:s') . "/" . $recordType . "/" . $recordEventID;
// START INSERT / WRITE *********************************
$db = JFactory::getDBO();
$insertQuery = "INSERT INTO fm_top_records (projects_rounds_id,record_event,record_type,record_rank,record_team,record_time,record_date,record_track,sheet_id,rounds_id)
VALUES ('$thisProjectRoundID','$recordEventID','$recordType','$recordRank','$recordTeam','$recordTime','$recordDate','$recordTrack','$sheetID','$currentDateTime')"; // borrowing rounds_id to track time, errors, double insertions etc
$db->setQuery($insertQuery);
$db->query();
// END INSERT / WRITE ***********************************
} // endforeach recordsBest
unset($recordsBest);
// --------------------------------------------------------
} // end foreach teamsInDrill
unset($teamsInDrill);
// --------------------------------------------------------
// INIT 7.3.21
// GET PARENT ID -----------------------------------------------------------------------------------------
$sheetID = '{top_records_events___sheet_id}';
// GET SELECTED EVENT ------------------------------------------------------------------------------------
$selectedEvent = '{top_records_events___selected_event}';
$selectedEventRaw = "," . $selectedEvent ; // add delimiter so we can check for partial strings (example: 5 vs 15)
// GET PAST EVENT PARAM ----------------------------------------------------------------------------------
$pastEventParams = '{top_records_events___event_params}';
$pastEventParams = strip_tags($pastEventParams);
// GET PAST LOG ------------------------------------------------------------------------------------------
$pastLog = '{top_records_events___log}';
// GET CURRENT TRACK -------------------------------------------------------------------------------------
$drillID = '{top_records_events___project_rounds_id}';
$db = FabrikWorker::getDbo(false, 2);
$query = $db->getQuery(true);
$query->SELECT('location');
$query->FROM('world_tracks_projects_rounds');
$query->WHERE ('id= ' . $db->quote($drillID));
$db->setQuery($query);
$currentTrack = $db->loadResult();
// CREATE NEW LOG ----------------------------------------------------------------------------------------
// get admin
$user = JFactory::getUser();
$adminID = $user->id;
$adminName = $user->name;
$adminUsername = $user->username;
$thisAdmin = $adminUsername;
// get mod date
date_default_timezone_set('New York');
$thisDate = date('n/j/Y@g:ia', time());
// get new log addition
$updatedLog = "[" . $thisAdmin . "]" . $thisDate . ":" . $selectedEvent . ", " . $pastLog;
// CHECK TO SEE IF SELECTED EVENT IS ALREADY IN EVENT PARAMS ---------------------------------------------
if (strpos($pastEventParams, $selectedEventRaw) !== false) {
// event is already in event params, no need to add it again
} else {
// add selected event to event params
// check if past event params is empty
if ($pastEventParams == "") {
// just add current params
$updatedEventParams = $selectedEvent;
} else {
// combine past with present
$updatedEventParams = $pastEventParams . "," . $selectedEvent;
}
// UPDATE EVENT PARAMS -----------------------------------------------
// write to log in top records generator
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->update('top_records_generator')
->set('event_params = ' . $myDb->quote($updatedEventParams))
->where('sheet_id = ' . $myDb->quote($sheetID));
$myDb->setQuery($myQuery);
$myDb->execute();
} // end if pastEventParams
// UPDATE LOG --------------------------------------------------------------------------------------------
// write to log in top records generator
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->update('top_records_generator')
->set('event_log = ' . $myDb->quote($updatedLog))
->where('sheet_id = ' . $myDb->quote($sheetID));
$myDb->setQuery($myQuery);
$myDb->execute();
// =========================================================================================
// OUTPUT RECORDS
// =========================================================================================
// INIT VARS
$sheetID = '{top_records_events___sheet_id}';
$thisProjectRoundID = '{top_records_events___project_rounds_id}';
$thisSeasonID = '{top_records_events___season_id}';
// GET SELECTED EVENT-RECORD TYPE
$selectedEventRecordType = '{top_records_events___selected_event}'; // format=> "1-All-Time"
$eventRecordArr = explode('-', $selectedEventRecordType);
// parse selected event record type
$recordEventID = $eventRecordArr[0];
$recordType = $eventRecordArr[1];
if ($recordType == "All") {
$recordType = "All-Time";
}
// GET RUNNING ORDER OF TEAMS IN THIS DRILL -----------------------------------
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->select('team, mro_num, team_id')
->from('fm_running_orders')
->where('projectround_id = ' . $myDb->quote($thisProjectRoundID));
$myQuery->order('mro_num asc');
$myDb->setQuery($myQuery);
$teamsInDrill = $myDb->loadObjectList();
// --------------------------------------------------------
// DELETE PREVIOUS RECORDS OF SAME SHEET ID, EVENT and RECORD TYPE
// --------------------------------------------------------
$db = JFactory::getDBO();
$q='DELETE FROM fm_top_records WHERE sheet_id = ' .$db->Quote($sheetID) .
'AND record_event = ' . $db->Quote($recordEventID) .
'AND record_type = ' . $db->Quote($recordType) ;
$db->setQuery($q);
$db->query();
// Get RECORDS FOR EACH TEAM ------------------------------------------------------------------------
foreach ($teamsInDrill as $team) { // ===============================================================
$thisTeamID = $team->team_id;
// ---------------------------------------------------------------------------------------------------
// GET ALL RESULTS BASED ON RECORD TYPE
if ($recordType == "Season") {
// GET TOP TIMES THIS SEASON ================================ SEASON ==================================
$db = FabrikWorker::getDbo(false, 2);
$query = $db->getQuery(true);
$query->SELECT('results.time,teams.last_name,projrounds.start_date_field,projrounds.location');
$query->FROM('fm_event_results AS results');
$query->WHERE ('seasons.id = ' . $db->quote($thisSeasonID) .
'and etypes.id= ' . $db->quote($recordEventID) .
'and teams.id= ' . $db->quote($thisTeamID) .
'and results.time <> ' . $db->quote('') . // don't include blank entries
'and results.time*1 != ' . $db->quote(0)); // don't include 0 times
$query->order('time*1 asc');
$query->setLimit('5');
$query->JOIN('LEFT','world_tracks_events AS events ON (results.event_id = events.id)');
$query->JOIN('LEFT','world_tracks_projects_rounds AS projrounds ON (events.projectround_id = projrounds.id)');
$query->JOIN('LEFT','world_tracks_projects AS projects ON (projrounds.project_id = projects.id)');
$query->JOIN('LEFT','world_tracks_seasons AS seasons ON (projects.season_id = seasons.id)');
$query->JOIN('LEFT','world_tracks_rounds AS rounds ON (projrounds.round_id = rounds.id)');
$query->JOIN('LEFT','world_tracks_eventtypes AS etypes ON (events.type = etypes.id)');
$query->JOIN('LEFT','world_tracks_individuals AS teams ON (results.individual_id = teams.id)');
$db->setQuery($query);
$recordsBest = $db->loadObjectList();
} elseif ($recordType == "Track") {
// GET TOP TIMES AT THIS TRACK =================================== TRACK ==============================
$db = FabrikWorker::getDbo(false, 2);
$query = $db->getQuery(true);
$query->SELECT('results.time,teams.last_name,projrounds.start_date_field,projrounds.location');
$query->FROM('fm_event_results AS results');
$query->WHERE ('etypes.id= ' . $db->quote($recordEventID) .
'and projrounds.location= ' . $db->quote($currentTrack) .
'and teams.id= ' . $db->quote($thisTeamID) .
'and results.time <> ' . $db->quote('') . // don't include blank entries
'and results.time*1 != ' . $db->quote(0)); // don't include 0 times
$query->order('time*1 asc');
$query->setLimit('5');
$query->JOIN('LEFT','world_tracks_events AS events ON (results.event_id = events.id)');
$query->JOIN('LEFT','world_tracks_projects_rounds AS projrounds ON (events.projectround_id = projrounds.id)');
$query->JOIN('LEFT','world_tracks_projects AS projects ON (projrounds.project_id = projects.id)');
$query->JOIN('LEFT','world_tracks_seasons AS seasons ON (projects.season_id = seasons.id)');
$query->JOIN('LEFT','world_tracks_rounds AS rounds ON (projrounds.round_id = rounds.id)');
$query->JOIN('LEFT','world_tracks_eventtypes AS etypes ON (events.type = etypes.id)');
$query->JOIN('LEFT','world_tracks_individuals AS teams ON (results.individual_id = teams.id)');
$db->setQuery($query);
$recordsBest = $db->loadObjectList();
} elseif ($recordType == "All-Time") {
// GET TOP TIMES OF ALL-TIME ===================================== ALL-TIME ==============================
$db = FabrikWorker::getDbo(false, 2);
$query = $db->getQuery(true);
$query->SELECT('results.time,teams.last_name,projrounds.start_date_field,projrounds.location');
$query->FROM('fm_event_results AS results');
$query->WHERE ('etypes.id= ' . $db->quote($recordEventID) .
'and teams.id= ' . $db->quote($thisTeamID) .
'and results.time <> ' . $db->quote('') . // don't include blank entries
'and results.time*1 != ' . $db->quote(0)); // don't include 0 times
$query->order('time*1 asc');
$query->setLimit('5');
$query->JOIN('LEFT','world_tracks_events AS events ON (results.event_id = events.id)');
$query->JOIN('LEFT','world_tracks_projects_rounds AS projrounds ON (events.projectround_id = projrounds.id)');
$query->JOIN('LEFT','world_tracks_projects AS projects ON (projrounds.project_id = projects.id)');
$query->JOIN('LEFT','world_tracks_seasons AS seasons ON (projects.season_id = seasons.id)');
$query->JOIN('LEFT','world_tracks_rounds AS rounds ON (projrounds.round_id = rounds.id)');
$query->JOIN('LEFT','world_tracks_eventtypes AS etypes ON (events.type = etypes.id)');
$query->JOIN('LEFT','world_tracks_individuals AS teams ON (results.individual_id = teams.id)');
$db->setQuery($query);
$recordsBest = $db->loadObjectList();
} // end if recordType
// --------------------------------------------------------
// --------------------------------------------------------
// PROCESS RECORDS FOR EACH TEAM
// --------------------------------------------------------
$recordRank = 0;
foreach ($recordsBest as $best) {
$recordTeam = $best->last_name;
$recordTeamID = $best->individual_id;
$recordTime = $best->time;
$recordDate = $best->start_date_field; // date may be in multiple different formats
$recordTrack = $best->location;
$recordRank++;
// get exact date and time for tracking error checking
$currentDateTime = date('Y-m-d H:i:s') . "/" . $recordType . "/" . $recordEventID;
// START INSERT / WRITE *********************************
$db = JFactory::getDBO();
$insertQuery = "INSERT INTO fm_top_records (projects_rounds_id,record_event,record_type,record_rank,record_team,record_time,record_date,record_track,sheet_id,rounds_id)
VALUES ('$thisProjectRoundID','$recordEventID','$recordType','$recordRank','$recordTeam','$recordTime','$recordDate','$recordTrack','$sheetID','$currentDateTime')"; // borrowing rounds_id to track time, errors, double insertions etc
$db->setQuery($insertQuery);
$db->query();
// END INSERT / WRITE ***********************************
} // endforeach recordsBest
unset($recordsBest);
// --------------------------------------------------------
} // end foreach teamsInDrill
unset($teamsInDrill);
// --------------------------------------------------------