/****** TABLES ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}DMX_ApplicationPermissions') AND OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}DMX_ApplicationPermissions (
 [PortalId] INT NOT NULL,
 [AllowAccess] BIT,
 [Expires] DATETIME,
 [PermissionId] INT NOT NULL,
 [RoleId] INT NOT NULL,
 [UserId] INT NOT NULL
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'PK_{objectQualifier}DMX_ApplicationPermissions') AND OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_ApplicationPermissions ADD CONSTRAINT
	PK_{objectQualifier}DMX_ApplicationPermissions PRIMARY KEY CLUSTERED 
	(
	[PortalId], [PermissionId], [RoleId], [UserId]
	) ON [PRIMARY]
GO

CREATE TABLE {databaseOwner}{objectQualifier}DMX_AttributesML (
 [AttributeId] INT NOT NULL,
 [Locale] VARCHAR(10) NOT NULL,
 [AttributeName] NVARCHAR (200) NOT NULL
) ON [PRIMARY]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_AttributesML') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_AttributesML ADD CONSTRAINT
 PK_{objectQualifier}DMX_AttributesML PRIMARY KEY CLUSTERED 
 (
 [AttributeId], [Locale]
 ) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes ADD
 [AttributeName] NVARCHAR (200)
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{objectQualifier}DMX_Attributes' AND COLUMN_NAME = 'Name')
BEGIN
 EXEC ('UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
  SET [AttributeName]=[Name]');
 EXEC ('ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes
  DROP [Name]');
END
GO

if exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{objectQualifier}DMX_Attributes' AND COLUMN_NAME = 'EntryTypes')
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes DROP COLUMN
 [EntryTypes]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_CategoriesML') and OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}DMX_CategoriesML (
 [CategoryId] INT NOT NULL,
 [Locale] VARCHAR (10) NOT NULL,
 [CategoryName] NVARCHAR (200) NOT NULL
) ON [PRIMARY]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_CategoriesML') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_CategoriesML ADD CONSTRAINT
 PK_{objectQualifier}DMX_CategoriesML PRIMARY KEY CLUSTERED 
 (
 [CategoryId], [Locale]
 ) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_Categories ADD
 [CategoryName] NVARCHAR (200)
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{objectQualifier}DMX_Categories' AND COLUMN_NAME = 'Name_old')
BEGIN
 EXEC('UPDATE {databaseOwner}{objectQualifier}DMX_Categories
  SET [CategoryName]=[Name_old]');
 EXEC('ALTER TABLE {databaseOwner}{objectQualifier}DMX_Categories
  DROP COLUMN [Name_old]');
END
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_EntriesML') and OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}DMX_EntriesML (
 [EntryId] INT NOT NULL,
 [Locale] NVARCHAR (10) NOT NULL,
 [Remarks] NVARCHAR(MAX),
 [Title] NVARCHAR (1000)
) ON [PRIMARY]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_EntriesML') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntriesML ADD CONSTRAINT
 PK_{objectQualifier}DMX_EntriesML PRIMARY KEY CLUSTERED 
 (
 [EntryId], [Locale]
 ) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries ADD
 [Title] NVARCHAR (1000)
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{objectQualifier}DMX_Entries' AND COLUMN_NAME = 'Remarks_old')
BEGIN
 EXEC sp_rename '{databaseOwner}{objectQualifier}DMX_Entries.[Remarks_old]', 'Remarks', 'COLUMN';
 EXEC ('ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries ALTER COLUMN [Remarks] NVARCHAR(MAX)');
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{objectQualifier}DMX_Entries' AND COLUMN_NAME = 'Remarks')
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries ADD
 [Remarks] NVARCHAR(MAX)
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{objectQualifier}DMX_Entries' AND COLUMN_NAME = 'Description')
BEGIN
 EXEC('UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET [Title]=[Description]');
 EXEC('ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries
  DROP COLUMN [Description]');
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}DMX_DirectAccessKeys') AND OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}DMX_DirectAccessKeys (
 [PortalId] INT NOT NULL,
 [Downloads] INT,
 [Email] NVARCHAR (100),
 [EntryId] INT,
 [Expires] DATETIME,
 [Key] NVARCHAR (100) NOT NULL,
 [UserId] INT
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'PK_{objectQualifier}DMX_DirectAccessKeys') AND OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_DirectAccessKeys ADD CONSTRAINT
	PK_{objectQualifier}DMX_DirectAccessKeys PRIMARY KEY CLUSTERED 
	(
	[PortalId], [Key]
	) ON [PRIMARY]
GO

EXEC sp_rename '{databaseOwner}{objectQualifier}DMX_Categories.[id]', 'CategoryId', 'COLUMN';
GO

EXEC sp_rename '{databaseOwner}{objectQualifier}DMX_Log.[EntId]', 'EntryId', 'COLUMN';
GO

EXEC sp_rename '{databaseOwner}{objectQualifier}DMX_Log.[id]', 'LogId', 'COLUMN';
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_Log ADD
 [PortalId] INT
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Log
SET PortalId = e.PortalId
FROM {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntryId=e.EntryId
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_SearchResults ADD
 [Hits] INT NOT NULL DEFAULT 1,
 [Extract] NVARCHAR(2000) NULL
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionPermissions ADD
 [PortalId] INT,
 [AllowAccess] BIT,
 [Expires] DATETIME,
 [UserId] INT
GO

UPDATE {databaseOwner}{objectQualifier}DMX_PermissionPermissions
SET PortalId = -1, UserId = -10, AllowAccess = 1
GO

UPDATE {databaseOwner}{objectQualifier}DMX_PermissionPermissions
SET PortalId = p.PortalId
FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON p.PermissionId=pp.PermissionId
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionPermissions ALTER COLUMN
 [PortalId] INT NOT NULL
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionPermissions ALTER COLUMN
 [UserId] INT NOT NULL
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionPermissions DROP CONSTRAINT
	PK_{objectQualifier}DMX_PermissionPermissions
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'PK_{objectQualifier}DMX_PermissionPermissions') AND OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionPermissions ADD CONSTRAINT
	PK_{objectQualifier}DMX_PermissionPermissions PRIMARY KEY CLUSTERED 
	(
	[PortalId], [PermissionId], [RoleId], [UserId]
	) ON [PRIMARY]
GO


/****** STRUCTURE ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'FK_{objectQualifier}DMX_ApplicationPermissions_Portals') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_ApplicationPermissions
 ADD CONSTRAINT	FK_{objectQualifier}DMX_ApplicationPermissions_Portals FOREIGN KEY
	(
    PortalId
	) REFERENCES {databaseOwner}{objectQualifier}Portals
	(
    PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_AttributesML_{objectQualifier}DMX_Attributes') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_AttributesML ADD CONSTRAINT
 FK_{objectQualifier}DMX_AttributesML_{objectQualifier}DMX_Attributes FOREIGN KEY
 (
    AttributeId
 ) REFERENCES {databaseOwner}{objectQualifier}DMX_Attributes
 (
    AttributeId
 ) ON DELETE CASCADE
  NOT FOR REPLICATION

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_CategoriesML_{objectQualifier}DMX_Categories') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_CategoriesML ADD CONSTRAINT
 FK_{objectQualifier}DMX_CategoriesML_{objectQualifier}DMX_Categories FOREIGN KEY
 (
    CategoryId
 ) REFERENCES {databaseOwner}{objectQualifier}DMX_Categories
 (
    CategoryId
 ) ON DELETE CASCADE
  NOT FOR REPLICATION

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_EntriesML_{objectQualifier}DMX_Entries') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntriesML ADD CONSTRAINT
 FK_{objectQualifier}DMX_EntriesML_{objectQualifier}DMX_Entries FOREIGN KEY
 (
    EntryId
 ) REFERENCES {databaseOwner}{objectQualifier}DMX_Entries
 (
    EntryId
 ) ON DELETE CASCADE
  NOT FOR REPLICATION

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'FK_{objectQualifier}DMX_DirectAccessKeys_Entries') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
 ADD CONSTRAINT	FK_{objectQualifier}DMX_DirectAccessKeys_Entries FOREIGN KEY
	(
    EntryId
	) REFERENCES {databaseOwner}{objectQualifier}DMX_Entries
	(
    EntryId
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

/****** NEW VIEWS ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}vw_DMX_ApplicationPermissions') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions
AS
SELECT 
 ap.AllowAccess, 
 ap.Expires, 
 ap.PermissionId, 
 ap.PortalId, 
 ap.RoleId, 
 ap.UserId, 
 p.Addon, 
 p.PermissionKey, 
 p.ResourceFile, 
 ISNULL(u.Username, N'') AS Username, 
 ISNULL(u.DisplayName, N'') AS DisplayName, 
 ISNULL(r.RoleName, N'') AS RoleName
FROM {databaseOwner}{objectQualifier}DMX_ApplicationPermissions ap
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ap.PermissionId = p.PermissionId 
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON ap.UserId = u.UserID
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Roles r ON ap.RoleId = r.RoleID

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}vw_DMX_PermissionPermissions') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_PermissionPermissions
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_PermissionPermissions
AS
SELECT 
 pp.AllowAccess, 
 pp.Expires, 
 pp.PermissionId, 
 pp.PortalId, 
 pp.RoleId, 
 pp.UserId, 
 p.Addon, 
 p.PermissionKey, 
 p.ResourceFile, 
 ISNULL(u.Username, N'') AS Username, 
 ISNULL(u.DisplayName, N'') AS DisplayName, 
 ISNULL(r.RoleName, N'') AS RoleName
FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON pp.PermissionId = p.PermissionId 
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON pp.UserId = u.UserID
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Roles r ON pp.RoleId = r.RoleID

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}vw_DMX_EntriesLocalized') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_EntriesLocalized
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_EntriesLocalized
AS
SELECT
 e.*, 
 eml.Remarks AS RemarksLocalized, 
 eml.Title AS TitleLocalized, 
 eml.Locale
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e 
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}DMX_EntriesML eml ON e.EntryId = eml.EntryId

GO

IF EXISTS (select * from {databaseOwner}sysobjects where id = object_id(N'{objectQualifier}vw_DMX_Entries') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_Entries
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_Entries
AS
SELECT 
 e.Author,
 e.CollectionId,
 e.Deleted,
 e.Entry, 
 e.EntryId,
 e.EntryType,
 e.FileSize,
 e.Created, 
 e.IsApproved,
 e.Keywords,
 e.LastModified,
 e.LastVersionId, 
 e.LockedBy,
 e.LockedUntil,
 e.LockKey,
 e.MD5Hash, 
 e.OriginalFileName,
 e.Owner,
 e.PortalId,
 e.State, 
 e.Version,
 e.VersionsToKeep,
 e.WorkflowId, 
 e.Path, 
 e.PermissionsOnlyByAdmin,
 e.InheritAttributes,
 e.StorageProviderID, 
 e.Remarks,
 e.Title,
 e.Thumbnail,
 e.Hidden, 
 ext.AccessRights,
 ext.Addon,
 ext.ControlToLoad, 
 ext.Custom,
 ext.DownloadUrl,
 ext.EntryTypes, 
 ext.Icon16,
 ext.Icon32,
 ext.IsPrivate,
 ext.MimeType, 
 ext.NodePattern,
 ext.ResourceFile,
 ext.SettingsControl, 
 ext.ViewByDefault,
 ext.EditControl,
 ext.ViewControl, 
 u.DisplayName AS OwnerDisplayName,
 CASE WHEN (PATINDEX('Collection%', EntryType) > 0) THEN 1 ELSE 0 END AS IsCollection, 
 CASE WHEN LockedUntil > GETDATE() THEN 1 ELSE 0 END AS IsLocked,
 ext.ExtensionKey
FROM {databaseOwner}{objectQualifier}DMX_Entries e INNER JOIN
 {databaseOwner}{objectQualifier}DMX_Extensions ext ON e.EntryType = ext.ExtensionKey AND e.PortalId = ext.PortalId
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON e.Owner = u.UserID

GO

IF EXISTS (select * from {databaseOwner}sysobjects where id = object_id(N'{objectQualifier}vw_DMX_EntryPermissions') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_EntryPermissions
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_EntryPermissions
AS
SELECT
 ep.AllowAccess,
 ep.EntryId,
 ep.Expires, 
 ep.PermissionId,
 ep.PortalId,
 ep.RoleId, 
 ep.UserId,
 p.Addon,
 p.PermissionKey, 
 p.ResourceFile,
 ISNULL(u.Username, N'') AS Username,
 ISNULL(u.DisplayName, N'') AS DisplayName,
 ISNULL(r.RoleName, N'') AS RoleName
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ep.PermissionId = p.PermissionId
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Roles r ON ep.RoleId = r.RoleID
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON ep.UserId = u.UserID

GO

IF EXISTS (select * from {databaseOwner}sysobjects where id = object_id(N'{objectQualifier}vw_DMX_Log') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_Log
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_Log
AS
SELECT
 l.Action,
 l.Datime,
 l.EntryId,
 l.LogId,
 l.UserId, 
 l.PortalId,
 lt.LogTypeKey,
 lt.ResourceFile,
 lt.AttachFile,
 lt.CustomSend, 
 lt.NotifyApprovers,
 lt.NotifyAudit,
 lt.NotifyEditors, 
 lt.NotifyOwner,
 lt.NotifySelf,
 lt.NotifySubscribers, 
 ISNULL(u.Username, N'') AS Username,
 ISNULL(u.DisplayName, N'') AS DisplayName
FROM {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.PortalId = lt.PortalId AND l.Action = lt.LogTypeKey
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON l.UserId = u.UserID

GO

IF EXISTS (select * from {databaseOwner}sysobjects where id = object_id(N'{objectQualifier}vw_DMX_Notifications') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_Notifications
GO

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_Notifications
AS
SELECT
 n.LogId,
 n.PortalId,
 n.Sent,
 n.Template, 
 n.UserId,
 l.Action,
 l.Datime,
 l.EntryId, 
 lt.ResourceFile,
 lt.AttachFile,
 lt.CustomSend, 
 lt.NotifyApprovers,
 lt.NotifyAudit,
 lt.NotifyEditors, 
 lt.NotifyOwner,
 lt.NotifySelf,
 lt.NotifySubscribers, 
 lt.LogTypeKey
FROM {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.PortalId = lt.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Notifications n ON l.LogId = n.LogId

GO

/****** DATA MIGRATION ******/
UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
 SET AttributeName=st.Text
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts st ON st.ObjectId=a.AttributeId AND st.TypeKey='ATT' AND st.SubTypeKey='NAME' AND st.Locale=''
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_AttributesML
 (AttributeId, Locale, AttributeName)
