<?php
///////////////////////////////////////////////////////
//
// There are 3 types of classes used in this program: 
//  1) _DataSource subclasses, which get data from SQL and formatted it for JS
//  2) _Chart subclasses, which handle writing the JS and HTML to display each of the charts
//  3) Request, which generates the whole page.
//
///////////////////////////////////////////////////////
 
date_default_timezone_set('America/Los_Angeles');
define('OLDEST', 2008);
define('THIS_YEAR', date("Y", time()));
define('LAST_YEAR', THIS_YEAR - 1);
 
include getenv('SQL_INFO_FILE');
 
// debugging from command line
if (sizeof($argv) > 1) {
  function member_value($key, $value) { return "$key => $value"; }
  echo "\n\n********************************\n**\n** Starting from command line\n**\n** args:\n";
  parse_str(implode('&', array_slice($argv, 1)), $_GET);
  foreach ($_GET as $var => $got) {
    if (is_array($got)) 
      $got = '{' . implode(', ', array_map('member_value', array_keys($got), array_values($got))) . '}';
    echo "**     $var => $got\n";
  }
  echo "**\n********************************\n\n";
  error_reporting(E_ALL | E_STRICT);
  ini_set('display_errors',1);
  ini_set('html_errors', 1);
 }
 
// minimum positive member of array (ignore 0's)
function minpos($arr) { $a = array_diff($arr, [0]); return ($a) ? min($a) : 0; }
 
///////////////////////////////////////////////////////
//
// _DataSource and its subclasses
//
///////////////////////////////////////////////////////
 
class _DataSource {
  private static $_data = '';
  public $js_variable;
 
  protected function raw_data($request) {
    if (!_DataSource::$_data) {
      $sql = new mysqli(DBHOST, DBUSER, DBPASSWORD, DB, DBSOCKET);
      if ($sql->connect_error) {
        //return 'SQL connection error ' . $sql->connect_errno . ': ' . $sql->connect_error;
        return _DataSource::$_data;
      }
      $query = "SELECT day, hour, avg FROM airQuality "
        . "WHERE city=$request->city AND day >= '$request->year-$request->month-1' AND day < '$request->endyear-$request->endmonth-1' "
        . "ORDER BY day, hour";
      $result = $sql->query($query);
      if ($result) {
        while (($row = $result->fetch_row()))
          _DataSource::$_data[] = $row;
        $result->free();
      }
      // final entry to make foreach loops get the last entry
      _DataSource::$_data[] = ['xxxx-xx-xx', 0, 0];
    }
    return _DataSource::$_data;
  }
 
  public function send_csv($request) {
    $sql = new mysqli(DBHOST, DBUSER, DBPASSWORD, DB, DBSOCKET);
    if ($sql->connect_error) 
      return 'Failed to get connection to DB';
    $query = "SELECT * FROM airQuality "
      . "WHERE city=$request->city AND day >= '$request->year-$request->month-1' AND day < '$request->endyear-$request->endmonth-1' "
      . "ORDER BY day, hour";
    $result = $sql->query($query);
    if (!$result)
      $retstatus = 'query of db failed';
    else {
      $num_fields = $result->field_count;
      $headers = array(); 
      for ($i = 0; $i < $num_fields; $i++) 
        $headers[] = $result->fetch_field_direct($i)->name;
      $fp = fopen('php://output', 'w');
      if (!$fp) 
        $retstatus = 'Could not open output stream';
      else {
        $filename = 'AQI-' . $request->city() . "-$request->year-$request->month--$request->endyear-$request->endmonth.csv";
        header('Content-Type: text/csv');
        header("Content-Disposition: attachment; filename='$filename'");
        header('Pragma: no-cache');    
        header('Expires: 0');
        fputcsv($fp, $headers); 
        while ($row = $result->fetch_row())
          fputcsv($fp, array_values($row)); 
        $retstatus = 'data exported';
      }
    }
    $sql->close();
    return $retstatus;
  }
}
  
