Virgo_scheduler/VirgoUI/Models/History/ProcessHistory/ProcessHistoryViewModel.cs

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Windows;
using System.Windows.Media;
using Aitex.Core.RT.Log;
using Aitex.Core.UI.ControlDataContext;
using Aitex.Core.UI.MVVM;
using Aitex.Sorter.Common;
using MECF.Framework.Common.CommonData;
using MECF.Framework.Common.ControlDataContext;
using MECF.Framework.Common.DataCenter;
using MECF.Framework.Common.Utilities;
using SciChart.Charting.Visuals.Annotations;
using SciChart.Charting.Visuals.Axes;
using SciChart.Charting.Visuals.RenderableSeries;
using SciChart.Data.Model;
using VirgoUI.Client.Models.Sys;
using Cali = Caliburn.Micro;

namespace VirgoUI.Client.Models.History.ProcessHistory
{
    public class ProcessHistoryViewModel : UiViewModelBase
    {
        private int MenuPermission;
        public ObservableCollection<RecipeItem> Recipes { get; set; }
        public List<HistoryDataItem> ProcessData { get; set; }
        public ObservableCollection<IRenderableSeries> SelectedData { get; set; }
        public ObservableCollection<RecipeItem> SelectedRecipes { get; set; }
        public DateTime StartDateTime { get; set; }
        public DateTime EndDateTime { get; set; }
        public string SelectedValuePM { get; set; }
        public string RecipeName { get; set; }

        public ProcessDataChartDataItem ProcessChartData
        {
            get;
            set;
        }

        public AutoRange ChartAutoRange
        {
            get { return EnableAutoZoom ? AutoRange.Always : AutoRange.Never; }
        }

        public AutoRange AutoRangeX
        {
            get => _autoRangeX;
            set
            {
                _autoRangeX = value;
                NotifyOfPropertyChange(nameof(AutoRangeX));
            }
        }

        public AutoRange AutoRangeY
        {
            get => _autoRangeY;
            set
            {
                _autoRangeY = value;
                NotifyOfPropertyChange(nameof(AutoRangeY));
            }
        }

        private bool _enableAutoZoom = true;
        public bool EnableAutoZoom
        {
            get { return _enableAutoZoom; }

            set
            {
                _enableAutoZoom = value;
                NotifyOfPropertyChange(nameof(EnableAutoZoom));
                NotifyOfPropertyChange(nameof(ChartAutoRange));
            }
        }

        private IRange _TimeRange;
        public IRange TimeRange
        {
            get { return _TimeRange; }
            set
            {
                _TimeRange = value;
                NotifyOfPropertyChange("TimeRange");
            }
        }

        private IRange _ValueRange;
        public IRange ValueRange
        {
            get { return _ValueRange; }
            set { _ValueRange = value; }
        }

        Cali.WindowManager wm = new Cali.WindowManager();
        SelectDataViewModel selectDataDlg = new SelectDataViewModel();
        private AutoRange _autoRangeX;
        private AutoRange _autoRangeY;
        private ProcessHistoryView view;

        public ProcessHistoryViewModel()
        {
            MenuPermission = ClientApp.Instance.GetPermission("ProcessHistory");
            var now = DateTime.Now;
            StartDateTime = now.AddHours(-2);
            EndDateTime = now;

            DisplayName = "Process History";
            ProcessChartData = new ProcessDataChartDataItem(60000);
            SelectedData = new ObservableCollection<IRenderableSeries>();
            SelectedRecipes = new ObservableCollection<RecipeItem>();
            Recipes = new ObservableCollection<RecipeItem>();

            TimeRange = new DateRange(DateTime.Now.AddMinutes(-1), DateTime.Now.AddMinutes(59));

            AutoRangeX = AutoRange.Once;
            AutoRangeY = AutoRange.Once;
        }

