<?php
namespace Modules\Catalogue\Repository;

use Helpers\QueryBuilder\AbsRepository;

class ControleRepository extends AbsRepository
{
    /**
     * @param string $idVersion
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getListeArticleCatalogue($idVersion, $post = [])
    {
        $req = "select 
                    CCATASRC.*, 
                    ARTEQUIV.CLASSNUM, ARTEQUIV.REFERENCE,
                    EQUIVART.ARTPRACT
                from CCATASRC with (nolock)
                left join ARTEQUIV with (nolock) ON 
                    ARTEQUIV.CODEART = CCATASRC.CODEARTI
                    and ARTEQUIV.CODEFOUR = CCATASRC.CODEFOUR
                    and ARTEQUIV.ARTFOUR = CCATASRC.REFARTFOU
                    and ARTEQUIV.CODEFAB = CCATASRC.FABRICANT
                    and ARTEQUIV.ARTFAB = CCATASRC.REFARTFAB
                left join EQUIVART with (nolock) ON 
                    EQUIVART.NUM_CLEQ = ARTEQUIV.CLASSNUM
                WHERE IDVERCATA= :idVersion ";

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

        $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 getUNSPSC(string $parent = '')
    {
        $req = "select 
                    DILISPECN.CODEDICO,DILISPECN.LIB01DICO,CLE1, DILISPEC.LIB01DICO LIB_ENG, 
                    (select count(1) from DILISPECN FILS with (nolock) where FILS.CLE1 = DILISPECN.CODEDICO) NODEPRESENT
                from DILISPECN with (nolock)
                left join DILISPEC with (nolock) on DILISPEC.TYPEDICO = DILISPECN.TYPEDICO and DILISPEC.CODEDICO = DILISPECN.CODEDICO
                where DILISPECN.TYPEDICO='UNSPSC' and CLE1 = :parent
                order by CODEDICO";

        $arrayReq = [
            ':parent' => $parent,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
     */
    public function getListeCarac(string $idLigne, string $typeCar)
    {
        $req = "select 
                CATCARACTINFO.COD_CAR,
                CASE CATCARACTINFO.TYP_CAR
                    when 'S' then TYPECARSEL.TITRE_CAR
                    else CATCARACTINFO.TITRE_CAR
                end TITRE_CAR,
                CATCARACTINFO.VAL_CAR,
                CATCARACTINFO.ISSU_CHARG,
                CATCARACTINFO.ID_CAR
                from CATCARACTINFO with (nolock)
                left join TYPECARSEL with (nolock) on TYPECARSEL.COD_CAR = CATCARACTINFO.COD_CAR
                where ID_LIGNE = :idLigne and TYP_CAR = :typeCar
                order by ID_CAR";

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

    /**
     * @return array<mixed>
     */
    public function getArticlesAssocies(string $idObjet, ?string $typeObjet = null, ?string $idObjetAssoc = null)
    {
        $req = "select 
                    CASE T_OBJ_ASS 
                        WHEN 'A' then (
                            select top 1 DESIGN from ARTICLES with (nolock) where ARTICLES.CODE_ART = ART_ASSOC.ID_OBJ_ASS
                        ) 
                        WHEN 'C' then (
                            select top 1  DESIGNCRT
                            from CCATASRC with (nolock)
                            left join VERSCATA with (nolock) on VERSCATA.IDATACATA = CCATASRC.IDVERCATA
                            where CONCAT( VERSCATA.ID_CATAL,'|',CODEARTI,'|',CODEFOUR,'|', REFARTFOU,'|', FABRICANT,'|',REFARTFAB) = ART_ASSOC.ID_OBJ_ASS
                        )
                        else ''
                    end LIB,*
                from ART_ASSOC with (nolock)
                where T_ASS = 'C' and ID_OBJ=:id";

        $arrayReq = [
            ':id' => $idObjet,
        ];
        
        if ($typeObjet && $idObjetAssoc) {
            $req .= " and T_OBJ_ASS = :typeObjet and ID_OBJ_ASS = :idObjetAssoc ";
            $arrayReq[':typeObjet'] = $typeObjet;
            $arrayReq[':idObjetAssoc'] = $idObjetAssoc;

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

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

    /**
     * @return array<mixed>
     */
    public function getAssociesA(string $idObjet, ?string $typeObjet = null, ?string $idAssoc = null)
    {
        $req = "select 
                    CASE T_ASS 
                        WHEN 'A' then (
                            select top 1 DESIGN from ARTICLES with (nolock) where ARTICLES.CODE_ART = ART_ASSOC.ID_OBJ
                        ) 
                        WHEN 'G' then (
                            select top 1 RS from GRPFOU with (nolock) where GRPFOU.CODE = ART_ASSOC.ID_OBJ
                        ) 
                        WHEN 'F' then (
                            select top 1 RS from FOURNISS with (nolock) where FOURNISS.CODE = ART_ASSOC.ID_OBJ
                        ) 
                        WHEN 'S' then (
                            select top 1 DESIGNAT from SSFAMILL with (nolock) where SSFAMILL.CODE_SFA = ART_ASSOC.ID_OBJ
                        ) 
                        WHEN 'R' then (
                            select top 1 LIBRAYON from RAYON with (nolock) where RAYON.IDRAYON = ART_ASSOC.ID_OBJ
                        ) 
                        WHEN 'C' then (
                            select top 1  DESIGNCRT
                            from CCATASRC with (nolock)
                            left join VERSCATA with (nolock) on VERSCATA.IDATACATA = CCATASRC.IDVERCATA
                            where CONCAT( VERSCATA.ID_CATAL,'|',CODEARTI,'|',CODEFOUR,'|', REFARTFOU,'|', FABRICANT,'|',REFARTFAB) = ART_ASSOC.ID_OBJ
                        )
                        else ''
                    end LIB,*
                from ART_ASSOC with (nolock)
                where T_OBJ_ASS = 'C' and ID_OBJ_ASS=:id";

        $arrayReq = [
            ':id' => $idObjet,
        ];

        if ($typeObjet && $idAssoc) {
            $req .= " and T_ASS = :typeObjet and ID_OBJ = :idAssoc ";
            $arrayReq[':typeObjet'] = $typeObjet;
            $arrayReq[':idAssoc'] = $idAssoc;
            
            return $this->fetchAssocOne($req, $arrayReq);
        }

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

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function listeEquivalenceClass(string $idClass, $post = [])
    {
        $req = "select * from (
                select 
                    ARTEQUIV.*,
                    FOURNISS.RS FOURNISS_RS,
                    FABRICAN.RS FABRICAN_RS,
                    CCATASRC.PRIXNET,
                    CCATASRC.DESIGNCRT
                from ARTEQUIV with (nolock)
                left join FOURNISS with (nolock) on FOURNISS.CODE = ARTEQUIV.CODEFOUR
                left join FABRICAN with (nolock) on FABRICAN.CODE = ARTEQUIV.CODEFAB
                left join CCATASRC with (nolock) on 
                                    ARTEQUIV.CODEART = CCATASRC.CODEARTI
                                    and ARTEQUIV.CODEFOUR = CCATASRC.CODEFOUR
                                    and ARTEQUIV.ARTFOUR = CCATASRC.REFARTFOU
                                    and ARTEQUIV.CODEFAB = CCATASRC.FABRICANT
                                    and ARTEQUIV.ARTFAB = CCATASRC.REFARTFAB
                where CLASSNUM = :idClass
        ) request where 1=1 ";

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


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

    /**
     * @param array<mixed> $post
     * @param string $idVersion
     * @return array<mixed>
     */
    public function listeArticles($post = [], $idVersion = null)
    {
        $req = "select 
                IDLIGNE,REFARTFOU , DESIGNCRT, DESIGNLG, CARACTER, 
                SOUSFAMI, SSFAMILL.DESIGNAT,
                FABRICANT, FABRICAN.RS,
                CODEFOUR, FOURNISS.RS
                from CCATASRC with (nolock)
                left join FOURNISS with (nolock) on FOURNISS.CODE = CCATASRC.CODEFOUR
                left join FABRICAN with (nolock) on FABRICAN.CODE = CCATASRC.FABRICANT
                left join SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = CCATASRC.SOUSFAMI
                WHERE 1=1 ";

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

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