SELECT
 st.ObjectId, st.Locale, st.Text
FROM {databaseOwner}{objectQualifier}DMX_ShortTexts st
WHERE
 st.TypeKey='ATT' 
 AND st.SubTypeKey='NAME' 
 AND NOT st.Locale=''
 AND EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_Attributes WHERE AttributeId=st.ObjectId)
 AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributesML
  WHERE AttributeId=st.ObjectId AND Locale=st.Locale)
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Categories
 SET CategoryName=st.Text
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts st ON st.ObjectId=c.CategoryId AND st.TypeKey='CAT' AND st.SubTypeKey='NAME' AND st.Locale=''
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_CategoriesML
 (CategoryId, Locale, CategoryName)
SELECT
 st.ObjectId, st.Locale, st.Text
FROM {databaseOwner}{objectQualifier}DMX_ShortTexts st
WHERE
 st.TypeKey='CAT' 
 AND st.SubTypeKey='NAME' 
 AND NOT st.Locale=''
 AND EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_Categories WHERE CategoryId=st.ObjectId)
 AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_CategoriesML 
  WHERE CategoryId=st.ObjectId AND Locale=st.Locale)
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET Title=st.Text
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts st ON st.ObjectId=e.EntryId AND st.TypeKey='ENT' AND st.SubTypeKey='TITLE' AND st.Locale=''
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_EntriesML
 (EntryId, Locale, Title)
SELECT
 st.ObjectId, st.Locale, st.Text
FROM {databaseOwner}{objectQualifier}DMX_ShortTexts st
WHERE
 st.TypeKey='ENT' 
 AND st.SubTypeKey='TITLE' 
 AND NOT st.Locale=''
 AND EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=st.ObjectId)
 AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntriesML 
  WHERE EntryId=st.ObjectId AND Locale=st.Locale)
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET Remarks=lt.Text
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LongTexts lt ON lt.ObjectId=e.EntryId AND lt.TypeKey='ENT' AND lt.SubTypeKey='REMARKS' AND lt.Locale=''
GO

UPDATE {databaseOwner}{objectQualifier}DMX_EntriesML
 SET Remarks=lt.Text
FROM {databaseOwner}{objectQualifier}DMX_EntriesML el
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LongTexts lt ON lt.ObjectId=el.EntryId AND lt.TypeKey='ENT' AND lt.SubTypeKey='REMARKS' AND lt.Locale=el.locale
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_EntriesML
 (EntryId, Locale, Remarks)
SELECT
 lt.ObjectId, lt.Locale, lt.Text
FROM {databaseOwner}{objectQualifier}DMX_LongTexts lt
WHERE
 lt.TypeKey='ENT'
 AND lt.SubTypeKey='REMARKS'
 AND NOT lt.Locale=''
 AND NOT EXISTS (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_EntriesML WHERE Locale=lt.Locale AND EntryId=lt.ObjectId)
GO

