<?php

//$sql = "SELECT * from aws WHERE DATE(CreationDate) BETWEEN '$startDate' AND '$endDate' GROUP BY FLOOR(UNIX_TIMESTAMP(CreationDate)/($intervalLast * 60)) ORDER BY `aws`.`CreationDate` ASC";
    //print_r($sql);
    //$sql = "SELECT * FROM ( SELECT mt.*, TIMESTAMPDIFF( $intervalType, @prevTS, `$datetimeCol` ) AS timeinterval, @prevTS := mt.`$datetimeCol` FROM $selectTable mt, ( SELECT @prevTS := ( SELECT MIN(`$datetimeCol`) FROM $selectTable )) vars ORDER BY ID ) subquery_alias WHERE DATE($datetimeCol) BETWEEN '$startDate' AND '$endDate' AND timeinterval = $intervalLast";
class data {
  /* Select Data */
  function getAllSelect() {
    $selection = [
      "30 SECOND",
      "1 MINUTE",
      "5 MINUTE",
      "30 MINUTE",
      "1 HOUR",
      "2 HOUR",
      "3 HOUR",
      "6 HOUR",
      "12 HOUR"
    ];
    return $selection;
  }
  /* Select Data Ends */

  /* DB Selection Start */
  private function selectDB($startDate, $endDate, $interval = null) {
    $output = null;
    $selectTable = SELECT_TABLE;
    $datetimeCol = SELECT_TABLE_DATECOL;
    
    $startDate = date("Y-m-d H:i:s", strtotime($startDate));
    $endDate = date("Y-m-d H:i:s", strtotime($endDate));  
    
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    // Check connection
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }
     
    $intervalLast = 3;
    $default = FALSE;
    $intervalType = "";
    if (!empty($interval)) {
        if (strcmp("DEFAULT", $interval) == 0) {
            $time = "00:00:00";
           $default = TRUE;
        
        } else { 
        
          $setInverval = explode(" ", $interval);
          $intervalLast = $setInverval[0];
          $intervalType = $setInverval[1];
          $time = "00:00:00";
        //   if (strcmp($intervalType, "DAY") == 0) {
        //       $intervalType = "MINUTE";
        //       $intervalLast = 1440;
        //       $time = "00:00-23:59";
               
        //   } 
        }
    }
    //print_r("Default is::",$default);
   
    //print_r("$startDate, $endDate", $interval);


    //$sql = "SELECT * from aws WHERE (CreationDate) BETWEEN '2021-08-03' AND '2021-08-04' GROUP BY FLOOR(UNIX_TIMESTAMP(CreationDate)/(`$intervalLast` * 60)) ORDER BY `aws`.`CreationDate` DESC";
    //$sql = "";
   
   // $sql = "SELECT id, ROUND(MAX(WSmax),2) as WSmax, ROUND(AVG(WSavg),2) as WSavg, ROUND(MIN(WSmin),2) as WSmin, ROUND(MAX(TMPmax),2) as TMPmax, ROUND(AVG(TMPavg),2) as TMPavg, ROUND(MIN(TMPmin),2) as TMPmin, ROUND(MAX(RHmax),2) as RHmax, ROUND(AVG(RHavg),2) as RHavg, ROUND(MIN(RHmin),2) as RHmin, Battery, WD, ROUND(AVG(BPavg),2) as BPavg, ROUND(SUM(RAINavg),2) as RAINavg, ROUND(AVG(SOLARavg),2) as SOLARavg, CreationDate from $selectTable WHERE DATE(CreationDate) BETWEEN '$startDate' AND '$endDate' GROUP BY FLOOR(UNIX_TIMESTAMP(CreationDate)/($intervalLast * 60)) ORDER BY $selectTable.`CreationDate` ASC";
     $sql = "SELECT id, ROUND(MAX(WSmax),2) as WSmax, ROUND(AVG(WSavg),2) as WSavg, ROUND(MIN(WSmin),2) as WSmin, ROUND(MAX(TMPmax),2) as TMPmax, ROUND(AVG(TMPavg),2) as TMPavg, ROUND(MIN(TMPmin),2) as TMPmin, ROUND(MAX(RHmax),2) as RHmax, ROUND(AVG(RHavg),2) as RHavg, ROUND(MIN(RHmin),2) as RHmin, Battery, WD, ROUND(SUM(RAINavg),2) as RAINavg, ROUND(AVG(SOLARavg),2) as SOLARavg, ROUND(AVG(BPavg),2) as BPavg, CreationDate from $selectTable WHERE CreationDate BETWEEN '$startDate' AND '$endDate' GROUP BY FLOOR(UNIX_TIMESTAMP(CreationDate)/($intervalLast * 60)) ORDER BY $selectTable.`CreationDate` ASC";

