/**
* Get setting
*
* @param string $id
* @return string value
*/
public static function get($id)
{
$q = (new SelectQuery(Database::getConnection()))->from("settings")->where("id = ?", $id)->limit(1)->fields("value");
$stmt = $q->prepare();
$stmt->execute();
return $stmt->fetchColumn();
}
public static function get($id = null, $type = null, $leagueId = null, $includeRestricted = false)
{
$q = (new SelectQuery(Database::getConnection()))->from("downloads")->fields(["id", "type", "extension", "title", "description", "league_id", "restricted"]);
if ($id) {
$q->where("id = ?", $id);
}
if ($type) {
$q->where("type = ?", $type);
}
if ($leagueId) {
$q->where("league_id = ?" . $leagueId);
}
if (!$includeRestricted) {
$q->where("restricted = 0");
}
$stmt = $q->prepare();
$stmt->execute();
$dls = [];
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$dl = new self();
list($dl->id, $dl->type, $dl->extension, $dl->title, $dl->description, $dl->leagueId, $dl->restricted) = $row;
$dls[] = $dl;
}
return $dls;
}
/**
* DATABASE MIGRATIONS
*/
protected static function migrateToVersion1()
{
$db = Database::getConnection();
$db->query(<<<QUERY
\t\t\tCREATE TABLE `autologins` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `user_id` bigint(20) unsigned NOT NULL,
\t\t\t `browser_parameters_hash` char(64) NOT NULL,
\t\t\t `key_hash` varchar(255) NOT NULL,
\t\t\t `epoch_created` bigint(20) unsigned NOT NULL,
\t\t\t `epoch_last_used` bigint(20) unsigned DEFAULT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `downloads` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `extension` varchar(8) NOT NULL,
\t\t\t `title` varchar(128) NOT NULL,
\t\t\t `description` varchar(255) DEFAULT NULL,
\t\t\t `type` tinyint unsigned NOT NULL,
\t\t\t `league_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `restricted` tinyint(1) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `fixtures` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `home_team_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `home_team_assigned_number` bigint(20) unsigned DEFAULT NULL,
\t\t\t `away_team_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `away_team_assigned_number` bigint(20) unsigned DEFAULT NULL,
\t\t\t `league_id` bigint(20) unsigned NOT NULL,
\t\t\t `play_by_date` date NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `league_sections` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `letter` varchar(4) NOT NULL,
\t\t\t `league_id` bigint(20) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `league_letter` (`letter`,`league_id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `leagues` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `name` varchar(32) NOT NULL,
\t\t\t `manager_id` bigint(20) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `match_reports` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `match_id` bigint(20) unsigned NOT NULL,
\t\t\t `user_id` bigint(20) unsigned NOT NULL,
\t\t\t `epoch` bigint(20) unsigned NOT NULL,
\t\t\t `home_score` tinyint unsigned NOT NULL,
\t\t\t `away_score` tinyint unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `matches` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `date` date NOT NULL,
\t\t\t `league_id` bigint(20) unsigned NOT NULL,
\t\t\t `home_team_id` bigint(20) unsigned NOT NULL,
\t\t\t `away_team_id` bigint(20) unsigned NOT NULL,
\t\t\t `home_score` tinyint unsigned NOT NULL,
\t\t\t `away_score` tinyint unsigned NOT NULL,
\t\t\t `status` tinyint unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `matches_players` (
\t\t\t `match_id` bigint(20) unsigned NOT NULL,
\t\t\t `player_id` bigint(20) unsigned NOT NULL,
\t\t\t `team_id` bigint(20) unsigned NOT NULL
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `organizations` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `name` varchar(128) NOT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `name` (`name`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `permissions` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `type` varchar(16) NOT NULL,
\t\t\t `name` varchar(32) NOT NULL,
\t\t\t `description` varchar(128) NOT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `name` (`name`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `players` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `full_name` varchar(128) NOT NULL,
\t\t\t `team_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `exempt` tinyint(1) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `settings` (
\t\t\t `id` varchar(64) NOT NULL,
\t\t\t `value` longtext NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `teams` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `designation` varchar(32) NOT NULL,
\t\t\t `organization_id` bigint(20) unsigned NOT NULL,
\t\t\t `league_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `league_section_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `assigned_number` bigint(20) unsigned DEFAULT NULL,
\t\t\t `epoch_registered` bigint(20) unsigned NOT NULL,
\t\t\t `registrant_id` bigint(20) unsigned NOT NULL,
\t\t\t `score_for` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `score_against` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `wins` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `draws` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `losses` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `points` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `organization_designation` (`organization_id`,`designation`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `user_groups` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `name` varchar(64) NOT NULL,
\t\t\t `special` tinyint(1) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `name` (`name`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `user_groups_permissions` (
\t\t\t `group_id` bigint(20) unsigned NOT NULL,
\t\t\t `permission_id` bigint(20) unsigned NOT NULL,
\t\t\t UNIQUE KEY `group_permission` (`group_id`,`permission_id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `users` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `email` varchar(254) NOT NULL,
\t\t\t `password_hash` varchar(255) NOT NULL,
\t\t\t `full_name` varchar(64) NOT NULL,
\t\t\t `phone_number` varchar(32) NOT NULL,
\t\t\t `group_id` bigint(20) unsigned NOT NULL,
\t\t\t `organization_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `email` (`email`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `valid_team_designations` (
\t\t\t `designation` varchar(32) NOT NULL,
\t\t\t PRIMARY KEY (`designation`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
QUERY
);
Permission::add("Admin", "AdminAccessDashboard", "Access the admin dashboard");
Permission::add("Admin", "AdminUsers", "Administrate users");
Permission::add("Admin", "AdminUserGroups", "Administrate user groups and their assigned permissions");
Permission::add("Admin", "AdminOrganizations", "Administrate organizations");
Permission::add("Admin", "AdminTeams", "Administrate teams");
Permission::add("Admin", "AdminMatches", "Administrate matches and match reports");
Permission::add("Admin", "AdminPlayers", "Administrate players");
Permission::add("Admin", "AdminAllLeagues", "Administrate all leagues (users always have permission to administrate leagues they are assigned as the manager of)");
Permission::add("Admin", "AdminAccessMaintenance", "Access the admin maintenance area and use the maintenance tools");
Permission::add("Admin", "PerformDeletionOperations", "Permanently delete data which they have admin access to");
Permission::add("Team", "RegisterTeamsForOwnOrganization", "Register teams for their own organization");
Permission::add("Team", "RegisterTeamsForAnyOrganization", "Register teams for any organization");
Permission::add("Info", "ViewManagerContactInfo", "View contact information for league managers");
Permission::add("Match", "SubmitMatchReport", "Submit match reports for completed matches");
}
<?php
/**
* Sports Match Administrator
*
* Copyright © 2014-2015, Jack P. Harley, jackpharley.com
* All Rights Reserved
*/
require_once __DIR__ . "/Init.php";
echo "Clearing all reconciled results and team points...";
(new \sma\query\UpdateQuery(\sma\Database::getConnection()))->table("teams")->set("wins = 0")->set("draws = 0")->set("losses = 0")->set("points = 0")->set("score_for = 0")->set("score_against = 0")->prepare()->execute();
(new \sma\query\UpdateQuery(\sma\Database::getConnection()))->table("matches")->set("home_score = NULL")->set("away_score = NULL")->prepare()->execute();
echo "done!\n\n";
echo "Now beginning to reconcile all matches\n";
require_once __DIR__ . "/ReconcileAllMatches.php";
/**
* Update a league
*
* @param int $id league id
* @param string $name league name
* @param int $managerId manager id
* @return int new id
*/
public static function update($id, $name = null, $managerId = null)
{
$q = (new UpdateQuery(Database::getConnection()))->table("leagues")->where("id = ?", $id)->limit(1);
if ($name) {
$q->set("name = ?", $name);
}
if ($managerId) {
$q->set("manager_id = ?", $managerId);
}
$q->prepare()->execute();
}
/**
* Get valid designations
*
* @return string[]|bool either boolean true indicating any designation is acceptable or an
* array of valid string designations
*/
public static function getValidDesignations()
{
$q = (new SelectQuery(Database::getConnection()))->from("valid_team_designations")->fields(["designation"]);
$stmt = $q->prepare();
$stmt->execute();
$data = $stmt->fetchAll(\PDO::FETCH_COLUMN, 0);
return !empty($data) ? $data : true;
}
/**
* Revokes permission(s) from a group
*
* @param int $groupId group id
* @param int|array $permissionIds permission id(s)
*/
public static function revokeFromGroup($groupId, $permissionIds)
{
if (!is_array($permissionIds)) {
$permissionIds = [$permissionIds];
}
(new DeleteQuery(Database::getConnection()))->from("user_groups_permissions")->where("group_id = ?", $groupId)->whereInArray("permission_id", $permissionIds)->limit(1)->prepare()->execute();
}
/**
* Delete group
*/
public function delete()
{
(new DeleteQuery(Database::getConnection()))->from("user_groups")->where("id = ?", $this->id)->limit(1)->prepare()->execute();
}
/**
* Add a new fixture
*
* @param int $type TYPE_ASSIGNED_NUMBERS or TYPE_SPECIFIC_TEAMS
* @param string $playByDate YYYY-MM-DD
* @param int $leagueId league
* @param int $homeTeamId home team
* @param int $awayTeamId away team
* @param int $homeTeamNumber home team assigned number
* @param int $awayTeamNumber away team assigned number
*/
public static function add($type, $playByDate, $leagueId, $homeTeamId = null, $awayTeamId = null, $homeTeamNumber = null, $awayTeamNumber = null)
{
$q = (new InsertQuery(Database::getConnection()))->into("fixtures");
if ($type == self::TYPE_ASSIGNED_NUMBERS) {
$q->fields(["play_by_date", "league_id", "home_team_assigned_number", "away_team_assigned_number"]);
$q->values("(?,?,?,?)", [$playByDate, $leagueId, $homeTeamNumber, $awayTeamNumber]);
} else {
if ($type == self::TYPE_SPECIFIC_TEAMS) {
$q->fields(["play_by_date", "league_id", "home_team_id", "away_team_id"]);
$q->values("(?,?,?,?)", [$playByDate, $leagueId, $homeTeamId, $awayTeamId]);
}
}
$q->prepare()->execute();
}
/**
* Change the teams on a match record
*
* @param int $id match id
* @param int $homeTeamId home team id
* @param int $awayTeamId away team id
*/
protected function updateTeams($id, $homeTeamId, $awayTeamId)
{
$q = (new UpdateQuery(Database::getConnection()))->table("matches")->where("id = ?", $id)->limit(1);
if ($homeTeamId) {
$q->set("home_team_id = ?", $homeTeamId);
}
if ($awayTeamId) {
$q->set("away_team_id = ?", $awayTeamId);
}
$q->prepare()->execute();
}
/**
* Add a new section
*
* @param int $leagueId league to add section for
* @return int new id
*/
public static function add($leagueId)
{
$currentSections = self::get(null, $leagueId);
if (!empty($currentSections)) {
$latestSection = end($currentSections);
$newLetter = ++$latestSection->letter;
} else {
$newLetter = "A";
}
(new InsertQuery(Database::getConnection()))->into("league_sections")->fields(["league_id", "letter"])->values("(?,?)", [$leagueId, $newLetter])->prepare()->execute();
return Database::getConnection()->lastInsertId();
}
/**
* Update object
*
* @param int $id org id
* @param string $name
* @return int object id
* @throws \sma\exceptions\DuplicateException if name already exists
*/
public static function update($id, $name = null)
{
$objs = self::get(null, $name);
if (count($objs) > 0) {
if (current($objs)->id != $id) {
throw new DuplicateException();
}
}
$q = (new UpdateQuery(Database::getConnection()))->table("organizations")->where("id = ?", $id)->limit(1);
if ($name) {
$q->set("name = ?", $name);
}
$q->prepare()->execute();
}
public static function submit()
{
Controller::requirePermissions(["SubmitMatchReports"]);
if (empty($_POST)) {
View::load("match/submit.twig", ["leagues" => League::get(), "players" => Player::get()]);
} else {
// basic input validation
Controller::requireFields("post", ["date", "league", "reporter-team", "reporter-score", "opposing-team", "opposing-score"], "/match/submit");
$datetime = DateTime::createFromFormat("Y-m-d", $_POST["date"]);
$epoch = $datetime->getTimestamp();
if ($datetime === false || array_sum($datetime->getLastErrors()) || $epoch > time() || time() - $epoch > 3600 * 24 * 365) {
Controller::addAlert(new Alert("danger", "You did not enter a valid date, please try again."));
Controller::redirect("/match/submit");
}
// check authorization of user to file reports on behalf of reporting team
$reporterTeam = current(Team::get($_POST["reporter-team"]));
$visitor = User::getVisitor();
if ($visitor->organizationId != $reporterTeam->organizationId) {
Controller::requirePermissions(["SubmitMatchReportsForAnyTeam"]);
}
// start determining the data for insertion
if ($_POST["location"] == "home") {
// reporting team is home
$homeTeamId = $_POST["reporter-team"];
$homeScore = $_POST["reporter-score"];
$awayTeamId = $_POST["opposing-team"];
$awayScore = $_POST["opposing-score"];
} else {
$awayTeamId = $_POST["reporter-team"];
$awayScore = $_POST["reporter-score"];
$homeTeamId = $_POST["opposing-team"];
$homeScore = $_POST["opposing-score"];
}
// transaction
Database::getConnection()->beginTransaction();
// attempt to pull an existing match record or add a new one
$match = current(MatchModel::get(null, $_POST["date"], $_POST["league"], $homeTeamId, $awayTeamId));
if ($match) {
$matchId = $match->id;
} else {
$matchId = MatchModel::add($_POST["date"], $_POST["league"], $homeTeamId, $awayTeamId);
}
try {
MatchReport::add($matchId, $_POST["reporter-team"], $visitor->id, $homeScore, $awayScore);
} catch (DuplicateException $e) {
Database::getConnection()->rollBack();
Controller::addAlert(new Alert("danger", "You have already submitted a report for that match!"));
Controller::redirect("/match/submit");
}
if (!$match) {
$match = current(MatchModel::get($matchId));
}
$players = $reporterTeam->getPlayers();
foreach ($players as $player) {
if (array_key_exists("player" . $player->id, $_POST)) {
$match->addParticipatingPlayer($reporterTeam->id, $player->id);
}
}
for ($i = 1; $i <= 8; $i++) {
if (array_key_exists("additional-player" . $i, $_POST) && $_POST["additional-player" . $i]) {
$match->addParticipatingPlayer($reporterTeam->id, null, $_POST["additional-player" . $i]);
}
}
// commit
Database::getConnection()->commit();
// attempt reconciliation
$matches = MatchModel::get($matchId);
current($matches)->attemptReportReconciliation();
Controller::addAlert(new Alert("success", "Match report submitted successfully!"));
Controller::redirect("/match/record?id=" . $matchId);
}
}
public function update($teamId = null)
{
$q = (new UpdateQuery(Database::getConnection()))->table("match_reports")->where("id = ?", $this->id)->limit(1);
if ($teamId) {
$q->set("team_id = ?", $teamId);
}
$q->prepare()->execute();
}
/**
* Update a user
*
* @param int $id user id
* @param string $email
* @param string $fullName
* @param string $phoneNumber
* @param string $password
* @param int $groupId initial user group id
* @param int $organizationId organization user is from
*/
public static function update($id, $email = null, $fullName = null, $phoneNumber = null, $password = null, $groupId = null, $organizationId = null)
{
$q = (new UpdateQuery(Database::getConnection()))->table("users")->where("id = ?", $id)->limit(1);
if ($password) {
$passwordHash = password_hash($password, self::HASHING_ALGORITHM, ["cost" => self::HASHING_COST]);
$q->set("password_hash = ?", $passwordHash);
}
if ($email) {
$q->set("email = ?", $email);
}
if ($fullName) {
$q->set("full_name = ?", $fullName);
}
if ($phoneNumber) {
$q->set("phone_number = ?", $phoneNumber);
}
if ($groupId) {
$q->set("group_id = ?", $groupId);
}
if ($organizationId !== null) {
if ($organizationId === 0) {
$q->set("organization_id = NULL");
} else {
$q->set("organization_id = ?", $organizationId);
}
}
$q->prepare()->execute();
}
/**
* Get players from a match
*
* @param int $matchId match id to get players for
* @param int $teamId team id to restrict players to or null for both teams
* @return Player[] players
*/
public static function getMatchPlayers($matchId, $teamId = null)
{
$q = (new SelectQuery(Database::getConnection()))->from("matches_players mp")->where("mp.match_id = ?", $matchId)->join("LEFT JOIN players p ON mp.player_id=p.id")->fields(["p.id", "p.full_name", "p.team_id", "p.exempt"])->orderby("p.full_name");
if ($teamId) {
$q->where("mp.team_id = ?", $teamId);
}
$stmt = $q->prepare();
$stmt->execute();
$players = [];
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$player = new Player();
list($player->id, $player->fullName, $player->teamId, $player->exempt) = $row;
$players[] = $player;
}
return $players;
}
