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);
        Update_POIs(_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");
                    _poia.Nominated = !_ids.Contains(poi_id);
                    _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");
    }
    
    private void Update_POIs(IProvider provider)
    {
        Logger.Send(LogType.Information,"","Migrating PurchaseOrderItems");
        
        var _ids = provider.Query(
            null,
            Columns.None<PurchaseOrderItemAllocation>()
                .Add(x=>x.Item.ID)
            ).ExtractValues<PurchaseOrderItemAllocation,Guid>(x => x.Item.ID,true);
        
        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)
                .Select(x=>x.ToObject<PurchaseOrderItem>())
                .ToList();
            foreach (var _poi in _pois)
            {
                if (!_ids.Contains(_poi.ID))
                {
                    var _poia = new PurchaseOrderItemAllocation();
                    _poia.Item.ID = _poi.ID;
                    _poia.Job.ID = (Guid)(CoreUtils.GetPropertyValue(_poi, "Job.ID") ?? Guid.Empty);
                    _poia.Quantity = _poi.Qty;
                    _poia.Nominated = true;
                    _poias.Add(_poia);
                }
                
                CoreUtils.SetPropertyValue(_poi,"Job.ID",Guid.Empty);
            }
            provider.Save(_poias);
            if(DESTRUCTIVE)
                provider.Save(_pois.Where(x=>x.IsChanged()));
            Logger.Send(LogType.Information, "", $"- Created {_poias.Count} Allocations ({_queue.Count} remaining)");
        }
    }


}