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.*, ISNULL(stLoc.[Text], stDef.[Text]) Name
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryCategories ec ON c.id=ec.CategoryId AND ec.EntryId=@EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stDef ON c.Id=stDef.ObjectId AND stDef.TypeKey='CAT' AND stDef.SubTypeKey='NAME' AND stDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stLoc ON c.Id=stLoc.ObjectId AND stLoc.TypeKey='CAT' AND stLoc.SubTypeKey='NAME' AND stLoc.Locale=@Locale
WHERE c.PortalId=@PortalID
ORDER BY ParentId, ViewOrder
GO

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

CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles
AS
SELECT ur.UserID, ur.RoleID
FROM {databaseOwner}{objectQualifier}UserRoles ur
WHERE 
 ISNULL(ur.EffectiveDate, DATEADD(day, -1, GETDATE())) < GETDATE()
 AND ISNULL(ur.ExpiryDate, DATEADD(day, 1, GETDATE())) > GETDATE()
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserPermissions
	@EntryId Int,
	@PortalId Int,
	@UserId Int
AS

SELECT DISTINCT p.PermissionKey
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ep.PermissionId=p.PermissionId
LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ep.RoleId=ur.RoleID AND ur.UserId=@UserId
LEFT JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=@UserId
WHERE ep.EntryId=@EntryId
AND ep.PortalId=@PortalId
AND p.PortalId=@PortalId
AND (ep.RoleID=-1 OR NOT ur.UserId IS NULL OR ep.UserId=@UserId OR u.IsSuperUser=1)

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifyPermissions
 @PortalId Int,
 @EntryId Int,
 @UserId INT
AS
DECLARE @BasePath VARCHAR(2000)
SELECT @BasePath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
IF EXISTS (select [name] from tempdb..sysobjects where [name] LIKE '#ToChange%')
 DROP TABLE #ToChange;
CREATE TABLE #ToChange 
 (EntryId INT, [Path] VARCHAR(2000), EntryType nvarchar(150));
INSERT INTO #ToChange
 (EntryId, [Path], EntryType)
SELECT e.EntryId, e.[Path], e.EntryType FROM {databaseOwner}{objectQualifier}DMX_Entries e INNER JOIN
 (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId
   WHERE pt.PermissionKey='EDIT' AND (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1)) perm ON perm.EntryId=e.EntryId
 WHERE PortalId=@PortalId AND [Path] LIKE @BasePath+'_%';
IF EXISTS (select [name] from tempdb..sysobjects where [name] LIKE '#NewPerms%')
 DROP TABLE #NewPerms;
CREATE TABLE #NewPerms (
	[AllowAccess] [bit] NULL,
	[EntryId] [int] NOT NULL,
	[Expires] [datetime] NULL,
	[PermissionId] [int] NOT NULL,
	[PortalId] [int] NOT NULL,
	[RoleId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
    [EntryType] nvarchar(150))
INSERT INTO #NewPerms
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId, EntryType)
SELECT ep.AllowAccess, ep.EntryId, ep.Expires, ep.PermissionId, ep.PortalId, ep.RoleId, ep.UserId, pet.EntryType
 FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntryTypes pet ON pet.PermissionId=ep.PermissionId
 WHERE ep.EntryId=@EntryId
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
 WHERE EntryId IN (SELECT EntryId FROM #ToChange);
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId)
 SELECT ep.AllowAccess, tc.EntryId, ep.Expires, ep.PermissionId, ep.PortalId, ep.RoleId, ep.UserId
  FROM #NewPerms ep
  INNER JOIN #ToChange tc ON tc.EntryType LIKE ep.EntryType+'%';
DROP TABLE #NewPerms;
DROP TABLE #ToChange;
GO



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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryListAsXml
 @EntryList VARCHAR(300),
 @UserId INT
AS

