Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\ProduitDeclinationValue;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use App\Entity\ValueDeclination;
/**
* @method ProduitDeclinationValue|null find($id, $lockMode = null, $lockVersion = null)
* @method ProduitDeclinationValue|null findOneBy(array $criteria, array $orderBy = null)
* @method ProduitDeclinationValue[] findAll()
* @method ProduitDeclinationValue[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class ProduitDeclinationValueRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, ProduitDeclinationValue::class);
}
public function findProduitGroup(
$page,
$limit,
int $idProduit,
?string $reference,
array $declinations = [], // [declinationId => valueId|valueId[]]
bool $withDeleted = false
) {
$qb = $this->createQueryBuilder('p')
->leftJoin('p.produit', 'pp')
->andWhere('pp.id = :produit')->setParameter('produit', $idProduit);
if ($reference) {
$qb->andWhere('UPPER(p.reference) LIKE :reference')
->setParameter('reference', '%'.mb_strtoupper($reference).'%');
}
// AND entre chaque declination
$i = 0;
foreach ($declinations as $declId => $val) {
if ($val === null || $val === '' || $val === [] ) { continue; }
$i++;
$alias = 'gdv'.$i;
$paramDecl = 'd'.$i;
$paramVal = 'v'.$i;
$vals = is_array($val) ? array_values(array_unique($val)) : [ (int)$val ];
// innerJoin = critère obligatoire
$qb->innerJoin('p.groupDeclinationValues', $alias, 'WITH',
$alias.'.declination = :'.$paramDecl.' AND '.$alias.'.value IN (:'.$paramVal.')'
)
->setParameter($paramDecl, (int)$declId)
->setParameter($paramVal, $vals);
}
if (!$withDeleted) {
$qb->andWhere('pp.deletedAt IS NULL');
}
$qb->orderBy('p.reference', 'ASC');
if ($page !== false) {
$qb->setMaxResults($limit)
->setFirstResult($page * $limit);
}
return $qb->getQuery()->getResult();
}
public function countProduitGroup(
int $idProduit,
?string $reference,
array $declinations = [], // [declinationId => valueId|valueId[]]
bool $withDeleted = false
) {
$qb = $this->createQueryBuilder('p')
->select('COUNT(p)')
->leftJoin('p.produit', 'pp')
->andWhere('pp.id = :produit')->setParameter('produit', $idProduit);
if ($reference) {
$qb->andWhere('UPPER(p.reference) LIKE :reference')
->setParameter('reference', '%'.mb_strtoupper($reference).'%');
}
$i = 0;
foreach ($declinations as $declId => $val) {
if ($val === null || $val === '' || $val === [] ) { continue; }
$i++;
$alias = 'gdv'.$i;
$paramDecl = 'd'.$i;
$paramVal = 'v'.$i;
$vals = is_array($val) ? array_values(array_unique($val)) : [ (int)$val ];
$qb->innerJoin('p.groupDeclinationValues', $alias, 'WITH',
$alias.'.declination = :'.$paramDecl.' AND '.$alias.'.value IN (:'.$paramVal.')'
)
->setParameter($paramDecl, (int)$declId)
->setParameter($paramVal, $vals);
}
if (!$withDeleted) {
$qb->andWhere('pp.deletedAt IS NULL');
}
return (int)$qb->getQuery()->getSingleScalarResult();
}
public function searchItem($query, $isAvailable = false, $maxResult = 5, $withDeleted = false, ?int $supplierId = null)
{
$query = trim((string) $query);
$isAvailable = filter_var($isAvailable, FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE) === true;
// Minimum 2 caractères
if (mb_strlen($query) < 2) {
return [];
}
$term = mb_strtoupper($query);
$qb = $this->createQueryBuilder('p')->distinct();
$qb->select('p.id, p.name, p.reference, p.description, p.price_ht, p.buyingPriceTtc')
->innerJoin('p.produit', 'pp')
->addSelect('pp.unit AS unit')
->leftJoin('pp.tva', 'pptva')
->addSelect('pptva.id AS tva_id')
->addSelect('pptva.number AS tva')
->leftJoin('p.stocks', 's');
// 2 caractères => référence uniquement
// > 2 caractères => référence OU nom
if (mb_strlen($query) === 2) {
$qb->andWhere('UPPER(p.reference) LIKE :referenceStart')
->setParameter('referenceStart', $term . '%');
} else {
$qb->andWhere(
$qb->expr()->orX(
'UPPER(p.reference) LIKE :referenceStart',
'UPPER(p.name) LIKE :nameContains'
)
)
->setParameter('referenceStart', $term . '%')
->setParameter('nameContains', '%' . $term . '%');
}
if (!$withDeleted) {
$qb->andWhere('pp.deletedAt IS NULL');
}
if ($supplierId !== null && $supplierId > 0) {
$qb->andWhere('IDENTITY(pp.supplier) = :supplierId')
->setParameter('supplierId', $supplierId);
}
if ($isAvailable) {
$qb->andWhere('(s.qtStock - s.qtReserved) >= 1');
}
$qb->orderBy('p.createdAt', 'DESC')
->setMaxResults($maxResult);
return $qb->getQuery()->getResult();
}
public function findBestMatchForSocialOrder(string $reference, ?string $color = null, ?string $size = null): ?ProduitDeclinationValue
{
$reference = mb_strtoupper(trim($reference));
if ($reference === '') {
return null;
}
$candidates = $this->createQueryBuilder('p')
->leftJoin('p.produit', 'produit')->addSelect('produit')
->leftJoin('produit.tva', 'tva')->addSelect('tva')
->leftJoin('p.groupDeclinationValues', 'gdv')->addSelect('gdv')
->leftJoin('gdv.declination', 'decl')->addSelect('decl')
->leftJoin('gdv.value', 'val')->addSelect('val')
->andWhere('UPPER(p.reference) = :reference OR UPPER(produit.reference) = :reference')
->setParameter('reference', $reference)
->getQuery()
->getResult();
if ($candidates === []) {
$candidates = $this->createQueryBuilder('p')
->leftJoin('p.produit', 'produit')->addSelect('produit')
->leftJoin('produit.tva', 'tva')->addSelect('tva')
->leftJoin('p.groupDeclinationValues', 'gdv')->addSelect('gdv')
->leftJoin('gdv.declination', 'decl')->addSelect('decl')
->leftJoin('gdv.value', 'val')->addSelect('val')
->andWhere('UPPER(p.reference) LIKE :referenceLike OR UPPER(produit.reference) LIKE :referenceLike')
->setParameter('referenceLike', $reference . '%')
->setMaxResults(20)
->getQuery()
->getResult();
}
if ($candidates === []) {
return null;
}
$normalizedColor = $this->normalizeToken($color);
$normalizedSize = $this->normalizeToken($size);
$bestCandidate = null;
$bestScore = -1;
foreach ($candidates as $candidate) {
$score = 0;
$candidateReference = mb_strtoupper((string) $candidate->getReference());
$productReference = mb_strtoupper((string) $candidate->getProduit()?->getReference());
if ($candidateReference === $reference) {
$score += 100;
} elseif (str_starts_with($candidateReference, $reference)) {
$score += 70;
}
if ($productReference === $reference) {
$score += 80;
} elseif ($productReference !== '' && str_starts_with($productReference, $reference)) {
$score += 50;
}
$matchedColor = false;
$matchedSize = false;
foreach ($candidate->getGroupDeclinationValues() as $groupValue) {
$declinationName = $this->normalizeToken($groupValue->getDeclination()?->getName());
$valueName = $this->normalizeToken($groupValue->getValue()?->getName());
if ($normalizedColor !== '' && in_array($declinationName, ['couleur', 'color'], true) && $valueName === $normalizedColor) {
$score += 20;
$matchedColor = true;
}
if ($normalizedSize !== '' && in_array($declinationName, ['taille', 'pointure', 'size'], true) && $valueName === $normalizedSize) {
$score += 20;
$matchedSize = true;
}
}
if ($normalizedColor !== '' && !$matchedColor) {
$score -= 10;
}
if ($normalizedSize !== '' && !$matchedSize) {
$score -= 10;
}
if ($score > $bestScore) {
$bestScore = $score;
$bestCandidate = $candidate;
}
}
return $bestCandidate;
}
public function findClosestMatchesForSocialOrder(string $query, int $limit = 3): array
{
$query = trim($query);
if ($query === '') {
return [];
}
$normalizedQuery = $this->normalizeToken($query);
if ($normalizedQuery === '') {
return [];
}
$term = mb_strtoupper($query);
$candidates = $this->createQueryBuilder('p')
->leftJoin('p.produit', 'produit')->addSelect('produit')
->leftJoin('produit.tva', 'tva')->addSelect('tva')
->andWhere('UPPER(p.reference) LIKE :term OR UPPER(produit.reference) LIKE :term OR UPPER(p.name) LIKE :contains OR UPPER(produit.name) LIKE :contains')
->setParameter('term', $term . '%')
->setParameter('contains', '%' . $term . '%')
->setMaxResults(25)
->getQuery()
->getResult();
$scored = [];
foreach ($candidates as $candidate) {
if (!$candidate instanceof ProduitDeclinationValue) {
continue;
}
$reference = $this->normalizeToken($candidate->getReference());
$name = $this->normalizeToken($candidate->getName());
$productReference = $this->normalizeToken($candidate->getProduit()?->getReference());
$productName = $this->normalizeToken($candidate->getProduit()?->getName());
$score = 0;
foreach ([$reference, $name, $productReference, $productName] as $value) {
if ($value === '') {
continue;
}
if ($value === $normalizedQuery) {
$score = max($score, 1000);
continue;
}
if (str_contains($value, $normalizedQuery) || str_contains($normalizedQuery, $value)) {
$score = max($score, 850 - abs(strlen($value) - strlen($normalizedQuery)));
}
similar_text($normalizedQuery, $value, $percent);
$score = max($score, (int) round($percent * 7));
if (function_exists('levenshtein')) {
$distance = levenshtein($normalizedQuery, $value);
$score = max($score, max(0, 700 - ($distance * 70)));
}
}
if ($score <= 0) {
continue;
}
$scored[] = [
'score' => $score,
'declination' => $candidate,
];
}
usort($scored, static fn (array $a, array $b): int => $b['score'] <=> $a['score']);
$results = [];
$seen = [];
foreach ($scored as $item) {
/** @var ProduitDeclinationValue $declination */
$declination = $item['declination'];
if (isset($seen[$declination->getId()])) {
continue;
}
$seen[$declination->getId()] = true;
$results[] = $declination;
if (count($results) >= $limit) {
break;
}
}
return $results;
}
private function normalizeToken(?string $value): string
{
$value = trim((string) $value);
if ($value === '') {
return '';
}
$ascii = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
$ascii = is_string($ascii) ? $ascii : $value;
return strtolower(trim((string) preg_replace('/[^a-zA-Z0-9]+/', '', $ascii)));
}
public function searchAndCountProduitDeclinations(
int $page,
int $limit,
$reference,
$name,
$categories,
$isAvailable,
$inPromo,
array $declinationFilters = [],
$qtMin = null,
$qtMax = null,
$buyingPriceMin = null,
$buyingPriceMax = null,
$priceMin = null,
$priceMax = null,
string $sortField = 'createdAt',
string $sortType = 'DESC',
bool $withDeleted = false,
?int $warehouseId = null
): array {
/* ===============================
1) QueryBuilder de base
=============================== */
$qb = $this->createQueryBuilder('p');
$this->QueryBuilderSearch(
$qb,
$reference,
$name,
$categories,
$isAvailable,
$inPromo,
$declinationFilters,
$qtMin,
$qtMax,
$buyingPriceMin,
$buyingPriceMax,
$priceMin,
$priceMax,
$withDeleted,
$warehouseId
);
/* ===============================
2) COUNT (clone)
=============================== */
$qbCount = clone $qb;
$total = count(
$qbCount
->resetDQLPart('select')
->resetDQLPart('orderBy')
->select('p.id')
->getQuery()
->getScalarResult()
);
/* ===============================
3) RESULTS (clone)
=============================== */
$qbResult = clone $qb;
if ($sortField === 'declinationPositions') {
$qbResult
->orderBy('declinationPosition1Sort', 'ASC')
->addOrderBy('declinationPosition2Sort', 'ASC')
->addOrderBy('p.reference', 'ASC');
} else {
$qbResult->orderBy($sortField === 'qtStock' ? 'qtStockSort' : 'p.' . $sortField, $sortType);
}
$qbResult
->setFirstResult($page * $limit)
->setMaxResults($limit);
$data = $qbResult->getQuery()->getResult();
return [
'data' => $data,
'total' => $total,
];
}
public function searchProduitDeclinations(
$page,
$limit,
$reference,
$name,
$categories,
$isAvailable,
$inPromo,
array $declinationFilters = [],
$qtMin = null,
$qtMax = null,
$buyingPriceMin = null,
$buyingPriceMax = null,
$priceMin = null,
$priceMax = null,
$sortField = 'createdAt',
$sortType = 'DESC',
$withDeleted = false,
?int $warehouseId = null
) {
$qb = $this->createQueryBuilder('p');
$qb = $this->QueryBuilderSearch(
$qb,
$reference,
$name,
$categories,
$isAvailable,
$inPromo,
$declinationFilters,
$qtMin,
$qtMax,
$buyingPriceMin,
$buyingPriceMax,
$priceMin,
$priceMax,
$withDeleted,
$warehouseId
);
if ($sortField === 'declinationPositions') {
$qb->orderBy('declinationPosition1Sort', 'ASC')
->addOrderBy('declinationPosition2Sort', 'ASC')
->addOrderBy('p.reference', 'ASC');
} else {
$qb->orderBy(($sortField === 'qtStock' ? '' : 'p.') . $sortField, $sortType);
}
if ($page !== false) $qb->setMaxResults($limit)->setFirstResult($page * $limit);
return $qb->getQuery()->getResult();
}
public function countProduitDeclinations(
$reference,
$name,
$categories,
$isAvailable,
$inPromo,
array $declinationFilters = [],
$qtMin = null,
$qtMax = null,
$buyingPriceMin = null,
$buyingPriceMax = null,
$priceMin = null,
$priceMax = null,
$withDeleted = false,
?int $warehouseId = null
) {
$qb = $this->createQueryBuilder('p');
$qb->select('COUNT(p)');
$qb = $this->QueryBuilderSearch(
$qb,
$reference,
$name,
$categories,
$isAvailable,
$inPromo,
$declinationFilters,
$qtMin,
$qtMax,
$buyingPriceMin,
$buyingPriceMax,
$priceMin,
$priceMax,
$withDeleted,
$warehouseId
);
$rows = $qb->getQuery()->getScalarResult();
return \count($rows);
}
/*--Filtre liste declinaisons--*/
private function QueryBuilderSearch(
$qb,
$reference,
$name,
$categories,
$isAvailable,
$inPromo,
array $declinationFilters = [],
$qtMin = null,
$qtMax = null,
$buyingPriceMin = null,
$buyingPriceMax = null,
$priceMin = null,
$priceMax = null,
$withDeleted = false,
?int $warehouseId = null
) {
// Produit parent utilisé pour les filtres "liste des déclinaisons d'un produit"
$qb->leftJoin('p.produit', 'pp');
if ($name) {
$qb->andWhere('UPPER(p.name) LIKE :name')
->setParameter('name', '%' . strtoupper($name) . '%');
}
if ($reference) {
$qb->andWhere('(UPPER(p.reference) LIKE :reference OR UPPER(pp.reference) LIKE :reference)')
->setParameter('reference', '%' . strtoupper(trim($reference)) . '%');
}
// Filtres dynamiques declinaisons
foreach ($declinationFilters as $declinationId => $valueId) {
if (empty($valueId)) {
continue;
}
$alias = 'gdv_' . (int)$declinationId;
$qb
->innerJoin('p.groupDeclinationValues', $alias)
->andWhere($alias . '.declination = :decl_' . $declinationId)
->setParameter('decl_' . $declinationId, (int)$declinationId);
if (is_array($valueId)) {
$qb->andWhere($alias . '.value IN (:val_' . $declinationId . ')')
->setParameter('val_' . $declinationId, $valueId);
} else {
$qb->andWhere($alias . '.value = :val_' . $declinationId)
->setParameter('val_' . $declinationId, (int)$valueId);
}
}
// Stock + agrégats
if (($warehouseId ?? 0) > 0) {
$qb->leftJoin('p.stocks', 's', 'WITH', 's.warehouse = :warehouseFilter')
->setParameter('warehouseFilter', $warehouseId);
} else {
$qb->leftJoin('p.stocks', 's');
}
$qb
->addSelect('COALESCE(SUM(s.qtStock),0) AS HIDDEN qtStockSort')
->addSelect('COALESCE(SUM(s.qtReserved),0) AS HIDDEN qtReservedSort')
->addSelect('COALESCE(SUM(s.qtStock - s.qtReserved),0) AS HIDDEN qtAvailableSort')
->addSelect("(SELECT MIN(sortValue1.name)
FROM App\Entity\GroupDeclinationValue sortGroup1
JOIN sortGroup1.declination sortDeclination1
JOIN sortGroup1.value sortValue1
WHERE sortGroup1.produitDeclination = p
AND sortDeclination1.position = 1) AS HIDDEN declinationPosition1Sort")
->addSelect("(SELECT MIN(sortValue2.name)
FROM App\Entity\GroupDeclinationValue sortGroup2
JOIN sortGroup2.declination sortDeclination2
JOIN sortGroup2.value sortValue2
WHERE sortGroup2.produitDeclination = p
AND sortDeclination2.position = 2) AS HIDDEN declinationPosition2Sort")
->groupBy('p.id');
// Stock
/*
$qb->leftJoin('p.stocks', 's')
->addSelect('COALESCE(SUM(s.qtStock),0) AS HIDDEN qtStockSort')
->groupBy('p.id');
*/
if ($isAvailable !== "") {
if ($isAvailable) {
$qb->andWhere('(s.qtStock - s.qtReserved) >= 1');
} else {
$qb->andWhere('(s.qtStock - s.qtReserved) <= 0');
}
}
// Categories (produit parent)
if (!empty($categories)) {
$qb->leftJoin('pp.categories', 'ppc');
if (is_array($categories)) {
$qb->andWhere('ppc.id IN (:categories)')
->setParameter('categories', $categories);
} else {
$qb->andWhere('ppc.id = :categories')
->setParameter('categories', (int)$categories);
}
}
// Promo (produit parent)
if ($inPromo !== "") {
if ($inPromo == '1') {
$qb->join('pp.promotion', 'promo')
->andWhere(':now >= promo.startAt')
->andWhere(':now <= promo.endAt')
->setParameter('now', new \DateTime('now'));
} elseif ($inPromo == '0') {
$qb->andWhere('pp.promotion IS NULL');
}
}
// Prix (TTC)
if ($buyingPriceMin) {
$qb->andWhere('p.buyingPriceTtc >= :buyingPriceMin')
->setParameter('buyingPriceMin', $buyingPriceMin);
}
if ($buyingPriceMax) {
$qb->andWhere('p.buyingPriceTtc <= :buyingPriceMax')
->setParameter('buyingPriceMax', $buyingPriceMax);
}
if ($priceMin) {
$qb->andWhere('pp.price_ttc >= :priceMin')
->setParameter('priceMin', $priceMin);
}
if ($priceMax) {
$qb->andWhere('pp.price_ttc <= :priceMax')
->setParameter('priceMax', $priceMax);
}
// Quantité (disponible)
if ($qtMin !== null && $qtMin !== '') {
$qb->having('COALESCE(SUM(s.qtStock - s.qtReserved), 0) >= :qtMin')
->setParameter('qtMin', (int) $qtMin);
}
if ($qtMax !== null && $qtMax !== '') {
$qb->andHaving('COALESCE(SUM(s.qtStock - s.qtReserved), 0) <= :qtMax')
->setParameter('qtMax', (int) $qtMax);
}
// Quantite
//if ($qtMin) $qb->andWhere('s.qtStock >= :qtMin')->setParameter('qtMin', $qtMin);
//if ($qtMax) $qb->andWhere('s.qtStock <= :qtMax')->setParameter('qtMax', $qtMax);
// Suppression
$qb->andWhere('pp.deletedAt IS ' . ($withDeleted ? 'NOT' : '') . ' NULL');
return $qb;
}
public function findDeclinationValueWithDeclination($idDeclination, $idProduit, ?int $supplierId = null) {
$qb = $this->createQueryBuilder('p');
$qb->leftJoin('p.produit', 'pp')->andWhere('pp.deletedAt IS NULL')
->andWhere('pp.id = :produit')->setParameter('produit', $idProduit);
if ($supplierId !== null && $supplierId > 0) {
$qb->andWhere('IDENTITY(pp.supplier) = :supplierId')
->setParameter('supplierId', $supplierId);
}
$qb->leftJoin('p.groupDeclinationValues', 'pgc')
->leftJoin('pgc.declination', 'pgcd')
->leftJoin('pgc.value', 'pgcv')
->andWhere('pgcv.id = :idDeclination')->setParameter('idDeclination', $idDeclination );
$qb->orderBy('p.reference', 'ASC');
return $qb->getQuery()->getResult();
}
public function qtyAvailable($prdDec)
{
$qb = $this->createQueryBuilder('p');
$qb->leftJoin('p.stocks','s')
->Select('(s.qtStock - s.qtReserved) as qtAvailable')
->where('p.id = :id')
->setParameter('id', $prdDec->getId());
}
public function qtyReserved($prdDec)
{
$qb = $this->createQueryBuilder('p');
$qb->leftJoin('p.stocks','s')
->Select('s.qtReserved as qtReserved')
->where('p.id = :id')
->setParameter('id', $prdDec->getId());
}
//fonction et requête pour déclinaion 1 (Exemple : Couleur)
public function getStatsByDeclinationPosition($idProduit, ?string $dateBefore, ?string $dateAfter, ?int $resellerUserId = null): array
{
$conn = $this->getEntityManager()->getConnection();
$sql = "
SELECT
dcl.name AS declinaison_label,
v.name AS valeur,
(
SELECT f.image_name
FROM produit_declination_value_file pdf
JOIN file f ON f.id = pdf.file_id
WHERE pdf.produit_declination_value_id = pdv.id
LIMIT 1
) AS decli_image,
SUM(ddp.quantity) AS qtTotal,
SUM(CASE WHEN d.status NOT IN ('annule', 'retourne', 'retour-en-cours') THEN ddp.quantity ELSE 0 END) AS qtVendu,
SUM(CASE WHEN d.status = 'annule' THEN ddp.quantity ELSE 0 END) AS qtAnnulee,
SUM(CASE WHEN d.status IN ('retourne', 'retour-en-cours') THEN ddp.quantity ELSE 0 END) AS qtRetour,
SUM(ddp.total_amount_ttc) AS montantTotal
FROM document_declination_produit ddp
JOIN produit_declination_value pdv ON ddp.produit_declination_value_id = pdv.id
JOIN group_declination_value gdv ON gdv.produit_declination_id = pdv.id
JOIN value_declination v ON v.id = gdv.value_id
JOIN declination dcl ON dcl.id = v.declination_id AND dcl.position = 1
JOIN document d ON d.id = ddp.document_id
WHERE pdv.produit_id = :idProduit
AND d.type = 'commande'
AND d.category = 'client'
";
$params = ['idProduit' => $idProduit];
if ($resellerUserId) {
$sql .= " AND d.reseller_user_id = :resellerUserId";
$params['resellerUserId'] = $resellerUserId;
}
if ($dateBefore && $dateAfter) {
$sql .= " AND d.created_at BETWEEN :dateBefore AND :dateAfter";
$params['dateBefore'] = $dateBefore;
$params['dateAfter'] = $dateAfter;
} elseif ($dateBefore) {
$sql .= " AND d.created_at >= :dateBefore";
$params['dateBefore'] = $dateBefore;
} elseif ($dateAfter) {
$sql .= " AND d.created_at <= :dateAfter";
$params['dateAfter'] = $dateAfter;
}
$sql .= "
GROUP BY dcl.name, v.name
ORDER BY qtTotal DESC
";
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery($params);
return $result->fetchAllAssociative();
}
//fonction et requête pour toutes les déclinaisons
public function getAllDeclinationsStats($idProduit, ?string $dateBefore, ?string $dateAfter, ?int $resellerUserId = null): array
{
$conn = $this->getEntityManager()->getConnection();
$sql = "
SELECT
dcl1.name AS declinaison1_label,
v1.name AS declinaison1_valeur,
pdv.id AS decli_id,
-- Déclinaison 2 (ex : Taille)
dcl2.name AS declinaison2_label,
v2.name AS declinaison2_valeur,
-- Totaux
SUM(ddp.quantity) AS qtTotal,
SUM(
CASE
WHEN d.status NOT IN ('annule', 'retourne', 'retour-en-cours')
THEN ddp.quantity
ELSE 0
END
) AS qtVendu,
SUM(
CASE
WHEN d.status = 'annule'
THEN ddp.quantity
ELSE 0
END
) AS qtAnnulee,
SUM(
CASE
WHEN d.status IN ('retourne', 'retour-en-cours')
THEN ddp.quantity
ELSE 0
END
) AS qtRetour,
SUM(ddp.total_amount_ttc) AS montantTotal
FROM document_declination_produit ddp
JOIN produit_declination_value pdv
ON ddp.produit_declination_value_id = pdv.id
-- Déclinaison 1 (ex : Couleur)
JOIN group_declination_value gdv1
ON gdv1.produit_declination_id = pdv.id
JOIN value_declination v1
ON v1.id = gdv1.value_id
JOIN declination dcl1
ON dcl1.id = v1.declination_id
AND dcl1.position = 1
-- Déclinaison 2 (ex : Taille)
JOIN group_declination_value gdv2
ON gdv2.produit_declination_id = pdv.id
JOIN value_declination v2
ON v2.id = gdv2.value_id
JOIN declination dcl2
ON dcl2.id = v2.declination_id
AND dcl2.position = 2
JOIN document d
ON d.id = ddp.document_id
WHERE pdv.produit_id = :idProduit
AND d.type = 'commande'
AND d.category = 'client'
";
$params = ['idProduit' => $idProduit];
/* Gestion dynamique des dates */
if ($resellerUserId) {
$sql .= " AND d.reseller_user_id = :resellerUserId";
$params['resellerUserId'] = $resellerUserId;
}
if ($dateBefore !== null && $dateAfter !== null) {
$sql .= " AND d.created_at BETWEEN :dateBefore AND :dateAfter";
$params['dateBefore'] = $dateBefore;
$params['dateAfter'] = $dateAfter;
} elseif ($dateBefore !== null) {
$sql .= " AND d.created_at >= :dateBefore";
$params['dateBefore'] = $dateBefore;
} elseif ($dateAfter !== null) {
$sql .= " AND d.created_at <= :dateAfter";
$params['dateAfter'] = $dateAfter;
}
$sql .= "
GROUP BY dcl1.name, v1.name, v2.name, pdv.id
ORDER BY v1.name ASC, v2.name ASC
";
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery($params);
return $result->fetchAllAssociative();
}
public function countByValue(ValueDeclination $value): int
{
return $this->createQueryBuilder('gdv')
->select('COUNT(gdv.id)')
->where('gdv.value = :val')
->setParameter('val', $value)
->getQuery()
->getSingleScalarResult();
}
}

src/Repository/ProduitDeclinationValueRepository.php line 369