From 9a3ad8883403949a59e8935497a548ec536f1d40 Mon Sep 17 00:00:00 2001 From: Igor Scheller Date: Sat, 21 Jan 2017 13:58:53 +0100 Subject: Changed from mysqli to PDO, some refactorings, faster sql queries --- includes/model/AngelType_model.php | 164 +++++++++------ includes/model/EventConfig_model.php | 72 ++++--- includes/model/LogEntries_model.php | 43 ++-- includes/model/Message_model.php | 48 ++--- includes/model/NeededAngelTypes_model.php | 65 +++--- includes/model/Room_model.php | 60 +++--- includes/model/ShiftEntry_model.php | 200 ++++++++++++------- includes/model/ShiftTypes_model.php | 65 ++++-- includes/model/Shifts_model.php | 251 +++++++++++++---------- includes/model/UserAngelTypes_model.php | 177 ++++++++++------- includes/model/UserDriverLicenses_model.php | 87 +++++--- includes/model/UserGroups_model.php | 20 +- includes/model/User_model.php | 296 +++++++++++++++++++--------- 13 files changed, 979 insertions(+), 569 deletions(-) (limited to 'includes/model') diff --git a/includes/model/AngelType_model.php b/includes/model/AngelType_model.php index c3270863..411c69ea 100644 --- a/includes/model/AngelType_model.php +++ b/includes/model/AngelType_model.php @@ -1,4 +1,6 @@ lastInsertId(); engelsystem_log( - 'Created angeltype: ' . $angeltype['name'] . ($angeltype['restricted'] ? ', restricted' : '') - . ($angeltype['requires_driver_license'] ? ', requires driver license' : '')); + 'Created angeltype: ' . $angeltype['name'] + . ($angeltype['restricted'] ? ', restricted' : '') + . ($angeltype['requires_driver_license'] ? ', requires driver license' : '') + ); return $angeltype; } @@ -167,19 +199,20 @@ function AngelType_validate_name($name, $angeltype) return new ValidationResult(false, ''); } if ($angeltype != null && isset($angeltype['id'])) { - $valid = sql_num_query(" - SELECT * - FROM `AngelTypes` - WHERE `name`='" . sql_escape($name) . "' - AND NOT `id`='" . sql_escape($angeltype['id']) . "' - LIMIT 1") == 0; + $valid = (count(DB::select(' + SELECT `id` + FROM `AngelTypes` + WHERE `name`=? + AND NOT `id`=? + LIMIT 1 + ', [$name, $angeltype['id']])) == 0); return new ValidationResult($valid, $name); } - $valid = sql_num_query(" + $valid = (count(DB::select(' SELECT `id` FROM `AngelTypes` - WHERE `name`='" . sql_escape($name) . "' - LIMIT 1") == 0; + WHERE `name`=? + LIMIT 1', [$name])) == 0); return new ValidationResult($valid, $name); } @@ -191,16 +224,17 @@ function AngelType_validate_name($name, $angeltype) */ function AngelTypes_with_user($user) { - $result = sql_select(" + $result = DB::select(' SELECT `AngelTypes`.*, `UserAngelTypes`.`id` AS `user_angeltype_id`, `UserAngelTypes`.`confirm_user_id`, `UserAngelTypes`.`supporter` FROM `AngelTypes` LEFT JOIN `UserAngelTypes` ON `AngelTypes`.`id`=`UserAngelTypes`.`angeltype_id` - AND `UserAngelTypes`.`user_id`=" . $user['UID'] . " - ORDER BY `name`"); - if ($result === false) { + AND `UserAngelTypes`.`user_id` = ? + ORDER BY `name`', [$user['UID']]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load angeltypes.'); } return $result; @@ -213,11 +247,12 @@ function AngelTypes_with_user($user) */ function AngelTypes() { - $result = sql_select(" + $result = DB::select(' SELECT * FROM `AngelTypes` - ORDER BY `name`"); - if ($result === false) { + ORDER BY `name`'); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load angeltypes.'); } return $result; @@ -230,8 +265,9 @@ function AngelTypes() */ function AngelType_ids() { - $result = sql_select("SELECT `id` FROM `AngelTypes`"); - if ($result === false) { + $result = DB::select('SELECT `id` FROM `AngelTypes`'); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load angeltypes.'); } return select_array($result, 'id', 'id'); @@ -241,16 +277,22 @@ function AngelType_ids() * Returns angelType by id. * * @param int $angeltype_id angelType ID - * @return array + * @return array|null */ function AngelType($angeltype_id) { - $angelType_source = sql_select("SELECT * FROM `AngelTypes` WHERE `id`='" . sql_escape($angeltype_id) . "'"); - if ($angelType_source === false) { + $angelType_source = DB::select( + 'SELECT * FROM `AngelTypes` WHERE `id`=?', + [$angeltype_id] + ); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load angeltype.'); } - if (count($angelType_source) > 0) { - return $angelType_source[0]; + + if (empty($angelType_source)) { + return null; } - return null; + + return array_shift($angelType_source); } diff --git a/includes/model/EventConfig_model.php b/includes/model/EventConfig_model.php index 330863a9..773ee2e0 100644 --- a/includes/model/EventConfig_model.php +++ b/includes/model/EventConfig_model.php @@ -1,21 +1,25 @@ errorCode() != '00000') { engelsystem_error('Unable to load event config.'); - return false; + return null; } - if (count($event_config) > 0) { - return $event_config[0]; + + if (empty($event_config)) { + return null; } - return null; + + return array_shift($event_config); } /** @@ -27,7 +31,7 @@ function EventConfig() * @param int $event_end_date * @param int $teardown_end_date * @param string $event_welcome_msg - * @return mysqli_result|false + * @return bool */ function EventConfig_update( $event_name, @@ -38,20 +42,44 @@ function EventConfig_update( $event_welcome_msg ) { if (EventConfig() == null) { - return sql_query("INSERT INTO `EventConfig` SET - `event_name`=" . sql_null($event_name) . ", - `buildup_start_date`=" . sql_null($buildup_start_date) . ", - `event_start_date`=" . sql_null($event_start_date) . ", - `event_end_date`=" . sql_null($event_end_date) . ", - `teardown_end_date`=" . sql_null($teardown_end_date) . ", - `event_welcome_msg`=" . sql_null($event_welcome_msg)); + return DB::insert(' + INSERT INTO `EventConfig` ( + `event_name`, + `buildup_start_date`, + `event_start_date`, + `event_end_date`, + `teardown_end_date`, + `event_welcome_msg` + ) + VALUES (?, ?, ?, ?, ?, ?) + ', + [ + $event_name, + $buildup_start_date, + $event_start_date, + $event_end_date, + $teardown_end_date, + $event_welcome_msg + ] + ); } - return sql_query("UPDATE `EventConfig` SET - `event_name`=" . sql_null($event_name) . ", - `buildup_start_date`=" . sql_null($buildup_start_date) . ", - `event_start_date`=" . sql_null($event_start_date) . ", - `event_end_date`=" . sql_null($event_end_date) . ", - `teardown_end_date`=" . sql_null($teardown_end_date) . ", - `event_welcome_msg`=" . sql_null($event_welcome_msg)); + return (bool)DB::update(' + UPDATE `EventConfig` SET + `event_name` = ?, + `buildup_start_date` = ?, + `event_start_date` = ?, + `event_end_date` = ?, + `teardown_end_date` = ?, + `event_welcome_msg` = ? + ', + [ + $event_name, + $buildup_start_date, + $event_start_date, + $event_end_date, + $teardown_end_date, + $event_welcome_msg, + ] + ); } diff --git a/includes/model/LogEntries_model.php b/includes/model/LogEntries_model.php index 920b1945..0e11bf8e 100644 --- a/includes/model/LogEntries_model.php +++ b/includes/model/LogEntries_model.php @@ -1,59 +1,62 @@ 0) { - return $message_source[0]; + $message_source = DB::select('SELECT * FROM `Messages` WHERE `id`=? LIMIT 1', [$message_id]); + if (empty($message_source)) { + return null; } - return null; + return array_shift($message_source); } /** @@ -46,22 +45,25 @@ function Message_send($receiver_user_id, $text) if ( ($text != '' && is_numeric($receiver_user_id)) - && (sql_num_query(" - SELECT * + && count(DB::select(' + SELECT `UID` FROM `User` - WHERE `UID`='" . sql_escape($receiver_user_id) . "' - AND NOT `UID`='" . sql_escape($user['UID']) . "' + WHERE `UID` = ? + AND NOT `UID` = ? LIMIT 1 - ") > 0) + ', [$receiver_user_id, $user['UID']])) > 0 ) { - sql_query(" - INSERT INTO `Messages` - SET `Datum`='" . sql_escape(time()) . "', - `SUID`='" . sql_escape($user['UID']) . "', - `RUID`='" . sql_escape($receiver_user_id) . "', - `Text`='" . sql_escape($text) . "' - "); - return true; + return DB::insert(' + INSERT INTO `Messages` (`Datum`, `SUID`, `RUID`, `Text`) + VALUES(?, ?, ?, ?) + ', + [ + time(), + $user['UID'], + $receiver_user_id, + $text + ] + ); } return false; diff --git a/includes/model/NeededAngelTypes_model.php b/includes/model/NeededAngelTypes_model.php index f65efc41..97b085f0 100644 --- a/includes/model/NeededAngelTypes_model.php +++ b/includes/model/NeededAngelTypes_model.php @@ -1,5 +1,7 @@ lastInsertId(); } /** * Deletes all needed angel types from given shift. * * @param int $shift_id id of the shift - * @return mysqli_result|false + * @return int count of affected rows */ function NeededAngelTypes_delete_by_shift($shift_id) { - return sql_query("DELETE FROM `NeededAngelTypes` WHERE `shift_id`='" . sql_escape($shift_id) . "'"); + return (int)DB::delete('DELETE FROM `NeededAngelTypes` WHERE `shift_id` = ?', [$shift_id]); } /** * Deletes all needed angel types from given room. * * @param int $room_id id of the room - * @return mysqli_result|false + * @return int count of affected rows */ function NeededAngelTypes_delete_by_room($room_id) { - return sql_query("DELETE FROM `NeededAngelTypes` WHERE `room_id`='" . sql_escape($room_id) . "'"); + return (int)DB::delete( + 'DELETE FROM `NeededAngelTypes` WHERE `room_id` = ?', + [$room_id] + ); } /** @@ -61,30 +67,31 @@ function NeededAngelTypes_delete_by_room($room_id) */ function NeededAngelTypes_by_shift($shiftId) { - $needed_angeltypes_source = sql_select(" + $needed_angeltypes_source = DB::select(' SELECT `NeededAngelTypes`.*, `AngelTypes`.`id`, `AngelTypes`.`name`, `AngelTypes`.`restricted`, `AngelTypes`.`no_self_signup` FROM `NeededAngelTypes` JOIN `AngelTypes` ON `AngelTypes`.`id` = `NeededAngelTypes`.`angel_type_id` - WHERE `shift_id`='" . sql_escape($shiftId) . "' + WHERE `shift_id` = ? AND `count` > 0 - ORDER BY `room_id` DESC - "); - if ($needed_angeltypes_source === false) { + ORDER BY `room_id` DESC', + [$shiftId] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load needed angeltypes.'); } // Use settings from room if (count($needed_angeltypes_source) == 0) { - $needed_angeltypes_source = sql_select(" + $needed_angeltypes_source = DB::select(' SELECT `NeededAngelTypes`.*, `AngelTypes`.`name`, `AngelTypes`.`restricted` FROM `NeededAngelTypes` JOIN `AngelTypes` ON `AngelTypes`.`id` = `NeededAngelTypes`.`angel_type_id` JOIN `Shifts` ON `Shifts`.`RID` = `NeededAngelTypes`.`room_id` - WHERE `Shifts`.`SID`='" . sql_escape($shiftId) . "' + WHERE `Shifts`.`SID` = ? AND `count` > 0 ORDER BY `room_id` DESC - "); - if ($needed_angeltypes_source === false) { + ', [$shiftId]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load needed angeltypes.'); } } diff --git a/includes/model/Room_model.php b/includes/model/Room_model.php index 3eb9f452..c8399bc4 100644 --- a/includes/model/Room_model.php +++ b/includes/model/Room_model.php @@ -1,53 +1,56 @@ lastInsertId(); } /** @@ -59,18 +62,21 @@ function Room_create($name, $from_frab, $public, $number = null) */ function Room($room_id, $show_only = true) { - $room_source = sql_select(" + $room_source = DB::select(' SELECT * FROM `Room` - WHERE `RID`='" . sql_escape($room_id) . "' - " . ($show_only ? "AND `show` = 'Y'" : '') + WHERE `RID` = ? + ' . ($show_only ? 'AND `show` = \'Y\'' : ''), + [$room_id] ); - if ($room_source === false) { + if (DB::getStm()->errorCode() != '00000') { return false; } - if (count($room_source) > 0) { - return $room_source[0]; + + if (empty($room_source)) { + return null; } - return null; + + return array_shift($room_source); } diff --git a/includes/model/ShiftEntry_model.php b/includes/model/ShiftEntry_model.php index acdb4160..87e186ac 100644 --- a/includes/model/ShiftEntry_model.php +++ b/includes/model/ShiftEntry_model.php @@ -1,5 +1,7 @@ errorCode() == '00000'); } /** @@ -97,11 +132,11 @@ function ShiftEntry_update($shift_entry) */ function ShiftEntry($shift_entry_id) { - $shift_entry = sql_select("SELECT * FROM `ShiftEntry` WHERE `id`='" . sql_escape($shift_entry_id) . "'"); - if ($shift_entry === false) { + $shift_entry = DB::select('SELECT * FROM `ShiftEntry` WHERE `id` = ?', [$shift_entry_id]); + if (DB::getStm()->errorCode() != '00000') { return false; } - if (count($shift_entry) == 0) { + if (empty($shift_entry)) { return null; } return $shift_entry[0]; @@ -111,52 +146,62 @@ function ShiftEntry($shift_entry_id) * Delete a shift entry. * * @param int $shift_entry_id - * @return mysqli_result|false + * @return bool */ function ShiftEntry_delete($shift_entry_id) { $shift_entry = ShiftEntry($shift_entry_id); mail_shift_removed(User($shift_entry['UID']), Shift($shift_entry['SID'])); - return sql_query("DELETE FROM `ShiftEntry` WHERE `id`='" . sql_escape($shift_entry_id) . "'"); + return DB::delete('DELETE FROM `ShiftEntry` WHERE `id` = ?', [$shift_entry_id]); } /** * Returns next (or current) shifts of given user. * * @param array $user - * @return array|false + * @return array */ function ShiftEntries_upcoming_for_user($user) { - return sql_select(" - SELECT * - FROM `ShiftEntry` - JOIN `Shifts` ON (`Shifts`.`SID` = `ShiftEntry`.`SID`) - JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` - WHERE `ShiftEntry`.`UID`=" . sql_escape($user['UID']) . " - AND `Shifts`.`end` > " . sql_escape(time()) . " - ORDER BY `Shifts`.`end` - "); + return DB::select(' + SELECT * + FROM `ShiftEntry` + JOIN `Shifts` ON (`Shifts`.`SID` = `ShiftEntry`.`SID`) + JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` + WHERE `ShiftEntry`.`UID` = ? + AND `Shifts`.`end` > ? + ORDER BY `Shifts`.`end` + ', + [ + $user['UID'], + time(), + ] + ); } /** * Returns shifts completed by the given user. * * @param array $user - * @return array|false + * @return array */ function ShiftEntries_finished_by_user($user) { - return sql_select(" - SELECT * - FROM `ShiftEntry` - JOIN `Shifts` ON (`Shifts`.`SID` = `ShiftEntry`.`SID`) - JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` - WHERE `ShiftEntry`.`UID`=" . sql_escape($user['UID']) . " - AND `Shifts`.`end` < " . sql_escape(time()) . " - AND `ShiftEntry`.`freeloaded` = 0 - ORDER BY `Shifts`.`end` - "); + return DB::select(' + SELECT * + FROM `ShiftEntry` + JOIN `Shifts` ON (`Shifts`.`SID` = `ShiftEntry`.`SID`) + JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` + WHERE `ShiftEntry`.`UID` = ? + AND `Shifts`.`end` < ? + AND `ShiftEntry`.`freeloaded` = 0 + ORDER BY `Shifts`.`end` + ', + [ + $user['UID'], + time(), + ] + ); } /** @@ -164,17 +209,22 @@ function ShiftEntries_finished_by_user($user) * * @param int $shift_id * @param int $angeltype_id - * @return array|false + * @return array */ function ShiftEntries_by_shift_and_angeltype($shift_id, $angeltype_id) { - $result = sql_select(" - SELECT * - FROM `ShiftEntry` - WHERE `SID`=" . sql_escape($shift_id) . " - AND `TID`=" . sql_escape($angeltype_id) . " - "); - if ($result === false) { + $result = DB::select(' + SELECT * + FROM `ShiftEntry` + WHERE `SID` = ? + AND `TID` = ? + ', + [ + $shift_id, + $angeltype_id, + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shift entries.'); } return $result; @@ -184,12 +234,18 @@ function ShiftEntries_by_shift_and_angeltype($shift_id, $angeltype_id) * Returns all freeloaded shifts for given user. * * @param array $user - * @return array|false + * @return array */ function ShiftEntries_freeloaded_by_user($user) { - return sql_select("SELECT * - FROM `ShiftEntry` - WHERE `freeloaded` = 1 - AND `UID`=" . sql_escape($user['UID'])); + return DB::select(' + SELECT * + FROM `ShiftEntry` + WHERE `freeloaded` = 1 + AND `UID` = ? + ', + [ + $user['UID'] + ] + ); } diff --git a/includes/model/ShiftTypes_model.php b/includes/model/ShiftTypes_model.php index 03a98bd8..4919875b 100644 --- a/includes/model/ShiftTypes_model.php +++ b/includes/model/ShiftTypes_model.php @@ -1,14 +1,16 @@ errorCode() == '00000'; } /** @@ -41,16 +52,22 @@ function ShiftType_update($shifttype_id, $name, $angeltype_id, $description) */ function ShiftType_create($name, $angeltype_id, $description) { - $result = sql_query(" - INSERT INTO `ShiftTypes` SET - `name`='" . sql_escape($name) . "', - `angeltype_id`=" . sql_null($angeltype_id) . ", - `description`='" . sql_escape($description) . "' - "); + $result = DB::insert(' + INSERT INTO `ShiftTypes` (`name`, `angeltype_id`, `description`) + VALUES(?, ?, ?) + ', + [ + $name, + $angeltype_id, + $description + ] + ); + if ($result === false) { return false; } - return sql_id(); + + return DB::getPdo()->lastInsertId(); } /** @@ -61,14 +78,14 @@ function ShiftType_create($name, $angeltype_id, $description) */ function ShiftType($shifttype_id) { - $shifttype = sql_select("SELECT * FROM `ShiftTypes` WHERE `id`='" . sql_escape($shifttype_id) . "'"); - if ($shifttype === false) { + $shifttype = DB::select('SELECT * FROM `ShiftTypes` WHERE `id`=?', [$shifttype_id]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shift type.'); } - if ($shifttype == null) { + if (empty($shifttype)) { return null; } - return $shifttype[0]; + return array_shift($shifttype); } /** @@ -78,5 +95,11 @@ function ShiftType($shifttype_id) */ function ShiftTypes() { - return sql_select('SELECT * FROM `ShiftTypes` ORDER BY `name`'); + $result = DB::select('SELECT * FROM `ShiftTypes` ORDER BY `name`'); + + if (DB::getStm()->errorCode() != '00000') { + return false; + } + + return $result; } 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 @@ 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; } diff --git a/includes/model/UserAngelTypes_model.php b/includes/model/UserAngelTypes_model.php index 9ae21772..b27724c3 100644 --- a/includes/model/UserAngelTypes_model.php +++ b/includes/model/UserAngelTypes_model.php @@ -1,5 +1,7 @@ 0; + WHERE `UserAngelTypes`.`user_id`=? + AND `angeltype_id`=? + ', [$user['UID'], $angeltype['id']])) > 0; } /** @@ -29,16 +31,18 @@ function UserAngelType_exists($user, $angeltype) */ function User_angeltypes($user) { - $result = sql_select(" + $result = DB::select(' SELECT `AngelTypes`.*, `UserAngelTypes`.`confirm_user_id`, `UserAngelTypes`.`supporter` FROM `UserAngelTypes` JOIN `AngelTypes` ON `UserAngelTypes`.`angeltype_id` = `AngelTypes`.`id` - WHERE `UserAngelTypes`.`user_id`='" . sql_escape($user['UID']) . "' - "); - if ($result === false) { + WHERE `UserAngelTypes`.`user_id`=? + ', [$user['UID']]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user angeltypes.'); return false; } + return $result; } @@ -50,7 +54,7 @@ function User_angeltypes($user) */ function User_unconfirmed_AngelTypes($user) { - $result = sql_select(" + $result = DB::select(' SELECT `UserAngelTypes`.*, `AngelTypes`.`name`, @@ -58,16 +62,18 @@ function User_unconfirmed_AngelTypes($user) FROM `UserAngelTypes` JOIN `AngelTypes` ON `UserAngelTypes`.`angeltype_id`=`AngelTypes`.`id` JOIN `UserAngelTypes` AS `UnconfirmedMembers` ON `UserAngelTypes`.`angeltype_id`=`UnconfirmedMembers`.`angeltype_id` - WHERE `UserAngelTypes`.`user_id`='" . sql_escape($user['UID']) . "' + WHERE `UserAngelTypes`.`user_id`=? AND `UserAngelTypes`.`supporter`=TRUE AND `AngelTypes`.`restricted`=TRUE AND `UnconfirmedMembers`.`confirm_user_id` IS NULL GROUP BY `UserAngelTypes`.`angeltype_id` ORDER BY `AngelTypes`.`name` - "); - if ($result === false) { + ', [$user['UID']]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user angeltypes.'); } + return $result; } @@ -83,14 +89,20 @@ function User_is_AngelType_supporter(&$user, $angeltype) if (!isset($user['privileges'])) { $user['privileges'] = privileges_for_user($user['UID']); } - return (sql_num_query(" - SELECT `id` - FROM `UserAngelTypes` - WHERE `user_id`='" . sql_escape($user['UID']) . "' - AND `angeltype_id`='" . sql_escape($angeltype['id']) . "' - AND `supporter`=TRUE - LIMIT 1 - ") > 0) || in_array('admin_user_angeltypes', $user['privileges']); + return (count(DB::select(' + SELECT `id` + FROM `UserAngelTypes` + WHERE `user_id`=? + AND `angeltype_id`=? + AND `supporter`=TRUE + LIMIT 1 + ', + [ + $user['UID'], + $angeltype['id'] + ] + )) > 0) + || in_array('admin_user_angeltypes', $user['privileges']); } /** @@ -98,19 +110,21 @@ function User_is_AngelType_supporter(&$user, $angeltype) * * @param int $user_angeltype_id * @param bool $supporter - * @return mysqli_result + * @return int */ function UserAngelType_update($user_angeltype_id, $supporter) { - $result = sql_query(" + $result = DB::update(' UPDATE `UserAngelTypes` - SET `supporter`=" . sql_bool($supporter) . " - WHERE `id`='" . sql_escape($user_angeltype_id) . "' + SET `supporter`=? + WHERE `id`=? LIMIT 1 - "); - if ($result === false) { + ', [$supporter, $user_angeltype_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to update supporter rights.'); } + return $result; } @@ -118,19 +132,21 @@ function UserAngelType_update($user_angeltype_id, $supporter) * Delete all unconfirmed UserAngelTypes for given Angeltype. * * @param int $angeltype_id - * @return mysqli_result + * @return bool */ function UserAngelTypes_delete_all($angeltype_id) { - $result = sql_query(" + DB::delete(' DELETE FROM `UserAngelTypes` - WHERE `angeltype_id`='" . sql_escape($angeltype_id) . "' + WHERE `angeltype_id`=? AND `confirm_user_id` IS NULL - "); - if ($result === false) { + ', [$angeltype_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to delete all unconfirmed users.'); } - return $result; + + return true; } /** @@ -138,20 +154,22 @@ function UserAngelTypes_delete_all($angeltype_id) * * @param int $angeltype_id * @param array $confirm_user - * @return mysqli_result + * @return bool */ function UserAngelTypes_confirm_all($angeltype_id, $confirm_user) { - $result = sql_query(" + $result = DB::update(' UPDATE `UserAngelTypes` - SET `confirm_user_id`='" . sql_escape($confirm_user['UID']) . "' - WHERE `angeltype_id`='" . sql_escape($angeltype_id) . "' + SET `confirm_user_id`=? + WHERE `angeltype_id`=? AND `confirm_user_id` IS NULL - "); - if ($result === false) { + ', [$confirm_user['UID'], $angeltype_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to confirm all users.'); } - return $result; + + return (bool)$result; } /** @@ -159,33 +177,33 @@ function UserAngelTypes_confirm_all($angeltype_id, $confirm_user) * * @param int $user_angeltype_id * @param array $confirm_user - * @return mysqli_result + * @return bool */ function UserAngelType_confirm($user_angeltype_id, $confirm_user) { - $result = sql_query(" + $result = DB::update(' UPDATE `UserAngelTypes` - SET `confirm_user_id`='" . sql_escape($confirm_user['UID']) . "' - WHERE `id`='" . sql_escape($user_angeltype_id) . "' - LIMIT 1"); - if ($result === false) { + SET `confirm_user_id`=? + WHERE `id`=? + LIMIT 1', [$confirm_user['UID'], $user_angeltype_id]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to confirm user angeltype.'); } - return $result; + return (bool)$result; } /** * Delete an UserAngelType. * * @param array $user_angeltype - * @return mysqli_result|false + * @return bool */ function UserAngelType_delete($user_angeltype) { - return sql_query(" + return (bool)DB::delete(' DELETE FROM `UserAngelTypes` - WHERE `id`='" . sql_escape($user_angeltype['id']) . "' - LIMIT 1"); + WHERE `id`=? + LIMIT 1', [$user_angeltype['id']]); } /** @@ -197,14 +215,21 @@ function UserAngelType_delete($user_angeltype) */ function UserAngelType_create($user, $angeltype) { - $result = sql_query(" - INSERT INTO `UserAngelTypes` SET - `user_id`='" . sql_escape($user['UID']) . "', - `angeltype_id`='" . sql_escape($angeltype['id']) . "'"); - if ($result === false) { + DB::insert(' + INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`) + VALUES (?, ?) + ', + [ + $user['UID'], + $angeltype['id'] + ] + ); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to create user angeltype.'); } - return sql_id(); + + return DB::getPdo()->lastInsertId(); } /** @@ -215,17 +240,20 @@ function UserAngelType_create($user, $angeltype) */ function UserAngelType($user_angeltype_id) { - $angeltype = sql_select(" + $angeltype = DB::select(' SELECT * FROM `UserAngelTypes` - WHERE `id`='" . sql_escape($user_angeltype_id) . "' - LIMIT 1"); - if ($angeltype === false) { + WHERE `id`=? + LIMIT 1', [$user_angeltype_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user angeltype.'); } - if (count($angeltype) == 0) { + + if (empty($angeltype)) { return null; } + return $angeltype[0]; } @@ -238,17 +266,26 @@ function UserAngelType($user_angeltype_id) */ function UserAngelType_by_User_and_AngelType($user, $angeltype) { - $angeltype = sql_select(" - SELECT * - FROM `UserAngelTypes` - WHERE `user_id`='" . sql_escape($user['UID']) . "' - AND `angeltype_id`='" . sql_escape($angeltype['id']) . "' - LIMIT 1"); - if ($angeltype === false) { + $angeltype = DB::select(' + SELECT * + FROM `UserAngelTypes` + WHERE `user_id`=? + AND `angeltype_id`=? + LIMIT 1 + ', + [ + $user['UID'], + $angeltype['id'] + ] + ); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user angeltype.'); } - if (count($angeltype) == 0) { + + if (empty($angeltype)) { return null; } - return $angeltype[0]; + + return array_shift($angeltype); } diff --git a/includes/model/UserDriverLicenses_model.php b/includes/model/UserDriverLicenses_model.php index 8091736c..5ff4df35 100644 --- a/includes/model/UserDriverLicenses_model.php +++ b/includes/model/UserDriverLicenses_model.php @@ -1,5 +1,7 @@ errorCode() != '00000') { engelsystem_error('Unable to load user driver license.'); return false; } - if (count($user_driver_license) == 0) { + if (empty($user_driver_license)) { return null; } - return $user_driver_license[0]; + return array_shift($user_driver_license); } /** @@ -66,18 +72,32 @@ function UserDriverLicense($user_id) function UserDriverLicenses_create($user_driver_license, $user) { $user_driver_license['user_id'] = $user['UID']; - $result = sql_query(" - INSERT INTO `UserDriverLicenses` SET - `user_id`=" . sql_escape($user_driver_license['user_id']) . ", - `has_car`=" . sql_bool($user_driver_license['has_car']) . ", - `has_license_car`=" . sql_bool($user_driver_license['has_license_car']) . ", - `has_license_3_5t_transporter`=" . sql_bool($user_driver_license['has_license_3_5t_transporter']) . ", - `has_license_7_5t_truck`=" . sql_bool($user_driver_license['has_license_7_5t_truck']) . ", - `has_license_12_5t_truck`=" . sql_bool($user_driver_license['has_license_12_5t_truck']) . ", - `has_license_forklift`=" . sql_bool($user_driver_license['has_license_forklift'])); - if ($result === false) { + DB::insert(' + INSERT INTO `UserDriverLicenses` ( + `user_id`, + `has_car`, + `has_license_car`, + `has_license_3_5t_transporter`, + `has_license_7_5t_truck`, + `has_license_12_5t_truck`, + `has_license_forklift` + ) + VALUES (?, ?, ?, ?, ?, ?, ?) + ', + [ + $user_driver_license['user_id'], + (bool)$user_driver_license['has_car'], + (bool)$user_driver_license['has_license_car'], + (bool)$user_driver_license['has_license_3_5t_transporter'], + (bool)$user_driver_license['has_license_7_5t_truck'], + (bool)$user_driver_license['has_license_12_5t_truck'], + (bool)$user_driver_license['has_license_forklift'], + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to create user driver license'); } + return $user_driver_license; } @@ -85,19 +105,32 @@ function UserDriverLicenses_create($user_driver_license, $user) * Update a user's driver license entry * * @param array $user_driver_license The UserDriverLicense to update - * @return mysqli_result + * @return bool */ function UserDriverLicenses_update($user_driver_license) { - $result = sql_query("UPDATE `UserDriverLicenses` SET - `has_car`=" . sql_bool($user_driver_license['has_car']) . ", - `has_license_car`=" . sql_bool($user_driver_license['has_license_car']) . ", - `has_license_3_5t_transporter`=" . sql_bool($user_driver_license['has_license_3_5t_transporter']) . ", - `has_license_7_5t_truck`=" . sql_bool($user_driver_license['has_license_7_5t_truck']) . ", - `has_license_12_5t_truck`=" . sql_bool($user_driver_license['has_license_12_5t_truck']) . ", - `has_license_forklift`=" . sql_bool($user_driver_license['has_license_forklift']) . " - WHERE `user_id`='" . sql_escape($user_driver_license['user_id']) . "'"); - if ($result === false) { + $result = DB::update(' + UPDATE `UserDriverLicenses` + SET + `has_car`=?, + `has_license_car`=?, + `has_license_3_5t_transporter`=?, + `has_license_7_5t_truck`=?, + `has_license_12_5t_truck`=?, + `has_license_forklift`=? + WHERE `user_id`=? + ', + [ + (bool)$user_driver_license['has_car'], + (bool)$user_driver_license['has_license_car'], + (bool)$user_driver_license['has_license_3_5t_transporter'], + (bool)$user_driver_license['has_license_7_5t_truck'], + (bool)$user_driver_license['has_license_12_5t_truck'], + (bool)$user_driver_license['has_license_forklift'], + $user_driver_license['user_id'], + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to update user driver license information'); } return $result; @@ -107,12 +140,12 @@ function UserDriverLicenses_update($user_driver_license) * Delete a user's driver license entry * * @param int $user_id - * @return mysqli_result + * @return bool */ function UserDriverLicenses_delete($user_id) { - $result = sql_query("DELETE FROM `UserDriverLicenses` WHERE `user_id`=" . sql_escape($user_id)); - if ($result === false) { + $result = DB::delete('DELETE FROM `UserDriverLicenses` WHERE `user_id`=?', [$user_id]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to remove user driver license information'); } return $result; diff --git a/includes/model/UserGroups_model.php b/includes/model/UserGroups_model.php index c390cd20..d4baf638 100644 --- a/includes/model/UserGroups_model.php +++ b/includes/model/UserGroups_model.php @@ -1,18 +1,22 @@ errorCode() == '00000'; } /** * Update user. * * @param array $user - * @return mysqli_result|false + * @return bool */ function User_update($user) { - return sql_query(" - UPDATE `User` SET - `Nick`='" . sql_escape($user['Nick']) . "', - `Name`='" . sql_escape($user['Name']) . "', - `Vorname`='" . sql_escape($user['Vorname']) . "', - `Alter`='" . sql_escape($user['Alter']) . "', - `Telefon`='" . sql_escape($user['Telefon']) . "', - `DECT`='" . sql_escape($user['DECT']) . "', - `Handy`='" . sql_escape($user['Handy']) . "', - `email`='" . sql_escape($user['email']) . "', - `email_shiftinfo`=" . sql_bool($user['email_shiftinfo']) . ", - `email_by_human_allowed`=" . sql_bool($user['email_by_human_allowed']) . ", - `jabber`='" . sql_escape($user['jabber']) . "', - `Size`='" . sql_escape($user['Size']) . "', - `Gekommen`='" . sql_escape($user['Gekommen']) . "', - `Aktiv`='" . sql_escape($user['Aktiv']) . "', - `force_active`=" . sql_bool($user['force_active']) . ", - `Tshirt`='" . sql_escape($user['Tshirt']) . "', - `color`='" . sql_escape($user['color']) . "', - `Sprache`='" . sql_escape($user['Sprache']) . "', - `Hometown`='" . sql_escape($user['Hometown']) . "', - `got_voucher`='" . sql_escape($user['got_voucher']) . "', - `arrival_date`='" . sql_escape($user['arrival_date']) . "', - `planned_arrival_date`='" . sql_escape($user['planned_arrival_date']) . "', - `planned_departure_date`=" . sql_null($user['planned_departure_date']) . " - WHERE `UID`='" . sql_escape($user['UID']) . "' - "); + return (bool)DB::update(" + UPDATE `User` SET + `Nick`=?, + `Name`=?, + `Vorname`=?, + `Alter`=?, + `Telefon`=?, + `DECT`=?, + `Handy`=?, + `email`=?, + `email_shiftinfo`=?, + `email_by_human_allowed`=?, + `jabber`=?, + `Size`=?, + `Gekommen`=?, + `Aktiv`=?, + `force_active`=?, + `Tshirt`=?, + `color`=?, + `Sprache`=?, + `Hometown`=?, + `got_voucher`=?, + `arrival_date`=?, + `planned_arrival_date`=?, + `planned_departure_date`=? + WHERE `UID`=? + ", + [ + $user['Nick'], + $user['Name'], + $user['Vorname'], + $user['Alter'], + $user['Telefon'], + $user['DECT'], + $user['Handy'], + $user['email'], + (bool)$user['email_shiftinfo'], + (bool)$user['email_by_human_allowed'], + $user['jabber'], + $user['Size'], + $user['Gekommen'], + $user['Aktiv'], + (bool)$user['force_active'], + $user['Tshirt'], + $user['color'], + $user['Sprache'], + $user['Hometown'], + $user['got_voucher'], + $user['arrival_date'], + $user['planned_arrival_date'], + $user['planned_departure_date'], + $user['UID'], + ] + ); } /** * Counts all forced active users. * - * @return string|null + * @return int */ function User_force_active_count() { - return sql_select_single_cell('SELECT COUNT(*) FROM `User` WHERE `force_active` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `User` WHERE `force_active` = 1'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** - * @return string|null + * @return int */ function User_active_count() { - return sql_select_single_cell('SELECT COUNT(*) FROM `User` WHERE `Aktiv` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `User` WHERE `Aktiv` = 1'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** - * @return string|null + * @return int */ function User_got_voucher_count() { - return sql_select_single_cell('SELECT SUM(`got_voucher`) FROM `User`'); + $result = DB::select('SELECT SUM(`got_voucher`) FROM `User`'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** - * @return string|null + * @return int */ function User_arrived_count() { - return sql_select_single_cell('SELECT COUNT(*) FROM `User` WHERE `Gekommen` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `User` WHERE `Gekommen` = 1'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** - * @return string|null + * @return int */ function User_tshirts_count() { - return sql_select_single_cell('SELECT COUNT(*) FROM `User` WHERE `Tshirt` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `User` WHERE `Tshirt` = 1'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** @@ -126,7 +192,19 @@ function User_sortable_columns() */ function Users($order_by = 'Nick') { - return sql_select("SELECT * FROM `User` ORDER BY `" . sql_escape($order_by) . "` ASC"); + $result = DB::select(sprintf(' + SELECT * + FROM `User` + ORDER BY `%s` ASC + ', + trim(DB::getPdo()->quote($order_by), '\'') + )); + + if (DB::getStm()->errorCode() != '00000') { + return false; + } + + return $result; } /** @@ -150,14 +228,19 @@ function User_is_freeloader($user) */ function Users_by_angeltype_inverted($angeltype) { - $result = sql_select(" - SELECT `User`.* - FROM `User` - LEFT JOIN `UserAngelTypes` - ON (`User`.`UID`=`UserAngelTypes`.`user_id` AND `angeltype_id`='" . sql_escape($angeltype['id']) . "') - WHERE `UserAngelTypes`.`id` IS NULL - ORDER BY `Nick`"); - if ($result === false) { + $result = DB::select(' + SELECT `User`.* + FROM `User` + LEFT JOIN `UserAngelTypes` + ON (`User`.`UID`=`UserAngelTypes`.`user_id` AND `angeltype_id`=?) + WHERE `UserAngelTypes`.`id` IS NULL + ORDER BY `Nick` + ', + [ + $angeltype['id'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error("Unable to load users."); } return $result; @@ -171,19 +254,24 @@ function Users_by_angeltype_inverted($angeltype) */ function Users_by_angeltype($angeltype) { - $result = sql_select(" - SELECT - `User`.*, - `UserAngelTypes`.`id` AS `user_angeltype_id`, - `UserAngelTypes`.`confirm_user_id`, - `UserAngelTypes`.`supporter`, - `UserDriverLicenses`.* - FROM `User` - JOIN `UserAngelTypes` ON `User`.`UID`=`UserAngelTypes`.`user_id` - LEFT JOIN `UserDriverLicenses` ON `User`.`UID`=`UserDriverLicenses`.`user_id` - WHERE `UserAngelTypes`.`angeltype_id`='" . sql_escape($angeltype['id']) . "' - ORDER BY `Nick`"); - if ($result === false) { + $result = DB::select(' + SELECT + `User`.*, + `UserAngelTypes`.`id` AS `user_angeltype_id`, + `UserAngelTypes`.`confirm_user_id`, + `UserAngelTypes`.`supporter`, + `UserDriverLicenses`.* + FROM `User` + JOIN `UserAngelTypes` ON `User`.`UID`=`UserAngelTypes`.`user_id` + LEFT JOIN `UserDriverLicenses` ON `User`.`UID`=`UserDriverLicenses`.`user_id` + WHERE `UserAngelTypes`.`angeltype_id`=? + ORDER BY `Nick` + ', + [ + $angeltype['id'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load members.'); } return $result; @@ -192,11 +280,11 @@ function Users_by_angeltype($angeltype) /** * Returns User id array * - * @return array|false + * @return array */ function User_ids() { - return sql_select('SELECT `UID` FROM `User`'); + return DB::select('SELECT `UID` FROM `User`'); } /** @@ -207,7 +295,7 @@ function User_ids() */ function User_validate_Nick($nick) { - return preg_replace('/([^a-z0-9üöäß. _+*-]{1,})/ui', '', $nick); + return preg_replace('/([^\wüöäß. +*-]{1,})/ui', '', $nick); } /** @@ -311,14 +399,17 @@ function User_validate_planned_departure_date($planned_arrival_date, $planned_de */ function User($user_id) { - $user_source = sql_select("SELECT * FROM `User` WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1"); - if ($user_source === false) { + $user_source = DB::select('SELECT * FROM `User` WHERE `UID`=? LIMIT 1', [$user_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user.'); } - if (count($user_source) > 0) { - return $user_source[0]; + + if (empty($user_source)) { + return null; } - return null; + + return array_shift($user_source); } /** @@ -330,13 +421,16 @@ function User($user_id) */ function User_by_api_key($api_key) { - $user = sql_select("SELECT * FROM `User` WHERE `api_key`='" . sql_escape($api_key) . "' LIMIT 1"); - if ($user === false) { + $user = DB::select('SELECT * FROM `User` WHERE `api_key`=? LIMIT 1', [$api_key]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to find user by api key.'); } - if (count($user) == 0) { + + if (empty($user)) { return null; } + return $user[0]; } @@ -348,14 +442,17 @@ function User_by_api_key($api_key) */ function User_by_email($email) { - $user = sql_select("SELECT * FROM `User` WHERE `email`='" . sql_escape($email) . "' LIMIT 1"); - if ($user === false) { + $user = DB::select('SELECT * FROM `User` WHERE `email`=? LIMIT 1', [$email]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user.'); } - if (count($user) == 0) { + + if (empty($user)) { return null; } - return $user[0]; + + return array_shift($user); } /** @@ -366,14 +463,17 @@ function User_by_email($email) */ function User_by_password_recovery_token($token) { - $user = sql_select("SELECT * FROM `User` WHERE `password_recovery_token`='" . sql_escape($token) . "' LIMIT 1"); - if ($user === false) { + $user = DB::select('SELECT * FROM `User` WHERE `password_recovery_token`=? LIMIT 1', [$token]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user.'); } - if (count($user) == 0) { + + if (empty($user)) { return null; } - return $user[0]; + + return array_shift($user); } /** @@ -386,8 +486,19 @@ function User_by_password_recovery_token($token) function User_reset_api_key(&$user, $log = true) { $user['api_key'] = md5($user['Nick'] . time() . rand()); - $result = sql_query("UPDATE `User` SET `api_key`='" . sql_escape($user['api_key']) . "' WHERE `UID`='" . sql_escape($user['UID']) . "' LIMIT 1"); - if ($result === false) { + DB::update( + ' + UPDATE `User` + SET `api_key`=? + WHERE `UID`=? + LIMIT 1 + ', + [ + $user['api_key'], + $user['UID'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { return false; } @@ -407,13 +518,18 @@ function User_reset_api_key(&$user, $log = true) function User_generate_password_recovery_token(&$user) { $user['password_recovery_token'] = md5($user['Nick'] . time() . rand()); - $result = sql_query(" - UPDATE `User` - SET `password_recovery_token`='" . sql_escape($user['password_recovery_token']) . "' - WHERE `UID`='" . sql_escape($user['UID']) . "' - LIMIT 1 - "); - if ($result === false) { + DB::update(' + UPDATE `User` + SET `password_recovery_token`=? + WHERE `UID`=? + LIMIT 1 + ', + [ + $user['password_recovery_token'], + $user['UID'], + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to generate password recovery token.'); } engelsystem_log('Password recovery for ' . User_Nick_render($user) . ' started.'); -- cgit v1.2.3-54-g00ecf