688 lines
38 KiB
PHP
688 lines
38 KiB
PHP
<?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); |