diff options
| -rw-r--r-- | includes/model/Shifts_model.php | 24 | ||||
| -rw-r--r-- | includes/model/Stats.php | 79 |
2 files changed, 79 insertions, 24 deletions
diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index b0d82a5b..2a77fd35 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -32,14 +32,26 @@ function Shifts_by_angeltype($angeltype) { function Shifts_free($start, $end) { $shifts = Db::select(" - SELECT * - FROM `Shifts` - 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) - ORDER BY `start` + SELECT * FROM ( + SELECT * + FROM `Shifts` + 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) + + UNION + + SELECT * + FROM `Shifts` + 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) + ) as `tmp` + ORDER BY `tmp`.`start` ", [ $start, + $end, + $start, $end ]); $free_shifts = []; diff --git a/includes/model/Stats.php b/includes/model/Stats.php index c6208dc3..5bca0aff 100644 --- a/includes/model/Stats.php +++ b/includes/model/Stats.php @@ -27,12 +27,25 @@ function stats_currently_working() function stats_hours_to_work() { $result = Db::selectOne(" - SELECT ROUND(SUM( - (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`) - * (`Shifts`.`end` - `Shifts`.`start`)/3600 - )) as `count` - FROM `Shifts` - WHERE `end` >= ?", [ + SELECT ROUND(SUM(`count`)) as `count` FROM ( + SELECT + (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`) + * (`Shifts`.`end` - `Shifts`.`start`)/3600 as `count` + FROM `Shifts` + WHERE `end` >= ? + AND `Shifts`.`PSID` IS NULL + + UNION + + SELECT + (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`room_id`=`Shifts`.`RID`) + * (`Shifts`.`end` - `Shifts`.`start`)/3600 as `count` + FROM `Shifts` + WHERE `end` >= ? + AND NOT `Shifts`.`PSID` IS NULL + ) as `tmp` + ", [ + time(), time() ]); if (empty($result['count'])) { @@ -49,12 +62,27 @@ function stats_angels_needed_three_hours() $now = time(); $in3hours = $now + 3 * 60 * 60; $result = Db::selectOne(" - SELECT SUM( - (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`) - - (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0) - ) as `count` - FROM `Shifts` - WHERE `end` > ? AND `start` < ?", [ + SELECT SUM(`count`) as `count` FROM ( + SELECT + (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`) + - (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0) + as `count` + FROM `Shifts` + WHERE `end` > ? AND `start` < ? + AND `Shifts`.`PSID` IS NULL + + UNION + + SELECT + (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`room_id`=`Shifts`.`RID`) + - (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0) + as `count` + FROM `Shifts` + WHERE `end` > ? AND `start` < ? + AND NOT `Shifts`.`PSID` IS NULL + ) as `tmp`", [ + $now, + $in3hours, $now, $in3hours ]); @@ -72,12 +100,27 @@ function stats_angels_needed_for_nightshifts() $night_start = parse_date('Y-m-d H:i', date('Y-m-d', time() + 12 * 60 * 60) . ' 02:00'); $night_end = $night_start + 6 * 60 * 60; $result = Db::selectOne(" - SELECT SUM( - (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`) - - (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0) - ) as `count` - FROM `Shifts` - WHERE `end` > ? AND `start` < ?", [ + SELECT SUM(`count`) as `count` FROM ( + SELECT + (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`) + - (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0) + as `count` + FROM `Shifts` + WHERE `end` > ? AND `start` < ? + AND `Shifts`.`PSID` IS NULL + + UNION + + SELECT + (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`room_id`=`Shifts`.`RID`) + - (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0) + as `count` + FROM `Shifts` + WHERE `end` > ? AND `start` < ? + AND NOT `Shifts`.`PSID` IS NULL + ) as `tmp`", [ + $night_start, + $night_end, $night_start, $night_end ]); |