     if($default == TRUE) {
        //echo "Default is::",$default;
        $sql = "SELECT * from $selectTable WHERE CreationDate BETWEEN '$startDate' AND '$endDate' ORDER BY $selectTable.`CreationDate` ASC";
    }
    
    if (strcmp($intervalType, "DAY") == 0) {

        $sql = "SELECT id, ROUND(MAX(WSmax),2) as WSmax, ROUND(AVG(WSavg),2) as WSavg, ROUND(MIN(WSmin),2) as WSmin, ROUND(MAX(TMPmax),2) as TMPmax, ROUND(AVG(TMPavg),2) as TMPavg, ROUND(MIN(TMPmin),2) as TMPmin, ROUND(MAX(RHmax),2) as RHmax, ROUND(AVG(RHavg),2) as RHavg, ROUND(MIN(RHmin),2) as RHmin, Battery, WD, ROUND(SUM(RAINavg),2) as RAINavg, ROUND(AVG(SOLARavg),2) as SOLARavg, ROUND(AVG(BPavg),2) as BPavg, CreationDate from $selectTable WHERE CreationDate BETWEEN '$startDate' AND '$endDate' GROUP BY DATE(DATE_SUB(CreationDate, INTERVAL 24 HOUR)) ORDER BY $selectTable.`CreationDate` ASC";
             
   } 
   
   if (strcmp($intervalType, "MONTH") == 0) {

        $sql = "SELECT id, ROUND(MAX(WSmax),2) as WSmax, ROUND(AVG(WSavg),2) as WSavg, ROUND(MIN(WSmin),2) as WSmin, ROUND(MAX(TMPmax),2) as TMPmax, ROUND(AVG(TMPavg),2) as TMPavg, ROUND(MIN(TMPmin),2) as TMPmin, ROUND(MAX(RHmax),2) as RHmax, ROUND(AVG(RHavg),2) as RHavg, ROUND(MIN(RHmin),2) as RHmin, Battery, WD, ROUND(SUM(RAINavg),2) as RAINavg, ROUND(AVG(SOLARavg),2) as SOLARavg, ROUND(AVG(BPavg),2) as BPavg, CreationDate from $selectTable WHERE CreationDate BETWEEN '$startDate' AND '$endDate' GROUP BY MONTH(CreationDate) ORDER BY $selectTable.`CreationDate` ASC";
             
     } 
    
