prs/prs.desktop/Dashboards/Manufacturing/FactoryFloorAnalysis.xaml.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Input;
using Comal.Classes;
using InABox.Clients;
using InABox.Configuration;
using InABox.Core;
using InABox.WPF;
using PRSDesktop.WidgetGroups;
using Syncfusion.UI.Xaml.Grid;
using Syncfusion.UI.Xaml.Grid.Cells;
using Syncfusion.UI.Xaml.Grid.Converter;
using Syncfusion.XlsIO;
using DataRow = System.Data.DataRow;
using SelectionChangedEventArgs = System.Windows.Controls.SelectionChangedEventArgs;

namespace PRSDesktop
{
    public class FactoryFloorAnalysisDashboardProperties : IUserConfigurationSettings, IDashboardProperties { }

    public class FactoryFloorAnalysisDashboardElement : DashboardElement<FactoryFloorAnalysis, Manufacturing, FactoryFloorAnalysisDashboardProperties> { }

    /// <summary>
    ///     Interaction logic for FactoryFloorAnalysis.xaml
    /// </summary>
    public partial class FactoryFloorAnalysis : UserControl, IPanel<ManufacturingPacket>, IDashboardWidget<Manufacturing, FactoryFloorAnalysisDashboardProperties>
    {
        private bool _changing;

        private DateTime _from;

        private string _search = "";
        private DateTime _to;

        private readonly Dictionary<string, string> SectionDisplayNames = new() { { "Total", "Total" } };


        private CoreTable sections;
        private CoreTable templatestages;

        public FactoryFloorAnalysis()
        {
            _from = DateTime.Today.AddDays(0 - WeekDay(DateTime.Today));
            _to = DateTime.Today;

            InitializeComponent();

            dataGrid.CellRenderers.Remove("StackedHeader");
            dataGrid.CellRenderers.Add("StackedHeader", new GridCustomStackedRenderer(Resources));
        }

        public event DataModelUpdateEvent OnUpdateDataModel;

        public bool IsReady { get; set; }

        public void CreateToolbarButtons(IPanelHost host)
        {
        }

        public void Setup()
        {
            FromDate.SelectedDate = _from;
            ToDate.SelectedDate = _to;

            var employees = new Dictionary<Guid, string> { { Guid.Empty, "All Employees" } };
            var emps = new Client<Employee>().Query(
                LookupFactory.DefineFilter<Employee>(),
                LookupFactory.DefineColumns<Employee>(),
                LookupFactory.DefineSort<Employee>()
            );
            foreach (var row in emps.Rows)
                //if (row.Get<Employee, String>(x => x.Group.Description).Equals("FACTORY"))
                employees[row.Get<Employee, Guid>(x => x.ID)] = row.Get<Employee, string>(x => x.Name);
            Employees.ItemsSource = employees;

            var joblist = new Dictionary<Guid, string> { { Guid.Empty, "All Jobs" } };
            var jobs = new Client<Job>().Query(
                LookupFactory.DefineFilter<Job>(),
                LookupFactory.DefineColumns<Job>(),
                LookupFactory.DefineSort<Job>()
            );
            foreach (var row in jobs.Rows)
                //if (row.Get<Employee, String>(x => x.Group.Description).Equals("FACTORY"))
                joblist[row.Get<Job, Guid>(x => x.ID)] =
                    string.Format("{0} - {1}", row.Get<Job, string>(x => x.JobNumber), row.Get<Job, string>(x => x.Name));
            Jobs.ItemsSource = joblist;

            sections = new Client<ManufacturingSection>().Query(
                new Filter<ManufacturingSection>(x => x.Hidden).IsEqualTo(false),
                null,
                new SortOrder<ManufacturingSection>(x => x.Factory.Sequence).ThenBy(x => x.Sequence));

            var templates = new Dictionary<Guid, string> { { Guid.Empty, "All Templates" } };
            templatestages = new Client<ManufacturingTemplateStage>().Query();
            foreach (var row in templatestages.Rows)
                templates[row.Get<ManufacturingTemplateStage, Guid>(x => x.Template.ID)] = string.Format("{0} - {1}",
                    row.Get<ManufacturingTemplateStage, string>(x => x.Template.Code),
                    row.Get<ManufacturingTemplateStage, string>(x => x.Template.Name));
            Templates.ItemsSource = templates;

            dataGrid.ScrollMode = ScrollMode.Async;

            var columns = new Dictionary<string, List<string>>();

            var factories = new Dictionary<Guid, string> { { Guid.Empty, "All Factories" } };
            foreach (var row in sections.Rows)
            {
                var factoryid = row.Get<ManufacturingSection, Guid>(x => x.Factory.ID);
                var factoryname = row.Get<ManufacturingSection, string>(x => x.Factory.Name);
                factories[factoryid] = factoryname;

                if (!columns.ContainsKey(factoryname))
                    columns[factoryname] = new List<string>();
            }

            Factories.ItemsSource = factories;

            ReloadHeaders(columns);
        }

