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

class Home_model extends CI_Model {	
  private $DB1;
  function __construct()
  {
      parent::__construct();
      $this->DB1 = $this->load->database('remote', TRUE);
  } 
  function diamond_list($where)
	{ 
      if($where!="")
      {
        $where="WHERE ".$where;        
      }
      $order_query="ORDER BY shape_sort, SN_COLOR, clarity_sort ASC , weight_sort ASC ";
      $select="*,       
      FL_RAPNET - ((FL_RAPNET *  FL_RAPNET_DISCOUNT) / 100) as COST_CARAT,   
       CASE (UPPER(`SN_GRADE`))
        WHEN  'FL' THEN 'A' 
        WHEN 'IF' THEN 'B'
        WHEN 'VVS1' THEN 'C' 
        WHEN 'VVS2' THEN 'D'
        WHEN 'VS1' THEN 'E' 
        WHEN 'VS2' THEN 'F'
        WHEN 'SI1' THEN 'G' 
        WHEN 'SI2' THEN 'H'
        WHEN 'SI3' THEN 'I'
        WHEN 'I1' THEN 'J' 
        WHEN 'I2' THEN 'K' 
        WHEN 'I3' THEN 'L'
        ELSE 'Z' END 
        AS clarity_sort,
      CASE (UPPER(`SN_SHAPE`))
        WHEN 'ROUND' THEN 'A' 
        WHEN 'PRINCESS' THEN 'B'
        WHEN 'CUSHION' THEN 'C' 
        WHEN 'RADIANT' THEN 'D'
        WHEN 'ASSCHER' THEN 'E' 
        WHEN 'EMERALD' THEN 'F'
        WHEN 'OVAL' THEN 'G' 
        WHEN 'PEAR' THEN 'H'
        WHEN 'MARQUISE' THEN 'I'
        WHEN 'HEART' THEN 'J'
        ELSE 'Z' END 
        AS shape_sort,
       CASE   
        WHEN QT_WEIGHT BETWEEN 0.01 AND .29 THEN 'A'
        WHEN QT_WEIGHT BETWEEN 0.30 AND .39 THEN 'B'
        WHEN QT_WEIGHT BETWEEN 0.40 AND .49 THEN 'C'
        WHEN QT_WEIGHT BETWEEN 0.50 AND .59 THEN 'D'
        WHEN QT_WEIGHT BETWEEN 0.60 AND .69 THEN 'E'
        WHEN QT_WEIGHT BETWEEN 0.70 AND .79 THEN 'F'
        WHEN QT_WEIGHT BETWEEN 0.80 AND .89 THEN 'G'
        WHEN QT_WEIGHT BETWEEN 0.90 AND .99 THEN 'H'
        WHEN QT_WEIGHT BETWEEN 1.00 AND 1.19 THEN 'I'
        WHEN QT_WEIGHT BETWEEN 1.20 AND 1.49 THEN 'J'
        WHEN QT_WEIGHT BETWEEN 1.50 AND 1.69 THEN 'K'
        WHEN QT_WEIGHT BETWEEN 1.70 AND 1.99 THEN 'L'
        WHEN QT_WEIGHT BETWEEN 2.00 AND 2.39 THEN 'M'
        WHEN QT_WEIGHT BETWEEN 2.40 AND 2.99 THEN 'N'
        WHEN QT_WEIGHT BETWEEN 3.00 AND 3.99 THEN 'O'
        WHEN QT_WEIGHT BETWEEN 4.00 AND 4.99 THEN 'P'
        WHEN QT_WEIGHT BETWEEN 5.00 AND 5.99 THEN 'Q'
        WHEN QT_WEIGHT BETWEEN 6.00 AND 6.99 THEN 'R'
        WHEN QT_WEIGHT BETWEEN 7.00 AND 25.99 THEN 'S'
        ELSE 'E' END
        AS weight_sort,
        CASE 
        WHEN (SN_SHAPE in ('B','BR','RB','RD','RBC','Round Brilliant','RND','ROUND')) THEN 'ROUND' 
        WHEN (SN_SHAPE in ('P','PS','PSH','PB','PMB','PEAR')) THEN 'PEAR' 
        WHEN (SN_SHAPE in ('E','EM','EC','EMERALD')) THEN 'EMERALD' 
        WHEN (SN_SHAPE in ('MQB','M','MQ','MARQUISE')) THEN 'MARQUISE' 
        WHEN (SN_SHAPE in ('A','CSS','CSSC','AC','AS','ASSCHER')) THEN 'ASSCHER' 
        WHEN (SN_SHAPE in ('CU','CUSHION','C','CUX','CUSH','CUSHIONBRILLIANT','CB','CUS','CUSHION' )) THEN 'CUSHION' 
        WHEN (SN_SHAPE in ('PRN','PR','PRIN','PN','PC','MDSQB','SMB','PRINCESS')) THEN 'PRINCESS' 
        WHEN (SN_SHAPE in ('H','HS','HT','MHRC','HEART')) THEN 'HEART' 
        WHEN (SN_SHAPE in ('O','OV','OMB','Oval','OVAL')) THEN 'OVAL' 
        WHEN (SN_SHAPE in ('R','RAD','RA','RC','RDN','CRMB','RADIANT','RN','RADIANT')) THEN 'RADIANT' 
        ELSE 'NA' END 
        AS `shape_full`,
        CASE 
        WHEN SN_CUT IN('EX','Excellent') THEN 'Excellent'
        WHEN SN_CUT IN('VG','Very Good') THEN 'Very Good'
        WHEN SN_CUT IN('G','Good') THEN 'Good'
        WHEN SN_CUT IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS cut_full,
        CASE 
        WHEN SN_POLISH IN('EX','Excellent') THEN 'Excellent'
        WHEN SN_POLISH IN('VG','Very Good') THEN 'Very Good'
        WHEN SN_POLISH IN('G','Good') THEN 'Good'
        WHEN SN_POLISH IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS polish_full,
        CASE 
        WHEN SYMMETRY IN('EX','Excellent') THEN 'Excellent'
        WHEN SYMMETRY IN('VG','Very Good') THEN 'Very Good'
        WHEN SYMMETRY IN('G','Good') THEN 'Good'
        WHEN SYMMETRY IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS symmetry_full,
        CASE 
        WHEN FLUORESCENCE_INT IN('NON','None') THEN 'None'
        WHEN FLUORESCENCE_INT IN('MED','Medium') THEN 'Medium'
        WHEN FLUORESCENCE_INT IN('FNT','Faint') THEN 'Faint'
        WHEN FLUORESCENCE_INT IN('STG','Strong') THEN 'Strong'
        WHEN FLUORESCENCE_INT IN('VST','Very Strong') THEN 'Very Strong'
        ELSE 'NA' END
        AS fluor_full
      ";
      $table_name='tbl_inventory';
      $query = $this->DB1->query("select ".$select." from ".$table_name." ".$where." ".$order_query." ");
      
      return $query->result();
	} 
  function diamond_count($where)
  {      
      if($where!="")
      {
        $where="WHERE ".$where;        
      }
      $order_query="ORDER BY CD_INVENTORY_ID";
      $select='count(SN_SERIALNO) as diamond_count ';
      $table_name='tbl_inventory';
      $query = $this->DB1->query("select ".$select." from ".$table_name." ".$where." ".$order_query." ");
      return $query->result();
  }     
        
