<?php
/**********************************************************************
***********************************************************************/
function add_to_sales_upload_log($invoice_reference, $invoice_details, $filename)
{
	$added_by = $_SESSION['wa_current_user']->user;
        $added_on = date('Y-m-d H:i:s');
        
        $sql = "SELECT id FROM ".TB_PREF."sales_upload_log 
                WHERE invoice_reference = '".$invoice_reference."' AND 
                        filename = '".$filename."'";
        $result = db_query($sql,"Sales upload log could not be retrieved");
        $my_row = db_fetch($result);
        $id = $my_row['id'];

        if($my_row !=null){
                
            $sql = "UPDATE ".TB_PREF."sales_upload_log 
                    SET error_message = 'Messages cleared' 
                    WHERE id = ".$id;
        
	db_query($sql,"Sales upload log could not be updated");
            return $id;
        }
        else{        
            $sql = "INSERT INTO ".TB_PREF."sales_upload_log (
                            invoice_reference, invoice_details, 
                            filename,
                            added_by, added_on)
                    VALUES (".db_escape($invoice_reference). ",".db_escape($invoice_details) . ", "
                            .db_escape($filename) . ", "
                            .db_escape($added_by) . ", ".db_escape($added_on).")";

            db_query($sql,"Invoice details could not be added");


            return db_insert_id();
        }
}


function update_sales_upload_log($id,$invoice_reference, $filename, $uploaded = 0, $error_message = null)
{
        $sql = "SELECT error_message FROM ".TB_PREF."sales_upload_log WHERE id = ".$id;
        $result = db_query($sql,"Sales upload log could not be retrieved");
        $my_row = db_fetch($result);
        
        $added_on = date('Y-m-d H:i:s');
        
        $exisiting_errors = "";
        if($my_row !=null){
            $exisiting_errors = $my_row["error_message"];
            $all_errors = $exisiting_errors." | ".$error_message;
        }
                
        $sql = "UPDATE ".TB_PREF."sales_upload_log 
                SET uploaded = ".$uploaded.", error_message = ".db_escape($all_errors).",
                    added_on = '".$added_on."' 
		WHERE id = ".$id." AND 
                        invoice_reference = ".db_escape($invoice_reference). " AND 
                        filename = ".db_escape($filename);
        
	db_query($sql,"Sales upload log could not be updated");
        
}

function get_all_unuploaded_invoice_details()
{
    $sql = "SELECT invoice_details FROM ".TB_PREF."sales_upload_log WHERE uploaded <> 1";
        
	$result = db_query($sql,"Invoice details could not be retrieved");
        
        return $result;
}


function get_unuploaded_invoice_details($id)
{
    $sql = "SELECT * FROM ".TB_PREF."sales_upload_log 
            WHERE id = ".$id;
        
	$result = db_query($sql,"Invoice details could not be retrieved");
        
        return db_fetch($result);
}


function get_all_unuploaded_invoices()
{
    $sql = "SELECT * FROM ".TB_PREF."sales_upload_log ORDER BY added_on DESC LIMIT 30 ";
        
	$result = db_query($sql,"Invoice details could not be retrieved");
        
        return $result;
}



function get_customer_short_name($customer_id)
{
    $sql = "SELECT debtor_ref FROM ".TB_PREF."debtors_master 
            WHERE debtor_no = ".$customer_id;
        
	$result = db_query($sql,"Customer short name could not be retrieved");
        
        return db_fetch($result);
}


function get_customer_branch_code($customer_short_name)
{
    $sql = "SELECT branch_code FROM ".TB_PREF."cust_branch 
            WHERE branch_ref = '".$customer_short_name."'";
        
	$result = db_query($sql,"branch details could not be retrieved");
        
        return db_fetch($result);
}

function get_bank_account_id($bank_account_name)
{
    $sql = "SELECT id FROM ".TB_PREF."bank_accounts 
            WHERE bank_account_name = '".$bank_account_name."'";
        
	$result = db_query($sql,"Bank account id could not be retrieved");
        
        return db_fetch($result);
}

