<?php
use chillerlan\Authenticator\{Authenticator, AuthenticatorOptions};
use chillerlan\Authenticator\Authenticators\AuthenticatorInterface;
/*
 *   This file is part of NOALYSS.
 *
 *   NOALYSS is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 *   NOALYSS is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *   along with NOALYSS; if not, write to the Free Software
 *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
// Copyright Author Dany De Bontridder danydb@aevalys.eu
/**
 * @file
 * @brief Data & function about connected users
 */
/**
 * @brief Data & function about connected users
 *   
 */
require_once NOALYSS_INCLUDE.'/constant.php';
require_once NOALYSS_INCLUDE.'/lib/user_common.php';
require_once NOALYSS_INCLUDE.'/lib/ac_common.php';

class Noalyss_User
{

    var $id; //!<  in account_repository , ac_users.use_id
    
    var $db; //!< database connx to the folder NOT repository
    var $admin; //!< is or is not admin
    var $valid; //!< is or is not valid
    var $first_name; 
    var $last_name ; //!< user's last_name
    var $name;
    var $active; //!< 1 active , 0 disables
    var $login; //!< login lower case
    var $password; //!< md5 of the password
    var $email; //!< user's email
    var $access_mode; //!< MOBILE or PC depending if when connecting $login contains @mobile 
    var $lang ; //!< user's language
    var $theme ; //!< user's  CSS Theme
    var $authent_method; //!< authentication method use for this user
    private $otp_secret; //!< string use as secret for OTP
    private $repository; //!< account_repository (\Database )
    /**
     * @brief Create an user , load an existing one or if p_id == -1 search for the connected user. To have an empty
     * user, give a p_id smaller than -1 or zero.
     *
     * @param $p_cn DatabaseCore connection
     * @param $p_id if -1 then load the current user, > 0 load the user , = 0 (or < -1 ) means an empty user
     * \param $repository ( \Database default null)  database 
     * to repository, if null  is given the database will be defined in config.inc.php
     */
    function __construct($p_cn, $p_id=-1,$repository=null)
    {
        $this->db=$p_cn;
        if ( $repository == null ) {
            $this->repository=new Database(0);
        } else {
            $this->repository=$repository;
        }
        
        // if p_id is not set then check the connected user
        if ($p_id==-1)
        {
            $this->connect_user();
            $this->set_session_var();
        }
        else // if p_id is set get data of another user
        {
            $this->id=$p_id;
            $this->load();
        }
    }
    /**
     * @brief get the repository
     * @return Database
     */
    public function get_repository():\Database {
        return $this->repository;
    }
    /**
     * @brief set the repository
     * @return Database
     */
    public function set_repository(Database $repository) {
        $this->repository = $repository;
        return $this;
    }

        /**
     * @brief   put user_login into Postgres config (session), it can be used for tracking users activities
      * @return void
     */
    public function  set_session_var()
    {
        $this->db->exec_sql(sprintf("select set_config('noalyss.user_login','%s',false)",
            Database::escape_string($_SESSION[SESSION_KEY.'g_user'])));
        $this->repository->exec_sql(sprintf("select set_config('noalyss.user_login','%s',false)",
            Database::escape_string($_SESSION[SESSION_KEY.'g_user'])));

    }
    public function __toString(): string
    {
        return "User ".print_r($this,true);
    }
    /**
     * @brief check the password and user
     */
    function can_connect()
    {
       
       $can_connect=$this->repository->get_value("select count(*) from ac_users 
				   where use_active=1 and
				   use_login=$1 and use_pass=$2",
               [$this->login,$this->password]);
       return $can_connect;
    }
    /**
     * @brief connect the user and set the $_SESSION variables if not set thanks the $_REQUEST  
     */
    private function connect_user()
    {
        if (!isset($_SESSION[SESSION_KEY.'g_user']))
        {
            $http=new \HttpInput();
            $user_login=$http->request("p_user", "string", "");
            $user_password=$http->request("p_pass", "string", "");
            
            if ($user_login!=""&&$user_password!="")
            {
                $_SESSION[SESSION_KEY."g_user"]=$user_login;
                $_SESSION[SESSION_KEY."g_pass"]=md5($user_password);
            }
            else
            {
                echo '<h2 class="error">'._('Session expirée<br>Utilisateur déconnecté').'</h2>';
                redirect('index.php', 1);
                exit();
            }


            if (strpos($user_login, '@mobile')!=false)
            {
                $this->access_mode='MOBILE';
                $this->login=str_ireplace("@mobile", "", $user_login);
            }
            else
            {
                $this->access_mode='PC';
                $this->login=strtolower($user_login);
            }
            
            $_SESSION[SESSION_KEY."access_mode"]=$this->access_mode;
            $_SESSION[SESSION_KEY.'g_user']=$this->login;
        }
        $this->login=$_SESSION[SESSION_KEY."g_user"];
        $this->password=$_SESSION[SESSION_KEY.'g_pass'];
        
        $this->id=-1;
        $this->lang=(isset($_SESSION[SESSION_KEY.'g_lang']))?$_SESSION[SESSION_KEY.'g_lang']:'fr_FR.utf8';
        $this->access_mode=$_SESSION[SESSION_KEY."access_mode"];
        

        // share user login with the repository
        $this->repository->exec_sql(sprintf("select set_config('noalyss.user_login','%s',false)",
            Database::escape_string($_SESSION[SESSION_KEY.'g_user'])));
        
        if ($this->can_connect() == 0 || $this->load()==-1  )
        {
           echo '<h2 class="error">'._('Utilisateur ou mot de passe incorrect').'</h2>';
           $sql="insert into audit_connect (ac_user,ac_ip,ac_module,ac_url,ac_state) values ($1,$2,$3,$4,$5)";
           $server_remote=$_SERVER['REMOTE_ADDR']?? "cmd-line";
           $request_uri=$_SERVER['REQUEST_URI']??"REQUEST-URI";
           
           $this->repository->exec_sql($sql,
                        array($_SESSION[SESSION_KEY.'g_user'],$server_remote, "DISCON",
                          $request_uri  , 'FAIL'));
           $this->clean_session();
           redirect('logout.php', 1);
           exit();
        }

        $this->load_global_pref();

        $_SESSION[SESSION_KEY.'g_lang']=$this->lang;
        $this->valid=(isset($_SESSION[SESSION_KEY.'isValid']))?1:0;
        if (isset($_SESSION[SESSION_KEY.'g_theme']))
        {
            $this->theme=$_SESSION[SESSION_KEY.'g_theme'];
        }
        $_SESSION[SESSION_KEY.'use_admin']=$this->admin;
        $_SESSION[SESSION_KEY.'use_name']=$this->name;
        $_SESSION[SESSION_KEY.'use_first_name']=$this->first_name;
    }

    /**
     * @brief access_mode tells what mode must be used : pc or mobile
     */
    public function get_access_mode()
    {
        return $this->access_mode;
    }

    /**
     * @brief access_mode tells what mode must be used : pc or mobile
     */
    public function set_access_mode($access_mode): object
    {
        $this->access_mode=$access_mode;
        return $this;
    }

    /**
     * @return int|mixed
     */
    public function getId(): int
    {
        return $this->id;
    }

    /**
     * @param int|mixed $id
     */
    public function setId(int $id): void
    {
        $this->id=$id;
    }

    /**
     * @return mixed
     */
    public function getDb()
    {
        return $this->db;
    }

    /**
     * @param mixed $db
     */
    public function setDb($db): void
    {
        $this->db=$db;
    }

    /**
     * @return default|int|mixed|string|string[]|null
     */
    public function getAdmin()
    {
        return $this->admin;
    }

    /**
     * @param default|int|mixed|string|string[]|null $admin
     */
    public function setAdmin($admin): void
    {
        $this->admin=$admin;
    }

    /**
     * @return int
     */
    public function getValid(): int
    {
        return $this->valid;
    }

    /**
     * @param int $valid
     */
    public function setValid(int $valid): void
    {
        $this->valid=$valid;
    }

    /**
     * @return default|mixed|string|string[]|null
     */
    public function getFirstName()
    {
        return $this->first_name;
    }

