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

class Pos_Model extends CI_Model {

  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct();
  }

  public function get_product_by_cat_id($category_id='')
  {
     $this->db->select('product_id,product_name,product_code,product_thumb,cat_id,sub_cat_id');
     $this->db->from('product');
     $this->db->where('cat_id',$category_id);
     $this->db->where('product_status',1);
     $query = $this->db->get();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }

  }
function get_customers($id = '')
    {
        $this->db->select('customer.*');
        $this->db->order_by("cus_name", "asc");
        $this->db->where("cus_status", 1);
        if ($id)
            $this->db->where("cus_id", $id);
        else
            $this->db->where("cus_id !=", 1);
        $query = $this->db->get('customer');
        return $query->result_array();
    }
  public function get_all_category()
  {
     $this->db->select('*');
     $this->db->from('product_category');
     $this->db->where('cat_status',1);
     $query = $this->db->get();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
  }

   function get_sub_category_by_cat_id($category_id='')
   {
     $this->db->select('*');
     $this->db->from('product_sub_category');
     $this->db->where('cat_id',$category_id);
     $this->db->where('sub_cat_status',1);
     $query = $this->db->get();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }

   }

   function get_product_by_cat_sub_id($category_id='',$sub_category_id ='')
   {
     $this->db->select('product_id,product_name,product_code,product_thumb,cat_id,sub_cat_id');
     $this->db->from('product');
     $this->db->where('cat_id',$category_id);
     $this->db->where('sub_cat_id',$sub_category_id);
     $this->db->where('product_status',1);
     $query = $this->db->get();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }

   function get_product_by_code($product_code,$customer_id,$warehouse_id)
   {
     $this->db->select('p.*');
     $this->db->from('product p');
     $this->db->like('p.product_name',$product_code); 
     $this->db->or_like('p.product_code',$product_code); 
     $this->db->or_like('p.product_part_no',$product_code); 
     $this->db->limit('10');
     $query = $this->db->get();
   
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }

	function get_customer()
	{
	  $this->db->select('*');
	  $this->db->from('customer');
	  $this->db->where('cus_status',1);
	     $query = $this->db->get();
	   
	     if($query->num_rows() >0)
	     {
	       return $query->result();
	     }
	     else
	     {
	       return false;
	     }
	}

	function get_warehouse()
	{
	  $this->db->select('*');
	  $this->db->from('warehouses');
	  $this->db->where('status',1);
	     $query = $this->db->get();
	   
	     if($query->num_rows() >0)
	     {
	       return $query->result();
	     }
	     else
	     {
	       return false;
	     }
	}

	public function save_sale_header($data)
	{
	    if($this->db->insert('hw_sales', $data)){
	      return $this->db->insert_id();
	    }else{
	      return false;
	    }
	}

  function sale_items_in($data)
  {
	  if($this->db->insert('hw_sale_items', $data)){
        return true;
      }else{
        return false;
      }
  }

  function sales_payment($data)
  {
      

      if($this->db->insert('sale_payments', $data)){
        return true;
      }else{
        return false;
      }
  }
	
	function complete_sale($sale_id)
    {
        return $this->db->query('UPDATE `sales` SET `sale_status`= 2 WHERE `sale_id` = ' . $sale_id);
    }
	function get_all_sales($start,$length,$search,$direct_sale="",$no_walk="") {
		
		$this->db->select('hw_sales.*, customer.cus_name,customer.cus_address,customer.cus_phone,customer.cus_id');
		$this->db->from('hw_sales');
		$this->db->join('customer', 'hw_sales.customer_id = customer.cus_id', 'inner');		
		if(isset($search['value']))
		if($search['value']){		
				$this->db->like('hw_sales.card_ref_number', $search['value']);
				$this->db->or_like('hw_sales.sale_reference_no', $search['value']);
				$this->db->or_like('customer.cus_name', $search['value']);
				$this->db->or_like('customer.cus_phone', $search['value']);
				$this->db->or_like('customer.cus_address', $search['value']);
			}
			
			//echo $direct_sale;
		//if($direct_sale == 1){
			//$this->db->where("hw_sales.direct_sale","1");
		//}else $this->db->where("hw_sales.direct_sale","0");
		
		if($no_walk){
		    $this->db->where("customer.cus_id != 1");
		}
		if($this->session->userdata('ss_group_id')==3){
		    $this->db->where('hw_sales.user_id', $this->session->userdata('ss_user_id'));
		}
		
		
		if($start!='' && $length!=''){
            $this->db->limit($length,$start);
			}
		$this->db->order_by("hw_sales.sale_datetime", "desc");		
		//echo $this->db->last_query();echo "|";
		if($start!='' && $length!=''){
			$query=$this->db->get();
			return $query->result_array();            
		}else{
			$query = $this->db->get();
			return $query->num_rows();
		}
	}
	function get_next_ref_no(){
	  $this->db->select_max('sale_id');
	  return $this->db->get('hw_sales');
    }
    function get_new_next_ref_no($price_type){
	  $this->db->select('count(sale_id) as count');
	  $this->db->where("price_type",$price_type);
	  return $this->db->get('hw_sales');
    }
	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='hw_sale' OR sale_payment_type='pos_sale')");
    	$query=$this->db->get();
    	if($query->row()->sale_pymnt_amount){
    		return $query->row()->sale_pymnt_amount;
    	}else {
    		return 0;
    	}
    }
    function get_total_return_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","hw_sales_return")
    	         ->where("sale_pymnt_paying_by != \"Return Payment\"");
    	         
    	$query=$this->db->get();
    	if($query->row()->sale_pymnt_amount){
    		return $query->row()->sale_pymnt_amount;
    	}else {
    		return 0;
    	}
    }
  
  function get_sale_item_list_by_sale_id($sale_id){
		$this->db->select('hw_sale_items.product_id, product.product_name, product.product_code, hw_sale_items.quantity, hw_sale_items.discount, hw_sale_items.discount_val, hw_sale_items.unit_price, hw_sale_items.gross_total,product.product_part_no,product.product_oem_part_number,hw_sale_items.is_free_issue');
		$this->db->from('hw_sale_items');
		$this->db->join('product', 'hw_sale_items.product_id = product.product_id', 'left');
		$this->db->order_by("hw_sale_items.id", "desc");
		$this->db->where("hw_sale_items.sale_id", $sale_id);//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
  }
  
  function get_hw_sale_qty($warehouse_id='',$product_id='',$srh_to_date='',$srh_from_date='')
	{	
		$this->db->select_sum('si.quantity');
		$this->db->from('hw_sale_items si');
		$this->db->join('hw_sales s', 's.sale_id = si.sale_id', 'left');
		
		if($warehouse_id)
		$this->db->where('s.warehouse_id',$warehouse_id);
		
		if($product_id)
		$this->db->where('si.product_id',$product_id);
		
		if($srh_to_date){
			//$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("date(s.sale_datetime) <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
		   	$this->db->where("date(s.sale_datetime) >=",$srh_from_date);//("id !=",$id);
		}
// 			$date=date_create("2020-05-29");
// 			$srh_from_date= date_format($date,"Y-m-d");
// 			$this->db->where("s.sale_datetime >=",$srh_from_date);//("id !=",$id);
		
		$query=$this->db->get();
		return $data['quantity']=$query->row()->quantity;
	
	}
  
	function get_sale_info($id)
	 {

		$this->db->select('hw_sales.*,user.user_first_name, user.user_last_name,user_group.user_group_name');
		$this->db->from('hw_sales');
		$this->db->where("sale_id", $id);
		$this->db->join('user','hw_sales.user_id = user.user_id');
		$this->db->join('user_group','user.group_id = user_group.user_group_id');
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		return $query->row_array(); 
	 }
 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);
		$this->db->where("sale_payments.sale_payment_type",'hw_sale');
		$query = $this->db->get();
		return $query->result();
	} 
	
	function get_sale_paynment_total_info($sale_id){
		$sale_payment_type="hw_sale";
		$this->db->select_sum('sale_pymnt_amount');
		$this->db->from('sale_payments');
		$this->db->where("sale_payment_type",$sale_payment_type );
		$this->db->where("sale_id",$sale_id );
		$query = $this->db->get();
		return $query->row_array();
	}
	
	function get_sale_info_by_customer_id($id,$srh_from_date="",$srh_to_date="")
	 {
		$this->db->select('*');
		$this->db->from('hw_sales');
		$this->db->where("customer_id", $id);
		if($srh_to_date){
			$this->db->where("date(hw_sales.sale_datetime) <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
		   	$this->db->where("date(hw_sales.sale_datetime) >=",$srh_from_date);//("id !=",$id);
		}
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		return $query->result(); 
	 }
	 
	 function get_return_sale_info_sale_id($id)
	 {
		$this->db->select('*');
		$this->db->from('hw_sales_return');
		$this->db->where("sale_id", $id);
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		return $query->result(); 
	 }
	 function clear_sale($sale_id, $warehouse_id)
    {
        if ($sale_id) {
            $sale_pyment_data = array(
                'sale_pymnt_amount' => 0,
                'sale_pymnt_paying_by' => '',
                'sale_pymnt_note' => 'Canceled by ' . $this->session->userdata('ss_user_first_name') . ' ( ' . $this->session->userdata('ss_user_id') . ' )',
                'sale_pymnt_cheque_no' => '',
                'sale_pymnt_crdt_card_no' => '',
                'sale_pymnt_crdt_card_holder_name' => '',
                'sale_pymnt_crdt_card_type' => '',
                'sale_pymnt_crdt_card_month' => '',
                'sale_pymnt_crdt_card_year' => ''
            );
            $this->db->where('sale_id', $sale_id);
            $this->db->where('sale_payment_type', 'hw_sale');
            if ($this->db->update('sale_payments', $sale_pyment_data)) {
                $item_data = array(
                    'quantity' => 0,
                    'discount' => 0,
                    'unit_price' => 0,
                    'item_cost' => 0,
                    'unit_price' => 0,
                    'discount_val' => 0,
                    'gross_total' => 0
                );
                $this->db->where('sale_id', $sale_id);
                if ($this->db->update('hw_sale_items', $item_data)) {
                            $sales_data = array(
                                'customer_id' => 0,
                                'sale_inv_discount' => 0,
                                'sale_status' => 0,
                                //'is_penalty_available' => 0,
                                //'document_charges' => 0,
                                //'interest_val' => 0,
                                //'interest_amount' => 0,
                                'payment_status' => 0,
                                'sale_shipping' => 0,
                                'sale_total' => 0,
                                'sale_paid' => 0,
                                'cost_total' => 0,
                                'sale_balance' => 0,
                                //'rental_amount' => 0,
                                'sale_inv_discount_amount' => 0,
                                'sale_internal_note' => 'Canceled by ' . $this->session->userdata('ss_user_first_name') . ' ( ' . $this->session->userdata('ss_user_id') . ' )'
                            );
                            $this->db->where('sale_id', $sale_id);
                            if ($this->db->update('hw_sales', $sales_data)) {
                                return 1;
                            } else
                                return 0;
                } else
                    return 0;
            } else
                return 0;
        } else
            return 0;
    }
    
	public function suggetions($term) // returns product_id repeatedly with all batches
	{
		$this->db->select('p.product_id,p.product_code,p.product_name,p.product_cost,p.retail_cash,p.retail_credit,p.retail_wholesale,p.warehouse_id,p.ao,bp.*')
		         ->from('batch_product bp')
		         ->join('product p','p.product_id=bp.product_id','inner')
		         ->where("p.product_status = 1 AND p.product_name LIKE '%$term%'")
		         ->or_where("p.product_status = 1 AND p.product_code LIKE '%$term%'");
		         $this->db->limit(10, 0);
		$query=$this->db->get();
		return $query->result_array();
	}
	function product_suggetions($term) // returns product_ids
	{
		$this->db->select('p.product_id,p.product_code,p.product_name,p.product_cost,p.retail_cash,p.retail_credit,p.retail_wholesale,p.warehouse_id,p.ao')
		         ->from('product p')
		         ->join('batch_product b','b.product_id=p.product_id','inner')
		         ->where("p.product_status = 1 AND b.batch_status = 1 AND  p.product_code LIKE '$term%'")
		         ->or_where("p.product_status = 1 AND b.batch_status = 1 AND p.product_name LIKE '%$term%'")
		         ->group_by('p.product_id');
		         $this->db->limit(10, 0);
		$query=$this->db->get();
		return $query->result_array();
	}
	function bcount($product_id)// return active batch count
	{
		$this->db->select('count(bp.batch_id) as bcount')
		         ->from('batch_product bp')
		         ->where("bp.product_id",$product_id)
		         ->where("bp.batch_status",1)
		         ->where("bp.is_deleted",0);
                $this->db->limit(10);
		$query=$this->db->get();
		return $query->row()->bcount;
	}
	function bdata($product_id,$pt)// return batch data
	{
		$this->db->select('batch_id,batch_ref_number,bp.'.$pt.' as product_price,bp.product_cost')
		         ->from('batch_product bp')
		         //->join('product p','p.product_id=bp.product_id','inner')
		         ->where("bp.batch_status = 1 AND bp.product_id = ".$product_id);
		         $this->db->order_by("bp.batch_id", "desc");
		         $this->db->limit(10);
		$query=$this->db->get();
		return $query->result_array();
	}
	
		function product_suggetions_waight_items($term) 
	{
		$this->db->select('p.product_id,p.product_code,p.product_name,p.product_cost,p.retail_cash,p.retail_credit,p.retail_wholesale,p.warehouse_id,p.ao')
		         ->from('product p')
		         ->join('batch_product b','b.product_id=p.product_id','inner')
		         ->where("p.product_status", 1)
		         ->where("b.batch_status", 1)
		         ->where("b.is_deleted", 0)
		         ->where("p.product_code", $term)
		         ->where("p.is_export_scale", 1)
		         ->group_by('p.product_id');
		         $this->db->limit(2);
		$query=$this->db->get();
		return $query->result_array();
	}
	function get_last_sale_id(){
	    $user_id=$this->session->userdata('ss_user_id');
	    $this->db->select('hw_sales.sale_id');
		$this->db->from('hw_sales');
		$this->db->where("user_id", $user_id);
		$this->db->limit(1);
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		$result= $query->row_array(); 
		if(isset($result['sale_id'])){
		    return $result['sale_id'];
		}else{
		    return 0;
		}
	}
	
	function update_hw_sale($data,$id)
	{
		if ($id>0)
		{
			$this->db->where('sale_id',$id);
		    return $this->db->update('hw_sales',$data);
		}
		else 
		{
	        return false;
		}
	}
	
}