<?php

namespace Modules\Additional\Specifique\Client118\Modules\EtatStatistique\Repository;

use Helpers\Validator\Validator;
use Modules\GlobalApplication\Outil\Outil_UO_oxadw;
use Helpers\Outil\Roles;
use Helpers\Outil\Date;

class TableauReporting
{

    protected $pdo;
    protected $pdoDW;

    public function __construct(\PDO $pdoDW, \PDO $pdo)
    {
        $this->pdo = $pdo;
        $this->pdoDW = $pdoDW;
    }
    
    public function listeLibellePrestation()
    {
        $req = "select DISTINCT(SCA_CODE_SOUS_CPTE_ANALYSE), SCA_DESIGNATION from SOUS_CPTES_ANALYSE with (nolock)";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute();
        
        $listeTmp = $requete->fetchAll($this->pdo::FETCH_ASSOC);
        
        $liste = [];
        foreach ($listeTmp as $ligne) {
            $liste[$ligne['SCA_CODE_SOUS_CPTE_ANALYSE']] = $ligne['SCA_DESIGNATION'];
        }
        
        return $liste;
    }
    
    public function listeLibelleNature()
    {
        $req = "select DISTINCT(SCA_CODE_CENTRE_ANALYSE), SCA_DESIGNATION_CENTRE_ANALYSE from SOUS_CPTES_ANALYSE with (nolock)";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute();
        
        $listeTmp = $requete->fetchAll($this->pdo::FETCH_ASSOC);
        
        $liste = [];
        foreach ($listeTmp as $ligne) {
            $liste[$ligne['SCA_CODE_CENTRE_ANALYSE']] = $ligne['SCA_DESIGNATION_CENTRE_ANALYSE'];
        }
        
        return $liste;
    }
    
    public function listeLibellePole()
    {
        $req = "select DISTINCT(SI_CODE_SECTION_IMPUTATION), SI_DESIGNATION from SECTIONS_IMPUTATIONS with (nolock)";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute();
        
        $listeTmp = $requete->fetchAll($this->pdo::FETCH_ASSOC);
        
        $liste = [];
        foreach ($listeTmp as $ligne) {
            $liste[$ligne['SI_CODE_SECTION_IMPUTATION']] = $ligne['SI_DESIGNATION'];
        }
        
        return $liste;
    }
    
    public function listeLibelleDirection()
    {
        $req = "select DISTINCT(SI_CODE_DIRECTION), SI_DESIGNATION_DIRECTION  from SECTIONS_IMPUTATIONS with (nolock)";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute();
        
        $listeTmp = $requete->fetchAll($this->pdo::FETCH_ASSOC);
        
        $liste = [];
        foreach ($listeTmp as $ligne) {
            $liste[$ligne['SI_CODE_DIRECTION']] = $ligne['SI_DESIGNATION_DIRECTION'];
        }
        
        return $liste;
    }
    
    public function listeSIOXADW($post)
    {
        $req = "select  DISTINCT(DIR_CODE_DIRECTION), DIR_DESIGNATION 
                from DIRECTIONS with (nolock) 
                left join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_DIRECTION = DIR_CODE_DIRECTION
                where 1=1";
        
        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', $droit['OX_SUITTUO'] == '0' ? 'UO' : 'OXAPROC')=='UO') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], ($post['SOCIETE']!=''? $post['SOCIETE']:$_SESSION['Session_SocUsrProfil']));
            
            $req   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            $req .= "  and DIR_CODE_SOCIETE=:societe";
            $arrayReq[':societe'] = ($post['SOCIETE']!=''? $post['SOCIETE']:$_SESSION['Session_SocUsrProfil']);
        } else {
            $req .= "  and DIR_CODE_SOCIETE=''";
        }
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($arrayReq);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeNature($post)
    {
        $req = "select CTA_CODE_SOCIETE, CTA_CODE_CENTRE_ANALYSE, CTA_DESIGNATION from CENTRES_ANALYSES with (nolock) where 1=1";
        
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            $req .= "  and CTA_CODE_SOCIETE=:societe";
            $arrayReq[':societe'] = ($post['SOCIETE']!=''? $post['SOCIETE']:$_SESSION['Session_SocUsrProfil']);
        } else {
            $req .= "  and CTA_CODE_SOCIETE=''";
        }
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($arrayReq);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listePole($post)
    {
        $req = "select SI_CODE_SECTION_IMPUTATION, SI_DESIGNATION from SECTIONS_IMPUTATIONS with (nolock) where 1=1";
        
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            $req .= "  and SI_CODE_SOCIETE=:societe";
            $arrayReq[':societe'] = ($post['SOCIETE']!=''? $post['SOCIETE']:$_SESSION['Session_SocUsrProfil']);
        } else {
            $req .= "  and SI_CODE_SOCIETE=''";
        }
        
        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', 'OXAPROC')=='UO' && $droit['OX_SUITTUO'] == '0') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], $post['SOCIETE']);
            
