<?php
/* 
A domain Class to demonstrate RESTful web services
*/
require_once("database.php");

$refline_placeholders = array(
	'MM' => 'date',
	'YY' => 'date',
	'YYYY' => 'date',
	'FF' => 'date', // fiscal year
	'FFFF' => 'date',
	'UU' => 'user',
 	'P' => 'pos',
	
);
Class Customers {
	
	/*
		you should hookup the DAO here
	*/

	private function mysql_date($date)
	{
	    if (!isset($date) || $date === '') {
	        return '';
	    }

	    $date = trim($date);
	    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
	        return $date;
	    }

	    if (preg_match('/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/', $date, $matches)) {
	        return sprintf('%04d-%02d-%02d', $matches[3], $matches[2], $matches[1]);
	    }

	    $timestamp = strtotime(str_replace('/', '-', $date));
	    return $timestamp ? date('Y-m-d', $timestamp) : $date;
	}

	 public function getCustomersDetails($searchString)
	{
	    
	    $searchString = htmlspecialchars($searchString);
	    
		$sql="SELECT dm.debtor_no,dm.cust_code,dm.name,cb.br_address,cb.area FROM 0_debtors_master dm,0_cust_branch cb where dm.cust_code like '%".$searchString."%' or dm.name like '%".$searchString."%' and dm.debtor_no=cb.debtor_no";

        $result=mysql_query($sql);
      $rows=array();
      
      while($record=mysql_fetch_assoc($result))
      {
		$sp['customer_id']=$record['debtor_no'];
		$sp['customer_code']=$record['cust_code'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['outstanding_balance']=$this->getCustomerOutstandingBalance($record['cust_code']);
        $sp['delivery_address']=$record['br_address'];
        $sp['area']=$record['area']==1?1:0;
	    $rows[]=$sp;
      }
	  
	  return $rows;
 
	}
	
	
	 public function getCustomersDetailsBySalesPerson($sales_person_id,$searchString)
	{
	    $searchString = htmlspecialchars($searchString);
	    
		$sql="SELECT dm.debtor_no,dm.cust_code,dm.name,cb.br_address,cb.area FROM 0_debtors_master dm,0_cust_branch cb where dm.cust_code like '%".$searchString."%' or dm.name like '%".$searchString."%' and dm.debtor_no=cb.debtor_no and cb.salesman=".$sales_person_id."";

        $result=mysql_query($sql);
      $rows=array();
      
      while($record=mysql_fetch_assoc($result))
      {
		$sp['customer_id']=$record['debtor_no'];
		$sp['customer_code']=$record['cust_code'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['outstanding_balance']=$this->getCustomerOutstandingBalance($record['cust_code']);
        $sp['delivery_address']=$record['br_address'];
        $sp['area']=$record['area']==1?1:0;
	    $rows[]=$sp;
      }
	  
	  return $rows;
 
	}
	
	
	public function getCustomerOutstandingBalance($cust_code)
	{
	   $sql="SELECT sum(balance) as outstanding_balance FROM 0_cust_outstanding_balances where customer_code='$cust_code'";

        $result=mysql_query($sql);
        if($row=mysql_fetch_assoc($result))
        {
            
           return is_null($row['outstanding_balance'])?"0":number_format($row['outstanding_balance'],3);
        }
        
        return "No Data Found!";
        
	} 
	
	public function getUploadedFile($collection_id)
	{
	   $sql="SELECT file_name FROM 0_upload_collections_data where collection_id='$collection_id'";

        $result=mysql_query($sql);
        if($row=mysql_fetch_assoc($result))
        {
           return $row['file_name'];
        }
        
        return 0;
        
	} 
	
	public function getCustomerBillsDetails($cust_code)
	{
	   $sql="SELECT * FROM 0_cust_outstanding_balances where customer_code='$cust_code'";

        $result=mysql_query($sql);
         $rows=array();
        while($row=mysql_fetch_assoc($result))
        {
           $cb['bill_date']=date("d/m/Y", strtotime($row['bill_date']));
           $cb['bill']=$row['bill_no'];
           $cb['due_date']=date("d/m/Y", strtotime($row['due_date']));
           $cb['balance']=$row['balance'];
       
	    $rows[]=$cb;
        }
        
        return $rows;
	}
	
	public function getCountrywiseSales()
	{
	   $sql="SELECT * FROM 0_country_wise_sales_revenue";

        $result=mysql_query($sql);
        
      $rows=array();
      while($record=mysql_fetch_assoc($result))
      {
		$sp['country']=$record['country'];
		$sp['revenue']=$record['revenue'];
       
	    $rows[]=$sp;
      }
	  
	  return $rows;
	}
	
	public function get_customer_outstanding_balances($cust_code)
	{
	    $sql = "SELECT Sum(balance) AS cust_balance FROM 0_cust_outstanding_balances WHERE customer_code= '$cust_code' ";
	   //  echo $sql;die;
	    $result=mysql_query($sql);
	    $result=mysql_fetch_row($result);
	    return $result[0]==null?"0":number_format($result[0],3);
	}
	
	public function getSalesEnquiries($from_date,$to_date,$sales_person_id,$customer_id,$page_no)
	{
	    
	   $offset= $page_no*10 - 10;
	   

	   $sql = "SELECT 
			sorder.order_no,
			sorder.reference,
			debtor.name,
			branch.br_name,
			sorder.customer_ref,
			sorder.ord_date,
			sorder.delivery_date,
			sorder.deliver_to,
			debtor.curr_code,
			salesman.salesman_name,
			sorder.salesman_id,
			debtor.cust_code
		FROM 0_sales_orders as sorder,0_sales_order_details as line,0_debtors_master as debtor,0_cust_branch as branch, 0_salesman salesman
			WHERE sorder.order_no = line.order_no
			AND sorder.trans_type = line.trans_type
			AND sorder.trans_type = 53 
			AND sorder.debtor_no = debtor.debtor_no
			AND sorder.debtor_no = branch.debtor_no
			AND debtor.debtor_no = branch.debtor_no
		    AND salesman.salesman_code = sorder.salesman_id";
		
		   
		 	$date_after = implode("-", array_reverse(explode("/", $from_date)));
		 	
			$date_before = implode("-", array_reverse(explode("/", $to_date)));

			$sql .=  " AND sorder.ord_date >= '$date_after'"." AND sorder.ord_date <= '$date_before'";
			
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND sorder.salesman_id = '$sales_person_id'"; 
			}
			
			if($customer_id!=0)
			{
			   $sql .=  " AND sorder.debtor_no = '$customer_id'"; 
			}
	
	    	$sql .= " GROUP BY sorder.order_no,
					sorder.debtor_no,
					sorder.branch_code,
					sorder.customer_ref,
					sorder.ord_date,
					sorder.deliver_to
				ORDER BY sorder.ord_date desc, sorder.order_no desc LIMIT $offset,10";
				

				
		$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['order_no']=$record['order_no'];
		$sp['reference']=$record['reference'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['customer_ref']=$record['customer_ref'];
		$sp['ord_date']=date("d/m/Y", strtotime($record['ord_date']));
		$sp['delivery_date']=date("d/m/Y", strtotime($record['delivery_date']));
		$sp['deliver_to']=$record['deliver_to'];
        $sp['curr_code']=$record['curr_code'];
        $sp['sales_person_name']=$record['salesman_name'];
        $sp['sales_person_id']=$record['salesman_id'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        $sp['print_url']="https://npf.techintegraerp.net/reporting/prn_redirect.php?PARAM_0=".$sp['order_no']."&PARAM_1=".$sp['order_no']."&REP_ID=1111";
	    $rows[]=$sp;
      }  
	   
	  return $rows;

        
	} 
	
	
	public function getSalesQuotations($from_date,$to_date,$sales_person_id,$customer_id,$page_no)
	{
	    
	   $offset= $page_no*10 - 10;
	   

	   $sql = "SELECT 
			sorder.order_no,
			sorder.reference,
			debtor.name,
			branch.br_name,
			sorder.customer_ref,
			sorder.ord_date,
			sorder.delivery_date,
			sorder.deliver_to,
			debtor.curr_code ,sorder.total,salesman.salesman_name,sorder.salesman_id,debtor.cust_code ,sorder.quote_status,sorder.order_ref 
		FROM 0_sales_orders as sorder,0_sales_order_details as line,0_debtors_master as debtor,0_cust_branch as branch,0_salesman salesman
			WHERE sorder.order_no = line.order_no
			AND sorder.trans_type = line.trans_type
			AND sorder.trans_type = 32 
			AND sorder.debtor_no = debtor.debtor_no
			AND sorder.debtor_no = branch.debtor_no
			AND debtor.debtor_no = branch.debtor_no
			AND salesman.salesman_code = sorder.salesman_id";
		
		
		   
		 	$date_after = implode("-", array_reverse(explode("/", $from_date)));
		 	
			$date_before = implode("-", array_reverse(explode("/", $to_date)));

			$sql .=  " AND sorder.ord_date >= '$date_after'"." AND sorder.ord_date <= '$date_before'";
			
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND sorder.salesman_id = '$sales_person_id'"; 
			}
			
			if($customer_id!=0)
			{
			   $sql .=  " AND sorder.debtor_no = '$customer_id'"; 
			}
	
	    	$sql .= " GROUP BY sorder.order_no,
					sorder.debtor_no,
					sorder.branch_code,
					sorder.customer_ref,
					sorder.ord_date,
					sorder.deliver_to
				ORDER BY sorder.ord_date desc, sorder.order_no desc LIMIT $offset,10";

				

        $result=mysql_query($sql);
        
        
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['order_no']=$record['order_no'];
		$sp['reference']=$record['reference'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['customer_ref']=$record['customer_ref'];
		$sp['ord_date']=date("d/m/Y", strtotime($record['ord_date']));
		$sp['delivery_date']=date("d/m/Y", strtotime($record['delivery_date']));
		$sp['deliver_to']=$record['deliver_to'];
        $sp['curr_code']=$record['curr_code'];
        $sp['total']=number_format($record['total'],3);
        $sp['sales_person_name']=$record['salesman_name'];
        $sp['quote_status']=$record['quote_status'];
        $sp['order_ref']=$record['order_ref'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        
        $sp['print_url']="https://npf.techintegraerp.net/reporting/prn_redirect.php?PARAM_0=".$sp['order_no']."&PARAM_1=".$sp['order_no']."&REP_ID=132";
      
	    $rows[]=$sp;
      }  
	   
	  return $rows;

        
	} 
	
	
	
		
	public function getSalesPendingQuotations($sales_person_id)
	{
	    
	 
	   $sql = "SELECT 
			sorder.order_no,
			sorder.reference,
			debtor.name,
			branch.br_name,
			sorder.customer_ref,
			sorder.ord_date,
			sorder.delivery_date,
			sorder.deliver_to,
			debtor.curr_code ,sorder.total,salesman.salesman_name,sorder.salesman_id,debtor.cust_code ,sorder.quote_status,sorder.order_ref 
		FROM 0_sales_orders as sorder,0_sales_order_details as line,0_debtors_master as debtor,0_cust_branch as branch,0_salesman salesman
			WHERE sorder.order_no = line.order_no
			AND sorder.trans_type = line.trans_type
			AND sorder.trans_type = 32 
			AND sorder.debtor_no = debtor.debtor_no
			AND sorder.debtor_no = branch.debtor_no
			AND debtor.debtor_no = branch.debtor_no
			AND auth_req=1 
			AND salesman.salesman_code = sorder.salesman_id";
		
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND sorder.salesman_id = '$sales_person_id'"; 
			}
			
	
	    	$sql .= " GROUP BY sorder.order_no,
					sorder.debtor_no,
					sorder.branch_code,
					sorder.customer_ref,
					sorder.ord_date,
					sorder.deliver_to
				ORDER BY sorder.ord_date desc, sorder.order_no desc ";

        $result=mysql_query($sql);
        
        
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['order_no']=$record['order_no'];
		$sp['reference']=$record['reference'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['customer_ref']=$record['customer_ref'];
		$sp['ord_date']=date("d/m/Y", strtotime($record['ord_date']));
		$sp['delivery_date']=date("d/m/Y", strtotime($record['delivery_date']));
		$sp['deliver_to']=$record['deliver_to'];
        $sp['curr_code']=$record['curr_code'];
        $sp['total']=$record['total'];
        $sp['sales_person_name']=$record['salesman_name'];
        $sp['quote_status']=$record['quote_status'];
        $sp['order_ref']=$record['order_ref'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        
        $sp['print_url']="https://npf.techintegraerp.net/reporting/prn_redirect.php?PARAM_0=".$sp['order_no']."&PARAM_1=".$sp['order_no']."&REP_ID=132";
      
	    $rows[]=$sp;
      }  
	   
	  return $rows;

        
	} 
	
	
	public function getSalesApprovedQuotations($sales_person_id)
	{
	    
	   $sql = "SELECT 
			sorder.order_no,
			sorder.reference,
			debtor.name,
			branch.br_name,
			sorder.customer_ref,
			sorder.ord_date,
			sorder.delivery_date,
			sorder.deliver_to,
			debtor.curr_code ,sorder.total,salesman.salesman_name,sorder.salesman_id,debtor.cust_code ,sorder.quote_status,sorder.order_ref 
		FROM 0_sales_orders as sorder,0_sales_order_details as line,0_debtors_master as debtor,0_cust_branch as branch,0_salesman salesman
			WHERE sorder.order_no = line.order_no
			AND sorder.trans_type = line.trans_type
			AND sorder.trans_type = 32 
			AND sorder.debtor_no = debtor.debtor_no
			AND sorder.debtor_no = branch.debtor_no
			AND debtor.debtor_no = branch.debtor_no
			AND auth_req=2 
			AND salesman.salesman_code = sorder.salesman_id";
		
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND sorder.salesman_id = '$sales_person_id'"; 
			}
			
	    	$sql .= " GROUP BY sorder.order_no,
					sorder.debtor_no,
					sorder.branch_code,
					sorder.customer_ref,
					sorder.ord_date,
					sorder.deliver_to
				ORDER BY sorder.ord_date desc, sorder.order_no desc ";

				

        $result=mysql_query($sql);
        
        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['order_no']=$record['order_no'];
		$sp['reference']=$record['reference'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['customer_ref']=$record['customer_ref'];
		$sp['ord_date']=date("d/m/Y", strtotime($record['ord_date']));
		$sp['delivery_date']=date("d/m/Y", strtotime($record['delivery_date']));
		$sp['deliver_to']=$record['deliver_to'];
        $sp['curr_code']=$record['curr_code'];
        $sp['total']=$record['total'];
        $sp['sales_person_name']=$record['salesman_name'];
        $sp['quote_status']=$record['quote_status'];
        $sp['order_ref']=$record['order_ref'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        
        $sp['print_url']="https://npf.techintegraerp.net/reporting/prn_redirect.php?PARAM_0=".$sp['order_no']."&PARAM_1=".$sp['order_no']."&REP_ID=132";
      
	    $rows[]=$sp;
      }  
	   
	  return $rows;

        
	} 
	
	public function getSingleSalesEnquiry($enq_no)
	{
	     $sql = "SELECT sorder.*,
	  cust.name,
	  cust.curr_code,
	  cust.address,
	  loc.location_name,
	  cust.discount,
	  stype.sales_type,
	  stype.id AS sales_type_id,
	  stype.tax_included,
	  stype.factor,
 	  ship.shipper_name,
	  tax_group.name AS tax_group_name,
	  tax_group.id AS tax_group_id,
	  cust.tax_id,
	  sorder.alloc,
	  sorder.prep_amount>0 as prepaid,
	  cust.cust_code, salesman.salesman_name,sorder.salesman_id,cust.cust_code,sorder.debtor_no 
	FROM 0_sales_orders sorder,
			0_debtors_master cust,
			0_sales_types stype, 
			0_tax_groups tax_group,
			0_cust_branch branch,
			0_locations loc,
			0_shippers ship,
			0_salesman salesman 
	WHERE sorder.order_type=stype.id
		AND branch.debtor_no = sorder.debtor_no
		AND branch.tax_group_id = tax_group.id
		AND sorder.debtor_no = cust.debtor_no
		AND loc.loc_code = sorder.from_stk_loc
		AND  ship.shipper_id = sorder.ship_via
		AND sorder.trans_type = 53
		AND salesman.salesman_code = sorder.salesman_id
		AND sorder.order_no = '$enq_no'";
		

	$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['order_no']=$record['order_no'];
		$sp['reference']=$record['reference'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['customer_ref']=$record['customer_ref'];
		$sp['ord_date']=date("d/m/Y", strtotime($record['ord_date']));
		$sp['delivery_date']=date("d/m/Y", strtotime($record['delivery_date']));
		$sp['deliver_to']=$record['deliver_to'];
        $sp['curr_code']=$record['curr_code'];
        $sp['sales_person_name']=$record['salesman_name'];
        
         $sp['contact_person_name']=$record['contact_person_name'];
         $sp['contact_phone']=$record['contact_phone'];
         $sp['delivery_address']=$record['delivery_address'];
         $sp['comments']=$record['comments'];
         $sp['multiple_delivery_locations_required']=$record['muliple_del_loc_req']==1?'YES':'NO';
         $sp['multiple_delivery_locations_details']=$record['multiple_loc_details'];	
         $sp['customer_code']=$record['cust_code'];
         $sp['debtor_no']=$record['debtor_no'];
         $sp['sales_person_id']=$record['salesman_id'];
         $sp['cust_balance']=$this ->get_customer_outstanding_balances($record['cust_code']);
         $sp['print_url']="https://npf.techintegraerp.net/reporting/prn_redirect.php?PARAM_0=".$sp['order_no']."&PARAM_1=".$sp['order_no']."&REP_ID=1111";
         $items=$this->get_sales_order_details($record['order_no'],53,$record['debtor_no']);
         $sp['itemList']  = $items;  

	    $rows[]=$sp;
      }  
	   
	  return $rows;
        
	} 
	
 public function getSingleSalesQuotation($quote_no)
	{
	    
     $sql = "SELECT sorder.*,
	  cust.name,
	  cust.curr_code,
	  cust.address,
	  loc.location_name,
	  cust.discount,
	  stype.sales_type,
	  stype.id AS sales_type_id,
	  stype.tax_included,
	  stype.factor,
 	  ship.shipper_name,
	  tax_group.name AS tax_group_name,
	  tax_group.id AS tax_group_id,
	  cust.tax_id,
	  sorder.alloc,
	  sorder.prep_amount>0 as prepaid,
	  cust.cust_code, salesman.salesman_name,
	  sorder.salesman_id,
	  cust.discount as default_discount
	FROM 0_sales_orders sorder,
			0_debtors_master cust,
			0_sales_types stype, 
			0_tax_groups tax_group,
			0_cust_branch branch,
			0_locations loc,
			0_shippers ship,
			0_salesman salesman 
	WHERE sorder.order_type=stype.id
		AND branch.debtor_no = sorder.debtor_no
		AND branch.tax_group_id = tax_group.id
		AND sorder.debtor_no = cust.debtor_no
		AND loc.loc_code = sorder.from_stk_loc
		AND  ship.shipper_id = sorder.ship_via
		AND sorder.trans_type = 32 
		AND salesman.salesman_code = sorder.salesman_id
		AND sorder.order_no = '$quote_no'";

	$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['order_no']=$record['order_no'];
		$sp['reference']=$record['reference'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['customer_ref']=$record['customer_ref'];
		$sp['ord_date']=date("d/m/Y", strtotime($record['ord_date']));
		$sp['delivery_date']=date("d/m/Y", strtotime($record['delivery_date']));
		$sp['deliver_to']=$record['deliver_to'];
        $sp['curr_code']=$record['curr_code'];
        $sp['sales_person_name']=$record['salesman_name'];
        
        $sp['customer_code']=$record['cust_code'];
         $sp['debtor_no']=$record['debtor_no'];
         
         $sp['contact_person_name']=$record['contact_person_name'];
         $sp['contact_phone']=$record['contact_phone'];
         $sp['delivery_address']=$record['delivery_address'];
         $sp['comments']=$record['comments'];
         $sp['multiple_delivery_locations_required']=$record['muliple_del_loc_req']==1?'YES':'NO';
         $sp['multiple_delivery_locations_details']=$record['multiple_loc_details'];
         
        $sp['sales_person_id']=$record['salesman_id'];
        $sp['approval_status']=$record['auth_req'];
        $sp['quote_status']=$record['quote_status'];
        $sp['order_ref']=$record['order_ref'];
        
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);

        $sp['print_url']="https://npf.techintegraerp.net/reporting/prn_redirect.php?PARAM_0=".$sp['order_no']."&PARAM_1=".$sp['order_no']."&REP_ID=132";
        
            $items=$this->get_sales_order_details($record['order_no'],32,$record['debtor_no'],$record['default_discount']);
            $sp['itemList']  = $items;  
			
              
	    $rows[]=$sp;
      }  
	  return $rows;
        
	} 
	
	public function get_sales_order_details($trans_no,$type,$debtor_no,$default_discount)
	{
	      
	    $sql = "SELECT id, stk_code,
				line.description,
				line.quantity,
				item.long_description,
				item.units,
				line.remarks,
				line.unit_price,
				line.discount_percent,
				line.secondary_quantity,
				line.stk_secondary_units,
				line.foc_quantity,
				item.conversion_factor_uom,
				item.inner_ctn_qty,
				item.outer_ctn_qty 
				
			FROM 0_sales_order_details line,
				0_stock_master item
			WHERE line.stk_code = item.stock_id
				AND order_no ='$trans_no'
				AND trans_type ='$type' ORDER BY id";
             $result=mysql_query($sql);
	     $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['id']=$record['id'];
		$sp['stk_code']=$record['stk_code'];
        $sp['description']=mb_convert_encoding($record['description'], "UTF-8", "HTML-ENTITIES");
		$sp['quantity']=$record['quantity'];
		$sp['long_description']=$record['long_description'];
        $sp['units']=$record['units'];
        $sp['remarks']=$record['remarks'];
        $sp['secondary_quantity']=$record['secondary_quantity'];
        $sp['stk_secondary_units']=$record['stk_secondary_units'];
        $sp['foc_quantity']=$record['foc_quantity'];
        $sp['conversion_factor']=$record['conversion_factor_uom'];
        $sp['inner_ctn_qty']=$record['inner_ctn_qty'];
        $sp['outer_ctn_qty']=$record['outer_ctn_qty'];
        
        if($type==53)
          $sp['cust_specific_price'] = $this->customer_prices($record['stk_code'],$debtor_no,1);
        
        if($type!=53)
        {
           $sp['unit_price']=$record['unit_price'];
           $sp['discount_percent']=$record['discount_percent']; 
        }
        // Price condition ## Ramesh
        if($type==32)
        {
            $auth_req=0;
            $cust_price = $this->customer_prices($record['stk_code'],$debtor_no,1);
			$last_quote_price = $this->sales_quote_last_price($record['stk_code'],$debtor_no, 1);
			$sales_price = $this->get_kit_price($record['stk_code'],'OMR');	 
			
			
		
				if($cust_price>0){
						$line_total = round($record['quantity'] * $record['unit_price'] * (1 - $record['discount_percent']),3);
						$unit_price = $record['quantity'] ? round($line_total/$record['quantity'],3) : 0;
					if($cust_price>$unit_price)
						$auth_req=1;
				}else if($sales_price>0){
					$final_price = round($sales_price *(1 - $default_discount),3);
					$line_total = round($record['quantity'] * $record['unit_price'] * (1 - $record['discount_percent']),3);
						$unit_price = $record['quantity'] ? round($line_total/$record['quantity'],3) : 0;
					if($final_price>$unit_price)
						$auth_req=1;
				}else{				
				$line_total = round($record['quantity'] * $record['unit_price'] * (1 - $record['discount_percent']),3);
						$unit_price = $record['quantity'] ? round($line_total/$record['quantity'],3) : 0;
				if($last_quote_price>$unit_price || $last_quote_price==0)
						$auth_req=1;
				}
			
			if($auth_req=='1')
			$sp['below_customer_price']="Yes";
			else
			$sp['below_customer_price']="No";
        } //End Price Conditions
        
            
	    $rows[]=$sp;
      }  
	   
	  return $rows;
	    
	}
	
	 public function getOrdersDetails($searchString)
	{
		 $sql = "SELECT 
			sorder.order_no,
			sorder.order_ref,
			debtor.name,
			branch.br_name,
			sorder.customer_ref,
			sorder.order_date,
			sorder.delivery_date,
			sorder.deliver_to,
			debtor.curr_code,
			sorder.total,
			sorder.order_status,
			sorder.order_status_ref, salesman.salesman_name,sorder.salesman_id 
			
		FROM 0_sales_orders as sorder,0_sales_order_details as line,0_debtors_master as debtor,0_cust_branch as branch,
			0_salesman salesman
			WHERE sorder.order_no = line.order_no
			AND sorder.trans_type = line.trans_type
			AND sorder.trans_type = 32 
			AND sorder.quote_status = 1  
			AND sorder.debtor_no = debtor.debtor_no
			AND sorder.debtor_no = branch.debtor_no
			AND debtor.debtor_no = branch.debtor_no
				AND salesman.salesman_code = sorder.salesman_id 
			AND (sorder.order_ref like '%".$searchString."%' OR sorder.internal_order_ref like '%".$searchString."%' OR debtor.name like '%".$searchString."%'  or debtor.cust_code like '%".$searchString."%') ";
		
		
	    	$sql .= " GROUP BY sorder.order_no,
					sorder.debtor_no,
					sorder.branch_code,
					sorder.customer_ref,
					sorder.ord_date,
					sorder.deliver_to
				ORDER BY sorder.order_no ";
				
			
        $result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['order_no']=$record['order_no'];
		$sp['reference']=$record['order_ref'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['customer_ref']=$record['customer_ref'];
		$sp['ord_date']=date("d/m/Y", strtotime($record['order_date']));
		$sp['delivery_date']=date("d/m/Y", strtotime($record['delivery_date']));
		$sp['deliver_to']=$record['deliver_to'];
        $sp['curr_code']=$record['curr_code'];
        $sp['total']=$record['total'];
        $sp['order_status']=$record['order_status']==0?'Outstanding':'Do cum Invoiced';
        $sp['order_status_ref']=$record['order_status']==0?'':$record['order_status_ref'];
        $sp['sales_person_id']=$record['salesman_id'];
         $sp['sales_person_name']=$record['salesman_name'];
      
	    $rows[]=$sp;
      }  
	   
	  return $rows;
 
	}
	
	 public function getSingleSalesOrder($order_no)
	{
	    
     $sql = "SELECT sorder.*,
	  cust.name,
	  cust.curr_code,
	  cust.address,
	  loc.location_name,
	  cust.discount,
	  stype.sales_type,
	  stype.id AS sales_type_id,
	  stype.tax_included,
	  stype.factor,
 	  ship.shipper_name,
	  tax_group.name AS tax_group_name,
	  tax_group.id AS tax_group_id,
	  cust.tax_id,
	  sorder.alloc,
	  sorder.prep_amount>0 as prepaid,
	  cust.cust_code, salesman.salesman_name,sorder.salesman_id,
	  sorder.total,
	  sorder.order_status,
	  sorder.order_status_ref
	FROM 0_sales_orders sorder,
			0_debtors_master cust,
			0_sales_types stype, 
			0_tax_groups tax_group,
			0_cust_branch branch,
			0_locations loc,
			0_shippers ship,
			0_salesman salesman 
	WHERE sorder.order_type=stype.id
		AND branch.debtor_no = sorder.debtor_no
		AND branch.tax_group_id = tax_group.id
		AND sorder.debtor_no = cust.debtor_no
		AND loc.loc_code = sorder.from_stk_loc
		AND  ship.shipper_id = sorder.ship_via
		AND sorder.trans_type = 32 
		AND sorder.quote_status = 1  
		AND salesman.salesman_code = sorder.salesman_id
		AND sorder.order_no = '$order_no'";

	$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['order_no']=$record['order_no'];
		$sp['reference']=$record['order_ref'];
        $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
        $sp['customer_ref']=$record['customer_ref'];
		$sp['ord_date']=date("d/m/Y", strtotime($record['order_date']));
		$sp['delivery_date']=date("d/m/Y", strtotime($record['delivery_date']));
		$sp['deliver_to']=$record['deliver_to'];
        $sp['curr_code']=$record['curr_code'];
         $sp['sales_person_name']=$record['salesman_name'];
        $sp['sales_person_id']=$record['salesman_id'];
         $sp['total']=$record['total'];
        $sp['order_status']=$record['order_status']==0?'Outstanding':'Do cum Invoiced';
        $sp['order_status_ref']=$record['order_status']==0?'':$record['order_status_ref'];
        
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        
            $items=$this->get_sales_order_details($record['order_no'],32);
            $sp['itemList']  = $items;  
			
              
	    $rows[]=$sp;
      }  
	   
	  return $rows;
        
	} 
	// auto order number and auto reference number 
	
    public function get_systype_db_info($type)
    {
    	switch ($type)
    	{
    	    case     53   : return array("0_sales_orders", "trans_type", "order_no", "reference", "ord_date");
    	    case     32   : return array("0_sales_orders", "trans_type", "order_no", "reference", "ord_date");
    	}
    }
    	
	public function get_next_trans_no($trans_type)
	{
	      
        	    $st = $this->get_systype_db_info($trans_type);
        
        	if (!($st && $st[0] && $st[2])) {
        		// this is in fact internal error condition.
        		echo "Internal error: invalid type passed to get_next_trans_no()";
        		return 0;
        	}
        	$sql1 = "SELECT MAX(`$st[2]`) as last_no FROM $st[0]";
        	if ($st[1] != null)
        		 $sql1 .= " WHERE `$st[1]`='$trans_type'";
        
        	// check also in voided transactions (some transactions like location transfer are removed completely)
        	$sql2 = "SELECT MAX(`id`) as last_no FROM 0_voided WHERE `type`='$trans_type'";
        
        	$sql = "SELECT max(last_no) last_no FROM ($sql1 UNION $sql2) a";
        //	echo $sql;die;
            $result=mysql_query($sql);
           $myrow = mysql_fetch_assoc($result);
            return $myrow['last_no'] + 1;
	    
	}
	 // auto reference 
	public function get_next($type, $line=null, $context=null) 
	{
	   	$date1 = str_replace('/', '-', $context);
            $context = date('d/m/Y', strtotime($date1));
            
		$refs = $this->get_all("trans_type='$type' AND `default`");
			$refline = mysql_fetch_assoc($refs);
	
		if ($this->_legacy_line($refline))
			return $refline['pattern'];

		return $this->_parse_next($type, $refline['prefix'].$refline['pattern'], $context);
	}
	
	public function get_all($where=null, $order_by=null)
	{
		$sql = "SELECT `trans_type`,`prefix`,`description`,`default`,`pattern`,`id`,`inactive` FROM 0_reflines";

		if ($where)
			$sql .= " WHERE  $where";
			  $result=mysql_query($sql);
		if ($result==false)
			return "Cannot get record from reflines";

		return $result;
	}
	
	public function _legacy_line($refline)
	{
		return strpbrk($refline['pattern'], '{}') == false;
	}
    public function explode_date_to_dmy($date_)
        {
             
            $date1 = str_replace('/', '-', $date_);
           $date = date('Y-m-d', strtotime($date1));
        	if ($date == "") 
        	{
        		return array(0,0,0);
        	}
        	list($year, $month, $day) = explode("-", $date);
        	return array($day, $month, $year);
        }
   public  function get_fiscalyear_begin_for_date($date)
        {
           	$sql = "SELECT begin FROM 0_fiscal_year WHERE '$date' >= begin AND '$date' <= end";
        	$result=mysql_query($sql);
        	$row = mysql_fetch_row($result);
        	if ($row != false){
        		// return (date_format($row[0],"d/m/Y"));
        		$date1 = str_replace('/', '-', $row[0]);
                  return   date('d/m/Y', strtotime($date1));
        	}
        }
	
	public function _parse_next($type, $template, $context=null)
	{
	        global  $refline_placeholders;
	        
	      //  $context = date_format($context,"d/m/Y");
            // echo $context;die;
		// date based placeholders are always allowed, so default for non-array context is date
		if (!isset($context))
			$context = date('d/m/Y');

		if (is_string($context))
			$context = array('date' => $context);

		$out = '';

		while(($start = strpos($template, '{')) !==false) {

			$out .= substr($template, 0, $start);
			$stop = strpos($template, '}');
			if ($stop === false) {
				echo (_("Invalid refline template."));
				$out .= $template; // debug
				break;
			}
			$ph = substr($template, $start+1, $stop-$start-1);
			$template = substr($template, $stop+1);
      
			if (isset($refline_placeholders[$ph])) {
				if (!isset($context[$refline_placeholders[$ph]]))
				{
					echo (sprintf(_("Missing refline context data: '%s'"), $refline_placeholders[$ph]));
					$out .= $ph; // debug
				} else {
					switch ($ph)
					{
						case 'MM':
						case 'YY':
						case 'YYYY':
							list($day, $month, $year) = $this->explode_date_to_dmy($context['date']);
							$out .= $ph == 'MM' ? sprintf('%02d', $month) : ($ph == 'YY' ? sprintf('%02d', $year%100): sprintf('%04d', $year));
							break;
						case 'FF':
						case 'FFFF':
							list($day, $month, $year) = $this->explode_date_to_dmy(get_fiscalyear_begin_for_date($context['date']));
							$out .= $ph == 'FF' ? sprintf('%02d', $year%100): sprintf('%04d', $year);
							break;
						case 'C':
							$out .= sprintf('%d', $context['customer']);
							break;

						case 'B':
							$out .= sprintf('%d', $context['branch']);
							break;

						case 'S':
							$out .= sprintf('%d', $context['supplier']);
							break;

						case 'L':
							$out .= sprintf('%s', $context['location']);
							break;

						case 'P':
							$out .= sprintf('%s', $context['pos']);
							break;

						case 'UU':
							$out .= sprintf('%02d', $context['user']);
						break;
					}
				}
			} elseif (is_numeric($ph)) {
				$out .= '{'.$ph.'}'; // index placeholder
			}
		}

		$out .= $template;	// add postfix
		 
		if (!preg_match('/^([^\{]*)?\{([^\}]*)\}(.*)/', $out, $match)) {	// parse index
			echo (_("Missing numeric placeholder in refline definition."));
			return $out;
		}
      
		$prefix = $match[1];
		$postfix = $match[3];

		$db_info = $this->get_systype_db_info($type);
		$trans_table = $db_info[0];
		$type_fld = $db_info[1];
		$tno_fld = $db_info[2];
		$ref_fld = $db_info[3];
		$type = $type;

		// retrieve last ref number in the refline from original transaction table
		$sql = "SELECT MAX(CAST(SUBSTR($ref_fld, ".(strlen($prefix)+1).",LENGTH($ref_fld)-".(strlen($postfix)+strlen($prefix)).") AS UNSIGNED))"
				." FROM `$trans_table` tbl
					LEFT JOIN 0_voided v ON tbl.`$tno_fld`=v.id AND v.type=$type"
				." WHERE ISNULL(v.id)"
				.($type_fld ? " AND tbl.`$type_fld`=$type" : '')
				." AND `$ref_fld` REGEXP "."'".'^'.preg_quote($prefix).'[0-9]*'.preg_quote($postfix).'$'."'";
	    
	        $result=mysql_query($sql);
        	$result = mysql_fetch_row($result);
		// $result = db_query($sql, 'cannot retrieve last reference');
		// $result = db_fetch_row($result);

			// fill with zeros to the length of original index placeholder
		return $prefix.sprintf('%0'.strlen($match[2]).'d', $result[0]+1).$postfix;
	}
	
	// END order number and reference
	public function add_sales_enquiry($order)
	{
	        mysql_query("START TRANSACTION");

	        $order_no = $this->get_next_trans_no(53);
	       $reference  = $this->get_next(53,'',$order->ord_date);
	       $delivery_date=date('Y-m-d', strtotime($order->delivery_date. ' +30 days'));
	       $deliver_to = str_replace("'","\'", $order->deliver_to);
	       $comments = isset($order->comments) ? str_replace("'","\'", $order->comments) : '';
	       $cust_ref = isset($order->cust_ref) ? $order->cust_ref : '';
	       $freight_cost = isset($order->freight_cost) ? $order->freight_cost : 0;
	       $prep_amount = isset($order->prep_amount) ? $order->prep_amount : 0;
	       $multiple_loc_details = isset($order->multiple_loc_details) ? $order->multiple_loc_details : '';
	       $sql = "INSERT INTO 0_sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date, order_type, ship_via, deliver_to, delivery_address, contact_phone,
		freight_cost, from_stk_loc, delivery_date, payment_terms, total, prep_amount,  cust_type, contact_person_name,salesman_id,payment_mode,packing_style,auth_req,
		auth_date,auth_remarks,quote_status,order_date,order_ref,status_remarks,user,order_status,order_status_date,order_status_ref,order_status_user,
		muliple_del_loc_req,multiple_loc_details,enq_ref_no,enq_ref_date,internal_order_ref,invoice_type,quote_auth_by)
		VALUES ('$order_no','0','$order->debtor_no', '53','$order->branch_code','$cust_ref','$reference','$comments','$order->ord_date','1', '1','$deliver_to','$order->delivery_address', '$order->phone','$freight_cost','$order->Location', '$delivery_date','$order->payment_terms','$order->total','$prep_amount','$order->cust_type','$order->contact_person_name','$order->salesman_id','$order->payment_mode','$order->packing_style','0',
		'$order->ord_date','','0','$order->ord_date','','','','0','$order->ord_date','','',
		'$order->muliple_del_loc_req','$multiple_loc_details','','$order->ord_date','','0','')";
		
			 $result=mysql_query($sql);
			 if(!$result) {
			     mysql_query("ROLLBACK");
			     return 0;
			 }
	       
        foreach ($order->itemList as $line)
	    {
           $description = str_replace("'","\'", $line->description);
           $line_discount_percent = (isset($line->discount_percent) && is_numeric($line->discount_percent)) ? $line->discount_percent : 0;
           $line_remarks = isset($line->remarks) ? $line->remarks : '';
           $stk_secondary_units = isset($line->stk_secondary_units) ? $line->stk_secondary_units : '';
           $secondary_quantity = (isset($line->secondary_quantity) && is_numeric($line->secondary_quantity)) ? $line->secondary_quantity : 0;
           $foc_quantity = (isset($line->foc_quantity) && is_numeric($line->foc_quantity)) ? $line->foc_quantity : 0;
           $sql = "INSERT INTO 0_sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent, remarks, stk_secondary_units, secondary_quantity, foc_quantity) VALUES ('$order_no','53','$line->stk_code','$description', '$line->unit_price','$line->quantity','$line_discount_percent','$line_remarks','$stk_secondary_units','$secondary_quantity','$foc_quantity')";
	   	 	$result=mysql_query($sql);
	   	 	if(!$result) {
	   	 	    mysql_query("ROLLBACK");
	   	 	    return 0;
	   	 	}
        }
        mysql_query("COMMIT");
        return $order_no;
	}
	
	
