--System configuration
IF NOT((SELECT SERVERPROPERTY('Edition') AS	[Edition]) = 'SQL Azure')
BEGIN
	DECLARE @NCOMMAND NVARCHAR(MAX)
	SET @NCOMMAND =
	N'EXECUTE SP_CONFIGURE ''show advanced options'', 1;
	RECONFIGURE;
	EXECUTE SP_CONFIGURE ''Ole Automation Procedures'', 1;
	RECONFIGURE;'
	EXECUTE SP_EXECUTESQL @NCOMMAND;
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KaaSCtlMaster' )
BEGIN
	CREATE TABLE [KaaSCtlMaster](
		[id]				[int] IDENTITY(1,1) NOT NULL,
		[ComponentName]		[varchar](100) NOT NULL,
		[ControlName]		[varchar](100) NULL,
		[FriendlyName]		[varchar](100) NOT NULL,
		[IsVisible]			[bit] NOT NULL,
		[Deleted]			[bit] NOT NULL,
		[ParentId]			[smallint] DEFAULT 0 NOT NULL
		CONSTRAINT pk_KaaSCtlMaster_Id PRIMARY KEY(Id)
	)
END

GO 

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KaaSCtlGroupControls' )
BEGIN
	CREATE TABLE [KaaSCtlGroupControls](
		[id]		[int] IDENTITY(1,1) NOT NULL,
		[groupid]	[int] NOT NULL,
		[masterid]	[int] NOT NULL,
		CONSTRAINT pk_KaaSCtlGroupControls_Id PRIMARY KEY(Id)
	)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'current-client-details' AND [ControlName] IS  NULL) 
BEGIN
	INSERT INTO [KaaSCtlMaster](
		[ComponentName], 
		[ControlName], 
		[FriendlyName], 
		[IsVisible], 
		[ParentId], 
		[Deleted]) 
	VALUES (
		'current-client-details',
		null, 
		'Current Client Details', 
		0, 
		0, 
		0)
END

GO

--Master
IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'change-matter-security-permissions' AND [ControlName] IS NULL) 
BEGIN
	INSERT INTO [KaaSCtlMaster](
		[ComponentName], 
		[ControlName], 
		[FriendlyName], 
		[IsVisible], 
		[ParentId], 
		[Deleted]) 
	VALUES ('change-matter-security-permissions',
		null, 
		'Change Matter Security Permissions', 
		0, 
		0, 
		0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'current-client-billing' AND [ControlName] IS NULL) 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], 
	[ControlName], 
	[FriendlyName], 
	[IsVisible], 
	[ParentId], 
	[Deleted]) 
