DatabaseService.cs
9.88 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
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
using Hh.Mes.Common.log;
using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.POJO.WebEntity.equipment;
using Microsoft.IdentityModel.Logging;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Hh.Mes.Common.Exel.NpoiExcelImportHelper;
namespace HH.Data.Excel.SqlHelp
{
public class DatabaseService
{
private static readonly Lazy<DatabaseService> _instance = new Lazy<DatabaseService>(() => new DatabaseService());
private SqlSugarDbContext DBContext { get; }
private DatabaseService()
{
string connectionString = ConfigurationManager.ConnectionStrings["BaseDBContext"].ConnectionString;
DBContext = new SqlSugarDbContext(connectionString);
}
public static DatabaseService Instance => _instance.Value;
/// <summary>
/// 查询项目列表
/// </summary>
/// <returns></returns>
public List<base_project> GetProjectList()
{
return DBContext.QueryableList<base_project>().OrderByDescending(x => x.createTime).ToList();
}
/// <summary>
/// 查询厂房列表
/// </summary>
/// <returns></returns>
public List<base_factory> GetFactoryList(Guid projectCode)
{
return DBContext.QueryableList<base_factory>(x => x.projectKeys == projectCode).ToList();
}
/// <summary>
/// 查询设备类型
/// </summary>
/// <returns></returns>
public List<daq_equipment_type> GetEqTypeList()
{
return DBContext.QueryableList<daq_equipment_type>(x => !SystemVariable.IotNotContainDevice.Contains(x.Code)).ToList();
}
/// <summary>
/// 查询设备类型
/// </summary>
/// <returns></returns>
public List<daq_equipment_type_prop_template> GetEqPropTemplateList(int equipmentTypeId)
{
return DBContext.QueryableList<daq_equipment_type_prop_template>(x => x.EquipmentTypeId == equipmentTypeId).ToList();
}
/// <summary>
/// 设备类型属性 保存 base_equipment，daq_equipment，daq_equipment_prop
/// </summary>
/// <returns></returns>
public Tuple<bool, string> DefaultSave(base_equipment eq, daq_equipment daqEq, List<daq_equipment_prop> prop)
{
try
{
var isBaseEq = DBContext.QueryableList<base_equipment>()
.Any(x => x.projectKeys == eq.projectKeys && x.ip == eq.ip && x.equipmentName == eq.equipmentName && x.equipmentTypeCode == eq.equipmentTypeCode);
if (isBaseEq)
{
return new Tuple<bool, string>(false, $"{Program._mainWindow._sysIpFileName}文件导入的设备,数据库存在相同的项目key:{eq.projectKeys}-ip:{eq.ip}-设备名称:{eq.equipmentName}-设备类型:{eq.equipmentTypeCode}");
}
var isDaqEq = DBContext.QueryableList<daq_equipment>()
.Any(x => x.iP == daqEq.iP && x.name == daqEq.name && x.equipmentTypeId == daqEq.equipmentTypeId && x.projectCode == daqEq.projectCode);
if (isDaqEq)
{
return new Tuple<bool, string>(false, $"{Program._mainWindow._sysIpFileName}文件导入的设备,数据库存在相同的Daq项目编码:{daqEq.projectCode}-设备名称:{daqEq.name}-设备类型ID:{daqEq.equipmentTypeId}-ip:{daqEq.iP}");
}
// 开启事务
DBContext._db.Ado.BeginTran();
DBContext._db.Insertable(eq).ExecuteCommand();
var eqId = DBContext._db.Insertable(daqEq).ExecuteReturnIdentity();
for (int i = 0; i < prop.Count; i++)
{
prop[i].EquipmentId = eqId;
}
DBContext._db.Insertable(prop).ExecuteCommand();
// 提交事务
DBContext._db.Ado.CommitTran();
return new Tuple<bool, string>(true, SystemVariable.dataActionOk);
}
catch (Exception ex)
{
// 事务回滚
DBContext._db.Ado.RollbackTran();
return new Tuple<bool, string>(false, $"设备默认属性保存失败:" + ex.ToString());
}
}
#region 插入设备、设备属性表数据
public ResponseNew JudgeData(Dictionary<string, SysDtAllCls> sourceData)
{
ResponseNew response = new ResponseNew();
try
{
// 验证 sourceData 是否为空
if (!IsSourceDataValid(sourceData, response)) return response;
// 判断设备表和设备属性表是否为空
if (!GetEquipAndPropTables(sourceData, response)) return response;
// 验证设备表数据
var daqEquipment = ValidateEquipTable(sourceData["设备列表"].dtData, response);
if (daqEquipment == null) return response;
// 开启事务
DBContext._db.BeginTran();
// 插入设备信息
InsertEquipment(daqEquipment);
// 获取插入的设备 ID
int equipId = daqEquipment.id;
// 插入设备属性信息
if (!InsertEquipmentProps(sourceData["设备属性"].dtData, equipId, response))
{
// 事务回滚
DBContext._db.Ado.RollbackTran();
return response;
}
// 提交事务
DBContext._db.Ado.CommitTran();
response.ResponseSuccess($"数据插入成功;");
}
catch (Exception ex)
{
// 发生异常，事务回滚
DBContext._db.Ado.RollbackTran();
return response.ResponseError(ex.Message);
}
return response;
}
private bool IsSourceDataValid(Dictionary<string, SysDtAllCls> sourceData, ResponseNew response)
{
if (sourceData.Values.Count == 0)
{
response.ResponseError($"表数据为空");
return false;
}
return true;
}
private bool GetEquipAndPropTables(Dictionary<string, SysDtAllCls> sourceData, ResponseNew response)
{
// 判断设备表和设备属性表是否有数据
foreach (var data in sourceData)
{
if (data.Key == "设备列表" && data.Value.dtData.Rows.Count < 1)
{
response.ResponseError("表没有设备列表数据");
return false;
}
if (data.Key == "设备属性" && data.Value.dtData.Rows.Count < 1)
{
response.ResponseError("表没有设备属性数据");
return false;
}
}
return true;
}
private daq_equipment ValidateEquipTable(DataTable equipTable, ResponseNew response)
{
if (equipTable == null || equipTable.Rows.Count == 0)
{
response.ResponseError("设备列表数据不能为空");
return null;
}
List<daq_equipment> equipList = DBContext._db.Utilities.DataTableToList<daq_equipment>(equipTable);
if (equipList.Count == 0)
{
response.ResponseError("设备列表数据解析失败");
return null;
}
daq_equipment daqEquipment = equipList.First();
if (daqEquipment.equipmentTypeId != 19)
{
response.ResponseError("设备类型必须为输送线（19）");
return null;
}
var isHaveEquipment = DBContext._db.Queryable<daq_equipment>().Any(x => x.name == daqEquipment.name && x.iP == daqEquipment.iP && x.projectCode == daqEquipment.projectCode);
if (isHaveEquipment)
{
response.ResponseError("设备编码已存在");
return null;
}
if (!DBContext._db.Queryable<base_project>().Any(x => x.projectCode == daqEquipment.projectCode))
{
response.ResponseError("项目编码不存在");
return null;
}
if (!DBContext._db.Queryable<base_factory>().Any(x => x.factoryCode == daqEquipment.factoryCode))
{
response.ResponseError("厂房编码不存在");
return null;
}
return daqEquipment;
}
private void InsertEquipment(daq_equipment daqEquipment)
{
daqEquipment.created = DateTime.Now;
daqEquipment.createdBy = "Excel导入";
daqEquipment.id = DBContext._db.Insertable(daqEquipment).ExecuteReturnIdentity();
}
private bool InsertEquipmentProps(DataTable propTable, int equipId, ResponseNew response)
{
try
{
List<daq_equipment_prop> propList = DBContext._db.Utilities.DataTableToList<daq_equipment_prop>(propTable);
foreach (var item in propList)
{
item.EquipmentId = equipId;
item.Created = DateTime.Now;
item.CreatedBy = "Excel导入";
}
DBContext._db.Insertable(propList).ExecuteCommand();
return true;
}
catch (Exception ex)
{
response.ResponseError($"设备属性数据插入失败: {ex.Message}");
return false;
}
}
#endregion
}
}