ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries ADD
PublishFrom DATE NOT NULL DEFAULT '1753-1-1',
PublishTo DATE NOT NULL DEFAULT '3000-1-1'
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
 @PortalId INT, 
 @Author NVARCHAR (500), 
 @CollectionId INT, 
 @Created DATETIME, 
 @Deleted BIT, 
 @Entry NVARCHAR (2000), 
 @EntryType NVARCHAR (150), 
 @FileSize BIGINT, 
 @Hidden BIT, 
 @InheritAttributes BIT, 
 @IsApproved BIT, 
 @Keywords NVARCHAR (255), 
 @LastVersionId INT, 
 @LockedBy INT, 
 @LockedUntil DATETIME, 
 @LockKey UNIQUEIDENTIFIER, 
 @MD5Hash NVARCHAR (50), 
 @OriginalFileName NVARCHAR (255), 
 @Owner INT, 
 @PermissionsOnlyByAdmin BIT, 
 @Remarks NVARCHAR (MAX), 
 @State NVARCHAR (50), 
 @StorageProviderID INT, 
 @Thumbnail VARCHAR (200), 
 @Title NVARCHAR (1000), 
 @Version INT, 
 @VersionsToKeep INT, 
 @WorkflowId NVARCHAR (50),
 @PublishFrom DATE,
 @PublishTo DATE
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries (
 [PortalId],
 [Author],
 [CollectionId],
 [Created],
 [Deleted],
 [Entry],
 [EntryType],
 [FileSize],
 [Hidden],
 [InheritAttributes],
 [IsApproved],
 [Keywords],
 [LastVersionId],
 [LockedBy],
 [LockedUntil],
 [LockKey],
 [MD5Hash],
 [OriginalFileName],
 [Owner],
 [PermissionsOnlyByAdmin],
 [Remarks],
 [State],
 [StorageProviderID],
 [Thumbnail],
 [Title],
 [Version],
 [VersionsToKeep],
 [WorkflowId],
 [PublishFrom],
 [PublishTo]
)
 VALUES ( @PortalId, @Author, @CollectionId, @Created, @Deleted, @Entry, @EntryType, @FileSize, @Hidden, @InheritAttributes, @IsApproved, @Keywords, @LastVersionId, @LockedBy, @LockedUntil, @LockKey, @MD5Hash, @OriginalFileName, @Owner, @PermissionsOnlyByAdmin, @Remarks, @State, @StorageProviderID, @Thumbnail, @Title, @Version, @VersionsToKeep, @WorkflowId, @PublishFrom, @PublishTo)
select SCOPE_IDENTITY()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
 @PortalId INT, 
 @Author NVARCHAR (500), 
 @CollectionId INT, 
 @Created DATETIME, 
 @Deleted BIT, 
 @Entry NVARCHAR (2000), 
 @EntryId INT, 
 @EntryType NVARCHAR (150), 
 @FileSize BIGINT, 
 @Hidden BIT, 
 @InheritAttributes BIT, 
 @IsApproved BIT, 
 @Keywords NVARCHAR (255), 
 @LastVersionId INT, 
 @LockedBy INT, 
 @LockedUntil DATETIME, 
 @LockKey UNIQUEIDENTIFIER, 
 @MD5Hash NVARCHAR (50), 
 @OriginalFileName NVARCHAR (255), 
 @Owner INT, 
 @PermissionsOnlyByAdmin BIT, 
 @Remarks NVARCHAR (MAX), 
 @State NVARCHAR (50), 
 @StorageProviderID INT, 
 @Thumbnail VARCHAR (200), 
 @Title NVARCHAR (1000), 
 @Version INT, 
 @VersionsToKeep INT, 
 @WorkflowId NVARCHAR (50),
 @PublishFrom DATE,
 @PublishTo DATE
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
 [PortalId] = @PortalId,
 [Author] = @Author,
 [CollectionId] = @CollectionId,
 [Created] = @Created,
 [Deleted] = @Deleted,
 [Entry] = @Entry,
 [EntryType] = @EntryType,
 [FileSize] = @FileSize,
 [Hidden] = @Hidden,
 [InheritAttributes] = @InheritAttributes,
 [IsApproved] = @IsApproved,
 [Keywords] = @Keywords,
 [LastVersionId] = @LastVersionId,
 [LockedBy] = @LockedBy,
 [LockedUntil] = @LockedUntil,
 [LockKey] = @LockKey,
 [MD5Hash] = @MD5Hash,
 [OriginalFileName] = @OriginalFileName,
 [Owner] = @Owner,
 [PermissionsOnlyByAdmin] = @PermissionsOnlyByAdmin,
 [Remarks] = @Remarks,
 [State] = @State,
 [StorageProviderID] = @StorageProviderID,
 [Thumbnail] = @Thumbnail,
 [Title] = @Title,
 [Version] = @Version,
 [VersionsToKeep] = @VersionsToKeep,
 [WorkflowId] = @WorkflowId,
 [PublishFrom] = @PublishFrom,
 [PublishTo] = @PublishTo
