Symfony Profiler

<?php
namespace App\Command\Export;
use App\Entity\Export\Export;
use App\Entity\Notes\Note;
use App\Service\Export\ComptabiliteService;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\Console\Command\Command;
//use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Question\ConfirmationQuestion;
use Symfony\Component\Console\Question\Question;
use Symfony\Component\Console\Question\ChoiceQuestion;
use Symfony\Component\Console\Input\ArrayInput;
use Symfony\Component\Console\Helper\ProgressBar;
use Symfony\Component\HttpFoundation\Response;
class exportCommand extends Command
{
protected static $defaultName = 'export:creer';
private EntityManagerInterface $entityManager;
private ComptabiliteService $comptabiliteService;
private string $publicPath;
public function __construct( EntityManagerInterface $entityManager, ComptabiliteService $comptabiliteService, string $publicPath)
{
parent::__construct();
$this->entityManager = $entityManager;
$this->comptabiliteService = $comptabiliteService;
$this->publicPath = $publicPath;
}
private $regenerate = false;
protected function configure()
{
$this
->setDescription('creer un export')
->addArgument('objetId', InputArgument::OPTIONAL, 'The id of the export.')
->addArgument('regenerate', InputArgument::OPTIONAL, 'Regenerate ?');
}
/**
* @throws \Doctrine\DBAL\Exception
*/
protected function execute(InputInterface $input, OutputInterface $output): int
{
set_time_limit(6000);
if ( ! empty($input->getArgument('regenerate'))) {
$this->regenerate = true;
}
$conn = $this->entityManager->getConnection();
$conn->getConfiguration()->setSQLLogger(null);
//$output->writeln('Mise à jour des pumps');
$repo_objet = $this->entityManager->getRepository(Export::class);
$exportEncours = $repo_objet->exportEnCours();
if (count($exportEncours)) {
foreach ($exportEncours as $ec) {
//$output->writeln('EC:'.$ec->getPid());
$pid = $ec->getPid();
if ( ! file_exists("/proc/$pid")) {
$ec->setEchec(true);
$this->entityManager->persist($ec);
$this->entityManager->flush();
// $output->writeln('GGGG');
}
}
}
$repo_note = $this->entityManager->getRepository(Note::class);
//$imports = $repo_import->importEnAttente();
//$export_attente = $repo_objet->findBy(array("id"=>60));
//$objet = $repo_objet->find($input->getArgument('objetId'));
if (empty($input->getArgument('objetId'))) {
$export_attente = $repo_objet->exportEnAttente();
} else {
$export_attente = [$repo_objet->find($input->getArgument('objetId'))];
}
if (count($export_attente) > 0) {
foreach ($export_attente as $objet) {
$pid = getmypid();
$objet->setDateDebutExecution(new \Datetime());
$date_debut_execution = new \Datetime();
$cronId = $objet->getId();
$conn->update('export__export', ['execute' => 1, 'pid' => $pid, 'date_debut_execution' => $date_debut_execution->format("Y-m-d H:i:s")], ['id' => $objet->getId()]);
/*
$note = new Note;
$note->setMessage('Un export est en cours depuis plus de 5min!');
if(is_object($objet->getUtilisateur())) $note->setUtilisateur($objet->getUtilisateur());
$date = new \DateTime();
$date->modify("+5 minutes");
$note->setDateRappel($date);
$note->setPrive(true);
$this->entityManager->persist($note);
$this->entityManager->flush();
$noteId = $note->getId();
*/
$count = 0;
$entityName = $objet->getEntityName();
$entity = $objet->getEntity();
$format = $objet->getFormat();
$champs = [];
foreach ($objet->getChampsAexporter() as $champsAexporter) {
$champs[] = $champsAexporter->getName();
}
$a = 'Export/'.$entityName.'/'.date("Y").'/'.date("m").'/'.date("d").'/export.'.$format;
$dir = $this->publicPath.'/Export/'.$entityName.'/'.date("Y").'/'.date("m").'/'.date("d");
$output->writeln($dir);
//$dir .= '/';
if ( ! is_dir($dir)) {
$output->writeln('creation du repertoire : '.$dir);
mkdir($dir, 0775, true);
chown($dir, 'www-data');
//chown($dir, 'www');
}
$dir = realpath($dir);
chown($dir, 'www-data');
chmod($dir, 0775);
//$file = $dir.'/export.'.$format;
$file = $dir.'/export_'.$objet->getId().'.'.$format;
$output->writeln($file);
$retour = $this->ExportProcess($champs, $entityName, $entity, $format, $file, $output, $note = "", $objet);
$date_debut_execution = new \Datetime();
$conn->update('export__export', ['execute' => 1, 'date_fin' => $date_debut_execution->format("Y-m-d H:i:s")], ['id' => $cronId]);
//$url = $this->getContainer()->get('router')->generate('dtc_inventaire_modifier', array('id'=>$inventaire->getId()));
/*
$note = $repo_note->find($noteId);
$note->setMessage('Votre export est terminé : <a href="/'.$a.'">'.$a.'</a>.');
$date = new \DateTime();
$date->modify("+1 minutes");
$note->setDateRappel($date);
$dateFin = new \Datetime();
$objet->setDateFin($dateFin);
$this->entityManager->persist($objet);
$this->entityManager->persist($note);
$this->entityManager->flush();
*/
}
}
return 1;
}
public function ExportProcess($champs, $entityName = 'article', $entity, $format, $file, $output, $note, $objet)
{
set_time_limit(6000);
if ($entityName == 'comptabilite') {
$this->comptabiliteService->genererExport($objet, $this->regenerate, $output);
$output->writeln('fin comptabilite exportCommand');
return;
} elseif ($entityName == "equivalence") {
$where = '';
$resultats = $this->getJoin($champs);
$select2 = "";
foreach ($resultats[1] as $subSelect) {
$select2 .= $subSelect.",";
}
$select2 .= ",";
$select2 = trim($select2, ",,");
$requete = <<<DQL
SELECT $select2 FROM App\Entity\Articles\Article e
JOIN e.refsEquivalentes re
LEFT JOIN e.marque m
LEFT JOIN re.marque mre
DQL;
//echo $requete;
$query = $this->entityManager->createQuery($requete);
} elseif ($entityName == "emplacementLibre") {
$resultats = $this->getJoin($champs);
$requete = <<<DQL
SELECT nt.id,e.libelle,sum(e.stock) as total
FROM App\Entity\Rangements\Emplacement e
JOIN e.niveauTrois nt
WHERE e.dateSuppression is null and e.niveauTrois is not null
and e.visibilite = 1
group by nt.id
having total = 0
DQL;
//$requete = "select n.codeBarres FROM DTCRangementsBundle:NiveauTrois n where n.id NOT IN (select nt.id FROM DTCRangementsBundle:Emplacement e JOIN e.niveauTrois nt)";
//$requete = "select n.codeBarres FROM DTCRangementsBundle:NiveauTrois n where n.id IN (select nt.id FROM DTCRangementsBundle:Emplacement e JOIN e.niveauTrois nt)";
$query = $this->entityManager->createQuery($requete);
//$output->writeln($query->getSql());
} elseif ($entityName == "exportCorrectionPump") {
$where = '';
$resultats = $this->getJoin($champs);
/*
$select = '';
foreach($resultats[2] as $res){
if($select != '') $select .= ', ';
$select .= $res;
}
*
*/
//$requete = 'SELECT a.pump, ca.prix_achat_net FROM article__condition_achat ca'
$requete = 'SELECT e.reference, e.pump, e.cpump as cump, ca.prixAchatNet as prixAchatNetDevise, ca.coefficientConditionnement, d.tauxChange
FROM App\Entity\Articles\ConditionAchat ca'
//$requete = 'SELECT '.$select.' FROM App\Entity\Articles\ConditionAchat ca'
//.' LEFT JOIN article__article a'
//.' LEFT JOIN App\Entity\Articles\Article a WITH a.id = ca.article_id'
.' JOIN ca.article e'
.' JOIN ca.fournisseur f'
.' JOIN f.devise d'
.' WHERE ca.defaut = 1';
$fournisseur = $objet->getFournisseur();
if (is_object($fournisseur)) {
$requete .= ' AND ca.fournisseur = '.$fournisseur->getId();
}
$marque = $objet->getMarque();
if (is_object($marque)) {
$requete .= ' AND e.marque = '.$marque->getId();
}
if ($objet->getArticlesComposes()) {
$requete .= ' AND e.produitInterne = 1';
}
if ( ! $objet->getArchive()) {
$requete .= ' AND (e.archive = 0 or e.archive is null)';
}
$requete .= ' ORDER BY e.cpump DESC';
//$requete .= ' ORDER BY a.pump DESC';
//$resultats[2][] = 'tauxChange';
//$resultats[2][] = 'PrixAchatNetEuro';
$query = $this->entityManager->createQuery($requete);
} elseif ($entityName == "CommandeFournisseur") {
$where = '';
$resultats = $this->getJoin($champs);
$requete = '';
$requete = 'SELECT id FROM export__export';
$query = $this->entityManager->createQuery($requete);
} else {
$where = '';
$resultats = $this->getJoin($champs);
$entityJoin = '';
foreach ($resultats[0] as $resultat) {
$entityJoin .= ' LEFT JOIN '.$resultat;
}
foreach ($resultats[3] as $resultat) {
if ($where != '') {
$where .= ' AND ';
}
$where .= $resultat;
}
$select = '';
foreach ($resultats[1] as $resultat) {
if ($select != '') {
$select .= ', ';
}
$select .= $resultat;
}
if ($entityName == 'Mouvements de stock') {
if (is_object($objet->getDateFiltreDebut())) {
if ($where != '') {
$where .= ' AND ';
}
$where .= "e.dateMvt > '".$objet->getDateFiltreDebut()->format('Y-m-d H:i:s')."'";
}
if (is_object($objet->getDateFiltreFin())) {
if ($where != '') {
$where .= ' AND ';
}
$where .= "e.dateMvt < '".$objet->getDateFiltreFin()->format('Y-m-d H:i:s')."'";
}
}
if ($entityName == "offres") {
$compteMarketPlace = $objet->getCompteMarketPlace();
if (is_object($compteMarketPlace)) {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'e.compteMarketPlace = '.$compteMarketPlace->getId();
if ($where != '') {
$where .= ' AND ';
}
$where .= 'e.idImport is not null';
}
} elseif ($entityName == "article") {
$calculDispo = $this->getApplication()->find('articleModifierDispo:maj');
$input = new ArrayInput(['forcer' => 1]);
$calculDispo->run($input, $output);
if ($where != '') {
$where .= ' AND ';
}
$where .= 'e.temporaire IS NULL AND e.parent is null';
if ($objet->getArticlesComposes()) {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'e.produitInterne = 1';
}
if ( ! $objet->getArchive()) {
if ($where != '') {
$where .= ' AND ';
}
$where .= '(e.archive = 0 or e.archive is null)';
}
$marque = $objet->getMarque();
if (is_object($marque)) {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'e.marque = '.$marque->getId();
}
} elseif ($entityName == "prixNetAchat") {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'e.prixNet = 1';
} elseif ($entityName == "conditionAchat") {
$marque = $objet->getMarque();
if (is_object($marque)) {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'article.marque = '.$marque->getId();
$entityJoin .= " LEFT JOIN e.article article";
}
if ($objet->getArticlesComposes()) {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'e.defaut = 1';
}
} elseif ($entityName == "conditionVente") {
$marque = $objet->getMarque();
if (is_object($marque)) {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'article.marque = '.$marque->getId();
$entityJoin .= " LEFT JOIN e.article article";
}
}
//Requete
$requete = 'SELECT '.$select.'
FROM '.$entity.' e
'.$entityJoin;
if ($where != '') {
$requete .= ' WHERE '.$where;
}
$requete .= ' GROUP BY e.id';
$output->writeln($requete);
//echo $format;
$output->writeln($requete);
//$output->writeln("STOP");
//exit;
$query = $this->entityManager->createQuery($requete);
}
switch ($format) {
case 'xml':
$retour = $this->exportXml($query, $entityName);
return $retour;
break;
case 'csv':
$iterate = true;
//if($entityName == "exportCorrectionPump") $iterate = false;
$retour = $this->exportCsv($requete, $entityName, $resultats[2], $file, $output, $note = "", $iterate, $objet);
return $retour;
break;
case 'json':
$retour = $this->exportJson($query, $entityName);
return $retour;
break;
}
}
private function getJoin($champs)
{
$entityJoin = [];
//$entityJoin['autres']='';
$select = [];
//$select['autres']='';
$titresColonnes = [];
$where = [];
foreach ($champs as $k => $champ) {
$result = trim(preg_replace('#([A-Z])#', '_$1', $champ));
$key = strtolower($result).'_id';
switch ($champ) {
/*
$requete = 'SELECT e.id FROM App\Entity\Articles\Article e'
.' JOIN e.refsEquivalentes re'
*/
case 'marqueEquivalence':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'mre.reference as '.$champ.'_marqueEquivalence';
//$entityJoin[] = 'e.marque m';
$titresColonnes[] = $champ;
break;
case 'marqueReference':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'm.reference as '.$champ.'_marqueReference';
//$entityJoin[] = 'e.marque m';
$titresColonnes[] = $champ;
break;
case 'articleReference':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'e.reference as '.$champ.'_reference';
$titresColonnes[] = $champ;
break;
case 'articleEquivalence':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 're.reference as '.$champ.'_reference';
$titresColonnes[] = $champ;
break;
case 'marque':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'categorieParent':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
//$entityJoin[] = 'e.marque m';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'modele':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'images':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
break;
case 'categories' :
$select[$key] = 'e.id as categArticles';
$titresColonnes[] = $champ;
break;
case 'uniteMesure':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'article':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.reference as '.$champ.'_reference';
$titresColonnes[] = $champ.'_reference';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'fournisseur':
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.reference as '.$champ.'_reference';
$titresColonnes[] = $champ.'_reference';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'client':
$select[$key] = 'a_'.$k.'.reference as '.$champ.'_reference, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_reference';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'familleCompta' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'type' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'regleTaxe' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'regleTaxeAchat' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'categorieRemiseArticle' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'categorie' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'delaiPaiement' :
//$select[$key] = '(e.delaiPaiement) as delaiPaiement_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'delaipaiement' :
//$select[$key] = '(e.delaiPaiement) as delaiPaiement_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.delaiPaiement a_'.$k;
break;
case 'articleRemplacement' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.reference as '.$champ.'_reference';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'emplacements' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
break;
case 'langue' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
break;
case 'modereglement' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'compta' :
//$entityJoin[]=" e.compta as compta";
//$select["compta.name"] = '(compta.name) as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.name as '.$champ.'_name';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'devise' :
//$select[$key] = '(e.'.$champ.') as '.$champ.'_id';
//$titresColonnes[] = $key;
$select[$key] = 'a_'.$k.'.name as '.$champ.'_name';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'stockDispo' :
$select[$key] = '(e.stock - e.stockReserve) as stockDispo';
$titresColonnes[] = $champ;
break;
case 'stockDispoOffre' :
$entityJoin[] = 'e.article a_'.$k;
$select[$key] = '(a_'.$k.'.stock - a_'.$k.'.stockReserve) as stockDispoOffre';
$titresColonnes[] = $champ;
break;
case 'serviceObj' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'civilite' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'pays' :
$select[$key] = 'a_'.$k.'.titre as '.$champ.'_titre, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_titre';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'categorieClient' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ.'_libelle';
//$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'codePostal' :
$select[$key] = 'a_'.$k.'.titre as '.$champ.'_titre, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_titre';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'ville' :
$select[$key] = 'a_'.$k.'.titre as '.$champ.'_titre, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_titre';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'zoneLivraison' :
$select[$key] = 'a_'.$k.'.reference as '.$champ.'_reference, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_reference';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'niveauUn' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'niveauDeux' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'niveauTrois' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'niveauQuatre' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'debut' :
$key = $champ;
$select[$key] = 'e.'.$champ;
$titresColonnes[] = "qte_mini";
break;
case 'fin' :
$key = $champ;
$select[$key] = 'e.'.$champ;
$titresColonnes[] = "qte_maxi";
break;
case 'secteurGeographique' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'transporteur' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'formeJuridique' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'origine' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'classification' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle';
$titresColonnes[] = $champ;
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'civiliteObj' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
/*
case 'condionAchatPrixAchatNetEuro' :
$entityJoin[]=" e.conditionsAchat as ca";
$select[$key] = '(ca.prixAchatNet) as '.$key;
$titresColonnes[] = $key;
$where[] = 'ca.defaut = 1';
break;
*
*/
case 'utilisateur' :
$select[$key] = 'a_'.$k.'.nom as '.$champ.'_nom, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
case 'raisonMouvementStock' :
$select[$key] = 'a_'.$k.'.libelle as '.$champ.'_libelle, a_'.$k.'.id as '.$champ.'_id';
$titresColonnes[] = $champ.'_libelle';
$titresColonnes[] = $champ.'_id';
$entityJoin[] = 'e.'.$champ.' a_'.$k;
break;
default:
$key = $champ;
$select[$key] = 'e.'.$champ;
$titresColonnes[] = $key;
}
}
return [$entityJoin, $select, $titresColonnes, $where];
}
private function exportXml($query, $entity)
{
$results = $query->getResult();
$rootNode = new \SimpleXMLElement("<?xml version='1.0' encoding='UTF-8' standalone='yes'?><items></items>");
foreach ($results as $result) {
$itemNode = $rootNode->addChild($entity);
foreach ($result as $key => $value) {
$itemNode->addChild($key, $value);
}
}
$date = new \Datetime();
$filename = date_format($date, 'Ymd_').$entity.'.xml';
return new Response($rootNode->asXML(), 200, [
'Content-Type' => 'application/force-download',
'Content-Disposition' => 'attachment; filename="'.$filename.'"',
]);
}
private function exportCsv($requete, $entity, $titresColonnes, $file, $output, $note, $iterate = true, $objet)
{
set_time_limit(600);
$output->writeln('$iterate : '.$iterate);
$handle = fopen($file, 'w+');
$delimiter = ';';
fputcsv($handle, $titresColonnes, $delimiter);
if (1 == 1 && $entity != "equivalence") {
$query = $this->entityManager->createQuery($requete);
//$output->writeln($query->getSql());
//exit;
//Pour tester avec un minimum de résultat
//$query->setMaxResults(5);
$results = $query->toIterable();
$total_lignes = count($query->getResult());
$this->writeFile($results, $entity, $handle, $delimiter, $output, $iterate, $titresColonnes, $objet, $total_lignes);
} elseif (1 == 1 && $entity != "equivalence") {
$nbResultChoice = 1000;
$nbResult = $nbResultChoice;
$j = 1;
while ($nbResultChoice == $nbResult) {
$output->writeln('$j : '.$j);
$query = $this->entityManager->createQuery($requete);
//$results = $query->setMaxResults($nbResultChoice)->setFirstResult($j)->getResult();
$results = $query->setMaxResults($nbResultChoice)->setFirstResult($j)->iterate();
$nbResult = count($results);
$output->writeln('$nbResult : '.$nbResult);
if ($nbResult > 0) {
$this->writeFile($results, $entity, $handle, $delimiter, $output, $iterate, $titresColonnes, $objet);
}
$j = $j + $nbResultChoice;
//if($j > 10000) break;
$output->writeln($j);
$this->entityManager->clear();
}
} elseif (($iterate === true || 1 == 1) && $entity != "equivalence") {
$results = $query
//->setMaxResults(50)
->iterate();
} else {
$query = $this->entityManager->createQuery($requete);
$results = $query
//->setMaxResults(50)
->execute();
$total_lignes = count($query->getResult());
$this->writeFile($results, $entity, $handle, $delimiter, $output, $iterate = false, $titresColonnes, $objet, $total_lignes);
}
fclose($handle);
}
public function writeFile($results, $entity, $handle, $delimiter, $output, $iterate, $titresColonnes, $objet, $total_lignes = 1)
{
$conn = $this->entityManager->getConnection();
$total_result = $total_lignes;
//$total_result = count($results);
$output->writeln('total_result '.$total_result);
if ($iterate) {
$i = 0;
foreach ($results as $row) {
if ($entity == "emplacementLibre" and 1 == 1) {
unset($row[$i]["id"]);
unset($row[$i]["total"]);
$result = $row[$i];
} elseif ($entity == "article") {
if ( ! empty($row[$i]["categArticles"])) {
$id_article = $row[$i]["categArticles"];
$req_article = "SELECT date FROM article__article where id = ".$id_article;
$stmt = $conn->executeQuery($req_article);
$article_bdd = $stmt->fetchAllAssociative();
$date_obj = new \DateTime($article_bdd[0]["date"]);
//$date_obj->format('Y-m-d h:i:s')
//$output->writeln($date_obj->format('dmY'));
$req_cat_art = "SELECT c.libelle
FROM article__article_categorie aac
LEFT JOIN article__categorie c ON aac.categorie_id = c.id
where c.id != 1 and article_id = ".$id_article;
$stmt = $conn->executeQuery($req_cat_art);
$categ_bdd = $stmt->fetchAllAssociative();
$categ_string = "";
if (count($categ_bdd)) {
foreach ($categ_bdd as $pic) {
$categ_string .= $pic["libelle"]."|||";
}
}
$categ_string = trim($categ_string, "|||");
$row[$i]["categArticles"] = $categ_string;
}
if ( ! empty($row[$i]["img"])) {
$id_article = $row[$i]["img"];
$req_article = "SELECT date FROM article__article where id = ".$id_article;
$stmt = $conn->executeQuery($req_article);
$article_bdd = $stmt->fetchAllAssociative();
$date_obj = new \Datetime($article_bdd[0]["date"]);
//$date_obj->format('Y-m-d h:i:s')
//$output->writeln($date_obj->format('dmY'));
$req_img = "SELECT * FROM article__image where article_id = ".$id_article;
$stmt = $conn->executeQuery($req_img);
$images_bdd = $stmt->fetchAllAssociative();
$img_string = "";
if (count($images_bdd)) {
foreach ($images_bdd as $pic) {
//http://lbdp.local.tl/uploads/articles/images/2017/07/01/730836/small/Logo-Boutique-du-Progres.jpg
$url_img = "http://bdp.devweb.pro/uploads/articles/images/";
$url_img .= $date_obj->format('Y')."/".$date_obj->format('m')."/".$date_obj->format('d')."/";
$url_img .= $id_article."/originals/";
$url_img .= $pic["url"];
$img_string .= $url_img."|||";
}
}
$img_string = trim($img_string, "|||");
$row[$i]["img"] = $img_string;
}
//print_r($row);
$result = $row;
} elseif ($entity == "exportEquivalence" and 1 != 1) {
} elseif ($entity == "exportCorrectionPump") {
$result = [];
foreach ($titresColonnes as $col) {
if (array_key_exists($col, $row[$i])) {
if (in_array($col, ['tauxChange', 'cump', 'pump', 'prixAchatNetDevise', 'coefficientConditionnement'])) {
$result[] = str_replace('.', ',', $row[$i][$col]);
} else {
$result[] = $row[$i][$col];
}
}
if ($col == 'prixAchatNetEuro') {
$prixAchatNetEuro = floatval($row[$i]['prixAchatNetDevise']) * floatval($row[$i]['tauxChange']);
$result[] = str_replace('.', ',', $prixAchatNetEuro);
}
}
} elseif ($entity == "conditionVente") {
foreach ($row as $k => $r) {
if (is_object($r)) {
$row[$k] = $r->format('Y-m-d h:i:s');
}
}
$result = $row;
} elseif ($entity == "Mouvements de stock") {
foreach ($row as $k => $r) {
if (is_a($r, 'DateTime')) {
$row[$k] = $r->format('Y-m-d h:i');
}
}
$result = $row;
} else {
$result = $row;
}
fputcsv($handle, $result, $delimiter);
if ($i % 1000 == 0) {
$output->writeln($i);
}
$i++;
$progression = 0;
if ($total_result > 0) {
round($progression = $i * 100 / $total_result);
}
$progression = round($progression);
//echo "<div>i ".$i."</div>\n";
//echo "<div>total ".count($results)."</div>\n";
//echo "<div>progression ".$progression."</div>\n";
//exit;
$conn->update('export__export', ['progression' => $progression], ['id' => $objet->getId()]);
}
} else {
$i = 0;
foreach ($results as $key => $row) {
if ($entity == "exportCorrectionPump") {
$result = [];
foreach ($titresColonnes as $col) {
if (array_key_exists($col, $row)) {
if (in_array($col, ['tauxChange', 'cump', 'pump', 'prixAchatNetDevise', 'coefficientConditionnement'])) {
$result[] = str_replace('.', ',', $row[$col]);
} else {
$result[] = $row[$col];
}
}
if ($col == 'prixAchatNetEuro') {
$prixAchatNetEuro = floatval($row['prixAchatNetDevise']) * floatval($row['tauxChange']);
$result[] = str_replace('.', ',', $prixAchatNetEuro);
}
}
} else {
$result = $row;
}
fputcsv($handle, $result, $delimiter);
$i++;
$progression = 0;
if ($total_result > 0) {
$progression = round($i * 100 / $total_result);
}
$conn->update('export__export', ['progression' => ($progression)], ['id' => $objet->getId()]);
}
}
}
private function exportJson($query, $entity)
{
$results = $query->getResult();
$date = new \Datetime();
$filename = date_format($date, 'Ymd_').$entity.'.json';
$response = new Response(json_encode(['Entity' => $results]));
//$response->headers->set('Content-Type', 'application/json');
$response->headers->set('Content-Type', 'application/force-download');
$response->headers->set('Content-Disposition', 'attachment; filename="'.$filename.'"');
return $response;
}
}

src/Command/Export/exportCommand.php line 175