    /**
     * @param default|mixed|string|string[]|null $first_name
     */
    public function setFirstName($first_name): void
    {
        $this->first_name=$first_name;
    }

    /**
     * @return default|mixed|string|string[]|null
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param default|mixed|string|string[]|null $name
     */
    public function setName($name): void
    {
        $this->name=$name;
    }

    /**
     * @return mixed
     */
    public function getActive()
    {
        return $this->active;
    }

    /**
     * @param mixed $active
     */
    public function setActive($active): void
    {
        $this->active=$active;
    }

    /**
     * @return string
     */
    public function getLogin(): string
    {
        return $this->login;
    }

    /**
     * @param string $login
     */
    public function setLogin(string $login): void
    {
        $this->login=$login;
    }

    /**
     * @return mixed
     */
    public function getPassword()
    {
        return $this->password;
    }

    /**
     * @param mixed $password
     */
    public function setPassword($password): void
    {
        $this->password=$password;
    }

    /**
     * @return mixed
     */
    public function getEmail()
    {
        return $this->email;
    }

    /**
     * @param mixed $email
     */
    public function setEmail($email): void
    {
        $this->email=$email;
    }

    /*     * \brief load data from database.
     * if this->id == -1, it is unknown so we have to retrieve it
      from the database by the login
     * return -1 if nothing is found or the use_id
     */

    function load():int
    {
        /* if this->id == -1, it is unknown so we have to retrieve it from
          the database thanks it login */
        if ($this->id<0)
        {
            $sql_cond="   where lower(use_login)=lower($1)";
            $sql_array=array($this->login);
        }
        else
        {
            $sql_cond="   where use_id=$1";
            $sql_array=array($this->id);
        }
        /**
         * mono version
         */
        if ( $this->repository->exist_table("repo_version")) {
            $repo_version = $this->repository->get_value("select val from repo_version");
        } else {
            $repo_version = $this->repository->get_value("select val from version");
        }
        /**
         * when upgrading , ACCOUNT_REPOSITORY is not yet upgraded
         */
        if ( $repo_version < 21)
        {
            $sql="select use_id,
                            use_first_name,
                            use_name,
                            use_login,
                            use_active,
                            use_admin,
                            use_pass,
                            use_email
                        from ac_users "; 
            $row=array();
            $row['use_auth_method']=$row['use_otp_secret']="";
        } else {
         $sql="select use_id,
                            use_first_name,
                            use_name,
                            use_login,
                            use_active,
                            use_admin,
                            use_pass,
                            use_email,
                            use_auth_method,
                            use_otp_secret
                        from ac_users ";
        }
        $Res=$this->repository->exec_sql($sql.$sql_cond, $sql_array);
        if (($Max=Database::num_row($Res))==0)
            return -1;
        $row=Database::fetch_array($Res, 0);
        $this->id=$row['use_id'];
        $this->first_name=$row['use_first_name'];
        $this->last_name=$row['use_name'];
        $this->name=$row['use_name'];
        $this->active=$row['use_active'];
        $this->login=strtolower($row['use_login']);
        $this->admin=$row['use_admin'];
        $this->password=$row['use_pass'];
        $this->email=$row['use_email'];
        $this->authent_method=$row['use_auth_method'];
        $this->otp_secret=$row['use_otp_secret'];
        return $this->id;
    }

    function save()
    {
        if ( $this->authent_method  != 0 && $this->otp_secret == null) {
            $this->generate_otp();
        }
        $Sql="update ac_users set use_first_name=$1, use_name=$2
             ,use_active=$3,use_admin=$4,use_pass=$5 ,use_email = $7 
             , use_auth_method=$8,use_otp_secret=$9
                where use_id=$6";
        $Res=$this->repository->exec_sql($Sql,
                array($this->first_name //1
                , $this->last_name // 2
                , $this->active //3 
                , $this->admin //4 
                , $this->password //5 
                , $this->id //6 
                , $this->email //7 
                , $this->authent_method //8
                , $this->otp_secret //9
                ));
    }

    function insert()
    {

        $Sql="INSERT INTO ac_users(
                        use_first_name, use_name, use_login, use_active,  
                        use_admin, use_pass, use_email)
                            VALUES ($1, $2, $3, $4, $5, $6, $7) returning use_id";

