IF Not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstHeader]') AND type in (N'U'))
Begin

	/****** Object:  Table [dbo].[RofstDefaultValue]    Script Date: 4/26/2022 6:59:49 AM ******/
	SET ANSI_NULLS ON
	
	SET QUOTED_IDENTIFIER ON
	
	CREATE TABLE [dbo].[RofstDefaultValue](
		[RofstID] [int] NOT NULL,
		[roid] [varchar](50) NOT NULL,
		[value] [varchar](max) NOT NULL,
		[AccPageID] [varchar](max) NULL,
		CONSTRAINT [PK_RofstDefaultValue] PRIMARY KEY CLUSTERED 
	(
		[RofstID] ASC,
		[roid] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
	

	/****** Object:  Table [dbo].[RofstChild]    Script Date: 4/26/2022 6:59:50 AM ******/
	SET ANSI_NULLS ON
	
	SET QUOTED_IDENTIFIER ON
	
	CREATE TABLE [dbo].[RofstChild](
		[RofstChildID] [int] IDENTITY(1,1) NOT NULL,
		[RofstID] [int] NOT NULL,
		[dbiID] [int] NOT NULL,
		[ID] [int] NOT NULL,
		[ParentID] [int] NOT NULL,
		[type] [int] NOT NULL,
		[title] [varchar](max) NOT NULL,
		[roid] [varchar](50) NOT NULL,
		[appid] [varchar](max) NULL,
		[value] [varchar](max) NULL,
		CONSTRAINT [PK_RofstChild] PRIMARY KEY CLUSTERED 
	(
		[RofstChildID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
	
	/****** Object:  Table [dbo].[RofstDatabase]    Script Date: 4/26/2022 6:59:50 AM ******/
	SET ANSI_NULLS ON
	
	SET QUOTED_IDENTIFIER ON
	
	CREATE TABLE [dbo].[RofstDatabase](
		[RofstID] [int] NOT NULL,
		[dbiID] [int] NOT NULL,
		[dbiType] [int] NOT NULL,
		[dbiAW] [int] NOT NULL,
		[dbiTitle] [varchar](max) NOT NULL,
		[dbiAP] [varchar](max) NOT NULL,
		[ID] [int] NOT NULL,
		[ParentID] [int] NOT NULL,
		CONSTRAINT [PK_RofstDatabase_1] PRIMARY KEY CLUSTERED 
	(
		[RofstID] ASC,
		[dbiID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
	

	/****** Object:  Table [dbo].[RofstHeader]    Script Date: 4/26/2022 6:59:50 AM ******/
	SET ANSI_NULLS ON
	
	SET QUOTED_IDENTIFIER ON
	
	CREATE TABLE [dbo].[RofstHeader](
		[RofstID] [int] NOT NULL,
		[hSize] [int] NOT NULL,
		[hMonth] [int] NOT NULL,
		[hDay] [int] NOT NULL,
		[hcYear] [int] NOT NULL,
		[hcMonth] [int] NOT NULL,
		[hcDay] [int] NOT NULL,
		[hcHour] [int] NOT NULL,
		[hcMin] [int] NOT NULL,
		[hcSec] [int] NOT NULL,
		[hcHund] [int] NOT NULL,
		[CreateUserID] [varchar](50) NOT NULL,
		[CreateDate] [datetime] NOT NULL,
		CONSTRAINT [PK_RofstHeader] PRIMARY KEY CLUSTERED 
	(
		[RofstID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY] 
	

	/****** Object:  Index [IX_RofstID_DbiID_ParentID]    Script Date: 4/26/2022 6:59:50 AM ******/
	CREATE NONCLUSTERED INDEX [IX_RofstID_DbiID_ParentID] ON [dbo].[RofstChild]
	(
		[RofstID] ASC,
		[dbiID] ASC,
		[ParentID] ASC
	)
	INCLUDE([ID],[type],[title],[roid],[appid],[value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	
	SET ANSI_PADDING ON
	
	/****** Object:  Index [IX_RofstID_Roid]    Script Date: 4/26/2022 6:59:50 AM ******/
	CREATE NONCLUSTERED INDEX [IX_RofstID_Roid] ON [dbo].[RofstChild]
	(
		[RofstID] ASC,
		[roid] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	
	ALTER TABLE [dbo].[RofstDatabase] ADD  CONSTRAINT [DF_RofstDatabase_ParentID]  DEFAULT ((0)) FOR [ParentID]
	
	ALTER TABLE [dbo].[RofstHeader] ADD  CONSTRAINT [DF_RofstHeader_CreateUserID]  DEFAULT ('SYSTEM') FOR [CreateUserID]
	
	ALTER TABLE [dbo].[RofstHeader] ADD  CONSTRAINT [DF_RofstHeader_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
	
	ALTER TABLE [dbo].[RofstChild]  WITH CHECK ADD  CONSTRAINT [FK_RofstChild_RofstDatabase] FOREIGN KEY([RofstID], [dbiID])
	REFERENCES [dbo].[RofstDatabase] ([RofstID], [dbiID])
	ON DELETE CASCADE
	
	ALTER TABLE [dbo].[RofstChild] CHECK CONSTRAINT [FK_RofstChild_RofstDatabase]
	
	ALTER TABLE [dbo].[RofstDatabase]  WITH CHECK ADD  CONSTRAINT [FK_RofstDatabase_RofstHeader] FOREIGN KEY([RofstID])
	REFERENCES [dbo].[RofstHeader] ([RofstID])
	ON DELETE CASCADE
	
	ALTER TABLE [dbo].[RofstDatabase] CHECK CONSTRAINT [FK_RofstDatabase_RofstHeader]
	
	ALTER TABLE [dbo].[RofstDefaultValue]  WITH CHECK ADD  CONSTRAINT [FK_RofstDefaultValue_RofstHeader] FOREIGN KEY([RofstID])
	REFERENCES [dbo].[RofstHeader] ([RofstID])
	ON DELETE CASCADE
	
	ALTER TABLE [dbo].[RofstDefaultValue] CHECK CONSTRAINT [FK_RofstDefaultValue_RofstHeader]
	
	ALTER TABLE [dbo].[RofstHeader]  WITH CHECK ADD  CONSTRAINT [FK_RofstHeader_ROFsts] FOREIGN KEY([RofstID])
	REFERENCES [dbo].[ROFsts] ([ROFstID])
	ON DELETE CASCADE
	
	ALTER TABLE [dbo].[RofstHeader] CHECK CONSTRAINT [FK_RofstHeader_ROFsts]
	
End
Go





If Exists(Select * From sys.objects Where type = 'FN' And object_id = OBJECT_ID('[dbo].[vefn_Clean]'))
	Drop Function [dbo].[vefn_Clean];

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- ==========================================================================================
-- Author:		Jake Ropar
-- Create Date: 4/21/2022
-- Description:	Cleans (Trim) the string value passed
--				Optional: Truncate is string is passed a specified max length
--				Optional: Convert any null values to an empty string or leave as null
-- ==========================================================================================

Create Function [dbo].[vefn_Clean] ( @Value VarChar(Max), @ConvertNulls bit = 0, @MaxLength Int = null )
Returns VarChar(Max)
As
Begin

	Declare @RetVal VarChar(Max);

	If (Len(IsNull(@Value,'')) > 0)
	Begin

		Set @RetVal = LTrim(RTrim(@Value));
		If (Not @MaxLength is null And Len(@RetVal) > @MaxLength) Set @RetVal = Left(@RetVal, @MaxLength);

	End

	If (@ConvertNulls = 1)
		Set @RetVal = IsNull(@RetVal, '');

	Return @RetVal;

End
GO



If Exists(Select * From sys.objects Where type = 'TF' And object_id = OBJECT_ID('[dbo].[vefn_ParseStringListToTable]'))
	Drop Function [dbo].[vefn_ParseStringListToTable];


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- ==========================================================================================
-- Author:		Jake Ropar
-- Create Date: 3/26/2018
-- Description:	Splits a delimited list of strings into a table of string values
-- ==========================================================================================

Create Function [dbo].[vefn_ParseStringListToTable] ( @List VarChar(Max), @Delimeter VarChar(1) = null )
Returns @RetVal Table (ListPosition Int Identity(1,1) Not Null, ListValue VarChar(Max) Not Null)
With Execute As Owner
As
Begin

	Declare @Position Int;
	Declare @TextPosition Int;
	Declare @Length smallint;
	Declare @String VarChar(Max);
	Declare @TempString VarChar(Max);
	Declare @LeftOver VarChar(Max);

    Set @TextPosition = 1;
	Set @LeftOver = '';
	Set @Delimeter = IsNull(@Delimeter, ',');

	If (DataLength(@List) = 1)
		Insert @RetVal (ListValue) Values (Cast(@List as VarChar(Max)));
	Else
		Begin
			While (@TextPosition <= DataLength(@List) / 2)
			Begin

				 Set @Length = (4000 - DataLength(@LeftOver) / 2);
				 Set @TempString = LTrim(@LeftOver + Substring(@List, @TextPosition, @Length));
				 Set @TextPosition = @TextPosition + @Length;
				 Set @Position = CharIndex(@Delimeter, @TempString);

				 While (@Position > 0)
				 Begin

					Set @String = Substring(@TempString, 1, @Position - 1);

					Insert @RetVal (ListValue) Values (Cast(@String as VarChar(Max)));

					Set @TempString = LTrim(Substring(@TempString, @Position + 1, Len(@TempString)));
					Set @Position = CharIndex(@Delimeter, @TempString);

				 End

				 Set @LeftOver = @TempString;
			End
		End

	If (LTrim(RTrim(@LeftOver)) <> '')
         Insert @RetVal (ListValue) Values (Cast(@LeftOver as VarChar(Max)));

	Return;
End

Go





If Exists(Select * From sys.objects Where type = 'FN' And object_id = OBJECT_ID('[dbo].[vefn_NumOfOccurrences]'))
	Drop Function [dbo].[vefn_NumOfOccurrences];



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ==========================================================================================
-- Author:		Jake Ropar
-- Create Date: 3/1/2022
-- Description:	Returns the number of occurences of a specific value in a specified string
-- ==========================================================================================

CREATE FUNCTION [dbo].[vefn_NumOfOccurrences](@Text VarChar(Max), @Value VarChar(Max)) Returns Int
WITH EXECUTE AS OWNER
AS
BEGIN
	
	Declare @ValueCount Int;

	If (Len(@Value) = 0)
		Set @ValueCount = 0;
	Else
		Set @ValueCount = (Len(@Text) - Len(Replace(@Text, @Value, ''))) / Len(@Value);

	Return @ValueCount;

END
Go





If Exists(Select * From sys.objects Where type = 'FN' And object_id = OBJECT_ID('[dbo].[vefn_RofstDataReplaceApplTagsWithDefaults]'))
	Drop Function [dbo].[vefn_RofstDataReplaceApplTagsWithDefaults];



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ==========================================================================================
-- Author:		Jake Ropar
-- Create Date: 3/25/2022
-- Description:	Replaces any "<APPL  /APL>" tags with the default value
-- ==========================================================================================

CREATE FUNCTION [dbo].[vefn_RofstDataReplaceApplTagsWithDefaults](@Values VarChar(Max)) Returns VarChar(Max)
WITH EXECUTE AS OWNER
AS
BEGIN
	
	Declare @RetVal VarChar(Max) = '';
	Declare @DefaultValue VarChar(Max) = '';

	Declare @StartTagIndex Int;
	Declare @EndTagIndex Int;
	Declare @StartTagValue VarChar(Max);
	
	Set @RetVal = @Values;
	Set @StartTagIndex = PatIndex('%<APL%', @RetVal);
	Set @EndTagIndex = PatIndex('%/APL>%', @RetVal);

	While (@StartTagIndex > 0)
		Begin
			
			Set @StartTagValue = SubString(@RetVal, @StartTagIndex, (@EndTagIndex - @StartTagIndex) + 5);
			Set @DefaultValue = Replace(@StartTagValue, '<APL DefaultVal=', '');

			Set @EndTagIndex = PatIndex('%,UnitIdx=%', @DefaultValue);
			If (@EndTagIndex <= 0) Set @EndTagIndex = PatIndex('%/APL>%', @DefaultValue);

			Set @DefaultValue = RTrim(SubString(@DefaultValue, 0, @EndTagIndex));
			Set @RetVal = Replace(@RetVal, @StartTagValue, @DefaultValue);

			Set @StartTagIndex = PatIndex('%<APL%', @RetVal);
			Set @EndTagIndex = PatIndex('%/APL>%', @RetVal);

		End -- While (@StartTagIndex > 0)

	Return @RetVal;

END
Go


If Exists(Select * From sys.objects Where type = 'FN' And object_id = OBJECT_ID('[dbo].[vefn_RofstDataReplaceVars]'))
	Drop Function [dbo].[vefn_RofstDataReplaceVars];



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ==========================================================================================
-- Author:		Jake Ropar
-- Create Date: 3/25/2022
-- Description:	Replaces Any Variables and returns the rest of the value string
-- ==========================================================================================

CREATE FUNCTION [dbo].[vefn_RofstDataReplaceVars](@Values VarChar(Max)) Returns VarChar(Max)
WITH EXECUTE AS OWNER
AS
BEGIN
	
	Declare @RetVal VarChar(Max) = '';
	Declare @EqualsIndex Int;
	Declare @EndIndex Int;
	Declare @NameValPairStartIndex Int;
	Declare @NameValPairEndIndex Int;
	
	Declare @VarPair VarChar(Max);
	Declare @VarName VarChar(Max);
	Declare @VarValue VarChar(Max);

	-- Replace Any "<APPL  /APL>" Tags with the Default Value first
	Select @RetVal = dbo.vefn_RofstDataReplaceApplTagsWithDefaults(@Values);

	If (PatIndex('%{{A%', @RetVal) > 0)
		Set @EndIndex = PatIndex('%{{A%', @RetVal);
	Else
		Set @EndIndex = Len(@RetVal) -1;
		
	Set @NameValPairStartIndex = PatIndex('%{%', @RetVal);
	Set @NameValPairEndIndex = PatIndex('%}%', @RetVal);
	
	While(@NameValPairStartIndex > 0 And @NameValPairStartIndex < @EndIndex)
		Begin
			
			-- Get Name Value Pair [ex. {EGS=1214}]
			Set @VarPair = SubString(@RetVal, @NameValPairStartIndex, (@NameValPairEndIndex - @NameValPairStartIndex) + 1);

			-- Remove Name Value Pair From Return Val
			Set @RetVal = Replace(@RetVal, @VarPair, '');

			-- Get Variable Name and Value
			Set @EqualsIndex = PatIndex('%=%', @VarPair);

			If (@EqualsIndex > 0)
			Begin
				Set @VarName = SubString(@VarPair, 2, @EqualsIndex - 2);
				Set @VarValue = SubString(@VarPair, @EqualsIndex + 1, Len(@VarPair) - @EqualsIndex - 1);
				Set @VarName = Concat('{', @VarName, '}');

				-- Replace All Occurences 
				Set @RetVal = Replace(@RetVal, @VarName, @VarValue);
			End

			-- Get Updated Index Values
			If (PatIndex('%{{A%', @RetVal) > 0)
				Set @EndIndex = PatIndex('%{{A%', @RetVal);
			Else
				Set @EndIndex = Len(@RetVal) -1;
		
			Set @NameValPairStartIndex = PatIndex('%{%', @RetVal);
			Set @NameValPairEndIndex = PatIndex('%}%', @RetVal);
					
		End -- End While(@ReplaceVarEndTagIndex > 0)

	Return @RetVal;

END
Go
	



If Exists(Select * From sys.views Where Name = N'vwRofstData_RofstDefaultValues')
	Drop View [dbo].[vwRofstData_RofstDefaultValues];
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2024
 Description:	(RofstData) Master View for Rofst Roid Default Values
==========================================================================================================
*/
Create View [dbo].[vwRofstData_RofstDefaultValues]
As

	Select rdv.RofstID as 'RofstID',
		rdv.roid as 'roid',
		rdv.[value] as 'value',
		rdv.AccPageID as 'AccPageID'
	From RofstDefaultValue rdv With (NoLock)
	


GO





If Exists(Select * From sys.views Where Name = N'vwRofstData_RofstHeaders')
	Drop View [dbo].[vwRofstData_RofstHeaders];
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2024
 Description:	(RofstData) Master View for RofstHeaders
==========================================================================================================
*/
Create View [dbo].[vwRofstData_RofstHeaders]
As

	Select rh.RofstID as 'RofstID',
		rh.hSize as 'hSize',
		rh.hMonth as 'hMonth',
		rh.hDay as 'hDay',
		rh.hcYear as 'hcYear',
		rh.hcMonth as 'hcMonth',
		rh.hcDay as 'hcDay',
		rh.hcHour as 'hcHour',
		rh.hcMin as 'hcMin',
		rh.hcSec as 'hcSec',
		rh.hcHund as 'hcHund',
		rh.CreateUserID as 'CreateUserID',
		rh.CreateDate as 'CreateDate'
	From RofstHeader rh With (NoLock)


GO




If Exists(Select * From sys.views Where Name = N'vwRofstData_RofstDatabases')
	Drop View [dbo].[vwRofstData_RofstDatabases];
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2024
 Description:	(RofstData) Master View for RofstDatabases
==========================================================================================================
*/
Create View [dbo].[vwRofstData_RofstDatabases]
As

	Select rd.RofstID as 'RofstID',
		rd.dbiID as 'dbiID',
		rd.dbiType as 'dbiType',
		rd.dbiAW as 'dbiAW',
		rd.dbiTitle as 'dbiTitle',
		rd.dbiAP as 'dbiAP',
		rd.ID as 'ID',
		rd.ParentID as 'ParentID'
	From RofstDatabase rd With (NoLock)


GO


If Exists(Select * From sys.views Where Name = N'vwRofstData_RofstChildren')
	Drop View [dbo].[vwRofstData_RofstChildren];
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2024
 Description:	(RofstData) Master View for RofstChildren (RoChild & Groups)
==========================================================================================================
*/
Create View [dbo].[vwRofstData_RofstChildren]
As

	Select rc.RofstChildID as 'RofstChildID',
		rd.RofstID as 'RofstID',
		-- RoDatabase Info
		rd.dbiID as 'dbiID',
		rd.dbiType as 'dbiType',
		rd.dbiAW as 'dbiAW',
		rd.dbiTitle as 'dbiTitle',
		rd.dbiAP as 'dbiAP',
		-- RoChild Info
		rc.ID as 'ID',
		rc.ParentID as 'ParentID',
		rc.[type] as 'type',
		Case When (rc.[type] = 1) Then 'Single Line Text'   -- Fixed Length Text
			When (rc.[type] = 2) Then 'Table' 
			When (rc.[type] = 3) Then 'Multiple Line Text' 
			When (rc.[type] = 4) Then 'X/Y Plot' 
			When (rc.[type] = 8) Then 'Image' 
		Else 'Unknown' End as 'TypeDescription',
		rc.title as 'title',
		rc.roid as 'roid',
		rc.appid as 'appid',
		rc.[value] as 'value',
		Case When (Not rc.[value] is null) Then rd.dbiAP + '-' + rc.appid Else null End as 'AccPageID'
	From RofstChild rc With (NoLock)
		inner join RofstDatabase rd With (NoLock) on rc.RofstID = rd.RofstID and rc.dbiID = rd.dbiID;


GO



IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstChildInsert]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstChildInsert];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstChildInsert]
(
	@RofstID Int,
	@ID Int,
	@ParentID Int,
	@dbiID Int,
	@type Int,
	@title VarChar(Max),
	@roid VarChar(50),
	@appid VarChar(Max) = null,
	@value VarChar(Max) = null
)
With Execute as Owner
As
Begin

	Declare @AccPageID VarChar(Max) = null;
	Declare @DefaultValues VarChar(Max);


	-- Create Rofst Child/Group Record
	Insert Into RofstChild (RofstID, ID, ParentID, dbiID, [type], title, roid, appid, [value])
	Values (@RofstID, @ID, @ParentID, @dbiID, @type, @title, @roid, @appid, @value);


	-- Check for appid, if exists, then insert the default value for each return type if multi-value
	If (Len(@appid) > 0)
	Begin

		-- Get Accessory Page ID
		Select @AccPageID = Concat(d.dbiAP, '-', @appid) 
		From RofstDatabase d with (NoLock) 
		Where d.RofstID = @RofstID And d.dbiID = @dbiID;
	
		-- Insert Rofst Default Value (Parent RoChild) [roid = 12]
		Insert Into RofstDefaultValue (RofstID, roid, [value], AccPageID) 
		Values (@RofstID, @roid, Replace(@title, '\u160?', ' '), @AccPageID);

		-- Insert Rofst Default Value(s) (Children RoChild) [roid = 16] (Do Not Insert Duplicates)
		Select @DefaultValues = Replace(dbo.vefn_RofstDataReplaceVars(@value), '{', ''); 

		With ChildrenValues as 
		(
			Select (x.ListPosition - 1) as 'OffsetIndex', 
				Case When (PatIndex('%=%', x.ListValue) > 0) Then Right(x.ListValue, Len(x.ListValue)-PatIndex('%=%', x.ListValue)) Else x.ListValue End as 'DefaultValue'
			From [dbo].[vefn_ParseStringListToTable](@DefaultValues, '}') x
			Where Len(x.ListValue) > 0
		)
		Insert Into RofstDefaultValue (RofstID, roid, [value], AccPageID) 
		Select @RofstID as 'RofstID',
			Concat(@roid, Cast(Format(Min(OffsetIndex), 'D4') as VarChar(4)))  as 'roid',
			DefaultValue as 'value',
			null as 'AccPageID'
		From ChildrenValues
		Group By DefaultValue
		Order By Min(OffsetIndex) Asc

	End -- (Len(@appid) > 0)


	Return;

End
Go


IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDatabaseInsert]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDatabaseInsert];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDatabaseInsert]
(
	@RofstID Int,
	@dbiID Int,
	@dbiType Int,
	@dbiAW Int,
	@dbiTitle VarChar(Max),
	@dbiAP VarChar(Max),
	@ID Int,
	@ParentID Int
)
With Execute as Owner
As
Begin

	
	Insert Into RofstDatabase (RofstID, dbiID, dbiType, dbiAW, dbiTitle, dbiAP, ID, ParentID)
	Values (@RofstID, @dbiID, @dbiType, @dbiAW, @dbiTitle, @dbiAP, @ID, @ParentID );
	
Return;
End
	
GO



IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataExists]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataExists];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataExists]
(
	@RofstID int
)
With Execute as Owner
As
Begin

	Select Count(1) 
	From RofstHeader with (NoLock)
	Where RofstID = @RofstID;


Return;
End
	
GO



IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetChildByAccPageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetChildByAccPageID];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetChildByAccPageID]
(
	@RofstID int,
	@AccPageID VarChar(50)
)
With Execute as Owner
As
Begin

	Select rc.ROFstID,
		rc.dbiID,
		rc.ID,
		rc.ParentID,
		rc.[type],
		rc.title,
		rc.roid,
		rc.appid,
		rc.[value],
		rc.AccPageID
	From vwRofstData_RofstChildren rc
	Where rc.RofstID = @RofstID
		And rc.AccPageID = @AccPageID;

Return;
End
	
GO



IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetChildByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetChildByID];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetChildByID]
(
	@RofstID int,
	@DbiID int,
	@ID Int
)
With Execute as Owner
As
Begin

	Select rc.RofstID,
		rc.dbiID,
		rc.ID,
		rc.ParentID,
		rc.[type],
		rc.title,
		rc.roid,
		rc.appid,
		rc.[value],
		rc.AccPageID
	From vwRofstData_RofstChildren rc
	Where rc.RofstID = @RofstID
		And rc.dbiID = @DbiID 
		And rc.ID = @ID;


Return;
End
	
GO



IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetChildByRoid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetChildByRoid];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetChildByRoid]
(
	@RofstID int,
	@Roid VarChar(50)
)
With Execute as Owner
As
Begin

	Select rc.ROFstID,
		rc.dbiID,
		rc.ID,
		rc.ParentID,
		rc.[type],
		rc.title,
		rc.roid,
		rc.appid,
		rc.[value],
		rc.AccPageID
	From vwRofstData_RofstChildren rc
	Where rc.RofstID = @RofstID
		And rc.roid = @Roid;

