<?php
include('includes/config/config.php');
include('includes/functions.php');
include('log_entry.php');
include('constants.php');
require_once('excel/vendor/php-excel-reader/excel_reader2.php');
require_once('excel/vendor/SpreadsheetReader.php');
header('Content-type: application/json');
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_WARNING);

// GENERATE TICKET ONLY ONC THEN INCREMENT
$generate_ticket = false;
$previous_ticket = "";
$ohc_type = $_SESSION['current_ohcttype'];
begin();
$allowedFileType = [
    'application/vnd.ms-excel',
    'text/xls',
    'text/xlsx',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
];

//ALL COLUMN NAME IN EXCEL
$dbColumnNameList = array();

// TEMPRORY
$column_name = array();

//TYPE FOR EXCEL FILE FROM DROPDOWN
$checkup_type_id = $_REQUEST['checkup_type_id'];
error_log("checkup_type_id " . $checkup_type_id);

//DELETE ANY REACORD IN TEMP TABLES
$del_status = "";
$tempTables = ["patient_master_temp", "checkup_form_temp", "checkup_form_key_value_temp"];
for ($z = 0; $z < count($tempTables); $z++) {

    $check_data =  "SELECT COUNT(*) AS data_exists FROM $tempTables[$z];";
    $check_data_row = mysqli_query($conn, $check_data);
    $check_data_res = $check_data_row->fetch_assoc();

    if ($check_data_res['data_exists'] > 0) {

        $queryDeleteData = "TRUNCATE TABLE $tempTables[$z]";
        $resultDeleteData = mysqli_query($conn, $queryDeleteData);

        // Check if the data deletion was successful
        if ($resultDeleteData) {
            $del_status = "Existed Data deleted";
        } else {
            $del_status = "Failed to delete existing Data";
            rollback();
            exit(mysqli_error($conn));
        }
    }
}
// selecting everything from checkup type master where id is this

$sqly = "select * from checkup_type where checkup_type_id=" . $checkup_type_id . "";
$resulty = mysqli_query($conn, $sqly);

