Entschuldigung an meine deutschen Leser, diesen Betrag schreibe ich in Englisch, da er sicher auch für „nicht-Deutschsprachige“ Leser interessant ist.
—-
I would like to show you, how to generate a universal CSV-Import-Featore for Sonata-Admin / Symfony 2.
Works at my system without any troubles.
This solution can be used to import the CSV typically generated from the Sonata-Admin Screens. So you can modify the data and load it back to your DB using the GUI.
This feature uses the CSV-Reader and DB-Writer-Freatures of the ddeboer/data-import-bundle Data Bundle, therefore you have to add this line to „composer.json“:
"require": {
[...]
"ddeboer/data-import-bundle": "dev-master"
You can find more info about this bundle here.
I have created a standalone block that can be displayed in the Dash-Screen of Sonata-Admin to easily import the given Data.
Step 1: Helper-Class
To easily add new tables to the import-feature, I’ve created a helper class that tells me, what could be imported and to which table to import:
<?php
// Fungus/ShortyBundle/Helper/CSVTypes.php
namespace FungusShortyBundleHelper;
class CSVTypes {
const RAETSEL = 1;
const SPRUECHE = 2;
public static function getTypes() {
return array(
self::RAETSEL => 'Rätsel',
self::SPRUECHE => 'Sprueche',
);
}
public static function getTypesAndIds() {
$all=self::getTypes();
$return=array();
foreach($all as $key=>$value) {
$return[]=array("id"=>$key,"title"=>$value);
}
return $return;
}
public static function getNameOfType($type) {
$allTypes=self::getTypes();
if (isset($allTypes[$type])) return $allTypes[$type];
return " - Unknown Type -";
}
public static function getEntityClass($type) {
switch ($type) {
case self::RAETSEL: return "FungusShortyBundle:Raetsel";
case self::SPRUECHE: return "FungusShortyBundle:Spruch";
default: return false;
}
}
public static function existsType($type) {
$allTypes=self::getTypes();
if (isset($allTypes[$type])) return true;
return false;
}
}
To add another Table, just create a new constant, add a corresponding line to getTypes and add the corresponding Entity-Class to getEntitityClass.
Step 2: The Block for the Dashboard:
{% extends 'SonataBlockBundle:Block:block_base.html.twig' %}
{% block block %}
<table>
<thead>
<tr>
<th colspan="3">CSV-Import</th>
</tr>
</thead>
<tbody>
<tr>
<td>Import File directly:</td>
<td>
<form action="{{ path('shortyImportCSV') }}" method="post" enctype="multipart/form-data" >
<select size="1" name="fileType">
{% for oneCSV in allTypes %}
<option value="{{ oneCSV.id }}">{{ oneCSV.title }}</option>
{% endfor %}
</select> <br />
<input type="file" name="csvFile" /> <br />
<input type="submit" value="Upload" />
</form>
</td>
</tr>
</tbody>
</table>
{% endblock %}
This file was saved as src/Fungus/ShortyBundle/Resources/views/Block/block_importCSV.html.twig
As you can see, you need a route called shortyImportCSV to make this working. Here my route:
shortyImportCSV:
pattern: /admin/csv/import
defaults: { _controller: FungusShortyBundle:CSV:importFile }
Be sure to set the path (pattern) below /admin to make sure that the Sonata-Admin Firewall takes care of the access. Otherwise also no user-account is passed to your controller!
Step 3: Show up the block
This has to be done in the config.yml. It is neccessary to define a new service that calls the block:
sonata_block:
default_contexts: [cms]
blocks:
sonata.admin.block.admin_list:
contexts: [admin]
fungus.block.service.importcsv: ~
sonata_admin:
security:
handler: sonata.admin.security.handler.acl
# acl security information
information:
GUEST: [VIEW, LIST]
STAFF: [EDIT, LIST, CREATE]
EDITOR: [OPERATOR, EXPORT]
ADMIN: [MASTER]
# permissions not related to an object instance and also to be available when objects do not exist
# the DELETE admin permission means the user is allowed to batch delete objects
admin_permissions: [CREATE, LIST, DELETE, UNDELETE, EXPORT, OPERATOR, MASTER]
# permission related to the objects
object_permissions: [VIEW, EDIT, DELETE, UNDELETE, OPERATOR, MASTER, OWNER]
title: fungus test
dashboard:
blocks:
# display a dashboard block
- { position: left, type: sonata.admin.block.admin_list }
- { position: right, type: fungus.block.service.importcsv }
services:
fungus.block.service.importcsv:
class: FungusShortyBundleBlockImportCSVService
arguments: [ "fungus.block.service.uploadCSV", @templating, @service_container ]
tags:
- { name: sonata.block }
As you can see, we also need a „Block-Class“ that is called to display the new block, here it is:
<?php
// src/Fungus/ShortyBundle/Block/ImportCSVService.php
namespace FungusShortyBundleBlock;
use SymfonyComponentHttpFoundationResponse;
use SonataAdminBundleFormFormMapper;
use SonataAdminBundleValidatorErrorElement;
use SonataBlockBundleModelBlockInterface;
use SonataBlockBundleBlockBaseBlockService;
use SonataBlockBundleBlockBlockContextInterface;
use FungusShortyBundleHelperCSVTypes;
class ImportCSVService extends BaseBlockService
{
private $container = null;
public function __construct($name, $templating, $container=null)
{
parent::__construct($name, $templating);
$this->container = $container;
}
public function getName()
{
return 'Import CSV';
}
public function getDefaultSettings()
{
return array();
}
public function validateBlock(ErrorElement $errorElement, BlockInterface $block)
{
}
public function buildEditForm(FormMapper $formMapper, BlockInterface $block)
{
}
public function execute(BlockContextInterface $blockContext, Response $response = null)
{
// merge settings
$settings = array_merge($this->getDefaultSettings(), $blockContext->getSettings());
$curBlock='FungusShortyBundle:Block:block_importCSV.html.twig';
if (!$this->container->get('security.context')->isGranted("ROLE_SUPER_ADMIN")) {
$curBlock='FungusShortyBundle:Block:block_empty.html.twig';
}
return $this->renderResponse($curBlock, array(
'block' => $blockContext->getBlock(),
'allTypes' => CSVTypes::getTypesAndIds(),
'settings' => $settings
), $response);
}
}
I’ve added some very-simple security-enhancement: only the ROLE_SUPER_ADMIN’s are able to see this block. The File block_empty.html.twig is just an empty textfile, therefore „nothing“ will be displayed if not in ROLE_SUPER_ADMIN.
Step 4: the Input Controller
Looks like we are nearly finished yet. We just have to add the controller that does the whole work. Thats all.
<?php
// src/Fungus/ShortyBundle/Controller/CSVController.php
namespace FungusShortyBundleController;
use SymfonyBundleFrameworkBundleControllerController;
use SensioBundleFrameworkExtraBundleConfigurationRoute;
use SensioBundleFrameworkExtraBundleConfigurationTemplate;
use SymfonyComponentHttpFoundationRequest;
use SymfonyComponentHttpFoundationResponse;
use FungusShortyBundleHelperCSVTypes;
use DdeboerDataImportReaderCsvReader;
use DdeboerDataImportSourceStreamSource;
use DdeboerDataImportWorkflow;
use DdeboerDataImportWriterDoctrineWriter;
class CSVController extends Controller
{
public function importFileAction(Request $request) {
// Get FileId to "import"
$param=$request->request;
$fileId=(int)trim($param->get("fileId"));
$curType=trim($param->get("fileType"));
$uploadedFile=$request->files->get("csvFile");
// if upload was not ok, just redirect to "shortyStatWrongPArameters"
if (!CSVTypes::existsType($curType) || $uploadedFile==null) return $this->redirect($this->generateUrl('shortyStatWrongParameters'));
// generate dummy dir
$dummyImport=getcwd()."/dummyImport";
$fname="directly.csv";
$filename=$dummyImport."/".$fname;
@mkdir($dummyImport);
@unlink($filename);
// move file to dummy filename
$uploadedFile->move($dummyImport,$fname);
echo "Starting to Import ".$filename.", Type: ".CSVTypes::getNameOfType($curType)."<br />n";
// open file
$source = new StreamSource($filename);
if ($source===false) die("Can't open filestream $filename");
$file = $source->getFile();
if ($file===false) die("Can't open file $filename");
// Create and configure the reader
$csvReader = new CsvReader($file,",");
if ($csvReader===false) die("Can't create csvReader $filename");
$csvReader->setHeaderRowNumber(0);
// this must be done to import CSVs where one of the data-field has CRs within!
$file->setFlags(SplFileObject::READ_CSV |
SplFileObject::SKIP_EMPTY |
SplFileObject::READ_AHEAD);
// Set Database into "nonchecking Foreign Keys"
$em=$this->getDoctrine()->getManager();
$em->getConnection()->exec("SET FOREIGN_KEY_CHECKS=0;");
// Create the workflow
$workflow = new Workflow($csvReader);
if ($workflow===false) die("Can't create workflow $filename");
$curEntityClass=CSVTypes::getEntityClass($curType);
$writer = new DoctrineWriter($em, $curEntityClass);
$writer->setTruncate(false);
$entityMetadata=$em->getClassMetadata($curEntityClass);
$entityMetadata->setIdGeneratorType(DoctrineORMMappingClassMetadata::GENERATOR_TYPE_NONE);
$workflow->addWriter($writer);
$workflow->process();
// RESetting Database Check Status
$em->getConnection()->exec("SET FOREIGN_KEY_CHECKS=1;");
// After successfully import, some files need special treatment --> Reset some DB fields
if ($curType==CSVTypes::SPRUECHE) {
$q=$em->createQuery("UPDATE FungusShortyBundle:Spruch s
SET s.dupeChecked = false");
$q->execute();
}
return $this->render('FungusShortyBundle:CSV:csv_import.html.twig');
}
}
This controller directly writes to screen (echo). The given twig-template is just a „going back“.
Currently also very little security-checks are done.
Here is the content of csv_import.html.twig:
<hr>
Import done!<br>
<a href='{{ path('FungusMain') }}'>Back</a>
As you can see, the twig for that is very very basic. FungusMain is mainly the root path, thats all.
I did dome tricks with the input-controller:
- $file->setFlags … –> This has to be done to be able to import CRs within one field
- $em->getConnection()->exec(„SET FOREIGN_KEY_CHECKS=0;“); –> This has to be done to be able to overwrite a table that is referenced at another table. Don’t know it that works with an DB other than mySQL
- $entityMetadata->setIdGeneratorType(DoctrineORMMappingClassMetadata::GENERATOR_TYPE_NONE); –> This is neccessary to define the ID-Field of the row. See more later.
More about the ID-Field or the setIdGeneratorType:
Normally you can not set the ID of an entity. The DB itself picks out the next free id and uses that. So if you import a csv-file that has also the ID-Field given the following could occur:
- The record/entity with the given ID exist –> The Ddeboer-Bundle will just overwritte that entity and all is good.
- The record/entity with the given ID _DOES_NOT_ exist –> The Dbeboer-Bundle will let the DB choose a new ID.
So if you import a CSV more than one time that has IDs given but that IDs do not exist in the DB, you will get dublicate entries.
Therefore something described here has to be done:
- this setIdGeneratorType has been done by myself
- you have to add a „setId“-Method as described here to any of your entities you are enabling to import.
Have fun 🙂
(Comments are welcome 🙂