Return;
End
	
GO



IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetChildrenByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetChildrenByID];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetChildrenByID]
(
	@RofstID int,
	@DbiID int,
	@ID Int
)
With Execute as Owner
As
Begin
		
	
	Select rc.RofstID,
		rc.dbiID,
		rc.ID,
		rc.ParentID,
		rc.[type],
		rc.title,
		rc.roid,
		rc.appid,
		rc.[value],
		rc.AccPageID
	From vwRofstData_RofstChildren rc
	Where rc.RofstID = @RofstID 
		And rc.dbiID = @DbiID 	
		And rc.ParentID = @ID
	Order By rc.ID Asc;


Return;
End
	
GO


IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetChildrenByRofstID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetChildrenByRofstID];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetChildrenByRofstID]
(
	@RofstID int
)
With Execute as Owner
As
Begin
		
	Select rc.RofstID,
		rc.dbiID,
		rc.ID,
		rc.ParentID,
		rc.[type],
		rc.title,
		rc.roid,
		rc.appid,
		rc.[value],
		rc.AccPageID
	From vwRofstData_RofstChildren rc 
	Where rc.RofstID = @RofstID
		And Not rc.[value] is null
	Order By rc.dbiID Asc, rc.[type] Asc, rc.roid Asc;


Return;
End
	
