<?php

//echo "[".date("Y-m-d H:i:s")."] Daily Report job is starting.\n";
include '../backend.php';

$db_host = $savvyext->cfgReadChar('database.host');
$db_name = $savvyext->cfgReadChar('database.name');
$db_user = $savvyext->cfgReadChar('database.user');
$db_pass = $savvyext->cfgReadChar('database.pass');
$db_port = $savvyext->cfgReadLong('database.port');
$connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
$conn = pg_connect($connstr);

$result_data = emissionsByDaysData($conn, 1, 30);

//print_r(emissionsByDaysData($conn, 3, 30));
 // print_r($result_data);
  /*
foreach ($result_data AS $drow){
    foreach ($drow AS $drow1){
         print_r($drow1);
    }
   
    echo "------------------------------";
}
*/
function emissionsByDaysData($db, $member_id, $days) {

    
            $gas_emission = [];
        $mysql_e = "SELECT sum(amount) AS amount,time::date FROM members_bankimport "
                . " WHERE member_id = $member_id AND  LOWER(category) ='gas' "
                . " AND time between  now()+'-$days days' "
                . " AND now() GROUP by time::date ORDER BY time DESC";

        $re = pg_query($db, $mysql_e);
        if ($re && pg_num_rows($re)) {
            $itm_e = 0;
            while ($fe = pg_fetch_assoc($re)) {
                $gas_emission_value = 0;
                $gas_amount = $fe["amount"]*0.01;
                $gas_date = $fe["time"];

              //  $emiss_arr = array_fill_keys($gas_date, array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount));
         //       $emiss_arr[$gas_date] = array_fill_keys($gas_date, array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount));
 $emiss_arr[$gas_date] = array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount);
                
               // print_r( $emiss_arr );
               // array_push($gas_emission, array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount));
              //  array_push($gas_emission, $emiss_arr);
            }
        }

    
        print_r($emiss_arr);
    
    
    $data = [];

    $data_result = travelTimeByDaysData($db, $member_id, $days);

    foreach ($data_result[0] as $drow) {
        // print_r($drow);
        //  echo "<hr>";
    }
    //print_r($data_result[0]);


    for ($ic = 0; $ic < $days; $ic++) {
        $new_date = date('Y-m-d', strtotime("-$ic days"));
        $emission_value = 0;

        if (array_key_exists($new_date, $data_result[0])) {
            $emission_value = $data_result[0][$new_date]["emission"];
        }

        array_push($data, array("date" => "$new_date", "value" => $emission_value));
    }

   // print_r($data);

    return [$data, []];
}

function travelTimeByDaysData($db, $member_id, $days) {

//PREPARE THE MODEL ARRAY 
    $mysql = "SELECT mkey,substr(lower(transport_mode),0,5) AS transport_mode, grams_km,passengers FROM emission_model";
    $model_data = [];
    $r = pg_query($db, $mysql);
    if ($r && pg_num_rows($r)) {
        $itm_count = 0;
        while ($f = pg_fetch_assoc($r)) {

            $model_line = array("mkey" => $f["mkey"], "transport_mode" => $f["transport_mode"], "grams_km" => $f["grams_km"], "passengers" => $f["passengers"]);
            $model_data[$f["transport_mode"]][] = $model_line;
        }
    }

    $data_result = [];
    // note we don travel everyday - i am modifying this to last 30 or 60 days - not just date diiference
    $q = "SELECT b.travel_date::date, c.category,count(*) AS count, sum(b.cost) AS amount  ,sum(b.duration) AS sum_duration , sum(b.distance) AS sum_distance
                FROM trackedemail_item a, parsedemail_item b, transport_providers c 
                WHERE a.id=b.trackedemail_item_id AND a.member_id=${member_id} AND c.id=b.transport_provider_id AND b.dup_id IS NULL AND
                date_trunc('day', b.travel_date_end) > (current_date - ${days}) AND date_trunc('day', b.travel_date_end) < current_date AND
                b.transport_provider_id > 0
                GROUP BY b.travel_date::date, c.category ORDER BY b.travel_date::date DESC "; // ${days}
    //error_log($q);
    $r = pg_query($db, $q);
    if ($r && pg_num_rows($r)) {
        $itm_count = 0;
        while ($f = pg_fetch_assoc($r)) {
            // pick the category
            $category_substring = strtolower(substr($f["category"], 0, 4));
            $model_read = $model_data[$category_substring][0];

            $grams_km = $model_read["grams_km"];
            $passengers = $model_read["passengers"];

            //echo "<hr>";
            //print_r( $model_read );
            $emission = $f["sum_distance"] * $grams_km / $passengers;

            $detail_line = array("category" => $f["category"], "amount" => $f["amount"], "duration" => $f["sum_duration"], "distance" => $f["sum_distance"], "emission" => $emission);
            $add_line = true;
            if ($itm_count > 0) {
                if (array_key_exists($f["travel_date"], $data_result)) {
                    $add_line = false; // dont add new line - modify the current one
                    $data_result[$f["travel_date"]]["value"] = $data_result[$f["travel_date"]]["value"] + $f["sum_duration"];
                    $data_result[$f["travel_date"]]["amount"] = $data_result[$f["travel_date"]]["amount"] + $f["amount"];
                    $data_result[$f["travel_date"]]["distance"] = $data_result[$f["travel_date"]]["distance"] + $f["sum_distance"];
                    $data_result[$f["travel_date"]]["emission"] = $data_result[$f["travel_date"]]["emission"] + $emission;
                    $data_result[$f["travel_date"]][$f["travel_date"]][] = $detail_line;
                }
            }

            if (true == $add_line) {
                $data_line = array(
                    "date" => $f["travel_date"],
                    "value" => $f["sum_duration"],
                    "amount" => $f["amount"],
                    "distance" => $f["sum_distance"],
                    "emission" => $emission,
                    $f["travel_date"] => $detail_line
                );
                $data_result[$f["travel_date"]] = $data_line;
            }
            $itm_count++;
        }
    }

    print_r($data_result);
    /*
      travel_date | category  | count | amount | sum_duration | distance
      -------------+-----------+-------+--------+--------------+----------
      2020-01-14  | RideShare |     1 |  13.39 |           14 |     1.43
      2019-12-10  | RideShare |     1 |  43.32 |           32 |    19.71
      2019-12-06  | RideShare |     4 |  43.29 |           52 |    11.76
      2019-10-19  | RideShare |     3 |  75.00 |           75 |    23.78
      2019-10-18  | RideShare |     3 |  20.10 |           34 |     8.85
      2019-10-17  | RideShare |     3 |  36.50 |           57 |    32.86
     */

    $optional_message = "Sweet! You’ve spent almost 30 minutes less time on your commute this month!";
    $icon = "fast-moving";

    $options_data = array('message' => $optional_message, 'icon' => $icon);

    return [$data_result, $options_data];
}


