822 lines
36 KiB
PHP
822 lines
36 KiB
PHP
<?php
|
||
ini_set('max_execution_time',120000);
|
||
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 ();
|
||
|
||
error_log("STARTING");
|
||
|
||
function copyFileWithTimestamp($sourceFolder, $destinationFolder, $filename) {
|
||
// Get the current timestamp in the format: YearMonthDay-HourMinutesSeconds
|
||
$timestamp = date("Ymd-His");
|
||
|
||
// Extract the file extension from the original filename
|
||
$fileExtension = pathinfo($filename, PATHINFO_EXTENSION);
|
||
|
||
// Create the new filename with the timestamp appended
|
||
$newFilename = $filename . '_' . $timestamp . '.' . $fileExtension;
|
||
|
||
// Construct the full paths of the source and destination files
|
||
$sourceFilePath = $sourceFolder . '/' . $filename;
|
||
$destinationFilePath = $destinationFolder . '/' . $newFilename;
|
||
|
||
// Copy the file with the new name and timestamp to the destination folder
|
||
copy($sourceFilePath, $destinationFilePath);
|
||
}
|
||
|
||
// Usage example:
|
||
$sourceFolder = 'excel/uploads/';
|
||
$destinationFolder = 'excel/uploads/in_process/';
|
||
$destinationFolder_success = 'excel/uploads/success/';
|
||
$destinationFolder_failure = 'excel/uploads/failure/';
|
||
$filename = 'White_Collar_OHC.xls';
|
||
|
||
//if (in_array ( $_FILES ["file"] ["type"], $allowedFileType )) {
|
||
|
||
$file_name = 'White_Collar_OHC.xls';
|
||
|
||
|
||
$targetPath = 'excel/uploads/White_Collar_OHC.xls';
|
||
$filePointer = "excel/uploads/in_process/White_Collar_OHC.xls";
|
||
|
||
|
||
if(!file_exists($filePointer)){
|
||
copyFileWithTimestamp($sourceFolder,$destinationFolder,$filename);
|
||
}
|
||
|
||
$Reader = new SpreadsheetReader ( $targetPath );
|
||
|
||
|
||
|
||
$sheetCount = count ( $Reader->sheets () );
|
||
$insert_counter = 0;
|
||
$update_counter = 0;
|
||
$fail_counter = 0;
|
||
|
||
for($i = 0; $i < 1; $i ++) {
|
||
|
||
$j = 0 ;
|
||
|
||
error_log("IN LOOP");
|
||
|
||
$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 < 1) {
|
||
// 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 = "";
|
||
$aadhar_no = "";
|
||
$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_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 = "";
|
||
$emp_type = "";
|
||
$sub_emp_type = "";
|
||
$group_comp = "";
|
||
$emp_cadre_name = "";
|
||
|
||
error_log ( "max value" . $max_cols );
|
||
$crntColIndex = 0;
|
||
while ( $crntColIndex < $max_cols ) {
|
||
|
||
error_log("FIELD NAME::" .$dbColumnNameList [$crntColIndex] . " FIELD VALUE::" .$colVal );
|
||
|
||
$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] == 'employee_id') {
|
||
$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] == 'full_name') {
|
||
$employee_name = $colVal;
|
||
error_log("EMPLOYEE NAME::" .$employee_name);
|
||
if ($employee_name == "" && $employee_name == NULL) {
|
||
$response_mssages .= "<br>Employee Name Missing at row: " . + $j;
|
||
$fail_counter ++;
|
||
continue;
|
||
}
|
||
} else if ($dbColumnNameList [$crntColIndex] == 'fathers_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] == 'contribution_level') {
|
||
$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] == 'department') {
|
||
$department = $colVal;
|
||
// if ($department == NULL) {
|
||
// $response_mssages .= "<br>Department Name Missing at row: " . + $j;
|
||
// $fail_counter ++;
|
||
// continue;
|
||
// }
|
||
}
|
||
else if ($dbColumnNameList [$crntColIndex] == 'section') {
|
||
$section = $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] == 'date_of_birth') {
|
||
$dob = $colVal;
|
||
$dbColumnValueList [$crntColIndex] = $dob;
|
||
} else if ($dbColumnNameList [$crntColIndex] == 'date_of_joining') {
|
||
$doj = $colVal;
|
||
$dbColumnValueList [$crntColIndex] = $doj;
|
||
}
|
||
// } else if ($dbColumnNameList [$crntColIndex] == 'employee_type') {
|
||
// $emp_type = $colVal;
|
||
// $dbColumnValueList [$crntColIndex] = $emp_type;
|
||
// }
|
||
else if ($dbColumnNameList [$crntColIndex] == 'sub_employee_type') {
|
||
$sub_emp_type = $colVal;
|
||
$dbColumnValueList [$crntColIndex] = $sub_emp_type;
|
||
}
|
||
else if ($dbColumnNameList [$crntColIndex] == 'direct_manager_employee_id') {
|
||
$mgr_emp_id = $colVal;
|
||
$dbColumnValueList [$crntColIndex] = $mgr_emp_id;
|
||
}
|
||
// else if ($dbColumnNameList [$crntColIndex] == 'group_company') {
|
||
// $group_comp = $colVal;
|
||
// $dbColumnValueList [$crntColIndex] = $group_comp;
|
||
// }
|
||
//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] == 'employee_type') {
|
||
$emp_cadre_name = $colVal;
|
||
error_log("EMPLOYEE CADRE::" .$emp_cadre_name);
|
||
$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] == 'office_mobile_no') {
|
||
$phone_number = $colVal;
|
||
$dbColumnValueList [$crntColIndex] = $phone_number;
|
||
$phone_number = str_replace("'","",$phone_number);
|
||
}
|
||
else if ($dbColumnNameList [$crntColIndex] == 'primary_mobile_number') {
|
||
$personal_phone = $colVal;
|
||
$dbColumnValueList [$crntColIndex] = $personal_phone;
|
||
}
|
||
else if ($dbColumnNameList [$crntColIndex] == 'company_email_id') {
|
||
$email_id = $colVal;
|
||
$dbColumnValueList [$crntColIndex] = $email_id;
|
||
} else if ($dbColumnNameList [$crntColIndex] == 'dependent_name') {
|
||
$name1 = $colVal;
|
||
error_log("DEPENDENT NAME::" .$name1);
|
||
$dbColumnValueList [$crntColIndex] = $name1;
|
||
} else if ($dbColumnNameList [$crntColIndex] == 'dob') {
|
||
$dob1 = $colVal;
|
||
error_log("DEPENDENT DOB::" .$dob1);
|
||
$dbColumnValueList [$crntColIndex] = $dob1;
|
||
}
|
||
else if ($dbColumnNameList [$crntColIndex] == 'relation_type') {
|
||
$relation_type1 = $colVal;
|
||
$dbColumnValueList [$crntColIndex] = $relation_type1;
|
||
}
|
||
|
||
//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] == '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] == '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] == '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 ( $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 {
|
||
// // $response_mssages .= "<br>Aadhar No or Emp Code is Mandatory but Missing at row: " . + $j;
|
||
// // $fail_counter ++;
|
||
// // // continue;
|
||
// // }
|
||
|
||
|
||
// $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."'");
|
||
// // }
|
||
|
||
if($sub_emp_type == 'White Collar - Regular - Corporate' || $sub_emp_type == 'White Collar - Regular' || $sub_emp_type == 'White Collar - Trainee - Corporate'){
|
||
$patient_category_id = 1;
|
||
} else if($sub_emp_type == 'White Collar - Consultant'){
|
||
$patient_category_id = 2;
|
||
}
|
||
|
||
// $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($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(isset($mgr_emp_code)){
|
||
// $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 . "','%d-%m-%Y'),doj = STR_TO_DATE('" . $doj . "','%d-%m-%Y')
|
||
, emp_code='" . $emp_code . "',patient_name='" . addslashes(trim($employee_name)) . "',father_name='" . addslashes(trim($father_name)) . "',gender='" . $gender . "',patient_cat_id='" . $patient_category_id . "',
|
||
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 . "',
|
||
known_health_advices='" . $health_advice_string . "', known_health_risks='" . $health_risks_string . "',primary_contact_person='" . $primary_contact_person . "',
|
||
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 . "', personal_phone='" . trim($personal_phone) . "', email_id='',offiial_email_id='" . trim($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);
|
||
copyFileWithTimestamp($sourceFolder,$destinationFolder_failure,$filename);
|
||
mysqli_query($conn,"insert into batch_status set run_date = '$curr_date', insert_count = '$insert_counter', update_count = '$update_counter', status = 'Failed'");
|
||
error_log ( "Exception:" . mysqli_error($conn) );
|
||
error_log ( "Failed to Execute Patient Insert/Update Query::: " . $query_patient );
|
||
rollback ();
|
||
exit ( mysqli_error($conn) );
|
||
} else {
|
||
error_log("NO. OF ROWS EXECUTED::" .$j++);
|
||
|
||
|
||
}
|
||
if (empty ( $id )) {
|
||
$id = @mysqli_insert_id ();
|
||
error_log ( "Newly Added employees Id:" . $id );
|
||
|
||
}
|
||
|
||
|
||
// $result = mysqli_query($conn, $query_patient);
|
||
$My_Family1 = "";
|
||
$id = getTableFieldValue('patient_master', 'id', 'emp_code', "'" . $emp_code . "'");
|
||
//mysqli_query($conn," delete from emp_family_members where emp_id='" . $id . "'" );
|
||
if ($name1 != null && $name1 != "") {
|
||
$My_Family1 = " insert into patient_master set patient_name='" . $name1 . "',dob = STR_TO_DATE('" . $dob1 . "','%d-%m-%Y'),gender='" . $gender1 . "',relation_type='" . $relation_type1 . "',primary_patient_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 . "','%d-%m-%Y'),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 . "','%d-%m-%Y'),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 . "','%d-%m-%Y'),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 . "','%d-%m-%Y'),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 . "','%d-%m-%Y'),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 . "','%d-%m-%Y'),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 ();
|
||
$curr_date = date('Y-m-d');
|
||
mysqli_query($conn,"insert into batch_status set run_date = '$curr_date', insert_count = '$insert_counter', update_count = '$update_counter', status = 'Completed'");
|
||
copyFileWithTimestamp($sourceFolder,$destinationFolder_success,$filename);
|
||
|
||
|
||
$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 );
|
||
exit("FILE SUCCESSFULLY EXECUTED");
|
||
?>
|