<?php

namespace Modules\Facture\Repository;

use Entity\Model\FacLigTravail;
use Entity\Model\Paravrac;
use Helpers\Outil\Date;
use Helpers\Outil\Roles;
use Helpers\Pagination\PaginatorGestion;
use Modules\GlobalApplication\Repository\LicenceOxalys;
use PDO;
use Helpers\Filter\FilterTable;
use Helpers\Outil\Sql;
use Helpers\Parametres\Constante;
use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Validator\Validator;
use Helpers\QueryBuilder\AbsRepository;

class CreationFacture extends AbsRepository
{

    public function getFournisseur($value)
    {
        $req = "SELECT COND_PAIE, MODE_PAIE, DEVISE, CODE_PAYS, RS_REGL, ADR_REGL, CPREGL, VILREGL, PAYSREGL, DOM_BK, CODE_BK, CODE_GUIC, NO_CPTE, CLE_RIB, 
            IBANPAYS, IBANCPT, IBANCLE, CODEBIC
        FROM FOURNISS with (nolock)
        WHERE UPPER(CODE) = :value1";

        $value    = mb_strtoupper($value);
        $arrayReq = [
            ':value1' => $value,
        ];

        $facture = $this->pdo->prepare($req);
        $facture->execute($arrayReq);
        return $facture->fetch();
    }

    public function choixCommande($codeFournisseur, $codeSociete, $post)
    {
        $req = <<<SQL
                    select 
                        c.NO_CDE, 
                        c.ETAT_CDE, 
                        c.OBJET_CA, 
                        c.ACH, 
                        c.DEMANDEUR, 
                        c.TOT_CDEHT, 
                        c.DEVISE, 
                        acheteur.NOM ACHNOM, 
                        demandeur.NOM DEMANDEURNOM,
                        br.ligneBr,  
                        fi.incident,
                        tp.NUM_CDE as termPaie
                    from CA_ENTET c with (nolock)
                        left outer join TYPDOC t with (nolock) 
                            on t.TYPE=c.TYP_DOC
                        left outer join HASTUTIL acheteur with (nolock) 
                            on acheteur.CODE=c.ACH
                        left outer join HASTUTIL demandeur with (nolock) 
                            on demandeur.CODE=c.DEMANDEUR
                        OUTER APPLY (
                            SELECT COUNT_BIG(*) AS ligneBr
                            FROM BR_RECEP AS brRecep WITH (NOLOCK)
                            WHERE brRecep.NUM_CDE = c.NO_CDE
                        ) AS br
                        OUTER APPLY (
                            SELECT COUNT_BIG(*) AS incident
                            FROM FOURNINC AS fournic WITH (NOLOCK)
                            WHERE fournic.NO_CDE = c.NO_CDE
                        ) AS fi
                        OUTER APPLY (
                            SELECT TOP 1 NUM_CDE
                            FROM TP_ENT tp WITH (NOLOCK)
                            WHERE tp.NUM_CDE = c.NO_CDE
                                AND tp.TYPEOBJ = 'CA_ENTET'
                                AND tp.TP_FACT = 0x54
                                AND tp.DATELIBFACT <= :date_today
                        ) tp
                    where c.CODE_FOU=:value1 
                        and c.CODE_STE=:value2 
                        and c.ETAT_FIN='' 
                        AND (c.ETAT_CDE IN ('E', 'S') OR tp.NUM_CDE IS NOT NULL)
                        AND (
                            tp.NUM_CDE IS NOT NULL
                            OR NOT EXISTS (
                                SELECT 1
                                FROM TP_ENT tp2 WITH (NOLOCK)
                                WHERE tp2.NUM_CDE = c.NO_CDE
                                    AND tp2.TYPEOBJ = 'CA_ENTET'
                            )
                        )
            SQL;

        $arrayReq = [
            ':value1'     => $codeFournisseur,
            ':value2'     => $codeSociete,
            ':date_today' => date('Ymd'),

        ];

        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $req .= ' and t.NIV_CONF<=' . $_SESSION['NiveauConfidentialite'];
        }

        if (isset($post['NUM_CDE']) && $post['NUM_CDE'] != '') {
            $req                .= ' AND c.NO_CDE = :noCDE';
            $arrayReq[':noCDE']  = $post['NUM_CDE'];
        }

        if (isset($post['dateCreationDu']) && $post['dateCreationDu'] != '') {
            Date::dateToSql($post['dateCreationDu']);
            $req                 .= ' AND c.DTE_CREAT >= :dateDu';
            $arrayReq[':dateDu']  = $post['dateCreationDu'];
        }

        if (isset($post['dateCreationAu']) && $post['dateCreationAu'] != '') {
            Date::dateToSql($post['dateCreationAu']);
            $req                 .= ' AND c.DTE_CREAT <= :dateAu';
            $arrayReq[':dateAu']  = $post['dateCreationAu'];
        }

        if (isset($post['si']) && $post['si'] != '') {
            $req .= " AND c.NO_CDE in (select NO_CDE from CA_LIGNE with (nolock) where NO_CDE=c.NO_CDE and SECTIMP= '" . $post['si'] . "')";

            if (isset($post['cg']) && $post['cg'] != '') {
                $cgParId = \Helpers\Outil\Roles::droit($this->pdo, 'CODE_GESTION_PAR_ID');
                if ($cgParId) {
                    $code = 'ID_CG';
                } else {
                    $code = 'CODGES';
                }
                $req .= " AND c.NO_CDE in (select NO_CDE from CA_LIGNE with (nolock) where NO_CDE=c.NO_CDE and $code= '" . $post['cg'] . "')";
            }
        }

        if (isset($post['cdc']) && $post['cdc'] != '') {
            $req .= " AND c.NO_CDE in (select NO_CDE from CA_LIGNE with (nolock) where NO_CDE=c.NO_CDE and CODE_COMP= '" . $post['cdc'] . "')";
        }

        if (isset($post['sca']) && $post['sca'] != '') {
            $req .= " AND c.NO_CDE in (select NO_CDE from CA_LIGNE with (nolock) where NO_CDE=c.NO_CDE and SSCPTEANA= '" . $post['sca'] . "')";
        }

        if (isset($post['tp']) && $post['tp'] == 'AVEC') {
            $req .= ' and tp.NUM_CDE IS NOT NULL';
        } elseif (isset($post['tp']) && $post['tp'] == 'SANS') {
            $req .= ' and tp.NUM_CDE IS NULL';
        }

        if (isset($post['filter_NO_CDE']) && $post['filter_NO_CDE'] != '') {
            $req                .= ' AND c.NO_CDE = :noCDE';
            $arrayReq[':noCDE']  = $post['filter_NO_CDE'];
        }

        if (isset($post['filter_ETAT_CDE']) && $post['filter_ETAT_CDE'] != '') {
            if ($post['filter_ETAT_CDE'] == 'L') {
                $req .= " AND (c.NO_CDE in (select distinct(brRecep2.NUM_CDE) from BR_RECEP brRecep2 with (nolock)) AND c.ETAT_CDE='E')";
            } elseif ($post['filter_ETAT_CDE'] == 'E') {
                $req .= " AND (c.NO_CDE not in (select distinct(brRecep2.NUM_CDE) from BR_RECEP brRecep2 with (nolock)) AND c.ETAT_CDE='E')";
            } else {
                $req               .= ' AND c.ETAT_CDE = :etat';
                $arrayReq[':etat']  = $post['filter_ETAT_CDE'];
            }
        }

