FurnaceELK/FrameworkLocal/UIClient/CenterViews/DataLogs/ProcessHistory/ProcessExportAllViewModel.cs

using Aitex.Core.Backend;
using Aitex.Core.RT.IOCore;
using Aitex.Core.RT.Log;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using MECF.Framework.Common.CommonData.EnumData;
using MECF.Framework.Common.ControlDataContext;
using MECF.Framework.Common.DataCenter;
using MECF.Framework.Common.Equipment;
using MECF.Framework.Common.Utilities;
using MECF.Framework.UI.Client.CenterViews.Configs.SystemConfig;
using MECF.Framework.UI.Client.CenterViews.DataLogs.ProcessHistory;
using MECF.Framework.UI.Client.CenterViews.Dialogs;
using MECF.Framework.UI.Client.CenterViews.Operations.RealTime;
using MECF.Framework.UI.Client.ClientBase;

using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.XSSF.UserModel;
using OpenSEMI.ClientBase;
using SciChart.Charting.Visuals;
using SciChart.Charting.Visuals.Annotations;
using SciChart.Charting.Visuals.Axes;
using SciChart.Charting.Visuals.RenderableSeries;
using SciChart.Data.Model;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;
using static MECF.Framework.Common.FAServices.DataVariables;
using Action = System.Action;
using Media = System.Windows.Media;

namespace MECF.Framework.UI.Client.CenterViews.DataLogs.ProcessHistory
{
    class ProcessExportAllViewModel<T> : ModuleUiViewModelBase where T : IComparable
    {
        private ProcessExportAllView view;
        private CancellationTokenSource _cancellationTokenSource;
        private event EventHandler Exporting;
        private const int MAX_PARAMETERS = 1000;
        public List<ProcessHistoryLot> RecipeDatas { get; set; }

