<?php

namespace Modules\Consultation\Repository;

use Helpers\Outil\Roles;
use Helpers\QueryBuilder\AbsRepository;

class Reponse extends AbsRepository
{

    /**
     * @return array<mixed>
    */
    /**
     * listeReponseAO
     * @return array<mixed>
     */
    public function listeReponseAO(
        string $idAO,
        string $codeFou,
        string $idRep,
        string $typeFG,
        string $cv,
        string $idLigne = null
    ) : ?array {
        $req = "SELECT 
                AO_LIGNE.NO_PO,
                AO_REP.*,
                MEMOS.CONTMEMO DES_COMP,
                FABRICAN.RS fabricant,
                GAMMES.DESIGN gammes,
                m2.CONTMEMO AS titre,
                AO_LIGNE.TITREST AS sous_titre 
            FROM AO_LIGNE with (nolock)
            left join AO_REP with (nolock) on AO_REP.NO_AO = AO_LIGNE.NO_AO and AO_REP.NO_PO_AO = AO_LIGNE.NO_PO and AO_REP.CODE_FOU = :codeFou and AO_REP.TYPE_FG = :typeFG and DTE_REP = :dteRep and C_V = :CV
            left join MEMOS with (nolock) on MEMOS.NODEMEMO = AO_REP.DESCOMPL
            left join MEMOS m2 with (nolock) on m2.NODEMEMO = AO_LIGNE.NM_ST_AV
            left join GAMMES with (nolock) on GAMMES.CODEGAMME = AO_REP.COD_GAM and GAMMES.CODEFABR  = AO_REP.COD_FAB
            left join FABRICAN with (nolock) on FABRICAN.CODE = AO_REP.COD_FAB 
            where AO_LIGNE.NO_AO = :noAO ";
        
        $arrayReq = [
            ':codeFou' => $codeFou,
            ':dteRep' => $idRep,
            ':noAO' => $idAO,
            ':typeFG' => $typeFG,
            ':CV' => $cv,
        ];
        
        if ($idLigne) {
            $req .= " and AO_LIGNE.NO_PO = :idLigne";
            $arrayReq[':idLigne'] = $idLigne;
        }
        
        $req .= "  order by NO_PO";
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
    * @return array<mixed>
    */
    public function getMaxSousLigne(string $idAO, string $codeFou, string $idRep, string $typeFG, string $idLigne) : ?array
    {
        $req = "select max(NO_SSLIG) maxSousLigne from AO_REPSL with (nolock) where NO_AO = :noAO and TYPE_FG = :typeFG and CODE_FOU = :codeFou and DTE_REP = :dteRep and NO_PO_AO = :idLigne";
        
        $arrayReq = [
            ':codeFou' => $codeFou,
            ':dteRep' => $idRep,
            ':noAO' => $idAO,
            ':typeFG' => $typeFG,
            ':idLigne' => $idLigne,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetch();
    }

    /**
    *
    * @return array<mixed>
    */
    public function getLastReponseAo(string $idAO, string $codeFou, bool $frsOxalink = false) : ?array
    {
        $req = "select DTE_REP, C_V, EVE_REP from AO_DAREP where NO_AO = :noAO and CODE_FOU = :codeFou ";
        
        $arrayReq = [
            ':codeFou' => $codeFou,
            ':noAO' => $idAO,
        ];
        $droit = Roles::droitsUtilisateur($this->pdo, 'HA_AO_SRF', $_SESSION["Session_Login"]);
        if ($frsOxalink && !$droit) {
            $req .= " and EVE_REP != '0' ";
        }

        $req .= " order by DTE_REP DESC ";
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
    * @return array<mixed>
    */
    public function getLastReponseValidateAo(string $idAO, string $codeFou) : ?array
    {
        $req = "select TOP 1 * from AO_DAREP with(nolock) where NO_AO = :noAO and CODE_FOU = :codeFou and EVE_REP != 0 ORDER BY DTE_REP DESC, C_V DESC";
        
        $arrayReq = [
            ':codeFou' => $codeFou,
            ':noAO' => $idAO,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetch();
    }
    
    /**
     * @return array<mixed>
    */
    public function getListDocAtt(string $idAO, string $typeFG, string $codeFou, string $CV, string $idRep, bool $docEstPresent = false) : ?array
    {
        $req = "select AO_DOC_ATTENDUS.*, AO_REP_PIJO.NOM_ORI, AO_DOC_ATTENDUS.TITRE_DOC TITRE from AO_DOC_ATTENDUS with (nolock)
                left join AO_REP_PIJO with (nolock) on AO_DOC_ATTENDUS.NO_AO = AO_REP_PIJO.NO_AO and AO_DOC_ATTENDUS.NUM_ORDRE = AO_REP_PIJO.NUM_ORDRE and CODE_FOU =  :codeFou and TYPE_FG = :typeFG and DTE_REP = :dteRep and C_V = :cv and TYPE_DOC = 'D'
                where AO_DOC_ATTENDUS.NO_AO = :noAO";
        
        $arrayReq = [
            ':noAO' => $idAO,
            ':codeFou' => $codeFou,
            ':cv' => $CV,
            ':dteRep' => $idRep,
            ':typeFG' => $typeFG,
        ];
        
        if ($docEstPresent) {
            $req .=" and OBLIGATOIRE = 0x54 and NOM_ORI is null";
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
    * @return array<mixed>
    */
    public function getMaxPJ(string $idAO, string $codeFou, string $idRep, string $typeFG, string $CV) : ?array
    {
        $req = "select max(NUM_ORDRE) maxOrdre from AO_REP_PIJO with (nolock)
                where NO_AO = :noAO and CODE_FOU = :codeFou  and TYPE_FG = :typeFG and DTE_REP = :dteRep and C_V = :cv and TYPE_DOC = 'P'";
        
        $arrayReq = [
            ':codeFou' => $codeFou,
            ':dteRep' => $idRep,
            ':noAO' => $idAO,
            ':typeFG' => $typeFG,
            ':cv' => $CV,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetch();
    }
    
    /**
     * @return array<mixed>
    */
    public function getListeQuestionReponse(string $idAO, string  $idTheme, string  $codeFou, string $idRep, string $typeFG) : ?array
    {
        $req = "SELECT aqq.*,comQst.CONTMEMO comQst, arq.REP_NUM, arq.REP_LOG, arq.REP_TAB, comRep.CONTMEMO comRep,  repOuvert.CONTMEMO repOuvert, arq.NOTE 
                from AO_QST_Q aqq with (nolock)
                LEFT JOIN MEMOS comQst with(nolock) on comQst.NODEMEMO = aqq.NM_COM
                left join AO_REP_QST arq 
                    on arq.NO_AO = aqq.NO_AO 
                    AND arq.ID_THEME = aqq.ID_THEME 
                    and arq.ID_QUEST = aqq.ID_QUEST 
                    and arq.CODE_FOU = :codeFou 
                    and arq.TYPE_FG = :typeFG 
                    and arq.C_V = 'C' /* This column is not actually used and should always be 'C' */
                    and arq.DTE_REP = :dteRep
                left join MEMOS comRep with(nolock) on comRep.NODEMEMO = arq.NM_COM
                left join MEMOS repOuvert with(nolock) on repOuvert.NODEMEMO = arq.REP_OUV_NM
                where aqq.NO_AO = :noAO and aqq.ID_THEME = :idTheme
                order by aqq.NM_ORDRE, aqq.ID_QUEST";
        
        return $this->fetchAssocAll($req, [
            ':codeFou' => $codeFou,
            ':dteRep' => $idRep,
            ':noAO' => $idAO,
            ':typeFG' => $typeFG,
            ':idTheme' => $idTheme,
        ]);
    }
    
    /**
    * @return array<mixed>
    */
    public function getListTheme(string $idAo, string $idPere) : ?array
    {
        $req = "SELECT * FROM AO_QST_TH with (nolock) where  UPPER(NO_AO) = :idAo and ID_PERE = :idPere and ID_THEME != 'APRIX' ORDER BY NM_ORDRE asc";
        
        $arrayReq = [
            ':idAo' => $idAo,
            ':idPere' => $idPere,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * getListThemeReponseQuestionnaire
     *
     * @param  string $idAo
     * @return array<mixed>
     */
    public function getListThemeReponseQuestionnaire(string $idAo): array
    {
        $req = "SELECT * 
        FROM AO_QST_TH th WITH (NOLOCK) 
        WHERE  UPPER(NO_AO) = :idAo 
            AND EXISTS (SELECT * FROM AO_QST_Q with(nolock) WHERE NO_AO = th.NO_AO AND ID_THEME = th.ID_THEME)";
        
        $arrayReq = [
            ':idAo' => $idAo,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
    * @return array<mixed>
    */
    public function getInfoRep(string $idAO, string $codeFou, string $typeFG, string $noLigne) : ?array
    {
        $req = "select TOP 1 AO_REP.QTE, AO_REP.PU, AO_REP.C_V, AO_REP.DTE_REP, AO_RETPARLIGNE.CODE_FOU, AO_REP.TYPE_FG, AO_DAREP.DTELIMIT,
                (select count(*) from AO_REPSL with (nolock) where AO_REPSL.NO_AO = AO_REP.NO_AO and AO_REPSL.NO_PO_AO = AO_REP.NO_PO_AO AND AO_REPSL.CODE_FOU = AO_REP.CODE_FOU AND AO_REPSL.TYPE_FG = AO_REP.TYPE_FG AND AO_REPSL.C_V = AO_REP.C_V) COUNT_REPSL
                from AO_REP with (nolock)
                LEFT JOIN AO_RETPARLIGNE with (nolock) on AO_RETPARLIGNE.NO_AO = AO_REP.NO_AO and AO_RETPARLIGNE.NO_PO = AO_REP.NO_PO_AO
                left join AO_DAREP with (nolock) on AO_DAREP.NO_AO = AO_REP.NO_AO and AO_DAREP.CODE_FOU= AO_REP.CODE_FOU and AO_DAREP.TYPE_FG = AO_REP.TYPE_FG AND AO_DAREP.DTE_REP= AO_REP.DTE_REP AND AO_DAREP.C_V = AO_REP.C_V 
                WHERE AO_REP.NO_AO = :noAO AND AO_REP.CODE_FOU = :codeFou and AO_REP.TYPE_FG = :typeFG AND AO_REP.NO_PO_AO = :noLigne and AO_DAREP.EVE_REP != '0'
                ORDER BY AO_REP.DTE_REP DESC, AO_REP.C_V DESC";
        
        $arrayReq = [
            ':noAO' => $idAO,
            ':codeFou' => $codeFou,
            ':typeFG' => $typeFG,
            ':noLigne' => $noLigne
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    /**
    * @return array<mixed>
    */
    public function getNoteFrs(string $idAO, string  $codeFou, string $typeFG, string $CV, string $idRep) : ?array
    {
        $req = "exec dbo.HASSTD_NoteConsultation 0,'','',:noAO,:codeFou,:typeFG,:typeCV,:idrep";
        
        $arrayReq = [
            ':noAO' => $idAO,
            ':codeFou' => $codeFou,
            ':typeCV' => $CV,
            ':typeFG' => $typeFG,
            ':idrep' => $idRep
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    public function updateFrsRetenu(string $idAO) : void
    {
        $req = "update AO_FOUR set FOU_RETEN = 0x54 where NO_AO = :idAO and CODE_FOU in (select DISTINCT(CODE_FOU) from AO_RETPARLIGNE with (nolock) where NO_AO = :idAO2)";
        
        $arrayReq = [
            ':idAO' => $idAO,
            ':idAO2' => $idAO,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
    * @return array<mixed>
    */
    public function getEnteteReponse(string $idAO, string $idFrs, string $typeFG) : ?array
    {
        $req = "SELECT TOP 1 AO_REP.*, FOURNISS.RS from AO_REP with (nolock)
                left join FOURNISS with(nolock) on FOURNISS.CODE = AO_REP.CODE_FOU
                where NO_AO = :idAO and CODE_FOU = :idFrs and TYPE_FG = :typeFG
                order by DTE_REP desc, C_V";
        $arrayReq = [
            ':idAO' => $idAO,
            ':idFrs' => $idFrs,
            ':typeFG' => $typeFG,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    /**
    * @return array<mixed>
    */
    public function getErrorRep(string $idAO) : ?array
    {
        $req = "select * from
                (select NO_PO, COUNT(NO_PO) countNoPo from AO_RETPARLIGNE with (nolock) where NO_AO = :idAO group by NO_PO) ERROR_REP
                where countNoPo > 1";
        
        $arrayReq = [':idAO' => $idAO];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
    * @return array<mixed>
    */
    public function getFrsByGrp(string $idFrs) : ?array
    {
        $req = "select * 
                from FOURNISS with (nolock) 
                where FOURPRIN = :idFrs and PROSPECT = 0x46 and DTEVALMIN <= '".date('Ymd')."'  AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '".date('Ymd')."') 
                ORDER BY RS";
        
        $arrayReq = [':idFrs' => $idFrs];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
    * @return array<mixed>
    */
    public function getMaxCol(string $idAo) : ?array
    {
        $req = "select max(NO_COL) MAX_NO_COL from AO_FOUR with (nolock) where NO_AO = :idAo";
        
        $arrayReq = [':idAo' => $idAo];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getNoteForNumerique
     *
     * @param  string $noAo
     * @param  string $idTheme
     * @param  string $idQuest
     * @param  string $value
     * @return array<mixed>
     */
    public function getNoteForNumerique(string $noAo, string $idTheme, string $idQuest, string $value) : ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':NO_AO' => $noAo,
            ':ID_THEME' => $idTheme,
            ':ID_QUEST' => $idQuest,
            ':value' => str_replace(' ', '', str_replace(',', '.', $value))
        ];
        
        $req="SELECT TOP 1 NOTE, CAST(REPLACE(TXT_REP, ',', '') AS FLOAT)  test
            FROM AO_QST_R
            WHERE NO_AO = :NO_AO
                AND ID_THEME = :ID_THEME
                AND ID_QUEST = :ID_QUEST
                AND CAST(:value AS FLOAT) >= CAST(REPLACE(TXT_REP, ',', '') AS FLOAT)
            ORDER BY test DESC ";

        /**
         * Validation de la requete et execution
        */
        $stmt = $this->pdo->prepare($req);

        $stmt->execute($arrayReq);
        
        return $stmt->fetch(\PDO::FETCH_ASSOC);
    }
}