// writes an entry for each day in the range giving max, average, and
// minimum values. Missing dates are filled with all 0 values.
class DailyData extends _DataSource {
  protected static $done = 0;
  public function __construct() { $this->js_variable = 'd_daily'; }
 
  public function js_data($request) {
    if (!DailyData::$done) {
      $data = $this->raw_data($request);
      $start_stamp = strtotime("$request->month/1/$request->year");
      $end_stamp = strtotime("$request->endmonth/1/$request->endyear");
      $stats = array_fill(0, floor(($end_stamp - $start_stamp)/(60*60*24)), '[,,,0]');
      
      $current_date = '';
      foreach ($data as $row) {
        if ($row[0] != $current_date) {
          if ($current_date) {
            $ptr = (strtotime($current_date) - $start_stamp)/(60*60*24);
            $avg = round($total/$count, 1);
            $stats[$ptr] = "[,$max,$avg,$min]";
          }
          $max = $min = $count = $total = 0;
          $current_date = $row[0];
        }
        if (!$min || ($row[2] < $min)) $min = $row[2];
        if ($row[2] > $max) $max = $row[2];
        $total += $row[2];
        $count++;
      }
      echo "$this->js_variable = [" . implode(',', $stats) . "];\n";
      DailyData::$done = 1;
    }
  }
}
 
// writes a one-dimensional array giving the readings for each hour in the
// range. Missing entries are padded with 0's
class HourlyData extends _DataSource {
  protected static $done = 0;
  public function __construct() { $this->js_variable = 'd_hourly'; }
 
  public function js_data($request) {
    if (!HourlyData::$done) {
      $data = $this->raw_data($request);
      $start_stamp = strtotime("$request->month/1/$request->year");
      $end_stamp = strtotime("$request->endmonth/1/$request->endyear");
      $stats = array_fill(0, floor(($end_stamp - $start_stamp)/(60*60)), 0);
      
      // error check
      $current_date = '';
      $hour = $ptr = 0;
      foreach ($data as $row) {
        $stamp = strtotime("${row[0]} ${row[1]}:00");
        $hours = floor(($stamp - $start_stamp + 1)/(60*60));
        $stats[$hours] = $row[2];
      }
      echo "$this->js_variable = [" . implode(',', $stats) . "];\n";
      HourlyData::$done = 1;
    }
  }
}
  
// writes a monthy summary for each month in the range. Output is defined in
// make_month(). Missing months are not added
class MonthlyData extends _DataSource {
  protected static $done = 0;
  public function __construct() { $this->js_variable = 'd_monthly'; }
 
  private function make_month($month, $days) {
    $max = max(array_map('max', $days));
    $min = minpos(array_map('minpos', $days));
    $lohi = minpos(array_map('max', $days));
    $hilo = max(array_map('minpos', $days));
    $flattened = call_user_func_array('array_merge', $days);
    $average = round(array_sum($flattened)/sizeof($flattened), 1);
    $stamp0 = strtotime("$month-1");
    $stamp1 = strtotime('+1 month', $stamp0);
    $expected = ($stamp1 - $stamp0)/3600;
    $missing = $expected - sizeof($flattened);
    $ret = "['$month',$max,$average,$min,$hilo,$lohi,$missing]";
    return $ret;
  }
 
  public function js_data($request) {
    if (!MonthlyData::$done) {
      $data = $this->raw_data($request);
      $stats = [];
      $days = [];
      $day = [];
      $current_month = $current_day = 0;
      foreach ($data as $row) {
        $d = explode('-', $row[0]);
        if ($current_day != $d[2]) {
          $current_day = $d[2];
          if ($day)
            $days[] = $day;
          $day = [];
        }
        if ($current_month != "${d[0]}-${d[1]}") {
          if ($days) {
            $stats[] = $this->make_month($current_month, $days);
            $days = [];
          }
          $current_month = "${d[0]}-${d[1]}";
          $current_day = -1;
        }
        $day[] = $row[2];
      }
      echo "$this->js_variable = [" . implode(',', $stats) . "];\n";
      MonthlyData::$done = 1;
    }
  }
}
 
