<?php

namespace App\Repository;

use App\Entity\Client;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\OptimisticLockException;
use Doctrine\ORM\ORMException;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\Persistence\ManagerRegistry;
use PDO;

/**
 * @method Client|null find($id, $lockMode = null, $lockVersion = null)
 * @method Client|null findOneBy(array $criteria, array $orderBy = null)
 * @method Client[]    findAll()
 * @method Client[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class ClientRepository extends ServiceEntityRepository
{
    private $em;
    private $selectClient = "SELECT 
    COALESCE(e.idclient_souscripteur, c.id) as id,
    c.numero_client AS c_numero_client,
    esite.nom AS esite_nom,
    string_agg(DISTINCT csss.nom::text, ','::text ORDER BY (csss.nom::text)) AS csss_nom,
    c.nom AS c_nom,
    c.prenom AS c_prenom,
    ec.libelle_court AS ec_libelle_court,
    tc.libelle AS tc_libelle,
    c.nom_entreprise AS c_nom_entreprise,
    cp.libelle AS cp_libelle,
    cs.nom AS cs_nom,
    string_agg(DISTINCT tel.num_tel::text, ' / '::text ORDER BY (tel.num_tel::text)) AS tel_num_tel,
    string_agg(DISTINCT mail.email::text, ','::text ORDER BY (mail.email::text)) AS mail_email,
    eat.libelle AS eat_libelle,
    CONCAT(ea.adresse, ' ', ea.adresse_complement) AS ea_adresse,
    string_agg(DISTINCT CASE  WHEN tca.libelle IS NOT NULL AND eac.valeur IS NOT NULL THEN CONCAT(tca.libelle::text, ' ', eac.valeur)
        ELSE NULL
        END,
        ', '::text)  AS eac_complement,
    ea.code_postal AS ea_code_postal,
    ea.ville AS ea_ville,
    e.num_serie_principal AS e_num_serie_principal,
    e.id AS e_id,
    et.type AS et_type,
    ef.libelle AS ef_libelle,
    ctct.libelle_court AS ctct_libelle_court,
    efc.libelle AS efc_libelle,
    ct.groupe AS ct_groupe,
    ct.id AS ct_id,
    string_agg(DISTINCT eoo.libelle_court::text, ','::text ORDER BY (eoo.libelle_court::text)) AS eoo_libelle_court,
    ct.date_debut_contrat::date AS ct_date_debut_contrat,
    ct.date_fin_contrat::date AS ct_date_fin_contrat,
    e.date_fin_garantie AS e_date_fin_garantie,
    e.date_premier_allumage::date AS e_date_premier_allumage,
    e.date_derniere_v1::date as e_date_derniere_v1,
    el.libelle AS el_libelle,
    r.libelle AS r_libelle,
    es.nom AS es_nom,
    ua.nom AS ua_nom,
    e.chantier AS e_chantier,
        CASE
            WHEN e.salarie_frisquet = true THEN 'oui'::text
            ELSE 'non'::text
        END AS e_salarie_frisquet,
    cpo.date AS cpo_date,
    e.date_eligible_privilege as e_date_eligible_privilege,
        CASE
            WHEN e.actif = true THEN 'non'::text
            ELSE 'oui'::text
        END AS e_actif,
    e.numero_client_oracle AS e_numero_client_oracle
   FROM sav.client c
     JOIN sav.client_site ccs ON c.id = ccs.idclient
     JOIN sav.site csss ON ccs.idsite = csss.id
     LEFT JOIN sav.equipement e ON (c.id = e.idclient_souscripteur OR c.id = e.idclient_utilisateur)
     LEFT JOIN sav.site esite ON e.idsite = esite.id
     LEFT JOIN sav.equipement_famille ef ON e.idequipement_famille = ef.id
     LEFT JOIN sav.client cs ON e.idclient_souscripteur = cs.id
     LEFT JOIN sav.equipement_famille_contrat efc ON e.idequipement_famille_contrat = efc.id
     LEFT JOIN sav.adresse ea ON e.idadresse = ea.id
     LEFT JOIN sav.adresse_complement eac ON ea.id = eac.idadresse
     LEFT JOIN sav.type_complement_adresse tca ON eac.idtype_complement_adresse = tca.id
     LEFT JOIN sav.type_logement eat ON ea.idtype_logement = eat.id
     LEFT JOIN sav.equipement_option eo ON e.id = eo.idequipement
     LEFT JOIN sav.equipement_local el ON e.idequipement_local = el.id
     LEFT JOIN sav.raccordement r ON e.idraccordement = r.id
     LEFT JOIN sav.secteur es ON e.idsecteur = es.id
     LEFT JOIN sav.agent_secteur esas ON es.id = esas.idsecteur_version and esas.rang_tri = 1
     LEFT JOIN sav.utilisateur ua ON esas.idutilisateur_agent = ua.id
     LEFT JOIN sav.option eoo ON eo.idoption = eoo.id
     LEFT JOIN sav.equipement_type_puissance etp ON e.idequipement_type_puissance = etp.id
     LEFT JOIN sav.equipement_type et ON etp.idequipement_type = et.id
     LEFT JOIN sav.etat_civil ec ON c.idetat_civil = ec.id
     LEFT JOIN sav.type_client tc ON c.idtype_client = tc.id
     LEFT JOIN sav.categorie_pro cp ON c.idcategorie_pro = cp.id
     LEFT JOIN sav.client_tel tel ON c.id = tel.idclient
     LEFT JOIN sav.client_email mail ON c.id = mail.idclient
     LEFT JOIN sav.contrat ct ON c.id = ct.idclient  and ct.idequipement = e.id AND ct.dernier_contrat = true [replace_contrat]
     LEFT JOIN sav.porte_ouverte cpo ON c.id = cpo.idclient AND cpo.derniere_po = true
     LEFT JOIN sav.contrat_type ctct ON ct.idcontrat_type = ctct.id
  ";
    private $groupByClient = "
    GROUP BY c.id, c.numero_client, esite.nom, ec.libelle_court, tc.libelle, cp.libelle, cs.nom, eat.libelle, ea.adresse,ea.adresse_complement, ea.code_postal, ea.ville, e.num_serie_principal, e.id, et.type, ef.libelle, ctct.libelle_court, efc.libelle, ct.groupe, ct.id, ct.date_debut_contrat, ct.date_fin_contrat, el.libelle, r.libelle, cpo.date, es.nom, ua.nom
    ";

    private $orderByClient = "
    ORDER BY c.id
    ";

    private $selectCountClient = "
    SELECT count(*) FROM
  (
  SELECT Distinct
       c.id,
    e.id    FROM sav.client c
    JOIN sav.client_site ccs ON c.id = ccs.idclient
     JOIN sav.site csss ON ccs.idsite = csss.id
     LEFT JOIN sav.equipement e ON (c.id = e.idclient_souscripteur OR c.id = e.idclient_utilisateur) 
     LEFT JOIN sav.site esite ON e.idsite = esite.id
    [replace_left_join]
    where true

  ";
    private $selectCountNumSerie = "
    SELECT count(*) FROM
  (
  SELECT Distinct
       e.num_serie_principal    FROM sav.client c
    JOIN sav.client_site ccs ON c.id = ccs.idclient
     JOIN sav.site csss ON ccs.idsite = csss.id
     LEFT JOIN sav.equipement e ON (c.id = e.idclient_souscripteur OR c.id = e.idclient_utilisateur) 
     LEFT JOIN sav.site esite ON e.idsite = esite.id
    [replace_left_join]
    where true

  ";
    private $selectCountClientLight = "
    SELECT count(*) FROM
  (
  SELECT Distinct
       c.id,
    e.id    FROM sav.client c
     LEFT JOIN sav.equipement e ON c.id = e.idclient_souscripteur AND e.actif IS TRUE
  ";
    private $selectCountEnd = ") t;";

    private $tabSearchLeft =
    [
        "cs.nom" => "LEFT JOIN sav.client cs ON e.idclient_souscripteur = cs.id",
        "ef.libelle" => "LEFT JOIN sav.equipement_famille ef ON e.idequipement_famille = ef.id",
        "ea.code_postal" => "LEFT JOIN sav.adresse ea ON e.idadresse = ea.id",
        "ea.ville" => "LEFT JOIN sav.adresse ea ON e.idadresse = ea.id",
        "ea.adresse" => "LEFT JOIN sav.adresse ea ON e.idadresse = ea.id",
        "efc.libelle" => "LEFT JOIN sav.equipement_famille_contrat efc ON e.idequipement_famille_contrat = efc.id",
        "et.type" => ["LEFT JOIN sav.equipement_type_puissance etp ON e.idequipement_type_puissance = etp.id","LEFT JOIN sav.equipement_type et ON etp.idequipement_type = et.id"],
        "eoo.libelle_court" => [" LEFT JOIN sav.equipement_option eo ON e.id = eo.idequipement","LEFT JOIN sav.option eoo ON eo.idoption = eoo.id"],
        "el.libelle" =>  "LEFT JOIN sav.equipement_local el ON e.idequipement_local = el.id",
        "r.libelle" =>  "LEFT JOIN sav.raccordement r ON e.idraccordement = r.id",
        "es.nom"   => "LEFT JOIN sav.secteur es ON e.idsecteur = es.id",
        "ua.nom" => ["LEFT JOIN sav.secteur es ON e.idsecteur = es.id", "LEFT JOIN sav.agent_secteur esas ON es.id = esas.idsecteur_version and esas.rang_tri = 1","LEFT JOIN sav.utilisateur ua ON esas.idutilisateur_agent = ua.id"],
        "ec.libelle_court" => "LEFT JOIN sav.etat_civil ec ON c.idetat_civil = ec.id",
        "eat.libelle" =>  ["LEFT JOIN sav.adresse ea ON e.idadresse = ea.id","LEFT JOIN sav.type_logement eat ON ea.idtype_logement = eat.id"],
        "tc.libelle" => "LEFT JOIN sav.type_client tc ON c.idtype_client = tc.id",
        "cp.libelle" => "LEFT JOIN sav.categorie_pro cp ON c.idcategorie_pro = cp.id",
        "tel.num_tel" => "LEFT JOIN sav.client_tel tel ON c.id = tel.idclient",
        "mail.email" =>  "LEFT JOIN sav.client_email mail ON c.id = mail.idclient",
        "ctct.libelle_court" =>  ["LEFT JOIN sav.contrat ct ON c.id = ct.idclient  and ct.idequipement = e.id  AND ct.dernier_contrat = true","LEFT JOIN sav.contrat_type ctct ON ct.idcontrat_type = ctct.id"],
        "ct.groupe" => 	"LEFT JOIN sav.contrat ct ON c.id = ct.idclient  and ct.idequipement = e.id  AND ct.dernier_contrat = true",
        "ct.date_debut_contrat" => "LEFT JOIN sav.contrat ct ON c.id = ct.idclient  and ct.idequipement = e.id  AND ct.dernier_contrat = true [replace_contrat]",
        "ct.date_fin_contrat"		=> "LEFT JOIN sav.contrat ct ON c.id = ct.idclient  and ct.idequipement = e.id  AND ct.dernier_contrat = true [replace_contrat]",
        "cpo.date" => "LEFT JOIN sav.porte_ouverte cpo ON c.id = cpo.idclient AND cpo.derniere_po = true"
    ];

    public function __construct(ManagerRegistry $registry, EntityManagerInterface $em)
    {
        parent::__construct($registry, Client::class);
        $this->em = $em;
    }

    /**
     * @throws ORMException
     * @throws OptimisticLockException
     */
    public function add(Client $entity, bool $flush = true): void
    {
        $this->_em->persist($entity);
        if ($flush) {
            $this->_em->flush();
        }
    }

    /**
     * @throws ORMException
     * @throws OptimisticLockException
     */
    public function remove(Client $entity, bool $flush = true): void
    {
        $this->_em->remove($entity);
        if ($flush) {
            $this->_em->flush();
        }
    }

    public function getClientView($skip = null, $take = null, $filters = null, $sort = null,$sites = null) {
        $conn = $this->em
            ->getConnection();

        $sql_contrat = '';
        $sql = $this->selectClient;

        $sql.= " WHERE c.id > $skip";
//        $sql.= " AND e.actif IS TRUE ";
//        $sql.= " AND esas.rang_tri = 1 ";
        if ($filters['AND']) {
            foreach ($filters['AND'] as $key => $filter) {
               // $qwhere = ($key == 0 && !$whereOk) ? 'WHERE ' : 'AND ';
                if (stripos($filter['filtersPlain'],'ct.date_debut_contrat') !== false || stripos($filter['filtersPlain'],'ct.date_fin_contrat') !== false) {
                    $sql = str_replace("LEFT JOIN sav.contrat", "INNER JOIN sav.contrat", $sql);
                    $sql_contrat .= ' AND '.$filter['filtersPlain'];
                } else {
                    //Ajout conditions pour concat adresse
                    $sql .= " AND (" . (str_replace('ea.adresse', "concat(ea.adresse, ' ', ea.adresse_complement)", $filter['filtersPlain'])) . ')';
                }


            }
            //si on a pas un filtre sur actif on met actif true par defaut pour l'équipement
            if (stripos($sql, 'e.actif = false') === false) {
                $sql.= ' AND e.actif = true';
            }
        }
        $sql.= $this->groupByClient;
        $sql = str_replace('[replace_contrat]', $sql_contrat, $sql );
       /* if ($sites) {
            $qwhere = (!$filters['AND']) ? 'WHERE ' : 'AND ';
            $sql .= " $qwhere  csss_nom in ($sites)";
        }*/


        if ($sort) {
            $pos = stripos($sort[0]['selector'], '_');
            $sortBy = substr_replace($sort[0]['selector'],'.',$pos,1);
            $order = ($sort[0]['desc']) ? " DESC" : " ASC";

            $sql .= " ORDER BY $sortBy $order";
        } else {
           // if ($filters['AND']) {
            //    $sql .= ' ORDER BY c.nom, c.nom_entreprise';
            //} else
                $sql .= $this->orderByClient;
        }
        if ($take !== null) {
            $sql .= " LIMIT  $take";
        }


        $stmt = $conn->prepare($sql);

        return $stmt->executeQuery()->fetchAllAssociative();
    }

    public function countClients2($filters = null, $type = 'client')
    {
        $sql_contrat = '';
        $has_date_contrat = false;
        $tabAddLeft = [];
        $conn = $this->em
            ->getConnection();


        if ($filters['AND']) {
            $sql = ($type == 'client') ? $this->selectCountClient : $this->selectCountNumSerie;
            foreach ($filters['AND'] as $key => $filter) {
                foreach($this->tabSearchLeft as $keySearch => $left) {
                    if (stripos($filter['filtersPlain'], $keySearch) !== false) {
                        if (is_array($left)) {
                            foreach ($left as $v) {
                                if (!in_array($v,$tabAddLeft)) $tabAddLeft[] = $v;
                            }
                        }else {
                            if (!in_array($left,$tabAddLeft)) $tabAddLeft[] = $left;
                        }

                    }
                }
                if (stripos($filter['filtersPlain'],'ct.date_debut_contrat') !== false || stripos($filter['filtersPlain'],'ct.date_fin_contrat') !== false) {
                    $sql_contrat .= ' AND '.$filter['filtersPlain'];
                    $has_date_contrat = true;
                }
                $sql .= " and (" .(str_replace('ea.adresse', "concat(ea.adresse, ' ', ea.adresse_complement)",$filter['filtersPlain'])) .')';

            }
            //si on a pas un filtre sur actif on met actif true par defaut pour l'équipement
            if (stripos($sql, 'e.actif = false') === false) {
                $sql.= ' AND e.actif = true';
            }
        } else {
            $sql = $this->selectCountClientLight;
        }
        $sql .= $this->selectCountEnd;
        $sql = str_replace('[replace_left_join]', implode(' ', $tabAddLeft), $sql );
        $sql = str_replace('[replace_contrat]', $sql_contrat, $sql );

        if ($has_date_contrat) str_replace("LEFT JOIN sav.contrat", "INNER JOIN sav.contrat", $sql);

        $stmt = $conn->prepare($sql);

       return $stmt->executeQuery()->fetchOne();
    }
    public function getClients($skip = null, $take = null, $filters = null) {

        $queryContrat = '(' . $this->em->createQueryBuilder()
                ->select("MAX(ct2.id)")
                ->from('App:Contrat', 'ct2')
                ->andWhere('ct2.client = c')->getDQL() . ') ';

        $queryPO = '(' . $this->em->createQueryBuilder()
                ->select("MAX(cpo2.id)")
                ->from('App:PorteOuverte', 'cpo2')
                ->andWhere('cpo2.client = c')->getDQL() . ') ';

        $qb = $this->createQueryBuilder('c');
        $qb->select("
         CASE
            WHEN e.idclient_souscripteur IS NOT NULL THEN e.idclient_souscripteur
            ELSE c.id
        END AS id, 
        c.numero_client as c_numero_client,
         esite.nom as esite_nom ,
         string_agg(distinct csss.nom, ',' ORDER BY csss.nom) as csss_nom,
          c.nom as c_nom,
           c.prenom as c_prenom,
            ec.libelle_court as ec_libelle_court,
            tc.libelle as tc_libelle,
             c.nom_entreprise as c_nom_entreprise
             , cp.libelle as cp_libelle,
             cs.numero_client as cs_numero_client,
              string_agg(distinct cast(tel.num_tel as varchar), ',' ORDER BY tel.num_tel) as tel_num_tel, 
              string_agg(distinct cast(mail.email as varchar), ',' ORDER BY mail.email) as mail_email,
             eat.libelle as eat_libelle,
              ea.adresse as ea_adresse,
               ea.code_postal as ea_code_postal,
                ea.ville as ea_ville,
                 e.num_serie_principal as e_num_serie_principal,
                 e.id as e_id,
              et.libelle as et_libelle,
               ef.libelle as ef_libelle,
                ctct.libelle_court as ctct_libelle_court,
                 efc.code as efc_code,
                  ct.groupe as ct_groupe,
              string_agg(distinct cast(eoo.libelle_court as varchar), ',' ORDER BY eoo.libelle_court) as eoo_libelle_court,
               cast(ct.date_debut_contrat as date) as ct_date_debut_contrat,
                cast(ct.date_fin_contrat as date) as ct_date_fin_contrat,
              cast(e.date_fin_garantie as date) as e_date_fin_garantie,
               cast(e.date_premier_allumage as date) as e_date_premier_allumage,
                el.libelle as el_libelle, r.libelle AS r_libelle,
              es.nom as es_nom, ua.nom as ua_nom, e.chantier as e_chantier,
              CASE WHEN e.salarie_frisquet =true then 'oui' else 'non' END as e_salarie_frisquet,
              cast(cpo.date as date) as cpo_date,
               CASE WHEN e.privilege = true then 'oui' else '' END as e_privilege,
                CASE WHEN e.actif =true then 'oui' else 'non' END as e_actif,
                e.numero_client_oracle as e_numero_client_oracle "
        )
            ->leftJoin('c.equipements', 'e')
            ->innerJoin('e.site', 'esite')
            ->leftJoin('e.equipement_famille', 'ef')
            ->leftJoin('e.client_souscripteur', 'cs')
            ->leftJoin('e.equipement_famille_contrat', 'efc')
            ->leftJoin('e.adresse', 'ea')
            ->leftJoin('ea.type_logement', 'eat')
            ->leftJoin('e.equipementOptions', 'eo')
            ->leftJoin('e.equipement_local', 'el')
            ->leftJoin('e.raccordement', 'r')
            ->leftJoin('e.secteurEquipements', 'es', 'WITH', )
            ->leftJoin('es.agentSecteurs', 'esas', 'WITH',  'esas.rang_tri = 1')
            ->leftJoin('esas.utilisateur_agent', 'ua')
            ->leftJoin('eo.option', 'eoo')
            ->leftJoin('e.equipement_type_puissance', 'etp')
            ->leftJoin('etp.equipement_type', 'et')
            ->leftJoin('c.etat_civil', 'ec')
            ->innerJoin('c.clientSites', 'ccs')
            ->innerJoin('ccs.site', 'csss')
            ->innerJoin('ccs.site', 'csss2')
            ->leftJoin('c.type_client', 'tc')
            ->leftJoin('c.categorie_pro', 'cp')
            ->leftJoin('c.clientTels', 'tel')
            ->leftJoin('c.clientEmails', 'mail')
            ->leftJoin('c.contrats', 'ct', 'WITH', 'ct.id = '. $queryContrat)
            ->leftJoin('c.porteOuvertes', 'cpo', 'WITH', 'cpo.id=' . $queryPO)
            ->leftJoin('ct.contrat_type', 'ctct')
            ->andWhere('e.actif = true')
            ->groupBy('c.id, c_numero_client','esite.nom', 'ec.libelle_court','tc.libelle', 'cp.libelle', 'cs.numero_client', 'eat.libelle',
                'ea.adresse', 'ea.code_postal', 'ea.ville', 'e.num_serie_principal','e.id', 'et.libelle', 'ef.libelle', 'ctct.libelle_court',
            'efc.code', 'ct.groupe', 'ct.date_debut_contrat', 'ct.date_fin_contrat', 'el.libelle', 'r.libelle', 'cpo.date', 'es.nom', 'ua.nom'
                )
        ;

        if ($skip !== null && $take !== null) $qb->setFirstResult( $skip )->setMaxResults( $take );
        if ($filters['AND']) {
            foreach ($filters['AND'] as $filter) {
                $qb->andWhere($filter['filters']);
                foreach ($filter['parameters'] as $parameter) {
                    $qb->setParameter(':' . $parameter['key'],$parameter['value']);
                }
            }
        }
        return $qb->getQuery()->getResult();
    }

    public function getClients2($skip = null, $take = null, $filters = null, $sites = []) {

        $qb = $this->createQueryBuilder('c')
            ->select("c.id as id_client, e.id as id_equipement")
            ->leftJoin('c.equipements', 'e', 'WITH', 'e.actif = true' );

        if ($sites) {
            $expr = $qb->expr();
            $qb->innerJoin('c.clientSites', 'cs')
                ->andWhere($expr->in('cs.site',  ':sites'))
                ->setParameter('sites', $sites);
        }

        if ($skip !== null && $take !== null) $qb->setFirstResult( $skip )->setMaxResults( $take );
//        if ($filters['AND']) {
//            foreach ($filters['AND'] as $filter) {
//                $qb->andWhere($filter['filters']);
//                foreach ($filter['parameters'] as $parameter) {
//                    $qb->setParameter(':' . $parameter['key'],$parameter['value']);
//                }
//            }
//        }
        return $qb->getQuery()->getResult();
    }

    public function countClients($filters = null) {
        $queryContrat = '(' . $this->em->createQueryBuilder()
                ->select("MAX(ct2.id)")
                ->from('App:Contrat', 'ct2')
                ->andWhere('ct2.client = c')->getDQL() . ') ';

        $queryPO = '(' . $this->em->createQueryBuilder()
                ->select("MAX(cpo2.id)")
                ->from('App:PorteOuverte', 'cpo2')
                ->andWhere('cpo2.client = c')->getDQL() . ') ';


        $qb = $this->createQueryBuilder('c')
            ->select("c.id, e.id, string_agg(distinct csss.nom, ',' ORDER BY csss.nom) as csss_nom,
            string_agg(distinct cast(tel.num_tel as varchar), ',' ORDER BY tel.num_tel) as tel_num_tel, string_agg(distinct cast(eoo.libelle_court as varchar), ',' ORDER BY eoo.libelle_court) as eoo_libelle_court,
            string_agg(distinct cast(mail.email as varchar), ',' ORDER BY mail.email) as mail_email")
            ->leftJoin('c.equipements', 'e')
            ->innerJoin('e.site', 'esite')
            ->leftJoin('e.equipement_famille', 'ef')
            ->leftJoin('e.client_souscripteur', 'cs')
            ->leftJoin('e.equipement_famille_contrat', 'efc')
            ->leftJoin('e.adresse', 'ea')
            ->leftJoin('ea.type_logement', 'eat')
            ->leftJoin('e.equipementOptions', 'eo')
            ->leftJoin('e.equipement_local', 'el')
            ->leftJoin('e.raccordement', 'el')
            ->leftJoin('e.privileges', 'ep')
            ->leftJoin('e.secteur', 'es')
            ->leftJoin('es.agentSecteurs', 'esas', 'WITH',  'esas.rang_tri = 1')
            ->leftJoin('esas.utilisateur_agent', 'ua')
            ->leftJoin('eo.option', 'eoo')
            ->leftJoin('e.equipement_type_puissance', 'etp')
            ->leftJoin('etp.equipement_type', 'et')
            ->leftJoin('c.etat_civil', 'ec')
            ->innerJoin('c.clientSites', 'ccs')
            ->innerJoin('ccs.site', 'csss')
            ->innerJoin('ccs.site', 'csss2')
            ->leftJoin('c.type_client', 'tc')
            ->leftJoin('c.categorie_pro', 'cp')
            ->leftJoin('c.clientTels', 'tel')
            ->leftJoin('c.clientEmails', 'mail')
            ->leftJoin('c.contrats', 'ct', 'WITH', 'ct.id = '. $queryContrat)
            ->leftJoin('c.porteOuvertes', 'cpo', 'WITH', 'cpo.id=' . $queryPO)
            ->leftJoin('ct.contrat_type', 'ctct')->groupBy('c.id, e.id')->distinct();

        if ($filters['AND']) {
            foreach ($filters['AND'] as $filter) {
                $qb->andWhere($filter['filters']);
                foreach ($filter['parameters'] as $parameter) {
                    $qb->setParameter(':' . $parameter['key'],$parameter['value']);
                }
            }
        }

        return $qb->getQuery()
            ->getArrayResult();
        ;
    }

    public function countClients3($filters = null, $sites =[]) {
            $queryBuilder = $this->createQueryBuilder('c');
            $queryBuilder
                ->select('count(c.id)')
                ->leftJoin('c.equipements', 'e', 'WITH', 'e.actif = true');

        if ($sites) {
            $expr = $queryBuilder->expr();
            $queryBuilder->innerJoin('c.clientSites', 'cs')
                ->andWhere($expr->in('cs.site',  ':sites'))
                ->setParameter('sites', $sites);
        }


        return $queryBuilder ->getQuery()
                ->getSingleScalarResult()
                ;
    }

    public function searchClients($typeClient,$datas, $categoriePro =null, $etatCivil =null) {
        $qb = $this->createQueryBuilder('c')
            ->select("c.id as id_client, c.nom, c.prenom,c.numero_client, c.nom_entreprise,etatCivil.libelle_court,categoriePro.libelle as categorie_pro, CONCAT(a.adresse, ' ', a.adresse_complement, ' ', a.code_postal, ' ', a.ville) as adresse,
            string_agg(tels.num_tel, ',') AS tel, string_agg(mail.email, ',') as email")
            ->leftJoin('c.etat_civil', 'etatCivil')
            ->leftJoin('c.categorie_pro', 'categoriePro')
            ->innerJoin('c.clientAdresses', 'ca', 'WITH', 'ca.actif=true')
            ->leftJoin('c.clientEmails', 'mail')
            ->leftJoin('c.clientTels', 'tels')
            ->innerJoin('ca.adresse', 'a')
            ->andWhere('c.type_client = :type_client')->setParameter('type_client', $typeClient);
        if ($categoriePro) {
            $qb->addSelect("c.nom_entreprise as nom_complet");
            $qb->andWhere('c.categorie_pro = :categorie_pro')->setParameter('categorie_pro', $categoriePro);
        } else if ($etatCivil) {
            $qb->addSelect("CONCAT(etatCivil.libelle_court, ' ', UPPER(c.nom), ' ', c.prenom) as nom_complet");
            $qb->andWhere('c.etat_civil = :etat_civil')->setParameter('etat_civil', $etatCivil);
            if (!empty($datas['client_nom']))
                $qb->andWhere('LOWER(c.nom) LIKE LOWER(:nom)')->setParameter('nom','%'.$datas['client_nom'].'%');
            if (!empty($datas['client_prenom']))
                $qb->andWhere('LOWER(c.prenom) LIKE LOWER(:prenom)')->setParameter('prenom','%'.$datas['client_prenom'].'%');
        }
        if (!empty($datas['email'])) {
            $qb->andWhere('LOWER(mail.email) LIKE LOWER(:email)')->setParameter('email','%'.$datas['email'].'%');
        }
        if ($datas['client_entreprise'])
            $qb->andWhere('LOWER(c.nom_entreprise) LIKE LOWER(:nom_entreprise)')->setParameter('nom_entreprise','%'.$datas['client_entreprise'].'%');

        $qb->groupBy('c.id, c.nom, c.prenom, c.numero_client, c.nom_entreprise, etatCivil.libelle_court, categoriePro.libelle, a.adresse, a.adresse_complement, a.code_postal, a.ville');

        return $qb->getQuery()->getResult();
    }

    public function getNouveauNumeroClient() {
        $year_month =  date('ym');

        $smt = $this->getEntityManager()
            ->getConnection()->prepare("
              SELECT  count(*) FROM sav.client 
              where SUBSTRING(numero_client::text,1,4) = :year_month
                ");
        $smt->bindParam(':year_month', $year_month);
        $smt->execute();
        return $smt->fetch(PDO::FETCH_COLUMN);

    }

    public function getDoublon($datas) {
        $qb = $this->createQueryBuilder('c')
            ->select("c.id")
            ->innerJoin('c.clientAdresses', 'ca', 'WITH', 'ca.actif=true');
            $qb->innerJoin('ca.adresse', 'a', 'WITH', $qb->expr()->andX(
                $qb->expr()->eq('LOWER(a.adresse)', ':adresse'),
                $qb->expr()->eq('a.code_postal', ':code_postal'),
                $qb->expr()->eq('LOWER(a.ville) ', ':ville')
            ));

        if ($datas['type'] == 2) {
            $qb->andWhere('LOWER(c.nom_entreprise)= :nom_entreprise');
            $qb->setParameter('nom_entreprise', $datas['entreprise']);
        } else {
            $qb->andWhere('LOWER(c.nom)=:nom')
                ->andWhere('LOWER(c.prenom)=:prenom');
            $qb->setParameter('prenom', $datas['prenom']);
            $qb->setParameter('nom', $datas['nom']);
        }
        $qb->setParameter('adresse', $datas['adresse'])
            ->setParameter('code_postal', $datas['code_postal'])
            ->setParameter('ville', $datas['ville']);

        return $qb->getQuery()->getResult();
    }

    public function searchByNumeroClient ($numeroClient) {
        $qb = $this->createQueryBuilder('c')
            ->select()
            ->andWhere('cast(c.numero_client as varchar) LIKE :numero_client')->setParameter('numero_client','%'.$numeroClient.'%');

        return $qb->getQuery()->getResult();
    }

}