diff options
| -rw-r--r-- | includes/model/ShiftEntry_model.php | 23 | ||||
| -rw-r--r-- | includes/model/Shifts_model.php | 63 | ||||
| -rw-r--r-- | includes/mysqli_provider.php | 43 | ||||
| -rw-r--r-- | includes/pages/admin_import.php | 24 | ||||
| -rw-r--r-- | includes/pages/admin_shifts.php | 10 | ||||
| -rw-r--r-- | includes/pages/admin_user.php | 1 | ||||
| -rw-r--r-- | includes/pages/user_myshifts.php | 24 | ||||
| -rw-r--r-- | includes/pages/user_shifts.php | 19 | ||||
| -rw-r--r-- | public/index.php | 8 |
9 files changed, 166 insertions, 49 deletions
diff --git a/includes/model/ShiftEntry_model.php b/includes/model/ShiftEntry_model.php index e3046152..0cf5c040 100644 --- a/includes/model/ShiftEntry_model.php +++ b/includes/model/ShiftEntry_model.php @@ -7,9 +7,12 @@ function ShiftEntries_freeleaded_count() { return sql_select_single_cell("SELECT COUNT(*) FROM `ShiftEntry` WHERE `freeloaded` = 1"); } +/** + * List users subsribed to a given shift. + */ function ShiftEntries_by_shift($shift_id) { return sql_select(" - SELECT `User`.`email`, `User`.`email_shiftinfo`, `User`.`Nick`, `User`.`Sprache`, `ShiftEntry`.`UID`, `ShiftEntry`.`TID`, `ShiftEntry`.`SID`, `AngelTypes`.`name` as `angel_type_name`, `ShiftEntry`.`Comment`, `ShiftEntry`.`freeloaded` + SELECT `User`.`Nick`, `User`.`email`, `User`.`email_shiftinfo`, `User`.`Sprache`, `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` @@ -32,6 +35,24 @@ function ShiftEntry_create($shift_entry) { } /** + * Update a shift entry. + */ +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_escape($shift_entry['freeloaded'] ? 'TRUE' : 'FALSE') . " + WHERE `id`=" . sql_escape($shift_entry['id'])); +} + +/** + * Delete a shift entry. + */ +function ShiftEntry_delete($shift_entry_id) { + return sql_query("DELETE FROM `ShiftEntry` WHERE `id`=" . sql_escape($shift_entry_id)); +} + +/** * Returns next (or current) shifts of given user. * * @param User $user diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index 5d0ec4a2..28f84c26 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -1,4 +1,67 @@ <?php + +/** + * Delete a shift by its external id. + */ +function Shift_delete_by_psid($shift_psid) { + return sql_query("DELETE FROM `Shifts` WHERE `PSID`=" . sql_escape($shift_psid)); +} + +/** + * Delete a shift. + */ +function Shift_delete($shift_id) { + return sql_query("DELETE FROM `Shifts` WHERE `SID`=" . sql_escape($shift_id)); +} + +/** + * Update a shift. + */ +function Shift_update($shift) { + return sql_query("UPDATE `Shifts` SET + `start`=" . sql_escape($shift['start']) . ", + `end`=" . sql_escape($shift['end']) . ", + `RID`=" . sql_escape($shift['RID']) . ", + `name`=" . sql_null($shift['name']) . ", + `URL`=" . sql_null($shift['URL']) . ", + `PSID`=" . sql_null($shift['PSID']) . " + WHERE `SID`=" . sql_escape($shift['SID'])); +} + +/** + * Update a shift by its external id. + */ +function Shift_update_by_psid($shift) { + return sql_query("UPDATE `Shifts` SET + `start`=" . sql_escape($shift['start']) . ", + `end`=" . sql_escape($shift['end']) . ", + `RID`=" . sql_escape($shift['RID']) . ", + `name`=" . sql_null($shift['name']) . ", + `URL`=" . sql_null($shift['URL']) . " + WHERE `PSID`=" . sql_escape($shift['PSID'])); +} + +/** + * Create a new shift. + * + * @return new shift id or false + */ +function Shift_create($shift) { + $result = sql_query("INSERT INTO `Shifts` SET + `start`=" . sql_escape($shift['start']) . ", + `end`=" . sql_escape($shift['end']) . ", + `RID`=" . sql_escape($shift['RID']) . ", + `name`=" . sql_null($shift['name']) . ", + `URL`=" . sql_null($shift['URL']) . ", + `PSID`=" . sql_null($shift['PSID'])); + if ($result === false) + return false; + return sql_id(); +} + +/** + * Return users shifts. + */ function Shifts_by_user($user) { return sql_select(" SELECT * diff --git a/includes/mysqli_provider.php b/includes/mysqli_provider.php index 9f901a40..9f4f1396 100644 --- a/includes/mysqli_provider.php +++ b/includes/mysqli_provider.php @@ -5,16 +5,23 @@ */ function sql_close() { global $sql_connection; - + return $sql_connection->close(); } /** + * Return NULL if given value is null. + */ +function sql_null($value = null) { + return $value == null ? 'NULL' : ("'" . sql_escape($value) . "'"); +} + +/** * Start new transaction. */ function sql_transaction_start() { global $sql_nested_transaction_level; - + if ($sql_nested_transaction_level ++ == 0) return sql_query("BEGIN"); else @@ -26,7 +33,7 @@ function sql_transaction_start() { */ function sql_transaction_commit() { global $sql_nested_transaction_level; - + if (-- $sql_nested_transaction_level == 0) return sql_query("COMMIT"); else @@ -38,7 +45,7 @@ function sql_transaction_commit() { */ function sql_transaction_rollback() { global $sql_nested_transaction_level; - + if (-- $sql_nested_transaction_level == 0) return sql_query("ROLLBACK"); else @@ -48,17 +55,17 @@ function sql_transaction_rollback() { /** * Logs an sql error. * - * @param string $message + * @param string $message * @return false */ function sql_error($message) { sql_close(); - + $message = trim($message) . "\n"; $message .= debug_string_backtrace() . "\n"; - + error_log('mysql_provider error: ' . $message); - + return false; } @@ -77,19 +84,19 @@ function sql_error($message) { */ function sql_connect($host, $user, $pass, $db) { global $sql_connection; - + $sql_connection = new mysqli($host, $user, $pass, $db); if ($sql_connection->connect_errno) 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; } @@ -110,12 +117,12 @@ function sql_select_db($db_name) { /** * MySQL SELECT query * - * @param string $query + * @param string $query * @return Result array or false on error */ function sql_select($query) { global $sql_connection; - + $result = $sql_connection->query($query); if ($result) { $data = array(); @@ -129,12 +136,12 @@ function sql_select($query) { /** * MySQL execute a query * - * @param string $query + * @param string $query * @return mysqli_result boolean resource or false on error */ function sql_query($query) { global $sql_connection; - + $result = $sql_connection->query($query); if ($result) { return $result; @@ -155,7 +162,7 @@ function sql_id() { /** * Escape a string for a sql query. * - * @param string $query + * @param string $query * @return string */ function sql_escape($query) { @@ -166,7 +173,7 @@ function sql_escape($query) { /** * Count query result lines. * - * @param string $query + * @param string $query * @return int Count of result lines */ function sql_num_query($query) { diff --git a/includes/pages/admin_import.php b/includes/pages/admin_import.php index 8362391d..120ab91b 100644 --- a/includes/pages/admin_import.php +++ b/includes/pages/admin_import.php @@ -1,4 +1,5 @@ <?php + function admin_import_title() { return _("Frab import"); } @@ -116,14 +117,23 @@ function admin_import() { sql_query("DELETE FROM `Room` WHERE `Name`='" . sql_escape($room) . "' LIMIT 1"); list($events_new, $events_updated, $events_deleted) = prepare_events($import_file); - foreach ($events_new as $event) - sql_query("INSERT INTO `Shifts` SET `name`='" . sql_escape($event['name']) . "', `start`=" . sql_escape($event['start']) . ", `end`=" . sql_escape($event['end']) . ", `RID`=" . sql_escape($event['RID']) . ", `PSID`=" . sql_escape($event['PSID']) . ", `URL`='" . sql_escape($event['URL']) . "'"); + foreach ($events_new as $event) { + $result = Shift_create($event); + if ($result === false) + engelsystem_error('Unable to create shift.'); + } - foreach ($events_updated as $event) - sql_query("UPDATE `Shifts` SET `name`='" . sql_escape($event['name']) . "', `start`=" . sql_escape($event['start']) . ", `end`=" . sql_escape($event['end']) . ", `RID`=" . sql_escape($event['RID']) . ", `PSID`=" . sql_escape($event['PSID']) . ", `URL`='" . sql_escape($event['URL']) . "' WHERE `PSID`=" . sql_escape($event['PSID']) . " LIMIT 1"); + foreach ($events_updated as $event) { + $result = Shift_update_by_psid($event); + if ($result === false) + engelsystem_error('Unable to update shift.'); + } - foreach ($events_deleted as $event) - sql_query("DELETE FROM `Shifts` WHERE `PSID`=" . sql_escape($event['PSID']) . " LIMIT 1"); + foreach ($events_deleted as $event) { + $result = Shift_delete_by_psid($event['PSID']); + if ($result === false) + engelsystem_error('Unable to delete shift.'); + } engelsystem_log("Pentabarf import done"); @@ -165,7 +175,7 @@ function prepare_rooms($file) { return array( $rooms_new, - $rooms_deleted + $rooms_deleted ); } diff --git a/includes/pages/admin_shifts.php b/includes/pages/admin_shifts.php index 473022eb..fd5b9b55 100644 --- a/includes/pages/admin_shifts.php +++ b/includes/pages/admin_shifts.php @@ -1,4 +1,5 @@ <?php + function admin_shifts_title() { return _("Create shifts"); } @@ -229,14 +230,17 @@ function admin_shifts() { )) )); } - } elseif (isset($_REQUEST['submit'])) { if (! is_array($_SESSION['admin_shifts_shifts']) || ! is_array($_SESSION['admin_shifts_types'])) redirect(page_link_to('admin_shifts')); foreach ($_SESSION['admin_shifts_shifts'] as $shift) { - sql_query("INSERT INTO `Shifts` SET `start`=" . sql_escape($shift['start']) . ", `end`=" . sql_escape($shift['end']) . ", `RID`=" . sql_escape($shift['RID']) . ", `name`='" . sql_escape($shift['name']) . "'"); - $shift_id = sql_id(); + $shift['URL'] = null; + $shift['PSID'] = null; + $shift_id = Shift_create($shift); + if ($shift_id === false) + engelsystem_error('Unable to create shift.'); + engelsystem_log("Shift created: " . $shift['name'] . " from " . date("Y-m-d H:i", $shift['start']) . " to " . date("Y-m-d H:i", $shift['end'])); $needed_angel_types_info = array(); foreach ($_SESSION['admin_shifts_types'] as $type_id => $count) { diff --git a/includes/pages/admin_user.php b/includes/pages/admin_user.php index 87190857..4e10ca1e 100644 --- a/includes/pages/admin_user.php +++ b/includes/pages/admin_user.php @@ -215,7 +215,6 @@ function admin_user() { $user_source = sql_select("SELECT `Nick`, `UID` FROM `User` WHERE `UID` = '" . sql_escape($id) . "' LIMIT 1"); sql_query("DELETE FROM `User` WHERE `UID`=" . sql_escape($id) . " LIMIT 1"); sql_query("DELETE FROM `UserGroups` WHERE `uid`=" . sql_escape($id)); - sql_query("UPDATE `ShiftEntry` SET `UID`=0, `Comment`=NULL WHERE `UID`=" . sql_escape($id)); engelsystem_log("Deleted user " . User_Nick_render($user_source)); $html .= success("Benutzer gelöscht!", true); } else { diff --git a/includes/pages/user_myshifts.php b/includes/pages/user_myshifts.php index d1c60dd6..d74475d2 100644 --- a/includes/pages/user_myshifts.php +++ b/includes/pages/user_myshifts.php @@ -57,12 +57,15 @@ function user_myshifts() { $comment = strip_request_item_nl('comment'); $user_source = User($shift['UID']); - sql_query("UPDATE `ShiftEntry` SET - `Comment`='" . sql_escape($comment) . "', - `freeloaded`=" . sql_escape($freeloaded ? 1 : 0) . ", - `freeload_comment`='" . sql_escape($freeload_comment) . "' - WHERE `id`=" . sql_escape($id) . " - LIMIT 1"); + $result = ShiftEntry_update(array( + 'id' => $id, + 'Comment' => $comment, + 'freeloaded' => $freeloaded, + 'freeload_comment' => $freeload_comment + )); + if ($result === false) + engelsystem_error('Unable to update shift entr.'); + engelsystem_log("Updated " . User_Nick_render($user_source) . "'s shift " . $shift['name'] . " from " . date("y-m-d H:i", $shift['start']) . " to " . date("y-m-d H:i", $shift['end']) . " with comment " . $comment); success(_("Shift saved.")); redirect(page_link_to('users') . '&action=view&user_id=' . $shifts_user['UID']); @@ -77,15 +80,16 @@ function user_myshifts() { if (count($shift) > 0) { $shift = $shift[0]; if (($shift['start'] > time() + $LETZTES_AUSTRAGEN * 3600) || in_array('user_shifts_admin', $privileges)) { - sql_query("DELETE FROM `ShiftEntry` WHERE `id`=" . sql_escape($id) . " LIMIT 1"); - $msg .= success(_("You have been signed off from the shift."), true); + $result = ShiftEntry_delete($id); + if ($result === false) + engelsystem_error('Unable to delete shift entry.'); + success(_("You have been signed off from the shift.")); } else - $msg .= error(_("It's too late to sign yourself off the shift. If neccessary, ask the dispatcher to do so."), true); + error(_("It's too late to sign yourself off the shift. If neccessary, ask the dispatcher to do so.")); } else redirect(page_link_to('user_myshifts')); } - msg(); redirect(page_link_to('users') . '&action=view'); } ?> diff --git a/includes/pages/user_shifts.php b/includes/pages/user_shifts.php index ee8e3b19..c1a5cc91 100644 --- a/includes/pages/user_shifts.php +++ b/includes/pages/user_shifts.php @@ -20,7 +20,10 @@ function user_shifts() { $shift_entry_source = sql_select("SELECT `User`.`Nick`, `ShiftEntry`.`Comment`, `ShiftEntry`.`UID`, `Shifts`.*, `Room`.`Name`, `AngelTypes`.`name` as `angel_type` FROM `ShiftEntry` JOIN `User` ON (`User`.`UID`=`ShiftEntry`.`UID`) JOIN `AngelTypes` ON (`ShiftEntry`.`TID` = `AngelTypes`.`id`) JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) WHERE `ShiftEntry`.`id`=" . sql_escape($entry_id) . " LIMIT 1"); if (count($shift_entry_source) > 0) { $shift_entry_source = $shift_entry_source[0]; - sql_query("DELETE FROM `ShiftEntry` WHERE `id`=" . sql_escape($entry_id) . " LIMIT 1"); + + $result = ShiftEntry_delete($entry_id); + if ($result === false) + engelsystem_error('Unable to delete shift entry.'); engelsystem_log("Deleted " . User_Nick_render($shift_entry_source) . "'s shift: " . $shift_entry_source['name'] . " at " . $shift_entry_source['Name'] . " from " . date("y-m-d H:i", $shift_entry_source['start']) . " to " . date("y-m-d H:i", $shift_entry_source['end']) . " as " . $shift_entry_source['angel_type']); success(_("Shift entry deleted.")); @@ -122,7 +125,13 @@ function user_shifts() { } if ($ok) { - sql_query("UPDATE `Shifts` SET `start`=" . sql_escape($start) . ", `end`=" . sql_escape($end) . ", `RID`=" . sql_escape($rid) . ", `name`='" . sql_escape($name) . "' WHERE `SID`=" . sql_escape($shift_id) . " LIMIT 1"); + $shift['name'] = $name; + $shift['RID'] = $rid; + $shift['start'] = $start; + $shift['end'] = $end; + $result = Shift_update($shift); + if ($result === false) + engelsystem_error('Unable to update shift.'); sql_query("DELETE FROM `NeededAngelTypes` WHERE `shift_id`=" . sql_escape($shift_id)); $needed_angel_types_info = array(); foreach ($needed_angel_types as $type_id => $count) { @@ -169,9 +178,9 @@ function user_shifts() { // Schicht löschen bestätigt if (isset($_REQUEST['delete'])) { - sql_query("DELETE FROM `ShiftEntry` WHERE `SID`=" . sql_escape($shift_id)); - sql_query("DELETE FROM `NeededAngelTypes` WHERE `shift_id`=" . sql_escape($shift_id)); - sql_query("DELETE FROM `Shifts` WHERE `SID`=" . sql_escape($shift_id) . " LIMIT 1"); + $result = Shift_delete($shift_id); + if ($result === false) + engelsystem_error('Unable to delete shift.'); engelsystem_log("Deleted shift " . $shift['name'] . " from " . date("y-m-d H:i", $shift['start']) . " to " . date("y-m-d H:i", $shift['end'])); success(_("Shift deleted.")); diff --git a/public/index.php b/public/index.php index 0ba203b0..cd77f9f3 100644 --- a/public/index.php +++ b/public/index.php @@ -82,7 +82,7 @@ $free_pages = array( 'api', 'credits', 'angeltypes', - 'users' + 'users' ); // Gewünschte Seite/Funktion @@ -91,10 +91,10 @@ if (! isset($_REQUEST['p'])) $_REQUEST['p'] = isset($user) ? "news" : "login"; if (isset($_REQUEST['p']) && preg_match("/^[a-z0-9_]*$/i", $_REQUEST['p']) && (in_array($_REQUEST['p'], $free_pages) || in_array($_REQUEST['p'], $privileges))) { $p = $_REQUEST['p']; - + $title = $p; $content = ""; - + if ($p == "api") { require_once realpath(__DIR__ . '/../includes/controller/api.php'); error("Api disabled temporily."); @@ -222,7 +222,7 @@ echo template_render('../templates/layout.html', array( 'content' => msg() . $content, 'header_toolbar' => header_toolbar(), 'faq_url' => $faq_url, - 'locale' => $_SESSION['locale'] + 'locale' => $_SESSION['locale'] )); counter(); |