-- EDOC-70 
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'ShortName')
    ALTER TABLE eDocumentTemplate ADD ShortName VARCHAR(200) NULL
GO


-- KOM-390
if not exists (select * from information_schema.columns where table_name = 'XmlIntegrationDataApdater' and column_name = 'XmlNamespaces')
    ALTER TABLE XmlIntegrationDataApdater ADD XmlNamespaces VARCHAR(2000) NULL
GO


-- EDOC-348
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'TemplateRepositoryUrl')
    ALTER TABLE eDocumentTemplate ADD TemplateRepositoryUrl VARCHAR(500) NULL
GO

-- EDOC-425
-- FlowProcess table updates
if not exists (select * from information_schema.columns where table_name = 'FlowProcess' and column_name = 'Caption')
    ALTER TABLE FlowProcess ADD Caption VARCHAR(200) NOT NULL
GO

if exists (select * from information_schema.columns where table_name = 'FlowProcess' and column_name = 'CreatedByConsumerID')
    EXEC sp_rename 'FlowProcess.[CreatedByConsumerID]', 'ConsumerID', 'COLUMN'
GO

if not exists (select * from information_schema.columns where table_name = 'FlowProcess' and column_name = 'DefaultSubmitFlowRuleID')
    ALTER TABLE FlowProcess ADD DefaultSubmitFlowRuleID UNIQUEIDENTIFIER NULL
GO


if not exists (select * from information_schema.columns where table_name = 'FlowProcess' and column_name = 'DefaultSaveFlowRuleID')
    ALTER TABLE FlowProcess ADD DefaultSaveFlowRuleID UNIQUEIDENTIFIER NULL
GO

-- FlowRule table updates
if exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'IsCreator')
    ALTER TABLE FlowRule DROP COLUMN IsCreator
GO

if exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'StartFlowEndpoint')
    EXEC sp_rename 'FlowRule.[StartFlowEndpoint]', 'FromFlowEndpointID', 'COLUMN'
GO

if exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'EndFlowEndpoint')
    EXEC sp_rename 'FlowRule.[EndFlowEndpoint]', 'ToFlowEndpointID', 'COLUMN'
GO

if exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'CreatedByConsumerID')
    ALTER TABLE FlowRule DROP COLUMN CreatedByConsumerID
GO

if not exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'ToFlowStatusID')
    ALTER TABLE FlowRule ADD ToFlowStatusID UNIQUEIDENTIFIER NOT NULL
GO

-- EDOC-378 
if not exists (select * from information_schema.columns where table_name = 'eDocumentInstance' and column_name = 'IsCoreModelDeleted')
    ALTER TABLE eDocumentInstance ADD IsCoreModelDeleted bit NOT NULL DEFAULT (0)
GO

if not exists (select * from information_schema.columns where table_name = 'eDocumentInstance' and column_name = 'CoreModelDeletedOn')
    ALTER TABLE eDocumentInstance ADD CoreModelDeletedOn DATETIME NULL
GO

-- EDOC-474
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'FormID')
    ALTER TABLE eDocumentTemplate ADD FormID VARCHAR(200) NULL
GO

-- KOM-118
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[InstanceWithAbortedOrNoProcess]') AND OBJECTPROPERTY(id, N'IsView') = 1)
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[InstanceWithAbortedOrNoProcess]
AS
SELECT DISTINCT eDocumentInstanceID
FROM         dbo.AbortedProcess
UNION
SELECT DISTINCT eDocumentInstanceID
FROM         dbo.InstanceWithNoExecutedProcessListItem
'
GO

if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'AdditionalModules')
    ALTER TABLE Consumer ADD AdditionalModules TEXT NULL
GO


-- EDOC-348
if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'DestinationSystemID')
    ALTER TABLE Subscription ADD DestinationSystemID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES DestinationSystem(DestinationSystemID)
GO

if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'PropertyBag')
    ALTER TABLE Subscription ADD PropertyBag TEXT NOT NULL DEFAULT ''
GO

UPDATE eDocumentTemplate
SET FormID = ''
WHERE FormID IS NULL
GO

if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'HttpPostUrl')
    ALTER TABLE Consumer ADD HttpPostUrl VARCHAR(1000) NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'PropertyBag')
    ALTER TABLE Consumer ADD PropertyBag TEXT NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'ExecutedProcess' and column_name = 'Text')
    ALTER TABLE ExecutedProcess ADD Text TEXT NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'AbortedProcess' and column_name = 'Description')
    ALTER TABLE AbortedProcess ADD Description TEXT NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'AbortedProcess' and column_name = 'StackTrace')
    ALTER TABLE AbortedProcess ADD StackTrace TEXT NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'AbortedProcess' and column_name = 'Source')
    ALTER TABLE AbortedProcess ADD Source TEXT NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'OppadConsumer' and column_name = 'UrlPushFile')
    ALTER TABLE OppadConsumer ADD UrlPushFile VARCHAR(500) NOT NULL DEFAULT ''
GO

-- add column ActionType.TransferToRemoteServer, defaults to false
if not exists (select * from information_schema.columns where table_name = 'ActionType' and column_name = 'TransferToRemoteServer')
    ALTER TABLE ActionType ADD TransferToRemoteServer BIT NOT NULL DEFAULT 0
GO


-- update all ActionTypes to have the correct TransferToRemoteServer value.
-- xsl 
UPDATE ActionType
SET TransferToRemoteServer = 1
WHERE ActionTypeID = '{60F23851-A942-4E7C-B00A-1E21E1A8899E}'

-- nlp
UPDATE ActionType
SET TransferToRemoteServer = 1
WHERE ActionTypeID = '{93F23851-A942-4E7C-B00A-1E21E1A8899E}'

-- isi
UPDATE ActionType
SET TransferToRemoteServer = 1
WHERE ActionTypeID = '{98F23851-A942-4E7C-B00A-1E21E1A8899E}'

-- oppad
UPDATE ActionType
SET TransferToRemoteServer = 1
WHERE ActionTypeID = '{99F23851-A942-4E7C-B00A-1E21E1A8899E}'

-- noark4ws
UPDATE ActionType
SET TransferToRemoteServer = 1
WHERE ActionTypeID = '{99F23851-A943-4E7C-B00A-1E21E1A8899E}'

-- EDOC-462
if not exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'Caption')
    ALTER TABLE FlowEndpoint ADD Caption VARCHAR(200) NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'FlowProcessID')
    ALTER TABLE FlowEndpoint ADD FlowProcessID uniqueidentifier NOT NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'IsSubmittedStatus')
    ALTER TABLE FlowEndpoint ADD IsSubmittedStatus int NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'IsSavedStatus')
    ALTER TABLE FlowEndpoint ADD IsSavedStatus int NULL
GO

if exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'CreatedByConsumerID')
    ALTER TABLE FlowEndpoint DROP COLUMN CreatedByConsumerID
GO

if exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'Code')
    ALTER TABLE FlowEndpoint DROP COLUMN Code
GO

IF exists (select name FROM sysindexes WHERE name = 'IX_FlowEndpoint_Code')
   DROP INDEX FlowEndpoint.IX_FlowEndpoint_Code
GO

if exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'Code')
    ALTER TABLE FlowEndpoint DROP COLUMN Code
GO

if exists (select * from information_schema.columns where table_name = 'ProcessOnFlowRule' and column_name = 'Sequence')
    ALTER TABLE ProcessOnFlowRule DROP COLUMN Sequence
GO

if exists (select * from information_schema.columns where table_name = 'ProcessOnFlowRule' and column_name = 'Comment')
    ALTER TABLE ProcessOnFlowRule DROP COLUMN Comment
GO

-- ???
if not exists (select * from information_schema.columns where table_name = 'KommuneSkjema' and column_name = 'eDocumentTemplateCategoryID')
    ALTER TABLE KommuneSkjema ADD eDocumentTemplateCategoryID uniqueidentifier NOT NULL
GO

-- EDOC-514
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'FriendlyPDFResponse')
    ALTER TABLE eDocumentTemplate ADD FriendlyPDFResponse bit NOT NULL DEFAULT 0
GO

if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'FriendlyPDFPrint')
    ALTER TABLE eDocumentTemplate ADD FriendlyPDFPrint bit NOT NULL DEFAULT 0
GO

-- EDOC-111
if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'AvailableCultures')
    ALTER TABLE Consumer ADD AvailableCultures VARCHAR(200) NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'AvailableCultures')
    ALTER TABLE Subscription ADD AvailableCultures VARCHAR(200) NOT NULL DEFAULT ''
GO

-- INTBLK-4
if not exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'ManageByADManager')
    ALTER TABLE FlowEndpoint ADD ManageByADManager int NOT NULL DEFAULT 0
GO

if not exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'ExecuteOnFlowToADManager')
    ALTER TABLE FlowRule ADD ExecuteOnFlowToADManager int NOT NULL DEFAULT 0
GO

-- =======================================================================================================
-- EDOC-123
-- need typename on actiontype to be able to instantiate the appropriate type
if not exists (select * from information_schema.columns where table_name = 'ActionType' and column_name = 'TypeName')
    ALTER TABLE ActionType ADD TypeName VARCHAR(300) NOT NULL DEFAULT ''
GO

-- need typename on responseitemtype to be able to instantiate the appropriate type
if not exists (select * from information_schema.columns where table_name = 'ResponseItemType' and column_name = 'TypeName')
    ALTER TABLE ResponseItemType ADD TypeName VARCHAR(300) NOT NULL DEFAULT ''
GO

-- parameterOnActionType is no longer used, relevant columns are moved into table Parameter
if exists (select * from information_schema.columns where table_name = 'ParameterOnActionType')
    DROP TABLE ParameterOnActionType;
GO

-- column DataTypeID was never used
if exists (select * from information_schema.columns where table_name = 'Parameter' and column_name = 'DataTypeID')
    ALTER TABLE Parameter DROP COLUMN DataTypeID
GO

-- moved from ParameterOnActionType
if not exists (select * from information_schema.columns where table_name = 'Parameter' and column_name = 'ActionTypeID')
    ALTER TABLE Parameter ADD ActionTypeID uniqueidentifier NULL;
GO

-- dropping bunch of relationship-tables for ReponseItemTypeParameter, which were never used.
if exists (select * from information_schema.columns where table_name = 'ResponseItemTypeParameterOnResponseItemTypeOnActionType')
    DROP TABLE ResponseItemTypeParameterOnResponseItemTypeOnActionType;
GO

if exists (select * from information_schema.columns where table_name = 'ResponseItemTypeParameterOnResponseItemTypeOnAction')
    DROP TABLE ResponseItemTypeParameterOnResponseItemTypeOnAction;
GO

if exists (select * from information_schema.columns where table_name = 'ResponseItemTypeParameter')
    DROP TABLE ResponseItemTypeParameter;
GO

-- dropping ResponseItem - no longer used (fetched through reflection)
if exists (select * from information_schema.columns where table_name = 'ResponseItem')
    DROP TABLE ResponseItem;
GO


-- dropping ResponseItemTypeOnActionType - no longer used (fetched through reflection)
if exists (select * from information_schema.columns where table_name = 'ResponseItemTypeOnActionType')
    DROP TABLE ResponseItemTypeOnActionType;
GO

-- deleting actiontypes that are no longer supported
-- ftp send
DELETE FROM ActionType WHERE ActionTypeID = '{90F23851-A942-4E7C-B00A-1E21E1A8899E}'

-- custom action
DELETE FROM ActionType WHERE ActionTypeID = '{91f23851-a942-4e7c-b00a-1e21e1a8899e}'

-- ela 
DELETE FROM ActionType WHERE ActionTypeID = '{95f23851-a942-4e7c-b00a-1e21e1a8899e}'

-- xsl transform fork
DELETE FROM ActionType WHERE ActionTypeID = '{97f23851-a942-4e7c-b00a-1e21e1a8899e}'


-- EDOC-637 - ekstern referanse
if not exists (select * from information_schema.columns where table_name = 'eDocumentInstance' and column_name = 'ExternalReferenceID')
    ALTER TABLE eDocumentInstance ADD ExternalReferenceID VARCHAR(200) NOT NULL DEFAULT ''
GO

if not exists (select * from information_schema.columns where table_name = 'AbortedProcess' and column_name = 'ExecutedActions')
    ALTER TABLE AbortedProcess ADD ExecutedActions VARCHAR(1000) NOT NULL DEFAULT ''
GO


-- EDOC-673 - QueryString
if not exists (select * from information_schema.columns where table_name = 'eDocumentInstance' and column_name = 'QueryString')
    ALTER TABLE eDocumentInstance ADD QueryString VARCHAR(1000) NOT NULL DEFAULT ''
GO