        if (isset($post['filter_OBJET_CA']) && $post['filter_OBJET_CA'] != '') {
            $req               .= ' AND upper(c.OBJET_CA) like :etat';
            $arrayReq[':etat']  = strtoupper('%' . $post['filter_OBJET_CA'] . '%');
        }

        if (isset($post['filter_ACH']) && $post['filter_ACH'] != '') {
            $req              .= ' AND c.ACH = :ach';
            $arrayReq[':ach']  = $post['filter_ACH'];
        }

        if (isset($post['filter_DEMANDEUR']) && $post['filter_DEMANDEUR'] != '') {
            $req                    .= ' AND c.DEMANDEUR = :demandeur';
            $arrayReq[':demandeur']  = $post['filter_DEMANDEUR'];
        }

        if (isset($post['filter_TOT_CDEHT']) && $post['filter_TOT_CDEHT'] != '') {
            $req                   .= ' AND c.TOT_CDEHT = :totCDEHT';
            $arrayReq[':totCDEHT']  = $post['filter_TOT_CDEHT'];
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post);
        if ($whereArray['tri']) {
            $req .= $whereArray['tri'];
        } else {
            $req .= ' ORDER BY c.NO_CDE ASC';
        }

        (new PaginatorGestion())->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    public function choixCommandesNonFacturables($post)
    {
        $req = "SELECT
                        c.NO_CDE, 
                        c.AVENANT, 
                        c.ETAT_CDE, 
                        c.OBJET_CA,
                        c.ACH, 
                        c.SIGNEE, 
                        c.REFUSEE, 
                        Signature.SIGNAT,

                        Signataire.NOM as Signataire_NOM,
                        Signataire.EMAIL as Signataire_EMAIL,
                        Signataire.TEL as Signataire_TEL,

                        Acheteur.NOM as Acheteur_NOM,
                        Acheteur.EMAIL as Acheteur_EMAIL,
                        Acheteur.TEL as Acheteur_TEL
                    FROM CA_ENTET c with (nolock)
                    LEFT JOIN TYPDOC t with (nolock) ON t.TYPE=c.TYP_DOC
                    LEFT JOIN HASTUTIL Acheteur with (nolock) ON Acheteur.CODE = c.ACH

                    LEFT JOIN (
                        /* Get first row of the group */
                        SELECT SIG_CDE.NO_CDE, MIN(SIG_CDE.SIGNAT) as SIGNAT
                        FROM SIG_CDE with (nolock) 
                        WHERE SIG_CDE.BON_SIGNE=" . Sql::TRUE . " 
                            AND SIG_CDE.SIGNABLE=" . Sql::TRUE . "
                            AND SIG_CDE.INFORME=" . Sql::FALSE . "
                        GROUP BY SIG_CDE.NO_CDE
                    ) as Signature ON Signature.NO_CDE = c.NO_CDE

                    LEFT JOIN HASTUTIL Signataire with (nolock) ON Signataire.CODE = Signature.SIGNAT

                    OUTER APPLY (
                        SELECT TOP 1 NUM_CDE
                        FROM TP_ENT tp WITH (NOLOCK)
                        WHERE tp.NUM_CDE = c.NO_CDE
                            AND tp.TYPEOBJ = 'CA_ENTET'
                            AND 
                            (
                                tp.DATELIBFACT > :date_today
                                OR  
                                tp.TP_FACT = 0x46
                            )
                    ) tp
                    WHERE c.CODE_FOU=:Fourniss
                        AND c.CODE_STE=:Societe 
                        AND c.ETAT_FIN='' 
                        AND (c.ETAT_CDE IN ('A', 'V') OR tp.NUM_CDE IS NOT NULL)
            ";

        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $req .= ' AND t.NIV_CONF<=' . $_SESSION['NiveauConfidentialite'];
        }

