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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
<?php
class GetPermissionData
{
const WITH_USER_COUNT = 1;
const WITH_LOCATION_COUNT = 2;
/**
* Get data for all users.
*
* @return array array of users (each with userid, username and roles (each with roleid and rolename))
*/
public static function getUserData(): array
{
$res = Database::simpleQuery("SELECT user.userid AS userid, user.login AS login, role.rolename AS rolename, role.roleid AS roleid
FROM user
LEFT JOIN role_x_user ON user.userid = role_x_user.userid
LEFT JOIN role ON role_x_user.roleid = role.roleid
");
$userdata = array();
foreach ($res as $row) {
$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 data for all locations.
*
* @return array array of locations (each including the roles that have permissions for them)
*/
public static function getLocationData(): array
{
$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 role.roleid, rolename ORDER BY rolename ASC");
$locations = Location::getLocations(0, 0, true, true);
$tree = Location::getLocationsAssoc();
$locations[0]['locationname'] = Dictionary::translate('global', true);
foreach ($res as $role) {
$locationids = explode(",", $role['locationids']);
if (in_array(0, $locationids)) {
$locationids = [0];
} else {
foreach ($locationids as $locationId) {
if (isset($tree[$locationId])) {
$locationids = array_merge($locationids, $tree[$locationId]['children']);
} else {
error_log("Unknown $locationId: " . json_encode($role));
}
}
}
foreach ($locationids as $locationid) {
if (!isset($locations[$locationid])) {
$locations[$locationid] = ['locationname' => $locationid, 'locationid' => $locationid];
}
$locations[$locationid]['roles'][] = array(
'roleid' => $role['roleid'],
'rolename' => $role['rolename']
);
}
}
return array_values($locations);
}
/**
* Get all roles.
*
* @param int $flags Bitmask specifying additional data to fetch (WITH_* constants of this class)
* @return array array roles (each with roleid and rolename)
*/
public static function getRoles(int $flags = 0): array
{
$cols = $joins = '';
if ($flags & self::WITH_USER_COUNT) {
$cols .= ', Count(DISTINCT rxu.userid) AS users';
$joins .= ' LEFT JOIN role_x_user rxu ON (r.roleid = rxu.roleid)';
}
if ($flags & self::WITH_LOCATION_COUNT) {
$cols .= ', Count(DISTINCT rxl.locationid) AS locations';
$joins .= ' LEFT JOIN role_x_location rxl ON (r.roleid = rxl.roleid)';
}
if (!empty($joins)) {
$joins .= ' GROUP BY r.roleid';
}
return Database::queryAll("SELECT r.roleid, r.rolename, r.builtin, r.roledescription $cols FROM role r
$joins
ORDER BY rolename ASC");
}
/**
* Get permissions and locations for a given role.
*
* @param string $roleid id of the role
* @return ?array array containing an array of permissions and an array of locations, null if not found
*/
public static function getRoleData(string $roleid): ?array
{
$data = self::getRole($roleid);
$res = Database::simpleQuery("SELECT roleid, locationid FROM role_x_location WHERE roleid = :roleid",
array("roleid" => $roleid));
if ($res === false)
return null;
$data["locations"] = array();
foreach ($res as $row) {
$data["locations"][] = $row['locationid'];
}
$res = Database::simpleQuery("SELECT roleid, permissionid FROM role_x_permission WHERE roleid = :roleid",
array("roleid" => $roleid));
$data["permissions"] = array();
foreach ($res as $row) {
$data["permissions"][] = $row['permissionid'];
}
return $data;
}
public static function getRole($roleId)
{
return Database::queryFirst("SELECT roleid, rolename, builtin, roledescription FROM role
WHERE roleid = :roleid", ['roleid' => $roleId]);
}
}
|