error_log("checkup type query " . $sqly);
while ($rowy = mysqli_fetch_array($resulty)) {
    $checkup_form_section_ids = $rowy['checkup_form_section_ids'];
}
if (in_array($_FILES["file"]["type"], $allowedFileType)) {

    $targetPath = 'excel/uploads/' . $_FILES['file']['name']; //MWNTION WHILE FILE CONTENT TO LOAD
    move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

    $Reader = new SpreadsheetReader($targetPath);
    $sheetCount = count($Reader->sheets());

    for ($i = 0; $i < 1; $i++) {
        $sql1 = 'select less_advices,less_risks,more_advices,more_risks,starting_range,ending_range,input_type,section_id,column_order,checkup_parameter_id,parameter_name,column_name,parameter_value from (select less_advices,less_risks,more_advices,more_risks,input_type,starting_range,ending_range,checkup_form_section_id,column_order,checkup_parameter_id ,parameter_name,column_name,parameter_value from checkup_parameter WHERE enabled="Y" and checkup_form_section_id in (' . $checkup_form_section_ids . ')) as c inner JOIN checkup_form_section on checkup_form_section.section_id=c.checkup_form_section_id and Status="Active" order by checkup_form_section_id,column_order';
        $result1 = mysqli_query($conn, $sql1);

        $Reader->ChangeSheet($i); //READ THE FIREST SHEET IN EXCEL
        $j = 0; //current row
        $max_cols;
        $crntColIndex = 0;

        try {
            foreach ($Reader as $Row) { //GO THROUGH EACH ROW (start from 0 i.e header row) $ROW has col VALUES
                $crntColIndex = 0; // CURRENT COLUMN INDEX

                if ($j == 0) { //IF 0 GET ALL THE HEADER NAME IN dbColumnNameList ARRAY

                    $crntColIndex = 0;
                    $nondb_col = 0;

                    while (isset($Row[$crntColIndex]) && mysqli_real_escape_string($conn, $Row[$crntColIndex]) != null && trim($Row[$crntColIndex]) != '') {
                        $colVal = mysqli_real_escape_string($conn, $Row[$crntColIndex]);
                        //
                        if ($colVal != 'NA') {
                            $dbColumnNameList[$crntColIndex] = $Row[$crntColIndex];
                        } else {
                            // if NA for DB col found... then just record it with NA_Prefix to distinguish and discard later
                            $dbColumnNameList[$crntColIndex] = "NA_" + $nondb_col;
                            $nondb_col++;
                        }
                        $crntColIndex++;
                    } // end of while -- reading all columns for first row

                    $j++;
                    // error_log("row j:" . $j);
                    $max_cols = $crntColIndex;
                    // error_log("max col:" . $max_cols);
                    // error_log("db_fields_Array:" . $dbColumnNameList);
                    // print_r($dbColumnNameList);
                    continue;
                }

                if ($j == 2) {
                    $filter_count = 0;
                    while ($filter_count < $max_cols) {
                        $header_value = $Row[$filter_count];

                        if (strpos($header_value, '(') !== false) {
                            $header_value = substr($header_value, 0, strpos($header_value, '('));
                            error_log("CURRET HEADER RENOVE BRACKET  " . $header_value);
                        }

                        if (!empty($header_value)) {
                            array_push($column_name, $header_value);
                            error_log("CURRET HEADER VALUE " . $header_value);
                        } else {
                            array_push($column_name, $dbColumnNameList[$filter_count]);
                        }

                        $filter_count++;
                    }
                }


                if ($j < 3) {
                    // ignore for other header rows
                    $j++;
                    // error_log("row:" . $j);
                    continue;
                }
                // error_log("datarow:" . $j);

                if ($Row[0] == '') {
                    // if a blank row is encountered stop reading
                    break;
                }
                $max_cols = $crntColIndex;
                // error_log("max value " . $max_cols);
                $crntColIndex = 0;

                $new_patient_flag = false;
                while ($crntColIndex < 16) { //PATIENT MASTER DATA CLASSIFICATION
                    $colVal = trim(mysqli_real_escape_string($conn, $Row[$crntColIndex]));
                    // $colVal contains VALUE FOR CURRENT INDEX of column

                    error_log("colval " . $colVal);
                    error_log("colIndex " . $crntColIndex);

                    $dbColumnValueList[$crntColIndex] = $colVal;

                    if ($dbColumnNameList[$crntColIndex] == 'emp_code') {
                        $emp_code = $colVal;
                    } else if ($dbColumnNameList[$crntColIndex] == 'patient_name') {
                        $patient_name = $colVal;
                        if ($patient_name == "" && $patient_name == NULL) {
                            $response_mssages .= "<br>Patient Name Missing at row: " . +$j;
                            $fail_counter++;
                            $crntColIndex++;
                            continue;
                        }
                    } else if ($dbColumnNameList[$crntColIndex] == 'designation_id') {
                        $designation = $colVal;
                        error_log("designation " . $designation);
                        if ($designation == "" && $designation == NULL) {
                            $response_mssages .= "<br>Designation Name Missing at row: " . +$j;
                            $fail_counter++;
                            $crntColIndex++;
                            continue;
                        }
                    } else if ($dbColumnNameList[$crntColIndex] == 'dept_id') {
                        $department = $colVal;
                        error_log("department " . $department);
                        if ($department == NULL) {
                            $response_mssages .= "<br>Department Name Missing at row: " . +$j;
                            $fail_counter++;
                            $crntColIndex++;
                            continue;
                        }
                    } else if ($dbColumnNameList[$crntColIndex] == 'patient_cat_id') {
                        $patient_category = $colVal;
                        error_log("patient_cat_id " . $patient_category);
                        if (trim($patient_category) == 'Employee') {
                            $patient_category = 1;
                        } else if (trim($patient_category) == 'Contractor Employee') {
                            $patient_category = 3;
                        } else {
                            $patient_category = 2;
                        }
                        if ($patient_category == NULL) {
                            $response_mssages .= "<br>patient category Missing at row: " . +$j;
                            $fail_counter++;
                            $crntColIndex++;
                            continue;
                        }
                    } else if ($dbColumnNameList[$crntColIndex] == 'employer_contractor_id') {
                        $employer_contractor_name = $colVal;
                        error_log("employer_contractor name " . $employer_contractor_name);
                        if ($employer_contractor_name == NULL) {
                            $response_mssages .= "<br>Employer Contractor  Missing at row: " . +$j;
                            $fail_counter++;
                            $crntColIndex++;
                            continue;
                        }
                    } else if ($dbColumnNameList[$crntColIndex] == 'bu_id') {
                        $bu_name = $colVal;
                        error_log("business name " . $bu_name);
                        if ($bu_name == NULL) {
                            $response_mssages .= "<br>Business name Missing at row: " . +$j;
                            $fail_counter++;
                            $crntColIndex++;
                            continue;
                        }
                    } else if ($dbColumnNameList[$crntColIndex] == 'examination_date') {
                        $medical_date = $colVal;
                    } else if ($dbColumnNameList[$crntColIndex] == 'doj') {
                        $doj = $colVal;
                    } else if ($dbColumnNameList[$crntColIndex] == 'aadhar_no') {
                        $aadhar_no = $colVal;
                        if ($aadhar_no == "" && $aadhar_no == NULL) {
                            $response_mssages .= "<br>Aadhar No Missing at row: " . +$j;
                            $fail_counter++;
                            $crntColIndex++;
                            continue;
                        }
                    } else if ($dbColumnNameList[$crntColIndex] == 'dob') {
                        $dob = $colVal;
                    } else if ($dbColumnNameList[$crntColIndex] == 'father_name') {
                        $father_name = $colVal;
                    } else if ($dbColumnNameList[$crntColIndex] == 'age') {
                        $age = $colVal;
                        error_log("dob: " . $dob);
                        error_log("age: " . $age);
                        if ($dob == '') {
                            $dob = getCalculatedDOBFromAge($age);
                            error_log("calculated dob: " . $dob);
                        }
                    } else if ($dbColumnNameList[$crntColIndex] == 'gender') {
                        $gender = $colVal;
                    } else if ($dbColumnNameList[$crntColIndex] == 'blood_group') {
                        $blood_group = $colVal;

                        error_log("blooooooood " . $blood_group);
                        error_log("bloooooooodyyyyyy " . $dbColumnValueList[$crntColIndex]);
                    } else if ($dbColumnNameList[$crntColIndex] == 'primary_phone') {
                        $primary_phone = $colVal;
                    }
                    $crntColIndex++;
                }

                error_log("DB COLUMN VALUE" . print_r($dbColumnValueList, true));

                //PART WHERE THE ID/VALUES FOR VARIABLES ARE SET AND IF NEW VALUES IS ENCOUNTERED ITS INSERTED TO DB
                // THERE ARE TWO CONDITIONS OUT OF WHICH ONE GETS FULFILLED TP INSERT TO PATIRNT MASTER
                $id = "";
                if (!empty($emp_code)) { //IF EMP CODE IS PRESENT FOR ROW THEN ENTER
                    $id = getTableFieldValue('patient_master_temp', 'id', 'emp_code', "'" . $emp_code . "'");
                    // $id = getTableFieldValue('patient_master', 'id', 'emp_code', "'" . $emp_code . "'");
                    error_log("when emp code is not empty and patient data existing already " . $id);
                    // DECIDED WHETHER TO UPDATE OR INSERT PATIENT

                    //DEPARTMENT ID
                    $dept_id = getTableFieldValue('department', 'dept_id', 'dept_name', "'" . $department . "'");
                    error_log("when emp code is not empty and patient data existing already dept id " . $dept_id);

                    if ($dept_id == null or $dept_id == "") {
                        mysqli_query($conn, "insert into department set dept_name='" . $department . "'");

                        $dept_id = getTableFieldValue('department', 'dept_id', 'dept_name', "'" . $department . "'");
                        error_log("when emp code is not empty but dept is new " . $dept_id);
                    }

                    //DESIGNATION ID
                    $designation_id = getTableFieldValue('designation', 'designation_id', 'designation_name', "'" . $designation . "'");
                    error_log("when emp code is not empty and patient data existing already desg id " . $designation_id);

                    if ($designation_id == null or $designation_id == "") {
                        mysqli_query($conn, "insert into designation set designation_name='" . $designation . "'");

                        $designation_id = getTableFieldValue('designation', 'designation_id', 'designation_name', "'" . $designation . "'");

                        error_log("when emp code is not empty but dept is new " . $designation_id);
                    }

                    //EMPLOYER CONTRACTOR ID
                    $cont_id = getTableFieldValue('employer_contractor', 'id', 'employer_contractor_name', "'" . $employer_contractor_name . "'");
                    error_log("when emp code is not empty and patient data existing already cont id " . $cont_id);

                    if ($cont_id == null or $cont_id == "") {
                        mysqli_query($conn, "insert into employer_contractor set employer_contractor_name='" . $employer_contractor_name . "'");

                        $cont_id = getTableFieldValue('employer_contractor', 'id', 'employer_contractor_name', "'" . $employer_contractor_name . "'");
                        error_log("when emp code is not empty but contractor is new " . $cont_id);
                    }

                    //UNIT ID
                    $bu_id = getTableFieldValue('bussiness_unit', 'bu_id', 'bu_name', "'" . $bu_name . "'");
                    error_log("when emp code is not empty and patient data existing already bu id " . $bu_id);

                    if ($bu_id == null or $bu_id == "") {
                        mysqli_query($conn, "insert into bussiness_unit set bu_name='" . $bu_name . "'");

                        $bu_id = getTableFieldValue('bussiness_unit', 'bu_id', 'bu_name', "'" . $bu_name . "'");
                        error_log("when emp code is not empty but business unit is new " . $cont_id);
                    }

                    $initquery = "";
                    $endquery = "";
                    error_log("patientid" . $id);

                    if (isset($id)) {
                        // $initquery = "update patient_master set  ";
                        // $endquery = " where id = '" . $id . "'   ";
                    } else {
                        $initquery = "insert into patient_master_temp set  ";
                        $endquery = "";
                        $new_emp_counter++;

                        $dob = date('Y-m-d', strtotime($dob));
                        $doj = date('Y-m-d', strtotime($doj));

                        error_log("patientid Entry USING ONLY EMPID" . $id);
                        $query_patient = $initquery . " dob= '" . $dob . "',doj= '" . $doj . "',primary_phone ='" . $primary_phone . "',emp_code='" . $emp_code . "',bu_id='" . $bu_id . "',patient_cat_id='" . $patient_category . "',employer_contractor_id='" . $cont_id . "',patient_name='" . $patient_name . "',father_name='" . $father_name . "',blood_group='" . $blood_group . "',gender='" . $gender . "',dept_id='" . $dept_id . "',designation_id='" . $designation_id . "',ohc_type_id='" . $_SESSION['current_ohcttype'] . "',modified_by='" . $_SESSION['user_id'] . "',aadhar_no='" . $aadhar_no . "'" . $endquery;


                        error_log("patient master query " . $query_patient);

                        if (!$result = @mysqli_query($conn, $query_patient)) {
                            error_log("Exception:" . mysqli_error($conn));
                            error_log("Failed to Execute Patient Insert/Update Query::: " . $query_patient);
                            // $response_array['responseText']=mysqli_error($conn);
                            rollback();
                            exit(mysqli_error($conn));
                        } else { // SUCCESS FOR PATIENT MASTER
                            error_log("SUCCESS to Execute Patient Insert/Update Query::: " . $query_patient);
                        }

                        if ($result) {
                            $response_array['status'] = 'success';
                            $new_patient_flag = true;
                        } else {
                            $response_array['status'] = 'error';
                        }
                    }
                }

                // SECOND CONDITION TO INSERT TO PATIENT MASTER ON BASIS OF ADHAR (WHEN PATIENT ASLREADY EXISTS)
                if (!empty($aadhar_no) && empty($emp_code)) {
                    $id = getTableFieldValue('patient_master_temp', 'id', 'aadhar_no', "'" . $aadhar_no . "'");
                    // $id = getTableFieldValue('patient_master', 'id', 'aadhar_no', "'" . $aadhar_no . "'");
                    error_log("when emp code is empty and patient data existing already " . $id);

                    $dept_id = getTableFieldValue('department', 'dept_id', 'dept_name', "'" . $department . "'");
                    error_log("when emp code is not empty and patient data existing already dept id " . $dept_id);

                    if ($dept_id == null or $dept_id == "") {
                        mysqli_query($conn, "insert into department set dept_name='" . $department . "'");

                        $dept_id = getTableFieldValue('department', 'dept_id', 'dept_name', "'" . $department . "'");
                        error_log("when emp code is not empty but dept is new " . $dept_id);
                    }

                    $designation_id = getTableFieldValue('designation', 'designation_id', 'designation_name', "'" . $designation . "'");
                    error_log("when emp code is not empty and patient data existing already desg id " . $designation_id);

                    if ($designation_id == null or $designation_id == "") {
                        mysqli_query($conn, "insert into designation set designation_name='" . $designation . "'");

                        $designation_id = getTableFieldValue('designation', 'designation_id', 'designation_name', "'" . $designation . "'");

                        error_log("when emp code is not empty but dept is new " . $designation_id);
                    }

                    $cont_id = getTableFieldValue('employer_contractor', 'id', 'employer_contractor_name', "'" . $employer_contractor_name . "'");

                    error_log("when emp code is not empty and patient data existing already cont id " . $cont_id);

                    if ($cont_id == null or $cont_id == "") {
                        mysqli_query($conn, "insert into employer_contractor set employer_contractor_name='" . $employer_contractor_name . "'");

                        $cont_id = getTableFieldValue('employer_contractor', 'id', 'employer_contractor_name', "'" . $employer_contractor_name . "'");
                        error_log("when emp code is not empty but contractor is new " . $cont_id);
                    }

                    $bu_id = getTableFieldValue('bussiness_unit', 'bu_id', 'bu_name', "'" . $bu_name . "'");

                    error_log("when emp code is not empty and patient data existing already bu id " . $bu_id);

                    if ($bu_id == null or $bu_id == "") {
                        mysqli_query($conn, "insert into bussiness_unit set bu_name='" . $bu_name . "'");

                        $bu_id = getTableFieldValue('bussiness_unit', 'bu_id', 'bu_name', "'" . $bu_name . "'");
                        error_log("when emp code is not empty but business unit is new " . $cont_id);
                    }

                    $initquery = "";
                    $endquery = "";
                    error_log("patientid" . $id);

                    if (isset($id)) {
                        // $initquery = "update patient_master set  ";
                        // $endquery = " where id = '" . $id . "'   ";
                    } else {
                        $initquery = "insert into patient_master_temp set  ";
                        $endquery = "";
                        $new_emp_counter++;

                        $dob = date('Y-m-d', strtotime($dob));
                        $doj = date('Y-m-d', strtotime($doj));

                        error_log("patientid Entry USING ONLY ADHAR CAUSE EMP ID ENTRY" . $id);
                        // $query_patient = $initquery . " dob=STR_TO_DATE('" . $dob . "','%Y/%m/%d'),doj=STR_TO_DATE('" . $doj . "','%Y/%m/%d'),primary_phone ='" . $primary_phone . "',emp_code='" . $emp_code . "',bu_id='" . $bu_id . "',patient_cat_id='" . $patient_category . "',employer_contractor_id='" . $cont_id . "',patient_name='" . $patient_name . "',father_name='" . $father_name . "',blood_group='" . $blood_group . "',gender='" . $gender . "',dept_id='" . $dept_id . "',designation_id='" . $designation_id . "',ohc_type_id='" . $_SESSION['current_ohcttype'] . "',modified_by='" . $_SESSION['user_id'] . "',aadhar_no='" . $aadhar_no . "'" . $endquery;

                        $query_patient = $initquery . " dob= '" . $dob . "',doj= '" . $doj . "',primary_phone ='" . $primary_phone . "',emp_code='" . $emp_code . "',bu_id='" . $bu_id . "',patient_cat_id='" . $patient_category . "',employer_contractor_id='" . $cont_id . "',patient_name='" . $patient_name . "',father_name='" . $father_name . "',blood_group='" . $blood_group . "',gender='" . $gender . "',dept_id='" . $dept_id . "',designation_id='" . $designation_id . "',ohc_type_id='" . $_SESSION['current_ohcttype'] . "',modified_by='" . $_SESSION['user_id'] . "',aadhar_no='" . $aadhar_no . "'" . $endquery;

                        error_log("patient master query " . $query_patient);

                        if (!$result = @mysqli_query($conn, $query_patient)) {
                            error_log("Exception:" . mysqli_error($conn));
                            error_log("Failed to Execute Patient Insert/Update Query::: " . $query_patient);
                            // $response_array['responseText']=mysqli_error($conn);
                            rollback();
                            exit(mysqli_error($conn));
                        }

                        if ($result) {
                            $response_array['status'] = 'success';
                            $new_patient_flag = true;
                        } else {
                            $response_array['status'] = 'error';
                        }
                    }
                }

                // CAN AMKE USE OF THIS TO GET PATIENT ID NA DDISPLAY PATIENT DATA!
                $id = "";
                if (!empty($emp_code)) { //GET ID ON BASIS OF EMP CODE
                    $id = getTableFieldValue('patient_master_temp', 'id', 'emp_code', "'" . $emp_code . "'");
                    // $id = getTableFieldValue('patient_master', 'id', 'emp_code', "'" . $emp_code . "'");

                    error_log("patientid " . $id);
                    if ($new_patient_flag) {
                        array_push($patient_fetch_id, $id); //GET PLAYER DATA
                    }
                }

                if (!empty($aadhar_no) && empty($emp_code)) {
                    $id = getTableFieldValue('patient_master_temp', 'id', 'aadhar_no', "'" . $aadhar_no . "'");
                    // $id = getTableFieldValue('patient_master', 'id', 'aadhar_no', "'" . $aadhar_no . "'");

                    error_log("patientid " . $id);
                    if ($new_patient_flag) {
                        array_push($patient_fetch_id, $id); //GET PLAYER DATA
                    }
                }

                //--------------------->END PATIENT INSERT

                error_log("ticket " . $ticket);

                $time = strtotime($medical_date);
                $medical_date = date('Y-m-d h:i:s', $time); //EXAMINATION DATE FROM EXCEL
                error_log("new format " . $medical_date);

                // SELECT WHERE EMP ID = PATIENT ID(GIT FROM EMPID,ADHAR FROM PATIENT MASTER)
                $sql_medical_update = "select count(*) as count,ticket_no from checkup_form_temp where emp_id='" . $id . "' and checkup_type_id='" . $checkup_type_id . "' and checkup_date='" . $medical_date . "' ";

                $result_medical_count = mysqli_query($conn, $sql_medical_update);

                $count = 0;
                $ticket = "";

                if ($row_medical_count = mysqli_fetch_array($result_medical_count)) {
                    $count = $row_medical_count['count'];

                    if (!empty($row_medical_count['ticket_no'])) {
                        $ticket = $row_medical_count['ticket_no'];
                    } else {
                        // IF $ticket is empty and its first time then use function

                        if ($generate_ticket == false) {
                            $ticket = creatingCheckupTicketNo();
                            $previous_ticket = $ticket;
                            $generate_ticket = true;
                        } else {
                            // Extract the number from the text using regular expression
                            $pattern = "/(\d+)/";
                            preg_match($pattern, $previous_ticket, $matches);
                            $number = $matches[1];

                            // Increment the number
                            $incrementedNumber = $number + 1;

                            // Replace the original number with the incremented number in the text
                            $newTicket = preg_replace($pattern, $incrementedNumber, $previous_ticket, 1);
                            $ticket = $newTicket;
                            $previous_ticket = $ticket;
                        }
                    }
                }
                error_log("record update count " . $count);

                error_log("checking for update " . $sql_medical_update);

                // GET UPDATED OR INSERTED ID FROM HERE
                if ($count > 0) { //RECORD ALREADY PRESENT IN MAIN TABLE
                    // $initquery = "update checkup_form_temp set  ";
                    // $endquery = " where emp_id = '" . $id . "'   ";
                    // $initquery = "insert into checkup_form_temp set  ";
                    // $endquery = "";
                    $update_counter++;
                    array_push($update_checkup_form_id, $id);
                    continue;
                } else {
                    $initquery = "insert into checkup_form_temp set  ";
                    $endquery = "";
                    $insert_counter++;
                    array_push($insert_checkup_form_id, $id);
                }

                $time1 = strtotime($medical_date);
                $medical_date1 = date('d/m/Y h:i A', $time1);
                error_log("type of date in excel " . gettype($medical_date1));
                error_log("new format2 " . $medical_date1);

                $medical_query = $initquery . "checkup_date =STR_TO_DATE('" . $medical_date1 . "','%d/%m/%Y %h:%i %p'),checkup_type_id='" . $checkup_type_id . "', emp_id='" . $id . "' ,ohc_type_id='" . $ohc_type . "',ticket_no='" . $ticket . "' ,current_status='" . $__STATUS_MEDICAL_PENDING . "', checkup_section_ids='" . $checkup_form_section_ids . "'" . $endquery;

                error_log("main checkup form update/insert query " . $medical_query);

                if (!$result = @mysqli_query($conn, $medical_query)) {
                    if (substr($medical_query, 0, 6) === "insert") {
                        $insert_counter--;
                    } else {
                        $update_counter--;
                    }
                    error_log("Exception:" . mysqli_error($conn));
                    error_log("Failed to Execute the Medical Examination Query::: " . $medical_query);
                    rollback();
                    exit(mysqli_error($conn));
                }
                // END OF CHECKUP FORM (GET CHECKUP FORM DATA USING $id)

                $checkup_id = "";
                // if else use temp
                $get_checkup_id = "select checkup_id from checkup_form_temp where emp_id='" . $id . "' and checkup_type_id='" . $checkup_type_id . "' and checkup_date='" . $medical_date . "' ";

                error_log("GET CHECKUP ID FOR INSERT- " . $get_checkup_id);

                $result_checkup_id = mysqli_query($conn, $get_checkup_id);
                if ($row_checkup_id = mysqli_fetch_array($result_checkup_id)) {
                    if (!empty($row_checkup_id['checkup_id'])) {
                        $checkup_id = $row_checkup_id['checkup_id'];
                    } else {
                        $checkup_id = mysqli_insert_id($conn);
                    }
                }

                $health_risk_array = array();
                $health_advice_array = array();
                $hiddenHighlighter_array = array(' ');
                error_log("FOR USER " . $j);
                while ($row = mysqli_fetch_array($result1)) {
                    error_log("enetered while loop");
                    $colVal = trim(mysqli_real_escape_string($conn, $Row[$crntColIndex]));

                    error_log("currentcol from excel " . $dbColumnNameList[$crntColIndex]);
                    error_log("currentcol value from excel" . $colVal);
                    // error_log("parameter name from query " . $row['column_name']);

                    if (trim($dbColumnNameList[$crntColIndex]) == trim($row['column_name'])) {
                        if ($row['input_type'] == 'select') {
                            $colVal1 = getFieldFromTable('parameter_value_id', 'checkup_parameter_value', 'parameter_value_name', $colVal);
                        } elseif ($row['input_type'] == 'multiselect') {
                            $colVal1 = getCommaSeperatedValuesForInClause('select parameter_value_id from checkup_parameter_value', 'parameter_value_name', $colVal);
                            error_log("original value ids before  " . $colVal1);
                            $colVal1 = str_replace(', ', ',', $colVal1);
                            error_log("original value ids " . $colVal1);
                        } else {
                            $colVal1 = $colVal;
                        }

                        // if ($row['input_type'] == 'number') {
                        //     if (floatval($colVal) < floatval($row['starting_range']) && floatval($row['starting_range']) != 0 && floatval($colVal) != 0) {
                        //         error_log("inside advice risk condition");
                        //         $arr = explode(',', $row['less_advices']);
                        //         $arr3 = explode(',', $row['less_risks']);
                        //         error_log("partial advice val " . print_r($arr, true));
                        //         error_log("partial risk val " . print_r($arr3, true));
                        //         foreach ($arr as $val) {
                        //             array_push($health_advice_array, $val);
                        //         }
                        //         foreach ($arr3 as $val3) {
                        //             array_push($health_risk_array, $val3);
                        //         }
                        //         array_push($hiddenHighlighter_array, $row['column_name']);
                        //     } else if (floatval($colVal) > floatval($row['ending_range']) && floatval($row['ending_range']) != 0 && floatval($colVal) != 0) {
                        //         $arr2 = explode(',', $row['more_advices']);
                        //         $arr4 = explode(',', $row['more_risks']);
                        //         error_log("partial advice val " . print_r($arr2, true));
                        //         error_log("partial risk val " . print_r($arr4, true));
                        //         foreach ($arr2 as $val2) {
                        //             array_push($health_advice_array, $val2);
                        //         }
                        //         foreach ($arr4 as $val4) {
                        //             array_push($health_risk_array, $val4);
                        //         }
                        //         array_push($hiddenHighlighter_array, $row['column_name']);
                        //     }
                        // }

                        $sql_checkup_form_key_value = "insert into checkup_form_key_value_temp set checkup_form_id='" . $checkup_id . "', checkup_form_key='" . $row['column_name'] . "',checkup_form_value='" . addslashes($colVal1) . "';
                            " . $endquery_key_value;
                        $insert_key_checkup_form_id[$row['column_name']] = $checkup_id;
                        error_log("(INSERTING INTO KEY VALUE) " . "KEY-> " . $row['column_name'] . " , VALUE-> " . $colVal1);
                        // error_log("for key value insert/update " . $sql_checkup_form_key_value);
                        $parameter_insert_counter++;
                        if (!$result_check_form_key_value = mysqli_query($conn, $sql_checkup_form_key_value)) {
                            // if (substr($sql_checkup_form_key_value, 0, 6) === "update") {
                            //     $parameter_insert_counter--;
                            // }
                            error_log("error for key value query " . mysqli_error($conn));
                            $parameter_insert_counter--;
                            rollback();
                        }
                    }

                    $crntColIndex++;
                }
                mysqli_data_seek($result1, 0);
                error_log("risk array " . print_r($health_risk_array, true));
                error_log("advice array " . print_r($health_advice_array, true));
                $health_risk_id = getMultiValuedSelectData($health_risk_array);
                $health_advice_id = getMultiValuedSelectData($health_advice_array);
                $update_health_risk_advice_query = "update checkup_form_temp set health_risk_id='" . addslashes($health_risk_id) . "' , health_advice_id='" . addslashes($health_advice_id) . "' where checkup_id = '" . $checkup_id . "'";

                error_log("risk advice update query " . $update_health_risk_advice_query);

                if (!$result_update_health_risk_advice_query = mysqli_query($conn, $update_health_risk_advice_query)) {
                    error_log("risk advice update error " . mysqli_error($conn));
                    rollback();
                }
                error_log("highlight param:" . print_r($hiddenHighlighter_array, true));
                if (count($hiddenHighlighter_array) > 0) {
                    $highlighter_values = implode(',', $hiddenHighlighter_array);
                    saveHighlightedParams($checkup_id, $highlighter_values, "");
                }
                // }
            }
        } catch (Exception $e) {
            error_log('Message: ' . $e->getMessage());
        }
    }
}
commit();
$response_mssages1 = $insert_counter . " " . "Medical Examination records inserted<br> ";
$response_mssages1 .= $update_counter . "  " . "Medical Examination records updated<br>";
$response_mssages1 .= $fail_counter . " " . "Medical Examination records skipped<br> ";
$response_mssages1 .= $new_emp_counter . " " . "New Employee records Found<br> ";
//FOR RULE APPLY

