dev-chiefworks
339 lines
14 KiB
PHP
339 lines
14 KiB
PHP
<?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];
|
||
}
|
||
|
||
?>
|