| 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 | {
|
|---|
| 20 | $stopdate="20130209";
|
|---|
| 21 | $stopdate=date("Ymd");
|
|---|
| 22 | }
|
|---|
| 23 |
|
|---|
| 24 | echo "<body>\n";
|
|---|
| 25 | echo "<form action='shifteval.php' METHOD='GET'>\n";
|
|---|
| 26 | echo "shift evaluation from ";
|
|---|
| 27 | printf("<input name='startdate' type='text' size='10' maxlength='10' value='%s'> to\n", $startdate);
|
|---|
| 28 | printf("<input name='stopdate' type='text' size='10' maxlength='10' value='%s'>\n", $stopdate);
|
|---|
| 29 | echo "<input type='submit' value='Show'> \n";
|
|---|
| 30 | echo "<table>\n";
|
|---|
| 31 |
|
|---|
| 32 | include ("db.php");
|
|---|
| 33 | $db_id = mysql_connect($host, $user, $pw);
|
|---|
| 34 |
|
|---|
| 35 | $querynames="SELECT count(*), u FROM calendar.Data ";
|
|---|
| 36 | $querynames.=" WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') ";
|
|---|
| 37 | $querynames.=" AND NOT x=1 ";
|
|---|
| 38 | $querynames.=" GROUP BY u ORDER BY u;";
|
|---|
| 39 | //$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;";
|
|---|
| 40 | $resultnames = mysql_query($querynames);
|
|---|
| 41 | //$resultscales = mysql_query($queryscales);
|
|---|
| 42 | while ($rownames = mysql_fetch_row($resultnames))
|
|---|
| 43 | {
|
|---|
| 44 | $sum=0;
|
|---|
| 45 | //$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;";
|
|---|
| 46 | $querynights="SELECT fNight, fStartObservation, fStopObservation, Time_to_Sec(Timediff(fStopObservation, fStartObservatioN))/60./60.+1, ";
|
|---|
| 47 | $querynights.=" (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
|
|---|
| 48 | $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
|
|---|
| 49 | $querynights.=" GROUP BY y,m,d), (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
|
|---|
| 50 | $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
|
|---|
| 51 | $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.ObservationTimes ";
|
|---|
| 52 | $querynights.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate;
|
|---|
| 53 | $resultnights = mysql_query($querynights);
|
|---|
| 54 | while ($rownights = mysql_fetch_row($resultnights))
|
|---|
| 55 | {
|
|---|
| 56 | if ($rownights[5]>0)
|
|---|
| 57 | {
|
|---|
| 58 | //echo $rownights[0]." ".$rownights[1]." ".$rownights[2]." ".$rownights[3]." ".$rownights[4]."<br>";
|
|---|
| 59 | $sum+=$rownights[3]/$rownights[4];
|
|---|
| 60 | }
|
|---|
| 61 | }
|
|---|
| 62 | echo "<tr>\n<td>".$rownames[1]."</td>\n<td>".$sum."</td>\n</tr>";
|
|---|
| 63 | mysql_free_result($resultnights);
|
|---|
| 64 | }
|
|---|
| 65 | //$sumhours+=$hours*scale
|
|---|
| 66 |
|
|---|
| 67 | mysql_free_result($resultnames);
|
|---|
| 68 | //mysql_free_result($resultscales);
|
|---|
| 69 |
|
|---|
| 70 | echo "</table>\n<br>\n";
|
|---|
| 71 |
|
|---|
| 72 | echo "Debugging times: (only after 2013/01, i.e. first 16 months missing) <br>\n";
|
|---|
| 73 | echo "<table>\n";
|
|---|
| 74 |
|
|---|
| 75 | $querynames="SELECT count(*), u FROM calendar.Data ";
|
|---|
| 76 | $querynames.=" WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') ";
|
|---|
| 77 | $querynames.=" AND NOT x=0 ";
|
|---|
| 78 | $querynames.=" GROUP BY u ORDER BY u;";
|
|---|
| 79 | //$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;";
|
|---|
| 80 | $resultnames = mysql_query($querynames);
|
|---|
| 81 | //$resultscales = mysql_query($queryscales);
|
|---|
| 82 | while ($rownames = mysql_fetch_row($resultnames))
|
|---|
| 83 | {
|
|---|
| 84 | $sum=0;
|
|---|
| 85 | $querynights="SELECT fNight, fStartObservation, fStopObservation, Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1, ";
|
|---|
| 86 | $querynights.=" (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=0 ";
|
|---|
| 87 | $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
|
|---|
| 88 | $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.ObservationTimes ";
|
|---|
| 89 | $querynights.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate;
|
|---|
| 90 | $resultnights = mysql_query($querynights);
|
|---|
| 91 | while ($rownights = mysql_fetch_row($resultnights))
|
|---|
| 92 | {
|
|---|
| 93 | if ($rownights[4]>0)
|
|---|
| 94 | {
|
|---|
| 95 | //echo $rownights[0]." ".$rownights[1]." ".$rownights[2]." ".$rownights[3]." ".$rownights[4]."<br>";
|
|---|
| 96 | $sum+=$rownights[3]*0.5;
|
|---|
| 97 | //$sum+=$rownights[3]/$rownights[4];
|
|---|
| 98 | }
|
|---|
| 99 | }
|
|---|
| 100 | echo "<tr>\n<td>".$rownames[1]."</td>\n<td>".$sum."</td>\n</tr>";
|
|---|
| 101 | mysql_free_result($resultnights);
|
|---|
| 102 | }
|
|---|
| 103 | //$sumhours+=$hours*scale
|
|---|
| 104 |
|
|---|
| 105 | mysql_free_result($resultnames);
|
|---|
| 106 | //mysql_free_result($resultscales);
|
|---|
| 107 |
|
|---|
| 108 | echo "</table>\n<br>\n<br>\n";
|
|---|
| 109 |
|
|---|
| 110 | $queryexpert ="SELECT fNight, Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1 FROM factdata.ObservationTimes ";
|
|---|
| 111 | $queryexpert .=" WHERE fNight>=".$startdate." and fNight<=".$stopdate." GROUP BY fNight";
|
|---|
| 112 | $resultexpert = mysql_query($queryexpert);
|
|---|
| 113 | $sumexpert=0;
|
|---|
| 114 | $sumthomas=0;
|
|---|
| 115 | $sumdani=0;
|
|---|
| 116 | $sumjens=0;
|
|---|
| 117 | $sumdom=0;
|
|---|
| 118 | //echo mysql_num_rows($resultexpert)."<br>";
|
|---|
| 119 |
|
|---|
| 120 | //total 1 year: 4000 hours ~10.9h/night -> 46 hours/shift slot
|
|---|
| 121 | //1 shift slot 46 hours
|
|---|
| 122 | // 4000/46 = 87
|
|---|
| 123 | // -> 87 shift slots
|
|---|
| 124 | // expert 6 slots
|
|---|
| 125 | // 87/6 -> 14.5
|
|---|
| 126 | // => expert counts 1/13 of normal time
|
|---|
| 127 | $devide=14.5;
|
|---|
| 128 | while ($rowexpert = mysql_fetch_row($resultexpert))
|
|---|
| 129 | {
|
|---|
| 130 | if ($rowexpert[0]>20130801)
|
|---|
| 131 | {
|
|---|
| 132 | $sumthomas+=$rowexpert[1]/$devide/3;
|
|---|
| 133 | $sumdani+=$rowexpert[1]/$devide/3;
|
|---|
| 134 | $sumjens+=$rowexpert[1]/$devide/3;
|
|---|
| 135 | }
|
|---|
| 136 | else
|
|---|
| 137 | {
|
|---|
| 138 | if ($rowexpert[0]>20130101)
|
|---|
| 139 | {
|
|---|
| 140 | $sumthomas+=$rowexpert[1]/$devide/2;
|
|---|
| 141 | $sumdani+=$rowexpert[1]/$devide/2;
|
|---|
| 142 | }
|
|---|
| 143 | else
|
|---|
| 144 | {
|
|---|
| 145 | $sumthomas+=$rowexpert[1]/$devide/3;
|
|---|
| 146 | $sumdani+=$rowexpert[1]/$devide/3;
|
|---|
| 147 | $sumdom+=$rowexpert[1]/$devide/3;
|
|---|
| 148 | }
|
|---|
| 149 | }
|
|---|
| 150 | $sumexpert+=$rowexpert[1]/$devide;
|
|---|
| 151 | }
|
|---|
| 152 |
|
|---|
| 153 | //echo $sumexpert;
|
|---|
| 154 | echo "Expert-on-Call times: <br>\n";
|
|---|
| 155 | echo "<table>\n";
|
|---|
| 156 | if ($sumthomas)
|
|---|
| 157 | echo "<tr>\n<td>tbretz</td>\n<td>".$sumthomas."</td>\n</tr>";
|
|---|
| 158 | if ($sumdani)
|
|---|
| 159 | echo "<tr>\n<td>dorner</td>\n<td>".$sumdani."</td>\n</tr>";
|
|---|
| 160 | if ($sumjens)
|
|---|
| 161 | echo "<tr>\n<td>jbuss</td>\n<td>".$sumjens."</td>\n</tr>";
|
|---|
| 162 | if ($sumdom)
|
|---|
| 163 | echo "<tr>\n<td>dneise</td>\n<td>".$sumdom."</td>\n</tr>";
|
|---|
| 164 | echo "</table>\n";
|
|---|
| 165 |
|
|---|
| 166 | mysql_close($db_id);
|
|---|
| 167 |
|
|---|
| 168 | echo "</form>\n";
|
|---|
| 169 | echo "</body>\n";
|
|---|
| 170 | echo "</html>\n";
|
|---|
| 171 |
|
|---|
| 172 | ?>
|
|---|
