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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
 @AttributeId Int,
 @PortalId int
AS

SELECT
 a.*
FROM
	{databaseOwner}{objectQualifier}DMX_Attributes a
WHERE
	a.AttributeId = @AttributeId
	AND (a.[PortalId] = @PortalId OR @PortalId=-1)
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry
 @PortalId Int,
 @EntryId Int,
 @NewCollectionId INT
AS

 DECLARE @destpath VARCHAR(2000)
 SET @destpath = (SELECT [Path] 
   FROM {databaseOwner}{objectQualifier}DMX_Entries
   WHERE EntryId = @NewCollectionId)
 IF NOT @destpath LIKE ';'+CAST(@EntryId AS VARCHAR(10))+';'
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET CollectionId=@NewCollectionId
 WHERE
  LastVersionId=@EntryId
  AND PortalId=@PortalId

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersToNotify
 @PortalId int
AS

SELECT 
 u.*
FROM
 {databaseOwner}{objectQualifier}Users u
WHERE
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Notifications n WHERE n.[UserId] = u.UserId AND n.[PortalId] = @PortalId AND n.[Sent] IS NULL)>0

GO

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

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

 DECLARE @entrypath VARCHAR(2000)
 SET @entrypath = (SELECT [Path] 
   FROM {databaseOwner}{objectQualifier}DMX_Entries
   WHERE EntryId = @EntryId)

 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET LastModified=GETDATE()
 WHERE PortalId=@PortalId
 AND @entrypath LIKE [Path]+'%'
 AND EntryId=LastVersionId

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_LockEntry
 @PortalId Int,
 @EntryId Int,
	@LockedUntil DateTime,
	@LockedBy Int,
	@LockKey UNIQUEIDENTIFIER
AS

DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
	[LockedBy] = @LockedBy,
	[LockedUntil] = @LockedUntil,
	[LockKey] = @LockKey
WHERE
	([EntryId] = @EntryId OR LastVersionId=@EntryId)
 AND [PortalId] = @PortalId;
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;

GO

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

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

DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
	[LockedBy] = -1,
	[LockedUntil] = GETDATE(),
	[LockKey] = NULL
WHERE
	([EntryId] = @EntryId OR LastVersionId=@EntryId)
 AND [PortalId] = @PortalId;
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}DMX_Split') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_Split
GO

CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_Split (@s varchar(512), @sep char(1))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
 @PortalId INT,
 @CollectionId INT=0,
 @FolderName NVARCHAR(255)='New Folder',
 @ViewRoles VARCHAR(2000)='',
 @EditRoles VARCHAR(2000)='',
 @AddRoles VARCHAR(2000)='',
 @ApproveRoles VARCHAR(2000)=''
AS

BEGIN
 DECLARE @Owner INT
 SET @Owner = (SELECT AdministratorId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
 DECLARE @NewId INT
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries
  (CollectionId, Created, Deleted, Entry, EntryType, IsApproved, Owner, PortalId, Version, PermissionsOnlyByAdmin)
  VALUES
  (@CollectionId, GETDATE(), 0, '', 'Collection', 1, @Owner, @PortalId, 1, 0);
 SET @NewId = SCOPE_IDENTITY()
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET LastVersionId=EntryId
  WHERE EntryId=@NewId;
 INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts
  (Locale, ObjectId, SubTypeKey, [Text], TypeKey)
  VALUES ('', @NewId, 'TITLE', @FolderName, 'ENT');
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Log
  ([Action],[Datime],[EntId],[UserId])
 VALUES ('Add', GETDATE(), @NewId, @Owner);
 DECLARE @AdminRoleId INT
 SET @AdminRoleId = (SELECT AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
 IF @ViewRoles='' AND @EditRoles='' AND @AddRoles='' AND @ApproveRoles=''
  BEGIN
   IF @CollectionId=0
    BEGIN
     -- set Admin roles for default folder under root
     INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
      ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
     SELECT 1, @NewId, PermissionId, @PortalId, @AdminRoleId, -10
      FROM {databaseOwner}{objectQualifier}DMX_Permissions
      WHERE PortalId=@PortalId AND (PermissionKey='VIEW' OR PermissionKey='EDIT' OR PermissionKey='ADD');
    END
   ELSE
    -- Inherit permissions from parent
    INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
     ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
    SELECT 1, @NewId, [PermissionId],[PortalId],[RoleId],[UserId]
     FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
     WHERE EntryId=@CollectionId;
  END
 ELSE
  BEGIN
   -- set View roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@ViewRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='VIEW')
    WHERE NOT @ViewRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Edit roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@EditRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='EDIT')
    WHERE NOT @EditRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Add roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@AddRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='ADD')
    WHERE NOT @AddRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Approve roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@ApproveRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='APPROVE')
    WHERE NOT @ApproveRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- make sure Admin is part of this
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p.PermissionId, @PortalId, @AdminRoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Permissions p
    WHERE p.PortalId=@PortalId AND (p.PermissionKey='VIEW' OR p.PermissionKey='EDIT' OR p.PermissionKey='ADD')
    AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
        WHERE ep.PermissionId=p.PermissionId AND ep.EntryId=@NewId AND ep.RoleId=@AdminRoleId);
  END
END
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance
 @PortalId INT
AS

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

