prs/prs.desktop/Panels/Jobs/JobSummaryGrid.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Media;
using Comal.Classes;
using InABox.Clients;
using InABox.Core;
using InABox.DynamicGrid;
using InABox.WPF;


namespace PRSDesktop
{
    internal class JobSummaryGrid : DynamicDataGrid<JobMaterial>, IJobControl, IDataModelSource
    {
        Guid empID = Guid.Empty;
        string empName = "";

        private bool _stylecolumnVisible;

        public bool IncludeReserves { get; set; }
        
        public JobSummaryGrid() : base()
        {

            ColumnsTag = nameof(JobSummaryGrid);
            
            Options.AddRange(
                DynamicGridOption.RecordCount,
                DynamicGridOption.SelectColumns,
                DynamicGridOption.FilterRows,
                DynamicGridOption.ExportData,
                DynamicGridOption.MultiSelect
            );

            HiddenColumns.Add(x => x.Product.ID);
            HiddenColumns.Add(x => x.Style.ID);
            HiddenColumns.Add(x => x.Dimensions.UnitSize);

            HiddenColumns.Add(x => x.BillOfMaterials);
            HiddenColumns.Add(x => x.Requisitions);
            HiddenColumns.Add(x => x.PickingLists);
            HiddenColumns.Add(x => x.Issued);
            
            HiddenColumns.Add(x => x.ReservedStock);
            HiddenColumns.Add(x => x.OnOrder);

            HiddenColumns.Add(x => x.JobShortage);
            
            HiddenColumns.Add(x => x.FreeOnHand);
            HiddenColumns.Add(x => x.FreeOnOrder);
            HiddenColumns.Add(x => x.FreeStockTotal);
            HiddenColumns.Add(x => x.FreeStockShortage);
            
            HiddenColumns.Add(x => x.Product.Image.ID);
            HiddenColumns.Add(x => x.Product.Image.FileName);
            ActionColumns.Add(new DynamicImageManagerColumn<JobMaterial>(this, x => x.Product.Image, false)
                { Position = DynamicActionColumnPosition.Start });

            AddButton("Create PO", null, CreatePO);

            OnCellDoubleClick += JobSummaryGrid_OnCellDoubleClick;
        }
        
        
        protected override void GenerateColumns(DynamicGridColumns columns)
        {
            columns.Add<JobMaterial, string>(x => x.Product.Group.Code, 120, "Group Code", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, string>(x => x.Product.Code, 200, "Product Code", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, string>(x => x.Product.Name, 0, "Product Name", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, string>(x => x.Dimensions.UnitSize, 120, "UOM", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.BillOfMaterials, 80, "BOM", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.Requisitions, 80, "Req.", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.PickingLists, 80, "P/L", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.Issued, 80, "Issued", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.ReservedStock, 80, "Reserved", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.OnOrder, 80, "Ordered", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.JobShortage, 80, "Job Short", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.FreeStockTotal, 80, "Free Stock", "", Alignment.MiddleCenter);
            columns.Add<JobMaterial, double>(x => x.FreeStockShortage, 80, "Stk Short", "", Alignment.MiddleCenter);
        }
        
        private void ShowDetailGrid<TEntity>(
            String columnname, 
            Expression<Func<TEntity,object?>> productcol, 
            Guid productid, 
            Expression<Func<TEntity,object?>> stylecol, 
            Guid? styleid, 
            Expression<Func<TEntity,object?>> unitcol, 
            String unitsize,
            Expression<Func<TEntity,object?>>? jobcol,
            Filter<TEntity>? extrafilter,
            Func<CoreRow,bool>? rowfilter
        )
        {
            var grid = (Activator.CreateInstance(typeof(DynamicDataGrid<>).MakeGenericType(typeof(TEntity))) as IDynamicDataGrid);
            if (grid == null)
            {
                MessageBox.Show($"Cannot create Grid for [{typeof(TEntity).Name}]");
                return;
            }
            grid.ColumnsTag = $"{ColumnsTag}.{columnname}";
            grid.Options.BeginUpdate().Clear().AddRange(DynamicGridOption.FilterRows, DynamicGridOption.SelectColumns).EndUpdate();
            grid.OnDefineFilter += t =>
            {
                var filter = new Filter<TEntity>(productcol).IsEqualTo(productid)
                    .And(unitcol).IsEqualTo(unitsize);
                if (styleid.HasValue)
                    filter = filter.And(stylecol).IsEqualTo(styleid);
                    
                if (jobcol != null)
                    filter = filter.And(jobcol).IsEqualTo(ParentID);
                    
                if (extrafilter != null)
                    filter = filter.And(extrafilter);
                return filter;
            };
            grid.OnFilterRecord += row => rowfilter?.Invoke(row) ?? true;
            var window = DynamicGridUtils.CreateGridWindow($"Viewing {CoreUtils.Neatify(columnname)} Calculation", (grid as BaseDynamicGrid)!);
            window.ShowDialog();
        }
        
        private void JobSummaryGrid_OnCellDoubleClick(object sender, DynamicGridCellClickEventArgs args)
        {
            Guid productid = args.Row.Get<StockSummary, Guid>(c => c.Product.ID);
            Guid? styleid = _stylecolumnVisible ? args.Row.Get<StockSummary, Guid>(c => c.Style.ID) : null;
            String unitsize = args.Row.Get<StockSummary, String>(c => c.Dimensions.UnitSize);

            if (String.Equals(args.Column.ColumnName, CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.BillOfMaterials, ".")))
            {
                ShowDetailGrid<JobBillOfMaterialsItem>(
                    args.Column.ColumnName, 
                    x => x.Product.ID, 
                    productid, 
                    x => x.Style.ID, 
                    styleid, 
                    x=>x.Dimensions.UnitSize,
                    unitsize,
                    x => x.Job.ID,
                    new Filter<JobBillOfMaterialsItem>(x=>x.BillOfMaterials.Approved).IsNotEqualTo(DateTime.MinValue),
                    null
                );
            }
            
            else if (String.Equals(args.Column.ColumnName, CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.Requisitions, ".")))
            {
                ShowDetailGrid<JobRequisitionItem>(
                    args.Column.ColumnName, 
                    x => x.Product.ID, 
                    productid, 
                    x => x.Style.ID, 
                    styleid, 
                    x=>x.Dimensions.UnitSize,
                    unitsize,
                    x => x.Job.ID,
                    new Filter<JobRequisitionItem>(x=>x.Requisition.Approved).IsNotEqualTo(DateTime.MinValue),
                    null
                );
            }
            
            else if (String.Equals(args.Column.ColumnName, CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.PickingLists, ".")))
            {
                ShowDetailGrid<RequisitionItem>(
                    args.Column.ColumnName, 
                    x => x.Product.ID, 
                    productid, 
                    x => x.Style.ID, 
                    styleid, 
                    x=>x.Dimensions.UnitSize,
                    unitsize,
                    x => x.Job.ID,
                    new Filter<RequisitionItem>(x=>x.RequisitionLink.Filled).IsEqualTo(DateTime.MinValue),
                    null
                );
            }
            
            else if (String.Equals(args.Column.ColumnName, CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.Issued, ".")))
            {
                ShowDetailGrid<StockMovement>(
                    args.Column.ColumnName, 
                    x => x.Product.ID, 
                    productid, 
                    x => x.Style.ID, 
                    styleid, 
                    x=>x.Dimensions.UnitSize,
                    unitsize,
                    x => x.Job.ID,
                    new Filter<StockMovement>(x=>x.IsTransfer).IsEqualTo(false).And(x=>x.Issued).IsNotEqualTo(0.0F),
                    null
                );
            }
            
