USP_FAFDONATION_CALCULATE

CREATE procedure dbo.USP_FAFDONATION_CALCULATE
(  
    @NUMBERPROCESSED integer = 0 output  
)  
as
begin
    -- do work 1


  -- get active events

  DECLARE @ACTIVEEVENTS TABLE
  (
       ID       int IDENTITY(1,1),
       EVENTID  uniqueidentifier
  )

  insert into @ACTIVEEVENTS(EVENTID)
    select e.ID from dbo.[EVENT] e 
       inner join dbo.EVENTEXTENSION et
          on e.ID = et.EVENTID
    where ISACTIVE =1

  declare @NUMEVENTS   int
  Select @NUMEVENTS = max(ID) from @ACTIVEEVENTS

  -- get max sequence number of donation data

  declare @curBatchNumber int
  set @curBatchNumber = (select top 1 BatchNumber from dbo.FAFDonation)

  if isnumeric(@curBatchNumber) = 0
     begin
        set @curBatchNumber = 0
     end

  -- remove old data

  truncate table FAFDonation

  declare @CHANGEAGENTID uniqueidentifier,
         @CURRENTDATE datetime;

 if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

 set @CURRENTDATE = getdate()

 -- insert donation data

 declare @count  int
 set @count = 1
 declare @eventID  uniqueidentifier

-- start transaction


