From 6aaf8d6f533863fd772be6fcda9d1ea64100b70f Mon Sep 17 00:00:00 2001 From: msquare Date: Tue, 26 Dec 2017 10:47:39 +0100 Subject: fix #389: Public Dashboard Stats --- includes/model/Stats.php | 68 ++++++++++++++++++++++++++++++---- includes/view/PublicDashboard_view.php | 8 +++- 2 files changed, 67 insertions(+), 9 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` < ? diff --git a/includes/view/PublicDashboard_view.php b/includes/view/PublicDashboard_view.php index b4064294..894440e2 100644 --- a/includes/view/PublicDashboard_view.php +++ b/includes/view/PublicDashboard_view.php @@ -11,10 +11,16 @@ function public_dashboard_view($stats, $free_shifts) { $needed_angels = ''; if (count($free_shifts) > 0) { - $shift_panels = []; + $shift_panels = [ + '
' + ]; foreach ($free_shifts as $shift) { $shift_panels[] = public_dashboard_shift_render($shift); + if(count($shift_panels) % 4 == 0) { + $shift_panels[] = '
'; + } } + $shift_panels[] = '
'; $needed_angels = div('first', [ div('col-md-12', [ heading(_('Needed angels:'), 1) -- cgit v1.2.3-54-g00ecf