-- EDOC-791 (INTBLK-4) - FlowHistory
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FlowHistory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[FlowHistory](
    [FlowHistoryID] [uniqueidentifier] NOT NULL,
    [FlowObjectID] [uniqueidentifier] NOT NULL,
    [eDocumentInstanceID] [uniqueidentifier] NOT NULL,
    [FlowStatusID] [uniqueidentifier] NULL,
    [FlowStatusName] [varchar](200) NULL,
    [FlowEndpointID] [uniqueidentifier] NULL,
    [FlowEndpointName] [varchar](200) NULL,
    [Comment] [text] NULL,
    [ADManager] [text] NULL,
    [LoggedInUsername] [text] NULL,
    [CreatedOn] [datetime] NOT NULL,
 CONSTRAINT [PK_FlowHistory] PRIMARY KEY CLUSTERED 
(
    [FlowHistoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

alter table dbo.ParameterOnAction alter column ParameterValue text
GO

-- EDOC-844 - DSL for processing - need to indicate on the process if it is configured via DSL
if not exists (select * from information_schema.columns where table_name = 'Process' and column_name = 'IsDsl')
    ALTER TABLE Process ADD IsDsl bit NOT NULL DEFAULT (0)
GO

--
if not exists(select * from information_schema.table_constraints where table_name = 'KommuneSkjema' and constraint_name = 'PK_KommuneSkjema')
   ALTER TABLE KommuneSkjema 
   ADD CONSTRAINT [PK_KommuneSkjema] PRIMARY KEY CLUSTERED 
   (
      [KommuneSkjemaID] ASC
   )
GO 

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('eDocumentTemplate') AND name = 'IX_ShortName') 
	BEGIN
		CREATE UNIQUE NONCLUSTERED INDEX [IX_ShortName] ON [dbo].[eDocumentTemplate] 
		(
		   [ShortName] ASC
		)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
	END

-- EDOC-784 - OppadAction må fungere som system
if not exists (select * from information_schema.columns where table_name = 'OppadConsumer' and column_name = 'UrlBarnehage')
    ALTER TABLE OppadConsumer ADD UrlBarnehage varchar(500) NULL 
GO

if not exists (select * from information_schema.columns where table_name = 'OppadConsumer' and column_name = 'UrlBarnepark')
    ALTER TABLE OppadConsumer ADD UrlBarnepark varchar(500) NULL 
GO

if not exists (select * from information_schema.columns where table_name = 'OppadConsumer' and column_name = 'UrlSfo')
    ALTER TABLE OppadConsumer ADD UrlSfo varchar(500) NULL 
GO

-- EDOC-952 - feltrelasjon med choice som output

if not exists (select * from information_schema.columns where table_name = 'KeyValue' and column_name = 'ParentKeyValueID')
    ALTER TABLE KeyValue ADD ParentKeyValueID uniqueidentifier NULL
GO


if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_KeyValue_KeyValue')
	begin
		ALTER TABLE [dbo].[KeyValue]  WITH CHECK ADD  CONSTRAINT [FK_KeyValue_KeyValue] FOREIGN KEY([ParentKeyValueID])
		REFERENCES [dbo].[KeyValue] ([KeyValueID])
	end

ALTER TABLE [dbo].[KeyValue] DROP CONSTRAINT IX_KeyValue_FieldRelationName_SubscriptionID_FieldValue;
GO

ALTER TABLE [dbo].[KeyValue]
ADD CONSTRAINT [IX_KeyValue_FieldRelationName_SubscriptionID_FieldValue] UNIQUE NONCLUSTERED 
(
   [FieldRelationName] ASC,
   [SubscriptionID] ASC,
   [FieldValue] ASC,
   [ParentKeyValueID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

if not exists (select * from information_schema.columns where table_name = 'FieldRelation' and column_name = 'ParentFieldRelationName')
    ALTER TABLE FieldRelation ADD ParentFieldRelationName varchar(200) NULL
GO

if not exists (select * from information_schema.columns where table_name = 'XmlIntegrationDataApdater' and column_name = 'XPathParent')
    ALTER TABLE XmlIntegrationDataApdater ADD XPathParent varchar(200) NULL
GO

if not exists (select * from information_schema.columns where table_name = 'XmlIntegrationDataApdater' and column_name = 'ParentFieldRelationName')
    ALTER TABLE XmlIntegrationDataApdater ADD ParentFieldRelationName varchar(200) NULL
GO

-- EDOC-445 - Dynamisk tittel på skjermdialog - verdi i skjemaet skal kunne gjengis i tittel på skjemaet slik det fremkommer i lister (e.g. oversikt over mellomlagrede innsendte)
if not exists (select * from information_schema.columns where table_name = 'eDocumentInstance' and column_name = 'Name')
    ALTER TABLE eDocumentInstance ADD Name varchar(400) NULL
GO

if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'InstanceName')
    ALTER TABLE eDocumentTemplate ADD InstanceName varchar(400) NULL
GO

ALTER VIEW [dbo].[SavedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.SavedOn, dbo.eDocumentInstance.CreatedOn, 
                      dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, 
                      dbo.eDocumentTemplate.eDocumentTemplateID, dbo.eDocumentInstance.ReferenceNumber, CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + ' ' + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSaved = 1) AND (dbo.eDocumentInstance.IsSubmitted = 0)
ORDER BY dbo.eDocumentInstance.SavedOn DESC
GO

ALTER VIEW [dbo].[SubmittedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.CreatedOn, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedOn, dbo.eDocumentInstance.ReferenceNumber, dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, 
                      dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.IsCoreModelDeleted, dbo.eDocumentInstance.CoreModelDeletedOn, 
                      dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, dbo.eDocumentTemplate.eDocumentTemplateID, 
                      CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + ' ' + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSubmitted = 1) AND (dbo.eDocumentInstance.IsDeleted = 0)
ORDER BY dbo.eDocumentInstance.SubmittedOn DESC
GO


-- KOM-861 - Feilmelding i rapportmodul når det tar for lang tid å generere rapporten
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('eDocumentInstance') AND name = 'IX_eDocumentInstance_SubmittedOn') 
	BEGIN
		CREATE NONCLUSTERED INDEX [IX_eDocumentInstance_SubmittedOn] ON [dbo].[eDocumentInstance] 
		(
		   [SubmittedOn] ASC
		)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
	END

-- EDOC-685 - Sikring av integritet - digest av søknadsdata
if not exists (select * from information_schema.columns where table_name = 'eDocumentInstance' and column_name = 'Hash')
    ALTER TABLE eDocumentInstance ADD Hash varchar(400) NULL
GO

-- EDOC-1038 - Ytelsesforbedring på rapporter - indeks på ExecutedProcess.eDocumentInstanceID
if not exists (select * from sysindexes where name = 'IX_ExecutedProcess_InstanceID')
   CREATE NONCLUSTERED INDEX [IX_ExecutedProcess_InstanceID] ON [ExecutedProcess] 
   (
      [eDocumentInstanceID] ASC
   )WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

-- KOM-929 Må ta høyde for konsumenter med uvanlige brukernavn
if not exists (select * from sysindexes where name = 'IX_Consumer_Username')
	CREATE UNIQUE NONCLUSTERED INDEX [IX_Consumer_Username] ON [dbo].[Consumer] 
	(
		[Username] ASC
	)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

-- EDOC-1118 - Listen over mangler skal ikke vise søkander innsendt de siste minuttene


ALTER VIEW [dbo].[InstanceWithNoExecutedProcessListItem]
AS
SELECT     dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.SubmittedOn AS eDocumentInstanceSubmittedOn, 
                      dbo.eDocumentInstance.SavedOn AS eDocumentInstanceSavedOn, dbo.eDocumentInstance.CreatedOn AS eDocumentInstanceCreatedOn, 
                      dbo.eDocumentInstance.SubmittedByConsumerID AS eDocumentInstanceSubmittedByConsumerID, 
                      dbo.eDocumentInstance.ReferenceNumber AS eDocumentInstanceReferenceNumber, dbo.Consumer.Username AS ConsumerUsername, 
                      dbo.Consumer.Name AS ConsumerName, dbo.eDocumentInstance.eDocumentTemplateVersionID, dbo.eDocumentTemplateVersion.eDocumentTemplateID, 
                      dbo.eDocumentTemplateVersion.Version AS eDocumentTemplateVersionVersion, dbo.eDocumentTemplate.Name AS eDocumentTemplateName, 
                      dbo.eDocumentTemplate.Comment AS eDocumentTemplateComment
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID LEFT OUTER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.ExecutedProcessListItem ON dbo.eDocumentInstance.eDocumentInstanceID = dbo.ExecutedProcessListItem.eDocumentInstanceID LEFT OUTER JOIN
                      dbo.AbortedProcessListItem ON dbo.eDocumentInstance.eDocumentInstanceID = dbo.AbortedProcessListItem.eDocumentInstanceID
WHERE     (dbo.AbortedProcessListItem.AbortedProcessID IS NULL) AND (dbo.ExecutedProcessListItem.ExecutedProcessID IS NULL) AND 
                      (dbo.eDocumentInstance.IsSubmitted = 1) AND (dbo.eDocumentInstance.SubmittedOn BETWEEN GETDATE() - 365 AND DATEADD(mi, - 15, GETDATE()))
GO

if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'OperationModel')
	ALTER TABLE Consumer ADD OperationModel int NOT NULL DEFAULT 0
GO

if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'OperationModel')
	ALTER TABLE Subscription ADD OperationModel int NULL
GO

-- EDOC-1119 - Vis grunnen til at søknad havner i mangel lista

ALTER VIEW [dbo].[InstanceWithNoExecutedProcessListItem]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.SubmittedOn AS eDocumentInstanceSubmittedOn, 
                      dbo.eDocumentInstance.SavedOn AS eDocumentInstanceSavedOn, dbo.eDocumentInstance.CreatedOn AS eDocumentInstanceCreatedOn, 
                      dbo.eDocumentInstance.SubmittedByConsumerID AS eDocumentInstanceSubmittedByConsumerID, 
                      dbo.eDocumentInstance.ReferenceNumber AS eDocumentInstanceReferenceNumber, dbo.Consumer.Username AS ConsumerUsername, 
                      dbo.Consumer.Name AS ConsumerName, dbo.eDocumentInstance.eDocumentTemplateVersionID, dbo.eDocumentTemplateVersion.eDocumentTemplateID, 
                      dbo.eDocumentTemplateVersion.Version AS eDocumentTemplateVersionVersion, dbo.eDocumentTemplate.Name AS eDocumentTemplateName, 
                      dbo.eDocumentTemplate.Comment AS eDocumentTemplateComment, dbo.Subscription.SubscriptionID, dbo.Subscription.ToDate, 
                      dbo.Subscription.DestinationSystemID, dbo.DestinationSystem.Name AS DestinationSystemName, dbo.ProcessOnSubscription.ProcessID, 
                      dbo.Process.Name AS ProcessName, dbo.ProcessOnEDocumentTemplate.ProcessOnEDocumentTemplateID, dbo.eDocumentTemplate.UploadedByConsumerID
FROM         dbo.Process RIGHT OUTER JOIN
                      dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID LEFT OUTER JOIN
                      dbo.ProcessOnEDocumentTemplate ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.ProcessOnEDocumentTemplate.eDocumentTemplateID LEFT OUTER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.ExecutedProcessListItem ON dbo.eDocumentInstance.eDocumentInstanceID = dbo.ExecutedProcessListItem.eDocumentInstanceID LEFT OUTER JOIN
                      dbo.AbortedProcessListItem ON dbo.eDocumentInstance.eDocumentInstanceID = dbo.AbortedProcessListItem.eDocumentInstanceID LEFT OUTER JOIN
                      dbo.DestinationSystem RIGHT OUTER JOIN
                      dbo.Subscription ON dbo.DestinationSystem.DestinationSystemID = dbo.Subscription.DestinationSystemID AND 
                      dbo.DestinationSystem.DestinationSystemID = dbo.Subscription.DestinationSystemID ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID AND 
                      dbo.Consumer.ConsumerID = dbo.Subscription.SubscribedByConsumerID LEFT OUTER JOIN
                      dbo.ProcessOnSubscription ON dbo.Subscription.SubscriptionID = dbo.ProcessOnSubscription.SubscriptionID ON 
                      dbo.Process.ProcessID = dbo.ProcessOnSubscription.ProcessID
WHERE     (dbo.AbortedProcessListItem.AbortedProcessID IS NULL) AND (dbo.ExecutedProcessListItem.ExecutedProcessID IS NULL) AND 
                      (dbo.eDocumentInstance.IsSubmitted = 1) AND (dbo.eDocumentInstance.SubmittedOn BETWEEN GETDATE() - 365 AND DATEADD(mi, - 15, GETDATE()))
ORDER BY eDocumentInstanceSubmittedOn DESC
GO

-- KOM-629 Bedre håndtering av kunder med driftsmodell B og C i ASP løsning
ALTER VIEW [dbo].[SubscribedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT dbo.Subscription.SubscribedByConsumerID, dbo.Subscription.eDocumentTemplateID, dbo.eDocumentTemplate.Name, 
                      dbo.eDocumentTemplate.CreatedOn, dbo.eDocumentTemplate.Comment, dbo.Subscription.SignMode, dbo.Subscription.SignObject, 
                      dbo.Subscription.DokumentbankenBlankettID, dbo.Subscription.SubscriptionID, dbo.Subscription.AvailableCultures, 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID, 
                      dbo.eDocumentTemplateCategory.Name AS eDocumentTemplateCategoryName, 
                      dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID AS HasFieldRelation, dbo.eDocumentTemplate.TemplateRepositoryUrl, 
                      dbo.eDocumentTemplate.ShortName, dbo.Subscription.FromDate, dbo.Subscription.ToDate, 
                      dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, dbo.eDocumentTemplate.FriendlyPDFResponse, 
                      dbo.eDocumentTemplate.FriendlyPDFPrint, dbo.eDocumentTemplate.FormID, dbo.Subscription.OperationModel
FROM         dbo.eDocumentTemplate INNER JOIN
                      dbo.Subscription ON dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateCategory ON 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID
                       LEFT OUTER JOIN
                      dbo.eDocumentTemplateWithFieldRelation ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID
WHERE     (dbo.eDocumentTemplate.IsPublished = 1)
ORDER BY dbo.eDocumentTemplate.Name
GO


-- EDOC-931 - inaktivere skjema
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'IsDeleted')
    ALTER TABLE eDocumentTemplate ADD IsDeleted bit NOT NULL DEFAULT (0)
GO

ALTER VIEW [dbo].[eDocumentTemplateListItem]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentTemplate.eDocumentTemplateID, dbo.eDocumentTemplate.CreatedOn, 
                      dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, dbo.eDocumentTemplateVersion.Version, dbo.eDocumentTemplate.UploadedByConsumerID, 
                      dbo.eDocumentTemplate.IsPrefillEnabled, dbo.eDocumentTemplate.Comment, dbo.eDocumentTemplate.SignMode, dbo.eDocumentTemplate.SignObject, 
                      dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.ShortName, dbo.eDocumentTemplate.IsPublished, dbo.eDocumentTemplate.ForwardValidationMode, 
                      dbo.eDocumentTemplate.BackwardValidationMode, dbo.Consumer.Name AS UploadedByConsumerName, 
                      dbo.eDocumentTemplate.Name + ' [' + dbo.eDocumentTemplate.Comment + ']' AS NameWithComment, ISNULL(dbo.eDocumentTemplate.ShortName, '') 
                      + ' - ' + dbo.eDocumentTemplate.Name AS ShortNameWithName, dbo.eDocumentTemplate.IsDeleted
FROM         dbo.eDocumentTemplate LEFT OUTER JOIN
                      dbo.Consumer ON dbo.eDocumentTemplate.UploadedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID
ORDER BY dbo.eDocumentTemplate.Name
GO

-- EDOC-1136 - Admin - Oversikten over prosesser skal tydelig vise prosesser som er feilkonfigurert (i.e. ingen handlinger) og prosesser som ikke er i bruk
ALTER VIEW [dbo].[ProcessListItem]
AS
SELECT     TOP (100) PERCENT dbo.Process.Name, dbo.Process.ProcessID, dbo.Process.CreatedOn, dbo.Process.Comment, dbo.Process.ConsumerID, 
                      COUNT(DISTINCT dbo.ProcessAction.ProcessActionID) AS ActionCount, COUNT(DISTINCT dbo.ProcessOnSubscription.ProcessOnSubscriptionID) AS SubscriptionCount, 
                      COUNT(DISTINCT dbo.ProcessOnFlowRule.ProcessOnFlowRuleID) AS FlowRuleCount, COUNT(DISTINCT dbo.ProcessOnDestinationSystem.DestinationSystemID) 
                      AS DestinationSystemCount, COUNT(DISTINCT dbo.ProcessOnEDocumentTemplate.ProcessOnEDocumentTemplateID) AS TemplateCount, 
                      COUNT(DISTINCT dbo.ProcessOnEDocumentTemplate.ProcessOnEDocumentTemplateID) + COUNT(DISTINCT dbo.ProcessOnSubscription.ProcessOnSubscriptionID) 
                      + COUNT(DISTINCT dbo.ProcessOnFlowRule.ProcessOnFlowRuleID) + COUNT(DISTINCT dbo.ProcessOnDestinationSystem.DestinationSystemID) 
                      AS TotalUsageCount
FROM         dbo.Process LEFT OUTER JOIN
                      dbo.ProcessOnDestinationSystem ON dbo.Process.ProcessID = dbo.ProcessOnDestinationSystem.ProcessID LEFT OUTER JOIN
                      dbo.ProcessOnEDocumentTemplate ON dbo.Process.ProcessID = dbo.ProcessOnEDocumentTemplate.ProcessID LEFT OUTER JOIN
                      dbo.ProcessOnFlowRule ON dbo.Process.ProcessID = dbo.ProcessOnFlowRule.ProcessID LEFT OUTER JOIN
                      dbo.ProcessOnSubscription ON dbo.Process.ProcessID = dbo.ProcessOnSubscription.ProcessID LEFT OUTER JOIN
                      dbo.ProcessAction ON dbo.Process.ProcessID = dbo.ProcessAction.ProcessID
