XS202202187439-XS202209198020/ProjectManagementSystem.Common/Config/ExcelConfig.cs

using Microsoft.CSharp.RuntimeBinder;
using MiniExcelLibs;
using MiniExcelLibs.Attributes;
using ProjectManagementSystem.Common.Extenions;
using ProjectManagementSystem.Common.Function;
using ProjectManagementSystem.Common.Logger;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Serialization;

namespace ProjectManagementSystem.Common.Config
{
    public class ExcelConfig
    {
        private static ExcelConfig m_instance;
        private static readonly object m_locker = new object();
        public static ExcelConfig Instance
        {
            get
            {
                if (m_instance == null)
                {
                    lock (m_locker)
                    {
                        if (m_instance == null)
                        {
                            m_instance = new ExcelConfig();
                            if (!m_instance.Load())
                            {
                                m_instance = null;
                            }
                        }
                    }
                }
                return m_instance;
            }
        }

        private ConcurrentDictionary<string, string> m_locationAddressDict;

        public Dictionary<string, RouteConfig> RouteConfigDictionary { get; private set; } = new Dictionary<string, RouteConfig>();
        public List<RouteConfig> RouteConfigList { get; private set; }
        public List<AreaConfig> AreaConfigList { get; private set; }
        public List<PlcBehavior> PlcBehaviorList { get; private set; }
        public List<TaskRely> TaskRelyList { get; private set; }
        public List<TaskMatch> TaskMatchList { get; private set; }
        public List<TaskStatusMatch> TaskStatusMatchList { get; private set; }
        public List<VcuAlarmInfo> VcuAlarmInfoList { get; private set; }

        public IEnumerable<IDictionary<string, object>> RouteConfigDicts { get; private set; }
        public Dictionary<string, CButton> ButtonPropertyDict { get; set; }

        public Dictionary<string, ColumnKeyInfo> ColumnKeyInfoDict { get; set; } = new Dictionary<string, ColumnKeyInfo>();
        public Dictionary<int, VcuAlarmInfo> VcuAlarmInfoDict { get; set; } = new Dictionary<int, VcuAlarmInfo>();

        public List<LineCallPrority> LineCallProrityList { get; private set; }
        public List<InfoPlc> InfoPlcList { get; private set; }

        public List<LineInfo> LineInfoList { get; private set; }
        public List<TrafficInfo> TrafficList { get; private set; }

        public string CurrentPath { get; private set; }

        private ExcelConfig()
        {
            m_locationAddressDict = new ConcurrentDictionary<string, string>();
            ButtonPropertyDict = new Dictionary<string, CButton>();
        }

        public RouteConfig GetRouteConfig(string location)
        {
            if (string.IsNullOrEmpty(location)) return null;

            RouteConfigDictionary.TryGetValue(location, out RouteConfig routeConfig);
            return routeConfig;
        }
        public VcuAlarmInfo GetVcuAlarmInfo(int id)
        {
            VcuAlarmInfoDict.TryGetValue(id, out var vcuAlarmInfo);
            return vcuAlarmInfo;
        }

        public void SaveExcel(string fileName, object data)
        {
            MiniExcel.SaveAs(fileName, data);
        }

        /// <summary>
        /// 根据库位号和列名，获取文本
        /// </summary>
        /// <param name="location">库位号</param>
        /// <param name="member">列名</param>
        /// <returns></returns>
        private string GetLocationMember(string location, string member)
        {
            var item = RouteConfigDicts.FirstOrDefault(d => d.ContainsKey("库位号") && d["库位号"].ToString() == location);
            if (item != null
                && item.ContainsKey(member))
            {
                return item[member]?.ToString();
            }

            return null;
        }

        public string GetLocationMemberCache(string location, string member)
        {
            string key = $"{location}-{member}";
            if (m_locationAddressDict.ContainsKey(key))
            {
                return m_locationAddressDict[key];
            }
            else
            {
                string realAddr = GetLocationMember(location, member);
                m_locationAddressDict.AddOrUpdate(key, realAddr, (oldKey, oldValue) => realAddr);
                return realAddr;
            }
        }

