USP_DATAFORMTEMPLATE_EDIT_REVENUEOFFLINEDONATION_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEOFFLINEDONATION_2 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,

    @REGISTRANTID uniqueidentifier,
    @ADDRESSBOOKFAFID uniqueidentifier,
    @DONORNAME nvarchar(255),
    @AMOUNT money,     
    @PAYMENTTYPECODE tinyint,
    @CHECKNUMBER nvarchar(20),
    @EXPECTEDDATE datetime
   ,@LOCALCORPNAME nvarchar(20) --    = 'none' -- 'none', 1= 'preferredcorp',  2 = 'zipcode'
   ,@LOCALCORPZIP nvarchar(20)  -- = ''
   ,@CURRENTAPPUSERID uniqueidentifier = null     
 --,@AMOUNTCONFIRMED money 
 --,@GIFTID    int  
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

  declare @ISGIFTCONFIRMED bit = 0
  select  @ISGIFTCONFIRMED = case when PLEDGEID is not null then 1 else 0 end 
  from dbo.INSTALLMENTSPLITPAYMENT  
  where PLEDGEID= @ID

  if @ISGIFTCONFIRMED = 1
  begin
        raiserror('The donation has been confirmed by the event administrator and cannot be edited.',13,1);
      return 1;
    end

    BEGIN TRY

    -- insert new donor into all members' of the household address 
    declare        @FNAME nvarchar (510), @MIDDLENAME nvarchar (200), @LASTNAME nvarchar (510), @EMAILADDRESS UDT_EMAILADDRESS, @CITY nvarchar (100), @POSTCODE nvarchar (24), 
          @CELLPHONE nvarchar(50), @HOMEPHONE nvarchar(50), @STATEDESC nvarchar (200), @COUNTRYDESC nvarchar (200), @ADDRESSBLOCK nvarchar (200), 
          @STATEID uniqueidentifier, @COUNTRYID uniqueidentifier, @TITLECODEID uniqueidentifier, @CONSTID uniqueidentifier;
    declare @CURRENTCONSTITUENTID uniqueidentifier, @EVENTID uniqueidentifier;

    -- if change the donor, insert the new donor into all members' of the household address 
    IF NOT EXISTS (SELECT * FROM dbo.REVENUEOFFLINEDONATION  WHERE ID=@ID AND ADDRESSBOOKFAFID=@ADDRESSBOOKFAFID)
      if exists (select * from dbo.ADDRESSBOOKFAF where    ID= @ADDRESSBOOKFAFID)    
      begin
          select    @FNAME = FIRSTNAME, @MIDDLENAME = MIDDLENAME, @LASTNAME = LASTNAME, @EMAILADDRESS = EMAILADDRESS, @CITY = CITY, @POSTCODE = POSTCODE, 
                @CELLPHONE = CELLPHONE, @HOMEPHONE = HOMEPHONE, @STATEDESC= S.[DESCRIPTION], @COUNTRYDESC= C.[DESCRIPTION], @ADDRESSBLOCK = ABF.ADDRESSBLOCK, 
                @STATEID= ABF.STATEID, @COUNTRYID= ABF.COUNTRYID, @TITLECODEID=ABF.TITLECODEID, @CONSTID=CONSTITUENTID
          from        dbo.ADDRESSBOOKFAF ABF
          left join    dbo.[STATE] S        ON  ABF.STATEID= S.ID
          left join    dbo.[COUNTRY] C    ON  ABF.COUNTRYID= C.ID
          where    ABF.ID=@ADDRESSBOOKFAFID;

        select @CURRENTCONSTITUENTID=CONSTITUENTID from dbo.REGISTRANT where id=@REGISTRANTID;
        select @EVENTID=EVENTID from REVENUEOFFLINEDONATION where ID=@ID
        if @CURRENTCONSTITUENTID is not null and @EVENTID is not null
        begin
          create table #TEMP_HOUSEHOLD_PEOPLE
            (        
                CLIENTUSERSID int
            );
          insert into #TEMP_HOUSEHOLD_PEOPLE (CLIENTUSERSID)
          select dbo.ufn_CLIENTUSERID_GET_BY_CONSTITUENT(C.ID)
          from dbo.TEAMFUNDRAISINGTEAMMEMBER TM  
             INNER JOIN dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CURRENTCONSTITUENTID, @EVENTID) FNTEAM  
                ON TM.TEAMFUNDRAISINGTEAMID = FNTEAM.TEAMID  
                AND FNTEAM.TEAMTYPECODE = 3 --household  
             INNER JOIN dbo.TEAMFUNDRAISER TF  
                ON TM.TEAMFUNDRAISERID = TF.ID  
             INNER JOIN dbo.CONSTITUENT C  
                ON TF.CONSTITUENTID = C.ID 
             where C.ID<>@CURRENTCONSTITUENTID
          if exists(select * from #TEMP_HOUSEHOLD_PEOPLE)
          begin
            insert into dbo.ADDRESSBOOKFAF 
              (
                  ID,
                  CLIENTUSERSID,
                  CONSTITUENTID,
                  FIRSTNAME,
                  LASTNAME,
                  MIDDLENAME,
                  TITLECODEID,
                  EMAILADDRESS,
                  HOMEPHONE,
                  ADDRESSBLOCK,
                  CITY,
                  STATEID,
                  COUNTRYID,
                  POSTCODE,
                  ADDEDBYID,
                  CHANGEDBYID,
                  DATEADDED,
                  DATECHANGED
              )
            select newid(),
              THP.CLIENTUSERSID,
              @CONSTID,
              @FNAME,
              @LASTNAME,
              @MIDDLENAME,
              @TITLECODEID,
              @EMAILADDRESS,
              @HOMEPHONE,
              @ADDRESSBLOCK,
              @CITY,
              @STATEID,
              @COUNTRYID,
              @POSTCODE,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              getdate(),
              getdate()
            FROM #TEMP_HOUSEHOLD_PEOPLE THP(nolock)  
            WHERE
              THP.CLIENTUSERSID NOT IN
              (
                SELECT TP.CLIENTUSERSID
                FROM #TEMP_HOUSEHOLD_PEOPLE TP(nolock) 
                inner join ADDRESSBOOKFAF AB(nolock) on AB.CLIENTUSERSID = TP.CLIENTUSERSID  
                and AB.CONSTITUENTID = @CONSTID
              )
          end
        end
      END



 --...Get Constituent (Address/Phone/Email) info constituent related tables    

--declare @CONSTID uniqueidentifier    
Select @CONSTID = ConstituentID from dbo.ADDRESSBOOKFAF where ID = @ADDRESSBOOKFAFID    

if @CONSTID is null   -- new person    
  Begin    
      if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..'+'#TEMP_Get_ConstID') )      
       drop table  #TEMP_Get_ConstID    
      ----------------    
      if not exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..'+'#TEMP_Get_ConstID') )      
        begin    
         create table #TEMP_Get_ConstID ( PRIM_ID int IDENTITY(1,1) primary key, CONSTITUENTID uniqueidentifier, FIRSTNAME nvarchar(100), LASTNAME nvarchar(200), MATCHPERCENTAGE decimal)    
         -----     
         insert into  #TEMP_Get_ConstID ( CONSTITUENTID, FIRSTNAME, LASTNAME, MATCHPERCENTAGE)     
         --    
         exec USP_CONSTITUENT_FUZZY_LOOKUP @TRANSACTIONTYPE = 'FAF Donation', @FIRSTNAME = @FNAME, @MIDDLENAME = @MIDDLENAME,  @LASTNAME = @LASTNAME, @EMAILADDRESS_EMAILADDRESS = @EMAILADDRESS,     
                           @ADDRESS_CITY = @CITY, @ADDRESS_POSTCODE = @POSTCODE, @PHONE_NUMBER = @CELLPHONE, @ADDRESS_STATEID= @STATEID     
                         --@ADDRESS_ADDRESSBLOCK= @ADDRESS_ADDRESSBLOCK, @ADDRESS_COUNTRY= @COUNTRYDESC,     
        END    

      --...If Donor does exist in Constituent, then update ADDRESSBOOKFAF table with constituent info, else create constituent record 1st, then update ADDRESSBOOKFAF    
      if  exists ( select CONSTITUENTID from #TEMP_Get_ConstID T, CONSTITUENT C where C.ID= T.CONSTITUENTID)    
         Begin    
          Select @CONSTID = (select top 1 CONSTITUENTID from #TEMP_Get_ConstID T order by T.MATCHPERCENTAGE desc)    
           Update  ADDRESSBOOKFAF Set CONSTITUENTID= @CONSTID where ID = @ADDRESSBOOKFAFID    
         End    
      ELSE    
         begin    
           declare @def_COUNTRYID uniqueidentifier    
           --if @COUNTRYID IS NULL set @def_COUNTRYID= 'CB8084BB-5A77-4E7F-9BA2-D6CD301F6913'    
            if @COUNTRYID IS NULL select @def_COUNTRYID= ID from COUNTRY where ABBREVIATION= 'USA'  ELSE select @def_COUNTRYID= @COUNTRYID    

           exec USP_DATAFORM_ADDNEW_1f9671b3_6740_447c_ad15_ef2718c0e43a @ID=@CONSTID output, @FIRSTNAME=@FNAME, @MIDDLENAME=@MIDDLENAME, @LASTNAME=@LASTNAME, @EMAILADDRESS_EMAILADDRESS=@EMAILADDRESS,    
                                        @ADDRESS_CITY=@CITY, @ADDRESS_POSTCODE=@POSTCODE, @PHONE_NUMBER=@CELLPHONE,  @ADDRESS_STATEID=@STATEID,     
                                        @ADDRESS_COUNTRYID=@def_COUNTRYID, @ADDRESS_ADDRESSBLOCK=@ADDRESSBLOCK,     
                                        @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTAPPUSERID= @CURRENTAPPUSERID    
           select @CONSTID    

           update  ADDRESSBOOKFAF set CONSTITUENTID= @CONSTID  where ID= @ADDRESSBOOKFAFID    
         End            
  End    
--------------------------------------------------------------------------------------------      
        -- handle updating the data
        UPDATE dbo.REVENUEOFFLINEDONATION 
        SET
            REGISTRANTID =        ISNULL(@REGISTRANTID, REGISTRANTID), 
            ADDRESSBOOKFAFID =    ISNULL(@ADDRESSBOOKFAFID, ADDRESSBOOKFAFID), 
            DONORNAME =            ISNULL(@DONORNAME, DONORNAME), 
            CHECKNUMBER =        ISNULL(@CHECKNUMBER, CHECKNUMBER), 
            EXPECTEDDATE = (case when @EXPECTEDDATE IS null then EXPECTEDDATE when @EXPECTEDDATE = CAST('1900-01-01' AS DATETIME) then null else GETDATE() end), 

         --,AMOUNTCONFIRMED =        ISNULL(@AMOUNTCONFIRMED, AMOUNTCONFIRMED)
         --,GIFTID =        ISNULL(@GIFTID, GIFTID),

            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            LOCALCORPNAME = @LOCALCORPNAME
        WHERE ID = @ID

    IF EXISTS (SELECT * FROM dbo.FINANCIALTRANSACTION WHERE ID= @ID)
    UPDATE dbo.FINANCIALTRANSACTION              
      SET TRANSACTIONAMOUNT= ISNULL(@AMOUNT, TRANSACTIONAMOUNT) , 
          BASEAMOUNT = ISNULL(@AMOUNT, BASEAMOUNT),
          ORGAMOUNT = ISNULL(@AMOUNT, ORGAMOUNT),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
      CONSTITUENTID = @CONSTID   
    WHERE ID= @ID

    declare @HASORIGINROW bit = 0;  
    select @HASORIGINROW = 1
    from dbo.CONSTITUENTORIGINATION  
    where ID = @CONSTID   
    if @HASORIGINROW = 0  
    begin  
     update dbo.CONSTITUENTORIGINATION  
     set  ID = @CONSTID      
    where REVENUEID = @ID  AND ID <> @CONSTID 
    end    

      -- link site to constituent  
    Exec dbo.USP_ADDSITETOCONSTITUENT @CONSTID, @EVENTID  

    IF EXISTS (SELECT * FROM dbo.REVENUEPAYMENTMETHOD WHERE REVENUEID= @ID) 
    UPDATE dbo.REVENUEPAYMENTMETHOD 
      SET AMOUNT= ISNULL(@AMOUNT, AMOUNT), PAYMENTMETHODCODE= ISNULL(@PAYMENTTYPECODE, PAYMENTMETHODCODE),      
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
    WHERE REVENUEID= @ID

    IF EXISTS (SELECT * FROM dbo.INSTALLMENT WHERE REVENUEID= @ID) 
    UPDATE dbo.INSTALLMENT 
      SET AMOUNT= ISNULL(@AMOUNT, AMOUNT) ,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
    WHERE REVENUEID= @ID

    UPDATE dbo.INSTALLMENTSPLIT
    SET 
      AMOUNT = @AMOUNT,
      TRANSACTIONAMOUNT = @AMOUNT,
      ORGANIZATIONAMOUNT = @AMOUNT,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
    WHERE 
      PLEDGEID = @ID

    UPDATE dbo.FINANCIALTRANSACTIONLINEITEM
    SET 
      TRANSACTIONAMOUNT= ISNULL(@AMOUNT, TRANSACTIONAMOUNT) , 
      BASEAMOUNT = ISNULL(@AMOUNT, BASEAMOUNT),
      ORGAMOUNT = ISNULL(@AMOUNT, ORGAMOUNT),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
    where
      FINANCIALTRANSACTIONID = @ID


    -- Need to handle NSA preferred corps processing by zipcode or registrant 

    -- select @DESIGNATIONID = DESIGNATIONID from APPEALDESIGNATION where APPEALID = @APPEALID   
    -- select @DESIGNATIONID = SELECT DESIGNATIONID FROM REVENUESPLIT WHERE REVENUEID = @ID
    DECLARE @LOCALCORPCOUNT INT
    DECLARE @ZIPCODE nvarchar(5)
    DECLARE  @DESIGNATIONID uniqueidentifier
    DECLARE @APPEALID uniqueidentifier

    SET @APPEALID= ( SELECT APPEALID from EVENT where ID= @EVENTID    )
    SET @LOCALCORPCOUNT = (SELECT COUNT(*) FROM LOCALCORP) 
    IF @LOCALCORPCOUNT > 0 AND @LOCALCORPNAME <> 'none' 
    BEGIN

        IF @LOCALCORPNAME = 'preferredcorp'
        BEGIN
                SET @DESIGNATIONID = 
                (
                  SELECT  TOP 1  B.DESIGNATIONID
                  FROM dbo.LOCALCORP L(NOLOCK)
                  JOIN dbo.BBNCDESIGNATIONIDMAP B(nolock) on (B.DESIGNATIONID = L.DESIGNATIONID)
                  JOIN dbo.ClientMerchantAccounts M(nolock) on (M.Name = L.MERCHANTACCT)
                  JOIN dbo.REGISTRANTEXTENSION R(nolock) on (R.LOCALCORPID = L.ID)
                  WHERE R.REGISTRANTID = @REGISTRANTID and L.ISACTIVE=1
                  )
        END
        ELSE IF @LOCALCORPNAME = 'zipcode'
        BEGIN
            IF len(@LOCALCORPZIP) > 5
                SET @ZIPCODE = substring(@LOCALCORPZIP, 1, 5);
            ELSE
                SET @ZIPCODE = @LOCALCORPZIP;

            SET @DESIGNATIONID = 
            ( 
                SELECT  TOP 1 B.DESIGNATIONID
                FROM dbo.LOCALCORP L(NOLOCK)
                JOIN dbo.BBNCDESIGNATIONIDMAP B(NOLOCK) ON (B.DESIGNATIONID = L.DESIGNATIONID)
                JOIN dbo.ClientMerchantAccounts M(NOLOCK) ON (M.Name = L.MERCHANTACCT)
                WHERE L.ZIPCODE = @ZIPCODE and L.ISACTIVE=1
            )        
        END

            IF @DESIGNATIONID IS null 
                SET @DESIGNATIONID = ( SELECT DESIGNATIONID FROM LOCALCORP WHERE ISNULL(LOCALCORP,'') = '' AND ISNULL(TERRITORYABBREV,'') = '' AND
                    ISNULL( DIVISIONABBREV , '') = '')


        IF @DESIGNATIONID IS null 
            SET @DESIGNATIONID = ( SELECT DESIGNATIONID FROM LOCALCORP WHERE ISNULL(LOCALCORP,'') = '' AND ISNULL(TERRITORYABBREV,'') = '' AND
                    ISNULL( DIVISIONABBREV , '') = '')

        IF @DESIGNATIONID IS null     
            SET @DESIGNATIONID = ( SELECT DESIGNATIONID FROM APPEALDESIGNATION where APPEALID = @APPEALID and ISDEFAULT = 1 )  
    END    
    ELSE
    BEGIN
        SET @DESIGNATIONID = (SELECT DESIGNATIONID FROM REVENUESPLIT WHERE REVENUEID = @ID   )
    END

/* --obsolete
    IF EXISTS (SELECT * FROM dbo.REVENUESPLIT WHERE REVENUEID= @ID)     
    UPDATE dbo.REVENUESPLIT 
      SET AMOUNT= ISNULL(@AMOUNT, AMOUNT) ,      
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            DESIGNATIONID = ISNULL(@DESIGNATIONID, DESIGNATIONID)
    WHERE REVENUEID= @ID
 */

--  IF EXISTS (SELECT * FROM dbo.REVENUESPLIT WHERE REVENUEID= @ID)   
    UPDATE REVENUESPLIT_EXT 
        SET DESIGNATIONID = ISNULL(@DESIGNATIONID, RSXT.DESIGNATIONID)
    FROM REVENUESPLIT RS 
    JOIN REVENUESPLIT_EXT RSXT ON RS.ID = RSXT.ID
    WHERE RS.REVENUEID= @ID 


    IF EXISTS (SELECT * FROM dbo.REVENUERECOGNITION WHERE ID= @ID)     
    UPDATE dbo.REVENUERECOGNITION 
      SET AMOUNT= ISNULL(@AMOUNT, AMOUNT) ,      
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
    WHERE ID= @ID

    END TRY

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;