USP_MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS_GETDATALIST

CREATE procedure dbo.[USP_MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS_GETDATALIST]
(
  @MEMBERSHIPRENEWALEFFORTPROCESSID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @STATUSCODE tinyint = null
)
as
    set nocount on;

    declare @EXPORTPROCESSOUTPUTTABLE table (
        [ID] uniqueidentifier, 
        [PARAMETERSETID] uniqueidentifier, 
        [TABLEKEY] nvarchar(50), 
        [QUANTITY] integer, 
        [STATUSCODE] tinyint, 
        [WORDMERGEENABLED] bit, 
        [NETCOMMUNITYEMAILJOBENABLED] bit, 
        [NETCOMMUNITYEMAILJOBSTARTED] bit,
        [CHANNELCODE] tinyint
    )

    declare @BUSINESSPROCESSSTATUSID uniqueidentifier;

    declare curExport cursor for
    select [MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS].[SEGMENTATIONEXPORTPROCESSSTATUSID]
      from  dbo.[MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS]  
    left outer join dbo.[MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS]
        on [MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS].[MEMBERSHIPRENEWALEFFORTPROCESSSTATUSID] = [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].[ID]
    where [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].[PARAMETERSETID] = @MEMBERSHIPRENEWALEFFORTPROCESSID

    open curExport
    fetch next from curExport into @BUSINESSPROCESSSTATUSID
    while @@FETCH_STATUS = 0
    begin

        insert into @EXPORTPROCESSOUTPUTTABLE 
            exec USP_MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS_GETSEGMENTATIONEXPORTPROCESSOUTPUT @BUSINESSPROCESSSTATUSID

        fetch next from curExport into @BUSINESSPROCESSSTATUSID
    end
    close curExport
    deallocate curExport

    declare @EXPORTPROCESSOUTPUT table (
        [PARAMETERSETID] uniqueidentifier, 
        [TABLEKEY] nvarchar(50),
        [EMAILTABLEKEY] nvarchar(50),
        [WORDMERGEENABLED] bit,
        [NETCOMMUNITYEMAILJOBENABLED] bit,
        [NETCOMMUNITYEMAILJOBSTARTED] bit,
        [BUSINESSPROCESSOUTPUTID] uniqueidentifier,
        [NUMBERPROCESSEDEMAIL] int,
        [EMAILJOBSTATUSCODE] tinyint,
        [MULTIPLEEXPORTDEFINITIONMAIL] bit,
        [MULTIPLEEXPORTDEFINITIONEMAIL] bit
    )

    insert into @EXPORTPROCESSOUTPUT (
        [PARAMETERSETID]
    )
    select distinct [PARAMETERSETID]
    from @EXPORTPROCESSOUTPUTTABLE

    update @EXPORTPROCESSOUTPUT 
        set TABLEKEY = T.TABLEKEY,
            WORDMERGEENABLED = T.WORDMERGEENABLED,
            MULTIPLEEXPORTDEFINITIONMAIL = case 
                                            when (select count(*)
                                                    from @EXPORTPROCESSOUTPUTTABLE 
                                                    where [PARAMETERSETID] = E.PARAMETERSETID
                                                        and [CHANNELCODE] = 0) > 1 then 1
                                            else 0
                                        end
    from @EXPORTPROCESSOUTPUT E 
    inner join @EXPORTPROCESSOUTPUTTABLE T on E.PARAMETERSETID = T.PARAMETERSETID 
        and T.[CHANNELCODE] = 0
        and T.[QUANTITY] > 0

    update @EXPORTPROCESSOUTPUT 
        set [EMAILTABLEKEY] = T.[TABLEKEY],
            [NETCOMMUNITYEMAILJOBENABLED] = T.[NETCOMMUNITYEMAILJOBENABLED],
            [NETCOMMUNITYEMAILJOBSTARTED] = T.[NETCOMMUNITYEMAILJOBSTARTED],
            [BUSINESSPROCESSOUTPUTID] = T.[ID],
            [NUMBERPROCESSEDEMAIL] = T.[QUANTITY],
            [EMAILJOBSTATUSCODE] = T.[STATUSCODE],
            MULTIPLEEXPORTDEFINITIONEMAIL = case 
                                            when (select count(*)
                                                    from @EXPORTPROCESSOUTPUTTABLE 
                                                    where [PARAMETERSETID] = E.PARAMETERSETID
                                                        and [CHANNELCODE] = 1) > 1 then 1
                                            else 0
                                        end
    from @EXPORTPROCESSOUTPUT E 
    inner join @EXPORTPROCESSOUTPUTTABLE T on E.PARAMETERSETID = T.PARAMETERSETID 
        and T.[CHANNELCODE] = 1
        and T.[QUANTITY] > 0

    select
        [BUSINESSPROCESSSTATUS].[ID],
        case 
          when [SEGMENTATIONEXPORTPROCESSSTATUS].[NUMBERPROCESSED] is null then cast(3 as tinyint)
          else [BUSINESSPROCESSSTATUS].[STATUSCODE]
        end as [STATUSCODE],
        case 
            when [SEGMENTATIONEXPORTPROCESSSTATUS].[NUMBERPROCESSED] is null then N'Results cleared' 
            when [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0 AND [SEGMENTATIONEXPORTPROCESSSTATUS].[NUMBEROFEXCEPTIONS] = 0 then N'Completed' 
            when [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0 AND [SEGMENTATIONEXPORTPROCESSSTATUS].[NUMBEROFEXCEPTIONS] > 0 then N'Completed with exceptions' 
            when [BUSINESSPROCESSSTATUS].[STATUSCODE] = 1 then N'Running' 
            when [BUSINESSPROCESSSTATUS].[STATUSCODE] = 2 then N'Did not finish' 
            when [BUSINESSPROCESSSTATUS].[STATUSCODE] = 3 then N'Results cleared'
        end as [STATUS],
        [BUSINESSPROCESSSTATUS].[STARTEDON],
        [BUSINESSPROCESSSTATUS].[ENDEDON],
        datediff(s, [BUSINESSPROCESSSTATUS].[STARTEDON], coalesce([BUSINESSPROCESSSTATUS].[ENDEDON], getdate())) as [DURATION],
        isnull([APPUSER].[USERNAME],'') as [USERNAME],
        [BUSINESSPROCESSSTATUS].[ERRORMESSAGE],
        [BUSINESSPROCESSSTATUS].[SERVERNAME],
        [SEGMENTATIONEXPORTPROCESSSTATUS].[NUMBERPROCESSED],
        [SEGMENTATIONEXPORTPROCESSSTATUS].[NUMBEROFEXCEPTIONS],
        [MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS].[SEGMENTATIONEXPORTPROCESSSTATUSID] as [EXPORTID],
        [EXPORTOUTPUTTABLE].[PARAMETERSETID],
        [EXPORTOUTPUTTABLE].[TABLEKEY],
        [EXPORTOUTPUTTABLE].[WORDMERGEENABLED],
        [EXPORTOUTPUTTABLE].[EMAILTABLEKEY],
        [EXPORTOUTPUTTABLE].[NETCOMMUNITYEMAILJOBENABLED],
        [EXPORTOUTPUTTABLE].[NETCOMMUNITYEMAILJOBSTARTED],
        case [EXPORTOUTPUTTABLE].[MULTIPLEEXPORTDEFINITIONEMAIL]
            when 0 then [EXPORTOUTPUTTABLE].[BUSINESSPROCESSOUTPUTID]
            else null
        end as [BUSINESSPROCESSOUTPUTID],
        [EXPORTOUTPUTTABLE].[NUMBERPROCESSEDEMAIL],
        [EXPORTOUTPUTTABLE].[EMAILJOBSTATUSCODE],
        [EXPORTOUTPUTTABLE].[MULTIPLEEXPORTDEFINITIONMAIL],
        [EXPORTOUTPUTTABLE].[MULTIPLEEXPORTDEFINITIONEMAIL]

    from dbo.[BUSINESSPROCESSSTATUS]
    inner join dbo.[MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS]  
        on [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].[ID] = [BUSINESSPROCESSSTATUS].[ID]
    inner join dbo.[APPUSER] 
        on [APPUSER].[ID] = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID]
    left outer join dbo.[MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS]
        on [MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS].[MEMBERSHIPRENEWALEFFORTPROCESSSTATUSID] = [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].[ID]
    left outer join dbo.[BUSINESSPROCESSSTATUS] [SEGMENTATIONEXPORTPROCESSSTATUS]
        on  [SEGMENTATIONEXPORTPROCESSSTATUS].[ID] = [MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS].[SEGMENTATIONEXPORTPROCESSSTATUSID]
    left outer join @EXPORTPROCESSOUTPUT [EXPORTOUTPUTTABLE]
        on [EXPORTOUTPUTTABLE].[PARAMETERSETID] = [SEGMENTATIONEXPORTPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] 
    where [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].[PARAMETERSETID] = @MEMBERSHIPRENEWALEFFORTPROCESSID
        and (dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS].[PARAMETERSETID]) = 1)
        and (@STATUSCODE is null or [BUSINESSPROCESSSTATUS].[STATUSCODE] = @STATUSCODE)
    order by [BUSINESSPROCESSSTATUS].[STARTEDON] desc

    return 0;