<?php

namespace Modules\Index\Repository;

use Entity\Model\Paravrac;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\Trad\Trad;
use Helpers\QueryBuilder\AbsRepository;

class TableauDeBord extends AbsRepository
{
    /**
     * @return array<mixed>
     */
    /**
     * rqGetListDemToCareByAdmin
     *
     * @param  string $profil
     * @param  array<mixed> $mode
     * @param  string $login
     * @param  int $isMulti
     * @param  string $societe
     * @param  bool $activecount
     * @return array<mixed>
     */
    public function rqGetListDemToCareByAdmin(string $profil, array $mode, string $login, int $isMulti, string $societe = '', bool $activecount = false)
    {
        $modeBackup = $_SESSION['MODE_BACKUP']??false;
        $droitAccesBackUpResp = $_SESSION['TYPE_BACKUP_RESP']??false;

        $arrayReq = [];

        if (!$activecount) {
            $req = 'SELECT *, DILISPEC.LIB01DICO respHierarchique ';
            $endReq = ' order by UTILCREAT.NOM';
        } else {
            $req = 'SELECT CREATMOD, count(CODE) as nbr ';
            $endReq = ' GROUP BY CREATMOD';
        }

        $req .= " FROM UTILCREAT with (nolock)
                inner join SOCIETE with (nolock) on UTILCREAT.CODE_STE = SOCIETE.CODE_STE";
        if (!$activecount) {
            $req .= " left join DILISPEC with (nolock) on UTILCREAT.CODE = DILISPEC.CODEDICO";
        }

        $req .=  " WHERE ";
        
        $i=0;

        foreach ($mode as $array) {
            if ($i != 0) {
                $req .=  " OR ";
            }
            $req .=  " CREATMOD = '$array' ";
            $i++;
        }

        if (($modeBackup && ($_SESSION['TYPE_BACKUP_RESP']??false))
        || ($modeBackup && $_SESSION['SuperieurHierarchique'])) {
            $req .= " AND UTILCREAT.CODE not in (select CODEDICO from DILISPEC with (nolock) where TYPEDICO  = 'WRKFL_CMUS')";
        }

        if ($isMulti == 0) {
            $req .= " and ( UTILCREAT.CODE_STE=:societe or  UTILCREAT.CODE_STE in (select COD_STE from  SOC_USR with (nolock) where COD_USR = :login))";

            $arrayReq[':login'] = $societe;
            $arrayReq[':societe'] = $societe;
        }

        if ($profil == 'locale') {
            $req .= " and UTILCREAT.CTR_ADR in 
					(
						select distinct ctr.CODE_CTR from CENTRE as ctr with(nolock), SOCIETE  as soc with(nolock)
						where ctr.EMISSION=0x54 and ctr.CODE_STE=soc.CODE_STE
						and (ctr.CODE_CTR in (select COD_CTR from ADML_CTR with(nolock) where COD_USR=:user1)
						or
						soc.CODE_STE in (select SOC_COD from ADML_SOC with(nolock) where COD_USR=:user2 and TTCTR=0x54)
						or
						soc.GROUPE in (select COD_GRPS from ADMLGRSO with(nolock) where COD_USR=:user3 and TTSOC=0x54))
                        
                        and ctr.CODE_STE=:societe2
                    )";

            
            $arrayReq[':user1'] = $login;
            $arrayReq[':user2'] = $login;
            $arrayReq[':user3'] = $login;
            $arrayReq[':societe2'] = $societe;
        }
        
        return $this->fetchAssocAll($req.$endReq, $arrayReq);
    }

    /**
     * @param bool $valide
     * @return array<mixed>
     */
    public function getStatutDocumentFrs(bool $valide = false)
    {

        $clauseSte = '';
        $arrayReq = [];
        if (Sql::toBool((new Paravrac($this->pdo))->getValue('PARAMFOU', 'LIEN_FOU_STE_ACTIF')) || !roles::droitsUtilisateur($this->pdo, 'HA_FOU_TST')) {
            $clauseSte = "and (
                exists (select * from LIENFOUGRPSTE lien with (nolock) where lien.TYPE_FG = 'F' and lien.CODE_FOUGR = f.CODE and lien.CODE_STE = :codeSte)
                or not exists (select * from LIENFOUGRPSTE lien with (nolock) where lien.TYPE_FG = 'F' and lien.CODE_FOUGR = f.CODE)
            ) ";

            $arrayReq[':codeSte'] = $_SESSION["Session_SocUsr"];
        }