GROUP BY dbo.Process.Name, dbo.Process.ProcessID, dbo.Process.CreatedOn, dbo.Process.Comment, dbo.Process.ConsumerID
ORDER BY dbo.Process.Name

GO

-- EDOC-1137 - Det skal være mulig å slette prosesser uten at det gjøres en cascade delete til ExecutedProcess
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ExecutedProcess_Process]') AND parent_object_id = OBJECT_ID(N'[dbo].[ExecutedProcess]'))
ALTER TABLE [dbo].[ExecutedProcess] DROP CONSTRAINT [FK_ExecutedProcess_Process]
GO


ALTER TABLE [dbo].[ExecutedProcess] ALTER COLUMN ProcessID uniqueidentifier NULL
GO

if not exists (select * from information_schema.columns where table_name = 'ExecutedProcess' and column_name = 'ProcessName')
	ALTER TABLE [dbo].[ExecutedProcess] ADD ProcessName varchar(200) NULL
GO

ALTER TABLE [dbo].[ExecutedProcess]  WITH NOCHECK ADD  CONSTRAINT [FK_ExecutedProcess_Process] FOREIGN KEY([ProcessID])
REFERENCES [dbo].[Process] ([ProcessID])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[ExecutedProcess] CHECK CONSTRAINT [FK_ExecutedProcess_Process]
GO

ALTER VIEW [dbo].[ExecutedProcessListItem]
AS
SELECT     dbo.ExecutedProcess.ExecutedProcessID, dbo.ExecutedProcess.ExecutionRequestedByConsumerID, dbo.ExecutedProcess.ProcessOwnerConsumerID, 
                      dbo.ExecutedProcess.eDocumentInstanceID, dbo.ExecutedProcess.ProcessID, dbo.ExecutedProcess.ResponseItemLoadingEndedOn, 
                      dbo.ExecutedProcess.ResponseItemLoadingStartedOn, dbo.ExecutedProcess.ProcessStartedOn, dbo.ExecutedProcess.ProcessEndedOn, 
                      dbo.ExecutedProcess.ActionExecutionStartedOn, dbo.ExecutedProcess.ActionExecutionEndedOn, CASE WHEN (ExecutedProcess.ProcessID IS NOT NULL) 
                      THEN Process.Name ELSE ExecutedProcess.ProcessName END AS ProcessName, dbo.Process.Comment AS ProcessComment, 
                      dbo.Consumer.Username AS ConsumerUsername, dbo.Consumer.Name AS ConsumerName, dbo.eDocumentInstance.eDocumentTemplateVersionID, 
                      dbo.eDocumentTemplateVersion.eDocumentTemplateID, dbo.eDocumentTemplate.Name AS eDocumentTemplateName, 
                      dbo.eDocumentInstance.CreatedOn AS eDocumentInstanceCreatedOn, dbo.eDocumentInstance.SavedOn AS eDocumentInstanceSavedOn, 
                      dbo.eDocumentInstance.SubmittedOn AS eDocumentInstanceSubmittedOn, dbo.eDocumentInstance.ReferenceNumber AS eDocumentInstanceReferenceNumber, 
                      dbo.eDocumentTemplateVersion.Version AS eDocumentTemplateVersionVersion, dbo.ExecutedProcess.Text
FROM         dbo.eDocumentTemplateVersion INNER JOIN
                      dbo.eDocumentInstance ON dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID = dbo.eDocumentInstance.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID RIGHT OUTER JOIN
                      dbo.ExecutedProcess ON dbo.eDocumentInstance.eDocumentInstanceID = dbo.ExecutedProcess.eDocumentInstanceID LEFT OUTER JOIN
                      dbo.Consumer ON dbo.ExecutedProcess.ExecutionRequestedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.Process ON dbo.ExecutedProcess.ProcessID = dbo.Process.ProcessID
GO

-- EDOC-321 - Støtte for å angi at et skjema krever et gitt sikkerhetsnivå
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'SecurityLevel')
    ALTER TABLE eDocumentTemplate ADD SecurityLevel int NULL 
GO

if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'SecurityLevel')
    ALTER TABLE Subscription ADD SecurityLevel int NULL 
GO

ALTER VIEW [dbo].[SubscriptionListItem]
AS
SELECT     TOP (100) PERCENT dbo.Subscription.SubscriptionID, dbo.Subscription.SubscribedByConsumerID, dbo.Consumer.Name AS SubscribedByConsumerName, 
                      dbo.Consumer.Username AS SubscribedByConsumerUserName, dbo.Subscription.eDocumentTemplateID, 
                      dbo.eDocumentTemplate.Name AS eDocumentTemplateName, dbo.eDocumentTemplate.ShortName AS eDocumentTemplateShortName, 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID, 
                      dbo.eDocumentTemplateCategory.Name AS eDocumentTemplateCategoryName, dbo.eDocumentTemplateCategory.Caption AS eDocumentTemplateCategoryCaption, 
                      dbo.Subscription.DokumentbankenBlankettID, dbo.DokumentbankenBlankett.Name AS DokumentbankenBlankettName, dbo.Subscription.FromDate, 
                      dbo.Subscription.ToDate, dbo.Subscription.Comment, dbo.Subscription.CreatedOn, dbo.Subscription.IsActive, dbo.Subscription.ConsumerID, 
                      dbo.Subscription.ProcessMode, dbo.Subscription.SignMode, dbo.Subscription.SignObject, dbo.Subscription.ForwardValidationMode, 
                      dbo.Subscription.BackwardValidationMode, dbo.eDocumentTemplate.FormID, dbo.Subscription.SecurityLevel AS SecurityLevelSubscription, 
                      dbo.eDocumentTemplate.SecurityLevel AS SecurityLevelTemplate
FROM         dbo.Subscription INNER JOIN
                      dbo.eDocumentTemplate ON dbo.Subscription.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID LEFT OUTER JOIN
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory LEFT OUTER JOIN
                      dbo.eDocumentTemplateCategory ON 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateID LEFT OUTER JOIN
                      dbo.Consumer ON dbo.Subscription.SubscribedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.DokumentbankenBlankett ON dbo.Subscription.DokumentbankenBlankettID = dbo.DokumentbankenBlankett.DokumentbankenBlankettID
ORDER BY eDocumentTemplateName
GO

ALTER VIEW [dbo].[eDocumentTemplateListItem]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentTemplate.eDocumentTemplateID, dbo.eDocumentTemplate.CreatedOn, 
                      dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, dbo.eDocumentTemplateVersion.Version, dbo.eDocumentTemplate.UploadedByConsumerID, 
                      dbo.eDocumentTemplate.IsPrefillEnabled, dbo.eDocumentTemplate.Comment, dbo.eDocumentTemplate.SignMode, dbo.eDocumentTemplate.SignObject, 
                      dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.ShortName, dbo.eDocumentTemplate.IsPublished, dbo.eDocumentTemplate.ForwardValidationMode, 
                      dbo.eDocumentTemplate.BackwardValidationMode, dbo.Consumer.Name AS UploadedByConsumerName, 
                      dbo.eDocumentTemplate.Name + ' [' + dbo.eDocumentTemplate.Comment + ']' AS NameWithComment, ISNULL(dbo.eDocumentTemplate.ShortName, '') 
                      + ' - ' + dbo.eDocumentTemplate.Name AS ShortNameWithName, dbo.eDocumentTemplate.IsDeleted, dbo.eDocumentTemplate.SecurityLevel
FROM         dbo.eDocumentTemplate LEFT OUTER JOIN
                      dbo.Consumer ON dbo.eDocumentTemplate.UploadedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID
ORDER BY dbo.eDocumentTemplate.Name
GO

ALTER VIEW [dbo].[SubscribedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT dbo.Subscription.SubscribedByConsumerID, dbo.Subscription.eDocumentTemplateID, dbo.eDocumentTemplate.Name, 
                      dbo.eDocumentTemplate.CreatedOn, dbo.eDocumentTemplate.Comment, dbo.Subscription.SignMode, dbo.Subscription.SignObject, 
                      dbo.Subscription.DokumentbankenBlankettID, dbo.Subscription.SubscriptionID, dbo.Subscription.AvailableCultures, 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID, 
                      dbo.eDocumentTemplateCategory.Name AS eDocumentTemplateCategoryName, 
                      dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID AS HasFieldRelation, dbo.eDocumentTemplate.TemplateRepositoryUrl, 
                      dbo.eDocumentTemplate.ShortName, dbo.Subscription.FromDate, dbo.Subscription.ToDate, dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, 
                      dbo.eDocumentTemplate.FriendlyPDFResponse, dbo.eDocumentTemplate.FriendlyPDFPrint, dbo.eDocumentTemplate.FormID, dbo.Subscription.OperationModel, 
                      dbo.Subscription.SecurityLevel AS SecurityLevelSubscription, dbo.eDocumentTemplate.SecurityLevel AS SecurityLevelTemplate
FROM         dbo.eDocumentTemplate INNER JOIN
                      dbo.Subscription ON dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateCategory ON 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID LEFT OUTER
                       JOIN
                      dbo.eDocumentTemplateWithFieldRelation ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID
WHERE     (dbo.eDocumentTemplate.IsPublished = 1)
ORDER BY dbo.eDocumentTemplate.Name
GO

ALTER VIEW [dbo].[PublishedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT eDocumentTemplateID, Name, CreatedOn, Comment, SignMode, SignObject, UploadedByConsumerID, CurrentEDocumentTemplateVersionID, 
                      FormID, ShortName, SecurityLevel
FROM         dbo.eDocumentTemplate
WHERE     (IsPublished = 1) AND (TemplateRepositoryUrl IS NULL)
ORDER BY Name
GO

-- EDOC-1111 - Ved opplasting av ny skjermdialogversjon: gjør det mulig å oppgradere mellomlagrede instanser til siste versjon
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'UpdateSavedToCurrentVersion')
    ALTER TABLE eDocumentTemplate ADD UpdateSavedToCurrentVersion bit NOT NULL DEFAULT (0)
GO

-- EDOC-1041 - kryptering

-- ny tabell encryption key
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'EncryptionKey')
	BEGIN
		CREATE TABLE [dbo].[EncryptionKey](
			[EncryptionKeyId] [uniqueidentifier] NOT NULL,
			[KeyName] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			[ConsumerId] [uniqueidentifier] NOT NULL,
			[CreatedOn] [datetime] NOT NULL,
			[FromUrl] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			[PublicKey] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			[KeyType] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			[Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		 CONSTRAINT [PK_EncryptionKey] PRIMARY KEY CLUSTERED 
		(
			[EncryptionKeyId] ASC
		)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
		) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
	END

-- kobling mellom encryptionkey og consumer (key belongs to consumer)
if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_EncryptionKey_Consumer')
	begin
		ALTER TABLE [dbo].[EncryptionKey]  WITH CHECK ADD  CONSTRAINT [FK_EncryptionKey_Consumer] FOREIGN KEY([ConsumerId])
		REFERENCES [dbo].[Consumer] ([ConsumerID])
		ON DELETE CASCADE
		ALTER TABLE [dbo].[EncryptionKey] CHECK CONSTRAINT [FK_EncryptionKey_Consumer]
	end

-- nye kolonner i consumer/subscription for encryption mode og key
if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'EncryptionMode')
    ALTER TABLE Consumer ADD EncryptionMode [int] NULL
GO

if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'EncryptionKeyId')
    ALTER TABLE Consumer ADD EncryptionKeyId [uniqueidentifier] NULL
GO

if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'EncryptionMode')
    ALTER TABLE Subscription ADD EncryptionMode [int] NULL
GO

-- kobling mellom consumer og encryption key (current key)
if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_Consumer_EncryptionKey')
	begin
		ALTER TABLE [dbo].[Consumer]  WITH CHECK ADD  CONSTRAINT [FK_Consumer_EncryptionKey] FOREIGN KEY([EncryptionKeyId])
			REFERENCES [dbo].[EncryptionKey] ([EncryptionKeyId])
		ALTER TABLE [dbo].[Consumer] CHECK CONSTRAINT [FK_Consumer_EncryptionKey]
	end	

-- ny kolonne i instance
if not exists (select * from information_schema.columns where table_name = 'eDocumentInstance' and column_name = 'EncryptionKeyId')
    ALTER TABLE eDocumentInstance ADD EncryptionKeyId [uniqueidentifier] NULL
GO

-- kobling mellom instance og encryptionkey (instance encrypted with key)
if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_eDocumentInstance_EncryptionKey')
	begin
		ALTER TABLE [dbo].[eDocumentInstance]  WITH CHECK ADD  CONSTRAINT [FK_eDocumentInstance_EncryptionKey] FOREIGN KEY([EncryptionKeyId])
			REFERENCES [dbo].[EncryptionKey] ([EncryptionKeyId])
		ALTER TABLE [dbo].[eDocumentInstance] CHECK CONSTRAINT [FK_eDocumentInstance_EncryptionKey]
	end	

-- nytt view som gir oversikt over aktive keys, antall instanser av hver
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'InstanceThatNeedsEncryptionKey')
    DROP VIEW InstanceThatNeedsEncryptionKey
GO

CREATE VIEW [dbo].[InstanceThatNeedsEncryptionKey]
AS
	SELECT DISTINCT eDocumentInstanceID
	FROM         dbo.eDocumentInstance
	WHERE     (IsSaved = 1) AND (IsSubmitted = 0) AND (EncryptionKeyId IS NOT NULL)
	UNION
	SELECT DISTINCT eDocumentInstanceID
	FROM         dbo.InstanceWithAbortedOrNoProcess
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'EncryptionKey_Active')
    DROP VIEW EncryptionKey_Active
GO

CREATE VIEW [dbo].[EncryptionKey_Active]
AS
	SELECT     TOP (100) PERCENT dbo.Consumer.Username, dbo.Consumer.Name, dbo.EncryptionKey.KeyName, dbo.EncryptionKey.CreatedOn, dbo.EncryptionKey.FromUrl, 
						  dbo.eDocumentInstance.EncryptionKeyId
	FROM         dbo.InstanceThatNeedsEncryptionKey INNER JOIN
						  dbo.eDocumentInstance ON dbo.InstanceThatNeedsEncryptionKey.eDocumentInstanceID = dbo.eDocumentInstance.eDocumentInstanceID INNER JOIN
						  dbo.EncryptionKey ON dbo.eDocumentInstance.EncryptionKeyId = dbo.EncryptionKey.EncryptionKeyId INNER JOIN
						  dbo.Consumer ON dbo.EncryptionKey.ConsumerId = dbo.Consumer.ConsumerID
	GROUP BY dbo.EncryptionKey.KeyName, dbo.EncryptionKey.CreatedOn, dbo.EncryptionKey.FromUrl, dbo.eDocumentInstance.EncryptionKeyId, dbo.Consumer.Username, 
						  dbo.Consumer.Name
	HAVING      (dbo.eDocumentInstance.EncryptionKeyId IS NOT NULL)
	ORDER BY dbo.Consumer.Username, dbo.EncryptionKey.CreatedOn DESC
