multiply night shifts by 2 (confable with raw mysql)

2 files changed, 15 insertions, 8 deletions

diff --git a/includes/pages/admin_active.php b/includes/pages/admin_active.php
index 0dabe568..241d0904 100644
--- a/includes/pages/admin_active.php
+++ b/includes/pages/admin_active.php
@@ -1,6 +1,6 @@
 <?php
 function admin_active() {
-  global $tshirt_sizes;
+  global $tshirt_sizes, $shift_sum_formula;
 
   $msg = "";
   $search = "";
@@ -24,7 +24,7 @@ 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`, SUM(`end`-`start`) 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 `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 `shift_length` DESC" . $limit);
       $user_nicks = array();
       foreach ($users as $usr) {
         sql_query("UPDATE `User` SET `Aktiv` = 1 WHERE `UID`=" . sql_escape($usr['UID']));
@@ -80,7 +80,7 @@ function admin_active() {
     else $msg = error("Angel not found.", true);
   }
 
-  $users = sql_select("SELECT `User`.*, COUNT(`ShiftEntry`.`id`) as `shift_count`, SUM(`end`-`start`) 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 `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 `shift_length` DESC" . $limit);
 
   $table = "";
   if ($search == "")
@@ -141,4 +141,4 @@ function admin_active() {
     'link' => page_link_to('admin_active')
   ));
 }
-?>
\ No newline at end of file
+?>
diff --git a/install/default-conf/config.php b/install/default-conf/config.php
index d27d809a..543d1dce 100644
--- a/install/default-conf/config.php
+++ b/install/default-conf/config.php
@@ -37,10 +37,6 @@ $gmdateOffset=3600;
 // für Developen 1, sonst = 0
 $debug = 0;
 
-// SSL Cert-KEY
-$show_SSLCERT = "MD5:<br>MD5SED<br>\n".
-    "SHA1:<br>SHA1SED";
-
 //globale const. fuer schischtplan
 $GlobalZeileProStunde = 4;
 
@@ -61,4 +57,15 @@ $PentabarfXMLEventID = "31";
 /// Passord for external Authorization, function only active if the var is defined
 //$CurrentExternAuthPass = 23;
 
+// multiply "night shifts" (start or end between 2 and 6 exclusive) by 2
+$shift_sum_formula = "SUM(
+  (1+(
+    (HOUR(FROM_UNIXTIME(`Shifts`.`end`)) > 2 AND HOUR(FROM_UNIXTIME(`Shifts`.`end`)) < 6)
+    OR (HOUR(FROM_UNIXTIME(`Shifts`.`start`)) > 2 AND HOUR(FROM_UNIXTIME(`Shifts`.`start`)) < 6)
+    OR (HOUR(FROM_UNIXTIME(`Shifts`.`start`)) <= 2 AND HOUR(FROM_UNIXTIME(`Shifts`.`end`)) >= 6)
+  ))*(`Shifts`.`end` - `Shifts`.`start`)
+)";
+
+// weigh every shift the same
+//$shift_sum_formula = "SUM(`end` - `start`)";
 ?>