        public void Shutdown()
        {
        }

        public void Refresh()
        {
            var data = new DataTable();
            data.Columns.Add("Template", typeof(string));
            data.Columns.Add("Job", typeof(string));
            data.Columns.Add("Setout", typeof(string));
            data.Columns.Add("Serial", typeof(string));
            data.Columns.Add("Description", typeof(string));
            data.Columns.Add("Qty", typeof(int));

            var columns = new Dictionary<string, List<string>>();

            foreach (var row in sections.Rows)
                if (Factories.SelectedValue.Equals(Guid.Empty) ||
                    row.Get<ManufacturingSection, Guid>(x => x.Factory.ID).Equals(Factories.SelectedValue))
                {
                    var factory = row.Get<ManufacturingSection, string>(x => x.Factory.Name);
                    if (!columns.ContainsKey(factory))
                        columns[factory] = new List<string>();

                    CreateColumn(data, columns, row, "Est");
                    CreateColumn(data, columns, row, "Act");
                    CreateColumn(data, columns, row, "Var");
                }

            ReloadHeaders(columns);

            //dataGrid.CoveredCells.Clear();
            dataGrid.ItemsSource = data;

            Progress.Show("Retrieving Completed Work (0%) ...");
            data.Rows.Clear();
            var empid = Employees.SelectedValue != null ? (Guid)Employees.SelectedValue : Guid.Empty;
            var jobid = Jobs.SelectedValue != null ? (Guid)Jobs.SelectedValue : Guid.Empty;


            var stgfilter = new Filter<ManufacturingPacketStage>(x => x.Completed).IsGreaterThanOrEqualTo(_from).And(x => x.Completed)
                .IsLessThan(_to.AddDays(1));
            if (jobid != Guid.Empty)
                stgfilter = stgfilter.And(x => x.Parent.SetoutLink.JobLink.ID).IsEqualTo(jobid);
            var completedstages = new Client<ManufacturingPacketStage>().Query(stgfilter,
                new Columns<ManufacturingPacketStage>(
                    x => x.Parent.ID,
                    x => x.Parent.ManufacturingTemplateLink.ID,
                    x => x.Parent.ManufacturingTemplateLink.Code,
                    x => x.Parent.SetoutLink.JobLink.JobNumber,
                    x => x.Parent.SetoutLink.Number,
                    x => x.Parent.Serial,
                    x => x.Parent.Title,
                    x => x.Parent.Quantity
                )
            );

            var pktids = new List<Guid> { Guid.Empty };
            foreach (var stagerow in completedstages.Rows)
            {
                var id = stagerow.Get<ManufacturingPacketStage, Guid>(c => c.Parent.ID);
                if (!pktids.Contains(id))
                    pktids.Add(id);
            }

            Progress.SetMessage("Retrieving History (10%) ...");
            var filter = new Filter<ManufacturingHistory>(x => x.Employee).LinkValid(empid);

            if (jobid != Guid.Empty)
                filter = filter.And(x => x.Packet.SetoutLink.JobLink.ID).IsEqualTo(jobid);
            filter = filter.And(x => x.Packet.ID).InList(pktids.ToArray());


            var history = new Client<ManufacturingHistory>().Query(
                filter,
                new Columns<ManufacturingHistory>(
                    x => x.Packet.ID,
                    x => x.Section.ID,
                    x => x.WorkDuration,
                    x => x.QADuration
                )
            );

            var totals = new Dictionary<string, double>();
            var qtytotal = 0;
            for (var i = 0; i < pktids.Count; i++)

            {
                var bHasData = false;

                Progress.SetMessage(string.Format("Calculating ({0:F2}%) ...", 20.0F + i * 80.0F / (double)pktids.Count));

                //var packet = packets.Rows[i];
                var pktid = pktids[i];
                if (pktid != Guid.Empty)
                {
                    var row = data.NewRow();

                    // Get the First completed stage, so that we can extract the info from the packetlink
                    var srow = completedstages.Rows.FirstOrDefault(r => r.Get<ManufacturingPacketStage, Guid>(x => x.Parent.ID).Equals(pktid));
                    var stage = srow.ToObject<ManufacturingPacketStage>();

                    row["Template"] = stage.Parent.ManufacturingTemplateLink.Code;
                    row["Job"] = stage.Parent.SetoutLink.JobLink.JobNumber;
                    row["Setout"] = stage.Parent.SetoutLink.Number;
                    row["Serial"] = stage.Parent.Serial;
                    row["Description"] = stage.Parent.Title;
                    row["Qty"] = stage.Parent.Quantity;

                    var pktstages = templatestages.Rows.Where(r =>
                        r.Get<ManufacturingTemplateStage, Guid>(c => c.Template.ID).Equals(stage.Parent.ManufacturingTemplateLink.ID));
                    foreach (var stagerow in pktstages)
                    {
                        var sectionid = stagerow.Get<ManufacturingTemplateStage, Guid>(c => c.Section.ID);
                        var section = sections.Rows.FirstOrDefault(r => r.Get<ManufacturingSection, Guid>(c => c.ID).Equals(sectionid));
                        var prefix = string.Format("{0}:{1}:", section.Get<ManufacturingSection, string>(x => x.Factory.Name),
                            Regex.Replace(section.Get<ManufacturingSection, string>(x => x.Name), "[^a-zA-Z0-9]", string.Empty));

                        var estimated = stagerow.Get<ManufacturingTemplateStage, TimeSpan>(x => x.Time).TotalHours * stage.Parent.Quantity;
                        var histrecords = history.Rows.Where(r =>
                            r.Get<ManufacturingHistory, Guid>(c => c.Packet.ID).Equals(stage.Parent.ID) &&
                            r.Get<ManufacturingHistory, Guid>(c => c.Section.ID).Equals(sectionid));
                        var actual = new TimeSpan(histrecords.Sum(r =>
                            r.Get<ManufacturingHistory, TimeSpan>(c => c.WorkDuration).Ticks +
                            r.Get<ManufacturingHistory, TimeSpan>(c => c.QADuration).Ticks)).TotalHours;

                        if (actual > 0.0F)
                        {
                            if (data.Columns.Contains(prefix + "Est"))
                            {
                                UpdateColumn(totals, row, prefix + "Est", estimated);
                                UpdateColumn(totals, row, prefix + "Act", actual);
                                UpdateColumn(totals, row, prefix + "Var", actual - estimated);
                            }

                            bHasData = true;
                        }
                    }

                    if (bHasData)
                    {
                        qtytotal += (int)row["Qty"];
                        data.Rows.Add(row);
                    }
                }
            }

            var total = data.NewRow();
            total["Template"] = "Totals";
            total["qty"] = qtytotal;
            foreach (var key in totals.Keys)
                total[key] = totals[key];
            data.Rows.Add(total);
            //dataGrid.CoveredCells.Add(new CoveredCellInfo(1, 5, data.Rows.Count, data.Rows.Count));
            Progress.Close();
        }

