<?php

namespace Modules\Consultation\Repository;

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

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 string
     */
    public function isLastResponse(string $idAO, string $codeFou, string $idRep, string $typeFG, string $CV): string
    {
        $req = <<<SQL

            select TOP 1 (CASE WHEN DTE_REP = :dteRep THEN 0x54 ELSE 0x46 END) DerniereReponse from AO_DAREP where NO_AO = :noAO and CODE_FOU = :codeFou and TYPE_FG = :typeFG and C_V = :CV order by DTE_REP desc

        SQL;

        $arrayReq = [
            ':codeFou' => $codeFou,
            ':dteRep' => $idRep,
            ':noAO' => (int) $idAO,
            ':typeFG' => $typeFG,
            ':CV' => $CV
        ];

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

    /**
     *
     * @return array<mixed>
     */
    public function getLastReponseAo(string $idAO, string $codeFou, bool $frsOxalink = false): ?array
    {
        $req = "SELECT ad.DTE_REP, ad.C_V, ad.EVE_REP, 
                (SELECT COUNT(arq.ID_QUEST) 
                    FROM AO_REP_QST arq 
                    WHERE arq.NO_AO = ad.NO_AO AND arq.DTE_REP = ad.DTE_REP AND arq.CODE_FOU = ad.CODE_FOU
                        AND arq.ID_THEME LIKE 'I%') COUNT_REP_CONTRIB
            FROM AO_DAREP ad
            WHERE ad.NO_AO = :noAO AND ad.CODE_FOU = :codeFou ";

        $arrayReq = [
            ':codeFou' => $codeFou,
            ':noAO' => $idAO,
        ];
        $droit = Roles::droitsUtilisateur($this->pdo, 'HA_AO_SRF', $_SESSION["Session_Login"]);
        if ($frsOxalink && !$droit) {
            $req .= " AND ad.EVE_REP != '0' ";
        }

        $req .= " ORDER BY ad.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, string $CV): ?array
    {
        $req = "SELECT aqq.*,comQst.CONTMEMO comQst, arq.REP_NUM, arq.REP_LOG, arq.REP_TAB, comRep.CONTMEMO comRep,  repOuvert.CONTMEMO repOuvert, arq.NOTE, arq.TERMINE
                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 = :CV
                    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,
            ':CV' => $CV
        ]);
    }

    /**
     * @return array<mixed>
     */
    public function getListTheme(string $idAo, string $idPere, string $type): ?array
    {
        $req = "SELECT * FROM AO_QST_TH with (nolock) 
        where  UPPER(NO_AO) = :idAo 
            and ID_PERE = :idPere and ID_THEME != 'APRIX' and ID_THEME LIKE :type 
        ORDER BY NM_ORDRE asc";

        $arrayReq = [
            ':idAo' => $idAo,
            ':idPere' => $idPere,
            ':type' => $type == 'F' ? 'A%' : 'I%',
        ];

        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);
    }

    /**
     * getContributorsQuestions
     *
     * @param  string $idAo
     * @return array<mixed>
     */
    public function getContributorsQuestions(string $idAo): array
    {
        $query = "SELECT AO_QST_Q.NO_AO, AO_QST_Q.ID_THEME, AO_QST_Q.ID_QUEST, AO_QST_Q.G_B_QST_LIE, AO_QST_Q.LIB_QUEST, AO_QST_Q.NM_COM, 
            AO_QST_Q.QUEST_FOUR, AO_QST_Q.TYPE_REP, AO_QST_Q.NOTE_FAUX, AO_QST_Q.NOTE_VRAI, AO_QST_Q.NUM_FORMAT, AO_QST_Q.NUM_MIN, 
            AO_QST_Q.NUM_MAX, AO_QST_Q.POIDS, AO_QST_Q.NM_ORDRE, AO_QST_Q.ID_MODEL, AO_QST_TH.PROFNOTEUR1, AO_QST_TH.PROFNOTEUR2
        FROM AO_QST_Q WITH (NOLOCK) 
            LEFT JOIN AO_QST_TH WITH (NOLOCK) ON AO_QST_TH.NO_AO = AO_QST_Q.NO_AO AND AO_QST_TH.ID_THEME = AO_QST_Q.ID_THEME
        WHERE AO_QST_Q.NO_AO = :idAo 
            AND AO_QST_Q.ID_THEME LIKE 'I%'";

        $params = [
            ':idAo' => $idAo,
        ];

        return $this->fetchAssocAll($query, $params);
    }

    /**
     * @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);
    }

    /**
     * getDetailsQuestions
     *
     * @param  string $noAo
     * @param  string $type
     * @param  array<mixed> $params
     * @return array<mixed>
     */
    public function getDetailsQuestions(string $noAo, string $type, array $params = []): ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':NO_AO' => $noAo,
            ':THEME_LIKE' => $type == 'contributors' ? 'I%' : 'A%',
        ];

        $whereContrib = '';
        $joinGroup = '';
        $joinRep = '';
        $contribSelect = "''";

        if ($type == 'contributors') {
            $joinGroup = " LEFT JOIN AO_CONTRIB ac WITH (NOLOCK) ON ac.NO_AO = aqt.NO_AO AND aqt.PROFNOTEUR1 = ''
			LEFT JOIN AO_CONTRIB_GRP acg on acg.NO_AO = aqq.NO_AO AND aqt.PROFNOTEUR1 = 'G' AND aqt.PROFNOTEUR2 = acg.GROUPE_CONTRIB ";
            $joinRep = "AND arq.CONTRIB = CASE 
                    WHEN aqt.PROFNOTEUR1 = 'G' 
                    THEN acg.CONTRIB 
                    WHEN aqt.PROFNOTEUR1 = 'N'
                    THEN aqt.PROFNOTEUR2
                    WHEN aqt.PROFNOTEUR1 = ''
                    THEN ac.CONTRIB 
                END";
            $contribSelect = " CASE 
                WHEN aqt.PROFNOTEUR1 = 'G' 
                THEN acg.CONTRIB 
                WHEN aqt.PROFNOTEUR1 = 'N'
                THEN aqt.PROFNOTEUR2
                WHEN aqt.PROFNOTEUR1 = ''
                THEN ac.CONTRIB 
            END ";
            if (isset($params['contrib']) && $params['contrib'] != '') {
                $user = $params['contrib'];
                $whereContrib = " AND CASE 
                    WHEN aqt.PROFNOTEUR1 = 'G' 
                    THEN acg.CONTRIB 
                    WHEN aqt.PROFNOTEUR1 = 'N'
                    THEN aqt.PROFNOTEUR2
                    WHEN aqt.PROFNOTEUR1 = ''
                    THEN ac.CONTRIB 
                END = :CONTRIB ";
                $arrayReq[':CONTRIB'] = $user;
            }
        }

        $req = "SELECT aqq.ID_QUEST, aqq.LIB_QUEST, aqq.ID_THEME, arq.NOTE, aqq.POIDS,
            CASE 
                WHEN aqq.TYPE_REP = 'L'
                THEN arq.REP_LOG
                WHEN aqq.TYPE_REP = 'N'
                THEN arq.REP_NUM
                WHEN aqq.TYPE_REP = 'T'
                THEN (
                    SELECT aqr.TXT_REP 
                    FROM AO_QST_R aqr 
                    WHERE aqr.NO_AO = aqq.NO_AO AND aqr.ID_THEME = aqq.ID_THEME AND aqr.ID_QUEST = aqq.ID_QUEST AND aqr.ID_REP = arq.REP_TAB
                )
                ELSE ISNULL(m.CONTMEMO, '')
            END AS REPONSE, arq.REP_TAB, aqq.TYPE_REP, af.CODE_FOU, {$contribSelect} AS CONTRIB, aqq.NUM_FORMAT, aqq.NUM_MIN, aqq.NUM_MAX, arq.TERMINE, 
            ISNULL(comment.CONTMEMO, '') AS COMMENTAIRE
        FROM AO_QST_Q aqq 
            LEFT JOIN AO_FOUR af on af.NO_AO = aqq.NO_AO 
			INNER JOIN AO_QST_TH aqt on aqt.NO_AO = aqq.NO_AO AND aqt.ID_THEME = aqq.ID_THEME AND aqt.ID_THEME LIKE :THEME_LIKE
                {$joinGroup}
            LEFT JOIN AO_REP_QST arq ON aqq.NO_AO = arq.NO_AO AND aqq.ID_THEME = arq.ID_THEME AND aqq.ID_QUEST = arq.ID_QUEST 
                AND arq.CODE_FOU = af.CODE_FOU
                {$joinRep}
            LEFT JOIN MEMOS m ON m.NODEMEMO = arq.REP_OUV_NM
            LEFT JOIN MEMOS comment ON comment.NODEMEMO = arq.NM_COM
        WHERE aqq.NO_AO = :NO_AO
            AND aqt.ID_THEME <> 'APRIX'
            {$whereContrib}
        ORDER BY af.CODE_FOU ASC";

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

    /**
     * getThemeTree
     *
     * @param  string $noAo
     * @param  string $type
     * @param  array<mixed> $params
     * @return array<mixed>
     */
    public function getThemeTree(string $noAo, string $type, array $params = []): ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':NO_AO' => $noAo,
            ':THEME_LIKE' => $type == 'contributors' ? 'I%' : 'A%',
        ];
        $whereContrib = '';
        $joinContrib = '';

        if ($type == 'contributors') {
            if (isset($params['contrib']) && $params['contrib'] != '') {
                $joinContrib = " LEFT JOIN AO_CONTRIB ac WITH (NOLOCK) ON ac.NO_AO = aqt.NO_AO AND aqt.PROFNOTEUR1 = ''
                            LEFT JOIN AO_CONTRIB_GRP acg on acg.NO_AO = aqt.NO_AO AND aqt.PROFNOTEUR1 = 'G' AND aqt.PROFNOTEUR2 = acg.GROUPE_CONTRIB ";
                $user = $params['contrib'];
                $whereContrib = " AND CASE 
                    WHEN aqt.PROFNOTEUR1 = 'G' 
                    THEN acg.CONTRIB 
                    WHEN aqt.PROFNOTEUR1 = 'N'
                    THEN aqt.PROFNOTEUR2
                    WHEN aqt.PROFNOTEUR1 = ''
                    THEN ac.CONTRIB 
                END = :CONTRIB ";
                $arrayReq[':CONTRIB'] = $user;
            }
        }

        $req = "WITH ThemeTree (NO_AO, TREE, ID_PERE, ID_THEME, LIB_THEME, niveau, ordretri)  
            AS (  
                SELECT aqt.NO_AO, CONVERT(VARCHAR(255),aqt.ID_THEME) TREE, aqt.ID_PERE, aqt.ID_THEME, aqt.LIB_THEME, 0 AS niveau, 
                    CONVERT(VARCHAR(255),aqt.ID_THEME+'|') ordretri 
                FROM AO_QST_TH aqt
                    {$joinContrib}
                WHERE aqt.NO_AO = :NO_AO AND aqt.ID_PERE = '' AND aqt.ID_THEME <> 'APRIX' AND aqt.ID_THEME LIKE :THEME_LIKE {$whereContrib}
                    UNION ALL
                SELECT e.NO_AO, CONVERT(VARCHAR(255), '' + e.ID_THEME), e.ID_PERE, e.ID_THEME, e.LIB_THEME, niveau + 1, 
                    CONVERT(VARCHAR(255), ordretri + e.ID_THEME) 
                FROM AO_QST_TH e
                    JOIN ThemeTree d ON e.ID_PERE = d.ID_THEME AND e.NO_AO = d.NO_AO
            )   
            SELECT NO_AO, TREE, ID_PERE, ID_THEME, LIB_THEME, niveau, ordretri, '0' COUNT_QUEST
            FROM ThemeTree  
            ORDER BY ordretri";

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

    /**
     * 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);
    }

    /**
     * Summary of getFouRecap
     * @param string $noDA
     * @param string $code
     * @return array<mixed>
     */
    public function getFouRecap(string $noDA, string $code)
    {
        $param = [];
        $mainReq = $this->listDocFouSql($noDA, $code, $param);

        $row = $this->fetchAssocOne(
            'WITH ' . $mainReq['with'] . ','
                . "Main as ("
                . ' SELECT ' . $mainReq['select']
                . ' FROM ' . $mainReq['from']
                . ' ' . $mainReq['join']
                . ' WHERE ' . $mainReq['where']
                . ")
            SELECT
                SUM(CASE WHEN ( -- Required
                    Main.MODE_CONTROL = 2
                ) THEN 1 ELSE 0 END) as countError,
                SUM(CASE WHEN ( -- Non bloquant
                    Main.MODE_CONTROL = 1
                ) THEN 1 ELSE 0 END) as countWarning
            FROM Main
            LEFT JOIN (SELECT CAST(:now as varchar) as now) as DateNow ON 1=1
            WHERE
                COALESCE(Main.MODE_CONTROL, '') != ''
                -- Status not 'valid'
                AND NOT (
                    COALESCE(Main.NOM_FIC, '') != '' 
                    AND (
                        Main.DT_VALID > DateNow.now
                        OR (Main.DT_PARUT != '00000000' AND Main.DT_VALID = '00000000')
                    )
                )
            ",
            $param + ['now' => date('Ymd')]
        );

        return [
            'countWarning' => intval($row['countWarning']),
            'countError' => intval($row['countError']),
        ];
    }

    /**
     * Summary of listDocFouSql
     * @param array<mixed> $param
     * @return array{from: string, join: string, select: string, where: string, with: string}
     */
    private function listDocFouSql(
        string $noDA,
        string $code,
        array &$param
    ): array {
        $param += [
            ':entityId_0' => $code,
        ];

        $selectFourniss = ($noDA == 'FOURNISS'
            ? "SELECT 
                    'FOURNISS' as Type, 
                    CODE as Id, 
                    CODE as BaseId, 
                    CODE_PAYS as Pays 
                FROM FOURNISS with(nolock)
                WHERE CODE = :entityId_0"
            : "SELECT 
                    'WFT_DEM_ENT' as Type,
                    CODE_DEMANDE as Id,
                    CODE_ENREG as BaseId, 
                    VALEUR_CHAMP as Pays
                FROM WFT_DEM_ENT with(nolock)
                LEFT JOIN WFT_DEM_LIG with(nolock) 
                    ON WFT_DEM_LIG.TYPE_ENT = WFT_DEM_ENT.TYPE_ENT
                    AND WFT_DEM_LIG.CODE_DEMANDE = WFT_DEM_ENT.CODE_DEMANDE
                    AND WFT_DEM_LIG.CODE_CHAMP = 'CODE_PAYS'
                WHERE WFT_DEM_ENT.TYPE_ENT = 'FOURNISS'
                    AND WFT_DEM_ENT.CODE_DEMANDE = :entityId_0");


        return [
            'with' => "Fourniss as ({$selectFourniss})",
            'select' =>
            "Canva.MODE_CONTROL,
                Canva.Corresp as Matching,
                case
                    when (
                        (
                            doc.CODE_CAT IS NULL
                            OR TRIM(doc.CODE_CAT) =''
                        )
                        AND Canva.CODE_CATEGORIE is not null
                        AND (Canva.CODE_CATEGORIE) !=''
                    ) THEN Canva.CODE_CATEGORIE
                    when (
                        (
                            doc.CODE_CAT IS NULL
                            OR TRIM(doc.CODE_CAT) =''
                        )
                        and (
                            Canva.CODE_CATEGORIE is null
                            or (Canva.CODE_CATEGORIE) = ''
                        )
                    )  then 'CAT99'
                    when doc.CODE_CAT = 'Autre' then 'CAT99'
                    ELSE doc.CODE_CAT
                end CODE_CAT,
                COALESCE(doc.TITRE, Canva.TITRE_DOC) as TITRE,
                doc.COD_ENR COD_ENR,
                COALESCE(Canva.NUM_ORDRE, -1) as NumOrder,
                doc.NO_ORDRE,
                DOC_CAT.LIBELLE_CAT LIBELLE_CAT,
                CENTRE.RAIS_SOC RAIS_SOC,
                Canva.Partenaire as CodePartenaire,
                CENTRE.CODE_CTR CODE_CTR,
                HASTUTIL.NOM NOM,
                HASTUTIL.CODE AUTEUR,
                doc.DT_PARUT DT_PARUT,
                doc.DT_VALID DT_VALID,
                DTE_DERNREL,
                doc.NOM_FIC,
                doc.DOC_INUTILE,
                doc.NPRELANCE,
                LTRIM(Canva.NUM_ORDRE) ORDRE_PARAM,
                MEMOS.CONTMEMO COMMENTAIRE",
            "from" => "Fourniss",
            "join" =>
            "JOIN DOC_ATT as doc with(nolock) 
                    ON doc.NO_DA = Fourniss.Type 
                    AND doc.COD_ENR = Fourniss.Id
                FULL JOIN (
                    SELECT
                        distinct(LST_DOCS.CODE_DOC),
                        LST_DOCS.TITRE_DOC,
                        LST_DOCS.CODE_CATEGORIE,
                        LST_DOCS.NUM_ORDRE,
                        LST_DOCS.A_SURVEILLER,
                        
                        LST_DOC_DEM.MODE_CONTROL,
                        PartnMatching.Partenaire,
                        PartnMatching.Corresp
                    FROM LST_DOCS with(nolock)
                    JOIN Fourniss ON 1=1
                    CROSS APPLY ( /* JOIN TOP 1 */
                        SELECT TOP 1 
                            LST_DOC_DEM.CODE_DOC,
                            MAX(LST_DOC_DEM.MODE_CONTROL) as MODE_CONTROL
                        FROM LST_DOC_DEM with(nolock) 
                        WHERE LST_DOCS.CODE_DOC = LST_DOC_DEM.CODE_DOC
                            AND (
                                (CODE_TABLE='COUNTRY' AND CODE_ENREG = Fourniss.Pays)
                                OR (CODE_TABLE='DOM_COMP' and CODE_ENREG in (
                                    select DISTINCT(CODE_DC) from SSFAMFOU with(nolock)
                                    left join D_C_FAM_SSFAM with(nolock) on D_C_FAM_SSFAM.CODE = SSFAMFOU.CODE_SFAM
                                    where SSFAMFOU.CODE_FOUR = Fourniss.Id and CODE_DC is not null
                                ))
                            )
                        GROUP BY LST_DOC_DEM.CODE_DOC
                    ) as LST_DOC_DEM
                    LEFT JOIN (
                        SELECT 
                            Corresp.ID1 as Partenaire,
                            Corresp.ID2 as CodeDoc,
                            Corresp.LIBELLE Corresp
                        FROM Fourniss
                        JOIN FOUR_PARTENAIRE_DATA as FourPartn 
                            ON FourPartn.CODE_FOU = Fourniss.BaseId
                        JOIN PARAMDICO as Corresp with (nolock)
                            ON Corresp.ID1 = FourPartn.CODEPARTENAIRE
                            AND Corresp.TYPEDICO = 'DOCATTFNR_LSTDOCS_PARTNER'
                    ) as PartnMatching ON PartnMatching.CodeDoc = LST_DOCS.CODE_DOC
                ) Canva  ON Canva.TITRE_DOC = doc.TITRE
                left join DOC_CAT with (nolock) on DOC_CAT.CODE_CAT = doc.CODE_CAT
                left join CENTRE with (nolock) on CENTRE.CODE_CTR = doc.CENTRE
                left join HASTUTIL with (nolock) on HASTUTIL.CODE = doc.AUTEUR
                LEFT join MEMOS with (nolock) on MEMOS.NODEMEMO = doc.NM_COMM",
            'where' => "1=1",
        ];
    }
}