        public void SelectData()
        {
            selectDataDlg.SelectedRecipes.Clear();
            selectDataDlg.SelectedParameters.Clear();

            var settings = new Dictionary<string, object> { { "Title", "Select Recipe Data" } };
            bool? ret = wm.ShowDialog(selectDataDlg, null, settings);
            if (ret == null || !ret.Value) return;

            SelectedData.Clear();

            double valueMin = double.MaxValue;
            double valueMax = double.MinValue;
            DateTime dtMin = DateTime.MaxValue;
            DateTime dtMax = DateTime.MinValue;

            foreach (var recipe in selectDataDlg.SelectedRecipes)
            {
                if (Convert.ToDateTime(recipe.StartTime) < dtMin)
                    dtMin = Convert.ToDateTime(recipe.StartTime);

                if (!string.IsNullOrEmpty(recipe.EndTime))
                {
                    if (Convert.ToDateTime(recipe.EndTime) > dtMax)
                        dtMax = Convert.ToDateTime(recipe.EndTime);
                }

                QueryData(recipe, selectDataDlg.SelectedParameters, ref valueMin, ref valueMax);
            }

            TimeRange = new DateRange(dtMin.AddMinutes(-1), dtMax.AddMinutes(5));
            ValueRange = new DoubleRange(valueMin - 5, valueMax + 5);
        }

        public void QueryData(RecipeItem recipe, ObservableCollection<string> keys, ref double min, ref double max)
        {
            if (string.IsNullOrEmpty(recipe.StartTime))
            {
                MessageBox.Show($"can not query recipe data, did not record {recipe.Recipe} start time");
                return;
            }

            DateTime dtFrom = Convert.ToDateTime(recipe.StartTime);
            DateTime dtTo = dtFrom.AddMinutes(10);
            if (!string.IsNullOrEmpty(recipe.EndTime))
            {
                dtTo = Convert.ToDateTime(recipe.EndTime);
            }

            Dictionary<string, List<Tuple<DateTime, double>>> result;

            if (GetDbData(recipe.Chamber, dtFrom, dtTo, keys, out result, ref min, ref max))
            {
                foreach (var data in result)
                {
                    ChartDataLine line = new ChartDataLine(data.Key);
                    line.DataSource = recipe.Recipe;

                    foreach (var tuple in data.Value)
                    {
                        line.Append(tuple.Item1, tuple.Item2);
                    }
                    SelectedData.Add(line);
                }
            }
        }

        public bool GetDbData(string chamber, DateTime from, DateTime to, IEnumerable<string> dataIdList, out Dictionary<string, List<Tuple<DateTime, double>>> returnDatas, ref double min, ref double max)
        {
            returnDatas = new Dictionary<string, List<Tuple<DateTime, double>>>();
            for (DateTime dfrom = new DateTime(from.Year, from.Month, from.Day); dfrom < to; dfrom += new TimeSpan(1, 0, 0, 0))
            {
                DateTime begin = (dfrom.Year == from.Year && dfrom.Month == from.Month && dfrom.Day == from.Day) ? from : new DateTime(dfrom.Year, dfrom.Month, dfrom.Day, 0, 0, 0, 0);
                DateTime end = (dfrom.Date == to.Date) ? to : new DateTime(dfrom.Year, dfrom.Month, dfrom.Day, 23, 59, 59, 999);
                //if (begin.Date > DateTime.Today)
                //    continue;
                try
                {
                    string sql = "select time AS InternalTimeStamp";
                    foreach (var dataId in dataIdList)
                    {
                        if (!returnDatas.Keys.Contains(dataId) && dataId.StartsWith($"{chamber}."))
                        {
                            returnDatas[dataId] = new List<Tuple<DateTime, double>>();
                            sql += "," + string.Format("\"{0}\"", dataId);
                        }
                    }

                    sql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc;",
                        begin.ToString("yyyyMMdd") + "." + chamber, begin.Ticks, end.Ticks);

                    using (var table = QueryDataClient.Instance.Service.QueryData(sql))
                    {
                        if (table == null || table.Rows.Count == 0) continue;
                        DateTime dt = new DateTime();
                        Dictionary<int, string> colName = new Dictionary<int, string>();
                        for (int colNo = 0; colNo < table.Columns.Count; colNo++)
                            colName.Add(colNo, table.Columns[colNo].ColumnName);
                        for (int rowNo = 0; rowNo < table.Rows.Count; rowNo++)
                        {
                            var row = table.Rows[rowNo];

                            for (int i = 0; i < table.Columns.Count; i++)
                            {
                                if (i == 0)
                                {
                                    long ticks = (long)row[i];
                                    dt = new DateTime(ticks);
                                }
                                else
                                {
                                    string dataId = colName[i];
                                    double value = 0.0;
                                    if (row[i] is DBNull || row[i] == null)
                                    {
                                        value = 0.0;
                                    }
                                    else if (row[i] is bool)
                                    {
                                        value = (bool)row[i] ? 1.0 : 0;
                                    }
                                    else
                                    {
                                        value = (double)float.Parse(row[i].ToString());
                                    }

                                    returnDatas[dataId].Add(Tuple.Create(dt, value));
                                    if (value < min)
                                        min = value;
                                    if (value > max)
                                        max = value;
                                }
                            }
                        }
                        table.Clear();
                    }
                }
                catch (Exception ex)
                {
                    LOG.Write(ex);

                    return false;
                }
            }
            return true;
        }

