Changed from mysqli to PDO, some refactorings, faster sql queries

1 files changed, 152 insertions, 99 deletions

diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php
index 462b2f65..3f199803 100644
--- a/includes/model/Shifts_model.php
+++ b/includes/model/Shifts_model.php
@@ -1,4 +1,6 @@
 <?php
+
+use Engelsystem\Database\DB;
 use Engelsystem\ShiftsFilter;
 use Engelsystem\ShiftSignupState;
 
@@ -8,8 +10,8 @@ use Engelsystem\ShiftSignupState;
  */
 function Shifts_by_room($room)
 {
-    $result = sql_select('SELECT * FROM `Shifts` WHERE `RID`=' . sql_escape($room['RID']) . ' ORDER BY `start`');
-    if ($result === false) {
+    $result = DB::select('SELECT * FROM `Shifts` WHERE `RID`=? ORDER BY `start`', [$room['RID']]);
+    if (empty($result)) {
         engelsystem_error('Unable to load shifts.');
     }
     return $result;
@@ -21,7 +23,8 @@ function Shifts_by_room($room)
  */
 function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
 {
-    $SQL = "SELECT * FROM (
+    //@TODO
+    $sql = "SELECT * FROM (
       SELECT DISTINCT `Shifts`.*, `ShiftTypes`.`name`, `Room`.`Name` AS `room_name`
       FROM `Shifts`
       JOIN `Room` USING (`RID`)
@@ -47,8 +50,8 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
       AND NOT `Shifts`.`PSID` IS NULL) AS tmp_shifts
           
       ORDER BY `start`";
-    $result = sql_select($SQL);
-    if ($result === false) {
+    $result = DB::select($sql);
+    if (DB::getStm()->errorCode() != '00000') {
         engelsystem_error('Unable to load shifts by filter.');
     }
     return $result;
@@ -56,11 +59,12 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
 
 /**
  * @param ShiftsFilter $shiftsFilter
- * @return array
+ * @return array[]
  */
 function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
 {
-    $SQL = "
+    //@TODO
+    $sql = "
       SELECT
           `NeededAngelTypes`.*,
           `Shifts`.`SID`,
@@ -90,8 +94,8 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
       WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ")
       AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . "
       AND NOT `Shifts`.`PSID` IS NULL";
-    $result = sql_select($SQL);
-    if ($result === false) {
+    $result = DB::select($sql);
+    if (DB::getStm()->errorCode() != '00000') {
         engelsystem_error('Unable to load needed angeltypes by filter.');
     }
     return $result;
@@ -104,40 +108,48 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
  */
 function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype)
 {
-    $result = sql_select("
-      SELECT
-          `NeededAngelTypes`.*,
-          `Shifts`.`SID`,
-          `AngelTypes`.`id`,
-          `AngelTypes`.`name`,
-          `AngelTypes`.`restricted`,
-          `AngelTypes`.`no_self_signup`
-      FROM `Shifts`
-      JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`
-      JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id`
-      WHERE `Shifts`.`SID`=" . sql_escape($shift['SID']) . "
-      AND `AngelTypes`.`id`=" . sql_escape($angeltype['id']) . "
-      AND `Shifts`.`PSID` IS NULL
-          
-      UNION
-          
-      SELECT
-            `NeededAngelTypes`.*,
-            `Shifts`.`SID`,
-            `AngelTypes`.`id`,
-            `AngelTypes`.`name`,
-            `AngelTypes`.`restricted`,
-            `AngelTypes`.`no_self_signup`
-      FROM `Shifts`
-      JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID`
-      JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id`
-      WHERE `Shifts`.`SID`=" . sql_escape($shift['SID']) . "
-      AND `AngelTypes`.`id`=" . sql_escape($angeltype['id']) . "
-      AND NOT `Shifts`.`PSID` IS NULL");
-    if ($result === false) {
+    $result = DB::select('
+          SELECT
+              `NeededAngelTypes`.*,
+              `Shifts`.`SID`,
+              `AngelTypes`.`id`,
+              `AngelTypes`.`name`,
+              `AngelTypes`.`restricted`,
+              `AngelTypes`.`no_self_signup`
+          FROM `Shifts`
+          JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID`
+          JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id`
+          WHERE `Shifts`.`SID`=?
+          AND `AngelTypes`.`id`=?
+          AND `Shifts`.`PSID` IS NULL
+              
+          UNION
+              
+          SELECT
+                `NeededAngelTypes`.*,
+                `Shifts`.`SID`,
+                `AngelTypes`.`id`,
+                `AngelTypes`.`name`,
+                `AngelTypes`.`restricted`,
+                `AngelTypes`.`no_self_signup`
+          FROM `Shifts`
+          JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID`
+          JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id`
+          WHERE `Shifts`.`SID`=?
+          AND `AngelTypes`.`id`=?
+          AND NOT `Shifts`.`PSID` IS NULL
+      ',
+        [
+            $shift['SID'],
+            $angeltype['id'],
+            $shift['SID'],
+            $angeltype['id']
+        ]
+    );
+    if (DB::getStm()->errorCode() != '00000') {
         engelsystem_error('Unable to load needed angeltypes by filter.');
     }
-    if (count($result) == 0) {
+    if (empty($result)) {
         return null;
     }
     return $result[0];
@@ -149,7 +161,8 @@ function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype)
  */
 function ShiftEntries_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
 {
-    $SQL = "
+    // @TODO
+    $sql = "
       SELECT
           `User`.`Nick`,
           `User`.`email`,
@@ -167,8 +180,8 @@ function ShiftEntries_by_ShiftsFilter(ShiftsFilter $shiftsFilter)
       WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ")
       AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . "
       ORDER BY `Shifts`.`start`";
-    $result = sql_select($SQL);
-    if ($result === false) {
+    $result = DB::select($sql);
+    if (DB::getStm()->errorCode() != '00000') {
         engelsystem_error('Unable to load shift entries by filter.');
     }
     return $result;
@@ -374,25 +387,31 @@ function Shift_signup_allowed(
  * Delete a shift by its external id.
  *
  * @param int $shift_psid
- * @return mysqli_result|false
+ * @return bool
  */
 function Shift_delete_by_psid($shift_psid)
 {
-    return sql_query("DELETE FROM `Shifts` WHERE `PSID`='" . sql_escape($shift_psid) . "'");
+    DB::delete('DELETE FROM `Shifts` WHERE `PSID`=?', [$shift_psid]);
+
+    if (DB::getStm()->errorCode() != '00000') {
+        return false;
+    }
+
+    return true;
 }
 
 /**
  * Delete a shift.
  *
  * @param int $shift_id
- * @return mysqli_result
+ * @return bool
  */
 function Shift_delete($shift_id)
 {
     mail_shift_delete(Shift($shift_id));
 
-    $result = sql_query("DELETE FROM `Shifts` WHERE `SID`='" . sql_escape($shift_id) . "'");
-    if ($result === false) {
+    $result = DB::delete('DELETE FROM `Shifts` WHERE `SID`=?', [$shift_id]);
+    if (DB::getStm()->errorCode() != '00000') {
         engelsystem_error('Unable to delete shift.');
     }
     return $result;
@@ -402,7 +421,7 @@ function Shift_delete($shift_id)
  * Update a shift.
  *
  * @param array $shift
- * @return mysqli_result|false
+ * @return bool
  */
 function Shift_update($shift)
 {
@@ -410,36 +429,51 @@ function Shift_update($shift)
     $shift['name'] = ShiftType($shift['shifttype_id'])['name'];
     mail_shift_change(Shift($shift['SID']), $shift);
 
-    return sql_query("
+    return (bool)DB::update('
       UPDATE `Shifts` SET
-      `shifttype_id`='" . sql_escape($shift['shifttype_id']) . "',
-      `start`='" . sql_escape($shift['start']) . "',
-      `end`='" . sql_escape($shift['end']) . "',
-      `RID`='" . sql_escape($shift['RID']) . "',
-      `title`=" . sql_null($shift['title']) . ",
-      `URL`=" . sql_null($shift['URL']) . ",
-      `PSID`=" . sql_null($shift['PSID']) . ",
-      `edited_by_user_id`='" . sql_escape($user['UID']) . "',
-      `edited_at_timestamp`=" . time() . "
-      WHERE `SID`='" . sql_escape($shift['SID']) . "'
-    ");
+      `shifttype_id` = ?,
+      `start` = ?,
+      `end` = ?,
+      `RID` = ?,
+      `title` = ?,
+      `URL` = ?,
+      `PSID` = ?,
+      `edited_by_user_id` = ?,
+      `edited_at_timestamp` = ?
+      WHERE `SID` = ?
+    ',
+        [
+            $shift['shifttype_id'],
+            $shift['start'],
+            $shift['end'],
+            $shift['RID'],
+            $shift['title'],
+            $shift['URL'],
+            $shift['PSID'],
+            $user['UID'],
+            time(),
+            $shift['SID']
+        ]
+    );
 }
 
 /**
  * Update a shift by its external id.
  *
  * @param array $shift
- * @return mysqli_result|false|null
+ * @return bool|null
  */
 function Shift_update_by_psid($shift)
 {
-    $shift_source = sql_select("SELECT `SID` FROM `Shifts` WHERE `PSID`=" . $shift['PSID']);
-    if ($shift_source === false) {
+    $shift_source = DB::select('SELECT `SID` FROM `Shifts` WHERE `PSID`=?', [$shift['PSID']]);
+    if (DB::getStm()->errorCode() != '00000') {
         return false;
     }
-    if (count($shift_source) == 0) {
+
+    if (empty($shift_source)) {
         return null;
     }
+
     $shift['SID'] = $shift_source[0]['SID'];
     return Shift_update($shift);
 }
@@ -453,22 +487,36 @@ function Shift_update_by_psid($shift)
 function Shift_create($shift)
 {
     global $user;
-    $result = sql_query("
-      INSERT INTO `Shifts` SET
-      `shifttype_id`='" . sql_escape($shift['shifttype_id']) . "',
-      `start`='" . sql_escape($shift['start']) . "',
-      `end`='" . sql_escape($shift['end']) . "',
-      `RID`='" . sql_escape($shift['RID']) . "',
-      `title`=" . sql_null($shift['title']) . ",
-      `URL`=" . sql_null($shift['URL']) . ",
-      `PSID`=" . sql_null($shift['PSID']) . ",
-      `created_by_user_id`='" . sql_escape($user['UID']) . "',
-      `created_at_timestamp`=" . time()
+    DB::insert('
+          INSERT INTO `Shifts` (
+              `shifttype_id`,
+              `start`,
+              `end`,
+              `RID`,
+              `title`,
+              `URL`,
+              `PSID`,
+              `created_by_user_id`,
+              `created_at_timestamp`
+          )
+           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
+        ',
+        [
+            $shift['shifttype_id'],
+            $shift['start'],
+            $shift['end'],
+            $shift['RID'],
+            $shift['title'],
+            $shift['URL'],
+            $shift['PSID'],
+            $user['UID'],
+            time(),
+        ]
     );
-    if ($result === false) {
+    if (DB::getStm()->errorCode() != '00000') {
         return false;
     }
-    return sql_id();
+    return DB::getPdo()->lastInsertId();
 }
 
 /**
@@ -480,7 +528,7 @@ function Shift_create($shift)
  */
 function Shifts_by_user($user, $include_freeload_comments = false)
 {
-    $result = sql_select('
+    $result = DB::select('
           SELECT `ShiftTypes`.`id` AS `shifttype_id`, `ShiftTypes`.`name`,
           `ShiftEntry`.`id`, `ShiftEntry`.`SID`, `ShiftEntry`.`TID`, `ShiftEntry`.`UID`, `ShiftEntry`.`freeloaded`, `ShiftEntry`.`Comment`,
           ' . ($include_freeload_comments ? '`ShiftEntry`.`freeload_comment`, ' : '') . '
@@ -489,10 +537,14 @@ function Shifts_by_user($user, $include_freeload_comments = false)
           JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) 
           JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`)
           JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) 
-          WHERE `UID`=\'' . sql_escape($user['UID']) . '\' 
+          WHERE `UID` = ? 
           ORDER BY `start`
-      ');
-    if ($result === false) {
+      ',
+        [
+            $user['UID']
+        ]
+    );
+    if (DB::getStm()->errorCode() != '00000') {
         engelsystem_error('Unable to load users shifts.');
     }
     return $result;
@@ -506,14 +558,13 @@ function Shifts_by_user($user, $include_freeload_comments = false)
  */
 function Shift($shift_id)
 {
-    $shifts_source = sql_select("
+    $shifts_source = DB::select('
       SELECT `Shifts`.*, `ShiftTypes`.`name`
       FROM `Shifts` 
       JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`)
-      WHERE `SID`='" . sql_escape($shift_id) . "'");
-    $shiftsEntry_source = sql_select("SELECT `id`, `TID` , `UID` , `freeloaded` FROM `ShiftEntry` WHERE `SID`='" . sql_escape($shift_id) . "'");
+      WHERE `SID`=?', [$shift_id]);
 
-    if ($shifts_source === false) {
+    if (DB::getStm()->errorCode() != '00000') {
         engelsystem_error('Unable to load shift.');
     }
 
@@ -523,16 +574,21 @@ function Shift($shift_id)
 
     $result = $shifts_source[0];
 
+    $shiftsEntry_source = DB::select('
+        SELECT `id`, `TID` , `UID` , `freeloaded`
+        FROM `ShiftEntry`
+        WHERE `SID`=?', [$shift_id]);
+
     $result['ShiftEntry'] = $shiftsEntry_source;
     $result['NeedAngels'] = [];
 
-    $temp = NeededAngelTypes_by_shift($shift_id);
-    foreach ($temp as $e) {
+    $angelTypes = NeededAngelTypes_by_shift($shift_id);
+    foreach ($angelTypes as $type) {
         $result['NeedAngels'][] = [
-            'TID'        => $e['angel_type_id'],
-            'count'      => $e['count'],
-            'restricted' => $e['restricted'],
-            'taken'      => $e['taken']
+            'TID'        => $type['angel_type_id'],
+            'count'      => $type['count'],
+            'restricted' => $type['restricted'],
+            'taken'      => $type['taken']
         ];
     }
 
@@ -546,22 +602,19 @@ function Shift($shift_id)
  */
 function Shifts()
 {
-    $shifts_source = sql_select('
+    $shifts_source = DB::select('
         SELECT `ShiftTypes`.`name`, `Shifts`.*, `Room`.`RID`, `Room`.`Name` AS `room_name` 
         FROM `Shifts`
         JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`)
         JOIN `Room` ON `Room`.`RID` = `Shifts`.`RID`
     ');
-    if ($shifts_source === false) {
+
+    if (DB::getStm()->errorCode() != '00000') {
         return false;
     }
 
     foreach ($shifts_source as &$shift) {
         $needed_angeltypes = NeededAngelTypes_by_shift($shift['SID']);
-        if ($needed_angeltypes === false) {
-            return false;
-        }
-
         $shift['angeltypes'] = $needed_angeltypes;
     }