/*
	public function add_sales_enquiry($order)
	{
	        
	        $order_no = $this->get_next_trans_no(53);
	       $reference  = $this->get_next(53,'',$order->ord_date);
	       $delivery_date=date('Y-m-d', strtotime($order->delivery_date. ' +30 days'));
	       $deliver_to = str_replace("'","\'", $order->deliver_to);
	       $sql = "INSERT INTO 0_sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date, order_type, ship_via, deliver_to, delivery_address, contact_phone,
		freight_cost, from_stk_loc, delivery_date, payment_terms, total, prep_amount,  cust_type, contact_person_name,salesman_id,payment_mode,packing_style,auth_req,
		muliple_del_loc_req,multiple_loc_details)
		VALUES ('$order_no','0','$order->debtor_no', '53','$order->branch_code','$order->cust_ref','$reference','$order->comments','$order->ord_date','1', '1','$deliver_to','$order->delivery_address', '$order->phone','$order->freight_cost','$order->Location', '$delivery_date','$order->payment_terms','$order->total','$order->prep_amount','$order->cust_type','$order->contact_person_name','$order->salesman_id','$order->payment_mode','$order->packing_style','0','$order->muliple_del_loc_req','$order->multiple_loc_details')";
		
			 $result=mysql_query($sql);
	       
        foreach ($order->itemList as $line)
	    {
           $sql = "INSERT INTO 0_sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent, remarks, stk_secondary_units, secondary_quantity, foc_quantity) VALUES ('$order_no','53','$line->stk_code','$line->description', '$line->unit_price','$line->quantity','$line->discount_percent','$line->remarks','$line->stk_secondary_units','$line->secondary_quantity','$line->foc_quantity')";
	   	 	$result=mysql_query($sql);
        }
        return $order_no;
	}
	
*/
	
	public function update_sales_enquiry($order)
	{
	     mysql_query("START TRANSACTION");
	     $delivery_date=date('Y-m-d', strtotime($order->delivery_date. ' +30 days'));
	     
	       $deliver_to = str_replace("'","\'", $order->deliver_to);
	       
	   $sql = "UPDATE 0_sales_orders SET 
		debtor_no = '$order->debtor_no',
		branch_code = '$order->branch_code',
		customer_ref = '$order->cust_ref',
		comments = '$order->comments',
		ord_date = '$order->ord_date',
		deliver_to = '$deliver_to',
		delivery_address = '$order->delivery_address',
		contact_phone = '$order->phone',
		freight_cost = '$order->freight_cost',
		from_stk_loc = '$order->Location',
		delivery_date = '$delivery_date',
		payment_terms = '$order->payment_terms',
		total = '$order->total',
		prep_amount = '$order->prep_amount',
		cust_type = '$order->cust_type',
		contact_person_name = '$order->contact_person_name',
		salesman_id = '$order->salesman_id',
		payment_mode = '$order->payment_mode',
		packing_style = '$order->packing_style',
		muliple_del_loc_req = '$order->muliple_del_loc_req',
		multiple_loc_details = '$order->multiple_loc_details'
	 WHERE order_no='$order->order_no' AND trans_type='53'";
	        $result=mysql_query($sql);
	        if(!$result) {
	            mysql_query("ROLLBACK");
	            return 0;
	        }
		       
		    $sql1 = "DELETE FROM 0_sales_order_details WHERE order_no ='$order->order_no' AND trans_type='53'";
			 $result1=mysql_query($sql1);
			 if(!$result1) {
			     mysql_query("ROLLBACK");
			     return 0;
			 }
		       
	        foreach ($order->itemList as $line)
		    {
	           $description = str_replace("'","\'", $line->description);
	           $line_discount_percent = (isset($line->discount_percent) && is_numeric($line->discount_percent)) ? $line->discount_percent : 0;
	           $line_remarks = isset($line->remarks) ? $line->remarks : '';
	           $stk_secondary_units = isset($line->stk_secondary_units) ? $line->stk_secondary_units : '';
	           $secondary_quantity = (isset($line->secondary_quantity) && is_numeric($line->secondary_quantity)) ? $line->secondary_quantity : 0;
	           $foc_quantity = (isset($line->foc_quantity) && is_numeric($line->foc_quantity)) ? $line->foc_quantity : 0;
	           $sql = "INSERT INTO 0_sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent, remarks, stk_secondary_units, secondary_quantity, foc_quantity) VALUES ('$order->order_no','53','$line->stk_code','$description', '$line->unit_price','$line->quantity','$line_discount_percent','$line_remarks','$stk_secondary_units','$secondary_quantity','$foc_quantity')";
		   	 	$result=mysql_query($sql);
		   	 	if(!$result) {
		   	 	    mysql_query("ROLLBACK");
		   	 	    return 0;
		   	 	}
	        }
	        mysql_query("COMMIT");
	        return $order->order_no;
	}
	
	
	/*
	public function update_sales_enquiry($order)
	{
	     $delivery_date=date('Y-m-d', strtotime($order->delivery_date. ' +30 days'));
	     
	       $deliver_to = str_replace("'","\'", $order->deliver_to);
	       
	   $sql = "UPDATE 0_sales_orders SET 
		debtor_no = '$order->debtor_no',
		branch_code = '$order->branch_code',
		customer_ref = '$order->cust_ref',
		comments = '$order->comments',
		ord_date = '$order->ord_date',
		deliver_to = '$deliver_to',
		delivery_address = '$order->delivery_address',
		contact_phone = '$order->phone',
		freight_cost = '$order->freight_cost',
		from_stk_loc = '$order->Location',
		delivery_date = '$delivery_date',
		payment_terms = '$order->payment_terms',
		total = '$order->total',
		prep_amount = '$order->prep_amount',
		cust_type = '$order->cust_type',
		contact_person_name = '$order->contact_person_name',
		salesman_id = '$order->salesman_id',
		payment_mode = '$order->payment_mode',
		packing_style = '$order->packing_style',
		muliple_del_loc_req = '$order->muliple_del_loc_req',
		multiple_loc_details = '$order->multiple_loc_details'
	 WHERE order_no='$order->order_no' AND trans_type='53'";
        $result=mysql_query($sql);
	       
	    $sql1 = "DELETE FROM 0_sales_order_details WHERE order_no ='$order->order_no' AND trans_type='53'";
		 $result1=mysql_query($sql1);
	       
        foreach ($order->itemList as $line)
	    {
           $sql = "INSERT INTO 0_sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent, remarks, stk_secondary_units, secondary_quantity, foc_quantity) VALUES ('$order->order_no','53','$line->stk_code','$line->description', '$line->unit_price','$line->quantity','$line->discount_percent','$line->remarks','$line->stk_secondary_units','$line->secondary_quantity','$line->foc_quantity')";
	   	 	$result=mysql_query($sql);
        }
        return $order->order_no;
	}
	
	*/
	
	
	public function add_sales_quotation($order)
	{
	       mysql_query("START TRANSACTION");

	       $order_no = $this->get_next_trans_no(32);
	       $ord_date = $this->mysql_date($order->ord_date);
	       $delivery_date_base = $this->mysql_date($order->delivery_date);
	       $reference  = $this->get_next(32,'',$ord_date);
	       $delivery_date=date('Y-m-d', strtotime($delivery_date_base. ' +30 days'));

	       file_put_contents('/var/www/html/npfapi/quotation_debug.log',
	           '['.date('Y-m-d H:i:s').'] add_sales_quotation: assigned order_no='.$order_no
	           .' salesman_id='.(isset($order->salesman_id)?$order->salesman_id:'(null)')
	           .' debtor_no='.(isset($order->debtor_no)?$order->debtor_no:'(null)')
	           .' ord_date_in='.(isset($order->ord_date)?$order->ord_date:'(null)')
	           .' ord_date_mysql='.$ord_date
	           .' total='.(isset($order->total)?$order->total:'(null)')
	           .' reference='.$reference.PHP_EOL,
	           FILE_APPEND);
	       
	         $deliver_to = str_replace("'","\'", $order->deliver_to);
	         $comments = isset($order->comments) ? str_replace("'","\'", $order->comments) : '';
	         $cust_ref = isset($order->cust_ref) ? $order->cust_ref : '';
	         $freight_cost = isset($order->freight_cost) ? $order->freight_cost : 0;
	         $prep_amount = isset($order->prep_amount) ? $order->prep_amount : 0;
	         $enq_ref_no = isset($order->enq_ref_no) ? $order->enq_ref_no : '';
	         $enq_ref_date = isset($order->enq_ref_date) && $order->enq_ref_date != '' ? $this->mysql_date($order->enq_ref_date) : $ord_date;
	         $multiple_loc_details = isset($order->multiple_loc_details) ? $order->multiple_loc_details : '';
	         $order_discount_percent = isset($order->discount_percent) ? $order->discount_percent : 0;
	         $auth_req = 0;
	       
	       
	       $sql = "INSERT INTO 0_sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date, order_type, ship_via, deliver_to, delivery_address, contact_phone,
		freight_cost, from_stk_loc, delivery_date, payment_terms, total, prep_amount,  cust_type, contact_person_name,salesman_id,payment_mode,packing_style,auth_req,
		auth_date,auth_remarks,quote_status,order_date,order_ref,status_remarks,user,order_status,order_status_date,order_status_ref,order_status_user,
		muliple_del_loc_req,multiple_loc_details,enq_ref_no,enq_ref_date,internal_order_ref,invoice_type,quote_auth_by)
		VALUES ('$order_no','0','$order->debtor_no', '32','$order->branch_code','$cust_ref','$reference','$comments','$ord_date','1', '1','$deliver_to','$order->delivery_address', '$order->phone','$freight_cost','$order->Location', '$delivery_date','$order->payment_terms','$order->total','$prep_amount','$order->cust_type','$order->contact_person_name','$order->salesman_id','$order->payment_mode','$order->packing_style','0',
		'$ord_date','','0','$ord_date','','','','0','$ord_date','','',
		'$order->muliple_del_loc_req','$multiple_loc_details','$enq_ref_no','$enq_ref_date','','0','')";
	
		 $result=mysql_query($sql);
		 if(!$result) {
		     $err = mysql_error();
		     error_log("add_sales_quotation order insert failed: ".$err);
		     file_put_contents('/var/www/html/npfapi/quotation_debug.log',
		         '['.date('Y-m-d H:i:s').'] add_sales_quotation HEADER INSERT FAILED order_no='.$order_no.' err='.$err.PHP_EOL,
		         FILE_APPEND);
		     mysql_query("ROLLBACK");
		     return 0;
		 }

        foreach ($order->itemList as $line)
	    {
	        $description = str_replace("'","\'", $line->description);
	        $line_discount_percent = (isset($line->discount_percent) && is_numeric($line->discount_percent)) ? $line->discount_percent : 0;
	        $line_remarks = isset($line->remarks) ? $line->remarks : '';
	        $stk_secondary_units = isset($line->stk_secondary_units) ? $line->stk_secondary_units : '';
	        $secondary_quantity = (isset($line->secondary_quantity) && is_numeric($line->secondary_quantity)) ? $line->secondary_quantity : 0;
	        $foc_quantity = (isset($line->foc_quantity) && is_numeric($line->foc_quantity)) ? $line->foc_quantity : 0;
           $sql = "INSERT INTO 0_sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent, remarks, stk_secondary_units, secondary_quantity, foc_quantity) VALUES ('$order_no','32','$line->stk_code','$description', '$line->unit_price','$line->quantity','$line_discount_percent','$line_remarks','$stk_secondary_units','$secondary_quantity','$foc_quantity')";
	   	 	$result=mysql_query($sql);
	   	 	if(!$result) {
	   	 	    $err = mysql_error();
	   	 	    error_log("add_sales_quotation detail insert failed: ".$err);
	   	 	    file_put_contents('/var/www/html/npfapi/quotation_debug.log',
	   	 	        '['.date('Y-m-d H:i:s').'] add_sales_quotation DETAIL INSERT FAILED order_no='.$order_no.' stk_code='.$line->stk_code.' err='.$err.PHP_EOL,
	   	 	        FILE_APPEND);
	   	 	    mysql_query("ROLLBACK");
	   	 	    return 0;
	   	 	}
	   	 	
	   	   $cust_price = $this->customer_prices($line->stk_code,$order->debtor_no,1);
	   	    $last_quote_price = $this->sales_quote_last_price($line->stk_code,$order->debtor_no, 1);
	   	   $sales_price = $this->get_kit_price($line->stk_code,'OMR');
	   	   
	   	   
	   	   if($cust_price>0){
						$line_total = round($line->quantity * $line->unit_price * (1 - $line_discount_percent),3);
						$unit_price = $line->quantity ? round($line_total/$line->quantity,3) : 0;
					if($cust_price>$unit_price)
						$auth_req=1;
						
				} 
				
				else if($sales_price>0)
				{
					$final_price = round($sales_price *(1 - $order_discount_percent),3);
					$line_total = round($line->quantity * $line->unit_price * (1 - $line_discount_percent),3);
						$unit_price = $line->quantity ? round($line_total/$line->quantity,3) : 0;
					if($final_price>$unit_price)
						$auth_req=1;
				}
				else{
				
				$line_total = round($line->quantity * $line->unit_price * (1 - $line_discount_percent),3);
							$unit_price = $line->quantity ? round($line_total/$line->quantity,3) : 0;
				if($last_quote_price>$unit_price || $last_quote_price==0)
						$auth_req=1;
				}		
					
					if($auth_req==1)
				{
					$sql= "UPDATE 0_sales_orders SET auth_req=1 WHERE order_no='$order_no'  AND trans_type='32'";
					 $res=mysql_query($sql);
					 if(!$res) {
					     $err = mysql_error();
					     error_log("add_sales_quotation auth update failed: ".$err);
					     file_put_contents('/var/www/html/npfapi/quotation_debug.log',
					         '['.date('Y-m-d H:i:s').'] add_sales_quotation AUTH UPDATE FAILED order_no='.$order_no.' err='.$err.PHP_EOL,
					         FILE_APPEND);
					     mysql_query("ROLLBACK");
					     return 0;
					 }
				}

        }

        mysql_query("COMMIT");

        $verify_sql = "SELECT salesman_id, ord_date, total, debtor_no FROM 0_sales_orders WHERE order_no='$order_no' AND trans_type='32'";
        $verify_res = mysql_query($verify_sql);
        $verify_row = $verify_res ? mysql_fetch_assoc($verify_res) : null;
        file_put_contents('/var/www/html/npfapi/quotation_debug.log',
            '['.date('Y-m-d H:i:s').'] add_sales_quotation COMMITTED order_no='.$order_no
            .' stored='.($verify_row ? json_encode($verify_row) : 'NOT FOUND').PHP_EOL,
            FILE_APPEND);

        return $order_no;
	}
	
	//ramesh for sales quotation approvals
	public function get_kit_price($stock_id,$currency)
	{
	    $sql = "SELECT price FROM 0_prices WHERE stock_id = '$stock_id' AND curr_abrev = '$currency'";
         $result=mysql_query($sql);
       	 $row = mysql_fetch_row($result);
         return isset($row[0])?$row[0]:0;
	    
	}
	
    public function customer_prices($item_code, $customer_id, $sales_type_id)
    {
    	$sql ="SELECT price FROM 0_customer_prices WHERE customer_id='$customer_id' AND stock_id='$item_code' AND sales_type_id='$sales_type_id' ORDER BY id DESC LIMIT 1";

        $result=mysql_query($sql);
       	$row = mysql_fetch_row($result);
        	if(isset($row[0]))
       	return number_format($row[0],4);
       	else
       	{
        $sql ="SELECT price FROM 0_prices WHERE curr_abrev='OMR' AND stock_id='$item_code' AND sales_type_id='$sales_type_id' ORDER BY id DESC LIMIT 1";
        $result=mysql_query($sql);
       	$row = mysql_fetch_row($result);
       	if(isset($row[0]))
       	return number_format($row[0],4);
       	else 
       	return "0";
       	}
    }
	
	public function sales_quote_last_price($stock_id,$customer_id,$sales_type)
    {
   
    	$sql ="SELECT (details.unit_price *(1-details.discount_percent)) as last_quote_price FROM 0_sales_orders so , 0_sales_order_details details WHERE so.order_no=details.order_no AND so.trans_type=details.trans_type AND so.trans_type='32' AND details.stk_code='$stock_id' AND so.debtor_no='$customer_id' AND so.auth_req=2 ORDER BY details.id DESC LIMIT 1";
    	 $res = mysql_query($sql);
    	$result = mysql_fetch_row($res);
    	if($result['0']>0){
    	return $result['0'];
    	}else{
    		return 0;
    	} 
    } 

	public function update_sales_quotation($order)
	{
	     mysql_query("START TRANSACTION");
	     $ord_date = $this->mysql_date($order->ord_date);
	     $delivery_date_base = $this->mysql_date($order->delivery_date);
	     $delivery_date=date('Y-m-d', strtotime($delivery_date_base. ' +30 days'));
	     
	       $deliver_to = str_replace("'","\'", $order->deliver_to);
	       $customer_ref = isset($order->customer_ref) ? $order->customer_ref : (isset($order->cust_ref) ? $order->cust_ref : '');
	       $freight_cost = isset($order->freight_cost) ? $order->freight_cost : 0;
	       $prep_amount = isset($order->prep_amount) ? $order->prep_amount : 0;
	       $multiple_loc_details = isset($order->multiple_loc_details) ? $order->multiple_loc_details : '';
	       $enq_ref_no = isset($order->enq_ref_no) ? $order->enq_ref_no : '';
	       $enq_ref_date = isset($order->enq_ref_date) && $order->enq_ref_date != '' ? $this->mysql_date($order->enq_ref_date) : $ord_date;
	       
	     $sql = "UPDATE 0_sales_orders SET 
		debtor_no = '$order->debtor_no',
		branch_code = '$order->branch_code',
		customer_ref = '$customer_ref',
		comments = '$order->comments',
		ord_date = '$ord_date',
		deliver_to = '$deliver_to',
		delivery_address = '$order->delivery_address',
		contact_phone = '$order->phone',
		freight_cost = '$freight_cost',
		from_stk_loc = '$order->Location',
		delivery_date = '$delivery_date',
		payment_terms = '$order->payment_terms',
		total = '$order->total',
		prep_amount = '$prep_amount',
		cust_type = '$order->cust_type',
		contact_person_name = '$order->contact_person_name',
		salesman_id = '$order->salesman_id',
		payment_mode = '$order->payment_mode',
		packing_style = '$order->packing_style',
		auth_req = '1',
		muliple_del_loc_req = '$order->muliple_del_loc_req',
		multiple_loc_details = '$multiple_loc_details',
		enq_ref_no = '$enq_ref_no',
		enq_ref_date = '$enq_ref_date'
	     WHERE order_no='$order->order_no' AND trans_type='32'";
        $result=mysql_query($sql);
        if(!$result) {
            error_log("update_sales_quotation order update failed: ".mysql_error());
            mysql_query("ROLLBACK");
            return 0;
        }
		       
	    $sql1 = "DELETE FROM 0_sales_order_details WHERE order_no ='$order->order_no' AND trans_type='32'";
		 $result1=mysql_query($sql1);
		 if(!$result1) {
		     error_log("update_sales_quotation detail delete failed: ".mysql_error());
		     mysql_query("ROLLBACK");
		     return 0;
		 }
        foreach ($order->itemList as $line)
	    {
		      $description = str_replace("'","\'", $line->description);
		      $line_discount_percent = (isset($line->discount_percent) && is_numeric($line->discount_percent)) ? $line->discount_percent : 0;
		      $line_remarks = isset($line->remarks) ? $line->remarks : '';
		      $stk_secondary_units = isset($line->stk_secondary_units) ? $line->stk_secondary_units : '';
		      $secondary_quantity = (isset($line->secondary_quantity) && is_numeric($line->secondary_quantity)) ? $line->secondary_quantity : 0;
		      $foc_quantity = (isset($line->foc_quantity) && is_numeric($line->foc_quantity)) ? $line->foc_quantity : 0;
           $sql = "INSERT INTO 0_sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent, remarks, stk_secondary_units, secondary_quantity, foc_quantity) VALUES ('$order->order_no','32','$line->stk_code','$description', '$line->unit_price','$line->quantity','$line_discount_percent','$line_remarks', '$stk_secondary_units', '$secondary_quantity', '$foc_quantity')";
	   
	   	 	$result=mysql_query($sql);
	   	 	if(!$result) {
	   	 	    error_log("update_sales_quotation detail insert failed: ".mysql_error());
	   	 	    mysql_query("ROLLBACK");
	   	 	    return 0;
	   	 	}
        }
        mysql_query("COMMIT");
        return $order->order_no;
	}
	
	public function add_task_details($task_details)
	{
	    
	    
	      
	    $task_status= $task_details->follow_up_required?0:1; 
	    
	    if($task_details->parent_task_id && $task_status) {
	        $sql ="UPDATE 0_tasks SET status=1 WHERE id='$task_details->parent_task_id'";
	        $result=mysql_query($sql);
	        if(!$result) {
	            return 0;
	        }
	    }
		    
		    
		    
	    $follow_up_date = isset($task_details->follow_up_date) && $task_details->follow_up_date != '' ? "'$task_details->follow_up_date'" : "NULL";
	    $closed_date = $task_status ? $task_details->task_date : '0001-01-01';
	    $remarks = isset($task_details->remarks) ? $task_details->remarks : '';
	    $assigned_by = isset($task_details->assigned_by) ? $task_details->assigned_by : 0;
	    $sql = "INSERT INTO 0_tasks (customer_id, date_, task_details, follow_up_req, follow_up_date, parent_task_id,sales_person_id,assigned_by,status,closed_by,closed_date,remarks)
		VALUES ('$task_details->customer_id','$task_details->task_date','$task_details->task_details','$task_details->follow_up_required',$follow_up_date,'$task_details->parent_task_id','$task_details->sales_person_id','$assigned_by','$task_status','','$closed_date','$remarks')";
		
		 $result=mysql_query($sql);
		 if(!$result) {
		     return 0;
		 }

        return  mysql_insert_id();
	}
	
  /*	public function getTaskDetails($from_date,$to_date,$sales_person_id,$customer_id,$task_id,$page_no)
	{
	    
	   $offset= $page_no*10 - 10;
	   

	   $sql = "SELECT 
			task.*,
			debtor.name,
			salesman.salesman_name,
			debtor.cust_code
		FROM 0_tasks as task,0_debtors_master as debtor, 0_salesman salesman
			WHERE task.customer_id = debtor.debtor_no
			AND salesman.salesman_code = task.sales_person_id ";
		
		   
		 	$date_after = implode("-", array_reverse(explode("/", $from_date)));
		 	
			$date_before = implode("-", array_reverse(explode("/", $to_date)));

			$sql .=  " AND task.date_ >= '$date_after'"." AND task.date_ <= '$date_before'";
			
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND task.sales_person_id = '$sales_person_id'"; 
			}
			
			if($customer_id!=0)
			{
			   $sql .=  " AND task.customer_id = '$customer_id'"; 
			}
			
				if($task_id!=0)
			{
			   $sql .=  " AND task.id = '$task_id'"; 
			}
	
	    	$sql .= " ORDER BY task.id LIMIT $offset,10";
				
				
				echo $sql; die;

		$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['task_id']=$record['id'];
	    $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	    $sp['customer_code']=$record['cust_code'];
	    $sp['task_details']=$record['task_details'];
        $sp['task_date']=date("d/m/Y", strtotime($record['date_']));
        $sp['follow_up_req']=$record['follow_up_req'];
		$sp['follow_up_date']=date("d/m/Y", strtotime($record['follow_up_date']));
		$sp['parent_task_id']=$record['parent_task_id'];
		$sp['sales_person_name']=$record['salesman_name'];
        $sp['sales_person_id']=$record['sales_person_id'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        
	    $rows[]=$sp;
      }  
	   
	  return $rows;

        
	} 	*/
	
	
	
	public function getTaskHistory($task_id)
	{
	    
	   $sql= "SELECT 
			task.*,
			debtor.name,
			salesman.salesman_name,
			debtor.cust_code
		FROM 0_tasks as task,0_debtors_master as debtor, 0_salesman salesman
			WHERE task.customer_id = debtor.debtor_no
			AND salesman.salesman_code = task.sales_person_id AND task.id = '$task_id'";
			
		$sql.=" UNION ";	
	    
	    $sql.= "SELECT 
			task.*,
			debtor.name,
			salesman.salesman_name,
			debtor.cust_code
		FROM 0_tasks as task,0_debtors_master as debtor, 0_salesman salesman
			WHERE task.customer_id = debtor.debtor_no
			AND salesman.salesman_code = task.sales_person_id AND task.parent_task_id = '$task_id'";
			

		$result=mysql_query($sql);
		
		return $result; 
	}
	
	
	public function getTaskInfo($task_id,$sales_person_id=0)
	{
	   $sql = "SELECT 
			task.*,
			debtor.name,
			salesman.salesman_name,
			debtor.cust_code
		FROM 0_tasks as task,0_debtors_master as debtor, 0_salesman salesman
			WHERE task.customer_id = debtor.debtor_no
			AND salesman.salesman_code = task.sales_person_id AND task.id = '$task_id'";
			
		if($sales_person_id)
		$sql.=" AND task.sales_person_id='$sales_person_id'";
			
		$result=mysql_query($sql);
		
		return $result;
	}
	
	public function getTaskDetails($task_id,$sales_person_id=0)
	{
	   $flag=0;
	  while(1)
	  {
	      if($flag==0)
	      $result=$this->getTaskInfo($task_id,$sales_person_id);
	      else if($flag==1 && $parent_task_id!=0)
	      $result= $this->getTaskInfo($parent_task_id,$sales_person_id);
	      else
	      break;
	    if($record=mysql_fetch_assoc($result))
        {
          
		$sp['task_id']=$record['id'];
		$sp['status']=$record['status'];
	    $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	    $sp['customer_code']=$record['cust_code'];
	    $sp['task_details']=$record['task_details'];
        $sp['task_date']=date("d/m/Y", strtotime($record['date_']));
        $sp['task_details']=$record['task_details'];
        $sp['follow_up_req']=$record['follow_up_req'];
		$sp['follow_up_date']=date("d/m/Y", strtotime($record['follow_up_date']));
		$sp['parent_task_id']=$record['parent_task_id'];
		$sp['sales_person_name']=$record['salesman_name'];
        $sp['sales_person_id']=$record['sales_person_id'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        $rows[]=$sp;
        $parent_task_id=$record['parent_task_id'];
        $flag=1;
       }
	  }

         	 return $rows;
	} 
	
	
	public function getTaskDetails_backup($task_id)
	{
	    
	   $sql = "SELECT 
			task.*,
			debtor.name,
			salesman.salesman_name,
			debtor.cust_code
		FROM 0_tasks as task,0_debtors_master as debtor, 0_salesman salesman
			WHERE task.customer_id = debtor.debtor_no
			AND salesman.salesman_code = task.sales_person_id AND task.id = '$task_id'";
		
		$result=mysql_query($sql);
        

        $rows=array();
  
      if($record=mysql_fetch_assoc($result))
      {
          
        if($record['parent_task_id']==0)
        {
		$sp['task_id']=$record['id'];
	    $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	    $sp['customer_code']=$record['cust_code'];
	    $sp['task_details']=$record['task_details'];
        $sp['task_date']=date("d/m/Y", strtotime($record['date_']));
        $sp['follow_up_req']=$record['follow_up_req'];
		$sp['follow_up_date']=date("d/m/Y", strtotime($record['follow_up_date']));
		$sp['parent_task_id']=$record['parent_task_id'];
		$sp['sales_person_name']=$record['salesman_name'];
        $sp['sales_person_id']=$record['sales_person_id'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        $rows[]=$sp;
        }
        else
        {
            
          
            $result= $this ->getTaskHistory($record['parent_task_id']);
            
            while($record=mysql_fetch_assoc($result))
            {
                $sp['task_id']=$record['id'];
	            $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	            $sp['customer_code']=$record['cust_code'];
	            $sp['task_details']=$record['task_details'];
                $sp['task_date']=date("d/m/Y", strtotime($record['date_']));
                $sp['follow_up_req']=$record['follow_up_req'];
		        $sp['follow_up_date']=date("d/m/Y", strtotime($record['follow_up_date']));
		        $sp['parent_task_id']=$record['parent_task_id'];
		        $sp['sales_person_name']=$record['salesman_name'];
                $sp['sales_person_id']=$record['sales_person_id'];
                $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
                $rows[]=$sp;
            }
        }
  
      }  
	   
	  return $rows;

        
	} 
	
	public function getOpenTaskDetails($sales_person_id)
	{
	    
	    $today=date('Y-m-d');
	
	   $sql = "SELECT 
			task.*,
			debtor.name,
			debtor.debtor_no,
			salesman.salesman_name,
			debtor.cust_code
		FROM 0_tasks as task,0_debtors_master as debtor, 0_salesman salesman
			WHERE task.customer_id = debtor.debtor_no
			AND salesman.salesman_code = task.sales_person_id and status=0 and id not in (select parent_task_id from 0_tasks)";
		
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND task.sales_person_id = '$sales_person_id'"; 
			}
			
		
	    	$sql .= " ORDER BY task.id ";
	    	

		$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['task_id']=$record['id'];
	    $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	    $sp['customer_code']=$record['cust_code'];
	    $sp['customer_id']=$record['debtor_no'];
	    $sp['task_details']=$record['task_details'];
        $sp['task_date']=date("d/m/Y", strtotime($record['date_']));
        $sp['follow_up_req']=$record['follow_up_req'];
		$sp['follow_up_date']=date("d/m/Y", strtotime($record['follow_up_date']));
		$sp['parent_task_id']=$record['parent_task_id'];
		$sp['sales_person_name']=$record['salesman_name'];
        $sp['sales_person_id']=$record['sales_person_id'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        
	    $rows[]=$sp;
      }  
	   
	  return $rows;

        
	} 	
	
	
	public function add_collection_details($collection_details)
	{
	    
	    $collection_date = implode("-", array_reverse(explode("/", $collection_details->collection_date)));
	      
	    $sql = "INSERT INTO 0_collections (customer_id, date_, collection_details,sales_person_id,payment_type,amount,against_invoice,bill_no)
		VALUES ('$collection_details->customer_id','$collection_date','$collection_details->collection_details','$collection_details->sales_person_id','$collection_details->payment_type','$collection_details->amount','$collection_details->against_invoice','$collection_details->bill_no')";

		 $result=mysql_query($sql);
		 if(!$result) {
		     return 0;
		 }

         $collection_id= mysql_insert_id();
         
         $sql = "SELECT count(*) FROM 0_sales_executive_commitments WHERE bill_no='$collection_details->bill_no' AND sales_person_id='$collection_details->sales_person_id'";

         $result=mysql_query($sql);
        
        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!==null)
        {
             $sql ="UPDATE 0_sales_executive_commitments SET collected_amount=collected_amount+'$collection_details->amount' WHERE sales_person_id='$collection_details->sales_person_id' and bill_no='$collection_details->bill_no'"; 
        
	             $result=mysql_query($sql);
	             if(!$result) {
	                 return 0;
	             }
	        }
        
         
         return $collection_id;
	}
	
	
	public function upload_collection_details($collection_id,$file_name,$sales_person_id)
	{
	    
	     $sql = "INSERT INTO 0_upload_collections_data (collection_id,file_name,sales_person_id)
		VALUES ($collection_id,'$file_name','$sales_person_id')";
		

		 $result=mysql_query($sql);
		 if(!$result) {
		     return 0;
		 }

        return  mysql_insert_id();
	}
	
	public function upload_quotation_sign_details($quote_id,$file_name,$sales_person_id)
	{
	    
	     $sql = "INSERT INTO 0_upload_quotations_sign (quote_id,file_name,sales_person_id)
		VALUES ($quote_id,'$file_name','$sales_person_id')";
		

		 $result=mysql_query($sql);
		 if(!$result) {
		     return 0;
		 }

        return  mysql_insert_id();
	}
	
	public function getCollectionDetails($from_date,$to_date,$sales_person_id,$customer_id,$collection_id,$page_no)
	{
	    
	   $offset= $page_no*10 - 10;
	   
	   $sql = "SELECT 
			collection.*,
			debtor.name,
			salesman.salesman_name,
			debtor.cust_code
		FROM 0_collections as collection,0_debtors_master as debtor, 0_salesman salesman
			WHERE collection.customer_id = debtor.debtor_no
			AND salesman.salesman_code = collection.sales_person_id ";
		
		   
		 	$date_after = implode("-", array_reverse(explode("/", $from_date)));
		 	
			$date_before = implode("-", array_reverse(explode("/", $to_date)));

			$sql .=  " AND collection.date_ >= '$date_after'"." AND collection.date_ <= '$date_before'";
			
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND collection.sales_person_id = '$sales_person_id'"; 
			}
			
			if($customer_id!=0)
			{
			   $sql .=  " AND collection.customer_id = '$customer_id'"; 
			}
			
			if($collection_id!=0)
			{
			   $sql .=  " AND collection.id = '$collection_id'"; 
			}
	
	    	$sql .= " ORDER BY collection.id LIMIT $offset,10";
	    	

		$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['collection_id']=$record['id'];
	    $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	    $sp['customer_code']=$record['cust_code'];
	    $sp['collection_details']=$record['collection_details'];
        $sp['collection_date']=date("d/m/Y", strtotime($record['date_']));
       	$sp['sales_person_name']=$record['salesman_name'];
        $sp['sales_person_id']=$record['sales_person_id'];
        $sp['payment_type']=$record['payment_type']==1?"Cash":($record['payment_type']==2?"Cheque":"Online Payment");
         $sp['amount']=$record['amount'];
        $sp['cust_balance']= $this ->get_customer_outstanding_balances($record['cust_code']);
        
        if($this ->getUploadedFile($record['id']))
        $sp['url']="https://npfapi.techintegraerp.net/collectionuploads/".$this ->getUploadedFile($record['id']);
        else
        $sp['url']='No Data Found';
        
	    $rows[]=$sp;
      }  
	   
	  return $rows;

        
	}
	
	 public function getSalesPersonAnalytics($searchString)
	{
		 $sql = "SELECT count(*) FROM 0_sales_orders 
			WHERE trans_type = 32 AND auth_req=1 
			AND salesman_id='$searchString'";

        $result=mysql_query($sql);
        
        $pending_quotations=0;
        
        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!==null)
        {
           $pending_quotations=$row[0];
        }
        
        
        $sql = "SELECT count(*) FROM 0_sales_orders 
			WHERE trans_type = 32 AND auth_req=2 
			AND salesman_id='$searchString'";

        $result=mysql_query($sql);
        
        $approved_quotations=0;
        
        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!==null)
        {
           $approved_quotations=$row[0];
        }
        
         $month_start_date=date('Y-m-01');
         $month_end_date=date('Y-m-t');
         
         
         $sql = "SELECT sum(total) FROM 0_sales_orders 
			WHERE trans_type = 32 AND quote_status=1 
			AND salesman_id='$searchString' and ord_date between '$month_start_date' and '$month_end_date' ";

        $result=mysql_query($sql);
        
        $current_month_revenue="0";
        
        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!==null)
        {
           $current_month_revenue=$row[0];
        }
          
          
      $year = date('Y');
      $year_starting_date=$year.'-01-01';
      $year_ending_date=$year.'-12-31';
      
       $sql = "SELECT sum(total) FROM 0_sales_orders 
			WHERE trans_type = 32 AND quote_status=1 
			AND salesman_id='$searchString' and ord_date between '$year_starting_date' and '$year_ending_date' ";

        $result=mysql_query($sql);
        
        $current_year_revenue="0";
        
        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!==null)
        {
           $current_year_revenue=$row[0];
        }
        
        
        $current_date=date('Y-m-d');
      
       $sql = "SELECT count(*) FROM 0_tasks 
			WHERE sales_person_id='$searchString' and follow_up_date='$current_date' ";
			
        $result=mysql_query($sql);
        
        $today_follow_ups="0";
        
        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!==null)
        {
           $today_follow_ups=$row[0];
        }
        
        
        
         $tomorrow = date("Y-m-d", strtotime("+1 day"));
         
        $sql = "SELECT count(*) FROM 0_tasks 
			WHERE sales_person_id='$searchString' and follow_up_date='$tomorrow' ";
			
        $result=mysql_query($sql);
        
        $tomorrow_follow_ups="0";
        
        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!==null)
        {
           $tomorrow_follow_ups=$row[0];
        }
        
        
        $sql = "SELECT sum(commited_amount),sum(collected_amount) FROM 0_sales_executive_commitments  
			WHERE sales_person_id='$searchString'";
			
        $result=mysql_query($sql);
        
        $commited_amount="0";
        $collected_amount="0";
        
        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!=null)
        {
           $commited_amount=$row[0];
           $collected_amount=$row[1];
        }
        
        
         $today=date("Y-m-d");
	     $sql = "SELECT count(*) FROM 0_vehicle_loading_details vld,0_debtors_master dm,0_cust_branch cb WHERE vld.customer_code=dm.cust_code and dm.debtor_no=cb.debtor_no and cb.salesman='$searchString' and loading_date='$today'";
        
          $result=mysql_query($sql);
        
        $today_deliveries=0;

        $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!=null)
        {
           $today_deliveries=$row[0];
        }
        
        
         
	   $sql = "SELECT count(*) total_deliveries FROM 0_vehicle_loading_details  WHERE loading_date='$today'";
		
	   $result=mysql_query($sql);
	   
	   $total_deliveries=0;
	   
       $row=mysql_fetch_row($result);
        
        if(isset($row) && $row[0]!=null)
        {
           $total_deliveries=$row[0];
        }
        
        
        
        
        $rows['pending_quotations']=$pending_quotations;
        $rows['approved_quotations']=$approved_quotations;
        $rows['current_month_revenue']=$current_month_revenue;
        $rows['current_year_revenue']=$current_year_revenue;
        $rows['today_followups']=$today_follow_ups;
        $rows['tomorrow_followups']=$tomorrow_follow_ups;
        $rows['commited_amount']=$commited_amount;
        $rows['collected_amount']=$collected_amount;
        $rows['today_deliveries']=$today_deliveries;
        $rows['total_deliveries']=$total_deliveries;
         
        
        return $rows;

	}
	
	public function getCustomerSpecificPrice($customer_id,$stock_id)
	{
	   $cust_price = $this->customer_prices($stock_id,$customer_id,1);
	   
	    $rows=array();
	    $rows['customer_price']=$cust_price;
	    return $rows;
	}
	
	public function getCustomerBalances($sales_person_id)
	{
	   
	   $sql = "SELECT 
			cob.*,
			debtor.name
		FROM 0_cust_outstanding_balances as cob,0_debtors_master as debtor, 0_salesman salesman,0_cust_branch cb
			WHERE cob.customer_code = debtor.cust_code and debtor.debtor_no=cb.debtor_no 
			AND salesman.salesman_code = cb.salesman ";
		
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND cb.salesman = '$sales_person_id'"; 
			}
			

		$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['bill_date']=date("d/m/Y", strtotime($record['bill_date']));
	    $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	    $sp['customer_code']=$record['customer_code'];
	    $sp['bill_no']=$record['bill_no'];
        $sp['due_date']=date("d/m/Y", strtotime($record['due_date']));
		$sp['balance']=$record['balance'];
	    $rows[]=$sp;
      }  
	   
	  return $rows;
	}
	
	
	public function getCustomerUncommitedBalances($sales_person_id,$customer_id=0)
	{
	   
	   $sql = "SELECT 
			cob.*,
			debtor.name
		FROM 0_cust_outstanding_balances as cob,0_debtors_master as debtor, 0_salesman salesman,0_cust_branch cb
			WHERE cob.customer_code = debtor.cust_code and debtor.debtor_no=cb.debtor_no 
			AND salesman.salesman_code = cb.salesman and cob.bill_no not in (select bill_no from 0_sales_executive_commitments)";
		
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND cb.salesman = '$sales_person_id'"; 
			}
			
			if($customer_id!=0)
			{
			   $sql .=  " AND debtor.debtor_no = '$customer_id'"; 
			}


		$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
		$sp['bill_date']=date("d/m/Y", strtotime($record['bill_date']));
	    $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	    $sp['customer_code']=$record['customer_code'];
	    $sp['bill_no']=$record['bill_no'];
        $sp['due_date']=date("d/m/Y", strtotime($record['due_date']));
		$sp['balance']=$record['balance'];
	    $rows[]=$sp;
      }  
	   
	  return $rows;
	}
	
    public function add_commitment_details($commitment_details)
	{
		      
	    $approval_status = isset($commitment_details->approval_status) ? $commitment_details->approval_status : 0;
	    $approved_date = isset($commitment_details->approved_date) && $commitment_details->approved_date != '' ? $commitment_details->approved_date : $commitment_details->date_;
	    $collected_amount = isset($commitment_details->collected_amount) ? $commitment_details->collected_amount : 0;
	    $sql = "INSERT INTO 0_sales_executive_commitments (date_, customer_code, bill_date,bill_no, due_date,balance,commited_date,commited_amount,sales_person_id,approval_status,approved_date,collected_amount)
		VALUES ('$commitment_details->date_','$commitment_details->customer_code','$commitment_details->bill_date','$commitment_details->bill_no','$commitment_details->due_date','$commitment_details->balance','$commitment_details->commited_date','$commitment_details->commited_amount','$commitment_details->sales_person_id','$approval_status','$approved_date','$collected_amount')";
		
		 $result=mysql_query($sql);
		 if(!$result) {
		     return 0;
		 }

        return  mysql_insert_id();
	}
	
	public function getAllcommitments($sales_person_id)
	{
	   
	   $sql = "SELECT 
			seco.*,
			debtor.name
		FROM 0_sales_executive_commitments as seco,0_debtors_master as debtor, 0_salesman salesman
			WHERE seco.customer_code = debtor.cust_code 
			AND salesman.salesman_code = seco.sales_person_id ";
		
	  				
			if($sales_person_id!=0)
			{
			   $sql .=  " AND seco.sales_person_id = '$sales_person_id'"; 
			}
			

		$result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
        $sp['id']=$record['id'];  
		$sp['bill_date']=date("d/m/Y", strtotime($record['bill_date']));
	    $sp['customer_name']=mb_convert_encoding($record['name'], "UTF-8", "HTML-ENTITIES");
	    $sp['customer_code']=$record['customer_code'];
	    $sp['bill_no']=$record['bill_no'];
        $sp['due_date']=date("d/m/Y", strtotime($record['due_date']));
		$sp['balance']=$record['balance'];
		$sp['commited_date']=date("d/m/Y", strtotime($record['commited_date']));
		$sp['commited_amount']=$record['commited_amount'];
		$sp['approval_status']=$record['approval_status'];
		$sp['collected_amount']=$record['collected_amount'];
	    $rows[]=$sp;
      }  
	   
	  return $rows;
	}
	
	public function updateCommitment($commitment_details)
	{
	      
	    $sql = "UPDATE 0_sales_executive_commitments SET commited_date='$commitment_details->commited_date',commited_amount='$commitment_details->commited_amount' WHERE id='$commitment_details->id'";
		
		$result=mysql_query($sql);
		if(!$result) {
		    return 0;
		}

        return $commitment_details->id;
	}
	
	
	public function closeTaskDetails($closeTaskDetails)
	{
	    
	    $ref_task_id=$closeTaskDetails->id;
	    while(1)
	    {
	        
	     $sql = "SELECT parent_task_id FROM 0_tasks where id = '$ref_task_id'";
	     $result=mysql_query($sql);
	     
	     if($record=mysql_fetch_assoc($result))
	     {
            $sql = "UPDATE 0_tasks SET closed_date='$closeTaskDetails->closed_date',status=1,closed_by='$closeTaskDetails->closed_by' WHERE id='$ref_task_id'";
	     	$result=mysql_query($sql);
	     	$ref_task_id = $record['parent_task_id'];
	     }
	     else
	     {
	         break;
	     }
	    }
		
	      
	    $sql = "UPDATE 0_tasks SET closed_date='$closeTaskDetails->closed_date',status=1,closed_by='$closeTaskDetails->closed_by' WHERE id='$ref_task_id'";
		
		$result=mysql_query($sql);

        return $closeTaskDetails->id;
	}
	
	
	
	public function getAllLoadingDetails($vehicle_no)
	{
	   
	    $today=date("Y-m-d");
	   
	   $sql = "SELECT *	FROM 0_vehicle_loading_details where vehicle_no='$vehicle_no' and loading_date='$today'";
	  
	 //  $sql = "SELECT *	FROM 0_vehicle_loading_details where vehicle_no='$vehicle_no'";
		
	   $result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
        $sp['id']=$record['id'];  
		$sp['loading_date']=date("d/m/Y", strtotime($record['loading_date']));
	    $sp['vehicle_no']=$record['vehicle_no'];
	    $sp['delivery_route_plan']=$record['delivery_route_plan'];
	    $sp['do_cum_invoice_no']=$record['do_cum_invoice_no'];
	    $sp['do_cum_invoice_date']=date("d/m/Y", strtotime($record['do_cum_invoice_date']));
	    $sp['customer_code']=$record['customer_code'];
	    $sp['customer_name']=mb_convert_encoding($record['customer_name'], "UTF-8", "HTML-ENTITIES");
      	$sp['driver_name']=$record['driver_name'];
		$sp['driver_number']=$record['driver_number'];
		$sp['delivery_status']=$record['delivery_status'];
		$sp['remarks']=$record['remarks'];
		$sp['delivery_inputs']=$record['delivery_inputs'];
	    $rows[]=$sp;
      }  
	   
	  return $rows;
	}
	
	public function updateLoadingDetails($loading_details)
	{
	      
	    $sql = "UPDATE 0_vehicle_loading_details SET remarks='$loading_details->remarks',delivery_status='$loading_details->delivery_status' WHERE id='$loading_details->id'";
		

		$result=mysql_query($sql);
		if(!$result) {
		    return 0;
		}

        return $loading_details->id;
	}
	
	public function getDeliveryDetails($sales_person_id)
	{
	   
	   $today=date("Y-m-d");
	   
	   $sql = "SELECT vld.* FROM 0_vehicle_loading_details vld,0_debtors_master dm,0_cust_branch cb WHERE vld.customer_code=dm.cust_code and dm.debtor_no=cb.debtor_no and cb.salesman='$sales_person_id' and loading_date='$today'";
		
	  // $sql = "SELECT vld.* FROM 0_vehicle_loading_details vld,0_debtors_master dm,0_cust_branch cb WHERE vld.customer_code=dm.cust_code and dm.debtor_no=cb.debtor_no and cb.salesman='$sales_person_id' ";
	  
	   $result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
        $sp['id']=$record['id'];  
		$sp['loading_date']=date("d/m/Y", strtotime($record['loading_date']));
	    $sp['vehicle_no']=$record['vehicle_no'];
	    $sp['delivery_route_plan']=$record['delivery_route_plan'];
	    $sp['do_cum_invoice_no']=$record['do_cum_invoice_no'];
	    $sp['do_cum_invoice_date']=date("d/m/Y", strtotime($record['do_cum_invoice_date']));
	    $sp['customer_code']=$record['customer_code'];
	    $sp['customer_name']=mb_convert_encoding($record['customer_name'], "UTF-8", "HTML-ENTITIES");
      	$sp['driver_name']=$record['driver_name'];
		$sp['driver_number']=$record['driver_number'];
		$sp['delivery_status']=$record['delivery_status'];
		$sp['remarks']=$record['remarks'];
	    $rows[]=$sp;
      }  
	   
	  return $rows;
	}
	
	 public function add_delivery_image($id,$fileName,$fileSize, $fileType)
	{
	    $sql = "INSERT INTO 0_delivey_image (vehicle_loading_id, filename, filesize, filetype)
		VALUES ('$id','$fileName','$fileSize','$fileType')";
		 $result=mysql_query($sql);
		 if(!$result) {
		     return 0;
		 }
        return  mysql_insert_id();
	}
	
	//ravi
	public function getAllTodayDeliveryDetails()
	{
	   
	   $today=date("Y-m-d");
	   
	   $sql = "SELECT vld.* FROM 0_vehicle_loading_details vld,0_debtors_master dm,0_cust_branch cb WHERE vld.customer_code=dm.cust_code and dm.debtor_no=cb.debtor_no and loading_date='$today'";
		
	  // $sql = "SELECT vld.* FROM 0_vehicle_loading_details vld,0_debtors_master dm,0_cust_branch cb WHERE vld.customer_code=dm.cust_code and dm.debtor_no=cb.debtor_no and cb.salesman='$sales_person_id' ";
	  
	   $result=mysql_query($sql);
        

        $rows=array();
  
       while($record=mysql_fetch_assoc($result))
      {
        $sp['id']=$record['id'];  
		$sp['loading_date']=date("d/m/Y", strtotime($record['loading_date']));
	    $sp['vehicle_no']=$record['vehicle_no'];
	    $sp['delivery_route_plan']=$record['delivery_route_plan'];
	    $sp['do_cum_invoice_no']=$record['do_cum_invoice_no'];
	    $sp['do_cum_invoice_date']=date("d/m/Y", strtotime($record['do_cum_invoice_date']));
	    $sp['customer_code']=$record['customer_code'];
	    $sp['customer_name']=mb_convert_encoding($record['customer_name'], "UTF-8", "HTML-ENTITIES");
      	$sp['driver_name']=$record['driver_name'];
		$sp['driver_number']=$record['driver_number'];
		$sp['delivery_status']=$record['delivery_status'];
		$sp['remarks']=$record['remarks'];
	    $rows[]=$sp;
      }  
	   
	  return $rows;
	}
	
	
	//ravi
	public function getAllTodayDeliveriesCount()
	{
	   
	   $today=date("Y-m-d");
	   
	   $sql = "SELECT count(*) total_deliveries FROM 0_vehicle_loading_details  WHERE loading_date='$today'";
		
	   $result=mysql_query($sql);
	   $sp['total_deliveries']="0";
	   
        if($row=mysql_fetch_assoc($result))
        {
            $sp['total_deliveries']=is_null($row['total_deliveries'])?"0":number_format($row['total_deliveries'],3);
          
        }
        
        return $sp;
        
	}
	
}
?>