prs/prs.shared/DatabaseUpdateScripts.cs

using Comal.Classes;
using InABox.Core;
using InABox.Database;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PRS.Shared
{
    public static class DatabaseUpdateScripts
    {
        public static void RegisterScripts()
        {
            DataUpdater.RegisterUpdateScript("6.31", Update_6_31);
            DataUpdater.RegisterUpdateScript("6.37", Update_6_37);
            DataUpdater.RegisterUpdateScript("6.38", Update_6_38);
            DataUpdater.RegisterUpdateScript("6.39", Update_6_39);
            DataUpdater.RegisterUpdateScript("6.43", Update_6_43);
        }

        private static Dictionary<string, Tuple<string, string>> _6_31_module_map = new()
        {
            { "Assignments", new("Assignments", "Assignments") },
            { "Daily Report", new("Daily Report", "Assignments") },
            { "Delivered On Site", new("Delivered On Site", "Delivery Items") },
            { "Deliveries", new("Deliveries", "Deliveries") },
            { "Digital Forms", new("Digital Forms", "DigitalForm") },
            { "Employee List", new("Employees", "Employee") },
            { "Equipment List", new("Equipment", "Equipment") },
            { "Factory Floor", new("Factory", "Manufacturing Packets") },
            { "Incoming Consignments", new("Consignments", "Consignment") },
            { "Manufacturing Status", new("Manufacturing Packets", "Manufacturing Packets") },
            { "Product List", new("Products", "Products") },
            { "Projects", new("Job Details", "Job Details") },
            { "Purchase Orders", new("Purchase Orders", "PurchaseOrder") },
            { "Quotes", new("Quotes", "Quotes") },
            { "Rack List", new("Shipping", "Shipments") },
            { "Site Requisitions", new("Requisitions", "Requisition") },
            { "Staff TimeSheets", new("Timesheets", "TimeSheet") },
            { "Stock Locations", new("Stock Locations", "StockLocation") },
            { "Stock Movements", new("Stock Movements", "StockMovement") },
            { "Task List", new("Tasks By Status", "Kanban") },
        };
        private static bool Update_6_31()
        {
            var modules = DbFactory.Provider.Query(new Filter<CustomModule>().All())
                .Rows.Select(x => x.ToObject<CustomModule>()).ToList();

            foreach(var module in modules)
            {
                if (!string.IsNullOrWhiteSpace(module.Section))
                {
                    if (_6_31_module_map.TryGetValue(module.Section, out var map))
                    {
                        module.Section = map.Item1;
                        module.DataModel = map.Item2;
                        module.AllRecords = true;
                    }
                    else
                    {
                        Logger.Send(LogType.Error, "", $"Custom Module '{module.Name}' has section name '{module.Section}' and will no longer be visible!");
                    }
                }
            }
            DbFactory.Provider.Save(modules);

            return true;
        }

        private static bool Update_6_37()
        {
            Logger.Send(LogType.Information, "", "Recreating views");
            DbFactory.Provider.ForceRecreateViews();
            return true;
        }
        
        private static bool Update_6_38()
        {
            
            Logger.Send(LogType.Information, "", "Converting Job Requisition Dates to Due Dates");
            List<JobRequisition> updates = new List<JobRequisition>();
            var columns = new Columns<JobRequisition>(x => x.ID);
            columns.Add("Date");
            CoreTable requis = DbFactory.Provider.Query<JobRequisition>(null, columns);
            foreach (var row in requis.Rows)
            {
                var requi = row.ToObject<JobRequisition>();
                requi.Approved = row.Get<DateTime>("Date");
                updates.Add(requi);
            }
            DbFactory.Provider.Save(updates);
            
            return true;
        }
        
        private static bool Update_6_39()
        {
            void ConvertJobDocumentIssuedDates()
            {
                Logger.Send(LogType.Information, "", "Converting Job Document Issued Dates");
                List<JobDocumentSetMileStone> updates = new List<JobDocumentSetMileStone>();
                var columns = new Columns<JobDocumentSetMileStone>(x => x.ID).Add(x => x.Submitted).Add(x => x.Status);
                columns.Add("Issued");
                CoreTable milestones = DbFactory.Provider.Query<JobDocumentSetMileStone>(null, columns);
                foreach (var row in milestones.Rows)
                {
                    var milestone = row.ToObject<JobDocumentSetMileStone>();
                    if (milestone.Status == JobDocumentSetMileStoneStatus.Unknown)
                        milestone.Status = JobDocumentSetMileStoneStatus.Submitted;
                    milestone.Submitted = row.Get<DateTime>("Issued");
                    updates.Add(milestone);
                }

                DbFactory.Provider.Save(updates);
            }
            
            void ConvertProductUnitsOfMeasure()
            {
                Logger.Send(LogType.Information, "", "Converting Product Units of Measure");
                List<ProductDimensionUnit> updates = new List<ProductDimensionUnit>();
                var columns = new Columns<ProductDimensionUnit>(x => x.ID).Add(x => x.Description);
                CoreTable units = DbFactory.Provider.Query<ProductDimensionUnit>(new Filter<ProductDimensionUnit>(x=>x.Code).IsEqualTo(""), columns);
                foreach (var row in units.Rows)
                {
                    var unit = row.ToObject<ProductDimensionUnit>();
                    unit.Code = unit.Description;
                    updates.Add(unit);
                }

                DbFactory.Provider.Save(updates);
            }
            
            void ConvertQuoteUnitsOfMeasure()
            {
                Logger.Send(LogType.Information, "", "Converting Quote Units of Measure");
                List<QuoteTakeOffUnit> updates = new List<QuoteTakeOffUnit>();
                var columns = new Columns<QuoteTakeOffUnit>(x => x.ID).Add(x => x.Description);
                CoreTable units = DbFactory.Provider.Query<QuoteTakeOffUnit>(new Filter<QuoteTakeOffUnit>(x=>x.Code).IsEqualTo(""), columns);
                foreach (var row in units.Rows)
                {
                    var unit = row.ToObject<QuoteTakeOffUnit>();
                    unit.Code = unit.Description;
                    updates.Add(unit);
                }

                DbFactory.Provider.Save(updates);
            }
            
            ConvertJobDocumentIssuedDates();
            
            ConvertProductUnitsOfMeasure();

            ConvertQuoteUnitsOfMeasure();
            
            return true;
        }

        private static bool Update_6_43()
        {
            void ConvertSupplierProductLinks()
            {
                Logger.Send(LogType.Information, "", "Converting Supplier/Product Links");
                List<SupplierProduct> updates = new List<SupplierProduct>();
                var columns = new Columns<SupplierProduct>(x => x.ID).Add(x=>x.Product.ID);
                columns.Add("ProductLink.ID");
                CoreTable products = DbFactory.Provider.Query<SupplierProduct>(null, columns);
                foreach (var row in products.Rows)
                {
                    Guid id = row.Get<SupplierProduct,Guid>(x=>x.ID);
                    Guid oldid = row.Get<Guid>("ProductLink.ID");
                    Guid newid = row.Get<SupplierProduct,Guid>(x=>x.Product.ID);
                    if ((oldid != Guid.Empty) && (newid == Guid.Empty))
                    {
                        var update = new SupplierProduct() { ID = id };
                        update.CommitChanges();
                        update.Product.ID = oldid;
                        updates.Add(update);
                    }
                }

                DbFactory.Provider.Save(updates);
            }

            ConvertSupplierProductLinks();

            return true;
        }
        
    }
}