首页 > 解决方案 > Laravel 将 CSV 数据解析为数组/对象:添加了重复/不正确的记录

问题描述

该函数的想法是上传一个 CSV,将其与数据库中的多个点进行比较,然后根据比较的方式将每一行推入一个单独的数组中。这将帮助我确定在上传干净副本以提交到数据库之前需要完成哪些手动输入工作(如果有)。出于某种原因,CSV 行被多次检查并进入错误的数组。我的代码,包括所需的结果,如下所示。这是基本概念:

上传 CSV 文件并使用 rx#/pharmacy 和医生/患者创建唯一变量。

对照数据库检查 rx#/pharmacy 以查看是否存在完全匹配。如果是这样,将行添加到existing_records数组。如果 rx# 存在,但 pharmacy 错误,则将其添加到fail_arrayrx#/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

标签: phplaravel

解决方案


这一点在这里:

 $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,如果是,添加记录成功,如果不是,则将记录添加到失败列表中。

希望这是有道理的。


推荐阅读