WHERE
 [EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}vw_DMX_Entries'))
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_Entries
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_Entries
AS
SELECT 
 e.Author,
 e.CollectionId,
 e.Deleted,
 e.Entry, 
 e.EntryId,
 e.EntryType,
 e.FileSize,
 e.Created, 
 e.IsApproved,
 e.Keywords,
 e.LastModified,
 e.LastVersionId, 
 e.LockedBy,
 e.LockedUntil,
 e.LockKey,
 e.MD5Hash, 
 e.OriginalFileName,
 e.Owner,
 e.PortalId,
 e.State, 
 e.Version,
 e.VersionsToKeep,
 e.WorkflowId,
 e.PublishFrom,
 e.PublishTo,
 e.Path, 
 e.PermissionsOnlyByAdmin,
 e.InheritAttributes,
 e.StorageProviderID, 
 e.Remarks,
 e.Title,
 e.Thumbnail,
 e.Hidden, 
 ext.AccessRights,
 ext.Addon,
 ext.ControlToLoad, 
 ext.Custom,
 ext.DownloadUrl,
 ext.EntryTypes, 
 ext.Icon16,
 ext.Icon32,
 ext.IsPrivate,
 ext.MimeType, 
 ext.NodePattern,
 ext.ResourceFile,
 ext.SettingsControl, 
 ext.ViewByDefault,
 ext.EditControl,
 ext.ViewControl, 
 u.DisplayName AS OwnerDisplayName,
 CASE WHEN (PATINDEX('Collection%', EntryType) > 0) THEN 1 ELSE 0 END AS IsCollection, 
 CASE WHEN LockedUntil > GETDATE() THEN 1 ELSE 0 END AS IsLocked,
 ext.ExtensionKey
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON e.EntryType = ext.ExtensionKey AND e.PortalId = ext.PortalId
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON e.Owner = u.UserID
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_fn_GetEntriesLocalized') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetEntriesLocalized
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetEntriesLocalized
 (@Locale VARCHAR(10))
 RETURNS TABLE
AS
RETURN
(SELECT
  e.Author,
  e.CollectionId,
  e.Deleted,
  e.Entry,
  e.EntryId,
  e.EntryType,
  e.FileSize,
  e.Created,
  e.IsApproved,
  e.Keywords,
  e.LastModified,
  e.LastVersionId,
  e.LockedBy,
  e.LockedUntil,
  e.LockKey,
  e.MD5Hash,
  e.OriginalFileName,
  e.Owner,
  e.PortalId,
  e.State,
  e.Version,
  e.VersionsToKeep,
  e.WorkflowId,
  e.PublishFrom,
  e.PublishTo,
  e.Path,
  e.PermissionsOnlyByAdmin,
  e.InheritAttributes,
  e.StorageProviderID,
  e.Thumbnail,
  e.Hidden,
  ISNULL(eml.Remarks, e.Remarks) AS Remarks, 
  ISNULL(eml.Title, e.Title) AS Title, 
  e.Remarks AS RemarksOriginal,
  e.Title AS TitleOriginal,
  eml.Remarks AS RemarksLocalized, 
  eml.Title AS TitleLocalized, 
  eml.Locale,
  ext.AccessRights,
  ext.Addon,
  ext.ControlToLoad, 
  ext.Custom,
  ext.DownloadUrl,
  ext.EntryTypes, 
  ext.Icon16,
  ext.Icon32,
  ext.IsPrivate,
  ext.MimeType, 
  ext.NodePattern,
  ext.ResourceFile,
  ext.SettingsControl, 
  ext.ViewByDefault,
  ext.EditControl,
  ext.ViewControl, 
  u.DisplayName AS OwnerDisplayName,
  CASE WHEN (PATINDEX('Collection%', EntryType) > 0) THEN 1 ELSE 0 END AS IsCollection, 
  CASE WHEN LockedUntil > GETDATE() THEN 1 ELSE 0 END AS IsLocked,
  ext.ExtensionKey
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON e.EntryType = ext.ExtensionKey AND e.PortalId = ext.PortalId
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON e.Owner = u.UserID
 LEFT OUTER JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntriesML WHERE Locale=@Locale) eml ON e.EntryId = eml.EntryId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_FindEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_FindEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindEntry
 @PortalId INT,
 @SearchText NVARCHAR(255),
 @Locale NVARCHAR(10)
