diff options
Diffstat (limited to 'includes/model')
| -rw-r--r-- | includes/model/AngelType_model.php | 25 | ||||
| -rw-r--r-- | includes/model/NeededAngelTypes_model.php | 62 | ||||
| -rw-r--r-- | includes/model/Room_model.php | 11 | ||||
| -rw-r--r-- | includes/model/ShiftEntry_model.php | 6 | ||||
| -rw-r--r-- | includes/model/ShiftTypes_model.php | 2 | ||||
| -rw-r--r-- | includes/model/ShiftsFilter.php | 112 | ||||
| -rw-r--r-- | includes/model/Shifts_model.php | 135 | ||||
| -rw-r--r-- | includes/model/UserAngelTypes_model.php | 36 | ||||
| -rw-r--r-- | includes/model/User_model.php | 8 |
9 files changed, 338 insertions, 59 deletions
diff --git a/includes/model/AngelType_model.php b/includes/model/AngelType_model.php index 73b746f0..be15dc59 100644 --- a/includes/model/AngelType_model.php +++ b/includes/model/AngelType_model.php @@ -91,7 +91,7 @@ function AngelType_validate_name($name, $angeltype) { * @param User $user */ function AngelTypes_with_user($user) { - return sql_select(" + $result = sql_select(" SELECT `AngelTypes`.*, `UserAngelTypes`.`id` as `user_angeltype_id`, `UserAngelTypes`.`confirm_user_id`, @@ -100,30 +100,35 @@ function AngelTypes_with_user($user) { LEFT JOIN `UserAngelTypes` ON `AngelTypes`.`id`=`UserAngelTypes`.`angeltype_id` AND `UserAngelTypes`.`user_id`=" . $user['UID'] . " ORDER BY `name`"); + if ($result === false) { + engelsystem_error("Unable to load angeltypes."); + } + return $result; } /** * Returns all angeltypes. */ function AngelTypes() { - return sql_select(" + $result = sql_select(" SELECT * FROM `AngelTypes` ORDER BY `name`"); + if ($result === false) { + engelsystem_error("Unable to load angeltypes."); + } + return $result; } /** * Returns AngelType id array */ function AngelType_ids() { - $angelType_source = sql_select("SELECT `id` FROM `AngelTypes`"); - if ($angelType_source === false) { - return false; - } - if (count($angelType_source) > 0) { - return $angelType_source; + $result = sql_select("SELECT `id` FROM `AngelTypes`"); + if ($result === false) { + engelsystem_error("Unable to load angeltypes."); } - return null; + return select_array($result, 'id', 'id'); } /** @@ -135,7 +140,7 @@ function AngelType_ids() { function AngelType($angeltype_id) { $angelType_source = sql_select("SELECT * FROM `AngelTypes` WHERE `id`='" . sql_escape($angeltype_id) . "' LIMIT 1"); if ($angelType_source === false) { - return false; + engelsystem_error("Unable to load angeltype."); } if (count($angelType_source) > 0) { return $angelType_source[0]; diff --git a/includes/model/NeededAngelTypes_model.php b/includes/model/NeededAngelTypes_model.php index 96ceca83..48f8ed20 100644 --- a/includes/model/NeededAngelTypes_model.php +++ b/includes/model/NeededAngelTypes_model.php @@ -1,13 +1,63 @@ <?php /** + * Entity needed angeltypes describes how many angels of given type are needed for a shift or in a room. + */ + +/** + * Insert a new needed angel type. + * + * @param int $shift_id + * The shift. Can be null, but then a room_id must be given. + * @param int $angeltype_id + * The angeltype + * @param int $room_id + * The room. Can be null, but then a shift_id must be given. + * @param int $count + * How many angels are needed? + */ +function NeededAngelType_add($shift_id, $angeltype_id, $room_id, $count) { + $result = sql_query(" + INSERT INTO `NeededAngelTypes` SET + `shift_id`=" . sql_null($shift_id) . ", + `angel_type_id`='" . sql_escape($angeltype_id) . "', + `room_id`=" . sql_null($room_id) . ", + `count`='" . sql_escape($count) . "'"); + if ($result === false) { + return false; + } + return sql_id(); +} + +/** + * Deletes all needed angel types from given shift. + * + * @param int $shift_id + * id of the shift + */ +function NeededAngelTypes_delete_by_shift($shift_id) { + return sql_query("DELETE FROM `NeededAngelTypes` WHERE `shift_id`='" . sql_escape($shift_id) . "'"); +} + +/** + * Deletes all needed angel types from given room. + * + * @param int $room_id + * id of the room + */ +function NeededAngelTypes_delete_by_room($room_id) { + return sql_query("DELETE FROM `NeededAngelTypes` WHERE `room_id`='" . sql_escape($room_id) . "'"); +} + +/** * Returns all needed angeltypes and already taken needs. * - * @param shiftID id of shift + * @param int $shiftID + * id of shift */ function NeededAngelTypes_by_shift($shiftId) { $needed_angeltypes_source = sql_select(" - SELECT `NeededAngelTypes`.*, `AngelTypes`.`name`, `AngelTypes`.`restricted` + SELECT `NeededAngelTypes`.*, `AngelTypes`.`id`, `AngelTypes`.`name`, `AngelTypes`.`restricted` FROM `NeededAngelTypes` JOIN `AngelTypes` ON `AngelTypes`.`id` = `NeededAngelTypes`.`angel_type_id` WHERE `shift_id`='" . sql_escape($shiftId) . "' @@ -15,7 +65,7 @@ function NeededAngelTypes_by_shift($shiftId) { ORDER BY `room_id` DESC "); if ($needed_angeltypes_source === false) { - return false; + engelsystem_error("Unable to load needed angeltypes."); } // Use settings from room @@ -30,18 +80,16 @@ function NeededAngelTypes_by_shift($shiftId) { ORDER BY `room_id` DESC "); if ($needed_angeltypes_source === false) { - return false; + engelsystem_error("Unable to load needed angeltypes."); } } $needed_angeltypes = []; foreach ($needed_angeltypes_source as $angeltype) { $shift_entries = ShiftEntries_by_shift_and_angeltype($shiftId, $angeltype['angel_type_id']); - if ($shift_entries === false) { - return false; - } $angeltype['taken'] = count($shift_entries); + $angeltype['shift_entries'] = $shift_entries; $needed_angeltypes[] = $angeltype; } diff --git a/includes/model/Room_model.php b/includes/model/Room_model.php index 4d03260a..6b6e269e 100644 --- a/includes/model/Room_model.php +++ b/includes/model/Room_model.php @@ -1,8 +1,17 @@ <?php /** + * returns a list of rooms. + * @param boolean $show_all returns also hidden rooms when true + */ +function Rooms($show_all = false) { + return sql_select("SELECT * FROM `Room`" . ($show_all ? "" : " WHERE `show`='Y'") . " ORDER BY `Name`"); +} + +/** * Delete a room - * @param int $room_id + * + * @param int $room_id */ function Room_delete($room_id) { return sql_query("DELETE FROM `Room` WHERE `RID`=" . sql_escape($room_id)); diff --git a/includes/model/ShiftEntry_model.php b/includes/model/ShiftEntry_model.php index 425b92e8..63127bc7 100644 --- a/includes/model/ShiftEntry_model.php +++ b/includes/model/ShiftEntry_model.php @@ -110,12 +110,16 @@ function ShiftEntries_finished_by_user($user) { * @param int $angeltype_id */ function ShiftEntries_by_shift_and_angeltype($shift_id, $angeltype_id) { - return sql_select(" + $result = sql_select(" SELECT * FROM `ShiftEntry` WHERE `SID`=" . sql_escape($shift_id) . " AND `TID`=" . sql_escape($angeltype_id) . " "); + if ($result === false) { + engelsystem_error("Unable to load shift entries."); + } + return $result; } /** diff --git a/includes/model/ShiftTypes_model.php b/includes/model/ShiftTypes_model.php index a9cf77bd..89704a65 100644 --- a/includes/model/ShiftTypes_model.php +++ b/includes/model/ShiftTypes_model.php @@ -51,7 +51,7 @@ function ShiftType_create($name, $angeltype_id, $description) { function ShiftType($shifttype_id) { $shifttype = sql_select("SELECT * FROM `ShiftTypes` WHERE `id`='" . sql_escape($shifttype_id) . "'"); if ($shifttype === false) { - return false; + engelsystem_error('Unable to load shift type.'); } if ($shifttype == null) { return null; diff --git a/includes/model/ShiftsFilter.php b/includes/model/ShiftsFilter.php new file mode 100644 index 00000000..044b32dd --- /dev/null +++ b/includes/model/ShiftsFilter.php @@ -0,0 +1,112 @@ +<?php + +namespace Engelsystem; + +/** + * BO Class that stores all parameters used to filter shifts for users. + * + * @author msquare + */ +class ShiftsFilter { + + /** + * Shift is completely full. + */ + const FILLED_FILLED = 1; + + /** + * Shift has some free slots. + */ + const FILLED_FREE = 0; + + /** + * Has the user "user shifts admin" privilege? + * + * @var boolean + */ + private $userShiftsAdmin; + + private $filled = []; + + private $rooms = []; + + private $types = []; + + private $startTime = null; + + private $endTime = null; + + public function __construct($user_shifts_admin, $rooms, $types) { + $this->user_shifts_admin = $user_shifts_admin; + $this->rooms = $rooms; + $this->types = $types; + + $this->filled = [ + ShiftsFilter::FILLED_FREE + ]; + + if ($user_shifts_admin) { + $this->filled[] = ShiftsFilter::FILLED_FILLED; + } + } + + public function getStartTime() { + return $this->startTime; + } + + public function setStartTime($startTime) { + $this->startTime = $startTime; + } + + public function getEndTime() { + return $this->endTime; + } + + public function setEndTime($endTime) { + $this->endTime = $endTime; + } + + public function getTypes() { + if (count($this->types) == 0) { + return [ + 0 + ]; + } + return $this->types; + } + + public function setTypes($types) { + $this->types = $types; + } + + public function getRooms() { + if (count($this->rooms) == 0) { + return [ + 0 + ]; + } + return $this->rooms; + } + + public function setRooms($rooms) { + $this->rooms = $rooms; + } + + public function isUserShiftsAdmin() { + return $this->userShiftsAdmin; + } + + public function setUserShiftsAdmin($userShiftsAdmin) { + $this->userShiftsAdmin = $userShiftsAdmin; + } + + public function getFilled() { + return $this->filled; + } + + public function setFilled($filled) { + $this->filled = $filled; + } +} + +?>
\ No newline at end of file diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index a827c6b5..f232360e 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -1,9 +1,82 @@ <?php +use Engelsystem\ShiftsFilter; + +function Shifts_by_room($room) { + $result = sql_select("SELECT * FROM `Shifts` WHERE `RID`=" . sql_escape($room['RID']) . " ORDER BY `start`"); + if ($result === false) { + engelsystem_error("Unable to load shifts."); + } + return $result; +} + +function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter, $user) { + $SQL = "SELECT DISTINCT `Shifts`.*, `ShiftTypes`.`name`, `Room`.`Name` as `room_name`, nat2.`special_needs` > 0 AS 'has_special_needs' + FROM `Shifts` + INNER JOIN `Room` USING (`RID`) + INNER JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + LEFT JOIN ( + SELECT COUNT(*) AS special_needs , nat3.`shift_id` + FROM `NeededAngelTypes` AS nat3 + WHERE `shift_id` IS NOT NULL + GROUP BY nat3.`shift_id` + ) AS nat2 ON nat2.`shift_id` = `Shifts`.`SID` + INNER JOIN `NeededAngelTypes` AS nat + ON nat.`count` != 0 + AND nat.`angel_type_id` IN (" . implode(',', $shiftsFilter->getTypes()) . ") + AND ( + (nat2.`special_needs` > 0 AND nat.`shift_id` = `Shifts`.`SID`) + OR + ( + (nat2.`special_needs` = 0 OR nat2.`special_needs` IS NULL) + AND nat.`room_id` = `RID`) + ) + LEFT JOIN ( + SELECT se.`SID`, se.`TID`, COUNT(*) as count + FROM `ShiftEntry` AS se GROUP BY se.`SID`, se.`TID` + ) AS entries ON entries.`SID` = `Shifts`.`SID` AND entries.`TID` = nat.`angel_type_id` + WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ") + AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime(); + + if (count($shiftsFilter->getFilled()) == 1) { + if ($shiftsFilter->getFilled()[0] == ShiftsFilter::FILLED_FREE) { + $SQL .= " + AND ( + nat.`count` > entries.`count` OR entries.`count` IS NULL + OR EXISTS ( + SELECT `SID` + FROM `ShiftEntry` + WHERE `UID` = '" . sql_escape($user['UID']) . "' + AND `ShiftEntry`.`SID` = `Shifts`.`SID` + ) + )"; + } elseif ($_SESSION['user_shifts']['filled'][0] == ShiftsFilter::FILLED_FILLED) { + $SQL .= " + AND ( + nat.`count` <= entries.`count` + OR EXISTS ( + SELECT `SID` + FROM `ShiftEntry` + WHERE `UID` = '" . sql_escape($user['UID']) . "' + AND `ShiftEntry`.`SID` = `Shifts`.`SID` + ) + )"; + } + } + $SQL .= " + ORDER BY `start`"; + + $result = sql_select($SQL); + if ($result === false) { + engelsystem_error("Unable to load shifts by filter."); + } + return $result; +} /** * Check if a shift collides with other shifts (in time). - * @param Shift $shift - * @param array<Shift> $shifts + * + * @param Shift $shift + * @param array<Shift> $shifts */ function Shift_collides($shift, $shifts) { foreach ($shifts as $other_shift) { @@ -28,18 +101,12 @@ function Shift_signup_allowed($shift, $angeltype, $user_angeltype = null, $user_ if ($user_shifts == null) { $user_shifts = Shifts_by_user($user); - if ($user_shifts === false) { - engelsystem_error('Unable to load users shifts.'); - } } $collides = Shift_collides($shift, $user_shifts); if ($user_angeltype == null) { $user_angeltype = UserAngelType_by_User_and_AngelType($user, $angeltype); - if ($user_angeltype === false) { - engelsystem_error('Unable to load user angeltype.'); - } } $signed_up = false; @@ -104,7 +171,11 @@ function Shift_delete_by_psid($shift_psid) { function Shift_delete($shift_id) { mail_shift_delete(Shift($shift_id)); - return sql_query("DELETE FROM `Shifts` WHERE `SID`='" . sql_escape($shift_id) . "'"); + $result = sql_query("DELETE FROM `Shifts` WHERE `SID`='" . sql_escape($shift_id) . "'"); + if ($result === false) { + engelsystem_error('Unable to delete shift.'); + } + return $result; } /** @@ -170,7 +241,7 @@ function Shift_create($shift) { * Return users shifts. */ function Shifts_by_user($user) { - return sql_select(" + $result = sql_select(" SELECT `ShiftTypes`.`id` as `shifttype_id`, `ShiftTypes`.`name`, `ShiftEntry`.*, `Shifts`.*, `Room`.* FROM `ShiftEntry` JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) @@ -179,6 +250,10 @@ function Shifts_by_user($user) { WHERE `UID`='" . sql_escape($user['UID']) . "' ORDER BY `start` "); + if ($result === false) { + engelsystem_error('Unable to load users shifts.'); + } + return $result; } /** @@ -242,27 +317,29 @@ function Shift($shift_id) { $shiftsEntry_source = sql_select("SELECT `id`, `TID` , `UID` , `freeloaded` FROM `ShiftEntry` WHERE `SID`='" . sql_escape($shift_id) . "'"); if ($shifts_source === false) { - return false; + engelsystem_error('Unable to load shift.'); } - if (count($shifts_source) > 0) { - $result = $shifts_source[0]; - - $result['ShiftEntry'] = $shiftsEntry_source; - $result['NeedAngels'] = []; - - $temp = NeededAngelTypes_by_shift($shift_id); - foreach ($temp as $e) { - $result['NeedAngels'][] = [ - 'TID' => $e['angel_type_id'], - 'count' => $e['count'], - 'restricted' => $e['restricted'], - 'taken' => $e['taken'] - ]; - } - - return $result; + + if (empty($shifts_source)) { + return null; } - return null; + + $result = $shifts_source[0]; + + $result['ShiftEntry'] = $shiftsEntry_source; + $result['NeedAngels'] = []; + + $temp = NeededAngelTypes_by_shift($shift_id); + foreach ($temp as $e) { + $result['NeedAngels'][] = [ + 'TID' => $e['angel_type_id'], + 'count' => $e['count'], + 'restricted' => $e['restricted'], + 'taken' => $e['taken'] + ]; + } + + return $result; } /** diff --git a/includes/model/UserAngelTypes_model.php b/includes/model/UserAngelTypes_model.php index fe5293f8..74b298cc 100644 --- a/includes/model/UserAngelTypes_model.php +++ b/includes/model/UserAngelTypes_model.php @@ -42,7 +42,7 @@ function User_angeltypes($user) { * @param User $user */ function User_unconfirmed_AngelTypes($user) { - return sql_select(" + $result = sql_select(" SELECT `UserAngelTypes`.*, `AngelTypes`.`name`, @@ -56,6 +56,10 @@ function User_unconfirmed_AngelTypes($user) { AND `UnconfirmedMembers`.`confirm_user_id` IS NULL GROUP BY `UserAngelTypes`.`angeltype_id` ORDER BY `AngelTypes`.`name`"); + if ($result === false) { + engelsystem_error("Unable to load user angeltypes."); + } + return $result; } /** @@ -81,11 +85,15 @@ function User_is_AngelType_coordinator($user, $angeltype) { * @param bool $coordinator */ function UserAngelType_update($user_angeltype_id, $coordinator) { - return sql_query(" + $result = sql_query(" UPDATE `UserAngelTypes` SET `coordinator`=" . sql_bool($coordinator) . " WHERE `id`='" . sql_escape($user_angeltype_id) . "' LIMIT 1"); + if ($result === false) { + engelsystem_error("Unable to update coordinator rights."); + } + return $result; } /** @@ -94,10 +102,14 @@ function UserAngelType_update($user_angeltype_id, $coordinator) { * @param int $angeltype_id */ function UserAngelTypes_delete_all($angeltype_id) { - return sql_query(" + $result = sql_query(" DELETE FROM `UserAngelTypes` WHERE `angeltype_id`='" . sql_escape($angeltype_id) . "' AND `confirm_user_id` IS NULL"); + if ($result === false) { + engelsystem_error("Unable to delete all unconfirmed users."); + } + return $result; } /** @@ -107,11 +119,15 @@ function UserAngelTypes_delete_all($angeltype_id) { * @param User $confirm_user */ function UserAngelTypes_confirm_all($angeltype_id, $confirm_user) { - return sql_query(" + $result = sql_query(" UPDATE `UserAngelTypes` SET `confirm_user_id`='" . sql_escape($confirm_user['UID']) . "' WHERE `angeltype_id`='" . sql_escape($angeltype_id) . "' AND `confirm_user_id` IS NULL"); + if ($result === false) { + engelsystem_error("Unable to confirm all users."); + } + return $result; } /** @@ -121,11 +137,15 @@ function UserAngelTypes_confirm_all($angeltype_id, $confirm_user) { * @param User $confirm_user */ function UserAngelType_confirm($user_angeltype_id, $confirm_user) { - return sql_query(" + $result = sql_query(" UPDATE `UserAngelTypes` SET `confirm_user_id`='" . sql_escape($confirm_user['UID']) . "' WHERE `id`='" . sql_escape($user_angeltype_id) . "' LIMIT 1"); + if ($result === false) { + engelsystem_error("Unable to confirm user angeltype."); + } + return $result; } /** @@ -152,7 +172,7 @@ function UserAngelType_create($user, $angeltype) { `user_id`='" . sql_escape($user['UID']) . "', `angeltype_id`='" . sql_escape($angeltype['id']) . "'"); if ($result === false) { - return false; + engelsystem_error("Unable to create user angeltype."); } return sql_id(); } @@ -169,7 +189,7 @@ function UserAngelType($user_angeltype_id) { WHERE `id`='" . sql_escape($user_angeltype_id) . "' LIMIT 1"); if ($angeltype === false) { - return false; + engelsystem_error("Unable to load user angeltype."); } if (count($angeltype) == 0) { return null; @@ -191,7 +211,7 @@ function UserAngelType_by_User_and_AngelType($user, $angeltype) { AND `angeltype_id`='" . sql_escape($angeltype['id']) . "' LIMIT 1"); if ($angeltype === false) { - return false; + engelsystem_error("Unable to load user angeltype."); } if (count($angeltype) == 0) { return null; diff --git a/includes/model/User_model.php b/includes/model/User_model.php index 576bb3f5..ee849f72 100644 --- a/includes/model/User_model.php +++ b/includes/model/User_model.php @@ -113,12 +113,16 @@ function User_is_freeloader($user) { * @param Angeltype $angeltype */ function Users_by_angeltype_inverted($angeltype) { - return sql_select(" + $result = sql_select(" SELECT `User`.* FROM `User` LEFT JOIN `UserAngelTypes` ON (`User`.`UID`=`UserAngelTypes`.`user_id` AND `angeltype_id`='" . sql_escape($angeltype['id']) . "') WHERE `UserAngelTypes`.`id` IS NULL ORDER BY `Nick`"); + if ($result === false) { + engelsystem_error("Unable to load users."); + } + return $result; } /** @@ -165,7 +169,7 @@ function User_validate_Nick($nick) { function User($user_id) { $user_source = sql_select("SELECT * FROM `User` WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1"); if ($user_source === false) { - return false; + engelsystem_error("Unable to load user."); } if (count($user_source) > 0) { return $user_source[0]; |