From 9a3ad8883403949a59e8935497a548ec536f1d40 Mon Sep 17 00:00:00 2001
From: Igor Scheller <igor.scheller@igorshp.de>
Date: Sat, 21 Jan 2017 13:58:53 +0100
Subject: Changed from mysqli to PDO, some refactorings, faster sql queries

---
 includes/controller/shift_entries_controller.php   | 80 ++++++++++++++--------
 includes/controller/shifts_controller.php          | 22 ++++--
 includes/controller/shifttypes_controller.php      | 11 +--
 includes/controller/user_angeltypes_controller.php |  3 -
 includes/controller/users_controller.php           | 28 ++++----
 5 files changed, 85 insertions(+), 59 deletions(-)

(limited to 'includes/controller')

diff --git a/includes/controller/shift_entries_controller.php b/includes/controller/shift_entries_controller.php
index 8cec5f10..0df1f430 100644
--- a/includes/controller/shift_entries_controller.php
+++ b/includes/controller/shift_entries_controller.php
@@ -1,5 +1,7 @@
 <?php
 
+use Engelsystem\Database\DB;
+
 /**
  * Sign up for a shift.
  *
@@ -17,7 +19,7 @@ function shift_entry_add_controller()
     }
 
     // Locations laden
-    $rooms = sql_select('SELECT * FROM `Room` WHERE `show`=\'Y\' ORDER BY `Name`');
+    $rooms = Rooms();
     $room_array = [];
     foreach ($rooms as $room) {
         $room_array[$room['RID']] = $room['Name'];
@@ -39,22 +41,24 @@ function shift_entry_add_controller()
     if (in_array('user_shifts_admin', $privileges) || in_array('shiftentry_edit_angeltype_supporter', $privileges)) {
         $type = AngelType($type_id);
     } else {
-        $type = sql_select("
-            SELECT * FROM `UserAngelTypes`
+        // TODO: Move queries to model
+        $type = DB::select('
+            SELECT *
+            FROM `UserAngelTypes`
             JOIN `AngelTypes` ON (`UserAngelTypes`.`angeltype_id` = `AngelTypes`.`id`)
-            WHERE `AngelTypes`.`id` = '" . sql_escape($type_id) . "'
+            WHERE `AngelTypes`.`id` = ?
             AND (
                 `AngelTypes`.`restricted` = 0
                 OR (
-                    `UserAngelTypes`.`user_id` = '" . sql_escape($user['UID']) . "'
+                    `UserAngelTypes`.`user_id` = ?
                     AND NOT `UserAngelTypes`.`confirm_user_id` IS NULL
                 )
             )
-        ");
-        $type = $type[0];
+        ', [$type_id, $user['UID']]);
+        $type = array_shift($type);
     }
 
-    if ($type == null) {
+    if (empty($type)) {
         redirect(page_link_to('user_shifts'));
     }
 
@@ -93,25 +97,32 @@ function shift_entry_add_controller()
         if (in_array('user_shifts_admin', $privileges) || in_array('shiftentry_edit_angeltype_supporter',
                 $privileges)
         ) {
-            if (sql_num_query("SELECT * FROM `User` WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1") == 0) {
+
+            if (count(DB::select('SELECT `UID` FROM `User` WHERE `UID`=? LIMIT 1', [$user_id])) == 0) {
                 redirect(page_link_to('user_shifts'));
             }
 
             if (
                 isset($_REQUEST['angeltype_id'])
                 && test_request_int('angeltype_id')
-                && sql_num_query("SELECT * FROM `AngelTypes` WHERE `id`='" . sql_escape($_REQUEST['angeltype_id']) . "' LIMIT 1") > 0
+                && count(DB::select(
+                    'SELECT `id` FROM `AngelTypes` WHERE `id`=? LIMIT 1',
+                    [$_REQUEST['angeltype_id']]
+                )) > 0
             ) {
                 $selected_type_id = $_REQUEST['angeltype_id'];
             }
         }
 
-        if (sql_num_query("SELECT * FROM `ShiftEntry` WHERE `SID`='" . sql_escape($shift['SID']) . "' AND `UID` = '" . sql_escape($user_id) . "'")) {
+        if (count(DB::select(
+            'SELECT `id` FROM `ShiftEntry` WHERE `SID`= ? AND `UID` = ?',
+            [$shift['SID'], $user_id]))
+        ) {
             return error("This angel does already have an entry for this shift.", true);
         }
 
-        $freeloaded = $shift['freeloaded'];
-        $freeload_comment = $shift['freeload_comment'];
+        $freeloaded = isset($shift['freeloaded']) ? $shift['freeloaded'] : false;
+        $freeload_comment = isset($shift['freeload_comment']) ? $shift['freeload_comment'] : '';
         if (in_array("user_shifts_admin", $privileges)) {
             $freeloaded = isset($_REQUEST['freeloaded']);
             $freeload_comment = strip_request_item_nl('freeload_comment');
@@ -132,27 +143,40 @@ function shift_entry_add_controller()
 
         if (
             $type['restricted'] == 0
-            && sql_num_query("
-              SELECT * FROM `UserAngelTypes`
+            && count(DB::select('
+              SELECT `id` FROM `UserAngelTypes`
               INNER JOIN `AngelTypes` ON `AngelTypes`.`id` = `UserAngelTypes`.`angeltype_id`
-              WHERE `angeltype_id` = '" . sql_escape($selected_type_id) . "' 
-              AND `user_id` = '" . sql_escape($user_id) . "'
-            ") == 0
+              WHERE `angeltype_id` = ?
+              AND `user_id` = ?
+            ', [$selected_type_id, $user_id])) == 0
         ) {
-            sql_query("INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`) VALUES ('" . sql_escape($user_id) . "', '" . sql_escape($selected_type_id) . "')");
+            DB::insert(
+                'INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`) VALUES (?, ?)',
+                [$user_id, $selected_type_id]
+            );
         }
 
         $user_source = User($user_id);
-        engelsystem_log('User ' . User_Nick_render($user_source) . ' signed up for shift ' . $shift['name'] . ' from ' . date('Y-m-d H:i',
-                $shift['start']) . ' to ' . date('Y-m-d H:i', $shift['end']));
+        engelsystem_log(
+            'User ' . User_Nick_render($user_source)
+            . ' signed up for shift ' . $shift['name']
+            . ' from ' . date('Y-m-d H:i', $shift['start'])
+            . ' to ' . date('Y-m-d H:i', $shift['end'])
+        );
         success(_('You are subscribed. Thank you!') . ' <a href="' . page_link_to('user_myshifts') . '">' . _('My shifts') . ' &raquo;</a>');
         redirect(shift_link($shift));
     }
 
     $angeltype_select = '';
     if (in_array('user_shifts_admin', $privileges)) {
-        $users = sql_select('
-            SELECT *, (SELECT count(*) FROM `ShiftEntry` WHERE `freeloaded`=1 AND `ShiftEntry`.`UID`=`User`.`UID`) AS `freeloaded`
+        $users = DB::select('
+            SELECT *,
+            (
+                SELECT count(*)
+                FROM `ShiftEntry`
+                WHERE `freeloaded`=1
+                AND `ShiftEntry`.`UID`=`User`.`UID`
+            ) AS `freeloaded`
             FROM `User`
             ORDER BY `Nick`
         ');
@@ -162,7 +186,7 @@ function shift_entry_add_controller()
         }
         $user_text = html_select_key('user_id', 'user_id', $users_select, $user['UID']);
 
-        $angeltypes_source = sql_select('SELECT * FROM `AngelTypes` ORDER BY `name`');
+        $angeltypes_source = DB::select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`');
         $angeltypes = [];
         foreach ($angeltypes_source as $angeltype) {
             $angeltypes[$angeltype['id']] = $angeltype['name'];
@@ -218,7 +242,7 @@ function shift_entry_delete_controller()
     }
     $entry_id = $_REQUEST['entry_id'];
 
-    $shift_entry_source = sql_select("
+    $shift_entry_source = DB::select('
         SELECT
             `User`.`Nick`,
             `ShiftEntry`.`Comment`,
@@ -234,9 +258,11 @@ function shift_entry_delete_controller()
         JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`)
         JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`)
         JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`)
-        WHERE `ShiftEntry`.`id`='" . sql_escape($entry_id) . "'");
+        WHERE `ShiftEntry`.`id`=?',
+        [$entry_id]
+    );
     if (count($shift_entry_source) > 0) {
-        $shift_entry_source = $shift_entry_source[0];
+        $shift_entry_source = array_shift($shift_entry_source);
 
         if (!in_array('user_shifts_admin', $privileges) && (!in_array('shiftentry_edit_angeltype_supporter',
                     $privileges) || !User_is_AngelType_supporter($user, AngelType($shift_entry_source['angeltype_id'])))
diff --git a/includes/controller/shifts_controller.php b/includes/controller/shifts_controller.php
index 56ee1452..989f1a69 100644
--- a/includes/controller/shifts_controller.php
+++ b/includes/controller/shifts_controller.php
@@ -7,7 +7,11 @@ use Engelsystem\ShiftSignupState;
  */
 function shift_link($shift)
 {
-    return page_link_to('shifts') . '&action=view&shift_id=' . $shift['SID'];
+    $link = page_link_to('shifts') . '&action=view';
+    if (isset($shift['SID'])) {
+        $link .= '&shift_id=' . $shift['SID'];
+    }
+    return $link;
 }
 
 /**
@@ -253,8 +257,15 @@ function shift_controller()
         $needed_angeltype = NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype);
         $shift_entries = ShiftEntries_by_shift_and_angeltype($shift['SID'], $angeltype['id']);
 
-        $angeltype_signup_state = Shift_signup_allowed($user, $shift, $angeltype, null, $user_shifts, $needed_angeltype,
-            $shift_entries);
+        $angeltype_signup_state = Shift_signup_allowed(
+            $user,
+            $shift,
+            $angeltype,
+            null,
+            $user_shifts,
+            $needed_angeltype,
+            $shift_entries
+        );
         if ($shift_signup_state == null) {
             $shift_signup_state = $angeltype_signup_state;
         } else {
@@ -304,11 +315,8 @@ function shift_next_controller()
     }
 
     $upcoming_shifts = ShiftEntries_upcoming_for_user($user);
-    if ($upcoming_shifts === false) {
-        return false;
-    }
 
-    if (count($upcoming_shifts) > 0) {
+    if (empty($upcoming_shifts)) {
         redirect(shift_link($upcoming_shifts[0]));
     }
 
diff --git a/includes/controller/shifttypes_controller.php b/includes/controller/shifttypes_controller.php
index 55bea389..e6ba716f 100644
--- a/includes/controller/shifttypes_controller.php
+++ b/includes/controller/shifttypes_controller.php
@@ -21,9 +21,6 @@ function shifttype_delete_controller()
     }
 
     $shifttype = ShiftType($_REQUEST['shifttype_id']);
-    if ($shifttype === false) {
-        engelsystem_error('Unable to load shifttype.');
-    }
 
     if ($shifttype == null) {
         redirect(page_link_to('shifttypes'));
@@ -31,7 +28,7 @@ function shifttype_delete_controller()
 
     if (isset($_REQUEST['confirmed'])) {
         $result = ShiftType_delete($shifttype['id']);
-        if ($result === false) {
+        if (empty($result)) {
             engelsystem_error('Unable to delete shifttype.');
         }
 
@@ -62,9 +59,6 @@ function shifttype_edit_controller()
 
     if (isset($_REQUEST['shifttype_id'])) {
         $shifttype = ShiftType($_REQUEST['shifttype_id']);
-        if ($shifttype === false) {
-            engelsystem_error('Unable to load shifttype.');
-        }
         if ($shifttype == null) {
             error(_('Shifttype not found.'));
             redirect(page_link_to('shifttypes'));
@@ -130,9 +124,6 @@ function shifttype_controller()
         redirect(page_link_to('shifttypes'));
     }
     $shifttype = ShiftType($_REQUEST['shifttype_id']);
-    if ($shifttype === false) {
-        engelsystem_error('Unable to load shifttype.');
-    }
     if ($shifttype == null) {
         redirect(page_link_to('shifttypes'));
     }
diff --git a/includes/controller/user_angeltypes_controller.php b/includes/controller/user_angeltypes_controller.php
index 0855e4c9..cf2c0a3c 100644
--- a/includes/controller/user_angeltypes_controller.php
+++ b/includes/controller/user_angeltypes_controller.php
@@ -153,9 +153,6 @@ function user_angeltype_confirm_controller()
 
     if (isset($_REQUEST['confirmed'])) {
         $result = UserAngelType_confirm($user_angeltype['id'], $user);
-        if ($result === false) {
-            engelsystem_error('Unable to confirm user angeltype.');
-        }
 
         engelsystem_log(sprintf(
             '%s confirmed for angeltype %s',
diff --git a/includes/controller/users_controller.php b/includes/controller/users_controller.php
index 72bddd14..b80fdb4d 100644
--- a/includes/controller/users_controller.php
+++ b/includes/controller/users_controller.php
@@ -1,4 +1,6 @@
 <?php
+
+use Engelsystem\Database\DB;
 use Engelsystem\ShiftCalendarRenderer;
 use Engelsystem\ShiftsFilter;
 
@@ -191,20 +193,25 @@ function user_controller()
     $shifts = Shifts_by_user($user_source, in_array('user_shifts_admin', $privileges));
     foreach ($shifts as &$shift) {
         // TODO: Move queries to model
-        $shift['needed_angeltypes'] = sql_select("
+        $shift['needed_angeltypes'] = DB::select('
             SELECT DISTINCT `AngelTypes`.*
             FROM `ShiftEntry`
             JOIN `AngelTypes` ON `ShiftEntry`.`TID`=`AngelTypes`.`id`
-            WHERE `ShiftEntry`.`SID`='" . sql_escape($shift['SID']) . "'
+            WHERE `ShiftEntry`.`SID` = ?
             ORDER BY `AngelTypes`.`name`
-        ");
+            ',
+            [$shift['SID']]
+        );
         foreach ($shift['needed_angeltypes'] as &$needed_angeltype) {
-            $needed_angeltype['users'] = sql_select("
-          SELECT `ShiftEntry`.`freeloaded`, `User`.*
-          FROM `ShiftEntry`
-          JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID`
-          WHERE `ShiftEntry`.`SID`='" . sql_escape($shift['SID']) . "'
-          AND `ShiftEntry`.`TID`='" . sql_escape($needed_angeltype['id']) . "'");
+            $needed_angeltype['users'] = DB::select('
+                  SELECT `ShiftEntry`.`freeloaded`, `User`.*
+                  FROM `ShiftEntry`
+                  JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID`
+                  WHERE `ShiftEntry`.`SID` = ?
+                  AND `ShiftEntry`.`TID` = ?
+                ',
+                [$shift['SID'], $needed_angeltype['id']]
+            );
         }
     }
 
@@ -387,9 +394,6 @@ function load_user()
     }
 
     $user = User($_REQUEST['user_id']);
-    if ($user === false) {
-        engelsystem_error('Unable to load user.');
-    }
 
     if ($user == null) {
         error(_('User doesn\'t exist.'));
-- 
cgit v1.2.3-54-g00ecf