error_log("ALL SECTION ID " . $checkup_form_section_ids);
error_log("CHECKUP TYPE ID " . $checkup_type_id);
$section_id_array = explode(",", $checkup_form_section_ids);
// SELECT ALL CHECKUP FORM DATA
$checkupfrom_table = "SELECT * FROM `checkup_form_temp`; ";
$getcheckupform_row = mysqli_query($conn, $checkupfrom_table);

if ($getcheckupform_row->num_rows > 0) {
    while ($getcheckupform_res = $getcheckupform_row->fetch_assoc()) {
        $current_patient_id = $getcheckupform_res['emp_id'];
        $current_checkup_id = $getcheckupform_res['checkup_id'];
        error_log("CURRENT PATIENT-> " . $current_patient_id . " CHECKUP ID-> " . $current_checkup_id);
        $current_patient_gender = getFieldFromTable('gender', 'patient_master_temp', 'id', $current_patient_id);
        $current_patient_dob = getFieldFromTable('dob', 'patient_master_temp', 'id', $current_patient_id);
        $today = date("Y-m-d");
        $current_patient_age = ($today - $current_patient_dob);
        error_log("PAtient DOB " . $current_patient_dob . " GENDER " . $current_patient_gender . " AGE " . $current_patient_age);

        $patient_risks = array();
        $patient_advices = array();

        // START FOR LOOP FOR EACH SECTION
        for ($z = 0; $z < sizeof($section_id_array); $z++) {
            error_log("CURRENT SECTION ID-> " . $section_id_array[$z]);
            $sql_section = "select * from checkup_form_section where section_id ='" . $section_id_array[$z] . "' having rule_ids!= ''";
            error_log("query for section " . $sql_section);
            $result_section = mysqli_query($conn, $sql_section);
            $data = array();
            $param_data = array();
            $data1 = array();
            while ($row_section = mysqli_fetch_assoc($result_section)) { //if the SECTION had rules assigned
                $rule_eq = getCommaSeperatedValuesToInClause($row_section['rule_ids']); //rule id on section

                $rule_sql = "select * from rule_equation where rule_eq_id in $rule_eq"; // array of rules
                error_log("rule sql " . $rule_sql);
                $result_rule = mysqli_query($conn, $rule_sql);

                while ($row_rule = mysqli_fetch_assoc($result_rule)) { //loop for every selected rule equations
                    error_log("INTO WHILE TO SELECT NRE RULE EQUATION");
                    $rule_age_start = $row_rule['rule_age_start'];
                    $rule_age_end = $row_rule['rule_age_end'];
                    $rule_gender = $row_rule['rule_gender'];
                    $result_id = $row_rule['result'];

                    error_log("it has the result field value of " . $result_id);
                    error_log("equation age gender data " . $rule_age_start . " " . $rule_age_end . " " . $rule_gender);

                    if (intval($rule_age_end) != 0) { //checkup if age is in range of start and end
                        if (intval($rule_age_start) > intval($age)) {
                            error_log("got here and continue for age mismatch" . $rule_age_start . " " . $age);
                            continue;
                        } else if (intval($rule_age_end) < intval($age)) {
                            error_log("got here and continue for age mismatch" . $rule_age_end . " " . $age);
                            continue;
                        }
                    }

                    if (($rule_gender != "" || $rule_gender != null) && $rule_gender != $pat_gender) {
                        error_log("got here and continue for gender mismatch");
                        continue;
                    } else { // start making equation for exaluation
                        $equation = "";

                        $rule_save = "select * from rule_save where equation_rule_id='" . $row_rule['rule_eq_id'] . "'";
                        error_log("rule save " . $rule_save);
                        $result_rule_save = mysqli_query($conn, $rule_save);

                        while ($row_rule_save = mysqli_fetch_assoc($result_rule_save)) { //making the rule equn and running

                            $key_name = getFieldFromTable('kay_param_name', 'key_health_reportable_parameter_master', 'key_param_id', $row_rule_save['checkup_parameter']); //parameter to check

                            $checkup_parameter = "";
                            $checkup_parameter_sql = "select column_name from checkup_parameter where key_health_map_name='" . $row_rule_save['checkup_parameter'] . "' and checkup_form_section_id='" . $row_section['section_id'] . "'"; //column name that is key in key_value table
                            error_log("to get parameter id name " . $checkup_parameter_sql);

                            $result_param = mysqli_query($conn, $checkup_parameter_sql);
                            while ($row_param = mysqli_fetch_assoc($result_param)) { //while for column name
                                $checkup_parameter = $row_param['column_name'];
                            } //END while for column name

                            error_log("checkup_parameter " . $checkup_parameter);

                            $condition = getTableFieldValue('rule_condition', 'condition_sy', 'rule_condition_id', $row_rule_save['condition_id']);
                            error_log("condition " . $condition);

                            $joiner = getTableFieldValue('rule_joiner', 'joiner_sy', 'rule_joiner_id', $row_rule_save['joiner_id']);
                            error_log("joiner " . $joiner);

                            // $user_entered_param_value = $_REQUEST[$checkup_parameter];
                            $user_entered_param_query = "SELECT `checkup_form_value` FROM `checkup_form_key_value_temp` WHERE `checkup_form_id` = '$current_checkup_id' AND `checkup_form_key` = '$checkup_parameter'; ";

                            error_log("VALUE FROM TEMP DATA USING FOR RULE-> " . $user_entered_param_query);
                            $user_entered_param_result = mysqli_query($conn, $user_entered_param_query);
                            $user_entered_param_row = mysqli_fetch_assoc($user_entered_param_result);

                            $user_entered_param_value = $user_entered_param_row['checkup_form_value'];
                            error_log("result_param: " . $result_id);
                            error_log("VALUE FROM TEMP value " . $user_entered_param_value);

                            if ($result_id === "0" || $result_id === 0) {
                                if ($user_entered_param_value == "") {
                                    // break;
                                } else {
                                    $equation = $equation . $joiner . $row_rule_save['op_br'] . $user_entered_param_value . $condition . $row_rule_save['rule_s_val'] . $row_rule_save['cl_br'];

                                    error_log("equation " . $equation);
                                }
                            } else {
                                if ($user_entered_param_value == "" && $row_rule_save['rule_s_val'] != '0') {
                                    $equation = $equation . $joiner . $row_rule_save['op_br'] . $condition . $row_rule_save['rule_s_val'] . $row_rule_save['cl_br'];

                                    error_log("got here equation is in else if " . $equation);
                                } else {
                                    if ($row_rule_save['rule_s_val'] == '0') {
                                        $equation = $equation . $joiner . $row_rule_save['op_br'] . $user_entered_param_value . $condition . $row_rule_save['cl_br'];

                                        error_log("got here equation is in else in if " . $equation);
                                    } else {
                                        $equation = $equation . $joiner . $row_rule_save['op_br'] . $user_entered_param_value . $condition . $row_rule_save['rule_s_val'] . $row_rule_save['cl_br'];
                                        error_log("got here equation is in else in else " . $equation);
                                    }

                                    error_log("equation " . $equation);
                                }
                            }
                        } // END OF making the rule equation and running

                        if ($equation != "" && ($result_id == "" || $result_id == 0 || $result_id == null)) {
                            eval("\$resultr = $equation;");

                            error_log("result " . $resultr);

                            if ($resultr == 1 || $resultr == true) {
                                // $abnormality = getFieldFromTable('abnormality_name', 'abnormality', 'abnormality_id', $row_rule['abnormality']);
                                // error_log("abnormalities " . $abnormality);

                                error_log("ABNORMALITY ID-> " . $row_rule['abnormality']);

                                array_push($data, $row_rule['abnormality']);

                                error_log("abnormalities " . print_r($data, true));
                            }
                        } else if ($equation != "" && ($result_id != "" && $result_id != 0 && $result_id != null)) {

                            error_log("we got the equation " . $equation);

                            $final_cal_val =  solveMathExpression($equation);
                            error_log("final_cal_val " . $final_cal_val);
                            $checkup_parameter_id = getFieldFromTable('column_name', 'checkup_parameter', 'checkup_parameter_id', $result_id);

                            $param_data[$checkup_parameter_id] = $final_cal_val;
                        }
                    } //end Of else in making equation for evaluation

                } // END while for every selected rule


                error_log("ABNORMALITIES DATA FOR CHECKUP ID " . $current_checkup_id . " -> " . print_r($data, true));
                error_log("CALCULATED DATA FOR CHECKUP ID " . $current_checkup_id . " -> " . print_r($param_data, true));
                if (!empty($param_data)) {


                    foreach ($param_data as $column => $value) {
                        error_log("VALUE OF BMI IN FOR -> " . $value);
                        // $column = trim($column);
                        // $escapedValue = mysqli_real_escape_string($connection, $value);
                        // $updateValues[] = "$column = '$escapedValue'";
                        $checkup_key_value_query = "UPDATE `checkup_form_key_value_temp` SET `checkup_form_value` = '$value' WHERE `checkup_form_id` = '$current_checkup_id' AND `checkup_form_key` = '$column';";

                        error_log("UPDATEe KEY VALUE TABLE-> " . $checkup_key_value_query);
                        if (!$result_update_key_val = mysqli_query($conn, $checkup_key_value_query)) {
                            error_log("KEY VALUE update error " . mysqli_error($conn));
                            rollback();
                        } else {
                            error_log("KEY VALUE update SUCCESS ");
                        }
                    }
                }

                if (!empty($data)) {
                    $abnormalityString = implode(',', $data);

                    $checkup_abnormality = "UPDATE `checkup_form_temp` SET `abnormality_ids`='$abnormalityString' WHERE `checkup_id`='$current_checkup_id';";
                    error_log("UPDATEe CHECKUP FORM TABLE-> " . $checkup_abnormality);
                    if (!$result_update_form = mysqli_query($conn, $checkup_abnormality)) {
                        error_log("CHECKUP FORM update error " . mysqli_error($conn));
                        rollback();
                    } else {
                        error_log("CHECKUP FORM update SUCCESS ");
                    }
                } else {
                    error_log("ABNORMAL STRING FOUND EMPTY");
                }
            } // END of While if the SECTION had rules assigned
            error_log("CHECK SECTION ID-> " . $section_id_array[$z] . " , " . $current_checkup_id);

            // INDIVIDUAL COMPONENT

            $section_parameter = "SELECT `column_name`, `rule_ids` FROM `checkup_parameter` WHERE `checkup_form_section_id` = $section_id_array[$z];";
            $result_section_parameter = mysqli_query($conn, $section_parameter);
            while ($row_section_parameter = mysqli_fetch_array($result_section_parameter)) { //GET COL NAME & RULE FOR EACH SECTION
                error_log("PARAMETER FOR SECTION " . $section_id_array[$z] . " -> " . $row_section_parameter['column_name']);
                if (!empty($row_section_parameter['rule_ids'])) { //check if theres rule for each parameter
                    error_log("RULE FOUND FOR PARAMETER-> " . $row_section_parameter['column_name']);
                    $key = $row_section_parameter['column_name'];
                    $rules = explode(",", $row_section_parameter['rule_ids']);

                    foreach ($rules as $id) { //loop for each rile on parameter
                        $val = "";
                        $get_val = "SELECT `checkup_form_value` FROM `checkup_form_key_value_temp` WHERE `checkup_form_id`='$current_checkup_id' AND `checkup_form_key`='$row_section_parameter[column_name]';";
                        error_log("GET COLUMN VALUE-> " . $get_val);
                        $get_val_res = mysqli_query($conn, $get_val);
                        if ($get_val_res->num_rows > 0) {
                            $get_val_row = mysqli_fetch_array($get_val_res);
                            $val = $get_val_row['checkup_form_value'];
                            error_log("VALUE FOUND TO BE-> " . $val);
                        }


                        $query = "select * from rule_equation where rule_eq_id='" . $id . "'";

                        error_log("rule equation getting query " . $query);

                        $result = mysqli_query($conn, $query);

                        $row = mysqli_fetch_assoc($result);

                        $rule_age_start = $row['rule_age_start'];
                        $rule_age_end = $row['rule_age_end'];
                        $rule_gender = $row['rule_gender'];

                        error_log("equation age gender data " . $rule_age_start . " " . $rule_age_end . " " . $rule_gender);

                        if (intval($rule_age_end) != 0) {
                            if (intval($rule_age_start) > intval($age)) {
                                error_log("got here and continue for age mismatch" . $rule_age_start . " " . $age);
                                continue;
                            } else if (intval($rule_age_end) < intval($age)) {
                                error_log("got here and continue for age mismatch" . $rule_age_end . " " . $age);
                                continue;
                            }
                        }
                        if (($rule_gender != "" || $rule_gender != null) && strtolower($rule_gender) != strtolower($pat_gender)) {
                            error_log("got here and continue for gender mismatch");
                            continue;
                        }

                        $equation = $row['rule_equation'];

                        error_log("before equation " . $equation);

                        if ($val != '' && $val != null) {
                            $equation = str_replace($key, $val, $equation);
                        }

                        error_log("after placing values in  equation " . $equation);

                        $ans = solveMathExpression($equation);

                        error_log("ans is " . $ans);

                        if ($ans == 1) {
                            error_log("got inside the if");
                            $data1 = $row;
                            error_log("DATA1 -> " . print_r($data1, true));

                            if (!empty($data1['color'])) {
                                saveHighlightedParams($current_checkup_id, $data1['color'], '');
                            }
                            // $temp = $data1['risks'];
                            // $temp1 = $data1['advices'];
                            $patient_risks[] = $data1['risks'];
                            $patient_advices[] = $data1['advices'];
                            // $data['risks'] = getCommaSeperatedValuesForInClause("select health_risk_name from health_risk", 'health_risk_id', $row['risks']);
                            // $data['advices'] = getCommaSeperatedValuesForInClause("select health_advice_name from health_advice", 'health_advice_id', $row['advices']);
                        }
                    } //END loop for each rile on parameter
                } //END if to check if theres rule for each parameter
            } // END of while for each parameter in section

        } // END OF LOOP FOR EACH SECTION
        // error_log("Advices FOR ID " . $current_checkup_id . " -> " . print_r($patient_advices));
    } //END OF WHILE LOOP FOR CHECKKUP FORM QUERY
    error_log("RISKS FOR ID " . $current_checkup_id . " -> " . print_r($patient_risks, true));
    error_log("Advices FOR ID " . $current_checkup_id . " -> " . print_r($patient_advices, true));
    $result_risks = implode(', ', $patient_risks);
    $result_advices = implode(', ', $patient_advices);

    $checkup_form_temp_update = "UPDATE `checkup_form_temp` SET `health_risk_id`='$result_risks',`health_advice_id`='$result_advices' WHERE `checkup_id` = '$current_checkup_id';";
    error_log("AD TO CHECKUP FORM TEMP " . $checkup_form_temp_update);

    if (!$result_checkup_update = @mysqli_query($conn, $checkup_form_temp_update)) {

        error_log("Exception:" . mysqli_error($conn));
        error_log("Failed to Execute the Medical Examination Query::: " . $checkup_form_temp_update);
        rollback();
        exit(mysqli_error($conn));
    } else {
        error_log("ADDED risks and advices to checkup form");
    }
}

