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

class Product_Models extends CI_Model {

public function __construct()
   {
      parent::__construct();

   }
 

  function get_product_next_ref_no($cat_id){
	  $this->db->select_max('product_cat_ref_id');
	   $this->db->where('cat_id',$cat_id);
	   
	  return $this->db->get('product');
  }
 
 
 
	function disable($product_id){
		
		$this->db->query('UPDATE `product` SET `product_status`= 0 WHERE `product_id` = '.$product_id.'');
	
		}

	
	function enable($product_id){
		
		$this->db->query('UPDATE `product` SET `product_status`= 1 WHERE `product_id` = '.$product_id.'');
	
		}
		
		
   public function get_search_product($str) {
        $this->db->select('pc.*');
        $this->db->from('product_category pc');
       
        $this->db->order_by("pc.cat_name", "asc");
        $this->db->or_like('pc.cat_name',$str);
		$this->db->or_like('pc.cat_code',$str);
       // $this->db->or_like('user_last_name',$str);
       // $this->db->or_like('user_group_name',$str);
	    $this->db->LIMIT(100);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

  function getProductsStockMovReport($product_id='',$start='',$length='',$search_key_val='',$cat_id='')
   {
     $this->db->select('p.* , c.cat_name , c.cat_code , s.sub_cat_name, u.unit_name');
     $this->db->from('product p');
	   $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');   
	   $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left'); 
	   $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left'); 
	    if($search_key_val){
            $this->db->where("p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '%$search_key_val%' OR p.product_oem_part_number LIKE '%$search_key_val%'");
       	}
	   if($product_id){
	   $this->db->where('p.product_id', $product_id);
	   }
	     if($cat_id){
	   $this->db->where('p.cat_id', $cat_id);
	   }
	  // $this->db->order_by("p.product_name", "asc");
	   $this->db->order_by("p.cat_id", "asc");
	   if($start!='' && $length!=''){
            $this->db->limit($length,$start);
        }
		
		// $this->db->limit(30);
	   $query = $this->db->get();  
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }  
	    
 public function create_product_code($cat_id='')
  {
     $this->db->select('*');
     $this->db->from('purchase_items_serial');
    // $this->db->where('pis_number',$sn);
	  $this->db->where('pis_sold',0);
     $query = $this->db->get();
       return $query->result();
  }  

   function getUnit()
   {
     $this->db->select('*');
     $this->db->from('mstr_unit');
     $this->db->where('unit_status',1);
     $query = $this->db->get();
   
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }
function getProductsForQTYReport($wherehouse_id='',$cat_srh='')
   {
	$query=$this->db->query("SELECT p.*,pc.cat_name,psc.sub_cat_name, SUM(IF(ft.fi_type_id ='sale', ft.fi_qty, 0)) AS sold_qty, SUM(IF(ft.fi_type_id ='grn', ft.fi_qty, 0)) AS purchased_qty FROM product p LEFT JOIN fi_table ft ON ft.fi_item_id = p.product_id AND (ft.fi_type_id ='sale' OR ft.fi_type_id ='grn') LEFT JOIN product_category pc ON pc.cat_id = p.cat_id
	LEFT JOIN product_sub_category psc ON psc.sub_cat_id = p.sub_cat_id

	 GROUP BY p.product_id  ORDER BY p.added_time desc");
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }
   function getTax()
   {
     $this->db->select('*');
     $this->db->from('tax_rates');
     $query = $this->db->get();
   
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }

 function save_product($product_name,$product_code,$category,$subcategory,$unit,$product_cost,$product_price,$wholesale_price,$credit_salling_price,$tax,$alert_quty,$imgName,$imageThumb,$product_details,$product_part_no,$product_oem_part_number,$product_id,$store_position,$product_max_qty,$product_sn_exissts,$product_cat_ref_id,$dep_id,$is_saleable,$ptype_id='',$pro_model_id,$pro_color_id,$pro_capacity_id)

   {

    

    $data1 = array(

       'cat_id'             	=> $category,

       'sub_cat_id'         	=> $subcategory,

       'product_name'        	=> $product_name,

       'product_code'			=> $product_code,

       'product_image'        	=> $imgName,

       'product_thumb' 			=> $imageThumb,

       'product_alert_qty'  	=> $alert_quty,

       'product_unit'  			=> $unit,

       'product_cost'  			=> $product_cost,

       'product_price'			=> $product_price,

       'wholesale_price'  		=> $wholesale_price,

       'credit_salling_price'  	=> $credit_salling_price,

       'tax'  					=> $tax,

       'product_details' 		=> $product_details,

		'product_part_no' => $product_part_no,
		'product_oem_part_number' => $product_oem_part_number,
		'store_position'=>$store_position,
		'product_max_qty'=>$product_max_qty,
		'product_sn_exissts'=>$product_sn_exissts,
		'product_cat_ref_id'=>$product_cat_ref_id,
		'dep_id'=>$dep_id,
		'is_saleable' =>$is_saleable,
		 'ptype_id'             => $ptype_id,
		 'pro_model_id'       => $pro_model_id,
		'pro_color_id'       => $pro_color_id,
		'pro_capacity_id'       => $pro_capacity_id,

    );



    $data2 = array(

       'cat_id'         		=> $category,

       'sub_cat_id'         	=> $subcategory,

       'product_name'        	=> $product_name,

      'product_code'			=> $product_code,

       'product_alert_qty'  	=> $alert_quty,

       'product_unit'  			=> $unit,

       'product_cost'  			=> $product_cost,

       'product_price'			=> $product_price,

       'wholesale_price'  		=> $wholesale_price,

       'credit_salling_price'  	=> $credit_salling_price,

       'tax'  					=> $tax,

       'product_details' 		=> $product_details,
	   'product_part_no' => $product_part_no,
	   'product_oem_part_number' => $product_oem_part_number,
	   'store_position'=>$store_position,
	   'product_max_qty'=>$product_max_qty,
	   'product_sn_exissts'=>$product_sn_exissts,
	   'product_cat_ref_id'=>$product_cat_ref_id,
	   	'dep_id'=>$dep_id,
		'is_saleable' =>$is_saleable,
		 'ptype_id'             => $ptype_id,
		 'pro_model_id'       => $pro_model_id,
		'pro_color_id'       => $pro_color_id,
		'pro_capacity_id'       => $pro_capacity_id,

    );



      if (!empty($imgName) && !empty($imageThumb)) {

        $data = $data1;

      }else{

        $data = $data2;

      }



     if($this->db->insert('product', $data))

     {
       //$lst = $this->db->insert_id();
      // $dta = $this->update_product_code($lst);
      // return $dta;
     }

     else

     {

       return false;

     }
     
     //echo "sql".$this->db->last_query();

   }

   public function update_product_code($product_id='')
   {
      $data = array(
                     'product_code' => "PD".sprintf("%04d",$product_id)
                  );

      $this->db->where('product_id', $product_id);
      $this->db->update('product', $data); 
      return $product_id;
   }
   
   public function update_product_cost($product_id='',$product_cost='')
   {
      $data = array(
                     'product_cost' => $product_cost,
                  );

      $this->db->where('product_id', $product_id);
      $this->db->update('product', $data); 
      return $product_id;
   }

function getProducts($start='',$length='',$search_key_val='',$category='',$search_name="",$rtn_count='',$srh_sub_cat='',$subcategory='',$dep_id='',$update_status='',$last_update_user="")
   {
     $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name');
     $this->db->from('product p');
       $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');   
       $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left'); 
       $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
	   if($category){
	    	$this->db->where('c.cat_id', $category);
	   }
	   if($dep_id){
	    	$this->db->where('p.dep_id', $dep_id);
	   }
	   
	   if($srh_sub_cat){
	    	$this->db->where('p.sub_cat_id', $srh_sub_cat);
	   }
	   if($subcategory){
	    	$this->db->where('p.ptype_id', $subcategory);
	   }
	   
	  // echo "<br/> last_update_user:$last_update_user, update_status:$update_status";
	   
	   if($last_update_user!=''){
	    	$this->db->where('p.last_update_user', $last_update_user);
	   }
	   
	   if($update_status==''){
	    	//$this->db->where('p.last_update_user', 0);
	   }else if($update_status=='1'){
	       $this->db->where('p.last_update_user != ', 0);
	   }else if($update_status=='0'){
	       	$this->db->where('p.last_update_user', 0);
	   }
	   
	   
	    
	    if($search_name){
	    //	$this->db->where("p.product_name LIKE '%$search_name%' OR p.product_code LIKE '%$search_name%'");
	   }
	   
       if($search_key_val){
           // $this->db->where("p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '%$search_key_val%'");
       }
       
       
       //echo "search_name:$search_name , ";
       /* advance search start */
       $pizza  = $search_name;
     $pieces = explode(" ", $pizza);
    // print_r($pieces);
     for($i=0; $i<count($pieces); $i++){
        // echo ', key:'.$pieces[$i];
        $searh_key=$pieces[$i];
        $this->db->like('p.product_name',$searh_key); 
     }
       
       /* end advance search */
       
	   
       $this->db->order_by("p.added_time", "desc");
       if($start!='' && $length!=''){
           if($length>0) $this->db->limit($length,$start);
        }
       $query = $this->db->get(); 
      // echo "<br/> update_status:$update_status";
      // echo $this->db->last_query();
     if($query->num_rows() >0)
     {
         if($rtn_count==1){
             return $query->num_rows();
         }else {
             
       return $query->result();
         }
     }
     else
     {
       return false;
     }
   }
   
     function getProductsProduCodePrint($cat_id=0,$sub_cat_id=0)
   {
     $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name');
     $this->db->from('product p');
	   $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');   
	   $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left'); 
	   $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left'); 
	   if($cat_id){
		   $this->db->where("p.cat_id","$cat_id");
	   }
	    if($sub_cat_id){
		   $this->db->where("p.sub_cat_id","$sub_cat_id");
	   }
	   $this->db->order_by("p.product_id", "asc");
	   $query = $this->db->get();  
	  //echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }

    function getProductsForReport($wherehouse_id='',$cat_srh='',$start='',$length='',$search_key_val='',$count='',$subcategory='',$product_id='',$product_name='',$dep_id='',$srh_sub_cat='',$pro_model_id='',$pro_color_id='',$pro_capacity_id='')
   {
       
    //  echo "p name:$product_name";
	   $limit='';
	    $q_where='';
	  
	   if($start!='' && $length!=''){
		   if($length>0){
            	$limit.="LIMIT $start , $length";
		   }
      }
      
      //$q_where='p.product_status=1 AND ';
	  
	 // $q_where='WHERE ';
	  if($cat_srh){
		   
	  	$q_where.="p.cat_id='$cat_srh' ";
	  
	  }
	  
	   if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.product_status=1";
	  
	  if($subcategory){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.ptype_id='$subcategory' ";
	  }
	  
	   if($srh_sub_cat){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.sub_cat_id='$srh_sub_cat' ";
	  }
	  
	  if($product_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.product_id='$product_id'";
	  
	  }
	  
	  if($pro_model_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.pro_model_id='$pro_model_id'";
	  
	  }
	  if($pro_capacity_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.pro_capacity_id='$pro_capacity_id'";
	  
	  }
	  if($pro_color_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.pro_color_id='$pro_color_id'";
	  
	  }
	  
	  
	  //echo "dep_id:$dep_id";
	  if($dep_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.dep_id='$dep_id'";
	  
	  }
	  
	  if($product_name){
	       	if($q_where){
			//	$q_where.=" AND "; 
			}
		  // $q_where='WHERE ';
		 //  $q_where.="(p.product_name LIKE '%$product_name%' OR p.product_code LIKE '%$product_name%')";
		   
		   /* serch + */
		        $pizza  = $product_name;
     $pieces = explode("+", $pizza);
     //print_r($pieces);
     for($i=0; $i<count($pieces); $i++){
        // echo ', key:'.$pieces[$i];
        $searh_key=$pieces[$i];
        //$this->db->like('p.product_name',$searh_key); 
       // if($i!=0)
       if($q_where)
        {
            $q_where.=" AND "; 
        }
        $q_where.="p.product_name LIKE '%$searh_key%'";
     }
		   /* end search + */
	  }
	  
	  
	 if($q_where){
	    $q_where='WHERE '.$q_where;
	}
	
	//echo "q_where:$q_where";

	  
	   
	  if($search_key_val){
		 // if($cat_srh){
			// $q_where.=" AND "; 
		//  }
			if($q_where){
				$q_where.=" AND "; 
			}
		  
		  //$q_where='';
            $q_where.="(p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '%$search_key_val%' OR pc.cat_name LIKE '%$search_key_val%')";
      }
	  
	  
	  
	  
	//  echo "q_where:".$q_where;
	
	
	$q="SELECT p.*,pc.cat_name,psc.sub_cat_name FROM (`product` p) 
LEFT JOIN product_category pc ON p.cat_id=pc.cat_id 
LEFT JOIN product_sub_category psc ON p.sub_cat_id=psc.sub_cat_id  $q_where
GROUP By p.product_id  order by p.product_name   $limit";
	
	/*
	//order by CAST(p.product_id AS SIGNED) asc
	$q="SELECT p.*,pc.cat_name,psc.sub_cat_name FROM (`product` p) 
INNER JOIN product_category pc ON pc.cat_id = p.cat_id
INNER JOIN product_sub_category psc ON pc.cat_id = p.cat_id $q_where
 GROUP By p.product_id order by CAST(p.product_name AS SIGNED) DESC $limit";
 */
 
	$query=$this->db->query($q);
 
	  //	 $lq = $this->db->last_query();	 
	// echo $this->db->last_query();
     if($query->num_rows() >0)
     {
		 if($count){
			return  $query->num_rows();
			
		 }
       	else{
			return $query->result();
			 echo $this->db->last_query();
		}
     }
     else
     {
       return false;
     }
   } 
   
   
   function getProductsForReport_v3($wherehouse_id='',$cat_srh='',$start='',$length='',$search_key_val='',$count='',$subcategory='',$product_id='',$product_name='',$dep_id='',$srh_sub_cat='')
   {
       
    //  echo "p name:$product_name";
	   $limit='';
	    $q_where='';
	  
	   if($start!='' && $length!=''){
		   if($length>0){
            	$limit.="LIMIT $start , $length";
		   }
      }
	  
	 // $q_where='WHERE ';
	  if($cat_srh){
		   
	  	$q_where.="p.cat_id='$cat_srh' ";
	  
	  }
	  
	   if($wherehouse_id){
		    if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="pis.avalable_warehouse_id='$wherehouse_id' ";
	  
	  }else{
	      if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="pis.avalable_warehouse_id !='0' ";
	  }
	  
	  
	  if($subcategory){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.ptype_id='$subcategory' ";
	  }
	  
	   if($srh_sub_cat){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.sub_cat_id='$srh_sub_cat' ";
	  }
	  
	  if($product_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.product_id='$product_id'";
	  
	  }
	  
	  
	  //echo "dep_id:$dep_id";
	  if($dep_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.dep_id='$dep_id'";
	  
	  }
	  
	  if($product_name){
	       	if($q_where){
			//	$q_where.=" AND "; 
			}
		  // $q_where='WHERE ';
		 //  $q_where.="(p.product_name LIKE '%$product_name%' OR p.product_code LIKE '%$product_name%')";
		   
		   /* serch + */
		        $pizza  = $product_name;
     $pieces = explode("+", $pizza);
     //print_r($pieces);
     for($i=0; $i<count($pieces); $i++){
        // echo ', key:'.$pieces[$i];
        $searh_key=$pieces[$i];
        //$this->db->like('p.product_name',$searh_key); 
       // if($i!=0)
       if($q_where)
        {
            $q_where.=" AND "; 
        }
        $q_where.="p.product_name LIKE '%$searh_key%'";
     }
		   /* end search + */
	  }
	  
	  
	 if($q_where){
	    $q_where='WHERE pis.product_avalable=1 AND '.$q_where;
	}
	
	//echo "q_where:$q_where";

	  
	   
	  if($search_key_val){
		 // if($cat_srh){
			// $q_where.=" AND "; 
		//  }
			if($q_where){
				$q_where.=" AND "; 
			}
		  
		  //$q_where='';
            $q_where.="(p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '%$search_key_val%' OR pc.cat_name LIKE '%$search_key_val%')";
      }
	  
	  
	  
	  
	//  echo "q_where:".$q_where;
	
	/*
	$q="SELECT SUM(pis.pis_qty) as product_balance , SUM(pis.pis_cost_price) as product_tot_cost, p.*,pc.cat_name,psc.sub_cat_name 
	FROM (`purchase_items_serial` pis) 
	INNER JOIN product p ON pis.product_id=p.product_id 
INNER JOIN product_category pc ON p.cat_id=pc.cat_id 
INNER JOIN product_sub_category psc ON p.sub_cat_id=psc.sub_cat_id  $q_where
GROUP By p.product_id  order by p.product_name   $limit";

*/


	$q="SELECT SUM(pis.pis_qty) as product_balance , SUM(pis.pis_cost_price) as product_tot_cost, p.*,pc.cat_name
	FROM (`purchase_items_serial` pis) 
	INNER JOIN product p ON pis.product_id=p.product_id 
INNER JOIN product_category pc ON p.cat_id=pc.cat_id 
 $q_where
GROUP By p.product_id  order by p.product_name   $limit";

	
	/*
	//order by CAST(p.product_id AS SIGNED) asc
	$q="SELECT p.*,pc.cat_name,psc.sub_cat_name FROM (`product` p) 
INNER JOIN product_category pc ON pc.cat_id = p.cat_id
INNER JOIN product_sub_category psc ON pc.cat_id = p.cat_id $q_where
 GROUP By p.product_id order by CAST(p.product_name AS SIGNED) DESC $limit";
 */
 
	$query=$this->db->query($q);
 
	  //	 $lq = $this->db->last_query();	 
	 //echo $this->db->last_query();
     if($query->num_rows() >0)
     {
		 if($count){
			return  $query->num_rows();
			
		 }
       	else{
			return $query->result();
			 echo $this->db->last_query();
		}
     }
     else
     {
       return false;
     }
   } 
   
   
   function get_fast_slow_movement_report($wherehouse_id='',$cat_srh='',$start='',$length='',$search_key_val='',$count='',$subcategory='',$product_id='',$product_name='',$dep_id='',$srh_sub_cat='',$supplier_id='',$ord_col='',$ord_dir='',$seach_key_9="")
   {
      // if($ord_col)==3 $ord_col="";
       
     //  echo "p name:$product_name";
	   $limit='';
	    $q_where='';
	  
	   if($start!='' && $length!=''){
		   if($length>0){
            	$limit.="LIMIT $start , $length";
		   }
      }
	  
	 // $q_where='WHERE ';
	  if($cat_srh){
		   
	  	$q_where.="p.cat_id='$cat_srh' ";
	  
	  }
	  
	  
	  if($seach_key_9){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.sub_cat_id='$seach_key_9' ";
	  }
	  
	  
	  if($subcategory){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.ptype_id='$subcategory' ";
	  }
	  
	   if($srh_sub_cat){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.sub_cat_id='$srh_sub_cat' ";
	  }
	  
	  if($product_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.product_id='$product_id'";
	  
	  }
	  
	  
	  //echo "dep_id:$dep_id";
	  if($dep_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.dep_id='$dep_id'";
	  
	  }
	  
	  if($product_name){
	       	if($q_where){
				$q_where.=" AND "; 
			}
		  // $q_where='WHERE ';
		   $q_where.="(p.product_name LIKE '%$product_name%' OR p.product_code LIKE '%$product_name%')";
	  }
	  
	  
	 if($q_where){
	    $q_where='WHERE '.$q_where;
	}

	  
	   
	  if($search_key_val){
		 // if($cat_srh){
			// $q_where.=" AND "; 
		//  }
			if($q_where){
				$q_where.=" AND "; 
			}
		  
		  //$q_where='';
            $q_where.="(p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '%$search_key_val%' OR pc.cat_name LIKE '%$search_key_val%')";
      }
	  
	  
	  
	  
	//  echo "q_where:".$q_where;
	
	
	$q="SELECT p.*,pc.cat_name,psc.sub_cat_name FROM (`product` p) 
INNER JOIN product_category pc ON p.cat_id=pc.cat_id 
INNER JOIN product_sub_category psc ON p.sub_cat_id=psc.sub_cat_id  $q_where
GROUP By p.product_id  order by p.product_name   $limit";
	
	/*
	//order by CAST(p.product_id AS SIGNED) asc
	$q="SELECT p.*,pc.cat_name,psc.sub_cat_name FROM (`product` p) 
INNER JOIN product_category pc ON pc.cat_id = p.cat_id
INNER JOIN product_sub_category psc ON pc.cat_id = p.cat_id $q_where
 GROUP By p.product_id order by CAST(p.product_name AS SIGNED) DESC $limit";
 */
 
	$query=$this->db->query($q);
 
	  //	 $lq = $this->db->last_query();	 
	 //echo $this->db->last_query();
     if($query->num_rows() >0)
     {
		 if($count){
			return  $query->num_rows();
			
		 }
       	else{
			return $query->result();
			 echo $this->db->last_query();
		}
     }
     else
     {
       return false;
     }
   } 
   
   
   function getProductsForReport_supp($wherehouse_id='',$cat_srh='',$start='',$length='',$search_key_val='',$count='',$subcategory='',$product_id='',$product_name='',$dep_id='',$srh_sub_cat='',$supplier_id='')
   {
       
     //  echo "p name:$product_name";
	   $limit='';
	    $q_where='';
	  
	   if($start!='' && $length!=''){
		   if($length>0){
            	$limit.="LIMIT $start , $length";
		   }
      }
	  
	 // $q_where='WHERE ';
	  if($cat_srh){
		   
	  	$q_where.="p.cat_id='$cat_srh' ";
	  
	  }
	  if($subcategory){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.ptype_id='$subcategory' ";
	  }
	  
	   if($srh_sub_cat){
		  if($q_where){
				$q_where.=" AND "; 
			}
		  $q_where.="p.sub_cat_id='$srh_sub_cat' ";
	  }
	  
	  if($product_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.product_id='$product_id'";
	  
	  }
	  
	  
	  //echo "dep_id:$dep_id";
	  if($dep_id){
		   if($q_where){
				$q_where.=" AND "; 
			}
	  	$q_where.="p.dep_id='$dep_id'";
	  
	  }
	  
	  if($product_name){
	       	if($q_where){
				$q_where.=" AND "; 
			}
		  // $q_where='WHERE ';
		   $q_where.="(p.product_name LIKE '%$product_name%' OR p.product_code LIKE '%$product_name%')";
	  }
	  
	  
	 if($q_where){
	    $q_where='WHERE '.$q_where;
	}

	  
	   
	  if($search_key_val){
		 // if($cat_srh){
			// $q_where.=" AND "; 
		//  }
			if($q_where){
				$q_where.=" AND "; 
			}
		  
		  //$q_where='';
            $q_where.="(p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '%$search_key_val%' OR pc.cat_name LIKE '%$search_key_val%')";
      }
	  
	  
	  
	  
	//  echo "q_where:".$q_where;
	
	
	$q="SELECT p.*,pc.cat_name,psc.sub_cat_name FROM (`product` p) 
INNER JOIN product_category pc ON p.cat_id=pc.cat_id 
INNER JOIN product_sub_category psc ON p.sub_cat_id=psc.sub_cat_id  $q_where
GROUP By p.product_id  order by p.product_name   $limit";
	
	/*
	//order by CAST(p.product_id AS SIGNED) asc
	$q="SELECT p.*,pc.cat_name,psc.sub_cat_name FROM (`product` p) 
INNER JOIN product_category pc ON pc.cat_id = p.cat_id
INNER JOIN product_sub_category psc ON pc.cat_id = p.cat_id $q_where
 GROUP By p.product_id order by CAST(p.product_name AS SIGNED) DESC $limit";
 */
 
	$query=$this->db->query($q);
 
	  //	 $lq = $this->db->last_query();	 
//	 echo $this->db->last_query();
     if($query->num_rows() >0)
     {
		 if($count){
			return  $query->num_rows();
			
		 }
       	else{
			return $query->result();
			 echo $this->db->last_query();
		}
     }
     else
     {
       return false;
     }
   } 
    function getProductsForReport_old($wherehouse_id='',$cat_srh='',$start='',$length='',$search_key_val='')
   {
/*echo "<script>alert('".$start."');</script>";	 */

     $this->db->select("p.*,pc.cat_name,psc.sub_cat_name");
     $this->db->from('product p');
	 $this->db->join("product_category pc", "pc.cat_id = p.cat_id", "left");
	  $this->db->join("product_sub_category psc", "psc.sub_cat_id = p.sub_cat_id", "left");
	  if($cat_srh){
	  $this->db->where("pc.cat_name","".$cat_srh."");
	  }
	  
	  if($search_key_val){
            $this->db->where("p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '%$search_key_val%' OR pc.cat_name LIKE '%$search_key_val%'");
      }
	  if($start!='' && $length!=''){
            if($length > 0)$this->db->limit($length,$start);
      }
	  
	$this->db->group_by("p.product_id"); 
	$this->db->order_by("p.product_name", "asc");
	$query = $this->db->get(); 
	  //	 $lq = $this->db->last_query();	 
	 echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   } 
   
   
   
   function getSupplierProductsForReport($srh_warehouse_id,$supplier_srh)
   {
	 $q='';
	 $q="SELECT p.*,s.supp_company_name,s.supp_code,SUM(IF(ft.fi_type_id ='sale', ft.fi_qty, 0)) AS sold_qty, 		SUM(IF(ft.fi_type_id ='grn', ft.fi_qty, 0)) AS purchased_qty
		FROM product p 
		LEFT JOIN purchase_items pi ON pi.product_id = p.product_id 
		LEFT JOIN purchases pu ON pu.id = pi.purchase_id
		LEFT JOIN supplier s ON s.supp_id = pu.supplier_id
		LEFT JOIN fi_table ft ON ft.fi_item_id = p.product_id AND (ft.fi_type_id ='sale' OR ft.fi_type_id ='grn')";
		if($supplier_srh){
			$q.="WHERE pu.supplier_id=$supplier_srh";	
		}else {
			$q.="WHERE p.product_id!=0";
		}
		$q.=" GROUP BY p.product_id  
		ORDER BY p.added_time desc";
		
		//echo "<br/>:".$q;
     $query=$this->db->query($q);
		
	
	 //echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }
   
   function get_product_by_name($name='',$product_id='')
   {
     $this->db->select('p.*');
     $this->db->from('product p');
    
     $this->db->where('p.product_name',$name);
	 if($product_id){
	 $this->db->where_not_in("p.product_id",$product_id);
	 }
	  $this->db->order_by("p.product_name", "asc");
     $query = $this->db->get();  
     if($query->num_rows() >0)
     {
       return $query->row();
     }
     else
     {
       return false;
     }
   }
   
   
   function get_product_by_id($product_id='',$cat_srh='',$subcategory='')
   {
     $this->db->select('p.*, c.cat_name, u.unit_name , t.*,u.unit_code');
     $this->db->from('product p');
     $this->db->join('product_category c','p.cat_id = c.cat_id','left');
     $this->db->join('mstr_unit u','p.product_unit = u.unit_id','left');
     $this->db->join('tax_rates t','p.tax = t.id','left');
      $this->db->where('p.product_id',$product_id);
	 if($cat_srh){
     $this->db->where('p.cat_id',$cat_srh);
	 }
	 if($subcategory){
	 $this->db->where('p.sub_cat_id',$subcategory);
	 }
     $query = $this->db->get();  
	 // echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->row();
     }
     else
     {
       return false;
     }
   }
   
   function get_sub_cat_name_by_id($id)
   {
	$this->db->select('s.sub_cat_name');
     $this->db->from('product_sub_category s');
	 $this->db->where("s.sub_cat_id",$id);
     $query = $this->db->get();
	 
	// echo $this->db->last_query();
   
     if($query->num_rows() >0)
     {
		// $name=$query->result();
		return $data['sub_cat_name']=$query->row()->sub_cat_name;
      
     }
     else
     {
       return false;
     }   
   }

   function get_warehouse_product($product_id='')
   {
     $this->db->select("w.name ,w.code , wp.quantity");
     $this->db->from("warehouses_products wp");
     $this->db->join("warehouses w","wp.warehouse_id = w.id","left");
     $this->db->where("wp.product_id",$product_id);
     $query = $this->db->get();  
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }

   function get_subcategory()
   {
     $this->db->select('*');
     $this->db->from('product_sub_category');
     $query = $this->db->get();
   
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }

    function update_product($prduct_id , $product_name,$product_code,$category,$subcategory,$unit,$product_cost,$product_price,$wholesale_price,$credit_salling_price,$tax,$alert_quty,$imgName,$imageThumb,$product_details,$product_part_no,$product_oem_part_number,$store_position,$product_max_qty,$product_sn_exissts,$product_cat_ref_id,$product_cost_2,$dep_id,$is_saleable,$ptype_id,$pro_model_id,$pro_color_id,$pro_capacity_id)

   {



    $data1 = array(

       'cat_id'             => $category,
       'ptype_id'             => $ptype_id,

       'sub_cat_id'           => $subcategory,

       'product_name'         => $product_name,

       'product_code'     => $product_code,

       'product_image'          => $imgName,

       'product_thumb'      => $imageThumb,

       'product_alert_qty'    => $alert_quty,

       'product_unit'       => $unit,

       'product_cost'       => $product_cost,

       'product_price'      => $product_price,

       'wholesale_price'      => $wholesale_price,

       'credit_salling_price'   => $credit_salling_price,

       'tax'            => $tax,

       'product_details'    => $product_details,
        'product_part_no' => $product_part_no,
		'product_oem_part_number' => $product_oem_part_number,
		'store_position'=>$store_position,
		'product_max_qty'=>$product_max_qty,
		'product_sn_exissts'=>$product_sn_exissts,
		'product_cat_ref_id'=>$product_cat_ref_id,
		'product_cost_2'       => $product_cost_2,
		'dep_id'=>$dep_id,
		'pro_model_id'       => $pro_model_id,
		'pro_color_id'       => $pro_color_id,
		'pro_capacity_id'       => $pro_capacity_id,
		'last_update_date'       => date("Y-m-d H:i:s"),
			'last_update_user'       => $this->session->userdata('ss_user_id'),
		

    );



    $data2 = array(

       'cat_id'             => $category,
       'ptype_id'             => $ptype_id,

       'sub_cat_id'           => $subcategory,

       'product_name'         => $product_name,

       'product_code'     => $product_code,

       'product_alert_qty'    => $alert_quty,

       'product_unit'       => $unit,

       'product_cost'       => $product_cost,

       'product_price'      => $product_price,

       'wholesale_price'      => $wholesale_price,

       'credit_salling_price'   => $credit_salling_price,

       'tax'            => $tax,

       'product_details'    => $product_details,
	    'product_part_no' => $product_part_no,
		'product_oem_part_number' => $product_oem_part_number,
		'store_position'=>$store_position,
		'product_max_qty'=>$product_max_qty,
		'product_sn_exissts'=>$product_sn_exissts,
		'product_cat_ref_id'=>$product_cat_ref_id,
		'product_cost_2'       => $product_cost_2,
		'dep_id'=>$dep_id,
		'pro_model_id'       => $pro_model_id,
		'pro_color_id'       => $pro_color_id,
		'pro_capacity_id'       => $pro_capacity_id,
		
		'last_update_date'       => date("Y-m-d H:i:s"),
			'last_update_user'       => $this->session->userdata('ss_user_id'),

    );







      if (!empty($imgName) && !empty($imageThumb)) {

        $data = $data1;

      }else{

        $data = $data2;

      }

//print_r($data);

      $this->db->where('product_id', $prduct_id);

     if($this->db->update('product', $data))

     {
         
         //echo $this->db->last_query();

       return true;

     }

     else

     {

       return false;

     }


   }

public function delete_product($product_id='')
{
  if ($this->check_del($product_id)) {
       return false;
  } else {
      $this->db->delete('product', array('product_id' => $product_id));
      return true;
  }
  
}

function check_del($product_id='')
{
  $this->db->select('product_id');
  $this->db->from('purchase_items');
  $this->db->where('product_id',$product_id);
  $query = $this->db->get();
  if($query->num_rows() > 0)
  {
    return true;
  }
  else{

    return false;
  }

}

function get_item_qty($product_id='')
{
  $this->db->select('SUM(p.quantity) AS qty');
  $this->db->from('purchase_items p');
  $this->db->where('p.product_id',$product_id);
  $query = $this->db->get();
  if($query->result())
  {
    return $query->result();
  }
  else{

    return false;
  }
}
 function getProductsForReport_c($wherehouse_id='',$cat_srh='',$start='',$length='',$search_key_val='')
   {
/*echo "<script>alert('".$start."');</script>";	 */

     $this->db->select("count(p.product_id) as count");
     $this->db->from('product p');
	 $this->db->join("product_category pc", "pc.cat_id = p.cat_id", "left");
	  $this->db->join("product_sub_category psc", "psc.sub_cat_id = p.sub_cat_id", "left");
	  if($cat_srh){
	  $this->db->where("pc.cat_name",$cat_srh);
	  }
	  
	  if($search_key_val){
            $this->db->where("p.product_name LIKE '%$search_key_val%' OR p.product_code LIKE '%$search_key_val%' OR pc.cat_name LIKE '%$search_key_val%'");
      }
	$query = $this->db->get(); 
	//  	 $lq = $this->db->last_query();	 
	// echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   } 
   
      function getProductIdsForReport($wherehouse_id='',$cat_srh='',$start='',$length='',$search_key_val='')
   {
/*echo "<script>alert('".$start."');</script>";	 */

     $this->db->select("p.product_id,p.product_price,p.product_cost");
     $this->db->from('product p');
	 $this->db->join("product_category pc", "pc.cat_id = p.cat_id", "left");
	  if($cat_srh){
	  $this->db->where("pc.cat_name",$cat_srh);
	  }
	$query = $this->db->get(); 
	 // 	 $lq = $this->db->last_query();	 
	// echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   } 

}
