USP_KPI_EMAILPROCESS_JOBSTATUS

          create procedure dbo.USP_KPI_EMAILPROCESS_JOBSTATUS(
              @VALUE integer output, 
              @ASOFDATE datetime, 
            --@JOBTYPE integer,

            @STATUSCODE integer,
            @INCLUDEBLASTS bit,
            @INCLUDEFUNDRAISERBLASTS bit,
            @INCLUDENEWSLETTERS bit,
            @INCLUDECAMPAIGNS bit,
            @INCLUDECHAPTER bit,
            @INCLUDEPERSONALPAGEBLASTS bit,
            @INCLUDEPROJECTAPPEALBLASTS bit,
            @INCLUDEPROJECTAPPEALSEED bit,
            @INCLUDESIGNUPNOTIFICATIONS bit,
            @INCLUDEDONATIONNOTIFICATIONS bit,
            @INCLUDEPAGESHARING bit,
            @INCLUDESITENOTIFICATIONS bit,
            @INCLUDEECARDS bit,
            @INCLUDEDIRECTMARKETING bit,
            @INCLUDETEST bit,
            @PROCESSID uniqueidentifier,
            @JOBTYPEID uniqueidentifier = null output
          )
          as
          begin
              set nocount on;

            -- actual source values

            --declare @ENTERPRISE integer = 0;

            --declare @NETCOMMUNITY integer = 1;

            --declare @EXTERNAL integer = 2;


            -- actual category values

            declare @BLASTS integer = -1;
            declare @FUNDRAISERBLASTS integer = -1;
            declare @NEWSLETTERS integer = -1;
            declare @CAMPAIGNS integer = -1;
            declare @CHAPTER integer = -1;
            declare @PERSONALPAGEBLASTS integer = -1;
            declare @PROJECTAPPEALBLASTS integer = -1;
            declare @PROJECTAPPEALSEED integer = -1;
            declare @SIGNUPNOTIFICATIONS integer = -1;
            declare @DONATIONNOTIFICATIONS integer = -1;
            declare @PAGESHARING integer = -1;
            declare @SITENOTIFICATIONS integer = -1;
            declare @ECARDS integer = -1;
            declare @DIRECTMARKETING integer = -1;
            declare @TEST integer = -1;


            if @INCLUDEBLASTS = 1
              select @BLASTS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'Blast';

            if @INCLUDEFUNDRAISERBLASTS = 1
              select @FUNDRAISERBLASTS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'FundraiserBlast';

            if @INCLUDENEWSLETTERS = 1
              select @NEWSLETTERS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'Newsletter';

            if @INCLUDECAMPAIGNS = 1
              select @CAMPAIGNS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'Campaign';

            if @INCLUDECHAPTER = 1
              select @CHAPTER = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'Chapter';

            if @INCLUDEPERSONALPAGEBLASTS = 1
              select @PERSONALPAGEBLASTS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'PersonalPageBlast';

            if @INCLUDEPROJECTAPPEALBLASTS = 1
              select @PROJECTAPPEALBLASTS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'ProjectAppealBlastFinal';

            if @INCLUDEPROJECTAPPEALSEED = 1
              select @PROJECTAPPEALSEED = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'ProjectAppealSeed';

            if @INCLUDESIGNUPNOTIFICATIONS = 1
              select @SIGNUPNOTIFICATIONS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'SignupNotification';

            if @INCLUDEDONATIONNOTIFICATIONS = 1
              select @DONATIONNOTIFICATIONS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'DonationNotification';

            if @INCLUDEPAGESHARING = 1
              select @PAGESHARING = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'PageSharing';

            if @INCLUDESITENOTIFICATIONS = 1
   select @SITENOTIFICATIONS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'SiteNotification';

            if @INCLUDEECARDS = 1
              select @ECARDS = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'ECard';

            if @INCLUDEDIRECTMARKETING = 1
              select @DIRECTMARKETING = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'DirectMarketing';

            if @INCLUDETEST = 1
              select @TEST = CATEGORYID from dbo.EMAILCATEGORYPRIORITY where DESCRIPTION = 'Testing';



            select @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

            if @PROCESSID = '00000000-0000-0000-0000-000000000001'
              set @JOBTYPEID = 'F79575E5-FE4F-47BD-954C-AB056EF109D9';

            if @PROCESSID = '00000000-0000-0000-0000-000000000002'
              set @JOBTYPEID = '5066A489-6282-400C-96AF-1ECC1B7F281F';

            if @JOBTYPEID is null
              set @JOBTYPEID = dbo.UFN_TRANSLATIONFUNCTION_EMAILPROCESS_GETTYPE(@PROCESSID);

            if @JOBTYPEID = 'F79575E5-FE4F-47BD-954C-AB056EF109D9'
            begin -- transactional

              select @VALUE = count(*) from EMAILSTATUSTRANSACTIONAL
                  where (@STATUSCODE = (case when [STATUS] = 15 then 0
                                                  when [STATUS] = 3 then 2
                                                  when [STATUS] = 18 then 4
                                                  when [STATUS] in (6,8) then 5
                                                  when [STATUS] in (1,2,7,10) then 1
                                                  else 6 end
                                    )
                                    or
                                    (@STATUSCODE = 7 and [STATUS] in (1,2,7,10,15))
                                    or
                                    (@STATUSCODE = 8 and [STATUS] in (3,6,8,18))
                                    or
                                    (@STATUSCODE = 9)
                                    )
                                    and
                                    (@PROCESSID = (case when @PROCESSID = '00000000-0000-0000-0000-000000000001' then '00000000-0000-0000-0000-000000000001'
                                                    when @PROCESSID = '00000000-0000-0000-0000-000000000002' then '00000000-0000-0000-0000-000000000002'
                                                    else INTERNALJOBIDENTITY end))


            end
            else -- general purpose

            begin
              select @VALUE = count(*) from EMAILSTATUSGENERALPURPOSE
                  where (@STATUSCODE = (case when [STATUS] = 15 then 0
                                                  when [STATUS] = 3 then 2
                                                  when [STATUS] = 18 then 4
                                                  when [STATUS] in (6,8) then 5
                                                  when [STATUS] in (1,2,7,10) then 1
                                                  else 6 end
                                    )
                                    or
                                    (@STATUSCODE = 7 and [STATUS] in (1,2,7,10,15))
                                    or
                                    (@STATUSCODE = 8 and [STATUS] in (3,6,8,18))
                                    or
                                    (@STATUSCODE = 9)
                                    )
                                    and
                                    (
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@BLASTS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@FUNDRAISERBLASTS")]') = 1
                                      or
                EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@NEWSLETTERS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@CAMPAIGNS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@CHAPTER")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@PERSONALPAGEBLASTS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@PROJECTAPPEALBLASTS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@PROJECTAPPEALSEED")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@SIGNUPNOTIFICATIONS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@DONATIONNOTIFICATIONS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@PAGESHARING")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@SITENOTIFICATIONS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@ECARDS")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@DIRECTMARKETING")]') = 1
                                      or
                                      EMAILHEADER.exist('/EmailHeader/Type[text()=sql:variable("@TEST")]') = 1
                                    )
                                    and
                                    (@PROCESSID = (case when @PROCESSID = '00000000-0000-0000-0000-000000000001' then '00000000-0000-0000-0000-000000000001'
                                                    when @PROCESSID = '00000000-0000-0000-0000-000000000002' then '00000000-0000-0000-0000-000000000002'
                                                    else INTERNALJOBIDENTITY end))
            end



            end