<?php

function get_receipts_details($trans_type_id, $from_, $to_, $printed='All', $filtering_receipt_code='') {
    $from = date2sql($from_);
    $to = date2sql($to_);
    
    $trans_date = 'tran_date';
    $select = 're.id rec_id, re.receipt_type_id, re.receipt_date, re.receipt_reference, re.receipt_code, vou.value_name receipt_name ';
    
        switch ($trans_type_id) {
            case ST_BANKDEPOSIT:
                $sql = " SELECT trn.*, $select
                         FROM ".TB_PREF."bank_trans trn ";
                
                    $trans_date = 'trans_date';

                break;
            case ST_CASH_RECEIPT:
                $sql = " SELECT trn.*, trn.tran_date trans_date, trn.reference ref, $select
                         FROM ".TB_PREF."journal trn ";

                break;
            case ST_CUSTPAYMENT:
                $sql = " SELECT trn.*, trn.tran_date trans_date, trn.reference ref, trn.ov_amount amount, '' person_type_id, '' person_id, $select 
                         FROM ".TB_PREF."debtor_trans trn ";

                break;
        }
                    
            $sql .= " LEFT JOIN ".TB_PREF."payment_receipts  re 
                        ON trn.type = re.type 
                            AND trn.trans_no = re.trans_no  
                            AND re.type = ".db_escape($trans_type_id)." 
                      LEFT JOIN ".TB_PREF."list_values vou 
                        ON vou.id = re.receipt_type_id  
        
                WHERE trn.$trans_date >= ".db_escape($from)."  
                    AND trn.$trans_date <= ".db_escape($to);

                if ($trans_type_id > 0) {
                    $sql .=" AND trn.type = ".db_escape($trans_type_id);
                }        
//                if ($receipt_type_id > 0) {
//                    $sql .=" AND re.receipt_type_id = ".db_escape($receipt_type_id);
//                }        

                if ($printed == 'Printed') {
                    $sql .= " AND re.id > 0 ";
                } elseif ($printed == 'Not Printed') {
                    $sql .= " AND re.id IS NULL";
                }
                if (strlen($filtering_receipt_code) > 0) {
                    $sql .=" AND pv.receipt_code LIKE ".db_escape('%'.$filtering_receipt_code.'%');
                }

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

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

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

function get_receipts_count_from_to_dates($from_date, $to_date) {
    
    $sql = "SELECT COUNT(id) id_count  
            FROM ".TB_PREF."payment_receipts  
            WHERE trans_date >= ".db_escape($from_date)." 
                  AND trans_date <= ".db_escape($to_date);
    
        $result = db_query($sql, "get_max_receipt_receipt_type_no");
        return db_result_value($result, 'id_count', 0);   
}

function get_receipt_by_filters($id=0, $trans_no=0, $type=0, $receipt_type_id=0, $receipt_no=0, $receipt_code='', $receipt_reference='') {
    $sql = "SELECT * 
            FROM ".TB_PREF."payment_receipts  
            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 ($receipt_type_id > 0) {
            $sql .= " AND receipt_type_id = ".db_escape($receipt_type_id);
        }
        if ($receipt_no > 0) {
            $sql .= " AND receipt_no = ".db_escape($receipt_no);
        }
        if (strlen($receipt_code) > 0) {
            $sql .= " AND receipt_code = ".db_escape($receipt_code);
        }
        if (strlen($receipt_reference) > 0) {
            $sql .= " AND receipt_reference = ".db_escape($receipt_reference);
        }
    
        return db_query($sql, "get_receipt_record");
}

function update_receipt($id, $vou_prefix, $receipt_code, $receipt_date) {
    
    $printed_by = $_SESSION['wa_current_user']->user;
    $printed_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."payment_receipts   
            SET receipt_prefix = ".db_escape($vou_prefix).",   
                receipt_code = ".db_escape($receipt_code).",   
                receipt_date  = ".db_escape($receipt_date).",   
                printed_by = ".db_escape($printed_by).",
                printed_on = ".db_escape($printed_on)."  
            WHERE id = ".db_escape($id);

    db_query($sql, "update_receipt");
        
}

function add_receipt($receipt_no, $type, $trans_no, $trans_date, $receipt_type_id, $receipt_date, $receipt_reference, $receipt_count, $receipt_code, $vou_prefix) {

    $printed_by = $_SESSION['wa_current_user']->user;
    $printed_on = date('Y-m-d H:i:s');
    
	$sql = "INSERT INTO ".TB_PREF."payment_receipts   
                    (id, type, 
                        trans_no, trans_date,  
                        receipt_type_id, receipt_date, 
                        receipt_reference, receipt_no,
                        receipt_code, receipt_prefix,
                        printed_by, printed_on) 
                VALUES(".db_escape($receipt_no).", ".db_escape($type).",
                        ".db_escape($trans_no).", ".db_escape($trans_date).",
                       ".db_escape($receipt_type_id).", ".db_escape($receipt_date).",
                       ".db_escape($receipt_reference).", ".db_escape($receipt_count).",
                       ".db_escape($receipt_code).", ".db_escape($vou_prefix).", 
                       ".db_escape($printed_by).", ".db_escape($printed_on).")";
    
        db_query($sql, "add_receipt");
        return db_insert_id();
}


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

function get_receipt_trans_type_record($trans_type, $trans_no) {
    
    switch ($trans_type) {
            case ST_BANKDEPOSIT:
                $result = get_bank_trans_new($trans_no, $trans_no, $trans_type);

                break;
            case ST_CASH_RECEIPT:
                $result = get_journal_trans_new($trans_no, $trans_no, $trans_type);

                break;
            case ST_CUSTPAYMENT:
                $result = get_customer_payments($trans_no, $trans_type);

                break;
        }
        
        $myrow = db_fetch($result);
        
        return $myrow;
    
}