<?php

namespace Helpers\ChampsSupplementaire;

use Entity\Model\CsDon;
use Entity\Model\OxaUserPanierCsDon;
use Exception;
use Helpers\Outil\Date;
use Helpers\Outil\MemoizeTrait;
use Helpers\Outil\Sql;
use Helpers\Parametres\Constante;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Trad\ITrad;
use Helpers\Trad\Trad;
use Skeleton\Router;

class ChampSupp extends AbsRepository
{
    
    use MemoizeTrait;

    /**
     * permet de récuperé la liste des champs supplémentaire (par defaut d'une table, cependant on peu récupere les valeurs du cs, ou spécifié un cs)
     *
     * @param string $codTab
     * @param string $codEnr
     * @param string|int $numFixe
     * @param string $codTableDifferent
     * @param bool $getAll
     * @param bool $postVal
     * @param bool $nonDispo
     * @return array<array<string>>
     */
    public function generateChampSupp(
        $codTab,
        $codEnr = null,
        $numFixe = null,
        $codTableDifferent = '',
        $getAll = false,
        $postVal = null,
        $nonDispo = false,
        ?string $idLigne = null,
        bool $OnlyNotNull = false,
        bool $modeValidation = false,
        bool $onlyNonDispo = false
    ) : array {
        $codTab = preg_replace('/[^0-9A-z_]/', '', $codTab);
        
        $arrayReq = [];
        if ($codEnr!='') {
            $codEnr = trim(addslashes($codEnr));
            switch ($codTableDifferent) {
                case 'FAC_LIG_TRAVAIL':
                case 'BR_OXAPROC':
                    $value =  " , CS_DON.VALEUR as VALUECHAMPSUPP";
                    break;
                default:
                    $value =  " , case when CS_TYPES.FORMAT='M' then (select CONTMEMO from MEMOS with (nolock) where NODEMEMO=cast(CS_DON.VALEUR as int)) else CS_DON.VALEUR end as VALUECHAMPSUPP";
                    break;
            }
        }

        if ($codTableDifferent == 'PANIER') {
            $value =  " , oxa_users_panier_CS_DON.VALEUR as VALUECHAMPSUPP";
        } if (!$codTableDifferent) {
            $codTableDifferent = $codTab;
        }

        $req = "SELECT ";
        if ($getAll) {
            $req .="CS_DON.COD_ENR ,";
        }

        $req .="
                NONMODIF,
                NONDUPLI,
                MODIFSIGN,
                NONDISPO,
                CS_CHPS.COD_TAB,
                CS_CHPS.NUM_FIX,
                CS_CHPS.NUM_VAR,
                CS_CHPS.TYPE,
                CS_CHPS.MODAPVAL,
                " . $this->selectTrad('CS_CHPS.NOM', null, 'TXTTRAD') . ",
                CS_CHPS.OBLIG,
                CS_CHPS.TYPE,
                CS_TYPES.FORMAT,
                CS_TYPES.TBLSECTO,
                CS_TYPES.ALP_LONG,
                CS_TYPES.PE_LONG,
                CS_TYPES.PD_LONG,
                CS_TYPES.CONTROLE,
                CSPARINI.TAB_SOUR,
                CSPARINI.CHP_SOUR,
                CS_TYPES.CTRL_STD,
                (CASE WHEN CS_CHPS_TRAD.DESCRIPTION IS NOT NULL 
                    THEN CS_CHPS_TRAD.DESCRIPTION
                    ELSE desc_cs_default.DESCRIPTION
                END) AS DESCRIPTION
                ".($value??'')." 
            FROM CS_CHPS WITH (NOLOCK)
            " . $this->joinTrad('CS_CHPS.NOM', $arrayReq) . "
            LEFT JOIN CS_TYPES WITH (NOLOCK) ON CS_TYPES.TYPE=CS_CHPS.TYPE
            LEFT JOIN CSPARINI WITH (NOLOCK) ON CSPARINI.TAB_DESTI = CS_CHPS.COD_TAB AND CSPARINI.CHP_DESTI = CS_CHPS.NUM_FIX
            LEFT JOIN CS_CHPS_TRAD WITH (NOLOCK) 
                ON CS_CHPS_TRAD.COD_TAB = CS_CHPS.COD_TAB 
                AND CS_CHPS_TRAD.NUM_FIX = CS_CHPS.NUM_FIX
                AND CS_CHPS_TRAD.COD_LNG = :lang
            -- si pas de résultat dans la langue de session, on prend la valeur par défaut
            LEFT JOIN CS_CHPS_TRAD desc_cs_default WITH (NOLOCK) 
                ON desc_cs_default.COD_TAB = CS_CHPS.COD_TAB 
                AND desc_cs_default.NUM_FIX = CS_CHPS.NUM_FIX
                AND desc_cs_default.COD_LNG = ''
        ";
        $arrayReq[':lang'] = $_SESSION['Session_Langue'];

        if ($codEnr) {
            $req .=" LEFT JOIN CS_DON WITH (NOLOCK) ON CS_DON.COD_TAB=:codTableDifferent AND CS_DON.NUM_FIX=CS_CHPS.NUM_FIX ";
            if (!$getAll) {
                $req .=" AND CS_DON.COD_ENR = :codEnr ";
                $arrayReq[':codEnr'] = $codEnr;
            }
                    
            $arrayReq[':codTableDifferent'] = $codTableDifferent;
        } elseif ($codTableDifferent == 'PANIER') {
            $req .=" LEFT JOIN oxa_users_panier_CS_DON WITH (NOLOCK) 
                        ON oxa_users_panier_CS_DON.COD_TAB = :paniercodTab
                        AND oxa_users_panier_CS_DON.NUM_FIX = CS_CHPS.NUM_FIX 
                        AND oxa_users_panier_CS_DON.ID_LIGNE = :idLigne 
                        AND oxa_users_panier_CS_DON.id_user = :idUser
                        AND oxa_users_panier_CS_DON.id_panier = :idPanier
                ";

            $arrayReq[':paniercodTab'] = $codTab;
            $arrayReq[':idUser'] = $_SESSION['id_user'];
            $arrayReq[':idPanier'] = $_SESSION['id_panier'];
            $arrayReq[':idLigne'] =  $codTab=='DA_LIGNE'?$idLigne:0;
        }
                
        $req .=" WHERE 1=1 ";

        if ($codTableDifferent == 'PANIER') {
            $req .=" AND CS_CHPS.COD_TAB = :value ";
            $arrayReq[':value'] = $codTab;
        } elseif ($codTab) {
            $req .=" AND CS_CHPS.COD_TAB = :value ";
            $arrayReq[':value'] = $codTab;
        }

        if ($onlyNonDispo) {
            $req .=" AND ( CS_CHPS.NONDISPO = ".SQL::setBoolSql(1)." )" ;
        } elseif ($modeValidation) {
            $req .=" AND ( CS_CHPS.NONDISPO = ".SQL::setBoolSql(0)."  or CS_TYPES.FORMAT = 'L' )" ;
        } elseif ($nonDispo === false) {
            $req .=" AND ( CS_CHPS.NONDISPO = ".SQL::setBoolSql(0)." )" ;
        }

        if ($numFixe) {
            $req .= ' AND CS_CHPS.NUM_FIX = :numfix ';
            $arrayReq[':numfix'] = $numFixe;
        }

        if ($postVal !== null) {
            $req .=  " AND CS_CHPS.MODAPVAL = ".SQL::setBoolSql(1);
        }

        if ($OnlyNotNull && $codEnr) {
            $req .= " and CS_DON.VALEUR != '' ";
        }

        if ($codTableDifferent == 'PANIER' && $codTab=='DA_ENTET') {
            $req .= " ORDER BY OBLIG DESC,NUM_VAR ";
        } else {
            $req .= " ORDER BY NUM_VAR ";
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return bool
     */
    public function hasCommandeModApVal(string $numCa): bool
    {
        return intval(
            $this->fetchAssocOne(
                "SELECT COUNT(*) as counter
                FROM CS_CHPS with (nolock)
                JOIN CS_DON with (nolock) 
                    ON (
                        CS_DON.COD_TAB = 'CA_ENTET' 
                        AND CS_DON.NUM_FIX = CS_CHPS.NUM_FIX 
                        AND CS_DON.COD_ENR = 
                            RIGHT('000000000' + CAST(:numEnr_0 as varchar), 9)         
                    )
                    OR (
                        CS_DON.COD_TAB = 'CA_LIGNE' 
                        AND CS_DON.NUM_FIX = CS_CHPS.NUM_FIX 
                        AND CS_DON.COD_ENR LIKE 
                            RIGHT('000000000' + CAST(:numEnr_1 as varchar), 9) 
                            + '%'
                    )
                WHERE CS_CHPS.COD_TAB IN ('CA_ENTET','CA_LIGNE')
                    AND CS_CHPS.NONDISPO = ".Sql::FALSE." 
                    AND CS_CHPS.MODAPVAL = ".Sql::TRUE,
                [
                    ':numEnr_0' => $numCa,
                    ':numEnr_1' => $numCa,
                ]
            )['counter'] ?? 0
        ) != 0;
    }
    
    /**
     * Insert une valeur pour un CS
     *
     * @param string $codTab
     * @param string $codEnr
     * @param string $numFixe
     * @param string $value
     * @param \PDO $pdo
     */
    public static function insertChampSupp($codTab, $codEnr, $numFixe, $value, $pdo) :bool
    {
        if ($codTab == 'FAC_LIG_TRAVAIL') {
            $codTabParam = 'FAC_LIG';
        } else {
            $codTabParam = $codTab;
        }

        $retour = self::getFormat($pdo, $codTab, $numFixe);
        
        if ($retour=='M' && $codTab != 'FAC_LIG_TRAVAIL') {
            $value = sql::updateMemo($pdo, '', $value);
        } elseif ($retour=='L') {
            $value = Sql::toBool($value) ? 'O':'N';
        } elseif ($retour=='D') {
            Date::dateToSql($value);
            $value = Date::dateConverte($value, 'jj/mm/aaaa');
        }
        
        $req="INSERT INTO CS_DON (COD_TAB,  NUM_FIX, COD_ENR, VALEUR) 
            VALUES (:entity_code,:numfix, :code_id, :myValue)";
        
        $arrayReq = [
            ':entity_code' => $codTab,
            ':code_id' => $codTab == 'DOA' ? $codEnr = str_pad($codEnr, 9, '0', STR_PAD_LEFT) : $codEnr,
            ':myValue' => $value,
            ':numfix' => $numFixe,
        ];
        
        $request = $pdo->prepare($req);
        return $request->execute($arrayReq);
    }
    
    /**
     * fonction récuperant la valeur d'un CS
     * @param string $codTab
     * @param string $codEnr
     * @param string $numFixe
     * @param \PDO $pdo
     */
    public static function getValueCS($codTab, $codEnr, $numFixe, $pdo) :string
    {
        $req = "SELECT * FROM CS_DON with(nolock) WHERE COD_TAB=:entity_code AND COD_ENR=:code_id AND NUM_FIX=:num_fix";
        
        $arrayReq = [
            ':entity_code'  => $codTab,
            ':code_id'      => $codEnr,
            ':num_fix'      => $numFixe
        ];
        
        $request = $pdo->prepare($req);
        $request->execute($arrayReq);
        
        return $request->fetch()['VALEUR']??'';
    }
    
    /**
     * Permet de mettre a jours une valeur d'un CS
     * On vérifie l'existence du CS, si il existe on update, autrement on créer
     *
     * @param string $codTab
     * @param string $codEnr
     * @param string $numFixe
     * @param string $value
     * @param \PDO $pdo
     * @return bool
     */
    public static function updateChampSupp($codTab, $codEnr, $numFixe, $value, $pdo)
    {
        $req = "SELECT COD_TAB, COD_ENR, VALEUR FROM CS_DON WHERE COD_TAB=:value1 AND COD_ENR=:value2 AND NUM_FIX=:value3";
        $arrayReq = [
            ':value1' => $codTab,
            ':value2' => $codTab == 'DOA' ? $codEnr = str_pad($codEnr, 9, '0', STR_PAD_LEFT) : $codEnr,
            ':value3' => $numFixe,
        ];

        
        $request = $pdo->prepare($req);
        $request->execute($arrayReq);
        $exist =  $request->fetch();
        
        if ($exist) {
            if ($codTab == 'FAC_LIG_TRAVAIL') {
                $codTabParam = 'FAC_LIG';
            } elseif ($codTab == 'BR_OXAPROC') {
                $codTabParam = 'BR_RECEP';
            } else {
                $codTabParam = $codTab;
            }

            $valReq = self::getFormat($pdo, $codTabParam, $numFixe);

            if ($valReq=='M' && $codTab != 'BR_OXAPROC'  && $codTab != 'FAC_LIG_TRAVAIL') {
                $value = sql::updateMemo($pdo, $exist['VALEUR'], $value);
                $retourSQL = true;
            } elseif ($valReq=='L') {
                $value = Sql::toBool($value) ? 'O':'N';
            } elseif ($valReq=='D') {
                Date::dateToSql($value);
                $value = Date::dateConverte($value, 'jj/mm/aaaa');
            }
            
            $req = "UPDATE CS_DON set VALEUR =:myValue where COD_TAB =:value1 and COD_ENR =:value2 and NUM_FIX = :numFix";
            $arrayReq = [
                ':value1' => $codTab,
                ':value2' => $codTab == 'DOA' ?
                    $codEnr = str_pad($codEnr, 9, '0', STR_PAD_LEFT) : $codEnr,
                ':myValue' => $value,
                ':numFix' => $numFixe
            ];
            $request = $pdo->prepare($req);
            $retourSQL = $request->execute($arrayReq);
        } else {
            $insert = new ChampSupp($pdo);
            $retourSQL = $insert->insertChampSupp($codTab, $codEnr, $numFixe, $value, $pdo);
        }

        return $retourSQL;
    }
    
    /**
     * Suppression des CS pour un enregistrement
     *
     * @param string $codTab
     * @param string $codEnr
     * @param mixed $idFixe
     * @param \PDO $pdo
     */
    public static function deleteCS($codTab, $codEnr, $pdo, $idFixe = null) :bool
    {
        $req = "DELETE from CS_DON where COD_TAB = :codTab AND COD_ENR = :codEnr";
        
        $arrayReq = [
            ':codTab' => $codTab,
            ':codEnr' => $codTab == 'DOA' ? $codEnr = str_pad($codEnr, 9, '0', STR_PAD_LEFT) : $codEnr,
        ];
        
        if ($idFixe) {
            $req .=" and NUM_FIX = :numFix";
            $arrayReq = [
                ':numFix' => $idFixe,
            ];
        }

        $param = $pdo->prepare($req);
        return $param->execute($arrayReq);
    }
    
    /**
     *
     * requete permetant de récuperer les valeurs disponibles pour un CS de type select
     *
     * @param string $TYPE
     * @param array<mixed> $post
     * @param boolean $count
     * @param float|int|string|null $date
     * @return array<mixed>
     */
    public function generateCSSelect($TYPE, $post = [], $count = false, $date = 0)
    {
        if (!$date) {
            $date = date('Ymd');
        } else {
            $date = (string) preg_replace('/[^0-9]/', '', (string)$date);
        }
        $arrayReq = [':value' => $TYPE];
        
        $select = "";
        if ($count) {
            $select = "TOP 1";
        }

        $req = "select * from (select $select TYPEDICO, COD_STE, CODEDICO, LIB01DICO from TS_DON with(nolock)  
            where TS_DON.TYPEDICO = :value 
            AND DATE_MIN<='".$date."' AND (DATE_MAX>='".$date."' OR DATE_MAX='00000000')";
            
        if (isset($post['CODEDICO']) && trim($post['CODEDICO']) != '') {
            $req .= " AND CODEDICO = :codedico";
            $arrayReq[':codedico'] = $post['CODEDICO'];
        }

        if (isset($post['COD_STE']) && trim($post['COD_STE']) != '') {
            $req .= " AND COD_STE = :codeSte";
            $arrayReq[':codeSte'] = $post['COD_STE'];
        }
        
        $req .= ") CS";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        if (!$count) {
            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        }
        
        if ($count) {
            return $this->fetchAssocOne($req, $arrayReq);
        } else {
            return $this->fetchAssocAll($req, $arrayReq);
        }
    }
    
    public static function convertDateToCS(mixed $dateTMP) :string
    {
        Date::dateToSql($dateTMP);
        
        $year=substr($dateTMP, 0, 4);
        $month=substr($dateTMP, 4, 2);
        $day=substr($dateTMP, 6, 2);
        
        return $day.'/'.$month.'/'.$year;
    }
    
    /**
     * @return string[][]
     */
    public static function & getListeTableStd()
    {
            // key              //entity    //liste
        $array = array(
            'ACD'       => array('Acccadre', 'accadre'),
            'ARTICLES'  => array('Articles', 'articles'),
            'CENTRE'    => array('Centre', 'centre'),
            'CENTRE_R'  => array('Centre', 'centreLivraison'),
            'CENTRE_E'  => array('Centre', 'centreEmetteur'),
            'CENTRE_F'  => array('Centre', 'centre'),
            
            'CONDPAIE'  => array('CondPaie', 'conditionPaiement'),
            'COUNTRY'   => array('Country', 'pays'),
            'DEVISES'   => array('Devise', 'devise'),
            'DECOUPAG'  => array('DecoupageGeographique', 'decoupage'),
            'DOMFONCT'  => array('DomaineFonctionnel', 'domaineFonctionnel'),
            'FAMILLE'   => array('Famille', 'famille'),
            'FOURNISS'  => array('Fourniss', 'fournisseur'),
            'GRPFOU'    => array('Grpfou', 'grpfou'),
            'HASTUTIL'  => array('Hastutil', 'utilisateur'),
            'MARCHES'   => array('Marches', 'marches'),
            'TYPMARCH'  => array('TypeDeMarche', 'typeDeMarche'),
            'MODELIVR'  => array('Modelivr', 'modelivraison'),
            'MODEPAIE'  => array('Modepaie', 'modePaiement'),
            'SOCIETE'   => array('Societe', 'societe'),
            'SSFAMILL'  => array('SousFamille', 'sousFamille'),
            'SSITESTO'  => array('SsItesto', 'ssItesto'),
            'TYPDOC'    => array('TypeDoc', 'typeDoc'),
            'TYPETVA'   => array('TypeTVA', 'typeTVA'),
            'UNITES'    => array('Unites', 'unites'),
            'FABRICAN'  => array('Fabricant', 'fabricant'),
            'LANGUES'   => array('Langues', 'langues'),
            'NATANA'    => array('Natana', 'natana'),

            
            // Table avec nécessite d'un filtre sur attribut
            'SIGBAP'    => array('Hastutil', 'sigBAP' ,'filter_SIG_BAP'),
            'SIGCDE'    => array('Hastutil', 'sigCde' ,'filter_SIG_CDE'),
            'SIGDA'     => array('Hastutil', 'sigDA' ,'filter_SIG_DA'),
            'ACHETEUR'  => array('Hastutil', 'acheteurUser' ,'filter_ACHETEUR'),
            'DEMANDEU'  => array('Hastutil', 'demandeur' ,'filter_DEMAND'),
            'ARTFRPORT'  => array('Articles', 'articles' ,'filter_ARTFRAISPORT'),
            
            // Fin Filtre
            
            'SECTIMPU'  => array('Sectimpu','si'),
            'SSCPTANA'  => array('Sscptana','sca'),
            'CODECOMP'  => array('Codecomp','cdc'),
            'CENTRANA'  => array('Centrana','centrana'),
            'DIRECTIO'  => array('Directio','directio'),
            'UO'        => array('UO', 'uo'),
            
            'AO_ENTET' => array('AoEntet', 'aoEntet'),
            'REGROUP' => array('Regroupement', 'regroup')
        );
        
        return $array;
    }
    
    /**
     * Fonction permettente le traitement automatique de l'insertion des CS après l'envoi d'un formulaire
     *
     * @param array<mixed> $post
     * @param \PDO $pdo
     * @param string $id
     */
    public static function ajouterCS($post, $pdo, $id = '') :void
    {
        $spliter = constante::spliter;
        $matches  = str_replace("CS_EXTENTION", "", (array) preg_grep('/^CS_EXTENTION/', array_keys($post)));
        foreach ($matches as $key) {
            $completeKey = 'CS_EXTENTION'.$key;
            $explod = explode($spliter, $completeKey);
            
            if ($id != '') {
                $explod[2]=$id;
            }
            
            ChampSupp::insertChampSupp($explod[1], $explod[2], $explod[3], $post[$completeKey], $pdo);
        }
    }
    
    /**
     * Fonction permettente le traitement automatique de la MAJ des CS après l'envoi d'un formulaire
     *
     * @param array<mixed> $post
     * @param \PDO $pdo
     */
    public static function updateCS($post, $pdo, bool $brOxaprocV1 = false, ?string $idCS = null) :int
    {
        $spliter = Constante::spliter;
        $matches = str_replace("CS_EXTENTION", "", (array) preg_grep('/^CS_EXTENTION/', array_keys($post)));
        $retourOk = 1;
        foreach ($matches as $key) {
            $completeKey = 'CS_EXTENTION'.$key;
            $explod = explode($spliter, $completeKey);
            
            if ($brOxaprocV1) {
                $explod[1] = 'BR_OXAPROC';
                $explod[2] = $idCS;
            }

            $retourSQL = ChampSupp::updateChampSupp($explod[1], $explod[2], $explod[3], $post[$completeKey], $pdo);
            if (!$retourSQL) {
                $retourOk = 0;
            }
        }
        return $retourOk;
    }

    /**
     * @param array<mixed> $post
     * @param \PDO $pdo
     * @param string $codTab
     * @return void
     */
    public static function mergeCSpanier($post, $pdo, $codTab) : void
    {
        $spliter = Constante::spliter;
        $matches  = str_replace("CS_EXTENTION", "", (array)preg_grep('/^CS_EXTENTION/', array_keys($post)));
        foreach ($matches as $key) {
            $completeKey = 'CS_EXTENTION'.$key;
            $explod = explode($spliter, $completeKey);

            (new OxaUserPanierCsDon($pdo))->merge([
                'id_user'   => $_SESSION['id_user'],
                'id_panier' => $_SESSION['id_panier'],
                'NUM_FIX'   => $explod[3],
                'VALEUR'    => $post[$completeKey],
                'ID_LIGNE'  => '0',
                'COD_TAB'   => $codTab,
            ]);
        }
    }
    
    /**
     *
     * @param string $codtab
     * @param \PDO $pdo
     * @param boolean $onlyDispo
     * @param string $TYPE
     * @return array<array<string>>  - liste id_cd
     */
    public static function listeIdCS($codtab, $pdo, $onlyDispo = false, $TYPE = null)
    {
        $req = "SELECT NUM_FIX, TYPE FROM CS_CHPS with(nolock) WHERE COD_TAB=:value";
        
        if ($onlyDispo) {
            $req .= " and CS_CHPS.NONDISPO = ".SQL::setBoolSql(0);
        }
        
        $arrayReq=[':value' => $codtab];

        if ($TYPE) {
            $req .= " AND CS_CHPS.TYPE = :type";
            $arrayReq[':type'] = $TYPE;
        }

        $req .= " ORDER BY NUM_FIX";

        $champSupp = $pdo->prepare($req);
        $champSupp->execute($arrayReq);
        
        $return = $champSupp->fetchAll($pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }
        
    /**
     * getinfoOneCs
     *
     * @param  mixed $numFix
     * @return array<mixed> List of CsChps
     */
    public function getinfoOneCs(string $codTab, $numFix) :array
    {
        return $this->getSchema($codTab)[ $numFix ] ?? [];
    }

    /**
     * return array{COD_TAB:string,NUM_FIX:string,TYPE:string,NOM:string}
     * @return array<string|int, string[]> List of CsChps indexed by NUMFIX
     */
    public function getSchema(string $codTab) :array
    {
        return $this->memoize(__FUNCTION__, [$codTab], function ($codTab) {
            
            $arrayReq = [':codeTab' => $codTab];
            
            return array_column($this->fetchAssocAll(
                "SELECT 
                    CS_CHPS.COD_TAB, 
                    CS_CHPS.NUM_FIX, 
                    CS_CHPS.NUM_VAR, 
                    CS_CHPS.TYPE, 
                    " . $this->selectTrad('CS_CHPS.NOM') . ",
                    CS_CHPS.OBLIG,
                    CS_CHPS.NONDISPO,
                    CS_CHPS.MODAPVAL,
                    CS_CHPS.NONMODIF,
                    CS_CHPS.NONDUPLI,
                    CS_CHPS.MODIFSIGN,
                    CS_CHPS.OXALINK_VIS,
                    CS_CHPS.OXALINK_OBLIG,

                    CS_TYPES.LIBELLE,
                    CS_TYPES.FORMAT,
                    CS_TYPES.CONTROLE,
                    CS_TYPES.ALP_LONG,
                    CS_TYPES.PE_LONG,
                    CS_TYPES.PD_LONG,
                    CS_TYPES.CONTROLE,
                    CS_TYPES.CTRL_STD,
                    CS_TYPES.NOM_CODE,
                    CS_TYPES.NOM_LIB,
                    CS_TYPES.LIB_LONG,
                    CS_TYPES.NEG_AUTO,
                    CS_TYPES.TBLSECTO,

                    CSPARINI.TAB_DESTI,
                    CSPARINI.CHP_DESTI,
                    CSPARINI.TAB_SOUR,
                    CSPARINI.CHP_SOUR 
                FROM CS_CHPS with(nolock)
                JOIN CS_TYPES with(nolock) ON CS_CHPS.TYPE = CS_TYPES.TYPE
                LEFT JOIN CSPARINI with(nolock) 
                    ON CSPARINI.TAB_DESTI = CS_CHPS.COD_TAB 
                    AND CSPARINI.CHP_DESTI = CS_CHPS.NUM_FIX 
                " . $this->joinTrad('CS_CHPS.NOM', $arrayReq) . "
                WHERE CS_CHPS.COD_TAB = :codeTab AND CS_CHPS.NONDISPO = 'F'
                ORDER BY CS_CHPS.NUM_VAR",
                $arrayReq
            ), null, 'NUM_FIX');
        });
    }

    /**
     * Requete permettant de récupere les parametrages des CS OXA.LINK (OXALINK_VIS, OXALINK_OBLIG)
     *
     * @param \PDO $pdo
     * @return array<array<string>> - liste CS oxalink
     */
    public static function listeCSoxalink($pdo)
    {
        $req = "SELECT NUM_FIX, NOM, OXALINK_VIS, OXALINK_OBLIG FROM CS_CHPS with(nolock) WHERE COD_TAB='FOURNISS'";
        
        $champSupp = $pdo->prepare($req);
        $champSupp->execute();
        
        $return = $champSupp->fetchAll($pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }
    
    /**
     * Requete récuperent les valeurs des CS (basé sur le code_enreg)
     *
     * @param string $codtab
     * @param string $codeEnr
     * @param \PDO $pdo
     * @return array<array<string>>
     */
    public static function listeCSDON($codtab, $codeEnr, $pdo)
    {
        $req = "SELECT * FROM CS_DON with(nolock) WHERE COD_TAB=:value AND COD_ENR=:codeEnr";
        
        $arrayReq = [
            ':value' => $codtab,
            ':codeEnr' => $codeEnr,
        ];

        $champSupp = $pdo->prepare($req);
        $champSupp->execute($arrayReq);
        
        $return = $champSupp->fetchAll($pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }
    
    /**
     * Requete permetant de récupere les valeurs d'initialisation d'un CS
     * elle est appelé en ajax lors du chargement d'une page
     *
     * @param string $desti
     * @param string $source
     * @param string $sourceId
     * @param \PDO $pdo
     * @return array<array<string>>
     */
    public static function initCs($desti, $source, $sourceId, $pdo)
    {
        $req = "SELECT ini.CHP_DESTI,
                case when FORMAT='M'
                    then (select CONTMEMO from MEMOS with(nolock) where NODEMEMO=cast(VALEUR as int))
                    else cs.VALEUR end
                    as CS_VALEUR, *
                from CSPARINI ini with(nolock)
                /* jointure vers les données des champs supplémentaires de la table source */
                join CS_DON cs with(nolock) on cs.COD_TAB=ini.TAB_SOUR and cs.NUM_FIX=ini.CHP_SOUR
                /* jointure vers les champs paramétrés sur la table cible pour pouvoir exclure les champs non visibles à l'écran */
                join CS_CHPS cspar with(nolock) on cspar.COD_TAB=ini.TAB_DESTI and cspar.NUM_FIX=ini.CHP_DESTI
                /* jointure vers la table des types de champ supplémentaire pour détecter les cas de champ mémo */
                join CS_TYPES typ with(nolock) on typ.TYPE=cspar.TYPE
                where /*cspar.NONDISPO=0x46 and*/
                ini.TAB_DESTI=:desti /*<Objet cible>*/
                and ini.TAB_SOUR=:source /*<Objet origine>*/
                and cs.COD_ENR=:sourceId /* <Identifiant origine (ici, le code fournisseur - à titre d'exempe sur la base EL2D 256)> */";
        
        $arrayReq = [
            ':desti' => $desti,
            ':source' => $source,
            ':sourceId' => $sourceId,
        ];

        $champSupp = $pdo->prepare($req);
        $champSupp->execute($arrayReq);
        $return = $champSupp->fetchAll($pdo::FETCH_ASSOC);
        
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * Undocumented function
     *
     * @param \pdo $pdo
     * @return array<mixed>
     */
    public static function initCsPanier(\pdo $pdo, string $codTab = 'DA_LIGNE')
    {
        $req = "SELECT ini.CHP_DESTI,
                case when FORMAT='M'
                    then (select CONTMEMO from MEMOS with(nolock) where NODEMEMO=cast(VALEUR as int))
                    else cs.VALEUR end
                    as CS_VALEUR, *
                from CSPARINI ini with(nolock)
                /* jointure vers les données des champs supplémentaires de la table source */
                join oxa_users_panier_CS_DON cs with(nolock) on cs.COD_TAB=ini.TAB_SOUR and cs.NUM_FIX=ini.CHP_SOUR
                /* jointure vers les champs paramétrés sur la table cible pour pouvoir exclure les champs non visibles à l'écran */
                join CS_CHPS cspar with(nolock) on cspar.COD_TAB=ini.TAB_DESTI and cspar.NUM_FIX=ini.CHP_DESTI
                /* jointure vers la table des types de champ supplémentaire pour détecter les cas de champ mémo */
                join CS_TYPES typ with(nolock) on typ.TYPE=cspar.TYPE
                where cspar.NONDISPO=0x46
                and ini.TAB_DESTI='$codTab' /*<Objet cible>*/
                and ini.TAB_SOUR='".($codTab=='DA_LIGNE'? 'DA_ENTET':'CA_ENTET')."' /*<Objet origine>*/
                and cs.id_panier= :idPanier and cs.id_user = :idUser /* <Identifiant origine (ici, le code fournisseur - à titre d'exempe sur la base EL2D 256)> */";
        
        $arrayReq = [
            ':idPanier' => $_SESSION['id_panier'],
            ':idUser' => $_SESSION['id_user']
        ];
        
        $champSupp = $pdo->prepare($req);
        $champSupp->execute($arrayReq);
        
        $return = $champSupp->fetchAll($pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * @param string|string[] $targetId
     */
    public function copyDefault(
        string $targetTab,
        $targetId,
        string $sourceTab,
        string $sourceId
    ) :void {
        // get default values

        $listDefaultValue = $this->fetchAssocAll(
            "SELECT 
                ini.TAB_DESTI as COD_TAB,
                ini.CHP_DESTI as NUM_FIX,
                typ.FORMAT as FORMAT,
                (
                    CASE when FORMAT='M'
                        then (select CONTMEMO from MEMOS with(nolock) where NODEMEMO = cast(cs.VALEUR as int))
                        else cs.VALEUR 
                    END
                ) as VALEUR,
                DestData.VALEUR as PrevValue
            FROM CSPARINI ini with(nolock)
            JOIN CS_DON cs with(nolock) 
                ON cs.COD_TAB = ini.TAB_SOUR 
                AND cs.NUM_FIX = ini.CHP_SOUR
                AND cs.COD_ENR = :sourceId
            JOIN CS_CHPS cspar with(nolock) 
                ON cspar.COD_TAB = ini.TAB_DESTI 
                AND cspar.NUM_FIX = ini.CHP_DESTI
                AND cspar.NONDISPO = 0x46
            JOIN CS_TYPES typ with(nolock) ON typ.TYPE = cspar.TYPE
            LEFT JOIN CS_DON DestData with(nolock) 
                ON DestData.COD_TAB = ini.TAB_DESTI
                AND DestData.NUM_FIX = ini.CHP_DESTI
                AND DestData.COD_ENR = :targetId
            WHERE ini.TAB_DESTI = :targetTab
                AND ini.TAB_SOUR = :sourceTab",
            [
                ':targetTab' => $targetTab,
                ':targetId' => self::encodeCodeEnr($targetTab, $targetId),
                ':sourceTab' => $sourceTab,
                ':sourceId' => self::encodeCodeEnr($sourceTab, $sourceId),
            ]
        );

        // Update/insert MEMOS
        foreach ($listDefaultValue as &$row) {
            if ($row['FORMAT'] != 'M') {
                continue;
            }

            // TODO : merge into one query
            $row['VALEUR'] = Sql::updateMemo(
                $this->pdo,
                $row['PrevValue'],
                $row['VALEUR']
            );
        }
        unset($row);

        // Update CsDon
        foreach ($listDefaultValue as $row) {
            // TODO : merge into one query
            (new CsDon($this->pdo))->merge([
                'COD_TAB' => $targetTab,
                'COD_ENR' => self::encodeCodeEnr($targetTab, $targetId),
                'NUM_FIX' => $row['NUM_FIX'],
                'VALEUR' => $row['VALEUR'],
            ]);
        }
    }
    
    /**
     * initCsSql
     *
     * @param  string|int $desti
     * @param  string|int $source
     * @param  string|int $idDesti
     * @param  string|int $idSource
     * @deprecated use copyDefault if it fit the need, or fix the memo bug
     */
    public static function initCsSql($desti, $source, $idDesti, $idSource, \PDO $pdo) : void
    {
        $champSupp = new ChampSupp($pdo);
        $champSupp->copyDefault(
            (string) $desti,
            (string) $idDesti,
            (string) $source,
            (string) $idSource
        );
    }
    
    /**
     * Permet l'autocompletion du code_enreg (par defaut sur 9 caractère pour les doc, et 12 pour la ligne d'un doc)
     *
     * @param string $codeEnr
     * @param string|int $ligneEnr
     * @param ?string $codeTab
     * @return string
     */
    public static function autoCompletCodeEnr($codeEnr, $ligneEnr = null, $codeTab = null) :string
    {
        $complet =str_pad($codeEnr, 9, '0', STR_PAD_LEFT);
        
        if ($codeTab=='FAC_LIG') {
            return $complet.'|'.$ligneEnr;
        }

        if ($ligneEnr) {
            $complet.= str_pad((string)$ligneEnr, 3, '0', STR_PAD_LEFT);
        }

        return $complet;
    }

    public static function getCodeEnrSqlExpr(
        string $fieldCodeEnr,
        ?string $fieldLigneEnr = null,
        ?string $codeTab = null
    ) :string {
        $complet = "RIGHT('000000000' + cast(".Sql::quoteColumn($fieldCodeEnr)." as varchar), 9)";
        if ($codeTab=='FAC_LIG') {
            return $complet." + '|' +  cast(".Sql::quoteColumn($fieldLigneEnr)." as varchar)";
        }
        
        if ($fieldLigneEnr) {
            return $complet." + RIGHT('000' + cast(".Sql::quoteColumn($fieldLigneEnr)." as varchar), 3)";
        }

        return $complet;
    }
    
    /**
     * Permet l'autocompletion du code_enreg des imputations
     *
     * @param \PDO $codeTab
     * @param string $codeTab
     * @param string $codeEnr
     * @return string
     */
    public static function autoCompletCodeEnrImputations(\PDO $pdo, string $codeTab, string $codeEnr, ?string $codeSte = null): string
    {
        $isSecto = false;
        if ($codeTab == 'SECTIMPU' || $codeTab == 'DIRECTIO') {
            $isSecto = \Helpers\Outil\Roles::droit($pdo, 'SISECTOR', 'PARAMSTE');
        } elseif ($codeTab == 'CODECOMP') {
            $isSecto = \Helpers\Outil\Roles::droit($pdo, 'CDCSECTO', 'PARAMSTE');
        } elseif ($codeTab == 'SSCPTANA') {
            $isSecto = \Helpers\Outil\Roles::droit($pdo, 'SCASECTO', 'PARAMSTE');
        }
        
        if ($isSecto) {
            if (!$codeSte) {
                $codeSte = $_SESSION['Session_SocUsr'];
            }
            $complet = str_pad($codeSte, 7, ' ', STR_PAD_RIGHT) . $codeEnr;
        } else {
            $complet = str_pad($codeEnr, 7, ' ', STR_PAD_LEFT);
        }

        return $complet;
    }

    /**
     * @param string|array<string> $codeEnr
     *
     */
    public static function encodeCodeEnr(string $codeTab, $codeEnr) :string
    {
        if (!in_array($codeTab, [
            'FAC_ENT', 'FAC_LIG',
            'CA_ENTET', 'BR_RECEP', 'CA_LIGNE',
            'DA_ENTET', 'DA_LIGNE', 'SBE_ENTE',
            'AO_ENTET', 'AO_LIGNE', 'SBE_LIGN',
            'DOA', 'SDS_ENTE','SDS_LIGN', 'SBS_ENTE',
            'SBS_LIGN','SBT_ENTE', 'SBT_LIGN',
            'AO', 'FAC_LIG_TRAVAIL' // ????
        ])) {
            if (!is_string($codeEnr)) {
                throw new Exception('!!!');
            }
            return $codeEnr;
        }

        if (is_string($codeEnr)) {
            return self::autoCompletCodeEnr($codeEnr);
        }
        if (is_array($codeEnr)) {
            return self::autoCompletCodeEnr($codeEnr[0], $codeEnr[1], $codeTab);
        }
        /** @phpstan-ignore-next-line */
        throw new Exception('Invalid type for $codeEnr');
    }

    /**
     * @param array<mixed> $params
     * @return array<mixed>
     */
    public static function getCSValueSpecific($params, \PDO $pdo)
    {
        $req =
        "SELECT NUM_FIX, VALEUR 
        FROM CS_DON with (nolock) 
        WHERE COD_TAB = :codeTab 
        AND COD_ENR = :codEnr 
        AND NUM_FIX = (
            SELECT LIB01DICO  
            FROM DILISPEC with (nolock) 
            WHERE TYPEDICO = :typedico
            AND CODEDICO = :codedico
        )
        ";
        
        $arrayReq = [
            ':codeTab' => $params['COD_TAB'],
            ':codEnr' => $params['COD_ENR'],
            ':typedico' => $params['typedico'],
            ':codedico' => $params['codedico'],
        ];

        $param = $pdo->prepare($req);
        $param->execute($arrayReq);
        $return = $param->fetch($pdo::FETCH_ASSOC);

        if ($return) {
            return $return;
        } else {
            return [];
        }
    }
    /**
     * @param array<mixed> $params
     * @return array<mixed>
     */
    public static function getEnregByValueSpecific($params, \pdo $pdo)
    {
        if ($params['VALEUR']) {
            $SELECT = 'COD_ENR';
            $WHERE = 'VALEUR';
        } else {
            $SELECT = 'VALEUR';
            $WHERE = 'COD_ENR';
        }
    
        $req =
        "SELECT $SELECT
        FROM CS_DON with (nolock) 
        WHERE COD_TAB = :codeTab 
        AND $WHERE = :valeur 
        AND NUM_FIX = (
            SELECT LIB01DICO  
            FROM DILISPEC with (nolock) 
            WHERE TYPEDICO = :typedico
            AND CODEDICO = :codedico
        )
        ";
        
        $arrayReq = [
            ':codeTab' => $params['COD_TAB'],
            ':typedico' => $params['typedico'],
            ':codedico' => $params['codedico'],
        ];

        if ($params['VALEUR']) {
            $arrayReq[':valeur'] = $params['VALEUR'];
        } else {
            $arrayReq[':valeur'] = $params['COD_ENR'];
        }

        $param = $pdo->prepare($req);
        $param->execute($arrayReq);
        $return = $param->fetch($pdo::FETCH_ASSOC);

        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * @param array<mixed> $params
     * @return array<string>
     */
    public static function getCSValueByCSOriginSpecific($params, \PDO $pdo)
    {
        $req =
        "SELECT LIB01DICO csNum, 
        (SELECT VALEUR 
            FROM CS_DON with (nolock) 
            WHERE COD_TAB = :codeTab 
            AND COD_ENR = :codeEnr
            AND NUM_FIX = (
                SELECT LIB01DICO  
                FROM DILISPEC with (nolock) 
                WHERE TYPEDICO = :typedico_1 
                AND CODEDICO = :codedicoDestination_1
            )
        ) VALEUR
        FROM DILISPEC with (nolock) 
        WHERE TYPEDICO = :typedico_0 AND CODEDICO = :codedicoDestination_0";
        
        $arrayReq = [
            ':codeTab' => $params['COD_TAB'],
            ':codeEnr' => $params['COD_ENR'],
            ':typedico_0' => $params['typedico'],
            ':typedico_1' => $params['typedico'],
            ':codedicoDestination_0' => $params['codedicoDestination'],
            ':codedicoDestination_1' => $params['codedicoDestination'],
        ];

        $param = $pdo->prepare($req);
        $param->execute($arrayReq);
        $return = $param->fetch($pdo::FETCH_ASSOC);

        if ($return) {
            return $return;
        } else {
            return [];
        }
    }
    
    /**
     * @return array<mixed>
     */
    public function getInfoCS(string $codeTab, mixed $numFixe = null) :array
    {
        if ($numFixe) {
            return $this->getSchema($codeTab)[ $numFixe ] ?? [];
        }
        return $this->getSchema($codeTab);
    }
    
    /**
     * supprime les valeurs d'un CS panier
     *
     * @param string $codeTab
     * @param string $numFixe
     * @param \PDO $pdo
     */
    public static function deleteCSpanier($codeTab, $numFixe, $pdo) :void
    {
        $req = "delete from oxa_users_panier_CS_DON where id_user   = :login and   id_panier = :panier and COD_TAB   = :codetab and NUM_FIX   = :numFixe";
    
        $arrayReq = [
            ':login' => $_SESSION['id_user'],
            ':panier' => $_SESSION['id_panier'],
            ':codetab' => $codeTab,
            ':numFixe' => $numFixe,
        ];

        $param = $pdo->prepare($req);
        $param->execute($arrayReq);
    }
    
    /**
     * update les valeurs des CS panier
     *
     * @param \PDO $pdo
     * @param string $numFixe
     * @param number $visVraiFaux
     * @param number $obliVraiFaux
     */
    public static function updateCSoxalink($pdo, $numFixe, $visVraiFaux, $obliVraiFaux) :void
    {
        $req = "UPDATE CS_CHPS SET 
            OXALINK_VIS= ".sql::setBoolSql($visVraiFaux).",  
            OXALINK_OBLIG= ".sql::setBoolSql($obliVraiFaux)." 
        where COD_TAB='FOURNISS' and NUM_FIX = :numfix";

        $param = $pdo->prepare($req);
        $param->execute(['numfix' => $numFixe]);
    }
    
    /**
     * @return array<string>
     */
    public static function CSObligatoirePresent(\PDO $pdo, string $codeTab)
    {
        $req = "SELECT * from CS_CHPS with(nolock) where COD_TAB = :codeTab and OBLIG=0x54";
        
        $arrayReq = [
            ':codeTab' => $codeTab,
        ];
        
        $param = $pdo->prepare($req);
        $param->execute($arrayReq);
        $return = $param->fetch($pdo::FETCH_ASSOC);

        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * @return array<mixed>
     */
    public function getAllTsDonExist()
    {
        $req = "select TYPEDICO from TS_DON with (nolock) group BY TYPEDICO";

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

        if ($return) {
            return $return;
        } else {
            return [];
        }
    }
    
    /**
     * @return array<string>
     */
    public function tsDonExist(string $codeTab)
    {
        $req = "SELECT TOP 1 CS_TYPES.* FROM TS_DON WITH (NOLOCK)
                LEFT JOIN CS_TYPES WITH (NOLOCK) ON CS_TYPES.TYPE = TS_DON.TYPEDICO
                WHERE TYPEDICO=:codTab";
        return $this->fetchAssocOne($req, [':codTab' => $codeTab]);
    }
    
    /**
     * @return array<string> label indexed by BDD_TABLE
     */
    public function getLinkedObjectsList(bool $isDoa = false) :array
    {
        $arrayReq = [];
        $req = "SELECT 
                    " . $this->selectTrad('CS_TYPES.LIBELLE') . ",
                    CS_TYPES.TYPE AS BDD_TABLE
                FROM CS_TSTD WITH (NOLOCK), CS_TYPES WITH (NOLOCK)
                " . $this->joinTrad('CS_TYPES.LIBELLE', $arrayReq) . "
                WHERE CS_TSTD.COD_TAB = CS_TYPES.TYPE 
                    AND TAB_CTRL = 0x54 
                    AND CTRL_STD=0x54 "
                    . ($isDoa ? ' AND DISPO_DOA = 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','AO_ENTET','NATANA')
            UNION 
                SELECT 
                    CS_TYPES.LIBELLE,
                    CS_TYPES.TYPE AS BDD_TABLE
                FROM CS_TYPES WITH (NOLOCK) 
                WHERE CONTROLE=0x54 AND CTRL_STD=0x46";
        
        $listCsType = $this->fetchAssocAll($req, $arrayReq);
        
        return array_column($listCsType, 'LIBELLE', 'BDD_TABLE');
    }

    /**
     * @param mixed $sectorise
     * @param mixed $idChamps
     * @return array<mixed>
     */
    public function generateImputProjetAchat(
        Router $router,
        ITrad $trad,
        string $typeElement,
        $sectorise = null,
        $idChamps = null
    ) :array {
        if (!$typeElement) {
            return [];
        }

        $res = $this->generateImput($router, $trad, $typeElement);
        
        if ($sectorise) {
            $res['paramZoom'] = "{'CODE_STE' : $('#input_CODE_STE_".$idChamps."').val()}";
        }
        return $res;
    }

    /**
     * @return array<mixed>
     */
    public function generateImput(Router $router, ITrad $trad, string $typeElement) :array
    {
        if (!$typeElement) {
            return [];
        }

        if ($csType = $this->tsDonExist($typeElement)) {
            return [
                'csType' => true,
                'pathZoom' => $router->generateUri(
                    'Ajax.liste.tsDon',
                    ['typeDico' => $csType['TYPE']]
                ),
                'paramZoom' => '',
                'titleZoom' => Trad::strBuild(
                    $trad->t('Liste des @1@'),
                    $csType['LIBELLE']
                ),
                'pathValidator' => $router->generateUri('Ajax.validator.validatorTSDON'),
                'paramValidator' => "{'nomChamp' : '".$csType['LIBELLE']."', 'null' : 'true', 'TYPE' : '".$csType['TYPE']."'}",
                'Entity' => '',
            ];
        }
        
        $array = ChampSupp::getListeTableStd();
        $zoom = $array[ $typeElement ] ?? null;

        $where = $zoom[2] ?? '';
        $entity = $zoom[0] ?? null;
        $action = $zoom[1] ?? null;
        return [
            'csType' => false,
            'pathZoom' => $router->generateUri(
                'Ajax.liste.'.$action,
                ($action == 'entityList' ? ['entity' => $entity] : [])
            ),
            'paramZoom' => (!empty($where) ? "{'".$where."' : 'T'}" : ''),
            'titleZoom' => Trad::strBuild(
                $trad->t('Liste des @1@'),
                ($typeElement == 'AO_ENTET' ?$trad->t('Consultations'):$this->getLinkedObjectsList()[ $typeElement ] ?? 'Liste')
            ),
            'pathValidator' => $router->generateUri('Ajax.validator.zoom'),
            'paramValidator' => "{'entity': '".$entity."','nomChamp' : '".($zoom[1]??null)."', 'null' : 'true', 'post' : {'".$where."' : 'T'} }",
            'Entity' => $entity??'',
        ];
    }
    
    
    //4474
    /**
     * @return string[][]
     */
    public function getInfoValueCs(string $codeTab, string $codeEnreg, mixed $numFix = '')
    {
        $sql = "SELECT 
                    CS_CHPS.NOM,
                    CS_CHPS.NUM_FIX,
                    CS_TYPES.FORMAT, 
                    (CASE WHEN CS_TYPES.FORMAT='M' 
                    THEN (
                        SELECT CONTMEMO 
                        from MEMOS with(nolock) 
                        where NODEMEMO=cast(CS_DON.VALEUR as int)
                    ) 
                    ELSE CS_DON.VALEUR END) as VALEUR
                FROM CS_CHPS with(nolock)
                LEFT JOIN CS_DON with(nolock) 
                    ON CS_DON.COD_TAB = CS_CHPS.COD_TAB 
                    AND CS_DON.COD_ENR = :codeEnreg 
                    AND CS_DON.NUM_FIX = CS_CHPS.NUM_FIX
                LEFT JOIN CS_TYPES with(nolock) ON CS_TYPES.TYPE = CS_CHPS.TYPE 
                WHERE CS_CHPS.COD_TAB = :codeTab";

        $arrayReq = [
            ':codeTab' => $codeTab,
            ':codeEnreg' => $codeEnreg
        ];
        
        if ($numFix != '') {
            $sql .= " and   CS_CHPS.NUM_FIX 	= :numFix";
            $arrayReq[':numFix'] = $numFix;
        }

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

    /**
     * getCsDon
     *
     * @param  array<mixed> $idList list of CsDon identifier (COD_TAB,COD_ENR,NUM_FIX)
     * @return array<mixed>
     */
    public function getCsDon(array $idList): array
    {
        // TODO : trim(addslashes($codEnr))?

        if (empty($idList)) {
            return [];
        }

        $whereIn = self::_makeJoinWhereInMultiCol([
            'CS_DON.COD_TAB',
            'CS_DON.COD_ENR',
            'CS_DON.NUM_FIX',
        ], $idList);

        $req = "SELECT CS_DON.*,
            (case when CS_TYPES.FORMAT='M' 
            then ( MEMOS.CONTMEMO ) 
            else (CS_DON.VALEUR) end) AS VALUECHAMPSUPP 
        FROM CS_DON WITH (NOLOCK)
        
        JOIN CS_CHPS WITH (NOLOCK) 
            ON CS_DON.COD_TAB = CS_CHPS.COD_TAB
            AND CS_DON.NUM_FIX = CS_CHPS.NUM_FIX
            AND CS_CHPS.NONDISPO = " . SQL::setBoolSql(0) . " 
        JOIN CS_TYPES WITH (NOLOCK) ON CS_TYPES.TYPE = CS_CHPS.TYPE
        LEFT JOIN MEMOS with (nolock) ON 
            IIF(
                CS_TYPES.FORMAT='M',
                cast(CS_DON.VALEUR AS INT),
                0
            ) = NODEMEMO
        " . $whereIn['sql'];

        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

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


    /**
     * getinfoOneCs
     *
     * @param array{0:string,1:int}[] $csId list of COD_TAB (index 0) & NUM_FIX (index 1)
     * @return array<mixed>
     */
    public function getInfoCsList(array $csId)
    {
        if (empty($csId)) {
            return [];
        }

        $whereIn = self::_makeJoinWhereInMultiCol(['COD_TAB', 'NUM_FIX'], $csId);

        $req = 'SELECT * FROM CS_CHPS with(nolock) ' . $whereIn['sql'];

        // TODO : add ? LEFT OUTER JOIN CSPARINI WITH (NOLOCK)
        // ON CSPARINI.TAB_DESTI = CS_CHPS.COD_TAB
        // AND CSPARINI.CHP_DESTI = CS_CHPS.NUM_FIX

        return $this->fetchAssocAll($req, $whereIn['param_map']);
    }
     /**
     * Generate SQL line similar to :
     *     JOIN ( VALUES ('somekey', 1), ('someotherkey', 2) ...) AS t_wherein (col1, col2)
     *     ON  t_wherein.col1 = my_table.col_1
     *     AND t_wherein.col2 = my_table.col_2 ...
     */
    /**
     * @param array<mixed> $list_col
     * @param array<mixed> $list_composed_id
     * @param string $param_prefix
     * @param string $table_tmp
     * @return array<mixed>
     * @throws Exception
     */
    private static function _makeJoinWhereInMultiCol(
        array  $list_col,
        array  $list_composed_id,
        string $param_prefix = 'param',
        string $table_tmp = 't_wherein'
    ) {
        $list_col = array_values($list_col);
        if (empty($list_composed_id)) {
            throw new Exception('!!!');
        }

        $param_map = [];
        $in_value_list = [];
        foreach (array_values($list_composed_id) as $i => $list_id) {
            $param_group = [];

            foreach (array_values($list_id) as $j => $value) {
                $param_key = $param_prefix . '_' . $i . '_' . $j;
                $param_map[$param_key] = $value;
                $param_group[] = $param_key;
            }

            // Implode each group into "(:param_1_1,:param_1_2,...)"
            $in_value_list[] = '(' . implode(',', array_map(function ($param_key) {
                return ':' . $param_key;
            }, $param_group)) . ')';
        }

        $table_tmp_col_list = array_map(function ($i) {
            return 'col_' . $i;
        }, array_keys($list_col));


        return [
            'sql' => 'JOIN ( VALUES '
                . implode(',', $in_value_list)
                . ') AS ' . $table_tmp
                . '('
                . implode(',', array_map(function ($item) {
                    return Sql::quoteColumn($item);
                }, $table_tmp_col_list))
                . ') ON '
                // Generate "table_tmp.col_1 = [{my_col_1}] AND table_tmp.col_2 = [{my_col_2}] ..."
                . implode(' AND ', array_map(function ($i, $col_name) use ($table_tmp) {
                    return Sql::quoteColumn($table_tmp . '.col_' . $i)
                        . '='
                        . Sql::quoteColumn($col_name);
                }, array_keys($list_col), $list_col)),
            'param_map' => $param_map,
        ];
    }

    /**
     * @throws Exception
     */
    public static function formatValue(
        string $champSupType,
        ?string $value,
        ITrad $trad
    ) :string {
        if ($value === null) {
            return '';
        }
        switch ($champSupType) {
            case 'LOGIQUE':
                return sql::toBool($value) ?
                    $trad->t('Oui') : $trad->t('Non');
            case 'DATE':
                return Date::dateConverte(
                    Date::convert($value, 'd/m/Y', 'Ymd')
                );
            case 'ALPHA':
            default:
                return $value;
            // TODO: add others
        }
    }
    
    
    /**
     * fonction récuperant la valeur d'un CS
     * @param string $codTab
     * @param string $idUser
     * @param string $idPanier
     * @param string $numFixe
     * @param \PDO $pdo
     * @return array<mixed>
     */
    public static function getValueCSPanier($codTab, $idUser, $idPanier, $numFixe, $pdo)
    {
        $req = "SELECT * FROM oxa_users_panier_CS_DON with(nolock) WHERE COD_TAB=:codTab AND id_user=:idUser and id_panier=:idPanier AND NUM_FIX=:num_fix";
        
        $arrayReq = [
            ':codTab'  => $codTab,
            ':idPanier'      => $idPanier,
            ':idUser'      => $idUser,
            ':num_fix'      => $numFixe
        ];
        
        $request = $pdo->prepare($req);
        $request->execute($arrayReq);
        return $request->fetch()['VALEUR']??'';
    }
    
    
    /**
     * @param array<mixed> $post
     */
    public static function updateCsPanier(\PDO $pdo, string $numLigne, $post) : void
    {
        $spliter = Constante::spliter;
        
        foreach ($post as $key => $value) {
            if (strpos($key, 'CS_EXTENTION') === 0) {
                $explodeCS = explode($spliter, $key);
                
                $codTab = $explodeCS[1];
                $numFix = $explodeCS[3];
                
                self::insertCsPanier($pdo, $codTab, $numFix, $numLigne, $value);
            }
        }
    }

    private static function getFormat(\PDO $pdo, string $codTabParam, string $numFixe):string
    {
        $isMemoReq = "  SELECT FORMAT FROM CS_CHPS WITH (NOLOCK)
                        LEFT JOIN CS_TYPES WITH (NOLOCK) ON CS_TYPES.TYPE = CS_CHPS.TYPE
                        WHERE COD_TAB=:codeTab AND NUM_FIX = :numfix";

                        
        $isMemoarrayReq = [':codeTab' => $codTabParam,':numfix' => $numFixe];
        
        $requestisMemos = $pdo->prepare($isMemoReq);
        $requestisMemos->execute($isMemoarrayReq);
        $retour = $requestisMemos->fetch();

        return $retour['FORMAT']??'';
    }
    
    
    public static function insertCsPanier(\pdo $pdo, string $codTab, string $numFix, string $numLigne, string $value) : void
    {
        $oxaUserPanierCS = new OxaUserPanierCsDon($pdo);
        
        $isExist = $oxaUserPanierCS->find([
            'id_user' => $_SESSION['id_user'],
            'id_panier' => $_SESSION['id_panier'],
            'NUM_FIX' => $numFix,
            'ID_LIGNE' => $numLigne,
            'COD_TAB'  => $codTab
        ]);

        $format = self::getFormat($pdo, $codTab, $numFix);

        if ($format == 'D') {
            Date::dateToSql($value);
            $value = Date::dateConverte($value, 'jj/mm/aaaa');
        }
        
        if ($isExist) {
            $oxaUserPanierCS->update([
                'VALEUR' => $value
            ], [
                'id_user' => $_SESSION['id_user'],
                'id_panier' => $_SESSION['id_panier'],
                'NUM_FIX' => $numFix,
                'ID_LIGNE' => $numLigne,
                'COD_TAB'  => $codTab,
            ]);
        } else {
            $oxaUserPanierCS->insert([
                'id_user' => $_SESSION['id_user'],
                'id_panier' => $_SESSION['id_panier'],
                'NUM_FIX' => $numFix,
                'ID_LIGNE' => $numLigne,
                'COD_TAB'  => $codTab,
                'VALEUR' => $value
            ]);
        }
    }

    public static function modifCsPostVal(\PDO $pdo, string $codTabEnt, ?string $codTablign = null) : bool
    {
        $req ="select count(*) count from CS_CHPS with(nolock) where (COD_TAB = :codTabEnt ".($codTablign? "or COD_TAB = :codTabLign":"")." ) and (CS_CHPS.NONDISPO = 0x46 AND CS_CHPS.MODAPVAL = 0x54)";

        $arrayReq[':codTabEnt'] = $codTabEnt;

        if ($codTablign) {
            $arrayReq[':codTabLign'] = $codTablign;
        }
        $request = $pdo->prepare($req);
        $request->execute($arrayReq);
        $return = $request->fetch()??'';


        return ($return['count'] > 0);
    }

    public static function getCsExtKey(string $entityType, string $numfix) :string
    {
        return implode(Constante::spliter, [
            'CS_EXTENTION',
            $entityType,
            '',
            $numfix
        ]);
    }
}
