<?php

namespace Modules\Facture\Repository;

use Helpers\Outil\Date;
use Helpers\Pagination\PaginatorGestion;
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  
        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="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,  
            (select TOP 1 count(*) from FOURNINC fournic with (nolock) where fournic.NO_CDE=c.NO_CDE) incident  
            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
            where c.CODE_FOU=:value1 and c.CODE_STE=:value2 and c.ETAT_FIN='' and (c.ETAT_CDE='E' or c.ETAT_CDE='S')";
            
        $arrayReq = [
            ':value1' => $codeFournisseur,
            ':value2' => $codeSociete
            
        ];
        /** @var \SimpleXMLElement $xml */
        $xml=simplexml_load_string($_SESSION['OXAPRC_LOGUSR_XML']);
        if ($xml->CONFIDENTIALITE_ACTIVE) {
            $req .= ' and t.NIV_CONF<='.$xml->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['filter_NO_CDE']) &&  $post['filter_NO_CDE'] !='') {
            $req .=' AND c.NO_CDE LIKE :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'];
        }

        $req .= ' ORDER BY c.NO_CDE DESC';

        (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

            WHERE c.CODE_FOU=:Fourniss
                AND c.CODE_STE=:Societe 
                AND c.ETAT_FIN='' 
                AND (c.ETAT_CDE='A' OR c.ETAT_CDE='V')";
        
        /** @var \SimpleXMLElement $xml */
        $xml=simplexml_load_string($_SESSION['OXAPRC_LOGUSR_XML']);
        if ($xml->CONFIDENTIALITE_ACTIVE) {
            $req .= ' AND t.NIV_CONF<='.$xml->NiveauConfidentialite;
        }
        
        return $this->fetchAssocAll($req, [
            ':Fourniss' => $post['CODE_FOUR'],
            ':Societe' => $post['CODE_STE']
        ]);
    }
    
    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 = [];
        
        /** @var \SimpleXMLElement $xml */
        $xml=simplexml_load_string($_SESSION['OXAPRC_LOGUSR_XML']); // TODO
        if ($xml->CONFIDENTIALITE_ACTIVE) {
            $req .= ' AND t.NIV_CONF<='.$xml->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 = '';
        
        /** @var \SimpleXMLElement $xml */
        $xml=simplexml_load_string($_SESSION['OXAPRC_LOGUSR_XML']);
        if ($xml->CONFIDENTIALITE_ACTIVE) {
            $reqConfidantialite .= ' AND t.NIV_CONF<='.$xml->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 cal.QTE_FACTU>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 paramsFactFouUnique()
    {
        $req=" SELECT LIB01PARV FROM PARAVRAC with (nolock) WHERE TYPEPARV=:value1 AND CODEPARV=:value2";
        
        $arrayReq = [
            ':value1' => 'PARAMGEN',
            ':value2' => 'FACFOU_UNIQUE'
            
        ];
        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        
        if (\str_replace(' ', '', $param->fetch()['LIB01PARV']) == 'O') {
            return true;
        } else {
            return false;
        }
    }
    
    public function getModeNumFact($societe)
    {
        $req=" SELECT LIB01PARV FROM PARAVRAC with (nolock) WHERE TYPEPARV=:value1 AND CODEPARV=:value2";
        
        $arrayReq = [
            ':value1' => 'PARAMGEN',
            ':value2' => 'SECTNUMFAC'
        ];
        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        
        if (\str_replace(' ', '', $param->fetch()['LIB01PARV']) == 'O') {
            $paramnum = new \Entity\Model\Paramnum($this->pdo);
            $paramnum = $paramnum->find(['CLE' => 'FA', 'CODE_STE' => $societe]);
        } else {
            $paramnum = new \Entity\Model\Paramnum($this->pdo);
            $paramnum = $paramnum->find(['CLE' => 'FA']);
        }
        
        return $paramnum['MODE_NUM'] ?? '';
    }
    
    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, 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);
    }
    
    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 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 ($params['IS_CG_ACTIVE']) {
            $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'] = $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::setBoolSql($value);
        } else {
            $set = ':value';
            $arrayReq[':value'] = $value;
        }
        
        $req = "UPDATE FAC_LIG_TRAVAIL set $champs = $set where NUM_FACT = :numFact and NUM_LIG = :numLig";
        $arrayReq[':numFact'] = $numFact;
        $arrayReq[':numLig']  = $numLig;
        
        
        $requete = $this->pdo->prepare($req);
        try {
            $requete->execute($arrayReq);
        } catch (\Exception $e) {
            @var_dump($e->getMessage());
        }
    }
    
    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, $this->pdo);
                } 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);
    }
}
