Changeset 17602 for trunk/www


Ignore:
Timestamp:
03/04/14 22:26:38 (11 years ago)
Author:
Daniela Dorner
Message:
complete rewriting of the page to take into account people changing institutes
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/www/dch/shifteval.php

    r17601 r17602  
    1717        $stopdate=$_GET["stopdate"];
    1818    else
    19     {
    20         //$stopdate="20130209";
    2119        $stopdate=date("Ymd");
    22     }
    2320    $starttimestamp = new DateTime($startdate);
    2421    date_add($starttimestamp, date_interval_create_from_date_string("12 hours"));
    … …  
    2825    echo "<body>\n";
    2926    echo "<form action='shifteval.php' METHOD='GET'>\n";
    30     echo "shift evaluation from ";
     27    echo "<b>Shift Evaluation</b> from ";
    3128    printf("<input name='startdate' type='text' size='10' maxlength='10' value='%s'> to\n", $startdate);
    3229    printf("<input name='stopdate' type='text' size='10' maxlength='10' value='%s'>\n", $stopdate);
    3330    echo "<input type='submit' value='Show'> \n";
    34     echo "<table>\n";
    3531
    3632    include ("db.php");
    … …  
    3834
    3935    //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
    4144    $users=array();
     45    $shifthours=array();
     46    $debughours=array();
     47    $experthours=array();
     48    $totalhours=array();
    4249    $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;
    5255
    5356    //get username-id connection
    54     $queryusers="SELECT username, uid FROM logbook.users ";
     57    $queryusers="SELECT username, uid FROM logbook.users ORDER BY username";
    5558    $resultusers  = mysql_query($queryusers);
    5659    while ($rowusers = mysql_fetch_row($resultusers))
    … …  
    5861    mysql_free_result($resultusers);
    5962
    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))
     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)
    6972    {
    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
    20984        $queryshare="SELECT instituteid, COUNT(*) FROM memberlist.members ";
    21085        $queryshare.=" WHERE start < '".$starttimestamp->format('Y-m-d H:i:s')."'";
    211         //$queryshare.=" AND stop > '".$stoptimestamp->format('Y-m-d H:i:s')."'";
    21286        $queryshare.=" AND stop > '".$starttimestamp->format('Y-m-d H:i:s')."'";
    21387        $queryshare.=" GROUP BY instituteid";
    214         //echo $queryshare." ";
    215         //echo $starttimestamp->format('Y-m-d H:i:s');
    21688        $resultshare  = mysql_query($queryshare);
    21789        $sumpeople=0;
    … …  
    21991        while ($rowshare = mysql_fetch_row($resultshare))
    22092        {
    221             $institutes[$rowshare[0]]+=$rowshare[1];///mysql_num_rows($resultshare);
     93            $institutes[$rowshare[0]]+=$rowshare[1];
    22294            $sumpeople+=$rowshare[1];
    223             //echo $rowshare[0]."..".$rowshare[1]." ";
    22495        }
    22596        foreach ($institutes as $key=>$value)
    22697            $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
    228159        date_add($starttimestamp, date_interval_create_from_date_string("1 days"));
    229160        $counter++;
    230161    }
    231     //$institutes = array_map( function($val,$factor) { return $val / $factor; }, $institutes, array_fill(0, count($institutes), $counter));
    232162    foreach ($institutes2 as $key=>$value)
    233163        $institutes2[$key]=$value/$counter;
    234     //print_r($institutes2);
    235164    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)
    241175    {
    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";
    244185    }
    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";
    257201
    258202    echo "</form>\n";
Note: See TracChangeset for help on using the changeset viewer.