UFN_MKTSEGMENTATION_GETUKGIFTINFO

CREATE function dbo.[UFN_MKTSEGMENTATION_GETUKGIFTINFO]
(
  @MKTSEGMENTATIONID as uniqueidentifier
)
returns @UKGIFTINFO table
(
  REGULARGIFTPAYMENTS int,
  REGULARGIFTREVENUE money,
  REGULARGIFTAVERAGE money,
  CASHGIFTPAYMENTS int,
  CASHGIFTREVENUE money,
  CASHGIFTAVERAGE money,
  GROSSAMOUNT money,
  ORGANIZATIONREGULARGIFTREVENUE money,
  ORGANIZATIONREGULARGIFTAVERAGE money,
  ORGANIZATIONCASHGIFTREVENUE money,
  ORGANIZATIONCASHGIFTAVERAGE money,
  ORGANIZATIONGROSSAMOUNT money
)
as
begin

  declare @IDSETREGISTERID uniqueidentifier;

  select
    @IDSETREGISTERID = [NORMALGIFTIDSETREGISTERID]
  from dbo.[MKTSEGMENTATIONACTIVATE]
  where [SEGMENTATIONID] = @MKTSEGMENTATIONID;

  /* Calculate the cash and regular gift amounts: */
  with [UKGIFTS_CTE] ([REGULARGIFTID], [CASHGIFTID], [REGULARGIFTAMOUNT], [CASHGIFTAMOUNT], [TAXCLAIMAMOUNT], [ORGANIZATIONREGULARGIFTAMOUNT], [ORGANIZATIONCASHGIFTAMOUNT], [ORGANIZATIONTAXCLAIMAMOUNT]) as
  (
    select
      [REGULARGIFTREVENUESPLIT].[ID],
      [CASHGIFTREVENUESPLIT].[ID],
      [REGULARGIFTREVENUESPLIT].[AMOUNT],
      [CASHGIFTREVENUESPLIT].[AMOUNT],
      [REVENUESPLITGIFTAID].[TAXCLAIMAMOUNT],
      [REGULARGIFTREVENUESPLIT].[ORGANIZATIONAMOUNT],
      [CASHGIFTREVENUESPLIT].[ORGANIZATIONAMOUNT],
      [ORGANIZATIONREVENUESPLITGIFTAID].[TAXCLAIMAMOUNT]
    from dbo.[UFN_IDSETREADER_GETRESULTS](@IDSETREGISTERID) as [NORMALGIFT]
    left outer join dbo.[REVENUE] as [REGULARGIFTREVENUE] on [REGULARGIFTREVENUE].[ID] = [NORMALGIFT].[ID] and [REGULARGIFTREVENUE].[TRANSACTIONTYPECODE] = 0
    left outer join dbo.[REVENUESPLIT] as [REGULARGIFTREVENUESPLIT] on [REGULARGIFTREVENUESPLIT].[REVENUEID] = [REGULARGIFTREVENUE].[ID] and REGULARGIFTREVENUESPLIT.[APPLICATIONCODE] = 3
    left outer join dbo.[REVENUE] as [CASHGIFTREVENUE] on [CASHGIFTREVENUE].[ID] = [NORMALGIFT].[ID] and ([CASHGIFTREVENUE].[TRANSACTIONTYPECODE] = 0 or [CASHGIFTREVENUE].[TRANSACTIONTYPECODE] = 1)
    left outer join dbo.[REVENUESPLIT] as [CASHGIFTREVENUESPLIT] on [CASHGIFTREVENUESPLIT].[REVENUEID] = [CASHGIFTREVENUE].[ID] and [CASHGIFTREVENUESPLIT].[APPLICATIONCODE] = 0
    left outer join dbo.[UFN_REVENUESPLITGIFTAID_GETELIGIBLE](1) as [REVENUESPLITGIFTAID] on [REVENUESPLITGIFTAID].[ID] = [REGULARGIFTREVENUESPLIT].[ID] or [REVENUESPLITGIFTAID].[ID] = [CASHGIFTREVENUESPLIT].[ID]
    left outer join dbo.[UFN_REVENUESPLITGIFTAID_GETELIGIBLE_2](1, 2) as [ORGANIZATIONREVENUESPLITGIFTAID] on [REVENUESPLITGIFTAID].[ID] = [REGULARGIFTREVENUESPLIT].[ID] or [REVENUESPLITGIFTAID].[ID] = [CASHGIFTREVENUESPLIT].[ID]
  )
  insert into @UKGIFTINFO
  (
    [REGULARGIFTPAYMENTS],
    [REGULARGIFTREVENUE],
    [REGULARGIFTAVERAGE],
    [CASHGIFTPAYMENTS],
    [CASHGIFTREVENUE],
    [CASHGIFTAVERAGE],
    [GROSSAMOUNT],
    [ORGANIZATIONREGULARGIFTREVENUE],
    [ORGANIZATIONREGULARGIFTAVERAGE],
    [ORGANIZATIONCASHGIFTREVENUE],
    [ORGANIZATIONCASHGIFTAVERAGE],
    [ORGANIZATIONGROSSAMOUNT]
  )
  select 
    count([REGULARGIFTID]) as [NUMREGULARGIFTS],
    isnull(sum([REGULARGIFTAMOUNT]), 0) as [REGULARGIFTTOTAL], 
    isnull(avg([REGULARGIFTAMOUNT]), 0) as [REGULARGIFTAVERAGE],
    count([CASHGIFTID]) as [NUMCASHGIFTS],
    isnull(sum([CASHGIFTAMOUNT]), 0) as [CASHGIFTTOTAL],
    isnull(avg([CASHGIFTAMOUNT]), 0) as [CASHGIFTAVERAGE],
    isnull(sum([CASHGIFTAMOUNT]), 0) + isnull(sum([REGULARGIFTAMOUNT]), 0) + isnull(sum([TAXCLAIMAMOUNT]), 0) as [GROSSAMOUNT],
    isnull(sum([ORGANIZATIONREGULARGIFTAMOUNT]), 0) as [ORGANIZATIONREGULARGIFTTOTAL], 
    isnull(avg([ORGANIZATIONREGULARGIFTAMOUNT]), 0) as [ORGANIZATIONREGULARGIFTAVERAGE],
    isnull(sum([ORGANIZATIONCASHGIFTAMOUNT]), 0) as [ORGANIZATIONCASHGIFTTOTAL],
    isnull(avg([ORGANIZATIONCASHGIFTAMOUNT]), 0) as [ORGANIZATIONCASHGIFTAVERAGE],
    isnull(sum([ORGANIZATIONCASHGIFTAMOUNT]), 0) + isnull(sum([ORGANIZATIONREGULARGIFTAMOUNT]), 0) + isnull(sum([ORGANIZATIONTAXCLAIMAMOUNT]), 0) as [ORGANIZATIONGROSSAMOUNT]
  from [UKGIFTS_CTE];

  return;
end