CODEWITHSUNDEEP
phplaravel
Xerakon
Xerakon

Reputation: 159

Laravel Parsing CSV Data into Arrays/Objects: Repetitive/Incorrect Records Added

The idea of the function is to upload a CSV, compare it against multiple points in the database, and push each row into a separate array depending on how it compares. This will help me to determine what manual entry work (if any) needs to be done before I can upload a clean copy to commit to the database. For some reason, CSV rows are being checked multiple times and into the wrong arrays. My code, including desired results, is below. Here is the basic concept:

Upload CSV file and create unique variables with rx#/pharmacy and doctor/patient.

Check rx#/pharmacy against database to see if an exact match exists. If so, add row to existing_records array. If rx# exists, but pharmacy is wrong, add it to fail_array with a reason of rx#/pharmacy mismatch.

If rx# doesn't exist in database, compare doctor/patient names against database to make sure they exist. If they do exist, then the names are spelled correctly in both places and I will be able to upload the spreadsheet properly to commit to the database, so the record is added to the success_array. If not, then the record is added to the fail_array with a reason of doctor/patient name mismatch.

Then the arrays are turned into objects so that the Laravel blade can run a foreach loop.

Controller

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);
}

Blade

<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>

Desired Results (RX numbers)

Fail Records
10  RX number-pharmacy mismatch
12  Doctor-patient name mismatch

Success Records
11

Existing Records
1
2
3
4
5
6
7

Actual Results (RX numbers)

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 Sample

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

Upvotes: 0

Views: 398

Answers (1)

akbra
akbra

Reputation: 26

This bit right here:

 $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"
                ]);
            }
        }                
    }

For every entry in the prescription database, you're checking if the names match and adding to fail/success accordingly, this is probably where the duplicates are coming from.

There's lots of ways to fix this but the lowest effort if slightly ugly fix is to use the loop through each record to compare like you do now, but instead of every time you find a match adding a record and every time you find a not-match adding a failed record, on finding a match set a boolean to true and break, and past the end of the loop, have a simple if check to see whether or not your match-finding boolean is true, and if it is, add the record to success and if it isn't add the record to the fail list.

Hopefully that made sense.

Upvotes: 1

Related Questions