diff options
Diffstat (limited to 'modules-available/permissionmanager/inc')
3 files changed, 66 insertions, 90 deletions
diff --git a/modules-available/permissionmanager/inc/getpermissiondata.inc.php b/modules-available/permissionmanager/inc/getpermissiondata.inc.php index 5114f4ef..9d69c722 100644 --- a/modules-available/permissionmanager/inc/getpermissiondata.inc.php +++ b/modules-available/permissionmanager/inc/getpermissiondata.inc.php @@ -8,8 +8,8 @@ class GetPermissionData { $userdata= array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $userdata[$row['userid'].' '.$row['login']][] = array( - 'roleId' => $row['roleId'], - 'roleName' => $row['roleName'] + 'roleid' => $row['roleid'], + 'rolename' => $row['rolename'] ); } $data = array(); @@ -26,60 +26,51 @@ class GetPermissionData { // get LocationIDs, Location Names, Roles of each Location public static function getLocationData() { - $res = self::queryLocationData(); - $locdata = array(); + $res = Database::simpleQuery("SELECT role.roleid as roleid, rolename, GROUP_CONCAT(locationid) as locationids FROM role + LEFT JOIN (SELECT roleid, COALESCE(locationid, 0) AS locationid FROM role_x_location) rxl + ON role.roleid = rxl.roleid GROUP BY roleid ORDER BY rolename ASC"); + $locations = Location::getLocations(0, 0, false, true); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $locdata[$row['locid'].' '.$row['locname']][] = array( - 'roleId' => $row['roleId'], - 'roleName' => $row['roleName'] - ); + $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'] + ); + } } - $data = array(); - foreach($locdata AS $loc => $roles) { - $loc = explode(" ", $loc, 2); - $data[] = array( - 'locid' => $loc[0], - 'locname' => $loc[1], - 'roles' => $roles - ); - } - return $data; + return array_values($locations); } // get all roles from database (id and name) public static function getRoles() { - $res = Database::simpleQuery("SELECT id, name FROM role ORDER BY name ASC"); + $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['id'], - 'roleName' => $row['name'] + 'roleid' => $row['roleid'], + 'rolename' => $row['rolename'] ); } 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)); + 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['locid']; + $data["locations"][] = $row['locationid']; } - $query = "SELECT roleid, permissionid FROM role_x_permission WHERE roleid = :roleId"; - $res = Database::simpleQuery($query, array("roleId" => $roleId)); + $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']; @@ -89,21 +80,10 @@ class GetPermissionData { // 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 + $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.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 + LEFT JOIN role ON user_x_role.roleid = role.roleid "); return $res; } diff --git a/modules-available/permissionmanager/inc/permissiondbupdate.inc.php b/modules-available/permissionmanager/inc/permissiondbupdate.inc.php index 87c989fa..f144b35e 100644 --- a/modules-available/permissionmanager/inc/permissiondbupdate.inc.php +++ b/modules-available/permissionmanager/inc/permissiondbupdate.inc.php @@ -4,53 +4,49 @@ class PermissionDbUpdate { // insert new user_x_role to database. "ignore" to ignore duplicate entry try public static function addRoleToUser($users, $roles) { - foreach($users AS $user) { - foreach ($roles AS $role) { - $query = "INSERT IGNORE INTO user_x_role (userid, roleid) VALUES (:user, :role)"; - Database::exec($query, array("user" => $user, "role" => $role)); + $query = "INSERT IGNORE INTO user_x_role (userid, roleid) VALUES (:userid, :roleid)"; + foreach($users AS $userid) { + foreach ($roles AS $roleid) { + Database::exec($query, array("userid" => $userid, "roleid" => $roleid)); } } } // remove user_x_role entry from database public static function removeRoleFromUser($users, $roles) { - foreach($users AS $user) { - foreach ($roles AS $role) { - $query = "DELETE FROM user_x_role WHERE userid = :user AND roleid = :role"; - Database::exec($query, array("user" => $user, "role" => $role)); - } - } + $query = "DELETE FROM user_x_role WHERE userid IN (:users) AND roleid IN (:roles)"; + Database::exec($query, array("users" => $users, "roles" => $roles)); } // delete role, delete user_x_role relationships, delete role_x_location relationships, delete role_x_permission relationships - public static function deleteRole($id) { - $query = "DELETE FROM role WHERE id = :id"; - Database::exec($query, array("id" => $id)); - $query = "DELETE FROM user_x_role WHERE roleid = :id"; - Database::exec($query, array("id" => $id)); - $query = "DELETE FROM role_x_location WHERE roleid = :id"; - Database::exec($query, array("id" => $id)); - $query = "DELETE FROM role_x_permission WHERE roleid = :id"; - Database::exec($query, array("id" => $id)); + public static function deleteRole($roleid) { + $query = "DELETE FROM role WHERE roleid = :roleid"; + Database::exec($query, array("roleid" => $roleid)); + $query = "DELETE FROM user_x_role WHERE roleid = :roleid"; + Database::exec($query, array("roleid" => $roleid)); + $query = "DELETE FROM role_x_location WHERE roleid = :roleid"; + Database::exec($query, array("roleid" => $roleid)); + $query = "DELETE FROM role_x_permission WHERE roleid = :roleid"; + Database::exec($query, array("roleid" => $roleid)); } - public static function saveRole($roleName, $locations, $permissions, $role = NULL) { - if ($role) { - Database::exec("UPDATE role SET name = :roleName WHERE id = :role", - array("roleName" => $roleName, "role" => $role)); - Database::exec("DELETE FROM role_x_location WHERE roleid = :role", array("role" => $role)); - Database::exec("DELETE FROM role_x_permission WHERE roleid = :role", array("role" => $role)); + public static function saveRole($rolename, $locations, $permissions, $roleid = NULL) { + if ($roleid) { + Database::exec("UPDATE role SET rolename = :rolename WHERE roleid = :roleid", + array("rolename" => $rolename, "roleid" => $roleid)); + Database::exec("DELETE FROM role_x_location WHERE roleid = :roleid", array("roleid" => $roleid)); + Database::exec("DELETE FROM role_x_permission WHERE roleid = :roleid", array("roleid" => $roleid)); } else { - Database::exec("INSERT INTO role (name) VALUES (:roleName)", array("roleName" => $roleName)); - $role = Database::lastInsertId(); + Database::exec("INSERT INTO role (rolename) VALUES (:rolename)", array("rolename" => $rolename)); + $roleid = Database::lastInsertId(); } - foreach ($locations as $locID) { - Database::exec("INSERT INTO role_x_location (roleid, locid) VALUES (:role, :locid)", - array("role" => $role, "locid" => $locID)); + foreach ($locations as $locationid) { + Database::exec("INSERT INTO role_x_location (roleid, locationid) VALUES (:roleid, :locationid)", + array("roleid" => $roleid, "locationid" => $locationid)); } - foreach ($permissions as $permission) { - Database::exec("INSERT INTO role_x_permission (roleid, permissionid) VALUES (:role, :permission)", - array("role" => $role, "permission" => $permission)); + foreach ($permissions as $permissionid) { + Database::exec("INSERT INTO role_x_permission (roleid, permissionid) VALUES (:roleid, :permissionid)", + array("roleid" => $roleid, "permissionid" => $permissionid)); } } diff --git a/modules-available/permissionmanager/inc/permissionutil.inc.php b/modules-available/permissionmanager/inc/permissionutil.inc.php index 5c3eef58..17257eec 100644 --- a/modules-available/permissionmanager/inc/permissionutil.inc.php +++ b/modules-available/permissionmanager/inc/permissionutil.inc.php @@ -11,7 +11,7 @@ class PermissionUtil } $res = Database::simpleQuery("SELECT role_x_permission.permissionid as 'permissionid', - role_x_location.locid as 'locationid' + role_x_location.locationid as 'locationid' FROM user_x_role INNER JOIN role_x_permission ON user_x_role.roleid = role_x_permission.roleid LEFT JOIN role_x_location ON role_x_permission.roleid = role_x_location.roleid @@ -29,11 +29,11 @@ class PermissionUtil public static function getAllowedLocations($userid, $permissionid) { - $res = Database::simpleQuery("SELECT role_x_permission.permissionid as 'permissionid', - role_x_location.locid as 'locationid' + $res = Database::simpleQuery("SELECT role_x_permission.permissionid, rxl.locationid FROM user_x_role INNER JOIN role_x_permission ON user_x_role.roleid = role_x_permission.roleid - LEFT JOIN role_x_location ON role_x_permission.roleid = role_x_location.roleid + LEFT JOIN (SELECT roleid, COALESCE(locationid, 0) AS locationid FROM role_x_location) rxl + ON role_x_permission.roleid = rxl.roleid WHERE user_x_role.userid = :userid", array("userid" => $userid)); $allowedLocations = array(); @@ -53,7 +53,7 @@ class PermissionUtil return $allowedLocations; } - private static function getSublocations($tree, $locations) { + public static function getSublocations($tree, $locations) { $result = array_flip($locations); foreach ($tree as $location) { if (array_key_exists("children", $location)) { |