USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFTFROMCONSTITUENT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFTFROMCONSTITUENT
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @DATE datetime,
  @AMOUNT money = 0,
  @PAYMENTMETHODCODE tinyint = 2,
  @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @REFERENCENUMBER nvarchar(20) = '',
  @CARDHOLDERNAME nvarchar(255) = '',
  @CREDITCARDNUMBER nvarchar(20) = '',
  @CREDITTYPECODEID uniqueidentifier = null,
  @EXPIRESON dbo.UDT_FUZZYDATE = null,
  @CONSTITUENTACCOUNTID uniqueidentifier = null,
  @SPLITS xml,
  @FREQUENCYCODE tinyint = 3,
  @ENDDATE datetime = null,
  @STARTDATE datetime = null,
  @FINDERNUMBER bigint = null,
  @SOURCECODE nvarchar(50) = null,
  @APPEALID uniqueidentifier = null,
  @GIVENANONYMOUSLY bit = 0,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @DONOTACKNOWLEDGE bit = 0,
  @AUTOPAY bit = 1,
  @REFERENCE nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @STANDINGORDERSETUP bit = 0,
  @STANDINGORDERSETUPDATE datetime = null,
  @DDISOURCECODEID uniqueidentifier = null,
  @DDISOURCEDATE date = null,
  @SENDREMINDER bit = 1,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @CONTEXTID uniqueidentifier
)
as
  set nocount on;

  declare @CURRENTDATE datetime
  declare @NEXTTRANSACTIONDATE datetime
  declare @TRANSACTIONTYPECODE tinyint
  declare @PAYMENTMETHODID uniqueidentifier;

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

  set @TRANSACTIONTYPECODE = 2; --Recurring Gift


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

  set @CURRENTDATE = getdate();

  --Multicurrency - RSC 4/14/10 - Use the appuser's base currency since there is no account system and we do not post recurring gifts.

  declare @BASECURRENCYID uniqueidentifier = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

  --Multicurrency - RSC 4/14/10 - Add a spot exchange rate if an existing rate hasn't

  --  been selected, the base and transaction currencies are different, and the rate

  --  entered isn't zero (which indicates that the user wants to enter the record without a rate).

  if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
  begin
    set @BASEEXCHANGERATEID = newid()

    --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

    /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
      and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
    begin
      raiserror('User does not have the right to add a new spot rate.', 13, 1);
      return 1;
    end*/

    insert into dbo.CURRENCYEXCHANGERATE(
      ID, 
      FROMCURRENCYID,
      TOCURRENCYID,
      RATE,
      ASOFDATE,
      TYPECODE,
      SOURCECODEID,
      ADDEDBYID, 
      CHANGEDBYID, 
      DATEADDED, 
      DATECHANGED
    )
    values(
      @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID,
      @BASECURRENCYID,
      @EXCHANGERATE,
      @DATE,
      2,
      null,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );
  end

  declare @SUM money 

  begin try
    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0;
    else if @FINDERNUMBER <> 0
      begin
        if dbo.[UFN_MKTFINDERNUMBER_VALIDATE_MOD10](@FINDERNUMBER) = 0
          raiserror('BBERR_FINDERNUMBER_FAILEDCHECKDIGIT', 13, 1);

        if dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER) is null
          raiserror('BBERR_FINDERNUMBER_INVALID', 13, 1);
      end

    if @AMOUNT < 0 
      raiserror('The amount cannot be negative.', 13, 1);

    if @STARTDATE < @DATE
      raiserror('The schedule cannot start before the gift date.', 13, 1);

    -- If the system has set that households can't be donors, verify that constituent isn't a household

    if dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS() = 0 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
      raiserror('HOUSEHOLDSCANNOTBEDONORS', 13, 1);

    -- if the group type can't be a donor, raise an error

    if exists(select GD.ID from dbo.GROUPDATA GD inner join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID where GD.ID = @CONSTITUENTID and GT.CANBEDONOR = 0)
      raiserror('GROUPCANNOTBEDONOR', 13, 1);

    exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, @TRANSACTIONTYPECODE, default, @TRANSACTIONCURRENCYID;

    if @AUTOPAY = 0
      set @PAYMENTMETHODCODE = 9;

    --Multicurrency - SlyyMu 8/12/10 replaced the previous multicurrency section done by (RSC 4/14/10) with the UFN_CURRENCY_GETCURRENCYVALUES()

    declare @BASEAMOUNT money;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @ORGANIZATIONAMOUNT money;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

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

    insert into dbo.REVENUE ([ID],[CONSTITUENTID],[DATE],[POSTDATE],[DONOTRECEIPT], [AMOUNT], [TRANSACTIONTYPECODE], [RECEIPTAMOUNT], [FINDERNUMBER], [SOURCECODE], [APPEALID], [GIVENANONYMOUSLY], [MAILINGID], [CHANNELCODEID], [DONOTACKNOWLEDGE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED],[BASECURRENCYID],[ORGANIZATIONAMOUNT],[ORGANIZATIONEXCHANGERATEID],[TRANSACTIONAMOUNT],[TRANSACTIONCURRENCYID],[BASEEXCHANGERATEID])
      values (@ID,@CONSTITUENTID,@DATE,@DATE, 0, @BASEAMOUNT, @TRANSACTIONTYPECODE, @AMOUNT, @FINDERNUMBER, @SOURCECODE, @APPEALID, @GIVENANONYMOUSLY, @MAILINGID, @CHANNELCODEID, @DONOTACKNOWLEDGE, @CHANGEAGENTID, @CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);

    --Add origination source

    exec dbo.USP_REVENUE_ADDORIGIN @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

    set @PAYMENTMETHODID = newid();
    insert into dbo.REVENUEPAYMENTMETHOD (ID, REVENUEID, AMOUNT, PAYMENTMETHODCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      values (@PAYMENTMETHODID, @ID, @BASEAMOUNT, @PAYMENTMETHODCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE ,@CURRENTDATE);

    exec dbo.USP_REVENUEREFERENCE_ADD @ID, @REFERENCE, @CHANGEAGENTID;

    --if semi-monthly ensure nexttransaction date starts on 1st or 15th

    if @FREQUENCYCODE = 7 and not (day(@STARTDATE) = 1 or day(@STARTDATE) = 15)
    begin
      --Use the old version of UFN_REVENUE_GETNEXTTRANSACTIONDATE for this purpose because it doesn't take a revenue ID and does exactly what is needed.

      set @NEXTTRANSACTIONDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE(@FREQUENCYCODE, @STARTDATE)
      set @STARTDATE = @NEXTTRANSACTIONDATE;
    end
    else
      set @NEXTTRANSACTIONDATE = @STARTDATE

    declare @CREDITCARDID uniqueidentifier
    if @PAYMENTMETHODCODE = 2 -- Credit Card

    begin
      exec dbo.USP_CREDITCARD_SAVE
          @ID = @CREDITCARDID output,
          @CREDITCARDTOKEN = @CREDITCARDTOKEN,
          @CARDHOLDERNAME = @CARDHOLDERNAME,
          @CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
          @CREDITTYPECODEID = @CREDITTYPECODEID,
          @EXPIRESON = @EXPIRESON,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @CURRENTDATE = @CURRENTDATE
    end

    insert into dbo.REVENUESCHEDULE ([ID], [STARTDATE], [SCHEDULESEEDDATE], [ENDDATE], [FREQUENCYCODE], [NEXTTRANSACTIONDATE], CREDITCARDID, SENDPLEDGEREMINDER, [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
      values (@ID, @STARTDATE, @STARTDATE, @ENDDATE, @FREQUENCYCODE, @NEXTTRANSACTIONDATE, @CREDITCARDID, @SENDREMINDER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    -- add first installment

    insert into dbo.RECURRINGGIFTINSTALLMENT (ID, REVENUEID, AMOUNT, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
    values (newid(), @ID, @BASEAMOUNT, @NEXTTRANSACTIONDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);

    -- add any additional installments to get us current

    exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS
      @ID = @ID,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CREATIONDATE = @CURRENTDATE,
      @AMOUNT = @BASEAMOUNT,
      @BASECURRENCYID = @BASECURRENCYID,
      @ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
      @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
      @TRANSACTIONAMOUNT = @AMOUNT,
      @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID = @BASEEXCHANGERATEID;

    if @PAYMENTMETHODCODE = 3 --Direct debit

    begin
      if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 0
      begin
        set @DDISOURCECODEID = null;
        set @DDISOURCEDATE = null;
      end

      declare @SENDNEWINSTRUCTION bit;
      declare @NEWINSTRUCTIONTOSEND tinyint;

      set @SENDNEWINSTRUCTION = 0;
      set @NEWINSTRUCTIONTOSEND = 0;

      if not @DDISOURCECODEID is null
      begin
        set @SENDNEWINSTRUCTION = 1;
        set @NEWINSTRUCTIONTOSEND = 1;
      end

      insert into dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
        (ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, DDISOURCECODEID, DDISOURCEDATE, SENDPMINSTRUCTION, PMINSTRUCTIONTOSENDCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (@ID, @REFERENCEDATE, @REFERENCENUMBER, @CONSTITUENTACCOUNTID, @DDISOURCECODEID, @DDISOURCEDATE, @SENDNEWINSTRUCTION, @NEWINSTRUCTIONTOSEND, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    end

    if @PAYMENTMETHODCODE = 11 --Standing order

      begin
        if @REFERENCEDATE is null or @REFERENCEDATE = '' or @REFERENCEDATE = '00000000'
          select @REFERENCEDATE = convert(nvarchar,DATEPART(yyyy,@CURRENTDATE)) + right('00' + convert(nvarchar,DATEPART(mm,@CURRENTDATE)),2) + right('00' + convert(nvarchar,DATEPART(dd,@CURRENTDATE)),2);

        if @STANDINGORDERSETUP = 0
          set @STANDINGORDERSETUPDATE = null

        insert into dbo.REVENUESCHEDULESTANDINGORDERPAYMENT
          (ID, REFERENCEDATE, CONSTITUENTACCOUNTID, STANDINGORDERSETUP, STANDINGORDERSETUPDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (@ID, @REFERENCEDATE, @CONSTITUENTACCOUNTID, @STANDINGORDERSETUP, @STANDINGORDERSETUPDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        insert into dbo.REVENUESTANDINGORDER(ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values(@ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
      end

    -- Set the IDs for any blank entries in the splits XML so that it can associate the declines with gift aid

    -- flag with the generated splits

    set @SPLITS = (select 
                     case when [ID] is null or ID = '00000000-0000-0000-0000-000000000000' then newid() else [ID] end [ID],
                     [AMOUNT],
                     [APPLICATIONCODE],
                     [DESIGNATIONID],
                     [TYPECODE],
                     [DECLINESGIFTAID]
                   from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)
                   for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64)

 --Multicurrency - RSC 4/14/10 - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

    set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
    exec dbo.USP_REVENUE_GETSPLITS_2_ADDFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;

    exec dbo.USP_REVENUE_CREATERECOGNITIONS @ID, @CHANGEAGENTID, @CURRENTDATE;

    exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @REVENUEID = @ID, 
      @CATEGORYCODEID = @CATEGORYCODEID, 
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CURRENTDATE = @CURRENTDATE

    exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID, 
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CHANGEDATE = @CURRENTDATE

    declare @SPLITSDECLININGGIFTAID xml
    set @SPLITSDECLININGGIFTAID = (select
                                     ID as REVENUESPLITID
                                   from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)
                                   where DECLINESGIFTAID = 1
                                   for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64)

    exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, @TRANSACTIONTYPECODE, @SPLITSDECLININGGIFTAID;
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;

    close symmetric key sym_BBInfinity;
    return 1;
  end catch

  return 0;