<?php

function write_pos_customer_payment($type, $pos_cus_pay_id, $reference, $trans_date, $float_id, $amount, $comments, $row_state, $source_type=-1, $source_trans_no=0) {
    global $Refs;
     
        if ($pos_cus_pay_id > 0) {
            $af_row = update_pos_customer_payment($pos_cus_pay_id, $reference, $trans_date, $float_id, $amount, $comments, $row_state);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $pos_cus_pay_id = insert_pos_customer_payment($type, $trans_no, $reference, $trans_date, $float_id, $amount, $comments, $source_type, $source_trans_no);
        }
    
        $Refs->save($type, $pos_cus_pay_id, $reference);

        if (! ($pos_cus_pay_id > 0)) {
            return 0;
        }

        return $pos_cus_pay_id;
}

function insert_pos_customer_payment($type, $trans_no, $reference, $trans_date, $float_id, $amount, $comments, $source_type=-1, $source_trans_no=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."pos_customer_payments
                      ( type, trans_no, reference,  
                        tran_date, float_id, amount, 
                        comments, source_type, source_trans_no,
                        created_by, created_on)         
                VALUES( ".db_escape($type).", ".db_escape($trans_no).", ".db_escape($reference).", 
                        ".db_escape(date2sql($trans_date)).", ".db_escape($float_id).", ".db_escape($amount).",
                        ".db_escape($comments).", ".db_escape($source_type).", ".db_escape($source_trans_no).", 
                        ".db_escape($created_by).", ".db_escape($created_on).")";

            db_query($sql, 'insert_pos_customer_payment');
            return db_insert_id();        
}

function update_pos_customer_payment($pos_cus_pay_id, $reference, $trans_date, $float_id, $amount, $comments, $row_state) {   
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."pos_customer_payments
                SET reference = ".db_escape($reference).",
                    tran_date = ".db_escape(date2sql($trans_date)).",
                    float_id = ".db_escape($float_id).",  
                    amount = ".db_escape($amount).",                          
                    comments = ".db_escape($comments).",  
                    row_state = ".db_escape(db_new_row_state($row_state)).",  
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($pos_cus_pay_id)."     
                    AND row_state = ".db_escape($row_state);    

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

function get_pos_customer_payment_record($pos_cus_pay_id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."pos_customer_payments    
            WHERE id = ".db_escape($pos_cus_pay_id);
    
        $result = db_query($sql, "get_pos_customer_payment_record");
        return db_fetch($result);
}

function get_pos_customer_payment_type_trans_no($type, $trans_no) {
    $sql = "SELECT sa.*,
                ft.name float_name 
            FROM ".TB_PREF."pos_customer_payments sa 
                JOIN ".TB_PREF."petty_cash_floats ft  
                    ON sa.float_id = ft.id  
            WHERE sa.type = ".db_escape($type)." 
                AND sa.trans_no = ".db_escape($trans_no);
    
        $result = db_query($sql, "get_pos_customer_payment_type_trans_no");
        return db_fetch($result);
}

function get_sql_for_pos_customer_payments_inquiry($trans_no, $tran_type, $reference, $from_date, $to_date, $float_id, $trans_status, $source_type, $source_trans_no) {
    $sql = "SELECT sa.id,
                sa.trans_no,
                sa.reference,
                sa.type,
                sa.amount,
                sa.tran_date,
                sa.approval_status, 
                ft.name float_name,  
                sa.source_type,  
                sa.source_trans_no   
            FROM ".TB_PREF."pos_customer_payments sa  
                JOIN ".TB_PREF."petty_cash_floats ft  
                    ON sa.float_id = ft.id  
            WHERE 1 = 1 ";
    
        if ($trans_no > 0 || $source_trans_no > 0) {
            if ($trans_no > 0) {
                $sql .= " AND sa.trans_no = ".db_escape($trans_no);
            }
            
            if ($source_trans_no > 0) {
                $sql .= " AND sa.source_trans_no = ".db_escape($source_trans_no);
            } 
        } else {

            if ($tran_type > 0) {
                $sql .= " AND sa.type = ".db_escape($tran_type);
            }
        
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }

            if ($float_id > 0) {
                $sql .= " AND sa.float_id = ".db_escape($float_id);
            }

            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 
            
            if ($trans_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($trans_status);
            } 

            if ($source_type > 0) {
                $sql .= " AND sa.source_type = ".db_escape($source_type);
            }
        }
    
        $sql .= " ORDER BY sa.id DESC, sa.tran_date DESC";
    
    return $sql;
}

function get_pos_customer_payments_total_by_source($source_type, $source_trans_no, $pos_cus_pay_id=0) {
    $sql = "SELECT SUM(amount) total_amount  
            FROM ".TB_PREF."pos_customer_payments 
            WHERE source_type = ".db_escape($source_type)."     
                AND source_trans_no = ".db_escape($source_trans_no);    
    
        if ($pos_cus_pay_id > 0) {
            $sql .= " AND id <> ".db_escape($pos_cus_pay_id);  
        }

        $result = db_query($sql, "get_pos_customer_payments_total_by_source");
        return db_result_value($result, "total_amount");
}

function calculate_and_update_pos_invoice_header_paid_amount($source_type, $source_trans_no, $row_state) {
    $paid_amount = get_pos_customer_payments_total_by_source($source_type, $source_trans_no);
    
    return update_pos_invoice_header_paid_amount($source_type, $source_trans_no, $paid_amount, $row_state);
}

function update_pos_customer_payment_row_state_by_id($id, $row_state) {
    $sql = "UPDATE ".TB_PREF."pos_customer_payments
            SET row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE id = ".db_escape($id)."   
                AND row_state = ".db_escape($row_state);    

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

function get_pos_customer_payments_by_source_type_source_trans_no($source_type, $source_trans_no) {
    $sql = "SELECT sa.*,
                ft.name float_name 
            FROM ".TB_PREF."pos_customer_payments sa 
                JOIN ".TB_PREF."petty_cash_floats ft  
                    ON sa.float_id = ft.id  
            WHERE sa.source_type = ".db_escape($source_type)."     
                AND sa.source_trans_no = ".db_escape($source_trans_no);    
    
        return db_query($sql, "get_pos_customer_payments_by_source_type_source_trans_no");
}

?>