<?php

namespace Modules\Consultation\Repository;

use Helpers\Outil\ArrayHelper;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\User\User;
use Modules\GlobalApplication\Repository\Societe;

class GestionConsultation extends AbsRepository
{

    private \Helpers\Pagination\PaginatorGestion $paginator;

    public function __construct(\PDO $pdo)
    {
        parent::__construct($pdo);
        $this->paginator = new PaginatorGestion();
    }

    /**
     * @param array <string> $post
     * @param array <string> $listeAO
     * @return array<mixed>
     */
    public function listeConsultation(array $post, array $listeAO = [], ?string $listeSelect = null): ?array
    {
        $savePost = $post;
        $arrayReq = [];

        if ($listeSelect) {
            $listeSelect = str_replace('oxaExport_STATUS', "(CASE 
					WHEN AO_ENTET.DTE_VALID_AO = '00000000' THEN 'PREP'
					WHEN AO_ENTET.VALCHXFOU = '00000000' THEN 'ENCO'
					ELSE 'TERM'
				END) as oxaExport_STATUS", $listeSelect);
            $select = $listeSelect;
        } else {
            $select = "AO_ENTET.*,
                HASTUTIL.NOM,
                (select count(distinct(CODE_FOU)) from AO_FOUR with(nolock) where NO_AO = AO_ENTET.NO_AO) countFrs,
                (CASE WHEN EXISTS(
                    SELECT 1 
                    FROM AO_FOUR with(nolock)
                    WHERE AO_FOUR.NO_AO = AO_ENTET.NO_AO
                        AND AO_FOUR.EVE_IMP != 0
                ) THEN 1 ELSE 0 END) as hasReponseEnAttente, "
                . $this->selectFlagExist(
                    "SELECT 1 FROM DOC_ATT with (nolock)
                        WHERE DOC_ATT.NO_DA = 'AO_ENTET' 
                            AND REPLACE(DOC_ATT.COD_ENR,' ','') = AO_ENTET.NO_AO",
                    'hasDocInt'
                ) . ",
                memosBlocNote.CONTMEMO as blocNote,
                (CASE 
					WHEN AO_ENTET.DTE_VALID_AO = '00000000' THEN 'PREP'
					WHEN AO_ENTET.VALCHXFOU = '00000000' THEN 'ENCO'
					ELSE 'TERM'
				END) Statut";
        }

        $post['filter_Statut'] = $post['filter_Statut'] ?? $post['filter_STATUT'] ?? '';

        $req = "select $select
            from AO_ENTET with (nolock)
            left join HASTUTIL with (nolock) on HASTUTIL.CODE = AO_ENTET.ACH
            LEFT JOIN AOBNOTE as aobnote with (nolock) ON aobnote.NO_AO = AO_ENTET.NO_AO
            LEFT JOIN MEMOS as memosBlocNote with (nolock) ON aobnote.AO_NOTE = memosBlocNote.NODEMEMO";

        if ($post['filter_Statut'] == 'ACAV') {
            $req .= " 
            LEFT JOIN (SELECT
                            COUNT(AO_FOUR.NO_AO) AS TOTAL, AO_FOUR.NO_AO
                        FROM
                            AO_FOUR
                        LEFT JOIN AO_ENTET 
                            ON AO_ENTET.NO_AO = AO_FOUR.NO_AO
                        WHERE AO_FOUR.ACC_CONF_VALID = 0x46
                            AND AO_FOUR.FIC_ACC_CONF_SIGNE <> ''
                        GROUP BY
                            AO_FOUR.NO_AO) 
                AS CountAccConfAValider
                ON CountAccConfAValider.NO_AO = AO_ENTET.NO_AO
             ";
        }

        $req .= " where 1=1 ";

        $listSoc = (new Societe($this->pdo))->listRestrictedSocieteForUser();
        if ($listSoc !== null) {
            $sqlInSoc = implode("','", array_column($listSoc, 'CODE_STE'));
            $req .= " AND (
                (
                    TYPE_SG = 'S' and AO_ENTET.CODE_STE in ('" . $sqlInSoc . "')
                ) OR (
                    TYPE_SG = 'G'
                    AND AO_ENTET.CODE_STE in (
                        SELECT DISTINCT GROUPE
                        FROM SOCIETE with (nolock)
                        WHERE SOCIETE.CODE_STE in ('" . $sqlInSoc . "')))) ";
        }

