<?php
	defined('BASEPATH') OR exit('No direct script access allowed');
	require FCPATH.'vendor/autoload.php';
	use PhpOffice\PhpSpreadsheet\Spreadsheet; 
	use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
	// use PhpOffice\PhpSpreadsheet\Style\Alignment;

	use PhpOffice\PhpSpreadsheet\IOFactory;
	use PhpOffice\PhpSpreadsheet\Style\Border;
	use PhpOffice\PhpSpreadsheet\Style\Color;
	use PhpOffice\PhpSpreadsheet\Cell\DataType;
	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

	class Purchasectrl extends CI_Controller {
		public $purchasemdl;
		public $excel;
		public $pagination;
		public function __construct()
		{
			parent::__construct();
			$this->load->model('purchase/Purchasemdl','purchasemdl');
			$this->load->library('session');
			$this->load->library('db_operations');
			$this->config->load('extra');
			$this->load->library('pagination');
		}

		public function purchase_master()
		{
			if(sessionExist()) 
			{
				if( $_GET['action'] == 'view')
				{
					$config 				= array();
					$config 				= $this->config->item('pagination');
					$config['total_rows'] 	= $this->purchasemdl->get_all_purchase_data_cnt();
					$config['base_url'] 	= base_url("purchase/purchasectrl/purchase_master?search=true");

					foreach ($_GET as $key => $value) 
					{
						if($key != 'search' && $key != 'offset')
						{
							$config['base_url'] .= "&" . $key . "=" .$value;
						}
					}

					$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
					$this->pagination->initialize($config);

					$record['count'] 		= $offset;
					$record['total_rows'] 	= $config['total_rows'];
					$record['role'] 		= $this->config->item('role');				
					$record['data'] 		= $this->purchasemdl->get_all_purchase_data($config['per_page'],$offset);

					$this->load->view('pages/purchase/purchase_master', $record);
				}
				else if( $_GET['action'] == 'add' )
				{
					$data = $this->purchasemdl->get_purchase_form_assets();
					$this->load->view('pages/purchase/purchase_form', $data);
				}
				else if( $_GET['action'] == 'edit' )
				{
					$purchase_id = encrypt_decrypt("decrypt", $_GET['id'], SECRET_KEY);
					$data = $this->purchasemdl->get_single_purchase_data_for_edit($purchase_id);
					$this->load->view('pages/purchase/purchase_form', $data);
				}
				// else if( $_GET['action'] == 'print' )
				// {
				// 	$purchase_id = encrypt_decrypt("decrypt", $_GET['id'], SECRET_KEY);
				// 	$data = $this->purchasemdl->get_single_purchase_data_for_print($purchase_id);
					
				// 	$this->load->view('pdfs/purchase/purchase_pdf', $data);
				// }
			}
			else 
			{
				redirect('loginctrl/logout');	
			}
		}

		public function get_diamond_data($dm_id, $supp_id)
		{
			if(sessionExist())
			{
				echo json_encode($this->purchasemdl->get_diamond_data($dm_id, $supp_id));
			}
			else
			{
				redirect('loginctrl/logout');
			}
		}

		// ============================
		// 		Invoice Insert
		// ============================

		public function purchase_insert($id)
		{
			if(sessionExist()) 
			{
				$formdata = $this->input->post();
			    // echo "<pre>"; print_r($formdata); exit;

                $purchase_data['pur_with_registered']   = $formdata['pur_with_registered'];

                if($formdata['pur_with_registered'] == 1){
                    $purchase_data['per_unregi_entry_no'] = isset($formdata['per_unregi_entry_no']) ? $formdata['per_unregi_entry_no'] : 0;
                }else{
                    $purchase_data['per_entry_no'] = isset($formdata['per_no']) ? $formdata['per_no'] : 0;
                }
                
				$purchase_data['per_entry_date'] = date('Y-m-d', strtotime($formdata['per_entry_date']));
				$purchase_data['per_invoice_date'] = date('Y-m-d', strtotime($formdata['per_invoice_date']));
				$purchase_data['per_supplier_id'] = $formdata['supp_id'];
				// $purchase_data['per_dispatch_by'] = $formdata['per_dispatch_by'];
				// $purchase_data['per_terms']      = $formdata['per_terms'];
				$purchase_data['per_bill_no'] = $formdata['per_bill_no'];
				$purchase_data['per_bill_date'] = date('Y-m-d', strtotime($formdata['per_bill_date']));
				$purchase_data['per_current_dollar_rate'] = $formdata['per_current_dollar_rate'];
				$purchase_data['per_acc_type'] = $formdata['acc_type'];
				$purchase_data['per_total_cgst'] = $formdata['total_per_cgst'];
				$purchase_data['per_total_sgst'] = $formdata['total_per_sgst'];
				$purchase_data['per_total_igst'] = $formdata['total_per_igst'];
				$purchase_data['per_total_qty'] = $formdata['total_qty'];
				$purchase_data['per_total_rate'] = $formdata['total_rate'];
				$purchase_data['per_sub_total_amt'] = $formdata['sub_total'];
				$purchase_data['per_total_amt'] = $formdata['final_total'];

				$this->db->trans_begin();

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

				if($id == 0)
				{
					$purchase_data['per_create_userid'] = $_SESSION['user_id'];
					$purchase_data['per_create_date'] = date("Y-m-d H:i:s");
					$purchase_data['per_fin_year'] = $_SESSION['fin_year'];
					
					$result = $this->db_operations->data_insert('purchase_master', $purchase_data);

					if(!empty($result))
					{
						$trans_result 	= $this->insert_update_purchase_trans($formdata, $result);
						if(!empty($trans_result))
						{
							if ($this->db->trans_status() === FALSE)
							{
							    $this->db->trans_rollback();
							    $response['flag'] = 0;
								echo json_encode($response);
							}
							else
							{
							    $this->db->trans_commit();
							   	$response['flag'] = 1;
							 	$response['last_id'] = encrypt_decrypt("encrypt", $result, SECRET_KEY);
								echo json_encode($response);
							}
						}

                        if(!empty($_FILES['purchase_attachment']['name'][0])){
                            $result1 = $this->upload_purchase_attachment($result);
                            if(!$result1['status']){
                                $this->db->trans_rollback();
                                echo json_encode(['status'=>FALSE,'msg'=>$result1['msg']]);
                                return;
                            }
                        }
					}
					else
					{
						$this->db->trans_commit();
						$response['flag'] = 0;
						echo json_encode($response);
					}
				}
				else
				{
					$purchase_data['per_update_userid'] = $_SESSION['user_id'];
					$purchase_data['per_update_date'] = date("Y-m-d H:i:s");
					
					$result = $this->db_operations->data_update('purchase_master', $purchase_data, 'per_id',$id);

					if(!empty($result))
					{
						$trans_result 	= $this->insert_update_purchase_trans($formdata, $id);
						if(!empty($trans_result))
						{
							if ($this->db->trans_status() === FALSE)
							{
							    $this->db->trans_rollback();
							    $response['flag'] = 0;
								echo json_encode($response);
							}
							else
							{
							    $this->db->trans_commit();
							   	$response['flag'] = 1;
							 	$response['last_id'] = encrypt_decrypt("encrypt", $id, SECRET_KEY);
								echo json_encode($response);
							}
						}

                        if(!empty($_FILES['purchase_attachment']['name'][0])){
                            $result1 = $this->upload_purchase_attachment($id);
                            if(!$result1['status']){
                                $this->db->trans_rollback();
                                echo json_encode(['status'=>FALSE,'msg'=>$result1['msg']]);
                                return;
                            }
                        }
					}
					else
					{
						$this->db->trans_commit();
						$response['flag'] = 0;
						echo json_encode($response);
					}
				}
			}
			else 
			{
				redirect('loginctrl/logout');	
			}
		}

		// ====
public function insert_update_purchase_trans($formdata, $purchase_id)
{
    $flag = 0;

    // Ensure prt_id exists
    if (!isset($formdata['prt_id']) || !is_array($formdata['prt_id'])) {
        $formdata['prt_id'] = [];
    }

    // Get existing purchase transactions
    $trans_data = $this->db_operations->get_record('purchase_trans', ['prt_per_id' => $purchase_id]);

    // --- Step 1: Delete removed diamonds ---
    foreach ($trans_data as $existing) {

        // If this prt_id is NOT in the submitted list -> DELETE
        if (!in_array($existing['prt_id'], $formdata['prt_id'])) {

            $dm_code = $existing['prt_dm_code'];

            // Delete the row
            $this->db_operations->delete_record('purchase_trans', [
                'prt_id' => $existing['prt_id']
            ]);

            // After delete → recalc remaining items for that dm_code
            $summary = $this->purchasemdl->get_purchase_trans_summary($dm_code);

            // Update diamond_master with new recalculated totals
            $update_after_delete = [
                'dm_carat'      => floatval($summary[0]['total_carat']),
                'dm_pc_amt'     => floatval($summary[0]['total_pc_amt']),
                'dm_dollar_amt' => floatval($summary[0]['total_dollar_amt']),
                'dm_pcs'        => floatval($summary[0]['total_pcs']),
                'dm_price_ct'   => floatval($summary[0]['price_per_ct']),
                'dm_dollar'     => floatval($summary[0]['dollar_per_ct']),
                'dm_stock_in'   => floatval($summary[0]['total_carat'])
            ];

            $this->db_operations->data_update(
                'diamond_master',
                $update_after_delete,
                'dm_code',
                $dm_code
            );
        }
    }

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

    // --- Step 2: Insert or update diamonds ---
    foreach ($formdata['prt_id'] as $key => $prt_id) {
        $trans = [];

        $trans['prt_per_id']         = $purchase_id;
        $trans['prt_dm_id']          = $formdata['prt_dm_id'][$key] ?? '';
        $trans['prt_hsn_id']         = $formdata['prt_hsn_id'][$key] ?? '';
        $trans['prt_date']           = date('Y-m-d', strtotime($formdata['prt_date'][$key])) ?? '';
        $trans['prt_dm_code']        = $formdata['prt_dm_code'][$key] ?? '';
        $trans['prt_price_ct']       = floatval($formdata['prt_price_ct'][$key] ?? 0);

        // Rate
        $trans['prt_pc_amt'] = floatval($formdata['prt_pc_amt'][$key] ?? 0);

        $trans['prt_dollar']         = floatval($formdata['prt_dollar'][$key] ?? 0);
        $trans['prt_dollar_amt']     = floatval($formdata['prt_dollar_amt'][$key] ?? 0);
        $trans['prt_size']           = $formdata['prt_size'][$key] ?? '';
        $trans['prt_pcs']            = floatval($formdata['prt_pcs'][$key] ?? 0);
        $trans['prt_cpd']            = $formdata['prt_cpd'][$key] ?? '';
        $trans['prt_dm_sh']          = $formdata['prt_dm_sh'][$key] ?? '';
        $trans['prt_lab']            = $formdata['prt_lab'][$key] ?? '';
        $trans['prt_cert_no']        = $formdata['prt_cert_no'][$key] ?? '';

        //QTY
        $trans['prt_carat']          = floatval($formdata['prt_carat'][$key] ?? 0);

        $trans['prt_dm_col']         = $formdata['prt_dm_col'][$key] ?? '';
        $trans['prt_dm_cla']         = $formdata['prt_dm_cla'][$key] ?? '';
        $trans['prt_dm_cut']         = $formdata['prt_dm_cut'][$key] ?? '';
        $trans['prt_dm_pol']         = $formdata['prt_dm_pol'][$key] ?? '';
        $trans['prt_dm_sym']         = $formdata['prt_dm_sym'][$key] ?? '';
        $trans['prt_dm_fl']          = $formdata['prt_dm_fl'][$key] ?? '';
        $trans['prt_length']         = $formdata['prt_length'][$key] ?? '';
        $trans['prt_width']          = $formdata['prt_width'][$key] ?? '';
        $trans['prt_height']         = $formdata['prt_height'][$key] ?? '';
        $trans['prt_dm_lab']         = $formdata['prt_dm_lab'][$key] ?? '';
        $trans['prt_disc']           = $formdata['prt_disc'][$key] ?? '';
        $trans['prt_rap']            = $formdata['prt_rap'][$key] ?? '';
        $trans['prt_video']          = $formdata['prt_video'][$key] ?? '';
        $trans['prt_status']         = $formdata['prt_status'][$key] ?? '';
        $trans['prt_uploaded_date']  = date('Y-m-d', strtotime($formdata['prt_uploaded_date'][$key])) ?? '';
        $trans['prt_no_of_days']     = $formdata['prt_no_of_days'][$key] ?? '';
        $trans['prt_rapnet']         = $formdata['prt_rapnet'][$key] ?? '';
        $trans['prt_suspense_stock'] = $formdata['prt_suspense_stock'][$key] ?? '';

        $trans['prt_location'] = $formdata['prt_location'][$key] ?? '';
        $trans['prt_dna'] = $formdata['prt_dna'][$key] ?? '';
        $trans['prt_rap_dollar'] = $formdata['prt_rap_dollar'][$key] ?? '';
        $trans['prt_tbl'] = $formdata['prt_tbl'][$key] ?? '';
        $trans['prt_td_percentage'] = $formdata['prt_td_percentage'][$key] ?? '';
        $trans['prt_l_w'] = $formdata['prt_l_w'][$key] ?? '';
        $trans['prt_bic'] = $formdata['prt_bic'][$key] ?? '';
        $trans['prt_bis'] = $formdata['prt_bis'][$key] ?? '';
        $trans['prt_wic'] = $formdata['prt_wic'][$key] ?? '';
        $trans['prt_col_tinge'] = $formdata['prt_col_tinge'][$key] ?? '';
        $trans['prt_lus'] = $formdata['prt_lus'][$key] ?? '';
        $trans['prt_remarks'] = $formdata['prt_remarks'][$key] ?? '';
        $trans['prt_h_a'] = $formdata['prt_h_a'][$key] ?? '';
        $trans['prt_girdle'] = $formdata['prt_girdle'][$key] ?? '';
        $trans['prt_girdle_percentage'] = $formdata['prt_girdle_percentage'][$key] ?? '';
        $trans['prt_girdle_cond'] = $formdata['prt_girdle_cond'][$key] ?? '';
        $trans['prt_ca'] = $formdata['prt_ca'][$key] ?? '';
        $trans['prt_ch'] = $formdata['prt_ch'][$key] ?? '';
        $trans['prt_pa'] = $formdata['prt_pa'][$key] ?? '';
        $trans['prt_pd'] = $formdata['prt_pd'][$key] ?? '';
        $trans['prt_str_ln'] = $formdata['prt_str_ln'][$key] ?? '';
        $trans['prt_lr_half'] = $formdata['prt_lr_half'][$key] ?? '';
        $trans['prt_dm_culet'] = $formdata['prt_dm_culet'][$key] ?? '';
        $trans['prt_kts'] = $formdata['prt_kts'][$key] ?? '';
        $trans['prt_rep_com'] = $formdata['prt_rep_com'][$key] ?? '';
        $trans['prt_cert_date'] = !empty($formdata['prt_cert_date'][$key]) ? 
        date('Y-m-d', strtotime($formdata['prt_cert_date'][$key])) : '';
        $trans['prt_image_link'] = $formdata['prt_image_link'][$key] ?? '';
        $trans['prt_certificate_link'] = $formdata['prt_certificate_link'][$key] ?? '';
        $trans['prt_category'] = $formdata['prt_category'][$key] ?? 0;


        // GST & totals
        $trans['prt_per_cgst']      = floatval($formdata['prt_cgst'][$key] ?? 0);
        $trans['prt_amt_cgst']      = floatval($formdata['prt_cgst_total'][$key] ?? 0);
        $trans['prt_per_sgst']      = floatval($formdata['prt_sgst'][$key] ?? 0);
        $trans['prt_amt_sgst']      = floatval($formdata['prt_sgst_total'][$key] ?? 0);
        $trans['prt_per_igst']      = floatval($formdata['prt_igst'][$key] ?? 0);
        $trans['prt_amt_igst']      = floatval($formdata['prt_igst_total'][$key] ?? 0);
        $trans['prt_sub_total_amt'] = floatval($formdata['prt_sub_total'][$key] ?? 0);
        $trans['prt_total_amt']     = floatval($formdata['prt_total'][$key] ?? 0);

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

        // Insert or update purchase_trans
        if (empty($prt_id) || $prt_id == 0) {
            $prt_id = $this->db_operations->data_insert('purchase_trans', $trans);
        } else {
            $this->db_operations->data_update('purchase_trans', $trans, 'prt_id', $prt_id);
        }

         // --- Step 3: Sync with diamond_master ---
        $dm_code = $trans['prt_dm_code'];

        // Check if diamond exists
        $existing_diamond = $this->db_operations->get_single_record(
            'diamond_master',
            ['dm_code' => $dm_code]
        );

        // -----------------------------------------------------------
        // STEP 1: GET ALL SUMS DIRECTLY FROM SQL (NO FOREACH)
        // -----------------------------------------------------------
        $summary = $this->purchasemdl->get_purchase_trans_summary($dm_code);
		// echo "<pre>"; print_r($summary); exit;

        $total_carat      = floatval($summary[0]['total_carat']);
        $total_pc_amt     = floatval($summary[0]['total_pc_amt']);
        $total_dollar_amt = floatval($summary[0]['total_dollar_amt']);
        $total_pcs        = floatval($summary[0]['total_pcs']);

        $price_per_ct     = floatval($summary[0]['price_per_ct']);
        $dollar_per_ct    = floatval($summary[0]['dollar_per_ct']);

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

        // -----------------------------------------------------------
        // STEP 2: Prepare diamond_master data
        // -----------------------------------------------------------

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

        $update_data = [
            'dm_code'          => $trans['prt_dm_code'],
            'dm_hsn_id'        => $trans['prt_hsn_id'],
            'dm_date'          => $trans['prt_date'],

            // From SQL SUM()
            'dm_price_ct'      => $price_per_ct,        // formula
            'dm_pc_amt'        => $total_pc_amt,        // SUM
            'dm_dollar'        => $dollar_per_ct,       // formula
            'dm_dollar_amt'    => $total_dollar_amt,    // SUM
            'dm_carat'         => $total_carat,         // SUM
            'dm_stock_in'      => $total_carat,         // SUM
            'dm_pcs'           => $total_pcs,           // SUM

            // Latest transaction details
            'dm_size'          => $trans['prt_size'],
            'dm_cpd'           => $trans['prt_cpd'],
            'dm_sh'            => $trans['prt_dm_sh'],
            'dm_lab'           => $trans['prt_lab'],
            'dm_cert_no'       => $trans['prt_cert_no'],
            'dm_col'           => $trans['prt_dm_col'],
            'dm_cla'           => $trans['prt_dm_cla'],
            'dm_cut'           => $trans['prt_dm_cut'],
            'dm_pol'           => $trans['prt_dm_pol'],
            'dm_sym'           => $trans['prt_dm_sym'],
            'dm_fl'            => $trans['prt_dm_fl'],
            'dm_len'           => $trans['prt_length'],
            'dm_wid'           => $trans['prt_width'],
            'dm_hei'           => $trans['prt_height'],
            'dm_nat_lab_grown' => $trans['prt_dm_lab'],
            'dm_disc'          => $trans['prt_disc'],
            'dm_rap'           => $trans['prt_rap'],
            'dm_video_link'    => $trans['prt_video'],
            'dm_status'        => $trans['prt_status'],
            'dm_uploaded_date' => $trans['prt_uploaded_date'],
            'dm_no_of_days'    => $trans['prt_no_of_days'],
            'dm_rapnet'        => $trans['prt_rapnet'],
            'dm_suspense'      => $trans['prt_suspense_stock'],


            'dm_location'               => !empty($trans['prt_location']) ? $trans['prt_location'] : '',
            'dm_dna'                    => !empty($trans['prt_dna']) ? $trans['prt_dna'] : '',
            'dm_rap_dollar'             => !empty($trans['prt_rap_dollar']) ? $trans['prt_rap_dollar'] : '',
            'dm_tbl'                    => !empty($trans['prt_tbl']) ? $trans['prt_tbl'] : '',
            'dm_td_percentage'          => !empty($trans['prt_td_percentage']) ? $trans['prt_td_percentage'] : '',
            'dm_l_w'                    => !empty($trans['prt_l_w']) ? $trans['prt_l_w'] : '',
            'dm_bic'                    => !empty($trans['prt_bic']) ? $trans['prt_bic'] : '',
            'dm_bis'                    => !empty($trans['prt_bis']) ? $trans['prt_bis'] : '',
            'dm_wic'                    => !empty($trans['prt_wic']) ? $trans['prt_wic'] : '',
            'dm_col_tinge'              => !empty($trans['prt_col_tinge']) ? $trans['prt_col_tinge'] : '',
            'dm_lus'                    => !empty($trans['prt_lus']) ? $trans['prt_lus'] : '',
            'dm_remarks'                => !empty($trans['prt_remarks']) ? $trans['prt_remarks'] : '',
            'dm_h_a'                    => !empty($trans['prt_h_a']) ? $trans['prt_dprt_h_ana'] : '',
            'dm_girdle'                 => !empty($trans['prt_girdle']) ? $trans['prt_girdle'] : '',
            'dm_girdle_percentage'      => !empty($trans['prt_girdle_percentage']) ? $trans['prt_girdle_percentage'] : '',
            'dm_girdle_cond'            => !empty($trans['prt_girdle_cond']) ? $trans['prt_girdle_cond'] : '',
            'dm_ca'                     => !empty($trans['prt_ca']) ? $trans['prt_ca'] : '',
            'dm_ch'                     => !empty($trans['prt_ch']) ? $trans['prt_ch'] : '',
            'dm_pa'                     => !empty($trans['prt_pa']) ? $trans['prt_pa'] : '',
            'dm_pd'                     => !empty($trans['prt_pd']) ? $trans['prt_pd'] : '',
            'dm_str_ln'                 => !empty($trans['prt_str_ln']) ? $trans['prt_str_ln'] : '',
            'dm_lr_half'                => !empty($trans['prt_lr_half']) ? $trans['prt_lr_half'] : '',
            'dm_culet'                  => !empty($trans['prt_dm_culet']) ? $trans['prt_dm_culet'] : '',
            'dm_kts'                    => !empty($trans['prt_kts']) ? $trans['prt_kts'] : '',
            'dm_rep_com'                => !empty($trans['prt_rep_com']) ? $trans['prt_rep_com'] : '',
            'dm_cert_date'              => !empty($trans['prt_cert_date']) ? $trans['prt_cert_date'] : '',
            'dm_image_link'             => !empty($trans['prt_image_link']) ? $trans['prt_image_link'] : '',
            'dm_certificate_link'       => !empty($trans['prt_certificate_link']) ? $trans['prt_certificate_link'] : '',
            // 'dm_category'               => !empty($trans['prt_category']) ? $trans['prt_category'] : '',


            // Link back
            'dm_purchase_id'   => $purchase_id,
            'dm_prt_id'        => $prt_id,
            'dm_user_id'       => $_SESSION['user_id'],
            'dm_date_create'   => date("Y-m-d H:i:s")
        ];

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

        // -----------------------------------------------------------
        // STEP 3: Insert or update diamond_master
        // -----------------------------------------------------------
        if ($existing_diamond) {

            $this->db_operations->data_update(
                'diamond_master',
                $update_data,
                'dm_id',
                $existing_diamond['dm_id']
            );

            $dm_id = $existing_diamond['dm_id'];

        } else {

            $dm_id = $this->db_operations->data_insert('diamond_master', $update_data);
        }

        $flag = 1;
    }

    return $flag ? 1 : 0;
}

		
		public function delete_purchase($id)
        {
            if (sessionExist()) {

                // --- STEP 1: Get all purchase_trans rows for this purchase ID ---
                $trans_data = $this->db_operations->get_record('purchase_trans', [
                    'prt_per_id' => $id
                ]);

                if (!empty($trans_data)) {

                    foreach ($trans_data as $row) {

                        $dm_code = $row['prt_dm_code'];
                        if ($dm_code == "") continue;

                        $prt_id = $row['prt_id'];

                        // --- STEP 2: Delete this purchase_trans row ---
                        $this->db_operations->delete_record('purchase_trans', [
                            'prt_id' => $prt_id
                        ]);

                        // --- STEP 3: Recalculate totals of REMAINING purchase_trans ---
                        $summary = $this->purchasemdl->get_purchase_trans_summary($dm_code);
		                // echo "<pre>"; print_r($summary); exit;

                        // --- STEP 4: Prepare updated fields ---
                        $update_data = [
                            'dm_carat'      => floatval($summary[0]['total_carat']),
                            'dm_pc_amt'     => floatval($summary[0]['total_pc_amt']),
                            'dm_dollar_amt' => floatval($summary['total_dollar_amt']),
                            'dm_pcs'        => floatval($summary[0]['total_pcs']),

                            'dm_price_ct'   => floatval($summary[0]['price_per_ct']),
                            'dm_dollar'     => floatval($summary[0]['dollar_per_ct']),

                            // Stock = total carat
                            'dm_stock_in'   => floatval($summary[0]['total_carat']),
                        ];

                        // --- STEP 5: Update diamond_master with SUM() values ---
                        $this->db_operations->data_update('diamond_master',$update_data,'dm_code',$dm_code);
                    }
                }

                // --- STEP 6: Delete purchase_master record ---
                $this->db_operations->delete_record('purchase_master', [
                    'per_id' => $id
                ]);

            } else {
                $this->load->view('pages/login');
            }
        }

		// ================================
		// 		Purchase Report
		public function hsnwise_purchase_report()
		{
			if(sessionExist())
			{
				// $record['data']	= $this->purchasemdl->get_all_hsnwise_purchase_data_print();
				$record="";
				if(isset($_GET['action']))
				{
					if ($_GET['action'] == "print") 
					{
						$record['data']	= $this->purchasemdl->get_all_hsnwise_purchase_data_print();
						$this->load->view('pdfs/report/hsnwise_purchase_report_pdf', $record);
					}
				}
				else
				{
					$this->load->view('pages/purchase/hsnwise_purchase_report', $record);	
				}
				
			}
			else
			{
				$this->load->view('pages/login');
			}
		}

		public function hsnwise_purchase_report_pdf()
		{
			if(sessionExist())
			{
				$record['data']	= $this->purchasemdl->get_all_hsnwise_purchase_data_print();
				$this->load->view('pdfs/report/hsnwise_purchase_report_pdf', $record);
			}
			else
			{
				$this->load->view('pages/login');
			}
		}

		public function insert_diamond()
		{
			$post = $this->input->post();

			// Check if code exists before insert
			$exists = $this->purchasemdl->check_dm_code_exists($post['rg_dm_code'] ?? '');

			if ($exists) {
				$resp = [
					'status'  => 'error',
					'message' => 'Diamond code already exists! Please use a different code.'
				];
			} else {
				// Insert new diamond
				$new_dm_id = $this->purchasemdl->diamond_insert($post);

				$resp = [
					'status'      => 'success',
					'message'     => 'Diamond added successfully!',
					'new_dm_id'   => $new_dm_id,
					'new_dm_code' => strtoupper($post['rg_dm_code'] ?? '')
				];
			}

			echo json_encode($resp);
		}

		public function get_hsn_data($hsn_id = 0)
		{
			$data = $this->purchasemdl->get_hsn_data($hsn_id);

			if (!empty($data)) {
				echo json_encode([
					'status' => 'success',
					'data' => [
						'cgst' => $data['hsn_cgst_per'],
						'sgst' => $data['hsn_sgst_per'],
						'igst' => $data['hsn_igst_per']
					]
				]);
			} else {
				echo json_encode(['status' => 'error', 'message' => 'HSN not found']);
			}
		}

public function import_diamond($supplier_id)
{
    if (!sessionExist()) {
        redirect('loginctrl/logout');
        return;
    }

    $file_data = $_FILES;
    if ($file_data['diamond_import']['error'] != 0) {
        echo json_encode(['resp' => 0, 'msg' => 'File upload error']);
        return;
    }

    $config = [
        'upload_path'   => 'public/uploads/rapnet/',
        'allowed_types' => 'xlsx|xls',
        'detect_mime'   => TRUE,
        'remove_spaces' => TRUE,
        'file_name'     => time() . '.' . pathinfo($file_data['diamond_import']['name'], PATHINFO_EXTENSION)
    ];

    $this->upload->initialize($config);

    if (!$this->upload->do_upload('diamond_import')) {
        echo json_encode(['resp' => 0, 'msg' => $this->upload->display_errors()]);
        return;
    }

    $filepath = "./public/uploads/rapnet/" . $config['file_name'];
    $inputFileType = IOFactory::identify($filepath);
    $reader = IOFactory::createReader($inputFileType);
    $spreadsheet = $reader->load($filepath);
    $sheet = $spreadsheet->getActiveSheet();

    $raw_data = [];
    foreach ($sheet->getRowIterator() as $row) {
        if ($row->getRowIndex() > 1) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);

            $row_data = [];
            foreach ($cellIterator as $cell) {
                $col = $cell->getColumn();
                $row_data[$col] = trim($cell->getFormattedValue(), " ");
            }
            $raw_data[] = $row_data;
        }
    }

    if (empty($raw_data)) {
        echo json_encode(['resp' => 2, 'add_cnt' => 0, 'edit_cnt' => 0]);
        return;
    }

    $add_cnt = $edit_cnt = 0;
    $this->db->trans_begin();

        $merged_data = [];

    // 🔹 STEP 1: Merge rows having same STOCK ID with numeric sum for pcs & carat
    // foreach ($raw_data as $row) {
    //     $stock_id = trim($row['B']);
    //     if (empty($stock_id)) continue;

    //     // Clean values
    //     $pcs_val     = floatval(preg_replace('/[^0-9.]/', '', trim($row['I'])));
    //     $carat_val   = floatval(preg_replace('/[^0-9.]/', '', trim($row['N'])));
    //     $price_ct    = floatval(preg_replace('/[^0-9.]/', '', trim($row['D'])));
    //     $pc_amt      = floatval(preg_replace('/[^0-9.]/', '', trim($row['E'])));
    //     $dollar_ct   = floatval(preg_replace('/[^0-9.]/', '', trim($row['F'])));
    //     $dollar_amt  = floatval(preg_replace('/[^0-9.]/', '', trim($row['G'])));

    //     // Force numeric precision
    //     $pcs_val   = round($pcs_val, 2);
    //     $carat_val = round($carat_val, 3);

    //     $row['I'] = $pcs_val;
    //     $row['N'] = $carat_val;
    //     $row['D'] = $price_ct;
    //     $row['E'] = $pc_amt;
    //     $row['F'] = $dollar_ct;
    //     $row['G'] = $dollar_amt;

    //     if (!isset($merged_data[$stock_id])) {
    //         $merged_data[$stock_id] = $row;
    //     } else {
    //         // 🔹 Sum PCS & CARAT
    //         $merged_data[$stock_id]['I'] += $pcs_val;
    //         $merged_data[$stock_id]['N'] += $carat_val;
    //         $merged_data[$stock_id]['D'] += $price_ct;
    //         $merged_data[$stock_id]['E'] += $pc_amt;
    //         $merged_data[$stock_id]['F'] += $dollar_ct;
    //         $merged_data[$stock_id]['G'] += $dollar_amt;
    //     }
    // }

    // 🔹 DO NOT MERGE DUPLICATE STOCK IDS — CLEAN + PUSH EVERY ROW AS-IS
    foreach ($raw_data as $row) {

        $stock_id = trim($row['B']);
        if (empty($stock_id)) continue;

        // Clean numeric values
        $pcs_val     = floatval(preg_replace('/[^0-9.]/', '', trim($row['I'])));
        $carat_val   = floatval(preg_replace('/[^0-9.]/', '', trim($row['N'])));
        $price_ct    = floatval(preg_replace('/[^0-9.]/', '', trim($row['D'])));
        $pc_amt      = floatval(preg_replace('/[^0-9.]/', '', trim($row['E'])));
        $dollar_ct   = floatval(preg_replace('/[^0-9.]/', '', trim($row['F'])));
        $dollar_amt  = floatval(preg_replace('/[^0-9.]/', '', trim($row['G'])));

        $row['I'] = round($pcs_val, 2);
        $row['N'] = round($carat_val, 3);
        $row['D'] = $price_ct;
        $row['E'] = $pc_amt;
        $row['F'] = $dollar_ct;
        $row['G'] = $dollar_amt;

        // 🔹 Add row individually (NO MERGE)
        $merged_data[] = $row;
    }

    $imported_data = [];

			// echo "<pre>"; print_r($merged_data); exit;
	
    // 🔹 STEP 2: Process merged data
    foreach ($merged_data as $row) {
        $stock_id = trim($row['B']);
        if (empty($stock_id)) continue;

        $hsn_code = trim($row['C']);
        $hsn_id = null;
        $pcs = preg_replace('/[^0-9.]/', '', $row['N']);

        $acc_info = $this->db->get_where('account_master', [
            'acc_grp_id' => 6,
            'acc_id'     => $supplier_id
        ])->row_array();

        $acc_type = $acc_info['acc_type'] ?? 0; // 1 = within MH, 2 = outside MH
			// echo "<pre>"; print_r($hsn_code); exit;

        // 🔹 HSN Handling
        if (!empty($hsn_code)) {
            $hsn_row = $this->db->get_where('hsn_master', ['hsn_code' => $hsn_code])->row_array();

            if ($hsn_row) {
                $hsn_id = $hsn_row['hsn_id'];
                $cgst = $hsn_row['hsn_cgst_per'] ?? 0;
                $sgst = $hsn_row['hsn_sgst_per'] ?? 0;
                $igst = $hsn_row['hsn_igst_per'] ?? 0;
            } else {
                $insert_hsn = [
                    'hsn_code'       => $hsn_code,
                    'hsn_sgst_per'   => 0.75,
                    'hsn_cgst_per'   => 0.75,
                    'hsn_igst_per'   => 1.5,
                    'hsn_created_at' => date('Y-m-d H:i:s'),
                    'hsn_user_id' 	 => $_SESSION['user_id']
                ];
                $this->db->insert('hsn_master', $insert_hsn);
                $hsn_id = $this->db->insert_id();
                $sgst = 0.75;
                $cgst = 0.75;
                $igst = 1.5;
            }
        } else {
            $hsn_id = 0;
            $sgst = 0.75;
            $cgst = 0.75;
            $igst = 1.5;
        }

        // 🔹 State-wise GST logic
        if ($acc_type == 1) { // within MH
            $igst = 0;
        } elseif ($acc_type == 2) { // outside MH
            $cgst = 0;
            $sgst = 0;
        } else {
            $cgst = $sgst = $igst = 0;
        }

        // 🔹 Amount Calculations
        // $sub_total = floatval($row['E']) * floatval($pcs);
        $sub_total = floatval($row['E']);
        $amt_cgst = ($sub_total * $cgst) / 100;
        $amt_sgst = ($sub_total * $sgst) / 100;
        $amt_igst = ($sub_total * $igst) / 100;
        $total_amt = $sub_total + $amt_cgst + $amt_sgst + $amt_igst;

        $prt_pc_amt = !empty($row['E']) ? $row['E'] : $row['D'] * $pcs; // prt_price_ct * carat
        $prt_price_ct = !empty($row['D']) ? $row['D'] : $prt_pc_amt / $pcs; // prt_pc_amt / carat

        $prt_dollar_amt = !empty($row['G']) ? $row['G'] : $row['F'] * $pcs; // prt_dollar_ct * carat
        $prt_dollar_ct = !empty($row['F']) ? $row['F'] : $prt_dollar_amt / $pcs; // prt_dollar_amt / carat

        if(!empty($row['BI'])){
            if($row['BI'] == 'YES' || $row['BI'] == 'yes'){
                $category = 1;
            }else{
                $category = 0;
            }
        }else{
                $category = 0;
        }
        // 🔹 Prepare final purchase data
        $purchase_data = [
            'prt_date'           => !empty($row['A']) ? date('d-m-Y', strtotime($row['A'])) : '',
            'prt_dm_code'        => $stock_id,
            'prt_hsn_code'       => $hsn_code,
            'prt_hsn_id'         => $hsn_id,
            'prt_price_ct'       => round($prt_price_ct, 2),
            'prt_pc_amt'         => round($prt_pc_amt, 2),
            'prt_dollar'         => round($prt_dollar_ct, 2),
            'prt_dollar_amt'     => round($prt_dollar_amt, 2),
            'prt_size'           => $row['H'],
            'prt_pcs'            => $row['I'],
            'prt_cpd'            => $row['J'],
            'prt_dm_sh'          => $row['K'],
            'prt_lab'            => $row['L'],
            'prt_cert_no'        => $row['M'],
            'prt_carat'          => $pcs,
            'prt_dm_col'         => $row['O'],
            'prt_dm_cla'         => $row['P'],
            'prt_dm_cut'         => $row['Q'],
            'prt_dm_pol'         => $row['R'],
            'prt_dm_sym'         => $row['S'],
            'prt_dm_fl'          => $row['T'],
            'prt_length'         => $row['U'],
            'prt_width'          => $row['V'],
            'prt_height'         => $row['W'],
            'prt_dm_lab'         => $row['X'],
            'prt_disc'           => $row['Y'],
            'prt_rap'            => $row['Z'],
            'prt_video'          => $row['AA'],
            'prt_status'         => $row['AB'] ?? '',
            'prt_uploaded_date'  => !empty($row['AC']) ? date('d-m-Y', strtotime($row['AC'])) : date('d-m-Y'),
            'prt_no_of_days'     => $row['AD'] ?? '',
            'prt_rapnet'         => $row['AE'] ?? '',
            'prt_suspense_stock' => $row['AF'] ?? '',



            'prt_location' => $row['AG'] ?? '',
            'prt_dna' => $row['AH'] ?? '',
            'prt_rap_dollar' => $row['AI'] ?? '',
            'prt_tbl' => $row['AJ'] ?? '',
            'prt_td_percentage' => $row['AK'] ?? '',
            'prt_l_w' => $row['AL'] ?? '',
            'prt_bic' => $row['AM'] ?? '',
            'prt_bis' => $row['AN'] ?? '',
            'prt_wic' => $row['AO'] ?? '',
            'prt_col_tinge' => $row['AP'] ?? '',
            'prt_lus' => $row['AQ'] ?? '',
            'prt_remarks' => $row['AR'] ?? '',
            'prt_h_a' => $row['AS'] ?? '',
            'prt_girdle' => $row['AT'] ?? '',
            'prt_girdle_percentage' => $row['AU'] ?? '',
            'prt_girdle_cond' => $row['AV'] ?? '',
            'prt_ca' => $row['AW'] ?? '',
            'prt_ch' => $row['AX'] ?? '',
            'prt_pa' => $row['AY'] ?? '',
            'prt_pd' => $row['AZ'] ?? '',
            'prt_str_ln' => $row['BA'] ?? '',
            'prt_lr_half' => $row['BB'] ?? '',
            'prt_dm_culet' => $row['BC'] ?? '',
            'prt_kts' => $row['BD'] ?? '',
            'prt_rep_com' => $row['BE'] ?? '',
            'prt_cert_date'  => !empty($row['BF']) ? date('d-m-Y', strtotime($row['BF'])) : date('d-m-Y'),
            'prt_image_link' => $row['BG'] ?? '',
            'prt_certificate_link' => $row['BH'] ?? '',
            'prt_category' => $category,




            'prt_cgst'           => $cgst,
            'prt_sgst'           => $sgst,
            'prt_igst'           => $igst,
            'prt_amt_cgst'       => $amt_cgst,
            'prt_amt_sgst'       => $amt_sgst,
            'prt_amt_igst'       => $amt_igst,
            'prt_total_amt'      => $total_amt,
            'prt_user_id'        => $_SESSION['user_id'],
        ];

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

        $imported_data[] = $purchase_data;
    }

    if ($this->db->trans_status() === FALSE) {
        $this->db->trans_rollback();
        echo json_encode(['resp' => 0, 'add_cnt' => 0, 'edit_cnt' => 0]);
    } else {
        $this->db->trans_commit();
        echo json_encode([
            'resp'          => 1,
            'add_cnt'       => $add_cnt,
            'edit_cnt'      => $edit_cnt,
            'imported_data' => $imported_data
        ]);
    }
}