            $req   .= " and SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($arrayReq);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listePrestation($post)
    {
        $req = "select DISTINCT(SCA_CODE_SOUS_CPTE_ANALYSE), SCA_DESIGNATION from SOUS_CPTES_ANALYSE with (nolock) where 1=1";
        
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            $req .= "  and SCA_CODE_SOCIETE=:societe";
            $arrayReq[':societe'] = ($post['SOCIETE']!=''? $post['SOCIETE']:$_SESSION['Session_SocUsrProfil']);
        } else {
            $req .= "  and SCA_CODE_SOCIETE=''";
        }
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($arrayReq);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeSignataire($post)
    {
        $req = "select * from SIGNATAIRES with (nolock) where 1=1 and SIG_CODE_SIGNATAIRE != ''";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        
        $arrayReq = [];
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = $whereArray[1];
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeAcheteurs($post)
    {
        $req = "select ACH_CODE_ACHETEUR, ACH_NOM_ACHETEUR from ACHETEURS with (nolock) where 1=1 and ACH_CODE_ACHETEUR != ''";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        
        $arrayReq = [];
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = $whereArray[1];
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeFamilles($post)
    {
        $req = "SELECT FA_CODE_FAMILLE, FA_DESIGNATION FROM FAMILLES with (nolock) where 1=1 and FA_CODE_FAMILLE != ''";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        
        $arrayReq = [];
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = $whereArray[1];
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeSousFamilles($post)
    {
        $req = "select SF_CODE_SOUS_FAMILLE, SF_DESIGNATION from SOUS_FAMILLES with (nolock) where 1=1 and SF_CODE_FAMILLE != ''";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        
        $arrayReq = [];
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = $whereArray[1];
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeFournisseurs($post)
    {
        $req = "select FRS_CODE_FOURNISSEUR, FRS_RAISON_SOCIALE from FOURNISSEURS with (nolock) where 1=1 and FRS_CODE_FOURNISSEUR != ''";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        
        $arrayReq = [];
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = $whereArray[1];
            }
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    /* CBND */
    // phpcs:ignore
    public function listeNature_Direction($post)
    {
        $arrayReq = [];
        
        $filtreDA = '';
        $filtreCA = '';
        $filtreBR = '';
        $filtreFAc1 = '';
        $filtreFAc2 = '';
        
        if (isset($post['filter_PERIODES']) && $post['filter_PERIODES']) {
            $periode = $post['filter_PERIODES'];
        }
        
        
        if (isset($post['filter_SOCIETE']) && $post['filter_SOCIETE']) {
            $filtreDA .= ' and dal.DOC_CODE_SOCIETE = :soc1';
            $filtreCA .= ' and cal.DOC_CODE_SOCIETE = :soc2';
            $filtreBR .= ' and brl.DOC_CODE_SOCIETE = :soc3';
            $filtreFAc1 .= ' and fal.DOC_CODE_SOCIETE = :soc4';
            $filtreFAc2 .= ' and fal.DOC_CODE_SOCIETE = :soc5';
            
            $arrayReq[':soc1']=$post['filter_SOCIETE'];
            $arrayReq[':soc2']=$post['filter_SOCIETE'];
            $arrayReq[':soc3']=$post['filter_SOCIETE'];
            $arrayReq[':soc4']=$post['filter_SOCIETE'];
            $arrayReq[':soc5']=$post['filter_SOCIETE'];
        }
        
        if (isset($post['filter_DATE_DEB']) && $post['filter_DATE_DEB']) {
            Date::dateToSql($post['filter_DATE_DEB']);
            
            $filtreDA .= " and per.PER_DATEC >= :dateDeb1";
            $filtreCA .= " and per.PER_DATEC >= :dateDeb2";
            $filtreBR .= " and per.PER_DATEC >= :dateDeb3";
            $filtreFAc1 .= " and per.PER_DATEC >= :dateDeb4";
            $filtreFAc2 .= " and per.PER_DATEC >= :dateDeb5";
            
            $arrayReq[':dateDeb1'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb2'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb3'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb4'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb5'] = $post['filter_DATE_DEB'];
        }
        
        if (isset($post['filter_DATE_FIN']) && $post['filter_DATE_FIN']) {
            Date::dateToSql($post['filter_DATE_FIN']);
            
            $filtreDA .= " and per.PER_DATEC <= :dateFin1";
            $filtreCA .= " and per.PER_DATEC <= :dateFin2";
            $filtreBR .= " and per.PER_DATEC <= :dateFin3";
            $filtreFAc1 .= " and per.PER_DATEC <= :dateFin4";
            $filtreFAc1 .= " and per.PER_DATEC <= :dateFin5";
            
            $arrayReq[':dateFin1'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin2'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin3'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin4'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin5'] = $post['filter_DATE_FIN'];
        }
        
        if (isset($post['filter_DIRECTION']) && $post['filter_DIRECTION']) {
            $filtreDA .= " and sec.SI_CODE_DIRECTION = :direction1";
            $filtreCA .= " and sec.SI_CODE_DIRECTION = :direction2";
            $filtreBR .= " and sec.SI_CODE_DIRECTION = :direction3";
            $filtreFAc1 .= " and sec.SI_CODE_DIRECTION = :direction4";
            $filtreFAc2 .= " and sec.SI_CODE_DIRECTION = :direction5";
            
            $arrayReq[':direction1'] = $post['filter_DIRECTION'];
            $arrayReq[':direction2'] = $post['filter_DIRECTION'];
            $arrayReq[':direction3'] = $post['filter_DIRECTION'];
            $arrayReq[':direction4'] = $post['filter_DIRECTION'];
            $arrayReq[':direction5'] = $post['filter_DIRECTION'];
        }
        
        if (isset($post['filter_NATURE']) && $post['filter_NATURE']) {
            $filtreDA .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature1";
            $filtreCA .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature2";
            $filtreBR .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature3";
            $filtreFAc1 .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature4";
            $filtreFAc2 .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature5";
            
            $arrayReq[':nature1'] = $post['filter_NATURE'];
            $arrayReq[':nature2'] = $post['filter_NATURE'];
            $arrayReq[':nature3'] = $post['filter_NATURE'];
            $arrayReq[':nature4'] = $post['filter_NATURE'];
            $arrayReq[':nature5'] = $post['filter_NATURE'];
        }
        
        if (isset($post['filter_POLE']) && $post['filter_POLE']) {
            $filtreDA .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole1";
            $filtreCA .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole2";
            $filtreBR .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole3";
            $filtreFAc1 .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole4";
            $filtreFAc2 .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole5";
            
            $arrayReq[':pole1'] = $post['filter_POLE'];
            $arrayReq[':pole2'] = $post['filter_POLE'];
            $arrayReq[':pole3'] = $post['filter_POLE'];
            $arrayReq[':pole4'] = $post['filter_POLE'];
            $arrayReq[':pole5'] = $post['filter_POLE'];
        }
        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', 'OXAPROC')=='UO' && $droit['OX_SUITTUO'] == '0') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], $post['filter_SOCIETE']);
            
            $filtreDA   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreCA   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreBR   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreFAc1 .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreFAc2 .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        $req = "
                    select result.Direction,
                    result.Nature,
                    result.Budget,
                    result.Consommé,
                    result.Disponible,
                    sum(result.PREENGAGE) as 'PREENGAGE',
                    sum(result.ENGAGE)as 'ENGAGE',
                    sum(result.REALISE)as 'RECEP',
                    sum(result.FACTURE_AVEC)as 'FACTURE_AVEC',
                    sum(result.FACTURE_SANS)as 'FACTURE_SANS'
                    from
                    (
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(dal.DOC_MONTANT_LIGNE_TTC_DA) as 'PREENGAGE',
                    sum(0) as 'ENGAGE',
                    sum(0) as 'REALISE',
                    sum(0) as 'FACTURE_AVEC',
                    sum(0) as 'FACTURE_SANS'
                    from DA_LIGNES_DETAIL dal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=dal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=dal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=dal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=dal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=dal.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and (dal.DOC_STATUT_DA ='TRAITEE' OR dal.DOC_STATUT_DA='SIGNEE') $filtreDA
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'PREENGAGE',
                    sum(cal.DOC_MONTANT_LIGNE_TTC_CDE) as 'ENGAGE',
                    sum(0) as 'REALISE',
                    sum(0) as 'FACTURE_AVEC',
                    sum(0) as 'FACTURE_SANS'
                    from CA_LIGNES_DETAIL cal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=cal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=cal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=cal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=cal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=cal.DOC_DATE_LIVRAISON_CDE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and cal.DOC_STATUT_CDE in ('SOLDEE','LIVRAISON_PARTIELLE','A_ENVOYER','ENVOYEE','EN_SIGNATURE') $filtreCA
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'PREENGAGE',
                    sum(0) as 'ENGAGE',
                    sum(brl.DOC_MONTANT_LIGNE_TTC_BR) as 'REALISE',
                    sum(0) as 'FACTURE_AVEC',
                    sum(0) as 'FACTURE_SANS'
                    from BR_LIGNES_DETAIL brl with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=brl.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=brl.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=brl.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=brl.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=brl.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' $filtreBR
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'PREENGAGE',
                    sum(0) as 'ENGAGE',
                    sum(0) as 'REALISE',
                    sum(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_AVEC',
                    sum(0) as 'FACTURE_SANS'
                    from FAC_LIGNES_DETAIL fal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=fal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=fal.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and fal.DOC_NUMERO_CDE<>0 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $filtreFAc1
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'PREENGAGE',
                    sum(0) as 'ENGAGE',
                    sum(0) as 'REALISE',
                    sum(0) as 'FACTURE_AVEC',
                    sum(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_SANS'
                    from FAC_LIGNES_DETAIL fal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=fal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=fal.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and fal.DOC_NUMERO_CDE=0 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $filtreFAc2
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    ) result
                    group by result.Direction, result.Nature, result.Budget, result.Consommé, result.Disponible
                    order by result.Direction,result.Nature
        ";
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    /* FIN CBND */
    
    public function tableau1EngageParDirection($total, $post)
    {
        $select = '';
        $where1 = '';
        $where2 = '';
        $groupBy = '';
        $jointure = '';
        
        $si = false;
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            $arrayReq[':socReq1'] = $post['filter_SOCIETE'];
            $arrayReq[':socReq2'] = $post['filter_SOCIETE'];
            $si = true;
        }
        
        if (!$total) {
            if ($si) {
                $arrayReq[':socReq3'] = $post['filter_SOCIETE'];
            }
            
            $select = "SI_CODE_DIRECTION,";
            $groupBy = "group by SI_CODE_DIRECTION";
            $jointure = "left outer join DIRECTIONS dir with(nolock) on dir.DIR_CODE_DIRECTION=SI_CODE_DIRECTION and DIR_CODE_SOCIETE= ".($si? ":socReq3":"''")."";
        }
        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', 'OXAPROC')=='UO' && $droit['OX_SUITTUO'] == '0') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], $post['filter_SOCIETE']);
            $where1 .= " and si.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $where2 .= " and si.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        
        if (isset($post['filter_DATE_DEB']) && $post['filter_DATE_DEB']) {
            Date::dateToSql($post['filter_DATE_DEB']);
            
            $where1 .= " and per.PER_DATEC >= :dateDeb1";
            $arrayReq[':dateDeb1'] = $post['filter_DATE_DEB'];
            
            $where2 .= " and per.PER_DATEC >= :dateDeb2";
            $arrayReq[':dateDeb2'] = $post['filter_DATE_DEB'];
        }
        
        if (isset($post['filter_DATE_FIN']) && $post['filter_DATE_FIN']) {
            Date::dateToSql($post['filter_DATE_FIN']);
            
            $where1 .= " and per.PER_DATEC <= :dateFin1";
            $arrayReq[':dateFin1'] = $post['filter_DATE_FIN'];
            
            $where2 .= " and per.PER_DATEC <= :dateFin2";
            $arrayReq[':dateFin2'] = $post['filter_DATE_FIN'];
        }
        
        if ((isset($post['filter_DIRECTION']) && $post['filter_DIRECTION'])) {
            $where1 .= " and si.SI_CODE_DIRECTION = :direction1";
            $arrayReq[':direction1'] = $post['filter_DIRECTION'];
            
            $where2 .= " and si.SI_CODE_DIRECTION = :direction2";
            $arrayReq[':direction2'] = $post['filter_DIRECTION'];
        }
        
        // if ($direction) {
        //     $where1 .= " and si.SI_CODE_DIRECTION = :direction3";
        //     $arrayReq[':direction3'] = $direction;
            
        //     $where2 .= " and si.SI_CODE_DIRECTION = :direction4";
        //     $arrayReq[':direction4'] = $direction;
        // }
        
        if (isset($post['filter_ETAT']) && $post['filter_ETAT']) {
            $where1 .= " and ce.DOC_ETAT_CDE = :etat1";
            $arrayReq[':etat1'] = $post['filter_ETAT'];
            
            $where2 .= " and ce.DOC_ETAT_CDE = :etat2";
            $arrayReq[':etat2'] = $post['filter_ETAT'];
        }
        
        $arrayReq[':periode1'] = $post['filter_PERIODES'];
        $arrayReq[':societe1'] = $post['filter_SOCIETE'];
        $arrayReq[':periode2'] = $post['filter_PERIODES'];
        $arrayReq[':societe2'] = $post['filter_SOCIETE'];
       
        
        $req = "
            select $select ".(!$total? 'DIR_DESIGNATION,':'')." SUM(BDC_TTC) BDC_TTC,SUM(NBR_BDC) NBR_BDC,SUM(FACTURE_TTC) FACTURE_TTC,SUM(NBR_FACTURE) NBR_FACTURE from (
            	select
            	$select
            	sum(cal.DOC_MONTANT_LIGNE_TTC_CDE) as 'BDC_TTC',
            	count (distinct(cal.DOC_NUMERO_CDE)) as 'NBR_BDC',
            	0 as 'FACTURE_TTC',
            	0 as 'NBR_FACTURE'
            	from CA_LIGNES cal with(nolock)
            	left outer join SECTIONS_IMPUTATIONS si with(nolock) on si.SI_CODE_SECTION_IMPUTATION=cal.DOC_CODE_SECTION_IMPUTATION and si.SI_CODE_SOCIETE =  ".($si? ":socReq1":"''")."
            	left outer join PERIODES per with (nolock) on cal.DOC_DATE_LIVRAISON_CDE = per.PER_DATE
            	left outer join CA_ENTETES ce with (nolock) on ce.DOC_NUMERO_CDE = cal.DOC_NUMERO_CDE
            	where  per.PER_ANNEE=:periode1 and cal.DOC_CODE_SOCIETE=:societe1 and ce.DOC_ETAT_CDE<>'Z' $where1
            	$groupBy
            union 
            	select
            	$select
            	0 as 'BDC_TTC',
            	0 as 'NBR_BDC',
                sum (fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_TTC',
                count (distinct(fal.DOC_NUMERO_FAC)) as 'NBR_FACTURE'
            	from FAC_LIGNES_DETAIL fal with(nolock)
            	left outer join SECTIONS_IMPUTATIONS si with(nolock) on si.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and si.SI_CODE_SOCIETE =  ".($si? ":socReq2":"''")."
            	left outer join PERIODES per with(nolock) on fal.DOC_DATE_PERIODE = per.PER_DATE
            	where  per.PER_ANNEE=:periode2 and fal.DOC_CODE_SOCIETE=:societe2 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $where2
            	$groupBy
            ) ENGAGE
            $jointure
            
            $groupBy ".(!$total? ', DIR_DESIGNATION':'')."
        ";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function engageParDirection($total, $post, $tranche = null, $direction = null)
    {
        
        $si = false;
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            $arrayReq[':socReq1'] = $post['filter_SOCIETE'];
            $si = true;
        }
        
        
        $req = "select
                ".(!$total? 'SI_CODE_DIRECTION,':'')."
                sum(cal.DOC_MONTANT_LIGNE_TTC_CDE) as 'BDC_TTC',
                count (distinct(cal.DOC_NUMERO_CDE)) as 'NBR_BDC',
                sum (fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_TTC',
                count (distinct(fal.DOC_NUMERO_FAC)) as 'NBR_FACTURE'
                from CA_LIGNES cal with(nolock)
                left outer join FAC_LIGNES_DETAIL fal with (nolock) on fal.DOC_NUMERO_CDE=cal.DOC_NUMERO_CDE and fal.DOC_AFFECTATION_VALIDE_FAC = 1
                left outer join SECTIONS_IMPUTATIONS si with(nolock) on si.SI_CODE_SECTION_IMPUTATION=cal.DOC_CODE_SECTION_IMPUTATION and si.SI_CODE_SOCIETE =  ".($si? ":socReq1":"''")."
                left outer join PERIODES per with (nolock) on cal.DOC_DATE_LIVRAISON_CDE between per.PER_DATE_DEBUT_PERIODE and per.PER_DATE_FIN_PERIODE and per.PER_MOIS=1 and per.PER_JOUR=1
                left outer join PERIODES perDate with (nolock) on cal.DOC_DATE_LIVRAISON_CDE = perDate.PER_DATE
                left outer join CA_ENTETES ce with (nolock) on ce.DOC_NUMERO_CDE = cal.DOC_NUMERO_CDE
                where  per.PER_ANNEE=:periode and cal.DOC_CODE_SOCIETE=:societe and ce.DOC_ETAT_CDE<>'Z'
                ";

        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', 'OXAPROC')=='UO' && $droit['OX_SUITTUO'] == '0') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], $post['filter_SOCIETE']);
            $req .= " and si.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        $arrayReq[':periode'] = $post['filter_PERIODES'];
        $arrayReq[':societe'] = $post['filter_SOCIETE'];
        
        if (isset($post['filter_DATE_DEB']) && $post['filter_DATE_DEB']) {
            Date::dateToSql($post['filter_DATE_DEB']);
            
            $req .= " and perDate.PER_DATEC >= :dateDeb";
            $arrayReq[':dateDeb'] = $post['filter_DATE_DEB'];
        }
        
        if (isset($post['filter_DATE_FIN']) && $post['filter_DATE_FIN']) {
            Date::dateToSql($post['filter_DATE_FIN']);
            
            $req .= " and perDate.PER_DATEC <= :dateFin";
            $arrayReq[':dateFin'] = $post['filter_DATE_FIN'];
        }
        
        if ((isset($post['filter_DIRECTION']) && $post['filter_DIRECTION'])) {
            $req .= " and si.SI_CODE_DIRECTION = :direction";
            $arrayReq[':direction'] = $post['filter_DIRECTION'];
        }
        
        if ($direction) {
            $req .= " and si.SI_CODE_DIRECTION = :direction2";
            $arrayReq[':direction2'] = $direction;
        }
        
        if (isset($post['filter_ETAT']) && $post['filter_ETAT']) {
            $req .= " and ce.DOC_ETAT_CDE = :etat";
            $arrayReq[':etat'] = $post['filter_ETAT'];
        }
        
        // phpcs:ignore
        if ($tranche) { 
            // phpcs:ignore
            switch ($tranche) {
                
                // phpcs:ignore
                case 1;
                    $req .= ' and ce.MONTANT_TTC <= 2000';
                    break;
                case 2:
                    $req .= ' and ce.MONTANT_TTC > 2000 and ce.MONTANT_TTC<=30000';
                    break;
                case 3:
                    $req .= ' and ce.MONTANT_TTC > 30000';
                    break;
            }
        }
        
        if (isset($post['accordCadre']) && $post['accordCadre']) {
            if ($post['accordCadre'] == 'avec') {
                $req .= "and cal.DOC_CODE_ACCORD_CADRE<>''";
            } else {
                $req .= "and cal.DOC_CODE_ACCORD_CADRE=''";
            }
        }
        
        if (!$total) {
            $req .= " group by SI_CODE_DIRECTION";
        }
        
        if (!$total) {
            if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
                $arrayReq[':socReq2'] = $post['filter_SOCIETE'];
                $si = true;
            }
            
            $req = "select requete.*, DIR_DESIGNATION from ($req) as requete
                    left outer join DIRECTIONS dir with(nolock) on dir.DIR_CODE_DIRECTION=SI_CODE_DIRECTION and DIR_CODE_SOCIETE= ".($si? ":socReq2":"''")."
                    ";
        }
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);

        if ($tranche) {
            return $requete->fetch($this->pdo::FETCH_ASSOC);
        } else {
            return $requete->fetchAll($this->pdo::FETCH_ASSOC);
        }
    }
    
    // phpcs:ignore
    public function listeDirection_Nature_Pole_Article($post)
    {
        $arrayReq = [];
        
        $filtreDA = '';
        $filtreCA = '';
        $filtreBR = '';
        $filtreFAc1 = '';
        $filtreFAc2 = '';
        
        if (isset($post['filter_PERIODES']) && $post['filter_PERIODES']) {
            $periode = $post['filter_PERIODES'];
        }
        
        
        if (isset($post['filter_SOCIETE']) && $post['filter_SOCIETE']) {
            $filtreDA .= ' and dal.DOC_CODE_SOCIETE = :soc1';
            $filtreCA .= ' and cal.DOC_CODE_SOCIETE = :soc2';
            $filtreBR .= ' and brl.DOC_CODE_SOCIETE = :soc3';
            $filtreFAc1 .= ' and fal.DOC_CODE_SOCIETE = :soc4';
            $filtreFAc2 .= ' and fal.DOC_CODE_SOCIETE = :soc5';
            
            $arrayReq[':soc1']=$post['filter_SOCIETE'];
            $arrayReq[':soc2']=$post['filter_SOCIETE'];
            $arrayReq[':soc3']=$post['filter_SOCIETE'];
            $arrayReq[':soc4']=$post['filter_SOCIETE'];
            $arrayReq[':soc5']=$post['filter_SOCIETE'];
        }
        
        if (isset($post['filter_DATE_DEB']) && $post['filter_DATE_DEB']) {
            Date::dateToSql($post['filter_DATE_DEB']);
            
            $filtreDA .= " and per.PER_DATEC >= :dateDeb1";
            $filtreCA .= " and per.PER_DATEC >= :dateDeb2";
            $filtreBR .= " and per.PER_DATEC >= :dateDeb3";
            $filtreFAc1 .= " and per.PER_DATEC >= :dateDeb4";
            $filtreFAc2 .= " and per.PER_DATEC >= :dateDeb5";
            
            $arrayReq[':dateDeb1'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb2'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb3'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb4'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb5'] = $post['filter_DATE_DEB'];
        }
        
        if (isset($post['filter_DATE_FIN']) && $post['filter_DATE_FIN']) {
            Date::dateToSql($post['filter_DATE_FIN']);
            
            $filtreDA .= " and per.PER_DATEC <= :dateFin1";
            $filtreCA .= " and per.PER_DATEC <= :dateFin2";
            $filtreBR .= " and per.PER_DATEC <= :dateFin3";
            $filtreFAc1 .= " and per.PER_DATEC <= :dateFin4";
            $filtreFAc2 .= " and per.PER_DATEC <= :dateFin5";
            
            $arrayReq[':dateFin1'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin2'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin3'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin4'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin5'] = $post['filter_DATE_FIN'];
        }
        
        if (isset($post['filter_DIRECTION']) && $post['filter_DIRECTION']) {
            $filtreDA .= " and sec.SI_CODE_DIRECTION = :direction1";
            $filtreCA .= " and sec.SI_CODE_DIRECTION = :direction2";
            $filtreBR .= " and sec.SI_CODE_DIRECTION = :direction3";
            $filtreFAc1 .= " and sec.SI_CODE_DIRECTION = :direction4";
            $filtreFAc2 .= " and sec.SI_CODE_DIRECTION = :direction5";
            
            $arrayReq[':direction1'] = $post['filter_DIRECTION'];
            $arrayReq[':direction2'] = $post['filter_DIRECTION'];
            $arrayReq[':direction3'] = $post['filter_DIRECTION'];
            $arrayReq[':direction4'] = $post['filter_DIRECTION'];
            $arrayReq[':direction5'] = $post['filter_DIRECTION'];
        }
        
        if (isset($post['filter_NATURE']) && $post['filter_NATURE']) {
            $filtreDA .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature1";
            $filtreCA .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature2";
            $filtreBR .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature3";
            $filtreFAc1 .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature4";
            $filtreFAc2 .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature5";
            
            $arrayReq[':nature1'] = $post['filter_NATURE'];
            $arrayReq[':nature2'] = $post['filter_NATURE'];
            $arrayReq[':nature3'] = $post['filter_NATURE'];
            $arrayReq[':nature4'] = $post['filter_NATURE'];
            $arrayReq[':nature5'] = $post['filter_NATURE'];
        }
        
        if (isset($post['filter_POLE']) && $post['filter_POLE']) {
            $filtreDA .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole1";
            $filtreCA .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole2";
            $filtreBR .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole3";
            $filtreFAc1 .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole4";
            $filtreFAc2 .= " and sec.SI_CODE_SECTION_IMPUTATION = :pole5";
            
            $arrayReq[':pole1'] = $post['filter_POLE'];
            $arrayReq[':pole2'] = $post['filter_POLE'];
            $arrayReq[':pole3'] = $post['filter_POLE'];
            $arrayReq[':pole4'] = $post['filter_POLE'];
            $arrayReq[':pole5'] = $post['filter_POLE'];
        }

        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', 'OXAPROC')=='UO' && $droit['OX_SUITTUO'] == '0') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], $post['filter_SOCIETE']);
            
            $filtreDA   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreCA   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreBR   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreFAc1 .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreFAc2 .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        $req = "
                    select result.Direction,
                    result.Nature,
                    result.Pôle,
                    result.Prestation,
                    result.Article,
                    result.Budget,
                    result.Consommé,
                    result.Disponible,
                    sum(result.PREENGAGE) as 'PREENGAGE',
                    sum(result.ENGAGE)as 'ENGAGE',
                    sum(result.REALISE)as 'REALISE',
                    sum(result.FACTURE_AVEC_COMMANDE)as 'FACTURE AVEC COMMANDE',
                    sum(result.FACTURE_SANS_COMMANDE)as 'FACTURE SANS COMMANDE'
                    from
                    (
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    sec.SI_CODE_SECTION_IMPUTATION as 'Pôle',
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    dal.DOC_DESIGNATION_ARTICLE_DA as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(dal.DOC_MONTANT_LIGNE_TTC_DA) as 'PREENGAGE',
                    sum(0) as 'ENGAGE',
                    sum(0) as 'REALISE',
                    sum(0) as 'FACTURE_AVEC_COMMANDE',
                    sum(0) as 'FACTURE_SANS_COMMANDE'
                    from DA_LIGNES_DETAIL dal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=dal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=dal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=dal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=dal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=dal.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and (dal.DOC_STATUT_DA ='TRAITEE' OR dal.DOC_STATUT_DA='SIGNEE') $filtreDA
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    sec.SI_CODE_SECTION_IMPUTATION,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    dal.DOC_DESIGNATION_ARTICLE_DA,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    sec.SI_CODE_SECTION_IMPUTATION as 'Pôle',
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    cal.DOC_DESIGNATION_ARTICLE_CDE as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'PREENGAGE',
                    sum(cal.DOC_MONTANT_LIGNE_TTC_CDE) as 'ENGAGE',
                    sum(0) as 'REALISE',
                    sum(0) as 'FACTURE_AVEC_COMMANDE',
                    sum(0) as 'FACTURE_SANS_COMMANDE'
                    from CA_LIGNES_DETAIL cal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=cal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=cal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=cal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=cal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=cal.DOC_DATE_LIVRAISON_CDE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and cal.DOC_STATUT_CDE in ('SOLDEE','LIVRAISON_PARTIELLE','A_ENVOYER','ENVOYEE','EN_SIGNATURE') $filtreCA
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    sec.SI_CODE_SECTION_IMPUTATION,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    cal.DOC_DESIGNATION_ARTICLE_CDE,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    sec.SI_CODE_SECTION_IMPUTATION as 'Pôle',
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    brl.DOC_DESIGNATION_ARTICLE_BR as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'PREENGAGE',
                    sum(0) as 'ENGAGE',
                    sum(brl.DOC_MONTANT_LIGNE_TTC_BR) as 'REALISE',
                    sum(0) as 'FACTURE_AVEC_COMMANDE',
                    sum(0) as 'FACTURE_SANS_COMMANDE'
                    from BR_LIGNES_DETAIL brl with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=brl.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=brl.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=brl.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=brl.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=brl.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' $filtreBR
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    sec.SI_CODE_SECTION_IMPUTATION,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    brl.DOC_DESIGNATION_ARTICLE_BR,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    sec.SI_CODE_SECTION_IMPUTATION as 'Pôle',
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    fal.DOC_DESIGNATION_FAC as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'PREENGAGE',
                    sum(0) as 'ENGAGE',
                    sum(0) as 'REALISE',
                    sum(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_AVEC_COMMANDE',
                    sum(0) as 'FACTURE_SANS_COMMANDE'
                    from FAC_LIGNES_DETAIL fal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=fal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=fal.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and fal.DOC_NUMERO_CDE<>0 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $filtreFAc1
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    sec.SI_CODE_SECTION_IMPUTATION,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    fal.DOC_DESIGNATION_FAC,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    sec.SI_CODE_SECTION_IMPUTATION as 'Pôle',
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    fal.DOC_DESIGNATION_FAC as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'PREENGAGE',
                    sum(0) as 'ENGAGE',
                    sum(0) as 'REALISE',
                    sum(0) as 'FACTURE_AVEC_COMMANDE',
                    sum(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_SANS_COMMANDE'
                    from FAC_LIGNES_DETAIL fal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=fal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=fal.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and fal.DOC_NUMERO_CDE=0 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $filtreFAc2
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    sec.SI_CODE_SECTION_IMPUTATION,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    fal.DOC_DESIGNATION_FAC,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    ) result
                    group by result.Direction, result.Nature, result.Pôle, result.Prestation, result.Article, result.Budget, result.Consommé, result.Disponible
                    order by result.Direction,result.Nature,result.Pôle,result.Prestation,result.Article
        ";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }

    // phpcs:ignore
    public function listeDirection_Nature_Fournisseur_Article($post)
    {
        $arrayReq = [];

        $filtreCA = '';
        $filtreBR = '';
        $filtreFAc1 = '';
        $filtreFAc2 = '';
        
        if (isset($post['filter_PERIODES']) && $post['filter_PERIODES']) {
            $periode = $post['filter_PERIODES'];
        }
        
        if (isset($post['filter_SOCIETE']) && $post['filter_SOCIETE']) {
            $filtreCA .= ' and cal.DOC_CODE_SOCIETE = :soc2';
            $filtreBR .= ' and brl.DOC_CODE_SOCIETE = :soc3';
            $filtreFAc1 .= ' and fal.DOC_CODE_SOCIETE = :soc4';
            $filtreFAc2 .= ' and fal.DOC_CODE_SOCIETE = :soc5';
            
            $arrayReq[':soc2']=$post['filter_SOCIETE'];
            $arrayReq[':soc3']=$post['filter_SOCIETE'];
            $arrayReq[':soc4']=$post['filter_SOCIETE'];
            $arrayReq[':soc5']=$post['filter_SOCIETE'];
        }
        
        if (isset($post['filter_DATE_DEB']) && $post['filter_DATE_DEB']) {
            Date::dateToSql($post['filter_DATE_DEB']);
            
            $filtreCA .= " and per.PER_DATEC >= :dateDeb2";
            $filtreBR .= " and per.PER_DATEC >= :dateDeb3";
            $filtreFAc1 .= " and per.PER_DATEC >= :dateDeb4";
            $filtreFAc2 .= " and per.PER_DATEC >= :dateDeb5";
            
            $arrayReq[':dateDeb2'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb3'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb4'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb5'] = $post['filter_DATE_DEB'];
        }
        
        if (isset($post['filter_DATE_FIN']) && $post['filter_DATE_FIN']) {
            Date::dateToSql($post['filter_DATE_FIN']);
            
            $filtreCA .= " and per.PER_DATEC <= :dateFin2";
            $filtreBR .= " and per.PER_DATEC <= :dateFin3";
            $filtreFAc1 .= " and per.PER_DATEC <= :dateFin4";
            $filtreFAc2 .= " and per.PER_DATEC <= :dateFin5";
            
            $arrayReq[':dateFin2'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin3'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin4'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin5'] = $post['filter_DATE_FIN'];
        }
        
        if (isset($post['filter_DIRECTION']) && $post['filter_DIRECTION']) {
            $filtreCA .= " and sec.SI_CODE_DIRECTION = :direction2";
            $filtreBR .= " and sec.SI_CODE_DIRECTION = :direction3";
            $filtreFAc1 .= " and sec.SI_CODE_DIRECTION = :direction4";
            $filtreFAc2 .= " and sec.SI_CODE_DIRECTION = :direction5";
            
            $arrayReq[':direction2'] = $post['filter_DIRECTION'];
            $arrayReq[':direction3'] = $post['filter_DIRECTION'];
            $arrayReq[':direction4'] = $post['filter_DIRECTION'];
            $arrayReq[':direction5'] = $post['filter_DIRECTION'];
        }
        
        if (isset($post['filter_NATURE']) && $post['filter_NATURE']) {
            $filtreCA .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature2";
            $filtreBR .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature3";
            $filtreFAc1 .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature4";
            $filtreFAc2 .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature5";
            
            $arrayReq[':nature2'] = $post['filter_NATURE'];
            $arrayReq[':nature3'] = $post['filter_NATURE'];
            $arrayReq[':nature4'] = $post['filter_NATURE'];
            $arrayReq[':nature5'] = $post['filter_NATURE'];
        }
        
        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', 'OXAPROC')=='UO' && $droit['OX_SUITTUO'] == '0') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], $post['filter_SOCIETE']);
            
            $filtreCA   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreBR   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreFAc1 .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreFAc2 .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        $req = "
                    select result.Direction,
                    result.Nature,
                    result.DOC_CODE_FOURNISSEUR,
                    result.Prestation,
                    result.Article,
                    result.Budget,
                    result.Consommé,
                    result.Disponible,
                    sum(result.NB_ENGAGE) as 'NB_ENGAGE',
                    sum(result.ENGAGE)as 'ENGAGE',
                    sum(result.NB_REALISE) as 'NB_REALISE',
                    sum(result.REALISE)as 'REALISE',
                    sum(result.NBR_FAC_AVEC_CDE) as 'NBR_FAC_AVEC_CDE',
                    sum(result.FACTURE_AVEC_COMMANDE)as 'FACTURE AVEC COMMANDE',
                    sum(result.NBR_FAC_SANS_CDE) as 'NBR_FAC_SANS_CDE',
                    sum(result.FACTURE_SANS_COMMANDE)as 'FACTURE SANS COMMANDE'
                    from
                    (
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    cal.DOC_CODE_FOURNISSEUR,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    cal.DOC_DESIGNATION_ARTICLE_CDE as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    count(cal.DOC_NUMERO_DOC) as 'NB_ENGAGE',
                    sum(cal.DOC_MONTANT_LIGNE_TTC_CDE) as 'ENGAGE',
                    sum(0) as 'NB_REALISE',
                    sum(0) as 'REALISE',
					SUM(0) as 'NBR_FAC_AVEC_CDE',
                    sum(0) as 'FACTURE_AVEC_COMMANDE',
					SUM(0) as 'NBR_FAC_SANS_CDE',
                    sum(0) as 'FACTURE_SANS_COMMANDE'
                    from CA_LIGNES_DETAIL cal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=cal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=cal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=cal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=cal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=cal.DOC_DATE_LIVRAISON_CDE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and cal.DOC_STATUT_CDE in ('SOLDEE','LIVRAISON_PARTIELLE','A_ENVOYER','ENVOYEE','EN_SIGNATURE') $filtreCA
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    cal.DOC_CODE_FOURNISSEUR,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    cal.DOC_DESIGNATION_ARTICLE_CDE,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    brl.DOC_CODE_FOURNISSEUR,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    brl.DOC_DESIGNATION_ARTICLE_BR as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'NB_ENGAGE',
                    sum(0) as 'ENGAGE',
                    count(brl.DOC_NUMERO_DOC) as 'NB_REALISE',
                    sum(brl.DOC_MONTANT_LIGNE_TTC_BR) as 'REALISE',
					SUM(0) as 'NBR_FAC_AVEC_CDE',
                    sum(0) as 'FACTURE_AVEC_COMMANDE',
					SUM(0) as 'NBR_FAC_SANS_CDE',
                    sum(0) as 'FACTURE_SANS_COMMANDE'
                    from BR_LIGNES_DETAIL brl with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=brl.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=brl.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=brl.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=brl.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=brl.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' $filtreBR
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    brl.DOC_CODE_FOURNISSEUR,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    brl.DOC_DESIGNATION_ARTICLE_BR,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    fal.DOC_CODE_FOURNISSEUR,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    fal.DOC_DESIGNATION_FAC as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'ENGAGE',
                    sum(0) as 'NB_ENGAGE',
                    sum(0) as 'NB_REALISE',
                    sum(0) as 'REALISE',
					COUNT(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'NBR_FAC_AVEC_CDE',
                    sum(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_AVEC_COMMANDE',
					SUM(0) as 'NBR_FAC_SANS_CDE',
                    sum(0) as 'FACTURE_SANS_COMMANDE'
                    from FAC_LIGNES_DETAIL fal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=fal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=fal.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and fal.DOC_NUMERO_CDE<>0 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $filtreFAc1
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    fal.DOC_CODE_FOURNISSEUR,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    fal.DOC_DESIGNATION_FAC,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    
                    UNION
                    
                    select sec.SI_CODE_DIRECTION as 'Direction',
                    sca.SCA_CODE_CENTRE_ANALYSE as 'Nature',
                    fal.DOC_CODE_FOURNISSEUR,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE as 'Prestation',
                    fal.DOC_DESIGNATION_FAC as 'Article',
                    bud.BUC_MONTANT_BUDGET as 'Budget',
                    bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                    (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                    sum(0) as 'ENGAGE',
                    sum(0) as 'NB_ENGAGE',
                    sum(0) as 'NB_REALISE',
                    sum(0) as 'REALISE',
					SUM(0) as 'NBR_FAC_AVEC_CDE',
                    sum(0) as 'FACTURE_AVEC_COMMANDE',
					COUNT(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'NBR_FAC_SANS_CDE',
                    sum(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_SANS_COMMANDE'
                    from FAC_LIGNES_DETAIL fal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=fal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                    left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                    left outer join PERIODES per with (nolock) on per.PER_DATE=fal.DOC_DATE_PERIODE
                    where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and fal.DOC_NUMERO_CDE=0 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $filtreFAc2
                    group by sec.SI_CODE_DIRECTION,
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    fal.DOC_CODE_FOURNISSEUR,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    fal.DOC_DESIGNATION_FAC,
                    bud.BUC_MONTANT_BUDGET,
                    bud.BUC_MONTANT_CONSOMMATION
                    ) result
                    group by result.Direction, result.Nature, result.DOC_CODE_FOURNISSEUR, result.Prestation, result.Article, result.Budget, result.Consommé, result.Disponible
                    order by result.Direction,result.Nature,result.DOC_CODE_FOURNISSEUR,result.Prestation,result.Article
        ";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeCodeGestion()
    {
        $req = "select DISTINCT(CG_CODE_GESTION_AFFICHE),CG_LIBELLE_CODE_GESTION  from CODES_GESTION";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute();
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    //fixe ddt
    // phpcs:ignore
    public function liste_DDT($post)
    {
        $filtreCA = '';
        $filtreBR = '';
        $filtreFAc1 = '';
        $filtreFAc2 = '';
        
        if (isset($post['filter_PERIODES']) && $post['filter_PERIODES']) {
            $periode = $post['filter_PERIODES'];
        }
        
        $arrayReq = [];
        
        if (isset($post['filter_SOCIETE']) && $post['filter_SOCIETE']) {
            $filtreCA .= ' and cal.DOC_CODE_SOCIETE = :soc2';
            $filtreBR .= ' and brl.DOC_CODE_SOCIETE = :soc3';
            $filtreFAc1 .= ' and fal.DOC_CODE_SOCIETE = :soc4';
            $filtreFAc2 .= ' and fal.DOC_CODE_SOCIETE = :soc5';
            
            $arrayReq[':soc2']=$post['filter_SOCIETE'];
            $arrayReq[':soc3']=$post['filter_SOCIETE'];
            $arrayReq[':soc4']=$post['filter_SOCIETE'];
            $arrayReq[':soc5']=$post['filter_SOCIETE'];
        }
        
        if (isset($post['filter_DATE_DEB']) && $post['filter_DATE_DEB']) {
            Date::dateToSql($post['filter_DATE_DEB']);
            
            $filtreCA .= " and per.PER_DATEC >= :dateDeb2";
            $filtreBR .= " and per.PER_DATEC >= :dateDeb3";
            $filtreFAc1 .= " and per.PER_DATEC >= :dateDeb4";
            $filtreFAc2 .= " and per.PER_DATEC >= :dateDeb5";
            
            $arrayReq[':dateDeb2'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb3'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb4'] = $post['filter_DATE_DEB'];
            $arrayReq[':dateDeb5'] = $post['filter_DATE_DEB'];
        }
        
        if (isset($post['filter_DATE_FIN']) && $post['filter_DATE_FIN']) {
            Date::dateToSql($post['filter_DATE_FIN']);
            
            $filtreCA .= " and per.PER_DATEC <= :dateFin2";
            $filtreBR .= " and per.PER_DATEC <= :dateFin3";
            $filtreFAc1 .= " and per.PER_DATEC <= :dateFin4";
            $filtreFAc2 .= " and per.PER_DATEC <= :dateFin5";
            
            $arrayReq[':dateFin2'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin3'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin4'] = $post['filter_DATE_FIN'];
            $arrayReq[':dateFin5'] = $post['filter_DATE_FIN'];
        }
        
        if (isset($post['filter_DIRECTION']) && $post['filter_DIRECTION']) {
            $filtreCA .= " and sec.SI_CODE_DIRECTION = :direction2";
            $filtreBR .= " and sec.SI_CODE_DIRECTION = :direction3";
            $filtreFAc1 .= " and sec.SI_CODE_DIRECTION = :direction4";
            $filtreFAc2 .= " and sec.SI_CODE_DIRECTION = :direction5";
            
            $arrayReq[':direction2'] = $post['filter_DIRECTION'];
            $arrayReq[':direction3'] = $post['filter_DIRECTION'];
            $arrayReq[':direction4'] = $post['filter_DIRECTION'];
            $arrayReq[':direction5'] = $post['filter_DIRECTION'];
        }
        
        if (isset($post['filter_NATURE']) && $post['filter_NATURE']) {
            $filtreCA .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature2";
            $filtreBR .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature3";
            $filtreFAc1 .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature4";
            $filtreFAc2 .= " and sca.SCA_CODE_CENTRE_ANALYSE = :nature5";
            
            $arrayReq[':nature2'] = $post['filter_NATURE'];
            $arrayReq[':nature3'] = $post['filter_NATURE'];
            $arrayReq[':nature4'] = $post['filter_NATURE'];
            $arrayReq[':nature5'] = $post['filter_NATURE'];
        }
        
        if (isset($post['filter_POLE']) && $post['filter_POLE']) {
            $filtreCA .= " and sec.SI_DESIGNATION = :pole2";
            $filtreBR .= " and sec.SI_DESIGNATION = :pole3";
            $filtreFAc1 .= " and sec.SI_DESIGNATION = :pole4";
            $filtreFAc2 .= " and sec.SI_DESIGNATION = :pole5";
            
            $arrayReq[':pole2'] = $post['SI_CODE_SECTION_IMPUTATION'];
            $arrayReq[':pole3'] = $post['SI_CODE_SECTION_IMPUTATION'];
            $arrayReq[':pole4'] = $post['SI_CODE_SECTION_IMPUTATION'];
            $arrayReq[':pole5'] = $post['SI_CODE_SECTION_IMPUTATION'];
        }
        
        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', 'OXAPROC')=='UO' && $droit['OX_SUITTUO'] == '0') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], $post['filter_SOCIETE']);
            
            $filtreCA   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreBR   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreFAc1 .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
            $filtreFAc2 .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        $req = "
           select result.Nature,
            result.Budget,
            result.Disponible,
            result.Pôle,
            result.Prestation,
            result.Article,
            result.DOC_CODE_FOURNISSEUR,
            result.Service,
            sum(result.ENGAGE)as 'ENGAGE',
            sum(result.REALISE)as 'REALISE',
            sum(result.FACTURE_AVEC_COMMANDE)as 'FACTURE AVEC COMMANDE',
            sum(result.FACTURE_SANS_COMMANDE)as 'FACTURE SANS COMMANDE'
            from
            (
                Select
                sca.SCA_DESIGNATION_CENTRE_ANALYSE as 'Nature',
                sec.SI_DESIGNATION as 'Pôle',
                cdg.CG_CODE_GESTION_AFFICHE as 'Service',
                sca.SCA_DESIGNATION as 'Prestation',
                cal.DOC_DESIGNATION_ARTICLE_CDE as 'Article',
                cal.DOC_CODE_FOURNISSEUR,
                bud.BUC_MONTANT_BUDGET as 'Budget',
                bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                sum(0) as 'PREENGAGE',
                sum(cal.DOC_MONTANT_LIGNE_TTC_CDE) as 'ENGAGE',
                sum(0) as 'REALISE',
                sum(0) as 'FACTURE_AVEC_COMMANDE',
                sum(0) as 'FACTURE_SANS_COMMANDE'
                from CA_LIGNES_DETAIL cal with (nolock)
                left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=cal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=cal.DOC_CODE_SOCIETE
                left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=cal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=cal.DOC_CODE_SOCIETE
                left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                left outer join PERIODES per with (nolock) on per.PER_DATE=cal.DOC_DATE_LIVRAISON_CDE
                left outer join CODES_GESTION cdg with (nolock) on cdg.CG_CODE_GESTION_AFFICHE=cal.DOC_CODE_GESTION_CDE
                where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and cal.DOC_STATUT_CDE in ('SOLDEE','LIVRAISON_PARTIELLE','A_ENVOYER','ENVOYEE','EN_SIGNATURE') $filtreCA
                group by
                sca.SCA_DESIGNATION_CENTRE_ANALYSE,
                sec.SI_DESIGNATION,
                cdg.CG_CODE_GESTION_AFFICHE,
                sca.SCA_DESIGNATION,
                cal.DOC_DESIGNATION_ARTICLE_CDE,
                cal.DOC_CODE_FOURNISSEUR,
                bud.BUC_MONTANT_BUDGET,
                bud.BUC_MONTANT_CONSOMMATION
                
                
                UNION
                
                select
                sca.SCA_DESIGNATION_CENTRE_ANALYSE as 'Nature',
                sec.SI_DESIGNATION as 'Pôle',
                cdg.CG_CODE_GESTION_AFFICHE as 'Service',
                sca.SCA_DESIGNATION as 'Prestation',
                brl.DOC_DESIGNATION_ARTICLE_BR as 'Article',
                brl.DOC_CODE_FOURNISSEUR,
                bud.BUC_MONTANT_BUDGET as 'Budget',
                bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                sum(0) as 'PREENGAGE',
                sum(0) as 'ENGAGE',
                sum(brl.DOC_MONTANT_LIGNE_TTC_BR) as 'REALISE',
                sum(0) as 'FACTURE_AVEC_COMMANDE',
                sum(0) as 'FACTURE_SANS_COMMANDE'
                from BR_LIGNES_DETAIL brl with (nolock)
                left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=brl.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=brl.DOC_CODE_SOCIETE
                left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=brl.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=brl.DOC_CODE_SOCIETE
                left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                left outer join PERIODES per with (nolock) on per.PER_DATE=brl.DOC_DATE_PERIODE
                left outer join CA_LIGNES_DETAIL cal with (nolock) on cal.DOC_NUMERO_CDE=brl.DOC_NUMERO_CDE and cal.DOC_NUMERO_LIGNE_CDE=brl.DOC_NUMERO_LIGNE_CDE
                left outer join CODES_GESTION cdg with (nolock) on cdg.CG_CODE_GESTION_AFFICHE=cal.DOC_CODE_GESTION_AFFICHE
                where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' $filtreBR
                group by
                sca.SCA_DESIGNATION_CENTRE_ANALYSE,
                sec.SI_DESIGNATION,
                cdg.CG_CODE_GESTION_AFFICHE,
                sca.SCA_DESIGNATION,
                brl.DOC_DESIGNATION_ARTICLE_BR,
                brl.DOC_CODE_FOURNISSEUR,
                bud.BUC_MONTANT_BUDGET,
                bud.BUC_MONTANT_CONSOMMATION
                
                
                UNION
                
                select
                sca.SCA_DESIGNATION_CENTRE_ANALYSE as 'Nature',
                sec.SI_DESIGNATION as 'Pôle',
                cdg.CG_CODE_GESTION_AFFICHE as 'Service',
                sca.SCA_DESIGNATION as 'Prestation',
                fal.DOC_DESIGNATION_FAC as 'Article',
                fal.DOC_CODE_FOURNISSEUR,
                bud.BUC_MONTANT_BUDGET as 'Budget',
                bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                sum(0) as 'PREENGAGE',
                sum(0) as 'ENGAGE',
                sum(0) as 'REALISE',
                sum(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_AVEC_COMMANDE',
                sum(0) as 'FACTURE_SANS_COMMANDE'
                from FAC_LIGNES_DETAIL fal with (nolock)
                left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=fal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                left outer join PERIODES per with (nolock) on per.PER_DATE=fal.DOC_DATE_PERIODE
                left outer join CA_LIGNES_DETAIL cal with (nolock) on cal.DOC_NUMERO_CDE=fal.DOC_NUMERO_CDE and cal.DOC_NUMERO_LIGNE_CDE=fal.DOC_NUMERO_LIGNE_CDE
                left outer join CODES_GESTION cdg with (nolock) on cdg.CG_CODE_GESTION_AFFICHE=cal.DOC_CODE_GESTION_AFFICHE
                where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and fal.DOC_NUMERO_CDE<>0 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $filtreFAc1
                group by
                sca.SCA_DESIGNATION_CENTRE_ANALYSE,
                sec.SI_DESIGNATION,
                sca.SCA_DESIGNATION,
                cdg.CG_CODE_GESTION_AFFICHE,
                fal.DOC_DESIGNATION_FAC,
                fal.DOC_CODE_FOURNISSEUR,
                bud.BUC_MONTANT_BUDGET,
                bud.BUC_MONTANT_CONSOMMATION
                
                UNION
                
                select
                sca.SCA_DESIGNATION_CENTRE_ANALYSE as 'Nature',
                sec.SI_DESIGNATION as 'Pôle',
                cdg.CG_CODE_GESTION_AFFICHE as 'Service',
                sca.SCA_DESIGNATION as 'Prestation',
                fal.DOC_DESIGNATION_FAC as 'Article',
                fal.DOC_CODE_FOURNISSEUR,
                bud.BUC_MONTANT_BUDGET as 'Budget',
                bud.BUC_MONTANT_CONSOMMATION as 'Consommé',
                (bud.BUC_MONTANT_BUDGET-bud.BUC_MONTANT_CONSOMMATION) as 'Disponible',
                sum(0) as 'PREENGAGE',
                sum(0) as 'ENGAGE',
                sum(0) as 'REALISE',
                sum(0) as 'FACTURE_AVEC_COMMANDE',
                sum(fal.DOC_MONTANT_LIGNE_TTC_FAC) as 'FACTURE_SANS_COMMANDE'
                from FAC_LIGNES_DETAIL fal with (nolock)
                left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=fal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=fal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE=fal.DOC_CODE_SOCIETE
                left outer join BUDGETS_CROISES bud with (nolock) on bud.BUC_CODE_AXE_1=sec.SI_CODE_DIRECTION and bud.BUC_CODE_AXE_2=sca.SCA_CODE_CENTRE_ANALYSE and bud.BUC_CODE_SOCIETE=sec.SI_CODE_SOCIETE and bud.BUC_CODE_SOCIETE=sca.SCA_CODE_SOCIETE
                left outer join PERIODES per with (nolock) on per.PER_DATE=fal.DOC_DATE_PERIODE
                left outer join CA_LIGNES_DETAIL cal with (nolock) on cal.DOC_NUMERO_CDE=fal.DOC_NUMERO_CDE and cal.DOC_NUMERO_LIGNE_CDE=fal.DOC_NUMERO_LIGNE_CDE
                left outer join CODES_GESTION cdg with (nolock) on cdg.CG_CODE_GESTION_AFFICHE=cal.DOC_CODE_GESTION_AFFICHE
                where sec.SI_CODE_DIRECTION<>'' and sca.SCA_CODE_CENTRE_ANALYSE<>'' and bud.BUC_CODE_PERIODE='$periode' and per.PER_CODE_PERIODE='$periode' and fal.DOC_NUMERO_CDE=0 and fal.DOC_AFFECTATION_VALIDE_FAC = 1 $filtreFAc2
                group by
                sca.SCA_DESIGNATION_CENTRE_ANALYSE,
                sec.SI_DESIGNATION,
                cdg.CG_CODE_GESTION_AFFICHE,
                sca.SCA_DESIGNATION,
                fal.DOC_DESIGNATION_FAC,
                fal.DOC_CODE_FOURNISSEUR,
                bud.BUC_MONTANT_BUDGET,
                bud.BUC_MONTANT_CONSOMMATION
            ) result
            /*where Service != NULL or Service <> ''*/
            group by
            result.Nature,
            result.Pôle,
            result.Prestation,
            result.Article,
            result.DOC_CODE_FOURNISSEUR,
            result.Service,
            result.Budget,
            result.Consommé,
            result.Disponible 
        ";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeConsoBDC($post)
    {
        $droit['OX_SUITTUO'] = Roles::droitsUtilisateur($this->pdo, 'OX_SUITTUO', $_SESSION["Session_Login"]);

        $whereSousRequete = '';
        if (Roles::getDroit($this->pdo, 'SOURCE_SI', 'OXAPROC')=='UO' && $droit['OX_SUITTUO'] == '0') {
            $listeSI = Outil_UO_oxadw::listeSiParUO($this->pdoDW, $_SESSION["Session_Login"], $post['filter_DOC_CODE_SOCIETE']??$_SESSION["Session_SocUsr"]);
            
            $whereSousRequete   .= " and sec.SI_CODE_SECTION_IMPUTATION in ('".implode("','", $listeSI)."')";
        }
        
        if (isset($post['SA_CODE_SIGNATAIRE']) && $post['SA_CODE_SIGNATAIRE'] != '') {
            $whereSousRequete .= " and cal.DOC_NUMERO_CDE in (select EVE_NUMERO_DOCUMENT from EVENEMENTS with (nolock) where EVE_TYPE_EVENEMENT='CDE' and EVE_CODE_EVENEMENT='SIG' AND EVE_UTILISATEUR = '".$post['SA_CODE_SIGNATAIRE']."')";
        }
        
        if (isset($post['SA_CODE_SIGNATAIRE_ATTENDU']) && $post['SA_CODE_SIGNATAIRE_ATTENDU'] != '') {
            $whereSousRequete .= " and cal.DOC_NUMERO_CDE in (select DISTINCT(SA_NUMERO_DOCUMENT) from SIGNATURES_ATTENDUES with (nolock) where SA_TYPE_TRANSACTION = 'CA' AND SA_CODE_SIGNATAIRE = '".$post['SA_CODE_SIGNATAIRE_ATTENDU']."')";
        }
        
        
        $req = "SELECT *,
                        round(case when DOC_SOLDE_PHYSIQUE_LIGNE_CDE=0x46 then DOC_MONTANT_LIGNE_CDE-\"Montant HT BR\" else 0 end,9,2) MT_RAL_HT,
                        round(case when DOC_SOLDE_PHYSIQUE_LIGNE_CDE=0x46 then DOC_MONTANT_LIGNE_TTC_CDE-\"Montant TTC BR\" else 0 end,9,2) 'Montant TTC reste à livrer'
                from (
                    select cal.DOC_NUMERO_CDE,
                    cal.DOC_NUMERO_LIGNE_1,
                    cal.DOC_STATUT_CDE,
                    cal.DOC_SOLDE_PHYSIQUE_LIGNE_CDE,
                    /*CONVERT(VARCHAR(10), cal.DOC_DATE_CREATION_CDE, 112) DOC_DATE_CREATION_CDE,*/
                    DOC_DATE_CREATION_CDE,
                    cal.DOC_CODE_ACHETEUR,
                    util.USR_NOM_UTILISATEUR,
                    cal.DOC_DATE_VALIDATION_CDE,
                    /*CONVERT(VARCHAR(10), min(cal.DOC_DATE_LIVRAISON), 112)  DOC_DATE_LIVRAISON,*/
                    min(cal.DOC_DATE_LIVRAISON) DOC_DATE_LIVRAISON,
                    0 as 'Date réception',
                    cal.DOC_DATE_PERIODE,
                    cal.DOC_DATE_SIGNATURE_CDE,
                    cal.DOC_DATE_IMP_CDE,
                    cal.DOC_OBJET_CDE,
                    cal.DOC_DESIGNATION_ARTICLE_CDE,
                    cal.DOC_DESIGNATION_COMP_CDE,
                    cal.DOC_TVA_CDE,
                    sum(cal.DOC_MONTANT_LIGNE_TTC_CDE) DOC_MONTANT_LIGNE_TTC_CDE,
                    sum(cal.DOC_MONTANT_LIGNE_CDE) DOC_MONTANT_LIGNE_CDE,
                    ISNULL((select SUM(brl.DOC_MONTANT_LIGNE_TTC_BR) from BR_LIGNES_DETAIL brl with (nolock) where brl.DOC_NUMERO_CDE=cal.DOC_NUMERO_CDE and brl.DOC_NUMERO_LIGNE_CDE=cal.DOC_NUMERO_LIGNE_1 ),0) as 'Montant TTC BR',
                    ISNULL((select SUM(brl.DOC_MONTANT_LIGNE_BR) from BR_LIGNES_DETAIL brl with (nolock) where brl.DOC_NUMERO_CDE=cal.DOC_NUMERO_CDE and brl.DOC_NUMERO_LIGNE_CDE=cal.DOC_NUMERO_LIGNE_1),0) as 'Montant HT BR',
                    ISNULL((select SUM(fac.DOC_MONTANT_LIGNE_TTC_FAC) from FAC_LIGNES_DETAIL fac with (nolock) where fac.DOC_NUMERO_CDE=cal.DOC_NUMERO_CDE and fac.DOC_NUMERO_LIGNE_CDE=cal.DOC_NUMERO_LIGNE_1),0) as 'Montant TTC FACTURE',
                    ISNULL((select SUM(fac.DOC_MONTANT_LIGNE_FAC) from FAC_LIGNES_DETAIL fac with (nolock) where fac.DOC_NUMERO_CDE=cal.DOC_NUMERO_CDE and fac.DOC_NUMERO_LIGNE_CDE=cal.DOC_NUMERO_LIGNE_1),0) as 'Montant HT FACTURE',
                    cal.DOC_CODE_SECTION_IMPUTATION,
                    sec.SI_DESIGNATION,
                    sec.SI_CODE_SECTION_IMPUTATION,
                    cal.DOC_CODE_GESTION_CDE,
                    cdg.CG_LIBELLE_CODE_GESTION,
                    sec.SI_CODE_DIRECTION,
                    sec.SI_DESIGNATION_DIRECTION,
                    cal.DOC_CODE_SOUS_CPTE_ANALYSE,
                    sca.SCA_DESIGNATION,
                    cc.CC_CS01,
                    cal.DOC_CODE_FOURNISSEUR,
                    frs.FRS_NO_SIRET,
                    frs.FRS_RAISON_SOCIALE,
                    frs.FRS_IDENTIFIANT_EXTERNE,
                    --frs.FRS_CS07,
                    frs.FRS_CS02,
                    --frs.FRS_CS02_L,
                    cal.DOC_CODE_ARTICLE,
                    cal.DOC_REFERENCE_FOURNISSEUR_CDE,
                    art.ART_RECEPTION_AUTOMATIQUE_CDE,
                    art.ART_PERIODICITE_CONTRAT,
                    0 as 'Article Non Receptionable',
                    sca.SCA_CODE_CENTRE_ANALYSE,
                    sca.SCA_DESIGNATION_CENTRE_ANALYSE,
                    cal.DOC_CODE_SOUS_FAMILLE,
                    sf.SF_DESIGNATION,
                    sf.SF_CODE_FAMILLE,
                    fa.FA_DESIGNATION,
                    cal.DOC_QTE_TOT_LIG_CDE,
                    cal.DOC_UNITE_ARTICLE,
                    (cal.DOC_PRIX_UNITAIRE_CDE * (1-cal.DOC_REMISE_CDE/100)) as 'DOC_PRIX_UNITAIRE_CDE',
                    cal.DOC_REMISE_CDE,
                    ((cal.DOC_PRIX_UNITAIRE_CDE*(1+cal.DOC_TVA_CDE/100))*(1-cal.DOC_REMISE_CDE/100)) as 'Prix Unitaire TTC',
                    cal.DOC_CODE_CE_CDE,
                    cal.DOC_CODE_CL_CDE,
                    cal.DOC_NUMERO_DA,
                    cal.DOC_NUMERO_LIGNE_DA,
                    cal.DOC_AVENANT,
                    cal.DOC_CODE_ACCORD_CADRE ,
                    acc.ACC_LIBELLE,
                    acc.ACC_DATE_DEB,
                    acc.ACC_DATE_FIN,
                    fa.FA_CODE_FAMILLE,
                    sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                    cal.DOC_CODE_SOCIETE,
                    cdg.CG_CODE_GESTION_AFFICHE,
                    cc.CC_DESIGNATION,
                    cal.DOC_MOTIF_AVENANT_CDE,
                    ISNULL((select max(DOC_DATE_CREATION_BR) from DOCUMENTS with(nolock) where DOC_TYPE_TRANSACTION = 'BR' AND DOC_NUMERO_CDE = cal.DOC_NUMERO_CDE),'') LAST_BR,
                    isnull( (select sum(brl.DOC_QUANTITE_ACCEPTEE_BR) from BR_LIGNES_DETAIL brl with(nolock) where brl.DOC_NUMERO_CDE = cal.DOC_NUMERO_CDE and  brl.DOC_NUMERO_LIGNE_1 = cal.DOC_NUMERO_LIGNE_1 ) , 0 ) as 'DOC_QTE_RECEP',
                    (select TOP 1 PERIODES.PER_CODE_PERIODE from PERIODES with (nolock) where PERIODES.PER_DATE = cal.DOC_DATE_CREATION_CDE ) PER_CODE_PERIODE
                    from CA_LIGNES_DETAIL cal with (nolock)
                    left outer join SECTIONS_IMPUTATIONS sec with (nolock) on sec.SI_CODE_SECTION_IMPUTATION=cal.DOC_CODE_SECTION_IMPUTATION and sec.SI_CODE_SOCIETE= ''
                    left outer join SOUS_CPTES_ANALYSE sca with (nolock) on sca.SCA_CODE_SOUS_CPTE_ANALYSE=cal.DOC_CODE_SOUS_CPTE_ANALYSE and sca.SCA_CODE_SOCIETE= ''
                    left outer join CODES_GESTION cdg with (nolock) on cdg.CG_CODE_GESTION_AFFICHE=cal.DOC_CODE_GESTION_CDE and cdg.CG_CODE_SECTION_IMPUTATION= cal.DOC_CODE_SECTION_IMPUTATION
                    left outer join FOURNISSEURS frs with (nolock) on frs.FRS_CODE_FOURNISSEUR = DOC_CODE_FOURNISSEUR
                    left outer join UTILISATEURS util with (nolock) on util.USR_CODE_UTILISATEUR = DOC_CODE_ACHETEUR
                    left outer join ARTICLES art with (nolock) on art.ART_CODE_ARTICLE = DOC_CODE_ARTICLE
                    left outer join SOUS_FAMILLES sf with (nolock) on cal.DOC_CODE_SOUS_FAMILLE = SF_CODE_SOUS_FAMILLE
                    left outer join FAMILLES fa with (nolock) on fa.FA_CODE_FAMILLE = SF_CODE_FAMILLE
                    left outer join ACCORD_CADRE acc with (nolock) on acc.ACC_CODE_ACCORD_CADRE = cal.DOC_CODE_ACCORD_CADRE
                    left outer join CENTRES_EMISSION ce with (nolock) on ce.CE_CODE_CENTRE_EMETTEUR = DOC_CODE_CE_CDE
                    left outer join CENTRES_LIVRAISON cL with (nolock) on cL.CL_CODE_CENTRE_LIVRAISON= DOC_CODE_CL_CDE
                    left outer join CODES_COMPLEMTAIRES cc with (nolock) on cc.CC_CODE_COMPLEMENTAIRE = DOC_CODE_COMPLEMENTAIRE and cc.CC_CODE_SOCIETE = ''
                    where 1=1 ".$whereSousRequete."
                    group by cal.DOC_NUMERO_CDE,
                     cal.DOC_NUMERO_LIGNE_1,
                     cal.DOC_STATUT_CDE,
                     cal.DOC_SOLDE_PHYSIQUE_LIGNE_CDE,
                     cal.DOC_DATE_CREATION_CDE,
                     cal.DOC_CODE_ACHETEUR,
                     util.USR_NOM_UTILISATEUR,
                     cal.DOC_DATE_VALIDATION_CDE,
                     cal.DOC_DATE_PERIODE,
                     cal.DOC_DATE_SIGNATURE_CDE,
                     cal.DOC_DATE_IMP_CDE,
                     cal.DOC_OBJET_CDE,
                     cal.DOC_DESIGNATION_ARTICLE_CDE,
                     cal.DOC_DESIGNATION_COMP_CDE,
                     cal.DOC_TVA_CDE,
                     cal.DOC_MONTANT_LIGNE_TTC_CDE,
                     cal.DOC_MONTANT_LIGNE_CDE,
                     cal.DOC_CODE_SECTION_IMPUTATION,
                     sec.SI_DESIGNATION,
                     cal.DOC_CODE_GESTION_CDE,
                     cdg.CG_LIBELLE_CODE_GESTION,
                     sec.SI_CODE_DIRECTION,
                     sec.SI_DESIGNATION_DIRECTION,
                     cal.DOC_CODE_SOUS_CPTE_ANALYSE,
                     sca.SCA_DESIGNATION,
                     cc.CC_CS01,
                     cal.DOC_CODE_FOURNISSEUR,
                     frs.FRS_NO_SIRET,
                     frs.FRS_RAISON_SOCIALE,
                     frs.FRS_IDENTIFIANT_EXTERNE,
                     --frs.FRS_CS07,
                     frs.FRS_CS02,
                     --frs.FRS_CS02_L,
                     cal.DOC_CODE_ARTICLE,
                     cal.DOC_REFERENCE_FOURNISSEUR_CDE,
                     art.ART_RECEPTION_AUTOMATIQUE_CDE,
                     art.ART_PERIODICITE_CONTRAT,
                     sca.SCA_CODE_CENTRE_ANALYSE,
                     sca.SCA_DESIGNATION_CENTRE_ANALYSE,
                     cal.DOC_CODE_SOUS_FAMILLE,
                     sf.SF_DESIGNATION,
                     sf.SF_CODE_FAMILLE,
                     fa.FA_DESIGNATION,
                     cal.DOC_QTE_TOT_LIG_CDE,
                     cal.DOC_UNITE_ARTICLE,
                     cal.DOC_PRIX_UNITAIRE_CDE,
                     cal.DOC_REMISE_CDE,
                     cal.DOC_CODE_CE_CDE,
                     cal.DOC_CODE_CL_CDE,
                     cal.DOC_NUMERO_DA,
                     cal.DOC_NUMERO_LIGNE_DA,
                     cal.DOC_AVENANT,
                     cal.DOC_CODE_ACCORD_CADRE ,
                     acc.ACC_LIBELLE,
                     acc.ACC_DATE_DEB,
                     acc.ACC_DATE_FIN,
                     sca.SCA_CODE_SOUS_CPTE_ANALYSE,
                     fa.FA_CODE_FAMILLE,
                     cal.DOC_CODE_SOCIETE,
                     cdg.CG_CODE_GESTION_AFFICHE,
                     cc.CC_DESIGNATION,
                     sec.SI_CODE_SECTION_IMPUTATION,
                     cal.DOC_MOTIF_AVENANT_CDE
                ) BDC where 1=1";
        
        $arrayReq = [];
        
        if (isset($post['filter_DOC_STATUT_CDE']) && $post['filter_DOC_STATUT_CDE'] == 'A_RECEPTIONNER') {
            $post['filter_DOC_STATUT_CDE'] = 'ENVOYEE';
            $req .= " and LAST_BR = '1900-01-01 00:00:00.000' ";
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1, true);
        $filtreAvance->pushArray($arrayReq);

        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        $req .= " order by DOC_NUMERO_CDE DESC, DOC_NUMERO_LIGNE_1";
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeSignataireCde($commande)
    {
        $req = "SELECT distinct(USR_NOM_UTILISATEUR), EVE_NUMERO_ORDRE
                from EVENEMENTS with (nolock)
                left join UTILISATEURS with (nolock) ON USR_CODE_UTILISATEUR = EVE_UTILISATEUR
                where EVE_TYPE_EVENEMENT='CDE' and EVE_CODE_EVENEMENT='SIG' and EVE_NUMERO_DOCUMENT=:numCde
                order by EVE_NUMERO_ORDRE asc";
        
        $arrayReq = [
            ':numCde' => $commande,
        ];
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeSignataireAttendu($commande)
    {
        $req = "select *  from SIGNATURES_ATTENDUES with(nolock)
                where SA_TYPE_TRANSACTION = 'CA' AND SA_NUMERO_DOCUMENT = :numCde
                order by SA_NUMERO_DOCUMENT, SA_ORDRE";
        
        $arrayReq = [
            ':numCde' => $commande,
        ];
        
        $requete = $this->pdoDW->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    
    public static function convertdateOXADW($dateold)
    {
        if ($dateold=='' || $dateold =='1900-01-01 00:00:00.000') {
            return '';
        }
        $date =  date_create($dateold)->getTimestamp();
        $date = Date::dateConverte(date('Ymd', $date));
        
        return $date;
    }
    
    public function listeStatut()
    {
        $req = "select * FROM STATUTS_CA with (nolock)";

        $requete = $this->pdoDW->prepare($req);
        $requete->execute();
        
        $arrayTmp  = $requete->fetchAll($this->pdo::FETCH_ASSOC);
        
        $listeStatut = [];
        foreach ($arrayTmp as $val) {
            $listeStatut[$val['STA_CODE_STATUT_CA']] = $val['STA_LIBELLE_STATUT_CA']!=''? $val['STA_LIBELLE_STATUT_CA']:$val['STA_CODE_STATUT_CA'];
        }
        
        return $listeStatut;
    }
}
