Symfony Profiler

<?php
namespace App\Repository\GestionComerciale;
use App\Entity\GestionComerciale\Acompte;
use App\Entity\GestionComerciale\Commande;
use App\Entity\GestionComerciale\ModeReglement;
use App\Entity\GestionComerciale\NumerotationDocument;
use DateInterval;
use Datetime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* AcompteRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class AcompteRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Acompte::class);
}
public function getTotalAcompte(Commande $commande)
{
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->where('p.commande = :commande AND p.typeReglement = :typeReglement AND p.dateSuppression IS NULL')
->setParameters(['commande' => $commande, 'typeReglement' => 1]);
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function getTotalReglementCommande(Commande $commande)
{
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->join('p.commande', 'c')
->join('c.statutCommande', 'sc')
->where('p.parent = :commande AND p.typeReglement = :typeReglement AND sc.ordre != 0')
->setParameters(['commande' => $commande, 'typeReglement' => 2]);
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function getTotalReglement(Commande $commande)
{
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->where('p.commande = :commande AND p.typeReglement = :typeReglement')
->setParameters(['commande' => $commande, 'typeReglement' => 2]);
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function getTotalReglementParTraite($moisRaz = '')
{
if ($moisRaz != "") {
//Y-m-d H:i:s
$date = new Datetime();
if ($moisRaz == '99') {
$date_mois = new Datetime($date->format('Y').'-'.$date->format('m').'-01');
} else {
$date_mois = new Datetime($date->format('Y').'-'.$moisRaz.'-01');
}
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->join('p.modeReglement', 'mr')
->where('p.dateSuppression IS NULL AND p.date >= :date AND p.modeReglement = 6')
->setParameters(['date' => $date_mois->format("Y-m-d H:i:s")]);
} else {
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->where('p.dateSuppression IS NULL AND p.modeReglement = 6');
}
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function getTotalReglementParModeReglement($moisRaz = '', $modeReglement)
{
$where = '';
$parameters = [];
$repo_mode_reglement = $this->getEntityManager()->getRepository(ModeReglement::class);
//Verifier si il existe un masque de numérotation pour ce mode de reglement
$repo_numerotation = $this->getEntityManager()->getRepository(NumerotationDocument::class);
$numerotation = $repo_numerotation->findOneBy(['modeReglement' => $modeReglement]);
if (is_object($numerotation)) {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'mr.id = :modeReglement';
$parameters['modeReglement'] = $modeReglement;
} else {
//Trouver les modes de réglement sans masque
$modesReglementSsMasque = [];
$modesReglement = $repo_mode_reglement->findAll();
foreach ($modesReglement as $modeReglement) {
$num = $repo_numerotation->findOneBy(['modeReglement' => $modeReglement]);
if ( ! is_object($num)) {
$modesReglementSsMasque[] = $modeReglement->getId();
}
}
if ($where != '') {
$where .= ' AND ';
}
$where .= 'mr.id IN (:modeReglement)';
$parameters['modeReglement'] = $modesReglementSsMasque;
}
if ($moisRaz != "") {
if ($where != '') {
$where .= ' AND ';
}
$where .= 'p.date >= :date';
$parameters['date'] = $date_mois->format("Y-m-d H:i:s");
//Y-m-d H:i:s
$date = new Datetime();
if ($moisRaz == '99') {
$date_mois = new Datetime($date->format('Y').'-'.$date->format('m').'-01');
} else {
$date_mois = new Datetime($date->format('Y').'-'.$moisRaz.'-01');
}
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->join('p.modeReglement', 'mr')
->where($where)
->setParameters($parameters);
} else {
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->join('p.modeReglement', 'mr')
->where($where)
->setParameters($parameters);
}
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function nbNewSurXjours($nbJours)
{
$date = new Datetime();
$date->sub(new DateInterval('P'.$nbJours.'D'));
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->where('p.dateSuppression IS NULL AND p.date >= :date')
->setParameters(['date' => $date->format("Y-m-d H:i:s")]);
$res = $query->getQuery()->getOneOrNullResult();
if ($res !== null) {
return $res['total'];
}
return $res;
}
public function getMontantRembousement($client)
{
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND p.commande IS NULL AND p.remboursement IS NULL AND (p.montant > :montant) AND (p.estParent = 0 or p.estParent is NULL) AND (p.horsErp = 0 OR p.horsErp IS NULL) AND (p.mouvementEquilibrage = 0 OR p.mouvementEquilibrage IS NULL)'
)
->setParameters(['client' => $client, 'montant' => '0']);
$res = $query->getQuery()->getOneOrNullResult();
$retour = 0;
if ($res["total"] > 0) {
$retour = '-'.$res["total"];
}
return $retour;
}
public function getMontantRembousement2($client)
{
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND p.remboursement IS NULL AND (p.montant > :montant) AND (p.estParent = 0 or p.estParent is NULL) AND (p.horsErp = 0 OR p.horsErp IS NULL) AND (p.mouvementEquilibrage = 0 OR p.mouvementEquilibrage IS NULL)'
)
->setParameters(['client' => $client, 'montant' => '0']);
$res = $query->getQuery()->getOneOrNullResult();
$retour = 0;
if ($res["total"] > 0) {
$retour = '-'.$res["total"];
}
return $retour;
}
public function getMontantRembourser($client)
{
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND p.commande IS NOT NULL AND p.remboursement IS NULL AND (p.montant < :montant) AND (p.estParent = 0 or p.estParent is NULL) AND (p.horsErp = 0 OR p.horsErp IS NULL) AND (p.mouvementEquilibrage = 0 OR p.mouvementEquilibrage IS NULL)'
)
->setParameters(['client' => $client, 'montant' => '0']);
$res = $query->getQuery()->getOneOrNullResult();
$retour = 0;
/*
if($res["total"] < 0) {
$retour = '-'.$res["total"];
}
*
*/
if ($res !== null) {
$retour = $res["total"];
}
return $retour;
}
public function getMontantAccompte($client)
{
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND p.commande IS NOT NULL AND p.remboursement IS NULL and (p.estParent = 0 or p.estParent is NULL) AND (p.horsErp = 0 OR p.horsErp IS NULL) AND (p.mouvementEquilibrage = 0 OR p.mouvementEquilibrage IS NULL)'
)
->setParameters(['client' => $client]);
$res = $query->getQuery()->getOneOrNullResult();
$retour = 0;
/*
if($res["total"] < 0) {
$retour = '-'.$res["total"];
}
*
*/
if ($res !== null) {
$retour = $res["total"];
}
return $retour;
}
public function getMontantaPayerPourClient($client)
{
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND p.commande IS NULL AND p.remboursement IS NULL AND (p.montant < :montant) AND (p.estParent = 0 or p.estParent is NULL) AND (p.horsErp = 0 OR p.horsErp IS NULL) AND (p.mouvementEquilibrage = 0 OR p.mouvementEquilibrage IS NULL)'
)
->setParameters(['client' => $client, 'montant' => '0']);
$retour = 0;
$res = $query->getQuery()->getOneOrNullResult();
if ($res !== null) {
$retour = "".$res["total"];
}
return $retour;
}
public function getMontantEquilibrage($client)
{
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where('p.dateSuppression IS NULL AND p.client = :client AND p.commande IS NULL AND p.mouvementEquilibrage = 1 ')
->setParameters(['client' => $client]);
$res = $query->getQuery()->getOneOrNullResult();
$retour = 0;
if ($res !== null) {
$retour = $res["total"];
}
return $retour;
}
public function getMontantHorsErp($client)
{
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where('p.dateSuppression IS NULL AND p.client = :client AND p.commande IS NULL AND p.horsErp = 1 ')
->setParameters(['client' => $client]);
$res = $query->getQuery()->getOneOrNullResult();
$retour = 0;
if ($res !== null) {
$retour = $res["total"];
}
return $retour;
}
public function proposerRemboursement($client)
{
return true;
}
public function getTotauxReglements($facture)
{
$client = $facture->getClient();
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where('p.dateSuppression IS NULL AND (p.commande IN (:commande) OR (p.client = :client AND p.commande is null AND p.remboursement is null AND p.estParent is null))')
->setParameters(['commande' => [$facture->getId(), $facture->getCommande()->getId()], 'client' => $client->getId()]);
$res = $query->getQuery()->getOneOrNullResult();
$retour = 0;
if ($res !== null) {
$retour = $res["total"];
}
return $retour;
}
public function getAcompteDisponible($client)
{
$query = $this->createQueryBuilder('p')
//->select("SUM(p.montant) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND p.commande IS NULL AND p.remboursement IS NULL AND p.montant > :montant AND (p.estParent = 0 or p.estParent is NULL) AND (p.mouvementEquilibrage = 0 OR p.mouvementEquilibrage IS NULL) AND (p.horsErp = 0 OR p.horsErp IS NULL)'
)
->setParameters(['client' => $client, 'montant' => '0'])
->orderBy('p.date', 'ASC');
$res = $query->getQuery()->getResult();
return $res;
}
public function getTotalAcomptesEtReglements($commande)
{
$query = $this->createQueryBuilder('p')
->select("SUM(p.montant) as total")
->where('p.dateSuppression IS NULL and (p.commande = :commande or p.parent= :commande)')
->setParameters(['commande' => $commande]);
$res = $query->getQuery()->getOneOrNullResult();
return $res["total"];
}
public function getAcomptesNonUtilisésTotalement($client)
{
$query = $this->createQueryBuilder('p')
//->select("SUM(p.montant) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND p.commande IS NULL AND p.remboursement IS NULL AND p.montant > :montant AND (p.estParent = 0 or p.estParent is NULL) AND (p.horsErp = 0 OR p.horsErp IS NULL) '
.'AND p.lettrage IS NULL '
.'AND (rp.id IS NULL or rp.lettrage IS NULL) AND (ap.id IS NULL or ap.lettrage IS NULL)'
)//AND (p.mouvementEquilibrage = 0 OR p.mouvementEquilibrage IS NULL) AND (p.horsErp = 0 OR p.horsErp IS NULL)
->leftJoin('p.reglementParent', 'rp')
->leftJoin('p.acompteParent', 'ap')
->setParameters(['client' => $client, 'montant' => '0'])
->orderBy('p.date', 'ASC');
$res = $query->getQuery()->getResult();
return $res;
}
public function getTotalAcomptesEtReglementsClient($client)
{
$query = $this->createQueryBuilder('p')
->select("SUM(COALESCE(p.montant, 0)) + SUM(COALESCE(p.operationDiverse, 0)) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND (p.acompteParent = 0 or p.acompteParent is NULL) AND (p.reglementParent = 0 or p.reglementParent is NULL)'
)
->setParameters(['client' => $client]);
$res = $query->getQuery()->getSingleScalarResult();
return $res;
}
public function getAcomptesEtReglementsClientDebug($client)
{
$query = $this->createQueryBuilder('p')
->select("p.montant")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND (p.acompteParent = 0 or p.acompteParent is NULL) AND (p.reglementParent = 0 or p.reglementParent is NULL)'
)
->setParameters(['client' => $client]);
$res = $query->getQuery()->getResult();
return $res;
}
public function getNbAcomptesEtReglementsClient($client)
{
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->where(
'p.dateSuppression IS NULL AND p.client = :client AND (p.acompteParent = 0 or p.acompteParent is NULL) AND (p.reglementParent = 0 or p.reglementParent is NULL)'
)
->setParameters(['client' => $client]);
$res = $query->getQuery()->getSingleScalarResult();
return $res;
}
public function getAcomptesEtReglementsClient($client, $start = 1, $length = 25, $sortColumn = 'dateReglement', $sortDir = 'DESC', $requete = '')
{
$limit = $start + $length;
$where = 'p.dateSuppression IS NULL AND p.client = :client AND (p.acompteParent = 0 or p.acompteParent is NULL) AND (p.reglementParent = 0 or p.reglementParent is NULL)';
if ($requete != '') {
$where .= $requete;
}
$query = $this->createQueryBuilder('p')
//->select("SUM(p.montant) as total")
->where($where)
->setParameters(['client' => $client])
//->orderBy(array('p.'.$sortColumn => $sortDir))
->add('orderBy', 'p.'.$sortColumn.' '.$sortDir)
->setMaxResults($limit);
return $query;
}
public function getReglementsBordereau($donnees = [], $output = '')
{
$where = 'p.dateSuppression IS NULL AND (p.acompteParent = 0 or p.acompteParent is NULL) AND (p.reglementParent = 0 or p.reglementParent is NULL)';
$where .= ' AND p.bordereau IS NULL';
$where .= " AND p.dateReglement > '2021-07-01 00:00:00'";
$parameters = [];
if ( ! empty($donnees['date'])) {
/*
$where .= ' AND p.date >= :dateDebut';
$parameters['dateDebut'] = $donnees['date']->format('Y-m-d 00:00:00');
*/
$where .= ' AND p.date <= :dateFin';
$parameters['dateFin'] = $donnees['date']->format('Y-m-d 23:59:59');
}
if ( ! empty($donnees['modeReglement'])) {
$where .= ' AND p.modeReglement = :modeReglement';
$parameters['modeReglement'] = $donnees['modeReglement'];
}
$query = $this->createQueryBuilder('p')
->where($where)
->setParameters($parameters);
if (is_object($output)) {
$output->writeln($query->getQuery()->getSql());
foreach ($donnees as $key => $donnee) {
$output->writeln($key);
}
}
return $query->getQuery()->getResult();
}
}

src/Repository/GestionComerciale/AcompteRepository.php line 98