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
|
<?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 = self::queryLocationData();
$locdata = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$locdata[$row['locid'].' '.$row['locname']][] = array(
'roleId' => $row['roleId'],
'roleName' => $row['roleName']
);
}
$data = array();
foreach($locdata AS $loc => $roles) {
$loc = explode(" ", $loc, 2);
$data[] = array(
'locid' => $loc[0],
'locname' => $loc[1],
'roles' => $roles
);
}
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, role.name AS roleName, role.id 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.id
");
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, role.name AS roleName, role.id AS roleId
FROM location
LEFT JOIN role_x_location ON location.locationid = role_x_location.locid
LEFT JOIN role ON role_x_location.roleid = role.id
ORDER BY location.locationname
");
return $res;
}
}
|