/****** DELETING OLD STUFF ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ShortTexts') AND type in (N'U'))
DROP TABLE {databaseOwner}{objectQualifier}DMX_ShortTexts
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_LongTexts') AND type in (N'U'))
DROP TABLE {databaseOwner}{objectQualifier}DMX_LongTexts
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddShortText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddShortText
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetShortText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetShortText
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetShortTexts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetShortTexts
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteShortText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteShortText
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateShortText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateShortText
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetLongText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLongText
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteLongText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLongText
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddLongText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLongText
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetLongTexts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLongTexts
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateLongText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLongText
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_AddPermissionPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermissionPermission
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeletePermissionPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionPermission
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPermissionPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermission
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPermissionPermissionsByPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermissionsByPermission
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPermissionPermissionsByRole') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermissionsByRole
GO

/****** TRIGGER ADJUSTMENT ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_EntryTriggerUpdate') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate
GO

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER UPDATE
AS

IF TRIGGER_NESTLEVEL() < 2
 AND (UPDATE(Author) OR UPDATE(CollectionId) OR UPDATE([Entry]) OR UPDATE(EntryType) 
  OR UPDATE(IsApproved) OR UPDATE(Keywords) OR UPDATE(MD5Hash) OR UPDATE(OriginalFilename)
  OR UPDATE([State]) OR UPDATE(PermissionsOnlyByAdmin) OR UPDATE(InheritAttributes)
  OR UPDATE(Title) OR UPDATE(Remarks))
BEGIN

 DECLARE @PortalId INT
 SELECT @PortalId = (SELECT TOP 1 PortalId FROM INSERTED);
 DECLARE @Now DATETIME
 SET @Now = GETDATE()

-- move: select old parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] - i.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN DELETED d ON d.CollectionId=e1.EntryId
  INNER JOIN INSERTED i ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId
 WHERE e.EntryType LIKE 'Collection%';

-- move: select new parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] + i.FileSize,
  e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId
 WHERE e.EntryType LIKE 'Collection%';

-- move: select self and children
 UPDATE e 
 SET [Path]=NULL
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN DELETED d ON e.[Path] LIKE d.[Path]+'%'
  INNER JOIN INSERTED i ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId;
 EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, -1;

-- nomove/nonewversion: select parents
 UPDATE e
 SET e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId=d.CollectionId AND i.FileSize=d.FileSize
 WHERE e.EntryType LIKE 'Collection%';

-- nomove/newversion: select parents
 UPDATE e
 SET e.LastModified = @Now,
  e.FileSize = e.FileSize - d.FileSize + i.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId=d.CollectionId AND i.FileSize<>d.FileSize;

-- move: select self
 UPDATE e
 SET e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND d.LastModified=i.LastModified;
 
END
GO

/****** CRUD PROCEDURES Entries ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
 @PortalId INT, 
 @Author NVARCHAR (500), 
 @CollectionId INT, 
 @Created DATETIME, 
 @Deleted BIT, 
 @Entry NVARCHAR (2000), 
 @EntryType NVARCHAR (150), 
 @FileSize BIGINT, 
 @Hidden BIT, 
 @InheritAttributes BIT, 
 @IsApproved BIT, 
 @Keywords NVARCHAR (255), 
 @LastVersionId INT, 
 @LockedBy INT, 
 @LockedUntil DATETIME, 
 @LockKey UNIQUEIDENTIFIER, 
 @MD5Hash NVARCHAR (50), 
 @OriginalFileName NVARCHAR (255), 
 @Owner INT, 
 @PermissionsOnlyByAdmin BIT, 
 @Remarks NVARCHAR (MAX), 
 @State NVARCHAR (50), 
 @StorageProviderID INT, 
 @Thumbnail VARCHAR (200), 
 @Title NVARCHAR (1000), 
 @Version INT, 
 @VersionsToKeep INT, 
 @WorkflowId NVARCHAR (50)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries (
 [PortalId],
 [Author],
 [CollectionId],
 [Created],
 [Deleted],
 [Entry],
 [EntryType],
 [FileSize],
 [Hidden],
 [InheritAttributes],
 [IsApproved],
 [Keywords],
 [LastVersionId],
 [LockedBy],
 [LockedUntil],
 [LockKey],
 [MD5Hash],
 [OriginalFileName],
 [Owner],
 [PermissionsOnlyByAdmin],
 [Remarks],
 [State],
 [StorageProviderID],
 [Thumbnail],
 [Title],
 [Version],
 [VersionsToKeep],
 [WorkflowId]
)
 VALUES ( @PortalId, @Author, @CollectionId, @Created, @Deleted, @Entry, @EntryType, @FileSize, @Hidden, @InheritAttributes, @IsApproved, @Keywords, @LastVersionId, @LockedBy, @LockedUntil, @LockKey, @MD5Hash, @OriginalFileName, @Owner, @PermissionsOnlyByAdmin, @Remarks, @State, @StorageProviderID, @Thumbnail, @Title, @Version, @VersionsToKeep, @WorkflowId)


select SCOPE_IDENTITY()
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
 @PortalId INT, 
 @Author NVARCHAR (500), 
 @CollectionId INT, 
 @Created DATETIME, 
 @Deleted BIT, 
 @Entry NVARCHAR (2000), 
 @EntryId INT, 
 @EntryType NVARCHAR (150), 
 @FileSize BIGINT, 
 @Hidden BIT, 
 @InheritAttributes BIT, 
 @IsApproved BIT, 
 @Keywords NVARCHAR (255), 
 @LastVersionId INT, 
 @LockedBy INT, 
 @LockedUntil DATETIME, 
 @LockKey UNIQUEIDENTIFIER, 
 @MD5Hash NVARCHAR (50), 
 @OriginalFileName NVARCHAR (255), 
 @Owner INT, 
 @PermissionsOnlyByAdmin BIT, 
 @Remarks NVARCHAR (MAX), 
 @State NVARCHAR (50), 
 @StorageProviderID INT, 
 @Thumbnail VARCHAR (200), 
 @Title NVARCHAR (1000), 
 @Version INT, 
 @VersionsToKeep INT, 
 @WorkflowId NVARCHAR (50)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
 [PortalId] = @PortalId,
 [Author] = @Author,
 [CollectionId] = @CollectionId,
 [Created] = @Created,
 [Deleted] = @Deleted,
 [Entry] = @Entry,
 [EntryType] = @EntryType,
 [FileSize] = @FileSize,
 [Hidden] = @Hidden,
 [InheritAttributes] = @InheritAttributes,
 [IsApproved] = @IsApproved,
 [Keywords] = @Keywords,
 [LastVersionId] = @LastVersionId,
 [LockedBy] = @LockedBy,
 [LockedUntil] = @LockedUntil,
 [LockKey] = @LockKey,
 [MD5Hash] = @MD5Hash,
 [OriginalFileName] = @OriginalFileName,
 [Owner] = @Owner,
 [PermissionsOnlyByAdmin] = @PermissionsOnlyByAdmin,
 [Remarks] = @Remarks,
 [State] = @State,
 [StorageProviderID] = @StorageProviderID,
 [Thumbnail] = @Thumbnail,
 [Title] = @Title,
 [Version] = @Version,
 [VersionsToKeep] = @VersionsToKeep,
 [WorkflowId] = @WorkflowId
WHERE
 [EntryId] = @EntryId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeleteEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntry
 @EntryId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE [EntryId] = @EntryId;
GO


/****** CRUD PROCEDURES NEW TABLES ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetApplicationPermissions') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissions
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissions
 @PortalId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions
WHERE
 [PortalId] = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser
 @PortalId INT,
 @UserId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions ap
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles aur ON ap.RoleId=aur.RoleId
WHERE
 ap.PortalId = @PortalId
 AND ISNULL(aur.UserId, ap.UserId) = @UserId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_SetApplicationPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetApplicationPermission
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetApplicationPermission
 @PortalId INT, 
 @AllowAccess BIT, 
 @Expires DATETIME, 
 @PermissionId INT, 
 @RoleId INT, 
 @UserId INT
AS

IF NOT EXISTS
 (SELECT * FROM {databaseOwner}{objectQualifier}DMX_ApplicationPermissions
  WHERE PortalId=@PortalId AND PermissionId=@PermissionId AND RoleId=@RoleId AND UserId=@UserId)
INSERT INTO {databaseOwner}{objectQualifier}DMX_ApplicationPermissions (
 [PortalId],
 [AllowAccess],
 [Expires],
 [PermissionId],
 [RoleId],
 [UserId])
VALUES (
 @PortalId,
 @AllowAccess,
 @Expires,
 @PermissionId,
 @RoleId,
 @UserId)
ELSE
UPDATE {databaseOwner}{objectQualifier}DMX_ApplicationPermissions SET
 [AllowAccess] = @AllowAccess,
 [Expires] = @Expires
WHERE
 [PermissionId] = @PermissionId
 AND [PortalId] = @PortalId
 AND [RoleId] = @RoleId
 AND [UserId] = @UserId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteApplicationPermissions') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteApplicationPermissions
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteApplicationPermissions
 @PortalId INT
AS

DELETE FROM
 {databaseOwner}{objectQualifier}DMX_ApplicationPermissions
WHERE
 [PortalId] = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetPermissionPermissions') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermissions
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermissions
 @PortalId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_PermissionPermissions
WHERE
 [PortalId] = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_SetPermissionPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetPermissionPermission
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetPermissionPermission
 @PortalId INT, 
 @AllowAccess BIT, 
 @Expires DATETIME, 
 @PermissionId INT, 
 @RoleId INT, 
 @UserId INT
AS

IF NOT EXISTS
 (SELECT * FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions
  WHERE PortalId=@PortalId AND PermissionId=@PermissionId AND RoleId=@RoleId AND UserId=@UserId)
INSERT INTO {databaseOwner}{objectQualifier}DMX_PermissionPermissions (
 [PortalId],
 [AllowAccess],
 [Expires],
 [PermissionId],
 [RoleId],
 [UserId])
VALUES (
 @PortalId,
 @AllowAccess,
 @Expires,
 @PermissionId,
 @RoleId,
 @UserId)
ELSE
UPDATE {databaseOwner}{objectQualifier}DMX_PermissionPermissions SET
 [AllowAccess] = @AllowAccess,
 [Expires] = @Expires
WHERE
 [PermissionId] = @PermissionId
 AND [PortalId] = @PortalId
 AND [RoleId] = @RoleId
 AND [UserId] = @UserId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeletePermissionPermissions') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionPermissions
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionPermissions
 @PortalId INT
AS

DELETE FROM
 {databaseOwner}{objectQualifier}DMX_PermissionPermissions
WHERE
 [PortalId] = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPermissionsForEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionsForEntrytype
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionsForEntrytype
 @PortalId INT,
 @EntryType NVARCHAR(100),
 @UserId INT
AS

BEGIN
IF @UserID=-4

SELECT p.*
FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes pe ON p.PermissionId=pe.PermissionId
WHERE (p.PortalId=@PortalID)
 AND @Entrytype LIKE pe.Entrytype+'%';

ELSE

SELECT p.*,
 CAST((SELECT COUNT(PermissionId) FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=pp.RoleId AND ur.UserId=@UserId
   WHERE pp.PermissionId=p.PermissionId AND pp.AllowAccess=1
    AND (NOT ur.UserId IS NULL OR pp.RoleId=-1)) AS Bit) AS IsAllowedToEdit
FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes pe ON p.PermissionId=pe.PermissionId
WHERE (p.PortalId=@PortalID)
 AND @Entrytype LIKE pe.Entrytype+'%';

END

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPermissionsByPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionsByPortal
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionsByPortal
 @PortalId INT,
 @UserId INT = -1
AS

SELECT p.*, 
 CAST((SELECT COUNT(PermissionId) FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=pp.RoleId AND ur.UserId=@UserId
   WHERE pp.PermissionId=p.PermissionId AND pp.AllowAccess=1
    AND (NOT ur.UserId IS NULL OR pp.RoleId=-1)) AS Bit) AS IsAllowedToEdit 
 FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 WHERE (p.PortalId=@PortalID)

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributeLocalizations') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeLocalizations
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeLocalizations
 @AttributeId INT
AS

SELECT *
FROM {databaseOwner}{objectQualifier}DMX_AttributesML
WHERE [AttributeId] = @AttributeId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SetAttributeLocalization') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetAttributeLocalization
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetAttributeLocalization
 @AttributeId INT,
 @Locale VARCHAR(10),
 @AttributeName NVARCHAR(200)
AS

IF @AttributeName=''
 DELETE FROM {databaseOwner}{objectQualifier}DMX_AttributesML
 WHERE [AttributeId] = @AttributeId AND [Locale] = @Locale
ELSE BEGIN
 IF EXISTS (SELECT AttributeName FROM {databaseOwner}{objectQualifier}DMX_AttributesML WHERE [AttributeId] = @AttributeId AND [Locale] = @Locale)
  UPDATE {databaseOwner}{objectQualifier}DMX_AttributesML
   SET [AttributeName] = @AttributeName
   WHERE [AttributeId] = @AttributeId AND [Locale] = @Locale
 ELSE
  INSERT INTO {databaseOwner}{objectQualifier}DMX_AttributesML
   ([AttributeId], [Locale], [AttributeName])
   VALUES (@AttributeId, @Locale, @AttributeName)
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCategoryLocalizations') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryLocalizations
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryLocalizations
 @CategoryId INT
AS

SELECT *
FROM {databaseOwner}{objectQualifier}DMX_CategoriesML
WHERE [CategoryId] = @CategoryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SetCategoryLocalization') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetCategoryLocalization
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetCategoryLocalization
 @CategoryId INT,
 @Locale VARCHAR(10),
 @CategoryName NVARCHAR(200)
AS

IF @CategoryName=''
 DELETE FROM {databaseOwner}{objectQualifier}DMX_CategoriesML
 WHERE [CategoryId] = @CategoryId AND [Locale] = @Locale
ELSE BEGIN
 IF EXISTS (SELECT CategoryName FROM {databaseOwner}{objectQualifier}DMX_CategoriesML WHERE [CategoryId] = @CategoryId AND [Locale] = @Locale)
  UPDATE {databaseOwner}{objectQualifier}DMX_CategoriesML
   SET [CategoryName] = @CategoryName
   WHERE [CategoryId] = @CategoryId AND [Locale] = @Locale
 ELSE
  INSERT INTO {databaseOwner}{objectQualifier}DMX_CategoriesML
   ([CategoryId], [Locale], [CategoryName])
   VALUES (@CategoryId, @Locale, @CategoryName)
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryLocalizations') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryLocalizations
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryLocalizations
 @EntryId INT
AS

SELECT *
FROM {databaseOwner}{objectQualifier}DMX_EntriesML
WHERE [EntryId] = @EntryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SetEntryLocalization') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryLocalization
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryLocalization
 @EntryId INT,
 @Locale VARCHAR(10),
 @Title NVARCHAR(1000),
 @Remarks NVARCHAR(MAX)
AS

IF @Title='' AND (@Remarks IS NULL OR @REMARKS = '')
 DELETE FROM {databaseOwner}{objectQualifier}DMX_EntriesML
 WHERE [EntryId] = @EntryId AND [Locale] = @Locale
ELSE BEGIN
 IF EXISTS (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_EntriesML WHERE [EntryId] = @EntryId AND [Locale] = @Locale)
  UPDATE {databaseOwner}{objectQualifier}DMX_EntriesML
   SET [Title] = @Title, [Remarks] = @Remarks
   WHERE [EntryId] = @EntryId AND [Locale] = @Locale
 ELSE
  INSERT INTO {databaseOwner}{objectQualifier}DMX_EntriesML
   ([EntryId], [Locale], [Title], [Remarks])
   VALUES (@EntryId, @Locale, @Title, @Remarks)
END
GO


/****** CRUD PROCEDURES OTHER TABLES ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetAddon') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddon
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddon
 @AddonKey NVARCHAR (50)
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Addons
WHERE
 [AddonKey] = @AddonKey
GO



IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddAddon') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAddon
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAddon
 @AddonKey NVARCHAR (50), 
 @Availability NVARCHAR (255), 
 @Controller NVARCHAR (255), 
 @Description NVARCHAR (255), 
 @Features INT, 
 @Installed DATETIME, 
 @Name NVARCHAR (50)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Addons (
 [AddonKey],
 [Availability],
 [Controller],
 [Description],
 [Features],
 [Installed],
 [Name]
)
 VALUES ( @AddonKey, @Availability, @Controller, @Description, @Features, @Installed, @Name)

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateAddon') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAddon
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAddon
 @AddonKey NVARCHAR (50), 
 @Availability NVARCHAR (255), 
 @Controller NVARCHAR (255), 
 @Description NVARCHAR (255), 
 @Features INT, 
 @Installed DATETIME, 
 @Name NVARCHAR (50)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Addons SET
 [Availability] = @Availability,
 [Controller] = @Controller,
 [Description] = @Description,
 [Features] = @Features,
 [Installed] = @Installed,
 [Name] = @Name
WHERE
 [AddonKey] = @AddonKey
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteAddon') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAddon
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAddon
 @AddonKey NVARCHAR (50)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Addons
WHERE
 [AddonKey] = @AddonKey
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetAttributeEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeEntrytype
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeEntrytype
 @AttributeId INT,
 @EntryType NVARCHAR (50)
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes
WHERE
 [AttributeId] = @AttributeId
 AND [EntryType] = @EntryType
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddAttributeEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttributeEntrytype
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttributeEntrytype
 @AttributeId INT, 
 @EntryType NVARCHAR (50)

AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes (
 [AttributeId],
 [EntryType])
VALUES (
 @AttributeId,
 @EntryType)

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytype
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytype
 @AttributeId INT,
 @EntryType NVARCHAR (50)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes
WHERE
 [AttributeId] = @AttributeId
 AND [EntryType] = @EntryType
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @AttributeName NVARCHAR (200), 
 @CollectionId INT, 
 @ControlToLoad NVARCHAR (255), 
 @IsPrivate BIT, 
 @Key NVARCHAR (50), 
 @Required BIT, 
 @ResourceFile NVARCHAR (255), 
 @ShowInUI BIT, 
 @Values NVARCHAR (2000), 
 @ValueType NVARCHAR (30), 
 @ViewOrder INT
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Attributes (
 [PortalId],
 [Addon],
 [AttributeName],
 [CollectionId],
 [ControlToLoad],
 [IsPrivate],
 [Key],
 [Required],
 [ResourceFile],
 [ShowInUI],
 [Values],
 [ValueType],
 [ViewOrder]
)
 VALUES ( @PortalId, @Addon, @AttributeName, @CollectionId, @ControlToLoad, @IsPrivate, @Key, @Required, @ResourceFile, @ShowInUI, @Values, @ValueType, @ViewOrder)


select SCOPE_IDENTITY()
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttribute
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @AttributeId INT, 
 @AttributeName NVARCHAR (200), 
 @CollectionId INT, 
 @ControlToLoad NVARCHAR (255), 
 @IsPrivate BIT, 
 @Key NVARCHAR (50), 
 @Required BIT, 
 @ResourceFile NVARCHAR (255), 
 @ShowInUI BIT, 
 @Values NVARCHAR (2000), 
 @ValueType NVARCHAR (30), 
 @ViewOrder INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Attributes SET
 [PortalId] = @PortalId,
 [Addon] = @Addon,
 [AttributeName] = @AttributeName,
 [CollectionId] = @CollectionId,
 [ControlToLoad] = @ControlToLoad,
 [IsPrivate] = @IsPrivate,
 [Key] = @Key,
 [Required] = @Required,
 [ResourceFile] = @ResourceFile,
 [ShowInUI] = @ShowInUI,
 [Values] = @Values,
 [ValueType] = @ValueType,
 [ViewOrder] = @ViewOrder
WHERE
 [AttributeId] = @AttributeId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttribute
 @AttributeId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Attributes
WHERE
 [AttributeId] = @AttributeId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddCategory
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddCategory
 @PortalId INT, 
 @CategoryName NVARCHAR (200), 
 @ParentId INT, 
 @ViewOrder INT
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Categories (
 [PortalId],
 [CategoryName],
 [ParentId],
 [ViewOrder]
)
 VALUES ( @PortalId, @CategoryName, @ParentId, @ViewOrder)


select SCOPE_IDENTITY()
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateCategory
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateCategory
 @PortalId INT, 
 @CategoryId INT, 
 @CategoryName NVARCHAR (200), 
 @ParentId INT, 
 @ViewOrder INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Categories SET
 [PortalId] = @PortalId,
 [CategoryName] = @CategoryName,
 [ParentId] = @ParentId,
 [ViewOrder] = @ViewOrder
WHERE
 [CategoryId] = @CategoryId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteCategory
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteCategory
 @CategoryId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Categories
WHERE
 [CategoryId] = @CategoryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddEntryAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryAttribute
 @AttributeId INT, 
 @EntryId INT, 
 @Value NVARCHAR (2000)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryAttributes (
 [AttributeId],
 [EntryId],
 [Value])
VALUES (
 @AttributeId,
 @EntryId,
 @Value)

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateEntryAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntryAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntryAttribute
 @AttributeId INT, 
 @EntryId INT, 
 @Value NVARCHAR (2000)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_EntryAttributes SET
 [Value] = @Value
WHERE
 [AttributeId] = @AttributeId
 AND [EntryId] = @EntryId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteEntryAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryAttribute
 @AttributeId INT,
 @EntryId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
WHERE
 [AttributeId] = @AttributeId
 AND [EntryId] = @EntryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddEntryPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryPermission
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryPermission
 @PortalId INT, 
 @AllowAccess BIT, 
 @EntryId INT, 
 @Expires DATETIME, 
 @PermissionId INT, 
 @RoleId INT, 
 @UserId INT
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions (
 [PortalId],
 [AllowAccess],
 [EntryId],
 [Expires],
 [PermissionId],
 [RoleId],
 [UserId])
VALUES (
 @PortalId,
 @AllowAccess,
 @EntryId,
 @Expires,
 @PermissionId,
 @RoleId,
 @UserId)

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteEntryPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryPermission
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryPermission
 @EntryId INT,
 @PermissionId INT,
 @RoleId INT,
 @UserId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE
 [EntryId] = @EntryId
 AND [PermissionId] = @PermissionId
 AND [RoleId] = @RoleId
 AND [UserId] = @UserId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetExtension') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtension
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtension
 @ExtensionKey NVARCHAR (50),
 @PortalId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
 [ExtensionKey] = @ExtensionKey
 AND [PortalId] = @PortalId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddExtension') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtension
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtension
 @PortalId INT, 
 @AccessRights NVARCHAR (255), 
 @Addon NVARCHAR (50), 
 @ControlToLoad NVARCHAR (255), 
 @Custom NVARCHAR (400), 
 @DownloadUrl NVARCHAR (255), 
 @EditControl NVARCHAR (255), 
 @EntryTypes NVARCHAR (255), 
 @ExtensionKey NVARCHAR (50), 
 @Icon16 NVARCHAR (100), 
 @Icon32 NVARCHAR (100), 
 @IsPrivate BIT, 
 @MimeType VARCHAR (200), 
 @NodePattern NVARCHAR (50), 
 @ResourceFile NVARCHAR (255), 
 @SettingsControl NVARCHAR (250), 
 @ViewByDefault BIT, 
 @ViewControl NVARCHAR (255)

AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Extensions (
 [PortalId],
 [AccessRights],
 [Addon],
 [ControlToLoad],
 [Custom],
 [DownloadUrl],
 [EditControl],
 [EntryTypes],
 [ExtensionKey],
 [Icon16],
 [Icon32],
 [IsPrivate],
 [MimeType],
 [NodePattern],
 [ResourceFile],
 [SettingsControl],
 [ViewByDefault],
 [ViewControl])
VALUES (
 @PortalId,
 @AccessRights,
 @Addon,
 @ControlToLoad,
 @Custom,
 @DownloadUrl,
 @EditControl,
 @EntryTypes,
 @ExtensionKey,
 @Icon16,
 @Icon32,
 @IsPrivate,
 @MimeType,
 @NodePattern,
 @ResourceFile,
 @SettingsControl,
 @ViewByDefault,
 @ViewControl)

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateExtension') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtension
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtension
 @PortalId INT, 
 @AccessRights NVARCHAR (255), 
 @Addon NVARCHAR (50), 
 @ControlToLoad NVARCHAR (255), 
 @Custom NVARCHAR (400), 
 @DownloadUrl NVARCHAR (255), 
 @EditControl NVARCHAR (255), 
 @EntryTypes NVARCHAR (255), 
 @ExtensionKey NVARCHAR (50), 
 @Icon16 NVARCHAR (100), 
 @Icon32 NVARCHAR (100), 
 @IsPrivate BIT, 
 @MimeType VARCHAR (200), 
 @NodePattern NVARCHAR (50), 
 @ResourceFile NVARCHAR (255), 
 @SettingsControl NVARCHAR (250), 
 @ViewByDefault BIT, 
 @ViewControl NVARCHAR (255)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET
 [AccessRights] = @AccessRights,
 [Addon] = @Addon,
 [ControlToLoad] = @ControlToLoad,
 [Custom] = @Custom,
 [DownloadUrl] = @DownloadUrl,
 [EditControl] = @EditControl,
 [EntryTypes] = @EntryTypes,
 [Icon16] = @Icon16,
 [Icon32] = @Icon32,
 [IsPrivate] = @IsPrivate,
 [MimeType] = @MimeType,
 [NodePattern] = @NodePattern,
 [ResourceFile] = @ResourceFile,
 [SettingsControl] = @SettingsControl,
 [ViewByDefault] = @ViewByDefault,
 [ViewControl] = @ViewControl
WHERE
 [ExtensionKey] = @ExtensionKey
 AND [PortalId] = @PortalId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteExtension') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteExtension
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteExtension
 @ExtensionKey NVARCHAR (50),
 @PortalId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
 [ExtensionKey] = @ExtensionKey
 AND [PortalId] = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetLog') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLog
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLog
 @LogId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Log
WHERE
 [LogId] = @LogId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddLog') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLog
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLog
 @PortalId INT, 
 @Action NVARCHAR (20), 
 @Datime DATETIME, 
 @EntryId INT, 
 @UserId INT
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Log (
 [PortalId],
 [Action],
 [Datime],
 [EntryId],
 [UserId]
)
 VALUES ( @PortalId, @Action, @Datime, @EntryId, @UserId)


select SCOPE_IDENTITY()
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateLog') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLog
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLog
 @PortalId INT, 
 @Action NVARCHAR (20), 
 @Datime DATETIME, 
 @EntryId INT, 
 @LogId INT, 
 @UserId INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Log SET
 [PortalId] = @PortalId,
 [Action] = @Action,
 [Datime] = @Datime,
 [EntryId] = @EntryId,
 [UserId] = @UserId
WHERE
 [LogId] = @LogId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteLog') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLog
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLog
 @LogId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Log
WHERE
 [LogId] = @LogId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetLogType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogType
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogType
 @LogTypeKey NVARCHAR (20),
 @PortalId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_LogTypes
WHERE
 [LogTypeKey] = @LogTypeKey
 AND [PortalId] = @PortalId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddLogType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLogType
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLogType
 @PortalId INT, 
 @AttachFile BIT, 
 @CustomSend BIT, 
 @LogTypeKey NVARCHAR (20), 
 @NotifyApprovers NVARCHAR (100), 
 @NotifyAudit BIT, 
 @NotifyEditors NVARCHAR (100), 
 @NotifyOwner NVARCHAR (100), 
 @NotifySelf NVARCHAR (100), 
 @NotifySubscribers NVARCHAR (100), 
 @ResourceFile NVARCHAR (150)

AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_LogTypes (
 [PortalId],
 [AttachFile],
 [CustomSend],
 [LogTypeKey],
 [NotifyApprovers],
 [NotifyAudit],
 [NotifyEditors],
 [NotifyOwner],
 [NotifySelf],
 [NotifySubscribers],
 [ResourceFile])
VALUES (
 @PortalId,
 @AttachFile,
 @CustomSend,
 @LogTypeKey,
 @NotifyApprovers,
 @NotifyAudit,
 @NotifyEditors,
 @NotifyOwner,
 @NotifySelf,
 @NotifySubscribers,
 @ResourceFile)

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateLogType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLogType
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLogType
 @PortalId INT, 
 @AttachFile BIT, 
 @CustomSend BIT, 
 @LogTypeKey NVARCHAR (20), 
 @NotifyApprovers NVARCHAR (100), 
 @NotifyAudit BIT, 
 @NotifyEditors NVARCHAR (100), 
 @NotifyOwner NVARCHAR (100), 
 @NotifySelf NVARCHAR (100), 
 @NotifySubscribers NVARCHAR (100), 
 @ResourceFile NVARCHAR (150)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes SET
 [AttachFile] = @AttachFile,
 [CustomSend] = @CustomSend,
 [NotifyApprovers] = @NotifyApprovers,
 [NotifyAudit] = @NotifyAudit,
 [NotifyEditors] = @NotifyEditors,
 [NotifyOwner] = @NotifyOwner,
 [NotifySelf] = @NotifySelf,
 [NotifySubscribers] = @NotifySubscribers,
 [ResourceFile] = @ResourceFile
WHERE
 [LogTypeKey] = @LogTypeKey
 AND [PortalId] = @PortalId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteLogType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLogType
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLogType
 @LogTypeKey NVARCHAR (20),
 @PortalId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_LogTypes
WHERE
 [LogTypeKey] = @LogTypeKey
 AND [PortalId] = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetNotification') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNotification
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNotification
 @LogId INT,
 @UserId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Notifications
WHERE
 [LogId] = @LogId
 AND [UserId] = @UserId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddNotification') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddNotification
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddNotification
 @PortalId INT, 
 @LogId INT, 
 @Sent DATETIME, 
 @Template NVARCHAR (100), 
 @UserId INT

AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Notifications (
 [PortalId],
 [LogId],
 [Sent],
 [Template],
 [UserId])
VALUES (
 @PortalId,
 @LogId,
 @Sent,
 @Template,
 @UserId)

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateNotification') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateNotification
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateNotification
 @PortalId INT, 
 @LogId INT, 
 @Sent DATETIME, 
 @Template NVARCHAR (100), 
 @UserId INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Notifications SET
 [PortalId] = @PortalId,
 [Sent] = @Sent,
 [Template] = @Template
WHERE
 [LogId] = @LogId
 AND [UserId] = @UserId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteNotification') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteNotification
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteNotification
 @LogId INT,
 @UserId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Notifications
WHERE
 [LogId] = @LogId
 AND [UserId] = @UserId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetPermissionEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionEntrytype
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionEntrytype
 @EntryType NVARCHAR (100),
 @PermissionId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes
WHERE
 [EntryType] = @EntryType
 AND [PermissionId] = @PermissionId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddPermissionEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermissionEntrytype
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermissionEntrytype
 @EntryType NVARCHAR (100), 
 @PermissionId INT

AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes (
 [EntryType],
 [PermissionId])
VALUES (
 @EntryType,
 @PermissionId)

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeletePermissionEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionEntrytype
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionEntrytype
 @EntryType NVARCHAR (100),
 @PermissionId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes
WHERE
 [EntryType] = @EntryType
 AND [PermissionId] = @PermissionId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermission
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermission
 @PermissionId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Permissions
WHERE
 [PermissionId] = @PermissionId
GO



IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermission
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermission
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @PermissionKey NVARCHAR (20), 
 @ResourceFile NVARCHAR (255)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (
 [PortalId],
 [Addon],
 [PermissionKey],
 [ResourceFile]
)
 VALUES ( @PortalId, @Addon, @PermissionKey, @ResourceFile)


select SCOPE_IDENTITY()
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdatePermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdatePermission
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdatePermission
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @PermissionId INT, 
 @PermissionKey NVARCHAR (20), 
 @ResourceFile NVARCHAR (255)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Permissions SET
 [PortalId] = @PortalId,
 [Addon] = @Addon,
 [PermissionKey] = @PermissionKey,
 [ResourceFile] = @ResourceFile
WHERE
 [PermissionId] = @PermissionId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeletePermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermission
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermission
 @PermissionId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Permissions
WHERE
 [PermissionId] = @PermissionId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvider
 @ProviderID INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Providers
WHERE
 [ProviderID] = @ProviderID
GO



IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddProvider
 @Provider NVARCHAR (255), 
 @ProviderName NVARCHAR (255), 
 @ProviderType INT
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers (
 [Provider],
 [ProviderName],
 [ProviderType]
)
 VALUES ( @Provider, @ProviderName, @ProviderType)


select SCOPE_IDENTITY()
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateProvider
 @Provider NVARCHAR (255), 
 @ProviderID INT, 
 @ProviderName NVARCHAR (255), 
 @ProviderType INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Providers SET
 [Provider] = @Provider,
 [ProviderName] = @ProviderName,
 [ProviderType] = @ProviderType
WHERE
 [ProviderID] = @ProviderID
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteProvider
 @ProviderID INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Providers
WHERE
 [ProviderID] = @ProviderID
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetSetting') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSetting
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSetting
 @PortalId INT,
 @SettingName NVARCHAR (50)
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Settings
WHERE
 [PortalId] = @PortalId
 AND [SettingName] = @SettingName
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteSetting') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSetting
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSetting
 @PortalId INT,
 @SettingName NVARCHAR (50)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Settings
WHERE
 [PortalId] = @PortalId
 AND [SettingName] = @SettingName
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetSubscription') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscription
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscription
 @EntryId INT,
 @UserId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Subscriptions
WHERE
 [EntryId] = @EntryId
 AND [UserId] = @UserId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddSubscription') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddSubscription
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddSubscription
 @EntryId INT, 
 @LastAccess DATETIME, 
 @UserId INT

AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Subscriptions (
 [EntryId],
 [LastAccess],
 [UserId])
VALUES (
 @EntryId,
 @LastAccess,
 @UserId)

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateSubscription') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateSubscription
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateSubscription
 @EntryId INT, 
 @LastAccess DATETIME, 
 @UserId INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Subscriptions SET
 [LastAccess] = @LastAccess
WHERE
 [EntryId] = @EntryId
 AND [UserId] = @UserId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteSubscription') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSubscription
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSubscription
 @EntryId INT,
 @UserId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Subscriptions
WHERE
 [EntryId] = @EntryId
 AND [UserId] = @UserId
GO

/****** OTHER PROCEDURES ******/
/****** Attributes SPROCS ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @AttributeName NVARCHAR (200), 
 @CollectionId INT, 
 @ControlToLoad NVARCHAR (255), 
 @IsPrivate BIT, 
 @Key NVARCHAR (50), 
 @Required BIT, 
 @ResourceFile NVARCHAR (255), 
 @ShowInUI BIT, 
 @Values NVARCHAR (2000), 
 @ValueType NVARCHAR (30), 
 @ViewOrder INT
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Attributes (
 [PortalId],
 [Addon],
 [AttributeName],
 [CollectionId],
 [ControlToLoad],
 [IsPrivate],
 [Key],
 [Required],
 [ResourceFile],
 [ShowInUI],
 [Values],
 [ValueType],
 [ViewOrder]
)
 VALUES ( @PortalId, @Addon, @AttributeName, @CollectionId, @ControlToLoad, @IsPrivate, @Key, @Required, @ResourceFile, @ShowInUI, @Values, @ValueType, @ViewOrder)


select SCOPE_IDENTITY()
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttribute
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @AttributeId INT, 
 @AttributeName NVARCHAR (200), 
 @CollectionId INT, 
 @ControlToLoad NVARCHAR (255), 
 @IsPrivate BIT, 
 @Key NVARCHAR (50), 
 @Required BIT, 
 @ResourceFile NVARCHAR (255), 
 @ShowInUI BIT, 
 @Values NVARCHAR (2000), 
 @ValueType NVARCHAR (30), 
 @ViewOrder INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Attributes SET
 [PortalId] = @PortalId,
 [Addon] = @Addon,
 [AttributeName] = @AttributeName,
 [CollectionId] = @CollectionId,
 [ControlToLoad] = @ControlToLoad,
 [IsPrivate] = @IsPrivate,
 [Key] = @Key,
 [Required] = @Required,
 [ResourceFile] = @ResourceFile,
 [ShowInUI] = @ShowInUI,
 [Values] = @Values,
 [ValueType] = @ValueType,
 [ViewOrder] = @ViewOrder
WHERE
 [AttributeId] = @AttributeId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetEntryAttributesByAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByAttribute
 @AttributeId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 ea.[EntryId],
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 ea.AttributeId = @AttributeId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributesByKeyAndValue') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByKeyAndValue
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByKeyAndValue
 @PortalId INT,
 @Key NVARCHAR(50),
 @AttributeValue NVARCHAR(2000),
 @Locale NVARCHAR(10)
AS

SELECT
 ea.[EntryId], 
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryAttributes ea ON ea.AttributeId=a.AttributeId
 WHERE a.PortalId=@PortalID
  AND a.[Key]=@Key
  AND ea.Value=@AttributeValue

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributesByPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
 @PortalId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName,
 ad.Description AS AddonsDescription
FROM
 {databaseOwner}{objectQualifier}DMX_Addons ad 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ad.AddonKey = a.Addon
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 a.PortalId = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributesByAddon') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByAddon
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByAddon
 @Addon NVARCHAR (50),
 @Locale NVARCHAR(10)
AS

SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 a.Addon = @Addon
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
 @AttributeId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 a.[AttributeId] = @AttributeId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttribute
 @EntryId INT,
 @AttributeId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 ea.[EntryId],
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 ea.[EntryId] = @EntryId
 AND ea.[AttributeId] = @AttributeId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributeListForEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeListForEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeListForEntry
 @PortalId INT,
 @EntryId INT,
 @EntryType NVARCHAR(50),
 @Locale NVARCHAR(10)
AS

SELECT 
 ea.[EntryId], 
 ea.[Value], 
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
 LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes WHERE EntryId=@EntryId) ea
 ON ea.AttributeId=a.AttributeId
 WHERE a.PortalId=@PortalId
  AND (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId)
   OR EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId AND @EntryType LIKE EntryType+'%'))

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributes
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributes
 @PortalId INT,
 @CollectionId INT,
 @IncludePrivate BIT,
 @Locale NVARCHAR(10)
AS

WITH tmp(EntryId , CollectionId ) AS
 (SELECT e1.EntryId, e1.CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries e1 WHERE e1.EntryId=@CollectionId AND e1.InheritAttributes=1
  UNION ALL
  SELECT e.EntryId, e.CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries e
   INNER JOIN tmp ON e.EntryId=tmp.CollectionId WHERE e.InheritAttributes=1)
SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
  LEFT JOIN tmp ON tmp.CollectionId=a.CollectionId
WHERE a.PortalId=@PortalId
 AND (a.IsPrivate=0 OR @IncludePrivate=1)
 AND (a.CollectionId=-1 OR (NOT tmp.CollectionId IS NULL) OR a.CollectionId=@CollectionId)

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributesByEntryType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByEntryType
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByEntryType
 @PortalId INT,
 @EntryType NVARCHAR(50),
 @Locale NVARCHAR(10)
AS

SELECT 
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
 WHERE a.PortalId=@PortalId
 AND a.IsPrivate=0
 AND (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId)
  OR EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId AND @EntryType LIKE EntryType+'%'))

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributeListForEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeListForEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeListForEntry
 @PortalId INT,
 @CollectionId INT,
 @EntryId INT,
 @EntryType NVARCHAR(50),
 @IncludePrivate BIT,
 @Locale NVARCHAR(10)
AS

WITH tmp (EntryId, CollectionId) AS
 (SELECT e1.EntryId, e1.CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries e1 WHERE e1.EntryId=@CollectionId AND e1.InheritAttributes=1
  UNION ALL
  SELECT e.EntryId, e.CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries e
   INNER JOIN tmp ON e.EntryId=tmp.CollectionId WHERE e.InheritAttributes=1)
SELECT
 ea.[EntryId], 
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName,
 CAST ((CASE WHEN (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId)
   OR EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId AND @EntryType LIKE EntryType+'%')) THEN 1 ELSE 0 END) AS BIT) AS Applies
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
  LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes WHERE EntryId = @EntryId) ea ON ea.AttributeId=a.AttributeId
  LEFT JOIN tmp ON tmp.CollectionId=a.CollectionId
WHERE a.PortalId=@PortalId
 AND (a.IsPrivate=0 OR @IncludePrivate=1)
 AND (a.CollectionId=-1 OR (NOT tmp.CollectionId IS NULL) OR a.CollectionId=@CollectionId)
ORDER BY CASE a.CollectionId WHEN -1 THEN 0 ELSE 1 END, a.ViewOrder

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributesByEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByEntry
 @EntryId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 ea.[EntryId],
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 ea.EntryId = @EntryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_FindAttributeByKey') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAttributeByKey
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAttributeByKey
 @Key NVARCHAR (50),
 @PortalId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 a.[Key] = @Key
 AND [PortalId] = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributeByEntryAndKey') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeByEntryAndKey
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeByEntryAndKey
 @PortalId INT,
 @EntryId INT,
 @Key NVARCHAR(50),
 @Locale NVARCHAR(10)
AS

SELECT
 ea.[EntryId], 
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
 LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes WHERE EntryId=@EntryId) ea ON ea.AttributeId=a.AttributeId
 WHERE a.PortalId=@PortalId
  AND a.[Key]=@Key

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPortalAttributeListWithEntryData') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortalAttributeListWithEntryData
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortalAttributeListWithEntryData
 @PortalId INT,
 @EntryId INT,
 @EntryType NVARCHAR(50),
 @IncludePrivate BIT,
 @Locale NVARCHAR(10)
AS

SELECT
 ea.[EntryId], 
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName,
 CAST ((CASE WHEN (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId)
   OR EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId AND @EntryType LIKE EntryType+'%')) THEN 1 ELSE 0 END) AS BIT) AS Applies
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes WHERE EntryId = @EntryId) ea ON ea.AttributeId=a.AttributeId
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
  WHERE a.PortalId=@PortalId
  AND (a.IsPrivate=0 OR @IncludePrivate=1)

GO

/****** Categories SPROCS ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddCategory
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddCategory
 @PortalId INT, 
 @CategoryName NVARCHAR (200), 
 @ParentId INT, 
 @ViewOrder INT
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Categories (
 [PortalId],
 [CategoryName],
 [ParentId],
 [ViewOrder]
)
 VALUES ( @PortalId, @CategoryName, @ParentId, @ViewOrder)


select SCOPE_IDENTITY()
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateCategory
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateCategory
 @PortalId INT, 
 @CategoryId INT, 
 @CategoryName NVARCHAR (200), 
 @ParentId INT, 
 @ViewOrder INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Categories SET
 [PortalId] = @PortalId,
 [CategoryName] = @CategoryName,
 [ParentId] = @ParentId,
 [ViewOrder] = @ViewOrder
WHERE
 [CategoryId] = @CategoryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategory
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategory
 @CategoryId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName
FROM
 {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE
 c.CategoryId = @CategoryId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategories
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategories
 @PortalId INT,
 @EntryId INT,
 @Locale NVARCHAR(6)
AS

SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_EntryCategories ec WHERE ec.EntryId=@EntryId  AND ec.CategoryId=c.CategoryId) Selected
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntryCategories ec ON c.CategoryId=ec.CategoryId AND ec.EntryId=@EntryId
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE c.PortalId=@PortalID
ORDER BY ParentId, ViewOrder
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCategoriesByEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByEntry
 @PortalId INT,
 @EntryId INT,
 @Locale NVARCHAR(6)
AS

SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryCategories ec ON c.CategoryId=ec.CategoryId AND ec.EntryId=@EntryId
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE c.PortalId=@PortalID
ORDER BY ParentId, ViewOrder
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeleteCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteCategory
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteCategory
 @CategoryId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE CategoryId=@CategoryId;
DELETE FROM {databaseOwner}{objectQualifier}DMX_Categories
WHERE CategoryId=@CategoryId;
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategories
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetSubCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubCategories
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubCategories
 @PortalId INT,
 @CategoryId INT,
 @Locale NVARCHAR(6)
AS

SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE c.PortalId=@PortalID
 AND c.ParentId=@CategoryId
ORDER BY ViewOrder, CategoryName

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCategoriesByPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByPortal
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByPortal
 @PortalId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName
FROM
 {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE
 c.PortalId = @PortalId

GO

/****** Entries SPROCS ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetBreadCrumbs') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetBreadCrumbs
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetBreadCrumbs
 @RootId INT,
 @EntryId INT,
 @Locale VARCHAR(10)
AS

DECLARE @BCPath VARCHAR(4000)
SELECT @BCPath = [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId
DECLARE @StartPath VARCHAR(4000)
SELECT @StartPath = [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@RootId
SET @StartPath = ISNULL(@StartPath, '0;') 
SET @BCPath=REPLACE(@BCPath, @StartPath, CAST(@RootId AS VARCHAR(10))+';')

SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Split(@BCPath, ';')
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=s
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE NOT s=''

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryBranch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryBranch
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryBranch
 @PortalId INT,
 @EntryId INT,
 @AllVersions BIT,
 @IncludeSelf BIT,
 @Locale NVARCHAR(6)
AS

EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;

DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;

SELECT 
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE ((e.[Path] LIKE @Path+'%') OR (e.LastVersionId=@EntryId AND @AllVersions=1 AND @IncludeSelf=1))
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId) OR @AllVersions=1)
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntriesByPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortal
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortal
 @PortalId INT,
 @IncludeDeleted BIT,
 @Locale NVARCHAR(10),
 @PageSize INT = 0,
 @StartRecord INT = 1
AS

SELECT *
FROM
(SELECT
 ROW_NUMBER() OVER (ORDER BY e.EntryId) AS rownum,
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.PortalId = @PortalId
 AND ((NOT e.Deleted = 1) OR (@IncludeDeleted = 1))) AS tmp
WHERE (@PageSize=0 OR (rownum >= @StartRecord AND rownum < @StartRecord + @PageSize))

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntriesByFilenameInCollection') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByFilenameInCollection
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByFilenameInCollection
 @PortalId INT,
 @CollectionId INT,
 @Filename NVARCHAR(255)
AS

SELECT 
 e.*
FROM {databaseOwner}{objectQualifier}DMX_Entries e
WHERE (e.CollectionId=@CollectionId OR @CollectionId=-1)
AND e.OriginalFilename=@Filename
AND PortalId=@PortalId
AND e.LastVersionId=e.EntryId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetApprovedEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApprovedEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApprovedEntry
 @LastVersionId INT,
 @PortalId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 [LastVersionId] = @LastVersionId
 AND e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE LastVersionId=e.LastVersionId AND IsApproved=1)
 AND [PortalId] = @PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetParentChain') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetParentChain
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetParentChain
 @PortalId INT,
 @EntryId INT,
 @Locale NVARCHAR(10)
AS
 DECLARE @CollectionId INT
 SELECT @CollectionId = (SELECT CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
 IF @CollectionId>0
  EXEC {databaseOwner}{objectQualifier}DMX_GetParentChain @PortalId, @CollectionId, @Locale
 SELECT 
  e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
   LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
   WHERE e.[EntryId] = @EntryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntry
 @EntryId INT,
 @Locale NVARCHAR(10)
AS

SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.[EntryId] = @EntryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_AddFolder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
 @PortalId INT,
 @CollectionId INT=0,
 @FolderName NVARCHAR(1000)='New Folder',
 @ViewRoles VARCHAR(2000)='',
 @EditRoles VARCHAR(2000)='',
 @AddRoles VARCHAR(2000)='',
 @ApproveRoles VARCHAR(2000)=''
AS

BEGIN
 DECLARE @Owner INT
 SET @Owner = (SELECT AdministratorId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
 DECLARE @NewId INT
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries
  (CollectionId, Created, Deleted, Entry, EntryType, IsApproved, Owner, PortalId, Version, PermissionsOnlyByAdmin, Title)
  VALUES
  (@CollectionId, GETDATE(), 0, '', 'Collection', 1, @Owner, @PortalId, 1, 0, @FolderName);
 SET @NewId = SCOPE_IDENTITY();
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Log
  ([Action],[Datime],[EntryId],[UserId])
 VALUES ('Add', GETDATE(), @NewId, @Owner);
 DECLARE @AdminRoleId INT
 SET @AdminRoleId = (SELECT AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
 IF @ViewRoles='' AND @EditRoles='' AND @AddRoles='' AND @ApproveRoles=''
  BEGIN
   IF @CollectionId=0
    BEGIN
     -- set Admin roles for default folder under root
     INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
      ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
     SELECT 1, @NewId, PermissionId, @PortalId, @AdminRoleId, -10
      FROM {databaseOwner}{objectQualifier}DMX_Permissions
      WHERE PortalId=@PortalId AND (PermissionKey='VIEW' OR PermissionKey='EDIT' OR PermissionKey='ADD');
    END
   ELSE
    -- Inherit permissions from parent
    INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
     ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
    SELECT 1, @NewId, [PermissionId],[PortalId],[RoleId],[UserId]
     FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
     WHERE EntryId=@CollectionId;
  END
 ELSE
  BEGIN
   -- set View roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@ViewRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='VIEW')
    WHERE NOT @ViewRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Edit roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@EditRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='EDIT')
    WHERE NOT @EditRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Add roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@AddRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='ADD')
    WHERE NOT @AddRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Approve roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@ApproveRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='APPROVE')
    WHERE NOT @ApproveRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- make sure Admin is part of this
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p.PermissionId, @PortalId, @AdminRoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Permissions p
    WHERE p.PortalId=@PortalId AND (p.PermissionKey='VIEW' OR p.PermissionKey='EDIT' OR p.PermissionKey='ADD')
    AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
        WHERE ep.PermissionId=p.PermissionId AND ep.EntryId=@NewId AND ep.RoleId=@AdminRoleId);
  END
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_FindEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_FindEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindEntry
 @PortalId INT,
 @SearchText NVARCHAR(255),
 @Locale NVARCHAR(10)
AS
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.PortalId=@PortalId AND
 (e.OriginalFilename LIKE '%'+@SearchText+'%' OR
 e.Keywords LIKE '%'+@SearchText+'%' OR
 e.Title LIKE '%'+@SearchText+'%' OR
 el.Title LIKE '%'+@SearchText+'%')
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeleteEntryBranch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryBranch
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryBranch
 @PortalId INT,
 @EntryId INT,
 @IncludeSelf BIT
AS

EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;

DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
 FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ea.EntryId=e.EntryId
 WHERE ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Attributes
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON a.CollectionId=e.EntryId
 WHERE ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
 WHERE (([Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
GO

DROP FUNCTION {databaseOwner}{objectQualifier}DMXDAV_GetPath
GO

CREATE FUNCTION {databaseOwner}{objectQualifier}DMXDAV_GetPath
 (@EntryId INT, 
  @PortalId INT, 
  @Locale NVARCHAR(10))
RETURNS NVARCHAR(4000)
AS
BEGIN
 DECLARE @res NVARCHAR(4000)
 IF EXISTS (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
 BEGIN
  DECLARE 
   @collection INT,
   @name NVARCHAR(2000),
   @orig NVARCHAR(300);
  WITH x AS (
   SELECT e.CollectionId, e.OriginalFileName, ISNULL(el.Title, e.Title) Title
   FROM {databaseOwner}{objectQualifier}DMX_Entries e
    LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
   WHERE e.EntryId=@EntryId AND e.PortalId=@PortalId)
  SELECT
   @collection = CollectionId,
   @name = Title,
   @orig = OriginalFilename
  FROM x;
  SELECT @res = {databaseOwner}{objectQualifier}DMXDAV_GetPath(@collection, @PortalId, @Locale)+'/'+@name+{databaseOwner}{objectQualifier}DMXDAV_GetExtension(@orig)
 END
ELSE
 SELECT @res = ''
RETURN @res
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_RenameEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_RenameEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RenameEntry
 @PortalId INT,
 @EntryId INT,
 @Locale NVARCHAR(10),
 @NewName NVARCHAR(1000)
AS
 IF EXISTS(SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
  BEGIN
   IF @Locale=''
    UPDATE {databaseOwner}{objectQualifier}DMX_Entries
     SET Title=@NewName WHERE EntryId=@EntryId AND PortalId=@PortalId
   ELSE BEGIN
    IF EXISTS (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_EntriesML WHERE [EntryId] = @EntryId AND [Locale] = @Locale)
     UPDATE {databaseOwner}{objectQualifier}DMX_EntriesML
     SET [Title] = @NewName
     WHERE [EntryId] = @EntryId AND [Locale] = @Locale
    ELSE
     INSERT INTO {databaseOwner}{objectQualifier}DMX_EntriesML
      ([EntryId], [Locale], [Title])
      VALUES (@EntryId, @Locale, @NewName)
   END
  END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeleteEntryBranch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryBranch
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryBranch
 @PortalId INT,
 @EntryId INT,
 @IncludeSelf BIT
AS

EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;

DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
 FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ea.EntryId=e.EntryId
 WHERE ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Attributes
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON a.CollectionId=e.EntryId
 WHERE ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
 WHERE (([Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
GO

/****** NEW SPROCS ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAllPortalCollections') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAllPortalCollections
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAllPortalCollections
 @PortalId INT,
 @UserId INT,
 @Locale VARCHAR(10)
AS

IF @UserId=-10
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  WHERE e.PortalId=@PortalId
  AND e.EntryType LIKE 'Collection%'
  ORDER BY Title
ELSE
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  INNER JOIN
   (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
     LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
     WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1))
   perm ON perm.EntryId=e.EntryId
  WHERE e.PortalId=@PortalId
  AND e.EntryType LIKE 'Collection%'
  ORDER BY Title

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryTitle') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryTitle
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryTitle
 @PortalId INT,
 @EntryId INT,
 @Locale VARCHAR(10)
AS

SELECT ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.EntryID=@EntryId
 AND e.PortalID=@PortalId
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND e2.IsApproved=1))
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCollectionContents') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCollectionContents
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCollectionContents
 @PortalId INT,
 @CollectionId INT,
 @Locale VARCHAR(10)
AS

SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND e2.IsApproved=1))
 ORDER BY Title
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCategoryByName') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryByName
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryByName
 @PortalId INT,
 @Name NVARCHAR(200),
 @Locale VARCHAR(10)
AS

SELECT c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder, ISNULL(cl.CategoryName, c.CategoryName) CategoryName
 FROM {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
 WHERE c.PortalId = @PortalID
 AND cl.CategoryName = @Name OR (cl.CategoryName IS NULL AND c.CategoryName=@Name)
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPermissionByKey') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionByKey
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionByKey
 @PermissionKey NVARCHAR(20),
 @PortalId INT
AS

SELECT p.* 
 FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 WHERE (p.PermissionKey=@PermissionKey) AND (p.PortalId=@PortalId)
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_ToggleDelete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ToggleDelete
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ToggleDelete
 @PortalId INT,
 @LastVersionId INT,
 @Delete BIT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET Deleted=@Delete
 WHERE LastVersionId=@LastVersionId
 AND PortalId=@PortalId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetUserRoles') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRoles
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRoles
 @PortalId INT,
 @UserId INT,
 @RolesString VARCHAR(100)
AS

SELECT ur.*
 FROM {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur
 INNER JOIN {databaseOwner}{objectQualifier}Roles r ON r.RoleID=ur.RoleID AND ur.UserID=@UserId
 WHERE r.PortalID=@PortalId
 AND NOT ur.UserID IS NULL
 AND ';'+@RolesString+';' LIKE '%;'+CAST(ur.RoleID AS VARCHAR(10))+';%'
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetUserRolesByRoleId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRolesByRoleId
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRolesByRoleId
 @PortalId INT,
 @RoleId INT
AS

SELECT ur.*
 FROM {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur
 INNER JOIN {databaseOwner}{objectQualifier}Roles r ON r.RoleId=ur.RoleID
 WHERE r.PortalID=@PortalId
 AND NOT ur.RoleID IS NULL
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_IsSuperUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_IsSuperUser
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_IsSuperUser
 @UserId INT
AS

SELECT ISNULL(IsSuperUser, 0) IsSuperUser
 FROM {databaseOwner}{objectQualifier}Users
 WHERE UserID=@UserId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryByLockKey') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByLockKey
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByLockKey
 @PortalId INT,
 @LockKey UNIQUEIDENTIFIER,
 @Locale VARCHAR(10)
AS

SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
  AND e.LockKey=@LockKey
  AND e.LockedUntil > GETDATE()
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryByFilename') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByFilename
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByFilename
 @PortalId INT,
 @UserId INT,
 @OriginalFileName NVARCHAR(255),
 @Locale VARCHAR(10)
AS

SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 INNER JOIN
  (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
    INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
    LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
    WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1))
  perm ON perm.EntryId=e.EntryId
 WHERE e.PortalId=@PortalId
 AND e.OriginalFileName=@OriginalFileName
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryByTitleInCollection') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
 @PortalId INT,
 @UserId INT,
 @CollectionId INT,
 @Title NVARCHAR(1000),
 @Locale VARCHAR(10)
AS

IF @UserId > -10
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 INNER JOIN
  (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
    INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
    LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
    WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1))
  perm ON perm.EntryId=e.EntryId
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
IF @UserId = -10
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryByTitle') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitle
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitle
 @PortalId INT,
 @UserId INT,
 @Title NVARCHAR(1000),
 @Locale VARCHAR(10)
AS

IF @UserId=-10
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  WHERE e.PortalId=@PortalId
  AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
  AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
ELSE
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  INNER JOIN
   (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
     LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
     WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1))
   perm ON perm.EntryId=e.EntryId
  WHERE e.PortalId=@PortalId
  AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
  AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
GO

/****** NAMESPACE CHANGES ******/
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
SET ControlToLoad='Bring2mind.DNN.Modules.DMX.Entities.Categories.CategoriesController, Bring2mind.DNN.Modules.DMX.Core'
WHERE ControlToLoad='Bring2mind.DNN.Modules.DMX.Business.CategoriesController, Bring2mind.DNN.Modules.DMX.Core'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET ControlToLoad='Bring2mind.DNN.Modules.DMX.Entities.Entries.EntriesController, Bring2mind.DNN.Modules.DMX.Core'
WHERE ControlToLoad='Bring2mind.DNN.Modules.DMX.Business.EntriesController, Bring2mind.DNN.Modules.DMX.Core'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
SET ControlToLoad='Bring2mind.DNN.Modules.DMX.Services.Approval.ApprovalController, Bring2mind.DNN.Modules.DMX.Core'
WHERE ControlToLoad='Bring2mind.DNN.Modules.DMX.Framework.ApprovalController, Bring2mind.DNN.Modules.DMX.Core'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
SET ControlToLoad='Bring2mind.DNN.Modules.DMX.Services.Subscription.SubscriptionsController, Bring2mind.DNN.Modules.DMX.Core'
WHERE ControlToLoad='Bring2mind.DNN.Modules.DMX.Business.SubscriptionsController, Bring2mind.DNN.Modules.DMX.Core'
GO


