USP_FAFDonation_Update

CREATE procedure dbo.USP_FAFDonation_Update
as
begin
    -- add organization

  update dbo.FAFDonation 
    set FAFDonation.FIRSTNAME = Individual.FIRSTNAME,
        FAFDonation.LASTNAME = Individual.LASTNAME,
        FAFDonation.[ADDRESS] = Individual.[ADDRESS],
        FAFDonation.[ADDRESSTYPE] = Individual.ADDRESSTYPE,
        FAFDonation.CITY = Individual.CITY,
        FAFDonation.[STATE] = Individual.[STATE],
        FAFDONATION.ZIP = Individual.ZIP,
        FAFDONATION.COUNTRY = Individual.COUNTRY,
        FAFDONATION.EMAIL = Individual.EMAIL, 
        FAFDONATION.PHONENUMBER = Individual.PHONENUMBER
    from dbo.FAFDonation fd (nolock) 
       inner join
        (select 
              firr.REVENUEID,
              isnull(c.FIRSTNAME, '') as FIRSTNAME,
              isnull(c.KEYNAME, '') as LASTNAME,
              isnull(a.ADDRESSBLOCK, '') as [ADDRESS],
              isnull(atc.[DESCRIPTION], '') as ADDRESSTYPE,
              isnull(a.[CITY], '') as CITY,
              isnull(st.ABBREVIATION, '') as [STATE],
              isnull(a.POSTCODE, '') as ZIP,
              isnull(ct.[DESCRIPTION], '') as COUNTRY,
              ISNULL(ea.EMAILADDRESS, '') as EMAIL,
              ISNULL(p.NUMBER, '') as PHONENUMBER
         from dbo.FAFORGANIZATIONINDIVIDUALRELATION firr (nolock) 
            inner join dbo.[CONSTITUENT] c (nolock)  
                on firr.INDIVIDUALCONSTITUENTID = c.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
            where c.IsORGANIZATION = 0) Individual
      on fd.REVENUEID = Individual.REVENUEID and fd.ISCOMPANYDONATION = 1

    -- get group name and type

    update dbo.FAFDonation 
        set FAFDonation.GROUPNAME = FAFGROUP.GROUPNAME,
            FAFDonation.GROUPTYPE = FAFGROUP.GROUPTYPE,
            FAFDonation.RegistrantRole = ''
     from dbo.FAFDonation (nolock)
       inner join
          (select distinct 
                   ts.TEAMCONSTITUENTID as constituentID, 
                   ts.EVENTID,
                   tfm.NAME As Groupname, 
                   ts.[TYPE] as GroupType,
                   '' As [Role]
                from dbo.TEAMEXTENSION ts (nolock)  
                     inner join dbo.TEAMFUNDRAISINGTEAM  tfm (nolock)  
                       on ts.TEAMFUNDRAISINGTEAMID = tfm.ID) FAFGROUP
           on FAFDonation.RECOGNITIONID = FAFGROUP.constituentID
     where FAFDonation.GROUPNAME = ''

     -- update previous donor

     update dbo.FAFDonation 
        set FAFDonation.ISPREVIOUSDONOR =
             case isnull(PriorEvent.CurrentEVENTID, '00000000-0000-0000-0000-000000000000')
             when '00000000-0000-0000-0000-000000000000'  then 0
             else 1
             end
        from dbo.FAFDonation (nolock)
          left join
              (select distinct Revenue.CONSTITUENTID, 
                      et.EVENTID As CurrentEventID
                    from dbo.EVENTEXTENSION et (nolock)  
                         inner join dbo.[Event] e (nolock)  
                              on et.PRIORYEAREVENTID = e.ID
                         inner join 
                           (select distinct R_EXT.APPEALID, fr.CONSTITUENTID
                              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 and RS_EXT.[APPLICATION] = 'Donation' 
                                  inner join dbo.REVENUE_EXT R_EXT (nolock)  
                                      on fr.ID = R_EXT.ID) Revenue
                        on Revenue.APPEALID = e.APPEALID) priorEvent
              on FAFDonation.EVENTID = priorEvent.CurrentEventID 
            and FAFDonation.CONSTITUENTID = priorEvent.CONSTITUENTID

     -- update recognition fro a single donor

     update dbo.FAFDonation 
          set RECOGNITION =
             case isnull(REGISTRANT.CONSTITUENTID, '00000000-0000-0000-0000-000000000000')
             when '00000000-0000-0000-0000-000000000000'  then ''
             else FAFDonation.RECOGNITION
             end

          from dbo.FAFDonation (nolock)
             left join dbo.REGISTRANT (nolock) 
                 on FAFDonation.RECOGNITIONID = REGISTRANT.CONSTITUENTID
                     and FAFDonation.EVENTID = REGISTRANT.EVENTID
         where FAFDonation.CONSTITUENTID = FAFDonation.RECOGNITIONID 
               and FAFDonation.REGISTRANTROLE = 'Individual'

end