<?php

namespace Modules\Suivi\Repository;

use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\Outil\StringHelper;
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
     * @param array<string> $listJoin
     */
    private function getMainRequest(
        $rupture = [],
        string $select = '',
        array $listJoin = []
    ): string {
        if ($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, IIF(FAC_ENT.ACOMPTE = 0x54, 'C', FAC_ENT.TYPE) TYPE, 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, FAC_ENT.LIB_COMPT,

                    /* 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 join CA_LIGNE calL with (nolock) on calL.NO_CDE = FAC_LIG.NUM_CDE and calL.NO_PO = FAC_LIG.NUM_LICDE
			left join CA_ENTET caeL with (nolock) on caeL.NO_CDE = FAC_LIG.NUM_CDE
            left 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

            " . implode("\r\n", $listJoin) . "
            where 1=1 " . Roles::getClauseSocieteOuvert('SOCIETE') . '  '
            . ((isset($_SESSION['NiveauConfidentialite']))
                ? " AND NOT EXISTS ( -- None of its associated Commande item is out of its NivConf
                SELECT 1 
                FROM (
                    SELECT TYPDOC.* FROM CA_ENTET
                    JOIN TYPDOC ON TYPDOC.TYPE = CA_ENTET.TYP_DOC
                    WHERE CA_ENTET.NO_CDE = FAC_ENT.NUM_CDE
                    
                    UNION

                    SELECT TYPDOC.* FROM FAC_LIG
                    JOIN CA_ENTET ON CA_ENTET.NO_CDE = FAC_LIG.NUM_CDE
                    JOIN TYPDOC ON TYPDOC.TYPE = CA_ENTET.TYP_DOC
                    WHERE FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                ) as TYPDOC
                WHERE NIV_CONF > " . intval($_SESSION['NiveauConfidentialite']) . '
            )'
                : '');
        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);

        $arrayReq = [];

        $where = $this->getFilter($post, $arrayReq);

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

        $finalWhere = $whereUO . $where;

        $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 = $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);
        }
    }

    /**
     * @param \PDO $pdo
     * @param array<mixed> $jointure
     * @param array<mixed> $alias
     * @return void
     */
    public static function jointureExport(\PDO $pdo, &$jointure, $alias = [])
    {

        $parametrage = [
            'sisecto' => \Helpers\Outil\Roles::droit($pdo, 'SISECTOR', 'PARAMSTE'),
            'scaSecto' => \Helpers\Outil\Roles::droit($pdo, 'SCASECTO', 'PARAMSTE'),
            'cdcSecto' => \Helpers\Outil\Roles::droit($pdo, 'CDCSECTO', 'PARAMSTE'),
        ];

        $aliasLigne = ($alias['FAC_LIG'] ? $alias['FAC_LIG'] : 'FAC_LIG');

        $jointure[] = "LEFT JOIN SSFAMILL oxaExport_LIGNE_CODE_SFAM with (nolock) on oxaExport_LIGNE_CODE_SFAM.CODE_SFA = $aliasLigne.CODE_SFAM";
        $jointure[] = "LEFT JOIN SOCIETE oxaExport_LIGNE_CODE_STE with (nolock) on oxaExport_LIGNE_CODE_STE.CODE_STE = $aliasLigne.CODE_STE";

        $jointure[] = "LEFT JOIN SECTIMPU oxaExport_LIGNE_CODE_SI with (nolock) on oxaExport_LIGNE_CODE_SI.CODE_SI = $aliasLigne.CODE_SI and oxaExport_LIGNE_CODE_SI.CODE_STE = " . ($parametrage['sisecto'] ? "$aliasLigne.CODE_STE" : "''");
        $jointure[] = "LEFT JOIN CODECOMP oxaExport_LIGNE_CODE_CDC with (nolock) on oxaExport_LIGNE_CODE_CDC.CODE_CDC = $aliasLigne.CODE_CDC and oxaExport_LIGNE_CODE_CDC.CODE_STE = " . ($parametrage['cdcSecto'] ? "$aliasLigne.CODE_STE" : "''");
        $jointure[] = "LEFT JOIN SSCPTANA oxaExport_LIGNE_CODE_SCA with (nolock) on oxaExport_LIGNE_CODE_SCA.CODE_SCA = $aliasLigne.CODE_SCA and oxaExport_LIGNE_CODE_SCA.CODE_STE = " . ($parametrage['scaSecto'] ? "$aliasLigne.CODE_STE" : "''");

        $jointure[] = "  left join CODGEST oxaExport_LIGNE_ID_CG with (nolock)
                        on oxaExport_LIGNE_ID_CG.CODE_STE = " . ($parametrage['sisecto'] ? "oxaExport_LIGNE_CODE_SI.CODE_STE" : "''") . "
                        and oxaExport_LIGNE_ID_CG.CODE_SI = $aliasLigne.CODE_SI
                        and oxaExport_LIGNE_ID_CG.CODE_GES = $aliasLigne.ID_CG";
    }

    /**
     * @param array<mixed> $post
     * @param array<string> $listField
     * @return \PDOStatement<array<string>>
     */
    public function export(array $post, array $listField): \PDOStatement
    {
        $q = $this->processSpecialField($listField);

        // A revoir ?
        $q->select = array_map(function ($item) {
            return match ($item) {
                "FAC_ENT.TYPE" => ['type' => 'sql_expr', 'expr' => "IIF(FAC_ENT.ACOMPTE = 0x54, 'C', FAC_ENT.TYPE)"],
                default => $item
            };
        }, $q->select);

        self::jointureExport($this->pdo, $q->join);


        $req = $this->getMainRequest([], $this->stringifySelect($q->select), $q->join);

        $arrayReq = [];

        $where = $this->getFilter($post, $arrayReq);

        /** UO */
        $whereUO = $this->getFilerUo($post);

        // TODO : $finalWhere .= $filtreAvance->generateSql(1, false, 'FAC_ENT');
        $req =
            (!empty($whereUO) ? $this->getClauseUO($_SESSION["Session_Login"], $post['UO'] ?? '') : '')
            . $req
            . $whereUO . $where;

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

    /**
     * @param array<mixed> $post
     * @param array<mixed> $arrayReq
     */
    private function getFilter(array &$post, array &$arrayReq): string
    {

        $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';
        }

        /* */

        /** Périmètre */
        /** Société ? multi / pluri / mono */
        $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;
        }

        if ($post['filter_TYPE_NEW'] ?? '') {
            $requeteWhere  .= match ($post['filter_TYPE_NEW']) {
                'F' => " AND FAC_ENT.TYPE = 'F' AND FAC_ENT.ACOMPTE = 0x46 ",
                'C' => " AND FAC_ENT.TYPE = 'F' AND FAC_ENT.ACOMPTE = 0x54 ",
                'A' => " AND FAC_ENT.TYPE = 'A' AND FAC_ENT.ACOMPTE = 0x46 ",
                default => ''
            };
            unset($post['filter_TYPE_NEW']);
        }


        return $requeteWhere . $clausePerimetre;
    }

    /**
     * @param array<mixed> $post
     */
    private function getFilerUo(array $post): string
    {
        /** filtre et/ou périmètre */
        if (!roles::droit($this->pdo, 'SUIVI_HIRARCHIQUE_UO', 'OXAPROC') ||
            !(
                ($post['UO'] ?? '')
                || !$this->droit['OX_SUITTUO']
            )
        ) {
            return '';
        }
        $whereUO = '';
        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)";
        }
        return $whereUO;
    }



    /**
     * @return array<mixed>
     */
    public function getInfoEntete(string $numFact)
    {
        $req = "select
                    FAC_ENT.NUM_FACT,
                    (
                        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, DATE_PER, FAC_ENT.NUM_FACT, FAC_ENT.TYPE
                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,desCompl.CONTMEMO DES_COMPL
                from FAC_LIG with (nolock)
                LEFT JOIN FAC_ENT with (nolock) on FAC_ENT.NUM_FACT = FAC_LIG.NUM_FACT
                left join CA_LIGNE calL on calL.NO_CDE = FAC_LIG.NUM_CDE AND calL.NO_PO = FAC_LIG.NUM_LICDE
                LEFT JOIN MEMOS desCompl with (nolock) ON desCompl.NODEMEMO =  calL.NM_DCOMP
                where FAC_LIG.NUM_FACT = :numFact";

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

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

    /**
     * @return array<mixed>
     */
    public function getFacLinkDoc(string $typeDoc, string $idDoc, string $typeFac)
    {
        $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
            ];
        } elseif ($typeDoc == 'Fac') {
            $req .= "
                and FAC_ENT.NUM_FACT in (
                    select distinct(REF_FACT) from FAC_LIG with(nolock) where NUM_FACT = :idDoc AND FAC_ENT.TYPE = 'F'
                )              
            ";
            $arrayReq = [
                ':idDoc' => $idDoc
            ];
        }

        $req .= match ($typeFac) {
            'avoir' => " AND FAC_ENT.TYPE = 'A' AND FAC_ENT.ACOMPTE = 'F' ",
            'acompte' => " AND FAC_ENT.TYPE = 'F' AND FAC_ENT.ACOMPTE = 'T' ",
            // facture
            default => " AND FAC_ENT.TYPE = 'F' AND FAC_ENT.ACOMPTE = 'F' ",
        };

        $liste = $this->fetchAssocAll($req, $arrayReq);

        $menuContextuel = new SuiviFacContextuel($this->pdo, $this->router, $this->trad);
        foreach ($liste as &$doc) {
            $ecartLigne = $this->serviceSuivi->getValeursEcartLigneFac($this, $doc['NUM_FACT']);
            $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);
    }
}
