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

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

namespace PRS.Shared;

public class Update_7_00 : DatabaseUpdateScript
{

    public override VersionNumber Version => new (7, 00);
        
    public override bool Update()
    {
        Convert<Assignment>(
            new Filter<Assignment>(x=>x.Booked.Start).IsEqualTo(DateTime.MinValue)
                .And(x=>x.Booked.Finish).IsEqualTo(DateTime.MinValue)
                .And(x=>x.Actual.Finish).IsEqualTo(DateTime.MinValue)
                .And(x=>x.Actual.Finish).IsEqualTo(DateTime.MinValue),
            new Map<Assignment>("Start",x => x.Booked.Start),
            new Map<Assignment>("Finish",x => x.Booked.Finish),
            new Map<Assignment>("Start",x => x.Actual.Start),
            new Map<Assignment>("Finish",x => x.Actual.Finish)
        );
        
        // ConvertTimes<TimeSheet>(
        //     x => x.Actual.Duration,
        //     new TimeExpressions<TimeSheet>(x => x.Actual.Start, x => x.Actual.Finish),
        //     new TimeExpressions<TimeSheet>(x => x.Approved.Start, x => x.Approved.Finish)
        // );
        
        Convert_StandardLeaves_and_LeaveRequests();

        return true;
    }
    
    void Convert_StandardLeaves_and_LeaveRequests()
    {
        // Delete from TimeSheet where processed={} and leaverequestlink.id != empty
        var unprocessedtimesheets = DbFactory.NewProvider(Logger.Main).Query<TimeSheet>(
            new Filter<TimeSheet>(x => x.Processed).IsEqualTo(DateTime.MinValue)
                .And(x => x.LeaveRequestLink.ID).IsNotEqualTo(Guid.Empty),
            Columns.None<TimeSheet>().Add(x => x.ID)
        ).Rows.Select(x=>x.ToObject<TimeSheet>()).ToArray();
        
        int iTimes = 0;
        while (iTimes < unprocessedtimesheets.Length)
        { 
            var deletions = unprocessedtimesheets.Skip(iTimes).Take(100).ToArray();
            DbFactory.NewProvider(Logger.Main).Purge<TimeSheet>(deletions);
            iTimes += deletions.Length;
        }
        //DbFactory.NewProvider(Logger.Main).Delete<TimeSheet>(unprocessedtimesheets,"");

        // Find all Leave Requests where public holiday != empty
        var standardleaverequests = DbFactory.NewProvider(Logger.Main).Query<LeaveRequest>(
            new Filter<LeaveRequest>(x => x.PublicHoliday.ID).IsNotEqualTo(Guid.Empty),
            Columns.None<LeaveRequest>().Add(x => x.ID)
                .Add(x=>x.PublicHoliday.ID)
        ).Rows.Select(x => x.ToObject<LeaveRequest>()).ToArray();
        foreach (var standardleaverequest in standardleaverequests)
        {
            // Find all timesheets for this leave request
            var standardleavetimesheets = DbFactory.NewProvider(Logger.Main).Query<TimeSheet>(
                new Filter<TimeSheet>(x=>x.LeaveRequestLink.ID).IsEqualTo(standardleaverequest.ID),
                Columns.None<TimeSheet>().Add(x => x.ID)
                    .Add(x=>x.LeaveRequestLink.ID)
            ).Rows.Select(x=>x.ToObject<TimeSheet>()).ToArray();
            
            // Redirect timesheet from leaverequest to standardleave
            foreach (var standardleavetimesheet in standardleavetimesheets)
            {
                standardleavetimesheet.StandardLeaveLink.ID = standardleaverequest.PublicHoliday.ID;
                standardleavetimesheet.LeaveRequestLink.ID = Guid.Empty;
            }
            if (standardleavetimesheets.Any())
                DbFactory.NewProvider(Logger.Main).Save(standardleavetimesheets);
        }
        // delete these leave requests
        int iRequests = 0;
        while (iRequests < standardleaverequests.Length)
        { 
            var deletions = standardleaverequests.Skip(iRequests).Take(100).ToArray();
            DbFactory.NewProvider(Logger.Main).Purge<LeaveRequest>(deletions);
            iRequests += deletions.Length;
        }
        
        // Delete from Assignment where leaverequestlink id != empty
        var leaveassignments = DbFactory.NewProvider(Logger.Main).Query<Assignment>(
            new Filter<Assignment>(x => x.LeaveRequestLink.ID).IsNotEqualTo(Guid.Empty),
            Columns.None<Assignment>().Add(x => x.ID)
        ).Rows.Select(x=>x.ToObject<Assignment>()).ToArray();
        
        int iAssignments = 0;
        while (iAssignments < leaveassignments.Length)
        { 
            var deletions = leaveassignments.Skip(iAssignments).Take(100).ToArray();
            DbFactory.NewProvider(Logger.Main).Purge<Assignment>(deletions);
            iAssignments += deletions.Length;
        }

    }
    
}