<?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 salesctrl extends CI_Controller {
		public $salesmdl;
		public $excel;
		public $pagination;
		public function __construct()
		{
			parent::__construct();
			$this->load->model('sales/salesmdl','salesmdl');
			$this->load->library('session');
			$this->load->library('db_operations');
			$this->config->load('extra');
			$this->load->library('pagination');
		}

		public function sales_master()
		{
			if(sessionExist()) 
			{
				if( $_GET['action'] == 'view')
				{
					$config 				= array();
					$config 				= $this->config->item('pagination');
					$config['total_rows'] 	= $this->salesmdl->get_all_sales_data_cnt();
					$config['base_url'] 	= base_url("sales/salesctrl/sales_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->salesmdl->get_all_sales_data($config['per_page'],$offset);

					$this->load->view('pages/sales/sales_master', $record);
				}
				else if( $_GET['action'] == 'add' )
				{
					$data = $this->salesmdl->get_sales_form_assets();
					$this->load->view('pages/sales/sales_form', $data);
				}
				else if( $_GET['action'] == 'edit' )
				{
					$sales_id = encrypt_decrypt("decrypt", $_GET['id'], SECRET_KEY);
					$data = $this->salesmdl->get_single_sales_data_for_edit($sales_id);
					$this->load->view('pages/sales/sales_form', $data);
				}
				else if( $_GET['action'] == 'print' )
				{
                    $sales_id = encrypt_decrypt("decrypt", $_GET['id'], SECRET_KEY);
                    $data = $this->salesmdl->get_single_sales_data_for_print($sales_id);
                   
					if( $_GET['type'] == 'export')
                    {
					    $this->load->view('pdfs/sales/sales_export_pdf', $data);
                    }else{
                        $this->load->view('pdfs/sales/sales_pdf', $data);
                    }
				}
			}
			else 
			{
				redirect('loginctrl/logout');	
			}
		}

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

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

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

                $sales_data['sale_type']   = !empty($formdata['sale_type']) ? $formdata['sale_type'] : 0;

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

                if($formdata['pur_with_registered'] == 1){
                    $sales_data['sale_unregi_entry_no'] = isset($formdata['sale_unregi_entry_no']) ? $formdata['sale_unregi_entry_no'] : 0;
                }else{
                    $sales_data['sale_entry_no'] = isset($formdata['sale_no']) ? $formdata['sale_no'] : 0;
                }
                
				$sales_data['sale_entry_date'] = date('Y-m-d', strtotime($formdata['sale_entry_date']));
				$sales_data['sale_invoice_date'] = date('Y-m-d', strtotime($formdata['sale_invoice_date']));
				$sales_data['sale_customer_id']  = $formdata['cust_id'];
				$sales_data['sale_shipping_id'] = $formdata['shipping_id'];
				// $sales_data['sale_dispatch_by'] = $formdata['sale_dispatch_by'];
				// $sales_data['sale_terms']      = $formdata['sale_terms'];
				$sales_data['sale_bill_no'] = $formdata['sale_bill_no'];
				$sales_data['sale_bill_date'] = date('Y-m-d', strtotime($formdata['sale_bill_date']));

                $sales_data['sale_buyer_order_no'] = $formdata['sale_buyer_order_no'];
				$sales_data['sale_buyer_order_date'] = date('Y-m-d', strtotime($formdata['sale_buyer_order_date']));

                $sales_data['sale_dispatch_doc_no'] = $formdata['sale_dispatch_doc_no'];
                $sales_data['sale_dispatched_through'] = $formdata['sale_dispatched_through'];
                $sales_data['sale_destination'] = $formdata['sale_destination'];
				$sales_data['sale_delivery_note_date'] = date('Y-m-d', strtotime($formdata['sale_delivery_note_date']));
                $sales_data['sale_delivery_note'] = $formdata['sale_delivery_note'];
                $sales_data['sale_terms_of_delivery'] = $formdata['sale_terms_of_delivery'];
		
				$sales_data['sale_current_dollar_rate'] = $formdata['sale_current_dollar_rate'];
				$sales_data['sale_acc_type'] = $formdata['acc_type'];
				$sales_data['sale_total_cgst'] = $formdata['total_per_cgst'];
				$sales_data['sale_total_sgst'] = $formdata['total_per_sgst'];
				$sales_data['sale_total_igst'] = $formdata['total_per_igst'];
				$sales_data['sale_total_qty'] = $formdata['total_qty'];
				$sales_data['sale_total_rate'] = $formdata['total_rate'];
				$sales_data['sale_sub_total_amt'] = $formdata['sub_total'];
				$sales_data['sale_total_amt'] = $formdata['final_total'];

				$this->db->trans_begin();

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

				if($id == 0)
				{
					$sales_data['sale_create_userid'] = $_SESSION['user_id'];
					$sales_data['sale_create_date'] = date("Y-m-d H:i:s");
					$sales_data['sale_fin_year'] = $_SESSION['fin_year'];
					
					$result = $this->db_operations->data_insert('sales_master', $sales_data);

					if(!empty($result))
					{
						$trans_result 	= $this->insert_update_sales_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['sales_attachment']['name'][0])){
                            $result1 = $this->upload_sales_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
				{
					$sales_data['sale_update_userid'] = $_SESSION['user_id'];
					$sales_data['sale_update_date'] = date("Y-m-d H:i:s");
					
					$result = $this->db_operations->data_update('sales_master', $sales_data, 'sale_id',$id);

					if(!empty($result))
					{
						$trans_result 	= $this->insert_update_sales_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['sales_attachment']['name'][0])){
                            $result1 = $this->upload_sales_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_sales_trans($formdata, $sales_id)
{
    $flag = 0;

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

    // Get existing sales transactions
    $trans_data = $this->db_operations->get_record('sales_trans', ['st_sale_id' => $sales_id]);

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

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

            $dm_code = $existing['st_dm_code'];

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

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

            // Update diamond_master with new recalculated totals
            $update_after_delete = [
                'dm_stock_out'  => 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['st_id'] as $key => $st_id) {
        $trans = [];

        $trans['st_sale_id']        = $sales_id;
        $trans['st_balance_stock']  = $formdata['st_balance_stock'][$key] ?? 0;
        $trans['st_dm_id']          = $formdata['st_dm_id'][$key] ?? '';
        $trans['st_hsn_id']         = $formdata['st_hsn_id'][$key] ?? '';
        $trans['st_date']           = date('Y-m-d', strtotime($formdata['st_date'][$key])) ?? '';
        $trans['st_dm_code']        = $formdata['st_dm_code'][$key] ?? '';
        $trans['st_price_ct']       = floatval($formdata['st_price_ct'][$key] ?? 0);

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

        $trans['st_dollar']         = floatval($formdata['st_dollar'][$key] ?? 0);
        $trans['st_dollar_amt']     = floatval($formdata['st_dollar_amt'][$key] ?? 0);
        $trans['st_size']           = $formdata['st_size'][$key] ?? '';
        $trans['st_pcs']            = floatval($formdata['st_pcs'][$key] ?? 0);
        $trans['st_cpd']            = $formdata['st_cpd'][$key] ?? '';
        $trans['st_dm_sh']          = $formdata['st_dm_sh'][$key] ?? '';
        $trans['st_lab']            = $formdata['st_lab'][$key] ?? '';
        $trans['st_cert_no']        = $formdata['st_cert_no'][$key] ?? '';

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

        $trans['st_dm_col']         = $formdata['st_dm_col'][$key] ?? '';
        $trans['st_dm_cla']         = $formdata['st_dm_cla'][$key] ?? '';
        $trans['st_dm_cut']         = $formdata['st_dm_cut'][$key] ?? '';
        $trans['st_dm_pol']         = $formdata['st_dm_pol'][$key] ?? '';
        $trans['st_dm_sym']         = $formdata['st_dm_sym'][$key] ?? '';
        $trans['st_dm_fl']          = $formdata['st_dm_fl'][$key] ?? '';
        $trans['st_length']         = $formdata['st_length'][$key] ?? '';
        $trans['st_width']          = $formdata['st_width'][$key] ?? '';
        $trans['st_height']         = $formdata['st_height'][$key] ?? '';
        $trans['st_dm_lab']         = $formdata['st_dm_lab'][$key] ?? '';
        $trans['st_disc']           = $formdata['st_disc'][$key] ?? '';
        $trans['st_rap']            = $formdata['st_rap'][$key] ?? '';
        $trans['st_video']          = $formdata['st_video'][$key] ?? '';
        $trans['st_status']         = $formdata['st_status'][$key] ?? '';
        $trans['st_uploaded_date']  = date('Y-m-d', strtotime($formdata['st_uploaded_date'][$key])) ?? '';
        $trans['st_no_of_days']     = $formdata['st_no_of_days'][$key] ?? '';
        $trans['st_rapnet']         = $formdata['st_rapnet'][$key] ?? '';
        $trans['st_suspense_stock'] = $formdata['st_suspense_stock'][$key] ?? '';

        $trans['st_location'] = $formdata['st_location'][$key] ?? '';
        $trans['st_dna'] = $formdata['st_dna'][$key] ?? '';
        $trans['st_rap_dollar'] = $formdata['st_rap_dollar'][$key] ?? '';
        $trans['st_tbl'] = $formdata['st_tbl'][$key] ?? '';
        $trans['st_td_percentage'] = $formdata['st_td_percentage'][$key] ?? '';
        $trans['st_l_w'] = $formdata['st_l_w'][$key] ?? '';
        $trans['st_bic'] = $formdata['st_bic'][$key] ?? '';
        $trans['st_bis'] = $formdata['st_bis'][$key] ?? '';
        $trans['st_wic'] = $formdata['st_wic'][$key] ?? '';
        $trans['st_col_tinge'] = $formdata['st_col_tinge'][$key] ?? '';
        $trans['st_lus'] = $formdata['st_lus'][$key] ?? '';
        $trans['st_remarks'] = $formdata['st_remarks'][$key] ?? '';
        $trans['st_h_a'] = $formdata['st_h_a'][$key] ?? '';
        $trans['st_girdle'] = $formdata['st_girdle'][$key] ?? '';
        $trans['st_girdle_percentage'] = $formdata['st_girdle_percentage'][$key] ?? '';
        $trans['st_girdle_cond'] = $formdata['st_girdle_cond'][$key] ?? '';
        $trans['st_ca'] = $formdata['st_ca'][$key] ?? '';
        $trans['st_ch'] = $formdata['st_ch'][$key] ?? '';
        $trans['st_pa'] = $formdata['st_pa'][$key] ?? '';
        $trans['st_pd'] = $formdata['st_pd'][$key] ?? '';
        $trans['st_str_ln'] = $formdata['st_str_ln'][$key] ?? '';
        $trans['st_lr_half'] = $formdata['st_lr_half'][$key] ?? '';
        $trans['st_dm_culet'] = $formdata['st_dm_culet'][$key] ?? '';
        $trans['st_kts'] = $formdata['st_kts'][$key] ?? '';
        $trans['st_rep_com'] = $formdata['st_rep_com'][$key] ?? '';
        $trans['st_cert_date'] = !empty($formdata['st_cert_date'][$key]) ? 
        date('Y-m-d', strtotime($formdata['st_cert_date'][$key])) : '';
        $trans['st_image_link'] = $formdata['st_image_link'][$key] ?? '';
        $trans['st_certificate_link'] = $formdata['st_certificate_link'][$key] ?? '';
        $trans['st_category'] = $formdata['st_category'][$key] ?? 0;


        // GST & totals
        $trans['st_per_cgst']      = floatval($formdata['st_cgst'][$key] ?? 0);
        $trans['st_amt_cgst']      = floatval($formdata['st_cgst_total'][$key] ?? 0);
        $trans['st_per_sgst']      = floatval($formdata['st_sgst'][$key] ?? 0);
        $trans['st_amt_sgst']      = floatval($formdata['st_sgst_total'][$key] ?? 0);
        $trans['st_per_igst']      = floatval($formdata['st_igst'][$key] ?? 0);
        $trans['st_amt_igst']      = floatval($formdata['st_igst_total'][$key] ?? 0);
        $trans['st_sub_total_amt'] = floatval($formdata['st_sub_total'][$key] ?? 0);
        $trans['st_total_amt']     = floatval($formdata['st_total'][$key] ?? 0);

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

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

         // --- Step 3: Sync with diamond_master ---
        $dm_code = $trans['st_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->salesmdl->get_sales_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_sale_ct     = floatval($summary[0]['price_sale_ct']);
        $dollar_sale_ct    = floatval($summary[0]['dollar_sale_ct']);

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

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

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

        $update_data = [
            'dm_stock_out'     => $total_carat,
        ];

        // 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'];
        }

        $flag = 1;
    }

    return $flag ? 1 : 0;
}

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

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

                if (!empty($trans_data)) {

                    foreach ($trans_data as $row) {

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

                        $st_id = $row['st_id'];

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

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

                        // --- STEP 4: Prepare updated fields ---
                        $update_data = [
                            // Stock = total carat
                            'dm_stock_out'  => 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 sales_master record ---
                $this->db_operations->delete_record('sales_master', [
                    'sale_id' => $id
                ]);

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

		// ================================
		// 		sales Report
		public function hsnwise_sales_report()
		{
			if(sessionExist())
			{
				// $record['data']	= $this->salesmdl->get_all_hsnwise_sales_data_print();
				$record="";
				if(isset($_GET['action']))
				{
					if ($_GET['action'] == "print") 
					{
						$record['data']	= $this->salesmdl->get_all_hsnwise_sales_data_print();
						$this->load->view('pdfs/report/hsnwise_sales_report_pdf', $record);
					}
				}
				else
				{
					$this->load->view('pages/sales/hsnwise_sales_report', $record);	
				}
				
			}
			else
			{
				$this->load->view('pages/login');
			}
		}

		public function hsnwise_sales_report_pdf()
		{
			if(sessionExist())
			{
				$record['data']	= $this->salesmdl->get_all_hsnwise_sales_data_print();
				$this->load->view('pdfs/report/hsnwise_sales_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->salesmdl->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->salesmdl->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->salesmdl->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($customer_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' => 5,
            'acc_id'     => $customer_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;

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

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

        if(!empty($row['BI'])){
            if($row['BI'] == 'YES' || $row['BI'] == 'yes'){
                $category = 1;
            }else{
                $category = 0;
            }
        }else{
                $category = 0;
        }

        

		// 1) Collect total PCS per stock id from merged_data
$stock_sum = [];

foreach ($merged_data as $r) {
    $sid = trim($r['B']);
    $pcs = floatval($r['N']);

    if (!isset($stock_sum[$sid])) {
        $stock_sum[$sid] = 0;
    }

    $stock_sum[$sid] += $pcs;
}

// 2) Validate each stock_id balance
foreach ($stock_sum as $sid => $total_pcs) {

    $diamond_data = $this->db
        ->select('dm_stock_in, dm_stock_out')
        ->get_where('diamond_master', ['dm_code' => $sid])
        ->row_array();

    if (!$diamond_data) {
        echo json_encode([
            'resp' => 0,
            'msg'  => "$sid Stock Id not found in diamond master!"
        ]);
        return;
    }

    // Balance stock = Stock In – Stock Out
    $balance_stock = (float)$diamond_data['dm_stock_in'] - (float)$diamond_data['dm_stock_out'];

    if ($total_pcs > $balance_stock) {
        echo json_encode([
            'resp' => 0,
            'msg'  => "$sid Stock Id exceeded the balance Carat!!! 
                        (Available: $balance_stock, Required: $total_pcs)"
        ]);
        return;
    }
}




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

        // Prepare final sales data
        $sales_data = [
            'st_date'           => !empty($row['A']) ? date('d-m-Y', strtotime($row['A'])) : '',
            'st_dm_code'        => $stock_id,
            'st_hsn_code'       => $hsn_code,
            'st_hsn_id'         => $hsn_id,
            'st_price_ct'       => round($st_price_ct, 2),
            'st_pc_amt'         => round($st_pc_amt, 2),
            'st_dollar'         => round($st_dollar_ct, 2),
            'st_dollar_amt'     => round($st_dollar_amt, 2),
            'st_size'           => $row['H'],
            'st_pcs'            => $row['I'],
            'st_cpd'            => $row['J'],
            'st_dm_sh'          => $row['K'],
            'st_lab'            => $row['L'],
            'st_cert_no'        => $row['M'],
            'st_carat'          => $pcs,
            'st_dm_col'         => $row['O'],
            'st_dm_cla'         => $row['P'],
            'st_dm_cut'         => $row['Q'],
            'st_dm_pol'         => $row['R'],
            'st_dm_sym'         => $row['S'],
            'st_dm_fl'          => $row['T'],
            'st_length'         => $row['U'],
            'st_width'          => $row['V'],
            'st_height'         => $row['W'],
            'st_dm_lab'         => $row['X'],
            'st_disc'           => $row['Y'],
            'st_rap'            => $row['Z'],
            'st_video'          => $row['AA'],
            'st_status'         => $row['AB'] ?? '',
            'st_uploaded_date'  => !empty($row['AC']) ? date('d-m-Y', strtotime($row['AC'])) : date('d-m-Y'),
            'st_no_of_days'     => $row['AD'] ?? '',
            'st_rapnet'         => $row['AE'] ?? '',
            'st_suspense_stock' => $row['AF'] ?? '',



            'st_location' => $row['AG'] ?? '',
            'st_dna' => $row['AH'] ?? '',
            'st_rap_dollar' => $row['AI'] ?? '',
            'st_tbl' => $row['AJ'] ?? '',
            'st_td_percentage' => $row['AK'] ?? '',
            'st_l_w' => $row['AL'] ?? '',
            'st_bic' => $row['AM'] ?? '',
            'st_bis' => $row['AN'] ?? '',
            'st_wic' => $row['AO'] ?? '',
            'st_col_tinge' => $row['AP'] ?? '',
            'st_lus' => $row['AQ'] ?? '',
            'st_remarks' => $row['AR'] ?? '',
            'st_h_a' => $row['AS'] ?? '',
            'st_girdle' => $row['AT'] ?? '',
            'st_girdle_percentage' => $row['AU'] ?? '',
            'st_girdle_cond' => $row['AV'] ?? '',
            'st_ca' => $row['AW'] ?? '',
            'st_ch' => $row['AX'] ?? '',
            'st_pa' => $row['AY'] ?? '',
            'st_pd' => $row['AZ'] ?? '',
            'st_str_ln' => $row['BA'] ?? '',
            'st_lr_half' => $row['BB'] ?? '',
            'st_dm_culet' => $row['BC'] ?? '',
            'st_kts' => $row['BD'] ?? '',
            'st_rep_com' => $row['BE'] ?? '',
            'st_cert_date'  => !empty($row['BF']) ? date('d-m-Y', strtotime($row['BF'])) : date('d-m-Y'),
            'st_image_link' => $row['BG'] ?? '',
            'st_certificate_link' => $row['BH'] ?? '',
            'st_category' => $category,




            'st_cgst'           => $cgst,
            'st_sgst'           => $sgst,
            'st_igst'           => $igst,
            'st_amt_cgst'       => $amt_cgst,
            'st_amt_sgst'       => $amt_sgst,
            'st_amt_igst'       => $amt_igst,
            'st_total_amt'      => $total_amt,
            'st_user_id'        => $_SESSION['user_id'],
            'st_balance_stock' => $balance_stock ?? 0
        ];

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

        $imported_data[] = $sales_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 customer_insert($acc_id = 0)
{
    if (!sessionExist()) {
        redirect('loginctrl/logout');
        return;
    }

    $form_data = $this->input->post();
        // echo "<pre>"; print_r($form_data); exit;
    
    $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;
                }
            }
        }
    }

    $form_data['acc_contact']   = $form_data['acc_contact'];
    $form_data['acc_owner']     = $form_data['acc_contact'];
    $form_data['acc_name']      = $form_data['acc_contact'];
    $form_data['acc_grp_id']    = 5;

    // Check if already exists
    $cnt = $this->salesmdl->get_cnt('account_master', ['acc_owner' => $form_data['acc_contact']]);
    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_contact']
        ]);
    } else {
        echo json_encode(['status' => 0]);
    }
}





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

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

	        unset($config);
			$config 					= array();
			$config['upload_path'] 		= 'public/uploads/sales/attachment';
			$config['allowed_types'] 	= 'gif|jpg|png|jpeg|pdf';
	      	$file_name 					= $files['sales_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('sales_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);		
			$sales_attachments 					= [];
			$sales_attachments['cam_sale_id']   = $sales_id;
			$sales_attachments['cam_path'] 		= 'sales/attachment/'.$filename;
			$sales_attachments['cam_created_by']   = $_SESSION['user_id'];
			$sales_attachments['cam_created_at']	= date('Y-m-d H:i:s');
			$sales_attachments['cam_updated_by']   = $_SESSION['user_id'];
			$sales_attachments['cam_updated_at']	= date('Y-m-d H:i:s');
			// echo "<pre>"; print_r($sales_attachments);exit;
			$id = $this->db_operations->data_insert('sales_attachment_master', $sales_attachments);
			if($id < 1) return ['msg' => 'Document not inserted in database.'];
			array_push($data, ['id' => $id, 'url' => uploads('sales/attachment/'.$filename)]);
		}
		return ['status' => TRUE, 'data' => $data,  'msg' => 'Document added successfully.'];
	}

	public function remove_sales_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('sales_attachment_master', array('cam_id' => $cam_id));	
			
			return['status' => TRUE, 'data' => [], 'msg' => 'Document deleted successfully!!!'];
		} else {
			redirect('Clogin/logout');
		}
	}

    public function get_balance_stock() {

    $dm_code = trim($this->input->post('dm_code'));

			

    $this->db->select('dm_code, dm_stock_in, dm_stock_out');
    $data = $this->db_operations->get_record('diamond_master', [
        'dm_code' => $dm_code
    ]);

    $final = [];

    foreach ($data as $key => $d) {
        $stock_in  = floatval($d['dm_stock_in']);
        $stock_out = floatval($d['dm_stock_out']);
        $final[$d['dm_code']] = $stock_in - $stock_out;
    }
			// echo "<pre>"; print_r($final);exit;

    echo json_encode([
        'resp' => 1,
        'balance_list' => $final,
    ]);
}
  public function get_total_balance_stock() {

    $data = $this->db
        ->select('dm_code, dm_stock_in, dm_stock_out')
        ->get('diamond_master')
        ->result_array();

    $final = [];

    foreach ($data as $d) {
        $stock_in  = floatval($d['dm_stock_in']);
        $stock_out = floatval($d['dm_stock_out']);
        $final[$d['dm_code']] = $stock_in - $stock_out;
    }
			// echo "<pre>"; print_r($final);exit;

    echo json_encode([
        'resp' => 1,
        'balance_list' => $final,
    ]);
}

    public function get_account_days()
    {
        $account_id = $this->input->post('account_id');

        $data = $this->db
                    ->select('acc_days')
                    ->where('acc_id', $account_id)
                    ->where('acc_grp_id', 5)
                    ->get('account_master')
                    ->row_array();

        if (!empty($data)) {
            echo json_encode([
                'status'   => true,
                'acc_days' => $data['acc_days']
            ]);
        }
    }
    
    public function export_excel()
    {
        $sales_id = $this->input->get('sales_id');

        if (empty($sales_id)) {
            show_error('Invalid Sales ID');
        }

        $data = $this->salesmdl->get_export_data($sales_id);

        // Prevent corruption
        if (ob_get_length()) {
            ob_end_clean();
        }

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        /* ================= HEADERS ================= */

        $columns = [
            'Invoice No.','Item No.','RITC','Item Description','Qty.','Unit of Qty',
            'Unit Price','Per','PMV Unit Price','Scheme Code','Drawback Sr. No.',
            'DBK Qty','DBK Qty Unit','Reward Item','STR Code','End Use',
            'IGST Payment Status','Taxable Value','IGST Amount','IGST Rate',
            'Compensation Cess Amount','State Code','District Code',
            'Standard Qty','Standard Qty Unit','FTA Code','Accessory Status',
            'RODTEP','STATEMENT CODE','LGD INFO CODE'
        ];

        $col = 'A';
        foreach ($columns as $header) {
            $sheet->setCellValue($col.'1', $header);
            $sheet->getStyle($col.'1')->getFont()->setBold(true);
            $col++;
        }

        /* ================= DATA ================= */

        $row = 2;
        $srNo = 1;

        foreach ($data as $record) {

            // Build description
            if ($record['dm_category'] == 1) {
                $descParts = [
                    $record['st_lab'],
                    $record['st_cert_no'],
                    $record['st_dm_sh'],
                    $record['st_dm_col'],
                    $record['st_dm_cla'],
                    $record['st_dm_cut'],
                    $record['st_dm_pol'],
                    $record['st_dm_sym'],
                    $record['st_dm_fl'],
                    $record['st_pcs']
                ];
            } else {
                $descParts = [
                    $record['st_cpd'],
                    $record['st_dm_sh'],
                    $record['st_dm_col'],
                    $record['st_dm_cla'],
                    $record['st_size'],
                    $record['st_pcs']
                ];
            }

            $itemDescription = implode(' ', array_filter($descParts));

            // LGD logic
            $lgdCode = '';
            if (!empty($record['st_dm_lab'])) {
                $lab = strtoupper(trim($record['st_dm_lab']));
                if ($lab == 'CVD') {
                    $lgdCode = 'LGD001';
                } elseif ($lab == 'HPHT') {
                    $lgdCode = 'LGD002';
                }
            }

            /* ================= WRITE ROW ================= */

            $sheet->setCellValue('A'.$row, 'EXP/'.$record['invoice_no'].'/'.$record['sale_fin_year']);
            $sheet->setCellValue('B'.$row, $srNo);
            $sheet->setCellValue('C'.$row, $record['hsn_code']);
            $sheet->setCellValue('D'.$row, $itemDescription);
            $sheet->setCellValue('E'.$row, (float)$record['st_carat']);
            $sheet->setCellValue('F'.$row, 'CTM');
            $sheet->setCellValue('G'.$row, (float)$record['st_dollar']);
            $sheet->setCellValue('H'.$row, '');
            $sheet->setCellValue('I'.$row, (float)$record['st_dollar_amt']);
            $sheet->setCellValue('J'.$row, '00');
            $sheet->setCellValue('K'.$row, '');
            $sheet->setCellValue('L'.$row, '');
            $sheet->setCellValue('M'.$row, '0.000');
            $sheet->setCellValue('N'.$row, '');
            $sheet->setCellValue('O'.$row, '');
            $sheet->setCellValue('P'.$row, 'GNX100');
            $sheet->setCellValue('Q'.$row, 'LUT');
            $sheet->setCellValue('R'.$row, '0.00');
            $sheet->setCellValue('S'.$row, '0.00');
            $sheet->setCellValue('T'.$row, '');
            $sheet->setCellValue('U'.$row, '0.000000');
            $sheet->setCellValue('V'.$row, '27');
            $sheet->setCellValue('W'.$row, '483');
            $sheet->setCellValue('X'.$row, (float)$record['st_carat']);
            $sheet->setCellValue('Y'.$row, 'CTM');
            $sheet->setCellValue('Z'.$row, 'NCPTI');
            $sheet->setCellValue('AA'.$row, '0');
            $sheet->setCellValue('AB'.$row, 'RODTEPY');
            $sheet->setCellValue('AC'.$row, 'RD001');
            $sheet->setCellValue('AD'.$row, $lgdCode);

            $row++;
            $srNo++;
        }

        /* ================= GRAND TOTAL ROW ================= */

        $sheet->setCellValue('D'.$row, 'GRAND TOTAL');
        $sheet->getStyle('D'.$row)->getFont()->setBold(true);

        // Excel auto formula totals
        $sheet->setCellValue('E'.$row, '=SUM(E2:E'.($row-1).')');
        $sheet->setCellValue('I'.$row, '=SUM(I2:I'.($row-1).')');
        $sheet->setCellValue('X'.$row, '=SUM(X2:X'.($row-1).')');

        $sheet->getStyle('E'.$row.':X'.$row)->getFont()->setBold(true);

        /* ================= NUMBER FORMATTING ================= */

        $sheet->getStyle('E2:E'.$row)
            ->getNumberFormat()
            ->setFormatCode('#,##0.000');

        $sheet->getStyle('I2:I'.$row)
            ->getNumberFormat()
            ->setFormatCode('#,##0.00');

        $sheet->getStyle('X2:X'.$row)
            ->getNumberFormat()
            ->setFormatCode('#,##0.000');

        /* ================= COLUMN WIDTH ================= */

        foreach (range('A', 'AD') as $colID) {
            if ($colID != 'D') {
                $sheet->getColumnDimension($colID)->setAutoSize(true);
            }
        }

        $sheet->getColumnDimension('D')->setWidth(45);

        $sheet->getStyle('D2:D'.$row)
            ->getAlignment()
            ->setWrapText(true);

        $sheet->freezePane('A2');

        /* ================= DOWNLOAD ================= */

        $filename = 'CPD_' . date('d-m-Y') . '.xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        header('Pragma: public');

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

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

?>