/****** SEARCH  CHANGES ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_LimitSearch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_LimitSearch
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_LimitSearch
 @PortalId Int,
 @SearchId nvarchar(50),
 @CollectionId Int,
 @OnlyLastVersion BIT,
 @HideDeleted BIT,
 @OnlyApproved BIT,
 @NrHits INT
AS

BEGIN
 IF @CollectionId > -1
 BEGIN
  DECLARE @entrypath VARCHAR(4000)
  SET @entrypath = (SELECT [Path] 
   FROM {databaseOwner}{objectQualifier}DMX_Entries
   WHERE EntryId = @CollectionId);
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
   FROM {databaseOwner}{objectQualifier}DMX_SearchResults sr
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON sr.EntryId=e.EntryId
   WHERE NOT e.[Path] LIKE @entrypath+'%';
 END
 IF @OnlyLastVersion = 1
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
   FROM {databaseOwner}{objectQualifier}DMX_SearchResults sr
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON sr.EntryId=e.EntryId
   WHERE NOT e.LastVersionId = e.EntryId;
 END
 IF @HideDeleted = 1
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
   FROM {databaseOwner}{objectQualifier}DMX_SearchResults sr
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON sr.EntryId=e.EntryId
   WHERE e.Deleted = 1;
 END
 IF @OnlyApproved = 1
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
   FROM {databaseOwner}{objectQualifier}DMX_SearchResults sr
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON sr.EntryId=e.EntryId
   WHERE e.IsApproved = 0;
 END
 IF @NrHits > 1
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
  WHERE SearchId=@SearchId
   AND Hits < @NrHits;
 END
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_ExtractConcatenate') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_ExtractConcatenate
GO

CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_ExtractConcatenate
 (@EntryId INT, @SEPARATOR NVARCHAR(8))
RETURNS NVARCHAR(2000)
AS
BEGIN
 DECLARE @RES NVARCHAR(2000)

 SELECT @RES = (SELECT ISNULL(Extract,'') AS X
  FROM {databaseOwner}{objectQualifier}DMX_SearchResults
  WHERE EntryId=@EntryId
  FOR XML PATH(''));

 SET @RES = REPLACE(@RES, '</X><X>', @SEPARATOR);
 SET @RES = REPLACE(@RES, '<X>', '');
 SET @RES = REPLACE(@RES, '</X>', '');

 RETURN @RES
END
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SearchResultsClean') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SearchResultsClean
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SearchResultsClean
 @SearchId NVARCHAR(50)
AS
BEGIN

CREATE TABLE #RES (EntryId INT, [Rank] REAL, Hits INT, Extract NVARCHAR(2000));
INSERT INTO #RES (EntryId, [Rank], Hits, Extract)
SELECT EntryId, SUM([Rank]), COUNT(EntryId), {databaseOwner}{objectQualifier}DMX_ExtractConcatenate(EntryId, ', ')
FROM {databaseOwner}{objectQualifier}DMX_SearchResults
GROUP BY SearchId, EntryId
HAVING SearchId=@SearchId;

DECLARE @maxRank REAL;
SET @maxRank = (SELECT MAX([Rank]) FROM #RES);
IF @maxRank=0
 SET @maxRank=1;

UPDATE #RES
SET [Rank] = [Rank]/@maxRank*1000;

DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
WHERE SearchId=@SearchId;

INSERT INTO {databaseOwner}{objectQualifier}DMX_SearchResults
 (SearchId, EntryId, [Rank], Hits, Extract)
SELECT
 @SearchId, EntryId, [Rank], Hits, Extract
FROM #RES;

DROP TABLE #RES;

END
GO

/****** Subscription Manager Inclusion ******/

