AX.FileDataUpload.Voluntary/FileDataUpload.FileOperate/FileHelper.cs

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;
        }
    }
}