';
        // print_r($ids); die;

        if($export_data['insurer_or_tpa'] == 'insurer') //check CD amt related issue for only insurer, not tpa
        {
            if (!empty($cash_balance)) {
                if ((int) $cash_balance['balance']  < (int) $rounded_totals) {
                    $this->myLogger->logme('error', 'Inception export failed due to insufficient deposit amount.');
                    $this->myLogger->logme('error', 'Inception export failed due to insufficient deposit amount.  CASH BALANCE : {balance}  and  TOTAL AMOUNT : {total}', ['balance' => $cash_balance['balance'], 'total' => $totals]);
                    return 0;
                }
            }
        }

        $count = count($objects);
        $export_data['count'] = $count;
        $export_data['amount'] = $rounded_totals;
        $export_data['status'] = 'success';

        $this->myLogger->logme('error', 'SI_Enhancement export data count : {data}', ['data' => $count]);

        if ($count == 0) {

            $this->myLogger->logme('error', 'No Record found. Endorsement ID Alread Updated. SI_Enhancement export data count : {data}', ['data' => $count]);
            return false;
        }

        $this->removeOldExportInfoFromBatchFile($export_data);
        $this->myLogger->logme('error', 'SI_Enhancement export file name : {data}', ['data' => $export_data['file_name']]);


        // get excel export format structure array
        $template_json = $this->clientPolicyModel
            ->select('insurer_excel_export_template.jsoncolumns')
            ->join('insurer_excel_export_template', 'insurer_excel_export_template.insurer_id = client_policy.insurer_id and insurer_excel_export_template.policy_type_id = client_policy.policy_type_id')
            ->where('client_policy.id', $export_data['client_policy_id'])
            ->where('insurer_excel_export_template.event_name', $export_data['event_type'])
            ->where('insurer_excel_export_template.is_active', 1) //Live issue changes 17-10-2024
            ->where('insurer_excel_export_template.type_name', $export_data['actions'])
            ->first();
        
        if(!empty($template_json) && $template_json != null){
            $excel_header_columns = json_decode($template_json['jsoncolumns'], true); 
        }else{
            return 6;
        }    

        if( $export_data['insurer_or_tpa'] == 'tpa'){
            
            $excel_header_columns = [
                ['column_index' => 0, 'column_name' => 'S.No', 'db_column_name' => 'index'],
                ['column_index' => 1, 'column_name' => 'NAME OF EMP/DEP', 'db_column_name' => 'emp_name'],
                ['column_index' => 2, 'column_name' => 'EMP ID', 'db_column_name' => 'emp_code'],
                ['column_index' => 3, 'column_name' => 'EMP/DEP TYPE', 'db_column_name' => 'emp_type'],
                ['column_index' => 4, 'column_name' => 'RELATION', 'db_column_name' => 'emp_relationship_code'],
                ['column_index' => 5, 'column_name' => 'DOB', 'db_column_name' => 'emp_dob'],
                ['column_index' => 6, 'column_name' => 'GENDER', 'db_column_name' => 'emp_gender'],
                ['column_index' => 7, 'column_name' => 'PRE EXISTING AILMENTS', 'db_column_name' => 'pre_existing_alignments'],
                ['column_index' => 8, 'column_name' => 'BASIC COVER SI', 'db_column_name' => 'new_basic_cover_si'],
                ['column_index' => 9, 'column_name' => 'Old Sum Insured', 'db_column_name' => 'old_basic_cover_si'],
                ['column_index' => 10, 'column_name' => 'Date of Coverage', 'db_column_name' => 'date_of_coverage'],
                ['column_index' => 11, 'column_name' => 'Policy End Date', 'db_column_name' => 'policy_end_date'],
                ['column_index' => 12, 'column_name' => 'No Of Days', 'db_column_name' => 'no_of_days'],
                ['column_index' => 13, 'column_name' => 'Old SI Premium', 'db_column_name' => 'old_si_premium'],
                ['column_index' => 14, 'column_name' => 'New SI premium', 'db_column_name' => 'new_si_premium'],
                ['column_index' => 15, 'column_name' => 'Difference premium', 'db_column_name' => 'difference_premium'],
                ['column_index' => 16, 'column_name' => 'Pro Rata Premium', 'db_column_name' => 'pro_rata_premium'],
                ['column_index' => 17, 'column_name' => 'GST', 'db_column_name' => 'gst'],
                ['column_index' => 18, 'column_name' => 'Total', 'db_column_name' => 'total'],
                ['column_index' => 19, 'column_name' => 'ENDORSEMENT_ID', 'db_column_name' => null]
            ];
            
        }

        $excel_data_info = generate_insurer_based_excel($excel_header_columns, $objects);

        // Generate Excel file
        $tempFile = tmpfile();
        $success = generate_excel($excel_data_info['header'], $excel_data_info['data'], $tempFile);

        if ($success) {
            $return = $this->batchFilesAndBatchListEntry($export_data, $objects);
            if ($return) {

                foreach ($ids as $key => $id) {
                    $group_key = $this->empEndorsementModel->select('group_key')->where('id', $id)->first();
                    if ($group_key) {
                        $this->empEndorsementModel->where('group_key', $group_key)->set('status', 'inprogress')->update();
                    }
                }

                // Set the appropriate headers for Excel file download
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="' . $export_data['file_name'] . '"');
                header('Cache-Control: max-age=0');

                // Rewind the temporary file pointer
                rewind($tempFile);

                // Output the contents of the temporary file to the browser
                fpassthru($tempFile);

                // Close and remove the temporary file
                fclose($tempFile);

                return true;
            } else {
                return false;
            }
        }
    }


    public function generateExcelForDeletion($export_data)
    {   
        // dd($export_data);

        $ids = [];

        $objects = $this->employeePolicyModel->getDeletionEmployeeDataForExportExcel($export_data);

        // dd($objects);

        $totals = 0;
        foreach ($objects as $obj) {
            $ids[] = $obj->endorsement_primarykey;
            $totals += $obj->total;
        }
        $rounded_totals = round($totals, 2);

        $count = count($objects);
        $export_data['count'] = $count;
        $export_data['amount'] = $rounded_totals;
        $export_data['status'] = 'success';

        $this->myLogger->logme('error', 'Deletion export data count : {data}', ['data' => $count]);

        if ($count == 0) {

            $this->myLogger->logme('error', 'No Record found. Endorsement ID Alread Updated. Deletion export data count : {data}', ['data' => $count]);
            return false;
        }

        $this->removeOldExportInfoFromBatchFile($export_data);
        $this->myLogger->logme('error', 'Deletion export file name : {data}', ['data' => $export_data['file_name']]);

        // get excel export format structure array
        $template_json = $this->clientPolicyModel
            ->select('insurer_excel_export_template.jsoncolumns')
            ->join('insurer_excel_export_template', 'insurer_excel_export_template.insurer_id = client_policy.insurer_id and insurer_excel_export_template.policy_type_id = client_policy.policy_type_id')
            ->where('client_policy.id', $export_data['client_policy_id'])
            ->where('insurer_excel_export_template.event_name', $export_data['event_type'])
            ->where('insurer_excel_export_template.is_active', 1) //Live issue changes 17-10-2024
            ->where('insurer_excel_export_template.type_name', $export_data['actions'])
            ->first();
        
        if(!empty($template_json) && $template_json != null){
            $excel_header_columns = json_decode($template_json['jsoncolumns'], true); 
        }else{
            return 6;
        }  

        if( $export_data['insurer_or_tpa'] == 'tpa'){
            
            $excel_header_columns = [
                ['column_index' => 0, 'column_name' => 'S.No', 'db_column_name' => 'index'],
                ['column_index' => 1, 'column_name' => 'EMP ID', 'db_column_name' => 'emp_code'],
                ['column_index' => 2, 'column_name' => 'EMP NAME', 'db_column_name' => 'emp_name'],
                ['column_index' => 3, 'column_name' => 'DOB', 'db_column_name' => 'emp_dob'],
                ['column_index' => 4, 'column_name' => 'GENDER', 'db_column_name' => 'emp_gender'],
                ['column_index' => 5, 'column_name' => 'RELATIONSHIP', 'db_column_name' => 'emp_relationship_code'],
                ['column_index' => 6, 'column_name' => 'SUM INSURED', 'db_column_name' => 'basic_cover_si'],
                ['column_index' => 7, 'column_name' => 'Date of Leaving', 'db_column_name' => 'date_of_leaving'],
                ['column_index' => 8, 'column_name' => 'Policy End Date', 'db_column_name' => 'policy_end_date'],
                ['column_index' => 9, 'column_name' => 'No Of Days', 'db_column_name' => 'no_of_days'],
                ['column_index' => 10, 'column_name' => 'Premium', 'db_column_name' => 'premium'],
                ['column_index' => 11, 'column_name' => 'Pro Rata Premium', 'db_column_name' => 'pro_rata_premium'],
                ['column_index' => 12, 'column_name' => 'GST', 'db_column_name' => 'gst'],
                ['column_index' => 13, 'column_name' => 'Total', 'db_column_name' => 'total'],
                ['column_index' => 14, 'column_name' => 'Claim Status', 'db_column_name' => null],
                ['column_index' => 15, 'column_name' => 'ENDORSEMENT_ID', 'db_column_name' => null]
            ];

        }

        $excel_data_info = generate_insurer_based_excel($excel_header_columns, $objects);

        // Generate Excel file
        $tempFile = tmpfile();
        $success = generate_excel($excel_data_info['header'], $excel_data_info['data'], $tempFile);

        if ($success) {
            $return = $this->batchFilesAndBatchListEntry($export_data, $objects);
            if ($return) {

                foreach ($ids as $key => $id) {
                    $group_key = $this->empEndorsementModel->select('group_key')->where('id', $id)->first();
                    if ($group_key) {
                        $this->empEndorsementModel->where('group_key', $group_key)->set('status', 'inprogress')->update();
                    }
                }

                // Set the appropriate headers for Excel file download
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="' . $export_data['file_name'] . '"');
                header('Cache-Control: max-age=0');

                // Rewind the temporary file pointer
                rewind($tempFile);

                // Output the contents of the temporary file to the browser
                fpassthru($tempFile);

                // Close and remove the temporary file
                fclose($tempFile);

                return true;
            } else {
                return false;
            }
        }
    }


    public function generateExcelForAllEventType($export_data)
    {

        $additionData = [];
        $deletionData = [];
        $inceptionData = [];
        $correctionData = []; 
        $enhancementData = []; 
        $dependentAdditionData = []; 

        $event_type_array = $export_data['event_type'];

        foreach ($export_data['event_type'] as $value) 
        {

            if($value == 'addition'){
                $export_data['event_type'] = 'addition';
                $additionData = $this->employeePolicyModel->getInceptionEmployeeDataForExportExcel($export_data);

            }else if($value == 'inception'){
                $export_data['event_type'] = 'inception';
                $inceptionData = $this->employeePolicyModel->getInceptionEmployeeDataForExportExcel($export_data); //Live issue changes 17-10-2024

            }else if($value == 'dependent_addition'){
                $export_data['event_type'] = 'dependent_addition';
                $dependentAdditionData = $this->employeePolicyModel->getInceptionEmployeeDataForExportExcel($export_data);

            }else if($value == 'deletion'){
                $export_data['event_type'] = 'deletion';
                $deletionData = $this->employeePolicyModel->getDeletionEmployeeDataForExportExcel($export_data);

            }else if($value == 'si_enhancement'){
                $export_data['event_type'] = 'si_enhancement';
                $enhancementData = $this->employeePolicyModel->getSIEnhancementEmployeesDataForExportExcel($export_data);

            }else if($value == 'correction'){
                $export_data['event_type'] = 'correction';
                $correctionData = $this->employeePolicyModel->getCorrectionEmployeesDataForExportExcel($export_data);

            }
          
        }

        if(empty($additionData) && empty($deletionData) && empty($inceptionData) && empty($correctionData) && empty($enhancementData) && empty($dependentAdditionData)){
           
            return 0;
        }

        //for cd tranction and cd master data is empty check
        if(!empty($inceptionData) && $inceptionData != null){

            $policy_details = $this->clientPolicyModel->where('id', $export_data['client_policy_id'])->first();

            if ($policy_details['cd_ac_no'] == null) {
                $this->myLogger->logme('error', 'The policy does not have a CD account number.');
                return 5;
            }

            $cash_balance = $this->clientDepositModel->where('cd_ac_no', $policy_details['cd_ac_no'])->orderBy('id', 'DESC')->first();

            if ($cash_balance == null) {
                $balance = $this->CDMasterModel
                    ->where('client_id', $export_data['client_id'])
                    ->where('insurer_id', $policy_details['insurer_id'])
                    ->where('cd_ac_no', $policy_details['cd_ac_no'])
                    ->first();

                $cash_balance['balance'] = $balance['opening_bal'];
            }


            
                // Calculate the total amount from the objects
                $totals = 0;
                foreach ($inceptionData as $item) {
                    $totals =  $totals + $item->total;
                }
             $totals = round($totals, 2);
            
            if($export_data['insurer_or_tpa'] != 'tpa')// check overal emp premium amt with cd balance only for insurer export, not tpa export
            { 
                if (!empty($cash_balance)) {
                    if ((int) $cash_balance['balance']  < (int) $totals) {
                        $this->myLogger->logme('error', 'Inception export failed due to insufficient deposit amount.');
                        $this->myLogger->logme('error', 'Inception export failed due to insufficient deposit amount.  CASH BALANCE : {balance}  and  TOTAL AMOUNT : {total}', ['balance' => $cash_balance['balance'], 'total' => $totals]);
                        return 0;
                    }
                }
            }
        }

        $this->removeOldExportInfoFromBatchFile($export_data);


        // Merging all data into a single array
        $mergedData = array_merge($inceptionData, $additionData, $dependentAdditionData, $correctionData, $enhancementData, $deletionData);

        // dd($inceptionData, $additionData, $dependentAdditionData, $correctionData, $enhancementData,  $deletionData, $mergedData);


        $template_json = $this->clientPolicyModel
            ->select('insurer_excel_export_template.jsoncolumns')
            ->join('insurer_excel_export_template', 'insurer_excel_export_template.insurer_id = client_policy.insurer_id and insurer_excel_export_template.policy_type_id = client_policy.policy_type_id')
            ->where('client_policy.id', $export_data['client_policy_id'])
            ->where('insurer_excel_export_template.event_name', 'all')
            ->where('insurer_excel_export_template.is_active', 1) //Live issue changes 17-10-2024
            ->where('insurer_excel_export_template.type_name', $export_data['actions'])
            ->first();


        if(!empty($template_json) && $template_json != null){
            $excel_header_columns = json_decode($template_json['jsoncolumns'], true); 
        }else{
            return 6;
        } 


        // Generating Excel data
        $excel_data_info = generate_insurer_based_excel($excel_header_columns, $mergedData);

        // Generate Excel file
        $tempFile = tmpfile();
        $success = generate_excel($excel_data_info['header'], $excel_data_info['data'], $tempFile);

        // If Excel generation is successful
        if ($success) {

            $random_number_count = 4;
            $batch_code = generate_random_string($random_number_count);
            session()->set('batch_code', $batch_code);

            foreach ($event_type_array as $value) 
            {
                $random_number_count = 4;
                $export_data['batch_code'] = session()->get('batch_code');
                $export_data['created_by'] = get_session_userid();

                if($value == 'addition'){
                   
                    if(!empty($additionData) && $additionData != null){

                        $count = count($additionData);
                        $export_data['count'] = $count;
                        $export_data['status'] = 'success';
                        $export_data['event_type'] = 'addition';

                        $insert = $this->batchFileModel->insert($export_data);
                        $batch_file_batch_code = $this->batchFileModel->where('id', $insert)->first();
                        // $this->insertBatchList(['batch_code' => $batch_code, 'batch_list_data' => $objects ]);
        
                        $job_details  = new Jobs();
                        $r = Jobs::addJob(['job_name' => 'insertBatchList', 'payload' => ['batch_code' => $batch_code, 'user_id' => get_session_userid(), 'batch_list_data' => $additionData]]);
    
                    }
    
                }else if($value == 'inception'){

                    if(!empty($inceptionData) && $inceptionData != null){

                        $count = count($inceptionData);
                        $export_data['count'] = $count;
                        $export_data['status'] = 'success';
                        $export_data['event_type'] = 'inception';

                        $insert = $this->batchFileModel->insert($export_data);
                        $batch_file_batch_code = $this->batchFileModel->where('id', $insert)->first();
                        // $this->insertBatchList(['batch_code' => $batch_code, 'batch_list_data' => $objects ]);
        
                        $job_details  = new Jobs();
                        $r = Jobs::addJob(['job_name' => 'insertBatchList', 'payload' => ['batch_code' => $batch_code, 'user_id' => get_session_userid(), 'batch_list_data' => $inceptionData]]);
                    }

                }else if($value == 'dependent_addition'){
                     
                    if(!empty($dependentAdditionData) && $dependentAdditionData != null){

                        $count = count($dependentAdditionData);
                        $export_data['count'] = $count;
                        $export_data['status'] = 'success';
                        $export_data['event_type'] = 'dependent_addition';

                        $insert = $this->batchFileModel->insert($export_data);
                        $batch_file_batch_code = $this->batchFileModel->where('id', $insert)->first();
                        // $this->insertBatchList(['batch_code' => $batch_code, 'batch_list_data' => $objects ]);
        
                        $job_details  = new Jobs();
                        $r = Jobs::addJob(['job_name' => 'insertBatchList', 'payload' => ['batch_code' => $batch_code, 'user_id' => get_session_userid(), 'batch_list_data' => $dependentAdditionData]]);
                    }
                }else if($value == 'deletion'){

                    if(!empty($deletionData) && $deletionData != null){

                        $count = count($deletionData);
                        $export_data['count'] = $count;
                        $export_data['status'] = 'success';
                        $export_data['event_type'] = 'deletion';

                        $insert = $this->batchFileModel->insert($export_data);
                        $batch_file_batch_code = $this->batchFileModel->where('id', $insert)->first();
                        // $this->insertBatchList(['batch_code' => $batch_code, 'batch_list_data' => $objects ]);
        
                        $job_details  = new Jobs();
                        $r = Jobs::addJob(['job_name' => 'insertBatchList', 'payload' => ['batch_code' => $batch_code, 'user_id' => get_session_userid(), 'batch_list_data' => $deletionData]]);
                    }

                }else if($value == 'si_enhancement'){

                    if(!empty($enhancementData) && $enhancementData != null){

                        $count = count($enhancementData);
                        $export_data['count'] = $count;
                        $export_data['status'] = 'success';
                        $export_data['event_type'] = 'si_enhancement';

                        $insert = $this->batchFileModel->insert($export_data);
                        $batch_file_batch_code = $this->batchFileModel->where('id', $insert)->first();
                        // $this->insertBatchList(['batch_code' => $batch_code, 'batch_list_data' => $objects ]);
        
                        $job_details  = new Jobs();
                        $r = Jobs::addJob(['job_name' => 'insertBatchList', 'payload' => ['batch_code' => $batch_code, 'user_id' => get_session_userid(), 'batch_list_data' => $enhancementData]]);
                    }

                }else if($value == 'correction'){

                    if(!empty($correctionData) && $correctionData != null){

                        $count = count($correctionData);
                        $export_data['count'] = $count;
                        $export_data['status'] = 'success';
                        $export_data['event_type'] = 'correction';

                        $insert = $this->batchFileModel->insert($export_data);
                        $batch_file_batch_code = $this->batchFileModel->where('id', $insert)->first();
                        // $this->insertBatchList(['batch_code' => $batch_code, 'batch_list_data' => $objects ]);
        
                        $job_details  = new Jobs();
                        $r = Jobs::addJob(['job_name' => 'insertBatchList', 'payload' => ['batch_code' => $batch_code, 'user_id' => get_session_userid(), 'batch_list_data' => $correctionData]]);
                    }
                }

            }

            session()->remove('batch_code');

            // If batch operation is successful
            if (true) {

                // Clear the output buffer to avoid any unwanted output
                if (ob_get_level()) {
                    ob_end_clean();
                }

                // Set headers for Excel file download
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment; filename="' . $export_data['file_name'] . '"');
                header('Content-Transfer-Encoding: binary');
                header('Cache-Control: must-revalidate');
                header('Pragma: public');
                header('Expires: 0');

                // Output file contents
                rewind($tempFile);
                fpassthru($tempFile);

                // Close and remove temporary file
                fclose($tempFile);

                return true; // Excel file successfully generated and exported

            } else {

                return false; // Batch operation failed
            }
        }

        return false; // Excel generation failed



    }

    //not in use
    public function generateExcelForAdditionAndDependentAddition($export_data)
    {
        $ids = [];

        $objects = $this->employeePolicyModel->getCorrectionEmployeesDataForExportExcel($export_data);

        foreach ($objects as $obj) {
            $ids[] = $obj->id;
        }

        $count = count($objects);
        $export_data['count'] = $count;
        $export_data['status'] = 'success';
        $this->myLogger->logme('error', 'Addition And Dependent Addition export data count : {data}', ['data' => $count]);

        if ($count == 0) {
            $this->myLogger->logme('error', 'No Record found. Endorsement ID Alread Updated.  Correction export data count : {data}', ['data' => $count]);
            return false;  
        }

        $this->removeOldExportInfoFromBatchFile($export_data);


        $this->myLogger->logme('error', 'Addition And Dependent Addition export file name : {data}', ['data' => $export_data['file_name']]);

        $correction_data = transform_objects_to_array_for_correction($objects);

        $headers = [
            'Emp Code',
            'RISK ID',
            'NAME OF EMP/DEP',
            'EMP/DEP TYPE',
            'RELATION',
            'DOB',
            'GENDER',
            'Wrong Data',
            'Correct Data',
            'Remarks',
            'Endorsement_Id'
        ];

        // Create a temporary file in memory
        $tempFile = tmpfile();

        // Generate Excel file with the temporary file
        $value = generate_excel($headers, $correction_data, $tempFile);

        if ($value) {



            $return = $this->batchFilesAndBatchListEntry($export_data, $objects);

            if ($return) {

                foreach ($ids as $key => $id) {
                    $group_key = $this->empEndorsementModel->select('group_key')->where('id', $id)->first();
                    if ($group_key) {
                        $this->empEndorsementModel->where('group_key', $group_key)->set('status', 'inprogress')->update();
                    }
                }

                // Set the appropriate headers for Excel file download
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="' . $export_data['file_name'] . '"');
                header('Cache-Control: max-age=0');

                // Rewind the temporary file pointer
                rewind($tempFile);

                // Output the contents of the temporary file to the browser
                fpassthru($tempFile);

                // Close and remove the temporary file
                fclose($tempFile);

                return true;
            } else {
                return false;
            }
        }
    }
    //end not in use

    /**
     * The below functions are Imports data from an Excel file for :
     *              - Inception
     *              - Deletion
     *              - Correction
     *              - SI_Enhancement
     * 
     * This functions are processes the uploaded Excel file, extracts relevant information, 
     * and updates employee policies and employees table accordingly.
     *
     * @param array $import_data An associative array containing : 
     *               - client_id, 
     *               - client_policy_id,
     *               - the uploaded file.
     * @return int Returns:
     *               - 1 if data import is successful.
     *               - 2 if the file does not exist.
     *               - 0 if the provided data is incomplete or incorrect.
     */


    //Endorsement Inception

    public function importInceptionFileValidation($params)
    {

        $this->myLogger->logme('info', 'Inception File Validation --  Function called');

        $file_id = $params['file_id'];
        // dd($file_id);

        $this->myLogger->logme('error', 'Inception File Validation --   Batch File Table Primary ID : {data}', ['data' => $file_id]);
        
        $file = $this->batchFileModel->where('id', $file_id)->first();
        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];

        $insurer_or_tpa = $file['insurer_or_tpa'];
        if ($insurer_or_tpa == 'tpa') {
            $id = 'tpa_id';
        } else if ($insurer_or_tpa == 'insurer') {
            $id = 'uhid';
        }

        $ref_data = [
            'client_id' => $client_id,
            'client_policy_id' => $client_policy_id,
            'client_branch_id' => $client_branch_id,
            'insurer_or_tpa' => $insurer_or_tpa,
            'event_type' => $file['event_type'],
        ];

        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];
        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        $excel_header = $excel_data[0];
        unset($excel_data[0]);
        // array_pop($excel_data);

        $inceptionHeader = [
            'S.No', 
            'NAME OF EMP/DEP',
            'EMP ID',
            // 'EMP/DEP TYPE',
            // 'RELATIONSHIP CODE',
            'DOB',
            'GENDER',
            'PRE EXISTING AILMENTS',
            'BASIC COVER SI',
            'DATE OF COVERAGE',
            'AGE',
            'RELATIONSHIP',
            'REMARKS',
            'POLICY END DATE',
            'NO OF DAYS',
            'TPA ID',
            'UHID',
            // 'PREMIUM',
            'PRO RATA PREMIUM',
            'GST',
            'TOTAL AMOUNT',
        ];

        if(in_array($file['event_type'], ['addition', 'dependent_addition'])){
            $inceptionHeader[] = 'ENDORSEMENT NO';
        }

        // dd($inceptionHeader);
        foreach ($inceptionHeader as $key => $value) {

            if($excel_header[$key] != $value){
                $data = [
                    'status' => 'failed-5',
                ];

                $this->batchFileModel->where('id', $file_id)->set($data)->update();

                $file_data = $this->getDataByFileId($file_id, 'failure');
                $this->setPullNotification($file_data);

                $this->myLogger->logme('error', 'Inception File Validation --  Upload the worng excel file');
                return ['status' => 'error', 'message' => 'Upload the worng excel file', 'excel_header' => $excel_header, 'inception_header' => $inceptionHeader];
            }
        }


        $emp_count = count($excel_data);

        //get the employee data for excel file validation
        $employee_data = $this->employeePolicyModel->getInceptionEmployeeDataForExportExcel($ref_data, 1);

        if ($employee_data == null || empty($employee_data)) {

            if ($insurer_or_tpa == 'tpa') {

                $data = [
                    'status' => 'failed-1',
                ];
    
                $this->batchFileModel->where('id', $file_id)->set($data)->update();
                $this->myLogger->logme('error', 'Inception File Validation --   TPAID already updated');

                $file_data = $this->getDataByFileId($file_id, 'failure');
                $this->setPullNotification($file_data);

                return ['status' => 'error', 'message' => 'The list of employees provided has already been updated with the TPA ID, or this is not the correct file'];

            } else if ($insurer_or_tpa == 'insurer') {

                $data = [
                    'status' => 'failed-2',
                ];
    
                $this->batchFileModel->where('id', $file_id)->set($data)->update();
                $this->myLogger->logme('error', 'Inception File Validation --   UHID already updated');

                $file_data = $this->getDataByFileId($file_id, 'failure');
                $this->setPullNotification($file_data);

                return ['status' => 'error', 'message' =>'The list of employees provided has already been updated with the UHID, or this is not the correct file'];
            }

            $this->myLogger->logme('error', 'Inception File Validation --   UHID or TPAID already updated or the uploadedfile is not correct');
        }

        $excel_data_count = count($excel_data);
        $emp_data_count = count($employee_data);

        $this->myLogger->logme('error', 'Inception File Validation --   Excel File count : {data}', ['data' => $excel_data_count]);
        $this->myLogger->logme('error', 'Inception File Validation --   Database count : {data}', ['data' => $emp_data_count]);


        // dd($excel_data_count, $emp_data_count);
        $difference = $emp_data_count - $excel_data_count;

        $status = 'in-progress';

        if ($excel_data_count < $emp_data_count) {
            $status = 'in-progress-partially';
            $partially_updated_data = 'Expected : ' . $emp_data_count . ',  ' . 'Updated : ' . $excel_data_count . ',  ' . 'difference : ' . $difference;
            $this->myLogger->logme('error', 'Inception File Validation --   excel file count partially : {data}', ['data' => $partially_updated_data]);
        }

        if ($emp_data_count < $excel_data_count) {

            $data = [
                'status' => 'failed-3',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'Inception File Validation --   Excel File Count ( {excel} ) exceeds db count ( {db} )', ['db' => $emp_data_count, 'excel' => $excel_data_count]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return ['status' => 'error', 'message' =>'The Excel record count exceeds the DB record count. excel file count : ' .  $excel_data_count . 'db count : ' .  $emp_data_count];
        }


        $errors = []; // Initialize an array to store errors
        $missing_id = [];
        $batch_list_id = [];

        foreach ($employee_data as $key => $emp_value) {

            $key = $key + 1;

            if(!isset($excel_data[$key])){
                break;
            }

            if ($insurer_or_tpa == 'tpa') {

                if ($excel_data[$key][13] === null) {
                    $missing_id[$key][] = [
                        'row' => $key,
                        'column' => 13,
                        'db_data' => "TPA ID is Must",
                        'excel_data' => $excel_data[$key][13]
                    ];
                }
            } else if ($insurer_or_tpa == 'insurer') {
                if ($excel_data[$key][14] === null) {
                    $missing_id[$key][] = [
                        'row' => $key,
                        'column' => 14,
                        'db_data' => "UHID or Risk ID is Must",
                        'excel_data' => $excel_data[$key][14]
                    ];
                }
            }


            if ($emp_value['emp_name'] != $excel_data[$key][1]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 1,
                    'db_data' => $emp_value['emp_name'],
                    'excel_data' => $excel_data[$key][1]
                ];
            }

            if ($emp_value['emp_code'] != $excel_data[$key][2]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 2,
                    'db_data' => $emp_value['emp_code'],
                    'excel_data' => $excel_data[$key][2]
                ];
            }

            if ($emp_value['emp_dob'] != $excel_data[$key][3]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 3,
                    'db_data' => $emp_value['emp_dob'],
                    'excel_data' => $excel_data[$key][3]
                ];
            }

            if ($emp_value['emp_gender'] != $excel_data[$key][4]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 4,
                    'db_data' => $emp_value['emp_gender'],
                    'excel_data' => $excel_data[$key][4]
                ];
            }

            if ($emp_value['pre_existing_alignments'] != $excel_data[$key][5]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 5,
                    'db_data' => $emp_value['pre_existing_alignments'],
                    'excel_data' => $excel_data[$key][5]
                ];
            }

            if ($emp_value['basic_cover_si'] != $excel_data[$key][6]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 6,
                    'db_data' => $emp_value['basic_cover_si'],
                    'excel_data' => $excel_data[$key][6]
                ];
            }

            if ($emp_value['emp_relationship'] != $excel_data[$key][9]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 9,
                    'db_data' => $emp_value['emp_relationship'],
                    'excel_data' => $excel_data[$key][9]
                ];
            }

            if ($emp_value['policy_end_date'] != $excel_data[$key][11]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 11,
                    'db_data' => $emp_value['policy_end_date'],
                    'excel_data' => $excel_data[$key][11]
                ];
            }

            if ($emp_value['no_of_days'] != $excel_data[$key][12]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 12,
                    'db_data' => $emp_value['no_of_days'],
                    'excel_data' => $excel_data[$key][12]
                ];
            }

            // if ($emp_value['premium'] != $excel_data[$key][17]) {
            //     $errors[$key][] = [
            //         'row' => $key,
            //         'column' => 17,
            //         'db_data' => $emp_value['premium'],
            //         'excel_data' => $excel_data[$key][17]
            //     ];
            // }

            if ($emp_value['pro_rata_premium'] != $excel_data[$key][15]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 15,
                    'db_data' => $emp_value['pro_rata_premium'],
                    'excel_data' => $excel_data[$key][15]
                ];
            }

            if ($emp_value['gst'] != $excel_data[$key][16]) {

                $errors[$key][] = [
                    'row' => $key,
                    'column' => 16,
                    'db_data' => $emp_value['gst'],
                    'excel_data' => $excel_data[$key][16]
                ];
            }

            if ($insurer_or_tpa == 'tpa') {

                if ($emp_value['uhid'] != $excel_data[$key][14]) {
                    $errors[$key][] = [
                        'row' => $key,
                        'column' => 14,
                        'db_data' => $emp_value['uhid'],
                        'excel_data' => $excel_data[$key][14]
                    ];
                }

            } else if ($insurer_or_tpa == 'insurer') {

                 if ($emp_value['tpa_id'] != $excel_data[$key][13]) {
                    $errors[$key][] = [
                        'row' => $key,
                        'column' => 13,
                        'db_data' => $emp_value['tpa_id'],
                        'excel_data' => $excel_data[$key][13]
                    ];
                }
            }

            $batch_list_id[] = $emp_value['primaryKey'];
        }


        $error_count = count($errors);
        $json_errors = json_encode($errors);

        $missing_id_count = count($missing_id);
        $json_missing_id = json_encode($missing_id);

        // dd($error_count, $missing_id_count, $json_errors, $json_missing_id, $employee_data,  $excel_data);

        if ($missing_id_count > 0) {

            $data = [
                'error_data' => $json_missing_id,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();

            if ($insurer_or_tpa == 'tpa') {

                $file_data = $this->getDataByFileId($file_id, 'failure');
                $this->setPullNotification($file_data);

                return ['status' => 'error', 'message' =>'The TPA ID column is either partially or entirely empty.'];

            } else if ($insurer_or_tpa == 'insurer') {

                $file_data = $this->getDataByFileId($file_id, 'failure');
                $this->setPullNotification($file_data);

                return ['status' => 'error', 'message' =>'The UHID column is either partially or entirely empty.'];
            }
        }


        if ($error_count > 0) {

            $data = [
                'error_data' => $json_errors,
                'status' => 'failed',
            ];

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            return ['status' => 'error', 'message' => 'Inception File Validation Failed Excel and Database Data are Mismatching'];

        } else {

            $data = [
                'count' => $emp_count,
                'status' => $status,
                'error_data' => $partially_updated_data ?? null,
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();


            foreach ($batch_list_id as $key => $value) {

                $data = [
                    'emp_policy_id' => $value,
                    'batch_code' => $batch_code,
                    'created_by' => $user_id
                ];

                $insert = $this->batchListModel->insert($data);
            }


            $parameters['file_id'] = $file_id;

            $job_details  = new Jobs();                             
            $r = Jobs::addJob(['job_name' => 'importInceptionUpdateTPAandUHID','payload' => ['file_id' => $file_id]]);

            // $this->importInceptionUpdateTPAandUHID(['file_id' => $file_id]);

            return ['status' => 'error', 'message' => 'Inception File Validation Successfully Completed'];

        }
        
    }


    public function importInceptionUpdateTPAandUHID($params)
    {

        $this->myLogger->logme('error', 'Inception Update TPA and UHID --  Function called');

        $file_id = $params['file_id'];
        $file = $this->batchFileModel->find($file_id);
        // dd($file);

        if (!$file) {

            $data = [
                'status' => 'failed-4',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'Inception Update TPA and UHID --   The Physical file not found --   File id : {data}', ['data' => $file_id]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return ['status' => 'error', 'message' => 'Inception Update TPA and UHID --   The Physical file not found']; // Return error code if file not found
        }

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $insurer_or_tpa = $file['insurer_or_tpa'];
        $status = $file['status'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];

        $insurer_id = $this->clientPolicyModel->where('id', $client_policy_id)->first();

        $CD_Account_Number = $this->CDMasterModel
                    ->where('client_id', $client_id)
                    ->where('insurer_id', $insurer_id['insurer_id'])
                    ->first();

        $get_policy_type = $this->clientPolicyModel
            ->select('client_policy.*, policy_type.policy_type')
            ->join('policy_type', 'policy_type.id = client_policy.policy_type_id', 'left')
            ->where('client_policy.id', $client_policy_id)
            ->first();

        // dd($get_policy_type);

        $status_val = 'success';
        if ($status == 'in-progress-partially') {
            $status_val = 'partially success';
        }

        $this->myLogger->logme('error', 'Inception Update TPA and UHID --    file name : {data}', ['data' => $file['file_name']]);

        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];
        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        unset($excel_data[0]); // Remove header row
        // array_pop($excel_data);

        $totals = 0;
        $emp_policy_ids = [];
        $emp_details = [];
        $tpa_id = [];
        $uhid = [];
        $emp_endorsement_table_data = [];
        $enrollment_file_id = null;
        $endorsement_id = null;

        $emp_count = count($excel_data);
        $db = \Config\Database::connect();

        // print_rr($excel_data); die;

        foreach ($excel_data as $key => $value) {

            $name = $value[1];
            $emp_code = $value[2];
            $tpa_id[] = $value[13];
            $uhid[] = $value[14];
            $amount = $value[17];

            if(!empty($name) && !empty($emp_code)){

                $totals = $totals + $amount;

                $query = $db->table('employee_polices');
                $query->select('employee_polices.id');
                $query->join('employees', 'employees.id = employee_polices.employee_id');
                $query->where('employee_polices.client_policy_id', $client_policy_id);
                $query->where('employees.client_id', $client_id);
                $query->where('employees.client_branch_id', $client_branch_id);
                $query->where('employees.name', $name);
                $query->where('employees.emp_code', $emp_code);
                if ($file['insurer_or_tpa'] == 'tpa') {

                    $query->where('(employee_polices.tpa_id IS NULL OR employee_polices.tpa_id = "")');
                } else if ($file['insurer_or_tpa'] == 'insurer') {

                    $query->where('(employee_polices.uhid IS NULL OR employee_polices.uhid = "")');
                }
                $query->where('employee_polices.is_active', 1);
                $query->where('employee_polices.status', 'active');
                $query->where('employees.is_active', 1);
                $query->where('employees.emp_status', 'active');
                $query->limit(1);

                $result = $query->get()->getRowArray();
                if (isset($result['id']) && $result['id'] !== null) {
                    $emp_policy_ids[] = $result['id']; //for cash deposite
                    $emp_details[] = array('id' => $result['id'], 'tpa_id' => $value[13], 'uhid' => $value[14]);
                }

                //for addition and dependent_addition endorsement , endorsement_id update functionality 
                if(in_array($file['event_type'], ['addition', 'dependent_addition']) && isset($value[18])){

                    $action = 'a';
                    if($file['event_type'] == "dependent_addition"){
                        $action = 'da';
                    }

                    $endorsement_id = $value[18];

                    $result_for_endorsement =  $this->empEndorsementModel
                        ->select('emp_endorsement.*, employees.id as emp_id, employee_polices.id as emp_policy_id')
                        ->join('employee_polices', 'employee_polices.id = emp_endorsement.pk')
                        ->join('employees', 'employees.emp_code = emp_endorsement.emp_code')
                        ->where('employees.emp_code', $emp_code)
                        ->where('employees.name', $name)
                        ->where('employees.client_id', $client_id)
                        ->where('employees.client_branch_id', $client_branch_id)
                        ->where('emp_endorsement.actions',  $action)
                        ->where('employee_polices.client_policy_id', $client_policy_id)
                        ->where('employee_polices.is_active', 1)
                        ->where('employee_polices.status', 'active')
                        ->where('employees.is_active', 1)
                        ->where('employees.emp_status', 'active')
                        ->where('(emp_endorsement.endorsement_id IS NULL OR emp_endorsement.endorsement_id = "")')
                        ->groupBy('emp_endorsement.group_key')
                    ->first();

                    if (!empty($result_for_endorsement) && count($result_for_endorsement)) {
                        $enrollment_file_id = $result_for_endorsement['file_id']; //files table primary key
                        $emp_endorsement_table_data[] = array('id' => $result_for_endorsement['id'], 'group_key' => $result_for_endorsement['group_key'], 'endorsement_id' => $value[18], 'status' => 'complete');
                    }
                }

            }

        }

        // dd($emp_endorsement_table_data, $enrollment_file_id);

        //update the employee policy data (TPAID or UHID)
        $return = $this->employeePolicyModel->bulkUpdate($emp_details);

        if(in_array($file['event_type'], ['addition', 'dependent_addition']) && !empty($emp_endorsement_table_data)){
            $this->employeePolicyModel->updateEmpEndorsementAddition($emp_endorsement_table_data);
            $this->storeEndorsementNumber($file_id, $endorsement_id, $enrollment_file_id);
        }

        // Update batch file status and amount
        $this->batchFileModel->update($file_id, [
            'count' => $emp_count,
            'status' =>  $status_val,
            'amount' => $totals,
        ]);

        $this->myLogger->logme('error', 'Inception Update TPA and UHID --    employee count : {data}', ['data' => $emp_count]);
        $this->myLogger->logme('error', 'Inception Update TPA and UHID --    batch file status : {data}', ['data' => $status_val]);
        $this->myLogger->logme('error', 'Inception Update TPA and UHID --    total amount for cash deposite : {data}', ['data' => $totals]);

        //update CD transaction entry if only insurer
        if ($file['insurer_or_tpa'] == 'insurer') {

            $this->myLogger->logme('error', 'Inception Update TPA and UHID --    set  cashDepositCalculationForInception and sendMailForDownloadingECard in JOB QUEUE');

            $policy_name = $this->getPolicyNameUsingClientPolicyId($client_policy_id);

            // $depositeData = [
            //     'employeeIds' => $emp_policy_ids,
            //     'client_id' => $client_id,
            //     'client_policy_id' => $client_policy_id,
            //     'client_branch_id' => $client_branch_id,
            //     'count' => $emp_count,
            //     'event' => $file['event_type'],
            //     'policy_name' => $policy_name['policy_name'],
            //     'user_id' => $user_id,
            // ];

            $job_details  = new Jobs();
            $r = Jobs::addJob(['job_name' => 'cashDepositCalculationForInception', 'payload' => [
                'employeeIds' => $emp_policy_ids,
                'client_id' => $client_id,
                'client_policy_id' => $client_policy_id,
                'cd_ac_no' => $CD_Account_Number['cd_ac_no'] ?? null,
                'endorsement_no' => $endorsement_id,
                'client_branch_id' => $client_branch_id,
                'count' => $emp_count,
                'event_name' => $file['event_type'],
                'policy_name' => $policy_name['policy_name'],
                'user_id' => $user_id,
            ]]);

            // $this->cashDepositCalculationForInception($depositeData);
            // $this->sendMailForDownloadingECard($emp_policy_ids);

        }

        //send ecard mail if the event is tpa only
        if ($file['insurer_or_tpa'] == 'tpa') {

            if ($get_policy_type['policy_type_id'] != 1) {

                $job_details  = new Jobs();
                $r = Jobs::addJob(['job_name' => 'sendMailForDownloadingECard', 'payload' => $emp_policy_ids]);
            }

        }

        $file_data = $this->getDataByFileId($file_id, 'success');
        $this->setPullNotification($file_data);

        //import file to upload Google Drive
        // if(excelFileGDriveUpload($file_id, 'batch_file')){
        //     $this->myLogger->logme('error', 'Inception Update Google Drive File Upload --    File Uploaded Successfully');
        // }else{
        //     $this->myLogger->logme('error', 'Inception Update Google Drive File Upload --    File Uploaded Field');
        // }

        return 'Import Inception Updated ' . $status_val . '- Updated Count : ' . $emp_count;
    }



    //Endorsement Correction
    public function importCorrectionValidation($params)
    {

        $this->myLogger->logme('error', 'Correction File Validation --   Function called');

        $file_id = $params['file_id'];
        $file = $this->batchFileModel->where('id', $file_id)->first();

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];

        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];

        if (!file_exists($file_name_with_path)) {

            $this->myLogger->logme('error', 'Correction File Validation --   The Physical file not found');
            $this->myLogger->logme('error', 'Correction File Validation --   File Name : {data}', ['data' => $file['file_name']]);
            $this->myLogger->logme('error', 'Correction File Validation --   File Path : {data}', ['data' => $file_name_with_path]);

            return 'The Physical file not found';
        }

        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        $excel_header = $excel_data[0];
        unset($excel_data[0]);

        $headers = ['Emp Code','RISK ID','NAME OF EMP/DEP','EMP/DEP TYPE','RELATION','DOB','GENDER','Wrong Data','Correct Data','Remarks','Endorsement_Id'];


        foreach ($headers as $key => $value) {
            if($excel_header[$key] != $value){
                $data = [
                    'status' => 'failed-5',
                ];

                $this->batchFileModel->where('id', $file_id)->set($data)->update();

                $file_data = $this->getDataByFileId($file_id, 'failure');
                $this->setPullNotification($file_data);

                $this->myLogger->logme('error', 'Correction File Validation --  Upload the worng excel file');
                return ['status' => 'error', 'message' => 'Upload the worng excel file'];
            }
        }



        $endorsement_data = $this->empEndorsementModel
            ->select("
                                emp_endorsement.id, 
                                emp_endorsement.pk, 
                                emp_endorsement.emp_code, 
                                emp_endorsement.endorsement_id, 
                                emp_endorsement.old_value, 
                                emp_endorsement.new_value, 
                                emp_endorsement.field_name, 
                                emp_endorsement.remarks, 
                                emp_endorsement.actions, 
                                employees.id AS primaryKey, 
                                employees.name AS emp_name, 
                                employees.dob AS emp_dob, 
                                employees.gender AS emp_gender, 
                                employees.client_id AS emp_client_id, 
                                'Has Define' AS emp_type,
                                employee_polices.uhid,
                                employees.relationship_code
                            ")
            ->join("employees", "employees.id = emp_endorsement.pk", "left")
            ->join("employee_polices", "employees.id = employee_polices.employee_id", "left")
            ->where("employees.client_id", $client_id)
            ->where("employee_polices.client_policy_id", $client_policy_id)
            ->where("employees.client_branch_id", $client_branch_id)
            ->where("employees.is_active", 1)
            ->where("employees.emp_status", "active")
            ->where("emp_endorsement.actions", "c")
            ->where("emp_endorsement.is_active", 1)
            ->where("emp_endorsement.status !=", "truncated")
            ->where("(emp_endorsement.endorsement_id IS NULL OR emp_endorsement.endorsement_id = '')")
            ->findAll();



        dd($endorsement_data, $excel_data);


        if ($endorsement_data == null || empty($endorsement_data)) {

            $data = [
                'status' => 'failed-1',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'correctionFileValidation ENDORSEMENT ID already updated');

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The list of employees provided has already been updated with the ENDORSEMENT ID, or this is not the correct file';

            $this->myLogger->logme('error', 'correctionFileValidation ENDORSEMENT ID already updated or the uploadedfile is not correct');
        }

        $excel_data_count = count($excel_data);
        $endorsement_data_count = count($endorsement_data);

        $this->myLogger->logme('error', 'correctionFileValidation excel file count : {data}', ['data' => $excel_data_count]);
        $this->myLogger->logme('error', 'correctionFileValidation database count : {data}', ['data' => $endorsement_data_count]);


        $difference = $endorsement_data_count - $excel_data_count;

        $status = 'in-progress';
        if ($excel_data_count < $endorsement_data_count) {

            $status = 'in-progress-partially';
            $partially_updated_data = 'Expected : ' . $endorsement_data_count . ',  ' . 'Updated : ' . $excel_data_count . ',  ' . 'difference : ' . $difference;
            $this->myLogger->logme('error', 'correctionFileValidation excel file count partially : {data}', ['data' => $partially_updated_data]);
        }



        if ($endorsement_data_count < $excel_data_count) {

            $data = [
                'status' => 'failed-3',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'correctionFileValidation excel file count ( {excel} ) exceeds db count ( {db} )', ['db' => $endorsement_data_count, 'excel' => $excel_data_count]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The Excel record count exceeds the DB record count. excel file count : ' .  $excel_data_count . 'db count : ' .  $endorsement_data_count;
        }


        $errors = []; // Initialize an array to store errors
        $missing_id = [];
        $batch_list_id = [];

        foreach ($endorsement_data as $key => $endorsement_value) {

            $key = $key + 1;

            if (!isset($excel_data[$key])) {
                break;
            }


            if ($excel_data[$key][10] === null) {
                $missing_id[$key][] = [
                    'row' => $key,
                    'column' => 10,
                ];
            }


            if ($endorsement_value['emp_name'] != $excel_data[$key][2]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 2,
                    'db_data' => $endorsement_value['emp_name'],
                    'excel_data' => $excel_data[$key][2]
                ];
            }

            if ($endorsement_value['emp_code'] != $excel_data[$key][0]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 0,
                    'db_data' => $endorsement_value['emp_code'],
                    'excel_data' => $excel_data[$key][0]
                ];
            }

            if ($endorsement_value['emp_dob'] != $excel_data[$key][5]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 5,
                    'db_data' => $endorsement_value['emp_dob'],
                    'excel_data' => $excel_data[$key][5]
                ];
            }

            if ($endorsement_value['emp_gender'] != $excel_data[$key][6]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 6,
                    'db_data' => $endorsement_value['emp_gender'],
                    'excel_data' => $excel_data[$key][6]
                ];
            }

            if ($endorsement_value['old_value'] != $excel_data[$key][7]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 7,
                    'db_data' => $endorsement_value['old_value'],
                    'excel_data' => $excel_data[$key][7]
                ];
            }

            if ($endorsement_value['new_value'] != $excel_data[$key][8]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 8,
                    'db_data' => $endorsement_value['new_value'],
                    'excel_data' => $excel_data[$key][8]
                ];
            }



            if ($endorsement_value['uhid'] != $excel_data[$key][1]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 1,
                    'db_data' => $endorsement_value['uhid'],
                    'excel_data' => $excel_data[$key][1]
                ];
            }

            $batch_list_id[] = $endorsement_value['primaryKey'];
        }


        $error_count = count($errors);
        $json_errors = json_encode($errors);


        $missing_id_count = count($missing_id);
        $json_missing_id = json_encode($missing_id);

        // dd($error_count, $missing_id_count, $json_errors, $json_missing_id, $batch_list_id);


        if ($missing_id_count > 0) {

            $data = [

                'count' => $endorsement_data_count,
                'error_data' => $json_missing_id,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The ENDORSEMENT ID column is either partially or entirely empty.';
           
        }


        if ($error_count > 0) {

            $data = [

                'count' => $endorsement_data_count,
                'error_data' => $json_errors,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            return 'Correction Validation Failed';

        } else {

            $data = [
                'count' => $endorsement_data_count,
                'status' => $status,
                'error_data' => $partially_updated_data ?? null,
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();


            foreach ($batch_list_id as $key => $value) {

                $data = [
                    'emp_policy_id' => $value,
                    'batch_code' => $batch_code,
                    'created_by' => $user_id,
                ];

                $insert = $this->batchListModel->insert($data);
            }


            $job_details  = new Jobs();                             
            $r = Jobs::addJob(['job_name' => 'importCorrectionUpdateEndorsementID','payload' => ['file_id' => $file_id]]);

            // $this->importCorrectionUpdateEndorsementID(['file_id' => $file_id]);

            return 'Correction Validation Success';
        }
    }


    public function importCorrectionUpdateEndorsementID($params){

        $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID called');

        $file_id = $params['file_id'];
        $file = $this->batchFileModel->find($file_id);
        if (!$file) {

            $data = [
                'status' => 'failed-4',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID the Physical file not found - file id : {data}', ['data' => $file_id]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'importCorrectionUpdateEndorsementID the Physical file not found'; // Return error code if file not found
        }

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $insurer_or_tpa = $file['insurer_or_tpa'];
        $status = $file['status'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];
        $event_type = $file['event_type'];


        $status_val = 'success';
        if ($status == 'in-progress-partially') {

            $status_val = 'partially success';
        }

        $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID  file name : {data}', ['data'=> $file['file_name']]);

        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];
        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        unset($excel_data[0]); 

        $emp_ids = [];
        $emp_details = [];
        $endorsement_id = [];
        $endorsement_details = [];
        $enrollment_file_id = null;

        $emp_count = count($excel_data);
        $db = \Config\Database::connect();


        foreach ($excel_data as $key => $value) {

            $emp_code = $value[0];
            $old_value = $value[7];
            $endorsement_id[] = $value[10];
            $uhid = $value[1];

            $result =  $this->empEndorsementModel
            ->select('emp_endorsement.*, employees.id as emp_id')
            ->join('employees', 'employees.id = emp_endorsement.pk')
            ->join('employee_polices', 'employee_polices.employee_id = employees.id')
            ->where('employees.emp_code', $emp_code)
            ->where('employees.client_id', $client_id)
            ->where('employees.client_branch_id', $client_branch_id)
            ->where('employee_polices.client_policy_id', $client_policy_id)
            ->where('employee_polices.uhid', $uhid)
            ->where('emp_endorsement.old_value', $old_value)
            
            ->where('employee_polices.is_active', 1)
            ->where('employee_polices.status', 'active')
            ->where('employees.is_active', 1)
            ->where('employees.emp_status', 'active')
            ->where("emp_endorsement.is_active", 1)
            ->where("emp_endorsement.status !=", "truncated")
            ->first();

            if (isset($result['id']) && $result['id'] !== null) {
                // return $result;
                $enrollment_file_id = $result['file_id'];
                $emp_details[] = array('id' => $result['emp_id'], $result['field_name'] => $value[8]);
                $endorsement_details[] = array('group_key' => $result['group_key'], 'id' => $result['id'], 'endorsement_id' => $value[10], 'status' => 'complete');
            }

        }

        // return [$emp_details, $endorsement_details];

       $this->empEndorsementModel->updateBatch($endorsement_details, 'group_key');
       $this->employeePolicyModel->bulkUpdateForCorrection($emp_details);
       $this->storeEndorsementNumber($file_id, $endorsement_id[0], $enrollment_file_id);


        // Update batch file status and amount
        $this->batchFileModel->update($file_id, [
            'count' => $emp_count,
            'status' =>  $status_val,
        ]);


        $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID  employee count : {data}', ['data'=> $emp_count]);
        $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID  batch file status : {data}', ['data'=> $status_val]);


        $file_data = $this->getDataByFileId($file_id, 'success');
        $this->setPullNotification($file_data);


        //import file to upload Google Drive
        // if(excelFileGDriveUpload($file_id, 'batch_file')){
        //     $this->myLogger->logme('error', 'Correction File Update Google Drive File Upload --    File Uploaded Successfully');
        // }else{
        //     $this->myLogger->logme('error', 'Correction File Update Google Drive File Upload --    File Uploaded Field');
        // }

        return 'Import Correction Updated '. $status_val . '- Updated Count : ' . $emp_count;

    }



    //Endorsement SIEnhancement
    public function importSIEnhancementValidation($params)
    {
        $file_id = $params['file_id'];
        $file = $this->batchFileModel->where('id', $file_id)->first();

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];


        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];

        if (!file_exists($file_name_with_path)) {
            return 'The Physical file not found';
        }

        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        $excel_header = $excel_data[0];
        unset($excel_data[0]);


        $headers = ['S.No','NAME OF EMP/DEP','EMP ID','EMP/DEP TYPE','RELATION','DOB','GENDER','PRE EXISTING AILMENTS','BASIC COVER SI','Old Sum Insured','Date of Coverage','Policy End Date','No Of Days','Old SI Premium','New SI premium','Difference premium','Pro Rata Premium','GST','Total','ENDORSEMENT_ID'];


        foreach ($headers as $key => $value) {
            if($excel_header[$key] != $value){
                $data = [
                    'status' => 'failed-5',
                ];

                $this->batchFileModel->where('id', $file_id)->set($data)->update();

                $file_data = $this->getDataByFileId($file_id, 'failure');
                $this->setPullNotification($file_data);

                $this->myLogger->logme('error', 'SI Enhancement File Validation --  Upload the worng excel file');
                return ['status' => 'error', 'message' => 'Upload the worng excel file'];
            }
        }


            $db = \Config\Database::connect();

            // Raw SQL query
            $sql = "
                SELECT 
                    a.id as endorsement_primarykey,
                    a.group_key,
                    employee_polices.id AS primaryKey,
                    employees.id AS emp_primary,
                    employees.name AS emp_name,
                    employees.emp_code AS emp_code,
                    employees.dob AS emp_dob,
                    employees.gender AS emp_gender,
                    employees.relationship_code AS emp_relationship_code,
                    'Has Define' AS emp_type,
                    employee_polices.uhid AS risk_id,
                    employee_polices.pre_existing_alignments,
                    employee_polices.policy_end_date,
                    employee_polices.basic_cover_si as old_basic_cover_si,
                    employee_polices.rata_premimum as old_si_premium,
                    sidata.new_basic_cover_si,
                    sidata.new_si_premium,
                    sidata.date_of_coverage,
                    DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1 AS no_of_days,
                    sidata.new_si_premium - employee_polices.rata_premimum AS difference_premium,
                    ROUND((sidata.new_si_premium - employee_polices.rata_premimum) * (DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1) / 365, 2) AS pro_rata_premimum,
                    ROUND(((sidata.new_si_premium - employee_polices.rata_premimum) * (DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1) / 365) * 0.18, 2) AS gst,
                    ((sidata.new_si_premium - employee_polices.rata_premimum) * (DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1) / 365) + ROUND(((sidata.new_si_premium - employee_polices.rata_premimum) * (DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1) / 365) * 0.18, 2) AS total
                FROM 
                    emp_endorsement a
                LEFT JOIN 
                    employees ON employees.emp_code = a.emp_code
                LEFT JOIN 
                    employee_polices ON employees.id = employee_polices.employee_id 
                LEFT JOIN (
                    SELECT 
                        aa.emp_code, 
                        aa.new_value as new_basic_cover_si, 
                        bb.new_value as new_si_premium, 
                        cc.new_value as date_of_coverage 
                    FROM (
                        SELECT  
                            a1.emp_code, 
                            a1.field_name, 
                            a1.new_value 
                        FROM 
                            emp_endorsement as a1 
                        WHERE 
                            a1.field_name = 'basic_cover_si'
                    ) aa 
                    LEFT JOIN (
                        SELECT  
                            b1.emp_code, 
                            b1.field_name, 
                            b1.new_value 
                        FROM 
                            emp_endorsement as b1 
                        WHERE 
                            b1.field_name = 'premium'
                    ) bb ON aa.emp_code = bb.emp_code 
                    LEFT JOIN (
                        SELECT  
                            c1.emp_code, 
                            c1.field_name, 
                            c1.new_value 
                        FROM 
                            emp_endorsement as c1  
                        WHERE 
                            c1.field_name = 'si_enhancement_date'
                    ) cc ON aa.emp_code = cc.emp_code
                ) as sidata ON a.emp_code = sidata.emp_code 
                WHERE 
                    employee_polices.client_policy_id = '$client_policy_id'
                    AND employees.client_branch_id = '$client_branch_id'
                    AND employee_polices.is_active = '1'
                    AND (a.endorsement_id IS NULL OR a.endorsement_id = '')   
                    AND a.actions = 'si'
                GROUP BY group_key
            ";

            // Execute the query
            $query = $db->query($sql);

            // Fetch the results
            $endorsement_data = $query->getResultArray();



        // dd($endorsement_data, $excel_data);


        if ($endorsement_data == null || empty($endorsement_data)) {

            $data = [
                'status' => 'failed-1',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'SI Enhancement_File Validation --    ENDORSEMENT ID already updated or the uploadedfile is not correct');

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The list of employees provided has already been updated with the ENDORSEMENT ID, or this is not the correct file';
        }

        $excel_data_count = count($excel_data);
        $endorsement_data_count = count($endorsement_data);

        $this->myLogger->logme('error', 'SI Enhancement_File Validation --   Excel file count : {data}', ['data' => $excel_data_count]);
        $this->myLogger->logme('error', 'SI Enhancement File Validation --   Database count : {data}', ['data' => $endorsement_data_count]);


        $difference = $endorsement_data_count - $excel_data_count;

        $status = 'in-progress';
        if ($excel_data_count < $endorsement_data_count) {

            $status = 'in-progress-partially';
            $partially_updated_data = 'Expected : ' . $endorsement_data_count . ',  ' . 'Updated : ' . $excel_data_count . ',  ' . 'difference : ' . $difference;
            $this->myLogger->logme('error', 'SI Enhancement File Validation --    excel file count partially : {data}', ['data' => $partially_updated_data]);
        }



        if ($endorsement_data_count < $excel_data_count) {

            $data = [
                'status' => 'failed-3',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'SI_Enhancement_FileValidation excel file count ( {excel} ) exceeds db count ( {db} )', ['db' => $endorsement_data_count, 'excel' => $excel_data_count]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The Excel record count exceeds the DB record count. excel file count : ' .  $excel_data_count . 'db count : ' .  $endorsement_data_count;
        }


        $errors = []; // Initialize an array to store errors
        $missing_id = [];
        $batch_list_id = [];

        foreach ($endorsement_data as $key => $endorsement_value) {

            $key = $key + 1;

            if (!isset($excel_data[$key])) {
                break;
            }


            if ($excel_data[$key][19] === null) {
                $missing_id[$key][] = [
                    'row' => $key,
                    'column' => 19,
                ];
            }


            if ($endorsement_value['emp_name'] != $excel_data[$key][1]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 1,
                    'db_data' => $endorsement_value['emp_name'],
                    'excel_data' => $excel_data[$key][1]
                ];
            }

            if ($endorsement_value['emp_code'] != $excel_data[$key][2]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 2,
                    'db_data' => $endorsement_value['emp_code'],
                    'excel_data' => $excel_data[$key][2]
                ];
            }

            if ($endorsement_value['emp_relationship_code'] != $excel_data[$key][4]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 4,
                    'db_data' => $endorsement_value['emp_relationship_code'],
                    'excel_data' => $excel_data[$key][4]
                ];
            }

            if ($endorsement_value['emp_dob'] != $excel_data[$key][5]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 5,
                    'db_data' => $endorsement_value['emp_dob'],
                    'excel_data' => $excel_data[$key][5]
                ];
            }

            if ($endorsement_value['emp_gender'] != $excel_data[$key][6]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 6,
                    'db_data' => $endorsement_value['emp_gender'],
                    'excel_data' => $excel_data[$key][6]
                ];
            }


            if ($endorsement_value['pre_existing_alignments'] != $excel_data[$key][7]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 7,
                    'db_data' => $endorsement_value['pre_existing_alignments'],
                    'excel_data' => $excel_data[$key][7]
                ];
            }

            if ($endorsement_value['new_basic_cover_si'] != $excel_data[$key][8]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 8,
                    'db_data' => $endorsement_value['new_basic_cover_si'],
                    'excel_data' => $excel_data[$key][8]
                ];
            }

            if ($endorsement_value['old_basic_cover_si'] != $excel_data[$key][9]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 9,
                    'db_data' => $endorsement_value['old_basic_cover_si'],
                    'excel_data' => $excel_data[$key][9]
                ];
            }



            if ($endorsement_value['date_of_coverage'] != $excel_data[$key][10]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 10,
                    'db_data' => $endorsement_value['date_of_coverage'],
                    'excel_data' => $excel_data[$key][10]
                ];
            }

            if ($endorsement_value['policy_end_date'] != $excel_data[$key][11]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 11,
                    'db_data' => $endorsement_value['policy_end_date'],
                    'excel_data' => $excel_data[$key][11]
                ];
            }

            if ($endorsement_value['no_of_days'] != $excel_data[$key][12]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 12,
                    'db_data' => $endorsement_value['no_of_days'],
                    'excel_data' => $excel_data[$key][12]
                ];
            }

            if ($endorsement_value['old_si_premium'] != $excel_data[$key][13]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 13,
                    'db_data' => $endorsement_value['old_si_premium'],
                    'excel_data' => $excel_data[$key][13]
                ];
            }

            if ($endorsement_value['new_si_premium'] != $excel_data[$key][14]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 14,
                    'db_data' => $endorsement_value['new_si_premium'],
                    'excel_data' => $excel_data[$key][14]
                ];
            }

            if ($endorsement_value['difference_premium'] != $excel_data[$key][15]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 15,
                    'db_data' => $endorsement_value['difference_premium'],
                    'excel_data' => $excel_data[$key][15]
                ];
            }

            if ($endorsement_value['pro_rata_premimum'] != $excel_data[$key][16]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 16,
                    'db_data' => $endorsement_value['pro_rata_premimum'],
                    'excel_data' => $excel_data[$key][16]
                ];
            }

            if ($endorsement_value['gst'] != $excel_data[$key][17]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 17,
                    'db_data' => $endorsement_value['gst'],
                    'excel_data' => $excel_data[$key][17]
                ];
            }  
            
            $batch_list_id[] = $endorsement_value['primaryKey'];
        }


        $error_count = count($errors);
        $json_errors = json_encode($errors);


        $missing_id_count = count($missing_id);
        $json_missing_id = json_encode($missing_id);

        // dd($error_count, $missing_id_count, $json_errors, $json_missing_id, $batch_list_id);


        if ($missing_id_count > 0) {

            $data = [

                'count' => $endorsement_data_count,
                'error_data' => $json_missing_id,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The ENDORSEMENT ID column is either partially or entirely empty.';
        }


        if ($error_count > 0) {

            $data = [

                'count' => $endorsement_data_count,
                'error_data' => $json_errors,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            return 'SI ENHANCEMENT File Validation Failed';
        } else {

            $data = [
                'count' => $endorsement_data_count,
                'status' => $status,
                'error_data' => $partially_updated_data ?? null,
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();


            foreach ($batch_list_id as $key => $value) {

                $data = [
                    'emp_policy_id' => $value,
                    'batch_code' => $batch_code,
                    'created_by' => $user_id,
                ];

                $insert = $this->batchListModel->insert($data);
            }


            $job_details  = new Jobs();
            $r = Jobs::addJob(['job_name' => 'importSIEnhancementUpdateEndorsementID', 'payload' => ['file_id' => $file_id]]);

            // $this->importSIEnhancementUpdateEndorsementID(['file_id' => $file_id]);
            return 'SI Enhancement_File Validation --    Success';
        }
    }


    public function importSIEnhancementUpdateEndorsementID($params)
    {

        $this->myLogger->logme('error', 'SI Enhancement Update Endorsement ID --   Function called');

        $file_id = $params['file_id'];
        $file = $this->batchFileModel->find($file_id);
        if (!$file) {

            $data = [
                'status' => 'failed-4',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'SI Enhancement Update Endorsement ID --     The Physical file not found --   File id : {data}', ['data' => $file_id]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'SI Enhancement Update Endorsement ID --     The Physical file not found'; // Return error code if file not found
        }

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $status = $file['status'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];

        $insurer_id = $this->clientPolicyModel->where('id', $client_policy_id)->first();

        $CD_Account_Number = $this->CDMasterModel
                    ->where('client_id', $client_id)
                    ->where('insurer_id', $insurer_id['insurer_id'])
                    ->first();


        $status_val = 'success';
        if ($status == 'in-progress-partially') {

            $status_val = 'partially success';
        }

        $this->myLogger->logme('error', 'SI Enhancement Update Endorsement ID --      file name : {data}', ['data' => $file['file_name']]);

        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];
        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        unset($excel_data[0]);

        $emp_count = count($excel_data); //excel file count

        $emp_policy_ids = [];
        $employeeIds = [];
        $emp_details = [];
        $endorsement_id = '';
        $enrollment_file_id = '';
        $endorsement_details = [];
        $totals = 0;

        foreach ($excel_data as $key => $value) {

            $emp_name = $value[1];
            $emp_code = $value[2];
            $endorsement_id = $value[19];
            $totals += $value[18];


            $result =  $this->empEndorsementModel

                ->select('emp_endorsement.*, employees.id as emp_id, employee_polices.id as emp_policy_id')
                ->join('employee_polices', 'employee_polices.id = emp_endorsement.pk')
                ->join('employees', 'employees.emp_code = emp_endorsement.emp_code')
                ->where('employees.emp_code', $emp_code)
                ->where('employees.name', $emp_name)
                ->where('employees.client_id', $client_id)
                ->where('employees.client_branch_id', $client_branch_id)
                ->where('emp_endorsement.actions', 'si')
                ->where('employee_polices.client_policy_id', $client_policy_id)
                ->where('employee_polices.is_active', 1)
                ->where('employee_polices.status', 'active')
                ->where('employees.is_active', 1)
                ->where('employees.emp_status', 'active')
                ->where('(emp_endorsement.endorsement_id IS NULL OR emp_endorsement.endorsement_id = "")')
                ->groupBy('emp_endorsement.group_key')
                ->first();

            if (isset($result['id']) && $result['id'] !== null) {

                $enrollment_file_id = $result['file_id'];
                $emp_policy_ids[] = array('id' => $result['emp_policy_id'], 'is_active' => 0);
                $employeeIds[] = $result['emp_policy_id'];
                $endorsement_details[] = array('id' => $result['id'], 'group_key' => $result['group_key'], 'endorsement_id' => $value[19], 'status' => 'complete');
            }

            $empData = $this->employeePolicyModel
                ->select('employee_polices.*')
                ->join('employees', 'employee_polices.employee_id = employees.id')
                ->where('employees.emp_code', $emp_code)
                ->where('employees.name', $emp_name)
                ->where('employees.client_id', $client_id)
                ->where('employees.client_branch_id', $client_branch_id)
                ->where('employee_polices.client_policy_id', $client_policy_id)
                ->where('employee_polices.is_active', 1)
                ->where('employee_polices.status', 'active')
                ->where('employees.is_active', 1)
                ->where('employees.emp_status', 'active')
                ->first();

                unset($empData['id'], $empData['created_by'], $empData['created_at'], $empData['updated_by'], $empData['updated_at'], $empData['is_active']);
                
                $empData['basic_cover_si'] = $value[8];
                $empData['premium'] = $value[14];
                $empData['si_enhancement_date'] = $value[10];
                $empData['rata_premimum'] = $value[16];
                $empData['gst'] = $value[17];
                $empData['created_by'] =  $user_id;

                $emp_details[] = $empData;
        }

        $rounded_totals = round($totals, 2);

        // dd($emp_policy_ids, $emp_details, $endorsement_details);

        $this->employeePolicyModel->updateBatch($emp_policy_ids, 'id');

        $db = \Config\Database::connect();
        $db->transStart();
        
        $this->employeePolicyModel->insertBatch($emp_details);
        
        $insertedIds = [];
        $startId = $db->insertID(); // Get the first inserted ID
        
        for ($i = 0; $i < count($emp_details); $i++) {
            $insertedIds[] = $startId + $i;
        }
        
        $db->transComplete();
        
        // if ($db->transStatus() === FALSE) {
        //     // Handle the error, rollback, etc.
        // } else {
        //     // Transaction successful
        //     print_r($insertedIds); // This will print the array of inserted IDs
        // }
        
        $this->employeePolicyModel->bulkUpdateForEndorsement($endorsement_details);
        $this->storeEndorsementNumber($file_id, $endorsement_id, $enrollment_file_id);


        // Update batch file status and amount
        $this->batchFileModel->update($file_id, [
            'count' => $emp_count,
            'status' =>  $status_val,
            'amount' =>  $rounded_totals,
        ]);


        $this->myLogger->logme('error', 'SI Enhancement Update Endorsement ID --      Employee count : {data}', ['data' => $emp_count]);
        $this->myLogger->logme('error', 'SI Enhancement Update Endorsement ID --      Batch File status : {data}', ['data' => $status_val]);

        //call the cash deposite function
        if ($file['insurer_or_tpa'] == 'insurer') {

            $policy_name = $this->getPolicyNameUsingClientPolicyId($client_policy_id);
            $job_details  = new Jobs();                             
            $r = Jobs::addJob(['job_name' => 'cashDepositCalculationForSIEnhancement','payload' => [
                'employeeIds' => $insertedIds,
                'client_id' => $client_id,
                'client_policy_id' => $client_policy_id,
                'client_branch_id' => $client_branch_id,
                'cd_ac_no' => $CD_Account_Number['cd_ac_no'] ?? null,
                'endorsement_no' => $endorsement_id ?? null,
                'count' => $emp_count,
                'event_name' => $file['event_type'],
                'policy_name' => $policy_name['policy_name'],
                'user_id' => $user_id,
            ]]);

        }

        $file_data = $this->getDataByFileId($file_id, 'success');
        $this->setPullNotification($file_data);

        //import file to upload Google Drive
        // if(excelFileGDriveUpload($file_id, 'batch_file')){
        //     $this->myLogger->logme('error', 'SI Enhancement File Update Google Drive File Upload --    File Uploaded Successfully');
        // }else{
        //     $this->myLogger->logme('error', 'SI Enhancement File Update Google Drive File Upload --    File Uploaded Field');
        // }

        return 'SI Enhancement Update Endorsement ID --      ' . $status_val . '--   Updated Count : ' . $emp_count;
    }



    //Endorsement Deletion
    public function importDeletionValidation($params)
    {   

        $file_id = $params['file_id'];
        $file = $this->batchFileModel->where('id', $file_id)->first();

        // dd($file);

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];

        $ref_data = [
            'client_id' => $client_id,
            'client_policy_id' => $client_policy_id,
            'client_branch_id' => $client_branch_id,
            'insurer_or_tpa' => $file['insurer_or_tpa'],
            'event_type' => $file['event_type'],
        ];


        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];

        if (!file_exists($file_name_with_path)) {
            return 'The Physical file not found';
        }

        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        $excel_header = $excel_data[0];
        unset($excel_data[0]);
        // array_pop($excel_data);

        $headers = ['S.No','EMP ID','EMP NAME','DOB','GENDER','RELATIONSHIP','SUM INSURED','Date of Leaving','Policy End Date','No Of Days','Premium','Pro Rata Premium','GST','Total','Claim Status','ENDORSEMENT_ID'];
        // dd($headers, $excel_header);

        foreach ($headers as $key => $value) {
            if($excel_header[$key] != $value){
                $data = [
                    'status' => 'failed-5',
                ];

                $this->batchFileModel->where('id', $file_id)->set($data)->update();

                $file_data = $this->getDataByFileId($file_id, 'failure');
                $this->setPullNotification($file_data);

                $this->myLogger->logme('error', 'Deletion File Validation --  Upload the worng excel file');
                return ['status' => 'error', 'message' => 'Upload the worng excel file'];
            }
        }

        $objects = $this->employeePolicyModel->getDeletionEmployeeDataForExportExcel($ref_data, 1);
        $endorsement_data =  $objects;

        // dd($endorsement_data);

        if ($endorsement_data == null || empty($endorsement_data)) {

            $data = [
                'status' => 'failed-1',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'Deletion File Validation ENDORSEMENT ID already updated');

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The list of employees provided has already been updated with the ENDORSEMENT ID, or this is not the correct file';
            $this->myLogger->logme('error', 'Deletion File Validation ENDORSEMENT ID already updated or the uploadedfile is not correct');
        }

        $excel_data_count = count($excel_data);
        $endorsement_data_count = count($endorsement_data);

        $this->myLogger->logme('error', 'Deletion File Validation excel file count : {data}', ['data' => $excel_data_count]);
        $this->myLogger->logme('error', 'Deletion File Validation database count : {data}', ['data' => $endorsement_data_count]);


        $difference = $endorsement_data_count - $excel_data_count;

        $status = 'in-progress';
        if ($excel_data_count < $endorsement_data_count) {

            $status = 'in-progress-partially';
            $partially_updated_data = 'Expected : ' . $endorsement_data_count . ',  ' . 'Updated : ' . $excel_data_count . ',  ' . 'difference : ' . $difference;
            $this->myLogger->logme('error', 'Deletion File Validation excel file count partially : {data}', ['data' => $partially_updated_data]);
        }



        if ($endorsement_data_count < $excel_data_count) {

            $data = [
                'status' => 'failed-3',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'Deletion File Validation excel file count ( {excel} ) exceeds db count ( {db} )', ['db' => $endorsement_data_count, 'excel' => $excel_data_count]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The Excel record count exceeds the DB record count. excel file count : ' .  $excel_data_count . '  db count : ' .  $endorsement_data_count;
        }


        $errors = []; // Initialize an array to store errors
        $missing_id = [];
        $batch_list_id = [];

        foreach ($endorsement_data as $key => $endorsement_value) {

            $key = $key + 1;

            if (!isset($excel_data[$key])) {
                break;
            }


            if ($excel_data[$key][15] === null) {
                $missing_id[$key][] = [
                    'row' => $key,
                    'column' => 15,
                ];
            }


            if ($endorsement_value['emp_name'] != $excel_data[$key][2]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 2,
                    'db_data' => $endorsement_value['emp_name'],
                    'excel_data' => $excel_data[$key][2]
                ];
            }

            if ($endorsement_value['emp_code'] != $excel_data[$key][1]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 1,
                    'db_data' => $endorsement_value['emp_code'],
                    'excel_data' => $excel_data[$key][1]
                ];
            }

            if ($endorsement_value['emp_dob'] != $excel_data[$key][3]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 3,
                    'db_data' => $endorsement_value['emp_dob'],
                    'excel_data' => $excel_data[$key][3]
                ];
            }

            if ($endorsement_value['emp_gender'] != $excel_data[$key][4]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 4,
                    'db_data' => $endorsement_value['emp_gender'],
                    'excel_data' => $excel_data[$key][4]
                ];
            }


            if ($endorsement_value['emp_relationship'] != $excel_data[$key][5]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 5,
                    'db_data' => $endorsement_value['emp_relationship'],
                    'excel_data' => $excel_data[$key][5]
                ];
            }

            if ($endorsement_value['basic_cover_si'] != $excel_data[$key][6]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 6,
                    'db_data' => $endorsement_value['basic_cover_si'],
                    'excel_data' => $excel_data[$key][6]
                ];
            }

            if ($endorsement_value['dateofexit'] != $excel_data[$key][7]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 7,
                    'db_data' => $endorsement_value['dateofexit'],
                    'excel_data' => $excel_data[$key][7]
                ];
            }

            if ($endorsement_value['policy_end_date'] != $excel_data[$key][8]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 8,
                    'db_data' => $endorsement_value['policy_end_date'],
                    'excel_data' => $excel_data[$key][8]
                ];
            }

            if ($endorsement_value['no_of_days'] != $excel_data[$key][9]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 9,
                    'db_data' => $endorsement_value['no_of_days'],
                    'excel_data' => $excel_data[$key][9]
                ];
            }



            if ($endorsement_value['premium'] != $excel_data[$key][10]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 10,
                    'db_data' => $endorsement_value['premium'],
                    'excel_data' => $excel_data[$key][10]
                ];
            }

            if ($endorsement_value['pro_rata_premium'] != $excel_data[$key][11]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 11,
                    'db_data' => $endorsement_value['pro_rata_premium'],
                    'excel_data' => $excel_data[$key][11]
                ];
            }

            if ($endorsement_value['gst'] != $excel_data[$key][12]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 12,
                    'db_data' => $endorsement_value['gst'],
                    'excel_data' => $excel_data[$key][12]
                ];
            }

            $batch_list_id[] = array('emp_policy_id' =>$endorsement_value['primaryKey'], 'batch_code' => $batch_code, 'created_by' => $user_id);
        }


        $error_count = count($errors);
        $json_errors = json_encode($errors);


        $missing_id_count = count($missing_id);
        $json_missing_id = json_encode($missing_id);

        // dd($error_count, $missing_id_count, $json_errors, $json_missing_id, $batch_list_id);

        if ($missing_id_count > 0) {

            $data = [

                'count' => $endorsement_data_count,
                'error_data' => $json_missing_id,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The ENDORSEMENT ID column is either partially or entirely empty.';
        }


        if ($error_count > 0) {

            $data = [
                'count' => $endorsement_data_count,
                'error_data' => $json_errors,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'Deletion File Validation Failed';

        } else {

            $data = [
                'count' => $endorsement_data_count,
                'status' => $status,
                'error_data' => $partially_updated_data ?? null,
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $insert = $this->batchListModel->insertBatch($batch_list_id);


            $job_details  = new Jobs();
            $r = Jobs::addJob(['job_name' => 'importDeletionUpdateEndorsementID', 'payload' => ['file_id' => $file_id]]);

            // $this->importDeletionUpdateEndorsementID(['file_id' => $file_id]);

            return 'Deletion File Validation ENDORSEMENT ID validated the file Successfully';
        }

    }


    public function importDeletionUpdateEndorsementID($params)
    {

        $this->myLogger->logme('error', 'Deletion Update Endorsement ID --   Function called');

        $file_id = $params['file_id'];
        $file = $this->batchFileModel->find($file_id);
        if (!$file) {

            $data = [
                'status' => 'failed-4',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'Deletion Update Endorsement ID --     The Physical file not found --   File id : {data}', ['data' => $file_id]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'Deletion Update Endorsement ID --     The Physical file not found'; // Return error code if file not found
        }

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $status = $file['status'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];

        $insurer_id = $this->clientPolicyModel->where('id', $client_policy_id)->first();

        $CD_Account_Number = $this->CDMasterModel
                    ->where('client_id', $client_id)
                    ->where('insurer_id', $insurer_id['insurer_id'])
                    ->first();


        $status_val = 'success';
        if ($status == 'in-progress-partially') {

            $status_val = 'partially success';
        }

        $this->myLogger->logme('error', 'Deletion Update Endorsement ID --      file name : {data}', ['data' => $file['file_name']]);

        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];
        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        unset($excel_data[0]);
        // array_pop($excel_data);

        $emp_count = count($excel_data); //excel file count

        $employees_table_data = [];
        $emp_endorsement_table_data = [];
        $employee_policy_table_data = [];
        $employee_policy_table_primaryKey = [];
        $endorsement_id = '';
        $enrollment_file_id = null; //files table primary key

        $totals = 0;

        
        foreach ($excel_data as $key => $value) {

            $emp_name = $value[2]; //employee name
            $emp_code = $value[1]; //employee code
            $totals = $totals + $value[13];
            $endorsement_id = $value[15];

            if(!empty($emp_code) && !empty($emp_name)){

                $fetch_data = [
                    'client_policy_id' => $client_policy_id,
                    'client_branch_id' => $client_branch_id,
                    'client_id' => $client_id,
                    'emp_name' => $emp_name,
                    'emp_code' => $emp_code
                ];

                $result = $this->employeePolicyModel->fetchEmpEndorsementData($fetch_data);
                // dd($result, db_connect()->getLastQuery());

                if (isset($result['emp_endorsement_primarykey']) && $result['emp_endorsement_primarykey'] !== null) {

                    $enrollment_file_id = $result['file_id']; //files table primary key
                    $employee_policy_table_primaryKey[] = $result['emp_policy_primarykey']; //for cash deposite
                    $employee_policy_table_data[] = array('id' => $result['emp_policy_primarykey'], 'date_of_exit' => $result['date_of_exit'], 'reason_for_exit' => $result['reason_for_exit'], 'claim_status' => $result['claim_status'], 'status' => $result['status']);
                    // $employees_table_data[] = array('id' => $result['employees_id'], 'emp_status' => $result['status']);
                    $emp_endorsement_table_data[] = array('id' => $result['emp_endorsement_primarykey'], 'group_key' => $result['group_key'], 'endorsement_id' => $value[15], 'status' => 'complete');
                }

            }
          
        }

        $rounded_totals = round($totals, 2);

        // dd($employees_table_data, $emp_endorsement_table_data, $employee_policy_table_data, $employee_policy_table_primaryKey, $totals, $emp_count);

        // Check if $employees_table_data is null or empty
        // if (empty($employees_table_data)) {
        //     return ['status' => 'error', 'message' => 'Employees table data is empty or null.'];
        // }

        // Check if $employee_policy_table_data is null or empty
        if (empty($employee_policy_table_data)) {
            return ['status' => 'error', 'message' => 'Employee policy table data is empty or null.'];
        }

        // Check if $emp_endorsement_table_data is null or empty
        if (empty($emp_endorsement_table_data)) {
            return ['status' => 'error', 'message' => 'Employee endorsement table data is empty or null.'];
        }

        // $this->employeeModel->updateBatch($employees_table_data, 'id');
        $this->employeePolicyModel->updateBatch($employee_policy_table_data, 'id');
        $this->employeePolicyModel->bulkUpdateForEndorsement($emp_endorsement_table_data);
        $this->storeEndorsementNumber($file_id, $endorsement_id, $enrollment_file_id);

        // Update batch file status and amount
        $this->batchFileModel->update($file_id, [
            'count' => $emp_count,
            'status' =>  $status_val,
            'amount' =>  $rounded_totals,
        ]);

        $this->myLogger->logme('error', 'Deletion Update Endorsement ID --      Employee count : {data}', ['data' => $emp_count]);
        $this->myLogger->logme('error', 'Deletion Update Endorsement ID --      Batch File status : {data}', ['data' => $status_val]);

        //call the cash deposite function
        if ($file['insurer_or_tpa'] == 'insurer') {

            $policy_name = $this->getPolicyNameUsingClientPolicyId($client_policy_id);
            $job_details  = new Jobs();
            $r = Jobs::addJob(['job_name' => 'cashDepositCalculationForDeletion', 'payload' => [
                'employeeIds' => $employee_policy_table_primaryKey,
                'client_id' => $client_id,
                'client_policy_id' => $client_policy_id,
                'client_branch_id' => $client_branch_id,
                'cd_ac_no' => $CD_Account_Number['cd_ac_no'] ?? null,
                'endorsement_no' => $endorsement_id ?? null,
                'count' => $emp_count,
                'event_name' => $file['event_type'],
                'policy_name' => $policy_name['policy_name'],
                'user_id' => $user_id,
            ]]);
        }

        $file_data = $this->getDataByFileId($file_id, 'success');
        $this->setPullNotification($file_data);

        //import file to upload Google Drive
        // if(excelFileGDriveUpload($file_id, 'batch_file')){
        //     $this->myLogger->logme('error', 'Deletion File Update Google Drive File Upload --    File Uploaded Successfully');
        // }else{
        //     $this->myLogger->logme('error', 'Deletion File Update Google Drive File Upload --    File Uploaded Field');
        // }

        return 'Deletion Update Endorsement ID --      ' . $status_val . '--   Updated Count : ' . $emp_count;
    }





    //Endorsement Addition and Dependent Addition

    //not in use
    public function AdditionAndDependentAddition($params)
    {

        $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation --   Function called');

        $file_id = $params['file_id'];
        $file = $this->batchFileModel->where('id', $file_id)->first();

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];

        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];

        if (!file_exists($file_name_with_path)) {

            $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation --   The Physical file not found');
            $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation --   File Name : {data}', ['data' => $file['file_name']]);
            $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation --   File Path : {data}', ['data' => $file_name_with_path]);

            return 'The Physical file not found';
        }

        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        unset($excel_data[0]);


        $endorsement_data = $this->empEndorsementModel
            ->select("
                                emp_endorsement.id, 
                                emp_endorsement.pk, 
                                emp_endorsement.emp_code, 
                                emp_endorsement.endorsement_id, 
                                emp_endorsement.old_value, 
                                emp_endorsement.new_value, 
                                emp_endorsement.field_name, 
                                emp_endorsement.remarks, 
                                emp_endorsement.actions, 
                                employees.id AS primaryKey, 
                                employees.name AS emp_name, 
                                employees.dob AS emp_dob, 
                                employees.gender AS emp_gender, 
                                employees.client_id AS emp_client_id, 
                                'Has Define' AS emp_type,
                                employee_polices.uhid,
                                employees.relationship_code
                            ")
            ->join("employees", "employees.id = emp_endorsement.pk", "left")
            ->join("employee_polices", "employees.id = employee_polices.employee_id", "left")
            ->where("employees.client_id", $client_id)
            ->where("employee_polices.client_policy_id", $client_policy_id)
            ->where("employees.client_branch_id", $client_branch_id)
            ->where("employees.is_active", 1)
            ->where("employees.emp_status", "active")
            ->where("emp_endorsement.actions", "c")
            ->where("(emp_endorsement.endorsement_id IS NULL OR emp_endorsement.endorsement_id = '')")
            ->findAll();



        // dd($endorsement_data, $excel_data);


        if ($endorsement_data == null || empty($endorsement_data)) {

            $data = [
                'status' => 'failed-1',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'correctionFileValidation ENDORSEMENT ID already updated');

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The list of employees provided has already been updated with the ENDORSEMENT ID, or this is not the correct file';

            $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation ENDORSEMENT ID already updated or the uploadedfile is not correct');
        }

        $excel_data_count = count($excel_data);
        $endorsement_data_count = count($endorsement_data);

        $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation excel file count : {data}', ['data' => $excel_data_count]);
        $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation database count : {data}', ['data' => $endorsement_data_count]);


        $difference = $endorsement_data_count - $excel_data_count;

        $status = 'in-progress';
        if ($excel_data_count < $endorsement_data_count) {

            $status = 'in-progress-partially';
            $partially_updated_data = 'Expected : ' . $endorsement_data_count . ',  ' . 'Updated : ' . $excel_data_count . ',  ' . 'difference : ' . $difference;
            $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation excel file count partially : {data}', ['data' => $partially_updated_data]);
        }



        if ($endorsement_data_count < $excel_data_count) {

            $data = [
                'status' => 'failed-3',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'Addition And Dependent Addition File Validation excel file count ( {excel} ) exceeds db count ( {db} )', ['db' => $endorsement_data_count, 'excel' => $excel_data_count]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The Excel record count exceeds the DB record count. excel file count : ' .  $excel_data_count . 'db count : ' .  $endorsement_data_count;
        }


        $errors = []; // Initialize an array to store errors
        $missing_id = [];
        $batch_list_id = [];

        foreach ($endorsement_data as $key => $endorsement_value) {

            $key = $key + 1;

            if (!isset($excel_data[$key])) {
                break;
            }


            if ($excel_data[$key][10] === null) {
                $missing_id[$key][] = [
                    'row' => $key,
                    'column' => 10,
                ];
            }


            if ($endorsement_value['emp_name'] != $excel_data[$key][2]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 2,
                    'db_data' => $endorsement_value['emp_name'],
                    'excel_data' => $excel_data[$key][2]
                ];
            }

            if ($endorsement_value['emp_code'] != $excel_data[$key][0]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 0,
                    'db_data' => $endorsement_value['emp_code'],
                    'excel_data' => $excel_data[$key][0]
                ];
            }

            if ($endorsement_value['emp_dob'] != $excel_data[$key][5]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 5,
                    'db_data' => $endorsement_value['emp_dob'],
                    'excel_data' => $excel_data[$key][5]
                ];
            }

            if ($endorsement_value['emp_gender'] != $excel_data[$key][6]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 6,
                    'db_data' => $endorsement_value['emp_gender'],
                    'excel_data' => $excel_data[$key][6]
                ];
            }

            if ($endorsement_value['old_value'] != $excel_data[$key][7]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 7,
                    'db_data' => $endorsement_value['old_value'],
                    'excel_data' => $excel_data[$key][7]
                ];
            }

            if ($endorsement_value['new_value'] != $excel_data[$key][8]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 8,
                    'db_data' => $endorsement_value['new_value'],
                    'excel_data' => $excel_data[$key][8]
                ];
            }



            if ($endorsement_value['uhid'] != $excel_data[$key][1]) {
                $errors[$key][] = [
                    'row' => $key,
                    'column' => 1,
                    'db_data' => $endorsement_value['uhid'],
                    'excel_data' => $excel_data[$key][1]
                ];
            }

            $batch_list_id[] = $endorsement_value['primaryKey'];
        }


        $error_count = count($errors);
        $json_errors = json_encode($errors);


        $missing_id_count = count($missing_id);
        $json_missing_id = json_encode($missing_id);

        // dd($error_count, $missing_id_count, $json_errors, $json_missing_id, $batch_list_id);


        if ($missing_id_count > 0) {

            $data = [

                'count' => $endorsement_data_count,
                'error_data' => $json_missing_id,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'The ENDORSEMENT ID column is either partially or entirely empty.';
           
        }


        if ($error_count > 0) {

            $data = [

                'count' => $endorsement_data_count,
                'error_data' => $json_errors,
                'status' => 'failed',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            return 'Correction Validation Failed';

        } else {

            $data = [
                'count' => $endorsement_data_count,
                'status' => $status,
                'error_data' => $partially_updated_data ?? null,
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();


            foreach ($batch_list_id as $key => $value) {

                $data = [
                    'emp_policy_id' => $value,
                    'batch_code' => $batch_code,
                    'created_by' => $user_id,
                ];

                $insert = $this->batchListModel->insert($data);
            }


            $job_details  = new Jobs();                             
            $r = Jobs::addJob(['job_name' => 'importAdditionAndDependentAdditionUpdateEndorsementID','payload' => ['file_id' => $file_id]]);

            // $this->importAdditionAndDependentAdditionUpdateEndorsementID(['file_id' => $file_id]);

            return 'Correction Validation Success';
        }
    }


    public function importAdditionAndDependentAdditionUpdateEndorsementID($params){

        $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID called');

        $file_id = $params['file_id'];
        $file = $this->batchFileModel->find($file_id);
        if (!$file) {

            $data = [
                'status' => 'failed-4',
            ];

            $this->batchFileModel->where('id', $file_id)->set($data)->update();
            $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID the Physical file not found - file id : {data}', ['data' => $file_id]);

            $file_data = $this->getDataByFileId($file_id, 'failure');
            $this->setPullNotification($file_data);

            return 'importCorrectionUpdateEndorsementID the Physical file not found'; // Return error code if file not found
        }

        $client_id = $file['client_id'];
        $client_policy_id = $file['client_policy_id'];
        $client_branch_id = $file['client_branch_id'];
        $insurer_or_tpa = $file['insurer_or_tpa'];
        $status = $file['status'];
        $batch_code = $file['batch_code'];
        $user_id = $file['created_by'];


        $status_val = 'success';
        if ($status == 'in-progress-partially') {

            $status_val = 'partially success';
        }

        $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID  file name : {data}', ['data'=> $file['file_name']]);

        $file_name_with_path = WRITEPATH . "/uploads/import_excel/" . $file['file_name'];
        $excel_data = $this->readExcelFileToArray($file_name_with_path);
        unset($excel_data[0]); 

        $emp_ids = [];
        $emp_details = [];
        $endorsement_id = [];
        $endorsement_details = [];

        $emp_count = count($excel_data);
        $db = \Config\Database::connect();


        foreach ($excel_data as $key => $value) {

            $emp_code = $value[0];
            $old_value = $value[7];
            $endorsement_id[] = $value[10];
            $uhid = $value[1];


            $result =  $this->empEndorsementModel
            ->select('emp_endorsement.*, employees.id as emp_id')
            ->join('employees', 'employees.id = emp_endorsement.pk')
            ->join('employee_polices', 'employee_polices.employee_id = employees.id')
            ->where('employees.emp_code', $emp_code)
            ->where('employees.client_id', $client_id)
            ->where('employees.client_branch_id', $client_branch_id)
            ->where('employee_polices.client_policy_id', $client_policy_id)
            ->where('employee_polices.uhid', $uhid)
            ->where('emp_endorsement.old_value', $old_value)
            
            ->where('employee_polices.is_active', 1)
            ->where('employee_polices.status', 'active')
            ->where('employees.is_active', 1)
            ->where('employees.emp_status', 'active')
            ->first();

            if (isset($result['id']) && $result['id'] !== null) {

                // return $result;
                $emp_details[] = array('id' => $result['emp_id'], $result['field_name'] => $value[8]);
                $endorsement_details[] = array('group_key' => $result['group_key'], 'id' => $result['id'], 'endorsement_id' => $value[10], 'status' => 'complete');
            }

        }

        // return [$emp_details, $endorsement_details];

       $this->empEndorsementModel->updateBatch($endorsement_details, 'group_key');
       $this->employeePolicyModel->bulkUpdateForCorrection($emp_details);

        // Update batch file status and amount
        $this->batchFileModel->update($file_id, [
            'count' => $emp_count,
            'status' =>  $status_val,
        ]);


        $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID  employee count : {data}', ['data'=> $emp_count]);
        $this->myLogger->logme('error', 'importCorrectionUpdateEndorsementID  batch file status : {data}', ['data'=> $status_val]);


        $file_data = $this->getDataByFileId($file_id, 'success');
        $this->setPullNotification($file_data);


        return 'Import Correction Updated '. $status_val . '- Updated Count : ' . $emp_count;

    }

    //end not in use

    /**
     * The below functions are Calculates and records cash deposits for employee policies at Deletion.
     * 
     * @param array $arrayData An array containing necessary data including : 
     *      - employee IDs, 
     *      - client policy ID, 
     *      - client ID,
     *      - count of employees,
     *      - policy_name, and 
     *      - event type.
     * 
     * @return bool Returns true if the operation is successful, otherwise returns 0.
     */

    public function cashDepositCalculationForInception($arrayData)
    {
        if (!empty($arrayData)) {


            $client_branch_data = $this->clientBranchModel->where('id', $arrayData['client_branch_id'])->first();
            $units = json_decode($client_branch_data['units']);

            // dd(json_decode($client_branch_data['units'])); 


            foreach($units as $unit)
            {
                $client_policy_id = $arrayData['client_policy_id'];
                $cd_ac_pk = $this->clientPolicyModel->select('cd_ac_pk')->where('id',$client_policy_id)->first();
                $amount = $this->employeePolicyModel->query("
                        SELECT SUM(rata_premimum + gst) AS total_sum
                        FROM employee_polices
                        JOIN employees ON employees.id = employee_polices.employee_id
                        WHERE employees.unit = '$unit'
                        AND employee_polices.id IN (" . implode(',', $arrayData['employeeIds']) . ")
                    ")->getRow();

                if($amount){      

                    $insurer_id = $this->clientPolicyModel->where('id', $arrayData['client_policy_id'])->first();
                    $description = 'The following amount of Rs. ' . round($amount->total_sum, 2) . '/- has been debited for the ' . $arrayData['count'] . ' employees at ' . remove_underscore_capitalize_first_letter($arrayData['event_name']) . ' to ' . remove_underscore_capitalize_first_letter($arrayData['policy_name']) . '.';

                    $data = [
                        'amount' => $amount->total_sum ?? 0,
                        'sub_type_id' => 4,
                        'client_id' => $arrayData['client_id'],
                        'client_policy_id' => $arrayData['client_policy_id'],
                        'endorsement_no' => $arrayData['endorsement_no'] ?? null,
                        'cd_ac_no' => $arrayData['cd_ac_no'] ?? null,
                        'insurer_id' => $insurer_id['insurer_id'],
                        'unit' => $unit,
                        'description' => $description,
                        'transaction_type' => 'Debit',
                        'updated_by' => $arrayData['user_id'],
                        'event_name' => $arrayData['event_name'],
                        'is_active' => 1,
                        'cd_ac_pk'  => $cd_ac_pk['cd_ac_pk']
                    ];

                    $response = DepositHelper::saveDeposit($data,  $arrayData['user_id']);
                }

            }

            return true;


        } else {
            return 0;
        }
    }


    public function cashDepositCalculationForSIEnhancement($arrayData)
    {  
        // return $arrayData;

        if (!empty($arrayData)) {

            $client_branch_data = $this->clientBranchModel->where('id', $arrayData['client_branch_id'])->first();
            $units = json_decode($client_branch_data['units']);

            foreach($units as $unit)
            {

                $amount = $this->employeePolicyModel->query("
                    SELECT SUM(rata_premimum + gst) AS total_sum
                    FROM employee_polices
                    JOIN employees ON employees.id = employee_polices.employee_id
                    WHERE employees.unit = '$unit'
                    AND employee_polices.id IN (" . implode(',', $arrayData['employeeIds']) . ")
                ")->getRow();

                if($amount){

                    $insurer_id = $this->clientPolicyModel->where('id', $arrayData['client_policy_id'])->first();
                    $description = 'The following amount of ' . round($amount->total_sum, 2) . ' has been credited for the ' . $arrayData['count'] . ' employees at ' . remove_underscore_capitalize_first_letter($arrayData['event_name']) . ' to ' . remove_underscore_capitalize_first_letter($arrayData['policy_name']) . '.';

                    $data = [

                        'amount' => $amount->total_sum,
                        'sub_type_id' => 4,
                        'client_id' => $arrayData['client_id'],
                        'client_policy_id' => $arrayData['client_policy_id'],
                        'endorsement_no' => $arrayData['endorsement_no'] ?? null,
                        'cd_ac_no' => $arrayData['cd_ac_no'] ?? null,
                        'insurer_id' => $insurer_id['insurer_id'],
                        'unit' => $unit,
                        'description' => $description,
                        'transaction_type' => 'Debit',
                        'updated_by' => $arrayData['user_id'],
                        'event_name' => $arrayData['event_name'],
                        'is_active' => 1,
                        'cd_ac_pk'  => $insurer_id['cd_ac_pk']
                    ];

                    $response = DepositHelper::saveDeposit($data, $arrayData['user_id']);
                }
            }

            $msg = "SI Enhancement Cash Deposite Updated Successfully ";
            return [$msg];

            // print_r($response);
            // $query = $this->employeePolicyModel->getLastQuery();
            // echo $query . "
"; 

        } else {
            return 0;
        }
    }


    public function cashDepositCalculationForDeletion($arrayData)
    {
        // print_r($arrayData);   
        if (!empty($arrayData)) {

            // dd($arrayData);

            // echo '
';
            // print_r($arrayData); die;

            $client_branch_data = $this->clientBranchModel->where('id', $arrayData['client_branch_id'])->first();
            $units = json_decode($client_branch_data['units']);

            // dd($client_branch_data, $units);

            $get_insurer_id_from_client_policy = db_connect()->table('client_policy')
                ->select('insurer_id,cd_ac_pk')
                ->where('id', $arrayData['client_policy_id'])
                ->get()
                ->getRowArray();

            // dd($get_insurer_id_from_client_policy);

            $add_one_day = 0;

            if (!empty($get_insurer_id_from_client_policy)) {

                $get_the_insurer_add_one_for_delete = db_connect()
                    ->table('insurers')
                    ->select('deletion_add_day')
                    ->where('id', $get_insurer_id_from_client_policy['insurer_id'])
                    ->get()
                    ->getRowArray();

                // dd($get_the_insurer_add_one_for_delete, $get_the_insurer_add_one_for_delete['deletion_add_day']);

                if (!empty($get_the_insurer_add_one_for_delete) && $get_the_insurer_add_one_for_delete['deletion_add_day'] == 1) {
                    $add_one_day = 1;
                }
            }

            // dd($add_one_day);

            foreach($units as $unit) {

                // $amount = $this->employeePolicyModel->query("
                //     SELECT SUM(rata_premimum + gst) AS total_sum
                //         FROM employee_polices
                //         JOIN employees ON employees.id = employee_polices.employee_id
                //         WHERE employees.unit = '$unit'
                //         AND employee_polices.id IN (" . implode(',', $arrayData['employeeIds']) . ")
                //     ")->getRow();

                $amount = $this->employeePolicyModel->query("
                    SELECT 
                        SUM(ROUND(
                            ((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, emp_endorsement.new_value) + '$add_one_day')) / 365) + 
                            (((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, emp_endorsement.new_value) + '$add_one_day')) / 365) * 0.18), 
                            2
                        )) AS total_sum
                    FROM 
                        employee_polices
                    JOIN 
                        employees ON employees.id = employee_polices.employee_id
                    JOIN 
                        emp_endorsement ON emp_endorsement.pk = employee_polices.id
                    WHERE 
                        employee_polices.id IN (" . implode(',', $arrayData['employeeIds']) . ")
                        AND emp_endorsement.pk IN (" . implode(',', $arrayData['employeeIds']) . ")
                        AND employee_polices.claim_status = 0
                        AND emp_endorsement.field_name = 'date_of_exit'
                ")->getRow();

                //  dd(db_connect()->getLastQuery(), $amount);

                if($amount){

                    $insurer_id = $this->clientPolicyModel->where('id', $arrayData['client_policy_id'])->first();
                    $description = 'The following amount of ' . round($amount->total_sum, 2) . ' has been credited for the ' . $arrayData['count'] . ' employees at ' . remove_underscore_capitalize_first_letter($arrayData['event_name']) . ' to ' . remove_underscore_capitalize_first_letter($arrayData['policy_name']) . '.';

                    $data = [

                        'amount' => $amount->total_sum,
                        'sub_type_id' => 3,
                        'client_id' => $arrayData['client_id'],
                        'client_policy_id' => $arrayData['client_policy_id'],
                        'endorsement_no' => $arrayData['endorsement_no'] ?? null,
                        'cd_ac_no' => $arrayData['cd_ac_no'] ?? null,
                        'insurer_id' => $insurer_id['insurer_id'],
                        'description' => $description,
                        'transaction_type' => 'Credit',
                        'updated_by' => $arrayData['user_id'],
                        'event_name' => $arrayData['event_name'],
                        'is_active' => 1,
                        'cd_ac_pk'  => $insurer_id['cd_ac_pk']
                    ];

                    $response = DepositHelper::saveDeposit($data, $arrayData['user_id']);
                }
            }

            $msg = "Deletion Cash Deposite Updated Successfully -- ";
            return [$msg];
            // print_r($response);
            // $query = $this->employeePolicyModel->getLastQuery();
            // echo $query . "
"; 

        } else {
            return 0;
        }
    }




    /**
     * Below function retrieves the policy name associated with a given client policy ID.
     *
     * @param int $client_policy_id The ID of the client policy.
     * @return mixed Returns the policy name if found, otherwise null.
     */

    public function getPolicyNameUsingClientPolicyId($client_policy_id)
    {
        return $this->clientPolicyModel->select('policy_type.policy_type as policy_name')
            ->join('policy_type', 'policy_type.id = client_policy.policy_type_id')
            ->where('client_policy.id', $client_policy_id)
            ->first();
    }




    /**
     * Below function converts row data into column data format for SI enhancement.
     *
     * This function takes an array of data containing information about SI enhancement
     * and calculates additional fields such as pro rata premium, GST, and total.
     *
     * @param array $data The array of data containing SI enhancement information.
     * @return array Returns the converted data in column format.
     */
    public function convertRowTColumnForSIEnhancement($data)
    {

        $result = array();

        foreach ($data as $obj) {

            $pro_rata_premium = round(($obj->difference_premium * $obj->no_of_days / 365), 2);
            $gst = round(($pro_rata_premium * 18 / 100), 2);
            $total = round(($pro_rata_premium + $gst), 2);

            $old_total = round(($obj->old_rata + $obj->old_gst), 2);


            $result[] = array(
                "field_name" => 'Basic Cover SI',
                "old_value" => $obj->old_basic_cover_si,
                "new_value" => $obj->new_basic_cover_si
            );

            $result[] = array(
                "field_name" => 'Premium',
                "old_value" => $obj->old_si_premium,
                "new_value" => $obj->new_si_premium
            );

            $result[] = array(
                "field_name" => 'SI Enhancement Date',
                "old_value" => change_date_format($obj->old_date, 'Y-m-d', 'd-M-Y'),
                "new_value" => change_date_format($obj->date_of_coverage, 'Y-m-d', 'd-M-Y')
            );

            $result[] = array(
                "field_name" => 'Difference Premium',
                "old_value" => ' --- ',
                "new_value" => $obj->difference_premium
            );

            $result[] = array(
                "field_name" => 'No of Days',
                "old_value" => $obj->old_days,
                "new_value" => $obj->no_of_days
            );

            $result[] = array(
                "field_name" => 'Pro Rata Premium',
                "old_value" => $obj->old_rata,
                "new_value" =>  $pro_rata_premium
            );

            $result[] = array(
                "field_name" => 'GST(18%)',
                "old_value" => $obj->old_gst,
                "new_value" => $gst
            );

            $result[] = array(
                "field_name" => 'Total',
                "old_value" => $old_total,
                "new_value" => $total
            );
        }

        return $result;
    }



    /**
     * Below function converts row data into column data format for deletion records.
     *
     * This function takes an array of data containing information about deletion records
     * and converts it into a column format. It also calculates additional fields such as
     * period of non-coverage, premium for non-coverage period, GST, total amount, and
     * claim status based on the exist_reason field.
     *
     * @param array $data The array of data containing deletion records information.
     * @return array Returns the converted data in column format.
     */
    public function convertRowTColumnForDeletion($data)
    {

        $result = array();

        foreach ($data as $obj) {

            $old_total = round(($obj->old_rata + $obj->old_gst), 2);

            $result[] = array(
                "field_name" => 'Policy Period',
                "data" => change_date_format($obj->start_date, 'Y-m-d', 'd-M-Y') . ' - 
 ' . change_date_format($obj->end_date, 'Y-m-d', 'd-M-Y'),
            );

            $result[] = array(
                "field_name" => 'Exit on',
                "data" => change_date_format($obj->date_of_leaving, 'Y-m-d', 'd-M-Y'),
            );

            $result[] = array(
                "field_name" => 'Reason',
                "data" => $obj->exist_reason,
            );



            if ($obj->exist_reason != 'death') {

                $result[] = array(
                    "field_name" => 'Period of non coverage',
                    "data" => $obj->no_of_days
                );

                $result[] = array(
                    "field_name" => 'Premium for non coverage period',
                    "data" => $obj->pro_rata_premium
                );

                $result[] = array(
                    "field_name" => 'GST(18%)',
                    "data" => $obj->gst
                );

                $result[] = array(
                    "field_name" => 'Total Amount',
                    "data" => $obj->total
                );

                $result[] = array(
                    "field_name" => 'Claim',
                    "data" => '---',
                );
            }
        }

        return $result;
    }

    // -----------------------------------------------------------------------------------


    public function readExcelToArray($file)
    {
        if ($file->isValid() && !$file->hasMoved()) {
            $file = $file;

            try {

                $reader = IOFactory::createReaderForFile($file->getPathname());
                $spreadsheet = $reader->load($file->getPathname());

                // Get the active sheet
                $sheet = $spreadsheet->getActiveSheet();

                // Iterate through rows to read data
                $data = [];
                foreach ($sheet->getRowIterator() as $row) {
                    $rowData = [];
                    foreach ($row->getCellIterator() as $cell) {
                        $rowData[] = $cell->getValue();
                    }
                    $data[] = $rowData;
                }

                return $data;
            } catch (SpreadsheetReaderException $e) {

                error_log('PhpSpreadsheet reader exception: ' . $e->getMessage());
                return false;
            }
        } else {
            return false;
        }
    }

    public function insertBatchFileAndBatchListForImportExcel($data, $ids)
    {

        $random_number_count = 4;
        $data['batch_code'] = generate_random_string($random_number_count);
        $data['created_by'] = get_session_userid();

        $insert = $this->batchFileModel->insert($data);
        $batch_file_batch_code = $this->batchFileModel->where('id', $insert)->first();

        if ($insert) {
            foreach ($ids as $id) {
                $batch_list_data['batch_code'] =  $batch_file_batch_code['batch_code'];
                $batch_list_data['emp_policy_id'] =  $id;
                $batch_list_data['created_by'] = get_session_userid();
                $this->batchListModel->insert($batch_list_data);
            }
        }

        return true;
    }

    public function sendMailForDownloadingECard(array $ids, $single_mail = null)
    {
        $this->myLogger->logme('info', 'sendMailForDownloadingECard - Function called');
    
        if (empty($ids)) {
            $this->myLogger->logme('error', 'sendMailForDownloadingECard - employee_policy_ids are empty');
            return false;
        }
    
        $temp_id = $ids[0];
    
        $get_client_info = $this->employeePolicyModel
            ->select('
                employee_polices.client_policy_id, 
                employees.emp_code, 
                employees.email_corporate, 
                employees.client_id, 
                employees.name, 
                employee_polices.rand_string, 
                employee_polices.tpa_id'
            )
            ->join('employees', 'employees.id = employee_polices.employee_id')
            ->where('employees.emp_status', 'active')
            ->where('employees.is_active', '1')
            ->where('employee_polices.status', 'active')
            ->where('employee_polices.is_active', '1')
            ->where('employee_polices.id', $temp_id)
            ->first();
    
        if (!$get_client_info) {
            $this->myLogger->logme('error', 'sendMailForDownloadingECard - No client information found for the first ID');
            return false;
        }
    
        $client_data = $this->clientModel->where('id', $get_client_info['client_id'])->first();
        $notification = $this->notificationModel
            ->where('client_id', $get_client_info['client_id'])
            ->where('template_name', 'member_ecard_mail')
            ->first();
    
        if (empty($notification) || $notification['enabled'] != 1) {
            $this->myLogger->logme('error', 'sendMailForDownloadingECard - Notification setup not enabled or missing for E-Card Notification');
            return false;
        }
    
        try {
            $this->myLogger->logme('error', 'sendMailForDownloadingECard - Inside try block');
    
            $count = 0;
            $processedCount = 0;
            $wholeData = [];
            // $wholeMailData = [];
    
            foreach ($ids as $id) {

                $emp_details = $this->employeePolicyModel
                    ->select('
                        employees.id as emp_id, 
                        employees.client_id,  
                        employees.client_branch_id,  
                        employees.relationship, 
                        employees.emp_code, 
                        employees.email_corporate, 
                        employees.name, 
                        employees.mobile, 

                        employee_polices.id as emp_policy_id, 
                        employee_polices.client_policy_id, 
                        employee_polices.rand_string, 
                        employee_polices.tpa_id
                    ')
                    ->join('employees', 'employees.id = employee_polices.employee_id')
                    ->where('employees.emp_status', 'active')
                    ->where('employees.is_active', '1')
                    ->where('employee_polices.status', 'active')
                    ->where('employee_polices.is_active', '1')
                    ->where('employee_polices.id', $id)
                    ->first();
    
                if ($emp_details) {
                    $this->myLogger->logme('error', 'sendMailForDownloadingECard - Processing employee_policy_id: {id}', ['id' => $id]);
    
                    if (!empty($emp_details['email_corporate']) && $emp_details['relationship'] === 'Self') {
                        $params = [
                            'rand_string' => $emp_details['rand_string'],
                            'tpa_id' => $emp_details['tpa_id'],
                            'notification' => $notification,
                            'client_data' => $client_data,
                            'get_emp_email_and_other_details' => $emp_details,
                        ];

                        $params['common'] = [
                            'client_id' => $emp_details['client_id'], 
                            'client_branch_id' => $emp_details['client_branch_id'], 
                            'client_policy_id' => $emp_details['client_policy_id'],
                            'employee_policy_id' => $emp_details['emp_policy_id'],
                            'employee_id' => $emp_details['emp_id'],
                            'mail_type' => 'member_ecard_mail',
                        ];

                        $wholeData[] = sendMailNotification::sendMailNotification('member_ecard_mail', $params);
                        // $wholeMailData[] = sendMailNotification::sendMailNotification('member_ecard_mail', $params);
                        $count++;
                    }
                    $processedCount++;
    
                    // Send batch emails or handle single email
                    if ($count == 20 || $processedCount == count($ids)) {
                        if (!empty($wholeData)) {
                            if ($single_mail == 1) {
                                $this->myLogger->logme('error', 'sendMailForDownloadingECard - Sending single mail');
                                $mail_result = MailHelper::send_email($wholeData[0]);
                                $this->myLogger->logme('info', 'sendMailForDownloadingECard - Single mail result: {result}', ['result' => $mail_result]);
                                return $mail_result;
                            } else {
                                $this->myLogger->logme('error', 'sendMailForDownloadingECard - Creating a job for bulk mail');
                                Jobs::addJob(['job_name' => 'bulk_mail', 'payload' => $wholeData]);
                                $wholeData = [];
                                $count = 0;
                            }
                        }
                    }
                }
            }

            // dd($wholeMailData);
    
            $this->myLogger->logme('error', 'sendMailForDownloadingECard - Email processing completed');
            return true;
    
        } catch (\Exception $e) {
            $this->myLogger->logme('error', 'sendMailForDownloadingECard - Exception occurred: {message}', ['message' => $e->getMessage()]);
            return false;
        }
    }
    
    public function readExcelFileToArray($path)
    {
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path);
        $sheet = $spreadsheet->getActiveSheet();
    
        $highestRowAndColumn = $sheet->getHighestRowAndColumn();
        $excel_data = $sheet->rangeToArray('A1:' . $highestRowAndColumn['column'] . $highestRowAndColumn['row']);
    
        // Filter out empty or null rows
        $filtered_data = array_filter($excel_data, function($row) {
            // Check if all cells in the row are empty or null
            foreach ($row as $cell) {
                if (!is_null($cell) && $cell !== '') {
                    return true;
                }
            }
            return false;
        });
    
        return $filtered_data;
    }
    
    public function removeOldExportInfoFromBatchFile($params)
    {

        $client_id = $params['client_id'];
        $client_policy_id = $params['client_policy_id'];
        $insurer_or_tpa = $params['insurer_or_tpa'];
        $event_type = $params['event_type'];
        $actions = $params['actions'];


        $batch_data = $this->batchFileModel
            ->where('client_id', $client_id)
            ->where('client_policy_id', $client_policy_id)
            ->where('insurer_or_tpa', $insurer_or_tpa)
            ->where('event_type', $event_type)
            ->where('actions', $actions)
            ->first();

            if(!empty($batch_data)){

                $id = $batch_data['id'];
                $batch_code = $batch_data['batch_code'];
                $this->batchFileModel->where('id', $id)->delete();
                $this->batchListModel->where('batch_code', $batch_code)->delete();
            }
    }

    public function getDataByFileId($file_id, $status = 'success')
    {
       $data = $this->batchFileModel
        ->select('batch_files.*, clients.client_name, clients.short_name, policy_type.policy_type as policy_name, client_branch.branch_name')
        ->join('clients', 'clients.id = batch_files.client_id')
        ->join('client_policy', 'client_policy.id = batch_files.client_policy_id')
        ->join('client_branch', 'client_branch.id = batch_files.client_branch_id')
        ->join('policy_type', 'policy_type.id = client_policy.policy_type_id')
        ->where('batch_files.id', $file_id)
        ->first();

        if($status == 'success'){

            $msg_txt = $data['client_name'] . '( ' . $data['branch_name'] . ' )' . ' - ' . $data['policy_name'] . ' - ' . $data['event_type'] . ' - ' . $data['actions'] . ' - ' . $data['insurer_or_tpa'];
            $msg_title = 'File Upload Success';

        }else if ($status == 'failure'){

            $msg_txt = $data['client_name'] . '( ' . $data['branch_name'] . ' )' . ' - ' . $data['policy_name'] . ' - ' . $data['event_type'] . ' - ' . $data['actions'] . ' - ' . $data['insurer_or_tpa'];
            $msg_title = 'File Upload Failure';
        }

        $user_id = $data['created_by'];
        $url = 'employee/upload#KYC-DOC-tab';

        return ['msg_txt' => $msg_txt, 'user_id' => $user_id, 'url' => $url, 'status' => $status, 'title' => $msg_title];
    }

    public function setPullNotification($data)
    {
        $array = ['message_text' => $data['msg_txt'], 'action_url' => $data['url'], 'msg_status' => $data['status'], 'msg_title' => $data['title']];
        $jsonEncodeData = json_encode($array);

        $msg_data = [

            'message_text' => $jsonEncodeData,
            'user_id' => $data['user_id'],
            'role_id' => NULL,
            'team_id' => NULL,
            'message_type' => '1to1',
         ];

         $this->messageModel->insert($msg_data);
    }

    public function storeEndorsementNumber($file_id, $endorsement_no, $enrollment_file_id)
    {   
        // Log the function entry with input parameters
        $this->myLogger->logme('error',  "storeEndorsementNumber --- Starting function with file_id: $file_id and endorsement_no: $endorsement_no");
    
        // Log before querying the database
        $this->myLogger->logme('error', "storeEndorsementNumber --- Fetching file data for file_id: $file_id");
    
        $filedata = $this->batchFileModel
            ->select('
                batch_files.client_id, 
                batch_files.client_policy_id, 
                batch_files.event_type, .
                batch_files.created_by, 
                client_policy.insurer_id, 
                client_policy.tpa_id
            ')
            ->join('client_policy', 'batch_files.client_policy_id = client_policy.id')
            ->where('batch_files.id', $file_id)
            ->first();

        // dd($filedata);
    
        // Log after fetching data
        if ($filedata) {
            $this->myLogger->logme('error', 'File data fetched successfully: ' . json_encode($filedata));
    
            // Log before inserting data
            $this->myLogger->logme('error', 'storeEndorsementNumber --- Inserting endorsement data into endorsementModel');
    
            $this->endorsementModel->insert([
                'client_id' => $filedata['client_id'],
                'client_policy_id' => $filedata['client_policy_id'],
                'insurer_id' => $filedata['insurer_id'],
                'tpa_id' => $filedata['tpa_id'],
                'endorsement_no' => $endorsement_no,
                'file_id' => $enrollment_file_id,
                'endorsement_type' => $filedata['event_type'],
                'created_by' => $filedata['created_by'],
            ]);
    
            // Log after data insertion
            $this->myLogger->logme('error', 'storeEndorsementNumber --- Endorsement data inserted successfully for file_id: ' . $file_id);
        } else {
            // Log if no file data is found
            $this->myLogger->logme('error', "storeEndorsementNumber --- No file data found for file_id: $file_id");
        }
    
        // Log function exit
        $this->myLogger->logme('error',"storeEndorsementNumber --- Function execution completed for file_id: $file_id");
    }
    



}