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

Last change on this file since 17599 was 17599, checked in by Daniela Dorner, 11 years ago
added evaluation of shift duties (so far not taking into account institute changes of members)
File size: 10.7 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 {
20 //$stopdate="20130209";
21 $stopdate=date("Ymd");
22 }
23 $starttimestamp = new DateTime($startdate);
24 date_add($starttimestamp, date_interval_create_from_date_string("12 hours"));
25 $stoptimestamp = new DateTime($stopdate);
26 date_add($stoptimestamp, date_interval_create_from_date_string("12 hours"));
27
28 echo "<body>\n";
29 echo "<form action='shifteval.php' METHOD='GET'>\n";
30 echo "shift evaluation from ";
31 printf("<input name='startdate' type='text' size='10' maxlength='10' value='%s'> to\n", $startdate);
32 printf("<input name='stopdate' type='text' size='10' maxlength='10' value='%s'>\n", $stopdate);
33 echo "<input type='submit' value='Show'> \n";
34 echo "<table>\n";
35
36 include ("db.php");
37 $db_id = mysql_connect($host, $user, $pw);
38
39 //some parameters for the evaluation
40 $institutesum= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
41 $users=array();
42 $affiliation=array();
43 $shifthours=array();
44 $shifthourstotal=0;
45
46 //get affiliation of users
47 $queryaffiliation="SELECT userid, Min(instituteid) FROM memberlist.members GROUP BY userid";
48 $resultaffiliation = mysql_query($queryaffiliation);
49 while ($rowaffiliation = mysql_fetch_row($resultaffiliation))
50 $affiliation[$rowaffiliation[0]]=$rowaffiliation[1];
51 mysql_free_result($resultaffiliation);
52
53 //get username-id connection
54 $queryusers="SELECT username, uid FROM logbook.users ";
55 $resultusers = mysql_query($queryusers);
56 while ($rowusers = mysql_fetch_row($resultusers))
57 $users[$rowusers[0]]=$rowusers[1];
58 mysql_free_result($resultusers);
59
60 //calculate shift hours
61 $querynames="SELECT count(*), u FROM calendar.Data ";
62 $querynames.=" WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') ";
63 $querynames.=" AND NOT x=1 ";
64 $querynames.=" GROUP BY u ORDER BY u;";
65 //$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;";
66 $resultnames = mysql_query($querynames);
67 //$resultscales = mysql_query($queryscales);
68 while ($rownames = mysql_fetch_row($resultnames))
69 {
70 $sum=0;
71 //$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;";
72 $querynights="SELECT fNight, fStartObservation, fStopObservation, Time_to_Sec(Timediff(fStopObservation, fStartObservatioN))/60./60.+1, ";
73 $querynights.=" (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
74 $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
75 $querynights.=" GROUP BY y,m,d), (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
76 $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
77 $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.ObservationTimes ";
78 $querynights.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate;
79 $resultnights = mysql_query($querynights);
80 while ($rownights = mysql_fetch_row($resultnights))
81 {
82 if ($rownights[5]>0)
83 {
84 //echo $rownights[0]." ".$rownights[1]." ".$rownights[2]." ".$rownights[3]." ".$rownights[4]."<br>";
85 $sum+=$rownights[3]/$rownights[4];
86 }
87 }
88 if ($sum>0)
89 {
90 echo "<tr>\n<td>".$rownames[1]."</td>\n<td>".$sum."</td>\n</tr>";
91 $shifthours[$users[$rownames[1]]]+=$sum;
92 }
93 mysql_free_result($resultnights);
94 }
95 //$sumhours+=$hours*scale
96
97 mysql_free_result($resultnames);
98 //mysql_free_result($resultscales);
99
100 echo "</table>\n<br>\n";
101
102 echo "Debugging times: (only after 2013/01, i.e. first 16 months missing) <br>\n";
103 echo "<table>\n";
104
105 $querynames="SELECT count(*), u FROM calendar.Data ";
106 $querynames.=" WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') ";
107 $querynames.=" AND NOT x=0 ";
108 $querynames.=" GROUP BY u ORDER BY u;";
109 //$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;";
110 $resultnames = mysql_query($querynames);
111 //$resultscales = mysql_query($queryscales);
112 while ($rownames = mysql_fetch_row($resultnames))
113 {
114 $sum=0;
115 $querynights="SELECT fNight, fStartObservation, fStopObservation, Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1, ";
116 $querynights.=" (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=0 ";
117 $querynights.=" AND y=DATE_FORMAT(fNight, '%Y') AND m=DATE_FORMAT(fNight, '%m')-1 AND d=DATE_FORMAT(fNight,'%d') ";
118 $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.ObservationTimes ";
119 $querynights.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate;
120 $resultnights = mysql_query($querynights);
121 while ($rownights = mysql_fetch_row($resultnights))
122 {
123 if ($rownights[4]>0)
124 {
125 //echo $rownights[0]." ".$rownights[1]." ".$rownights[2]." ".$rownights[3]." ".$rownights[4]."<br>";
126 $sum+=$rownights[3]*0.5;
127 //$sum+=$rownights[3]/$rownights[4];
128 }
129 }
130 echo "<tr>\n<td>".$rownames[1]."</td>\n<td>".$sum."</td>\n</tr>";
131 $shifthours[$users[$rownames[1]]]+=$sum;
132 mysql_free_result($resultnights);
133 }
134 //$sumhours+=$hours*scale
135
136 mysql_free_result($resultnames);
137 //mysql_free_result($resultscales);
138
139 echo "</table>\n<br>\n<br>\n";
140
141 $queryexpert ="SELECT fNight, Time_to_Sec(Timediff(fStopObservation, fStartObservation))/60./60.+1 FROM factdata.ObservationTimes ";
142 $queryexpert .=" WHERE fNight>=".$startdate." and fNight<=".$stopdate." GROUP BY fNight";
143 $resultexpert = mysql_query($queryexpert);
144 $sumexpert=0;
145 $sumthomas=0;
146 $sumdani=0;
147 $sumjens=0;
148 $sumdom=0;
149 //echo mysql_num_rows($resultexpert)."<br>";
150
151 //total 1 year: 4000 hours ~10.9h/night -> 46 hours/shift slot
152 //1 shift slot 46 hours
153 // 4000/46 = 87
154 // -> 87 shift slots
155 // expert 6 slots
156 // 87/6 -> 14.5
157 // => expert counts 1/13 of normal time
158 $devide=14.5;
159 while ($rowexpert = mysql_fetch_row($resultexpert))
160 {
161 if ($rowexpert[0]>20130801)
162 {
163 $sumthomas+=$rowexpert[1]/$devide/3;
164 $sumdani+=$rowexpert[1]/$devide/3;
165 $sumjens+=$rowexpert[1]/$devide/3;
166 }
167 else
168 {
169 if ($rowexpert[0]>20130101)
170 {
171 $sumthomas+=$rowexpert[1]/$devide/2;
172 $sumdani+=$rowexpert[1]/$devide/2;
173 }
174 else
175 {
176 $sumthomas+=$rowexpert[1]/$devide/3;
177 $sumdani+=$rowexpert[1]/$devide/3;
178 $sumdom+=$rowexpert[1]/$devide/3;
179 }
180 }
181 $sumexpert+=$rowexpert[1]/$devide;
182 }
183 $shifthours[2]+=$sumdani;
184 $shifthours[3]+=$sumthomas;
185 $shifthours[7]+=$sumjens;
186 $shifthours[11]+=$sumdom;
187
188 //echo $sumexpert;
189 echo "Expert-on-Call times: <br>\n";
190 echo "<table>\n";
191 if ($sumthomas)
192 echo "<tr>\n<td>tbretz</td>\n<td>".$sumthomas."</td>\n</tr>";
193 if ($sumdani)
194 echo "<tr>\n<td>dorner</td>\n<td>".$sumdani."</td>\n</tr>";
195 if ($sumjens)
196 echo "<tr>\n<td>jbuss</td>\n<td>".$sumjens."</td>\n</tr>";
197 if ($sumdom)
198 echo "<tr>\n<td>dneise</td>\n<td>".$sumdom."</td>\n</tr>";
199 echo "</table>\n";
200
201
202 //calculating the shares of the institutes according to the member list
203 $counter=0;
204 $institutes2= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
205 print_r($institutes);
206
207 while ($starttimestamp<$stoptimestamp)
208 {
209 $queryshare="SELECT instituteid, COUNT(*) FROM memberlist.members ";
210 $queryshare.=" WHERE start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
211 $queryshare.=" AND stop > '".$stoptimestamp->format('Y-m-d H:i:s')."'";
212 $queryshare.=" GROUP BY instituteid";
213 //echo $queryshare." ";
214 //echo $starttimestamp->format('Y-m-d H:i:s');
215 $resultshare = mysql_query($queryshare);
216 $sumpeople=0;
217 $institutes= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 );
218 while ($rowshare = mysql_fetch_row($resultshare))
219 {
220 $institutes[$rowshare[0]]+=$rowshare[1];///mysql_num_rows($resultshare);
221 $sumpeople+=$rowshare[1];
222 //echo $rowshare[0]."..".$rowshare[1]." ";
223 }
224 foreach ($institutes as $key=>$value)
225 $institutes2[$key]+=$value/$sumpeople;
226 //print_r($institutes2);
227 date_add($starttimestamp, date_interval_create_from_date_string("1 days"));
228 $counter++;
229 }
230 //$institutes = array_map( function($val,$factor) { return $val / $factor; }, $institutes, array_fill(0, count($institutes), $counter));
231 foreach ($institutes2 as $key=>$value)
232 $institutes2[$key]=$value/$counter;
233 //print_r($institutes2);
234 mysql_free_result($resultshare);
235 //print_r($affiliation);
236 //print_r($users);
237 //print_r($shifthours);
238
239 foreach ($shifthours as $user=>$hours)
240 {
241 $shifthourstotal+=$hours;
242 $institutesum[$affiliation[$user]]+=$hours;
243 }
244 foreach ($institutesum as $key=>$value)
245 $institutesum[$key]=$value/$shifthourstotal;
246 //print_r($institutesum);
247 echo "<br>\n First Evaluation:<br>\n Institute: duty - done<br>\n";
248 foreach ($institutes2 as $key=>$value)
249 echo $key.": ".$value." - ".$institutesum[$key]."=> ".$institutesum[$key]*100/$value."% <br>";
250 echo "remark: does not yet take into account institute changes, i.e. only valid from 1.9.2012<br>";
251
252
253
254 mysql_close($db_id);
255
256 echo "</form>\n";
257 echo "</body>\n";
258 echo "</html>\n";
259
260?>
Note: See TracBrowser for help on using the repository browser.