/*******************************/
/******** IMPORTANT!!! ********/
/* Update the @active variable with the appropriate number for the
specific plant you are working on BEFORE you run this script.  This
must be formatted in a comma-separated list with each value for the 
formats that should be made available to the plant.  Additionally,
the number 1000 is for all generic and base formats that should be
available to all plants.  You can find the value(s) to be entered by
looking at the ApplicablePlant value in update statements below*/
/*******************************/
DECLARE @active varchar(50)
SET @active = '1000,4' 
UPDATE Folders SET Config = REPLACE(Config, '</Config>','<Formats Active="' + @active + '" /></Config>')
WHERE FolderID = 1

/***********************************************************************************/

/****** Add the ApplicablePlant field to the Formats table ******/
IF COL_LENGTH('dob.Formats', 'ApplicablePlant') IS NULL
BEGIN
    ALTER TABLE Formats ADD ApplicablePlant int NULL;
END
GO

/****** Update the Formats table with the appropriate ApplicablePlant value ******/

--This update statement corrects a typo in the name of one of the format records
UPDATE Formats SET [Description] = REPLACE([Description], 'Robimson', 'Robinson') WHERE [Description] LIKE '%Robimson%';

UPDATE Formats SET ApplicablePlant = 1 WHERE [Description] LIKE '%DC Cook%' OR [Name] LIKE 'AEP%';
UPDATE Formats SET ApplicablePlant = 2 WHERE [Description] LIKE '%Calvert%' OR [Name] LIKE 'BGE%';
UPDATE Formats SET ApplicablePlant = 3 WHERE [Description] LIKE '%BNPP%' OR [Name] LIKE 'BNPP%';
UPDATE Formats SET ApplicablePlant = 4 WHERE [Description] LIKE '%Beaver Valley%' OR [Name] LIKE 'BVPS%';
UPDATE Formats SET ApplicablePlant = 5 WHERE [Description] LIKE '%Byr-Bwd%' OR ([Name] LIKE 'EXC%' OR [Name] LIKE 'EXEB%' OR [Name] LIKE 'CWE%');
UPDATE Formats SET ApplicablePlant = 6 WHERE [Description] LIKE '%Callaway%' OR [Name] LIKE 'CAL%';
UPDATE Formats SET ApplicablePlant = 7 WHERE [Description] LIKE '%Catawba%' OR [Name] LIKE 'CAT%';
UPDATE Formats SET ApplicablePlant = 8 WHERE [Description] LIKE '%Comanche%' OR ([Name] LIKE 'ComPeak%' OR [Name] LIKE 'CPD%' OR [Name] LIKE 'CPF%' OR [Name] LIKE 'CPS%' OR [Name] LIKE 'TUEC%');
UPDATE Formats SET ApplicablePlant = 9 WHERE [Description] LIKE '%Robinson%' OR [Name] LIKE 'RNP%';
UPDATE Formats SET ApplicablePlant = 10 WHERE [Description] LIKE '%Harris%' OR ([Name] LIKE 'CPLS%' OR [Name] LIKE 'SHE%');
UPDATE Formats SET ApplicablePlant = 11 WHERE [Description] LIKE '%IP%' OR [Name] LIKE 'IP%';
UPDATE Formats SET ApplicablePlant = 12 WHERE [Description] LIKE '%Farley%' OR [Name] LIKE 'FNP%';
UPDATE Formats SET ApplicablePlant = 13 WHERE [Description] LIKE '%Turkey%' OR [Name] LIKE 'FPL%' OR [Name] LIKE 'TP%';
UPDATE Formats SET ApplicablePlant = 14 WHERE [Description] LIKE '%Ginna%' OR [Name] LIKE 'Ginna%' OR [Name] LIKE 'RGE%';
UPDATE Formats SET ApplicablePlant = 15 WHERE [Description] LIKE '%Vogtle%' OR [Name] LIKE 'GPC%' OR [Name] LIKE 'VEGP%';
UPDATE Formats SET ApplicablePlant = 16 WHERE [Description] LIKE '%South Texas%' OR [Name] LIKE 'HLP%' OR [Name] LIKE 'OHLP%';
UPDATE Formats SET ApplicablePlant = 17 WHERE [Description] LIKE '%McGuire%' OR [Name] LIKE 'MCG%';
UPDATE Formats SET ApplicablePlant = 18 WHERE [Description] LIKE '%PI%' OR [Name] LIKE 'NSP%';
UPDATE Formats SET ApplicablePlant = 19 WHERE [Description] LIKE '%Southern Nuclear%' OR [Name] LIKE 'STHN%';
UPDATE Formats SET ApplicablePlant = 20 WHERE [Description] LIKE '%VC Summer%' OR [Name] LIKE 'SUM%' OR [Name] LIKE 'VCB%' OR [Name] LIKE 'VCS%';
UPDATE Formats SET ApplicablePlant = 21 WHERE [Description] LIKE '%TVA%' OR [Name] LIKE 'TVA%';
UPDATE Formats SET ApplicablePlant = 22 WHERE [Description] LIKE '%Wolf%' OR [Name] LIKE 'WCN%';
UPDATE Formats SET ApplicablePlant = 23 WHERE [Description] LIKE '%Westinghouse%' OR [Name] LIKE 'WST%';
UPDATE Formats SET ApplicablePlant = 23 WHERE [Description] LIKE '%Vogtle%' OR [Name] LIKE 'VEGP%';
UPDATE Formats SET ApplicablePlant = 1000 WHERE [Description] LIKE '%Proms%' OR [Description] LIKE '%Generic%' OR [Name] LIKE 'EXP%' OR [Name] LIKE 'Proms%' OR [Name] LIKE 'base%';


