<?php

function get_bank_account_transactions_mismatched($bank_account_id, $gl_account_code) 
{
    
    $sql = "SELECT bnk_tr.id bank_gl_fi_tran_id, bnk_tr.type, bnk_tr.trans_no tran_no,
                    bnk_tr.trans_date tran_date, 
                    bnk_tr.amount bank_amount,
                    0 fi_amount,
                    gl_tr.amount gl_amount,
                    'Bank GL' bank_gl_fi_tran_type    
            FROM ".TB_PREF."bank_trans bnk_tr    
                LEFT JOIN ".TB_PREF."gl_trans gl_tr  
                    ON bnk_tr.type = gl_tr.type 
                        AND bnk_tr.trans_no = gl_tr.type_no  
            WHERE bnk_tr.type NOT IN (12,22,120) 
                    AND gl_tr.counter IS NULL";
    
    if (strlen($bank_account_id) > 0) {
        $sql .= " AND bnk_tr.bank_act = ".db_escape($bank_account_id);
    }
    
    $sql2 = "SELECT gl_tr2.counter bank_gl_fi_tran_id, gl_tr2.type, gl_tr2.type_no tran_no,
                    gl_tr2.tran_date, 
                    bnk_tr2.amount bank_amount,
                    0 fi_amount,
                    gl_tr2.amount gl_amount, 
                    'GL Bank' bank_gl_fi_tran_type 
            FROM ".TB_PREF."gl_trans gl_tr2  
                LEFT JOIN ".TB_PREF."bank_trans bnk_tr2   
                    ON bnk_tr2.type = gl_tr2.type 
                        AND bnk_tr2.trans_no = gl_tr2.type_no 
            WHERE gl_tr2.type NOT IN (204) 
                    AND bnk_tr2.id IS NULL";
    
    if (strlen($gl_account_code) > 0) {
        $sql2 .= " AND gl_tr2.account = ".db_escape($gl_account_code);
    }
    
    $sql3 = "SELECT bnk_tr3.id bank_gl_fi_tran_id, bnk_tr3.type, bnk_tr3.trans_no tran_no,
                    bnk_tr3.trans_date tran_date, 
                    bnk_tr3.amount bank_amount,                     
                    fi_tr3.amount fi_amount,
                    0 gl_amount,
                    'Bank FI' bank_gl_fi_tran_type 
            FROM ".TB_PREF."bank_trans bnk_tr3    
                LEFT JOIN ".TB_PREF."fi_trans fi_tr3  
                    ON bnk_tr3.type = fi_tr3.type 
                        AND bnk_tr3.trans_no = fi_tr3.type_no 
            WHERE bnk_tr3.type NOT IN (0,1,2,4) 
                    AND fi_tr3.counter IS NULL";
    
    if (strlen($bank_account_id) > 0) {
        $sql3 .= " AND bnk_tr3.bank_act = ".db_escape($bank_account_id);
    }
    
    $sql4 = "SELECT fi_tr4.counter bank_gl_fi_tran_id, fi_tr4.type, fi_tr4.type_no tran_no,
                    fi_tr4.tran_date, 
                    bnk_tr4.amount bank_amount,
                    fi_tr4.amount fi_amount, 
                    0 gl_amount,
                    'FI Bank' bank_gl_fi_tran_type 
            FROM ".TB_PREF."fi_trans fi_tr4  
                LEFT JOIN ".TB_PREF."bank_trans bnk_tr4   
                    ON bnk_tr4.type = fi_tr4.type 
                        AND bnk_tr4.trans_no = fi_tr4.type_no 
            WHERE bnk_tr4.id IS NULL";
    
    if (strlen($gl_account_code) > 0) {
        $sql4 .= " AND fi_tr4.account = ".db_escape($gl_account_code);
    }
    
//    $sql2 = "SELECT gl_tr2.counter bank_gl_fi_tran_id, gl_tr2.type, gl_tr2.type_no tran_no,
//                    gl_tr2.tran_date, gl_tr2.amount, 'GL Bank' bank_gl_fi_tran_type 
//            FROM ".TB_PREF."gl_trans gl_tr2  
//                LEFT JOIN ".TB_PREF."bank_trans bnk_tr2   
//                    ON bnk_tr2.type = gl_tr2.type 
//                        AND bnk_tr2.trans_no = gl_tr2.type_no 
//            WHERE bnk_tr2.id IS NULL";
//    
//    if (strlen($bank_account_code) > 0) {
//        $sql2 .= " AND gl_tr2.account = ".db_escape($bank_account_code);
//    }
    
    $sql5 = "$sql UNION $sql2 UNION $sql3 UNION $sql4";
//    $sql5 = "$sql2 ";
    
    $sql5 .= " ORDER BY tran_date, bank_gl_fi_tran_id";
    
    return db_query($sql5, 'get_bank_account_transactions_mismatched');
}