<?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

 */