USP_DATAFORMTEMPLATE_ADD_REVENUEOFFLINEDONATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEOFFLINEDONATION
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @REGISTRANTID uniqueidentifier = null,
    @ADDRESSBOOKFAFID uniqueidentifier = null,
    @DONORNAME nvarchar(255)= '',
    @AMOUNT money = 0,         
    @PAYMENTTYPECODE tinyint = 0,
    @CHECKNUMBER nvarchar(20)= '',
    @EXPECTEDDATE datetime= '',

    @FIRSTNAME    nvarchar(50)= '',
    @KEYNAME    nvarchar(50)= ''

 ,@CURRENTAPPUSERID uniqueidentifier = null
 ,@LOCALCORPNAME nvarchar(20)    = 'none' -- 'none', 1= 'preferredcorp',  2 = 'zipcode'

 ,@LOCALCORPZIP nvarchar(20) = ''
 ,@EVENTID  uniqueidentifier = null
 ,@CLIENTUSERSID int = 0
 --,@AMOUNTCONFIRMED money = 0         

 --,@GifTID    int = 0

)        
as

set nocount on;

declare @CURRENTDATE date,
        @STARTDATE datetime

declare @REVENUESPLITID uniqueidentifier, 
        @REGCONSTITUENTID uniqueidentifier,
        @DESIGNATIONID uniqueidentifier,
        @BASECURRENCYID uniqueidentifier

declare @APPEALID uniqueidentifier

declare @CURRENTCONSTITUENTID uniqueidentifier,
        @TITLECODEID uniqueidentifier

if @ID is null
    set @ID = newid()

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

set @CURRENTDATE = getdate()

if @BASECURRENCYID is null
  set @BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();


-- handle inserting the data

begin TRY

--1...If Address Book exist, fetch info from it (otherwise add info there- maybe later)

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

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
    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     
END
--------------------------------------------------------------------------------------------            


--2...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

      --3...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
--------------------------------------------------------------------------------------------            



-----     select     @CONSTID= CONSTITUENTID    from  ADDRESSBOOKFAF    where ID= @ADDRESSBOOKFAFID                    

--------------------------------------------------------------------------------------------            


  declare @BASEEXCHANGERATEID uniqueidentifier,
          @TRANSACTIONCURRENCYID uniqueidentifier,
          @BASEAMOUNT money,
          @ORGANIZATIONCURRENCYID uniqueidentifier,
          @ORGANIZATIONAMOUNT money,
          @ORGANIZATIONEXCHANGERATEID uniqueidentifier

  exec dbo.USP_CURRENCY_GETCURRENCYvalues @AMOUNT, @CURRENTDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;

    --declare @REVENUEOFFLINEDONATIONID uniqueidentifier  


    --select @EVENTID= R.EVENTID from REGISTRANT R JOIN EVENTEXTENSION EX ON R.EVENTID= EX.EVENTID where R.ID= @REGISTRANTID

    --select @CLIENTUSERSID= CLIENTUSERSID from ADDRESSBOOKFAF where ID=  @ADDRESSBOOKFAFID

    --select @REVENUEOFFLINEDONATIONID= NewID()    


  Select @APPEALID= APPEALID from EVENT where ID= @EVENTID    

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


    -- select @DESIGNATIONID = DESIGNATIONID from APPEALDESIGNATION where APPEALID = @APPEALID   

    DECLARE @LOCALCORPCOUNT INT
    DECLARE @ZIPCODE nvarchar(5)

    SET @LOCALCORPCOUNT = (SELECT COUNT(*) FROM LOCALCORP) 
  -- SHL BBIS Bug 319197; If the offline gift is not specifying a local corp when it is an option (as indicated by 'preferredcorp') 

  -- then it gets the same designation as as a normal online donation (the event's designation)


  -- UPDATE: BBIS Bug 399926; Handled 'preferredcorp' condition in 'else if' block. 

    IF @LOCALCORPCOUNT > 0 AND @LOCALCORPNAME <> 'none' AND @LOCALCORPNAME <> 'preferredcorp'
    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
            )        

      -- SET DESIGNATIONID IF IT IS NOT FOUND.

        IF @DESIGNATIONID IS null 
              SET @DESIGNATIONID = ( SELECT DESIGNATIONID FROM LOCALCORP WHERE ISNULL(LOCALCORP,'') = '' AND ISNULL(TERRITORYABBREV,'') = '' AND
                ISNULL( DIVISIONABBREV , '') = '')
    END
  ELSE 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
         )

     -- SET DESIGNATIONID IF IT IS NOT FOUND.

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

        IF @DESIGNATIONID IS null     
            SET @DESIGNATIONID = ( SELECT DESIGNATIONID FROM APPEALDESIGNATION where APPEALID = @APPEALID and ISDEFAULT = 1 ) 


--4...Create Offline Donation 

