818 lines
35 KiB
PHP
818 lines
35 KiB
PHP
<?php
|
|
include('includes/config/config.php');
|
|
include('includes/functions.php');
|
|
include('log_entry.php');
|
|
// $conn = mysqli_connect("localhost","root","","phpsamples");
|
|
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);
|
|
|
|
$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();
|
|
|
|
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;
|
|
|
|
for ($i = 0; $i < 1; $i++) {
|
|
|
|
$Reader->ChangeSheet($i);
|
|
$j = 0;
|
|
$max_cols = 0;
|
|
$crntColIndex = 0;
|
|
foreach ($Reader as $Row) {
|
|
$crntColIndex = 0;
|
|
// read row first to read table fields
|
|
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++;
|
|
$max_cols = $crntColIndex;
|
|
error_log("db_fields_Array:" . $dbColumnNameList);
|
|
// print_r($dbColumnNameList);
|
|
continue;
|
|
} // end reading first row db fields
|
|
|
|
if ($j < 3) {
|
|
// ignore for other header rows
|
|
$j++;
|
|
continue;
|
|
}
|
|
if ($Row[0] == '') {
|
|
// if a blank row is encountered stop reading
|
|
break;
|
|
}
|
|
// read data rows - start
|
|
|
|
$emp_code = "";
|
|
$employee_name = "";
|
|
$designation = "";
|
|
$department = "";
|
|
$father_name = "";
|
|
$father_surname = "";
|
|
$aadhar_no = "";
|
|
$passport = "";
|
|
$dbColumnValueList = array();
|
|
|
|
$dob = "";
|
|
$doj = "";
|
|
$patient_category = "";
|
|
$employee_contractor = "";
|
|
$gender = "";
|
|
$phone_number = "";
|
|
$village = "";
|
|
$post = "";
|
|
$ps = "";
|
|
$tehsil = "";
|
|
$district = "";
|
|
$state = "";
|
|
$pin_code = "";
|
|
$is_first_aid = "";
|
|
$blood_group = "";
|
|
$health_risks = "";
|
|
$health_advices = "";
|
|
$ohc_location = "";
|
|
$status = "";
|
|
$primary_contact_person = "";
|
|
$ohc_location = "";
|
|
$status = "";
|
|
$primary_contact_person = "";
|
|
$primary_patient_id = "";
|
|
$relation_type = "";
|
|
$primary_contact_no = "";
|
|
$secondary_contact_person = "";
|
|
$secondary_contact_no = "";
|
|
$name1 = "";
|
|
$age1 = "";
|
|
$gender1 = "";
|
|
$relation_type1 = "";
|
|
$typhoid = "";
|
|
$major_injury = "";
|
|
$diabetes = "";
|
|
$tb = "";
|
|
$hbd = "";
|
|
$epilepsy = "";
|
|
$psychiatric = "";
|
|
$heart_deases = "";
|
|
$height = "";
|
|
$weight = "";
|
|
$bp = "";
|
|
$waist = "";
|
|
$is_ohc_staff = "";
|
|
|
|
error_log("max value" . $max_cols);
|
|
$crntColIndex = 0;
|
|
while ($crntColIndex < $max_cols) {
|
|
|
|
$colVal = mysqli_real_escape_string($conn, $Row[$crntColIndex]);
|
|
|
|
$dbColumnValueList[$crntColIndex] = $colVal; // store the column value into valuearray
|
|
|
|
// custom logic, validations and calculations -- start
|
|
if ($dbColumnNameList[$crntColIndex] == 'emp_code') {
|
|
$emp_code = $colVal;
|
|
// if ($emp_code == "" && $emp_code == NULL) {
|
|
// $response_mssages .= "<br>Employee Code Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'patient_name') {
|
|
$employee_name = $colVal;
|
|
if ($employee_name == "" && $employee_name == NULL) {
|
|
$response_mssages .= "<br>Employee Name Missing at row: " . +$j;
|
|
$fail_counter++;
|
|
continue;
|
|
}
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'father_name') {
|
|
$father_name = $colVal;
|
|
/*if ($father_name == "" && $father_name == NULL) {
|
|
$response_mssages .= "<br>Employee Father Name Missing at row: " . + $j;
|
|
$fail_counter ++;
|
|
continue;
|
|
}*/
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'father_surname') {
|
|
$father_surname = $colVal;
|
|
/*if ($father_surname == "" && $father_surname == NULL) {
|
|
$response_mssages .= "<br>Employee Father Surname Name Missing at row: " . + $j;
|
|
$fail_counter ++;
|
|
continue;
|
|
}*/
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'designation_id') {
|
|
$designation = $colVal;
|
|
// if ($designation == "" && $designation == NULL) {
|
|
// $response_mssages .= "<br>Designation Name Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'bu_id') {
|
|
$bussiness_unit = $colVal;
|
|
// if ($bussiness_unit == NULL) {
|
|
// $response_mssages .= "<br>Bussiness unit Name Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'dept_id') {
|
|
$department = $colVal;
|
|
// if ($department == NULL) {
|
|
// $response_mssages .= "<br>Department Name Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'section_id') {
|
|
$section = $colVal;
|
|
// if ($section == NULL) {
|
|
// $response_mssages .= "<br>section Name Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'client_id') {
|
|
$client = $colVal;
|
|
// if ($section == NULL) {
|
|
// $response_mssages .= "<br>section Name Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'client_location_id') {
|
|
$client_location = $colVal;
|
|
// if ($section == NULL) {
|
|
// $response_mssages .= "<br>section Name Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'passport') {
|
|
$passport = $colVal;
|
|
// if ($section == NULL) {
|
|
// $response_mssages .= "<br>section Name Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
}
|
|
|
|
else if ($dbColumnNameList[$crntColIndex] == 'sub_section_id') {
|
|
$sub_section = $colVal;
|
|
// if ($sub_section == NULL) {
|
|
// $response_mssages .= "<br>Sub-section Name Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'aadhar_no') {
|
|
|
|
$aadhar_no = $colVal;
|
|
// if ($aadhar_no == "" && $aadhar_no == NULL && minlength == "12" && maxlength == "12") {
|
|
// $response_mssages .= "<br>Aadhar No Missing at row: " . + $j;
|
|
// $fail_counter ++;
|
|
// continue;
|
|
// }
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'dob') {
|
|
$dob = $colVal;
|
|
error_log("dob previous " . $dob);
|
|
if ($dob != '' && $dob != null) {
|
|
$time1 = strtotime($dob);
|
|
$dob = date('Y-m-d', $time1);
|
|
}
|
|
// error_log("type of date in excel " . gettype($dob));
|
|
error_log("new format dob date " . $dob);
|
|
|
|
$dbColumnValueList[$crntColIndex] = $dob;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'doj') {
|
|
$doj = $colVal;
|
|
error_log("doj previous " . $doj);
|
|
if ($doj != '' && $doj != null) {
|
|
$time2 = strtotime($doj);
|
|
$doj = date('Y-m-d', $time2);
|
|
}
|
|
// error_log("type of date in excel " . gettype($dob));
|
|
error_log("new format doj date " . $doj);
|
|
|
|
$dbColumnValueList[$crntColIndex] = $doj;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'patient_cat_id') {
|
|
$patient_category = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $patient_category;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'emp_cadre') {
|
|
$emp_cadre_name = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $emp_cadre_name;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'emp_cadre_id') {
|
|
$emp_cadre_name = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $emp_cadre_name;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'employer_contractor_id') {
|
|
$employee_contractor = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $employee_contractor;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'mgr_emp_code') {
|
|
$mgr_emp_code = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $mgr_emp_code;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'gender') {
|
|
$gender = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $gender;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'primary_phone') {
|
|
$phone_number = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $phone_number;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'personal_phone') {
|
|
$personal_phone = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $personal_phone;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'email_id') {
|
|
$email_id = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $email_id;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'offiial_email_id') {
|
|
$offiial_email_id = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $offiial_email_id;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'primary_phone') {
|
|
$phone_number = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $phone_number;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'primary_phone') {
|
|
$phone_number = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $phone_number;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'village') {
|
|
$village = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $village;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'post') {
|
|
$post = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $post;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'ps') {
|
|
$ps = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $ps;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'tehsil') {
|
|
$tehsil = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $tehsil;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'district') {
|
|
$district = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $district;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'state') {
|
|
$state = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $state;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'pin_code') {
|
|
$pin_code = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $pin_code;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'is_first_aid ') {
|
|
$is_first_aid = $colVal;
|
|
if (strtolower($is_first_aid) == 'yes')
|
|
$is_first_aid = 1;
|
|
else
|
|
$is_first_aid = 0;
|
|
$dbColumnValueList[$crntColIndex] = $is_first_aid;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'blood_group') {
|
|
$blood_group = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $blood_group;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'known_health_risks') {
|
|
$health_risks = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $health_risks;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'known_health_advices') {
|
|
$health_advices = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $health_advices;
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'hazardous_process') {
|
|
$hazardous_process = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $hazardous_process;
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'dangerous_process') {
|
|
$dangerous_process = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $dangerous_process;
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'raw_materials_exposed') {
|
|
$raw_materials_exposed = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $raw_materials_exposed;
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'nature_of_job') {
|
|
$nature_of_job = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $nature_of_job;
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'identity') {
|
|
$identity = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $identity;
|
|
}
|
|
else if ($dbColumnNameList[$crntColIndex] == 'status') {
|
|
$status = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $status;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'primary_contact_person') {
|
|
$primary_contact_person = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $primary_contact_person;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'primary_patient_id') {
|
|
$primary_patient_id = getFieldFromTable('id', 'patient_master', 'patient_name', $colVal);
|
|
$dbColumnValueList[$crntColIndex] = $primary_patient_id;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'relation_type') {
|
|
$relation_type = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $relation_type;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'status') {
|
|
$status = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $status;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'primary_contact_person') {
|
|
$primary_contact_person = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $primary_contact_person;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'primary_contact_no') {
|
|
$primary_contact_no = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $primary_contact_no;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'secondary_contact_person') {
|
|
$secondary_contact_person = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $secondary_contact_person;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'secondary_contact_no') {
|
|
$secondary_contact_no = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $secondary_contact_no;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'name1') {
|
|
$name1 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $name1;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'dob1') {
|
|
$dob1 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $dob1;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'gender1') {
|
|
$gender1 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $gender1;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'relation_type1') {
|
|
$relation_type1 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $relation_type1;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'name2') {
|
|
$name2 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $name2;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'dob2') {
|
|
$dob2 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $dob2;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'gender2') {
|
|
$gender2 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $gender2;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'relation_type2') {
|
|
$relation_type2 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $relation_type2;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'name3') {
|
|
$name3 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $name3;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'dob3') {
|
|
$dob3 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $dob3;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'gender3') {
|
|
$gender3 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $gender3;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'relation_type3') {
|
|
$relation_type3 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $relation_type3;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'name4') {
|
|
$name4 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $name4;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'dob4') {
|
|
$dob4 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $dob4;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'gender4') {
|
|
$gender4 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $gender4;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'relation_type4') {
|
|
$relation_type4 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $relation_type4;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'name5') {
|
|
$name5 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $name5;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'dob5') {
|
|
$dob5 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $dob5;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'gender5') {
|
|
$gender5 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $gender5;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'relation_type5') {
|
|
$relation_type5 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $relation_type5;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'name6') {
|
|
$name6 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $name6;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'dob6') {
|
|
$dob6 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $dob6;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'gender6') {
|
|
$gender6 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $gender6;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'relation_type6') {
|
|
$relation_type6 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $relation_type6;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'name7') {
|
|
$name7 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $name7;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'dob7') {
|
|
$dob7 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $dob7;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'gender7') {
|
|
$gender7 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $gender7;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'relation_type7') {
|
|
$relation_type7 = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $relation_type7;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'typhoid ') {
|
|
$typhoid = $colVal;
|
|
if (strtolower($typhoid) == 'yes')
|
|
$typhoid = 1;
|
|
else
|
|
$typhoid = 0;
|
|
$dbColumnValueList[$crntColIndex] = $typhoid;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'major_injury ') {
|
|
$major_injury = $colVal;
|
|
if (strtolower($major_injury) == 'yes')
|
|
$major_injury = 1;
|
|
else
|
|
$major_injury = 0;
|
|
$dbColumnValueList[$crntColIndex] = $major_injury;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'diabetes') {
|
|
$diabetes = $colVal;
|
|
if (strtolower($diabetes) == 'yes')
|
|
$diabetes = 1;
|
|
else
|
|
$diabetes = 0;
|
|
$dbColumnValueList[$crntColIndex] = $diabetes;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'tb') {
|
|
$tb = $colVal;
|
|
if (strtolower($tb) == 'yes')
|
|
$tb = 1;
|
|
else
|
|
$tb = 0;
|
|
$dbColumnValueList[$crntColIndex] = $tb;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'hbd') {
|
|
$hbd = $colVal;
|
|
if (strtolower($hbd) == 'yes')
|
|
$hbd = 1;
|
|
else
|
|
$hbd = 0;
|
|
$dbColumnValueList[$crntColIndex] = $hbd;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'epilepsy') {
|
|
$epilepsy = $colVal;
|
|
if (strtolower($epilepsy) == 'yes')
|
|
$hbd = 1;
|
|
else
|
|
$epilepsy = 0;
|
|
$dbColumnValueList[$crntColIndex] = $epilepsy;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'psychiatric') {
|
|
$psychiatric = $colVal;
|
|
if (strtolower($psychiatric) == 'yes')
|
|
$psychiatric = 1;
|
|
else
|
|
$psychiatric = 0;
|
|
$dbColumnValueList[$crntColIndex] = $psychiatric;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'heart_dis') {
|
|
$heart_deases = $colVal;
|
|
if (strtolower($heart_deases) == 'yes')
|
|
$heart_deases = 1;
|
|
else
|
|
$heart_deases = 0;
|
|
$dbColumnValueList[$crntColIndex] = $heart_deases;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'height') {
|
|
$height = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $height;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'weight') {
|
|
$weight = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $weight;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'bp') {
|
|
$bp = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $bp;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'waist') {
|
|
$waist = $colVal;
|
|
$dbColumnValueList[$crntColIndex] = $waist;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'is_ohc_staff') {
|
|
|
|
$is_ohc_staff = $colVal;
|
|
|
|
if (strtolower($is_ohc_staff) == 'yes')
|
|
$is_ohc_staff = 1;
|
|
else
|
|
$is_ohc_staff = 0;
|
|
|
|
$dbColumnValueList[$crntColIndex] = $is_ohc_staff;
|
|
} else if ($dbColumnNameList[$crntColIndex] == 'ohc_type_id') {
|
|
$ohc_location = $colVal;
|
|
error_log("" . $ohc_location);
|
|
|
|
$ohc_location_id = getTableFieldValue('ohc_type', 'ohc_type_id', 'lower(ohc_type_name)', "'" . strtolower($ohc_location) . "'");
|
|
|
|
$dbColumnValueList[$crntColIndex] = $ohc_location_id;
|
|
}
|
|
|
|
$crntColIndex++; // continue reading next column value
|
|
}
|
|
|
|
if (!empty($aadhar_no)) {
|
|
$id = getTableFieldValue('patient_master', 'id', 'aadhar_no', "'" . $aadhar_no . "'");
|
|
error_log("Found Existing Record with aadhar: " . $aadhar_no . "emp_id:" . $id);
|
|
} else if (!empty($emp_code)) {
|
|
$id = getTableFieldValue('patient_master', 'id', 'emp_code', "'" . $emp_code . "'");
|
|
error_log("Found Existing Record with ecode/gatepass no: " . $emp_code . "emp_id:" . $id);
|
|
} else if (!empty($passport)) {
|
|
$id = getTableFieldValue('patient_master', 'id', 'passport', "'" . $passport . "'");
|
|
error_log("Found Existing Record with ecode/gatepass no: " . $passport . "emp_id:" . $id);
|
|
}
|
|
else if (empty($aadhar_no) && empty($emp_code) && empty($passport)) {
|
|
$id_sql = "select id from patient_master where patient_name='" . $employee_name . "' and primary_patient_id = '" . $primary_patient_id . "'";
|
|
error_log("getting id for dependent non emp sql " . $id_sql);
|
|
$result_id = mysqli_query($conn, $id_sql);
|
|
$row_id = mysqli_fetch_assoc($result_id);
|
|
$id = $row_id['id'];
|
|
}
|
|
|
|
|
|
$health_advices_array = explode(",", $health_advices);
|
|
// echo $health_advices;
|
|
$health_advice_string = "";
|
|
for ($k = 0; $k < count($health_advices_array); $k++) {
|
|
if ($health_advices_array[$k] != "" && $health_advices_array[$k] != null) {
|
|
$health_advice_id = getTableFieldValue('health_advice', 'health_advice_id', 'health_advice_name', "'" . $health_advices_array[$k] . "'");
|
|
if ($health_advice_id == null or $health_advice_id == "")
|
|
mysqli_query($conn, "insert into health_advice set health_advice_name='" . $health_advices_array[$k] . "'");
|
|
$health_advice_id = getTableFieldValue('health_advice', 'health_advice_id', 'health_advice_name', "'" . $health_advices_array[$k] . "'");
|
|
if ($k == count($health_advices_array) - 1)
|
|
$health_advice_string .= $health_advice_id;
|
|
else
|
|
$health_advice_string .= $health_advice_id . ",";
|
|
}
|
|
}
|
|
|
|
$health_risks_array = explode(",", $health_risks);
|
|
$health_risks_string = "";
|
|
// echo $health_risks;
|
|
for ($k = 0; $k < count($health_risks_array); $k++) {
|
|
if ($health_risks_array[$k] != "" && $health_risks_array[$k] != null) {
|
|
$health_risk_id = getTableFieldValue('health_risk', 'health_risk_id', 'health_risk_name', "'" . $health_risks_array[$k] . "'");
|
|
if ($health_risk_id == null or $health_risk_id == "")
|
|
mysqli_query($conn, "insert into health_risk set health_risk_name='" . $health_risks_array[$k] . "'");
|
|
$health_risk_id = getTableFieldValue('health_risk', 'health_risk_id', 'health_risk_name', "'" . $health_risks_array[$k] . "'");
|
|
if ($k == count($health_risks_array) - 1)
|
|
$health_risks_string .= $health_risk_id;
|
|
else
|
|
$health_risks_string .= $health_risk_id . ",";
|
|
}
|
|
}
|
|
// $blood_group_id=getTableFieldValue('blood_group','id','type',"'".$blood_group."'");
|
|
|
|
// $blood_group_id=getTableFieldValue('blood_group','id','type',"'".$blood_group."'");
|
|
// if($blood_group_id==null or $blood_group_id==""){
|
|
// mysqli_query($conn,"insert into blood_group set type='".$blood_group."'");
|
|
// $blood_group_id=getTableFieldValue('blood_group','id','type',"'".$blood_group."'");
|
|
// }
|
|
|
|
$patient_category_id = getTableFieldValue('patient_category', 'patient_cat_id', 'patient_cat_name', "'" . $patient_category . "'");
|
|
if ($patient_category_id == null or $patient_category_id == "") {
|
|
mysqli_query($conn, "insert into patient_category set patient_cat_name='" . $patient_category . "'");
|
|
$patient_category_id = getTableFieldValue('patient_category', 'patient_cat_id', 'patient_cat_name', "'" . $patient_category . "'");
|
|
}
|
|
if (isset($bussiness_unit)) {
|
|
$bu_id = getTableFieldValue('bussiness_unit', 'bu_id', 'bu_name', "'" . $bussiness_unit . "'");
|
|
if ($bu_id == null or $bu_id == "") {
|
|
mysqli_query($conn, "insert into bussiness_unit set bu_name='" . $bussiness_unit . "'");
|
|
$bu_id = getTableFieldValue('bussiness_unit', 'bu_id', 'bu_name', "'" . $bussiness_unit . "'");
|
|
}
|
|
}
|
|
if (isset($department)) {
|
|
$dept_id = getTableFieldValue('department', 'dept_id', 'dept_name', "'" . $department . "'");
|
|
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 . "'");
|
|
}
|
|
}
|
|
if (isset($section)) {
|
|
$section_id = getTableFieldValue('section', 'section_id', 'section_name', "'" . $section . "'");
|
|
if ($section_id == null or $section_id == "") {
|
|
mysqli_query($conn, "insert into section set section_name='" . $section . "'");
|
|
$section_id = getTableFieldValue('section', 'section_id', 'section_name', "'" . $section . "'");
|
|
}
|
|
}
|
|
if (isset($client)) {
|
|
$client_id = getTableFieldValue('client_master', 'id', 'client_name', "'" . $client . "'","ohc_type",$_SESSION['current_ohcttype']);
|
|
if ($client_id == null or $client_id == "") {
|
|
mysqli_query($conn, "insert into client_master set client_name='" . $client . "' , ohc_type='".$_SESSION['current_ohcttype']."'");
|
|
$client_id = getTableFieldValue('client_master', 'id', 'client_name', "'" . $client . "'" ,"ohc_type",$_SESSION['current_ohcttype']);
|
|
}
|
|
}if (isset($client_location)) {
|
|
$client_location_id = getTableFieldValue('client_location', 'client_location_id', 'client_location', "'" . $client_location . "'");
|
|
if ($client_location_id == null or $client_location_id == "") {
|
|
mysqli_query($conn, "insert into client_location set client_location='" . $client_location . "'");
|
|
$client_location_id = getTableFieldValue('client_location', 'client_location_id', 'client_location', "'" . $client_location . "'");
|
|
}
|
|
}
|
|
if (isset($sub_section)) {
|
|
$sub_section_id = getTableFieldValue('sub_section', 'sub_section_id', 'sub_section_name', "'" . $sub_section . "'");
|
|
if ($sub_section_id == null or $sub_section_id == "") {
|
|
mysqli_query($conn, "insert into sub_section set sub_section_name='" . $sub_section . "'");
|
|
$sub_section_id = getTableFieldValue('sub_section', 'sub_section_id', 'sub_section_name', "'" . $sub_section . "'");
|
|
}
|
|
}
|
|
$designation_id = getTableFieldValue('designation', 'designation_id', 'designation_name', "'" . $designation . "'");
|
|
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 . "'");
|
|
}
|
|
|
|
$employer_contractor_id = getTableFieldValue('employer_contractor', 'id', 'employer_contractor_name', "'" . $employee_contractor . "'");
|
|
if ($employer_contractor_id == null or $employer_contractor_id == "") {
|
|
mysqli_query($conn, "insert into employer_contractor set employer_contractor_name='" . $employee_contractor . "'");
|
|
$employer_contractor_id = getTableFieldValue('employer_contractor', 'id', 'employer_contractor_name', "'" . $employee_contractor . "'");
|
|
}
|
|
|
|
$emp_cadre_id = getTableFieldValue('emp_cadre', 'emp_cadre_id', 'emp_cadre', "'" . $emp_cadre_name . "'");
|
|
|
|
if ($emp_cadre_id == null or $emp_cadre_id == "") {
|
|
mysqli_query($conn, "insert into emp_cadre set emp_cadre='" . $emp_cadre_name . "'");
|
|
$emp_cadre_id = getTableFieldValue('emp_cadre', 'emp_cadre_id', 'emp_cadre', "'" . $emp_cadre_name . "'");
|
|
}
|
|
if ($mgr_emp_code != '' || $mgr_emp_code != null) {
|
|
$mgr_emp_id = getFieldFromTable('id', 'patient_master', 'emp_code', $mgr_emp_code);
|
|
}
|
|
$initquery = "";
|
|
$endquery = "";
|
|
|
|
if (isset($id)) {
|
|
$initquery = "update patient_master set ";
|
|
$endquery = " where id = '" . $id . "' ";
|
|
$update_counter++;
|
|
} else {
|
|
$initquery = "insert into patient_master set ";
|
|
$endquery = "";
|
|
$insert_counter++;
|
|
}
|
|
$query_patient = $initquery . " dob = STR_TO_DATE('" . $dob . "','%Y-%m-%d'),doj = STR_TO_DATE('" . $doj . "','%Y-%m-%d')
|
|
, emp_code='" . $emp_code . "',patient_name='" . $employee_name . "',father_name='" . $father_name . "',father_surname='" . $father_surname . "',gender='" . $gender . "',patient_cat_id='" . $patient_category_id . "',nature_of_job='".$nature_of_job."',identity='".$identity."',hazardous_process='".$hazardous_process."',dangerous_process='".$dangerous_process."',raw_materials_exposed='".$raw_materials_exposed."',
|
|
primary_phone='" . $phone_number . "',bu_id='" . $bu_id . "',dept_id='" . $dept_id . "',section_id='" . $section_id . "',sub_section_id='" . $sub_section_id . "',designation_id='" . $designation_id . "',blood_group='" . $blood_group . "',is_first_aid='" . $is_first_aid . "',is_ohc_staff='" . $is_ohc_staff . "',ps='" . $ps . "',
|
|
pin_code='" . $pin_code . "',district='" . $district . "',tehsil='" . $tehsil . "',post='" . $post . "',village='" . $village . "',aadhar_no='" . $aadhar_no . "',passport='" . $passport . "',known_health_advices='" . $health_advice_string . "', known_health_risks='" . $health_risks_string . "',primary_contact_person='" . $primary_contact_person . "',primary_patient_id='" . $primary_patient_id . "',relation_type='" . $relation_type . "',
|
|
primary_contact_no='" . $primary_contact_no . "', secondary_contact_person='" . $secondary_contact_person . "' , secondary_contact_no='" . $secondary_contact_no . "',
|
|
height='" . $height . "', weight='" . $weight . "', waist='" . $waist . "', diabetes='" . $diabetes . "', bp='" . $bp . "',ohc_type_id='" . $ohc_location_id . "',status='" . $status . "',
|
|
emp_mgr_code='" . $mgr_emp_id . "',client_id='".$client_id."',client_location_id='".$client_location_id."', personal_phone='" . $personal_phone . "', email_id='" . $email_id . "',offiial_email_id='" . $offiial_email_id . "', emp_cadre='" . $emp_cadre_id . "',employer_contractor_id='" . $employer_contractor_id . "'" . $endquery;
|
|
|
|
error_log("new query for employee.." . $query_patient);
|
|
|
|
if (!$result = @mysqli_query($conn, $query_patient)) {
|
|
// $response_array['responseText']=mysqli_error($conn);
|
|
error_log("Exception:" . mysqli_error($conn));
|
|
error_log("Failed to Execute Patient Insert/Update Query::: " . $query_patient);
|
|
rollback();
|
|
exit(mysqli_error($conn));
|
|
}
|
|
if (empty($id)) {
|
|
$id = @mysqli_insert_id($conn);
|
|
error_log("Newly Added employees Id:" . $id);
|
|
}
|
|
|
|
// $result = mysqli_query($conn, $query_patient);
|
|
$My_Family1 = "";
|
|
// $id = getTableFieldValue('patient_master', 'id', 'aadhar_no', "'" . $aadhar_no . "'");
|
|
mysqli_query($conn, " delete from emp_family_members where emp_id='" . $id . "'");
|
|
if ($name1 != null && $name1 != "") {
|
|
$My_Family1 = " insert into emp_family_members set name='" . $name1 . "',dob = STR_TO_DATE('" . $dob1 . "','%Y/%m/%d'),gender='" . $gender1 . "',relation_type='" . $relation_type1 . "',emp_id='" . $id . "' ";
|
|
error_log("family meme1" . $My_Family1);
|
|
if (!$result1 = @mysqli_query($conn, $My_Family1)) {
|
|
|
|
error_log("Exception:" . mysqli_error($conn));
|
|
error_log("Failed to Execute Family Member Insert/Update Query::: " . $My_Family1);
|
|
rollback();
|
|
exit(mysqli_error($conn));
|
|
}
|
|
}
|
|
if ($name2 != null && $name2 != "") {
|
|
$My_Family2 = " insert into emp_family_members set name='" . $name2 . "',dob = STR_TO_DATE('" . $dob2 . "','%Y/%m/%d'),gender='" . $gender2 . "',relation_type='" . $relation_type2 . "',emp_id='" . $id . "' ";
|
|
error_log("family meme2" . $My_Family2);
|
|
if (!$result2 = @mysqli_query($conn, $My_Family2)) {
|
|
|
|
error_log("Exception:" . mysqli_error($conn));
|
|
error_log("Failed to Execute Family Member Insert/Update Query::: " . $My_Family2);
|
|
rollback();
|
|
exit(mysqli_error($conn));
|
|
}
|
|
}
|
|
if ($name3 != null && $name3 != "") {
|
|
$My_Family3 = " insert into emp_family_members set name='" . $name3 . "',dob = STR_TO_DATE('" . $dob3 . "','%Y/%m/%d'),gender='" . $gender3 . "',relation_type='" . $relation_type3 . "',emp_id='" . $id . "' ";
|
|
error_log("family meme3" . $My_Family3);
|
|
if (!$result3 = @mysqli_query($conn, $My_Family3)) {
|
|
|
|
error_log("Exception:" . mysqli_error($conn));
|
|
error_log("Failed to Execute Family Member Insert/Update Query::: " . $My_Family3);
|
|
rollback();
|
|
exit(mysqli_error($conn));
|
|
}
|
|
}
|
|
if ($name4 != null && $name4 != "") {
|
|
$My_Family4 = " insert into emp_family_members set name='" . $name4 . "',dob = STR_TO_DATE('" . $dob4 . "','%Y/%m/%d'),gender='" . $gender4 . "',relation_type='" . $relation_type4 . "',emp_id='" . $id . "' ";
|
|
error_log("family meme4" . $My_Family4);
|
|
if (!$result4 = @mysqli_query($conn, $My_Family4)) {
|
|
|
|
error_log("Exception:" . mysqli_error($conn));
|
|
error_log("Failed to Execute Family Member Insert/Update Query::: " . $My_Family4);
|
|
rollback();
|
|
exit(mysqli_error($conn));
|
|
}
|
|
}
|
|
if ($name5 != null && $name5 != "") {
|
|
$My_Family5 = " insert into emp_family_members set name='" . $name5 . "',dob = STR_TO_DATE('" . $dob5 . "','%Y/%m/%d'),gender='" . $gender5 . "',relation_type='" . $relation_type5 . "',emp_id='" . $id . "' ";
|
|
error_log("family meme5" . $My_Family5);
|
|
if (!$result5 = @mysqli_query($conn, $My_Family5)) {
|
|
|
|
error_log("Exception:" . mysqli_error($conn));
|
|
error_log("Failed to Execute Family Member Insert/Update Query::: " . $My_Family5);
|
|
rollback();
|
|
exit(mysqli_error($conn));
|
|
}
|
|
}
|
|
if ($name6 != null && $name6 != "") {
|
|
$My_Family6 = " insert into emp_family_members set name='" . $name6 . "',dob = STR_TO_DATE('" . $dob6 . "','%Y/%m/%d'),gender='" . $gender6 . "',relation_type='" . $relation_type6 . "',emp_id='" . $id . "' ";
|
|
error_log("family meme6" . $My_Family6);
|
|
if (!$result6 = @mysqli_query($conn, $My_Family6)) {
|
|
|
|
error_log("Exception:" . mysqli_error($conn));
|
|
error_log("Failed to Execute Family Member Insert/Update Query::: " . $My_Family6);
|
|
rollback();
|
|
exit(mysqli_error($conn));
|
|
}
|
|
}
|
|
if ($name7 != null && $name7 != "") {
|
|
$My_Family7 = " insert into emp_family_members set name='" . $name7 . "',dob = STR_TO_DATE('" . $dob7 . "','%Y/%m/%d'),gender='" . $gender7 . "',relation_type='" . $relation_type7 . "',emp_id='" . $id . "' ";
|
|
error_log("family meme7" . $My_Family7);
|
|
if (!$result7 = @mysqli_query($conn, $My_Family7)) {
|
|
|
|
error_log("Exception:" . mysqli_error($conn));
|
|
error_log("Failed to Execute Family Member Insert/Update Query::: " . $My_Family7);
|
|
rollback();
|
|
exit(mysqli_error($conn));
|
|
}
|
|
}
|
|
if ($result) {
|
|
$response_array['status'] = 'success';
|
|
// $message = "Excel Data Imported Into the Database";
|
|
} else {
|
|
$response_array['status'] = 'error';
|
|
// $messaged = "Problem In Importing Excel Data";
|
|
}
|
|
// }
|
|
}
|
|
}
|
|
}
|
|
commit();
|
|
$response_mssages1 = $insert_counter . " " . "records inserted<br> ";
|
|
$response_mssages1 .= $update_counter . " " . "records updated<br>";
|
|
$response_mssages1 .= $fail_counter . " " . "records skipped<br> ";
|
|
$response_array['responseText'] = "<br>" . $response_mssages1 . $response_mssages;
|
|
echo json_encode($response_array);
|