VALUES ('current-client-billing',
	null, 
	'Current Client Billing', 
	0, 
	0, 
	0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'general-tab' AND [ControlName] =  'btnSaveGeneral') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('general-tab','btnSaveGeneral', 'Current Client Details - General', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'bank-details-tab' AND [ControlName] =  'btnSaveBankDetails') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('bank-details-tab','btnSaveBankDetails', 'Current Client Details - Bank Details', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'billing-details-tab' AND [ControlName] =  'btnSaveBillingDetails') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('billing-details-tab','btnSaveBillingDetails', 'Current Client Details - Billing Details', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'categories-tab' AND [ControlName] =  'btnSaveCategories') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('categories-tab','btnSaveCategories', 'Current Client Details - Categories', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'corporate-tab' AND [ControlName] =  'btnSaveCorporate') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('corporate-tab','btnSaveCorporate', 'Current Client Details - Corporate', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'cross-ref-tab' AND [ControlName] =  'btnSaveCrossRef') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('cross-ref-tab','btnSaveCrossRef', 'Current Client Details - Cross Reference', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'legal-details-tab' AND [ControlName] =  'btnSaveLegalDetails') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('legal-details-tab','btnSaveLegalDetails', 'Current Client Details - Legal Details', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'matters-tab' AND [ControlName] =  'btnSaveMatters') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('matters-tab','btnSaveMatters', 'Current Client Details - Matters', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'notes-tab' AND [ControlName] =  'btnSaveNotes') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('notes-tab','btnSaveNotes', 'Current Client Details - Notes', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'permission-tab' AND [ControlName] =  'btnSavePermission') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('permission-tab','btnSavePermission', 'Current Client Details - Permission', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'personal-tab' AND [ControlName] =  'btnSavePersonal') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('personal-tab','btnSavePersonal', 'Current Client Details - Personal', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'client-contact-tab' AND [ControlName] =  'btnClientContactInsert') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('client-contact-tab','btnClientContactInsert', 'Current Client Details - Client Contact', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'client-contact-tab' AND [ControlName] =  'btnClientContactChange') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('client-contact-tab','btnClientContactChange', 'Current Client Details - Client Contact', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'client-contact-tab' AND [ControlName] =  'btnClientContactDelete') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('client-contact-tab','btnClientContactDelete', 'Current Client Details - Client Contact', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'cross-ref-tab' AND [ControlName] =  'btnCrossRefInsert') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('cross-ref-tab','btnCrossRefInsert', 'Current Client Details - Cross Reference', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'cross-ref-tab' AND [ControlName] =  'btnCrossRefChange') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('cross-ref-tab','btnCrossRefChange', 'Current Client Details - Cross Reference', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'cross-ref-tab' AND [ControlName] =  'btnCrossRefDelete') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('cross-ref-tab','btnCrossRefDelete', 'Current Client Details - Cross Reference', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'categories-tab' AND [ControlName] =  'btnCategoriesInsert') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('categories-tab','btnCategoriesInsert', 'Current Client Details - Categories', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'categories-tab' AND [ControlName] =  'btnCategoriesChange') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('categories-tab','btnCategoriesChange', 'Current Client Details - Categories', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'categories-tab' AND [ControlName] =  'btnCategoriesDelete') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('categories-tab','btnCategoriesDelete', 'Current Client Details - Categories', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'corporate-tab' AND [ControlName] =  'btnCorporateOfficerInsert') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('corporate-tab','btnCorporateOfficerInsert', 'Current Client Details - Corporate', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'corporate-tab' AND [ControlName] =  'btnCorporateOfficerChange') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('corporate-tab','btnCorporateOfficerChange', 'Current Client Details - Corporate', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'corporate-tab' AND [ControlName] =  'btnCorporateOfficerDelete') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('corporate-tab','btnCorporateOfficerDelete', 'Current Client Details - Corporate', 0, 1, 0)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'current-client-detail-permission-tab' AND [ControlName] =  'mat-tab-label-0-10') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('current-client-detail-permission-tab','mat-tab-label-0-10', 'Current Client Details - Permission Tab', 0, 2, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'current-client-detail-billing-tab' AND [ControlName] =  'dvCurrentClientDetailsManageBilling') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('current-client-detail-billing-tab','dvCurrentClientDetailsManageBilling', 'Current Client Details - Billing Tab', 0, 3, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlGroupControls] WHERE [GroupId] = '1000033' AND [MasterId] =  1) 
BEGIN
INSERT INTO [KaaSCtlGroupControls] ([GroupId], [MasterId]) VALUES ('1000033',1)
END

GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlGroupControls] WHERE [GroupId] = '1000012' AND [MasterId] =  2) 
BEGIN
INSERT INTO [KaaSCtlGroupControls] ([GroupId], [MasterId]) VALUES ('1000012',2)
END


GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlGroupControls] WHERE [GroupId] = '1000006' AND [MasterId] =  3) 
BEGIN
INSERT INTO [KaaSCtlGroupControls] ([GroupId], [MasterId]) VALUES ('1000006',3)
END

GO


IF NOT EXISTS(SELECT TOP 1 1 FROM Settings where [KeyName] = 'StatuteWarning')
BEGIN
	INSERT INTO Settings ([KeyName],[KeyValue]) VALUES ('StatuteWarning',0)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM Settings where [KeyName] = 'StatuteActionCode')
BEGIN
	INSERT INTO Settings ([KeyName],[KeyValue]) VALUES ('StatuteActionCode','')
END
GO

--Sprint 6 Scritps
IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'ClientLabel')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'ClientLabel',
		 'Client'
		)
END
GO

IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'MatterLabel')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'MatterLabel',
		 'Matter'
		)
END
GO

IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'AssociateLabel')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'AssociateLabel',
		 'Associate'
		)
