<?php

namespace Modules\ProjetAchat\Repository;

use Helpers\QueryBuilder\AbsRepository;

class Action extends AbsRepository
{

    /**
     * @return array<mixed>
    */
    public function getListActor(int $idAction)
    {
        $req = "select DOA_ACTIONS_ACTEURS.*, NOM from DOA_ACTIONS_ACTEURS with(nolock)
                left join HASTUTIL with(nolock) on HASTUTIL.CODE = DOA_ACTIONS_ACTEURS.CODE_ACTEUR
                where ID_ACTION=:idAction";

        $arrayReq = [
            ':idAction' => $idAction,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListRACI(int $idAction, bool $actorOnly = false)
    {
        $req = "select DOA_ACTIONS_RACI.RACI ROLE, DOA_ACTIONS_RACI.CODE_USER CODE_ACTEUR, DOA_ACTIONS_RACI.EMAIL, NOM, TERMINE, DOA_EVT.DATE_EVT, DOA_EVT.HEURE_EVT from DOA_ACTIONS_RACI with(nolock)
                left join HASTUTIL with(nolock) on HASTUTIL.CODE = DOA_ACTIONS_RACI.CODE_USER
                left join DOA_EVT with(nolock) on DOA_EVT.ID_DOA = DOA_ACTIONS_RACI.ID_DOA AND DOA_EVT.ID_TACHE = DOA_ACTIONS_RACI.ID_TACHE AND DOA_EVT.ID_ACTION = DOA_ACTIONS_RACI.ID_ACTION and DOA_EVT.CODE_USER = DOA_ACTIONS_RACI.CODE_USER and DOA_EVT.CODE_EVT = 'AAT'
                where DOA_ACTIONS_RACI.ID_ACTION=:idAction";

        if ($actorOnly) {
            $req .= " and RACI in ('A', 'C')";
        }

        $arrayReq = [
            ':idAction' => $idAction,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListStruct(int $idAction, int $idData = null)
    {
        /*$req = "select DISTINCT(dad.ID_DONNEE), ds.*, dadon.NUM_ORDRE ordreDon from DOA_ACTIONS_DATA dad with(nolock)
                left join DOA_STRUCT ds with(nolock) on ds.TYPE_OBJET = dad.TYPE_OBJET and ds.ID_OBJET = dad.ID_OBJET and ds.ID_DONNEE = dad.ID_DONNEE
                left join DOA_ACTIONS_DON dadon with(nolock) on dadon.TYPE_OBJET = dad.TYPE_OBJET and dadon.ID_OBJET = dad.ID_OBJET and dad.ID_TACHE = dadon.ID_TACHE and dadon.ID_DONNEE = dad.ID_DONNEE
                where dad.ID_ACTION=:idAction ";

        $arrayReq = [
            ':idAction' => $idAction,
        ];

        if ($idData) {
            $req .= " and dad.ID_DONNEE = :idData";
            $arrayReq[':idData'] = $idData;
        }*/

        //TODO 301107
        $req = "select ds.*, dadon.NUM_ORDRE ordreDon 
                from DOA_ACTIONS_DON dadon with (nolock)
                left join DOA_STRUCT ds with(nolock) on ds.TYPE_OBJET = dadon.TYPE_OBJET and ds.ID_OBJET = dadon.ID_OBJET and ds.ID_DONNEE = dadon.ID_DONNEE 
                where ID_ACTION = :idAction";

        $arrayReq = [
            ':idAction' => $idAction,
        ];
        
        if ($idData) {
            $req .= " and ds.ID_DONNEE = :idData";
            $arrayReq[':idData'] = $idData;
        }

        $req .= " order by dadon.NUM_ORDRE";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListData(int $idAction, int $idDonnee)
    {
        $req = "select dad.*, ds.NOM_DONNEE, ds.MULTIPLE, dd.LIE_A_OBJET, ds.OBJET, dd.NOM_DATA, dd.FLAG_ID_OBJET, dd.DATA_LIE_A_OBJ, dd.ID_OBJET_DATA from DOA_ACTIONS_DATA dad with(nolock)
                left join DOA_DATA dd with(nolock) on dd.TYPE_OBJET = dad.TYPE_OBJET and dd.ID_OBJET = dad.ID_OBJET and dd.ID_DONNEE = dad.ID_DONNEE and dd.ID_DATA = dad.ID_DATA   
                left join DOA_STRUCT ds with(nolock) on ds.TYPE_OBJET = dad.TYPE_OBJET and ds.ID_OBJET = dad.ID_OBJET and ds.ID_DONNEE = dad.ID_DONNEE 
                where dad.ID_ACTION=:idAction and dad.ID_DONNEE=:idDonnee";
        
        $arrayReq = [
            ':idAction' => $idAction,
            ':idDonnee' => $idDonnee,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    public function deleteAction(int $idTache) : void
    {
        $req = "delete from DOA_ACTIONS where ID_TACHE = :idTache";
        
        $arrayReq = [
            ':idTache' => $idTache,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function deleteActionData(int $idTache) : void
    {
        $req = "delete from DOA_ACTIONS_DATA where ID_TACHE = :idTache";
        
        $arrayReq = [
            ':idTache' => $idTache,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function deleteActionActeur(int $idTache) : void
    {
        $req = "delete from DOA_ACTIONS_ACTEURS where ID_TACHE = :idTache";
        
        $arrayReq = [
            ':idTache' => $idTache,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function deleteActionDon(int $idTache) : void
    {
        $req = "delete from DOA_ACTIONS_DON where ID_TACHE = :idTache";
        
        $arrayReq = [
            ':idTache' => $idTache,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListCategorieOnObjet(string $typeObjet, string $idObjet, string $idTask, string $idAction)
    {
        $req = "select distinct DOAR_CAT.ID, DOAR_CAT.LIB from DOA_STRUCT with(nolock) 
                inner join DOAR_CAT with(nolock) on DOA_STRUCT.TYPE_OBJET = DOAR_CAT.TYPE_OBJET and DOA_STRUCT.ID_OBJET = DOAR_CAT.ID_OBJET and DOA_STRUCT.ID_CAT = DOAR_CAT.ID 
                where DOA_STRUCT.TYPE_OBJET = :typeObjet and   DOA_STRUCT.ID_OBJET= :idObjet
                and not exists( select null from DOA_ACTIONS_DATA with (nolock) 
                                where DOA_ACTIONS_DATA.TYPE_OBJET = DOA_STRUCT.TYPE_OBJET 
                                and DOA_ACTIONS_DATA.ID_OBJET = DOA_STRUCT.ID_OBJET 
                                and DOA_ACTIONS_DATA.ID_TACHE = :idTask and  DOA_ACTIONS_DATA.ID_ACTION   = :idAction
                                and DOA_ACTIONS_DATA.ID_DONNEE = DOA_STRUCT.ID_DONNEE ) 
                order by DOAR_CAT.LIB";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':idTask' => $idTask,
            ':idAction' => $idAction,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    public function deleteDataAction(string $typeObjet, int $idObjet, int $idTask, int $idAction, int $idDonnee) : void
    {
        $req = "delete from DOA_ACTIONS_DATA WHERE TYPE_OBJET=:typeObjet and ID_OBJET = :idObjet and ID_TACHE = :idTask and ID_ACTION = :idAction and ID_DONNEE = :idDonnee";
    
        $arrayReq = [
        ':typeObjet' => $typeObjet,
        ':idObjet' => $idObjet,
        ':idTask' => $idTask,
        ':idAction' => $idAction,
        ':idDonnee' => $idDonnee,
        ];
    
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListeDataOnObjet(string $typeObjet, int $idObjet, int $idCat, int $idTask, int $idAction)
    {
        $req = "select DOA_STRUCT.*, DOAR_CAT.LIB as CAT_LIB, DOAR_CAT.DESCR as CAT_DESCR 
                from DOA_STRUCT with(nolock) 
                inner join DOAR_CAT  with(nolock) on DOA_STRUCT.TYPE_OBJET = DOAR_CAT.TYPE_OBJET and DOA_STRUCT.ID_OBJET = DOAR_CAT.ID_OBJET and DOA_STRUCT.ID_CAT = DOAR_CAT.ID 
                where  DOA_STRUCT.TYPE_OBJET = :typeObjet and DOA_STRUCT.ID_OBJET = :idObjet
                and DOA_STRUCT.ID_CAT = :idCat and not exists(  select null from DOA_ACTIONS_DATA with (nolock) 
                											where DOA_ACTIONS_DATA.TYPE_OBJET = DOA_STRUCT.TYPE_OBJET and DOA_ACTIONS_DATA.ID_OBJET = DOA_STRUCT.ID_OBJET 
                											and DOA_ACTIONS_DATA.ID_TACHE = :idTask and DOA_ACTIONS_DATA.ID_ACTION = :idAction and DOA_ACTIONS_DATA.ID_DONNEE  = DOA_STRUCT.ID_DONNEE)" ;
        
        $arrayReq = [
        ':typeObjet' => $typeObjet,
        ':idObjet' => $idObjet,
        ':idCat' => $idCat,
        ':idTask' => $idTask,
        ':idAction' => $idAction,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getRightDoaActionsDonOrder(string $typeObjet, int $idObjet, int $idTask, int $idAction)
    {
        $sql = "select max(NUM_ORDRE) from DOA_ACTIONS_DON with(nolock) WHERE TYPE_OBJET=:typeObjet and ID_OBJET = :idObjet and ID_TACHE = :idTask and ID_ACTION = :idAction";
        
        $arrayReq = [
        ':typeObjet' => $typeObjet,
        ':idObjet' => $idObjet,
        ':idTask' => $idTask,
        ':idAction' => $idAction,
        ];
        
        
        $requete = $this->pdo->prepare($sql);
        
        $requete->execute($arrayReq);
        
        return $requete->fetch()[0];
    }
    
    public function deleteDonAction(string $typeObjet, int $idObjet, int $idTask, int $idAction, int $idDonnee) : void
    {
        $req = "delete from DOA_ACTIONS_DON WHERE TYPE_OBJET=:typeObjet and ID_OBJET = :idObjet and ID_TACHE = :idTask and ID_ACTION = :idAction and ID_DONNEE = :idDonnee";
        
        $arrayReq = [
        ':typeObjet' => $typeObjet,
        ':idObjet' => $idObjet,
        ':idTask' => $idTask,
        ':idAction' => $idAction,
        ':idDonnee' => $idDonnee,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function actorResponsableExist(string $typeObjet, int $idObjet, int $idTasks, int $idAction)
    {
        $req = "select * from DOA_ACTIONS_ACTEURS with(nolock) WHERE TYPE_OBJET=:typeObjet and ID_OBJET = :idObjet and ID_TACHE = :idTask and ID_ACTION = :idAction and ROLE='R'";

        $arrayReq = [
        ':typeObjet' => $typeObjet,
        ':idObjet' => $idObjet,
        ':idTask' => $idTasks,
        ':idAction' => $idAction,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    /**
     * @return array<mixed>
    */
    public function getListeDeclencheurProjet(int $idObjet)
    {
        $req = "select distinct TYPE_ELEM from DOA_ELEM with(nolock) where ID_DOA = :idObjet order by TYPE_ELEM";
        
        $arrayReq = [
        ':idObjet' => $idObjet,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListeValueDeclencheur(int $idObjet, string $typeElem)
    {
        $req = "select TYPE_ELEM, ID_ELEM from DOA_ELEM with(nolock) where ID_DOA = :idObjet and TYPE_ELEM=:typeElem";
        
        $arrayReq = [
        ':idObjet' => $idObjet,
        ':typeElem' => $typeElem,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListeCSTypeActeur(string $codTab)
    {
        $arrayReq = [
            ':codTab' => $codTab,
        ];
        $req = "SELECT 
                COD_TAB, 
                NUM_FIX,
                NUM_VAR,
                TYPE, 
                " . $this->selectTrad('CS_CHPS.NOM') . ",
                OBLIG, 
                NONDISPO,
                MODAPVAL,
                NONMODIF,
                NONDUPLI,
                MODIFSIGN,
                OXALINK_VIS,
                OXALINK_OBLIG
            FROM CS_CHPS WITH(NOLOCK)
            " . $this->joinTrad('CS_CHPS.NOM', $arrayReq) . "
            WHERE COD_TAB = :codTab 
                AND TYPE IN ('ACHETEUR','DEMANDEU','CREATEURFAC','HASTUTIL','SIGDA','SIGCDE','SIGBAP','SIGDOC','UO')";
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getData(Int $idAction)
    {
        $req = "SELECT *
            FROM DOA_ACTIONS_DON dad with (nolock)
            LEFT OUTER JOIN DOA_STRUCT ds with (nolock) ON dad.ID_DONNEE = ds.ID_DONNEE
            WHERE dad.ID_ACTION = :idAction
            order by dad.NUM_ORDRE";

        $arrayReq = [
            ':idAction' => $idAction
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getDataByCategorie(int $idCat)
    {
        $req = "SELECT *
                FROM DOA_STRUCT ds with (nolock)
                where ds.ID_CAT = :idCat";

        $arrayReq = [
            ':idCat' => $idCat
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getHeaders(Int $idAction, Int $idData)
    {
        $req = "SELECT *
            FROM DOA_ACTIONS_DATA dad with (nolock)
            LEFT OUTER JOIN DOA_DATA dd with (nolock) ON dad.ID_DATA = dd.ID_DATA
            WHERE ID_ACTION = :idAction
            AND dad.ID_DONNEE = :idData AND dad.AFFICHEE = 0x54 order by dd.NUM_ORDRE";

        $arrayReq = [
            ':idAction' => $idAction,
            ':idData'   => $idData
        ];
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getHeadersByData(int $idData)
    {
        $req = "SELECT * FROM DOA_DATA with (nolock) WHERE ID_DONNEE = :idData order by DOA_DATA.NUM_ORDRE";
        
        $arrayReq = [
            ':idData'   => $idData
        ];
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getDatas(Int $idData)
    {
        $req = "SELECT *
            FROM DOA_RECORD_VAL with (nolock)
            WHERE ID_DONNEE = :idData
            ORDER BY ID_RECORD, ID_DATA ASC";

        $arrayReq = [
            ':idData'   => $idData
        ];
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getInfoData(string $idAction, int $idData, string $idRecord = null, bool $objectLink = false, string $dataLink = null)
    {
        if ($idAction == 'noId') {
            $req = "select dd.*, 0x54 'AFFICHEE', 0x54 'MODIFIABLE'  ".($idRecord!== null? ", drc.VALEUR":"")."
                    from DOA_DATA dd with (nolock)
                    ".($idRecord!== null? "left join DOA_RECORD_VAL drc with(nolock) ON drc.ID_DATA = dd.ID_DATA AND ID_RECORD= :idRecord":"")."
                    where dd.ID_DONNEE = :idData";
            
            $arrayReq = [
            ':idData'   => $idData,
            ];
        } else {
            $req = "SELECT dd.*, dad.AFFICHEE, dad.MODIFIABLE ".($idRecord!== null? ", drc.VALEUR":"")."
                FROM DOA_ACTIONS_DATA dad with (nolock)
                LEFT JOIN DOA_DATA dd with (nolock) ON dad.ID_DATA = dd.ID_DATA
                ".($idRecord!== null? "left join DOA_RECORD_VAL drc with(nolock) ON drc.ID_DATA = dd.ID_DATA AND ID_RECORD= :idRecord":"")."
                WHERE ID_ACTION = :idAction AND dad.ID_DONNEE = :idData";
            
            $arrayReq = [
            ':idAction' => $idAction,
            ':idData'   => $idData,
            ];
        }
        
        if ($idRecord !== null) {
            $arrayReq[':idRecord'] = $idRecord;
        }
        
        if ($objectLink === true) {
            $req .=" AND LIE_A_OBJET = 0x54";
        }
        
        if ($dataLink) {
            $req .=" and ID_DATA_LIEE = :dataLink ";
            $arrayReq[':dataLink'] = $dataLink;

            if ($idAction != 'noId') {
                $req .=" or dad.ID_DATA = :dataLink2";
                $arrayReq[':dataLink2'] = $dataLink;
            }
        }
        
        $req .= " order by dd.NUM_ORDRE";
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getRecord(int $idDonne, int $idData, int $idRecord)
    {
        $req = "select * from DOA_RECORD_VAL with (nolock) where ID_DONNEE=:idDonnee and ID_RECORD = :idRecord and ID_DATA = :idData";
        
        $arrayReq = [
        ':idDonnee' => $idDonne,
        ':idRecord' => $idRecord,
        ':idData'   => $idData,
        ];
        
        $request = $this->pdo->prepare($req);
        $request->execute($arrayReq);
        
        return $request->fetch();
    }

    /**
     * @return array<mixed>
    */
    public function getListeRecord(int $idObjet, int $idDonne)
    {
        $req = "select ID_RECORD from DOA_RECORD with (nolock) where ID_DOA = :idObjet and ID_DONNEE = :idDonnee";
        
        $arrayReq = [
            ':idObjet' => $idObjet,
            ':idDonnee' => $idDonne,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getMaxRecord(int $idObjet, int $idDonne)
    {
        $req = "select max(ID_RECORD) maxID_RECORD from DOA_RECORD with (nolock) where ID_DOA = :idObjet and ID_DONNEE=:idDonnee ";
        
        $arrayReq = [
        ':idObjet' => $idObjet,
        ':idDonnee' => $idDonne,
        ];
        
        $request = $this->pdo->prepare($req);
        $request->execute($arrayReq);
        
        return $request->fetch();
    }
    
    public function deleteDoaRecord(int $idObjet, int $idDonne, int $idRecord) : void
    {
        $req = "delete from DOA_RECORD where ID_DOA = :idObjet and ID_DONNEE = :idDonnee and ID_RECORD = :idRecord";
        
        $arrayReq = [
        ':idObjet' => $idObjet,
        ':idDonnee' => $idDonne,
        ':idRecord' => $idRecord
        ];
        
        $request = $this->pdo->prepare($req);
        $request->execute($arrayReq);
    }
    
    public function deleteDoaRecordVal(int $idObjet, int $idDonne, int $idRecord) : void
    {
        $req = "delete from DOA_RECORD_VAL where ID_DOA = :idObjet and ID_DONNEE = :idDonnee and ID_RECORD = :idRecord";
        
        $arrayReq = [
        ':idObjet' => $idObjet,
        ':idDonnee' => $idDonne,
        ':idRecord' => $idRecord
        ];
        
        $request = $this->pdo->prepare($req);
        $request->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function importDataExist(int $idStruct)
    {
        $req = "select * from PARAMDICO with(nolock) where TYPEDICO='DOA_FORMAT_IMPORT_DONNEES' and ID1=:idStruct and LIBELLE<>'0'";
        
        $arrayReq = [
        ':idStruct' => $idStruct,
        ];
        
        $request = $this->pdo->prepare($req);
        $request->execute($arrayReq);
        
        return $request->fetch();
    }
    
    /**
     * @param array<string> $payload
    */
    public function reorderData(array $payload = []) : void
    {
        $firstReq = "UPDATE DOA_ACTIONS_DON
                    SET NUM_ORDRE = 0
                    WHERE NUM_ORDRE = :initialPos AND ID_ACTION = :idAction";
        
        $firstExec = $this->pdo->prepare($firstReq);
        $firstExec->execute([
        ':initialPos' => $payload['initialPosition'],
        ':idAction' => $payload['idAction'],
        ]);
        
        if ("+" === $payload['sign']) {
            $secondReq = "UPDATE DOA_ACTIONS_DON
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :finalPos AND  NUM_ORDRE <= :initialPos AND ID_ACTION = :idAction";
        } else {
            $secondReq = "UPDATE DOA_ACTIONS_DON
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :initialPos AND  NUM_ORDRE <= :finalPos AND ID_ACTION = :idAction";
        }
        
        $secondExec = $this->pdo->prepare($secondReq);
        $secondExec->execute([
        ':finalPos' => $payload['newPosition'],
        ':initialPos' => $payload['initialPosition'],
        ':idAction' => $payload['idAction'],
        ]);
        
        $thirdReq = "UPDATE DOA_ACTIONS_DON
                    SET NUM_ORDRE = :finalPos
                    WHERE NUM_ORDRE = 0 AND ID_ACTION = :idAction";
        
        $thirdExec = $this->pdo->prepare($thirdReq);
        $thirdExec->execute([
        ':finalPos' => $payload['newPosition'],
        ':idAction' => $payload['idAction'],
        ]);
    }
    
    /**
     * @return array<mixed>
    */
    public function getErrorOrderAction(string $typeObjet, int $idObjet, int $idTache)
    {
        $req = "select * from (
                select NUM_ORDRE, count(NUM_ORDRE) count_Order
                from DOA_ACTIONS with (nolock)
                where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_TACHE = :idTache
                group by NUM_ORDRE ) testOrder where count_Order > 1 ";
        
        $arrayReq = [
        ':typeObjet'    => $typeObjet,
        ':idObjet'      => $idObjet,
        ':idTache'          => $idTache
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    
    /* *************************************** */
    /* Requete Liens consultation/Projet achat */
    /**
     * @return array<mixed>
    */
    public function getValueByChamps(int $idObjet, string $codeChamps): ?array
    {
        $req = "select drv.VALEUR from DOA_DATA dd with (nolock)
                left join DOA_RECORD_VAL drv with (nolock) on drv.ID_DOA = dd.ID_OBJET and drv.ID_DATA = dd.ID_DATA and drv.ID_DONNEE = dd.ID_DONNEE
                where ID_OBJET = :idObjet and OBJET = :codeChamps";
        
        
        $arrayReq = [
        ':idObjet'      => $idObjet,
        ':codeChamps'      => $codeChamps
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function getValueByIdData(int $idObjet, int $idDonnee, string $champSearch, int $idRecord): ?array
    {
        $req = "select VALEUR from DOA_DATA dd with (nolock)
                left join DOA_RECORD_VAL drv with (nolock) on drv.ID_DOA = dd.ID_OBJET and drv.ID_DATA = dd.ID_DATA and drv.ID_DONNEE = dd.ID_DONNEE and ID_RECORD = :idRecord
                where dd.ID_OBJET = :idObjet and dd.ID_DONNEE = :idDonnee and CLE_UNIQUE = 0x54";
        
        $arrayReq = [
        ':idObjet'      => $idObjet,
        ':idDonnee'      => $idDonnee,
        ':idRecord'      => $idRecord,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    /* Requete pour vérifié l'existence d'un liens projet achat / consultation */
    public function checkLienExterneProjetExist(int $idAO, string $uniqueKey): ?array
    {
        $req = "SELECT ID_OBJET, dd.ID_DONNEE 
            FROM DOA_DATA dd with (nolock)
            LEFT JOIN DOA_RECORD_VAL drv with (nolock) 
                ON drv.ID_DOA = dd.ID_OBJET 
                AND drv.ID_DATA = dd.ID_DATA 
                AND drv.ID_DONNEE = dd.ID_DONNEE
            LEFT JOIN DOA with (nolock) on DOA.ID = dd.ID_OBJET
            WHERE drv.VALEUR = :idAO 
                AND dd.LIE_A_OBJET = 0x54 
                AND OBJET = '$uniqueKey' 
                AND DOA.STATUT = 'E'";

        $arrayReq = [
            ':idAO' => $idAO,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    public function deleteLiensExterne(int $idObjet, int $idDonnee) : void
    {
        $req = "delete from DOA_RECORD_VAL where ID_DOA = :idObjet and ID_DONNEE = :idDonnee and ID_DATA in (select ID_DATA from DOA_DATA with (nolock) where ID_DONNEE = DOA_RECORD_VAL.ID_DONNEE and LIE_A_OBJET = 0x54 )";
        
        $arrayReq = [
        ':idObjet'      => $idObjet,
        ':idDonnee'      => $idDonnee,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /* retourne la liste des donnée lié a un AO_FOUR */
    /**
     * @return array<mixed>
    */
    public function getListDataLie(int $idObjet, string $champSearch): ?array
    {
        $req = "select distinct(ID_DONNEE) from DOA_DATA with (nolock) where ID_OBJET = :idObjet and (LIE_A_OBJET = 0x54 and OBJET like '$champSearch.%')";
        
        $arrayReq = [
        ':idObjet' => $idObjet,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /* Requete pour récuperé les info de la structure de donnée ou est présent des champs AO_FOUR */
    /**
     * @return array<mixed>
    */
    public function getInfoStructLie(int $idObjet, int  $idDonnee): ?array
    {
        $req = "select LIE_A_OBJET, ID_DONNEE, OBJET from DOA_STRUCT with (nolock) 
                where ID_OBJET = :idObjet and ID_DONNEE = :idDonnee";
        
        $arrayReq = [
        ':idObjet'      => $idObjet,
        ':idDonnee'      => $idDonnee,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function getIdChampsForUpdate(int $idObjet, string $objet, string $uniqueKey, int $idDonnee = null): ?array
    {
        $req = "select ID_DATA,ID_DONNEE from DOA_DATA dd with (nolock) where dd.ID_OBJET = :idObjet and OBJET_DATA = '$objet' and (dd.LIE_A_OBJET = 0x54 and OBJET = '$uniqueKey')";
        
        $arrayReq = [
        ':idObjet'      => $idObjet,
        ];
        
        if ($idDonnee) {
            $req .=" and ID_DONNEE = :idDonnee";
            
            $arrayReq[':idDonnee'] = $idDonnee;
        }
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /*
     * Permet de récuperé la valeur d'un objet a supprimé dans une table externe
     * (exemple : suppresion d'un fournisseur dans un record, donne l'id du fournisseur supprimé pour le supprimé dans AO_FOUR)
    */
    /**
     * @return array<mixed>
    */
    public function getIdObjectExterne(int $idObjet, string $champSearch, int $idDonnee, int $idRecord = null, string $valeur = null)
    {
        $req = "SELECT VALEUR, ID_RECORD from DOA_DATA with(nolock)
                left join DOA_RECORD_VAL with(nolock) on ID_DOA = ID_OBJET and DOA_RECORD_VAL.ID_DONNEE = DOA_DATA.ID_DONNEE and DOA_DATA.ID_DATA = DOA_RECORD_VAL.ID_DATA 
                        ".($idRecord? " and ID_RECORD = :idRecord":"")." 
                        ".($valeur? " and VALEUR = :valeur":"")." 
                where ID_OBJET = :idObjet and LIE_A_OBJET = 0x54 and FLAG_ID_OBJET = 0x54 
                and DOA_DATA.ID_DONNEE = (select DISTINCT(ID_DONNEE) from DOA_DATA with(nolock) where ID_OBJET = DOA_DATA.ID_OBJET and OBJET like '$champSearch.%' and ID_DONNEE=:idDonnee)";
        
        $arrayReq = [
        ':idObjet'      => $idObjet,
        ':idDonnee'     => $idDonnee,
        ];
        
        if ($idRecord) {
            $arrayReq[':idRecord'] = $idRecord;
        }
        
        if ($valeur) {
            $arrayReq[':valeur'] = $valeur;
        }
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /*
     * Permet de mettre a blanc les champs qui étaient lié a une table secondaire
     * (exemple :suppresion d'un fournisseur sur une consultation )
    */
    public function suppresionLienTableSecondaire(int $idObjet, int $idDonnee, int $idKeyUnique, string $champSearch, string $codeUpdate) : void
    {
        $req = "update drv set drv.VALEUR = ''
                from DOA_RECORD_VAL drv
                left join DOA_DATA dd with (nolock) on drv.ID_DOA = dd.ID_OBJET and drv.ID_DATA = dd.ID_DATA and drv.ID_DONNEE = dd.ID_DONNEE
                where drv.ID_DOA = :idObjet and drv.ID_DONNEE = :idDonnee
                and ID_RECORD = (select ID_RECORD from DOA_RECORD_VAL drv2 where drv2.VALEUR = :codeUpdate and drv2.ID_DOA = drv.ID_DOA  and drv2.ID_DONNEE = drv.ID_DONNEE and drv2.ID_DATA = :idKeyUnique)
                and dd.OBJET like '$champSearch.%'";
        
        $arrayReq = [
        ':idObjet'      => $idObjet,
        ':idDonnee'     => $idDonnee,
        ':idKeyUnique'  => $idKeyUnique,
        ':codeUpdate'   => $codeUpdate,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function resetLinkObjectExternel(int $idObjet, string $champSearch) : void
    {
        $req = "update DOA_RECORD_VAL set VALEUR = '' 
                where ID_DOA = :idObjet and ID_DATA in (select ID_DATA from DOA_DATA where ID_OBJET = DOA_RECORD_VAL.ID_DOA and OBJET like '$champSearch.%')";

        $arrayReq = [
        ':idObjet'      => $idObjet,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListeExternalObject(int $idObjet, int $idDonnee)
    {
        $req = "select VALEUR from DOA_RECORD_VAL with (nolock)
                where ID_DOA = :idObjet and ID_DONNEE = :idDonnee and ID_DATA = (select ID_DATA from DOA_DATA with (nolock) where ID_DONNEE = DOA_RECORD_VAL.ID_DONNEE and ID_OBJET =DOA_RECORD_VAL.ID_DOA and LIE_A_OBJET = 0x54 and FLAG_ID_OBJET = 0x54)";
        
        $arrayReq = [
        ':idObjet'      => $idObjet,
        ':idDonnee'     => $idDonnee,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    /* *************************************** */
}
