dev-chiefworks
131 lines
5.2 KiB
PHP
131 lines
5.2 KiB
PHP
<?php
|
|
|
|
set_time_limit(0); // No limit!
|
|
|
|
require('../backend.php');
|
|
|
|
$httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
|
|
$encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
|
|
$encryptionKey = $savvyext->cfgReadChar('encryption.key');
|
|
$encryptionIV = $savvyext->cfgReadChar('encryption.iv');
|
|
|
|
$baseURL = $savvyext->cfgReadChar('system.api_url');
|
|
|
|
$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);
|
|
|
|
$member_id = 1;
|
|
$days = 90;
|
|
|
|
|
|
|
|
$db = $conn;
|
|
|
|
$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
|
|
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 - 2260) AND date_trunc('day', b.travel_date_end) < current_date
|
|
GROUP BY b.travel_date::date, c.category ORDER BY b.travel_date::date DESC LIMIT ${days} ";
|
|
|
|
// error_log($q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$itm_count = 0;
|
|
while ($f = pg_fetch_assoc($r)) {
|
|
$detail_line = array("category" => $f["category"],"amount"=>$f["amount"], "duration" => $f["sum_duration"]);
|
|
$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"]][$f["travel_date"]][] = $detail_line;
|
|
}
|
|
}
|
|
|
|
if (true == $add_line) {
|
|
$data_line = array(
|
|
"date" => $f["travel_date"],
|
|
"value" => $f["sum_duration"],
|
|
"amount" => $f["amount"],
|
|
$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
|
|
|
|
*/
|
|
|
|
//TODO: replace with real data here
|
|
$data = [
|
|
|
|
array("date" => "2006-1-13", "value" => 60),
|
|
array("date" => "2006-1-23", "value" => 60),
|
|
array("date" => "2006-3-24", "value" => 55),
|
|
array("date" => "2006-2-24", "value" => 65),
|
|
array("date" => "2006-4-26", "value" => 50),
|
|
array("date" => "2006-5-27", "value" => 70),
|
|
array("date" => "2006-6-30", "value" => 60),
|
|
array("date" => "2006-7-1", "value" => 69),
|
|
array("date" => "2006-8-10", "value" => 45),
|
|
array("date" => "2006-10-15", "value" => 35),
|
|
array("date" => "2006-11-13", "value" => 45),
|
|
array("date" => "2006-12-12", "value" => 45),
|
|
array("date" => "2007-1-13", "value" => 60),
|
|
array("date" => "2007-1-23", "value" => 60),
|
|
array("date" => "2007-2-24", "value" => 65),
|
|
array("date" => "2007-3-24", "value" => 55),
|
|
array("date" => "2007-4-26", "value" => 50),
|
|
array("date" => "2007-5-27", "value" => 70),
|
|
array("date" => "2007-6-30", "value" => 60),
|
|
array("date" => "2007-7-1", "value" => 169),
|
|
array("date" => "2007-8-10", "value" => 45),
|
|
array("date" => "2007-10-15", "value" => 35),
|
|
array("date" => "2007-11-13", "value" => 45),
|
|
array("date" => "2007-12-12", "value" => 45),
|
|
array("date" => "2008-1-13", "value" => 21),
|
|
array("date" => "2008-1-15", "value" => 20),
|
|
array("date" => "2008-3-24", "value" => 30),
|
|
array("date" => "2008-4-26", "value" => 22),
|
|
array("date" => "2008-5-27", "value" => 34),
|
|
array("date" => "2008-6-30", "value" => 45),
|
|
array("date" => "2008-7-1", "value" => 25),
|
|
array("date" => "2008-8-10", "value" => 24),
|
|
array("date" => "2008-10-15", "value" => 35),
|
|
array("date" => "2008-11-13", "value" => 45),
|
|
array("date" => "2008-12-12", "value" => 45),
|
|
];
|
|
|
|
//print_r($data);
|
|
print_r($data_result);
|
|
// return [$data_result,[]];
|
|
|
|
|