if not exists (select ID from REVENUE R where R.ID= @ID)
begin    
  declare @PDACCOUNTSYSTEMID uniqueidentifier = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B',
          @INSTALLMENTID uniqueidentifier;
  set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  /*insert into dbo.REVENUE 
        (ID, CONSTITUENTID, DATE, DOnotPOST, POSTDATE, DOnotRECEIPT, AMOUNT, TRANSACTIONTYPECODE, RECEIPTAMOUNT, MAILINGID, APPEALID, SOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)  
    values                        
        (@ID, @CONSTID, @CURRENTDATE, 1, @CURRENTDATE, 1, @AMOUNT, 9, 0, NULL, @APPEALID, '', @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
  */
  insert into dbo.FINANCIALTRANSACTION
    (ID, CONSTITUENTID, DATE, POSTDATE, POSTSTATUSCODE, TRANSACTIONAMOUNT, TYPECODE, TRANSACTIONCURRENCYID, BASEAMOUNT, ORGAMOUNT, PDACCOUNTSYSTEMID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) 
  values
   (@ID, @CONSTID, @CURRENTDATE, @CURRENTDATE, 3,  @AMOUNT, 9, @ORGANIZATIONCURRENCYID, @AMOUNT, @AMOUNT, @PDACCOUNTSYSTEMID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

  insert into dbo.REVENUE_EXT
    (ID, DONOTRECEIPT, RECEIPTAMOUNT, APPEALID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  values
   (@ID, 1, 0, @APPEALID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    --Add origination source  

    exec dbo.USP_REVENUE_ADDORIGIN @ID, @CONSTID, @CHANGEAGENTID, @CURRENTDATE

    insert into dbo.REVENUEPAYMENTMETHOD 
        (REVENUEID, AMOUNT, PAYMENTMETHODCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)  
    values 
        (@ID, @AMOUNT, @PAYMENTTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE ,@CURRENTDATE)  ;

    insert into dbo.REVENUEOFFLINEDONATION
    --Dave added back the paymenttypecode as it is required by the table

        (ID, CLIENTUSERSID, EVENTID, REGISTRANTID, ADDRESSBOOKFAFID, DONORNAME, CHECKNUMBER, EXPECTEDDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PAYMENTTYPECODE, LOCALCORPNAME)               --AMOUNTCONFIRMED, GifTID,

    values
        (@ID, @CLIENTUSERSID, @EVENTID, @REGISTRANTID, @ADDRESSBOOKFAFID, @DONORNAME, @CHECKNUMBER, @EXPECTEDDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @PAYMENTTYPECODE, @LOCALCORPNAME); --@AMOUNTCONFIRMED, @GifTID, @LOCALCORPNAME


    set @REVENUESPLITID = NewID()

  select @STARTDATE = [DATE] from dbo.REVENUE where ID = @ID

    insert into dbo.REVENUESCHEDULE 
        (ID, STARTDATE, FREQUENCYCODE, NUMBEROFINSTALLMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values 
        (@ID, @STARTDATE, 5, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

   set @INSTALLMENTID = newid();
   insert into dbo.INSTALLMENT 
    (ID, REVENUEID, AMOUNT, DATE, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
     values 
    (@INSTALLMENTID, @ID, @AMOUNT, @CURRENTDATE, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);

  insert into dbo.FINANCIALTRANSACTIONLINEITEM
  (ID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, BASEAMOUNT, ORGAMOUNT, POSTDATE, POSTSTATUSCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  values
  (@REVENUESPLITID, @ID, @AMOUNT, @AMOUNT, @AMOUNT, @CURRENTDATE, 3, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

  insert into dbo.REVENUESPLIT_EXT
  (ID, DESIGNATIONID, TYPECODE, APPLICATIONCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  values
  (@REVENUESPLITID, @DESIGNATIONID, 0, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

  --exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;

  insert into dbo.INSTALLMENTSPLIT( INSTALLMENTID, PLEDGEID, DESIGNATIONID, AMOUNT,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID, ORGANIZATIONAMOUNT,
                            ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID)
    select @INSTALLMENTID, @ID, @DESIGNATIONID, @AMOUNT,
                 @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @BASECURRENCYID, FTLI.ORGAMOUNT,
                            FT.ORGEXCHANGERATEID, @AMOUNT, FT.TRANSACTIONCURRENCYID,
                            FT.BASEEXCHANGERATEID
   from dbo.FINANCIALTRANSACTION FT
   left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
   where FT.ID = @ID

  select @REGCONSTITUENTID= CONSTITUENTID from dbo.REGISTRANT where ID=@REGISTRANTID

  insert into REVENUERECOGNITION 
      (ID, REVENUESPLITID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID, ORGANIZATIONAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  values                        
    (NewID(), @REVENUESPLITID, @REGCONSTITUENTID, @AMOUNT, @CURRENTDATE, NULL, @AMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

  -- link site to constituent

  Exec dbo.USP_ADDSITETOCONSTITUENT @CONSTID, @EVENTID

end

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0