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

class Cus_Pricing_Model extends CI_Model {
  
  private $tableName = 'sales';
  
  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct();
  }
  
 	//get Sold Qty By WarehouseId
	public function getSoldQtyByWarehouseId($warehouse_id,$product_id)
	{
		$this->db->select_sum('si.quantity');
		$this->db->from('sale_items si');
		$this->db->join('sales s', 's.sale_id = si.sale_id', 'left');
		$this->db->where('s.warehouse_id',$warehouse_id);
		$this->db->where('si.product_id',$product_id);
		$query=$this->db->get();
		return $data['quantity']=$query->row()->quantity;
	}
function get_all_sales_return_for_report($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$sale_id='',$from='',$to='') {
		$this->db->select('sr.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('sales_return sr');
		$this->db->join('customer c', 'sr.customer_id = c.cus_id', 'left');
		$this->db->join('sale_payments p', 'sr.sl_rtn_id = p.sale_id', 'left');
		$this->db->where("p.sale_payment_type",'sales_return');
		//$this->db->join('sales_return sr', 'sr.sale_id = p.sale_id', 'left');
		$this->db->order_by("sr.sl_rtn_id", "desc");
		$this->db->group_by('sr.sl_rtn_id');
		if($srh_warehouse_id){
			$this->db->where("sr.warehouse_id",$srh_warehouse_id);//("id !=",$id);
		}
		if($srh_to_date){
			$this->db->where("sr.sl_rtn_datetime <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("sr.sl_rtn_datetime >=",$srh_from_date);//("id !=",$id);
		}
		//if($sl_rtn_id){
			//$this->db->where("sr.sl_rtn_id =",$sl_rtn_id);//("id !=",$id);
	//	}
		if($to){
		$this->db->limit($to,$from);
		}
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}	
	
	
		     //Sales get information
	public function get_sale_info_by_customer_id($id)
	 {
		$this->db->select('*');
		$this->db->from('sales');
		$this->db->where("customer_id", $id);
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		return $query->result(); 
	 }
	 
	 
 function getPaymentsForPrint($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$srh_type='',$srh_payment_term='')
   {
	   $this->db->select('p.*,c.cus_name,b.*,u.user_first_name');
       $this->db->from('sale_payments p');
	   $this->db->join('sales b', 'b.sale_id = p.sale_id', 'left');
	   $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
	   $this->db->join('customer c', 'c.cus_id = b.customer_id', 'left');
	    $this->db->join('user u', 'u.user_id = p.user_id', 'left');
	
		
	   if($srh_type){
	   
	   $this->db->where("p.sale_payment_type",$srh_type);//
	   }
	   if($srh_payment_term){
	   
	   $this->db->where("p.sale_pymnt_paying_by",$srh_payment_term);//
	   }
	   if($srh_warehouse_id){
	   
	   $this->db->where("b.warehouse_id",$srh_warehouse_id);//
	   }
	   if($srh_to_date){
			$this->db->where("p.sale_pymnt_date_time <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("p.sale_pymnt_date_time >=",$srh_from_date);//("id !=",$id);
		}
	   $this->db->order_by("b.sale_id", "desc");
	  
	   $query = $this->db->get();
	   // echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       //return false;
     }

   }	
	  
   //Sales best for dashboard
   function getBestSales($year=null,$month=0,$from=0,$to=0){
	$this->db->select('SUM(ft.fi_qty)AS fi_qty_tot,p.product_name,p.product_code,p.product_part_no,p.product_oem_part_number');
	$this->db->from('fi_table ft');
	$this->db->join('product p', 'ft.fi_item_id = p.product_id', 'left');
	$this->db->where('ft.fi_type_id', 'sale');
	if($month){
		$this->db->where('MONTH(ft.fi_date_time)', $month , FALSE);
	}
	if($year){
		$this->db->where('YEAR(ft.fi_date_time)', $year , FALSE);
	}
	if($to){
		$this->db->limit($to,$from);
	}
	$this->db->order_by("fi_qty_tot", "desc");
	$this->db->group_by('ft.fi_item_id');
	$query=$this->db->get();
	return $query->result();
  }  
  
  //Sales genarate referance number
  function get_next_ref_no_cus(){
	  $this->db->select_max('id');
	  return $this->db->get('cus_pricing');
  }
 
  //Sales get avalable product qty
  function get_avalable_product_qty($product_id,$warehouse_id){
		$this->db->select_sum('fi_qty');
		$query = $this->db->get('fi_table');
		return $query->row()->fi_qty;
  }
  
  //Sales get toatal paid 
  function get_total_paid_by_sale_id($sale_id){
	$this->db->select_sum('sale_pymnt_amount');
	$this->db->from('sale_payments');
	$this->db->where("sale_id",$sale_id)->where("(sale_payment_type='sale' OR sale_payment_type='pos_sale')");
	$query=$this->db->get();
	//echo $this->db->last_query();
	if($query->row()->sale_pymnt_amount){
		return $query->row()->sale_pymnt_amount;
	}else {
		return 0;
	}
  }
  
    //Sales get information
	public function get_sale_info($id)
	 {
		$this->db->select('*');
		$this->db->from('sales');
		$this->db->where("sale_id", $id);
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		return $query->row_array(); 
	 }
	 
	//Sales item list get by id 
	public function get_price_list_by_cus_id($sale_id)
	 {
		$this->db->select('cus_pricing.*, product.product_id,product.product_name,product.product_code,product.product_oem_part_number,product.product_part_no,mstr_city.*');
		$this->db->from('cus_pricing');
		$this->db->join('product', 'cus_pricing.product_id = product.product_id', 'left');
		$this->db->join('mstr_city', 'mstr_city.cid = cus_pricing.city_id', 'left');
		//$this->db->order_by("sale_items.id", "desc");
		$this->db->where('cid', $sale_id);//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
		
	 }

	function update_price($cid,$pid,$price){

$price = "'".$price."'";

$query = $this->db->query('UPDATE `cus_pricing` SET `wholesale_price` = '.$price.' WHERE `cus_pricing`.`city_id` = '.$cid.' AND `cus_pricing`.`product_id` = '.$pid.' ');

//$this->db->where('city_id',$cid);
//$this->db->where('product_id',$pid);
//$this->db->update('cus_pricing',$price);
		
//echo $this->db->last_query();

$query = $this->db->query('select `wholesale_price` from `cus_pricing` WHERE `city_id` = '.$cid.' AND `product_id` = '.$pid.' ');

		
		return	$query->result_array();
		}

	//Sales save
	function save_sales(&$supplier_data,$sale_id=false)
	{
		if (!$sale_id)
		{
			$this->db->insert($this->tableName,$supplier_data);
		}else {
			$this->db->where('sale_id', $sale_id);
			return $this->db->update($this->tableName,$supplier_data);
		}
	}	
	
	//Sales item save
	function save_prices(&$data_item)
	{
			$this->db->insert('cus_pricing',$data_item);
	}	

	//Sales get for report
	function get_all_sales_for_report($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$sale_id='',$from='',$to='',$srh_customer_id='') {
		//echo "<br/>Test".$srh_customer_id;
		$this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('sales s');
		$this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
		$this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
		$this->db->order_by("s.sale_id", "desc");
		$this->db->group_by('s.sale_id');
		//$this->db->where("p.sale_payment_type",'sale');
		if($srh_warehouse_id){
			$this->db->where("s.warehouse_id",$srh_warehouse_id);//("id !=",$id);
		}
		if($srh_to_date){
			$this->db->where("s.sale_datetime <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("s.sale_datetime >=",$srh_from_date);//("id !=",$id);
		}
		if($sale_id){
			$this->db->where("s.sale_id =",$sale_id);//("id !=",$id);
		}
		if($srh_customer_id){
			$this->db->where("s.customer_id",$srh_customer_id);//("id !=",$id);
	}
		if($to){
		$this->db->limit($to,$from);
		}
		$query = $this->db->get();
		
		//echo $this->db->last_query();
		return $query->result_array();
	}	
	
	//Sales all get
	function get_all_city($start='',$length='',$search_key_val='') {
		$this->db->select('sales.*, customer.cus_name');
		$this->db->from('customer');
		$this->db->join('sales', 'sales.customer_id = customer.cus_id', 'left');
		$this->db->order_by("sales.sale_id", "desc");
		$this->db->where("sales.sale_id IS NOT NULL");//("id !=",$id);
		if($search_key_val){
			$this->db->where("sales.sale_reference_no LIKE '%$search_key_val%' OR customer.cus_name LIKE '%$search_key_val%'");
			//$this->db->like('sales.sale_reference_no', $search_key_val);
			//$this->db->like('customer.cus_name', $search_key_val);
		}
		//$this->db->where("product_name LIKE '%$term%' OR product_code LIKE '%$term%' OR product_oem_part_number LIKE '%$term%' OR product_part_no LIKE '%$term%'");
		if($start!='' && $length!=''){
			$this->db->limit($length,$start);
		}
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}
	
	//Sales get for print
	function get_all_sales_for_print_sales() {
		$this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('sales s');
		$this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
		$this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
		$this->db->order_by("s.sale_id", "desc");
		$this->db->group_by('s.sale_id');
		$this->db->where("s.sale_id IS NOT NULL");//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	//Sales payment get 
	function get_sale_payments_by_sale_id($sale_id) {
		$this->db->select('sale_payments.*,user.user_first_name,user_group.user_group_name');
		$this->db->from('sale_payments');
		$this->db->join('user', 'sale_payments.user_id = user.user_id', 'left');
		$this->db->join('user_group', 'user.group_id = user_group.user_group_id', 'left');
		$this->db->order_by("sale_payments.sale_pymnt_id", "desc");
		$this->db->where("sale_payments.sale_id",$sale_id);//("id !=",$id);
		$this->db->where("sale_payments.sale_payment_type",'sale');
		$query = $this->db->get();
		return $query->result();
	}
	
	//Get product sujetions
	function get_products_suggestions($term,$cus_id){
		$this->db->select('product'.'.*');
		$this->db->join('cus_pricing','product.product_id = cus_pricing.product_id ','LEFT');
		$this->db->order_by("product_name", "asc");
		$this->db->where("(product.product_id NOT IN(select product_id from cus_pricing where city_id LIKE '%$cus_id%'))AND (product_name LIKE '%$term%' OR product_code LIKE '%$term%' OR product_oem_part_number LIKE '%$term%' OR product_part_no LIKE '%$term%')");
		 $this->db->limit(10, 0);
		$query = $this->db->get('product');
		//echo $this->db->last_query();
		return $query->result_array();
		
	}
	
		function get_products_suggestions_sale($term,$cus_id){
		$this->db->distinct();
		$this->db->select('p.product_id,p.product_name,p.product_code,p.product_part_no,p.product_oem_part_number,cus_pricing.wholesale_price as product_price , p.product_cost ');
		$this->db->join('cus_pricing','p.product_id = cus_pricing.product_id ','LEFT');
		$this->db->order_by("p.product_name","asc");
		$this->db->where("cus_pricing.city_id = ".$cus_id." AND p.product_name LIKE '%$term%'");
		$this->db->or_where("cus_pricing.city_id = ".$cus_id." AND p.product_code LIKE '%$term%'");
		// OR p.product_oem_part_number LIKE '%$term%' OR p.product_part_no LIKE '%$term%' 
		
		 $this->db->limit(10, 0);
		$query = $this->db->get('product p');
		//echo $this->db->last_query();
		return $query->result_array();
		
	}
	/*	function get_products_sugge($term,$cus_id){
		$this->db->select('product'.'.*');
		$this->db->join('cus_pricing','product.product_id =cus_pricing.product_id ','LEFT');
		$this->db->order_by("product_name", "asc");
		//$this->db->where("product_name LIKE '%$term%'");
		$this->db->where("(product.product_id NOT IN(select product_id from cus_pricing where cus_id LIKE '%$cus_id%'))AND (product_name LIKE '%$term%' OR product_code LIKE '%$term%' OR product_oem_part_number LIKE '%$term%' OR product_part_no LIKE '%$term%')");
		 $this->db->limit(10, 0);
		$query = $this->db->get('product');
		//echo $this->db->last_query();
		return $query->result_array();
		
	}
	*/
	//Get all products
	function get_all_products() {
		$this->db->select('product'.'.*');
		$this->db->order_by("product_name", "asc");
		$this->db->where("product_id IS NOT NULL");//("id !=",$id);
		$query = $this->db->get('product');
		return $query->result_array();
	}
	//LAKMAL
	
	//Sales payment save
	function save_sale_payments(&$data,$sale_pymnt_id=false)
	{
		if (!$sale_pymnt_id)
		{
			return $this->db->insert('sale_payments',$data);
		}else {
			$this->db->where('supp_id', $sale_pymnt_id);
			return $this->db->update('sale_payments',$data);
		}
	}	
		function save_group(&$customer_data,$cus_id=false)
	{
		if (!$cus_id)
		{
			$this->db->insert('cus_pricing',$customer_data);
		}else {
			$this->db->where('cus_id', $cus_id);
			return $this->db->update($this->tableName,$customer_data);
		}
	}
	
	public function get_all_added_areas()
	{
		//$query = $this->db->query('SELECT * FROM mstr_city');
		$this->db->distinct();
		$this->db->select('mstr_city.*');
		$this->db->from('mstr_city');
		$this->db->join('cus_pricing','cus_pricing.city_id = mstr_city.cid','inner');
		$this->db->where('cus_pricing.city_id = mstr_city.cid ');
        $query = $this->db->get();
		
//print_r($query->result_object());
		
		return $query->result_array();
	}
	public function delete_pricing($id)
	{
		$this->db->where('city_id',$id);
		$this->db->delete('cus_pricing');
	
	}
	public function disable_pricing($cid)
	{
		$this->db->set('status',0);  
        $this->db->where('cid', $cid);  
        $this->db->update('mstr_city');
	}
	public function enable_pricing($cid)
	{
		$this->db->set('status',1);  
        $this->db->where('cid', $cid);  
        $this->db->update('mstr_city');
	}	
}