        if (isset($post['tp']) && $post['tp'] == 'AVEC') {
            $req .= ' and tp.NUM_CDE IS NOT NULL';
        } elseif (isset($post['tp']) && $post['tp'] == 'SANS') {
            $req .= ' and tp.NUM_CDE IS NULL';
        } else {
            $req .= " AND (
                            tp.NUM_CDE IS NOT NULL
                            OR 
                            NOT EXISTS (
                                SELECT 1
                                FROM TP_ENT tp2 WITH (NOLOCK)
                                WHERE tp2.NUM_CDE = c.NO_CDE
                                    AND tp2.TYPEOBJ = 'CA_ENTET'
                            )
                        )";
        }

        return $this->fetchAssocAll($req, [
            ':Fourniss'   => $post['CODE_FOUR'],
            ':Societe'    => $post['CODE_STE'],
            ':date_today' => date('Ymd'),
        ]);
    }

    public function choixCommandeAvoir($codeFournisseur, $codeSociete, $post)
    {
        $reqImbrique = '';

        if (isset($post['datePeriodeDu']) && $post['datePeriodeDu'] != '') {
            Date::dateToSql($post['datePeriodeDu']);
            $reqImbrique .= ' AND f.DATE_PER >= ' . $post['datePeriodeDu'];
        }

        if (isset($post['datePeriodeAu']) && $post['datePeriodeAu'] != '') {
            Date::dateToSql($post['datePeriodeAu']);
            $reqImbrique .= ' AND f.DATE_PER <= ' . $post['datePeriodeAu'];
        }

        if (isset($post['si']) && $post['si'] != '') {
            $reqImbrique .= ' AND l.CODE_SI = \'' . $post['si'] . '\'';
        }

        if (isset($post['cg']) && $post['cg'] != '') {
            $reqImbrique .= ' AND l.ID_CG = \'' . $post['cg'] . '\'';
        }

        if (isset($post['cdc']) && $post['cdc'] != '') {
            $reqImbrique .= ' AND l.CODE_CDC = \'' . $post['cdc'] . '\'';
        }

        if (isset($post['sca']) && $post['sca'] != '') {
            $reqImbrique .= ' AND l.CODE_SCA = \'' . $post['sca'] . '\'';
        }

        // TOOD : prepare param
        $req = "SELECT c.NO_CDE, c.ETAT_CDE, c.OBJET_CA, c.ACH, c.DEMANDEUR, c.TOT_CDEHT, c.DEVISE, acheteur.NOM ACHNOM, demandeur.NOM DEMANDEURNOM,
            (SELECT TOP 1 count(*) FROM BR_RECEP brRecep with (nolock) WHERE brRecep.NUM_CDE=c.NO_CDE) ligneBr  
            FROM CA_ENTET c with (nolock)
            LEFT JOIN TYPDOC t with (nolock) ON t.TYPE=c.TYP_DOC
			LEFT JOIN HASTUTIL acheteur with (nolock) on acheteur.CODE=c.ACH
			LEFT JOIN HASTUTIL demandeur with (nolock) on demandeur.CODE=c.DEMANDEUR
            WHERE c.NO_CDE in 
                (SELECT distinct(l.NUM_CDE) FROM FAC_LIG l with (nolock)
                JOIN FAC_ENT f with (nolock) ON f.NUM_FACT=l.NUM_FACT
                LEFT JOIN CA_LIGNE cal with (nolock) ON cal.NO_CDE=l.NUM_CDE AND cal.NO_PO=l.NUM_LICDE
                WHERE l.CODE_STE='$codeSociete' AND f.CODE_FOUR='$codeFournisseur' AND f.TYPE='F' 
                AND l.NUM_CDE<>0 AND cal.QTE_FACTU>0$reqImbrique)";

        $arrayReq = [];

        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $req .= ' AND t.NIV_CONF<=' . $_SESSION['NiveauConfidentialite'];
        }

        if (isset($post['NUM_CDE']) && $post['NUM_CDE'] != '') {
            $req                 .= ' AND c.NO_CDE = :numCde';
            $arrayReq[':numCde']  = $post['NUM_CDE'];
        }

        if (isset($post['filter_ETAT_CDE']) && $post['filter_ETAT_CDE'] != '') {
            switch ($post['filter_ETAT_CDE']) {
                case 'L':
                    $req .= " AND c.ETAT_CDE = 'E' AND exists(SELECT TOP 1 * FROM BR_RECEP brRec with (nolock) WHERE brRec.NUM_CDE=c.NO_CDE)";
                    break;
                case 'E':
                    $req .= " AND c.ETAT_CDE = 'E' AND not exists(SELECT TOP 1 * FROM BR_RECEP brRec with (nolock) WHERE brRec.NUM_CDE=c.NO_CDE)";
                    break;
                default:
                    $req .= ' AND c.ETAT_CDE = :etatCde';
                    $arrayReq[':etatCde'] = $post['filter_ETAT_CDE'];
                    break;
            }

            $post['filter_ETAT_CDE'] = '';
        }

        $splitFilter  = FilterTable::splitFilter($post, 0, 0, 1);
        $req         .=
            ($splitFilter[0 /* Clause */] ?? '')
            . ($splitFilter['tri'] ?? '');
        $arrayReq    += ($splitFilter[1 /* Param */] ?? []);

        (new PaginatorGestion())->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    public function choixAffectationLad($numFact)
    {
        $req = "SELECT NUM_CDE FROM FAC_CDELAD with (nolock) WHERE NUM_FACT=:value1";

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

        $facture = $this->pdo->prepare($req);
        $facture->execute($arrayReq);
        return $facture->fetchAll(PDO::FETCH_ASSOC);
    }

    public function choixFacture($codeFournisseur, $codeSociete, $post)
    {
        $reqImbrique        = '';
        $reqConfidantialite = '';

        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $reqConfidantialite .= ' AND t.NIV_CONF<=' . $_SESSION['NiveauConfidentialite'];
        }

        if (isset($post['NUM_CDE']) && $post['NUM_CDE'] != '') {
            $reqImbrique .= ' AND l.NUM_CDE = \'' . $post['NUM_CDE'] . '\'';
        }

        if (isset($post['datePeriodeDu']) && $post['datePeriodeDu'] != '') {
            Date::dateToSql($post['datePeriodeDu']);
            $reqImbrique .= ' AND facent.DATE_PER >= ' . $post['datePeriodeDu'];
        }

        if (isset($post['datePeriodeAu']) && $post['datePeriodeAu'] != '') {
            Date::dateToSql($post['datePeriodeAu']);
            $reqImbrique .= ' AND facent.DATE_PER <= ' . $post['datePeriodeAu'];
        }

        if (isset($post['si']) && $post['si'] != '') {
            $reqImbrique .= ' AND l.CODE_SI = \'' . $post['si'] . '\'';
        }

        if (isset($post['cg']) && $post['cg'] != '') {
            $reqImbrique .= ' AND l.ID_CG = \'' . $post['cg'] . '\'';
        }

        if (isset($post['cdc']) && $post['cdc'] != '') {
            $reqImbrique .= ' AND l.CODE_CDC = \'' . $post['cdc'] . '\'';
        }

        if (isset($post['sca']) && $post['sca'] != '') {
            $reqImbrique .= ' AND l.CODE_SCA = \'' . $post['sca'] . '\'';
        }

        // TODO : prepare facent.CODE_FOUR='$codeFournisseur'
        $req = "SELECT f.NUM_FACT, f.FACT_FOU, f.DATE_ENR, f.DATE_FAFO, f.TOTAL_HT, f.TOTAL_TTC, f.DEVISE
            from FAC_ENT f with (nolock)
            WHERE 1=1 AND f.CODE_FOUR=:value1 AND f.CODE_STE=:value2 AND f.TYPE='F'
            AND f.NUM_FACT in (SELECT distinct(lig.NUM_FACT) FROM FAC_LIG lig with (nolock) where lig.NUM_FACT=f.NUM_FACT)
            AND f.NUM_FACT in (
                SELECT distinct(l.NUM_FACT) 
                FROM FAC_LIG l with (nolock)
                INNER JOIN FAC_ENT facent with (nolock) ON facent.NUM_FACT=l.NUM_FACT
                LEFT JOIN CA_LIGNE cal with (nolock) ON cal.NO_CDE=l.NUM_CDE AND cal.NO_PO=l.NUM_LICDE
                LEFT JOIN CA_ENTET ca with (nolock) ON ca.NO_CDE=cal.NO_CDE
                LEFT JOIN TYPDOC t with (nolock) ON t.TYPE=ca.TYP_DOC
                WHERE facent.CODE_FOUR='$codeFournisseur' AND l.CODE_STE='$codeSociete' 
                    AND facent.TYPE='F' 
                    AND ((l.NUM_CDE=0 or (l.NUM_CDE<>0 and l.NUM_LICDE = 0)) $reqConfidantialite)
                    $reqImbrique
            )";

        $arrayReq = [
            ':value1' => $codeFournisseur,
            ':value2' => $codeSociete,
        ];

        $splitFilter  = FilterTable::splitFilter($post, 0, 0, 1);
        $req         .=
            ($splitFilter[0 /* Clause */] ?? '')
            . ($splitFilter['tri'] ?? '');
        $arrayReq    += ($splitFilter[1 /* Param */] ?? []);

        (new PaginatorGestion())->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    public function numLot()
    {
        $req = "SELECT MAX(NUM_LOT) NUM_LOT FROM LOTGEST with (nolock) WHERE DAT_EFF<='" . Date('Ymd') . "'";

        $facture = $this->pdo->prepare($req);
        $facture->execute();
        return $facture->fetch();
    }

    public function ligneCommande($post)
    {
        $req = "SELECT CA_LIGNE.NO_CDE, CA_LIGNE.NO_PO, CA_LIGNE.DES_SOMM, CA_LIGNE.SSFAM, 
            CA_LIGNE.PU, CA_LIGNE.TVA, CA_LIGNE.UNITE, CA_LIGNE.SECTIMP, CA_LIGNE.CODE_COMP, 
            CA_LIGNE.SSCPTEANA, CA_LIGNE.CODGES, CA_LIGNE.QTE_COMM, CA_LIGNE.QTE_FACTU, 
            CA_LIGNE.CTRL_BR, MEMOS.CONTMEMO as DETAIL_DESIGNAT
            FROM CA_LIGNE with (nolock)
            LEFT OUTER JOIN MEMOS with (nolock) ON CA_LIGNE.NM_DCOMP=MEMOS.NODEMEMO";

        $arrayReq = [];


        $listeCde = '';
        $i        = 0;
        foreach ($post as $key => $value) {
            $tblKey = explode('_', $key);
            if ($tblKey[0] == 'choix') {
                $listeCde .= ', :choix' . $i;

                $arrayReq[':choix' . $i++] = $value;
            }
        }

        $req .= ' WHERE CA_LIGNE.NO_CDE in (' . substr($listeCde, 2) . ')';

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

    public function paramsFactFouObligatoir()
    {
        $req = " SELECT LIB01PARV FROM PARAVRAC with (nolock) WHERE TYPEPARV=:value1 AND CODEPARV=:value2";

        $arrayReq = [
            ':value1' => 'PARAMGEN',
            ':value2' => 'REFFACFOU',

        ];
        $param    = $this->pdo->prepare($req);
        $param->execute($arrayReq);

        if (\str_replace(' ', '', $param->fetch()['LIB01PARV']) == 'O') {
            return true;
        } else {
            return false;
        }
    }

    public function getModeNumFact($societe): ?string
    {
        return (new \Entity\Model\Paramnum($this->pdo))->getNodeNum(
            'FA',
            ((new Paravrac($this->pdo))->getFlag('PARAMGEN', 'SECTNUMFAC')
                ? $societe
                : null),
        );
    }

    public function dateFafou($value)
    {
        $req      = "SELECT PER_ENCOU FROM PARAMETR with (nolock) WHERE CLE = '1'";
        $arrayReq = [];
        $date     = $this->pdo->prepare($req);

        $date->execute($arrayReq);
        if (trim($date->fetch()['PER_ENCOU']) == substr($value, 0, 4)) {
            $result = $this->ouverturePeriode($value);
            if (isset($result['CODE_PER'])) {
                return true;
            } else {
                return false;
            }
        }
        return true;
    }

    public function ouverturePeriode($value)
    {
        $req = "SELECT CODE_PER FROM PERIODES with (nolock) WHERE DATE_DEB <= :value1 AND DATE_FIN >= :value2";

        $arrayReq = [
            ':value1' => $value,
            ':value2' => $value,
        ];

        $date2 = $this->pdo->prepare($req);
        $date2->execute($arrayReq);
        return $date2->fetch();
    }

    public function ouvertureSousPeriode($value, $ste, $verifouverture = 0)
    {
        $req = "SELECT SSPERIOD.*, SSP_STE_CLOSING.PREV_CLOSING_DATE PREV_CLOSING_DATE_STE 
        FROM SSPERIOD with (nolock)
        LEFT JOIN SSP_STE_CLOSING with (nolock) on SSPERIOD.CODE_PER=SSP_STE_CLOSING.CODE_PER AND SSPERIOD.DATE_DEB=SSP_STE_CLOSING.DATE_DEB AND SSP_STE_CLOSING.CODE_STE = :ste
        WHERE SSPERIOD.DATE_DEB <= :value1 AND SSPERIOD.DATE_FIN >= :value2";

        $arrayReq = [
            ':ste'    => $ste,
            ':value1' => $value,
            ':value2' => $value,
        ];

        if ($verifouverture) {
            $dateVerif = Date('Ymd');

            $req                  .= " AND (SSPERIOD.ETAT = 'O' OR (
                SSPERIOD.ETAT = '' 
                AND SSPERIOD.DATE_DEB <= :dateDeb 
                AND (SSPERIOD.DATE_FIN >= :dateFin or SSPERIOD.PREV_CLOSING_DATE >= :datePrevGlobal or isnull(SSP_STE_CLOSING.PREV_CLOSING_DATE,'00000000') >= :datePrevSte))
            )";
            $arrayReq[':dateDeb']  = $arrayReq[':dateFin'] = $arrayReq[':datePrevGlobal'] = $arrayReq[':datePrevSte'] = $dateVerif;
        }

        $date2 = $this->pdo->prepare($req);
        $date2->execute($arrayReq);
        return $date2->fetch();
    }

    // phpcs:ignore
    public function DatepremiereSousPeriode($date, $ste)
    {
        $req = "SELECT TOP 1 SSPERIOD.DATE_DEB 
            FROM SSPERIOD with (nolock)
            LEFT JOIN SSP_STE_CLOSING with (nolock) on SSPERIOD.CODE_PER=SSP_STE_CLOSING.CODE_PER AND SSPERIOD.DATE_DEB=SSP_STE_CLOSING.DATE_DEB AND SSP_STE_CLOSING.CODE_STE = :ste
            WHERE SSPERIOD.DATE_DEB > :value 
            AND (
                SSPERIOD.ETAT = 'O' OR 
                (SSPERIOD.ETAT = '' AND SSPERIOD.DATE_DEB <= :dateDeb AND (SSPERIOD.DATE_FIN >= :dateFin OR SSPERIOD.PREV_CLOSING_DATE >= :datePrevGlobal OR SSP_STE_CLOSING.PREV_CLOSING_DATE >= :datePrevSte))
            ) 
            ORDER BY SSPERIOD.DATE_DEB asc";

        $dateJour = Date('Ymd');
        $arrayReq = [
            ':ste'            => $ste,
            ':value'          => $date,
            ':dateDeb'        => $dateJour,
            ':dateFin'        => $dateJour,
            ':datePrevGlobal' => $dateJour,
            ':datePrevSte'    => $dateJour,
        ];

        $sousPerdiode = $this->pdo->prepare($req);
        $sousPerdiode->execute($arrayReq);
        return $sousPerdiode->fetch();
    }

    // phpcs:ignore
    public function DateDernièreSousPeriode($date, $ste)
    {
        $req = "SELECT TOP 1 SSPERIOD.DATE_FIN 
            FROM SSPERIOD with (nolock)
            LEFT JOIN SSP_STE_CLOSING with (nolock) on SSPERIOD.CODE_PER=SSP_STE_CLOSING.CODE_PER AND SSPERIOD.DATE_DEB=SSP_STE_CLOSING.DATE_DEB AND SSP_STE_CLOSING.CODE_STE = :ste
            WHERE SSPERIOD.DATE_FIN < :value 
            AND (
                SSPERIOD.ETAT = 'O' OR 
                (SSPERIOD.ETAT = '' AND SSPERIOD.DATE_DEB <= :dateDeb AND (SSPERIOD.DATE_FIN >= :dateFin OR SSPERIOD.PREV_CLOSING_DATE >= :datePrevGlobal OR SSP_STE_CLOSING.PREV_CLOSING_DATE >= :datePrevSte))
            ) 
            ORDER BY SSPERIOD.DATE_DEB desc";

        $dateJour = Date('Ymd');
        $arrayReq = [
            ':ste'            => $ste,
            ':value'          => $date,
            ':dateDeb'        => $dateJour,
            ':dateFin'        => $dateJour,
            ':datePrevGlobal' => $dateJour,
            ':datePrevSte'    => $dateJour,
        ];

        $sousPerdiode = $this->pdo->prepare($req);
        $sousPerdiode->execute($arrayReq);
        return $sousPerdiode->fetch();
    }

    public static function ordonanceActif($pdo)
    {
        $req = "SELECT ORDO FROM PARAMETR with (nolock) WHERE CLE=:value";

        $arrayReq = [
            ':value' => '1',
        ];

        $ordonance = $pdo->prepare($req);
        $ordonance->execute($arrayReq);

        if (str_replace(' ', '', $ordonance->fetch()['ORDO']) != '') {
            return $ordonance->fetch()['ORDO'];
        } else {
            return false;
        }
    }

    public function getLignCDE($numCDE)
    {
        $req = " SELECT CA_LIGNE.NO_PO, CA_LIGNE.DES_SOMM, CA_LIGNE.QTE_COMM, CA_LIGNE.PU, CA_LIGNE.TVA, CA_LIGNE.UNITE, MEMOS.CONTMEMO
            FROM CA_LIGNE with (nolock) 
            LEFT JOIN MEMOS with (nolock) ON CA_LIGNE.NM_DCOMP = MEMOS.NODEMEMO 
            WHERE CA_LIGNE.NO_CDE =:value1";

        $arrayReq = [
            ':value1' => $numCDE,
        ];

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

    /**
     * Summary of fournissHasTPOnCDE
     * @param string|int $cde
     * @return string[][]
     */
    public function fournissHasTPOnCDE(string|int $cde)
    {
        $req = " SELECT TP_ENT.LIBELLE, TP_ENT.DESCRIPTIF, TP_ENT.MNT_BASE, TP_ENT.POURC_TP, TP_ENT.DELAIFACT, TP_ENT.TYPEDELAIFACT, TP_POS.*
                FROM TP_POS with (nolock) 
                OUTER APPLY (
                    SELECT TOP 1 *
                    FROM TP_ENT
                    WHERE TP_ENT.NUM_CDE = TP_POS.NUM_CDE
                    AND TP_ENT.NUM_TP = TP_POS.NUM_TP
                ) AS TP_ENT
                WHERE TP_POS.NUM_CDE = :cde 
                ORDER BY TP_ENT.POURC_TP DESC, TP_ENT.MNT_BASE DESC, TP_POS.NUM_TP,  TP_POS.NUM_PO_CD                
        ";

        $arrayReq = [
            ':cde' => intval($cde),
        ];

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

    public function getCondEtModePaiementFacture($numFact)
    {
        $req = "SELECT FAC_ENT.MODE_REG, FAC_ENT.DTAE_ECH, FAC_ENT.DATE_FAFO, FAC_ENT.DATE_ENR, FAC_ENT.TYPE, FOURNISS.COND_PAIE, 
        ISNULL(TRAD_CDP.LIBELLE, CONDPAIE.LIBELLE) AS libelleCDP, ISNULL(t.LIBELLE, MODEPAIE.LIBELLE) AS libelleMDP
        FROM FAC_ENT with (nolock) 
            LEFT JOIN FOURNISS with (nolock) on FOURNISS.CODE = FAC_ENT.CODE_FOUR
            LEFT JOIN CONDPAIE with (nolock) on CONDPAIE.CODE_CDP = FOURNISS.COND_PAIE
            LEFT JOIN MODEPAIE with (nolock) on MODEPAIE.CODE_MDP = FAC_ENT.MODE_REG
            LEFT OUTER JOIN TRAD_MDP t WITH(NOLOCK) ON t.CODE_MDP = MODEPAIE.CODE_MDP AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
            LEFT OUTER JOIN TRAD_CDP WITH(NOLOCK) ON TRAD_CDP.CODE_CDP = CONDPAIE.CODE_CDP AND TRAD_CDP.COD_LNG = '{$_SESSION['Session_Langue']}'
        WHERE NUM_FACT = :numFact";

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

        $facture = $this->pdo->prepare($req);
        $facture->execute($arrayReq);
        return $facture->fetch(PDO::FETCH_ASSOC);
    }

    public function getCondEtModePaiementCommande($numFact)
    {
        $req = "SELECT CA_ENTET.COND_PAIE, CA_ENTET.MODE_PAIE, ISNULL(TRAD_CDP.LIBELLE, CONDPAIE.LIBELLE) AS libelleCDP, ISNULL(t.LIBELLE, MODEPAIE.LIBELLE) AS libelleMDP, CONDPAIE.DELAI_PAI, CONDPAIE.FIN_D_MOI, CONDPAIE.QUANT_PAI
        FROM CA_ENTET with (nolock) 
            INNER JOIN CONDPAIE with (nolock) on CONDPAIE.CODE_CDP = CA_ENTET.COND_PAIE
            LEFT JOIN MODEPAIE with (nolock) on MODEPAIE.CODE_MDP = CA_ENTET.MODE_PAIE
            LEFT OUTER JOIN TRAD_MDP t WITH(NOLOCK) ON t.CODE_MDP = MODEPAIE.CODE_MDP AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
            LEFT OUTER JOIN TRAD_CDP WITH(NOLOCK) ON TRAD_CDP.CODE_CDP = CONDPAIE.CODE_CDP AND TRAD_CDP.COD_LNG = '{$_SESSION['Session_Langue']}'
        WHERE NO_CDE in (select NUM_CDE from FAC_LIG with(nolock) where NUM_FACT = $numFact)";

        $arrayReq = [];

        $facture = $this->pdo->prepare($req);
        $facture->execute($arrayReq);
        return $facture->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getSiSousFamille($params)
    {
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            $codeSte = $params['societe'];
        } else {
            $codeSte = '';
        }

        $req =
            "SELECT case when isnull (sisf.SI,'')='' then f.SI else sisf.SI end as SI
        FROM SSFAMILL sf with (nolock) 
        LEFT JOIN SISTESFA sisf with (nolock) on sisf.SFAMILL=sf.CODE_SFA and sisf.CODE_STE='" . $codeSte . "'
        LEFT JOIN SISTEFAM f with (nolock) on f.FAMILLE=sf.CODE_FAM and f.CODE_STE='" . $codeSte . "'
        WHERE SFAMILL = :sousFamille";

        $arrayReq = [
            ':sousFamille' => $params['CODE_SFAM'],
        ];

        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        return $param->fetch()['SI'] ?? '';
    }

    public function getCdcSousFamille($params)
    {
        if (\Helpers\Outil\Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE')) {
            $codeSte = $params['societe'];
        } else {
            $codeSte = '';
        }

        $req =
            "SELECT case when isnull (cdcsf.CDC,'')='' then f.CDC else cdcsf.CDC end as CDC
        FROM SSFAMILL sf with (nolock)
        LEFT JOIN CDCSTESF cdcsf with (nolock) on cdcsf.SFAMILL=sf.CODE_SFA and cdcsf.CODE_STE='" . $codeSte . "'
        LEFT JOIN CDCSTEFA f with (nolock) on f.FAMILLE=sf.CODE_FAM and f.CODE_STE='" . $codeSte . " '      
        WHERE SFAMILL = :sousFamille";

        $arrayReq = [
            ':sousFamille' => $params['CODE_SFAM'],
        ];

        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        return $param->fetch()['CDC'] ?? '';
    }

    public function getScaSousFamille($params)
    {
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            $codeSte = $params['societe'];
        } else {
            $codeSte = '';
        }

        $req =
            "SELECT case when isnull (scasf.SCA,'')='' then f.SCA else scasf.SCA end as SCA
        FROM SSFAMILL sf with (nolock)
        LEFT JOIN SCASTESF scasf with (nolock) on scasf.SFAMILL=sf.CODE_SFA and scasf.CODE_STE='" . $codeSte . "'
        LEFT JOIN SCASTEFA f with (nolock) on f.FAMILLE=sf.CODE_FAM and f.CODE_STE='" . $codeSte . " '        
        WHERE SFAMILL = :sousFamille";

        $arrayReq = [
            ':sousFamille' => $params['CODE_SFAM'],
        ];

        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        return $param->fetch()['SCA'] ?? '';
    }

    public function listeLigneFactureSelectionne($numFact)
    {
        $req = "SELECT * from FAC_LIG_TRAVAIL with (nolock) where NUM_FACT = :numFact and SELECTIONNEE = 0x54";

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

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

    public function sumLigTravailMontant(string $numFact) :string
    {
        return $this->fetchAssocOne(
            "SELECT SUM(MONTANT) as montantTotal 
            FROM FAC_LIG_TRAVAIL with(nolock) 
            WHERE NUM_FACT = :numFact",
            ['numFact' => $numFact]
        )['montantTotal'] ?? '0.0';
    }

    public function listeLigneFactureTravail($numFact, $params, $lot, $post = [])
    {

        $si_secto = '';
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            $si_secto = " and SECTIMPU.CODE_STE = FAC_LIG_TRAVAIL.CODE_STE";
            $cg_secto = " and CODGEST.CODE_STE = FAC_LIG_TRAVAIL.CODE_STE";
        } else {
            $si_secto = " and SECTIMPU.CODE_STE = ''";
            $cg_secto = " and CODGEST.CODE_STE = ''";
        }

        $cdc_secto = '';
        if (\Helpers\Outil\Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE')) {
            $cdc_secto = " and CODECOMP.CODE_STE = FAC_LIG_TRAVAIL.CODE_STE";
        } else {
            $cdc_secto = " and CODECOMP.CODE_STE = ''";
        }

        $sca_secto = '';
        if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            $sca_secto = " and SSCPTANA.CODE_STE = FAC_LIG_TRAVAIL.CODE_STE";
        } else {
            $sca_secto = " and SSCPTANA.CODE_STE = ''";
        }

        $arrayReq = [];
        $jointure = '';
        $select   = '';

        if ((new LicenceOxalys($this->pdo))->codeGestionActive()) {
            $jointure            .= "left join CODGEST with(nolock) 
                on CODGEST.CODE_SI = FAC_LIG_TRAVAIL.CODE_SI 
                and NUM_LOT = :numLot 
                and CODGEST.CODE_GES = FAC_LIG_TRAVAIL.ID_CG $cg_secto";
            $arrayReq[':numLot']  = intval($lot);
            $select              .= " , CODE_AFF, UPPER(LIB_GES) AS LIBELLE_CDG";
        }

        $req = "    SELECT *
                    FROM (  
                            SELECT  
                                FAC_LIG_TRAVAIL.*,
                                SECTIMPU.SICODG,
                                CA_ENTET.CTR_EMETT,
                                CA_ENTET.TYP_DOC,
                                BR_RECEP.DATE_BL,
                                UPPER(SECTIMPU.LIBELLE)     AS LIBELLE_SI, 
                                UPPER(CODECOMP.LIBELLE)     AS LIBELLE_CDC,
                                UPPER(SSCPTANA.LIBELLE)     AS LIBELLE_SCA,
                                SECTIMPU.IMPCODG, SELECTIONNEE SELECT_LIGNE $select 
                            FROM FAC_LIG_TRAVAIL with (nolock)
                            LEFT JOIN CA_ENTET with (nolock) ON CA_ENTET.NO_CDE = FAC_LIG_TRAVAIL.NUM_CDE
                            LEFT JOIN BR_RECEP with (nolock) on BR_RECEP.NUM_BR = FAC_LIG_TRAVAIL.NUM_BR
                            LEFT JOIN SECTIMPU with (nolock) ON UPPER(SECTIMPU.CODE_SI) = UPPER(FAC_LIG_TRAVAIL.CODE_SI) $si_secto
                            $jointure
                            LEFT JOIN CODECOMP with(nolock) ON CODECOMP.CODE_CDC = FAC_LIG_TRAVAIL.CODE_CDC $cdc_secto
                            LEFT JOIN SSCPTANA with(nolock) ON SSCPTANA.CODE_SCA = FAC_LIG_TRAVAIL.CODE_SCA $sca_secto
                            WHERE NUM_FACT = :numFact
                    ) FAC";

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


        $whereArray    = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        $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]);
            }
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    public function detailSelectFactTravail($numFact)
    {
        $req = "select SUM(MONTANT) HT, SUM(MT_TVA) TVA from FAC_LIG_TRAVAIL with (nolock) where NUM_FACT = :numFact and SELECTIONNEE = 0x54 group by NUM_FACT";

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

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch();
    }

    public function detailFactTravail($numFact)
    {
        $req = "select MAX(NUM_LIG) NUM_LIG, COUNT(NUM_LIG) NB_LIG from FAC_LIG_TRAVAIL with (nolock) where NUM_FACT = :numFact group by NUM_FACT";

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

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch();
    }

    public function updateFacLigTravail($numFact, $numLig, $value, $champs, $type)
    {

        if ($type == 'int' || $type == 'float') {
            $set = str_replace(',', '.', $value);
        } elseif ($type == 'RADIO') {
            $set = Sql::toBool($value);
        } else {
            $set = $value;
        }
        (new FacLigTravail($this->pdo))->update([
            $champs => $set,
        ], ['NUM_FACT' => $numFact, 'NUM_LIG' => $numLig]);
    }

    public function insertNewFacLigTravail($numFact, $numLig, $values)
    {
        foreach ($values as &$val) {
            $val = addslashes($val);
        }

        $champSupplementaire     = '';
        $valeurSupplementaire    = '';
        $valeurSupplementaireReq = [];
        if (isset($values['CODE_CDC'])) {
            $champSupplementaire                 .= ', CODE_CDC';
            $valeurSupplementaire                .= ', :codeCdc';
            $valeurSupplementaireReq[':codeCdc']  = $values['CODE_CDC'];
        }
        if (isset($values['CODE_SFAM'])) {
            $champSupplementaire                   .= ', CODE_SFAM';
            $valeurSupplementaire                  .= ', :codeSsFam';
            $valeurSupplementaireReq[':codeSsFam']  = $values['CODE_SFAM'];
        }

        $recepMontant = Sql::setBoolSql($values['RECEP_EN_MONTANT']);
        $req          = "insert into FAC_LIG_TRAVAIL (NUM_FACT, NUM_LIG, TAUX_TVA, TYPE_TVA, RECEP_EN_MONTANT, CODE_STE, CODE_SI, SELECTIONNEE, LIG_MAN$champSupplementaire) VALUES (:numFact, :numLig, " . $values['TAUX_TVA'] . ", :typeTva, " . $recepMontant . " , :codeSte, :codeSI , 0x54, 0x54$valeurSupplementaire)";

        $arrayReq = [
            ':numFact' => $numFact,
            ':numLig'  => $numLig,
            ':typeTva' => $values['TYPE_TVA'],
            ':codeSte' => $values['CODE_STE'],
            ':codeSI'  => $values['CODE_SI'] ?? ''
        ];
        $arrayReq = array_merge($arrayReq, $valeurSupplementaireReq);

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);


        $spliter = Constante::spliter;
        foreach ($values as $key => $value) {
            if (substr($key, 0, 12) == 'CS_EXTENTION') {
                $tblKey    = explode($spliter, $key);
                $numFixe   = $tblKey[3];
                $champSupp = new ChampSupp($this->pdo);
                $detailCS  = $champSupp->generateChampSupp('FAC_LIG', '', $numFixe);

                if ($detailCS['0']['FORMAT'] == 'D') {
                    ChampSupp::convertDateToCS($value);
                } elseif ($detailCS['0']['FORMAT'] == 'L') {
                    if ($value == 'true') {
                        $value = '1';
                    } else {
                        $value = '0';
                    }
                }

                $champSupp->updateChampSupp('FAC_LIG_TRAVAIL', $champSupp::autoCompletCodeEnr($numFact, $numLig, 'FAC_LIG'), $numFixe, $value, $this->pdo);
            }
        }
    }

    public function updateLigneFacLigTravail($numFact, $numLig, $ligne)
    {
        foreach ($ligne as &$val) {
            $val = addslashes($val);
        }

        $req = "UPDATE FAC_LIG_TRAVAIL set CODE_SI = :CODE_SI, ID_CG = :ID_CG, CODE_CDC = :CODE_CDC,  CODE_SCA = :CODE_SCA, QUANTITE = " . $ligne['QUANTITE'] . ", 
                            PU_REMISE = " . $ligne['PU_REMISE'] . ", MONTANT = " . $ligne['MONTANT'] . ", TAUX_TVA = " . $ligne['TAUX_TVA'] . ", MT_TVA = " . $ligne['MT_TVA'] . "
                            where NUM_FACT = :numFact and NUM_LIG = :numLig";

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

        $arrayReq[':CODE_SI']  = ($ligne['CODE_SI'] ?? '');
        $arrayReq[':ID_CG']    = ($ligne['ID_CG'] ?? '');
        $arrayReq[':CODE_CDC'] = ($ligne['CODE_CDC'] ?? '');
        $arrayReq[':CODE_SCA'] = ($ligne['CODE_SCA'] ?? '');

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }

    public function insertLigneFacLigTravail($numFact, $numLigInit, $newNumLig, $ligne)
    {
        foreach ($ligne as &$val) {
            $val = addslashes($val);
        }

        $ligne['SELECT_LIGNE'] = Sql::toBool($ligne['SELECT_LIGNE']) ? '0x54' : '0x46';

        $req = "INSERT INTO FAC_LIG_TRAVAIL 
        SELECT NUM_FACT, $newNumLig, NUM_CDE, NUM_LICDE, NUM_BR, NUM_LIBR, NUM_TERME, DESIGNAT, CODE_SFAM, TP_QUANT, " . $ligne['QUANTITE'] . ", " . $ligne['PU_REMISE'] . ", " . $ligne['MONTANT'] . ", :CODE_SI, :CODE_CDC, :CODE_SCA, CODE_PROFIL_TVA, TYPE_TVA, " . $ligne['TAUX_TVA'] . ",
        REF_FACT,BL_FOURN, CODE_STE, NUM_EXP, NOLIGEXP, DATEMODI,AFF_TMP,LIG_ERR,REFARTFOU, :ID_CG,
        " . $ligne['MT_TVA'] . " ," . $ligne['SELECT_LIGNE'] . ",PRESENCE_INCIDENT_FOURNISSEUR,RECEP_EN_MONTANT,DESCOMP,LIG_MAN,QUANTITE_INIT,PU_REMISE_INIT,MONTANT_INIT,TAUX_TVA_INIT, MT_TVA_INIT
        FROM FAC_LIG_TRAVAIL with (nolock) 
        WHERE NUM_FACT=:numFact and NUM_LIG=:numLig";

        $arrayReq[':numFact'] = $numFact;
        $arrayReq[':numLig']  = $numLigInit;

        $arrayReq[':CODE_SI']  = ($ligne['CODE_SI'] ?? '');
        $arrayReq[':ID_CG']    = ($ligne['ID_CG'] ?? '');
        $arrayReq[':CODE_CDC'] = ($ligne['CODE_CDC'] ?? '');
        $arrayReq[':CODE_SCA'] = ($ligne['CODE_SCA'] ?? '');


        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }

    public function updateFacture($numFact, $post)
    {
        $req = "UPDATE FAC_LIG_TRAVAIL SET ";
        $set = '';

        if (isset($post['update_CODE_SI']) && $post['update_CODE_SI'] != '') {
            $set                  .= ", CODE_SI=:CODE_SI";
            $arrayReq[':CODE_SI']  = $post['update_CODE_SI'];
        }

        if (isset($post['update_CODE_CDC']) && $post['update_CODE_CDC'] != '') {
            $set                   .= ", CODE_CDC=:CODE_CDC";
            $arrayReq[':CODE_CDC']  = $post['update_CODE_CDC'];
        }

        if (isset($post['update_CODE_SCA']) && $post['update_CODE_SCA'] != '') {
            $set                   .= ", CODE_SCA=:CODE_SCA";
            $arrayReq[':CODE_SCA']  = $post['update_CODE_SCA'];
        }

        if (isset($post['update_TYPE_TVA']) && $post['update_TYPE_TVA'] != '') {
            $set                   .= ", TYPE_TVA=:TYPE_TVA";
            $arrayReq[':TYPE_TVA']  = $post['update_TYPE_TVA'];
        }

        if (isset($post['update_TAUX_TVA']) && $post['update_TAUX_TVA'] != '') {
            $set .= ", TAUX_TVA=" . $post['update_TAUX_TVA'] . "";

            $set .= ", MT_TVA = ((MONTANT * (1+CAST('" . $post['update_TAUX_TVA'] . "' as FLOAT)/100)) - MONTANT)";
        }

        if (isset($post['update_SELECTIONNEE']) && $post['update_SELECTIONNEE'] != '') {
            $set .= ", SELECTIONNEE=" . Sql::setBoolSql($post['update_SELECTIONNEE']) . "";
        }

        if (isset($post['update_CG']) && $post['update_CG'] != '') {
            $set                .= ", ID_CG=:ID_CG";
            $arrayReq[':ID_CG']  = $post['update_CG'];
        } elseif (isset($post['update_CODE_SI']) && $post['update_CODE_SI'] != '') {
            $set                .= ", ID_CG=:ID_CG";
            $arrayReq[':ID_CG']  = '';
        }

        $req .= substr($set, 1);

        $req                  .= " WHERE NUM_FACT=:numFact";
        $arrayReq[':numFact']  = $numFact;

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);

        if (count($whereArray) > 0) {
            $req .= $whereArray[0];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }

    public function infoLigneTravail($numFact, $numLig)
    {
        $req = " SELECT  
                    FAC_LIG_TRAVAIL.*,
                    CA_ENTET.CTR_EMETT,
                    CA_ENTET.TYP_DOC,
                    BR_RECEP.DATE_BL
                FROM FAC_LIG_TRAVAIL with (nolock)
                LEFT JOIN CA_ENTET with (nolock) ON CA_ENTET.NO_CDE = FAC_LIG_TRAVAIL.NUM_CDE
                LEFT JOIN BR_RECEP with (nolock) on BR_RECEP.NUM_BR = FAC_LIG_TRAVAIL.NUM_BR
                where FAC_LIG_TRAVAIL.NUM_FACT=:numFact and FAC_LIG_TRAVAIL.NUM_LIG=:numLig";

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

        $facture = $this->pdo->prepare($req);
        $facture->execute($arrayReq);
        return $facture->fetch($this->pdo::FETCH_ASSOC);
    }

    public function infoCSLigTravail($numFact, $numLig)
    {
        $champSupp = new ChampSupp($this->pdo);
        $numCS     = $champSupp::autoCompletCodeEnr($numFact, $numLig, 'FAC_LIG');

        $req      = "select * from CS_DON with (nolock) where COD_TAB='FAC_LIG_TRAVAIL' and COD_ENR=:numCS";
        $arrayReq = [
            ':numCS' => $numCS,
        ];
        $facture  = $this->pdo->prepare($req);
        $facture->execute($arrayReq);

        return $facture->fetchAll($this->pdo::FETCH_ASSOC);
    }

    public function copyFacLigToFacLigTravail($numFact)
    {
        $req = "INSERT INTO FAC_LIG_TRAVAIL 
        SELECT NUM_FACT, NUM_LIG, NUM_CDE, NUM_LICDE, NUM_BR, NUM_LIBR, NUM_TERME, DESIGNAT, CODE_SFAM, TP_QUANT, QUANTITE, PU_REMISE, MONTANT, CODE_SI, CODE_CDC, CODE_SCA, CODE_PROFIL_TVA, TYPE_TVA, TAUX_TVA,
        REF_FACT,BL_FOURN, CODE_STE, NUM_EXP, NOLIGEXP, DATEMODI,AFF_TMP,LIG_ERR,REFARTFOU, ID_CG,
        MT_TVA ,0x54,0x46,0x54,'',0x54,QUANTITE,PU_REMISE,MONTANT,TAUX_TVA, MT_TVA
        FROM FAC_LIG with (nolock) 
        WHERE NUM_FACT=:numFact";

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

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        // CREATION DES CS
        $champSupp = new ChampSupp($this->pdo);
        $req       = "INSERT INTO CS_DON 
        SELECT 'FAC_LIG_TRAVAIL', COD_ENR, NUM_FIX, VALEUR, VALEUR_COMP
        FROM CS_DON with (nolock) 
        WHERE COD_TAB = :codTab and COD_ENR like :codEnr";

        $arrayReq = [
            ':codTab' => 'FAC_LIG',
            ':codEnr' => $champSupp::autoCompletCodeEnr($numFact) . '%',
        ];

        $request = $this->pdo->prepare($req);
        $request->execute($arrayReq);
    }

    public function deleteLigneFacLigTravail($numFact)
    {
        $req = "DELETE FROM FAC_LIG_TRAVAIL WHERE NUM_FACT=:numFact";

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

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        // SUPPPRESION DES CS
        $champSupp = new ChampSupp($this->pdo);
        $req       = "DELETE FROM CS_DON WHERE COD_TAB = :codTab and COD_ENR like :codEnr";

        $arrayReq = [
            ':codTab' => 'FAC_LIG_TRAVAIL',
            ':codEnr' => $champSupp::autoCompletCodeEnr($numFact) . '%',
        ];

        $request = $this->pdo->prepare($req);
        $request->execute($arrayReq);
    }

    /**
     * @param array<mixed> $post
     *
     * @return array<mixed>|bool
     */
    public function articleSiCdcSca($post)
    {
        $si_secto  = Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE') ? true : false;
        $cdc_secto = Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE') ? true : false;
        $sca_secto = Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE') ? true : false;

        $req = "SELECT * FROM (
                SELECT  ARTICLES.CODE_ART,
                        CASE
                            WHEN DES_ART IS NULL OR DES_ART = ''
                            THEN DESIGN else DES_ART
                        END DESIGNAT,
                        ARTICLES.SSFAM AS CODE_SFAM,
                        isnull(SISTEART.SI,isnull(SISTESFA.SI,isnull(SISTEFAM.SI,''))) AS CODE_SI,
                        isnull(CDCSTEAR.CDC,isnull(CDCSTESF.CDC,isnull(CDCSTEFA.CDC,''))) AS CODE_CDC,
                        isnull(SCASTEAR.SCA,isnull(SCASTESF.SCA,isnull(SCASTEFA.SCA,''))) AS CODE_SCA

                FROM ARTICLES WITH (nolock)
                LEFT JOIN TRAD_ART WITH (nolock) on TRAD_ART.COD_ART = ARTICLES.CODE_ART AND COD_LNG = :langue
                LEFT JOIN SSFAMILL with(nolock) on SSFAMILL.CODE_SFA = ARTICLES.SSFAM

                LEFT JOIN SISTEART with(nolock) on SISTEART.CODE_ART = ARTICLES.CODE_ART and SISTEART.CODE_STE= :CODESTESI1
                LEFT JOIN SISTESFA with(nolock) on SISTESFA.SFAMILL = ARTICLES.SSFAM and SISTESFA.CODE_STE= :CODESTESI2
                LEFT JOIN SISTEFAM with(nolock) on SISTEFAM.FAMILLE = SSFAMILL.CODE_FAM and SISTEFAM.CODE_STE= :CODESTESI3

                LEFT JOIN CDCSTEAR with(nolock) on CDCSTEAR.CODE_ART = ARTICLES.CODE_ART and CDCSTEAR.CODE_STE= :CODESTECDC1
                LEFT JOIN CDCSTESF with(nolock) on CDCSTESF.SFAMILL = ARTICLES.SSFAM and CDCSTESF.CODE_STE= :CODESTECDC2
                LEFT JOIN CDCSTEFA with(nolock) on CDCSTEFA.FAMILLE = SSFAMILL.CODE_FAM and CDCSTEFA.CODE_STE= :CODESTECDC3

                LEFT JOIN SCASTEAR with(nolock) on SCASTEAR.CODE_ART = ARTICLES.CODE_ART and SCASTEAR.CODE_STE= :CODESTESCA1
                LEFT JOIN SCASTESF with(nolock) on SCASTESF.SFAMILL = ARTICLES.SSFAM and SCASTESF.CODE_STE= :CODESTESCA2
                LEFT JOIN SCASTEFA with(nolock) on SCASTEFA.FAMILLE = SSFAMILL.CODE_FAM and SCASTEFA.CODE_STE= :CODESTESCA3

                ) ARTICLES WHERE 1=1 AND ARTICLES.CODE_ART = :CODEART
                ";

        $societeIfSISectorise  = $si_secto ? ($post['CODE_STE'] ?? "") : "";
        $societeIfCDCSectorise = $cdc_secto ? ($post['CODE_STE'] ?? "") : "";
        $societeIfSCASectorise = $sca_secto ? ($post['CODE_STE'] ?? "") : "";

        $arrayReq[':langue']      = $_SESSION['Session_Langue'];
        $arrayReq[':CODESTESI1']  = $arrayReq[':CODESTESI2'] = $arrayReq[':CODESTESI3'] = $societeIfSISectorise;
        $arrayReq[':CODESTECDC1'] = $arrayReq[':CODESTECDC2'] = $arrayReq[':CODESTECDC3'] = $societeIfCDCSectorise;
        $arrayReq[':CODESTESCA1'] = $arrayReq[':CODESTESCA2'] = $arrayReq[':CODESTESCA3'] = $societeIfSCASectorise;
        $arrayReq[':CODEART'] = $post["CODE_ART"] ?? '';

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param array<string> $fac FacEnt
     * @return array<string>
     */
    public function getDuplicateRefFou(array $fac): array
    {
        return $this->fetchAssocOne(
            "SELECT * FROM FAC_ENT with(nolock)
            WHERE CODE_FOUR = :fou
                AND FACT_FOU = :ref 
                AND SUBSTRING(DATE_FAFO, 1, 4) = :year
            " . (!empty($fac['NUM_FACT']) ? " AND NUM_FACT != :numFact " : ''),
            [
                'fou'  => $fac['CODE_FOUR'],
                'ref'  => $fac['FACT_FOU'],
                'year' => substr($fac['DATE_FAFO'], 0, 4),
            ]
            + (!empty($fac['NUM_FACT']) ? [
                'numFact' => $fac['NUM_FACT'],
            ] : [])
        );
    }
    
    /**
     * @return array<mixed>
     */
    public function getFactureByRefFour(string $ref, string $year)
    {
        return $this->fetchAssocOne(
            "SELECT * FROM FAC_ENT with(nolock)
            WHERE FACT_FOU = :ref AND SUBSTRING(DATE_FAFO, 1, 4) = :year ",
            [':ref' => $ref, ':year' => $year]
        );
    }
}