        public string SectionName => "Factory Floor Analysis";

        public FactoryFloorAnalysisDashboardProperties Properties { get; set; }
        public event LoadSettings<FactoryFloorAnalysisDashboardProperties>? LoadSettings;
        public event SaveSettings<FactoryFloorAnalysisDashboardProperties>? SaveSettings;

        public DataModel DataModel(Selection selection)
        {
            Filter<ManufacturingPacket>? filter = null;
            if(selection == Selection.None)
            {
                filter = new Filter<ManufacturingPacket>();
            }
            return new AutoDataModel<ManufacturingPacket>(filter);
        }

        public Dictionary<string, object[]> Selected()
        {
            return new Dictionary<string, object[]>();
        }

        public void Heartbeat(TimeSpan time)
        {
        }

        private void ReloadHeaders(Dictionary<string, List<string>> columns)
        {
            dataGrid.StackedHeaderRows.Clear();
            //dataGrid.TableSummaryRows.Clear();

            var FactoryRow = new StackedHeaderRow();
            var SectionRow = new StackedHeaderRow();

            //var summaryRow = new GridTableSummaryRow();
            //summaryRow.ShowSummaryInRow = false;
            //var summaries = new ObservableCollection<ISummaryColumn>();
            //summaryRow.SummaryColumns = summaries;

            // fab / prs / glz
            foreach (var factory in columns.Keys)
            {
                FactoryRow.StackedColumns.Add(new StackedColumn
                    { ChildColumns = string.Join(",", columns[factory]), HeaderText = factory, MappingName = factory });

                var sections = new Dictionary<string, List<string>>();
                foreach (var col in columns[factory])
                {
                    var bits = col.Split(':');
                    if (!sections.ContainsKey(bits[1]))
                        sections[bits[1]] = new List<string>();
                    sections[bits[1]].Add(col);

                    //var summary = new GridSummaryColumn()
                    //{
                    //    Name = col,
                    //    MappingName = col,
                    //    SummaryType = SummaryType.DoubleAggregate,
                    //    Format = "{Sum:F2}"
                    //};
                    //summaries.Add(summary);
                }

                // cut / mach / ass
                foreach (var sect in sections.Keys)
                    SectionRow.StackedColumns.Add(new StackedColumn
                        { ChildColumns = string.Join(",", sections[sect]), HeaderText = sect, MappingName = sect });
            }

            dataGrid.StackedHeaderRows.Add(FactoryRow);
            dataGrid.StackedHeaderRows.Add(SectionRow);
            //dataGrid.TableSummaryRows.Add(summaryRow);
        }

