/**
  * 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;
 }