function solveMathExpression($expression)
{
    // Define a regular expression pattern to match a valid math expression
    // $pattern = "/^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?(\s*[-+*\/]\s*[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?)*$/";

    // Check if the math expression matches the pattern
    // if (preg_match($pattern, $expression)) {
    try {
        return round(eval("return ($expression);"), 2);
    } catch (ParseError $e) {
        error_log("getting exception in this " . $e->getMessage());
    }
    // } else {
    // error_log("equation is not valid");
    // }
}

//ACCESS $checkup_form_section_ids for section id and loop 


// for ($z=0; $z < count($section_id_array); $z++) { 
//     $get_section_rule = "select rule_ids from checkup_form_section where section_id='" . $section_id_array[$z] . "' ";

//     error_log("GET SECTION ID RULE- " . $get_section_rule);

//     $result_section_rule = mysqli_query($conn, $get_section_rule);
//     $row_section_rule = mysqli_fetch_array($result_section_rule);

//     if (!empty($row_section_rule['rule_ids'])) {
//         error_log("RULES ON SECTION " . $section_id_array[$z] . " -> " . $row_section_rule['rule_ids']);
//         //APLLY SECTION RULES THEN GO TO EACH PARAMETER

//         // $section_parameter = "SELECT `column_name` FROM `checkup_parameter` WHERE `checkup_form_section_id` = $section_id_array[$z];";
//         // $result_section_parameter = mysqli_query($conn, $section_parameter);
//         // while ($row_section_parameter = mysqli_fetch_array($result_section_parameter)) {
//         //      error_log("PARAMETER FOR SECTION " . $section_id_array[$z] . " -> " . $row_section_parameter['column_name'] );
//         //  }
//     }else{
//         error_log("NO RULES ON SECTION " . $section_id_array[$z]);
//         //GO TO EACH PARAMETER
//         // $section_parameter = getFieldFromTable('column_name', 'checkup_parameter', 'checkup_form_section_id', $section_id_array[$z]);
//         $section_parameter = "SELECT `column_name` FROM `checkup_parameter` WHERE `checkup_form_section_id` = $section_id_array[$z];";
//         $result_section_parameter = mysqli_query($conn, $section_parameter);
//         while ($row_section_parameter = mysqli_fetch_array($result_section_parameter)) {
//              error_log("PARAMETER FOR SECTION " . $section_id_array[$z] . " -> " . $row_section_parameter['column_name'] );
//         }

