[permissionmanager] added key relationships to install script;

changed nested php for loops to sql code; standardized sql column naming;
small bugfixes;

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)) {