source: trunk/www/dch/datataking_efficiency.php@ 19598

<?php

    include("db.php");

 //   ini_set("display_errors", "On");
 //   ini_set("mysql.trace_mode", "On");


$db_id = mysql_connect($host, $user, $pw);
if ($db_id==FALSE)
{
    printf("mysql_connect returned the following error: %s\n", mysql_error());
    die("");
}
mysql_select_db($db);

// --------------------------------------------------------------------

 //0: night
 //1: astronomical twilight range
 //2: time when data was taken
 //3: runtime all runs
 //4: runtime data runs

$query = <<<EOT

SELECT
   fNight AS '0',
   TIME_TO_SEC(TIMEDIFF(fStopObservation, fStartObservation)) AS '1',
   TIME_TO_SEC(TIMEDIFF(MAX(fRunStop),MIN(fRunStart))) AS '2',
   SUM(IF (TIMEDIFF(fRunStop,fRunStart)<'00:30:00', TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart)), 0)) AS '3',
   SUM(IF (TIMEDIFF(fRunStop,fRunStart)<'00:30:00' AND fRunTypeKey=1, TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart)), 0)) AS '4'
FROM ObservationTimes
LEFT JOIN RunInfo USING(fNight)
WHERE fNight<DATE_FORMAT(DATE(NOW()), '%Y%m%d')
GROUP BY fNight
ORDER BY fNight

EOT;

// --------------------------------------------------------------------

$var1 = "";
$debug = "";
$total = array();
$total["All"] = array();

$result = mysql_query($query, $db_id);
if ($result)
{
    $counter = 0;
    while ($row = mysql_fetch_assoc($result))
    {
        $counter++;
        if (empty($row['1']))
           continue;

        $year = substr($row['0'], 0, 4);
        $date = substr($row['0'], 0, 4)."-".substr($row['0'], 4, 2)."-".substr($row['0'], 6, 2);

        //1: astronomical twilight range
        //2: time when data was taken
        //3: runtime all runs
        //4: runtime data runs

        $v1 = empty($row['1']) ? 0 : $row['1'];
        $v2 = empty($row['2']) ? 0 : $row['2'];
        $v3 = empty($row['3']) ? 0 : $row['3'];
        $v4 = empty($row['4']) ? 0 : $row['4'];

        if ($v3<0)
        {
            $v3 = 0;

            $debug .= "[3]< 0 : ".print_r($row, true)."<br>";
        }

        if ($v2<0)
        {
            $v2 = 0;

            $debug .= "[2]< 0 : ".print_r($row, true)."<br>";
        }

        if ($v4<0)
        {
            $v4 = 0;

            $debug .= "[4]< 0 : ".print_r($row, true)."<br>";
        }

        // Last minus first run can easily be larger than astronomical twilight
        if ($v2>$v1)
            $v2 = $v1;

        if ($v3>$v2)
        {
            $debug .= "[3]>[2]: ".print_r($row, true)."<br>";

            $v3 = $v2;
        }

        if ($v4>$v3)
        {
            $debug .= "[4]>[3]: ".print_r($row, true)."<br>";

            $v4 = $v3;
        }

        $v4 /= 3600;
        $v3 /= 3600;
        $v2 /= 3600;
        $v1 /= 3600;

        $val4 = $v4;
        $val3 = $v3-$v4;
        $val2 = $v2-$v3;
        $val1 = $v1-$v2;

        if (!isset($total[$year]))
           $total[$year] = array();

        $total["All"]["Twilight"]      += $val1;
        $total["All"]["First-to-last"] += $val2;
        $total["All"]["All runs"]      += $val3;
        $total["All"]["Data runs"]     += $val4;

        $total[$year]["Twilight"]      += $val1;
        $total[$year]["First-to-last"] += $val2;
        $total[$year]["All runs"]      += $val3;
        $total[$year]["Data runs"]     += $val4;

        $var1 .= "\n[";
        $var1 .= "new Date('".$date."'),";
        $var1 .= $val4.",";
        $var1 .= $val3.",";
        $var1 .= $val2.",";
        $var1 .= $val1.",";
        //$var1 .= "'lightgray', ";
        $var1 .= "],";
    }

    mysql_free_result($result);
}
//else
//    $table0 = "[empty:".mysql_error()."]\n";


// --------------------------------------------------------------------

$var2 = "";
foreach($total as $key => $val)
{
    $var2 .= "\n['".$key."',";
    $var2 .= $total[$key]["Data runs"].",";
    $var2 .= $total[$key]["All runs"].",";
    $var2 .= $total[$key]["First-to-last"].",";
    $var2 .= $total[$key]["Twilight"].",";
    $var2 .= "],";
}

// --------------------------------------------------------------------

$querycol = colorize($query);

// --------------------------------------------------------------------

echo <<<EOT