//     }

// }
//ONLY EXCEL SPECIFIC DATA

// $checkupfrom_table = "SELECT * FROM `checkup_form_temp`; ";
// $getcheckupform_row = mysqli_query($conn, $checkupfrom_table);

// $final_res = [];
// // $display_table_header = [];
// if ($getcheckupform_row->num_rows > 0) {

//     while($getcheckupform_res = $getcheckupform_row->fetch_assoc()){

//         $temp_array = array();
//         $patient_id = $emp_code1 = $getcheckupform_res["emp_id"]; // Only differet id
//         $checkup_form_id = $getcheckupform_res["checkup_id"];

//         $key_id_string = $patient_id . "," . $checkup_form_id;

//         //GET PATIENT DATA
//         $getpatient_table = "SELECT * FROM `patient_master_temp` WHERE `id` = '$patient_id'; ";
//         $getpatient_row = mysqli_query($conn, $getpatient_table);
//         $getpatient_res = $getpatient_row->fetch_assoc(); //ALL PATIENT DATA

//         error_log("EMPLOYEE CODE IN WHILE-> " . $emp_code1 . "CHECKUP ID " . $checkup_form_id);

//         for ($m=0; $m < count($dbColumnNameList) ; $m++) { 
//             if ($m == 0) {
//                 // GET MEDICAL DATE TO GET ID WHERE ONLY DATE IS DIFFERENT
//                 if ($dbColumnNameList[$m] == 'examination_date') {
//                     $medical_date_new = getFieldFromTable('checkup_date', 'checkup_form_temp', 'checkup_id', $checkup_form_id);
//                     error_log("MEDICAL DATE " . $medical_date_new);
//                     array_push($temp_array ,$medical_date_new);
//                 }
//             }else if ($m > 0 && $m < 16) { //All patient Data
//                 $actual_value = "";


