<?php

namespace Modules\Panier\Repository;

use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;

class PanierLigne extends AbsRepository
{
    public function getAllLignePanier($id_panier, $id_ligne = 0)
    {
        $req = "select   IDLIGNECAT IDLIGNE, PX_ART_INIT, TAUX_TVA,id_user,qte,CodeCtrLivraison,MODE_TRS,PROVENANCE,ID_CAT,COD_ART,COD_FOURN,RS_FOURN,REF_FOURN,COD_FAB,RS_FAB,COD_GAM,LIB_GAM,REF_FAB,
                COD_SFAM,LIB_SFAM,DES_ART,CAST(DES_COMP AS TEXT) AS DES_COMP, COD_UNIT,PX_ART,DELAI,CARACT,IMGART,TYPIMAGE, CONDITNT,TXTCOND,COD_ACD,flg_oxaproc_art,flg_oxaproc_frs,
                CODE_MET,CODE_SPE,type_de_ligne,flg_ligne_ok,SI_CODE,CG_CODE,CDC_CODE,SCA_CODE,COD_GRP,CODE_FM,REFLMAN,CLASSNUM,COEFCONV,budget_tot,id_lig,DATELIVR, CARACT_ART_HAP, NO_LIGNE_CA,
                FICHTECH, ARTASSOCIE, ASS_OBLIG, PRIXDEGRESSIF , ID_LIGNE_ASSOC , CATPRLST , COD_DEV , CRITERE_REGROUP, RECEP_AUTO, PER_REC_AUTO, NONRECEPT,FIN_DEB_PER,OUPL_RECEPAUTO_JOUR 
                FROM oxa_users_panier_ligne with (nolock) 
                WHERE id_user = :id_user and id_panier = :id_panier";
        
        $arrayReq = [
            ':id_user' => $_SESSION['id_user'],
            ':id_panier' => $id_panier,
        ];
        
        if (!$id_ligne) {
            $return = $this->fetchAssocAll($req, $arrayReq);
        } else {
            $req .= " AND id_lig = :idLigne ";
            $arrayReq[':idLigne'] = $id_ligne;
            
            $return = $this->fetchAssocOne($req, $arrayReq);
        }
        
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }
        
