<?php

namespace Modules\RechercheArticles\Repository;

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

class Recherche extends AbsRepository
{
    public function generateProfilPub(PDO $pdoOxaFt, string $user, string $ctrLivraison, string $ctrEmetteur, bool $force = false) : string
    {
        if (!isset($_SESSION['V2profilpub'])
            || $force
            || ($_SESSION['V2profilpub']['centreLiv'] != $ctrLivraison)
            || ($_SESSION['V2profilpub']['centreEmetteur'] != $ctrEmetteur)
        ) {
            $req = "exec Oxaproc_profilpub2 :user,:livraison,:emetteur, :bdd, :table";
            $arrayReq = [
                ':user' => $user,
                ':livraison' => $ctrLivraison,
                ':emetteur' => $ctrEmetteur,
                ':bdd' => '',
                ':table' => '',
                //':bdd' => ((new ParseBase1())->getOxaFT()->Server==(new ParseBase1())->getOxaSql()->Server? '':(new ParseBase1())->getOxaFT()->Server),
                //':table' => (new ParseBase1())->getOxaFT()->Name,
            ];
            
            $requete = $pdoOxaFt->prepare($req);
            $requete->execute($arrayReq);
            
            /**
             * BBA : Solution de contournemant,
             * mais il faudra revoir la procedure stockée Oxaproc_profilpub2
             * pour enlever le select dedans
             */
            $req = "SELECT string_agg(PROFIL,',') PROFIL 
                    FROM PROFILPUB with (nolock) 
                    WHERE CODE_USER = :user";
  
            $arrayReq = [
                ':user' => $user
            ];

            $requete = $pdoOxaFt->prepare($req);
            $requete->execute($arrayReq);
            $retour = $requete->fetch()['0']??'';

            if ($retour == '0') {
                $retour = '99999';
            }
            
            $_SESSION['V2profilpub'] = [
                'centreLiv'         => $ctrLivraison,
                'centreEmetteur'    => $ctrEmetteur,
                'perimetre'         => $retour
            ];
        }

        return $_SESSION['V2profilpub']['perimetre'];
    }

    /**
     * @param string $ctrLivraison
     * @return string
     */
    public function generateProfilCtr(PDO $pdoOxaFt, string $ctrLivraison, bool $force = false)
    {
        if (!isset($_SESSION['V2profilctr'])
            || $force
            || $_SESSION['V2profilctr']['centre'] != $ctrLivraison
        ) {
            $req = "exec Oxaproc_profilctr2 :livraison, :bdd, :table";
            $arrayReq = [
                ':livraison' => $ctrLivraison,
                ':bdd' => '',
                ':table' => '',
                //':bdd' => ((new ParseBase1())->getOxaFT()->Server==(new ParseBase1())->getOxaSql()->Server? '':(new ParseBase1())->getOxaFT()->Server),
                //':table' => (new ParseBase1())->getOxaFT()->Name,
            ];
            
            $requete = $pdoOxaFt->prepare($req);
            $requete->execute($arrayReq);

            $req = "SELECT string_agg(PROFIL,',') PROFIL 
                    from PROFILCTR with (nolock)
                    where CODE_CTR = :livraison";

            $arrayReq = [
                ':livraison' => $ctrLivraison
            ];

            $requete = $pdoOxaFt->prepare($req);
            $requete->execute($arrayReq);
            $retour = $requete->fetch()['0']??'';

            $_SESSION['V2profilctr'] = [
                'centre' => $ctrLivraison,
                'perimetre' =>  $retour,
            ];
        }
        
        return $_SESSION['V2profilctr']['perimetre'];
    }
    