AS
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.PortalId=@PortalId AND
 (e.OriginalFilename LIKE '%'+@SearchText+'%' OR
 e.Keywords LIKE '%'+@SearchText+'%' OR
 e.Title LIKE '%'+@SearchText+'%' OR
 el.Title LIKE '%'+@SearchText+'%')
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAllPortalCollections') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAllPortalCollections
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAllPortalCollections
 @PortalId INT,
 @UserId INT,
 @Locale VARCHAR(10)
AS
IF @UserId=-10
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  WHERE e.PortalId=@PortalId
  AND e.EntryType LIKE 'Collection%'
  ORDER BY Title
ELSE
 SELECT DISTINCT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  INNER JOIN
   (SELECT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
     LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
     WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1 OR (@UserId=-1 AND ep.RoleId=-3)))
   perm ON perm.EntryId=e.EntryId
  WHERE e.PortalId=@PortalId
  AND e.EntryType LIKE 'Collection%'
  AND (e.Owner=@UserId OR (e.PublishFrom <= GETDATE() AND e.PublishTo >= GETDATE()))
  ORDER BY Title
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetApprovedEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApprovedEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApprovedEntry
 @LastVersionId INT,
 @PortalId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 [LastVersionId] = @LastVersionId
 AND e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE LastVersionId=e.LastVersionId AND IsApproved=1)
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetBreadCrumbs') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetBreadCrumbs
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetBreadCrumbs
 @RootId INT,
 @EntryId INT,
 @Locale VARCHAR(10)
AS
DECLARE @BCPath VARCHAR(4000)
SELECT @BCPath = [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId
DECLARE @StartPath VARCHAR(4000)
SELECT @StartPath = [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@RootId
SET @StartPath = ISNULL(@StartPath, '0;') 
SET @BCPath=REPLACE(@BCPath, @StartPath, CAST(@RootId AS VARCHAR(10))+';')
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Split(@BCPath, ';')
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=s
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE NOT s=''
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetCollectionContents') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCollectionContents
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCollectionContents
 @PortalId INT,
 @CollectionId INT,
 @Locale VARCHAR(10)
AS
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND e2.IsApproved=1))
 ORDER BY Title
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntriesByPortal') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortal
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortal
 @PortalId INT,
 @IncludeDeleted BIT,
 @Locale NVARCHAR(10),
 @PageSize INT = 0,
 @StartRecord INT = 1
AS
SELECT *
FROM
(SELECT
 ROW_NUMBER() OVER (ORDER BY e.EntryId) AS rownum,
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.PortalId = @PortalId
 AND ((NOT e.Deleted = 1) OR (@IncludeDeleted = 1))) AS tmp
WHERE (@PageSize=0 OR (rownum >= @StartRecord AND rownum < @StartRecord + @PageSize))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntry
 @EntryId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.[EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryBranch') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryBranch
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryBranch
 @PortalId INT,
 @EntryId INT,
 @AllVersions BIT,
 @IncludeSelf BIT,
 @Locale NVARCHAR(6)
AS
EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;
DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;
SELECT 
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE ((e.[Path] LIKE @Path+'%') OR (e.LastVersionId=@EntryId AND @AllVersions=1 AND @IncludeSelf=1))
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId) OR @AllVersions=1)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByFilename') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByFilename
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByFilename
 @PortalId INT,
 @UserId INT,
 @CollectionId INT,
 @OriginalFileName NVARCHAR(255),
 @Locale VARCHAR(10)
AS
IF @UserId=-10
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
 AND e.OriginalFileName=@OriginalFileName
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
 AND (e.CollectionId=@CollectionId OR @CollectionId=-1)
