source: trunk/www/dch/shifteval.php@ 19778

Last change on this file since 19778 was 19742, checked in by Daniela Dorner, 5 years ago
added listing of all duties
File size: 13.8 KB
Line 
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?>
Note: See TracBrowser for help on using the repository browser.