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 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);
        Migrate_JRIPOIs(_provider);
        return true;
    }

    private void Clear_POIAs(IProvider provider)
    {
        Logger.Send(LogType.Information, "", "Clearing Existing POIAs");
        var sql = $"delete from PurchaseOrderItemAllocation";
        var deleted = provider.Update(sql);
        Logger.Send(LogType.Information,"",$"Cleared {deleted} POIAs");        
    }
    
    private void Migrate_JRIPOIs(IProvider provider)
    {
        if (provider.TableExists("JobRequisitionItemPurchaseOrderItem"))
        {
            Logger.Send(LogType.Information,"","Migrating JRIPOIs");
            
            var _pois = provider.Query(
                null, 
                Columns.None<PurchaseOrderItem>().Add(x => x.ID).Add(x => x.Qty)
            ).ToDictionary<PurchaseOrderItem,Guid,double>(x=>x.ID, x=>x.Qty);

            var _sql =
                $"select jripoi.[purchaseorderitem.id], jripoi.[jobrequisitionitem.id], jri.[job.id], cast(poi.[qty] as double) as [qty] " +
                $"from jobrequisitionitempurchaseorderitem jripoi " +
                $"left outer join jobrequisitionitem jri on jripoi.[jobrequisitionitem.id] = jri.[id] " +
                $"left outer join purchaseorderitem poi on jripoi.[purchaseorderitem.id] = poi.[id]";
            var _queue = provider.Query(_sql)
                .Rows.ToQueue();
            
            List<Guid> _ids = new();
            while (_queue.Any())
            {
                List<PurchaseOrderItemAllocation> _poias = new();
                var _jripois = _queue.Dequeue(CHUNK_SIZE).ToList();
                foreach (var _jripoi in _jripois)
                {
                    Guid poi_id = Guid.TryParse(_jripoi.Get<string>("purchaseorderitem.id"), out var pid) ? pid : Guid.Empty;
                    var _poia = new PurchaseOrderItemAllocation();
                    _poia.Item.ID = poi_id;
                    _poia.Job.ID = Guid.TryParse(_jripoi.Get<string>("job.id"), out var jid) ? jid : Guid.Empty;
                    _poia.JobRequisitionItem.ID = Guid.TryParse(_jripoi.Get<string>("jobrequisitionitem.id"), out var rid) ? rid : Guid.Empty;
                    _poia.Quantity = _jripoi.Get<double>("qty");
                    _poias.Add(_poia);
                    _ids.Add(poi_id);
                }

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

        }
        
        if (DESTRUCTIVE)
            provider.DropTable("JobRequisitionItemPurchaseOrderItem");
    }
}