<?php

function get_payment_vouchers_details($trans_type_id, $from_, $to_, $printed='All', $filtering_voucher_no=-1, $filtering_voucher_code='') {
    $from = date2sql($from_);
    $to = date2sql($to_);
    
    
    $select = 'pv.id rec_id, pv.voucher_type_id, pv.voucher_date, pv.voucher_reference, pv.voucher_code, vou.value_name voucher_name ';
    
        switch ($trans_type_id) {
            case ST_BANKPAYMENT:
            case ST_BANKTRANSFER:
            case ST_PETTY_CASH_FLOAT_REIMBUR:
                $trans_date = 'trans_date';
                $sql = " SELECT trn.trans_no, trn.type, trn.ref, trn.trans_date, trn.amount, 
                                trn.person_type_id, trn.person_id, trn.person_detail_id, trn.bank_act,  
                            $select
                         FROM ".TB_PREF."bank_trans trn   
                            LEFT JOIN ".TB_PREF."payment_vouchers pv 
                                ON trn.type = pv.type 
                                AND trn.trans_no = pv.trans_no  
                                AND pv.type = ".db_escape($trans_type_id)." 
                            LEFT JOIN ".TB_PREF."list_values vou 
                                ON vou.id = pv.voucher_type_id  ";
                break;
            case ST_CASH_PAYMENT:
                $trans_date = 'tran_date';
                $sql = " SELECT trn.trans_no, trn.type, trn.reference ref, trn.tran_date trans_date, trn.amount, 
                                trn.person_type_id, trn.person_id, 0 person_detail_id, 0 bank_act,   
                            $select
                         FROM ".TB_PREF."journal trn 
                            JOIN ".TB_PREF."pre_journal prej 
                                ON prej.type = trn.type 
                                AND prej.trans_no = trn.trans_no    
                            LEFT JOIN ".TB_PREF."payment_vouchers pv 
                                ON trn.type = pv.type 
                                AND trn.trans_no = pv.trans_no  
                                AND pv.type = ".db_escape($trans_type_id)." 
                            LEFT JOIN ".TB_PREF."list_values vou 
                                ON vou.id = pv.voucher_type_id  ";
                break;
            case ST_SUPPAYMENT:
                $trans_date = 'tran_date';
                $sql = " SELECT trn.trans_no, trn.type, trn.tran_date trans_date, trn.reference ref, trn.ov_amount amount, bnk.bank_act, 
                                '' person_type_id, '' person_id, 0 person_detail_id, $select 
                        FROM ".TB_PREF."supp_trans trn 
                            JOIN ".TB_PREF."bank_trans bnk 
                                ON trn.type = bnk.type 
                                    AND trn.trans_no = bnk.trans_no     
                            LEFT JOIN ".TB_PREF."payment_vouchers pv 
                                ON trn.type = pv.type 
                                AND trn.trans_no = pv.trans_no  
                                AND pv.type = ".db_escape($trans_type_id)." 
                            LEFT JOIN ".TB_PREF."list_values vou 
                                ON vou.id = pv.voucher_type_id  ";

                break;
            case ST_I_OWE_YOU_PAYMENT:
                $trans_date = 'payment_date';                
                $sql = " SELECT trn.id trans_no, ".$trans_type_id." type, trn.payment_date trans_date, '' ref, trn.amount, 0 bank_act, 
                                '' person_type_id, '' person_id, 0 person_detail_id, $select 
                        FROM ".TB_PREF."i_owe_you_payments trn   
                            LEFT JOIN ".TB_PREF."payment_vouchers pv 
                                ON trn.expense_source = ".db_escape(ST_I_OWE_YOU)."  
                                AND trn.id = pv.trans_no  
                                AND pv.type = ".db_escape($trans_type_id)." 
                            LEFT JOIN ".TB_PREF."list_values vou 
                                ON vou.id = pv.voucher_type_id  ";
                break;
				
			default :	
                display_error("Selected transaction type is not supported for Payment Voucher Printing.");	
                return;
        }
                    
            
        $sql .= " WHERE trn.$trans_date >= ".db_escape($from)."  
                    AND trn.$trans_date <= ".db_escape($to);
                
                if ($trans_type_id == ST_BANKTRANSFER) {
                    $sql .=" AND trn.amount < 0 ";
                }               

                if ($trans_type_id > 0) {
                    if ($trans_type_id != ST_I_OWE_YOU_PAYMENT) {
                        $sql .=" AND trn.type = ".db_escape($trans_type_id);
                    }
                } 

                if ($printed == 'Printed') {
                    $sql .= " AND pv.id > 0 ";
                } elseif ($printed == 'Not Printed') {
                    $sql .= " AND pv.id IS NULL";
                }
                if ($filtering_voucher_no > 0) {
                    $sql .=" AND pv.voucher_no = ".db_escape($filtering_voucher_no);
                }        
                if (strlen($filtering_voucher_code) > 0) {
                    $sql .=" AND pv.voucher_code LIKE ".db_escape('%'.$filtering_voucher_code.'%');
                }        

                $sql .= " ORDER BY trn.$trans_date, trans_no";

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

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

function get_max_payment_voucher_no($voucher_type_id, $bank_account_id) {
    $sql = "SELECT MAX(voucher_no) max_vou_no 
            FROM ".TB_PREF."payment_vouchers 
            WHERE voucher_type_id = ".db_escape($voucher_type_id)." 
                AND bank_account_id = ".db_escape($bank_account_id);
    
        $result = db_query($sql, "get_max_payment_voucher_no_by_voucher_type_no_bank_account_id");
        return db_result_value($result, 'max_vou_no', 0);   
}

function get_payment_voucher_by_filters($id=0, $trans_no=0, $type=0, $voucher_type_id=0, $voucher_no=0, $voucher_code='', $voucher_reference='') { 
    $sql = "SELECT * 
            FROM ".TB_PREF."payment_vouchers 
            WHERE 1 = 1 ";
    
        if ($id > 0) {
            $sql .= " AND id = ".db_escape($id);
        }
        if ($trans_no > 0) {
            $sql .= " AND trans_no = ".db_escape($trans_no);
        }
        if ($type > 0) {
            $sql .= " AND type = ".db_escape($type);
        }
        if ($voucher_type_id > 0) {
            $sql .= " AND voucher_type_id = ".db_escape($voucher_type_id);
        }
        if ($voucher_no > 0) {
            $sql .= " AND voucher_no = ".db_escape($voucher_no);
        }
        if (strlen($voucher_code) > 0) {
            $sql .= " AND voucher_code = ".db_escape($voucher_code);
        }
        if (strlen($voucher_reference) > 0) {
            $sql .= " AND voucher_reference = ".db_escape($voucher_reference);
        }
    
        return db_query($sql, "get_payment_voucher_record");
}

function update_payment_voucher($id, $bank_act_id, $vou_prefix, $voucher_code, $voucher_date) {
    $printed_by = $_SESSION['wa_current_user']->user;
    $printed_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."payment_vouchers  
            SET bank_account_id = ".db_escape($bank_act_id).",
                voucher_prefix = ".db_escape($vou_prefix).",   
                voucher_code = ".db_escape($voucher_code).",   
                voucher_date = ".db_escape($voucher_date).",   
                printed_by = ".db_escape($printed_by).",
                printed_on = ".db_escape($printed_on)."  
            WHERE id = ".db_escape($id);

	db_query($sql, "update_payment_voucher");
}

function add_payment_voucher($voucher_no, $type, $trans_no, $trans_date, $vou_type_id, $vou_date, $vou_reference, $vou_no, $vou_code, $bank_act_id, $vou_prefix) {
    $printed_by = $_SESSION['wa_current_user']->user;
    $printed_on = date('Y-m-d H:i:s');
    
	$sql = "INSERT INTO ".TB_PREF."payment_vouchers  
                    (id, type, trans_no, trans_date, 
                     voucher_type_id, voucher_date, 
                     voucher_reference, voucher_no,
                     voucher_code, bank_account_id, voucher_prefix, 
                     printed_by, printed_on) 
                VALUES(".db_escape($voucher_no).", ".db_escape($type).", 
                        ".db_escape($trans_no).", ".db_escape($trans_date).",
                       ".db_escape($vou_type_id).", ".db_escape($vou_date).",
                       ".db_escape($vou_reference).", ".db_escape($vou_no).",
                       ".db_escape($vou_code).", ".db_escape($bank_act_id).", ".db_escape($vou_prefix).", 
                       ".db_escape($printed_by).", ".db_escape($printed_on).")";
    
        db_query($sql, "add_payment_voucher");
        return db_insert_id();
}

function check_voucher_code_exisits($voucher_code, $rec_id=0) { 
    
    global $systypes_array;
    
    $sql = "SELECT * 
            FROM ".TB_PREF."payment_vouchers 
            WHERE voucher_code = ".db_escape($voucher_code);
        
    if ($rec_id > 0) {
        $sql .= " AND id <> ".db_escape($rec_id);
    }
    
    $result = db_query($sql, "get_payment_voucher_record");
    $row = db_fetch($result);
    if (is_array($row)) {        
        return array('success' => false, 'message' => 'Generated voucher code '.$voucher_code.' already used in the payment voucher of '.$systypes_array[$row['type']].' #'.$row['trans_no'].'.');
    }
    else{
        return array('success' => true, 'message' => '');
    }
            
}


function get_payment_vouchers_trans_type_record($type, $trans_no) {
    switch ($type) {
        case ST_BANKPAYMENT:
            $sql = " SELECT trn.trans_date  
                     FROM ".TB_PREF."bank_trans trn 
                     WHERE trn.type = ".db_escape($type)." 
                         AND trn.trans_no = ".db_escape($trans_no);

            break;
        case ST_BANKTRANSFER:
            $sql = " SELECT trn.trans_date  
                     FROM ".TB_PREF."bank_trans trn 
                     WHERE trn.type = ".db_escape($type)." 
                         AND trn.trans_no = ".db_escape($trans_no)." 
                         AND trn.amount < 0 ";

            break;
        case ST_CASH_PAYMENT:
            $sql = " SELECT trn.tran_date trans_date 
                     FROM ".TB_PREF."journal trn 
                     WHERE trn.type = ".db_escape($type)." 
                         AND trn.trans_no = ".db_escape($trans_no);

            break;
        case ST_SUPPAYMENT:
            $sql = " SELECT trn.tran_date trans_date                          
                     FROM ".TB_PREF."supp_trans trn 
                        JOIN ".TB_PREF."bank_trans bnk 
                            ON trn.type = bnk.type 
                                AND trn.trans_no = bnk.trans_no 
                     WHERE trn.type = ".db_escape($type)." 
                         AND trn.trans_no = ".db_escape($trans_no);
            break;
		case ST_PETTY_CASH_FLOAT_REIMBUR:	
            $sql = " SELECT trn.tran_date trans_date 	
                     FROM ".TB_PREF."i_owe_you_float_reimbursements trn 	
                     WHERE trn.id = ".db_escape($trans_no);	
            break;
        case ST_I_OWE_YOU_PAYMENT:
            $sql = " SELECT trn.payment_date trans_date                          
                     FROM ".TB_PREF."i_owe_you_payments trn  
                     WHERE trn.expense_source = ".db_escape(ST_I_OWE_YOU)." 
                         AND trn.id = ".db_escape($trans_no);
            break;
    }
    
        $result = db_query($sql, 'get_payment_vouchers_trans_type_record');
        return db_fetch($result);
}

function get_payment_vouchers_count_from_to_dates($from_date, $to_date) {
    $sql_1 = " SELECT pv.id  
                FROM ".TB_PREF."bank_trans trn 
                    JOIN ".TB_PREF."payment_vouchers pv 
                        ON trn.type = pv.type 
                            AND trn.trans_no = pv.trans_no  
                WHERE trn.trans_date >= ".db_escape($from_date)." 
                    AND trn.trans_date <= ".db_escape($to_date);

    $sql_2 = " SELECT pv.id  
                FROM ".TB_PREF."journal trn 
                    JOIN ".TB_PREF."payment_vouchers pv 
                        ON trn.type = pv.type 
                            AND trn.trans_no = pv.trans_no  
                WHERE trn.tran_date >= ".db_escape($from_date)." 
                    AND trn.tran_date <= ".db_escape($to_date);
    
    $sql_3 = " SELECT pv.id                        
                FROM ".TB_PREF."supp_trans trn 
                    JOIN ".TB_PREF."bank_trans bnk 
                        ON trn.type = bnk.type 
                            AND trn.trans_no = bnk.trans_no 
                    JOIN ".TB_PREF."payment_vouchers pv 
                           ON trn.type = pv.type 
                               AND trn.trans_no = pv.trans_no  
                WHERE trn.tran_date >= ".db_escape($from_date)." 
                    AND trn.tran_date <= ".db_escape($to_date);
    
        $union_sql = "$sql_1 UNION $sql_2 UNION $sql_3";
        
            $sql = "SELECT COUNT(DISTINCT id) count  
                    FROM ".TB_PREF."payment_vouchers  
                    WHERE id IN ($union_sql)";
            
        $sql = "SELECT COUNT(id) id_count  
                    FROM ".TB_PREF."payment_vouchers  
                    WHERE trans_date >= ".db_escape($from_date)." 
                            AND trans_date <= ".db_escape($to_date);
            
        $result = db_query($sql, "get_payment_vouchers_count_from_to_dates");
        return db_result_value($result, 'id_count', 0);   
}
