fix dashboard using needed angeltypes from room config too for frab shifts

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
     ]);