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

	class Mastermdl extends CI_model{

		public function get_cnt($table, $arr){

			$this->db->where($arr);
			return $this->db->count_all_results($table);
		}
		public function get_user($email){

			$this->db->where(array('user_name' => $email));
			return $this->db->get('user_master')->result_array();
		}

/************************ BROKER MASTER *******************************/

    public function get_all_broker_data()
    {
        $query ="
                    SELECT acc_id as broker_id, acc_name as broker_name FROM account_master where acc_grp_id = 23 ORDER BY acc_name ASC
                ";

        // echo "<pre>";print_r($query); exit;

        return $this->db->query($query)->result_array();
    }

/************************ /BROKER MASTER *******************************/

/************************ Exchange Rate MASTER *******************************/

    public function get_all_exchange_rate_data()
    {
        $query ="
                    SELECT exchange_rate_id, exchange_rate_date FROM exchange_rate_master ORDER BY exchange_rate_date ASC
                ";
        return $this->db->query($query)->result_array();
    }

/************************ /Exchange Rate MASTER *******************************/

/************************ CITY MASTER *******************************/

    public function get_all_city_data()
    {
        $query ="
                    SELECT city_id, city_name FROM city_master ORDER BY city_name ASC
                ";
        return $this->db->query($query)->result_array();
    }

/************************ /CITY MASTER *******************************/

/************************ STATE MASTER *******************************/

    public function get_all_state_data()
    {
        $query ="
                    SELECT state_id, state_name FROM state_master ORDER BY state_name ASC
                ";
        return $this->db->query($query)->result_array();
    }

/************************ /STATE MASTER *******************************/

/************************ COUNTRY MASTER *******************************/

    public function get_all_country_data()
    {
        $query ="
                    SELECT country_id, UPPER(country_name) as country_name FROM country_master ORDER BY country_name ASC
                ";
        return $this->db->query($query)->result_array();
    }

/************************ /COUNTRY MASTER *******************************/

/************************ HSN MASTER *******************************/

    public function get_all_hsn_data()
    {
        $query ="
                    SELECT hsn_id, hsn_code FROM hsn_master ORDER BY hsn_code ASC
                ";
        return $this->db->query($query)->result_array();
    }

/************************ /HSN MASTER *******************************/

/************************ Account MASTER *******************************/
	public function get_group_list()
	{
		return $this->db->query("SELECT g.* FROM group_master g WHERE g.grp_id NOT IN(5,6,23)")->result_array();
	}

	public function get_single_acc_record($acc_id)
	{
		$query = "SELECT ac.*,ct.city_id,ct.city_name FROM account_master ac
					LEFT JOIN city_master ct ON(ac.acc_city_id = ct.city_id)
				WHERE ac.acc_id = $acc_id";

		return $this->db->query($query)->result_array();

	}

	public function get_account_data_cnt($type)
    {
        $subsql = "";
        if(!empty($_GET['search_value_by_acc_id']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_id'];
        }
        if(!empty($_GET['search_value_by_acc_code']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_code'];
        }
        if(!empty($_GET['search_value_by_acc_email']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_email'];
        }
        if(!empty($_GET['search_value_by_acc_contact_person']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_contact_person'];
        }
        if(!empty($_GET['search_value_by_acc_mobile']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_mobile'];
        }
        if(!empty($_GET['search_value_by_acc_tel_1']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_tel_1'];
        }
        if(!empty($_GET['search_value_acc_op_balance']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_acc_op_balance'];
        }
        if(!empty($_GET['search_value_by_acc_city_id']))
        {
            $subsql .= " AND a.acc_city_id =".$_GET['search_value_by_acc_city_id'];
        }
        if(!empty($_GET['search_value_by_acc_state_id']))
        {
            $subsql .= " AND a.acc_state_id =".$_GET['search_value_by_acc_state_id'];
        }
        if(!empty($_GET['search_value_by_acc_country_id']))
        {
            $subsql .= " AND a.acc_country_id =".$_GET['search_value_by_acc_country_id'];
        }
        if(!empty($_GET['search_value_acc_drcr']) && $_GET['search_value_acc_drcr'] != 0)
        {
            $drcr = $_GET['search_value_acc_drcr'] == 1 ? 'CR' : 'DR';
            $subsql .= " AND a.acc_drcr = '$drcr'";
        }
        if(!empty($_GET['search_value_acc_status']) && $_GET['search_value_acc_status'] != 0)
        {
            $status = $_GET['search_value_acc_status'] == 1 ? 1 : 0;
            $subsql .= " AND a.acc_status = $status";
        }
        if(!empty($_GET['search_value_by_acc_kyc']) && $_GET['search_value_by_acc_kyc'] != 0)
        {
            $kyc = $_GET['search_value_by_acc_kyc'] == 1 ? 0 : 1;
            $subsql .= " AND a.acc_kyc = $kyc";
        }
        if($type != 0)
        {
            $subsql .= " AND a.acc_grp_id=$type";
        }
        else
        {
            $subsql .= " AND a.acc_grp_id NOT IN(5,6,23)";
        }
        $query = "
                    SELECT
                        a.acc_id
                    FROM
                        account_master a
                    LEFT JOIN
                        group_master g ON(a.acc_grp_id = g.grp_id)
                    LEFT JOIN
                        city_master ct ON(a.acc_city_id = ct.city_id)
                    WHERE
                        1
                        $subsql
                ";
        return $this->db->query($query)->num_rows();
    }
    public function get_account_data($type,$per_page, $offset)
    {
        $subsql = "";
        if(!empty($_GET['search_value_by_acc_id']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_id'];
        }
        if(!empty($_GET['search_value_by_acc_code']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_code'];
        }
        if(!empty($_GET['search_value_by_acc_email']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_email'];
        }
        if(!empty($_GET['search_value_by_acc_contact_person']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_contact_person'];
        }
        if(!empty($_GET['search_value_by_acc_mobile']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_mobile'];
        }
        if(!empty($_GET['search_value_by_acc_tel_1']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_by_acc_tel_1'];
        }
        if(!empty($_GET['search_value_acc_op_balance']))
        {
            $subsql .= " AND a.acc_id =".$_GET['search_value_acc_op_balance'];
        }
        if(!empty($_GET['search_value_by_acc_city_id']))
        {
            $subsql .= " AND a.acc_city_id =".$_GET['search_value_by_acc_city_id'];
        }
        if(!empty($_GET['search_value_by_acc_state_id']))
        {
            $subsql .= " AND a.acc_state_id =".$_GET['search_value_by_acc_state_id'];
        }
        if(!empty($_GET['search_value_by_acc_country_id']))
        {
            $subsql .= " AND a.acc_country_id =".$_GET['search_value_by_acc_country_id'];
        }
        if(!empty($_GET['search_value_acc_drcr']) && $_GET['search_value_acc_drcr'] != 0)
        {
            $drcr = $_GET['search_value_acc_drcr'] == 1 ? 'CR' : 'DR';
            $subsql .= " AND a.acc_drcr = '$drcr'";
        }
        if(!empty($_GET['search_value_acc_status']) && $_GET['search_value_acc_status'] != 0)
        {
            $status = $_GET['search_value_acc_status'] == 1 ? 1 : 0;
            $subsql .= " AND a.acc_status = $status";
        }
        if(!empty($_GET['search_value_by_acc_kyc']) && $_GET['search_value_by_acc_kyc'] != 0)
        {
            $kyc = $_GET['search_value_by_acc_kyc'] == 1 ? 0 : 1;
            $subsql .= " AND a.acc_kyc = $kyc";
        }
        if($type != 0)
        {
            $subsql .= " AND a.acc_grp_id=$type";
        }
        else
        {
            $subsql .= " AND a.acc_grp_id NOT IN(5,6,23)";
        }
        $query = "
                    SELECT
                        a.*,g.grp_name,ct.city_name
                    FROM
                        account_master a
                    LEFT JOIN
                        group_master g ON(a.acc_grp_id = g.grp_id)
                    LEFT JOIN
                        city_master ct ON(a.acc_city_id = ct.city_id)
                    WHERE
                        1
                        $subsql
                    ORDER BY
                        acc_id DESC
                    LIMIT
                        $per_page
                    OFFSET
                        $offset
                ";
        // echo "<pre>";print_r($query); exit;
        $record['account_data'] = $this->db->query($query)->result_array();

        // acc name drop down
        $acc_query = "SELECT am.* FROM account_master am WHERE am.acc_grp_id = $type ORDER BY am.acc_name ASC";
        $acc_data = $this->db->query($acc_query)->result_array();
        $record['acc_name_drop_down'][0] = 'ALL';
        foreach ($acc_data as $key => $value)
        {
            $record['acc_name_drop_down'][$value['acc_id']] = strtoupper($value['acc_name']);
        }
        // acc name drop down

        // acc kyc drop down
        $record['acc_kyc_drop_down'][0] = 'ALL';
        $record['acc_kyc_drop_down'][1] = 'No';
        $record['acc_kyc_drop_down'][2] = 'Yes';
        // acc kyc drop down


        return $record;
    }


/************************ /Account MASTER *******************************/

    public function get_single_bank_rate($bank_rate_id)
    {
        $record['bank_rate_data'] = $this->db_operations->get_record("bank_rate_master", array("bank_rate_id" => $bank_rate_id));
        $record['is_exists'] = $this->db->query("
                                                    SELECT ct.ct_id
                                                    FROM challan_master cm
                                                    LEFT JOIN challan_trans ct ON(ct.ct_cm_id = cm.cm_id)
                                                    WHERE cm.cm_entry_date = '".$record['bank_rate_data'][0]['bank_rate_date']."'
                                                    AND ct.ct_bank_rate = ".$record['bank_rate_data'][0]['bank_rate']."
                                                ")->num_rows();
        return $record;
    }

/*diamond master*/
    /*diamond master*/
	public function get_diamond_data($id)
    {
        return $this->db->query("SELECT dm.*
         FROM diamond_master dm WHERE dm_id = $id")->result_array();
    }
	/*diamond master*/
	public function get_all_diamond_data_cnt()
    {
        // echo "<pre>"; print_r($_GET);exit;
        $subsql     = "";
        $start_date = date('2019-06-01');
        $end_date   = date('Y-m-d');

        if(!empty($_REQUEST['search_value_stock_id']))
        {
            $stock_id = implode(',', $_REQUEST['search_value_stock_id']);
            // echo "<pre>"; print_r($stock_id); exit;
            $subsql .= " AND dm.dm_id IN (".$stock_id. ")";
        }
        if(!empty($_REQUEST['search_value_sh']))
        {
            $sh = '';
            foreach ($_REQUEST['search_value_sh'] as $key => $value)
            {
                $db_sh = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_sh'];
                $sh .= "'".$db_sh."',";
            }
            $sh = rtrim($sh, ',');
            // echo "<pre>"; print_r($sh); exit;
            $subsql .= " AND dm.dm_sh IN (".$sh. ")";
        }
        if(!empty($_REQUEST['search_value_wt']))
        {
            $array = str_replace( "-", " AND ", $_REQUEST['search_value_wt']);
            /*$wt = implode(',',$array);*/
            // echo "<pre>"; print_r($stock_id); exit;
            $subsql .= "AND";
            $weight = '';
             foreach($array as $wt)
             {
                $weight .= " dm.dm_wt BETWEEN ".$wt."OR";
             }
              $subsql .= rtrim($weight,"OR");
        }
        if(!empty($_REQUEST['search_value_col']))
        {
            $col = '';
            foreach ($_REQUEST['search_value_col'] as $key => $value)
            {
                $db_col = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_col'];
                $col .= "'".$db_col."',";
            }
            $col = rtrim($col, ',');
            // echo "<pre>"; print_r($col); exit;
            $subsql .= " AND dm.dm_col IN (".$col. ")";
        }
        if(!empty($_REQUEST['search_value_cla']))
        {
            $cla = '';
            foreach ($_REQUEST['search_value_cla'] as $key => $value)
            {
                $db_cla = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_cla'];
                $cla .= "'".$db_cla."',";
            }
            $cla = rtrim($cla, ',');
            // echo "<pre>"; print_r($cla); exit;
            $subsql .= " AND dm.dm_cla IN (".$cla. ")";
        }
        if(!empty($_REQUEST['search_value_cut']))
        {
            $cut = '';
            foreach ($_REQUEST['search_value_cut'] as $key => $value)
            {
                $db_cut = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_cut'];
                $cut .= "'".$db_cut."',";
            }
            $cut = rtrim($cut, ',');
            // echo "<pre>"; print_r($cut); exit;
            $subsql .= " AND dm.dm_cut IN (".$cut. ")";
        }
        if(!empty($_REQUEST['search_value_pol']))
        {
            $pol = '';
            foreach ($_REQUEST['search_value_pol'] as $key => $value)
            {
                $db_pol = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_pol'];
                $pol .= "'".$db_pol."',";
            }
            $pol = rtrim($pol, ',');
            // echo "<pre>"; print_r($pol); exit;
            $subsql .= " AND dm.dm_pol IN (".$pol. ")";
        }
        if(!empty($_REQUEST['search_value_sym']))
        {
            $sym = '';
            foreach ($_REQUEST['search_value_sym'] as $key => $value)
            {
                $db_sym = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_sym'];
                $sym .= "'".$db_sym."',";
            }
            $sym = rtrim($sym, ',');
            // echo "<pre>"; print_r($sym); exit;
            $subsql .= " AND dm.dm_sym IN (".$sym. ")";
        }
        if(!empty($_REQUEST['search_value_fl']))
        {
            $fl = '';
            foreach ($_REQUEST['search_value_fl'] as $key => $value)
            {
                $db_fl = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_fl'];
                $fl .= "'".$db_fl."',";
            }
            $fl = rtrim($fl, ',');
            // echo "<pre>"; print_r($fl); exit;
            $subsql .= " AND dm.dm_fl IN (".$fl. ")";
        }
        if(!empty($_REQUEST['search_value_lab']))
        {
            $lab = '';
            foreach ($_REQUEST['search_value_lab'] as $key => $value)
            {
                $db_lab = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_lab'];
                $lab .= "'".$db_lab."',";
            }
            $lab = rtrim($lab, ',');
            // echo "<pre>"; print_r($lab); exit;
            $subsql .= " AND dm.dm_lab IN (".$lab. ")";
        }
        if(!empty($_REQUEST['search_value_status']) && $_REQUEST['search_value_status'] != 0)
        {
            if($_REQUEST['search_value_status'] == 1)
            {
                $status = 0;
            }
            elseif ($_REQUEST['search_value_status'] == 2)
            {
                $status = 1;
            }
            elseif ($_REQUEST['search_value_status'] == 3)
            {
                $status = 2;
            }
            else
            {
                $status = 3;
            }
            $subsql .= " AND dm.dm_status =".$status;
        }
        else
        {
            $subsql .= " AND (dm.dm_status != 3)";
        }
        if(!empty($_REQUEST['search_value_start_date']) && !empty($_REQUEST['search_value_end_date']))
        {
            $start_date = date('Y-m-d', strtotime($_REQUEST['search_value_start_date']));
            $end_date = date('Y-m-d', strtotime($_REQUEST['search_value_end_date']));
            $subsql .= " AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'";
        }
        else
        {
            $subsql .= " AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'";
        }


       if(!empty($_REQUEST['search_value_from_dollar']) && !empty($_REQUEST['search_value_to_dollar']))
        {
            $subsql .= " AND dm.dm_dollar BETWEEN ".$_REQUEST['search_value_from_dollar']." AND ".$_REQUEST['search_value_to_dollar'];
        }

        if(!empty($_REQUEST['search_value_from_ppc']) && !empty($_REQUEST['search_value_to_ppc']))
        {
            $subsql .= " AND dm.dm_price_ct BETWEEN ".$_REQUEST['search_value_from_ppc']." AND ".$_REQUEST['search_value_to_ppc'];
        }

        if(!empty($_REQUEST['search_value_key2sym']))
        {

            $db_key2sym = $this->db_operations->get_record('diamond_master', ['dm_id'=>$_REQUEST['search_value_key2sym']]);
            if(!empty($db_key2sym))
            {
                $key2sym = $db_key2sym[0]['dm_key2sym'];
                $subsql .= " AND dm.dm_key2sym = '".$key2sym."'";
            }
        }
        if(!empty($_REQUEST['search_value_r2net']))
        {
            $dm_r2net = $_REQUEST['search_value_r2net'] == 1 ? 0 : 1;
            $subsql .= " AND dm.dm_r2net = ".$dm_r2net;
        }

        // if(!empty($_REQUEST['search_value_category']))
        // {
        //     $dm_r2net = $_REQUEST['search_value_category'] == 1 ? 0 : 1;
        //     $subsql .= " AND dm.dm_category = ".$dm_r2net;
        // }

        if(!empty($_REQUEST['search_value_suspense']))
        {
            $dm_suspense = $_REQUEST['search_value_suspense'] == 1 ? 0 : 1;
            $subsql .= " AND dm.dm_suspense = ".$dm_suspense;
        }

        if (isset($_REQUEST['search_value_category']) && $_REQUEST['search_value_category'] !== '') 
        {
            $cat = $_REQUEST['search_value_category'];

            // 2 = ALL → do not apply filter
            if ($cat != 2) {
                // CERTIFIED = 1 → dm_category = 1
                // NON-CERTIFIED = 0 → dm_category = 0
                $dm_category = ($cat == 1 ? 1 : 0);
                $subsql .= " AND dm.dm_category = ".$dm_category;
            }
        }

        return $this->db->query("SELECT dm.* FROM diamond_master dm
                WHERE 1 $subsql
            ")->num_rows();
    }

    // Get record by ID
    public function get_by_id($dm_id)
    {
        // get current dollar rate
        $current_dollar_rate = $this->db->query("
            SELECT exchange_rate_buying_rate
            FROM exchange_rate_master
            WHERE exchange_rate_date = '".date('Y-m-d')."'
        ")->row_array();

        // get diamond record
        $record['data'] = $this->db->where('dm_id', $dm_id)
                        ->get('diamond_master')
                        ->row_array();

        // concat / append rate to record
        $record['current_dollar_rate'] = !empty($current_dollar_rate)
            ? $current_dollar_rate['exchange_rate_buying_rate']
            : '';
        // echo "<pre>"; print_r($record); exit;

        return $record;
    }
    public function diamond_code_exists($code, $exclude_id = 0)
    {
        $this->db->where('dm_code', $code);
        
        if ($exclude_id > 0) {
            $this->db->where('dm_id !=', $exclude_id);
        }

        return $this->db->get('diamond_master')->num_rows() > 0;
    }

    public function diamond_add($data)
    {
        $insert_data = [
            'dm_category'               => $data['rg_dm_category'] ?? 0,

            'dm_dollar_rate'            => $data['rg_dm_dollar_rate'] ?? null,
            'dm_date'                   => $data['rg_dm_date'] ?? null,
            
            'dm_location'               => $data['rg_dm_location'] ?? null,
            'dm_dna'                    => $data['rg_dm_dna'] ?? null,
            'dm_tbl'                    => $data['rg_dm_tbl'] ?? null,
            'dm_td_percentage'          => $data['rg_dm_td_percentage'] ?? null,
            'dm_l_w'                    => $data['rg_dm_l_w'] ?? null,
            'dm_bic'                    => $data['rg_dm_bic'] ?? null,
            'dm_bis'                    => $data['rg_dm_bis'] ?? null,
            'dm_wic'                    => $data['rg_dm_wic'] ?? null,
            'dm_wis'                    => $data['rg_dm_wis'] ?? null,
            'dm_col_tinge'              => $data['rg_dm_col_tinge'] ?? null,
            'dm_lus'                    => $data['rg_dm_lus'] ?? null,
            'dm_remarks'                => $data['rg_dm_remarks'] ?? null,
            'dm_h_a'                    => $data['rg_dm_h_a'] ?? null,
            'dm_girdle'                 => $data['rg_dm_girdle'] ?? null,
            'dm_girdle_percentage'      => $data['rg_dm_girdle_percentage'] ?? null,
            'dm_girdle_cond'            => $data['rg_dm_girdle_cond'] ?? null,
            'dm_ca'                     => $data['rg_dm_ca'] ?? null,
            'dm_ch'                     => $data['rg_dm_ch'] ?? null,
            'dm_pa'                     => $data['rg_dm_pa'] ?? null,
            'dm_pd'                     => $data['rg_dm_pd'] ?? null,
            'dm_str_ln'                 => $data['rg_dm_str_ln'] ?? null,
            'dm_lr_half'                => $data['rg_dm_lr_half'] ?? null,
            'dm_culet'                  => $data['rg_dm_culet'] ?? null,
            'dm_kts'                    => $data['rg_dm_kts'] ?? null,
            'dm_rep_com'                => $data['rg_dm_rep_com'] ?? null,
            'dm_cert_date'              => $data['rg_dm_cert_date'] ?? null,
            'dm_image_link'             => $data['rg_dm_image_link'] ?? null,
            'dm_certificate_link'       => $data['rg_dm_certificate_link'] ?? null,

            'dm_code'                   => $data['rg_dm_code'] ?? null,
            'dm_price_ct'               => $data['rg_dm_ppc'] ?? null,
            'dm_pc_amt'                 => $data['rg_dm_pc_amt'] ?? null,
            'dm_dollar'                 => $data['rg_dm_dollar'] ?? null,
            'dm_dollar_amt'             => $data['rg_dm_dollar_amt'] ?? null,
            'dm_size'                   => $data['rg_dm_size'] ?? null,
            'dm_pcs'                    => $data['rg_dm_pcs'] ?? null,
            'dm_cpd'                    => $data['rg_dm_cpd'] ?? null,
            'dm_sh'                     => $data['rg_dm_sh'] ?? null,
            'dm_lab'                    => $data['rg_dm_lab'] ?? null,
            'dm_cert_no'                => $data['rg_dm_cert_no'] ?? null,
            'dm_carat'                  => $data['rg_dm_carat'] ?? null,
            'dm_col'                    => $data['rg_dm_col'] ?? null,
            'dm_cla'                    => $data['rg_dm_cla'] ?? null,
            'dm_cut'                    => $data['rg_dm_cut'] ?? null,
            'dm_pol'                    => $data['rg_dm_pol'] ?? null,
            'dm_sym'                    => $data['rg_dm_sym'] ?? null,
            'dm_fl'                     => $data['rg_dm_fl'] ?? null,
            'dm_len'                    => $data['rg_dm_len'] ?? null,
            'dm_wid'                    => $data['rg_dm_wid'] ?? null,
            'dm_hei'                    => $data['rg_dm_hei'] ?? null,
            'dm_nat_lab_grown'          => $data['rg_dm_nat_lab_grown'] ?? null,
            'dm_disc'                   => $data['rg_dm_disc'] ?? null,
            'dm_rap'                    => $data['rg_dm_rap'] ?? null,
            'dm_video_link'             => $data['rg_dm_video_link'] ?? null,
            'dm_user_id'                => $_SESSION['user_id'] ?? null,
            'dm_date_create'            => date('Y-m-d') ?? null
        ];

        $this->db->insert('diamond_master', $insert_data);

        return $this->db->insert_id();
    }

    public function diamond_edit($dm_id, $data) {
        $update_data = [
            'dm_category'               => $data['rg_dm_category'] ?? 0,

            'dm_dollar_rate'            => $data['rg_dm_dollar_rate'] ?? null,
            'dm_date'                   => $data['rg_dm_date'] ?? null,

            'dm_location'               => $data['rg_dm_location'] ?? null,
            'dm_dna'                    => $data['rg_dm_dna'] ?? null,
            'dm_tbl'                    => $data['rg_dm_tbl'] ?? null,
            'dm_td_percentage'          => $data['rg_dm_td_percentage'] ?? null,
            'dm_l_w'                    => $data['rg_dm_l_w'] ?? null,
            'dm_bic'                    => $data['rg_dm_bic'] ?? null,
            'dm_bis'                    => $data['rg_dm_bis'] ?? null,
            'dm_wic'                    => $data['rg_dm_wic'] ?? null,
            'dm_wis'                    => $data['rg_dm_wis'] ?? null,
            'dm_col_tinge'              => $data['rg_dm_col_tinge'] ?? null,
            'dm_lus'                    => $data['rg_dm_lus'] ?? null,
            'dm_remarks'                => $data['rg_dm_remarks'] ?? null,
            'dm_h_a'                    => $data['rg_dm_h_a'] ?? null,
            'dm_girdle'                 => $data['rg_dm_girdle'] ?? null,
            'dm_girdle_percentage'      => $data['rg_dm_girdle_percentage'] ?? null,
            'dm_girdle_cond'            => $data['rg_dm_girdle_cond'] ?? null,
            'dm_ca'                     => $data['rg_dm_ca'] ?? null,
            'dm_ch'                     => $data['rg_dm_ch'] ?? null,
            'dm_pa'                     => $data['rg_dm_pa'] ?? null,
            'dm_pd'                     => $data['rg_dm_pd'] ?? null,
            'dm_str_ln'                 => $data['rg_dm_str_ln'] ?? null,
            'dm_lr_half'                => $data['rg_dm_lr_half'] ?? null,
            'dm_culet'                  => $data['rg_dm_culet'] ?? null,
            'dm_kts'                    => $data['rg_dm_kts'] ?? null,
            'dm_rep_com'                => $data['rg_dm_rep_com'] ?? null,
            'dm_cert_date'              => $data['rg_dm_cert_date'] ?? null,
            'dm_image_link'             => $data['rg_dm_image_link'] ?? null,
            'dm_certificate_link'       => $data['rg_dm_certificate_link'] ?? null,

            'dm_code'                   => $data['rg_dm_code'] ?? null,
            'dm_price_ct'               => $data['rg_dm_ppc'] ?? null,
            'dm_pc_amt'                 => $data['rg_dm_pc_amt'] ?? null,
            'dm_dollar'                 => $data['rg_dm_dollar'] ?? null,
            'dm_dollar_amt'             => $data['rg_dm_dollar_amt'] ?? null,
            'dm_size'                   => $data['rg_dm_size'] ?? null,
            'dm_pcs'                    => $data['rg_dm_pcs'] ?? null,
            'dm_cpd'                    => $data['rg_dm_cpd'] ?? null,
            'dm_sh'                     => $data['rg_dm_sh'] ?? null,
            'dm_lab'                    => $data['rg_dm_lab'] ?? null,
            'dm_cert_no'                => $data['rg_dm_cert_no'] ?? null,
            'dm_carat'                  => $data['rg_dm_carat'] ?? null,
            'dm_col'                    => $data['rg_dm_col'] ?? null,
            'dm_cla'                    => $data['rg_dm_cla'] ?? null,
            'dm_cut'                    => $data['rg_dm_cut'] ?? null,
            'dm_pol'                    => $data['rg_dm_pol'] ?? null,
            'dm_sym'                    => $data['rg_dm_sym'] ?? null,
            'dm_fl'                     => $data['rg_dm_fl'] ?? null,
            'dm_len'                    => $data['rg_dm_len'] ?? null,
            'dm_wid'                    => $data['rg_dm_wid'] ?? null,
            'dm_hei'                    => $data['rg_dm_hei'] ?? null,
            'dm_nat_lab_grown'          => $data['rg_dm_nat_lab_grown'] ?? null,
            'dm_disc'                   => $data['rg_dm_disc'] ?? null,
            'dm_rap'                    => $data['rg_dm_rap'] ?? null,
            'dm_rap_dollar'             => $data['rg_dm_rap_dollar'] ?? null,
            'dm_video_link'             => $data['rg_dm_video_link'] ?? null
        ];

        return $this->db->where('dm_id', $dm_id)
                        ->update('diamond_master', $update_data);
    }
    public function get_all_diamond_data($per_page, $offset, $check_flags=null)
    {

        $subsql = "";
        $start_date = date('2019-06-01');
        $end_date   = date('Y-m-d');

        // echo "<pre>"; print_r($_REQUEST);exit;


        if(!empty($_REQUEST['search_value_stock_id']))
        {
            $stock_id = implode(',', $_REQUEST['search_value_stock_id']);
            // echo "<pre>"; print_r($stock_id); exit;
            $subsql .= " AND dm.dm_id IN (".$stock_id.")";
        }
        if(!empty($_REQUEST['search_value_sh']))
        {
            $sh = '';
            foreach ($_REQUEST['search_value_sh'] as $key => $value)
            {
                $db_sh = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_sh'];
                $sh .= "'".$db_sh."',";
            }
            $sh = rtrim($sh, ',');
            // echo "<pre>"; print_r($sh); exit;
            $subsql .= " AND dm.dm_sh IN (".$sh. ")";
        }
        if(!empty($_REQUEST['search_value_wt']))
        {
            $array = str_replace( "-", " AND ", $_REQUEST['search_value_wt']);
            /*$wt = implode(',',$array);*/
            // echo "<pre>"; print_r($stock_id); exit;
            $subsql .= "AND";
            $weight = '';
             foreach($array as $wt)
             {
                $weight .= " dm.dm_wt BETWEEN ".$wt."OR";
             }
              $subsql .= rtrim($weight,"OR");
        }
        if(!empty($_REQUEST['search_value_col']))
        {
            $col = '';
            foreach ($_REQUEST['search_value_col'] as $key => $value)
            {
                $db_col = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_col'];
                $col .= "'".$db_col."',";
            }
            $col = rtrim($col, ',');
            // echo "<pre>"; print_r($col); exit;
            $subsql .= " AND dm.dm_col IN (".$col. ")";
        }
        if(!empty($_REQUEST['search_value_cla']))
        {
            $cla = '';
            foreach ($_REQUEST['search_value_cla'] as $key => $value)
            {
                $db_cla = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_cla'];
                $cla .= "'".$db_cla."',";
            }
            $cla = rtrim($cla, ',');
            // echo "<pre>"; print_r($cla); exit;
            $subsql .= " AND dm.dm_cla IN (".$cla. ")";
        }
        if(!empty($_REQUEST['search_value_cut']))
        {
            $cut = '';
            foreach ($_REQUEST['search_value_cut'] as $key => $value)
            {
                $db_cut = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_cut'];
                $cut .= "'".$db_cut."',";
            }
            $cut = rtrim($cut, ',');
            // echo "<pre>"; print_r($cut); exit;
            $subsql .= " AND dm.dm_cut IN (".$cut. ")";
        }
        if(!empty($_REQUEST['search_value_pol']))
        {
            $pol = '';
            foreach ($_REQUEST['search_value_pol'] as $key => $value)
            {
                $db_pol = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_pol'];
                $pol .= "'".$db_pol."',";
            }
            $pol = rtrim($pol, ',');
            // echo "<pre>"; print_r($pol); exit;
            $subsql .= " AND dm.dm_pol IN (".$pol. ")";
        }
        if(!empty($_REQUEST['search_value_sym']))
        {
            $sym = '';
            foreach ($_REQUEST['search_value_sym'] as $key => $value)
            {
                $db_sym = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_sym'];
                $sym .= "'".$db_sym."',";
            }
            $sym = rtrim($sym, ',');
            // echo "<pre>"; print_r($sym); exit;
            $subsql .= " AND dm.dm_sym IN (".$sym. ")";
        }
        if(!empty($_REQUEST['search_value_fl']))
        {
            $fl = '';
            foreach ($_REQUEST['search_value_fl'] as $key => $value)
            {
                $db_fl = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_fl'];
                $fl .= "'".$db_fl."',";
            }
            $fl = rtrim($fl, ',');
            // echo "<pre>"; print_r($fl); exit;
            $subsql .= " AND dm.dm_fl IN (".$fl. ")";
        }
        if(!empty($_REQUEST['search_value_lab']))
        {
            $lab = '';
            foreach ($_REQUEST['search_value_lab'] as $key => $value)
            {
                $db_lab = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_lab'];
                $lab .= "'".$db_lab."',";
            }
            $lab = rtrim($lab, ',');
            // echo "<pre>"; print_r($lab); exit;
            $subsql .= " AND dm.dm_lab IN (".$lab. ")";
        }
        if(!empty($_REQUEST['search_value_status']) && $_REQUEST['search_value_status'] != 0)
        {
        	if($_REQUEST['search_value_status'] == 1)
        	{
        		$status = 0;
        	}
        	elseif ($_REQUEST['search_value_status'] == 2)
        	{
        		$status = 1;
        	}
        	elseif ($_REQUEST['search_value_status'] == 3)
        	{
        		$status = 2;
        	}
        	else
        	{
        		$status = 3;
        	}
            $subsql .= " AND dm.dm_status =".$status;
        }
        else
        {
            $subsql .= " AND (dm.dm_status != 3)";
        }
        if(!empty($_REQUEST['search_value_start_date']) && !empty($_REQUEST['search_value_end_date']))
        {
            $start_date = date('Y-m-d', strtotime($_REQUEST['search_value_start_date']));
            $end_date = date('Y-m-d', strtotime($_REQUEST['search_value_end_date']));
            $subsql .= " AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'";
        }
        else
        {
            $subsql .= " AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'";
        }

        if(!empty($_REQUEST['search_value_from_dollar']) && !empty($_REQUEST['search_value_to_dollar']))
        {
            $subsql .= " AND dm.dm_dollar BETWEEN ".$_REQUEST['search_value_from_dollar']." AND ".$_REQUEST['search_value_to_dollar'];
        }

        if(!empty($_REQUEST['search_value_from_ppc']) && !empty($_REQUEST['search_value_to_ppc']))
        {
            $subsql .= " AND dm.dm_price_ct BETWEEN ".$_REQUEST['search_value_from_ppc']." AND ".$_REQUEST['search_value_to_ppc'];
        }

        if(!empty($_REQUEST['search_value_key2sym']))
        {

            $db_key2sym = $this->db_operations->get_record('diamond_master', ['dm_id'=>$_REQUEST['search_value_key2sym']]);
            if(!empty($db_key2sym))
            {
                $key2sym = $db_key2sym[0]['dm_key2sym'];
                $subsql .= " AND dm.dm_key2sym = '".$key2sym."'";
            }
        }

        if(!empty($_REQUEST['search_value_r2net']))
        {
            $dm_r2net = $_REQUEST['search_value_r2net'] == 1 ? 0 : 1;
            $subsql .= " AND dm.dm_r2net = ".$dm_r2net;
        }

        // if(!empty($_REQUEST['search_value_category']))
        // {
        //     $dm_r2net = $_REQUEST['search_value_category'] == 1 ? 0 : 1;
        //     $subsql .= " AND dm.dm_category = ".$dm_r2net;
        // }

        if(!empty($_REQUEST['search_value_suspense']))
        {
            $dm_suspense = $_REQUEST['search_value_suspense'] == 1 ? 0 : 1;
            $subsql .= " AND dm.dm_suspense = ".$dm_suspense;
        }

        if (isset($_REQUEST['search_value_category']) && $_REQUEST['search_value_category'] !== '') 
        {
            $cat = $_REQUEST['search_value_category'];

            // 2 = ALL → do not apply filter
            if ($cat != 2) {
                // CERTIFIED = 1 → dm_category = 1
                // NON-CERTIFIED = 0 → dm_category = 0
                $dm_category = ($cat == 1 ? 1 : 0);
                $subsql .= " AND dm.dm_category = ".$dm_category;
            }
        }

        if(isset($_REQUEST['submit']) && $_REQUEST['submit'] == 'excel')
        {
            $diamond_query = "SELECT dm.*
                FROM diamond_master dm
                WHERE 1 $subsql ORDER BY dm.dm_date_create DESC";
        }
        else
        {
            $diamond_query = "SELECT dm.*
                FROM diamond_master dm
                WHERE 1 $subsql ORDER BY dm.dm_date_create DESC LIMIT $per_page OFFSET $offset";

        }

        //  echo "<pre>"; print_r($diamond_query); exit;
        $record['diamond_data'] = $this->db->query($diamond_query)->result_array();

        foreach ($record['diamond_data'] as $key => $value)
        {
            $record['diamond_data'][$key]['is_exists'] = $this->db->query("
                SELECT prt.prt_id
                FROM purchase_trans prt
                WHERE prt.prt_dm_code = '".$value['dm_code']."'
            ")->num_rows();
        }

        // echo "<pre>"; print_r($record['diamond_data']); exit;

        /* Drop Down*/
        // stock id
        $stock_id_data = $this->db->query("SELECT dm.dm_id, dm.dm_code FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'")->result_array();
        $record['stock_id_drop_down'] = [];
        foreach ($stock_id_data as $key => $value)
        {
        	$record['stock_id_drop_down'][$value['dm_id']] = $value['dm_code'];
        }
        if (!empty($record['stock_id_drop_down'])) {
            asort($record['stock_id_drop_down']);
        }
        // stock id

        // shape
        $shape_data = $this->db->query("SELECT dm.dm_sh,dm.dm_id FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_sh ORDER BY FIELD(dm.dm_sh,'CUSHION MODIFIED','MARQUISE','OVAL','HEART','PRINCESS','EMERALD','PEAR','ROUND')desc")->result_array();
        $record['shape_drop_down'] = [];
        foreach ($shape_data as $key => $value)
        {
            $record['shape_drop_down'][$value['dm_id']] = $value['dm_sh'];
        }

        // shape

        // weight
        $weight_data = $this->db->query("SELECT dm.dm_id,dm.dm_wt FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_wt")->result_array();
        $weight_dataa = $this->db->query("SELECT MAX(dm.dm_wt) as max ,MIN(dm.dm_wt) as min FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'")->result_array();

           $a = 0.00;
           $b = 0.09 ;
           $data= array();
           for($i =0.10 ; $i<=$weight_dataa[0]['max']+0.20;)
           {
             $data[] = number_format($a,2). '-' .number_format($b,2);
             $a = $b + 0.01;
             $b = $b + 0.10;
             $i = $i + 0.10;
           }


        $record['weight_drop_down']= [];
        foreach ($data as $key => $value)
        {
            $record['weight_drop_down'][$value] = $value;

        }
        if (!empty($record['weight_drop_down'])) {
            asort($record['weight_drop_down']);
        }
        // weight

        // color
        $color_data = $this->db->query("SELECT dm.dm_id, dm.dm_col FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_col ORDER BY FIELD(dm.dm_col,'D','E','F','G','H','I','J','K','L')")->result_array();
        $record['color_drop_down'] = [];
        foreach ($color_data as $key => $value)
        {
            $record['color_drop_down'][$value['dm_id']] = $value['dm_col'];
        }

        // color

        // clarity
        $clarity_data = $this->db->query("SELECT dm.dm_id, dm.dm_cla FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_cla ORDER BY FIELD(dm.dm_cla,'FL','IF','VVS1','VVS2','VS1','SI1','VS2','SI2')")->result_array();
        $record['clarity_drop_down'] = [];
        foreach ($clarity_data as $key => $value)
        {
            $record['clarity_drop_down'][$value['dm_id']] = $value['dm_cla'];
        }

        // clarity

        // cut
        $cut_data = $this->db->query("SELECT dm.dm_id, dm.dm_cut FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_cut ORDER BY FIELD(dm.dm_cut,'ID','EX','VG','GD')")->result_array();
        $record['cut_drop_down'] = [];
        foreach ($cut_data as $key => $value)
        {
            $record['cut_drop_down'][$value['dm_id']] = $value['dm_cut'];
        }
        // cut

        // pol
        $pol_data = $this->db->query("SELECT dm.dm_id, dm.dm_pol FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_pol ORDER BY FIELD(dm.dm_pol,'EX','VG','G','GD','FR')")->result_array();
        $record['pol_drop_down'] = [];
        foreach ($pol_data as $key => $value)
        {
            $record['pol_drop_down'][$value['dm_id']] = $value['dm_pol'];
        }
        // pol

        // sym
        $sym_data = $this->db->query("SELECT dm.dm_id, dm.dm_sym FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'GROUP BY dm.dm_sym ORDER BY FIELD(dm.dm_sym,'EX','VG','G','GD','FR')")->result_array();
        $record['sym_drop_down'] = [];
        foreach ($sym_data as $key => $value)
        {
            $record['sym_drop_down'][$value['dm_id']] = $value['dm_sym'];
        }
        // sym

        // fl
        $fl_data = $this->db->query("SELECT dm.dm_id, dm.dm_fl FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_fl ORDER BY FIELD(dm.dm_fl,'N','F','VSL','M','STR')")->result_array();
        $record['fl_drop_down'] = [];
        foreach ($fl_data as $key => $value)
        {
            $record['fl_drop_down'][$value['dm_id']] = $value['dm_fl'];
        }
        // fl

        // lab
        $lab_data = $this->db->query("SELECT dm.dm_id, dm.dm_lab FROM diamond_master dm WHERE 1 AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_lab")->result_array();
        $record['lab_drop_down'] = [];
        foreach ($lab_data as $key => $value)
        {
            $record['lab_drop_down'][$value['dm_id']] = $value['dm_lab'];
        }
        if (!empty($record['lab_drop_down'])) {
            ksort($record['lab_drop_down']);
        }
        // lab

        // key2sym
        $key2sym_data = $this->db->query("SELECT dm.dm_id, dm.dm_key2sym FROM diamond_master dm WHERE dm.dm_key2sym != '' AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."' GROUP BY dm.dm_key2sym")->result_array();
        $record['key2sym_drop_down'][0] = 'ALL';
        foreach ($key2sym_data as $key => $value)
        {
            $record['key2sym_drop_down'][$value['dm_id']] = $value['dm_key2sym'];
        }


        
    if (isset($_REQUEST['submit']) && $_REQUEST['submit'] == 'excel') {

    $selected_category = $_REQUEST['search_value_category'] ?? '2'; // 0=NON-CERTIFIED, 1=CERTIFIED, 2=ALL

    // Base headers
    $excel_array[0] = [
        'SR NO','DATE','STOCK ID','PRICE/CT','PC AMT','DOLLAR/CT','DOLLAR AMT',
        'SIZE','PCS','CPD','SHAPE','LAB','CERT. NO.','CARAT','COL','CLAR.',
        'CUT','POL','SYM','FLUR.','LENGTH','WIDTH','HEIGHT','NAT/LAB GROWN',
        'DISC','RAP','DIAMOND IMAGE AND VIDEO','STATUS','UPLOADED DATE','NO OF DAYS'
    ];

    // Certified headers (only if user selected CERTIFIED or ALL)
    $certified_fields = [
        'Location','DNA','Rap Dollar','Tbl','TD %','L/W','BIC','BIS','WIC','Col Tinge',
        'LUS','Remarks','H/A','Girdle','Girdle %','Girdle Condition','CA','CH','PA',
        'PD','Str Ln','LR Half','Culet','KTS','Rep Com','Cert Date','Image Link','Certificate Link'
    ];

    if ($selected_category == '1' || $selected_category == '2') {
        $excel_array[0] = array_merge($excel_array[0], $certified_fields);
    }

    $wt_total = 0;
    $total_amount = 0;
    $total_discount = 0;
    $total_cert = 0;
    $total_rap = 0;
    $count = 1;

    foreach ($record['diamond_data'] as $key => $value) {

        // Determine status
        $status = match($value['dm_status']) {
            1 => 'Issue',
            2 => 'Hold',
            3 => 'Sold',
            default => 'Available'
        };

        // Calculate days since uploaded
        $uploaded_date = strtotime($value['dm_date_create']);
        $current_date = time();
        $nod = round(($current_date - $uploaded_date) / (60*60*24));

        $wt_total += $value['dm_wt'];
        $total_amount += $value['dm_dollar'];
        $total_discount += $value['dm_disc'];
        $total_cert += $value['dm_rap'] * (100 + $value['dm_disc']) / 100;
        $total_rap += $value['dm_rap'];

        // Base row
        $row = [
            $key + 1,
            !empty($value['dm_date']) ? date('d-m-Y', strtotime($value['dm_date'])) : '',
            $value['dm_code'] ?? '',
            $value['dm_price_ct'] ?? '',
            $value['dm_pc_amt'] ?? '',
            $value['dm_dollar'] ?? '',
            $value['dm_dollar_amt'] ?? '',
            $value['dm_size'] ?? '',
            $value['dm_pcs'] ?? '',
            $value['dm_cpd'] ?? '',
            $value['dm_sh'] ?? '',
            $value['dm_lab'] ?? '',
            $value['dm_cert_no'] ?? '',
            $value['dm_carat'] ?? '',
            $value['dm_col'] ?? '',
            $value['dm_cla'] ?? '',
            $value['dm_cut'] ?? '',
            $value['dm_pol'] ?? '',
            $value['dm_sym'] ?? '',
            $value['dm_fl'] ?? '',
            $value['dm_len'] ?? '',
            $value['dm_wid'] ?? '',
            $value['dm_hei'] ?? '',
            $value['dm_nat_lab_grown'] ?? '',
            $value['dm_disc'] ?? '',
            $value['dm_rap'] ?? '',
            $value['dm_video_link'] ?? '',
            $status,
            $value['dm_date_create'] ?? '',
            $nod
        ];

        // Add certified fields only if row is certified AND user selected CERTIFIED/ALL
        if (($selected_category == '1' || $selected_category == '2') && ($value['dm_category'] ?? 0) != 0) {
            $extra_fields = [
                $value['dm_location'] ?? '',
                $value['dm_dna'] ?? '',
                $value['dm_rap_dollar'] ?? '',
                $value['dm_tbl'] ?? '',
                $value['dm_td_percentage'] ?? '',
                $value['dm_l_w'] ?? '',
                $value['dm_bic'] ?? '',
                $value['dm_bis'] ?? '',
                $value['dm_wic'] ?? '',
                $value['dm_wis'] ?? '',
                $value['dm_col_tinge'] ?? '',
                $value['dm_lus'] ?? '',
                $value['dm_remarks'] ?? '',
                $value['dm_h_a'] ?? '',
                $value['dm_girdle'] ?? '',
                $value['dm_girdle_percentage'] ?? '',
                $value['dm_girdle_cond'] ?? '',
                $value['dm_ca'] ?? '',
                $value['dm_ch'] ?? '',
                $value['dm_pa'] ?? '',
                $value['dm_pd'] ?? '',
                $value['dm_str_ln'] ?? '',
                $value['dm_lr_half'] ?? '',
                $value['dm_culet'] ?? '',
                $value['dm_kts'] ?? '',
                $value['dm_rep_com'] ?? '',
                !empty($value['dm_cert_date']) ? date('Y-m-d', strtotime($value['dm_cert_date'])) : '',
                $value['dm_image_link'] ?? '',
                $value['dm_certificate_link'] ?? ''
            ];
            $row = array_merge($row, $extra_fields);
        }

        $excel_array[$key + 1] = $row;
        $count++;
    }

    // Totals row
    // $tot_price = $total_cert * round($wt_total);
    // $tot_rap = $total_rap * round($wt_total);
    // $tot_disc = (!empty($tot_rap) && $tot_rap != 0) ? (1 - ($tot_price / $tot_rap)) * 100 : 0;

    // $tot_row = array_fill(0, count($excel_array[0]), '');
    // $tot_row[2] = 'TOTAL';
    // $tot_row[3] = $wt_total;
    // $tot_row[11] = '-' . $tot_disc;
    // $tot_row[16] = $total_amount;

    // $excel_array[$count] = $tot_row;

    $record['excel_array'] = $excel_array;
}


        return $record;

    }



    public function diamond_master_excel()
    {
        $subsql = "";
        $start_date = date('Y-06-01');
        $end_date   = date('Y-m-d');

        $form_data = $this->input->post();
        // echo "<pre>";print_r($form_data);exit();


        if(isset($form_data['search_value_stock_id']))
        {
            $stock_id = implode(',', $form_data['search_value_stock_id']);
            // echo "<pre>"; print_r($stock_id); exit;
            $subsql .= " AND dm.dm_id IN (".$stock_id. ")";
        }
        if(isset($form_data['search_value_sh']))
        {
            $sh = '';
            foreach ($form_data['search_value_sh'] as $key => $value)
            {
                $db_sh = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_sh'];
                $sh .= "'".$db_sh."',";
            }
            $sh = rtrim($sh, ',');
            // echo "<pre>"; print_r($sh); exit;
            $subsql .= " AND dm.dm_sh IN (".$sh. ")";
        }
        if(isset($form_data['search_value_wt']))
        {
            $wt = implode(',', $form_data['search_value_wt']);
            // echo "<pre>"; print_r($stock_id); exit;
            $subsql .= " AND dm.dm_wt IN (".$wt. ")";
        }
        if(isset($form_data['search_value_col']))
        {
            $col = '';
            foreach ($form_data['search_value_col'] as $key => $value)
            {
                $db_col = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_col'];
                $col .= "'".$db_col."',";
            }
            $col = rtrim($col, ',');
            // echo "<pre>"; print_r($col); exit;
            $subsql .= " AND dm.dm_col IN (".$col. ")";
        }
        if(!empty($_REQUEST['search_value_cla']))
        {
            $cla = '';
            foreach ($_REQUEST['search_value_cla'] as $key => $value)
            {
                $db_cla = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_cla'];
                $cla .= "'".$db_cla."',";
            }
            $cla = rtrim($cla, ',');
            // echo "<pre>"; print_r($cla); exit;
            $subsql .= " AND dm.dm_cla IN (".$cla. ")";
        }
        if(!empty($_REQUEST['search_value_cut']))
        {
            $cut = '';
            foreach ($_REQUEST['search_value_cut'] as $key => $value)
            {
                $db_cut = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_cut'];
                $cut .= "'".$db_cut."',";
            }
            $cut = rtrim($cut, ',');
            // echo "<pre>"; print_r($cut); exit;
            $subsql .= " AND dm.dm_cut IN (".$cut. ")";
        }
        if(!empty($_REQUEST['search_value_pol']))
        {
            $pol = '';
            foreach ($_REQUEST['search_value_pol'] as $key => $value)
            {
                $db_pol = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_pol'];
                $pol .= "'".$db_pol."',";
            }
            $pol = rtrim($pol, ',');
            // echo "<pre>"; print_r($pol); exit;
            $subsql .= " AND dm.dm_pol IN (".$pol. ")";
        }
        if(!empty($_REQUEST['search_value_sym']))
        {
            $sym = '';
            foreach ($_REQUEST['search_value_sym'] as $key => $value)
            {
                $db_sym = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_sym'];
                $sym .= "'".$db_sym."',";
            }
            $sym = rtrim($sym, ',');
            // echo "<pre>"; print_r($sym); exit;
            $subsql .= " AND dm.dm_sym IN (".$sym. ")";
        }
        if(!empty($_REQUEST['search_value_fl']))
        {
            $fl = '';
            foreach ($_REQUEST['search_value_fl'] as $key => $value)
            {
                $db_fl = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_fl'];
                $fl .= "'".$db_fl."',";
            }
            $fl = rtrim($fl, ',');
            // echo "<pre>"; print_r($fl); exit;
            $subsql .= " AND dm.dm_fl IN (".$fl. ")";
        }
        if(!empty($_REQUEST['search_value_lab']))
        {
            $lab = '';
            foreach ($_REQUEST['search_value_lab'] as $key => $value)
            {
                $db_lab = $this->db_operations->get_record('diamond_master', ['dm_id'=>$value])[0]['dm_lab'];
                $lab .= "'".$db_lab."',";
            }
            $lab = rtrim($lab, ',');
            // echo "<pre>"; print_r($lab); exit;
            $subsql .= " AND dm.dm_lab IN (".$lab. ")";
        }
        if(!empty($_REQUEST['search_value_status']) && $_REQUEST['search_value_status'] != 0)
        {
            if($_REQUEST['search_value_status'] == 1)
            {
                $status = 0;
            }
            elseif ($_REQUEST['search_value_status'] == 2)
            {
                $status = 1;
            }
            elseif ($_REQUEST['search_value_status'] == 3)
            {
                $status = 2;
            }
            else
            {
                $status = 3;
            }
            $subsql .= " AND dm.dm_status =".$status;
        }
        // else
        // {
        //     $subsql .= " AND (dm.dm_status = 0)";
        // }
        if(!empty($_REQUEST['search_value_start_date']) && !empty($_REQUEST['search_value_end_date']))
        {
            $start_date = date('Y-m-d', strtotime($_REQUEST['search_value_start_date']));
            $end_date = date('Y-m-d', strtotime($_REQUEST['search_value_end_date']));
            $subsql .= " AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'";
        }
        else
        {
            $subsql .= " AND DATE_FORMAT(dm.dm_date_create, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'";
        }

        if(!empty($_REQUEST['search_value_from_dollar']) && !empty($_REQUEST['search_value_to_dollar']))
        {
            $subsql .= " AND dm.dm_dollar BETWEEN ".$_REQUEST['search_value_from_dollar']." AND ".$_REQUEST['search_value_to_dollar'];
        }

        if(!empty($_REQUEST['search_value_from_ppc']) && !empty($_REQUEST['search_value_to_ppc']))
        {
            $subsql .= " AND dm.dm_price_ct BETWEEN ".$_REQUEST['search_value_from_ppc']." AND ".$_REQUEST['search_value_to_ppc'];
        }


        $diamond_query = "SELECT dm.*
                FROM diamond_master dm
                WHERE 1 $subsql ORDER BY dm.dm_date_create DESC";
        // echo "<pre>"; print_r($diamond_query); exit;
        $record['diamond_data'] = $this->db->query($diamond_query)->result_array();

        $excel_array[0] = array(
                0 =>  'SR NO',
                1 =>  'DATE',
                2 =>  'STOCK ID',
                3 =>  'PRICE/CT',
                4 =>  'PC AMT',
                5 =>  'DOLLAR/CT',
                6 =>  'DOLLAR AMT',
                7 =>  'SIZE',
                8 =>  'PCS',
                9 =>  'CPD',
                10 =>  'SHAPE',
                11 =>  'LAB',
                12 =>  'CERT. NO.',
                13 =>  'CARAT',
                14 =>  'COL',
                15 =>  'CLAR.',
                16 =>  'CUT',
                17 =>  'POL',
                18 =>  'SYM',
                19 =>  'FLUR.',
                20 =>  'LENGTH',
                // 13 =>  'TOTAL DOLLAR',
                21 =>  'WIDTH',
                22 =>  'HEIGHT',
                23 =>  'NAT/LAB GROWN',
                24 =>  'DISC',
                25 =>  'RAP',
                26 =>  'DIAMOND IMAGE AND VIDEO',
                27 =>  'STATUS',
                28 =>  'UPLOADED DATE',
                29 =>  'NO OF DAYS'
            );
            foreach ($record['diamond_data'] as $key => $value)
            {
                $status = '';
                if($value['dm_status'] == 1)
                {
                    $status = 'Issue';
                }
                else if($value['dm_status'] == 2)
                {
                    $status = 'Hold';
                }
                else if($value['dm_status'] == 3)
                {
                    $status = 'Sold';
                }
                else
                {
                    $status = 'Available';
                }
                $uploaded_date  = strtotime($value['dm_date_create']);
                $current_date   = time();
                $nod            = $current_date - $uploaded_date;
                $nod            = round($nod/(60*60*24));
                /*excel_array*/
               $excel_array[$key+1][0] = $key+1;
               $excel_array[$key+1][1] = !empty($value['dm_date']) ? date('d-m-Y', strtotime($value['dm_date'])) : '';
               $excel_array[$key+1][2] = $value['dm_code'];
               $excel_array[$key+1][3] = $value['dm_price_ct'];
               $excel_array[$key+1][4] = $value['dm_pc_amt'];
               $excel_array[$key+1][5] = $value['dm_dollar'];
               $excel_array[$key+1][6] = $value['dm_dollar_amt'];
               $excel_array[$key+1][7] = $value['dm_size'];
               $excel_array[$key+1][8] = $value['dm_pcs'];
               $excel_array[$key+1][9] = $value['dm_cpd'];
               $excel_array[$key+1][10] = $value['dm_sh'];
               $excel_array[$key+1][11] = $value['dm_lab'];
               $excel_array[$key+1][12] = $value['dm_cert_no'];
               $excel_array[$key+1][13] = $value['dm_carat'];
               $excel_array[$key+1][14] = $value['dm_col'];
               $excel_array[$key+1][15] = $value['dm_cla'];
               $excel_array[$key+1][16] = $value['dm_cut'];
               $excel_array[$key+1][17] = $value['dm_pol'];
               $excel_array[$key+1][18] = $value['dm_sym'];
               $excel_array[$key+1][19] = $value['dm_fl'];
               $excel_array[$key+1][20] = $value['dm_len'];
               $excel_array[$key+1][21] = $value['dm_wid'];
               $excel_array[$key+1][22] = $value['dm_hei'];
               $excel_array[$key+1][23] = $value['dm_nat_lab_grown'];
               $excel_array[$key+1][24] = $value['dm_disc'];
               $excel_array[$key+1][25] = $value['dm_rap'];
               $excel_array[$key+1][26] = $value['dm_video_link'];
               $excel_array[$key+1][27] = $status;
               $excel_array[$key+1][28] = $value['dm_date_create'];
               $excel_array[$key+1][29] = $nod;
            }
            // echo "<pre>"; print_r($excel_array); exit();
        return $excel_array;

    }
    function get_challan_data($dm_id, $ct_id)
    {
        $query = "SELECT
                        cm.cm_entry_no, DATE_FORMAT(cm.cm_entry_date, '%d-%m-%Y') as cm_entry_date, cm.cm_through,
                        cust.acc_name as customer, brk.acc_name as broker
                    FROM
                        challan_trans ct
                    LEFT JOIN
                        challan_master cm ON(cm.cm_id = ct.ct_cm_id)
                    LEFT JOIN
                        account_master cust ON(cust.acc_id = cm.cm_cust_id)
                    LEFT JOIN
                        account_master brk ON(brk.acc_id = cm.cm_broker_id)
                    WHERE
                        ct.ct_id = $ct_id
                    AND
                        ct.ct_dm_id = $dm_id
                ";
        $data = $this->db->query($query)->result_array();

        return $data;
    }
    function get_challan_return_data($dm_id, $crt_id)
    {
        $query = "SELECT
                        crm.crm_entry_no, DATE_FORMAT(crm.crm_entry_date, '%d-%m-%Y') as crm_entry_date, crm.crm_through,
                        cust.acc_name as customer, brk.acc_name as broker
                    FROM
                        challan_return_trans crt
                    LEFT JOIN
                        challan_return_master crm ON(crm.crm_id = crt.crt_crm_id)
                    LEFT JOIN
                        account_master cust ON(cust.acc_id = crm.crm_cust_id)
                    LEFT JOIN
                        account_master brk ON(brk.acc_id = crm.crm_broker_id)
                    WHERE
                        crt.crt_id = $crt_id
                    AND
                        crt.crt_dm_id = $dm_id
                ";
        $data = $this->db->query($query)->result_array();

        return $data;
    }




/*/diamond master*/


/************************ Rapaport MASTER *******************************/

    public function check_duplicate_rapaport($diam_type,$clarity,$rng_frm,$rng_to,$clr,$res)
    {
        $query = "SELECT * from rapaport_master WHERE rp_diamond_type = '$diam_type' AND rp_clarity = '$clarity' AND rp_range_from = '$rng_frm' AND rp_range_to = '$rng_to' AND rp_color = '$clr' AND rp_result = '$res'";

        return $this->db->query($query)->num_rows();
    }

    public function check_edit_duplicate_rapaport($diam_type,$clarity,$rng_frm,$rng_to,$clr,$res,$rap_id)
    {
        $query = "SELECT * from rapaport_master WHERE rp_diamond_type = '$diam_type' AND rp_clarity = '$clarity' AND rp_range_from = '$rng_frm' AND rp_range_to = '$rng_to' AND rp_color = '$clr' AND rp_result = '$res' AND rp_id != '$rap_id'";

        return $this->db->query($query)->num_rows();
    }

    public function get_rapaport_data_count()
    {
        $subsql = "";
        if(!empty($_GET['search_value_type']))
        {
            $subsql .= " AND rm.rp_diamond_type =".$_GET['search_value_type'];
        }
        if(!empty($_GET['search_value_clarity']))
        {
            $clarity_data = $this->db_operations->get_record('rapaport_master', ['rp_id' => $_GET['search_value_clarity']]);

            if(!empty($clarity_data))
            {
                $clarity = $clarity_data[0]['rp_clarity'];
                $subsql .= " AND rm.rp_clarity ='".$clarity."'";
            }
        }
        if(!empty($_GET['search_value_color']))
        {
            $color_data = $this->db_operations->get_record('rapaport_master', ['rp_id' => $_GET['search_value_color']]);
            if(!empty($color_data))
            {
                $color = $color_data[0]['rp_color'];
                $subsql .= " AND rm.rp_color ='".$color."'";
            }
        }

        if(!empty($_GET['search_value_range_from']))
        {
            $subsql .= " AND rm.rp_range_from >=".$_GET['search_value_range_from'];
        }
        if(!empty($_GET['search_value_range_to']))
        {
            $subsql .= " AND rm.rp_range_to <=".$_GET['search_value_range_to'];
        }

        $query = "SELECT rm.*
                    FROM rapaport_master rm
                    WHERE 1 $subsql
                ";
        return $this->db->query($query)->num_rows();
    }

    public function get_rapaport_data($per_page, $offset)
    {
        $subsql = "";

            if(!empty($_GET['search_value_type']))
            {
                $subsql .= " AND rm.rp_diamond_type =".$_GET['search_value_type'];
            }
            if(!empty($_GET['search_value_clarity']))
            {
                $clarity_data = $this->db_operations->get_record('rapaport_master', ['rp_id' => $_GET['search_value_clarity']]);
                if(!empty($clarity_data))
                {
                    $clarity = $clarity_data[0]['rp_clarity'];
                    $subsql .= " AND rm.rp_clarity ='".$clarity."'";
                }
            }
            if(!empty($_GET['search_value_color']))
            {
                $color_data = $this->db_operations->get_record('rapaport_master', ['rp_id' => $_GET['search_value_color']]);
                if(!empty($color_data))
                {
                    $color = $color_data[0]['rp_color'];
                    $subsql .= " AND rm.rp_color ='".$color."'";
                }
            }
            if(!empty($_GET['search_value_range_from']))
            {
                $subsql .= " AND rm.rp_range_from >=".$_GET['search_value_range_from'];
            }
            if(!empty($_GET['search_value_range_to']))
            {
                $subsql .= " AND rm.rp_range_to <=".$_GET['search_value_range_to'];
            }

            $rapaport_query = "SELECT rm.*
                        FROM rapaport_master rm
                        WHERE 1
                        $subsql
                        ORDER BY rm.rp_id DESC
                        LIMIT $per_page
                        OFFSET $offset
                    ";
            // echo "<pre>"; print_r($rapaport_query); exit;
            $record['rapaport_data'] = $this->db->query($rapaport_query)->result_array();

            // type
            $record['type_drop_down'][0] = 'ALL';
            $record['type_drop_down'][1] = 'ROUND';
            $record['type_drop_down'][2] = 'PEARS';

            // type

            $clarity_drop_down_query = "SELECT rm.rp_id, rm.rp_clarity
                                FROM rapaport_master rm
                                WHERE 1
                                GROUP BY rm.rp_clarity
                                ";
            $clarity_drop_down = $this->db->query($clarity_drop_down_query)->result_array();
            // clarity
            $record['clarity_drop_down'][0] = 'ALL';
            foreach ($clarity_drop_down as $key => $value)
            {
                $record['clarity_drop_down'][$value['rp_id']] = $value['rp_clarity'];
            }
            // clarity


             $color_drop_down_query = "SELECT rm.rp_id, rm.rp_color
                                FROM rapaport_master rm
                                WHERE 1
                                GROUP BY rm.rp_color
                                ";
            $color_drop_down = $this->db->query($color_drop_down_query)->result_array();
            // color
            $record['color_drop_down'][0] = 'ALL';
            foreach ($color_drop_down as $key => $value)
            {
                $record['color_drop_down'][$value['rp_id']] = $value['rp_color'];
            }
            // color

            return $record;
    }

    public function insert_data($data)
    {
        $this->db->truncate('rapaport_master');
        $this->db->insert_batch('rapaport_master', $data);
    }


/************************ /Rapaport MASTER *******************************/





	}
?>