<?php

namespace Modules\Evaluation\Repository;

use Entity\Model\CampSoc;
use GuzzleHttp\Psr7\Response;
use Helpers\Filter\FilterTable;
use Helpers\Outil\Date;
use Helpers\Outil\Sql;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use PDO;
use PDOException;

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

    /**
     * @var mixed $pdoWithoutODBC
     */
    private $pdoWithoutODBC;

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

    /**
     * @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, 
            CASE 
                WHEN (CONCAT(campaign.DTEFIN_PREVUE, campaign.HEUREFIN_PREVUE) <= :today_0 AND campaign.STATUT = 'E')
                THEN 'A'
                ELSE campaign.STATUT
            END AS 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'],
            ':today_0' => \date('YmdHis')
        ];

        if (isset($post['filter_STATUT']) && $post['filter_STATUT'] == 'A') {
            $query .= " AND (campaign.STATUT = 'E' AND CONCAT(campaign.DTEFIN_PREVUE, campaign.HEUREFIN_PREVUE) <= :today_1)";
            $params[':today_1'] = \date('YmdHis');
            unset($post['filter_STATUT']);
        }

        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_campaign.CREEEPAR'] = $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, 
            DTEDEB_PREVUE, DTEFIN_PREVUE, HEUREDEB_PREVUE, HEUREFIN_PREVUE, creator.NOM AS CREATOR, s.CODE_STE, soc.RAIS_SOC
        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 SOCIETE soc WITH(NOLOCK)
            ON soc.CODE_STE = s.CODE_STE
        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, DTEDEB_PREVUE, DTEFIN_PREVUE, HEUREDEB_PREVUE, HEUREFIN_PREVUE, COD_PER)
        VALUES (:ID_CAMP, :LIBELLE, :DESCRIPT, :TOUTESTE, :ID_GRILLE, :STATUT, :CREEEPAR, :DTECREAT, :DTEDEB_PREVUE, :DTEFIN_PREVUE, :HEUREDEB_PREVUE, :HEUREFIN_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'),
            ':DTEDEB_PREVUE'    => $post['DTEDEB_PREVUE'],
            ':DTEFIN_PREVUE'    => $post['DTEFIN_PREVUE'],
            ':HEUREDEB_PREVUE'  => str_replace(':', '', $post['HEUREDEB_PREVUE']),
            ':HEUREFIN_PREVUE'  => str_replace(':', '', $post['HEUREFIN_PREVUE']),
            ':COD_PER'          => $post['COD_PER'],
            ':TOUTESTE'         => sql::toBool($post['TOUTESTE'] ?? false),
        ];

        return $this->execute($query, $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 les valeurs enregistrées dans le post (notamment pour garder les filtres en changeant de page)
        $filters = $post;

        $query = "SELECT * FROM (SELECT F.*, COUNTRY.LIBELLE PAYS,
            (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 'P'
                WHEN F.OCCASION = 0x54 THEN 'O'
                ELSE 'R'
            END AS TYPE
        FROM FOURNISS F WITH(NOLOCK)
            LEFT JOIN COUNTRY WITH (NOLOCK) ON COUNTRY.CODE_PAYS = F.CODE_PAYS) F 
        WHERE CODE NOT IN 
            (SELECT CODE_FOU 
            FROM CAMP_LST_FOUR WITH(NOLOCK) 
            WHERE ID_CAMP = :id) 
        ";

        if (isset($filters['filter_typeFourniss']) && $filters['filter_typeFourniss'] != '') {
            $query .= " AND EXISTS (SELECT CODFOURN 
                FROM FOU_LSTFOURN flf WITH(NOLOCK)
                    LEFT JOIN LST_FOURN lf WITH(NOLOCK) ON lf.CODLST = flf.CODLST
                WHERE F.CODE = flf.CODFOURN  
                    AND lf.LIBELLE = :LIBELLE_TYPE_FOURN) ";

            $params[':LIBELLE_TYPE_FOURN'] = $filters['filter_typeFourniss'];
            unset($filters['filter_typeFourniss']);
        }

        /** Filter complémentaire **/
        if (isset($filters['filter_recherche']) && $filters['filter_recherche'] != '') {
            $query .= " AND (UPPER(F.RS) LIKE :value5  OR UPPER(F.VILLE1) LIKE :value6 OR UPPER(F.PAYS) LIKE :value7 
                OR UPPER(F.CODE) LIKE :value8 OR UPPER(F.CPOSTAL) LIKE :value9)";
            $params[':value5'] = '%' . mb_strtoupper($filters['filter_recherche']) . '%';
            $params[':value6'] = '%' . mb_strtoupper($filters['filter_recherche']) . '%';
            $params[':value7'] = '%' . mb_strtoupper($filters['filter_recherche']) . '%';
            $params[':value8'] = '%' . mb_strtoupper($filters['filter_recherche']) . '%';
            $params[':value9'] = '%' . mb_strtoupper($filters['filter_recherche']) . '%';

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

        if (isset($filters['SSFAM']) && $filters['SSFAM']) {
            if ($filters['SSFAMCond'] == 'egal') {
                $query .= " AND F.CODE IN (SELECT CODE_FOUR FROM SSFAMFOU WITH(NOLOCK) WHERE CODE_SFAM = :SSFAM)";
            } else {
                $query .= " AND F.CODE NOT IN (SELECT CODE_FOUR FROM SSFAMFOU WITH(NOLOCK) WHERE CODE_SFAM = :SSFAM)";
            }
            $params[':SSFAM'] = $filters['SSFAM'];
            unset($filters['SSFAM']);
        }

        if (isset($filters['DOMAINE']) && $filters['DOMAINE']) {
            $domaine = htmlentities($filters['DOMAINE']);
            $conditionDomaine = $filters['DOMAINECond'] == 'egal' ? 'IN' : 'NOT IN';

            $query .= " AND F.CODE {$conditionDomaine} (
                SELECT SSFAMFOU.CODE_FOUR 
                FROM D_C_FAM_SSFAM WITH(NOLOCK)
                    LEFT JOIN SSFAMFOU WITH(NOLOCK) ON SSFAMFOU.CODE_SFAM = D_C_FAM_SSFAM.CODE
                WHERE SSFAMFOU.CODE_FOUR != '' AND CODE_DC = '{$domaine}' GROUP BY SSFAMFOU.CODE_FOUR)";

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

        if (isset($filters['CLEFFOUR']) && $filters['CLEFFOUR']) {
            if ($filters['CLEFFOURCond'] == 'egal') {
                $query .= " AND F.CODE IN (SELECT COD_FOUR FROM CLEFFOUR WITH(NOLOCK) WHERE MOTCLEF= :CLEFFOUR)";
            } else {
                $query .= " AND F.CODE NOT IN (SELECT COD_FOUR FROM CLEFFOUR WITH(NOLOCK) WHERE MOTCLEF= :CLEFFOUR)";
            }

            $params[':CLEFFOUR'] = $filters['CLEFFOUR'];
            unset($filters['CLEFFOUR']);
        }

        if (isset($filters['RAT_PART']) && $filters['RAT_PART']) {
            $ratPartNot = '';
            if ($filters['RAT_PART'] == 'NON') {
                $ratPartNot = " NOT ";
            }
            $ratPart = " AND {$ratPartNot} EXISTS(SELECT CODE_FOU FROM FOUR_PARTENAIRE_DATA WITH(NOLOCK) WHERE CODE_FOU = F.CODE)";

            $query .= $ratPart;
        }

        if (isset($filters['filter_RS']) && $filters['filter_RS'] != '') {
            $query .= " AND (UPPER(F.RS) LIKE :RSS1 OR UPPER(F.ENSCOMM) LIKE :RSS2 )";
            $params[':RSS1'] = '%' . mb_strtoupper($filters['filter_RS']) . '%';
            $params[':RSS2'] = '%' . mb_strtoupper($filters['filter_RS']) . '%';
            unset($filters['filter_RS']);
        }

        if (isset($filters['filter_motclef']) && $filters['filter_motclef'] != '') {
            $query .= " AND EXISTS (SELECT MOTCLEF FROM CLEFFOUR WITH(NOLOCK) WHERE COD_FOUR = F.CODE AND MOTCLEF = :value11)";
            $params[':value11'] = $filters['filter_motclef'];

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

        if (isset($filters['filter_sousFamille']) && $filters['filter_sousFamille'] != '') {
            $query .= " AND EXISTS (SELECT CODE_SFAM FROM SSFAMFOU WITH(NOLOCK) WHERE CODE_FOUR = F.CODE AND CODE_SFAM = :value12)";
            $params[':value12'] = $filters['filter_sousFamille'];

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

        if (isset($filters['filter_article']) && $filters['filter_article'] != '') {
            $query .= " AND EXISTS (SELECT CODE_ART FROM ARTFOURN WITH(NOLOCK) WHERE FOU = F.CODE AND CODE_ART = :value13)";
            $params[':value13'] = $filters['filter_article'];

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

        if (isset($filters['filter_typeAchat']) && $filters['filter_typeAchat'] != '') {
            $query .= " AND EXISTS (SELECT TACFOTAC FROM TAC_TYFO WITH(NOLOCK) WHERE TACFOFOU = F.CODE AND TACFOTAC = :value14)";
            $params[':value14'] = $filters['filter_typeAchat'];

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

        if (isset($filters['filter_valid']) && $filters['filter_valid'] != '') {
            $today = date('Ymd');
            if ($filters['filter_valid'] == 'VALIDE') {
                $query .= " AND ((F.DTEVALMIN <= '{$today}') AND F.DTEVALMAX > '{$today}' OR F.DTEVALMAX = '00000000')";
            }
            if ($filters['filter_valid'] == 'NON_VALIDE') {
                $query .= " AND ((F.DTEVALMAX <> '00000000' AND F.DTEVALMAX < '{$today}'))";
            }

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

        if (isset($filters['filter_PARTENAIRE']) && $filters['filter_PARTENAIRE'] != '') {
            $query .=  " AND F.CODE IN (
                SELECT CODE_FOU FROM FOUR_PARTENAIRE_DATA WITH(NOLOCK) 
                WHERE CODEPARTENAIRE = :partenaire) ";
            $params[':partenaire'] = $filters['filter_PARTENAIRE'];

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

        if (isset($filters['filter_DOMAINE']) && $filters['filter_DOMAINE'] != '') {
            $query .= " AND F.CODE IN (
                SELECT SSFAMFOU.CODE_FOUR 
                FROM D_C_FAM_SSFAM WITH(NOLOCK)
                    LEFT JOIN SSFAMFOU WITH(NOLOCK) ON SSFAMFOU.CODE_SFAM = D_C_FAM_SSFAM.CODE
                WHERE SSFAMFOU.CODE_FOUR != '' AND CODE_DC = :domaine 
                GROUP BY SSFAMFOU.CODE_FOUR)";
            $params[':domaine'] = $filters['filter_DOMAINE'];

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

        // filtres 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);

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

            if (!empty($queryArray[1])) {
                $params = array_merge($params, $queryArray[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 ';
        
        $i = 1;
        $params = [];
        foreach ($suppliers as $supplier) {
            $query .= '(:ID_CAMP' . $i . ', :FOU' . $i . '), ';
            $params[':ID_CAMP' . $i] = $campaignId;
            $params[':FOU' . $i] = $supplier;
            $i++;
        }

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

        $this->execute($query, $params);
    }

    /**
     * companiesMultiInsert
     *
     * @param  int $campaignId
     * @param  array<int, string> $companies
     * @return void
     */
    public function companiesMultiInsert(int $campaignId, array $companies): void
    {
        // on supprime les données de CAMP_SOC si jamais on est en modif
        $query = "DELETE FROM CAMP_SOC WHERE ID_CAMP = :ID_CAMP";

        $this->execute($query, [':ID_CAMP' => $campaignId]);

        // on insère
        $query = "INSERT INTO CAMP_SOC (ID_CAMP, CODE_STE)
        VALUES ";

        $i = 1;
        $params = [];
        foreach ($companies as $company) {
            $query .= '(:ID_CAMP' . $i . ', :SOC' . $i . '), ';
            $params[':ID_CAMP' . $i] = $campaignId;
            $params[':SOC' . $i] = $company;
            $i++;
        }
        $query = \rtrim($query, ', ');

        $this->execute($query, $params);
    }

    /**
     * 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> $queryClause (optional) une éventuelle clause 'WHERE'
     * @return int
     */
    public function getMaxOrder(string $table, string $designation, array $queryClause = [])
    {
        $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($queryClause) && count($queryClause) > 0) {
            foreach ($queryClause as $key => $clause) {
                $query .= ' AND ' . $key . ' = ' . $clause;
            }
        }

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

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

    /**
     * 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
    {
        $error= '';
        
        $query = "EXEC STD_Evaluation_Cloture_Campagne 0,'',:ERROR_SORTIE,:USER_LOGIN,:ID_CAMP," . ($confirm ? "0x54" : "0x46");

        $stmt = $this->pdoWithoutODBC->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();

        try {
            $return = $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            $return = [];
        }
    
        return $this->lightReturn($return);
    }

    /**
     * validationCampaign
     *
     * @param  string $id
     * @param  bool $confirm
     * @return array<mixed>
     */
    public function validationCampaign(string $id, bool $confirm = false): array
    {
        $code = '';
        $error ='';
        $validation=0;

        $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->pdoWithoutODBC->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();

        try {
            $resultBody =  $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            $resultBody = [];
        }

        $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)
    {
        
        $error ='';
        $validation = 0;

        $query = "EXEC STD_Evaluation_DeValidation 0,'',:ERROR_MESSAGE, :ID_CAMP,:USER_LOGIN,:ERROR_VALIDATION";

        $stmt = $this->pdoWithoutODBC->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();
        
        try {
            $resultBody =  $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            $resultBody = [];
        }
        
        $stmt->closeCursor();

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