        private ObservableCollection<ParameterNode> _ParameterNodes;
        public ObservableCollection<ParameterNode> ParameterNodes
        {
            get { return _ParameterNodes; }
            set { _ParameterNodes = value; NotifyOfPropertyChange("ParameterNodes"); }
        }
        private Dictionary<string, string> _processDetailDisplayDic = new Dictionary<string, string>();
        private Dictionary<string, Dictionary<string, string>> _processProcessDetailAttributeDict = new Dictionary<string, Dictionary<string, string>>();
        //   public ObservableCollection<IRenderableSeries> SelectedData { get; set; }
        public ObservableCollection<ParameterNode> GetParameters()
        {
            ObservableCollection<ParameterNode> rootNode = new ObservableCollection<ParameterNode>();
            try
            {
                Dictionary<string, string> displayDic = QueryDataClient.Instance.Service.GetData("System.ProcessDetailDisplay") as Dictionary<string, string>;

                if (displayDic == null)
                    return rootNode;

                List<string> dataList = new List<string>();
                foreach (var key in displayDic.Keys)
                {
                    string[] item = key.Split('.');
                    if (item != null)
                    {
                        if (item.Length == 2)
                        {
                            _processDetailDisplayDic.Add($"{item[1]}", displayDic[key]);
                            dataList.Add($"{item[0]}.{item[1]}");
                        }
                        else if (item.Length == 3)
                        {
                            _processDetailDisplayDic.Add($"{item[1]} {item[2]}", displayDic[key]);
                            dataList.Add($"{item[0]}.{item[1]}.{item[2]}");
                        }

                    }

                    Dictionary<string, ParameterNode> indexer = new Dictionary<string, ParameterNode>();
                    foreach (string dataName in dataList)
                    {
                        string[] nodeName = dataName.Split('.');
                        ParameterNode parentNode = null;
                        string pathName = "";
                        for (int i = 0; i < nodeName.Length; i++)
                        {
                            pathName = (i == 0 || i == 1) ? nodeName[i] : (pathName + " " + nodeName[i]);

                            if (!indexer.ContainsKey(pathName))
                            {
                                indexer[pathName] = new ParameterNode() { Name = pathName, ChildNodes = new ObservableCollection<ParameterNode>(), ParentNode = parentNode };

                                if (parentNode == null)
                                {
                                    rootNode.Add(indexer[pathName]);
                                }
                                else
                                {
                                    parentNode.ChildNodes.Add(indexer[pathName]);
                                }
                            }

                            parentNode = indexer[pathName];
                        }
                    }
                    SortParameterNode(rootNode, "Heater", 0);
                    SortParameterNode(rootNode, "MFC", 1);
                    SortParameterNode(rootNode, "Pressure", 2);
                    SortParameterNode(rootNode, "Boat", 3);
                    SortParameterNode(rootNode, "HeaterBand", 4);
                    SortParameterNode(rootNode, "Valve", 5);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
            }

            return rootNode;
        }

        void SortParameterNode(ObservableCollection<ParameterNode> rootNode, string Name, int Index)
        {

            if (rootNode[Index].Name != Name)
            {
                for (int i = 0; i < rootNode.Count; i++)
                {
                    rootNode[i].Selected = true;
                    if (rootNode[i].Name == Name)
                    {
                        ParameterNode node = rootNode[i];
                        rootNode.RemoveAt(i);
                        rootNode.Insert(Index, node);
                    }
                }
            }
        }

        private IRange _timeRange;
        public IRange VisibleRangeTime
        {
            get { return _timeRange; }
            set
            {
                _timeRange = value;
                NotifyOfPropertyChange(nameof(VisibleRangeTime));
            }
        }

        private IRange _VisibleRangeValue;
        public IRange VisibleRangeValue
        {
            get { return _VisibleRangeValue; }
            set { _VisibleRangeValue = value; NotifyOfPropertyChange(nameof(VisibleRangeValue)); }
        }
        private AnnotationCollection _annotations;
        public AnnotationCollection Annotations
        {
            get => _annotations;
            set
            {
                _annotations = value;
                InvokePropertyChanged(nameof(Annotations));
            }

        }
        //private PeriodicJob _thread;

        private List<StepInfo> _stepInfo = new List<StepInfo>();
        public List<StepInfo> StepInfo
        {
            get { return _stepInfo; }
            set { _stepInfo = value; this.NotifyOfPropertyChange(nameof(StepInfo)); }
        }

        private bool _isBusy = false;
        private string _busyIndicatorMessage;

        /// <summary>
        /// 设置或返回忙信息。
        /// </summary>
        public string BusyIndicatorContent
        {
            get => _busyIndicatorMessage;
            set
            {
                _busyIndicatorMessage = value;
                NotifyOfPropertyChange(nameof(BusyIndicatorContent));
            }
        }

        /// <summary>
        /// 设置或返回视图是否正忙。
        /// </summary>
        public bool IsBusy
        {
            get => _isBusy;
            set
            {
                _isBusy = value;
                NotifyOfPropertyChange(nameof(IsBusy));
            }
        }
        private RealtimeProvider _realtimeProvider;
        public ProcessExportAllViewModel(List<ProcessHistoryLot> recipes)
        {
            DisplayName = "Process Export";
            RecipeDatas = recipes;
            _realtimeProvider = new RealtimeProvider();
            ParameterNodes = _realtimeProvider.GetParameters(out var dict, true);
            _processDetailDisplayDic = dict;
            _processProcessDetailAttributeDict = _realtimeProvider.GetProcessProcessDetailAttributeDict();
            if (recipes == null || recipes.Count == 0)
            {
                return;
            }
            //SelectedData = new ObservableCollection<IRenderableSeries>();
            var now = DateTime.Now;

            VisibleRangeTime = new DateRange(DateTime.Now.AddMinutes(60), DateTime.Now.AddMinutes(-60));
            VisibleRangeValue = new DoubleRange(0, 10);
            Annotations = new AnnotationCollection();
            // _thread = new PeriodicJob(200, MonitorData, "ProcessExport", true);

            RecipeDatas.ToList().ForEach(recipe =>
            {
                QueryStep(recipe);
            });

            if (StepInfo != null && StepInfo.Count > 0)
            {
                StepStartTime = StepInfo.FirstOrDefault().StartTime;
                StepEndTime = StepInfo.LastOrDefault().EndTime;
            }
            else
            {
                if (recipes.Count > 0)
                {
                    StepStartTime = recipes[0].StartTime;
                    StepEndTime = recipes[0].EndTime;
                }
            }
        }
        public bool IsStepVisiable => RecipeDatas.Count == 1;

        public DateTime StepStartTime { get; set; }
        public DateTime StepEndTime { get; set; }
        private bool _isAdding;

        private static object _lockSelection = new object();

        private ConcurrentBag<QueryIndexer> _lstTokenTimeData = new ConcurrentBag<QueryIndexer>();

        public class QueryIndexer
        {
            public TimeChartDataLine DataLine { get; set; }
            public List<string> DataList { get; set; }
            public DateTime TimeToken { get; set; }
        }
        public class TimeChartDataLine : ChartDataLine<T>
        {
            public string Module { get; set; }
            public DateTime StartTime { get; set; }
            public DateTime EndTime { get; set; }

            public TimeChartDataLine(string dataName, string module, DateTime startTime, DateTime endTime) : base(dataName)
            {
                StartTime = startTime;
                EndTime = endTime;
                Module = module;
            }
        }

        private string _resolution;
        private AutoRange _autoRange;
        public AutoRange ChartAutoRange
        {
            get { return _autoRange; }
            set
            {
                _autoRange = value;
                NotifyOfPropertyChange(nameof(ChartAutoRange));
            }
        }


        public void QueryStep(ProcessHistoryLot historyLot)//, string recipeName去掉查询recipeName名称，有嵌套调用
        {
            string sql = string.Empty;
            DataTable dbData = new DataTable();

            if (!string.IsNullOrEmpty(historyLot.PjId))
            {
                sql = $"SELECT * FROM process_data where pj_id='{historyLot.PjId}'";
                dbData = QueryDataClient.Instance.Service.QueryData(sql);
            }

            if (dbData == null || dbData.Rows.Count == 0)
            {
                DateTime starTime = historyLot.StartTime.AddMinutes(-1);
                DateTime endTime = historyLot.EndTime.AddMinutes(1);
                sql = $"SELECT * FROM \"process_data\" where (\"process_begin_time\" >= '{starTime:yyyy/MM/dd HH:mm:ss.fff}' and \"process_end_time\" <= '{endTime:yyyy/MM/dd HH:mm:ss.fff}') order by \"process_begin_time\" DESC;";
                dbData = QueryDataClient.Instance.Service.QueryData(sql);
            }
            if (dbData != null && dbData.Rows.Count > 0)
            {
                List<ProcessDataLot> ProcessDataLotList = new List<ProcessDataLot>();
                for (int i = 0; i < dbData.Rows.Count; i++)
                {
                    ProcessDataLot item = new ProcessDataLot();

                    item.GUID = dbData.Rows[i]["guid"].ToString();
                    item.RecipeName = dbData.Rows[i]["recipe_name"].ToString();
                    item.ProcessStatus = dbData.Rows[i]["process_status"].ToString();
                    item.WaferDataGUID = dbData.Rows[i]["wafer_data_guid"].ToString();
                    item.ProcessIn = dbData.Rows[i]["process_in"].ToString();

                    item.RecipeType = dbData.Rows[i]["recipe_type"].ToString();
                    item.RecipeExeEntry = dbData.Rows[i]["recipe_exec_entry"].ToString();

                    if (!dbData.Rows[i]["process_begin_time"].Equals(DBNull.Value))
                    {
                        item.ProcessBeginTime = (DateTime)dbData.Rows[i]["process_begin_time"];
                    }

                    if (!dbData.Rows[i]["process_end_time"].Equals(DBNull.Value))
                    {
                        item.ProcessEndTime = (DateTime)dbData.Rows[i]["process_end_time"];
                    }



                    ProcessDataLotList.Add(item);
                }
                if (ProcessDataLotList.Count == 0) { LOG.Warning($"QueryStep:No process data({sql})"); return; }

                historyLot.ProcessStartTime = ProcessDataLotList[0].ProcessBeginTime;
                historyLot.ProcessEndTime = ProcessDataLotList[0].ProcessEndTime;

                if (int.TryParse(ProcessDataLotList[0].RecipeType, out int type))
                    historyLot.RecipeType = (RecipeTypeEnum)type;

                if (int.TryParse(ProcessDataLotList[0].RecipeExeEntry, out int entry))
                    historyLot.RecipeExecEntry = (RecipeExecEntryEnum)entry;

                //   Annotations.Add(VerLine(Media.Brushes.Blue, ProcessDataLotList[0].ProcessBeginTime, Media.Brushes.Blue, $"{ProcessDataLotList[0].RecipeName}"));

                //Annotations.Add(VerLine(Media.Brushes.Blue, ProcessDataLotList[0].ProcessEndTime, Media.Brushes.Blue, $"Recipe End"));


                string sql2 = $"SELECT * FROM \"recipe_step_data\" where";

                sql2 += $" \"recipe_step_data\".\"process_data_guid\" = '{ProcessDataLotList[0].GUID.ToString()}'";

                sql2 += " order by \"step_begin_time\" ASC;";
                using (var table = QueryDataClient.Instance.Service.QueryData(sql2))
                {
                    if (!(table == null || table.Rows.Count == 0))
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            var item = table.Rows[i];
                            string startStepTime = "";
                            string endStepTime = "";
                            double stepTime = 0;

                            string subRecipeStepNumber = "";
                            string subRecipeStepTime = "";
                            string subRecipeStepName = "";
                            string subRecipeLoopInfo = "";
                            string tempCorrection = "";
                            string tempPid = "";
                            if (!item["step_begin_time"].Equals(DBNull.Value))
                                startStepTime = ((DateTime)item["step_begin_time"]).ToString("yyyy/MM/dd HH:mm:ss.fff");
                            if (!item["step_end_time"].Equals(DBNull.Value))
                            {
                                endStepTime = ((DateTime)item["step_end_time"]).ToString("yyyy/MM/dd HH:mm:ss.fff");
                            }
                            if (!item["step_time"].Equals(DBNull.Value))
                            {
                                stepTime = (float)item["step_time"];
                            }
                            if (!item["sub_recipe_step_time"].Equals(DBNull.Value))
                            {
                                subRecipeStepTime = item["sub_recipe_step_time"].ToString();
                            }
                            if (!item["sub_recipe_step_number"].Equals(DBNull.Value))
                            {
                                subRecipeStepNumber = item["sub_recipe_step_number"].ToString();
                            }
                            if (!item["sub_recipe_step_name"].Equals(DBNull.Value))
                            {
                                subRecipeStepName = item["sub_recipe_step_name"].ToString();
                            }
                            if (!item["sub_recipe_loop_info"].Equals(DBNull.Value))
                            {
                                subRecipeLoopInfo = item["sub_recipe_loop_info"].ToString();
                            }
                            if (!item["temp_correction"].Equals(DBNull.Value))
                            {
                                tempCorrection = item["temp_correction"].ToString().Replace(",", ":");
                            }
                            if (!item["temp_pid"].Equals(DBNull.Value))
                            {
                                tempPid = item["temp_pid"].ToString().Replace(",", ":");
                            }
                            string stepNo = item["step_number"].ToString();
                            string stepName = item["step_name"].ToString();
                            if (DateTime.TryParse(startStepTime, out DateTime StartTime) && DateTime.TryParse(endStepTime, out DateTime EndTime))
                            {

                                //Annotations.Add(VerLine(Media.Brushes.AliceBlue, StartTime, Media.Brushes.Blue, $"{stepNo}"));
                                var time = StartTime.AddSeconds(stepTime);
                                if (EndTime < time && time < ProcessDataLotList[0].ProcessEndTime)//解决process过程abort，数据导出问题：数据点时间范围大于recipe结束时间。
                                {
                                    EndTime = StartTime.AddSeconds(stepTime);
                                }
                                _stepInfo.Add(new StepInfo()
                                {
                                    StartTime = StartTime,
                                    EndTime = EndTime,
                                    StepName = stepName,
                                    StepTime = stepTime,
                                    StepNo = stepNo,
                                    StepEndTime = StartTime.AddSeconds(stepTime),
                                    SubRecipeStepNumber = subRecipeStepNumber,
                                    SubRecipeStepTime = subRecipeStepTime,
                                    SubRecipeStepName = subRecipeStepName,
                                    SubRecipeLoopInfo = subRecipeLoopInfo,
                                    TempCorrection = tempCorrection,
                                    TempPid = tempPid,
                                });
                            }
                        }
                    }
                }
            }
        }

