/*

Pentana Audit - Populate missing DocumentInfo.FileName from Attachment or Report title.sql

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

Purpose of script:
	
	This script fixes missing FileName values in the DocumentInfo table, creating from Title and FileType.
	More info in KB:  Pentana Audit:  Error opening attachment:  "Document Is Unavailable" and Filename is blank

	Identify affected records with this query:

	SELECT * FROM DocumentInfo WHERE FileName = '';


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
 
*/

--	Records might be Attachments or Reports - update each in turn
	-- Attachments
	UPDATE D SET FileName = A.Title + D.FileType
	From DocumentInfo AS D
	INNER JOIN Attachment AS A
	ON D.AttachmentID = A.ID
	WHERE D.FileName = '';

	-- Audit Reports
	UPDATE D SET FileName = A.Title + D.FileType
	From DocumentInfo AS D
	INNER JOIN AuditReport AS A
	ON D.AuditReportID = A.ID
	WHERE D.FileName = '';
	
-- 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 DocumentInfo.FileName from Attachment or Report title.sql', 'Script', GETDATE(), @FromVersion, NULL, SYSTEM_USER);
	ELSE
		INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Pentana Audit - Populate missing DocumentInfo.FileName from Attachment or Report title.sql', 'Script', GETDATE(), @ToVersion, NULL, SYSTEM_USER);
	GO