using System;
|
using System.Collections.Generic;
|
using System.Collections.ObjectModel;
|
using System.Data;
|
using System.IO;
|
using HMI.Core.Util;
|
using HMI.MenuManage.Entity;
|
using HMI.MenuManage.Util;
|
using HMI.Core.Entity;
|
|
namespace HMI.MenuManage.Model
|
{
|
public class UserManageModel : NotifyBase
|
{
|
public SqliteHelper sqliteHelper;
|
|
private ObservableCollection<UserData> _DataSource;
|
|
public ObservableCollection<UserData> DataSource
|
{
|
get { return _DataSource; }
|
set { Set(ref _DataSource, value); }
|
}
|
private UserData _SelectedUser;
|
|
public UserData SelectedUser
|
{
|
get { return _SelectedUser; }
|
set
|
{
|
Set(ref _SelectedUser, value);
|
LoadUserRole();
|
}
|
}
|
|
private ObservableCollection<UserRoleRel> _Roles;
|
public ObservableCollection<UserRoleRel> Roles
|
{
|
get => _Roles;
|
set { Set(ref _Roles, value); }
|
}
|
|
public UserManageModel()
|
{
|
if (!File.Exists(PathHelper.DatabasePath))
|
{
|
SqliteHelper.CreateDatabase(PathHelper.DatabasePath);
|
}
|
sqliteHelper = new SqliteHelper(PathHelper.DatabasePath);
|
sqliteHelper.ExecuteSql(@"CREATE TABLE IF NOT EXISTS user (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
code TEXT NOT NULL,
|
username TEXT NOT NULL,
|
password TEXT NOT NULL,
|
name TEXT,
|
UNIQUE(username)
|
);
|
CREATE TABLE IF NOT EXISTS user_role_rel (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
username TEXT NOT NULL,
|
roleCode TEXT NOT NULL,
|
UNIQUE(username,roleCode)
|
);
|
");
|
}
|
|
public void ImportUser(List<UserData> users)
|
{
|
List<string> batch = new List<string>();
|
foreach (var user in users)
|
{
|
string sql = $"SELECT 1 FROM user WHERE username='{user.Username}' OR code='{user.Code}' OR name='{user.Name}'";
|
if (sqliteHelper.HasData(sql))
|
{
|
throw new Exception($"User {user.Username} already exists.");
|
}
|
string codes = "''";
|
string roleCodes = user.RoleCodes?.Trim();
|
if (!string.IsNullOrEmpty(roleCodes))
|
{
|
codes = $"'{roleCodes.Replace(",", "','")}'";
|
}
|
batch.Add($"INSERT INTO user (code,username,name,password) VALUES ('{user.Code}', '{user.Username}', '{user.Name}', '{user.Password}');");
|
batch.Add($"INSERT INTO user_role_rel (username, roleCode) SELECT '{user.Username}', code FROM role WHERE code IN ({codes});");
|
}
|
sqliteHelper.ExecuteBatchSql(batch);
|
}
|
|
public void LoadData()
|
{
|
ObservableCollection<UserData> data = new ObservableCollection<UserData>();
|
DataTable dt = sqliteHelper.GetDataTable("SELECT * FROM user Order By id;");
|
if (dt != null)
|
{
|
foreach (DataRow dr in dt.Rows)
|
{
|
var menu = new UserData
|
{
|
Id = dr["id"].ToString(),
|
Code = dr["code"].ToString(),
|
Username = dr["username"].ToString(),
|
Password = dr["password"].ToString(),
|
Name = dr["name"].ToString()
|
};
|
DataTable roles = sqliteHelper.GetDataTable($@"SELECT r.code AS code FROM user_role_rel AS rel
|
INNER JOIN role AS r ON rel.roleCode = r.code AND rel.username = '{menu.Username}' Order By r.code;");
|
List<string> codes = new List<string>();
|
foreach (DataRow row in roles.Rows)
|
{
|
if (row["code"] != DBNull.Value)
|
{
|
codes.Add(row["code"].ToString());
|
}
|
}
|
menu.RoleCodes = string.Join(",", codes);
|
data.Add(menu);
|
}
|
}
|
DataSource = data;
|
}
|
|
public void LoadUserRole()
|
{
|
Roles = SelectedUser == null ? new ObservableCollection<UserRoleRel>()
|
: UserRoleRelHelper.SelectUserRoleData(sqliteHelper, SelectedUser.Username);
|
}
|
|
public void SaveUserRole()
|
{
|
UserRoleRelHelper.SaveUserRoleRel(sqliteHelper, Roles, SelectedUser, null, null);
|
}
|
|
public void DeleteUserByUsername(string username)
|
{
|
sqliteHelper.ExecuteBatchSql(new List<string> {
|
$"DELETE FROM user WHERE username = '{username}';",
|
$"DELETE FROM user_role_rel WHERE username = '{username}';"
|
});
|
}
|
|
public void DeleteUserByUsernames(IEnumerable<string> usernames)
|
{
|
List<string> sqls = new List<string>();
|
foreach (string username in usernames)
|
{
|
sqls.Add($"DELETE FROM user WHERE username = '{username}';");
|
sqls.Add($"DELETE FROM user_role_rel WHERE username = '{username}';");
|
}
|
sqliteHelper.ExecuteBatchSql(sqls);
|
}
|
}
|
}
|