        protected override void OnViewLoaded(object view)
        {
            base.OnViewLoaded(view);
            useMaxRow = (int)QueryDataClient.Instance.Service.GetConfig("System.SetUp.ExportMaxRow");
            this.view = (ProcessExportAllView)view;
        }

        List<string> nodeOrigin = new List<string>();

        void GetNode(ParameterNode pNode, bool Selected)
        {
            foreach (var item in pNode.ChildNodes)
            {
                if (item.ChildNodes.Count > 0)
                {
                    GetNode(item, Selected);
                }
                else
                {
                    //if(item.Selected == true)
                    {
                        nodeOrigin.Add(item.Name);

                    }
                }
            }
        }

        public static int DivideAndRoundUp(int dividend, int divisor)
        {
            int result = dividend / divisor;
            int remainder = dividend % divisor;
            if (remainder > 0)
            {
                result++;
            }
            return result;
        }

        private int useMaxRow = 25000;

        string csv = ",";

        public async void ExportAll()
        {
            try
            {
                nodeOrigin.Clear();
                if (StepStartTime.Year == 1 || StepEndTime.Year == 1)
                {
                    MessageBox.Show("No data is available in the selected period!", "Export", MessageBoxButton.OK, MessageBoxImage.Warning);
                    return;
                }
                foreach (var node in ParameterNodes)
                {
                    if (node.Selected == true)
                    {
                        GetNode(node, node.Selected);
                    }
                }

                if (nodeOrigin.Count == 0)
                {
                    MessageBox.Show($"Please select the data you want to export.", "Export", MessageBoxButton.OK,
                        MessageBoxImage.Warning);
                    return;
                }
                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".csv"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.csv)|*.csv"; // Filter files by extension
                                                         //去除创建文件时的文件名中包含的非法字符'\'
                dlg.FileName = $"{DisplayName}_{string.Join(",", RecipeDatas.Select(x => x.RecipeName.Replace('\\', '.')))}_{DateTime.Now:yyyyMMdd_HHmmss}";
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {

                    BusyIndicatorContent = "Exporting start ...";
                    IsBusy = true;
                    Exporting?.Invoke(this, System.EventArgs.Empty);
                    _cancellationTokenSource = new CancellationTokenSource();
                    await Task.Run(() =>
                    {

                        DateTime startTime = startTime = StepStartTime;

                        QueryDataClientFromTable(dlg.FileName, RecipeDatas.FirstOrDefault());
                    }, _cancellationTokenSource.Token).ContinueWith(t =>
                    {
                        if (t.IsCanceled || t.IsFaulted)
                        {
                            IsBusy = false;
                            return;
                        }
                    });

                    if (_cancellationTokenSource?.Token.IsCancellationRequested == true)
                    {
                        IsBusy = false;
                        return;
                    }
                    BusyIndicatorContent = "Data is written to Excel file ...";
                    IsBusy = false;
                    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK, MessageBoxImage.Information);
                    Window window = Window.GetWindow(this.view);
                    window.Close();
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("Write failed," + ex.Message, "export failed", MessageBoxButton.OK, MessageBoxImage.Warning);
                IsBusy = false;
            }
        }

        private void QueryDataClientFromTable(string fileName, ProcessHistoryLot recipeInfo)
        {
            DateTime startTime = StepStartTime;
            DateTime endTime = StepEndTime;
            List<string> keys = new List<string>(nodeOrigin);
            List<string> pmList = new List<string>();
            List<string> systemList = new List<string>();



            if (keys != null && keys.Count > 0)
            {
                keys = _processDetailDisplayDic.Keys.Intersect(keys).OrderBy(key => _processDetailDisplayDic.Keys.ToList().IndexOf(key)).ToList();

                foreach (var dataKey in keys)
                {
                    var dataId = _processDetailDisplayDic.ContainsKey(dataKey) ? _processDetailDisplayDic[dataKey] : dataKey;
                    var module = dataId.Split('.').ToList()[0];
                    if (module.ToLower() == "pm1")
                    {
                        pmList.Add(dataId);
                    }
                    else if (module.ToLower() == "system")
                    {
                        systemList.Add(dataId);
                        // systemList.Add(string.Format("\"{0}\"", dataId));
                    }
                }
            }
            List<DataTable> pmTableList = new List<DataTable>();
            List<DataTable> systemTableList = new List<DataTable>();
            if (StepStartTime.Date == StepEndTime.Date)
            {
                var pmItem = GetDataTable(ModuleName.PM1, pmList, startTime, endTime);
                pmTableList.Add(pmItem);

                var systemmItem = GetDataTable(ModuleName.System, systemList, startTime, endTime);
                systemTableList.Add(systemmItem);
            }
            else
            {
                endTime = new DateTime(startTime.Year, startTime.Month, startTime.Day, 23, 59, 59);
                while (endTime < StepEndTime)
                {
                    var pmItemWhile = GetDataTable(ModuleName.PM1, pmList, startTime, endTime);
                    pmTableList.Add(pmItemWhile);

                    var systemmItemWhile = GetDataTable(ModuleName.System, systemList, startTime, endTime);
                    systemTableList.Add(systemmItemWhile);

                    startTime = new DateTime(startTime.Year, startTime.Month, startTime.Day, 0, 0, 0).AddDays(1);
                    endTime = new DateTime(startTime.Year, startTime.Month, startTime.Day, 23, 59, 59);
                }
                var pmItem = GetDataTable(ModuleName.PM1, pmList, startTime, StepEndTime);
                pmTableList.Add(pmItem);

                var systemmItem = GetDataTable(ModuleName.System, systemList, startTime, StepEndTime);
                systemTableList.Add(systemmItem);
            }
            pmTableList = pmTableList.Where(a => a != null).ToList();
            systemTableList = systemTableList.Where(a => a != null).ToList();
            DataTable pm = new DataTable();
            DataTable system = new DataTable();

            if (pmTableList != null && pmTableList.Count > 0)
                pm = MergeDataTables(pmTableList, pmTableList.FirstOrDefault().Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList());

            if (systemTableList != null && systemTableList.Count > 0)
                system = MergeDataTables(systemTableList, systemTableList.FirstOrDefault().Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList());


            SaveDataToTable(pm, system, fileName, pmList, systemList, startTime, endTime, recipeInfo);
        }

        private bool firstHeader = false;

        private Dictionary<string, string> GetReverseDict()
        {
            Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();
            foreach (var kvp in _processDetailDisplayDic)
            {
                if (!keyValuePairs.ContainsKey(kvp.Value))
                    keyValuePairs[kvp.Value] = kvp.Key.Trim().Contains(" ") ? kvp.Key.Trim().Substring(kvp.Key.Trim().LastIndexOf(" ") + 1) : kvp.Key;
            }
            return keyValuePairs;

        }
        public class NeedCellColor
        {
            /// <summary>
            /// 是否需要单元格颜色
            /// </summary>
            public bool IsNeed;
            /// <summary>
            /// 配置项中的Color 数据,只支持16进制
            /// </summary>
            public string Color;

        }
        private static System.Drawing.Color ParseHexColor(string hexColor)
        {
            // 移除 #
            hexColor = hexColor.Replace("#", "").Trim();

            if (!ColorUtil.IsValidHexColor(hexColor))
                return System.Drawing.Color.Transparent;


            try
            {
                byte r = Convert.ToByte(hexColor.Substring(0, 2), 16);
                byte g = Convert.ToByte(hexColor.Substring(2, 2), 16);
                byte b = Convert.ToByte(hexColor.Substring(4, 2), 16);

                return System.Drawing.Color.FromArgb(r, g, b);
            }
            catch (Exception ex)
            {
                return System.Drawing.Color.Transparent;
            }
        }


        private void SaveDataToTable(DataTable pmDataTable, DataTable systemDataTable, string fileName, List<string> pmList, List<string> systemList, DateTime startTime, DateTime endTime, ProcessHistoryLot recipeInfo)
        {
            int numData = 35;
            var time = DateTime.Now;


            string stepID = "", stepName = "", subRecipeLoopInfo = "", subRecipeStepName = "", subRecipeStepNumber = "", tempCorrection = "", tempPid = "";

            Dictionary<DateTime, List<string>> pmDataDictList = new Dictionary<DateTime, List<string>>();
            if (pmDataTable != null && pmDataTable.Rows.Count > 0)
            {
                for (int i = 0; i < pmDataTable.Rows.Count; i++)
                {
                    _cancellationTokenSource?.Token.ThrowIfCancellationRequested();
                    List<string> strings = new List<string>();

                    foreach (var item in pmList)
                    {
                        if (!pmDataTable.Columns.Contains(item))
                            continue;
                        strings.Add(pmDataTable.Rows[i][item].ToString());
                    }
                    pmDataDictList.Add(new DateTime((long)pmDataTable.Rows[i]["InternalTimeStamp"]), strings);
                }
            }
            Dictionary<DateTime, List<string>> systemDataDictList = new Dictionary<DateTime, List<string>>();
            if (systemDataTable != null && systemDataTable.Rows.Count > 0)
            {
                for (int i = 0; i < systemDataTable.Rows.Count; i++)
                {
                    _cancellationTokenSource?.Token.ThrowIfCancellationRequested();
                    List<string> strings = new List<string>();

                    foreach (var item in systemList)
                    {
                        if (!systemDataTable.Columns.Contains(item))
                            continue;
                        strings.Add(systemDataTable.Rows[i][item].ToString());
                    }
                    systemDataDictList.Add(new DateTime((long)systemDataTable.Rows[i]["InternalTimeStamp"]), strings);
                }
            }
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            #region Recipe 信息

            int rowIndex = 0;
            int cellIndex = 0;

            Dictionary<string, string> keyValuePairs = new Dictionary<string, string>()
            {
                { "Recipe Info",recipeInfo.RecipeName},
                { "Recipe StartTime",recipeInfo.ProcessStartTime.ToString(DateTimeUtil.DateTimeFormat)},
                { "Recipe EndTime",recipeInfo.ProcessEndTime.ToString(DateTimeUtil.DateTimeFormat)},
                { "Recipe Type",recipeInfo.RecipeType.ToString()},
                { "Recipe ExecEntry",recipeInfo.RecipeExecEntry.ToString()},
            };

            IRow dictRow = sheet.CreateRow(0);
            IRow dictRowTwo = sheet.CreateRow(1);
            dictRow.HeightInPoints = numData;
            dictRowTwo.HeightInPoints = numData;
            foreach (var kvp in keyValuePairs)
            {
                var RecipeCellIndex = cellIndex++;
                WriterCell(dictRow, RecipeCellIndex, workbook, kvp.Key, GetICellStyle(workbook));
                WriterCell(dictRowTwo, RecipeCellIndex, workbook, kvp.Value, GetICellStyle(workbook));

            }
            #endregion


            #region 数据列表头信息

            rowIndex = 4;
            cellIndex = 0;

            if (!firstHeader)
            {
                Dictionary<string, string> subColNames = new Dictionary<string, string>()
                {
                    { "Step\nStartDate",ColorUtil.Color_Defalult},
                    { "Step\nStartTime",ColorUtil.Color_Defalult},
                    { "\tStepID\t ",ColorUtil.Color_Defalult},
                    { "\tStepName\t",ColorUtil.Color_Defalult},
                    { "SubRecipe\nStepName",ColorUtil.Color_Defalult},
                    { "SubRecipe\nStepNumber",ColorUtil.Color_Defalult},
                    { "SubRecipe\nLoopInfo",ColorUtil.Color_Defalult},
                    { "Temp\nCorrection",ColorUtil.Color_Yellow},
                    { "\tTempPID\t",ColorUtil.Color_Yellow},

                };

                firstHeader = true;
                var colColorNameDict = GetColNameColor(pmDataTable, systemDataTable);


                var pmColorDict = colColorNameDict.Item1;
                var systemmColorDict = colColorNameDict.Item2;


                IRow headerRow = sheet.CreateRow(rowIndex++);
                cellIndex = 0;
                headerRow.HeightInPoints = numData;
                foreach (var item in subColNames)
                {
                    var dataCellIndex = cellIndex++;
                    var needCellColor = new NeedCellColor() { IsNeed = false };
                    if (subColNames.TryGetValue(item.Key, out var dataColor) && ColorUtil.IsValidHexColor(dataColor))
                    {
                        needCellColor.IsNeed = true;
                        needCellColor.Color = dataColor;
                    }
                    WriterCell(headerRow, dataCellIndex, workbook, item.Key.Replace("(", "\n("), GetICellStyle(workbook, needCellColor));
                    sheet.SetColumnWidth(dataCellIndex, 22 * 256);
                }

                foreach (var item in systemmColorDict.Keys)
                {
                    var dataCellIndex = cellIndex++;
                    var needCellColor = new NeedCellColor() { IsNeed = false };
                    if (systemmColorDict.TryGetValue(item, out var dataColor) && ColorUtil.IsValidHexColor(dataColor))
                    {
                        needCellColor.IsNeed = true;
                        needCellColor.Color = dataColor;
                    }
                    WriterCell(headerRow, dataCellIndex, workbook, item.Replace("(", "\n("), GetICellStyle(workbook, needCellColor));
                    sheet.SetColumnWidth(dataCellIndex, item.Length * 256);
                }

                foreach (var item in pmColorDict.Keys)
                {
                    var dataCellIndex = cellIndex++;
                    var needCellColor = new NeedCellColor() { IsNeed = false };
                    if (pmColorDict.TryGetValue(item, out var dataColor) && ColorUtil.IsValidHexColor(dataColor))
                    {
                        needCellColor.IsNeed = true;
                        needCellColor.Color = dataColor;
                    }
                    WriterCell(headerRow, dataCellIndex, workbook, item.Replace("(", "\n("), GetICellStyle(workbook));
                    sheet.SetColumnWidth(dataCellIndex, item.Length * 256);
                }
            }
            #endregion


            #region 数据列 行数据信息


            rowIndex = 5;
            _stepInfo = _stepInfo.OrderBy(x => x.StartTime).ToList();

            for (int i = 0; i < _stepInfo.Count; i++)
            {
                var tempStepInfo = _stepInfo[i];
                var rowCount = tempStepInfo.StepTime;
                if (!string.IsNullOrEmpty(tempStepInfo.SubRecipeStepNumber))
                {
                    rowCount = int.Parse(tempStepInfo.SubRecipeStepTime);
                }

                for (int jx = 1; jx < rowCount + 1; jx++)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex++);
                    dataRow.HeightInPoints = 25;
                    var rowStartTime = jx == 1 ? tempStepInfo.StartTime : tempStepInfo.StartTime.AddSeconds(jx - 1);
                    var rowEndTime = tempStepInfo.StartTime.AddSeconds(jx);
                    if (tempStepInfo != null)
                    {
                        stepID = tempStepInfo.StepNo;
                        stepName = tempStepInfo.StepName;
                        subRecipeStepName = tempStepInfo.SubRecipeStepName;
                        subRecipeStepNumber = tempStepInfo.SubRecipeStepNumber;
                        subRecipeLoopInfo = !string.IsNullOrEmpty(tempStepInfo.SubRecipeLoopInfo) ? tempStepInfo.SubRecipeLoopInfo.Replace("/", "|") : tempStepInfo.SubRecipeLoopInfo;
                        tempCorrection = !string.IsNullOrEmpty(tempStepInfo.TempCorrection) ? tempStepInfo.TempCorrection : "";
                        tempPid = !string.IsNullOrEmpty(tempStepInfo.TempPid) ? tempStepInfo.TempPid : "";
                    }

                    cellIndex = 0;

                    var pmRow = new List<string>();
                    var newRowStartTime = new DateTime(rowStartTime.Year, rowStartTime.Month, rowStartTime.Day, rowStartTime.Hour, rowStartTime.Minute, rowStartTime.Second);
                    var newRowEndTime = new DateTime(rowEndTime.Year, rowEndTime.Month, rowEndTime.Day, rowEndTime.Hour, rowEndTime.Minute, rowEndTime.Second);
                    var rowPm = pmDataDictList.Keys.FirstOrDefault(a => a.Ticks >= newRowStartTime.Ticks);
                    if (rowPm != null)
                        pmRow = pmDataDictList[rowPm];

                    var systemRow = new List<string>();
                    var rowsSystem = systemDataDictList.Keys.FirstOrDefault(a => a.Ticks >= newRowStartTime.Ticks);
                    if (rowsSystem != null)
                        systemRow = systemDataDictList[rowsSystem];

                    dataRow.CreateCell(cellIndex++).SetCellValue(rowStartTime.ToString(DateTimeUtil.DateFormat));
                    dataRow.CreateCell(cellIndex++).SetCellValue(rowStartTime.ToString(DateTimeUtil.TimeFormat));
                    dataRow.CreateCell(cellIndex++).SetCellValue(stepID);
                    dataRow.CreateCell(cellIndex++).SetCellValue(stepName);
                    dataRow.CreateCell(cellIndex++).SetCellValue(subRecipeStepName);
                    dataRow.CreateCell(cellIndex++).SetCellValue(subRecipeStepNumber);
                    dataRow.CreateCell(cellIndex++).SetCellValue(subRecipeLoopInfo);
                    dataRow.CreateCell(cellIndex++).SetCellValue(tempCorrection);
                    dataRow.CreateCell(cellIndex++).SetCellValue(tempPid);
                    if (pmRow == null && systemRow != null)
                    {
                        for (int j = 1; j < systemRow.Count; j++)
                        {
                            WriterCell(dataRow, cellIndex++, workbook, systemRow[j].ToString());
                        }
                        for (int j = 0; j < (pmDataTable?.Columns.Count ?? 0); j++)
                        {
                            WriterCell(dataRow, cellIndex++, workbook, "");
                        }
                    }
                    else if (pmRow != null && systemRow == null)
                    {

                        for (int j = 0; j < (systemDataTable?.Columns?.Count ?? 0); j++)
                        {
                            WriterCell(dataRow, cellIndex++, workbook, "");
                        }
                        for (int j = 1; j < pmRow.Count; j++)
                        {
                            WriterCell(dataRow, cellIndex++, workbook, ConvertBooleanValues(pmRow[j]));
                        }
                    }
                    else if (systemDataTable != null)
                    {
                        for (int j = 1; j < systemRow.Count; j++)
                        {
                            WriterCell(dataRow, cellIndex++, workbook, systemRow[j].ToString());
                        }
                        for (int j = 1; j < pmRow.Count; j++)
                        {
                            WriterCell(dataRow, cellIndex++, workbook, ConvertBooleanValues(pmRow[j]));
                        }
                    }
                }
            }
            #endregion


            sheet.CreateFreezePane(4, 5);

            using (MemoryStream memoryStream = new MemoryStream())
            {
                workbook.Write(memoryStream);
                File.WriteAllBytes(fileName, memoryStream.ToArray());
            }
        }



        #region 抽离方法
        private ICellStyle GetICellStyle(IWorkbook workbook, NeedCellColor needCellColor = null)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.WrapText = true;
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            if (needCellColor != null && needCellColor.IsNeed)
            {
                string hexColor = needCellColor.Color;
                XSSFColor colorIndex = new XSSFColor(ParseHexColor(hexColor));

                ((XSSFCellStyle)cellStyle).SetFillForegroundColor(colorIndex);
                cellStyle.FillPattern = FillPattern.SolidForeground;
            }


            return cellStyle;

        }
        private string ConvertBooleanValues(object value)
        {
            if (bool.TryParse(value.ToString(), out bool avData))
            {
                return avData ? "Open" : "Close";
            }
            return value.ToString();
        }
        private DataTable MergeDataTables(List<DataTable> tables, List<string> commonColumns)
        {
            if (!tables.Any()) return new DataTable();

            DataTable result = new DataTable();

            // 添加列（确保顺序一致）
            foreach (var colName in commonColumns)
            {
                var sampleCol = tables[0].Columns[colName];
                result.Columns.Add(colName, sampleCol.DataType);
            }

            // 合并每一行
            foreach (var table in tables)
            {
                foreach (DataRow row in table.Rows)
                {
                    var newRow = result.NewRow();
                    foreach (var colName in commonColumns)
                    {
                        if (row[colName] != DBNull.Value)
                            newRow[colName] = row[colName];
                        else
                            newRow[colName] = DBNull.Value;
                    }
                    result.Rows.Add(newRow);
                }
            }
            // 排序最终结果（按时间戳）
            DataView view = result.DefaultView;
            view.Sort = "InternalTimeStamp ASC";

            return view.ToTable();
        }

        public DataTable GetDataTable(ModuleName type, List<string> queryColNameList, DateTime startTime, DateTime endTime)
        {
            startTime = startTime.AddSeconds(-10);
            endTime = endTime.AddSeconds(10);
            var tableName = ModuleName.PM1.ToString();

            if (type == ModuleName.System)
                tableName = ModuleName.System.ToString();


            DataTable resultTable = null;
            var columnsql = $"select column_name from information_schema.columns where table_name = '{startTime.ToString("yyyyMMdd")}.{tableName}'";
            var columnTable = QueryDataClient.Instance.Service.QueryData(columnsql);
            List<string> col = columnTable.Rows.Cast<DataRow>().Select(x => x.ItemArray[0].ToString()).ToList();

            queryColNameList = queryColNameList.Where(x => col.Any(y => y == x)).ToList();
            var tableColName = queryColNameList.Select(x => string.Format("\"{0}\"", x));

            string pmsql = $"select time AS InternalTimeStamp, {string.Join(",", tableColName)}";
            pmsql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc",
            startTime.ToString("yyyyMMdd") + "." + $"{tableName}", startTime.Ticks, endTime.Ticks);
            BusyIndicatorContent = $"Example Query {tableName} data ...";
            resultTable = QueryDataClient.Instance.Service.QueryData(pmsql);

            return resultTable;

        }
        private int GetMaxRow(DataTable pmDataTable, DataTable systemDataTable)
        {

            int maxRow = 0;
            if ((pmDataTable == null || pmDataTable.Rows.Count == 0))
            {
                if (systemDataTable != null)
                {
                    maxRow = systemDataTable.Rows.Count;
                }
            }
            else if ((systemDataTable == null || systemDataTable.Rows.Count == 0))
            {
                if (pmDataTable != null)
                {
                    maxRow = pmDataTable.Rows.Count;
                }
            }
            else
            {
                maxRow = pmDataTable.Rows.Count > systemDataTable.Rows.Count ? systemDataTable.Rows.Count : pmDataTable.Rows.Count;
            }
            return maxRow;
        }
        private Tuple<Dictionary<string, string>, Dictionary<string, string>> GetColNameColor(DataTable pmDataTable, DataTable systemDataTable)
        {
            Dictionary<string, string> systemColorNameDict = new Dictionary<string, string>();
            foreach (var item in systemDataTable?.Columns.Cast<DataColumn>().Where(x => x.ColumnName != "internaltimestamp"))
            {
                if (!_processProcessDetailAttributeDict.ContainsKey(item.ColumnName))
                {
                    systemColorNameDict.Add(item.ColumnName, "");
                    continue;
                }


                var colAttributeName = "ColName";
                if (!_processProcessDetailAttributeDict[item.ColumnName].ContainsKey(colAttributeName))
                {
                    colAttributeName = "DisplayName";
                }
                var tempStr = _processProcessDetailAttributeDict[item.ColumnName].ContainsKey("DisplayName") && !string.IsNullOrEmpty(_processProcessDetailAttributeDict[item.ColumnName]["DisplayName"]) ? _processProcessDetailAttributeDict[item.ColumnName]["DisplayName"] : item.ColumnName;
                var resultName = string.IsNullOrEmpty(_processProcessDetailAttributeDict[item.ColumnName][colAttributeName]) ? item.ColumnName : _processProcessDetailAttributeDict[item.ColumnName][colAttributeName];

                colAttributeName = "Color";
                var resultColor = "";
                if (_processProcessDetailAttributeDict[item.ColumnName].ContainsKey(colAttributeName))
                    resultColor = string.IsNullOrEmpty(_processProcessDetailAttributeDict[item.ColumnName][colAttributeName]) ? "" : _processProcessDetailAttributeDict[item.ColumnName][colAttributeName];

                systemColorNameDict.Add(resultName, resultColor);


            }
            Dictionary<string, string> pmColorNameDict = new Dictionary<string, string>();
            foreach (var item in pmDataTable?.Columns.Cast<DataColumn>().Where(x => x.ColumnName != "internaltimestamp"))
            {
                if (!_processProcessDetailAttributeDict.ContainsKey(item.ColumnName))
                {
                    pmColorNameDict.Add(item.ColumnName, "");
                    continue;
                }


                var colAttributeName = "ColName";
                if (!_processProcessDetailAttributeDict[item.ColumnName].ContainsKey(colAttributeName))
                {
                    colAttributeName = "DisplayName";
                }
                var tempStr = _processProcessDetailAttributeDict[item.ColumnName].ContainsKey("DisplayName") && !string.IsNullOrEmpty(_processProcessDetailAttributeDict[item.ColumnName]["DisplayName"]) ? _processProcessDetailAttributeDict[item.ColumnName]["DisplayName"] : item.ColumnName;
                var resultName = string.IsNullOrEmpty(_processProcessDetailAttributeDict[item.ColumnName][colAttributeName]) ? item.ColumnName : _processProcessDetailAttributeDict[item.ColumnName][colAttributeName];
                colAttributeName = "Color";
                var resultColor = "";
                if (_processProcessDetailAttributeDict[item.ColumnName].ContainsKey(colAttributeName))
                    resultColor = string.IsNullOrEmpty(_processProcessDetailAttributeDict[item.ColumnName][colAttributeName]) ? "" : _processProcessDetailAttributeDict[item.ColumnName][colAttributeName];

                pmColorNameDict.Add(resultName, resultColor);
            }

            return new Tuple<Dictionary<string, string>, Dictionary<string, string>>(pmColorNameDict, systemColorNameDict);

        }
        private Tuple<IEnumerable<string>, IEnumerable<string>> GetColName(DataTable pmDataTable, DataTable systemDataTable)
        {

            var systemColNames = systemDataTable?.Columns.Cast<DataColumn>().Where(x => x.ColumnName != "internaltimestamp").Select(x =>
            {
                if (_processProcessDetailAttributeDict.ContainsKey(x.ColumnName))
                {
                    var colAttributeName = "ColName";
                    if (!_processProcessDetailAttributeDict[x.ColumnName].ContainsKey(colAttributeName))
                    {
                        colAttributeName = "DisplayName";
                    }
                    var tempStr = _processProcessDetailAttributeDict[x.ColumnName].ContainsKey("DisplayName") && !string.IsNullOrEmpty(_processProcessDetailAttributeDict[x.ColumnName]["DisplayName"]) ? _processProcessDetailAttributeDict[x.ColumnName]["DisplayName"] : x.ColumnName;
                    return string.IsNullOrEmpty(_processProcessDetailAttributeDict[x.ColumnName][colAttributeName]) ? x.ColumnName : _processProcessDetailAttributeDict[x.ColumnName][colAttributeName];
                }
                return x.ColumnName;
            });

            var pmColNames = pmDataTable?.Columns.Cast<DataColumn>().Where(x => x.ColumnName != "internaltimestamp").Select(x =>
            {
                if (_processProcessDetailAttributeDict.ContainsKey(x.ColumnName))
                {
                    var colAttributeName = "ColName";
                    if (!_processProcessDetailAttributeDict[x.ColumnName].ContainsKey(colAttributeName))
                    {
                        colAttributeName = "DisplayName";
                    }
                    var tempStr = _processProcessDetailAttributeDict[x.ColumnName].ContainsKey("DisplayName") && !string.IsNullOrEmpty(_processProcessDetailAttributeDict[x.ColumnName]["DisplayName"]) ? _processProcessDetailAttributeDict[x.ColumnName]["DisplayName"] : x.ColumnName;
                    return string.IsNullOrEmpty(_processProcessDetailAttributeDict[x.ColumnName][colAttributeName]) ? x.ColumnName : _processProcessDetailAttributeDict[x.ColumnName][colAttributeName];
                }
                return x.ColumnName;
            });

            return new Tuple<IEnumerable<string>, IEnumerable<string>>(pmColNames, systemColNames);
        }

        private void WriterCell(IRow row, int cellIndex, IWorkbook workbook, string setValue, ICellStyle cellStyle = null)
        {
            ICell keyCell = row.CreateCell(cellIndex);
            keyCell.SetCellValue(setValue);
            keyCell.CellStyle = cellStyle;


        }
        #endregion
        static string CheckAndReplace(string input)
        {
            if (input.StartsWith("PM1"))
            {
                return input.Substring(4);
            }
            if (input.StartsWith("System"))
            {
                return input.Substring(7);
            }
            return input;
        }

        /// <summary>
        /// 取消查询。
        /// </summary>
        public void CancelQuery()
        {
            Task.Run(() =>
            {
                if (_cancellationTokenSource?.Token.CanBeCanceled == true)
                {
                    _cancellationTokenSource.Cancel();
                }
                Thread.Sleep(100);
                //_progQueryUpdate.Report(new ProgressUpdatingEventArgs(100, 100, ""));
                //_progChartSuspendUpdating.Report(false);
            });


        }
    }
}