Changeset 17602 for trunk/www/dch/shifteval.php
- Timestamp:
- 03/04/14 22:26:38 (11 years ago)
- File:
-
- 1 edited
-
trunk/www/dch/shifteval.php (modified) (5 diffs)
Legend:
- Unmodified
- Added
- Removed
-
trunk/www/dch/shifteval.php
r17601 r17602 17 17 $stopdate=$_GET["stopdate"]; 18 18 else 19 {20 //$stopdate="20130209";21 19 $stopdate=date("Ymd"); 22 }23 20 $starttimestamp = new DateTime($startdate); 24 21 date_add($starttimestamp, date_interval_create_from_date_string("12 hours")); … … 28 25 echo "<body>\n"; 29 26 echo "<form action='shifteval.php' METHOD='GET'>\n"; 30 echo " shift evaluationfrom ";27 echo "<b>Shift Evaluation</b> from "; 31 28 printf("<input name='startdate' type='text' size='10' maxlength='10' value='%s'> to\n", $startdate); 32 29 printf("<input name='stopdate' type='text' size='10' maxlength='10' value='%s'>\n", $stopdate); 33 30 echo "<input type='submit' value='Show'> \n"; 34 echo "<table>\n";35 31 36 32 include ("db.php"); … … 38 34 39 35 //some parameters for the evaluation 40 $institutesum= array ("1" => 0, "2" => 0, "3" => 0, "4" => 0, "5" => 0 ); 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 41 44 $users=array(); 45 $shifthours=array(); 46 $debughours=array(); 47 $experthours=array(); 48 $totalhours=array(); 42 49 $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); 50 //$shifthourstotal=0; 51 //$debughourstotal=0; 52 //$experthourstotal=0; 53 $hourstotal=0; 54 $counter=0; 52 55 53 56 //get username-id connection 54 $queryusers="SELECT username, uid FROM logbook.users ";57 $queryusers="SELECT username, uid FROM logbook.users ORDER BY username"; 55 58 $resultusers = mysql_query($queryusers); 56 59 while ($rowusers = mysql_fetch_row($resultusers)) … … 58 61 mysql_free_result($resultusers); 59 62 60 // calculate shift hours61 $query names="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))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) 69 72 { 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 { 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 209 84 $queryshare="SELECT instituteid, COUNT(*) FROM memberlist.members "; 210 85 $queryshare.=" WHERE start < '".$starttimestamp->format('Y-m-d H:i:s')."'"; 211 //$queryshare.=" AND stop > '".$stoptimestamp->format('Y-m-d H:i:s')."'";212 86 $queryshare.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'"; 213 87 $queryshare.=" GROUP BY instituteid"; 214 //echo $queryshare." ";215 //echo $starttimestamp->format('Y-m-d H:i:s');216 88 $resultshare = mysql_query($queryshare); 217 89 $sumpeople=0; … … 219 91 while ($rowshare = mysql_fetch_row($resultshare)) 220 92 { 221 $institutes[$rowshare[0]]+=$rowshare[1]; ///mysql_num_rows($resultshare);93 $institutes[$rowshare[0]]+=$rowshare[1]; 222 94 $sumpeople+=$rowshare[1]; 223 //echo $rowshare[0]."..".$rowshare[1]." ";224 95 } 225 96 foreach ($institutes as $key=>$value) 226 97 $institutes2[$key]+=$value/$sumpeople; 227 //print_r($institutes2); 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 228 159 date_add($starttimestamp, date_interval_create_from_date_string("1 days")); 229 160 $counter++; 230 161 } 231 //$institutes = array_map( function($val,$factor) { return $val / $factor; }, $institutes, array_fill(0, count($institutes), $counter));232 162 foreach ($institutes2 as $key=>$value) 233 163 $institutes2[$key]=$value/$counter; 234 //print_r($institutes2);235 164 mysql_free_result($resultshare); 236 //print_r($affiliation); 237 //print_r($users); 238 //print_r($shifthours); 239 240 foreach ($shifthours as $user=>$hours) 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) 241 175 { 242 $shifthourstotal+=$hours; 243 $institutesum[$affiliation[$user]]+=$hours; 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"; 244 185 } 245 foreach ($institutesum as $key=>$value) 246 $institutesum[$key]=$value/$shifthourstotal; 247 //print_r($institutesum); 248 echo "<br>\n First Evaluation:<br>\n Institute: duty - done<br>\n"; 249 foreach ($institutes2 as $key=>$value) 250 echo $key.": ".$value." - ".$institutesum[$key]."=> ".$institutesum[$key]*100/$value."% <br>"; 251 echo "remark: does not yet take into account institute changes, i.e. only valid from 1.9.2012<br>"; 252 253 echo "--> ".$shifthourstotal; 254 255 256 mysql_close($db_id); 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"; 257 201 258 202 echo "</form>\n";
Note:
See TracChangeset
for help on using the changeset viewer.
