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 < 0) {
// ignore for other header rows
$j++;
continue;
}
if ($Row[0] == '') {
// if a blank row is encountered stop reading
break;
}
// read data rows - start
$id = "";
$employee_name = "";
$emp_code = "";
$aadhar_no = "";
$sr_no = "";
$vac_id = "";
$vac_name = "";
$vac_d1 = "";
$vac_d2 = "";
$vac_d3 = "";
$date = "";
$dose = "";
$remarks = "";
$vac_center = "";
$dbColumnValueList = array();
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
error_log('ttt' . $dbColumnNameList[$crntColIndex]);
error_log('vvv' . $colVal);
// custom logic, validations and calculations -- start
if ($dbColumnNameList[$crntColIndex] == 'ECODE' || $dbColumnNameList[$crntColIndex] == 'ecode') {
$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' || $dbColumnNameList[$crntColIndex] == 'patient name' || $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] == 'Sr No.' || $dbColumnNameList[$crntColIndex] == 'SR NO.' || $dbColumnNameList[$crntColIndex] == 'sr no.') {
$sr_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] == 'Aadhar No.' || $dbColumnNameList[$crntColIndex] == 'AADHAR NO.' || $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] == 'Vaccine Name' || $dbColumnNameList[$crntColIndex] == 'vaccine name' || $dbColumnNameList[$crntColIndex] == 'VACCINE NAME') {
$vac_name = $colVal;
$que2 = "SELECT `id` FROM `vaccine_master` WHERE `vaccine_name`='" . $vac_name . "'";
$res2 = @mysqli_query($conn, $que2);
$rowp2 = @mysqli_fetch_assoc($res2);
$vac_id = $rowp2["id"];
$dbColumnValueList[$crntColIndex] = $vac_id;
} else if ($dbColumnNameList[$crntColIndex] == 'Date' || $dbColumnNameList[$crntColIndex] == 'DATE' || $dbColumnNameList[$crntColIndex] == 'date') {
$date = $colVal;
error_log("dob previous " . $date);
if ($date != '' && $date != null) {
$time = strtotime($date);
$date = date('Y-m-d', $time);
}
// error_log("type of date in excel " . gettype($dob));
error_log("new format dob date " . $date);
$dbColumnValueList[$crntColIndex] = $date;
// if ($designation == "" && $designation == NULL) {
// $response_mssages .= "
Designation Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
} else if ($dbColumnNameList[$crntColIndex] == 'Remarks' || $dbColumnNameList[$crntColIndex] == 'remarks' || $dbColumnNameList[$crntColIndex] == 'REMARKS') {
$remarks = $colVal;
// if ($section == NULL) {
// $response_mssages .= "
section Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
} else if ($dbColumnNameList[$crntColIndex] == 'Vaccine Center' || $dbColumnNameList[$crntColIndex] == 'vaccine center' || $dbColumnNameList[$crntColIndex] == 'VACCINE CENTER') {
$vac_center = $colVal;
// if ($section == NULL) {
// $response_mssages .= "
section Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
} else if ($dbColumnNameList[$crntColIndex] == 'Dose' || $dbColumnNameList[$crntColIndex] == 'dose' || $dbColumnNameList[$crntColIndex] == 'DOSE') {
$dose = $colVal;
// if ($section == NULL) {
// $response_mssages .= "
section Name Missing at row: " . + $j;
// $fail_counter ++;
// continue;
// }
}
$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);
}
$initquery = "";
$endquery = "";
$dataid = array();
$fail_sr_no = array();
$fail;
$queid = "select id ,vac_id from vaccination_details where emp_id='" . $id . "' and vac_id='" . $vac_id . "'";
if (!$resultid = @mysqli_query($conn, $queid)) {
exit(mysqli_error($conn));
}
if (mysqli_num_rows($resultid) > 0) {
while ($rowid = @mysqli_fetch_assoc($resultid)) {
$dataid = $rowid['id'];
}
}
if (!empty($dataid)) {
$initquery = "update vaccination_details set ";
$endquery = " where id = '" . $dataid . "' ";
$update_counter++;
} else {
if ($id != 0) {
$initquery = "insert into vaccination_details set ";
$endquery = "";
$insert_counter++;
} else {
$fail_counter++;
array_push($fail_sr_no, $sr_no);
foreach ($fail_sr_no as $key => $value) {
$fail = $fail . strval($value) . ", ";
}
continue;
}
}
if (isset($id)) {
if ($dose == 1) {
$vac_d1 = $date;
$query_patient = $initquery . " emp_id = '" . $id . "',vac_id = '" . $vac_id . "' , vac_d1='" . $vac_d1 . "',vac_center1='" . $vac_center . "',remarks='" . $remarks . "'" . $endquery;
} else if ($dose == 2) {
$vac_d2 = $date;
$query_patient = $initquery . " emp_id = '" . $id . "',vac_id = '" . $vac_id . "' ,vac_d2='" . $vac_d2 . "',vac_center2='" . $vac_center . "',remarks='" . $remarks . "'" . $endquery;
} else {
$vac_d3 = $date;
$query_patient = $initquery . " emp_id = '" . $id . "',vac_id = '" . $vac_id . "' ,vac_d3='" . $vac_d3 . "',vac_center3='" . $vac_center . "',remarks='" . $remarks . "'" . $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);
}
// }
}
}
}
commit();
$response_mssages1 = $insert_counter . " " . "records inserted
";
$response_mssages1 .= $update_counter . " " . "records updated
";
$response_mssages1 .= $fail_counter . " " . "records skipped with Sr No. " . " " . $fail . "
";
$response_array['responseText'] = "
" . $response_mssages1 . $response_mssages;
echo json_encode($response_array);