        public void SearchRecipe()
        {
            if (MenuPermission != 3) return;
            this.StartDateTime = this.view.wfTimeFrom.Value;
            this.EndDateTime = this.view.wfTimeTo.Value;
            Recipes.Clear();

            //ObservableCollection<RecipeItem> items = ProcessHistoryProvider.Instance.SearchRecipe();
            //foreach (RecipeItem item in items)
            //    Recipes.Add(item);

            try
            {
                string sql = $"SELECT * FROM \"process_data\" where \"process_begin_time\" >='{StartDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff")}' and \"process_begin_time\" <='{EndDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff")}' ";

                if (!string.IsNullOrEmpty(SelectedValuePM))
                {
                    string[] pms = SelectedValuePM.Split(',');
                    if (pms.Length > 0)
                    {
                        sql += " and (FALSE ";
                        foreach (var pm in pms)
                        {
                            sql += $" OR \"process_in\"='{pm}' ";
                        }
                        sql += " ) ";
                    }
                }

                if (!string.IsNullOrEmpty(RecipeName))
                {
                    sql += string.Format(" and lower(\"recipe_name\") like '%{0}%'", RecipeName.ToLower());
                }

                sql += " order by \"process_begin_time\" ASC;";

                DataTable dbData = QueryDataClient.Instance.Service.QueryData(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    if (dbData == null || dbData.Rows.Count == 0) return;

                    for (int i = 0; i < dbData.Rows.Count; i++)
                    {
                        RecipeItem item = new RecipeItem();

                        item.Recipe = dbData.Rows[i]["recipe_name"].ToString();
                        item.Guid = dbData.Rows[i]["guid"].ToString();
                        item.RecipeRunGuid = dbData.Rows[i]["wafer_data_guid"].ToString();
                        item.Chamber = dbData.Rows[i]["process_in"].ToString();
                        item.Status = dbData.Rows[i]["process_status"].ToString();
                        item.SlotID = dbData.Rows[i]["slot_id"].ToString();
                        item.LotID = dbData.Rows[i]["lot_id"].ToString();
                        if (!dbData.Rows[i]["process_begin_time"].Equals(DBNull.Value))
                            item.StartTime = ((DateTime)dbData.Rows[i]["process_begin_time"]).ToString("yyyy-MM-dd HH:mm:ss.fff");
                        if (!dbData.Rows[i]["process_end_time"].Equals(DBNull.Value))
                            item.EndTime = ((DateTime)dbData.Rows[i]["process_end_time"]).ToString("yyyy-MM-dd HH:mm:ss.fff");
                        Recipes.Add(item);

                    }

                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }

        public void CheckRecipe(RecipeItem recipe)
        {
            if (recipe.Selected)
            {
                SelectedRecipes.Add(recipe);
            }
            else
            {
                RecipeItem item = SelectedRecipes.FirstOrDefault(t => t.Recipe == recipe.Recipe);
                if (item != null)
                    SelectedRecipes.Remove(item);
            }
        }

        public void CheckAllRecipe()
        {

        }

        public void UpdateData(RecipeItem dataLog)
        {
            if (dataLog == null)
            {
                ProcessChartData.ClearData();
                return;
            }

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

            Application.Current.Dispatcher.Invoke(new Action(() =>
            {
                Dictionary<string, Tuple<string, string, bool>> dicItems = GetDataElements("zh-CN");
                keys = dicItems.Keys.ToList();

                ProcessChartData.UpdateColumns(dicItems);
            }));

            ProcessData = QueryDataClient.Instance.Service.GetHistoryData(keys, dataLog.Guid, "Data");

            Application.Current.Dispatcher.Invoke(new Action(() =>
            {
                ProcessChartData.SetInfo(dataLog.Recipe);

                ProcessChartData.UpdateData(ProcessData);


                QueryRecipe(dataLog.Guid);


                if (DateTime.TryParse(dataLog.EndTime, out DateTime result))
                {
                    ProcessChartData.Annotations.Add(VerLine(Brushes.Blue, result, Brushes.Blue, $"Recipe End"));
                }
                

            }));
        }

        public static VerticalLineAnnotation VerLine(Brush stroke, DateTime x, Brush color, string text)
        {
            var label = new AnnotationLabel()
            {
                LabelPlacement = LabelPlacement.TopRight,
                FontSize = 12,
                RotationAngle = 0,
                Foreground = color,
                Text = text
            };

            var line = new VerticalLineAnnotation()
            {
                Stroke = stroke,
                StrokeThickness = 1,
                X1 = x,
                IsEditable = false,
                VerticalAlignment = VerticalAlignment.Stretch,
                AnnotationLabels = new ObservableCollection<AnnotationLabel>() { label },
            };
            return line;
        }
        public void QueryRecipe(string recipeGuid)
        {

            AnnotationCollection annotation = new AnnotationCollection();
 
            string sql   = $"SELECT * FROM \"recipe_step_data\" where \"process_data_guid\" = '{recipeGuid}' order by step_number ASC;";
            var dbData = QueryDataClient.Instance.Service.QueryData(sql);

            List<RecipeStep> steps = new List<RecipeStep>();
            if (dbData != null && dbData.Rows.Count > 0)
            {
                for (int i = 0; i < dbData.Rows.Count; i++)
                {
                    RecipeStep item = new RecipeStep();

                    item.No = int.Parse(dbData.Rows[i]["step_number"].ToString());
                    item.Name = dbData.Rows[i]["step_name"].ToString();

                    if (!dbData.Rows[i]["step_begin_time"].Equals(DBNull.Value))
                        item.StartTime = (DateTime)dbData.Rows[i]["step_begin_time"];

                    if (!dbData.Rows[i]["step_end_time"].Equals(DBNull.Value))
                        item.EndTime = (DateTime)dbData.Rows[i]["step_end_time"];

                    item.ActualTime = item.EndTime.CompareTo(item.StartTime) <= 0 ? "" : item.EndTime.Subtract(item.StartTime).TotalSeconds.ToString();

                    item.SettingTime = dbData.Rows[i]["step_time"].ToString();

                    annotation.Add(VerLine(Brushes.Blue, item.StartTime, Brushes.Blue, $"{item.No}:{item.Name}"));

                     
                }
            }

            ProcessChartData.Annotations = annotation;

        }

        public void DeleteAll()
        {
            SelectedData.Clear();
        }


        public void Delete(ChartDataLine cp)
        {
            if (cp != null && SelectedData.Contains(cp))
            {

                SelectedData.Remove(cp);
            }

        }

        public void ExportAll()
        {
            try
            {
                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xlsx"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.xlsx)|*.xlsx"; // Filter files by extension 
                dlg.FileName = $"Export_{DateTime.Now:yyyyMMdd_HHmmss}";
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    ds.Tables.Add(new System.Data.DataTable($"Export_{DateTime.Now:yyyyMMdd_HHmmss}"));
                    ds.Tables[0].Columns.Add("Time");
                    ds.Tables[0].Columns[0].DataType = typeof(DateTime);

                    Dictionary<DateTime, double[]> timeValue = new Dictionary<DateTime, double[]>();

                    for (int i = 0; i < SelectedData.Count; i++)
                    {
                        List<Tuple<DateTime, double>> data = (SelectedData[i] as ChartDataLine).Points;
                        foreach (var tuple in data)
                        {
                            if (!timeValue.ContainsKey(tuple.Item1))
                                timeValue[tuple.Item1] = new double[SelectedData.Count];

                            timeValue[tuple.Item1][i] = tuple.Item2;
                        }

                        ds.Tables[0].Columns.Add((SelectedData[i] as ChartDataLine).DataName);
                        ds.Tables[0].Columns[i + 1].DataType = typeof(double);
                    }

                    foreach (var item in timeValue)
                    {
                        var row = ds.Tables[0].NewRow();
                        row[0] = item.Key;
                        for (int j = 0; j < item.Value.Length; j++)
                        {
                            row[j + 1] = item.Value[j];
                        }

                        ds.Tables[0].Rows.Add(row);
                    }

                    if (!ExcelHelper.ExportToExcel(dlg.FileName, ds, out string reason))
                    {
                        MessageBox.Show($"Export failed, {reason}", "Export", MessageBoxButton.OK,
                            MessageBoxImage.Warning);
                        return;
                    }

                    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK,
                        MessageBoxImage.Information);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("Write failed," + ex.Message, "export failed", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
        }

        public void Export(ChartDataLine cp)
        {
            try
            {
                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xlsx"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.xlsx)|*.xlsx"; // Filter files by extension 
                dlg.FileName = $"{cp.DataSource}_{DateTime.Now:yyyyMMdd_HHmmss}";
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    ds.Tables.Add(new System.Data.DataTable(cp.DataName));
                    ds.Tables[0].Columns.Add("Time");
                    ds.Tables[0].Columns[0].DataType = typeof(DateTime);
                    ds.Tables[0].Columns.Add(cp.DataName);
                    ds.Tables[0].Columns[1].DataType = typeof(double);

                    foreach (var item in cp.Points)
                    {
                        var row = ds.Tables[0].NewRow();
                        row[0] = item.Item1;
                        row[1] = item.Item2;
                        ds.Tables[0].Rows.Add(row);
                    }

                    if (!ExcelHelper.ExportToExcel(dlg.FileName, ds, out string reason))
                    {
                        MessageBox.Show($"Export failed, {reason}", "Export", MessageBoxButton.OK, MessageBoxImage.Warning);
                        return;
                    }

                    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("Write failed," + ex.Message, "export failed", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
        }


        public void SelectColor(ChartDataLine cp)
        {
            if (cp == null) return;

            var dlg = new System.Windows.Forms.ColorDialog();
            if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                cp.Stroke = new System.Windows.Media.Color() { A = dlg.Color.A, B = dlg.Color.B, G = dlg.Color.G, R = dlg.Color.R };

            }
        }

        protected override void OnViewLoaded(object _view)
        {
            base.OnViewLoaded(_view);
            this.view = (ProcessHistoryView)_view;
            this.view.wfTimeFrom.Value = this.StartDateTime;
            this.view.wfTimeTo.Value = this.EndDateTime;
            //this.AppendData();
        }
        public void Query(object parameter)
        {
            WaferHistoryWafer waferHistoryRecipe = parameter as WaferHistoryWafer;
            if (waferHistoryRecipe != null)
            {
                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    if (waferHistoryRecipe.StartTime != DateTime.MinValue)
                        this.view.wfTimeFrom.Value = waferHistoryRecipe.StartTime;
                    if (waferHistoryRecipe.EndTime != DateTime.MinValue)
                        this.view.wfTimeTo.Value = waferHistoryRecipe.EndTime;
                    else
                        this.view.wfTimeTo.Value = waferHistoryRecipe.StartTime.AddHours(1);
                    SearchRecipe(waferHistoryRecipe);
                }));
            }
        }

