USP_DATALIST_SYSTEMROLETASKS

CREATE procedure dbo.USP_DATALIST_SYSTEMROLETASKS (@SYSTEMROLEID uniqueidentifier)
as
with XMLNAMESPACES
(
    'bb_appfx_task' as t,
    'bb_appfx_commontypes' as c
),
[TASKS_CTE] as
(
    select 
        [V_INSTALLED_TASKCATALOG].[ID],
        [V_INSTALLED_TASKCATALOG].[NAME],
        [V_INSTALLED_TASKCATALOG].[DESCRIPTION],
        [V_INSTALLED_TASKCATALOG].[FUNCTIONALAREAID],
        [V_INSTALLED_TASKCATALOG].[TASKSPECXML].value('/t:TaskSpec[1]/@ImageKey', 'nvarchar(500)') [TASKIMAGEKEY],

        --Because task specs can only have one task hidden condition, we don't need to cross apply across the TaskHiddenCondition 

        --nodes. If we ever add multiple conditions, this code will need to change.

        [V_INSTALLED_TASKCATALOG].[TASKSPECXML].value('/t:TaskSpec[1]/t:TaskHiddenCondition[1]/c:ExistsCondition[1]/@Key', 'nvarchar(max)') [EXISTSCONDITIONKEY],
        [V_INSTALLED_TASKCATALOG].[TASKSPECXML].value('/t:TaskSpec[1]/t:TaskHiddenCondition[1]/c:DoesNotExistCondition[1]/@Key', 'nvarchar(max)') [DOESNOTEXISTCONDITIONKEY]        
    from [dbo].[V_INSTALLED_TASKCATALOG]
)
select    [SYSTEMROLETASK].[ID], 
        [TASKS_CTE].[ID],
        [TASKS_CTE].[NAME],
        [TASKS_CTE].[DESCRIPTION],
        [FUNCTIONALAREACATALOG].[NAME] as 'Functional Area',
        [TASKS_CTE].[TASKIMAGEKEY],
        [SYSTEMROLETASK].[DISPLAYONHOMEPAGE]
from [TASKS_CTE]
inner join [dbo].[SYSTEMROLETASK] on [TASKS_CTE].[ID] = [SYSTEMROLETASK].[TASKID]
left outer join [dbo].[FUNCTIONALAREACATALOG] on [TASKS_CTE].[FUNCTIONALAREAID] = [FUNCTIONALAREACATALOG].[ID]
where 
    (        ([TASKS_CTE].[EXISTSCONDITIONKEY] is null and [TASKS_CTE].[DOESNOTEXISTCONDITIONKEY] is null)
        --Because this is logic for "task hidden", the logic to show the task is reversed: 

        --    for the "condition key exists", check that the condition does not exist

        --    and for the "condition key does not exist", check that the condition does exist

        or    ([TASKS_CTE].[EXISTSCONDITIONKEY] is not null and not exists(select 1 from [dbo].[CONDITIONSETTING] where [CONDITIONSETTING].[NAME] = [TASKS_CTE].EXISTSCONDITIONKEY))
        or    ([TASKS_CTE].[DOESNOTEXISTCONDITIONKEY] is not null and exists(select 1 from [dbo].[CONDITIONSETTING] where [CONDITIONSETTING].[NAME] = [TASKS_CTE].[DOESNOTEXISTCONDITIONKEY]))
    )
    and
    [SYSTEMROLETASK].SYSTEMROLEID = @SYSTEMROLEID