Excel导入数据库时基于手机号和客户名去重的实现需求

Got it, let's tackle this duplicate detection issue for your Excel import. The core requirement is to skip inserting rows where a record with the same customer name and phone number already exists in your database. Here's how to modify your controller code, plus some small optimizations:

Modified Controller Code #

public function importExcel(Request $request) {
    if ($request->hasFile('import_file')) {
        Excel::load($request->file('import_file')->getRealPath(), function ($reader) {
            foreach ($reader->toArray() as $key => $row) {
                // Step 1: Check for existing record using customername and phone
                $existingRecord = Registration::where('customername', $row['customername'])
                    ->where('phone', $row['phone'])
                    ->first();
                
                // Skip this row if duplicate exists
                if ($existingRecord) {
                    continue;
                }

                // Step 2: Get branch (add fallback to avoid errors if branch not found)
                $branch = Branch::where([['branch_code', $row['branchcode']], ['status', 0]])->first();
                
                // Optional: Skip row if branch doesn't exist to prevent fatal errors
                if (!$branch) {
                    continue;
                }

                // Step 3: Optimize registration_id generation (cleaner than ordering and taking 1)
                $lastRegId = Registration::max('registration_id');
                $regid = $lastRegId ? $lastRegId + 1 : 1;

                // Step 4: Create and save Registration record
                $register = new Registration();
                $register->registration_id = $regid;
                $register->customername = $row['customername'];
                $register->chassis = $row['chassis'];
                $register->model = $row['model'];
                $register->branchcode = $row['branchcode'];
                $register->delivery = $row['delivery'];
                $register->customerid = $row['customerid'];
                $register->phone = $row['phone'];
                $register->invoicedate = $row['invoicedate'];
                $register->dse = $row['dse'];
                $register->branch = $row['branch'];
                $register->finance = $row['finance'];
                $register->dono = $row['dono'];
                $register->invoice = $row['invoice'];
                $register->zsm = $row['zsm'];
                $register->sm = $row['sm'];
                $register->agm = $row['agm'];
                $register->dsecode = $row['dsecode'];
                $register->address = $row['address'];
                $register->email = $row['email'];
                $register->color = $row['color'];
                $register->extendedwarrenty = $row['extendedwarrenty'];
                $register->autocaddownload = $row['autocaddownload'];
                $register->numberplate = $row['numberplate'];
                $register->mcpstatus = $row['mcpstatus'];
                $register->plandt = $row['plandt'];
                $register->planok = $row['planok'];
                $register->fasttag = $row['fasttag'];
                $register->rcstatus = $row['rcstatus'];
                $register->dealership = $branch->dealership_id;
                $register->zone = $branch->zone_id;
                $register->dh = $branch->dh_id;
                $register->status = '0';
                $register->created_user_id = Session::get('created_id');
                $register->save();

                // Step 5: Create Ssi_track record (use the saved registration_id directly)
                $ssitrack = new Ssi_track();
                $ssitrack->registration_id = $register->registration_id; // Use the saved ID instead of recalculating
                $ssitrack->ssi_track_id = $register->registration_id;
                $ssitrack->save();
            }
        });
    }
    return back()->with('success', 'Your File Is Successfully Uploaded To Database!');
}

Key Changes Explained #

• Duplicate Check: Added a query to look for existing records matching both customername and phone. If found, we use continue to skip the rest of the loop for that row.
• Branch Safety Check: Added a check to skip rows where the associated branch isn't found (prevents fatal errors when accessing $branch->dealership_id).
• Registration ID Optimization: Replaced the old way of getting the last registration ID with Registration::max('registration_id')—it's cleaner and more efficient.
• Ssi_track Improvement: Instead of re-querying the last registration ID, we use $register->registration_id directly after saving the record (since Laravel sets this automatically on save).

内容的提问来源于stack exchange，提问作者user9461267