<?php

function get_customer_payments_summary($tran_type, $from_date, $to_date) {
    $sql = "SELECT SUM(dt.ov_amount) total_cost 
            FROM ".TB_PREF."debtor_trans dt  
                JOIN ".TB_PREF."bank_trans bt 
                    ON bt.type = dt.type 
                    AND bt.trans_no = dt.trans_no 
                JOIN ".TB_PREF."bank_accounts a  
                    ON bt.bank_act = a.id 
            WHERE a.internal = 0 
                AND dt.type = ".db_escape($tran_type)." 
                AND dt.tran_date >= ".db_escape($from_date)." 
                AND dt.tran_date <= ".db_escape($to_date);
    
        $result = db_query($sql, "Could not get_customer_payments_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_customer_payments($tran_type, $from_date, $to_date) {
    $sql = "SELECT dt.trans_no, dt.reference, dt.tran_date, dt.debtor_no, SUM(dt.ov_amount) total_cost,
                b.bank_account_name 
            FROM ".TB_PREF."debtor_trans dt 
                JOIN ".TB_PREF."bank_trans bt 
                    ON bt.type = dt.type 
                    AND bt.trans_no = dt.trans_no 
                JOIN ".TB_PREF."bank_accounts b  
                    ON b.id = bt.bank_act  
            WHERE b.internal = 0 
                AND dt.type = ".db_escape($tran_type)." 
                AND dt.tran_date >= ".db_escape($from_date)." 
                AND dt.tran_date <= ".db_escape($to_date)." 
            GROUP by bt.trans_no  
            ORDER by b.bank_account_name , dt.tran_date";
    
        return db_query($sql, "Could not get_customer_payments");
}

function get_suplier_credit_notes_summary($tran_type, $from_date, $to_date) {
    $sql = "SELECT SUM(ov_amount) total_cost 
            FROM ".TB_PREF."supp_trans   
            WHERE type = ".db_escape($tran_type)." 
                AND tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date);
    
        $result = db_query($sql, "Could not get_suplier_credit_notes_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_suplier_credit_notes($tran_type, $from_date, $to_date) {
    $sql = "SELECT trans_no, reference, tran_date, supplier_id, 
                SUM(ov_amount) total_cost  
            FROM ".TB_PREF."supp_trans   
            WHERE type = ".db_escape($tran_type)." 
                AND tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date)." 
            GROUP by trans_no  
            ORDER by tran_date";
    
        return db_query($sql, "Could not get_customer_payments");
}

