From 42721e95726559b4a601240bb5b0fe4e5d755b2a Mon Sep 17 00:00:00 2001 From: Igor Scheller Date: Wed, 27 Nov 2019 23:43:21 +0100 Subject: Added Schedule parsing and replaced old Fahrplan importer Resolves #553 (Change Frab Import from xCal to XML) Resolves #538 (Feature Request: Multi Frab Import) --- includes/model/Room_model.php | 30 +++-------------- includes/model/Shifts_model.php | 75 ++++++++++++----------------------------- includes/model/Stats.php | 18 ++++++---- 3 files changed, 38 insertions(+), 85 deletions(-) (limited to 'includes/model') diff --git a/includes/model/Room_model.php b/includes/model/Room_model.php index b29f68fe..f9eaf31d 100644 --- a/includes/model/Room_model.php +++ b/includes/model/Room_model.php @@ -61,36 +61,21 @@ function Room_delete($room_id) engelsystem_log('Room deleted: ' . $room['Name']); } -/** - * Delete a room by its name - * - * @param string $name - */ -function Room_delete_by_name($name) -{ - DB::delete('DELETE FROM `Room` WHERE `Name` = ?', [ - $name - ]); - engelsystem_log('Room deleted: ' . $name); -} - /** * Create a new room * * @param string $name Name of the room - * @param boolean $from_frab Is this a frab imported room? * @param string $map_url URL to a map tha can be displayed in an iframe * @param string description Markdown description * @return false|int */ -function Room_create($name, $from_frab, $map_url, $description) +function Room_create($name, $map_url, $description) { DB::insert(' - INSERT INTO `Room` (`Name`, `from_frab`, `map_url`, `description`) - VALUES (?, ?, ?, ?) + INSERT INTO `Room` (`Name`, `map_url`, `description`) + VALUES (?, ?, ?) ', [ $name, - (int)$from_frab, $map_url, $description ]); @@ -98,7 +83,6 @@ function Room_create($name, $from_frab, $map_url, $description) engelsystem_log( 'Room created: ' . $name - . ', frab import: ' . ($from_frab ? 'Yes' : '') . ', map_url: ' . $map_url . ', description: ' . $description ); @@ -107,28 +91,25 @@ function Room_create($name, $from_frab, $map_url, $description) } /** - * update a room + * Update a room * * @param int $room_id The rooms id * @param string $name Name of the room - * @param boolean $from_frab Is this a frab imported room? * @param string $map_url URL to a map tha can be displayed in an iframe * @param string $description Markdown description * @return int */ -function Room_update($room_id, $name, $from_frab, $map_url, $description) +function Room_update($room_id, $name, $map_url, $description) { $result = DB::update(' UPDATE `Room` SET `Name`=?, - `from_frab`=?, `map_url`=?, `description`=? WHERE `RID`=? LIMIT 1', [ $name, - (int)$from_frab, $map_url, $description, $room_id @@ -136,7 +117,6 @@ function Room_update($room_id, $name, $from_frab, $map_url, $description) engelsystem_log( 'Room updated: ' . $name . - ', frab import: ' . ($from_frab ? 'Yes' : '') . ', map_url: ' . $map_url . ', description: ' . $description ); diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index 01295fbc..fc88908d 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -14,17 +14,19 @@ function Shifts_by_angeltype($angeltype) return DB::select(' SELECT DISTINCT `Shifts`.* FROM `Shifts` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id` = `Shifts`.`SID` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `NeededAngelTypes`.`angel_type_id` = ? AND `NeededAngelTypes`.`count` > 0 - AND `Shifts`.`PSID` IS NULL + AND s.shift_id IS NULL UNION SELECT DISTINCT `Shifts`.* FROM `Shifts` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id` = `Shifts`.`RID` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `NeededAngelTypes`.`angel_type_id` = ? AND `NeededAngelTypes`.`count` > 0 - AND NOT `Shifts`.`PSID` IS NULL + AND NOT s.shift_id IS NULL ', [$angeltype['id'], $angeltype['id']]); } @@ -41,19 +43,21 @@ function Shifts_free($start, $end) SELECT * FROM ( SELECT * FROM `Shifts` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE (`end` > ? AND `start` < ?) AND (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`) > (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0) - AND `Shifts`.`PSID` IS NULL + AND s.shift_id IS NULL UNION SELECT * FROM `Shifts` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE (`end` > ? AND `start` < ?) AND (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`room_id`=`Shifts`.`RID`) > (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0) - AND NOT `Shifts`.`PSID` IS NULL + AND NOT s.shift_id IS NULL ) AS `tmp` ORDER BY `tmp`.`start` ", [ @@ -69,16 +73,6 @@ function Shifts_free($start, $end) return $free_shifts; } -/** - * Returns all shifts with a PSID (from frab import) - * - * @return array[] - */ -function Shifts_from_frab() -{ - return DB::select('SELECT * FROM `Shifts` WHERE `PSID` IS NOT NULL ORDER BY `start`'); -} - /** * @param array|int $room * @return array[] @@ -103,11 +97,12 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter) JOIN `Room` USING (`RID`) JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id` = `Shifts`.`SID` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `Shifts`.`RID` IN (' . implode(',', $shiftsFilter->getRooms()) . ') AND `start` BETWEEN ? AND ? AND `NeededAngelTypes`.`angel_type_id` IN (' . implode(',', $shiftsFilter->getTypes()) . ') AND `NeededAngelTypes`.`count` > 0 - AND `Shifts`.`PSID` IS NULL + AND s.shift_id IS NULL UNION @@ -116,11 +111,12 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter) JOIN `Room` USING (`RID`) JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `Shifts`.`RID` IN (' . implode(',', $shiftsFilter->getRooms()) . ') AND `start` BETWEEN ? AND ? AND `NeededAngelTypes`.`angel_type_id` IN (' . implode(',', $shiftsFilter->getTypes()) . ') AND `NeededAngelTypes`.`count` > 0 - AND NOT `Shifts`.`PSID` IS NULL) AS tmp_shifts + AND NOT s.shift_id IS NULL) AS tmp_shifts ORDER BY `room_name`, `start`'; @@ -152,9 +148,10 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter) FROM `Shifts` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID` JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `Shifts`.`RID` IN (' . implode(',', $shiftsFilter->getRooms()) . ') AND `start` BETWEEN ? AND ? - AND `Shifts`.`PSID` IS NULL + AND s.shift_id IS NULL UNION @@ -168,9 +165,10 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter) FROM `Shifts` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `Shifts`.`RID` IN (' . implode(',', $shiftsFilter->getRooms()) . ') AND `start` BETWEEN ? AND ? - AND NOT `Shifts`.`PSID` IS NULL'; + AND NOT s.shift_id IS NULL'; return DB::select( $sql, @@ -201,9 +199,10 @@ function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype) FROM `Shifts` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID` JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `Shifts`.`SID`=? AND `AngelTypes`.`id`=? - AND `Shifts`.`PSID` IS NULL + AND s.shift_id IS NULL UNION @@ -217,9 +216,10 @@ function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype) FROM `Shifts` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `Shifts`.`SID`=? AND `AngelTypes`.`id`=? - AND NOT `Shifts`.`PSID` IS NULL + AND NOT s.shift_id IS NULL ', [ $shift['SID'], @@ -494,16 +494,6 @@ function Shift_signup_allowed( ); } -/** - * Delete a shift by its external id. - * - * @param int $shift_psid - */ -function Shift_delete_by_psid($shift_psid) -{ - DB::delete('DELETE FROM `Shifts` WHERE `PSID`=?', [$shift_psid]); -} - /** * Delete a shift. * @@ -535,7 +525,6 @@ function Shift_update($shift) `RID` = ?, `title` = ?, `URL` = ?, - `PSID` = ?, `edited_by_user_id` = ?, `edited_at_timestamp` = ? WHERE `SID` = ? @@ -547,7 +536,6 @@ function Shift_update($shift) $shift['RID'], $shift['title'], $shift['URL'], - $shift['PSID'], $user->id, time(), $shift['SID'] @@ -555,25 +543,6 @@ function Shift_update($shift) ); } -/** - * Update a shift by its external id. - * - * @param array $shift - * @return int - * @throws Exception - */ -function Shift_update_by_psid($shift) -{ - $shift_source = DB::selectOne('SELECT `SID` FROM `Shifts` WHERE `PSID`=?', [$shift['PSID']]); - - if (empty($shift_source)) { - throw new Exception('Shift not found.'); - } - - $shift['SID'] = $shift_source['SID']; - return Shift_update($shift); -} - /** * Create a new shift. * @@ -590,12 +559,11 @@ function Shift_create($shift) `RID`, `title`, `URL`, - `PSID`, `created_by_user_id`, `edited_at_timestamp`, `created_at_timestamp` ) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ', [ $shift['shifttype_id'], @@ -604,7 +572,6 @@ function Shift_create($shift) $shift['RID'], $shift['title'], $shift['URL'], - $shift['PSID'], auth()->user()->id, time(), time(), diff --git a/includes/model/Stats.php b/includes/model/Stats.php index c8342d82..e355b064 100644 --- a/includes/model/Stats.php +++ b/includes/model/Stats.php @@ -39,8 +39,9 @@ function stats_hours_to_work() (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`) * (`Shifts`.`end` - `Shifts`.`start`)/3600 AS `count` FROM `Shifts` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `end` >= ? - AND `Shifts`.`PSID` IS NULL + AND s.shift_id IS NULL UNION ALL @@ -48,8 +49,9 @@ function stats_hours_to_work() (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`room_id`=`Shifts`.`RID`) * (`Shifts`.`end` - `Shifts`.`start`)/3600 AS `count` FROM `Shifts` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `end` >= ? - AND NOT `Shifts`.`PSID` IS NULL + AND NOT s.shift_id IS NULL ) AS `tmp` ", [ time(), @@ -90,8 +92,9 @@ function stats_angels_needed_three_hours() ) AS `count` FROM `Shifts` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `end` > ? AND `start` < ? - AND `Shifts`.`PSID` IS NULL + AND s.shift_id IS NULL UNION ALL @@ -113,8 +116,9 @@ function stats_angels_needed_three_hours() ) AS `count` FROM `Shifts` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `end` > ? AND `start` < ? - AND NOT `Shifts`.`PSID` IS NULL + AND NOT s.shift_id IS NULL ) AS `tmp`", [ $now, $in3hours, @@ -163,8 +167,9 @@ function stats_angels_needed_for_nightshifts() ) AS `count` FROM `Shifts` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `end` > ? AND `start` < ? - AND `Shifts`.`PSID` IS NULL + AND s.shift_id IS NULL UNION ALL @@ -186,8 +191,9 @@ function stats_angels_needed_for_nightshifts() ) AS `count` FROM `Shifts` + LEFT JOIN schedule_shift AS s on Shifts.SID = s.shift_id WHERE `end` > ? AND `start` < ? - AND NOT `Shifts`.`PSID` IS NULL + AND NOT s.shift_id IS NULL ) AS `tmp`", [ $night_start, $night_end, -- cgit v1.2.3-54-g00ecf