How to add custom increment value to database with laravel excel import-CodePudding

i'm using maatwebsite laravel excel to import some data from excel to database. But i want to add some custom ID with incremental value for each row of data. For now, i'm able to import data with some input value form together.

DataImport file

class DataImport implements ToModel, WithStartRow{

public function model(array $row)
{
       
    return new Tempdat([
        'employee_id' => ??? (combination of client_code  1)
        'name' => $row[1],
        'gender' => $row[2],
        'bod' => $this->transformDate($row[3]),
        'engagement_code' => request('engagement_code'), //from input form
        'client_code' => request('client_code'), //from input form
    ]);
}

public function transformDate($value, $format = 'Y-m-d')
{
    try {
        return \Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));
    } catch (\ErrorException $e) {
        return \Carbon\Carbon::createFromFormat($format, $value);
    }
}

public function startRow(): int
{
    return 2;
} }

DataController file

public function ImportExcel(Request $request)
{   
    $this->validate($request,[
        'file' => 'required|mimes:xls,xlsx',
        'engagement_code' => 'required',
    ]);

    $file = $request->file('file');
    $clientCode = request('client_code');
    $engagementCode = request('engagement_code');
    $todayDate = date('dFY');

    $file_name = $engagementCode.'_'.$todayDate.$file->getClientOriginalName();
    $file->move('tempdat',$file_name);

    Excel::import(new DataImport, public_path('/tempdat/'.$file_name));


    return redirect()->route('dashboard.tempdat.index');
}

What i'd like to do is to add "employee code" which is combination of "client_code" 1 for every row. for example if client_code is ABCD and there is 3 rows of data imported then the employee_code will be :

ABCD0001
ABCD0002
ABCD0003
...

i'm already searching for count rows but nothing found yet.

CodePudding user response：

Counting rows will bring you problems whenever you remove a single record from the table: You won't be able to insert new rows if your primary employee_id field has UNIQUE key, or you will start inserting duplicated IDs.

Generating the ID in PHP isn't my recomendation either since you could face integrity problems if two records are trying to be stored simultaneously.

I would use the default model's model_id field with autoincrement to make sure that I won't have problems with id assignation, and inmediatly after saving the record I would update the id for the employee_id field which can be also keyed as "primary" in order to be indexed and accessed efficiently.

For example:

class MyModel extends Model {
    public function save() {
        if(parent::save()) {
            $this->update['employee_id' => "ABCD".$this->id];
        }
    }
}

I haven't still realized how the Excel library you're using handles the model, but I supposed it can be specified somewhere along the process.

CodePudding user response：

I am able to generate incremental id in laravel excel using this:

https://github.com/haruncpi/laravel-id-generator

$config = [
        'table' => 'table_name',
        'length' => 7,
        'field' => 'employee_id',
        'prefix' => request('client_code'),
        'reset_on_prefix_change' => true,
    ];

    $employee_id = IdGenerator::generate($config);

so, everytime import executed, employee_id will generated on its own following with client_code prefix (ex: ABCD001, ... )