USP_DATALIST_APPUSERWORKSPACETASK

CREATE procedure dbo.USP_DATALIST_APPUSERWORKSPACETASK
(
    @CURRENTAPPUSERID uniqueidentifier,
    @TYPECODE tinyint = null,
    @INCLUDECOMPLETED bit = 0
)
as
    set nocount on;

    declare @DATE datetime;
    set @DATE = getDate();

    select
        APPEALMAILINGTASK.ID [ID],
        APPEALMAILINGTASK.SEGMENTATIONID [CONTEXTID],
        case
            when APPEALMAILINGTASK.STATUSCODE = 0 and datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) < 0 then 1
            else 0
        end [OVERDUE],
        'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.fa_mailings.png' [IMAGE],
        0 [TYPECODE],
        'Mailing task' [TYPE],
        APPEALMAILINGTASK.SUBJECT [SUBJECT],
        MKTSEGMENTATION.NAME  [DETAILS],
        case
            when APPEALMAILINGTASK.DATEDUE = '00000000' then null
            else dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)
        end [DATEDUE],
        APPEALMAILINGTASK.DATECOMPLETED [DATECOMPLETED],
        APPEALMAILINGTASK.STATUSCODE [STATUSCODE],
        case 
            when APPEALMAILINGTASK.STATUSCODE = 0 then 1 
            else 0 
        end [ACTIVE],
        0 [ISMOVE],
        case
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) < -7 then 'Past'
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) between -7 and -1 then 'Last 7 days'
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) = 0 then 'Today'
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) between 1 and 7 then 'Next 7 days'
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) > 7 then 'Future'
            else 'No date due'
        end [GROUP],
        case
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) < -7 then 1
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) between -7 and -1 then 2
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) = 0 then 3
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) between 1 and 7 then 4
            when datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) > 7 then 5
            else 0
        end [GROUPCODE],
        0 [TASKSAVAILABLE]
    from
        dbo.APPEALMAILINGTASK
    inner join
        dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = APPEALMAILINGTASK.SEGMENTATIONID
    inner join
        dbo.APPUSER on APPUSER.CONSTITUENTID = APPEALMAILINGTASK.OWNERID
    where
        APPUSER.ID = @CURRENTAPPUSERID
        and ( (APPEALMAILINGTASK.STATUSCODE = 0) or (@INCLUDECOMPLETED = 1) )
        and ( (@TYPECODE is null) or (@TYPECODE = 0) )

    union all

    select
        EVENTTASK.ID [ID],
        EVENTTASK.EVENTID [CONTEXTID],
        case
            when EVENTTASK.STATUSCODE = 0 and datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) < 0 then 1
            else 0
        end [OVERDUE],
        'catalog:Blackbaud.AppFx.EventManagement.Catalog.dll,Blackbaud.AppFx.EventManagement.Catalog.fa_events.png' [IMAGE],
        1 [TYPECODE],
        'Event task' [TYPE],
        EVENTTASK.NAME [SUBJECT],
        EVENT.NAME [DETAILS],
        EVENTTASK.COMPLETEBYDATE [DATEDUE],
        EVENTTASK.DATECOMPLETED [DATECOMPLETED],
        EVENTTASK.STATUSCODE [STATUSCODE],
        case
            when EVENTTASK.STATUSCODE = 0 then 1 
            else 0 
        end [ACTIVE],
        0 [ISMOVE],
        case
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) < -7 then 'Past'
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) between -7 and -1 then 'Last 7 days'
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) = 0 then 'Today'
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) between 1 and 7 then 'Next 7 days'
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) > 7 then 'Future'
            else 'No date due'
        end [GROUP],
        case
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) < -7 then 1
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) between -7 and -1 then 2
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) = 0 then 3
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) between 1 and 7 then 4
            when datediff(day, @DATE, EVENTTASK.COMPLETEBYDATE) > 7 then 5
            else 0
        end [GROUPCODE],
        0 [TASKSAVAILABLE]
    from
        dbo.EVENTTASK
    inner join
        dbo.EVENT on EVENT.ID = EVENTTASK.EVENTID
    inner join
        dbo.APPUSER on APPUSER.CONSTITUENTID = EVENTTASK.OWNERID
    where
        APPUSER.ID = @CURRENTAPPUSERID
        and ( (EVENTTASK.STATUSCODE = 0) or (@INCLUDECOMPLETED = 1) )
        and ( (@TYPECODE is null) or (@TYPECODE = 1) )

    union all

    select
        INTERACTION.ID [ID],
        INTERACTION.CONSTITUENTID [CONTEXTID],
        case
            when INTERACTION.STATUSCODE <> 2 and datediff(day, @DATE, INTERACTION.EXPECTEDDATE) < 0 then 1
            else 0
        end [OVERDUE],
        'catalog:Blackbaud.AppFx.Constituent.Catalog.dll,Blackbaud.AppFx.Constituent.Catalog.interactions.png' [IMAGE],
        2 [TYPECODE],
        'Interaction' [TYPE],
        INTERACTION.OBJECTIVE [SUBJECT],
        CONSTITUENT_NF.NAME [DETAILS],
        INTERACTION.EXPECTEDDATE [DATEDUE],
        INTERACTION.ACTUALDATE [DATECOMPLETED],
        INTERACTION.STATUSCODE [STATUSCODE],
        case
            when INTERACTION.STATUSCODE = 1 then 1
            else 0 
        end [ACTIVE],
        case 
            when INTERACTION.PROSPECTPLANID is null then 0 
            else 1 
        end [ISMOVE],
        case
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) < -7 then 'Past'
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) between -7 and -1 then 'Last 7 days'
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) = 0 then 'Today'
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) between 1 and 7 then 'Next 7 days'
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) > 7 then 'Future'
            else 'No date due'
        end [GROUP],
        case
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) < -7 then 1
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) between -7 and -1 then 2
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) = 0 then 3
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) between 1 and 7 then 4
            when datediff(day, @DATE, INTERACTION.EXPECTEDDATE) > 7 then 5
            else 0
        end [GROUPCODE],
        0 [TASKSAVAILABLE]
    from
        dbo.INTERACTION
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.CONSTITUENTID) CONSTITUENT_NF
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.FUNDRAISERID) OWNER_NF
    left join
        dbo.APPUSER on APPUSER.CONSTITUENTID = INTERACTION.FUNDRAISERID
    left join
        dbo.INTERACTIONTYPECODE on INTERACTIONTYPECODE.ID = INTERACTION.INTERACTIONTYPECODEID
    where
        APPUSER.ID = @CURRENTAPPUSERID
        and ( (INTERACTION.STATUSCODE = 1) or (@INCLUDECOMPLETED = 1) )
        and ( (@TYPECODE is null) or (@TYPECODE = 2) )

    union all

    select
        BATCH.ID [ID],
        BATCH.ID [CONTEXTID],
        case
            when BATCH.STATUSCODE = 0 and BATCHWORKFLOWSTATE.OVERDUEIN > 0 and datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) < 0 then 1
            else 0
        end [OVERDUE],
        'RES:fa_batch' [IMAGE],
        3 [TYPECODE],
        'Batch' [TYPE],
        BATCH.BATCHNUMBER [SUBJECT],
        BATCHTYPECATALOG.CATEGORY [DETAILS],
        case
            when BATCH.STATUSCODE = 0 and BATCHWORKFLOWSTATE.OVERDUEIN > 0 then dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))
            else null
        end [DATEDUE],
        BATCH.DATECOMMITTED [DATECOMPLETED],
        BATCH.STATUSCODE [STATUSCODE],
        case
            when BATCH.STATUSCODE = 0 then 1 
            else 0
        end [ACTIVE],
        0 [ISMOVE],
        case
            when BATCH.STATUSCODE = 0 and BATCHWORKFLOWSTATE.OVERDUEIN > 0 then
                case
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) < -7 then 'Past'
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) between -7 and -1 then 'Last 7 days'
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) = 0 then 'Today'
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) between 1 and 7 then 'Next 7 days'
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) > 7 then 'Future'
                    else 'No date due'
                end
        else
            'No date due'
        end [GROUP],
        case
            when BATCH.STATUSCODE = 0 and BATCHWORKFLOWSTATE.OVERDUEIN > 0 then
                case
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) < -7 then 1
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) between -7 and -1 then 2
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) = 0 then 3
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) between 1 and 7 then 4
                    when datediff(day, @DATE, dateAdd(day, BATCHWORKFLOWSTATE.OVERDUEIN, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc))) > 7 then 5
                    else 0
                end
        else 0
        end [GROUPCODE],
        dbo.UFN_BATCHWORKFLOWSTATE_TASKSAVAILABLE(BATCH.BATCHWORKFLOWSTATEID) [TASKSAVAILABLE]
    from
        dbo.BATCH
    inner join
        dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
    inner join
        dbo.BATCHTYPECATALOG on BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
    left join
        dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID
    where
        BATCH.APPUSERID = @CURRENTAPPUSERID
        and ( (BATCH.STATUSCODE <> 1) or (@INCLUDECOMPLETED = 1) )
        and BATCH.STATUSCODE <> 2
        and ( (@TYPECODE is null) or (@TYPECODE = 3) )
    order by
        GROUPCODE, DATEDUE, SUBJECT;

    return 0;