<?php

namespace Modules\ProjetAchat\Repository;

use Entity\Model\DoaActionsRACI;
use Helpers\Outil\Date;
use Helpers\Outil\Roles;
use Helpers\Pagination\PaginatorGestion;
use Helpers\Parametres\Constante;
use Helpers\QueryBuilder\AbsRepository;
use Modules\ProjetAchat\Service\DoublonsService;
use Modules\GlobalApplication\Outil\UOService;

class ProjetAchat extends AbsRepository
{
    /**
     * @param array<mixed> $post
     * @param array<mixed> $droit
     * @return array<mixed>
    */
    public function listeProjetAchat(array $post, array $droit = [], string $listeSelect = null): ?array
    {
        $where = '';
        $arrayReq = [];
        
        $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_ACTIONS with (nolock) 
                        WHERE ID_OBJET=DOA.ID
                    ) ActionTotal,
                    (
                        SELECT count(*) FROM DOA_ACTIONS with (nolock) 
                        WHERE ID_OBJET=DOA.ID and TERMINEE=0x54
                    ) ActionTerminer,
                    (
                        SELECT count(*) FROM DOA_RECORD_VAL with(nolock)
                        WHERE ID_DOA=DOA.ID AND VALEUR<>''
                    ) donneeSaisie,
                    (
                        SELECT TOP 1 ISNULL(CODE_USER, '') as AJO_USR 
                        FROM DOA_EVT with (nolock) 
                        WHERE CODE_EVT = 'AJO' AND ID_DOA = DOA.ID 
                        ORDER BY (DATE_EVT+HEURE_EVT) DESC
                    ) AJO_USR,
                    (
                        SELECT TOP 1 ISNULL(COMMENTAIRE, '') as AJO_COM 
                        FROM DOA_EVT with (nolock) 
                        WHERE CODE_EVT = 'AJO' AND ID_DOA = DOA.ID 
                        ORDER BY (DATE_EVT+HEURE_EVT) DESC
                    ) AJO_COM,
                    (
                        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 = DOA.ID
                    ) NO_AO_LIE,
                    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 = 'CRE'
                LEFT JOIN DOA doaPere with (nolock) on doaPere.ID = DOA.ID_PERE
                WHERE DOA.MODELE=0x46
            ) DOA 
            LEFT JOIN HASTUTIL createur with (nolock) on createur.CODE = DOA.CREATEUR
				WHERE 1=1 $where";
        $this->_listeProjetAchatApplyDroit($req, $arrayReq, $droit);
        $this->_listeProjetAchatApplyPost($req, $arrayReq, $post);

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

    /**
     * @param array<mixed> $post
     * @return array<array<string>>
     */
    public function listeProjetAchatExport(array $post, string $listeSelect = null) :array
    {

        $droit = [
            'DOA_DOA_CM' => Roles::droitsUtilisateur($this->pdo, 'DOA_DOA_CM', $_SESSION["Session_Login"]),
            'DOA_DOA_TT' => Roles::droitsUtilisateur($this->pdo, 'DOA_DOA_TT', $_SESSION["Session_Login"]),
            'DOA_VIA_UO' => Roles::droitsUtilisateur($this->pdo, 'DOA_VIA_UO', $_SESSION["Session_Login"]),
            'DOA_VIA_CT' => Roles::droitsUtilisateur($this->pdo, 'DOA_VIA_CT', $_SESSION["Session_Login"]),
        ];
        
        $req = "SELECT $listeSelect FROM (
				SELECT 
                    DOA.*, 
                    proprio.NOM, 
                    proprio.CODE_UO PROPRIO_UO, 
                    prescri.CODE_UO PRESCRI_UO,
                    CASE WHEN CODE_EVT is null 
                        THEN DOA.PROPRIETAIRE
                        ELSE CODE_USER
                    END CREATEUR
                FROM DOA with (nolock)
                LEFT JOIN DOA_EVT with (nolock) on DOA_EVT.ID_DOA = DOA.ID and CODE_EVT = 'CRE'
                LEFT JOIN HASTUTIL proprio with (nolock) ON proprio.CODE = DOA.PROPRIETAIRE
                LEFT JOIN HASTUTIL prescri with (nolock) ON prescri.CODE = DOA.PROPRIETAIRE
                WHERE DOA.MODELE=0x46
            ) DOA 
            LEFT JOIN HASTUTIL createur with (nolock) ON createur.CODE = DOA.CREATEUR
				WHERE 1=1";

        $arrayReq = [];
        $this->_listeProjetAchatApplyDroit($req, $arrayReq, $droit);
        $this->_listeProjetAchatApplyPost($req, $arrayReq, $post);

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

    /**
     * @param array<string> $post
     * @return array<array<string>>
    */
    public function listeProjetAchatSearch(array $post) :array
    {
        $droit = [
            'DOA_DOA_CM' => Roles::droitsUtilisateur($this->pdo, 'DOA_DOA_CM', $_SESSION["Session_Login"]),
            'DOA_DOA_TT' => Roles::droitsUtilisateur($this->pdo, 'DOA_DOA_TT', $_SESSION["Session_Login"]),
            'DOA_VIA_UO' => Roles::droitsUtilisateur($this->pdo, 'DOA_VIA_UO', $_SESSION["Session_Login"]),
        ];
        $where = '';
        $arrayReq = [];
        
        $where = $this->whereDoublonsDoa($post, $arrayReq);

        $whereAjourne = ($post['originSearchDoublon'] ?? false) ?
            " AND DOA.AJOURNE=0x46 " : '';
        
        $req = "SELECT DOA.*, createur.CODE_UO CREAT_UO FROM (
				SELECT 
                    DOA.*, 
                    proprio.NOM,
                    proprio.CODE_UO PROPRIO_UO, 
                    prescri.CODE_UO PRESCRI_UO,
                    doaPere.LIB LIB_PERE, 
                    doaPere.REF REF_PERE,
                    CASE WHEN CODE_EVT is null 
                        THEN DOA.PROPRIETAIRE
                        ELSE CODE_USER
                    END CREATEUR,
                    Prescripteur.NOM as Prescripteur_NOM,
                    CentreEmetteur.RAIS_SOC as CentreEmetteur_RAIS_SOC
                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 = 'CRE'
                LEFT JOIN DOA doaPere with (nolock) on doaPere.ID = DOA.ID_PERE
                LEFT JOIN HASTUTIL Prescripteur with (nolock) ON Prescripteur.CODE = DOA.PRESCRIPTEUR
                LEFT JOIN CENTRE CentreEmetteur with (nolock) ON CentreEmetteur.CODE_CTR = DOA.CTR_EMETT

                WHERE DOA.MODELE=0x46 {$whereAjourne} 
            ) DOA 
            LEFT JOIN HASTUTIL createur with (nolock) on createur.CODE = DOA.CREATEUR
            WHERE 1=1 $where";
        
        $this->_listeProjetAchatApplyDroit($req, $arrayReq, $droit);
        $this->_listeProjetAchatApplyPost($req, $arrayReq, $post);

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

    /**
     * @param array<string> $post
     * @param array<string> $arrayReq
    */
    public function whereDoublonsDoa(array $post, array &$arrayReq) : string
    {
        $where = " and (STATUT != 'T') ";
        
        if (isset($post['LIB']) && $post['LIB']) {
            $value = mb_strtoupper($post['LIB']);
            if ($post['TYPE_SEARCH'] == 'CONTIENT') {
                $where .=" and  UPPER(LIB) like :filterLib";
                $arrayReq[':filterLib'] = '%'.$value.'%';
            } elseif ($post['TYPE_SEARCH'] == 'COMMENCE') {
                $where .=" and  UPPER(LIB) like :filterLib";
                $arrayReq[':filterLib'] = $value.'%';
            } elseif ($post['TYPE_SEARCH'] == 'EXACT') {
                $where .=" and  UPPER(LI)B = :filterLib";
                $arrayReq[':filterLib'] = $value;
            }
        }
        
        $arrayWhere = [];
        foreach ($post as $key => $val) {
            if (substr($key, 0, 12) == 'panel_filter' && $val === 'on') {
                // $keySplit[1] == table (STATUT, SOCIETE, GROUPSOC ou TYPE_ELEM)
                // $keySplit[2] == valeur
                $keySplit = explode(Constante::spliter, $key);
                $arrayWhere[$keySplit[1]][] = "'".str_replace('@espace@', ' ', $keySplit[2])."'";
            }
        }
        
        foreach ($arrayWhere as $key => $val) {
            if ($key=='STATUT') {
                $implode = implode(",", $val);
                $where .=" and STATUT in ($implode)";
            } elseif ($key=='DOA_SOCIETE' || $key=='GROUPSOC') {
                $implode = implode(",", $val);
                $where .= " and ID in (select ID_DOA from DOA_SOC with (nolock) where TYPE_GS=".($key=='DOA_SOCIETE'? "'S'":"'G'")." and CODE IN ($implode))";
            } else {
                $implode = implode(",", $val);
                $where .=" and ID in (SELECT ID_DOA FROM DOA_ELEM with (nolock) where TYPE_ELEM = '$key' and ID_ELEM IN ($implode))";
            }
        }
        
        return $where;
    }

    /**
     * @param array<mixed> $param
     * @param array<mixed> $droit
     */
    private function _listeProjetAchatApplyDroit(
        string &$req,
        array &$param,
        array $droit
    ) :void {
        // Case : no restriction
        if ($droit['DOA_DOA_TT']) {
            return;
        }

        $req .= " AND ( 
            (CREATEUR = :usr_login1
            OR PROPRIETAIRE = :usr_login2
            OR PRESCRIPTEUR = :usr_login3
        )";

        $param[':usr_login1'] = $_SESSION["Session_Login"];
        $param[':usr_login2'] = $_SESSION["Session_Login"];
        $param[':usr_login3'] = $_SESSION["Session_Login"];

        
        if ($droit['DOA_VIA_CT']) {
            $req .= " OR ( 
                CTR_EMETT IN (
                    SELECT CTR_ADR FROM HASTUTIL with (nolock) 
                    WHERE CODE = :usr_login4
                UNION
                    SELECT CTREMET FROM SOC_USR  with (nolock) 
                    WHERE COD_USR = :usr_login5 AND COD_STE = :usr_soc
                )
            )";

            $param[':usr_soc'] = $_SESSION["Session_SocUsr"];
            $param[':usr_login4'] = $_SESSION["Session_Login"];
            $param[':usr_login5'] = $_SESSION["Session_Login"];
        }
        
        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 createur.CODE_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 .= ")";
    }

    /**
     * @param array<mixed> $arrayReq
     * @param array<mixed> $post
     */
    private function _listeProjetAchatApplyPost(
        string &$req,
        array &$arrayReq,
        array $post
    ) :void {
        foreach ($post as $key => $val) {
            $i = 1;
            if (strpos($key, 'DOAELEM_') === 0 && substr($key, -4) != 'Cond') {
                $req .=" AND ID ".($post[$key.'Cond'] == 'egal' ? 'IN' : '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']) && $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'];
        }
        
        if (isset($post['DOA']) && $post['DOA']) {
            $req .= " and (ID = :idDoa or ID_PERE = :doaPere)";
            $arrayReq[':idDoa'] = $post['DOA'];
            $arrayReq[':doaPere'] = $post['DOA'];
        }
        
        if (isset($post['MASQUER']) && $post['MASQUER']) {
            $req .= " and STATUT != 'T'";
        }

        if (isset($post['filter_STATUT']) && ($post['filter_STATUT']=='IS' || $post['filter_STATUT']=='PS' || $post['filter_STATUT']=='ES')) {
            $post['filter_STATUT'] = substr($post['filter_STATUT'], 0, 1);
            
            $req .= " and SUSPENDU = 0x54";
        } elseif (isset($post['filter_STATUT']) && $post['filter_STATUT'] != '') {
            $req .= " and SUSPENDU = 0x46";
        }

        if (isset($post['filter_STATUT']) && ($post['filter_STATUT'] == 'IA' || $post['filter_STATUT'] == 'PA' || $post['filter_STATUT'] == 'EA')) {
            $post['filter_STATUT'] = substr($post['filter_STATUT'], 0, 1);
            $req .= " and AJOURNE = 0x54";
        } elseif (isset($post['filter_STATUT']) && $post['filter_STATUT'] != '') {
            $req .= " AND AJOURNE = 0x46";
        }

        if (isset($post['filter_date_DATE_DEB']) && $post['filter_date_DATE_DEB'] != '') {
            $req .= ' and DOA.DATE_DEB = :DATE_DEB';
            Date::dateToSql($post['filter_date_DATE_DEB']);
            $arrayReq[':DATE_DEB'] = $post['filter_date_DATE_DEB'];
            unset($post['filter_date_DATE_DEB']);
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1, false, 'DOA');

        $filtreAvance->pushArray($arrayReq);
        
        if (count($whereArray)>  0) {
            if ($whereArray['tri']) {
                $req .= $whereArray[0].$whereArray['tri'];
            } else {
                $req .= $whereArray[0].' order by ID DESC';
            }
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        } else {
            $req .= ' order by ID DESC';
        }
    }
    
    /**
     * @return array<mixed>
    */
    public function detailProjetAchat(int $idProjetAchat): ?array
    {
        $req = "SELECT DOA.*, DOA_EVT.CODE_USER, userEvent.NOM NOM_EVENT, userProprio.NOM NOM_PROPRIO from DOA with (nolock) 
                left join DOA_EVT with (nolock) on DOA_EVT.ID_DOA = DOA.ID and CODE_EVT = 'CRE'
                left join HASTUTIL userEvent with (nolock) on userEvent.CODE = DOA_EVT.CODE_USER
                left join HASTUTIL userProprio with (nolock) on userProprio.CODE = DOA.PROPRIETAIRE
                where ID= :id";
        
        $arrayReq = [':id' => $idProjetAchat];
        
        $shortList = $this->pdo->prepare($req);
        $shortList->execute($arrayReq);
        
        return  $shortList->fetch($this->pdo::FETCH_ASSOC);
    }

    

    /**
     * @return array<mixed>
    */
    public function detailProjetAchatFull(int $idProjetAchat): array
    {
        $req = "SELECT 
                DOA.*, 
                DOA_EVT.CODE_USER, 
                userEvent.NOM NOM_EVENT, 
                userProprio.NOM NOM_PROPRIO,
                Prescripteur.NOM as Prescripteur_NOM
            FROM DOA with (nolock) 
            LEFT JOIN DOA_EVT with (nolock) ON DOA_EVT.ID_DOA = DOA.ID and CODE_EVT = 'CRE'
            LEFT JOIN HASTUTIL userEvent with (nolock) ON userEvent.CODE = DOA_EVT.CODE_USER
            LEFT JOIN HASTUTIL userProprio with (nolock) ON userProprio.CODE = DOA.PROPRIETAIRE
            LEFT JOIN HASTUTIL Prescripteur with (nolock) ON Prescripteur.CODE = DOA.PRESCRIPTEUR
            LEFT JOIN CENTRE CentreEmetteur with (nolock) ON CentreEmetteur.CODE_CTR = DOA.CTR_EMETT
            WHERE ID = :id";
        
        $arrayReq = [':id' => $idProjetAchat];
        
        $shortList = $this->pdo->prepare($req);
        $shortList->execute($arrayReq);
        
        return  $shortList->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function getDaysOffDOA(int $idProjetAchat): ?array
    {
        $req = 'SELECT cal.JOURS_FERMES, jf.DATE_JOUR
            FROM DOA d with(nolock)
            LEFT JOIN DOAR_CAL cal with(nolock) ON cal.ID = d.ID_CAL
            LEFT JOIN DOAR_JF jf with(nolock) ON jf.ID_CAL = d.ID_CAL
            WHERE d.ID = :idDoa';

        $arrayReq = [
            ':idDoa'    => $idProjetAchat
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getInfoDoabyRef(string $ref): ?array
    {
        $req = "select * from DOA with (nolock) where REF = :ref";
        
        $arrayReq = [':ref' => $ref];
        
        $shortList = $this->pdo->prepare($req);
        $shortList->execute($arrayReq);
        
        return  $shortList->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @param array<string|int> $data
    */
    public function supprimeIdStruct(array $data): void
    {
        $this->supprimerDOAStruct($data['TYPE_OBJET'], $data['ID_OBJET'], $data['ID_DONNEE']);
        $this->supprimerDOAActionDon($data['TYPE_OBJET'], $data['ID_OBJET'], $data['ID_DONNEE']);
        
        $champRepository = new \Modules\ProjetAchat\Repository\Champ($this->pdo);
        $champRepository->supprimeData($data['TYPE_OBJET'], $data['ID_OBJET'], $data['ID_DONNEE']);
        
        $dataActionRepository = new \Modules\ProjetAchat\Repository\DataAction($this->pdo);
        $dataActionRepository->supprimerDataAction($data['TYPE_OBJET'], $data['ID_OBJET'], $data['ID_DONNEE']);
    }
    
    /**
     * @param array<string|int> $data
    */
    public function supprimeLienIdStruct(array $data): void
    {
        
        $champRepository = new \Modules\ProjetAchat\Repository\Champ($this->pdo);
        $champRepository->modifierData($data['TYPE_OBJET'], $data['ID_OBJET'], $data['ID_DONNEE'], 0);
        
        $this->supprimeLienIdStructData($data);
    }
    
    /**
     * @param array<string|int> $data
    */
    public function supprimeLienIdStructData(array $data): void
    {
        
        $champRepository = new \Modules\ProjetAchat\Repository\Champ($this->pdo);
        
        if (array_key_exists('ID_DATA', $data)) {
            if ($data = $champRepository->getInfoData($data['ID_DATA'], $data['TYPE_OBJET'], $data['ID_OBJET'], $data['ID_DONNEE'])) {
                if (\Helpers\Outil\Sql::boolSql($data['DATA_LIE_A_OBJ'])) {
                    if ($data['ID_DATA_LIEE'] == 0) {
                        $champRepository->modifierData($data['TYPE_OBJET'], $data['ID_OBJET'], $data['ID_DONNEE'], 0, $data['ID_DATA']);
                    }
                }
            }
        }
        
        $champRepository->modifierData($data['TYPE_OBJET'], $data['ID_OBJET'], $data['ID_DONNEE'], 0, ($data['ID_DATA'] ?? null));
    }
    
    public function supprimerDOAStruct(string $typeObjet, int $idObjet, int $idDonnee) : void
    {
        $req = 'delete
        from DOA_STRUCT
        WHERE ID_DONNEE  =  :idDonnee
            AND   TYPE_OBJET = :typeObjet
            AND  ID_OBJET   =  :idObjet';
        
        
        $arrayReq = [
            ':typeObjet'       => $typeObjet,
            ':idObjet'         => $idObjet,
            ':idDonnee'        => $idDonnee
        ];
        
        $dataAction              = $this->pdo->prepare($req);
        $dataAction->execute($arrayReq);
    }
    
    public function supprimerDOAActionDon(string $typeObjet, int $idObjet, int $idDonnee) : void
    {
        
        $req = 'delete
        from DOA_ACTIONS_DON
         WHERE ID_DONNEE  =  :idDonnee AND   TYPE_OBJET = :typeObjet AND   ID_OBJET   =   :idObjet';
        
        
        $arrayReq = [
            ':typeObjet'       => $typeObjet,
            ':idObjet'         => $idObjet,
            ':idDonnee'        => $idDonnee
        ];
        
        $dataAction              = $this->pdo->prepare($req);
        $dataAction->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function listePlanningHistorise(int $idDoa): ?array
    {
        $req = "select * from DOA_HISTO_PLANNING_ENT with (nolock) where ID_DOA=:idDoa order by ID_VERSION DESC";
        
        $arrayReq = [
            ':idDoa' => $idDoa,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function rqGetInfoForMail(int $id): ?array
    {
        $req = "select DOA.REF, DOA.LIB, DOA.DESCR, DOAR_TYPE.LIB LIB_TYPE, DOAR_OPE.LIB LIB_OPERATION, prescri.NOM NOM_PRESCRIPTEUR, CENTRE.RAIS_SOC CTR_EMETT, DOA.DATE_DEB, DOA.DATE_FIN from DOA with (nolock)
                left join DOAR_TYPE with (nolock) on DOAR_TYPE.ID = DOA.ID_TYPE
                left join DOAR_OPE with (nolock) on DOAR_OPE.CODE_OPE = DOA.CODE_OPE
                left join HASTUTIL prescri with (nolock) on prescri.CODE = DOA.PRESCRIPTEUR
                left join CENTRE with (nolock) on CENTRE.CODE_CTR = DOA.CTR_EMETT
                where DOA.ID=:idDoa";
        
        $arrayReq = [
            ':idDoa' => $id,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListElem(int $idDoa): ?array
    {
        $req = "SELECT * FROM DOA_ELEM WITH (NOLOCK) WHERE ID_DOA = :idDoa ORDER BY TYPE_ELEM";
        
        $arrayReq = [
            ':idDoa' => $idDoa,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    public function updateResponsableAction(int $idDoa, string $oldPoprio, string $codeActeur, bool $all = false): void
    {
        $req = "update DOA_ACTIONS_ACTEURS set CODE_ACTEUR = :codeActeur where ID_ACTION in (
                	select DOA_ACTIONS_ACTEURS.ID_ACTION from DOA_ACTIONS_ACTEURS with (nolock)
                	left join DOA_ACTIONS with (nolock) on DOA_ACTIONS.ID_ACTION = DOA_ACTIONS_ACTEURS.ID_ACTION
                	left join DOA with (nolock) on DOA.ID = DOA_ACTIONS_ACTEURS.ID_OBJET
                	where TERMINEE = 0x46 AND DOA_ACTIONS_ACTEURS.ID_OBJET = :idDoa
                ) and CODE_ACTEUR = :oldPoprio";
        
        if ($all == false) {
            $req .= " and ROLE='R' ";
        }
        
        
        $arrayReq = [
            ':codeActeur' => $codeActeur,
            ':oldPoprio' => $oldPoprio,
            ':idDoa' => $idDoa,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function updateResponsableTaches(int $idDoa, string $oldPoprio, string $newResponsable): void
    {
        $req = "update  DOA_TACHES set RESPONSABLE = :newResponsable where ID_OBJET = :idDoa and RESPONSABLE = :oldPoprio and TERMINEE = 0x46";
        
        
        $arrayReq = [
            ':newResponsable' => $newResponsable,
            ':oldPoprio' => $oldPoprio,
            ':idDoa' => $idDoa,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function updateResponsableRaciAction(
        int $idDoa,
        string $oldPoprio,
        string $newResponsable,
        bool $all = false
    ): void {
        (new DoaActionsRACI($this->pdo))->update(
            ['CODE_USER' => $newResponsable],
            [
                'ID_DOA' => $idDoa,
                'TERMINE' => false,
                'CODE_USER' => $oldPoprio,
            ] + ($all == false ? [
                'RACI' => 'R',
            ] : [])
        );
    }
    
    /**
     * @return array<mixed>
    */
    public function docExistInData(int $idObjet, int $numOrder): ?array
    {
        $req = "SELECT dd.NOM_DATA, drv.ID_DATA, drv.ID_DONNEE, drv.ID_RECORD from DOA_RECORD_VAL drv
                left join DOA_DATA dd with(nolock) on dd.ID_OBJET = drv.ID_DOA 
                    and dd.ID_DONNEE = drv.ID_DONNEE and dd.ID_DATA = drv.ID_DATA
                where ID_DOA=:idDoa and TYPE_DATA = 'F' and VALEUR = :numOrder";
        
        $arrayReq = [
            ':idDoa' => $idObjet,
            ':numOrder' => $numOrder,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function dataIsTerminer(int $idObjet, int $idData): ?array
    {
        $req = "SELECT * from DOA_ACTIONS_DATA with(nolock) 
                LEFT JOIN DOA_ACTIONS with(nolock) on DOA_ACTIONS.TYPE_OBJET = DOA_ACTIONS_DATA.TYPE_OBJET and  DOA_ACTIONS.ID_OBJET = DOA_ACTIONS_DATA.ID_OBJET and  DOA_ACTIONS.ID_TACHE = DOA_ACTIONS_DATA.ID_TACHE and  DOA_ACTIONS.ID_ACTION = DOA_ACTIONS_DATA.ID_ACTION
                WHERE DOA_ACTIONS_DATA.ID_OBJET = :idDoa and ID_DATA = :idData AND TERMINEE = 0x54";
        
        $arrayReq = [
            ':idDoa' => $idObjet,
            ':idData' => $idData,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    public function deleteFileData(int $idObjet, int $idDonnee, int $idData, int $idRecord): void
    {
        $req = "update from DOA_RECORD_VAL set VALEUR = '' where ID_DOA = :idDoa and ID_DONNEE = :idDonnee and ID_DATA = :idData and ID_RECORD = :idRecord";
        
        $arrayReq = [
            ':idDoa' => $idObjet,
            ':idDonnee' => $idDonnee,
            ':idData' => $idData,
            ':idRecord' => $idRecord,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListDoarOpe(): ?array
    {
        $params = [
            ':dateDeb' => date('Ymd'),
            ':dateFin' => date('Ymd'),
        ];

        $query = "SELECT 
            DOAR_OPE.CODE_OPE, 
            DOAR_OPE.LIB, 
            " . $this->selectTrad('DOAR_OPE.LIB') . ",
            DOAR_OPE.DESCR, 
            DOAR_OPE.DATE_DEB, 
            DOAR_OPE.DATE_FIN
        FROM DOAR_OPE WITH (NOLOCK)
        " . $this->joinTrad('DOAR_OPE.LIB', $params) . "
        WHERE DOAR_OPE.DATE_DEB < :dateDeb and DOAR_OPE.DATE_FIN > :dateFin";

        return $this->fetchAssocAll($query, $params);
    }
        
    /**
     * getDoarTypeList
     *
     * @return array<mixed>
     */
    public function getDoarTypeList(): ?array
    {
        $params = [];
        $query = "SELECT 
            DOAR_TYPE.ID, 
            DOAR_TYPE.ID_PARENT, 
            " . $this->selectTrad('DOAR_TYPE.LIB') . ",
            " . $this->selectTrad('DOAR_TYPE.LIB_LONG') . ",
            DOAR_TYPE.DESCR, 
            DOAR_TYPE.PHP_FONT, 
            DOAR_TYPE.PHP_COLOR, 
            DOAR_TYPE.FIC_ICONE, 
            DOAR_TYPE.NUM_ORDRE 
        FROM DOAR_TYPE WITH (NOLOCK)
        " . $this->joinTrad('DOAR_TYPE.LIB', $params) . "
        " . $this->joinTrad('DOAR_TYPE.LIB_LONG', $params, 'trad2') . "
        WHERE 1=1";

        return $this->fetchAssocAll($query, $params);
    }
    
    /**
     *
     * @return array<mixed>
    */
    public function getListeProjetByListId(string $listId): ?array
    {
        $req = "select * from DOA with (nolock) where ID in ($listId)";

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

    /**
     * @param int $idProjetAchat
     * @return void
     */
    public function ajourneProjet(int $idProjetAchat): void
    {
        $stmt = "
            UPDATE
                DOA
            SET
                DOA.AJOURNE = 0x54
            WHERE
                DOA.ID = :idProjetAchat
        ";

        $query = $this->pdo->prepare($stmt);
        $query->execute([
            ':idProjetAchat' => $idProjetAchat,
        ]);
    }

    /**
     * @param int $idProjetAchat
     * @return void
     */
    public function reprendreProjet(int $idProjetAchat): void
    {
        $stmt = "
            UPDATE
                DOA
            SET
                DOA.AJOURNE = 0x46
            WHERE
                DOA.ID = :idProjetAchat
        ";

        $query = $this->pdo->prepare($stmt);

        $query->execute([
            ':idProjetAchat' => $idProjetAchat,
        ]);
    }
}
