USP_DONATIONBATCH_ADD_EDIT

CREATE procedure dbo.USP_DONATIONBATCH_ADD_EDIT
(
    @ID uniqueidentifier = null output,
    @BATCHID uniqueidentifier = null,
    @SEQUENCE int = null,
    @EVENTID uniqueidentifier = null,
    @USERID nvarchar(100) = '',
    @PASSWORD nvarchar(500) = '',
    @LASTNAME nvarchar(100) = '',
    @FIRSTNAME nvarchar(100) = '',
    @MIDDLENAME nvarchar(100) = '',
    @TITLECODEID uniqueidentifier = null,
    @SUFFIXCODEID uniqueidentifier = null,
    @NICKNAME nvarchar(100) = '',
    @MAIDENNAME nvarchar(200) = '',
    @BIRTHDATE UDT_FUZZYDATE = '00000000',
    @GENDERCODE int = 0,
    @MARITALSTATUSCODEID uniqueidentifier = null,
    @ADDRESSTYPECODEID uniqueidentifier = null,
    @COUNTRYID uniqueidentifier = null,
    @ADDRESS nvarchar(300) = '',
    @CITY nvarchar(100) = '',
    @STATEID uniqueidentifier = null,
    @POSTCODE nvarchar(24) = '',
    @DONOTMAIL bit = 0,
    @DONOTMAILREASONCODEID uniqueidentifier = null,
    @PHONETYPECODEID uniqueidentifier = null,
    @PHONENUMBER nvarchar(200) = '',
    @EMAILADDRESS UDT_EMAILADDRESS = '',
    @ISCOMPANYGIFT bit = 0,
    @COMPANYNAME nvarchar(200) = '',
    @COMPANYURL UDT_WEBADDRESS = null,
    @COMPANY_ADDRESSTYPECODEID uniqueidentifier = null,
    @COMPANY_COUNTRYID uniqueidentifier = null,
    @COMPANY_ADDRESS nvarchar(300) = '',
    @COMPANY_CITY nvarchar(100) = '',
    @COMPANY_STATEID uniqueidentifier = null,
    @COMPANY_POSTCODE nvarchar(24) = '',
    @COMPANY_DONOTMAIL bit = 0,
    @COMPANY_DONOTMAILREASONCODEID uniqueidentifier = null,
    @COMPANY_PHONETYPECODEID uniqueidentifier = null,
    @COMPANY_PHONENUMBER nvarchar(200) = '',
    @COMPANY_EMAILADDRESS UDT_EMAILADDRESS = null,
    @GIFTTYPE nvarchar(100) = '',
    @GIFTAMOUNT money = 0,
    @NUMPAYMENTS int = 0,
    @FREQUENCY nvarchar(200) = '',
    @AUTOINSTALLMENTS bit = 0,
    @ANONYMOUS bit = 0,
    @STARTDATE UDT_FUZZYDATE = '00000000',
    @INFINITEGIFT bit = 0,
    @PAYMENTMETHODCODE int = 0,
    @CREDITCARDHOLDER nvarchar(200) = '',
    @CREDITCARDNUMBER nvarchar(200) = '',
    @CREDITCARDTYPE uniqueidentifier = null,
    @EXPIRATIONDATE UDT_FUZZYDATE = '00000000',
  @CREDITCARDTOKEN uniqueidentifier = null,
  @CHECKDATE UDT_FUZZYDATE = '00000000',
  @CHECKNUMBER nvarchar(40) = '',
    @BANKACCOUNTTYPECODE int = 0,
    @BANKACCOUNTHOLDER nvarchar(200) = '',
    @BANKACCOUNTNUMBER nvarchar(200) = '',
    @BANKACCOUNTROUTINGCODE nvarchar(200) = '',
    @SAMEASPRIMARYADDRESS bit = 0,
    @BILLING_ADDRESSTYPECODEID uniqueidentifier = null,
    @BILLING_COUNTRYID uniqueidentifier = null,
    @BILLING_ADDRESSBLOCK nvarchar(300) = '',
    @BILLING_CITY nvarchar(100) = '',
    @BILLING_STATEID uniqueidentifier = null,
    @BILLING_POSTCODE nvarchar(24) = '',
  @SAVETRIBUTEEASCONSTITUENT bit = 0,
    @SHOWTRIBUTE bit = 0,
    @TRIBUTEFIRSTNAME nvarchar(400) = '',
    @TRIBUTELASTNAME nvarchar(400) = '',
    @TRIBUTEEMAIL UDT_EMAILADDRESS = null,
    @TRIBUTEADDRESS nvarchar(600) = '',
    @TRIBUTECITY nvarchar(200) = '',
    @TRIBUTEPOSTCODE nvarchar(48) = '',
    @TRIBUTESTATEID uniqueidentifier = null,
    @TRIBUTETYPECODEID uniqueidentifier = null,
    @TRIBUTETEXT nvarchar(500) = '',
    @TRIBUTEANONYMOUS bit = 0,
    @ACKNOWLEDGEFIRSTNAME nvarchar(400) = '',
    @ACKNOWLEDGELASTNAME nvarchar(400) = '',
    @ACKNOWLEDGEEMAIL UDT_EMAILADDRESS = null,
    @MESSAGETOACKNOWLEDGE nvarchar(2000) = '',
    @RECOGNITIONCREDIT nvarchar(100) = '',
    @MATCHINGCOMPANYNAME nvarchar(400) = '',
    @MATCHINGAMOUNT money = 0,
    @RECIPIENTGROUPID uniqueidentifier = null,
  @RECIPIENTREGISTRANTID uniqueidentifier = null,
    @RECIPIENTROLE nvarchar(200) = '',
    @SENDTOACKNOWLEDGE bit = 0,

  @ORIGINALCLIENTUSERID integer = null,
  @ORIGINALCONSTITUENTID uniqueidentifier = null,
  @PHONEID uniqueidentifier = null,
  @ADDRESSID uniqueidentifier = null,
  @EMAILADDRESSID uniqueidentifier = null,

  @ORGANIZATIONCONSTITUENTID uniqueidentifier = null,

  @CHANGEAGENTID uniqueidentifier = null,
  @AUTHORIZATIONCODE nvarchar(20) = '',
  @TRANSACTIONID uniqueidentifier = null,
  @REJECTIONMESSAGE nvarchar(250) = '',

  @SENDNOTIFICATIONS bit = 1,
  @VENDORID nvarchar(50) = ''
)
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()

    if @PAYMENTMETHODCODE is null
    set @PAYMENTMETHODCODE = '0'

  if @BANKACCOUNTTYPECODE is null
    set @BANKACCOUNTTYPECODE = '0'

  if @ORIGINALCLIENTUSERID = 0
    set @ORIGINALCLIENTUSERID = null

  if @ORIGINALCONSTITUENTID = '00000000-0000-0000-0000-000000000000'
    set @ORIGINALCONSTITUENTID = null

  if @PHONEID = '00000000-0000-0000-0000-000000000000'
    set @PHONEID = null

  if @ADDRESSID = '00000000-0000-0000-0000-000000000000'
    set @ADDRESSID = null

  if @EMAILADDRESSID = '00000000-0000-0000-0000-000000000000'
    set @EMAILADDRESSID = null

  if @ORGANIZATIONCONSTITUENTID = '00000000-0000-0000-0000-000000000000'
    set @ORGANIZATIONCONSTITUENTID = null

  if @PAYMENTMETHODCODE = 2 
  begin
    if coalesce(@CREDITCARDHOLDER, '') = ''
      begin
          raiserror('CREDITCARDHOLDERREQUIRED', 13, 1)
          return 1
      end 
    else if coalesce(@CREDITCARDNUMBER, '') = ''
      begin
          raiserror('CREDITCARDNUMBERREQUIRED', 13, 1)
          return 1
      end
    else if @CREDITCARDTYPE IS NULL
      begin
          raiserror('CREDITCARDTYPEREQUIRED', 13, 1)
          return 1
      end
    else if @EXPIRATIONDATE = '00000000'
      begin
          raiserror('EXPIRATIONDATEREQUIRED', 13, 1)
          return 1
      end
    else if convert(date, @EXPIRATIONDATE, 112) < convert(date, getdate())
      begin
          raiserror('INVALIDEXPIRATIONDATE', 13, 1)
          return 1
      end 
  end

  begin try

  declare @CREDITCARDID uniqueidentifier
  if @PAYMENTMETHODCODE = 2
  begin
    exec dbo.USP_CREDITCARD_SAVE
      @ID = @CREDITCARDID output,
      @CREDITCARDTOKEN = @CREDITCARDTOKEN,
      @CARDHOLDERNAME = @CREDITCARDHOLDER,
      @CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
      @CREDITTYPECODEID = null,
      @EXPIRESON = @EXPIRATIONDATE,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CURRENTDATE = @CURRENTDATE
  end

  IF NOT EXISTS (SELECT * FROM dbo.DONATIONBATCH (NOLOCK) WHERE ID= @ID)
  begin
      INSERT INTO dbo.DONATIONBATCH
          (ID,BATCHID,SEQUENCE,EVENTID,USERID,PASSWORD,LASTNAME,FIRSTNAME,MIDDLENAME,TITLECODEID,SUFFIXCODEID,NICKNAME,MAIDENNAME,
          BIRTHDATE,GENDERCODE,MARITALSTATUSCODEID,ADDRESSTYPECODEID,COUNTRYID,ADDRESS,CITY,STATEID,POSTCODE,DONOTMAIL,
          DONOTMAILREASONCODEID,PHONETYPECODEID,PHONENUMBER,EMAILADDRESS,ISCOMPANYGIFT,COMPANYNAME,COMPANYURL,COMPANY_ADDRESSTYPECODEID,
          COMPANY_COUNTRYID,COMPANY_ADDRESS,COMPANY_CITY,COMPANY_STATEID,COMPANY_POSTCODE,COMPANY_DONOTMAIL,COMPANY_DONOTMAILREASONCODEID,
          COMPANY_PHONETYPECODEID,COMPANY_PHONENUMBER,COMPANY_EMAILADDRESS,GIFTTYPE,GIFTAMOUNT,NUMPAYMENTS,FREQUENCY,AUTOINSTALLMENTS,
          ANONYMOUS,STARTDATE,INFINITEGIFT,PAYMENTMETHODCODE,CREDITCARDTYPE,CREDITCARDID, AUTHORIZATIONCODE, TRANSACTIONID, REJECTIONMESSAGE,
          CHECKDATE,CHECKNUMBER,BANKACCOUNTTYPECODE,BANKACCOUNTHOLDER,BANKACCOUNTNUMBER,BANKACCOUNTROUTINGCODE,SAMEASPRIMARYADDRESS,
          BILLING_ADDRESSTYPECODEID,BILLING_COUNTRYID,BILLING_ADDRESSBLOCK,BILLING_CITY,BILLING_STATEID,BILLING_POSTCODE,SHOWTRIBUTE,
          TRIBUTEFIRSTNAME,TRIBUTELASTNAME,TRIBUTEEMAIL,TRIBUTEADDRESS,TRIBUTECITY,TRIBUTEPOSTCODE,TRIBUTESTATEID,
          TRIBUTETYPECODEID,TRIBUTETEXT,TRIBUTEANONYMOUS,ACKNOWLEDGEFIRSTNAME,ACKNOWLEDGELASTNAME,ACKNOWLEDGEEMAIL,
          MESSAGETOACKNOWLEDGE,RECOGNITIONCREDIT,MATCHINGCOMPANYNAME,MATCHINGAMOUNT,RECIPIENTGROUPID,RECIPIENTREGISTRANTID,RECIPIENTROLE,SENDTOACKNOWLEDGE,
          ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SAVETRIBUTEEASCONSTITUENT, ORIGINALCLIENTUSERID, ORIGINALCONSTITUENTID,
      PHONEID, ADDRESSID, EMAILADDRESSID, ORGANIZATIONCONSTITUENTID,SENDNOTIFICATIONS,VENDORID)
    VALUES
        (@ID,@BATCHID,@SEQUENCE,@EVENTID,isnull(@USERID, ''),isnull(@PASSWORD, ''),isnull(@LASTNAME, ''),isnull(@FIRSTNAME, ''),isnull(@MIDDLENAME, ''),@TITLECODEID,@SUFFIXCODEID,isnull(@NICKNAME,''),isnull(@MAIDENNAME,''),
           isnull(@BIRTHDATE,'00000000'),isnull(@GENDERCODE, 0),@MARITALSTATUSCODEID,@ADDRESSTYPECODEID,@COUNTRYID,isnull(@ADDRESS,''),isnull(@CITY,''),@STATEID,isnull(@POSTCODE,''),isnull(@DONOTMAIL,0),
           @DONOTMAILREASONCODEID,@PHONETYPECODEID,isnull(@PHONENUMBER,''),isnull(@EMAILADDRESS,''),isnull(@ISCOMPANYGIFT,0),isnull(@COMPANYNAME,''),isnull(@COMPANYURL,''),@COMPANY_ADDRESSTYPECODEID,
           @COMPANY_COUNTRYID,isnull(@COMPANY_ADDRESS,''),isnull(@COMPANY_CITY,''),@COMPANY_STATEID,isnull(@COMPANY_POSTCODE,''),isnull(@COMPANY_DONOTMAIL,0),@COMPANY_DONOTMAILREASONCODEID,
           @COMPANY_PHONETYPECODEID,isnull(@COMPANY_PHONENUMBER,''),isnull(@COMPANY_EMAILADDRESS,''),isnull(@GIFTTYPE,''),@GIFTAMOUNT,isnull(@NUMPAYMENTS,0),isnull(@FREQUENCY,''),isnull(@AUTOINSTALLMENTS,0),
           isnull(@ANONYMOUS,0),isnull(@STARTDATE,'00000000'),isnull(@INFINITEGIFT,0),@PAYMENTMETHODCODE,@CREDITCARDTYPE,@CREDITCARDID, isnull(@AUTHORIZATIONCODE,''), @TRANSACTIONID, isnull(@REJECTIONMESSAGE,''),
           isnull(@CHECKDATE,'00000000'),isnull(@CHECKNUMBER,''),@BANKACCOUNTTYPECODE,isnull(@BANKACCOUNTHOLDER,''),isnull(@BANKACCOUNTNUMBER,''),isnull(@BANKACCOUNTROUTINGCODE,''),isnull(@SAMEASPRIMARYADDRESS,0),
           @BILLING_ADDRESSTYPECODEID,@BILLING_COUNTRYID,isnull(@BILLING_ADDRESSBLOCK,''),isnull(@BILLING_CITY,''),@BILLING_STATEID,isnull(@BILLING_POSTCODE,''),isnull(@SHOWTRIBUTE,0),
           isnull(@TRIBUTEFIRSTNAME,''),isnull(@TRIBUTELASTNAME,''),isnull(@TRIBUTEEMAIL,''),isnull(@TRIBUTEADDRESS,''),isnull(@TRIBUTECITY,''),isnull(@TRIBUTEPOSTCODE,''),@TRIBUTESTATEID,
           @TRIBUTETYPECODEID,isnull(@TRIBUTETEXT,''),isnull(@TRIBUTEANONYMOUS,0),isnull(@ACKNOWLEDGEFIRSTNAME,''),isnull(@ACKNOWLEDGELASTNAME,''),isnull(@ACKNOWLEDGEEMAIL,''),
           isnull(@MESSAGETOACKNOWLEDGE,''),isnull(@RECOGNITIONCREDIT,''),isnull(@MATCHINGCOMPANYNAME,''),isnull(@MATCHINGAMOUNT,0),@RECIPIENTGROUPID,@RECIPIENTREGISTRANTID,isnull(@RECIPIENTROLE,''),isnull(@SENDTOACKNOWLEDGE,0),
           @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,isnull(@SAVETRIBUTEEASCONSTITUENT,0), @ORIGINALCLIENTUSERID, @ORIGINALCONSTITUENTID,
       @PHONEID, @ADDRESSID, @EMAILADDRESSID, @ORGANIZATIONCONSTITUENTID,isnull(@SENDNOTIFICATIONS,0),isnull(@VENDORID, ''))
  END
  ELSE
      UPDATE dbo.DONATIONBATCH
      SET 
          ID = @ID,
      SEQUENCE = @SEQUENCE,
      EVENTID = @EVENTID,
      USERID = @USERID,
      PASSWORD = @PASSWORD,
      LASTNAME = @LASTNAME,
      FIRSTNAME = @FIRSTNAME,
      MIDDLENAME = @MIDDLENAME,
      TITLECODEID = @TITLECODEID,
      SUFFIXCODEID = @SUFFIXCODEID,
      NICKNAME = @NICKNAME,
      MAIDENNAME = @MAIDENNAME,
      BIRTHDATE = @BIRTHDATE,
      GENDERCODE = @GENDERCODE,
      MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
      ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
      COUNTRYID = @COUNTRYID,
      ADDRESS = @ADDRESS,
      CITY = @CITY,
      STATEID = @STATEID,
      POSTCODE = @POSTCODE,
      DONOTMAIL = @DONOTMAIL,
      DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
      PHONETYPECODEID = @PHONETYPECODEID,
      PHONENUMBER = @PHONENUMBER,
      EMAILADDRESS = @EMAILADDRESS,
      ISCOMPANYGIFT = @ISCOMPANYGIFT,
      COMPANYNAME = @COMPANYNAME,
      COMPANYURL = @COMPANYURL,
      COMPANY_ADDRESSTYPECODEID = @COMPANY_ADDRESSTYPECODEID,
      COMPANY_COUNTRYID = @COMPANY_COUNTRYID,
      COMPANY_ADDRESS = @COMPANY_ADDRESS,
      COMPANY_CITY = @COMPANY_CITY,
      COMPANY_STATEID = @COMPANY_STATEID,
      COMPANY_POSTCODE = @COMPANY_POSTCODE,
      COMPANY_DONOTMAIL = @COMPANY_DONOTMAIL,
      COMPANY_DONOTMAILREASONCODEID = @COMPANY_DONOTMAILREASONCODEID,
      COMPANY_PHONETYPECODEID = @COMPANY_PHONETYPECODEID,
      COMPANY_PHONENUMBER = @COMPANY_PHONENUMBER,
      COMPANY_EMAILADDRESS = @COMPANY_EMAILADDRESS,
      GIFTTYPE = @GIFTTYPE,
      GIFTAMOUNT = @GIFTAMOUNT,
      NUMPAYMENTS = @NUMPAYMENTS,
      FREQUENCY = @FREQUENCY,
      AUTOINSTALLMENTS = @AUTOINSTALLMENTS,
      ANONYMOUS = @ANONYMOUS,
      STARTDATE = @STARTDATE,
      INFINITEGIFT = @INFINITEGIFT,
      PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
      CREDITCARDTYPE = @CREDITCARDTYPE,
      CREDITCARDID = @CREDITCARDID,
      AUTHORIZATIONCODE = @AUTHORIZATIONCODE, 
      TRANSACTIONID = @TRANSACTIONID, 
      REJECTIONMESSAGE = @REJECTIONMESSAGE,
      CHECKDATE = @CHECKDATE,
      CHECKNUMBER = @CHECKNUMBER,
      BANKACCOUNTTYPECODE = @BANKACCOUNTTYPECODE,
      BANKACCOUNTHOLDER = @BANKACCOUNTHOLDER,
      BANKACCOUNTNUMBER = @BANKACCOUNTNUMBER,
      BANKACCOUNTROUTINGCODE = @BANKACCOUNTROUTINGCODE,
      SAMEASPRIMARYADDRESS = @SAMEASPRIMARYADDRESS,
      BILLING_ADDRESSTYPECODEID = @BILLING_ADDRESSTYPECODEID,
      BILLING_COUNTRYID = @BILLING_COUNTRYID,
      BILLING_ADDRESSBLOCK = @BILLING_ADDRESSBLOCK,
      BILLING_CITY = @BILLING_CITY,
      BILLING_STATEID = @BILLING_STATEID,
      BILLING_POSTCODE = @BILLING_POSTCODE,
      SHOWTRIBUTE = @SHOWTRIBUTE,
      TRIBUTEFIRSTNAME = @TRIBUTEFIRSTNAME,
      TRIBUTELASTNAME = @TRIBUTELASTNAME,
      TRIBUTEEMAIL = @TRIBUTEEMAIL,
      TRIBUTEADDRESS = @TRIBUTEADDRESS,
      TRIBUTECITY = @TRIBUTECITY,
      TRIBUTEPOSTCODE = @TRIBUTEPOSTCODE,
      TRIBUTESTATEID = @TRIBUTESTATEID,
      TRIBUTETYPECODEID = @TRIBUTETYPECODEID,
      TRIBUTETEXT = @TRIBUTETEXT,
      TRIBUTEANONYMOUS = @TRIBUTEANONYMOUS,
      ACKNOWLEDGEFIRSTNAME = @ACKNOWLEDGEFIRSTNAME,
      ACKNOWLEDGELASTNAME = @ACKNOWLEDGELASTNAME,
      ACKNOWLEDGEEMAIL = @ACKNOWLEDGEEMAIL,
      MESSAGETOACKNOWLEDGE = @MESSAGETOACKNOWLEDGE,
      RECOGNITIONCREDIT = @RECOGNITIONCREDIT,
      MATCHINGCOMPANYNAME = @MATCHINGCOMPANYNAME,
      MATCHINGAMOUNT = @MATCHINGAMOUNT,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE,
      RECIPIENTGROUPID = @RECIPIENTGROUPID,
      RECIPIENTREGISTRANTID = @RECIPIENTREGISTRANTID,
      RECIPIENTROLE = @RECIPIENTROLE,
      SENDTOACKNOWLEDGE = @SENDTOACKNOWLEDGE,
      SAVETRIBUTEEASCONSTITUENT = @SAVETRIBUTEEASCONSTITUENT,
      ORIGINALCLIENTUSERID = @ORIGINALCLIENTUSERID,
      ORIGINALCONSTITUENTID = @ORIGINALCONSTITUENTID,
      PHONEID = @PHONEID,
      ADDRESSID = @ADDRESSID,
      EMAILADDRESSID = @EMAILADDRESSID,
      ORGANIZATIONCONSTITUENTID = @ORGANIZATIONCONSTITUENTID,
      SENDNOTIFICATIONS = @SENDNOTIFICATIONS,
      VENDORID = isnull(@VENDORID, '')
        WHERE  ID= @ID
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0