GO


IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetChildrenByRoid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetChildrenByRoid];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetChildrenByRoid]
(
	@RofstID int,
	@Roid VarChar(50)
)
With Execute as Owner
As
Begin
		
	With RoParent as 
	(
		Select RofstID, dbiID, ID, [type], title, roid
		From vwRofstData_RofstChildren
		Where RofstID = @RofstID And roid = @Roid
	)
	Select rc.RofstID,
		rc.dbiID,
		-- Ro Parent Info
		rp.[type] as 'ParentType',
		rp.title as 'ParentTitle',
		rp.roid as 'ParentRoid',
		-- Ro Child Info
		rc.ID,
		rc.ParentID,
		rc.[type],
		rc.title,
		rc.roid,
		rc.appid,
		rc.[value],
		rc.AccPageID
	From RoParent rp
		inner join vwRofstData_RofstChildren rc on rc.RofstID = rp.RofstID and rc.dbiID = rp.dbiID and rc.ParentID = rp.ID 
	Order By rc.ID Asc;


Return;
End
	
GO


IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetChildrenByType]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetChildrenByType];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetChildrenByType]
(
	@RofstID int,
	@ValueTypes VarChar(Max)
)
With Execute as Owner
As
Begin
	
	-- Remove any leading/trailing white spaces
	Set @ValueTypes = dbo.vefn_Clean( @ValueTypes, 0, null );
	
	-- Format search value
	If (Len(@ValueTypes) = 0) Set @ValueTypes = null;

	With ValueTypes as 
	(
		Select Cast(x.ListValue as Int) as 'ListValue'
		From dbo.vefn_ParseStringListToTable(@ValueTypes, ',') as x
	)
	Select rc.ROFstID,
		rc.dbiID,
		rc.ID,
		rc.ParentID,
		rc.[type],
		rc.title,
		rc.roid,
		rc.appid,
		rc.[value],
		rc.AccPageID
	From vwRofstData_RofstChildren rc
		Left outer join ValueTypes vt on vt.ListValue = rc.[type]
	Where rc.RofstID = @RofstID
		And Not rc.[value] is null
		And (@ValueTypes is null Or Not vt.ListValue is null)
	Option (Recompile); 

