/*
Pentana Audit: Activate Pentana timesheets and fix roll up existing time.  Written by Ideagen (email: support-pentana-audit@ideagen.com)
Pentana Audit:  After ceasing use of RetainTime, Actual Time does not appear in Audit Assignments or Audits when Timesheets are used

Where RetainTime is used with Pentana Audit:

	- users enter timesheet data into RetainTime
	- the Robot collects that data and updates Actual Time in Audit Assignments
	- time is aggregated from Audit Assignments into the parent Audits

If the organisation chooses to cease using RetainTime and switch to using Pentana timesheets then the correct process for disabling RetainTime must be followed (i.e. the 'Disable RetainTime' customisation must be run at cutover), otherwise while timesheet data can be entered it does not correctly roll up into Audit Assignment and Audit summaries, and related calculated fields are not updated.

You can run the 'Disable RetainTime' customisation after users have started entering time data into Pentana Audit, but existing Timsheets will not roll up into Audit Assignments/Audits.

This script can be used to correct the situation.  Typically the organisation will want to cut over after a particular Audit has been entered, so to implement:

- identify the ID of the final Audit to be maintained via RetainTime (using the database query SELECT ID, Name FROM AUDIT;)
- edit the top line of this script so that this ID is specified
- stop the Retain Server service
- stop the Pentana Audit robot service
- stop the Pentana application pools
- back up the Pentana database and verify
- run this script - it should run without error
- restart the Retain service
- restart the Pentana app pools
- restart the Pentana Audit robot service
- verify

Note that this script should not be used without modification if:

- the working week is not 5 days
- the criteria for the last audit to be completed in RetainTime cannot be specified by ID

This script should be run in UAT prior to running Live.

*/


DECLARE @Audit INT = 0;  -- Edit this line with the ID of the final Audit to be completed in RetainTime

-- Backup affected columns
SELECT ID, ActualTime, BudgetToActual, PlannedToActual, ActualToActual INTO Z_timesheetfix_Audit FROM Audit;
SELECT ID, ActualTime, BudgetToActual, PlannedToActual INTO Z_timesheetfix_AuditAssignment FROM AuditAssignment;


-- Update system setting, as done by the Disable RetainTime customisation
UPDATE SystemSetting SET Value = 'False' WHERE Name = 'RetainTime.Enabled';

-- Update the Behavio(u)r
DECLARE @btid INT = (SELECT ID FROM BehaviorType WHERE CLASS = 'AggregateTimeSheetLines');
UPDATE Behavior SET Active = 1 WHERE TypeID = @btid;



-- clear existing data
UPDATE [Audit] SET [ActualTime] = 0 WHERE ID > @Audit;
UPDATE [AuditAssignment] SET [ActualTime] = 0 WHERE ID > @Audit;

-- get system settings
DECLARE @dayLength AS varchar(max);
SELECT @dayLength = [Value] FROM [SystemSetting] WHERE [Name] = 'TimeRecording.DayLength'

DECLARE @units AS varchar(max);
SELECT @units = [Value] FROM [SystemSetting] WHERE [Name] = 'TimeRecording.Units'

-- determine scale factor
DECLARE @scaleFactor AS decimal = 1 -- assume hours

IF @units = 'Days' SELECT @scaleFactor = @dayLength
IF @units = 'Weeks' SELECT @scaleFactor = @dayLength * 5 -- adjust this if you know they have an unorthodox working week!

-- update audit assignments
UPDATE [AA] SET [ActualTime] = (SELECT SUM([Total]) / @scaleFactor FROM [TimeSheetLine] WHERE [AuditAssignmentID] = [AA].[ID]) FROM [AuditAssignment] [AA] WHERE AuditID > @Audit;

-- recalculate audit assignment variances
UPDATE [AuditAssignment] SET [BudgetToActual] = [BudgetTime] - [ActualTime] WHERE AuditID > @Audit;
UPDATE [AuditAssignment] SET [PlannedToActual] = [PlannedTime] - [ActualTime] WHERE AuditID > @Audit;

-- update audits
UPDATE [A] SET [ActualTime] = (SELECT SUM([ActualTime]) FROM [AuditAssignment] WHERE [AuditID] = [A].[ID]) FROM [Audit] [A] WHERE ID > @Audit;

-- recalculate audit variances
UPDATE [Audit] SET [BudgetToActual] = [BudgetTime] - [ActualTime] WHERE ID > @Audit;
UPDATE [Audit] SET [PlannedToActual] = [PlannedTime] - [ActualTime] WHERE ID > @Audit;
UPDATE [Audit] SET [ActualToActual] = [ActualEffort] - [ActualTime] WHERE ID > @Audit;

-- Deal with the NULLs
UPDATE Audit SET ActualTime = 0 WHERE ActualTime IS NULL;
UPDATE Audit SET BudgetToActual = 0 WHERE BudgetToActual IS NULL;
UPDATE Audit SET PlannedToActual = 0 WHERE PlannedToActual IS NULL;
UPDATE Audit SET ActualToActual = 0 WHERE ActualToActual IS NULL;

UPDATE AuditAssignment SET ActualTime = 0 WHERE ActualTime IS NULL;
UPDATE AuditAssignment SET BudgetToActual = 0 WHERE BudgetToActual IS NULL;
UPDATE AuditAssignment SET PlannedToActual = 0 WHERE PlannedToActual IS NULL;


-- Register this in the Installlog

-- Find whether the last entry was an install or upgrade and manage the FromVersion and ToVersion appropiately
DECLARE @FromVersion NVARCHAR(25) = (SELECT TOP 1 FromVersion FROM InstallLog ORDER BY ID DESC);
DECLARE @ToVersion NVARCHAR(25) = (SELECT TOP 1 ToVersion FROM InstallLog ORDER BY ID DESC);

IF @ToVersion IS NULL
	INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Activate Pentana timesheets and fix roll up existing time', 'Script', GETDATE(), @FromVersion, NULL, SYSTEM_USER);
ELSE
	INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Activate Pentana timesheets and fix roll up existing time', 'Script', GETDATE(), @ToVersion, NULL, SYSTEM_USER);


/*
-- select new data (to check it looks right)
SELECT [ID], [AuditAssignmentID], [Total] FROM [TimeSheetLine] WHERE [AuditAssignmentID] IS NOT NULL
SELECT [ID], [AuditID], [ActualTime], [BudgetTime], [PlannedTime], [BudgetToActual], [PlannedToActual] FROM [AuditAssignment] WHERE [ActualTime] <> 0
SELECT [ID], [ActualTime], [BudgetTime], [PlannedTime], [ActualEffort], [BudgetToActual], [PlannedToActual], [ActualToActual] FROM [Audit] WHERE [ActualTime] <> 0

*/