<?php
/**********************************************************************
    Copyright (C) FrontAccounting, LLC.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = 'SA_IMINQ';
$path_to_root="../../..";
include_once($path_to_root . "/includes/session.inc");

include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/db_pager.inc");
include_once($path_to_root . "/includes/ui.inc");
include_once($path_to_root . "/includes/data_checks.inc");

include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/includes/banking.inc");

include_once($path_to_root . "/includes/ui/items_cart.inc");
include_once($path_to_root . "/modules/Kvcodes_importexport/includes/gl_db.inc");
include_once($path_to_root . "/modules/Kvcodes_importexport/includes/gl_ui.inc");
include_once($path_to_root . "/modules/Kvcodes_importexport/includes/ui/gl_bank_ui.inc");
include_once($path_to_root . "/modules/Kvcodes_importexport/includes/kvcodes.inc");
include_once($path_to_root . "/modules/Kvcodes_importexport/includes/ui/allocation_cart.inc");

add_access_extensions();
$js = "";
if ($SysPrefs->use_popup_windows)
	$js .= get_js_open_window(1300, 700);
if (user_use_date_picker())
	$js .= get_js_date_picker();
page(_($help_context = "Imported Bank Account Inquiry"), isset($_GET['bank_account']) && !isset($_GET['TransAfterDate']), false, "", $js, false, "", true);

check_db_has_bank_accounts(_("There are no bank accounts defined in the system."));
$undefined_bank_trans =array();

if(isset($_GET['b_id']) &&isset($_GET['b_id']) &&isset($_GET['b_id'])){
	$_POST['bank_account'] =$_GET['b_id'];
	$_POST['TransAfterDate'] =sql2date($_GET['f_date']);
	$_POST['TransToDate'] =sql2date($_GET['t_date']);
}

if(isset($_GET['updated']) && $_GET['updated']='yes')
	display_notification("Selected Bank transactions are allocated.");
//-----------------------------------------------------------------------------------
// Ajax updates
//
if (get_post('Show'))
{
	$Ajax->activate('trans_tbl');
}
//------------------------------------------------------------------------------------------------

if (isset($_GET['bank_account']))
	$_POST['bank_account'] = $_GET['bank_account'];



function view_popup_link($link_text, $url, $icon=false)
{
	global $path_to_root;
	
	if (user_graphic_links() && $icon)
		$link_text = set_icon($icon, $link_text);

	$href = $path_to_root . $url;
	// <a target='_blank' href='$path_to_root/gl/manage/exchange_rates.php?' onclick=\"javascript:openWindow(this.href,this.target); return false;\"> here</a>
	return "<a target='_blank' href='$href' onclick=\"javascript:openWindow(this.href,this.target); return false;\">" . $link_text . "</a>";
}


function import_gl_all_accounts_list($name, $selected_id=null, $skip_bank_accounts=false,
	$cells=false, $all_option=false, $submit_on_change=false, $all=false, $type_id=false)
{
	if ($skip_bank_accounts)
		$sql = "SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
			FROM (".TB_PREF."chart_master chart,".TB_PREF."chart_types type) "
			."LEFT JOIN ".TB_PREF."bank_accounts acc "
			."ON chart.account_code=acc.account_code
				WHERE acc.account_code  IS NULL
			AND chart.account_type=type.id";
	else
		$sql = "SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
			FROM ".TB_PREF."chart_master chart,".TB_PREF."chart_types type
			WHERE chart.account_type=type.id";
	if ($type_id ) $sql .= " AND chart.account_type=".db_escape($type_id);
	$account = ($skip_bank_accounts ? "account2" : "account");
	return combo_input($name, $selected_id, $sql, 'chart.account_code', 'chart.account_name',
		array(
			'format' => '_format_account',
			'spec_option' => $all_option===true ?  _("Select Accounts") : $all_option,
			'spec_id' => '',
			'type' => 2,
			'order' => array('type.class_id','type.id','account_code'),
			'search_box' => $cells,
			'search_submit' => false,
			'size' => 12,
			'max' => 10,
			'cells' => true,
			'select_submit'=> $submit_on_change,
			'async' => false,
			'category' => 2,
			'show_inactive' => $all
		), $account );
}

//for allocation

function customer_payment($customer_id,$trans_no){
	global $Refs;
	$_SESSION['alloc'] = new allocation(ST_CUSTPAYMENT, 0,$customer_id);

	if ($trans_no) {
		//  get date and supplier
		$bank_trans = db_fetch(get_bank_trans(ST_UNDEFINED, $trans_no));

		begin_transaction();
		$person_id =$customer_id;
		new_doc_date(sql2date($bank_trans['trans_date']));

		$new_pmt = !$_SESSION['alloc']->trans_no;
		//Chaitanya : 13-OCT-2011 - To support Edit feature
		$_POST['ref'] = $Refs->get_next(ST_CUSTPAYMENT, null, array(
			'customer' => $customer_id, 'date' => sql2date($bank_trans['trans_date'])));
		$payment_no = write_customer_payment($_SESSION['alloc']->trans_no, $customer_id, ANY_NUMERIC,
			$bank_trans["bank_act"], sql2date($bank_trans['trans_date']), $_POST['ref'],
			$bank_trans['amount'], 0, get_comments_string(ST_UNDEFINED, $bank_trans['trans_no']), 0,0, $bank_trans['amount']);
		$_SESSION['alloc']->trans_no = $payment_no;
		$_SESSION['alloc']->date_ = sql2date($bank_trans['trans_date']);
		$_SESSION['alloc']->write();

		unset($_SESSION['alloc']);
		Update('bank_trans',array("type"=>ST_CUSTPAYMENT,"trans_no"=>$payment_no),array('imported'=>1,'reconciled'=>date2sql(Today())));
		Delete('bank_trans',array("type"=>ST_UNDEFINED,"trans_no"=>$bank_trans['trans_no']));
		Delete('comments', array('type' => ST_UNDEFINED, 'id' => $bank_trans['trans_no']));
		commit_transaction();

	}

}
function supplier_payment($supplier_id,$trans_no){
	global $Refs;
	$_SESSION['alloc'] = new allocation(ST_SUPPAYMENT, 0, $supplier_id);
	begin_transaction();

	$bank_trans = db_fetch(get_bank_trans(ST_UNDEFINED, $trans_no));

	$_POST['ref'] =$Refs->get_next(ST_SUPPAYMENT, null, 
	array('supplier'=>$supplier_id, 'date'=>sql2date($bank_trans['trans_date'])));

	$payment_id = write_supp_payment(0, $supplier_id, $_POST['bank_account'],
	sql2date($bank_trans['trans_date']), $_POST['ref'], $bank_trans['amount'],	0, get_comments_string(ST_UNDEFINED, $bank_trans['trans_no']),0, $bank_trans['amount']);
	new_doc_date(sql2date($bank_trans['trans_date']));

	$_SESSION['alloc']->trans_no = $payment_id;
	$_SESSION['alloc']->date_ = sql2date($bank_trans['trans_date']);
	$_SESSION['alloc']->write();

	Update('bank_trans',array("type"=>ST_SUPPAYMENT,"trans_no"=>$payment_id),array('imported'=>1,'reconciled'=>date2sql(Today())));
	Delete('bank_trans',array("type"=>ST_UNDEFINED,"trans_no"=>$bank_trans['trans_no']));
	Delete('comments', array('type' => ST_UNDEFINED, 'id' => $bank_trans['trans_no']));
	commit_transaction();
}

function create_cart($type, $trans_no)
{
	global $Refs;

	if (isset($_SESSION['pay_items']))
	{
		unset ($_SESSION['pay_items']);
	}

	$cart = new items_cart($type);
    $cart->order_id = $trans_no;

	if ($trans_no) {

		$bank_trans = db_fetch(get_bank_trans(ST_UNDEFINED, $trans_no));
		$_POST['bank_account'] = $bank_trans["bank_act"];
		$_POST['PayType'] = $bank_trans["person_type_id"];
		$cart->reference = $bank_trans["ref"];

		if ($bank_trans["person_type_id"] == PT_CUSTOMER)
		{
			$trans = get_customer_trans($trans_no, $type);	
			$_POST['person_id'] = $trans["debtor_no"];
			$_POST['PersonDetailID'] = $trans["branch_code"];
		}
		elseif ($bank_trans["person_type_id"] == PT_SUPPLIER)
		{
			$trans = get_supp_trans($trans_no, $type);
			$_POST['person_id'] = $trans["supplier_id"];
		}
		elseif ($bank_trans["person_type_id"] == PT_MISC)
			$_POST['person_id'] = $bank_trans["person_id"];
		elseif ($bank_trans["person_type_id"] == PT_QUICKENTRY)
			$_POST['person_id'] = $bank_trans["person_id"];
		else 
			$_POST['person_id'] = $bank_trans["person_id"];

		$cart->memo_ = get_comments_string(ST_UNDEFINED, $trans_no);
		$cart->tran_date = sql2date($bank_trans['trans_date']);
		$cart->original_amount = $bank_trans['amount'];
		$result = get_gl_trans(ST_UNDEFINED, $trans_no);
		if ($result) {
			while ($row = db_fetch($result)) {
				if (is_bank_account($row['account'])) {
					// date exchange rate is currenly not stored in bank transaction,
					// so we have to restore it from original gl amounts
					$ex_rate = $bank_trans['amount']/$row['amount'];
				} else {
					$cart->add_gl_item( $row['account'], $row['dimension_id'],
						$row['dimension2_id'], $row['amount'], $row['memo_']);
				}
			}
		}

		// apply exchange rate
		foreach($cart->gl_items as $line_no => $line)
			$cart->gl_items[$line_no]->amount *= $ex_rate;

	} else {
		$cart->reference = $Refs->get_next($cart->trans_type, null, $cart->tran_date);
		$cart->tran_date = new_doc_date();
		if (!is_date_in_fiscalyear($cart->tran_date))
			$cart->tran_date = end_fiscalyear();
	}

	$_POST['memo_'] = $cart->memo_;
	$_POST['ref'] = $cart->reference;
	$_POST['date_'] = $cart->tran_date;

	$_SESSION['pay_items'] = &$cart;
}

function import_subledger_list($name, $account, $selected_id=null)
{

	$type = is_subledger_account($account);
	if (!$type)
		return '';

	if($type > 0)
		$sql = "SELECT DISTINCT d.debtor_no as id, CONCAT(debtor_ref,'-',name) as name 
		FROM "
		.TB_PREF."debtors_master d,"
		.TB_PREF."cust_branch c
		WHERE d.debtor_no=c.debtor_no AND NOT d.inactive AND c.receivables_account=".db_escape($account);
	else
		$sql = "SELECT supplier_id as id, CONCAT(supp_ref,'-',supp_name) as name 
		FROM "
		.TB_PREF."suppliers s
		WHERE NOT s.inactive AND s.payable_account=".db_escape($account);

	$mode = get_company_pref('no_customer_list');

	return combo_input($name, $selected_id, $sql, 'id', 'name',
	array(
		'type' => 1,
		'size' => 20,
		'async' => false,
	) );
}


function import_subledger_list_cells($label, $name, $account, $selected_id=null)
{
	if ($label != null)
		echo "<td>$label</td>\n";
	echo "<td nowrap>";
	echo import_subledger_list($name, $account, $selected_id);
	echo "</td>\n";
}
function get_imported_bank_trans_for_bank_account($bank_account, $from, $to,$type)
{
	$from = date2sql($from);
	$to = date2sql($to);
	$sql = "SELECT t.* ,ba.account_code
		FROM ".TB_PREF."bank_trans t 
			LEFT JOIN ".TB_PREF."bank_accounts ba ON t.bank_act =ba.id
			LEFT JOIN ".TB_PREF."voided v ON t.type=v.type AND t.trans_no=v.id
		WHERE t.bank_act = ".db_escape($bank_account) . "
			AND ISNULL(v.date_)
			AND t.imported=1 ";
	if($type !=-1)
		$sql .="AND t.type=".db_escape($type)." ";
	$sql .= "AND trans_date >= '$from'
			AND trans_date <= '$to'
			AND amount != 0
		ORDER BY trans_date, t.id";

	return db_query($sql,"The transactions for '" . $bank_account . "' could not be retrieved");
}


//------------------------------------------------------------------------------------------------

if (!isset($_POST['bank_account']))
	$_POST['bank_account'] = "";
if(!isset($_POST['curr_selected_account']))
	$_POST['curr_selected_account'] ="";
if(!isset($_POST['curr_selected_party']))
	$_POST['curr_selected_party'] ="";

if(isset($_POST['undefined_bank_trans']) && $_POST['undefined_bank_trans']){
	$trans_nos=explode(";",$_POST['undefined_bank_trans']);
	foreach ($trans_nos as $key) {

		if(list_updated("code_id_".$key) && check_value("check_".$key)){
			$_POST['curr_selected_account'] =$_POST["code_id_".$key];
			if(isset($_POST['person_id_'.$key])){
				$_POST['curr_selected_party'] =$_POST["person_id_".$key];
			}else{
				$_POST['curr_selected_party'] ='';
			}
			
			$Ajax->activate('trans_tbl');
		}

		if(list_updated("check_".$key)){
			$Ajax->activate('trans_tbl');
		}
	}
}

function log_undefined()
{
	br();
	if (!isset($_POST['clearundefined'])){
		submit_center('clearundefined', _("Clear Undefined transctions"), true, '', '','cancel');
	}else{
		display_warning(_("Are you sure you want to delete selected undefined bank transctions?"), 0, 1);
		submit_center_first('Confirmclearundefined', _("Proceed"), '', false,'default');
		submit_center_last('Cancelclearundefined', _("Cancel"), '', false,'cancel');
	}
}


if(isset($_POST['Confirmclearundefined'])){
	$imported_bank_trans = get_imported_bank_trans_for_bank_account($_POST['bank_account'], $_POST['TransAfterDate'], $_POST['TransToDate'], $_POST['type']);
	$cleared =false ;
	foreach ($imported_bank_trans as $value) {
		
		if(check_value('check_'.$value['trans_no'])){
			$cleared =true;
			Delete('bank_trans',array('trans_no'=>$value['trans_no'],'type'=>101));
			Delete('comments',array('id'=>$value['trans_no'],'type'=>101));
		}
	}
	if($cleared)
		display_notification(_('Selected undefined bank transctions has been cleared successfully'));
	else
		display_error('Atleast one undefined bank transction should be selected');
}

start_form();
start_table(TABLESTYLE_NOBORDER);
start_row();
bank_accounts_list_cells(_("Account:"), 'bank_account', null);
journal_types_list_cells(_("Type:"), "type");	
date_cells(_("From:"), 'TransAfterDate', '', null, -user_transaction_days());
date_cells(_("To:"), 'TransToDate');

submit_cells('Show',_("Show"),'','', 'default');
end_row();
end_table();
	
div_start('trans_tbl');
$act = get_bank_account($_POST["bank_account"]);
display_heading($act['bank_account_name']." - ".$act['bank_curr_code']);
$result = get_imported_bank_trans_for_bank_account($_POST['bank_account'], $_POST['TransAfterDate'], $_POST['TransToDate'], $_POST['type']);
start_table(TABLESTYLE);

$th = array(_("Type"), _("Reference"), _("Date"),
	_("Debit"), _("Credit"), _("Balance"), _("Person/Item"), _("Memo"), "", "",_("Account"),_("Counter Party"), _("#"));
table_header($th);

$bfw = get_balance_before_for_bank_account($_POST['bank_account'], $_POST['TransAfterDate']);

$credit = $debit = 0;
start_row("class='inquirybg' style='font-weight:bold'");
label_cell(_("Opening Balance")." - ".$_POST['TransAfterDate'], "colspan=3");
display_debit_or_credit_cells($bfw);
label_cell("");
label_cell("", "colspan=6");

end_row();
$running_total = $bfw;
if ($bfw > 0 ) 
	$debit += $bfw;
else 
	$credit += $bfw;
$j = 1;
$k = 0; //row colour counter
while ($myrow = db_fetch($result))
{

	alt_table_row_color($k);

	$running_total += $myrow["amount"];

	$trandate = sql2date($myrow["trans_date"]);
	label_cell($systypes_array[$myrow["type"]]);
	// label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"]));
	label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"],$myrow['ref']));
	label_cell($trandate);
	display_debit_or_credit_cells($myrow["amount"]);
	amount_cell($running_total);

	label_cell(payment_person_name($myrow["person_type_id"],$myrow["person_id"]));

	label_cell(get_comments_string($myrow["type"], $myrow["trans_no"]));
	label_cell(get_gl_view_str($myrow["type"], $myrow["trans_no"]));
	if($myrow['type'] ==ST_UNDEFINED){
		if($myrow['amount']<0)
			$debcrt ="ModifyPayment=";
		else
			$debcrt ="ModifyDeposit=";
		label_cell(view_popup_link(_("Edit"),$path_to_root."/modules/Kvcodes_importexport/view/undefined_bank_trans.php?".$debcrt."Yes&trans_no=".$myrow["trans_no"]."&trans_type=".$myrow["type"], ICON_EDIT));
	}
	else
		label_cell(trans_editor_link($myrow["type"], $myrow["trans_no"]));
	if($myrow['type'] ==ST_UNDEFINED){
		$def=$party=null;
		$undefined_bank_trans [$myrow['trans_no']] =$myrow;
		if(check_value('check_'.$myrow['trans_no'])){
			$def=$_POST['curr_selected_account'];
			$party=$_POST['curr_selected_party'];
		}
		echo "<td>".import_gl_all_accounts_list('code_id_'.$myrow['trans_no'], $def, true, false,true,true)."</td>";
		if ($subledger =is_subledger_account(get_post('code_id_'.$myrow['trans_no'])))
		{	// for home currency postings show all customers
			if($subledger =='1'){
				hidden('person_type_'.$myrow['trans_no'] ,PT_CUSTOMER);
			}elseif($subledger =='-1'){
				hidden('person_type_'.$myrow['trans_no'] ,PT_SUPPLIER);
			}
			import_subledger_list_cells(null, 'person_id_'.$myrow['trans_no'], get_post('code_id_'.$myrow['trans_no']), $party);
		} else{
			label_cell('');
		}


		check_cells(null,'check_'.$myrow['trans_no'],null,true);
	}
	else{
		$items = get_gl_trans($myrow['type'], $myrow["trans_no"]);
		$accounts =array();
		if($items){
			foreach ($items as $item) {
				if ($item["account"] != $myrow["account_code"]){
					$accounts[]=$item["account_name"];
				}
			}
		}
		label_cell(implode(',', $accounts));
		label_cell("");
		if($myrow['type']==ST_BANKPAYMENT || $myrow['type']==ST_BANKDEPOSIT ||$myrow['type'] ==ST_CUSTPAYMENT||$myrow['type'] ==ST_SUPPAYMENT||$myrow['type'] ==ST_BANKTRANSFER){
			label_cell(view_popup_link(_("Roll back"),$path_to_root."/modules/Kvcodes_importexport/view/trans_to_undefined.php?id=".$myrow["id"]."&trans_no=".$myrow["trans_no"]."&trans_type=".$myrow["type"], ICON_DELETE));
		}
		else{
			label_cell("");
		}


	}
	end_row();
 	if ($myrow["amount"] > 0 ) 
 		$debit += $myrow["amount"];
 	else 
 		$credit += $myrow["amount"];

	if ($j == 12)
	{
		$j = 1;
		table_header($th);
	}
	$j++;
}
//end of while loop

start_row("class='inquirybg' style='font-weight:bold'");
label_cell(_("Ending Balance")." - ". $_POST['TransToDate'], "colspan=3");
amount_cell($debit);
amount_cell(-$credit);
//display_debit_or_credit_cells($running_total);
amount_cell($debit+$credit);
label_cell("", "colspan=6");
end_row();
end_table(2);
div_end();



if(isset($_POST['BNK_TRANS'])){
	foreach ($undefined_bank_trans as $key => $value) {
		// if(check_value('check_'.$myrow['trans_no'])){}
		if(isset($_POST['person_id_'.$key]) && $_POST['person_id_'.$key]){
			if($_POST['person_type_'.$key] ==PT_CUSTOMER){
				customer_payment($_POST['person_id_'.$key],$value['trans_no']);
			}else{
				supplier_payment($_POST['person_id_'.$key],$value['trans_no']);
			}
		}else{
			if(isset($_POST['code_id_'.$key]) && $_POST['code_id_'.$key]){
			if($value['amount']<0){
				create_cart(ST_BANKPAYMENT, $value['trans_no']);
				$amount = ($_SESSION['pay_items']->trans_type==ST_BANKPAYMENT ? 1:-1) * abs($value['amount']);
				$_SESSION['pay_items']->add_gl_item($_POST['code_id_'.$key], 0,
					0, $amount, "");

			}else{
				create_cart(ST_BANKDEPOSIT, $value['trans_no']);
				$amount = ($_SESSION['pay_items']->trans_type==ST_BANKPAYMENT ? 1:-1) * abs($value['amount']);
				$_SESSION['pay_items']->add_gl_item($_POST['code_id_'.$key], 0,
					0, $amount, "");
			}


			begin_transaction();
			
			$person_id ="";

			$_SESSION['pay_items'] = &$_SESSION['pay_items'];
			$new = $_SESSION['pay_items']->order_id = 0;

			add_new_exchange_rate(get_bank_account_currency($value['bank_act']),sql2date($value['trans_date']), input_num('_ex_rate'));

			$trans = write_bank_transaction(
				$_SESSION['pay_items']->trans_type,0, $value['bank_act'],
				$_SESSION['pay_items'], sql2date($value['trans_date']),
				PT_MISC, $person_id, get_post('PersonDetailID'),
				$_SESSION['pay_items']->reference, $_SESSION['pay_items']->memo_, true, input_num('settled_amount', null));

			$trans_type = $trans[0];
		   	$trans_no = $trans[1];
			new_doc_date(sql2date($value['trans_date']));

			$_SESSION['pay_items']->clear_items();
			unset($_SESSION['pay_items']);

			Update('bank_trans',array("type"=>$trans_type,"trans_no"=>$trans_no),array('imported'=>1,'reconciled'=>date2sql(Today())));
			Delete('bank_trans',array("type"=>ST_UNDEFINED,"trans_no"=>$value['trans_no']));
			Delete('comments', array('type' => ST_UNDEFINED, 'id' => $value['trans_no']));
			commit_transaction();
		}
		
		}
	}
	display_notification("Selected Undefined bank transactions are allocated.");
	$Ajax->activate('trans_tbl');
	meta_forward($_SERVER['PHP_SELF'].'?b_id='.$_POST['bank_account'].'&f_date='.date2sql($_POST['TransAfterDate']).'&t_date='.date2sql($_POST['TransToDate']).'&updated=yes');
}


hidden('undefined_bank_trans',implode(";", array_keys($undefined_bank_trans)));
hidden('curr_selected_account',$_POST['curr_selected_account']);
hidden('curr_selected_party',$_POST['curr_selected_party']);
submit_center( 'BNK_TRANS', _('Add Bank Trasctions'), true, '',  'default');
log_undefined();
end_form();
//------------------------------------------------------------------------------------------------

end_page();

	