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.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name,
 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 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_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}UserRoles ur ON ur.RoleId=ep1.RoleId
  WHERE ep1.EntryId=@EntryId AND (ur.UserID=s.UserId OR ep1.UserId=s.UserId OR ep1.RoleId=-1)
   AND Permission.PermissionKey='VIEW'
   AND (ISNULL(ur.EffectiveDate, GETDATE()) <= GETDATE())
   AND (ISNULL(ur.ExpiryDate,GETDATE()) >= GETDATE()))
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

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+'%'
 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+'%'
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_ShortTexts
FROM {databaseOwner}{objectQualifier}DMX_ShortTexts st
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=st.[ObjectId]
WHERE	st.[TypeKey] = 'ENT'	
 AND e.[Path] LIKE @Path+'%'
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_LongTexts
FROM {databaseOwner}{objectQualifier}DMX_LongTexts lt
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=lt.[ObjectId]
WHERE	lt.[TypeKey] = 'ENT'	
 AND e.[Path] LIKE @Path+'%'
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE [Path] LIKE @Path+'%'
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
GO