DELETE FROM {databaseOwner}{objectQualifier}DMX_Extensions
WHERE ExtensionKey='Command.SubscriptionMgr'
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_Extensions ([AccessRights], [Addon], [ControlToLoad], [Custom], [EntryTypes], [ExtensionKey], [IsPrivate], [PortalId], [ResourceFile])
SELECT CAST(p.AdministratorRoleId AS VARCHAR(10))+';', 'Core', 'Bring2mind/DMX/Admin/SubscriptionMgr.ascx', NULL, NULL, 'Command.SubscriptionMgr', 0, p.PortalId, 'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources'
FROM {databaseOwner}{objectQualifier}Portals p
GO

/****** User and Role methods ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetUserRoles') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRoles
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRoles
 @PortalId INT,
 @UserId INT,
 @Roles VARCHAR(50)
AS

SELECT r.*
FROM {databaseOwner}{objectQualifier}Roles r
 INNER JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles aur ON r.RoleId=aur.RoleId
WHERE aur.UserId=@UserId
 AND @Roles LIKE '%;'+CAST(aur.RoleId AS VARCHAR)+';%'
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetUsersByRoleId') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersByRoleId
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersByRoleId
 @RoleId INT
AS

SELECT u.*
FROM {databaseOwner}{objectQualifier}Users u
 INNER JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles aur ON u.UserId=aur.UserId
WHERE aur.RoleId=@RoleId
GO

/****** DMX_Log use of view and column name changes ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetLogsByEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByEntry
 @EntryId INT,
 @MaxRecords INT
AS

IF @MaxRecords > 0
 BEGIN
  DECLARE @SQL varchar(max);
  SET @SQL = 'SELECT TOP ' + Convert(varchar, @MaxRecords);
  SET @SQL = @SQL + ' *';
  SET @SQL = @SQL + ' FROM {databaseOwner}{objectQualifier}vw_DMX_Log';
  SET @SQL = @SQL + ' WHERE EntryId = ' + Convert(varchar, @EntryId) + ' ORDER BY Datime DESC';
  EXEC(@SQL);
 END
ELSE
 SELECT
  *
 FROM
  {databaseOwner}{objectQualifier}vw_DMX_Log
 WHERE
  EntryId = @EntryId
  ORDER BY Datime DESC;

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetLogsByPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByPortal
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByPortal
 @PortalId Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Log
WHERE
 PortalId = @PortalId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetLogsByUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByUser
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByUser
 @UserId Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Log
WHERE
 UserId = @UserId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetLogByTime') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogByTime
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogByTime
 @PortalId Int,
 @StartTime DATETIME,
 @EndTime DATETIME
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Log
WHERE
 PortalId = @PortalId
 AND Datime >= @StartTime
 AND Datime < @EndTime

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetLastActionEvent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLastActionEvent
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLastActionEvent
 @EntryId INT,
 @Action VARCHAR(50)
AS

SELECT MAX(Datime) FROM {databaseOwner}{objectQualifier}DMX_Log
WHERE EntryId=@EntryId
AND Action=@Action

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_MoveLog') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveLog
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveLog
 @OldEntryId INT,
 @NewEntryId INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Log
SET EntryId=@NewEntryId WHERE EntryId=@OldEntryId
GO

/****** Other view changes ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryPermissionsByEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermissionsByEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermissionsByEntry
 @EntryId Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_EntryPermissions
WHERE
 EntryId = @EntryId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetUnsentNotifications') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUnsentNotifications
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUnsentNotifications
 @PortalId int,
 @UserId Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Notifications
WHERE
 [UserId] = @UserId
 AND [PortalId] = @PortalId
 AND [Sent] IS NULL

GO

/****** Various Repairs ******/
UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes
SET ResourceFile='DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources'
WHERE ResourceFile IS NULL
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_RemoveOldUserNotifications') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserNotifications
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserNotifications
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Notifications
FROM {databaseOwner}{objectQualifier}DMX_Notifications n
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Log l ON l.LogId=n.LogId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=l.EntryId
 LEFT JOIN {databaseOwner}{objectQualifier}UserPortals up ON up.UserId=n.UserId AND up.PortalId=e.PortalId