GO

---------------------------------------------------------------------------------------------------------------
-- EDOC-1271 - Fjern unødvendige BLL klasser
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ElaConsumer')
    DROP TABLE ElaConsumer
GO

-----------------------------------------
-- integration data adapter stuff
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationDataAdapterParameterOnIntegrationDataAdapterType')
    DROP TABLE IntegrationDataAdapterParameterOnIntegrationDataAdapterType
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationDataAdapterParameterOnIntegrationDataAdapter')
    DROP TABLE IntegrationDataAdapterParameterOnIntegrationDataAdapter
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationDataAdapterParameterOnIntegrationDataAdapter')
    DROP TABLE IntegrationDataAdapterParameterOnIntegrationDataAdapter
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationDataAdapterOnIntegrationField')
    DROP TABLE IntegrationDataAdapterOnIntegrationField
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationDataAdapterArgument')
    DROP TABLE IntegrationDataAdapterArgument
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationDataAdapterParameter')
    DROP TABLE IntegrationDataAdapterParameter
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationDataAdapter')
    DROP TABLE IntegrationDataAdapter
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationDataAdapterType')
    DROP TABLE IntegrationDataAdapterType
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'IntegrationDataAdapterListItem')
    DROP VIEW IntegrationDataAdapterListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'IntegrationDataAdapterOnIntegrationFieldListItem')
    DROP VIEW IntegrationDataAdapterOnIntegrationFieldListItem
GO

-----------------------------------------
-- integration field
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationFieldValueOnIntegrationField')
    DROP TABLE IntegrationFieldValueOnIntegrationField
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationFieldValue')
    DROP TABLE IntegrationFieldValue
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationField')
    DROP TABLE IntegrationField
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IntegrationFieldType')
    DROP TABLE IntegrationFieldType
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'IntegrationFieldListItem')
    DROP VIEW IntegrationFieldListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'IntegrationFieldValueListItem')
    DROP VIEW IntegrationFieldValueListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'IntegrationFieldValueOnIntegrationFieldListItem')
    DROP VIEW IntegrationFieldValueOnIntegrationFieldListItem
GO

-----------------------------------------
-- condition stuff
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ConditionParameterOnConditionType')
    DROP TABLE ConditionParameterOnConditionType
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ConditionOnConditionCollection')
    DROP TABLE ConditionOnConditionCollection
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ConditionParameterOnCondition')
    DROP TABLE ConditionParameterOnCondition
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ConditionParameter')
    DROP TABLE ConditionParameter
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ConditionCollection')
    DROP TABLE ConditionCollection
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Condition')
    DROP TABLE Condition
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ConditionType')
    DROP TABLE ConditionType
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ConditionListItem')
    DROP VIEW ConditionListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ConditionOnConditionCollectionListItem')
    DROP VIEW ConditionOnConditionCollectionListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ConditionParameterOnConditionCollectionListItem')
    DROP VIEW ConditionParameterOnConditionCollectionListItem
GO
-------------------------------------------------
-- user interaction stuff, viewer session, section visit
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserInteractionException')
    DROP TABLE UserInteractionException
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SectionVisit')
    DROP TABLE SectionVisit
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserInteraction')
    DROP TABLE UserInteraction
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserInteractionType')
    DROP TABLE UserInteractionType
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserInteractionItem')
    DROP TABLE UserInteractionItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'eDocumentViewerSession')
    DROP TABLE eDocumentViewerSession
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'UserInteractionExceptionListItem')
    DROP VIEW UserInteractionExceptionListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'UserInteractionListItem')
    DROP VIEW UserInteractionListItem
GO

-------------------------------------------------
-- viewer request
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ViewerRequestOnEntity')
    DROP TABLE ViewerRequestOnEntity
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ViewerRequest')
    DROP TABLE ViewerRequest
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EntityType')
    DROP TABLE EntityType
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewerRequestConsumerCountListItem')
    DROP VIEW ViewerRequestConsumerCountListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewerRequestSinceLast24HoursListItem')
    DROP VIEW ViewerRequestSinceLast24HoursListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewerRequestSinceLast60MinutesListItem')
    DROP VIEW ViewerRequestSinceLast60MinutesListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewerRequestTodayListItem')
    DROP VIEW ViewerRequestTodayListItem
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewerRequestUrlRefererCountListItem')
    DROP VIEW ViewerRequestUrlRefererCountListItem
GO

-------------------------------------------------
-- event log item 
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventLogItemOnEntity')
    DROP TABLE EventLogItemOnEntity
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventLogItem')
    DROP TABLE EventLogItem
GO

-------------------------------------------------
-- field
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Field')
    DROP TABLE Field
GO

-------------------------------------------------
-- query
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'QueryFieldOptionOnQueryFieldAnswer')
    DROP TABLE QueryFieldOptionOnQueryFieldAnswer
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'QueryFieldOption')
    DROP TABLE QueryFieldOption
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'QueryFieldAnswer')
    DROP TABLE QueryFieldAnswer
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'QueryField')
    DROP TABLE QueryField
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Query')
    DROP TABLE Query
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'QueryFieldType')
    DROP TABLE QueryFieldType
GO

-------------------------------------------------
-- validation error
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ValidationError')
    DROP TABLE ValidationError
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ValidationErrorType')
    DROP TABLE ValidationErrorType
GO

---------------------------------------------------------------------------------------------------------------
-- EDOC-1289 - CoreService: Saved/Submitted instances skal ha egen property for InstanceNamePostfix

ALTER VIEW [dbo].[SavedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.SavedOn, dbo.eDocumentInstance.CreatedOn, 
                      dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, 
                      dbo.eDocumentTemplate.eDocumentTemplateID, dbo.eDocumentInstance.ReferenceNumber, CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + ' ' + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName, 
                      dbo.eDocumentInstance.Name AS InstanceNamePostfix
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSaved = 1) AND (dbo.eDocumentInstance.IsSubmitted = 0)
ORDER BY dbo.eDocumentInstance.SavedOn DESC
GO

ALTER VIEW [dbo].[SubmittedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.CreatedOn, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedOn, dbo.eDocumentInstance.ReferenceNumber, dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, 
                      dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.IsCoreModelDeleted, dbo.eDocumentInstance.CoreModelDeletedOn, 
                      dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, dbo.eDocumentTemplate.eDocumentTemplateID, 
                      CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + ' ' + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName, 
                      dbo.eDocumentInstance.Name AS InstanceNamePostfix
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSubmitted = 1) AND (dbo.eDocumentInstance.IsDeleted = 0)
ORDER BY dbo.eDocumentInstance.SubmittedOn DESC
GO

ALTER VIEW [dbo].[SavedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.SavedOn, dbo.eDocumentInstance.CreatedOn, 
                      dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, 
                      dbo.eDocumentTemplate.eDocumentTemplateID, dbo.eDocumentInstance.ReferenceNumber, CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + ' ' + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName, 
                      dbo.eDocumentInstance.Name AS InstanceNamePostfix, dbo.eDocumentTemplate.ShortName
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSaved = 1) AND (dbo.eDocumentInstance.IsSubmitted = 0)
ORDER BY dbo.eDocumentInstance.SavedOn DESC
GO

ALTER VIEW [dbo].[SubmittedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.CreatedOn, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedOn, dbo.eDocumentInstance.ReferenceNumber, dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, 
                      dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.IsCoreModelDeleted, dbo.eDocumentInstance.CoreModelDeletedOn, 
                      dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, dbo.eDocumentTemplate.eDocumentTemplateID, 
                      CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + ' ' + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName, 
                      dbo.eDocumentInstance.Name AS InstanceNamePostfix, dbo.eDocumentTemplate.ShortName
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSubmitted = 1) AND (dbo.eDocumentInstance.IsDeleted = 0)
ORDER BY dbo.eDocumentInstance.SubmittedOn DESC
GO

---------------------------------------------------------------------------------------------------------------
-- EDOC-1305 - Det skal ikke legges på et automatisk mellomrom mellom skjemanavn og instancename

ALTER VIEW [dbo].[SubmittedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.CreatedOn, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedOn, dbo.eDocumentInstance.ReferenceNumber, dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, 
                      dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.IsCoreModelDeleted, dbo.eDocumentInstance.CoreModelDeletedOn, 
                      dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, dbo.eDocumentTemplate.eDocumentTemplateID, 
                      CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName, 
                      dbo.eDocumentInstance.Name AS InstanceNamePostfix, dbo.eDocumentTemplate.ShortName
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSubmitted = 1) AND (dbo.eDocumentInstance.IsDeleted = 0)
ORDER BY dbo.eDocumentInstance.SubmittedOn DESC
GO

ALTER VIEW [dbo].[SavedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.SavedOn, dbo.eDocumentInstance.CreatedOn, 
                      dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, 
                      dbo.eDocumentTemplate.eDocumentTemplateID, dbo.eDocumentInstance.ReferenceNumber, CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName, 
                      dbo.eDocumentInstance.Name AS InstanceNamePostfix, dbo.eDocumentTemplate.ShortName
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSaved = 1) AND (dbo.eDocumentInstance.IsSubmitted = 0)
ORDER BY dbo.eDocumentInstance.SavedOn DESC
GO

-- ===========================================================================================================
-- VERSION 4.4
-- ===========================================================================================================
--------------------------------------------------------------------------------------------------------------
-- EDOC-1445 - kundetilpasset css
if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'CssUrl')
    ALTER TABLE Consumer ADD CssUrl VARCHAR(400) NULL
GO

---------------------------------------------------------------------------------------------------------------
-- EDOC-1458 - Les mer: stored procedure må sortere
ALTER PROCEDURE [dbo].[ReadMoreOnSubscriptionListItem_GetBySubscriptionID]
(
    @subscriptionID uniqueidentifier
)
AS
    SET NOCOUNT ON;
SELECT ReadMoreID, Caption, ImageUrl, HrefTarget, JavaScriptWindowName, JavaScriptWindowWidth, JavaScriptWindowHeight, JavaScriptWindowPositionFromLeft, JavaScriptWindowPositionFromTop, JavaScriptResizableWindow, JavaScriptShowScrollBars, JavaScriptShowFileMenuBar, JavaScriptShowToolBar, JavaScriptShowAddressBar, JavaScriptShowLinkBar, JavaScriptShowStatusBar, CreatedByConsumerID, ReadMoreItemMode, Url, ReadMoreOnSusbscriptionID, SubscriptionID, Sequence, SectionID, SectionName, SubscribedByConsumerID FROM ReadMoreOnSubscriptionListItem WHERE (SubscriptionID = @subscriptionID) ORDER BY Sequence
GO

-- ===========================================================================================================
-- VERSION 4.5
-- ===========================================================================================================

---------------------------------------------------------------------------------------------------------------
-- EDOC-1326 - Forbedret ytelse på GetCategories når konsument har mange abo

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'SubscribedCategories')
    DROP VIEW SubscribedCategories
GO

CREATE VIEW [dbo].[SubscribedCategories]
AS
SELECT     TOP (100) PERCENT dbo.Subscription.SubscribedByConsumerID, dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID, 
                      dbo.eDocumentTemplateCategory.Code, dbo.eDocumentTemplateCategory.Name, dbo.eDocumentTemplateCategory.Caption, 
                      dbo.eDocumentTemplateCategory.Description, dbo.eDocumentTemplateCategory.ImageUrl, dbo.ConsumerOnEDocumentTemplateCategory.Caption AS NewCaption, 
                      dbo.ConsumerOnEDocumentTemplateCategory.ImageUrl AS NewImageUrl, dbo.ConsumerOnEDocumentTemplateCategory.Comment AS NewComment
FROM         dbo.ConsumerOnEDocumentTemplateCategory RIGHT OUTER JOIN
                      dbo.eDocumentTemplateCategory RIGHT OUTER JOIN
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory ON 
                      dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID RIGHT OUTER
                       JOIN
                      dbo.eDocumentTemplate INNER JOIN
                      dbo.Subscription ON dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID ON 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID ON 
                      dbo.ConsumerOnEDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID AND 
                      dbo.ConsumerOnEDocumentTemplateCategory.ConsumerID = dbo.Subscription.SubscribedByConsumerID
GROUP BY dbo.eDocumentTemplateCategory.Name, dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID, dbo.ConsumerOnEDocumentTemplateCategory.Caption, 
                      dbo.ConsumerOnEDocumentTemplateCategory.CreatedByConsumerID, dbo.ConsumerOnEDocumentTemplateCategory.ConsumerID, 
                      dbo.eDocumentTemplateCategory.Code, dbo.eDocumentTemplateCategory.Description, dbo.eDocumentTemplateCategory.Caption, 
                      dbo.eDocumentTemplateCategory.ImageUrl, dbo.ConsumerOnEDocumentTemplateCategory.ImageUrl, dbo.ConsumerOnEDocumentTemplateCategory.Comment, 
                      dbo.Subscription.SubscribedByConsumerID
ORDER BY dbo.eDocumentTemplateCategory.Name

GO

---------------------------------------------------------------------------------------------------------------
-- EDOC-1469 - Informasjon om språk må lagres i basen (default språk og tilgjengelige språk)

IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_name = 'eDocumentTemplate' AND column_name = 'DefaultCulture')
    ALTER TABLE eDocumentTemplate ADD DefaultCulture VARCHAR(50) NULL
GO

IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_name = 'eDocumentTemplate' AND column_name = 'AvailableCultures')
    ALTER TABLE eDocumentTemplate ADD AvailableCultures VARCHAR(500) NULL
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'SubscribedEDocumentTemplate')
    DROP VIEW SubscribedEDocumentTemplate
GO

CREATE VIEW [dbo].[SubscribedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT dbo.Subscription.SubscribedByConsumerID, dbo.Subscription.eDocumentTemplateID, dbo.eDocumentTemplate.Name, 
                      dbo.eDocumentTemplate.CreatedOn, dbo.eDocumentTemplate.Comment, dbo.Subscription.SignMode, dbo.Subscription.SignObject, 
                      dbo.Subscription.DokumentbankenBlankettID, dbo.Subscription.SubscriptionID, dbo.Subscription.AvailableCultures, 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID, 
                      dbo.eDocumentTemplateCategory.Name AS eDocumentTemplateCategoryName, 
                      dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID AS HasFieldRelation, dbo.eDocumentTemplate.TemplateRepositoryUrl, 
                      dbo.eDocumentTemplate.ShortName, dbo.Subscription.FromDate, dbo.Subscription.ToDate, dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, 
                      dbo.eDocumentTemplate.FriendlyPDFResponse, dbo.eDocumentTemplate.FriendlyPDFPrint, dbo.eDocumentTemplate.FormID, dbo.Subscription.OperationModel, 
                      dbo.Subscription.SecurityLevel AS SecurityLevelSubscription, dbo.eDocumentTemplate.SecurityLevel AS SecurityLevelTemplate, 
                      dbo.eDocumentTemplate.DefaultCulture, 
                      dbo.eDocumentTemplate.AvailableCultures AS AvailableCulturesOnTemplate
FROM         dbo.eDocumentTemplate INNER JOIN
                      dbo.Subscription ON dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateCategory ON 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID LEFT OUTER
                       JOIN
                      dbo.eDocumentTemplateWithFieldRelation ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID
WHERE     (dbo.eDocumentTemplate.IsPublished = 1)
ORDER BY dbo.eDocumentTemplate.Name

GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'PublishedEDocumentTemplate')
    DROP VIEW PublishedEDocumentTemplate
