if exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_Entries_{objectQualifier}DMX_Users') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries
  DROP CONSTRAINT FK_{objectQualifier}DMX_Entries_{objectQualifier}DMX_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_Log_{objectQualifier}DMX_Users') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Log
  DROP CONSTRAINT FK_{objectQualifier}DMX_Log_{objectQualifier}DMX_Users
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 RIGHT 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


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogForEntry
  @LastVersionId Int
AS

SELECT {objectQualifier}DMX_Log.*, LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)) AS FullName, {objectQualifier}DMX_Entries.Version, {objectQualifier}DMX_LogTypes.ResourceFile
FROM {objectQualifier}Users RIGHT JOIN ({objectQualifier}DMX_LogTypes INNER JOIN ({objectQualifier}DMX_Entries INNER JOIN {objectQualifier}DMX_Log ON {objectQualifier}DMX_Entries.EntryId = {objectQualifier}DMX_Log.EntId) ON {objectQualifier}DMX_LogTypes.LogTypeKey = {objectQualifier}DMX_Log.Operation) ON {objectQualifier}Users.UserID = {objectQualifier}DMX_Log.UserId
WHERE ((({objectQualifier}DMX_Entries.LastVersionId)=@LastVersionId))
ORDER BY {objectQualifier}DMX_Log.Datime DESC

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogForItem
  @EntryId Int
AS

SELECT {objectQualifier}DMX_Log.*, LTrim(RTrim({objectQualifier}Users.FirstName+' '+{objectQualifier}Users.LastName)) AS FullName, {objectQualifier}DMX_Entries.Version, {objectQualifier}DMX_LogTypes.ResourceFile
FROM {objectQualifier}Users RIGHT JOIN ({objectQualifier}DMX_LogTypes INNER JOIN ({objectQualifier}DMX_Entries INNER JOIN {objectQualifier}DMX_Log ON {objectQualifier}DMX_Entries.EntryId = {objectQualifier}DMX_Log.EntId) ON {objectQualifier}DMX_LogTypes.LogTypeKey = {objectQualifier}DMX_Log.Operation) ON {objectQualifier}Users.UserID = {objectQualifier}DMX_Log.UserId
WHERE ((({objectQualifier}DMX_Entries.EntryId)=@EntryId))
ORDER BY {objectQualifier}DMX_Log.Datime DESC

GO

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

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

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

GO

