/****** REARRANGEMENT OF VIEW/EDIT CONTROLS ******/
UPDATE {databaseOwner}{objectQualifier}ModuleSettings
 SET SettingValue = 'bring2mind/dmx/edits/editentry.ascx'
 WHERE SettingValue = 'bring2mind/dmx/editentry.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}ModuleSettings
 SET SettingValue = 'bring2mind/dmx/edits/editentrypb.ascx'
 WHERE SettingValue = 'bring2mind/dmx/editentrypb.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}ModuleSettings
 SET SettingValue = 'bring2mind/dmx/views/ajaxtwopanel.ascx'
 WHERE SettingValue = 'bring2mind/dmx/viewcollection.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}ModuleSettings
 SET SettingValue = 'bring2mind/dmx/views/ajaxthreepanel.ascx'
 WHERE SettingValue = 'bring2mind/dmx/viewthreepanel.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}ModuleSettings
 SET SettingValue = 'bring2mind/dmx/views/templateview.ascx'
 WHERE SettingValue = 'bring2mind/dmx/templateview.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}ModuleSettings
 SET SettingValue = 'bring2mind/dmx/views/search.ascx'
 WHERE SettingValue = 'bring2mind/dmx/search.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
 SET ControlToLoad = 'bring2mind/dmx/edits/editentry.ascx'
 WHERE ControlToLoad = 'bring2mind/dmx/editentry.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
 SET ControlToLoad = 'bring2mind/dmx/edits/editentrypb.ascx'
 WHERE ControlToLoad = 'bring2mind/dmx/editentrypb.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
 SET ControlToLoad = 'bring2mind/dmx/views/ajaxtwopanel.ascx'
 WHERE ControlToLoad = 'bring2mind/dmx/viewcollection.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
 SET ControlToLoad = 'bring2mind/dmx/views/ajaxthreepanel.ascx'
 WHERE ControlToLoad = 'bring2mind/dmx/viewthreepanel.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
 SET ControlToLoad = 'bring2mind/dmx/views/templateview.ascx'
 WHERE ControlToLoad = 'bring2mind/dmx/templateview.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
 SET ControlToLoad = 'bring2mind/dmx/views/search.ascx'
 WHERE ControlToLoad = 'bring2mind/dmx/search.ascx'
GO

/****** New entries retrieval pattern ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}vw_DMX_EntriesLocalized') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_EntriesLocalized
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

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.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

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SearchResultsClean
 @SearchId NVARCHAR(50)
AS
BEGIN

CREATE TABLE #RES (EntryId INT, [Rank] REAL, Hits INT, Extract NVARCHAR(2000));
INSERT INTO #RES (EntryId, [Rank], Hits, Extract)
SELECT EntryId, SUM([Rank]), COUNT(Hits), {databaseOwner}{objectQualifier}DMX_ExtractConcatenate(EntryId, ', ')
FROM {databaseOwner}{objectQualifier}DMX_SearchResults
GROUP BY SearchId, EntryId
HAVING SearchId=@SearchId OR SearchId LIKE @SearchId + '-%';

DECLARE @maxRank REAL;
SET @maxRank = (SELECT MAX([Rank]) FROM #RES);
IF @maxRank=0
 SET @maxRank=1;

UPDATE #RES
SET [Rank] = [Rank]/@maxRank*1000;

DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
WHERE SearchId=@SearchId;

INSERT INTO {databaseOwner}{objectQualifier}DMX_SearchResults
 (SearchId, EntryId, [Rank], Hits, Extract)
SELECT
 @SearchId, EntryId, [Rank], Hits, Extract
FROM #RES;

DROP TABLE #RES;

DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
WHERE SearchId LIKE @SearchId + '-%';

END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryByFilename') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByFilename
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.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 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.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 DISTINCT 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 (e.CollectionId=@CollectionId OR @CollectionId=-1)

GO

/****** REPAIRS FOR UnAuth Users ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAllPortalCollections') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAllPortalCollections
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.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 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.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 DISTINCT 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%'
  ORDER BY Title

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryByTitle') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitle
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.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 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.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 DISTINCT 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)))
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
 @PortalId INT,
 @UserId INT,
 @CollectionId 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.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 DISTINCT 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)))
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.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.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))

GO

/****** Remove DMXDAV_ qualifier in favor of DMX_ ******/

EXEC sp_rename '{objectQualifier}DMXDAV_Numbers', '{objectQualifier}DMX_Numbers'
GO

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

CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetExtension
 (@Filename NVARCHAR(200))
