<?php
/*
 * author: Michał Zieliński, mz@bim-it.pl
 * created: 2015-09-10
 * last modified date: 2015-09-15 by MZ
 */

$process = "0";
if ($_REQUEST['process'])
	$process = $_REQUEST['process'];


$ss = new Sugar_Smarty();

//show options
if ($process == "0") {
	$si = new EcmSysInfo();
	$ss->assign("BANKS",$si->getBanks());
	$ss->assign("CUR", $app_list_strings['currency_dom']);
	$ss->assign("DATE", date("d.m.Y"));
	$ss->display ( 'modules/EcmTransactions/tpls/importBankStatement/options.tpl' );
	return;
}

//read
if ($process == "1") {
	if ($_FILES['file']['error'] == '0') {
		//statement name
		$si = new EcmSysInfo();
		foreach ($si->getBanks() as $k=>$v)
			if ($v['ID'] == $_REQUEST['bank']) $b = $v['NAMESHORT'];
		$statement_name = $b.' - '.$_REQUEST['statement_date'];
		$data = readCSV($_FILES['file']['tmp_name'], $_REQUEST['bank'], $statement_name);
		$ss->assign("DATA", $data);
		$ss->assign("STATEMENT_NAME", $statement_name);
		$ss->assign("STATEMENT_DATE", $_REQUEST['statement_date']);
		$ss->display('modules/EcmTransactions/tpls/importBankStatement/list.tpl');
	} else {
		die('Błąd odczytu pliku - czy plik został wybrany?');
	}
}

//save
if ($process == "2") {
	$data = json_decode(base64_decode($_REQUEST['transactions_data']));
	$ids = array(); //array with new transactions ids
	foreach ($data as $t) {
		//warning! $t is stdObject NOT ARRAY
		//firstly try to save bank account
		if ($t->account_id!="")
			saveBankAccount($t->account_id, $t->account);
		//next save transaction
		$ids[] = saveTransaction($t, $_REQUEST['statement_name'], $_REQUEST['statement_date']);
	}
	$ss->assign('DATA', $ids);
	$ss->display('modules/EcmTransactions/tpls/importBankStatement/afterSave.tpl');
}