        private void SearchRecipe(WaferHistoryWafer waferHistoryRecipe)
        {
            if (MenuPermission != 3) return;
            this.StartDateTime = this.view.wfTimeFrom.Value;
            this.EndDateTime = this.view.wfTimeTo.Value;
            Recipes.Clear();

            //ObservableCollection<RecipeItem> items = ProcessHistoryProvider.Instance.SearchRecipe();
            //foreach (RecipeItem item in items)
            //    Recipes.Add(item);

            try
            {
                string sql = $"SELECT * FROM \"process_data\" where \"process_begin_time\" >='{StartDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff")}' and \"process_begin_time\" <='{EndDateTime.ToString("yyyy/MM/dd HH:mm:ss.fff")}' ";

                if (!string.IsNullOrEmpty(SelectedValuePM))
                {
                    string[] pms = SelectedValuePM.Split(',');
                    if (pms.Length > 0)
                    {
                        sql += " and (FALSE ";
                        foreach (var pm in pms)
                        {
                            sql += $" OR \"process_in\"='{pm}' ";
                        }
                        sql += " ) ";
                    }
                }

                if (!string.IsNullOrEmpty(RecipeName))
                {
                    sql += string.Format(" and lower(\"recipe_name\") like '%{0}%'", RecipeName.ToLower());
                }

