prs/prs.shared/Database Update Scripts/Update_8_58.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;

namespace PRS.Shared.Database_Update_Scripts;

internal class Update_8_58 : DatabaseUpdateScript
{
    public override VersionNumber Version => new(8, 58);

    private static void UpdateTimeSheets(IProvider provider)
    {
        Logger.Send(LogType.Information, "", $"Migrating TimeSheet.Processed -> TimeSheet.Posted");

        var timeSheets = provider.Query(
            Filter<TimeSheet>.Where(x => x.Processed).IsNotEqualTo(DateTime.MinValue)
                .And(x => x.Posted).IsEqualTo(DateTime.MinValue),
            Columns.None<TimeSheet>()
                .Add(x => x.ID)
                .Add(x => x.Processed)
                .Add(x => x.Posted))
            .ToArray<TimeSheet>();
        if(timeSheets.Length == 0)
        {
            Logger.Send(LogType.Information, "", $"Migrating TimeSheet.Processed -> TimeSheet.Posted: Done");
            return;
        }
        Logger.Send(LogType.Information, "", $"Migrating TimeSheet.Processed -> TimeSheet.Posted: {timeSheets.Length} items");
        Utils.Utils.ProcessInChunks(
            timeSheets,
            chunk =>
            {
                foreach (var timeSheet in chunk)
                {
                    timeSheet.Posted = timeSheet.Processed;
                }
                provider.Save(chunk);
            },
            200,
            percentage => Logger.Send(LogType.Information, "", $"Migrating TimeSheet.Processed: {percentage:F2}%"));
    }

    private static void ConvertLink<T, TFromLink, TToLink>(IProvider provider, Expression<Func<T, TFromLink>> fromLink, Expression<Func<T, TToLink>> toLink)
        where T : Entity, new()
        where TFromLink : IEntityLink
        where TToLink : IEntityLink
    {
        var fromColumn = Column<T>.SubColumn(fromLink, new Column<TFromLink>(x => x.ID));
        var toColumn = Column<T>.SubColumn(toLink, new Column<TToLink>(x => x.ID));

        Logger.Send(LogType.Information, "", $"Migrating {typeof(T).Name}.{fromColumn.Property} -> {typeof(T).Name}.{toColumn.Property}");

        var items = provider.Query(
            Filter<T>.Where<Guid>(fromColumn).IsNotEqualTo(Guid.Empty)
                .And<Guid>(toColumn).IsEqualTo(Guid.Empty),
            Columns.None<T>()
                .Add(x => x.ID)
                .Add(fromColumn)
                .Add(toColumn))
            .ToArray<T>();
        if(items.Length == 0)
        {
            Logger.Send(LogType.Information, "", $"Migrating {typeof(T).Name}.{fromColumn.Property} -> {typeof(T).Name}.{toColumn.Property}: Done");
            return;
        }
        Logger.Send(LogType.Information, "", $"Migrating {typeof(T).Name}.{fromColumn.Property} -> {typeof(T).Name}.{toColumn.Property}: {items.Length} items");

        Utils.Utils.ProcessInChunks(
            items,
            chunk =>
            {
                foreach (var item in chunk)
                {
                    toColumn.PropertyDefinition.Setter()(
                        item,
                        fromColumn.PropertyDefinition.Getter()(item));
                }
                provider.Save(chunk);
            },
            200,
            percentage => Logger.Send(LogType.Information, "", $"Migrating {typeof(T).Name}.{fromColumn.Property}: {percentage:F2}%"));
    }
    private static void ConvertLink<T, TLink>(IProvider provider, Expression<Func<T, TLink>> fromLink, Expression<Func<T, TLink>> toLink)
        where T : Entity, new()
        where TLink : IEntityLink
    {
        ConvertLink<T, TLink, TLink>(provider, fromLink, toLink);
    }