GO

CREATE VIEW [dbo].[PublishedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT eDocumentTemplateID, Name, CreatedOn, Comment, SignMode, SignObject, UploadedByConsumerID, CurrentEDocumentTemplateVersionID, 
                      FormID, ShortName, SecurityLevel, DefaultCulture, AvailableCultures
FROM         dbo.eDocumentTemplate
WHERE     (IsPublished = 1) AND (TemplateRepositoryUrl IS NULL)
ORDER BY Name

GO

-- ===========================================================================================================
-- VERSION 5.0
-- ===========================================================================================================

--------------------------------------------------------------------------------------------------------------
-- EDOC-1036 - mobil

-- ny tabell device
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Device')
	BEGIN
		CREATE TABLE [dbo].[Device](
			[DeviceId] [uniqueidentifier] NOT NULL,
			[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			[Capabilities] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		 CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED 
		(
			[DeviceId] ASC
		)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
		) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
	END
GO

-- ny kolonne DeviceId i template
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'DeviceId')
    ALTER TABLE eDocumentTemplate ADD DeviceId uniqueidentifier NULL
GO

-- ny kolonne parent templateid i template
if not exists (select * from information_schema.columns where table_name = 'eDocumentTemplate' and column_name = 'ParentTemplateId')
    ALTER TABLE eDocumentTemplate ADD ParentTemplateId uniqueidentifier NULL
GO


if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_eDocumentTemplate_Device')
	begin
		ALTER TABLE [dbo].[eDocumentTemplate]  WITH CHECK ADD CONSTRAINT [FK_eDocumentTemplate_Device] FOREIGN KEY([DeviceId])
		REFERENCES [dbo].[Device] ([DeviceId])
	end 

if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_eDocumentTemplate_eDocumentTemplate')
	begin
		ALTER TABLE [dbo].[eDocumentTemplate]  WITH CHECK ADD  CONSTRAINT [FK_eDocumentTemplate_eDocumentTemplate] FOREIGN KEY([ParentTemplateId])
		REFERENCES [dbo].[eDocumentTemplate] ([eDocumentTemplateID])
	end

-- nytt view for variant
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'SubTemplate')
	DROP VIEW SubTemplate
GO

CREATE VIEW [dbo].[SubTemplate]
AS
SELECT TOP 100 PERCENT dbo.eDocumentTemplate.eDocumentTemplateID, dbo.eDocumentTemplate.ShortName, dbo.eDocumentTemplate.CreatedOn AS TemplateCreatedOn, 
	dbo.eDocumentTemplate.DeviceId, dbo.Device.Name AS DeviceName, dbo.Device.Description AS DeviceDescription, 
	dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, dbo.eDocumentTemplateVersion.Version, 
	dbo.eDocumentTemplateVersion.CreatedOn AS VersionCreatedOn, dbo.eDocumentTemplate.ParentTemplateId, 
	dbo.eDocumentTemplate.Name AS TemplateName
FROM dbo.eDocumentTemplate INNER JOIN
	dbo.Device ON dbo.eDocumentTemplate.DeviceId = dbo.Device.DeviceId INNER JOIN
	dbo.eDocumentTemplateVersion ON dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID
WHERE     (dbo.eDocumentTemplate.IsDeleted = 0)
ORDER BY dbo.eDocumentTemplate.ShortName
GO

-- modifikasjoner i eDocumentTemplateListItem
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[eDocumentTemplateListItem]'))
	DROP VIEW [dbo].[eDocumentTemplateListItem]
GO

CREATE VIEW [dbo].[eDocumentTemplateListItem]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentTemplate.eDocumentTemplateID, dbo.eDocumentTemplate.CreatedOn, 
                      dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, dbo.eDocumentTemplateVersion.Version, dbo.eDocumentTemplate.UploadedByConsumerID, 
                      dbo.eDocumentTemplate.IsPrefillEnabled, dbo.eDocumentTemplate.Comment, dbo.eDocumentTemplate.SignMode, dbo.eDocumentTemplate.SignObject, 
                      dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.ShortName, dbo.eDocumentTemplate.IsPublished, dbo.eDocumentTemplate.ForwardValidationMode, 
                      dbo.eDocumentTemplate.BackwardValidationMode, dbo.Consumer.Name AS UploadedByConsumerName, 
                      dbo.eDocumentTemplate.Name + ' [' + dbo.eDocumentTemplate.Comment + ']' AS NameWithComment, ISNULL(dbo.eDocumentTemplate.ShortName, '') 
                      + ' - ' + dbo.eDocumentTemplate.Name AS ShortNameWithName, dbo.eDocumentTemplate.IsDeleted, dbo.eDocumentTemplate.SecurityLevel, 
                      dbo.eDocumentTemplate.DeviceId, dbo.Device.Name AS DeviceName, dbo.eDocumentTemplate.ParentTemplateId
FROM         dbo.eDocumentTemplate LEFT OUTER JOIN
                      dbo.Device ON dbo.eDocumentTemplate.DeviceId = dbo.Device.DeviceId LEFT OUTER JOIN
                      dbo.Consumer ON dbo.eDocumentTemplate.UploadedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID
ORDER BY dbo.eDocumentTemplate.Name
GO

-- modifikasjoner i PublishedTemplate

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PublishedEDocumentTemplate]'))
DROP VIEW [dbo].[PublishedEDocumentTemplate]
GO

CREATE VIEW [dbo].[PublishedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT eDocumentTemplateID, Name, CreatedOn, Comment, SignMode, SignObject, UploadedByConsumerID, CurrentEDocumentTemplateVersionID, 
                      FormID, ShortName, SecurityLevel, ParentTemplateId, DeviceId, DefaultCulture, AvailableCultures
FROM         dbo.eDocumentTemplate
WHERE     (IsPublished = 1) AND (TemplateRepositoryUrl IS NULL)
ORDER BY Name
GO

-- modifikasjoner i SubscribedEDocumentTemplate
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[SubscribedEDocumentTemplate]'))
DROP VIEW [dbo].[SubscribedEDocumentTemplate]
GO

CREATE VIEW [dbo].[SubscribedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT dbo.Subscription.SubscribedByConsumerID, dbo.Subscription.eDocumentTemplateID, dbo.eDocumentTemplate.Name, 
                      dbo.eDocumentTemplate.CreatedOn, dbo.eDocumentTemplate.Comment, dbo.Subscription.SignMode, dbo.Subscription.SignObject, 
                      dbo.Subscription.DokumentbankenBlankettID, dbo.Subscription.SubscriptionID, dbo.Subscription.AvailableCultures, 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID, 
                      dbo.eDocumentTemplateCategory.Name AS eDocumentTemplateCategoryName, 
                      dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID AS HasFieldRelation, dbo.eDocumentTemplate.TemplateRepositoryUrl, 
                      dbo.eDocumentTemplate.ShortName, dbo.Subscription.FromDate, dbo.Subscription.ToDate, dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, 
                      dbo.eDocumentTemplate.FriendlyPDFResponse, dbo.eDocumentTemplate.FriendlyPDFPrint, dbo.eDocumentTemplate.FormID, dbo.Subscription.OperationModel, 
                      dbo.Subscription.SecurityLevel AS SecurityLevelSubscription, dbo.eDocumentTemplate.SecurityLevel AS SecurityLevelTemplate, 
                      dbo.eDocumentTemplate.ParentTemplateId,
                      dbo.eDocumentTemplate.DefaultCulture,
                      dbo.eDocumentTemplate.AvailableCultures AS AvailableCulturesOnTemplate
FROM         dbo.eDocumentTemplate INNER JOIN
                      dbo.Subscription ON dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateCategory ON 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID LEFT OUTER
                       JOIN
                      dbo.eDocumentTemplateWithFieldRelation ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID
WHERE     (dbo.eDocumentTemplate.IsPublished = 1)
ORDER BY dbo.eDocumentTemplate.Name
GO

--------------------------------------------------------------------------------------------------------------
-- EDOC-1482 - nytt view for å hente ut oversikt over hvilke skjema som er device spesifikke

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[TemplatesWithDevice]'))
DROP VIEW [dbo].[TemplatesWithDevice]
GO

CREATE VIEW [dbo].[TemplatesWithDevice]
AS
SELECT     TOP (100) PERCENT dbo.Device.DeviceId, dbo.Device.Name AS DeviceName, ParentTemplate.FormID AS ParentFormID, 
                      ParentTemplate.ShortName AS ParentShortName, ParentTemplate.eDocumentTemplateID AS ParentTemplateID, dbo.eDocumentTemplate.FormID, 
                      dbo.eDocumentTemplate.ShortName, dbo.eDocumentTemplate.eDocumentTemplateID
FROM         dbo.eDocumentTemplate INNER JOIN
                      dbo.Device ON dbo.eDocumentTemplate.DeviceId = dbo.Device.DeviceId LEFT OUTER JOIN
                      dbo.eDocumentTemplate AS ParentTemplate ON dbo.eDocumentTemplate.ParentTemplateId = ParentTemplate.eDocumentTemplateID
GROUP BY ParentTemplate.FormID, ParentTemplate.ShortName, dbo.Device.DeviceId, dbo.Device.Name, ParentTemplate.eDocumentTemplateID, 
                      dbo.eDocumentTemplate.FormID, dbo.eDocumentTemplate.ShortName, dbo.eDocumentTemplate.eDocumentTemplateID
ORDER BY ParentShortName

GO

--------------------------------------------------------------------------------------------------------------
-- EDOC-1510 - Fjern unik index IX_FlowRule_Name på tabellen FlowRule

IF exists (select name FROM sysindexes WHERE name = 'IX_FlowRule_Name')
   DROP INDEX FlowRule.IX_FlowRule_Name
GO

-- EDOC-1511 Server: Ny handling - FTP Send
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'FtpConsumer')
	BEGIN
		CREATE TABLE [dbo].[FtpConsumer](
			[FtpConsumerID] [uniqueidentifier] NOT NULL,
			[ConsumerID] [uniqueidentifier] NOT NULL,
			[Url] [varchar](500) NOT NULL,
			[Username] [varchar](100) NULL,
			[Password] [varchar](100) NULL,
		CONSTRAINT [PK_FtpConsumer] PRIMARY KEY CLUSTERED 
		(
			[FtpConsumerID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		)ON [PRIMARY]
	END
GO

--------------------------------------------------------------------------------------------------------------
-- EDOC-1551 - Fjerne STING (NLP) integrasjon

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'StingConsumer')
    BEGIN
		DROP TABLE StingConsumer
	END
	
DELETE FROM ACTIONTYPE WHERE ACTIONTYPEID = '93f23851-a942-4e7c-b00a-1e21e1a8899e'

--------------------------------------------------------------------------------------------------------------
-- EDOC-1563 - Trenger en mekanisme for å legge meta på instans i basen
if not exists (select * from information_schema.columns where table_name = 'eDocumentInstance' and column_name = 'Meta')
    ALTER TABLE eDocumentInstance ADD Meta text NULL
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[SubmittedEDocumentInstance]'))
DROP VIEW [dbo].[SubmittedEDocumentInstance]
GO

CREATE VIEW [dbo].[SubmittedEDocumentInstance]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.CreatedOn, dbo.eDocumentInstance.SubmitUserID, 
                      dbo.eDocumentInstance.SubmittedOn, dbo.eDocumentInstance.ReferenceNumber, dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, 
                      dbo.eDocumentTemplate.FormID, dbo.eDocumentInstance.SubmittedByConsumerID, dbo.Consumer.Name AS SubmittedByConsumerName, 
                      dbo.Consumer.Username AS SubmittedByConsumerUsername, dbo.eDocumentInstance.IsCoreModelDeleted, dbo.eDocumentInstance.CoreModelDeletedOn, 
                      dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, dbo.eDocumentTemplate.eDocumentTemplateID, 
                      CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + ' ' + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName, 
                      dbo.eDocumentInstance.Name AS InstanceNamePostfix, dbo.eDocumentTemplate.ShortName, dbo.eDocumentInstance.Meta
FROM         dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID INNER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID
WHERE     (dbo.eDocumentInstance.IsSubmitted = 1) AND (dbo.eDocumentInstance.IsDeleted = 0)
ORDER BY dbo.eDocumentInstance.SubmittedOn DESC
GO

--------------------------------------------------------------------------------------------------------------
-- EDOC-1554 - workflow 2.0


-- ***************************************************************
-- FLOWPROCESS
-- fjern disse to referansene fra FlowProcess fordi vi nå skal støtte flere endepunkter nå skal kunne gjøre save og submit på samme skjema!
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FlowProcess_FlowRule_DefaultSave]') AND parent_object_id = OBJECT_ID(N'[dbo].[FlowProcess]'))
ALTER TABLE [dbo].[FlowProcess] DROP CONSTRAINT [FK_FlowProcess_FlowRule_DefaultSave]

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FlowProcess_FlowRule_DefaultSubmit]') AND parent_object_id = OBJECT_ID(N'[dbo].[FlowProcess]'))
ALTER TABLE [dbo].[FlowProcess] DROP CONSTRAINT [FK_FlowProcess_FlowRule_DefaultSubmit]

if exists (select * from information_schema.columns where table_name = 'FlowProcess' and column_name = 'DefaultSubmitFlowRuleID')
    ALTER TABLE FlowProcess DROP COLUMN DefaultSubmitFlowRuleID
GO

if exists (select * from information_schema.columns where table_name = 'FlowProcess' and column_name = 'DefaultSaveFlowRuleID')
    ALTER TABLE FlowProcess DROP COLUMN DefaultSaveFlowRuleID
GO

-- allerede ved oppstart av skjema må vi vite hvilket endepunkt søknaden er hos (bl.a. på grunn av propertylayers). Derfor en referanse til StartStatus.
if not exists (select * from information_schema.columns where table_name = 'FlowProcess' and column_name = 'StartStatusId')
    ALTER TABLE FlowProcess ADD StartStatusId UNIQUEIDENTIFIER NULL
GO

if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_FlowProcess_StartStatus')
    begin	
		ALTER TABLE [dbo].[FlowProcess]  WITH CHECK ADD  CONSTRAINT [FK_FlowProcess_StartStatus] FOREIGN KEY([StartStatusId])
		REFERENCES [dbo].[FlowStatus] ([FlowStatusID])
	end

-- ***************************************************************
-- FLOWENDPOINT
-- fjerner disse to kolonnene fra endpoint fordi vi nå skal støtte flere endepunkter nå skal kunne gjøre save og submit på samme skjema! FlowStatus.IsLocked vil angi om skjema kan redigeres.
if exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'IsSubmittedStatus')
    ALTER TABLE FlowEndpoint DROP COLUMN IsSubmittedStatus
GO

if exists (select * from information_schema.columns where table_name = 'FlowEndpoint' and column_name = 'IsSavedStatus')
    ALTER TABLE FlowEndpoint DROP COLUMN IsSavedStatus
GO