END
GO

IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'CaseLabel')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'CaseLabel',
		 'Case'
		)
END
GO


IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'DocumentNamingMethod')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'DocumentNamingMethod',
		 'D'
		)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE [Name] = 'RecentHandlerList')
BEGIN
CREATE TABLE RecentHandlerList (
    [Id] INT IDENTITY(1,1) CONSTRAINT PK_RecentHandlerList PRIMARY KEY,
	[Code] VARCHAR(10),
	[AccessedBy] VARCHAR(10) CONSTRAINT FK_Handlers_Code_RecentHandlerList_AccessedBy FOREIGN KEY REFERENCES Handlers(CODE),
	[AccessedTime] DATETIME,
)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'Panels')
BEGIN
CREATE TABLE [dbo].[Panels](
	[PanelId] [int] IDENTITY(1,1) NOT NULL,
	[PanelName] [varchar](255) NULL,
	[Description] [varchar](255) NULL,
	[DashboardType] [varchar](50) NULL,
	[Width] [int] NULL,
	CONSTRAINT pk_PanelId PRIMARY KEY (PanelId)
)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'Widgets')
BEGIN
CREATE TABLE [dbo].[Widgets](
	[WidgetId] [int] IDENTITY(1,1) NOT NULL,
	[WidgetName] [varchar](255) NULL,
	[Description] [varchar](255) NULL,
	[TemplateId] [int] NULL,
	[WidgetData] [text] NULL,
	CONSTRAINT pk_WidgetId PRIMARY KEY (WidgetId)
	)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'PanelWidgetAssociation')
BEGIN
CREATE TABLE [dbo].[PanelWidgetAssociation](
	[AssociationID] [int] IDENTITY(1,1) NOT NULL,
	[PanelId] [int] NOT NULL,
	[WidgetId] [int] NOT NULL,
	CONSTRAINT pk_AssociationId PRIMARY KEY (AssociationId),
	CONSTRAINT [FkPanelAssociation] FOREIGN KEY([PanelId]) REFERENCES [dbo].[Panels] ([PanelId]),
	CONSTRAINT [FkWidgetAssociation] FOREIGN KEY([WidgetId]) REFERENCES [dbo].[Widgets] ([WidgetId])
	)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'UserPanels')
BEGIN
CREATE TABLE [dbo].[UserPanels](
	[UserPanelId] [int] IDENTITY(1,1) NOT NULL,
	[HandlerCode] [varchar](10) NOT NULL,
	[PanelId] [int] NOT NULL,
	[PanelName] [varchar](255) NULL,
	[ParentPanel] [int] NULL,
	CONSTRAINT pk_UserPanelId PRIMARY KEY (UserPanelId),
	CONSTRAINT [FkUserPanelPanelId] FOREIGN KEY([PanelId]) REFERENCES [dbo].[Panels] ([PanelId])
	)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'UserWidgets')
BEGIN
CREATE TABLE [dbo].[UserWidgets](
	[UserWidgetsId] [int] IDENTITY(1,1) NOT NULL,
	[UserPanelId] [int] NOT NULL,
	[WidgetId] [int] NOT NULL,
	[WidgetData] [text] NULL,
	CONSTRAINT pk_UserWidgetsId PRIMARY KEY (UserWidgetsId),
	CONSTRAINT [FkUserWidgetsPanelId] FOREIGN KEY([UserPanelId]) REFERENCES [dbo].[UserPanels] ([UserPanelId]),
	CONSTRAINT [FkUserWidgetsWidgetId] FOREIGN KEY([WidgetId]) REFERENCES [dbo].[Widgets] ([WidgetId])
	)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'UITemplates')
BEGIN
CREATE TABLE [dbo].[UITemplates](
	[TemplateId] [int] IDENTITY(1,1) NOT NULL,
	[TemplateName] [varchar](255) NULL,
	[Description] [varchar](255) NULL,
	[TemplateQuery] [text] NULL,
	[QueryComponent] [varchar](255) NULL,
	[DisplayComponent] [varchar](255) NULL,
	CONSTRAINT pk_TemplateId PRIMARY KEY (TemplateId),
	CONSTRAINT [FK_Widget_TemplateId] FOREIGN KEY([TemplateId]) REFERENCES [dbo].[UITemplates] ([TemplateId])
	)