function get_iou_payments_summary($from_date, $to_date, $is_return) {
    $sql = "SELECT SUM(ioup.amount) total_cost 
            FROM ".TB_PREF."i_owe_you_payments ioup 
                JOIN ".TB_PREF."i_owe_you iou   
                    ON ioup.iou_id = iou.id  
                JOIN ".TB_PREF."list_values tp    
                    ON tp.id = iou.type  
            WHERE tp.list_code = 'PETTY_CASH_TYPES'  
                AND tp.value_code = 'I_OWE_YOU'  
                AND ioup.payment_date >= ".db_escape($from_date)." 
                AND ioup.payment_date <= ".db_escape($to_date)." 
                AND ioup.is_return = ".db_escape($is_return);
    
        $result = db_query($sql, "Could not get_iou_payments_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_iou_payments($from_date, $to_date, $is_return) {
    $sql = "SELECT ioup.id trans_no, '' reference, ioup.payment_date tran_date, iou.employee_code, iou.company_code, 
                SUM(ioup.amount) total_cost,
                pf.name float_name 
            FROM ".TB_PREF."i_owe_you_payments ioup 
                JOIN ".TB_PREF."i_owe_you iou   
                    ON ioup.iou_id = iou.id  
                JOIN ".TB_PREF."petty_cash_floats pf     
                    ON pf.id = ioup.float_id  
            WHERE ioup.payment_date >= ".db_escape($from_date)." 
                AND ioup.payment_date <= ".db_escape($to_date)." 
                AND ioup.is_return = ".db_escape($is_return)." 
                AND ioup.paid = 1  
            GROUP by iou.id   
            ORDER by iou.tran_date";
    
        return db_query($sql, "Could not get_iou_payments");
}

function get_bank_transactions_summary($tran_type, $from_date, $to_date) {
    $sql = "SELECT SUM(bt.amount) total_cost 
            FROM ".TB_PREF."bank_trans bt   
                JOIN ".TB_PREF."bank_accounts a  
                    ON bt.bank_act = a.id 
            WHERE a.internal = 0 
                AND bt.type = ".db_escape($tran_type)." 
                AND bt.trans_date >= ".db_escape($from_date)." 
                AND bt.trans_date <= ".db_escape($to_date);
    
        $result = db_query($sql, "Could not get_bank_transactions_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_bank_transactions($tran_type, $from_date, $to_date) {
    $sql = "SELECT bt.trans_no, bt.ref reference, bt.trans_date, bt.person_id, bt.person_type_id, 
                SUM(bt.amount) total_cost,
                b.bank_account_name 
            FROM ".TB_PREF."bank_trans bt    
                JOIN ".TB_PREF."bank_accounts b  
                    ON b.id = bt.bank_act  
            WHERE b.internal = 0 
                AND bt.type = ".db_escape($tran_type)." 
                AND bt.trans_date >= ".db_escape($from_date)." 
                AND bt.trans_date <= ".db_escape($to_date)." 
            GROUP by bt.trans_no  
            ORDER by bt.trans_date";
    
        return db_query($sql, "Could not get_bank_transactions");
}

function get_journal_transactions_summary($tran_type, $from_date, $to_date) {
    $sql = "SELECT SUM(amount) total_cost 
            FROM ".TB_PREF."journal  
            WHERE type = ".db_escape($tran_type)." 
                AND tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date);
    
        $result = db_query($sql, "Could not get_journal_transactions_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_journal_transactions($tran_type, $from_date, $to_date) {
    $sql = "SELECT j.trans_no, j.reference, j.tran_date, j.person_id, j.person_type_id, 
                SUM(j.amount) total_cost,
                pcf.name float_name 
            FROM ".TB_PREF."journal j      
                JOIN ".TB_PREF."petty_cash_floats pcf      
                    ON j.float_id = pcf.id 
            WHERE j.type = ".db_escape($tran_type)." 
                AND j.tran_date >= ".db_escape($from_date)." 
                AND j.tran_date <= ".db_escape($to_date)." 
            GROUP by j.trans_no  
            ORDER by j.tran_date";
    
        return db_query($sql, "Could not journal_transactions");
}

function get_company_loan_repayments_summary($from_date, $to_date, $is_return) {
    $sql = "SELECT SUM(payment_amount + payment_interest_amount + late_interest_amount) total_cost 
            FROM ".TB_PREF."company_loan_payments       
            WHERE tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date)." 
                AND is_return = ".db_escape($is_return);
        
        $result = db_query($sql, "Could not get_company_loan_repayments_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_company_loan_repayments($from_date, $to_date, $is_return) {
    $sql = "SELECT lnp.id, lnp.loan_id, lnp.reference, lnp.tran_date, 
                lon.name, lon.description, lon.loan_amount, lon.interest,  
                SUM(lnp.payment_amount + lnp.payment_interest_amount + lnp.late_interest_amount) total_cost,
                ba.bank_account_name 
            FROM ".TB_PREF."company_loans lon      
                JOIN ".TB_PREF."company_loan_payments lnp 
                    ON lon.id = lnp.loan_id 
                JOIN ".TB_PREF."bank_accounts ba 
                    ON ba.id = lnp.bank_account_id 
            WHERE lnp.tran_date >= ".db_escape($from_date)." 
                AND lnp.tran_date <= ".db_escape($to_date)." 
                AND lnp.is_return = ".db_escape($is_return)." 
            GROUP by lnp.id    
            ORDER by lnp.tran_date, lnp.id";
    
        return db_query($sql, "Could not get_company_loan_repayments");
}

function get_customer_credit_notes_summary($tran_type, $from_date, $to_date) {    
    $sql = "SELECT SUM(ov_amount - ov_discount) total_cost  
            FROM ".TB_PREF."debtor_trans  
            WHERE type = ".db_escape($tran_type)." 
                AND tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date);
    
    $result = db_query($sql, "get_customer_credit_notes_summary");
    
        $row = db_fetch($result);
            if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                return $row['total_cost'];
            } else {
                return 0;
            }
}

function get_customer_credit_notes($tran_type, $from_date, $to_date) {    
    $sql = "SELECT trans_no, reference, tran_date, debtor_no, 
                SUM(ov_amount - ov_discount) total_cost   
            FROM ".TB_PREF."debtor_trans  
            WHERE type = ".db_escape($tran_type)." 
                AND tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date)." 
            GROUP by trans_no     
            ORDER by tran_date";
    
        return db_query($sql, "get_customer_credit_notes");
}

