function _buildCompetitorsArray() { $competitorTable = CmaticSchema::getTypeDbTable('competitor'); $sexTable = CmaticSchema::getTypeDbTable('sex'); $divisionTable = CmaticSchema::getTypeDbTable('division'); $scoringTable = CmaticSchema::getTypeDbTable('scoring'); $groupTable = CmaticSchema::getTypeDbTable('group'); $groupMemberTable = CmaticSchema::getTypeDbTable('groupMember'); $competitorQuery = 'select' . ' c.competitor_id, c.last_name, c.first_name, c.age, c.weight, c.amount_paid,' . ' c.email, c.phone_1, c.phone_2, c.street_address, c.city, c.state, c.postal_code,' . ' c.country, c.school, c.coach, c.emergency_contact_name, c.emergency_contact_relation, c.emergency_contact_phone,' . ' s.long_name as sex, d.long_name as division' . " from {$competitorTable} c, {$sexTable} s, {$divisionTable} d" . ' where c.sex_id = s.sex_id and c.division_id = d.division_id'; $conn = PdoHelper::getPdo(); $r = $conn->query($competitorQuery); $competitorResultSet = $r->fetchAll(PDO::FETCH_ASSOC); $ret = array(); // set competitor info foreach ($competitorResultSet as $row) { $ret[$row['competitor_id']] = array('last_name' => $row['last_name'], 'first_name' => $row['first_name'], 'sex' => $row['sex'], 'age' => _convertAgeToAgeGroup($row['age']), 'division' => $row['division'], 'weight' => $row['weight'], 'amount_paid' => $row['amount_paid'], 'email' => $row['email'], 'phone_1' => $row['phone_1'], 'phone_2' => $row['phone_2'], 'street_address' => $row['street_address'], 'city' => $row['city'], 'state' => $row['state'], 'zip' => $row['postal_code'], 'country' => $row['country'], 'school' => $row['school'], 'instructor' => $row['coach'], 'emergency_contact_name' => $row['emergency_contact_name'], 'emergency_contact_relation' => $row['emergency_contact_relation'], 'emergency_contact_phone' => $row['emergency_contact_phone'], 'individual_events' => array(), 'group_events' => array()); } // individual and group performances $eventQuery = "select event_id, competitor_id, group_id from {$scoringTable} order by competitor_id"; $r = $conn->query($eventQuery); $eventResultSet = $r->fetchAll(PDO::FETCH_ASSOC); // groups $groupQuery = "select group_id, name from {$groupTable}"; $r = $conn->query($groupQuery); $groupResultSet = $r->fetchAll(PDO::FETCH_ASSOC); // group members $groupMemberQuery = "select group_id, competitor_id from {$groupMemberTable} order by group_id"; $r = $conn->query($groupMemberQuery); $groupMemberResultSet = $r->fetchAll(PDO::FETCH_ASSOC); // build group->name map $groupNameMap = array(); foreach ($groupResultSet as $row) { $groupNameMap[$row['group_id']] = $row['name']; } // build group->member map $groupMemberMap = array(); foreach ($groupMemberResultSet as $row) { if (!$groupMemberMap[$row['group_id']]) { $groupMemberMap[$row['group_id']] = array(); } $groupMemberMap[$row['group_id']][] = $row['competitor_id']; } // add events foreach ($eventResultSet as $row) { if ($row['competitor_id']) { // individual event $ret[$row['competitor_id']]['individual_events'][] = $row['event_id']; } else { if ($row['group_id']) { // group event if ($groupMemberMap[$row['group_id']]) { foreach ($groupMemberMap[$row['group_id']] as $competitorId) { $ret[$competitorId]['group_events'][strval($row['event_id'])] = $groupNameMap[$row['group_id']]; } } } } } return $ret; }
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <?php require_once '../util/Db.php'; require_once '../util/Ex.php'; require_once '../util/TextUtils.php'; $eventTable = CmaticSchema::getTypeDbTable('event'); $formTable = CmaticSchema::getTypeDbTable('form'); // Get data $conn = PdoHelper::getPdo(); $r = $conn->query("select e.event_code, e.num_competitors, e.ring_id, e.ring_order, f.long_name as form_name" . " from {$eventTable} e, {$formTable} f" . " where e.form_id = f.form_id" . " and is_finished = false" . " order by ring_id, ring_order"); $eventsRs = $r->fetchAll(PDO::FETCH_ASSOC); $conn = null; // Organize information $schedule = array(); foreach ($eventsRs as $row) { $ringId = $row['ring_id']; if (!isset($schedule[$ringId])) { $schedule[$ringId] = array(); } array_push($schedule[$ringId], $row); } print <<<EOD <html> <head> <meta http-equiv="refresh" content="30"> <title>Tentative Event Schedule</title> <style type="text/css"> .title { text-align: center; }
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <head> <title>Competitor Info Mailing List</title> </head> <body> <?php // Table with cmatid, competitor full name, url require_once '../util/Db.php'; $conn = PdoHelper::getPdo(); $r = $conn->query('select competitor_id, last_name, first_name, email from ' . CmaticSchema::getTypeDbTable('competitor')); $rs = $r->fetchAll(PDO::FETCH_ASSOC); $conn = null; $LIST = array(); foreach ($rs as $row) { $LIST[] = array('id' => $row['competitor_id'], 'name' => "{$row['last_name']}, {$row['first_name']}", 'email' => $row['email']); } function makeUrl($id) { $hash = str_rot13(md5($id)); return "http://www.ocf.berkeley.edu/~calwushu/cmat16data/competitorInfo.php?id={$id}&hash={$hash}"; } ?> <table> <thead> <tr> <th scope="col">CMAT ID</th> <th scope="col">Competitor Name</th> <th scope="col">Email</th> <th scope="col">URL</th> </tr>
} if (is_null($tmpValue)) { throw new CmaticApiException(sprintf('Record %d: Invalid value for %s.%s: $s', $index, $typeApiName, $fieldApiName, $value)); } $setClause[] = sprintf('%s = %s', $tmpField, $tmpValue); } // TODO: Move last_mod into triggers? $conn->query(sprintf('update %s set last_mod = now(), %s where %s=%s', $typeDbTable, implode(',', $setClause), CmaticSchema::getFieldDbColumn($typeApiName, 'id'), $recordId)); } } else { if ($op == 'delete') { // Disable deletes it's too scary to allow // TODO: There needs to be some checks to make sure not just anything can be deleted. // Specifically, we should NEVER be able to delete scoring rows if they have scores. // Perhaps this should be set up as a trigger? $recordIds = array(); foreach ($records as $index => $record) { $recordIds[] = $record['id']; } $conn->query(sprintf('delete from %s where %s in (%s)', $typeDbTable, CmaticSchema::getFieldDbColumn($typeApiName, 'id'), implode(',', $recordIds))); } } } $conn->commit(); $conn = null; // What should the response be on a successful set-request? if (isset($newId)) { print "{\"success\": \"true\", newId: {$newId}}"; } else { print '{"success": "true"}'; }
<?php /** * This page is entirely hardcoded. It magically knows all of * the things it needs to know about the configuration data. */ require_once '../util/Db.php'; require_once '../util/Ex.php'; require_once '../util/TextUtils.php'; $byDivision = array(); $bySex = array(); $byType = array(); $eventTable = CmaticSchema::getTypeDbTable('event'); $conn = PdoHelper::getPdo(); $r = $conn->query("select num_competitors, division_id, sex_id, age_group_id, form_id" . " from {$eventTable}"); $resultSet = $r->fetchAll(PDO::FETCH_ASSOC); $conn = null; $ret = array(); foreach ($resultSet as $row) { $num = $row['num_competitors']; $age = strval($row['age_group_id']); if (!isset($byDivision[$age])) { $byDivision[$age] = array('1' => 0, '2' => 0, '3' => 0, '4' => 0); } if (!isset($bySex[$age])) { $bySex[$age] = array('1' => 0, '2' => 0, '3' => 0); } if (!isset($byType[$age])) { $byType[$age] = array('t' => 0, 'c' => 0, 'i' => 0); } $byDivision[$age][strval($row['division_id'])] += $num;
{ return "{$last}, {$first}"; } // Get data $scoringTable = CmaticSchema::getTypeDbTable('scoring'); $competitorTable = CmaticSchema::getTypeDbTable('competitor'); $eventTable = CmaticSchema::getTypeDbTable('event'); $divisionTable = CmaticSchema::getTypeDbTable('division'); $ageTable = CmaticSchema::getTypeDbTable('ageGroup'); $sexTable = CmaticSchema::getTypeDbTable('sex'); $formTable = CmaticSchema::getTypeDbTable('form'); $conn = PdoHelper::getPdo(); $r = $conn->query("select s.competitor_id as id, (c.last_name || ', ' || c.first_name) as name from {$scoringTable} s, {$competitorTable} c where s.competitor_id = c.competitor_id and s.event_id = {$_REQUEST['id']} order by performance_order"); $scoringRs = $r->fetchAll(PDO::FETCH_ASSOC); if (0 == count($scoringRs)) { $groupTable = CmaticSchema::getTypeDbTable('group'); $r = $conn->query("select '' as id, g.name as name from {$scoringTable} s, {$groupTable} g where s.group_id = g.group_id and s.event_id = {$_REQUEST['id']} order by performance_order"); $scoringRs = $r->fetchAll(PDO::FETCH_ASSOC); } //TODO: Clean this up $r = $conn->query("select ring_id, event_code, d.long_name as division_name, a.long_name as age_name, s.long_name as sex_name, f.long_name as form_name from {$eventTable} e LEFT JOIN {$divisionTable} d ON (d.division_id=e.division_id) JOIN {$ageTable} a ON (a.age_group_id = e.age_group_id) JOIN {$sexTable} s ON s.sex_id=e.sex_id JOIN {$formTable} f ON f.form_id = e.form_id where event_id = {$_REQUEST['id']}"); $eventRs = $r->fetchAll(PDO::FETCH_ASSOC); $conn = null; $EVENT_CODE_TITLE = 'Ring ' . $eventRs[0]['ring_id'] . ': ' . $eventRs[0]['event_code'] . ' -- <span style="font-size: 18px">' . $eventRs[0]['division_name'] . ' ' . $eventRs[0]['sex_name'] . ' ' . $eventRs[0]['age_name'] . ' ' . $eventRs[0]['form_name'] . '</span>'; // Output print <<<EOD <h1>{$EVENT_CODE_TITLE}</h1> <table border="1" cellpadding="2" cellspacing="2"> <thead> <th>Order</th> <th>Id</th>
<?php /** * Update all of the event codes of all existing events. */ $debug = isset($_REQUEST['debug']); header('Content-type: text/' . ($debug ? 'html' : 'x-json')); require_once '../util/Db.php'; $conn = PdoHelper::getPdo(); $isSuccessful = false; try { $conn->beginTransaction(); CmaticSchema::updateEventCodes($conn, false); $conn->commit(); $isSuccessful = true; } catch (Exception $e) { $conn->rollBack(); $conn = null; } $conn = null; ?> {success: <?php echo $isSuccessful ? 'true' : 'false'; ?> }
/** * Updates all num competitors * * @param $conn {PDO Connection} */ public static function updateNumCompetitors($conn) { // get counts for all events $scoringTable = CmaticSchema::getTypeDbTable('scoring'); $eventTable = CmaticSchema::getTypeDbTable('event'); $r = $conn->query("select event_id, count(*) from {$scoringTable} group by event_id"); $countResultSet = $r->fetchAll(PDO::FETCH_ASSOC); // Reset all counts to 0 $conn->query("update {$eventTable} set num_competitors = 0"); // Set all new counts foreach ($countResultSet as $row) { $conn->query(sprintf("update {$eventTable} set num_competitors = %d where event_id = %d", $row['count'], $row['event_id'])); } }
require_once '../util/Ex.php'; require_once '../util/TextUtils.php'; function makeCmatId($id) { return sprintf('CMAT18%04d', $id); } function makeName($first, $last) { return "{$last}, {$first}"; } $competitorTable = CmaticSchema::getTypeDbTable('competitor'); $scoringTable = CmaticSchema::getTypeDbTable('scoring'); $eventTable = CmaticSchema::getTypeDbTable('event'); $formTable = CmaticSchema::getTypeDbTable('form'); $groupTable = CmaticSchema::getTypeDbTable('group'); $groupMemberTable = CmaticSchema::getTypeDbTable('groupMember'); // Get data $conn = PdoHelper::getPdo(); $r = $conn->query("select competitor_id, first_name, last_name from {$competitorTable}"); $competitorRs = $r->fetchAll(PDO::FETCH_ASSOC); $r = $conn->query("select s.competitor_id as competitor_id, e.event_code as code, f.long_name as form_name" . " from {$scoringTable} s, {$eventTable} e, {$formTable} f" . " where s.event_id = e.event_id" . " and e.form_id = f.form_id" . " and s.competitor_id is not null"); $individualRs = $r->fetchAll(PDO::FETCH_ASSOC); $r = $conn->query("select g.name as group_name, gm.competitor_id as competitor_id" . " from {$groupTable} g, {$groupMemberTable} gm" . " where g.group_id = gm.group_id"); $groupRs = $r->fetchAll(PDO::FETCH_ASSOC); $conn = null; // Organize information $competitorIds = array(); $competitorName = array(); foreach ($competitorRs as $row) { $cmatId = makeCmatId($row['competitor_id']); $competitorIds[] = $cmatId;
<?php /** * Update all of the number of competitors of all existing events. */ $debug = isset($_REQUEST['debug']); header('Content-type: text/' . ($debug ? 'html' : 'x-json')); require_once '../util/Db.php'; $conn = PdoHelper::getPdo(); $isSuccessful = false; try { $conn->beginTransaction(); CmaticSchema::updateNumCompetitors($conn); $conn->commit(); $isSuccessful = true; } catch (Exception $e) { $conn->rollBack(); $conn = null; } $conn = null; ?> {success: <?php echo $isSuccessful ? 'true' : 'false'; ?> }
} } } } // Do the mass update if there is something to do if (!empty($newEvents)) { $conn->beginTransaction(); try { foreach ($newEvents as $e) { // TODO: Consider using binds instead $conn->query(sprintf('insert into %s (division_id, sex_id, age_group_id, form_id) values(%d, %d, %d, %d)', $eventDbTable, $e['d'], $e['s'], $e['a'], $e['f'])); } // Update all event codes that are NULL // This will include all the newly added ones and any that were // somehow missed before. if (!CmaticSchema::updateEventCodes($conn, true)) { // TODO: Throw cmatic exception about the failed update throw new CmaticApiException('Arg, update failed'); } $conn->commit(); // If we got passed the commit, it's all good, report success $isSuccessful = true; } catch (Exception $e) { $conn->rollBack(); // This should be in a "finally" block, but PHP doesn't have that $conn = null; throw $e; } } else { // Got here because we had nothing to do. // We'll consider that a success.
function getCompetitorId() { $clauses = array(); $cols = $this->getCompetitorColumns(); foreach (array('first_name', 'last_name', 'sex_id', 'age', 'division_id') as $col) { $clauses[] = sprintf('%s = %s', $col, $cols[$col]); } $sql = sprintf('select competitor_id from %s where %s', CmaticSchema::getTypeDbTable('competitor'), implode(' and ', $clauses)); $conn = PdoHelper::GetPdo(); $results = $conn->query($sql)->fetchAll(PDO::FETCH_ASSOC); switch (count($results)) { case 0: return null; case 1: return $results[0]['competitor_id']; default: throw new Exception('Multiple possible competitor IDs'); } }
} require_once '../util/Db.php'; require_once '../util/Ex.php'; require_once '../util/TextUtils.php'; $requestParams = TextUtils::undoMagicQuotes($_REQUEST); $type = $requestParams['type']; $table = CmaticSchema::getTypeDbTable($type); if (is_null($table)) { // TODO: Should probably catch this throw new CmaticApiException('Unrecognized type: ' . $requestParams['type']); } // Build out the api field names to return $fieldSelection = array(); foreach (CmaticSchema::getAllFieldsForType($type) as $apiField => $dbColumn) { // Quotes to maintain case $fieldSelection[] = $dbColumn . ' AS "' . $apiField . '"'; } // To filter by a specific FK field, both of these parameters // must be specified. $filterField = $requestParams['filterField']; $filterValue = $requestParams['filterValue']; $filterClause = ''; if (!is_null($filterField) && !is_null($filterValue)) { $filterClause = sprintf(' where %s = %d', CmaticSchema::getFieldDbColumn($type, $filterField), $filterValue); } $conn = PdoHelper::getPdo(); $r = $conn->query(sprintf('select %s from %s%s', implode(', ', $fieldSelection), $table, $filterClause)); $ret = array(); $ret['records'] = $r->fetchAll(PDO::FETCH_ASSOC); $conn = null; echo json_encode($ret);