// writes a list of all streaks meeting the requirements of the request.
class ThresholdData extends _DataSource {
  protected static $done = 0;
  public function __construct() { $this->js_variable = 'd_thresh'; }
 
  private function process_streak($streak) {
    $readings = sizeof($streak);
    $last = $streak[$readings - 1];
    $first = $streak[0];
    $hours = (strtotime("${last[0]} ${last[1]}:00") - strtotime("${first[0]} ${first[1]}:00"))/3600 + 1;
    $max = $min = 0;
    foreach ($streak as $row) {
      if ($row[2] > $max) $max = $row[2];
      if (!$min || ($row[2] < $min)) $min = $row[2];
    }
    return "['${first[0]}',${first[1]},$hours,$readings,$max,$min]";
  }
 
  public function js_data($request) {
    if (!ThresholdData::$done) {
      $data = $this->raw_data($request);
      $table = [];
      $streak = [];
      foreach ($data as $row) {
        if ($row[2] && (($row[2] >= $request->threshold) == $request->dir)) {
          $streak[] = $row;
        }
        else if ($streak) {
          $table[] = $this->process_streak($streak);
          $streak = [];
        }
      }
      $this->process_streak($table, $streak);
      echo "$this->js_variable = [" . implode(',', $table) . "];\n";
      ThresholdData::$done = 1;
    }
  }
}
  
///////////////////////////////////////////////////////
//
// _Chart and its subclasses
//
///////////////////////////////////////////////////////
 
class _Chart {
  protected static $id_counter = 0;
  private static $data = '';
  protected $name, $desc, $js_func, $checked;
  protected $extra = '';   // filled in by subclasses which need it
  protected $id = '';
  protected $dataobj;
  protected function __construct($checked, $name, $desc, $js_func) {
    list($this->name, $this->desc, $this->js_func) = [$name, $desc, $js_func];
    $this->checked = ($checked) ? ' checked' : '';
  }
 
  public function js($request, $months) {
    $this->dataobj->js_data($request);
    $this->id = $this->get_id();
    $m = $request->month - 1;
    $city = $request->city();
    $js_var = $this->dataobj->js_variable;
    echo "google.charts.setOnLoadCallback(function () {draw_chart($this->js_func, '$city', $request->year, $m, $months, $js_var, '$this->id'); });\n";
  }
 
  public function html($full, $seq) {
    $desc = ($full) ? $this->desc : '';
    echo "&nbsp;&nbsp;&nbsp; <input type='checkbox' name='charts[]' value='$seq'$this->checked/> <b>$this->name</b> $this->extra $desc<br />\n";
  }
 
  public function chart_div($city) {
    $subid = $this->id . '_a';
    echo "    <div id='$this->id'> Building $city $this->name chart...</div>\n<div id='$subid'></div>\n";
  }
 
  protected function get_id() { return 'chart' . _Chart::$id_counter++; }
    
}
 
 
 
 
class SummaryChart extends _Chart {
  public function __construct($selected, $request) {
    parent::__construct($selected, 'Summary',
                        'Shows the range for all readings, plotted by month',
                        'summary_chart');
    $this->dataobj = new HourlyData();
  }
}
 
class DailyChart extends _Chart {
  public function __construct($selected, $request) {
    parent::__construct($selected, 'Daily',
                        'Plots the high, average, and low readings for each day in the range',
                        'daily_chart');
    $this->dataobj = new DailyData();
  }
}
  
class GreenChart extends _Chart {
  public function __construct($selected, $request) {
    parent::__construct($selected, 'Green air',
                        'Shows the range of the highest reading of each day',
                        'green_chart');
    $this->dataobj = new DailyData();
  }
}
  