function get_gst_rates($hsn_code) {
    $gst = $this->db->get_where('hsn_master', ['hsn_code' => $hsn_code])->row_array();
    return $gst ? [
        'cgst' => floatval($gst['hsn_cgst_per']),
        'sgst' => floatval($gst['hsn_sgst_per']),
        'igst' => floatval($gst['hsn_igst_per'])
    ] : [
        'cgst' => 0,
        'sgst' => 0,
        'igst' => 0
    ];
}


public function supplier_insert($acc_id = 0)
{
    if (!sessionExist()) {
        redirect('loginctrl/logout');
        return;
    }

    $form_data = $this->input->post();
    $files     = $_FILES;
    $sr_no     = 0;

    $acc_diamond_type = isset($_POST['acc_diamond_type']) ? implode(',', $_POST['acc_diamond_type']) : 0;

    // --- KYC IMAGES UPLOAD ---
    if (!empty($files['kyc_img']['name'][0])) {
        $count = count($files['kyc_img']['name']);
        for ($i = 0; $i < $count; $i++) {
            if ($files['kyc_img']['error'][$i] == 0) {
                $path = $files['kyc_img']['name'][$i];
                $ext = pathinfo($path, PATHINFO_EXTENSION);
                $fileName = time() . '_kyc_' . ($sr_no + 1) . '.' . $ext;

                $config = [
                    'upload_path'   => 'public/uploads/customer_kyc/',
                    'allowed_types' => 'gif|jpg|png|jpeg|pdf',
                    'max_size'      => '102400',
                    'file_name'     => $fileName
                ];

                $this->upload->initialize($config);
                $_FILES['kyc_img_temp'] = [
                    'name'     => $files['kyc_img']['name'][$i],
                    'type'     => $files['kyc_img']['type'][$i],
                    'tmp_name' => $files['kyc_img']['tmp_name'][$i],
                    'error'    => $files['kyc_img']['error'][$i],
                    'size'     => $files['kyc_img']['size'][$i]
                ];

                if ($this->upload->do_upload('kyc_img_temp')) {
                    $sr_no++;
                    $form_data['acc_kyc_img_' . $sr_no] = $fileName;
                }
            }
        }
    }

    // --- INSERT ONLY ---
    if ($form_data['acc_grp_id'] == 6) {
        $form_data['acc_name'] = $form_data['acc_contact'];
    }

    // Check if already exists
    $cnt = $this->purchasemdl->get_cnt('account_master', ['acc_name' => $form_data['acc_name']]);
    if ($cnt > 0) {
        echo json_encode(['status' => 2]); // Account already exists
        return;
    }

    // Prepare insert data
    $form_data['acc_diamond_type'] = $acc_diamond_type;
    $form_data['acc_user_id']      = $_SESSION['user_id'];
    $form_data['acc_date_create']  = date("Y-m-d H:i:s");

    // Insert record
    $insert_id = $this->db_operations->data_insert('account_master', $form_data);

    if ($insert_id) {
        echo json_encode([
            'status' => 1,
            'id'     => $insert_id,
            'name'   => $form_data['acc_name']
        ]);
    } else {
        echo json_encode(['status' => 0]);
    }
}