function get_supplier_payments_summary($tran_type, $from_date, $to_date) {    
    $sql = "SELECT SUM(st.ov_amount - st.ov_discount) total_cost  
            FROM ".TB_PREF."supp_trans st   
                JOIN ".TB_PREF."bank_trans bt 
                    ON bt.type = st.type 
                    AND bt.trans_no = st.trans_no 
                JOIN ".TB_PREF."bank_accounts b  
                    ON b.id = bt.bank_act  
            WHERE b.internal = 0 
                AND st.type = ".db_escape($tran_type)." 
                AND st.tran_date >= ".db_escape($from_date)." 
                AND st.tran_date <= ".db_escape($to_date);
    
    $result = db_query($sql, "get_supplier_payments_summary");
    
        $row = db_fetch($result);
            if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                return $row['total_cost'];
            } else {
                return 0;
            }
}

function get_supplier_payments($tran_type, $from_date, $to_date) {    
    $sql = "SELECT st.trans_no, st.reference, st.tran_date, st.supplier_id, 
                SUM(st.ov_amount) total_cost,
                b.bank_account_name 
            FROM ".TB_PREF."supp_trans st  
                JOIN ".TB_PREF."bank_trans bt 
                    ON bt.type = st.type 
                    AND bt.trans_no = st.trans_no 
                JOIN ".TB_PREF."bank_accounts b  
                    ON b.id = bt.bank_act  
            WHERE b.internal = 0 
                AND st.type = ".db_escape($tran_type)." 
                AND st.tran_date >= ".db_escape($from_date)." 
                AND st.tran_date <= ".db_escape($to_date)." 
            GROUP by st.trans_no     
            ORDER by st.tran_date";
    
        return db_query($sql, "get_supplier_payments");
}

