finish basic public dashboard

2 files changed, 117 insertions, 0 deletions

diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php
index 1fc7fe89..b0d82a5b 100644
--- a/includes/model/Shifts_model.php
+++ b/includes/model/Shifts_model.php
@@ -27,6 +27,29 @@ function Shifts_by_angeltype($angeltype) {
 }
 
 /**
+ * Returns every shift with needed angels in the given time range.
+ */
+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`
+        ", [
+                $start,
+                $end
+            ]);
+    $free_shifts = [];
+    foreach ($shifts as $shift) {
+        $free_shifts[] = Shift($shift['SID']);
+    }
+    return $free_shifts;
+}
+
+/**
  * Returns all shifts with a PSID (from frab import)
  */
 function Shifts_from_frab() {
diff --git a/includes/model/Stats.php b/includes/model/Stats.php
new file mode 100644
index 00000000..5618cdc6
--- /dev/null
+++ b/includes/model/Stats.php
@@ -0,0 +1,94 @@
+<?php
+use Engelsystem\Database\Db;
+
+/**
+ * Returns the number of angels currently working.
+ */
+function stats_currently_working()
+{
+    $result = Db::selectOne("
+        SELECT SUM(
+            (SELECT COUNT(*) FROM `ShiftEntry` WHERE `ShiftEntry`.`SID`=`Shifts`.`SID` AND `freeloaded`=0)
+            ) as `count`
+        FROM `Shifts`
+        WHERE (`end` >= ? AND `start` <= ?)", [
+        time(),
+        time()
+    ]);
+    if (empty($result['count'])) {
+        return '-';
+    }
+    return $result['count'];
+}
+
+/**
+ * Return the number of hours still to work.
+ */
+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` >= ?", [
+        time()
+    ]);
+    if (empty($result['count'])) {
+        return '-';
+    }
+    return $result['count'];
+}
+
+/**
+ * Returns the number of needed angels in the next 3 hours
+ */
+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 `end` < ?) OR (`start` > ? AND `start` < ?))", [
+        $now,
+        $in3hours,
+        $now,
+        $in3hours
+    ]);
+    if (empty($result['count'])) {
+        return '-';
+    }
+    return $result['count'];
+}
+
+/**
+ * Returns the number of needed angels for nightshifts (between 2 and 8)
+ */
+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 `end` < ?) OR (`start` > ? AND `start` < ?))", [
+        $night_start,
+        $night_end,
+        $night_start,
+        $night_end
+    ]);
+    if (empty($result['count'])) {
+        return '-';
+    }
+    return $result['count'];
+}
+
+?>
\ No newline at end of file