        if (!empty(trim($post['filter_Statut']))) {
            switch ($post['filter_Statut']) {
                case 'PREP': // Préparation
                    $req .= ' and AO_ENTET.DTE_VALID_AO = \'00000000\'';
                    break;
                case 'VALC': // Validation en cours;
                    $req .= ' and AO_ENTET.DTE_VALID_AO <> \'00000000\'';
                    break;
                case 'PRCO': // 'En préparation et en cours'
                    $req .= ' and (AO_ENTET.DTE_VALID_AO = \'00000000\' or AO_ENTET.VALCHXFOU = \'00000000\') ';
                    break;
                case 'ACAV': // 'Accord Confidentialite A Valider'
                    $req .= " AND CountAccConfAValider.TOTAL > 0";
                    break;
                case 'ENCO': // En cours
                    $req .= ' and AO_ENTET.DTE_VALID_AO<>\'00000000\' and AO_ENTET.VALCHXFOU=\'00000000\'';
                    break;
                case 'ENVO': // A envoyer
                    $req .= ' and AO_ENTET.DTE_VALID_AO<>\'00000000\' AND AO_ENTET.VALCHXFOU=\'00000000\' ' .
                        'and exists (
                                select null from AO_FOUR with(nolock) 
                                left join FOURNISS with (nolock) on FOURNISS.CODE = AO_FOUR.CODE_FOU
                                where AO_FOUR.NO_AO = AO_ENTET.NO_AO and AO_FOUR.EVE_IMP=0 and FOURNISS.GEREPORTAIL = 0x46
                            )';
                    break;
                case 'VALF': // Validation choix fournisseur
                    $req .= ' and AO_ENTET.VALCHXFOU=\'00000000\' and AO_ENTET.DTE_VALID_AO <> \'00000000\' ';
                    break;
                case 'TERM': // Terminé
                    $req .= ' and AO_ENTET.VALCHXFOU<>\'00000000\'';
                    break;
                case 'NON_CLOS':
                    $req .= " AND AO_ENTET.NO_AO in (
                        select distinct(AO_ENTET.NO_AO) 
                        from AO_ENTET with(nolock)
                        left join AO_LIGNE with(nolock) on AO_LIGNE.NO_AO = AO_ENTET.NO_AO
                        LEFT JOIN DA_ENTET with(nolock) on AO_LIGNE.NO_DA = DA_ENTET.NO_DA
                        where DA_ENTET.NO_DEM = '" . $_SESSION["Session_Login"] . "'
                            and AO_ENTET.VALCHXFOU = 0)";
                    break;
                default:
                    break;
            }
            $statut = trim($post['filter_Statut']);
            unset($post['filter_Statut']);
        }

        if ($listeAO) {
            $req .= $this->makeWhereIn('AO_ENTET.NO_AO', $listeAO, $arrayReq);
        }

        if (isset($post['ACHETEUR']) && $post['ACHETEUR'] != '') {
            $post['filter_egal_ACH'] = $post['ACHETEUR'];
        }

        if (isset($post['questionSansReponse'])) {
            $req .= "and exists (
                        select 
                            * 
                        from AO_FOUR_QR with (nolock) 
                        where AO_ENTET.NO_AO = AO_FOUR_QR.NO_AO 
                        and REP = '' 
                        and (AUTEUR_OXALINK != '' or (AUTEUR_OXALINK = '' and DATE_ENV_QUEST != '00000000'))
                    )";
        }

        if (isset($post['contributions'])) {
            $arrayReq[':user'] = $_SESSION['Session_Login'];
            $req .= "AND AO_ENTET.NO_AO in (
                        SELECT DISTINCT aqt.NO_AO
                        FROM AO_QST_TH aqt WITH (NOLOCK)
                            LEFT JOIN AO_CONTRIB_GRP acg WITH (NOLOCK) ON acg.NO_AO = aqt.NO_AO AND acg.GROUPE_CONTRIB = aqt.PROFNOTEUR2 AND aqt.PROFNOTEUR1 = 'G'
                            LEFT JOIN AO_CONTRIB ac WITH (NOLOCK) ON ac.NO_AO = aqt.NO_AO AND aqt.PROFNOTEUR1 = ''
                            LEFT JOIN AO_REP_QST arq WITH (NOLOCK) ON arq.NO_AO = aqt.NO_AO AND arq.ID_THEME = aqt.ID_THEME AND arq.ID_THEME = aqt.ID_THEME
                                AND arq.CONTRIB = CASE 
                                    WHEN aqt.PROFNOTEUR1 = 'G' 
                                    THEN acg.CONTRIB 
                                    WHEN aqt.PROFNOTEUR1 = 'N'
                                    THEN aqt.PROFNOTEUR2
                                    WHEN aqt.PROFNOTEUR1 = ''
                                    THEN ac.CONTRIB 
                                END
                            LEFT JOIN HASTUTIL ha WITH (NOLOCK) ON ha.CODE = CASE 
                                WHEN aqt.PROFNOTEUR1 = 'G' 
                                THEN acg.CONTRIB 
                                WHEN aqt.PROFNOTEUR1 = 'N'
                                THEN aqt.PROFNOTEUR2
                                WHEN aqt.PROFNOTEUR1 = ''
                                THEN ac.CONTRIB 
                            END
                        WHERE aqt.ID_THEME LIKE 'I%' AND :user = CASE WHEN aqt.PROFNOTEUR1 = 'G' 
                                THEN acg.CONTRIB 
                                WHEN aqt.PROFNOTEUR1 = 'N'
                                THEN aqt.PROFNOTEUR2
                                WHEN aqt.PROFNOTEUR1 = ''
                                THEN ac.CONTRIB 
                            END
                    )";
        }

        if (isset($post['GR_SOCIETE']) && $post['GR_SOCIETE'] != '') {
            $arrayReq[':grpSoc'] = $post['GR_SOCIETE'];
            $req .= " and (AO_ENTET.CODE_STE  = :grpSoc and TYPE_SG = 'G')";
        }

        if (isset($post['CODE_STE']) && $post['CODE_STE'] != '') {
            $req .= " and (AO_ENTET.CODE_STE = :codeSte and TYPE_SG = 'S')";

            $arrayReq[':codeSte'] = $post['CODE_STE'];
        }

        if (isset($post['FAMILLE']) && $post['FAMILLE'] != '') {
            $req .= "";
        }

        if (isset($post['filter_FOUdebut-----finRS']) && $post['filter_FOUdebut-----finRS'] != '') {
            $req .= " and AO_ENTET.NO_AO in (select distinct(NO_AO) from AO_FOUR with (nolock) where CODE_FOU = :codeFou)";

            $arrayReq[':codeFou'] = $post['filter_FOUdebut-----finRS'];
            unset($post['filter_FOUdebut-----finRS']);
        }

        if (isset($post['filter_egal_BUDG_ENT']) && $post['filter_egal_BUDG_ENT'] != '') {
            $req .= " and AO_ENTET.BUDG_ENT = :budget";

            $arrayReq[':budget'] = $post['filter_egal_BUDG_ENT'];
            unset($post['filter_egal_BUDG_ENT']);
        }

        if (isset($post['ARTICLE']) && $post['ARTICLE']) {
            if ($post['ARTICLECond'] == 'egal') {
                $req .= " and AO_ENTET.NO_AO in (select NO_AO from AO_LIGNE with (nolock) WHERE CODE_ART= :ARTICLE)";
            } else {
                $req .= " and AO_ENTET.NO_AO not in (select NO_AO from AO_LIGNE with (nolock) WHERE CODE_ART= :ARTICLE)";
            }
            $arrayReq[':ARTICLE'] = $post['ARTICLE'];
        }

        if (!empty($post['NO_DA'])) {
            $not = $post['NO_DACond'] == 'different' ? 'NOT' : '';
            $req .= " AND $not EXISTS (
                SELECT 1 FROM AO_LIGNE with(nolock)
                WHERE AO_LIGNE.NO_AO = AO_ENTET.NO_AO AND AO_LIGNE.NO_DA = :NO_DA
            ) ";
            $arrayReq[':NO_DA'] = $post['NO_DA'];
        }

        // Ambiguous column
        $post['filter_AO_ENTET.NO_AO'] = $post['filter_NO_AO'] ?? null;
        unset($post['filter_NO_AO']);
        $post['tri_AO_ENTET.NO_AO'] = $post['tri_NO_AO'] ?? null;
        unset($post['tri_NO_AO']);

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

        if (isset($statut)) {
            $_SESSION['PaginatorGestion']['infoPagination']['filter']['filter_Statut'] = $statut;
        }

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

    public function countToBeSent(string $acheteur): int
    {
        $post = [
            'filter_STATUT' => 'ENVO',
            'ACHETEUR' => $_SESSION["Session_Login"]
        ];

        $req = "SELECT COUNT(AO_ENTET.*) as count
            FROM AO_ENTET with (nolock)
            WHERE AO_ENTET.ACH = :acheteur";

        $listSoc = (new Societe($this->pdo))->listRestrictedSocieteForUser();
        if ($listSoc !== null) {
            $listeSociete = implode("','", array_column($listSoc, 'CODE_STE'));
            $req .= " and ((TYPE_SG = 'S' and AO_ENTET.CODE_STE in ('$listeSociete')) or (TYPE_SG = 'G' and AO_ENTET.CODE_STE in (select DISTINCT GROUPE from SOCIETE with (nolock) where SOCIETE.CODE_STE in ('$listeSociete')))) ";
        }
        $req .= ' and AO_ENTET.DTE_VALID_AO<>\'00000000\' AND AO_ENTET.VALCHXFOU=\'00000000\' ' . 'and exists (select null from AO_FOUR with(nolock) where AO_FOUR.NO_AO = AO_ENTET.NO_AO and AO_FOUR.EVE_IMP=0)';
        unset($post['filter_STATUT']);


        $post['filter_egal_ACH'] = $post['ACHETEUR'];


        return $this->fetchAssocOne($req, [
            ':acheteur' => $acheteur,
        ])['count'] ?? 0;
    }



    /**
     * @return array<mixed>
     */
    public function listeLigneConsultation(string $numAO): ?array
    {
        $req = "SELECT AO_LIGNE.*, ISNULL((CASE WHEN t.DESIGNAT = '' THEN NULL ELSE t.DESIGNAT END), SSFAMILL.DESIGNAT) AS sousFamille, GAMMES.DESIGN gammes, FABRICAN.RS fabricant,MEMOS.CONTMEMO TITRE_AVANT_LIGNE, MEMOS2.CONTMEMO DES_COMP
                from AO_LIGNE with (nolock) 
                left join SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = AO_LIGNE.SSFAMILLE
                LEFT OUTER JOIN TRAD_SFA t WITH(NOLOCK) ON t.CODE_SFA = SSFAMILL.CODE_SFA AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
                left join GAMMES with (nolock) on GAMMES.CODEGAMME = AO_LIGNE.COD_GAM and GAMMES.CODEFABR  = AO_LIGNE.COD_FAB
                left join FABRICAN with (nolock) on FABRICAN.CODE = AO_LIGNE.COD_FAB 
                left join MEMOS with(nolock) on MEMOS.NODEMEMO = AO_LIGNE.NM_ST_AV
                left join MEMOS MEMOS2 with(nolock) on MEMOS2.NODEMEMO = AO_LIGNE.NM_DCOMP
                where NO_AO = :numAO order by NO_PO asc";

        $arrayReq = [':numAO' => $numAO];

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

    /**
     * @return array<mixed>
     */
    public function listeFournisseurConsultation(string $numAO): ?array
    {
        $req = "SELECT
                AO_FOUR.NO_AO,
                AO_FOUR.CODE_FOU,
                case AO_FOUR.TYPE_FG when 'F' then FOURNISS.RS else GRPFOU.RS end as RS_FOU,
                case AO_FOUR.TYPE_FG when 'F' then FOURNISS.ENSCOMM else '' end as EC_FOU,
                AO_FOUR.NOM_CTCT    as NOM_CTCT,
                AO_FOUR.TEL_CTCT    as TEL_CTCT,
                AO_FOUR.FAX_CTCT    as FAX_CTCT,
                AO_FOUR.EMAILCONT   as EMAILCONT,
                AO_FOUR.FOU_RETEN   as FOU_RETEN,
                AO_FOUR.TYPE_FG   	as TYPE_FG,
                AO_ENTET.VALCHXFOU  as VALCHXFOU,
                AO_EVENT.DTE        as DTE_ENVOI,
                (CASE WHEN EXISTS (
                    SELECT * FROM AO_DAREP with(nolock)
                    WHERE AO_DAREP.NO_AO = AO_FOUR.NO_AO
                        AND AO_DAREP.CODE_FOU = AO_FOUR.CODE_FOU 
                        AND AO_DAREP.EVE_REP != 0 
                ) THEN 1 ELSE 0 END) as hasResponded
            FROM AO_FOUR with(nolock)
                left join AO_EVENT with(nolock) on AO_FOUR.NO_AO = AO_EVENT.NO_AO and AO_FOUR.EVE_IMP = AO_EVENT.NUM_ORD
                left join FOURNISS with(nolock) on AO_FOUR.CODE_FOU = FOURNISS.CODE
                left join GRPFOU with(nolock) on AO_FOUR.CODE_FOU = GRPFOU.CODE
                left join AO_ENTET with(nolock) on AO_ENTET.NO_AO = AO_FOUR.NO_AO
            WHERE AO_FOUR.NO_AO = :numAO and NOT EXISTS  (
				select * from AO_EVENT as EventRefus with (nolock) 
				WHERE EventRefus.NO_AO = AO_FOUR.NO_AO
				AND EventRefus.CODE = 'FRE'/* Fournisseur REfus */
				and EventRefus.CODE_FOU = FOURNISS.CODE
			)";

        $arrayReq = [':numAO' => $numAO];

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

    /**
     * @param array<string> $listFourId
     * @param array<string> $listGroupId
     * @_return array<string,array<array{ParentId:string,NOM:string,EMAIL:string,isOxalink:string}>> List of contact indexed by parent id
     * @return array<string,array<array<mixed>>> List of contact indexed by parent id
     */
    public function getMapContactAvailable(array $listFourId, array $listGroupId)
    {
        $param = [];
        $list = $this->fetchAssocAll(
            implode(' UNION ', array_filter([
                // Contact FOURNISS
                !empty($listFourId) ?
                    "SELECT 
                        CONCAT('F', CODE_FOU) as ParentId,
                        CONTFOUR.NOM,
                        CONTFOUR.EMAIL,
                        '0' as isOxalink
                    FROM CONTFOUR with(nolock)
                    WHERE CONTFOUR.VIBOXAP = 0x54
                        AND " . $this->makeWhereIn('CONTFOUR.CODE_FOU', $listFourId, $param, 'four_') :
                    null,
                // Contact four OXALINK
                !empty($listFourId) ?
                    "SELECT 
                        CONCAT('F', CONTFOUR.CODE_FOU) as ParentId,
                        CONTFOUR.NOM,
                        CONTFOUR.EMAIL,
                        '1' as isOxalink
                    FROM CONTFOUR with(nolock)
                    JOIN FOURNISS with(nolock) 
                        ON FOURNISS.CODE = CONTFOUR.CODE_FOU
                        AND FOURNISS.GEREPORTAIL = 0x54
                    JOIN OXALINK_USER with (nolock) on OXALINK_USER.USER_ID = CONTFOUR.OXALINK_ID
                    WHERE CONTFOUR.OXALINK_ID != '' 
                        AND OXALINK_USER.STATUT = 'A'
                        AND " . $this->makeWhereIn('CONTFOUR.CODE_FOU', $listFourId, $param, 'fouroxa_') :
                    null,
                // Contact GROUP
                !empty($listGroupId) ?
                    "SELECT 
                        CONCAT('G', CODE_GF) as ParentId,
                        CONTGF.NOM,
                        CONTGF.EMAIL,
                        '0' as isOxalink
                    FROM CONTGF with(nolock)
                    WHERE " . $this->makeWhereIn('CONTGF.CODE_GF', $listGroupId, $param, 'group_') :
                    null,
            ])),
            $param
        );

        // Filter : contact oxalink (oxalink contact override "normal" contact)
        $map = ArrayHelper::indexBy($list, 'ParentId');
        foreach ($map as $parentId => &$listContact) {
            $mapOxalink = ArrayHelper::indexBy($listContact, 'isOxalink');

            // Case : at least one contact oxalink is available -> override other kind of contact
            if (!empty($mapOxalink['1'])) {
                $listContact = array_filter($listContact, function ($contact) {
                    return $contact['isOxalink'] == '1';
                });
                continue;
            }

            $listContact = array_filter($listContact, function ($contact) {
                return $contact['isOxalink'] == '0';
            });
        }
        unset($listContact);

        return $map;
    }

    /**
     * @param array<string> $listFournissFullId List of "full id" (G|F + CODE) of fourniss or group
     * @return array<array<mixed>>
     */
    public function listFournissConsultEnvoiMail(string $numAO, array $listFournissFullId): ?array
    {
        $param = [':numAO' => $numAO];
        $listRow = $this->fetchAssocAll(
            "SELECT " . $this->generateFieldList([
                'AO_FOUR' => ['NO_AO', 'CODE_FOU', 'EMAILCONT', 'TYPE_FG'],
                'FOURNISS' => ['CODE', 'RS', 'COD_LNG', 'GEREPORTAIL'],
                'GRPFOU' => ['CODE', 'RS', 'COD_LNG'],
            ]) . "
            FROM AO_FOUR with(nolock)
            LEFT JOIN FOURNISS with(nolock) 
                ON FOURNISS.CODE = AO_FOUR.CODE_FOU 
                AND AO_FOUR.TYPE_FG = 'F'
            LEFT JOIN GRPFOU with(nolock) 
                ON GRPFOU.CODE = AO_FOUR.CODE_FOU
                AND AO_FOUR.TYPE_FG = 'G'
            WHERE AO_FOUR.NO_AO = :numAO
                AND " . $this->makeWhereIn(
                'CONCAT(AO_FOUR.TYPE_FG, AO_FOUR.CODE_FOU)',
                $listFournissFullId,
                $param
            ),
            $param
        );

        $listConsultFour = [];
        foreach ($listRow as $row) {
            $split = $this->splitRowByTable($row);
            $listConsultFour[] =
                $split['AO_FOUR']
                + [
                    'Fournisseur' => empty($split['FOURNISS']['CODE']) ? null : $split['FOURNISS'],
                    'GroupFou' => empty($split['GRPFOU']['CODE']) ? null : $split['GRPFOU'],
                ];
        }
        return $listConsultFour;
    }

    /**
     * @return array<mixed>
     */
    public function getInfoAo(string $idAo): ?array
    {
        return $this->fetchAssocOne(
            "SELECT 
                AO_ENTET.*,
                ACHETEUR.NOM as ACHETEUR,
                ACHETEUR.NOM as Acheteur_NOM,
                ACHETEUR.EMAIL as Acheteur_EMAIL,
                ACHETEUR.BCC_MAO as Acheteur_BCC_MAO,
                CE.RAIS_SOC,
                MEMOS.CONTMEMO as TITRE,
                MemoCommentaire.CONTMEMO as Commentaire,
                PRESCRIPTEUR.NOM as NOM_PRESCRIPTEUR,"
                . implode(',', [
                    $this->selectFlagExist(
                        "SELECT 1 FROM AO_FOUR with(nolock)
                        WHERE AO_FOUR.NO_AO = AO_ENTET.NO_AO
                            AND AO_FOUR.EVE_IMP != 0",
                        'hasReponseEnAttente'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 from AO_LIGNE with (nolock) 
                        where NO_AO = AO_ENTET.NO_AO",
                        'hasLigne'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 from AO_FOUR with (nolock)
                        where AO_FOUR.NO_AO = AO_ENTET.NO_AO",
                        'hasFourniss'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 FROM AO_QST_TH WITH (NOLOCK) 
                        WHERE NO_AO = AO_ENTET.NO_AO AND ID_THEME != 'APRIX' AND ID_THEME LIKE 'A%'",
                        'hasQst'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 FROM AO_QST_TH WITH (NOLOCK) 
                        WHERE NO_AO = AO_ENTET.NO_AO AND ID_THEME LIKE 'I%'", // si thème commence par "I" alors il s'agit d'un thème pour contrib
                        'hasQstContrib'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 FROM AO_CONTRIB WITH (NOLOCK) 
                        WHERE NO_AO = AO_ENTET.NO_AO",
                        'hasContrib'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 from AO_PIJO with (nolock)
                        where NO_AO = AO_ENTET.NO_AO and NOM_FIC != ''",
                        'hasPijo'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 from AO_CLAUS with (nolock)
                        where NO_AO = AO_ENTET.NO_AO",
                        'hasClause'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 FROM AO_DOC_ATTENDUS with (nolock)
                        WHERE AO_DOC_ATTENDUS.NO_AO = AO_ENTET.NO_AO",
                        'hasDocRep'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 FROM DOC_ATT with (nolock)
                        WHERE DOC_ATT.NO_DA = 'AO_ENTET' 
                            AND REPLACE(DOC_ATT.COD_ENR,' ','') = AO_ENTET.NO_AO",
                        'hasDocInt'
                    ),
                    $this->selectFlagExist(
                        "SELECT 1 FROM DOA_DATA dd with (nolock)
                        LEFT JOIN DOA_RECORD_VAL drv with (nolock) 
                            ON drv.ID_DOA = dd.ID_OBJET 
                            AND drv.ID_DATA = dd.ID_DATA 
                            AND drv.ID_DONNEE = dd.ID_DONNEE
                        LEFT JOIN DOA with (nolock) on DOA.ID = dd.ID_OBJET
                        WHERE drv.VALEUR = CAST(AO_ENTET.NO_AO AS varchar)
                            AND dd.LIE_A_OBJET = 0x54 
                            AND OBJET = 'NO_AO' 
                            AND DOA.STATUT = 'E'",
                        'hasProjetLie'
                    ),
                ])
                . " FROM AO_ENTET with (nolock)
            left join HASTUTIL ACHETEUR with(nolock) on ACHETEUR.CODE = AO_ENTET.ACH
            left join HASTUTIL PRESCRIPTEUR with(nolock) on PRESCRIPTEUR.CODE = AO_ENTET.PRESCRIPTEUR
            left join CENTRE CE with(nolock) on CE.CODE_CTR = AO_ENTET.EMETTEUR
            LEFT JOIN MEMOS with(nolock) on MEMOS.NODEMEMO = AO_ENTET.NM_TITRE
            LEFT JOIN MEMOS as MemoCommentaire with(nolock) on MemoCommentaire.NODEMEMO = AO_ENTET.NM_COMENT
            WHERE NO_AO = :idAo",
            [':idAo' => $idAo]
        );
    }

    /**
     * @return array<array<string>>
     */
    public function getInfoMailEnvoieInfoToDem(string $idAO) :array
    {
        return $this->fetchAssocAll(
            "SELECT DISTINCT
                AO_ENTET.NO_AO,
                Acheteur.CODE as Acheteur_CODE,
                Acheteur.NOM as Acheteur_NOM,
                DA_ENTET.NO_DA,
                Demandeur.CODE as Demandeur_CODE,
                Demandeur.NOM as Demandeur_NOM,
                Demandeur.EMAIL as Demandeur_EMAIL,
                Demandeur.COD_LNG as Demandeur_COD_LNG
            FROM AO_ENTET with(nolock)
            JOIn HASTUTIL as Acheteur with(nolock) ON Acheteur.CODE = AO_ENTET.ACH
            JOIN AO_LIGNE with(nolock) ON AO_LIGNE.NO_AO = AO_ENTET.NO_AO
            JOIN DA_ENTET with(nolock) ON DA_ENTET.NO_DA = AO_LIGNE.NO_DA
            JOIN HASTUTIL as Demandeur with(nolock) ON Demandeur.CODE = DA_ENTET.NO_DEM
            WHERE AO_ENTET.NO_AO = :id
                AND Demandeur.EMAIL != ''
            ",
            ['id' => $idAO]
        );
    }

    /**
     * @return array<mixed>
     */
    public function getListPJ(string $idAo): ?array
    {
        $req = "SELECT AO_PIJO.NO_AO, AO_PIJO.NO_ORDRE, AO_PIJO.TYPE_PJ, AO_PIJO.TITRE, AO_PIJO.LECTURE, AO_PIJO.NOM_FIC, 
            AO_PIJO.NOM_ORI, AO_PIJO.FIC_PDF 
        FROM AO_PIJO WITH (NOLOCK)
        WHERE NO_AO = :idAo
        ORDER BY NO_ORDRE";

        $arrayReq = [':idAo' => $idAo];

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

    /**
     * @return array<mixed>
     */
    public function getListClause(string $idAo): ?array
    {
        $req = "select * from AO_CLAUS with (nolock)
                left join MEMOS with(nolock) on MEMOS.NODEMEMO = NM_PARAG
                where NO_AO = :idAo";

        $arrayReq = [':idAo' => $idAo];

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

    /**
     * getListeFourniss
     *
     * @param  string $idAo
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getListeFourniss(string $idAo, array $post = []): ?array
    {
        $req = "SELECT
            AO_FOUR.*,
            FOURNISS.NO_SIRET, FOURNISS.RS, FOURNISS.COD_LNG,FOURNISS.GEREPORTAIL,
            COUNTRY.LIBELLE LIB_PAYS,
            SUBSTRING(( 
                select ',' + DESIGNAT AS 'data()' 
                from SSFAMFOU with (nolock) 
                    left join SSFAMILL with(nolock) on SSFAMILL.CODE_SFA = SSFAMFOU.CODE_SFAM
                where CODE_FOUR = AO_FOUR.CODE_FOU FOR XML PATH('') 
            ), 2 , 9999) as listeSousFamille,
            GRPFOU.NO_SIRET as NO_SIRET_GRP,
            GRPFOU.RS as RS_GRP,
            (
                select count(*) ctcOxalinkActif 
                from CONTFOUR with (nolock)
                join OXALINK_USER with (nolock) 
                    on OXALINK_USER.USER_ID = CONTFOUR.OXALINK_ID 
                    and OXALINK_USER.STATUT = 'A'
                where CODE_FOU = FOURNISS.CODE 
            ) as ctcOxalinkActif,
            IIF(Refus.CODE IS NULL, 0, 1) as hasRefus,
            Refus.COMMENTAIRE as 'Refus_COMMENTAIRE',
            Reponse.DTE_REP as 'Reponse.DTE_REP',
            Reponse.C_V as 'Reponse.C_V',
            Reponse.EVE_REP as 'Reponse.EVE_REP',
            Draft.DTE_REP as 'Draft.DTE_REP',
            Draft.C_V as 'Draft.C_V',
            Draft.EVE_REP as 'Draft.EVE_REP',
            LastRepValid.CODE as 'LastRepValid_CODE',
            LastRepValid.DTE as 'LastRepValid_DTE',
            LastRepValid.HEURE as 'LastRepValid_HEURE',
            (select STATUT_DEMANDE from WFT_DEM_ENT with (nolock) 
                WHERE WFT_DEM_ENT.TYPE_ENT='FOURNISS' 
                    and WFT_DEM_ENT.STATUT_DEMANDE IN ('1','0','S')
					and WFT_DEM_ENT.CODE_ENREG = AO_FOUR.CODE_FOU
            ) as WF_ATTENT
        FROM AO_FOUR with (nolock)
        left join FOURNISS with (nolock) on FOURNISS.CODE = AO_FOUR.CODE_FOU
        left join GRPFOU with (nolock) on GRPFOU.CODE = AO_FOUR.CODE_FOU
        left join COUNTRY with (nolock) on COUNTRY.CODE_PAYS = AO_FOUR.PAYS
        OUTER APPLY ( /* LEFT JOIN TOP 1 */
            SELECT TOP 1 * FROM AO_EVENT with (nolock) 
            WHERE AO_EVENT.NO_AO = AO_FOUR.NO_AO
            AND AO_EVENT.CODE_FOU = AO_FOUR.CODE_FOU
            AND AO_EVENT.TYPE_FG = AO_FOUR.TYPE_FG
            AND AO_EVENT.CODE = 'FRE'
        ) as Refus
        OUTER APPLY ( /* LEFT JOIN TOP 1 */
            SELECT TOP 1 * FROM AO_DAREP with (nolock) 
            WHERE AO_DAREP.NO_AO = AO_FOUR.NO_AO 
            AND AO_DAREP.CODE_FOU = AO_FOUR.CODE_FOU
            AND AO_DAREP.EVE_REP != 0 /* Not a draft */
        ) as Reponse
        OUTER APPLY ( /* LEFT JOIN TOP 1 */
            SELECT TOP 1 * FROM AO_DAREP with (nolock) 
            WHERE AO_DAREP.NO_AO = AO_FOUR.NO_AO 
            AND AO_DAREP.CODE_FOU = AO_FOUR.CODE_FOU
            AND AO_DAREP.EVE_REP = 0
        ) as Draft
        OUTER APPLY ( /* LEFT JOIN TOP 1 */
            SELECT TOP 1 * FROM AO_EVENT with (nolock) 
            WHERE AO_EVENT.NO_AO = AO_FOUR.NO_AO 
            AND AO_EVENT.CODE_FOU = AO_FOUR.CODE_FOU
            AND AO_EVENT.TYPE_FG = AO_FOUR.TYPE_FG
            AND AO_EVENT.CODE IN ('REP'/* Validation Reponse */)
            ORDER BY AO_EVENT.DTE DESC, AO_EVENT.HEURE DESC
        ) as LastRepValid
        where AO_FOUR.NO_AO = :idAo ";

        $arrayReq = [':idAo' => $idAo];

        // TODO : this is terrible, improve using a real Query Builder ?
        // This is to protect LastRepValid 'order by' from the PaginatorGestion
        if (/* Si pas de tri dans le post */empty(ArrayHelper::findFirst(
            array_keys(array_filter($post)),
            function ($key) {
                return str_starts_with($key, 'tri_');
            }
        ))) {
            $post['tri_CODE_FOU'] = 'ASC';
        }

        // Colonne ambigu
        $post['filter_AO_FOUR.NO_AO'] = $post['filter_NO_AO'] ?? null;
        unset($post['filter_NO_AO']);

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

        $this->paginator->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * @return array<mixed>
     */
    public function getMaxOrderClause(string $idAo): ?array
    {
        $req = "select max(NO_ORDRE) MAX_ORDER from AO_CLAUS with (nolock) where NO_AO = :idAo";

        $arrayReq = [':idAo' => $idAo];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param array <string> $post
     * @return array<mixed>
     */
    public function getListeFrs(string $idAo, array $post): ?array
    {
        $req = "SELECT * from (
                    select 'F' as 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 with(nolock) 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 AO_FOUR with (nolock) 
                        where NO_AO = :idAo and TYPE_FG= 'F'
                    )
                    
                    UNION
                    
                    select 
                        'G' as TYPE_FG,
                        CODE,
                        RS,
                        NO_SIRET,
                        CPOSTAL,
                        VILLE1,
                        '',
                        '' as listeSousFamille 
                    from GRPFOU with (nolock)
                    where CODE not in (
                        select CODE_FOU 
                        from AO_FOUR with (nolock) 
                        where NO_AO = :idAo2 and TYPE_FG= 'G'
                    ) 
                ) listeFrsGrpFrs WHERE 1=1";

        $arrayReq = [':idAo' => $idAo, ':idAo2' => $idAo];

        if (isset($post['filter_RS']) && $post['filter_RS']) {
            $req .= " and (UPPER(RS) like :rs1 or UPPER(ENSCOMM) like :ens1)";
            $arrayReq[':rs1'] = '%' . mb_strtoupper($post['filter_RS']) . '%';
            $arrayReq[':ens1'] = '%' . mb_strtoupper($post['filter_RS']) . '%';

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

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

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

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

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

    /**
     * @return array<mixed>
     */
    public function getInfoFrs(string $frs): ?array
    {
        $req = "select CODE, RS, ADR, VILLE, CODE_PAYS, CPOSTAL, VILLE1 
                from FOURNISS with (nolock)
                where CODE = :idFrs
                order by CODE";

        $arrayReq = [':idFrs' => $frs];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }


    /**
     * @return array<mixed>
     */
    public function getContactFrs(string $frs): ?array
    {
        $req = "select NOM ,TEL, FAX, TELPORT,EMAIL, CTC_CONSULT 
                from CONTFOUR with (nolock) 
                where CODE_FOU = :idFrs
                order by CODE_FOU, IND_FOU";

        $arrayReq = [':idFrs' => $frs];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
     */
    public function getInfoGrpFrs(string $frs): ?array
    {
        $req = "select CODE, RS, ADR, VILLE, CODE_PAYS, CPOSTAL, VILLE1 
                from GRPFOU with (nolock)
                where CODE = :idFrs
                order by CODE";

        $arrayReq = [':idFrs' => $frs];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
     */
    public function getContactGrpFrs(string $frs): ?array
    {
        $req = "SELECT NOM ,TEL, FAX, TELPORT,EMAIL, CTC_CONSULT 
                FROM CONTGF with (nolock) 
                WHERE CODE_GF = :idFrs
                ORDER BY CODE_GF, IND_FOU";

        $arrayReq = [':idFrs' => $frs];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
     */
    public function getMaxLigneAo(string $idAo): ?array
    {
        $req = "select max(NO_PO) MAX_PO from AO_LIGNE with (nolock) where NO_AO = :idAo";

        $arrayReq = [':idAo' => $idAo];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
     */
    public function getMaxOrderdocAttendu(string $idAO): ?array
    {
        $req = "select max(NUM_ORDRE) MAX_ORDER from AO_DOC_ATTENDUS with (nolock) where NO_AO = :idAo";

        $arrayReq = [':idAo' => $idAO];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
     */
    public function getMaxidAo(): ?array
    {
        $req = "select max(NO_AO) maxId from AO_ENTET with (nolock) ";

        $requete = $this->pdo->prepare($req);
        $requete->execute();

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param array <string> $post
     * @return array<mixed>
     */
    public function getListeLigneDa(array $post, ?string $listeSocConsult = null): ?array
    {

        $listSoc = (new Societe($this->pdo))->listRestrictedSocieteForUser();
        $sqlInSoc = ($listSoc !== null ?
            implode("','", array_column($listSoc, 'CODE_STE')) : '');

        $req = "SELECT * from (
                    select DA_LIGNE.*, DA_ENTET.NO_DEM,DA_ENTET.DEVISE, DEMAND.NOM NOM_DEMANDEUR, ACHETEUR.NOM NOM_ACHETEUR, ISNULL(t.DESIGNAT, SSFAMILL.DESIGNAT) AS DESIGNAT, FOURNISS.RS, MEMOS2.CONTMEMO DES_COMP 
                    from DA_LIGNE with (nolock)
                    left join DA_ENTET with (nolock)         on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
                    left join HASTUTIL DEMAND with(nolock)   on DA_ENTET.NO_DEM = DEMAND.CODE
                    left join HASTUTIL ACHETEUR with(nolock) on DA_LIGNE.ACHETEUR = ACHETEUR.CODE
                    left join SSFAMILL with (nolock)         on DA_LIGNE.SSFAM = SSFAMILL.CODE_SFA
                    LEFT OUTER JOIN TRAD_SFA t WITH(NOLOCK) ON t.CODE_SFA = SSFAMILL.CODE_SFA AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
                    left join FAMILLE with (nolock)          on SSFAMILL.CODE_FAM = FAMILLE.CODE_FAM
                    left join FOURNISS with (nolock)         on DA_LIGNE.CODE_FOU = FOURNISS.CODE
                    left join MEMOS MEMOS2 with(nolock) on MEMOS2.NODEMEMO = DA_LIGNE.NM_DCOMP
                    where DA_ENTET.FM_CODE  = '' and DA_LIGNE.TRAITEE  = 0x46 and DA_LIGNE.ACHATDIR = 0x46 and DA_LIGNE.CDEAUTO  = 0x46 and DA_LIGNE.SITE_STK = ''
                    and DA_LIGNE.VALIDEE  = 0x54 and DA_ENTET.REFUSEE  = 0x46 and DA_LIGNE.QTE_DEM > DA_LIGNE.DEJA_COMM and DA_ENTET.DTE_VAL != '00000000' and DA_ENTET.SIGNEE = 0x54
                    " . ($sqlInSoc ? " and DA_ENTET.CODE_STE in ('$sqlInSoc')" : "") . "
                    " . ($listeSocConsult ? " and DA_ENTET.CODE_STE in ('$listeSocConsult')" : "") . "
                ) listeDA";

        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

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

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

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

    /**
     * @return array<mixed>
     */
    public function getListeUserOxalinkActif(string $codeFou): ?array
    {
        return $this->fetchAssocAll(
            "SELECT CONTFOUR.NOM, CONTFOUR.EMAIL  
            FROM AO_CONTFOUR with (nolock)
            JOIN OXALINK_USER with (nolock) on OXALINK_USER.USER_ID = CONTFOUR.OXALINK_ID
            JOIN CONTFOUR with (nolock) on AO_CONTFOUR.CODE_FOUR = CONTFOUR.CODE_FOU and AO_CONTFOUR.TYPE_FG = 'F'
            WHERE AO_CONTFOUR.CODE_FOU = :codeFou 
                and CONTFOUR.OXALINK_ID != '' 
                and OXALINK_USER.STATUT = 'A'",
            [':codeFou' => $codeFou]
        );
    }

    /**
     * @return bool
     */
    public function isContribForConsultation(string $idAO): bool
    {
        $user = (new User())->getCodeUser();
        return count($this->fetchAssocAll(
            "SELECT aqt.NO_AO
            FROM AO_QST_TH aqt
                LEFT JOIN AO_CONTRIB_GRP acg WITH (NOLOCK) ON acg.NO_AO = aqt.NO_AO AND acg.GROUPE_CONTRIB = aqt.PROFNOTEUR2 AND aqt.PROFNOTEUR1 = 'G'
                LEFT JOIN AO_CONTRIB ac WITH (NOLOCK) ON ac.NO_AO = aqt.NO_AO AND aqt.PROFNOTEUR1 = ''
                LEFT JOIN HASTUTIL ha WITH (NOLOCK) ON ha.CODE = CASE 
                    WHEN aqt.PROFNOTEUR1 = 'G' 
                    THEN acg.CONTRIB 
                    WHEN aqt.PROFNOTEUR1 = 'N'
                    THEN aqt.PROFNOTEUR2
                    WHEN aqt.PROFNOTEUR1 = ''
                    THEN ac.CONTRIB 
                END
            WHERE aqt.ID_THEME LIKE 'I%' 
                AND aqt.NO_AO = :NO_AO
                AND CASE 
                    WHEN aqt.PROFNOTEUR1 = 'G' 
                    THEN acg.CONTRIB 
                    WHEN aqt.PROFNOTEUR1 = 'N'
                    THEN aqt.PROFNOTEUR2
                    WHEN aqt.PROFNOTEUR1 = ''
                    THEN ac.CONTRIB 
                END = :CONTRIB",
            [
                ':NO_AO' => $idAO,
                ':CONTRIB' => $user,
            ]
        )) > 0;
    }

    /**
     * Summary of listeAccordConfAValider
     * @return string[][]
     */
    public function totalAccordConfAValider(): array
    {
        $req = <<<SQL
            SELECT
                COUNT(AO_FOUR.NO_AO)
            FROM
                AO_FOUR
            LEFT JOIN AO_ENTET 
                ON AO_ENTET.NO_AO = AO_FOUR.NO_AO
            WHERE
                AO_ENTET.ACH = :CODE_USER
                AND AO_FOUR.ACC_CONF_VALID = 0x46
                AND AO_FOUR.FIC_ACC_CONF_SIGNE <> ''
            GROUP BY
                AO_FOUR.NO_AO
        SQL;

        $arrayReq = [':CODE_USER' => \Helpers\User\User::getUser()->getCodeUser()];

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