|
|
using FileDataUpload.Common;
|
|
|
using FileDataUpload.Entity;
|
|
|
using MiniExcelLibs;
|
|
|
using System;
|
|
|
using System.Collections.Generic;
|
|
|
using System.IO;
|
|
|
using System.Linq;
|
|
|
using System.Text;
|
|
|
using System.Text.RegularExpressions;
|
|
|
using System.Threading.Tasks;
|
|
|
|
|
|
namespace FileDataUpload.FileOperate
|
|
|
{
|
|
|
public sealed class FileHelper
|
|
|
{
|
|
|
|
|
|
private int paramBeginRowNumber = 6;
|
|
|
|
|
|
private readonly JsonChange jsonChange = JsonChange.Instance;
|
|
|
|
|
|
private readonly LogHelper logHelper = LogHelper.Instance;
|
|
|
|
|
|
private AppConfig appConfig = AppConfig.Instance;
|
|
|
|
|
|
private static readonly Lazy<FileHelper> lazy = new Lazy<FileHelper>(() => new FileHelper());
|
|
|
|
|
|
public static FileHelper Instance
|
|
|
{
|
|
|
get
|
|
|
{
|
|
|
return lazy.Value;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
private FileHelper() { }
|
|
|
|
|
|
[Obsolete]
|
|
|
public Dictionary<string, List<DeviceParam>> ReadExcelFile_New(string filePath)
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
Dictionary<string, List<DeviceParam>> keyValues = new Dictionary<string, List<DeviceParam>>();
|
|
|
|
|
|
var sheetNames = MiniExcel.GetSheetNames(filePath);
|
|
|
foreach (var sheetName in sheetNames)
|
|
|
{
|
|
|
List<DeviceParam> deviceParams = new List<DeviceParam>();
|
|
|
DeviceParam parameterInfo = new DeviceParam();
|
|
|
List<dynamic> info = MiniExcel.Query(filePath, sheetName: sheetName).ToList();
|
|
|
if (info != null && info.Count() > paramBeginRowNumber)
|
|
|
{
|
|
|
for (int i = 8; i < 16; i++)
|
|
|
{
|
|
|
DeviceParam deviceParam = new DeviceParam();
|
|
|
deviceParam.id = System.Guid.NewGuid().ToString("N");
|
|
|
ParamData param = new ParamData();
|
|
|
param.deviceCode = "EVtest";
|
|
|
param.propertyCode = info[i].C;
|
|
|
param.dataType = GetDataType(info[16].F == null ? "Text" : info[16].F);
|
|
|
param.propertyValue = info[i].E;
|
|
|
param.time = GetTimeStamp();
|
|
|
deviceParam.data = param;
|
|
|
deviceParams.Add(deviceParam);
|
|
|
}
|
|
|
|
|
|
DeviceParam deviceParam_17 = new DeviceParam();
|
|
|
deviceParam_17.id = System.Guid.NewGuid().ToString("N");
|
|
|
ParamData param_17 = new ParamData();
|
|
|
param_17.deviceCode = "EVtest";
|
|
|
param_17.propertyCode = info[17].C;
|
|
|
param_17.dataType = 5;
|
|
|
|
|
|
List<PropertyValue> propertyValues = new List<PropertyValue>();
|
|
|
//tag9 监测明细
|
|
|
for (int i = 17; i < info.Count();)
|
|
|
{
|
|
|
if (string.IsNullOrEmpty((string)info[i].D))
|
|
|
{
|
|
|
i = i + 6;
|
|
|
continue;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
PropertyValue propertyValue = new PropertyValue();
|
|
|
propertyValue.CHECK_ITEM = MidStrEx_New((string)info[i].D, "-", "-");
|
|
|
propertyValue.ITEM_VALUE = info[i].E == null ? " " : string.Format("{0}", info[i].E);
|
|
|
propertyValue.TEST_STATUS = info[i + 1].E == null ? " " : string.Format("{0}", info[i + 1].E);
|
|
|
propertyValue.STANDARDVALUE = info[i + 2].E == null ? " " : string.Format("{0}", info[i + 2].E);
|
|
|
propertyValue.STANDARDMAX = info[i + 3].E == null ? " " : string.Format("{0}", info[i + 3].E);
|
|
|
propertyValue.STANDARDMIN = info[i + 4].E == null ? " " : string.Format("{0}", info[i + 4].E);
|
|
|
propertyValue.TEST_DETAIL_ID = System.Guid.NewGuid().ToString("N");
|
|
|
|
|
|
//空值处理
|
|
|
propertyValue.DEVICE_NAME = "";
|
|
|
propertyValue.ACCURACYVALUE = "";
|
|
|
propertyValue.UOM = "";
|
|
|
propertyValues.Add(propertyValue);
|
|
|
i = i + 6;
|
|
|
}
|
|
|
|
|
|
|
|
|
}
|
|
|
param_17.propertyValue = jsonChange.ModeToJson(propertyValues);
|
|
|
param_17.time = GetTimeStamp();
|
|
|
deviceParam_17.data = param_17;
|
|
|
deviceParams.Add(deviceParam_17);
|
|
|
|
|
|
//推送标志位
|
|
|
DeviceParam deviceParam_16 = new DeviceParam();
|
|
|
deviceParam_16.id = System.Guid.NewGuid().ToString("N");
|
|
|
ParamData param_16 = new ParamData();
|
|
|
param_16.deviceCode = "EVtest";
|
|
|
param_16.propertyCode = info[16].C;
|
|
|
param_16.dataType = GetDataType(info[16].F == null ? "Text" : info[16].F);
|
|
|
param_16.propertyValue = info[16].E == null ? "" : info[16].E;
|
|
|
param_16.time = GetTimeStamp();
|
|
|
deviceParam_16.data = param_16;
|
|
|
deviceParams.Add(deviceParam_16);
|
|
|
}
|
|
|
|
|
|
keyValues.Add(sheetName, deviceParams);
|
|
|
}
|
|
|
|
|
|
return keyValues;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logHelper.Error("文件解析异常", ex);
|
|
|
return null;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
[Obsolete]
|
|
|
public Dictionary<string, List<DeviceParam>> ReadExcelFile(string filePath)
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
List<DeviceParameterInfo> result = new List<DeviceParameterInfo>();
|
|
|
var sheetNames = MiniExcel.GetSheetNames(filePath);
|
|
|
foreach (var sheetName in sheetNames)
|
|
|
{
|
|
|
DeviceParameterInfo parameterInfo = new DeviceParameterInfo();
|
|
|
List<dynamic> info = MiniExcel.Query(filePath, sheetName: sheetName).ToList();
|
|
|
var rows = MiniExcel.Query(filePath, sheetName: sheetName);
|
|
|
//避免读取数据为空的工作表
|
|
|
if (info != null && info.Count() > paramBeginRowNumber)
|
|
|
{
|
|
|
//获取前两行参数：公司名称B、工件名称E、工件编号B、操作人员E
|
|
|
parameterInfo.companyName = info[0].B;
|
|
|
parameterInfo.partName = info[0].E;
|
|
|
parameterInfo.partNumber = info[1].B;
|
|
|
parameterInfo.operatingPersonnel = info[2].E;
|
|
|
List<DeviceParametersValue> values = new List<DeviceParametersValue>();
|
|
|
|
|
|
var deviceParams = info.GetRange(paramBeginRowNumber, info.Count() - paramBeginRowNumber);
|
|
|
if (deviceParams == null) continue;
|
|
|
foreach (var item in deviceParams)
|
|
|
{
|
|
|
|
|
|
DeviceParametersValue parametersValue = new DeviceParametersValue();
|
|
|
if (item.A.ToString() == null) continue;
|
|
|
parametersValue.paramNumber = item.A.ToString();
|
|
|
parametersValue.paramContent = item.A.ToString();
|
|
|
#region 备份修改 将double转为object
|
|
|
/*parametersValue.observedValue = item.C == null ? 0 : item.C;
|
|
|
parametersValue.nominalValue = item.D == null ? 0 : item.D;
|
|
|
parametersValue.overproofValue = item.E == null ? 0 : item.E;
|
|
|
parametersValue.upperDeviation = item.F == null ? 0 : item.F;
|
|
|
parametersValue.lowerDeviation = item.G == null ? 0 : item.G;
|
|
|
parametersValue.state = item.H as string;*/
|
|
|
#endregion
|
|
|
|
|
|
parametersValue.observedValue = string.IsNullOrEmpty(item.C.ToString()) ? 0 : item.C;
|
|
|
parametersValue.nominalValue = string.IsNullOrEmpty(item.D.ToString()) ? 0 : item.D;
|
|
|
parametersValue.overproofValue = string.IsNullOrEmpty(item.E.ToString()) ? 0 : item.E;
|
|
|
parametersValue.upperDeviation = string.IsNullOrEmpty(item.F.ToString()) ? 0 : item.F;
|
|
|
parametersValue.lowerDeviation = string.IsNullOrEmpty(item.G.ToString()) ? 0 : item.G;
|
|
|
parametersValue.state = item.H.ToString();
|
|
|
|
|
|
values.Add(parametersValue);
|
|
|
}
|
|
|
parameterInfo.deviceParametersValues = values;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
continue;
|
|
|
}
|
|
|
|
|
|
result.Add(parameterInfo);
|
|
|
}
|
|
|
return formatFileData(result);
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
throw new Exception(ex.Message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 格式化文件数据
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
private Dictionary<string, List<DeviceParam>> formatFileData(List<DeviceParameterInfo> parameterInfos)
|
|
|
{
|
|
|
Dictionary<string, List<DeviceParam>> keyValues = new Dictionary<string, List<DeviceParam>>();
|
|
|
|
|
|
if (parameterInfos.Count == 0) return null;
|
|
|
|
|
|
foreach (var infos in parameterInfos)
|
|
|
{
|
|
|
|
|
|
List<DeviceParam> deviceParams = new List<DeviceParam>();
|
|
|
|
|
|
//add tag1-8、tag10
|
|
|
for (int i = 1; i < 9; i++)
|
|
|
{
|
|
|
DeviceParam deviceParam2 = new DeviceParam();
|
|
|
deviceParam2.id = System.Guid.NewGuid().ToString("N");
|
|
|
ParamData param2 = new ParamData();
|
|
|
param2.deviceCode = "EVtest";
|
|
|
param2.propertyCode = $"tag{i}";
|
|
|
param2.dataType = 5;
|
|
|
param2.propertyValue = getNodeValue(param2.propertyCode);
|
|
|
param2.time = GetTimeStamp();
|
|
|
deviceParam2.data = param2;
|
|
|
deviceParams.Add(deviceParam2);
|
|
|
}
|
|
|
|
|
|
DeviceParam deviceParam = new DeviceParam();
|
|
|
deviceParam.id = System.Guid.NewGuid().ToString("N");
|
|
|
ParamData param = new ParamData();
|
|
|
param.propertyCode = "tag9";
|
|
|
param.dataType = 5;
|
|
|
var ParametersValues = infos.deviceParametersValues;
|
|
|
List<PropertyValue> propertyValues = new List<PropertyValue>();
|
|
|
|
|
|
//tag9 监测明细
|
|
|
foreach (var item in ParametersValues)
|
|
|
{
|
|
|
PropertyValue propertyValue = new PropertyValue();
|
|
|
propertyValue.CHECK_ITEM = string.IsNullOrEmpty(item.paramContent) ? "" : item.paramContent;
|
|
|
propertyValue.ITEM_VALUE = string.IsNullOrEmpty(item.observedValue.ToString()) ? "" : item.observedValue.ToString();
|
|
|
|
|
|
propertyValue.TEST_STATUS = string.IsNullOrEmpty(item.state) ? "" : item.state;
|
|
|
propertyValue.STANDARDVALUE = string.IsNullOrEmpty(item.nominalValue.ToString()) ? "" : item.nominalValue.ToString();
|
|
|
|
|
|
propertyValue.STANDARDMAX = CalculateThreshold(item.nominalValue.ToString(), item.upperDeviation.ToString());
|
|
|
//propertyValue.STANDARDMIN = string.IsNullOrEmpty(item.lowerDeviation.ToString()) ? "" : item.lowerDeviation.ToString();
|
|
|
propertyValue.STANDARDMIN = CalculateThreshold(item.nominalValue.ToString(), item.lowerDeviation.ToString());
|
|
|
propertyValue.TEST_DETAIL_ID = System.Guid.NewGuid().ToString("N");
|
|
|
|
|
|
//空值处理
|
|
|
propertyValue.ACCURACYVALUE = "";
|
|
|
propertyValue.DEVICE_NAME = "";
|
|
|
propertyValue.UOM = "";
|
|
|
propertyValues.Add(propertyValue);
|
|
|
|
|
|
|
|
|
}
|
|
|
param.propertyValue = jsonChange.ModeToJson(propertyValues);
|
|
|
logHelper.Info($"propertyValue:{jsonChange.ModeToJson(propertyValues)}");
|
|
|
//param.propertyValue = propertyValues;
|
|
|
param.time = GetTimeStamp();
|
|
|
deviceParam.data = param;
|
|
|
|
|
|
deviceParams.Add(deviceParam);
|
|
|
|
|
|
|
|
|
|
|
|
//推送标志位
|
|
|
DeviceParam deviceParam_16 = new DeviceParam();
|
|
|
deviceParam_16.id = System.Guid.NewGuid().ToString("N");
|
|
|
ParamData param_16 = new ParamData();
|
|
|
param_16.deviceCode = "EVtest";
|
|
|
param_16.propertyCode = "tag10";
|
|
|
param_16.dataType = 5;
|
|
|
param_16.propertyValue = "";
|
|
|
param_16.time = GetTimeStamp();
|
|
|
deviceParam_16.data = param_16;
|
|
|
deviceParams.Add(deviceParam_16);
|
|
|
|
|
|
keyValues.Add("排气阀片", deviceParams);
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
return keyValues;
|
|
|
}
|
|
|
|
|
|
private string getNodeValue(string nodeStr)
|
|
|
{
|
|
|
string info = "";
|
|
|
switch (nodeStr)
|
|
|
{
|
|
|
case "tag1":
|
|
|
info = appConfig.产线;
|
|
|
break;
|
|
|
case "tag2":
|
|
|
info = appConfig.物料编号;
|
|
|
break;
|
|
|
case "tag3":
|
|
|
info = appConfig.生产条码;
|
|
|
break;
|
|
|
case "tag4":
|
|
|
info = appConfig.设备编码;
|
|
|
break;
|
|
|
case "tag5":
|
|
|
info = appConfig.测试总结果;
|
|
|
break;
|
|
|
case "tag6":
|
|
|
info = appConfig.当日生产总数;
|
|
|
break;
|
|
|
case "tag7":
|
|
|
info = appConfig.当日生产不良数;
|
|
|
break;
|
|
|
case "tag8":
|
|
|
info = appConfig.班次;
|
|
|
break;
|
|
|
}
|
|
|
return info;
|
|
|
}
|
|
|
|
|
|
private string getNodeName(string nodeStr)
|
|
|
{
|
|
|
string info = "";
|
|
|
switch (nodeStr)
|
|
|
{
|
|
|
case "tag1":
|
|
|
info = "产线";
|
|
|
break;
|
|
|
case "tag2":
|
|
|
info = "物料编号";
|
|
|
break;
|
|
|
case "tag3":
|
|
|
info = "生产条码";
|
|
|
break;
|
|
|
case "tag4":
|
|
|
info = "设备编码";
|
|
|
break;
|
|
|
case "tag5":
|
|
|
info = "测试总结果";
|
|
|
break;
|
|
|
case "tag6":
|
|
|
info = "当日生产总数";
|
|
|
break;
|
|
|
case "tag7":
|
|
|
info = "当日生产不良数";
|
|
|
break;
|
|
|
case "tag8":
|
|
|
info = "班次";
|
|
|
break;
|
|
|
}
|
|
|
return info;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据名义值、上公差/下公差计算最大/最小值
|
|
|
/// </summary>
|
|
|
/// <param name="nominalValue"></param>
|
|
|
/// <param name="difference"></param>
|
|
|
/// <returns></returns>
|
|
|
private string CalculateThreshold(string nominalValue, string difference)
|
|
|
{
|
|
|
|
|
|
string threshold = "";
|
|
|
try
|
|
|
{
|
|
|
if (string.IsNullOrEmpty(nominalValue)) return threshold;
|
|
|
|
|
|
decimal nominalValueDecimal = decimal.Parse(nominalValue);
|
|
|
decimal differenceDecimal = decimal.Parse(string.IsNullOrEmpty(difference) ? "0" : difference);
|
|
|
|
|
|
threshold = (nominalValueDecimal + differenceDecimal).ToString();
|
|
|
|
|
|
return threshold;
|
|
|
}
|
|
|
catch (Exception e)
|
|
|
{
|
|
|
logHelper.Error("CalculateThreshold异常", e);
|
|
|
return threshold;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取时间戳
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
private string GetTimeStamp()
|
|
|
{
|
|
|
TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
|
|
|
return Convert.ToInt64(ts.TotalMilliseconds).ToString();
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 字符串截取
|
|
|
/// </summary>
|
|
|
/// <param name="sourse"></param>
|
|
|
/// <param name="startstr"></param>
|
|
|
/// <param name="endstr"></param>
|
|
|
/// <returns></returns>
|
|
|
private string MidStrEx_New(string sourse, string startstr, string endstr)
|
|
|
{
|
|
|
Regex rg = new Regex("(?<=(" + startstr + "))[.\\s\\S]*?(?=(" + endstr + "))", RegexOptions.Multiline | RegexOptions.Singleline);
|
|
|
return rg.Match(sourse).Value;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 数据类型
|
|
|
/// </summary>
|
|
|
/// <param name="str"></param>
|
|
|
/// <returns></returns>
|
|
|
private int GetDataType(string str)
|
|
|
{
|
|
|
int result = 5;
|
|
|
switch (str)
|
|
|
{
|
|
|
case "Int32":
|
|
|
result = 0;
|
|
|
break;
|
|
|
case "Float":
|
|
|
result = 1;
|
|
|
break;
|
|
|
case "Double":
|
|
|
result = 2;
|
|
|
break;
|
|
|
case "Bool":
|
|
|
result = 3;
|
|
|
break;
|
|
|
case "Enum":
|
|
|
result = 4;
|
|
|
break;
|
|
|
case "Text":
|
|
|
result = 5;
|
|
|
break;
|
|
|
case "Time":
|
|
|
result = 6;
|
|
|
break;
|
|
|
case "Array":
|
|
|
result = 7;
|
|
|
break;
|
|
|
case "Struct":
|
|
|
result = 8;
|
|
|
break;
|
|
|
default:
|
|
|
result = 5;
|
|
|
break;
|
|
|
}
|
|
|
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
}
|