Return;
End
	
GO



IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetDatabaseByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetDatabaseByID];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetDatabaseByID]
(
	@RofstID int,
	@dbiID int
)
With Execute as Owner
As
Begin

	Select rd.RofstID,
		rd.dbiID,
		rd.dbiType,
		rd.dbiAW,
		rd.dbiTitle,
		rd.dbiAP,
		rd.ID,
		rd.ParentID
	From vwRofstData_RofstDatabases rd
	Where rd.RofstID = @RofstID
		And rd.dbiID = @dbiID;


Return;
End
	
GO


IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetDatabases]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetDatabases];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetDatabases]
(
	@RofstID int
)
With Execute as Owner
As
Begin

	Select rd.RofstID,
		rd.dbiID,
		rd.dbiType,
		rd.dbiAW,
		rd.dbiTitle,
		rd.dbiAP,
		rd.ID,
		rd.ParentID
	From vwRofstData_RofstDatabases rd
	Where rd.RofstID = @RofstID
	Order By rd.dbiID Asc;


Return;
End
	
GO


IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetRofstByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetRofstByID];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetRofstByID]
(
	@RofstID int,
	@IncludeRoLookup Bit
)
With Execute as Owner
As
Begin

	Select r.ROFstID,
		r.RODbID,
		Case (@IncludeRoLookup) When 1 Then r.ROLookup Else null End as 'ROLookup',
		r.DTS,
		r.UserID,
		r.LastChanged
	From ROFsts r with (NoLock)
	Where r.RofstID = @RofstID;