<!DOCTYPE HTML>
<html>
<head>
<script src="https://code.jquery.com/jquery-2.2.1.min.js" integrity="sha256-gvQgAFzTH6trSrAWoH1iPo9Xc96QxSZ3feW6kem+O00=" crossorigin="anonymous"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    google.charts.load('current', {packages: ['corechart', 'bar']});
    google.charts.setOnLoadCallback(drawAxisTickColors);

    function drawAxisTickColors()
    {
      var data1 = new google.visualization.DataTable();
      data1.addColumn('date', 'Date');
      data1.addColumn('number', 'Data runs');
      data1.addColumn('number', 'All runs');
      data1.addColumn('number', 'First-to-last');
      data1.addColumn('number', 'Twilight');
      data1.addRows([$var1]);

      var data2 = new google.visualization.DataTable();
      //data2.addColumn('date', 'Date');
      data2.addColumn('string', 'Date');
      data2.addColumn('number', 'Data runs');
      data2.addColumn('number', 'All runs');
      data2.addColumn('number', 'First-to-last');
      data2.addColumn('number', 'Twilight');
      data2.addRows([$var2]);

      var options1 = {
        title: "Datataking time",
              //subtitle:
          height: 400,
          width:'90%',
        hAxis: {
                title: 'Date',
        },
        vAxis: {
            title: 'Datataking time in hours',
            minValue: 0,
            gridlines: {count:-1}, minorGridlines: {count:1}
        },
        legend: { position: 'top', maxLines: 1 },
        bar:    { groupWidth: '100%' },
        isStacked: true, 
       chartArea: {left:60,top:40,bottom:40,width:'90%'},
        explorer: {keepInBound:true,actions:['dragToZoom','rightClickToReset']}
      };

      var options2 = {
        title: "Datataking time",
              //subtitle:
          height: 400,
          width:500,
        hAxis: {
                title: 'Year',
        },
        vAxis: {
            title: 'Datataking time in hours',
            minValue: 0,
            gridlines: {count:-1}, minorGridlines: {count:1}
        },
        legend: { position: 'top', maxLines: 1 },
        bar:    { groupWidth: '90%' },
        isStacked: true,
        dataOpacity: 0.9,
        chartArea: {left:60,top:40,bottom:40,width:'90%'},
        explorer: {keepInBound:true,actions:['dragToZoom','rightClickToReset']}
      };

      var chart1 = new google.visualization.ColumnChart(document.getElementById('columnchart1_values'));
      chart1.draw(data1, options1);
      document.getElementById('print1_values').innerHTML = '<a href="' + chart1.getImageURI() + '">Print</a>';

      var chart2 = new google.visualization.ColumnChart(document.getElementById('columnchart2_values'));
      chart2.draw(data2, options2);
      document.getElementById('print2_values').innerHTML = '<a href="' + chart2.getImageURI() + '">Print</a>';

      options1.title = 'Data taking efficiency relative to astronomical twilight';
      options1.isStacked = 'percent';
      options1.vAxis.title = 'Data taking efficiency';

      options2.title = 'Data taking efficiency relative to astronomical twilight';
      options2.isStacked = 'percent';
      options2.vAxis.title = 'Data taking efficiency';

      var chart3 = new google.visualization.ColumnChart(document.getElementById('columnchart1_efficiency'));
      chart3.draw(data1, options1);
      document.getElementById('print1_efficiency').innerHTML = '<a href="' + chart3.getImageURI() + '">Print</a>';

      var chart4 = new google.visualization.ColumnChart(document.getElementById('columnchart2_efficiency'));
      chart4.draw(data2, options2);
      document.getElementById('print2_efficiency').innerHTML = '<a href="' + chart4.getImageURI() + '">Print</a>';

      console.log("Google.chart.ready");
    }
</script>
<style>
dl {
    //border: 3px double #ccc;
    padding: 0.5em;
  }
  dt {
    float: left;
    clear: left;
    width: 130px;
    text-align: right;
    font-weight: bold;
    color: green;
  }
  dt:after {
    content: ":";
  }
  dd {
    margin: 0 0 0 140px;
    padding: 0 0 1em 0;
  }
</style>
<body>

<div style='position:relative'>
<div id='columnchart1_values'>Preparing charts... this may take a moment.</div>
<div id='print1_values' style='position:absolute;top:10px;right:40px;'></div>
</div>

<p>

<div style='position:relative'>
<div id='columnchart1_efficiency'></div>
<div id='print1_efficiency' style='position:absolute;top:10px;right:40px;'></div>
</div>

<hr>

<div style='position:relative'>
<div id='columnchart2_values'></div>
<div id='print2_values' style='position:absolute;top:10px;right:40px;'></div>
</div>

<p>

<div style='position:relative'>
<div id='columnchart2_efficiency'></div>
<div id='print2_efficiency' style='position:absolute;top:10px;right:40px;'></div>
</div>

<hr>

<input type="button" onclick="$('#spoiler0').toggle(300);" value="Legend"/>
<div id="spoiler0" style="display:none">
<dl>
  <dt>Data runs</dt>
  <dd>The sum of the data taking time of all data runs of the night with
      less than 30min calculated as the difference between the time stamp
      of closing and opening time of the run.</dd>

  <dt>All runs</dt>
  <dd>Same as <b>Onle data runs</b> but including all other run types such
      as pedestal, drs calibration etc., which have a source name set. This
      is usually the case if the telescope is tracking a source during the
      run. This does not include times as repositioning times when the
      no data acquisition was active.</dd>

  <dt>First-to-last</dt>
  <dd>This is the time difference between the end time of the last run of
      the night and the start time of the first run of the night. This
      corresponds roughly to the time the telescope is operated and is
      a measure whether startup and shutdown was properly scheduled.</dd>

  <dt>Twilight</dt>
  <dd>This is the time of astronomical twilight which - in the ideal case -
      corresponds to the maximum data taking time.</dd>
 </dl>

 On average 42% (10h) of a night is twilight. So a data taking efficiency
 of 50% twlight means a total duty cycle of 21%.

</div>

<hr>

<input type="button" onclick="$('#spoiler3').toggle(300);" value="SQL Query"/>
<div id="spoiler3" style="display:none">
<pre>
$querycol
</pre>
</div>

<hr>

<input type="button" onclick="$('#spoiler4').toggle(300);" value="Warnings"/>
<div id="spoiler4" style="display:none">
$debug
</div>

</body>
</html>
EOT;

?>