<?php


function get_bank_reconciliation_header($bank_account, $reconciled_date)
{
//        $reconciled_date = date2sql($reconciled_date_);

	$sql = "SELECT t.*, 
                IF(uu.id > 0, uu.real_name, cu.real_name) user 
                FROM ".TB_PREF."bank_account_reconciliations t 
                    JOIN ".TB_PREF."users cu 
                        ON t.created_by = cu.id 
                    LEFT JOIN ".TB_PREF."users uu 
                        ON t.created_by = uu.id 
                    
		WHERE t.account_id = ".db_escape($bank_account) . "
                    AND t.reconciliation_date = ".db_escape($reconciled_date);

	$result = db_query($sql,"get_bank_reconciliation_header");
	return db_fetch($result);
}

function get_bank_reconciliation_header_by_id($rec_id)
{
//        $reconciled_date = date2sql($reconciled_date_);

	$sql = "SELECT t.* 
                FROM ".TB_PREF."bank_account_reconciliations t  
		WHERE t.id = ".db_escape($rec_id);

	$result = db_query($sql,"get_bank_reconciliation_header_by_id");
	return db_fetch($result);
}


function get_bank_reconciliation_details($bank_account, $reconciled_date)
{
//        $reconciled_date = date2sql($reconciled_date_);

	$sql = "SELECT t.* 
                FROM ".TB_PREF."bank_trans t  
		WHERE t.bank_act = ".db_escape($bank_account) . "
                    AND t.reconciled = ".db_escape($reconciled_date)." 
		ORDER BY t.trans_date, t.id";

	return db_query($sql,"get_bank_reconciliation_details");
}

function get_bank_unreconciled_details($bank_account, $unreconciled_ids, $debits=true)
{
//        $reconciled_date = date2sql($reconciled_date_);

	$sql = "SELECT t.* 
                FROM ".TB_PREF."bank_trans t  
		WHERE t.bank_act = ".db_escape($bank_account) . "
			AND id IN (".$unreconciled_ids.")";
        
        if($debits){
		$sql .= " AND amount > 0 ";
        }
        else{
            $sql .= " AND amount < 0 ";
        }
		
        $sql .= " ORDER BY type, trans_date";

	return db_query($sql,"get_bank_unreconciled_details");
}

function view_bank_unreconciled_transactions($bank_account, $unreconciled_ids)
{
//        $reconciled_date = date2sql($reconciled_date_);

	$sql = "SELECT t.* 
                FROM ".TB_PREF."bank_trans t  
		WHERE t.bank_act = ".db_escape($bank_account) . "
			AND id IN (".$unreconciled_ids.")         
                        AND amount <> 0 ";
         
        $sql .= " ORDER BY trans_date, id";

	return db_query($sql,"view_bank_unreconciled_transactions");
}

function view_reconciled_account_difference($bank_account, $reconciled_date)
{
        
        $sql = "Select *  
                FROM ".TB_PREF."bank_trans 
                WHERE bank_act = ".db_escape($bank_account) . "
                    AND trans_date <= ".db_escape($reconciled_date) . " 
                    AND id NOT IN 
                                (SELECT id FROM ".TB_PREF."bank_trans 
                                 WHERE bank_act = ".db_escape($bank_account) . "
                                        AND trans_date <= ".db_escape($reconciled_date) . " 
                                        AND reconciled <= ".db_escape($reconciled_date) . " )";
        
        $sql .= " ORDER BY trans_date, id";
        

	return db_query($sql,"view_reconciled_account_difference");
}