     if (strcmp($intervalType, "YEAR") == 0) {

        $sql = "SELECT id, ROUND(MAX(WSmax),2) as WSmax, ROUND(AVG(WSavg),2) as WSavg, ROUND(MIN(WSmin),2) as WSmin, ROUND(MAX(TMPmax),2) as TMPmax, ROUND(AVG(TMPavg),2) as TMPavg, ROUND(MIN(TMPmin),2) as TMPmin, ROUND(MAX(RHmax),2) as RHmax, ROUND(AVG(RHavg),2) as RHavg, ROUND(MIN(RHmin),2) as RHmin, Battery, WD, ROUND(SUM(RAINavg),2) as RAINavg, ROUND(AVG(SOLARavg),2) as SOLARavg, ROUND(AVG(BPavg),2) as BPavg, CreationDate from $selectTable WHERE CreationDate BETWEEN '$startDate' AND '$endDate' GROUP BY YEAR(CreationDate) ORDER BY $selectTable.`CreationDate` ASC";
             
     } 
     
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
      // output data of each row
      while ($row = $result->fetch_assoc()) {
          //print_r($row);
        $output[] = [
          "DATE" => date("d-m-Y", strtotime($row[SELECT_TABLE_DATECOL])),
          "TIME" => date("H:i:s", strtotime($row[SELECT_TABLE_DATECOL])),
          "WS_Max" => $row['WSmax'],
          "WS_Avg" => $row['WSavg'],
          "WS_Min" => $row['WSmin'],
          "AT_Max" => $row['TMPmax'],
          "AT_Avg" => $row['TMPavg'],
          "AT_Min" => $row['TMPmin'],
          "RH_Max" => $row['RHmax'],
          "RH_Avg" => $row['RHavg'],
          "RH_Min" => $row['RHmin'],
          "Battery" => $row['Battery'],
          "WD" => $row['WD'],
          "BP" => $row['BPavg'],
          "Rain" => $row['RAINavg'],
         "Solar" => $row['SOLARavg'],
          '' => '',
        ];
      }
    //   if (strcmp($intervalType, "DAY") == 0) {
    //       if ($startDate != date("Y-m-d")) {
    //             array_pop($output);
    //       }
    //   }
    } else {
      $output = [];
    }
    $start[] = [
      "DATE" => "DD/MM/YY",
      "TIME" => "HH:MM:SS",
      "WS_Max" => "km/h",
      "WS_Avg" => "km/h",
      "WS_Min" => "km/h",
      "AT_Max" => "DegC",
      "AT_Avg" => "DegC",
      "AT_Min" => "DegC",
      "RH_Max" => "%",
      "RH_Avg" => "%",
      "RH_Min" => "%",
      "Battery" => "Volts",
      "WD" => "Deg",
      "BP" => "mbar",
      "Rain" => "MM",
       "Solar" => "watt/m2",
      '' => '',
    ];

    $start[] = [
      "DATE" => "WM START AT:",
      "TIME" => date("d-m-Y", strtotime($startDate)),
      "WS_Max" => $time,
      "WS_Avg" => "INTERVAL:",
      "WS_Min" => "$intervalLast",
    //  '' => '',
    //   "AT_Max" => ' ',
    //   "AT_Avg" => ' ',
    //   "AT_Min" => ' ',
    //   "RH_Max" => ' ',
    //   "RH_Avg" => ' ',
    //   "RH_Min" => ' ',
    //   "Battery" => ' ',
    //   "WD" => ' ',
    //   "BP" => ' ',
    //   "Rain" => ' ',
    //   "Solar" => ' ',
    ];
    //$start = str_replace('"', ' ', $start);
    $output = array_merge($start, $output);
    //$output = str_replace('"', ' ', $output);
    //$conn->close();
    return $output;
  }
  /* DB Selection Ends */

  /* Array to CSV */
  function str_putcsv($data) {
    ob_clean();
    header("Content-type: text/x-csv");
    header("Content-Transfer-Encoding: binary");
    header("Content-Disposition: attachment; filename=generated-".date("Y-m-d").".csv");
    header("Pragma: no-cache");
    header("Expires: 0");
    
    // Generate CSV in Memory
    $file = fopen('php://temp/maxmemory:'. (12*1024*1024), 'r+'); // 128mb
    
    // Write CSV to memory
    fputcsv($file, array_keys(call_user_func_array('array_merge', $data)));
  
    foreach($data as $row) {
        //$row = preg_replace('/(^|;)"([^"]+)";/','$1$2;',$row);
        fputcsv($file, $row);
        //fputcsv($file, $row, ',',chr(32));
        //fwrite($file, implode(",", $row));

    }
    
    // Fetch CSV contents
    rewind($file);
    $output = stream_get_contents($file);
    fclose($file);
    
    // Echo CSV
    $output = str_replace('"', '', $output);
    echo $output;
    die();
  }
  /* Array to CSV Ends */

  /* Process Form Data */
  function processForm($startDate, $endDate, $interval) {
    //$getInterval = $this->getAllSelect();
    //$getInterval = $getInterval[$interval];
    
    //$dbData = $this->selectDB($startDate, $endDate, $getInterval);
    $dbData = $this->selectDB($startDate, $endDate, $interval);
    $this->str_putcsv($dbData);
    return $dbData;
  }
  /* Process Form Data Ends */


  /* Test Insert */
  function testInsert() {
    $conn = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
    // Check connection
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }

    $timeNow = date("Y-m-d H:i:s");

    $sql = "INSERT INTO mytable (WS_DateTime) VALUES ('{$timeNow}')";

    if ($conn->query($sql) === TRUE) {
      echo "New record created successfully";
    } else {
      echo "Error: " . $sql . "<br>" . $conn->error;
    }
  }
  /* Test Insert Ends */
}
