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 .= "
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 .= "
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 .= "
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 .= "
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 .= "
Designation Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
} else if ($dbColumnNameList[$crntColIndex] == 'bu_id') {
$bussiness_unit = $colVal;
// if ($bussiness_unit == NULL) {
// $response_mssages .= "
Bussiness unit Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
} else if ($dbColumnNameList[$crntColIndex] == 'dept_id') {
$department = $colVal;
// if ($department == NULL) {
// $response_mssages .= "
Department Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
} else if ($dbColumnNameList[$crntColIndex] == 'section_id') {
$section = $colVal;
// if ($section == NULL) {
// $response_mssages .= "
section Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
}
else if ($dbColumnNameList[$crntColIndex] == 'client_id') {
$client = $colVal;
// if ($section == NULL) {
// $response_mssages .= "
section Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
}
else if ($dbColumnNameList[$crntColIndex] == 'client_location_id') {
$client_location = $colVal;
// if ($section == NULL) {
// $response_mssages .= "
section Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
} else if ($dbColumnNameList[$crntColIndex] == 'passport') {
$passport = $colVal;
// if ($section == NULL) {
// $response_mssages .= "
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 .= "
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 .= "
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
";
$response_mssages1 .= $update_counter . " " . "records updated
";
$response_mssages1 .= $fail_counter . " " . "records skipped
";
$response_array['responseText'] = "
" . $response_mssages1 . $response_mssages;
echo json_encode($response_array);