<?php

namespace Modules\Consultation\Repository;

use Entity\Model\AoFour;
use Helpers\QueryBuilder\AbsRepository;

class QuestionReponseRepo extends AbsRepository
{
    /**
     * @param string $idAo
     * @param string $idFrs
     * @return array<mixed>
     */
    public function getListeQuestion(string $idAo, string $idFrs, string $typeFG)
    {
        $req = "select 
                    AO_FOUR_QR.NO_AO,
                    AO_FOUR_QR.CODE_FOU,
                    AO_FOUR_QR.TYPE_FG,
                    AO_FOUR_QR.ID_QR,
                    AO_FOUR_QR.DATE_CREAT,
                    AO_FOUR_QR.CREATEUR,
                    AO_FOUR_QR.AUTEUR_OXALINK,

                    AO_FOUR_QR.ORIGIN_TYPE_FG,
                    AO_FOUR_QR.ORIGIN_CODE_FOU,
                    AO_FOUR_QR.ORIGIN_ID_QR,

                    ISNULL(origin.AUTEUR_OXALINK, AO_FOUR_QR.AUTEUR_OXALINK) AUTEUR_OXALINK,
                    ISNULL(origin.QUEST, AO_FOUR_QR.QUEST) QUEST,
                    ISNULL(origin.REP, AO_FOUR_QR.REP) REP,
                    AO_FOUR_QR.DATE_ENV_REP,
                    AO_FOUR_QR.[PUBLIC],
                    ISNULL(origin.QUEST_PUBLIC, AO_FOUR_QR.QUEST_PUBLIC) QUEST_PUBLIC,
                    (
                        select COUNT(*) from DOC_ATT with (nolock) 
                        where NO_DA = 'AO_FOUR_QR_Q' and COD_ENR = CONCAT(	AO_FOUR_QR.NO_AO, 
                                                                            ISNULL(origin.TYPE_FG, AO_FOUR_QR.TYPE_FG),
                                                                            ISNULL(origin.CODE_FOU, AO_FOUR_QR.CODE_FOU), 
                                                                            ISNULL(origin.ID_QR, AO_FOUR_QR.ID_QR)
                                                                        )
                    ) DOC_QUEST,
                    (
                        select COUNT(*) from DOC_ATT with (nolock) 
                        where NO_DA = 'AO_FOUR_QR_R' and COD_ENR = CONCAT(	AO_FOUR_QR.NO_AO, 
                                                                            ISNULL(origin.TYPE_FG, AO_FOUR_QR.TYPE_FG),
                                                                            ISNULL(origin.CODE_FOU, AO_FOUR_QR.CODE_FOU), 
                                                                            ISNULL(origin.ID_QR, AO_FOUR_QR.ID_QR)
                                                                        )
                    ) DOC_REP
                from AO_FOUR_QR with (nolock)
                left join AO_FOUR_QR origin with (nolock) on AO_FOUR_QR.ORIGIN_TYPE_FG = origin.TYPE_FG 
                                                        and AO_FOUR_QR.ORIGIN_CODE_FOU = origin.CODE_FOU 
                                                        and AO_FOUR_QR.ORIGIN_ID_QR = origin.ID_QR
                                                        and AO_FOUR_QR.NO_AO = origin.NO_AO
                where (AO_FOUR_QR.NO_AO = :noAo and AO_FOUR_QR.CODE_FOU=:idFrs and AO_FOUR_QR.TYPE_FG = :typeFG)
                and (AO_FOUR_QR.AUTEUR_OXALINK = '' or (AO_FOUR_QR.AUTEUR_OXALINK <> '' and AO_FOUR_QR.DATE_ENV_QUEST != '00000000'))";

        $arrayReq = [
            ':noAo'         => $idAo,
            ':idFrs'        => $idFrs,
            ':typeFG'       => $typeFG,
        ];

        $req .= " order by DATE_CREAT, ID_QR ";

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

    /**
     * @return array<mixed>
     */
    public function listeFrsWithoutQuestion(string $idAo, string $typeFG, string $frsOrigin, string $idQst)
    {
        $req = "select * from AO_FOUR with (nolock)
                where NO_AO =  :noAo  and CONCAT(CODE_FOU, TYPE_FG) != :codeFrsFG
                and not exists (
                    select * from AO_FOUR_QR with (nolock) 
                    where AO_FOUR_QR.NO_AO = AO_FOUR.NO_AO and AO_FOUR_QR.CODE_FOU = AO_FOUR.CODE_FOU and AO_FOUR_QR.TYPE_FG = AO_FOUR.TYPE_FG and AO_FOUR_QR.ORIGIN_ID_QR = :idQst
                )";

        $arrayReq = [
            ':noAo'         => $idAo,
            ':codeFrsFG'    => $frsOrigin.$typeFG,
            ':idQst'        => $idQst,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param string $idAo
     * @param string $idFrs
     * @param string $idQst
     * @return array<mixed>
     */
    public function getInfoFrsPourEnvoie(string $idAo, string $idFrs, string $typeFG, string $idQst)
    {
        $req = "select AO_FOUR_QR.*, AO_ENTET.OBJET_AO from AO_FOUR_QR with (nolock)
                left join AO_ENTET with (nolock) on AO_ENTET.NO_AO = AO_FOUR_QR.NO_AO
                where AO_FOUR_QR.NO_AO = :noAo and AO_FOUR_QR.CODE_FOU = :idFrs and AO_FOUR_QR.TYPE_FG = :typeFG and ID_QR=:idQst ";

        $arrayReq = [
            ':noAo'         => $idAo,
            ':idFrs'        => $idFrs,
            ':idQst'        => $idQst,
            ':typeFG'       => $typeFG,
        ];

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

    /**
     * Summary of listeReponseSend
     * @param string $idAo
     * @param string $idFrs
     * @param string $typeFG
     * @param array<mixed> $listeSelected
     * @param bool $all
     * @return array<mixed>
     */
    public function listeReponseSend(string $idAo, string $idFrs, string $typeFG, array $listeSelected = [], bool $all = false)
    {
        $arrayReq = [
            ':noAo'         => $idAo,
            ':idFrs'        => $idFrs,
            ':typeFG'        => $typeFG,
        ];

        $req = "select 
                    AO_FOUR_QR.ID_QR,
                    case when AO_FOUR_QR.QUEST = '' then ISNULL(origin.QUEST_PUBLIC, AO_FOUR_QR.QUEST_PUBLIC) else ISNULL(origin.QUEST, AO_FOUR_QR.QUEST) end QUEST,
                    ISNULL(origin.REP, AO_FOUR_QR.REP) REP
                from AO_FOUR_QR with (nolock)
                left join AO_FOUR_QR origin with (nolock) on AO_FOUR_QR.ORIGIN_TYPE_FG = origin.TYPE_FG 
                                                        and AO_FOUR_QR.ORIGIN_CODE_FOU = origin.CODE_FOU 
                                                        and AO_FOUR_QR.ORIGIN_ID_QR = origin.ID_QR
                                                        and AO_FOUR_QR.NO_AO = origin.NO_AO
                where (AO_FOUR_QR.NO_AO = :noAo and AO_FOUR_QR.CODE_FOU=:idFrs and AO_FOUR_QR.TYPE_FG = :typeFG)";

        if ($all) {
            $req .= "  and AO_FOUR_QR.DATE_ENV_REP = '00000000' and ISNULL(origin.REP, AO_FOUR_QR.REP) != '' ";
        } else {
            $req .= " and ".$this->makeWhereIn('AO_FOUR_QR.ID_QR', $listeSelected, $arrayReq);
        }

        $req .= " order by AO_FOUR_QR.DATE_CREAT, ID_QR ";
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param string $idAo
     * @param string $idFrs
     * @param string $typeFG
     * @param array<mixed> $listeSelected
     */
    public function updateSending(string $idAo, string $idFrs, string $typeFG, $listeSelected = [], bool $all = false):void
    {
        $arrayReq = [
            ':noAo'         => $idAo,
            ':idFrs'        => $idFrs,
            ':typeFG'       => $typeFG,
            ':date'         => date('Ymd')
        ];

        $req = "update AO_FOUR_QR set DATE_ENV_REP = :date
                where (AO_FOUR_QR.NO_AO = :noAo and AO_FOUR_QR.CODE_FOU=:idFrs and AO_FOUR_QR.TYPE_FG = :typeFG)";

        if ($all) {
            $req .= "  and AO_FOUR_QR.DATE_ENV_REP = '00000000' ";
        } else {
            $req .= "and ".$this->makeWhereIn('AO_FOUR_QR.ID_QR', $listeSelected, $arrayReq);
        }

        $this->execute($req, $arrayReq);
    }

    /**
     * @param string $idAo
     * @param string $idFrs
     * @param string $typeFG
     * @return array<mixed>
     */
    public function getContactFrs(string $idAo, string $idFrs, string $typeFG)
    {
        $req = "select top 1 EMAILCONT from (
                    select EMAILCONT from AO_FOUR where CODE_FOU = :codeFrs and TYPE_FG = :typeFG and NO_AO = :idAO and EMAILCONT != ''
                    union
                    select EMAILCONT from AO_CONTFOUR where CODE_FOUR = :codeFrs2 and TYPE_FG =:typeFG2  and NO_AO = :idAO2 and EMAILCONT != ''
                ) emailFrs";

        $arrayReq  = [
            ':idAO'     => $idAo,
            ':typeFG'   => $typeFG,
            ':codeFrs'  => $idFrs,

            ':idAO2'     => $idAo,
            ':typeFG2'   => $typeFG,
            ':codeFrs2'  => $idFrs,
        ];

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

    /**
     * @return array<mixed>
     */
    public function listeFournisseur(string $idAO)
    {
        $req = "select 
                    AO_FOUR.NO_AO, 
                    AO_FOUR.TYPE_FG, 
                    AO_FOUR.CODE_FOU,
                    ISNULL(FOURNISS.RS, GRPFOU.RS) RS,
                    (
                        select 
                            COUNT(*) 
                        from AO_FOUR_QR with (nolock) 
                        where AO_FOUR_QR.NO_AO = AO_FOUR.NO_AO and AO_FOUR_QR.TYPE_FG = AO_FOUR.TYPE_FG and AO_FOUR_QR.CODE_FOU = AO_FOUR.CODE_FOU
                        and (AO_FOUR_QR.AUTEUR_OXALINK = '' or (AO_FOUR_QR.AUTEUR_OXALINK <> '' and AO_FOUR_QR.DATE_ENV_QUEST != '00000000'))
                    ) totalQR,
                    (
                        select 
                            COUNT(*) 
                        from AO_FOUR_QR with (nolock) 
                        left join AO_FOUR_QR origin with (nolock) on AO_FOUR_QR.ORIGIN_TYPE_FG = origin.TYPE_FG 
                                                                and AO_FOUR_QR.ORIGIN_CODE_FOU = origin.CODE_FOU 
                                                                and AO_FOUR_QR.ORIGIN_ID_QR = origin.ID_QR
                                                                and AO_FOUR_QR.NO_AO = origin.NO_AO
                        where AO_FOUR_QR.NO_AO = AO_FOUR.NO_AO and AO_FOUR_QR.TYPE_FG = AO_FOUR.TYPE_FG and AO_FOUR_QR.CODE_FOU = AO_FOUR.CODE_FOU
                        and ISNULL(origin.REP, AO_FOUR_QR.REP) <> ''
                    ) QRWithRep
                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
                where NO_AO = :noAo";

        $arrayReq = [
            ':noAo'         => $idAO,
        ];

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

    /**
     * @param string $idAO
     * @return array<mixed>
     */
    public function getAllDocumentAOQR(string $idAO)
    {
        $req = "select * from DOC_ATT where (NO_DA= 'AO_FOUR_QR_Q' or  NO_DA= 'AO_FOUR_QR_R') and (COD_ENR like '{$idAO}F%' or COD_ENR like '{$idAO}G%')";

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

    public function deleteAllDocumentAOQR(string $idAO):void
    {
        $req = "DELETE from DOC_ATT where (NO_DA= 'AO_FOUR_QR_Q' or  NO_DA= 'AO_FOUR_QR_R') and (COD_ENR like '{$idAO}F%' or COD_ENR like '{$idAO}G%')";

        $this->execute($req, []);
    }
}