function travelTimeByDaysDataOLD($db, $member_id, $days) {

//PREPARE THE MODEL ARRAY 
    $mysql = "SELECT mkey,substr(lower(transport_mode),0,5) AS transport_mode, grams_km,passengers FROM emission_model";
    $model_data = [];
    $r = pg_query($db, $mysql);
    if ($r && pg_num_rows($r)) {
        $itm_count = 0;
        while ($f = pg_fetch_assoc($r)) {

            $model_line = array("mkey" => $f["mkey"], "transport_mode" => $f["transport_mode"], "grams_km" => $f["grams_km"], "passengers" => $f["passengers"]);
            $model_data[$f["transport_mode"]][] = $model_line;
        }
    }

    $data_result = [];
    // note we don travel everyday - i am modifying this to last 30 or 60 days - not just date diiference
    $q = "SELECT b.travel_date::date, c.category,count(*) AS count, sum(b.cost) AS amount  ,sum(b.duration) AS sum_duration , sum(b.distance) AS sum_distance
                FROM trackedemail_item a, parsedemail_item b, transport_providers c 
                WHERE a.id=b.trackedemail_item_id AND a.member_id=${member_id} AND c.id=b.transport_provider_id AND b.dup_id IS NULL AND
                date_trunc('day', b.travel_date_end) > (current_date - ${days}) AND date_trunc('day', b.travel_date_end) < current_date AND
                b.transport_provider_id > 0
                GROUP BY b.travel_date::date, c.category ORDER BY b.travel_date::date DESC "; // ${days}
    //error_log($q);
    $r = pg_query($db, $q);
    if ($r && pg_num_rows($r)) {
        $itm_count = 0;
        while ($f = pg_fetch_assoc($r)) {
            // pick the category
            $category_substring = strtolower(substr($f["category"], 0, 4));
            $model_read = $model_data[$category_substring][0];

            $grams_km = $model_read["grams_km"];
            $passengers = $model_read["passengers"];

            //echo "<hr>";
            //print_r( $model_read );
            $emission = $f["sum_distance"] * $grams_km / $passengers;

            $detail_line = array("category" => $f["category"], "amount" => $f["amount"], "duration" => $f["sum_duration"], "distance" => $f["sum_distance"], "emission" => $emission);
            $add_line = true;
            if ($itm_count > 0) {
                if (array_key_exists($f["travel_date"], $data_result)) {
                    $add_line = false; // dont add new line - modify the current one
                    $data_result[$f["travel_date"]]["value"] = $data_result[$f["travel_date"]]["value"] + $f["sum_duration"];
                    $data_result[$f["travel_date"]]["amount"] = $data_result[$f["travel_date"]]["amount"] + $f["amount"];
                    $data_result[$f["travel_date"]]["distance"] = $data_result[$f["travel_date"]]["distance"] + $f["sum_distance"];
                    $data_result[$f["travel_date"]]["emission"] = $data_result[$f["travel_date"]]["emission"] + $emission;
                    $data_result[$f["travel_date"]][$f["travel_date"]][] = $detail_line;
                }
            }

            if (true == $add_line) {
                $data_line = array(
                    "date" => $f["travel_date"],
                    "value" => $f["sum_duration"],
                    "amount" => $f["amount"],
                    "distance" => $f["sum_distance"],
                    "emission" => $emission,
                    $f["travel_date"] => $detail_line
                );
                $data_result[$f["travel_date"]] = $data_line;
            }
            $itm_count++;
        }
    }

    /*
      travel_date | category  | count | amount | sum_duration
      -------------+-----------+-------+--------+-----
      2019-07-25  | RideShare |     1 |   6.00 |   7
      2019-07-22  | RideShare |     1 |  13.90 |  19
      2019-06-24  | RideShare |     1 |  14.50 |  20
      2019-06-22  | RideShare |     1 |   6.00 |   6
      2019-06-21  | RideShare |     1 |  14.90 |  13
      2019-06-20  | RideShare |     2 |  24.00 |  33
      2019-06-16  | RideShare |     2 |  20.10 |  21
      2019-05-28  | RideShare |     1 |  14.20 |  15
      2019-05-27  | RideShare |     4 |  40.30 |  64
      2019-05-27  | Taxi      |     1 |  13.20 |  10
      2019-05-26  | RideShare |     2 |  27.70 |  17
      2019-05-24  | RideShare |     3 |  27.50 |  53
      2019-05-24  | Taxi      |     1 |  24.50 |  36
      2019-05-23  | RideShare |     4 |  38.10 |  45

      travel_date | category  | count | amount | sum_duration | distance
      -------------+-----------+-------+--------+--------------+----------
      2020-01-14  | RideShare |     1 |  13.39 |           14 |     1.43
      2019-12-10  | RideShare |     1 |  43.32 |           32 |    19.71
      2019-12-06  | RideShare |     4 |  43.29 |           52 |    11.76
      2019-10-19  | RideShare |     3 |  75.00 |           75 |    23.78
      2019-10-18  | RideShare |     3 |  20.10 |           34 |     8.85
      2019-10-17  | RideShare |     3 |  36.50 |           57 |    32.86
      2019-10-16  | RideShare |     8 |  96.90 |          144 |    90.25
      2019-10-15  | RideShare |     7 |  71.00 |          140 |    48.77
      2019-10-14  | RideShare |     4 |  41.20 |           87 |    38.93
      2019-10-13  | RideShare |     4 |  60.00 |           79 |    54.41
      2019-10-12  | RideShare |     5 |  54.60 |           58 |     42.6
      2019-10-11  | RideShare |     7 |  76.80 |          139 |    68.37
      2019-10-10  | RideShare |     4 |  57.40 |           97 |    50.74
      2019-10-09  | RideShare |     8 |  87.40 |          147 |    80.95
      2019-10-08  | RideShare |     6 |  81.30 |          106 |    71.11
      2019-10-07  | RideShare |     5 |  52.00 |           69 |    33.25
      2019-10-06  | RideShare |     6 |  60.80 |          101 |    49.45
      2019-10-05  | RideShare |     4 |  28.20 |           33 |     19.0
      2019-10-04  | RideShare |     2 |  44.50 |           53 |    31.44
      2019-10-03  | RideShare |     7 |  76.26 |          164 |    51.86
      2019-10-02  | RideShare |     6 |  84.40 |          219 |    54.40
      2019-10-01  | RideShare |     4 |  56.30 |          132 |    52.25
      2019-09-30  | RideShare |     7 |  90.80 |          123 |    67.76
      2019-09-29  | RideShare |     1 |  15.00 |           21 |    21.85
      2019-09-14  | RideShare |     3 |  29.00 |           33 |    27.61
      2019-09-13  | RideShare |     3 |  48.40 |           67 |    33.88
      2019-09-12  | RideShare |     4 |  54.25 |           87 |    48.11
      2019-09-11  | RideShare |     5 |  53.20 |           82 |    44.29
      2019-09-10  | RideShare |     3 |  23.90 |           44 |     20.5
      2019-09-09  | RideShare |     1 |  20.00 |           34 |    14.60
      2019-09-07  | RideShare |     2 |  30.90 |           41 |    29.86
      2019-09-06  | RideShare |     8 |  73.60 |          121 |    52.84
      (32 rows)


     */

    $optional_message = "Sweet! You’ve spent almost 30 minutes less time on your commute this month!";
    $icon = "fast-moving";

    $options_data = array('message' => $optional_message, 'icon' => $icon);

    return [$data_result, $options_data];
}

?>