SyncUserService.cs
7.63 KB
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
153
154
155
156
157
158
159
160
161
162
163
164
165
using Hh.Mes.Common.config;
using Hh.Mes.POJO.ApiEntity;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Text;
using Hh.Mes.Service.Repository;
using Hh.Mes.POJO.Entity;
using Hh.Mes.Common.Http;
using Hh.Mes.POJO.EnumEntitys;
using System.Net;
using Hh.Mes.Service.Logs;
using Hh.Mes.Common.Json;
using Hh.Mes.Common;
namespace Hh.Mes.Service.QuartzJobService
{
public class SyncUserService : RepositorySqlSugar<sys_user>
{
/// <summary>
/// 同步OA用户账号到 sys_user表
/// </summary>
public void Execute()
{
//1、调用OA接口；
var item = new HttpItem()
{
URL = ConfigRead.GetInstance.GetAppsetConnection().AppCustomExtend6,
Method = "post",
ContentType = "application/json",//返回类型 可选项有默认值
};
var result = new HttpHelper().GetHtml(item);
var oaType = EnumLog.OA人员接口.ToString();
if (result.StatusCode != HttpStatusCode.OK)
{
QueueInterLog.GetInstance.EnqueueInterLog(item, result, oaType, result.Html, 0, user: "SyncUserJob");
return;
}
var json = DynamicJson.Parse(result.Html);
if (json.code != 200)
{
QueueInterLog.GetInstance.EnqueueInterLog(item, result, oaType, json.msg, 0, user: "SyncUserJob");
return;
}
//2、返回结果，解密用户信息；
var userInfo = Encryption.AesDecryptOA(json.data, "e9c4b6be35f74dca");
if (string.IsNullOrEmpty(userInfo))
{
QueueInterLog.GetInstance.EnqueueInterLog(item, result, oaType, "OA用户信息解密解析失败!", 0, user: "SyncUserJob");
return;
}
List<SysncUserOaEntity> entity = JsonConvert.DeserializeObject<List<SysncUserOaEntity>>(userInfo);
// 3、新增IoT用户表，优化批量插入逻辑
// 3.1 提取所有需要插入的用户账号
var allUserAccounts = new List<string>();
foreach (var e in entity)
{
foreach (var member in e.memberInfoList)
{
if (string.IsNullOrEmpty(member.code)) continue;
string userCode = member.code.Replace("'", "''");
allUserAccounts.Add(userCode);
}
}
// 3.2 查询数据库中已存在的账号（一次性查询）
var existingAccounts = new List<string>();
if (allUserAccounts.Count > 0)
{
string accountList = string.Join("','", allUserAccounts);
existingAccounts = Context.Ado.SqlQuery<string>($"SELECT account FROM sys_user WHERE account IN ('{accountList}')");
}
// 3.3 构建批量插入SQL
StringBuilder sbInsert = new StringBuilder();
sbInsert.AppendLine(@"
set nocount on;
set xact_abort on;
begin try
if @@trancount = 0
begin transaction;");
// 3.4 只插入不存在的用户
bool hasNewUsers = false;
foreach (var e in entity)
{
foreach (var member in e.memberInfoList)
{
if (string.IsNullOrEmpty(member.code)) continue;
string userCode = member.code.Replace("'", "''");
string userName = (member.name ?? "").Replace("'", "''");
// 检查是否已存在
if (existingAccounts.Contains(userCode)) continue;
hasNewUsers = true; // 标记有新用户
sbInsert.AppendLine(@$"
IF NOT EXISTS (SELECT 1 FROM sys_user WHERE account = '{userCode}')
INSERT INTO [dbo].[sys_user]
([account],
[password],
[name],
[sex],
[status],
[remarks],
[createTime],
[createBy])
VALUES
('{userCode}',
'6EjfZkO8o92OjsH7rK+kVg==' ,
'{userName}',
1,1,'数据库任务定时作业同步OA账号',GETDATE(),'OA') ");
}
}
// 如果没有新用户，直接返回
if (!hasNewUsers)
{
// QueueInterLog.GetInstance.EnqueueInterLog(null, null, oaType, "没有新用户需要同步", 0, user: "SyncUserJob");
return;
}
//4、新增用户-部门关系表，不存在新增，存在跳过；默认部门：【长沙华恒机器人制造有限公司】 and t2.relKey = 'UserOrg'
sbInsert.AppendLine($@" insert into sys_relevance (relKey,firstId,secondId,createBy,createTime)
select UserOrg='UserOrg' ,
t1.id ,
secondId={ConfigRead.GetInstance.GetAppsetConnection().AppCustomExtend2},
createBy=t1.createBy ,
[createTime] = GETDATE()
from sys_user t1 with (nolock)
left join (select distinct firstId from sys_relevance) t2 on t1.id=t2.firstId
where t1.createBy='OA' and t2.firstId is null");
//5、新增用户-角色关系表，~; 默认角色：【普通员工】
sbInsert.AppendLine($@" insert into sys_relevance (relKey,firstId,secondId,createBy,createTime)
select UserRole='UserRole',
t1.id,
secondId={ConfigRead.GetInstance.GetAppsetConnection().AppCustomExtend3},
createBy=t1.createBy,
[createTime] = GETDATE()
from sys_user t1 with (nolock)
where t1.createBy ='OA' AND id NOT IN (SELECT firstId FROM sys_relevance where relKey='UserRole' )");
sbInsert.AppendLine(@" -- 确保只提交我们开始的事务
if @@trancount > 0 and xact_state() = 1
commit transaction;
end try
begin catch
-- 仅在事务处于活动状态时回滚
if @@trancount > 0 and xact_state() = 1
rollback transaction;
throw;
end catch");
// 执行优化后的SQL
var res = Context.Ado.ExecuteCommand(sbInsert.ToString());
// var errorMsg = $"【SqlSugar】执行SQL语句【{sbInsert.ToString()}】】";
//QueueInterLog.GetInstance.EnqueueInterLog(null, null, oaType, errorMsg, 0, user: "SyncUserJob", system: oaType.ToString());
}
}
}