    private static string QuoteString(string str)
    {
        return $"\"{str.Replace("\\", "\\\\").Replace("\"", "\\\"")}\"";
    }

    private static void ConvertQAQuestions(IProvider provider)
    {
        var qaQuestions = provider.Query(
            Filter<QAQuestion>.Where(x => x.Converted).IsEqualTo(false),
            Columns.None<QAQuestion>()
                .Add(x => x.ID)
                .Add(x => x.Converted)
                .Add(x => x.Code)
                .Add(x => x.Question)
                .Add(x => x.Section)
                .Add(x => x.Description)
                .Add(x => x.Answer)
                .Add(x => x.Parameters)
                .Add(x => x.Sequence)
                .Add(x => x.Form.ID))
            .ToObjects<QAQuestion>()
            .GroupBy(x => x.Form.ID)
            .Select(x => new
            {
                FormID = x.Key,
                Questions = x.OrderBy(x => x.Sequence).ToList()
            })
            .ToList();
        Logger.Send(LogType.Information, "", $"Converting {qaQuestions.Count} QA forms into DigitalForms");
        var formSequences = provider.Query(
            Filter<DigitalFormVariable>.Where(x => x.Form.ID)
                .InList(qaQuestions.ToArray(x => x.FormID)),
            Columns.None<DigitalFormVariable>()
                .Add(x => x.Form.ID)
                .Add(x => x.Sequence))
            .ToObjects<DigitalFormVariable>()
            .GroupBy(x => x.Form.ID)
            .ToDictionary(x => x.Key, x => x.Max(x => x.Sequence));

        var variableMappings = new Dictionary<Guid, Dictionary<Guid, string>>();

        var variables = new List<DigitalFormVariable>();
        var layouts = new List<DigitalFormLayout>();
        foreach(var item in qaQuestions)
        {
            if(formSequences.TryGetValue(item.FormID, out var sequence))
            {
                sequence++;
            }
            else
            {
                sequence = 0;
            }

            var layout = new DFLayout();
            layout.ColumnWidths.Add("Auto");
            layout.ColumnWidths.Add("*");

            var codes = new HashSet<string>();
            string GenerateCode(string code)
            {
                var originalCode = code;
                var i = 1;
                while (codes.Contains(code))
                {
                    code = $"{originalCode}{i}";
                    ++i;
                }
                return code;
            }

            var mappings = variableMappings.GetValueOrAdd(item.FormID);

            var nButtons = 0;

            var i = 1;
            foreach(var question in item.Questions)
            {
                layout.RowHeights.Add("Auto");
                var row = layout.RowHeights.Count;

                if(question.Answer == QAAnswer.Comment)
                {
                    var label = new DFLayoutLabel { Caption = question.Question, Row = row, Column = 1, ColumnSpan = 3 };
                    label.Style.HorizontalTextAlignment = DFLayoutAlignment.Middle;
                    layout.Elements.Add(label);
                }
                else
                {
                    var rowNum = new DFLayoutLabel { Caption = i.ToString(), Row = row, Column = 1 };
                    var label = new DFLayoutLabel { Caption = question.Question, Row = row, Column = 2 };
                    layout.Elements.Add(rowNum);
                    layout.Elements.Add(label);

                    var variable = new DigitalFormVariable();
                    variable.Form.CopyFrom(question.Form);
                    variable.Sequence = sequence++;

                    DFLayoutFieldProperties properties;
                    Type fieldType;

                    var code = GenerateCode(question.Code.NotWhiteSpaceOr(question.Answer.ToString()));

                    var parameters = question.ParseParameters();
                    switch (question.Answer)
                    {
                        case QAAnswer.Choice:
                            {
                                // ColourExpression

                                var buttons = parameters["Options"].Split(',').ToArray(x => x.Trim());
                                var colors = parameters["Colors"].Split(',').ToArray(x => x.Trim());
                                var defValue = parameters["Default"].Trim();

                                fieldType = typeof(DFLayoutOptionField);
                                var optionProperties = new DFLayoutOptionFieldProperties();
                                properties = optionProperties;

                                optionProperties.Default = defValue;
                                optionProperties.OptionType = DFLayoutOptionType.Buttons;
                                optionProperties.Options = DFLayoutOptionFieldProperties.WriteOptions(buttons);

                                var colourExpression = "null";
                                foreach(var (option, colour) in buttons.Zip(colors))
                                {
                                    colourExpression = $"If([{code}] == {QuoteString(option)}, {QuoteString(colour)}, {colourExpression})";
                                }
                                optionProperties.ColourExpression = colourExpression;

                                nButtons = Math.Max(nButtons, buttons.Length);
                            }
                            break;
                        case QAAnswer.Number:
                            {
                                var defValue = parameters["Default"];

                                fieldType = typeof(DFLayoutDoubleField);
                                var doubleProperties = new DFLayoutDoubleFieldProperties();
                                properties = doubleProperties;

                                doubleProperties.Default = double.TryParse(defValue, out var d) ? d : default;
                            }
                            break;
                        case QAAnswer.Text:
                            {
                                var defValue = parameters["Default"];

                                fieldType = typeof(DFLayoutStringField);
                                var stringProperties = new DFLayoutStringFieldProperties();
                                properties = stringProperties;

                                stringProperties.Default = defValue;
                            }
                            break;
                        case QAAnswer.Combo:
                            {
                                var buttons = parameters["Options"].Split(',');
                                var defValue = parameters["Default"];

                                fieldType = typeof(DFLayoutOptionField);
                                var optionProperties = new DFLayoutOptionFieldProperties();
                                properties = optionProperties;

                                optionProperties.Default = defValue;
                                optionProperties.OptionType = DFLayoutOptionType.Combo;
                                optionProperties.Options = DFLayoutOptionFieldProperties.WriteOptions(buttons);
                            }
                            break;
                        default:
                            throw new Exception("Impossible");
                    }

                    properties.Code = code;
                    properties.Description = question.Description.NotWhiteSpaceOr(question.Question);
                    properties.Required = parameters.GetValueOrDefault("Default").IsNullOrWhiteSpace();

                    variable.SaveProperties(fieldType, properties);

                    mappings.Add(question.ID, variable.Code);
                    codes.Add(variable.Code);

                    var field = (Activator.CreateInstance(variable.FieldType()) as DFLayoutField)!;
                    field.Name = variable.Code;
                    field.Row = row;
                    field.Column = 3;
                    layout.Elements.Add(field);

                    variables.Add(variable);
                    ++i;
                }

                question.Converted = true;
            }

            layout.ColumnWidths.Add(Math.Max(150, nButtons * 80).ToString());

            var dfLayout = new DigitalFormLayout();
            dfLayout.Form.ID = item.FormID;
            dfLayout.Layout = layout.SaveLayout();
            dfLayout.Description = "Generated from QA form";
            dfLayout.Type = DFLayoutType.Desktop;
            dfLayout.Active = true;
            layouts.Add(dfLayout);
        }

        provider.Save(variables);
        provider.Save(layouts);
        provider.Save(qaQuestions.SelectMany(x => x.Questions));

        FormUpdater.UpdateAllForms(
            (form, variables) => false,
            (formType, instance, form, variables) =>
            {
                if (!variableMappings.TryGetValue(form.ID, out var mappings)) return false;

                var values = DigitalForm.DeserializeFormSaveData(instance) ?? new();
                var items = values.ToLoadStorage().Items().ToArray();
                foreach(var (key, value) in items)
                {
                    if (!Guid.TryParse(key, out var id)) continue;
                    if (!mappings.TryGetValue(id, out var code)) continue;

                    values.AddValue(code, value?.ToString()?.Trim());
                }

                DigitalForm.SerializeFormData(instance, values);
                return true;
            },
            filter: Filter<DigitalForm>.Where(x => x.ID).InList(qaQuestions.ToArray(x => x.FormID)));
    }

    public override bool Update()
    {
        var provider = DbFactory.NewProvider(Logger.Main);

        UpdateTimeSheets(provider);

        ConvertLink<Assignment, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<EquipmentAssignment, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<Kanban, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<Requisition, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<RequisitionItem, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<Setout, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<JobBillOfMaterialsActivity, JobLink>(provider, x => x.JobLink, x => x.Job);

        ConvertLink<ManufacturingSection, QAFormLink, DigitalFormLink>(provider, x => x.QAForm, x => x.DigitalForm);
        ConvertLink<ManufacturingTemplateStage, QAFormLink, DigitalFormLink>(provider, x => x.QAForm, x => x.DigitalForm);

        ConvertQAQuestions(provider);

        return true;
    }
}