ELSE
SELECT DISTINCT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 INNER JOIN
  (SELECT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
    INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
    LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
    WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1 OR (@UserId=-1 AND ep.RoleId=-3)))
  perm ON perm.EntryId=e.EntryId
 WHERE e.PortalId=@PortalId
 AND e.OriginalFileName=@OriginalFileName
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE 
      e2.LastVersionId=e.LastVersionId 
	  AND (e2.IsApproved=1 OR e2.Owner=@UserId)
	  AND (e2.Owner=@UserId OR (e2.PublishFrom <= GETDATE() AND e2.PublishTo >= GETDATE()))))
 AND (e.CollectionId=@CollectionId OR @CollectionId=-1)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByLockKey') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByLockKey
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByLockKey
 @PortalId INT,
 @LockKey UNIQUEIDENTIFIER,
 @Locale VARCHAR(10)
AS
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
  AND e.LockKey=@LockKey
  AND e.LockedUntil > GETDATE()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByTitle') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitle
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitle
 @PortalId INT,
 @UserId INT,
 @Title NVARCHAR(1000),
 @Locale VARCHAR(10)
AS
IF @UserId=-10
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  WHERE e.PortalId=@PortalId
  AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
  AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
ELSE
 SELECT DISTINCT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  INNER JOIN
   (SELECT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
     LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
     WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1 OR (@UserId=-1 AND ep.RoleId=-3)))
   perm ON perm.EntryId=e.EntryId
  WHERE e.PortalId=@PortalId
  AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
  AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE 
      e2.LastVersionId=e.LastVersionId 
	  AND (e2.IsApproved=1 OR e2.Owner=@UserId)
	  AND (e2.Owner=@UserId OR (e2.PublishFrom <= GETDATE() AND e2.PublishTo >= GETDATE()))))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
 @PortalId INT,
 @UserId INT,
 @CollectionId INT,
 @Title NVARCHAR(1000),
 @Locale VARCHAR(10)
AS
IF @UserId > -10
SELECT DISTINCT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 INNER JOIN
  (SELECT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
    INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
    LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
    WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1 OR (@UserId=-1 AND ep.RoleId=-3)))
  perm ON perm.EntryId=e.EntryId
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE 
      e2.LastVersionId=e.LastVersionId 
	  AND (e2.IsApproved=1 OR e2.Owner=@UserId)
	  AND (e2.Owner=@UserId OR (e2.PublishFrom <= GETDATE() AND e2.PublishTo >= GETDATE()))))