-- sørger for at endepunkt har unikt navn innenfor en flytprosess
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('FlowEndpoint') AND name = 'IX_FlowProcess_Name') 
	BEGIN
		CREATE UNIQUE NONCLUSTERED INDEX [IX_FlowProcess_Name] ON [dbo].[FlowEndpoint] 
		(
			[Name] ASC,
			[FlowProcessID] ASC
		)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
	END

-- ***************************************************************	
-- FLOWSTATUS
if not exists (select * from information_schema.columns where table_name = 'FlowStatus' and column_name = 'IsLocked')
    ALTER TABLE FlowStatus ADD IsLocked bit NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowStatus' and column_name = 'ParentEndpointId')
    ALTER TABLE FlowStatus ADD ParentEndpointId UNIQUEIDENTIFIER NULL
GO

if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_FlowStatus_ParentEndpoint')
    begin	
		ALTER TABLE [dbo].[FlowStatus]  WITH CHECK ADD  CONSTRAINT [FK_FlowStatus_ParentEndpoint] FOREIGN KEY([ParentEndpointId])
		REFERENCES [dbo].[FlowEndpoint] ([FlowEndpointID])
	end

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('FlowStatus') AND name = 'IX_FlowProcess_Name') 
	BEGIN	
		CREATE UNIQUE NONCLUSTERED INDEX [IX_FlowProcess_Name] ON [dbo].[FlowStatus] 
		(
			[Name] ASC,
			[FlowProcessID] ASC
		)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
	END

-- ***************************************************************
-- FLOWRULE

-- dropp fra endpunkt og til endepunkt. 
-- dropp fremmednøkklene først
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FlowRule_FlowEndpoint_From]') AND parent_object_id = OBJECT_ID(N'[dbo].[FlowRule]'))
ALTER TABLE [dbo].[FlowRule] DROP CONSTRAINT [FK_FlowRule_FlowEndpoint_From]

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FlowRule_FlowEndpoint_To]') AND parent_object_id = OBJECT_ID(N'[dbo].[FlowRule]'))
ALTER TABLE [dbo].[FlowRule] DROP CONSTRAINT [FK_FlowRule_FlowEndpoint_To]

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FlowRule_FlowStatus]') AND parent_object_id = OBJECT_ID(N'[dbo].[FlowRule]'))
ALTER TABLE [dbo].[FlowRule] DROP CONSTRAINT [FK_FlowRule_FlowStatus]

-- dropp kolonner
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FlowRule_FlowEndpoint]') AND parent_object_id = OBJECT_ID(N'[dbo].[FlowRule]'))
	ALTER TABLE [dbo].[FlowRule] DROP CONSTRAINT [FK_FlowRule_FlowEndpoint]
GO

if exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'ToFlowEndpointID')
    ALTER TABLE FlowRule DROP COLUMN ToFlowEndpointID
GO

if exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'FromFlowEndpointID')
    ALTER TABLE FlowRule DROP COLUMN FromFlowEndpointID
GO

-- opprett nye kolonner
if not exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'FromFlowStatusID')
    ALTER TABLE FlowRule ADD FromFlowStatusID UNIQUEIDENTIFIER NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'RuleType')
    ALTER TABLE FlowRule ADD RuleType VARCHAR(200) NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'Caption')
    ALTER TABLE FlowRule ADD Caption VARCHAR(200) NULL
GO

-- opprett nye fremmednøkler
if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_FlowRule_FromStatus')
    begin	
		ALTER TABLE [dbo].[FlowRule]  WITH CHECK ADD  CONSTRAINT [FK_FlowRule_FromStatus] FOREIGN KEY([FromFlowStatusID])
		REFERENCES [dbo].[FlowStatus] ([FlowStatusID])
	end
	
if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_FlowRule_ToStatus')
    begin	
		ALTER TABLE [dbo].[FlowRule]  WITH CHECK ADD  CONSTRAINT [FK_FlowRule_ToStatus] FOREIGN KEY([ToFlowStatusID])
		REFERENCES [dbo].[FlowStatus] ([FlowStatusID])
	end	

-- opprett nye indekser	
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('FlowRule') AND name = 'IX_FlowRule_unique_from_to_status') 
	BEGIN
		CREATE UNIQUE NONCLUSTERED INDEX [IX_FlowRule_unique_from_to_status] ON [dbo].[FlowRule] 
		(
			[ToFlowStatusID] ASC,
			[FlowProcessID] ASC,
			[FromFlowStatusID] ASC
		)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]	
	END
		
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('FlowRule') AND name = 'IX_FlowProcess_Name') 
	BEGIN	
		CREATE UNIQUE NONCLUSTERED INDEX [IX_FlowProcess_Name] ON [dbo].[FlowRule] 
		(
			[Name] ASC,
			[FlowProcessID] ASC
		)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
	END

-- ***************************************************************
-- FLOWPROCESS ON TEMPLATE
-- sørger for at det bare legges én arbeidsflyt per skjermdialog

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('FlowProcessOnEDocumentTemplate') AND name = 'IX_OnlyOneFlowProcessPerTemplate') 
	BEGIN
		CREATE UNIQUE NONCLUSTERED INDEX [IX_OnlyOneFlowProcessPerTemplate] ON [dbo].[FlowProcessOnEDocumentTemplate] 
		(
		   [eDocumentTemplateID] ASC
		)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
	END

-- ***************************************************************
-- PROCESS ON FLOWRULE

-- må vite target endpoint for å sette riktig propertylayer. target endpoint er valgfritt.
if not exists (select * from information_schema.columns where table_name = 'ProcessOnFlowRule' and column_name = 'TargetEndpointId')
    ALTER TABLE ProcessOnFlowRule ADD TargetEndpointId UNIQUEIDENTIFIER NULL
GO


if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_ProcessOnFlowRule_TargetEndpoint')
    begin	
		ALTER TABLE [dbo].[ProcessOnFlowRule]  WITH CHECK ADD  CONSTRAINT [FK_ProcessOnFlowRule_TargetEndpoint] FOREIGN KEY([TargetEndpointId])
		REFERENCES [dbo].[FlowEndpoint] ([FlowEndpointID])
	end	
		
		
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('ProcessOnFlowRule') AND name = 'IX_ProcessOnFlowRule_unique_process_for_endpoint') 
	BEGIN
		CREATE UNIQUE NONCLUSTERED INDEX [IX_ProcessOnFlowRule_unique_process_for_endpoint] ON [dbo].[ProcessOnFlowRule] 
		(
			[ProcessID] ASC,
			[FlowRuleID] ASC,
			[TargetEndpointId] ASC
		)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
	END
		
-- ***************************************************************
-- FLOWOBJECT

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FlowObject_FlowEndpoint]') AND parent_object_id = OBJECT_ID(N'[dbo].[FlowObject]'))
ALTER TABLE [dbo].[FlowObject] DROP CONSTRAINT [FK_FlowObject_FlowEndpoint]

if not exists (select * from information_schema.columns where table_name = 'FlowObject' and column_name = 'CurrentUsername')
    ALTER TABLE FlowObject ADD CurrentUsername VARCHAR(400) NULL
GO

-- dropp kolonner
if exists (select * from information_schema.columns where table_name = 'FlowObject' and column_name = 'CurrentEndpointID')
    ALTER TABLE FlowObject DROP COLUMN CurrentEndpointID
GO

-- ***************************************************************
-- FLOWHISTORY
if exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'eDocumentInstanceID')
    ALTER TABLE FlowHistory DROP COLUMN eDocumentInstanceID
GO

if exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'FlowStatusName')
    ALTER TABLE FlowHistory DROP COLUMN FlowStatusName
GO

if exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'FlowEndpointID')
    ALTER TABLE FlowHistory DROP COLUMN FlowEndpointID
GO

if exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'FlowEndpointName')
    ALTER TABLE FlowHistory DROP COLUMN FlowEndpointName
GO

if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_FlowHistory_FlowObject')
    begin	
		ALTER TABLE [dbo].[FlowHistory]  WITH CHECK ADD  CONSTRAINT [FK_FlowHistory_FlowObject] FOREIGN KEY([FlowObjectID])
		REFERENCES [dbo].[FlowObject] ([FlowObjectID])
	end
	
if not exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_FlowHistory_FlowStatus')
    begin	
		ALTER TABLE [dbo].[FlowHistory]  WITH CHECK ADD  CONSTRAINT [FK_FlowHistory_FlowStatus] FOREIGN KEY([ToFlowStatusID])
		REFERENCES [dbo].[FlowStatus] ([FlowStatusID])
	end


-- ***************************************************************
-- PROCESSONFLOWRULE VIEW

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ProcessOnFlowRuleView]'))
DROP VIEW [dbo].[ProcessOnFlowRuleView]
GO

CREATE VIEW [dbo].[ProcessOnFlowRuleView]
AS
SELECT     dbo.ProcessOnFlowRule.ProcessOnFlowRuleID, dbo.ProcessOnFlowRule.ProcessID, dbo.Process.Name AS ProcessName, dbo.ProcessOnFlowRule.TargetEndpointId, 
                      dbo.FlowEndpoint.Name AS EndpointName, dbo.ProcessOnFlowRule.FlowRuleID
FROM         dbo.ProcessOnFlowRule INNER JOIN
                      dbo.Process ON dbo.ProcessOnFlowRule.ProcessID = dbo.Process.ProcessID LEFT OUTER JOIN
                      dbo.FlowEndpoint ON dbo.ProcessOnFlowRule.TargetEndpointId = dbo.FlowEndpoint.FlowEndpointID

GO


-- ***************************************************************
-- FlowProcessOnEDocumentTemplateView 

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FlowProcessOnEDocumentTemplateView]'))
DROP VIEW [dbo].[FlowProcessOnEDocumentTemplateView]
GO

CREATE VIEW [dbo].[FlowProcessOnEDocumentTemplateView]
AS
SELECT     dbo.FlowProcessOnEDocumentTemplate.FlowProcessOnEDocumentTemplateID, dbo.FlowProcessOnEDocumentTemplate.FlowProcessID, 
                      dbo.FlowProcessOnEDocumentTemplate.eDocumentTemplateID, dbo.eDocumentTemplate.CreatedOn, dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, 
                      dbo.eDocumentTemplate.UploadedByConsumerID, dbo.eDocumentTemplate.IsPrefillEnabled, dbo.eDocumentTemplate.Comment, dbo.eDocumentTemplate.SignMode, 
                      dbo.eDocumentTemplate.SignObject, dbo.eDocumentTemplate.Name, dbo.eDocumentTemplate.IsPublished, dbo.eDocumentTemplate.ForwardValidationMode, 
                      dbo.eDocumentTemplate.BackwardValidationMode, dbo.eDocumentTemplate.ShortName, dbo.eDocumentTemplate.TemplateRepositoryUrl, 
                      dbo.eDocumentTemplate.FormID, dbo.eDocumentTemplate.FriendlyPDFResponse, dbo.eDocumentTemplate.FriendlyPDFPrint, 
                      dbo.eDocumentTemplate.InstanceName, dbo.eDocumentTemplate.IsDeleted, dbo.eDocumentTemplate.SecurityLevel, 
                      dbo.eDocumentTemplate.UpdateSavedToCurrentVersion, dbo.eDocumentTemplate.ParentTemplateId, dbo.eDocumentTemplate.DeviceId, 
                      dbo.eDocumentTemplate.DefaultCulture, dbo.eDocumentTemplate.AvailableCultures
FROM         dbo.FlowProcessOnEDocumentTemplate INNER JOIN
                      dbo.FlowProcess ON dbo.FlowProcessOnEDocumentTemplate.FlowProcessID = dbo.FlowProcess.FlowProcessID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.FlowProcessOnEDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID

GO

-- ***************************************************************
-- INSTANCE VIEW

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InstanceView]'))
DROP VIEW [dbo].[InstanceView]
GO

CREATE VIEW [dbo].[InstanceView]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.eDocumentTemplateVersionID, dbo.eDocumentInstance.IsSubmitted, 
                      dbo.eDocumentInstance.IsSaved, dbo.eDocumentInstance.SubmittedOn, dbo.eDocumentInstance.SavedOn, dbo.eDocumentInstance.CreatedOn, 
                      dbo.eDocumentInstance.SubmitUserID, dbo.eDocumentInstance.SectionIndex, dbo.eDocumentInstance.PathIndex, dbo.eDocumentInstance.SubmittedByConsumerID, 
                      dbo.eDocumentInstance.IsDeleted, dbo.eDocumentInstance.ReferenceNumber, dbo.eDocumentInstance.IsCoreModelDeleted, 
                      dbo.eDocumentInstance.CoreModelDeletedOn, dbo.eDocumentInstance.ExternalReferenceID, dbo.eDocumentInstance.QueryString, 
                      dbo.eDocumentInstance.Name AS InstanceNamePostfix, dbo.eDocumentInstance.Hash, dbo.eDocumentInstance.EncryptionKeyId, dbo.eDocumentInstance.Meta, 
                      dbo.Consumer.Username AS ConsumerUsername, dbo.Consumer.Name AS ConsumerName, dbo.SubmitUser.Username AS SubmitUserUsername, 
                      dbo.eDocumentTemplate.Name AS TemplateName, dbo.eDocumentTemplate.ShortName AS TemplateShortName, dbo.eDocumentTemplate.FormID AS TemplateFormId,
                       dbo.eDocumentTemplate.SecurityLevel AS TemplateSecurityLevel, dbo.eDocumentTemplate.eDocumentTemplateID AS TemplateId, dbo.Device.DeviceId, 
                      dbo.Device.Name AS DeviceName, dbo.FlowObject.FlowObjectID, dbo.FlowObject.UpdatedOn AS FlowObjectUpdatedOn, dbo.FlowStatus.FlowStatusID, 
                      dbo.FlowStatus.Name AS FlowStatusName, dbo.FlowStatus.Caption AS FlowStatusCaption, dbo.FlowStatus.IsLocked AS FlowStatusIsLocked, 
                      dbo.FlowStatus.Description AS FlowStatusDescription, dbo.FlowEndpoint.FlowEndpointID, dbo.FlowEndpoint.Name AS FlowEndpointName, 
                      dbo.FlowEndpoint.Caption AS FlowEndpointCaption, dbo.FlowEndpoint.Description AS FlowEndpointDescription, dbo.FlowProcess.FlowProcessID, 
                      dbo.FlowProcess.Name AS FlowProcessName, dbo.FlowProcess.Caption AS FlowProcessCaption, dbo.FlowProcess.Description AS FlowProcessDescription, 
                      dbo.FlowObject.CurrentUsername AS FlowObjectCurrentUsername, CASE WHEN (eDocumentInstance.Name IS NOT NULL) 
                      THEN eDocumentTemplate.Name + ' ' + eDocumentInstance.Name ELSE eDocumentTemplate.Name END AS InstanceName, 
                      CASE WHEN (eDocumentInstance.IsSubmitted = 1) THEN eDocumentInstance.SubmittedOn ELSE eDocumentInstance.SavedOn END AS UpdatedOn
