<?php
namespace Modules\Catalogue\Repository;

use Entity\Model\CFormats;
use Helpers\Ariane\Ariane;
use Helpers\QueryBuilder\AbsRepository;
use Skeleton\Controller\Controller;

class CatalogueExterneRepository extends AbsRepository
{
    /**
     * Summary of getListeCatalogue
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getListeCatalogue($post)
    {
        $req = "select 
                    CATAENT.*,
                    CFORMATS.DESCFRMT,CFORMATS.TYPDATAS,
                    FOURNISS.RS RS_FRS,
                    GRPFOU.RS RS_GRP,
                    createur.NOM CREATEUR, 
                    modifieur.NOM MODIFIEUR
                from CATAENT with (nolock)
                left join CFORMATS with (nolock) on CFORMATS.IDFORMAT = CATAENT.IDFORMAT
                left join FOURNISS with (nolock) on FOURNISS.CODE = CATAENT.REGRFOUR
                left join GRPFOU with (nolock) on GRPFOU.CODE = CATAENT.REGRFOUR
                left join HASTUTIL createur with (nolock) on createur.CODE = CATAENT.CREATPAR
                left join HASTUTIL modifieur with (nolock) on modifieur.CODE = CATAENT.MODIFPAR
                where 1=1";

        $arrayReq = [];

        $req = $this->applyFilter($req, $arrayReq, $post);

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount(
            $req,
            $post,
            $arrayReq,
            $this->pdo,
            false,
        );

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

    /**
     * @return array<mixed>
     */
    public function getInfoCatalogue(string $idCatalogue)
    {
        $req = "select 
                    CATAENT.*,
                    CFORMATS.DESCFRMT,CFORMATS.TYPDATAS,
                    FOURNISS.RS RS_FRS,
                    GRPFOU.RS RS_GRP,
                    createur.NOM CREATEUR, 
                    modifieur.NOM MODIFIEUR
                from CATAENT with (nolock)
                left join CFORMATS with (nolock) on CFORMATS.IDFORMAT = CATAENT.IDFORMAT
                left join FOURNISS with (nolock) on FOURNISS.CODE = CATAENT.REGRFOUR
                left join GRPFOU with (nolock) on GRPFOU.CODE = CATAENT.REGRFOUR
                left join HASTUTIL createur with (nolock) on createur.CODE = CATAENT.CREATPAR
                left join HASTUTIL modifieur with (nolock) on modifieur.CODE = CATAENT.MODIFPAR
                where 1=1 and CATAENT.ID_CATAL = :idCatalogue";

        $arrayReq = [];


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

    /**
     * @param string $idVersion
     * @param string $noOrdre
     * @return array<mixed>
     */
    public function getInfoVersCata(string $idVersion, ?string $noOrdre = null)
    {
        $req = "select 
                TOP 1 PARAMCHG.*, 
                PARCATPUNCH.URL_CONNECT,PARCATPUNCH.IDSRVARIBA,PARCATPUNCH.IDFOURPUNCH,PARCATPUNCH.URLSITEFOUR,PARCATPUNCH.VERSION_OCI,PARCATPUNCH.MODEPLOY,
                CFORMATS.TYPDATAS, VERSCATA.ID_CATAL, CATAENT.TYPECATA,AUTRFICH.NOMFICH
                from PARAMCHG with (nolock)
                left join PARCATPUNCH with (nolock) on PARCATPUNCH.IDVERSION = PARAMCHG.IDVERSION
                left join VERSCATA with (nolock) on PARAMCHG.IDVERSION=VERSCATA.IDATACATA
                left join CATAENT with (nolock) on CATAENT.ID_CATAL = VERSCATA.ID_CATAL
                left join CFORMATS with (nolock) on CFORMATS.IDFORMAT = CATAENT.IDFORMAT
                left join AUTRFICH with (nolock) on AUTRFICH.IDVERSION = PARAMCHG.IDVERSION and AUTRFICH.ORDRE = PARAMCHG.ORDRE and CODEFICH = 'REMGAM'
                where PARAMCHG.IDVERSION= :idVersion ";

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

        if ($noOrdre) {
            $req .= " and PARAMCHG.ORDRE = :noOrdre ";
            $arrayReq[':noOrdre'] = $noOrdre;
        }

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

    /**
     * @param string $idVersion
     * @return array<mixed>
     */
    public function getInfoForChargementCatalogue(string $idVersion)
    {
        $req = "select 
                VERSCATA.IDATACATA, VERSCATA.ID_CATAL , CATAENT.IDFORMAT, 
                CFORMATS.TYPDATAS, CATAENT.TYPECATA, CATAENT.REGRFOUR,
                (
                    select COUNT(*) from MULTIMAPP with (nolock)
                    left join FMTMAPP with (nolock) on FMTMAPP.IDFORMAT = MULTIMAPP.IDFORMAT AND FMTMAPP.LIGNE = MULTIMAPP.LIGNE
                    where MULTIMAPP.IDFORMAT= CATAENT.IDFORMAT and FMTMAPP.ACTIVE = 0x54
                ) countMultiMapping
                from VERSCATA with (nolock) 
                left join CATAENT with (nolock) on CATAENT.ID_CATAL = VERSCATA.ID_CATAL
                left join CFORMATS with (nolock) on CFORMATS.IDFORMAT = CATAENT.IDFORMAT
                where IDATACATA = :idVersion";

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

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

    /**
     * @return array<mixed>
     */
    public function listeMultiMapping(string $idFormat)
    {
        $req = "select 
                    MULTIMAPP.*,STRUCATA.LARGCOLCHRG,FMTMAPP.TYPECONTSTR, FMTMAPP.SEPARCOUPLE, FMTMAPP.SEPARLST, STRUCATA.RFORMAT, TDBNAME
                from MULTIMAPP with (nolock)
                left join FMTMAPP with (nolock) on FMTMAPP.IDFORMAT = MULTIMAPP.IDFORMAT AND FMTMAPP.LIGNE = MULTIMAPP.LIGNE
                left join STRUCATA with (nolock) on STRUCATA.LIGNE = FMTMAPP.LIGNE
                where MULTIMAPP.IDFORMAT= :idFormat and FMTMAPP.ACTIVE = 0x54 and STRUCATA.RATTRIBUT='Tab'";

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

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

    /**
     * Summary of listeVersionCatalogue
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function listeVersionCatalogue($post)
    {
        $req = "select 
                    CATAENT.*,
                    CFORMATS.DESCFRMT,
                    VERSCATA.VERSCATA,
                    VERSCATA.STATUT,
                    VERSCATA.DATEPUB,
                    VERSCATA.IDATACATA,
                    VERSCATA.VALIDCATA
                from CATAENT with (nolock)
                left join CFORMATS with (nolock) on CFORMATS.IDFORMAT = CATAENT.IDFORMAT
                left join VERSCATA with (nolock) on VERSCATA.ID_CATAL = CATAENT.ID_CATAL
                where STATUT IN ('A', 'P') ";

        $arrayReq = [];

        $req = $this->applyFilter($req, $arrayReq, $post);

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getListeSousFamilleCatalogue($post = [])
    {
        $arrayReq = [];
        $req = "select 
                    distinct(CCATASRC.SOUSFAMI), SSFAMILL.DESIGNAT 
                from CCATASRC WITH (NOLOCK)
                left join SSFAMILL WITH (nolock) on CCATASRC.SOUSFAMI = SSFAMILL.CODE_SFA
                where 1=1 ";

        $req = $this->applyFilter($req, $arrayReq, $post);

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function geteListeGammeCatalogue($post = [])
    {
        $arrayReq = [];
        $req = "select 
                    distinct(CCATASRC.GAMFABRIC), GAMMES.DESIGN,  CCATASRC.FABRICANT, FABRICAN.RS
                from CCATASRC WITH (NOLOCK)
                left join GAMMES WITH (nolock) on CCATASRC.GAMFABRIC = GAMMES.CODEGAMME
                LEFT JOIN FABRICAN WITH (NOLOCK) on FABRICAN.CODE = CCATASRC.FABRICANT
                where 1=1 ";

        $req = $this->applyFilter($req, $arrayReq, $post);

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * @param array<mixed> $listeUpdate
     */
    public function updateTypeArticle(string $carArt, $listeUpdate, bool $vide = false, ?string $idVersion = null):void
    {
        $arrayReq = [];

        $listeClause = [];

        if ($listeUpdate['SFA']) {
            $listeClause[] = $this->makeWhereIn('SOUSFAMI', $listeUpdate['SFA'], $arrayReq);
        }

        $i = 1;
        if ($listeUpdate['GAMME']) {
            foreach ($listeUpdate['GAMME'] as $gamme) {
                $listeClause[] = " (FABRICANT = :fab$i and GAMFABRIC = :gamme$i) ";
                $arrayReq[":fab$i"] = $gamme['FABRICANT'];
                $arrayReq[":gamme$i"] = $gamme['GAMME'];
                $i++;
            }
        }

        $arrayReq[':carac'] = ($vide? '':';'). $carArt;
        $arrayReq[':carac2'] = '%'.$carArt.'%';
        $req = "update CCATASRC 
                set CARACTER = trim(CARACTER) + :carac
                where trim(CARACTER) ".($vide? '=':'!=' )." '' 
                and trim(CARACTER) not like :carac2
                and 
                (
                    ".implode(' or ', $listeClause)."
                )";

        if ($idVersion) {
            $req .= " and IDVERCATA = :idVersionn";
            $arrayReq[':idVersionn'] = $idVersion;
        }

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