function get_members_customer_id($member_no)
{
        $sql = "SELECT debtor_no FROM ".TB_PREF."member_master 
            WHERE member_no = '".$member_no."'";
        
	$result = db_query($sql,"Members customer id could not be retrieved");
        
        return db_fetch($result);
    
}

function get_bank_account_name($list_code,$value_code)
{
    
    $sql="SELECT DISTINCT value_name FROM ".TB_PREF."list_values  
		WHERE list_code= ".db_escape($list_code)." AND value_code=".db_escape($value_code);

	$result = db_query($sql,"item kit could not be retrieved");
	
	return db_fetch($result);
}


function get_payment_record($reference)
{    
    $sql = "SELECT reference   
            FROM 
                ".TB_PREF."debtor_trans
            WHERE
                type = ".ST_CUSTPAYMENT." AND reference = ".db_escape($reference);
    
    $result = db_query($sql,"Payment record could not be retreived");
    
    return db_fetch($result);
    
}

function get_refund_record($reference)
{    
    $sql = "SELECT reference   
            FROM 
                ".TB_PREF."debtor_trans
            WHERE
                type = ".ST_BANKPAYMENT." AND reference = ".db_escape($reference);
    
    $result = db_query($sql,"Customer refund record could not be retreived");
    
    return db_fetch($result);
    
}


function get_customer_transaction_record($reference, $type)
{    
    $sql = "SELECT trans_no    
            FROM 
                ".TB_PREF."debtor_trans
            WHERE
                type = ".$type." AND 
                reference = ".db_escape($reference);
    
    $result = db_query($sql,"Customer transaction record could not be retreived");
    
    return db_fetch($result);
    
}

function get_customer_transaction_record_details($trans_no, $type)
{    
    $sql = "SELECT *     
            FROM 
                ".TB_PREF."debtor_trans
            WHERE
                trans_no = ".db_escape($trans_no)." AND 
                type = ".db_escape($type);
    
    $result = db_query($sql,"Customer transaction record could not be retreived");
    
    return db_fetch($result);
    
}

//=============================================================================================

function revers_debtor_trans_allocation($amount, $payment_id, $invoice_id){
    $sql = "UPDATE ".TB_PREF."debtor_trans 
            SET alloc=alloc - ".db_escape($amount)." 
            WHERE (type= 12 AND trans_no=".db_escape($payment_id).")
			OR (type=10 AND trans_no=".db_escape($invoice_id).")";
    
    db_query($sql,"Debtor allocation record could not be reversed");
    
}

function delete_cust_allocations($payment_id){
    $sql = "DELETE FROM ".TB_PREF."cust_allocations 
            WHERE (trans_type_from='12' AND trans_no_from=".db_escape($payment_id).")
                        OR (trans_type_to='12' AND trans_no_to=".db_escape($payment_id).")";
    
    db_query($sql,"Payment allocation record could not be deleted");
    
}

function create_cust_allocations($amount, $payment_id, $invoice_id){
    $sql = "INSERT INTO ".TB_PREF."cust_allocations (
                    amt, date_alloc,
                    trans_type_from, trans_no_from, 
                    trans_no_to, trans_type_to)
            VALUES (".db_escape($amount).", Now(), 
                    '12', ".db_escape($payment_id).", 
                    ".db_escape($invoice_id).", '10')";
    
    db_query($sql,"Payment allocation record could not be added");
    
    return db_insert_id();
}

function allocate_debtor_trans_invoice($invoice_id){
    $sql =  "UPDATE ".TB_PREF."debtor_trans trans,
			(SELECT sum(amt) amt from ".TB_PREF."cust_allocations
				WHERE (trans_type_to='10' AND trans_no_to=".db_escape($invoice_id).")
				OR (trans_type_from='10' AND trans_no_from=".db_escape($invoice_id).")) allocated
            SET 
                    trans.alloc=IFNULL(allocated.amt,0)
            WHERE trans.type='10' AND trans_no=".db_escape($invoice_id);
    
    db_query($sql,"Debtor invoice allocation could not be updated");
    
    return db_num_affected_rows();
}

