USP_DATALIST_BREAKEVENANALYSIS

CREATE procedure dbo.[USP_DATALIST_BREAKEVENANALYSIS]
(
  @SEGMENTATIONID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @DATA table (
    [MAILINGNAME] nvarchar(100),
    [MAILINGDESCRIPTION] nvarchar(255),
    [MAILINGCODE] nvarchar(10),
    [MAILINGSITE] nvarchar(1024),
    [PACKAGEID] uniqueidentifier,
    [PACKAGENAME] nvarchar(100),
    [SEGMENTID] uniqueidentifier,
    [SEGMENTNAME] nvarchar(100),
    [COSTPERPIECE] money,
    [OFFERS] integer,
    [RESPONDERS] integer,
    [RESPONSES] integer,
    [GIFTAMOUNT] money,
    [AVERAGEGIFTAMOUNT] money,
    [SEQUENCE] integer);

  declare @SEGMENTCOUNTS table (
    [OFFERS] integer,
    [RESPONDERS] integer,
    [RESPONSES] integer,
    [GIFTAMOUNT] money,
    [AVERAGEGIFTAMOUNT] money,
    [ORGANIZATIONTOTALGIFTAMOUNT] money,
    [ORGANIZATIONAVERAGEGIFTAMOUNT] money);

  declare @MAILINGNAME nvarchar(100);
  declare @MAILINGDESCRIPTION nvarchar(255);
  declare @MAILINGCODE nvarchar(10);
  declare @MAILINGSITE nvarchar(1024);
  declare @MAILINGSEGMENTID uniqueidentifier;
  declare @FIXEDCOST money = 0;
  declare @FIXEDCOSTPERPIECE money = 0;
  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTNAME nvarchar(100);
  declare @PACKAGEID uniqueidentifier;
  declare @PACKAGENAME nvarchar(100);
  declare @PACKAGECOST money = 0;
  declare @PACKAGECOSTDISTRIBUTIONMETHODCODE tinyint;
  declare @COSTPERRECORD money = 0;
  declare @TOTALOFFERS integer = 0;
  declare @SEQUENCE integer = 0;
  declare @INSERTCOST money = 0;

  begin try
    /* Get any relevant mailing information */
    select
      @MAILINGNAME = [MKTSEGMENTATION].[NAME],
      @MAILINGDESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
      @MAILINGCODE = [MKTSEGMENTATION].[CODE],
      @MAILINGSITE = dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]),
      @FIXEDCOST = isnull([MKTSEGMENTATIONBUDGET].[FIXEDCOST], 0)
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
    where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

    /* Get segment information */
    declare SEGMENTCURSOR cursor local fast_forward for
      select
        [P].[ID],
        coalesce([P].[NAME], '') as [PACKAGENAME],
        [SP].[UNITCOST],
        [SP].[COSTDISTRIBUTIONMETHODCODE],
        [SS].[ID] as [SEGMENTID],
        coalesce([S].[NAME], '') as [SEGMENTNAME],
        [SS].[ID] as [SEGMENTATIONSEGMENTID],
        [SS].[SEQUENCE],
        (case when [S].[SEGMENTTYPECODE] = 2 then dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([SS].[ID], 0) else 0 end) as [COSTPERRECORD],
        case [SP].[COSTDISTRIBUTIONMETHODCODE]
          when 0 then [SP].[INSERTCOSTPERPIECE]
          when 1 then [SP].[INSERTCOSTPERRESPONSE]
          when 2 then [SP].[INSERTCOSTPEREFFORT]
          when 4 then [SP].[INSERTCOSTPERPIECE]
          else 0
        end
      from dbo.[MKTSEGMENT] [S] 
      right outer join dbo.[MKTSEGMENTATIONSEGMENT] as [SS] on [S].[ID] = [SS].[SEGMENTID]
      inner join dbo.[MKTSEGMENTATIONPACKAGE] as [SP] on ([SP].[PACKAGEID] = [SS].[PACKAGEID] and [SP].[SEGMENTATIONID] = [SS].[SEGMENTATIONID])
      inner join dbo.[MKTPACKAGE] as [P] on [P].[ID] = [SS].[PACKAGEID]
      where [SS].[SEGMENTATIONID] = @SEGMENTATIONID;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @PACKAGEID, @PACKAGENAME, @PACKAGECOST, @PACKAGECOSTDISTRIBUTIONMETHODCODE, @SEGMENTID, @SEGMENTNAME, @MAILINGSEGMENTID, @SEQUENCE, @COSTPERRECORD, @INSERTCOST;

    while (@@FETCH_STATUS = 0)
      begin
        delete from @SEGMENTCOUNTS;
        insert into @SEGMENTCOUNTS
          exec dbo.[USP_MKTSEGMENTATIONSEGMENT_GETRESPONSECOUNTS] @MAILINGSEGMENTID;

        select @TOTALOFFERS = @TOTALOFFERS + [OFFERS] from @SEGMENTCOUNTS;

        insert into @DATA 
        select
          @MAILINGNAME,
          @MAILINGDESCRIPTION,
          @MAILINGCODE,
          @MAILINGSITE,
          @PACKAGEID,
          @PACKAGENAME,
          @SEGMENTID,
          @SEGMENTNAME,
          case @PACKAGECOSTDISTRIBUTIONMETHODCODE
            when 0 -- per piece

            then @PACKAGECOST + @INSERTCOST
            when 1 -- per response

            then case when [OFFERS] > 0 then (cast(@PACKAGECOST + @INSERTCOST as decimal(20, 10)) * cast([RESPONSES] as decimal(20, 10))) / cast([OFFERS] as decimal(20, 10)) else 0 end
            when 4 -- per thousand

            then (cast(@PACKAGECOST as decimal(20, 10)) / cast(1000 as decimal(20, 10))) + cast(@INSERTCOST as decimal(20, 10))--INSERTCOST has already been divided by 1000 when we cached it

            else 0 end -- per marketing effort (included later), not included in marketing effort cost

          + @COSTPERRECORD as [COSTPERPIECE],
          [OFFERS],
          [RESPONDERS],
          [RESPONSES],
          [GIFTAMOUNT],
          [AVERAGEGIFTAMOUNT],
          @SEQUENCE
        from @SEGMENTCOUNTS;

        if @PACKAGECOSTDISTRIBUTIONMETHODCODE = 2
          set @FIXEDCOST = @FIXEDCOST + isnull(@PACKAGECOST, 0) + isnull(@INSERTCOST, 0);

        fetch next from SEGMENTCURSOR into @PACKAGEID, @PACKAGENAME, @PACKAGECOST, @PACKAGECOSTDISTRIBUTIONMETHODCODE, @SEGMENTID, @SEGMENTNAME, @MAILINGSEGMENTID, @SEQUENCE, @COSTPERRECORD, @INSERTCOST;
      end;

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;

    /* Calculate the fixed cost per person */
    if cast(@TOTALOFFERS as decimal(20, 10)) > 0 
      set @FIXEDCOSTPERPIECE = cast(@FIXEDCOST as decimal(20, 10)) / cast(@TOTALOFFERS as decimal(20, 10))

    select 
      [MAILINGNAME],
      [MAILINGDESCRIPTION],
      [MAILINGCODE],
      [MAILINGSITE],
      [PACKAGEID],
      [PACKAGENAME],
      [SEGMENTID],
      [SEGMENTNAME],
      case when ([AVERAGEGIFTAMOUNT] - ([COSTPERPIECE] + @FIXEDCOSTPERPIECE)) <> 0
           then [COSTPERPIECE] / ([AVERAGEGIFTAMOUNT] - ([COSTPERPIECE] + @FIXEDCOSTPERPIECE)) 
           else 0 end
      as [BREAKEVEN],
      case when [OFFERS] <> 0 and ([AVERAGEGIFTAMOUNT] - ([COSTPERPIECE] + @FIXEDCOSTPERPIECE)) <> 0 and [COSTPERPIECE] <> 0
           then (
                 (
                  (cast([RESPONSES] as decimal(20, 10)) / cast([OFFERS] as decimal(20, 10)))
                  -
                  (cast([COSTPERPIECE] as decimal(20, 10)) / cast([AVERAGEGIFTAMOUNT] - ([COSTPERPIECE] + @FIXEDCOSTPERPIECE) as decimal(20, 10)))
                 )
                 /
                 (cast([COSTPERPIECE] as decimal(20, 10)) / cast([AVERAGEGIFTAMOUNT] - ([COSTPERPIECE] + @FIXEDCOSTPERPIECE) as decimal(20, 10)))
                ) * 100
           else 0
      end as [BREAKEVENINDEX],
      [COSTPERPIECE],
      @FIXEDCOSTPERPIECE as [FIXEDCOSTPERPIECE],
      [OFFERS],
      [RESPONDERS],
      [AVERAGEGIFTAMOUNT] as [AVGGIFTAMOUNT],
      [SEQUENCE] 
    from @DATA;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;