From 7807e30a69ab39b3f2a7f20d2608ba6b46b456e8 Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Tue, 28 Mar 2017 11:23:24 +0200
Subject: [permission-manager] first part of UI, database table, sql queries
 and db methods

---
 .../permissionmanager/inc/dbupdate.inc.php         | 37 +++++++++++++++++++
 .../permissionmanager/inc/getdata.inc.php          | 43 ++++++++++++++++++++++
 2 files changed, 80 insertions(+)
 create mode 100644 modules-available/permissionmanager/inc/dbupdate.inc.php
 create mode 100644 modules-available/permissionmanager/inc/getdata.inc.php

(limited to 'modules-available/permissionmanager/inc')

diff --git a/modules-available/permissionmanager/inc/dbupdate.inc.php b/modules-available/permissionmanager/inc/dbupdate.inc.php
new file mode 100644
index 00000000..20ff746a
--- /dev/null
+++ b/modules-available/permissionmanager/inc/dbupdate.inc.php
@@ -0,0 +1,37 @@
+<?php
+
+class DbUpdate {
+
+	// insert new userXrole 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 userXrole (userid, roleid) VALUES ($user, $role)";
+				Database::exec($query);
+			}
+		}
+	}
+
+	// remove userXrole entry from database
+	public static function removeRoleFromUser($users, $roles) {
+		foreach($users AS $user) {
+			foreach ($roles AS $role) {
+				$query = "DELETE FROM userXrole WHERE userid = $user AND roleid = $role";
+				Database::exec($query);
+			}
+		}
+	}
+
+	// delete role, delete userXrole relationships, delete roleXlocation relationships, delete roleXpermission relationships
+	public static function deleteRole($id) {
+		$query = "DELETE FROM role WHERE id = $id";
+		Database::exec($query);
+		$query = "DELETE FROM userXrole WHERE roleid = $id";
+		Database::exec($query);
+		$query = "DELETE FROM roleXlocation WHERE roleid = $id";
+		Database::exec($query);
+		$query = "DELETE FROM roleXpermission WHERE roleid = $id";
+		Database::exec($query);
+	}
+
+}
diff --git a/modules-available/permissionmanager/inc/getdata.inc.php b/modules-available/permissionmanager/inc/getdata.inc.php
new file mode 100644
index 00000000..7eac333f
--- /dev/null
+++ b/modules-available/permissionmanager/inc/getdata.inc.php
@@ -0,0 +1,43 @@
+<?php
+
+class GetData {
+
+	// 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 all roles from database (id and name)
+	public static function getRoles() {
+		$res = Database::simpleQuery("SELECT id, name FROM role ORDER BY name");
+		$data = array();
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$data[] = array(
+				'roleId' => $row['id'],
+				'roleName' => $row['name']
+			);
+		}
+		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) AS role
+												FROM user
+													LEFT JOIN userXrole ON user.userid = userXrole.userid
+													LEFT JOIN role ON userXrole.roleid = role.id
+												GROUP BY user.userid
+												");
+		return $res;
+	}
+
+}
\ No newline at end of file
-- 
cgit v1.2.3-55-g7522