dev-chiefworks
134 lines
4.3 KiB
PHP
134 lines
4.3 KiB
PHP
<?php
|
|
|
|
set_time_limit(0); // No limit!
|
|
|
|
require('../backend.php');
|
|
|
|
$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;
|
|
//open file - STEW_BANK_IMPORT.txt
|
|
$file = fopen("STEW_BANK_IMPORT.txt", "r");
|
|
$ic = 0;
|
|
$insert_fld = "";
|
|
while (!feof($file)) {
|
|
|
|
$new_line = fgets($file);
|
|
|
|
if ($ic == 0) {
|
|
$fld = explode("|", $new_line);
|
|
$nc = 0;
|
|
foreach ($fld as $fr) {
|
|
if ($nc > 0) {
|
|
$sp = "";
|
|
if ($nc > 1) {
|
|
$sp = ",";
|
|
}
|
|
$insert_fld = $insert_fld . $sp . $fr;
|
|
}
|
|
$nc++;
|
|
}
|
|
}
|
|
if ($ic > 1) {
|
|
$datal = explode("|", $new_line);
|
|
$nc = 0;
|
|
$data_fld='';
|
|
foreach ($datal as $fr) {
|
|
if ($nc > 0) {
|
|
$sp = "";
|
|
if ($nc > 1) {
|
|
$sp = ",";
|
|
}
|
|
if($nc==1){
|
|
$fr = trim($fr)."01";
|
|
}
|
|
if($nc==2){
|
|
$fr = 1;
|
|
}
|
|
$data_fld = $data_fld . $sp . "'".trim($fr)."'";
|
|
}
|
|
$nc++;
|
|
}
|
|
echo $sqlline = "INSERT INTO members_bankimport ( $insert_fld ) VALUES ($data_fld)";
|
|
//pg_query($db, $sqlline);
|
|
}
|
|
|
|
$ic++;
|
|
}
|
|
|
|
fclose($file);
|
|
|
|
|
|
|
|
/*
|
|
|
|
|
|
|
|
$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
|
|
|
|
*/
|
|
|