RETURNS NVARCHAR(10)
AS
BEGIN
 IF @Filename IS NOT NULL
  IF @Filename<>''
   DECLARE @POS INT
   SELECT @POS = LEN(@Filename)-1
   DECLARE @CHR NVARCHAR(1)
   SELECT @CHR = SUBSTRING(@Filename,@POS,1)
   WHILE @CHR<>'.' AND @POS>1
   BEGIN
    SELECT @POS = @POS-1
    SELECT @CHR = SUBSTRING(@Filename,@POS,1)
   END
   IF @POS > 1
    RETURN SUBSTRING(@Filename,@POS,LEN(@Filename)-@POS+1)
 RETURN ''
END
GO

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

CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_fn_URLEncode(@String NVARCHAR(4000))
RETURNS VARCHAR(8000)
AS
BEGIN
 DECLARE @URLEncodedString VARCHAR(8000)
 SELECT @URLEncodedString=''
 SELECT @URLEncodedString=@URLEncodedString + 
  CASE WHEN theChar LIKE '[A-Za-z0-9()''*-._!/]'
   THEN theChar
   ELSE '%' + substring ('0123456789ABCDEF',(ASCII(theChar) / 16)+1,1) +  SUBSTRING('0123456789ABCDEF',(ASCII(theChar) % 16)+1,1)
  END
  FROM (SELECT [theChar]=SUBSTRING(@string,number,1)
   FROM {databaseOwner}{objectQualifier}DMX_Numbers WHERE Number <= LEN(@String)) CHARACTERARRAY
 RETURN @URLEncodedString
END
GO

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

CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetPath
 (@EntryId INT, 
  @PortalId INT, 
  @Locale NVARCHAR(10))
RETURNS NVARCHAR(4000)
AS
BEGIN
 DECLARE @res NVARCHAR(4000)
 IF EXISTS (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
 BEGIN
  DECLARE 
   @collection INT,
   @name NVARCHAR(2000),
   @orig NVARCHAR(300);
  WITH x AS (
   SELECT e.CollectionId, e.OriginalFileName, 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 AND e.PortalId=@PortalId)
  SELECT
   @collection = CollectionId,
   @name = Title,
   @orig = OriginalFilename
  FROM x;
  SELECT @res = {databaseOwner}{objectQualifier}DMX_fn_GetPath(@collection, @PortalId, @Locale)+'/'+@name+{databaseOwner}{objectQualifier}DMX_fn_GetExtension(@orig)
 END
ELSE
 SELECT @res = ''
RETURN @res
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryListAsXml') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryListAsXml
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],
 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,
 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

/****** Repair for portal groups in DNNEE ******/

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance
 @PortalId INT
AS
DECLARE @NotInPortalGroup INT
SET @NotInPortalGroup=1
BEGIN TRY
 DECLARE @sSQL nvarchar(500);
 DECLARE @ParmDefinition nvarchar(500);
 SELECT @sSQL = N'SELECT @retvalOUT = COUNT(p.PortalId) FROM {databaseOwner}{objectQualifier}Portals p WHERE p.PortalId=' + CAST(@PortalId AS NVARCHAR) + ' AND p.PortalGroupID=-1';
 SET @ParmDefinition = N'@retvalOUT int OUTPUT';
 EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@NotInPortalGroup OUTPUT;
 SELECT @NotInPortalGroup;
END TRY
BEGIN CATCH
END CATCH
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE 
 EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_Entries e WHERE e.EntryId={databaseOwner}{objectQualifier}DMX_EntryPermissions.EntryId AND e.PortalId=@PortalId)
 AND ((UserId>0 
   AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Users u 
    INNER JOIN {databaseOwner}{objectQualifier}UserPortals up ON u.UserId=up.UserId
    WHERE u.UserId = {databaseOwner}{objectQualifier}DMX_EntryPermissions.UserId
     AND up.PortalId=@PortalId)
   AND @NotInPortalGroup=1)
  OR (UserId=-10 
   AND RoleId>0 
   AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Roles r 
    WHERE r.RoleId = {databaseOwner}{objectQualifier}DMX_EntryPermissions.RoleId
     AND r.PortalId=@PortalId)))
GO

