DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCollectionContents
GO

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

SELECT {databaseOwner}{objectQualifier}DMX_Entries.EntryId, {databaseOwner}{objectQualifier}DMX_Entries.EntryType, {databaseOwner}{objectQualifier}DMX_Entries.Entry, {databaseOwner}{objectQualifier}DMX_Entries.Description, {databaseOwner}{objectQualifier}DMX_Entries.DateSubmitted, {databaseOwner}{objectQualifier}DMX_Entries.ViewRoles, {databaseOwner}{objectQualifier}DMX_Entries.EditRoles, {databaseOwner}{objectQualifier}DMX_Entries.Deleted, {databaseOwner}{objectQualifier}DMX_Entries.FileSize, {databaseOwner}{objectQualifier}DMX_Entries.Version, {databaseOwner}{objectQualifier}DMX_Entries.Author, {databaseOwner}{objectQualifier}DMX_Entries.IsApproved, {databaseOwner}{objectQualifier}DMX_Entries.IsLocked, {databaseOwner}{objectQualifier}DMX_Entries.LastVersionId, LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)) AS FullName, Max({objectQualifier}DMX_Log.Datime) AS LastModified
FROM {databaseOwner}{objectQualifier}Users INNER JOIN ({objectQualifier}DMX_Entries LEFT JOIN {databaseOwner}{objectQualifier}DMX_Log ON {databaseOwner}{objectQualifier}DMX_Entries.EntryId={objectQualifier}DMX_Log.EntId) ON {databaseOwner}{objectQualifier}Users.UserID={objectQualifier}DMX_Entries.UserId
WHERE ((({objectQualifier}DMX_Log.Operation)<>4 And ({objectQualifier}DMX_Log.Operation)<>5 And ({objectQualifier}DMX_Log.Operation)<>6))
GROUP BY {databaseOwner}{objectQualifier}DMX_Entries.EntryId, {databaseOwner}{objectQualifier}DMX_Entries.EntryType, {databaseOwner}{objectQualifier}DMX_Entries.Entry, {databaseOwner}{objectQualifier}DMX_Entries.Description, {databaseOwner}{objectQualifier}DMX_Entries.DateSubmitted, {databaseOwner}{objectQualifier}DMX_Entries.ViewRoles, {databaseOwner}{objectQualifier}DMX_Entries.EditRoles, {databaseOwner}{objectQualifier}DMX_Entries.Deleted, {databaseOwner}{objectQualifier}DMX_Entries.FileSize, {databaseOwner}{objectQualifier}DMX_Entries.Version, {databaseOwner}{objectQualifier}DMX_Entries.Author, {databaseOwner}{objectQualifier}DMX_Entries.IsApproved, {databaseOwner}{objectQualifier}DMX_Entries.IsLocked, {databaseOwner}{objectQualifier}DMX_Entries.LastVersionId, LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)), {databaseOwner}{objectQualifier}DMX_Entries.CollectionId, {databaseOwner}{objectQualifier}DMX_Entries.PortalId, {databaseOwner}{objectQualifier}DMX_Entries.EntryId
HAVING ((({objectQualifier}DMX_Entries.EntryId)=({objectQualifier}DMX_Entries.LastVersionId)) And (({objectQualifier}DMX_Entries.CollectionId)=@ParentId) And (({objectQualifier}DMX_Entries.PortalId)=@PortalId))

GO



DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAllPortalCollections
GO

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

SELECT {databaseOwner}{objectQualifier}DMX_Entries.Description, {databaseOwner}{objectQualifier}DMX_Entries.EntryId, {databaseOwner}{objectQualifier}DMX_Entries.CollectionId, {databaseOwner}{objectQualifier}DMX_Entries.EditRoles, {databaseOwner}{objectQualifier}DMX_Entries.ViewRoles
FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE ((({objectQualifier}DMX_Entries.EntryType) LIKE 'Collection') AND ((NOT {databaseOwner}{objectQualifier}DMX_Entries.Deleted = 1)) AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId))
ORDER BY {databaseOwner}{objectQualifier}DMX_Entries.Description

GO


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

SELECT {databaseOwner}{objectQualifier}DMX_Entries.Description, {databaseOwner}{objectQualifier}DMX_Entries.CollectionId AS CollectionId0, Entries_1.Description AS Description1, Entries_1.CollectionId AS CollectionId1, Entries_2.Description AS Description2, Entries_2.CollectionId AS CollectionId2, Entries_3.Description AS Description3, Entries_3.CollectionId AS CollectionId3, Entries_4.Description AS Description4, Entries_4.CollectionId AS CollectionId4
FROM ((({objectQualifier}DMX_Entries LEFT JOIN {databaseOwner}{objectQualifier}DMX_Entries AS Entries_1 ON {databaseOwner}{objectQualifier}DMX_Entries.CollectionId=Entries_1.EntryId) LEFT JOIN {databaseOwner}{objectQualifier}DMX_Entries AS Entries_2 ON Entries_1.CollectionId=Entries_2.EntryId) LEFT JOIN {databaseOwner}{objectQualifier}DMX_Entries AS Entries_3 ON Entries_2.CollectionId=Entries_3.EntryId) LEFT JOIN {databaseOwner}{objectQualifier}DMX_Entries AS Entries_4 ON Entries_3.CollectionId=Entries_4.EntryId
WHERE ((({objectQualifier}DMX_Entries.EntryId)=@EntryId) AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId))

GO



