/*

Pentana Audit - Populate missing Checkout.CreatedBy fields from Checkout_Version.sql

Written by Ideagen (email: support-pentana-audit@ideagen.com)

Purpose of script:
	This script fixes missing ID and timestamp data in Checkout records by updating from CheckOut_Version.
	More info in KB:  Pentana Audit:  Attachments behave as thought reserved, but show no name in the Reservation field

	Identify affected records with this query:

	SELECT * FROM CheckOut WHERE CreatedByID IS NULL;

	
Changes:

To run:

- stop the Pentana Audit robot service
- stop the Pentana application pools
- if Retain is used, stop the Retain Server service
- back up the Pentana database and verify
- if Retain is used, back up the Retain database and verify
- run this script - it should run without error
- restart the Pentana app pools
- restart the Pentana Audit robot service
- if Retain is used, restart the Retain service
- verify effect of change
 
*/

UPDATE C SET C.CreatedByID = V.ByID, C.CreatedByGUID = V.ByGUID, C.CreatedTime = V.[Time] 
FROM CheckOut AS C
INNER JOIN CheckOut_Version AS V
ON C.ID = V.ID
WHERE C.CreatedByID 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(), 'Pentana Audit - Populate missing Checkout.CreatedBy fields from Checkout_Version.sql', 'Script', GETDATE(), @FromVersion, NULL, SYSTEM_USER);
	ELSE
		INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Pentana Audit - Populate missing Checkout.CreatedBy fields from Checkout_Version.sql', 'Script', GETDATE(), @ToVersion, NULL, SYSTEM_USER);
	GO