prs/prs.shared/Database Update Scripts/Update_8_57.cs

using com.sun.org.apache.xml.@internal.security.utils;
using Comal.Classes;
using InABox.Core;
using InABox.Database;
using Syncfusion.XlsIO.FormatParser.FormatTokens;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using InABox.Database.SQLite;

namespace PRS.Shared.Database_Update_Scripts;

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

    private void ApproveOldSetouts(IProvider provider)
    {
        Logger.Send(LogType.Information, "", "Approving all old setouts");

        if (provider is SQLiteProvider p)
        {
            var sql = $"update setout set [status]='Approved'";
            p.Update(sql);
            return;
        }
        
        var ids = provider.Query(
            Filter<StagingSetout>.Where(x => x.Setout.ID).IsNotEqualTo(Guid.Empty).And(
                Filter<StagingSetout>.Where(x => x.Task.ID).IsNotEqualTo(Guid.Empty)
                    .Or(x => x.UnapprovedDocuments).IsGreaterThan(0)
            ),
            Columns.None<StagingSetout>().Add(x => x.ID)).ExtractValues<StagingSetout, Guid>(x => x.Setout.ID);
        
        var setouts = provider.Query(
            Filter<Setout>.Where(x => x.Status).IsEqualTo(SetoutStatus.Unapproved)
                .And(x => x.ID).NotInList(ids),
            Columns.None<Setout>()
                .Add(x => x.ID)
                .Add(x => x.Status))
            .ToArray<Setout>()
            .ToQueue();
        var totalsetouts = setouts.Count;
        var processedsetouts = 0;
        while (setouts.Count > 0)
        {
            Logger.Send(LogType.Information, "", $"Updating {setouts.Count} staging setouts; {processedsetouts}/{totalsetouts}");
            var updates = setouts.Dequeue(100).ToArray();
            foreach(var update in updates)
                update.Status = SetoutStatus.Approved;
            provider.Save(updates);
            processedsetouts += updates.Length;
        };

        
    }

    private static void ApproveAllManufacturingPackets(IProvider provider)
    {
        Logger.Send(LogType.Information, "", "Approving all old manufacturing packets");
        if (provider is SQLiteProvider p)
        {
            var sql = $"update manufacturingpacket set [approved]=[created] where [approved] is null";
            p.Update(sql);
            return;
        }
        
        var packets = provider.Query(
                Filter<ManufacturingPacket>.Where(x => x.Approved).IsEqualTo(DateTime.MinValue),
                Columns.None<ManufacturingPacket>()
                    .Add(x => x.ID)
                    .Add(x => x.Approved)
                    .Add(x => x.Created))
            .ToArray<ManufacturingPacket>()
            .ToQueue();
        var totalpackets = packets.Count;
        var processedpackets = 0;
        while (packets.Count > 0)
        {
            Logger.Send(LogType.Information, "", $"Updating {packets.Count} staging setouts; {processedpackets}/{totalpackets}");
            var updates = packets.Dequeue(100).ToArray();
            foreach(var update in updates)
                update.Approved = update.Created;
            provider.Save(updates);
            processedpackets += updates.Length;
        }
    }

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

        ApproveOldSetouts(provider);
        ApproveAllManufacturingPackets(provider);
        ConvertStagingSetouts(provider);

        return true;
    }

    private void ConvertStagingSetouts(IProvider provider)
    {
        var stagingSetoutsQueue = provider.Query(
                Filter<StagingSetout>.Where(x => x.Setout.ID).IsEqualTo(Guid.Empty)
                    .Or(x => x.Task.ID).IsNotEqualTo(Guid.Empty)
                    .Or(x => x.UnapprovedDocuments).IsGreaterThan(0),
                Columns.None<StagingSetout>()
                    .Add(x => x.ID)
                    .Add(x => x.Created)
                    .Add(x => x.CreatedBy)
                    .Add(x => x.Number)
                    .Add(x => x.JobLink.ID)
                    .Add(x => x.Group.ID)
                    .Add(x => x.OriginalPath)
                    .Add(x => x.OriginalCRC)
                    .Add(x => x.SavePath)
                    .Add(x => x.Archived)
                    .Add(x => x.LockedBy.ID)
                    .Add(x => x.Task.ID)
                    .Add(x => x.Task.EmployeeLink.ID)
                    .Add(x => x.Task.Title)
                    .Add(x => x.Task.Description)
                    .Add(x => x.Task.Notes)
                    .Add(x => x.Task.Completed)
                    .Add(x => x.JobScope.ID)
                    .Add(x => x.Setout.ID))
            .ToArray<StagingSetout>()
            .ToQueue();

        Logger.Send(LogType.Information, "", $"Updating {stagingSetoutsQueue.Count} staging setouts");

        var total = stagingSetoutsQueue.Count;
        var processed = 0;

        while(stagingSetoutsQueue.Count > 0)
        {
            Logger.Send(LogType.Information, "", $"Updating {stagingSetoutsQueue.Count} staging setouts; {processed}/{total}");

            var stagingSetouts = stagingSetoutsQueue.Dequeue(100).ToArray();

            ProcessStagingSetouts(provider, stagingSetouts);
            
            processed += stagingSetouts.Length;
        }
    }

    void ProcessStagingSetouts(IProvider provider, StagingSetout[] stagingSetouts)
    {
        var setoutIDs = stagingSetouts.ToArray(x => x.ID);

        var stagingSetoutDocuments = provider.Query(
            Filter<StagingSetoutDocument>.Where(x => x.EntityLink.ID).InList(setoutIDs),
            Columns.None<StagingSetoutDocument>()
                .Add(x => x.Approved)
                .Add(x => x.Notes)
                .Add(x => x.Superceded)
                .Add(x => x.Thumbnail)
                .Add(x => x.Type.ID)
                .Add(x => x.EntityLink.ID)
                .Add(x => x.DocumentLink.ID))
            .ToObjects<StagingSetoutDocument>()
            .GroupByDictionary(x => x.EntityLink.ID);
        var stagingSetoutForms = provider.Query(
            Filter<StagingSetoutForm>.Where(x => x.Parent.ID).InList(setoutIDs),
            Columns.None<StagingSetoutForm>()
                .Add(x => x.Parent.ID)
                .Add(x => x.Number)
                .Add(x => x.Description)
                .Add(x => x.Form.ID)
                .Add(x => x.FormData)
                .Add(x => x.BlobData)
                .Add(x => x.FormStarted)
                .Add(x => x.FormCompleted)
                .Add(x => x.FormProcessed)
                .Add(x => x.FormCancelled)
                .Add(x => x.FormCompletedBy.ID)
                .Add(x => x.Location.Address)
                .Add(x => x.Location.Latitude)
                .Add(x => x.Location.Longitude)
                .Add(x => x.Location.Timestamp)
                .Add(x => x.FormOpen)
                .Add(x => x.Sequence))
            .ToObjects<StagingSetoutForm>()
            .GroupByDictionary(x => x.Parent.ID);
        var stagingSetoutComponents = provider.Query(
            Filter<StagingSetoutComponent>.Where(x => x.StagingSetout.ID).InList(setoutIDs)
                .And(x => x.StagingManufacturingPacketComponent.ID).IsEqualTo(Guid.Empty),
            Columns.None<StagingSetoutComponent>()
                .Add(x => x.StagingSetout.ID)
                .Add(x => x.Product.ID)
                .Add(x => x.Description)
                .AddDimensionsColumns(x => x.Dimensions, Dimensions.ColumnsType.Local)
                .Add(x => x.Quantity)
                .Add(x => x.Sequence))
            .ToObjects<StagingSetoutComponent>()
            .GroupByDictionary(x => x.StagingSetout.ID);
        var stagingManufacturingPackets = provider.Query(
            Filter<StagingManufacturingPacket>.Where(x => x.StagingSetout.ID).InList(setoutIDs),
            Columns.None<StagingManufacturingPacket>()
                .Add(x => x.ID)
                .Add(x => x.Title)
                .Add(x => x.Serial)
                .Add(x => x.ITP.ID)
                .Add(x => x.StagingSetout.ID)
                .Add(x => x.Watermark)
                .Add(x => x.Location)
                .Add(x => x.Quantity)
                .Add(x => x.BarcodeQuantity)
                .Add(x => x.Group.ID)
                .Add(x => x.Group.Watermark)
                .Add(x => x.Template.ID)
                .Add(x => x.ManufacturingPacket.ID))
            .ToObjects<StagingManufacturingPacket>()
            .GroupByDictionary(x => x.StagingSetout.ID);
        var manufacturingPackets = provider.Query(
            Filter<ManufacturingPacket>.Where(x => x.SetoutLink.ID).IsEqualTo(Guid.Empty)
                .And(x => x.ID).InQuery(
                    Filter<StagingManufacturingPacket>.Where(x => x.StagingSetout.ID).InList(setoutIDs),
                    x => x.ManufacturingPacket.ID),
            Columns.None<ManufacturingPacket>()
                .Add(x => x.ID)
                .Add(x => x.SetoutLink.ID))
            .ToObjects<ManufacturingPacket>()
            .ToDictionary(x => x.ID);
        var stagingManufacturingPacketComponents = provider.Query(
            Filter<StagingManufacturingPacketComponent>.Where(x => x.Packet.ID).InQuery(
                Filter<StagingManufacturingPacket>.Where(x => x.StagingSetout.ID).InList(setoutIDs),
                x => x.ID),
            Columns.None<StagingManufacturingPacketComponent>()
                .Add(x => x.Packet.ID)
                .Add(x => x.Description)
                .Add(x => x.Product.ID)
                .Add(x => x.Quantity)
                .AddDimensionsColumns(x => x.Dimensions, Dimensions.ColumnsType.Local))
            .ToObjects<StagingManufacturingPacketComponent>()
            .GroupByDictionary(x => x.Packet.ID);
        var stagingManufacturingPacketStages = provider.Query(
            Filter<StagingManufacturingPacketStage>.Where(x => x.Packet.ID).InQuery(
                Filter<StagingManufacturingPacket>.Where(x => x.StagingSetout.ID).InList(setoutIDs),
                x => x.ID),
            Columns.None<StagingManufacturingPacketStage>()
                .Add(x => x.Packet.ID)
                .Add(x => x.Time)
                .Add(x => x.Sequence)
                .Add(x => x.SequenceType)
                .Add(x => x.QualityChecks)
                .Add(x => x.Section.ID))
            .ToObjects<StagingManufacturingPacketStage>()
            .GroupByDictionary(x => x.Packet.ID);
        var stagingManufacturingPacketTreatments = provider.Query(
            Filter<StagingManufacturingPacketTreatment>.Where(x => x.Packet.ID).InQuery(
                Filter<StagingManufacturingPacket>.Where(x => x.StagingSetout.ID).InList(setoutIDs),
                x => x.ID),
            Columns.None<StagingManufacturingPacketTreatment>()
                .Add(x => x.Packet.ID)
                .Add(x => x.Product.ID)
                .Add(x => x.Parameter))
            .ToObjects<StagingManufacturingPacketTreatment>()
            .GroupByDictionary(x => x.Packet.ID);
        var oldSetouts = provider.Query(
            Filter<Setout>.Where(x => x.ID).InList(stagingSetouts.ToArray(x => x.Setout.ID)),
            Columns.None<Setout>()
                .Add(x => x.ID))
            .ToObjects<Setout>()
            .ToDictionary(x => x.ID);

        var oldMap = new Dictionary<Setout, StagingSetout>();
        var setouts = new List<Setout>();
        var setoutDocuments = new Dictionary<Setout, List<SetoutDocument>>();
        var setoutForms = new Dictionary<Setout, List<SetoutForm>>();
        var components = new Dictionary<Setout, List<ManufacturingPacketComponent>>();
        var packets = new Dictionary<Setout, List<ManufacturingPacket>>();
        var packetComponents = new Dictionary<Setout, Dictionary<ManufacturingPacket, List<ManufacturingPacketComponent>>>();
        var packetStages = new Dictionary<ManufacturingPacket, List<ManufacturingPacketStage>>();
        var packetTreatments = new Dictionary<ManufacturingPacket, List<ManufacturingTreatment>>();

        foreach(var stagingSetout in stagingSetouts)
        {
            if(stagingSetout.Setout.ID == Guid.Empty
                || !oldSetouts.TryGetValue(stagingSetout.Setout.ID, out var setout))
            {
                setout = new Setout();
                setout.Created = stagingSetout.Created;
                setout.CreatedBy = stagingSetout.CreatedBy;
                setout.Number = stagingSetout.Number;
                setout.JobLink.ID = stagingSetout.JobLink.ID;
                setout.Group.ID = stagingSetout.Group.ID;
                setout.Status = SetoutStatus.Unapproved;
                setout.JobScope.ID = stagingSetout.JobScope.ID;
            }
            else
            {

            }
            if (oldMap.TryAdd(setout, stagingSetout))
            {
                setouts.Add(setout);
            }

            if(stagingSetoutDocuments.TryGetValue(stagingSetout.ID, out var documents))
            {
                var newSetoutDocuments = setoutDocuments.GetValueOrAdd(setout);

                //if(documents.All(x => x.Approved))
                //{
                //    setout.Status = SetoutStatus.Approved;
                //}

                foreach(var stagingSetoutDocument in documents)
                {
                    var setoutDocument = new SetoutDocument();
                    setoutDocument.DocumentLink.ID = stagingSetoutDocument.DocumentLink.ID;
                    setoutDocument.Type.ID = stagingSetoutDocument.Type.ID;
                    setoutDocument.Thumbnail = stagingSetoutDocument.Thumbnail;
                    setoutDocument.Superceded = stagingSetoutDocument.Superceded;
                    setoutDocument.Notes = stagingSetoutDocument.Notes;
                    setoutDocument.Staging.OriginalCRC = stagingSetout.OriginalCRC;
                    setoutDocument.Staging.OriginalPath = stagingSetout.OriginalPath;
                    setoutDocument.Staging.SavePath = stagingSetout.SavePath;
                    setoutDocument.Staging.LockedBy.ID = stagingSetout.LockedBy.ID;
                    newSetoutDocuments.Add(setoutDocument);
                }
            }

            if(stagingSetout.Task.ID != Guid.Empty)
            {
                setout.Status = SetoutStatus.Cancelled;
                setout.Problem.AssignedTo.ID = stagingSetout.Task.EmployeeLink.ID;
                var notes = new List<string>()
                {
                    stagingSetout.Task.Title,
                    stagingSetout.Task.Description,
                };
                if (stagingSetout.Task.Notes != null)
                    notes.AddRange(stagingSetout.Task.Notes);
                setout.Problem.Notes = notes.ToArray();
                setout.Problem.Resolved = stagingSetout.Task.Completed;
            }

            if(stagingSetoutForms.TryGetValue(stagingSetout.ID, out var forms))
            {
                var newSetoutForms = setoutForms.GetValueOrAdd(setout);
                foreach(var form in forms)
                {
                    var newForm = new SetoutForm();
                    newForm.Number = form.Number;
                    newForm.Description = form.Description;
                    newForm.Form.ID = form.Form.ID;
                    newForm.FormData = form.FormData;
                    newForm.BlobData = form.BlobData;
                    newForm.FormStarted = form.FormStarted;
                    newForm.FormCompleted = form.FormCompleted;
                    newForm.FormProcessed = form.FormProcessed;
                    newForm.FormCancelled = form.FormCancelled;
                    newForm.FormCompletedBy.ID = form.FormCompletedBy.ID;
                    newForm.Location.CopyFrom(form.Location);
                    newForm.FormOpen = form.FormOpen;
                    newForm.Sequence = form.Sequence;

                    newSetoutForms.Add(newForm);
                }
            }

            if(stagingSetoutComponents.TryGetValue(stagingSetout.ID, out var componentsList))
            {
                var newComponents = components.GetValueOrAdd(setout);
                foreach(var component in componentsList)
                {
                    var newComponent = new ManufacturingPacketComponent();
                    newComponent.Product.ID = component.Product.ID;
                    newComponent.Description = component.Description;
                    newComponent.Dimensions.CopyFrom(component.Dimensions);
                    newComponent.Quantity = component.Quantity;
                    newComponent.Sequence = component.Sequence;
                    newComponents.Add(newComponent);
                }
            }

            if(stagingManufacturingPackets.TryGetValue(stagingSetout.ID, out var stagingPackets))
            {
                var newPackets = packets.GetValueOrAdd(setout);

                var newSetoutPacketComponents = packetComponents.GetValueOrAdd(setout);

                foreach(var stagingPacket in stagingPackets)
                {
                    if(stagingPacket.ManufacturingPacket.ID != Guid.Empty)
                    {
                        if(manufacturingPackets.TryGetValue(stagingPacket.ManufacturingPacket.ID, out var oldPacket))
                        {
                            newPackets.Add(oldPacket);
                        }
                    }
                    else
                    {
                        var newPacket = new ManufacturingPacket();
                        newPacket.Title = stagingPacket.Title;
                        newPacket.Serial = stagingPacket.Serial;
                        newPacket.ITP.ID = stagingPacket.ITP.ID;
                        newPacket.WaterMark = stagingPacket.Watermark.NotWhiteSpaceOr(stagingPacket.Group.Watermark);
                        newPacket.Location = stagingPacket.Location;
                        newPacket.Quantity = stagingPacket.Quantity;
                        newPacket.BarcodeQty = stagingPacket.BarcodeQuantity.IsNullOrWhiteSpace()
                            ? stagingPacket.Quantity
                            : int.Parse(stagingPacket.BarcodeQuantity);
                        newPacket.TemplateGroup.ID = stagingPacket.Group.ID;
                        newPacket.ManufacturingTemplateLink.ID = stagingPacket.Template.ID;
                        newPackets.Add(newPacket);

                        if(stagingManufacturingPacketComponents.TryGetValue(stagingPacket.ID, out var stagingComponents))
                        {
                            var newPacketComponents = new List<ManufacturingPacketComponent>();
                            newSetoutPacketComponents.Add(newPacket, newPacketComponents);
                            foreach(var stagingComponent in stagingComponents)
                            {
                                var component = new ManufacturingPacketComponent();
                                component.Description = stagingComponent.Description;
                                component.Product.ID = stagingComponent.Product.ID;
                                component.Quantity = stagingComponent.Quantity;
                                component.Dimensions.CopyFrom(stagingComponent.Dimensions);
                                newPacketComponents.Add(component);
                            }
                        }

                        if(stagingManufacturingPacketStages.TryGetValue(stagingPacket.ID, out var stagingStages))
                        {
                            var newStages = new List<ManufacturingPacketStage>();
                            packetStages.Add(newPacket, newStages);
                            foreach(var stagingStage in stagingStages)
                            {
                                var stage = new ManufacturingPacketStage
                                {
                                    Time = stagingStage.Time,
                                    Sequence = stagingStage.Sequence,
                                    SequenceType = stagingStage.SequenceType,
                                    Started = DateTime.MinValue,
                                    PercentageComplete = 0.0F,
                                    Completed = DateTime.MinValue,
                                    QualityChecks = stagingStage.QualityChecks,
                                    QualityStatus = QualityStatus.NotChecked,
                                    QualityNotes = ""
                                };
                                stage.ManufacturingSectionLink.ID = stagingStage.Section.ID;

                                newStages.Add(stage);
                            }
                        }

                        if(stagingManufacturingPacketTreatments.TryGetValue(stagingPacket.ID, out var stagingTreatments))
                        {
                            var newTreatments = new List<ManufacturingTreatment>();
                            packetTreatments.Add(newPacket, newTreatments);
                            foreach(var stagingTreatment in stagingTreatments)
                            {
                                var treatment = new ManufacturingTreatment();
                                treatment.Product.ID = stagingTreatment.Product.ID;
                                treatment.Parameter = stagingTreatment.Parameter;
                                newTreatments.Add(treatment);
                            }
                        }
                    }
                }
            }
        }

        provider.Save(setouts);
        foreach(var setout in setouts)
        {
            if(oldMap.TryGetValue(setout, out var stagingSetout))
            {
                stagingSetout.Setout.ID = setout.ID;
            }
            if(setoutDocuments.TryGetValue(setout, out var documents))
            {
                foreach(var document in documents)
                {
                    document.EntityLink.ID = setout.ID;
                }
            }
            if(setoutForms.TryGetValue(setout, out var forms))
            {
                foreach(var form in forms)
                {
                    form.Parent.ID = setout.ID;
                }
            }
            if(components.TryGetValue(setout, out var setoutComponents))
            {
                foreach(var component in setoutComponents)
                {
                    component.Setout.ID = setout.ID;
                }
            }
            if(packets.TryGetValue(setout, out var setoutPackets))
            {
                var setoutPacketComponents = packetComponents.GetValueOrDefault(setout);
                foreach(var packet in setoutPackets)
                {
                    packet.SetoutLink.ID = setout.ID;
                }
            }
        }
        provider.Save(stagingSetouts);
        provider.Save(setoutDocuments.SelectMany(x => x.Value).Where(x => x.EntityLink.ID != Guid.Empty));
        provider.Save(setoutForms.SelectMany(x => x.Value).Where(x => x.Parent.ID != Guid.Empty));
        provider.Save(packets.SelectMany(x => x.Value).Where(x => x.SetoutLink.ID != Guid.Empty));

        foreach(var (setout, setoutPackets) in packets)
        {
            var setoutComponents = packetComponents.GetValueOrDefault(setout);
            foreach(var packet in setoutPackets)
            {
                if (packet.SetoutLink.ID == Guid.Empty) continue;

                if(setoutComponents is not null
                    && setoutComponents.TryGetValue(packet, out var manufacturingPacketComponents))
                {
                    foreach(var component in manufacturingPacketComponents)
                    {
                        component.Setout.ID = setout.ID;
                        component.Packet.ID = packet.ID;
                    }
                }
                if(packetStages.TryGetValue(packet, out var stages))
                {
                    foreach(var stage in stages)
                    {
                        stage.Parent.ID = packet.ID;
                    }
                }
                if(packetTreatments.TryGetValue(packet, out var treatments))
                {
                    foreach(var treatment in treatments)
                    {
                        treatment.Packet.ID = packet.ID;
                    }
                }
            }
        }

        provider.Save(components.SelectMany(x => x.Value).Where(x => x.Setout.ID != Guid.Empty)
            .Concat(packetComponents.SelectMany(x => x.Value).SelectMany(x => x.Value).Where(x => x.Packet.ID != Guid.Empty && x.Setout.ID != Guid.Empty)));
        provider.Save(packetStages.SelectMany(x => x.Value).Where(x => x.Parent.ID != Guid.Empty));
        provider.Save(packetTreatments.SelectMany(x => x.Value).Where(x => x.Packet.ID != Guid.Empty));
    }
}