Symfony Profiler

<?php
namespace App\Controller\Export;
use App\Entity\Client;
use App\Entity\Clientvente;
use App\Entity\Fournisseur;
use App\Entity\Moisvalide;
use App\Excel\Excel;
use Doctrine\ORM\EntityManagerInterface;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Symfony\Component\Routing\Annotation\Route;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class ExportController extends AbstractController
{
/**
* @Route("/admin/", name="malys_export_homepage")
*/
public function indexAction(EntityManagerInterface $em)
{
// FOURNISSEURS
$fournisseurs = $em->getRepository(Fournisseur::class)->findAllOrderByNom()->getQuery()->getResult();
return $this->render('export/index.html.twig', array('fournisseurs' => $fournisseurs));
}
/**
* @Route("/admin/export_cactif", name="malys_export_cactif")
*/
public function exportClientActifAction(EntityManagerInterface $em)
{
// Récupération des clients actifs
$t_clients_actifs = $em->getRepository(Client::class)->findAllClientActifAndVisible();
// Création du classeur
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Actifs');
// Entêtes
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Nom');
$sheet->getStyle('A1:B1')->getFont()->setBold(true);
// Remplissage des données
$row = 2;
$e = 0;
foreach ($t_clients_actifs as $client) {
// Selon ta structure : $client[0]['id'] et $client[0]['nom']
$sheet->setCellValue('A' . $row, $client[0]['id']);
$sheet->setCellValue('B' . $row, $client[0]['nom']);
$row++;
$e++;
}
// Génération du fichier Excel via StreamedResponse
$response = new StreamedResponse(function() use ($spreadsheet) {
$writer = new Xls($spreadsheet);
$writer->save('php://output');
});
$filename = 'export_anp_' . date("Ymd_Hi") . '.xls';
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', 'inline; filename="' . $filename . '"');
return $response;
}
/**
* @Route("/admin/export_anp", name="malys_export_anp")
*/
public function exportListActifNouveauPerduAction(EntityManagerInterface $em)
{
$annee = date("Y");
// Récupère le mois validé le plus proche
$MonthValid = $em->getRepository(Moisvalide::class)->findLastMoisvalide(date("n"), date("Y"));
// Tous les clients avec CA par mois/année
$t_allCaClientByMonth = $em->getRepository(Clientvente::class)
->getAllClientsCaLight($annee, null, 'three year', $this->getUser()->getId(), $this->getUser()->getFamille());
$t_listClientActif = [];
$t_listClientNouveau = [];
$t_listClientPerdu = [];
foreach ($t_allCaClientByMonth as $item) {
$yearfr = $item['datefr']->format('Y');
$monthfr = $item['datefr']->format('n');
// Clients actifs du dernier mois validé
if ($monthfr == $MonthValid[0]['mois'] && $yearfr == $MonthValid[0]['annee']) {
if (!isset($t_listClientActif[$item['id']])) {
$t_listClientActif[$item['id']] = $item['nom'];
$t_listClientNouveau[$item['id']] = $item['nom'];
}
}
// Clients perdus du mois précédent
if ($monthfr == $MonthValid[0]['mois'] - 1 && $yearfr == $MonthValid[0]['annee']) {
if (!isset($t_listClientPerdu[$item['id']])) {
$t_listClientPerdu[$item['id']] = $item['nom'];
}
}
}
// Retirer les clients actifs de la liste des perdus
foreach ($t_listClientPerdu as $k => $value) {
if (isset($t_listClientActif[$k])) {
unset($t_listClientPerdu[$k]);
}
}
// Déterminer les nouveaux clients
foreach ($t_allCaClientByMonth as $item) {
$yearfr = $item['datefr']->format('Y');
$monthfr = $item['datefr']->format('n');
if ($yearfr == ($MonthValid[0]['annee'])) {
if (in_array($monthfr, [
$MonthValid[0]['mois'] - 1,
$MonthValid[0]['mois'] - 2,
$MonthValid[0]['mois'] - 3,
$MonthValid[0]['mois'] - 4
])) {
if (isset($t_listClientNouveau[$item['id']])) {
unset($t_listClientNouveau[$item['id']]);
}
}
}
}
// Création du classeur
$spreadsheet = new Spreadsheet();
// Feuille Actifs
$sheetActifs = $spreadsheet->getActiveSheet();
$sheetActifs->setTitle('Actifs');
$sheetActifs->setCellValue('A1', 'ID');
$sheetActifs->setCellValue('B1', 'Nom');
$sheetActifs->getStyle('A1:B1')->getFont()->setBold(true);
$row = 2;
foreach ($t_listClientActif as $id => $nom) {
$sheetActifs->setCellValue('A' . $row, $id);
$sheetActifs->setCellValue('B' . $row, $nom);
$row++;
}
// Feuille Nouveaux
$sheetNouveaux = $spreadsheet->createSheet();
$sheetNouveaux->setTitle('Nouveaux');
$sheetNouveaux->setCellValue('A1', 'ID');
$sheetNouveaux->setCellValue('B1', 'Nom');
$sheetNouveaux->getStyle('A1:B1')->getFont()->setBold(true);
$row = 2;
foreach ($t_listClientNouveau as $id => $nom) {
$sheetNouveaux->setCellValue('A' . $row, $id);
$sheetNouveaux->setCellValue('B' . $row, $nom);
$row++;
}
// Feuille Perdus
$sheetPerdus = $spreadsheet->createSheet();
$sheetPerdus->setTitle('Perdus');
$sheetPerdus->setCellValue('A1', 'ID');
$sheetPerdus->setCellValue('B1', 'Nom');
$sheetPerdus->getStyle('A1:B1')->getFont()->setBold(true);
$row = 2;
foreach ($t_listClientPerdu as $id => $nom) {
$sheetPerdus->setCellValue('A' . $row, $id);
$sheetPerdus->setCellValue('B' . $row, $nom);
$row++;
}
// Génération du fichier Excel via StreamedResponse
$response = new StreamedResponse(function () use ($spreadsheet) {
$writer = new Xls($spreadsheet);
$writer->save('php://output');
});
$filename = 'export_anp_' . $MonthValid[0]['mois'] . '_' . $MonthValid[0]['annee'] . '_' . date("Ymd_Hi") . '.xls';
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', 'inline; filename="' . $filename . '"');
return $response;
}
/**
* @Route("/admin/export_cf", name="malys_export_cf")
*/
public function exportCFAction(EntityManagerInterface $em)
{ $fournisseurs = $em->getRepository(Fournisseur::class)->findBy(['activite' => 1]);
// GET CLIENTS
$clients = $em->getRepository(Client::class)->findClientsWithAdmin(0);
// GET PROSPECTS
$prospects = $em->getRepository(Client::class)->findClientsWithAdmin(1);
// Création du classeur
$spreadsheet = new Spreadsheet();
// --- Clients ---
$sheetClients = $spreadsheet->getActiveSheet();
$sheetClients->setTitle('Clients');
$headerClients = ['id', 'nom', 'raison', 'adresse', 'cp', 'ville', 'contact', 'fonction', 'tel', 'mobile', 'fax', 'nomchef', 'mobilechef', 'email', 'activite', 'commercial'];
$sheetClients->fromArray($headerClients, null, 'A1');
$row = 2;
foreach ($clients as $c) {
$sheetClients->fromArray([
$c['id'],
$c['nom'],
$c['raison'],
$c['adresse'],
$c['cp'],
$c['ville'],
$c['contact'],
$c['fonction'],
$c['tel'],
$c['mobile'],
$c['fax'],
$c['nomchef'],
$c['mobilechef'],
$c['email'],
$c['activite'] == 1 ? 'Actif' : 'Inactif',
$c['idadmin']['contact'] ?? ''
], null, 'A'.$row);
$row++;
}
// --- Prospects ---
$sheetProspects = $spreadsheet->createSheet();
$sheetProspects->setTitle('Prospects');
$headerProspects = ['id', 'nom', 'raison', 'adresse', 'cp', 'ville', 'contact', 'fonction', 'tel', 'mobile', 'fax', 'nomchef', 'mobilechef', 'email', 'etat', 'commercial'];
$sheetProspects->fromArray($headerProspects, null, 'A1');
$row = 2;
$etats = ['Mort', 'Froid', 'Chaud'];
foreach ($prospects as $p) {
$etat = $etats[$p['note']] ?? '';
$sheetProspects->fromArray([
$p['id'],
$p['nom'],
$p['raison'],
$p['adresse'],
$p['cp'],
$p['ville'],
$p['contact'],
$p['fonction'],
$p['tel'],
$p['mobile'],
$p['fax'],
$p['nomchef'],
$p['mobilechef'],
$p['email'],
$etat,
$p['idadmin']['contact'] ?? ''
], null, 'A'.$row);
$row++;
}
// --- Fournisseurs ---
$sheetFournisseurs = $spreadsheet->createSheet();
$sheetFournisseurs->setTitle('Fournisseurs');
$headerFournisseurs = ['id', 'nom', 'raison', 'adresse', 'cp', 'ville', 'contact', 'fonction', 'tel', 'mobile', 'fax', 'email'];
$sheetFournisseurs->fromArray($headerFournisseurs, null, 'A1');
$row = 2;
foreach ($fournisseurs as $f) {
$sheetFournisseurs->fromArray([
$f->getId(),
$f->getNom(),
$f->getRaison(),
$f->getAdresse(),
$f->getCp(),
$f->getVille(),
$f->getContact(),
$f->getFonction(),
$f->getTel(),
$f->getMobile(),
$f->getFax(),
$f->getEmail()
], null, 'A'.$row);
$row++;
}
// --- Génération du fichier Excel ---
$writer = new Xls($spreadsheet);
$filename = 'export_cf_' . date('Ymd_Hi') . '.xls';
$temp_file = tempnam(sys_get_temp_dir(), $filename);
$writer->save($temp_file);
// --- Réponse Symfony ---
return $this->file($temp_file, $filename, ResponseHeaderBag::DISPOSITION_INLINE);
}
/**
* @Route("/admin/export_segments", name="malys_export_segments")
*/
public function exportSegmentsAction(EntityManagerInterface $em)
{
// Récupération des fournisseurs
$fournisseurs = $em->getRepository(Fournisseur::class)->findAllFournisseursWithClients();
// Création du classeur
$spreadsheet = new Spreadsheet();
$firstSheet = true;
foreach ($fournisseurs as $f) {
// Feuille : si première, on utilise la feuille par défaut, sinon on crée une nouvelle
if ($firstSheet) {
$sheet = $spreadsheet->getActiveSheet();
$firstSheet = false;
} else {
$sheet = $spreadsheet->createSheet();
}
// Nom de la feuille
$nomFeuille = $f->getId() . "-" . strtoupper(substr($f->getNom() . (($f->getSource() == "restogest") ? " RSTG" : ""), 0, 27));
$nomFeuille = preg_replace('/[:\\\\\/\?\*\[\]]/', '', $nomFeuille);
$sheet->setTitle($nomFeuille);
// Entêtes
$sheet->setCellValue('A1', 'Nom');
$sheet->setCellValue('B1', 'Raison');
$sheet->setCellValue('C1', 'CP');
$sheet->setCellValue('D1', 'Ville');
$sheet->setCellValue('E1', 'Contact');
$sheet->setCellValue('F1', 'Mobile');
$sheet->setCellValue('G1', 'Email');
$sheet->setCellValue('H1', 'Fournisseur');
$sheet->getStyle('A1:H1')->getFont()->setBold(true);
// Remplissage des clients
$row = 2;
foreach ($f->getClientrelation() as $cr) {
$c = $cr->getIdclient();
$sheet->setCellValue('A' . $row, $c->getNom());
$sheet->setCellValue('B' . $row, $c->getRaison());
$sheet->setCellValueExplicit('C' . $row, $c->getCp(), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValue('D' . $row, $c->getVille());
$sheet->setCellValue('E' . $row, $c->getContact());
$sheet->setCellValueExplicit('F' . $row, $c->getMobile(), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValueExplicit('G' . $row, $c->getEmail(), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValue('H' . $row, $f->getNom());
$row++;
}
}
// Téléchargement direct via StreamedResponse
$response = new StreamedResponse(function () use ($spreadsheet) {
$writer = new Xls($spreadsheet);
$writer->save('php://output');
});
$filename = 'export_segmentfc_' . date("Ymd_Hi") . '.xls';
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', 'inline; filename="' . $filename . '"');
return $response;
}
/**
* @Route("/admin/export_c_by_f", name="malys_export_c_by_f")
*/
public function exportCByFAction(EntityManagerInterface $em)
{
// FOURNISSEURS
$fournisseurs = $em->getRepository(Fournisseur::class)->findAllOrderByNom()->getQuery()->getResult();
return $this->render('export/exportCByF_filter.html.twig', array('fournisseurs' => $fournisseurs));
}
/**
* @Route("/admin/export_c_by_f_traitement", name="malys_export_c_by_f_traitement")
*/
public function exportCByFTraitementAction(Request $request, EntityManagerInterface $em)
{
if ($request->isMethod('POST')) {
$exclu = $request->request->get('exclu', []);
$inclu = $request->request->get('inclu', []);
if (!empty($exclu) || !empty($inclu)) {
$clients_tab = $em->getRepository(Client::class)->findClientByFournisseursWorked($exclu, $inclu);
} else {
$clients_tab = $em->getRepository(Client::class)->findClientByFournisseursWorked();
}
if (empty($clients_tab)) {
// FOURNISSEURS
$fournisseurs = $em->getRepository(Fournisseur::class)->findAllOrderByNom()->getQuery()->getResult();
return $this->render('export/exportCByF_filter.html.twig', ['fournisseurs' => $fournisseurs]);
}
// Création du classeur
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Clients');
// Entêtes
$headers = ['id', 'nom', 'raison', 'adresse', 'cp', 'ville', 'contact', 'fonction', 'tel', 'mobile', 'fax', 'nomchef', 'mobilechef', 'email', 'activite', 'commercial'];
$col = 'A';
foreach ($headers as $header) {
$sheet->setCellValue($col . '1', $header);
$sheet->getStyle($col . '1')->getFont()->setBold(true);
$col++;
}
// Contenu
$row = 2;
foreach ($clients_tab as $c) {
$sheet->setCellValue('A' . $row, $c['id']);
$sheet->setCellValue('B' . $row, $c['nom']);
$sheet->setCellValue('C' . $row, $c['raison']);
$sheet->setCellValue('D' . $row, $c['adresse']);
$sheet->setCellValueExplicit('E' . $row, $c['cp'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValue('F' . $row, $c['ville']);
$sheet->setCellValue('G' . $row, $c['contact']);
$sheet->setCellValue('H' . $row, $c['fonction']);
$sheet->setCellValueExplicit('I' . $row, $c['tel'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValueExplicit('J' . $row, $c['mobile'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValueExplicit('K' . $row, $c['fax'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValue('L' . $row, $c['nomchef']);
$sheet->setCellValueExplicit('M' . $row, $c['mobilechef'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValueExplicit('N' . $row, $c['email'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValue('O' . $row, ($c['activite'] == 1 ? 'Actif' : 'Inactif'));
$sheet->setCellValue('P' . $row, $c['idadmin']['contact'] ?? '');
$row++;
}
// Génération du fichier Excel
$writer = new Xls($spreadsheet);
$response = new StreamedResponse(function() use ($writer) {
$writer->save('php://output');
});
$filename = 'ex_cli_by_frn_worked_' . date("Ymd_Hi") . '.xls';
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', 'attachment; filename="' . $filename . '"');
return $response;
}
return false;
}
/**
* @Route("/admin/export_client", name="malys_export_client")
*/
public function exportClientsAction(EntityManagerInterface $em)
{
ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(0);
date_default_timezone_set('Europe/Paris');
// $filePath = tempnam( "documents/tmp", ".xls" );
//$this->get('malys_export.excel');
// $workbook = new \writeexcel_workbook( $filePath );
// $worksheet = $workbook->addworksheet( 'Clients' );
// $heading = $workbook->addformat( array(
// 'bold' => 1,
// 'color' => 'black',
// 'size' => 12,
// 'merge' => 1
// ) );
$now = new \DateTime();
$now->modify('-1 month');
$monthAgo = new \DateTime('last month');
$monthAgo->modify('-1 month');
// $worksheet->write( "A1", "Client", $heading );
// $worksheet->write( "B1", "Commercial", $heading );
//
// $worksheet->write( "C1", "CA " . $now->format( "F Y" ) );
// $worksheet->write( "D1", "Solde " . $now->format( "F Y" ) );
//
// $worksheet->write( "E1", "CA " . $monthAgo->format( "F Y" ) );
// $worksheet->write( "F1", "Solde " . $monthAgo->format( "F Y" ) );
// $response = new \Symfony\Component\HttpFoundation\Response;
// $response->headers->set( 'Content-Type', 'application/vnd.ms-excel' );
// $response->headers->set( 'Content-Disposition', 'attachment; filename="exportClients_' . date( "Ymd" ) . '_' . date( "Hi" ) . '.xls"' );
// $response->setContent( file_get_contents( $filePath ) );
// unlink( $filePath );
$ventesRepo = $em->getRepository(Clientvente::class);
$customerRepo = $em->getRepository(Client::class);
$clients = $customerRepo->findBy(
[
'affiche' => 1,
'prospect' => 0
]
);
usort($clients, function ($a, $b) {
return ($a->getIdadmin()->getId() < $b->getIdadmin()->getId()) ? -1 : 1;
});
$thisMonthStart = new \DateTime('first day of this month');
$thisMonthStart->modify('-1 month');
$thisMonthStartStr = $thisMonthStart->format('Y-m-d');
$thisMonthEnd = new \DateTime('last day of this month');
$thisMonthEnd->modify('-1 month');
$thisMonthEndStr = $thisMonthEnd->format('Y-m-d');
$lastMonthStart = new \DateTime('first day of last month');
$lastMonthStart->modify('-1 month');
$lastMonthStartStr = $lastMonthStart->format('Y-m-d');
$lastMonthEnd = new \DateTime('last day of last month');
$lastMonthEnd->modify('-1 month');
$lastMonthEndStr = $lastMonthEnd->format('Y-m-d');
$infos = [];
foreach ($clients as $client) {
$thisMonthVentes = $ventesRepo->getCustomerBetweenDates($client, $thisMonthStartStr, $thisMonthEndStr);
$lastMonthVentes = $ventesRepo->getCustomerBetweenDates($client, $lastMonthStartStr, $lastMonthEndStr);
$clientInfos = [];
$clientInfos['entity'] = $client;
$clientInfos['thisMonthCA'] = $this->getCA($thisMonthVentes);
$clientInfos['thisMonthSolde'] = $this->getSolde($thisMonthVentes);
$clientInfos['lastMonthCA'] = $this->getCA($lastMonthVentes);
$clientInfos['lastMonthSolde'] = $this->getSolde($lastMonthVentes);
$infos[] = $clientInfos;
}
return $this->render('export/ClientPerAgent.html.twig', array(
'now' => $now,
'monthAgo' => $monthAgo,
'infos' => $infos
));
}
private function getCA($ventes)
{
$result = 0;
foreach ($ventes as $vente) {
$venteCA = $vente->getMontantht();
$result = $result + $venteCA;
}
$result = str_replace('.', ',', $result);
return $result;
}
private function getSolde($ventes)
{
$result = 0;
foreach ($ventes as $vente) {
$venteCA = $vente->getSolde();
$result = $result + $venteCA;
}
$result = str_replace('.', ',', $result);
return $result;
}
}

src/Controller/Export/ExportController.php line 29