USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHCOMMIT
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @VALIDATEONLY bit = 0,
  @CONSTITUENTID uniqueidentifier = null,
  @DONORID uniqueidentifier= null,
  @PLANNEDENDDATE datetime = null,
  @SPONSORSHIPPROGRAMID uniqueidentifier = null,
  @SPONSORSHIPLOCATIONID uniqueidentifier = null,
  @GENDERCODE int = 0,
  @SPROPPAGERANGEID uniqueidentifier = null,
  @ISHIVPOSITIVECODE int = 0,
  @HASCONDITIONCODE int = 0,
  @ISORPHANEDCODE int = 0,
  @SPROPPPROJECTCATEGORYCODEID uniqueidentifier = null,
  @SPONSORSHIPOPPORTUNITYIDCHILD uniqueidentifier = null,
  @SPONSORSHIPOPPORTUNITYIDPROJECT uniqueidentifier = null,
  @SPONSORSHIPOPPORTUNITYTYPECODE tinyint = null,
  @ISSOLESPONSORSHIP bit = 0,
  @STARTDATE date = null,
  @ENDDATE date = null,
  @AMOUNT money = 0,
  @PAYMENTMETHODCODE tinyint = 2,
  @FREQUENCYCODE tinyint = 3,
  @AUTOPAY bit = 1,
  @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,
  @REVENUESCHEDULEENDDATE datetime = null,
  @REVENUESCHEDULESTARTDATE datetime = null,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @ISTRANSIENTCARD bit = null,
  @RESERVEDOPPORTUNITYID uniqueidentifier = null,
  @RESERVATIONKEYID uniqueidentifier = null,
  @SENDREMINDER bit = 0,
  @FINDERNUMBER bigint = 0,
  @SOURCECODE nvarchar(50) = '',
  @APPEALID uniqueidentifier = null,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @REFERENCE nvarchar(255) = null,
  @REVENUECATEGORYCODEID uniqueidentifier = null,
  @ISGIFTSPONSORSHIP bit = 0,
  @EXPIRATIONREASONID uniqueidentifier = null,
  @BID uniqueidentifier = null,
  @PARTIALCREDITCARDNUMBER nvarchar(4) = '',
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @BATCHNUMBER nvarchar(100) = null,
  @COMMITMENTID nvarchar(100) = '',
  @SEPAMANDATEID uniqueidentifier = null,
  @ADDSEPAMANDATE bit = 0,
  @SEPAMANDATECUSTOMIDENTIFIER nvarchar(35) = '',
  @SEPAMANDATESIGNATUREDATE date = null,
  @SEPAMANDATETYPECODE tinyint = 0,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
  @IMPORT bit = 0
)
as
  set nocount on;

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

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()
  declare @BATCHID uniqueidentifier;

  begin try

    if @PAYMENTMETHODCODE = 10 --Other

    begin
      if @OTHERPAYMENTMETHODCODEID is null
        raiserror('Please enter an ''Other method'' for other payment method sponsorship.', 13, 1);
    end

    select 
      @CREDITCARDTOKEN = C.CREDITCARDTOKEN
    from dbo.CREDITCARD C inner join dbo.BATCHSPONSORSHIP BS on BS.CREDITCARDID = C.ID
    where BS.ID = @ID

    select 
      @BATCHID = [BS].[BATCHID]
    from dbo.[BATCHSPONSORSHIP] [BS]
    where [BS].[ID] = @ID;

    if @REVENUESCHEDULESTARTDATE is null
      set @REVENUESCHEDULESTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)

    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0;


    /* Validate all marketing data and dependent fields */
    exec dbo.[USP_REVENUEBATCH_VALIDATEMARKETINGDATA]
      @FINDERNUMBER = @FINDERNUMBER,
      @SOURCECODE = @SOURCECODE,
      @MAILINGID = @MAILINGID,
      @APPEALID = @APPEALID,
      @CONSTITUENTID = @DONORID,
      @BATCHID = @BATCHID;

    --if we have a finder number make sure the constituent has been added to the tables

    if isnull(@FINDERNUMBER, 0) > 0
      exec dbo.USP_DATAFORMTEMPLATE_ADD_ACQUISITIONLISTMEMBER @CONSTITUENTID OUTPUT, @CHANGEAGENTID, @FINDERNUMBER;

    --add new constituent

    if not exists(select ID from dbo.CONSTITUENT where ID=@CONSTITUENTID)
      exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_ADD 
        @CONSTITUENTID OUTPUT, 
        @CHANGEAGENTID, 
        @CONSTITUENTID, 
        @CONSTITUENTACCOUNTID, 
        @CONSTITUENTACCOUNTID output,
        0,
        0;

    --add new donor

    if @DONORID is null
    begin
      set @DONORID= @CONSTITUENTID
      set @ISGIFTSPONSORSHIP = 0
    end
    else
    begin
      set @ISGIFTSPONSORSHIP = 1
      if not exists(select ID from dbo.CONSTITUENT where ID=@DONORID)
        exec dbo.USP_SPONSORSHIPBATCH_CONSTITUENT_ADD 
          @DONORID OUTPUT, 
          @CHANGEAGENTID, 
          @DONORID, 
          @CONSTITUENTACCOUNTID, 
          @CONSTITUENTACCOUNTID output,
          0,
          0;
    end


    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier;
    declare @MATCHEDOPPORTUNITYID uniqueidentifier;

    select @SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITYID from dbo.BATCHSPONSORSHIP where ID=@BID

    if @SPONSORSHIPOPPORTUNITYID is not null
    begin  
      if exists(select ID from dbo.SPONSORSHIPOPPORTUNITYCHILD where ID= @SPONSORSHIPOPPORTUNITYID)
        if @RESERVEDOPPORTUNITYID is null    
          begin
            set @SPONSORSHIPOPPORTUNITYIDCHILD = @SPONSORSHIPOPPORTUNITYID
          end
      else
        set @SPONSORSHIPOPPORTUNITYIDPROJECT = @SPONSORSHIPOPPORTUNITYID
    end
    else
    begin
      declare @TEMP table(SPONSORSHIPOPPORTUNITY uniqueidentifier,NAME nvarchar(255), OFFERSOLESPONSORSHIP bit,
                          GENDER nvarchar(7),BIRTHDATE nvarchar(25), LOOKUPID nvarchar(36),
                          LOCATION nvarchar(max), CATEGORY nvarchar(100))
      insert into @TEMP
        exec dbo.USP_DATALIST_SPONSORSHIPOPPORTUNITYMATCH
          @CHANGEAGENTID,
          @SPONSORSHIPPROGRAMID,
          @SPONSORSHIPLOCATIONID,
          @GENDERCODE,
          @SPROPPAGERANGEID,
          @ISHIVPOSITIVECODE,
          @HASCONDITIONCODE,
          @ISORPHANEDCODE,
          @SPROPPPROJECTCATEGORYCODEID,
          @CONSTITUENTID,
          @ISSOLESPONSORSHIP,
          @DONORID

      select top 1 @SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY from @TEMP

      if @SPONSORSHIPOPPORTUNITYID is null
        raiserror ('No matching opportunity found', 13,1);

      set @MATCHEDOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYID;
      set @SPONSORSHIPOPPORTUNITYIDCHILD = null;
      set @SPONSORSHIPOPPORTUNITYIDPROJECT = null;

    end

    if @AMOUNT is null
      set @AMOUNT =0

    -- Multicurrency setup


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

    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();


    if @BASECURRENCYID is null
    begin
      set @BASECURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)
    end

    if @TRANSACTIONCURRENCYID is null
      set @TRANSACTIONCURRENCYID = @BASECURRENCYID;

    if @EXCHANGERATE is null
      set @EXCHANGERATE = 0;

    -- Create the base exchange rate when "Spot rate" is selected.


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

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

    declare @CREDITCARDPARTIALNUMBER nvarchar(4) = ''
    set @CREDITCARDPARTIALNUMBER = case when @PAYMENTMETHODCODE = 98 then @PARTIALCREDITCARDNUMBER else @CREDITCARDNUMBER end;

    exec dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORSHIP
      @ID,
      @CHANGEAGENTID,
      @DONORID,
      @CONSTITUENTID,
      @SPONSORSHIPPROGRAMID,
      @SPONSORSHIPLOCATIONID,
      @GENDERCODE,
      @SPROPPAGERANGEID,
      @ISHIVPOSITIVECODE,
      @HASCONDITIONCODE,
      @ISORPHANEDCODE,
      @SPROPPPROJECTCATEGORYCODEID,
      @SPONSORSHIPOPPORTUNITYIDCHILD,
      @SPONSORSHIPOPPORTUNITYIDPROJECT,
      @ISSOLESPONSORSHIP,
      @STARTDATE,
      @AMOUNT,
      @PAYMENTMETHODCODE,
      @REFERENCEDATE,
      @REFERENCENUMBER,
      @CARDHOLDERNAME,
      @CREDITCARDPARTIALNUMBER,
      @CREDITTYPECODEID,
      @EXPIRESON,
      @CONSTITUENTACCOUNTID,
      @FREQUENCYCODE,
      @REVENUESCHEDULEENDDATE,
      @REVENUESCHEDULESTARTDATE,
      @AUTOPAY,
      @CREDITCARDTOKEN,
      @ISGIFTSPONSORSHIP,
      @PLANNEDENDDATE,
      @RESERVEDOPPORTUNITYID,
      @RESERVATIONKEYID,
      @MATCHEDOPPORTUNITYID,
      @SENDREMINDER,
      @FINDERNUMBER,
      @SOURCECODE,
      @APPEALID,
      @MAILINGID,
      @CHANNELCODEID,
      @REFERENCE,
      @REVENUECATEGORYCODEID,
      @EXPIRATIONREASONID,
      @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID,
      @EXCHANGERATE,
      @CURRENTAPPUSERID,
      @BATCHNUMBER,
      default,
      @SEPAMANDATEID,
      0, --@CARRYFORWARDPASTDUE

      null, --@CARRYFORWARDPASTDUEAMOUNT

      @OTHERPAYMENTMETHODCODEID


    if @COMMITMENTID <> ''
      update dbo.SPONSORSHIPCOMMITMENT
      set CUSTOMIDENTIFIER = @COMMITMENTID
      from dbo.SPONSORSHIPCOMMITMENT SC
      inner join SPONSORSHIP S on S.SPONSORSHIPCOMMITMENTID = SC.ID
      where @ID = S.ID

  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;