<?php

namespace Modules\RechercheArticles\Repository;

use Helpers\QueryBuilder\AbsRepository;

class Punchout extends AbsRepository
{

    /**
     * @return array<mixed>
     */
    public function getInfoPunchout(string $idCat)
    {
        $req = "select 
                    REGRFOUR,
                    SRCDATA,
                    URL_CONNECT,
                    CATAENT.IDFORMAT,
                    ID_CLI_LOGIN,
                    ID_SRV_PASSW,
                    IDSRVARIBA,
                    IDFOURPUNCH,
                    URLSITEFOUR,
                    MODEPLOY,
                    NUMCOMPTE,
                    ID_SOCIETE,
                    ENCODING,
                    VERSION_OCI 
                from PARCATPUNCH with (nolock)
                left join VERSCATA with (nolock) on VERSCATA.IDATACATA = PARCATPUNCH.IDVERSION
                left join CPSTRUCT with (nolock) on CPSTRUCT.VALINFO   = VERSCATA.ID_CATAL
                left join CATAPRIVE with (nolock) on  CATAPRIVE.ID_CATPR = CPSTRUCT.ID_CATPR
                left join CATAENT with (nolock) on CATAENT.ID_CATAL   = CPSTRUCT.VALINFO
                left join CFORMATS with (nolock) on CFORMATS.IDFORMAT = CATAENT.IDFORMAT
                where CATAPRIVE.ID_CATPR = :idCat
                and (CATAPRIVE.SRCDATA = 'PO' or CATAPRIVE.SRCDATA = 'PC') 
                and (VERSCATA.STATUT   = 'A' or VERSCATA.STATUT = 'P') 
                and CPSTRUCT.TYPEINFO  ='CCATASRC'";
            
        $arrayReq = [
            ':idCat' => $idCat,
        ];
        
        return $this->fetchAssocOne($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getParamPunchOut(string $idFormat)
    {
        $req = "select * from PFORMATS with(nolock) where IDFORMAT= :idFormat and NOM_PARAMETRE != 'target' and IDCLE='PARAM' order by ORDRE";

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

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

    /**
     * @return array<mixed>
     */
    public function getParamField(string $idFormat)
    {
        $req = "SELECT * FROM FMTMAPP WITH(NOLOCK)
                LEFT JOIN STRUCATA WITH(NOLOCK)  on STRUCATA.LIGNE = FMTMAPP.LIGNE 
                WHERE FMTMAPP.IDFORMAT = :idFormat
                ORDER BY ELEM_XML DESC
                ";

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

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

    /**
     * @return array<mixed>
     */
    public function getQteCond(string $idFormat)
    {
        $req = "SELECT * FROM FMTMAPP WITH(NOLOCK)
                LEFT JOIN STRUCATA WITH(NOLOCK)  on STRUCATA.LIGNE = FMTMAPP.LIGNE 
                WHERE FMTMAPP.IDFORMAT = :idFormat AND TDBNAME = 'CONDITNT'
                ";

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

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

    /**
     * @return array<mixed>
     */
    public function getTransco(string $fields, string $idCat, string $value)
    {
        $req = "select VALREMPLA from CATAPRIVE with(nolock)
                left join CPSTRUCT with(nolock) on CATAPRIVE.ID_CATPR = CPSTRUCT.ID_CATPR
                left join CATAENT with(nolock)  on CATAENT.ID_CATAL   = CPSTRUCT.VALINFO
                left join CFORMATS with(nolock) on CFORMATS.IDFORMAT  = CATAENT.IDFORMAT
                left join TRANSCOD with(nolock) on TRANSCOD.IDFORMAT  = CFORMATS.IDFORMAT
                left join STRUCATA with(nolock)	on TRANSCOD.LGMAPPING = STRUCATA.LIGNE
                where  (CFORMATS.TYPDATAS = 'cXML' or CFORMATS.TYPDATAS  = 'OCI')
                and STRUCATA.TDBNAME   = :field
                and CATAPRIVE.ID_CATPR = :idCat
                and VALENTREE = :valEntree ";

        $arrayReq = [
            ':idCat'        => $idCat,
            ':field'        => $fields,
            ':valEntree'    => $value
        ];
        
        return $this->fetchAssocOne($req, $arrayReq);
    }
}
