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;
}
public static function getPdo()
{
global $CMATIC;
$conf = $CMATIC['conf'];
$pdo = new PDO(PdoHelper::getPgsqlDsn($conf['db']['host'], $conf['db']['port'], $conf['db']['db']), $conf['db']['user'], $conf['db']['password']);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
}
$errorMessages[] = 'The table prefix must start with a letter and may only contain letters, numbers, and underscores';
}
}
if (count($errorMessages) > 0) {
throw new CmaticInstallerException($errorMessages);
}
try {
// TODO: Probably should refactor this logic out into a function
// Create the schema in the DB
// Open the file, replace the table prefix, strip comments
$schemaFile = 'schema/postgres8.sql';
$sql = fread(fopen($schemaFile, 'r'), filesize($schemaFile));
$sql = preg_replace('/cmatic_/', $requestParams['tablePrefix'], $sql);
// Strip away inline comments beginning with "--" or "//"
$sql = PdoHelper::removeComments($sql);
$db = new PDO(PdoHelper::getPgsqlDsn($requestParams['host'], $requestParams['port'], $requestParams['db']), $requestParams['user'], $requestParams['password']);
// The squeaky wheel gets the oil, make it known when things went wrong.
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->beginTransaction();
try {
foreach ($sql as $singleSql) {
$singleSql = trim($singleSql);
if (!empty($singleSql)) {
$db->exec($singleSql);
}
}
$db->commit();
// This should be in a "finally" block, but PHP doesn't have such a thing?!
$db = null;
} catch (Exception $e) {
// Roll back the transaction and then re-throw the exception
<!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;
}
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');
}
}