Return;
End
	
GO


IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataGetValueDifferences]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataGetValueDifferences];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataGetValueDifferences]
(
	@OriginalRofstID Int,
	@CurrentRofstID Int
)
With Execute as Owner
As
Begin

	Declare @Differences Table (OriginalRoid VarChar(25), OriginalValue VarChar(Max), 
								CurrentRoid VarChar(25), CurrentValue VarChar(Max));


	With OriginalRofst as 
	(
		Select rc.roid, rc.[value]
		From vwRofstData_RofstChildren rc
		Where rc.RofstID = @OriginalRofstID
			And Not rc.[value] is null 

	), CurrentRofst as (

		Select rc.roid, rc.[value]
		From vwRofstData_RofstChildren rc
		Where rc.RofstID = @CurrentRofstID
			And Not rc.[value] is null 
	)
	Insert Into @Differences (OriginalRoid, OriginalValue, CurrentRoid, CurrentValue)
	Select o.roid, o.[value], c.roid, c.[value]
	From OriginalRofst o 
		left outer join CurrentRofst c on c.roid = o.roid
	Where o.[value] <> c.[value];


	Select OriginalRoid as 'Roid',
		Case When (CurrentValue is null) Then 1 Else 0 End as 'IsDeleted',
		Case When (Not CurrentValue is null) Then 1 Else 0 End as 'IsModified'
	From @Differences;