class ExtremeChart extends _Chart {
  public function __construct($selected, $request) {
    parent::__construct($selected, 'Lowest high/highest low',
                        'Shows the monthly highest low and lowest high to tell good and bad days',
                        'extremes_chart');
    $this->dataobj = new MonthlyData();
  }
}
  
class TableChart extends _Chart {
  public function __construct($selected, $request) {
    parent::__construct($selected, 'Ratings table',
                        'Ranks months according to various criteria',
                        'table_chart');
    $this->dataobj = new MonthlyData();
  }
}
 
class StreakChart extends _Chart {
  public function __construct($selected, $request) {
    if ($request->dir) { $over = 'checked'; $under = ''; }
    else { $over = ''; $under = ' checked'; }
    $threshold = $request->threshold;
    $dir = $request->dir;
    $this->extra = "Over <input type='radio' name='dir' value='1'$over> 
Under <input type='radio' name='dir' value='0'$under>
<input type='number' name='threshold' min=0 max='1000' value='$threshold'; ?>";
      parent::__construct($selected, 'Streaks',
                          'Finds consecutive readings over or under a threshold',
                          'thresh_chart');
    $this->dataobj = new ThresholdData();
  }
}
 
///////////////////////////////////////////////////////
//
// Request is the class that runs the whole show. It fetches and stores
// arguments passed to the invocation and uses them to call the requested
// chart objects to insert javascript and html. Only the very outermost html
// is inserted inline at the end of the code.
//
///////////////////////////////////////////////////////
class Request {
  static $CITIES = ['Beijing', 'Chengdu', 'Guangzhou', 'Shanghai', 'Shenyang'];
  static $CHARTS = ['DailyChart', 'SummaryChart', 'GreenChart', 'ExtremeChart', 'TableChart', 'StreakChart'];
  var $end_year, $end_month;
  private $chartobjs;
  // default values of the arguments
  private $ARGS = ['city' => 0,
                   'year' => LAST_YEAR,
                   'month' => 1,
                   'years' => 1,
                   'months' => 0,
                   'threshold' => 500,
                   'dir' => 1,
                   'charts' => []];
 
  // magic function to fetch the parameters
  public function __get($name) { return (isset($this->ARGS[$name])) ? $this->ARGS[$name] : ''; }
 
  function __construct() {
    $this->ARGS = array_merge($this->ARGS, $_GET);
    $this->endyear = $this->year + $this->years;
    $this->endmonth = $this->month + $this->months;
    if ($this->endmonth > 12) {
      $this->endmonth--;
      $this->endyear += floor($this->endmonth/12);
      $this->endmonth = $this->endmonth%12 + 1;
    }
    $this->chartobjs = [];
    for ($chart = 0; $chart < sizeof(Request::$CHARTS); $chart++)
      $this->chartobjs[] = new Request::$CHARTS[$chart](in_array($chart, $this->charts), $this);
  }
 
  public function city($i=-1) { return Request::$CITIES[($i < 0) ? $this->city : $i]; }
 
  function month_dropdown() {
    $ret = "<select name='month'>\n";
    for ($i = 1; $i <= 12; $i++) {
      $month = date("F", mktime(null, null, null, $i, 10));
      $selected = ($this->month == $i) ? " selected='selected'" : '';
      $ret .= "    <option value='$i'$selected>$month</option>\n";
    }
    return $ret . "  </select>\n";
  }
  
  function years_dropdown() {
    $string = "<select name='year'>\n";
    for ($year = THIS_YEAR; $year >= OLDEST; $year--) {
      $selected = ($this->year == $year) ? " selected='selected'" : '';
      $string .= "<option value='$year'$selected>$year</option>\n";
    }
    return $string . "</select>\n";
  }
 
  function city_dropdown() {
    $string = "<select name='city'>\n";
    for ($i = 0; $i < sizeof(Request::$CITIES); $i++) {
      $selected = ($this->city == $i) ? " selected='selected'" : '';
      $city = $this->city($i);
      $string .= "<option value='$i'$selected>$city</option>\n";
    }
    return $string . "</select>\n";
  }
 