            else if (String.Equals(args.Column.ColumnName, CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.ReservedStock, ".")))
            {
                ShowDetailGrid<StockHolding>(
                    args.Column.ColumnName, 
                    x => x.Product.ID, 
                    productid, 
                    x => x.Style.ID, 
                    styleid, 
                    x=>x.Dimensions.UnitSize,
                    unitsize,
                    x=>x.Job.ID,
                    new Filter<StockHolding>(x => x.Units).IsGreaterThan(0.1),
                    null
                );                  
            }
            
            else if (String.Equals(args.Column.ColumnName, CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.OnOrder, ".")))
            {
                ShowDetailGrid<PurchaseOrderItem>(
                    args.Column.ColumnName, 
                    x => x.Product.ID, 
                    productid, 
                    x => x.Style.ID, 
                    styleid, 
                    x=>x.Dimensions.UnitSize,
                    unitsize,
                    x=>x.Job.ID,
                    null,
                    null
                );                  
            }
            
            else if (String.Equals(args.Column.ColumnName, CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.FreeOnHand, ".")))
            {
                ShowDetailGrid<StockHolding>(
                    args.Column.ColumnName, 
                    x => x.Product.ID, 
                    productid, 
                    x => x.Style.ID, 
                    styleid, 
                    x=>x.Dimensions.UnitSize,
                    unitsize,
                    null,
                    new Filter<StockHolding>(x=>x.Units).IsNotEqualTo(0.0F)
                        .And(
                            IncludeReserves 
                                ?  new Filter<StockHolding>(x=>x.Job.ID).IsNotEqualTo(ParentID) 
                                : new Filter<StockHolding>(x=>x.Job.JobStatus.Active).IsEqualTo(false)
                            ),
                    null
                );
            }
            else if (String.Equals(args.Column.ColumnName, CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.FreeOnOrder, ".")))
            {
                ShowDetailGrid<PurchaseOrderItem>(
                    args.Column.ColumnName, 
                    x => x.Product.ID, 
                    productid, 
                    x => x.Style.ID, 
                    styleid, 
                    x=>x.Dimensions.UnitSize,
                    unitsize,
                    null,
                    
                    IncludeReserves 
                        ?  new Filter<PurchaseOrderItem>(x=>x.Job.ID).IsNotEqualTo(ParentID)
                        : new Filter<PurchaseOrderItem>(x=>x.Job.JobStatus.Active).IsEqualTo(false),
                    null
                );                
            }
        }

        public event DataModelUpdateEvent OnUpdateDataModel;

        public string SectionName => "Job Summary";

        public DataModel DataModel(Selection selection)
        {
            return new AutoDataModel<JobMaterial>(new Filter<JobMaterial>(x => x.Job.ID).IsEqualTo(ParentID));
        }

        public Guid ParentID { get; set; }
        
        public JobPanelSettings Settings { get; set; }

        protected override JobMaterial CreateItem()
        {
            var result = base.CreateItem();
            result.Job.ID = ParentID;
            return result;
        }
        
        private Tuple<Guid,Guid,Guid?,String>[] GetKeys(IEnumerable<CoreRow> rows, Columns<JobMaterial> columns, bool hasstyle)
        {
            int jobcol = columns.IndexOf(x => x.Job.ID);
            int productcol = columns.IndexOf(x => x.Product.ID);
            int stylecol = hasstyle ? columns.IndexOf(x => x.Style.ID) : -1;
            int unitcol = columns.IndexOf(x => x.Dimensions.UnitSize);
            
            var result = rows.Select(r => new Tuple<Guid, Guid, Guid?, String>(
                (Guid)(r.Values[jobcol] ?? Guid.Empty),
                (Guid)(r.Values[productcol] ?? Guid.Empty),
                (stylecol != -1) ? (Guid)(r.Values[stylecol] ?? Guid.Empty) : null,
                (String)(r.Values[unitcol] ?? ""))
            ).Distinct().ToArray();
            
            return result;
        }

        private CoreRow[] GetRows<TSource>(IEnumerable<CoreRow> rows, Columns<TSource> columns, Guid? jobid, Guid productid, Guid? styleid, String unitsize, Func<CoreRow,bool>? extrafilter = null) where TSource : IJobMaterial
        {
            int jobcol = columns.IndexOf(x => x.Job.ID);
            int productcol = columns.IndexOf(x => x.Product.ID);
            int stylecol = styleid.HasValue ? columns.IndexOf(x => x.Style.ID) : -1;
            int unitcol = columns.IndexOf(x => x.Dimensions.UnitSize); 
            
            var subset = rows
                .Where(r=> 
                    (!jobid.HasValue || Guid.Equals(r.Values[jobcol], jobid))
                    && Guid.Equals(r.Values[productcol], productid)
                    && (!styleid.HasValue || Guid.Equals(r.Values[stylecol], styleid))
                    && String.Equals(r.Values[unitcol], unitsize)
                    && ((extrafilter == null) || extrafilter(r) )
                );
            
            return subset.ToArray();
        }

        private double Aggregate<TSource>(IEnumerable<CoreRow> rows, Columns<TSource> columns, bool hasstyle, bool hasjob, Expression<Func<TSource, object>> source, CoreRow target, Expression<Func<JobMaterial, object>> aggregate )
        {
            int srcol = columns.IndexOf(source);
            
            if (srcol == -1)
                return 0.00;

            var total =  rows.Aggregate(0d, (value, row) => value + (double)(row.Values[srcol] ?? 0.0d));
            
            target.Set(aggregate, total);
            return total;
        }
        
        protected override void Reload(Filters<JobMaterial> criteria, Columns<JobMaterial> columns, ref SortOrder<JobMaterial>? sort,
            Action<CoreTable?, Exception?> action)
        {
            
            Filter<JobMaterial>? filter = ParentID == Guid.Empty
                ? new Filter<JobMaterial>().None()
                : new Filter<JobMaterial>(x => x.Job.ID).IsEqualTo(ParentID)
                    .And(x => x.Product.ID).IsNotEqualTo(Guid.Empty);

            var orderby = sort;
            
            Progress.ShowModal("Loading Data", 
                (progress) =>
                {
                    CoreTable table = new CoreTable();
                    table.LoadColumns(columns);
                    
                    var data = new Client<JobMaterial>().Query(filter, columns, orderby);
                    var pids = data.ExtractValues<JobMaterial, Guid>(x => x.Product.ID).ToArray();
                    
                    if (pids.Any())
                    {

                        MultiQuery query = new MultiQuery();

                        query.Add<StockHolding>(
                            new Filter<StockHolding>(x => x.Product.ID).InList(pids)
                                .And(x => x.Units).IsNotEqualTo(0.0F)
                                .And(new Filter<StockHolding>(x => x.Job.ID).IsEqualTo(Guid.Empty).Or(x=>x.Job.ID).IsNotEqualTo(ParentID)),
                            new Columns<StockHolding>(x => x.Product.ID)
                                .Add(x => x.Style.ID)
                                .Add(x => x.Dimensions.UnitSize)
                                .Add(x => x.Units)
                                .Add(x => x.Job.ID)
                                .Add(x => x.Job.JobStatus.Active)
                        );

                        query.Add<PurchaseOrderItem>(
                            new Filter<PurchaseOrderItem>(x => x.ReceivedDate).IsEqualTo(DateTime.MinValue)
                                .And(x => x.Product.ID).InList(pids)
                                .And(new Filter<PurchaseOrderItem>(x => x.Job.ID).IsEqualTo(Guid.Empty).Or(x=>x.Job.ID).IsNotEqualTo(ParentID)),
                            new Columns<PurchaseOrderItem>(x => x.Product.ID)
                                .Add(x => x.Style.ID)
                                .Add(x => x.Dimensions.UnitSize)
                                .Add(x => x.Qty)
                                .Add(x => x.Job.ID)
                                .Add(x => x.Job.JobStatus.Active)
                        );

                        query.Query();

                        var freestock = query.Get<StockHolding>();
                        Columns<StockHolding> freestockcolumns = new Columns<StockHolding>(freestock.Columns.Select(x => x.ColumnName));

                        var freeorders = query.Get<PurchaseOrderItem>();
                        Columns<PurchaseOrderItem> freeordercolumns = new Columns<PurchaseOrderItem>(freeorders.Columns.Select(x => x.ColumnName));
                        
                        var keys = GetKeys(data.Rows, columns, _stylecolumnVisible);

                        foreach (var key in keys)
                        {

                            var rows = GetRows(data.Rows, columns, key.Item1, key.Item2, key.Item3, key.Item4);
                            if (rows.Any())
                            {

                                CoreRow newrow = table.NewRow();
                                newrow.LoadValues(rows.First().Values);

                                var bom = Aggregate(rows, columns, _stylecolumnVisible, true, x => x.BillOfMaterials, newrow,
                                    x => x.BillOfMaterials);
                                var requi = Aggregate(rows, columns, _stylecolumnVisible, true, x => x.Requisitions, newrow, x => x.Requisitions);
                                var picklist = Aggregate(rows, columns, _stylecolumnVisible, true, x => x.PickingLists, newrow,
                                    x => x.PickingLists);
                                var issued = Aggregate(rows, columns, _stylecolumnVisible, true, x => x.Issued, newrow, x => x.Issued);

                                var reserved = Aggregate(rows, columns, _stylecolumnVisible, true, x => x.ReservedStock, newrow,
                                    x => x.ReservedStock);
                                var ordered = Aggregate(rows, columns, _stylecolumnVisible, true, x => x.OnOrder, newrow, x => x.OnOrder);

                                var shortage = Math.Max(0, Math.Max(0, (requi - issued)) - (reserved + ordered));
                                newrow.Set<JobMaterial, double>(x => x.JobShortage, shortage);

                                var freestockrows = GetRows(freestock.Rows, freestockcolumns, null, key.Item2, key.Item3, key.Item4, 
                                    IncludeReserves ? null : (r) => !r.Get<StockHolding,bool>(x=>x.Job.JobStatus.Active));
                                var freeonhand = Aggregate(freestockrows, freestockcolumns, _stylecolumnVisible, false, x => x.Units, newrow,
                                    x => x.FreeOnHand);
                                newrow.Set<JobMaterial, double>(x => x.FreeOnHand, freeonhand);

                                var freeorderrows = GetRows(freeorders.Rows, freeordercolumns, null, key.Item2, key.Item3, key.Item4,
                                    IncludeReserves ? null : (r) => !r.Get<PurchaseOrderItem,bool>(x=>x.Job.JobStatus.Active));
                                var freeonorder = Aggregate(freeorderrows, freeordercolumns, _stylecolumnVisible, false, x => x.Qty, newrow,
                                    x => x.FreeOnOrder);
                                newrow.Set<JobMaterial, double>(x => x.FreeOnOrder, freeonorder);

                                newrow.Set<JobMaterial, double>(x => x.FreeStockTotal, freeonhand + freeonorder);
                                newrow.Set<JobMaterial, double>(x => x.FreeStockShortage, Math.Max(0, shortage - (freeonhand + freeonorder)));
                                table.Rows.Add(newrow);
                            }


                        }
                    }

                    action?.Invoke(table, null);
                    
                }
            );
        }
        
        protected override bool FilterRecord(CoreRow row)
        {
            var result = base.FilterRecord(row)
                && (
                    row.Get<JobMaterial, double>(x => x.BillOfMaterials) != 0.0F ||
                    row.Get<JobMaterial, double>(x => x.Requisitions) != 0.0F ||
                    row.Get<JobMaterial, double>(x => x.PickingLists) != 0.0F ||
                    row.Get<JobMaterial, double>(x => x.Issued) != 0.0F ||
                    row.Get<JobMaterial, double>(x => x.ReservedStock) != 0.0F ||
                    row.Get<JobMaterial, double>(x => x.OnOrder) != 0.0F
                );
            return result;
        }
        
        private String _jobshortage = CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.JobShortage, ".");
        private String _stockshortage = CoreUtils.GetFullPropertyName<JobMaterial, double>(x => x.FreeStockShortage, ".");

        protected override Brush? GetCellBackground(CoreRow row, string columnname)
        {

            
            if (String.Equals(columnname, _jobshortage))
                return row.Get<JobMaterial, double>(x => x.JobShortage) > 0.0F ? new SolidColorBrush(Colors.LightSalmon) { Opacity = 0.5 } : null;
            if (String.Equals(columnname, _stockshortage))
                return row.Get<JobMaterial, double>(x => x.FreeStockShortage) > 0.0F ? new SolidColorBrush(Colors.LightSalmon) { Opacity = 0.5 } : null;
            return null;
        }

        #region Create PO
        private void GetEmpID()
        {
            CoreTable table = new Client<Employee>().Query(new Filter<Employee>(x => x.UserLink.UserID).IsEqualTo(ClientFactory.UserID), new Columns<Employee>(x => x.ID, x => x.Name));
            if (table.Rows.Any())
            {
                empID = Guid.Parse(table.Rows.FirstOrDefault().Values[0].ToString());
                empName = table.Rows.FirstOrDefault().Values[1].ToString();
            }
        }

        private bool CreatePO(System.Windows.Controls.Button btn, CoreRow[] rows)
        {
            if (!rows.Any())
            {
                MessageBox.Show("Please select at least one row to add to PO");
                return false;
            }
            PurchaseOrder purchaseOrder = new PurchaseOrder();
            purchaseOrder.Notes = "Created from Job Summary Screen" + System.Environment.NewLine;
            purchaseOrder.RaisedBy.ID = empID;
            var page = new SupplierPurchaseOrders();
            page.OnAfterSave += (form, items) =>
            {
                MessageBox.Show("Success - New Purchase Order Created (" + purchaseOrder.PONumber + ")");
            };
            return page.EditItems(new[] { purchaseOrder }, LoadPurchaseOrderItems, true);
        }       

        private CoreTable LoadPurchaseOrderItems(Type arg)
        {
            Progress.Show("Working");
            var result = new CoreTable();
            result.LoadColumns(typeof(PurchaseOrderItem));
            List<PurchaseOrderItem> items = new List<PurchaseOrderItem>();
            foreach (CoreRow row in SelectedRows)
            {
                JobMaterial material = row.ToObject<JobMaterial>();
                PurchaseOrderItem POItem = new PurchaseOrderItem();
                POItem.Product.ID = material.Product.ID;
                POItem.Product.Code = material.Product.Code;
                POItem.Product.Name = material.Product.Name;
                POItem.Description = material.Product.Name;
                POItem.Qty = 0;
                POItem.Dimensions.CopyFrom(material.Dimensions);
                POItem.Style.ID = material.Style.ID;
                POItem.Style.Code = material.Style.Code;
                POItem.Style.Description = material.Style.Description;
                POItem.Job.ID = material.Job.ID;
                POItem.Dimensions.UnitSize = material.Dimensions.UnitSize;
                items.Add(POItem);
            }
            result.LoadRows(items);
            Progress.Close();
            return result;
        }

        #endregion
    }
}