From b05b235cf821ec2d9fc84779d09db71dc1bf7797 Mon Sep 17 00:00:00 2001
From: msquare <msquare@notrademark.de>
Date: Tue, 27 Dec 2016 16:02:13 +0100
Subject: fix slow query

---
 db/update.sql                   |  1 +
 includes/model/Shifts_model.php | 28 ++++++++++++----------------
 2 files changed, 13 insertions(+), 16 deletions(-)

diff --git a/db/update.sql b/db/update.sql
index 6bfe0c4a..dd203a86 100644
--- a/db/update.sql
+++ b/db/update.sql
@@ -23,3 +23,4 @@ INSERT INTO `Privileges` (`id`, `name`, `desc`) VALUES (NULL, 'shiftentry_edit_a
 
 -- DB Performance
 ALTER TABLE `Shifts` ADD INDEX(`start`);
+ALTER TABLE `NeededAngelTypes` ADD INDEX(`count`);
\ No newline at end of file
diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php
index ed484e1b..74cfa356 100644
--- a/includes/model/Shifts_model.php
+++ b/includes/model/Shifts_model.php
@@ -11,7 +11,15 @@ function Shifts_by_room($room) {
 }
 
 function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter, $user) {
-  $SQL = "SELECT DISTINCT `Shifts`.*, `ShiftTypes`.`name`, `Room`.`Name` as `room_name`, nat2.`special_needs` > 0 AS 'has_special_needs'
+  $SQL="SELECT `Shifts`.*, `ShiftTypes`.`name`, `Room`.`Name` as `room_name`
+      FROM `Shifts`
+      JOIN `Room` USING (`RID`)
+      JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id`
+      WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ")
+      AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . "
+      ORDER BY `Shifts`.`start`";
+  /**
+  $SQL = "SELECT DISTINCT `Shifts`.*, `ShiftTypes`.`name`, `Room`.`Name` as `room_name`
   FROM `Shifts`
   INNER JOIN `Room` USING (`RID`)
   INNER JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`)
@@ -42,30 +50,18 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter, $user) {
     if ($shiftsFilter->getFilled()[0] == ShiftsFilter::FILLED_FREE) {
       $SQL .= "
       AND (
-          nat.`count` > entries.`count` OR entries.`count` IS NULL 
-          OR EXISTS (
-              SELECT `SID` 
-              FROM `ShiftEntry` 
-              WHERE `UID` = '" . sql_escape($user['UID']) . "' 
-              AND `ShiftEntry`.`SID` = `Shifts`.`SID`
-          )
+          nat.`count` > entries.`count` OR entries.`count` IS NULL
       )";
     } elseif ($_SESSION['user_shifts']['filled'][0] == ShiftsFilter::FILLED_FILLED) {
       $SQL .= "
       AND (
-          nat.`count` <= entries.`count` 
-          OR EXISTS (
-              SELECT `SID` 
-              FROM `ShiftEntry` 
-              WHERE `UID` = '" . sql_escape($user['UID']) . "' 
-              AND `ShiftEntry`.`SID` = `Shifts`.`SID`
-          )
+          nat.`count` <= entries.`count`
       )";
     }
   }
   $SQL .= "
   ORDER BY `start`";
-  
+  */
   $result = sql_select($SQL);
   if ($result === false) {
     engelsystem_error("Unable to load shifts by filter.");
-- 
cgit v1.2.3-54-g00ecf