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
|
<?php
class GetPermissionData {
// get UserIDs, User Login Names, User Roles
public static function getUserData() {
$res = self::queryUserData();
$data = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$data[] = array(
'userid' => $row['userid'],
'name' => $row['login'],
'role' => explode(",",$row['role'])
);
}
return $data;
}
// get LocationIDs, Location Names, Roles of each Location
public static function getLocationData() {
$res = self::queryLocationData();
$data = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$data[] = array(
'locid' => $row['locid'],
'name' => $row['locname'],
'role' => explode(",",$row['role'])
);
}
return $data;
}
// get all roles from database (id and name)
public static function getRoles() {
$res = Database::simpleQuery("SELECT id, name FROM role ORDER BY name ASC");
$data = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$data[] = array(
'roleId' => $row['id'],
'roleName' => $row['name']
);
}
return $data;
}
public static function getLocations($selected) {
$res = Database::simplequery("SELECT locationid, locationname FROM location");
$data = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$data[] = array('locid' => $row['locationid'], 'locName' => $row['locationname'],
'selected' => in_array($row['locationid'], $selected) ? "selected" : "");
}
return $data;
}
public static function getRoleData($roleId) {
$query = "SELECT id, name FROM role WHERE id = :roleId";
$data = Database::queryFirst($query, array("roleId" => $roleId));
$query = "SELECT roleid, locid 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['locid'];
}
$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, GROUP_CONCAT(role.name ORDER BY role.name ASC) AS role
FROM user
LEFT JOIN user_x_role ON user.userid = user_x_role.userid
LEFT JOIN role ON user_x_role.roleid = role.id
GROUP BY user.userid
");
return $res;
}
// LocationID, Location Name, Roles of each Location
private static function queryLocationData() {
$res = Database::simpleQuery("SELECT location.locationid AS locid, location.locationname AS locname, GROUP_CONCAT(role.name ORDER BY role.name ASC) AS role
FROM location
LEFT JOIN role_x_location ON location.locationid = role_x_location.locid
LEFT JOIN role ON role_x_location.roleid = role.id
GROUP BY location.locationid
ORDER BY location.locationname
");
return $res;
}
}
|
