Index: trunk/www/dch/shifteval.php
===================================================================
--- trunk/www/dch/shifteval.php	(revision 17151)
+++ trunk/www/dch/shifteval.php	(revision 17531)
@@ -33,9 +33,9 @@
     $db_id = mysql_connect($host, $user, $pw);
 
-    $querynames="SELECT count(*), u FROM calendar.data ";
+    $querynames="SELECT count(*), u FROM calendar.Data ";
     $querynames.=" WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') ";
     $querynames.=" AND NOT x=1 ";
     $querynames.=" GROUP BY u ORDER BY u;";
-    //$queryscales="select count(*), concat(y,if(m+1<10, concat('0',m), m+1),if (d<10, concat('0',d),d)) from calendar.data where u not in ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE')  group by y,m,d order by u;";
+    //$queryscales="select count(*), concat(y,if(m+1<10, concat('0',m), m+1),if (d<10, concat('0',d),d)) from calendar.Data where u not in ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE')  group by y,m,d order by u;";
     $resultnames  = mysql_query($querynames);
     //$resultscales  = mysql_query($queryscales);
@@ -43,11 +43,11 @@
     {
         $sum=0;
-        //$querynights="select fNight, fStartObservation, fStopObservation, Time_to_Sec(Timediff(fStopObservation, fStartObservatioN))/60./60., (Select count(*) from calendar.data where u not in ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') and y=Date_format(fNight, '%Y') and m=Date_format(fNight, '%m')-1 and d=Date_format(fNight,'%d') and u='".$rownames[1]."' group by y,m,d) from factdata.observationtimes;";
+        //$querynights="select fNight, fStartObservation, fStopObservation, Time_to_Sec(Timediff(fStopObservation, fStartObservatioN))/60./60., (Select count(*) from calendar.Data where u not in ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') and y=Date_format(fNight, '%Y') and m=Date_format(fNight, '%m')-1 and d=Date_format(fNight,'%d') and u='".$rownames[1]."' group by y,m,d) from factdata.ObservationTimes;";
         $querynights="SELECT fNight, fStartObservation, fStopObservation, Time_to_Sec(Timediff(fStopObservation, fStartObservatioN))/60./60.+1, ";
-        $querynights.=" (SELECT COUNT(*) FROM calendar.data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
+        $querynights.=" (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
         $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
-        $querynights.=" GROUP BY y,m,d), (SELECT COUNT(*) FROM calendar.data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
+        $querynights.=" GROUP BY y,m,d), (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
         $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
-        $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.observationtimes ";
+        $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.ObservationTimes ";
         $querynights.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate;
         $resultnights  = mysql_query($querynights);
@@ -70,12 +70,12 @@
     echo "</table>\n<br>\n";
 
-    echo "Debugging times: <br>\n";
+    echo "Debugging times: (only after 2013/01, i.e. first 16 months missing) <br>\n";
     echo "<table>\n";
 
-    $querynames="SELECT count(*), u FROM calendar.data ";
+    $querynames="SELECT count(*), u FROM calendar.Data ";
     $querynames.=" WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') ";
     $querynames.=" AND NOT x=0 ";
     $querynames.=" GROUP BY u ORDER BY u;";
-    //$queryscales="select count(*), concat(y,if(m+1<10, concat('0',m), m+1),if (d<10, concat('0',d),d)) from calendar.data where u not in ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE')  group by y,m,d order by u;";
+    //$queryscales="select count(*), concat(y,if(m+1<10, concat('0',m), m+1),if (d<10, concat('0',d),d)) from calendar.Data where u not in ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE')  group by y,m,d order by u;";
     $resultnames  = mysql_query($querynames);
     //$resultscales  = mysql_query($queryscales);
@@ -84,7 +84,7 @@
         $sum=0;
         $querynights="SELECT fNight, fStartObservation, fStopObservation, Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1, ";
-        $querynights.=" (SELECT COUNT(*) FROM calendar.data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=0 ";
+        $querynights.=" (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=0 ";
         $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
-        $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.observationtimes ";
+        $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.ObservationTimes ";
         $querynights.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate;
         $resultnights  = mysql_query($querynights);
@@ -99,5 +99,5 @@
         }
         echo "<tr>\n<td>".$rownames[1]."</td>\n<td>".$sum."</td>\n</tr>";
-    mysql_free_result($resultnights);
+        mysql_free_result($resultnights);
     }
     //$sumhours+=$hours*scale
@@ -106,6 +106,63 @@
     //mysql_free_result($resultscales);
 
+    echo "</table>\n<br>\n<br>\n";
+
+    $queryexpert  ="SELECT fNight, Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1 FROM factdata.ObservationTimes ";
+    $queryexpert .=" WHERE fNight>=".$startdate." and fNight<=".$stopdate." GROUP BY fNight";
+    $resultexpert  = mysql_query($queryexpert);
+    $sumexpert=0;
+    $sumthomas=0;
+    $sumdani=0;
+    $sumjens=0;
+    $sumdom=0;
+    //echo mysql_num_rows($resultexpert)."<br>";
+
+    //total 1 year: 4000 hours ~10.9h/night -> 46 hours/shift slot
+    //1 shift slot 46 hours
+    // 4000/46 = 87
+    // -> 87 shift slots
+    // expert 6 slots
+    // 87/6 -> 14.5
+    // => expert counts 1/13 of normal time
+    $devide=14.5;
+    while ($rowexpert = mysql_fetch_row($resultexpert))
+    {
+        if ($rowexpert[0]>20130801)
+        {
+            $sumthomas+=$rowexpert[1]/$devide/3;
+            $sumdani+=$rowexpert[1]/$devide/3;
+            $sumjens+=$rowexpert[1]/$devide/3;
+        }
+        else
+        {
+            if ($rowexpert[0]>20130101)
+            {
+                $sumthomas+=$rowexpert[1]/$devide/2;
+                $sumdani+=$rowexpert[1]/$devide/2;
+            }
+            else
+            {
+                $sumthomas+=$rowexpert[1]/$devide/3;
+                $sumdani+=$rowexpert[1]/$devide/3;
+                $sumdom+=$rowexpert[1]/$devide/3;
+            }
+        }
+        $sumexpert+=$rowexpert[1]/$devide;
+    }
+
+    //echo $sumexpert;
+    echo "Expert-on-Call times: <br>\n";
+    echo "<table>\n";
+    if ($sumthomas)
+        echo "<tr>\n<td>tbretz</td>\n<td>".$sumthomas."</td>\n</tr>";
+    if ($sumdani)
+        echo "<tr>\n<td>dorner</td>\n<td>".$sumdani."</td>\n</tr>";
+    if ($sumjens)
+        echo "<tr>\n<td>jbuss</td>\n<td>".$sumjens."</td>\n</tr>";
+    if ($sumdom)
+        echo "<tr>\n<td>dneise</td>\n<td>".$sumdom."</td>\n</tr>";
+    echo "</table>\n";
+
     mysql_close($db_id);
-    echo "</table>\n";
 
     echo "</form>\n";