Cascade Pages... And Modules!

So Arena's been able to Cascade Permissions on a bunch of things for a while, but what's been missing the entire time has been Module permissions when cascading Page permissions. Well, this sproc update will change that:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[secu_sp_cascadePagePermissions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[secu_sp_cascadePagePermissions]
GO

CREATE PROC [dbo].[secu_sp_cascadePagePermissions]
@parent INT,
@OrganizationID int

AS

DECLARE @page INT

DELETE FROM dbo.secu_permission
WHERE object_type=2 AND object_key in (SELECT module_instance_id FROM port_module_instance WHERE page_id=@parent AND organization_id=@OrganizationID) and organization_id = @OrganizationID

INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
SELECT DISTINCT 'AS-IP', 'AS-IP',2, module_instance_id, operation_type, subject_type, subject_key, @OrganizationID
FROM dbo.secu_permission AS sp
JOIN dbo.port_module_instance AS pmi ON pmi.page_id=@parent AND sp.object_type=1 AND pmi.organization_id=sp.organization_id
WHERE sp.object_key=@parent
AND sp.organization_id = @OrganizationID

DELETE FROM dbo.secu_permission
WHERE object_type = 2 AND object_key IN (SELECT child.module_instance_id FROM port_module_instance AS child JOIN port_module_instance AS parent ON parent.module_instance_id=child.parent_module_instance_id AND parent.organization_id = child.organization_id WHERE parent.page_id = @parent AND parent.organization_id =@OrganizationID) AND organization_id = @OrganizationID

INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
SELECT DISTINCT 'AS-IP', 'AS-IP',2, child.module_instance_id, operation_type, subject_type, subject_key, @OrganizationID
FROM dbo.secu_permission AS sp
JOIN dbo.port_module_instance AS pmi ON pmi.page_id=@parent AND sp.object_type=1 AND pmi.organization_id=sp.organization_id
JOIN dbo.port_module_instance AS child ON child.parent_module_instance_id=pmi.module_instance_id AND child.organization_id=pmi.organization_id
WHERE sp.object_key=@parent
AND sp.organization_id = @OrganizationID

DECLARE PageCursor CURSOR READ_ONLY FOR
WITH pageTree (page_id) AS (
SELECT page_id
FROM port_portal_page
WHERE organization_id=@OrganizationID
AND parent_page_id=@parent
UNION ALL
SELECT p.page_id
FROM port_portal_page AS p
JOIN pageTree AS t ON t.page_id=p.parent_page_id
WHERE p.organization_id=@OrganizationID)
SELECT page_id FROM pageTree;

OPEN PageCursor

FETCH NEXT FROM PageCursor
INTO @page

WHILE (@@FETCH_STATUS <> -1)
BEGIN

 IF (@@FETCH_STATUS = 0)
 BEGIN

  DELETE FROM dbo.secu_permission
  WHERE object_type=1 AND object_key=@page and organization_id = @OrganizationID

  INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
  SELECT 'AS-IP', 'AS-IP',object_type, @page, operation_type, subject_type, subject_key, @OrganizationID
  FROM dbo.secu_permission
  WHERE object_type=1
  AND object_key=@parent
  AND organization_id = @OrganizationID

  DELETE FROM dbo.secu_permission
  WHERE object_type=2 AND object_key in (SELECT module_instance_id FROM port_module_instance WHERE page_id=@page AND organization_id=@OrganizationID) and organization_id = @OrganizationID

  INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
  SELECT DISTINCT 'AS-IP', 'AS-IP',2, module_instance_id, operation_type, subject_type, subject_key, @OrganizationID
  FROM dbo.secu_permission AS sp
  JOIN dbo.port_module_instance AS pmi ON pmi.page_id=@page AND sp.object_type=1 AND pmi.organization_id=sp.organization_id
  WHERE sp.object_key=@parent
  AND sp.organization_id = @OrganizationID

  DELETE FROM dbo.secu_permission
  WHERE object_type = 2 AND object_key IN (SELECT child.module_instance_id FROM port_module_instance AS child JOIN port_module_instance AS parent ON parent.module_instance_id=child.parent_module_instance_id AND parent.organization_id=child.organization_id WHERE parent.page_id=@page AND parent.organization_id = @OrganizationID) AND organization_id = @OrganizationID

  INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
  SELECT DISTINCT 'AS-IP', 'AS-IP',2, child.module_instance_id, operation_type, subject_type, subject_key, @OrganizationID
  FROM dbo.secu_permission AS sp
  JOIN dbo.port_module_instance AS pmi ON pmi.page_id=@page AND sp.object_type=1 AND pmi.organization_id=sp.organization_id
  JOIN dbo.port_module_instance AS child ON child.parent_module_instance_id=pmi.module_instance_id AND child.organization_id=pmi.organization_id
  WHERE sp.object_key=@parent
  AND sp.organization_id = @OrganizationID

 END

 FETCH NEXT FROM PageCursor
 INTO @page

END

CLOSE PageCursor
DEALLOCATE PageCursor