WHERE n.Sent IS NULL AND
 up.UserPortalId IS NULL
GO

/****** Set Category ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_SetEntryCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryCategory
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryCategory
 @EntryId INT, 
 @CategoryId INT
AS

IF NOT EXISTS
 (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryCategories
  WHERE EntryId=@EntryId AND CategoryId=@CategoryId)
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryCategories (
 [EntryId],
 [CategoryId])
VALUES (
 @EntryId,
 @CategoryId)
GO

/****** Mailing and Direct Access Keys ******/
INSERT INTO {databaseOwner}{objectQualifier}DMX_Extensions ([AccessRights], [Addon], [ControlToLoad], [Custom], [ExtensionKey], [Icon16], [Icon32], [IsPrivate], [MimeType], [ResourceFile], [PortalId], [ViewByDefault], [EntryTypes])
SELECT '-1;', 'Core', 'Bring2mind/DMX/Apps/Mail.ascx', Null, 'Command.Mail', Null, Null, 0, Null, 'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources', p.PortalId, 0, 'File'
FROM {databaseOwner}{objectQualifier}Portals p
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddDirectAccessKey') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddDirectAccessKey
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddDirectAccessKey
 @PortalId INT, 
 @Downloads INT, 
 @Email NVARCHAR (100), 
 @EntryId INT, 
 @Expires DATETIME, 
 @Key NVARCHAR (100), 
 @UserId INT
