Symfony Profiler

<?php
namespace App\Controller;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Annotation\Route;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\HttpFoundation\Session\SessionInterface;
use Doctrine\DBAL\Connection;
use App\Entity\Commande;
use App\Entity\User;
use App\Entity\Client;
use App\Entity\Commercial;
use App\Entity\Bordereau;
use App\Entity\Parametrage;
use App\Entity\Fournisseur;
use App\Entity\Lignecommande;
use App\Entity\Lignetypecommande;
use App\Entity\Livraison;
use App\Entity\Produit;
use App\Entity\Magasin;
use App\Entity\ArriereClient;
use App\EventSubscriber\LocaleSubscriber;
use App\Service\LandingRouteResolver;
use App\Service\LogActionService;
use Symfony\Component\PasswordHasher\Hasher\UserPasswordHasherInterface;
use Symfony\Component\Security\Http\Authentication\AuthenticationUtils;
use App\Security\TokenAuthenticator;
use Symfony\Component\Security\Guard\GuardAuthenticatorHandler;
class AccueilController extends AbstractController
{
private $connection;
private $session;
private $idproduit;
private $nomproduit;
private $produit;
public function __construct(ManagerRegistry $doctrine,Connection $connection,SessionInterface $session)
{
$em = $doctrine->getManager();
$this->connection = $connection;
$this->session = $session;
if($this->session->get('getsessionproduit')=="" || $this->session->get('getsessionproduit')==null){
$arrays = array();
$arrays['datesuppressionproduit'] = null;
$oneprod = $em->getRepository(Produit::class)->findOneBy($arrays);
if($oneprod!=null){
$this->session->set('getsessionproduit',$oneprod->getId());
$this->session->set('getsessionnomproduit',$oneprod->getNomproduit());
}
}
$this->idproduit = $this->session->get('getsessionproduit');
$this->nomproduit = $this->session->get('getsessionnomproduit');
$this->produit = $em->getRepository(Produit::class)->findOneBy(['id'=>$this->session->get('getsessionproduit')]);
}
public $keypass = "@Dmin1494";
/**
* @Route("/sidebar", name="sidebar")
*/
public function sidebar(ManagerRegistry $doctrine,Request $request): Response
{
$em = $this->getDoctrine()->getManager();
$conn = $this->connection;
$parametrage = $em->getRepository(Parametrage::class)->findOneBy([], ['id' => 'ASC']);
if($parametrage==null){
$parametrage = new Parametrage();
$em->persist($parametrage);
$em->flush();
}
$commande = $em->getRepository(Commande::class)->findOneBy(['produitid'=>$this->idproduit,'id'=>0,'produitid'=>$this->idproduit]);
$commandes = $conn->fetchAllAssociative("
SELECT COUNT(*) AS nombre, m.nommagasin , m.id
FROM stockboutique sb , typeciment tc , magasin m
WHERE sb.typecimentid = tc.id AND tc.datesuppressiontypeciment IS NULL
AND m.id = sb.magasinid
AND sb.qtestock < tc.qteinitiale
AND tc.produitid = $this->idproduit
GROUP BY m.id ORDER BY nombre DESC");
return $this->render('partials/rsidehead.html.twig',[
'COMMANDE'=>$commande,
'NOTIFICATIONS'=>$commandes,
'PARAMETRAGE'=>$parametrage,
'BORDEREAUX'=>[],
'PRODUITS'=>[],
'CT'=>[],
'CJ'=>[],
]);
}
/**
* @Route("/sidebarmagasin", name="sidebarmagasin")
*/
public function sidebarmagasin(ManagerRegistry $doctrine,Request $request): Response
{
$user = $this->getUser();
$em = $this->getDoctrine()->getManager();
$conn = $this->connection;
$parametrage = $em->getRepository(Parametrage::class)->findOneBy([], ['id' => 'ASC']);
if($parametrage==null){
$parametrage = new Parametrage();
$em->persist($parametrage);
$em->flush();
}
$commande = $em->getRepository(Commande::class)->findOneBy(['produitid'=>$this->idproduit,'id'=>0,'produitid'=>$this->idproduit]);
$arrays = array();
$arrays['datesuppressionproduit'] = null;
if(!$this->isGranted('ROLE_SUPER_ADMIN')){
$arrays['id'] = $user->getProduitslist()??[];
}
$produits = $em->getRepository(Produit::class)->findBy($arrays);
$defaut = 0;
if(sizeof($produits)>0){
$defaut = $produits[0]->getId();
}
$commandes = $conn->fetchAllAssociative("
SELECT COUNT(*) AS nombre, m.nommagasin , m.id
FROM stockboutique sb , typeciment tc , magasin m
WHERE sb.typecimentid = tc.id AND tc.datesuppressiontypeciment IS NULL
AND m.id = sb.magasinid
AND sb.qtestock < tc.qteinitiale
AND tc.produitid = $this->idproduit
GROUP BY m.id ORDER BY nombre DESC");
return $this->render('partials/rsideheadmagasin.html.twig',[
'PRODUITES'=>$produits,
'COMMANDE'=>$commande,
'NOTIFICATIONS'=>$commandes,
'PARAMETRAGE'=>$parametrage,
'BORDEREAUX'=>[],
'PRODUITS'=>[],
'CT'=>[],
'CJ'=>[],
]);
}
/**
* @Route("/side", name="side")
*/
public function side(SessionInterface $sessions,ManagerRegistry $doctrine,Request $request): Response
{
$user = $this->getUser();
$em = $doctrine->getManager();
$parametres = $this->container;
$conn = $this->connection;
$modification = true;
$message = "";
$statut = 0;
$color = "red";
if((sizeof($user->getProduitslist()??[])==1)){
$arrays = array();
$arrays['datesuppressionproduit'] = null;
if(!$this->isGranted('ROLE_SUPER_ADMIN')){
$arrays['id'] = $user->getProduitslist()??[];
}
$produits = $em->getRepository(Produit::class)->findOneBy($arrays);
$defaut = 1;
$defautname = "Ciment";
if($produits!=null){
$defaut = $produits->getId();
$defautname = $produits->getNomproduit();
}
$sessions->set('getsessionproduit',$defaut);
$sessions->set('getsessionnomproduit',$defautname);
}
$parametrage = $em->getRepository(Parametrage::class)->findOneBy([], ['id' => 'ASC']);
if($parametrage==null){
$parametrage = new Parametrage();
$em->persist($parametrage);
$em->flush();
}
$commande = $em->getRepository(Commande::class)->findOneBy(['produitid'=>$this->idproduit,'id'=>0]);
//$bordereau = $em->getRepository(Bordereau::class)->findBy(['produitid'=>$this->idproduit,'datesuppressionbordereau'=>null],['datebordereau'=>'DESC'],200);
$lastcommande = $em->getRepository(Commande::class)->findOneBy(['produitid'=>$this->idproduit,],['numcommande'=>'DESC']);
$lastcomm = 1 ;
if($lastcommande!=null){
$lastcomm = $lastcommande->getNumcommande()+1;
}
$formatted_num = sprintf('%03d', $lastcomm);
if($commande==null){
$commande = new Commande();
}
$arrays = array();
$arrays['datesuppressionproduit'] = null;
if(!$this->isGranted('ROLE_SUPER_ADMIN')){
$arrays['id'] = $user->getProduitslist()??[];
}
$produits = $em->getRepository(Produit::class)->findBy($arrays);
$defaut = 0;
if(sizeof($produits)>0){
$defaut = $produits[0]->getId();
}
$session = $sessions->get('getsessionproduit')??$defaut;
// Le menu latéral se rend sur CHAQUE page : un COUNT suffit pour le
// badge — l'ancien SELECT * ramenait toutes les colonnes de tout
// l'historique des commandes à chaque affichage.
$nbCommandesEnCours = (int) $conn->fetchOne("SELECT COUNT(*)
FROM commande c, bordereau b
where c.datesuppressioncommande is null AND c.produitid = $this->idproduit AND c.bordereauid = b.id
AND
IFNULL((SELECT SUM(l.quantite) FROM lignetypecommande l where l.commandeid = c.id AND l.datesuppressionlignetypecommande is NULL),0)>
IFNULL((SELECT SUM(l.tonne) FROM livraison l where l.commandeid = c.id AND l.datesuppressionlivraison is NULL AND l.vehiculeid IS NOT NULL),0)");
// Créer un QueryBuilder pour Bordereau
$qb = $em->createQueryBuilder();
// Construire la requête
$qb->select('b')
->from(Bordereau::class, 'b')
->where('b.produitid = :idProduit')
->andWhere('b.datesuppressionbordereau IS NULL')
->andWhere($qb->expr()->notIn(
'b.id',
$em->createQueryBuilder()
->select('IDENTITY(c.bordereauid)')
->from(Commande::class, 'c')
->where('c.datesuppressioncommande IS NULL')
->getDQL()
))
->orderBy('b.datebordereau', 'DESC')
->setMaxResults(200)
->setParameter('idProduit', $this->idproduit);
// Exécuter la requête
$bordereau = $qb->getQuery()->getResult();
return $this->render('partials/side.html.twig',[
'SESSIONS'=>$session,
'PRODUITES'=>$produits,
'PARAMETRAGE'=>$parametrage,
'COMMANDE'=>$commande,
'BORDEREAUX'=>$bordereau,
'lastcommande'=>$formatted_num,
'COMMS'=>$nbCommandesEnCours,
'CT'=>[],
'CJ'=>[],
]);
}
/**
* @Route("/", name="home")
*/
public function index(Request $request, LandingRouteResolver $landingResolver): Response
{
// Chaque profil atterrit sur le premier écran que ses droits autorisent
// (un commercial terrain n'a pas « voir-tableaudebord »).
return $this->redirectToRoute($landingResolver->resolve($this->getUser()));
}
/**
* @Route("/admin", name="homer")
*/
public function indexer(Request $request, LandingRouteResolver $landingResolver): Response
{
return $this->redirectToRoute($landingResolver->resolve($this->getUser()));
}
/**
* @Route("/404", name="404")
*/
public function erreur(Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
return $this->render('accueil/404.html.twig');
}
/**
* @Route("/setsession", name="setsession")
*/
public function setsession(SessionInterface $sessions,Request $request): Response
{
$em = $this->getDoctrine()->getManager();
$produits = $em->getRepository(Produit::class)->findOneBy(['datesuppressionproduit'=>null,'id'=>$request->get('setsession')]);
// Périmètre produit du compte (audit 02/09/2026) : la session ne
// porte jamais un type de produit hors produitslist — c'est elle
// qui borne les écrans commande. Hors périmètre : on retombe sur
// le premier type autorisé du compte.
$utilisateur = $this->getUser();
if ($produits !== null && $utilisateur !== null && !$this->isGranted('ROLE_SUPER_ADMIN')) {
$autorises = array_values(array_filter(array_map('intval', $utilisateur->getProduitslist() ?? [])));
if ($autorises !== [] && !in_array((int) $produits->getId(), $autorises, true)) {
$produits = $em->getRepository(Produit::class)->findOneBy([
'datesuppressionproduit' => null,
'id' => $autorises[0],
]);
}
}
$defaut = 1;
$defautname = "Ciment";
if($produits!=null){
$defaut = $produits->getId();
$defautname = $produits->getNomproduit();
}
$sessions->set('getsessionproduit',$defaut);
$sessions->set('getsessionnomproduit',$defautname);
return new JsonResponse(['OK']);
}
/**
* @Route("/admin/rapportsolde", name="rapportsolde")
*/
public function rapportsolde(ManagerRegistry $doctrine,SessionInterface $sessions,Connection $conn,Request $request,LogActionService $logger): Response
{
$u = $this->getUser();
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « solde »",
$u ? $u->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'solde', 'filtres' => $request->query->all()]]
);
return new JsonResponse(['html'=>$this->rapportventetotalmagasin( $doctrine, $request)]);
}
public function rapportventetotalmagasin(ManagerRegistry $doctrine,Request $request)
{
$user = $this->getUser();
$em = $doctrine->getManager();
$parametres = $this->container;
$conn = $this->connection;
$modification = true;
$message = "";
$statut = 0;
$color = "red";
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin')." 23:59:59";
if(strlen($datedebut)<8){
$timestampDebutLundi = strtotime('monday this week');
$datedebut = date('Y-m-d', $timestampDebutLundi);
$datefin = date('Y-m-d 23:59:59');
}
$AllData = array();
$idmagasin = 0;
if($user!=null && $user->getMagasinid()!=null){
$arrays = array();
$idmagasin = $user->getMagasinid()->getId();
$arrays['datesuppressionproduit'] = null;
if(!$this->isGranted('ROLE_SUPER_ADMIN')){
$arrays['id'] = $user->getProduitslist()??[];
}
$produits = $em->getRepository(Produit::class)->findBy($arrays);
foreach($produits as $produit){
$ventedata = $em->createQueryBuilder()
->select('SUM(p.totalvente) AS totalVente', 'SUM(p.totalpaye) AS totalPaye', 'SUM(p.remisevente) AS totalRemise', 'SUM(p.totalvente-p.totalpaye) AS totalReste')
->from('App\Entity\Vente', 'p')
->where('p.datesuppressionvente IS null')
->andWhere('p.produitid = :idprod ')->setParameter('idprod', $produit->getId())
->andWhere('p.magasinid = :idmag ')->setParameter('idmag',$idmagasin)
->andWhere("p.datevente >= :datedebut ")->setParameter('datedebut', $datedebut)
->andWhere("p.datevente <= :datefin")->setParameter('datefin', $datefin)
->getQuery()->getResult();
$AllData[] = [
"Produit"=>$produit,
"Data"=>$ventedata
] ;
}
}
$depense = $em->createQueryBuilder()
->select('SUM(p.montantdepense) AS totalDepense')
->from('App\Entity\Depense', 'p')
->where('p.datesuppressiondepense IS null')
->andWhere('p.magasinid = :idmag ')->setParameter('idmag',$idmagasin)
->andWhere("p.datedepense >= :datedebut ")->setParameter('datedebut', $datedebut)
->andWhere("p.datedepense <= :datefin")->setParameter('datefin', $datefin)
->getQuery()->getResult();
return $this->renderView('ventemobile/totalgestion.html.twig', [
"ALLDATA"=>$AllData,
"ALLDEPENSE"=>$depense
]);
}
/**
* @Route("/admin/tdb", name="accueil")
*/
public function admin(ManagerRegistry $doctrine,SessionInterface $sessions,Connection $conn,Request $request): Response
{
$ajaxs = false;
$user = $this->getUser();
$em = $this->getDoctrine()->getManager();
$parametres = $this->container;
$sql = "";
$arrayprod = array();
$arrayprod[] = "Ciment";
$arrayprod[] = "Fer à Béton";
$data = array();
$idmagasin = 1;
if($user!=null && $user->getMagasinid()!=null){
$arrays = array();
$idmagasin = $user->getMagasinid()->getId();
$arrays['datesuppressionproduit'] = null;
if(!$this->isGranted('ROLE_SUPER_ADMIN')){
$arrays['id'] = $user->getProduitslist()??[];
}
$produits = $em->getRepository(Produit::class)->findBy($arrays);
$listeciment = array();
$listeciment2 = array();
foreach($produits as $produit){
$idprod = $produit->getId();
$typeciments = $conn->fetchAllAssociative("
SELECT * , sm.qtestock AS stockactuel , tc.id , (tc.prixtypeciment/tc.barretonne) AS prixtypeciment,
(IFNULL((SELECT nomfournisseur FROM fournisseur WHERE id = tc.fournisseurid),'')) AS nomfournisseur
FROM typeciment tc , stockboutique sm
WHERE tc.id = sm.typecimentid
AND tc.produitid = $idprod
AND sm.magasinid = $idmagasin
AND tc.datesuppressiontypeciment IS NULL
AND sm.datesuppressionstockboutique IS NULL
AND sm.qtestock > 0
ORDER BY LENGTH(tc.nomtypeciment) ASC , tc.nomtypeciment ASC");
$typeciments2 = $conn->fetchAllAssociative("
SELECT * , sm.qtestock AS stockactuel , tc.id , (tc.prixtypeciment/tc.barretonne) AS prixtypeciment,
(IFNULL((SELECT nomfournisseur FROM fournisseur WHERE id = tc.fournisseurid),'')) AS nomfournisseur
FROM typeciment tc , stockboutique sm
WHERE tc.id = sm.typecimentid
AND tc.produitid = $idprod
AND sm.magasinid = $idmagasin
AND tc.datesuppressiontypeciment IS NULL
AND sm.datesuppressionstockboutique IS NULL
ORDER BY LENGTH(tc.nomtypeciment) ASC , tc.nomtypeciment ASC");
//$result = array_merge($typeciments, $typeciments2);
$listeciment[$idprod] = $typeciments;
$listeciment2[$idprod] = $typeciments2;
}
$timestampLundi = null;
if((new DroitController())->estautoriser($this,'lister-vente-jour',true)){
$timestampLundi = date('Y-m-d');
}
if((new DroitController())->estautoriser($this,'lister-vente-semaine',true)){
$timestampLundi = strtotime('monday this week');
$timestampLundi = date('Y-m-d', $timestampLundi);
}
if((new DroitController())->estautoriser($this,'lister-vente-mois',true)){
$timestampLundi = date('Y-m').'-01';
}
if((new DroitController())->estautoriser($this,'lister-vente-annee',true)){
$timestampLundi = date('Y').'-01-01';
}
$clients = $em->getRepository(Client::class)->findBy(['datesuppressionclient'=>null],['nom'=>'ASC','prenom'=>'ASC']);
$commercials = $em->getRepository(Commercial::class)->findBy(['datesuppressioncommercial'=>null],['nom'=>'ASC','prenom'=>'ASC']);
$timestampDebutLundi = strtotime('monday this week');
$timestampDebutLundi = date('Y-m-d', $timestampDebutLundi);
return $this->render('ventemobile/index.html.twig',[
'CLIENTS'=>$clients,
'COMMERCIALS'=>$commercials,
'PRODUITES'=>$produits,
'TYPECIMENT'=>$listeciment,
'TYPECIMENT2'=>$listeciment2,
'LIGNEUNIQUE'=>null,
'MINDATE'=>$timestampLundi ,
'DEBUTDATE'=>$timestampDebutLundi,
'TEMPLATE'=> $this->rapportventetotalmagasin( $doctrine, $request)
]);
}
foreach($arrayprod as $arraypr){
$prod = $em->getRepository(Produit::class)->findOneBy(['nomproduit'=>$arraypr]);
if($prod==null){
$prod = new Produit();
$prod->setNomproduit($arraypr);
$prod->setJournalproduit("Création du produit $arraypr");
$prod->setDatecreationproduit(new \DateTime());
$em->persist($prod);
$em->flush();
}
}
if($sessions->get('getsessionproduit')==null || strlen($sessions->get('getsessionproduit'))==0){
$arrays = array();
$arrays['datesuppressionproduit'] = null;
if(!$this->isGranted('ROLE_SUPER_ADMIN')){
$arrays['id'] = $user->getProduitslist()??[];
}
$produits = $em->getRepository(Produit::class)->findOneBy($arrays);
$defaut = 1;
$defautname = "Ciment";
if($produits!=null){
$defaut = $produits->getId();
$defautname = $produits->getNomproduit();
}
$sessions->set('getsessionproduit',$defaut);
$sessions->set('getsessionnomproduit',$defautname);
}
if ((new DroitController())->estautoriser($this,'voir-tableaudebord',1) == true){
$localite = $conn->fetchAllAssociative("SELECT * FROM localite l where l.datesuppressionlocalite is null ORDER BY nomlocalite ASC LIMIT 6");
$option = 1;
$dated = "";
$datef = "";
if ($request->getMethod() == 'PUT') {
$option = $request->get('periode');
$dated = $request->get('dated');
$datef = $request->get('datef');
}
if($option==10){
$dates = $this->recupererDates($option,$dated,$datef);
}else{
$dates = $this->recupererDates($option);
}
$datedebut = $dates['datedebut'];
$datefin = $dates['datefin'];
$graphe = $dates['graphe'];
$grapheval = $dates['grapheval'];
$dat = [];
$sommecommandetermine = [];
$sommecommandeencours = [];
$sommecommandeannule = [];
foreach($graphe as $f){
$dat[] = strlen($f);
}
$sommebordereau = $conn->fetchAllAssociative("
SELECT IFNULL(SUM(montantversement),0) AS TT , COUNT(*) AS NT FROM bordereau where datebordereau BETWEEN
'$datedebut' AND '$datefin' AND datesuppressionbordereau IS NULL AND produitid = $this->idproduit ;");
$sommecommande = $conn->fetchAllAssociative("
SELECT COUNT(*) AS NT ,
IFNULL((SELECT SUM(m.apayer) FROM livraison m WHERE m.datesuppressionlivraison is null AND m.produitid = $this->idproduit AND m.commandeid = c.id ),0) AS TG,
IFNULL((SELECT SUM(m.total) FROM lignetypecommande m WHERE m.datesuppressionlignetypecommande is null AND m.produitid = $this->idproduit AND m.commandeid = c.id ),0) AS TT
FROM commande c
where c.datecommande BETWEEN '$datedebut' AND '$datefin'
AND c.produitid = $this->idproduit
AND c.datesuppressioncommande IS NULL ;");
$sommecommande2 = $conn->fetchAllAssociative("SELECT IFNULL(SUM(tc.total),0) AS TT , IFNULL(SUM(lc.prixunitairelignecommande*lc.total),0) AS TG , COUNT(*) AS NT
FROM dangote c , lignedangote lc , lignetypecommande tc
where c.datecommande BETWEEN '$datedebut' AND '$datefin'
AND c.produitid = $this->idproduit
AND c.id = lc.commandeid AND c.id = tc.commandeid
AND c.datesuppressioncommande IS NULL AND lc.datesuppressionlignecommande IS NULL ;");
$grapheDatas = [];
$tab01 = [];
$tab02 = [];
$tab03 = [];
foreach($grapheval as $el){
$datedebut = $el['dd'];
$datefin = $el['df'];
$sommecommandetermine = $conn->fetchAllAssociative("
SELECT COUNT(*) AS NT ,
IFNULL((SELECT SUM(m.apayer) FROM livraison m WHERE m.datesuppressionlivraison is null AND m.produitid = $this->idproduit AND m.commandeid = c.id ),0) AS TG,
IFNULL((SELECT SUM(m.total) FROM lignetypecommande m WHERE m.datesuppressionlignetypecommande is null AND m.produitid = $this->idproduit AND m.commandeid = c.id ),0) AS TT
FROM commande c
where c.datecommande BETWEEN '$datedebut' AND '$datefin'
AND c.produitid = $this->idproduit
AND c.datevalidationcommande IS NOT NULL
AND c.datesuppressioncommande IS NULL ;");
$sommecommandeencours = $conn->fetchAllAssociative("
SELECT COUNT(*) AS NT ,
IFNULL((SELECT SUM(m.apayer) FROM livraison m WHERE m.datesuppressionlivraison is null AND m.produitid = $this->idproduit AND m.commandeid = c.id ),0) AS TG,
IFNULL((SELECT SUM(m.total) FROM lignetypecommande m WHERE m.datesuppressionlignetypecommande is null AND m.produitid = $this->idproduit AND m.commandeid = c.id ),0) AS TT
FROM commande c
where c.datecommande BETWEEN '$datedebut' AND '$datefin'
AND c.produitid = $this->idproduit
AND c.datevalidationcommande IS NULL
AND c.datesuppressioncommande IS NULL ;");
$sommecommandeannule = $conn->fetchAllAssociative("
SELECT COUNT(*) AS NT ,
IFNULL((SELECT SUM(m.apayer) FROM livraison m WHERE m.datesuppressionlivraison is null AND m.produitid = $this->idproduit AND m.commandeid = c.id ),0) AS TG,
IFNULL((SELECT SUM(m.total) FROM lignetypecommande m WHERE m.datesuppressionlignetypecommande is null AND m.produitid = $this->idproduit AND m.commandeid = c.id ),0) AS TT
FROM commande c
where c.datecommande BETWEEN '$datedebut' AND '$datefin'
AND c.produitid = $this->idproduit
AND c.datevalidationcommande IS NULL
AND c.datesuppressioncommande IS NOT NULL ;");
$tab01[] = sizeof($sommecommandetermine)>0 ? $sommecommandetermine[0]['TT'] : 0;
$tab02[] = sizeof($sommecommandeannule)>0 ? $sommecommandeannule[0]['TT'] : 0;
$tab03[] = sizeof($sommecommandeencours)>0 ? $sommecommandeencours[0]['TT'] : 0;
}
$grapheDatas[] = [['name'=>'Terminé','data'=>$tab01],['name'=>'Annulé','data'=>$tab02],['name'=>'En cours','data'=>$tab03]];
$commandes = $conn->fetchAllAssociative("SELECT * , c.id AS idc , b.id AS idb ,
IFNULL((SELECT SUM(l.tonne*l.quantite) FROM lignecommande l where l.commandeid = c.id AND l.datesuppressionlignecommande is NULL),0) AS totaltonne
FROM commande c, bordereau b
where c.datesuppressioncommande is null
AND c.produitid = $this->idproduit
AND c.bordereauid = b.id AND c.datevalidationcommande is NULL AND c.dateannulationcommande IS NULL
ORDER BY c.numcommande DESC LIMIT 10");
$commandes2 = $conn->fetchAllAssociative("SELECT * , c.id AS idc,
IFNULL((SELECT SUM(l.tonne*l.quantite) FROM lignedangote l where l.commandeid = c.id AND l.datesuppressionlignecommande is NULL),0) AS totaltonne
FROM dangote c , typeciment tc
where c.datesuppressioncommande is null AND tc.id = c.typecimentid AND c.datevalidationcommande is NULL AND c.dateannulationcommande IS NULL
ORDER BY c.numcommande DESC LIMIT 10");
// ── 1. DÉPENSES sur la même période que le filtre ──────────────────────────
$depenses_tdb = $conn->fetchAllAssociative("
SELECT
IFNULL(SUM(d.montantdepense), 0) AS total,
COUNT(d.id) AS nb,
SUM(CASE WHEN f.id IS NULL THEN 1 ELSE 0 END) AS sans_justif,
c.nom AS categorie_nom,
c.couleur AS categorie_couleur,
IFNULL(c.nom, 'Non catégorisé') AS cat_label
FROM depense d
LEFT JOIN depense_categorie c ON c.id = d.categorieid
LEFT JOIN (
SELECT depenseid, MIN(id) AS id
FROM depense_fichier
WHERE datesuppression IS NULL
GROUP BY depenseid
) f ON f.depenseid = d.id
WHERE d.datesuppressiondepense IS NULL
AND d.produitid = $this->idproduit
AND d.datedepense BETWEEN '$datedebut' AND '$datefin'
GROUP BY d.categorieid
ORDER BY total DESC
LIMIT 8
");
// Totaux dépenses pour les KPI cards
$depenses_totaux = $conn->fetchAssociative("
SELECT
IFNULL(SUM(montantdepense), 0) AS total,
COUNT(id) AS nb,
SUM(CASE WHEN (
SELECT COUNT(*) FROM depense_fichier
WHERE depenseid = d.id AND datesuppression IS NULL
) = 0 THEN 1 ELSE 0 END) AS sans_justif
FROM depense d
WHERE datesuppressiondepense IS NULL
AND produitid = $this->idproduit
AND datedepense BETWEEN '$datedebut' AND '$datefin'
") ?: ['total'=>0,'nb'=>0,'sans_justif'=>0];
// ── 2. STOCK disponible par type de ciment ─────────────────────────────────
$stock_tdb = $conn->fetchAllAssociative("
SELECT
tc.nomtypeciment,
tc.prixtypeciment / tc.barretonne AS prix_unitaire,
sm.qtestock,
sm.qtestock * (tc.prixtypeciment / tc.barretonne) AS valeur_stock,
IFNULL((SELECT nomfournisseur FROM fournisseur WHERE id = tc.fournisseurid), '—') AS fournisseur
FROM typeciment tc
INNER JOIN stockboutique sm ON sm.typecimentid = tc.id
WHERE tc.produitid = $this->idproduit
AND sm.magasinid = $idmagasin
AND tc.datesuppressiontypeciment IS NULL
AND sm.datesuppressionstockboutique IS NULL
AND sm.qtestock > 0
ORDER BY LENGTH(tc.nomtypeciment), tc.nomtypeciment
");
$stock_total_valeur = array_sum(array_column($stock_tdb, 'valeur_stock'));
$stock_total_qte = array_sum(array_column($stock_tdb, 'qtestock'));
// ── 3. TOP CLIENTS ──────────────────────────────────────────────────────────
$top_clients = $conn->fetchAllAssociative("
SELECT
CONCAT(IFNULL(cl.nom,''), ' ', IFNULL(cl.prenom,'')) AS nom_client,
IFNULL(SUM(v.totalvente), 0) AS total_vente,
IFNULL(SUM(v.totalpaye), 0) AS total_paye,
IFNULL(SUM(v.totalvente - v.totalpaye), 0) AS reste,
COUNT(v.id) AS nb_ventes
FROM vente v
INNER JOIN client cl ON cl.id = v.clientid
WHERE v.datesuppressionvente IS NULL
AND v.produitid = $this->idproduit
AND v.magasinid = $idmagasin
AND v.datevente BETWEEN '$datedebut' AND '$datefin'
GROUP BY v.clientid
ORDER BY total_vente DESC
LIMIT 8
");
// ── 4. VENTES MAGASIN (résumé rapportventetotalmagasin) ────────────────────
$ventes_magasin = $conn->fetchAllAssociative("
SELECT
IFNULL(SUM(v.totalvente), 0) AS totalVente,
IFNULL(SUM(v.totalpaye), 0) AS totalPaye,
IFNULL(SUM(v.remisevente), 0) AS totalRemise,
IFNULL(SUM(v.totalvente - v.totalpaye), 0) AS totalReste,
COUNT(v.id) AS nbVentes,
p.nomproduit
FROM vente v
INNER JOIN produit p ON p.id = v.produitid
WHERE v.datesuppressionvente IS NULL
AND v.produitid = $this->idproduit
AND v.magasinid = $idmagasin
AND v.datevente BETWEEN '$datedebut' AND '$datefin'
GROUP BY v.produitid
") ?: [];
$ventes_magasin_resume = $ventes_magasin[0] ?? ['totalVente'=>0,'totalPaye'=>0,'totalRemise'=>0,'totalReste'=>0,'nbVentes'=>0];
// ── Ajouter au tableau $data (insérer dans les clés existantes) ─────────────
// Dans votre $data = [...], ajoutez ces lignes :
// 'DEPENSES_TDB' => $depenses_tdb,
// 'DEPENSES_TOTAUX' => $depenses_totaux,
// 'STOCK_TDB' => $stock_tdb,
// 'STOCK_TOTAL_VALEUR' => $stock_total_valeur,
// 'STOCK_TOTAL_QTE' => $stock_total_qte,
// 'TOP_CLIENTS' => $top_clients,
// 'VENTES_MAGASIN' => $ventes_magasin_resume,
// 'idmagasin' => $idmagasin,
$data = [
'dd'=>$datedebut,
'df'=>$datefin,
'LOCALITES'=>$localite,
'COMMANDES'=>$commandes,
'COMMANDES2'=>$commandes2,
'RESTAURANTS'=>[],
'PRODUITS'=>[],
'TB'=>$sommebordereau,
'TC'=>$sommecommande,
'TC2'=>$sommecommande2,
'CV'=>sizeof($sommecommandetermine)>0 ? $sommecommandetermine[0] : ['TT'=>0],
'CEC'=>sizeof($sommecommandeencours)>0 ? $sommecommandeencours[0] : ['TT'=>0],
'CA'=>sizeof($sommecommandeannule)>0 ? $sommecommandeannule[0] : ['TT'=>0],
'G'=>$graphe,
'D'=>$dat,
'V'=>$grapheval,
'GD'=>$grapheDatas
];
if ($request->getMethod() == 'PUT') {
return new JsonResponse(["vu"=>$this->renderView('accueil/include1.html.twig',$data),'data'=>$data]);
}
return $this->render('accueil/index.html.twig',$data);
}else{
return $this->render('accueil/noindex.html.twig');
}
}
/**
* @Route("/admin/rapport", name="rapport")
*/
public function rapport(ManagerRegistry $doctrine,SessionInterface $sessions,Connection $conn,Request $request,LogActionService $logger,\App\Service\MagasinCatalogueFilterService $magasinFilter): Response
{
$ajaxs = false;
$user = $this->getUser();
$em = $this->getDoctrine()->getManager();
$parametres = $this->container;
$sql = "";
$logger->createLog(
'rapport_consultation',
"Consultation du rapport général",
$user ? $user->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'general', 'filtres' => $request->query->all()]]
);
$arrayprod = array();
$arrayprod[] = "Ciment";
$arrayprod[] = "Fer à Béton";
$data = array();
$timestampLundi = strtotime('monday this week');
$timestampLundi = date('Y-m-d', $timestampLundi);
$fournisseurs = $em->getRepository(Fournisseur::class)->findBy(['produitid'=>$this->idproduit,'datesuppressionfournisseur'=>null],['nomfournisseur'=>'ASC']);
$commerciaux = $em->getRepository(Commercial::class)->findBy(['datesuppressioncommercial'=>null],['nom'=>'ASC']);
$arrays = array();
$idmagasin = 0;
$arrays['datesuppressionproduit'] = null;
if(!$this->isGranted('ROLE_SUPER_ADMIN')){
$arrays['id'] = $user->getProduitslist()??[];
}
$produits = $em->getRepository(Produit::class)->findBy($arrays);
$typeciments2 = $conn->fetchAllAssociative("
SELECT * , tc.id , (tc.prixtypeciment/tc.barretonne) AS prixtypeciment,
(IFNULL((SELECT nomfournisseur FROM fournisseur WHERE id = tc.fournisseurid),'')) AS nomfournisseur
FROM typeciment tc
WHERE tc.produitid = $this->idproduit
AND tc.datesuppressiontypeciment IS NULL
ORDER BY LENGTH(tc.nomtypeciment) ASC , tc.nomtypeciment ASC");
$timestampDebutLundi = strtotime('monday this week');
$timestampDebutLundi = date('Y-m-d', $timestampDebutLundi);
$clients = $em->getRepository(Client::class)->findBy(['datesuppressionclient'=>null],['nom'=>'ASC','prenom'=>'ASC']);
$commercials = $em->getRepository(Commercial::class)->findBy(['datesuppressioncommercial'=>null],['nom'=>'ASC','prenom'=>'ASC']);
// Ne lister que les magasins qui gèrent le produit actif, au sens du
// catalogue (stockboutique.catalogue_actif = 1) — la même règle que
// Vente, Dépense, Ajustement et Situation magasin, via
// MagasinCatalogueFilterService. La colonne héritée magasin.produitid
// ne porte qu'un seul produit et ne reflète plus la réalité : elle
// masquait des magasins qui vendent bel et bien le produit actif.
// Sans produit actif — ou si le catalogue ne répond rien — on retombe
// sur tous les magasins ouverts : mieux vaut une liste complète qu'un
// filtre vide.
$magasins = $magasinFilter->magasinsPourProduit($this->idproduit);
if (empty($magasins)) {
$magasins = $em->getRepository(Magasin::class)->findBy(
['datesuppressionmagasin' => null],
['nommagasin' => 'ASC']
);
}
$arriereStats = $em->getRepository(ArriereClient::class)->statsRapport($this->idproduit);
$data = ["VIEW"=>0,'idproduit'=>$this->idproduit,'FOURNISSEURS'=>$fournisseurs,'DATEDEBUT'=>$timestampLundi,'CLIENTS'=>$clients,
'COMMERCIALS'=>$commercials, 'TYPECIMENT'=>$typeciments2, 'MINDATE'=>$timestampLundi, 'DEBUTDATE'=>$timestampDebutLundi , 'MAGASINS'=>$magasins,'COMMERCIAUX'=>$commerciaux,
'TEMPLATE'=> $this->rapportventetotalmagasin( $doctrine, $request),
'ARRIERES_STATS' => $arriereStats];
return $this->render('rapport/index.html.twig',$data);
}
/**
* @Route("/admin/rapportventemagasin", name="rapportventemagasin")
*/
public function rapportventemagasin(ManagerRegistry $doctrine,Request $request,LogActionService $logger): Response
{
$user = $this->getUser();
$em = $doctrine->getManager();
$parametres = $this->container;
$conn = $this->connection;
$modification = true;
$message = "";
$statut = 0;
$color = "red";
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « ventes par magasin »",
$user ? $user->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'ventes_par_magasin', 'filtres' => $request->query->all()]]
);
$idproduitelement = $request->get('idproduitelement');
$filtredetail = $request->get('filtredetail');
$id = $request->get('idproduit');
$statutvente = $request->get('statutvente')??1;
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin');
$client = $request->get('client')??0;
$commercial = $request->get('commercial')??0;
$idmagasin = $request->get('magasinid');
$ventes = $em->createQueryBuilder()
->select('lv','p')
->from('App\Entity\Lignetypevente', 'lv')
->join('lv.venteid', 'p')
->where('p.datesuppressionvente IS null')
->andWhere('p.datevalidationvente IS NOT null')
->andWhere('lv.datesuppressionlignetypevente IS null')
->andWhere('p.produitid = :idprod ')
->setParameter('idprod', $id);
$ventedata = $em->createQueryBuilder()
->select('SUM(p.totalvente) AS totalVente', 'SUM(p.totalpaye) AS totalPaye', 'SUM(p.remisevente) AS totalRemise', 'SUM(p.totalvente-p.totalpaye) AS totalReste')
->from('App\Entity\Vente', 'p')
->where('p.datesuppressionvente IS null')
->andWhere('p.datevalidationvente IS NOT null')
->andWhere('p.produitid = :idprod ')
->setParameter('idprod', $id);
if($idmagasin>0){
$ventes = $ventes->andWhere('p.magasinid = :idmag ')->setParameter('idmag',$idmagasin);
$ventedata = $ventedata->andWhere('p.magasinid = :idmag ')->setParameter('idmag',$idmagasin);
}
if ($idproduitelement == -1) {
$ventes = $ventes
->join('lv.typecimentid', 'tc')
->select('tc.nomtypeciment AS nomtypeciment','IDENTITY(lv.typecimentid) AS typeciment', 'SUM(lv.quantite) AS quantiteTotal', 'SUM(lv.total) AS totalVente')
->groupBy('lv.typecimentid');
}
if($idproduitelement>0){
$ventes = $ventes->andWhere("lv.typecimentid = :idproduitelement ")->setParameter('idproduitelement', $idproduitelement);
//$ventedata = $ventedata->andWhere("lv.typecimentid = :idproduitelement ")->setParameter('idproduitelement', $idproduitelement);
}
if(strlen($datedebut)>7){
$ventes = $ventes->andWhere("p.datevente >= :datedebut ")->setParameter('datedebut', $datedebut);
$ventedata = $ventedata->andWhere("p.datevente >= :datedebut ")->setParameter('datedebut', $datedebut);
}
if(strlen($datedebut)>7){
$ventes = $ventes->andWhere("p.datevente <= :datefin")->setParameter('datefin', $datefin);
$ventedata = $ventedata->andWhere("p.datevente <= :datefin")->setParameter('datefin', $datefin);
}
if((new DroitController())->estautoriser($this,'voir-mes-vente',true)){
//$ventes = $ventes->andWhere("p.venteajoutepar = :venteajoutepar")->setParameter('venteajoutepar', $user->getId());
}
if($client>0){
$ventes = $ventes->andWhere("p.clientid = $client");
$ventedata = $ventedata->andWhere("p.clientid = $client");
}
if($commercial>0){
$ventes = $ventes->andWhere("p.commercialid = $commercial");
$ventedata = $ventedata->andWhere("p.commercialid = $commercial");
}
if($statutvente==2){ // En attente
$ventes = $ventes->andWhere("p.datevalidationvente IS NULL");
$ventedata = $ventedata->andWhere("p.datevalidationvente IS NULL");
}
if($statutvente==3){ // Aucun paiement
$ventes = $ventes->andWhere("p.totalvente>0 AND p.totalpaye = 0");
$ventedata = $ventedata->andWhere("p.totalvente>0 AND p.totalpaye = 0");
}
if($statutvente==4){ // Paiement partiel
$ventes = $ventes->andWhere("p.totalvente > p.totalpaye AND p.totalpaye>0");
$ventedata = $ventedata->andWhere("p.totalvente > p.totalpaye AND p.totalpaye>0");
}
if($statutvente==5){ // Soldé
$ventes = $ventes->andWhere("p.totalvente>0 AND p.totalvente <= p.totalpaye");
$ventedata = $ventedata->andWhere("p.totalvente>0 AND p.totalvente <= p.totalpaye");
}
if ($idproduitelement == -1) {
$ventes = $ventes->orderBy('totalVente', 'DESC')->getQuery()->getResult();
$ventedata = $ventedata->orderBy('totalVente', 'DESC')->getQuery()->getResult();
}else{
$ventes = $ventes->orderBy('p.id', 'ASC')->getQuery()->getResult();
$ventedata = $ventedata->orderBy('p.id', 'ASC')->getQuery()->getResult();
}
$total = 0;
$totalpaye = 0;
$qte = 0;
$nombre = 0;
$lignevente = array();
foreach($ventes as $vente){
if ($idproduitelement == -1) {
$qte += $vente['quantiteTotal'];
$total += $vente['totalVente'];
}else{
$total = $total + $vente->getTotal();
$qte = $qte + $vente->getQuantite();
}
$nombre++;
$totalpaye = 0;
}
return $this->render('rapport/rapportventemagasin.html.twig', [
'VENTE'=>$ventedata,
'TOTAL'=>$total,
'TOTALPAYE'=>$totalpaye,
'VENTES' => $ventes,
'NOMBRE'=> $nombre,
'idproduitelement'=> $idproduitelement,
'QTE'=> $qte
]);
}
/**
* @Route("/admin/rapportventeclient", name="rapportventeclient")
*/
public function rapportventeclient(ManagerRegistry $doctrine,Request $request,LogActionService $logger): Response
{
$user = $this->getUser();
$em = $doctrine->getManager();
$parametres = $this->container;
$conn = $this->connection;
$modification = true;
$message = "";
$statut = 0;
$color = "red";
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « ventes par client »",
$user ? $user->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'ventes_par_client', 'filtres' => $request->query->all()]]
);
$idproduitelement = $request->get('idproduitelement');
$filtredetail = $request->get('filtredetail');
$id = $request->get('idproduit');
$statutvente = $request->get('statutvente')??1;
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin');
$client = $request->get('client')??0;
$commercial = $request->get('commercial')??0;
$idmagasin = $request->get('magasinid');
$ventedata = $em->createQueryBuilder()
->select('SUM(ltv.total) AS totalVente', 'SUM(ltv.total) AS totalPaye', 'SUM(ltv.total) AS totalRemise', 'SUM(ltv.total) AS totalReste')
->from('App\Entity\Lignetypecommande', 'ltv')
->join('ltv.commandeid', 'p')
->where('p.datesuppressioncommande IS null')
//->andWhere('p.datevalidationcommande IS NOT null')
->andWhere('p.produitid = :idprod ')
->setParameter('idprod', $id);
$sql = "";
if($idmagasin>0){
//$ventes = $ventes->andWhere('p.magasinid = :idmag ')->setParameter('idmag',$idmagasin);
//$ventedata = $ventedata->andWhere('p.magasinid = :idmag ')->setParameter('idmag',$idmagasin);
}
if ($idproduitelement == -1) {
/* $ventes = $ventes
->join('ltv.typecimentid', 'tc')
->select('tc.nomtypeciment AS nomtypeciment','IDENTITY(ltv.typecimentid) AS typeciment', 'SUM(ltv.quantite) AS quantiteTotal', 'SUM(ltv.total) AS totalVente')
->groupBy('ltv.typecimentid'); */
}
if($idproduitelement>0){
$sql .= " AND tc.id = $idproduitelement ";
}
if(strlen($datedebut)>7){
$sql .= " AND lc.datecreationlignecommande >= '$datedebut 00:00:00' ";
}
if(strlen($datefin)>7){
$sql .= " AND lc.datecreationlignecommande <= '$datefin 23:59:59' ";
}
if($client>0){
$sql .= " AND lc.clientid = $client ";
}
if($commercial>0){
$sql .= " AND l.commercialid = $commercial ";
}
if($statutvente==2){ // En attente
}
if($statutvente==3){ // Aucun paiement
$sql .= " AND l.totalpaye = 0 ";
}
if($statutvente==4){ // Paiement partiel
$sql .= " AND l.totalpaye > 0 AND l.totalpaye < l.apayer ";
}
if($statutvente==5){ // Soldé
$sql .= " AND l.totalpaye >= l.apayer ";
}
if ($idproduitelement == -1) {
$ventes = $conn->fetchAllAssociative("
SELECT tc.* , SUM(l.tonne) AS tonne , SUM(l.apayer) AS apayer , SUM(l.totalpaye) AS totalpaye FROM
typeciment tc , commande c , lignetypecommande ltc , lignecommande lc , livraison l
WHERE tc.id = ltc.typecimentid AND c.id = ltc.commandeid AND ltc.id = lc.lignetypecommandeid AND lc.id = l.lignecommandeid
AND c.datesuppressioncommande IS NULL AND ltc.datesuppressionlignetypecommande IS NULL AND lc.datesuppressionlignecommande IS NULL AND l.datesuppressionlivraison IS NULL
AND (lc.clientid IS NOT NULL OR lc.localiteid IS NOT NULL) AND tc.produitid = $id $sql GROUP BY tc.id");
}else{
$ventes = $conn->fetchAllAssociative("
SELECT * ,
IFNULL((SELECT CONCAT(client.nom,' ',client.prenom,' ',client.telephone) FROM client WHERE client.id = lc.clientid),
IFNULL((SELECT nomlocalite FROM localite WHERE localite.id = lc.localiteid),'-')) AS MYCLIENT,
IFNULL((SELECT CONCAT(commercial.nom,' ',commercial.prenom,' ',commercial.telephone) FROM commercial WHERE commercial.id = l.commercialid),'-') AS MYCOMMERCIAL
FROM
typeciment tc , commande c , lignetypecommande ltc , lignecommande lc , livraison l
WHERE tc.id = ltc.typecimentid AND c.id = ltc.commandeid AND ltc.id = lc.lignetypecommandeid AND lc.id = l.lignecommandeid
AND c.datesuppressioncommande IS NULL AND ltc.datesuppressionlignetypecommande IS NULL AND lc.datesuppressionlignecommande IS NULL AND l.datesuppressionlivraison IS NULL
AND (lc.clientid IS NOT NULL OR lc.localiteid IS NOT NULL) AND tc.produitid = $id $sql ");
}
$total = 0;
$totalpaye = 0;
$qte = 0;
$nombre = 0;
$lignevente = array();
foreach($ventes as $vente){
$total += $vente['apayer'];
$totalpaye += $vente['totalpaye'];
$qte += intval($vente['tonne']);
$nombre++;
}
return $this->render('rapport/rapportventeclient.html.twig', [
'VENTE'=>$ventedata,
'TOTAL'=>$total,
'TOTALPAYE'=>$totalpaye,
'COMMANDES' => $ventes,
'NOMBRE'=> $nombre,
'idproduitelement'=> $idproduitelement,
'QTE'=> $qte
]);
}
/**
* @Route("/admin/rapportcommandetotal", name="rapportcommandetotal")
*/
public function rapportcommandetotal(ManagerRegistry $doctrine,Request $request,LogActionService $logger): Response
{
$user = $this->getUser();
$em = $doctrine->getManager();
$parametres = $this->container;
$conn = $this->connection;
$modification = true;
$message = "";
$statut = 0;
$color = "red";
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « commandes total »",
$user ? $user->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'commandes_total', 'filtres' => $request->query->all()]]
);
$idproduitelement = $request->get('idproduitelement');
$filtredetail = $request->get('filtredetail');
$id = $request->get('idproduit');
$statutvente = $request->get('statutvente')??1;
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin');
$client = $request->get('client')??0;
$commercial = $request->get('commercial')??0;
$idmagasin = $request->get('magasinid');
$fournisseurid = $request->get('fournisseurid');
$sql = "";
if($fournisseurid>0){
$sql .= " AND b.fournisseurid = $fournisseurid ";
}
if($idproduitelement>0){
$sql .= " AND tc.id = $idproduitelement ";
}
if(strlen($datedebut)>7){
$sql .= " AND c.datecommande >= '$datedebut' ";
}
if(strlen($datefin)>7){
$sql .= " AND c.datecommande <= '$datefin' ";
}
if($client>0){
//$sql .= " AND lc.clientid = $client ";
}
if($commercial>0){
//$sql .= " AND l.commercialid = $commercial ";
}
if($statutvente==2){ // En attente
}
if($statutvente==3){ // Aucun paiement
//$sql .= " AND l.totalpaye = 0 ";
}
if($statutvente==4){ // Paiement partiel
//$sql .= " AND l.totalpaye > 0 AND l.totalpaye < l.apayer ";
}
if($statutvente==5){ // Soldé
//$sql .= " AND l.totalpaye >= l.apayer ";
}
if ($idproduitelement == -1) {
$ventes = $conn->fetchAllAssociative("
SELECT tc.* , SUM(ltc.total) AS total , SUM(ltc.quantite) AS quantite
FROM fournisseur f, bordereau b , typeciment tc , commande c , lignetypecommande ltc
WHERE f.id = b.fournisseurid AND tc.id = ltc.typecimentid AND b.id = c.bordereauid AND c.id = ltc.commandeid
AND c.datesuppressioncommande IS NULL AND ltc.datesuppressionlignetypecommande IS NULL AND tc.produitid = $id $sql GROUP BY tc.id");
}else{
$ventes = $conn->fetchAllAssociative("
SELECT * FROM fournisseur f, bordereau b , typeciment tc , commande c , lignetypecommande ltc
WHERE f.id = b.fournisseurid AND tc.id = ltc.typecimentid AND c.id = ltc.commandeid AND b.id = c.bordereauid
AND c.datesuppressioncommande IS NULL AND ltc.datesuppressionlignetypecommande IS NULL AND tc.produitid = $id $sql ");
}
$total = 0;
$totalpaye = 0;
$prixunitairelignetypecommande = 0;
$qte = 0;
$nombre = 0;
$lignevente = array();
foreach($ventes as $vente){
if ($idproduitelement == -1) {
$total += $vente['barretonne']*$vente['quantite'];
$totalpaye += $vente['prixtypeciment']*intval($vente['quantite']);
$prixunitairelignetypecommande += $vente['total'];
$qte += intval($vente['quantite']);
}else{
$total += $vente['barretonne']*$vente['quantite'];
$totalpaye += $vente['total'];
$prixunitairelignetypecommande += $vente['prixunitairelignetypecommande']*intval($vente['quantite']);
$qte += intval($vente['quantite']);
}
$nombre++;
}
return $this->render('rapport/rapportcommande.html.twig', [
'TOTAL'=>$total,
'TOTALPAYE'=>$totalpaye,
'COMMANDES' => $ventes,
'NOMBRE'=> $nombre,
'MONTANTTOTAL'=> $prixunitairelignetypecommande,
'idproduitelement'=> $idproduitelement,
'QTE'=> $qte
]);
}
/**
* @Route("/admin/rapportcommandevalorisation", name="rapportcommandevalorisation")
*/
public function rapportcommandevalorisation(ManagerRegistry $doctrine, Request $request, LogActionService $logger): Response
{
$user = $this->getUser();
$conn = $this->connection;
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « valorisation commandes »",
$user ? $user->getId() : null,
null, null,
['old' => null, 'new' => ['rapport' => 'commande_valorisation', 'filtres' => $request->query->all()]]
);
// Filtres
$f_fournisseur = (int) ($request->get('fournisseurid') ?? 0);
$f_livraison = (string) ($request->get('livraison') ?? '');
$f_statut = (string) ($request->get('statutcommande') ?? '');
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin');
$sql = "";
if ($f_fournisseur > 0) $sql .= " AND b.fournisseurid = $f_fournisseur";
if (strlen($datedebut) > 7) $sql .= " AND c.datecommande >= '$datedebut'";
if (strlen($datefin) > 7) $sql .= " AND c.datecommande <= '$datefin'";
if ($f_statut === 'attente') $sql .= " AND c.datevalidationcommande IS NULL AND c.dateannulationcommande IS NULL";
elseif ($f_statut === 'validee') $sql .= " AND c.datevalidationcommande IS NOT NULL AND c.dateannulationcommande IS NULL";
elseif ($f_statut === 'annulee') $sql .= " AND c.dateannulationcommande IS NOT NULL";
$having = "";
if ($f_livraison === 'aucune') $having = " HAVING totaltonne <= 0 AND lignetotaltonne > 0";
elseif ($f_livraison === 'partielle') $having = " HAVING totaltonne > 0 AND totaltonne < lignetotaltonne";
elseif ($f_livraison === 'complete') $having = " HAVING lignetotaltonne > 0 AND totaltonne >= lignetotaltonne";
elseif ($f_livraison === 'restant') $having = " HAVING lignetotaltonne > 0 AND totaltonne < lignetotaltonne";
// Détection colonnes prix
$colsLtc = array_column($conn->fetchAllAssociative("SHOW COLUMNS FROM lignetypecommande"), 'Field');
$colsLc = array_column($conn->fetchAllAssociative("SHOW COLUMNS FROM lignecommande"), 'Field');
$hasPrixLtc = in_array('prixunitairelignetypecommande', $colsLtc, true);
$hasPrixLc = in_array('prixunitairelignecommande', $colsLc, true);
$sqlValCmd = $hasPrixLtc
? "IFNULL((SELECT SUM(l.quantite * l.prixunitairelignetypecommande) FROM lignetypecommande l WHERE l.commandeid = c.id AND l.datesuppressionlignetypecommande IS NULL AND l.produitid = $this->idproduit),0)"
: "0";
$sqlValRep = $hasPrixLc
? "IFNULL((SELECT SUM(l.tonne*l.quantite*l.prixunitairelignecommande) FROM lignecommande l WHERE l.commandeid = c.id AND l.datesuppressionlignecommande IS NULL AND l.produitid = $this->idproduit),0)"
: "0";
$commandes = $conn->fetchAllAssociative("SELECT c.id AS idc, c.numcommande, c.datecommande, c.datevalidationcommande, c.dateannulationcommande,
b.id AS idb, b.numbordereau,
IFNULL((SELECT f.nomfournisseur FROM fournisseur f WHERE f.id = b.fournisseurid),'-') AS nomfournisseur,
IFNULL((SELECT SUM(l.quantite) FROM lignetypecommande l WHERE l.commandeid = c.id AND l.datesuppressionlignetypecommande IS NULL AND l.produitid = $this->idproduit),0) AS lignetotaltonne,
$sqlValCmd AS valeur_commandee,
IFNULL((SELECT SUM(l.tonne*l.quantite) FROM lignecommande l WHERE l.commandeid = c.id AND l.datesuppressionlignecommande IS NULL AND l.produitid = $this->idproduit),0) AS totaltonne,
$sqlValRep AS valeur_repartie,
IFNULL((SELECT SUM(l.tonne) FROM livraison l, lignecommande ls WHERE ls.id = l.lignecommandeid AND ls.datesuppressionlignecommande IS NULL AND l.commandeid = c.id AND l.datesuppressionlivraison IS NULL AND TRIM(l.numlivraison) <> '' AND l.produitid = $this->idproduit),0) AS totallivraisontonne
FROM commande c, bordereau b
WHERE c.datesuppressioncommande IS NULL AND c.produitid = $this->idproduit AND c.bordereauid = b.id $sql
$having
ORDER BY c.datecommande DESC, c.numcommande DESC");
// Totaux pour KPI
$tot_cmd_qte = 0; $tot_rep_qte = 0; $tot_livr_qte = 0;
$tot_cmd_val = 0; $tot_rep_val = 0;
foreach ($commandes as $r) {
$tot_cmd_qte += (float)$r['lignetotaltonne'];
$tot_rep_qte += (float)$r['totaltonne'];
$tot_livr_qte += (float)$r['totallivraisontonne'];
$tot_cmd_val += (float)$r['valeur_commandee'];
$tot_rep_val += (float)$r['valeur_repartie'];
}
return $this->render('rapport/rapportcommandevalorisation.html.twig', [
'COMMANDES' => $commandes,
'NOMBRE' => count($commandes),
'TOT_CMD_QTE'=> $tot_cmd_qte,
'TOT_REP_QTE'=> $tot_rep_qte,
'TOT_LIVR_QTE' => $tot_livr_qte,
'TOT_CMD_VAL'=> $tot_cmd_val,
'TOT_REP_VAL'=> $tot_rep_val,
'TOT_RESTE_VAL' => $tot_cmd_val - $tot_rep_val,
'TOT_RESTE_QTE' => $tot_cmd_qte - $tot_rep_qte,
'F_LIVRAISON'=> $f_livraison,
'F_STATUT' => $f_statut,
'F_FOURNISSEUR' => $f_fournisseur,
]);
}
/**
* @Route("/admin/rapportstockagemagasin", name="rapportstockagemagasin")
*/
public function rapportstockagemagasin(ManagerRegistry $doctrine,Request $request,LogActionService $logger): Response
{
$user = $this->getUser();
$em = $doctrine->getManager();
$parametres = $this->container;
$conn = $this->connection;
$modification = true;
$message = "";
$statut = 0;
$color = "red";
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « stockage magasin »",
$user ? $user->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'stockage_magasin', 'filtres' => $request->query->all()]]
);
$idproduitelement = $request->get('idproduitelement');
$filtredetail = $request->get('filtredetail');
$id = $request->get('idproduit');
$statutvente = $request->get('statutvente')??1;
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin');
$client = $request->get('client')??0;
$commercial = $request->get('commercial')??0;
$idmagasin = $request->get('magasinid');
$sql = "";
if($idmagasin>0){
$sql .= " AND lc.magasinid = $idmagasin ";
}
if($idproduitelement>0){
$sql .= " AND tc.id = $idproduitelement ";
}
if(strlen($datedebut)>7){
$sql .= " AND c.datecommande >= '$datedebut' ";
}
if(strlen($datefin)>7){
$sql .= " AND c.datecommande <= '$datefin' ";
}
if($client>0){
$sql .= " AND lc.clientid = $client ";
}
if($commercial>0){
$sql .= " AND l.commercialid = $commercial ";
}
if($statutvente==2){ // En attente
}
if($statutvente==3){ // Aucun paiement
$sql .= " AND l.totalpaye = 0 ";
}
if($statutvente==4){ // Paiement partiel
$sql .= " AND l.totalpaye > 0 AND l.totalpaye < l.apayer ";
}
if($statutvente==5){ // Soldé
$sql .= " AND l.totalpaye >= l.apayer ";
}
if ($idproduitelement == -1) {
$ventes = $conn->fetchAllAssociative("
SELECT tc.* , SUM(l.tonne*ltc.prixunitairelignetypecommande) AS prixunitairelignetypecommandetotal , SUM(l.tonne) AS tonne , SUM(l.tonne*tc.barretonne) AS barretonnetotal , SUM(l.apayer) AS apayer , SUM(l.totalpaye) AS totalpaye FROM
typeciment tc , commande c , lignetypecommande ltc , lignecommande lc , livraison l
WHERE tc.id = ltc.typecimentid AND c.id = ltc.commandeid AND ltc.id = lc.lignetypecommandeid AND lc.id = l.lignecommandeid
AND c.datesuppressioncommande IS NULL AND ltc.datesuppressionlignetypecommande IS NULL AND lc.datesuppressionlignecommande IS NULL AND l.datesuppressionlivraison IS NULL
AND lc.clientid IS NULL AND lc.localiteid IS NULL AND lc.magasinid IS NOT NULL AND tc.produitid = $id $sql GROUP BY tc.id");
}else{
$ventes = $conn->fetchAllAssociative("
SELECT * ,
IFNULL((SELECT CONCAT(client.nom,' ',client.prenom,' ',client.telephone) FROM client WHERE client.id = lc.clientid),
IFNULL((SELECT nommagasin FROM magasin WHERE magasin.id = lc.magasinid),'-')) AS MYMAGASIN
FROM
typeciment tc , commande c , lignetypecommande ltc , lignecommande lc , livraison l
WHERE tc.id = ltc.typecimentid AND c.id = ltc.commandeid AND ltc.id = lc.lignetypecommandeid AND lc.id = l.lignecommandeid
AND c.datesuppressioncommande IS NULL AND ltc.datesuppressionlignetypecommande IS NULL AND lc.datesuppressionlignecommande IS NULL AND l.datesuppressionlivraison IS NULL
AND lc.clientid IS NULL AND lc.localiteid IS NULL AND lc.magasinid IS NOT NULL AND tc.produitid = $id $sql ");
}
$total = 0;
$totalpaye = 0;
$prixunitairelignetypecommande = 0;
$qte = 0;
$nombre = 0;
$lignevente = array();
foreach($ventes as $vente){
if ($idproduitelement == -1) {
$total += $vente['barretonnetotal'];
$totalpaye += $vente['prixtypeciment']*intval($vente['tonne']);
$prixunitairelignetypecommande += $vente['prixunitairelignetypecommandetotal'];
$qte += intval($vente['tonne']);
}else{
$total += $vente['barretonne']*$vente['tonne'];
$totalpaye += $vente['totalpaye'];
$prixunitairelignetypecommande += $vente['prixunitairelignetypecommande']*intval($vente['tonne']);
$qte += intval($vente['tonne']);
}
$nombre++;
}
return $this->render('rapport/rapportstockagemagasin.html.twig', [
'TOTAL'=>$total,
'TOTALPAYE'=>$totalpaye,
'COMMANDES' => $ventes,
'NOMBRE'=> $nombre,
'MONTANTTOTAL'=> $prixunitairelignetypecommande,
'idproduitelement'=> $idproduitelement,
'QTE'=> $qte
]);
}
/**
* @Route("/admin/rapportprofitperte", name="rapportprofitperte")
*/
public function rapportprofitperte(ManagerRegistry $doctrine,Request $request,LogActionService $logger): Response
{
$user = $this->getUser();
$em = $doctrine->getManager();
$parametres = $this->container;
$conn = $this->connection;
$modification = true;
$message = "";
$statut = 0;
$color = "red";
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « profit / perte »",
$user ? $user->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'profit_perte', 'filtres' => $request->query->all()]]
);
$idproduitelement = $request->get('idproduitelement');
$filtredetail = $request->get('filtredetail');
$id = $request->get('idproduit');
$statutvente = $request->get('statutvente')??1;
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin');
$client = $request->get('client')??0;
$commercial = $request->get('commercial')??0;
$idmagasin = $request->get('magasinid');
$sql = "";
$sql2 = "";
if(strlen($datedebut)>7){
$sql .= " AND datebordereau >= '$datedebut' ";
$sql2 .= " AND datedepense >= '$datedebut' ";
}
if(strlen($datefin)>7){
$sql .= " AND datebordereau <= '$datefin' ";
$sql2 .= " AND datedepense <= '$datefin 23:59:59' ";
}
$montantversement = $conn->fetchAllAssociative("SELECT IFNULL(SUM(montantversement),0) AS MONTANT FROM bordereau WHERE datesuppressionbordereau IS NULL AND produitid = $this->idproduit $sql");
$sql = "";
if(strlen($datedebut)>7){
$sql .= " AND datepaiement >= '$datedebut' ";
}
if(strlen($datefin)>7){
$sql .= " AND datepaiement <= '$datefin' ";
}
$montantpaiement = $conn->fetchAllAssociative("SELECT IFNULL(SUM(montantpaiement),0) AS MONTANT FROM paiement WHERE datesuppressionpaiement IS NULL AND produitid = $this->idproduit $sql");
$sql = "";
if(strlen($datedebut)>7){
$sql .= " AND datepaiementvente >= '$datedebut' ";
}
if(strlen($datefin)>7){
$sql .= " AND datepaiementvente <= '$datefin' ";
}
$montantpaiementvente = $conn->fetchAllAssociative("SELECT IFNULL(SUM(montantpaiementvente),0) AS MONTANT
FROM paiementvente , vente
WHERE datesuppressionpaiementvente IS NULL
AND paiementvente.venteid = vente.id
AND vente.datesuppressionvente IS NULL
AND vente.dateannulationvente IS NULL
AND paiementvente.produitid = $this->idproduit $sql");
$sql2 = $sql2." AND produitid = ".$this->idproduit;
$montantdepense = $conn->fetchAllAssociative("SELECT IFNULL(SUM(montantdepense),0) AS MONTANT
FROM depense
WHERE datesuppressiondepense IS NULL $sql2");
return $this->render('rapport/rapportprofitperte.html.twig', [
'BORDEREAU'=>$montantversement,
'VENTEDIRECT'=>$montantpaiement,
'VENTEMAGASIN'=>$montantpaiementvente,
'DEPENSEMONTANT'=>$montantdepense,
]);
}
/**
* @Route("/admin/rapportcommercial", name="rapportcommercial")
*/
public function rapportcommercial(ManagerRegistry $doctrine,Request $request,LogActionService $logger): Response
{
$user = $this->getUser();
$em = $doctrine->getManager();
$parametres = $this->container;
$conn = $this->connection;
$modification = true;
$message = "";
$statut = 0;
$color = "red";
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « commerciaux »",
$user ? $user->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'commercial', 'filtres' => $request->query->all()]]
);
$idproduitelement = $request->get('idproduitelement');
$filtredetail = $request->get('filtredetail');
$id = $request->get('idproduit');
$statutvente = $request->get('statutvente')??1;
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin');
$client = $request->get('client')??0;
$commercialid = $request->get('commercialid')??0;
$idmagasin = $request->get('magasinid');
$etatvente = $request->get('etatvente');
$sql = "";
$sql2 = "";
$sql3 = "";
if(strlen($datedebut)>7){
$sql .= " AND datebordereau >= '$datedebut' ";
$sql2 = $sql2." AND l.datecreationlivraison >= '$datedebut'";
$sql3 = $sql3." AND v.datevente >= '$datedebut'";
}
if(strlen($datefin)>7){
$sql .= " AND datebordereau <= '$datefin' ";
$dr = date('Y-m-d', strtotime($datefin . ' +1 day'));
$sql2 = $sql2." AND l.datecreationlivraison <= '$dr'";
$sql3 = $sql3." AND v.datevente <= '$dr'";
}
$montantversement = $conn->fetchAllAssociative("SELECT IFNULL(SUM(montantversement),0) AS MONTANT FROM bordereau WHERE datesuppressionbordereau IS NULL AND produitid = $this->idproduit $sql");
$sql = "";
if(strlen($datedebut)>7){
$sql .= " AND datepaiement >= '$datedebut' ";
}
if(strlen($datefin)>7){
$sql .= " AND datepaiement <= '$datefin' ";
}
$montantpaiement = $conn->fetchAllAssociative("SELECT IFNULL(SUM(montantpaiement),0) AS MONTANT FROM paiement WHERE datesuppressionpaiement IS NULL AND produitid = $this->idproduit $sql");
$sql = "";
if(strlen($datedebut)>7){
$sql .= " AND datepaiementvente >= '$datedebut' ";
}
if(strlen($datefin)>7){
$sql .= " AND datepaiementvente <= '$datefin' ";
}
if($commercialid>0){
$sql2 .= " AND l.commercialid = $commercialid ";
$sql3 .= " AND v.commercialid = $commercialid ";
}
if($etatvente == 1){
$sql2 .= " AND l.apayer > 0 AND l.totalpaye = 0 ";
$sql3 .= " AND v.totalvente > 0 AND v.totalpaye = 0 ";
}
if($etatvente == 2){
$sql2 .= " AND l.apayer > 0 AND l.totalpaye < l.apayer ";
$sql3 .= " AND v.totalvente > 0 AND v.totalpaye < v.totalvente ";
}
if($etatvente == 3){
$sql2 .= " AND l.apayer > 0 AND l.totalpaye >= l.apayer ";
$sql3 .= " AND v.totalvente > 0 AND v.totalpaye >= v.totalvente ";
}
$montantpaiementvente = $conn->fetchAllAssociative("SELECT IFNULL(SUM(montantpaiementvente),0) AS MONTANT
FROM paiementvente , vente
WHERE datesuppressionpaiementvente IS NULL
AND paiementvente.venteid = vente.id
AND vente.datesuppressionvente IS NULL
AND vente.dateannulationvente IS NULL
AND paiementvente.produitid = $this->idproduit $sql");
$requete1 = "SELECT
SUM(l.apayer) AS TOTALAPAYER , SUM(l.totalpaye) AS TOTALPAYER , SUM(l.apayer - l.totalpaye) AS RESTEAPAYER , c.id , c.nom , c.prenom , COUNT(*) AS nombrelivraison
FROM livraison l , commercial c , commande co , lignecommande lc
WHERE
co.produitid = $this->idproduit AND
lc.id = l.lignecommandeid AND c.id = l.commercialid AND l.datesuppressionlivraison is NULL
AND co.id = l.commandeid AND co.datesuppressioncommande IS NULL $sql2 ";
$inventairesAll = $conn->fetchAllAssociative($requete1." ORDER BY TOTALAPAYER DESC");
$inventaires = $conn->fetchAllAssociative($requete1." GROUP BY c.id , c.nom , c.prenom ORDER BY TOTALAPAYER DESC;");
$requete2 = "SELECT
SUM(v.totalvente) AS TOTALAPAYER , SUM(v.totalpaye) AS TOTALPAYER , SUM(v.totalvente - v.totalpaye) AS RESTEAPAYER , c.id , c.nom , c.prenom , COUNT(*) AS nombrelivraison
FROM vente v , commercial c
WHERE
v.produitid = $this->idproduit AND
v.commercialid = c.id AND v.datesuppressionvente is NULL $sql3 ";
$inventairesAll2 = $conn->fetchAllAssociative($requete2." ORDER BY TOTALAPAYER DESC");
$inventaires2 = $conn->fetchAllAssociative($requete2." GROUP BY c.id , c.nom , c.prenom ORDER BY TOTALAPAYER DESC;");
return $this->render('rapport/rapportcommercial.html.twig', [
'INVENTAIRES' => $inventaires,
'ALLINVENTAIRES' => $inventairesAll,
'INVENTAIRES2' => $inventaires2,
'ALLINVENTAIRES2' => $inventairesAll2,
'BORDEREAU'=>$montantversement,
'VENTEDIRECT'=>$montantpaiement,
'VENTEMAGASIN'=>$montantpaiementvente,
]);
}
/**
* @Route("/admin/rapportbordereau", name="rapportbordereau")
*/
public function rapportbordereau(SessionInterface $sessions,Connection $conn,Request $request,LogActionService $logger): Response
{
$ajaxs = false;
$user = $this->getUser();
$em = $this->getDoctrine()->getManager();
$parametres = $this->container;
$sql = "";
$logger->createLog(
'rapport_consultation',
"Consultation du rapport « bordereaux »",
$user ? $user->getId() : null,
null,
null,
['old' => null, 'new' => ['rapport' => 'bordereaux', 'filtres' => $request->query->all()]]
);
$arrayprod = array();
$arrayprod[] = "Ciment";
$arrayprod[] = "Fer à Béton";
$data = array();
$datedebut = $request->get('datedebut');
$datefin = $request->get('datefin');
$etat = $request->get('etat');
if(strlen($datefin)<8){
$datefin = date('Y-m-d')." 23:59:59";
}
$fournisseurid = $request->get('fournisseurid');
$sql = "";
if(strlen($fournisseurid)>0){
$sql .= " AND f.id = $fournisseurid ";
}
if($etat==1){
$sql .= " AND b.id IN (SELECT c.bordereauid FROM commande c WHERE c.datevalidationcommande IS NULL AND c.produitid = $this->idproduit AND c.datesuppressioncommande IS NULL ) ";
}
if($etat==2){
$sql .= " AND b.id IN (SELECT c.bordereauid FROM commande c WHERE c.datevalidationcommande IS NOT NULL AND c.produitid = $this->idproduit AND c.datesuppressioncommande IS NULL ) ";
}
$sommebordereau = $conn->fetchAllAssociative("
SELECT IFNULL(SUM(montantversement),0) AS TT , COUNT(*) AS NT FROM fournisseur f , bordereau b where datebordereau BETWEEN
'$datedebut' AND '$datefin' AND f.id = b.fournisseurid AND datesuppressionbordereau IS NULL AND b.produitid = $this->idproduit $sql ;");
$bordereaus = $conn->fetchAllAssociative("SELECT * ,
IFNULL((SELECT SUM(l.tonne*l.quantite) FROM lignecommande l , commande c where l.commandeid = c.id AND c.bordereauid = b.id AND l.datesuppressionlignecommande is NULL AND c.datesuppressioncommande is NULL),0) AS totaltonne,
IFNULL((SELECT SUM(l.quantite) FROM lignetypecommande l , commande c where l.commandeid = c.id AND c.bordereauid = b.id AND l.datesuppressionlignetypecommande is NULL AND c.datesuppressioncommande is NULL),0) AS totalcommande,
IFNULL((SELECT COUNT(*) FROM commande c where c.bordereauid = b.id AND c.datesuppressioncommande is NULL AND dateannulationcommande is NULL),0) AS nombrebon,
IFNULL((SELECT SUM(l.tonne*l.quantite) FROM livraison ll , lignecommande l , commande c where ll.numlivraison <>'' AND ll.lignecommandeid = l.id AND ll.datesuppressionlivraison IS NULL AND l.commandeid = c.id AND c.bordereauid = b.id AND l.datesuppressionlignecommande is NULL AND c.datesuppressioncommande is NULL),0) AS nombrelivrer
FROM fournisseur f , bordereau b
where datebordereau BETWEEN '$datedebut' AND '$datefin' AND b.datesuppressionbordereau is null AND b.produitid = $this->idproduit
AND f.id = b.fournisseurid $sql
ORDER BY b.id DESC");
$fournisseurs = $em->getRepository(Fournisseur::class)->findBy(['produitid'=>$this->idproduit,'datesuppressionfournisseur'=>null],['nomfournisseur'=>'ASC']);
$data = [
'BORDEREAUS' => $bordereaus,
'FOURNISSEURS'=>$fournisseurs,
'BORDEREAU'=>$sommebordereau
];
return $this->render('rapport/rapportbordereau.html.twig',$data);
}
function recupererDates($option,$ddd="",$ddf="") {
$datedebut = '2000-01-01';
$datefin = date('Y-m-d');
$tab = [];
$tabval = [];
switch ($option) {
case 1: // Aujourd'hui
$datedebut = date('Y-m-d');
$datefin = date('Y-m-d');
$tab[] = $datefin;
$tabval[] = ['dd'=>$datedebut,'df'=>$datefin];
break;
case 2: // Hier
$datedebut = date('Y-m-d', strtotime('yesterday'));
$datefin = $datedebut;
$tab[] = $datefin;
$tabval[] = ['dd'=>$datedebut,'df'=>$datefin];
break;
case 3: // Cette semaine
$datedebut = date('Y-m-d', strtotime('monday this week'));
$datefin = date('Y-m-d');
$currentDate = strtotime($datedebut);
$endDate = strtotime($datefin);
while ($currentDate <= $endDate) {
$c = date('Y-m-d', $currentDate);
$tab[] = $c;
$currentDate = strtotime('+1 day', $currentDate);
$tabval[] = ['dd'=>$c,'df'=>date('Y-m-d', $currentDate)];
}
break;
case 4: // Semaine dernière
$datedebut = date('Y-m-d', strtotime('monday last week'));
$datefin = date('Y-m-d', strtotime('sunday last week'));
$currentDate = strtotime($datedebut);
$endDate = strtotime($datefin);
while ($currentDate <= $endDate) {
$c = date('Y-m-d', $currentDate);
$tab[] = $c;
$currentDate = strtotime('+1 day', $currentDate);
$tabval[] = ['dd'=>$c,'df'=>date('Y-m-d', $currentDate)];
}
break;
case 5: // Ce mois
$datedebut = date('Y-m-01');
$datefin = date('Y-m-t');
$tab = [date('M')];
break;
case 6: // Mois dernier
$datedebut = date('Y-m-01');
$datefin = date('Y-m-t');
//$tab = ['Jan', 'Fév', 'Mar', 'Avr', 'Mai', 'Juin', 'Jul', 'Août', 'Sep', 'Oct', 'Nov', 'Déc'];
for ($i = 0; $i < 12; $i++) {
$tab[] = date('M', strtotime("-".($i-11)." month"));
$dateDebut = date('Y-m-01', strtotime("-".($i-11)." month"));
$dateFin = date('Y-m-t', strtotime("-".($i-11)." month"));
$tabval[] = array('dd' => $dateDebut, 'df' => $dateFin);
}
break;
case 7: // Cette année
$datedebut = date('Y-01-01');
$datefin = date('Y-12-31');
$tab = [date('Y')];
$tabval[] = array('dd' => $datedebut, 'df' => $datefin);
break;
case 8: // Année dernière
$datedebut = date('Y-01-01', strtotime('last year'));
$datefin = date('Y-12-31', strtotime('last year'));
for($i = 0; $i<12; $i++){
$a = intval(date('Y'))-11+$i;
$tab[] = $a;
$dateDebut = date($a.'-01-01');
$dateFin = date($a.'-12-t');
$tabval[] = array('dd' => $dateDebut, 'df' => $dateFin);
}
break;
case 9: // Depuis le début
// Vous devez spécifier votre propre date de début ici
// Par exemple, si votre application a commencé le 1er janvier 2020 :
// $datedebut = '2020-01-01';
// $datefin = date('Y-m-d');
for($i = 0; $i<12 ; $i++){
$a = intval(date('Y'))-11+$i;
$tab[] = $a;
$dateDebut = date($a.'-01-01');
$dateFin = date($a.'-12-t');
$tabval[] = array('dd' => $dateDebut, 'df' => $dateFin);
}
break;
case 10: // Date pre
$datedebut = $ddd;
$datefin = $ddf;
$currentDate = strtotime($datedebut);
$endDate = strtotime($datefin);
$cv = date('Y', $currentDate);
$ce = date('Y', $endDate);
$cv2 = date('Y-m', $currentDate);
$ce2 = date('Y-m', $endDate);
if($cv!=$ce){
for($i = 0; $i<=(intval($ce)-intval($cv)) ; $i++){
$a = intval($cv)+$i;
$tab[] = $a;
$dateDebut = date($a.'-01-01');
$dateFin = date($a.'-12-t');
$tabval[] = array('dd' => $dateDebut, 'df' => $dateFin);
}
}elseif($cv2!=$ce2){
for ($i = 0; $i < 12; $i++) {
$tab[] = date('M', strtotime("-".($i-11)." month"));
$dateDebut = date('Y-m-01', strtotime("-".($i-11)." month"));
$dateFin = date('Y-m-t', strtotime("-".($i-11)." month"));
$tabval[] = array('dd' => $dateDebut, 'df' => $dateFin);
}
}elseif($cv2==$ce2){
while ($currentDate <= $endDate) {
$c = date('Y-m-d', $currentDate);
$tab[] = $c;
$currentDate = strtotime('+1 day', $currentDate);
$tabval[] = ['dd'=>$c,'df'=>date('Y-m-d', $currentDate)];
}
}
break;
default:
// Gérer les options invalides ou non définies ici
for($i = 0; $i<12 ; $i++){
$tab[] = intval(date('Y'))-11+$i;
}
break;
}
return array('datedebut' => $datedebut, 'datefin' => $datefin,'graphe'=>$tab,'grapheval'=>$tabval);
}
/**
* @Route("/auth/restaurants/popularrestaurant", name="popularrestaurant")
*/
public function popularRestaurant(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$restaurants = $conn->fetchAllAssociative("SELECT * FROM restaurant where datesuppressionrestaurant is null LIMIT 20");
shuffle($restaurants);
return new JsonResponse($restaurants);
}
/**
* @Route("/auth/categorie", name="categorieliste")
*/
public function categorieList(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$sql = "";
$data = json_decode($request->getContent(), true);
$boutiqueid = isset($data['boutiqueid']) ? $data['boutiqueid'] : null;
$boutiqueid == null ? $sql = " " : $sql = " OR boutiqueid = $boutiqueid";
$categories = $conn->fetchAllAssociative("SELECT * FROM categorie where datesuppressioncategorie is null AND (boutiqueid is null $sql) ORDER BY boutiqueid DESC , nomcategorie ASC;");
if($boutiqueid==null){
shuffle($categories);
}
return new JsonResponse($categories);
}
/**
* @Route("/auth/restaurantscategorie", name="categorieid")
*/
public function restaurantscategorie(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = $_GET;
$categorieid = isset($data['categorieid']) && strlen($data['categorieid'])>0 ? $data['categorieid'] : '';
$page = isset($data['page']) && strlen($data['page'])>0 ? $data['page'] : 1;
$pagesize = isset($data['pagesize']) && strlen($data['pagesize'])>0 ? $data['pagesize'] : 1;
$offset = intval($pagesize) * (intval($page) - 1);
$sql = "";
if(strlen($categorieid)>0){
$sql = " AND id IN (SELECT p.restaurant_id FROM produit p , categorie c WHERE p.categorie_id = c.id AND c.nomcategorie LIKE '$categorieid' )";
}
$restaurants = $conn->fetchAllAssociative("SELECT * FROM restaurant WHERE datesuppressionrestaurant is null $sql LIMIT $pagesize OFFSET $offset");
$categor = $conn->fetchAllAssociative("SELECT * FROM categorie where datesuppressioncategorie is null ORDER BY boutiqueid ASC , nomcategorie ASC;");
shuffle($restaurants);
$r = array();
$categories = array();
$categories[] = "";
$cats = array();
foreach($categor as $rr){
$categories[] = $rr['nomcategorie'];
$cat = array();
$cat['title'] = $rr['nomcategorie'];
$cats[] = $cat;
}
foreach($restaurants as $rr){
$cat = array();
$cat['title'] = $rr['name'];
$rr['categories'] = [];
$r[] = $rr;
}
$restaurants = $r;
$restaurant = null;
if(sizeof($restaurants)>0){
$restaurant = $restaurants[0];
//$restaurants = array_merge($restaurants, $restaurants);
$restaurant['data'] = $restaurants;
}
return new JsonResponse(['status'=>200,'data'=>['restaurants'=>$restaurant,'categories'=>$categories]]);
}
/**
* @Route("/auth/restaurants/{id}", name="restaurants")
*/
public function restaurants(Connection $conn,Request $request,$id=0): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$sql = "";
$sql2 = "";
if($id>0){
$sql = " AND id = $id";
$sql2 = " AND restaurant_id = $id";
}
$restaurants = $conn->fetchAllAssociative("SELECT * FROM restaurant WHERE 1=1 $sql AND datesuppressionrestaurant is null LIMIT 1");
$products = $conn->fetchAllAssociative("SELECT produit.* , restaurant.name AS restaurant , restaurant.logo AS logoresto FROM produit , restaurant WHERE restaurant.id = produit.restaurant_id $sql2 LIMIT 20");
$restau = [];
foreach($restaurants as $r){
$r['product'] = $products;
$json_data = $r['horairevendeur'];
$data = json_decode($json_data, true);
setlocale(LC_TIME, 'fr_FR.utf8');
$jour_actuel = ucfirst(strftime('%A'));
if (array_key_exists($jour_actuel, $data)) {
$message_du_jour = $data[$jour_actuel]['message'];
$r['aujourdhui'] = $message_du_jour;
}else{
$r['aujourdhui'] = "Toute la journée";
}
$restau [] = $r;
}
return new JsonResponse(['data'=>$restau]);
}
/**
* @Route("/auth/products/popularproduct", name="popularproduct")
*/
public function popularproduct(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$produits = $conn->fetchAllAssociative("SELECT produit.* , restaurant.name AS restaurant , restaurant.logo AS logoresto FROM produit , restaurant WHERE restaurant.id = produit.restaurant_id LIMIT 20");
shuffle($produits);
return new JsonResponse($produits);
}
/**
* @Route("/auth/products", name="products")
*/
public function products(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$categorieid = isset($data['categorieid']) && strlen($data['categorieid'])>0 ? $data['categorieid'] : '';
$restaurantid = isset($data['restaurantid']) && strlen($data['restaurantid'])>0 ? $data['restaurantid'] : '';
$page = isset($data['page']) && strlen($data['page'])>0 ? $data['page'] : 1;
$pagesize = isset($data['pagesize']) && strlen($data['pagesize'])>0 ? $data['pagesize'] : 1;
$offset = intval($pagesize) * (intval($page) - 1);
$sql = "";
if(strlen($categorieid)>0){
$sql .= " AND categorie_id = $categorieid";
}
if(strlen($restaurantid)>0){
$sql .= " AND restaurant_id = $restaurantid";
}
$produits = $conn->fetchAllAssociative("SELECT produit.* , restaurant.name AS restaurant , restaurant.logo AS logoresto
FROM produit ,restaurant where datesuppressionproduit is null and restaurant.id = produit.restaurant_id and datevalidationproduit is not null $sql LIMIT $pagesize OFFSET $offset;");
return new JsonResponse($produits);
}
/**
* @Route("/auth/products/{id}/reviews", name="productsreviews")
*/
public function productsreviews(Connection $conn,Request $request,$id=0): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$rate = isset($data['rate']) ? $data['rate'] : 4;
$avis = $conn->fetchAllAssociative("SELECT * FROM avis WHERE rating_for_id = $id AND rate = $rate AND deleted_at is NULL ORDER BY id DESC LIMIT 30");
$a = array();
foreach($avis as $av){
$user = $conn->fetchAllAssociative("SELECT * FROM user where id = ".$av['user_id']);
$av['user'] = isset($user[0])?$user[0]:null;
$a[] = $av;
}
return new JsonResponse($a);
}
/**
* @Route("/auth/products/{id}/review", name="productsreview")
*/
public function productsreview(Connection $conn,Request $request,$id=0): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$user = isset($data['user']) ? $data['user'] : 0;
$rate = isset($data['rate']) ? $data['rate'] : 4;
$feedback = isset($data['feedback']) ? $data['feedback'] : "";
if(strlen(trim($feedback))>0){
$client = $em->getRepository(User::class)->findOneBy(['produitid'=>$this->idproduit,"id"=>$user]);
$avis = new Avis();
$avis->setUserId($client);
$avis->setRatingForId($id);
$avis->setRate($rate);
$avis->setFeedback($feedback);
$avis->setCreatedAt(new \DateTime());
$avis->setUpdatedAt(new \DateTime());
$em->persist($avis);
$em->flush();
}
$avis = $conn->fetchAllAssociative("SELECT * FROM avis WHERE rating_for_id = $id AND deleted_at is NULL ORDER BY id DESC LIMIT 20");
$conn->executeUpdate("UPDATE produit p set p.numrating = (select count(*) FROM avis where avis.rating_for_id = p.id) , p.rating = (select AVG(avis.rate) FROM avis where avis.rating_for_id = p.id) WHERE p.id = $id;");
return new JsonResponse($avis);
}
/**
* @Route("/auth/categories", name="categories")
*/
public function categories(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$restaurants = $conn->fetchAllAssociative("SELECT * FROM restaurant where datesuppressionrestaurant is null");
return new JsonResponse($restaurants);
}
/**
* @Route("/auth/search", name="search")
*/
public function search(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = strtolower($_GET['search_query']);
$produits = $conn->fetchAllAssociative("SELECT produit.* , restaurant.name from AS restaurant , restaurant.logo AS logoresto FROM produit , restaurant where restaurant.id = produit.restaurant_id AND LOWER(produit.name) LIKE '%$data%' ");
return new JsonResponse(['Data'=>$_GET,'Products'=>$produits]);
}
/**
* @Route("/auth/filter", name="filter")
*/
public function filter(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
//{category: BFC, idrestaurant: 3, price: 40506, pricemin: 40506, rating: 4}
$data = json_decode($request->getContent(), true);
$category = $data['category'];
$idrestaurant = $data['idrestaurant'];
$idcategorie = $data['idcategorie'];
$price = $data['price'];
$pricemin = $data['pricemin'];
$rating = $data['rating'];
$sql = "";
if(intval($idrestaurant)>0){
$sql .= " AND restaurant_id = $idrestaurant ";
}
if(intval($idcategorie)>0){
$sql .= " AND categorie_id = $idcategorie ";
}
if(intval($rating)>0){
$sql .= " AND produit.rating = $rating ";
}
$produits = $conn->fetchAllAssociative("SELECT produit.* , restaurant.name AS restaurant , logo AS logoresto FROM produit , restaurant where restaurant.id = produit.restaurant_id AND price BETWEEN $pricemin AND $price $sql ORDER BY produit.name ASC LIMIT 50;");
return new JsonResponse($produits);
}
/**
* @Route("/auth/resto", name="resto")
*/
public function resto(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = strtolower($_GET['search_query']);
$restaurants = $conn->fetchAllAssociative("SELECT * FROM restaurant where LOWER(name) LIKE '%$data%' ");
return new JsonResponse(['Data'=>$_GET,'Restaurant'=>$restaurants]);
}
/**
* @Route("/auth/discounts", name="discounts")
*/
public function discounts(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$promos = array();
$data = $_GET;
$date = date('Y-m-d', strtotime(' -1 days'));
$date2 = date('Y-m-d', strtotime(' -0 days'));
$page = isset($data['page']) && strlen($data['page'])>0 ? $data['page'] : 1;
$pagesize = isset($data['pagesize']) && strlen($data['pagesize'])>0 ? $data['pagesize'] : 1;
$offset = intval($pagesize) * (intval($page) - 1);
for($i=1;$i<3;$i++){
$promotion = $conn->fetchAllAssociative("SELECT * FROM promotion where typer = $i AND datesuppressionpromotion IS NULL
AND datecreationpromotion <= '$date' AND deadline >= '$date2' LIMIT $pagesize OFFSET $offset");
$promo = array();
foreach($promotion as $pro){
$prod = $conn->fetchAllAssociative("SELECT produit.* , restaurant.name AS restaurant , logo AS logoresto FROM produit ,restaurant where restaurant.id = produit.restaurant_id AND produit.id = ".$pro['product_id']);
if(sizeof($prod)>0){
$pro["product"] = $prod[0];
$promo[] = $pro;
}
}
shuffle($promo);
$promos["P$i"] = $promo;
}
return new JsonResponse($promos);
$data = json_decode($request->getContent(), true);
}
/**
* @Route("/auth/user/addtocart", name="addtocart")
*/
public function addtocart(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$quantity = $data['quantity'];
$product_id = $data['product_id'];
$token = $data['token'];
//$produit = $conn->fetchAllAssociative("SELECT * FROM produit where id = $product_id;");
$p = 0;
$produit = $em->getRepository(Produit::class)->findOneBy(["id"=>$product_id]);
$panier = $em->getRepository(Panier::class)->findOneBy(['produitid'=>$this->idproduit,"product_id"=>$product_id,'token'=>$token]);
if($produit!=null && $panier==null){
$panier = new Panier();
$panier->setOrderId(0);
$panier->setName($produit->getName());
$panier->setImage($produit->getImage());
$panier->setQuantity($quantity);
$panier->setUnitprice($produit->getPrice());
$panier->setPrice(intval($quantity)*intval($produit->getPrice()));
$panier->setToken($token);
$panier->setProductId($produit);
$panier->setDateajout(new \DateTime());
$em->persist($panier);
$em->flush();
$p = $panier->getId();
}else if($panier!=null){
$panier->setQuantity($quantity);
$panier->setUnitprice($produit->getPrice());
$panier->setPrice(intval($quantity)*intval($produit->getPrice()));
$panier->setDateajout(new \DateTime());
$em->persist($panier);
$em->flush();
$p = $panier->getId();
}
$panier = $conn->fetchAllAssociative("SELECT * , IFNULL((select r.name FROM restaurant r , produit p where p.restaurant_id = r.id AND panier.product_id = p.id),'-') AS restaurant FROM panier where id = $p;");
return new JsonResponse($panier);
}
/**
* @Route("/auth/user/deletefromcart/{id}/{token}", name="deletefromcart")
*/
public function deletefromcart(Connection $conn,Request $request,$id=0,$token=""): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$panier = $em->getRepository(Panier::class)->findOneBy(['produitid'=>$this->idproduit,"id"=>$id,'token'=>$token]);
if($panier!=null){
$em->remove($panier);
$em->flush();
}
$panier = $conn->fetchAllAssociative("SELECT * FROM panier where id = $p;");
return new JsonResponse($panier);
}
/**
* @Route("/auth/user/deletefromcartresto/{id}/{token}", name="deletefromcartresto")
*/
public function deletefromcartresto(Connection $conn,Request $request,$id=0,$token=""): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$panier = $em->getRepository(Panier::class)->findOneBy(['produitid'=>$this->idproduit,"id"=>$id,'token'=>$token]);
if($panier!=null){
$em->remove($panier);
$em->flush();
}
$panier = $conn->fetchAllAssociative("SELECT * FROM panier where id = $p;");
return new JsonResponse($panier);
}
/**
* @Route("/auth/user/checkout", name="checkout")
*/
public function checkout(Connection $conn,Request $request): Response
{
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$panier = isset($data['panier']) ? $data['panier'] : [];
$token = isset($data['token']) ? $data['token'] : '';
$adresse = isset($data['adresse']) ? $data['adresse'] : '';
$user = isset($data['user']) ? intval($data['user']) : 1;
$lastcmd = $em->getRepository(Commande::class)->findOneBy(['produitid'=>$this->idproduit,],['id'=>'DESC']);
$codecommande = $lastcmd == null ? 1 : $lastcmd->getId()+1;
$idcmd = 0;
if(sizeof($panier)>0){
$commande = new Commande();
$commande->setDatecommande(new \DateTime());
$commande->setAdresselivraison($adresse);
$commande->setUserid($em->getRepository(User::class)->findOneBy(['produitid'=>$this->idproduit,"id"=>$user]));
$commande->setCodecommande("COM-BR-".$codecommande);
$em->persist($commande);
$em->flush();
$total = 0 ;
$boutik = null;
foreach($panier as $element){
$lignecommande = new Lignecommande();
$lignecommande->setQuantite($element['quantity']);
$lignecommande->setPu($element['unitprice']);
$lignecommande->setPrixtotal($element['quantity']*$element['unitprice']);
$lignecommande->setIdcommande($commande);
$prd = $em->getRepository(Produit::class)->findOneBy(["id"=>$element['product_id']]);
$lignecommande->setIdproduit($prd);
if($prd!=null){
$boutik = $prd->getRestaurantId();
}
$em->persist($lignecommande);
$em->flush();
$total = $total + ($element['quantity']*$element['unitprice']);
}
$commande->setRestaurantId($boutik);
$commande->setTotalcommande(intval($total));
$commande->setFraislivraison(0);
$em->persist($commande);
$em->flush();
}
//$commande = $conn->fetchAllAssociative("SELECT * , (select CONCAT(r.logo,'||||',r.name) FROM restaurant r , commande c , produit p , lignecommande lc where r.id = p.restaurant_id AND c.id = commande.id AND c.id = lc.idcommande AND p.id = lc.idproduit LIMIT 1) AS logorestaurant FROM commande WHERE userid = $user ORDER BY id DESC LIMIT 60;");
$commande = $conn->fetchAllAssociative("SELECT * FROM commande c WHERE userid = $user ORDER BY id DESC LIMIT 60;");
$c = array();
foreach($commande as $com){
$datacommande = $conn->fetchAllAssociative("SELECT r.name AS restaurant , r.logo , p.name AS produit , p.image , l.* FROM lignecommande l , produit p , restaurant r
where r.id = p.restaurant_id AND p.id = l.idproduit AND l.idcommande = ".$com['id']);
$com['produits'] = $datacommande;
$c[] = $com;
}
return new JsonResponse($c);
}
/**
* @Route("/auth/user/orders", name="orders")
*/
public function orders(Connection $conn,Request $request): Response
{
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$token = isset($data['token']) ? $data['token'] : '';
$user = isset($data['user']) ? $data['user'] : 0;
$commande = $conn->fetchAllAssociative("SELECT * FROM commande c WHERE userid = $user ORDER BY id DESC LIMIT 60;");
$c = array();
foreach($commande as $com){
$datacommande = $conn->fetchAllAssociative("SELECT r.name AS restaurant , r.logo , p.name AS produit , p.image , l.* FROM lignecommande l , produit p , restaurant r
where r.id = p.restaurant_id AND p.id = l.idproduit AND l.idcommande = ".$com['id']);
$com['produits'] = $datacommande;
$c[] = $com;
}
return new JsonResponse($c);
}
/**
* Legacy : ancienne route notifications héritée (table `notification` du projet
* resto/food). Redirige vers le nouveau système app_notification.
*
* @Route("/auth/user/notifications", name="notifications")
*/
public function notifications(Connection $conn,Request $request): Response
{
return $this->redirectToRoute('app_notifications');
// ⬇️ Code legacy conservé pour mémoire (table `notification` supprimée). ⬇️
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$token = isset($data['token']) ? $data['token'] : '';
$user = isset($data['user']) ? $data['user'] : 0;
$dernier = isset($data['dernier']) ? $data['dernier'] : 0;
$date = date('Y-m-d', strtotime(' -7 days'));
$commande = $conn->fetchAllAssociative("
SELECT *
,IFNULL((select name from restaurant where id = notification.restaurantid),'') AS restaurant
,IFNULL((select name from produit where id = notification.platid),'') AS plat
,IFNULL((select codecommande from commande where id = notification.commandeid),'') AS commande
FROM notification
WHERE (userid = $user OR userid is NULL)
AND datesuppressionnotification IS NULL
AND datenotification > '$date'
AND id > $dernier
ORDER BY id DESC LIMIT 10;");
$dam = array();
foreach($commande as $c){
$el = array();
$el['notif'] = $c;
$el['data'] = null;
if($c['platid']!=null){
$da = $conn->fetchAllAssociative("SELECT produit.* , restaurant.name AS restaurant , logo AS logoresto FROM produit , restaurant WHERE restaurant.id = produit.restaurant_id AND produit.id = ".$c['platid']);
$el['data'] = sizeof($da)>0 ? $da[0] : null;
}
if($c['restaurantid']!=null){
$da = $conn->fetchAllAssociative("SELECT * FROM restaurant WHERE id = ".$c['restaurantid']);
$el['data'] = sizeof($da)>0 ? $da[0] : null;
}
if($c['commandeid']!=null){
$da = $conn->fetchAllAssociative("SELECT * FROM commande WHERE id = ".$c['commandeid']);
$el['data'] = sizeof($da)>0 ? $da[0] : null;
}
$dam[] = $el;
}
return new JsonResponse($dam);
return new JsonResponse($commande);
}
/**
* @Route("/auth/connexion", name="connexion")
*/
public function connexion(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$type = isset($data['type']) ? $data['type'] : '';
$dataauth = isset($data['data']) ? $data['data'] : '';
$email = isset($data['email']) ? $data['email'] : '';
$password = isset($data['password']) ? $data['password'] : '';
$token = md5(date('Ymdhis')."stephaneogou@gmail.com");
//$email = "stephaneogou@gmail.com";
$data = array();
$data['access_token'] = $token;
$data['token_type'] = 'https';
$data['expires_in'] = 2;
$user = $em->getRepository(User::class)->findOneBy(['produitid'=>$this->idproduit,"email"=>$email]);
if($user!=null){
if($type=="gmail"){
//User(displayName: Ogou Stephane, email: stephaneogou@gmail.com, isEmailVerified: true, isAnonymous: false, metadata: UserMetadata(creationTime: 2024-03-16 04:46:01.365Z, lastSignInTime: 2024-03-16 05:20:19.986Z), phoneNumber: null, photoURL: https://lh3.googleusercontent.com/a/ACg8ocJuZTJ5og1YAhYtqcymKer9i8vo7ByyP69S8vrrIXRK2zg=s96-c, providerData, [UserInfo(displayName: Ogou Stephane, email: stephaneogou@gmail.com, phoneNumber: null, photoURL: https://lh3.googleusercontent.com/a/ACg8ocJuZTJ5og1YAhYtqcymKer9i8vo7ByyP69S8vrrIXRK2zg=s96-c, providerId: google.com, uid: 101736167964474266692)], refreshToken: null, tenantId: null, uid: QZO4IHQfN7RD97e2Z0xjuiM0hD13)
$user->setIdgmail($dataauth);
}
$user->setTypecon($type);
$user->setToken($token);
$user->setLastLogin(new \DateTime());
$em->persist($user);
$em->flush();
}
$user = $conn->fetchAllAssociative("SELECT * FROM user where email = '$email';");
$data['user'] = isset($user[0])?$user[0]:null;
return new JsonResponse($data);
}
/**
* @Route("/auth/register", name="authregister")
*/
public function authregister(Connection $conn,Request $request,TokenAuthenticator $authenticator, GuardAuthenticatorHandler $guardHandler,AuthenticationUtils $authenticationUtils, UserPasswordHasherInterface $userPasswordHasher): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
$type = isset($data['type']) ? $data['type'] : '';
$dataauth = isset($data['data']) ? $data['data'] : '';
$email = isset($data['email']) ? $data['email'] : '';
$password = isset($data['password']) ? $data['password'] : '';
$name = isset($data['name']) ? $data['name'] : '';
$token = md5(date('Ymdhis')."stephaneogou@gmail.com");
//$email = "stephaneogou@gmail.com";
$data = array();
$data['access_token'] = $token;
$data['token_type'] = 'https';
$data['expires_in'] = 2;
$user = $em->getRepository(User::class)->findOneBy(['produitid'=>$this->idproduit,"email"=>$email]);
if($email!="" && $user==null){
$utilisateur = new User();
$utilisateur->setEmail($email);
$utilisateur->setPseudo($email);
$utilisateur->setName($name);
$utilisateur->setPhonenumber("");
$utilisateur->setRoles(["ROLE_CLIENT"]);
$utilisateur->setRestaurantid(null);
$utilisateur->setDatecreationuser(new \DateTime());
$utilisateur->setMdp($password);
$utilisateur->setPassword($userPasswordHasher->hashPassword($utilisateur,$password));
$utilisateur->setIsverified(true);
$em->persist($utilisateur);
$em->flush();
}
$user = $em->getRepository(User::class)->findOneBy(['produitid'=>$this->idproduit,"email"=>$email]);
if($user!=null){
if($type=="gmail"){
//User(displayName: Ogou Stephane, email: stephaneogou@gmail.com, isEmailVerified: true, isAnonymous: false, metadata: UserMetadata(creationTime: 2024-03-16 04:46:01.365Z, lastSignInTime: 2024-03-16 05:20:19.986Z), phoneNumber: null, photoURL: https://lh3.googleusercontent.com/a/ACg8ocJuZTJ5og1YAhYtqcymKer9i8vo7ByyP69S8vrrIXRK2zg=s96-c, providerData, [UserInfo(displayName: Ogou Stephane, email: stephaneogou@gmail.com, phoneNumber: null, photoURL: https://lh3.googleusercontent.com/a/ACg8ocJuZTJ5og1YAhYtqcymKer9i8vo7ByyP69S8vrrIXRK2zg=s96-c, providerId: google.com, uid: 101736167964474266692)], refreshToken: null, tenantId: null, uid: QZO4IHQfN7RD97e2Z0xjuiM0hD13)
$user->setIdgmail($dataauth);
}
$user->setTypecon($type);
$user->setToken($token);
$user->setLastLogin(new \DateTime());
$em->persist($user);
$em->flush();
}
$user = null; //$conn->fetchAllAssociative("SELECT * FROM user where email = '$email';");
$data['user'] = isset($user[0])?$user[0]:null;
return new JsonResponse($data);
}
/**
* @Route("/auth/logout/{iduser}", name="logout")
*/
public function logout(Connection $conn,Request $request,$iduser=0): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
$user = $conn->fetchAllAssociative("SELECT * FROM user where id = $iduser;");
$data['user'] = isset($user[0])?$user[0]:null;
$user = $em->getRepository(User::class)->findOneBy(['produitid'=>$this->idproduit,"id"=>$iduser]);
if($user!=null){
$user->setToken("");
$user->setLastLogout(new \DateTime());
$em->persist($user);
$em->flush();
}
return new JsonResponse($data);
}
/**
* @Route("/mon-profil", name="mon_profil")
*/
public function monProfil(
Request $request,
UserPasswordHasherInterface $userPasswordHasher,
\App\Service\DeuxFacteursService $deuxFacteurs
): Response {
$user = $this->getUser();
if ($user === null) {
return $this->redirectToRoute('login');
}
$em = $this->getDoctrine()->getManager();
$message = '';
$etat = '';
if ($request->getMethod() === 'POST') {
$action = $request->request->get('action', 'profil');
if ($action === 'profil') {
$name = trim((string) $request->request->get('name', ''));
$phone = trim((string) $request->request->get('phone', ''));
$user->setName($name !== '' ? $name : null);
$user->setPhoneNumber($phone !== '' ? $phone : null);
$image = $request->files->get('image');
if ($image) {
$profil = $this->fichierAction($image, '/images/client');
$user->setImage($profil);
}
$em->persist($user);
$em->flush();
$message = 'Profil mis à jour avec succès.';
$etat = 'success';
} elseif ($action === 'motdepasse') {
$actuel = (string) $request->request->get('motdepasseactuel', '');
$nouveau = (string) $request->request->get('nouveaumotdepasse', '');
$confirm = (string) $request->request->get('confirmermotdepasse', '');
if ($actuel !== $user->getMdp()) {
$message = 'Votre mot de passe actuel est incorrect.';
$etat = 'danger';
} elseif (strlen($nouveau) < 4) {
$message = 'Le nouveau mot de passe doit contenir au moins 4 caractères.';
$etat = 'warning';
} elseif ($nouveau !== $confirm) {
$message = 'Le nouveau mot de passe et sa confirmation ne correspondent pas.';
$etat = 'warning';
} else {
$passnew = $userPasswordHasher->hashPassword($user, $nouveau);
$user->setMdp($nouveau);
$user->setPassword($passnew);
$em->persist($user);
$em->flush();
$message = 'Mot de passe modifié avec succès.';
$etat = 'success';
}
}
}
return $this->render('profil/index.html.twig', [
'USER' => $user,
'message' => $message,
'etat' => $etat,
// Double authentification : état affiché dans la section dédiée.
'codes_secours_restants' => $user->isTotpActif()
? $deuxFacteurs->codesSecoursRestants($user)
: 0,
'appareils_de_confiance' => $user->isTotpActif()
? $deuxFacteurs->appareils($user)
: [],
]);
}
/**
* @Route("/auth/user/updateprofile", name="updateprofile")
*/
public function updateprofile(Connection $conn,Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
// Récupérer les données de la requête
/* $data = json_decode($request->getContent(), true);
$image = isset($data['image']) ? $data['image'] : false;
$name = isset($data['name']) ? $data['name'] : '';
$phone = isset($data['phone']) ? $data['phone'] : '';
$token = isset($data['token']) ? $data['token'] : '';
$userId = isset($data['user']) ? $data['user'] : 0;*/
$image = $request->files->get('image');
$name = $request->request->get('name');
$phone = $request->request->get('phone');
$token = $request->request->get('token');
$userId = $request->request->get('user');
$user = $em->getRepository(User::class)->findOneBy(['produitid'=>$this->idproduit,"id"=>$userId]);
if($user!=null){
if ($image) {
$profil = $this->fichierAction($image,'/images/client');
$user->setImage($profil);
}
$user->setToken($token);
$user->setName($name);
$user->setPhonenumber($phone);
$em->persist($user);
$em->flush();
}
$user = $conn->fetchAllAssociative("SELECT * FROM user where id = $userId;");
$data['data'] = isset($user[0])?$user[0]:null;
return new JsonResponse($data);
}
/**
* @Route("/privacy", name="privacy")
*/
public function privacy(Request $request): Response
{
$ajaxs = false;
$em = $this->getDoctrine()->getManager();
return $this->render('accueil/privacy.html.twig');
}
function decrypt($data) {
$keyBytes = utf8_encode($this->keypass);
$inputBytes = base64_decode($data);
// Extract the original input bytes (excluding the HMAC digest)
$originalInputBytes = substr($inputBytes, 0, -32);
// Extract the HMAC digest from the end of the input bytes
$hmacDigest = substr($inputBytes, -32);
$data = json_decode($request->getContent(), true);
// Recalculate the HMAC digest using the provided secret key
$calculatedDigest = hash_hmac('sha256', $originalInputBytes, $keyBytes, true);
$return = "";
// Compare the calculated digest with the extracted digest
if (hash_equals($calculatedDigest, $hmacDigest)) {
$return = utf8_decode($originalInputBytes);
} else {
$return = "NOEXISTNUMBER"; // HMAC verification failed
}
return $return;
}
/**
* @Route("/mobile/addmessage", name="addmessage")
*/
public function addmessage(Connection $conn,Request $request): Response
{
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
// Utilisez les données comme nécessaire
$msg = "Aucun";
if($request->getMethod() == 'POST' && isset($data['message'])){
$msg = $data['message'];
$receveur = $this->decrypt($data['receveur']);
$envoyeur = $this->decrypt($data['envoyeur']);
$datecreation = $data['datecreation'];
$declenche = $data['declenche'];
$keymessage = $this->decrypt($data['keymessage']);
$messages = $conn->fetchAllAssociative("SELECT * FROM message
where ((RIGHT(envoyeur,8) = RIGHT($envoyeur,8) AND RIGHT(receveur,8) = RIGHT($receveur,8))
OR (RIGHT(receveur,8) = RIGHT($envoyeur,8) AND RIGHT(envoyeur,8) = RIGHT($receveur,8))) AND keymessage = '$keymessage'
LIMIT 1");
$declenche = 0;
//$key = md5($datecreation);
if((sizeof($messages)>0 && $messages[0]['envoyeur']==$envoyeur)|| sizeof($messages) == 0){
$declenche = 1;
}
$message = new Message();
$message->setReceveur($receveur);
$message->setEnvoyeur($envoyeur);
$message->setMessage($msg);
$message->setDatecreation(new \DateTime());
$message->setLu(0);
$message->setDeclenche($declenche);
$message->setKeymessage($keymessage);
$em->persist($message);
$em->flush();
}
// ... faites quelque chose avec les données ...
return new Response('Données reçues avec succès : '.$msg.' | '.$request->getMethod().' | '.$request->getContent(), 200);
}
/**
* @Route("/mobile/getmessage", name="getmessage")
*/
public function getmessage(Connection $conn,Request $request): Response
{
$em = $this->getDoctrine()->getManager();
$data = json_decode($request->getContent(), true);
// Utilisez les données comme nécessaire
$msg = "fail";
$messages = null;
if($request->getMethod() == 'POST' && isset($data['telephone'])){
//$messages = $em->getRepository(Message::class)->findBy(['produitid'=>$this->idproduit,"envoyeur"=>$data['telephone']]);
$numero = $this->decrypt($data['telephone']);
$dernierID = $data['dernierID'];
$str = " envoyeur LIKE '%$numero' OR receveur LIKE '%$numero' ";
$table = [];
for($i=1; $i<5;$i++){
if(strlen($numero)>$i){
$table[] = substr($numero,$i,strlen($numero));
}
}
foreach($table as $t){
$str = $str." OR envoyeur LIKE '%$t' OR receveur LIKE '%$t' ";
}
$messages = $conn->fetchAllAssociative("SELECT * FROM message where id > $dernierID AND datesuppression is null AND ($str) LIMIT 20");
$msg = "success";
}
// ... faites quelque chose avec les données ...
return new JsonResponse(['msg'=>$msg,'messages'=>$messages]);
}
public function trueData($datatable){
$serializer = $this->container->get('serializer');
$reports = $serializer->serialize($datatable, 'json');
return $reports;
}
public function fichierAction($file,$chemin='/images/client'){
$nomfichier = date('YmdHis')."-".str_replace(" ","-",$file->getClientOriginalName());
$fileSize = $file->getMaxFilesize();
$extension = $file->getClientOriginalExtension();
$minetype = $file->getClientMimeType();
$i = 1;
$newnomfichier = str_replace(".".$extension,"",$nomfichier);
$defutnomfichier = str_replace(".".$extension,"",$nomfichier);
$status = array('status' => "success","fileUploaded" => false);
if(!is_null($file)){
$path = $this->getParameter('kernel.project_dir')."/public".$chemin;
$file->move($path,$nomfichier); // move the file to a path
$status = array('status' => "success","fileUploaded" => true);
}
return $nomfichier;
}
/**
* @Route("/preferences/theme", name="user_theme_update", methods={"POST"})
*/
public function updateUserTheme(Request $request): JsonResponse
{
$user = $this->getUser();
if (!$user instanceof User) {
return new JsonResponse(['status' => 'error', 'message' => 'Non authentifie'], 401);
}
$theme = $request->request->get('theme');
if (!in_array($theme, ['light', 'dark'], true)) {
return new JsonResponse(['status' => 'error', 'message' => 'Theme invalide'], 400);
}
$em = $this->getDoctrine()->getManager();
$user->setTheme($theme);
$em->persist($user);
$em->flush();
return new JsonResponse(['status' => 'success', 'theme' => $theme]);
}
/**
* I18N-01 — Changement de la langue d'interface par l'utilisateur.
*
* Deux écritures indissociables : le compte (pour retrouver sa langue à
* la prochaine connexion, sur n'importe quel appareil) et la session
* (pour que la page rechargée juste après soit déjà dans la bonne
* langue). Écrire l'un sans l'autre donne un changement qui « ne prend
* pas », ou qui s'oublie au matin.
*
* @Route("/preferences/langue", name="user_locale_update", methods={"POST"})
*/
public function updateUserLocale(Request $request, LogActionService $journal): JsonResponse
{
$user = $this->getUser();
if (!$user instanceof User) {
return new JsonResponse(['status' => 'error', 'message' => 'Non authentifie'], 401);
}
$locale = $request->request->get('locale');
if (!is_string($locale) || !in_array($locale, User::LOCALES_SUPPORTEES, true)) {
return new JsonResponse(['status' => 'error', 'message' => 'Langue non prise en charge'], 400);
}
$avant = $user->getLocale();
if ($avant === $locale) {
// Rien à écrire : on ne pollue pas le journal d'actions avec des
// clics qui ne changent rien.
return new JsonResponse(['status' => 'success', 'locale' => $locale]);
}
$em = $this->getDoctrine()->getManager();
$user->setLocale($locale);
$em->persist($user);
$em->flush();
$request->getSession()->set(LocaleSubscriber::CLE_SESSION, $locale);
$journal->audit(
'PREFERENCE_LANGUE',
$user,
['locale' => $avant],
['locale' => $locale],
sprintf('Langue d\'interface changee de %s a %s', $avant, $locale)
);
return new JsonResponse(['status' => 'success', 'locale' => $locale]);
}
}

src/Controller/AccueilController.php line 330