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);