  private function js_setup() {
    $months = ($this->endyear - $this->year)*12 + $this->endmonth - $this->month;
      ?>
      <!--Load the AJAX API-->
        <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
        <script type="text/javascript" src="charts.js"></script>
        <script type="text/javascript">
        city = '<?php echo $this->city(); ?>';
      start_date = '<?php echo "$this->year-$this->month-1"; ?>';
      months = <?php echo $months; ?>;
      <?php
    }
 
  private function js_finish() { echo "</script>\n"; }
 
  public function html() {
    $i = 0;
    foreach ($this->chartobjs as $chart)
      $chart->html(!$this->charts, $i++);
  }
 
  public function js() {
    if ($this->charts) {
      $months = $this->years*12 + $this->months;
      $this->js_setup($months);
      foreach ($this->charts as $chart)
        $this->chartobjs[$chart]->js($this, $months);
      $this->js_finish();
    }
  }
 
  public function chart_divs() {
    foreach ($this->charts as $chart)
      $this->chartobjs[$chart]->chart_div($this->city());
  }    
}
 
  $status = '';
if ($_GET['action'] == 'Clear')
    $_GET = [];
$request = new Request();
if ($_GET['action'] == 'Export CSV') {
  $data = new _DataSource();
  $status = $data->send_csv($request);
  exit(0);
 }
  
?>
 
<html>
  <head>
    <link rel='shortcut icon' href='favicon.ico' type='image/x-icon'>
    <link rel='shortcut icon' href='animated.gif' type='image/x-icon'>
    <title>AQI History Charts</title>
   <?php $request->js(); ?>
  </head>
  <body>
  <b><?php echo $status; ?></b>
    <h3>AQI History Charts</h3>
<?php if (!$request->charts) { ?>
  Since 2008 the US Embassy in Beijing has been publishing hourly PM2.5 and AQI levels. In 2013,
 after protesting it proved not to work and increasingly vocal Chinese popular opinion pushed
 for it, the Chinese government also started publishing their own statistics. It is a foregone
 conclusion that Xinhua reporting will show things getting better. This site lets you review the
 historic figures to check if it is really true. (spoiler alert: for 2015 it certainly is).
   <p />For the last couple years at the end of the year I compiled data for the previous years into
 a <a href="year.php">subsite</a>, but that is a lot of work, and answering some questions only
 brings up more. So, this page will let people generate their own charts to get their own answers.
   Charts you generate can be bookmarked and sent to others.
   <p />
 Any suggestions for other chart types can be considered if you <a href='mail.php'>send me mail</a>. 
  The <a href='colorize.php?file=charts.phps'>PHP source</a> and the <a href='colorize.php?file=charts.js'>
  Javascript code</a> are available if anyone is interested, and <a href='https://developers.google.com/chart/'>Google Charts</a>
  is used for the graphing.
<p /><a href='index.php'>Go to airquality reporting page</a>
 
 <?php } else echo "<b /><a href='mail.php'>send mail</a><br /><a href='index.php'>Go to airquality reporting page</a><p />"; ?>
    <form action='charts.php' method='GET'>
       Get <input type='number' name='years' min='0' value='<?php echo $request->years; ?>' /> years
       <input type='number' name='months' min='0' max='100' value='<?php echo $request->months; ?>' /> months
    for <?php echo $request->city_dropdown(); ?>
    starting at <?php echo $request->month_dropdown() . $request->years_dropdown(); ?>
 
    <p /><h4>display charts:</h4> <?php echo $request->html(); ?>
    <p /><input type='submit' name='action' value='Go' /> <input type='submit' name='action' value='Clear' />
    <p /><input type='submit' name='action' value='Export CSV' />
    </form>
       <?php $request->chart_divs(); ?>
   </body>
 </html>