<?php

// vim: ts=2 sw=2

  include_once "base_controller.php";

  include_once "data_formatters.php";

  // include_once "elapsed_time.php";

  class DataExportController extends BaseController {

    var $special_keys = array('table', 'columns', 'dt_after', 'dt_before', 'dt_start', 'dt_end', 'dt_tz', 'dt_period', 'dt_week_start_on_sunday', 'format', 'file', 'true_text', 'false_text', 'null_text', 'last_n_row', 'save_to_local', 'zip_threshold', 'auth_key');

    protected function signinRequired() {

      return true;

    }

    protected function authKeyRequired() {

      return true;

    }

    protected function sameHostAuthFree() {

      return true;

    }

    protected function tz() {

      $dt_tz = null;

      if (isset($_GET['dt_tz']))

        $dt_tz = $_GET['dt_tz'];

      try {

        $result = new DateTimeZone($dt_tz);

        return $result;

      } catch(Exception $e) {

        $error = L('"dt_tz" is not valid: ') . $dt_tz;

        error_log($error);

        // $default_tz = date_default_timezone_get();

        $default_tz = 'UTC';

        try {

          $result = new DateTimeZone($default_tz);

          return $result;

        } catch(Exception $e) {

          $error = 'default timezone "' . $default_tz .'" is not valid';

          error_log($error);

          $this->renderErrorAndHelp($error);

        }

      }

    }

    protected function convertTS($paramName, $tz) {

      $timeString = array_key_exists($paramName, $_GET) ? $_GET[$paramName] : null;

      $date = NULL;

      if (empty($timeString))

        return $date;

      $date = DateTime::createFromFormat('YmdHis', $timeString, $tz);

      if (empty($date))

          $this->renderErrorAndHelp(L('wrong datetime format: ') . $timeString);

      $date->setTimezone(new DateTimeZone('UTC'));

      return $date;

    }

    protected function tzOffset($userTZ) {

      $utcTZ = new DateTimeZone("UTC");

      $utcTime = new DateTime('now', $utcTZ);

      return $userTZ->getOffset($utcTime)/3600;

    }

    protected function processDateTimePeriod($dt_tz, &$dt_after, &$dt_before) {

      $dt_period_str = array_key_exists('dt_period', $_GET) ? $_GET['dt_period'] : null;

      if (empty($dt_period_str))

        return;

      $week_start_on_sunday = false;

      $start_on_sunday = $_GET['dt_week_start_on_sunday'];

      $week_start_on_sunday = (isset($start_on_sunday) && ($start_on_sunday == '1' || $start_on_sunday == 'true'));

      $now = new DateTime('now', $dt_tz);

      $today_no = $now->format('N');

      $after_str = null;

      $before_str = null;

      if (strcasecmp($dt_period_str, 'today') == 0) {

        $after_str = 'today';

        $before_str = 'tomorrow';

      } else if (strcasecmp($dt_period_str, 'yesterday') == 0) {

        $after_str = 'yesterday';

        $before_str = 'today';

      } else if (strcasecmp($dt_period_str, 'last_24hr') == 0) {

        $after_str = '-24 hours';

        $before_str = 'now';

      } else if (strcasecmp($dt_period_str, 'week_to_date') == 0) {

        if ($week_start_on_sunday) {

          if ($today_no == 7) // if today is Sunday

            $after_str = 'today';

          else

            $after_str = 'Sunday last week';

        }

        else {

          //GOTCHA: 'Monday this week' works well except on Sunday, it will return the Monday of next week on Sunday. Following logics fix this issue.

          $after_str = '-' . ($today_no-1) . ' days today';

        }

        $before_str = 'tomorrow';

      } else if (strcasecmp($dt_period_str, 'last_week') == 0) {

        if ($week_start_on_sunday) {

          $before_str = 'Sunday last week';

          if ($today_no == 7 )

            $before_str = 'today';

          $after_str = '-7 days ' . $before_str;

        } else {

          $after_str = '-' . ($today_no+6) . ' days today';

          $before_str = '-' . ($today_no-1) . ' days today';

        }

      } else if (strcasecmp($dt_period_str, 'month_to_date') == 0) {

        $after_str = 'midnight first day of this month';

        $before_str = 'tomorrow';

      } else if (strcasecmp($dt_period_str, 'last_month') == 0) {

        $after_str = 'midnight first day of last month';

        $before_str = 'midnight first day of this month';

      } else if (strcasecmp($dt_period_str, 'year_to_date') == 0) {

        $after_str = 'midnight first day of January this year';

        $before_str = 'tomorrow';

      } else if (strcasecmp($dt_period_str, 'last_year') == 0) {

        $after_str = 'midnight first day of January last year';

        $before_str = 'midnight first day of January this year';

      } else {

        $err_msg = $dt_period_str . L(' is not a valid date period value');

        error_log($err_msg);

        $this->renderErrorAndHelp($err_msg);

        return;

      }

      $utc_tz = new DateTimeZone('UTC');

      if (!is_null($after_str))

      {

        $dt_after = new DateTime($after_str, $dt_tz);

        $dt_after->setTimezone($utc_tz);

      }

      if (!is_null($before_str))

      {

        $dt_before = new DateTime($before_str, $dt_tz);

        $dt_before->setTimezone($utc_tz);

      }

    }

    protected function doAjaxGet() {

      $this->doGet();

    }

    protected function doGet() {

      $table = $_GET['table'];

      $columns = array_key_exists('columns', $_GET) ? $_GET['columns'] : null; 

      $dt_tz = $this->tz();

      $dt_start_param_name = 'dt_after';

      if (array_key_exists('dt_start', $_GET))

        $dt_start_param_name = 'dt_start';

      $dt_after = $this->convertTS($dt_start_param_name, $dt_tz);

      $dt_end_param_name = 'dt_before';

      if (array_key_exists('dt_end', $_GET))

          $dt_end_param_name = 'dt_end';

      $dt_before = $this->convertTS($dt_end_param_name, $dt_tz);

      if (is_null($dt_after) && is_null($dt_before)) {

        $this->processDateTimePeriod($dt_tz, $dt_after, $dt_before);

      }

      // handle 'file' parameter

      $download_file_name = null;

      $file_param = array_key_exists('file', $_GET) ? $_GET['file'] : null;

      if (!is_null($file_param)) {

        if (preg_match('/^[-_a-zA-Z0-9]+$/', $file_param))

          $download_file_name = $file_param;

        else

          $this->renderErrorAndHelp(L("invalid file name: ") . $file_param . L(" (only alphbet, number, dash and underscore are valid)"));

      }

      $true_text = null;

      $false_text = null;

      $null_text = null;

      if (isset($_GET['true_text']))

        $true_text = $_GET['true_text'];

      if (isset($_GET['false_text']))

        $false_text = $_GET['false_text'];

      if (isset($_GET['null_text']))

        $null_text = $_GET['null_text'];

      $format = $_GET['format'];

      $formatter = build_formatter($format);

      if (is_null($formatter))

        $this->renderErrorAndHelp(L("invalid format: ") . $format);

      else

      {

        if (isset($_GET['save_to_local'])) {

          $file_name = $_GET['save_to_local'];

          if (preg_match('/^[-_a-zA-Z0-9]+$/', $file_name))  {

            $formatter->saveToLocal($file_name);

            if (isset($_GET['zip_threshold'])) {

              $zip_threshold = floatval($_GET['zip_threshold']);

              $formatter->setZipThreshold($zip_threshold);  

            }

          }

          else

            $this->renderErrorAndHelp(L("invalid file name: ") . $file_name . L(" (only alphbet, number, dash and underscore are valid)"));

        }

      }

      if (empty($table))

        $this->renderErrorAndHelp(L("'table' is invalid"));

      if (empty($columns))

        $columns = '*';

      //NOTE: convert dt in sqlite to get better performance

      $orig_columns = $columns;

      $tzOffset = $this->tzOffset($dt_tz);

      $convertDTExpr = "datetime(dt, '{$tzOffset} hours') as _dt";

      if ($columns == '*')

      {

        $columns = "*,{$convertDTExpr}";

      } else {

        $pieces = explode(",", $columns);

        $selectors = array();

        $foundDt = false;

        foreach($pieces as $piece) {

          $piece = trim($piece);

          if (strcmp($piece, 'dt') == 0)

            $foundDt = true;

          $selectors[] = "\"$piece\"";

        }

        if ($foundDt)

          $selectors[] = $convertDTExpr;

        $columns = implode(",", $selectors);

      }

      //build and do sql query

      $db = DBConn::instance()->history_db();

      $conditions = array();

      // if (isset($_GET['conditions']))

      //   $conditions[] = '(' . $_GET['conditions'] . ')';

      // $conditions[] = "dt >= :dt_after";

      // $conditions[] = "dt < :dt_before";

      if (!is_null($dt_after))

        $conditions[] = "dt >= '" . $dt_after->format('Y-m-d H:i:s') . "'";

      if (!is_null($dt_before))

        $conditions[] = "dt < '" . $dt_before->format('Y-m-d H:i:s') . "'";

      $query = "SELECT {$columns} FROM {$table}"

      foreach($_GET as $k=>$v) {

        if (in_array($k, $this->special_keys))

          continue;

        $conditions[] = "\"$k\"=:$k";

      }

      if ($conditions)

        $query = $query . ' WHERE ' . implode(' AND ', $conditions);

      if (isset($_GET['last_n_row']))

        $lastNRow = $_GET['last_n_row'];

      if (isset($lastNRow) && is_numeric($lastNRow)) {

        $lastNRow = min(10000, intval($lastNRow));

        $query = $query . ' ORDER BY dt DESC LIMIT ' . $lastNRow;

        $query = 'SELECT * FROM ( ' . $query . ') ORDER BY dt ASC;'

      } else {

        $query = $query . ' ORDER BY dt LIMIT 10000 ;';

      }

      $stmt = $db->prepare($query);

      if (!isset($stmt) || !$stmt) {

        $db_error = $db->errorInfo();

        if (count($db_error) != 3)

          $this->renderErrorAndHelp(L('unknown error'));

        else

          $this->renderErrorAndHelp(L('error: ') . $db_error[2]);

      }

      foreach($_GET as $k=>$v) {

        if (in_array($k, $this->special_keys))

          continue;

        $stmt->bindValue(":$k", $v);

      }

      // $stmt->bindValue(':dt_after', '"' . $dt_after->format('Y-m-d H:i:s') . '"');

      // $stmt->bindValue(':dt_before', '"' . $dt_before->format('Y-m-d H:i:s'). '"');

      // $etime = new ElapsedTime();

      $stmt->execute();

      // error_log("### elapsed after query: " . $etime->elapsed());

      $bool_text_mappings = array('0' => $false_text, '1' => $true_text, '2' => $null_text);

      output_history_data($stmt, $formatter, $download_file_name, $orig_columns, $bool_text_mappings, $db, $table);

    }

    protected function renderErrorAndHelp($error) {

        $style = <<<STYLEDOC

<style>

.error_text { color: red; }

table, th, td { border: 1px solid grey; }

th,td { padding:15px; }

</style>

STYLEDOC;

        $help_desc = <<<HELPDOC

<div>

    <p>data_exporter script will allow you to export data from database, the URL syntax looks like this: <b>http://DEVICE_IP/sdcard/cpt/app/data_exporter.php?PARAM=VALUEhttp://DEVICE_IP/sdcard/cpt/app/data_exporter.php?PARAM=VALUE&PARAM2=VALUE2...</b></p>amp;PARAM2=VALUE2...http://DEVICE_IP/sdcard/cpt/app/data_exporter.php?PARAM=VALUE&PARAM2=VALUE2...</b></p>lt;/bhttp://DEVICE_IP/sdcard/cpt/app/data_exporter.php?PARAM=VALUE&PARAM2=VALUE2...</b></p>gt;http://DEVICE_IP/sdcard/cpt/app/data_exporter.php?PARAM=VALUE&PARAM2=VALUE2...</b></p>lt;/phttp://DEVICE_IP/sdcard/cpt/app/data_exporter.php?PARAM=VALUE&PARAM2=VALUE2...</b></p>gt;

    <div>Parameter details:

    <table style="border:1px solid grey;">

        <thead>

            <tr>

                <td>Name</td>

                <td>Value</td>

            </tr>

        </thead>

        <tbody>

            <tr>

                <td>table</td>

                <td>name of the database table, required.</td>

            </tr>

            <tr>

                <td>columns</td>

                <td>column name list, seperated by ','. if missing will return all columns' data</td>

            </tr>

            <tr>

                <td>format</td>

                <td>returned data format, valid format value: csv, html, json. if missing, the default is html.</td>

            </tr>

            <tr>

                <td>file</td>

                <td>with this parameter in url value, the exported data will trigger a download file process in browser and the file's name will the content of this parameter; otherwise data will be rendered on browser page directly. for example, if this parameter is 'last_week_data' and 'format' parameter is 'csv', then the download file's default name will be 'last_week_data.csv'. if missing, the default is rendering on browser page.</td>

            </tr>

            <tr>

                <td>dt_period</td>

                <td>only export data within this period. valid period value: today, yesterday, last_24hr, week_to_date, last_week, month_to_date, last_month, year_to_date, last_year. if missing, will export all.</td>

            </tr>

            <tr>

                <td>dt_start</td>

                <td>export data after(inclusive) this timestamp. for example 20130515011025 will export data after  01:10:25 of May 15, 2013. if missing, will export data starting from the oldest record.</td>

            </tr>

            <tr>

                <td>dt_after</td>

                <td>the same as 'dt_start'.</td>

            </tr>

            <tr>

                <td>dt_before</td>

                <td>export data before(exclusive) this timestamp. the format is the same as 'dt_after'. if missing, will export data till now.</td>

            </tr>

            <tr>

                <td>dt_end</td>

                <td>the same as 'dt_before'.</td>

            </tr>

            <tr>

                <td>dt_tz</td>

                <td>the timezone of 'dt_start'/'dt_end' or 'dt_after'/'dt_before' parameters. NOTE: the '/' character in the timezone's name must be encoded as '%2F', for example, 'Asia/Shanghai' should be 'Asia%2FShanghai'. </td>

            </tr>

            <tr>

                <td>dt_week_start_on_sunday</td>

                <td>if this param passed in with '1' or 'true' as its value, week will start from Sunday, otherwise, week starts from Monday</td>

            </tr>

            <tr>

                <td>true_text</td>

                <td>replace any boolean column in TRUE value with given text</td>

            </tr>

            <tr>

                <td>false_text</td>

                <td>replace any boolean column in FALSE value with given text</td>

            </tr>

            <tr>

                <td>null_text</td>

                <td>replace any boolean column in NULL value with given text</td>

            </tr>

            <tr>

                <td>last_n_row</td>

                <td>only return the last N rows in the query, this parameter must be a valid integer</td>

            </tr>

            <tr>

                <td>save_to_local</td>

                <td>save exported data on controller, you can specify file's name here(for example: "last_month_temperature"). the response will be data file's full path on controller. </td>

            </tr>

            <tr>

                <td>auth_key</td>

                <td>ust specify auth_key to export data, ask it from admin of CPT Graphics. </td>

            </tr>

        </tbody>

    </table>

    </div>

</div>

HELPDOC;

        $error_msg = $style . "<div class='error_text'>ERROR: " . $error . "</div>" . $help_desc;

        $this->renderErrorResponse($error_msg);

    }

  };

  $controller = new DataExportController();

  $controller->run();

?>