USP_DATALIST_MKTSOURCECODEPERFORMANCE

CREATE procedure dbo.[USP_DATALIST_MKTSOURCECODEPERFORMANCE]
(
  @SELECTIONID uniqueidentifier,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @SOURCECODEITEM nvarchar(50) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @SEGMENTCURSOR cursor;
  declare @SEGMENTATIONSEGMENTID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @TYPECODE tinyint;
  declare @SOURCECODE nvarchar(50);
  declare @SOURCECODEITEMID uniqueidentifier;

  /* Set currency symbols using the organization currency */
  /* Note, for now this report will only display in the organization currency.  Since this report
     sums efforts that could have different base currencies, it was decided the amount of processing it would take to 
     convert different base currencies on the fly is too much and a different approach would be needed. */
  declare @CURRENCYISOCURRENCYCODE nvarchar(6);
  declare @CURRENCYDECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(10);
  declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

  select 
    @CURRENCYISOCURRENCYCODE = [ISO4217],
    @CURRENCYDECIMALDIGITS = [DECIMALDIGITS],
    @CURRENCYSYMBOL = [CURRENCYSYMBOL],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE = [SYMBOLDISPLAYSETTINGCODE]
  from dbo.[CURRENCY]
  where [ID] = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  declare @ALL table (
    [SOURCECODE] nvarchar(10),
    [SEGMENTID] uniqueidentifier,
    [SEGMENTNAME] nvarchar(100),
    [PACKAGEID] uniqueidentifier,
    [PACKAGENAME] nvarchar(100),
    [QUANTITY] int,
    [TOTALCOST] money,
    [RESPONSES] int,
    [TOTALREVENUE] money,
    [ISTESTSEGMENT] bit
    );

  if @ENDDATE is null
    set @ENDDATE = getdate();
  if @STARTDATE is null
    set @STARTDATE = dateadd(yy, -1, @ENDDATE);

  if @SELECTIONID is null
  begin
    set @SQL = 'set @SEGMENTCURSOR = cursor local fast_forward for' + char(13) +
               'select distinct' + char(13) +
               '  [MKTSEGMENTATIONSEGMENT].[ID],' + char(13) +
               '  [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],' + char(13) +
               '  [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE],' + char(13) +
               '  [MKTSOURCECODEITEM].[ID]' + char(13) +
               'from'  + char(13) +
               '  dbo.[MKTSOURCECODEITEM]'  + char(13) +           
               '  inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]' + char(13) +
               '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID]' + char(13) +
               '  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]' + char(13) +
               'where' + char(13) +
               '  dbo.[UFN_SITEALLOWEDFORUSER](''' + convert(nvarchar(36), @CURRENTAPPUSERID) + ''', [MKTSEGMENTATION].[SITEID]) = 1 and' + char(13) + 
               '  [MKTSOURCECODEPARTDEFINITION].[NAME] = @SOURCECODEITEM;'
    set @SQL = @SQL + char(13) + 'open @SEGMENTCURSOR;';   
  end
  else
  begin
    set @SQL = 'set @SEGMENTCURSOR = cursor local fast_forward for' + char(13) +
               'select distinct' + char(13) +
               '  [MKTSEGMENTATIONSEGMENT].[ID],' + char(13) +
               '  [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],' + char(13) +
               '  [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE],' + char(13) +
               '  [MKTSOURCECODEITEM].[ID]' + char(13) +
               'from'  + char(13) +
               '  dbo.[MKTSOURCECODEITEM]'  + char(13) +   
               '  inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]' + char(13) +
               '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID]' + char(13) +
               '  inner join ' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SELECTIONID) + ' as [IDSET] on [MKTSEGMENTATION].[ID] = [IDSET].[ID]' + char(13) +
               '  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]' + char(13) +
               'where' + char(13) +
               '  dbo.[UFN_SITEALLOWEDFORUSER](''' + convert(nvarchar(36), @CURRENTAPPUSERID) + ''', [MKTSEGMENTATION].[SITEID]) = 1 and' + char(13) + 
               '  [MKTSOURCECODEPARTDEFINITION].[NAME] = @SOURCECODEITEM;'
    set @SQL = @SQL + char(13) + 'open @SEGMENTCURSOR;';  
  end;

  -- might as well leave UFN_SITEALLOWEDFORUSER up there, to cut down on the number of rows the real site security has to check

  -- they still need to be checked, though, because of the link between site security and the role in which access to a datalist is granted


  exec sp_executesql @SQL, N'@SEGMENTCURSOR cursor output, @SOURCECODEITEM nvarchar(50)', @SEGMENTCURSOR = @SEGMENTCURSOR output, @SOURCECODEITEM = @SOURCECODEITEM; 

  fetch next from @SEGMENTCURSOR into @SEGMENTATIONSEGMENTID, @SEGMENTATIONID, @TYPECODE, @SOURCECODEITEMID;
  while (@@FETCH_STATUS = 0)
  begin
    if ( -- check site security

         select count(*) 
         from (select [SITEID]
               from dbo.[MKTSEGMENTATION]
               where [ID] = @SEGMENTATIONID) 
         as [SEGMENTATIONSITE]
         where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SEGMENTATIONSITE].[SITEID] or (SITEID is null and [SEGMENTATIONSITE].[SITEID] is null)))
       ) > 0
      begin
        -- get the source code for the part for this segment

        -- BTR CR292456-013008 1/30/2008

        -- replaced code copied from UFN_MKTSOURCECODE_BUILDCODE with new call to UFN_MKTSOURCECODE_BUILDCODE

        set @SOURCECODE = dbo.[UFN_MKTSOURCECODE_BUILDCODE](@SEGMENTATIONSEGMENTID, default, @SOURCECODEITEMID);

        -- rollup totals by sourcecode, segmentationsegment, and package

        -- list rollup from the list cache table

        if @TYPECODE = 6
          begin
            insert into @ALL
              select
                [MKTSOURCECODEPART].[CODE] as [SOURCECODE],
                [MKTSEGMENT].[ID] as [SEGMENTID],
                [MKTSEGMENT].[NAME] as [SEGMENTNAME],
                [MKTPACKAGE].[ID] as [PACKAGEID],
                [MKTPACKAGE].[NAME] as [PACKAGENAME],
                sum([MKTSEGMENTATIONLISTACTIVE].[QUANTITY]) as [QUANTITY],
                sum([MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALCOST]) as [TOTALCOST],
                sum([MKTSEGMENTATIONLISTACTIVE].[RESPONSES]) as [RESPONSES],
                sum([MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT]) as [TOTALGIFTAMOUNT],
                0 as [ISTESTSEGMENT]
              from 
                dbo.[MKTSEGMENTATIONLISTACTIVE]
                inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONLISTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
                inner join dbo.[MKTPACKAGE] on [MKTSEGMENTATIONLISTACTIVE].[PACKAGEID] = [MKTPACKAGE].[ID]
                inner join dbo.[MKTLIST] on [MKTSEGMENTATIONLISTACTIVE].[LISTID] = [MKTLIST].[ID]
                inner join dbo.[MKTSOURCECODEPART] on [MKTLIST].[ID] = [MKTSOURCECODEPART].[LISTID] 
                  and [MKTSEGMENTATION].[ID] = [MKTSOURCECODEPART].[SEGMENTATIONID]
              where
                [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID
                and [MKTSOURCECODEPART].[SOURCECODEITEMID] = @SOURCECODEITEMID
                and isnull([MKTSEGMENTATION].[MAILDATE], cast([MKTSEGMENTATION].[ACTIVATEDATE] as date)) between @STARTDATE and @ENDDATE 
              group by 
                [MKTSOURCECODEPART].[CODE],
                [MKTLIST].[ID],
                [MKTSEGMENT].[ID],
                [MKTSEGMENT].[NAME],
                [MKTPACKAGE].[ID],
                [MKTPACKAGE].[NAME];       

              --insert values for house lists

              insert into @ALL
                select
                  [MKTSOURCECODEPART].[CODE] as [SOURCECODE],
                  isnull([MKTSEGMENTATIONTESTSEGMENT].[ID],[MKTSEGMENT].[ID]) as [SEGMENTID],
                  isnull([MKTSEGMENTATIONTESTSEGMENT].[NAME],[MKTSEGMENT].[NAME]) as [SEGMENTNAME],
                  isnull([MKTTESTPACKAGE].[ID],[MKTPACKAGE].[ID]) as [PACKAGEID],
                  isnull([MKTTESTPACKAGE].[NAME],[MKTPACKAGE].[NAME]) as [PACKAGENAME],
                  [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] - [LIST].[QUANTITY] as [QUANTITY],
                  [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST] - [LIST].[ORGANIZATIONTOTALCOST] as [TOTALCOST],
                  [MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] - [LIST].[RESPONSES] as [RESPONSES],
                  [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT] - [LIST].[ORGANIZATIONTOTALGIFTAMOUNT] as [TOTALGIFTAMOUNT],
                  case when [MKTSEGMENTATIONTESTSEGMENT].[ID] is null then 0 else 1 end as [ISTESTSEGMENT]
                from
                  dbo.[MKTSEGMENTATIONSEGMENT]
                  inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
                  inner join dbo.[MKTRECORDSOURCE] on [MKTSEGMENT].[QUERYVIEWCATALOGID] = [MKTRECORDSOURCE].[ID]
                  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
                  inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID]
                  inner join dbo.[MKTSOURCECODEPART] on [MKTRECORDSOURCE].[ID] = [MKTSOURCECODEPART].[LISTID]
                    and [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSOURCECODEPART].[SEGMENTATIONID]
                  inner join (
                    select
                      [MKTSEGMENTATIONSEGMENT].[ID] as [ID],  
                      isnull(sum([MKTSEGMENTATIONLISTACTIVE].[QUANTITY]),0) as [QUANTITY],
                      isnull(sum([MKTSEGMENTATIONLISTACTIVE].[TOTALCOST]),0) as [TOTALCOST],
                      isnull(sum([MKTSEGMENTATIONLISTACTIVE].[RESPONSES]),0) as [RESPONSES],
                      isnull(sum([MKTSEGMENTATIONLISTACTIVE].[TOTALGIFTAMOUNT]),0) as [TOTALGIFTAMOUNT],
                      isnull(sum([MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALCOST]),0) as [ORGANIZATIONTOTALCOST],
                      isnull(sum([MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT]),0) as [ORGANIZATIONTOTALGIFTAMOUNT]
                    from
                      dbo.[MKTSEGMENTATIONSEGMENT]
                      left join dbo.[MKTSEGMENTATIONLISTACTIVE] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONLISTACTIVE].[SEGMENTID]
                    where
                      [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID
                    group by
                      [MKTSEGMENTATIONSEGMENT].[ID]
                    ) as [LIST] on [MKTSEGMENTATIONSEGMENT].[ID] = [LIST].[ID]
                  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID]
                  left join dbo.[MKTPACKAGE] as [MKTTESTPACKAGE] on [MKTTESTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]   
  where
                  [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID
                  and [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] - [LIST].[QUANTITY] > 0
                  and [MKTSOURCECODEPART].[SOURCECODEITEMID] = @SOURCECODEITEMID
                  and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;
          end
        else
          begin
            insert into @ALL 
                select
                  case when [MKTSEGMENTATIONTESTSEGMENT].[ID] is null 
                    then dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONSEGMENT].[ID], default, @SOURCECODEITEMID)
                    else dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONTESTSEGMENT].[ID], default, @SOURCECODEITEMID) end,
                  isnull([MKTSEGMENTATIONTESTSEGMENT].[ID],[MKTSEGMENT].[ID]) as [SEGMENTID],
                  isnull([MKTSEGMENTATIONTESTSEGMENT].[NAME],[MKTSEGMENT].[NAME]) as [SEGMENTNAME],
                  isnull([MKTTESTPACKAGE].[ID],[MKTPACKAGE].[ID]) as [PACKAGID],
                  isnull([MKTTESTPACKAGE].[NAME],[MKTPACKAGE].[NAME]) as [PACKAGENAME],
                  sum([MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]) as [QUANTITY],
                  sum([MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST]) as [TOTALCOST],
                  sum([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES]) as [RESPONSES],
                  sum([MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT]) as [TOTALGIFTAMOUNT],
                  case when [MKTSEGMENTATIONTESTSEGMENT].[ID] is null then 0 else 1 end as [ISTESTSEGMENT]
                from 
                  dbo.[MKTSEGMENT]
                  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                  inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
                  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID]
                  left join dbo.[MKTPACKAGE] as [MKTTESTPACKAGE] on [MKTTESTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]      
                where 
                  [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID     
                  and isnull([MKTSEGMENTATION].[MAILDATE], cast([MKTSEGMENTATION].[ACTIVATEDATE] as date)) between @STARTDATE and @ENDDATE 
                group by 
                  [MKTSEGMENT].[ID],
                  [MKTSEGMENTATIONTESTSEGMENT].[ID],
                  [MKTSEGMENT].[NAME],
                  [MKTSEGMENTATIONTESTSEGMENT].[NAME],
                  [MKTTESTPACKAGE].[ID],
                  [MKTTESTPACKAGE].[NAME],
                  [MKTPACKAGE].[ID],
                  [MKTPACKAGE].[NAME],
                  [MKTSEGMENTATIONSEGMENT].[ID]
          end;
      end;

    fetch next from @SEGMENTCURSOR into @SEGMENTATIONSEGMENTID, @SEGMENTATIONID, @TYPECODE, @SOURCECODEITEMID;
  end;

  close @SEGMENTCURSOR;
  deallocate @SEGMENTCURSOR;


  --Display

  if (select count(*) from @ALL) = 0
  begin
    select 
      cast(null as nvarchar(10)) as [SOURCECODE],
      cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) as [SEGMENTID],
      cast(null as nvarchar(100)) as [SEGMENTNAME],
      cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) as [PACKAGEID],
      cast(null as nvarchar(100)) as [PACKAGENAME],
      cast(0 as int) as [QUANTITY],
      cast(0 as money) as [TOTALCOST],
      cast(0 as int) as [RESPONSES],
      cast(0 as money) as [TOTALREVENUE],
      (select [NAME] from dbo.[IDSETREGISTER] where [ID] = @SELECTIONID) as [SELECTIONNAME],
cast(0 as bit) as [ISTESTSEGMENT],
      @CURRENCYISOCURRENCYCODE as [CURRENCYISOCURRENCYCODE],
      @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
      @CURRENCYSYMBOL as [CURRENCYSYMBOL],
      @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE];
  end
  else
  begin
    select 
      [SOURCECODE],
      [SEGMENTID],
      [SEGMENTNAME],
      [PACKAGEID],
      [PACKAGENAME],  
      sum(isnull([QUANTITY],0)) as [QUANTITY],
      sum(isnull([TOTALCOST],0)) as [TOTALCOST],
      sum(isnull([RESPONSES],0)) as [RESPONSES],
      sum(isnull([TOTALREVENUE],0)) as [TOTALREVENUE],
      (select [NAME] from dbo.[IDSETREGISTER] where [ID] = @SELECTIONID) as [SELECTIONNAME],
      [ISTESTSEGMENT],
      @CURRENCYISOCURRENCYCODE as [CURRENCYISOCURRENCYCODE],
      @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
      @CURRENCYSYMBOL as [CURRENCYSYMBOL],
      @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
    from @ALL
    group by
      [SOURCECODE],
      [SEGMENTID],
      [SEGMENTNAME],
      [PACKAGEID],
      [PACKAGENAME],
      [ISTESTSEGMENT]
    order by
      [SOURCECODE],
      [SEGMENTNAME],
      [PACKAGENAME];
  end

  return 0;