public function upload_purchase_attachment($purchase_id){
	    $files      = $_FILES;
		
		// echo "<pre>"; print_r($post_data);exit;
		if(empty($files)) return ['msg' => 'Document is empty.'];			
		$cnt = isset($files['purchase_attachment']['name']) ? count($files['purchase_attachment']['name']) : 0;
		$data=[];
		for($i = 0; $i < $cnt; $i++){
			if($files['purchase_attachment']['error'][$i] != 0) return ['msg' => 'Error in Image.'];

			$_FILES['purchase_attachment']['name']		= $files['purchase_attachment']['name'][$i];
			$_FILES['purchase_attachment']['type']		= $files['purchase_attachment']['type'][$i];
	        $_FILES['purchase_attachment']['tmp_name']	= $files['purchase_attachment']['tmp_name'][$i];
	        $_FILES['purchase_attachment']['error']		= $files['purchase_attachment']['error'][$i];
	        $_FILES['purchase_attachment']['size']		= $files['purchase_attachment']['size'][$i];

	        unset($config);
			$config 					= array();
			$config['upload_path'] 		= 'public/uploads/purchase/attachment';
			$config['allowed_types'] 	= 'gif|jpg|png|jpeg|pdf';
	      	$file_name 					= $files['purchase_attachment']['name'][$i];

	      	$ext 						= strtolower(substr($file_name, strrpos($file_name, '.') + 1));
	      	$filename 					= $i.''.time().'.'.$ext;
	      	$config['file_name'] 		= $filename;
	      	if(!file_exists($config['upload_path'])){
	      		mkdir($config['upload_path'], 0777);
	      	}
	  		$this->upload->initialize($config);
			if(!$this->upload->do_upload('purchase_attachment')) return ['msg' => $this->upload->display_errors()];
			$imageinfo = $this->upload->data();
			$full_path = $imageinfo['full_path'];
				
			// check EXIF and autorotate if needed
			// $this->db_operations->image_autorotate_resize(array('filepath' => $full_path), TRUE);		
			$purchase_attachments 					= [];
			$purchase_attachments['cam_purchase_id']   = $purchase_id;
			$purchase_attachments['cam_path'] 		= 'purchase/attachment/'.$filename;
			$purchase_attachments['cam_created_by']   = $_SESSION['user_id'];
			$purchase_attachments['cam_created_at']	= date('Y-m-d H:i:s');
			$purchase_attachments['cam_updated_by']   = $_SESSION['user_id'];
			$purchase_attachments['cam_updated_at']	= date('Y-m-d H:i:s');
			// echo "<pre>"; print_r($purchase_attachments);exit;
			$id = $this->db_operations->data_insert('purchase_attachment_master', $purchase_attachments);
			if($id < 1) return ['msg' => 'Document not inserted in database.'];
			array_push($data, ['id' => $id, 'url' => uploads('purchase/attachment/'.$filename)]);
		}
		return ['status' => TRUE, 'data' => $data,  'msg' => 'Document added successfully.'];
	}

	public function remove_purchase_preview_image($cam_id)
	{
		// $post_data  = $this->input->post();
		// $cam_id         = $post_data['id'];
		// print_r($id); exit;
		$user_id = $this->session->userdata('user_id');
		if(!empty($user_id))
		{
			$this->db_operations->delete_record('purchase_attachment_master', array('cam_id' => $cam_id));	
			
			return['status' => TRUE, 'data' => [], 'msg' => 'Document deleted successfully!!!'];
		} else {
			redirect('Clogin/logout');
		}
	}



		// ================================
	}

?>
