<?php

namespace Modules\Evaluation\Repository;

use Helpers\Filter\FilterTable;
use Helpers\Outil\Sql;
use Helpers\User\User;
use PDO;
use Helpers\QueryBuilder\AbsRepository;

class Evaluation extends AbsRepository
{

    /**
     * getThemeParUser
     *
     * @param  string $idCamp
     * @return array<mixed>
     */
    public function getThemeParUser(string $idCamp): ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':ID_CAMP' => $idCamp,
            ':ID_PERE' => ''
        ];

        $req = "SELECT ID_THEME, LIB_THEME, ID_PERE
                FROM CAMP_QST_TH with(nolock)
                WHERE ID_CAMP = :ID_CAMP
                    AND ID_PERE = :ID_PERE
                ORDER BY NM_ORDRE
            ";

        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<mixed> $param
     * @return string
     */
    private function getSqlNotation(array &$param, bool $visu = false): string
    {
        $now   = date('YmdHis');
        $param += [
            ':noteur' => $_SESSION['Session_Login'],
            ':CURRENT_DATE_0' => $now,
            ':CURRENT_DATE_1' => $now,
        ];

        return "SELECT DISTINCT crq.ID_CAMP, ce.LIBELLE,
                ce.CREEEPAR, ha.NOM, ha.TEL, ha.EMAIL,
                ce.DTECREAT, ce.DTEVALID, ce.DTEFIN_PREVUE, ce.DTEFIN,
                p.LIBELLE as LIBELLE_PERIODE
            FROM CAMP_REP_QST crq with(nolock)
            JOIN CAMP_ENT ce with(nolock)
                ON crq.ID_CAMP = ce.ID_CAMP
            JOIN HASTUTIL ha with(nolock)
                ON ce.CREEEPAR = ha.CODE
            LEFT JOIN PERIODES p with(nolock)
                ON p.CODE_PER = ce.COD_PER
            LEFT JOIN CAMP_NOT_STATUS cns with(nolock)
                ON cns.ID_CAMP = crq.ID_CAMP 
                    AND cns.CODE_FOU = crq.CODE_FOU 
                    AND cns.COD_NOTEUR = crq.COD_NOTEUR
            WHERE 
                NOT_FOUR = 0x46
                AND cns.TERMINE = " . ($visu ? "0x54" : "0x46") . "
                AND crq.COD_NOTEUR = :noteur
                AND ce.DTEVALID <> '000000000'
                AND (
                    CONCAT(ce.DTEDEB_PREVUE, ce.HEUREDEB_PREVUE) <= :CURRENT_DATE_0
                    and CONCAT(ce.DTEFIN_PREVUE, ce.HEUREFIN_PREVUE) >= :CURRENT_DATE_1
                )
                AND crq.QUEST_ACTIF = 0x54 ";
    }

    /**
     * getNotationParUser
     *
     * @param  array<mixed> $post
     * @return array<mixed>|null
     */
    public function getNotation(array $post, bool $visu): ?array
    {
        /**
         * Creation de la requete
         */
        $dateFin = '00000000';

        $arrayReq = [];
        $req      = $this->getSqlNotation($arrayReq, $visu);

        /**
         * Prise en compte de la pagination et des filtres
         */
        $whereArray   = FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(0);
        $filtreAvance->pushArray($arrayReq);

        if (count($whereArray) > 0) {
            if ($whereArray['tri'] ?? '' != '') {
                $req .= $whereArray[0] . $whereArray['tri'];
            } else {
                $req .= $whereArray[0];
            }

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

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }

    public function countNotation(): int
    {
        $param = [];
        return intval($this->fetchAssocOne(
            "SELECT COUNT(*) as count 
            FROM (" . $this->getSqlNotation($param, false) . ") as main",
            $param
        )['count'] ?? 0);
    }


    // /**
    //  * getNumberOfEvaluationsPerUser
    //  *
    //  * @param  string|null $user
    //  * @return int
    //  */
    // public function getNumberOfEvaluationsPerUser($user)
    // {
    //     $query = "SELECT COUNT(DISTINCT crq.ID_CAMP) AS COUNTING
    //     FROM CAMP_REP_QST crq with(nolock)
    //         INNER JOIN CAMP_ENT ce with(nolock)
    //             ON crq.ID_CAMP = ce.ID_CAMP
    //         LEFT OUTER JOIN CAMP_NOT_STATUS cns with(nolock)
    //             ON cns.ID_CAMP = crq.ID_CAMP
    //                 AND cns.CODE_FOU = crq.CODE_FOU
    //                 AND cns.COD_NOTEUR = crq.COD_NOTEUR
    //     WHERE crq.NOT_FOUR = 0x46
    //         AND crq.COD_NOTEUR =  :NOTEUR
    //         AND crq.QUEST_ACTIF = 0x54
    //         AND ce.DTEFIN = '00000000'
    //         AND cns.TERMINE = 0x46";

    //     $query = $this->pdo->prepare($query);

    //     $query->bindParam(':NOTEUR', $user, \PDO::PARAM_STR);

    //     $query->execute();

    //     return $query->fetch(\PDO::FETCH_ASSOC)['COUNTING'] ?? '';
    // }


    /**
     * getArboQuestion
     *
     * @param  string $idCamp
     * @return array<mixed>|null
     */
    public function getArboQuestion(string $idCamp): ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':idCamp1' => $idCamp,
            ':idCamp2' => $idCamp,
        ];

        $req = "WITH ArboThemes (arbre,ID_PERE, ID_THEME,LIB_THEME, niveau,ordretri)  
        AS   
        (  
            SELECT CONVERT(VARCHAR(255),ID_THEME) arbre, ID_PERE, ID_THEME,LIB_THEME, 0 AS niveau, CONVERT(VARCHAR(255),ID_THEME) ordretri 
            FROM CAMP_QST_TH 
            WHERE ID_PERE='' 
                AND ID_CAMP = :idCamp1
        UNION ALL
            SELECT 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 CAMP_QST_TH e
                INNER JOIN ArboThemes d 
                    ON e.ID_PERE=d.ID_THEME
            WHERE ID_CAMP = :idCamp2
        )   
        SELECT arbre,ID_PERE, ID_THEME,LIB_THEME, niveau,ordretri,'0' COUNT_QUEST
        FROM ArboThemes  
        ORDER BY ordretri";


        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }



    /**
     * getDetailEvaluationEnCoursParFournisseur
     *
     * @param  string $idUser
     * @param  array<mixed> $post
     * @param  string $idCamp
     * @return array<mixed>|null
     */
    public function getDetailEvaluationEnCoursParFournisseur(string $idUser, array $post, string $idCamp, bool $visu = false): ?array
    {

        /**
         * Creation de la requete
         */
        $dateFin = '00000000';

        $arrayReq = [
            ':noteur' => $idUser,
            ':idCamp' => $idCamp
        ];
        if (!$visu) {
            $arrayReq['dateFin'] = $dateFin;
        }
        $req = "SELECT DISTINCT crq.ID_CAMP, ce.LIBELLE,DESCRIPT,
                ce.CREEEPAR, ha.NOM, ha.TEL, ha.EMAIL,
                ce.DTECREAT, ce.DTEVALID, ce.DTEFIN_PREVUE, ce.DTEFIN,
                crq.CODE_FOU,f.RS, f.NO_SIRET, 
                    CASE	
                        WHEN cns.TERMINE = 0x54
                        THEN 1
                        ELSE 0
                    END AS VISU
            FROM CAMP_REP_QST crq with(nolock)
                INNER JOIN CAMP_ENT ce with(nolock)
                    ON crq.ID_CAMP = ce.ID_CAMP
                INNER JOIN HASTUTIL ha with(nolock)
                    ON ce.CREEEPAR = ha.CODE
                LEFT JOIN FOURNISS f with(nolock)
                    ON f.CODE = crq.CODE_FOU
				LEFT JOIN CAMP_NOT_STATUS cns with(nolock)
					ON cns.ID_CAMP = crq.ID_CAMP
					AND cns.COD_NOTEUR = crq.COD_NOTEUR
					AND cns.CODE_FOU = crq.CODE_FOU
            WHERE NOT_FOUR = 0x46
                -- AND TERMINE = 0x46
                AND crq.COD_NOTEUR =  :noteur
                " . (!$visu ? "AND ce.DTEFIN = :dateFin" : "") . " 
                AND  crq.QUEST_ACTIF = 0x54
                AND crq.ID_CAMP = :idCamp";

        /**
         * Prise en compte de la pagination et des filtres
         */
        $whereArray   = FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(0);
        $filtreAvance->pushArray($arrayReq);

        if (count($whereArray) > 0) {
            if ($whereArray['tri'] ?? '' != '') {
                $req .= $whereArray[0] . $whereArray['tri'];
            } else {
                $req .= $whereArray[0];
            }

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

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

        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * getQuesionForReponseForm
     *
     * @param  string $idUser
     * @param  string $codeFrs
     * @param  string $idCamp
     * @param  int $externe
     * @param  bool $isFrs
     * @param  bool $stripper
     * @return array<mixed>
     */
    public function getQuesionForReponseForm(string $idUser, string $codeFrs, string $idCamp, int $externe = 0, bool $isFrs = false, bool $stripper = false): ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':codeFrs' => $codeFrs,
            ':idCamp' => $idCamp
        ];

        if (!$stripper) {
            $arrayReq[':noteur'] = $idUser;
        }

        if ($externe == 1) {
            $whereClause = " AND crq.NOTEUR_EXTERNE = 0x54 ";
        } elseif ($externe == 2) {
            $whereClause = " AND cqq.QUEST_FOUR = 0x54 ";
        } elseif ($externe == 0) {
            $whereClause = " AND ((cqq.QUEST_FOUR = 0x54 AND ha.CODE IS NULL) OR (cqq.QUEST_FOUR = 0x46 AND ha.CODE IS NOT NULL)) ";
        } else {
            $whereClause = "";
        }

        if ($codeFrs != '') {
            $whereClause .= " AND crq.CODE_FOU = :codeFrs ";
        }

        $req = "SELECT crq.QUEST_ACTIF, crq.CODE_FOU, crq.COD_NOTEUR, crq.TERMINE, OUVNONAPPL,cqdTmp.ID_QST_PARENT,OUV_NAPOSS,crq.COMMENT as comRep, REP_TAB, REP_LOG, NOTE,crq.ID_CAMP, crq.ID_THEME,  crq.ID_QUEST, crq.ID_THEME, crq.REP_NUM,  crq.REP_OUV, crq.COMMENTAIRE, cqq.G_B_QST_LIE, cqq.LIB_QUEST,OUV_NOTEPAR,
                cqq.TYPE_REP, cqq.NOTE_FAUX, cqq.NOTE_VRAI, cqq.NUM_MIN, cqq.NUM_MAX, cqq.NUM_FORMAT, cqq.AUTHCOMMENT, REGCOMOBLIG, cqq.DESCRIPTION, cqq.QST_OBLIG, th.LIB_THEME, th.ID_PERE
            FROM CAMP_REP_QST crq
                LEFT JOIN HASTUTIL ha ON crq.COD_NOTEUR = ha.CODE
                JOIN CAMP_QST_Q cqq with(nolock)
                    ON crq.ID_CAMP = cqq.ID_CAMP 
                        AND crq.ID_THEME = cqq.ID_THEME 
                        AND crq.ID_QUEST = cqq.ID_QUEST
                JOIN CAMP_QST_TH th with(nolock)
                    ON th.ID_THEME = crq.ID_THEME 
                        AND th.ID_CAMP =  cqq.ID_CAMP
                LEFT JOIN CAMP_QST_DEP cqd with(nolock)
                    ON cqd.ID_CAMP = cqq.ID_CAMP 
                        AND cqd.ID_QST_DEP = cqq.ID_QUEST 
                        AND cqd.ID_THEME_DEP = cqq.ID_THEME
                LEFT JOIN CAMP_QST_DEP cqdTmp with(nolock)
                    ON cqdTmp.ID_CAMP = cqq.ID_CAMP 
                        AND cqdTmp.ID_QST_PARENT = cqq.ID_QUEST 
                        AND cqdTmp.ID_THEME_PARENT = cqq.ID_THEME
            WHERE 
                " . ($isFrs == true ? ' crq.NOT_FOUR = 0x54 ' : ' crq.NOT_FOUR = 0x46 ') . " 
                " . ($stripper ? ' ' : ' AND crq.COD_NOTEUR =  :noteur   ') . " 
                AND crq.ID_CAMP = :idCamp
                AND crq.QUEST_ACTIF = 0x54
                AND cqq.TYPE_REP <> 'C'
                {$whereClause}
            ORDER BY th.NM_ORDRE,cqq.NM_ORDRE ASC";
        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * getCanEdit
     * @param string $idUser
     * @param string $codeFrs
     * @param string $idCamp
     * @param bool $externe
     * @return array<mixed>|null
     */
    public function getCanEdit(string $idUser, string $codeFrs, string $idCamp, bool $externe = false): ?array
    {
        $arrayReq = [
            ':noteur' => $idUser,
            ':codeFrs' => $codeFrs,
            ':idCamp' => $idCamp
        ];

        $req = "SELECT TERMINE
            FROM CAMP_NOT_STATUS with(nolock)
            WHERE COD_NOTEUR =  :noteur
                AND CODE_FOU = :codeFrs
                AND ID_CAMP = :idCamp
                AND NOTEUR_EXTERNE = " . ($externe ? "0x54" : "0x46");

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

    /**
     * Summary of listeQuestionDepouilleur
     * @param string $idCamp
     * @return string[][]
     */
    public function listeQuestionDepouilleur(string $idCamp): ?array
    {
        $arrayReq = [
            ':idCamp' => $idCamp,
            // permet de savoir si la question ouverte a déjà été noté
            ':ouvNote' => Sql::boolSql2(false),
            ':termine' => Sql::boolSql2(true),
        ];

        $req = "SELECT
                    cqq.ID_CAMP,
                    cqq.ID_QUEST,
                    cqq.ID_THEME,
                    cqq.OUV_NOTEPAR,
                    crq.OUV_NOTE
                from
                    CAMP_QST_Q cqq
                join CAMP_REP_QST crq ON cqq.ID_CAMP = crq.ID_CAMP
                    and cqq.ID_QUEST = crq.ID_QUEST
                    and cqq.ID_THEME = crq.ID_THEME
                where
                    cqq.OUV_NOTEPAR = 'C'
                and crq.OUV_NOTE = :ouvNote
                and crq.ID_CAMP = :idCamp
                and crq.DTE_REP != '00000000'
                and crq.TERMINE = :termine
            ";

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

    /**
     * getUserByCampagne
     *
     * @param  string $idCamp
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getUserByCampagne(string $idCamp, array $post): ?array
    {
        $arrayReq = [
            ':idCamp' => $idCamp,
        ];

        // TODO : simplify
        // This col is in some context requiered by the filter, but we can't include it in all context due to the DISTINCT
        $colCodeFou = !empty($post['filter_egal_CODE_FOU']) ? 'crq.CODE_FOU,' : '';

        $colType = ($post['groupByType'] ?? false == true) ? 'crq.NOTEUR_EXTERNE, crq.NOT_FOUR, cqq.QUEST_FOUR,' : '';

        $req = "SELECT * FROM (
                SELECT DISTINCT
                    crq.COD_NOTEUR,  {$colCodeFou}
                    COALESCE(ha.NOM, cn.NOM_NOTEUR, Contact.NOM, '') as NOM, 
                    COALESCE(ha.EMAIL, cn.EMAIL_NOTEUR, Contact.EMAIL) as EMAIL,
                    COALESCE(ha.FONCTION, cn.FCT_NOTEUR, Contact.FONCTION) as FONCTION,
                    COALESCE(fr.RS, cn.SOC_NOTEUR, soc.RAIS_SOC) as CODE_STE,
                    {$colType}
                    CampStat.nbrTermine,
                    CampStat.nbrTotal,
                    CampStat.completion
                FROM CAMP_REP_QST crq with(nolock)
                LEFT JOIN HASTUTIL ha with(nolock) ON crq.COD_NOTEUR = ha.CODE
                LEFT JOIN CONTFOUR Contact with(nolock) 
                    ON Contact.CODE_FOU = crq.CODE_FOU 
                    AND crq.NOT_FOUR = 0x54 
                    AND Contact.OXALINK_ID <> ''
                LEFT JOIN OXALINK_USER ou with(nolock) 
                    ON ou.USER_ID = Contact.OXALINK_ID 
                    AND ou.STATUT = 'A' 
                    AND ou.EMAIL IS NOT NULL 
                    AND ou.USER_ID IS NOT NULL
                LEFT JOIN FOURNISS fr with(nolock) 
                    ON fr.CODE = Contact.CODE_FOU
                LEFT JOIN CAMP_NOTEX cn with(nolock) 
                    ON crq.COD_NOTEUR = cn.ID_NOTEUR
                LEFT JOIN SOCIETE soc with(nolock) 
                    ON soc.CODE_STE = ha.CODE_STE
                LEFT JOIN CAMP_QST_Q cqq with(nolock) 
                    ON cqq.ID_CAMP = crq.ID_CAMP 
                    AND cqq.ID_THEME = crq.ID_THEME 
                    AND cqq.ID_QUEST = crq.ID_QUEST 
                LEFT JOIN (
                    SELECT
                        Reponse.ID_CAMP,
                        Reponse.COD_NOTEUR,
                        Question.QUEST_FOUR,
                        COUNT(*) AS nbrTotal,
                        SUM(CASE WHEN Reponse.TERMINE = 0x54 THEN 1 ELSE 0 END) AS nbrTermine,
                        CASE
                            WHEN COUNT(*) > 0 THEN
                                (SUM(CASE WHEN Reponse.TERMINE = 0x54 THEN 1 ELSE 0 END) * 100.0 / COUNT(*))
                            ELSE 0
                        END AS completion
                    FROM CAMP_REP_QST AS Reponse WITH (NOLOCK)
                        LEFT JOIN CAMP_QST_Q AS Question WITH (NOLOCK)
                            ON Question.ID_CAMP = Reponse.ID_CAMP
                                AND Question.ID_THEME = Reponse.ID_THEME
                                AND Question.ID_QUEST = Reponse.ID_QUEST
                    WHERE Reponse.QUEST_ACTIF = 0x54
                        AND Reponse.REP_CALC = '' -- Réponse non calculée
                    GROUP BY Reponse.ID_CAMP, Reponse.COD_NOTEUR, Question.QUEST_FOUR
                ) as CampStat 
                    ON CampStat.ID_CAMP = crq.ID_CAMP
                    AND CampStat.COD_NOTEUR = crq.COD_NOTEUR 
                    AND CampStat.QUEST_FOUR = cqq.QUEST_FOUR
                WHERE crq.ID_CAMP = :idCamp
                    AND crq.REP_CALC = ''
                    AND crq.ID_QUEST <> 0
            ) AS Tmp
            WHERE 1=1 ";


        if (isset($post['filter_FONCTION']) && $post['filter_FONCTION'] != '') {
            $req .= " AND (ha.FONCTION LIKE :fonction1 OR cn.FCT_NOTEUR LIKE :fonction2) ";
            $arrayReq[':fonction1'] = $post['filter_FONCTION'];
            $arrayReq[':fonction2'] = $post['filter_FONCTION'];
            unset($post['filter_FONCTION']);
        }
        if (isset($post['filter_NOTEUR_EXTERNE']) && $post['filter_NOTEUR_EXTERNE'] != '') {
            switch ($post['filter_NOTEUR_EXTERNE']) {
                case '1': // Externe
                    $req .= " AND NOTEUR_EXTERNE = 0x54 ";
                    break;
                case '2': // Interne
                    $req .= " AND NOTEUR_EXTERNE = 0x46 AND  NOT_FOUR = 0x46 AND QUEST_FOUR = 0x46";
                    break;
                case '3': // Fournisseur Oxalink
                    $req .= " AND NOT_FOUR = 0x54 ";
                    break;
                case '4': // Fournisseur non Oxalink
                    $req .= " AND NOT_FOUR = 0x46 AND QUEST_FOUR = 0x54 ";
                    break;
            }
            if ($post['filter_NOTEUR_EXTERNE'] == 1) {
                $req .= " AND NOTEUR_EXTERNE = 0x54 ";
            } else {
                $req .= " AND NOTEUR_EXTERNE = 0x46 ";
            }

            unset($post['filter_NOTEUR_EXTERNE']);
        }

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

        $with = "WITH Main as ({$req}),
        Stat as (
            SELECT 
                COUNT(*) as Stat_count,
                SUM(
                    CASE WHEN Main.nbrTermine = Main.nbrTotal THEN 1 ELSE 0 END
                ) as Stat_countTermine
            FROM Main
        )";
        $req  = "SELECT Main.*, Stat.* FROM Main, Stat";

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

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

    /**
     * getDetailUserByCampagne
     *
     * @param  string $idCamp
     * @param  string $user
     * @param  int $externe
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getDetailUserByCampagne(string $idCamp, string $user, int $externe, array $post): ?array
    {

        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':idCamp' => $idCamp,
            ':user' => $user
        ];

        if ($externe == 1) {
            $whereClause = " AND crq.NOTEUR_EXTERNE = 0x54 ";
        } elseif ($externe == 2) {
            $whereClause = " AND cqq.QUEST_FOUR = 0x54 ";
        } else {
            $whereClause = " AND (
                (cqq.QUEST_FOUR = 0x54 AND ha.CODE IS NULL) 
                OR (cqq.QUEST_FOUR = 0x46 AND ha.CODE IS NOT NULL)
            ) ";
        }

        $req = "SELECT DISTINCT 
            crq.CODE_FOU,
            crq.COD_NOTEUR,
            crq.NOTEUR_EXTERNE,
            crq.NOT_FOUR,

            ha.NOM,
            ha.EMAIL,
            ha.FONCTION,
            ha.CODE_STE,

            cn.NOM_NOTEUR,
            cn.EMAIL_NOTEUR,
            cn.FCT_NOTEUR,
            cn.SOC_NOTEUR,
            
            soc.RAIS_SOC,
            soc.SIRET,
            fr.NO_SIRET,
            fr.RS,
            Stat.nbrTermine,
            Stat.nbrTotal,
            cqq.QUEST_FOUR
        FROM CAMP_REP_QST crq with(nolock)
            LEFT JOIN HASTUTIL ha with(nolock) ON crq.COD_NOTEUR = ha.CODE
            LEFT JOIN CAMP_QST_Q cqq with(nolock) ON cqq.ID_CAMP = crq.ID_CAMP AND cqq.ID_THEME = crq.ID_THEME AND cqq.ID_QUEST = crq.ID_QUEST
            LEFT JOIN CAMP_NOTEX cn with(nolock) ON crq.COD_NOTEUR = cn.ID_NOTEUR
            LEFT JOIN SOCIETE soc with(nolock) ON soc.CODE_STE = ha.CODE_STE
            LEFT JOIN FOURNISS fr with(nolock) ON fr.CODE = crq.CODE_FOU

            LEFT JOIN (
                SELECT 
                    COUNT(*) as nbrTotal,
                    SUM(CASE WHEN TERMINE = 0x54 OR (TERMINE = 0x46 AND Question.QST_OBLIG = 0x46) THEN 1 ELSE 0 END) as nbrTermine,
                    Reponse.ID_CAMP,
                    Reponse.COD_NOTEUR,
                    Reponse.CODE_FOU,
                    Question.QUEST_FOUR
                FROM CAMP_REP_QST as Reponse with(nolock)
                LEFT JOIN CAMP_QST_Q as Question with(nolock) 
                    ON Question.ID_CAMP = Reponse.ID_CAMP 
                    AND Question.ID_THEME = Reponse.ID_THEME 
                    AND Question.ID_QUEST = Reponse.ID_QUEST
                WHERE Reponse.QUEST_ACTIF = 0x54 AND Reponse.REP_CALC = ''
                GROUP BY 
                    Reponse.COD_NOTEUR, Reponse.ID_CAMP, Reponse.CODE_FOU,
                    Question.QUEST_FOUR 
            ) AS Stat 
                ON Stat.COD_NOTEUR = crq.COD_NOTEUR 
                and Stat.QUEST_FOUR = cqq.QUEST_FOUR
                and Stat.CODE_FOU = crq.CODE_FOU
                and Stat.ID_CAMP = crq.ID_CAMP
        WHERE crq.ID_CAMP = :idCamp
            AND crq.COD_NOTEUR = :user
            {$whereClause}";

        /**
         * Prise en compte de la pagination et des filtres
         */
        $whereArray   = FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(0);
        $filtreAvance->pushArray($arrayReq);

        if (count($whereArray) > 0) {
            if ($whereArray['tri'] ?? '' != '') {
                $req .= $whereArray[0] . $whereArray['tri'];
            } else {
                $req .= $whereArray[0];
            }

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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


    /**
     * getFournisseurByCamp
     *
     * @param  string $idCamp
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getFournisseurByCamp(string $idCamp, array $post): array
    {
        $arrayReq = [':idCamp' => $idCamp];
        $req      = "SELECT DISTINCT 
            clf.CODE_FOU,
            fr.NO_SIRET,fr.RS,
            QuestStat.nbrTermine,
            QuestStat.nbrTotal
        FROM CAMP_LST_FOUR clf
        LEFT JOIN FOURNISS fr with(nolock) ON fr.CODE = clf.CODE_FOU
		LEFT JOIN (
            SELECT 
                count(*) as nbrTotal,
                case when LstFour.TERMINE = 0x54 then count(*) else sum(case when crq.TERMINE = 0x54 then 1 else 0 end) end as nbrTermine,
                crq.ID_CAMP,
                crq.CODE_FOU 
            from CAMP_REP_QST crq with(nolock)
			LEFT JOIN CAMP_QST_Q cqq ON cqq.ID_CAMP = crq.ID_CAMP AND cqq.ID_QUEST = crq.ID_QUEST AND cqq.ID_THEME = crq.ID_THEME
            LEFT JOIN CAMP_LST_FOUR as LstFour with (nolock) on LstFour.ID_CAMP = crq.ID_CAMP and LstFour.CODE_FOU = crq.CODE_FOU
            where QUEST_ACTIF = 0x54
            group by QUEST_ACTIF,crq.ID_CAMP,crq.CODE_FOU,LstFour.TERMINE
        ) as QuestStat 
            ON QuestStat.ID_CAMP = clf.ID_CAMP 
            AND QuestStat.CODE_FOU = clf.CODE_FOU
        WHERE clf.ID_CAMP = :idCamp";

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

        $with = "WITH Main as ({$req}),
        Stat as (
            SELECT 
                COUNT(*) as Stat_count,
                SUM(
                    CASE WHEN Main.nbrTermine = Main.nbrTotal THEN 1 ELSE 0 END
                ) as Stat_countTermine
            FROM Main
        )";
        $req  = "SELECT Main.*, Stat.* FROM Main, Stat";

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

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

    /**
     * validerReponseParCampagne
     *
     * @param  string $idCamp
     * @param  string $idFrs
     * @return bool
     */
    public function validerReponseParCampagne(string $idCamp, string $idFrs, string $idQuestion, string $idTheme, bool $terminer = true): bool
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':DTE_REP' => date('Ymd'),
            ':ID_CAMP' => $idCamp,
            ':CODE_FOU' => $idFrs,
            ':COD_NOTEUR' => $_SESSION['Session_Login'],
            ':ID_QUEST' => $idQuestion,
            ':ID_THEME' => $idTheme
        ];

        $req = "UPDATE CAMP_REP_QST
        SET TERMINE = " . ($terminer ? "0x54" : "0x46") . ",
            DTE_REP = :DTE_REP
        WHERE ID_CAMP = :ID_CAMP 
            AND CODE_FOU = :CODE_FOU
            AND COD_NOTEUR = :COD_NOTEUR
            AND ID_QUEST = :ID_QUEST
            AND ID_THEME = :ID_THEME";

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

        return $stmt->execute($arrayReq);
    }


    /**
     * updateCampQstQ
     */
    public function updateCampRepQstRepLog(string $idCamp, string $idFrs, string $idQuest, string $idTheme, bool $repLog, string $note): bool
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':ID_CAMP' => (int) $idCamp,
            ':ID_QUEST' => (int) $idQuest,
            ':CODE_FOU' => $idFrs,
            ':COD_NOTEUR' => $_SESSION['Session_Login'],
            ':ID_THEME' => $idTheme,
            ':NOTE' => (int) $note
        ];

        $req = "UPDATE CAMP_REP_QST
        SET REP_LOG = " . ($repLog ? '0x54' : '0x46') . ", TERMINE = 0x54,  
                NOTE = :NOTE
            WHERE ID_CAMP = :ID_CAMP 
                AND CODE_FOU = :CODE_FOU
                AND COD_NOTEUR = :COD_NOTEUR
                AND ID_THEME = :ID_THEME
                AND ID_QUEST = :ID_QUEST";
        /**
         * Validation de la requete et execution
         */
        $stmt = $this->pdo->prepare($req);

        return $stmt->execute($arrayReq);
    }


    /**
     * updateCampQstQ
     */
    public function updateCampRepQstNA(string $idCamp, string $idFrs, string $idQuest, string $idTheme, bool $repLog): bool
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':ID_CAMP' => $idCamp,
            ':ID_QUEST' => $idQuest,
            ':CODE_FOU' => $idFrs,
            ':COD_NOTEUR' => $_SESSION['Session_Login'],
            ':ID_THEME' => $idTheme
        ];

        $req = "UPDATE CAMP_REP_QST SET OUVNONAPPL = " . ($repLog ? '0x54' : '0x46') . ", TERMINE = " . ($repLog ? '0x54' : '0x46') . " 
         WHERE ID_CAMP = :ID_CAMP 
            AND CODE_FOU = :CODE_FOU
            AND COD_NOTEUR = :COD_NOTEUR
            AND ID_THEME = :ID_THEME
            AND ID_QUEST = :ID_QUEST";

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

        return $stmt->execute($arrayReq);
    }


    /**
     * getQuestionDependante
     *
     * @param  string $idCamp
     * @param  string $idQstParent
     * @param  string $idThemeParent
     * @param  string $codeFrs
     * @param  bool $other
     * @return array<mixed>
     */
    public function getQuestionDependante(string $idCamp, string $idQstParent, string $idThemeParent, string $codeFrs, bool $other = false): ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':idCamp' => $idCamp,
            ':idCamp2' => $idCamp,
            ':idQstParent' => $idQstParent,
            ':idThemeParent' => $idThemeParent,
            // ':noteur' =>  'NOTEUR',
            // ':noteur' => $_SESSION['Session_Login'],
            ':codeFrs' => $codeFrs
        ];

        $req = "SELECT DISTINCT crq.NOTEUR_EXTERNE,crq.COD_NOTEUR, crq.TERMINE, thP.LIB_THEME as libTheme, OUV_NAPOSS,OUV_NOTEPAR,cqd.*,crq.COMMENT as comRep, REP_TAB, REP_LOG, NOTE,crq.ID_CAMP, crq.ID_THEME,  crq.ID_QUEST,  crq.ID_THEME,  crq.REP_NUM,  crq.REP_OUV, crq.COMMENTAIRE, cqq.G_B_QST_LIE, cqq.LIB_QUEST,
        cqq.TYPE_REP, cqq.NOTE_FAUX, cqq.NOTE_VRAI, cqq.NUM_MIN, cqq.NUM_MAX, cqq.NUM_FORMAT, cqq.AUTHCOMMENT, REGCOMOBLIG, cqq.DESCRIPTION, th.LIB_THEME, th.ID_PERE , th.THEME_LIE, crq.NOT_FOUR, crq.CODE_FOU
        FROM CAMP_QST_DEP cqd with(nolock)
            JOIN CAMP_REP_QST crq with(nolock) on crq.ID_THEME = cqd.ID_THEME_DEP 
                AND crq.ID_QUEST = cqd.ID_QST_DEP 
                AND crq.ID_CAMP = cqd.ID_CAMP 
                -- AND crq.COD_NOTEUR " . ($other ? '!=' : '=') . " :noteur 
                AND crq.CODE_FOU = :codeFrs
            JOIN  CAMP_QST_Q cqq with(nolock) ON crq.ID_CAMP = cqq.ID_CAMP and crq.ID_THEME = cqq.ID_THEME and crq.ID_QUEST = cqq.ID_QUEST
            JOIN CAMP_QST_TH th with(nolock) on th.ID_THEME = crq.ID_THEME and th.ID_CAMP =  cqq.ID_CAMP
            JOIN CAMP_QST_TH thP with(nolock) on thP.ID_THEME = cqd.ID_THEME_PARENT and thP.ID_CAMP = :idCamp2
        WHERE cqd.ID_CAMP = :idCamp
            AND ID_QST_PARENT = :idQstParent
            AND ID_THEME_PARENT = :idThemeParent";

        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * setActifQuestionDependante
     *
     * @param  string $idCamp
     * @param  string $codeFrs
     * @param  bool $activerQuestion
     * @param  array<mixed> $dependanceDesactif
     * @param  array<mixed> $dependanceActif
     * @return bool
     */
    public function setActifQuestionDependante(string $idCamp, string $codeFrs, bool $activerQuestion = true, array $dependanceDesactif = [], array $dependanceActif = []): bool
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':idCamp' => $idCamp,
            ':codeFrs' => $codeFrs
        ];

        $i        = 0;
        $reqWhere = '(';

        foreach ((($activerQuestion == true) ? $dependanceActif : $dependanceDesactif) as $dep) {
            if ($i > 0) {
                $reqWhere .= ' OR ';
            }
            $arrayReq['idThemeDep' . $i] = $dep['ID_THEME'];
            $arrayReq['idQstDep' . $i]   = $dep['ID_QUEST'];

            $reqWhere .= ' (CAMP_QST_DEP.ID_QST_DEP = :idQstDep' . $i . ' AND CAMP_QST_DEP.ID_THEME_DEP = :idThemeDep' . $i . ') ';
            $i++;
        }

        $reqWhere .= ')';

        $req = "UPDATE CAMP_REP_QST
        SET CAMP_REP_QST.QUEST_ACTIF = " . ($activerQuestion ? '0x54' : '0x46') . "
        FROM CAMP_REP_QST INNER JOIN CAMP_QST_DEP
                ON CAMP_REP_QST.ID_THEME = CAMP_QST_DEP.ID_THEME_DEP 
                        AND CAMP_REP_QST.ID_QUEST = CAMP_QST_DEP.ID_QST_DEP 
                        AND CAMP_REP_QST.ID_CAMP = CAMP_QST_DEP.ID_CAMP 
                        AND CAMP_REP_QST.CODE_FOU = :codeFrs
        WHERE CAMP_QST_DEP.ID_CAMP = :idCamp 
            AND " . $reqWhere;



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

        return $stmt->execute($arrayReq);
    }

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

        $arrayReq = [
            ':ID_CAMP' => $idCamp,
            ':ID_THEME' => $idTheme,
            ':ID_QUEST' => $idQuest,
            ':value' => str_replace(' ', '', str_replace(',', '.', $value))
        ];

        $req = "SELECT TOP 1 NOTE, CAST(REPLACE(TXT_REP, ',', '') AS FLOAT) as floatval
            FROM CAMP_QST_R
            WHERE ID_CAMP = :ID_CAMP
                AND ID_THEME = :ID_THEME
                AND ID_QUEST = :ID_QUEST
                AND CAST(:value AS FLOAT) >= CAST(REPLACE(TXT_REP, ',', '') AS FLOAT)
            ORDER BY floatval DESC ";

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


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

        $arrayReq = [
            ':ID_THEME' => $idTheme,
            ':ID_CAMP' => $idCamp,
            ':ID_CAMP2' => $idCamp
        ];

        $req = "WITH first_theme_data (ID_THEME, ID_PERE, LIB_THEME, DESCRIPTION, POIDS, NM_ORDRE)
        AS
        (
            select t1.ID_THEME, t1.ID_PERE, t1.LIB_THEME, t1.DESCRIPTION, t1.POIDS, t1.NM_ORDRE
            from CAMP_QST_TH t1
            where t1.ID_THEME = :ID_THEME AND ID_CAMP = :ID_CAMP
            UNION ALL
            select t2.ID_THEME, t2.ID_PERE, t2.LIB_THEME, t2.DESCRIPTION, t2.POIDS, t2.NM_ORDRE
            from CAMP_QST_TH t2
            inner join first_theme_data on first_theme_data.ID_PERE = t2.ID_THEME
            WHERE ID_CAMP = :ID_CAMP2
        )

        -- references expression name
        SELECT DISTINCT LIB_THEME
        FROM first_theme_data
        wHERE first_theme_data.ID_PERE = ''";

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

    /**
     * Summary of depouilleurHasNotedWithAnswering
     * @param string $idCamp
     * @param string $idFrs
     * @return bool
     */
    public function depouilleurOrEvaluateurHasNotedWhileAnswering(string $idCamp, string $idFrs)
    {

        $req = "UPDATE crq
        SET OUV_NOTE = 0x54
        FROM CAMP_REP_QST crq
        LEFT JOIN CAMP_QST_Q cqq 
            ON cqq.ID_CAMP = crq.ID_CAMP 
            AND cqq.ID_THEME = crq.ID_THEME 
            AND cqq.ID_QUEST = crq.ID_QUEST
        LEFT JOIN CAMP_ENT ce
            ON crq.ID_CAMP = ce.ID_CAMP
        WHERE crq.ID_CAMP = :ID_CAMP 
            AND crq.CODE_FOU = :CODE_FOU
            AND (
                (crq.COD_NOTEUR = :COD_NOTEUR1 AND cqq.OUV_NOTEPAR = 'N')
                OR 
                (crq.COD_NOTEUR = :COD_NOTEUR2 AND ce.CREEEPAR = :COD_NOTEUR3 AND cqq.OUV_NOTEPAR = 'C')
                )
        ";

        $arrayReq = [
            ':ID_CAMP' => $idCamp,
            ':CODE_FOU' => $idFrs,
            ':COD_NOTEUR1' => User::getUser()->getCodeUser(),
            ':COD_NOTEUR2' => User::getUser()->getCodeUser(),
            ':COD_NOTEUR3' => User::getUser()->getCodeUser(),
        ];

        $stmt = $this->pdo->prepare($req);

        return $stmt->execute($arrayReq);
    }

    /**
     * validationQuestionnaire
     *
     * @param  string $idCamp
     * @param  string $idFrs
     * @param  bool $externe
     * @return bool
     */
    public function validationQuestionnaire(string $idCamp, string $idFrs, bool $externe = false): bool
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':ID_CAMP' => $idCamp,
            ':CODE_FOU' => $idFrs,
            ':COD_NOTEUR' => User::getUser()->getCodeUser()
        ];

        $req = "UPDATE CAMP_NOT_STATUS
        SET TERMINE = 0x54
        WHERE ID_CAMP = :ID_CAMP 
            AND CODE_FOU = :CODE_FOU
            AND COD_NOTEUR = :COD_NOTEUR
            AND NOTEUR_EXTERNE = " . ($externe ? "0x54" : "0x46");

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

        return $stmt->execute($arrayReq);
    }

    public function checkThatSupplierAssessmentIsComplete(string $campId, string $supplier): bool
    {
        $query = "SELECT CODE_FOU 
        FROM CAMP_NOT_STATUS WITH(NOLOCK) 
        WHERE ID_CAMP = :ID_CAMP
            AND CODE_FOU = :CODE_FOU
            AND TERMINE = 0x46";

        $params = [
            ':ID_CAMP' => [
                'value' => $campId,
                'option' => \PDO::PARAM_INT
            ],
            ':CODE_FOU' => [
                'value' => $supplier,
                'option' => \PDO::PARAM_STR
            ],
        ];

        $this->pdo->prepare($query);

        $check = $this->pdo->prepare($query);
        foreach ($params as $key => $param) {
            $check->bindParam($key, $param['value'], $param['option']);
        }

        $check->execute();
        $result = $check->fetchAll(PDO::FETCH_ASSOC);

        if ($result) {
            return false;
        } else {
            return true;
        }
    }

    public function updateSupplierStatus(string $campId, string $supplier): bool
    {
        $query = "UPDATE CAMP_LST_FOUR
        SET TERMINE = 0x54
        WHERE ID_CAMP = :ID_CAMP
            AND CODE_FOU = :CODE_FOU";

        $params = [
            ':ID_CAMP' => [
                'value' => $campId,
                'option' => \PDO::PARAM_INT
            ],
            ':CODE_FOU' => [
                'value' => $supplier,
                'option' => \PDO::PARAM_STR
            ],
        ];

        $this->pdo->prepare($query);

        $check = $this->pdo->prepare($query);
        foreach ($params as $key => $param) {
            $check->bindParam($key, $param['value'], $param['option']);
        }

        return $check->execute();
    }

    /**
     * getCampNotStatusTermine
     *
     * @param  string $idCamp
     * @param  string $codeFrs
     * @param  bool|string $termine
     * @param  bool|string $externe
     * @param  string $user
     * @return array<mixed>
     */
    public function getCampNotStatusTermine(string $idCamp, string $codeFrs, $termine = false, $externe = false, string $user = ''): ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':ID_CAMP' => $idCamp,
            ':CODE_FOU' => $codeFrs,
            ':COD_NOTEUR' => $user
        ];

        $req = "SELECT count(TERMINE) AS count
            FROM CAMP_NOT_STATUS with(nolock)
            WHERE ID_CAMP = :ID_CAMP
                AND CODE_FOU = :CODE_FOU
                AND COD_NOTEUR = :COD_NOTEUR
                AND NOTEUR_EXTERNE = " . ($externe ? "0x54" : "0x46") . "
                AND TERMINE = " . ($termine ? "0x54" : "0x46");


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

    /**
     * updateCampNotStatusTermine
     *
     * @param  string $idCamp
     * @param  string $codeFrs
     * @param  bool|string $externe
     * @param  string $user
     * @return bool
     */
    public function updateCampNotStatusTermine(string $idCamp, string $codeFrs, $externe = false, string $user = ''): bool
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':ID_CAMP' => $idCamp,
            ':CODE_FOU' => $codeFrs,
            ':COD_NOTEUR' => $user
        ];

        $req = "UPDATE CAMP_NOT_STATUS
        SET TERMINE = 0x46
        WHERE ID_CAMP = :ID_CAMP
        AND CODE_FOU = :CODE_FOU
        AND COD_NOTEUR = :COD_NOTEUR
        AND NOTEUR_EXTERNE = " . ($externe ? "0x54" : "0x46");

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

        return $stmt->execute($arrayReq);
    }

    /**
     * getContactOxalink
     *
     * @param  string $codeFrs
     * @return array<mixed>
     */
    public function getContactOxalink(string $codeFrs): ?array
    {
        /**
         * Creation de la requete
         */

        $arrayReq = [
            ':CODE_FOU' => $codeFrs,
            ':STATUT' => 'A'
        ];

        $req = "SELECT ou.EMAIL, ou.FONCTION, ou.NOM, ou.COD_LNG,fr.RS
        FROM CONTFOUR c with(nolock)
        INNER JOIN OXALINK_USER ou with(nolock)
            ON ou.USER_ID = c.OXALINK_ID
        LEFT JOIN FOURNISS fr with(nolock)
            ON fr.CODE = c.CODE_FOU
        WHERE c.CODE_FOU = :CODE_FOU
            AND ou.STATUT = :STATUT ";


        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * Summary of getAllQuesionForDepouilleur
     * @param string $idUser
     * @param string $idCamp
     * @return string[][]
     */
    public function getAllQuesionForDepouilleur(string $idUser, string $idCamp): ?array
    {
        $arrayReq = [
            ':depouilleur' => $idUser,
            ':idCamp' => $idCamp
        ];

        $req = "SELECT crq.CODE_FOU, crq.COD_NOTEUR, crq.QUEST_ACTIF, crq.TERMINE, OUVNONAPPL,cqdTmp.ID_QST_PARENT,OUV_NAPOSS,crq.COMMENT as comRep, REP_TAB, REP_LOG, NOTE,crq.ID_CAMP, crq.ID_THEME,  crq.ID_QUEST, crq.ID_THEME, crq.REP_NUM,  crq.REP_OUV, crq.COMMENTAIRE, crq.OUV_NOTE, cqq.G_B_QST_LIE, cqq.LIB_QUEST,OUV_NOTEPAR,
                cqq.TYPE_REP, cqq.NOTE_FAUX, cqq.NOTE_VRAI, cqq.NUM_MIN, cqq.NUM_MAX, cqq.NUM_FORMAT, cqq.AUTHCOMMENT, REGCOMOBLIG, cqq.DESCRIPTION, cqq.QST_OBLIG, th.LIB_THEME, th.ID_PERE, four.RS
            FROM CAMP_REP_QST crq
                JOIN CAMP_ENT ce with(nolock)
                    ON crq.ID_CAMP = ce.ID_CAMP
                JOIN CAMP_QST_Q cqq with(nolock)
                    ON crq.ID_CAMP = cqq.ID_CAMP 
                        AND crq.ID_THEME = cqq.ID_THEME 
                        AND crq.ID_QUEST = cqq.ID_QUEST
                JOIN CAMP_QST_TH th with(nolock)
                    ON th.ID_THEME = crq.ID_THEME 
                        AND th.ID_CAMP =  cqq.ID_CAMP
                LEFT JOIN CAMP_QST_DEP cqd with(nolock)
                    ON cqd.ID_CAMP = cqq.ID_CAMP 
                        AND cqd.ID_QST_DEP = cqq.ID_QUEST 
                        AND cqd.ID_THEME_DEP = cqq.ID_THEME
                LEFT JOIN CAMP_QST_DEP cqdTmp with(nolock)
                    ON cqdTmp.ID_CAMP = cqq.ID_CAMP 
                        AND cqdTmp.ID_QST_PARENT = cqq.ID_QUEST 
                        AND cqdTmp.ID_THEME_PARENT = cqq.ID_THEME
                LEFT JOIN FOURNISS four ON four.CODE = crq.CODE_FOU
            WHERE crq.ID_CAMP = :idCamp
                AND crq.QUEST_ACTIF = 0x54
                AND cqq.TYPE_REP = 'O'
                AND crq.OUV_NOTE = 0x46
                AND ce.CREEEPAR = :depouilleur
                AND crq.OUVNONAPPL = 0x46
                AND crq.TERMINE = 0x54
            ORDER BY crq.CODE_FOU,th.NM_ORDRE,cqq.NM_ORDRE ASC";
        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }
}
