<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Marketing_Delivery_Model extends CI_Model {
  
  private $tableNameMaster = 'sample_order_master';
  private $tableNameMaster_id = 'sample_master_id';
  private $tableNameItem = 'sample_order_items';
  private $tableNameItem_id = 'sample_order_item_id';
  
  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct();
  }
  
  function get_rep_first_and_second_name_by_id($sales_rep_id)
	{
	$this->db->select('user_first_name');	
	$this->db->select('user_last_name');
	$this->db->from("user");
	$this->db->where("user_id",$sales_rep_id);
	$query=$this->db->get();
	return $query->row_array();
	
	}
  
  public function get_all_sales_user_reps()
	{
		$this->db->select('user.user_id');
		$this->db->select('user.user_first_name');
		$this->db->order_by("user_id", "asc");
		$this->db->where("user_status",1);
		$this->db->where("group_id", 4);
		$query = $this->db->get('user');
		return $query->result_array();
	
	}
	
	
	
	public function get_all_routes() {
		$this->db->select('*');
		$this->db->order_by("route_name", "desc");
		$this->db->where("route_id IS NOT NULL");
		$this->db->where("route_status",1);
		$query = $this->db->get('routes');
		return $query->result_array();
  }
  
  public function get_next_ref_no()
	{
	$this->db->select_max('sales_rep_issue_id');
	return $this->db->get('sales_rep_issues');	
	}
	
	
	function get_sale_rep_invoice_item_qty($product_id,$sales_rep_id,$wharehouse_id,$sale_rep_issue_id)
	{		
		$this->db->select_sum('quantity');
		$this->db->from('sale_items');
		$this->db->where("sale_items.product_id",$product_id);
		$this->db->where("sale_items.sales_rep_issue_id ",$sale_rep_issue_id);
		$query =$this->db->get();	
		return $query->row_array();		
	}
	

	 function get_all_areas()
    {
        $this->db->select('*');
        $this->db->order_by("area_name", "asc");
        $this->db->where("status", 1); //("id !=",$id);
        $query = $this->db->get('master_area');
        return $query->result();
    }
	  public function get_all_vehicle()
    {
        $this->db->select('*');
        $this->db->order_by("vehicle_name", "desc");
        $this->db->where("vehicle_id IS NOT NULL");
        $this->db->where("vehicle_status", 1);
        $query = $this->db->get('vehicles');
        return $query->result_array();
    }
	 function save_data_loading_master($data)
	{
		$result =$this->db->insert('marketing_loading_unloading',$data);
		$id=$this->db->insert_id();			
		return $id;
	}	
	public function get_delivary_details($delivary_id)
	{
		$this->db->select('mlu.*');
		$this->db->select('vehicles.vehicle_code');	
		//$this->db->select('routes.route_name');
		$this->db->select('master_area.area_name');
		$this->db->select('user.user_first_name');		
		$this->db->from('marketing_loading_unloading mlu');	
		$this->db->join('vehicles','mlu.lorry_id=vehicles.vehicle_id','left');	
		//$this->db->join('routes','mlu.route_id=routes.route_id','left');
		$this->db->join('master_area','mlu.area_id=master_area.area_id','left');
		$this->db->join('user','mlu.rep_id=user.user_id','left');
		//$this->db->where("mlu.status","TO_BE_LOADED");
		$this->db->where("mlu.id",$delivary_id);
		//$this->db->order_by("mlu.loading_date_time", "asc");
		$query=$this->db->get();
		return $query->row_array();
	}
	
	public function get_sales_rep_id($loading_id) // access area id, ref id from loading id
	{
	$this->db->select('marketing_loading_unloading.rep_id,area_id');
	$this->db->from('marketing_loading_unloading');	
	$this->db->where("marketing_loading_unloading.id",$loading_id);	
	$query=$this->db->get();
	return $query->row_array();	
	}
	
	
	
  
   public function get_delivary_pending()
	{
		$this->db->select('mlu.*');
		$this->db->select('vehicles.vehicle_code');	
		$this->db->select('master_area.area_code,master_area.area_name');
		$this->db->select('user.user_first_name');
		$this->db->select('mas.ap_status');
		$this->db->select('mls.loading_name');
		$this->db->from('marketing_loading_unloading mlu');	
		$this->db->join('vehicles','mlu.lorry_id=vehicles.vehicle_id','left');	
		$this->db->join('master_area','mlu.area_id=master_area.area_id','left');
		$this->db->join('user','mlu.rep_id=user.user_id','left');
		$this->db->join('master_approval_status mas','mlu.approval_status=mas.app_value','left');	
		$this->db->join('master_loading_status mls','mlu.loading_status=mls.loading_value','left');
		$this->db->where("mlu.loading_status",0);
		$this->db->where("mlu.approval_status",0);
		$this->db->order_by("mlu.loading_date", "asc");
		$query=$this->db->get();
		return $query->result_array();
	}
	
	 public function get_delivary_approved()
	{
		$this->db->select('mlu.*');
		$this->db->select('vehicles.vehicle_code');	
		$this->db->select('master_area.area_code,master_area.area_name');
		$this->db->select('user.user_first_name');
		$this->db->select('mas.ap_status');
		$this->db->select('mls.loading_name');
		$this->db->from('marketing_loading_unloading mlu');	
		$this->db->join('vehicles','mlu.lorry_id=vehicles.vehicle_id','left');	
		$this->db->join('master_area','mlu.area_id=master_area.area_id','left');
		$this->db->join('user','mlu.rep_id=user.user_id','left');
		$this->db->join('master_approval_status mas','mlu.approval_status=mas.app_value','left');	
		$this->db->join('master_loading_status mls','mlu.loading_status=mls.loading_value','left');
		$this->db->where("mlu.loading_status",1);
		$this->db->where("mlu.approval_status",1);
		$this->db->order_by("mlu.loading_date", "asc");
		$query=$this->db->get();
		return $query->result_array();
	}
	
	
	public function get_delivary_ongoing()
	{
		$this->db->select('mlu.*');
		$this->db->select('vehicles.vehicle_code');	
		$this->db->select('master_area.area_code,master_area.area_name');
		$this->db->select('user.user_first_name');
		$this->db->select('mas.ap_status');
		$this->db->select('mls.loading_name');
		$this->db->from('marketing_loading_unloading mlu');	
		$this->db->join('vehicles','mlu.lorry_id=vehicles.vehicle_id','left');	
		$this->db->join('master_area','mlu.area_id=master_area.area_id','left');
		$this->db->join('user','mlu.rep_id=user.user_id','left');
		$this->db->join('master_approval_status mas','mlu.approval_status=mas.app_value','left');	
		$this->db->join('master_loading_status mls','mlu.loading_status=mls.loading_value','left');
		$this->db->where("mlu.loading_status",3);
		$this->db->where("mlu.approval_status",1);
		$this->db->order_by("mlu.loading_date", "asc");
		$query=$this->db->get();
		return $query->result_array();
	}
	
	
	
	public function get_delivary_sample_list_inner_product($delivery_id)
	{
	$this->db->select('srii.product_id');
	$this->db->select('p.product_name,p.product_code');
	$this->db->from('sales_rep_issue_items srii');
	$this->db->join('product p','srii.product_id=p.product_id','inner');
	$this->db->join('sales_rep_issues sri','srii.sales_rep_issue_id=sri.sales_rep_issue_id','left');
	$this->db->where("sri.delivery_status",1);
	$this->db->where("sri.delivery_id",$delivery_id);	
	$this->db->group_by('srii.product_id');
	$this->db->order_by('p.product_name','asc');	
	$query=$this->db->get();
	return $query->result_array();
	}
	
	public function get_sample_sum_of_product($product_id,$delivery_id)
	{
	$this->db->select('SUM(srii.salse_rep_issue_qty) as salse_rep_issue_qty ');
	$this->db->from('sales_rep_issue_items srii');
	$this->db->join('sales_rep_issues sri','srii.sales_rep_issue_id=sri.sales_rep_issue_id','left');
	$this->db->where("sri.delivery_status",1);
	$this->db->where("sri.delivery_id",$delivery_id);
	$this->db->where("srii.product_id",$product_id);	
	$query=$this->db->get();
	return $query->row_array();		 	
	}
	
	
	//-------------------------------------------------------------2019-08-20 new update
	public function get_sample_list_relevent_to_area($route_array='')
	{
		
	 $this->db->select('soi.sample_order_item_id,soi.product_id,soi.order_qty');
	 $this->db->select('som.sample_ref_no,som.sample_order_date');
	$this->db->select('p.product_name,p.product_code');
	$this->db->select('u.user_first_name');
	$this->db->select('r.route_code');
	$this->db->from('sample_order_items soi');
	$this->db->join('sample_order_master som',' soi.sample_order_master_id =som.sample_master_id','left');
	$this->db->join('product p','soi.product_id=p.product_id','left');
	$this->db->join('user u','som.order_rep_id=u.user_id','left');
	$this->db->join('routes r','som.route_id=r.route_id','left');
	$this->db->where("som.status",1);	
	$this->db->where("soi.delivery_status",0);
	$this->db->where_in("som.route_id",$route_array);
	//$this->db->where('soi.sample_order_item_id NOT IN (sample_order_item_id from markrting_lot_item where status=1)',NULL,FALSE);
	$query=$this->db->get();
	return $query->result_array();
	}
	
	public function update_sample_delivary($check,$delivary_id,$status)
	{
		$data=array('delivery_status'=>$status,'delivery_id'=>$delivary_id);
		$this->db->where_in($this->tableNameItem.".".$this->tableNameItem_id,$check);
		return $this->db->update($this->tableNameItem, $data);
	}
	
	public function get_pending_delivary_sample_items($delivery_id)
	{
	$this->db->select('soi.product_id,soi.product_cost,soi.after_discount_added_price,soi.order_qty,soi.issue_qty,soi.delivery_id,soi.sample_order_master_id,soi.sample_order_item_id');
	$this->db->select('p.product_name,p.product_code');
	$this->db->select('som.sample_ref_no,som.sample_order_date');
	$this->db->select('u.user_first_name');
	$this->db->from('sample_order_items soi');
	$this->db->join('product p','soi.product_id=p.product_id','left');
	$this->db->join('sample_order_master som','soi.sample_order_master_id=som.sample_master_id','left');
	$this->db->join('user u','som.order_rep_id=u.user_id','left');
	$this->db->where("soi.delivery_status",1);
	$this->db->where("soi.delivery_id",$delivery_id);	
	$query=$this->db->get();
	return $query->result_array();	
		
	}
	
	 public function get_delivary_approved_for_manual_sales()
	{
		$this->db->select('mlu.*');
		//$this->db->select('vehicles.vehicle_code');	
		//$this->db->select('master_area.area_code,master_area.area_name');
		//$this->db->select('user.user_first_name');
		//$this->db->select('mas.ap_status');
		$this->db->select('mls.loading_name');
		$this->db->from('marketing_loading_unloading mlu');	
		//$this->db->join('vehicles','mlu.lorry_id=vehicles.vehicle_id','left');	
		//$this->db->join('master_area','mlu.area_id=master_area.area_id','left');
		//$this->db->join('user','mlu.rep_id=user.user_id','left');
		//$this->db->join('master_approval_status mas','mlu.approval_status=mas.app_value','left');	
		$this->db->join('master_loading_status mls','mlu.loading_status=mls.loading_value','left');
		$this->db->where("mlu.loading_status",1);
		$this->db->where("mlu.approval_status",1);
		$this->db->order_by("mlu.loading_date", "asc");
		$query=$this->db->get();
		return $query->result_array();
	}
	
	
	function get_pending_lot_by_area_id($area_id)
	{
		$this->db->select('lm.*');
		$this->db->select('u.user_first_name');
		$this->db->from('lot_order_master lm');		
		$this->db->join('user u','lm.order_rep_id=u.user_id','left');
		$this->db->where("lm.route_id",$area_id);
		$this->db->where("lm.status",0);
		$this->db->order_by("lm.lot_ref_no", "asc");
		$query=$this->db->get();
		return $query->result_array();
	
		
	}
	
	function get_pending_delivary_lot_items($delivary_id)
	{
		$this->db->select('li.*');
		$this->db->select('p.product_name,p.product_code');
		$this->db->from('lot_order_items li');	
		$this->db->join('product p','li.product_id=p.product_id','left');
		//$this->db->join('user u','li.order_rep_id=u.user_id','left');
		$this->db->where("li.delivery_id",$delivary_id);
		$this->db->where("li.delivery_status",1);
		$this->db->where("li.item_status",1);
		//$this->db->order_by("li.id", "asc");
		$this->db->group_by("li.product_id");
		$query=$this->db->get();
		return $query->result_array();	
		
	}
	
	function get_delivery_product_item($delivery_id,$product_id)
	{
		$this->db->select_sum('li.issue_qty');		
		$this->db->from('lot_order_items li');	
		$this->db->where("li.delivery_id",$delivery_id);
		$this->db->where("li.product_id",$product_id);
		$this->db->where("li.delivery_status",1);
		$this->db->where("li.item_status",1);
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['issue_qty']))
		{
			return $qty['issue_qty'];
		}
		else
		{
		return 0;	
		}		
	}
	
	function get_all_delivery_product_item($warehouse_id,$product_id)
	{
		$this->db->select_sum('li.issue_qty');		
		$this->db->from('lot_order_items li');	
		//$this->db->where("li.delivery_id",$delivery_id);
		$this->db->where("li.product_id",$product_id);
		$this->db->where("li.delivery_status",1);
		$this->db->where("li.item_status",1);
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['issue_qty']))
		{
			return $qty['issue_qty'];
		}
		else
		{
		return 0;	
		}		
	}
	
	function get_invoice_product_item($delivery_id,$product_id)
	{
		$this->db->select_sum('si.quantity');		
		$this->db->from('sale_items si');	
		$this->db->where("si.delivery_id",$delivery_id);
		$this->db->where("si.product_id",$product_id);
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['quantity']))
		{
			return $qty['quantity'];
		}
		else
		{
		return 0;	
		}		
	}
	
	function get_return_product_item($delivery_id,$product_id)
	{
		$this->db->select_sum('li.return_qty');		
		$this->db->from('lot_return_items li');	
		$this->db->where("li.delivery_id",$delivery_id);
		$this->db->where("li.product_id",$product_id);
		$this->db->where("li.delivery_status",1);
		$this->db->where("li.item_status",1);
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['return_qty']))
		{
			return $qty['return_qty'];
		}
		else
		{
		return 0;	
		}		
	}
	
	function get_all_return_product_item($warehouse_id,$product_id)
	{
		$this->db->select_sum('li.return_qty');		
		$this->db->from('lot_return_items li');	
		//$this->db->where("li.delivery_id",$delivery_id);
		$this->db->where("li.product_id",$product_id);
		$this->db->where("li.delivery_status",1);
		$this->db->where("li.item_status",1);
		$query=$this->db->get();
		$qty=$query->row_array();
		
		if(isset($qty['return_qty']))
		{
			return $qty['return_qty'];
		}
		else
		{
		return 0;	
		}		
	}
	
	
	function save_data_return($data)
	{
		$result =$this->db->insert('lot_return_items',$data);
		$id=$this->db->insert_id();			
		return $id;
		
	}
	
	
	public function update_lot_delivary_items($check,$delivary_id,$status)
	{
		$data=array('delivery_status'=>$status,'delivery_id'=>$delivary_id);
		$this->db->where_in("lot_order_items.lot_order_master_id",$check);
		return $this->db->update("lot_order_items", $data);
	}
	
	public function update_lot_delivary_master($check,$delivary_id,$status)
	{
		$data=array('status'=>$status,'delivery_id'=>$delivary_id);
		$this->db->where_in("lot_order_master.lot_master_id",$check);
		return $this->db->update("lot_order_master", $data);
	}
	
	public function unloading_delivery($delivary_id,$status)
	{
		if($delivary_id>0){
			$data=array('delivery_status'=>$status);
		$this->db->where('marketing_loading_unloading.delivery_id',$delivary_id);
		return $this->db->update('marketing_loading_unloading', $data);			
		}else{return false;}
		
	}
	


}