From 928e707015fa5b9b3e2dd52c9493552ae1218911 Mon Sep 17 00:00:00 2001
From: msquare <msquare@notrademark.de>
Date: Fri, 24 Nov 2017 12:01:19 +0100
Subject: add shift calendar to angeltype view

---
 includes/model/Room_model.php   | 11 +++++++++++
 includes/model/Shifts_model.php | 25 ++++++++++++++++++++++++-
 2 files changed, 35 insertions(+), 1 deletion(-)

(limited to 'includes/model')

diff --git a/includes/model/Room_model.php b/includes/model/Room_model.php
index 8425e5ad..74a08be4 100644
--- a/includes/model/Room_model.php
+++ b/includes/model/Room_model.php
@@ -13,6 +13,17 @@ function Rooms($show_all = false)
     return DB::select('SELECT * FROM `Room`' . ($show_all ? '' : ' WHERE `show`=\'Y\'') . ' ORDER BY `Name`');
 }
 
+/**
+ * Returns Room id array
+ *
+ * @return array
+ */
+function Room_ids()
+{
+    $result = DB::select('SELECT `RID` FROM `Room`');
+    return select_array($result, 'RID', 'RID');
+}
+
 /**
  * Delete a room
  *
diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php
index ef02aaab..8a1b69f9 100644
--- a/includes/model/Shifts_model.php
+++ b/includes/model/Shifts_model.php
@@ -4,6 +4,29 @@ use Engelsystem\Database\DB;
 use Engelsystem\ShiftsFilter;
 use Engelsystem\ShiftSignupState;
 
+/**
+ * @param array $angeltype
+ * @return array
+ */
+function Shifts_by_angeltype($angeltype) {
+    return DB::select('
+        SELECT DISTINCT `Shifts`.* FROM `Shifts`
+        JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id` = `Shifts`.`SID`
+        WHERE `NeededAngelTypes`.`angel_type_id` = ?
+        AND `NeededAngelTypes`.`count` > 0
+        AND `Shifts`.`PSID` IS NULL
+        
+        UNION
+        
+        SELECT DISTINCT `Shifts`.* FROM `Shifts`
+        JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id` = `Shifts`.`RID`
+        WHERE `NeededAngelTypes`.`angel_type_id` = ?
+        AND `NeededAngelTypes`.`count` > 0
+        AND NOT `Shifts`.`PSID` IS NULL
+        ', [$angeltype['id'], $angeltype['id']]);
+}
+
+
 /**
  * @param array $room
  * @return array
@@ -24,7 +47,7 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
       FROM `Shifts`
       JOIN `Room` USING (`RID`)
       JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id`
-      JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`
+      JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id` = `Shifts`.`SID`
       WHERE `Shifts`.`RID` IN (' . implode(',', $shiftsFilter->getRooms()) . ')
       AND `start` BETWEEN ? AND ?
       AND `NeededAngelTypes`.`angel_type_id` IN (' . implode(',', $shiftsFilter->getTypes()) . ')
-- 
cgit v1.2.3-54-g00ecf