SELECT DISTINCT Entry.Author, Entry.CollectionId, Entry.Created, Entry.Deleted, Entry.Entry, 
Entry.EntryId, Entry.EntryType, Entry.FileSize, Entry.IsApproved, Entry.Keywords, 
Entry.LastVersionId, Entry.LockedBy, Entry.OriginalFileName, Entry.PortalId, Entry.Owner, 
Entry.Version, Entry.LastModified, Entry.LockedUntil, Entry.LockKey, Entry.MD5Hash, Entry.State, 
Entry.VersionsToKeep, Entry.WorkflowId, 
REPLACE({databaseOwner}{objectQualifier}DMXDAV_URLEncode({databaseOwner}{objectQualifier}DMXDAV_GetPath(Entry.EntryId, Entry.PortalId, '')), '''', '\''') AS FullPath, 
CAST (CASE WHEN Entry.LockedUntil > GETDATE() THEN 1 ELSE 0 END AS BIT) AS IsLocked,
CAST (CASE WHEN subs.EntryID IS NULL THEN 0 ELSE 1 END AS BIT) AS Subscription,
Permission.PermissionKey 
FROM (SELECT DMXEntry.*, 
ext.ControlToLoad, ext.Custom, ext.DownloadUrl, ext.EntryTypes, ext.Icon16, 
ext.Icon32, ext.IsPrivate, ext.MimeType, ext.ResourceFile, ext.SettingsControl, 
ext.ViewByDefault
FROM (SELECT s FROM {databaseOwner}{objectQualifier}DMX_Split(@EntryList,';')) recs
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries DMXEntry ON recs.s=DMXEntry.EntryId
INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON ext.ExtensionKey=DMXEntry.EntryType AND DMXEntry.PortalId=ext.PortalId) AS Entry 
INNER JOIN (SELECT ep1.EntryId, Permission.PermissionKey
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep1
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions Permission ON Permission.PermissionId=ep1.PermissionId
LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep1.RoleId AND r.UserId=@UserId WHERE (ep1.UserId=@UserId OR NOT r.UserId IS NULL OR ep1.RoleId=-1)) Permission
ON Permission.EntryId=Entry.EntryId 
LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions WHERE UserID=@UserId) subs ON subs.EntryId=Entry.EntryId
FOR XML AUTO

GO

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

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

BEGIN
 DECLARE @BasePath VARCHAR(2000)
 SELECT @BasePath = ';'+(SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID);
 SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions s
 WHERE @BasePath LIKE '%;'+CAST(s.EntryId AS VARCHAR(10))+';%'
 AND EXISTS (SELECT ep1.EntryId 
  FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep1
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions Permission ON Permission.PermissionId=ep1.PermissionId 
  LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=ep1.RoleId AND ur.UserID=s.UserId
  WHERE ep1.EntryId=@EntryId AND (NOT ur.UserId IS NULL OR ep1.UserId=s.UserId OR ep1.RoleId=-1)
   AND Permission.PermissionKey='VIEW')
END
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 (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_GetSpecificEntryPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSpecificEntryPermission
GO

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

SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ep.PermissionId=p.PermissionId
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ep.RoleId=ur.RoleId AND ur.UserId=@UserId
WHERE (ep.UserID=@UserId
 OR NOT ur.UserId IS NULL
 OR ep.RoleId=-1)
 AND ep.PortalId=@PortalId
 AND ep.EntryId=@EntryId
 AND p.PermissionKey=@PermissionKey

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 (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_GetEntryPermissionsForUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermissionsForUser
GO

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

SELECT p.* FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryPermissions ep ON p.PermissionId=ep.PermissionId 
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=ep.RoleId AND ur.UserId=@UserId
WHERE 
 (ep.RoleId=-1 OR NOT ur.UserId IS NULL OR ep.UserId=@UserId) 
 AND (ep.EntryId=@EntryId) 
 AND (ep.PortalId=@PortalId)

GO

IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'{objectQualifier}DMX_EntryPermissions') and [name]=N'{objectQualifier}IX_DMX_EntryPermissions_PermissionId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_EntryPermissions_PermissionId
 ON {databaseOwner}{objectQualifier}DMX_EntryPermissions ([PermissionId])
GO

IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'{objectQualifier}DMX_EntryPermissions') and [name]=N'{objectQualifier}IX_DMX_EntryPermissions_RoleId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_EntryPermissions_RoleId
 ON {databaseOwner}{objectQualifier}DMX_EntryPermissions ([RoleId])
GO

IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'{objectQualifier}DMX_EntryAttributes') and [name]=N'{objectQualifier}IX_DMX_EntryAttributes_EntryId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_EntryAttributes_EntryId
 ON {databaseOwner}{objectQualifier}DMX_EntryAttributes ([EntryId])
GO

IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'{objectQualifier}DMX_EntryCategories') and [name]=N'{objectQualifier}IX_DMX_EntryCategories_EntryId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_EntryCategories_EntryId
 ON {databaseOwner}{objectQualifier}DMX_EntryCategories ([EntryId])
GO

IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'{objectQualifier}DMX_Log') and [name]=N'{objectQualifier}IX_DMX_Log_EntId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_Log_EntId
 ON {databaseOwner}{objectQualifier}DMX_Log ([EntId])
GO

IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'{objectQualifier}DMX_ShortTexts') and [name]=N'{objectQualifier}IX_DMX_ShortTexts_ObjectId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_ShortTexts_ObjectId
 ON {databaseOwner}{objectQualifier}DMX_ShortTexts ([ObjectId])
GO

IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'{objectQualifier}DMX_LongTexts') and [name]=N'{objectQualifier}IX_DMX_LongTexts_ObjectId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_LongTexts_ObjectId
 ON {databaseOwner}{objectQualifier}DMX_LongTexts ([ObjectId])
GO

IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'{objectQualifier}DMX_PermissionPermissions') and [name]=N'{objectQualifier}IX_DMX_PermissionPermissions_RoleId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_PermissionPermissions_RoleId
 ON {databaseOwner}{objectQualifier}DMX_PermissionPermissions ([RoleId])
GO

