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