USP_DATALIST_FAFCOMMUNICATIONPERFORMANCE

CREATE procedure dbo.USP_DATALIST_FAFCOMMUNICATIONPERFORMANCE(@EVENTID uniqueidentifier)
as
    set nocount on;

  declare @TEMP_CommunicationChannels as table
  (
      CHANNELCODE int,
      CHANNEL nvarchar(20)
  )

  insert into @TEMP_CommunicationChannels (CHANNELCODE, CHANNEL)
  values (1, 'Email')
  insert into @TEMP_CommunicationChannels (CHANNELCODE, CHANNEL)
  values (4, 'Social media')
  insert into @TEMP_CommunicationChannels (CHANNELCODE, CHANNEL)
  values (0, 'Others')

  -- registration

  declare @TEMP_CommunicationChannelRegistrant as table
  (
      CHANNELCODE int,
      REGISTRANTCOUNT int,
      REGISTRATIONAMOUNT money
  )

  insert into @TEMP_CommunicationChannelRegistrant (CHANNELCODE, REGISTRANTCOUNT, REGISTRATIONAMOUNT)
  select
      TCC.CHANNELCODE,
      ISNULL(COUNT(RG.ID), 0),
      ISNULL(SUM(ER.[APPLIED AMOUNT]), 0)

  from @TEMP_CommunicationChannels TCC
  left join dbo.FAFEVENTCOMMUNICATIONCHANNEL FCC (nolock) on TCC.CHANNELCODE = FCC.CHANNELCODE and FCC.TYPECODE = 0
  left join dbo.REGISTRANT RG (nolock) on RG.ID = FCC.TYPEGUID and RG.EVENTID = @EVENTID
  left join dbo.UFN_REVENUE_EVENTREVENUEDETAILS(@EVENTID) ER on ER.CONSTITUENTID = RG.CONSTITUENTID and ER.TRANSACTIONTYPE = 'Event registration'
  group by TCC.CHANNELCODE

  -- get deleted registration 

  Update  tcr
      Set tcr.REGISTRATIONAMOUNT = tcr.REGISTRATIONAMOUNT + DELETEREGISTRATION.DeletedRegistrationAmount
      from @TEMP_CommunicationChannelRegistrant tcr
         inner join 
         (Select Sum(RGH.RegistrationAmount) as DeletedRegistrationAmount, 
            COUNT(RGH.ID) as DeletedRegistrationCount,
            FCC.ChannelCode as DeletedRegistrationChannel
             from dbo.FAFEVENTCOMMUNICATIONCHANNEL FCC (nolock) 
                  inner join dbo.REGISTRATIONDELETEHISTORY RGH (nolock) on RGH.REGISTRANTID = FCC.TYPEGUID 
                  left join dbo.UFN_REVENUE_EVENTREVENUEDETAILS(@EVENTID) ER 
                      on ER.CONSTITUENTID = RGH.CONSTITUENTID and ER.TRANSACTIONTYPE = 'Event registration'
             where RGH.EVENTID = @EVENTID
             group by FCC.ChannelCode) DELETEREGISTRATION
          on DELETEREGISTRATION.DeletedRegistrationChannel = tcr.CHANNELCODE
    where DELETEREGISTRATION.DeletedRegistrationChannel = tcr.CHANNELCODE

  -- donation

  declare @ISUNPAIDPLEDGES bit = 0
  declare @ISUNPAIDRECURRINGGIFTS bit = 0

  select
      @ISUNPAIDPLEDGES = ISUNPAIDPLEDGES,
      @ISUNPAIDRECURRINGGIFTS = ISUNPAIDRECURRINGGIFTS
  from dbo.FAFEVENTDONATIONOPTIONSCONFIG (nolock) where EVENTID = @EVENTID


  declare @TEMP_CommunicationChannelDonation as table
  (
      CHANNELCODE int,
      DONATIONCOUNT int,
      DONATIONAMOUNT money
  )

  insert into @TEMP_CommunicationChannelDonation (CHANNELCODE, DONATIONCOUNT, DONATIONAMOUNT)
  select
      TCC.CHANNELCODE,
      ISNULL(COUNT(ER.REVENUEID), 0),
      SUM
      (
          case
              when ER.TRANSACTIONTYPE = 'donation' then 
                  ER.ORIGINALAMOUNT
              when ER.TRANSACTIONTYPE = 'pledge' then
                  case
                      when @ISUNPAIDPLEDGES = 0 then
                          ER.PAIDAMOUNT
                      else
                          ER.ORIGINALAMOUNT
                  end
              when ER.TRANSACTIONTYPE = 'recurring gift' then
                  case
                      when @ISUNPAIDRECURRINGGIFTS = 0 then
                          ER.PAIDAMOUNT
                      else
                          ER.ORIGINALAMOUNT
                  end
          end
      )
  from @TEMP_CommunicationChannels TCC
  left join dbo.FAFEVENTCOMMUNICATIONCHANNEL FCC (nolock) on TCC.CHANNELCODE = FCC.CHANNELCODE and FCC.TYPECODE = 1
  left join
  (
      select
          REVENUEID,
          TRANSACTIONTYPE,
          case
              when TRANSACTIONTYPE = 'donation' or TRANSACTIONTYPE = 'pledge' then
                  AMOUNT
              when TRANSACTIONTYPE = 'recurring gift' then
                  case
                      when NUMBEROFINSTALLMENTS > 0 then
                          AMOUNT * NUMBEROFINSTALLMENTS
                      when NUMBEROFINSTALLMENTS = 0 then
                          ISNULL(dbo.UFN_RECURRINGGIFT_GETBALANCEASOF(REVENUEID, CURRENT_TIMESTAMP), 0)
                  end
          end as ORIGINALAMOUNT,
          case
              when TRANSACTIONTYPE = 'donation' then
                  AMOUNT
              when TRANSACTIONTYPE = 'pledge' then
                  ISNULL(dbo.UFN_PLEDGE_GETAMOUNTPAID(REVENUEID), 0)
              when TRANSACTIONTYPE = 'recurring gift' then
                  ISNULL(dbo.UFN_RECURRINGGIFT_GETBALANCEASOF(REVENUEID, CURRENT_TIMESTAMP), 0)
          end as PAIDAMOUNT
      from
      (
          select
              RVS.REVENUEID,
              case
                  when RV.TRANSACTIONTYPECODE = 0 and RVS.APPLICATIONCODE = 0 then 'donation'
                  when RV.TRANSACTIONTYPECODE = 1 and RVS.APPLICATIONCODE = 0 then 'pledge'
                  when RV.TRANSACTIONTYPECODE = 2 and RVS.APPLICATIONCODE = 0 then 'recurring gift'
              end as TRANSACTIONTYPE,
              RVS.AMOUNT,
              case
                  when RVSCH.NUMBEROFINSTALLMENTS = 0 and RVSCH.ENDDATE is not null then
                      case RVSCH.FREQUENCYCODE
                          when 0 then -- annually

                              DATEDIFF(MONTH, RVSCH.STARTDATE, RVSCH.ENDDATE) / 12 + 1
                          when 1 then -- semi-annual

                              DATEDIFF(MONTH, RVSCH.STARTDATE, RVSCH.ENDDATE) / 6 + 1
                          when 2 then -- quarterly

                              DATEDIFF(MONTH, RVSCH.STARTDATE, RVSCH.ENDDATE) / 3 + 1
                          when 3 then -- monthly

                              DATEDIFF(MONTH, RVSCH.STARTDATE, RVSCH.ENDDATE) + 1
                          when 5 then -- once

                              1
                          when 6 then -- bimonthly

                              DATEDIFF(MONTH, RVSCH.STARTDATE, RVSCH.ENDDATE) / 2 + 1
                          when 7 then -- semi-monthly

                              DATEDIFF(MONTH, RVSCH.STARTDATE, RVSCH.ENDDATE) * 2 + 1
                          when 8 then -- biweekly

                              DATEDIFF(WEEK, RVSCH.STARTDATE, RVSCH.ENDDATE) / 2 + 1
                          when 9 then -- weekly

                              DATEDIFF(WEEK, RVSCH.STARTDATE, RVSCH.ENDDATE) + 1
                      end
                  else ISNULL(RVSCH.NUMBEROFINSTALLMENTS, 0)
              end as NUMBEROFINSTALLMENTS

          from dbo.REVENUESPLIT RVS (nolock)
          inner join dbo.REVENUE RV (nolock) 
              on RV.ID = RVS.REVENUEID
              and 
              (
                  (RV.TRANSACTIONTYPECODE = 0 and RVS.APPLICATIONCODE = 0) or --Donation

                  (RV.TRANSACTIONTYPECODE = 1 and RVS.APPLICATIONCODE = 0) or --Pending pledge

                  (RV.TRANSACTIONTYPECODE = 2 and RVS.APPLICATIONCODE = 0)    --Pending recurring gift

              )
          inner join dbo.EVENT E (nolock) on E.APPEALID = RV.APPEALID
          left join dbo.REVENUESCHEDULE RVSCH (nolock) on RVSCH.ID = RV.ID

          where E.ID = @EVENTID
      ) FAFEVENTREVENUEAMOUNTDETAIL
  ) as ER on ER.REVENUEID = FCC.TYPEGUID


  group by TCC.CHANNELCODE


  select
      TCC.CHANNELCODE,
      TCC.CHANNEL,
      TCRG.REGISTRANTCOUNT,
      TCRG.REGISTRATIONAMOUNT,
      TCRV.DONATIONCOUNT,
      ISNULL(TCRV.DONATIONAMOUNT, 0) as DONATIONAMOUNT
  from @TEMP_CommunicationChannels TCC
  inner join @TEMP_CommunicationChannelRegistrant TCRG on TCRG.CHANNELCODE = TCC.CHANNELCODE
  inner join @TEMP_CommunicationChannelDonation TCRV on TCRV.CHANNELCODE = TCC.CHANNELCODE