\n"; echo "
\n"; echo "Shift Evaluation from "; printf(" to\n", $startdate); printf("\n", $stopdate); echo " \n"; include ("db.php"); $db_id = mysql_connect($host, $user, $pw); //some parameters for the evaluation //arrays on institute base $instituteshifthours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); $institutedebughours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); $instituteexperthours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); $instituteshexperthours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); $instituteflareexperthours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); $institutetotalhours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); $institutes2= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); $institutenames=array(); //arrays on userbase $users=array(); $shifthours=array(); $debughours=array(); $experthours=array(); $totalhours=array(); $affiliation=array(); //$shifthourstotal=0; //$debughourstotal=0; //$experthourstotal=0; $hourstotal=0; $hourstotal2=0; $hourstotal3=0; $counter=0; //get username-id connection $queryusers="SELECT username, uid FROM logbook.users ORDER BY username"; $resultusers = mysql_query($queryusers); while ($rowusers = mysql_fetch_row($resultusers)) $users[$rowusers[0]]=$rowusers[1]; mysql_free_result($resultusers); //get institutename-id connection $queryinstitutes="SELECT instituteid, institutename FROM memberlist.institutes "; $resultinstitutes = mysql_query($queryinstitutes); while ($rowinstitutes = mysql_fetch_row($resultinstitutes)) $institutenames[$rowinstitutes[0]]=$rowinstitutes[1]; mysql_free_result($resultinstitutes); //loop over the nights in the given time range while ($starttimestamp<=$stoptimestamp) { //get affiliation of users $queryaffiliation="SELECT userid, instituteid FROM memberlist.members "; $queryaffiliation.=" WHERE (start < '".$starttimestamp->format('Y-m-d H:i:s')."'"; $queryaffiliation.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."')"; $queryaffiliation.=" OR (ISNULL(start) AND ISNULL(stop))"; $resultaffiliation = mysql_query($queryaffiliation); while ($rowaffiliation = mysql_fetch_row($resultaffiliation)) $affiliation[$rowaffiliation[0]]=$rowaffiliation[1]; mysql_free_result($resultaffiliation); // get share according to member list $queryshare="SELECT instituteid, COUNT(*) FROM memberlist.members "; $queryshare.=" WHERE start < '".$starttimestamp->format('Y-m-d H:i:s')."'"; $queryshare.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'"; $queryshare.=" GROUP BY instituteid"; $resultshare = mysql_query($queryshare); $sumpeople=0; $institutes= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); while ($rowshare = mysql_fetch_row($resultshare)) { $institutes[$rowshare[0]]+=$rowshare[1]; $sumpeople+=$rowshare[1]; } foreach ($institutes as $key=>$value) $institutes2[$key]+=$value/$sumpeople; // get total hours of night $queryhours="SELECT Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1"; $queryhours.=" FROM factdata.ObservationTimes WHERE fNight=".$starttimestamp->format('Ymd'); $resulthours = mysql_query($queryhours); $rowhours = mysql_fetch_row($resulthours); $numhours=$rowhours[0]; mysql_free_result($resulthours); $hourstotal2+=$numhours; $queryusers="SELECT uid FROM calendar.Data LEFT JOIN logbook.users ON calendar.Data.u=logbook.users.username "; $queryusers.="WHERE CONCAT(y,LPAD(m+1,2,0),LPAD(d,2,0))=".$starttimestamp->format('Ymd'); $queryusers.=" AND u NOT IN ('ETHZ','ISDC','UNIWUE','TUDO','moon')"; // get shifthours per user and institute $queryshifters=$queryusers." AND x=0"; $resultshifters = mysql_query($queryshifters); if (mysql_num_rows($resultshifters)>0) $hourstotal3+=$numhours; while ($rowshifters = mysql_fetch_row($resultshifters)) { if(!isset($shifthours[$rowshifters[0]])) $shifthours[$rowshifters[0]]=0; if(!isset($totalhours[$rowshifters[0]])) $totalhours[$rowshifters[0]]=0; if (!isset($affiliation[$rowshifters[0]])) { $affiliation[$rowshifters[0]] = $rowshifters[0]; $institutes2[$rowshifters[0]] = 0; $institutenames[$rowshifters[0]] = $rowshifters[0]; $institutetotalhours[$rowshifters[0]] = 0; $instituteshifthours[$rowshifters[0]] = 0; } $shifthours[$rowshifters[0]]+=$numhours/mysql_num_rows($resultshifters); $totalhours[$rowshifters[0]]+=$numhours/mysql_num_rows($resultshifters); $instituteshifthours[$affiliation[$rowshifters[0]]]+=$numhours/mysql_num_rows($resultshifters); $institutetotalhours[$affiliation[$rowshifters[0]]]+=$numhours/mysql_num_rows($resultshifters); $hourstotal+=$numhours/mysql_num_rows($resultshifters); } mysql_free_result($resultshifters); //get debugging times per user and institute $querydebug=$queryusers." AND x=1"; $resultdebug = mysql_query($querydebug); while ($rowdebug = mysql_fetch_row($resultdebug)) { if(!isset($debughours[$rowdebug[0]])) $debughours[$rowdebug[0]]=0; if(!isset($totalhours[$rowdebug[0]])) $totalhours[$rowdebug[0]]=0; $debughours[$rowdebug[0]]+=$numhours*0.5; $totalhours[$rowdebug[0]]+=$numhours*0.5; $institutedebughours[$affiliation[$rowdebug[0]]]+=$numhours*0.5; $institutetotalhours[$affiliation[$rowdebug[0]]]+=$numhours*0.5; $hourstotal+=$numhours*0.5; } mysql_free_result($resultdebug); //get experts // old shift scheme: // per period: 3 blocks with 2 shifters -> 6 slots // expert: 2 months = 1 slot // new shift scheme: // per period: 4 blocks with 1 shifters -> 4 slots // expert: 1 months = 1 slot // + SH expert // + add flare expert starting from 2014/03 if ($starttimestamp->format('Ymd')<20171201) $devide=12.0; else $devide=6.0; $queryexperts="SELECT userid FROM memberlist.experts "; $queryexperts.=" WHERE expert=1 "; $queryexperts.=" AND start < '".$starttimestamp->format('Y-m-d H:i:s')."'"; $queryexperts.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'"; $resultexperts = mysql_query($queryexperts); while ($rowexperts = mysql_fetch_row($resultexperts)) { if (!isset($experthours[$rowexperts[0]])) $experthours[$rowexperts[0]]=0; $experthours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultexperts)/$devide; $totalhours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultexperts)/$devide; $instituteexperthours[$affiliation[$rowexperts[0]]]+=$numhours/mysql_num_rows($resultexperts)/$devide; $institutetotalhours[$affiliation[$rowexperts[0]]]+=$numhours/mysql_num_rows($resultexperts)/$devide; $hourstotal+=$numhours/mysql_num_rows($resultexperts)/$devide; } mysql_free_result($resultexperts); // sh experts if ($starttimestamp->format('Ymd')>20171201) { $devide=6.0; $queryshexperts="SELECT userid FROM memberlist.experts "; $queryshexperts.=" WHERE sh=1 "; $queryshexperts.=" AND start < '".$starttimestamp->format('Y-m-d H:i:s')."'"; $queryshexperts.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'"; $resultshexperts = mysql_query($queryshexperts); while ($rowshexperts = mysql_fetch_row($resultshexperts)) { if (!isset($shexperthours[$rowshexperts[0]])) $shexperthours[$rowshexperts[0]]=0; $shexperthours[$rowshexperts[0]]+=$numhours/mysql_num_rows($resultshexperts)/$devide; $totalhours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultshexperts)/$devide; $instituteshexperthours[$affiliation[$rowshexperts[0]]]+=$numhours/mysql_num_rows($resultshexperts)/$devide; $institutetotalhours[$affiliation[$rowshexperts[0]]]+=$numhours/mysql_num_rows($resultshexperts)/$devide; $hourstotal+=$numhours/mysql_num_rows($resultshexperts)/$devide; } mysql_free_result($resultshexperts); } // flare experts if ($starttimestamp->format('Ymd')>20140318) { $devide=6.0; $queryflareexperts="SELECT userid FROM memberlist.experts "; $queryflareexperts.=" WHERE flare=1 "; $queryflareexperts.=" AND start < '".$starttimestamp->format('Y-m-d H:i:s')."'"; $queryflareexperts.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'"; $resultflareexperts = mysql_query($queryflareexperts); while ($rowflareexperts = mysql_fetch_row($resultflareexperts)) { if (!isset($flareexperthours[$rowflareexperts[0]])) $flareexperthours[$rowflareexperts[0]]=0; $flareexperthours[$rowflareexperts[0]]+=$numhours/mysql_num_rows($resultflareexperts)/$devide; $totalhours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultflareexperts)/$devide; $instituteflareexperthours[$affiliation[$rowflareexperts[0]]]+=$numhours/mysql_num_rows($resultflareexperts)/$devide; $institutetotalhours[$affiliation[$rowflareexperts[0]]]+=$numhours/mysql_num_rows($resultflareexperts)/$devide; $hourstotal+=$numhours/mysql_num_rows($resultflareexperts)/$devide; } mysql_free_result($resultflareexperts); } date_add($starttimestamp, date_interval_create_from_date_string("1 days")); $counter++; } foreach ($institutes2 as $key=>$value) $institutes2[$key]=$value/$counter; mysql_free_result($resultshare); mysql_close($db_id); echo "
\n
\n"; //echo "Total number of hours: ".$hourstotal2." h (all nights).
\n"; //echo "Total number of hours: ".$hourstotal3." h (all data taking nights).
\n"; //echo "Total number of hours: ".$hourstotal." h (all data taking nights incl expert hours).\n"; echo "Total number of hours (all duties): ".$hourstotal." h.
\n"; echo "Total number of hours (shift duties): ".$hourstotal3." h.\n"; echo "\n"; echo "\n"; foreach ($institutenames as $id=>$name) { //if (!$institutetotalhours[$id]) // continue; if ($institutes2[$id]==0) continue; echo "\n"; } echo "
institutehours (shift/debug/expert/flare/sh)share required:done->fulfilled
"; echo $name;//."(".$id.")"; echo ""; printf("%7.2f (%7.2f/%7.2f/%7.2f/%7.2f/%7.2f)",$institutetotalhours[$id],$instituteshifthours[$id],$institutedebughours[$id],$instituteexperthours[$id],$instituteflareexperthours[$id],$instituteshexperthours[$id]); echo ""; printf("%7.2f%%: %7.2f%% -> %5.2f%%",$institutes2[$id]*100, $institutetotalhours[$id]/$hourstotal*100, ($institutetotalhours[$id]/$hourstotal)/$institutes2[$id]*100); echo "

\n"; echo "\n"; echo "\n"; foreach ($users as $name=>$id) { if (!isset($totalhours[$id]) || !$totalhours[$id]) continue; if(!isset($totalhours[$id])) $totalhours[$id]=0; if(!isset($debughours[$id])) $debughours[$id]=0; if(!isset($experthours[$id])) $experthours[$id]=0; echo "\n"; } echo "
userhours(shift/debug/expert/flare/sh)
"; echo $name;//." (".$id.")"; echo ""; printf("%5.2f (%5.2f/%5.2f/%5.2f/%5.2f/%5.2f)",$totalhours[$id],$shifthours[$id],$debughours[$id],$experthours[$id],$flareexperthours[$id],$shexperthours[$id]); echo "
\n
\n"; echo "Remark: Debugging times before 2013/02 are not taken into account, i.e. first 16 months of debugging is missing in the automatic evaluation system."; echo "
\n
\n"; echo "
\n"; echo "\n"; echo "\n"; ?>