        private void CreateColumn(DataTable data, Dictionary<string, List<string>> columns, CoreRow row, string subcolumn)
        {
            var factory = row.Get<ManufacturingSection, string>(x => x.Factory.Name);
            var section = row.Get<ManufacturingSection, string>(x => x.Name);
            var columnname = string.Format("{0}:{1}:{2}", factory, Regex.Replace(section, "[^a-zA-Z0-9]", string.Empty), subcolumn);
            columns[factory].Add(columnname);
            data.Columns.Add(columnname, typeof(double));
            SectionDisplayNames[columnname] = section;
        }

        private static void UpdateColumn(Dictionary<string, double> totals, DataRow row, string fieldname, double value)
        {
            row[fieldname] = value;
            if (!totals.ContainsKey(fieldname))
                totals[fieldname] = 0.00F;
            totals[fieldname] = totals[fieldname] + value;
        }

        private void Employees_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (IsReady && !_changing)
                Refresh();
        }

        private void Jobs_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (IsReady && !_changing)
                Refresh();
        }


        private void Factories_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (IsReady && !_changing)
                Refresh();
        }


        private void Templates_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (IsReady && !_changing)
                Refresh();
        }

        private void Export_Click(object sender, RoutedEventArgs e)
        {
            var emp = (KeyValuePair<Guid, string>)Employees.SelectedItem;
            var fact = (KeyValuePair<Guid, string>)Factories.SelectedItem;
            var temp = (KeyValuePair<Guid, string>)Templates.SelectedItem;

            var filename = string.Format("{0} - {1} - {2} - {3:yyyy-MM-dd} - {4:yyyy-MM-dd}.xlsx", emp.Value, fact.Value, temp.Value,
                FromDate.SelectedDate, ToDate.SelectedDate);

            var options = new ExcelExportingOptions();
            options.ExcelVersion = ExcelVersion.Excel2013;
            options.ExportStackedHeaders = true;
            var excelEngine = dataGrid.ExportToExcel(dataGrid.View, options);
            var workBook = excelEngine.Excel.Workbooks[0];
            workBook.SaveAs(filename);

            var startInfo = new ProcessStartInfo(filename);
            startInfo.Verb = "open";
            startInfo.UseShellExecute = true;
            Process.Start(startInfo);
        }

        private void Search_KeyUp(object sender, KeyEventArgs e)
        {
            if (string.IsNullOrWhiteSpace(Search.Text) || e.Key == Key.Return)
            {
                _search = Search.Text;
                Refresh();
            }
        }

        private void DataGrid_AutoGeneratingColumn(object sender, AutoGeneratingColumnArgs e)
        {
            e.Column.TextAlignment = TextAlignment.Center;
            e.Column.HorizontalHeaderContentAlignment = HorizontalAlignment.Center;
            e.Column.ColumnSizer = GridLengthUnitType.None;

            var value = e.Column.ValueBinding as Binding;
            if (value.Path.Path.Equals("Serial"))
            {
                e.Column.Width = 150;
                e.Column.HeaderStyle = Resources["TemplateHeaderStyle"] as Style;
            }
            else if (value.Path.Path.Equals("Job"))
            {
                e.Column.Width = 60;
                e.Column.HeaderStyle = Resources["TemplateHeaderStyle"] as Style;
            }
            else if (value.Path.Path.Equals("Setout"))
            {
                e.Column.Width = 120;
                e.Column.HeaderStyle = Resources["TemplateHeaderStyle"] as Style;
            }
            else if (value.Path.Path.Equals("Description"))
            {
                e.Column.Width = 350;
                e.Column.HeaderStyle = Resources["TemplateHeaderStyle"] as Style;
            }
            else if (value.Path.Path.Equals("Template"))
            {
                e.Column.Width = 80;
                e.Column.HeaderStyle = Resources["TemplateHeaderStyle"] as Style;
            }

            else
            {
                var style = new Style(typeof(GridCell));
                e.Column.CellStyle = style;
                e.Column.Width = 50;
                e.Column.HeaderStyle = Resources["TemplateHeaderStyle"] as Style;
                e.Column.HeaderText = e.Column.HeaderText.Split(':').Last(); //SectionDisplayNames[value.Path.Path];
            }
        }

        private class GridCustomStackedRenderer : GridStackedHeaderCellRenderer
        {
            private readonly ResourceDictionary _resources;

            public GridCustomStackedRenderer(ResourceDictionary resources)
            {
                _resources = resources;
            }

            public override void OnInitializeEditElement(DataColumnBase dataColumn, GridStackedHeaderCellControl uiElement, object dataContext)
            {
                uiElement.Style = _resources["GroupHeaderStyle"] as Style;
                base.OnInitializeEditElement(dataColumn, uiElement, dataContext);
            }
        }

        #region Date Handling

        private int WeekDay(DateTime date)
        {
            if (date.DayOfWeek == DayOfWeek.Sunday)
                return 7;
            return (int)date.DayOfWeek - 1;
        }

        private void SetDates(DateTime from, DateTime to, bool enable)
        {
            if (_changing)
                return;
            _changing = true;

            _from = from;
            FromDate.SelectedDate = from;
            FromDate.IsEnabled = enable;

            _to = to;
            ToDate.SelectedDate = to;
            ToDate.IsEnabled = enable;

            _changing = false;
            if (!enable)
                Refresh();
        }

        private void DateRange_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (!IsReady)
                return;

            if (DateRange.SelectedIndex == 0) // Week To Date
                SetDates(DateTime.Today.AddDays(0 - WeekDay(DateTime.Today)), DateTime.Today, false);
            else if (DateRange.SelectedIndex == 1) // Last 7 Days
                SetDates(DateTime.Today.AddDays(-6), DateTime.Today, false);
            else if (DateRange.SelectedIndex == 2) // Month To Date
                SetDates(new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1), DateTime.Today, false);
            else if (DateRange.SelectedIndex == 3) // Last 30 days
                SetDates(DateTime.Today.AddDays(-29), DateTime.Today, false);
            else if (DateRange.SelectedIndex == 4) // Year To Date
                SetDates(new DateTime(DateTime.Today.Year, 1, 1), DateTime.Today, false);
            else if (DateRange.SelectedIndex == 5) // Last 12 Months
                SetDates(DateTime.Today.AddYears(-1).AddDays(1), DateTime.Today, false);
            else if (DateRange.SelectedIndex == 6) // Custom
                SetDates(FromDate.SelectedDate.Value, ToDate.SelectedDate.Value, true);
        }

        private void FromDate_SelectedDateChanged(object sender, SelectionChangedEventArgs e)
        {
            if (IsReady && !_changing)
            {
                _from = FromDate.SelectedDate.Value.Date;
                Refresh();
            }
        }

        private void ToDate_SelectedDateChanged(object sender, SelectionChangedEventArgs e)
        {
            if (IsReady && !_changing)
            {
                _to = ToDate.SelectedDate.Value.Date;
                Refresh();
            }
        }

        #endregion
    }
}