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); } },
}; ;
}
/// <summary>
/// 注册excel 表逻辑读取、逻辑运算方法
/// </summary>
private void RegisterDicMethod()
{
sysDicMethod = ActionAttribute.GetMethods(typeof(ImportMethod));
}
#endregion
#region 读取excel 文件数据 保存到 dicDtSource
/// <summary>
/// 导入
/// </summary>
public dynamic ImportIn(IFormFileCollection excelfile, string contentRootPath)
{
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!=null && item.Value.dtData.Rows.Count > 0)
{
if (item.Value.dtData.Columns.Contains("Id")) item.Value.dtData.Columns.Remove("Id");
if (item.Value.dtData.Columns.Contains("id")) item.Value.dtData.Columns.Remove("id");
var list = base.Context.Utilities.DataTableToDictionaryList(item.Value.dtData);
var tableName = string.IsNullOrEmpty(item.Value.dtData.TableName) ? item.Key.Replace(TableName.tableSuffix,"") : item.Value.dtData.TableName;
base.Context.Insertable(list).AS(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
}
}