summaryrefslogtreecommitdiff
path: root/includes/model/Stats.php
diff options
context:
space:
mode:
authormsquare <msquare@notrademark.de>2017-12-26 10:47:39 +0100
committermsquare <msquare@notrademark.de>2017-12-26 10:47:39 +0100
commit6aaf8d6f533863fd772be6fcda9d1ea64100b70f (patch)
treeb2c9c64e2a8f4a13ea1514f349e1515785e847db /includes/model/Stats.php
parentde61c88d6de67d12d5de5b6dd0ab6b8c82e5a290 (diff)
fix #389: Public Dashboard Stats
Diffstat (limited to 'includes/model/Stats.php')
-rw-r--r--includes/model/Stats.php68
1 files changed, 60 insertions, 8 deletions
diff --git a/includes/model/Stats.php b/includes/model/Stats.php
index 4ff376c6..94bb70ce 100644
--- a/includes/model/Stats.php
+++ b/includes/model/Stats.php
@@ -73,8 +73,21 @@ function stats_angels_needed_three_hours()
$result = Db::selectOne("
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)
+ GREATEST(0,
+ (
+ SELECT SUM(`count`)
+ FROM `NeededAngelTypes`
+ JOIN `AngelTypes` ON `AngelTypes`.`id`=`NeededAngelTypes`.`angel_type_id`
+ WHERE `AngelTypes`.`show_on_dashboard`=FALSE
+ AND `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`
+ ) - (
+ SELECT COUNT(*) FROM `ShiftEntry`
+ JOIN `AngelTypes` ON `AngelTypes`.`id`=`ShiftEntry`.`TID`
+ WHERE `AngelTypes`.`show_on_dashboard`=FALSE
+ AND `ShiftEntry`.`SID`=`Shifts`.`SID`
+ AND `freeloaded`=0
+ )
+ )
AS `count`
FROM `Shifts`
WHERE `end` > ? AND `start` < ?
@@ -83,8 +96,21 @@ function stats_angels_needed_three_hours()
UNION ALL
SELECT
- (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`room_id`=`Shifts`.`RID`)
- - (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0)
+ GREATEST(0,
+ (
+ SELECT SUM(`count`)
+ FROM `NeededAngelTypes`
+ JOIN `AngelTypes` ON `AngelTypes`.`id`=`NeededAngelTypes`.`angel_type_id`
+ WHERE `AngelTypes`.`show_on_dashboard`=FALSE
+ AND `NeededAngelTypes`.`room_id`=`Shifts`.`RID`
+ ) - (
+ SELECT COUNT(*) FROM `ShiftEntry`
+ JOIN `AngelTypes` ON `AngelTypes`.`id`=`ShiftEntry`.`TID`
+ WHERE `AngelTypes`.`show_on_dashboard`=FALSE
+ AND `ShiftEntry`.`SID`=`Shifts`.`SID`
+ AND `freeloaded`=0
+ )
+ )
AS `count`
FROM `Shifts`
WHERE `end` > ? AND `start` < ?
@@ -116,8 +142,21 @@ function stats_angels_needed_for_nightshifts()
$result = Db::selectOne("
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)
+ GREATEST(0,
+ (
+ SELECT SUM(`count`)
+ FROM `NeededAngelTypes`
+ JOIN `AngelTypes` ON `AngelTypes`.`id`=`NeededAngelTypes`.`angel_type_id`
+ WHERE `AngelTypes`.`show_on_dashboard`=FALSE
+ AND `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`
+ ) - (
+ SELECT COUNT(*) FROM `ShiftEntry`
+ JOIN `AngelTypes` ON `AngelTypes`.`id`=`ShiftEntry`.`TID`
+ WHERE `AngelTypes`.`show_on_dashboard`=FALSE
+ AND `ShiftEntry`.`SID`=`Shifts`.`SID`
+ AND `freeloaded`=0
+ )
+ )
AS `count`
FROM `Shifts`
WHERE `end` > ? AND `start` < ?
@@ -126,8 +165,21 @@ function stats_angels_needed_for_nightshifts()
UNION ALL
SELECT
- (SELECT SUM(`count`) FROM `NeededAngelTypes` WHERE `NeededAngelTypes`.`room_id`=`Shifts`.`RID`)
- - (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0)
+ GREATEST(0,
+ (
+ SELECT SUM(`count`)
+ FROM `NeededAngelTypes`
+ JOIN `AngelTypes` ON `AngelTypes`.`id`=`NeededAngelTypes`.`angel_type_id`
+ WHERE `AngelTypes`.`show_on_dashboard`=FALSE
+ AND `NeededAngelTypes`.`room_id`=`Shifts`.`RID`
+ ) - (
+ SELECT COUNT(*) FROM `ShiftEntry`
+ JOIN `AngelTypes` ON `AngelTypes`.`id`=`ShiftEntry`.`TID`
+ WHERE `AngelTypes`.`show_on_dashboard`=FALSE
+ AND `ShiftEntry`.`SID`=`Shifts`.`SID`
+ AND `freeloaded`=0
+ )
+ )
AS `count`
FROM `Shifts`
WHERE `end` > ? AND `start` < ?