//                     // CONDITIONS TO CLASSIFY THE DATA
//                     if($dbColumnNameList[$m] == 'designation_id'){
//                         $actual_value = getFieldFromTable('designation_name', 'designation', 'designation_id', $getpatient_res[$dbColumnNameList[$m]]);
//                         array_push($temp_array ,$actual_value);

//                     }else if($dbColumnNameList[$m] == 'dept_id'){
//                         $actual_value = getFieldFromTable('dept_name', 'department', 'dept_id', $getpatient_res[$dbColumnNameList[$m]]);
//                         array_push($temp_array ,$actual_value);

//                     }else if($dbColumnNameList[$m] == 'patient_cat_id'){
//                         $actual_value = getFieldFromTable('patient_cat_name', 'patient_category', 'patient_cat_id', $getpatient_res[$dbColumnNameList[$m]]);
//                         array_push($temp_array ,$actual_value);

//                     }else if($dbColumnNameList[$m] == 'employer_contractor_id'){
//                         $actual_value = getFieldFromTable('employer_contractor_name', 'employer_contractor', 'id', $getpatient_res[$dbColumnNameList[$m]]);
//                         array_push($temp_array ,$actual_value);

//                     }else if($dbColumnNameList[$m] == 'bu_id'){
//                         $actual_value = getFieldFromTable('bu_name', 'bussiness_unit', 'bu_id', $getpatient_res[$dbColumnNameList[$m]]);
//                         array_push($temp_array ,$actual_value);