    /**
     * @return array<mixed>
     */
    public function getFrsLigne(string $sfam = '')
    {
        $clauseSfam = '';

        if ($sfam) {
            $param = Sql::paravrac($this->pdo, 'OXAPROC', 'ARTGEN_LISTEFOURN_LIMITEE');
            $droit = Roles::droitsUtilisateur($this->pdo, 'OX_AG_LF_L');

            if ($param == 'O' && !$droit) {
                $clauseSfam = " and oupl.COD_FOURN in (
                    select sf.CODE_FOUR from SSFAMFOU sf with (nolock)
                    Left outer join FOURNISS f with(nolock) on f.CODE=sf.CODE_FOUR
                    where  f.DTEVALMIN<='".date('Ymd')."' 
                    AND (f.DTEVALMAX='00000000' OR f.DTEVALMAX>='".date('Ymd')."') 
                    and CODE_SFAM = :sfam
                        union 
                    select CODE CODE_FOUR from FOURNISS with(nolock) where CODE not in (select CODE_FOUR from SSFAMFOU sf)
                )";

                $arrayReq[':sfam'] = $sfam;
            }
        }

        $req = "select 
                    oupl.COD_FOURN,oupl.CRITERE_REGROUP,oupl.COD_DEV, FOURNISS.RS
                from oxa_users_panier_ligne oupl with(nolock) 
                left join FOURNISS with (nolock) on FOURNISS.CODE = COD_FOURN
                where oupl.id_panier = :idPanier and oupl.id_user = :idUser $clauseSfam
                group by COD_FOURN,CRITERE_REGROUP,COD_DEV,FOURNISS.RS";
        
        $arrayReq[':idUser'] = $_SESSION['id_user'];
        $arrayReq[':idPanier'] = $_SESSION['id_panier'];

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


