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

using Comal.Classes;
using InABox.Core;
using InABox.Database;

namespace PRS.Shared.Database_Update_Scripts;

internal class Update_8_24 : DatabaseUpdateScript
{
    private Type jripoiType = null;
    
    public Update_8_24()
    {
        jripoiType = CoreUtils.GetEntity("Comal.Classes.JobRequisitionItemPurchaseOrderItem");
    } 

    private static readonly int CHUNK_SIZE = 500;
    private static readonly bool DESTRUCTIVE = false;
    
    public override VersionNumber Version => new(8, 24);
    
    public override bool Update()
    {
        var _provider = DbFactory.NewProvider(Logger.Main);
        Clear_POIAs(_provider);
        var _poias = Migrate_JRIPOIs(_provider);
        Migrate_POIs(_provider, _poias);
        return true;
    }

    private void Clear_POIAs(IProvider provider)
    {
        Logger.Send(LogType.Information, "", "Clearing Existing Allocations");
        var _queue = provider.Query(
            new Filter<PurchaseOrderItemAllocation>().All(),
            Columns.None<PurchaseOrderItemAllocation>().Add(x => x.ID)
        ).Rows.ToQueue();

        while (_queue.Any())
        {
            List<PurchaseOrderItemAllocation> _poias = _queue
                .Dequeue(CHUNK_SIZE)
                .Select(x => x.ToObject<PurchaseOrderItemAllocation>())
                .ToList();
            provider.Delete(_poias, "");

            Logger.Send(LogType.Information, "", $"- Deleted {_poias.Count} Allocations ({_queue.Count} remaining)");
        }
    }

    private static readonly string RANDOMGUID =
        "lower(hex(randomblob(4)) " +
        "|| '-' || hex(randomblob(2)) " +
        "|| '-' || '4' || substr(hex( randomblob(2)), 2) " +
        "|| '-' || substr('AB89', 1 + (abs(random()) % 4) , 1)  || substr(hex(randomblob(2)), 2) " +
        "|| '-' || hex(randomblob(6)))";

    private List<PurchaseOrderItemAllocation> Migrate_JRIPOIs(IProvider provider)
    {
        // var sql = $"insert into PurchaseOrderItemAllocation (ID, [JobRequisitionItem.ID], Qty)" +
        //           $"select " +
        //           $"lower(hex(randomblob(4)) " +
        //           $"|| '-' || hex(randomblob(2)) " +
        //           $"|| '-' || '4' || substr(hex( randomblob(2)), 2) " +
        //           $"|| '-' || substr('AB89', 1 + (abs(random()) % 4) , 1)  || substr(hex(randomblob(2)), 2) " +
        //           $"|| '-' || hex(randomblob(6))) as ID, " +
        //           $"jripoi.[PurchaseOrderItem.ID],jripoi.[JobRequisitionItem.ID],poi.Qty " +
        //           $"from JobRequisitionItemPurchaseOrderItem jripoi " +
        //           $"join PurchaseOrderItem poi on poi.[ID] = jripoi.[PurchaseOrderItem.ID]";
        
        var _result = new List<PurchaseOrderItemAllocation>();

        if (!provider.TableExists(jripoiType))
            return _result;
        
        Logger.Send(LogType.Information,"","Migrating JobRequisitionItems");
        var _queue = provider.Query(jripoiType,
            null,
            Columns.None(jripoiType)
                .Add("PurchaseOrderItem.ID")
                .Add("PurchaseOrderItem.Qty")
                .Add("JobRequisitionItem.ID")
                .Add("JobRequisitionItem.Job.ID")
        ).Rows.ToQueue();
        
        while (_queue.Any())
        {
            List<PurchaseOrderItemAllocation> _poias = new();
            var _rows = _queue.Dequeue(CHUNK_SIZE).ToList();
            foreach (var _row in _rows)
            {
                var _poia = new PurchaseOrderItemAllocation();
                _poia.Item.ID = _row.Get<Guid>("PurchaseOrderItem.ID");
                _poia.Job.ID = _row.Get<Guid>("JobRequisitionItem.Job.ID");
                _poia.JobRequisitionItem.ID = _row.Get<Guid>("JobRequisitionItem.ID");
                _poia.Quantity = _row.Get<double>("PurchaseOrderItem.Qty");
                _poias.Add(_poia);
                CoreUtils.SetPropertyValue(_row,"Job.ID",Guid.Empty);
            }
            provider.Save(_poias);
            Logger.Send(LogType.Information, "", $"- Created {_poias.Count} Allocations ({_queue.Count} remaining)");
            _result.AddRange(_poias);
        }
        
        if (DESTRUCTIVE)
            provider.DropTable(jripoiType);
        
        return _result;
    }
    
    private void Migrate_POIs(IProvider provider, List<PurchaseOrderItemAllocation> poias)
    {
        Logger.Send(LogType.Information,"","Migrating PurchaseOrderItems");
        var _ids = poias.Select(x => x.Item.ID).Distinct().ToArray();
        var _queue = provider.Query(
            new Filter<PurchaseOrderItem>("Job.ID").IsNotEqualTo(Guid.Empty),
            Columns.Required<PurchaseOrderItem>().Add("Job.ID")
        ).Rows.ToQueue();
        
        while (_queue.Any())
        {
            List<PurchaseOrderItemAllocation> _poias = new();
            var _pois = _queue.Dequeue(CHUNK_SIZE)
                .Where(r => !_ids.Contains(r.Get<PurchaseOrderItemAllocation,Guid>(c=>c.ID)))
                .Select(x=>x.ToObject<PurchaseOrderItem>())
                .ToList();
            foreach (var _poi in _pois)
            {
                var _poia = new PurchaseOrderItemAllocation();
                _poia.Item.ID = _poi.ID;
                _poia.Job.ID = (Guid)(CoreUtils.GetPropertyValue(_poi, "Job.ID") ?? Guid.Empty);
                _poia.Quantity = _poi.Qty;
                _poias.Add(_poia);
                CoreUtils.SetPropertyValue(_poi,"Job.ID",Guid.Empty);
            }
            provider.Save(_poias);
            if(DESTRUCTIVE)
                provider.Save(_pois);
            Logger.Send(LogType.Information, "", $"- Created {_poias.Count} Allocations ({_queue.Count} remaining)");
        }
    }


}