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

	class Purchasemdl 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_purchase_data_cnt()
		{
		    $subsql = "";
        
            if(!empty($_GET['entry_no']))
            {
                $subsql .= " AND per.per_id =".$_GET['entry_no'];
            }

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

            if(!empty($_GET['supp_id']))
            {
                $subsql .= " AND per.per_supplier_id =".$_GET['supp_id'];
            }
            
			return $this->db->query("SELECT per.*, am.*, am.acc_name, am.acc_mobile, am.acc_email, sm.state_name, cm.city_name FROM purchase_master per 
	    		LEFT JOIN account_master am ON(am.acc_id = per.per_supplier_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_purchase_data($per_page, $offset)
		{
			$subsql = "";
        
            if(!empty($_GET['entry_no']))
            {
                $subsql .= " AND per.per_id =".$_GET['entry_no'];
            }

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

            if(!empty($_GET['supp_id']))
            {
                $subsql .= " AND per.per_supplier_id =".$_GET['supp_id'];
            }

			  $record['purchase_data'] = $this->db->query("
        SELECT 
            per.*,
            am.acc_name, am.acc_contact, am.acc_mobile, am.acc_email, 
            sm.state_name, cm.city_name,
            COALESCE(SUM(prt.prt_price_ct), 0) AS total_price_ct,
            COALESCE(SUM(prt.prt_pc_amt), 0) AS total_pc_amt,
            COALESCE(SUM(prt.prt_carat), 0) AS total_qty,
            COALESCE(SUM(prt.prt_total_amt), 0) AS total_amt,
			COALESCE(SUM(prt.prt_dollar), 0) AS total_dollar,
			COALESCE(SUM(prt.prt_dollar_amt), 0) AS total_dollar_amt
        FROM purchase_master per
        LEFT JOIN account_master am ON(am.acc_id = per.per_supplier_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 purchase_trans prt ON(prt.prt_per_id = per.per_id)
        WHERE 1 $subsql
        GROUP BY per.per_id
        ORDER BY per.per_id DESC
        LIMIT $per_page OFFSET $offset
    ")->result_array();

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

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

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

			return $record;
		}
		
		public function get_purchase_form_assets()
	    {

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

	        $record['entry_no'] = $this->get_max_id('purchase_master', 'per_entry_no');

			$record['entry_no_unregi'] = $this->get_max_id('purchase_master', 'per_unregi_entry_no');

			$record['bill_no'] = $this->get_max_id('purchase_master', 'per_bill_no');
			
			$current_dollar_rate = $this->db->query("
                SELECT exchange_rate_buying_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_buying_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_purchase_data_for_edit($id)
	    {
	    	$users = $this->db->query("SELECT acc_id, UPPER(acc_contact) as acc_name FROM account_master WHERE acc_grp_id = 6 ORDER BY acc_name ASC ")->result_array();
	        $record['user'][0] = "PLEASE SELECT";
	        foreach ($users as $key => $value) {
	        	$record['user'][$value['acc_id']] = $value['acc_name'];
	        }

	        $record['entry_no'] = $this->get_max_id('purchase_master', 'per_entry_no');

			$record['entry_no_unregi'] = $this->get_max_id('purchase_master', 'per_unregi_entry_no');

			$record['bill_no'] = $this->get_max_id('purchase_master', 'per_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['purchase_data'] = $this->db->query("SELECT per.per_id, per.pur_with_registered, per.per_entry_no, per.per_unregi_entry_no, per.per_dispatch_by, per.per_invoice_date, per.per_entry_date, per.per_supplier_id,per.per_bill_no, per.per_bill_date, per.per_current_dollar_rate, per.per_acc_type, per_total_cgst, per_total_sgst, per_total_igst, per_total_qty, per_total_rate, per_sub_total_amt, per_total_amt, per.per_terms, am.acc_id, am.acc_name, am.acc_mobile, am.acc_email, sm.state_name, cm.city_name, $user_id as user_id FROM purchase_master per 
	    		LEFT JOIN account_master am ON(am.acc_id = per.per_supplier_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 per_id = $id")->result_array();

	    	$record['purchase_trans'] = $this->db->query("SELECT prt.*, prt.prt_id, prt_dm_id, prt_pc_amt, prt_qty, prt_amt_cgst, prt_amt_sgst, prt_amt_igst, prt_per_cgst, prt_per_sgst, prt_per_igst, prt_sub_total_amt, prt_total_amt, prt_hsn_id, hsn.hsn_id, hsn.hsn_code
	    		FROM purchase_trans prt 
	    		LEFT JOIN hsn_master hsn ON(hsn.hsn_id = prt.prt_hsn_id)
	    		WHERE prt_per_id = $id")->result_array();

			$query1="SELECT 
                pam.cam_id as purchase_cam_id, 
                pam.cam_path as purchase_cam_path
                FROM purchase_master per
                LEFT JOIN purchase_attachment_master pam ON(pam.cam_purchase_id = per.per_id)
                WHERE per.per_id = $id";
        	$record['purchase_attachment_data'] = $this->db->query($query1)->result_array();

	    	return $record;
	    }

	    public function get_diamond_data($dm_id, $supp_id)
		{
			// 1️⃣ Get diamond + purchase transaction data
			$diamond_query = "
				SELECT dm.*, prt.prt_id, prt.prt_per_id
				FROM diamond_master dm
				LEFT JOIN purchase_trans prt ON prt.prt_dm_id = dm.dm_id
				WHERE dm.dm_id = $dm_id
				ORDER BY prt.prt_id DESC
				LIMIT 1
			";
			$diamond_data = $this->db->query($diamond_query)->row_array();

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

			// 2️⃣ Get supplier data separately
			$supplier_query = "
				SELECT supplier.acc_id, supplier.acc_name, supplier.acc_type
				FROM account_master supplier
				WHERE supplier.acc_id = $supp_id
			";

			$supplier_data = $this->db->query($supplier_query)->row_array();

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

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

			return $diamond_data;
		}


	    public function get_single_purchase_data_for_print($id)
	    {
	    	$record['purchase_data'] = $this->db->query("SELECT per_entry_no, per_dispatch_by, per_terms, per_invoice_date, per_fin_year, am.acc_name, am.acc_mobile, am.acc_email,am.acc_address, am.acc_pan, am.acc_gst, sm.state_name, sm.state_code, cm.city_name, per_total_qty, per_total_rate, per_total_sgst, per_total_cgst, per_total_igst, per_total_amt, per_sub_total_amt FROM purchase_master inv 
	    		LEFT JOIN account_master am ON(am.acc_id = per.per_supplier_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 per_id = $id")->result_array();

	    	$record['purchase_trans'] = $this->db->query("SELECT prt.prt_id, prt_qty, prt_pc_amt, prt_sub_total_amt, prt_total_amt, prt_amt_cgst, prt_amt_sgst, prt_amt_igst, dm.dm_id, dm.dm_code, prt_per_cgst, prt_per_sgst, prt_per_igst, prt_hsn_id, hsn.hsn_id, hsn.hsn_code
	    		FROM invoice_trans prt 
	    		LEFT JOIN diamond_master dm ON(dm.dm_id = prt.prt_dm_id)
	    		LEFT JOIN hsn_master hsn ON(hsn.hsn_id = prt.prt_hsn_id)
	    		WHERE prt_per_id = $id")->result_array();

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

	    	return $record;
	    }

	    public function get_all_hsnwise_purchase_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(per.per_invoice_date, '%Y-%m-%d') BETWEEN '".$start_date."' AND '".$end_date."'";
            }


            $record['hsn_data'] =  $this->db->query("SELECT prt.prt_hsn_id, hsn.hsn_id, hsn.hsn_code, prt.prt_id, prt.prt_dm_id, SUM(prt.prt_sub_total_amt) as total_taxable_amt, SUM(prt.prt_amt_cgst) as total_cgst, SUM(prt.prt_amt_sgst) as total_sgst, SUM(prt.prt_amt_igst) as total_igst, SUM(prt.prt_total_amt) as total_amt, dm.dm_id, dm.dm_code, per.per_invoice_date, am.acc_name, am.acc_gst, per.per_entry_no, sm.state_name FROM purchase_trans prt 
                LEFT JOIN purchase_master per ON(per.per_id = prt.prt_per_id)
                LEFT JOIN diamond_master dm ON(dm.dm_id = prt.prt_dm_id)
				LEFT JOIN hsn_master hsn ON(hsn.hsn_id = prt.prt_hsn_id)
                LEFT JOIN account_master am ON(am.acc_id = per.per_supplier_id)
                LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
                WHERE 1 $subsql GROUP BY prt.prt_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_wis'                  => !empty($data['rg_dm_wis']) ? $data['rg_dm_wis'] : '',
    '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_purchase_trans_summary($dm_code)
		{
			 $purchase_query = "SELECT 
					SUM(prt_carat) AS total_carat,
					SUM(prt_pc_amt) AS total_pc_amt,
					SUM(prt_dollar_amt) AS total_dollar_amt,
					SUM(prt_pcs) AS total_pcs,

					-- formulas
					-- (SUM(prt_pc_amt) / NULLIF(SUM(prt_carat), 0)) AS price_per_ct,
					-- (SUM(prt_dollar_amt) / NULLIF(SUM(prt_carat), 0)) AS dollar_per_ct
					SUM(prt_price_ct) AS price_per_ct,
					SUM(prt_dollar) AS dollar_per_ct

				FROM purchase_trans
				WHERE prt_dm_code = '$dm_code'";
            return $this->db->query($purchase_query)->result_array();
		}
	}
?>