| 1 | <html>
|
|---|
| 2 | <head>
|
|---|
| 3 | <meta name="Author" content="Daniela Dorner" />
|
|---|
| 4 | <meta http-equiv="content-type" content="text/html; charset=utf-8" />
|
|---|
| 5 | <title>FACT Project</title>
|
|---|
| 6 | <link rel="StyleSheet" type="text/css" href="../style.css" />
|
|---|
| 7 | </head>
|
|---|
| 8 | <?php
|
|---|
| 9 |
|
|---|
| 10 | echo (file_get_contents("../shifteval/header.html"));
|
|---|
| 11 |
|
|---|
| 12 | if (!empty($_GET["startdate"]))
|
|---|
| 13 | $startdate=$_GET["startdate"];
|
|---|
| 14 | else
|
|---|
| 15 | $startdate="20111001";
|
|---|
| 16 | if (!empty($_GET["stopdate"]))
|
|---|
| 17 | $stopdate=$_GET["stopdate"];
|
|---|
| 18 | else
|
|---|
| 19 | $stopdate=date("Ymd");
|
|---|
| 20 | $starttimestamp = new DateTime($startdate);
|
|---|
| 21 | date_add($starttimestamp, date_interval_create_from_date_string("12 hours"));
|
|---|
| 22 | $stoptimestamp = new DateTime($stopdate);
|
|---|
| 23 | date_add($stoptimestamp, date_interval_create_from_date_string("12 hours"));
|
|---|
| 24 |
|
|---|
| 25 | echo "<body>\n";
|
|---|
| 26 | echo "<form action='shifteval.php' METHOD='GET'>\n";
|
|---|
| 27 | echo "<b>Shift Evaluation</b> from ";
|
|---|
| 28 | printf("<input name='startdate' type='text' size='10' maxlength='10' value='%s'> to\n", $startdate);
|
|---|
| 29 | printf("<input name='stopdate' type='text' size='10' maxlength='10' value='%s'>\n", $stopdate);
|
|---|
| 30 | echo "<input type='submit' value='Show'> \n";
|
|---|
| 31 |
|
|---|
| 32 | include ("db.php");
|
|---|
| 33 | $db_id = mysql_connect($host, $user, $pw);
|
|---|
| 34 |
|
|---|
| 35 | //some parameters for the evaluation
|
|---|
| 36 | //arrays on institute base
|
|---|
| 37 | $instituteshifthours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 38 | $institutedebughours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 39 | $instituteexperthours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 40 | $institutetotalhours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 41 | $institutes2= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 42 | $institutenames=array();
|
|---|
| 43 | //arrays on userbase
|
|---|
| 44 | $users=array();
|
|---|
| 45 | $shifthours=array();
|
|---|
| 46 | $debughours=array();
|
|---|
| 47 | $experthours=array();
|
|---|
| 48 | $totalhours=array();
|
|---|
| 49 | $affiliation=array();
|
|---|
| 50 | //$shifthourstotal=0;
|
|---|
| 51 | //$debughourstotal=0;
|
|---|
| 52 | //$experthourstotal=0;
|
|---|
| 53 | $hourstotal=0;
|
|---|
| 54 | $counter=0;
|
|---|
| 55 |
|
|---|
| 56 | //get username-id connection
|
|---|
| 57 | $queryusers="SELECT username, uid FROM logbook.users ORDER BY username";
|
|---|
| 58 | $resultusers = mysql_query($queryusers);
|
|---|
| 59 | while ($rowusers = mysql_fetch_row($resultusers))
|
|---|
| 60 | $users[$rowusers[0]]=$rowusers[1];
|
|---|
| 61 | mysql_free_result($resultusers);
|
|---|
| 62 |
|
|---|
| 63 | //get institutename-id connection
|
|---|
| 64 | $queryinstitutes="SELECT instituteid, institutename FROM memberlist.institutes ";
|
|---|
| 65 | $resultinstitutes = mysql_query($queryinstitutes);
|
|---|
| 66 | while ($rowinstitutes = mysql_fetch_row($resultinstitutes))
|
|---|
| 67 | $institutenames[$rowinstitutes[0]]=$rowinstitutes[1];
|
|---|
| 68 | mysql_free_result($resultinstitutes);
|
|---|
| 69 |
|
|---|
| 70 | //loop over the nights in the given time range
|
|---|
| 71 | while ($starttimestamp<=$stoptimestamp)
|
|---|
| 72 | {
|
|---|
| 73 | //get affiliation of users
|
|---|
| 74 | $queryaffiliation="SELECT userid, instituteid FROM memberlist.members ";
|
|---|
| 75 | $queryaffiliation.=" WHERE (start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 76 | $queryaffiliation.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."')";
|
|---|
| 77 | $queryaffiliation.=" OR (ISNULL(start) AND ISNULL(stop))";
|
|---|
| 78 | $resultaffiliation = mysql_query($queryaffiliation);
|
|---|
| 79 | while ($rowaffiliation = mysql_fetch_row($resultaffiliation))
|
|---|
| 80 | $affiliation[$rowaffiliation[0]]=$rowaffiliation[1];
|
|---|
| 81 | mysql_free_result($resultaffiliation);
|
|---|
| 82 |
|
|---|
| 83 | // get share according to member list
|
|---|
| 84 | $queryshare="SELECT instituteid, COUNT(*) FROM memberlist.members ";
|
|---|
| 85 | $queryshare.=" WHERE start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 86 | $queryshare.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 87 | $queryshare.=" GROUP BY instituteid";
|
|---|
| 88 | $resultshare = mysql_query($queryshare);
|
|---|
| 89 | $sumpeople=0;
|
|---|
| 90 | $institutes= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 91 | while ($rowshare = mysql_fetch_row($resultshare))
|
|---|
| 92 | {
|
|---|
| 93 | $institutes[$rowshare[0]]+=$rowshare[1];
|
|---|
| 94 | $sumpeople+=$rowshare[1];
|
|---|
| 95 | }
|
|---|
| 96 | foreach ($institutes as $key=>$value)
|
|---|
| 97 | $institutes2[$key]+=$value/$sumpeople;
|
|---|
| 98 |
|
|---|
| 99 | // get total hours of night
|
|---|
| 100 | $queryhours="SELECT Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1";
|
|---|
| 101 | $queryhours.=" FROM factdata.ObservationTimes WHERE fNight=".$starttimestamp->format('Ymd');
|
|---|
| 102 | $resulthours = mysql_query($queryhours);
|
|---|
| 103 | $rowhours = mysql_fetch_row($resulthours);
|
|---|
| 104 | $numhours=$rowhours[0];
|
|---|
| 105 | mysql_free_result($resulthours);
|
|---|
| 106 |
|
|---|
| 107 | $queryusers="SELECT uid FROM calendar.Data LEFT JOIN logbook.users ON calendar.Data.u=logbook.users.username ";
|
|---|
| 108 | $queryusers.="WHERE CONCAT(y,LPAD(m+1,2,0),LPAD(d,2,0))=".$starttimestamp->format('Ymd');
|
|---|
| 109 | $queryusers.=" AND u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE')";
|
|---|
| 110 | // get shifthours per user and institute
|
|---|
| 111 | $queryshifters=$queryusers." AND x=0";
|
|---|
| 112 | $resultshifters = mysql_query($queryshifters);
|
|---|
| 113 | while ($rowshifters = mysql_fetch_row($resultshifters))
|
|---|
| 114 | {
|
|---|
| 115 | $shifthours[$rowshifters[0]]+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 116 | $totalhours[$rowshifters[0]]+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 117 | $instituteshifthours[$affiliation[$rowshifters[0]]]+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 118 | $institutetotalhours[$affiliation[$rowshifters[0]]]+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 119 | $hourstotal+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 120 | }
|
|---|
| 121 | mysql_free_result($resultshifters);
|
|---|
| 122 | //get debugging times per user and institute
|
|---|
| 123 | $querydebug=$queryusers." AND x=1";
|
|---|
| 124 | $resultdebug = mysql_query($querydebug);
|
|---|
| 125 | while ($rowdebug = mysql_fetch_row($resultdebug))
|
|---|
| 126 | {
|
|---|
| 127 | $debughours[$rowdebug[0]]+=$numhours*0.5;
|
|---|
| 128 | $totalhours[$rowdebug[0]]+=$numhours*0.5;
|
|---|
| 129 | $institutedebughours[$affiliation[$rowdebug[0]]]+=$numhours*0.5;
|
|---|
| 130 | $institutetotalhours[$affiliation[$rowdebug[0]]]+=$numhours*0.5;
|
|---|
| 131 | $hourstotal+=$numhours*0.5;
|
|---|
| 132 | }
|
|---|
| 133 | mysql_free_result($resultdebug);
|
|---|
| 134 |
|
|---|
| 135 | //get experts
|
|---|
| 136 | //total 1 year: 4000 hours ~10.9h/night -> 46 hours/shift slot
|
|---|
| 137 | //1 shift slot 46 hours
|
|---|
| 138 | // 4000/46 = 87
|
|---|
| 139 | // -> 87 shift slots
|
|---|
| 140 | // expert 6 slots
|
|---|
| 141 | // 87/6 -> 14.5
|
|---|
| 142 | // => expert counts 1/13 of normal time
|
|---|
| 143 | $devide=14.5;
|
|---|
| 144 | $queryexperts="SELECT userid FROM memberlist.experts ";
|
|---|
| 145 | $queryexperts.=" WHERE start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 146 | //$queryexperts.=" AND stop > '".$stoptimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 147 | $queryexperts.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 148 | $resultexperts = mysql_query($queryexperts);
|
|---|
| 149 | while ($rowexperts = mysql_fetch_row($resultexperts))
|
|---|
| 150 | {
|
|---|
| 151 | $experthours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 152 | $totalhours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 153 | $instituteexperthours[$affiliation[$rowexperts[0]]]+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 154 | $institutetotalhours[$affiliation[$rowexperts[0]]]+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 155 | $hourstotal+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 156 | }
|
|---|
| 157 | mysql_free_result($resultexperts);
|
|---|
| 158 |
|
|---|
| 159 | date_add($starttimestamp, date_interval_create_from_date_string("1 days"));
|
|---|
| 160 | $counter++;
|
|---|
| 161 | }
|
|---|
| 162 | foreach ($institutes2 as $key=>$value)
|
|---|
| 163 | $institutes2[$key]=$value/$counter;
|
|---|
| 164 | mysql_free_result($resultshare);
|
|---|
| 165 |
|
|---|
| 166 | mysql_close($db_id);
|
|---|
| 167 |
|
|---|
| 168 | echo "<br>\n<br>\n";
|
|---|
| 169 | 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.";
|
|---|
| 170 | echo "<br>\n<br>\n";
|
|---|
| 171 | echo "<b>Total number of hours:</b> ".$hourstotal." h.";
|
|---|
| 172 | echo "<table>\n";
|
|---|
| 173 | echo "<tr><td align='center'><b>institute</b></td><td align='center'>hours (shift/debug/expert)</td><td>share required:done->fulfilled</td></tr>\n";
|
|---|
| 174 | foreach ($institutenames as $id=>$name)
|
|---|
| 175 | {
|
|---|
| 176 | if (!$institutetotalhours[$id])
|
|---|
| 177 | continue;
|
|---|
| 178 | echo "<tr><td>";
|
|---|
| 179 | echo $name;//."(".$id.")";
|
|---|
| 180 | echo "</td><td align='right'>";
|
|---|
| 181 | printf("%7.2f (%7.2f/%7.2f/%7.2f)",$institutetotalhours[$id],$instituteshifthours[$id],$institutedebughours[$id],$instituteexperthours[$id]);
|
|---|
| 182 | echo "</td><td align='right'>";
|
|---|
| 183 | printf("%7.2f%%: %7.2f%% -> %5.2f%%",$institutes2[$id]*100, $institutetotalhours[$id]/$hourstotal*100, ($institutetotalhours[$id]/$hourstotal)/$institutes2[$id]*100);
|
|---|
| 184 | echo "</td></tr>\n";
|
|---|
| 185 | }
|
|---|
| 186 | echo "</table>\n";
|
|---|
| 187 |
|
|---|
| 188 | echo "<table>\n";
|
|---|
| 189 | echo "<tr><td align='center'><b>user</b></td><td align='center'>hours(shift/debug/expert)</td></tr>\n";
|
|---|
| 190 | foreach ($users as $name=>$id)
|
|---|
| 191 | {
|
|---|
| 192 | if (!$totalhours[$id])
|
|---|
| 193 | continue;
|
|---|
| 194 | echo "<tr><td>";
|
|---|
| 195 | echo $name;//." (".$id.")";
|
|---|
| 196 | echo "</td><td align='right'>";
|
|---|
| 197 | printf("%5.2f (%5.2f/%5.2f/%5.2f)",$totalhours[$id],$shifthours[$id],$debughours[$id],$experthours[$id]);
|
|---|
| 198 | echo "</td></tr>\n";
|
|---|
| 199 | }
|
|---|
| 200 | echo "</table>\n<br>\n";
|
|---|
| 201 |
|
|---|
| 202 | echo "</form>\n";
|
|---|
| 203 | echo "</body>\n";
|
|---|
| 204 | echo "</html>\n";
|
|---|
| 205 |
|
|---|
| 206 | ?>
|
|---|
