--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
GO
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
GO
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
GO
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
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] = '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
--Added for Default Partner in settings
IF NOT EXISTS (SELECT 1 FROM [dbo].[Settings] WHERE KeyName = 'TransactNewCaseDefaultPartner')
BEGIN
insert into [dbo].[Settings] values('TransactNewCaseDefaultPartner','');
END
GO
--Updating seller price UDF field to Purchaser price as discussed for TRansact - Keyhouse integration
IF NOT EXISTS(SELECT TOP 1 1 FROM [TransactUDFFieldMapping] WHERE TransactUDFField = 'UDFPropertyPurchasePrice' AND KeyhouseUDFField='PropertyPurchasePrice')
BEGIN
IF EXISTS(SELECT TOP 1 1 FROM [TransactUDFFieldMapping] WHERE TransactUDFField = 'UDFPropertySalePrice')
BEGIN
UPDATE [TransactUDFFieldMapping] SET TransactUDFField = 'UDFPropertyPurchasePrice' , KeyhouseUDFField='PropertyPurchasePrice' WHERE TransactUDFField = 'UDFPropertySalePrice'
END
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [SystemUserDefinedFields] WHERE FIELDNAME = 'PropertyPurchasePrice')
BEGIN
IF EXISTS(SELECT TOP 1 1 FROM [SystemUserDefinedFields] WHERE FIELDNAME = 'PropertySalePrice')
BEGIN
UPDATE [SystemUserDefinedFields] SET FIELDNAME = 'PropertyPurchasePrice' , PROMPT='Enter property purchase price' WHERE FIELDNAME = 'PropertySalePrice'
END
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Udftemplatesworktypes] WHERE UDUVCODE = 'PropertyPurchasePrice')
BEGIN
IF EXISTS(SELECT TOP 1 1 FROM [Udftemplatesworktypes] WHERE UDUVCODE = 'PropertySalePrice')
BEGIN
UPDATE [Udftemplatesworktypes] SET UDUVCODE = 'PropertyPurchasePrice' WHERE UDUVCODE = 'PropertySalePrice'
END
END
GO
IF OBJECT_ID(N'DefaultPageColumnsByHanlder',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[DefaultPageColumnsByHanlder](
[ID] [int] IDENTITY(1,1) NOT NULL,
[HandlerCode] [varchar](50) NOT NULL,
[PageName] [varchar](50) NOT NULL,
[DefaultColumns] [nvarchar](MAX) NOT NULL
CONSTRAINT pk_DefaultPageColumnsByHanlder_id PRIMARY KEY (ID))
END
GO
-- Alter script to include publish column for UDF fields
IF EXISTS
(SELECT TOP 1 1
FROM sys.objects [SO]
LEFT OUTER JOIN sys.columns [SC]
ON [SC].object_id = [SO].object_id
AND [SC].[name] = 'PUBLISH'
WHERE [SO].[name] = 'SystemUserDefinedFields'
AND [SO].[type] = 'U'
AND [SC].[column_id] IS NULL)
BEGIN
ALTER TABLE [dbo].[SystemUserDefinedFields]
ADD [PUBLISH] BIT NOT NULL
CONSTRAINT [DF_SystemUserDefinedFields_PUBLISH] DEFAULT (0)
END
GO
IF OBJECT_ID(N'PortalNotificationTemplate',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[PortalNotificationTemplate](
[PortalTemplateId] [INT] IDENTITY(1,1) NOT NULL,
[TemplateType] [VARCHAR](100) NOT NULL,
[TemplateSubject] [VARCHAR](100) NULL,
[TemplateBody] [VARCHAR](MAX) NOT NULL
CONSTRAINT PK_PortalNotificationTemplate_Id PRIMARY KEY ([PortalTemplateId]))
END
GO
-- MAIL TEMPLATE FOR INVITE NEW USERS TO CLIENT PORTAL
IF NOT EXISTS (SELECT 1 FROM [dbo].[PortalNotificationTemplate] WHERE [TemplateType] = 'Invite_New_Client_User')
BEGIN
INSERT INTO [dbo].[PortalNotificationTemplate]
([TemplateType],
[TemplateSubject],
[TemplateBody])
VALUES(
'Invite_New_Client_User',
'Client Portal - Account Registration',
'
$TenantFirmName$
Hi,
You have been successfully registered on Client Portal
To complete your registration please use the below link.
You will be able to login to the portal after you enter all the required details.
'
)
END
GO
-- OTP TEMPLATE FOR 2FA IN CLIENT PORTAL
IF NOT EXISTS (SELECT 1 FROM [dbo].[PortalNotificationTemplate] WHERE [TemplateType] = 'SMS_OTP_VERIFICATION')
BEGIN
INSERT INTO [dbo].[PortalNotificationTemplate]
([TemplateType],
[TemplateBody])
VALUES(
'SMS_OTP_VERIFICATION',
'OTP for Client Portal login is %token. This OTP will be valid for %time minutes.'
)
END
GO
IF NOT EXISTS (SELECT 1 FROM [dbo].[PortalNotificationTemplate] WHERE [TemplateType] = 'SMS_OTP_RESET_PASSWORD')
BEGIN
INSERT INTO [dbo].[PortalNotificationTemplate]
([TemplateType],
[TemplateBody])
VALUES(
'SMS_OTP_RESET_PASSWORD',
'The OTP to reset your password for Client Portal is {0}'
)
END
GO