summaryrefslogtreecommitdiff
path: root/includes
diff options
context:
space:
mode:
authorIgor Scheller <igor.scheller@igorshp.de>2017-01-21 13:58:53 +0100
committerIgor Scheller <igor.scheller@igorshp.de>2017-01-21 13:58:53 +0100
commit9a3ad8883403949a59e8935497a548ec536f1d40 (patch)
treed3c27912c925e53bc240640ccc1133d8f87f1fd3 /includes
parentf7c09cb7ff84db1004a4fa83a70735475702023f (diff)
Changed from mysqli to PDO, some refactorings, faster sql queries
Diffstat (limited to 'includes')
-rw-r--r--includes/controller/shift_entries_controller.php80
-rw-r--r--includes/controller/shifts_controller.php22
-rw-r--r--includes/controller/shifttypes_controller.php11
-rw-r--r--includes/controller/user_angeltypes_controller.php3
-rw-r--r--includes/controller/users_controller.php28
-rw-r--r--includes/engelsystem_provider.php13
-rw-r--r--includes/model/AngelType_model.php164
-rw-r--r--includes/model/EventConfig_model.php72
-rw-r--r--includes/model/LogEntries_model.php43
-rw-r--r--includes/model/Message_model.php48
-rw-r--r--includes/model/NeededAngelTypes_model.php65
-rw-r--r--includes/model/Room_model.php60
-rw-r--r--includes/model/ShiftEntry_model.php200
-rw-r--r--includes/model/ShiftTypes_model.php65
-rw-r--r--includes/model/Shifts_model.php251
-rw-r--r--includes/model/UserAngelTypes_model.php177
-rw-r--r--includes/model/UserDriverLicenses_model.php87
-rw-r--r--includes/model/UserGroups_model.php20
-rw-r--r--includes/model/User_model.php296
-rw-r--r--includes/mysqli_provider.php250
-rw-r--r--includes/pages/admin_active.php102
-rw-r--r--includes/pages/admin_arrive.php18
-rw-r--r--includes/pages/admin_free.php50
-rw-r--r--includes/pages/admin_groups.php69
-rw-r--r--includes/pages/admin_import.php14
-rw-r--r--includes/pages/admin_news.php35
-rw-r--r--includes/pages/admin_questions.php39
-rw-r--r--includes/pages/admin_rooms.php41
-rw-r--r--includes/pages/admin_shifts.php45
-rw-r--r--includes/pages/admin_user.php144
-rw-r--r--includes/pages/guest_login.php95
-rw-r--r--includes/pages/guest_stats.php12
-rw-r--r--includes/pages/user_atom.php8
-rw-r--r--includes/pages/user_messages.php44
-rw-r--r--includes/pages/user_myshifts.php67
-rw-r--r--includes/pages/user_news.php81
-rw-r--r--includes/pages/user_questions.php34
-rw-r--r--includes/pages/user_settings.php28
-rw-r--r--includes/pages/user_shifts.php67
-rw-r--r--includes/sys_auth.php76
-rw-r--r--includes/sys_log.php22
-rw-r--r--includes/sys_page.php8
-rw-r--r--includes/view/User_view.php21
43 files changed, 1775 insertions, 1300 deletions
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.'));
diff --git a/includes/engelsystem_provider.php b/includes/engelsystem_provider.php
index 3f46e4ab..3537f100 100644
--- a/includes/engelsystem_provider.php
+++ b/includes/engelsystem_provider.php
@@ -1,17 +1,17 @@
<?php
+use Engelsystem\Database\Db;
use Engelsystem\Exceptions\Handler as ExceptionHandler;
/**
* This file includes all needed functions, connects to the db etc.
*/
+
if (!is_readable(__DIR__ . '/../vendor/autoload.php')) {
die('Please run composer.phar install');
}
require __DIR__ . '/../vendor/autoload.php';
-require_once realpath(__DIR__ . '/../includes/mysqli_provider.php');
-
require_once realpath(__DIR__ . '/../includes/sys_auth.php');
require_once realpath(__DIR__ . '/../includes/sys_form.php');
require_once realpath(__DIR__ . '/../includes/sys_log.php');
@@ -107,10 +107,15 @@ $errorHandler = new ExceptionHandler(
)
);
+Db::connect(
+ 'mysql:host=' . $config['host'] . ';dbname=' . $config['db'] . ';charset=utf8',
+ $config['user'],
+ $config['pw']
+) || die('Error: Unable to connect to database');
+Db::getPdo()->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+
session_start();
gettext_init();
-sql_connect($config['host'], $config['user'], $config['pw'], $config['db']);
-
load_auth();
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 @@
<?php
+
+use Engelsystem\Database\DB;
use Engelsystem\ValidationResult;
/**
@@ -75,43 +77,56 @@ function AngelType_contact_info($angeltype)
* Delete an Angeltype.
*
* @param array $angeltype
- * @return mysqli_result
+ * @return bool
*/
function AngelType_delete($angeltype)
{
- $result = sql_query("
+ $result = DB::delete('
DELETE FROM `AngelTypes`
- WHERE `id`='" . sql_escape($angeltype['id']) . "'
+ WHERE `id`=?
LIMIT 1
- ");
- if ($result === false) {
+ ', [$angeltype['id']]);
+ if (is_null($result)) {
engelsystem_error('Unable to delete angeltype.');
}
engelsystem_log('Deleted angeltype: ' . AngelType_name_render($angeltype));
- return $result;
+ return true;
}
/**
* Update Angeltype.
*
* @param array $angeltype The angeltype
- * @return mysqli_result
+ * @return bool
*/
function AngelType_update($angeltype)
{
- $result = sql_query("
- UPDATE `AngelTypes` SET
- `name`='" . sql_escape($angeltype['name']) . "',
- `restricted`=" . sql_bool($angeltype['restricted']) . ",
- `description`='" . sql_escape($angeltype['description']) . "',
- `requires_driver_license`=" . sql_bool($angeltype['requires_driver_license']) . ",
- `no_self_signup`=" . sql_bool($angeltype['no_self_signup']) . ",
- `contact_user_id`=" . sql_null($angeltype['contact_user_id']) . ",
- `contact_name`=" . sql_null($angeltype['contact_name']) . ",
- `contact_dect`=" . sql_null($angeltype['contact_dect']) . ",
- `contact_email`=" . sql_null($angeltype['contact_email']) . "
- WHERE `id`='" . sql_escape($angeltype['id']) . "'");
- if ($result === false) {
+ $result = DB::update('
+ UPDATE `AngelTypes` SET
+ `name` = ?,
+ `restricted` = ?,
+ `description` = ?,
+ `requires_driver_license` = ?,
+ `no_self_signup` = ?,
+ `contact_user_id` = ?,
+ `contact_name` = ?,
+ `contact_dect` = ?,
+ `contact_email` = ?
+ WHERE `id` = ?',
+ [
+ $angeltype['name'],
+ $angeltype['restricted'],
+ $angeltype['description'],
+ $angeltype['requires_driver_license'],
+ $angeltype['no_self_signup'],
+ $angeltype['contact_user_id'],
+ $angeltype['contact_name'],
+ $angeltype['contact_dect'],
+ $angeltype['contact_email'],
+ $angeltype['id'],
+ ]
+ );
+ if (is_null($result)) {
engelsystem_error('Unable to update angeltype.');
}
engelsystem_log(
@@ -119,7 +134,7 @@ function AngelType_update($angeltype)
. ($angeltype['no_self_signup'] ? ', no_self_signup' : '')
. ($angeltype['requires_driver_license'] ? ', requires driver license' : '')
);
- return $result;
+ return true;
}
/**
@@ -130,24 +145,41 @@ function AngelType_update($angeltype)
*/
function AngelType_create($angeltype)
{
- $result = sql_query("
- INSERT INTO `AngelTypes` SET
- `name`='" . sql_escape($angeltype['name']) . "',
- `restricted`=" . sql_bool($angeltype['restricted']) . ",
- `description`='" . sql_escape($angeltype['description']) . "',
- `requires_driver_license`=" . sql_bool($angeltype['requires_driver_license']) . ",
- `no_self_signup`=" . sql_bool($angeltype['no_self_signup']) . ",
- `contact_user_id`=" . sql_null($angeltype['contact_user_id']) . ",
- `contact_name`=" . sql_null($angeltype['contact_name']) . ",
- `contact_dect`=" . sql_null($angeltype['contact_dect']) . ",
- `contact_email`=" . sql_null($angeltype['contact_email']));
- if ($result === false) {
- engelsystem_error("Unable to create angeltype.");
+ $result = DB::insert('
+ INSERT INTO `AngelTypes` (
+ `name`,
+ `restricted`,
+ `description`,
+ `requires_driver_license`,
+ `no_self_signup`,
+ `contact_user_id`,
+ `contact_name`,
+ `contact_dect`,
+ `contact_email`
+ )
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
+ ',
+ [
+ $angeltype['name'],
+ (bool)$angeltype['restricted'],
+ $angeltype['description'],
+ (bool)$angeltype['requires_driver_license'],
+ (bool)$angeltype['no_self_signup'],
+ $angeltype['contact_user_id'],
+ $angeltype['contact_name'],
+ $angeltype['contact_dect'],
+ $angeltype['contact_email'],
+ ]
+ );
+ if (is_null($result)) {
+ engelsystem_error('Unable to create angeltype.');
}
- $angeltype['id'] = sql_id();
+ $angeltype['id'] = DB::getPdo()->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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Get event config.
*
- * łreturn array|false|null
+ * @return array|null
*/
function EventConfig()
{
- $event_config = sql_select('SELECT * FROM `EventConfig` LIMIT 1');
- if ($event_config === false) {
+ $event_config = DB::select('SELECT * FROM `EventConfig` LIMIT 1');
+ if (DB::getStm()->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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Creates a log entry.
*
* @param string $nick Username
* @param string $message Log Message
- * @return mysqli_result|false
+ * @return bool
*/
function LogEntry_create($nick, $message)
{
- return sql_query("
- INSERT INTO `LogEntries`
- SET
- `timestamp`='" . sql_escape(time()) . "',
- `nick`='" . sql_escape($nick) . "',
- `message`='" . sql_escape($message) . "'
- ");
+ return DB::insert('
+ INSERT INTO `LogEntries` (`timestamp`, `nick`, `message`)
+ VALUES(?, ?, ?)
+ ', [time(), $nick, $message]);
}
/**
* Returns log entries with maximum count of 10000.
*
- * @return array|false
+ * @return array
*/
function LogEntries()
{
- return sql_select('SELECT * FROM `LogEntries` ORDER BY `timestamp` DESC LIMIT 10000');
+ return DB::select('SELECT * FROM `LogEntries` ORDER BY `timestamp` DESC LIMIT 10000');
}
/**
* Returns log entries filtered by a keyword
*
* @param string $keyword
- * @return array|false
+ * @return array
*/
function LogEntries_filter($keyword)
{
if ($keyword == '') {
return LogEntries();
}
- return sql_select("
- SELECT *
- FROM `LogEntries`
- WHERE `nick` LIKE '%" . sql_escape($keyword) . "%'
- OR `message` LIKE '%" . sql_escape($keyword) . "%'
- ORDER BY `timestamp` DESC
- ");
+
+ $keyword = '%' . $keyword . '%';
+ return DB::select('
+ SELECT *
+ FROM `LogEntries`
+ WHERE `nick` LIKE ?
+ OR `message` LIKE ?
+ ORDER BY `timestamp` DESC
+ ',
+ [$keyword, $keyword]
+ );
}
/**
* Delete all log entries.
*
- * @return mysqli_result|false
+ * @return bool
*/
function LogEntries_clear_all()
{
- return sql_query('TRUNCATE `LogEntries`');
+ return DB::statement('TRUNCATE `LogEntries`');
}
diff --git a/includes/model/Message_model.php b/includes/model/Message_model.php
index e998ba04..a7da63b8 100644
--- a/includes/model/Message_model.php
+++ b/includes/model/Message_model.php
@@ -1,31 +1,30 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Returns Message id array
*
- * @return array|false
+ * @return array
*/
function Message_ids()
{
- return sql_select('SELECT `id` FROM `Messages`');
+ return DB::select('SELECT `id` FROM `Messages`');
}
/**
* Returns message by id.
*
* @param int $message_id message ID
- * @return array|false|null
+ * @return array|null
*/
function Message($message_id)
{
- $message_source = sql_select("SELECT * FROM `Messages` WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1");
- if ($message_source === false) {
- return false;
- }
- if (count($message_source) > 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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Entity needed angeltypes describes how many angels of given type are needed for a shift or in a room.
*/
@@ -7,50 +9,54 @@
/**
* Insert a new needed angel type.
*
- * @param int $shift_id
- * The shift. Can be null, but then a room_id must be given.
- * @param int $angeltype_id
- * The angeltype
- * @param int $room_id
- * The room. Can be null, but then a shift_id must be given.
- * @param int $count
- * How many angels are needed?
- * @return false|int
+ * @param int $shift_id The shift. Can be null, but then a room_id must be given.
+ * @param int $angeltype_id The angeltype
+ * @param int $room_id The room. Can be null, but then a shift_id must be given.
+ * @param int $count How many angels are needed?
+ * @return int|false
*/
function NeededAngelType_add($shift_id, $angeltype_id, $room_id, $count)
{
- $result = sql_query("
- INSERT INTO `NeededAngelTypes` SET
- `shift_id`=" . sql_null($shift_id) . ",
- `angel_type_id`='" . sql_escape($angeltype_id) . "',
- `room_id`=" . sql_null($room_id) . ",
- `count`='" . sql_escape($count) . "'");
+ $result = DB::insert('
+ INSERT INTO `NeededAngelTypes` ( `shift_id`, `angel_type_id`, `room_id`, `count`)
+ VALUES (?, ?, ?, ?)
+ ',
+ [
+ $shift_id,
+ $angeltype_id,
+ $room_id,
+ $count,
+ ]);
if ($result === false) {
return false;
}
- return sql_id();
+
+ return DB::getPdo()->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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* returns a list of rooms.
*
* @param boolean $show_all returns also hidden rooms when true
- * @return array|false
+ * @return array
*/
function Rooms($show_all = false)
{
- return sql_select("SELECT * FROM `Room`" . ($show_all ? "" : " WHERE `show`='Y'") . " ORDER BY `Name`");
+ return DB::select('SELECT * FROM `Room`' . ($show_all ? '' : ' WHERE `show`=\'Y\'') . ' ORDER BY `Name`');
}
/**
* Delete a room
*
* @param int $room_id
- * @return mysqli_result|false
+ * @return bool
*/
function Room_delete($room_id)
{
- return sql_query('DELETE FROM `Room` WHERE `RID`=' . sql_escape($room_id));
+ return DB::delete('DELETE FROM `Room` WHERE `RID` = ?', [$room_id]);
}
/**
* Create a new room
*
- * @param string $name
- * Name of the room
- * @param boolean $from_frab
- * Is this a frab imported room?
- * @param boolean $public
- * Is the room visible for angels?
- * @param int $number
- * Room number
+ * @param string $name Name of the room
+ * @param boolean $from_frab Is this a frab imported room?
+ * @param boolean $public Is the room visible for angels?
+ * @param int $number Room number
* @return false|int
*/
function Room_create($name, $from_frab, $public, $number = null)
{
- $result = sql_query("
- INSERT INTO `Room` SET
- `Name`='" . sql_escape($name) . "',
- `FromPentabarf`='" . sql_escape($from_frab ? 'Y' : '') . "',
- `show`='" . sql_escape($public ? 'Y' : '') . "',
- `Number`=" . (int)$number
+ $result = DB::insert('
+ INSERT INTO `Room` (`Name`, `FromPentabarf`, `show`, `Number`)
+ VALUES (?, ?, ?, ?)
+ ',
+ [
+ $name,
+ $from_frab ? 'Y' : '',
+ $public ? 'Y' : '',
+ (int)$number,
+ ]
);
- if ($result === false) {
+ if (!$result) {
return false;
}
- return sql_id();
+
+ return DB::getPdo()->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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Returns an array with the attributes of shift entries.
* FIXME! Needs entity object.
@@ -26,67 +28,100 @@ function ShiftEntry_new()
*/
function ShiftEntries_freeleaded_count()
{
- return (int)sql_select_single_cell('SELECT COUNT(*) FROM `ShiftEntry` WHERE `freeloaded` = 1');
+ $result = DB::select('SELECT COUNT(*) FROM `ShiftEntry` WHERE `freeloaded` = 1');
+ $result = array_shift($result);
+
+ if (!is_array($result)) {
+ return 0;
+ }
+
+ return (int)array_shift($result);
}
/**
* List users subsribed to a given shift.
*
* @param int $shift_id
- * @return array|false
+ * @return array
*/
function ShiftEntries_by_shift($shift_id)
{
- return sql_select("
- SELECT
- `User`.`Nick`,
- `User`.`email`,
- `User`.`email_shiftinfo`,
- `User`.`Sprache`,
- `User`.`Gekommen`,
- `ShiftEntry`.`UID`,
- `ShiftEntry`.`TID`,
- `ShiftEntry`.`SID`,
- `AngelTypes`.`name` AS `angel_type_name`,
- `ShiftEntry`.`Comment`,
- `ShiftEntry`.`freeloaded`
- FROM `ShiftEntry`
- JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID`
- JOIN `AngelTypes` ON `ShiftEntry`.`TID`=`AngelTypes`.`id`
- WHERE `ShiftEntry`.`SID`='" . sql_escape($shift_id) . "'");
+ return DB::select("
+ SELECT
+ `User`.`Nick`,
+ `User`.`email`,
+ `User`.`email_shiftinfo`,
+ `User`.`Sprache`,
+ `User`.`Gekommen`,
+ `ShiftEntry`.`UID`,
+ `ShiftEntry`.`TID`,
+ `ShiftEntry`.`SID`,
+ `AngelTypes`.`name` AS `angel_type_name`,
+ `ShiftEntry`.`Comment`,
+ `ShiftEntry`.`freeloaded`
+ FROM `ShiftEntry`
+ JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID`
+ JOIN `AngelTypes` ON `ShiftEntry`.`TID`=`AngelTypes`.`id`
+ WHERE `ShiftEntry`.`SID` = ?",
+ [$shift_id]
+ );
}
/**
* Create a new shift entry.
*
* @param array $shift_entry
- * @return mysqli_result|false
+ * @return bool
*/
function ShiftEntry_create($shift_entry)
{
mail_shift_assign(User($shift_entry['UID']), Shift($shift_entry['SID']));
- return sql_query("INSERT INTO `ShiftEntry` SET
- `SID`='" . sql_escape($shift_entry['SID']) . "',
- `TID`='" . sql_escape($shift_entry['TID']) . "',
- `UID`='" . sql_escape($shift_entry['UID']) . "',
- `Comment`='" . sql_escape($shift_entry['Comment']) . "',
- `freeload_comment`='" . sql_escape($shift_entry['freeload_comment']) . "',
- `freeloaded`=" . sql_bool($shift_entry['freeloaded']));
+ return DB::insert('
+ INSERT INTO `ShiftEntry` (
+ `SID`,
+ `TID`,
+ `UID`,
+ `Comment`,
+ `freeload_comment`,
+ `freeloaded`
+ )
+ VALUES(?, ?, ?, ?, ?, ?)
+ ',
+ [
+ $shift_entry['SID'],
+ $shift_entry['TID'],
+ $shift_entry['UID'],
+ $shift_entry['Comment'],
+ $shift_entry['freeload_comment'],
+ $shift_entry['freeloaded'],
+ ]
+ );
}
/**
* Update a shift entry.
*
* @param array $shift_entry
- * @return false|mysqli_result
+ * @return bool
*/
function ShiftEntry_update($shift_entry)
{
- return sql_query("UPDATE `ShiftEntry` SET
- `Comment`='" . sql_escape($shift_entry['Comment']) . "',
- `freeload_comment`='" . sql_escape($shift_entry['freeload_comment']) . "',
- `freeloaded`=" . sql_bool($shift_entry['freeloaded']) . "
- WHERE `id`='" . sql_escape($shift_entry['id']) . "'");
+ DB::update('
+ UPDATE `ShiftEntry`
+ SET
+ `Comment` = ?,
+ `freeload_comment` = ?,
+ `freeloaded` = ?
+ WHERE `id` = ?',
+ [
+ $shift_entry['Comment'],
+ $shift_entry['freeload_comment'],
+ $shift_entry['freeloaded'],
+ $shift_entry['id']
+ ]
+ );
+
+ return (DB::getStm()->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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Delete a shift type.
*
* @param int $shifttype_id
- * @return mysqli_result|false
+ * @return bool
*/
function ShiftType_delete($shifttype_id)
{
- return sql_query("DELETE FROM `ShiftTypes` WHERE `id`='" . sql_escape($shifttype_id) . "'");
+ return DB::delete('DELETE FROM `ShiftTypes` WHERE `id`=?', [$shifttype_id]);
}
/**
@@ -18,17 +20,26 @@ function ShiftType_delete($shifttype_id)
* @param string $name
* @param int $angeltype_id
* @param string $description
- * @return mysqli_result|false
+ * @return bool
*/
function ShiftType_update($shifttype_id, $name, $angeltype_id, $description)
{
- return sql_query("
+ DB::update('
UPDATE `ShiftTypes` SET
- `name`='" . sql_escape($name) . "',
- `angeltype_id`=" . sql_null($angeltype_id) . ",
- `description`='" . sql_escape($description) . "'
- WHERE `id`='" . sql_escape($shifttype_id) . "'
- ");
+ `name`=?,
+ `angeltype_id`=?,
+ `description`=?
+ WHERE `id`=?
+ ',
+ [
+ $name,
+ $angeltype_id,
+ $description,
+ $shifttype_id,
+ ]
+ );
+
+ return DB::getStm()->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 @@
<?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;
}
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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* User angeltypes model
*/
@@ -13,12 +15,12 @@
*/
function UserAngelType_exists($user, $angeltype)
{
- return sql_num_query("
+ return count(DB::select('
SELECT `id`
FROM `UserAngelTypes`
- WHERE `UserAngelTypes`.`user_id`='" . sql_escape($user['UID']) . "'
- AND `angeltype_id`='" . sql_escape($angeltype['id']) . "'
- ") > 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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Returns a new empty UserDriverLicense
* FIXME entity object needed
@@ -43,17 +45,21 @@ function UserDriverLicense_valid($user_driver_license)
*/
function UserDriverLicense($user_id)
{
- $user_driver_license = sql_select("SELECT * FROM `UserDriverLicenses` WHERE `user_id`='" . sql_escape($user_id) . "'");
- if ($user_driver_license === false) {
+ $user_driver_license = DB::select('
+ SELECT *
+ FROM `UserDriverLicenses`
+ WHERE `user_id`=?', [$user_id]);
+
+ if (DB::getStm()->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 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Returns users groups
*
* @param array $user
- * @return array|false
+ * @return array
*/
function User_groups($user)
{
- return sql_select('
- SELECT `Groups`.*
- FROM `UserGroups`
- JOIN `Groups` ON `Groups`.`UID`=`UserGroups`.`group_id`
- WHERE `UserGroups`.`uid`=\'' . sql_escape($user['UID']) . '\'
- ORDER BY `UserGroups`.`group_id`
- ');
+ return DB::select('
+ SELECT `Groups`.*
+ FROM `UserGroups`
+ JOIN `Groups` ON `Groups`.`UID`=`UserGroups`.`group_id`
+ WHERE `UserGroups`.`uid`=?
+ ORDER BY `UserGroups`.`group_id`
+ ',
+ [$user['UID']]
+ );
}
diff --git a/includes/model/User_model.php b/includes/model/User_model.php
index 22299664..836ca914 100644
--- a/includes/model/User_model.php
+++ b/includes/model/User_model.php
@@ -1,4 +1,6 @@
<?php
+
+use Engelsystem\Database\DB;
use Engelsystem\ValidationResult;
/**
@@ -9,90 +11,154 @@ use Engelsystem\ValidationResult;
* Delete a user
*
* @param int $user_id
- * @return mysqli_result|false
+ * @return bool
*/
function User_delete($user_id)
{
- return sql_query("DELETE FROM `User` WHERE `UID`='" . sql_escape($user_id) . "'");
+ DB::delete('DELETE FROM `User` WHERE `UID`=?', [$user_id]);
+
+ return DB::getStm()->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.');
diff --git a/includes/mysqli_provider.php b/includes/mysqli_provider.php
deleted file mode 100644
index 0efb670a..00000000
--- a/includes/mysqli_provider.php
+++ /dev/null
@@ -1,250 +0,0 @@
-<?php
-/** @var mysqli $sql_connection */
-$sql_connection = null;
-
-/**
- * Close connection.
- *
- * @return bool
- */
-function sql_close()
-{
- global $sql_connection;
-
- return $sql_connection->close();
-}
-
-/**
- * Return NULL if given value is null.
- *
- * @param mixed $value
- * @return bool
- */
-function sql_null($value = null)
-{
- return $value == null ? 'NULL' : ("'" . sql_escape($value) . "'");
-}
-
-/**
- * Start new transaction.
- *
- * @return mysqli_result|bool
- */
-function sql_transaction_start()
-{
- global $sql_nested_transaction_level;
-
- if ($sql_nested_transaction_level++ == 0) {
- return sql_query('BEGIN');
- }
-
- return true;
-}
-
-/**
- * Commit transaction.
- *
- * @return mysqli_result|bool
- */
-function sql_transaction_commit()
-{
- global $sql_nested_transaction_level;
-
- if (--$sql_nested_transaction_level == 0) {
- return sql_query('COMMIT');
- }
-
- return true;
-}
-
-/**
- * Stop transaction, revert database.
- *
- * @return mysqli_result|bool
- */
-function sql_transaction_rollback()
-{
- global $sql_nested_transaction_level;
-
- if (--$sql_nested_transaction_level == 0) {
- return sql_query('ROLLBACK');
- }
-
- return true;
-}
-
-/**
- * Logs an sql error.
- *
- * @param string $message
- * @return false
- */
-function sql_error($message)
-{
- // @TODO: Bad idea..
- sql_close();
-
- $message = trim($message) . "\n";
- $message .= debug_string_backtrace() . "\n";
-
- error_log('mysql_provider error: ' . $message);
-
- return false;
-}
-
-/**
- * Connect to mysql server.
- *
- * @param string $host Host
- * @param string $user Username
- * @param string $pass Password
- * @param string $db_name DB to select
- * @return mysqli|false The connection handler
- */
-function sql_connect($host, $user, $pass, $db_name)
-{
- global $sql_connection;
-
- $sql_connection = new mysqli($host, $user, $pass, $db_name);
- if ($sql_connection->connect_errno) {
- error('Unable to connect to MySQL: ' . $sql_connection->connect_error);
- return sql_error('Unable to connect to MySQL: ' . $sql_connection->connect_error);
- }
-
- $result = $sql_connection->query('SET CHARACTER SET utf8;');
- if (!$result) {
- return sql_error('Unable to set utf8 character set (' . $sql_connection->errno . ') ' . $sql_connection->error);
- }
-
- $result = $sql_connection->set_charset('utf8');
- if (!$result) {
- return sql_error('Unable to set utf8 names (' . $sql_connection->errno . ') ' . $sql_connection->error);
- }
-
- return $sql_connection;
-}
-
-/**
- * Change the selected db in current mysql-connection.
- *
- * @param $db_name
- * @return bool true on success, false on error
- */
-function sql_select_db($db_name)
-{
- global $sql_connection;
- if (!$sql_connection->select_db($db_name)) {
- return sql_error('No database selected.');
- }
- return true;
-}
-
-/**
- * MySQL SELECT query
- *
- * @param string $query
- * @return array|false Result array or false on error
- */
-function sql_select($query)
-{
- global $sql_connection;
-
- $result = $sql_connection->query($query);
- if ($result) {
- $data = [];
- while ($line = $result->fetch_assoc()) {
- array_push($data, $line);
- }
- return $data;
- }
-
- return sql_error('MySQL-query error: ' . $query . ' (' . $sql_connection->errno . ') ' . $sql_connection->error);
-}
-
-/**
- * MySQL execute a query
- *
- * @param string $query
- * @return mysqli_result|false boolean resource or false on error
- */
-function sql_query($query)
-{
- global $sql_connection;
-
- $result = $sql_connection->query($query);
- if ($result) {
- return $result;
- }
-
- return sql_error('MySQL-query error: ' . $query . ' (' . $sql_connection->errno . ') ' . $sql_connection->error);
-}
-
-/**
- * Returns last inserted id.
- *
- * @return int
- */
-function sql_id()
-{
- global $sql_connection;
- return $sql_connection->insert_id;
-}
-
-/**
- * Escape a string for a sql query.
- *
- * @param string $query
- * @return string
- */
-function sql_escape($query)
-{
- global $sql_connection;
- return $sql_connection->real_escape_string($query);
-}
-
-/**
- * Convert a boolean for mysql-queries.
- *
- * @param boolean $boolean
- * @return string
- */
-function sql_bool($boolean)
-{
- return $boolean == true ? 'TRUE' : 'FALSE';
-}
-
-/**
- * Count query result lines.
- *
- * @param string $query
- * @return int Count of result lines
- */
-function sql_num_query($query)
-{
- return sql_query($query)->num_rows;
-}
-
-function sql_select_single_col($query)
-{
- $result = sql_select($query);
- return array_map('array_shift', $result);
-}
-
-/**
- * @param string $query
- * @return string|null
- */
-function sql_select_single_cell($query)
-{
- $result = sql_select($query);
- if ($result == false) {
- return null;
- }
-
- $result = array_shift($result);
- if (!is_array($result)) {
- return null;
- }
-
- return array_shift($result);
-}
diff --git a/includes/pages/admin_active.php b/includes/pages/admin_active.php
index c4b6e119..d3b290f9 100644
--- a/includes/pages/admin_active.php
+++ b/includes/pages/admin_active.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -17,7 +19,7 @@ function admin_active()
$msg = '';
$search = '';
- $forced_count = sql_num_query('SELECT * FROM `User` WHERE `force_active`=1');
+ $forced_count = count(DB::select('SELECT `UID` FROM `User` WHERE `force_active`=1'));
$count = $forced_count;
$limit = '';
$set_active = '';
@@ -49,22 +51,31 @@ function admin_active()
$limit = ' LIMIT ' . $count;
}
if (isset($_REQUEST['ack'])) {
- sql_query('UPDATE `User` SET `Aktiv` = 0 WHERE `Tshirt` = 0');
- $users = sql_select("
- SELECT `User`.*, COUNT(`ShiftEntry`.`id`) as `shift_count`, $shift_sum_formula as `shift_length`
- FROM `User`
- LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID`
- LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID`
- WHERE `User`.`Gekommen` = 1 AND `User`.`force_active`=0
- GROUP BY `User`.`UID`
- ORDER BY `force_active` DESC, `shift_length` DESC" . $limit
- );
+ DB::update('UPDATE `User` SET `Aktiv` = 0 WHERE `Tshirt` = 0');
+ $users = DB::select(sprintf(
+ '
+ SELECT
+ `User`.*,
+ COUNT(`ShiftEntry`.`id`) AS `shift_count`,
+ %s AS `shift_length`
+ FROM `User`
+ LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID`
+ LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID`
+ WHERE `User`.`Gekommen` = 1
+ AND `User`.`force_active`=0
+ GROUP BY `User`.`UID`
+ ORDER BY `force_active` DESC, `shift_length` DESC
+ %s
+ ',
+ $shift_sum_formula,
+ $limit
+ ));
$user_nicks = [];
foreach ($users as $usr) {
- sql_query('UPDATE `User` SET `Aktiv` = 1 WHERE `UID`=\'' . sql_escape($usr['UID']) . '\'');
+ DB::update('UPDATE `User` SET `Aktiv` = 1 WHERE `UID`=?', [$usr['UID']]);
$user_nicks[] = User_Nick_render($usr);
}
- sql_query('UPDATE `User` SET `Aktiv`=1 WHERE `force_active`=TRUE');
+ DB::update('UPDATE `User` SET `Aktiv`=1 WHERE `force_active`=TRUE');
engelsystem_log('These angels are active now: ' . join(', ', $user_nicks));
$limit = '';
@@ -82,7 +93,7 @@ function admin_active()
$user_id = $_REQUEST['active'];
$user_source = User($user_id);
if ($user_source != null) {
- sql_query('UPDATE `User` SET `Aktiv`=1 WHERE `UID`=\'' . sql_escape($user_id) . '\' LIMIT 1');
+ DB::update('UPDATE `User` SET `Aktiv`=1 WHERE `UID`=? LIMIT 1', [$user_id]);
engelsystem_log('User ' . User_Nick_render($user_source) . ' is active now.');
$msg = success(_('Angel has been marked as active.'), true);
} else {
@@ -92,7 +103,7 @@ function admin_active()
$user_id = $_REQUEST['not_active'];
$user_source = User($user_id);
if ($user_source != null) {
- sql_query("UPDATE `User` SET `Aktiv`=0 WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1");
+ DB::update('UPDATE `User` SET `Aktiv`=0 WHERE `UID`=? LIMIT 1', [$user_id]);
engelsystem_log('User ' . User_Nick_render($user_source) . ' is NOT active now.');
$msg = success(_('Angel has been marked as not active.'), true);
} else {
@@ -102,7 +113,7 @@ function admin_active()
$user_id = $_REQUEST['tshirt'];
$user_source = User($user_id);
if ($user_source != null) {
- sql_query("UPDATE `User` SET `Tshirt`=1 WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1");
+ DB::update('UPDATE `User` SET `Tshirt`=1 WHERE `UID`=? LIMIT 1', [$user_id]);
engelsystem_log('User ' . User_Nick_render($user_source) . ' has tshirt now.');
$msg = success(_('Angel has got a t-shirt.'), true);
} else {
@@ -112,7 +123,7 @@ function admin_active()
$user_id = $_REQUEST['not_tshirt'];
$user_source = User($user_id);
if ($user_source != null) {
- sql_query("UPDATE `User` SET `Tshirt`=0 WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1");
+ DB::update('UPDATE `User` SET `Tshirt`=0 WHERE `UID`=? LIMIT 1', [$user_id]);
engelsystem_log('User ' . User_Nick_render($user_source) . ' has NO tshirt.');
$msg = success(_('Angel has got no t-shirt.'), true);
} else {
@@ -120,18 +131,22 @@ function admin_active()
}
}
- $users = sql_select("
- SELECT
- `User`.*,
- COUNT(`ShiftEntry`.`id`) AS `shift_count`,
- ${shift_sum_formula} AS `shift_length`
- FROM `User` LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID`
- LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID` "
- . ($show_all_shifts ? "" : "AND (`Shifts`.`end` < " . time() . " OR `Shifts`.`end` IS NULL)") . "
- WHERE `User`.`Gekommen` = 1
- GROUP BY `User`.`UID`
- ORDER BY `force_active` DESC, `shift_length` DESC" . $limit
- );
+ $users = DB::select(sprintf('
+ SELECT
+ `User`.*,
+ COUNT(`ShiftEntry`.`id`) AS `shift_count`,
+ %s AS `shift_length`
+ FROM `User` LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID`
+ LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID` '
+ . ($show_all_shifts ? '' : 'AND (`Shifts`.`end` < ' . time() . " OR `Shifts`.`end` IS NULL)") . '
+ WHERE `User`.`Gekommen` = 1
+ GROUP BY `User`.`UID`
+ ORDER BY `force_active` DESC, `shift_length` DESC
+ %s
+ ',
+ $shift_sum_formula,
+ $limit
+ ));
$matched_users = [];
if ($search == '') {
$tokens = [];
@@ -194,21 +209,36 @@ function admin_active()
$shirt_statistics = [];
foreach (array_keys($tshirt_sizes) as $size) {
if ($size != '') {
+ $sc = DB::select(
+ 'SELECT count(*) FROM `User` WHERE `Size`=? AND `Gekommen`=1',
+ [$size]
+ );
+ $sc = array_shift($sc);
+ $sc = array_shift($sc);
+
+ $gc = DB::select(
+ 'SELECT count(*) FROM `User` WHERE `Size`=? AND `Tshirt`=1',
+ [$size]
+ );
+ $gc = array_shift($gc);
+ $gc = array_shift($gc);
+
$shirt_statistics[] = [
'size' => $size,
- 'needed' => sql_select_single_cell(
- "SELECT count(*) FROM `User` WHERE `Size`='" . sql_escape($size) . "' AND `Gekommen`=1"
- ),
- 'given' => sql_select_single_cell(
- "SELECT count(*) FROM `User` WHERE `Size`='" . sql_escape($size) . "' AND `Tshirt`=1"
- )
+ 'needed' => (int)$sc,
+ 'given' => (int)$gc
];
}
}
+
+ $uc = DB::select('SELECT count(*) FROM `User` WHERE `Tshirt`=1');
+ $uc = array_shift($uc);
+ $uc = array_shift($uc);
+
$shirt_statistics[] = [
'size' => '<b>' . _('Sum') . '</b>',
'needed' => '<b>' . User_arrived_count() . '</b>',
- 'given' => '<b>' . sql_select_single_cell('SELECT count(*) FROM `User` WHERE `Tshirt`=1') . '</b>'
+ 'given' => '<b>' . (int)$uc . '</b>'
];
return page_with_title(admin_active_title(), [
diff --git a/includes/pages/admin_arrive.php b/includes/pages/admin_arrive.php
index a17408e7..0080ccf9 100644
--- a/includes/pages/admin_arrive.php
+++ b/includes/pages/admin_arrive.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -23,12 +25,12 @@ function admin_arrive()
$user_id = $_REQUEST['reset'];
$user_source = User($user_id);
if ($user_source != null) {
- sql_query("
+ DB::update('
UPDATE `User`
SET `Gekommen`=0, `arrival_date` = NULL
- WHERE `UID`='" . sql_escape($user_id) . "'
+ WHERE `UID`=?
LIMIT 1
- ");
+ ', [$user_id]);
engelsystem_log('User set to not arrived: ' . User_Nick_render($user_source));
success(_('Reset done. Angel has not arrived.'));
redirect(user_link($user_source));
@@ -39,12 +41,12 @@ function admin_arrive()
$user_id = $_REQUEST['arrived'];
$user_source = User($user_id);
if ($user_source != null) {
- sql_query("
+ DB::update('
UPDATE `User`
- SET `Gekommen`=1, `arrival_date`='" . time() . "'
- WHERE `UID`='" . sql_escape($user_id) . "'
+ SET `Gekommen`=1, `arrival_date`=?
+ WHERE `UID`=?
LIMIT 1
- ");
+ ', [time(), $user_id]);
engelsystem_log('User set has arrived: ' . User_Nick_render($user_source));
success(_('Angel has been marked as arrived.'));
redirect(user_link($user_source));
@@ -53,7 +55,7 @@ function admin_arrive()
}
}
- $users = sql_select('SELECT * FROM `User` ORDER BY `Nick`');
+ $users = DB::select('SELECT * FROM `User` ORDER BY `Nick`');
$arrival_count_at_day = [];
$planned_arrival_count_at_day = [];
$planned_departure_count_at_day = [];
diff --git a/includes/pages/admin_free.php b/includes/pages/admin_free.php
index 67d91006..630603ba 100644
--- a/includes/pages/admin_free.php
+++ b/includes/pages/admin_free.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -20,19 +22,20 @@ function admin_free()
$search = strip_request_item('search');
}
- $angeltypesearch = '';
+ $angelTypeSearch = '';
if (empty($_REQUEST['angeltype'])) {
$_REQUEST['angeltype'] = '';
} else {
- $angeltypesearch = ' INNER JOIN `UserAngelTypes` ON (`UserAngelTypes`.`angeltype_id` = \''
- . sql_escape($_REQUEST['angeltype']) . "' AND `UserAngelTypes`.`user_id` = `User`.`UID`";
+ $angelTypeSearch = ' INNER JOIN `UserAngelTypes` ON (`UserAngelTypes`.`angeltype_id` = '
+ . DB::getPdo()->quote($_REQUEST['angeltype'])
+ . ' AND `UserAngelTypes`.`user_id` = `User`.`UID`';
if (isset($_REQUEST['confirmed_only'])) {
- $angeltypesearch .= ' AND `UserAngelTypes`.`confirm_user_id`';
+ $angelTypeSearch .= ' AND `UserAngelTypes`.`confirm_user_id`';
}
- $angeltypesearch .= ') ';
+ $angelTypeSearch .= ') ';
}
- $angel_types_source = sql_select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`');
+ $angel_types_source = DB::select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`');
$angel_types = [
'' => 'alle Typen'
];
@@ -40,20 +43,27 @@ function admin_free()
$angel_types[$angel_type['id']] = $angel_type['name'];
}
- $users = sql_select("
- SELECT `User`.*
- FROM `User`
- ${angeltypesearch}
- LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID`
- LEFT JOIN `Shifts`
- ON (
- `ShiftEntry`.`SID` = `Shifts`.`SID`
- AND `Shifts`.`start` < '" . sql_escape(time()) . "'
- AND `Shifts`.`end` > '" . sql_escape(time()) . "'
- )
- WHERE `User`.`Gekommen` = 1 AND `Shifts`.`SID` IS NULL
- GROUP BY `User`.`UID`
- ORDER BY `Nick`");
+ $users = DB::select('
+ SELECT `User`.*
+ FROM `User`
+ ' . $angelTypeSearch . '
+ LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID`
+ LEFT JOIN `Shifts`
+ ON (
+ `ShiftEntry`.`SID` = `Shifts`.`SID`
+ AND `Shifts`.`start` < ?
+ AND `Shifts`.`end` > ?
+ )
+ WHERE `User`.`Gekommen` = 1
+ AND `Shifts`.`SID` IS NULL
+ GROUP BY `User`.`UID`
+ ORDER BY `Nick`
+ ',
+ [
+ time(),
+ time(),
+ ]
+ );
$free_users_table = [];
if ($search == '') {
diff --git a/includes/pages/admin_groups.php b/includes/pages/admin_groups.php
index 8e578cb2..bc33a2b0 100644
--- a/includes/pages/admin_groups.php
+++ b/includes/pages/admin_groups.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -14,20 +16,20 @@ function admin_groups_title()
function admin_groups()
{
$html = '';
- $groups = sql_select('SELECT * FROM `Groups` ORDER BY `Name`');
+ $groups = DB::select('SELECT * FROM `Groups` ORDER BY `Name`');
if (!isset($_REQUEST['action'])) {
$groups_table = [];
foreach ($groups as $group) {
- $privileges = sql_select("
- SELECT *
+ $privileges = DB::select('
+ SELECT `name`
FROM `GroupPrivileges`
JOIN `Privileges` ON (`GroupPrivileges`.`privilege_id` = `Privileges`.`id`)
- WHERE `group_id`='" . sql_escape($group['UID']) . "'
- ");
+ WHERE `group_id`=?
+ ', [$group['UID']]);
$privileges_html = [];
- foreach ($privileges as $priv) {
- $privileges_html[] = $priv['name'];
+ foreach ($privileges as $privilege) {
+ $privileges_html[] = $privilege['name'];
}
$groups_table[] = [
@@ -57,33 +59,33 @@ function admin_groups()
return error('Incomplete call, missing Groups ID.', true);
}
- $group = sql_select("SELECT * FROM `Groups` WHERE `UID`='" . sql_escape($group_id) . "' LIMIT 1");
- if (count($group) > 0) {
- $privileges = sql_select("
+ $group = DB::select('SELECT * FROM `Groups` WHERE `UID`=? LIMIT 1', [$group_id]);
+ if (!empty($group)) {
+ $privileges = DB::select('
SELECT `Privileges`.*, `GroupPrivileges`.`group_id`
FROM `Privileges`
LEFT OUTER JOIN `GroupPrivileges`
ON (
`Privileges`.`id` = `GroupPrivileges`.`privilege_id`
- AND `GroupPrivileges`.`group_id`='" . sql_escape($group_id) . "'
+ AND `GroupPrivileges`.`group_id`=?
)
ORDER BY `Privileges`.`name`
- ");
+ ', [$group_id]);
$privileges_html = '';
$privileges_form = [];
- foreach ($privileges as $priv) {
+ foreach ($privileges as $privilege) {
$privileges_form[] = form_checkbox(
'privileges[]',
- $priv['desc'] . ' (' . $priv['name'] . ')',
- $priv['group_id'] != '',
- $priv['id']
+ $privilege['desc'] . ' (' . $privilege['name'] . ')',
+ $privilege['group_id'] != '',
+ $privilege['id']
);
$privileges_html .= sprintf(
'<tr><td><input type="checkbox" name="privileges[]" value="%s" %s /></td> <td>%s</td> <td>%s</td></tr>',
- $priv['id'],
- ($priv['group_id'] != '' ? 'checked="checked"' : ''),
- $priv['name'],
- $priv['desc']
+ $privilege['id'],
+ ($privilege['group_id'] != '' ? 'checked="checked"' : ''),
+ $privilege['name'],
+ $privilege['desc']
);
}
@@ -103,20 +105,27 @@ function admin_groups()
return error('Incomplete call, missing Groups ID.', true);
}
- $group = sql_select("SELECT * FROM `Groups` WHERE `UID`='" . sql_escape($group_id) . "' LIMIT 1");
+ $group = DB::select('SELECT * FROM `Groups` WHERE `UID`=? LIMIT 1', [$group_id]);
if (!is_array($_REQUEST['privileges'])) {
$_REQUEST['privileges'] = [];
}
- if (count($group) > 0) {
- list($group) = $group;
- sql_query("DELETE FROM `GroupPrivileges` WHERE `group_id`='" . sql_escape($group_id) . "'");
+ if (!empty($group)) {
+ $group = array_shift($group);
+ DB::delete('DELETE FROM `GroupPrivileges` WHERE `group_id`=?', [$group_id]);
$privilege_names = [];
- foreach ($_REQUEST['privileges'] as $priv) {
- if (preg_match("/^[0-9]{1,}$/", $priv)) {
- $group_privileges_source = sql_select("SELECT * FROM `Privileges` WHERE `id`='" . sql_escape($priv) . "' LIMIT 1");
- if (count($group_privileges_source) > 0) {
- sql_query("INSERT INTO `GroupPrivileges` SET `group_id`='" . sql_escape($group_id) . "', `privilege_id`='" . sql_escape($priv) . "'");
- $privilege_names[] = $group_privileges_source[0]['name'];
+ foreach ($_REQUEST['privileges'] as $privilege) {
+ if (preg_match("/^[0-9]{1,}$/", $privilege)) {
+ $group_privileges_source = DB::select(
+ 'SELECT `name` FROM `Privileges` WHERE `id`=? LIMIT 1',
+ [$privilege]
+ );
+ if (!empty($group_privileges_source)) {
+ $group_privileges_source = array_shift($group_privileges_source);
+ DB::insert(
+ 'INSERT INTO `GroupPrivileges` (`group_id`, `privilege_id`) VALUES (?, ?)',
+ [$group_id, $privilege]
+ );
+ $privilege_names[] = $group_privileges_source['name'];
}
}
}
diff --git a/includes/pages/admin_import.php b/includes/pages/admin_import.php
index 2e37572f..7a246b4b 100644
--- a/includes/pages/admin_import.php
+++ b/includes/pages/admin_import.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -252,10 +254,10 @@ function admin_import()
if ($result === false) {
engelsystem_error('Unable to create room.');
}
- $rooms_import[trim($room)] = sql_id();
+ $rooms_import[trim($room)] = $result;
}
foreach ($rooms_deleted as $room) {
- sql_query("DELETE FROM `Room` WHERE `Name`='" . sql_escape($room) . "' LIMIT 1");
+ DB::delete('DELETE FROM `Room` WHERE `Name`=? LIMIT 1', [$room]);
}
list($events_new, $events_updated, $events_deleted) = prepare_events(
@@ -317,11 +319,11 @@ function prepare_rooms($file)
$data = read_xml($file);
// Load rooms from db for compare with input
- $rooms = sql_select('SELECT * FROM `Room` WHERE `FromPentabarf`=\'Y\'');
+ $rooms = DB::select('SELECT `Name`, `RID` FROM `Room` WHERE `FromPentabarf`=\'Y\'');
$rooms_db = [];
$rooms_import = [];
foreach ($rooms as $room) {
- $rooms_db[] = (string)$room['Name'];
+ $rooms_db[] = $room['Name'];
$rooms_import[$room['Name']] = $room['RID'];
}
@@ -356,7 +358,7 @@ function prepare_events($file, $shifttype_id, $add_minutes_start, $add_minutes_e
global $rooms_import;
$data = read_xml($file);
- $rooms = sql_select('SELECT * FROM `Room`');
+ $rooms = Rooms(true);
$rooms_db = [];
foreach ($rooms as $room) {
$rooms_db[$room['Name']] = $room['RID'];
@@ -378,7 +380,7 @@ function prepare_events($file, $shifttype_id, $add_minutes_start, $add_minutes_e
];
}
- $shifts = sql_select('SELECT * FROM `Shifts` WHERE `PSID` IS NOT NULL ORDER BY `start`');
+ $shifts = DB::select('SELECT * FROM `Shifts` WHERE `PSID` IS NOT NULL ORDER BY `start`');
$shifts_db = [];
foreach ($shifts as $shift) {
$shifts_db[$shift['PSID']] = $shift;
diff --git a/includes/pages/admin_news.php b/includes/pages/admin_news.php
index 6d52bcf5..86631d6b 100644
--- a/includes/pages/admin_news.php
+++ b/includes/pages/admin_news.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -18,14 +20,14 @@ function admin_news()
return error('Incomplete call, missing News ID.', true);
}
- $news = sql_select("SELECT * FROM `News` WHERE `ID`='" . sql_escape($news_id) . "' LIMIT 1");
+ $news = DB::select('SELECT * FROM `News` WHERE `ID`=? LIMIT 1', [$news_id]);
if (empty($news)) {
return error('No News found.', true);
}
switch ($_REQUEST['action']) {
case 'edit':
- list($news) = $news;
+ $news = array_shift($news);
$user_source = User($news['UID']);
$html .= form([
@@ -43,21 +45,32 @@ function admin_news()
break;
case 'save':
- sql_query("UPDATE `News` SET
- `Datum`='" . sql_escape(time()) . "',
- `Betreff`='" . sql_escape($_POST["eBetreff"]) . "',
- `Text`='" . sql_escape($_POST["eText"]) . "',
- `UID`='" . sql_escape($user['UID']) . "',
- `Treffen`='" . sql_escape($_POST["eTreffen"]) . "'
- WHERE `ID`='" . sql_escape($news_id) . "'");
+ DB::update('
+ UPDATE `News` SET
+ `Datum`=?,
+ `Betreff`=?,
+ `Text`=?,
+ `UID`=?,
+ `Treffen`=?
+ WHERE `ID`=?
+ ',
+ [
+ time(),
+ $_POST["eBetreff"],
+ $_POST["eText"],
+ $user['UID'],
+ isset($_POST["eTreffen"]) ? 1 : 0,
+ $news_id
+ ]
+ );
engelsystem_log('News updated: ' . $_POST['eBetreff']);
success(_('News entry updated.'));
redirect(page_link_to('news'));
break;
case 'delete':
- list($news) = $news;
- sql_query("DELETE FROM `News` WHERE `ID`='" . sql_escape($news_id) . "' LIMIT 1");
+ $news = array_shift($news);
+ DB::delete('DELETE FROM `News` WHERE `ID`=? LIMIT 1', [$news_id]);
engelsystem_log('News deleted: ' . $news['Betreff']);
success(_('News entry deleted.'));
redirect(page_link_to('news'));
diff --git a/includes/pages/admin_questions.php b/includes/pages/admin_questions.php
index 7dcb3057..ef84b111 100644
--- a/includes/pages/admin_questions.php
+++ b/includes/pages/admin_questions.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -19,7 +21,7 @@ function admin_new_questions()
if ($page != 'admin_questions') {
if (in_array('admin_questions', $privileges)) {
- $new_messages = sql_num_query('SELECT * FROM `Questions` WHERE `AID` IS NULL');
+ $new_messages = count(DB::select('SELECT `QID` FROM `Questions` WHERE `AID` IS NULL'));
if ($new_messages > 0) {
return '<a href="' . page_link_to("admin_questions") . '">' . _('There are unanswered questions!') . '</a>';
@@ -39,7 +41,7 @@ function admin_questions()
if (!isset($_REQUEST['action'])) {
$unanswered_questions_table = [];
- $questions = sql_select("SELECT * FROM `Questions` WHERE `AID` IS NULL");
+ $questions = DB::select('SELECT * FROM `Questions` WHERE `AID` IS NULL');
foreach ($questions as $question) {
$user_source = User($question['UID']);
@@ -59,7 +61,7 @@ function admin_questions()
}
$answered_questions_table = [];
- $questions = sql_select("SELECT * FROM `Questions` WHERE NOT `AID` IS NULL");
+ $questions = DB::select('SELECT * FROM `Questions` WHERE NOT `AID` IS NULL');
foreach ($questions as $question) {
$user_source = User($question['UID']);
$answer_user_source = User($question['AID']);
@@ -102,7 +104,10 @@ function admin_questions()
return error('Incomplete call, missing Question ID.', true);
}
- $question = sql_select("SELECT * FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1");
+ $question = DB::select(
+ 'SELECT * FROM `Questions` WHERE `QID`=? LIMIT 1',
+ [$question_id]
+ );
if (count($question) > 0 && $question[0]['AID'] == null) {
$answer = trim(
preg_replace("/([^\p{L}\p{P}\p{Z}\p{N}\n]{1,})/ui",
@@ -111,12 +116,19 @@ function admin_questions()
));
if ($answer != '') {
- sql_query("
- UPDATE `Questions`
- SET `AID`='" . sql_escape($user['UID']) . "', `Answer`='" . sql_escape($answer) . "'
- WHERE `QID`='" . sql_escape($question_id) . "'
- LIMIT 1
- ");
+ DB::update(
+ '
+ UPDATE `Questions`
+ SET `AID`=?, `Answer`=?
+ WHERE `QID`=?
+ LIMIT 1
+ ',
+ [
+ $user['UID'],
+ $answer,
+ $question_id,
+ ]
+ );
engelsystem_log('Question ' . $question[0]['Question'] . ' answered: ' . $answer);
redirect(page_link_to('admin_questions'));
} else {
@@ -133,9 +145,12 @@ function admin_questions()
return error('Incomplete call, missing Question ID.', true);
}
- $question = sql_select("SELECT * FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1");
+ $question = DB::select(
+ 'SELECT * FROM `Questions` WHERE `QID`=? LIMIT 1',
+ [$question_id]
+ );
if (count($question) > 0) {
- sql_query("DELETE FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1");
+ DB::delete('DELETE FROM `Questions` WHERE `QID`=? LIMIT 1', [$question_id]);
engelsystem_log('Question deleted: ' . $question[0]['Question']);
redirect(page_link_to('admin_questions'));
} else {
diff --git a/includes/pages/admin_rooms.php b/includes/pages/admin_rooms.php
index 61923689..50be15f3 100644
--- a/includes/pages/admin_rooms.php
+++ b/includes/pages/admin_rooms.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -13,7 +15,7 @@ function admin_rooms_title()
*/
function admin_rooms()
{
- $rooms_source = sql_select('SELECT * FROM `Room` ORDER BY `Name`');
+ $rooms_source = DB::select('SELECT * FROM `Room` ORDER BY `Name`');
$rooms = [];
foreach ($rooms_source as $room) {
$rooms[] = [
@@ -36,7 +38,7 @@ function admin_rooms()
$number = '';
$room_id = 0;
- $angeltypes_source = sql_select('SELECT * FROM `AngelTypes` ORDER BY `name`');
+ $angeltypes_source = DB::select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`');
$angeltypes = [];
$angeltypes_count = [];
foreach ($angeltypes_source as $angeltype) {
@@ -59,7 +61,10 @@ function admin_rooms()
$public = $room['show'];
$number = $room['Number'];
- $needed_angeltypes = sql_select("SELECT * FROM `NeededAngelTypes` WHERE `room_id`='" . sql_escape($room_id) . "'");
+ $needed_angeltypes = DB::select(
+ 'SELECT `angel_type_id`, `count` FROM `NeededAngelTypes` WHERE `room_id`=?',
+ [$room_id]
+ );
foreach ($needed_angeltypes as $needed_angeltype) {
$angeltypes_count[$needed_angeltype['angel_type_id']] = $needed_angeltype['count'];
}
@@ -71,7 +76,13 @@ function admin_rooms()
if (isset($_REQUEST['name']) && strlen(strip_request_item('name')) > 0) {
$name = strip_request_item('name');
- if (isset($room) && sql_num_query("SELECT * FROM `Room` WHERE `Name`='" . sql_escape($name) . "' AND NOT `RID`=" . sql_escape($room_id)) > 0) {
+ if (
+ isset($room)
+ && count(DB::select(
+ 'SELECT RID FROM `Room` WHERE `Name`=? AND NOT `RID`=?',
+ [$name, $room_id]
+ )) > 0
+ ) {
$valid = false;
$msg .= error(_('This name is already in use.'), true);
}
@@ -111,17 +122,23 @@ function admin_rooms()
}
if ($valid) {
- if (isset($room_id)) {
- sql_query("
+ if (!empty($room_id)) {
+ DB::update('
UPDATE `Room`
SET
- `Name`='" . sql_escape($name) . "',
- `FromPentabarf`='" . sql_escape($from_pentabarf) . "',
- `show`='" . sql_escape($public) . "',
- `Number`='" . sql_escape($number) . "'
- WHERE `RID`='" . sql_escape($room_id) . "'
+ `Name`=?,
+ `FromPentabarf`=?,
+ `show`=?,
+ `Number`=?
+ WHERE `RID`=?
LIMIT 1
- ");
+ ', [
+ $name,
+ $from_pentabarf,
+ $public,
+ $number,
+ $room_id,
+ ]);
engelsystem_log(
'Room updated: ' . $name
. ', pentabarf import: ' . $from_pentabarf
diff --git a/includes/pages/admin_shifts.php b/includes/pages/admin_shifts.php
index 1e19c5e4..c543e827 100644
--- a/includes/pages/admin_shifts.php
+++ b/includes/pages/admin_shifts.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -27,14 +29,14 @@ function admin_shifts()
$shifttype_id = null;
// Locations laden (auch unsichtbare - fuer Erzengel ist das ok)
- $rooms = sql_select('SELECT * FROM `Room` ORDER BY `Name`');
+ $rooms = DB::select('SELECT `RID`, `Name` FROM `Room` ORDER BY `Name`');
$room_array = [];
foreach ($rooms as $room) {
$room_array[$room['RID']] = $room['Name'];
}
// Engeltypen laden
- $types = sql_select('SELECT * FROM `AngelTypes` ORDER BY `name`');
+ $types = DB::select('SELECT * FROM `AngelTypes` ORDER BY `name`');
$needed_angel_types = [];
foreach ($types as $type) {
$needed_angel_types[$type['id']] = 0;
@@ -53,9 +55,6 @@ function admin_shifts()
if (isset($_REQUEST['preview']) || isset($_REQUEST['back'])) {
if (isset($_REQUEST['shifttype_id'])) {
$shifttype = ShiftType($_REQUEST['shifttype_id']);
- if ($shifttype === false) {
- engelsystem_error('Unable to load shift type.');
- }
if ($shifttype == null) {
$valid = false;
error(_('Please select a shift type.'));
@@ -168,7 +167,13 @@ function admin_shifts()
if ($valid) {
if ($angelmode == 'location') {
$needed_angel_types = [];
- $needed_angel_types_location = sql_select("SELECT * FROM `NeededAngelTypes` WHERE `room_id`='" . sql_escape($rid) . "'");
+ $needed_angel_types_location = DB::select('
+ SELECT `angel_type_id`, `count`
+ FROM `NeededAngelTypes`
+ WHERE `room_id`=?
+ ',
+ [$rid]
+ );
foreach ($needed_angel_types_location as $type) {
$needed_angel_types[$type['angel_type_id']] = $type['count'];
}
@@ -300,7 +305,12 @@ function admin_shifts()
]);
}
} elseif (isset($_REQUEST['submit'])) {
- if (!is_array($_SESSION['admin_shifts_shifts']) || !is_array($_SESSION['admin_shifts_types'])) {
+ if (
+ !isset($_SESSION['admin_shifts_shifts'])
+ || !isset($_SESSION['admin_shifts_types'])
+ || !is_array($_SESSION['admin_shifts_shifts'])
+ || !is_array($_SESSION['admin_shifts_types'])
+ ) {
redirect(page_link_to('admin_shifts'));
}
@@ -321,10 +331,23 @@ function admin_shifts()
);
foreach ($_SESSION['admin_shifts_types'] as $type_id => $count) {
- $angel_type_source = sql_select("SELECT * FROM `AngelTypes` WHERE `id`='" . sql_escape($type_id) . "' LIMIT 1");
- if (count($angel_type_source) > 0) {
- sql_query("INSERT INTO `NeededAngelTypes` SET `shift_id`='" . sql_escape($shift_id) . "', `angel_type_id`='" . sql_escape($type_id) . "', `count`='" . sql_escape($count) . "'");
- $needed_angel_types_info[] = $angel_type_source[0]['name'] . ": " . $count;
+ $angel_type_source = DB::select('
+ SELECT *
+ FROM `AngelTypes`
+ WHERE `id` = ?
+ LIMIT 1', [$type_id]);
+ if (!empty($angel_type_source)) {
+ DB::insert('
+ INSERT INTO `NeededAngelTypes` (`shift_id`, `angel_type_id`, `count`)
+ VALUES (?, ?, ?)
+ ',
+ [
+ $shift_id,
+ $type_id,
+ $count
+ ]
+ );
+ $needed_angel_types_info[] = $angel_type_source[0]['name'] . ': ' . $count;
}
}
}
diff --git a/includes/pages/admin_user.php b/includes/pages/admin_user.php
index 8e11c5f3..192becb0 100644
--- a/includes/pages/admin_user.php
+++ b/includes/pages/admin_user.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -106,12 +108,18 @@ function admin_user()
$html .= '<hr />';
- $my_highest_group = sql_select("SELECT * FROM `UserGroups` WHERE `uid`='" . sql_escape($user['UID']) . "' ORDER BY `group_id` LIMIT 1");
+ $my_highest_group = DB::select(
+ 'SELECT group_id FROM `UserGroups` WHERE `uid`=? ORDER BY `group_id` LIMIT 1',
+ [$user['UID']]
+ );
if (count($my_highest_group) > 0) {
$my_highest_group = $my_highest_group[0]['group_id'];
}
- $his_highest_group = sql_select("SELECT * FROM `UserGroups` WHERE `uid`='" . sql_escape($user_id) . "' ORDER BY `group_id` LIMIT 1");
+ $his_highest_group = DB::select(
+ 'SELECT `group_id` FROM `UserGroups` WHERE `uid`=? ORDER BY `group_id` LIMIT 1',
+ [$user_id]
+ );
if (count($his_highest_group) > 0) {
$his_highest_group = $his_highest_group[0]['group_id'];
}
@@ -121,16 +129,21 @@ function admin_user()
. page_link_to('admin_user') . '&action=save_groups&id=' . $user_id . '" method="post">' . "\n";
$html .= '<table>';
- $groups = sql_select("
- SELECT *
- FROM `Groups`
- LEFT OUTER JOIN `UserGroups` ON (
- `UserGroups`.`group_id` = `Groups`.`UID`
- AND `UserGroups`.`uid` = '" . sql_escape($user_id) . "'
- )
- WHERE `Groups`.`UID` >= '" . sql_escape($my_highest_group) . "'
- ORDER BY `Groups`.`Name`
- ");
+ $groups = DB::select('
+ SELECT *
+ FROM `Groups`
+ LEFT OUTER JOIN `UserGroups` ON (
+ `UserGroups`.`group_id` = `Groups`.`UID`
+ AND `UserGroups`.`uid` = ?
+ )
+ WHERE `Groups`.`UID` >= ?
+ ORDER BY `Groups`.`Name`
+ ',
+ [
+ $user_id,
+ $my_highest_group,
+ ]
+ );
foreach ($groups as $group) {
$html .= '<tr><td><input type="checkbox" name="groups[]" value="' . $group['UID'] . '" '
. ($group['group_id'] != '' ? ' checked="checked"' : '')
@@ -154,20 +167,37 @@ function admin_user()
switch ($_REQUEST['action']) {
case 'save_groups':
if ($user_id != $user['UID']) {
- $my_highest_group = sql_select("SELECT * FROM `UserGroups` WHERE `uid`='" . sql_escape($user['UID']) . "' ORDER BY `group_id`");
- $his_highest_group = sql_select("SELECT * FROM `UserGroups` WHERE `uid`='" . sql_escape($user_id) . "' ORDER BY `group_id`");
-
- if (count($my_highest_group) > 0 && (count($his_highest_group) == 0 || ($my_highest_group[0]['group_id'] <= $his_highest_group[0]['group_id']))) {
- $groups_source = sql_select("
- SELECT *
- FROM `Groups`
- LEFT OUTER JOIN `UserGroups` ON (
- `UserGroups`.`group_id` = `Groups`.`UID`
- AND `UserGroups`.`uid` = '" . sql_escape($user_id) . "'
- )
- WHERE `Groups`.`UID` >= '" . sql_escape($my_highest_group[0]['group_id']) . "'
- ORDER BY `Groups`.`Name`
- ");
+ $my_highest_group = DB::select(
+ 'SELECT * FROM `UserGroups` WHERE `uid`=? ORDER BY `group_id`',
+ [$user['UID']]
+ );
+ $his_highest_group = DB::select(
+ 'SELECT * FROM `UserGroups` WHERE `uid`=? ORDER BY `group_id`',
+ [$user_id]
+ );
+
+ if (
+ count($my_highest_group) > 0
+ && (
+ count($his_highest_group) == 0
+ || ($my_highest_group[0]['group_id'] <= $his_highest_group[0]['group_id'])
+ )
+ ) {
+ $groups_source = DB::select('
+ SELECT *
+ FROM `Groups`
+ LEFT OUTER JOIN `UserGroups` ON (
+ `UserGroups`.`group_id` = `Groups`.`UID`
+ AND `UserGroups`.`uid` = ?
+ )
+ WHERE `Groups`.`UID` >= ?
+ ORDER BY `Groups`.`Name`
+ ',
+ [
+ $user_id,
+ $my_highest_group[0]['group_id'],
+ ]
+ );
$groups = [];
$grouplist = [];
foreach ($groups_source as $group) {
@@ -179,11 +209,14 @@ function admin_user()
$_REQUEST['groups'] = [];
}
- sql_query("DELETE FROM `UserGroups` WHERE `uid`='" . sql_escape($user_id) . "'");
+ DB::delete('DELETE FROM `UserGroups` WHERE `uid`=?', [$user_id]);
$user_groups_info = [];
foreach ($_REQUEST['groups'] as $group) {
if (in_array($group, $grouplist)) {
- sql_query("INSERT INTO `UserGroups` SET `uid`='" . sql_escape($user_id) . "', `group_id`='" . sql_escape($group) . "'");
+ DB::insert(
+ 'INSERT INTO `UserGroups` (`uid`, `group_id`) VALUES (?, ?)',
+ [$user_id, $group]
+ );
$user_groups_info[] = $groups[$group]['Name'];
}
}
@@ -206,25 +239,42 @@ function admin_user()
if (in_array('admin_active', $privileges)) {
$force_active = $_REQUEST['force_active'];
}
- $SQL = "UPDATE `User` SET
- `Nick` = '" . sql_escape($_POST["eNick"]) . "',
- `Name` = '" . sql_escape($_POST["eName"]) . "',
- `Vorname` = '" . sql_escape($_POST["eVorname"]) . "',
- `Telefon` = '" . sql_escape($_POST["eTelefon"]) . "',
- `Handy` = '" . sql_escape($_POST["eHandy"]) . "',
- `Alter` = '" . sql_escape($_POST["eAlter"]) . "',
- `DECT` = '" . sql_escape($_POST["eDECT"]) . "',
- " . ($user_source['email_by_human_allowed'] ? "`email` = '" . sql_escape($_POST["eemail"]) . "'," : "") . "
- `jabber` = '" . sql_escape($_POST["ejabber"]) . "',
- `Size` = '" . sql_escape($_POST["eSize"]) . "',
- `Gekommen`= '" . sql_escape($_POST["eGekommen"]) . "',
- `Aktiv`= '" . sql_escape($_POST["eAktiv"]) . "',
- `force_active`= " . sql_escape($force_active) . ",
- `Tshirt` = '" . sql_escape($_POST["eTshirt"]) . "',
- `Hometown` = '" . sql_escape($_POST["Hometown"]) . "'
- WHERE `UID` = '" . sql_escape($user_id) . "'
- LIMIT 1";
- sql_query($SQL);
+ $sql = '
+ UPDATE `User` SET
+ `Nick` = ?,
+ `Name` = ?,
+ `Vorname` = ?,
+ `Telefon` = ?,
+ `Handy` = ?,
+ `Alter` =?,
+ `DECT` = ?,
+ ' . ($user_source['email_by_human_allowed'] ? '`email` = ' . DB::getPdo()->quote($_POST["eemail"]) . ',' : '') . '
+ `jabber` = ?,
+ `Size` = ?,
+ `Gekommen`= ?,
+ `Aktiv`= ?,
+ `force_active`= ?,
+ `Tshirt` = ?,
+ `Hometown` = ?
+ WHERE `UID` = ?
+ LIMIT 1';
+ DB::update($sql, [
+ $_POST['eNick'],
+ $_POST['eName'],
+ $_POST['eVorname'],
+ $_POST['eTelefon'],
+ $_POST['eHandy'],
+ $_POST['eAlter'],
+ $_POST['eDECT'],
+ $_POST['ejabber'],
+ $_POST['eSize'],
+ $_POST['eGekommen'],
+ $_POST['eAktiv'],
+ $force_active,
+ $_POST['eTshirt'],
+ $_POST['Hometown'],
+ $user_id,
+ ]);
engelsystem_log(
'Updated user: ' . $_POST['eNick'] . ', ' . $_POST['eSize']
. ', arrived: ' . $_POST['eGekommen']
diff --git a/includes/pages/guest_login.php b/includes/pages/guest_login.php
index d202d92d..f08f9260 100644
--- a/includes/pages/guest_login.php
+++ b/includes/pages/guest_login.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -37,8 +39,8 @@ function guest_register()
$msg = '';
$nick = '';
- $lastname = '';
- $prename = '';
+ $lastName = '';
+ $preName = '';
$age = '';
$tel = '';
$dect = '';
@@ -68,7 +70,7 @@ function guest_register()
if (isset($_REQUEST['nick']) && strlen(User_validate_Nick($_REQUEST['nick'])) > 1) {
$nick = User_validate_Nick($_REQUEST['nick']);
- if (sql_num_query("SELECT * FROM `User` WHERE `Nick`='" . sql_escape($nick) . "' LIMIT 1") > 0) {
+ if (count(DB::select('SELECT `UID` FROM `User` WHERE `Nick`=? LIMIT 1', [$nick])) > 0) {
$valid = false;
$msg .= error(sprintf(_('Your nick &quot;%s&quot; already exists.'), $nick), true);
}
@@ -148,10 +150,10 @@ function guest_register()
// Trivia
if (isset($_REQUEST['lastname'])) {
- $lastname = strip_request_item('lastname');
+ $lastName = strip_request_item('lastname');
}
if (isset($_REQUEST['prename'])) {
- $prename = strip_request_item('prename');
+ $preName = strip_request_item('prename');
}
if (isset($_REQUEST['age']) && preg_match("/^[0-9]{0,4}$/", $_REQUEST['age'])) {
$age = strip_request_item('age');
@@ -173,38 +175,65 @@ function guest_register()
}
if ($valid) {
- sql_query("
- INSERT INTO `User` SET
- `color`='" . sql_escape($default_theme) . "',
- `Nick`='" . sql_escape($nick) . "',
- `Vorname`='" . sql_escape($prename) . "',
- `Name`='" . sql_escape($lastname) . "',
- `Alter`='" . sql_escape($age) . "',
- `Telefon`='" . sql_escape($tel) . "',
- `DECT`='" . sql_escape($dect) . "',
- `Handy`='" . sql_escape($mobile) . "',
- `email`='" . sql_escape($mail) . "',
- `email_shiftinfo`=" . sql_bool($email_shiftinfo) . ",
- `email_by_human_allowed`=" . sql_bool($email_by_human_allowed) . ",
- `jabber`='" . sql_escape($jabber) . "',
- `Size`='" . sql_escape($tshirt_size) . "',
- `Passwort`='" . sql_escape($password_hash) . "',
- `kommentar`='" . sql_escape($comment) . "',
- `Hometown`='" . sql_escape($hometown) . "',
- `CreateDate`=NOW(),
- `Sprache`='" . sql_escape($_SESSION["locale"]) . "',
- `arrival_date`=NULL,
- `planned_arrival_date`='" . sql_escape($planned_arrival_date) . "'");
+ DB::insert('
+ INSERT INTO `User` (
+ `color`,
+ `Nick`,
+ `Vorname`,
+ `Name`,
+ `Alter`,
+ `Telefon`,
+ `DECT`,
+ `Handy`,
+ `email`,
+ `email_shiftinfo`,
+ `email_by_human_allowed`,
+ `jabber`,
+ `Size`,
+ `Passwort`,
+ `kommentar`,
+ `Hometown`,
+ `CreateDate`,
+ `Sprache`,
+ `arrival_date`,
+ `planned_arrival_date`
+ )
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, NULL, ?)
+ ',
+ [
+ $default_theme,
+ $nick,
+ $preName,
+ $lastName,
+ $age,
+ $tel,
+ $dect,
+ $mobile,
+ $mail,
+ (bool)$email_shiftinfo,
+ (bool)$email_by_human_allowed,
+ $jabber,
+ $tshirt_size,
+ $password_hash,
+ $comment,
+ $hometown,
+ $_SESSION['locale'],
+ $planned_arrival_date,
+ ]
+ );
// Assign user-group and set password
- $user_id = sql_id();
- sql_query("INSERT INTO `UserGroups` SET `uid`='" . sql_escape($user_id) . "', `group_id`=-2");
+ $user_id = DB::getPdo()->lastInsertId();
+ DB::insert('INSERT INTO `UserGroups` (`uid`, `group_id`) VALUES (?, -2)', [$user_id]);
set_password($user_id, $_REQUEST['password']);
// Assign angel-types
$user_angel_types_info = [];
foreach ($selected_angel_types as $selected_angel_type_id) {
- sql_query("INSERT INTO `UserAngelTypes` SET `user_id`='" . sql_escape($user_id) . "', `angeltype_id`='" . sql_escape($selected_angel_type_id) . "'");
+ DB::insert(
+ 'INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`) VALUES (?, ?)',
+ [$user_id, $selected_angel_type_id]
+ );
$user_angel_types_info[] = $angel_types[$selected_angel_type_id];
}
@@ -316,10 +345,10 @@ function guest_register()
form_text('jabber', _('Jabber'), $jabber),
div('row', [
div('col-sm-6', [
- form_text('prename', _('First name'), $prename)
+ form_text('prename', _('First name'), $preName)
]),
div('col-sm-6', [
- form_text('lastname', _('Last name'), $lastname)
+ form_text('lastname', _('Last name'), $lastName)
])
]),
div('row', [
@@ -361,7 +390,7 @@ function guest_login()
if (isset($_REQUEST['submit'])) {
if (isset($_REQUEST['nick']) && strlen(User_validate_Nick($_REQUEST['nick'])) > 0) {
$nick = User_validate_Nick($_REQUEST['nick']);
- $login_user = sql_select("SELECT * FROM `User` WHERE `Nick`='" . sql_escape($nick) . "'");
+ $login_user = DB::select('SELECT * FROM `User` WHERE `Nick`=?', [$nick]);
if (count($login_user) > 0) {
$login_user = $login_user[0];
if (isset($_REQUEST['password'])) {
diff --git a/includes/pages/guest_stats.php b/includes/pages/guest_stats.php
index c4e1af74..4add3e97 100644
--- a/includes/pages/guest_stats.php
+++ b/includes/pages/guest_stats.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
function guest_stats()
{
global $api_key;
@@ -8,21 +10,23 @@ function guest_stats()
if ($_REQUEST['api_key'] == $api_key) {
$stats = [];
- list($user_count) = sql_select('SELECT count(*) AS `user_count` FROM `User`');
+ list($user_count) = DB::select('SELECT count(*) AS `user_count` FROM `User`');
$stats['user_count'] = $user_count['user_count'];
- list($arrived_user_count) = sql_select('SELECT count(*) AS `user_count` FROM `User` WHERE `Gekommen`=1');
+ list($arrived_user_count) = DB::select('SELECT count(*) AS `user_count` FROM `User` WHERE `Gekommen`=1');
$stats['arrived_user_count'] = $arrived_user_count['user_count'];
- $done_shifts_seconds = sql_select_single_cell('
+ $done_shifts_seconds = DB::select('
SELECT SUM(`Shifts`.`end` - `Shifts`.`start`)
FROM `ShiftEntry`
JOIN `Shifts` USING (`SID`)
WHERE `Shifts`.`end` < UNIX_TIMESTAMP()
');
+ $done_shifts_seconds = array_shift($done_shifts_seconds);
+ $done_shifts_seconds = (int)array_shift($done_shifts_seconds);
$stats['done_work_hours'] = round($done_shifts_seconds / (60 * 60), 0);
- $users_in_action = sql_select('
+ $users_in_action = DB::select('
SELECT `Shifts`.`start`, `Shifts`.`end`
FROM `ShiftEntry`
JOIN `Shifts` ON `Shifts`.`SID`=`ShiftEntry`.`SID`
diff --git a/includes/pages/user_atom.php b/includes/pages/user_atom.php
index 913a8821..04edf03f 100644
--- a/includes/pages/user_atom.php
+++ b/includes/pages/user_atom.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Publically available page to feed the news to feedreaders
*/
@@ -20,12 +22,12 @@ function user_atom()
engelsystem_error('No privilege for atom.');
}
- $news = sql_select("
+ $news = DB::select('
SELECT *
FROM `News`
- " . (empty($_REQUEST['meetings']) ? '' : 'WHERE `Treffen` = 1 ') . "
+ ' . (empty($_REQUEST['meetings']) ? '' : 'WHERE `Treffen` = 1 ') . '
ORDER BY `ID`
- DESC LIMIT " . (int)$display_news
+ DESC LIMIT ' . (int)$display_news
);
$output = make_atom_entries_from_news($news);
diff --git a/includes/pages/user_messages.php b/includes/pages/user_messages.php
index 00dbafe8..e4669411 100644
--- a/includes/pages/user_messages.php
+++ b/includes/pages/user_messages.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -16,7 +18,10 @@ function user_unread_messages()
global $user;
if (isset($user)) {
- $new_messages = sql_num_query("SELECT * FROM `Messages` WHERE isRead='N' AND `RUID`='" . sql_escape($user['UID']) . "'");
+ $new_messages = count(DB::select(
+ 'SELECT `id` FROM `Messages` WHERE isRead=\'N\' AND `RUID`=?',
+ [$user['UID']]
+ ));
if ($new_messages > 0) {
return ' <span class="badge danger">' . $new_messages . '</span>';
}
@@ -32,7 +37,10 @@ function user_messages()
global $user;
if (!isset($_REQUEST['action'])) {
- $users = sql_select("SELECT * FROM `User` WHERE NOT `UID`='" . sql_escape($user['UID']) . "' ORDER BY `Nick`");
+ $users = DB::select(
+ 'SELECT `UID`, `Nick` FROM `User` WHERE NOT `UID`=? ORDER BY `Nick`',
+ [$user['UID']]
+ );
$to_select_data = [
'' => _('Select recipient...')
@@ -44,13 +52,18 @@ function user_messages()
$to_select = html_select_key('to', 'to', $to_select_data, '');
- $messages = sql_select("
+ $messages = DB::select('
SELECT *
FROM `Messages`
- WHERE `SUID`='" . sql_escape($user['UID']) . "'
- OR `RUID`='" . sql_escape($user['UID']) . "'
+ WHERE `SUID`=?
+ OR `RUID`=?
ORDER BY `isRead`,`Datum` DESC
- ");
+ ',
+ [
+ $user['UID'],
+ $user['UID'],
+ ]
+ );
$messages_table = [
[
@@ -116,9 +129,15 @@ function user_messages()
return error(_('Incomplete call, missing Message ID.'), true);
}
- $message = sql_select("SELECT * FROM `Messages` WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1");
+ $message = DB::select(
+ 'SELECT `RUID` FROM `Messages` WHERE `id`=? LIMIT 1',
+ [$message_id]
+ );
if (count($message) > 0 && $message[0]['RUID'] == $user['UID']) {
- sql_query("UPDATE `Messages` SET `isRead`='Y' WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1");
+ DB::update(
+ 'UPDATE `Messages` SET `isRead`=\'Y\' WHERE `id`=? LIMIT 1',
+ [$message_id]
+ );
redirect(page_link_to('user_messages'));
} else {
return error(_('No Message found.'), true);
@@ -132,9 +151,12 @@ function user_messages()
return error(_('Incomplete call, missing Message ID.'), true);
}
- $message = sql_select("SELECT * FROM `Messages` WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1");
+ $message = DB::select(
+ 'SELECT `SUID` FROM `Messages` WHERE `id`=? LIMIT 1',
+ [$message_id]
+ );
if (count($message) > 0 && $message[0]['SUID'] == $user['UID']) {
- sql_query("DELETE FROM `Messages` WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1");
+ DB::delete('DELETE FROM `Messages` WHERE `id`=? LIMIT 1', [$message_id]);
redirect(page_link_to('user_messages'));
} else {
return error(_('No Message found.'), true);
@@ -142,7 +164,7 @@ function user_messages()
break;
case 'send':
- if (Message_send($_REQUEST['to'], $_REQUEST['text']) === true) {
+ if (Message_send($_REQUEST['to'], $_REQUEST['text'])) {
redirect(page_link_to('user_messages'));
} else {
return error(_('Transmitting was terminated with an Error.'), true);
diff --git a/includes/pages/user_myshifts.php b/includes/pages/user_myshifts.php
index 2079c789..76b79032 100644
--- a/includes/pages/user_myshifts.php
+++ b/includes/pages/user_myshifts.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -22,14 +24,15 @@ function user_myshifts()
isset($_REQUEST['id'])
&& in_array('user_shifts_admin', $privileges)
&& preg_match('/^[0-9]{1,}$/', $_REQUEST['id'])
- && sql_num_query("SELECT * FROM `User` WHERE `UID`='" . sql_escape($_REQUEST['id']) . "'") > 0
+ && count(DB::select('SELECT `UID` FROM `User` WHERE `UID`=?', [$_REQUEST['id']])) > 0
) {
$user_id = $_REQUEST['id'];
} else {
$user_id = $user['UID'];
}
- list($shifts_user) = sql_select("SELECT * FROM `User` WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1");
+ $shifts_user = DB::select('SELECT * FROM `User` WHERE `UID`=? LIMIT 1', [$user_id]);
+ $shifts_user = array_shift($shifts_user);
if (isset($_REQUEST['reset'])) {
if ($_REQUEST['reset'] == 'ack') {
@@ -46,24 +49,32 @@ function user_myshifts()
]);
} elseif (isset($_REQUEST['edit']) && preg_match('/^[0-9]*$/', $_REQUEST['edit'])) {
$user_id = $_REQUEST['edit'];
- $shift = sql_select("SELECT
- `ShiftEntry`.`freeloaded`,
- `ShiftEntry`.`freeload_comment`,
- `ShiftEntry`.`Comment`,
- `ShiftEntry`.`UID`,
- `ShiftTypes`.`name`,
- `Shifts`.*,
- `Room`.`Name`,
- `AngelTypes`.`name` AS `angel_type`
- FROM `ShiftEntry`
- JOIN `AngelTypes` ON (`ShiftEntry`.`TID` = `AngelTypes`.`id`)
- 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($user_id) . "'
- AND `UID`='" . sql_escape($shifts_user['UID']) . "' LIMIT 1");
+ $shift = DB::select('
+ SELECT
+ `ShiftEntry`.`freeloaded`,
+ `ShiftEntry`.`freeload_comment`,
+ `ShiftEntry`.`Comment`,
+ `ShiftEntry`.`UID`,
+ `ShiftTypes`.`name`,
+ `Shifts`.*,
+ `Room`.`Name`,
+ `AngelTypes`.`name` AS `angel_type`
+ FROM `ShiftEntry`
+ JOIN `AngelTypes` ON (`ShiftEntry`.`TID` = `AngelTypes`.`id`)
+ 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`=?
+ AND `UID`=?
+ LIMIT 1
+ ',
+ [
+ $user_id,
+ $shifts_user['UID'],
+ ]
+ );
if (count($shift) > 0) {
- $shift = $shift[0];
+ $shift = array_shift($shift);
$freeloaded = $shift['freeloaded'];
$freeload_comment = $shift['freeload_comment'];
@@ -120,13 +131,19 @@ function user_myshifts()
}
} elseif (isset($_REQUEST['cancel']) && preg_match('/^[0-9]*$/', $_REQUEST['cancel'])) {
$user_id = $_REQUEST['cancel'];
- $shift = sql_select("
- SELECT *
- FROM `Shifts`
- INNER JOIN `ShiftEntry` USING (`SID`)
- WHERE `ShiftEntry`.`id`='" . sql_escape($user_id) . "' AND `UID`='" . sql_escape($shifts_user['UID']) . "'");
+ $shift = DB::select('
+ SELECT *
+ FROM `Shifts`
+ INNER JOIN `ShiftEntry` USING (`SID`)
+ WHERE `ShiftEntry`.`id`=? AND `UID`=?
+ ',
+ [
+ $user_id,
+ $shifts_user['UID'],
+ ]
+ );
if (count($shift) > 0) {
- $shift = $shift[0];
+ $shift = array_shift($shift);
if (($shift['start'] > time() + $last_unsubscribe * 3600) || in_array('user_shifts_admin', $privileges)) {
$result = ShiftEntry_delete($user_id);
if ($result === false) {
diff --git a/includes/pages/user_news.php b/includes/pages/user_news.php
index ceed75f2..3828e293 100644
--- a/includes/pages/user_news.php
+++ b/includes/pages/user_news.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -39,18 +41,20 @@ function user_meetings()
$page = 0;
}
- $news = sql_select("
+ $news = DB::select(sprintf('
SELECT *
FROM `News`
WHERE `Treffen`=1
ORDER BY `Datum`DESC
- LIMIT " . sql_escape($page * $display_news) . ", " . sql_escape($display_news)
- );
+ LIMIT %u, %u',
+ $page * $display_news,
+ $display_news
+ ));
foreach ($news as $entry) {
$html .= display_news($entry);
}
- $dis_rows = ceil(sql_num_query('SELECT * FROM `News`') / $display_news);
+ $dis_rows = ceil(count(DB::select('SELECT `ID` FROM `News`')) / $display_news);
$html .= '<div class="text-center">' . '<ul class="pagination">';
for ($i = 0; $i < $dis_rows; $i++) {
if (isset($_REQUEST['page']) && $i == $_REQUEST['page']) {
@@ -98,7 +102,7 @@ function display_news($news)
. '<span class="glyphicon glyphicon-comment"></span> '
. _('Comments') . ' &raquo;</a> '
. '<span class="badge">'
- . sql_num_query("SELECT * FROM `NewsComments` WHERE `Refid`='" . sql_escape($news['ID']) . "'")
+ . count(DB::select('SELECT `ID` FROM `NewsComments` WHERE `Refid`=?', [$news['ID']]))
. '</span>';
}
$html .= '</div>';
@@ -117,28 +121,34 @@ function user_news_comments()
if (
isset($_REQUEST['nid'])
&& preg_match('/^[0-9]{1,}$/', $_REQUEST['nid'])
- && sql_num_query("SELECT * FROM `News` WHERE `ID`='" . sql_escape($_REQUEST['nid']) . "' LIMIT 1") > 0
+ && count(DB::select('SELECT `ID` FROM `News` WHERE `ID`=? LIMIT 1', [$_REQUEST['nid']])) > 0
) {
$nid = $_REQUEST['nid'];
- list($news) = sql_select("SELECT * FROM `News` WHERE `ID`='" . sql_escape($nid) . "' LIMIT 1");
+ $news = DB::select('SELECT * FROM `News` WHERE `ID`=? LIMIT 1', [$nid]);
+ $news = array_shift($news);
if (isset($_REQUEST['text'])) {
$text = preg_replace("/([^\p{L}\p{P}\p{Z}\p{N}\n]{1,})/ui", '', strip_tags($_REQUEST['text']));
- sql_query("
- INSERT INTO `NewsComments` (`Refid`, `Datum`, `Text`, `UID`)
- VALUES (
- '" . sql_escape($nid) . "',
- '" . date("Y-m-d H:i:s") . "',
- '" . sql_escape($text) . "',
- '" . sql_escape($user["UID"]) . "'
- )
- ");
+ DB::insert('
+ INSERT INTO `NewsComments` (`Refid`, `Datum`, `Text`, `UID`)
+ VALUES (?, ?, ?, ?)
+ ',
+ [
+ $nid,
+ date("Y-m-d H:i:s"),
+ $text,
+ $user["UID"],
+ ]
+ );
engelsystem_log('Created news_comment: ' . $text);
$html .= success(_('Entry saved.'), true);
}
$html .= display_news($news);
- $comments = sql_select("SELECT * FROM `NewsComments` WHERE `Refid`='" . sql_escape($nid) . "' ORDER BY 'ID'");
+ $comments = DB::select(
+ 'SELECT * FROM `NewsComments` WHERE `Refid`=? ORDER BY \'ID\'',
+ [$nid]
+ );
foreach ($comments as $comment) {
$user_source = User($comment['UID']);
@@ -176,16 +186,18 @@ function user_news()
if (!isset($_POST['treffen']) || !in_array('admin_news', $privileges)) {
$_POST['treffen'] = 0;
}
- sql_query("
+ DB::insert('
INSERT INTO `News` (`Datum`, `Betreff`, `Text`, `UID`, `Treffen`)
- VALUES (
- '" . sql_escape(time()) . "',
- '" . sql_escape($_POST["betreff"]) . "',
- '" . sql_escape($_POST["text"]) . "',
- '" . sql_escape($user['UID']) . "',
- '" . sql_escape($_POST["treffen"]) . "'
- )
- ");
+ VALUES (?, ?, ?, ?, ?)
+ ',
+ [
+ time(),
+ $_POST['betreff'],
+ $_POST['text'],
+ $user['UID'],
+ $_POST['treffen'],
+ ]
+ );
engelsystem_log('Created news: ' . $_POST['betreff'] . ', treffen: ' . $_POST['treffen']);
success(_('Entry saved.'));
redirect(page_link_to('news'));
@@ -197,17 +209,20 @@ function user_news()
$page = 0;
}
- $news = sql_select("
- SELECT *
- FROM `News`
- ORDER BY `Datum`
- DESC LIMIT " . sql_escape($page * $display_news) . ", " . sql_escape($display_news)
- );
+ $news = DB::select(sprintf('
+ SELECT *
+ FROM `News`
+ ORDER BY `Datum`
+ DESC LIMIT %u, %u
+ ',
+ $page * $display_news,
+ $display_news
+ ));
foreach ($news as $entry) {
$html .= display_news($entry);
}
- $dis_rows = ceil(sql_num_query('SELECT * FROM `News`') / $display_news);
+ $dis_rows = ceil(count(DB::select('SELECT `ID` FROM `News`')) / $display_news);
$html .= '<div class="text-center">' . '<ul class="pagination">';
for ($i = 0; $i < $dis_rows; $i++) {
if (isset($_REQUEST['page']) && $i == $_REQUEST['page']) {
diff --git a/includes/pages/user_questions.php b/includes/pages/user_questions.php
index b8ebe92d..04ae8914 100644
--- a/includes/pages/user_questions.php
+++ b/includes/pages/user_questions.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -16,12 +18,14 @@ function user_questions()
global $user;
if (!isset($_REQUEST['action'])) {
- $open_questions = sql_select(
- "SELECT * FROM `Questions` WHERE `AID` IS NULL AND `UID`='" . sql_escape($user['UID']) . "'"
+ $open_questions = DB::select(
+ 'SELECT * FROM `Questions` WHERE `AID` IS NULL AND `UID`=?',
+ [$user['UID']]
);
- $answered_questions = sql_select(
- "SELECT * FROM `Questions` WHERE NOT `AID` IS NULL AND `UID`='" . sql_escape($user['UID']) . "'"
+ $answered_questions = DB::select(
+ 'SELECT * FROM `Questions` WHERE NOT `AID` IS NULL AND `UID`=?',
+ [$user['UID']]
);
foreach ($answered_questions as &$question) {
$answer_user_source = User($question['AID']);
@@ -34,11 +38,13 @@ function user_questions()
case 'ask':
$question = strip_request_item_nl('question');
if ($question != '') {
- $result = sql_query("
- INSERT INTO `Questions`
- SET `UID`='" . sql_escape($user['UID']) . "', `Question`='" . sql_escape($question) . "'
- ");
- if ($result === false) {
+ $result = DB::insert('
+ INSERT INTO `Questions` (`UID`, `Question`)
+ VALUES (?, ?)
+ ',
+ [$user['UID'], $question]
+ );
+ if (!$result) {
engelsystem_error(_('Unable to save question.'));
}
success(_('You question was saved.'));
@@ -56,9 +62,15 @@ function user_questions()
return error(_('Incomplete call, missing Question ID.'), true);
}
- $question = sql_select("SELECT * FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1");
+ $question = DB::select(
+ 'SELECT `UID` FROM `Questions` WHERE `QID`=? LIMIT 1',
+ [$question_id]
+ );
if (count($question) > 0 && $question[0]['UID'] == $user['UID']) {
- sql_query("DELETE FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1");
+ DB::delete(
+ 'DELETE FROM `Questions` WHERE `QID`=? LIMIT 1',
+ [$question_id]
+ );
redirect(page_link_to('user_questions'));
} else {
return page_with_title(questions_title(), [
diff --git a/includes/pages/user_settings.php b/includes/pages/user_settings.php
index b848ff5f..5d4ba368 100644
--- a/includes/pages/user_settings.php
+++ b/includes/pages/user_settings.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* @return string
*/
@@ -131,11 +133,16 @@ function user_settings_theme($user_source, $themes)
}
if ($valid) {
- sql_query("
+ DB::update('
UPDATE `User`
- SET `color`='" . sql_escape($user_source['color']) . "'
- WHERE `UID`='" . sql_escape($user_source['UID']) . "'
- ");
+ SET `color`=?
+ WHERE `UID`=?
+ ',
+ [
+ $user_source['color'],
+ $user_source['UID'],
+ ]
+ );
success(_('Theme changed.'));
redirect(page_link_to('user_settings'));
@@ -162,11 +169,16 @@ function user_settings_locale($user_source, $locales)
}
if ($valid) {
- sql_query("
+ DB::update('
UPDATE `User`
- SET `Sprache`='" . sql_escape($user_source['Sprache']) . "'
- WHERE `UID`='" . sql_escape($user_source['UID']) . "'
- ");
+ SET `Sprache`=?
+ WHERE `UID`=?
+ ',
+ [
+ $user_source['Sprache'],
+ $user_source['UID'],
+ ]
+ );
$_SESSION['locale'] = $user_source['Sprache'];
success('Language changed.');
diff --git a/includes/pages/user_shifts.php b/includes/pages/user_shifts.php
index bfb33f12..16af0197 100644
--- a/includes/pages/user_shifts.php
+++ b/includes/pages/user_shifts.php
@@ -1,4 +1,6 @@
<?php
+
+use Engelsystem\Database\DB;
use Engelsystem\ShiftsFilter;
/**
@@ -70,12 +72,9 @@ function update_ShiftsFilter_timerange(ShiftsFilter $shiftsFilter, $days)
/**
* Update given ShiftsFilter with filter params from user input
*
- * @param ShiftsFilter $shiftsFilter
- * The shifts filter to update from request data
- * @param boolean $user_shifts_admin
- * Has the user user_shift_admin privilege?
- * @param string[] $days
- * An array of available filter days
+ * @param ShiftsFilter $shiftsFilter The shifts filter to update from request data
+ * @param boolean $user_shifts_admin Has the user user_shift_admin privilege?
+ * @param string[] $days An array of available filter days
*/
function update_ShiftsFilter(ShiftsFilter $shiftsFilter, $user_shifts_admin, $days)
{
@@ -91,8 +90,10 @@ function update_ShiftsFilter(ShiftsFilter $shiftsFilter, $user_shifts_admin, $da
*/
function load_rooms()
{
- $rooms = sql_select('SELECT `RID` AS `id`, `Name` AS `name` FROM `Room` WHERE `show`=\'Y\' ORDER BY `Name`');
- if (!$rooms || count($rooms) == 0) {
+ $rooms = DB::select(
+ 'SELECT `RID` AS `id`, `Name` AS `name` FROM `Room` WHERE `show`=\'Y\' ORDER BY `Name`'
+ );
+ if (empty($rooms)) {
error(_('The administration has not configured any rooms yet.'));
redirect('?');
}
@@ -104,12 +105,14 @@ function load_rooms()
*/
function load_days()
{
- $days = sql_select_single_col('
+ $days = DB::select('
SELECT DISTINCT DATE(FROM_UNIXTIME(`start`)) AS `id`, DATE(FROM_UNIXTIME(`start`)) AS `name`
FROM `Shifts`
ORDER BY `start`
');
- if (count($days) == 0) {
+ $days = array_map('array_shift', $days);
+
+ if (empty($days)) {
error(_('The administration has not configured any shifts yet.'));
redirect('?');
}
@@ -123,31 +126,35 @@ function load_types()
{
global $user;
- if (sql_num_query('SELECT `id`, `name` FROM `AngelTypes` WHERE `restricted` = 0') == 0) {
+ if (!count(DB::select('SELECT `id`, `name` FROM `AngelTypes` WHERE `restricted` = 0'))) {
error(_('The administration has not configured any angeltypes yet - or you are not subscribed to any angeltype.'));
redirect('?');
}
- $types = sql_select("
- SELECT
- `AngelTypes`.`id`,
- `AngelTypes`.`name`,
- (
- `AngelTypes`.`restricted`=0
- OR (
- NOT `UserAngelTypes`.`confirm_user_id` IS NULL
- OR `UserAngelTypes`.`id` IS NULL
+ $types = DB::select('
+ SELECT
+ `AngelTypes`.`id`,
+ `AngelTypes`.`name`,
+ (
+ `AngelTypes`.`restricted`=0
+ OR (
+ NOT `UserAngelTypes`.`confirm_user_id` IS NULL
+ OR `UserAngelTypes`.`id` IS NULL
+ )
+ ) AS `enabled`
+ FROM `AngelTypes`
+ LEFT JOIN `UserAngelTypes`
+ ON (
+ `UserAngelTypes`.`angeltype_id`=`AngelTypes`.`id`
+ AND `UserAngelTypes`.`user_id`=?
)
- ) AS `enabled`
- FROM `AngelTypes`
- LEFT JOIN `UserAngelTypes`
- ON (
- `UserAngelTypes`.`angeltype_id`=`AngelTypes`.`id`
- AND `UserAngelTypes`.`user_id`='" . sql_escape($user['UID']) . "'
- )
- ORDER BY `AngelTypes`.`name`
- ");
+ ORDER BY `AngelTypes`.`name`
+ ',
+ [
+ $user['UID'],
+ ]
+ );
if (empty($types)) {
- return sql_select('SELECT `id`, `name` FROM `AngelTypes` WHERE `restricted` = 0');
+ return DB::select('SELECT `id`, `name` FROM `AngelTypes` WHERE `restricted` = 0');
}
return $types;
}
diff --git a/includes/sys_auth.php b/includes/sys_auth.php
index a478226c..083c1b8d 100644
--- a/includes/sys_auth.php
+++ b/includes/sys_auth.php
@@ -1,5 +1,7 @@
<?php
+use Engelsystem\Database\DB;
+
/**
* Testet ob ein User eingeloggt ist und lädt die entsprechenden Privilegien
*/
@@ -9,16 +11,19 @@ function load_auth()
$user = null;
if (isset($_SESSION['uid'])) {
- $user = sql_select("SELECT * FROM `User` WHERE `UID`='" . sql_escape($_SESSION['uid']) . "' LIMIT 1");
+ $user = DB::select('SELECT * FROM `User` WHERE `UID`=? LIMIT 1', [$_SESSION['uid']]);
if (count($user) > 0) {
// User ist eingeloggt, Datensatz zur Verfügung stellen und Timestamp updaten
- list($user) = $user;
- sql_query("
+ $user = array_shift($user);
+ DB::update('
UPDATE `User`
- SET " . "`lastLogIn` = '" . time() . "'" . "
- WHERE `UID` = '" . sql_escape($_SESSION['uid']) . "'
+ SET `lastLogIn` = ?
+ WHERE `UID` = ?
LIMIT 1
- ");
+ ', [
+ time(),
+ $_SESSION['uid'],
+ ]);
$privileges = privileges_for_user($user['UID']);
return;
}
@@ -50,19 +55,24 @@ function generate_salt($length = 16)
*
* @param int $uid
* @param string $password
- * @return mysqli_result
+ * @return bool
*/
function set_password($uid, $password)
{
global $crypt_alg;
- $result = sql_query("
+ $result = DB::update('
UPDATE `User`
- SET `Passwort` = '" . sql_escape(crypt($password, $crypt_alg . '$' . generate_salt(16) . '$')) . "',
+ SET `Passwort` = ?,
`password_recovery_token`=NULL
- WHERE `UID` = " . intval($uid) . "
+ WHERE `UID` = ?
LIMIT 1
- ");
- if ($result === false) {
+ ',
+ [
+ crypt($password, $crypt_alg . '$' . generate_salt(16) . '$'),
+ $uid
+ ]
+ );
+ if (DB::getStm()->errorCode() != '00000') {
engelsystem_error('Unable to update password.');
}
return $result;
@@ -93,13 +103,19 @@ function verify_password($password, $salt, $uid = null)
// this password is stored in another format than we want it to be.
// let's update it!
// we duplicate the query from the above set_password() function to have the extra safety of checking the old hash
- sql_query("
- UPDATE `User`
- SET `Passwort` = '" . sql_escape(crypt($password, $crypt_alg . '$' . generate_salt() . '$')) . "'
- WHERE `UID` = " . intval($uid) . "
- AND `Passwort` = '" . sql_escape($salt) . "'
- LIMIT 1
- ");
+ DB::update('
+ UPDATE `User`
+ SET `Passwort` = ?
+ WHERE `UID` = ?
+ AND `Passwort` = ?
+ LIMIT 1
+ ',
+ [
+ crypt($password, $crypt_alg . '$' . generate_salt() . '$'),
+ $uid,
+ $salt,
+ ]
+ );
}
return $correct;
}
@@ -111,16 +127,16 @@ function verify_password($password, $salt, $uid = null)
function privileges_for_user($user_id)
{
$privileges = [];
- $user_privs = sql_select("
+ $user_privileges = DB::select('
SELECT `Privileges`.`name`
FROM `User`
JOIN `UserGroups` ON (`User`.`UID` = `UserGroups`.`uid`)
JOIN `GroupPrivileges` ON (`UserGroups`.`group_id` = `GroupPrivileges`.`group_id`)
JOIN `Privileges` ON (`GroupPrivileges`.`privilege_id` = `Privileges`.`id`)
- WHERE `User`.`UID`='" . sql_escape($user_id) . "'
- ");
- foreach ($user_privs as $user_priv) {
- $privileges[] = $user_priv['name'];
+ WHERE `User`.`UID`=?
+ ', [$user_id]);
+ foreach ($user_privileges as $user_privilege) {
+ $privileges[] = $user_privilege['name'];
}
return $privileges;
}
@@ -132,14 +148,14 @@ function privileges_for_user($user_id)
function privileges_for_group($group_id)
{
$privileges = [];
- $groups_privs = sql_select("
- SELECT *
+ $groups_privileges = DB::select('
+ SELECT `name`
FROM `GroupPrivileges`
JOIN `Privileges` ON (`GroupPrivileges`.`privilege_id` = `Privileges`.`id`)
- WHERE `group_id`='" . sql_escape($group_id) . "'
- ");
- foreach ($groups_privs as $guest_priv) {
- $privileges[] = $guest_priv['name'];
+ WHERE `group_id`=?
+ ', [$group_id]);
+ foreach ($groups_privileges as $guest_privilege) {
+ $privileges[] = $guest_privilege['name'];
}
return $privileges;
}
diff --git a/includes/sys_log.php b/includes/sys_log.php
index b253d6ad..c4ef890e 100644
--- a/includes/sys_log.php
+++ b/includes/sys_log.php
@@ -16,25 +16,3 @@ function engelsystem_log($message)
}
LogEntry_create($nick, $message);
}
-
-/**
- * Generates a PHP Stacktrace.
- *
- * @return string
- */
-function debug_string_backtrace()
-{
- ob_start();
- debug_print_backtrace();
- $trace = ob_get_contents();
- ob_end_clean();
-
- // Remove first item from backtrace as it's this function which
- // is redundant.
- $trace = preg_replace('/^#0\s+' . __FUNCTION__ . "[^\n]*\n/", '', $trace, 1);
-
- // Renumber backtrace items.
- // $trace = preg_replace('/^#(\d+)/me', '\'#\' . ($1 - 1)', $trace);
-
- return $trace;
-}
diff --git a/includes/sys_page.php b/includes/sys_page.php
index 7bc2b9cb..fd03e291 100644
--- a/includes/sys_page.php
+++ b/includes/sys_page.php
@@ -61,7 +61,7 @@ function redirect($url)
*
* @param String $output String to display
*/
-function raw_output($output)
+function raw_output($output = '')
{
echo $output;
die();
@@ -78,11 +78,11 @@ function raw_output($output)
*/
function select_array($data, $key_name, $value_name)
{
- $ret = [];
+ $return = [];
foreach ($data as $value) {
- $ret[$value[$key_name]] = $value[$value_name];
+ $return[$value[$key_name]] = $value[$value_name];
}
- return $ret;
+ return $return;
}
/**
diff --git a/includes/view/User_view.php b/includes/view/User_view.php
index ca32b80e..832569a6 100644
--- a/includes/view/User_view.php
+++ b/includes/view/User_view.php
@@ -282,26 +282,25 @@ function Users_table_header_link($column, $label, $order_by)
function User_shift_state_render($user)
{
$upcoming_shifts = ShiftEntries_upcoming_for_user($user);
- if ($upcoming_shifts === false) {
- return false;
- }
- if (count($upcoming_shifts) == 0) {
+ if (empty($upcoming_shifts)) {
return '<span class="text-success">' . _('Free') . '</span>';
}
- if ($upcoming_shifts[0]['start'] > time()) {
- if ($upcoming_shifts[0]['start'] - time() > 3600) {
- return '<span class="text-success moment-countdown" data-timestamp="' . $upcoming_shifts[0]['start'] . '">' . _('Next shift %c') . '</span>';
+ $nextShift = array_shift($upcoming_shifts);
+
+ if ($nextShift['start'] > time()) {
+ if ($nextShift['start'] - time() > 3600) {
+ return '<span class="text-success moment-countdown" data-timestamp="' . $nextShift['start'] . '">' . _('Next shift %c') . '</span>';
}
- return '<span class="text-warning moment-countdown" data-timestamp="' . $upcoming_shifts[0]['start'] . '">' . _('Next shift %c') . '</span>';
+ return '<span class="text-warning moment-countdown" data-timestamp="' . $nextShift['start'] . '">' . _('Next shift %c') . '</span>';
}
- $halfway = ($upcoming_shifts[0]['start'] + $upcoming_shifts[0]['end']) / 2;
+ $halfway = ($nextShift['start'] + $nextShift['end']) / 2;
if (time() < $halfway) {
- return '<span class="text-danger moment-countdown" data-timestamp="' . $upcoming_shifts[0]['start'] . '">' . _('Shift starts %c') . '</span>';
+ return '<span class="text-danger moment-countdown" data-timestamp="' . $nextShift['start'] . '">' . _('Shift starts %c') . '</span>';
}
- return '<span class="text-danger moment-countdown" data-timestamp="' . $upcoming_shifts[0]['end'] . '">' . _('Shift ends %c') . '</span>';
+ return '<span class="text-danger moment-countdown" data-timestamp="' . $nextShift['end'] . '">' . _('Shift ends %c') . '</span>';
}
/**