<?php
/**********************************************************************
***********************************************************************/
//-------------------------------------------------------------------------------------------------------------
//
//	FIXME - this should be revised for transaction update case.
//
function write_supp_trans($type, $trans_no, $supplier_id, $date_, $due_date, $reference, $supp_reference,
                            $amount, $amount_tax, $discount, $err_msg="", $rate=0, $included=0, 
                            $po_no = 0, $float_id = 0, $tax_group_id = 0, $amount_stax=0)
{
	if(!($trans_no > 0)){
            $new = $trans_no == 0;
        }
        else{
            $new = 0;
        }
        
	$date = date2sql($date_);
	if ($due_date == ""){
		$due_date = "1970-01-01";
        }
	else{
		$due_date = date2sql($due_date);
        }

	if(!($trans_no > 0)){
		$trans_no = get_next_trans_no($type);
        }

	$curr = get_supplier_currency($supplier_id);
	
	if ($rate == 0){
		$rate = get_exchange_rate_from_home_currency($curr, $date_);
        }

        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
	$sql = "INSERT INTO ".TB_PREF."supp_trans (trans_no, type, supplier_id, tran_date, due_date,
                                                    reference, supp_reference, ov_amount, 
                                                    ov_gst, ov_stax, rate, ov_discount, 
                                                    tax_included, po_no, float_id, tax_group_id, created_by, created_on) ";
	$sql .= "VALUES (".db_escape($trans_no).", ".db_escape($type).", "
                .db_escape($supplier_id).", '$date', '$due_date', "
                .db_escape($reference).", ".db_escape($supp_reference).", ".db_escape($amount).", "
                .db_escape($amount_tax).", ".db_escape($amount_stax).", ".db_escape($rate).", ".db_escape($discount).", "
                .db_escape($included).", ".db_escape($po_no).", ".db_escape($float_id).", "
                .db_escape($tax_group_id).", "
                .db_escape($created_by).", ".db_escape($created_on).")";

	if ($err_msg == ""){
		$err_msg = "Cannot insert a supplier transaction record";
    }

	db_query($sql, $err_msg);
	add_audit_trail($type, $trans_no, $date_);

	return $trans_no;
}

//-------------------------------------------------------------------------------------------------------------

function get_supp_trans($trans_no, $trans_type=-1)
{
	$sql = "SELECT supp_trans.*, (supp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount) AS Total,
                    suppliers.supp_name AS supplier_name, suppliers.curr_code AS curr_code ";

	if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT)
	{
		// it's a payment so also get the bank account
		$sql .= ", bank_accounts.bank_name, bank_accounts.bank_account_name, bank_accounts.bank_curr_code,
                    bank_accounts.account_type AS BankTransType, bank_trans.amount AS bank_amount,
                    bank_trans.ref, bank_trans.cheque_no ";
	}

	$sql .= " FROM ".TB_PREF."supp_trans supp_trans, ".TB_PREF."suppliers suppliers";

	if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT)
	{
		// it's a payment so also get the bank account
		$sql .= ", ".TB_PREF."bank_trans bank_trans, ".TB_PREF."bank_accounts bank_accounts";
	}

	$sql .= " WHERE supp_trans.trans_no = ".db_escape($trans_no)."
                    AND supp_trans.supplier_id = suppliers.supplier_id";

	if ($trans_type > 0){
		$sql .= " AND supp_trans.type=".db_escape($trans_type);
    }

	if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT)
	{
		// it's a payment so also get the bank account
		$sql .= " AND bank_trans.trans_no =".db_escape($trans_no)."
                    AND bank_trans.type = ".db_escape($trans_type)."
                    AND bank_accounts.id = bank_trans.bank_act ";
	}

	$result = db_query($sql, "Cannot retreive a supplier transaction");

    if (db_num_rows($result) == 0)
    {
       // can't return nothing
       display_db_error("no supplier trans found for given params", $sql, true);
       exit;
    }

    if (db_num_rows($result) > 1)
    {
       // can't return multiple
       display_db_error("duplicate supplier transactions found for given params", $sql, true);
       exit;
    }

    return db_fetch($result);
}
//-------------------------------------------------------------------------------------------------------------

function get_supp_trans_record($trans_no, $trans_type)
{
	$sql = "SELECT * 
                FROM ".TB_PREF."supp_trans 
                WHERE trans_no = ".db_escape($trans_no)."
                    AND type = ".db_escape($trans_type);

	$result = db_query($sql, "get_supp_trans_record");

        return db_fetch($result);
}

//----------------------------------------------------------------------------------------

function exists_supp_trans($type, $type_no)
{
	if ($type == ST_SUPPRECEIVE)
		return exists_grn($type_no);

	$sql = "SELECT trans_no FROM ".TB_PREF."supp_trans WHERE type=".db_escape($type)."
		AND trans_no=".db_escape($type_no);
	$result = db_query($sql, "Cannot retreive a supplier transaction");

    return (db_num_rows($result) > 0);
}

//----------------------------------------------------------------------------------------