    public function updateLignePanierCtrLiv($id_user, $id_panier, $ctr_liv, $mode_trs = '', $id_ligne = 0)
    {
        $req = "update oxa_users_panier_ligne set CodeCtrLivraison = :ctrLivr, MODE_TRS =:modeTras
                where id_user = :id_user and id_panier = :id_panier";
        
        $arrayReq = [
            ':ctrLivr' => $ctr_liv,
            ':modeTras' => $mode_trs,
            ':id_user' => $id_user,
            ':id_panier' => $id_panier,
        ];
        
        if ($id_ligne != 0) {
            $req .= " and id_lig = :id_ligne";
            $arrayReq[':id_ligne'] = $id_ligne;
        }
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function getNbLignePanier($idPanier, $idUser):string
    {
        $req = "select COUNT(*) TOTAL from oxa_users_panier_ligne with (nolock)
                WHERE oxa_users_panier_ligne.id_user = :idUser AND   oxa_users_panier_ligne.id_panier = :idPanier ";
        
        $arrayReq = [
            ':idUser' => $idUser,
            ':idPanier' => $idPanier
        ];

        
        return $this->fetchAssocOne($req, $arrayReq)['TOTAL']??'0';
    }
    
    /**
     * @param string|int $idPanier
     * @param string|int $idUser
     * @param string|int $typeLigne
     * @param array<mixed> $array
     * @return array<mixed>
     * v1 : rqIsArticlePanier_1
     */
    public function articleExistPanier($idPanier, $idUser, $typeLigne, $array)
    {
        $req = "select 
                    id_lig, qte, PX_ART, NO_CDE,COD_DEV, COD_UNIT, COD_SFAM,CodeCtrLivraison, TAUX_TVA,CRITERE_REGROUP, COD_FOURN,
                    PRIXDEGRESSIF, TXTCOND,CONDITNT,PROVENANCE,ID_CAT, IDLIGNECAT,COD_ART, PX_ART_INIT, ARTASSOCIE
                from oxa_users_panier_ligne with (nolock) 
                WHERE oxa_users_panier_ligne.id_user        = :idUser
                AND   oxa_users_panier_ligne.id_panier   	= :idPanier
                AND   oxa_users_panier_ligne.COD_ART     	= :codeArt
                AND   oxa_users_panier_ligne.type_de_ligne  = :typeLigne
                AND   oxa_users_panier_ligne.COD_FOURN      = :codFourn
                AND   oxa_users_panier_ligne.REF_FOURN      = :refFourn
                AND   oxa_users_panier_ligne.COD_FAB        = :codFab
                AND   oxa_users_panier_ligne.REF_FAB        = :refFab                
                AND   oxa_users_panier_ligne.ID_CAT         = :idCat";

        $arrayReq = [
            ':idUser' => $idUser,
            ':idPanier' => $idPanier,
            ':codeArt' => $array['COD_ART']??'',
            ':typeLigne' => intval($typeLigne),

            ':codFourn' => $array['COD_FOURN']??'',
            ':refFourn' => $array['REF_FOURN']??'',
            ':codFab' => $array['COD_FAB']??'',
            ':refFab' => $array['REF_FAB']??'',
            ':idCat' => $array['ID_CAT']??'',
        ];

        if ($array['COD_ACD']) {
            $req .= " AND oxa_users_panier_ligne.COD_ACD = :acd ";
            $arrayReq[':acd'] = $array['COD_ACD'];
        }

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


    /**
     * @param string|int $idLig
     * @return array<mixed>
     */
    public function articleByLigne($idLig)
    {
        $req = "select 
                    id_lig, qte, PX_ART, NO_CDE,COD_DEV, COD_UNIT, COD_SFAM,CodeCtrLivraison, TAUX_TVA,CRITERE_REGROUP, COD_FOURN,
                    PRIXDEGRESSIF, TXTCOND,CONDITNT,PROVENANCE,ID_CAT, IDLIGNECAT,COD_ART, PX_ART_INIT, ARTASSOCIE,type_de_ligne,
                    case type_de_ligne
                    when 3 then (select FRPOBLIG from FOURNISS with (nolock) where CODE = oxa_users_panier_ligne.COD_FOURN )
                    else 0x46
                    end ART_OBLIG
                from oxa_users_panier_ligne with (nolock) 
                WHERE oxa_users_panier_ligne.id_lig = :idLig";
                
        $arrayReq = [
            ':idLig' => $idLig,
        ];
        
        $return = $this->fetchAssocOne($req, $arrayReq);
        $this->majForPanier($return);
        return $return;
    }
    
    /**
     * @param array<mixed> $array
     * @return array<mixed>
     */
    public function multiArticleExistPanier(string $idPanier, string $idUser, int $typeLigne, $array)
    {
        
        $arrayReq = [
            ':idUser' => $idUser,
            ':idPanier' => $idPanier,
            ':typeLigne' => intval($typeLigne),
        ];

        $req = "select 
                    id_lig, qte, COD_ART, COD_FOURN, REF_FOURN, COD_FAB, REF_FAB, ID_CAT, COD_ACD,
                    PX_ART,PX_ART_INIT,IDLIGNECAT, COD_ART, DES_ART,CodeCtrLivraison
                from oxa_users_panier_ligne with (nolock) 
                WHERE oxa_users_panier_ligne.id_user        = :idUser
                AND   oxa_users_panier_ligne.id_panier   	= :idPanier
                AND   oxa_users_panier_ligne.type_de_ligne  = :typeLigne";
    
        $where = '';
                
        $i = 0;
        foreach ($array as $line) {
            if ($where) {
                $where .= " OR ";
            }

            $where .="(oxa_users_panier_ligne.COD_ART     	    = :codeArt$i 
                    AND   oxa_users_panier_ligne.COD_FOURN      = :codFourn$i 
                    AND   oxa_users_panier_ligne.REF_FOURN      = :refFourn$i 
                    AND   oxa_users_panier_ligne.COD_FAB        = :codFab$i 
                    AND   oxa_users_panier_ligne.REF_FAB        = :refFab$i 
                    AND   oxa_users_panier_ligne.ID_CAT         = :idCat$i ";
    
    
            $arrayReq[':codeArt'.$i]   = $line['COD_ART']??'';
            $arrayReq[':codFourn'.$i]  = $line['COD_FOURN']??'';
            $arrayReq[':refFourn'.$i]  = $line['REF_FOURN']??'';
            $arrayReq[':codFab'.$i]    = $line['COD_FAB']??'';
            $arrayReq[':refFab'.$i]    = $line['REF_FAB']??'';
            $arrayReq[':idCat'.$i]     = $line['ID_CAT']??'';
    
            if ($line['COD_ACD']) {
                $where .= " AND oxa_users_panier_ligne.COD_ACD = :acd$i ";
                $arrayReq[':acd'.$i] = $line['COD_ACD'];
            }

            $where .= ")";

            $i++;
        }
        if ($where) {
            $req = $req. " and ( ".$where." )";

            $test = $req;
            foreach ($arrayReq as $key => $ff) {
                $test = str_replace($key.' ', "'".$ff."' ", $test);
                $test = str_replace($key.')', "'".$ff."')", $test);
            }
            
            $return = $this->fetchAssocAll($req, $arrayReq);

            if ($return) {
                foreach ($return as &$r) {
                    $this->majForPanier($r);
                }

                return $return;
            }
        }
        return [];
    }

    /**
     * @param array<mixed> $article
     */
    public static function majForPanier(&$article) :void
    {
        if ($article) {
            $article['PU']      = $article['PX_ART'];
            $article['PU_INIT'] = $article['PX_ART_INIT'];
    
            $article['IDLIGNE'] = $article['IDLIGNECAT'];
            $article['CODE_ART'] = $article['COD_ART'];
        }
    }

    /**
     * @return array<mixed>
     */
    public function isArtStkForCtrLiv(string $codeArt = null, string $centreLivr = null)
    {
        $req = "select count(*) TROUVE from SSTOCKLO with(nolock), SARTICLE with(nolock)
                where
                    SSTOCKLO.CODE_ART=SARTICLE.CODE_ART
                and SSTOCKLO.SITE= (select SITSTOCK from CENTRE with(nolock) where LIVRAISON = 0x54 and CODE_CTR = :centreLivr)
                and SSTOCKLO.CODE_ART= :codeArt
                and (SARTICLE.DTE_FERM='00000000' or SARTICLE.DTE_FERM >= '".date('Ymd')."')
                and (SSTOCKLO.DTE_FERM='00000000' or SSTOCKLO.DTE_FERM >= '".date('Ymd')."')";

        $arrayReq = [
            ':codeArt' => $codeArt??'',
            ':centreLivr'  => $centreLivr??'',
        ];

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

    /**
     * @return array<mixed>
     */
    public function getDateLigneFrs(string $codeFourn)
    {
        $req = "SELECT * from oxa_users_panier_ligne with(nolock)
                where oxa_users_panier_ligne.id_panier =   :idPanier
                and   oxa_users_panier_ligne.id_user   =   :idUser
                and   oxa_users_panier_ligne.COD_FOURN =   :codeFourn
                and   oxa_users_panier_ligne.DATELIVR  <>  '00000000' ";

        $arrayReq[':codeFourn'] = $codeFourn;
        $arrayReq[':idPanier'] = $_SESSION['id_panier'];
        $arrayReq[':idUser'] = $_SESSION['id_user'];

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

    /**
     * @return string|null
     */
    public function getLastIdCreate()
    {
        $req = "select max(id_lig) as LAST_LIGNE 
                from oxa_users_panier_ligne with (nolock) 
                where oxa_users_panier_ligne.id_user  = :idUser 
                and oxa_users_panier_ligne.id_panier 	= :idPanier";
        
        $arrayReq[':idPanier'] = $_SESSION['id_panier'];
        $arrayReq[':idUser'] = $_SESSION['id_user'];

        return $this->fetchAssocOne($req, $arrayReq)['LAST_LIGNE'];
    }

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

        $req = "select 
                oxa_users_panier_ligne.*, CENTRE.MOD_TRS, CENTRE.TRS_INTER, SECTIMPU.SICODG,
                CASE type_de_ligne WHEN 2 THEN 1 else 0 end fraisAnnexes
                from oxa_users_panier_ligne with (nolock)
                left join oxa_users_panier with (nolock) on oxa_users_panier.id_panier = oxa_users_panier_ligne.id_panier and oxa_users_panier.id_user = oxa_users_panier_ligne.id_user
                left join CENTRE with (nolock) on CENTRE.CODE_CTR = oxa_users_panier_ligne.CodeCtrLivraison
                left join SECTIMPU with (nolock) on SECTIMPU.CODE_SI = oxa_users_panier_ligne.SI_CODE AND SECTIMPU.CODE_STE = ".($siSecto? 'oxa_users_panier.CODE_STE':"''")."
                where oxa_users_panier_ligne.id_user = :idUser and oxa_users_panier_ligne.id_panier=:idPanier and CRITERE_REGROUP = :critere order by fraisAnnexes, id_lig";

        
        $arrayReq[':idPanier'] = $_SESSION['id_panier'];
        $arrayReq[':idUser'] = $_SESSION['id_user'];
        $arrayReq[':critere'] = $critere;

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

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

    /**
     * @return array<mixed>
     */
    public function getListeLignePanier()
    {
        $req = "select 
                (
                    select
                        COUNT(*) 
                    from FAVEPROC with(nolock)
                    where CODE_USER = :codeUser
                    and (
                        CODEFAB = olp.COD_FAB 
                        and REFARTFAB  = olp.REF_FAB 
                        and CODE_FOU = olp.COD_FOURN 
                        and REFARTFOU  = olp.REF_FOURN 
                        and CODE_ART = olp.COD_ART
                        and CODE_ACD  = olp.COD_ACD
                    )
                    
                ) art_fav,  

                case type_de_ligne
                when 3 then (select FRPOBLIG from FOURNISS with (nolock) where CODE = olp.COD_FOURN )
                else 0x46
                end

                ART_OBLIG,
                CASE type_de_ligne WHEN 2 THEN 1 else 0 end fraisAnnexes,
                olp.*, acc.LIBELLE LIB_ACD, ISNULL(u.NB_DECIM, 99) NB_DECIM
                from oxa_users_panier_ligne olp with (nolock)
                left join ACCCADRE acc with (nolock) on acc.CODE_ACD = olp.COD_ACD
                left join UNITES u with (nolock) on u.CODE_UNI = olp.COD_UNIT
                where olp.id_user = :idUser and olp.id_panier = :idPanier
                order by fraisAnnexes, id_lig";

        $arrayReq[':idPanier'] = $_SESSION['id_panier'];
        $arrayReq[':idUser'] = $_SESSION['id_user'];
        $arrayReq[':codeUser'] = $_SESSION["Session_Login"];

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

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

    /**
     * @return array<mixed>
     */
    public function getFiche(string $idLigne)
    {
        $req = "select 
        IDLIGNECAT,
        COD_ART,
        DES_ART,
        COD_GAM,
        LIB_GAM,
        REF_FAB,
        COD_SFAM,
        LIB_SFAM,
        DES_COMP,
        COD_UNIT,
        COD_ACD,
        PX_ART,
        TAUX_TVA TXTVA,
        DELAI,
        IMGART,
        COD_FAB,
        RS_FAB,
        FAMILLE.CODE_FAM CODE_FAM,
        FAMILLE.DESIGNAT DESIGNFAM,
        '' ZONEALPHA, -- existe pas
        oxa_users_panier_ligne.NONRECEPT,
        oxa_users_panier_ligne.RECEP_AUTO,
        (PX_ART * (1+(TAUX_TVA/100))) PX_ART_TTC,
        '' ACD_QTEDISPO,
        COD_DEV DEVISE,

        CARACT_ART_HAP,
        CodeCtrLivraison,
        CLASSNUM,
        ID_CAT,
        IDLIGNECAT,
        COEFCONV,
        COD_FOURN,
        REF_FOURN,
        qte

        from oxa_users_panier_ligne with (nolock)
        left join SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = COD_SFAM
        left join FAMILLE with (nolock) on FAMILLE.CODE_FAM = SSFAMILL.CODE_FAM
        where id_lig = :idLigne";

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

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

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

    /**
     * @return array<mixed>
     */
    public function getCaractInfoFromIdLigne(string $idLigne)
    {
        $req = "select
                CATCARACTINFO.ID_LIGNE, CATCARACTINFO.COD_ART, CATCARACTINFO.ID_CAR, CATCARACTINFO.TYP_CAR,
                CATCARACTINFO.COD_CAR, CATCARACTINFO.VAL_CAR, CATCARACTINFO.ISSU_CHARG, CATCARACTINFO.IDCATVER,
                
                case when ISNULL(TYPECARSEL.TITRE_CAR,'')<>'' then TYPECARSEL.TITRE_CAR else CATCARACTINFO.TITRE_CAR end TITRE_CAR
                
                from CATCARACTINFO with(nolock)
                LEFT JOIN TYPECARSEL with(nolock) on TYPECARSEL.COD_CAR = CATCARACTINFO.COD_CAR 
                where ID_LIGNE= :idLigne";
        
                $arrayReq[':idLigne'] = $idLigne;

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

    /**
     * @param string $idUser
     * @param string $idPanier
     * @param array<mixed> $listeLigne
     * @return array<mixed>
     */
    public function getMontantByCritere(string $idUser, string $idPanier, $listeLigne)
    {
        if (!$listeLigne) {
            return [];
        }

        $arrayReq = [];

        $req = "select 
                    oxa_users_panier_four.CRITERE_REGROUP,
                    montant.montantCritere,
                    montant.montantCritereTTC,
                    montantAnnexes.montantCritere montantAnnexesCritere,
                    montantAnnexes.montantCritereTTC montantAnnexesCritereTTC
                from oxa_users_panier_four with (nolock)
                OUTER APPLY (
                    select 		
                        CRITERE_REGROUP,
                        SUM(qte*PX_ART) montantCritere, 
                        SUM(qte*PX_ART * (1 + (TAUX_TVA/100))) montantCritereTTC 
                    from oxa_users_panier_ligne with (nolock)
                    where oxa_users_panier_four.id_panier = oxa_users_panier_ligne.id_panier
                    and  oxa_users_panier_four.id_user = oxa_users_panier_ligne.id_user
                    and  oxa_users_panier_four.CRITERE_REGROUP = oxa_users_panier_ligne.CRITERE_REGROUP
                    group by CRITERE_REGROUP
                ) montant
                OUTER APPLY (
                    select 		
                        CRITERE_REGROUP,
                        SUM(qte*PX_ART) montantCritere, 
                        SUM(qte*PX_ART * (1 + (TAUX_TVA/100))) montantCritereTTC 
                    from oxa_users_panier_ligne with (nolock)
                    where oxa_users_panier_four.id_panier = oxa_users_panier_ligne.id_panier
                    and  oxa_users_panier_four.id_user = oxa_users_panier_ligne.id_user
                    and  oxa_users_panier_four.CRITERE_REGROUP = oxa_users_panier_ligne.CRITERE_REGROUP
                    and oxa_users_panier_ligne.type_de_ligne = '2'
                    group by CRITERE_REGROUP
                ) montantAnnexes
                where id_user = :idUser 
                and id_panier = :idPanier 
                and oxa_users_panier_four.CRITERE_REGROUP in (
                    select 
                        CRITERE_REGROUP 
                    from oxa_users_panier_ligne with (nolock) 
                    where ".$this->makeWhereIn('id_lig', $listeLigne, $arrayReq)."
                )";

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

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

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

    /**
     * @param string $idCat
     * @param string|null $idLigneExlude
     * @return array<mixed>
     */
    public function getLigneByIdCat(string $idCat, string $idLigneExlude = null)
    {
        $req = "select 
                    * 
                from oxa_users_panier_ligne with(nolock) 	
                where   id_user   = :idUser
                    and id_panier = :idPanier
                    and oxa_users_panier_ligne.PROVENANCE != 'PO' 
                    and ID_CAT = :idCat";

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

        if ($idLigneExlude !== null) {
            $req .= " and id_lig != :idLigne";

            $arrayReq[':idLigne'] = $idLigneExlude;
        }

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

    /**
     * @param array<mixed> $arrayParm
     */
    public function reqDuplicateLigne($arrayParm = [], bool $dateLigne = false):string
    {
        $idUser         = $arrayParm['idUser'];
        $idPanier       = $arrayParm['idPanier'];
        $idLigne        = $arrayParm['idLigne'];

        $idUserCible    = $arrayParm['idUserCible']??null;
        $idPanierCible  = $arrayParm['idPanierCible']??null;
        
        $qte            = $arrayParm['qte']??null;

        $req = "
            insert into oxa_users_panier_ligne (
                id_user,
                id_panier,
                id_article,
                qte,
                CodeCtrLivraison,
                PX_ART,
                flg_oxaproc_art,
                flg_oxaproc_frs,
                PROVENANCE,
                OLD_ID_LIG,
                ID_CAT,
                COD_ART,
                COD_FOURN,
                REF_FOURN,
                COD_FAB,
                RS_FAB,
                REF_FAB,
                COD_UNIT,
                DES_ART,
                DES_COMP,
                TYPIMAGE,
                IMGART,
                CARACT,
                COD_SFAM,
                LIB_SFAM,
                COD_GAM,
                LIB_GAM,
                VILLE_FOU,
                COD_ACD,
                CARACT_ART_HAP,
                RS_FOURN,
                type_de_ligne,
                VILLE_FOURN,
                DELAI,
                TXTCOND,
                SI_CODE,
                CG_CODE,
                CDC_CODE,
                SCA_CODE,
                CODE_SPE,
                CODE_MET,
                flg_ligne_ok,
                COD_GRP,
                NO_CDE,
                CONDITNT,
                CLASSNUM,
                CODE_FM,
                REFLMAN,
                COEFCONV,
                CHPSPEC01,
                budget_tot,
                NO_LIGNE_CA,
                MODE_TRS,
                COD_UNIT_PO,
                COD_SFAM_PO,
                REF_FOURN_2,
                FICHTECH,
                ARTASSOCIE,
                PRIXDEGRESSIF,
                CATPRLST,
                ID_LIGNE_ASSOC,
                IDLIGNECAT,
                PX_ART_INIT,
                COD_DEV,
                CRITERE_REGROUP,
                RECEP_AUTO,
                NONRECEPT,
                ASS_OBLIG,
                TAUX_TVA,
                OUPL_RECEPAUTO_JOUR,
                ID_EXTERNE,

                DATELIVR,
                DATEDEBCONT,
                DATEFINCONT,
                PER_REC_AUTO
            )
            SELECT 
                ".($idUserCible===null? 'oxa_users_panier_ligne.id_user':$idUserCible).",
                ".($idPanierCible===null? 'oxa_users_panier_ligne.id_panier':$idPanierCible).",
                oxa_users_panier_ligne.id_article,
                ".($qte? $qte:'oxa_users_panier_ligne.qte').",
                oxa_users_panier_ligne.CodeCtrLivraison,
                oxa_users_panier_ligne.PX_ART,
                oxa_users_panier_ligne.flg_oxaproc_art,
                oxa_users_panier_ligne.flg_oxaproc_frs,
                oxa_users_panier_ligne.PROVENANCE,
                oxa_users_panier_ligne.id_lig, /* OLD_ID_LIG */
                oxa_users_panier_ligne.ID_CAT,
                oxa_users_panier_ligne.COD_ART,
                oxa_users_panier_ligne.COD_FOURN,
                oxa_users_panier_ligne.REF_FOURN,
                oxa_users_panier_ligne.COD_FAB,
                oxa_users_panier_ligne.RS_FAB,
                oxa_users_panier_ligne.REF_FAB,
                oxa_users_panier_ligne.COD_UNIT,
                oxa_users_panier_ligne.DES_ART,
                oxa_users_panier_ligne.DES_COMP,
                oxa_users_panier_ligne.TYPIMAGE,
                oxa_users_panier_ligne.IMGART,
                oxa_users_panier_ligne.CARACT,
                oxa_users_panier_ligne.COD_SFAM,
                oxa_users_panier_ligne.LIB_SFAM,
                oxa_users_panier_ligne.COD_GAM,
                oxa_users_panier_ligne.LIB_GAM,
                oxa_users_panier_ligne.VILLE_FOU,
                oxa_users_panier_ligne.COD_ACD,
                oxa_users_panier_ligne.CARACT_ART_HAP,
                oxa_users_panier_ligne.RS_FOURN,
                oxa_users_panier_ligne.type_de_ligne,
                oxa_users_panier_ligne.VILLE_FOURN,
                oxa_users_panier_ligne.DELAI,
                oxa_users_panier_ligne.TXTCOND,
                oxa_users_panier_ligne.SI_CODE,
                oxa_users_panier_ligne.CG_CODE,
                oxa_users_panier_ligne.CDC_CODE,
                oxa_users_panier_ligne.SCA_CODE,
                oxa_users_panier_ligne.CODE_SPE,
                oxa_users_panier_ligne.CODE_MET,
                oxa_users_panier_ligne.flg_ligne_ok,
                oxa_users_panier_ligne.COD_GRP,
                oxa_users_panier_ligne.NO_CDE,
                oxa_users_panier_ligne.CONDITNT,
                oxa_users_panier_ligne.CLASSNUM,
                oxa_users_panier_ligne.CODE_FM,
                oxa_users_panier_ligne.REFLMAN,
                oxa_users_panier_ligne.COEFCONV,
                oxa_users_panier_ligne.CHPSPEC01,
                oxa_users_panier_ligne.budget_tot,
                0,
                oxa_users_panier_ligne.MODE_TRS,
                oxa_users_panier_ligne.COD_UNIT_PO,
                oxa_users_panier_ligne.COD_SFAM_PO,
                oxa_users_panier_ligne.REF_FOURN_2,
                oxa_users_panier_ligne.FICHTECH,
                oxa_users_panier_ligne.ARTASSOCIE,
                oxa_users_panier_ligne.PRIXDEGRESSIF,
                oxa_users_panier_ligne.CATPRLST,
                oxa_users_panier_ligne.ID_LIGNE_ASSOC,
                oxa_users_panier_ligne.IDLIGNECAT,
                oxa_users_panier_ligne.PX_ART_INIT,
                oxa_users_panier_ligne.COD_DEV,
                oxa_users_panier_ligne.CRITERE_REGROUP,
                oxa_users_panier_ligne.RECEP_AUTO,
                oxa_users_panier_ligne.NONRECEPT,
                oxa_users_panier_ligne.ASS_OBLIG,
                oxa_users_panier_ligne.TAUX_TVA,
                oxa_users_panier_ligne.OUPL_RECEPAUTO_JOUR,
                oxa_users_panier_ligne.ID_EXTERNE,

                ".($dateLigne? "oxa_users_panier_ligne.DATELIVR":"ISNULL(oxa_users_panier_four.DATELIVR, oxa_users_panier_ligne.DATELIVR)").",
                ".($dateLigne? "oxa_users_panier_ligne.DATEDEBCONT":"ISNULL(oxa_users_panier_four.DATEDEBCONT, oxa_users_panier_ligne.DATEDEBCONT)").",
                ".($dateLigne? "oxa_users_panier_ligne.DATEFINCONT":"ISNULL(oxa_users_panier_four.DATEFINCONT, oxa_users_panier_ligne.DATEFINCONT)").",
                CASE WHEN ISNULL(oxa_users_panier_four.PER_REC_AUTO, '') = '' THEN oxa_users_panier_ligne.PER_REC_AUTO else oxa_users_panier_four.PER_REC_AUTO end
            FROM oxa_users_panier_ligne
            LEFT JOIN oxa_users_panier_four 
                on oxa_users_panier_four.id_panier = oxa_users_panier_ligne.id_panier 
                and oxa_users_panier_four.id_user = oxa_users_panier_ligne.id_user
                and oxa_users_panier_four.CRITERE_REGROUP = oxa_users_panier_ligne.CRITERE_REGROUP
        where oxa_users_panier_ligne.id_user = :idUser and oxa_users_panier_ligne.id_panier = :idPanier and id_lig = :idLigne";

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

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return (string)Sql::getLastInsertId($this->pdo, 'oxa_users_panier_ligne', 'id_lig');
    }

    /**
     * @param array<mixed> $arrayParm
     * @return void
     */
    public function reqDuplicateCsLigne($arrayParm)
    {
        $idUser         = $arrayParm['idUser'];
        $idPanier       = $arrayParm['idPanier'];
        $idLigne        = $arrayParm['idLigne'];
        
        $newLigne       = $arrayParm['newLigne'];

        $idUserCible    = $arrayParm['idUserCible']??null;
        $idPanierCible  = $arrayParm['idPanierCible']??null;

        $req = "insert oxa_users_panier_CS_DON (
                    id_user, 
                    id_panier, 
                    NUM_FIX, 
                    VALEUR, 
                    ID_LIGNE, 
                    COD_TAB
                )
                select 
                    ".($idUserCible===null? 'id_user':$idUserCible).",
                    ".($idPanierCible===null? 'id_panier':$idPanierCible).",
                    NUM_FIX, 
                    VALEUR, 
                    ".$newLigne.", 
                    COD_TAB 
                from oxa_users_panier_CS_DON
                where id_user = :idUser and id_panier = :idPanier and ID_LIGNE = :oldLine";

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

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

    /**
     * @param string $idUser
     * @param string $idPanier
     * @return array<mixed>
     */
    public function rqListArtAssocWithNewId($idUser, $idPanier)
    {
        $req = "select DISTINCT ID_LIGNE_ASSOC, 
                (	select id_lig 
                    from oxa_users_panier_ligne artOrigin 
                    where artOrigin.OLD_ID_LIG =  artAssoc.ID_LIGNE_ASSOC and artOrigin.id_panier = artAssoc.id_panier and artOrigin.id_user = artAssoc.id_user
                ) NEW_ID_LIGNE_ASSOC
                from oxa_users_panier_ligne artAssoc 
                where id_user = :idUser and id_panier = :idPanier and ID_LIGNE_ASSOC <> 0 ";


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

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

    /**
     * @param string $idPanier
     * @param string $critere
     * @param string $codeFou
     * @return array<mixed>
     */
    public function listePanierMobilite(string $idPanier, string $critere = null, string $codeFou = null)
    {
        $siSecto  = \Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE');
        $cdcSecto = \Helpers\Outil\Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE');
        $scaSecto = \Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE');

        $req = "select 
                oxa_users_panier_ligne.*,
                SECTIMPU.LIBELLE as LIBELLE_SI, 
                CODECOMP.LIBELLE as LIBELLE_CDC,
                SSCPTANA.LIBELLE as LIBELLE_SCA, 
                CODGEST.LIB_GES as LIBELLE_CG, 
                CENTRE.RAIS_SOC as LIBELLE_CTR_LIV,

                oxa_users_panier_four.CODE_PAYS FOU_CODE_PAYS,
                oxa_users_panier_four.CPOSTAL FOU_CPOSTAL, 
                oxa_users_panier_four.VILLE1 FOU_VILLE1, 
                oxa_users_panier_four.ADR FOU_ADR

                from oxa_users_panier_ligne with (nolock)
				left join oxa_users_panier with (nolock)
                    on oxa_users_panier.id_user = oxa_users_panier_ligne.id_user
                    and oxa_users_panier.id_panier = oxa_users_panier_ligne.id_panier
                left join oxa_users_panier_four with (nolock) 
                    on oxa_users_panier_four.id_user = oxa_users_panier_ligne.id_user
                    and oxa_users_panier_four.id_panier = oxa_users_panier_ligne.id_panier
                    and oxa_users_panier_four.COD_FOURN = oxa_users_panier_ligne.COD_FOURN
                    and oxa_users_panier_four.CRITERE_REGROUP = oxa_users_panier_ligne.CRITERE_REGROUP
                left join SECTIMPU with (nolock) 
                    on SECTIMPU.CODE_SI = oxa_users_panier_ligne.SI_CODE 
                    and SECTIMPU.CODE_STE = ".($siSecto? 'oxa_users_panier.CODE_STE':"''")."
                left join CODECOMP with (nolock) 
                    on CODECOMP.CODE_CDC = oxa_users_panier_ligne.CDC_CODE 
                    and CODECOMP.CODE_STE = ".($cdcSecto? 'oxa_users_panier.CODE_STE':"''")."
                left join SSCPTANA  with (nolock) 
                    on SSCPTANA.CODE_SCA = oxa_users_panier_ligne.SCA_CODE 
                    and SSCPTANA.CODE_STE = ".($scaSecto? 'oxa_users_panier.CODE_STE':"''")."
                left join CODGEST  with (nolock) 
                    on CODGEST.CODE_AFF = oxa_users_panier_ligne.CG_CODE 
                    and CODGEST.CODE_STE = ".($siSecto? 'oxa_users_panier.CODE_STE':"''")." 
                    and CODGEST.CODE_SI = SECTIMPU.CODE_SI 
                    and CODGEST.NUM_LOT = 1
                left join CENTRE  with (nolock) 
                    on CENTRE.CODE_CTR = CodeCtrLivraison 
                where 
                        oxa_users_panier_ligne.id_user   = :idUser
                and     oxa_users_panier_ligne.id_panier = :idPanier";

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

        if ($critere) {
            $req .= " and oxa_users_panier_ligne.CRITERE_REGROUP = :critere";
            $arrayReq[':critere'] = $critere;
        }
        if ($codeFou) {
            $req .= " and oxa_users_panier_ligne.COD_FOURN = :codeFou";
            $arrayReq[':codeFou'] = $codeFou;
        }

        $req .= " order by id_lig";

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


    public function majBudgetLignePanier(string $taux):void
    {
        $req = 'update oxa_users_panier_ligne 
                set budget_tot  = budget_tot * '.(float)$taux.' 
                where id_user   = :idUser and id_panier = :idPanier';
        
        $param = [
            'idPanier' => $_SESSION['id_panier'],
            'idUser' => $_SESSION['id_user']
        ];

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