<?php

namespace Modules\Index\Repository;

use Entity\Model\Paravrac;
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 UTILCREAT.NOM, UTILCREAT.CODE, SOCIETE.RAIS_SOC, 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);
    }
    
    /**
     * @return string
     */
    public function nbrDocTotal(bool $critique = false, bool $surveillance = false, bool  $partenaire = false)
    {
        if ($critique === true) {
            $where = " and CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with(nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = 0x54)";
            if ($surveillance === true) {
                $where .= " and A_SURVEILLER=0x54 ";
            }
        } else {
            $where = '';
        }

        $clausePartenaire = '';
        if ($partenaire) {
            $clausePartenaire = " AND (
                SELECT COUNT(*) NB_PARTENAIRE from PARAMDICO with(nolock)
                where TYPEDICO='INVIT_PARTFOU' and ID3 = FOURNISS.CODE) != 0 ";
        }

        $clauseLiensSte = $this->getClauseLiensSte();

        $req = "SELECT COUNT(*) NB_DOC  from
            (
                select * from ( select distinct f.CODE,d.CODE_DOC from FOURNISS f with(nolock)
                join LST_DOC_DEM d with(nolock) on d.CODE_TABLE='COUNTRY' and d.CODE_ENREG=f.CODE_PAYS
                left join LST_DOCS with (nolock) on LST_DOCS.CODE_DOC = d.CODE_DOC $where
                union
                select distinct sf.CODE_FOUR, doc.CODE_DOC from SSFAMFOU sf with(nolock)
                join D_C_FAM_SSFAM dc with(nolock) on dc.FAM_SSFAM='S' and dc.CODE=sf.CODE_SFAM
                join LST_DOC_DEM doc with(nolock) on doc.CODE_TABLE='DOM_COMP' and doc.CODE_ENREG=dc.CODE_DC ) as FOURNISS
                WHERE 1=1
                $clausePartenaire$clauseLiensSte
            ) requeteTotal";
        
        return $this->fetchAssocOne($req, [])['NB_DOC']??'0';
    }

    /**
     * @return string
     */
    public function nbrDocValide(bool $critique = false, bool $surveillance = false, bool $partenaire = false)
    {
        if ($critique === true) {
            $where = " CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with(nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = 0x54) and";
            if ($surveillance === true) {
                $where .= " A_SURVEILLER=0x54 and ";
            }
        } else {
            $where = '';
        }

        $clausePartenaire = '';
        if ($partenaire) {
            $clausePartenaire = " and (select COUNT(*) NB_PARTENAIRE from PARAMDICO with(nolock) where TYPEDICO='INVIT_PARTFOU' and ID3 = FOURNISS.CODE) != 0 ";
        }

        $clauseLiensSte = $this->getClauseLiensSte();

        $req = "SELECT SUM(nbrDoc) NB_DOC from (
                SELECT COD_ENR, count(*) as nbrDoc  
                FROM DOC_ATT with(nolock) 
                JOIN FOURNISS with(nolock) on FOURNISS.CODE = DOC_ATT.COD_ENR and (DT_VALID>='".date('Ymd')."' or DT_VALID='00000000')
                WHERE NO_DA = 'FOURNISS' 
                    AND DOC_ATT.TITRE in (
                            select DISTINCT(LST_DOCS.TITRE_DOC) 
                            from LST_DOC_DEM with(nolock)
                            left join LST_DOCS with(nolock) on LST_DOCS.CODE_DOC = LST_DOC_DEM.CODE_DOC
                            where $where ((CODE_TABLE='COUNTRY' AND CODE_ENREG=FOURNISS.CODE_PAYS) 
                            or ( CODE_TABLE='DOM_COMP'  and CODE_ENREG in 
                                    (
                                        select DISTINCT(CODE_DC) 
                                        from SSFAMFOU with(nolock) 
                                        left join D_C_FAM_SSFAM with(nolock)  on D_C_FAM_SSFAM.CODE = SSFAMFOU.CODE_SFAM 
                                        where SSFAMFOU.CODE_FOUR = FOURNISS.CODE and CODE_DC is not null 
                                    )
                                ) 
                    ) 
                    $clausePartenaire
                    $clauseLiensSte
            ) 
            GROUP BY COD_ENR ) total";
        
        return $this->fetchAssocOne($req, [])['NB_DOC'] ?? '0';
    }

    private function getClauseLiensSte() :string
    {
        if (! Sql::toBool((new Paravrac($this->pdo))->getValue('PARAMFOU', 'LIEN_FOU_STE_ACTIF'))
            || $_SESSION['VoirTousFournisseurs']
        ) {
            return '';
        }
        return " AND exists (
            SELECT * from LIENFOUGRPSTE lien with (nolock) 
            WHERE lien.TYPE_FG = 'F' 
                and lien.CODE_FOUGR = FOURNISS.CODE 
                and lien.CODE_STE = '".$_SESSION["Session_SocUsr"]."')";
    }

    /**
     * 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 (!$_SESSION['CDE_AuthImprim']) {
            $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();
    }
}
