<?php

namespace Modules\Panier\Repository;

use Helpers\QueryBuilder\AbsRepository;
use Modules\Index\Repository\Connexion;
use Helpers\Outil\Sql;

class PanierEntete extends AbsRepository
{

    protected $pdo;

    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * Summary of updateEntetePanierSociete
     * @param int|string $idPanier
     * @param string $societe
     */
    public function updateEntetePanierSociete($idPanier, $societe) :void
    {
        $req = "UPDATE oxa_users_panier SET CODE_STE=:societe WHERE id_user = :idUser and id_panier=:idPanier";
        
        $arrayReq=[
            ':societe' => $societe,
            ':idUser' => $_SESSION['id_user'],
            ':idPanier' => $idPanier,
        ];

        $this->execute($req, $arrayReq);
    }

    /* rqGetInfoFourPanier */
    /**
     * Summary of getInfoFourPanier
     * @param array<mixed> $critere
     * @return array<mixed>
     */
    public function getInfoFourPanier($critere)
    {
        $req = "select id_panier,id_user,COD_FOURN, commentaire,PX_ART,CAST(Note AS TEXT) AS Note,DATELIVR,TYP_ACH, MDP, CDP, MDL, MSG_STD, flg_add_pj, flg_add_clause, DOSSIER_ACHAT_COMP, COMMENT_SUIVI_ACHAT , CRITERE_REGROUP , ADR, VILLE1, CPOSTAL, CODE_PAYS, RECEP_AUTO, PER_REC_AUTO, DATEDEBCONT, DATEFINCONT, FIN_DEB_PER 
                from oxa_users_panier_four with (nolock)
                WHERE oxa_users_panier_four.id_user  = '".($_SESSION['id_user'] ?? $critere['id_user'])."' AND oxa_users_panier_four.id_panier  = '".($_SESSION['id_panier'] ?? $critere['id_panier'])."' AND oxa_users_panier_four.COD_FOURN = '{$critere['COD_FOURN']}' ";

        if (isset($critere['CRITERE_REGROUP']) && $critere['CRITERE_REGROUP']!='') {
            $req .= "  AND oxa_users_panier_four.CRITERE_REGROUP = '{$critere['CRITERE_REGROUP']}' ";
        }
        
        return $this->fetchAssocOne($req, []);
    }
    
