<?php
ini_set('max_execution_time', 12000);
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);

$response_array = array();
$response_mssages = "";
$ohc_type = $_SESSION['current_ohcttype'];
begin();
$allowedFileType = [
    'application/vnd.ms-excel',
    'text/xls',
    'text/xlsx',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
];
$dbColumnNameList = array();
$checkup_type_id = $_REQUEST['checkup_type_id'];
error_log("checkup_type_id " . $checkup_type_id);

// 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'];
    move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

    $Reader = new SpreadsheetReader($targetPath);

    $sheetCount = count($Reader->sheets());
    $insert_counter = 0;
    $update_counter = 0;
    $fail_counter = 0;
    $new_emp_counter = 0;
    $parameter_insert_counter = 0;
    $parameter_update_counter = 0;

    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);

       //error_log("Fetched result Value " . $sql1);

        $Reader->ChangeSheet($i);
        $j = 0;
        $max_cols;
        $crntColIndex = 0;

        try {
            foreach ($Reader as $Row) {
                $crntColIndex = 0;
               //error_log("data1row:" . $Row);
               //error_log("data2row:" . $j);
                if ($j == 0) {

                    $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;
                }
               //error_log("row:" . $j);

                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;

                while ($crntColIndex < 16) {
                    $colVal = trim(mysqli_real_escape_string($conn, $Row[$crntColIndex]));

                   //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 (strtolower(trim($patient_category)) == 'employee') {
                            $patient_category = 1;
                        } else if (strtolower(trim($patient_category)) == 'contract-employee') {
                            $patient_category = 3;
                        } else if(strtolower(trim($patient_category)) == 'non-employee'){
                            $patient_category = 2;
                        }else{
                            $patient_category ='';
                        }
                        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;
                    } else if ($dbColumnNameList[$crntColIndex] == 'identity') {
                        $identity = $colVal;
                    }
                    $crntColIndex++;
                }
                $id = "";

                if (!empty($emp_code)) {
                    $id = getTableFieldValue('patient_master', 'id', 'emp_code', "'" . $emp_code . "'");
					error_log("processing row:".$j.", emp_code".$emp_code);
                   //error_log("when emp code is not 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 set  ";
                        $endquery = "";

                        $new_emp_counter++;

                       //error_log("patientid" . $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'] . "',identity='" . $identity . "',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';
                        } else {
                            $response_array['status'] = 'error';
                        }
                    }
                }

                if (!empty($aadhar_no) && empty($emp_code)) {
                    $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 set  ";
                        $endquery = "";
                        $new_emp_counter++;

                       //error_log("patientid" . $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'] . "',identity='" . $identity . "',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';
                        } else {
                            $response_array['status'] = 'error';
                        }
                    }
                }

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

                   //error_log("patientid " . $id);
                }

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

                   //error_log("patientid " . $id);
                }

               //error_log("ticket " . $ticket);

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

                $sql_medical_update = "select count(*) as count,ticket_no from checkup_form 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 {
                        $ticket = creatingCheckupTicketNo();
                    }
                }
               //error_log("record update count " . $count);

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

                if ($count > 0) {
                    $initquery = "update checkup_form set  ";
                    $endquery = " where emp_id = '" . $id . "'   ";
                    $update_counter++;
                } else {
                    $initquery = "insert into checkup_form set  ";
                    $endquery = "";
                    $insert_counter++;
                }

                $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 . "',department='" . $dept_id . "',designation='" . $designation_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));
                }

                $checkup_id = "";
                $get_checkup_id = "select checkup_id from checkup_form where emp_id='" . $id . "' and checkup_type_id='" . $checkup_type_id . "' and checkup_date='" . $medical_date . "' ";
                $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);
                    }
                }

                $count_key_value = 0;
                $sql_key_value = "select count(*) as count from checkup_form_key_value where checkup_form_id = '" . $checkup_id . "'";
                $result_key_value_count = mysqli_query($conn, $sql_key_value);

                if ($row_key_value = mysqli_fetch_array($result_key_value_count)) {
                    $count_key_value = $row_key_value['count'];
                }

               //error_log("checking for key value insert/update " . $sql_key_value);


                if ($count_key_value > 0) {
                    // $health_risk_array = array();
                    // $health_advice_array = array();
                    $hiddenHighlighter_array = array(' ');
                    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));

                            //        //error_log("starting range " . floatval($row['starting_range']));
                            //        //error_log("col val for range " . floatval($colVal));


                            //         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));

                            //        //error_log("ending range " . floatval($row['ending_range']));
                            //        //error_log("col val for range " . floatval($colVal));
                            //         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']);
                            //     }
                            // }
							if(isset($colVal1) && $colVal1!=""){
								$sql_checkup_form_key_value = "update checkup_form_key_value set checkup_form_value='" . addslashes($colVal1) . "' where checkup_form_key='" . $row['column_name'] . "' and checkup_form_id='" . $checkup_id . "'";
                           //error_log("for key value update " . $sql_checkup_form_key_value);
                            // $parameter_update_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_update_counter--;
                                // }
                               //error_log("error for key value query " . mysqli_error($conn));
                                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 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);
                    }*/
                } else {
                    // $health_risk_array = array();
                    // $health_advice_array = array();
                    $hiddenHighlighter_array = array(' ');
                    while ($row = mysqli_fetch_array($result1)) {
                       //error_log("entered while loop for checkup form key value");

                        $colVal = trim(mysqli_real_escape_string($conn, $Row[$crntColIndex]));

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

                       //error_log("input type ".$row['input_type'] );

                        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']);
                            //     }
                            // }
							if(isset($colVal1) && $colVal1!=""){
								$sql_checkup_form_key_value = "insert into checkup_form_key_value set checkup_form_id='" . $checkup_id . "', checkup_form_key='" . $row['column_name'] . "',checkup_form_value='" . addslashes($colVal1) . "';
								" . $endquery_key_value;
							   //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));
									rollback();
								}
							}
                        }else{
                           //error_log("mismatch name ".$row['column_name']);
                        }

                        $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 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);
                  //  }
                }
				commit();
            }
			commit();
        } 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> ";

$response_array['responseText'] = "<br>" . $response_mssages1 . $response_mssages;
echo json_encode($response_array);