while @count <= @NUMEVENTS
begin 
    Select @eventID = eventid from @ACTIVEEVENTS where id = @count

    begin try
         insert into dbo.FAFDONATION
         (
            REVENUEID,
            CONSTITUENTID,
            EVENTID,
            FIRSTNAME,
            LASTNAME,
            [ADDRESS],
            ADDRESSTYPE,
            CITY,
            [STATE],
            ZIP,
            COUNTRY,
            EMAIL,
            PHONENUMBER,
            PHONETYPE,
            TITLE,
            SUFFIX,
            AMOUNT,
            ISANONYMOUSGIFT,
            DONOTCALL,
            DONOTMAIL,
            DONOTEMAIL,
            CREDITCARDTYPE,
            CHECKNUMBER,
            RECEIPTNUMBER,
            CHECKDATE,
            GIFTDATE,
            INBOUNDCHANNEL,
            SOLICITATIONCHANNNEL,
            ISONLINEGIFT,
            TRANSACTIONTYPE,
            PAYMENTMETHOD,
            POSTSTATUS,
            TRIBUTEE,
            TRIBUTEMESSAGE,
            TRIBUTETYPE, 
            MATCHINGGIFTCLAIMED,
            MATCHINGGIFTPAID,
            MATCHINGGIFTORGANIZATION,
            RECOGNITION,
            ISPREVIOUSDONOR,
            REGISTRANTROLE,
            GROUPNAME,
            GROUPTYPE,
            COMPANYNAME,
            COMPANYADDRESS,
            COMPANYADDRESSTYPE,
            COMPANYCITY,
            COMPANYSTATE,
            COMPANYZIP,
            COMPANYPHONE,
            COMPANYEMAIL,
            COMPANYWEBADDRESS,
            ISCOMPANYDONATION,
            REVENUEDATECHANGED,
            REVENUECHANGEDBY,
            DONORLOOKUPID,
            REVENUELOOKUPID,
            RECOGNITIONID,
            BATCHNUMBER,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )

        select distinct
         fr.ID as RevenueID,
         c.ID as ConstituentID,
         e.ID as EventID,

         FirstName =
            case c.ISORGANIZATION
               when 0 then isnull(c.FIRSTNAME, '')
               else ''
            end,
         LastName =
            case c.ISORGANIZATION
               when 0 then isnull(c.KEYNAME,'')
               else ''
            end,
         [Address] =
            case c.ISORGANIZATION
               when 0 then isnull(a.ADDRESSBLOCK,'')
               else ''
            end,
         AddressType =
            case c.ISORGANIZATION
               when 0 then isnull(atc.[DESCRIPTION], '')
               else ''
            end,
         City =
             case c.ISORGANIZATION
               when 0 then isnull(a.[CITY], '')
               else ''
             end,
         [STATE] =
             case c.ISORGANIZATION
               when 0 then isnull(st.ABBREVIATION, '') 
        else ''
             end,
         ZIP =
             case c.ISORGANIZATION
               when 0 then isnull(a.POSTCODE, '')
               else ''
             end,
          Country =    
             case c.ISORGANIZATION
               when 0 then isnull(ct.DESCRIPTION, '')
               else ''
             end,
          Email =
             case c.ISORGANIZATION
               when 0 then isnull(ea.EMAILADDRESS, '')
               else ''
             end,
         PhoneNumber =
             case c.ISORGANIZATION
               when 0 then isnull(p.NUMBER, '')    
               else ''
             end,
         isnull(ptc.[DESCRIPTION], '') as PhoneType,   
         isnull(tc.[DESCRIPTION], '') as Title,
         isnull(sc.[DESCRIPTION], '') as Suffix, 
         rs.AMOUNT as Amount,
         R_EXT.GIVENANONYMOUSLY as AnonymousGift,
         isnull(p.DONOTCALL, 0) As DoNotCall, 
         isnull(a.DONOTMAIL, 0) As DoNotMail, 
         isnull(ea.DONOTEMAIL, 0) As DoNotEmail,
         isnull(ctp.[DESCRIPTION], '') As CreditcardType,
         isnull(ckpmd.CHECKNUMBER, '') As CheckNumber,
         isnull(rpt.RECEIPTNUMBER, ''),
         CheckDate =  
             case isdate(ckpmd.CHECKDATE) 
               when 1 then ckpmd.CHECKDATE  
               else null  
             end,    
         fr.DATEADDED    As GiftDate,
         isnull(cc.[DESCRIPTION], '') as InboundChannel,
         isnull(fccl.Channel, 'Others') as SOLICITATIONCHANNNEL,
         IsOnlineGift = 
             case rp.PAYMENTMETHODCODE
               when 2 Then 1
               else 0
             end,
         isnull(fr.[TYPE], '') as Transactiontype, 
         isnull(rp.PAYMENTMETHOD, '') as PaymentMethod,
         PostStatus = isnull(fr.PostStatus, ''),
         isnull(tet.TRIBUTEEFIRSTNAME + ' ' + tet.TRIBUTEELASTNAME, '') as Tributee,
         isnull(t.TRIBUTETEXT, '') as TributeMessage,
         isnull(ttc.DESCRIPTION, '') as TributeType,
         isnull(MatchingGift.ClaimAmount, 0) as  MatchingGiftClaimed,
         isnull(MatchingGift.PaidAmount, 0) as MatchingGiftPaid,
         isnull(MatchingGift.CompanyName, '') as MatchingGiftOrganization,
         isnull(C3.NAME, '') as Recognition, 
         0 as ISPREVIOUSDONOR,
         isnull(Registrant.[Role], 'Individual') as [RegistrantRole],
         isnull(Registrant.Groupname,'') As GroupName,
         isnull(Registrant.GroupType, '') As GroupType,
         CompanyName =
            case c.ISORGANIZATION
               when 1 then isnull(c.KEYNAME, '')
               else ''
            end,
         CompanyAddress =
             case c.ISORGANIZATION
               when 1 then isnull(a.ADDRESSBLOCK, '')
               else ''
            end,
         CompanyAddressType =
             case c.ISORGANIZATION
               when 1 then isnull(atc.[DESCRIPTION], '')
               else ''
            end,    
         CompanyCity =
             case c.ISORGANIZATION
               when 1 then isnull(a.[CITY], '')
               else ''
             end,
         COMPANYSTATE =
             case c.ISORGANIZATION
               when 1 then isnull(st.ABBREVIATION, '')
               else ''
             end,
         COMPANYZIP =
             case c.ISORGANIZATION
               when 1 then isnull(a.POSTCODE, '')
               else ''
             end,  

         COMPANYPhoneNumber =
             case c.ISORGANIZATION
               when 1 then isnull(p.NUMBER, '')     
               else ''
             end,

         CompanyEmail =
             case c.ISORGANIZATION
               when 1 then isnull(ea.EMAILADDRESS, '')
               else ''
             end, 
         isnull(c.WEBADDRESS, '') As Companywebsite,

         IsCompanyDonation =
            case c.ISORGANIZATION
              when 1 Then 1
              else 0
            end,
          fr.DateChanged as RevenueChangedDate,
          ca.UserName as RevenueChangedBy,
          isnull(c.LOOKUPID, '') as DonorLookupID, 
          isnull(fr.CALCULATEDUSERDEFINEDID, '') as RevenueLookUpID,
          C3.ID,
          @curBatchNumber + 1,
          @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE  
      from dbo.FINANCIALTRANSACTION fr (nolock)      
        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI (nolock)  
          on FTLI.FINANCIALTRANSACTIONID = fr.ID 
          inner join dbo.REVENUESPLIT_EXT RS_EXT (nolock)  
            on RS_EXT.ID = FTLI.ID 
        inner join dbo.REVENUE_EXT R_EXT (nolock)  
          on fr.ID = R_EXT.ID  
        inner Join dbo.[CONSTITUENT] c (nolock)  
              on fr.CONSTITUENTID = c.id
        left join dbo.CHANGEAGENT ca (nolock)  
          on fr.ChangedByID = ca.ID
        left outer join dbo.TITLECODE tc (nolock)  
              on c.TITLECODEID = tc.ID 
        left outer join dbo.SUFFIXCODE sc (nolock)  
              on c.SUFFIXCODEID = sC.ID
        left Join dbo.[ADDRESS] a (nolock)  
            on c.ID = a.CONSTITUENTID and a.ISPRIMARY = 1
        left outer join dbo.[STATE] st (nolock)  
            on a.STATEID = st.ID   
        left Join dbo.ADDRESSTYPECODE atc    (nolock)  
           on atc.ID = a.ADDRESSTYPECODEID
          left join dbo.EMAILADDRESS ea (nolock)  
             on ea.CONSTITUENTID = c.ID and ea.ISPRIMARY = 1
          left join dbo.phone p (nolock)  
                 on p.CONSTITUENTID = c.ID and p.ISPRIMARY = 1
          left join dbo.PHONETYPECODE ptc (nolock)  
                 on p.PHONETYPECODEID = ptc.ID 
          left join dbo.COUNTRY ct (nolock)  
                 on ct.ID = a.COUNTRYID 
          left Join dbo.[REVENUETRIBUTE] rt (nolock)  
                 on fr.ID = rt.REVENUEID
        left join dbo.[TRIBUTEEXTENSION] tet (nolock)  
             on rt.TRIBUTEID = tet.TRIBUTEID
          left join dbo.TRIBUTE t (nolock)  
                 on rt.TRIBUTEID = t.ID
        left join dbo.TRIBUTETYPECODE ttc (nolock) 
             on ttc.ID = t.TRIBUTETYPECODEID
          left join dbo.REVENUEPAYMENTMETHOD rp (nolock)  
            on fr.ID = rp.REVENUEID
        left join dbo.CHECKPAYMENTMETHODDETAIL ckpmd (nolock)  
            on ckpmd.ID = rp.ID
        left join dbo.CREDITCARDPAYMENTMETHODDETAIL cpmd (nolock)  
            on rp.ID = cpmd.ID
        left join dbo.CREDITTYPECODE ctp (nolock)  
            on ctp.ID = cpmd.CREDITTYPECODEID
        inner join dbo.REVENUESPLIT rs (nolock)  
            on fr.ID = rs.REVENUEID 
        left join dbo.REVENUERECOGNITION rr (nolock)  
            on rs.ID = rr.REVENUESPLITID 
        left join dbo.CONSTITUENT C3 (nolock)    -- recognition

            on rr.CONSTITUENTID = C3.Id 
        left join dbo.REVENUERECEIPT rpt (nolock)  
            on rpt.REVENUEID = fr.ID
        left Join  -- matching gift

           (   -- recurring gift

               select sum(r2.Amount) as ClaimAmount,  
                   sum(ip.AMOUNT) as PaidAmount,
                   rga.SOURCEREVENUEID as SourceRevenueID,
                   c.KEYNAME As CompanyName
              from dbo.REVENUE r (nolock)  
                inner join dbo.REVENUESPLIT rs (nolock)  on r.ID = rs.REVENUEID
                inner join dbo.RECURRINGGIFTACTIVITY rga (nolock) on rga.PAYMENTREVENUEID = rs.ID
                inner join dbo.REVENUEMATCHINGGIFT rm (nolock) on rm.MGSOURCEREVENUEID = r.ID
                inner join dbo.REVENUE r2 (nolock) on r2.ID = rm.ID
                inner join dbo.INSTALLMENT il (nolock) on il.REVENUEID = r2.ID
                inner join dbo.INSTALLMENTPAYMENT ip (nolock) on il.ID = ip.INSTALLMENTID
                inner join CONSTITUENT c (nolock) on c.ID = r2.CONSTITUENTID and c.ISORGANIZATION=1
              where r.TRANSACTIONTYPE = 'Payment'
              group by rga.SOURCEREVENUEID, c.KEYNAME

              union

              -- single gift

              select sum(r2.Amount) as ClaimAmount,  
           sum(ip.AMOUNT) as PaidAmount,
                   rm.MGSOURCEREVENUEID As SourceRevenueID,
                   c.KEYNAME As CompanyName
              from dbo.REVENUE r (nolock)
                inner join dbo.REVENUEMATCHINGGIFT rm (nolock) on rm.MGSOURCEREVENUEID = r.ID
                inner join dbo.REVENUE r2 (nolock) on r2.ID = rm.ID
                inner join dbo.INSTALLMENT il (nolock) on il.REVENUEID = r2.ID
                inner join dbo.INSTALLMENTPAYMENT ip (nolock) on il.ID = ip.INSTALLMENTID
                inner join CONSTITUENT c (nolock) on c.ID = r2.CONSTITUENTID and c.ISORGANIZATION=1
                where r.TRANSACTIONTYPE = 'Payment'
              group by rm.MGSOURCEREVENUEID, c.KEYNAME

              union
              -- pledge

               select  
                      sum(fr2.TRANSACTIONAMOUNT) as ClaimAmount,
                      sum(ip2.AMOUNT) as PaidAmount,
                      ip.PLEDGEID As SourceRevenueID,
                      c.KEYNAME As CompanyName
                from 
                     -- get payment revenueid 

                     dbo.INSTALLMENTPAYMENT ip (nolock)
                     inner join dbo.FINANCIALTRANSACTIONLINEITEM frl (nolock) on frl.ID = ip.PAYMENTID  
                     inner join dbo.REVENUESPLIT_EXT rst (nolock) on frl.ID = rst.ID  
                     inner join dbo.FINANCIALTRANSACTION fr (nolock) on frl.FINANCIALTRANSACTIONID = fr.ID  
                     inner join dbo.REVENUE_EXT (nolock) on fr.ID = REVENUE_EXT.ID
                     -- get matching gift revenueid

                     inner join dbo.REVENUEMATCHINGGIFT rm (nolock) on rm.MGSOURCEREVENUEID = fr.ID
                     inner join dbo.FINANCIALTRANSACTION fr2 (nolock) on fr2.ID = rm.ID
                     inner join CONSTITUENT c (nolock) on c.ID = fr2.CONSTITUENTID and c.ISORGANIZATION=1
                     -- get matching gift payment

                     inner join dbo.INSTALLMENT il (nolock) on il.REVENUEID = fr2.ID
                     inner join dbo.INSTALLMENTPAYMENT ip2 (nolock) on il.ID = ip2.INSTALLMENTID
                     inner join dbo.Revenue r (nolock) on r.ID = ip.PledgeID and r.TransActionType ='Pledge'
                 group by ip.PLEDGEID, c.KEYNAME

           ) MatchingGift
            on MatchingGift.SourceRevenueID = fr.ID
        inner join dbo.[EVENT] e (nolock)  
            on e.APPEALID = R_EXT.APPEALID
        left join -- registrant

           (select distinct
                       rg.CONSTITUENTID,
                       e.ID As EVENTID,
                       tft.NAME as Groupname,
                       ts.[TYPE] As GroupType,
                       case when tftc.ID is not null and ts.TYPECODE = 3 then 'Head of household'  
                       when tftc.ID is null  and ts.TYPECODE = 3 then 'Household member'  
                       when tftc.ID is not null and ts.TYPECODE = 1 then 'Team leader'  
                       when tftc.ID is null and ts.TYPECODE = 1 then 'Team member'  
                       when tftc.ID is not null and ts.TYPECODE = 2 then 'Company leader'  
                       when tftc.ID is null and ts.TYPECODE = 2 then 'Individual'  
                       when tftc.ID is null then 'Individual' end as [Role]  
                  from dbo.REGISTRANT rg With (NOLOCK)
                      inner join dbo.TEAMFUNDRAISER TF With (NOLOCK) on TF.CONSTITUENTID = rg.CONSTITUENTID
                      inner join dbo.TEAMFUNDRAISINGTEAMMEMBER tfm (nolock) on tfm.TEAMFUNDRAISERID = tf.ID
                      inner join dbo.TEAMFUNDRAISINGTEAM tft (nolock) on tfm.TEAMFUNDRAISINGTEAMID = tft.ID
                      inner join dbo.TEAMEXTENSION ts (nolock) on ts.TEAMFUNDRAISINGTEAMID = tft.ID
              inner join dbo.[EVENT] e (nolock) on e.APPEALID = tft.APPEALID
                      left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN tftc (nolock) on tftc.CONSTITUENTID = tf.CONSTITUENTID
            where e.ID = @EventID 
                ) Registrant
               on C3.ID = Registrant.CONSTITUENTID 
         left join dbo.FAFEVENTCOMMUNICATIONCHANNEL fccl (nolock)  
             on fccl.TYPECODE = 1 and fccl.TYPEGUID = fr.ID 
       left join dbo.CHANNELCODE cc (nolock)  
             on cc.ID = R_EXT.CHANNELCODEID
         where (fr.TYPECODE = 0 and rs.APPLICATIONCODE in (0, 7, 17) OR fr.TYPECODE IN (1,2) )
          and fr.[TYPE] <> 'Pending Gift'
          and e.ID = @EventID
          order by fr.DATEADDED desc
    end try
    -- track error

    begin catch
       exec dbo.USP_RAISE_ERROR;    
       return 1
    end catch

    -- add loop count

    set @count = @count + 1
 end   -- end of loop


 -- complete transaction if no error occurs

 begin try

     exec dbo.USP_FAFDonation_Update

 end try
  -- track error

 begin catch
       exec dbo.USP_RAISE_ERROR;  
       return 1
 end catch

  -- get the number of processing

 select @NUMBERPROCESSED=COUNT(*) from dbo.FAFDonation

end