function allocate_debtor_trans_payment($payment_id){
    $sql = "UPDATE ".TB_PREF."debtor_trans trans,
			(SELECT sum(amt) amt from ".TB_PREF."cust_allocations
				WHERE (trans_type_to='12' AND trans_no_to=".db_escape($payment_id).")
				OR (trans_type_from='12' AND trans_no_from=".db_escape($payment_id).")) allocated
            SET 
                    trans.alloc=IFNULL(allocated.amt,0)
            WHERE trans.type='12' AND trans_no=".db_escape($payment_id);
    
    db_query($sql,"Debtor payment allocation could not be updated");
    
    return db_num_affected_rows();
}


//=============================================================================================

function revers_creditnote_refund_allocation($amount, $refund_id, $creditnote_id){
    $sql = "UPDATE ".TB_PREF."debtor_trans 
            SET alloc=alloc - ".db_escape($amount)." 
            WHERE (type= 1 AND trans_no=".db_escape($refund_id).")
			OR (type=11 AND trans_no=".db_escape($creditnote_id).")";
    
    db_query($sql,"Credit note refund allocation record could not be reversed");
    
}

function delete_refund_allocations($refund_id){
    $sql = "DELETE FROM ".TB_PREF."cust_allocations 
            WHERE (trans_type_from='1' AND trans_no_from=".db_escape($refund_id).")
                        OR (trans_type_to='1' AND trans_no_to=".db_escape($refund_id).")";
    
    db_query($sql,"Refund allocation record could not be deleted");
    
}

function create_creditnote_refund_allocations($amount, $refund_id, $creditnote_id){
    $sql = "INSERT INTO ".TB_PREF."cust_allocations (
                    amt, date_alloc,
                    trans_type_from, trans_no_from, 
                    trans_no_to, trans_type_to)
            VALUES (".db_escape($amount).", Now(), 
                    '1', ".db_escape($refund_id).", 
                    ".db_escape($creditnote_id).", '11')";
    
    db_query($sql,"Refund allocation record could not be added");
    
    return db_insert_id();
}

function allocate_debtor_trans_creditnote($creditnote_id){
    $sql =  "UPDATE ".TB_PREF."debtor_trans trans,
			(SELECT sum(amt) amt from ".TB_PREF."cust_allocations
				WHERE (trans_type_to='11' AND trans_no_to=".db_escape($creditnote_id).")
				OR (trans_type_from='11' AND trans_no_from=".db_escape($creditnote_id).")) allocated
            SET 
                    trans.alloc=IFNULL(allocated.amt,0)
            WHERE trans.type='11' AND trans_no=".db_escape($creditnote_id);
    
    db_query($sql,"Credit note allocation could not be updated");
    
    return db_num_affected_rows();
}

function allocate_debtor_trans_refund($refund_id){
    $sql = "UPDATE ".TB_PREF."debtor_trans trans,
			(SELECT sum(amt) amt from ".TB_PREF."cust_allocations
				WHERE (trans_type_to='1' AND trans_no_to=".db_escape($refund_id).")
				OR (trans_type_from='1' AND trans_no_from=".db_escape($refund_id).")) allocated
            SET 
                    trans.alloc=IFNULL(allocated.amt,0)
            WHERE trans.type='1' AND trans_no=".db_escape($refund_id);
    
    db_query($sql,"Refund allocation could not be updated");
    
    return db_num_affected_rows();
}

function create_pos_customer_account_payment($debtor_no, $branch_code, 
                        $tran_date, $reference, $invoice_no, 
                        $amount, $rate = 1)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
    $sql = "INSERT INTO ".TB_PREF."pos_customer_account_payments (
                        debtor_no, branch_code, 
                        tran_date, reference, invoice_no, 
                        amount, rate,
                        created_by, created_on) 
            VALUES (".db_escape($debtor_no).", ".db_escape($branch_code).", 
                    ".db_escape(date2sql($tran_date)).", ".db_escape($reference).",".db_escape($invoice_no).", 
                    ".db_escape($amount).", ".db_escape($rate).",
                    ".db_escape($created_by).", ".db_escape($created_on).")";
    
    db_query($sql,"POS customer account payment could not be added");
    
    return db_insert_id();
    
    
}

?>