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

using Aitex.Core.RT.Log;
using Aitex.Core.UI.ControlDataContext;
using Aitex.Core.Util;
using Caliburn.Micro;
using Caliburn.Micro.Core;
using DocumentFormat.OpenXml.Spreadsheet;
using MECF.Framework.Common.CommonData;
using MECF.Framework.Common.CommonData.EnumData;
using MECF.Framework.Common.ControlDataContext;
using MECF.Framework.Common.DataCenter;
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.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
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));
                    }
                }
            }

            if (StepStartTime.Date == StepEndTime.Date)
            {

                SaveDataToTable(fileName, pmList, systemList, startTime, endTime, recipeInfo);
            }
            else
            {
                endTime = new DateTime(startTime.Year, startTime.Month, startTime.Day, 23, 59, 59);
                while (endTime < StepEndTime)
                {
                    SaveDataToTable(fileName, pmList, systemList, startTime, endTime, recipeInfo);
                    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);
                }
                SaveDataToTable(fileName, pmList, systemList, startTime, StepEndTime, 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
        {
            public bool IsNeed;
            public string Color;
            public short FillForegroundColor;
            public FillPattern FillPatternShort;
        }
        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;
                //short colorIndex = GetCustomPaletteColor(workbook, hexColor);
                cellStyle.FillForegroundColor = needCellColor.FillForegroundColor;
                cellStyle.FillPattern = needCellColor.FillPatternShort;
            }


            return cellStyle;

        }
        static short GetCustomPaletteColor(IWorkbook workbook, string hexColor)
        {
            if (hexColor.StartsWith("#"))
            {
                hexColor = hexColor.Substring(1);
            }

            int red = Convert.ToInt32(hexColor.Substring(0, 2), 16);
            int green = Convert.ToInt32(hexColor.Substring(2, 2), 16);
            int blue = Convert.ToInt32(hexColor.Substring(4, 2), 16);

            HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook;
            HSSFPalette palette = hssfWorkbook.GetCustomPalette();

            try
            {
                return palette.FindSimilarColor((byte)red, (byte)green, (byte)blue).Indexed;
            }
            catch (Exception)
            {
                // 如果找不到相似的颜色，则分配新的调色板索引
                HSSFColor customColor = palette.FindColor((byte)red, (byte)green, (byte)blue);
                if (customColor == null)
                {
                    customColor = palette.AddColor((byte)red, (byte)green, (byte)blue);
                }
                return customColor.Indexed;
            }
        }
        private void SaveDataToTable(string fileName, List<string> pmList, List<string> systemList, DateTime startTime, DateTime endTime, ProcessHistoryLot recipeInfo)
        {
            string stepID = "", stepName = "", subRecipeLoopInfo = "", subRecipeStepName = "", subRecipeStepNumber = "", tempCorrection = "", tempPid = "";

            DataTable pmDataTable = null;


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

            string pmsql = $"select time AS InternalTimeStamp, {string.Join(",", pmList.Select(x => string.Format("\"{0}\"", x)))}";
            DataTable systemDataTable = null;
            pmsql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc",
                   startTime.ToString("yyyyMMdd") + "." + "PM1", startTime.Ticks, endTime.Ticks);
            BusyIndicatorContent = "Example Query PM data ...";
            pmDataTable = QueryDataClient.Instance.Service.QueryData(pmsql);


            var sysColumnsql = $"select column_name from information_schema.columns where table_name = '{startTime.ToString("yyyyMMdd")}.System'";
            var sysColumnTable = QueryDataClient.Instance.Service.QueryData(sysColumnsql);
            List<string> systemCol = sysColumnTable?.Rows.Cast<DataRow>().Select(x => x.ItemArray[0].ToString()).ToList();
            systemList = systemList.Where(x => systemCol.Any(y => y == x)).ToList();
            string systemsql = $"select time AS InternalTimeStamp, {string.Join(",", systemList.Select(x => string.Format("\"{0}\"", x)))}";
            systemsql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc",
                    startTime.ToString("yyyyMMdd") + "." + "System", startTime.Ticks, endTime.Ticks);
            BusyIndicatorContent = "Example Query System data ...";
            systemDataTable = QueryDataClient.Instance.Service.QueryData(systemsql);
            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;
            }

            var subColNames = new List<DataColumn>() {
                     new DataColumn("Step\nStartDate"),
                     new DataColumn("Step\nStartTime"),
                     new DataColumn("\tStepID\t "),
                     new DataColumn("\tStepName\t"),
                     new DataColumn("SubRecipe\nStepName"),
                     new DataColumn("SubRecipe\nStepNumber"),
                     new DataColumn("SubRecipe\nLoopInfo"),
                     new DataColumn("Temp\nCorrection"),
                     new DataColumn("\tTempPID\t"),
                 };


            // Create a new workbook and sheet
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            #region style



            ICellStyle borderCellStyle = workbook.CreateCellStyle();
            borderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
            borderCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
            borderCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
            borderCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//粗线

            IFont font = workbook.CreateFont();

            borderCellStyle.SetFont(font);
            borderCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            borderCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            #endregion


            int rowIndex = 4;
            int cellIndex = 0;
            if (!firstHeader)
            {
                firstHeader = true;
                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;
                });

                // Create header row
                IRow headerRow = sheet.CreateRow(rowIndex++);
                cellIndex = 0;
                headerRow.HeightInPoints = 35;
                foreach (var subCol in subColNames)
                {
                    ICell cell = headerRow.CreateCell(cellIndex++);

                    string headerText = subCol.ColumnName.Replace("(", "\n(");
                    cell.SetCellValue(headerText);
                    cell.CellStyle = GetICellStyle(workbook);
                    sheet.SetColumnWidth(cell.ColumnIndex, subCol.ColumnName.Length * 256);
                }

                foreach (var sysCol in systemColNames ?? new string[0])
                {
                    ICell cell = headerRow.CreateCell(cellIndex++);
                    string headerText = sysCol.Replace("(", "\n(");
                    cell.SetCellValue(headerText);
                    cell.CellStyle = GetICellStyle(workbook);
                    sheet.SetColumnWidth(cell.ColumnIndex, sysCol.Length * 256);
                }

                foreach (var pmCol in pmColNames ?? new string[0])
                {
                    ICell cell = headerRow.CreateCell(cellIndex++);
                    string headerText = pmCol.Replace("(", "\n(");
                    cell.SetCellValue(headerText);
                    cell.CellStyle = GetICellStyle(workbook);
                    sheet.SetColumnWidth(cell.ColumnIndex, pmCol.Length * 256);
                }
            }

            Dictionary<string, string> keyValuePairs = new Dictionary<string, string>()
            {
                { "Recipe Info",recipeInfo.RecipeName},
                { "Recipe StartTime",recipeInfo.ProcessStartTime.ToString("yyyy-MM-dd HH:mm:ss")},
                { "Recipe EndTime",recipeInfo.ProcessEndTime.ToString("yyyy-MM-dd HH:mm:ss")},
                { "Recipe Type",recipeInfo.RecipeType.ToString()},
                { "Recipe ExecEntry",recipeInfo.RecipeExecEntry.ToString()},
            };
            rowIndex = 0;
            cellIndex = 0;
            IRow dictRow = sheet.CreateRow(rowIndex);
            dictRow.HeightInPoints = 35;
            foreach (var kvp in keyValuePairs)
            {
                ICell keyCell = dictRow.CreateCell(cellIndex++);
                keyCell.SetCellValue(kvp.Key);
                keyCell.CellStyle = GetICellStyle(workbook);
            }

            rowIndex = 1;
            cellIndex = 0;
            dictRow = sheet.CreateRow(rowIndex);
            dictRow.HeightInPoints = 35;
            foreach (var kvp in keyValuePairs)
            {
                ICell keyCell = dictRow.CreateCell(cellIndex++);
                keyCell.SetCellValue(kvp.Value);
                keyCell.CellStyle = GetICellStyle(workbook);
                //sheet.AutoSizeColumn(cellIndex);
                sheet.SetColumnWidth(cellIndex - 1, kvp.Value.Length * 256);
            }
            int columnIndex = subColNames.Count + 1;
            rowIndex = 5;

            for (int i = 0; i < maxRow; i++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex++);
                dataRow.HeightInPoints = 25;
                var pmRow = pmDataTable != null && pmDataTable.Rows.Count > 0 ? pmDataTable.Rows[i] : null;
                var systemRow = systemDataTable != null && systemDataTable.Rows.Count > 0 ? systemDataTable.Rows[i] : null;

                DateTime dateTimeKey = new DateTime(systemRow != null ? (long)systemRow[0] : (long)pmRow[0]);

                var tempStepInfo = _stepInfo.OrderByDescending(x => x.StartTime).Where(x => x.StartTime <= dateTimeKey).FirstOrDefault();

                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;

                dataRow.CreateCell(cellIndex++).SetCellValue(dateTimeKey.ToString("yyyy/MM/dd"));
                dataRow.CreateCell(cellIndex++).SetCellValue(dateTimeKey.ToString("HH:mm:ss"));
                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.ItemArray.Length; j++)
                    {
                        ICell cell = dataRow.CreateCell(cellIndex++);
                        cell.SetCellValue(systemRow[j].ToString());
                        cell.CellStyle = GetICellStyle(workbook);
                    }
                    for (int j = 0; j < (pmDataTable?.Columns.Count ?? 0); j++)
                    {
                        ICell cell = dataRow.CreateCell(cellIndex++);
                        cell.SetCellValue("");
                        cell.CellStyle = GetICellStyle(workbook);
                    }
                }
                else if (pmRow != null && systemRow == null)
                {
                    for (int j = 0; j < (systemDataTable?.Columns?.Count ?? 0); j++)
                    {
                        ICell cell = dataRow.CreateCell(cellIndex++);
                        cell.SetCellValue("");
                        cell.CellStyle = GetICellStyle(workbook);
                    }
                    for (int j = 1; j < pmRow.ItemArray.Length; j++)
                    {
                        ICell cell = dataRow.CreateCell(cellIndex++);

                        var setValueData = pmRow[j].ToString();
                        if (bool.TryParse(pmRow[j].ToString(), out var avData))
                            setValueData = pmRow[j].ToString() == bool.TrueString ? "Open" : "Close";

                        cell.SetCellValue(setValueData);
                        cell.CellStyle = GetICellStyle(workbook);
                    }
                }
                else if (systemDataTable != null)
                {
                    for (int j = 1; j < systemRow.ItemArray.Length; j++)
                    {
                        ICell cell = dataRow.CreateCell(cellIndex++);
                        cell.SetCellValue(systemRow[j].ToString());
                        cell.CellStyle = GetICellStyle(workbook);
                    }
                    for (int j = 1; j < pmRow.ItemArray.Length; j++)
                    {
                        ICell cell = dataRow.CreateCell(cellIndex++);
                        var setValueData = pmRow[j].ToString();
                        if (bool.TryParse(pmRow[j].ToString(), out var avData))
                            setValueData = pmRow[j].ToString() == bool.TrueString ? "Open" : "Close";

                        cell.SetCellValue(setValueData);
                        cell.CellStyle = GetICellStyle(workbook);
                    }
                }
            }
            sheet.SetColumnWidth(0, keyValuePairs.LastOrDefault().Key.Length * 256);
            sheet.SetColumnWidth(3, keyValuePairs.LastOrDefault().Key.Length * 256);
            sheet.SetColumnWidth(4, keyValuePairs.LastOrDefault().Key.Length * 256);
            // Write the output to a file
            using (FileStream file = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(file);
            }
        }

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


        }
    }
}