<?php

namespace Modules\Index\Repository;

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

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'];
    }

    /**
     * Summary of getCommandeWithTermOfPayments
     * @param ?string $codeUser
     * @return string[][]
     */
    public function getCommandeWithTermOfPayments(?string $codeUser = null)
    {
        $req = <<<SQL
            SELECT DISTINCT ca_ent.*
            FROM CA_ENTET ca_ent
            LEFT JOIN TP_ENT ON TP_ENT.NUM_CDE = ca_ent.NO_CDE AND TP_ENT.TYPEOBJ = 'CA_ENTET'
            LEFT JOIN TP_EVT ON TP_EVT.NUM_CDE = ca_ent.NO_CDE AND TP_EVT.TYPEOBJ = 'CA_ENTET'
            LEFT JOIN TYEVTSTP listeEvent ON listeEvent.CODE_EVT = TP_EVT.EVENEMENT
            WHERE TP_EVT.EVT_OK = 0x46
            AND listeEvent.HAPLUS = 0x46
            AND ca_ent.ETAT_CDE IN ('E','S')
        SQL;

        if ($codeUser) {
            $req .= " AND TP_EVT.RES_TP = :user ";
            $arrayReq = [
                ':user' => $codeUser
            ];
        }

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

    /**
     * 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();
    }

    public function rqLigneDAaTraiter():string
    {
        $param = Sql::paravrac($this->pdo, 'PARAMSIG', 'NOTCREATCDEDANONSIG');
        $req = "select COUNT(*) TOTAL from DA_LIGNE 
                left join DA_ENTET with (nolock) on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
                left join AO_LIGNE with (nolock) on AO_LIGNE.NO_DA = DA_LIGNE.NO_DA and AO_LIGNE.NO_PO_DA = DA_LIGNE.NO_PO
                LEFT join AO_ENTET with (nolock) on AO_ENTET.NO_AO = AO_LIGNE.NO_AO
                left join SOCIETE soc with (nolock) on soc.CODE_STE = DA_ENTET.CODE_STE
                left join TYPDOC with (nolock) on TYPDOC.TYPE = DA_LIGNE.TYP_DOC
                where  DA_LIGNE.CHIF_EXEC='E' and DA_LIGNE.VALIDEE=0x54 and DA_LIGNE.TRAITEE=0x46 and REFUSEE=0x46 /*and DA_LIGNE.RECEP_AUTO = 0x46*/
                and (DA_LIGNE.CODE_ART='' or not exists (select null from DCC_LIEN with(nolock) where TYPEOBJ='DA_ENTET' and cast(COD_ENR as int)=DA_LIGNE.NO_DA))
                and (DA_LIGNE.ACHATDIR = 0x46 or (DA_LIGNE.ACHATDIR = 0x54 and DA_ENTET.SIGNEE = 0x54))
                and DA_ENTET.CODE_STE=:societe and (AO_ENTET.VALCHXFOU != 0  or AO_ENTET.EVE_CHXFOU is null)
                and TYPDOC.NIV_CONF <= :nivConf
                ".Roles::getClauseSocieteOuvert('soc')."
                ".($param == 'O'? " and DA_ENTET.SIGNEE = 0x54  ":'');

        $infoUser = (new Hastutil($this->pdo))->find($_SESSION["Session_Login"]);
        $arrayReq[':nivConf'] = $infoUser['NIV_CONF'];
        $arrayReq[':societe']= (new User())->getSociete()->code;

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

        return $requete->fetch()['TOTAL']??'';
    }

    /**
     * @return array<mixed>
     */
    public function getFactureStat(string $userCode) :array
    {
        return $this->fetchAssocOne(
            "WITH FactLitige as (
                SELECT *
                FROM FAC_ENT with (nolock) 
                OUTER APPLY (
                    SELECT TOP 1 FOURNINC.*
                    FROM FOURNINC
                    JOIN INCIDENT with (nolock) on INCIDENT.COD_INC = FOURNINC.CODE
                    WHERE FOURNINC.NO_FACT = FAC_ENT.NUM_FACT 
                        AND INCIDENT.CODE_CAT = 'LITFACT'
                        AND FOURNINC.INC_FERME = 0x46
                        AND FOURNINC.INCF_USER = :respLitige 
                    ORDER BY FOURNINC.DTE_CREAT DESC
                ) as Litige
                WHERE Litige.FOU IS NOT NULL AND FAC_ENT.LITIGE = 'T'
            )
            SELECT 
            (
                SELECT COUNT(*) 
                FROM FactLitige with (nolock)
            ) as countLitige,
            (
                SELECT COUNT(*)
                FROM FactLitige
                JOIN FA_EVENT with(nolock)
                    ON FA_EVENT.NUM_FACT = FactLitige.NUM_FACT
                    AND FA_EVENT.CODE_EVE = 'MRL'-- Message Reponse Litige
                GROUP BY FactLitige.NUM_FACT
            ) as countLitigeResponse",
            [':respLitige' => $userCode]
        );
    }

    /**
     * @return array<mixed>
     */
    public function getCountSuppliersByCategories() :array
    {
        return $this->fetchAssocAll(
            "SELECT p.LIB01PARV, p.CODEPARV, r.LIBELLE, COUNT(f.CODE) AS COUNT
            FROM PARAVRAC p WITH (NOLOCK)
                LEFT JOIN FOURNISS f WITH (NOLOCK) ON f.CODE_REGR = p.LIB01PARV
                LEFT JOIN REGROUP r WITH (NOLOCK) ON r.CODE_REG = p.LIB01PARV
            WHERE TYPEPARV = 'PARAMTDB' AND CODEPARV LIKE 'CAT_FOUR_%'
            GROUP BY p.LIB01PARV, r.LIBELLE,p.CODEPARV
            ORDER BY p.CODEPARV",
            []
        );
    }
}