END
GO

------------------------------------[PanelWidgetAssociation] Insert End--------------------------------------------------------

IF NOT EXISTS(SELECT TOP 1 1  FROM SYS.schemas WHERE [name] = 'Common')
BEGIN
	Declare @Query nvarchar(200)
	SET @Query = 'CREATE SCHEMA Common'
	exec sp_executesql @Query
END

GO

IF OBJECT_ID(N'[Common].[Release]', N'U')IS NULL
BEGIN
CREATE TABLE [Common].[Release]
(
	[ReleaseID]		[smallint] IDENTITY(1,1) NOT NULL,
	[Version]		[nvarchar](40) NOT NULL,
	[VersionLabel]	[nvarchar](40) NOT NULL,
	[ReleaseDate]	[datetime] NOT NULL,
	CONSTRAINT [pk_Release_ReleaseID] PRIMARY KEY(ReleaseID)
 )
END
GO

IF NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'Release' and [NAME] = 'ExecutedPatchFrom')
BEGIN
	ALTER TABLE [Common].[Release]
	ADD [ExecutedPatchFrom] TINYINT DEFAULT 0 NOT NULL
END
GO

IF NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'Release' and [NAME] = 'ExecutedBy')
BEGIN
	ALTER TABLE [Common].[Release]
	ADD [ExecutedBy] VARCHAR(200)
END
GO

IF NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'Release' and [NAME] = 'UpdatedStatus')
BEGIN
	ALTER TABLE [Common].[Release]
	ADD [UpdatedStatus] TINYINT DEFAULT 0 NOT NULL
END
GO

IF OBJECT_ID(N'BriefBuildingWebJobStatus',N'U')IS NULL
BEGIN
	Create TABLE 
		BriefBuildingWebJobStatus
		(
			[WebJobId]			VARCHAR(50) NOT NULL,
			[CreatedTime]		DATETIME,
			[ExpirationTime]	DATETIME,			
			[BriefId]			INT,
			[Status]			VARCHAR(20),
			[NoOfDocument]		INT,
			[Completed]			INT,
			CONSTRAINT pk_BriefBuildingWebJobStatus PRIMARY KEY ([WebJobId]),
			CONSTRAINT fk_BriefBuildingWebJobStatus_BriefId_Brief_BriefId FOREIGN KEY (BriefId) REFERENCES Brief(ID)
	)
END
GO