function saveTransaction($data, $statement_name, $statement_date) {
	global $current_user; 		
	$t = new EcmTransaction ();
	$t->name = $statement_name;
	$t->created_by = $current_user->id;
	$t->deleted = 0;
	$t->assigned_user_id = $current_user->id;
	if ($data->account_id != "") {
		$a = new Account();
		$a->retrieve($data->account_id);
		$t->parent_name = $a->name;
		$t->type2='2';
		$p_name = $a->name;
		$t->parent_id = $a->id;
		unset($a);
	}
	$t->type = 1;
	$t->type2 = 1;
	$t->type3='2';
	$t->payment_date = $data->date;
	$t->register_date = $statement_date;
	$t->currency_id = 'PLN';
	$t->value = unformat_number($data->value);
	if ($data->desc && $data->desc!=""){
		$t->description = $data->desc;
		$t->name = "PKO - ".$t->description;
	}
		
	$id = $t->save (true);
	return array('id' => $id, 'name' => $statement_name, 'value' => $data->value, 'account_id'=>$data->account_id, 'account_name'=>$p_name);
}
function saveBankAccount($account_id, $account) {
	$db = $GLOBALS['db'];
	$acc_res = $db->query("
		SELECT ab.account_id, a.name FROM account_banks AS ab
		INNER JOIN accounts AS a
		ON a.id=ab.account_id
		WHERE a.deleted='0' AND TRIM(REPLACE(ab.account, ' ', '')) = '".trim(str_replace(" ", "", $account))."'");
	if ($acc_res->num_rows == 0) {
		//save
		global $current_user;
		$db->query("INSERT INTO account_banks VALUES ('".create_guid()."','".date('Y-m-d H:m:s')."','".$current_user->id."', '$account', '', 'Utworzone podczas importu transakcji', '0', '$account_id' )");	
	}
}

function readCSV($filename, $bank, $statement_name) {
	$fp = fopen($filename, 'rb');
	$fp2 = fopen('logi.txt', 'a');
	$data = array();
	$format = getFormats($bank);
	if (!is_array($format))
		die('Brak określonego formatu pliku dla wybranego banku');
	$sum = 0;
	$i=0;
	global $timedate;
	while ( ($line = fgets($fp)) !== false) {
	    fwrite($fp2,$line);
		$i++;
		if ($format['startLine'] && $i < $format['startLine'])
			continue;
		if ($format['charset'])
			$line = iconv($format['charset'],"UTF-8",$line);
		$l = explode($format['delimiter'], $line);  
		
		$tmp = array();
		if (isset($format['account'])) {
			$tmp['account'] = str_replace("Nazwa nadawcy: ","",$l[$format['account']]);
			
			$tmp['account'] = preg_replace("/[^0-9]/", "", $tmp['account']);
		} else 
			die('Niepoprawnie zdefiniowany format wyciągu');

		if (isset($format['date'])) {
			$date = $l[$format['date']];
			if ($format['fieldSorrounder'])
				$date = str_replace($format['fieldSorrounder'], "", $date);
			$date = implode(".",array_reverse(explode("-",$date)));
			$tmp['date'] = $date;
		} else die('Niepoprawnie zdefiniowany format wyciągu');

		if (isset($format['value'])) {
			$value = $l[$format['value']];
			if ($format['fieldSorrounder'])
				$value = str_replace($format['fieldSorrounder'], "", $value);
			if ($format['number_delimiter']!='.')
				$value=str_replace($format['number_delimiter'], '.', $value);
			$sum+=$value;
			if($value>0){
			    $side='1';
			} else {
			    $side='0';
			}
			$tmp['value'] = format_number($value);
		} else die('Niepoprawnie zdefiniowany format wyciągu');

		if (isset($format['desc'])) {
			$tmp['desc'] = $l[$format['desc']];
			if ($format['fieldSorrounder'])
				$tmp['desc'] = str_replace($format['fieldSorrounder'], "", $tmp['desc']);
			if (isset($format['desc_trim']))
			    $tmp['desc'] = str_replace($format['desc_trim'], "", $tmp['desc']);
		}
		if (isset($format['desc_trim'])){

		    $desc = searchTitle($format['desc_trim'], $l);

		    $tmp['desc'] = $desc;
		}
		//try get account
		$db = $GLOBALS['db'];
		$acc_res = $db->query("
			SELECT ab.account_id, a.name FROM account_banks AS ab
			INNER JOIN accounts AS a
			ON a.id=ab.account_id
			WHERE a.deleted='0' AND ab.deleted='0' AND TRIM(REPLACE(ab.account, ' ', '')) = '".trim(str_replace(" ", "", $tmp['account']))."'");
		if ($acc_res->num_rows == 1) {
			$acc = $db->fetchByAssoc($acc_res);
			$tmp['account_found'] = "1";
			$tmp['account_name'] = $acc['name'];
			$tmp['account_id'] = $acc['account_id'];
		} else {
			$tmp['account_found'] = "0";
			$tmp['account_id'] = "";
			if (isset($format['payer'])) {
				$tmp['payer'] = $l[$format['payer']];
				if (isset($format['payer_trim']))
					$tmp['payer'] = str_replace($format['payer_trim'], "", $tmp['payer']);
			}
		}
		$date_unformated= implode("-", array_reverse(explode(".", $date)));
		//check if transaction could exists
		$query = "SELECT * FROM ecmtransactions WHERE payment_date='".$date_unformated."' 
				AND value='".unformat_number($tmp['value'])."' 
				AND deleted='0' and type='$side'";
		if ($tmp['account_found'] == "1")
			$query.=" AND parent_id='".$tmp['account_id']."'";
		$trans_res = $db->query($query);

		if ($trans_res->num_rows > 0) {
			$tmp['transaction_exists'] = "1";
			$tmp['transactions'] = array();
			while ($trans_row = $db->fetchByAssoc($trans_res)) {
				$tmp2 = array();
				$tmp2['id'] = $trans_row['id'];
				$tmp2['desc'] = $trans_row['name'].'&nbsp;&nbsp&nbsp'.format_number($trans_row['value']);
				$tmp['transactions'][] = $tmp2;
			}
		}
		$tmp['uniqID']=uniqid();
		$data[] = $tmp;
	}

	//add sum row
	$tmp = array();
	$tmp['sum'] = "1";
	$tmp['sum_val'] = format_number($sum);
	$data[] = $tmp;
	fclose($fp2);
	return $data;
}

function searchTitle($what,$elements){

    if(count($elements)>0){
        foreach ($elements as $key=>$val){
          
            $pos = strpos($val, $what);
          
            if($pos!==false){

                $return=str_replace('"', "", $val);

                $return=str_replace($what, "", $return);
                return $return;
            }
        }
        
    } else {
        return false;
    }
}

function getFormats($bank) {
	//banks file format settings
	$formats = array();
	//Reifaisen
	$formats['20'] = array (
			'delimiter' => '","',
			'startLine' => 2,
			'fieldSorrounder' => '"',
			'number_delimiter' => '.',
			'account' => 6,
			'value' => 3,
			'date' => 0,
			'desc' => 14,
			'payer' => 7,
			'payer_trim' => 'Dane adr. rach. przeciwst.: ',
			'desc_trim' => 'Tytuł: ',
			'charset' => 'ISO-8859-2',
	);
	return $formats[$bank];
}