  function diamond_list_limit($where,$limit='',$offset=0)
  {  
      if($where!="")
      {
        $where="WHERE ".$where;
      }
       $order_query="ORDER BY shape_sort, SN_COLOR, clarity_sort ASC , weight_sort ASC ";
      $select="*, 
      FL_RAPNET - ((FL_RAPNET *  FL_RAPNET_DISCOUNT) / 100) as COST_CARAT,      
       CASE (UPPER(`SN_GRADE`))
        WHEN  'FL' THEN 'A' 
        WHEN 'IF' THEN 'B'
        WHEN 'VVS1' THEN 'C' 
        WHEN 'VVS2' THEN 'D'
        WHEN 'VS1' THEN 'E' 
        WHEN 'VS2' THEN 'F'
        WHEN 'SI1' THEN 'G' 
        WHEN 'SI2' THEN 'H'
        WHEN 'SI3' THEN 'I'
        WHEN 'I1' THEN 'J' 
        WHEN 'I2' THEN 'K' 
        WHEN 'I3' THEN 'L'
        ELSE 'Z' END 
        AS clarity_sort,
      CASE (UPPER(`SN_SHAPE`))
        WHEN 'ROUND' THEN 'A' 
        WHEN 'PRINCESS' THEN 'B'
        WHEN 'CUSHION' THEN 'C' 
        WHEN 'RADIANT' THEN 'D'
        WHEN 'ASSCHER' THEN 'E' 
        WHEN 'EMERALD' THEN 'F'
        WHEN 'OVAL' THEN 'G' 
        WHEN 'PEAR' THEN 'H'
        WHEN 'MARQUISE' THEN 'I'
        WHEN 'HEART' THEN 'J'
        ELSE 'Z' END 
        AS shape_sort,
       CASE   
        WHEN QT_WEIGHT BETWEEN 0.01 AND .29 THEN 'A'
        WHEN QT_WEIGHT BETWEEN 0.30 AND .39 THEN 'B'
        WHEN QT_WEIGHT BETWEEN 0.40 AND .49 THEN 'C'
        WHEN QT_WEIGHT BETWEEN 0.50 AND .59 THEN 'D'
        WHEN QT_WEIGHT BETWEEN 0.60 AND .69 THEN 'E'
        WHEN QT_WEIGHT BETWEEN 0.70 AND .79 THEN 'F'
        WHEN QT_WEIGHT BETWEEN 0.80 AND .89 THEN 'G'
        WHEN QT_WEIGHT BETWEEN 0.90 AND .99 THEN 'H'
        WHEN QT_WEIGHT BETWEEN 1.00 AND 1.19 THEN 'I'
        WHEN QT_WEIGHT BETWEEN 1.20 AND 1.49 THEN 'J'
        WHEN QT_WEIGHT BETWEEN 1.50 AND 1.69 THEN 'K'
        WHEN QT_WEIGHT BETWEEN 1.70 AND 1.99 THEN 'L'
        WHEN QT_WEIGHT BETWEEN 2.00 AND 2.39 THEN 'M'
        WHEN QT_WEIGHT BETWEEN 2.40 AND 2.99 THEN 'N'
        WHEN QT_WEIGHT BETWEEN 3.00 AND 3.99 THEN 'O'
        WHEN QT_WEIGHT BETWEEN 4.00 AND 4.99 THEN 'P'
        WHEN QT_WEIGHT BETWEEN 5.00 AND 5.99 THEN 'Q'
        WHEN QT_WEIGHT BETWEEN 6.00 AND 6.99 THEN 'R'
        WHEN QT_WEIGHT BETWEEN 7.00 AND 25.99 THEN 'S'
        ELSE 'E' END
        AS weight_sort,
      CASE 
        WHEN (SN_SHAPE in ('B','BR','RB','RD','RBC','Round Brilliant','RND','ROUND')) THEN 'ROUND' 
        WHEN (SN_SHAPE in ('P','PS','PSH','PB','PMB','PEAR')) THEN 'PEAR' 
        WHEN (SN_SHAPE in ('E','EM','EC','EMERALD')) THEN 'EMERALD' 
        WHEN (SN_SHAPE in ('MQB','M','MQ','MARQUISE')) THEN 'MARQUISE' 
        WHEN (SN_SHAPE in ('A','CSS','CSSC','AC','AS','ASSCHER')) THEN 'ASSCHER' 
        WHEN (SN_SHAPE in ('CU','CUSHION','C','CUX','CUSH','CUSHIONBRILLIANT','CB','CUS','CUSHION' )) THEN 'CUSHION' 
        WHEN (SN_SHAPE in ('PRN','PR','PRIN','PN','PC','MDSQB','SMB','PRINCESS')) THEN 'PRINCESS' 
        WHEN (SN_SHAPE in ('H','HS','HT','MHRC','HEART')) THEN 'HEART' 
        WHEN (SN_SHAPE in ('O','OV','OMB','Oval','OVAL')) THEN 'OVAL' 
        WHEN (SN_SHAPE in ('R','RAD','RA','RC','RDN','CRMB','RADIANT','RN','RADIANT')) THEN 'RADIANT' 
        ELSE 'NA' END 
        AS `shape_full`,
        CASE 
        WHEN SN_CUT IN('EX','Excellent') THEN 'Excellent'
        WHEN SN_CUT IN('VG','Very Good') THEN 'Very Good'
        WHEN SN_CUT IN('G','Good') THEN 'Good'
        WHEN SN_CUT IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS cut_full,
        CASE 
        WHEN SN_POLISH IN('EX','Excellent') THEN 'Excellent'
        WHEN SN_POLISH IN('VG','Very Good') THEN 'Very Good'
        WHEN SN_POLISH IN('G','Good') THEN 'Good'
        WHEN SN_POLISH IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS polish_full,
        CASE 
        WHEN SYMMETRY IN('EX','Excellent') THEN 'Excellent'
        WHEN SYMMETRY IN('VG','Very Good') THEN 'Very Good'
        WHEN SYMMETRY IN('G','Good') THEN 'Good'
        WHEN SYMMETRY IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS symmetry_full,
        CASE 
        WHEN FLUORESCENCE_INT IN('NON','None') THEN 'None'
        WHEN FLUORESCENCE_INT IN('MED','Medium') THEN 'Medium'
        WHEN FLUORESCENCE_INT IN('FNT','Faint') THEN 'Faint'
        WHEN FLUORESCENCE_INT IN('STG','Strong') THEN 'Strong'
        WHEN FLUORESCENCE_INT IN('VST','Very Strong') THEN 'Very Strong'
        ELSE 'NA' END
        AS fluor_full
      ";
      $table_name='tbl_inventory';
      $query = $this->DB1->query("select ".$select." 
        
        from ".$table_name." ".$where." ".$order_query." LIMIT  ".$offset." , ".$limit." ");
      
     return $query->result();
	  
  } 
  function diamond_price_min($where)
  {      
      if($where!="")
      {
        $where="WHERE ".$where;        
      }
      $order_query="ORDER BY CD_INVENTORY_ID";
      $select='MIN(FL_RAPNET) as price_min ';
      $table_name='tbl_inventory';
      $query = $this->DB1->query("select ".$select." from ".$table_name." ".$where." ".$order_query." ");
      return $query->result();
  }
  function diamond_price_max($where)
  {      
      if($where!="")
      {
        $where="WHERE ".$where;        
      }
      $order_query="ORDER BY CD_INVENTORY_ID";
      $select='MAX(FL_RAPNET) as price_max ';
      $table_name='gb_diamond';
      $query = $this->DB1->query("select ".$select." from ".$table_name." ".$where." ".$order_query." ");
      return $query->result();
  }
  //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  function vendor_list1()
  {
      $this->DB1->select('U.CD_USER_ID,UD.NM_COMPANY_NAME,UD.NM_FOLDER_NAME');
      $this->DB1->from('tbl_user U');
      $this->DB1->join('tbl_user_details UD','U.CD_USER_ID=UD.CD_USER_ID');    
        
      $this->DB1->where('U.CD_GROUP_ID','3');     
      $query=$this->DB1->get();
      return $query->result();
  }
  //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  function watch_list($where)
  {        
      if($where!="")
      {
        $where="WHERE ".$where;
      }
      $order_query="ORDER BY WATCH_ID ";
      $select='W.*,U.CD_USER_ID,U.NM_USER_FULLNAME, UD.NM_FOLDER_NAME, U.NM_USER_EMAIL';
      $table_name='tbl_watch W';
      $query = $this->DB1->query("select ".$select." from ".$table_name."
        join tbl_user as U on W.CD_VENDOR_ID=U.CD_USER_ID
        join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
          ".$where." ".$order_query." ");
      return $query->result();  
  }
  function watch_list_limit($where,$limit='',$offset=0,$order='')
  {        
      if($where!="")
      {
        $where="WHERE ".$where;
      }
      $order_query="ORDER BY WATCH_ID ";
      if($order!="")
      {
        $order_query="ORDER BY ".$order;
      }
      
      $select='W.*,U.CD_USER_ID,U.NM_USER_FULLNAME, UD.NM_FOLDER_NAME';
      $table_name='tbl_watch W';
      // echo "select ".$select." from ".$table_name."
      //   join tbl_user as U on W.CD_VENDOR_ID=U.CD_USER_ID
      //   join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
      //     ".$where." ".$order_query." LIMIT  ".$offset." , ".$limit." ";exit;
      $query = $this->DB1->query("select ".$select." from ".$table_name."
        join tbl_user as U on W.CD_VENDOR_ID=U.CD_USER_ID
        join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
          ".$where." ".$order_query." LIMIT  ".$offset." , ".$limit." ");
      return $query->result();  
  }         
  function watch_count($where)
  {      
      if($where!="")
      {
        $where="WHERE ".$where;         
      }
      $order_query="ORDER BY WATCH_ID ";
      $select='count(STOCK_ID) as watch_count';
      $table_name='tbl_watch as I';
      $query = $this->DB1->query("select ".$select." from ".$table_name."
      join tbl_user as U on I.CD_VENDOR_ID=U.CD_USER_ID
      join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
          ".$where." ".$order_query." ");
      return $query->result();
  }
  //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  function get_filter($column,$table,$where="")
  {
      $this->DB1->select($column);
      $this->DB1->from($table);
       
      $this->DB1->group_by($column); 
      $this->DB1->where($column.'!=',""); 
      if($where!="")
      {
         $this->DB1->where($where); 
      }
      $query=$this->DB1->get();
      return $query->result();
  }
  //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  function jewelry_list_limit($where,$limit='',$offset=0,$order='')
  {  
      if($where!="")
      {
        $where="WHERE ".$where." AND jewelry_id IN (  SELECT MIN(jewelry_id) FROM tbl_jewelry GROUP BY style )";
        //$where="WHERE ".$where;
      }
      $order_query="ORDER BY jewelry_id ";
      if($order!="")
      {
        $order_query="ORDER BY ".$order;
      }
      
      $select='UD.NM_FOLDER_NAME,J.*,U.CD_USER_ID';
      $table_name='tbl_jewelry J';     
      $query = $this->DB1->query("select ".$select." from ".$table_name."
        join tbl_user as U on J.CD_VENDOR_ID=U.CD_USER_ID
        join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
          ".$where." ".$order_query." LIMIT  ".$offset." , ".$limit);      
     
      return $query->result();
  }    
  function jewelry_list($where)
  { 
      if($where!="")
      {
        $where="WHERE ".$where;
      }
      $order_query="ORDER BY jewelry_id ";
      $select='UD.NM_FOLDER_NAME,J.*,U.CD_USER_ID,U.NM_USER_EMAIL';
      $table_name='tbl_jewelry J';
      $query = $this->DB1->query("select ".$select." from ".$table_name."
        join tbl_user as U on J.CD_VENDOR_ID=U.CD_USER_ID
        join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
          ".$where." GROUP BY size ".$order_query." ");
      return $query->result();
  }
  function jewelry_compare($where)
  { 
      if($where!="")
      {
        $where="WHERE ".$where;
      }
      $order_query="ORDER BY jewelry_id ";
      $select='UD.NM_FOLDER_NAME,J.*,U.CD_USER_ID,U.NM_USER_EMAIL';
      $table_name='tbl_jewelry J';
      $query = $this->DB1->query("select ".$select." from ".$table_name."
        join tbl_user as U on J.CD_VENDOR_ID=U.CD_USER_ID
        join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
          ".$where." ".$order_query." ");
      return $query->result();
  }
  function jewelry_count($where)
  {
      if($where!="")
      {
        $where="WHERE ".$where." AND jewelry_id IN (  SELECT MIN(jewelry_id) FROM tbl_jewelry GROUP BY style )";
        //$where="WHERE ".$where;
      }
      $order_query="ORDER BY jewelry_id ";
      $select='count(DISTINCT J.style) as jewelry_count';
      $table_name='tbl_jewelry J';
      
      $query = $this->DB1->query("select ".$select." from ".$table_name."
        join tbl_user as U on J.CD_VENDOR_ID=U.CD_USER_ID
        join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
          ".$where." ".$order_query." ");
      return $query->result();
  }
  // function jewelry_list_size($where)
  // { 
  //     if($where!="")
  //     {
  //       $where="WHERE ".$where;
  //     }
  //     $order_query="ORDER BY size DESC ";
  //     $select='UD.NM_FOLDER_NAME,J.*,U.CD_USER_ID,U.NM_USER_EMAIL';
  //     $table_name='tbl_jewelry J';
  //     $query = $this->DB1->query("select ".$select." from ".$table_name."
  //       join tbl_user as U on J.CD_VENDOR_ID=U.CD_USER_ID
  //       join tbl_user_details as UD on U.CD_USER_ID=UD.CD_USER_ID
  //         ".$where." GROUP BY size ".$order_query." ");
  //     return $query->result();
  // }
  // function img_check($jewelry_id,$value)
  // {
  //     $this->DB1->query("UPDATE tbl_jewelry SET img_check = ".$value." WHERE jewelry_id = ".$jewelry_id);
      
      
  // }
  function get_subcategory($category)
  {
      $this->DB1->select('sub_category');
      $this->DB1->from('tbl_jewelry');
      $this->DB1->group_by('sub_category'); 
      $this->DB1->where('category',$category);
      $query=$this->DB1->get();
      return $query->result();      
  }      
   
  
  function vendor_details($vendor_id)
  {
      $this->db->select('U.CD_USER_ID, U.NM_USER_EMAIL, U.NM_USER_FULLNAME, U.USER_PASSWORD, U.SN_USER_MOBILE, UD.NM_COMPANY_NAME, UD.COMPANY_TAGLINE,UD.SN_COMPANY_LOGO, UD.NM_COMPANY_BIO,UD.NM_COMPANY_WEBSITE,');
      $this->db->from('tbl_user U');
      $this->db->join('tbl_user_details UD','U.CD_USER_ID=UD.CD_USER_ID');    
        
      $this->db->where('U.CD_USER_ID',$vendor_id);     
      $query=$this->db->get();
      return $query->result();
  }
  function select($select,$table,$where)
  {
      $this->db->select($select);
      $this->db->from($table);
      $this->db->where($where);     
      $query=$this->db->get();
      return $query->result();
  }
  

  function diamond_list_match($where,$limit='',$offset=0)
  {  
      if($where!="")
      {
        $where="WHERE ".$where;
      }
      $order_query="ORDER BY shape_sort, SN_COLOR, clarity_sort ASC , weight_sort ASC ";
      $select="*,
       
      FL_RAPNET - ((FL_RAPNET *  FL_RAPNET_DISCOUNT) / 100) as COST_CARAT,      
       CASE (UPPER(`SN_GRADE`))
        WHEN  'FL' THEN 'A' 
        WHEN 'IF' THEN 'B'
        WHEN 'VVS1' THEN 'C' 
        WHEN 'VVS2' THEN 'D'
        WHEN 'VS1' THEN 'E' 
        WHEN 'VS2' THEN 'F'
        WHEN 'SI1' THEN 'G' 
        WHEN 'SI2' THEN 'H'
        WHEN 'SI3' THEN 'I'
        WHEN 'I1' THEN 'J' 
        WHEN 'I2' THEN 'K' 
        WHEN 'I3' THEN 'L'
        ELSE 'Z' END 
        AS clarity_sort,
      CASE (UPPER(`SN_SHAPE`))
        WHEN 'ROUND' THEN 'A' 
        WHEN 'PRINCESS' THEN 'B'
        WHEN 'CUSHION' THEN 'C' 
        WHEN 'RADIANT' THEN 'D'
        WHEN 'ASSCHER' THEN 'E' 
        WHEN 'EMERALD' THEN 'F'
        WHEN 'OVAL' THEN 'G' 
        WHEN 'PEAR' THEN 'H'
        WHEN 'MARQUISE' THEN 'I'
        WHEN 'HEART' THEN 'J'
        ELSE 'Z' END 
        AS shape_sort,
       CASE   
        WHEN QT_WEIGHT BETWEEN 0.01 AND .29 THEN 'A'
        WHEN QT_WEIGHT BETWEEN 0.30 AND .39 THEN 'B'
        WHEN QT_WEIGHT BETWEEN 0.40 AND .49 THEN 'C'
        WHEN QT_WEIGHT BETWEEN 0.50 AND .59 THEN 'D'
        WHEN QT_WEIGHT BETWEEN 0.60 AND .69 THEN 'E'
        WHEN QT_WEIGHT BETWEEN 0.70 AND .79 THEN 'F'
        WHEN QT_WEIGHT BETWEEN 0.80 AND .89 THEN 'G'
        WHEN QT_WEIGHT BETWEEN 0.90 AND .99 THEN 'H'
        WHEN QT_WEIGHT BETWEEN 1.00 AND 1.19 THEN 'I'
        WHEN QT_WEIGHT BETWEEN 1.20 AND 1.49 THEN 'J'
        WHEN QT_WEIGHT BETWEEN 1.50 AND 1.69 THEN 'K'
        WHEN QT_WEIGHT BETWEEN 1.70 AND 1.99 THEN 'L'
        WHEN QT_WEIGHT BETWEEN 2.00 AND 2.39 THEN 'M'
        WHEN QT_WEIGHT BETWEEN 2.40 AND 2.99 THEN 'N'
        WHEN QT_WEIGHT BETWEEN 3.00 AND 3.99 THEN 'O'
        WHEN QT_WEIGHT BETWEEN 4.00 AND 4.99 THEN 'P'
        WHEN QT_WEIGHT BETWEEN 5.00 AND 5.99 THEN 'Q'
        WHEN QT_WEIGHT BETWEEN 6.00 AND 6.99 THEN 'R'
        WHEN QT_WEIGHT BETWEEN 7.00 AND 25.99 THEN 'S'
        ELSE 'E' END
        AS weight_sort,
        CASE 
        WHEN (SN_SHAPE in ('B','BR','RB','RD','RBC','Round Brilliant','RND','ROUND')) THEN 'ROUND' 
        WHEN (SN_SHAPE in ('P','PS','PSH','PB','PMB','PEAR')) THEN 'PEAR' 
        WHEN (SN_SHAPE in ('E','EM','EC','EMERALD')) THEN 'EMERALD' 
        WHEN (SN_SHAPE in ('MQB','M','MQ','MARQUISE')) THEN 'MARQUISE' 
        WHEN (SN_SHAPE in ('A','CSS','CSSC','AC','AS','ASSCHER')) THEN 'ASSCHER' 
        WHEN (SN_SHAPE in ('CU','CUSHION','C','CUX','CUSH','CUSHIONBRILLIANT','CB','CUS','CUSHION' )) THEN 'CUSHION' 
        WHEN (SN_SHAPE in ('PRN','PR','PRIN','PN','PC','MDSQB','SMB','PRINCESS')) THEN 'PRINCESS' 
        WHEN (SN_SHAPE in ('H','HS','HT','MHRC','HEART')) THEN 'HEART' 
        WHEN (SN_SHAPE in ('O','OV','OMB','Oval','OVAL')) THEN 'OVAL' 
        WHEN (SN_SHAPE in ('R','RAD','RA','RC','RDN','CRMB','RADIANT','RN','RADIANT')) THEN 'RADIANT' 
        ELSE 'NA' END 
        AS `shape_full`,
        CASE 
        WHEN SN_CUT IN('EX','Excellent') THEN 'Excellent'
        WHEN SN_CUT IN('VG','Very Good') THEN 'Very Good'
        WHEN SN_CUT IN('G','Good') THEN 'Good'
        WHEN SN_CUT IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS cut_full,
        CASE 
        WHEN SN_POLISH IN('EX','Excellent') THEN 'Excellent'
        WHEN SN_POLISH IN('VG','Very Good') THEN 'Very Good'
        WHEN SN_POLISH IN('G','Good') THEN 'Good'
        WHEN SN_POLISH IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS polish_full,
        CASE 
        WHEN SYMMETRY IN('EX','Excellent') THEN 'Excellent'
        WHEN SYMMETRY IN('VG','Very Good') THEN 'Very Good'
        WHEN SYMMETRY IN('G','Good') THEN 'Good'
        WHEN SYMMETRY IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS symmetry_full,
        CASE 
        WHEN FLUORESCENCE_INT IN('NON','None') THEN 'None'
        WHEN FLUORESCENCE_INT IN('MED','Medium') THEN 'Medium'
        WHEN FLUORESCENCE_INT IN('FNT','Faint') THEN 'Faint'
        WHEN FLUORESCENCE_INT IN('STG','Strong') THEN 'Strong'
        WHEN FLUORESCENCE_INT IN('VST','Very Strong') THEN 'Very Strong'
        ELSE 'NA' END
        AS fluor_full
      ";
      $table_name='tbl_inventory as D';
      $query = $this->DB1->query("select ".$select." from ".$table_name." ".$where." ".$order_query ." LIMIT  ".$offset." , ".$limit." ");
      
      return $query->result();
  } 
  function diamond_list_match1($where)
  {  
      if($where!="")
      {
        $where="WHERE ".$where;
      }
      $order_query="ORDER BY shape_sort, SN_COLOR, clarity_sort ASC , weight_sort ASC ";
      $select="*,
       
      FL_RAPNET - ((FL_RAPNET *  FL_RAPNET_DISCOUNT) / 100) as COST_CARAT,      
       CASE (UPPER(`SN_GRADE`))
        WHEN  'FL' THEN 'A' 
        WHEN 'IF' THEN 'B'
        WHEN 'VVS1' THEN 'C' 
        WHEN 'VVS2' THEN 'D'
        WHEN 'VS1' THEN 'E' 
        WHEN 'VS2' THEN 'F'
        WHEN 'SI1' THEN 'G' 
        WHEN 'SI2' THEN 'H'
        WHEN 'SI3' THEN 'I'
        WHEN 'I1' THEN 'J' 
        WHEN 'I2' THEN 'K' 
        WHEN 'I3' THEN 'L'
        ELSE 'Z' END 
        AS clarity_sort,
      CASE (UPPER(`SN_SHAPE`))
        WHEN 'ROUND' THEN 'A' 
        WHEN 'PRINCESS' THEN 'B'
        WHEN 'CUSHION' THEN 'C' 
        WHEN 'RADIANT' THEN 'D'
        WHEN 'ASSCHER' THEN 'E' 
        WHEN 'EMERALD' THEN 'F'
        WHEN 'OVAL' THEN 'G' 
        WHEN 'PEAR' THEN 'H'
        WHEN 'MARQUISE' THEN 'I'
        WHEN 'HEART' THEN 'J'
        ELSE 'Z' END 
        AS shape_sort,
       CASE   
        WHEN QT_WEIGHT BETWEEN 0.01 AND .29 THEN 'A'
        WHEN QT_WEIGHT BETWEEN 0.30 AND .39 THEN 'B'
        WHEN QT_WEIGHT BETWEEN 0.40 AND .49 THEN 'C'
        WHEN QT_WEIGHT BETWEEN 0.50 AND .59 THEN 'D'
        WHEN QT_WEIGHT BETWEEN 0.60 AND .69 THEN 'E'
        WHEN QT_WEIGHT BETWEEN 0.70 AND .79 THEN 'F'
        WHEN QT_WEIGHT BETWEEN 0.80 AND .89 THEN 'G'
        WHEN QT_WEIGHT BETWEEN 0.90 AND .99 THEN 'H'
        WHEN QT_WEIGHT BETWEEN 1.00 AND 1.19 THEN 'I'
        WHEN QT_WEIGHT BETWEEN 1.20 AND 1.49 THEN 'J'
        WHEN QT_WEIGHT BETWEEN 1.50 AND 1.69 THEN 'K'
        WHEN QT_WEIGHT BETWEEN 1.70 AND 1.99 THEN 'L'
        WHEN QT_WEIGHT BETWEEN 2.00 AND 2.39 THEN 'M'
        WHEN QT_WEIGHT BETWEEN 2.40 AND 2.99 THEN 'N'
        WHEN QT_WEIGHT BETWEEN 3.00 AND 3.99 THEN 'O'
        WHEN QT_WEIGHT BETWEEN 4.00 AND 4.99 THEN 'P'
        WHEN QT_WEIGHT BETWEEN 5.00 AND 5.99 THEN 'Q'
        WHEN QT_WEIGHT BETWEEN 6.00 AND 6.99 THEN 'R'
        WHEN QT_WEIGHT BETWEEN 7.00 AND 25.99 THEN 'S'
        ELSE 'E' END
        AS weight_sort,
        CASE 
        WHEN (SN_SHAPE in ('B','BR','RB','RD','RBC','Round Brilliant','RND','ROUND')) THEN 'ROUND' 
        WHEN (SN_SHAPE in ('P','PS','PSH','PB','PMB','PEAR')) THEN 'PEAR' 
        WHEN (SN_SHAPE in ('E','EM','EC','EMERALD')) THEN 'EMERALD' 
        WHEN (SN_SHAPE in ('MQB','M','MQ','MARQUISE')) THEN 'MARQUISE' 
        WHEN (SN_SHAPE in ('A','CSS','CSSC','AC','AS','ASSCHER')) THEN 'ASSCHER' 
        WHEN (SN_SHAPE in ('CU','CUSHION','C','CUX','CUSH','CUSHIONBRILLIANT','CB','CUS','CUSHION' )) THEN 'CUSHION' 
        WHEN (SN_SHAPE in ('PRN','PR','PRIN','PN','PC','MDSQB','SMB','PRINCESS')) THEN 'PRINCESS' 
        WHEN (SN_SHAPE in ('H','HS','HT','MHRC','HEART')) THEN 'HEART' 
        WHEN (SN_SHAPE in ('O','OV','OMB','Oval','OVAL')) THEN 'OVAL' 
        WHEN (SN_SHAPE in ('R','RAD','RA','RC','RDN','CRMB','RADIANT','RN','RADIANT')) THEN 'RADIANT' 
        ELSE 'NA' END 
        AS `shape_full`,
        CASE 
        WHEN SN_CUT IN('EX','Excellent') THEN 'Excellent'
        WHEN SN_CUT IN('VG','Very Good') THEN 'Very Good'
        WHEN SN_CUT IN('G','Good') THEN 'Good'
        WHEN SN_CUT IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS cut_full,
        CASE 
        WHEN SN_POLISH IN('EX','Excellent') THEN 'Excellent'
        WHEN SN_POLISH IN('VG','Very Good') THEN 'Very Good'
        WHEN SN_POLISH IN('G','Good') THEN 'Good'
        WHEN SN_POLISH IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS polish_full,
        CASE 
        WHEN SYMMETRY IN('EX','Excellent') THEN 'Excellent'
        WHEN SYMMETRY IN('VG','Very Good') THEN 'Very Good'
        WHEN SYMMETRY IN('G','Good') THEN 'Good'
        WHEN SYMMETRY IN('F','Fair') THEN 'Fair'
        ELSE 'NA' END
        AS symmetry_full,
        CASE 
        WHEN FLUORESCENCE_INT IN('NON','None') THEN 'None'
        WHEN FLUORESCENCE_INT IN('MED','Medium') THEN 'Medium'
        WHEN FLUORESCENCE_INT IN('FNT','Faint') THEN 'Faint'
        WHEN FLUORESCENCE_INT IN('STG','Strong') THEN 'Strong'
        WHEN FLUORESCENCE_INT IN('VST','Very Strong') THEN 'Very Strong'
        ELSE 'NA' END
        AS fluor_full
      ";
      $table_name='tbl_inventory as D';
      $query = $this->DB1->query("select ".$select." from ".$table_name." ".$where." ".$order_query );
      
      return $query->result();
  } 
  
  
 function getreview()
 {
    $this->db->select('*');
    $this->db->from('tbl_product_review as PR');
    $this->db->join('tbl_customer', 'tbl_customer.customer_id = PR.user_id');
    $this->db->where('PR.status','Active');
    $query = $this->db->get();
    return $query->result();
   // echo $this->db->last_query(); die;
 }
  
}
