diff options
Diffstat (limited to 'includes/model/Shifts_model.php')
| -rw-r--r-- | includes/model/Shifts_model.php | 251 |
1 files changed, 152 insertions, 99 deletions
diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index 462b2f65..3f199803 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -1,4 +1,6 @@ <?php + +use Engelsystem\Database\DB; use Engelsystem\ShiftsFilter; use Engelsystem\ShiftSignupState; @@ -8,8 +10,8 @@ use Engelsystem\ShiftSignupState; */ function Shifts_by_room($room) { - $result = sql_select('SELECT * FROM `Shifts` WHERE `RID`=' . sql_escape($room['RID']) . ' ORDER BY `start`'); - if ($result === false) { + $result = DB::select('SELECT * FROM `Shifts` WHERE `RID`=? ORDER BY `start`', [$room['RID']]); + if (empty($result)) { engelsystem_error('Unable to load shifts.'); } return $result; @@ -21,7 +23,8 @@ function Shifts_by_room($room) */ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter) { - $SQL = "SELECT * FROM ( + //@TODO + $sql = "SELECT * FROM ( SELECT DISTINCT `Shifts`.*, `ShiftTypes`.`name`, `Room`.`Name` AS `room_name` FROM `Shifts` JOIN `Room` USING (`RID`) @@ -47,8 +50,8 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter) AND NOT `Shifts`.`PSID` IS NULL) AS tmp_shifts ORDER BY `start`"; - $result = sql_select($SQL); - if ($result === false) { + $result = DB::select($sql); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shifts by filter.'); } return $result; @@ -56,11 +59,12 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter) /** * @param ShiftsFilter $shiftsFilter - * @return array + * @return array[] */ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter) { - $SQL = " + //@TODO + $sql = " SELECT `NeededAngelTypes`.*, `Shifts`.`SID`, @@ -90,8 +94,8 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter) WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ") AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . " AND NOT `Shifts`.`PSID` IS NULL"; - $result = sql_select($SQL); - if ($result === false) { + $result = DB::select($sql); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load needed angeltypes by filter.'); } return $result; @@ -104,40 +108,48 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter) */ function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype) { - $result = sql_select(" - SELECT - `NeededAngelTypes`.*, - `Shifts`.`SID`, - `AngelTypes`.`id`, - `AngelTypes`.`name`, - `AngelTypes`.`restricted`, - `AngelTypes`.`no_self_signup` - FROM `Shifts` - JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID` - JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` - WHERE `Shifts`.`SID`=" . sql_escape($shift['SID']) . " - AND `AngelTypes`.`id`=" . sql_escape($angeltype['id']) . " - AND `Shifts`.`PSID` IS NULL - - UNION - - SELECT - `NeededAngelTypes`.*, - `Shifts`.`SID`, - `AngelTypes`.`id`, - `AngelTypes`.`name`, - `AngelTypes`.`restricted`, - `AngelTypes`.`no_self_signup` - FROM `Shifts` - JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` - JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` - WHERE `Shifts`.`SID`=" . sql_escape($shift['SID']) . " - AND `AngelTypes`.`id`=" . sql_escape($angeltype['id']) . " - AND NOT `Shifts`.`PSID` IS NULL"); - if ($result === false) { + $result = DB::select(' + SELECT + `NeededAngelTypes`.*, + `Shifts`.`SID`, + `AngelTypes`.`id`, + `AngelTypes`.`name`, + `AngelTypes`.`restricted`, + `AngelTypes`.`no_self_signup` + FROM `Shifts` + JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID` + JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + WHERE `Shifts`.`SID`=? + AND `AngelTypes`.`id`=? + AND `Shifts`.`PSID` IS NULL + + UNION + + SELECT + `NeededAngelTypes`.*, + `Shifts`.`SID`, + `AngelTypes`.`id`, + `AngelTypes`.`name`, + `AngelTypes`.`restricted`, + `AngelTypes`.`no_self_signup` + FROM `Shifts` + JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` + JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + WHERE `Shifts`.`SID`=? + AND `AngelTypes`.`id`=? + AND NOT `Shifts`.`PSID` IS NULL + ', + [ + $shift['SID'], + $angeltype['id'], + $shift['SID'], + $angeltype['id'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load needed angeltypes by filter.'); } - if (count($result) == 0) { + if (empty($result)) { return null; } return $result[0]; @@ -149,7 +161,8 @@ function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype) */ function ShiftEntries_by_ShiftsFilter(ShiftsFilter $shiftsFilter) { - $SQL = " + // @TODO + $sql = " SELECT `User`.`Nick`, `User`.`email`, @@ -167,8 +180,8 @@ function ShiftEntries_by_ShiftsFilter(ShiftsFilter $shiftsFilter) WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ") AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . " ORDER BY `Shifts`.`start`"; - $result = sql_select($SQL); - if ($result === false) { + $result = DB::select($sql); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shift entries by filter.'); } return $result; @@ -374,25 +387,31 @@ function Shift_signup_allowed( * Delete a shift by its external id. * * @param int $shift_psid - * @return mysqli_result|false + * @return bool */ function Shift_delete_by_psid($shift_psid) { - return sql_query("DELETE FROM `Shifts` WHERE `PSID`='" . sql_escape($shift_psid) . "'"); + DB::delete('DELETE FROM `Shifts` WHERE `PSID`=?', [$shift_psid]); + + if (DB::getStm()->errorCode() != '00000') { + return false; + } + + return true; } /** * Delete a shift. * * @param int $shift_id - * @return mysqli_result + * @return bool */ function Shift_delete($shift_id) { mail_shift_delete(Shift($shift_id)); - $result = sql_query("DELETE FROM `Shifts` WHERE `SID`='" . sql_escape($shift_id) . "'"); - if ($result === false) { + $result = DB::delete('DELETE FROM `Shifts` WHERE `SID`=?', [$shift_id]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to delete shift.'); } return $result; @@ -402,7 +421,7 @@ function Shift_delete($shift_id) * Update a shift. * * @param array $shift - * @return mysqli_result|false + * @return bool */ function Shift_update($shift) { @@ -410,36 +429,51 @@ function Shift_update($shift) $shift['name'] = ShiftType($shift['shifttype_id'])['name']; mail_shift_change(Shift($shift['SID']), $shift); - return sql_query(" + return (bool)DB::update(' UPDATE `Shifts` SET - `shifttype_id`='" . sql_escape($shift['shifttype_id']) . "', - `start`='" . sql_escape($shift['start']) . "', - `end`='" . sql_escape($shift['end']) . "', - `RID`='" . sql_escape($shift['RID']) . "', - `title`=" . sql_null($shift['title']) . ", - `URL`=" . sql_null($shift['URL']) . ", - `PSID`=" . sql_null($shift['PSID']) . ", - `edited_by_user_id`='" . sql_escape($user['UID']) . "', - `edited_at_timestamp`=" . time() . " - WHERE `SID`='" . sql_escape($shift['SID']) . "' - "); + `shifttype_id` = ?, + `start` = ?, + `end` = ?, + `RID` = ?, + `title` = ?, + `URL` = ?, + `PSID` = ?, + `edited_by_user_id` = ?, + `edited_at_timestamp` = ? + WHERE `SID` = ? + ', + [ + $shift['shifttype_id'], + $shift['start'], + $shift['end'], + $shift['RID'], + $shift['title'], + $shift['URL'], + $shift['PSID'], + $user['UID'], + time(), + $shift['SID'] + ] + ); } /** * Update a shift by its external id. * * @param array $shift - * @return mysqli_result|false|null + * @return bool|null */ function Shift_update_by_psid($shift) { - $shift_source = sql_select("SELECT `SID` FROM `Shifts` WHERE `PSID`=" . $shift['PSID']); - if ($shift_source === false) { + $shift_source = DB::select('SELECT `SID` FROM `Shifts` WHERE `PSID`=?', [$shift['PSID']]); + if (DB::getStm()->errorCode() != '00000') { return false; } - if (count($shift_source) == 0) { + + if (empty($shift_source)) { return null; } + $shift['SID'] = $shift_source[0]['SID']; return Shift_update($shift); } @@ -453,22 +487,36 @@ function Shift_update_by_psid($shift) function Shift_create($shift) { global $user; - $result = sql_query(" - INSERT INTO `Shifts` SET - `shifttype_id`='" . sql_escape($shift['shifttype_id']) . "', - `start`='" . sql_escape($shift['start']) . "', - `end`='" . sql_escape($shift['end']) . "', - `RID`='" . sql_escape($shift['RID']) . "', - `title`=" . sql_null($shift['title']) . ", - `URL`=" . sql_null($shift['URL']) . ", - `PSID`=" . sql_null($shift['PSID']) . ", - `created_by_user_id`='" . sql_escape($user['UID']) . "', - `created_at_timestamp`=" . time() + DB::insert(' + INSERT INTO `Shifts` ( + `shifttype_id`, + `start`, + `end`, + `RID`, + `title`, + `URL`, + `PSID`, + `created_by_user_id`, + `created_at_timestamp` + ) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) + ', + [ + $shift['shifttype_id'], + $shift['start'], + $shift['end'], + $shift['RID'], + $shift['title'], + $shift['URL'], + $shift['PSID'], + $user['UID'], + time(), + ] ); - if ($result === false) { + if (DB::getStm()->errorCode() != '00000') { return false; } - return sql_id(); + return DB::getPdo()->lastInsertId(); } /** @@ -480,7 +528,7 @@ function Shift_create($shift) */ function Shifts_by_user($user, $include_freeload_comments = false) { - $result = sql_select(' + $result = DB::select(' SELECT `ShiftTypes`.`id` AS `shifttype_id`, `ShiftTypes`.`name`, `ShiftEntry`.`id`, `ShiftEntry`.`SID`, `ShiftEntry`.`TID`, `ShiftEntry`.`UID`, `ShiftEntry`.`freeloaded`, `ShiftEntry`.`Comment`, ' . ($include_freeload_comments ? '`ShiftEntry`.`freeload_comment`, ' : '') . ' @@ -489,10 +537,14 @@ function Shifts_by_user($user, $include_freeload_comments = false) JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) - WHERE `UID`=\'' . sql_escape($user['UID']) . '\' + WHERE `UID` = ? ORDER BY `start` - '); - if ($result === false) { + ', + [ + $user['UID'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load users shifts.'); } return $result; @@ -506,14 +558,13 @@ function Shifts_by_user($user, $include_freeload_comments = false) */ function Shift($shift_id) { - $shifts_source = sql_select(" + $shifts_source = DB::select(' SELECT `Shifts`.*, `ShiftTypes`.`name` FROM `Shifts` JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) - WHERE `SID`='" . sql_escape($shift_id) . "'"); - $shiftsEntry_source = sql_select("SELECT `id`, `TID` , `UID` , `freeloaded` FROM `ShiftEntry` WHERE `SID`='" . sql_escape($shift_id) . "'"); + WHERE `SID`=?', [$shift_id]); - if ($shifts_source === false) { + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shift.'); } @@ -523,16 +574,21 @@ function Shift($shift_id) $result = $shifts_source[0]; + $shiftsEntry_source = DB::select(' + SELECT `id`, `TID` , `UID` , `freeloaded` + FROM `ShiftEntry` + WHERE `SID`=?', [$shift_id]); + $result['ShiftEntry'] = $shiftsEntry_source; $result['NeedAngels'] = []; - $temp = NeededAngelTypes_by_shift($shift_id); - foreach ($temp as $e) { + $angelTypes = NeededAngelTypes_by_shift($shift_id); + foreach ($angelTypes as $type) { $result['NeedAngels'][] = [ - 'TID' => $e['angel_type_id'], - 'count' => $e['count'], - 'restricted' => $e['restricted'], - 'taken' => $e['taken'] + 'TID' => $type['angel_type_id'], + 'count' => $type['count'], + 'restricted' => $type['restricted'], + 'taken' => $type['taken'] ]; } @@ -546,22 +602,19 @@ function Shift($shift_id) */ function Shifts() { - $shifts_source = sql_select(' + $shifts_source = DB::select(' SELECT `ShiftTypes`.`name`, `Shifts`.*, `Room`.`RID`, `Room`.`Name` AS `room_name` FROM `Shifts` JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) JOIN `Room` ON `Room`.`RID` = `Shifts`.`RID` '); - if ($shifts_source === false) { + + if (DB::getStm()->errorCode() != '00000') { return false; } foreach ($shifts_source as &$shift) { $needed_angeltypes = NeededAngelTypes_by_shift($shift['SID']); - if ($needed_angeltypes === false) { - return false; - } - $shift['angeltypes'] = $needed_angeltypes; } |