IF @UserId = -10
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
 AND (e.EntryId=e.LastVersionId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryListAsXml') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryListAsXml
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryListAsXml
 @EntryList VARCHAR(300),
 @UserId INT
AS
DECLARE @Ents TABLE (s NVARCHAR(512))
INSERT INTO @Ents (s) SELECT s FROM {databaseOwner}{objectQualifier}DMX_Split(@EntryList,';')
SELECT
 1 AS Tag,
 NULL AS Parent,
 e.EntryId AS [Entry!1!EntryId!ID], 
 e.Author AS [Entry!1!Author],
 e.CollectionId AS [Entry!1!CollectionId],
 e.Created AS [Entry!1!Created],
 e.Deleted AS [Entry!1!Deleted],
 e.Entry AS [Entry!1!Entry],
 e.EntryType AS [Entry!1!EntryType],
 e.FileSize AS [Entry!1!FileSize],
 e.IsApproved AS [Entry!1!IsApproved],
 e.Keywords AS [Entry!1!Keywords],
 e.LastVersionId AS [Entry!1!LastVersionId],
 e.LockedBy AS [Entry!1!LockedBy],
 e.OriginalFileName AS [Entry!1!OriginalFileName],
 e.PortalId AS [Entry!1!PortalId],
 e.Owner AS [Entry!1!Owner],
 e.Version AS [Entry!1!Version],
 e.LastModified AS [Entry!1!LastModified],
 e.LockedUntil AS [Entry!1!LockedUntil],
 e.LockKey AS [Entry!1!LockKey],
 e.MD5Hash AS [Entry!1!MD5Hash],
 e.State AS [Entry!1!State],
 e.VersionsToKeep AS [Entry!1!VersionsToKeep],
 e.WorkflowId AS [Entry!1!WorkflowId],
 e.PublishFrom AS [Entry!1!PublishFrom],
 e.PublishTo AS [Entry!1!PublishTo],
 ext.EditControl AS [Entry!1!EditControl],
 ext.ViewControl AS [Entry!1!ViewControl],
 REPLACE({databaseOwner}{objectQualifier}DMX_fn_URLEncode({databaseOwner}{objectQualifier}DMX_fn_GetPath(e.EntryId, e.PortalId, '')), '''', '\''') AS [Entry!1!FullPath],
 CAST (CASE WHEN e.LockedUntil > GETDATE() THEN 1 ELSE 0 END AS BIT) AS [Entry!1!IsLocked],
 CAST (CASE WHEN subs.EntryID IS NULL THEN 0 ELSE 1 END AS BIT) AS [Entry!1!Subscription],
 NULL AS [Permission!2!PermissionKey]
FROM
 @Ents ents
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ents.s=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON ext.ExtensionKey=e.EntryType AND e.PortalId=ext.PortalId
 LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions WHERE UserID=@UserId) subs ON subs.EntryId=e.EntryId
UNION ALL
SELECT DISTINCT
 2,
 1,
 e.EntryId, 
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 p.PermissionKey AS [Permission!2!PermissionKey]
FROM
 @Ents ents
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ents.s=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryPermissions ep ON ep.EntryId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON p.PermissionId=ep.PermissionId
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId
WHERE
 (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1)
FOR XML EXPLICIT
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetParentChain') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetParentChain
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetParentChain
 @PortalId INT,
 @EntryId INT,
 @Locale NVARCHAR(10)
AS
 DECLARE @CollectionId INT
 SELECT @CollectionId = (SELECT CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
 IF @CollectionId>0
  EXEC {databaseOwner}{objectQualifier}DMX_GetParentChain @PortalId, @CollectionId, @Locale
 SELECT 
  e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
   LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
   WHERE e.[EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPublishedAndApprovedEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPublishedAndApprovedEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPublishedAndApprovedEntry
 @LastVersionId INT,
 @PortalId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 [LastVersionId] = @LastVersionId
 AND e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE LastVersionId=e.LastVersionId AND IsApproved=1)
 AND (e.PublishFrom <= GETDATE() AND e.PublishTo >= GETDATE())
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetBreadCrumbs') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetBreadCrumbs
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetBreadCrumbs
 @RootId INT,
 @EntryId INT,
 @Locale VARCHAR(10)
AS
DECLARE @BCPath VARCHAR(4000)
SELECT @BCPath = [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId
DECLARE @StartPath VARCHAR(4000)
SELECT @StartPath = [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@RootId
SET @StartPath = ISNULL(@StartPath, '0;') 
SET @BCPath=REPLACE(@BCPath, @StartPath, CAST(@RootId AS VARCHAR(10))+';')
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.PublishFrom, e.PublishTo, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Split(@BCPath, ';')
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=s
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE NOT s=''
ORDER BY pn
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ExtractConcatenate') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_ExtractConcatenate
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_ExtractConcatenate
 (@EntryId INT, @SEPARATOR NVARCHAR(8))
RETURNS NVARCHAR(2000)
AS
BEGIN
 DECLARE @RES NVARCHAR(2000)
 SELECT @RES = (SELECT STUFF((SELECT ISNULL(Extract,'') AS X
  FROM {databaseOwner}{objectQualifier}DMX_SearchResults
  WHERE EntryId=@EntryId
  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'),1,1,' ... '));
 RETURN SUBSTRING(@RES, 6, LEN(@RES)-5)
END
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Providers
SET Provider='Bring2mind.DNN.Modules.DMX.Core8.Services.Search.LuceneSearch.LuceneSearchProvider, BRING2MIND.DNN.MODULES.DMX.CORE8'
WHERE Provider='Bring2mind.DNN.Modules.DMX.Services.Search.LuceneSearchProvider.LuceneSearchProvider, BRING2MIND.DNN.MODULES.DMX'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Settings
SET SettingValue='JsUploadProvider'
WHERE SettingName='UploadProvider'
AND SettingValue='RadUploadProvider'
GO

DELETE FROM {databaseOwner}{objectQualifier}DMX_Providers
WHERE ProviderName IN ('RadUploadProvider')
GO