//                     }else if($dbColumnNameList[$m] == 'age'){
//                         // error_log("GET AGE VALUE " . $colVal1);
//                         // if (!empty($colVal1)) {
//                         //     $actual_value = $colVal1;
//                         // }else{
//                             $db_dob = getFieldFromTable('dob', 'patient_master_temp', 'id', $patient_id);
//                             $age = date_diff(date_create($db_dob), date_create('today'))->y;
//                             $actual_value = $age;
//                         // }

//                         array_push($temp_array ,$actual_value);

//                     }else{
//                         array_push($temp_array ,$getpatient_res[$dbColumnNameList[$m]]);
//                         error_log("Entry For Column:- " . $m);
//                     }
//                     //END CONDITIONS TO CLASSIFY THE DATA

//                 }else{
//                     error_log("INTO THE ELSE at " . $m);

//                     $search_col_key = trim($dbColumnNameList[$m]);
//                     $key_val_query = "SELECT `checkup_form_value` FROM `checkup_form_key_value_temp` WHERE `checkup_form_key` = '$search_col_key' AND `checkup_form_id` = '$checkup_form_id';";
//                     error_log("KEY QUERY-> " . $key_val_query );
//                     $test_key_row = mysqli_query($conn, $key_val_query);

//                     if ($test_key_row->num_rows > 0) {
//                         $test_key_res = $test_key_row->fetch_assoc();

//                         // BASED ON INPUT TYPE FETCH VALUE
//                         $get_input_type = "SELECT `input_type`,`parameter_name` FROM `checkup_parameter` WHERE `column_name`='$search_col_key';";
//                         error_log("QUERY FOR INPUT YPE AND COL CHECK-> " . $get_input_type);
//                         $input_type_row = mysqli_query($conn, $get_input_type);
//                         $input_type_res = $input_type_row->fetch_assoc();  
//                             if ($input_type_row->num_rows > 0) { //COLUMN EXISTS IN CHECKUP PARAMETER
//                                 error_log("FOUND COLUMN AND INPUT TYPE FOR-> " . $m);

//                                 if ($input_type_res['input_type'] == 'select') {
//                                     error_log("TYPE FOR " . $search_col_key . "IS SELECT");
//                                     $actual_value = getFieldFromTable('parameter_value_name', 'checkup_parameter_value', 'parameter_value_id',  $test_key_res['checkup_form_value']);
//                                     array_push($temp_array, $actual_value);

//                                 }else if ($input_type_res['input_type'] == 'multiselect') {
//                                     error_log("TYPE FOR " . $search_col_key . "IS MULTISELECT VALUES " . $test_key_res['checkup_form_value']);
//                                     $actual_value = getCommaSeperatedValuesForInClause('select parameter_value_name from checkup_parameter_value', 'parameter_value_id', $test_key_res['checkup_form_value']);
//                                     array_push($temp_array, $actual_value);

//                                 }else if ($input_type_res['input_type'] == 'number') {
//                                     error_log("TYPE FOR " . $search_col_key . "IS NUMBER");
//                                     $actual_value = $test_key_res['checkup_form_value'];
//                                     array_push($temp_array, $actual_value);

//                                 }else{
//                                     $actual_value = $test_key_res['checkup_form_value'];
//                                     array_push($temp_array, $actual_value);
//                                 }
//                             }else{
//                                 // error_log("NOT FOUND COLUMN AND INPUT TYPE FOR-> " . $search_col_key);
//                                 array_push($temp_array, $test_key_res['checkup_form_value']);
//                                 error_log("Entry For Column:- " . $m);
//                             }                      
//                         // error_log($dbColumnNameList[$m] . " ITS A VALUE NOT COLUMN");
//                     }else{
//                         error_log($dbColumnNameList[$m] . " NOTHING TO BE FOUND");
//                         array_push($temp_array ," ");
//                         error_log("NO ENRY FOUND FOR THE ABOVE VALUE IN KEY_VALUE TABLE " . $m);
//                     }
//                 }
//         }

//         // array_push($final_res, $temp_array);
//         $final_res[$key_id_string] = $temp_array;

//     }
// }
// ------------------------------------

//     $emp_code1 = ;
//     $patient_id = "";
//     $checkup_form_id = "";

//         $temp_array = array();
//         // LOOP THROUGH COLUMNS AND GET ITS VALUE
//         error_log("GET DATA FOR ROW " . $k);
//         $get_medical_date = "";
//         for ($m=0; $m < count($dbColumnNameList) ; $m++) { 
//             error_log("EXCEL COLUMN NO:- " . $m);
//             $colVal1 = trim(mysqli_real_escape_string($conn, $Row1[$m]));
//                 // $colVal contains VALUE FOR CURRENT INDEX of column
//             error_log($k . " CURRETN COL VALUE -> " . $colVal1);
//                 $dbColumnValueList[$crntColIndex] = $colVal;

//                 if ($dbColumnNameList[$m] == 'emp_code') {
//                     $emp_code1 = $colVal1;

//                     error_log("EMPLOYEE CODE - " . $emp_code1);

//                     $get_patient_id = "SELECT `id` FROM `patient_master_temp` WHERE `emp_code`='$emp_code1';";
//                     $patient_id_row = mysqli_query($conn, $get_patient_id);
//                     $patient_id_res = $patient_id_row->fetch_assoc();
//                     $patient_id = $patient_id_res['id'];

//                     $get_form_id = "SELECT `checkup_id` FROM `checkup_form_temp` WHERE `emp_id`='$patient_id' AND `checkup_date` = '$get_medical_date';";
//                     $form_id_row = mysqli_query($conn, $get_form_id);
//                     $form_id_res = $form_id_row->fetch_assoc();
//                     $checkup_form_id = $form_id_res['checkup_id'];
//                 }
//                 // error_log("Current Checkup Form ID-> " . $checkup_form_id );
//                 // error_log("Current Patient Id-> " . $patient_id ); 
//                 // error_log("Current Employee Code-> " . $emp_code1 );

//             // $index = array_search($dbColumnNameList[$m], $patientHead);
//             // $index1 = array_search($dbColumnNameList[$m], $formHead);