    /**
     * Summary of rqCopiePanierFrsPanierPjChangeCritere
     * @param int $id_panier
     * @param string $critereOld
     * @param string $critereNew
     * @return void
     */
    public function rqCopiePanierFrsPanierPjChangeCritere($id_panier, $critereOld, $critereNew)
    {
        $req = "insert into oxa_users_panier_four_pj (id_user,id_panier,COD_FOURN,CRITERE_REGROUP,PJ_TYPES,TYP_ACH,nom_piece_user,nom_piece_sys,PHYSIQUE,OUVRABLE,IMPRIMABL,VERROU,ISSU_DOCATT,TABLE_DOCATT,TITRE)  
                select id_user,{$id_panier}  ,COD_FOURN,'" . $critereNew . "',PJ_TYPES,TYP_ACH,nom_piece_user,nom_piece_sys,PHYSIQUE,OUVRABLE,IMPRIMABL,VERROU,ISSU_DOCATT,TABLE_DOCATT,TITRE 
                from oxa_users_panier_four_pj where id_user = {$_SESSION['id_user']} AND id_panier = {$id_panier} AND CRITERE_REGROUP = '" . $critereOld . "' ";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqCopiePanierFrsPanierClauseChangeCritere
     * @param int $id_panier
     * @param string $critereOld
     * @param string $critereNew
     * @return void
     */
    public function rqCopiePanierFrsPanierClauseChangeCritere($id_panier, $critereOld, $critereNew)
    {
        $req = "insert into oxa_users_panier_four_clause (id_user,id_panier,COD_FOURN,CRITERE_REGROUP,CONTENU,TYP_ACH,CLAU_FIX,VERROU)  
                select id_user,{$id_panier}  ,COD_FOURN, '" . $critereNew . "', CONTENU,TYP_ACH,CLAU_FIX,VERROU 
                from oxa_users_panier_four_clause  where id_user = {$_SESSION['id_user']} AND   id_panier   = {$id_panier} AND   CRITERE_REGROUP = '" . $critereOld . "' ";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqCopiePanierContactLivByBlocFrsClauseChangeCritere
     * @param int $id_panier
     * @param string $critereOld
     * @param string $critereNew
     * @return void
     */
    public function rqCopiePanierContactLivByBlocFrsClauseChangeCritere($id_panier, $critereOld, $critereNew)
    {
        $req = "insert into OXA_USERS_PANIER_FOUR_CONTACT (ID_USER, ID_PANIER, CTC_TYPE, CODE_CTR_LIV, CODE_FOURN, CRITERE_REGROUP, CTC_IND, CTC_NOM, CTC_FCT, CTC_TEL, CTC_FAX, CTC_MAIL, CTC_GSM )
				select ID_USER, $id_panier , CTC_TYPE, CODE_CTR_LIV, CODE_FOURN, '" . $critereNew . "', CTC_IND, CTC_NOM, CTC_FCT, CTC_TEL, CTC_FAX, CTC_MAIL, CTC_GSM
				from OXA_USERS_PANIER_FOUR_CONTACT where ID_USER     = {$_SESSION['id_user']} AND   ID_PANIER   = {$id_panier} AND   CRITERE_REGROUP = '" . $critereOld . "' ";
        
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqCopiePanierContactFrsByBlocFrsClauseChangeCritere
     * @param int $id_panier
     * @param string $critereOld
     * @param string $critereNew
     * @return void
     */
    public function rqCopiePanierContactFrsByBlocFrsClauseChangeCritere($id_panier, $critereOld, $critereNew)
    {
        $req = "insert into OXA_USERS_PANIER_FOUR_CONTACT (ID_USER,ID_PANIER,CTC_TYPE,CODE_FOURN,CRITERE_REGROUP,CTC_IND,CTC_NOM,CTC_FCT,CTC_TEL,CTC_FAX,CTC_MAIL,CTC_GSM)
				select ID_USER,$id_panier, CTC_TYPE, CODE_FOURN, '" . $critereNew . "', CTC_IND,CTC_NOM,CTC_FCT,CTC_TEL,CTC_FAX,CTC_MAIL,CTC_GSM
				from OXA_USERS_PANIER_FOUR_CONTACT where ID_USER = {$_SESSION['id_user']} AND   ID_PANIER   = {$id_panier} AND   CRITERE_REGROUP = '" . $critereOld . "' ";

        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqCopiePanierFrsChangeCritere
     * @param int $id_panier
     * @param string $critereOld
     * @param string $critereNew
     * @return void
     */
    public function rqCopiePanierFrsChangeCritere($id_panier, $critereOld, $critereNew)
    {
        $req = "INSERT INTO oxa_users_panier_four (
            id_user, id_panier,
            PX_ART,
            COD_FOURN,
            commentaire,
            Note,
            DATELIVR,MDP,CDP,MDL,
            MSG_STD,TYP_ACH,flg_add_pj,
            flg_add_clause, 
            DOSSIER_ACHAT_COMP, COMMENT_SUIVI_ACHAT, ADR, CODE_PAYS, VILLE1, 
            CPOSTAL,CRITERE_REGROUP
        ) select 
            id_user, :id_panier,
            PX_ART, COD_FOURN, commentaire,
            Note,DATELIVR,MDP,CDP,MDL,MSG_STD,TYP_ACH,
            flg_add_pj,flg_add_clause,
            DOSSIER_ACHAT_COMP, COMMENT_SUIVI_ACHAT,
            ADR, CODE_PAYS, VILLE1, CPOSTAL,
            :critereNew
        from oxa_users_panier_four 
        where id_user = :id_user 
            AND id_panier = :id_panier_0
            AND CRITERE_REGROUP = :critereOld";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute([
            ':id_panier' => $id_panier,
            ':id_panier_0' => $id_panier,
            ':id_user' => $_SESSION['id_user'],
            ':critereNew' => $critereNew,
            ':critereOld' => $critereOld,
        ]);
    }
    
    /**
     * Summary of rqCopiePanierFrsPanierDocRattChangeCritere
     * @param int $id_panier
     * @param string $critereOld
     * @param string $critereNew
     * @return void
     */
    public function rqCopiePanierFrsPanierDocRattChangeCritere($id_panier, $critereOld, $critereNew)
    {
        $req = "insert into OXA_USERS_PANIER_FOUR_DOC (ID_USER,ID_PANIER, COD_FOURN,CRITERE_REGROUP,NUM_DOC,TITRE,TYPE_DOC,NOM_FIC_ORI,NOM_FIC_FINAL,DATE_PARUTION,DATE_VALIDITE,AUTEUR,CENTRE,COMMENTAIRE)  
                select ID_USER,{$id_panier},COD_FOURN,'" .$critereNew. "',NUM_DOC,TITRE,TYPE_DOC,NOM_FIC_ORI,NOM_FIC_FINAL,DATE_PARUTION,DATE_VALIDITE,AUTEUR,CENTRE,COMMENTAIRE from OXA_USERS_PANIER_FOUR_DOC 
                where ID_USER = {$_SESSION['id_user']} AND  ID_PANIER   = {$id_panier} AND CRITERE_REGROUP = '" . $critereOld . "'";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqUpdateCritereRegroupementLigne
     * @param string $id_ligne
     * @param string $critere
     * @return void
     */
    public function rqUpdateCritereRegroupementLigne($id_ligne, $critere)
    {
        $req = "update oxa_users_panier_ligne set CRITERE_REGROUP  = '$critere' 
                where oxa_users_panier_ligne.id_user   = {$_SESSION['id_user']} and  oxa_users_panier_ligne.id_panier = {$_SESSION['id_panier']} and oxa_users_panier_ligne.id_lig = {$id_ligne} ";

        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqGetLignePanierBlocFournisseur
     * @param int $id_panier
     * @param array<mixed> $critere
     * @return array<mixed>
     */
    public function rqGetLignePanierBlocFournisseur($id_panier, &$critere)
    {
        $req = "select * FROM oxa_users_panier_ligne with (nolock) 
                where id_user   = {$_SESSION['id_user']} and   id_panier = {$id_panier} ";

        if (isset($critere['COD_FOURN']) && $critere['COD_FOURN']) {
            $req .=  "AND   COD_FOURN = '" . $critere['COD_FOURN'] . "' ";
        }
        
        if (isset($critere['CRITERE_REGROUP']) && $critere['CRITERE_REGROUP']) {
            $req .=  "AND   CRITERE_REGROUP = '" . $critere['CRITERE_REGROUP'] . "' ";
        }
        
        return $this->fetchAssocOne($req, []);
    }
    
    /**
     * Summary of rqDeleteDocRattRegroupement
     * @param int $idPanier
     * @param array<mixed> $bloc
     * @return void
     */
    public function rqDeleteDocRattRegroupement($idPanier, $bloc)
    {
        $req = "DELETE FROM OXA_USERS_PANIER_FOUR_DOC WHERE ID_USER =  {$_SESSION['id_user']} AND ID_PANIER = {$idPanier} AND COD_FOURN = '{$bloc['COD_FOURN']}' AND CRITERE_REGROUP = '{$bloc['CRITERE_REGROUP']}'";

        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqDeletePjRegroupement
     * @param int $idPanier
     * @param array<mixed> $bloc
     * @return void
     */
    public function rqDeletePjRegroupement($idPanier, $bloc)
    {
        $req = "DELETE FROM oxa_users_panier_four_pj WHERE id_user =  {$_SESSION['id_user']} AND id_panier = {$idPanier} AND COD_FOURN = '{$bloc['COD_FOURN']}' AND CRITERE_REGROUP = '{$bloc['CRITERE_REGROUP']}'";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqDeleteClauseRegroupement
     * @param int $idPanier
     * @param array<mixed> $bloc
     * @return void
     */
    public function rqDeleteClauseRegroupement($idPanier, $bloc)
    {
        $req = "DELETE FROM oxa_users_panier_four_clause WHERE id_user = {$_SESSION['id_user']} AND id_panier = {$idPanier} AND COD_FOURN = '{$bloc['COD_FOURN']}' AND CRITERE_REGROUP = '{$bloc['CRITERE_REGROUP']}'";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqDeleteContactRegroupement
     * @param int $idPanier
     * @param array<mixed> $bloc
     * @return void
     */
    public function rqDeleteContactRegroupement($idPanier, $bloc)
    {
        $req = "DELETE FROM OXA_USERS_PANIER_FOUR_CONTACT WHERE ID_USER = {$_SESSION['id_user']} AND ID_PANIER = {$idPanier} AND CODE_FOURN = '{$bloc['COD_FOURN']}' AND CRITERE_REGROUP = '{$bloc['CRITERE_REGROUP']}'";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqGetAllFrsByUserPanier
     * @return array<mixed>
     */
    public function rqGetAllFrsByUserPanier()
    {
        $req = "select * from oxa_users_panier_four with (nolock)
                WHERE oxa_users_panier_four.id_user  = {$_SESSION['id_user']} AND oxa_users_panier_four.id_panier  = {$_SESSION['id_panier']}";

        return $this->fetchAssocAll($req, []);
    }
    
    /**
     * Summary of rqUpdateContactByBlocFrs
     * @param array<mixed> $information_four
     * @param string $type
     * @param string $ctc_ind
     * @param array<mixed> $info_contact_livr
     * @return void
     */
    public function rqUpdateContactByBlocFrs($information_four, $type, $ctc_ind, $info_contact_livr)
    {
        $req = " update OXA_USERS_PANIER_FOUR_CONTACT
					set
					CODE_CTR_LIV 		= '" . (array_key_exists('CODE_CTR_LIV', $info_contact_livr) ? $info_contact_livr['CODE_CTR_LIV'] : '') . "'	,
					CTC_NOM				= '" . ($info_contact_livr['CTC_NOM']) . "'		,
					CTC_FCT		 		= '" . ($info_contact_livr['CTC_FCT']) . "'		,
					CTC_TEL 			= '" . ($info_contact_livr['CTC_TEL']) . "'		,
					CTC_FAX 			= '" . ($info_contact_livr['CTC_FAX']) . "'		,
					CTC_MAIL 			= '" . ($info_contact_livr['CTC_MAIL']) . "'	,
					CTC_GSM 			= '" . ($info_contact_livr['CTC_GSM']) . "'
                    WHERE       OXA_USERS_PANIER_FOUR_CONTACT.ID_USER    	= {$_SESSION['id_user']}
						AND   	OXA_USERS_PANIER_FOUR_CONTACT.ID_PANIER  	= {$_SESSION['id_panier']}
						AND   	OXA_USERS_PANIER_FOUR_CONTACT.CODE_FOURN  	= '" . ($information_four['COD_FOURN']) . "'
						AND   	OXA_USERS_PANIER_FOUR_CONTACT.CRITERE_REGROUP  	= '" . ($information_four['CRITERE_REGROUP']) . "'
						AND   	OXA_USERS_PANIER_FOUR_CONTACT.CTC_IND  		= '{$ctc_ind}'
						AND   	OXA_USERS_PANIER_FOUR_CONTACT.CTC_TYPE  	= '{$type}'";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqGetFrsLigne
     * @param string $id_panier
     * @param string $cod_frs
     * @return string[][]
     */
    public function rqGetFrsLigne($id_panier, $cod_frs = null)
    {
        $req = "select COD_FOURN,CRITERE_REGROUP,COD_DEV from oxa_users_panier_ligne with (nolock) 
                WHERE oxa_users_panier_ligne.id_user = {$_SESSION['id_user']} AND  oxa_users_panier_ligne.id_panier  = {$id_panier}";

        if ($cod_frs) {
            $req .= "AND COD_FOURN = '".$cod_frs."'";
        }
        
        return $this->fetchAssocAll($req, []);
    }
    
    /**
     * Summary of rqGetFlagAddPj
     * @param array<mixed> $bloc
     * @return string[][]
     */
    public function rqGetFlagAddPj(&$bloc)
    {
        $req = "SELECT flg_add_pj FROM oxa_users_panier_four with (nolock) WHERE COD_FOURN = '{$bloc['COD_FOURN']}' AND CRITERE_REGROUP = '{$bloc['CRITERE_REGROUP']}'";
        
        return $this->fetchAssocAll($req, []);
    }
    
    /**
     * Summary of rqDeletePjDocAtt
     * @param array<mixed> $bloc
     * @param string $tbl_docatt
     * @return void
     */
    public function rqDeletePjDocAtt($bloc, $tbl_docatt)
    {
        $req = "DELETE FROM oxa_users_panier_four_pj WHERE id_user = {$_SESSION['id_user']} AND id_panier = {$_SESSION['id_panier']} AND ISSU_DOCATT = 0x54 AND TABLE_DOCATT = '{$tbl_docatt}' AND COD_FOURN = '{$bloc['COD_FOURN']}' AND CRITERE_REGROUP = '{$bloc['CRITERE_REGROUP']}'";

        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }
    
    /**
     * Summary of rqGetPjDocAtt
     * @param string $COD_FOURN
     * @return string[][]|null
     */
    public function rqGetPjDocAtt($COD_FOURN)
    {
        $connexionSQL = new Connexion($this->pdo);
        $EntetePanier = $connexionSQL->getInfoPannier($_SESSION['id_panier'], $_SESSION['id_user']);

        $CTRLIV = $EntetePanier['LIV_CTR'];
        $where = '';
        if ($CTRLIV != '') {
            /** @phpstan-ignore-next-line */
            $where .= ($where != '' ? ' OR ' : ' ') . " NO_DA = 'CENTRE' AND COD_ENR = '{$CTRLIV}' ";
        }
        if ($_SESSION['Session_CodeCtrEmetteur'] != '') {
            $where .= ($where != '' ? ' OR ' : ' ') . " NO_DA = 'CENTRE' AND COD_ENR = '{$_SESSION['Session_CodeCtrEmetteur']}' ";
        }
        if ($_SESSION['Session_SocUsr'] != '') {
            $where .= ($where != '' ? ' OR ' : ' ') . " NO_DA = 'SOCIETE' AND COD_ENR = '{$_SESSION['Session_SocUsr']}' ";
        }
        if ($COD_FOURN != '') {
            $where .= ($where != '' ? ' OR ' : ' ') . " NO_DA = 'FOURNISS' AND COD_ENR = '{$COD_FOURN}' ";
        }
                        
        if ($where == '') {
            return null;
        }
                            
        $sql = "select * from DOC_ATT with (nolock) ' . 'WHERE PJ_CDE = 0x54 AND (DT_PARUT = '00000000' OR DT_PARUT <= '".date('Ymd')."') AND (DT_VALID = '00000000' OR DT_VALID >= '".date('Ymd')."') AND ({$where}) ";
        
        return $this->fetchAssocAll($sql, []);
    }
    
    /**
     * Summary of rqAddPj
     * @param string $id_user
     * @param string $id_panier
     * @param array<mixed> $liste_four
     * @param string $titre
     * @param string $pj_type
     * @param string $nom_piece_user
     * @param string $nom_file_system
     * @param string $FIC_PDF
     * @param mixed $ouvertur
     * @param mixed $imprimable
     * @param mixed $aimprimer
     * @param string $typ_ach
     * @param mixed $verrou
     * @param mixed $useCga
     * @param string $issu_docatt
     * @param string $table_docatt
     * @return void
     */
    public function rqAddPj($id_user, $id_panier, $liste_four, $titre, $pj_type, $nom_piece_user, $nom_file_system, $FIC_PDF, $ouvertur, $imprimable, $aimprimer, $typ_ach, $verrou, $useCga, $issu_docatt, $table_docatt)
    {
        $COD_FOURN = (array_key_exists('COD_FOURN', $liste_four)) ? $liste_four['COD_FOURN'] : '';
        $CRITERE_REGROUP = (array_key_exists('CRITERE_REGROUP', $liste_four)) ? $liste_four['CRITERE_REGROUP'] : '';
        
        $req = "insert into oxa_users_panier_four_pj (id_user,id_panier,COD_FOURN,CRITERE_REGROUP,TITRE,PJ_TYPES,nom_piece_user,nom_piece_sys,FIC_PDF,PHYSIQUE,OUVRABLE,IMPRIMABL,VERROU,USE_CGA,TYP_ACH,ISSU_DOCATT,TABLE_DOCATT) 
                VALUES({$id_user},{$id_panier},'{$COD_FOURN}','{$CRITERE_REGROUP}','" . $titre . '\'' . ",'" . $pj_type . '\'' . ",'" . $nom_piece_user . '\'' . ",'" . $nom_file_system . '\'' . ",'" . $FIC_PDF . "'" . ", " . Sql::setBoolSql($ouvertur) . ", " . Sql::setBoolSql($imprimable) . ", " . Sql::setBoolSql($aimprimer) . ", " . Sql::setBoolSql($verrou) . ", " . Sql::setBoolSql($useCga) . ",'{$typ_ach}'" . ", " . ($issu_docatt == 1 ? '0x54' : '0x46') . ",'{$table_docatt}'" . ')';
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }

    /**
     * @return array<mixed>
     */
    public function getEntetePanier(string $idPanier, string $idUser)
    {
        $siSecto = \Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE');
        $scaSecto = \Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE');
        $cdcSecto = \Helpers\Outil\Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE');
        
        $req = 'SELECT 
                    id_user, 
                    id_panier, 
                    id_rowid_panier, 
                    lib_panier, 
                    flg_panier_modele, 
                    NO_CDE, 
                    CODE_FM, 
                    COD_DEV, 
                    mnt_budget, 
                    NO_DA, 
                    CHPSPECCLT, 
                    IS_INITIALIZED, 
                    NO_DEM, TOTAL_DA, 
                    BUDGET_DA, 
                    oxa_users_panier.CODE_STE, 
                    DEVISE, 
                    SI_CODE,  
                    SECTIMPU.LIBELLE AS LIBELLE_SI, 
                    SECTIMPU.SICODG,
                    SECTIMPU.IMPCODG,
                    CDC_CODE,  
                    CODECOMP.LIBELLE AS LIBELLE_CDC, 
                    ORIGINE, SCA_CODE, 
                    SSCPTANA.LIBELLE AS LIBELLE_SCA,  
                    CODGEST.CODE_AFF AS CG_CODE,  
                    CODGEST.LIB_GES AS LIBELLE_CG, 
                    CHIF_EXEC, 
                    CHIF_EXEC_LOCK, 
                    LIV_CTR, 
                    CENTRE.RAIS_SOC AS LIBELLE_LIV_CTR, 
                    CENTRE.TRS_INTER,
                    CENTRE.SITSTOCK,
                    oxa_users_panier.MOD_TRS, 
                    LIV_ADR, 
                    LIV_PAYS, COUNTRY.LIBELLE LIB_PAYS,
                    LIV_CP, 
                    LIV_VILLE, 
                    CTC_TEL, 
                    CTC_FCT, 
                    CTC_NOM, 
                    CTC_FAX, 
                    CTC_MAIL, 
                    CTC_GSM, 
                    CAST(ZONE_LIBRE AS TEXT) AS ZONE_LIBRE, 
                    CAST (COMMENT_INT AS TEXT) AS COMMENT_INT, 
                    CAST(TITRE_DA AS TEXT) AS TITRE_DA,  
                    OUPA_CENTRE_EMETTEUR AS CENTRE_EMETTEUR, 
                    ctr.RAIS_SOC AS LIBELLE_CENTRE_EMETTEUR,
                    (
                        select 
                            COUNT(*) 
                        from oxa_users_panier_ligne 
                        where oxa_users_panier.id_user = oxa_users_panier_ligne.id_user and oxa_users_panier.id_panier = oxa_users_panier_ligne.id_panier
                    ) nombreLigne 
                FROM oxa_users_panier WITH (NOLOCK) 
                LEFT JOIN SECTIMPU WITH (NOLOCK) ON SECTIMPU.CODE_SI = oxa_users_panier.SI_CODE AND SECTIMPU.CODE_STE = '.($siSecto? 'oxa_users_panier.CODE_STE':"''").'
                LEFT JOIN CODECOMP WITH (NOLOCK) ON CODECOMP.CODE_CDC = oxa_users_panier.CDC_CODE AND CODECOMP.CODE_STE = '.($cdcSecto? 'oxa_users_panier.CODE_STE':"''").'
                LEFT JOIN SSCPTANA  WITH (NOLOCK) ON SSCPTANA.CODE_SCA = oxa_users_panier.SCA_CODE AND SSCPTANA.CODE_STE = '.($scaSecto? 'oxa_users_panier.CODE_STE':"''").'
                LEFT JOIN CODGEST WITH (NOLOCK)  ON CODGEST.CODE_AFF = oxa_users_panier.CG_CODE AND CODGEST.CODE_SI = oxa_users_panier.SI_CODE AND CODGEST.CODE_STE = '.($siSecto? 'oxa_users_panier.CODE_STE':"''").' AND NUM_LOT = \'1\'
                LEFT JOIN CENTRE  WITH (NOLOCK) ON CENTRE.CODE_CTR = LIV_CTR
                LEFT JOIN CENTRE AS ctr  WITH (NOLOCK) ON ctr.CODE_CTR = OUPA_CENTRE_EMETTEUR 
                LEFT JOIN COUNTRY with (nolock) on COUNTRY.CODE_PAYS = LIV_PAYS
                WHERE oxa_users_panier.id_user = :idUser AND oxa_users_panier.id_panier = :idPanier';
        

        $arrayReq = [
            ':idUser' => $idUser,
            ':idPanier' => $idPanier
        ];

        $infoPanierEntete = $this->fetchAssocOne($req, $arrayReq);

        if ($infoPanierEntete) {
            $pos = strpos($infoPanierEntete['CHPSPECCLT'], '|dateperiode');
            if ($pos === false) {
            } else {
                $infoPanierEntete['CHPSPECCLT'] = substr($infoPanierEntete['CHPSPECCLT'], 0, $pos);
            }
        }
        
        return $infoPanierEntete;
    }

    /**
     * @param array<mixed> $id
     * @return array<mixed>
     */
    public function getListeFrsPanier($id)
    {
        $req = "select 
                    oxa_users_panier_four.*, 
                    FOURNISS.RS, FOURNISS.MNTMINFRA, FOURNISS.DEVISE DEV_FOU,FOURNISS.FRPOBLIG,
                    FOURNISS.RECEP_AUTO, FOURNISS.ARTFRPORT,
                    PXAFO.PU PRIX_ARTPORT,
                    (select COUNT(*) count from oxa_users_panier_ligne where id_user = oxa_users_panier_four.id_user and id_panier = oxa_users_panier_four.id_panier and CRITERE_REGROUP = oxa_users_panier_four.CRITERE_REGROUP and RECEP_AUTO = 0x54) artRecepAuto,
                    (select COUNT(BDD.NO_PAGE) from BDD with(nolock) where CODE_ART='Fournisseur' and BDD.CODE_FOU = oxa_users_panier_four.COD_FOURN and (NM_TEXTE <> 0 or NM_TEXTE <> null)) BDD_PRESENT
                from oxa_users_panier_four with (nolock)
                left join FOURNISS with (nolock) on FOURNISS.CODE = oxa_users_panier_four.COD_FOURN
                left join PXAFO	with (nolock) on PXAFO.QTE = '0' and CODE_FOU = FOURNISS.CODE and PXAFO.CODE_ART=FOURNISS.ARTFRPORT
                where id_user = :idUser and id_panier = :idPanier";

        $arrayReq = [
            ':idPanier' => $id['id_panier'],
            ':idUser'   => $id['id_user'],
        ];

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

    /**
     * @param array<mixed>|string $listeFrs
     * @return array<mixed>
     */
    public function getListeTypeAchatByFrs($listeFrs, ?string $typeAchatParDefaut = null, bool $obligOnly = false)
    {

        if (!$typeAchatParDefaut) {
            $typeAchatParDefaut = sql::paravrac($this->pdo, 'PARAMFOU', 'INITTAC');
        }

        $arrayReq = [
            ':typeAchat1' => $typeAchatParDefaut,
            ':typeAchat2' => $typeAchatParDefaut,
        ];

        $modeMultiLng = sql::paravrac($this->pdo, 'PARAMOXA', 'MULTILNGACTIF') == 'O';

        $req = "select 
                    FOURNISS.CODE, 
                    ISNULL(t.TRA_TAC, TYPEACHAT.LIB_TAC) LIB_TAC,
                    ISNULL(TACFOTAC, :typeAchat1) TACFOTAC,
                    ISNULL(TAC_TYFO.TACFODEF, 0x54) TACFODEF
                from FOURNISS with (nolock)
                left join TAC_TYFO with(nolock) on TAC_TYFO.TACFOFOU = FOURNISS.CODE ".($obligOnly? ' and TACFODEF = 0x54':'')."
                left join TYPEACHAT with (nolock) on TYPEACHAT.TYP_ACH = ISNULL(TACFOTAC, :typeAchat2)
                LEFT JOIN TRAD_TAC t WITH(NOLOCK) ON t.TYP_ACH = TYPEACHAT.TYP_ACH AND t.COD_LNG = ".($modeMultiLng? 'FOURNISS.COD_LNG':"''")."
                WHERE";

        if (is_array($listeFrs)) {
            $req .= $this->makeWhereIn('FOURNISS.CODE', $listeFrs, $arrayReq);

            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            $req .= " FOURNISS.CODE = :codeFrs";
            $arrayReq[':codeFrs'] = $listeFrs;
            
            return $this->fetchAssocOne($req, $arrayReq);
        }
    }

    /**
     * @return array<mixed>
     */
    public function getListeImputation(string $idPanier, string $idUser)
    {
        $siSecto = \Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE');

        $req = 'select 
                    oxa_users_panier_imputation.*,
                    SECTIMPU.SICODG,
                    SECTIMPU.IMPCODG
                from oxa_users_panier_imputation with (nolock)
                left join oxa_users_panier with (nolock) on oxa_users_panier.id_user = oxa_users_panier_imputation.id_user and oxa_users_panier.id_panier = oxa_users_panier_imputation.id_panier
                LEFT JOIN SECTIMPU WITH (NOLOCK) ON SECTIMPU.CODE_SI = oxa_users_panier_imputation.SI_CODE 
                    AND SECTIMPU.CODE_STE = '.($siSecto? 'oxa_users_panier.CODE_STE':"''").'
                where oxa_users_panier_imputation.id_user = :idUser and oxa_users_panier_imputation.id_panier = :idPanier';

        $arrayReq = [
            ':idPanier' => $idPanier,
            ':idUser'   => $idUser,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param string $centre
     * @return array<mixed>
     */
    public function getInfoCentre(string $centre)
    {
        $req = "SELECT 
                    CODE_CTR LIV_CTR,
                    ADRESSE LIV_ADR,
                    VILLE1 LIV_VILLE,
                    CPOSTAL LIV_CP,
                    PAYS LIV_PAYS,
                
                    CENTRE.CODE_STE,
                    CENTRE.TRS_INTER,
                    CENTRE.MOD_TRS,
                
                    ctc.CTC_NOM,
                    ctc.CTC_FCT,
                    ctc.CTC_FAX,
                    ctc.CTC_TEL,
                    ctc.CTC_GSM,
                    ctc.CTC_MAIL
                from CENTRE 
                OUTER  apply (
                    select TOP 1 * 
                    from CTC_CTR with(nolock)
                    WHERE CTC_CTR.COD_CTR = CENTRE.CODE_CTR
                    order by CTC_IND
                ) ctc
                where CENTRE.CODE_CTR = :centre
        ";

        $arrayReq = [
            ':centre'=> $centre,
        ];

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

    /**
     * @return array<mixed>
     */
    public function getListePanier()
    {
        $req = "select            
                    lib_panier,
                    flg_panier_modele,
                    id_panier,
                    (
                        select 
                            COUNT(*) 
                        from oxa_users_panier_ligne with (nolock)
                        where oxa_users_panier.id_user = oxa_users_panier_ligne.id_user and oxa_users_panier.id_panier = oxa_users_panier_ligne.id_panier
                    ) nombreLigne 
                FROM oxa_users_panier WITH (NOLOCK)
                where id_user = :idUser and id_panier != '0' order by upper(lib_panier)";

        $arrayReq = [
            ':idUser' => $_SESSION['id_user'],
        ];

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

    /**
     * @param string $codeFrs
     * @return array<mixed>
     */
    public function getPanierModeleForRegul($codeFrs)
    {
        $req = "SELECT id_panier, lib_panier from oxa_users_panier with (nolock) 
                where oxa_users_panier.id_user   =  :idUser
                and   oxa_users_panier.flg_panier_modele = 1 
                and   oxa_users_panier.id_panier > 0
                and exists (
                    select null from oxa_users_panier_ligne with (nolock) 
                    where oxa_users_panier_ligne.id_user   =  oxa_users_panier.id_user 
                    and   oxa_users_panier_ligne.id_panier =  oxa_users_panier.id_panier
                    and   (oxa_users_panier_ligne.COD_FOURN = :codeFrs or    oxa_users_panier_ligne.COD_FOURN = '')
                    and  not exists (select null from oxa_users_panier_ligne as buf_panier with (nolock)
                    where buf_panier.id_user    = oxa_users_panier_ligne.id_user  and   buf_panier.id_panier  = oxa_users_panier_ligne.id_panier
                    and   buf_panier.COD_FOURN <> oxa_users_panier_ligne.COD_FOURN and   buf_panier.COD_FOURN <> '')
                )";

        $arrayReq = [
            ':idUser' => $_SESSION['id_user'],
            ':codeFrs' => $codeFrs,
        ];

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