USP_DATALIST_MKTPLANNEROUTLOOK

Returns a list of plans in the planner which have income for a given year, plus the income for each month.
Parameters

Parameter	Parameter Type	Mode	Description
@YEAR	int	IN	Year
@MONTH1RETURN	nvarchar(6)	IN	Month 1 Return Rate
@MONTH2RETURN	nvarchar(6)	IN	Month 2 Return Rate
@MONTH3RETURN	nvarchar(6)	IN	Month 3 Return Rate
@MONTH4RETURN	nvarchar(6)	IN	Month 4 Return Rate
@SITEID	uniqueidentifier	IN	Site
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@CURRENCYCODE	tinyint	IN	Currency
Definition

Copy


CREATE procedure dbo.[USP_DATALIST_MKTPLANNEROUTLOOK]
(
  @YEAR int = null,
  @MONTH1RETURN nvarchar(6) = null,
  @MONTH2RETURN nvarchar(6) = null,
  @MONTH3RETURN nvarchar(6) = null,
  @MONTH4RETURN nvarchar(6) = null,
  @SITEID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1
)
as
  set nocount on;

  declare @RETURNTABLE table(
    [PLANID] uniqueidentifier,
    [PLANNAME] nvarchar(100),
    [MAILINGID] uniqueidentifier,
    [MAILINGNAME] nvarchar(100),
    [STARTDATE] datetime,
    [JAN] float,
    [FEB] float,
    [MAR] float,
    [APR] float,
    [MAY] float,
    [JUN] float,
    [JUL] float,
    [AUG] float,
    [SEP] float,
    [OCT] float,
    [NOV] float,
    [DEC] float,
    [YEAR] int,
    [MAILDATE] datetime,
    [CURRENCYISOCURRENCYCODE] nvarchar(6),
    [CURRENCYDECIMALDIGITS] tinyint,
    [CURRENCYSYMBOL] nvarchar(10),
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint);

  if @YEAR is null
    set @YEAR = datepart(yyyy, getdate());

  if @MONTH1RETURN is null
    set @MONTH1RETURN = '50.0';
  if @MONTH2RETURN is null
    set @MONTH2RETURN = '35.0';
  if @MONTH3RETURN is null
    set @MONTH3RETURN = '10.0';
  if @MONTH4RETURN is null
    set @MONTH4RETURN = '5.0';

  declare @DAYSPERPERIOD int;
  set @DAYSPERPERIOD = 30;

  -- create base table of 10 numbers

  declare @BASE table([NUM] int);
  insert into @BASE
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
  select 7 union all
  select 8 union all
  select 9 union all
  select 10;

  -- use base table to make table of numbers 1-120

  declare @NUMS table([NUM] int);
  insert into @NUMS
  select [NUM] from @BASE union all
  select ([NUM] + 10) from @BASE union all
  select ([NUM] + 20) from @BASE union all
  select ([NUM] + 30) from @BASE union all
  select ([NUM] + 40) from @BASE union all
  select ([NUM] + 50) from @BASE union all
  select ([NUM] + 60) from @BASE union all
  select ([NUM] + 70) from @BASE union all
  select ([NUM] + 80) from @BASE union all
  select ([NUM] + 90) from @BASE union all
  select ([NUM] + 100) from @BASE union all
  select ([NUM] + 110) from @BASE;

  declare REVENUECURSOR cursor local fast_forward for
  select
    [MPI2].[ID] as [MAILINGID], 
    sum(case
    when [N].[NUM] <= 30 then [MPI2].[TOTALREVENUEGOAL] * cast(@MONTH1RETURN as float) / 100 / @DAYSPERPERIOD
    when [N].[NUM] > 30 and [N].[NUM] <= 60 then [MPI2].[TOTALREVENUEGOAL] * cast(@MONTH2RETURN as float) / 100 / @DAYSPERPERIOD
    when [N].[NUM] > 60 and [N].[NUM] <= 90 then [MPI2].[TOTALREVENUEGOAL] * cast(@MONTH3RETURN as float) / 100 / @DAYSPERPERIOD
    when [N].[NUM] > 90 and [N].[NUM] <= 120 then [MPI2].[TOTALREVENUEGOAL] * cast(@MONTH4RETURN as float) / 100 / @DAYSPERPERIOD
    else 0
    end) as [REVENUE],
    datepart(mm, dateadd(d, [N].[NUM] - 1, isnull([MPI2].[MAILDATE], dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI2].[STARTDATE])))) as [MONTH]
  from dbo.[MKTMARKETINGPLANITEM] as [MPI]
  inner join @NUMS as [N] on 1 = 1
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MPI].[MARKETINGPLANID]
  inner join dbo.[MKTMARKETINGPLANITEM] as [MPI2] on [MPI2].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPI2].[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] where [MPI2].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID])
  where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTMARKETINGPLAN].[SITEID] or (SITEID is null and [MKTMARKETINGPLAN].[SITEID] is null)))
  and [MPI].[LEVEL] = 0
  and (@SITEID is null or [MKTMARKETINGPLAN].[SITEID] = @SITEID)
  and datepart(yyyy, dateadd(d, [N].[NUM] - 1, isnull([MPI2].[MAILDATE], dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI2].[STARTDATE])))) = @YEAR
  and [MPI2].[TOTALREVENUEGOAL] > 0
  and exists (
        select
          top 1 1
        from dbo.[MKTMARKETINGPLANITEM] as [MPI3]
        where [MPI3].[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] where [MPI3].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID])
        and [MPI3].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID]
        and (datepart(yyyy, isnull([MPI2].[MAILDATE], dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI2].[STARTDATE]))) = @YEAR or datepart(yyyy, dateadd(d, 119, isnull([MPI2].[MAILDATE], dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI2].[STARTDATE])))) = @YEAR))
  group by [MPI].[ID], [MPI].[NAME], [MPI2].[ID], [MPI2].[NAME], (datepart(mm, dateadd(d, [N].[NUM] - 1, isnull([MPI2].[MAILDATE], dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI2].[STARTDATE])))))
  order by [MPI].[NAME], [MPI2].[NAME];

  insert into @RETURNTABLE (
    [PLANID],
    [YEAR]
  ) values (
    '00000000-0000-0000-0000-000000000000',
    @YEAR
  );

  insert into @RETURNTABLE (
    [PLANID],
    [PLANNAME],
    [MAILINGID], 
    [MAILINGNAME],
    [STARTDATE],
    [JAN], [FEB], [MAR], [APR], [MAY], [JUN], [JUL], [AUG], [SEP], [OCT], [NOV], [DEC],
    [YEAR],
    [MAILDATE]
  )
  select
    [MPI].[ID] as [PLANID],
    [MPI].[NAME] as [PLANNAME],
    [MPI2].[ID] as [MAILINGID], 
    [MPI2].[NAME] as [MAILINGNAME],
    dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI2].[STARTDATE]) as [STARTDATE],
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
    @YEAR as [YEAR],
    [MPI2].[MAILDATE]
  from dbo.[MKTMARKETINGPLANITEM] as [MPI]
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MPI].[MARKETINGPLANID]
  inner join dbo.[MKTMARKETINGPLANITEM] as [MPI2] on [MPI2].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPI2].[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] where [MPI2].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID])
  where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTMARKETINGPLAN].[SITEID] or (SITEID is null and [MKTMARKETINGPLAN].[SITEID] is null)))
  and [MPI].[LEVEL] = 0
  and [MPI2].[TOTALREVENUEGOAL] > 0
  and exists (
        select
          [MPI3].[ID]
        from dbo.[MKTMARKETINGPLANITEM] as [MPI3]
        where [MPI3].[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] where [MPI3].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID])
        and [MPI3].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID]
        and (datepart(yyyy, isnull([MPI2].[MAILDATE], dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI2].[STARTDATE]))) = @YEAR or datepart(yyyy, dateadd(d, 119, isnull([MPI2].[MAILDATE], dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI2].[STARTDATE])))) = @YEAR))
  order by [MPI].[NAME], [MPI2].[NAME];

  declare @MAILINGID uniqueidentifier;
  declare @REVENUE float;
  declare @MONTH int;

  open REVENUECURSOR;
  fetch next from REVENUECURSOR into @MAILINGID, @REVENUE, @MONTH;

  while (@@FETCH_STATUS = 0)
    begin
      if @MONTH = 1 update @RETURNTABLE set [JAN] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 2 update @RETURNTABLE set [FEB] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 3 update @RETURNTABLE set [MAR] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 4 update @RETURNTABLE set [APR] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 5 update @RETURNTABLE set [MAY] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 6 update @RETURNTABLE set [JUN] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 7 update @RETURNTABLE set [JUL] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 8 update @RETURNTABLE set [AUG] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 9 update @RETURNTABLE set [SEP] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 10 update @RETURNTABLE set [OCT] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 11 update @RETURNTABLE set [NOV] = @REVENUE where [MAILINGID] = @MAILINGID;
      if @MONTH = 12 update @RETURNTABLE set [DEC] = @REVENUE where [MAILINGID] = @MAILINGID;

      fetch next from REVENUECURSOR into @MAILINGID, @REVENUE, @MONTH;
    end

  close REVENUECURSOR;
  deallocate REVENUECURSOR;

  update @RETURNTABLE set 
    [CURRENCYISOCURRENCYCODE] = [CURRENCY].[ISO4217],
    [CURRENCYDECIMALDIGITS] = [CURRENCY].[DECIMALDIGITS],
    [CURRENCYSYMBOL] = [CURRENCY].[CURRENCYSYMBOL],
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] = [CURRENCY].[SYMBOLDISPLAYSETTINGCODE]
  from @RETURNTABLE as [RETURN]
  inner join dbo.[MKTMARKETINGPLANITEM] as [MPI] on [MPI].[ID] = [RETURN].[PLANID]
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MPI].[MARKETINGPLANID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTMARKETINGPLAN].[BASECURRENCYID] end;

  select * from @RETURNTABLE;

  return 0;