AS

IF EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
   WHERE PortalId=@PortalId AND [Key]=@Key)
 SELECT 0
ELSE
INSERT INTO {databaseOwner}{objectQualifier}DMX_DirectAccessKeys (
 [PortalId],
 [Downloads],
 [Email],
 [EntryId],
 [Expires],
 [Key],
 [UserId])
VALUES (
 @PortalId,
 @Downloads,
 @Email,
 @EntryId,
 @Expires,
 @Key,
 @UserId);
 SELECT 1;
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_CleanUpDirectAccessKeys') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_CleanUpDirectAccessKeys
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_CleanUpDirectAccessKeys
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
WHERE Expires < GETDATE() OR Downloads < 1
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DirectAccess') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DirectAccess
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DirectAccess
 @PortalId INT,
 @Key NVARCHAR (100)
AS

DECLARE @Result INT
SET @Result = -1

IF EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
 WHERE PortalId=@PortalId AND [Key]=@Key AND Downloads>0 AND Expires>GETDATE())
BEGIN
 SET @Result = (SELECT EntryId
 FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
 WHERE [PortalId] = @PortalId
  AND [Key] = @Key);
 UPDATE {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
 SET Downloads=Downloads-1
 WHERE PortalId=@PortalId AND [Key]=@Key;
 EXEC {databaseOwner}{objectQualifier}DMX_CleanUpDirectAccessKeys;
END

SELECT @Result

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteDirectAccessKey') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteDirectAccessKey
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteDirectAccessKey
 @PortalId INT,
 @Key NVARCHAR (100)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
WHERE
 [PortalId] = @PortalId
 AND [Key] = @Key
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetDirectAccessKeysByEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetDirectAccessKeysByEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetDirectAccessKeysByEntry
 @EntryId INT,
 @StartRowIndex INT = -1,
 @MaximumRows INT = 0,
 @OrderBy VARCHAR(100) = ''
AS

SELECT * FROM
(SELECT
 dak.*,
 ROW_NUMBER() OVER
 (ORDER BY
  CASE @OrderBy WHEN 'PORTALID DESC' THEN dak.[PortalId] END DESC,
  CASE @OrderBy WHEN 'PORTALID' THEN dak.[PortalId] END ASC,
  CASE @OrderBy WHEN 'PORTALID ASC' THEN dak.[PortalId] END ASC,
  CASE @OrderBy WHEN 'DOWNLOADS DESC' THEN dak.[Downloads] END DESC,
  CASE @OrderBy WHEN 'DOWNLOADS' THEN dak.[Downloads] END ASC,
  CASE @OrderBy WHEN 'DOWNLOADS ASC' THEN dak.[Downloads] END ASC,
  CASE @OrderBy WHEN 'EMAIL DESC' THEN dak.[Email] END DESC,
  CASE @OrderBy WHEN 'EMAIL' THEN dak.[Email] END ASC,
  CASE @OrderBy WHEN 'EMAIL ASC' THEN dak.[Email] END ASC,
  CASE @OrderBy WHEN 'ENTRYID DESC' THEN dak.[EntryId] END DESC,
  CASE @OrderBy WHEN 'ENTRYID' THEN dak.[EntryId] END ASC,
  CASE @OrderBy WHEN 'ENTRYID ASC' THEN dak.[EntryId] END ASC,
  CASE @OrderBy WHEN 'EXPIRES DESC' THEN dak.[Expires] END DESC,
  CASE @OrderBy WHEN 'EXPIRES' THEN dak.[Expires] END ASC,
  CASE @OrderBy WHEN 'EXPIRES ASC' THEN dak.[Expires] END ASC,
  CASE @OrderBy WHEN 'KEY DESC' THEN dak.[Key] END DESC,
  CASE @OrderBy WHEN 'KEY' THEN dak.[Key] END ASC,
  CASE @OrderBy WHEN 'KEY ASC' THEN dak.[Key] END ASC,
  CASE @OrderBy WHEN 'USERID DESC' THEN dak.[UserId] END DESC,
  CASE @OrderBy WHEN 'USERID' THEN dak.[UserId] END ASC,
  CASE @OrderBy WHEN 'USERID ASC' THEN dak.[UserId] END ASC
) AS RowNum
FROM
 {databaseOwner}{objectQualifier}DMX_DirectAccessKeys dak
WHERE
 dak.EntryId = @EntryId) AS Tbl
WHERE ((RowNum BETWEEN @StartRowIndex AND (@MaximumRows + @StartRowIndex - 1)) OR @StartRowIndex = 0)
 OR (@StartRowIndex = -1 AND (RowNum-1) % @MaximumRows = 0)
ORDER BY RowNum ASC

GO

-- Remove FK PortalId constraints on Permissions and Extensions
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_Permissions_{objectQualifier}Portals') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Permissions
DROP CONSTRAINT FK_{objectQualifier}DMX_Permissions_{objectQualifier}Portals
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_Extensions_{objectQualifier}Portals') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Extensions
DROP CONSTRAINT FK_{objectQualifier}DMX_Extensions_{objectQualifier}Portals
GO

-- add the global permissions and extensions
INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (Addon,PermissionKey,PortalId,ResourceFile)
VALUES (N'Core', N'DOCADMIN', -1, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources')
GO
INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (Addon,PermissionKey,PortalId,ResourceFile)
VALUES (N'Core', N'WEBDAV', -1, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources')
GO
INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (Addon,PermissionKey,PortalId,ResourceFile)
VALUES (N'Core', N'MAILING', -1, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources')
GO

-- add cleanup code to maintenance task to delete from Permissions and Extensions when portal is gone
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DBCleanup') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DBCleanup
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DBCleanup
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Permissions
FROM {databaseOwner}{objectQualifier}DMX_Permissions perm
 LEFT JOIN {databaseOwner}{objectQualifier}Portals p ON perm.PortalId=p.PortalId
WHERE perm.PortalId>-1
 AND p.PortalId IS NULL;
DELETE FROM {databaseOwner}{objectQualifier}DMX_Extensions
FROM {databaseOwner}{objectQualifier}DMX_Extensions ext
 LEFT JOIN {databaseOwner}{objectQualifier}Portals p ON ext.PortalId=p.PortalId
WHERE ext.PortalId>-1
 AND p.PortalId IS NULL;

GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([Provider],	[ProviderName],	[ProviderType])
VALUES ('Bring2mind.DNN.Modules.DMX.Services.Upload.SLUploadProvider.SLUploadProvider, BRING2MIND.DNN.MODULES.DMX','SilverlightUploadProvider',3)
GO

DELETE FROM {databaseOwner}{objectQualifier}DMX_Providers
WHERE [ProviderName]='ISRemoteSqlSearchProvider'
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_EntryTriggerInsert') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerInsert
GO

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerInsert
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER INSERT
AS

IF TRIGGER_NESTLEVEL() < 2
BEGIN

 -- select self (in root)
 UPDATE e
 SET e.[Path] = '0;' + CAST(i.EntryId AS NVARCHAR(20)) + ';',
  e.LastModified = ISNULL(e.LastModified, GETDATE()),
  e.LastVersionId = ISNULL(e.LastVersionId, e.EntryId)
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId
  WHERE i.CollectionId=0;

 -- select self (not in root)
 UPDATE e
 SET e.[Path] = e1.[Path] + CAST(i.EntryId AS NVARCHAR(20)) + ';',
  e.LastModified = ISNULL(e.LastModified, GETDATE()),
  e.LastVersionId = ISNULL(e.LastVersionId, e.EntryId)
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.EntryId LIKE e.CollectionId
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId;
 
 -- select parents where new entry
 UPDATE e
 SET e.[FileSize] = e.[FileSize] + i.FileSize,
  e.LastModified = GETDATE()
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  WHERE e.EntryType LIKE 'Collection%' AND (i.LastVersionId IS NULL OR i.LastVersionId=i.EntryId);

 -- repair missing LastVersionId
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET LastVersionId=EntryId
 WHERE LastVersionId IS NULL OR LastVersionId=-1;

END
GO

