<?php
/**********************************************************************
***********************************************************************/


function get_batch_bank_transfer_source_supplier_payments($trans_no, $to_bank_act, $filtering_type_id, $filtering_from_date, $filtering_to_date)
{
    $sql= "SELECT bt.trans_no,
                bt.type,
                bt.amount,
                bt.ref reference,
                bt.trans_date,
                bt.person_type_id,
                bt.person_id,
                bt.person_detail_id,
                gltn.value_name gl_trans_type_name 
            FROM ".TB_PREF."bank_trans bt   
                LEFT JOIN ".TB_PREF."batch_bank_transfer_details bbtd 
                    ON bbtd.source_type = bt.type 
                    AND bbtd.source_trans_no = bt.trans_no   
                LEFT JOIN ".TB_PREF."list_values gltn  
                    ON gltn.id = bt.gl_trans_type_id  
            WHERE bt.type = ". db_escape($filtering_type_id)."    
                AND bt.bank_act = ". db_escape($to_bank_act)."    
                AND bt.trans_date >= ".db_escape($filtering_from_date)." 
                AND bt.trans_date <= ".db_escape($filtering_to_date)."                   
                AND (bbtd.removed_by IS NULL OR bbtd.removed_by = 1)  
            ORDER BY bt.trans_date";
    
    return db_query($sql,"get_batch_bank_transfer_source_iou_bank_payments");	
    
}


function get_batch_bank_transfer_source_iou_bank_payments($trans_no, $to_bank_act, $filtering_type_id, $filtering_from_date, $filtering_to_date)
{
    $sql= "SELECT bt.trans_no,
                bt.type,
                bt.amount,
                bt.ref reference,
                bt.trans_date,
                bt.person_type_id,
                bt.person_id,
                bt.person_detail_id,
                gltn.value_name gl_trans_type_name 
            FROM ".TB_PREF."bank_trans bt   
                LEFT JOIN ".TB_PREF."batch_bank_transfer_details bbtd 
                    ON bbtd.source_type = bt.type 
                    AND bbtd.source_trans_no = bt.trans_no   
                LEFT JOIN ".TB_PREF."list_values gltn  
                    ON gltn.id = bt.gl_trans_type_id  
            WHERE bt.type = ". db_escape($filtering_type_id)."    
                AND bt.bank_act = ". db_escape($to_bank_act)."    
                AND bt.trans_date >= ".db_escape($filtering_from_date)." 
                AND bt.trans_date <= ".db_escape($filtering_to_date)."                   
                AND (bbtd.removed_by IS NULL OR bbtd.removed_by > 0)  
            ORDER BY bt.trans_date";
    
    return db_query($sql,"get_batch_bank_transfer_source_iou_bank_payments");	
    
}

    
function update_batch_bank_transfer_header($type_id, $trans_no, $trans_date, $reference,
                                            $from_bank_act, $to_bank_act, $comment)
{
    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');         
    
    $sql = "UPDATE ".TB_PREF."batch_bank_transfer_header  
            SET 
                trans_date =".db_escape($trans_date)." ,
                reference =".db_escape($reference)." ,
                from_bank_act =".db_escape($from_bank_act)." ,
                to_bank_act =".db_escape($to_bank_act)." ,
                comment = ".db_escape($comment).",    
                updated_by = ".db_escape($updated_by).",    
                updated_on = ".db_escape($updated_on)."    
            WHERE type = ".db_escape($type_id)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND fund_transfer_id = 0 ";

    db_query($sql, "update_batch_bank_transfer_header");    
    
    return db_num_affected_rows();
}
    
function update_batch_bank_transfer_header_total($type_id, $trans_no)
{
    
    $total = get_batch_bank_transfer_details_total($type_id, $trans_no);       
    
    $sql = "UPDATE ".TB_PREF."batch_bank_transfer_header  
            SET amount =".db_escape(-$total)."   
            WHERE type = ".db_escape($type_id)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND fund_transfer_id = 0 ";

    db_query($sql, "update_batch_bank_transfer_header");    
    
    $n = db_num_affected_rows();
    if($n !== 1){
        $message = "Could not update the transaction total.";
        return array("success" => false, "message" => $message);
    }else{
        return array("success" => true, "message" => "");
    }
}