FROM         dbo.FlowProcess RIGHT OUTER JOIN
                      dbo.FlowStatus ON dbo.FlowProcess.StartStatusId = dbo.FlowStatus.FlowStatusID LEFT OUTER JOIN
                      dbo.FlowEndpoint ON dbo.FlowStatus.ParentEndpointId = dbo.FlowEndpoint.FlowEndpointID RIGHT OUTER JOIN
                      dbo.FlowObject ON dbo.FlowStatus.FlowStatusID = dbo.FlowObject.CurrentFlowStatusID RIGHT OUTER JOIN
                      dbo.eDocumentInstance ON dbo.FlowObject.eDocumentInstanceID = dbo.eDocumentInstance.eDocumentInstanceID LEFT OUTER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.eDocumentTemplate RIGHT OUTER JOIN
                      dbo.eDocumentTemplateVersion ON dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateVersion.eDocumentTemplateID ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID LEFT OUTER JOIN
                      dbo.Device ON dbo.eDocumentTemplate.DeviceId = dbo.Device.DeviceId LEFT OUTER JOIN
                      dbo.SubmitUser ON dbo.eDocumentInstance.SubmitUserID = dbo.SubmitUser.SubmitUserID
WHERE     (dbo.eDocumentInstance.IsDeleted = 0)
ORDER BY UpdatedOn DESC
GO


-- ***************************************************************
-- FLOWRULE VIEW

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FlowRuleView]'))
DROP VIEW [dbo].[FlowRuleView]
GO

CREATE VIEW [dbo].[FlowRuleView]
AS
SELECT     dbo.FlowRule.FlowRuleID, dbo.FlowRule.Name, dbo.FlowRule.Description, dbo.FlowRule.FlowProcessID, dbo.FlowRule.RuleType, dbo.FlowRule.FromFlowStatusID, 
                      FlowStatus_1.Name AS FromFlowStatusName, FlowStatus_1.Caption AS FromFlowStatusCaption, FlowStatus_1.Description AS FromFlowStatusDescription, 
                      FlowStatus_1.IsLocked AS FromFlowStatusIsLocked, FlowStatus_1.ParentEndpointId AS FromFlowStatusEndpointId, 
                      FlowEndpoint_1.Name AS FromFlowEndpointName, FlowEndpoint_1.Caption AS FromFlowEndpointCaption, 
                      FlowEndpoint_1.Description AS FromFlowEndpointDescription, dbo.FlowRule.ToFlowStatusID, dbo.FlowStatus.Name AS ToFlowStatusName, 
                      dbo.FlowStatus.Caption AS ToFlowStatusCaption, dbo.FlowStatus.Description AS ToFlowStatusDescription, dbo.FlowStatus.IsLocked AS ToFlowStatusIsLocked, 
                      dbo.FlowStatus.ParentEndpointId AS ToFlowStatusEndpointId, dbo.FlowEndpoint.Name AS ToFlowEndpointName, dbo.FlowEndpoint.Caption AS ToFlowEndpointCaption,
                       dbo.FlowEndpoint.Description AS ToFlowEndpointDescription
FROM         dbo.FlowRule INNER JOIN
                      dbo.FlowStatus ON dbo.FlowRule.ToFlowStatusID = dbo.FlowStatus.FlowStatusID INNER JOIN
                      dbo.FlowEndpoint ON dbo.FlowStatus.ParentEndpointId = dbo.FlowEndpoint.FlowEndpointID INNER JOIN
                      dbo.FlowStatus AS FlowStatus_1 ON dbo.FlowRule.FromFlowStatusID = FlowStatus_1.FlowStatusID INNER JOIN
                      dbo.FlowEndpoint AS FlowEndpoint_1 ON FlowStatus_1.ParentEndpointId = FlowEndpoint_1.FlowEndpointID

GO

-- ***************************************************************
-- FLOWSTATUS VIEW
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FlowStatusView]'))
DROP VIEW [dbo].[FlowStatusView]
GO

CREATE VIEW [dbo].[FlowStatusView]
AS
SELECT     dbo.FlowStatus.FlowStatusID, dbo.FlowStatus.Name, dbo.FlowStatus.Caption, dbo.FlowStatus.Description, dbo.FlowStatus.FlowProcessID, dbo.FlowStatus.IsLocked, 
                      dbo.FlowStatus.ParentEndpointId, dbo.FlowEndpoint.Name AS FlowEndpointName, dbo.FlowEndpoint.Caption AS FlowEndpointCaption, 
                      dbo.FlowEndpoint.Description AS FlowEndpointDescription
FROM         dbo.FlowStatus INNER JOIN
                      dbo.FlowEndpoint ON dbo.FlowStatus.ParentEndpointId = dbo.FlowEndpoint.FlowEndpointID

GO

-- EDOC-1633 Arbeidsflyt - skal være mulig å autorisere bruker før utfylling
if not exists (select * from information_schema.columns where table_name = 'FlowProcess' and column_name = 'AuthorizationType')
    ALTER TABLE FlowProcess ADD AuthorizationType text NULL
GO


-- EDOC-1675 Arbeidsflyt - timeout
if not exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'TimeoutDays')
    ALTER TABLE FlowRule ADD TimeoutDays int NULL
GO

IF EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('FlowRule') AND name = 'IX_FlowRule_unique_from_to_status') 
	DROP INDEX FlowRule.IX_FlowRule_unique_from_to_status
GO


-- EDOC-1678 Arbeidsflyt: velge flytregel basert på valg i skjema
if not exists (select * from information_schema.columns where table_name = 'FlowRule' and column_name = 'RuleField')
    ALTER TABLE FlowRule ADD RuleField VARCHAR(200) NULL

-- EDOC-1676 - Arbeidsflyt - mer info i FlowHistory
if not exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'ToFlowStatusID')
	EXEC sp_RENAME 'FlowHistory.FlowStatusID' , 'ToFlowStatusID', 'COLUMN'
GO

if not exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'FromFlowStatusID')
    ALTER TABLE FlowHistory ADD FromFlowStatusID UNIQUEIDENTIFIER NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'FromFlowEndpointID')
    ALTER TABLE FlowHistory ADD FromFlowEndpointID UNIQUEIDENTIFIER NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'ToFlowEndpointID')
    ALTER TABLE FlowHistory ADD ToFlowEndpointID UNIQUEIDENTIFIER NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'FlowRuleType')
    ALTER TABLE FlowHistory ADD FlowRuleType VARCHAR(200) NULL
GO

if not exists (select * from information_schema.columns where table_name = 'FlowHistory' and column_name = 'FlowRuleID')
    ALTER TABLE FlowHistory ADD FlowRuleID UNIQUEIDENTIFIER NULL
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FlowHistoryView]'))
	DROP VIEW [dbo].[FlowHistoryView]
GO

CREATE VIEW [dbo].[FlowHistoryView]
AS
SELECT        dbo.FlowObject.FlowObjectID, dbo.FlowObject.eDocumentInstanceID, dbo.FlowObject.CurrentFlowStatusID, dbo.FlowHistory.CreatedOn, dbo.FlowHistory.Comment, dbo.FlowHistory.ADManager, 
                         dbo.FlowHistory.LoggedInUsername, dbo.FlowHistory.FlowHistoryID, dbo.FlowHistory.FromFlowStatusID, dbo.FlowStatus.Name AS FromFlowStatusName, dbo.FlowStatus.Caption AS FromFlowStatusCaption, 
                         dbo.FlowHistory.FromFlowEndpointID, dbo.FlowEndpoint.Name AS FromFlowEndpointName, dbo.FlowEndpoint.Caption AS FromFlowEndpointCaption, dbo.FlowHistory.FlowRuleType, dbo.FlowHistory.FlowRuleID, 
                         dbo.FlowHistory.ToFlowStatusID, FlowStatus_1.Name AS ToFlowStatusName, FlowStatus_1.Caption AS ToFlowStatusCaption, dbo.FlowHistory.ToFlowEndpointID, FlowEndpoint_1.Name AS ToFlowEndpointName, 
                         FlowEndpoint_1.Caption AS ToFlowEndpointCaption
FROM            dbo.FlowEndpoint AS FlowEndpoint_1 RIGHT OUTER JOIN
                         dbo.FlowStatus AS FlowStatus_1 RIGHT OUTER JOIN
                         dbo.FlowEndpoint RIGHT OUTER JOIN
                         dbo.FlowHistory INNER JOIN
                         dbo.FlowObject ON dbo.FlowHistory.FlowObjectID = dbo.FlowObject.FlowObjectID ON dbo.FlowEndpoint.FlowEndpointID = dbo.FlowHistory.FromFlowEndpointID LEFT OUTER JOIN
                         dbo.FlowStatus ON dbo.FlowHistory.FromFlowStatusID = dbo.FlowStatus.FlowStatusID ON FlowStatus_1.FlowStatusID = dbo.FlowHistory.ToFlowStatusID ON 
                         FlowEndpoint_1.FlowEndpointID = dbo.FlowHistory.ToFlowEndpointID
GO

-- EDOC-1692 - Betingelse på flowrule - ingen valg i choice
IF EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('FlowRule') AND name = 'IX_FlowRule_unique_from_to_status') 
	DROP INDEX FlowRule.IX_FlowRule_unique_from_to_status
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_FlowRule_unique_from_to_status] ON [dbo].[FlowRule]
(
	[ToFlowStatusID] ASC,
	[FlowProcessID] ASC,
	[FromFlowStatusID] ASC,
	[RuleType] ASC,
	[RuleField] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

--EDOC-1707 Styre rekkefølge på response items
if not exists (select * from information_schema.columns where table_name = 'ResponseItemTypeOnAction' and column_name = 'Importable')
    ALTER TABLE ResponseItemTypeOnAction ADD Importable bit NULL
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ResponseItemTypeOnActionListItem]'))
	DROP VIEW [dbo].[ResponseItemTypeOnActionListItem]
GO

CREATE VIEW [dbo].[ResponseItemTypeOnActionListItem]
AS
SELECT     dbo.ResponseItemTypeOnAction.ResponseItemTypeOnActionID, dbo.ResponseItemTypeOnAction.ActionID, dbo.ResponseItemTypeOnAction.ResponseItemTypeID, 
                      dbo.ResponseItemType.Name AS ResponseItemTypeName, dbo.ResponseItemTypeOnAction.Importable
FROM         dbo.ResponseItemTypeOnAction LEFT OUTER JOIN
                      dbo.ResponseItemType ON dbo.ResponseItemTypeOnAction.ResponseItemTypeID = dbo.ResponseItemType.ResponseItemTypeID
GO

-- EDOC-1731 - workflow restart executed/aborted workflow
if not exists (select * from information_schema.columns where table_name = 'ExecutedProcess' and column_name = 'EndpointName')
    ALTER TABLE ExecutedProcess ADD EndpointName VARCHAR(600) NULL
GO

if not exists (select * from information_schema.columns where table_name = 'AbortedProcess' and column_name = 'EndpointName')
    ALTER TABLE AbortedProcess ADD EndpointName VARCHAR(600) NULL
GO


-- EDOC-1757 - slette skjema som er del av arbeidsflyt
if exists (select * from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = 'FK_FlowHistory_FlowObject')
    begin	
		ALTER TABLE [dbo].[FlowHistory] DROP CONSTRAINT [FK_FlowHistory_FlowObject]
	end
	
ALTER TABLE [dbo].[FlowHistory]  WITH CHECK ADD  CONSTRAINT [FK_FlowHistory_FlowObject] FOREIGN KEY([FlowObjectID])
REFERENCES [dbo].[FlowObject] ([FlowObjectID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[FlowHistory] CHECK CONSTRAINT [FK_FlowHistory_FlowObject]
GO


-- EDOC-1776 - FlowObject.CurrentUsername
alter table FlowHistory
alter column LoggedInUsername varchar(400)

if not exists (SELECT * FROM sysindexes WHERE id = OBJECT_ID('FlowHistory') AND name = 'IX_FlowHistory_LoggedInUsername')
	begin
		CREATE NONCLUSTERED INDEX [IX_FlowHistory_LoggedInUsername] ON [dbo].[FlowHistory]
		(
			[LoggedInUsername] 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)
	end
	
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[InstanceViewWithFlowHistory]'))
	DROP VIEW [dbo].[InstanceViewWithFlowHistory]
GO
	
CREATE VIEW [dbo].[InstanceViewWithFlowHistory]
AS
SELECT        dbo.FlowHistory.LoggedInUsername, dbo.FlowHistory.FlowObjectID, dbo.InstanceView.eDocumentTemplateVersionID, dbo.InstanceView.InstanceName, dbo.InstanceView.FlowObjectCurrentUsername, 
                         dbo.InstanceView.FlowProcessCaption, dbo.InstanceView.FlowProcessName, dbo.InstanceView.FlowProcessID, dbo.InstanceView.FlowEndpointCaption, dbo.InstanceView.FlowEndpointName, 
                         dbo.InstanceView.FlowEndpointID, dbo.InstanceView.FlowStatusCaption, dbo.InstanceView.FlowStatusName, dbo.InstanceView.FlowStatusID, dbo.InstanceView.FlowObjectUpdatedOn, 
                         dbo.InstanceView.DeviceName, dbo.InstanceView.DeviceId, dbo.InstanceView.TemplateId, dbo.InstanceView.TemplateSecurityLevel, dbo.InstanceView.TemplateFormId, dbo.InstanceView.TemplateShortName, 
                         dbo.InstanceView.TemplateName, dbo.InstanceView.SubmitUserUsername, dbo.InstanceView.ConsumerName, dbo.InstanceView.ConsumerUsername, dbo.InstanceView.EncryptionKeyId, dbo.InstanceView.Hash, 
                         dbo.InstanceView.InstanceNamePostfix, dbo.InstanceView.QueryString, dbo.InstanceView.ExternalReferenceID, dbo.InstanceView.CoreModelDeletedOn, dbo.InstanceView.ReferenceNumber, 
                         dbo.InstanceView.SubmittedByConsumerID, dbo.InstanceView.PathIndex, dbo.InstanceView.SectionIndex, dbo.InstanceView.SubmitUserID, dbo.InstanceView.CreatedOn, dbo.InstanceView.SavedOn, 
                         dbo.InstanceView.SubmittedOn, dbo.InstanceView.IsSubmitted, dbo.InstanceView.IsSaved, dbo.InstanceView.IsDeleted, dbo.InstanceView.IsCoreModelDeleted, dbo.InstanceView.FlowStatusIsLocked, 
                         dbo.InstanceView.UpdatedOn, dbo.InstanceView.eDocumentInstanceID
FROM            dbo.FlowHistory INNER JOIN
                         dbo.InstanceView ON dbo.FlowHistory.FlowObjectID = dbo.InstanceView.FlowObjectID
GROUP BY dbo.FlowHistory.LoggedInUsername, dbo.FlowHistory.FlowObjectID, dbo.InstanceView.eDocumentTemplateVersionID, dbo.InstanceView.InstanceName, dbo.InstanceView.FlowObjectCurrentUsername, 
                         dbo.InstanceView.FlowProcessCaption, dbo.InstanceView.FlowProcessName, dbo.InstanceView.FlowProcessID, dbo.InstanceView.FlowEndpointCaption, dbo.InstanceView.FlowEndpointName, 
                         dbo.InstanceView.FlowEndpointID, dbo.InstanceView.FlowStatusCaption, dbo.InstanceView.FlowStatusName, dbo.InstanceView.FlowStatusID, dbo.InstanceView.FlowObjectUpdatedOn, 
                         dbo.InstanceView.DeviceName, dbo.InstanceView.DeviceId, dbo.InstanceView.TemplateId, dbo.InstanceView.TemplateSecurityLevel, dbo.InstanceView.TemplateFormId, dbo.InstanceView.TemplateShortName, 
                         dbo.InstanceView.TemplateName, dbo.InstanceView.SubmitUserUsername, dbo.InstanceView.ConsumerName, dbo.InstanceView.ConsumerUsername, dbo.InstanceView.EncryptionKeyId, dbo.InstanceView.Hash, 
                         dbo.InstanceView.InstanceNamePostfix, dbo.InstanceView.QueryString, dbo.InstanceView.ExternalReferenceID, dbo.InstanceView.CoreModelDeletedOn, dbo.InstanceView.ReferenceNumber, 
                         dbo.InstanceView.SubmittedByConsumerID, dbo.InstanceView.PathIndex, dbo.InstanceView.SectionIndex, dbo.InstanceView.SubmitUserID, dbo.InstanceView.CreatedOn, dbo.InstanceView.SavedOn, 
                         dbo.InstanceView.SubmittedOn, dbo.InstanceView.IsSubmitted, dbo.InstanceView.UpdatedOn, dbo.InstanceView.IsSaved, dbo.InstanceView.IsDeleted, dbo.InstanceView.IsCoreModelDeleted, 
                         dbo.InstanceView.FlowStatusIsLocked, dbo.InstanceView.eDocumentInstanceID

GO

-- versjon 6.2

-- EDOC-1824 - orgno på konsument 
if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'OrgNo')
    ALTER TABLE Consumer ADD OrgNo VARCHAR(20) NULL
GO

if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'DifiSign')
    ALTER TABLE Subscription ADD DifiSign bit NOT NULL DEFAULT 0
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[SubscribedEDocumentTemplate]'))
DROP VIEW [dbo].[SubscribedEDocumentTemplate]
GO

