<?php

namespace Modules\ProjetAchat\Repository;

use Helpers\QueryBuilder\AbsRepository;

class BibliothequeDonnee extends AbsRepository
{
    /** requete catégorie **/
    /**
     * @return array<mixed>
    */
    public function getListeCategorie(string $typeObjet, int $idObjet) : ?array
    {
        $req = "select ID, ID_PARENT, LIB, (select count(*) COUNT_STRUCT from DOA_STRUCT with (nolock) where ID_CAT = DOAR_CAT.ID) COUNT_STRUCT from DOAR_CAT with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet order by NUM_ORDRE";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];
        

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

    public function updateCategorie(int $id, int $idParent) : void
    {
        $req = "select ID, ID_PARENT, NUM_ORDRE from DOAR_CAT with (nolock) where ID = :idParent";
        
        $arrayReq = [
            ':idParent' => $idParent
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $resultParent = $requete->fetch($this->pdo::FETCH_ASSOC);
        

        
        $req = "select ID, ID_PARENT, NUM_ORDRE from DOAR_CAT with (nolock) where ID = :id";
        
        $arrayReq = [
            ':id' => $id
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $resultID = $requete->fetch($this->pdo::FETCH_ASSOC);
//         $additionner = 0;

//         if( $resultID['ID_PARENT'] != $resultParent['ID_PARENT'])
//         {
//             $req = "update DOAR_CAT set NUM_ORDRE = NUM_ORDRE+1 where ID_PARENT = :idParent and NUM_ORDRE>= :newNum";
        
//             $arrayReq = [
//                 ':idParent' => $resultParent['ID_PARENT'],
//                 ':newNum' => $resultParent['NUM_ORDRE']
//             ];
            
//             $requete = $this->pdo->prepare($req);
//             $requete->execute($arrayReq);



//             $req = "update DOAR_CAT set NUM_ORDRE = NUM_ORDRE-1 where ID_PARENT = :idParent and NUM_ORDRE>= :newNum";
        
//             $arrayReq = [
//                 ':idParent' => $resultID['ID_PARENT'],
//                 ':newNum' => $resultID['NUM_ORDRE']
//             ];
            
           
//         }else
//         {
            
//             $additionner = -1;
//             if($resultID['NUM_ORDRE']>$resultParent['NUM_ORDRE'])
//                 $req = "update DOAR_CAT set NUM_ORDRE = NUM_ORDRE-1 where ID_PARENT = :idParent and NUM_ORDRE< :newNumOld and NUM_ORDRE> :newNum";
//             else
//                 $req = "update DOAR_CAT set NUM_ORDRE = NUM_ORDRE-1 where ID_PARENT = :idParent and NUM_ORDRE> :newNumOld and NUM_ORDRE< :newNum";

//             $arrayReq = [
//                 ':idParent' => $resultID['ID_PARENT'],
//                 ':newNumOld' => $resultID['NUM_ORDRE'],
//                 ':newNum' => $resultParent['NUM_ORDRE']
//             ];
            
//         }

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

        $req = "update DOAR_CAT set ID_PARENT = :idParent, NUM_ORDRE = :newNum where ID = :id";
        
        $arrayReq = [
            ':id' => $id,
            ':idParent' => ($resultParent['ID_PARENT'] == $id)?$resultID['ID_PARENT']:$resultParent['ID_PARENT'],
            ':newNum' => $resultParent['NUM_ORDRE'],
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
     /*

        $req = "update DOAR_CAT set  NUM_ORDRE = :newNum where ID = :id";

        $arrayReq = [
            ':id' => $idParent,
            ':newNum' => $resultParent['NUM_ORDRE']-0.1,
        ];

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

*/
        $req ="update DOAR_CAT set NUM_ORDRE =TMP from DOAR_CAT
            INNER JOIN (
            SELECT ID_PARENT,ID,NUM_ORDRE,
            ROW_NUMBER() OVER (ORDER BY ID_PARENT,NUM_ORDRE) AS TMP
            FROM DOAR_CAT
            where TYPE_OBJET='B' and ID_PARENT = :ID_PARENT
            ) TMPTable ON TMPTable.ID = DOAR_CAT.ID
            where DOAR_CAT.ID_PARENT = :ID_PARENT1 and TYPE_OBJET='B'";

        $arrayReq = [
            ':ID_PARENT' =>$resultParent['ID_PARENT'],
            ':ID_PARENT1' => $resultParent['ID_PARENT'],
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        $req ="update DOAR_CAT set NUM_ORDRE =TMP from DOAR_CAT
        INNER JOIN (
        SELECT ID_PARENT,ID,NUM_ORDRE,
        ROW_NUMBER() OVER (ORDER BY ID_PARENT,NUM_ORDRE) AS TMP
        FROM DOAR_CAT
        where TYPE_OBJET='B' and ID_PARENT = :ID_PARENT
        ) TMPTable ON TMPTable.ID = DOAR_CAT.ID
        where DOAR_CAT.ID_PARENT = :ID_PARENT1 and TYPE_OBJET='B'";

        $arrayReq = [
        ':ID_PARENT' =>$resultID['ID_PARENT'],
        ':ID_PARENT1' => $resultID['ID_PARENT'],
        ];
    
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getHierarchyList(string $typeObjet, int $idObjet) : ?array
    {
        $req = "select ID_TACHE_H, ID_PARENT, LIB from DOA_TACHES_H with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet order by NUM_ORDRE";

        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function categorieAFille(string $typeObjet, int $idObjet, int $idCategorie): ?array
    {
        $req = "select * from DOAR_CAT with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_PARENT=:id";
        
        $arrayReq = [
            ':id' => $idCategorie,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function listeCategorieAFille(string $typeObjet, int $idObjet, int $idCategorie): ?array
    {
        $req = "select * from DOAR_CAT with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_PARENT=:id";
        
        $arrayReq = [
            ':id' => $idCategorie,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getInfoCategorie(string $typeObjet, int $idObjet, int $idCategorie): ?array
    {
        $req = "select *  from DOAR_CAT with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID= :id ";

        $arrayReq = [
            ':id' => $idCategorie,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
 
    /** requete Donnée **/
    /**
     * @return array<mixed>
    */
    public function getDonneByCategorie(string $typeObjet, int $idObjet, int $idCategorie): ?array
    {
        $req = "select *  from DOA_STRUCT with(nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_CAT=:id  order by NUM_ORDRE ASC";
        
        $arrayReq = [
            ':id' => $idCategorie,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /** requete champs **/
    /**
     * @return array<mixed>
    */
    public function getChampByDonnee(string $typeObjet, int $idObjet, int $idDonnee): ?array
    {
        $req = "select * from DOA_DATA with(nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_DONNEE=:id order by NUM_ORDRE ASC";
        
        $arrayReq = [
            ':id' => $idDonnee,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListeDonneOrderById(string $typeObjet, int $idObjet, int $idDonnee): ?array
    {
        $req = "select * from DOA_DATA with(nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_DONNEE=:id order by ID_DONNEE, ID_DATA";
        
        $arrayReq = [
            ':id' => $idDonnee,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getInfoData(string $typeObjet, int $idObjet, int $idDonne, int $idDonneLie): ?array
    {
        $req = "select * from DOA_DATA with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_DATA = :idLie and ID_DONNEE=:id";
        
        $arrayReq = [
            ':id' => $idDonneLie,
            ':idLie' => $idDonne,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function valeurChampsExiste(string $typeObjet, int $idObjet, int $idDonne, string $champs, string $valeur): ?array
    {
        $req = "select * from DOA_DATA with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_DONNEE=:id and $champs = :valeur";
        
        $arrayReq = [
            ':id' => $idDonne,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':valeur' => $valeur,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
    */
    public function valeurDataExiste(string $typeObjet, int $idObjet, int $idCat, string $champs, string $valeur): ?array
    {
        $req = "select * from DOA_STRUCT with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_CAT=:id and $champs = :valeur";
        
        $arrayReq = [
            ':id' => $idCat,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':valeur' => $valeur,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function objetExist(string $typeObjet, int $idObjet, int $idDonne, string $objet): ?array
    {
        $req = "select * from DOA_DATA with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_DONNEE=:id and DATA_LIE_A_OBJ=0x54 and LIE_A_OBJET=0x46 and ID_DATA_LIEE='0' and OBJET_DATA=:objet";
        
        $arrayReq = [
            ':id' => $idDonne,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':objet' => $objet,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function maxOrderChamps(string $typeObjet, int $idObjet, int $idDonne): ?array
    {
        $req = "select MAX(NUM_ORDRE) NUM_ORDRE from DOA_DATA with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_DONNEE=:id ";
        $arrayReq = [
            ':id' => $idDonne,
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function champUtilise(int $idChamp) : ?array
    {
        $req = "select * from DOA_DATA with (nolock) WHERE TYPE_OBJET in ('M', 'D') and ID_STRUCT = :idChamp";
        
        $arrayReq = [
            ':idChamp' => $idChamp,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function categorieUtilise(int $idCategorie): ?array
    {
        $req = "select * from DOA_STRUCT with (nolock) where TYPE_OBJET in ('D', 'M') and ID_STRUCT in (select ID_DONNEE from DOA_STRUCT WITH (NOLOCK) where ID_CAT=:idCategorie)";
        
        $arrayReq = [
            ':idCategorie' => $idCategorie,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function dataUtilise(int $idData): ?array
    {
        $req = "select * from DOA_STRUCT with (nolock) WHERE TYPE_OBJET in ('M', 'D') and ID_STRUCT = :idData";
        
        $arrayReq = [
            ':idData' => $idData,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function donneeUtilise(int $idDonnee): ?array
    {
        $req = "select * from DOA_DATA with (nolock) WHERE TYPE_OBJET in ('M', 'D') and ID_STRUCT = :idDonnee";
        
        $arrayReq = [
            ':idDonnee' => $idDonnee,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function getInfoChamps(int $idChamp): ?array
    {
        $req = "select DOA_DATA.TYPE_OBJET, DOA_DATA.ID_OBJET, DOA_STRUCT.ID_CAT, DOA_DATA.ID_DONNEE, DOA_DATA.NOM_DATA from DOA_DATA with (nolock) 
                left join DOA_STRUCT with (nolock) ON DOA_DATA.ID_DONNEE = DOA_STRUCT.ID_DONNEE
                where ID_DATA=:idChamp";
        
        $arrayReq = [
            ':idChamp' => $idChamp,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    // /**
    //  * @return array<mixed>
    // */
    // public function deleteHeritage(int $idChamp, bool $modele = false, bool $projet = false): bool
    // {
    //     if (!$modele && !$modele) {
    //         return false;
    //     }

    //     $where = ' and ( ';
    //     if ($modele) {
    //         $where .= " TYPE_OBJET='M' ";
    //     }
    //     if ($projet) {
    //         if ($modele) {
    //             $where .= " OR ";
    //         }
            
    //         $where .= " (TYPE_OBJET='D' and ID_OBJET in (select DISTINCT ID_OBJET from DOA_DATA with(nolock) left join DOA with(nolock) on DOA.ID = DOA_DATA.ID_OBJET where STATUT in ('I', 'P') and TYPE_OBJET='D')) ";
    //     }
    //     $where .= ')';
        
    //     $req = "delete DOA_DATA WHERE ID_STRUCT = :idChamp ".$where;
        
    //     $arrayReq[':idChamp'] = $idChamp;
    //     $requete = $this->pdo->prepare($req);
    //     return $requete->execute($arrayReq);
    // }
   
    // /**
    //  * updateHeritage
    //  * @param int $idChamp
    //  * @param bool $modele
    //  * @param bool $projet
    //  * @param array<mixed> $valueUpdate
    //  * @return array<mixed>
    //  */
    // public function updateHeritage(int $idChamp, bool $modele = false, bool $projet = false, array $valueUpdate = []): ?array
    // {
    //     if (!$modele && !$modele) {
    //         return [];
    //     }
        
    //     $value = "";
    //     $i = 0;
    //     $val = '';
        
    //     foreach ($valueUpdate as $key => $value) {
    //         if (strpos($key, 'numerique_')===0) {
    //             $key = substr($key, 10);
    //             if ($i == 0) {
    //                 $val = $key.' = '.$value;
    //             } else {
    //                 $val .= ', '.$key.' = '.$value;
    //             }
    //         } else {
    //             if ($i == 0) {
    //                 $val = $key.' = :value'.$i;
    //             } else {
    //                 $val .= ', '.$key.' = :value'.$i;
    //             }
                
    //             $arrayUpdate[':value'.$i] = $value;
    //             $i++;
    //         }
    //     }
        
    //     $arrayUpdate[':id'] = $idChamp;
        
    //     $where = 'and (';
    //     if ($modele) {
    //         $where .= "TYPE_OBJET='M'";
    //     }
    //     if ($projet) {
    //         if ($modele) {
    //             $where .= "OR";
    //         }
            
    //         $where .= " (TYPE_OBJET='D' and ID_OBJET in (select DISTINCT ID_OBJET from DOA_DATA with(nolock) left join DOA with(nolock) on DOA.ID = DOA_DATA.ID_OBJET where STATUT in ('I', 'P') and TYPE_OBJET='D'))";
    //     }
    //     $where .= ')';
        
    //     $req = "UPDATE DOA_DATA SET ".$val." WHERE ID_STRUCT = :id ".$where;
        
    //     $requete = $this->pdo->prepare($req);
    //     return $requete->execute($arrayUpdate);
    // }

    /**
     * Reorder table data
     */
    /**
     * @param array<string> $payload
    */
    public function reorderFields(array $payload): void
    {
        $firstReq = "UPDATE DOA_DATA
                    SET NUM_ORDRE = 0
                    WHERE NUM_ORDRE = :initialPos AND ID_DONNEE = :idData";

        $firstExec = $this->pdo->prepare($firstReq);
        $firstExec->execute([
            ':initialPos' => $payload['initialPosition'],
            ':idData' => $payload['idData'],
        ]);

        if ("+" === $payload['sign']) {
            $secondReq = "UPDATE DOA_DATA
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :finalPos AND  NUM_ORDRE <= :initialPos AND ID_DONNEE = :idData";
        } else {
            $secondReq = "UPDATE DOA_DATA
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :initialPos AND  NUM_ORDRE <= :finalPos AND ID_DONNEE = :idData";
        }

        $secondExec = $this->pdo->prepare($secondReq);
        $secondExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':initialPos' => $payload['initialPosition'],
            ':idData' => $payload['idData'],
        ]);

        $thirdReq = "UPDATE DOA_DATA
                    SET NUM_ORDRE = :finalPos
                    WHERE NUM_ORDRE = 0 AND ID_DONNEE = :idData";

        $thirdExec = $this->pdo->prepare($thirdReq);
        $thirdExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':idData' => $payload['idData'],
        ]);
    }

    public function reorderDatas($payload)
    {
        $firstReq = "UPDATE DOA_STRUCT
                    SET NUM_ORDRE = 0
                    WHERE NUM_ORDRE = :initialPos AND ID_CAT = :idData";

        $firstExec = $this->pdo->prepare($firstReq);
        $firstExec->execute([
            ':initialPos' => $payload['initialPosition'],
            ':idData' => $payload['idData'],
        ]);

        if ("+" === $payload['sign']) {
            $secondReq = "UPDATE DOA_STRUCT
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :finalPos AND  NUM_ORDRE <= :initialPos AND ID_CAT = :idData";
        } else {
            $secondReq = "UPDATE DOA_STRUCT
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :initialPos AND  NUM_ORDRE <= :finalPos AND ID_CAT = :idData";
        }

        $secondExec = $this->pdo->prepare($secondReq);
        $secondExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':initialPos' => $payload['initialPosition'],
            ':idData' => $payload['idData'],
        ]);

        $thirdReq = "UPDATE DOA_STRUCT
                    SET NUM_ORDRE = :finalPos
                    WHERE NUM_ORDRE = 0 AND ID_CAT = :idData";

        $thirdExec = $this->pdo->prepare($thirdReq);
        $thirdExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':idData' => $payload['idData'],
        ]);
    }
    
    /**
     * @param array<string> $payload
    */
    public function reorderTasks(array $payload): void
    {
        $firstReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = 0
                    WHERE NUM_ORDRE = :initialPos AND ID_TACHE_H = :idData";
        
        $firstExec = $this->pdo->prepare($firstReq);
        $firstExec->execute([
            ':initialPos' => $payload['initialPosition'],
            ':idData' => $payload['idData'],
        ]);
        
        if ("+" === $payload['sign']) {
            $secondReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :finalPos AND  NUM_ORDRE <= :initialPos AND ID_TACHE_H = :idData";
        } else {
            $secondReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :initialPos AND  NUM_ORDRE <= :finalPos AND ID_TACHE_H = :idData";
        }
        
        $secondExec = $this->pdo->prepare($secondReq);
        $secondExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':initialPos' => $payload['initialPosition'],
            ':idData' => $payload['idData'],
        ]);
        
        $thirdReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = :finalPos
                    WHERE NUM_ORDRE = 0 AND ID_TACHE_H = :idData";
        
        $thirdExec = $this->pdo->prepare($thirdReq);
        $thirdExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':idData' => $payload['idData'],
        ]);
    }
    
    /**
     * @param array<string> $payload
    */
    public function reorderActions(array $payload): void
    {
        $firstReq = "UPDATE DOA_ACTIONS
                    SET NUM_ORDRE = 0
                    WHERE NUM_ORDRE = :initialPos AND ID_TACHE = :idData";
        
        $firstExec = $this->pdo->prepare($firstReq);
        $firstExec->execute([
            ':initialPos' => $payload['initialPosition'],
            ':idData' => $payload['idData'],
        ]);
        
        if ("+" === $payload['sign']) {
            $secondReq = "UPDATE DOA_ACTIONS
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :finalPos AND  NUM_ORDRE <= :initialPos AND ID_TACHE = :idData";
        } else {
            $secondReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :initialPos AND  NUM_ORDRE <= :finalPos AND ID_TACHE = :idData";
        }
        
        $secondExec = $this->pdo->prepare($secondReq);
        $secondExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':initialPos' => $payload['initialPosition'],
            ':idData' => $payload['idData'],
        ]);
        
        $thirdReq = "UPDATE DOA_ACTIONS
                    SET NUM_ORDRE = :finalPos
                    WHERE NUM_ORDRE = 0 AND ID_TACHE = :idData";
        
        $thirdExec = $this->pdo->prepare($thirdReq);
        $thirdExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':idData' => $payload['idData'],
        ]);
    }

    /**
     * Fonction retourntant la liste des objet disponible a la création
     */
    
    /**
     * @return array<array<mixed>>
    */
    public function getListeObjet(bool $donnee = false): ?array
    {
        $param = [];
        $req = "SELECT 
            " . $this->selectTrad('tmp.LIBELLE') . ",
            tmp.BDD_TABLE
            FROM ( 
                    SELECT 
                        CS_TYPES.LIBELLE,
                        CS_TYPES.TYPE as BDD_TABLE 
                    FROM CS_TSTD WITH (NOLOCK) 
                    LEFT JOIN CS_TYPES with(nolock) ON CS_TSTD.COD_TAB = CS_TYPES.TYPE
                    WHERE  TAB_CTRL = 0x54
                        AND CTRL_STD=0x54 
                        AND CS_TSTD.COD_TAB IN ('ACD','ARTICLES','CENTRE','CENTRE_R','CENTRE_E','CENTRE_F','CONDPAIE','COUNTRY','DEVISES','FAMILLE','FOURNISS','GRPFOU','HASTUTIL','MARCHES','MODELIVR','MODEPAIE','SOCIETE','SSFAMILL','SSITESTO','TYPDOC','TYPETVA','UNITES','FABRICAN','SIGBAP','SIGCDE','SIGDA','ACHETEUR','DEMANDEU','SECTIMPU','SSCPTANA','CODECOMP','CENTRANA','DIRECTIO','UO','LANGUES')
                UNION 
                    SELECT 
                        CS_TYPES.LIBELLE,
                        CS_TYPES.TYPE as BDD_TABLE 
                        FROM CS_TYPES with (nolock)
                        WHERE CONTROLE=0x54 AND CTRL_STD=0x46 
                ".($donnee? "UNION SELECT 'Consultation', 'AO_ENTET'" : "")."
            ) AS tmp
            " . $this->joinTrad('tmp.LIBELLE', $param) . "
            ORDER BY upper(LIBELLE)";

        return $this->fetchAssocAll($req, $param);
    }
    
    /**
     * @return array<mixed>
    */
    public function listeFormat(int $idRegroupement): ?array
    {
        $req = "select * from DOAR_B_FMT_DOC with (nolock) where ID_H=:id";
        
        $arrayReq = [
            ':id' => $idRegroupement,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListDataLink(string $typeObjet, int $idObjet): ?array
    {
        $req = "select * from DOA_STRUCT with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet AND ID_STRUCT != '0'";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function infoDataByStruct(string $typeObjet, int $idObjet, int $idData): ?array
    {
        $req = "select * from DOA_STRUCT with(nolock) where TYPE_OBJET = :typeObjet and  ID_OBJET = :idObjet and ID_STRUCT = :idDonnee ";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':idDonnee' => $idData
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function infoData(string $typeObjet, int $idObjet, int $idData): ?array
    {
        $req = "select * from DOA_STRUCT with(nolock) where TYPE_OBJET = :typeObjet and  ID_OBJET = :idObjet and ID_DONNEE = :idDonnee ";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':idDonnee' => $idData
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @return array<mixed>
    */
    public function rqDOAgetCatLibelleSansFils(string $typeObjet, int $idObjet, string $lib): ?array
    {
        $req = "select * from DOAR_CAT with (nolock) where TYPE_OBJET = :typeObjet AND ID_OBJET = :idObjet AND LIB = :lib 
                /*AND NOT EXISTS (select null from DOAR_CAT RECH with (nolock) WHERE RECH.TYPE_OBJET = DOAR_CAT.TYPE_OBJET  AND   RECH.ID_OBJET   = DOAR_CAT.ID_OBJET AND   RECH.ID_PARENT  = DOAR_CAT.ID)*/";
    
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':lib' => $lib
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
    */
    public function rqDOATrouveDonneeDOA(string $typeObjet, int $idObjet, int $idStruct, string $nomData, string $lib): ?array
    {
        $req = "select * from DOAR_CAT with(nolock) 
                left outer join DOA_STRUCT with(nolock) on DOAR_CAT.TYPE_OBJET=DOA_STRUCT.TYPE_OBJET and DOAR_CAT.ID_OBJET=DOA_STRUCT.ID_OBJET and DOAR_CAT.ID=DOA_STRUCT.ID_CAT 
                where DOAR_CAT.TYPE_OBJET = :typeObjet and DOAR_CAT.ID_OBJET = :idObjet and  DOAR_CAT.LIB = :lib and  DOA_STRUCT.NOM_DONNEE = :nomData and  DOA_STRUCT.ID_STRUCT = :idStruct";
        
        $arrayReq = [
            ':typeObjet'    => $typeObjet,
            ':idObjet'      => $idObjet,
            ':lib'          => $lib,
            ':nomData'      => $nomData,
            ':idStruct'     => $idStruct
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
    */
    public function getErrorOrderStruct(string $typeObjet, int $idObjet, int $idCategorie): ?array
    {
        $req = "select * from (
                select NUM_ORDRE, count(NUM_ORDRE) count_Order 
                from DOA_STRUCT with (nolock)
                where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_CAT = :idCat
                group by NUM_ORDRE ) testOrder where count_Order > 1 ";
        
        $arrayReq = [
            ':typeObjet'    => $typeObjet,
            ':idObjet'      => $idObjet,
            ':idCat'          => $idCategorie
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
}