        $this->id=$this->repository->get_value($Sql,
                array($this->first_name, $this->last_name, $this->login, 1, $this->admin, 
                    $this->password, $this->email));
    }

    /**
     * \brief Check if user is active and exists in therepository
     * Automatically redirect, it doesn't check if a user can access a folder
     * \param $silent false, echo an error message and exit, true : exit without warning
     * default is false
     *
      ++ */
    function Check($silent=false, $from='')
    {

        $res=0;
        $pass5=$this->password;

        $sql="select ac_users.use_login,ac_users.use_active, ac_users.use_pass,
             use_admin,use_first_name,use_name
             from ac_users
             where ac_users.use_id=$1 
             and ac_users.use_active=1
             and ac_users.use_pass=$2";
        $ret=$this->repository->exec_sql($sql, array($this->id, $pass5));
        $res=Database::num_row($ret);
        if ($res>0)
        {
            $r=Database::fetch_array($ret, 0);
            $_SESSION[SESSION_KEY.'use_admin']=$r['use_admin'];
            $_SESSION[SESSION_KEY.'use_name']=$r['use_name'];
            $_SESSION[SESSION_KEY.'use_first_name']=$r['use_first_name'];
            $_SESSION[SESSION_KEY.'isValid']=1;

            $this->admin=$_SESSION[SESSION_KEY.'use_admin'];
            $this->name=$_SESSION[SESSION_KEY.'use_name'];
            $this->first_name=$_SESSION[SESSION_KEY.'use_first_name'];
            $this->load_global_pref();
        }
        $sql="insert into audit_connect (ac_user,ac_ip,ac_module,ac_url,ac_state) values ($1,$2,$3,$4,$5)";

        if ($res==0 || $this->can_connect() == 0)
        {
            $this->repository->exec_sql($sql,
                    array($_SESSION[SESSION_KEY.'g_user'], $_SERVER["REMOTE_ADDR"],
                        $from, $_SERVER['REQUEST_URI'], 'FAIL'));
            if (!$silent)
            {
                echo '<script> alert(\''._('Utilisateur ou mot de passe incorrect').'\')</script>';
                redirect('index.html');
            }
            $this->valid=0;
            session_unset();
            exit-1;
        }
        else
        {
            if ($from=='LOGIN' || $from=='PORTAL') 
            {
                $this->repository->exec_sql($sql,
                        array($_SESSION[SESSION_KEY.'g_user'], $_SERVER["REMOTE_ADDR"], $from,
                            $_SERVER['REQUEST_URI'], 'SUCCESS'));
            }
            $this->valid=1;
        }

        return $ret;
    }

    /**
     * \brief return  the access to a folder,
     * \param $p_dossier id if it is == 0 then we take the value from $_SESSION
     * \return the priv_priv
     *          - X no access
     *          - R has access (normal user)

     *
     */
    function get_folder_access($p_dossier=0)
    {

        if ($p_dossier==0)
            $p_dossier=dossier::id();
        if ($this->admin==1)
            return 'R';

        $sql="select 'R' from jnt_use_dos where use_id=$1 and dos_id=$2";

        $res=$this->repository->get_value($sql, array($this->id, $p_dossier));

        if ($this->repository->get_affected()==0)
            return 'X';
        return $res;
    }

    /**
     * \brief save the access of a folder
     * \param $db_id the dossier id
     * \param $priv boolean, true then it is granted, false it is removed
     */
    function set_folder_access($db_id, $priv)
    {

        if ($priv)
        {
            // the access is granted
            $jnt=$this->repository->get_value("select jnt_id from jnt_use_dos where dos_id=$1 and use_id=$2", array($db_id, $this->id));

            if ($this->repository->size()==0)
            {

                $Res=$this->repository->exec_sql("insert into jnt_use_dos(dos_id,use_id) values($1,$2)", array($db_id, $this->id));
            }
        }
        else
        {
            // Access is revoked
            $this->repository->exec_sql('delete from jnt_use_dos where use_id  = $1 and dos_id = $2 ', array($this->id, $db_id));
        }
    }

    /**
     * \brief check that a user is valid and the access to the folder
     * \param $p_ledger the ledger to check
     * \return the priv_priv
     * - O only predefined operation
     * - W write
     * - R read only
     * - X no access
     *
     *
     */
    function get_ledger_access($p_ledger)
    {
        if ($this->admin==1||
                $this->is_local_admin(dossier::id())==1||$this->get_status_security_ledger()==0)
            return 'W';

        $sql="select uj_priv from user_sec_jrn where uj_login=$1 and uj_jrn_id=$2";
        $res=$this->db->get_value($sql, array($this->login, $p_ledger));

        if ($res=='')
            $res='X';
        return $res;
    }

    /**
     * \brief get all the available ledgers for the current user
     * \param $p_type = ALL or the type of the ledger (ACH,VEN,FIN,ODS)
     * \param $p_access =3 for Read or WRITE, 2  write, 1 for readonly
     * \param (boolean) $all if true show also inactive
     *  \return a double array of available ledgers
      @verbatim
      [0] => [jrn_def_id]
      [jrn_def_type]
      [jrn_def_name]
      [jrn_def_class_deb]
      [jrn_def_class_cred]
      [jrn_type_id]
      [jrn_desc]
      [uj_priv]
      @endverbatim
     */
    function get_ledger($p_type='ALL', $p_access=3, $all=TRUE)
    {
        $p_type=strtoupper($p_type);
        if (!in_array($p_type, ["FIN", "ALL", "ODS", "VEN", 'ACH']))
        {
            record_log(sprintf("UGL1, p_type %s", $p_type));
            throw new Exception("UGL1"._("Type incorrecte"));
        }
        if ($all==TRUE)
        {
            $sql_enable="";
        }
        else
        {
            $sql_enable="and jrn_enable=1";
        }
        if ($this->admin!=1&&$this->is_local_admin()!=1&&$this->get_status_security_ledger()==1)
        {
            $sql_type=($p_type=='ALL')?'':"and jrn_def_type=upper('".sql_string($p_type)."')";
            switch ($p_access)
            {
                case 3:
                    $sql_access=" and uj_priv!= 'X' ";
                    break;
                case 2:
                    $sql_access=" and uj_priv = 'W' and jrn_enable=1 ";
                    break;

                case 1:
                    $sql_access=" and ( uj_priv = 'R' or uj_priv='W') ";
                    break;
            }

            $sql="select jrn_def_id,jrn_def_type,jrn_def_code,
                 jrn_def_name,jrn_def_class_deb,jrn_def_class_cred,jrn_type_id,jrn_desc,uj_priv,
                 jrn_deb_max_line,jrn_cred_max_line,jrn_def_description,jrn_enable
                 from jrn_def join jrn_type on jrn_def_type=jrn_type_id
                 join user_sec_jrn on uj_jrn_id=jrn_def_id
                 where
                 uj_login='".$this->login."'".
                    $sql_type.$sql_access.$sql_enable.
                    " order by jrn_Def_name";
        }
        else
        {
            $sql_type=($p_type=='ALL')?'  '.$sql_enable:"where jrn_def_type=upper('".sql_string($p_type)."')  ".$sql_enable;
            $sql="select jrn_def_id,jrn_def_type,jrn_def_code,jrn_def_name,jrn_def_class_deb,jrn_def_class_cred,jrn_deb_max_line,jrn_cred_max_line,
                 jrn_type_id,jrn_desc,'W' as uj_priv,jrn_def_description,jrn_enable
                 from jrn_def join jrn_type on jrn_def_type=jrn_type_id
                 $sql_type
                 order by jrn_Def_name";
        }
        $res=$this->db->exec_sql($sql);
        if (Database::num_row($res)==0)
            return null;
        $array=Database::fetch_all($res);
        return $array;
    }

    /**
     * \brief return an sql condition for filtering the permitted ledger
     * \param $p_type = ALL or the type of the ledger (ACH,VEN,FIN,ODS)
     * \param $p_access =3 for READ or WRITE, 2 READ and write, 1 for readonly
     *
     * \return sql condition like = jrn_def_id in (...)
     */
    function get_ledger_sql($p_type='ALL', $p_access=3)
    {
        $aLedger=$this->get_ledger($p_type, $p_access);
        if (empty($aLedger))
            return ' jrn_def_id < 0 ';
        $sql=" jrn_def_id in (";
        foreach ($aLedger as $row)
        {
            $sql.=$row['jrn_def_id'].',';
        }
        $sql.='-1)';
        return $sql;
    }

    /**
     * @brief synomym for isAdmin,
     * @deprecated
     */
    function Admin():int
    {
        return $this->isAdmin();
    }

    /**
     * @brief  Check if an user is an admin and check also his password
     *
     * @return 1 for yes 0 for no
     */
    function isAdmin():int
    {
        $this->admin=0;
        $pass5=$this->password;
        $sql="select count(*) from ac_users where use_login=$1
             and use_active=1 and use_admin=1 and use_pass=$2 ";

        $this->admin=$this->repository->get_value($sql, array($this->login,$pass5));
        return $this->admin;
    }

    /**
     * \brief  Set the selected periode in the user's preferences
     *
     * \param $p_periode periode
     * \param     - $p_user
     *
     */
    function set_periode($p_periode)
    {
        $sql="update user_local_pref set parameter_value=$1 where user_id=$2 and parameter_type='PERIODE'";
        $Res=$this->db->exec_sql($sql, [$p_periode, $this->id]);
    }

    private function set_default_periode()
    {

        /* get the first periode */
        $sql='select min(p_id) as pid '
                .' from parm_periode '
                .' where p_closed = false and p_start = (select min(p_start) from parm_periode)';
        $Res=$this->db->exec_sql($sql);

        $pid=Database::fetch_result($Res, 0, 0);
        /* if all the periode are closed, then we use the last closed period */
        if ($pid==null)
        {
            $sql='select min(p_id) as pid '
                    .'from parm_periode '
                    .'where p_start = (select max(p_start) from parm_periode)';
            $Res2=$this->db->exec_sql($sql);
            $pid=Database::fetch_result($Res2, 0, 0);
            if ($pid==null)
            {
                throw new Exception(_("Aucune période trouvéee !!!"));
            }

            $pid=Database::fetch_result($Res2, 0, 0);
        }

        $sql=sprintf("insert into user_local_pref (user_id,parameter_value,parameter_type)
                     values ('%s','%d','PERIODE')", $this->id, $pid);
        $Res=$this->db->exec_sql($sql);
    }

    /**
     * \brief  Get the default periode from the user's preferences
     *
     * \return the default periode
     *
     *
     */
    function get_periode()
    {

        $array=$this->get_preference();
        if (!isset($array['PERIODE']))
        {
            $this->set_default_periode();
            $array=$this->get_preference();
        }
        return $array['PERIODE'];
    }


    /**
     * @brief Save the preference , the scope is global, the settings are saved
     * into account_repository
     * @param $key THEME,  LANG , PAGESIZE
     * @param $value value of the key
     */
    function save_global_preference($key, $value)
    {
        $count=$this->repository->get_value("select count(*)
	    from
	    user_global_pref
	    where
	    parameter_type=$1 and user_id=$2", array($key, $this->login));
        if ($count==1)
        {
            $this->repository->exec_sql("update user_global_pref set parameter_value=$1
		where parameter_type=$2 and user_id=$3", array($value, $key, $this->login));
        }
        elseif ($count==0)
        {
            $this->repository->exec_sql("insert into user_global_pref(user_id,parameter_type,parameter_value)
		values($1,$2,$3)", array($this->login, $key, $value));
        }
    }

    /**
     * \brief  Get the default user's preferences
     * \return array of (parameter_type => parameter_value)
     */
    function get_preference()
    {
        $sql="select parameter_type,parameter_value from user_local_pref where user_id=$1";
        $Res=$this->db->exec_sql($sql, array($this->id));
        $l_array=array();
        for ($i=0; $i<Database::num_row($Res); $i++)
        {
            $row=Database::fetch_array($Res, $i);
            $type=$row['parameter_type'];
            $l_array[$type]=$row['parameter_value'];
        }
        $a_global_pref=$this->repository->get_array("select parameter_type,parameter_value from user_global_pref 
									where 
									upper(user_id) = upper($1)", [$this->login]);
        $nb_global=count($a_global_pref);
        for ($i=0; $i<$nb_global; $i++)
        {
            $idx=$a_global_pref[$i]['parameter_type'];
            $value=$a_global_pref[$i]['parameter_value'];
            $l_array[$idx]=$value;
        }

        return $l_array;
    }
    /**
     * @brief Check if an user can access a module, return 1 if yes, otherwise 0
     * record in audit log
     * This function works only if user is connected to a Folder
     * @param string $p_module menu_ref.me_code
     * @returns 0 for FORBIDDEN,  1 for GRANTED
     */
    function check_module($p_module)
    {
        if ( $this->access_mode == "PC") {

            $acc=$this->db->get_value("select count(*) from v_all_menu where p_id = $1
                    and me_code=$2", array($this->get_profile(), $p_module));
        } elseif ($this->access_mode=="MOBILE") {
            $acc=$this->db->get_value("select count(*) from profile_mobile where p_id=$1 and me_code=$2",
                    array($this->get_profile(), $p_module));
        } else {
            throw new Exception("USER:823:ACCESS_MODE INCONNU");
        }
        if ($acc==0)
        {
            $this->audit("FAIL", $p_module);
            return 0;
        }
        $this->audit("SUCCESS", $p_module);
        return 1;
    }

    /**
     * \brief  Check if an user is allowed to do an action
     * \param p_action_id
     * \return
     * 	- 0 no priv
     *      - 1 priv granted
     * @see constant.security.php
     */
    function check_action($p_action_id)
    {
        /*  save it into the log */
        global $audit;
        if ($this->Admin()==1)
            return 1;
        if ($this->is_local_admin(dossier::id())==1)
            return 1;
        if ($this->get_status_security_action()==0)
            return 1;
        $Res=$this->db->exec_sql(
                "select * from user_sec_act where ua_login=$1 and ua_act_id=$2", array($this->login, $p_action_id));
        $Count=Database::num_row($Res);
        if ($Count==0)
        {
            if (isset($audit)&&$audit==true)
            {
                
                $sql="insert into audit_connect (ac_user,ac_ip,ac_module,ac_url,ac_state) values ($1,$2,$3,$4,$5)";
                $this->repository->exec_sql($sql,
                        array($_SESSION[SESSION_KEY.'g_user'], $_SERVER["REMOTE_ADDR"], $p_action_id, $_SERVER['REQUEST_URI'],
                            'FAIL'));
            }
            return 0;
        }
        if ($Count==1)
            return 1;
        echo_error(_("Action invalide"));
        record_log("User:check_action".sprintf("login %s ua_act_id %s", $this->login, $p_action_id));
        exit();
    }

    /**
     * \brief  Get the global preferences from user_global_pref
     *        in the account_repository db
     *
     * \note set $SESSION[g_variable]
     */
    function load_global_pref()
    {
        // Load everything in an array
        $Res=$this->repository->exec_sql("select parameter_type,parameter_value from
                            user_global_pref
                            where user_id=$1", [$this->login]);
        $Max=Database::num_row($Res);
        if ($Max==0)
        {
            $this->insert_default_global_pref();
            $this->load_global_pref();
            return;
        }
        // Load value into array
        $line=array();
        for ($i=0; $i<$Max; $i++)
        {
            $row=Database::fetch_array($Res, $i);
            $type=$row['parameter_type'];
            $line[$type]=$row['parameter_value'];
        }
        // save array into g_ variable
        $array_pref=array('g_theme'=>'THEME',
            'g_pagesize'=>'PAGESIZE',
            'g_topmenu'=>'TOPMENU',
            'g_lang'=>'LANG',
            'csv_fieldsep'=>'csv_fieldsep',
            'csv_decimal'=>'csv_decimal',
            'csv_encoding'=>'csv_encoding',
            'first_week_day'=>'first_week_day');

        foreach ($array_pref as $name=> $parameter)
        {
            if (!isset($line[$parameter]))
            {
                $this->insert_default_global_pref($parameter);
                $this->load_global_pref();
                return;
            }
            $_SESSION[SESSION_KEY.$name]=$line[$parameter];
        }
    }

    /**
     * \brief  insert default pref
     *        if no parameter are given insert all the existing
     *        parameter otherwise only the requested
     * \param $p_type parameter's type or nothing
     * \param $p_value parameter value
     *
     */
    function insert_default_global_pref($p_type="", $p_value="")
    {

        $default_parameter=array("THEME"=>"classic",
            "PAGESIZE"=>"50",
            'TOPMENU'=>'TEXT',
            'LANG'=>'fr_FR.utf8',
            'csv_fieldsep'=>'0',
            'csv_decimal'=>'0',
            'csv_encoding'=>'utf8',
            'first_week_day'=>1
        );
        $sql="insert into user_global_pref(user_id,parameter_type,parameter_value)
             values ($1,$2,$3)";
        if ($p_type=="")
        {
            foreach ($default_parameter as $name=> $value)
            {
                $this->repository->exec_sql($sql, array($this->login, $name, $value));
            }
        }
        else
        {
            $value=($p_value=="")?$default_parameter[$p_type]:$p_value;
            if ( $this->repository->get_value("select count(*) from user_global_pref where user_id=$1 and parameter_type=$2",
                array($this->login,$p_type)) == 1)
            {
                $this->repository->exec_sql("update user_global_pref set parameter_value=$1 where user_id=$2 and parameter_type=$3",
                        array($value,$this->login,$p_type));
            } else {
                $this->repository->exec_sql($sql, array($this->login, $p_type, $value));
            }
        }
    }

    /**
     * \brief  update default pref
     *           if value is not given then use the default value
     *
     * \param $p_type parameter's type
     * \param $p_value parameter's value value of the type
     */
    function update_global_pref($p_type, $p_value="")
    {
        $default_parameter=array("THEME"=>"classic",
            "PAGESIZE"=>"50",
            "LANG"=>'fr_FR.utf8',
            'TOPMENU'=>'SELECT',
            'csv_fieldsep'=>'0',
            'csv_decimal'=>'0',
            'csv_encoding'=>'utf8',
            'first_week_day'=>1
        );
        $Sql="update user_global_pref set parameter_value=$1
             where parameter_type=$2 and
             user_id=$3";
        $value=($p_value=="")?$default_parameter[$p_type]:$p_value;
        $this->repository->exec_sql($Sql, array($value, $p_type, $this->login));
    }

//end function
    /**
     * \brief Return the year of current Periode
     *        it is the parm_periode.p_exercice col
     *        if an error occurs return 0
     */

    function get_exercice()
    {
        $sql="select p_exercice from parm_periode where p_id=$1";
        $Ret=$this->db->exec_sql($sql,[$this->get_periode()]);
        if (Database::num_row($Ret)==1)
        {
            $r=Database::fetch_array($Ret, 0);
            return $r['p_exercice'];
        }
        else
            return 0;
    }

    /**
     * \brief Check if the user can access
     * otherwise warn and exit
     * \param $p_action requested action
     * \param $p_js = 1 javascript, or 0 just a text or 2 to log it silently
     * \return nothing the program exits automatically
     */

    function can_request($p_action, $p_js=0)
    {
        if ($this->check_action($p_action)==0)
        {
            $this->audit('FAIL');
            if ($p_js==1)
            {
                echo create_script("alert_box(content[59])");
            }
            elseif ($p_js==2)
            {
                record_log(_("Access invalid").$p_action);
            }
            else
            {
                echo '<h2 class="error">',
                htmlspecialchars(_("Cette action ne vous est pas autorisée Contactez votre responsable")),
                '</h2>';
                echo '</div>';
            }
            exit(-1);
        }
    }

    /**
     * @brief Check if the user can print (in menu_ref p_type_display=p)
     * 	otherwise warn and exit
     * @param $p_action requested action
     * @return nothing the program exits automatically
     */
    function check_print($p_action)
    {
        global $audit;
        $this->audit('AUDIT', $p_action);
        if ($this->Admin()==1)
            return 1;

        $res=$this->db->get_value("select count(*) from profile_menu
			join profile_user using (p_id)
			where user_name=$1 and me_code=$2 ", array($this->login, $p_action));
        return $res;
    }

    /**
     * \brief Check if the user can print (in menu_ref p_type_display=p)
     * otherwise warn and exit
     * \param $p_action requested action
     * \return nothing the program exits automatically
     */

    function can_print($p_action, $p_js=0)
    {
        if ($this->check_print($p_action)==0)
        {
            $this->audit('FAIL');
            if ($p_js==1)
            {
                echo create_script("alert_box(content[59])");
            }
            else
            {
                echo '<div class="redcontent">';
                echo '<h2 class="error">',
                htmlspecialchars(_("Cette action ne vous est pas autorisée Contactez votre responsable")),
                '</h2>';
                echo '</div>';
            }
            exit(-1);
        }
    }

    /**
     * \brief  Check if an user is an local administrator
     * @deprecated since version 6.7
     *
     *
     * \param $p_dossier : dossier_id
     *
     * \return
     * 	- 0 if no
     *      - 1 if yes
     *
     */
    function is_local_admin($p_dossier=-1)
    {
        return 0;
    }

    /**
     * @brief return array of available repository
     *
     * @param $p_access  R for read W for write
     * @return an array
     */
    function get_available_repository($p_access='R')
    {
        $profile=$this->get_profile();
        $r=array();
        if ($p_access=='R')
        {
            $r=$this->db->get_array("select distinct u.r_id,r_name
                from
					profile_sec_repository as u
					join stock_repository as s on(u.r_id=s.r_id)
                where
                p_id =$1
                and ur_right='W'
				order by 2
				", array($profile));
        }
        if ($p_access=='W')
        {
            $r=$this->db->get_array("select distinct u.r_id,r_name
                from
					profile_sec_repository as u
					join stock_repository as s on(u.r_id=s.r_id)
                where
                p_id =$1 order by 2
               ", array($profile));
        }
        return $r;
    }

    /**
     * \brief return an array with all the active users who can access 
     *  $p_dossier including the global admin. The list concerns the user
     * in the repository of the "domain" defined in config.inc.php
     * 
     *  The user must be activated
     *
     * \param $p_dossier dossier
     * \return an array of user's  object
     *  array indices
     *    - use_id (id )
     *    - use_login (login of the user)
     *    - use_name
     *    - use_first_name
     * \param $db_repository ( \Database default null)  database 
     * to repository, if null  is given the database will be defined in config.inc.php
     * \exception throw an exception if nobody can access
     */
    static function get_list($p_dossier,
            $db_repository = null
            )
    {
        $sql="select distinct use_id,use_login,use_first_name,use_name from ac_users
             left outer join  jnt_use_dos using (use_id)
             where
              (dos_id=$1 and use_active=1) or (use_active=1 and use_admin=1)
              order by use_login,use_name";

        // connect to the repository
        if ( $db_repository == null ) {
            $repo_cnx=new Database(0);
        } else {
            $repo_cnx=$db_repository;
        }
        $array=$repo_cnx->get_array($sql, array($p_dossier));
        if ($repo_cnx->size()==0)
        {
            throw new \Exception('noalyss_user.get_list error inaccessible folders',1186);
        }
        return $array;
    }

    /**
     * \brief check the access of an user on a ledger
     *
     * \param $p_jrn the ledger id
     * \return
     * - O only predefined operation
     * - W write
     * - R read only
     * - X no access
     *
     */
    function check_jrn($p_jrn)
    {
        return $this->get_ledger_access($p_jrn);
    }

    /**
     * \brief check if an user can access a folder, if he cannot display a dialog box
     * and exit
     * \param the folder if
     * \param $silent false, echo an error message and exit, true : exit without warning
     * default is false
     * \return
     *  - L for administrator (local and global)
     *  - X no access
     *  - R regular user
     */
    function check_dossier($p_dossier_id, $silent=false)
    {
        $this->Admin();
        if ($this->admin==1||$this->is_local_admin($p_dossier_id)==1)
            return 'L';
        

        $dossier=$this->repository->get_value("select 'R' from jnt_use_dos where dos_id=$1 and use_id=$2",
                array($p_dossier_id, $this->id));
        $dossier=($dossier=='')?'X':$dossier;
        if ($dossier=='X')
        {
            $this->audit('FAIL', "Access folder ");
            if (!$silent)
            {
                alert(_('Dossier non accessible'));
                exit();
            }
        }
        return $dossier;
    }

    /**
     * @brief return the first date and the last date of the current exercice for the current user
     * @return and array ([0] => start_date,[1] => end_date)
     */
    function get_limit_current_exercice()
    {
        $current_exercice=$this->get_exercice();
        $periode=new Periode($this->db);
        list($per_start, $per_end)=$periode->get_limit($current_exercice);
        $start=$per_start->first_day();
        $end=$per_end->last_day();
        return array($start, $end);
    }

    /**
     * \brief   Show all the available folder  for the users
     *          at the login page. For the special case 'E'
     *          go directly to extension and bypasse the dashboard
     * \param $p_filtre user
     *
     * \return table in HTML
     *
     */
    function show_dossier($p_filtre="")
    {
        $p_array=$this->get_available_folder($p_filtre);

        $result="";

        $result.="<TABLE id=\"folder\" class=\"result\">";
        $result.="<tr>";
        $result.="<th>";
        $result.=_("Id");
        $result.="</th>";
        $result.="<th>";
        $result.=_("Nom");
        $result.="</th>";
        $result.="<th>";
        $result.=_("Description");
        $result.="</th>";
        $result.="</tr>";
        if ($p_array==0)
        {
            $result.="<tr>";
            $result.='<td style="width:auto" colspan=3>';
            $result.=_("Aucun dossier disponible");
            $result.='</td>';
            $result.="</tr>";
            return $result;
        }

        for ($i=0; $i<sizeof($p_array); $i++)
        {

            $id=$p_array[$i]['dos_id'];
            $name=$p_array[$i]['dos_name'];
            $desc=$p_array[$i]['dos_description'];
            if ($i%2==0)
                $tr="odd";
            else
                $tr="even";
            $target="do.php?gDossier=$id";

            $result.="<TR class=\"$tr\">";

            $result.=td($id, ' class="num" ');
            $result.="<TD class=\"$tr\">";
            $result.="<A class=\"dossier\" HREF=\"$target\">";
            $result.="  <B>".h($name)."</B>";
            $result.="</A>";
            $result.="</TD>";
            $desc=($desc=="")?"<i>Aucune description</i>":h($desc);
            $desc="<A class=\"dossier\" HREF=\"$target\">$desc</A>";
            $result.="<TD class=\"$tr\" >".$desc;
            $result.="</TD>";
            $result.="</TR>";
        }
        $result.="</TABLE>";
        return $result;
    }

    /**
     * \brief   Get all the available folders
     *          for the users, checked with the security
     *
     * \param  $p_filter
     * \return array containing
     *       - ac_dossier.dos_id
     *       - ac_dossier.dos_name
     *       - ac_dossier.dos_description
     *
     */
    function get_available_folder($p_filter="")
    {
        $cn=$this->repository;
        $filter="";
        if ($this->admin==0)
        {
            // show only available folders
            // if user is not an admin
            $Res=$this->repository->exec_sql("select 
    						distinct dos_id,dos_name,dos_description 
                            from ac_users
								natural join jnt_use_dos
								natural join  ac_dossier
                            where
								use_login= $1
								and use_active = 1
								and ( dos_name ilike '%' || $2 || '%' or dos_description ilike '%' || $2 || '%' )
                            order by dos_name", array($this->login, $p_filter));
        }
        else
        {
            $Res=$this->repository->exec_sql("select 
    			distinct dos_id,dos_name,dos_description from ac_dossier
             where   
                   dos_name  ilike '%' || $1|| '%' or dos_description ilike '%' || $1 || '%' 
			  order by dos_name", array($p_filter));
        }

        $max=Database::num_row($Res);
        if ($max==0)
            return 0;

        for ($i=0; $i<$max; $i++)
        {
            $array[]=Database::fetch_array($Res, $i);
        }
        return $array;
    }

    /**
     * @brief Audit action from the administration menu. 
     * Connect to the repository of the domain defined in config.inc.php
     * @param $p_module description of the action
     * @param $db_repository ( \Database default null)  database 
     * to repository, if null  is given the database will be defined in config.inc.php
     */
    static function audit_admin($p_module,$db_repository=null)
    {
        // connect to the repository
        if ( $db_repository == null ) {
            $repo_cnx=new Database(0);
        } else {
            $repo_cnx=$db_repository;
        }
        $sql="insert into audit_connect (ac_user,ac_ip,ac_module,ac_url,ac_state) values ($1,$2,$3,$4,$5)";

        $repo_cnx->exec_sql($sql,
                array(
                    $_SESSION[SESSION_KEY.'g_user'],
                    $_SERVER["REMOTE_ADDR"],
                    $p_module,
                    $_SERVER['REQUEST_URI'],
                    'ADMIN'));
    }

    function audit($action='AUDIT', $p_module="")
    {
        global $audit;
        $http=new \HttpInput();
        if ($audit)
        {
            if ($p_module==""&&isset($_REQUEST['ac']))
            {
                $p_module=$_REQUEST['ac'];
            }
            $dossier=$http->request("gDossier","string",0);
            if ( $dossier != 0)
                $p_module.=" dossier : ".$dossier;
            
            $sql="insert into audit_connect (ac_user,ac_ip,ac_module,ac_url,ac_state) values ($1,$2,$3,$4,$5)";

            $this->repository->exec_sql($sql,
                    array(
                        $_SESSION[SESSION_KEY.'g_user'],
                        $_SERVER["REMOTE_ADDR"],
                        $p_module,
                        $_SERVER['REQUEST_URI'],
                        $action));
            if ( $action=="FAIL") {
                record_log("security alert see audit");
            }
        }
    }

    function save_profile($p_id)
    {
        $count=$this->db->get_value("select count(*) from profile_user where user_name=$1", array($this->login));
        if ($count==0)
        {
            $this->db->exec_sql("insert into profile_user(p_id,user_name)
								values ($1,$2)", array($p_id, $this->login));
        }
        else
        {
            $this->db->exec_sql("update profile_user set p_id=$1 where user_name=$2", array($p_id, $this->login));
        }
    }

    /**
     * @brief  return the profile (p_id)
     * @return profile.p_id
     */
    function get_profile()
    {
        $profile=$this->db->get_value("select p_id from profile_user where
				lower(user_name)=lower($1) ", array($this->login));
        return $profile;
    }

    /**
     * @brief Compute the SQL string for the writable profile, 
     * the subselect for p_id , example
     * p_id in $g_user->sql_writable_profile.
     * The administrator can access all the profiles
     * R = Read Only W = Write and delete O = write and not delete
     * @return SQL string with the subselect for p_id 
     */
    function sql_writable_profile()
    {
        if ($this->admin!=1)
        {
            $sql=" (select p_granted "
                    ."     from user_sec_action_profile "
                    ."     where ua_right in ('W','O') and p_id=".$this->get_profile().") ";
        }
        else
        {
            $sql="(select p_id p_granted from profile)";
        }
        return $sql;
    }
    /**
     * @brief return array of writable action_profile 
     * 
     */
    function get_writable_profile()
    {
       $value=$this->db->get_array("select p_granted from ".$this->sql_writable_profile()." as m") ;
       $aGranted=array_column($value,"p_granted");
       return $aGranted;
    }
    /**
     * @brief return array of readable action_profile 
     * 
     */
    function get_readable_profile()
    {
       $value=$this->db->get_array("select p_granted from ".$this->sql_readable_profile()." as m") ;
       $aGranted=array_column($value,"p_granted");
       return $aGranted;
    }
    /**
     *@brief  Compute the SQL string for the readable profile, 
     * the subselect for p_id , example
     * p_id in $g_user->sql_readable_profile.
     * The administrator can read all the profiles
     * @return SQL string with the subselect for p_id 
     */
    function sql_readable_profile()
    {
        if ($this->admin!=1)
        {
            $sql=" (select p_granted "
                    ."     from user_sec_action_profile "
                    ."     where ua_right in ('W','R','O') and p_id=".$this->get_profile().") ";
        }
        else
        {
            $sql="(select p_id p_granted from profile)";
        }
        return $sql;
    }

    /**
     * @brief Check if the current user can add an action in the profile given
     * in parameter
     * @param type $p_profile profile.p_id = action_gestion.ag_dest
     * @return boolean
     */
    function can_add_action($p_profile)
    {

        $r=$this->db->get_value(' select count(*) 
                from user_sec_action_profile
                where p_granted=$2
                and p_id=$1', array($this->get_profile(), $p_profile));
        if ($r==0)
        {
            return false;
        }
        return true;
    }

    /**
     * @brief Check if the profile of the user can write for this profile
     * @param  $dtoc action_gestion.ag_id
     * @return true if he can write otherwise false
     */
    function can_write_action($dtoc)
    {
        if ($this->Admin()==1)
            return TRUE;
        if ($this->get_status_security_action()==0)
            return TRUE;
        $profile=$this->get_profile();
        $r=$this->db->get_value(" select count(*) from action_gestion where ag_id=$1 and ag_dest in
				(select p_granted from user_sec_action_profile where ua_right in ('W','O') and p_id=$2) ", array($dtoc, $profile));
        if ($r==0)
            return FALSE;
        return true;
    }

    /**
     * @brief Check if the profile of the user can write AND delete for this profile
     * @param  $dtoc action_gestion.ag_id
     * @return true if he can write otherwise false
     */
    function can_delete_action($dtoc)
    {
        if ($this->Admin()==1)
            return TRUE;
        if ($this->get_status_security_action()==0)
            return TRUE;
        $profile=$this->get_profile();
        $r=$this->db->get_value(" select count(*) from action_gestion where ag_id=$1 and ag_dest in
				(select p_granted from user_sec_action_profile where ua_right='W' and p_id=$2) ", array($dtoc, $profile));
        if ($r==0)
            return FALSE;
        return true;
    }

    /**
     * @brief Check if the profile of the user can write for this profile
     * @param  $dtoc action_gestion.ag_id
     * @return true if he can write otherwise false
     */
    function can_read_action($dtoc)
    {
        if ($this->Admin()==1)
            return true;
        $profile=$this->get_profile();
        $r=$this->db->get_value(" select count(*) from action_gestion where ag_id=$1 and (ag_dest in
				(select p_granted from user_sec_action_profile where p_id=$2) or ag_owner=$3)",
                array($dtoc, $profile, $this->login));
        if ($r==0)
            return false;
        return true;
    }

    /**
     * @brief Check if the profile of the user can write for this repository
     * @param  $p_repo stock_repository.r_id
     * @return true if he can write otherwise false
     */
    function can_write_repo($p_repo)
    {
        if ($this->Admin()==1)
            return true;
        $profile=$this->get_profile();
        $r=$this->db->get_value("select count(*)
                from profile_sec_repository
                where
                r_id=$1
                and p_id =$2
                and ur_right='W'", array($p_repo, $profile));
        if ($r==0)
            return false;
        return true;
    }

    /**
     * @brief Check if the profile of the user can read for this repository
     * @param  $p_repo stock_repository.r_id
     * @return true if he read write otherwise false
     */
    function can_read_repo($p_repo)
    {
        if ($this->Admin()==1)
            return true;
        $profile=$this->get_profile();
        $r=$this->db->get_value("select count(*)
                from profile_sec_repository
                where
                r_id=$1
                and p_id =$2
               ", array($p_repo, $profile));
        if ($r==0)
            return false;
        return true;
    }
    /**
     * @brief store the password in session
     */
    function password_to_session()
    {
          $_SESSION[SESSION_KEY.'g_pass']=$this->getPassword();
    }
    /**
     * @brief Save the password of the current user 
     * @param string $p_pass1 password (clear)
     * @param string $p_pass2 for confirming password (clear)
     * @see check_password_strength()
     * @return true : password successfully changed otherwise false
     */
    function save_password($p_pass1, $p_pass2)
    {
        if ($p_pass1==$p_pass2 && count(check_password_strength($p_pass1)['msg'])==0)
        {
            
            $l_pass=md5($p_pass1);
            $this->setPassword($l_pass);
            $this->repository->exec_sql("update ac_users set use_pass=$1 where use_login=$2",
                    array($l_pass, $this->login));
            return true;
        }
        else
        {
            
            return false;
        }
    }

    /**
     * @brief Save the password from PREFERENCE MODULE
     * @param type $p_email
     */
    function save_email($p_email)
    {
        $this->repository->exec_sql("update ac_users set use_email=$1 where use_login=$2",
                array($p_email, $_SESSION[SESSION_KEY.'g_user']));
    }

    /**
     *@brief  Remove a user and all his privileges
     * So it cannot connect anymore and all his privileges are removed from
     * the dossier.
     * @param $p_login (String) login
     * @param $p_dossier (int) dossier id
     * @param $db_repository ( \Database default null)  database 
     * to repository, if null  is given the database will be defined in config.inc.php
     * 
     */
    static function revoke_access($p_login, $p_dossier,$db_repository=null)
    {
        // connect to the repository
        if ( $db_repository == null ) {
            $repo_cnx=new Database(0);
        } else {
            $repo_cnx=$db_repository;
        }

        // Retrieve the user
        $user=$repo_cnx->get_array('select use_id,use_login from ac_users where use_login=$1', array($p_login));
        if (!$user)
            return false;

        // remove him from jnt_use_dos
        $repo_cnx->exec_sql("delete from jnt_use_dos WHERE use_id=$1 and dos_id=$2",
                array($user[0]['use_id'], $p_dossier));

        // Remove user from user's dossier
        $cn_dossier=new Database($p_dossier);
        $cn_dossier->exec_sql("delete from profile_user where user_name=$1", array($p_login));
        $cn_dossier->exec_sql("delete from user_sec_act where ua_login=$1", array($p_login));
    }

    /**
     * @brief Grant access to folder, grant administrator profile , all the ledgers and all the action
      * @param $p_login (String) login
     * @param $p_dossier (int) dossier id
     * @param $db_repository ( \Database default null)  database 
     * to repository, if null  is given the database will be defined in config.inc.php
     */
    static function grant_admin_access($p_login, $p_dossier,$db_repository=null)
    {
       // connect to the repository
        if ( $db_repository == null ) {
            $repo_cnx=new Database(0);
        } else {
            $repo_cnx=$db_repository;
        }
        $user=$repo_cnx->get_array("select use_id,use_login
                                from ac_users
                                where use_login=$1", array($p_login));

        if (!$user)
            return false;
        $cn_dossier=new Database($p_dossier);
        // if not access to DB
        if (
                $repo_cnx->get_value("select count(*) from jnt_use_dos where use_id=$1 and dos_id=$2",
                        array($user[0]['use_id'], $p_dossier))==0
        )
        {
            $repo_cnx->exec_sql("insert into jnt_use_dos(use_id,dos_id) values ($1,$2)",
                    array($user[0]['use_id'], $p_dossier));
        }
        //------  Give him the admin menu
        if ($cn_dossier->get_value("select count(*) from profile_user where user_name=$1", array($user[0]['use_login']))==0)
        {
            $cn_dossier->exec_sql('insert into profile_user(user_name,p_id) values($1,1)', array($user[0]['use_login']));
        }
        // Grant all action + ledger to him
        $cn_dossier->exec_sql("delete from user_sec_act where ua_login=$1", array($p_login));

        $cn_dossier->exec_sql("insert into user_sec_act (ua_login,ua_act_id)"
                ." select $1 ,ac_id from action ", array($p_login));

        $cn_dossier->exec_sql("delete from user_sec_jrn where uj_login=$1", array($p_login));
        $cn_dossier->exec_sql("insert into user_sec_jrn(uj_login,uj_jrn_id,uj_priv)"
                ." select $1,jrn_def_id,'W' from jrn_def", array($p_login));
    }
    /**
     * @brief cleansing : remove inexistant user
     * @param $p_dossier (int) dossier id
     * @param $db_repository ( \Database default null)  database 
     * to repository, if null  is given the database will be defined in config.inc.php
     * @return bool : true if success , false if no change
     */
    static function remove_inexistant_user($p_dossier,$db_repository=null)
    {
        // connect to the repository
        if ( $db_repository == null ) {
            $cnx_repo=new Database(0);
        } else {
            $cnx_repo=$db_repository;
        }
        $name=$cnx_repo->format_name($p_dossier, 'dos');
        if ($cnx_repo->exist_database($name)==0)
            return false;
        $cnx_dossier=new Database($p_dossier);
        if ($cnx_dossier->exist_table('profile_user'))
            $a_user=$cnx_dossier->get_array('select user_name from profile_user');
        else
            return false;

        if (!$a_user)
            return;
        $nb=count($a_user);
        for ($i=0; $i<$nb; $i++)
        {
            if ($cnx_repo->get_value('select count(*) from ac_users where use_login=$1', array($a_user[$i]['user_name']))==0)
            {
                if ($cnx_dossier->exist_table('user_sec_jrn'))
                    $cnx_dossier->exec_sql("delete from user_sec_jrn where uj_login=$1", array($a_user[$i]['user_name']));
                $cnx_dossier->exec_sql("delete from profile_user where user_name=$1", array($a_user[$i]['user_name']));
                if ($cnx_dossier->exist_table('user_sec_act'))
                    $cnx_dossier->exec_sql("delete from user_sec_act where ua_login=$1", array($a_user[$i]['user_name']));
                if ($cnx_dossier->exist_table('user_sec_jrn'))
                    $cnx_dossier->exec_sql("delete from user_sec_jrn where uj_login=$1", array($a_user[$i]['user_name']));
                if ($cnx_dossier->exist_table('user_active_security'))
                    $cnx_dossier->exec_sql("delete from user_active_security where us_login=$1",
                            array($a_user[$i]['user_name']));
            }
        }
        return true;
    }

    /**
     * @brief Check the security on ledger for the user , it returns 1 if the security
     * on ledgers is enabled, otherwise 0 
     */
    function get_status_security_ledger()
    {
        $security=$this->db->get_value("select us_ledger from user_active_security 
                where 
                us_login=$1", [$this->login]);
        $n_security=($security=="Y")?1:0;
        return $n_security;
    }

    /**
     * @brief  Set the flag in the table user_active_security
     * @param int $p_value 1==enable  , 0 = disable
     * @exceptions invalid value
     */
    function set_status_security_ledger($p_value)
    {
        if ($p_value!=0&&$p_value!=1)
            throw new Exception(_("Valeur invalide"));
        $exist=$this->db->get_value("select count(*) from user_active_security where us_login=$1", [$this->login]);
        $flag=($p_value==1)?"Y":"N";
        if ($exist==0)
        {
            $this->db->exec_sql("insert into user_active_security (us_login,us_ledger,us_action) values ($1,$2,$3)",
                    [$this->login, $flag, 'Y']);
        }
        else
        {
            $this->db->exec_sql("update user_active_security set us_ledger=$1 where us_login = $2",
                    [$flag, $this->login]);
        }
    }

    /**
     * Check the security on ledger for the user , it returns 1 if the security 
     * on ledgers is enabled, otherwise 0 
     */
    function get_status_security_action()
    {
        $security=$this->db->get_value("select us_action from user_active_security 
                where 
                us_login=$1", [$this->login]);
        $n_security=($security=="Y")?1:0;
        return $n_security;
    }

    /**
     * Set the flag in the table user_active_security
     * @param int $p_value 1==enable  , 0 = disable
     * @exceptions invalid value
     */
    function set_status_security_action($p_value)
    {
        if ($p_value!=0&&$p_value!=1)
            throw new Exception(_("Valeur invalide"));
        $exist=$this->db->get_value("select count(*) from user_active_security where us_login=$1", [$this->login]);
        $flag=($p_value==1)?"Y":"N";
        if ($exist==0)
        {
            $this->db->exec_sql("insert into user_active_security (us_login,us_action,us_ledger) values ($1,$2,$3)",
                    [$this->login, $flag, 'Y']);
        }
        else
        {
            $this->db->exec_sql("update user_active_security set us_action=$1 where us_login = $2",
                    [$flag, $this->login]);
        }
    }

    /**
     *@brief first day in calendar
     * @see IDate::set_firstDate(
     */
    function get_first_week_day()
    {
        
        $result=$this->repository->get_value("select parameter_value from user_global_pref where parameter_type=$1 and user_id=$2 ",
                array("first_week_day", $this->login));
        if ($this->repository->count()==0)
        {
            $this->save_global_preference("first_week_day", 1);
            return 1;
        }
        return $result;
    }
    /**
     * @brief clean the sessions 
     */
    static function clean_session()
    {

        $aSession=$_SESSION;
        foreach($aSession as $key => $value) {
            if(DEBUGNOALYSS>1) { echo "[$key]=>[$value]";}
            
            if ( strpos($key,SESSION_KEY) === 0) {
                unset($_SESSION[$key]);
               if(DEBUGNOALYSS>1) { echo "=> [$key] cleaned";}
            }
        }
    }
    /**
     *@brief Get preference , either the user see the numeric id for VAT or its code, if the preference doesn't exist
     * by default , 0 is saved in ACCOUNT_REPOSITORY
     * @see ITva_Popup::set_vat_code()
     * @see ITva_Popup
     * 
     */
    function get_vat_code_preference():int
    {
        
        
        $result=$this->repository->get_value("select parameter_value from user_global_pref where parameter_type=$1 and user_id=$2 ",
            array("vat_code", $this->login));
        if ($this->repository->count()==0)
        {
            $this->save_global_preference("vat_code", 0);
            return 0;
        }
        return $result;
    }
    /**
     * @brief generate OTP secret to store in AC_USER.USE_OTP_SECRET
     */
    function generate_otp()
    {
       $otp=new \Noalyss\OTP();
       $this->otp_secret=$otp->build_secret();
    }
    /**
     * @brief retrieve authent_method
     *   - 0 password
     *   - 1 password + code by email
     *   - 2 password + OTP from application
     */
    public function get_authent_method() {
        return $this->authent_method;
    }

    public function get_otp_secret() {
        return $this->otp_secret;
    }

  
    public function set_authent_method($authent_method) {
        $this->authent_method = $authent_method;
        return $this;
    }

    public function set_otp_secret($otp_secret) {
        $this->otp_secret = $otp_secret;
        return $this;
    }
    public function set_identified()
    {
         $_SESSION[SESSION_KEY."db_auth"]='ok';
    }
    /**
     * @brief check is the double authentication has been successful
     * @return bool
     */
    public function is_double_identified() {
        
        if ( $this->authent_method == 0 )
        {
            $_SESSION[SESSION_KEY."db_auth"]='ok';
            return true;
        }
        if ( ! isset($_SESSION[SESSION_KEY."db_auth"])) {
            return false;
        }
        if ($_SESSION[SESSION_KEY."db_auth"] == "ok") {
            return true;
        }
        return false;
    }
    /**
     * @brief send a code to the user and save the expected code + UUID in
     * a table OTP_SEND_SECRET
     * @return bool false if fails true if succeeds
     * @throws \Exception
     */
    public function send_code_otp()
    {
        if ( $this->authent_method !=1 ) { return false; }
        $mail=new \Sendmail();
        $mail->set_format("HTML");
        $mail->set_from(ADMIN_WEB);
        $mail->mailto($this->getEmail());
        $mail->set_subject(_("NOALYSS : votre code secret "));
        $noalyss_url=NOALYSS_URL;
        
        
        if ( strlen(trim($this->otp_secret??"")) == 0 )  {
            throw new \Exception("noalyss_user.send_code_otp:secret empty",1945);
        }
        $otp=new \Noalyss\OTP();
        $code=$otp->compute_code($this->otp_secret);
       $message="<p>Bonjour,
           <br>
           <br>
           <p>Voici votre code secret utilisable pendant 10 minutes pour NOALYSS : <b> $code</b>  .
               </p>
               
             <p>
   Merci d'utiliser NOALYSS
   <br/>
   <br/>
   <br/>
   
Cordialement,
   <br/>
   <br/>

Noalyss team
      </p>

    <p>
    <i> Si cet email est dans vos spams, ajoutez l'expéditeur dans votre carnet d'adresse</i>
    </p>
";
        try {
            $uuid= guidv4();
            // remove old for this user 
            $this->repository->exec_sql("delete from otp_send_secret where use_id=$1 and os_code is not null"
                    ,[$this->id]);
            // remove also old one 
            $this->repository->exec_sql("delete from otp_send_secret where os_valid_time < now()");
           $now=new \DateTime();
           $valid=new \DateTime();
           $valid->modify('+10 minutes');
           
           $otp_send_secret=new Otp_Send_Secret_SQL($this->repository);
           $otp_send_secret->set("use_id",$this->id)
                   ->set('os_request',$uuid)
                   ->set("os_code",$code)
                   ->set('os_valid_time',$valid->format('d.m.Y H:i:s'));
           $otp_send_secret->save();
            $mail->set_message($message);
            $mail->compose();
            $mail->send();
            return $uuid;
        } catch (Exception $ex) {
            \record_log ($ex);
            throw new \Exception("noalyss_user.send_code_otp",1963,$ex);
        }
    }
     /**
     * @brief send an email with link to the user
     * @param $base_url (string) http url to scan the QRCode, the url in the 
     * mail will be {$base_url}/index.php?otp={$uuid}
     */
    function send_link_otp($base_url=null) {
        $mail = new \Sendmail();
        $mail->set_format("HTML");
        $mail->set_from(ADMIN_WEB);
        $mail->mailto($this->getEmail());
        $mail->set_subject(_("NOALYSS : Double authentification lien pour 2FA: OTP"));
        $noalyss_url = $base_url??NOALYSS_URL;
        $uuid = guidv4();
        $valid_time=new \DateTime();
        $valid_time->add(new \DateInterval('PT12H'));
        $str_time=$valid_time->format('d-m-Y H:i');
        /**
         * save in DB first
         */
        $message = "<p>Bonjour,</p>
<p>
    Afin de pouvoir utiliser la double authentification avec 2FA: OTP, pourriez-vous
    suivre ce lien et scanner le QRCode avec votre application android freeOTP ou Google Authenticator.
  </p>             
    <p>Ce lien expirera le <b>{$str_time}</b>.
        <br>
        <br>
        <br>
        
   <a href=\"{$noalyss_url}/index.php?otp={$uuid}\">{$noalyss_url}/index.php?otp={$uuid}</a>
        </p>
   
   <p>
   
   Merci d'utiliser NOALYSS
   </p>
   
<p>
Bien cordialement,
</p>
   <p>
               <i> Si cet email est dans vos spams, ajoutez l'expéditeur dans votre carnet d'adresse</i>
               </p>
";
        try {
            
            // remove old for this user 
            $this->repository->exec_sql("delete from otp_send_secret where use_id=$1 and os_code is null"
                    ,[$this->id]);
            // remove also old one 
            $this->repository->exec_sql("delete from otp_send_secret where os_valid_time < now()");
           
            $otp_send_secret_sql = new \Otp_Send_Secret_SQL($this->repository);
            $otp_send_secret_sql->set('use_id', $this->id)
                    ->set('os_valid_time',$valid_time->format('d-m-Y H:i'))
                    ->set('os_request', $uuid);
            
            $otp_send_secret_sql->save();
            $mail->set_message($message);
            $mail->compose();
            $mail->send();
            return $uuid;
        } catch (Exception $ex) {
            \record_log($ex);
            throw new \Exception("noalyss_user.send_link_otp",1998,$ex);
        }
    }
    /**
     * @brief FORM to enter the 6 digit enter by OTP
     * @param $uuid (string UUID) UUID in the message, null if there is no message
     */
    function input_otp($uuid="",$url="")
    {
      require_once NOALYSS_TEMPLATE."/noalyss_user-input_otp.php" ;
    }
    /**
     * @brief check that the OTP code is the one on smartphone
     * returns false if the given does not match the OTP 
     */
    function check_otp($code)
    {
        $otp=new \Noalyss\OTP();
        
        if ( $otp->compute_code($this->otp_secret) == $code ) {
            return true;
        }
        return false;
    }
}

?>