1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
|
<?php
class GetPermissionData {
// get UserIDs, User Login Names, User Roles
public static function getUserData() {
$res = self::queryUserData();
$userdata= array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$userdata[$row['userid'].' '.$row['login']][] = array(
'roleid' => $row['roleid'],
'rolename' => $row['rolename']
);
}
$data = array();
foreach($userdata AS $user => $roles) {
$user = explode(" ", $user, 2);
$data[] = array(
'userid' => $user[0],
'username' => $user[1],
'roles' => $roles
);
}
return $data;
}
// get LocationIDs, Location Names, Roles of each Location
public static function getLocationData() {
$res = Database::simpleQuery("SELECT role.roleid as roleid, rolename, GROUP_CONCAT(COALESCE(locationid, 0)) AS locationids FROM role
INNER JOIN role_x_location ON role.roleid = role_x_location.roleid GROUP BY roleid ORDER BY rolename ASC");
$locations = Location::getLocations(0, 0, false, true);
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$locationids = explode(",", $row['locationids']);
if (in_array("0", $locationids)) {
$locationids = array_map("intval", Location::extractIds(Location::getTree()));
} else {
$locationids = PermissionUtil::getSublocations(Location::getTree(), $locationids);
}
foreach ($locationids as $locationid) {
$locations[$locationid]['roles'][] = array(
'roleid' => $row['roleid'],
'rolename' => $row['rolename']
);
}
}
return array_values($locations);
}
// get all roles from database (id and name)
public static function getRoles() {
$res = Database::simpleQuery("SELECT roleid, rolename FROM role ORDER BY rolename ASC");
$data = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$data[] = array(
'roleid' => $row['roleid'],
'rolename' => $row['rolename']
);
}
return $data;
}
public static function getRoleData($roleid) {
$query = "SELECT roleid, rolename FROM role WHERE roleid = :roleid";
$data = Database::queryFirst($query, array("roleid" => $roleid));
$query = "SELECT roleid, locationid FROM role_x_location WHERE roleid = :roleid";
$res = Database::simpleQuery($query, array("roleid" => $roleid));
$data["locations"] = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$data["locations"][] = $row['locationid'];
}
$query = "SELECT roleid, permissionid FROM role_x_permission WHERE roleid = :roleid";
$res = Database::simpleQuery($query, array("roleid" => $roleid));
$data["permissions"] = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$data["permissions"][] = $row['permissionid'];
}
return $data;
}
// UserID, User Login Name, Roles of each User
private static function queryUserData() {
$res = Database::simpleQuery("SELECT user.userid AS userid, user.login AS login, role.rolename AS rolename, role.roleid AS roleid
FROM user
LEFT JOIN user_x_role ON user.userid = user_x_role.userid
LEFT JOIN role ON user_x_role.roleid = role.roleid
");
return $res;
}
}
|