<?php
/**********************************************************************
***********************************************************************/



function get_sql_for_outstanding_sales_orders_inquiry($selected_customer, $trans_type, $trans_no,  
	$stock_item=null, $from='', $to='', $ref='', $location='', $customer_id=ALL_TEXT)
{

        $date_after = date2sql($from);
        $date_before = date2sql($to);
                        
	$sql = "SELECT 
                sorder.order_no,
                sorder.reference,
                cat_trans.order_category_reference,
                debtor.name,
                branch.br_name,
                sorder.comments, 
                sorder.customer_ref, 
                sorder.ord_date,
                sorder.delivery_date,
                sorder.deliver_to,
                Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost AS OrderValue,
                sorder.type,
                debtor.curr_code,
                Sum(line.qty_sent) AS TotDelivered,
                Sum(line.quantity) AS TotQuantity,
                sorder.approval_status,
                sorder.closed_for_prs 
                
		FROM ".TB_PREF."sales_orders as sorder 
                INNER JOIN ".TB_PREF."sales_order_details as line ON 
                        sorder.order_no = line.order_no AND sorder.trans_type = line.trans_type 
                INNER JOIN ".TB_PREF."debtors_master as debtor ON 
                        sorder.debtor_no = debtor.debtor_no 
                INNER JOIN ".TB_PREF."cust_branch as branch ON 
                        sorder.branch_code = branch.branch_code AND debtor.debtor_no = branch.debtor_no 
                LEFT JOIN ".TB_PREF."sales_order_category_transactions cat_trans ON 
                        sorder.trans_type = cat_trans.tran_type AND sorder.order_no = cat_trans.tran_type_no 
        WHERE 
                sorder.trans_type = ".db_escape($trans_type);
        
        $sql .= " AND line.qty_sent < line.quantity AND sorder.approval_status =".db_escape(AS_APPROVED);

        

			$sql .=  " AND sorder.ord_date >= '$date_after'"
					." AND sorder.ord_date <= '$date_before'";
                        
	if (isset($trans_no) && $trans_no != "")
	{
            $number_like = "%".$trans_no;
            $sql .= " AND sorder.order_no LIKE ".db_escape($number_like);
//				." GROUP BY sorder.order_no";
	}
	elseif ($ref != "")
	{
		// search orders with reference like 
		$number_like = "%".$ref."%";
		$sql .= " AND sorder.reference LIKE ".db_escape($number_like);
//				." GROUP BY sorder.order_no";
	}
	else	// ... or select inquiry constraints
	{

			

        

	}

    if ($selected_customer != -1)
        $sql .= " AND sorder.debtor_no=".db_escape($selected_customer);

    if (isset($stock_item))
        $sql .= " AND line.stk_code=".db_escape($stock_item);

    if ($location)
        $sql .= " AND sorder.from_stk_loc = ".db_escape($location);

    

    //Chaiatanya : New Filter
    if ($customer_id != ALL_TEXT)
        $sql .= " AND sorder.debtor_no = ".db_escape($customer_id);		

    $sql .= " GROUP BY sorder.order_no,
                sorder.debtor_no,
                sorder.branch_code,
                sorder.customer_ref,
                sorder.ord_date,
                sorder.deliver_to";
	return $sql;
    
    
}


