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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCollectionContents
  @AllVersions Bit, @ParentId Int, @PortalId Int, @UserId Int
AS

SELECT {objectQualifier}DMX_Entries.*, LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)) AS FullName, {objectQualifier}DMX_Extensions.Name, {objectQualifier}DMX_Extensions.Icon16, UserSubs.id, UserSubs.LastAccess
FROM (({objectQualifier}Users INNER JOIN {objectQualifier}DMX_Entries ON {objectQualifier}Users.UserID = {objectQualifier}DMX_Entries.UserId) LEFT JOIN {objectQualifier}DMX_Extensions ON {objectQualifier}DMX_Entries.EntryType = {objectQualifier}DMX_Extensions.ExtensionKey) LEFT JOIN (SELECT * FROM {objectQualifier}DMX_Subscriptions WHERE {objectQualifier}DMX_Subscriptions.UserId=@UserId) AS UserSubs ON {objectQualifier}DMX_Entries.EntryId = UserSubs.EntryId
WHERE (((({objectQualifier}DMX_Entries.EntryId)=([{objectQualifier}DMX_Entries].[LastVersionId])) OR (@AllVersions=1))
AND (({objectQualifier}DMX_Entries.CollectionId)=@ParentId) 
AND (({objectQualifier}DMX_Extensions.PortalId)=@PortalId)
AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId))

GO

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

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

SELECT {objectQualifier}DMX_Entries.Description, {objectQualifier}DMX_Entries.EntryId, {objectQualifier}DMX_Entries.CollectionId, {objectQualifier}DMX_Entries.EditRoles, {objectQualifier}DMX_Entries.ViewRoles, {objectQualifier}DMX_Extensions.Name, {objectQualifier}DMX_Extensions.Icon16
FROM {objectQualifier}DMX_Entries LEFT JOIN {objectQualifier}DMX_Extensions ON {objectQualifier}DMX_Entries.EntryType={objectQualifier}DMX_Extensions.ExtensionKey
WHERE (((LEFT({objectQualifier}DMX_Entries.EntryType,10)='Collection') Or (LEFT({objectQualifier}DMX_Entries.EntryType,17)='Custom.Collection')) And (({objectQualifier}DMX_Entries.Deleted)=0) And (({objectQualifier}DMX_Entries.PortalId)=@PortalId)
And (({objectQualifier}DMX_Extensions.PortalId)=@PortalId))
ORDER BY {objectQualifier}DMX_Entries.Description

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetDeleteList
  @LastVersionId Int, @MaxVersion Int, @PortalId Int
AS

SELECT {objectQualifier}DMX_Entries.Entry
FROM {objectQualifier}DMX_Entries
GROUP BY {objectQualifier}DMX_Entries.Entry, {objectQualifier}DMX_Entries.LastVersionId, {objectQualifier}DMX_Entries.PortalId
HAVING ((({objectQualifier}DMX_Entries.LastVersionId)=@LastVersionId) AND ((Max({objectQualifier}DMX_Entries.Version))<@MaxVersion) AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId))

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateDeleteList
  @LastVersionId Int, @MaxVersion Int, @PortalId Int
AS

UPDATE {objectQualifier}DMX_Entries

SET {objectQualifier}DMX_Entries.Entry = ISNULL(NULLIF({objectQualifier}DMX_Entries.Entry, 
(SELECT {objectQualifier}DMX_Entries.Entry
FROM {objectQualifier}DMX_Entries
GROUP BY {objectQualifier}DMX_Entries.Entry, {objectQualifier}DMX_Entries.LastVersionId, {objectQualifier}DMX_Entries.PortalId
HAVING ((({objectQualifier}DMX_Entries.LastVersionId)=@LastVersionId) AND ((Max({objectQualifier}DMX_Entries.Version))<@MaxVersion) AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId)))),
''),
{objectQualifier}DMX_Entries.Deleted = 1

WHERE ((({objectQualifier}DMX_Entries.LastVersionId)=@LastVersionId) AND (({objectQualifier}DMX_Entries.Version)<@MaxVersion) AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId))

GO



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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryContents
  @CategoryId Int, @PortalId Int, @UserId Int
AS

SELECT {objectQualifier}DMX_Entries.*, LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)) AS FullName, {objectQualifier}DMX_Extensions.Name, {objectQualifier}DMX_Extensions.Icon16, Entries_1.Description AS CollectionName, UserSubs.id, UserSubs.LastAccess
FROM ((({objectQualifier}Users INNER JOIN {objectQualifier}DMX_Entries ON {objectQualifier}Users.UserID={objectQualifier}DMX_Entries.UserId) LEFT JOIN {objectQualifier}DMX_Extensions ON {objectQualifier}DMX_Entries.EntryType={objectQualifier}DMX_Extensions.ExtensionKey) LEFT JOIN {objectQualifier}DMX_Entries AS Entries_1 ON {objectQualifier}DMX_Entries.CollectionId=Entries_1.EntryId) LEFT JOIN (SELECT * FROM {objectQualifier}DMX_Subscriptions WHERE {objectQualifier}DMX_Subscriptions.UserId=@UserId) AS UserSubs ON {objectQualifier}DMX_Entries.EntryId=UserSubs.EntryId
WHERE ((({objectQualifier}DMX_Entries.EntryId)=({objectQualifier}DMX_Entries.LastVersionId))
And (';' + {objectQualifier}DMX_Entries.Categories + ';' LIKE '%;' + LTRIM(RTRIM(STR(@CategoryId))) + ';%')
AND (({objectQualifier}DMX_Extensions.PortalId)=@PortalId)
AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId))

GO

