summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--includes/model/Shifts_model.php24
-rw-r--r--includes/model/Stats.php79
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
]);