summaryrefslogtreecommitdiff
path: root/includes/model/Shifts_model.php
diff options
context:
space:
mode:
Diffstat (limited to 'includes/model/Shifts_model.php')
-rw-r--r--includes/model/Shifts_model.php251
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;
}