<?php

namespace Modules\Suivi\Repository;

use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Trad\ITrad;
use Modules\Suivi\MenuContextuel\SuiviFacContextuel;
use Modules\Suivi\Service\SuiviService;
use Skeleton\Router;

class SuiviFacRepo extends SuiviRepo
{

    public SuiviService $serviceSuivi;

    public function __construct(\PDO $pdo, Router $router, ITrad $trad)
    {
        parent::__construct($pdo, $router, $trad);
        $this->serviceSuivi = new SuiviService($this->pdo, $trad);

        $this->droit = Roles::droitsUtilMulti($this->pdo, [
            'HA_DA_TTDM',
            'HA_DA_TTCT',
            'HA_DA_TTCE',
            'OX_SUITTUO',
        ]);
    }

    /**
     * @param array<mixed> $rupture
     * @return string
     */
    private function getMainRequest($rupture = [], string $select = '', bool $count = false)
    {
        if ($count) {
            $select = 'distinct(FAC_ENT.NUM_FACT)';
        } elseif ($select) {
            $select = 'DISTINCT '.$select;
        } elseif ($rupture) {
            $affTTC = Sql::paravrac($this->pdo, 'OXAPROC', 'AFFTTC') == 'O';

            $select = "DISTINCT (FAC_ENT.NUM_FACT), ".($affTTC? "FAC_ENT.TOTAL_TTC": "FAC_ENT.TOTAL_HT")." RUPTURE";
            foreach ($rupture as $k => $r) {
                if ($k != '') {
                    $select .= ",".$k;
                }
            }
        } else {
            $select = "distinct(FAC_ENT.NUM_FACT) NUM_FACT, FAC_ENT.CODE_STE, SOCIETE.RAIS_SOC, FAC_ENT.CODE_FOUR, FOURNISS.RS, 
                    FAC_ENT.FACT_FOU, FAC_ENT.DATE_ENR, FAC_ENT.NUM_CDE, FAC_ENT.TOTAL_HT, FAC_ENT.TOTAL_TTC,FAC_ENT.DEVISE,
                    caeE.ACH, FAC_ENT.LITIGE,

                    /* Menu contextuel */
                    (select COUNT(*) from FAC_REG with (nolock) where FAC_REG.NUM_FAC = FAC_ENT.NUM_FACT) FAC_REG,
                    FAC_ENT.TYPE_URL_LAD, FAC_ENT.FIC_LAD,

                    /* LIB FOR RUPTURE */
                    FOURNISS.RS lib_CODE_FOUR, acheteur.NOM lib_ACH, SOCIETE.RAIS_SOC lib_CODE_STE,
                    (select COUNT(*) from FAC_PIJO with (nolock) where NO_FAC = FAC_ENT.NUM_FACT) NB_PIJO";
        }

        $req = "
            select 
                ".$select."
            from FAC_ENT WITH (nolock)
            left join SOCIETE with (nolock) on SOCIETE.CODE_STE = FAC_ENT.CODE_STE
            left join FOURNISS with (nolock) on FOURNISS.CODE = FAC_ENT.CODE_FOUR
            left join FAC_LIG with (nolock) on FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
            left outer join CA_LIGNE calL with (nolock) on calL.NO_CDE = FAC_LIG.NUM_CDE and calL.NO_PO = FAC_LIG.NUM_LICDE
			left outer join CA_ENTET caeL with (nolock) on caeL.NO_CDE = FAC_LIG.NUM_CDE
            left outer join DA_ENTET daeL with (nolock) on daeL.NO_DA = calL.NO_DA
            left join SECTIMPU with (nolock) on SECTIMPU.CODE_SI = FAC_LIG.CODE_SI and SECTIMPU.CODE_STE =  ".($this->parametrage['sisecto']? "FAC_LIG.CODE_STE":"''")." 
            left join CODECOMP with (nolock) on CODECOMP.CODE_CDC = FAC_LIG.CODE_CDC and CODECOMP.CODE_STE =  ".($this->parametrage['cdcSecto']? "FAC_LIG.CODE_STE":"''")." 
            left join SSCPTANA with (nolock) on SSCPTANA.CODE_SCA = FAC_LIG.CODE_SCA and SSCPTANA.CODE_STE =  ".($this->parametrage['scaSecto']? "FAC_LIG.CODE_STE":"''")." 
            left join SIG_BAP with (nolock) on FAC_ENT.NUM_FACT = SIG_BAP.NO_FAC
            left join FA_EVENT with (nolock) on FA_EVENT.NUM_FACT = SIG_BAP.NO_FAC
            LEFT JOIN FABNOTE fabNote WITH(NOLOCK) ON fabNote.NUM_FACT = FAC_ENT.NUM_FACT
            LEFT JOIN MEMOS memoFabNote WITH(NOLOCK) ON fabNote.NM_BNOTE = memoFabNote.NODEMEMO

			left join CA_ENTET caeE with (nolock) on FAC_ENT.NUM_CDE = caeE.NO_CDE
            left join CA_LIGNE calE with (nolock) on FAC_ENT.NUM_CDE = calE.NO_CDE 
            left join DA_ENTET daeE with (nolock) on calE.NO_DA = daeE.NO_DA
            left join HASTUTIL acheteur with (nolock) on acheteur.CODE = caeE.ACH

            where 1=1 ".Roles::getClauseSocieteOuvert('SOCIETE');

        return $req;
    }