Return;
End
	
GO



IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstDataSearch]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstDataSearch];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	RO Default Value Search
				
				@SearchTypeID => (1) Starts With (2) Ends With (3) Contains (4) Exact Match 
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstDataSearch]
(
	@RofstID int,
	@Value VarChar(Max) = null,
	@SearchTypeID Int = 1, -- Starts With
	@MaxNumOfRecords Int = null
)
With Execute as Owner
As
Begin

	-- Remove any leading/trailing white spaces
	Set @Value = dbo.vefn_Clean( @Value, 0, null );
	
	-- Escape any % signs in the search value
	Set @Value = Replace( @Value, '%', '[%]' );
	
	-- Format search value
	If (Len(@Value) = 0) Set @Value = null;
	
	-- Create Temp Results Table
	Create Table #SearchResults 
	(roid VarChar(50) Not Null, [value] VarChar(Max), RowNum Int Not Null Primary Key);

	-- Select roids/values based on search criteria
	Insert Into #SearchResults (roid, [value], RowNum)
	Select rdv.roid, 
		rdv.[value],
		Row_Number() Over (Order By rdv.[value] Asc, rdv.roid Asc) as 'RowNum'
	From vwRofstData_RofstDefaultValues rdv
	Where rdv.RofstID = @RofstID
		And ((@Value is null)
			Or (@SearchTypeID = 1 And rdv.[value] like @Value + '%')		-- Starts With
			Or (@SearchTypeID = 2 And rdv.[value] like '%' + @Value)		-- Ends With
			Or (@SearchTypeID = 3 And rdv.[value] like '%' + @Value + '%')	-- Contains
			Or (@SearchTypeID = 4 And rdv.[value] = @Value))				-- Exact Match
	Order By rdv.[value] Asc, rdv.roid Asc 
	Option (Recompile);

	-- Return the specified number of results
	Select sr.roid, sr.[value]
	From #SearchResults sr
	Where (@MaxNumOfRecords is null Or sr.RowNum <= @MaxNumOfRecords) 
	Order By sr.RowNum Asc;



Return;
End
	
GO






IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vesp_RofstHeaderInsert]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP Procedure [dbo].[vesp_RofstHeaderInsert];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2020 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
/*
==========================================================================================================
 Author:		Jake Ropar
 Create Date:	03/24/2022
 Description:	
==========================================================================================================
 */
Create Procedure [dbo].[vesp_RofstHeaderInsert]
(
	@RofstID Int,
	@hSize Int,
	@hMonth Int,
	@hDay Int,
	@hcYear Int,
	@hcMonth Int,
	@hcDay Int,
	@hcHour Int,
	@hcMin Int,
	@hcSec Int,
	@hcHund Int,
	@UserID VarChar(50)
)
With Execute as Owner
As
Begin

	
	Insert Into RofstHeader (RofstID, hSize, hMonth, hDay, hcYear, hcMonth, hcDay, hcHour, hcMin, hcSec, hcHund, CreateUserID, CreateDate)
	Values (@RofstID, @hSize, @hMonth, @hDay, @hcYear, @hcMonth, @hcDay, @hcHour, @hcMin, @hcSec, @hcHund, @UserID, GetDate());

	
Return;
End
	
GO