        $req = "with totalDoc as (
                    select distinct(f.CODE),LST_DOCS.*, DOC_ATT.TITRE from FOURNISS f with (nolock)
                    left join SSFAMFOU sf with (nolock) on sf.CODE_FOUR = f.CODE
                    left join D_C_FAM_SSFAM dc with(nolock) on dc.FAM_SSFAM='S' and dc.CODE=sf.CODE_SFAM
                    join LST_DOC_DEM d with(nolock) on (d.CODE_TABLE='COUNTRY' and d.CODE_ENREG=f.CODE_PAYS) or (d.CODE_TABLE='DOM_COMP' and d.CODE_ENREG=dc.CODE_DC)
                    left join LST_DOCS with (nolock) on LST_DOCS.CODE_DOC = d.CODE_DOC
                    left join DOC_ATT with (nolock) on DOC_ATT.NO_DA = 'FOURNISS' 
                                    and DOC_ATT.CODE_CAT = LST_DOCS.CODE_CATEGORIE 
                                    and DOC_ATT.COD_ENR = sf.CODE_FOUR
                                    and DOC_ATT.TITRE = LST_DOCS.TITRE_DOC
                    where 1=1 ".($valide? " and (DT_VALID>='".date('Ymd')."' or DT_VALID='00000000') ":"")." $clauseSte
                ), totalCritique as (
                    select * from totalDoc where totalDoc.CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with(nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = 0x54)
                ), totalSurveillance as (
                    select * from totalDoc where A_SURVEILLER=0x54
                ), totalDocPart as (
                    select * from totalDoc where (
                        SELECT COUNT(*) NB_PARTENAIRE from PARAMDICO with(nolock)
                        where TYPEDICO='INVIT_PARTFOU' and ID3 = totalDoc.CODE) != 0
                ), totalCritiquePart as (
                    select * from totalCritique where (
                        SELECT COUNT(*) NB_PARTENAIRE from PARAMDICO with(nolock)
                        where TYPEDICO='INVIT_PARTFOU' and ID3 = totalCritique.CODE) != 0
                ), totalSurveillancePart as (
                    select * from totalSurveillance where (
                        SELECT COUNT(*) NB_PARTENAIRE from PARAMDICO with(nolock)
                        where TYPEDICO='INVIT_PARTFOU' and ID3 = totalSurveillance.CODE) != 0
                )
                select
                (select COUNT(*) from totalDoc) totalDoc,
                (select COUNT(*) from totalCritique) totalCritique,
                (select COUNT(*) from totalSurveillance) totalSurveillance,
                (select COUNT(*) from totalDocPart) totalDocPart,
                (select COUNT(*) from totalCritiquePart) totalCritiquePart,
                (select COUNT(*) from totalSurveillancePart) totalSurveillancePart";

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

    /**
     * rqGetCdeToPrint
     * @return string
     */
    public function getCdeToPrint(string $demandeur)
    {
        
        $req = "select count(*) as TOTAL
                from CA_ENTET with (nolock) 	 
                LEFT OUTER JOIN TYPDOC with (nolock) on TYPDOC.TYPE = CA_ENTET.TYP_DOC
                where 
                    (
                        CA_ENTET.DEMANDEUR 		= '".$demandeur."'
                        OR (CA_ENTET.AVENANT<>'' and '".$demandeur."' = (select UTIL from CA_EVENT with (nolock) where CA_EVENT.NO_CDE=CA_ENTET.NO_CDE and CA_EVENT.NUM_ORD = CA_ENTET.EVE_VAL))
                    )   
                and (CA_ENTET.ETAT_CDE		='V'	or ( CA_ENTET.ETAT_CDE 		='Z' and CA_ENTET.EVE_IMP 		=0))
                and CA_ENTET.SIGNEE			= 0x54 
                and CA_ENTET.REFUSEE		= 0x46";
                
        /** Données déjà maitrisé donc pas besoin de le traiter : Performance */
        $arrayReq = [
            // ':demandeur1' => $demandeur,
            // ':demandeur2' => $demandeur,
        ];

        if (!Roles::droitsUtilisateur($this->pdo, 'HA_CA_IMP')) {
            $paravrac = Sql::paravrac($this->pdo, 'OXAPROC', 'TYPDOCUMENT');
            $req .= " and (TYP_DOC = :typeDoc or TYDO_IND_IMPR_PAR_DEM = 0x54) ";
            $arrayReq[':typeDoc'] = $paravrac;
        }


        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch()['TOTAL'];
    }

    /**
     * rqGetCdeSstraitance
     * @return string
     */
    public function getCdeSstraitance(string $typeSsTraitance)
    {
        $req = " select count(*) as TOTAL from CA_ENTET with(nolock) 
                inner join TYPEACHAT tac with(nolock) on tac.TYP_ACH = CA_ENTET.COD_TAC 
                where tac.STRAIT = 0x54 and ACH = :user";

        switch ($typeSsTraitance) {
            case 'SANS_CONTRAT':
                $req .= ' and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT = 0)';
                break;
            case 'CONTRAT_NON_RECEP':
                $req .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT <> 0 and CA_ENTET.DTE_RECCONT = '00000000')";
                break;
        }

        $arrayReq = [
            ':user' => $_SESSION['Session_Login'],
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch()['TOTAL'];
    }

    /**
     * @return string
     */
    public function searchCampNota()
    {
        $req = 'SELECT count(FOURNISS.CODE) TOTAL from FOURNISS with (nolock) 
                where exists (  select null from CAMP_LST_FOUR with (nolock) 
                                where CAMP_LST_FOUR.TERMINE = 0x46 
                                AND   CAMP_LST_FOUR.CODE_FOU = FOURNISS.CODE 
                                and exists (    select null from CAMP_ENT with (nolock) 
                                                where CAMP_ENT.ID_CAMP = CAMP_LST_FOUR.ID_CAMP
                                            ) 
                            AND exists (    select null from CAMP_REP_QST with(nolock)
                                            where CAMP_REP_QST.COD_NOTEUR = :user 
                                            and   CAMP_REP_QST.CODE_FOU = CAMP_LST_FOUR.CODE_FOU 
                                            and   CAMP_REP_QST.ID_CAMP = CAMP_LST_FOUR.ID_CAMP 
                                        ) 
                            ) ';
        
        $arrayReq = [
            ':user' => $_SESSION['Session_Login'],
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch()['TOTAL'];
    }

    /**
     * @return array<mixed>
     */
    public function getCanevasDocRattaches()
    {
        $req = "select CODE_CATEGORIE as CATEGORIE, NUM_ORDRE as NO_ORDRE, TITRE_DOC as LIBELLE,
                    CASE A_SURVEILLER
                        WHEN 0x54 THEN  'O'
                        ELSE   'N'
                    END as SURVEILLANCE,
                    (select COUNT(*) DOC_PARTNER from PARAMDICO with (nolock) where TYPEDICO='DOCATTFNR_LSTDOCS_PARTNER' and ID2= LST_DOCS.TITRE_DOC) DOC_PARTNER
                from LST_DOCS with (nolock)";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
        
        return $requete->fetch();
    }

    /**
     * @return array<mixed>
     */
    public function rqPartenaireActifDocument()
    {
        $NO_CLIENT = $_SESSION['Session_NumClient'];
        $req = "select * from PARAMDICO with (nolock) where TYPEDICO = 'CLIENT".$NO_CLIENT."_PARTFOU'  and ALPHA1 = 'O'";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
        
        return $requete->fetch();
    }
}
