<?php

namespace Modules\ProjetAchat\Repository;

use Helpers\QueryBuilder\AbsRepository;
use Modules\GlobalApplication\Outil\UOService;

class Modele extends AbsRepository
{
    /**
     * @param array<string> $post
     * @param array<string> $droit
     * @return array<mixed>[]
    */
    public function listeModelesProjetAchat(array $post, array $droit = []): array
    {
        $req = "select DOA.*, createur.CODE_UO CREAT_UO from (
				select DOA.*, proprio.NOM, proprio.NOM as ProprioName, proprio.CODE_UO PROPRIO_UO, prescri.CODE_UO PRESCRI_UO,
                (select COUNT(*) from DOA_FAVORIS with(nolock) where CODE_USER='".$_SESSION["Session_Login"]."' and ID_MODELE=DOA.ID) FAVORIS,
                doaPere.LIB LIB_PERE, doaPere.REF REF_PERE,
				CASE
					 WHEN CODE_EVT is null THEN DOA.PROPRIETAIRE
					 ELSE CODE_USER
				END CREATEUR
                from DOA with (nolock)
                left join HASTUTIL proprio with (nolock) on proprio.CODE = DOA.PROPRIETAIRE
                left join HASTUTIL prescri with (nolock) on prescri.CODE = DOA.PROPRIETAIRE
                left join DOA_EVT with (nolock) on DOA_EVT.ID_DOA = DOA.ID and CODE_EVT = 'CMO'
                left join DOA doaPere with (nolock) on doaPere.ID = DOA.ID_PERE
                where DOA.MODELE=0x54 ) DOA 
				left join HASTUTIL createur with (nolock) on createur.CODE = DOA.CREATEUR
				WHERE 1=1 ";
        