CREATE VIEW [dbo].[SubscribedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT dbo.Subscription.SubscribedByConsumerID, dbo.Subscription.eDocumentTemplateID, dbo.eDocumentTemplate.Name, 
                      dbo.eDocumentTemplate.CreatedOn, dbo.eDocumentTemplate.Comment, dbo.Subscription.SignMode, dbo.Subscription.SignObject, 
                      dbo.Subscription.DokumentbankenBlankettID, dbo.Subscription.SubscriptionID, dbo.Subscription.AvailableCultures, 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID, 
                      dbo.eDocumentTemplateCategory.Name AS eDocumentTemplateCategoryName, 
                      dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID AS HasFieldRelation, dbo.eDocumentTemplate.TemplateRepositoryUrl, 
                      dbo.eDocumentTemplate.ShortName, dbo.Subscription.FromDate, dbo.Subscription.ToDate, dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, 
                      dbo.eDocumentTemplate.FriendlyPDFResponse, dbo.eDocumentTemplate.FriendlyPDFPrint, dbo.eDocumentTemplate.FormID, dbo.Subscription.OperationModel, 
                      dbo.Subscription.SecurityLevel AS SecurityLevelSubscription, dbo.eDocumentTemplate.SecurityLevel AS SecurityLevelTemplate, 
                      dbo.eDocumentTemplate.ParentTemplateId,
                      dbo.eDocumentTemplate.DefaultCulture,
                      dbo.eDocumentTemplate.AvailableCultures AS AvailableCulturesOnTemplate,
                      dbo.Subscription.DifiSign
FROM         dbo.eDocumentTemplate INNER JOIN
                      dbo.Subscription ON dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateCategory ON 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID LEFT OUTER
                       JOIN
                      dbo.eDocumentTemplateWithFieldRelation ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID
WHERE     (dbo.eDocumentTemplate.IsPublished = 1)
ORDER BY dbo.eDocumentTemplate.Name
GO

-- EDOC-1909 - konsument kan overstyre start/stop/active på abonnement
if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'ConsumerIsActive')
    ALTER TABLE Subscription ADD ConsumerIsActive bit NULL 
GO

if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'ConsumerToDate')
    ALTER TABLE Subscription ADD ConsumerToDate DATETIME NULL
GO

if not exists (select * from information_schema.columns where table_name = 'Subscription' and column_name = 'ConsumerFromDate')
    ALTER TABLE Subscription ADD ConsumerFromDate DATETIME NULL
GO

--EDOC-1910	Automatisk restart av avbrutte og manglende prosesser
if not exists (select * from information_schema.columns where table_name = 'Process' and column_name = 'RestartAbortedProcessInterval')
    ALTER TABLE Process ADD RestartAbortedProcessInterval int NULL
GO

ALTER VIEW [dbo].[AbortedProcessListItem]
AS
SELECT     dbo.AbortedProcess.AbortedProcessID, dbo.AbortedProcess.eDocumentInstanceID, dbo.eDocumentInstance.CreatedOn AS eDocumentInstanceCreatedOn, 
                      dbo.eDocumentInstance.SubmittedOn AS eDocumentInstanceSubmittedOn, dbo.eDocumentTemplateVersion.eDocumentTemplateID, 
                      dbo.eDocumentTemplate.Name AS eDocumentTemplateName, dbo.eDocumentInstance.ReferenceNumber AS eDocumentInstanceReferenceNumber, 
                      dbo.AbortedProcess.ProcessID, dbo.Process.Name AS ProcessName, dbo.Process.Comment AS ProcessComment, dbo.AbortedProcess.EventLogItemID, 
                      dbo.AbortedProcess.Source AS EventLogItemSource, dbo.AbortedProcess.StackTrace AS EventLogItemStackTrace, 
                      dbo.AbortedProcess.Description AS EventLogItemDescription, dbo.AbortedProcess.AbortedOn AS AbortedProcessAbortedOn, 
                      dbo.AbortedProcess.LastSuccessfulActionID AS AbortedProcessLastSuccessfulActionID, 
                      dbo.AbortedProcess.InvokerConsumerID AS AbortedProcessInvokerConsumerID, dbo.Consumer.Username AS ConsumerUsername, 
                      dbo.Consumer.Name AS ConsumerName, dbo.eDocumentInstance.eDocumentTemplateVersionID, dbo.eDocumentInstance.SavedOn AS eDocumentInstanceSavedOn, 
                      dbo.eDocumentTemplateVersion.Version AS eDocumentTemplateVersionVersion, dbo.Process.RestartAbortedProcessInterval AS RestartAbortedProcessInterval
FROM         dbo.eDocumentTemplate INNER JOIN
                      dbo.eDocumentTemplateVersion INNER JOIN
                      dbo.AbortedProcess INNER JOIN
                      dbo.eDocumentInstance ON dbo.AbortedProcess.eDocumentInstanceID = dbo.eDocumentInstance.eDocumentInstanceID ON 
                      dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID = dbo.eDocumentInstance.eDocumentTemplateVersionID ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateVersion.eDocumentTemplateID LEFT OUTER JOIN
                      dbo.Process ON dbo.AbortedProcess.ProcessID = dbo.Process.ProcessID LEFT OUTER JOIN
                      dbo.Consumer ON dbo.AbortedProcess.InvokerConsumerID = dbo.Consumer.ConsumerID
GO

ALTER PROCEDURE [dbo].[AbortedProcessListItem_Select]
AS
    SET NOCOUNT ON;
SELECT AbortedProcessID, eDocumentInstanceID, eDocumentInstanceCreatedOn, eDocumentInstanceSubmittedOn, eDocumentTemplateID, eDocumentTemplateName, eDocumentInstanceReferenceNumber, ProcessID, ProcessName, ProcessComment, EventLogItemID, EventLogItemSource, EventLogItemStackTrace, EventLogItemDescription, AbortedProcessAbortedOn, AbortedProcessLastSuccessfulActionID, AbortedProcessInvokerConsumerID, ConsumerUsername, ConsumerName, eDocumentTemplateVersionID, eDocumentInstanceSavedOn, eDocumentTemplateVersionVersion, RestartAbortedProcessInterval FROM AbortedProcessListItem

GO

ALTER VIEW [dbo].[InstanceWithNoExecutedProcessListItem]
AS
SELECT     TOP (100) PERCENT dbo.eDocumentInstance.eDocumentInstanceID, dbo.eDocumentInstance.SubmittedOn AS eDocumentInstanceSubmittedOn, 
                      dbo.eDocumentInstance.SavedOn AS eDocumentInstanceSavedOn, dbo.eDocumentInstance.CreatedOn AS eDocumentInstanceCreatedOn, 
                      dbo.eDocumentInstance.SubmittedByConsumerID AS eDocumentInstanceSubmittedByConsumerID, 
                      dbo.eDocumentInstance.ReferenceNumber AS eDocumentInstanceReferenceNumber, dbo.Consumer.Username AS ConsumerUsername, 
                      dbo.Consumer.Name AS ConsumerName, dbo.eDocumentInstance.eDocumentTemplateVersionID, dbo.eDocumentTemplateVersion.eDocumentTemplateID, 
                      dbo.eDocumentTemplateVersion.Version AS eDocumentTemplateVersionVersion, dbo.eDocumentTemplate.Name AS eDocumentTemplateName, 
                      dbo.eDocumentTemplate.Comment AS eDocumentTemplateComment, dbo.Subscription.SubscriptionID, dbo.Subscription.ToDate, 
                      dbo.Subscription.DestinationSystemID, dbo.DestinationSystem.Name AS DestinationSystemName, dbo.ProcessOnSubscription.ProcessID, 
                      dbo.Process.Name AS ProcessName, dbo.ProcessOnEDocumentTemplate.ProcessOnEDocumentTemplateID, dbo.eDocumentTemplate.UploadedByConsumerID, dbo.Process.RestartAbortedProcessInterval AS RestartAbortedProcessInterval
FROM         dbo.Process RIGHT OUTER JOIN
                      dbo.eDocumentInstance INNER JOIN
                      dbo.eDocumentTemplateVersion ON 
                      dbo.eDocumentInstance.eDocumentTemplateVersionID = dbo.eDocumentTemplateVersion.eDocumentTemplateVersionID INNER JOIN
                      dbo.eDocumentTemplate ON dbo.eDocumentTemplateVersion.eDocumentTemplateID = dbo.eDocumentTemplate.eDocumentTemplateID LEFT OUTER JOIN
                      dbo.ProcessOnEDocumentTemplate ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.ProcessOnEDocumentTemplate.eDocumentTemplateID LEFT OUTER JOIN
                      dbo.Consumer ON dbo.eDocumentInstance.SubmittedByConsumerID = dbo.Consumer.ConsumerID LEFT OUTER JOIN
                      dbo.ExecutedProcessListItem ON dbo.eDocumentInstance.eDocumentInstanceID = dbo.ExecutedProcessListItem.eDocumentInstanceID LEFT OUTER JOIN
                      dbo.AbortedProcessListItem ON dbo.eDocumentInstance.eDocumentInstanceID = dbo.AbortedProcessListItem.eDocumentInstanceID LEFT OUTER JOIN
                      dbo.DestinationSystem RIGHT OUTER JOIN
                      dbo.Subscription ON dbo.DestinationSystem.DestinationSystemID = dbo.Subscription.DestinationSystemID AND 
                      dbo.DestinationSystem.DestinationSystemID = dbo.Subscription.DestinationSystemID ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID AND 
                      dbo.Consumer.ConsumerID = dbo.Subscription.SubscribedByConsumerID LEFT OUTER JOIN
                      dbo.ProcessOnSubscription ON dbo.Subscription.SubscriptionID = dbo.ProcessOnSubscription.SubscriptionID ON 
                      dbo.Process.ProcessID = dbo.ProcessOnSubscription.ProcessID
WHERE     (dbo.AbortedProcessListItem.AbortedProcessID IS NULL) AND (dbo.ExecutedProcessListItem.ExecutedProcessID IS NULL) AND 
                      (dbo.eDocumentInstance.IsSubmitted = 1) AND (dbo.eDocumentInstance.SubmittedOn BETWEEN GETDATE() - 365 AND DATEADD(mi, - 15, GETDATE()))
ORDER BY eDocumentInstanceSubmittedOn DESC

GO

--KOM-2549 Avpublisering av skjema (Kundeadmin.) > gir feilmelding
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[SubscribedEDocumentTemplate]'))
DROP VIEW [dbo].[SubscribedEDocumentTemplate]
GO

CREATE VIEW [dbo].[SubscribedEDocumentTemplate]
AS
SELECT     TOP (100) PERCENT dbo.Subscription.SubscribedByConsumerID, dbo.Subscription.eDocumentTemplateID, dbo.eDocumentTemplate.Name, 
                      dbo.eDocumentTemplate.CreatedOn, dbo.eDocumentTemplate.Comment, dbo.Subscription.SignMode, dbo.Subscription.SignObject, 
                      dbo.Subscription.DokumentbankenBlankettID, dbo.Subscription.SubscriptionID, dbo.Subscription.AvailableCultures, 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID, 
                      dbo.eDocumentTemplateCategory.Name AS eDocumentTemplateCategoryName, 
                      dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID AS HasFieldRelation, dbo.eDocumentTemplate.TemplateRepositoryUrl, 
                      dbo.eDocumentTemplate.ShortName, dbo.Subscription.FromDate, dbo.Subscription.ToDate, dbo.eDocumentTemplate.CurrentEDocumentTemplateVersionID, 
                      dbo.eDocumentTemplate.FriendlyPDFResponse, dbo.eDocumentTemplate.FriendlyPDFPrint, dbo.eDocumentTemplate.FormID, dbo.Subscription.OperationModel, 
                      dbo.Subscription.SecurityLevel AS SecurityLevelSubscription, dbo.eDocumentTemplate.SecurityLevel AS SecurityLevelTemplate, 
                      dbo.eDocumentTemplate.ParentTemplateId,
                      dbo.eDocumentTemplate.DefaultCulture,
                      dbo.eDocumentTemplate.AvailableCultures AS AvailableCulturesOnTemplate,
                      dbo.Subscription.DifiSign, dbo.Subscription.ConsumerFromDate, dbo.Subscription.ConsumerToDate, dbo.Subscription.ConsumerIsActive
FROM         dbo.eDocumentTemplate INNER JOIN
                      dbo.Subscription ON dbo.eDocumentTemplate.eDocumentTemplateID = dbo.Subscription.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateID INNER JOIN
                      dbo.eDocumentTemplateCategory ON 
                      dbo.eDocumentTemplateOnEDocumentTemplateCategory.eDocumentTemplateCategoryID = dbo.eDocumentTemplateCategory.eDocumentTemplateCategoryID LEFT OUTER
                       JOIN
                      dbo.eDocumentTemplateWithFieldRelation ON 
                      dbo.eDocumentTemplate.eDocumentTemplateID = dbo.eDocumentTemplateWithFieldRelation.eDocumentTemplateID
WHERE     (dbo.eDocumentTemplate.IsPublished = 1)
ORDER BY dbo.eDocumentTemplate.Name
GO

--KOM-1857 Opprette et eget felt for kommunenummer på konsument
if not exists (select * from information_schema.columns where table_name = 'Consumer' and column_name = 'Kommunenr')
    ALTER TABLE Consumer ADD Kommunenr VARCHAR(20) NULL
GO