From 25889920cf216e46873da968afe4b2dae5fead64 Mon Sep 17 00:00:00 2001
From: Philip Häusler <msquare@notrademark.de>
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 <msquare@notrademark.de>
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') . '&amp;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') . '&amp;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 .= '<li' . ($page == $p ? ' class="selected"' : '') . '><a href="' . page_link_to($page) . '">' . $title . '</a></li>';
-
+  
   if ($menu != "")
     $menu = '<nav class="container"><h4>' . $name . '</h4><ul class="content">' . $menu . '</ul></nav>';
   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 <msquare@notrademark.de>
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 @@
 <?php
 
+/**
+ * Delete a shifttype.
+ */
 function shifttype_delete_controller() {
+  if (! isset($_REQUEST['shifttype_id']))
+    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'));
+  
+  if (isset($_REQUEST['confirmed'])) {
+    $result = ShiftType_delete($shifttype['id']);
+    if ($result === false)
+      engelsystem_error('Unable to delete shifttype.');
+    
+    engelsystem_log('Deleted shifttype ' . $shifttype['name']);
+    success(sprintf(_('Shifttype %s deleted.'), $shifttype['name']));
+    redirect(page_link_to('shifttypes'));
+  }
+  
+  return array(
+      sprintf(_("Delete shifttype %s"), $shifttype['name']),
+      ShiftType_delete_view($shifttype) 
+  );
 }
 
 /**
@@ -16,6 +41,20 @@ function shifttype_edit_controller() {
   if ($angeltypes === false)
     engelsystem_error("Unable to load angel types.");
   
+  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'));
+    }
+    $shifttype_id = $shifttype['id'];
+    $name = $shifttype['name'];
+    $angeltype_id = $shifttype['angeltype_id'];
+    $description = $shifttype['description'];
+  }
+  
   if (isset($_REQUEST['submit'])) {
     $ok = true;
     
@@ -35,10 +74,16 @@ function shifttype_edit_controller() {
       $description = strip_request_item_nl('description');
     
     if ($ok) {
-      if ($shifttype_id) {} else {
+      if ($shifttype_id) {
+        $result = ShiftType_update($shifttype_id, $name, $angeltype_id, $description);
+        if ($result === false)
+          engelsystem_error('Unable to update shifttype.');
+        engelsystem_log('Updated shifttype ' . $name);
+        success(_('Updated shifttype.'));
+      } else {
         $shifttype_id = ShiftType_create($name, $angeltype_id, $description);
         if ($shifttype_id === false)
-          engelsystem_error('Unable to create shift type.');
+          engelsystem_error('Unable to create shifttype.');
         engelsystem_log('Created shifttype ' . $name);
         success(_('Created shifttype.'));
       }
@@ -53,6 +98,25 @@ function shifttype_edit_controller() {
 }
 
 function shifttype_controller() {
+  if (! isset($_REQUEST['shifttype_id']))
+    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'));
+  
+  $angeltype = null;
+  if ($shifttype['angeltype_id'] != null) {
+    $angeltype = AngelType($shifttype['angeltype_id']);
+    if ($angeltype === false)
+      engelsystem_error('Unable to load angeltype.');
+  }
+  
+  return [
+      $shifttype['name'],
+      ShiftType_view($shifttype, $angeltype) 
+  ];
 }
 
 /**
diff --git a/includes/view/ShiftTypes_view.php b/includes/view/ShiftTypes_view.php
index 580e4c45..5210f29c 100644
--- a/includes/view/ShiftTypes_view.php
+++ b/includes/view/ShiftTypes_view.php
@@ -1,6 +1,13 @@
 <?php
 
 function ShiftType_delete_view($shifttype) {
+  return page_with_title(sprintf(_("Delete shifttype %s"), $shifttype['name']), array(
+      info(sprintf(_("Do you want to delete shifttype %s?"), $shifttype['name']), true),
+      buttons(array(
+          button(page_link_to('shifttypes'), _("cancel"), 'cancel'),
+          button(page_link_to('shifttypes') . '&action=delete&shifttype_id=' . $shifttype['id'] . '&confirmed', _("delete"), 'ok') 
+      )) 
+  ));
 }
 
 function ShiftType_edit_view($name, $angeltype_id, $angeltypes, $description, $shifttype_id) {
@@ -25,7 +32,21 @@ function ShiftType_edit_view($name, $angeltype_id, $angeltypes, $description, $s
   ]);
 }
 
-function ShiftType_view($shifttype) {
+function ShiftType_view($shifttype, $angeltype) {
+  $parsedown = new Parsedown();
+  $title = $shifttype['name'];
+  if ($angeltype)
+    $title .= ' <small>' . sprintf(_('for team %s'), $angeltype['name']) . '</small>';
+  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 <msquare@notrademark.de>
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 <msquare@notrademark.de>
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