IF OBJECT_ID(N'CustomFilterbyUserandPage',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[CustomFilterbyUserandPage](
		[ID]			[int]			IDENTITY(1,1) NOT NULL,
		[HandlerCode]	[varchar](50)	NOT NULL,
		[PageName]		[varchar](50)	NOT NULL,
		[FilterName]	[varchar](50)	NOT NULL,
		[CreatedDate]	[datetime]		NOT NULL,
		[ActionType]	[varchar](50)	NULL,
		[Priority]		[varchar](50)	NULL,
		[Flag]			[varchar](50)	NULL,
		[IsDefault]		[bit]			DEFAULT 0,
	CONSTRAINT pk_CustomFilterbyUserandPage_id PRIMARY KEY (ID))

END

GO

IF OBJECT_ID(N'AppRedirectionUrls',N'U')IS NULL
BEGIN
	Create TABLE 
		AppRedirectionUrls
		(
			[UrlId]				INT IDENTITY(1,1) NOT NULL,
			[ShortCode]			VARCHAR(100) NOT NULL,
			[Description]		VARCHAR(100) NOT NULL,
			[UrlLink]			VARCHAR(200) NOT NULL,
			[IsActive]		    BIT,
			[CreatedTime]		DATETIME,
			CONSTRAINT pk_AppRedirectionUrls PRIMARY KEY ([UrlId])
	)
END
GO

IF NOT EXISTS(SELECT * FROM sys.objects WHERE [type] = 'D' And [name] = 'DF_AppRedirectionUrls_IsActive')
BEGIN
	ALTER TABLE [AppRedirectionUrls] ADD  CONSTRAINT [DF_AppRedirectionUrls_IsActive]  DEFAULT ((1)) FOR [IsActive]
END
GO
IF NOT EXISTS(SELECT * FROM sys.objects WHERE [type] = 'D' And [name] = 'DF_AppRedirectionUrls_CreatedTime')
BEGIN
	ALTER TABLE [AppRedirectionUrls] ADD  CONSTRAINT [DF_AppRedirectionUrls_CreatedTime]  DEFAULT (GETUTCDATE()) FOR [CreatedTime]
END
GO

IF NOT EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'WidgetQuery' WHERE SO.[name] = 'Widgets' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [Widgets] ADD WidgetQuery TEXT NULL
END
GO

IF NOT EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'RedirectionUrlId' WHERE SO.[name] = 'Widgets' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [Widgets] ADD RedirectionUrlId INT NULL
END
GO

IF NOT EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'ActionQuery' WHERE SO.[name] = 'Widgets' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [Widgets] ADD ActionQuery TEXT NULL
END
GO

IF NOT EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'RedirectionFilter' WHERE SO.[name] = 'Widgets' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [Widgets] ADD RedirectionFilter VARCHAR(300) NULL
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_DASHBOARD' AND [UrlLink] = '/tasks/task-dashboard')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Task Dashboard','/tasks/task-dashboard','TASK_DASHBOARD')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_MY' AND [UrlLink] = '/tasks/main/my-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('My Tasks','/tasks/main/my-tasks','TASK_MY')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_TEAM' AND [UrlLink] = '/tasks/main/team-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Team Tasks','/tasks/main/team-tasks','TASK_TEAM')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_OVERDUE' AND [UrlLink] = '/tasks/main/overdue-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Overdue Tasks','/tasks/main/overdue-tasks','TASK_OVERDUE')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_DAY' AND [UrlLink] = '/tasks/main/days-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Days Tasks','/tasks/main/days-tasks','TASK_DAY')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_NEXT_WEEK' AND [UrlLink] = '/tasks/main/next-weeks-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Next Week Tasks','/tasks/main/next-weeks-tasks','TASK_NEXT_WEEK')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_NEXT_MONTH' AND [UrlLink] = '/tasks/main/next-months-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Next Month Tasks','/tasks/main/next-months-tasks','TASK_NEXT_MONTH')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_OUTSTANDING' AND [UrlLink] = '/tasks/main/outstanding-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Outstanding Tasks','/tasks/main/outstanding-tasks','TASK_OUTSTANDING')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_LAST_WEEK' AND [UrlLink] = '/tasks/main/last-weeks-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Last Weeks Tasks','/tasks/main/last-weeks-tasks','TASK_LAST_WEEK')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_LAST_MONTH' AND [UrlLink] = '/tasks/main/last-months-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('Last Month Tasks','/tasks/main/last-months-tasks','TASK_LAST_MONTH')
END
GO

IF NOT EXISTS (SELECT 1 FROM AppRedirectionUrls WHERE [ShortCode] = 'TASK_ALL' AND [UrlLink] = '/tasks/main/all-tasks')
BEGIN
	INSERT INTO AppRedirectionUrls ([Description],[UrlLink],[ShortCode]) VALUES ('All Tasks','/tasks/main/all-tasks','TASK_ALL')
END
GO

--SELECT * FROM Widgets

UPDATE Widgets SET RedirectionUrlId = (SELECT UrlId From AppRedirectionUrls WHERE ShortCode='TASK_OVERDUE') WHERE TemplateId = 1000
GO

UPDATE Widgets SET RedirectionUrlId = (SELECT UrlId From AppRedirectionUrls WHERE ShortCode='TASK_OUTSTANDING') WHERE TemplateId = 1001
GO

UPDATE Widgets SET RedirectionUrlId = (SELECT UrlId From AppRedirectionUrls WHERE ShortCode='TASK_MY') WHERE TemplateId IN(1006,1007,1008,1009,1010,1011)
GO

UPDATE Widgets SET RedirectionFilter = 'DEL.ActionType=''C''' WHERE WidgetId = 106
GO

UPDATE Widgets SET RedirectionFilter = 'DEL.ActionType=''O''' WHERE WidgetId = 107
GO