    /**
     * @param array<mixed>|string $codeFou
     * @return array <mixed>
     */
    public function getInfoFrsArticle($codeFou = [])
    {
        $req = "select CODE, ISNULL(PXAFO.PU, ARTICLES.PRXPLBAS) PRXPLBAS,MNTMINFRA, FRPOBLIG, ARTFRPORT, DEVISE from FOURNISS with (nolock)
                left join ARTFOURN with (nolock) on ARTFOURN.CODE_ART = FOURNISS.ARTFRPORT AND ARTFOURN.FOU= FOURNISS.CODE
                left join PXAFO with (nolock) on PXAFO.CODE_FOU = FOURNISS.CODE AND PXAFO.CODE_ART =FOURNISS.ARTFRPORT and QTE = 0
                left join ARTICLES with (nolock) on ARTICLES.CODE_ART = FOURNISS.ARTFRPORT
                where 1=1";

        if (is_array($codeFou)) {
            $arrayReq = [];
            $req .= " and ".$this->makeWhereIn('CODE', $codeFou, $arrayReq);

            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            $req .= " and CODE = :codeFou";

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

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

    /**
     * @param array<mixed> $acd
     * @return array<array<mixed>>
     */
    public function getListeAcd($acd = [])
    {
        if (empty($acd)) {
            return [];
        }
        $req = "select * from ACCCADRE with (nolock) where ";

        $arrayReq = [];
        $req .= $this->makeWhereIn('CODE_ACD', $acd, $arrayReq);

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

    /**
     * @param array<mixed> $listeRayon
     * @return array<mixed>
     */
    public function getListeRayon(array $listeRayon = [])
    {
        if (empty($listeRayon)) {
            return [];
        }

        $param = [':langue' => $_SESSION['Session_Langue']];
        $req = "SELECT 
                    RAYON.IDRAYON, RAYON.RAYCOULEUR,RAYON.AUTHCLIC,
                    RAYON.NIVRAYON,RAYON.RAYPERE,RAYON.ORDEAFF, 
                    isnull(RAYON_TRAD.LIBRAYON, RAYON.LIBRAYON) LIBRAYON,
                    isnull(MEMOS.CONTMEMO,RAYON.TXT_COMMENT) CONTMEMO,
                    HASTUTIL.NOM, HASTUTIL.TEL,
                    prec.RAYPERE as 'RAYON_N-1', 
                    (select COUNT(*) from RAYON cr with (nolock) where cr.RAYPERE = RAYON.IDRAYON) countSubRayon 
                from CAT_RAYON RAYON with (nolock) 
                left join CAT_RAYON prec with (nolock) on prec.IDRAYON = RAYON.RAYPERE
                left join HASTUTIL with (nolock) on HASTUTIL.CODE = RAYON.ACHAFFICHE
                left join RAYON_TRAD with (nolock) on 
                    RAYON_TRAD.IDRAYON = RAYON.IDRAYON COLLATE French_BIN 
                    and RAYON_TRAD.COD_LNG = :langue
                left join MEMOS with (nolock) on MEMOS.NODEMEMO = RAYON_TRAD.NM_COMMENT
                where ".$this->makeWhereIn('RAYON.IDRAYON', $listeRayon, $param)."
                order by RAYON.NIVRAYON, RAYON.RAYPERE,  RAYON.ORDEAFF";
                
        return $this->fetchAssocAll($req, $param);
    }

    /**
     * @param array<mixed> $listeRayon
     * @return array<mixed>
     */
    public function getListeRayonMobilite(string $idRayon, array $listeRayon = [])
    {
        $param[':idRayon'] = $idRayon;
        $req = "with LstRayon(idRayon, libRayon, niveau) as
                (
                    select rayon.IDRAYON, rayon.LIBRAYON, rayon.NIVRAYON from RAYON rayon 
                    where rayon.IDRAYON =  :idRayon
                    union all
                    select rayon2.IDRAYON,rayon2.LIBRAYON, rayon2.NIVRAYON
                    from RAYON rayon2 
                    inner join LstRayon l  on rayon2.RAYPERE=l.idRayon
                )select * from LstRayon where ".$this->makeWhereIn('idRayon', $listeRayon, $param);

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

    /**
     * @return array<mixed>
     */
    public function getListeTypeCarSelect()
    {
        $req = "select * from TYPECARSEL with(nolock) order by ORDRE_CAR";

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

    /**
     * @return array<mixed>
     */
    public function getListeCatalogue(string $user, string $listePublication)
    {

        $req = "SELECT distinct CATAPRIVE.ID_CATPR,NCATAPR,DESCRCATA, FAVEPUBLI.CODE_USER, URLIMG, SRCDATA, PDF_URL
                from CATAPRIVE with (nolock) 
                left join CPSTRUCT with (nolock)  on CPSTRUCT.ID_CATPR = CATAPRIVE.ID_CATPR
                left join FAVEPUBLI with (nolock) on FAVEPUBLI.ID_CATPR = CATAPRIVE.ID_CATPR AND FAVEPUBLI.CODE_USER = :user
                where 
                -- CATAPRIVE.ID_CATPR in (select PROFIL from PROFILPUB with (nolock) where CODE_USER = :user2) AND
                CATAPRIVE.VALIDU <= '".date('Ymd')."'  and (CATAPRIVE.VALIDAU >= '".date('Ymd')."' or CATAPRIVE.VALIDAU = '00000000')

                ".($listePublication? " AND CATAPRIVE.ID_CATPR in (".$listePublication.")":'') // TODO : prevent SQL injection
                ."
                ORDER BY FAVEPUBLI.CODE_USER desc,NCATAPR asc";

        $arrayReq[':user'] = $user;
        $arrayReq = [
            ':user' => $user,
            //':user2' => $user,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getRayonUser(PDO $pdoOxaFT, string $user, string $centre, string $siteStock)
    {
        $req = "EXEC Oxaproc_RayArtFreeRankDes :user,:centre,:siteStock,0, 'CATAPROC','','',''";

        $arrayReq = [
            ':user' => $user,
            ':centre' => $centre,
            ':siteStock' => $siteStock,
        ];
        
        $requete = $pdoOxaFT->prepare($req);
        $requete->execute($arrayReq);

         //! requete no_fetchAssocOne
        $return = $requete->fetchAll($this->pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * @return array<mixed>
     */
    public function getRayonGenUser(PDO $pdoOxaFT, string $user, string $langue)
    {
        $req = "EXEC Oxaproc_GenRayFreeRankDes :user,:langue,'','','',''";

        $arrayReq = [
            ':user' => $user,
            ':langue' => $langue,
        ];
        
        $requete = $pdoOxaFT->prepare($req);
        $requete->execute($arrayReq);
        
         //! requete no_fetchAssocOne
        $return = $requete->fetchAll($pdoOxaFT::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * @param bool|null $all
     * @return array<mixed>
     */
    public function getParamFicheArticle($all = false)
    {
        $req = "SELECT * from PARFICHE with(nolock)
            WHERE CODE_TAB ='ART'
            ".($all? '': "and CODE_CHP not in ('COD_ART', 'DES_ART', 'DES_COMP', 'PX_ART', 'IMGART') ")."
            and NUM_CHP != '0'
            order by NUM_CHP";

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

    /**
     * @return array<mixed>
     */
    public function getCaracteristique()
    {
        $req = "select CAR_ID AS id_cat,CAR_IMG AS image,CAR_DES as designation from CAR_ART with (nolock)";

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

    /**
     * @return array<mixed>
     */
    public function getParamFicheFournisseur()
    {
        $req = "SELECT * from PARFICHE with(nolock)
            WHERE CODE_TAB ='FOURN'
            and CODE_CHP not in ('CODE', 'RS') and NUM_CHP != '0'
            order by NUM_CHP";

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

    /**
     * @return array<mixed>
     */
    public function getParamContact()
    {
        $req = "SELECT * from PARFICHE with(nolock)
            WHERE CODE_TAB ='CTCFOU' and NUM_CHP != '0'
            order by NUM_CHP";

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

    /**
     * @return array<mixed>
     */
    public function getBDDFrs(string $codeFou)
    {
        $req = "select 
                    BDD.*,CAST(MEMOS.CONTMEMO AS TEXT) AS CONTMEMO 
                from BDD with(nolock)
                left outer join MEMOS with(nolock) ON MEMOS.NODEMEMO = BDD.NM_TEXTE
	            WHERE BDD.NM_TEXTE > 0 AND BDD.VEPROC = 0x54 
                AND BDD.CODE_FOU = :codeFou AND CODE_ART = 'Fournisseur'";

        $arrayReq = [
            ':codeFou' => $codeFou,
        ];
    
        $multiLangue = Sql::paravrac($this->pdo, 'PARAMOXA', 'MULTILNGACTIF');

        if ($_SESSION['Session_Langue'] != '' && $multiLangue == 'O') {
            $req .= " AND LANGUE = :langue";
            $arrayReq[':langue'] = $_SESSION['Session_Langue'];
        }

        $req .= ' ORDER BY BDD.ONGLET, BDD.NUMORDRE ';
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function zoneGeoActive(string $centre)
    {
        $req = "select count(1) active from PROFILCTR with (nolock) WHERE CODE_CTR = :centre AND PROFIL <> ''";

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

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

    /**
     * @return array<mixed>
     */
    public function synonymeActif(PDO $pdoOxaFT)
    {
        $req = "select TOP 1 CHAINE from SYNONYMES with (nolock) ";
        
        $requete = $pdoOxaFT->prepare($req);
        $requete->execute([]);

         //! requete no_fetchAssocOne
        $return = $requete->fetch($this->pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    public function getSynonyme(PDO $pdoOxaFT, string $text) : string
    {
        $req = "select TOP 1 CHAINE from SYNONYMES with (nolock) where UPPER(REFERENCE) = UPPER(:text) and LANGUE = :langue";

        $arrayReq = [
            ':text' => $text,
            ':langue' => $_SESSION['Session_Langue'],
        ];

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

         //! requete no_fetchAssocOne
        $return = $requete->fetch($this->pdo::FETCH_ASSOC);
        if ($return) {
            return $return['CHAINE']??'';
        } else {
            return '';
        }
    }
}