/****** Robustness ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryPermission
 @PortalId INT, 
 @AllowAccess BIT, 
 @EntryId INT, 
 @Expires DATETIME, 
 @PermissionId INT, 
 @RoleId INT, 
 @UserId INT
AS

IF NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions WHERE PortalId=@PortalId AND EntryId=@EntryId AND PermissionId=@PermissionId AND RoleId=@RoleId AND UserId=@UserId)
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions (
 [PortalId],
 [AllowAccess],
 [EntryId],
 [Expires],
 [PermissionId],
 [RoleId],
 [UserId])
VALUES (
 @PortalId,
 @AllowAccess,
 @EntryId,
 @Expires,
 @PermissionId,
 @RoleId,
 @UserId)

GO



/****** Adjust naming of some FKs ******/

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_AttributeEntrytypes_{objectQualifier}DMX_Attributes')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_AttributeEntrytypes'))
EXEC sp_rename 'FK_{objectQualifier}DMX_AttributeEntrytypes_{objectQualifier}DMX_Attributes', 'FK_{objectQualifier}DMX_AttributeEntrytypes_Attributes'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Attributes_{objectQualifier}DMX_Addons')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Attributes'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Attributes_{objectQualifier}DMX_Addons', 'FK_{objectQualifier}DMX_Attributes_Addons'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Attributes_{objectQualifier}Portals')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Attributes'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Attributes_{objectQualifier}Portals', 'FK_{objectQualifier}DMX_Attributes_Portals'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_AttributesML_{objectQualifier}DMX_Attributes')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_AttributesML'))
EXEC sp_rename 'FK_{objectQualifier}DMX_AttributesML_{objectQualifier}DMX_Attributes', 'FK_{objectQualifier}DMX_AttributesML_Attributes'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Categories_{objectQualifier}Portals')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Categories'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Categories_{objectQualifier}Portals', 'FK_{objectQualifier}DMX_Categories_Portals'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_CategoriesML_{objectQualifier}DMX_Categories')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_CategoriesML'))
EXEC sp_rename 'FK_{objectQualifier}DMX_CategoriesML_{objectQualifier}DMX_Categories', 'FK_{objectQualifier}DMX_CategoriesML_Categories'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Entries_{objectQualifier}Portals')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Entries'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Entries_{objectQualifier}Portals', 'FK_{objectQualifier}DMX_Entries_Portals'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_EntriesML_{objectQualifier}DMX_Entries')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_EntriesML'))
EXEC sp_rename 'FK_{objectQualifier}DMX_EntriesML_{objectQualifier}DMX_Entries', 'FK_{objectQualifier}DMX_EntriesML_Entries'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_EntryAttributes_{objectQualifier}DMX_Attributes')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_EntryAttributes'))
EXEC sp_rename 'FK_{objectQualifier}DMX_EntryAttributes_{objectQualifier}DMX_Attributes', 'FK_{objectQualifier}DMX_EntryAttributes_Attributes'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_EntryAttributes_{objectQualifier}DMX_Entries')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_EntryAttributes'))
EXEC sp_rename 'FK_{objectQualifier}DMX_EntryAttributes_{objectQualifier}DMX_Entries', 'FK_{objectQualifier}DMX_EntryAttributes_Entries'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_EntryCategories_{objectQualifier}DMX_Entries')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_EntryCategories'))
EXEC sp_rename 'FK_{objectQualifier}DMX_EntryCategories_{objectQualifier}DMX_Entries', 'FK_{objectQualifier}DMX_EntryCategories_Entries'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_EntryPermissions_{objectQualifier}DMX_Entries')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_EntryPermissions'))
EXEC sp_rename 'FK_{objectQualifier}DMX_EntryPermissions_{objectQualifier}DMX_Entries', 'FK_{objectQualifier}DMX_EntryPermissions_Entries'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Extensions_{objectQualifier}DMX_Addons')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Extensions'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Extensions_{objectQualifier}DMX_Addons', 'FK_{objectQualifier}DMX_Extensions_Addons'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Log_{objectQualifier}DMX_Entries')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Log'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Log_{objectQualifier}DMX_Entries', 'FK_{objectQualifier}DMX_Log_Entries'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Notifications_{objectQualifier}DMX_Log')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Notifications'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Notifications_{objectQualifier}DMX_Log', 'FK_{objectQualifier}DMX_Notifications_Log'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_PermissionEntrytypes_{objectQualifier}DMX_Permissions')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_PermissionEntrytypes'))
EXEC sp_rename 'FK_{objectQualifier}DMX_PermissionEntrytypes_{objectQualifier}DMX_Permissions', 'FK_{objectQualifier}DMX_PermissionEntrytypes_Permissions'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_PermissionPermissions_{objectQualifier}DMX_Permissions')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_PermissionPermissions'))
EXEC sp_rename 'FK_{objectQualifier}DMX_PermissionPermissions_{objectQualifier}DMX_Permissions', 'FK_{objectQualifier}DMX_PermissionPermissions_Permissions'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Repository_{objectQualifier}Portals')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Repository'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Repository_{objectQualifier}Portals', 'FK_{objectQualifier}DMX_Repository_Portals'
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_{objectQualifier}DMX_Subscriptions_{objectQualifier}DMX_Entries')
 AND parent_object_id = OBJECT_ID(N'{objectQualifier}DMX_Subscriptions'))
EXEC sp_rename 'FK_{objectQualifier}DMX_Subscriptions_{objectQualifier}DMX_Entries', 'FK_{objectQualifier}DMX_Subscriptions_Entries'
GO



