maj version dans config.xml
[mtweb] / mw / app / data / modules / sql / mw_data_sql_users.php
1 <?php
2
3   class mw_data_sql_users extends mw_data{
4
5     public $users;
6     public $user;
7     public $roles;
8     public $actions_roles;
9
10     # ----------------------------------------------------------------------------------------
11     #                                                                                    users
12     #
13
14     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public function roles(){
197       if(!isset($this->roles)) return false;
198       return $this->roles;
199     }
200
201     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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     public 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   }