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.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Syncfusion.Windows.Tools.Controls;
using System.Diagnostics.CodeAnalysis;
using System.Reflection;
using FastReport.Utils;

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);
            DataUpdater.RegisterUpdateScript("7.00", Update_7_00);
            DataUpdater.RegisterUpdateScript("7.06", Update_7_06);
            DataUpdater.RegisterUpdateScript("7.14", Update_7_14);
        }

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

        private struct Map<T>
        {
            public String Old;
            public Expression<Func<T, object>> New;

            public Map(String oldcolumn, Expression<Func<T, object>> newcolumn)
            {
                Old = oldcolumn;
                New = newcolumn;
            }
        }
        
        private static bool Update_7_00()
        {
            
            static void Convert<T>(
                Filter<T> filter,
                params Map<T>[] maps
            ) where T : Entity, IPersistent, IRemotable, new()
            {
                Logger.Send(LogType.Information, "", $"Converting {typeof(T).EntityName().Split('.').Last()}...");
                List<T> updates = new List<T>();
                var columns = new Columns<T>(x => x.ID);
                foreach (var map in maps)
                {
                    if (!columns.Items.Any(x=>String.Equals(x.Property,map.Old)))
                        columns.Add(map.Old);
                    
                    if (!columns.Items.Any(x=>String.Equals(x.Property,CoreUtils.GetFullPropertyName<T, object>(map.New, "."))))
                     columns.Add(map.New);
                }

                CoreTable table = DbFactory.Provider.Query<T>(filter,columns);
                int iCount = 0;
                foreach (var row in table.Rows)
                {
                    

                    var update = row.ToObject<T>();
                    foreach (var map in maps)
                        CoreUtils.SetPropertyValue(update, CoreUtils.GetFullPropertyName<T, object>(map.New, "."), CoreUtils.GetPropertyValue(update, map.Old));
                    if (update.IsChanged())
                        updates.Add(update);
            
                    if (updates.Count == 100)
                    {
                        iCount += updates.Count;
                        Logger.Send(LogType.Information, "", $"Converting {typeof(T).EntityName().Split('.').Last()} Times ({iCount}/{table.Rows.Count}");
                        DbFactory.Provider.Save(updates);
                        updates.Clear();
                    }
                }
                
                if (updates.Count > 0)
                {
                    iCount += updates.Count;
                    Logger.Send(LogType.Information, "", $"Converting {typeof(T).EntityName().Split('.').Last()} Times ({iCount}/{table.Rows.Count})");
                    DbFactory.Provider.Save(updates);
                    updates.Clear();
                }
                
            }

            Convert<Assignment>(
                new Filter<Assignment>(x=>x.Booked.Start).IsEqualTo(DateTime.MinValue)
                    .And(x=>x.Booked.Finish).IsEqualTo(DateTime.MinValue)
                    .And(x=>x.Actual.Finish).IsEqualTo(DateTime.MinValue)
                    .And(x=>x.Actual.Finish).IsEqualTo(DateTime.MinValue),
            new Map<Assignment>("Start",x => x.Booked.Start),
                new Map<Assignment>("Finish",x => x.Booked.Finish),
                new Map<Assignment>("Start",x => x.Actual.Start),
                new Map<Assignment>("Finish",x => x.Actual.Finish)
            );
            
            // ConvertTimes<TimeSheet>(
            //     x => x.Actual.Duration,
            //     new TimeExpressions<TimeSheet>(x => x.Actual.Start, x => x.Actual.Finish),
            //     new TimeExpressions<TimeSheet>(x => x.Approved.Start, x => x.Approved.Finish)
            // );
            
            void Convert_StandardLeaves_and_LeaveRequests()
            {
                // Delete from TimeSheet where processed={} and leaverequestlink.id != empty
                var unprocessedtimesheets = DbFactory.Provider.Query<TimeSheet>(
                    new Filter<TimeSheet>(x => x.Processed).IsEqualTo(DateTime.MinValue)
                        .And(x => x.LeaveRequestLink.ID).IsNotEqualTo(Guid.Empty),
                    new Columns<TimeSheet>(x=>x.ID)
                ).Rows.Select(x=>x.ToObject<TimeSheet>()).ToArray();
                
                int iTimes = 0;
                while (iTimes < unprocessedtimesheets.Length)
                { 
                    var deletions = unprocessedtimesheets.Skip(iTimes).Take(100).ToArray();
                    DbFactory.Provider.Purge<TimeSheet>(deletions);
                    iTimes += deletions.Length;
                }
                //DbFactory.Provider.Delete<TimeSheet>(unprocessedtimesheets,"");

                // Find all Leave Requests where public holiday != empty
                var standardleaverequests = DbFactory.Provider.Query<LeaveRequest>(
                    new Filter<LeaveRequest>(x => x.PublicHoliday.ID).IsNotEqualTo(Guid.Empty),
                    new Columns<LeaveRequest>(x=>x.ID)
                        .Add(x=>x.PublicHoliday.ID)
                ).Rows.Select(x => x.ToObject<LeaveRequest>()).ToArray();
                foreach (var standardleaverequest in standardleaverequests)
                {
                    // Find all timesheets for this leave request
                    var standardleavetimesheets = DbFactory.Provider.Query<TimeSheet>(
                        new Filter<TimeSheet>(x=>x.LeaveRequestLink.ID).IsEqualTo(standardleaverequest.ID),
                        new Columns<TimeSheet>(x=>x.ID)
                            .Add(x=>x.LeaveRequestLink.ID)
                    ).Rows.Select(x=>x.ToObject<TimeSheet>()).ToArray();
                    
                    // Redirect timesheet from leaverequest to standardleave
                    foreach (var standardleavetimesheet in standardleavetimesheets)
                    {
                        standardleavetimesheet.StandardLeaveLink.ID = standardleaverequest.PublicHoliday.ID;
                        standardleavetimesheet.LeaveRequestLink.ID = Guid.Empty;
                    }
                    if (standardleavetimesheets.Any())
                        DbFactory.Provider.Save(standardleavetimesheets);
                }
                // delete these leave requests
                int iRequests = 0;
                while (iRequests < standardleaverequests.Length)
                { 
                    var deletions = standardleaverequests.Skip(iRequests).Take(100).ToArray();
                    DbFactory.Provider.Purge<LeaveRequest>(deletions);
                    iRequests += deletions.Length;
                }
                
                // Delete from Assignment where leaverequestlink id != empty
                var leaveassignments = DbFactory.Provider.Query<Assignment>(
                    new Filter<Assignment>(x => x.LeaveRequestLink.ID).IsNotEqualTo(Guid.Empty),
                    new Columns<Assignment>(x=>x.ID)
                ).Rows.Select(x=>x.ToObject<Assignment>()).ToArray();
                
                int iAssignments = 0;
                while (iAssignments < leaveassignments.Length)
                { 
                    var deletions = leaveassignments.Skip(iAssignments).Take(100).ToArray();
                    DbFactory.Provider.Purge<Assignment>(deletions);
                    iAssignments += deletions.Length;
                }

            }
                       
            Convert_StandardLeaves_and_LeaveRequests();

            return true;
        }
        
        private class Update_7_06_Class
        {
            private static Dictionary<Type, List<Tuple<Type, string>>> _cascades = new();
            private static Dictionary<Type, List<Tuple<Type, List<string>>>> _setNulls = new();

            private static void LoadDeletions(Type type)
            {
                if (_cascades.ContainsKey(type)) return;

                // Get the EntityLink that is associated with this class
                var linkclass = CoreUtils.TypeList(
                    new[] { type.Assembly },
                    x => typeof(IEntityLink).GetTypeInfo().IsAssignableFrom(x) && x.GetInheritedGenericTypeArguments().FirstOrDefault() == type
                ).FirstOrDefault();

                // if The entitylink does not exist, we don't need to do anything
                if (linkclass == null)
                    return;

                var cascades = new List<Tuple<Type, string>>();
                var setNulls = new List<Tuple<Type, List<string>>>();

                var childtypes = DbFactory.Provider.Types.Where(x => x.IsSubclassOf(typeof(Entity)) && x.GetCustomAttribute<AutoEntity>() == null);
                foreach (var childtype in childtypes)
                {
                    // Get all registered types for this entitylink
                    var fields = new List<string>();
                    var bDelete = false;

                    // Find any IEntityLink<> properties that refer back to this class
                    var childprops = CoreUtils.PropertyList(childtype, x => x.PropertyType == linkclass);

                    foreach (var childprop in childprops)
                    {
                        var fieldname = string.Format("{0}.ID", childprop.Name);
                        var attr = childprop.GetCustomAttributes(typeof(EntityRelationshipAttribute), true).FirstOrDefault();
                        if (attr != null && ((EntityRelationshipAttribute)attr).Action.Equals(DeleteAction.Cascade))
                        {
                            cascades.Add(new(childtype, fieldname));
                            bDelete = true;
                            break;
                        }

                        fields.Add(fieldname);
                    }
                    if (!bDelete && fields.Any())
                    {
                        setNulls.Add(new(childtype, fields));
                    }
                }

                _cascades[type] = cascades;
                _setNulls[type] = setNulls;
            }
            private static bool GetCascades(Type type, [NotNullWhen(true)] out List<Tuple<Type, string>>? cascades)
            {
                LoadDeletions(type);
                return _cascades.TryGetValue(type, out cascades);
            }
            private static bool GetSetNulls(Type type, [NotNullWhen(true)] out List<Tuple<Type, List<string>>>? setNulls)
            {
                LoadDeletions(type);
                return _setNulls.TryGetValue(type, out setNulls);
            }


            private static MethodInfo _deleteEntitiesMethod = typeof(Update_7_06_Class).GetMethods(BindingFlags.NonPublic | BindingFlags.Static)
                .Single(x => x.Name == nameof(DeleteEntity) && x.IsGenericMethod);
            private static void DeleteEntity<T>(Deletion deletion, Guid parentID, string parentField, DeletionData deletionData) where T : Entity, new()
            {
                var columns = DeletionData.DeletionColumns<T>();
                var delEntities = DbFactory.Provider.QueryDeleted(deletion, new Filter<T>(parentField).IsEqualTo(parentID), columns);
                var nDelntities = DbFactory.Provider.Query(new Filter<T>(parentField).IsEqualTo(parentID), columns);
                foreach (var row in delEntities.Rows.Concat(nDelntities.Rows))
                {
                    deletionData.DeleteEntity<T>(row);
                    CascadeDelete(typeof(T), deletion, row.Get<T, Guid>(x => x.ID), deletionData);
                }
            }

            private static void DeleteEntity(Type T, Deletion deletion, Guid parentID, string parentField, DeletionData deletionData)
            {
                _deleteEntitiesMethod.MakeGenericMethod(T).Invoke(null, new object?[] { deletion, parentID, parentField, deletionData });
            }


            private static MethodInfo _setNullEntityMethod = typeof(Update_7_06_Class).GetMethods(BindingFlags.NonPublic | BindingFlags.Static)
                .Single(x => x.Name == nameof(SetNullEntity) && x.IsGenericMethod);
            private static void SetNullEntity<T>(List<string> properties, Guid parentID, DeletionData deletionData) where T : Entity, new()
            {
                foreach (var property in properties)
                {
                    var entities = DbFactory.Provider.Query(new Filter<T>(property).IsEqualTo(parentID), new Columns<T>(x => x.ID));
                    foreach (var row in entities.Rows)
                    {
                        deletionData.SetNullEntity<T>(row.Get<T, Guid>(x => x.ID), property, parentID);
                    }
                }
            }

            private static void SetNullEntity(Type T, List<string> properties, Guid parentID, DeletionData deletionData)
            {
                _setNullEntityMethod.MakeGenericMethod(T).Invoke(null, new object?[] { properties, parentID, deletionData });
            }


            private static void CascadeDelete(Type type, Deletion deletion, Guid parentID, DeletionData deletionData)
            {
                if (GetCascades(type, out var cascades))
                {
                    foreach (var cascade in cascades)
                    {
                        DeleteEntity(cascade.Item1, deletion, parentID, cascade.Item2, deletionData);
                    }
                }
                if (GetSetNulls(type, out var setNulls))
                {
                    foreach (var setNull in setNulls)
                    {
                        SetNullEntity(setNull.Item1, setNull.Item2, parentID, deletionData);
                    }
                }
            }

            // Referenced via reflection.
            private static void PurgeEntityType<T>(Deletion deletion) where T : Entity, new()
            {
                var entities = DbFactory.Provider.QueryDeleted(deletion, null, DeletionData.DeletionColumns<T>()).ToObjects<T>().ToList();

                var deletionData = new DeletionData();
                foreach (var entity in entities)
                {
                    deletionData.DeleteEntity(entity);
                    CascadeDelete(typeof(T), deletion, entity.ID, deletionData);
                }

                if (deletionData.Cascades.Count > 0 || deletionData.SetNulls.Count > 0)
                {
                    var tableName = typeof(T).Name;
                    var newDeletion = new Deletion()
                    {
                        DeletionDate = DateTime.Now,
                        HeadTable = tableName,
                        Description = $"Deleted {entities.Count} entries",
                        DeletedBy = deletion.DeletedBy,
                        Data = Serialization.Serialize(deletionData)
                    };
                    DbFactory.Provider.Save(newDeletion);
                }
                DbFactory.Provider.Purge(entities);
            }

            public static void Purge(Deletion deletion)
            {
                if (deletion.ID == Guid.Empty)
                {
                    Logger.Send(LogType.Error, "", "Empty Deletion ID");
                    return;
                }

                var entityType = CoreUtils.Entities.FirstOrDefault(x => x.Name == deletion.HeadTable);
                if (entityType is null)
                {
                    Logger.Send(LogType.Error, "", $"Entity {deletion.HeadTable} does not exist");
                    return;
                }
                var purgeMethod = typeof(Update_7_06_Class).GetMethod(nameof(PurgeEntityType), BindingFlags.NonPublic | BindingFlags.Static)!;
                purgeMethod.MakeGenericMethod(entityType).Invoke(null, new object[] { deletion });

                DbFactory.Provider.Purge<Deletion>(deletion);
            }
        }

        private static bool Update_7_06()
        {
            var deletions = DbFactory.Provider.Query<Deletion>(
                new Filter<Deletion>(x => x.Data).IsEqualTo(""));
            Logger.Send(LogType.Information, "", "Updating Deletions");
            foreach (var deletion in deletions.ToObjects<Deletion>())
            {
                Update_7_06_Class.Purge(deletion);
            }
            Logger.Send(LogType.Information, "", "Finished updating Deletions");
            return true;
        }

        /// <summary>
        /// Updating Wpf and Timebench fields to use Platform.DesktopVersion and Platform.MobileVersion
        /// </summary>
        /// <returns></returns>
        private static bool Update_7_14()
        {
            Logger.Send(LogType.Information, "", "Converting User.Wpf, User.Timebench -> User.Platform.DesktopVersion, User.Platform.MobileVersion");

            Logger.Send(LogType.Information, "", "Loading Wpf, Timebench properties");
            var props = DbFactory.Provider.Query<CustomProperty>(new Filter<CustomProperty>(x => x.Name).InList("Wpf", "TimeBench"))
                .Rows.Select(x => x.ToObject<CustomProperty>()).ToArray();
            DatabaseSchema.Load(props);

            var columns = new Columns<User>(x => x.ID);
            columns.Add("Wpf", "TimeBench");

            var users = DbFactory.Provider.Query<User>(
                new Filter<User>().All(),
                columns).ToObjects<User>().ToList();
            foreach(var user in users)
            {
                if(user.UserProperties.Dictionary.TryGetValue("Wpf", out var wpf))
                {
                    user.Platform.DesktopVersion = wpf?.Value?.ToString() ?? "";
                }
                if (user.UserProperties.Dictionary.TryGetValue("TimeBench", out var timebench))
                {
                    user.Platform.MobileVersion = timebench?.Value?.ToString() ?? "";
                }
            }
            DbFactory.Provider.Save<User>(users);

            Logger.Send(LogType.Information, "", "Finished updating user versions");

            return true;
        }
    }
}