From 25889920cf216e46873da968afe4b2dae5fead64 Mon Sep 17 00:00:00 2001 From: Philip Häusler Date: Tue, 16 Dec 2014 08:43:41 +0100 Subject: prepare for shift types model, add db update --- db/update.sql | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'db/update.sql') diff --git a/db/update.sql b/db/update.sql index 7caf7223..d7d91a80 100644 --- a/db/update.sql +++ b/db/update.sql @@ -1,3 +1,12 @@ +/* introduce shift types */ +CREATE TABLE IF NOT EXISTS `ShiftTypes` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `angeltype_id` int(11) DEFAULT NULL, + `description` text NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; + /* cleanup */ ALTER TABLE `User` DROP `ICQ` ; -- cgit v1.2.3-54-g00ecf From c8cc46886b4a163f9408df80c3bfbdcfa4ae2f7b Mon Sep 17 00:00:00 2001 From: Philip Häusler Date: Tue, 16 Dec 2014 09:25:36 +0100 Subject: shift type list --- db/update.sql | 4 +- includes/controller/shifttypes_controller.php | 13 +++++++ includes/sys_menu.php | 53 ++++++++++++++------------- includes/view/ShiftTypes_view.php | 19 +++++++++- public/index.php | 2 + 5 files changed, 63 insertions(+), 28 deletions(-) (limited to 'db/update.sql') diff --git a/db/update.sql b/db/update.sql index d7d91a80..8c9bdaec 100644 --- a/db/update.sql +++ b/db/update.sql @@ -5,7 +5,9 @@ CREATE TABLE IF NOT EXISTS `ShiftTypes` ( `angeltype_id` int(11) DEFAULT NULL, `description` text NOT NULL, PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; +INSERT INTO `engelsystem`.`Privileges` (`id`, `name`, `desc`) VALUES (NULL , 'shifttypes', 'Administrate shift types'); +INSERT INTO `GroupPrivileges` SET `group_id`=-5, `privilege_id`=(SELECT `id` FROM `Privileges` WHERE `name`='shifttypes'); /* cleanup */ ALTER TABLE `User` DROP `ICQ` ; diff --git a/includes/controller/shifttypes_controller.php b/includes/controller/shifttypes_controller.php index 3ceb5c0d..6d061111 100644 --- a/includes/controller/shifttypes_controller.php +++ b/includes/controller/shifttypes_controller.php @@ -10,6 +10,19 @@ function shifttype_controller() { } function shifttypes_list_controller() { + global $privileges, $user; + + if (! in_array('shifttypes', $privileges)) + redirect('?'); + + $shifttypes = ShiftTypes(); + if ($shifttypes === false) + engelsystem_error("Unable to load shifttypes."); + + return array( + shifttypes_title(), + ShiftTypes_list_view($shifttypes) + ); } /** diff --git a/includes/sys_menu.php b/includes/sys_menu.php index ffe76219..75c7c3db 100644 --- a/includes/sys_menu.php +++ b/includes/sys_menu.php @@ -15,21 +15,21 @@ function page_link_to_absolute($page) { */ function header_toolbar() { global $p, $privileges, $user, $enable_tshirt_size; - + $toolbar_items = array(); - + if (isset($user)) $toolbar_items[] = toolbar_item_link(page_link_to('users') . '&action=view', 'time', User_shift_state_render($user)); - + if (! isset($user) && in_array('register', $privileges)) $toolbar_items[] = toolbar_item_link(page_link_to('register'), 'plus', register_title(), $p == 'register'); - + if (in_array('login', $privileges)) $toolbar_items[] = toolbar_item_link(page_link_to('login'), 'log-in', login_title(), $p == 'login'); - + if (isset($user) && in_array('user_messages', $privileges)) $toolbar_items[] = toolbar_item_link(page_link_to('user_messages'), 'envelope', user_unread_messages()); - + $hints = []; if (isset($user)) { $hint_class = 'info'; @@ -40,30 +40,30 @@ function header_toolbar() { if ($new_questions != "") $hints[] = $new_questions; } - + $unconfirmed_hint = user_angeltypes_unconfirmed_hint(); if ($unconfirmed_hint != '') $hints[] = $unconfirmed_hint; - + if (User_is_freeloader($user)) { $hints[] = error(sprintf(_("You freeloaded at least %s shifts. Shift signup is locked. Please go to heavens desk to be unlocked again."), $max_freeloadable_shifts), true); $hint_class = 'danger'; $glyphicon = 'warning-sign'; } - + // Hinweis für Engel, die noch nicht angekommen sind if ($user['Gekommen'] == 0) { $hints[] = error(_("You are not marked as arrived. Please go to heaven's desk, get your angel badge and/or tell them that you arrived already."), true); $hint_class = 'danger'; $glyphicon = 'warning-sign'; } - + if ($enable_tshirt_size && $user['Size'] == "") { $hints[] = error(_("You need to specify a tshirt size in your settings!"), true); $hint_class = 'danger'; $glyphicon = 'warning-sign'; } - + if ($user['DECT'] == "") { $hints[] = error(_("You need to specify a DECT phone number in your settings! If you don't have a DECT phone, just enter \"-\"."), true); $hint_class = 'danger'; @@ -72,40 +72,40 @@ function header_toolbar() { } if (count($hints) > 0) $toolbar_items[] = toolbar_popover($glyphicon . ' text-' . $hint_class, '', $hints, 'bg-' . $hint_class); - + $user_submenu = make_langselect(); $user_submenu[] = toolbar_item_divider(); if (in_array('user_myshifts', $privileges)) $toolbar_items[] = toolbar_item_link(page_link_to('users') . '&action=view', ' icon-icon_angel', $user['Nick'], $p == 'users'); - + if (in_array('user_settings', $privileges)) $user_submenu[] = toolbar_item_link(page_link_to('user_settings'), 'list-alt', settings_title(), $p == 'user_settings'); - + if (in_array('logout', $privileges)) $user_submenu[] = toolbar_item_link(page_link_to('logout'), 'log-out', logout_title(), $p == 'logout'); - + if (count($user_submenu) > 0) $toolbar_items[] = toolbar_dropdown('', '', $user_submenu); - + return toolbar($toolbar_items, true); } function make_navigation() { global $p, $privileges; - + $menu = array(); $pages = array( "news" => news_title(), "user_meetings" => meetings_title(), "user_shifts" => shifts_title(), "angeltypes" => angeltypes_title(), - "user_questions" => questions_title() + "user_questions" => questions_title() ); - + foreach ($pages as $page => $title) if (in_array($page, $privileges)) $menu[] = toolbar_item_link(page_link_to($page), '', $title, $page == $p); - + $admin_menu = array(); $admin_pages = array( "admin_arrive" => admin_arrive_title(), @@ -113,31 +113,32 @@ function make_navigation() { "admin_user" => admin_user_title(), "admin_free" => admin_free_title(), "admin_questions" => admin_questions_title(), + "shifttypes" => shifttypes_title(), "admin_shifts" => admin_shifts_title(), "admin_rooms" => admin_rooms_title(), "admin_groups" => admin_groups_title(), "admin_import" => admin_import_title(), - "admin_log" => admin_log_title() + "admin_log" => admin_log_title() ); - + foreach ($admin_pages as $page => $title) if (in_array($page, $privileges)) $admin_menu[] = toolbar_item_link(page_link_to($page), '', $title, $page == $p); - + if (count($admin_menu) > 0) $menu[] = toolbar_dropdown('', _("Admin"), $admin_menu); - + return toolbar($menu); } function make_navigation_for($name, $pages) { global $privileges, $p; - + $menu = ""; foreach ($pages as $page) if (in_array($page, $privileges)) $menu .= '' . $title . ''; - + if ($menu != "") $menu = ''; return $menu; diff --git a/includes/view/ShiftTypes_view.php b/includes/view/ShiftTypes_view.php index c18ea493..a7df2c87 100644 --- a/includes/view/ShiftTypes_view.php +++ b/includes/view/ShiftTypes_view.php @@ -9,7 +9,24 @@ function ShiftType_edit_view($name, $angeltype_id, $angeltypes, $description, $s function ShiftType_view($shifttype) { } -function ShiftTypes_list_view() { +function ShiftTypes_list_view($shifttypes) { + foreach ($shifttypes as &$shifttype) { + $shifttype['actions'] = table_buttons([ + button(page_link_to('shifttypes') . '&action=edit&shifttype_id=' . $shifttype['id'], _("edit"), "btn-xs"), + button(page_link_to('shifttypes') . '&action=delete&shifttype_id=' . $shifttypes['id'], _("delete"), "btn-xs") + ]); + } + + return page_with_title(shifttypes_title(), array( + msg(), + buttons(array( + button(page_link_to('shifttypes') . '&action=edit', _("New shifttype"), 'add') + )), + table(array( + 'name' => _("Name"), + 'actions' => "" + ), $shifttypes) + )); } ?> \ No newline at end of file diff --git a/public/index.php b/public/index.php index 6574b619..51bd7661 100644 --- a/public/index.php +++ b/public/index.php @@ -131,6 +131,8 @@ if (isset($_REQUEST['p']) && preg_match("/^[a-z0-9_]*$/i", $_REQUEST['p']) && (i list($title, $content) = users_controller(); } elseif ($p == "user_angeltypes") { list($title, $content) = user_angeltypes_controller(); + } elseif ($p == "shifttypes") { + list($title, $content) = shifttypes_controller(); } elseif ($p == "news") { $title = news_title(); $content = user_news(); -- cgit v1.2.3-54-g00ecf From 3f8e5e47c05a2cf8fe4e51034523b0b6021b45f5 Mon Sep 17 00:00:00 2001 From: Philip Häusler Date: Tue, 16 Dec 2014 10:19:38 +0100 Subject: add update, delete and view for shift type --- db/update.sql | 2 + includes/controller/shifttypes_controller.php | 68 ++++++++++++++++++++++++++- includes/view/ShiftTypes_view.php | 23 ++++++++- 3 files changed, 90 insertions(+), 3 deletions(-) (limited to 'db/update.sql') diff --git a/db/update.sql b/db/update.sql index 8c9bdaec..7375b507 100644 --- a/db/update.sql +++ b/db/update.sql @@ -6,6 +6,8 @@ CREATE TABLE IF NOT EXISTS `ShiftTypes` ( `description` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; +ALTER TABLE `ShiftTypes` ADD INDEX ( `angeltype_id` ); +ALTER TABLE `ShiftTypes` ADD FOREIGN KEY ( `angeltype_id` ) REFERENCES `engelsystem`.`AngelTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; INSERT INTO `engelsystem`.`Privileges` (`id`, `name`, `desc`) VALUES (NULL , 'shifttypes', 'Administrate shift types'); INSERT INTO `GroupPrivileges` SET `group_id`=-5, `privilege_id`=(SELECT `id` FROM `Privileges` WHERE `name`='shifttypes'); diff --git a/includes/controller/shifttypes_controller.php b/includes/controller/shifttypes_controller.php index aaefaa33..f5760ad5 100644 --- a/includes/controller/shifttypes_controller.php +++ b/includes/controller/shifttypes_controller.php @@ -1,6 +1,31 @@ ' . sprintf(_('for team %s'), $angeltype['name']) . ''; + return page_with_title($title, [ + msg(), + buttons([ + button(page_link_to('shifttypes'), shifttypes_title(), 'back'), + $angeltype ? button(page_link_to('angeltypes') . '&action=view&angeltype_id=' . $angeltype['id'], $angeltype['name']) : '', + button(page_link_to('shifttypes'), _('edit'), 'edit'), + button(page_link_to('shifttypes'), _('delete'), 'delete') + ]), + $parsedown->parse($shifttype['description']) + ]); } function ShiftTypes_list_view($shifttypes) { -- cgit v1.2.3-54-g00ecf From a791a75b0a893308f35865542149c77f8761b3a0 Mon Sep 17 00:00:00 2001 From: Philip Häusler Date: Wed, 17 Dec 2014 17:22:35 +0100 Subject: integrate shift type into shifts --- db/update.sql | 7 +++++ includes/controller/shifts_controller.php | 9 +++++- includes/model/ShiftEntry_model.php | 3 +- includes/model/Shifts_model.php | 20 +++++++++---- includes/pages/admin_active.php | 17 +++++++++-- includes/pages/admin_free.php | 10 ++++++- includes/pages/user_ical.php | 9 +++++- includes/pages/user_myshifts.php | 8 ++++- includes/pages/user_shifts.php | 50 ++++++++++++++++++++++++++----- 9 files changed, 112 insertions(+), 21 deletions(-) (limited to 'db/update.sql') diff --git a/db/update.sql b/db/update.sql index 7375b507..401be215 100644 --- a/db/update.sql +++ b/db/update.sql @@ -11,6 +11,13 @@ ALTER TABLE `ShiftTypes` ADD FOREIGN KEY ( `angeltype_id` ) REFERENCES `engelsys INSERT INTO `engelsystem`.`Privileges` (`id`, `name`, `desc`) VALUES (NULL , 'shifttypes', 'Administrate shift types'); INSERT INTO `GroupPrivileges` SET `group_id`=-5, `privilege_id`=(SELECT `id` FROM `Privileges` WHERE `name`='shifttypes'); +ALTER TABLE `Shifts` ADD `shifttype_id` INT NOT NULL AFTER `SID`, ADD INDEX ( `shifttype_id` ); +INSERT INTO `ShiftTypes` SELECT DISTINCT NULL , `name` , NULL , '' FROM `Shifts`; +UPDATE `Shifts` SET `shifttype_id`=(SELECT `id` FROM `ShiftTypes` WHERE `ShiftTypes`.`name`=`Shifts`.`name`); +ALTER TABLE `Shifts` DROP `name`; +ALTER TABLE `Shifts` ADD FOREIGN KEY ( `shifttype_id` ) REFERENCES `engelsystem`.`ShiftTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `Shifts` ADD `title` TEXT NULL AFTER `SID`; + /* cleanup */ ALTER TABLE `User` DROP `ICQ` ; diff --git a/includes/controller/shifts_controller.php b/includes/controller/shifts_controller.php index 868e903b..8352092d 100644 --- a/includes/controller/shifts_controller.php +++ b/includes/controller/shifts_controller.php @@ -48,7 +48,14 @@ function shifts_json_export_controller() { require_once realpath(__DIR__ . '/../pages/user_shifts.php'); view_user_shifts(); } else { - $ical_shifts = sql_select("SELECT `Shifts`.*, `Room`.`Name` as `room_name` FROM `ShiftEntry` INNER JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) INNER JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) WHERE `UID`=" . sql_escape($user['UID']) . " ORDER BY `start`"); + $ical_shifts = sql_select(" + SELECT `ShiftTypes`.`name`, `Shifts`.*, `Room`.`Name` as `room_name` + FROM `ShiftEntry` + INNER JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) + INNER JOIN `ShiftTypes` ON (`Shifts`.`shifttype_id`=`ShiftTypes`.`id`) + INNER JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) + WHERE `UID`=" . sql_escape($user['UID']) . " + ORDER BY `start`"); } header("Content-Type: application/json; charset=utf-8"); diff --git a/includes/model/ShiftEntry_model.php b/includes/model/ShiftEntry_model.php index e1f0cd2e..5129f15a 100644 --- a/includes/model/ShiftEntry_model.php +++ b/includes/model/ShiftEntry_model.php @@ -76,7 +76,8 @@ function ShiftEntries_upcoming_for_user($user) { return sql_select(" SELECT * FROM `ShiftEntry` - JOIN `Shifts` ON `Shifts`.`SID`=`ShiftEntry`.`SID` + 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` diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index 8530a0c8..1f22ebdb 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -12,7 +12,7 @@ function Shift_delete_by_psid($shift_psid) { */ function Shift_delete($shift_id) { mail_shift_delete(Shift($shift_id)); - + return sql_query("DELETE FROM `Shifts` WHERE `SID`=" . sql_escape($shift_id)); } @@ -22,12 +22,13 @@ function Shift_delete($shift_id) { function Shift_update($shift) { $old_shift = Shift($shift['SID']); mail_shift_change(Shift($shift['SID']), $shift); - + return sql_query("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']) . ", - `name`=" . sql_null($shift['name']) . ", + `title`=" . sql_null($shift['title']) . ", `URL`=" . sql_null($shift['URL']) . ", `PSID`=" . sql_null($shift['PSID']) . " WHERE `SID`=" . sql_escape($shift['SID'])); @@ -53,10 +54,11 @@ function Shift_update_by_psid($shift) { */ function Shift_create($shift) { $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']) . ", - `name`=" . sql_null($shift['name']) . ", + `title`=" . sql_null($shift['title']) . ", `URL`=" . sql_null($shift['URL']) . ", `PSID`=" . sql_null($shift['PSID'])); if ($result === false) @@ -72,6 +74,7 @@ function Shifts_by_user($user) { SELECT * FROM `ShiftEntry` 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']) . " ORDER BY `start` @@ -130,7 +133,11 @@ function Shifts_filtered() { * ID */ function Shift($id) { - $shifts_source = sql_select("SELECT * FROM `Shifts` WHERE `SID`=" . sql_escape($id) . " LIMIT 1"); + $shifts_source = sql_select(" + SELECT `Shifts`.*, `ShiftTypes`.`name` + FROM `Shifts` + JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + WHERE `SID`=" . sql_escape($id)); $shiftsEntry_source = sql_select("SELECT `TID` , `UID` , `freeloaded` FROM `ShiftEntry` WHERE `SID`=" . sql_escape($id)); if ($shifts_source === false) @@ -160,8 +167,9 @@ function Shift($id) { */ function Shifts() { $shifts_source = sql_select(" - SELECT `Shifts`.*, `Room`.`RID`, `Room`.`Name` as `room_name` + 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) diff --git a/includes/pages/admin_active.php b/includes/pages/admin_active.php index 58c36f5d..08c436fc 100644 --- a/includes/pages/admin_active.php +++ b/includes/pages/admin_active.php @@ -32,7 +32,14 @@ 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); + $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); $user_nicks = array(); foreach ($users as $usr) { sql_query("UPDATE `User` SET `Aktiv` = 1 WHERE `UID`=" . sql_escape($usr['UID'])); @@ -85,7 +92,13 @@ function admin_active() { $msg = error(_("Angel not found."), true); } - $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 GROUP BY `User`.`UID` ORDER BY `force_active` DESC, `shift_length` DESC" . $limit); + $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 + GROUP BY `User`.`UID` + ORDER BY `force_active` DESC, `shift_length` DESC" . $limit); $matched_users = array(); if ($search == "") diff --git a/includes/pages/admin_free.php b/includes/pages/admin_free.php index 13d4f795..b56da4d1 100644 --- a/includes/pages/admin_free.php +++ b/includes/pages/admin_free.php @@ -27,7 +27,15 @@ function admin_free() { foreach ($angel_types_source as $angel_type) $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 = 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`"); $free_users_table = array(); if ($search == "") diff --git a/includes/pages/user_ical.php b/includes/pages/user_ical.php index 911f48aa..ba832842 100644 --- a/includes/pages/user_ical.php +++ b/includes/pages/user_ical.php @@ -22,7 +22,14 @@ function user_ical() { require_once realpath(__DIR__ . '/user_shifts.php'); view_user_shifts(); } else { - $ical_shifts = sql_select("SELECT `Shifts`.*, `Room`.`Name` as `room_name` FROM `ShiftEntry` INNER JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) INNER JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) WHERE `UID`=" . sql_escape($user['UID']) . " ORDER BY `start`"); + $ical_shifts = sql_select(" + SELECT `ShiftTypes`.`name`, `Shifts`.*, `Room`.`Name` as `room_name` + FROM `ShiftEntry` + INNER JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) + JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + INNER JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) + WHERE `UID`=" . sql_escape($user['UID']) . " + ORDER BY `start`"); } header("Content-Type: text/calendar; charset=utf-8"); diff --git a/includes/pages/user_myshifts.php b/includes/pages/user_myshifts.php index 6fe9afa2..e557054d 100644 --- a/includes/pages/user_myshifts.php +++ b/includes/pages/user_myshifts.php @@ -35,12 +35,14 @@ function user_myshifts() { `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($id) . " AND `UID`=" . sql_escape($shifts_user['UID']) . " LIMIT 1"); @@ -76,7 +78,11 @@ function user_myshifts() { redirect(page_link_to('user_myshifts')); } elseif (isset($_REQUEST['cancel']) && preg_match("/^[0-9]*$/", $_REQUEST['cancel'])) { $id = $_REQUEST['cancel']; - $shift = sql_select("SELECT `Shifts`.`start` FROM `Shifts` INNER JOIN `ShiftEntry` USING (`SID`) WHERE `ShiftEntry`.`id`=" . sql_escape($id) . " AND `UID`=" . sql_escape($shifts_user['UID']) . " LIMIT 1"); + $shift = sql_select(" + SELECT `Shifts`.`start` + FROM `Shifts` + INNER JOIN `ShiftEntry` USING (`SID`) + WHERE `ShiftEntry`.`id`=" . sql_escape($id) . " AND `UID`=" . sql_escape($shifts_user['UID'])); if (count($shift) > 0) { $shift = $shift[0]; if (($shift['start'] > time() + $LETZTES_AUSTRAGEN * 3600) || in_array('user_shifts_admin', $privileges)) { diff --git a/includes/pages/user_shifts.php b/includes/pages/user_shifts.php index a536c1ac..5181d707 100644 --- a/includes/pages/user_shifts.php +++ b/includes/pages/user_shifts.php @@ -17,7 +17,15 @@ function user_shifts() { else redirect(page_link_to('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"); + $shift_entry_source = sql_select(" + SELECT `User`.`Nick`, `ShiftEntry`.`Comment`, `ShiftEntry`.`UID`, `ShiftTypes`.`name`, `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 `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) + WHERE `ShiftEntry`.`id`=" . sql_escape($entry_id)); if (count($shift_entry_source) > 0) { $shift_entry_source = $shift_entry_source[0]; @@ -44,7 +52,11 @@ function user_shifts() { * if (sql_num_query("SELECT * FROM `ShiftEntry` WHERE `SID`=" . sql_escape($shift_id) . " LIMIT 1") > 0) { error("Du kannst nur Schichten bearbeiten, bei denen niemand eingetragen ist."); redirect(page_link_to('user_shift')); } */ - $shift = sql_select("SELECT * FROM `Shifts` JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) WHERE `SID`=" . sql_escape($shift_id) . " LIMIT 1"); + $shift = sql_select(" + SELECT `ShiftTypes`.`name`, `Shifts`.*, `Room`.* FROM `Shifts` + JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) + JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + WHERE `SID`=" . sql_escape($shift_id)); if (count($shift) == 0) redirect(page_link_to('user_shifts')); $shift = $shift[0]; @@ -171,7 +183,12 @@ function user_shifts() { else redirect(page_link_to('user_shifts')); - $shift = sql_select("SELECT * FROM `Shifts` JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) WHERE `SID`=" . sql_escape($shift_id) . " LIMIT 1"); + $shift = sql_select(" + SELECT `Shifts`.*, `ShiftTypes`.`name`, `Room`.* + FROM `Shifts` + JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) + JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + WHERE `SID`=" . sql_escape($shift_id)); if (count($shift) == 0) redirect(page_link_to('user_shifts')); $shift = $shift[0]; @@ -197,7 +214,12 @@ function user_shifts() { else redirect(page_link_to('user_shifts')); - $shift = sql_select("SELECT * FROM `Shifts` JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) WHERE `SID`=" . sql_escape($shift_id) . " LIMIT 1"); + $shift = sql_select(" + SELECT `ShiftTypes`.`name`, `Shifts`.*, `Room`.* + FROM `Shifts` + JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) + JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + WHERE `SID`=" . sql_escape($shift_id)); if (count($shift) == 0) redirect(page_link_to('user_shifts')); $shift = $shift[0]; @@ -214,7 +236,11 @@ function user_shifts() { } // Another shift the user is signed up for collides with this one - if (! in_array('user_shifts_admin', $privileges) && sql_num_query("SELECT `Shifts`.`SID` FROM `Shifts` INNER JOIN `ShiftEntry` ON (`Shifts`.`SID` = `ShiftEntry`.`SID` AND `ShiftEntry`.`UID` = " . sql_escape($user['UID']) . ") WHERE `start` < '" . sql_escape($shift['end']) . "' AND `end` > '" . sql_escape($shift['start']) . "'") > 0) { + if (! in_array('user_shifts_admin', $privileges) && sql_num_query(" + SELECT `Shifts`.`SID` + FROM `Shifts` + INNER JOIN `ShiftEntry` ON (`Shifts`.`SID` = `ShiftEntry`.`SID` AND `ShiftEntry`.`UID` = " . sql_escape($user['UID']) . ") + WHERE `start` < '" . sql_escape($shift['end']) . "' AND `end` > '" . sql_escape($shift['start']) . "'") > 0) { error(_("You already subscribed to shift in the same timeslot. Please contact a dispatcher to join the shift.")); redirect(page_link_to('user_shifts')); } @@ -304,7 +330,10 @@ function view_user_shifts() { global $ical_shifts; $ical_shifts = array(); - $days = sql_select_single_col("SELECT DISTINCT DATE(FROM_UNIXTIME(`start`)) AS `id`, DATE(FROM_UNIXTIME(`start`)) AS `name` FROM `Shifts` ORDER BY `start`"); + $days = sql_select_single_col(" + SELECT DISTINCT DATE(FROM_UNIXTIME(`start`)) AS `id`, DATE(FROM_UNIXTIME(`start`)) AS `name` + FROM `Shifts` + ORDER BY `start`"); if (count($days) == 0) { error(_("The administration has not configured any shifts yet.")); @@ -409,9 +438,10 @@ function view_user_shifts() { 0 ); - $SQL = "SELECT DISTINCT `Shifts`.*, `Room`.`Name` as `room_name`, nat2.`special_needs` > 0 AS 'has_special_needs' + $SQL = "SELECT DISTINCT `ShiftTypes`.`name`, `Shifts`.*, `Room`.`Name` as `room_name`, nat2.`special_needs` > 0 AS 'has_special_needs' FROM `Shifts` INNER JOIN `Room` USING (`RID`) + INNER JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) LEFT JOIN (SELECT COUNT(*) AS special_needs , nat3.`shift_id` FROM `NeededAngelTypes` AS nat3 WHERE `shift_id` IS NOT NULL GROUP BY nat3.`shift_id`) AS nat2 ON nat2.`shift_id` = `Shifts`.`SID` INNER JOIN `NeededAngelTypes` AS nat ON nat.`count` != 0 AND nat.`angel_type_id` IN (" . implode(',', $_SESSION['user_shifts']['types']) . ") AND ((nat2.`special_needs` > 0 AND nat.`shift_id` = `Shifts`.`SID`) OR ((nat2.`special_needs` = 0 OR nat2.`special_needs` IS NULL) AND nat.`room_id` = `RID`)) LEFT JOIN (SELECT se.`SID`, se.`TID`, COUNT(*) as count FROM `ShiftEntry` AS se GROUP BY se.`SID`, se.`TID`) AS entries ON entries.`SID` = `Shifts`.`SID` AND entries.`TID` = nat.`angel_type_id` @@ -429,7 +459,11 @@ function view_user_shifts() { $SQL .= " ORDER BY `start`"; $shifts = sql_select($SQL); - $ownshifts_source = sql_select("SELECT `Shifts`.* FROM `Shifts` INNER JOIN `ShiftEntry` ON (`Shifts`.`SID` = `ShiftEntry`.`SID` AND `ShiftEntry`.`UID` = '" . sql_escape($user['UID']) . "') + $ownshifts_source = sql_select(" + SELECT `ShiftTypes`.`name`, `Shifts`.* + FROM `Shifts` + INNER JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + INNER JOIN `ShiftEntry` ON (`Shifts`.`SID` = `ShiftEntry`.`SID` AND `ShiftEntry`.`UID` = '" . sql_escape($user['UID']) . "') WHERE `Shifts`.`RID` IN (" . implode(',', $_SESSION['user_shifts']['rooms']) . ") AND `start` BETWEEN " . $starttime . " AND " . $endtime); $ownshifts = array(); -- cgit v1.2.3-54-g00ecf From 74b98b8c3e37c2ff578617d3c3b4ab2b02cb06b9 Mon Sep 17 00:00:00 2001 From: Philip Häusler Date: Thu, 25 Dec 2014 20:57:02 +0100 Subject: fix update script for shifttypes --- db/update.sql | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'db/update.sql') diff --git a/db/update.sql b/db/update.sql index 401be215..681d2a7c 100644 --- a/db/update.sql +++ b/db/update.sql @@ -12,11 +12,12 @@ INSERT INTO `engelsystem`.`Privileges` (`id`, `name`, `desc`) VALUES (NULL , 'sh INSERT INTO `GroupPrivileges` SET `group_id`=-5, `privilege_id`=(SELECT `id` FROM `Privileges` WHERE `name`='shifttypes'); ALTER TABLE `Shifts` ADD `shifttype_id` INT NOT NULL AFTER `SID`, ADD INDEX ( `shifttype_id` ); +UPDATE `Shifts` SET `name`='' WHERE `name` IS NULL; INSERT INTO `ShiftTypes` SELECT DISTINCT NULL , `name` , NULL , '' FROM `Shifts`; UPDATE `Shifts` SET `shifttype_id`=(SELECT `id` FROM `ShiftTypes` WHERE `ShiftTypes`.`name`=`Shifts`.`name`); -ALTER TABLE `Shifts` DROP `name`; -ALTER TABLE `Shifts` ADD FOREIGN KEY ( `shifttype_id` ) REFERENCES `engelsystem`.`ShiftTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `Shifts` ADD `title` TEXT NULL AFTER `SID`; +ALTER TABLE `Shifts` ADD FOREIGN KEY ( `shifttype_id` ) REFERENCES `engelsystem`.`ShiftTypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `Shifts` DROP `name`; /* cleanup */ ALTER TABLE `User` DROP `ICQ` ; -- cgit v1.2.3-54-g00ecf