/**
*
* @param \PDO $pdo
* @param int $user_id
* @param int $game_type_id
* @return boolean
*/
public static function delete($pdo, $user_id, $game_type_id)
{
$statement = $pdo->prepare("DELETE FROM `web_logickehry_db`.`game_rating` " . "WHERE `game_type_id` = :gid AND `user_id` = :uid;");
if (!$statement->execute(['gid' => $game_type_id, 'uid' => $user_id])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return false;
}
return true;
}
/**
* @param \PDO $pdo
* @param int $user_id
* @param int $game_type_id
* @return boolean
*/
public static function insert($pdo, $user_id, $game_type_id)
{
$statement = $pdo->prepare("INSERT INTO `web_logickehry_db`.`subscription` " . "(`user_id`, `game_type_id`) " . "VALUES (:uid, :gid)");
if (!$statement->execute(['uid' => $user_id, 'gid' => $game_type_id])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return false;
}
return true;
}
/**
*
* @param \PDO $pdo
* @param int $user_id
* @return ReservationExtended
*/
public static function fetchById($pdo, $user_id)
{
$statement = $pdo->prepare("SELECT * FROM user_extended\n\t\t\tWHERE user_id = :id");
if (!$statement->execute(['id' => $user_id])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return null;
}
return $statement->fetchObject(UserExtended::class);
}
/**
*
* @param \PDO $pdo
* @param String $orion_login
* @param int $role_id
* @return boolean
*/
public static function setUserRole($pdo, $orion_login, $role_id)
{
$statement = $pdo->prepare("UPDATE `web_logickehry_db`.`user` SET " . "`role_id` = :role " . "WHERE `user`.`orion_login` = :orion_login");
if (!$statement->execute(['role' => $role_id, 'orion_login' => $orion_login])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return false;
}
return true;
}
/**
*
* @param \PDO $pdo
* @param int $game_type_id
* @return GameRatingExtended[]
*/
public static function fetchAllByGameType($pdo, $game_type_id)
{
$statement = $pdo->prepare("SELECT * FROM `web_logickehry_db`.`game_rating_extended` " . "WHERE `game_type_id` = :gid;");
if (!$statement->execute(['gid' => $game_type_id])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return false;
}
return $statement->fetchAll(\PDO::FETCH_CLASS, self::class);
}
/**
*
* @param \PDO $pdo
* @param int[] $desk_ids
*/
public static function deleteMany($pdo, $desk_ids)
{
$statement = $pdo->prepare('DELETE FROM `desk` WHERE desk_id IN (:desk_id) ');
$pars = ['desk_id' => implode(', ', $desk_ids)];
if (!$statement->execute($pars)) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString, $pars);
return false;
}
return $statement->columnCount();
}
/**
*
* @param mixed[] $pars
* @return Event[]
*/
public function fetchWithinTimespan($date_from, $date_to)
{
$sql = "SELECT * FROM `event` " . "WHERE event_date >= :date_from AND event_date <= :date_to " . "ORDER BY time_from ASC";
$statement = $this->pdo->prepare($sql);
if (!$statement->execute(['date_from' => $date_from, 'date_to' => $date_to])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString, $pars);
return null;
}
return $statement->fetchAll(\PDO::FETCH_CLASS, Event::class);
}
/**
*
* @param \PDO $pdo
* @param String $code
* @return \model\database\views\GameBoxExtended
*/
public static function retire($pdo, $code)
{
$box = \model\database\views\GameBoxExtended::fetchByCode($pdo, $code);
if (!$box) {
return null;
}
$statement = $pdo->prepare("UPDATE `web_logickehry_db`.`game_box` SET " . "`retired` = 1 " . "WHERE `game_box`.`tracking_code` = :tracking_code");
if (!$statement->execute(['tracking_code' => $code])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return null;
}
return $box;
}
/**
*
* @param \PDO $pdo
* @param int $game_type_id
* @return GameBoxExtended[]
*/
public static function fetchAllByGameType($pdo, $game_type_id)
{
$statement = $pdo->prepare("SELECT * FROM game_box_extended" . " WHERE retired = 0 " . "AND game_type_id = :gid " . "ORDER BY times_reserved ASC");
if (!$statement->execute(['gid' => $game_type_id])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return false;
}
$result = $statement->fetchAll(\PDO::FETCH_CLASS, GameBoxExtended::class);
$boxes = [];
foreach ($result as $gb) {
$boxes[$gb->game_box_id] = $gb;
}
return $boxes;
}
/**
*
* @param \PDO $pdo
* @param mixed[] $pars
* @return boolean
*/
public static function update($pdo, $pars, $id)
{
$sql = "UPDATE `web_logickehry_db`.`game_type` SET ";
foreach ($pars as $par => $val) {
$sql .= "`{$par}` = :{$par}, ";
}
$sql .= "WHERE `game_type_id` = :id ";
$statement = $pdo->prepare($sql);
$pars['id'] = $id;
if ($statement->execute($pars)) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString, $pars);
return false;
}
return true;
}
/**
*
* @param \PDO $pdo
* @param Date $date
* @param Time[] $time
* @return mixed[]
*/
public static function countReservationsOn($pdo, $date, $time = null)
{
$statement = $pdo->prepare('SELECT reservation_type_id, count(reservation_id) AS count FROM reservation_extended ' . 'WHERE reservation_date = :date ' . ($time ? 'AND ( ( time_from <= :time_from1 AND :time_from2 <= time_to ) OR' . ' ( time_from <= :time_to1 AND :time_to2 <= time_to ) )' : '') . 'GROUP BY reservation_type_id');
$pars = ['date' => $date];
if ($time) {
$pars['time_from1'] = $pars['time_from2'] = $time['from'];
$pars['time_to1'] = $pars['time_to2'] = $time['to'];
}
if (!$statement->execute($pars)) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString, $pars);
return false;
}
$return = [];
$result = $statement->fetchAll(\PDO::FETCH_ASSOC);
$total = 0;
foreach ($result as $r) {
$total += $return[$r['reservation_type_id']] = $r['count'];
}
if ($total > 0) {
$return['total'] = $total;
}
return $return;
}
public function preRender()
{
$messages = array_merge(DB_Service::getErrorLog(), $this->mb->getLog());
$this->template['alert_messages'] = $messages;
}
/**
*
* @param \PDO $pdo
* @return Feedback
*/
public static function fetchById($pdo, $id)
{
$statement = $pdo->prepare("SELECT * FROM feedback " . "WHERE feedback_id = :id");
if (!$statement->execute(['id' => $id])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return null;
}
return $statement->fetch(\PDO::FETCH_CLASS, Feedback::class);
}
public function __construct(PDO $pdo)
{
parent::__construct($pdo);
}
protected static function createFromPost($class)
{
return DB_Service::fromPOST($class);
}
/**
*
* @param \PDO $pdo
* @param Date $date
* @param Time $time_from
* @param Time $time_to
* @return boolean
*/
public static function existsDuring($pdo, $date, $time_from, $time_to)
{
$statement = $pdo->prepare('SELECT * FROM event' . ' WHERE event_date = :date AND ( ' . ' ( time_from <= :time_from1 AND :time_from2 <= time_to ) OR' . ' ( time_from <= :time_to1 AND :time_to2 <= time_to )' . ')');
$pars = ['date' => $date];
$pars['time_from1'] = $pars['time_from2'] = $time_from;
$pars['time_to1'] = $pars['time_to2'] = $time_to;
if (!$statement->execute($pars)) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString, $pars);
return false;
}
return !empty($statement->fetchAll(\PDO::FETCH_CLASS, Event::class));
}
/**
*
* @param \PDO $pdo
* @param int $id
*/
public static function delete($pdo, $id)
{
$statement = $pdo->prepare("DELETE FROM reservation WHERE reservation_id = :rid");
if (!$statement->execute(['rid' => $id])) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString);
return false;
}
return true;
}
/**
*
* @param type $sql
* @param type $pars
* @param type $function
* @return \PDOStatement
*/
protected function execute($sql, $pars = null)
{
$statement = $this->pdo->prepare($sql);
if (!$statement->execute($pars)) {
$function = $this->stringifyCaller();
DB_Service::logError($statement->errorInfo(), $function, $statement->queryString, $pars);
return false;
}
return $statement;
}
public function countWithin($date_from, $date_to)
{
$statement = $this->pdo->prepare("SELECT game_type_id, count(reservation_id) as count FROM `reservation_extended` " . "WHERE reservation_type_id = :reservation_type_id AND :date_from <= reservation_date AND reservation_date <= :date_to " . "GROUP BY game_type_id");
$pars = ['reservation_type_id' => Reservation::RES_TYPE_OPEN, 'date_from' => $date_from, 'date_to' => $date_to];
if (!$statement->execute($pars)) {
DB_Service::logError($statement->errorInfo(), __CLASS__ . "::" . __FUNCTION__, $statement->queryString, $pars);
return null;
}
return $statement->fetchAll(\PDO::FETCH_ASSOC);
}
