<?php

namespace Modules\Evaluation\Repository;

use Entity\Model\CampLstFour;
use Entity\Model\CampSoc;
use Entity\Model\Fourniss;
use GuzzleHttp\Psr7\Response;
use Helpers\Filter\FilterTable;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use PDO;

class Campaign extends AbsRepository
{
    /**
     * @var mixed $pdoStorage
     */
    private $pdoStorage;

    /**
     * __construct
     *
     * @param  \PDO $pdo
     * @param  mixed $pdoStorage
     */
    public function __construct(PDO $pdo, $pdoStorage = null)
    {
        parent::__construct($pdo);
        $this->pdoStorage = $pdoStorage;
    }

    /**
     * @param array<mixed> $post
     */
    public function campaignList(array $post = []): array
    {
        $query = 'SELECT DISTINCT campaign.ID_CAMP, campaign.LIBELLE, campaign.DESCRIPT, campaign.TOUTESTE,
            campaign.ID_GRILLE, campaign.STATUT, campaign.CREEEPAR, campaign.DTECREAT, campaign.DTEVALID,
            campaign.DTEFIN, campaign.DTEFIN_PREVUE, creator.NOM AS CREATOR,crq.ID_CAMP as REPONSEENCOURS, ISNULL(prem.POURCENT, 0) as encours, prcnt.POURCENT as total, p.LIBELLE as LIBELLEPER
        FROM CAMP_ENT campaign WITH (NOLOCK)
            LEFT JOIN HASTUTIL creator WITH (NOLOCK)
                ON creator.CODE = campaign.CREEEPAR
            LEFT JOIN CAMP_SOC s WITH (NOLOCK)
                ON s.ID_CAMP = campaign.ID_CAMP 
            LEFT JOIN (
                SELECT crq.ID_CAMP 
                FROM CAMP_REP_QST crq WITH (NOLOCK) 
                INNER JOIN  CAMP_QST_Q cqq WITH (NOLOCK)
                    ON cqq.ID_THEME = crq.ID_THEME 
                        AND cqq.ID_CAMP = crq.ID_CAMP 
                        AND cqq.ID_QUEST = crq.ID_QUEST
                WHERE  crq.QUEST_ACTIF = 0x54 
                    AND crq.NOT_FOUR = 0x46 
                    AND QUEST_FOUR = 0x54
                    AND crq.COD_NOTEUR = :NOTEUR
                GROUP BY crq.ID_CAMP 
            ) crq  
                ON crq.ID_CAMP = campaign.ID_CAMP 
			LEFT JOIN (
                SELECT COUNT(*)*1.0 AS POURCENT,ID_CAMP  
                FROM CAMP_REP_QST WITH(NOLOCK) 
                WHERE TERMINE=0x54 
                    AND QUEST_ACTIF=0x54 
                GROUP BY ID_CAMP) prem 
                    ON prem.ID_CAMP = campaign.ID_CAMP
			LEFT JOIN (
                SELECT COUNT(*)*1.0  AS POURCENT,ID_CAMP  
                FROM CAMP_REP_QST WITH(NOLOCK) 
                WHERE QUEST_ACTIF=0x54 
                GROUP BY ID_CAMP) prcnt 
                    ON prcnt.ID_CAMP = campaign.ID_CAMP
            ' . (((isset($post['frs']) && $post['frs'] != '') || (isset($post['notexterne']) && $post['notexterne']!='') || (isset($post['COD_NOTEUR']) && $post['COD_NOTEUR'] != ''))
                ? 'LEFT JOIN CAMP_LST_FOUR crqTmp WITH (NOLOCK)
                    ON crqTmp.ID_CAMP = campaign.ID_CAMP'
                : '') . '
            ' . (((isset($post['COD_NOTEUR']) && $post['COD_NOTEUR'] != '') || (isset($post['notexterne']) && $post['notexterne'] != ''))
                ? 'LEFT JOIN CAMP_LST_NOT_CAMP clnc with(nolock) ON clnc.ID_CAMP = campaign.ID_CAMP'
                : '') . '
            LEFT JOIN PERIODES p WITH (NOLOCK)
                ON p.CODE_PER = campaign.COD_PER
        WHERE 1=1 ';
        $params = [':NOTEUR' => $_SESSION['Session_Login']];

        if (isset($post['societe']) && $post['societe'] != '') {
            $query .= " AND (UPPER(s.CODE_STE) like :codeSte1 OR campaign.TOUTESTE = 0x54)";
            $params[':codeSte1'] = strtoupper('%' . trim($post['societe']) . '%');
        }

        if (isset($post['ID_CAMP']) && $post['ID_CAMP'] != '') {
            $query .= " AND UPPER(campaign.ID_CAMP) like :camp1";
            $params[':camp1'] = strtoupper('%' . trim($post['ID_CAMP']) . '%');
        }

        if (isset($post['ID_GRILLE']) && $post['ID_GRILLE'] != '') {
            $query .= " AND UPPER(campaign.ID_GRILLE) like :grille1";
            $params[':grille1'] = strtoupper('%' . trim($post['ID_GRILLE']) . '%');
        }

        if (isset($post['frs']) && $post['frs'] != '') {
            $query .= " AND crqTmp.CODE_FOU = :valuefrs";
            $params[':valuefrs'] = $post['frs'];
        }


        if (isset($post['notexterne']) && $post['notexterne'] != '') {
            $query .= " AND clnc.NOTEUR = :valuenotex AND clnc.NOTEUR_EXTERNE = 0x54 ";
            $params[':valuenotex'] = $post['notexterne'];
        }


        if (isset($post['COD_NOTEUR']) && $post['COD_NOTEUR'] != '') {
            $query .= " AND clnc.NOTEUR = :valuecodenoteur AND clnc.NOTEUR_EXTERNE = 0x46 ";
            $params[':valuecodenoteur'] = $post['COD_NOTEUR'];
        }

        if (isset($post['tri_COD_PER']) && $post['tri_COD_PER'] != '') {
            $post['tri_LIBELLEPER'] = $post['tri_COD_PER'];
            unset($post['tri_COD_PER']);
        }
        
        if (isset($post['filter_COD_PER']) && $post['filter_COD_PER'] != '') {
            $post['filter_p.CODE_PER'] = $post['filter_COD_PER'];
            unset($post['filter_COD_PER']);
        }

        if (isset($post['filter_ID_CAMP']) && $post['filter_ID_CAMP'] != '') {
            $post['filter_campaign.ID_CAMP'] = $post['filter_ID_CAMP'];
            unset($post['filter_ID_CAMP']);
        }

        if (isset($post['filter_LIBELLE']) && $post['filter_LIBELLE'] != '') {
            $post['filter_campaign.LIBELLE'] = $post['filter_LIBELLE'];
            unset($post['filter_LIBELLE']);
        }

        if (isset($post['filter_CREEEPAR']) && $post['filter_CREEEPAR'] != '') {
            $post['filter_creator.NOM'] = $post['filter_CREEEPAR'];
            unset($post['filter_CREEEPAR']);
        }

        $filter = FilterTable::splitFilter($post);

        if (count($filter) > 0 && isset($filter[1])) {
            $query .= str_ireplace('where', 'AND', $filter[0]) . $filter['tri'];

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

        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount($query, $post, $params, $this->pdo);

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

    public function campaignDetail(int $id): array
    {
        $query = 'SELECT campaign.ID_CAMP, LIBELLE, DESCRIPT, TOUTESTE, ID_GRILLE, COD_PER, STATUT, CREEEPAR, DTECREAT, DTEVALID, DTEFIN, DTEFIN_PREVUE, creator.NOM AS CREATOR
        FROM CAMP_ENT campaign WITH (NOLOCK)
        LEFT JOIN HASTUTIL creator WITH (NOLOCK)
            ON creator.CODE = campaign.CREEEPAR
        WHERE campaign.ID_CAMP = :ID_CAMP';

        $params = [
            ':ID_CAMP' => $id
        ];

        $detail = $this->pdo->prepare($query);
        $detail->execute($params);

        return $detail->fetch(\PDO::FETCH_ASSOC);
    }

    public function campaignNew(array $post)
    {
        $query = "INSERT INTO CAMP_ENT (ID_CAMP, LIBELLE, DESCRIPT, TOUTESTE, ID_GRILLE, STATUT, CREEEPAR, DTECREAT, DTEFIN_PREVUE, COD_PER)
        VALUES (:ID_CAMP, :LIBELLE, :DESCRIPT, " . $post['TOUTESTE'] . ", :ID_GRILLE, :STATUT, :CREEEPAR, :DTECREAT, :DTEFIN_PREVUE, :COD_PER)";
        $params = [
            ':ID_CAMP'          => $post['ID_CAMP'],
            ':LIBELLE'          => $post['LIBELLE'],
            ':DESCRIPT'         => $post['DESCRIPTION'] ?? '',
            ':ID_GRILLE'        => $post['CATEGORIE'],
            ':STATUT'           => 'P',
            ':CREEEPAR'         => \strtoupper($_SESSION['Session_Login']),
            ':DTECREAT'         => \date('Ymd'),
            ':DTEFIN_PREVUE'    => $post['DTEFIN_PREVUE'],
            ':COD_PER'          => $post['COD_PER'],
        ];
        $insert = $this->pdo->prepare($query);

        return $insert->execute($params);
    }

    /**
     * campaignSupplierList
     *
     * @param  int $id
     * @return array
     */
    public function campaignSupplierList(int $id, bool $contextSaisieRep = false): array
    {
        $query = "SELECT liste.CODE_FOU, liste.DTEDEB, liste.DATEFIN, liste.TERMINE, liste.NOTE, fournisseur.RS, fournisseur.NO_SIRET, fournisseur.ADR, fournisseur.CPOSTAL, fournisseur.VILLE1, COUNTRY.LIBELLE AS LIB_PAYS, cf.EMAIL AS EMAILCONT, cf.TEL AS TEL_CTCT, cf.FAX AS FAX_CTCT, cf.TELPORT AS POR_CTCT " . ($contextSaisieRep ? ", crq.CODE_FOU as REPONSEENCOURS" : "" )." 
        FROM CAMP_LST_FOUR liste WITH(NOLOCK)
        LEFT JOIN FOURNISS fournisseur WITH (NOLOCK)
            ON liste.CODE_FOU = fournisseur.CODE
        LEFT JOIN COUNTRY WITH (NOLOCK) 
            ON COUNTRY.CODE_PAYS = fournisseur.CODE_PAYS
        LEFT JOIN CONTFOUR cf
            ON cf.CODE_FOU = fournisseur.CODE
                AND cf.IND_FOU = 0
        " . ($contextSaisieRep ? " LEFT JOIN (
            SELECT crq.CODE_FOU 
					FROM CAMP_REP_QST crq WITH (NOLOCK) 
					INNER JOIN  CAMP_QST_Q cqq WITH (NOLOCK)
                        ON cqq.ID_THEME = crq.ID_THEME 
                            AND cqq.ID_CAMP = crq.ID_CAMP 
                            AND cqq.ID_QUEST = crq.ID_QUEST
					WHERE crq.ID_CAMP = :ID_CAMP2 
                        AND crq.QUEST_ACTIF = 0x54 
						AND crq.NOT_FOUR = 0x46 
						AND crq.COD_NOTEUR = :NOTEUR
						AND QUEST_FOUR = 0x54
					GROUP BY crq.CODE_FOU 
        ) crq  
            ON crq.CODE_FOU = fournisseur.CODE" : '') . "
        WHERE ID_CAMP = :ID_CAMP";

        $params = [
            ':ID_CAMP' => $id,
        ];
        
        if ($contextSaisieRep == true) {
            $params[':ID_CAMP2'] = $id;
            $params[':NOTEUR'] = $_SESSION['Session_Login'];
        }

        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount($query, $params, $params, $this->pdo);
        return $this->fetchAssocAll($query, $params);
    }

    public function supplierList(int $id, array $post): array
    {
        // requête si besoin du type de fournisseur
        // $query = "SELECT *
        //         FROM (SELECT 'F' TYPE_FG,*
        //                 FROM (SELECT FOURNISS.CODE, FOURNISS.RS, FOURNISS.NO_SIRET, FOURNISS.CPOSTAL, FOURNISS.VILLE1, FOURNISS.ENSCOMM, SUBSTRING((SELECT ',' + DESIGNAT AS 'data()'
        //                     FROM SSFAMFOU WITH (NOLOCK)
        //                     LEFT JOIN SSFAMILL on SSFAMILL.CODE_SFA = SSFAMFOU.CODE_SFAM
        //                     WHERE CODE_FOUR = FOURNISS.CODE FOR XML PATH('')), 2 , 9999) AS listeSousFamille
        //                     FROM FOURNISS WITH (NOLOCK)) frs
        //                 WHERE CODE NOT IN
        //                     (SELECT CODE_FOU
        //                     FROM CAMP_LST_FOUR WITH (NOLOCK)
        //                     WHERE ID_CAMP = :id)
        //             UNION
        //             SELECT 'G' TYPE_FG, CODE, RS, NO_SIRET, CPOSTAL, VILLE1, '', '' listeSousFamille
        //             FROM GRPFOU WITH (NOLOCK)
        //             WHERE CODE NOT IN (SELECT CODE_FOU FROM CAMP_LST_FOUR WITH (NOLOCK) WHERE ID_CAMP = :id2)) listeFrsGrpFrs
        //         WHERE 1=1";

        // dans un premier temps on vérifie le périmètre société de la campagne
        $campaignCompanies = [];
        $campaignCompanies = array_column(
            (new CampSoc($this->pdo))
                ->findAll(
                    ['ID_CAMP' => $id],
                    ['CODE_STE']
                ),
            'CODE_STE'
        );
        
        $companiesPartForQuery = '';
        if (is_array($campaignCompanies) && count($campaignCompanies) > 0) {
            $cumPeri = ' STE_PERI ';
            $companiesPartForQuery = ' AND CODE_STE IN ( \'';
            foreach ($campaignCompanies as $company) {
                $companiesPartForQuery .= $company . '\', \'';
            }
            $companiesPartForQuery = \rtrim($companiesPartForQuery, ', \'');
            $companiesPartForQuery .= '\') ';
        } else {
            $cumPeri = ' CUM_PERI ';
        }

        // on enregistre les filtres dans une autre variable pour pouvoir unset les filtres appliqués manuellement sans perdre la valeurs enregistrées dans le post (notamment pour garder les filtres en changeant de page)
        $filters = $post;

        $query = "SELECT * FROM (SELECT F.*, 
            (SELECT COUNT(CODE_FOU) FROM FOUR_PARTENAIRE_DATA WITH (NOLOCK) WHERE CODE_FOU = F.CODE ) PARTENAIRE,
            SUBSTRING(
            (SELECT ',' + DESIGNAT AS 'data()' 
            FROM SSFAMFOU WITH (NOLOCK) 
            LEFT JOIN SSFAMILL with(nolock) on SSFAMILL.CODE_SFA = SSFAMFOU.CODE_SFAM
            WHERE CODE_FOUR = F.CODE FOR XML PATH('')), 2 , 9999) AS listeSousFamille, 
            CASE 
                WHEN F.PROSPECT = 0x54 THEN 'Prospect'
                WHEN F.OCCASION = 0x54 THEN 'Occasionnel'
                ELSE 'Référencé'
            END AS TYPE
        FROM FOURNISS F WITH (NOLOCK)) F 
        WHERE CODE NOT IN 
            (SELECT CODE_FOU 
            FROM CAMP_LST_FOUR WITH (NOLOCK) 
            WHERE ID_CAMP = :id) 
        ";

        // filtre sous-famille
        if (isset($post['filter_listeSousFamille']) && $post['filter_listeSousFamille']) {
            $query .= " AND F.CODE IN (SELECT CODE_FOUR FROM SSFAMFOU sfafo WITH (NOLOCK)
			LEFT JOIN SSFAMILL sfa with(nolock)
				ON sfa.CODE_SFA = sfafo.CODE_SFAM
			WHERE sfa.CODE_SFA = :SSFAMILLE)
            ";
            $params[':SSFAMILLE'] = $post['filter_listeSousFamille'];
            unset($filters['filter_listeSousFamille']);
        }

        // filtre prospect ou référencé pour les fournisseurs
        if (isset($post['filter_TYPE']) && $post['filter_TYPE'] != '') {
            if ($post['filter_TYPE'] == 'P') {
                $query .= " AND F.PROSPECT = 0x54 ";
            } elseif ($post['filter_TYPE'] == 'R') {
                $query .= " AND (F.PROSPECT = 0x46 AND F.OCCASION = 0x46) ";
            } else {
                $query .= " AND F.OCCASION = 0x54 ";
            }
            unset($filters['filter_TYPE']);
        }

        // filtre sur le chiffre d'affaires déclaré
        if (isset($post['declared_turnover_rule']) && $post['declared_turnover_rule']) {
            $query .= " AND F.CODE IN 
                        (SELECT CODE_FOUR 
                        FROM FOUR_CA CA with(nolock)
                        WHERE CA.ANNEE_CA = :ANNEE_CA 
                            AND CA.MONTANT_CA ";

            $params[':ANNEE_CA'] = $post['turnover_year'];

            switch ($post['declared_turnover_rule']) {
                case 'E':
                    $query .= " = :MONTANT_CA ";
                    $params[':MONTANT_CA'] = $post['declared_turnover_min_value'];
                    break;

                case 'G':
                    $query .= " > :MONTANT_CA ";
                    $params[':MONTANT_CA'] = $post['declared_turnover_min_value'];
                    break;

                case 'L':
                    $query .= " < :MONTANT_CA ";
                    $params[':MONTANT_CA'] = $post['declared_turnover_min_value'];
                    break;

                case 'B':
                    $query .= " > :MONTANT_CA AND CA.MONTANT_CA < :MONTANT_CA2 ";
                    $params[':MONTANT_CA'] = $post['declared_turnover_min_value'];
                    $params[':MONTANT_CA2'] = $post['declared_turnover_max_value'];
                    break;

                default:
                    $query .= " <> NULL ";
                    break;
            }

            $query .= " ) ";

            unset($filters['turnover_year']);
            unset($filters['declared_turnover_rule']);
            unset($filters['declared_turnover_min_value']);
            unset($filters['declared_turnover_max_value']);
        }

        // filtre sur le chiffre d'affaires généré sur la plateforme
        if ((isset($post['generated_turnover_type']) && $post['generated_turnover_type']) && (isset($post['generated_turnover_rule']) && $post['generated_turnover_rule'])) {
            $columnName = '';

            switch ($post['generated_turnover_type']) {
                case 'C':
                    $columnName = 'ENGAGE';
                    break;
                
                case 'R':
                    $columnName = 'REALISE';
                    break;
                
                case 'F':
                    $columnName = 'FACTURE';
                    break;
            }

            $query .= " AND F.CODE IN 
                    (SELECT CODE_IDEN 
                    FROM $cumPeri with(nolock)
                    WHERE CODE_TAB = 'FOU'
                        AND CODE_PER = :CODE_PER
                        $companiesPartForQuery
                        AND $columnName ";

            $params[':CODE_PER'] = $post['turnover_year'];

            switch ($post['generated_turnover_rule']) {
                case 'E':
                    $query .= " = :REALISE ";
                    $params[':REALISE'] = $post['generated_turnover_min_value'];
                    break;

                case 'G':
                    $query .= " > :REALISE ";
                    $params[':REALISE'] = $post['generated_turnover_min_value'];
                    break;

                case 'L':
                    $query .= " < :REALISE ";
                    $params[':REALISE'] = $post['generated_turnover_min_value'];
                    break;

                case 'B':
                    $query .= " > :REALISE AND REALISE < :REALISE2 ";
                    $params[':REALISE'] = $post['generated_turnover_min_value'];
                    $params[':REALISE2'] = $post['generated_turnover_max_value'];
                    break;

                default:
                    $query .= " <> NULL ";
                    break;
            }

            $query .= " ) ";

            unset($filters['turnover_year']);
            unset($filters['generated_turnover_type']);
            unset($filters['generated_turnover_rule']);
            unset($filters['generated_turnover_min_value']);
            unset($filters['generated_turnover_max_value']);
        }

        // filtre sur les appels d'offre
        if (isset($post['tenders_rule']) && $post['tenders_rule'] != '') {
            $query .= " AND F.CODE IN 
                    (SELECT DISTINCT AO.CODE_FOU
                    FROM AO_FOUR AO WITH (NOLOCK)
                    LEFT JOIN 
                        (SELECT CODE_FOU, COUNT(NO_AO) count
                        FROM AO_FOUR with(nolock)
                        GROUP BY CODE_FOU) AO2 
                            ON AO2.CODE_FOU = AO.CODE_FOU
                    WHERE AO2.count ";

            switch ($post['tenders_rule']) {
                case 'E':
                    $query .= " = :count ";
                    $params[':count'] = $post['tenders_min_value'];
                    break;

                case 'G':
                    $query .= " > :count ";
                    $params[':count'] = $post['tenders_min_value'];
                    break;

                case 'L':
                    $query .= " < :count ";
                    $params[':count'] = $post['tenders_min_value'];
                    break;

                case 'B':
                    $query .= " > :count AND count < :count2 ";
                    $params[':count'] = $post['tenders_min_value'];
                    $params[':count2'] = $post['tenders_max_value'];
                    break;

                default:
                    $query .= " <> NULL ";
                    break;
            }

            $query .= " ) ";

            unset($filters['tenders_filters']);
            unset($filters['tenders_rule']);
            unset($filters['tenders_min_value']);
            unset($filters['tenders_max_value']);
        }

        // filtre sur les commandes
        if (isset($post['orders_rule']) && $post['orders_rule'] != '') {
            $query .= " AND F.CODE IN 
                    (SELECT DISTINCT CODE_IDEN
						FROM $cumPeri WITH (NOLOCK)
						WHERE CODE_TAB = 'FOU'
                            $companiesPartForQuery
							AND NB_CDES ";

            switch ($post['orders_rule']) {
                case 'E':
                    $query .= " = :NB_CDES ";
                    $params[':NB_CDES'] = $post['orders_min_value'];
                    break;

                case 'G':
                    $query .= " > :NB_CDES ";
                    $params[':NB_CDES'] = $post['orders_min_value'];
                    break;

                case 'L':
                    $query .= " < :NB_CDES ";
                    $params[':NB_CDES'] = $post['orders_min_value'];
                    break;

                case 'B':
                    $query .= " > :NB_CDES AND NB_CDES < :NB_CDES2 ";
                    $params[':NB_CDES'] = $post['orders_min_value'];
                    $params[':NB_CDES2'] = $post['orders_max_value'];
                    break;

                default:
                    $query .= " <> NULL ";
                    break;
            }

            $query .= " ) ";

            unset($filters['orders_filters']);
            unset($filters['orders_rule']);
            unset($filters['orders_min_value']);
            unset($filters['orders_max_value']);
        }

        // filtre sur les factures
        if (isset($post['invoices_rule']) && $post['invoices_rule'] != '') {
            $query .= " AND F.CODE IN 
                    (SELECT CODE 
                    FROM FOURNISS WITH (NOLOCK)
                    LEFT JOIN 
                        (SELECT CODE_FOUR, COUNT(NUM_FACT) count 
                        FROM FAC_ENT with(nolock)
                        LEFT JOIN CAMP_ENT C with(nolock) ON C.ID_CAMP = $id
						LEFT JOIN PERIODES P with(nolock) ON P.CODE_PER = C.COD_PER
                        WHERE DATE_PER > P.DATE_DEB 
                            AND DATE_PER < P.DATE_FIN
                            AND TYPE = 'F' 
                            AND ENT_VALID = 0X54 
                            AND AFF_VALID = 0X54 
                            AND BON_A_PAY = 0X54 
                            $companiesPartForQuery
                        GROUP BY CODE_FOUR) FAC_COUNT 
                            ON FAC_COUNT.CODE_FOUR = FOURNISS.CODE
                    WHERE FAC_COUNT.count ";

            switch ($post['invoices_rule']) {
                case 'E':
                    $query .= " = :count ";
                    $params[':count'] = $post['invoices_min_value'];
                    break;

                case 'G':
                    $query .= " > :count ";
                    $params[':count'] = $post['invoices_min_value'];
                    break;

                case 'L':
                    $query .= " < :count ";
                    $params[':count'] = $post['invoices_min_value'];
                    break;

                case 'B':
                    $query .= " > :count AND count < :count2 ";
                    $params[':count'] = $post['invoices_min_value'];
                    $params[':count2'] = $post['invoices_max_value'];
                    break;

                default:
                    $query .= " <> NULL ";
                    break;
            }

            $query .= " ) ";

            unset($filters['invoices_filters']);
            unset($filters['invoices_rule']);
            unset($filters['invoices_min_value']);
            unset($filters['invoices_max_value']);
        }

        if (isset($post['filter_INV_PART']) && $post['filter_INV_PART']) {
            $query .= " and CODE in (select CODE from FOURNISS frs with(nolock)
                    left join (select * from PARAMDICO with(nolock) where TYPEDICO='INVIT_PARTFOU') par  on par.ID3=frs.CODE
                    where ALPHA2<>'A' and ID1='" . $post['filter_INV_PART'] . "')";

            unset($filters['filter_INV_PART']);
        }

        $params[':id'] = $id;

        if (isset($post['filter_RS']) && $post['filter_RS']) {
            $query .= " AND (UPPER(RS) LIKE :rs1 OR UPPER(ENSCOMM) LIKE :ens1)";
            $params[':rs1'] = '%' . mb_strtoupper($post['filter_RS']) . '%';
            $params[':ens1'] = '%' . mb_strtoupper($post['filter_RS']) . '%';

            unset($filters['filter_RS']);
        }

        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($filters);
        $query .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($params);

        $whereArray = FilterTable::splitFilter($filters, 0, 0, 1);
        if (! empty($whereArray)) {
            $query .= $whereArray[0] . $whereArray['tri'];

            if (! empty($whereArray[1])) {
                $params = array_merge($params, $whereArray[1]);
            }
        }

        $paginatorGestion = new PaginatorGestion();
        $paginatorGestion->injecterSqlCount($query, $post, $params, $this->pdo);

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

    /**
     * @param int $campaignId
     * @param array<int, string> $suppliers
     */
    public function supplierMultiInsert(int $campaignId, array $suppliers): void
    {
        $query = 'INSERT INTO CAMP_LST_FOUR (ID_CAMP, CODE_FOU)
                VALUES ';
        foreach ($suppliers as $supplier) {
            $query .= '(' . $campaignId . ', \'' . $supplier . '\'), ';
        }
        $query = \rtrim($query, ', ');

        $multiInsert = $this->pdo->prepare($query);
        $multiInsert->execute();
    }

    /**
     * companiesMultiInsert
     *
     * @param  int $campaignId
     * @param  array<int, string> $companies
     * @return void
     */
    public function companiesMultiInsert(int $campaignId, array $companies): void
    {
        // on crée la table temporaire qui nous permettra de faire les contrôles sur l'insert initial
        $query = "SELECT C.ID_CAMP, C.CODE_STE, 'D' STATUT INTO #TEMP_CAMP_SOC 
        FROM CAMP_SOC C with(nolock) WHERE ID_CAMP = 0 and CODE_STE = ''";

        // on réalise un premier insert dans la table temporaire
        $query .= "INSERT INTO #TEMP_CAMP_SOC (ID_CAMP, CODE_STE, STATUT)
        VALUES ";

        foreach ($companies as $company) {
            $query .= '(' . $campaignId . ', \'' . $company . '\', \'\'), ';
        }
        $query = \rtrim($query, ', ');

        // on update la table temporaire avec le champ 'statut' pour organiser les résultats
        $query .= "UPDATE T
                SET T.STATUT = 
                    CASE 
                        WHEN ISNULL (C.CODE_STE, '') = ''
                        THEN 'O'
                        ELSE 'D' 
                    END
                FROM #TEMP_CAMP_SOC T
                LEFT JOIN CAMP_SOC C ON C.ID_CAMP = $campaignId AND C.CODE_STE = T.CODE_STE;";

        // on insère les sociétés non-doublons dans la liste des sociétés de la campagne
        $query .= "INSERT INTO CAMP_SOC (ID_CAMP, CODE_STE)
                SELECT ID_CAMP, CODE_STE FROM #TEMP_CAMP_SOC T WHERE T.STATUT = 'O';";

        // $query = 'INSERT INTO CAMP_SOC (ID_CAMP, CODE_STE)
        //         VALUES ';
        // foreach ($companies as $company) {
        //     $query .= '(' . $campaignId . ', \'' . $company . '\'), ';
        // }
        // $query = \rtrim($query, ', ');

        $multiInsert = $this->pdo->prepare($query);
        $multiInsert->execute();
    }

    /**
     * importCampaignSuppliers
     *
     * @param  int $campaignId
     * @param  array<mixed> $suppliers
     * @return array<mixed>
     */
    public function importCampaignSuppliers(int $campaignId, array $suppliers): array
    {
        // on crée la table temporaire qui nous permettra de faire les contrôles sur l'insert initial
        $query = "SELECT ID_CAMP, CODE_FOU, '' AS STATUT
                    INTO #TEMP_CAMP_LST_FOUR
                FROM CAMP_LST_FOUR
                WHERE ID_CAMP = 0; ";

        // on réalise un premier insert dans la table temporaire
        $query .= "INSERT INTO #TEMP_CAMP_LST_FOUR (ID_CAMP, CODE_FOU, STATUT)
        VALUES ";

        foreach ($suppliers as $supplier) {
            $query .= '(' . $campaignId . ', \'' . $supplier . '\', \'\'), ';
        }
        $query = \rtrim($query, ', ');

        // on update la table temporaire avec le champ 'statut' pour organiser les résultats
        $query .= "UPDATE T
                SET T.STATUT = 
                    CASE 
                        WHEN ISNULL (F.CODE, '') = ''
                        THEN 'E'
                        WHEN ISNULL (C.CODE_FOU, '') = ''
                        THEN 'O'
                        ELSE 'D' 
                    END
                FROM #TEMP_CAMP_LST_FOUR T
                LEFT OUTER JOIN FOURNISS F ON F.CODE = T.CODE_FOU
                LEFT OUTER JOIN CAMP_LST_FOUR C ON C.ID_CAMP = $campaignId AND C.CODE_FOU = T.CODE_FOU;";

        // on insère les fournisseurs vérifiés et non-doublons dans la liste des fournisseurs de la campagne
        $query .= "INSERT INTO CAMP_LST_FOUR (ID_CAMP, CODE_FOU)
                SELECT ID_CAMP, CODE_FOU FROM #TEMP_CAMP_LST_FOUR T WHERE T.STATUT = 'O';";

        // on exécute la requête en connexion persistante
        $stmt = sqlsrv_query($this->pdoStorage, $query);

        // on récupère le compte de fournisseurs importés et insérés
        $query = "SELECT COUNT(*) AS count FROM #TEMP_CAMP_LST_FOUR T WHERE T.STATUT = 'O';";

        $stmt = sqlsrv_query($this->pdoStorage, $query);
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $count = $row['count'];
        }

        // on récupère la liste des fournisseurs inexistants
        $query = "SELECT T.CODE_FOU,
                    CASE 
						WHEN F.RS IS NULL
						THEN 'Code inexistant'
						ELSE F.RS
					END AS RS 
                FROM #TEMP_CAMP_LST_FOUR T 
                LEFT JOIN FOURNISS F ON T.CODE_FOU = F.CODE
                WHERE T.STATUT = 'E'";

        $stmt = sqlsrv_query($this->pdoStorage, $query);
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $missingSuppliers[$row['CODE_FOU']] = $row['RS'];
        }

        // on récupère la liste des fournisseurs qui étaient déjà enregistrés dans la campagne
        $query = "SELECT T.CODE_FOU, F.RS
                FROM #TEMP_CAMP_LST_FOUR T 
                LEFT JOIN FOURNISS F ON T.CODE_FOU = F.CODE
                WHERE T.STATUT = 'D'";

        $stmt = sqlsrv_query($this->pdoStorage, $query);
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $alreadyRegisteredSuppliers[$row['CODE_FOU']] = $row['RS'];
        }

        return [
            'count'                       => $count ?? 0,
            'missingSuppliers'            => $missingSuppliers ?? [],
            'alreadyRegisteredSuppliers'  => $alreadyRegisteredSuppliers ?? [],
        ];
    }

    /**
     * getMaxIdCamp
     *
     * @return array<mixed>|null
     */
    public function getMaxIdCamp()
    {
        $query = 'SELECT MAX(ID_CAMP) AS MAX_ID FROM CAMP_ENT WITH(NOLOCK)';
        $maxId = $this->pdo->prepare($query);
        $maxId->execute();
        return $maxId->fetch(\PDO::FETCH_ASSOC);
    }

    /**
     * getMaxOrder
     * retourne le numéro d'ordre maximum d'une entité donnée
     *
     * @param  string $table la table concernée
     * @param  string $designation le nom de la colonne numéro d'ordre
     * @param  array<mixed> $whereClause (optional) une éventuelle clause 'WHERE'
     * @return int
     */
    public function getMaxOrder(string $table, string $designation, array $whereClause = [])
    {
        $query = 'SELECT CASE 
                            WHEN ISNULL(MAX(' . $designation . '), 0) = 0
                            THEN 0
                            ELSE MAX(' . $designation . ')
                        END AS max_order
                    FROM ' . $table . ' WITH(NOLOCK)
                    WHERE 1 = 1 ';

        if (is_array($whereClause) && count($whereClause) > 0) {
            foreach ($whereClause as $key => $clause) {
                $query .= ' AND ' . $key . ' = ' . $clause;
            }
        }

        $maxOrder = $this->pdo->prepare($query);
        $maxOrder->execute();

        return $maxOrder->fetch(PDO::FETCH_ASSOC)['max_order'];
    }

    /**
     * eventList
     *
     * @param  string $campaignId
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function eventList(string $campaignId = '', array $post = []): array
    {
        $params = [];
        $query = "SELECT 
                E.ID_CAMP,
                NUM_ORDR,
                DTE_EVE,
                HEU_EVE,
                ID_USER,
                E.COD_EVE,
                COMMENTAIRE,
                " . $this->selectTrad('T.LIBELLE') . ",
                H.NOM, 
                C.LIBELLE as CAMPAGNE
            FROM CAMP_EVE E WITH (NOLOCK)
            LEFT JOIN CAMP_ENT C WITH (NOLOCK) 
            ON C.ID_CAMP = E.ID_CAMP
            LEFT JOIN CAMP_TYP_EVE T WITH (NOLOCK) 
                ON T.COD_EVE = E.COD_EVE
            LEFT JOIN HASTUTIL H WITH (NOLOCK) 
                ON H.CODE = E.ID_USER
            " . $this->joinTrad('T.LIBELLE', $params) . "
            WHERE 1=1";
            
        if ($campaignId != '') {
            $query .= " AND E.ID_CAMP = :ID_CAMP";
            $params[':ID_CAMP'] = $campaignId;
        }

        if (isset($post['filter_HEU_EVE']) && $post['filter_HEU_EVE'] != '') {
            $post['filter_HEU_EVE'] = str_replace(':', '', $post['filter_HEU_EVE']);
        }

        if (isset($post['filter_LIBELLE']) && $post['filter_LIBELLE'] != '') {
            $query .= " AND UPPER(T.LIBELLE) like UPPER(:LIBELLE) ";
            $params[':LIBELLE'] = '%' . $post['filter_LIBELLE'] . '%';
            unset($post['filter_LIBELLE']);
        }


        $whereArray = FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $query .= $whereArray[0] . $whereArray['tri'];

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

        $paginatorGestion = new PaginatorGestion();
        $paginatorGestion->injecterSqlCount($query, $post, $params, $this->pdo);
        
        return $this->fetchAssocAll($query, $params);
    }

    /**
     * checkRatersForCampaign
     * procédure stockée qui vérifie que chaque question d'une campagne compte au moins un évaluateur
     * si c'est le cas, la procédure met à jour les tables CAMP_REP_QST & CAMP_NOT_FIN
     * sinon, retourne la liste des couples question/fournisseur pour lesquels un évaluateur est manquant
     *
     * @param  int $campaignId
     * @return array<mixed>
     */
    public function checkRatersForCampaign(int $campaignId)
    {
        $query = "EXEC STD_Evaluation_ValidationReturn 0, '', '', :ID_CAMP, :USER_LOGIN, :COD_LNG, :SOC_USER";

        $params = [
            ':ID_CAMP'      => $campaignId,
            ':USER_LOGIN'   => $_SESSION['Session_Login'],
            ':COD_LNG'      => $_SESSION['Session_Langue'],
            ':SOC_USER'     => $_SESSION['Session_SocUsr'],
        ];

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

    /**
     * eventMultiInsert
     * insère dans la table CAMP_EVE une liste d'évènements quand plusieurs sont insérés en même temps (évite de faire une requête dans une boucle)
     *
     * @param  int $campaignId
     * @param  string $eventCode
     * @param  array<mixed> $commentParams paramètres du commentaire
     * @return bool
     */
    public function eventMultiInsert(int $campaignId, string $eventCode, array $commentParams)
    {
        $query = 'INSERT INTO CAMP_EVE (ID_CAMP, NUM_ORDR, DTE_EVE, HEU_EVE, ID_USER, COD_EVE, COMMENTAIRE)
                    VALUES (';
        $params = [];

        $maxOrder = $this->getMaxOrder('CAMP_EVE', 'NUM_ORDR', [
            'ID_CAMP'   => $campaignId
        ]);

        if ($eventCode == 'DFO') {
            $i = 1;
            foreach ($commentParams as $supplier) {
                $comment = 'Fournisseur : ' . $supplier['RS'] . ' (' . $supplier['CODE_FOU'] . ')';

                $query .= ':ID_CAMP' . $i . ', :NUM_ORDR' . $i . ', :DTE_EVE' . $i . ', :HEU_EVE' . $i . ', :ID_USER' . $i . ', :COD_EVE' . $i . ', \'' . $comment . '\'), (';

                $params[':ID_CAMP' . $i] = $campaignId;
                $params[':NUM_ORDR' . $i] = $maxOrder + $i;
                $params[':DTE_EVE' . $i] = date('Ymd');
                $params[':HEU_EVE' . $i] = date('His');
                $params[':ID_USER' . $i] = $_SESSION['Session_Login'];
                $params[':COD_EVE' . $i] = $eventCode;
                $i++;
            }

            $query = \rtrim($query, ', (');

            $insert = $this->pdo->prepare($query);
            $insert->execute($params);

            return $insert;
        }

        if ($eventCode == 'FFO') {
            $i = 1;
            foreach ($commentParams as $supplier) {
                $comment = 'Fournisseur : ' . $supplier['RS'] . ' (' . $supplier['CODE_FOU'] . ')';

                $query .= ':ID_CAMP' . $i . ', :NUM_ORDR' . $i . ', :DTE_EVE' . $i . ', :HEU_EVE' . $i . ', :ID_USER' . $i . ', :COD_EVE' . $i . ', \'' . $comment . '\'), ';

                $params[':ID_CAMP' . $i] = $campaignId;
                $params[':NUM_ORDR' . $i] = $maxOrder + $i;
                $params[':DTE_EVE' . $i] = date('Ymd');
                $params[':HEU_EVE' . $i] = date('His');
                $params[':ID_USER' . $i] = $_SESSION['Session_Login'];
                $params[':COD_EVE' . $i] = $eventCode;
                $i++;
            }

            $query = \rtrim($query, ', ');

            $insert = $this->pdo->prepare($query);
            $insert->execute($params);

            return $insert;
        }

        if ($eventCode == 'REL') {
            $comment = $commentParams['COMMENTAIRE'];
            $query .= ':ID_CAMP, :NUM_ORDR, :DTE_EVE, :HEU_EVE, :ID_USER, :COD_EVE, \'' . $comment . '\')';

            $params[':ID_CAMP']   = $campaignId;
            $params[':NUM_ORDR']  = $maxOrder + 1;
            $params[':DTE_EVE']   = date('Ymd');
            $params[':HEU_EVE']   = date('His');
            $params[':ID_USER']   = $_SESSION['Session_Login'];
            $params[':COD_EVE']   = $eventCode;

            $insert = $this->pdo->prepare($query);
            $insert->execute($params);

            return $insert;
        }

        return false;
    }

    /**
     * getValidationEmailSender
     * retourne le CODE, NOM, EMAIL et ID_CAMP de l'utilisateur qui a créé une campagne d'évaluation
     *
     * @param  int $campaignId
     * @return array<mixed>
     */
    public function getValidationEmailSender(int $campaignId)
    {
        $query = 'SELECT h.CODE, h.NOM, h.EMAIL, h.COD_LNG, h.CODE_STE, c.ID_CAMP, h.FONCTION
                    FROM HASTUTIL h WITH (NOLOCK)
                        LEFT OUTER JOIN CAMP_ENT c WITH (NOLOCK)
                            ON c.CREEEPAR = h.CODE
                    WHERE c.ID_CAMP = :ID_CAMP';
        $params = [
            ':ID_CAMP'  => $campaignId,
        ];

        $sender = $this->pdo->prepare($query);
        $sender->execute($params);

        return $sender->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * duplicateCampaign
     *
     * @param  int $campaignToDuplicate
     * @param  int $newCampaign
     * @return Response
     */
    public function duplicateCampaign(int $campaignToDuplicate, int $newCampaign)
    {
        $query = "SELECT ''
        DECLARE 
        @v_New_Campaign_ID INT, 
        @v_Duplicated_Campaign_Id INT
        
        SET @v_Duplicated_Campaign_Id = :DUP_CAMP_ID
        SET @v_New_Campaign_ID = :NEW_CAMP_ID
        
        BEGIN TRANSACTION
        INSERT INTO CAMP_ASSO (ID_CAMP, TYPE_OBJ, VAL_OBJ, CODE_STE)
                SELECT @v_New_Campaign_ID, TYPE_OBJ, VAL_OBJ, CODE_STE FROM CAMP_ASSO WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_GRP_NOT (ID_CAMP, GROUPE)
                SELECT @v_New_Campaign_ID, GROUPE FROM CAMP_GRP_NOT WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_LST_FOUR (ID_CAMP, CODE_FOU, DTEDEB, DATEFIN, TERMINE, NOTE)
                SELECT @v_New_Campaign_ID, CODE_FOU, DTEDEB, DATEFIN, 0x46, NOTE FROM CAMP_LST_FOUR WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_LST_NOT_CAMP (ID_CAMP, NOTEUR, NOTEUR_EXTERNE)
                SELECT @v_New_Campaign_ID, NOTEUR, NOTEUR_EXTERNE FROM CAMP_LST_NOT_CAMP WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_LST_NOT_FOUR (ID_CAMP, CODE_FOU, NOTEUR, NOTEUR_EXTERNE)
                SELECT @v_New_Campaign_ID, CODE_FOU, NOTEUR, NOTEUR_EXTERNE FROM CAMP_LST_NOT_FOUR WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_LST_NOT_GRP (ID_CAMP, GROUPE, NOTEUR, NOTEUR_EXTERNE)
                SELECT @v_New_Campaign_ID, GROUPE, NOTEUR, NOTEUR_EXTERNE FROM CAMP_LST_NOT_GRP WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_QST_DEP (ID_CAMP, ID_QST_PARENT, ID_QST_DEP, BASIS_RULE, RULE_TYPE, MIN_VALUE, MAX_VALUE, ID_THEME_PARENT, ID_THEME_DEP)
                SELECT @v_New_Campaign_ID, ID_QST_PARENT, ID_QST_DEP, BASIS_RULE, RULE_TYPE, MIN_VALUE, MAX_VALUE, ID_THEME_PARENT, ID_THEME_DEP FROM CAMP_QST_DEP WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_QST_Q (ID_CAMP, ID_THEME, ID_QUEST, G_B_QST_LIE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, POIDS, NM_ORDRE, ID_MODEL, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, DESCRIPTION)
                SELECT @v_New_Campaign_ID, ID_THEME, ID_QUEST, G_B_QST_LIE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, POIDS, NM_ORDRE, ID_MODEL, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, DESCRIPTION FROM CAMP_QST_Q WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_QST_R (ID_CAMP, ID_THEME, ID_QUEST, ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION)
                SELECT @v_New_Campaign_ID, ID_THEME, ID_QUEST, ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION FROM CAMP_QST_R WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id

        INSERT INTO CAMP_QST_TH (ID_CAMP, ID_THEME, ID_PERE, LIB_THEME, NM_ORDRE, POIDS, THEME_LIE, DESCRIPTION)
                SELECT @v_New_Campaign_ID, ID_THEME, ID_PERE, LIB_THEME, NM_ORDRE, POIDS, THEME_LIE, DESCRIPTION FROM CAMP_QST_TH WITH(ROWLOCK, UPDLOCK) WHERE ID_CAMP = @v_Duplicated_Campaign_Id
        
        COMMIT";

        $params = [
            ':DUP_CAMP_ID'  => $campaignToDuplicate,
            ':NEW_CAMP_ID'  => $newCampaign
        ];

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

        $duplication->execute($params);

        return new Response();
    }

    /**
     * @return array<mixed>
     */
    public function getResultatCampaign(string $idCamp, string $codeFou = '', string $idPere = '')
    {
        $req = "select CAMP_QST_TH.LIB_THEME,CAMP_NOT_FIN.* from CAMP_QST_TH with (nolock)
                left join CAMP_NOT_FIN with (nolock) on CAMP_NOT_FIN.ID_CAMP = CAMP_QST_TH.ID_CAMP and CAMP_NOT_FIN.ID_THEME = CAMP_QST_TH.ID_THEME
                where CAMP_QST_TH.ID_CAMP = :ID_CAMP and CODE_FOU = :CODE_FOU and CAMP_QST_TH.ID_PERE = :ID_PERE and ID_QUEST = 0";

        $params = [
            ':ID_CAMP'  => $idCamp,
            ':CODE_FOU'  => $codeFou,
            ':ID_PERE'  => $idPere,
        ];

        return $this->fetchAssocAll($req, $params);
    }
    
    /**
     * closeCampaign
     *
     * @param  string $id
     * @param  bool $confirm
     * @return array<mixed>
     */
    public function closeCampaign(string $id, bool $confirm = false) : ?array
    {
        $query = "EXEC STD_Evaluation_Cloture_Campagne 0,'',:ERROR_SORTIE,:USER_LOGIN,:ID_CAMP,".($confirm?"0x54":"0x46");

        $stmt = $this->pdo->prepare($query);
        $stmt->bindParam(':ID_CAMP', $id, \PDO::PARAM_STR);
        $stmt->bindParam(':USER_LOGIN', $_SESSION['Session_Login'], \PDO::PARAM_STR);
        $stmt->bindParam(':ERROR_SORTIE', $error, \PDO::PARAM_STR|\PDO::PARAM_INPUT_OUTPUT, 500);
        $stmt->execute();
        $return = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $this->lightReturn($return);
    }
    
    /**
     * validationCampaign
     *
     * @param  string $id
     * @param  bool $confirm
     * @return array<mixed>
     */
    public function validationCampaign(string $id, bool $confirm = false) : array
    {
        $code ='';
        $query = "EXEC STD_Evaluation_ValidationReturn 0,'',:ERROR_MESSAGE, :ID_CAMP,:USER_LOGIN,:USER_LANGUE, :USER_SOCIETE, ".($confirm==2?"0x54":"0x46").",:ERROR_VALIDATION , :ERROR_CODE " ;
        
        $stmt = $this->pdo->prepare($query);
        $stmt->bindParam(':ERROR_MESSAGE', $error, \PDO::PARAM_STR|\PDO::PARAM_INPUT_OUTPUT, 250);
        $stmt->bindParam(':ID_CAMP', $id, \PDO::PARAM_STR);
        $stmt->bindParam(':USER_LOGIN', $_SESSION['Session_Login'], \PDO::PARAM_STR);
        $stmt->bindParam(':USER_LANGUE', $_SESSION['Session_Langue'], \PDO::PARAM_STR);
        $stmt->bindParam(':USER_SOCIETE', $_SESSION['Session_SocUsr'], \PDO::PARAM_STR);

        $stmt->bindParam(':ERROR_VALIDATION', $validation, \PDO::PARAM_INT|\PDO::PARAM_INPUT_OUTPUT, 1);
        $stmt->bindParam(':ERROR_CODE', $code, \PDO::PARAM_STR|\PDO::PARAM_INPUT_OUTPUT, 100);
        $stmt->execute();

        $resultBody =  $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt->closeCursor();

        return [
            'result' => $this->lightReturn($resultBody),
            'error' => $error,
            'errorValidation' => $validation,
            'erreurCode' => $code
        ];
    }
    
    /**
     * devalidCampagne
     *
     * @param  string $id
     * @return array<mixed>
     */
    public function devalidCampagne(string $id)
    {
        $query = "EXEC STD_Evaluation_DeValidation 0,'',:ERROR_MESSAGE, :ID_CAMP,:USER_LOGIN,:ERROR_VALIDATION" ;
        
        $stmt = $this->pdo->prepare($query);
        $stmt->bindParam(':ERROR_MESSAGE', $error, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 250);
        $stmt->bindParam(':ID_CAMP', $id, PDO::PARAM_STR);
        $stmt->bindParam(':USER_LOGIN', $_SESSION['Session_Login'], PDO::PARAM_STR);

        $stmt->bindParam(':ERROR_VALIDATION', $validation, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 1);
        $stmt->execute();

        $resultBody =  $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt->closeCursor();

        return [
            'result' => $this->lightReturn($resultBody),
            'error' => $error,
            'errorValidation' => $validation
        ];
    }
}