UPDATE Widgets SET RedirectionFilter = 'DEL.Priority=''H''' WHERE WidgetId = 108
GO

UPDATE Widgets SET RedirectionFilter = 'DEL.ActionType=''T''' WHERE WidgetId = 109
GO

IF EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'TemplateQuery' WHERE SO.[name] = 'UITemplates' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [dbo].[UITemplates] DROP COLUMN TemplateQuery
END
GO

IF OBJECT_ID(N'CustomFilterbyUserandPage',N'U') IS NOT NULL  
BEGIN
	IF NOT EXISTS(SELECT 1 FROM sys.columns 
			  WHERE Name = N'FilterOrder'
			  AND Object_ID = Object_ID(N'CustomFilterbyUserandPage',N'U'))
		BEGIN
		ALTER TABLE [dbo].[CustomFilterbyUserandPage] ADD FilterOrder int default 0
		END
	
END 
GO

IF OBJECT_ID(N'CustomFilterbyUserandPage',N'U') IS NOT NULL  
BEGIN
	IF EXISTS(SELECT 1 FROM sys.columns 
			  WHERE Name = N'FilterOrder'
			  AND Object_ID = Object_ID(N'CustomFilterbyUserandPage',N'U'))
		BEGIN
			UPDATE  [dbo].[CustomFilterbyUserandPage] SET FilterOrder = 0 WHERE FilterOrder is null
		END
END 
GO

IF OBJECT_ID(N'CustomSavedFilters',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].CustomSavedFilters(
		[ID]			[int]			IDENTITY(1,1) NOT NULL,
		[FilterID]		[int]			NOT NULL,
		[FilterColumn]	[varchar](50)	NOT NULL,
		[Value]			[varchar](2000)	NOT NULL
	CONSTRAINT pk_CustomSavedFilters_id PRIMARY KEY (ID) 
	)
END

GO

--- Custom Filter Defined Script Start

IF OBJECT_ID(N'CustomFilterColumndetails',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].CustomFilterColumndetails(
		[ID]				[int]			IDENTITY(1,1) NOT NULL,
		[name]				[VARCHAR](50)	NOT NULL,
		[Code]				[varchar](50)	NOT NULL,
		[isSelected]		bit				NOT NULL DEFAULT 1,
		[FilterOrder]		int				NOT NULL DEFAULT 0,
		[IsIconAvailable]	bit				NOT NULL DEFAULT 0,
		[Iconpath]			[varchar](50)	NOT NULL DEFAULT '',
		[ColumnName]		[varchar](50)	NOT NULL,
		[PageName]			[varchar](50)	NOT NULL
	CONSTRAINT pk_CustomFilterColumndetails_id PRIMARY KEY (ID) 
	)
END

GO

IF OBJECT_ID(N'CustomFilterColumndetails',N'U') IS NOT NULL
BEGIN
TRUNCATE TABLE CustomFilterColumndetails
END

GO

IF OBJECT_ID(N'CustomFilterColumndetails',N'U') IS NOT NULL
BEGIN

INSERT INTO CustomFilterColumndetails
-- Action Type Case Diary
SELECT 'Action'			AS [name],	'A' AS [Code],1 AS [isSelected] ,0  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Fixed Date'		AS [name],	'F' AS [Code],1 AS [isSelected] ,1  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Note'			AS [name],	'N' AS [Code],1 AS [isSelected] ,2  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Appointment'	AS [name],	'P' AS [Code],1 AS [isSelected] ,3  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Remainder'		AS [name],	'R' AS [Code],1 AS [isSelected] ,4  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Email'			AS [name],	'E' AS [Code],1 AS [isSelected] ,5  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Link'			AS [name],	'L' AS [Code],1 AS [isSelected] ,6  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Phone'			AS [name],	'T' AS [Code],1 AS [isSelected] ,7  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Dictation'		AS [name],	'D' AS [Code],1 AS [isSelected] ,8  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Undertaking'	AS [name],	'U' AS [Code],1 AS [isSelected] ,9  AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Statute'		AS [name],	'S' AS [Code],1 AS [isSelected] ,10 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Critical'		AS [name],	'C' AS [Code],1 AS [isSelected] ,11 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Court'			AS [name],	'0' AS [Code],1 AS [isSelected] ,12 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Scanned'		AS [name],	'M' AS [Code],1 AS [isSelected] ,13 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Cheque'			AS [name],	'Q' AS [Code],1 AS [isSelected] ,14 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'PhotoID'		AS [name],	'K' AS [Code],1 AS [isSelected] ,15 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Non PhotoId'	AS [name],	'I' AS [Code],1 AS [isSelected] ,16 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Type' AS [ColumnName], 'CaseDiary' AS [PageName] 

