<?php
namespace Modules\Catalogue\Repository;

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

class FormatRepository extends AbsRepository
{
    /**
     * @param mixed $post
     * @return array<mixed>
     */
    public function getListeFormat($post)
    {
        $req = "SELECT 
                IDFORMAT, DESCFRMT, TYPDATAS, DATECREAT,
                (select COUNT(*) from CATAENT with (nolock) where IDFORMAT=CFORMATS.IDFORMAT) formatUsed,
                (
                    select COUNT(*) mapping
                    from FMTMAPP 
                    where IDFORMAT = CFORMATS.IDFORMAT and LIGNE in (
                        select LIGNE 
                        from STRUCATA 
                        WHERE NOMRUBRIQUE in ('Reference_Article_Fabricant', 'Reference_Article_Fournisseur', 'Fabricant','Code_Fournisseur')
                    )
                ) controleMapping,
                (
                    select COUNT(*) from FMTMAPP where IDFORMAT = CFORMATS.IDFORMAT and POSITION='0' and TYPEMAPPING != 'M' and ACTIVE = 0x54
                ) controlePosition
                FROM CFORMATS with (nolock) 
                where 1=1";

        $arrayReq = [];

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

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

        $req = (string)str_replace('[DESCFRMT] ASC', 'upper([DESCFRMT]) ASC', $req);
        $req = (string)str_replace('[DESCFRMT] DESC', 'upper([DESCFRMT]) DESC', $req);

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

    /**
     * @return array<mixed>
     */
    public function getStructure(string $idFormat, bool $activeOnly = false)
    {
        $req = "select 
                    STRUCATA.*, 
                    FMTMAPP.ACTIVE,FMTMAPP.ELEM_XML, FMTMAPP.POSITION, FMTMAPP.ACTIVE, FMTMAPP.TYPEMAPPING,
                    FMTMAPP.TYPECONTSTR,FMTMAPP.SEPARLST, FMTMAPP.SEPARCOUPLE, FMTMAPP.OBLIGATOI,
                    FMTMAPP.TRANSCOD, FMTMAPP.REGLES, FMTMAPP.VALDEFAUT, FMTMAPP.DIVISEUR,
                    FVALDEF.Valeur_par_defaut
                from STRUCATA with (nolock)
                left join FMTMAPP with (nolock) on IDFORMAT= :idFormat and FMTMAPP.LIGNE=STRUCATA.LIGNE
                left join FVALDEF with (nolock) on FVALDEF.IDFORMAT = FMTMAPP.IDFORMAT and FVALDEF.Ligne = FMTMAPP.LIGNE
                where 1=1 ".($activeOnly? " and ACTIVE = 0x54 ":"")."
                order by STRUCATA.LIGNE asc";

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

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

    public function resetTypeMappingSimple(string $idFormat):void
    {
        $req = "update FMTMAPP set TYPEMAPPING='S' where IDFORMAT=:idFormat and exists (
                    select * from STRUCATA where STRUCATA.LIGNE = FMTMAPP.LIGNE and STRUCATA.RATTRIBUT!='Tab'
                )";

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

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

    /**
     * @param string $idformat
     * @return array<mixed>
     */
    public function setupArianeStructure(string $idformat)
    {
        $req = "with setupCTE as (
                    select * from FMTMAPP with (nolock) where IDFORMAT= :idFormat and ACTIVE = 0x54
                ), count_transco as (
                    select COUNT(*) countTranso from setupCTE where TRANSCOD <> 0
                ), count_regle as (
                    select COUNT(*) countRegle from setupCTE where REGLES <> 0
                ), count_param as (
                    select COUNT(*) countParam from setupCTE
                ), count_connecteur as (
                    select COUNT(*) countConnecteur from PFORMATS with (nolock) where PFORMATS.IDFORMAT = :idFormat2
                )
                select * from count_param,count_transco, count_regle, count_connecteur";

        $arrayReq = [
            ':idFormat' => $idformat,
            ':idFormat2' => $idformat,
        ];

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

    /**
     * @return array<mixed> $idFormat
     */
    public function controleMapping(string $idFormat)
    {
        $req = "select COUNT(*) mapping
                from FMTMAPP 
                where IDFORMAT = :idFormat and LIGNE in (
                    select LIGNE 
                    from STRUCATA 
                    WHERE NOMRUBRIQUE in ('Reference_Article_Fabricant', 'Reference_Article_Fournisseur', 'Fabricant','Code_Fournisseur')
                )";

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

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


    /**
     * @return array<mixed> $idFormat
     */
    public function controlePosition(string $idFormat)
    {
        $req = "select COUNT(*) countPosition from FMTMAPP where IDFORMAT = :idFormat and POSITION='0' and TYPEMAPPING != 'M' and ACTIVE = 0x54";

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

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

    /**
     * @param array<mixed> $tableau
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function listeMappingPopup($tableau, $post)
    {
        $tableau = array_filter($tableau, function ($var) {
            return ($var['TAG_XML'] != '…' && $var['TAG_XML'] != '….');
        });

        $ligne = [];
        foreach ($tableau as $k) {
            $description = str_replace("'", "''", $k['DESCRIPTION']);
            $description = (string)mb_convert_encoding((string)$description, "Windows-1252", "UTF-8"); // pour anticipé le formatage dbStatement formatOuputValue

            $ligne[] = "select '".$k['ORDRE']."' ORDRE,'".$k['TAG_XML']."' TAG_XML,'".$k['LONGUEUR']."'LONGUEUR ,'".$description."' DESCRIPTION";
        }

        $req = "select * from\r\n (".implode("\r\n union \r\n", $ligne).")\r\n listeMapping ";
        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    /**
     * @param string $idformat
     * @return array<mixed>
     */
    public function getStructureControle(string $idformat)
    {
        $req = "select 
                    FMTMAPP.POSITION, STRUCATA.NOMRUBRIQUE,STRUCATA.RATTRIBUT,STRUCATA.RFORMAT,
                    FMTMAPP.OBLIGATOI, FMTMAPP.VALDEFAUT,FVALDEF.Valeur_par_defaut,
                    FMTMAPP.DIVISEUR, FMTMAPP.TRANSCOD,FMTMAPP.REGLES,
                    FMTMAPP.LIGNE
                from FMTMAPP with (nolock)
                left join FVALDEF with (nolock) on FMTMAPP.IDFORMAT = FVALDEF.IDFORMAT and FMTMAPP.LIGNE = FVALDEF.Ligne
                left join STRUCATA with (nolock) on STRUCATA.LIGNE = FMTMAPP.LIGNE
                where FMTMAPP.IDFORMAT=:idFormat and FMTMAPP.POSITION != '0'
                order by FMTMAPP.POSITION asc";

        $arrayReq = [
            ':idFormat' => $idformat,
        ];

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

    /**
     * @param string $idformat
     * @return array<mixed>
     */
    public function getTranscodage(string $idformat)
    {
        $req = "select 
                    FMTMAPP.POSITION, STRUCATA.NOMRUBRIQUE,STRUCATA.RATTRIBUT,STRUCATA.RFORMAT, FMTMAPP.LIGNE,TRANSCOD.*
                from FMTMAPP with (nolock)
                left join STRUCATA with (nolock) on STRUCATA.LIGNE = FMTMAPP.LIGNE
                outer apply (
                    select top 1 CONCAT(TRANSCOD.VALENTREE,' -> ', TRANSCOD.VALREMPLA) TRANSCO from TRANSCOD with (nolock) where TRANSCOD.IDFORMAT = FMTMAPP.IDFORMAT and TRANSCOD.LGMAPPING = FMTMAPP.LIGNE
                ) TRANSCOD
                where FMTMAPP.IDFORMAT=:idFormat and FMTMAPP.TRANSCOD = 1
                order by LIGNE asc";

        $arrayReq = [
            ':idFormat' => $idformat,
        ];

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

    /**
     * @param string $idformat
     * @return array<mixed>
     */
    public function getRegleValidation(string $idformat)
    {
        $req = "select 
                    FMTMAPP.POSITION, STRUCATA.NOMRUBRIQUE,STRUCATA.RATTRIBUT,STRUCATA.RFORMAT, FMTMAPP.LIGNE,regle.OPERATEUR, regle.REGCOMPAR, regle.ZONE1, regle.ZONE2
                from FMTMAPP with (nolock)
                left join STRUCATA with (nolock) on STRUCATA.LIGNE = FMTMAPP.LIGNE
                outer apply (
                    select top 1 * from REGVALID with (nolock) where REGVALID.IDFORMAT = FMTMAPP.IDFORMAT and REGVALID.LGMAPPING = FMTMAPP.LIGNE
                ) regle
                where FMTMAPP.IDFORMAT= :idFormat and FMTMAPP.REGLES = 1
                order by regle.LIGNE asc";

        $arrayReq = [
            ':idFormat' => $idformat,
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * Summary of getListeNomParam
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getListeNomParam($post)
    {
        $req = "SELECT * FROM CROSSREF_FMT with (nolock) ";

        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        (new PaginatorGestion())->injecterSqlCount($req, $post, $arrayReq);

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

    /**
     * @return array<mixed>
     */
    public function getInfoPunchoutLink(string $idformat)
    {
        $req = "SELECT 
                    PARCATPUNCH.*, VERSCATA.VERSCATA,VERSCATA.STATUT,CATAENT.ID_CATAL,CATAENT.DESCRIPTION_CATA 
                FROM PARCATPUNCH with (nolock)
                LEFT JOIN VERSCATA with (nolock) on PARCATPUNCH.IDVERSION = VERSCATA.IDATACATA
                LEFT JOIN CATAENT with (nolock) on VERSCATA.ID_CATAL = CATAENT.ID_CATAL
                WHERE IDFORMAT=:idFormat";


        $arrayReq = [
            ':idFormat' => $idformat,
        ];

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