<?php

namespace Modules\Panier\Repository;

use Entity\Model\Fourniss;
use Entity\Model\Paravrac;
use Helpers\QueryBuilder\AbsRepository;

class ArticlePanier extends AbsRepository
{
    /**
     * @return array<mixed>
     */
    public function getListeArticlesAnnexes()
    {
        $req = "SELECT 
                    ARTICLES.SSFAM,ARTICLES.CODE_ART, 
                    case when TRAD_ART.DES_ART != '' then TRAD_ART.DES_ART else ARTICLES.DESIGN end DESIGN,ARTICLES.UNIT 
                FROM ARTICLES with (nolock)  
                left outer join TRAD_ART with(nolock) on TRAD_ART.COD_ART = ARTICLES.CODE_ART and TRAD_ART.COD_LNG = '{$_SESSION['Session_Langue']}'
                WHERE ARTICLES.CODE_ART IN (
                    SELECT ARTFAN.COD_ART  FROM ARTFAN with (nolock)
                )";

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

    /**
     * @param string $codeArt
     * @return array<mixed>
     */
    public function getArticlesFraisAnnexes(string $codeArt)
    {
        $req = " SELECT 
                    ARTICLES.CODE_ART, ARTICLES.SSFAM , ARTICLES.UNIT, ARTICLES.CODEFAB, ARTICLES.CODEGAM, ARTICLES.REFARTFAB, 
                    case when TRAD_ART.DES_ART != '' then TRAD_ART.DES_ART else ARTICLES.DESIGN end DESIGN, 
                    SSFAMILL.DESIGNAT as LIB_SFAM, GAMMES.DESIGN as RS_GAM, FABRICAN.RS as RS_FAB 
                from ARTICLES with(nolock)
                left outer join TRAD_ART with(nolock) on TRAD_ART.COD_ART = ARTICLES.CODE_ART and TRAD_ART.COD_LNG = '{$_SESSION['Session_Langue']}' 
                left outer join SSFAMILL with(nolock) on ARTICLES.SSFAM = SSFAMILL.CODE_SFA
                left outer join GAMMES with(nolock)   on ARTICLES.CODEGAM = GAMMES.CODEGAMME and ARTICLES.CODEFAB = GAMMES.CODEFABR 
                left outer join FABRICAN with(nolock) on ARTICLES.CODEFAB = FABRICAN.CODE 
                WHERE ARTICLES.CODE_ART = :codeArt;
            ";

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

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

    /**
     * Undocumented function
     *
     * @param array<mixed> $article_trouve
     * @param array<mixed> $infoLigne
     * @return array<mixed>
     */
    public function getListeArtAssocier($article_trouve, $infoLigne)
    {
        $sql = '';
        $arrayReq = [];
        
        // ‘R’ = Rayon : associé au rayon de rattachement de la ligne
        $in = '';

        $i=0;
        foreach ($article_trouve['RAYON']??[] as $rayon) {
            if ($rayon != '') {
                $in .= ($in == '' ? '' : ',') . ":rayon".$i."";
                $arrayReq[':rayon'.$i] = $rayon;
            }
            $i++;
        }
        
        if ($in != '') {
            $sql .= "SELECT T_OBJ_ASS,ID_OBJ_ASS,-1 AS COEFF,OBLIG  FROM ART_ASSOC with(nolock) WHERE T_ASS = 'R' AND ID_OBJ IN ($in) ";
        }
        
        // ‘F’ = Fournisseur : associés au fournisseur de la ligne
        if ($article_trouve['COD_GRP'] == '') {
            if ($article_trouve['COD_FOURN'] != '') {
                $sql .= ($sql == '' ? '' : ' UNION ') . "SELECT T_OBJ_ASS,ID_OBJ_ASS,-1 AS COEFF,OBLIG  FROM ART_ASSOC with(nolock) WHERE T_ASS = 'F' AND ID_OBJ = :idFourn ";
                $arrayReq[':idFourn'] = $article_trouve['COD_FOURN'];
            }
        }
        
        // ‘S’ = Sous famille : associés à la sous famille de la ligne
        if ($article_trouve['COD_SFAM'] != '') {
            $sql .= ($sql == '' ? '' : ' UNION ') . "SELECT T_OBJ_ASS,ID_OBJ_ASS,-1 AS COEFF,OBLIG  FROM ART_ASSOC with(nolock) WHERE T_ASS = 'S' AND ID_OBJ = :ssFamille ";
            $arrayReq[':ssFamille'] = $article_trouve['COD_SFAM'];
        }
        
        // ‘G’ = Groupement fournisseurs : associés au groupement de rattachement du fournisseur de la ligne
        
        $fourniss = new Fourniss($this->pdo);
        $fournisseur = $fourniss->find($article_trouve['COD_FOURN'], ['CODE', 'FOURPRIN', 'CODE_REGR']);
        
        if ($fournisseur) {
            // echo '';
            if ($fournisseur['CODE_REGR'] == '') {
                $sql .= ($sql == '' ? '' : ' UNION ') . "SELECT T_OBJ_ASS,ID_OBJ_ASS,      COEFF,OBLIG  FROM ART_ASSOC with(nolock) WHERE T_ASS = 'F' AND ID_OBJ = :codeFourn ";
                $arrayReq[':codeFourn'] = $fournisseur['CODE'];
            } elseif ($fournisseur['FOURPRIN'] != '') {
                $sql .= ($sql == '' ? '' : ' UNION ') . "SELECT T_OBJ_ASS,ID_OBJ_ASS,-1 AS COEFF,OBLIG  FROM ART_ASSOC with(nolock) WHERE T_ASS = 'G' AND ID_OBJ = :FOURPRIN ";
                $arrayReq[':FOURPRIN'] = $fournisseur['FOURPRIN'];
            }
        }
        // Les articles associés peuvent être de 2 types
        // - Article HA Plus : ART_ASSOC.T_OBJ_ASS=’A’
        $req = '';
        if ($infoLigne['PROVENANCE'] == 'HAP') {
            $sql .= ($sql == '' ? '' : ' UNION ') . "SELECT T_OBJ_ASS,ID_OBJ_ASS,COEFF,OBLIG  FROM ART_ASSOC with(nolock) WHERE ID_OBJ     = :COD_ART AND   T_ASS  = 'A'";
            $arrayReq[':COD_ART'] = $article_trouve['COD_ART'];
        } else {
            // NE PAS TOUCHER L'histoire du code de regroupement car il semble que tous soit un peu faux
            // Il faut donc chercher sur le code du fournisseur ou blanc sinon il arrive des fois que rien n'en sorte
            $identifiant1 = $infoLigne['ID_CAT'] . '|' . $infoLigne['COD_ART'] . '|' . '' . '|' . $infoLigne['REF_FOURN'] . '|' . $infoLigne['COD_FAB'] . '|' . $infoLigne['REF_FAB'];
            $identifiant2 = $infoLigne['ID_CAT'] . '|' . $infoLigne['COD_ART'] . '|' . $infoLigne['COD_FOURN'] . '|' . $infoLigne['REF_FOURN'] . '|' . $infoLigne['COD_FAB'] . '|' . $infoLigne['REF_FAB'];
            $req .= " WHERE (ID_OBJ = :id1 OR ID_OBJ = :id2 ) ";

            $arrayReq[':id1'] = $identifiant1;
            $arrayReq[':id2'] = $identifiant2;
            
            $sql .= ($sql == '' ? '' : ' UNION ') . "SELECT T_OBJ_ASS,ID_OBJ_ASS,COEFF,OBLIG  FROM ART_ASSOC with(nolock) " . $req . " AND   T_ASS  = 'C'";
            
            if ($infoLigne['COD_ART'] != '') {
                $sql .= "UNION SELECT T_OBJ_ASS,ID_OBJ_ASS,COEFF,OBLIG  FROM ART_ASSOC with(nolock) WHERE ID_OBJ = :codeArt2 AND   T_ASS  = 'A'";
                $arrayReq[':codeArt2'] = $infoLigne['COD_ART'];
            }
        }
        
        // La liste est volontairement trie par odre decroissant avec l'information d'obligation / et le coeff si = -1 alors on prendra une quantité = 1
        $req = 'SELECT DISTINCT T_OBJ_ASS,ID_OBJ_ASS,COEFF,OBLIG FROM (' . $sql . ') AS tmp ORDER BY OBLIG DESC,COEFF DESC ';
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getListePrixDegressifArt(string $provenance, string $idCat, string $idLigneCat, string $codeArt, float $qte = 0)
    {
        $sql = "select RQTECAT.IDCAT, RQTECAT.IDVERCATA, RQTECAT.IDLIGNE, RQTECAT.COD_ART, RQTECAT.QTETRANCHE, RQTECAT.REMQTE, RQTECAT.PRIXNET 
                from RQTECAT with(nolock) 
                where 1=1 ";

        $arrayReq = [];
        $where = '';
        $orderBy = '';
        if ($provenance == 'CAT') {
            $where .= " and IDCAT = :idCat and IDLIGNE = :idLigne ";
            $arrayReq[':idCat'] = $idCat;
            $arrayReq[':idLigne'] = $idLigneCat;
        } elseif ($provenance == 'HAP') {
            $where .= " and COD_ART = :codeArt";
            $arrayReq[':codeArt'] = $codeArt;
        }
        
        if ($qte) {
            $where .= " and RQTECAT.QTETRANCHE <= :qte ";
            $orderBy .= ' order by RQTECAT.QTETRANCHE desc ';
            $arrayReq[':qte'] = $qte;
        } else {
            $orderBy .= ' order by RQTECAT.QTETRANCHE';
        }

        if ($where != '') {
            $sql .= $where;
        } else {
            $sql .= " and 1=2 ";
        }

        $sql .= $orderBy;

        $commande = $this->pdo->prepare($sql);
        $commande->execute($arrayReq);
        
        if ($qte) {
            return $this->fetchAssocOne($sql, $arrayReq);
        } else {
            return $this->fetchAssocAll($sql, $arrayReq);
        }
    }

    /**
     * @return array<mixed>
     */
    public function reqAllFrsAndTxForArtCatRemise(string $lst_ctr, string $ctrEmetteur, string $idCat, string $codFab, string $codGam, ?string $top = null)
    {
        $paravrac = new Paravrac($this->pdo);
        $param = $paravrac->find(['TYPEPARV' => 'PARAMFOU', 'CODEPARV' => 'IS_CTRL_CTR_FRS']);

        if ($lst_ctr == "('')" || $lst_ctr == '') {
            $lst_ctr = '()';
        } else {
            if (strpos($lst_ctr, "'") == false) {
                $lst_ctr = substr($lst_ctr, 1);
                $lst_ctr = substr($lst_ctr, 0, -1);
                $lst_ctr = explode(',', $lst_ctr);
                $lst_ctr = "('".implode("','", $lst_ctr)."')";
            }

            $lst_ctr = str_replace("'", "''", $lst_ctr);
        }

        if (($param['LIB01PARV']??'') == 'O' && $ctrEmetteur) {
            $sql = "exec Oxaproc_remfour2 '$idCat', '$codFab', '$codGam', '" .$lst_ctr. "', '$top', '$ctrEmetteur' ";
        } else {
            $sql = "exec Oxaproc_remfour2 '$idCat', '$codFab', '$codGam', '" .$lst_ctr. "', '$top', '' ";
        }

        $commande = $this->pdo->prepare($sql);
        $commande->execute();
        
        return $this->fetchAssocAll($sql, []);
    }


    /**
     * @param string $frs
     * @return array<mixed>
     */
    public function rqSinequaLstfabgam($frs)
    {
        $req = " SELECT 
                    a.CODEFAB, a.CODEGAM
				 FROM RGAMCAT a with (nolock)
				 LEFT OUTER JOIN VERSCATA b with (nolock) ON a.IDVERCATA = b.IDATACATA
				 WHERE 
				 a.CODEFOUR = :frs AND b.STATUT = 'A' 
				 AND   b.DATEPUB <= '".date('Ymd')."' 
				 AND   b.VALIDCATA >= '".date('Ymd')."'
				 GROUP BY CODEFAB, CODEGAM";
        
        return $this->fetchAssocOne($req, [':frs' => $frs]);
    }

    /**
     * @param string $frs
     * @return array<mixed>
     */
    public function rqGetIdCatFromFrs($frs)
    {
        $req = "SELECT 
                    IDCAT 
                from RGAMCAT a with (nolock) 
                JOIN VERSCATA b with(nolock) ON a.IDVERCATA=b.IDATACATA and b.STATUT = 'A' 
                    and '".date('Ymd')."' >= b.DATEPUB 
                    and '".date('Ymd')."' <= b.VALIDCATA 
                    and CODEFOUR = :frs
                GROUP BY IDCAT;";

        return $this->fetchAssocOne($req, [':frs' => $frs]);
    }

  /**
     * @return int|string
     */
    public function rqIsArtAuthCdeOxaproc(string $codeFou, string $codeFab, string $codeAcd, string $codeArt)
    {
        if ($codeFab == '' && $codeAcd == '' && $codeArt == '') {
            return 0;
        }
        // Une des conditions pour l'autorisation à commande chantier est le montant max de commande autorisé pour le fournisseur
        
        $sql_montant_max = " (select MTMAXCC from FOURNISS with(nolock) where FOURNISS.CODE = '".$codeFou."') ";
        $sql = "select
                    case
                        when
                        (
                            select		1
                            from		FOURNISS with (nolock), FABRICAN with (nolock)
                            where		FOURNISS.CODE				= :codeFou1
                            and		FABRICAN.CODE				= :codeFab
                            and		FOURNISS.AUTOCC				= 0x54
                            and		FABRICAN.AUTOCC				= 0x54
                            and		ltrim(rtrim(FOURNISS.CODE)) != ''
                            and		ltrim(rtrim(FABRICAN.CODE)) != ''
                            and 	$sql_montant_max > 0
                        ) = 1
                        then 1
                        when   (
                            select		1
                            from		ACCCADRE with (nolock)
                            where		CODE_ACD					= :codeAcd
                            and AUTOCC						= 0x54
                            and ltrim(rtrim(CODE_ACD)) 		!= ''
                            and $sql_montant_max > 0
                        ) = 1
                        then 1
                        when	(
                            select  1
                            from	FOURNISS with (nolock), ARTICLES with (nolock)
                            where	FOURNISS.CODE					 = :codeFou2
                            and ARTICLES.CODE_ART				 = :codeArt
                            and FOURNISS.AUTOCC					 = 0x54
                            and ARTICLES.CDIRFMT				 = 0x54
                            and	ltrim(rtrim(FOURNISS.CODE))		!= ''
                            and	ltrim(rtrim(ARTICLES.CODE_ART)) != ''
                            and $sql_montant_max > 0
                        ) = 1
                        then 1
                    else 0
                    end	AUTOCC ";
        
        $arrayReq = [
            ':codeFou1' => $codeFou,
            ':codeFou2' => $codeFou,

            ':codeFab' => $codeFab,

            ':codeAcd' => $codeAcd,
            ':codeArt' => $codeArt,

        ];

        return $this->fetchAssocOne($sql, $arrayReq)['AUTOCC']??'0';
    }

    /**
     * @return array<mixed>
     */
    public function reqFrsControle(string $lst_ctr, string $ctrEmetteur, string $cod_four, string $cod_fab, string $cod_gam, string $limit = 'top 1')
    {
        $paravrac = new Paravrac($this->pdo);
        $param = $paravrac->find(['TYPEPARV' => 'PARAMFOU', 'CODEPARV' => 'IS_CTRL_CTR_FRS']);

        if ($lst_ctr != "('')" && $lst_ctr != '()' && strpos($lst_ctr, "'") == false) {
            $lst_ctr = substr($lst_ctr, 1);
            $lst_ctr = substr($lst_ctr, 0, -1);
            
            $lst_ctr = explode(',', $lst_ctr);
            $lst_ctr = "('".implode("','", $lst_ctr)."')";
        }

        // if ($lst_ctr == '') {
        //     $lst_ctr = '()';
        // } else {
            $lst_ctr = str_replace("'", "''", $lst_ctr);
        // }

        if (($param['LIB01PARV']??'') == 'O' && $ctrEmetteur) {
            $sql = "exec Oxaproc_remfour_ctrl '" . $cod_four . "',	'" . $cod_fab . "',	'" . $cod_gam . "', '" . $lst_ctr . "' ,  '" . $limit . "', '" . $ctrEmetteur . "' ";
        } else {
            $sql = "exec Oxaproc_remfour_ctrl '" . $cod_four . "',	'" . $cod_fab . "',	'" . $cod_gam . "', '" . $lst_ctr . "' ,  '" . $limit . "', '' ";
        }

        $commande = $this->pdo->prepare($sql);
        $commande->execute();
        
        return $commande->fetch($this->pdo::FETCH_ASSOC);
    }
}