        if (!$droit['DOA_MOD_TT']) {
            $req .= " and ((CREATEUR = '".$_SESSION["Session_Login"]."' or PROPRIETAIRE = '".$_SESSION["Session_Login"]."' or PRESCRIPTEUR = '".$_SESSION["Session_Login"]."')";

            if ($droit['DOA_VIA_CT']) {
                $req .= " or (  CTR_EMETT in (select CTR_ADR from HASTUTIL with (nolock) where CODE = '".$_SESSION["Session_Login"]."'
                                UNION
                                select CTREMET from SOC_USR  with (nolock) where COD_USR = '".$_SESSION["Session_Login"]."' AND COD_STE = '".$_SESSION["Session_SocUsr"]."'))";
            }
            
            if ($droit['DOA_VIA_UO']) {
                $arrayUO = UOService::listeUoUser($this->pdo, $_SESSION["Session_Login"], $_SESSION["Session_SocUsr"]);
                $listeUO = implode("','", $arrayUO);
                
                $req .= " or (PROPRIO_UO in ($listeUO) or PRESCRI_UO in ($listeUO) or CREAT_UO in ($listeUO) or DOA.ID in (select ID_DOA from DOA_ELEM with (nolock) where TYPE_ELEM = 'UO' and ID_ELEM in ($listeUO))";
            }
            
            $req .= ")";
        }
        
        $arrayReq = [];

        
        foreach ($post as $key => $val) {
            $i = 1;
            if (strpos($key, 'DOAELEM_') === 0 && substr($key, -4) != 'Cond') {
                if ($post[$key.'Cond'] == 'egal') {
                    $req .=" and ID in (select ID_DOA from DOA_ELEM with (nolock) where TYPE_ELEM = :typeElem$i and ID_ELEM = :idElem$i)";
                } else {
                    $req .=" and ID not in (select ID_DOA from DOA_ELEM with (nolock) where TYPE_ELEM = :typeElem$i and ID_ELEM = :idElem$i)";
                }
                
                $arrayReq[":typeElem$i"] =  substr($key, 8);
                $arrayReq[":idElem$i"]   = $val;
                $i++;
            }
        }
        
        if (isset($post['SOCIETE']) and $post['SOCIETE']) {
            $req .= " and ID IN (select DISTINCT(ID_DOA) from DOA_SOC with (nolock) where TYPE_GS = 'S' and CODE = :societe)";
            $arrayReq[':societe'] = $post['SOCIETE'];
        }
        
        if (isset($post['GR_SOCIETE']) and $post['GR_SOCIETE']) {
            $req .= "   and ( ID IN (select DISTINCT(ID_DOA) from DOA_SOC with (nolock) where TYPE_GS = 'G' and CODE = :GRSociete) 
                        or ID IN (select DISTINCT(ID_DOA) from DOA_SOC with (nolock) where TYPE_GS = 'S' and CODE in 
                            (
                                select SIEGE from GROUPSOC with(nolock) where CODE_GRP = :GRSociete2
                                    union 
                                select CODE_STE from SOCIETE with (nolock) where GROUPE = :GRSociete3)
                            )
                        ) ";
            $arrayReq[':GRSociete'] = $post['GR_SOCIETE'];
            $arrayReq[':GRSociete2'] = $post['GR_SOCIETE'];
            $arrayReq[':GRSociete3'] = $post['GR_SOCIETE'];
        }
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1, 1);
        
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($arrayReq);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0]." order by FAVORIS DESC ".$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        } else {
            $req .=" order by FAVORIS DESC";
        }

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    public function addFavori(string $code, string $user): void
    {
        $req = "INSERT INTO DOA_FAVORIS VALUES (:value1, :value2)";
        
        $arrayReq = [':value1' => $user, ':value2' => $code];
        $fav = $this->pdo->prepare($req);
        $fav->execute($arrayReq);
    }
    
    public function rmvFavori(string $code, string $user): void
    {
        $req = "DELETE FROM DOA_FAVORIS WHERE CODE_USER=:value1 and ID_MODELE=:value2";
        
        $arrayReq = [':value1' => $user, ':value2' => $code];
        $fav = $this->pdo->prepare($req);
        $fav->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>[]
    */
    public function find(int $id): array
    {
        $req = "SELECT 
            d.ID, 
            d.CTR_EMETT, 
            d.DATE_CREAT, 
            d.PROPRIETAIRE, 
            d.REF, 
            d.LIB, 
            t.LIB as TYPE_LIB, 
            t.ID as ID_TYPE, 
            h.NOM, 
            h.CODE as CODE_PROPRIO, d.DESCR, do.LIB as LIB_OP, do.CODE_OPE, d.STATUT, d.MODELE, d.PRESCRIPTEUR, p.NOM NOM_PRESC,  
            d.DATE_DEB, 
            d.DATE_FIN, 
            d.CODE_OPE, 
            d.IS_PERE, 
            d.SUSPENDU, 
            d.ID_PERE, 
            d.ID_ETAT_AV, 
            d.AJOURNE,
            (select count(*) from DOA_ACTIONS with (nolock) where ID_OBJET=d.ID) ActionTotal,
        	(select count(*) from DOA_ACTIONS with (nolock) where ID_OBJET=d.ID and TERMINEE=0x54) ActionTerminer,
            (SELECT count(*) FROM DOA_RECORD_VAL WHERE ID_DOA=d.ID AND VALEUR<>'') donneeSaisie,
        	(
                select TOP 1 VALEUR from DOA_DATA dd with (nolock) 
                left join DOA_RECORD_VAL drv with (nolock) on dd.ID_OBJET = drv.ID_DOA and dd.ID_DONNEE = drv.ID_DONNEE and dd.ID_DATA = drv.ID_DATA 
                where OBJET  = 'NO_AO' and OBJET_DATA = 'AO_ENTET' and ID_OBJET = d.ID
            ) NO_AO_LIE  
        FROM DOA d with (nolock)
        LEFT JOIN DOAR_TYPE t with (nolock) ON t.ID=ID_TYPE
        LEFT JOIN HASTUTIL h with (nolock) ON h.CODE=d.PROPRIETAIRE
        LEFT JOIN HASTUTIL p with (nolock) ON p.CODE=d.PRESCRIPTEUR
        LEFT JOIN DOAR_OPE do with (nolock) ON d.CODE_OPE=do.CODE_OPE
        WHERE d.ID=:idModel";
        
        return $this->fetchAssocAll($req, [
            ':idModel' => $id,
        ]);
    }

    /**
     * @param array<string> $listId
     * @return array<array<string>>|null
     */
    public function listLinkedProjet(array $listId): ?array
    {
        if (empty($listId)) {
            return [];
        }

        $param = [];
        foreach (array_values($listId) as $k => $id) {
            $param[ ':param_'.$k ] = $id;
        }

        $req = "SELECT 
            projet.*, 
            Proprietaire.NOM as Proprietaire_NOM,
            Prescripteur.NOM as Prescripteur_NOM, 
            CentreEmet.RAIS_SOC as CentreEmet_RAIS_SOC
        FROM DOA projet with (nolock)
        LEFT JOIN HASTUTIL Proprietaire with (nolock) ON Proprietaire.CODE = projet.PROPRIETAIRE
        LEFT JOIN HASTUTIL Prescripteur with (nolock) ON Prescripteur.CODE = projet.PRESCRIPTEUR
        LEFT JOIN CENTRE CentreEmet with (nolock) ON CentreEmet.CODE_CTR = projet.CTR_EMETT
        WHERE projet.ID IN (".join(',', array_keys($param)).")";
        
        return $this->fetchAssocAll($req, $param);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListFamily(int $idPere, int $idProjet): ?array
    {
        $req = "SELECT 
                DOA.*, 
                HASTUTIL.NOM,
                Prescrip.NOM as Prescrip_NOM,
                centre.RAIS_SOC as LIB_CTR_EMETT
            FROM DOA with (nolock) 
            LEFT JOIN HASTUTIL with (nolock) ON HASTUTIL.CODE = DOA.PROPRIETAIRE
            LEFT JOIN HASTUTIL Prescrip with (nolock) ON Prescrip.CODE = DOA.PRESCRIPTEUR
            LEFT JOIN CENTRE centre with (nolock) on centre.CODE_CTR = DOA.CTR_EMETT
            WHERE (ID_PERE = :idProjet1 OR ID = :idProjet2) AND ID != :idProjet3";
        
        return $this->fetchAssocAll($req, [
            ':idProjet1' => $idPere,
            ':idProjet2' => $idPere,
            ':idProjet3' => $idProjet
        ]);
    }
}