                sql += " order by \"process_begin_time\" ASC;";

                DataTable dbData = QueryDataClient.Instance.Service.QueryData(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    if (dbData == null || dbData.Rows.Count == 0) return;

                    for (int i = 0; i < dbData.Rows.Count; i++)
                    {
                        RecipeItem item = new RecipeItem();

                        item.Recipe = dbData.Rows[i]["recipe_name"].ToString();
                        item.Guid = dbData.Rows[i]["guid"].ToString();
                        item.RecipeRunGuid = dbData.Rows[i]["wafer_data_guid"].ToString();
                        item.Chamber = dbData.Rows[i]["process_in"].ToString();
                        item.Status = dbData.Rows[i]["process_status"].ToString();
                        item.SlotID = dbData.Rows[i]["slot_id"].ToString();
                        item.LotID = dbData.Rows[i]["lot_id"].ToString();
                        if (!dbData.Rows[i]["process_begin_time"].Equals(DBNull.Value))
                        {
                            item.StartTime = ((DateTime)dbData.Rows[i]["process_begin_time"]).ToString("yyyy-MM-dd HH:mm:ss.fff");
                            if ((DateTime)dbData.Rows[i]["process_begin_time"] == waferHistoryRecipe.StartTime)
                            {
                                Recipes.Add(item);
                                break;
                            }
                        }
                        if (!dbData.Rows[i]["process_end_time"].Equals(DBNull.Value))
                            item.EndTime = ((DateTime)dbData.Rows[i]["process_end_time"]).ToString("yyyy-MM-dd HH:mm:ss.fff");

                        if (item.SlotID == waferHistoryRecipe.SlotID)
                        {
                            Recipes.Add(item);
                            break;
                        }
                    }

                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }
    }
}