    /**
     * @param array<mixed> $post
     * @param array<mixed> $rupture
     * @return array<mixed>
     */
    public function getListe($post, $rupture = [], string $listeSelect = '')
    {
        $postPagination = $post;

        /** Rupture */
        $this->rupture->cleanPostForRupture($post, $rupture, 'NUM_FACT');
        $req = $this->getMainRequest($rupture, $listeSelect);

        $post['filter_FAC_ENT.NUM_FACT'] = $post['filter_NUM_FACT']??'';
        unset($post['filter_NUM_FACT']);

        $post['filter_FAC_ENT.NUM_CDE'] = $post['filter_NUM_CDE']??'';
        unset($post['filter_NUM_CDE']);

        $post['filter_egal_FAC_ENT.CODE_STE'] = $post['filter_egal_CODE_STE']??'';
        unset($post['filter_egal_CODE_STE']);

        $post['filter_egal_FAC_ENT.LITIGE'] = $post['LITIGE']??'';

        if (!($post['ARCHIVEES']??false)) {
            $post['filter_egal_FAC_ENT.FLG_ARCHIVE'] = 'F';
        }

        $arrayReq = [];

        /** UO */
        $whereUO = '';
        $clauseUO = '';

        /** filtre et/ou périmètre */
        if ($_SESSION['FiltreUOparDefaut'] && (($post['UO']??'') || !$this->droit['OX_SUITTUO'])) {
            if ($this->parametrage['SUIVI_EXCLURE_LIENDOC_UO_HASTUTIL'] != 'O') {
                $whereUO .= " ( 
                                    caeL.UO_ACH in (select CodeUO from LstUO)
                                    or caeL.UO_DEM in (select CodeUO from LstUO)
                                    or daeL.UO_DEM in (select CodeUO from LstUO)

                                    or caeE.UO_ACH in (select CodeUO from LstUO)
                                    or caeE.UO_DEM in (select CodeUO from LstUO)
                                    or daeE.UO_DEM in (select CodeUO from LstUO)
                                    or FAC_ENT.UO_CREAT in (select CodeUO from LstUO) /* TODO ATTENDRE RESTRUCTURATION */
                                )";
            }
            

            if ($this->parametrage['SOURCE_SI'] == 'UO') {
                if ($whereUO) {
                    $whereUO .= " or ";
                }

                $whereUO .= " EXISTS
                (   
                    SELECT NUM_FACT FROM FAC_LIG with (nolock)
                    LEFT OUTER JOIN LSTSIPARUO with (nolock) ON LSTSIPARUO.CODE_SI = FAC_LIG.CODE_SI ".($this->parametrage['sisecto']? " and LSTSIPARUO.CODE_STESI = FAC_LIG.CODE_STE ":" and LSTSIPARUO.CODE_STESI = '' ")."
                    WHERE FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    AND LSTSIPARUO.CODE_UO IN (select CodeUO from LstUO) ".($this->parametrage['sisecto']? " AND CODE_STESI = FAC_LIG.CODE_STE":"")." 
                )";
            }
                
            if ($whereUO) {
                $whereUO = " AND ($whereUO)";

                $clauseUO = $this->getClauseUO($_SESSION["Session_Login"], $post['UO']??'');
            }
        }