INSERT INTO CustomFilterColumndetails
-- Action Priority Case Diary
SELECT 'High'			AS [name],	'H' AS [Code],1 AS [isSelected] ,0 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Priority' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Low'			AS [name],	'L' AS [Code],1 AS [isSelected] ,1 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Priority' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Normal'			AS [name],	'N' AS [Code],1 AS [isSelected] ,2 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Priority' AS [ColumnName], 'CaseDiary' AS [PageName]


INSERT INTO CustomFilterColumndetails
-- Action Flag Case Diary
SELECT 'Clear Flag'		AS [name],	'0' AS [Code],1 AS [isSelected] ,0 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Flag' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Blue Flag'		AS [name],	'1' AS [Code],1 AS [isSelected] ,1 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Flag' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Orange Flag'	AS [name],	'2' AS [Code],1 AS [isSelected] ,2 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Flag' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Red Flag'		AS [name],	'3' AS [Code],1 AS [isSelected] ,3 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Flag' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Green Flag'		AS [name],	'4' AS [Code],1 AS [isSelected] ,4 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Flag' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Black Flag'		AS [name],	'5' AS [Code],1 AS [isSelected] ,5 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Flag' AS [ColumnName], 'CaseDiary' AS [PageName] UNION 
SELECT 'Yellow Flag'	AS [name],	'6' AS [Code],1 AS [isSelected] ,6 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Action Flag' AS [ColumnName], 'CaseDiary' AS [PageName] 

INSERT INTO CustomFilterColumndetails
-- Released MyInvoice
SELECT 'Yes'			AS [name],	'Y' AS [Code],1 AS [isSelected] ,0 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Released' AS [ColumnName]   , 'MyInvoice' AS [PageName] UNION 
SELECT 'No'				AS [name],	'N' AS [Code],1 AS [isSelected] ,1 AS [FilterOrder]	,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Released' AS [ColumnName]   , 'MyInvoice' AS [PageName]

INSERT INTO CustomFilterColumndetails
-- Released MyInvoice
SELECT 'Equals 0'		AS [name],	'0' AS [Code],1 AS [isSelected] ,0 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'BillNo' AS [ColumnName]   , 'MyInvoice' AS [PageName] UNION 
SELECT 'Not equals 0'	AS [name],	'-1' AS [Code],1 AS [isSelected],1 AS [FilterOrder]	,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'BillNo' AS [ColumnName]   , 'MyInvoice' AS [PageName] 

INSERT INTO CustomFilterColumndetails

SELECT 'Request'		AS [name],	'0' AS [Code],1 AS [isSelected] ,0 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Status' AS [ColumnName], 'MyCheque' AS [PageName] UNION 
SELECT 'Released'		AS [name],	'1' AS [Code],1 AS [isSelected] ,1 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Status' AS [ColumnName], 'MyCheque' AS [PageName] UNION
SELECT 'Approved'		AS [name],	'2' AS [Code],1 AS [isSelected] ,2 AS [FilterOrder] ,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Status' AS [ColumnName], 'MyCheque' AS [PageName] UNION
SELECT 'Returned'		AS [name],	'3' AS [Code],1 AS [isSelected] ,3 AS [FilterOrder]	,0 AS [IsIconAvailable] , '' AS [Iconpath] , 'Status' AS [ColumnName], 'MyCheque' AS [PageName] 




INSERT INTO CustomFilterColumndetails