//             //get id prom patient master and use it in checkup form query using emp code
//             // is column present
//             // if ($index !== false) {
//             if ($m == 0) {
//                 // // GET MEDICAL DATE TO GET ID WHERE ONLY DATE IS DIFFERENT
//                 if ($dbColumnNameList[$m] == 'examination_date') {
//                     $medical_date_new = $colVal1;
//                     $get_time = strtotime($medical_date_new);
//                     $get_medical_date = date('Y-m-d h:i:s', $get_time); //EXAMINATION DATE FROM EXCEL
//                     array_push($temp_array ,$get_medical_date);
//                 }
//             }
//             else if ($m > 0 && $m < 16) {
//                 $actual_value = "";

//                 if ($dbColumnNameList[$m] != 'age') {
//                     $patient_data = "SELECT $dbColumnNameList[$m] FROM `patient_master_temp` WHERE `id`='$patient_id';";
//                 }
//                 // error_log($dbColumnNameList[$m] . " Column exist in PATIENT TABLE");

//                 error_log($patient_data);
//                 $patient_data_row = mysqli_query($conn, $patient_data);
//                 $patient_data_res = $patient_data_row->fetch_assoc();

//                 // CONDITIONS TO CLASSIFY THE DATA
//                 if($dbColumnNameList[$m] == 'designation_id'){
//                     $actual_value = getFieldFromTable('designation_name', 'designation', 'designation_id', $patient_data_res[$dbColumnNameList[$m]]);
//                     array_push($temp_array ,$actual_value);
//                     error_log("Entry For Column:- " . $m);
//                 }else if($dbColumnNameList[$m] == 'dept_id'){
//                     $actual_value = getFieldFromTable('dept_name', 'department', 'dept_id', $patient_data_res[$dbColumnNameList[$m]]);
//                     array_push($temp_array ,$actual_value);
//                     error_log("Entry For Column:- " . $m);
//                 }else if($dbColumnNameList[$m] == 'patient_cat_id'){
//                     $actual_value = getFieldFromTable('patient_cat_name', 'patient_category', 'patient_cat_id', $patient_data_res[$dbColumnNameList[$m]]);
//                     array_push($temp_array ,$actual_value);
//                     error_log("Entry For Column:- " . $m);
//                 }else if($dbColumnNameList[$m] == 'employer_contractor_id'){
//                     $actual_value = getFieldFromTable('employer_contractor_name', 'employer_contractor', 'id', $patient_data_res[$dbColumnNameList[$m]]);
//                     array_push($temp_array ,$actual_value);
//                     error_log("Entry For Column:- " . $m);
//                 }else if($dbColumnNameList[$m] == 'bu_id'){
//                     $actual_value = getFieldFromTable('bu_name', 'bussiness_unit', 'bu_id', $patient_data_res[$dbColumnNameList[$m]]);
//                     array_push($temp_array ,$actual_value);
//                     error_log("Entry For Column:- " . $m);
//                 }else if($dbColumnNameList[$m] == 'age'){
//                     error_log("GET AGE VALUE " . $colVal1);
//                     if (!empty($colVal1)) {
//                         $actual_value = $colVal1;
//                     }else{
//                         $db_dob = getFieldFromTable('dob', 'patient_master_temp', 'id', $patient_id);
//                         $age = date_diff(date_create($db_dob), date_create('today'))->y;
//                         $actual_value = $age;
//                     }
//                     // $coldob = trim(mysqli_real_escape_string($conn, $Row1['dob']));
//                     // error_log("CAN I GET DOB VALUE ? " . $coldob);
//                     // $actual_value = "CALC AGE";
//                     array_push($temp_array ,$actual_value);
//                     error_log("Entry For Column:- " . $m);
//                 }else{
//                     array_push($temp_array ,$patient_data_res[$dbColumnNameList[$m]]);
//                     error_log("Entry For Column:- " . $m);
//                 }
//                 //END CONDITIONS TO CLASSIFY THE DATA

//             }
//             // else if($index1 !== false){
//             //     error_log($dbColumnNameList[$m] . " Column exist in CHECKUP FORM TABLE");

//             //     array_push($temp_array, "CHECKUP FORM COLUMN");

//             // }
//             else{
//                 error_log("INTO THE ELSE at " . $m);
//                 // get checkup id where empid = empid then 
//                 // select using checkup form id and key
//                 // select input type from checkup parameter and get values based on input type
//                 $search_col_key = trim($dbColumnNameList[$m]);
//                 $key_val_query = "SELECT `checkup_form_value` FROM `checkup_form_key_value_temp` WHERE `checkup_form_key` = '$search_col_key' AND `checkup_form_id` = '$checkup_form_id';";
//                 error_log("KEY QUERY-> " . $key_val_query );
//                 $test_key_row = mysqli_query($conn, $key_val_query);

//                 if ($test_key_row->num_rows > 0) {
//                     $test_key_res = $test_key_row->fetch_assoc();

//                     // BASED ON INPUT TYPE FETCH VALUE
//                     $get_input_type = "SELECT `input_type`,`parameter_name` FROM `checkup_parameter` WHERE `column_name`='$search_col_key';";
//                     error_log("QUERY FOR INPUT YPE AND COL CHECK-> " . $get_input_type);
//                     $input_type_row = mysqli_query($conn, $get_input_type);
//                     $input_type_res = $input_type_row->fetch_assoc();  
//                         if ($input_type_row->num_rows > 0) { //COLUMN EXISTS IN CHECKUP PARAMETER
//                             error_log("FOUND COLUMN AND INPUT TYPE FOR-> " . $m);

//                             if ($input_type_res['input_type'] == 'select') {
//                                 error_log("TYPE FOR " . $search_col_key . "IS SELECT");
//                                 $actual_value = getFieldFromTable('parameter_value_name', 'checkup_parameter_value', 'parameter_value_id',  $test_key_res['checkup_form_value']);
//                                 array_push($temp_array, $actual_value);
//                                 error_log("Entry For Column:- " . $m);
//                             }else if ($input_type_res['input_type'] == 'multiselect') {
//                                 error_log("TYPE FOR " . $search_col_key . "IS MULTISELECT VALUES " . $test_key_res['checkup_form_value']);
//                                 $actual_value = getCommaSeperatedValuesForInClause('select parameter_value_name from checkup_parameter_value', 'parameter_value_id', $test_key_res['checkup_form_value']);
//                                 array_push($temp_array, $actual_value);
//                                 error_log("Entry For Column:- " . $m);
//                             }else if ($input_type_res['input_type'] == 'number') {
//                                 error_log("TYPE FOR " . $search_col_key . "IS NUMBER");
//                                 $actual_value = $test_key_res['checkup_form_value'];
//                                 array_push($temp_array, $actual_value);
//                                 error_log("Entry For Column:- " . $m);
//                             }else{
//                                 $actual_value = $test_key_res['checkup_form_value'];
//                                 array_push($temp_array, $actual_value);
//                             }
//                         }else{
//                             // error_log("NOT FOUND COLUMN AND INPUT TYPE FOR-> " . $search_col_key);
//                             array_push($temp_array, $test_key_res['checkup_form_value']);
//                             error_log("Entry For Column:- " . $m);
//                         }                      
//                     // error_log($dbColumnNameList[$m] . " ITS A VALUE NOT COLUMN");
//                 }else{
//                     error_log($dbColumnNameList[$m] . " NOTHING TO BE FOUND");
//                     array_push($temp_array ," ");
//                     error_log("Entry For Column:- " . $m);
//                 }
//             }
//         }

//         error_log($emp_code1 . " TEMP ARRAY-> " . print_r($temp_array, true));
//         array_push($final_res, $temp_array);
//     }
//     error_log(" FINAL ARRAY-> " . print_r($final_res, true));
// }
// }

// $response_array['responseText'] = "<br>" . $response_mssages1 . $response_mssages;
$response_array = array(
    // "patient_header" => $patient_fetch_header,
    // "patient_data" => $patient_fetch_data,
    // "form_update_header" => $form_fetch_header,
    // "form_update_data" => $form_fetch_data,
    // "form_insert_header" => $form_insert_header,
    // "form_insert_data" => $form_insert_data,
    "responseText" => $response_mssages1 . $response_mssages,
    // "new_col_list" => $column_name,
    "excel_column_name" => $column_name,
    "final_result" => $final_res
);
echo json_encode($response_array);


//     } //END FOR  for ($i = 0; $i < 1; $i++)

// } // END OF if (in_array($_FILES["file"]["type"], $allowedFileType))