优化bug；

alielie

2025-10-10 15bbd62b0b24b72c717ae91ab45348a2083ad04e

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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
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);
        }
    }
}