        public bool SaveButtonPropertyList()
        {
            try
            {
                string sLocation = System.Reflection.Assembly.GetExecutingAssembly().Location;
                FileInfo finfo = new FileInfo(sLocation);

                string currPath = finfo.DirectoryName;
                string xmlFilePath = Path.Combine(currPath, "CButtonTemplate.xml");

                var list = ButtonPropertyDict.Values.ToList();
                XmlSerializer serializer = new XmlSerializer(list.GetType());
                using (TextWriter tw = new StreamWriter(xmlFilePath))
                {
                    serializer.Serialize(tw, list);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }

        }

        public object GetProperty(object o, string member)
        {
            if (o == null) throw new ArgumentNullException("o");
            if (member == null) throw new ArgumentNullException("member");
            Type scope = o.GetType();
            IDynamicMetaObjectProvider provider = o as IDynamicMetaObjectProvider;
            if (provider != null)
            {
                ParameterExpression param = Expression.Parameter(typeof(object));
                DynamicMetaObject mobj = provider.GetMetaObject(param);
                GetMemberBinder binder = (GetMemberBinder)Microsoft.CSharp.RuntimeBinder.Binder.GetMember(0, member, scope, new CSharpArgumentInfo[] { CSharpArgumentInfo.Create(0, null) });
                DynamicMetaObject ret = mobj.BindGetMember(binder);
                BlockExpression final = Expression.Block(
                    Expression.Label(CallSiteBinder.UpdateLabel),
                    ret.Expression
                );
                LambdaExpression lambda = Expression.Lambda(final, param);
                Delegate del = lambda.Compile();
                return del.DynamicInvoke(o);
            }
            else
            {
                return o.GetType().GetProperty(member, BindingFlags.Public | BindingFlags.Instance).GetValue(o, null);
            }
        }

        private bool Load()
        {
            try
            {
                string sLocation = System.Reflection.Assembly.GetExecutingAssembly().Location;
                FileInfo finfo = new FileInfo(sLocation);

                CurrentPath = finfo.DirectoryName;
                string currPath = finfo.DirectoryName;
                string dataPath = Path.Combine(currPath, "PMS", "Config");
                string dataFile = Path.Combine(dataPath, "基础配置表.xlsx");

                /* 如果没有数据路径，则创建该路径 */
                if (false == Directory.Exists(dataPath))
                {
                    Directory.CreateDirectory(dataPath);
                }

                var sheets = MiniExcel.GetSheetNames(dataFile);
                if(sheets.Contains("表格显示"))
                {
                    var rows = MiniExcel.Query<ColumnKeyInfo>(dataFile, sheetName: "表格显示");
                    foreach (var item in rows)
                    {
                        if(!ColumnKeyInfoDict.ContainsKey(item.Name))
                        {
                            ColumnKeyInfoDict.Add(item.Name, item);
                        }
                    }
                }

                RouteConfigDicts = MiniExcel.Query(dataFile, true, sheetName: "线路配置").Cast<IDictionary<string, object>>();
                RouteConfigList = MiniExcel.Query<RouteConfig>(dataFile, sheetName: "线路配置").ToList();
                InfoPlcList = MiniExcel.Query<InfoPlc>(dataFile, sheetName: "线路配置")
                    .Where(d => !string.IsNullOrEmpty(d.IP地址))
                    .ToList();

                AreaConfigList = MiniExcel.Query<AreaConfig>(dataFile, sheetName: "区域配置").ToList();

                PlcBehaviorList = MiniExcel.Query<PlcBehavior>(dataFile, sheetName: "PLC行为码").ToList();
                TaskRelyList = MiniExcel.Query<TaskRely>(dataFile, sheetName: "任务依赖").ToList();

                LineCallProrityList = MiniExcel.Query<LineCallPrority>(dataFile, sheetName: "叫料优先级").ToList();
                if (sheets.Contains("任务匹配"))
                {
                    TaskMatchList = MiniExcel.Query<TaskMatch>(dataFile, sheetName: "任务匹配").ToList();
                }
                if (sheets.Contains("任务状态"))
                {
                    TaskStatusMatchList = MiniExcel.Query<TaskStatusMatch>(dataFile, sheetName: "任务状态").ToList();
                }
                if (sheets.Contains("报警信息"))
                {
                    VcuAlarmInfoList = MiniExcel.Query<VcuAlarmInfo>(dataFile, sheetName: "报警信息").ToList();

                    foreach (var item in VcuAlarmInfoList)
                    {
                        if (item.AlarmId <= 0) continue;

                        if (!VcuAlarmInfoDict.ContainsKey(item.AlarmId))
                        {
                            VcuAlarmInfoDict.Add(item.AlarmId, item);
                        }
                    }
                }

                foreach (var item in RouteConfigList)
                {
                    if (string.IsNullOrEmpty(item.LocationCode)) continue;

                    if (!RouteConfigDictionary.ContainsKey(item.LocationCode))
                    {
                        RouteConfigDictionary.Add(item.LocationCode, item);
                    }
                }

                foreach (var item in RouteConfigList)
                {
                    item.LesRoutes = item.LesRouteStr.ToValueArray<int>();
                    item.LesPodTypes = item.LesPodTypeStr.ToValueArray<string>();
                }

                foreach (var item in PlcBehaviorList)
                {
                    item.DbAddressRead = item.DbAddressReadStr.ToValueArray<string>();
                    item.DbAddressWrite = item.DbAddressWriteStr.ToValueArray<string>();
                    item.DbAddressFinalWrite = item.DbAddressFinalWriteStr.ToValueArray<string>();
                    //item.DbValueRead = item.DbValueReadStr.ToBoolArray();
                    //item.DbValueWrite = item.DbValueWriteStr.ToBoolArray();
                    //item.DbValueFinalWrite = item.DbValueFinalWriteStr.ToBoolArray();

                    if(item.IsIOTrans)
                    {
                        item.DbValueReadIO = item.DbValueReadStr.ToValueArray<ushort>();
                        item.DbValueWriteIO = item.DbValueWriteStr.ToValueArray<ushort>();
                        item.DbValueFinalWriteIO = item.DbValueFinalWriteStr.ToValueArray<ushort>();
                    }
                }

                foreach (var item in AreaConfigList)
                {
                    item.GraphVertexs = item.GraphVertexStr.ToValueArray<int>();
                    item.GraphEdges = item.GraphEdgeStr.ToValueArray<int>();
                }

                foreach (var item in TaskRelyList)
                {
                    item.EndWareHouse = item.EndWareHouseStr.ToValueArray<string>();
                }

                foreach (var item in LineCallProrityList)
                {
                    item.CalledLocations = item.CalledLocationStr.ToValueArray<string>();
                }

                //
                string dataFile2 = Path.Combine(dataPath, "类型一.xlsx");
                if(File.Exists(dataFile2))
                {
                    LineInfoList = MiniExcel.Query<LineInfo>(dataFile2, sheetName: "线体信息").ToList();
                    TrafficList = MiniExcel.Query<TrafficInfo>(dataFile2, sheetName: "管制信息").ToList();

                    foreach (var item in LineInfoList)
                    {
                        item.DetailTable = MiniExcel.QueryAsDataTable(dataFile2, sheetName: item.Name);
                    }

                    foreach (var item in TrafficList)
                    {
                        item.Marks = item.MarksString.ToValueArray<int>();
                        item.ExtraMarks = item.ExtraMarksString.ToValueArray<int>();
                    }
                }
                //else
                //{
                //    WareHouseManager wareHouseManager = new WareHouseManager();
                //    var wareHouseList = wareHouseManager.GetList();
                //    for (int i = 0; i < wareHouseList.Count; i++)
                //    {
                //        var wareHouse = wareHouseList[i];
                //        if (!m_wareHouseDict.ContainsKey(wareHouse.WareHouseCode))
                //        {
                //            m_wareHouseDict.Add(wareHouse.WareHouseCode, wareHouse.WareHouseName);
                //        }
                //    }

                //    MaterialDetailManager materialDetailManager = new MaterialDetailManager();
                //    var materialDetailList = materialDetailManager.GetList();

                //    for (int i = 0; i < materialDetailList.Count; i++)
                //    {
                //        var mData = materialDetailList[i];
                //        string[] materialIdArray = mData.MaterialId.Split(new char[] { '|' });
                //        string[] materialNameArray = mData.MaterialName.Split(new char[] { '|' });
                //        string[] materialNumberArray = mData.MaterialNumber.Split(new char[] { '|' });

                //        for (int index = 0; index < materialIdArray.Length; index++)
                //        {
                //            MaterialDetail materialDetail = new MaterialDetail();
                //            materialDetail.WareHouseCode = mData.WareHouseCode;
                //            materialDetail.LocationCode = mData.LocationCode;
                //            materialDetail.MaterialId = materialIdArray[index];
                //            materialDetail.MaterialName = materialNameArray[index];
                //            materialDetail.MaterialNumber = materialNumberArray[index];
                //            MaterialDetailList.Add(materialDetail);

                //            if (!m_materailDict.ContainsKey(materialDetail.MaterialId))
                //            {
                //                m_materailDict.Add(materialDetail.MaterialId, materialDetail.MaterialName);
                //            }
                //        }
                //    }
                //}


                //加载布局
                string xmlFilePath = Path.Combine(currPath, "CButtonTemplate.xml");
                if (File.Exists(xmlFilePath))
                {
                    List<CButton> cButtonList = new List<CButton>();
                    XmlSerializer deserializer = new XmlSerializer(cButtonList.GetType());
                    using (TextReader tr = new StreamReader(xmlFilePath))
                    {
                        cButtonList = (List<CButton>)deserializer.Deserialize(tr);
                    }

                    for (int i = 0; i < cButtonList.Count; i++)
                    {
                        var cBtn = cButtonList[i];
                        if (!ButtonPropertyDict.ContainsKey(cBtn.ButtonName))
                        {
                            ButtonPropertyDict.Add(cBtn.ButtonName, cBtn);
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                CLog.Instance.SystemLog.WriteException("ExcelConfig", ex);
            }
            return false;
        }
    }


    public class RouteConfig
    {
        [ExcelColumnName("线路")]
        public int Route { get; set; }
        [ExcelColumnName("仓库号")]
        public string WareHouseCode { get; set; }
        [ExcelColumnName("库位号")]
        public string LocationCode { get; set; }
        [ExcelColumnName("库位组名称")]
        public string WarehouseGroupType { get; set; }

        [ExcelColumnName("活动站点")]
        public int StationID { get; set; }

        [ExcelColumnName("导航点")]
        public int GraphVertex { get; set; }

        [ExcelColumnName("关联点1")]
        public int RelationStationID1 { get; set; }

        [ExcelColumnName("关联点2")]
        public int RelationStationID2 { get; set; }

        [ExcelColumnName("关联点3")]
        public int RelationStationID3 { get; set; }

        [ExcelColumnName("行")]
        public int RowIndex { get; set; }

        [ExcelColumnName("列")]
        public int ColumnIndex { get; set; }

        [ExcelColumnName("备注1")]
        public string Remark1 { get; set; }

        [ExcelColumnName("叫料状态")]
        public int CallLineStauts { get; set; }
        [ExcelColumnName("取消叫料状态")]
        public int CancelLineStauts { get; set; }

        [ExcelColumnName("IP地址(PLC)")]
        public string PlcIpAddr { get; set; }


        [ExcelColumnName("心跳信号(PLC)")]
        public string PlcHeartbearAddr { get; set; }
        [ExcelColumnName("心跳信号(AGV)")]
        public string PlcHeartbearAddr_AGV { get; set; }

        [ExcelColumnName("滚筒台ID")]
        public int RollerID { get; set; }
        [ExcelColumnName("对接盒ID")]
        public int IOTransID { get; set; }


        [ExcelColumnName("LES线路")]
        public string LesRouteStr { get; set; }
        [ExcelColumnName("叫料器具类型")]
        public string LesPodTypeStr { get; set; }

        public int[] LesRoutes { get; set; }
        public string[] LesPodTypes { get; set; }

        public bool PlcIsNeedClear { get; set; }
        public bool PlcConnected { get; set; }


        [ExcelIgnore(true)]
        public Newtonsoft.Json.Linq.JObject JData { get; set; } = new Newtonsoft.Json.Linq.JObject();
        public HslCommunication.Core.IReadWriteNet PlcReadWrite { get; set; }

        public DateTime LastPackTaskAddTime { get; set; }
        public bool PackTaskAddCooled { get { return Math.Abs((DateTime.Now - LastPackTaskAddTime).TotalSeconds) > 5; } }
    }

    public class AreaConfig
    {
        [ExcelColumnName("区域名称")]
        public string Name { get; set; }

        [ExcelColumnName("导航点")]
        public string GraphVertexStr { get; set; }
        [ExcelColumnName("路段")]
        public string GraphEdgeStr { get; set; }


        public bool AgvInArea { get; set; }

        [ExcelColumnName("交通灯编号")]
        public int TrafficLightId { get; set; }
        public DateTime SetTrafficLightTime { get; set; }
        public bool ReSetTrafficLight
        {
            get { return (DateTime.Now - SetTrafficLightTime).TotalSeconds > 30; }
        }

        public int[] GraphVertexs { get; set; }
        public int[] GraphEdges { get; set; }
    }

    public class PlcBehavior
    {
        [ExcelColumnName("行为码")]
        public int Behavior { get; set; }
        [ExcelColumnName("逻辑位(True)")]
        public int LogicBitTrue { get; set; } = -1;
        [ExcelColumnName("DB地址写入")]
        public string DbAddressWriteStr { get; set; }
        [ExcelColumnName("DB地址读取")]
        public string DbAddressReadStr { get; set; }
        [ExcelColumnName("DB地址最终写入")]
        public string DbAddressFinalWriteStr { get; set; }
        [ExcelColumnName("写入值")]
        public string DbValueWriteStr { get; set; }
        [ExcelColumnName("读取值")]
        public string DbValueReadStr { get; set; }
        [ExcelColumnName("最终写入值")]
        public string DbValueFinalWriteStr { get; set; }

        public string[] DbAddressWrite { get; set; }
        public string[] DbAddressRead { get; set; }
        public string[] DbAddressFinalWrite { get; set; }
        //public bool[] DbValueWrite { get; set; }
        //public bool[] DbValueRead { get; set; }
        //public bool[] DbValueFinalWrite { get; set; }

        public ushort[] DbValueWriteIO { get; set; }
        public ushort[] DbValueReadIO { get; set; }
        public ushort[] DbValueFinalWriteIO { get; set; }

        [ExcelColumnName("数据类型")]
        public string DbValueDataType { get; set; }

        [ExcelColumnName("备注")]
        public string Remark { get; set; }

        [ExcelColumnName("模拟")]
        public bool IsSimulate { get; set; }

        [ExcelColumnName("IO透传")]
        public bool IsIOTrans { get; set; }

        [ExcelColumnName("异步执行类")]
        public string AsyncClassName { get; set; }

        [ExcelColumnName("异步执行步骤数")]
        public int AsyncStepOffset { get; set; }
    }

    public class TaskRely
    {
        [ExcelColumnName("新任务开始仓库")]
        public string StartWareHouse { get; set; }
        [ExcelColumnName("旧任务结束仓库")]
        public string EndWareHouseStr { get; set; }
        public string[] EndWareHouse { get; set; }
    }

    public class ColumnKeyInfo
    {
        public string Name { get; set; }
        public string HeaderText { get; set; }
        public string Format { get; set; }
        public bool Visible { get; set; }

    }

    public class LineCallPrority
    {
        [ExcelColumnName("当前点")]
        public string Location { get; set; }
        [ExcelColumnName("设备叫料点")]
        public string CalledLocationStr { get; set; }

        public string[] CalledLocations { get; set; }

    }

    public class InfoPlc
    {
        internal RouteConfig routeConfig { get; set; }

        public string 库位号 { get; set; }

        [ExcelColumnName("IP地址(PLC)")]
        public string IP地址 { get; set; }


        [ExcelIgnore(true)]
        public Newtonsoft.Json.Linq.JObject JData { get; set; }

        public string[] PlcSignalAddrArray { get; set; }
        public ushort[] PlcSignalAddrLengthArray { get; set; }

        public DateTime LastUpdateTime { get; set; }

        public bool 有效
        {
            get { return (DateTime.Now - LastUpdateTime).TotalSeconds < 5; }
        }
    }


    public class LineInfo
    {
        [ExcelColumnName("线体编号")]
        public int Number { get; set; }
        [ExcelColumnName("线体名称")]
        public string Name { get; set; }
        [ExcelColumnName("下一线体编号")]
        public int NextNumber { get; set; }
        [ExcelColumnName("判断点")]
        public int Mark { get; set; }
        [ExcelColumnName("对接方式")]
        public int CommunicationType { get; set; }
        public DataTable DetailTable { get; set; }
    }

    public class TrafficInfo
    {
        [ExcelColumnName("进管制区地标")]
        public string MarksString { get; set; }
        [ExcelColumnName("管制区内地标")]
        public string ExtraMarksString { get; set; }

        public int[] Marks { get; set; }

        public int[] ExtraMarks { get; set; }

        public bool IsTraffic { get; set; } = true;

        public long LastReleaseTime { get; set; }

        public bool ReleaseCold { get { return (DateTime.Now.Ticks - LastReleaseTime) > 50000000L; } }
    }

    public class TaskMatch
    {
        [ExcelColumnName("取货仓库")]
        public string WarehouseCode { get; set; }
        [ExcelColumnName("取货仓位")]
        public string LocationCode { get; set; }


        [ExcelColumnName("放货仓库")]
        public string TargetWarehouseCode { get; set; }
        [ExcelColumnName("放货仓位")]
        public string TargetLocationCode { get; set; }

        [ExcelColumnName("任务模板")]
        public string TemplateName { get; set; }

        [ExcelColumnName("指定小车")]
        public int AgvId { get; set; }

        [ExcelColumnName("小车类型")]
        public string AgvType { get; set; }

        [ExcelColumnName("优先级")]
        public int Priority { get; set; }

    }

    public class TaskStatusMatch
    {
        [ExcelColumnName("任务模板")]
        public string TaskBook { get; set; }

        [ExcelColumnName("步骤")]
        public int StepID { get; set; }

        [ExcelColumnName("状态")]
        public int TaskState { get; set; }

        [ExcelColumnName("状态描述")]
        public string TaskDesc { get; set; }
    }

    public class VcuAlarmInfo
    {
        [ExcelColumnName("报警编号")]
        public int AlarmId { get; set; }

        [ExcelColumnName("报警名称")]
        public string AlarmName { get; set; }

        [ExcelColumnName("忽略")]
        public bool Ignore { get; set; }
    }
}