        /* */

        /** Périmètre */
        /** Société ? multi / pluri / mono */
        $clausePerimetre = '';
        $clausePerimetre .= Roles::getClausePerimetreSociete('FAC_ENT.CODE_STE', $arrayReq);

        /** acheteur */
        $perimetreAcheteur = '';

        if (!$this->droit['HA_DA_TTDM']) {
            $perimetreAcheteur .= "(
                caeL.ACH = :userAch1 or caeE.DEMANDEUR = :userAch2 or daeL.NO_DEM = :userAch3 or
                caeE.ACH = :userAch5 or caeE.DEMANDEUR = :userAch6 or daeE.NO_DEM = :userAch7
            )";

            $arrayReq[':userAch1'] = $_SESSION["Session_Login"];
            $arrayReq[':userAch2'] = $_SESSION["Session_Login"];
            $arrayReq[':userAch3'] = $_SESSION["Session_Login"];

            $arrayReq[':userAch5'] = $_SESSION["Session_Login"];
            $arrayReq[':userAch6'] = $_SESSION["Session_Login"];
            $arrayReq[':userAch7'] = $_SESSION["Session_Login"];
        }

        /* Clause EVENEMENT ou SIGNATAIRE */
        $perimetreEvent = '';
        if (sql::boolSql($this->user['SIG_BAP']??false)) {
            $perimetreEvent = "
                exists(select null from FA_EVENT with (nolock) WHERE FAC_ENT.NUM_FACT = FA_EVENT.NUM_FACT AND   (FA_EVENT.UTIL = :userEvent1 or FA_EVENT.SIGNAT = :userEvent2)) 
                or
                exists(select null from SIG_BAP with (nolock) where FAC_ENT.NUM_FACT = SIG_BAP.NO_FAC AND   SIG_BAP.SIGNAT  = :userEvent3)
            ";
        }
        
        if ($perimetreAcheteur && $perimetreEvent) {
            $clausePerimetre .= ' and ( '.$perimetreAcheteur.' or '.$perimetreEvent.' )';

            $arrayReq[':userEvent1'] = $_SESSION["Session_Login"];
            $arrayReq[':userEvent2'] = $_SESSION["Session_Login"];
            $arrayReq[':userEvent3'] = $_SESSION["Session_Login"];
        } elseif ($perimetreAcheteur) {
            $clausePerimetre .= 'and '.$perimetreAcheteur;
        }

        /** Filtre */
        $requeteWhere = '';

        if (($post['SELECT_SI_DIR']??'') == 'SI' && ($post['CODE_SI']??'')) {
            $requeteWhere .= " AND (
                FAC_LIG.CODE_SI = :sectimpE 
            ) ";
            $arrayReq[':sectimpE'] = $post['CODE_SI'];

            if ($post['CODE_CODGES']??'') {
                $requeteWhere .= " AND (
                    FAC_LIG.ID_CG = :codeGestE 
                )";
                
                $arrayReq[':codeGestE'] = $post['CODE_CODGES'];
            }
        } elseif (($post['SELECT_SI_DIR']??'') == 'DIR' && ($post['CODE_DIR']??'')) {
            $requeteWhere .= " AND (SECTIMPU.CODE_DIR = :codeDirE)";

            $arrayReq[':codeDirE'] = $post['CODE_DIR'];
        }
        
        if (($post['SELECT_CDC_NA']??'') == 'CDC' && ($post['CODE_CDC']??'')) {
            $requeteWhere .= " AND (FAC_LIG.CODE_CDC = :codeCompE ) ";

            $arrayReq[':codeCompE'] = $post['CODE_CDC'];
        } elseif (($post['SELECT_CDC_NA']??'') == 'NA' && ($post['CODE_NA']??'')) {
            $requeteWhere .= " AND (CODECOMP.CODNAN = :codeNaE)";

            $arrayReq[':codeNaE'] = $post['CODE_NA'];
        }

        if (($post['SELECT_SCA_CA']??'') == 'SCA' && ($post['CODE_SCA']??'')) {
            $requeteWhere .= " AND (FAC_LIG.CODE_SCA = :scaE ) ";

            $arrayReq[':scaE'] = $post['CODE_SCA'];
        } elseif (($post['SELECT_SCA_CA']??'') == 'CA' && ($post['CODE_CA']??'')) {
            $requeteWhere .= " AND (SSCPTANA.CODE_CTA = :codeCaE)";

            $arrayReq[':codeCaE'] = $post['CODE_CA'];
        }

        if ($post['SIG_BAP']??false) {
            $requeteWhere .= " and (SIG_BAP.SIGNAT = :signat1 or FA_EVENT.SIGNAT=:signat2) ";

            $arrayReq[':signat1'] = $_SESSION["Session_Login"];
            $arrayReq[':signat2'] = $_SESSION["Session_Login"];
        }

        if ($post['ACH']??'') {
            $requeteWhere .= "  and (upper(caeE.DEMANDEUR) like :ach1
                                or 
                                upper(caeE.ACH) like :ach2
                                or 
                                upper(daeE.NO_DEM) like :ach3
                                or
                                upper(caeL.DEMANDEUR) like :ach4
                                or 
                                upper(caeL.ACH) like :ach5
                                or 
                                upper(daeL.NO_DEM) like :ach6 )";

            $arrayReq[':ach1'] = '%'.strtoupper($post['ACH']).'%';
            $arrayReq[':ach2'] = '%'.strtoupper($post['ACH']).'%';
            $arrayReq[':ach3'] = '%'.strtoupper($post['ACH']).'%';
            $arrayReq[':ach4'] = '%'.strtoupper($post['ACH']).'%';
            $arrayReq[':ach5'] = '%'.strtoupper($post['ACH']).'%';
            $arrayReq[':ach6'] = '%'.strtoupper($post['ACH']).'%';
        }

        $ModeReglement = $post['sousReglement']??'';
        switch ($ModeReglement) {
            case '':
                break;
            case 'NON': // Aucun RG
                $requeteWhere .= ' and ABS(FAC_ENT.MT_TOTREG) < ABS(FAC_ENT.TOTAL_TTC) AND ABS(FAC_ENT.MT_TOTREG)  = 0 ';
                break;
            case 'NONPART':
                $requeteWhere .= ' and (ABS(FAC_ENT.MT_TOTREG) < ABS(FAC_ENT.TOTAL_TTC) AND ABS(FAC_ENT.MT_TOTREG) <> 0 OR ABS(FAC_ENT.MT_TOTREG) < ABS(FAC_ENT.TOTAL_TTC) AND ABS(FAC_ENT.MT_TOTREG)  = 0 ) ';
                break;
            case 'PART':
                $requeteWhere .= ' and ABS(FAC_ENT.MT_TOTREG) < ABS(FAC_ENT.TOTAL_TTC) AND ABS(FAC_ENT.MT_TOTREG) <> 0 ';
                break;
            case 'TOTAL':
                $requeteWhere .= ' and (FAC_ENT.MT_TOTREG) >= ABS(FAC_ENT.TOTAL_TTC) AND ABS(FAC_ENT.TOTAL_TTC)<> 0 ';
                break;
            default:
                break;
        }

        $Statut = $post['STATUT']??'';
        switch ($Statut) {
            case 'QUELCONQUE':
                break;
            case 'AFF_A_SAIS':
                $requeteWhere .= ' and FAC_ENT.AFF_ENREG=0x46';
                break;
            case 'PRE_AFF':
                $requeteWhere .= ' and FAC_ENT.AFF_TMP=0x54';
                break;
            case 'AFFECT_VALID':
                $requeteWhere .= ' and FAC_ENT.AFF_ENREG=0x54 and FAC_ENT.AFF_VALID=0x46';
                break;
            case 'ATTENTE_BAP':
                if (Sql::paravrac($this->pdo, 'PARAMINIFA', 'SIGN_BAP') == 'O') {
                    $requeteWhere .= ' AND (FAC_ENT.ATT_SIG=0x54 AND FAC_ENT.REFUSEE=0x46 AND FAC_ENT.LITIGE =0x46)';
                } else {
                    $requeteWhere .= ' AND (FAC_ENT.ENT_VALID=0x54 AND FAC_ENT.BON_A_PAY=0x46 AND FAC_ENT.LITIGE =0x46)';
                }
                break;
            case 'FAC_LITIG':
                $requeteWhere .= ' and FAC_ENT.LITIGE=0x54';
                break;
            case 'SIGNEES':
                $requeteWhere .= ' and FAC_ENT.DATE_BAP!=\'00000000\'';
                break;
            case 'REFUSEES':
                $requeteWhere .= ' and FAC_ENT.REFUSEE=0x54';
                break;
            case 'NON_REGL':
                $requeteWhere .= ' and FAC_ENT.MT_TOTREG=0';
                break;
            case 'PART_REGL':
                $requeteWhere .= ' and FAC_ENT.MT_TOTREG!=0 and FAC_ENT.MT_TOTREG < FAC_ENT.TOTAL_TTC';
                break;
            case 'TOT_REGL':
                $requeteWhere .= ' and FAC_ENT.MT_TOTREG >= FAC_ENT.TOTAL_TTC';
                break;
        }

        $finalWhere = $requeteWhere.$whereUO.$clausePerimetre;

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1, 0, $rupture);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $finalWhere .= $filtreAvance->generateSql(1, false, 'FAC_ENT');
        $filtreAvance->pushArray($arrayReq);

        if (count($whereArray)>  0) {
            $finalWhere .= $whereArray[0]. ' ' . $whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        $req .= $finalWhere;

        if (!$rupture) {
            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $postPagination, $arrayReq, $this->pdo, false, $clauseUO);

            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            $req = $this->rupture->genrateRequeteRupture($req, $rupture, $clauseUO);

            return $this->fetchAssocOne($req, $arrayReq);
        }
    }


    /**
     * @return array<mixed>
     */
    public function getInfoEntete(string $numFact)
    {
        $req = "select 
                    (
                        SELECT  
                        CAST(NUM_CDE AS VARCHAR(MAX)) 
                        FROM FAC_CDELAD with(nolock)
                        where FAC_CDELAD.NUM_FACT = FAC_ENT.NUM_FACT FOR XML PATH(''),TYPE
                    ).value('.','VARCHAR(MAX)') CDE_LAD,
                    uo.CODE_UO UO_CREAT,uo.LIB_UO LIB_UO_CREAT, FIC_LAD
                from FAC_ENT with (nolock)
                left join UO uo with (nolock) on uo.CODE_UO = FAC_ENT.UO_CREAT and uo.CODE_STE = ".($this->parametrage['uoSecto']? "FAC_ENT.CODE_STE":"''")."
                where FAC_ENT.NUM_FACT = :numFact";

        $arrayReq = [
            ':numFact' => $numFact
        ];

        return $this->fetchAssocOne($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getLiseLigne(string $numFact)
    {
        $req = "select 
                    FAC_LIG.NUM_LIG, FAC_LIG.NUM_CDE, FAC_LIG.NUM_LICDE, NUM_BR, NUM_LIBR, DESIGNAT, QUANTITE, PU_REMISE, MONTANT, TAUX_TVA, DEVISE
                from FAC_LIG with (nolock)
                LEFT JOIN FAC_ENT with (nolock) on FAC_ENT.NUM_FACT = FAC_LIG.NUM_FACT
                where FAC_LIG.NUM_FACT = :numFact";

        $arrayReq = [
            ':numFact' => $numFact
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getFacLinkDoc(string $typeDoc, string $idDoc)
    {
        $req = $this->getMainRequest();

        $arrayReq = [];

        if ($typeDoc == 'DA') {
            $req .= "
                and FAC_ENT.NUM_FACT in (
                    select distinct(NUM_FACT) from FAC_LIG with(nolock)
                    LEFT join CA_LIGNE with (nolock) on CA_LIGNE.NO_CDE = FAC_LIG.NUM_CDE and  CA_LIGNE.NO_PO = FAC_LIG.NUM_LICDE
                    where CA_LIGNE.NO_DA = :idDoc
                )
            ";

            $arrayReq = [
                ':idDoc' => $idDoc
            ];
        } elseif ($typeDoc == 'CDE') {
            $req .= "
                and FAC_ENT.NUM_FACT in (
                    select distinct(NUM_FACT) from FAC_LIG with(nolock) where NUM_CDE = :idDoc
                )
            ";

            $arrayReq = [
                ':idDoc' => $idDoc
            ];
        } elseif ($typeDoc == 'Recep') {
            $req .= "
                and FAC_ENT.NUM_FACT in (
                    select distinct(NUM_FACT) from FAC_LIG with(nolock) where NUM_BR = :idDoc
                )            
            ";
            $arrayReq = [
                ':idDoc' => $idDoc
            ];
        }
        
        $liste = $this->fetchAssocAll($req, $arrayReq);

        $menuContextuel = new SuiviFacContextuel($this->pdo, $this->router, $this->trad);
        foreach ($liste as &$doc) {
            $ecartLigne = $this->serviceSuivi->getValeursEcartLigneFac($this, $idDoc);
            $doc = array_merge($doc, $ecartLigne);

            if ($typeDoc == 'CDE') {
                $pathRefresh = $this->router->generateUri('Suivi.SuiviCde.ajaxDetails', ['idDoc' => $idDoc, 'source' => 'CDE']);
            } elseif ($typeDoc == 'Recep') {
                $pathRefresh = $this->router->generateUri('Suivi.SuiviRecep.ajaxDetails', ['idDoc' => $idDoc, 'source' => 'Recep']);
            } elseif ($typeDoc == 'DA') {
                $pathRefresh = $this->router->generateUri('Suivi.SuiviDa.ajaxDetails', ['idDoc' => $idDoc, 'source' => 'DA']);
            } else {
                $pathRefresh = '';
            }

            $doc['menuContextuel'] = $menuContextuel->generate($doc, 'sousDocument', $pathRefresh);
        }

        return $liste;
    }

    /**
     * Undocumented function
     *
     * @param string $numFact
     * @return array<mixed>
     */
    public function getInfoFacEcart(string $numFact)
    {
        $req = "SELECT 
                FAC_LIG.NUM_FACT,
                FAC_LIG.NUM_LIG,
                NUM_LICDE, 
                QUANTITE, 
                MONTANT, 
                FAC_LIG.NUM_BR, 
                FAC_LIG.NUM_LIBR, 
                round((SELECT SUM (factLigSousRequete.MONTANT) FROM FAC_LIG factLigSousRequete with(nolock) WHERE factLigSousRequete.NUM_BR = BR_LIGNE.NUM_BR AND factLigSousRequete.NUM_LIBR =FAC_LIG.NUM_LIBR) - BR_LIGNE.MNT_REC, 2) as ECART_MNT_1,  
                round(CA_LIGNE.SOM_FACTU - CA_LIGNE.QTE_COMM * CA_LIGNE.PU*(1-CA_LIGNE.REMISE / 100), 2) as ECART_MNT_2,  
                CA_ENTET.VAL_DEVIS,  
                FAC_ENT.TX_DEVISE,
                case when FAC_ENT.TYPE='F' then 1 else -1 end * FAC_LIG.PU_REMISE as FAC_LIG_PU, 
                CA_LIGNE.PU * (1 - CA_LIGNE.REMISE / 100) * CA_ENTET.VAL_DEVIS / (CASE FAC_ENT.TX_DEVISE WHEN 0 THEN 1 ELSE FAC_ENT.TX_DEVISE END) as ECART_DEVISE, 
                case when BR_LIGNE.QTE_FACT=0 then 0 else BR_LIGNE.QTE_FACT - BR_LIGNE.QTE_ACCEP end as ECART_QTE_FACT_ACC,  
                case when CA_LIGNE.QTE_FACTU=0 then 0 else CA_LIGNE.QTE_FACTU - CA_LIGNE.QTE_COMM end  as ECART_QTE_FACT_COMM,  
                
				FAC_LIG.QUANTITE * 
				
				(
					(case when FAC_ENT.TYPE='F' then 1 else -1 end * FAC_LIG.PU_REMISE) 
					- 
					CA_LIGNE.PU*(1-CA_LIGNE.REMISE / 100) * (
					
						CASE WHEN FAC_ENT.DEVISE != CA_ENTET.DEVISE THEN 
						(CASE CA_ENTET.VAL_DEVIS WHEN 0 THEN 1 ELSE CA_ENTET.VAL_DEVIS END) / (CASE FAC_ENT.TX_DEVISE WHEN 0 THEN 1 ELSE FAC_ENT.TX_DEVISE END)
						else 1 end
					
					)
				
				) as ECART_QTE_1,

                CA_LIGNE.PU*(1-CA_LIGNE.REMISE / 100) * CA_ENTET.VAL_DEVIS / (CASE FAC_ENT.TX_DEVISE WHEN 0 THEN 1 ELSE FAC_ENT.TX_DEVISE END) as ECART_QTE_2,  

                CA_LIGNE.PU * (1.0 - round(CA_LIGNE.REMISE, 2) / 100.0) as CA_LIG_PU, 
                PU_REMISE, 
                ISNULL(CA_LIGNE.PU, 0) * (1 - ISNULL(CA_LIGNE.REMISE, 0) / 100) as PU_CDE, 
                CASE BR_LIGNE.EN_MNT
                    WHEN 0x46 THEN CASE
                            WHEN CA_LIGNE.NO_PO <> 0 AND FAC_LIG.NUM_TERME = 0 THEN CASE
                                    WHEN 
                                        ABS( ROUND(CA_LIGNE.PU * (1.0 - round(CA_LIGNE.REMISE, 2) / 100.0) * CASE WHEN ISNULL(CA_ENTET.VAL_DEVSTE, 0) <> 0 THEN CA_ENTET.VAL_DEVSTE ELSE 1 END, 5))
                                        <>
                                        ABS( ROUND(FAC_LIG.PU_REMISE * CASE WHEN ISNULL(FAC_ENT.TX_DEVSTE, 0) <> 0 THEN FAC_ENT.TX_DEVSTE ELSE 1 END, 5))
                                    THEN 1
                                    ELSE 0
                                    END
                            ELSE 0
                            END
                    ELSE 0
                END as ECART_PU 
                FROM  FAC_LIG WITH(NOLOCK)  
                LEFT JOIN FAC_ENT WITH(NOLOCK) on FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT  
                LEFT JOIN CA_ENTET WITH(NOLOCK) on CA_ENTET.NO_CDE=FAC_LIG.NUM_CDE  
                LEFT JOIN CA_LIGNE WITH(NOLOCK) on CA_LIGNE.CODE_STE=FAC_LIG.CODE_STE AND CA_LIGNE.NO_CDE=FAC_LIG.NUM_CDE AND CA_LIGNE.NO_PO=FAC_LIG.NUM_LICDE  
                LEFT JOIN BR_LIGNE WITH(NOLOCK) ON BR_LIGNE.NUM_BR = FAC_LIG.NUM_BR AND BR_LIGNE.NO_PO_BR = FAC_LIG.NUM_LIG AND BR_LIGNE.NUM_CDE = FAC_LIG.NUM_CDE AND BR_LIGNE.NO_PO_CDE = FAC_LIG.NUM_LICDE  
                WHERE FAC_LIG.NUM_FACT= :numFact
                order by FAC_LIG.NUM_CDE, FAC_LIG.NUM_LICDE, FAC_LIG.NUM_BR, FAC_LIG.NUM_LIBR";

        $arrayReq = [
            ':numFact' => $numFact
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }
}