function update_batch_bank_transfer_approved($type_id, $trans_no, $fund_trans_no)
{
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');         

    $sql = "UPDATE ".TB_PREF."batch_bank_transfer_header  
            SET approval_status = ".db_escape(AS_APPROVED).", 
                fund_transfer_id = ".db_escape($fund_trans_no).", 
                approved_by = ".db_escape($approved_by).", 
                approved_on = ".db_escape($approved_on)." 
            WHERE type = ".db_escape($type_id)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND fund_transfer_id = 0";

    db_query($sql, "update_batch_bank_transfer_approved");    
    
    $n = db_num_affected_rows();
    if($n !== 1){
        $message = "Could not update the target transaction number.";
        return array("success" => false, "message" => $message);
    }else{
        return array("success" => true, "message" => "");
    }
}



function get_batch_bank_transfer_record_by_id($id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."batch_bank_transfer_header 
            WHERE id =".db_escape($id);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}

function get_batch_bank_transfer_record_by_fund_transfer_no($fund_transfer_no)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."batch_bank_transfer_header 
            WHERE fund_transfer_id =".db_escape($fund_transfer_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function get_batch_bank_transfer_record($type_id, $trans_no)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."batch_bank_transfer_header 
            WHERE type =".db_escape($type_id)." 
                AND trans_no = ".db_escape($trans_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function delete_batch_bank_transfer_detail_item($type_id, $trans_no, $del_trans_no)
{
    $sql = "DELETE 
            FROM ".TB_PREF."batch_bank_transfer_details  
            WHERE id = ".db_escape($del_trans_no);
    
    db_query($sql, "delete_batch_bank_transfer_detail_item"); 
    
    return db_num_affected_rows();
    
}

function get_batch_bank_transfer_details_record($type_id, $trans_no, $source_type, $source_trans_no)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."batch_bank_transfer_details 
            WHERE type =".db_escape($type_id)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND source_type =".db_escape($source_type)." 
                AND source_trans_no = ".db_escape($source_trans_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}

function get_batch_bank_transfer_details($type_id, $trans_no)
{
    $sql = "SELECT bbtr.*,
                    bt.ref reference,
                    bt.trans_date,
                    bt.person_type_id,
                    bt.person_id,
                    bt.gl_trans_type_id,
                    bt.person_detail_id,
                    bt.amount, 
                    gltn.value_name gl_trans_type_name,
                    bbtr.removed_by 
            FROM ".TB_PREF."batch_bank_transfer_details bbtr 
                JOIN ".TB_PREF."bank_trans bt 
                    ON bt.type = bbtr.source_type 
                    AND bt.trans_no = bbtr.source_trans_no 
                LEFT JOIN ".TB_PREF."list_values gltn  
                    ON gltn.id = bt.gl_trans_type_id  
            WHERE bbtr.type =".db_escape($type_id)." 
                AND bbtr.trans_no = ".db_escape($trans_no);
    
    return db_query($sql, "get_batch_bank_transfer_details"); 
    
}



function get_batch_bank_transfer_items_count($type_id, $trans_no)
{
    $sql = "SELECT COUNT(id) lines_count 
            FROM ".TB_PREF."batch_bank_transfer_details 
            WHERE type =".db_escape($type_id)." 
                AND trans_no =".db_escape($trans_no);
    
    $result = db_query($sql, "get_batch_bank_transfer_items_count");
    return db_result_value($result, "lines_count");   
    
}


function get_batch_bank_transfer_details_total($type_id, $trans_no)
{
    $sql = "SELECT SUM(bt.amount) details_total
            FROM ".TB_PREF."batch_bank_transfer_details bbtd 
                JOIN ".TB_PREF."bank_trans bt 
                    ON bt.type = bbtd.source_type 
                    AND bt.trans_no = bbtd.source_trans_no 
            WHERE bbtd.type =".db_escape($type_id)." 
                AND bbtd.trans_no =".db_escape($trans_no);
    
    $result = db_query($sql, "get_batch_bank_transfer_details_total");
    return db_result_value($result, "details_total");   
    
}


function add_batch_bank_transfer_header($type, $trans_no, $reference, $amount,
                    $from_bank_act, $to_bank_act, $trans_date, $comment)
{  
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');         

    $sql = "INSERT INTO ".TB_PREF."batch_bank_transfer_header(
                    type, trans_no, 
                    reference, amount,
                    from_bank_act, to_bank_act,
                    trans_date, comment, 
                    created_by, created_on)
            VALUES (".db_escape($type).", ".db_escape($trans_no).", "
                    .db_escape($reference).", ".db_escape($amount).", "
                    .db_escape($from_bank_act).", ".db_escape($to_bank_act).", "
                    .db_escape($trans_date).", ".db_escape($comment).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";
    
    db_query($sql, "add_batch_bank_transfer_header");
    
    return db_insert_id();
}



function add_batch_bank_transfer_detail($type, $trans_no, $source_type, $source_trans_no)
{  
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');         

    $sql = "INSERT INTO ".TB_PREF."batch_bank_transfer_details(
                    type, trans_no, 
                    source_type, source_trans_no, 
                    created_by, created_on)
            VALUES (".db_escape($type).", ".db_escape($trans_no).", "
                    .db_escape($source_type).", ".db_escape($source_trans_no).", " 
                    .db_escape($created_by).", ".db_escape($created_on).")";
    
    db_query($sql, "add_batch_bank_transfer_detail");
    
    return db_insert_id();
}


function get_sql_for_batch_bank_transfer_requests_inquiry($from_date, $to_date, 
                                        $trans_no, $reference, 
                                        $from_bank_account, $to_bank_account) 
{
	$from = date2sql($from_date);
	$to = date2sql($to_date);
        
	$sql = "SELECT bbth.* 
                FROM ".TB_PREF."batch_bank_transfer_header bbth  
		WHERE bbth.trans_date >= ". db_escape($from)." 
		AND bbth.trans_date <= ". db_escape($to);
        
        if($trans_no > 0){
            $sql .= " AND bbth.trans_no = ". db_escape($trans_no);
        }
        if(strlen($reference) > 0){
            $sql .= " AND bbth.reference = ". db_escape($reference);
        }
        if($from_bank_account > 0){
            $sql .= " AND bbth.from_bank_act = ". db_escape($from_bank_account);
        }
        
        if($to_bank_account > 0){
            $sql .= " AND bbth.to_bank_act = ". db_escape($to_bank_account);
        }

        $sql .= " ORDER BY bbth.trans_no";

	return $sql;
}



function view_batch_bank_transfer_header($type_id, $trans_no)
{
    $sql = "SELECT b.*,
                    fba.bank_account_name from_bank_account_name,
                    fba.bank_curr_code from_bank_curr_code,
                    tba.bank_account_name to_bank_account_name,
                    tba.bank_curr_code to_bank_curr_code 
            FROM ".TB_PREF."batch_bank_transfer_header b 
                JOIN ".TB_PREF."bank_accounts fba 
                    ON fba.id = b.from_bank_act 
                JOIN ".TB_PREF."bank_accounts tba 
                    ON tba.id = b.to_bank_act 
                
            WHERE b.type =".db_escape($type_id)." 
                AND b.trans_no = ".db_escape($trans_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function view_batch_bank_transfer_details($type_id, $trans_no)
{
    $sql = "SELECT bbtr.*,
                    bt.ref reference,
                    bt.trans_date,
                    bt.person_type_id,
                    bt.person_id,
                    bt.gl_trans_type_id,
                    bt.person_detail_id,
                    bt.amount, 
                    gltn.value_name gl_trans_type_name 
            FROM ".TB_PREF."batch_bank_transfer_details bbtr 
                JOIN ".TB_PREF."bank_trans bt 
                    ON bt.type = bbtr.source_type 
                    AND bt.trans_no = bbtr.source_trans_no 
                LEFT JOIN ".TB_PREF."list_values gltn  
                    ON gltn.id = bt.gl_trans_type_id  
            WHERE bbtr.type =".db_escape($type_id)." 
                AND bbtr.trans_no = ".db_escape($trans_no);
    
    return db_query($sql, "get_batch_bank_transfer_details"); 
    
}


    
function update_batch_bank_transfer_details_removed($type_id, $trans_no)
{
    
    $user_id = $_SESSION['wa_current_user']->user;
    $time = date('Y-m-d H:i:s');         
    
    $sql = "UPDATE ".TB_PREF."batch_bank_transfer_details d  
                JOIN ".TB_PREF."batch_bank_transfer_header h 
                    ON h.type = d.type 
                    AND h.trans_no = d.trans_no 
            SET 
                d.removed_by = ".db_escape($user_id).",    
                d.removed_on = ".db_escape($time)."    
            WHERE d.type = ".db_escape($type_id)." 
                AND d.trans_no = ".db_escape($trans_no)." 
                AND h.fund_transfer_id > 0 ";

    db_query($sql, "update_batch_bank_transfer_header");    
    
    return db_num_affected_rows();
}