git.dj3c1t.com Git - mtweb/blob - mw/app/data/modules/sql/mw_data_sql_users.php

   1 <?php
   2
   3   class mw_data_sql_users extends mw_data{
   4
   5     var $users;
   6     var $user;
   7     var $roles;
   8     var $actions_roles;
   9
  10     # ----------------------------------------------------------------------------------------
  11     #                                                                                    users
  12     #
  13
  14     function users($start = 0, $alpha = null, $id_role = null){
  15       $sgbd = $this->sgbd();
  16       $env = $this->env();
  17       $users = array("list" => array(), "total" => 0);
  18       try{
  19         $SELECT = "SELECT #--users.*";
  20         $FROM = " FROM #--users";
  21         $WHERE = "";
  22         $WHERE .= (isset($alpha) ? ($WHERE ? " AND" : " WHERE")." LEFT(login, 1)=".$this->eq($alpha) : "");
  23         if(isset($id_role)){
  24           $SELECT .= ", #--users_roles.id_role";
  25           $FROM .=
  26            " LEFT JOIN #--users_roles ON ("
  27           ." #--users_roles.id_user=#--users.id"
  28           ." AND #--users_roles.id_role=".$this->eq($id_role)
  29           .")";
  30           $WHERE .= ($WHERE ? " AND" : " WHERE")." mw_users_roles.id_role IS NOT NULL";
  31         }
  32         $LIMIT = ($env->config("max_list") ? " LIMIT ".$env->config("max_list")." OFFSET ".$start : "");
  33         $sql = "SELECT count(*) as n FROM(".$SELECT.$FROM.$WHERE.") res";
  34         $rst = $sgbd->query($sql);
  35         if($v_rst = $sgbd->fetch_assoc($rst)) $users["total"] = $v_rst["n"];
  36         $sgbd->free_result($rst);
  37         if($users["total"] > 0){
  38           $sql = "SELECT * FROM(".$SELECT.$FROM.$WHERE.$LIMIT.") res";
  39           $rst = $sgbd->query($sql);
  40           while($v_rst = $sgbd->fetch_assoc($rst)) $users["list"][$v_rst["id"]] = $v_rst;
  41           $sgbd->free_result($rst);
  42           foreach($users["list"] as $id_user => $user){
  43             if(($roles = $this->list_user_roles($id_user)) !== false){
  44               $users["list"][$id_user]["roles"] = $roles;
  45             }
  46             else{
  47               $users = false;
  48               break;
  49             }
  50           }
  51         }
  52       }
  53       catch(Exception $e) { $users = false; }
  54       return $users;
  55     }
  56
  57     function list_user_roles($id_user){
  58       $sgbd = $this->sgbd();
  59       $roles = array();
  60       try{
  61         $rst = $sgbd->query("SELECT id_role FROM #--users_roles WHERE id_user=".$this->eq($id_user));
  62         while($v_rst = $sgbd->fetch_assoc($rst)) $roles[] = $v_rst["id_role"];
  63         $sgbd->free_result($rst);
  64       }
  65       catch(Exception $e) { $roles = false; }
  66       return $roles;
  67     }
  68
  69     function user_by_id($id){
  70       $env = $this->env();
  71       $user = $env->get_model("users");
  72       if($user->load("id", $id) === false) return false;
  73       return $user->get_values();
  74     }
  75
  76     function user($login){
  77       $env = $this->env();
  78       $user = $env->get_model("users");
  79       if($user->load("login", $login) === false) return false;
  80       return $user->get_values();
  81     }
  82
  83     function user_exists($login){
  84       $sgbd = $this->sgbd();
  85       $EXISTS = 0;
  86       try{
  87         $sql = "SELECT count(*) as n from #--users WHERE login=".$this->eq($login);
  88         $rst = $sgbd->query($sql);
  89         if($v_rst = $sgbd->fetch_assoc($rst)) $EXISTS = $v_rst["n"];
  90         $sgbd->free_result($rst);
  91       }
  92       catch(Exception $e) { $EXISTS = false; }
  93       return $EXISTS;
  94     }
  95
  96     function add_user($login, $password, $email, $roles){
  97       $sgbd = $this->sgbd();
  98       $user_id = false;
  99       try{
 100         $sql =
 101          "INSERT INTO #--users(login, password, email) VALUES"
 102         ."( ".$this->eq($login)
 103         .", ".$this->eq($password)
 104         .", ".$this->eq($email)
 105         .")";
 106         $sgbd->query($sql);
 107         $user_id = $sgbd->insert_id();
 108         foreach($roles as $id_role){
 109           $sql =
 110            "INSERT INTO #--users_roles(id_user, id_role) VALUES"
 111           ."( ".$user_id
 112           .", ".$this->eq($id_role)
 113           .")";
 114           $sgbd->query($sql);
 115         }
 116       }
 117       catch(Exception $e) { $user_id = false; }
 118       return $user_id;
 119     }
 120
 121     function set_user($id, $login, $password, $email, $roles){
 122       $sgbd = $this->sgbd();
 123       try{
 124         $sql =
 125          "UPDATE #--users SET"
 126         ."  login=".$this->eq($login)
 127         .", password=".$this->eq($password)
 128         .", email=".$this->eq($email)
 129         ." WHERE id=".$this->eq($id);
 130         $sgbd->query($sql);
 131         if(!$this->clear_user_roles($id)) return false;
 132         foreach($roles as $id_role){
 133           if(!$this->add_user_role($id, $id_role)) return false;
 134         }
 135       }
 136       catch(Exception $e) { return false; }
 137       return true;
 138     }
 139
 140     function clear_user_roles($id_user){
 141       $sgbd = $this->sgbd();
 142       try{
 143         $sql = "DELETE FROM #--users_roles WHERE id_user=".$this->eq($id_user);
 144         $sgbd->query($sql);
 145       }
 146       catch(Exception $e) { return false; }
 147       return true;
 148     }
 149
 150     function add_user_role($id_user, $id_role){
 151       $sgbd = $this->sgbd();
 152       try{
 153         $sql =
 154          "INSERT INTO #--users_roles(id_user, id_role) VALUES"
 155         ."( ".$this->eq($id_user)
 156         .", ".$this->eq($id_role)
 157         .")";
 158         $sgbd->query($sql);
 159       }
 160       catch(Exception $e) { return false; }
 161       return true;
 162     }
 163
 164     function del_user($login){
 165       if(($user = $this->user($login)) !== false){
 166         $sgbd = $this->sgbd();
 167         try{
 168           $sql = "DELETE FROM #--users_roles WHERE id_user=".$user["id"];
 169           $sgbd->query($sql);
 170           $sql = "DELETE FROM #--users WHERE login=".$this->eq($login)." AND id=".$user["id"];
 171           $sgbd->query($sql);
 172         }
 173         catch(Exception $e) { return false; }
 174       }
 175       else return false;
 176       return true;
 177     }
 178
 179     # ----------------------------------------------------------------------------------------
 180     #                                                                                    roles
 181     #
 182
 183     function init_roles(){
 184       $sgbd = $this->sgbd();
 185       $this->roles = array();
 186       try{
 187         $sql = "SELECT * FROM #--roles";
 188         $rst = $sgbd->query($sql);
 189         while($v_rst = $sgbd->fetch_assoc($rst)) $this->roles[$v_rst["id"]] = $v_rst;
 190         $sgbd->free_result($rst);
 191       }
 192       catch(Exception $e) { $this->roles = false; }
 193       return $this->roles;
 194     }
 195
 196     function roles(){
 197       if(!isset($this->roles)) return false;
 198       return $this->roles;
 199     }
 200
 201     function add_role($nom, $intitule){
 202       $sgbd = $this->sgbd();
 203       try{
 204         $sql =
 205          "INSERT INTO #--roles(nom, intitule) VALUES("
 206         ."  ".$this->eq($nom)
 207         .", ".$this->eq($intitule)
 208         .")";
 209         $rst = $sgbd->query($sql);
 210         $id_role = $sgbd->insert_id();
 211       }
 212       catch(Exception $e) { $id_role = false; }
 213       return $id_role;
 214     }
 215
 216     function get_role($id){
 217       if($id === "0") return array(
 218         "id" => 0,
 219         "nom" => "",
 220         "intitule" => ""
 221       );
 222       $sgbd = $this->sgbd();
 223       $role = array();
 224       try{
 225         $sql = "SELECT * FROM #--roles WHERE id=".$this->eq($id);
 226         $rst = $sgbd->query($sql);
 227         if($v_rst = $sgbd->fetch_assoc($rst)) $role = $v_rst;
 228         $sgbd->free_result($rst);
 229       }
 230       catch(Exception $e) { $role = false; }
 231       return $role;
 232     }
 233
 234     function set_role($id, $nom, $intitule){
 235       $sgbd = $this->sgbd();
 236       try{
 237         $sql =
 238          "UPDATE #--roles SET"
 239         ."  nom=".$this->eq($nom)
 240         .", intitule=".$this->eq($intitule)
 241         ." WHERE id=".$this->eq($id);
 242         $rst = $sgbd->query($sql);
 243       }
 244       catch(Exception $e) { return false; }
 245       return true;
 246     }
 247
 248     function clear_role_actions($id_role){
 249       $sgbd = $this->sgbd();
 250       try{
 251         $sql = "DELETE FROM #--actions_roles WHERE id_role=".$this->eq($id_role);
 252         $sgbd->query($sql);
 253       }
 254       catch(Exception $e) { return false; }
 255       return true;
 256     }
 257
 258     function clear_role_users($id_role){
 259       $sgbd = $this->sgbd();
 260       try{
 261         $sql = "DELETE FROM #--users_roles WHERE id_role=".$this->eq($id_role);
 262         $sgbd->query($sql);
 263       }
 264       catch(Exception $e) { return false; }
 265       return true;
 266     }
 267
 268     function add_role_action($id_role, $action){
 269       $sgbd = $this->sgbd();
 270       try{
 271         $sql = "INSERT INTO #--actions_roles(action, id_role) VALUES(".$this->eq($action).", ".$this->eq($id_role).")";
 272         $sgbd->query($sql);
 273         $id_action_role = $sgbd->insert_id();
 274       }
 275       catch(Exception $e) { $id_action_role = false; }
 276       return $id_action_role;
 277     }
 278
 279     function del_role($id_role){
 280       $sgbd = $this->sgbd();
 281       try{
 282         $sql = "DELETE FROM #--roles WHERE id=".$this->eq($id_role);
 283         $sgbd->query($sql);
 284       }
 285       catch(Exception $e) { return false; }
 286       return true;
 287     }
 288
 289     function get_user_roles(){
 290       $user_roles = array();
 291       $user = $this->get_session_user();
 292       if($user && isset($user["id"])){
 293         $sgbd = $this->sgbd();
 294         try{
 295           $sql = "SELECT id_role FROM #--users_roles WHERE id_user=".$this->eq($user["id"]);
 296           $rst = $sgbd->query($sql);
 297           while($v_rst = $sgbd->fetch_assoc($rst)) $user_roles[] = $v_rst["id_role"];
 298           $sgbd->free_result($rst);
 299         }
 300         catch(Exception $_e){ return false; }
 301       }
 302       else $user_roles[] = 0;
 303       if(!$user_roles) $user_roles[] = 0;
 304       return $user_roles;
 305     }
 306
 307     function init_actions_roles(){
 308       if(!isset($this->roles)) return false;
 309       $this->actions_roles = $this->read_actions_roles();
 310       return $this->actions_roles;
 311     }
 312
 313     function read_actions_roles($params = array()){
 314       $group_by_action = isset($params["group_by_action"]) ? $params["group_by_action"] : false;
 315       $sgbd = $this->sgbd();
 316       $actions_roles = array();
 317       try{
 318         $sql = "SELECT * FROM #--actions_roles";
 319         $rst = $sgbd->query($sql);
 320         while($v_rst = $sgbd->fetch_assoc($rst)){
 321           if($group_by_action){
 322             if(!isset($actions_roles[$v_rst["action"]])) $actions_roles[$v_rst["action"]] = array();
 323             $actions_roles[$v_rst["action"]][] = $v_rst["id_role"];
 324           }
 325           else $actions_roles[$v_rst["id"]] = $v_rst;
 326         }
 327         $sgbd->free_result($rst);
 328       }
 329       catch(Exception $e) { $actions_roles = false; }
 330       return $actions_roles;
 331     }
 332
 333     function get_action_roles($mod, $controller = "index", $action = "index"){
 334       $sgbd = $this->sgbd();
 335       $roles = array();
 336       try{
 337         $sql =
 338          "SELECT action, id_role"
 339         ." FROM #--actions_roles"
 340         ." WHERE action=".$this->eq($mod)
 341         ." OR action=".$this->eq($mod."/".$controller)
 342         ." OR action=".$this->eq($mod."/".$controller."/".$action);
 343         $rst = $sgbd->query($sql);
 344         while($v_rst = $sgbd->fetch_assoc($rst)){
 345           if(!isset($roles[$v_rst["action"]])) $roles[$v_rst["action"]] = array();
 346           $roles[$v_rst["action"]][$v_rst["id_role"]] = true;
 347         }
 348         $sgbd->free_result($rst);
 349       }
 350       catch(Exception $e) { $roles = false; }
 351       return $roles;
 352     }
 353
 354     function get_actions($id_role = null){
 355       $env = $this->env();
 356       if($actions = $env->get_actions()){
 357         if(($actions_roles = $this->read_actions_roles(array("group_by_action" => true))) !== false){
 358           foreach($actions as $module_name => $module){
 359             if(isset($id_role)) $actions[$module_name]["module_allowed"] =
 360                 isset($actions_roles[$module_name])
 361             &&  in_array($id_role, $actions_roles[$module_name]);
 362             $actions[$module_name]["is_public"] =
 363                 isset($actions_roles[$module_name])
 364             &&  in_array(0, $actions_roles[$module_name]);
 365             foreach($module["controleurs"] as $controleur_name => $controleur){
 366               if(isset($id_role)) $actions[$module_name]["controleurs"][$controleur_name]["controleur_allowed"] =
 367                   isset($actions_roles[$module_name."/".$controleur_name])
 368               &&  in_array($id_role, $actions_roles[$module_name."/".$controleur_name]);
 369               $actions[$module_name]["controleurs"][$controleur_name]["is_public"] =
 370                   isset($actions_roles[$module_name."/".$controleur_name])
 371               &&  in_array(0, $actions_roles[$module_name."/".$controleur_name]);
 372               foreach($controleur["als"] as $index_als => $al){
 373                 if($al["actions"]){
 374                   if(isset($id_role)){
 375                     $HAS_ACTION_NOT_ALLOWED = false;
 376                     foreach($al["actions"] as $action_name){
 377                       if(
 378                           !isset($actions_roles[$module_name."/".$controleur_name."/".$action_name])
 379                       ||  !in_array($id_role, $actions_roles[$module_name."/".$controleur_name."/".$action_name])
 380                       ){
 381                         $HAS_ACTION_NOT_ALLOWED = true;
 382                         break;
 383                       }
 384                     }
 385                     if(!$HAS_ACTION_NOT_ALLOWED){
 386                       $actions[$module_name]["controleurs"][$controleur_name]["als"][$index_als]["action_allowed"] = true;
 387                     }
 388                   }
 389                   $HAS_ACTION_NOT_ALLOWED = false;
 390                   foreach($al["actions"] as $action_name){
 391                     if(
 392                         !isset($actions_roles[$module_name."/".$controleur_name."/".$action_name])
 393                     ||  !in_array(0, $actions_roles[$module_name."/".$controleur_name."/".$action_name])
 394                     ){
 395                       $HAS_ACTION_NOT_ALLOWED = true;
 396                       break;
 397                     }
 398                   }
 399                   if(!$HAS_ACTION_NOT_ALLOWED){
 400                     $actions[$module_name]["controleurs"][$controleur_name]["als"][$index_als]["is_public"] = true;
 401                   }
 402                 }
 403               }
 404             }
 405           }
 406           return $actions;
 407         }
 408       }
 409       return array();
 410     }
 411
 412   }
 413
 414 ?>