SELECT  'Email Files'				AS [name], '.eml,EML,MSG'									AS [Code] ,1 AS [isSelected] ,0  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/outlook.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Images Files'				AS [name], '.jpg,JPG,PNG,BMP,gif,tif,PSD,HEI,ICO,JPE'		AS [Code] ,1 AS [isSelected] ,1  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/IMAGE.png'		AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'PDF Files'					AS [name], '.pdf,PDF'										AS [Code] ,1 AS [isSelected] ,2  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/pdf.png'		AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Text Files'				AS [name], '.txt,TXT,LOG,TEX,RTF'							AS [Code] ,1 AS [isSelected] ,3  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/TEXT.png'		AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Documents'					AS [name], 'docx,DOC,ODS,ODT'								AS [Code] ,1 AS [isSelected] ,4  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/DOCUMENT.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Excel Files'				AS [name], 'CSV,XLS,xlsx'									AS [Code] ,1 AS [isSelected] ,5  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/excelv1.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Audio Files'				AS [name], 'MP3,WAV,M4A'									AS [Code] ,1 AS [isSelected] ,6  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/WAVIcon.jpg'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Presentation Documents'	AS [name], 'pptx,PPT,ODP,PPS'								AS [Code] ,1 AS [isSelected] ,7  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/PPT.png'		AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Compressed Files'			AS [name], 'ZIP'											AS [Code] ,1 AS [isSelected] ,8  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/Unknown.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Video Files'				AS [name], 'MP4,MOV'										AS [Code] ,1 AS [isSelected] ,9  AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/Unknown.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Android Apps'				AS [name], 'APK'											AS [Code] ,1 AS [isSelected] ,10 AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/Unknown.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Web Files'					AS [name], 'htm,XML,MHT,SWF'								AS [Code] ,1 AS [isSelected] ,11 AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/Unknown.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Graphics Documents'		AS [name], 'CGM,DWG,EPS,PS,SVG,VSD'							AS [Code] ,1 AS [isSelected] ,12 AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/Unknown.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'DB Files'					AS [name], 'FOL,SQL'										AS [Code] ,1 AS [isSelected] ,13 AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/Unknown.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] UNION
SELECT  'Calendar Files'			AS [name], 'ICS'											AS [Code] ,1 AS [isSelected] ,14 AS [FilterOrder] ,1 AS [IsIconAvailable] , 'assets/images/filetypes/Unknown.png'	AS [Iconpath] , 'Type' AS [ColumnName], 'DocumentManager' AS [PageName] 
																									   
END

GO	

IF NOT EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO 
INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'HandlerCode'
 WHERE SO.[name] = 'TransactViewProperty' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [dbo].[TransactViewProperty] 
	ADD [HandlerCode] VARCHAR(10)
END 


IF NOT EXISTS (SELECT TOP 1 1  FROM sys.objects where type='F' and name ='FK_Handlers_Code_TransactViewProperty_HandlerCode') 
BEGIN	
	ALTER TABLE [dbo].[TransactViewProperty]  WITH NOCHECK
	ADD  CONSTRAINT [FK_Handlers_Code_TransactViewProperty_HandlerCode] FOREIGN KEY([HandlerCode]) REFERENCES [dbo].[Handlers] ([CODE])
END


IF EXISTS (SELECT TOP 1 1  FROM sys.objects where type='F' and name ='FK_Handlers_Code_TransactViewProperty_HandlerCode') 
BEGIN
	ALTER TABLE [dbo].[TransactViewProperty] 
	CHECK CONSTRAINT [FK_Handlers_Code_TransactViewProperty_HandlerCode]
END



IF OBJECT_ID(N'TransactUDFFieldMapping',N'U') IS NULL
BEGIN
CREATE
 TABLE [dbo].[TransactUDFFieldMapping]
      ([Id]							INT	IDENTITY(1,1),
       [TransactUDFField]           NVARCHAR(60),
       [KeyhouseUDFField]           NVARCHAR(60),
       [IsRetired]                  BIT      DEFAULT 0  NOT NULL,
       CONSTRAINT [pk_TransactUDFFieldMapping]
                  PRIMARY KEY CLUSTERED
                 ([Id])
		)
END
																					   
IF NOT EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO 
INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'TransactSide'
 WHERE SO.[name] = 'TransactUDFFieldMapping' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [dbo].[TransactUDFFieldMapping] 
	ADD [TransactSide] VARCHAR(1) DEFAULT 'C' NOT NULL
END 

GO



--- Custom Filter Defined Script End