php - Laravel 将 CSV 数据解析为数组/对象:添加了重复/不正确的记录
问题描述
该函数的想法是上传一个 CSV,将其与数据库中的多个点进行比较,然后根据比较的方式将每一行推入一个单独的数组中。这将帮助我确定在上传干净副本以提交到数据库之前需要完成哪些手动输入工作(如果有)。出于某种原因,CSV 行被多次检查并进入错误的数组。我的代码,包括所需的结果,如下所示。这是基本概念:
上传 CSV 文件并使用 rx#/pharmacy 和医生/患者创建唯一变量。
对照数据库检查 rx#/pharmacy 以查看是否存在完全匹配。如果是这样,将行添加到existing_records
数组。如果 rx# 存在,但 pharmacy 错误,则将其添加到fail_array
rx#/pharmacy 不匹配的原因。
如果数据库中不存在 rx#,请将医生/患者姓名与数据库进行比较以确保它们存在。如果它们确实存在,那么两个地方的名称拼写正确,我将能够正确上传电子表格以提交到数据库,因此记录被添加到success_array
. 如果不是,则将记录添加到fail_array
医生/患者姓名不匹配的原因中。
然后将数组转换为对象,以便 Laravel 刀片可以运行 foreach 循环。
控制器
public function comparePharmacyReport(Request $request)
{
// Initialize variables
$header_array = [];
$existing_records_array = [];
$existing_records = new \stdClass();
$fail_records_array = [];
$fail_records = new \stdClass();
$success_records_array = [];
$success_records = new \stdClass();
// Get CSV file
$upload = $request->file('upload_file');
$file_path = $upload->getRealPath();
// Open and read the file
$file = fopen($file_path, 'r');
$header = fgetcsv($file);
// Validate the file
foreach ($header as $key => $value) {
// Transform $header to lowercase
$header_item = strtolower(trim($value));
// Place each item in the $header_array
array_push($header_array, $header_item);
}
// Loop through the columns
while ($columns = fgetcsv($file)) {
if ($columns[0] == "") {
continue;
}
$record = array_combine($header_array, $columns);
// Update table
$upload_rx_number = $record['rx_number'];
$upload_doctor = $record['provider'];
$upload_patient = $record['patient'];
$upload_pharmacy = $record['pharmacy'];
// create unique identifier for uploaded records
$upload_unique_pharmacy_rx_number = $upload_pharmacy . '_' . $upload_rx_number;
$upload_unique_doctor_patient = $upload_doctor . '_' . $upload_patient;
// check to see if $upload_rx_number is already in Prescriptions
if (Prescription::where('rx_number', $upload_rx_number)->first() != null) {
// $upload_rx_number exists
// get $database_pharmacy_id associated with the $upload_rx_number
$rx_check_prescription = Prescription::where('rx_number', $upload_rx_number)->first();
$database_pharmacy_id = Script::where('id', $rx_check_prescription->id)->first()->pharmacy_id;
// create database identifier for matching to database
$database_unique_pharmacy_rx_number = $database_pharmacy_id . '_' . $upload_rx_number;
// check to see if the uploaded pharmacy_rx_number unique identifier matches the database pharmacy_rx_number unique identifier
if ($upload_unique_pharmacy_rx_number == $database_unique_pharmacy_rx_number) {
// unique identifiers match
// add to existing array
array_push($existing_records_array, [
'rx_number' => $upload_rx_number,
'doctor' => $upload_doctor,
'patient' => $upload_patient,
'pharmacy' => $upload_pharmacy
]);
} else {
// unique identifiers DO NOT match
// add to fail array
array_push($fail_records_array, [
'rx_number' => $upload_rx_number,
'doctor' => $upload_doctor,
'patient' => $upload_patient,
'pharmacy' => $upload_pharmacy,
'reason' => "RX number-pharmacy mismatch"
]);
}
} else {
// $upload_rx_number doesn't exist in database
// prescription is new
$database_prescriptions = Prescription::all();
// run through all $database_prescriptions and check patient names
foreach ($database_prescriptions as $database_prescription) {
// setup database variables
$database_script_id = $database_prescription->script_id;
$database_script = Script::find($database_script_id);
$database_patient_id = $database_script->patient_id;
$database_patient = Patient::find($database_patient_id);
$database_doctor = $database_patient->doctors()->first();
// create database doctor/patient identifier
$database_unique_doctor_patient = $database_doctor->full_name . '_' . $database_patient->full_name;
// check to see if the uploaded doctor_patient unique identifier matches the database doctor_patient unique identifier
if ($database_unique_doctor_patient == $upload_unique_doctor_patient) {
// unique identifiers match
// add to success array
array_push($success_records_array, [
'rx_number' => $upload_rx_number,
'doctor' => $upload_doctor,
'patient' => $upload_patient,
'pharmacy' => $upload_pharmacy
]);
} else {
// unique identifiers DO NOT match
// add to fail array
array_push($fail_records_array, [
'rx_number' => $upload_rx_number,
'doctor' => $upload_doctor,
'patient' => $upload_patient,
'pharmacy' => $upload_pharmacy,
'reason' => "Doctor-patient name mismatch"
]);
}
}
}
}
foreach ($existing_records_array as $key => $value) {
$existing_records->$key = $value;
}
foreach ($fail_records_array as $key => $value) {
$fail_records->$key = $value;
}
foreach ($success_records_array as $key => $value) {
$success_records->$key = $value;
}
$data = [
'existing_records' => $existing_records,
'fail_records' => $fail_records,
'success_records' => $success_records,
];
return view('prescriptions.compare-results')->with($data);
}
刀
<h1>Fail Records</h1>
<div class="table-responsive">
<table class="table table-hover table-sm">
<thead>
<tr>
<th scope="col">RX #</th>
<th scope="col">Doctor</th>
<th scope="col">Patient</th>
<th scope="col">Pharmacy</th>
<th scope="col">Fail Reason</th>
</tr>
</thead>
<tbody>
@foreach ($fail_records as $fail_record)
<tr>
<th scope="row">{{$fail_record['rx_number']}}</th>
<td>{{$fail_record['doctor']}}</td>
<td>{{$fail_record['patient']}}</td>
<td>{{$fail_record['pharmacy']}}</td>
<td>{{$fail_record['reason']}}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
<br><br>
<h1>Success Records</h1>
<div class="table-responsive">
<table class="table table-hover table-sm">
<thead>
<tr>
<th scope="col">RX #</th>
<th scope="col">Doctor</th>
<th scope="col">Patient</th>
<th scope="col">Pharmacy</th>
</tr>
</thead>
<tbody>
@foreach ($success_records as $success_record)
<tr>
<th scope="row">{{$success_record['rx_number']}}</th>
<td>{{$success_record['doctor']}}</td>
<td>{{$success_record['patient']}}</td>
<td>{{$success_record['pharmacy']}}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
<br><br>
<h1>Existing Records</h1>
<div class="table-responsive">
<table class="table table-hover table-sm">
<thead>
<tr>
<th scope="col">RX #</th>
<th scope="col">Doctor</th>
<th scope="col">Patient</th>
<th scope="col">Pharmacy</th>
</tr>
</thead>
<tbody>
@foreach ($existing_records as $existing_record)
<tr>
<th scope="row">{{$existing_record['rx_number']}}</th>
<td>{{$existing_record['doctor']}}</td>
<td>{{$existing_record['patient']}}</td>
<td>{{$existing_record['pharmacy']}}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
期望的结果(RX 编号)
Fail Records
10 RX number-pharmacy mismatch
12 Doctor-patient name mismatch
Success Records
11
Existing Records
1
2
3
4
5
6
7
实际结果(RX 编号)
Fail Records
2 RX number-pharmacy mismatch
3 RX number-pharmacy mismatch
4 RX number-pharmacy mismatch
5 RX number-pharmacy mismatch
7 RX number-pharmacy mismatch
11 Doctor-patient name mismatch
11 Doctor-patient name mismatch
11 Doctor-patient name mismatch
11 Doctor-patient name mismatch
11 Doctor-patient name mismatch
11 Doctor-patient name mismatch
11 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
12 Doctor-patient name mismatch
10 RX number-pharmacy mismatch
Success Records
11
11
11
Existing Records
1
6
CSV 样本
rx_number provider patient pharmacy desired result
1 Doctor, John Anderson, Aaron 5 existing
2 Doctor, John Anderson, Aaron 5 existing
3 Doctor, Brad Smith, Kevin 4 existing
4 Doctor, Brad Smith, Kevin 4 existing
5 Doctor, Brad Smith, Kevin 4 existing
6 Doctor, Brad Doe, Richard 5 existing
7 Doctor, Brad Small, Big 5 existing
10 Doctor, Brad Hope, Bob 6 fail - rx#matches, but pharmacy wrong
11 Doctor, Brad Hope, Bob 5 success - rx# not in system, but names correct
12 Doctor, Brad Hope, Bobb 5 fail - patient name incorrect
解决方案
这一点在这里:
$database_prescriptions = Prescription::all();
// run through all $database_prescriptions and check patient names
foreach ($database_prescriptions as $database_prescription) {
// setup database variables
$database_script_id = $database_prescription->script_id;
$database_script = Script::find($database_script_id);
$database_patient_id = $database_script->patient_id;
$database_patient = Patient::find($database_patient_id);
$database_doctor = $database_patient->doctors()->first();
// create database doctor/patient identifier
$database_unique_doctor_patient = $database_doctor->full_name . '_' . $database_patient->full_name;
// check to see if the uploaded doctor_patient unique identifier matches the database doctor_patient unique identifier
if ($database_unique_doctor_patient == $upload_unique_doctor_patient) {
// unique identifiers match
// add to success array
array_push($success_records_array, [
'rx_number' => $upload_rx_number,
'doctor' => $upload_doctor,
'patient' => $upload_patient,
'pharmacy' => $upload_pharmacy
]);
} else {
// unique identifiers DO NOT match
// add to fail array
array_push($fail_records_array, [
'rx_number' => $upload_rx_number,
'doctor' => $upload_doctor,
'patient' => $upload_patient,
'pharmacy' => $upload_pharmacy,
'reason' => "Doctor-patient name mismatch"
]);
}
}
}
对于处方数据库中的每个条目,您正在检查名称是否匹配并相应地添加失败/成功,这可能是重复项的来源。
有很多方法可以解决这个问题,但如果稍微难看的解决方法,最简单的方法是使用循环遍历每条记录来进行比较,就像你现在做的那样,而不是每次找到匹配项时添加一条记录,每次找到一个不 -匹配添加失败记录,在找到匹配时将布尔值设置为 true 并中断,然后在循环结束后,进行简单的 if 检查以查看匹配查找布尔值是否为 true,如果是,添加记录成功,如果不是,则将记录添加到失败列表中。
希望这是有道理的。
推荐阅读
- github - 使用github桌面上传我的1GB文件时报错
- django - DRF simplejwt 刷新存储在 HTTPonly cookie 中的 access_token
- optimization - 如何检测 cvxpy 框架中的问题类型?
- webpack - 在 webpack5 中,使用 externals 时,如何将 callback 转换为 Promise?
- scala - 如何使用 LazyList 和 takeWhile 或 dropWhile 并避免产生永无止境的流?
- python - Pyinstaller exe python 文件给出错误 requests.exceptions.SSLError: [Errno 2] No such file or directory
- python - 根据另一个数据帧的lower_bound和upper_bound删除每列的异常值
- javascript - “TypeError: Object(...) is not a function”,在 react redux 中使用 dispatch 时出错
- flutter - Flutter - Mockito - 在测试中使用 async 会产生错误,但使用 async* 使其工作正常?
- reactjs - font-display: block 仍然会导致 FOUT