/****** Object:  StoredProcedure [dbo].[getFormats]    Script Date: 9/28/2023 8:42:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2018 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
ALTER PROCEDURE [dbo].[getFormats]

WITH EXECUTE AS OWNER
AS
BEGIN
		with formatz([FormatID], [ParentID],[Name],[Description],[Data],[Config],[GenMac],[DTS],[UserID],[LastChanged],[ApplicablePlant]) as 
		(select [FormatID], [ParentID],[Name],[Description],[Data],[Config],[GenMac],[DTS],[UserID],[LastChanged],[ApplicablePlant]
		FROM [dbo].[Formats] fs where DATALENGTH(fs.Data) > 5 
		union all  -- Child formats
		select fs.[FormatID], fs.[ParentID], fs.[Name], fs.[Description], fz.[Data], fs.[Config], fz.[GenMac], fs.[DTS], fs.[UserID], fs.[LastChanged], fs.[ApplicablePlant]
		from formats fs 
		join formatz fz on fz.FormatID = fs.ParentID
		where DATALENGTH(fs.Data) = 5)   -- the DATALENGTH(fs.Data) = 5 is how to check for empty xml (Data's type is xml)
		select * from (
	select *,	
	(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formatz].[FormatID]) [ContentCount],
	(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formatz].[FormatID]) [DocVersionCount],
	(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formatz].[FormatID]) [FolderCount],
	(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formatz].[FormatID]) [ChildCount] from Formatz) t1
	where Description not like '%(Unused)%' or (ContentCount + DocVersionCount + FolderCount + ChildCount > 0)
END
RETURN

/****** Object:  StoredProcedure [dbo].[getAllFormats]    Script Date: 9/28/2023 8:32:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2018 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
ALTER PROCEDURE [dbo].[getAllFormats]

WITH EXECUTE AS OWNER
AS
	SELECT * From (SELECT
		[FormatID],
		[ParentID],
		[Name],
		[Description],
		[Data],
		[Config],
		[GenMac],
		[DTS],
		[UserID],
		[LastChanged],
		[ApplicablePlant],
		(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
		(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
	FROM [Formats] ) T1
	RETURN


/****** Object:  StoredProcedure [dbo].[getFormatByName]    Script Date: 9/29/2023 12:16:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getFormatByName]
(
	@Name varchar(255)
)
WITH EXECUTE AS OWNER
AS
	DECLARE @FormatID INT
	Set @FormatID = (select FormatID from Formats where Name = @Name)
	BEGIN
	with formatz([FormatID], [ParentID],[Name],[Description],[Data],[Config],[GenMac],[DTS],[UserID],[LastChanged],[ApplicablePlant]) as 
	(select [FormatID], [ParentID],[Name],[Description],[Data],[Config],[GenMac],[DTS],[UserID],[LastChanged],[ApplicablePlant]
	FROM [dbo].[Formats] fs where DATALENGTH(fs.Data) > 5 
	union all  -- Child formats
	select fs.[FormatID], fs.[ParentID], fs.[Name], fs.[Description], fz.[Data], fs.[Config], fz.[GenMac], fs.[DTS], fs.[UserID], fs.[LastChanged], fs.[ApplicablePlant]
	from formats fs 
	join formatz fz on fz.FormatID = fs.ParentID
	where DATALENGTH(fs.Data) = 5)   -- the DATALENGTH(fs.Data) = 5 is how to check for empty xml (Data's type is xml)
	select *,	
	(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formatz].[FormatID]) [ContentCount],
	(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formatz].[FormatID]) [DocVersionCount],
	(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formatz].[FormatID]) [FolderCount],
	(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formatz].[FormatID]) [ChildCount] from Formatz
	WHERE [FormatID]=@FormatID
	END

	SELECT
		[Contents].[ContentID],
		[Contents].[Number],
		[Contents].[Text],
		[Contents].[Type],
		[Contents].[FormatID],
		[Contents].[Config],
		[Contents].[DTS],
		[Contents].[UserID],
		[Contents].[LastChanged]
	FROM [Contents]
	WHERE
		[Contents].[FormatID]=@FormatID


	SELECT
		[DocVersions].[VersionID],
		[DocVersions].[FolderID],
		[DocVersions].[VersionType],
		[DocVersions].[Name],
		[DocVersions].[Title],
		[DocVersions].[ItemID],
		[DocVersions].[FormatID],
		[DocVersions].[Config],
		[DocVersions].[DTS],
		[DocVersions].[UserID],
		[DocVersions].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID]
	FROM [DocVersions]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[DocVersions].[FolderID]
	WHERE
		[DocVersions].[FormatID]=@FormatID


	SELECT
		[Folders].[FolderID],
		[Folders].[ParentID],
		[Folders].[DBID],
		[Folders].[Name],
		[Folders].[Title],
		[Folders].[ShortName],
		[Folders].[FormatID],
		[Folders].[ManualOrder],
		[Folders].[Config],
		[Folders].[DTS],
		[Folders].[UsrID],
		[Folders].[LastChanged],
		[Connections].[Name] [Connection_Name],
		[Connections].[Title] [Connection_Title],
		[Connections].[ConnectionString] [Connection_ConnectionString],
		[Connections].[ServerType] [Connection_ServerType],
		[Connections].[Config] [Connection_Config],
		[Connections].[DTS] [Connection_DTS],
		[Connections].[UsrID] [Connection_UsrID]
	FROM [Folders]
		JOIN [Connections]  ON
			[Connections].[DBID]=[Folders].[DBID]
	WHERE
		[Folders].[FormatID]=@FormatID

	RETURN