280 lines
9.5 KiB
PHP
280 lines
9.5 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
use Sabberworm\CSS\Value\Value;
|
||
|
|
|
||
|
|
include('includes/config/config.php');
|
||
|
|
include('includes/functions.php');
|
||
|
|
include('log_entry.php');
|
||
|
|
// $conn = mysqli_connect("localhost","root","","phpsamples");
|
||
|
|
require_once('excel/vendor/php-excel-reader/excel_reader2.php');
|
||
|
|
require_once('excel/vendor/SpreadsheetReader.php');
|
||
|
|
header('Content-type: application/json');
|
||
|
|
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
|
||
|
|
|
||
|
|
$response_array = array();
|
||
|
|
$response_mssages = "";
|
||
|
|
|
||
|
|
//$ohc_type = $_SESSION ['current_ohcttype'];
|
||
|
|
begin();
|
||
|
|
$allowedFileType = [
|
||
|
|
'application/vnd.ms-excel',
|
||
|
|
'text/xls',
|
||
|
|
'text/xlsx',
|
||
|
|
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
|
||
|
|
];
|
||
|
|
|
||
|
|
$dbColumnNameList = array();
|
||
|
|
|
||
|
|
if (in_array($_FILES["file"]["type"], $allowedFileType)) {
|
||
|
|
|
||
|
|
$targetPath = 'excel/uploads/' . $_FILES['file']['name'];
|
||
|
|
move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
|
||
|
|
|
||
|
|
$Reader = new SpreadsheetReader($targetPath);
|
||
|
|
|
||
|
|
$sheetCount = count($Reader->sheets());
|
||
|
|
$insert_counter = 0;
|
||
|
|
$update_counter = 0;
|
||
|
|
$fail_counter = 0;
|
||
|
|
|
||
|
|
for ($i = 0; $i < 1; $i++) {
|
||
|
|
|
||
|
|
$Reader->ChangeSheet($i);
|
||
|
|
$j = 0;
|
||
|
|
$max_cols = 0;
|
||
|
|
$crntColIndex = 0;
|
||
|
|
foreach ($Reader as $Row) {
|
||
|
|
$crntColIndex = 0;
|
||
|
|
// read row first to read table fields
|
||
|
|
if ($j == 0) {
|
||
|
|
$crntColIndex = 0;
|
||
|
|
$nondb_col = 0;
|
||
|
|
|
||
|
|
while (isset($Row[$crntColIndex]) && mysqli_real_escape_string($conn, $Row[$crntColIndex]) != null && trim($Row[$crntColIndex]) != '') {
|
||
|
|
$colVal = mysqli_real_escape_string($conn, $Row[$crntColIndex]);
|
||
|
|
//
|
||
|
|
if ($colVal != 'NA') {
|
||
|
|
$dbColumnNameList[$crntColIndex] = $Row[$crntColIndex];
|
||
|
|
} else {
|
||
|
|
// if NA for DB col found... then just record it with NA_Prefix to distinguish and discard later
|
||
|
|
$dbColumnNameList[$crntColIndex] = "NA_" + $nondb_col;
|
||
|
|
$nondb_col++;
|
||
|
|
}
|
||
|
|
$crntColIndex++;
|
||
|
|
} // end of while -- reading all columns for first row
|
||
|
|
|
||
|
|
$j++;
|
||
|
|
$max_cols = $crntColIndex;
|
||
|
|
error_log("db_fields_Array:" . $dbColumnNameList);
|
||
|
|
// print_r($dbColumnNameList);
|
||
|
|
continue;
|
||
|
|
} // end reading first row db fields
|
||
|
|
|
||
|
|
if ($j < 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 .= "<br>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 .= "<br>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 .= "<br>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 .= "<br>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 .= "<br>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 .= "<br>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 .= "<br>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 .= "<br>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<br> ";
|
||
|
|
$response_mssages1 .= $update_counter . " " . "records updated<br>";
|
||
|
|
$response_mssages1 .= $fail_counter . " " . "records skipped with Sr No. " . " " . $fail . "<br>";
|
||
|
|
$response_array['responseText'] = "<br>" . $response_mssages1 . $response_mssages;
|
||
|
|
echo json_encode($response_array);
|