Changeset 17531 for trunk/www/dch


Ignore:
Timestamp:
01/21/14 01:37:16 (11 years ago)
Author:
Daniela Dorner
Message:
added evaluation of debugging and expert on call time
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/www/dch/shifteval.php

    r15314 r17531  
    3333    $db_id = mysql_connect($host, $user, $pw);
    3434
    35     $querynames="SELECT count(*), u FROM calendar.data ";
     35    $querynames="SELECT count(*), u FROM calendar.Data ";
    3636    $querynames.=" WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') ";
    3737    $querynames.=" AND NOT x=1 ";
    3838    $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;";
     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;";
    4040    $resultnames  = mysql_query($querynames);
    4141    //$resultscales  = mysql_query($queryscales);
    … …  
    4343    {
    4444        $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;";
     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;";
    4646        $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 ";
     47        $querynights.=" (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
    4848        $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 ";
     49        $querynights.=" GROUP BY y,m,d), (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=1 ";
    5050        $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 ";
     51        $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.ObservationTimes ";
    5252        $querynights.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate;
    5353        $resultnights  = mysql_query($querynights);
    … …  
    7070    echo "</table>\n<br>\n";
    7171
    72     echo "Debugging times: <br>\n";
     72    echo "Debugging times: (only after 2013/01, i.e. first 16 months missing) <br>\n";
    7373    echo "<table>\n";
    7474
    75     $querynames="SELECT count(*), u FROM calendar.data ";
     75    $querynames="SELECT count(*), u FROM calendar.Data ";
    7676    $querynames.=" WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') ";
    7777    $querynames.=" AND NOT x=0 ";
    7878    $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;";
     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;";
    8080    $resultnames  = mysql_query($querynames);
    8181    //$resultscales  = mysql_query($queryscales);
    … …  
    8484        $sum=0;
    8585        $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 ";
     86        $querynights.=" (SELECT COUNT(*) FROM calendar.Data WHERE u NOT IN ('ETHZ', 'ISDC', 'UNIDO', 'UNIWUE') AND NOT x=0 ";
    8787        $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 ";
     88        $querynights.=" AND u='".$rownames[1]."' GROUP BY y,m,d) FROM factdata.ObservationTimes ";
    8989        $querynights.=" WHERE fNight>=".$startdate." and fNight<=".$stopdate;
    9090        $resultnights  = mysql_query($querynights);
    … …  
    9999        }
    100100        echo "<tr>\n<td>".$rownames[1]."</td>\n<td>".$sum."</td>\n</tr>";
    101     mysql_free_result($resultnights);
     101        mysql_free_result($resultnights);
    102102    }
    103103    //$sumhours+=$hours*scale
    … …  
    106106    //mysql_free_result($resultscales);
    107107
     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
    108166    mysql_close($db_id);
    109     echo "</table>\n";
    110167
    111168    echo "</form>\n";
Note: See TracChangeset for help on using the changeset viewer.