\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 ); $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; $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); $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); 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 //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; $queryexperts="SELECT userid FROM memberlist.experts "; $queryexperts.=" WHERE start < '".$starttimestamp->format('Y-m-d H:i:s')."'"; //$queryexperts.=" AND stop > '".$stoptimestamp->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); 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: ".$hourstotal." h."; echo "\n"; echo "\n"; foreach ($institutenames as $id=>$name) { //if (!$institutetotalhours[$id]) // continue; if ($institutes2[$id]==0) continue; echo "\n"; } echo "

institute	hours (shift/debug/expert)	share required:done->fulfilled
"; echo $name;//."(".$id.")"; echo "	"; printf("%7.2f (%7.2f/%7.2f/%7.2f)",$institutetotalhours[$id],$instituteshifthours[$id],$institutedebughours[$id],$instituteexperthours[$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 "

user	hours(shift/debug/expert)
"; echo $name;//." (".$id.")"; echo "	"; printf("%5.2f (%5.2f/%5.2f/%5.2f)",$totalhours[$id],$shifthours[$id],$debughours[$id],$experthours[$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"; ?>