Forked from harishrkpatel/2018_12_29_113449_create_csv_data_table.php
Last active
January 21, 2021 19:49
-
-
Save johnpbloch/f2a387b4cfd8e2ab4c3ff2991a441527 to your computer and use it in GitHub Desktop.
Backpack For Laravel Import Functionality
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
use Illuminate\Support\Facades\Schema; | |
use Illuminate\Database\Schema\Blueprint; | |
use Illuminate\Database\Migrations\Migration; | |
class CreateCsvDataTable extends Migration | |
{ | |
/** | |
* Run the migrations. | |
* | |
* @return void | |
*/ | |
public function up() | |
{ | |
Schema::create('csv_data', function (Blueprint $table) { | |
$table->bigIncrements('id'); | |
$table->string('created_by', 100)->nullable(); | |
$table->string('last_upd_by', 100)->nullable(); | |
$table->softDeletes(); | |
$table->timestamps(); | |
// now add the actual fields. | |
$table->string('csv_filename', 64)->default(null)->nullable(); | |
$table->longText('csv_data')->default(null)->nullable(); | |
}); | |
} | |
/** | |
* Reverse the migrations. | |
* | |
* @return void | |
*/ | |
public function down() | |
{ | |
Schema::dropIfExists('csv_data'); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace App\Models; | |
use Illuminate\Database\Eloquent\Model; | |
/** | |
* App\Models\CsvData | |
* | |
* @property int $id | |
* @property string|null $created_by | |
* @property string|null $last_upd_by | |
* @property string|null $deleted_at | |
* @property \Illuminate\Support\Carbon|null $created_at | |
* @property \Illuminate\Support\Carbon|null $updated_at | |
* @property string|null $csv_filename | |
* @property string|null $csv_data | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData newModelQuery() | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData newQuery() | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData query() | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData whereCreatedAt($value) | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData whereCreatedBy($value) | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData whereCsvData($value) | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData whereCsvFilename($value) | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData whereDeletedAt($value) | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData whereId($value) | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData whereLastUpdBy($value) | |
* @method static \Illuminate\Database\Eloquent\Builder|CsvData whereUpdatedAt($value) | |
* @mixin \Eloquent | |
*/ | |
class CsvData extends Model | |
{ | |
protected $table = 'csv_data'; | |
protected $fillable = ['csv_filename', 'csv_data']; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
@extends('common.import_template') | |
@section('import_content') | |
<div class="col-sm-12 col-md-8"> | |
<div class="card"> | |
<div class="card-body"> | |
<form class="form-horizontal" method="POST" | |
action="{{ route('admin.'.strtolower(str_replace(' ', '', $crud->entity_name)).'.importParse') }}" | |
enctype="multipart/form-data"> | |
{{ csrf_field() }} | |
<div class="form-group row{{ $errors->has('csv_file') ? ' has-error' : '' }}"> | |
<label class="col-md-4 col-form-label" for="csv_file">CSV file to import</label> | |
<div class="col-md-8"> | |
<input id="csv_file" type="file" name="csv_file" required> | |
@if ($errors->has('csv_file')) | |
<span class="help-block"> | |
<strong>{{ $errors->first('csv_file') }}</strong> | |
</span> | |
@endif | |
</div> | |
</div> | |
<div class="form-group row"> | |
<label class="col-md-4 col-form-label">Instructions:</label> | |
<div class="col-md-8"> | |
<ol> | |
<li>Upload a CSV in the following format: <a href="{{ route('admin.'.strtolower(str_replace(' ', '', $crud->entity_name)).'.importFormatDownload') }}">Download</a> | |
</li> | |
@foreach($instructions as $idx => $instruction) | |
<li>{{ $instruction }}</li> | |
@endforeach | |
</ol> | |
</div> | |
</div> | |
<div class="form-group row"> | |
<div class="col-md-8 col-md-offset-4"> | |
<button type="submit" class="btn btn-primary">Parse CSV</button> | |
</div> | |
</div> | |
</form> | |
</div> | |
</div> | |
</div> | |
@endsection |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
@extends('common.import_template') | |
@php($breadcrumb_name='Import Mapping') | |
@php($crud->setSubheading('Import Mapping for '.ucfirst($crud->entity_name_plural))) | |
@section('import_content') | |
<form class="form-horizontal" method="POST" action="{{ route('admin.'.strtolower(str_replace(' ', '', $crud->entity_name)).'.importProcess') }}"> | |
{{ csrf_field() }} | |
<input type="hidden" name="csv_data_file_id" value="{{ $csv_data_file->id }}"/> | |
<div style="overflow: auto; width: 100%;"> | |
<table class="box table table-striped table-hover responsive nowrap m-t-0" style="width: 100%"> | |
@foreach ($csv_data as $row) | |
<tr> | |
@foreach ($row as $key => $value) | |
<td>{{ $value }}</td> | |
@endforeach | |
</tr> | |
@endforeach | |
<tr> | |
@foreach ($csv_data[0] as $key => $value) | |
<td> | |
<select name="fields[{{ $key }}]" class="form-control select2"> | |
<option value="">Select mapping</option> | |
@foreach ($import_fields as $key => $import_field) | |
<option @if ($import_field['name']===$value || $import_field['label']===$value) selected @endif value="{{ $import_field['name'] }}">{{ $import_field['label'] }} | |
</option> | |
@endforeach | |
</select> | |
</td> | |
@endforeach | |
</tr> | |
</table> | |
</div> | |
<div style="float: left;"> | |
<a href="{{ route('admin.'.strtolower(str_replace(' ', '', $crud->entity_name)).'.import') }}" class="btn btn-primary">Back</a> | |
</div> | |
<div style="float: right;"> | |
<button type="submit" class="btn btn-primary">Import Data</button> | |
</div> | |
</form> | |
@endsection | |
@section('after_styles') | |
<!-- DATA TABLES --> | |
<link href="{{ asset('vendor/adminlte/bower_components/select2/dist/css/select2.min.css') }}" rel="stylesheet" type="text/css"/> | |
<link href="https://cdnjs.cloudflare.com/ajax/libs/select2-bootstrap-theme/0.1.0-beta.10/select2-bootstrap.min.css" rel="stylesheet" type="text/css"/> | |
<style> | |
.select2-container .select2-selection--single { | |
box-sizing: border-box; | |
cursor: pointer; | |
display: block; | |
height: 35px; | |
user-select: none; | |
-webkit-user-select: none; | |
} | |
.select2-container { | |
width: auto !important; | |
} | |
</style> | |
<!-- CRUD LIST CONTENT - crud_list_styles stack --> | |
@stack('crud_fields_styles') | |
@endsection | |
@section('after_scripts') | |
<script src="{{ asset('vendor/adminlte/bower_components/select2/dist/js/select2.min.js') }}"></script> | |
<style type="text/css"> | |
.select2-selection__clear::after { | |
content: ' Clear'; | |
} | |
</style> | |
<script> | |
$(document).ready(function () { | |
$('select.select2').select2(); | |
}); | |
</script> | |
<!-- CRUD LIST CONTENT - crud_list_scripts stack --> | |
@stack('crud_fields_scripts') | |
@endsection |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
@extends('common.import_template') | |
@php($breadcrumb_name='Import Result') | |
@php($crud->setSubheading('Import Results for '.ucfirst($crud->entity_name_plural))) | |
@section('import_content') | |
@if (empty($errors)) | |
<div class="alert alert-success col-12" role="alert">Imported successfully</div> | |
@else | |
<div class="alert alert-danger col-12" role="alert">Some rows had errors:</div> | |
<table class="table table-responsive-sm table-bordered table-striped col-12"> | |
<tr> | |
<td>Row Number</td> | |
<td>Error Messages</td> | |
</tr> | |
@foreach ($errors as $idx_1 => $error) | |
<tr> | |
<td>{{ $error['row_number'] }}</td> | |
<td> | |
<ul style="padding-left: 15px;"> | |
@foreach($error['errors'] as $idx_2 => $e) | |
<li>{{ $e }}</li> | |
@endforeach | |
</ul> | |
</td> | |
</tr> | |
@endforeach | |
</table> | |
@endif | |
<div class="col-md-3"> | |
<a href="{{ route('admin.'.strtolower(str_replace(' ', '', $crud->entity_name)).'.import') }}" class="btn btn-primary btn-block">Import More</a> | |
</div> | |
<div class="col-md-3 offset-md-6"> | |
<a href="{{ url($crud->route) }}" class="btn btn-secondary btn-block">Finished</a> | |
</div> | |
@endsection |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
@extends('backpack::blank') | |
@php | |
$breadcrumbs = [ | |
trans('backpack::crud.admin') => url(config('backpack.base.route_prefix'), 'dashboard'), | |
$crud->entity_name_plural => url($crud->route), | |
($breadcrumb_name ?? 'Import') => false, | |
]; | |
@endphp | |
@section('content') | |
<div class="container-fluid"> | |
<h2> | |
<span class="text-capitalize">{!! $crud->getHeading() ?? $crud->entity_name_plural !!}</span> | |
<small>{!! $crud->getSubheading() ?? 'Import '.$crud->entity_name_plural !!}</small> | |
</h2> | |
<div class="row"> | |
@yield('import_content') | |
</div> | |
</div> | |
@endsection |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace App\Services; | |
use Illuminate\Http\Request; | |
use App\Models\CsvData; | |
use Illuminate\Support\Facades\Route; | |
use Illuminate\Support\Facades\Validator; | |
/** | |
* | |
* Include this trait in any CrudController and make provision for enabling configuration driven import functionality on that entity. | |
* To enable import on any entity the following steps need to be undertaken. | |
* | |
* 1. use this trait in the respective entity controller. | |
* | |
* 2. Override the following methods. | |
* a) importSampleFilename() | |
* b) importInstructions() | |
* c) importValidationRules() | |
* d) importValidationMessages() | |
* e) importCreate($entity) | |
* | |
* Instructions of how to override each of these methods is provided against each methods documentation. | |
* | |
* 3. Once you have imported this trait it allows you to add the following routes. | |
* a) GET entityName/import | |
* b) POST entityName/importParse | |
* c) POST entityName/importProcess | |
* d) GET entityName/importFormatDownload | |
* | |
* These routes get mapped to the methods with the same names which are present in this trait. | |
* | |
* 4. Optionally to have a more fine grained control over the import process you can override the following two methods. | |
* a) beforeImport($entities) | |
* b) afterImport($entities) | |
* | |
* Again both these methods specify the exact nature of the functionality they are exposing. | |
* | |
* Trait ImportAwareTrait | |
* @package App\Services | |
*/ | |
trait ImportAwareTrait | |
{ | |
protected function setupImportRoutes($segment, $routeName, $controller) | |
{ | |
Route::get($segment . '/import', [ | |
'as' => "admin.$routeName.import", | |
'uses' => "$controller@import", | |
'operation' => 'import', | |
]); | |
Route::get($segment . '/import/download-format', [ | |
'as' => "admin.$routeName.importFormatDownload", | |
'uses' => "$controller@importFormatDownload", | |
'operation' => 'import', | |
]); | |
Route::post($segment . '/import/parse', [ | |
'as' => "admin.$routeName.importParse", | |
'uses' => "$controller@importParse", | |
'operation' => 'import', | |
]); | |
Route::post($segment . '/import/process', [ | |
'as' => "admin.$routeName.importProcess", | |
'uses' => "$controller@importProcess", | |
'operation' => 'import', | |
]); | |
} | |
/** | |
* The entity should override this method to provide a new name for the sample CSV which will be | |
* downloaded during the first step. | |
* | |
* Recommended To Override | |
* | |
* @return string | |
*/ | |
protected function importSampleFilename() | |
{ | |
return 'export_sample.csv'; | |
} | |
/** | |
* Optionally you can override this method to provide a completely different view for the first step of the import process. | |
* | |
* Optional To Override | |
* | |
* @return string | |
*/ | |
protected function importView() | |
{ | |
return 'common/import'; | |
} | |
/** | |
* You will never have to override this, however left this as a convenience just incase someone wants to override this method. | |
* | |
* Optional To Override | |
* | |
* @return string | |
*/ | |
protected function importParseView() | |
{ | |
return 'common/import_parse'; | |
} | |
/** | |
* This view represents the last step of the import process, again optionally you can override this method to customise the | |
* last step of the import process. | |
* | |
* Optional To Override | |
* | |
* @return string | |
*/ | |
protected function importProcessView() | |
{ | |
return 'common/import_process'; | |
} | |
/** | |
* Override this method to provide an array of instructions. These instructions are then displayed in step 1 | |
* as a numbered list. The first instruction is always the default instruction to download the sample csv. | |
* | |
* Required To Override | |
* | |
* @return array | |
*/ | |
protected function importInstructions() | |
{ | |
return []; | |
} | |
/** | |
* | |
* Override this method to provide an array of validation rules. The syntax to follow is the same as the syntax we | |
* use for defining Laravel validation rules. | |
* return [ | |
* 'user_id' => 'required', | |
* 'package_id' => 'required', | |
* 'company_id' => 'required', | |
* ]; | |
* | |
* Required To Override | |
* | |
* @return array | |
* | |
*/ | |
protected function importValidationRules() | |
{ | |
return []; | |
} | |
/** | |
* Override this method to provide an array of validation rule failure messages. A sample return could be | |
* | |
* return [ | |
* 'user_id.required' => 'Specifying a user is mandatory when creating an appointment, please check if you have specified a valid name, email, date of birth (yyyy-mm-dd format) & contact number of the user.', | |
* 'package_id.required' => 'Specifying a package name is mandatory when creating an appointment, please check if you have specified a valid package name.', | |
* 'company_id.required' => 'Specifying a company name is mandatory when creating an appointment, please check if you have specified a valid company name.', | |
* ]; | |
* | |
* Required To Override | |
* | |
* @return array | |
*/ | |
protected function importValidationMessages() | |
{ | |
return []; | |
} | |
/** | |
* | |
* After all foreign keys have been resolved and validation rules have run successfully for all rows, this method | |
* is called with an array representing the entity which is to be created. | |
* | |
* Required To Override | |
* | |
* @param array $entity | |
* @return mixed You need to return the created entity object, generally you can return the result of calling the create method on the entity class. | |
*/ | |
protected abstract function importCreate($entity); | |
/** | |
* Takes an array of entities representing all rows in the CSV as the argument. | |
* Each entity passed to this method is actually an array object representing the fully resolved object where all validations have passed and foreign keys resolved. | |
* You can optionally override this method to do any processing that is required to be done across all rows which are being imported. | |
* To do pre-create activities on a per entity basis you can simply write the code when implemeting importCreate() method. | |
* | |
* Optional To Override | |
* | |
* @param array $entities | |
*/ | |
protected function beforeImport($entities) | |
{ | |
} | |
/** | |
* Takes an array of entities representing all rows in the CSV as the argument. Each entity passed to this method is actually an instance of the entity class. | |
* You can optionally override this method to do any processing that is required to be done across all rows which are being imported. | |
* To do post-create activities on a per entity basis you can simply write the code when implemeting importCreate() method. | |
* | |
* Optional To Override | |
* | |
* @param array $entities | |
*/ | |
protected function afterImport($entities) | |
{ | |
} | |
/** | |
* Action method linked to the GET <entityname>/import route. | |
* Simply renders the first step which allows one to upload hte CSV file & see the import instructions. | |
* | |
* @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View | |
*/ | |
public function import() | |
{ | |
return view($this->importView(), [ | |
'crud' => $this->crud, | |
'instructions' => $this->importInstructions(), | |
]); | |
} | |
/** | |
* Action method linked to the GET <entityname>/importFormatDownload route. | |
* Generates a CSV and streams the response to the browser. The CSV is generated based on cofiguration provided | |
* in the setup() method of the entity controller. | |
* | |
* @return \Symfony\Component\HttpFoundation\StreamedResponse | |
*/ | |
public function importFormatDownload() | |
{ | |
// figure out the importable fields. | |
$importFields = $this->getImportableFields(); | |
$headers = array( | |
"Content-type" => "text/csv", | |
"Content-Disposition" => "attachment; filename=" . $this->importSampleFilename(), | |
"Pragma" => "no-cache", | |
"Cache-Control" => "must-revalidate, post-check=0, pre-check=0", | |
"Expires" => "0" | |
); | |
$columns = []; | |
foreach ($importFields as $idx_1 => $importField) { | |
$columns[] = $importField['label']; | |
} | |
$callback = function () use ($columns) { | |
$file = fopen('php://output', 'w'); | |
fputcsv($file, $columns); | |
fclose($file); | |
}; | |
return response()->stream($callback, 200, $headers); | |
} | |
/** | |
* Action method linked to the POST <entityname>/importParse route. | |
* This method consumes the file upload, saves it to the CsvData entity which stores the uploaded file in interim storage. | |
* This method then renders a view which allows the user to do the mapping between the csv header fields & the field names | |
* as specified in the setup() method of the CrudController. | |
* | |
* @param Request $request | |
* @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View | |
*/ | |
public function importParse(Request $request) | |
{ | |
$path = $request->file('csv_file')->getRealPath(); | |
$data = array_map('str_getcsv', file($path)); | |
$csvDataFile = CsvData::create([ | |
'csv_filename' => $request->file('csv_file')->getClientOriginalName(), | |
'csv_data' => json_encode(mb_convert_encoding($data, 'UTF-8', 'auto')) | |
]); | |
$csvData = array_slice($data, 0, 10); | |
return view($this->importParseView(), [ | |
'import_fields' => $this->getImportableFields(), | |
'csv_data' => $csvData, | |
'csv_header' => $csvData[0], | |
'csv_data_file' => $csvDataFile, | |
'crud' => $this->crud, | |
]); | |
} | |
/** | |
* | |
* Action method linked to the POST <entityname>/importProcess route. | |
* Here is where the bulk of the logic of importing the data is written. This method uses the mapping provided in the | |
* previous step and uses the CSV data stored earlier in the CsvData entity. | |
* It then runs all resolutions / validations as configured. | |
* Finally the resolved entities are passed to the importCreate() method. The lifecycle methods are also invoked | |
* while running this method. | |
* | |
* @param Request $request | |
* @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View | |
*/ | |
public function importProcess(Request $request) | |
{ | |
// figure out the importable fields. | |
$importFields = $this->getImportableFields(); | |
// load the data stored in the database. | |
$requestFields = $request->fields; | |
$data = CsvData::find($request->csv_data_file_id); | |
$csv_data = json_decode($data->csv_data, true); | |
$entities = []; | |
foreach ($csv_data as $idx_1 => $row) { | |
if ($idx_1 == 0) { | |
continue; | |
} | |
$entity = []; | |
foreach ($importFields as $idx_2 => $importField) { | |
$t = array_search($importField['name'], $requestFields); | |
if ($t !== false) { | |
$entity[$importField['name']] = isset($row[$t]) ? $row[$t] : null; | |
} | |
} | |
$entities[] = $entity; | |
} | |
// resolve all the foreign keys. | |
foreach ($entities as $idx_1 => &$entity) { | |
foreach ($importFields as $idx_2 => $importField) { | |
if (isset($importField['import_resolver'])) { | |
$importField['import_resolver']($entity); | |
} | |
} | |
} | |
// run validations. | |
$errors = []; | |
foreach ($entities as $idx => &$entity) { | |
$rules = $this->importValidationRules(); | |
$messages = $this->importValidationMessages(); | |
$validator = Validator::make($entity, $rules, $messages); | |
if ($validator->fails()) { | |
$errors[] = [ | |
'row_number' => $idx + 1, | |
'errors' => $validator->errors()->all() | |
]; | |
} | |
} | |
// create the user appointments. | |
$entityObjects = []; | |
$this->beforeImport($entities); | |
if (empty($errors)) { | |
foreach ($entities as $idx => &$entity) { | |
$entityObjects[] = $this->importCreate($entity); | |
} | |
} | |
$this->afterImport($entityObjects); | |
return view($this->importProcessView(), [ | |
'crud' => $this->crud, | |
'errors' => $errors, | |
]); | |
} | |
/** | |
* | |
* Used internally to fetch a list of importable fields. This method uses the CrudPanel metadata and infers | |
* importable fields based on the "importable => true" configuration attribute. | |
* | |
* @return array | |
*/ | |
private function getImportableFields() | |
{ | |
$createFields = $this->crud->getFields('create'); | |
$importFields = []; | |
foreach ($createFields as $idx_1 => $createField) { | |
if (isset($createField['importable']) && $createField['importable']) { | |
if (isset($createField['importable_fields'])) { | |
foreach ($createField['importable_fields'] as $idx_2 => $nested) { | |
$importFields[] = $nested; | |
} | |
} else { | |
$importFields[] = $createField; | |
} | |
} | |
} | |
return $importFields; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment