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
  @AllVersions Bit, @ParentId Int, @PortalId Int, @UserId Int, @CanApprove Bit
AS

IF (@AllVersions=1 OR @CanApprove=1)

SELECT
 {databaseOwner}{objectQualifier}DMX_Entries.*,
 LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)) AS FullName,
 {databaseOwner}{objectQualifier}DMX_Extensions.ResourceFile,
 {databaseOwner}{objectQualifier}DMX_Extensions.Icon16,
 UserSubs.id,
 UserSubs.LastAccess,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Entries AS c WHERE CollectionId={objectQualifier}DMX_Entries.EntryId AND c.EntryId=c.LastVersionId) AS Children,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Entries AS c WHERE CollectionId={objectQualifier}DMX_Entries.EntryId AND c.EntryId=c.LastVersionId AND c.EntryType LIKE '%Collection%') AS Subcollections
FROM (({objectQualifier}Users RIGHT JOIN {databaseOwner}{objectQualifier}DMX_Entries ON {databaseOwner}{objectQualifier}Users.UserID = {databaseOwner}{objectQualifier}DMX_Entries.UserId) LEFT JOIN {databaseOwner}{objectQualifier}DMX_Extensions ON {databaseOwner}{objectQualifier}DMX_Entries.EntryType = {databaseOwner}{objectQualifier}DMX_Extensions.ExtensionKey) LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions WHERE {databaseOwner}{objectQualifier}DMX_Subscriptions.UserId=@UserId) AS UserSubs ON {databaseOwner}{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))

ELSE

SELECT
 {databaseOwner}{objectQualifier}DMX_Entries.*,
 LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)) AS FullName,
 {databaseOwner}{objectQualifier}DMX_Extensions.ResourceFile,
 {databaseOwner}{objectQualifier}DMX_Extensions.Icon16,
 UserSubs.id,
 UserSubs.LastAccess,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Entries AS c WHERE CollectionId={objectQualifier}DMX_Entries.EntryId AND c.EntryId=c.LastVersionId) AS Children,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Entries AS c WHERE CollectionId={objectQualifier}DMX_Entries.EntryId AND c.EntryId=c.LastVersionId AND c.EntryType LIKE '%Collection%') AS Subcollections
FROM (({objectQualifier}Users RIGHT JOIN 
({objectQualifier}DMX_Entries INNER JOIN
(SELECT
 MAX(Version) AS LastVersion,
 LastVersionId
FROM 
{objectQualifier}DMX_Entries 
GROUP BY
 LastVersionId,
 PortalId,
 CollectionId,
 IsApproved
HAVING 
((({objectQualifier}DMX_Entries.CollectionId)=@ParentId) 
AND ({objectQualifier}DMX_Entries.IsApproved=1)
AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId)))
 AS EntrySelect ON (EntrySelect.LastVersion={objectQualifier}DMX_Entries.Version AND EntrySelect.LastVersionId={objectQualifier}DMX_Entries.LastVersionId))
ON {databaseOwner}{objectQualifier}Users.UserID = {databaseOwner}{objectQualifier}DMX_Entries.UserId) LEFT JOIN {databaseOwner}{objectQualifier}DMX_Extensions ON {databaseOwner}{objectQualifier}DMX_Entries.EntryType = {databaseOwner}{objectQualifier}DMX_Extensions.ExtensionKey) LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions WHERE {databaseOwner}{objectQualifier}DMX_Subscriptions.UserId=@UserId) AS UserSubs ON {databaseOwner}{objectQualifier}DMX_Entries.EntryId = UserSubs.EntryId
WHERE ({objectQualifier}DMX_Extensions.PortalId=@PortalId)

GO


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

CREATE PROCEDURE dbo.{objectQualifier}DMX_GetVersionList
  @LastVersionId Int, @PortalId Int, @CanApprove Bit
AS

SELECT {databaseOwner}{objectQualifier}DMX_Entries.*, LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)) AS FullName
FROM {databaseOwner}{objectQualifier}DMX_Entries LEFT JOIN {databaseOwner}{objectQualifier}Users ON {databaseOwner}{objectQualifier}DMX_Entries.UserId={objectQualifier}Users.UserID
WHERE 
((({objectQualifier}DMX_Entries.LastVersionId)=@LastVersionId)
 AND (({objectQualifier}DMX_Entries.IsApproved=1) OR (@CanApprove=1))
 AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId))
ORDER BY {databaseOwner}{objectQualifier}DMX_Entries.DateSubmitted DESC


GO


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

CREATE PROCEDURE dbo.{objectQualifier}DMX_GetLatestApprovedEntry
	@EntryId Int,
    @PortalId int
AS

SELECT
	[Author],
	[Categories],
	[CollectionId],
	[DateSubmitted],
	[Deleted],
	[Description],
	[EditRoles],
	[Entry],
	[EntryId],
	[EntryType],
	[FileSize],
	[IsApproved],
	[IsLocked],
	[IsPrivate],
	[Keywords],
	[LastModified],
	[LastVersionId],
	[LockedBy],
	[OriginalFileName],
	[PortalId],
	[Remarks],
	[UserId],
	[Version],
	[ViewRoles]
FROM
 {databaseOwner}{objectQualifier}DMX_Entries INNER JOIN
  (SELECT MAX(Version) AS LastVersion, LastVersionId AS LVI
   FROM {databaseOwner}{objectQualifier}DMX_Entries
   GROUP BY LastVersionId, IsApproved
   HAVING ({objectQualifier}DMX_Entries.LastVersionId=(SELECT LastVersionId AS LVI FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)  AND IsApproved=1))
  AS LastEntry
  ON 
   (LastEntry.LastVersion = {databaseOwner}{objectQualifier}DMX_Entries.Version
   AND LastEntry.LVI = {databaseOwner}{objectQualifier}DMX_Entries.LastVersionId)
WHERE
	[PortalId] = @PortalId

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeByEntryAndAttributeName
  @PortalId Int, @AttributeName NVarChar (50), @EntryId Int
AS

SELECT {databaseOwner}{objectQualifier}DMX_EntryAttributes.*
FROM {databaseOwner}{objectQualifier}DMX_Attributes INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryAttributes ON {databaseOwner}{objectQualifier}DMX_Attributes.id={objectQualifier}DMX_EntryAttributes.Attribute
WHERE ((({objectQualifier}DMX_Attributes.PortalId)=@PortalId) AND (({objectQualifier}DMX_Attributes.Name)=@AttributeName) AND (({objectQualifier}DMX_EntryAttributes.EntryId)=@EntryId))

GO

