\n"; echo "

\n"; echo "shift evaluation from "; printf(" to\n", $startdate); printf("\n", $stopdate); echo " \n"; echo "\n"; include ("db.php"); $db_id = mysql_connect($host, $user, $pw); $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;"; $resultnames = mysql_query($querynames); //$resultscales = mysql_query($queryscales); while ($rownames = mysql_fetch_row($resultnames)) { $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.+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.=" 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.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate; $resultnights = mysql_query($querynights); while ($rownights = mysql_fetch_row($resultnights)) { if ($rownights[5]>0) { //echo $rownights[0]." ".$rownights[1]." ".$rownights[2]." ".$rownights[3]." ".$rownights[4]."
"; $sum+=$rownights[3]/$rownights[4]; } } echo "\n\n\n"; mysql_free_result($resultnights); } //$sumhours+=$hours*scale mysql_free_result($resultnames); //mysql_free_result($resultscales); echo "

".$rownames[1]."

".$sum."

\n
\n"; echo "Debugging times: (only after 2013/01, i.e. first 16 months missing)
\n"; echo "\n"; $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;"; $resultnames = mysql_query($querynames); //$resultscales = mysql_query($queryscales); while ($rownames = mysql_fetch_row($resultnames)) { $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.=" 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.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate; $resultnights = mysql_query($querynights); while ($rownights = mysql_fetch_row($resultnights)) { if ($rownights[4]>0) { //echo $rownights[0]." ".$rownights[1]." ".$rownights[2]." ".$rownights[3]." ".$rownights[4]."
"; $sum+=$rownights[3]*0.5; //$sum+=$rownights[3]/$rownights[4]; } } echo "\n\n\n"; mysql_free_result($resultnights); } //$sumhours+=$hours*scale mysql_free_result($resultnames); //mysql_free_result($resultscales); echo "

".$rownames[1]."

".$sum."

\n
\n
\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)."
"; //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:
\n"; echo "\n"; if ($sumthomas) echo "\n\n\n"; if ($sumdani) echo "\n\n\n"; if ($sumjens) echo "\n\n\n"; if ($sumdom) echo "\n\n\n"; echo "

tbretz	".$sumthomas."
dorner	".$sumdani."
jbuss	".$sumjens."
dneise	".$sumdom."

\n"; mysql_close($db_id); echo "

\n"; echo "\n"; echo "\n"; ?>