<?php
/**********************************************************************
***********************************************************************/
//-------------------------------------------------------------------------------------------------------------
//
//	FIXME - this should be revised for transaction update case.
//
function write_emp_trans($type, $trans_no, $company_code, $employee_id, 
                    $reference, $tran_date_, $amount, 
                    $account, $bank_account_id, $rate, $alloc = 0)
{
        if(!($trans_no > 0)){
            $new = $trans_no == 0;
        }
        else{
            $new = 0;
        }
	$tran_date = date2sql($tran_date_);

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

        if($bank_account_id > 0){
            $bank_account = get_bank_account($bank_account_id);
            $curr = $bank_account['bank_curr_code'];
        }
        else{
            $curr = get_company_pref('curr_default');
        }
        
	if ($rate == 0){
		$rate = get_exchange_rate_from_home_currency($curr, $tran_date_);
        }

        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
        $sql = "INSERT INTO ".TB_PREF."emp_trans(
                    trans_no, type, company_code, employee_id, 
                    reference, tran_date, amount, 
                    account, bank_account_id, rate, alloc,
                    created_by, created_on) ";
        
	$sql .= "VALUES (".db_escape($trans_no).", ".db_escape($type).", ".db_escape($company_code).", ".db_escape($employee_id).","
                        .db_escape($reference).", ".db_escape($tran_date).", ".db_escape($amount).", "
                        .db_escape($account).", ".db_escape($bank_account_id).", ".db_escape($rate).", ".db_escape($alloc).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";


        $err_msg = "Cannot insert a employee transaction record";    

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

	return $trans_no;
}

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

function get_emp_trans($trans_no, $trans_type=-1)
{
	$sql = "SELECT * 
                FROM ".TB_PREF."emp_trans  
                WHERE trans_no = ".db_escape($trans_no)."
                    AND type = ".db_escape($trans_type);
        
	$result = db_query($sql, "Cannot retreive a supplier transaction");

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

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

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

function get_emp_trans_record($trans_no, $trans_type=-1)
{
	$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_emp_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 clear_emp_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 get_sql_for_emp_inquiry($filter, $after_date, $to_date, $supplier_id=ALL_TEXT, $tran_no = null)
{
    $date_after = date2sql($after_date);
    $date_to = date2sql($to_date);

    $sql = "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) {
   		$sql .= " AND trans.supplier_id = ".db_escape($supplier_id);
   		$sql2 .= " AND trans.supplier_id = ".db_escape($supplier_id);
	}
        
        if ($tran_no != null) 
	{
			$sql .= " AND trans.trans_no = ".db_escape($tran_no);
                        $sql2 .= " AND trans.id = ".db_escape($tran_no);
	} 
        
	if (($filter == '6')) 
	{
			$sql = $sql2;
	} 
	elseif (!isset($filter) || $filter == ALL_TEXT || $filter == '6') {
		$sql = "SELECT * FROM (($sql) UNION ($sql2)) as tr";
   	}

   	if (isset($filter) && $filter != ALL_TEXT)
   	{
   		if (($filter == '1')) 
   		{
   			$sql .= " AND trans.type IN(".ST_SUPPINVOICE.",".ST_BANKDEPOSIT.",".ST_UTILITY_BILLS.",".ST_SUPPLIER_TRANSPORT_INVOICES.",".ST_SUBCONTRACTOR_INVOICE.") ";
   		} 
   		elseif (($filter == '2')) 
   		{
   			$sql .= " AND trans.type IN (".ST_SUPPINVOICE.",".ST_SUPPLIER_TRANSPORT_INVOICES.",".ST_UTILITY_BILLS.",".ST_SUBCONTRACTOR_INVOICE.") ";
   		} 
   		elseif ($filter == '3') 
   		{
			$sql .= " AND (trans.type = ".ST_SUPPAYMENT." OR trans.type = ".ST_BANKPAYMENT.") ";
   		} 
   		elseif (($filter == '4') || ($filter == '5')) 
   		{
			$sql .= " AND trans.type IN (".ST_SUPPCREDIT.",".ST_UTILITY_BILL_CREDIT_NOTE.",".ST_SUBCONTRACTOR_CREDIT_NOTE." ) ";
   		}

   		if (($filter == '2') || ($filter == '5')) 
   		{
   			$today =  date2sql(Today());
			$sql .= " AND trans.due_date < '$today' ";
   		}
   	}
        
        
        
   	return $sql;
}




?>