summaryrefslogtreecommitdiff
path: root/includes/model
diff options
context:
space:
mode:
Diffstat (limited to 'includes/model')
-rw-r--r--includes/model/Room_model.php30
-rw-r--r--includes/model/Shifts_model.php75
-rw-r--r--includes/model/Stats.php18
3 files changed, 38 insertions, 85 deletions
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
@@ -62,35 +62,20 @@ function Room_delete($room_id)
}
/**
- * 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 1f957c84..756d9284 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`
", [
@@ -70,16 +74,6 @@ function Shifts_free($start, $end)
}
/**
- * 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'],
@@ -495,16 +495,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.
*
* @param int $shift_id
@@ -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']
@@ -556,25 +544,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.
*
* @param array $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,
generated by cgit v1.2.3-54-g00ecf (git 2.45.0) at 2024-05-18 08:04:26 +0000