ImportService.cs
8.22 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
using Hh.Mes.Common;
using Hh.Mes.Common.DataTableTo;
using Hh.Mes.Common.Exel;
using Hh.Mes.Common.Json;
using Hh.Mes.Common.log;
using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.Service.Repository;
using Hh.Mes.Service.WebService.Planned;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using static Hh.Mes.Common.Exel.NpoiExcelImportHelper;
namespace Hh.Mes.Service.Planned
{
/// <summary>
/// 导入Excel 公共方法 这里使用了反射，字典 动态执行注册方法
/// 1：ImportOhter类 ExcelName 定义 Excel页签的名称 对应数据库表
/// 2：ImportOhter类 TableName 定义表名
/// 3: ImportMethod 实体类 写对应的导入方法
/// </summary>
public class ImportService : RepositorySqlSugar<base_factory>
{
#region 属性
/// <summary>
/// 方法 需要加上特性
/// </summary>
private Dictionary<string, MethodInfo> sysDicMethod { get; set; }
/// <summary>
/// 导入Excel文件数据源 key 是文件名，value 是数据源
/// ps：【keys】 保存数据的时候只取后缀包含 string tableSuffix = "_source"的中间过程可能存在加工
/// </summary>
private Dictionary<string, SysDtAllCls> sysDicDtSource { get; set; }
private Dictionary<string, Action<DateTime>> ImportInSaveDataSuccess { get; set; }
private DateTime sysNowTime { get; set; }
private string materialDtKey { get; set; }
#endregion
#region 构造函数 初始化
public ImportService()
{
Init();
RegisterDicMethod();
}
private void Init()
{
sysDicDtSource = new Dictionary<string, SysDtAllCls>();
sysNowTime = DateTime.Now;
ImportInSaveDataSuccess = new Dictionary<string, Action<DateTime>>
{
{ ExcelName.sys_user.ToString(), (sysNowTime) => { new ImportMethod().SaveSuccessAfterSysUesr(sysNowTime, base.sysWebUser); } },
{ ExcelName.base_process_route_head.ToString(), (sysNowTime) => { new ImportMethod().SaveSuccessAfterBaseProcessRoute(sysDicDtSource,base.sysWebUser); } },
}; ;
}
/// <summary>
/// 注册excel 表逻辑读取、逻辑运算方法
/// </summary>
private void RegisterDicMethod()
{
sysDicMethod = ActionAttribute.GetMethods(typeof(ImportMethod));
}
#endregion
#region 读取excel 文件数据 保存到 dicDtSource
/// <summary>
/// 导入
/// </summary>
public dynamic ImportIn(IFormFileCollection excelfile, IHostingEnvironment hostingEnvironment)
{
var response = new Response();
return ExceptionsHelp.Instance.ExecuteT<string>(() =>
{
#region Excel 读取 保存到dt
foreach (var file in excelfile)
{
var fileSuffix = Path.GetExtension(file.FileName);
var key = Path.GetFileNameWithoutExtension(file.FileName);
var dic = NpoiExcelImportHelper._.ExcelToDataTable(file.OpenReadStream(), fileSuffix, out var result, out var resultMsg);
foreach (var item in dic)
{
sysDicDtSource.Add(item.Key, item.Value);
}
if (result) continue;
response.Code = 500;
response.Message = resultMsg;
return response.ToJson();
}
#endregion
#region 调用导入处理方法 key: dict[key]
var objImportMethod = new ImportMethod();
var isExtMethod = false;
for (int i = 0; i < sysDicDtSource.Count; i++)
{
(string key, SysDtAllCls value) = sysDicDtSource.ElementAt(i);
if (sysDicMethod.ContainsKey(key))
{
isExtMethod = true;
response = sysDicMethod[key].Invoke(objImportMethod, new object[] { sysDicDtSource, base.sysWebUser, sysNowTime }) as Response;
if (response == null) throw new Exception(SystemVariable.dataActionError + "--ImportIn");
if (response.Code == 200) continue;
return response.ToJson();
}
}
if (!isExtMethod)
{
sysDicDtSource = null;
sysDicMethod = null;
response.Code = 500;
response.Message = "【ImportMethod】未实现导入方法，或者Excel文件 sheet页签名称和ExcelName不一致,请核实!";
return response.ToJson();
}
#endregion
#region 导入数据判断
#endregion
//保存数据源
return ImportInSaveData(response);
});
}
#endregion
#region 保存
/// <summary>
/// 写入数据源之前判断
/// </summary>
public Response ImportInSaveDataBefore(Response response)
{
//公共物料编码dt判断
if (sysDicDtSource[materialDtKey].dtData.Rows.Count > 0)
{
sysDicDtSource[materialDtKey].dtData = sysDicDtSource[materialDtKey].dtData.Distinct("materialCode");
var materialdt = sysDicDtSource[materialDtKey].dtData;
for (int i = materialdt.Rows.Count - 1; i >= 0; i--)
{
var index = i;
if (Context.Queryable<base_material>().Where(u => u.materialCode == materialdt.Rows[index]["materialCode"].ToString()).Any())
{
materialdt.Rows.Remove(materialdt.Rows[i]);
}
}
}
return response;
}
/// <summary>
/// 保存数据 只保存key 后缀包含TableName.tableSuffix
/// </summary>
public string ImportInSaveData(Response response)
{
//Dictionary<string, object> tempData = new Dictionary<string, object>();
try
{
Context.Ado.BeginTran();
foreach (var item in sysDicDtSource)
{
if (item.Key.IndexOf(TableName.tableSuffix, StringComparison.Ordinal) > 0)
{
if (item.Value.dtData.Rows.Count > 0)
{
var list = base.Context.Utilities.DataTableToDictionaryList(item.Value.dtData);
base.Context.Insertable(list).AS(item.Value.dtData.TableName).ExecuteCommand();
//后续如果此处数据量超大 ，改写sql BulkCopy
//var dt = item.Value;
//base.Context.Insertable(dt).UseSqlServer().ExecuteBulkCopy();
}
}
}
Context.Ado.CommitTran();
response.Code = 200;
response.Message = SystemVariable.excelDataOk;
ImportInSaveDataSuccessAfter();
}
catch (Exception ex)
{
Context.Ado.RollbackTran();
var tempMes = ex.ToString().StrJsonReplace();
tempMes = tempMes.Substring(1, 160);
response.Message = tempMes;
response.Code = 500;
}
return response.ToJson();
}
public void ImportInSaveDataSuccessAfter()
{
for (int i = 0; i < sysDicDtSource.Count; i++)
{
(string key, SysDtAllCls value) = sysDicDtSource.ElementAt(i);
if (ImportInSaveDataSuccess.ContainsKey(key))
{
ImportInSaveDataSuccess[key].Invoke(sysNowTime);
}
}
}
#endregion
}
}