function get_sql_for_customer_transaction_inquiry($tran_no, $reference, $customer_id, $TransFromDate, $TransToDate, $filterType){
    
    
    $from_date = date2sql($TransFromDate);
    $to_date = date2sql($TransToDate);

  	$sql = "SELECT trans.type, 
                        trans.trans_no, 
                        trans.reference,
                        trans.order_,                         
                        cat_trans.order_category_reference, 
                        trans.tran_date, 
                        trans.due_date, 
                        debtor.name, 
                        branch.br_name,
                        debtor.curr_code,
                        (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount)	AS TotalAmount, "; 
        

        if(in_array($filterType, array(ST_CUSTDELIVERY, ST_CUSTOMER_PAYMENT_RETURN, ST_BANKPAYMENT, ST_PROJECT_INVOICE, ST_SALESINVOICE, ST_CASH_PAYMENT, ST_GL_DEBIT))){
            
            $sql .= "@bal := @bal+(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), ";
        }
        elseif(in_array($filterType, array(ST_CUSTCREDIT, ST_CUSTPAYMENT, ST_BANKDEPOSIT, ST_PROJECT_CREDIT_NOTE, ST_CASH_RECEIPT, ST_GL_CREDIT))){
            $sql .= "@bal := @bal-(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), ";
        }

        $sql .= "trans.alloc AS Allocated,
		((trans.type = ".ST_SALESINVOICE.") AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue ,
		Sum(line.quantity-line.qty_done) AS Outstanding ";
        
        if(in_array($filterType, array(ST_CUSTDELIVERY, ST_CUSTOMER_PAYMENT_RETURN, ST_BANKPAYMENT, ST_PROJECT_INVOICE, ST_SALESINVOICE, ST_CASH_PAYMENT, ST_GL_DEBIT))){
            $sql .= ", SUM(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) rb_total ";
        }
        elseif(in_array($filterType, array(ST_CUSTCREDIT, ST_CUSTPAYMENT, ST_BANKDEPOSIT, ST_PROJECT_CREDIT_NOTE, ST_CASH_RECEIPT, ST_GL_CREDIT))){
            $sql .= ", SUM(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount * -1) rb_total ";
        }
              
        
	$sql .= "
		
                FROM ".TB_PREF."debtor_trans as trans 
                    INNER JOIN ".TB_PREF."debtors_master as debtor ON 
                        debtor.debtor_no = trans.debtor_no 
                    INNER JOIN ".TB_PREF."cust_branch as branch ON 
                        trans.branch_code = branch.branch_code 
                    LEFT JOIN ".TB_PREF."debtor_trans_details as line ON 
                        trans.trans_no=line.debtor_trans_no AND trans.type=line.debtor_trans_type 			
                    LEFT JOIN ".TB_PREF."sales_order_category_transactions cat_trans ON 
                        trans.type = cat_trans.tran_type AND trans.trans_no = cat_trans.tran_type_no 

                WHERE trans.tran_date >= '$from_date'
                    AND trans.tran_date <= '$to_date' ";

        if($tran_no > 0 || $reference != ""){
            if($tran_no > 0){
                $sql .= " AND trans.trans_no = ".db_escape($tran_no);
            }
            elseif($reference != ""){
                $reference_like = "%".$reference."%";
                $sql .= " AND trans.reference LIKE ".db_escape($reference_like);
            }
        }
        else{
            if($customer_id != -1){
                $sql .= " AND trans.debtor_no = ".db_escape($customer_id);
            }

            if ($filterType!= 0){
                $sql .= " AND trans.type = ".db_escape($filterType);
            }
        }

	$sql .= " GROUP BY trans.trans_no, trans.type";
        $sql .= " ORDER BY trans.tran_date ";
        
   	return $sql;
}



function get_sql_for_sales_orders_inquiry($selected_customer, $trans_type, $order_no, $from='', $to='', $ref='', $location='', $customer_id=ALL_TEXT)
{

	$sql = "SELECT 
                sorder.order_no,
                sorder.reference,
                cat_trans.order_category_reference,
                debtor.name,
                branch.br_name, 
                sorder.customer_ref,
                sorder.ord_date,
                sorder.delivery_date,
                sorder.total, 
                sorder.approval_status, 
                sorder.type,
                debtor.curr_code,
                
                sorder.closed_for_prs 
                
		FROM ".TB_PREF."sales_orders as sorder 
                INNER JOIN ".TB_PREF."debtors_master as debtor ON 
                        sorder.debtor_no = debtor.debtor_no 
                INNER JOIN ".TB_PREF."cust_branch as branch ON 
                        sorder.branch_code = branch.branch_code AND debtor.debtor_no = branch.debtor_no 
                LEFT JOIN ".TB_PREF."sales_order_category_transactions cat_trans ON 
                        sorder.trans_type = cat_trans.tran_type AND sorder.order_no = cat_trans.tran_type_no 
        WHERE 
                sorder.trans_type = ".db_escape($trans_type);

        if((isset($order_no) && $order_no > 0) || (isset($ref) && $ref != ""))
	{
                if (isset($order_no) && $order_no != "")
                {
                        $sql .= " AND sorder.order_no = ".db_escape($order_no);
                }
                elseif (isset($ref) && $ref != "")
                {
                        $number_like = "%".$ref."%";
                        $sql .= " AND sorder.reference LIKE ".db_escape($number_like);
                }
        }
	else	// ... or select inquiry constraints
	{

			$date_after = date2sql($from);
			$date_before = date2sql($to);

			$sql .=  " AND sorder.ord_date >= '$date_after'"
					." AND sorder.ord_date <= '$date_before'";

	}
    

    if ($selected_customer != -1){
        $sql .= " AND sorder.debtor_no=".db_escape($selected_customer);
    }


    if ($location){
        $sql .= " AND sorder.from_stk_loc = ".db_escape($location);
    }


    //Chaiatanya : New Filter
    if ($customer_id != ALL_TEXT){
        $sql .= " AND sorder.debtor_no = ".db_escape($customer_id);
    }
    
    $sql .= " ORDER BY sorder.order_no";
    
    
	return $sql;
    
    
}



function get_customer_opening_balance($debtorno, $to)
{
	if($to)
		$to = date2sql($to);

    $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR t.type = ".ST_BANKPAYMENT." OR t.type = ".ST_PROJECT_INVOICE.", 
                                (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,
                SUM(IF(t.type <> ".ST_SALESINVOICE." AND t.type <> ".ST_BANKPAYMENT." AND t.type <> ".ST_PROJECT_INVOICE.", 
	    	(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,
		SUM(t.alloc) AS Allocated,
		SUM(IF(t.type = ".ST_SALESINVOICE." OR t.type = ".ST_BANKPAYMENT." OR t.type = ".ST_PROJECT_INVOICE.",
			(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount - t.alloc),
	    	((t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1 + t.alloc))) AS OutStanding
		FROM ".TB_PREF."debtor_trans t
    	WHERE t.debtor_no = ".db_escape($debtorno)
		." AND t.type <> ".ST_CUSTDELIVERY;
    if ($to){
    	$sql .= " AND t.tran_date < '$to'";
    }
	
    $sql .= " GROUP BY debtor_no";

    $result = db_query($sql,"No transactions were returned");
    return db_fetch($result);
}

function get_customer_transaction_allocation_from_within_period($tran_type, $tran_no, $from_date_, $to_date_) {
    $from_date = date2sql($from_date_);
    $to_date = date2sql($to_date_);
            
        $sql = "SELECT SUM(ca.amt) allocated 
                FROM ".TB_PREF."debtor_trans dt  
                    JOIN ".TB_PREF."cust_allocations ca 
                        ON dt.type = ca.trans_type_to   
                            AND dt.trans_no = ca.trans_no_to  
                    JOIN ".TB_PREF."debtor_trans dt2  
                        ON ca.trans_type_from = dt2.type 
                            AND ca.trans_no_from = dt2.trans_no  
                WHERE dt.trans_no = ".db_escape($tran_no)."
                    AND dt.type = ".db_escape($tran_type)." 
                    AND dt2.tran_date >= ".db_escape($from_date)."
                    AND dt2.tran_date <= ".db_escape($to_date);

        $result = db_query($sql, "get_customer_transaction_allocation_from_within_period_2");
        return db_result_value($result, 'allocated');
}

function get_customer_transaction_allocation_to_within_period($tran_type, $tran_no, $from_date_, $to_date_) {
    $from_date = date2sql($from_date_);
    $to_date = date2sql($to_date_);
    
        $sql = "SELECT SUM(ca.amt) allocated 
                FROM ".TB_PREF."debtor_trans dt  
                    JOIN ".TB_PREF."cust_allocations ca 
                        ON dt.type = ca.trans_type_from  
                            AND dt.trans_no = ca.trans_no_from 
                    JOIN ".TB_PREF."debtor_trans dt2  
                        ON ca.trans_type_to = dt2.type 
                            AND ca.trans_no_to = dt2.trans_no  
                WHERE dt.trans_no = ".db_escape($tran_no)."
                    AND dt.type = ".db_escape($tran_type)." 
                    AND dt2.tran_date >= ".db_escape($from_date)."
                    AND dt2.tran_date <= ".db_escape($to_date);
        
        $result = db_query($sql, "get_customer_transaction_allocation_to_within_period_2");
        return db_result_value($result, 'allocated');
}




function get_customer_transactions($debtorno, $from, $to)
{
	$from = date2sql($from);
	$to = date2sql($to);

    $sql = "SELECT ".TB_PREF."debtor_trans.*,
		(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + 
		".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount)
		AS TotalAmount, ".TB_PREF."debtor_trans.alloc AS Allocated,
		((".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.")
		AND ".TB_PREF."debtor_trans.due_date < '$to') AS OverDue
    	FROM ".TB_PREF."debtor_trans
    	WHERE ".TB_PREF."debtor_trans.tran_date >= '$from'
		AND ".TB_PREF."debtor_trans.tran_date <= '$to'
		AND ".TB_PREF."debtor_trans.debtor_no = ".db_escape($debtorno)."
		AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY."
    	ORDER BY ".TB_PREF."debtor_trans.tran_date";
    
    $sql = "SELECT dt.*,
		(dt.ov_amount + dt.ov_gst + dt.ov_freight + dt.ov_freight_tax + dt.ov_discount) AS TotalAmount, 
                dt.alloc AS Allocated 
            FROM ".TB_PREF."debtor_trans dt 
            WHERE dt.tran_date >= '$from'
                    AND dt.tran_date <= '$to'
                    AND dt.debtor_no = ".db_escape($debtorno)."
                    AND dt.type <> ".ST_CUSTDELIVERY."
            ORDER BY dt.tran_date";

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



function get_pos_sales_summary($from_, $to_)
{
	$from = date2sql($from_);
	$to = date2sql($to_);

    $sql = "SELECT h.*,
                dm.debtor_ref, dm.name debtor_name,
                f.name float_name 
            FROM ".TB_PREF."pos_invoices h   
                JOIN ".TB_PREF."debtors_master dm 
                    ON dm.debtor_no = h.debtor_no 
                JOIN ".TB_PREF."petty_cash_floats f  
                    ON f.id = h.float_id  
            WHERE h.tran_date >= ".db_escape($from)." 
                    AND h.tran_date <= ".db_escape($to)." 
            ORDER BY h.tran_date";

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