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

	class salesmdl extends CI_model{

		public function get_cnt($table, $arr){

			$this->db->where($arr);
			return $this->db->count_all_results($table);
		}
		public function get_max_id($table, $field)
		{
		    $this->db->select_max($field, 'max_id');
		    $record = $this->db->get($table)->result_array();

		    if(empty($record[0]['max_id']))
		    {
		       return 1;
		    }
		    else
		    {
		      return $record[0]['max_id']+1;
		    }
		}

		public function get_all_sales_data_cnt()
		{
		    $subsql = "";
        
            if(!empty($_GET['entry_no']))
            {
                $subsql .= " AND sale.sale_id =".$_GET['entry_no'];
            }

            if(!empty($_GET['entry_date']))
            {
                $entry_date = date('Y-m-d', strtotime($_GET['entry_date']));
                $subsql .= " AND sale.sale_entry_date = '$entry_date'";
            }

            if(!empty($_GET['cust_id']))
            {
                $subsql .= " AND sale.sale_customer_id =".$_GET['cust_id'];
            }
            
			return $this->db->query("SELECT sale.*, am.*, am.acc_owner, am.acc_mobile, am.acc_email, sm.state_name, cm.city_name FROM sales_master sale 
	    		LEFT JOIN account_master am ON(am.acc_id = sale.sale_customer_id)
	    		LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
	    		LEFT JOIN city_master cm ON(cm.city_id = am.acc_city_id) WHERE 1 $subsql")->num_rows();
		}

		public function get_all_sales_data($per_page, $offset)
		{
			$subsql = "";
        
            if(!empty($_GET['entry_no']))
            {
                $subsql .= " AND sale.sale_id =".$_GET['entry_no'];
            }

            if(!empty($_GET['entry_date']))
            {
                $entry_date = date('Y-m-d', strtotime($_GET['entry_date']));
                $subsql .= " AND sale.sale_entry_date = '$entry_date'";
            }

            if(!empty($_GET['cust_id']))
            {
                $subsql .= " AND sale.sale_customer_id =".$_GET['cust_id'];
            }

			  $record['sales_data'] = $this->db->query("
        SELECT 
            sale.*,
            am.acc_name, am.acc_mobile, am.acc_email, 
            sm.state_name, cm.city_name,
            COALESCE(SUM(st.st_price_ct), 0) AS total_price_ct,
            COALESCE(SUM(st.st_pc_amt), 0) AS total_pc_amt,
            COALESCE(SUM(st.st_carat), 0) AS total_qty,
            COALESCE(SUM(st.st_total_amt), 0) AS total_amt,
			COALESCE(SUM(st.st_dollar), 0) AS total_dollar,
			COALESCE(SUM(st.st_dollar_amt), 0) AS total_dollar_amt
        FROM sales_master sale
        LEFT JOIN account_master am ON(am.acc_id = sale.sale_customer_id)
        LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
        LEFT JOIN city_master cm ON(cm.city_id = am.acc_city_id)
        LEFT JOIN sales_trans st ON(st.st_sale_id = sale.sale_id)
        WHERE 1 $subsql
        GROUP BY sale.sale_id
        ORDER BY sale.sale_id DESC
        LIMIT $per_page OFFSET $offset
    ")->result_array();

			$challan_data = $this->db->query("SELECT sale.sale_id, sale.sale_entry_no FROM sales_master sale")->result_array();
            // entry no
            $record['entry_no_drop_down'][0] = 'ALL';
            foreach ($challan_data as $key => $value) 
            {
            	$record['entry_no_drop_down'][$value['sale_id']] = $value['sale_entry_no'];
            }

			$account_data = $this->db->query("SELECT * FROM account_master WHERE acc_grp_id = 5")->result_array();
            $record['account_data'][0] = "PLEASE SELECT";
            foreach ($account_data as $key => $value) {
                $record['account_data'][$value['acc_id']] = $value['acc_owner'];
            }

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

			return $record;
		}
		
		public function get_sales_form_assets()
	    {

	        $users = $this->db->query("SELECT acc_id, Upper(acc_name) as acc_owner FROM account_master WHERE acc_grp_id = 5 ORDER BY acc_owner ASC ")->result_array();
	        $record['user'][0] = "PLEASE SELECT";
	        foreach ($users as $key => $value) {
	        	$record['user'][$value['acc_id']] = $value['acc_owner'];
	        }

	        $record['entry_no'] = $this->get_max_id('sales_master', 'sale_entry_no');

			$record['entry_no_unregi'] = $this->get_max_id('sales_master', 'sale_unregi_entry_no');

			$record['bill_no'] = $this->get_max_id('sales_master', 'sale_bill_no');

            $current_dollar_rate = $this->db->query("
                SELECT exchange_rate_selling_rate
                FROM exchange_rate_master
                WHERE exchange_rate_date = '".date('Y-m-d')."'
            ")->result_array();

            $record['current_dollar_rate'] = !empty($current_dollar_rate) ? $current_dollar_rate[0]['exchange_rate_selling_rate'] : '';

	        // diamond drop down
            $diamond_query = "SELECT dm.* FROM diamond_master dm WHERE dm.dm_status = 0 OR dm.dm_status = 4";
            $diamond_data = $this->db->query($diamond_query)->result_array();
            $record['diamond_data'][0] = 'PLEASE SELECT';
            foreach ($diamond_data as $key => $value) 
            {
                $record['diamond_data'][$value['dm_id']] = strtoUpper($value['dm_code']);
            }
            // diamond drop down 

			// hsn drop down
            $hsn_query = "SELECT hsn.* FROM hsn_master hsn";
            $hsn_data = $this->db->query($hsn_query)->result_array();
            $record['hsn_data'][0] = 'PLEASE SELECT';
            foreach ($hsn_data as $key => $value) 
            {
                $record['hsn_data'][$value['hsn_id']] = strtoUpper($value['hsn_code']);
            }
            // hsn drop down

	        return $record;
	    }

	    public function get_single_sales_data_for_edit($id)
	    {
	    	$users = $this->db->query("SELECT acc_id, Upper(acc_name) as acc_owner FROM account_master WHERE acc_grp_id = 5 ORDER BY acc_owner ASC ")->result_array();
	        $record['user'][0] = "PLEASE SELECT";
	        foreach ($users as $key => $value) {
	        	$record['user'][$value['acc_id']] = $value['acc_owner'];
	        }

	        $record['entry_no'] = $this->get_max_id('sales_master', 'sale_entry_no');

			$record['entry_no_unregi'] = $this->get_max_id('sales_master', 'sale_unregi_entry_no');

			$record['bill_no'] = $this->get_max_id('sales_master', 'sale_bill_no');

	         // diamond drop down
            $diamond_query = "SELECT dm.* FROM diamond_master dm WHERE dm.dm_status = 0 OR dm.dm_status = 4";
            $diamond_data = $this->db->query($diamond_query)->result_array();
            $record['diamond_data'][0] = 'PLEASE SELECT';
            foreach ($diamond_data as $key => $value) 
            {
                $record['diamond_data'][$value['dm_id']] = strtoUpper($value['dm_code']);
            }
            // diamond drop down 

			// hsn drop down
            $hsn_query = "SELECT hsn.* FROM hsn_master hsn";
            $hsn_data = $this->db->query($hsn_query)->result_array();
            $record['hsn_data'][0] = 'PLEASE SELECT';
            foreach ($hsn_data as $key => $value) 
            {
                $record['hsn_data'][$value['hsn_id']] = strtoUpper($value['hsn_code']);
            }
            // hsn drop down
			$user_id = $_SESSION['user_id'];

	    	$record['sales_data'] = $this->db->query("SELECT sale.sale_id, sale.sale_type, sale.pur_with_registered, sale.sale_entry_no, sale.sale_unregi_entry_no, sale.sale_dispatch_by, sale.sale_invoice_date, sale.sale_entry_date, sale.sale_customer_id,sale.sale_shipping_id,sale.sale_bill_no, sale.sale_bill_date, sale.sale_buyer_order_no, sale.sale_buyer_order_date, 
            sale.sale_delivery_note, sale.sale_delivery_note_date, sale.sale_dispatch_doc_no, sale.sale_dispatched_through, sale.sale_destination, sale.sale_terms_of_delivery, sale.sale_current_dollar_rate, sale.sale_acc_type, sale_total_cgst, sale_total_sgst, sale_total_igst, sale_total_qty, sale_total_rate, sale_sub_total_amt, sale_total_amt, sale.sale_terms, am.acc_id, am.acc_owner, am.acc_mobile, am.acc_email,shipping.acc_owner, shipping.acc_mobile, sm.state_name, cm.city_name, $user_id as user_id FROM sales_master sale 
	    		LEFT JOIN account_master am ON(am.acc_id = sale.sale_customer_id)
                LEFT JOIN account_master shipping ON(shipping.acc_id = sale.sale_shipping_id)
	    		LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
	    		LEFT JOIN city_master cm ON(cm.city_id = am.acc_city_id)
	    		WHERE sale_id = $id")->result_array();

	    	$record['sales_trans'] = $this->db->query("SELECT st.*, st.st_id, st_dm_id, st_pc_amt, st_qty, st_amt_cgst, st_amt_sgst, st_amt_igst, st_per_cgst, st_per_sgst, st_per_igst, st_sub_total_amt, st_total_amt, st_hsn_id, hsn.hsn_id, hsn.hsn_code, dm.dm_stock_in, dm.dm_stock_out
	    		FROM sales_trans st 
	    		LEFT JOIN hsn_master hsn ON(hsn.hsn_id = st.st_hsn_id)
				LEFT JOIN diamond_master dm ON(dm.dm_code = st.st_dm_code)
	    		WHERE st_sale_id = $id")->result_array();

			$query1="SELECT 
                pam.cam_id as sales_cam_id, 
                pam.cam_path as sales_cam_path
                FROM sales_master sale
                LEFT JOIN sales_attachment_master pam ON(pam.cam_sale_id = sale.sale_id)
                WHERE sale.sale_id = $id";
        	$record['sales_attachment_data'] = $this->db->query($query1)->result_array();

	    	return $record;
	    }

	    public function get_diamond_data($dm_id, $cust_id)
		{
			// 1️⃣ Get diamond + sales transaction data
			$diamond_query = "
				SELECT dm.*, st.st_id, st.st_sale_id
				FROM diamond_master dm
				LEFT JOIN sales_trans st ON st.st_dm_id = dm.dm_id
				WHERE dm.dm_id = $dm_id
				ORDER BY st.st_id DESC
				LIMIT 1
			";
			$diamond_data = $this->db->query($diamond_query)->row_array();

			if (!$diamond_data) {
				return [];
			}

			// 2️⃣ Get customer data separately
			$customer_query = "
				SELECT customer.acc_id, customer.acc_owner, customer.acc_type
				FROM account_master customer
				WHERE customer.acc_id = $cust_id
			";

			$customer_data = $this->db->query($customer_query)->row_array();

			// 3️⃣ Merge customer info into diamond data
			if ($customer_data) {
				$diamond_data = array_merge($diamond_data, $customer_data);
			}

			// Optional debug
			// echo "<pre>"; print_r($diamond_data); exit;

			return $diamond_data;
		}


	    public function get_single_sales_data_for_print($id)
	    { 
            $user_id = $_SESSION['user_id'];

	    	$record['sales_data'] = $this->db->query("SELECT sale.*, am.acc_id, am.acc_name, am.acc_mobile, am.acc_email, am.acc_address, am.acc_gst, am.acc_pan, am.acc_days, am.acc_discount, sm.state_name as acc_state, cm.city_name as acc_city, 
            shipping.acc_email as shipping_email,shipping.acc_name as shipping_name, shipping.acc_mobile as shipping_mobile, shipping.acc_address as shipping_address, shipping.acc_gst as shipping_gst, shipping.acc_pan as shipping_pan, shipping_state.state_name as shipping_state, shipping_city.city_name as shipping_city,
             $user_id as user_id 
             FROM sales_master sale 
	    		LEFT JOIN account_master am ON(am.acc_id = sale.sale_customer_id)
                LEFT JOIN account_master shipping ON(shipping.acc_id = sale.sale_shipping_id)
	    		LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
	    		LEFT JOIN city_master cm ON(cm.city_id = am.acc_city_id)
                LEFT JOIN state_master shipping_state ON(shipping_state.state_id = am.acc_state_id)
	    		LEFT JOIN city_master shipping_city ON(shipping_city.city_id = am.acc_city_id)
	    		WHERE sale_id = $id")->result_array();

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


	    	$record['sales_trans'] = $this->db->query("SELECT st.*, st.st_id, st_dm_id, st_pc_amt, st_qty, st_amt_cgst, st_amt_sgst, st_amt_igst, st_per_cgst, st_per_sgst, st_per_igst, st_sub_total_amt, st_total_amt, st_hsn_id, hsn.hsn_id, hsn.hsn_code, UPPER(hsn.hsn_description) as hsn_description, dm.dm_stock_in, dm.dm_stock_out
	    		FROM sales_trans st 
	    		LEFT JOIN hsn_master hsn ON(hsn.hsn_id = st.st_hsn_id)
				LEFT JOIN diamond_master dm ON(dm.dm_code = st.st_dm_code)
	    		WHERE st_sale_id = $id")->result_array();

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

	    	return $record;
	    }

	    public function get_all_hsnwise_sales_data_print()
        {
            $subsql = "";
            
            if(!empty($_GET['start_date']) && !empty($_GET['end_date']))
            {
            	$start_date = date('Y-m-d', strtotime($_GET['start_date']));
                $end_date = date('Y-m-d', strtotime($_GET['end_date']));
                $subsql .= " AND DATE_FORMAT(sale.sale_invoice_date, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'";
            }


            $record['hsn_data'] =  $this->db->query("SELECT st.st_hsn_id, hsn.hsn_id, hsn.hsn_code, st.st_id, st.st_dm_id, SUM(st.st_sub_total_amt) as total_taxable_amt, SUM(st.st_amt_cgst) as total_cgst, SUM(st.st_amt_sgst) as total_sgst, SUM(st.st_amt_igst) as total_igst, SUM(st.st_total_amt) as total_amt, dm.dm_id, dm.dm_code, sale.sale_invoice_date, am.acc_owner, am.acc_gst, sale.sale_entry_no, sm.state_name FROM sales_trans st 
                LEFT JOIN sales_master sale ON(sale.sale_id = st.st_sale_id)
                LEFT JOIN diamond_master dm ON(dm.dm_id = st.st_dm_id)
				LEFT JOIN hsn_master hsn ON(hsn.hsn_id = st.st_hsn_id)
                LEFT JOIN account_master am ON(am.acc_id = sale.sale_customer_id)
                LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
                WHERE 1 $subsql GROUP BY st.st_dm_id")->result_array();
            
            // print_r($record['hsn_data']);
            // exit;


            return $record;
        }
        /*   END HSN Wise SALES REPORT   */

		public function check_dm_code_exists($dm_code)
		{
			if (empty($dm_code)) return false;

			$this->db->select('dm_id');
			$this->db->from('diamond_master');
			$this->db->where('dm_code', $dm_code);
			$query = $this->db->get();

			return ($query->num_rows() > 0);
		}

		public function diamond_insert($data)
		{
// echo "<pre>";
// 	    	print_r($data['rg_dm_category']);
// 	    	exit;
		$insert_data = [
    'dm_category' => !empty($data['rg_dm_category']) ? $data['rg_dm_category'] : 0,

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

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

    'dm_user_id'              => !empty($_SESSION['user_id']) ? $_SESSION['user_id'] : '',
    'dm_date_create'          => date('Y-m-d')
];



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

		public function get_hsn_data($hsn_id)
		{
			return $this->db->where('hsn_id', $hsn_id)
							->get('hsn_master')
							->row_array();
		}

		public function get_sales_trans_summary($dm_code)
		{
			 $sales_query = "SELECT 
					SUM(st_carat) AS total_carat,
					SUM(st_pc_amt) AS total_pc_amt,
					SUM(st_dollar_amt) AS total_dollar_amt,
					SUM(st_pcs) AS total_pcs,

					-- formulas
					-- (SUM(st_pc_amt) / NULLIF(SUM(st_carat), 0)) AS price_sale_ct,
					-- (SUM(st_dollar_amt) / NULLIF(SUM(st_carat), 0)) AS dollar_sale_ct
					SUM(st_price_ct) AS price_sale_ct,
					SUM(st_dollar) AS dollar_sale_ct

				FROM sales_trans
				WHERE st_dm_code = '$dm_code'";
            return $this->db->query($sales_query)->result_array();
		}

        public function get_export_data($sales_id)
        {
            $sales_id = (int) $sales_id; 

            $query = "
                SELECT 
                    CASE 
                        WHEN sm.pur_with_registered = 1 
                        THEN sm.sale_unregi_entry_no 
                        ELSE sm.sale_entry_no 
                    END AS invoice_no,

                    hsn.hsn_code,
                    dm.dm_category,

                    st.st_carat,
                    st.st_dollar,
                    st.st_dollar_amt,
                    st.st_dm_lab,

                    st.st_lab,
                    st.st_cert_no,
                    st.st_dm_sh,
                    st.st_dm_col,
                    st.st_dm_cla,
                    st.st_dm_cut,
                    st.st_dm_pol,
                    st.st_dm_sym,
                    st.st_dm_fl,
                    st.st_pcs,
                    st.st_cpd,
                    st.st_size,
                    sm.sale_fin_year

                FROM sales_master sm

                INNER JOIN sales_trans st 
                    ON sm.sale_id = st.st_sale_id

                INNER JOIN diamond_master dm 
                    ON dm.dm_id = st.st_dm_id

                LEFT JOIN hsn_master hsn 
                    ON hsn.hsn_id = st.st_hsn_id

                WHERE sm.sale_id = {$sales_id}
            ";
            
            return $this->db->query($query)->result_array();
        }


	}
?>