| 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 | $instituteshexperthours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 41 | $instituteflareexperthours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 42 | $institutetotalhours= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 43 | $institutes2= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 44 | $institutenames=array();
|
|---|
| 45 | //arrays on userbase
|
|---|
| 46 | $users=array();
|
|---|
| 47 | $shifthours=array();
|
|---|
| 48 | $debughours=array();
|
|---|
| 49 | $experthours=array();
|
|---|
| 50 | $totalhours=array();
|
|---|
| 51 | $affiliation=array();
|
|---|
| 52 | //$shifthourstotal=0;
|
|---|
| 53 | //$debughourstotal=0;
|
|---|
| 54 | //$experthourstotal=0;
|
|---|
| 55 | $hourstotal=0;
|
|---|
| 56 | $hourstotal2=0;
|
|---|
| 57 | $hourstotal3=0;
|
|---|
| 58 | $counter=0;
|
|---|
| 59 |
|
|---|
| 60 | //get username-id connection
|
|---|
| 61 | $queryusers="SELECT username, uid FROM logbook.users ORDER BY username";
|
|---|
| 62 | $resultusers = mysql_query($queryusers);
|
|---|
| 63 | while ($rowusers = mysql_fetch_row($resultusers))
|
|---|
| 64 | $users[$rowusers[0]]=$rowusers[1];
|
|---|
| 65 | mysql_free_result($resultusers);
|
|---|
| 66 |
|
|---|
| 67 | //get institutename-id connection
|
|---|
| 68 | $queryinstitutes="SELECT instituteid, institutename FROM memberlist.institutes ";
|
|---|
| 69 | $resultinstitutes = mysql_query($queryinstitutes);
|
|---|
| 70 | while ($rowinstitutes = mysql_fetch_row($resultinstitutes))
|
|---|
| 71 | $institutenames[$rowinstitutes[0]]=$rowinstitutes[1];
|
|---|
| 72 | mysql_free_result($resultinstitutes);
|
|---|
| 73 |
|
|---|
| 74 | //loop over the nights in the given time range
|
|---|
| 75 | while ($starttimestamp<=$stoptimestamp)
|
|---|
| 76 | {
|
|---|
| 77 | //get affiliation of users
|
|---|
| 78 | $queryaffiliation="SELECT userid, instituteid FROM memberlist.members ";
|
|---|
| 79 | $queryaffiliation.=" WHERE (start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 80 | $queryaffiliation.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."')";
|
|---|
| 81 | $queryaffiliation.=" OR (ISNULL(start) AND ISNULL(stop))";
|
|---|
| 82 | $resultaffiliation = mysql_query($queryaffiliation);
|
|---|
| 83 | while ($rowaffiliation = mysql_fetch_row($resultaffiliation))
|
|---|
| 84 | $affiliation[$rowaffiliation[0]]=$rowaffiliation[1];
|
|---|
| 85 | mysql_free_result($resultaffiliation);
|
|---|
| 86 |
|
|---|
| 87 | // get share according to member list
|
|---|
| 88 | $queryshare="SELECT instituteid, COUNT(*) FROM memberlist.members ";
|
|---|
| 89 | $queryshare.=" WHERE start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 90 | $queryshare.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 91 | $queryshare.=" GROUP BY instituteid";
|
|---|
| 92 | $resultshare = mysql_query($queryshare);
|
|---|
| 93 | $sumpeople=0;
|
|---|
| 94 | $institutes= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
|
|---|
| 95 | while ($rowshare = mysql_fetch_row($resultshare))
|
|---|
| 96 | {
|
|---|
| 97 | $institutes[$rowshare[0]]+=$rowshare[1];
|
|---|
| 98 | $sumpeople+=$rowshare[1];
|
|---|
| 99 | }
|
|---|
| 100 | foreach ($institutes as $key=>$value)
|
|---|
| 101 | $institutes2[$key]+=$value/$sumpeople;
|
|---|
| 102 |
|
|---|
| 103 | // get total hours of night
|
|---|
| 104 | $queryhours="SELECT Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1";
|
|---|
| 105 | $queryhours.=" FROM factdata.ObservationTimes WHERE fNight=".$starttimestamp->format('Ymd');
|
|---|
| 106 | $resulthours = mysql_query($queryhours);
|
|---|
| 107 | $rowhours = mysql_fetch_row($resulthours);
|
|---|
| 108 | $numhours=$rowhours[0];
|
|---|
| 109 | mysql_free_result($resulthours);
|
|---|
| 110 | $hourstotal2+=$numhours;
|
|---|
| 111 |
|
|---|
| 112 | $queryusers="SELECT uid FROM calendar.Data LEFT JOIN logbook.users ON calendar.Data.u=logbook.users.username ";
|
|---|
| 113 | $queryusers.="WHERE CONCAT(y,LPAD(m+1,2,0),LPAD(d,2,0))=".$starttimestamp->format('Ymd');
|
|---|
| 114 | $queryusers.=" AND u NOT IN ('ETHZ','ISDC','UNIWUE','TUDO','moon')";
|
|---|
| 115 | // get shifthours per user and institute
|
|---|
| 116 | $queryshifters=$queryusers." AND x=0";
|
|---|
| 117 | $resultshifters = mysql_query($queryshifters);
|
|---|
| 118 | if (mysql_num_rows($resultshifters)>0)
|
|---|
| 119 | $hourstotal3+=$numhours;
|
|---|
| 120 | while ($rowshifters = mysql_fetch_row($resultshifters))
|
|---|
| 121 | {
|
|---|
| 122 | if(!isset($shifthours[$rowshifters[0]]))
|
|---|
| 123 | $shifthours[$rowshifters[0]]=0;
|
|---|
| 124 | if(!isset($totalhours[$rowshifters[0]]))
|
|---|
| 125 | $totalhours[$rowshifters[0]]=0;
|
|---|
| 126 | if (!isset($affiliation[$rowshifters[0]]))
|
|---|
| 127 | {
|
|---|
| 128 | $affiliation[$rowshifters[0]] = $rowshifters[0];
|
|---|
| 129 | $institutes2[$rowshifters[0]] = 0;
|
|---|
| 130 | $institutenames[$rowshifters[0]] = $rowshifters[0];
|
|---|
| 131 | $institutetotalhours[$rowshifters[0]] = 0;
|
|---|
| 132 | $instituteshifthours[$rowshifters[0]] = 0;
|
|---|
| 133 | }
|
|---|
| 134 |
|
|---|
| 135 | $shifthours[$rowshifters[0]]+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 136 | $totalhours[$rowshifters[0]]+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 137 | $instituteshifthours[$affiliation[$rowshifters[0]]]+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 138 | $institutetotalhours[$affiliation[$rowshifters[0]]]+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 139 | $hourstotal+=$numhours/mysql_num_rows($resultshifters);
|
|---|
| 140 | }
|
|---|
| 141 | mysql_free_result($resultshifters);
|
|---|
| 142 | //get debugging times per user and institute
|
|---|
| 143 | $querydebug=$queryusers." AND x=1";
|
|---|
| 144 | $resultdebug = mysql_query($querydebug);
|
|---|
| 145 | while ($rowdebug = mysql_fetch_row($resultdebug))
|
|---|
| 146 | {
|
|---|
| 147 | if(!isset($debughours[$rowdebug[0]]))
|
|---|
| 148 | $debughours[$rowdebug[0]]=0;
|
|---|
| 149 | if(!isset($totalhours[$rowdebug[0]]))
|
|---|
| 150 | $totalhours[$rowdebug[0]]=0;
|
|---|
| 151 |
|
|---|
| 152 | $debughours[$rowdebug[0]]+=$numhours*0.5;
|
|---|
| 153 | $totalhours[$rowdebug[0]]+=$numhours*0.5;
|
|---|
| 154 | $institutedebughours[$affiliation[$rowdebug[0]]]+=$numhours*0.5;
|
|---|
| 155 | $institutetotalhours[$affiliation[$rowdebug[0]]]+=$numhours*0.5;
|
|---|
| 156 | $hourstotal+=$numhours*0.5;
|
|---|
| 157 | }
|
|---|
| 158 | mysql_free_result($resultdebug);
|
|---|
| 159 |
|
|---|
| 160 | //get experts
|
|---|
| 161 | // old shift scheme:
|
|---|
| 162 | // per period: 3 blocks with 2 shifters -> 6 slots
|
|---|
| 163 | // expert: 2 months = 1 slot
|
|---|
| 164 | // new shift scheme:
|
|---|
| 165 | // per period: 4 blocks with 1 shifters -> 4 slots
|
|---|
| 166 | // expert: 1 months = 1 slot
|
|---|
| 167 | // + SH expert
|
|---|
| 168 | // + add flare expert starting from 2014/03
|
|---|
| 169 | if ($starttimestamp->format('Ymd')<20171201)
|
|---|
| 170 | $devide=12.0;
|
|---|
| 171 | else
|
|---|
| 172 | $devide=6.0;
|
|---|
| 173 | $queryexperts="SELECT userid FROM memberlist.experts ";
|
|---|
| 174 | $queryexperts.=" WHERE expert=1 ";
|
|---|
| 175 | $queryexperts.=" AND start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 176 | $queryexperts.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 177 | $resultexperts = mysql_query($queryexperts);
|
|---|
| 178 | while ($rowexperts = mysql_fetch_row($resultexperts))
|
|---|
| 179 | {
|
|---|
| 180 | if (!isset($experthours[$rowexperts[0]]))
|
|---|
| 181 | $experthours[$rowexperts[0]]=0;
|
|---|
| 182 |
|
|---|
| 183 | $experthours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 184 | $totalhours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 185 | $instituteexperthours[$affiliation[$rowexperts[0]]]+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 186 | $institutetotalhours[$affiliation[$rowexperts[0]]]+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 187 | $hourstotal+=$numhours/mysql_num_rows($resultexperts)/$devide;
|
|---|
| 188 | }
|
|---|
| 189 | mysql_free_result($resultexperts);
|
|---|
| 190 | // sh experts
|
|---|
| 191 | if ($starttimestamp->format('Ymd')>20171201)
|
|---|
| 192 | {
|
|---|
| 193 | $devide=6.0;
|
|---|
| 194 | $queryshexperts="SELECT userid FROM memberlist.experts ";
|
|---|
| 195 | $queryshexperts.=" WHERE sh=1 ";
|
|---|
| 196 | $queryshexperts.=" AND start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 197 | $queryshexperts.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 198 | $resultshexperts = mysql_query($queryshexperts);
|
|---|
| 199 | while ($rowshexperts = mysql_fetch_row($resultshexperts))
|
|---|
| 200 | {
|
|---|
| 201 | if (!isset($shexperthours[$rowshexperts[0]]))
|
|---|
| 202 | $shexperthours[$rowshexperts[0]]=0;
|
|---|
| 203 |
|
|---|
| 204 | $shexperthours[$rowshexperts[0]]+=$numhours/mysql_num_rows($resultshexperts)/$devide;
|
|---|
| 205 | $totalhours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultshexperts)/$devide;
|
|---|
| 206 | $instituteshexperthours[$affiliation[$rowshexperts[0]]]+=$numhours/mysql_num_rows($resultshexperts)/$devide;
|
|---|
| 207 | $institutetotalhours[$affiliation[$rowshexperts[0]]]+=$numhours/mysql_num_rows($resultshexperts)/$devide;
|
|---|
| 208 | $hourstotal+=$numhours/mysql_num_rows($resultshexperts)/$devide;
|
|---|
| 209 | }
|
|---|
| 210 | mysql_free_result($resultshexperts);
|
|---|
| 211 | }
|
|---|
| 212 | // flare experts
|
|---|
| 213 | if ($starttimestamp->format('Ymd')>20140318)
|
|---|
| 214 | {
|
|---|
| 215 | $devide=6.0;
|
|---|
| 216 | $queryflareexperts="SELECT userid FROM memberlist.experts ";
|
|---|
| 217 | $queryflareexperts.=" WHERE flare=1 ";
|
|---|
| 218 | $queryflareexperts.=" AND start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 219 | $queryflareexperts.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'";
|
|---|
| 220 | $resultflareexperts = mysql_query($queryflareexperts);
|
|---|
| 221 | while ($rowflareexperts = mysql_fetch_row($resultflareexperts))
|
|---|
| 222 | {
|
|---|
| 223 | if (!isset($flareexperthours[$rowflareexperts[0]]))
|
|---|
| 224 | $flareexperthours[$rowflareexperts[0]]=0;
|
|---|
| 225 |
|
|---|
| 226 | $flareexperthours[$rowflareexperts[0]]+=$numhours/mysql_num_rows($resultflareexperts)/$devide;
|
|---|
| 227 | $totalhours[$rowexperts[0]]+=$numhours/mysql_num_rows($resultflareexperts)/$devide;
|
|---|
| 228 | $instituteflareexperthours[$affiliation[$rowflareexperts[0]]]+=$numhours/mysql_num_rows($resultflareexperts)/$devide;
|
|---|
| 229 | $institutetotalhours[$affiliation[$rowflareexperts[0]]]+=$numhours/mysql_num_rows($resultflareexperts)/$devide;
|
|---|
| 230 | $hourstotal+=$numhours/mysql_num_rows($resultflareexperts)/$devide;
|
|---|
| 231 | }
|
|---|
| 232 | mysql_free_result($resultflareexperts);
|
|---|
| 233 | }
|
|---|
| 234 |
|
|---|
| 235 | date_add($starttimestamp, date_interval_create_from_date_string("1 days"));
|
|---|
| 236 | $counter++;
|
|---|
| 237 | }
|
|---|
| 238 | foreach ($institutes2 as $key=>$value)
|
|---|
| 239 | $institutes2[$key]=$value/$counter;
|
|---|
| 240 | mysql_free_result($resultshare);
|
|---|
| 241 |
|
|---|
| 242 | mysql_close($db_id);
|
|---|
| 243 |
|
|---|
| 244 | echo "<br>\n<br>\n";
|
|---|
| 245 | //echo "<b>Total number of hours:</b> ".$hourstotal2." h (all nights).<br>\n";
|
|---|
| 246 | //echo "<b>Total number of hours:</b> ".$hourstotal3." h (all data taking nights).<br>\n";
|
|---|
| 247 | //echo "<b>Total number of hours:</b> ".$hourstotal." h (all data taking nights incl expert hours).\n";
|
|---|
| 248 | echo "<b>Total number of hours (all duties):</b> ".$hourstotal." h.<br>\n";
|
|---|
| 249 | echo "<b>Total number of hours (shift duties):</b> ".$hourstotal3." h.\n";
|
|---|
| 250 | echo "<table>\n";
|
|---|
| 251 | echo "<tr><td align='center'><b>institute</b></td><td align='center'>hours (shift/debug/expert/flare/sh)</td><td>share required:done->fulfilled</td></tr>\n";
|
|---|
| 252 | foreach ($institutenames as $id=>$name)
|
|---|
| 253 | {
|
|---|
| 254 | //if (!$institutetotalhours[$id])
|
|---|
| 255 | // continue;
|
|---|
| 256 |
|
|---|
| 257 | if ($institutes2[$id]==0)
|
|---|
| 258 | continue;
|
|---|
| 259 |
|
|---|
| 260 | echo "<tr><td>";
|
|---|
| 261 | echo $name;//."(".$id.")";
|
|---|
| 262 | echo "</td><td align='right'>";
|
|---|
| 263 | 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]);
|
|---|
| 264 | echo "</td><td align='right'>";
|
|---|
| 265 | printf("%7.2f%%: %7.2f%% -> %5.2f%%",$institutes2[$id]*100, $institutetotalhours[$id]/$hourstotal*100, ($institutetotalhours[$id]/$hourstotal)/$institutes2[$id]*100);
|
|---|
| 266 | echo "</td></tr>\n";
|
|---|
| 267 | }
|
|---|
| 268 | echo "</table><br>\n";
|
|---|
| 269 |
|
|---|
| 270 | echo "<table>\n";
|
|---|
| 271 | echo "<tr><td align='center'><b>user</b></td><td align='center'>hours(shift/debug/expert/flare/sh)</td></tr>\n";
|
|---|
| 272 | foreach ($users as $name=>$id)
|
|---|
| 273 | {
|
|---|
| 274 | if (!isset($totalhours[$id]) || !$totalhours[$id])
|
|---|
| 275 | continue;
|
|---|
| 276 |
|
|---|
| 277 | if(!isset($totalhours[$id]))
|
|---|
| 278 | $totalhours[$id]=0;
|
|---|
| 279 | if(!isset($debughours[$id]))
|
|---|
| 280 | $debughours[$id]=0;
|
|---|
| 281 | if(!isset($experthours[$id]))
|
|---|
| 282 | $experthours[$id]=0;
|
|---|
| 283 |
|
|---|
| 284 | echo "<tr><td>";
|
|---|
| 285 | echo $name;//." (".$id.")";
|
|---|
| 286 | echo "</td><td align='right'>";
|
|---|
| 287 | 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]);
|
|---|
| 288 | echo "</td></tr>\n";
|
|---|
| 289 | }
|
|---|
| 290 | echo "</table>\n<br>\n";
|
|---|
| 291 | 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.";
|
|---|
| 292 | echo "<br>\n<br>\n";
|
|---|
| 293 |
|
|---|
| 294 | echo "</form>\n";
|
|---|
| 295 | echo "</body>\n";
|
|---|
| 296 | echo "</html>\n";
|
|---|
| 297 |
|
|---|
| 298 | ?>
|
|---|