function get_iou_advances_summary($from_date, $to_date) {
    $sql = "SELECT SUM(ioua.amount) total_cost 
            FROM ".TB_PREF."i_owe_you iou 
                JOIN ".TB_PREF."i_owe_you_advances_details ioua 
            WHERE iou.id = ioua.iou_id 
                AND iou.tran_date >= ".db_escape($from_date)." 
                AND iou.tran_date <= ".db_escape($to_date);
            
        $result = db_query($sql, "get_iou_advances_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_iou_advances($from_date, $to_date) {
    $sql = "SELECT iou.id trans_no, iou.reference, iou.tran_date, iou.company_code, iou.employee_code, 
                SUM(ioua.amount) total_cost,
                pf.name float_name 
            FROM ".TB_PREF."i_owe_you iou   
                JOIN ".TB_PREF."i_owe_you_advances_details ioua 
                    ON ioua.iou_id = iou.id  
                JOIN ".TB_PREF."petty_cash_floats pf     
                    ON pf.id = iou.float_id  
            WHERE iou.tran_date >= ".db_escape($from_date)." 
                AND iou.tran_date <= ".db_escape($to_date)." 
            GROUP by iou.id   
            ORDER by iou.tran_date";
    
        return db_query($sql, "Could not get_iou_advances");
}

function get_bank_account_other_receipts_payments_summary($tran_type, $from_date, $to_date, $receipt_payment) {
    $sql = "SELECT SUM(bt.amount) total_cost 
            FROM ".TB_PREF."bank_trans bt   
                JOIN ".TB_PREF."bank_accounts b  
                    ON b.id = bt.bank_act  
            WHERE b.internal = 0 
                AND bt.type = ".db_escape($tran_type)." 
                AND bt.trans_date >= ".db_escape($from_date)." 
                AND bt.trans_date <= ".db_escape($to_date);
    
        if ($receipt_payment === 1) {
            $sql .= " AND bt.amount > 0 ";
        } else {
            $sql .= " AND bt.amount < 0 ";
        }
    
        $result = db_query($sql, "Could not get_bank_account_other_receipts_payments_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_bank_account_other_receipts_payments($tran_type, $from_date, $to_date, $receipt_payment) {
    $sql = "SELECT bt.trans_no, bt.ref reference, bt.trans_date, 
                    bt.person_type_id, bt.person_id, SUM(bt.amount) total_cost,
                    ba.bank_account_name 
            FROM ".TB_PREF."bank_trans bt    
                JOIN ".TB_PREF."bank_accounts ba 
                    ON bt.bank_act = ba.id 
            WHERE ba.internal = 0 
                AND type = ".db_escape($tran_type)." 
                AND trans_date >= ".db_escape($from_date)." 
                AND trans_date <= ".db_escape($to_date);
    
        if ((int)$receipt_payment === 1) {
            $sql .= " AND amount > 0 ";
        } else {
            $sql .= " AND amount < 0 ";
        }
        
        $sql .= " GROUP by trans_no  
                  ORDER by trans_date";
    
        return db_query($sql, "Could not get_bank_account_other_receipts_payments");
}

function get_month_reporting_adjustments_summary($tran_type, $from_date, $to_date) {
    $sql = "SELECT SUM(amount) total_cost 
            FROM ".TB_PREF."month_reporting_adjustments     
            WHERE report_adjust_type_id = ".db_escape($tran_type)." 
                AND date >= ".db_escape($from_date)." 
                AND date <= ".db_escape($to_date);
    
        $result = db_query($sql, "Could not get_month_reporting_adjustments_summary");

            $row = db_fetch($result);
                if (isset($row['total_cost']) && $row['total_cost'] != 0) {
                    return $row['total_cost'];
                } else {
                    return 0;
                }
}

function get_month_reporting_adjustments($tran_type, $from_date, $to_date) {
    $sql = "SELECT id, date,  
                SUM(amount) total_cost  
            FROM ".TB_PREF."month_reporting_adjustments     
            WHERE report_adjust_type_id = ".db_escape($tran_type)." 
                AND date >= ".db_escape($from_date)." 
                AND date <= ".db_escape($to_date)." 
            GROUP by id  
            ORDER by date";
    
        return db_query($sql, "Could not get_month_reporting_adjustments");
}

function get_bank_accounts_and_opening_balance($from_date, $to_date, $internal) {
    $sql = "SELECT ba.id, ba.bank_account_name ,SUM(bt.amount) opening_balance 
            FROM ".TB_PREF."bank_trans bt    
                JOIN ".TB_PREF."bank_accounts ba 
                    ON bt.bank_act = ba.id 
            WHERE bt.trans_date >= ".db_escape($from_date)." 
                AND bt.trans_date <= ".db_escape($to_date)." 
                AND ba.internal = ".db_escape($internal)." 
            GROUP by ba.id  
            ORDER by ba.bank_account_name";
    
        return db_query($sql, "Could not get_all_bank_accounts");
}

function get_company_cash_folw_bank_accounts_summary($bank_account_no, $from_date, $to_date, $tran_type_in=null) {
    $sql = "SELECT SUM( CASE WHEN type = ".db_escape(ST_CUSTPAYMENT)." THEN amount ELSE 0 END ) customer_payment, 
                    SUM( CASE WHEN type = ".db_escape(ST_SUPPAYMENT)." THEN amount ELSE 0 END ) supplier_payment, 
                    SUM( CASE WHEN type = ".db_escape(ST_BANKDEPOSIT)." THEN amount ELSE 0 END ) bank_deposit, 
                    SUM( CASE WHEN type = ".db_escape(ST_BANKPAYMENT)." THEN amount ELSE 0 END ) bank_payment, 
                    SUM( CASE WHEN type = ".db_escape(ST_COMP_LOAN_REPAYMENT)." THEN amount ELSE 0 END ) company_loan_payment, 
                    SUM( CASE WHEN type = ".db_escape(ST_PETTY_CASH_FLOAT_REIMBUR)." THEN amount ELSE 0 END ) petty_cash_foat_reimbursment, 
                    SUM( CASE WHEN type = ".db_escape(ST_BANKTRANSFER)." THEN amount ELSE 0 END ) banktransfer, 
                    SUM( CASE WHEN type = ".db_escape(ST_JOURNAL)." THEN amount ELSE 0 END ) journal, 
                    SUM( CASE WHEN type = ".db_escape(ST_GL_DEBIT)." THEN amount ELSE 0 END ) gl_debit, 
                    SUM( CASE WHEN type = ".db_escape(ST_GL_CREDIT)." THEN amount ELSE 0 END ) gl_credit,   

                    SUM(amount) total_of_all_types  

            FROM ".TB_PREF."bank_trans    
            WHERE bank_act = ".db_escape($bank_account_no)." 
                AND trans_date >= ".db_escape($from_date)." 
                AND trans_date <= ".db_escape($to_date);
    
                if (! ($tran_type_in === null)) {
                    $sql .= " AND type IN ( $tran_type_in )";
                }
    
            $result = db_query($sql, "Could not get_company_cash_folw_bank_accounts_summary");
                return db_fetch($result);
}

?>