function void_supp_trans($type, $type_no)
{
	$sql = "UPDATE ".TB_PREF."supp_trans SET ov_amount=0, ov_discount=0, ov_gst=0,
		alloc=0 WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);

	db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no");
}

//----------------------------------------------------------------------------------------

function clear_supp_trans($type, $type_no)
{
	$sql = "DELETE FROM ".TB_PREF."supp_trans 
			WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);

	db_query($sql, "could not clear supp transactions for type=$type and trans_no=$type_no");
}
//----------------------------------------------------------------------------------------

function post_void_supp_trans($type, $type_no)
{
	if ($type == ST_SUPPAYMENT)
	{
		void_supp_payment($type, $type_no);
		return true;
	}

	if ($type == ST_SUPPINVOICE || $type == ST_SUPPCREDIT)
	{
	 	void_supp_invoice($type, $type_no);
		return true;
	}

	if ($type == ST_SUPPRECEIVE)
	{
	 	return void_grn($type_no);
	}

	return false;
}

//----------------------------------------------------------------------------------------

function get_sql_for_supplier_inquiry($filter, $after_date, $to_date, $supplier_id=ALL_TEXT, $tran_no = null)
{
    $date_after = date2sql($after_date);
    $date_to = date2sql($to_date);

    $sql1 = "SELECT trans.type, 
		trans.trans_no,
		trans.reference, 
		supplier.supp_name, 
		trans.supp_reference,
                trans.tran_date, 
		trans.due_date,
		supplier.curr_code, 
                (trans.ov_amount + trans.ov_gst  + trans.ov_discount) AS TotalAmount, 
		trans.alloc AS Allocated,
		((trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.") AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue,
                (ABS(trans.ov_amount + trans.ov_gst  + trans.ov_discount) - trans.alloc <= ".FLOAT_COMP_DELTA.") AS Settled
            FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as supplier
            WHERE supplier.supplier_id = trans.supplier_id
                AND trans.tran_date >= '$date_after'
                AND trans.tran_date <= '$date_to'
		AND trans.ov_amount != 0";	// exclude voided transactions

    $sql2 = "SELECT ".ST_SUPPRECEIVE." as type, 
		trans.id as trans_no,
		trans.reference, 
		supplier.supp_name, 
		po.requisition_no AS supp_reference,
                delivery_date as tran_date, 
		'' as due_date,
		supplier.curr_code, 
                '' AS TotalAmount,
		'' AS Allocated,
		0 as OverDue,
                1 as Settled
            FROM ".TB_PREF."grn_batch as trans, ".TB_PREF."suppliers as supplier, ".TB_PREF."purch_orders as po
            WHERE supplier.supplier_id = trans.supplier_id
                AND trans.purch_order_no = po.order_no
                AND trans.delivery_date >= '$date_after'
                AND trans.delivery_date <= '$date_to'";

   	if ($supplier_id != ALL_TEXT) {
   		$sql1 .= " AND trans.supplier_id = ".db_escape($supplier_id);
   		$sql2 .= " AND trans.supplier_id = ".db_escape($supplier_id);
	}
        
        if ($tran_no != null) 
	{
                $sql1 .= " AND trans.trans_no = ".db_escape($tran_no);
                $sql2 .= " AND trans.id = ".db_escape($tran_no);
	} 
        
        if ($filter > 0){
            if ($filter == ST_SUPPRECEIVE) 
            {
                    $sql = $sql2 ;   		
            }
            elseif ($filter > 0) 
            {
                    $sql1 .= " AND trans.type = ".db_escape($filter);
                    $sql = $sql1 ;
            }
        }
        else{
//        if (!isset($filter) || $filter == ALL_TEXT || $filter == '6') {
//		$sql = "SELECT * FROM (($sql) UNION ($sql2)) as tr";
		$sql = $sql1. " UNION ". $sql2 ;
   	}

        $sql4 = "SELECT * FROM ".TB_PREF."vw_supplier_transactions vw 
                WHERE vw.tran_date >= '$date_after'
                    AND vw.tran_date <= '$date_to'";

   	if ($supplier_id != ALL_TEXT) {
   		$sql4 .= " AND vw.supplier_id = ".db_escape($supplier_id);
	}
        
        if ($tran_no != null) 
	{
                $sql4 .= " AND vw.trans_no = ".db_escape($tran_no);
	} 
        
        if ($filter > 0){
                    $sql4 .= " AND vw.type = ".db_escape($filter);
        }
        
   	return $sql4;
}



function update_supplier_payment_float_reimbursement_amount($id, $reimbursed_amount)
{
    $sql = "UPDATE ".TB_PREF."supp_trans 
            SET reimbursed_amount = $reimbursed_amount 
            WHERE ABS(ov_amount - ov_discount) >= ".db_escape($reimbursed_amount)." AND 
                    type =".ST_SUPPAYMENT." AND 
                    trans_no =".db_escape($id);
    
    db_query($sql, "update_supplier_payment_float_reimbursement_amount");  
    return db_num_affected_rows();    
    
}


?>