USP_BATCHEVENTSPONSORSHIP_ADD_EDIT

CREATE procedure dbo.USP_BATCHEVENTSPONSORSHIP_ADD_EDIT
(
  @ID uniqueidentifier = null output,
  @BATCHID uniqueidentifier = null,
  @EVENTID uniqueidentifier = null,
  @SEQUENCE int = null,
  @USERID nvarchar(100),
  @PASSWORD nvarchar(510),
  @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 = '',

  @ISCOMPANYSPONSOR bit = 0,
  @ORGANIZATIONCONSTITUENTID uniqueidentifier = null,
  @COMPANYNAME nvarchar(200) = '',
  @COMPANYURL UDT_WEBADDRESS = '',
  @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 = '',

  @SPONSORSHIPTYPEID uniqueidentifier = null,
  @DECLINEBENEFITS bit,
  @SPONSORSHIPURL UDT_WEBADDRESS = '',
  @SPONSORSHIPMESSAGE nvarchar(2000) = '',

  @ADDITIONALDONATIONAMOUNT money = 0,
  @PAYMENTMETHODCODE nvarchar(20) = '0',  
  @CREDITCARDHOLDER nvarchar(200) = '',
  @CREDITCARDNUMBER nvarchar(200) = '',
  @CREDITCARDTYPE uniqueidentifier = null,
  @EXPIRATIONDATE UDT_FUZZYDATE = '00000000',
  @CREDITCARDTOKEN uniqueidentifier = null,
  @CHECKNUMBER nvarchar(20) = '',
  @CHECKDATE UDT_FUZZYDATE = '00000000',
  @BANKACCOUNTTYPECODE nvarchar(20) = '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(50) = '',
  @BILLING_STATEID uniqueidentifier = null,
  @BILLING_POSTCODE nvarchar(24) = '',

  @ORIGINALCLIENTUSERID integer = null,
  @ORIGINALCONSTITUENTID uniqueidentifier = null,
  @PHONEID uniqueidentifier = null,
  @ADDRESSID uniqueidentifier = null,
  @EMAILADDRESSID 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.BATCHEVENTSPONSORSHIP (NOLOCK) WHERE ID= @ID) 
    begin   
      INSERT INTO dbo.BATCHEVENTSPONSORSHIP    
      ( ID, BATCHID, SEQUENCE, EVENTID, ORIGINALCLIENTUSERID, USERID, PASSWORD, ORIGINALCONSTITUENTID, PHONEID, ADDRESSID, EMAILADDRESSID, LASTNAME, FIRSTNAME, MIDDLENAME, TITLECODEID, SUFFIXCODEID, NICKNAME,
     MAIDENNAME, BIRTHDATE, GENDERCODE, MARITALSTATUSCODEID, ADDRESSTYPECODEID, COUNTRYID, ADDRESS, CITY, STATEID, POSTCODE,
     DONOTMAIL, DONOTMAILREASONCODEID, PHONETYPECODEID, PHONENUMBER, EMAILADDRESS, ISCOMPANYSPONSOR, ORGANIZATIONCONSTITUENTID, COMPANYNAME, COMPANYURL, COMPANY_ADDRESSTYPECODEID,
     COMPANY_COUNTRYID, COMPANY_ADDRESS, COMPANY_CITY, COMPANY_STATEID, COMPANY_POSTCODE, COMPANY_DONOTMAIL, COMPANY_DONOTMAILREASONCODEID,
     COMPANY_PHONETYPECODEID, COMPANY_PHONENUMBER, COMPANY_EMAILADDRESS, SPONSORSHIPTYPEID, DECLINEBENEFITS, SPONSORSHIPURL, SPONSORSHIPMESSAGE,
     ADDITIONALDONATIONAMOUNT, PAYMENTMETHODCODE, CREDITCARDTYPE, CREDITCARDID, AUTHORIZATIONCODE, TRANSACTIONID, REJECTIONMESSAGE, CHECKNUMBER,
     CHECKDATE, BANKACCOUNTTYPECODE, BANKACCOUNTHOLDER, BANKACCOUNTNUMBER, BANKACCOUNTROUTINGCODE, SAMEASPRIMARYADDRESS, BILLING_ADDRESSTYPECODEID,
     BILLING_COUNTRYID, BILLING_ADDRESSBLOCK, BILLING_CITY, BILLING_STATEID, BILLING_POSTCODE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,SENDNOTIFICATIONS,VENDORID)    
      VALUES    
      (@ID, @BATCHID, @SEQUENCE, @EVENTID, @ORIGINALCLIENTUSERID, isnull(@USERID,''), isnull(@PASSWORD,''), @ORIGINALCONSTITUENTID, @PHONEID, @ADDRESSID, @EMAILADDRESSID, 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(@ISCOMPANYSPONSOR,0), @ORGANIZATIONCONSTITUENTID, 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,''), @SPONSORSHIPTYPEID, isnull(@DECLINEBENEFITS,0), isnull(@SPONSORSHIPURL,''), isnull(@SPONSORSHIPMESSAGE,''),
     isnull(@ADDITIONALDONATIONAMOUNT,0), @PAYMENTMETHODCODE, @CREDITCARDTYPE, @CREDITCARDID, isnull(@AUTHORIZATIONCODE,''), @TRANSACTIONID, isnull(@REJECTIONMESSAGE,''), isnull(@CHECKNUMBER,''),
     isnull(@CHECKDATE,'00000000'), @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,''),
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,isnull(@SENDNOTIFICATIONS,0), isnull(@VENDORID, ''))  

    end
    ELSE
      UPDATE dbo.BATCHEVENTSPONSORSHIP 
    SET
     EVENTID = @EVENTID,
     SEQUENCE = @SEQUENCE,
     ORIGINALCLIENTUSERID = @ORIGINALCLIENTUSERID,
     ORIGINALCONSTITUENTID = @ORIGINALCONSTITUENTID,
     PHONEID = @PHONEID,
     ADDRESSID = @ADDRESSID,
     EMAILADDRESSID = @EMAILADDRESSID,
     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,

     ISCOMPANYSPONSOR = @ISCOMPANYSPONSOR,
     ORGANIZATIONCONSTITUENTID = @ORGANIZATIONCONSTITUENTID,
     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,

     SPONSORSHIPTYPEID = @SPONSORSHIPTYPEID,
     DECLINEBENEFITS = @DECLINEBENEFITS,
     SPONSORSHIPURL = @SPONSORSHIPURL,
     SPONSORSHIPMESSAGE = @SPONSORSHIPMESSAGE,

     ADDITIONALDONATIONAMOUNT = @ADDITIONALDONATIONAMOUNT,
     PAYMENTMETHODCODE = @PAYMENTMETHODCODE,  
     CREDITCARDTYPE = @CREDITCARDTYPE,
     CREDITCARDID = @CREDITCARDID,
     AUTHORIZATIONCODE = @AUTHORIZATIONCODE, 
     TRANSACTIONID = @TRANSACTIONID, 
     REJECTIONMESSAGE = @REJECTIONMESSAGE,
     CHECKNUMBER = @CHECKNUMBER,
     CHECKDATE = @CHECKDATE,
     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,
     CHANGEDBYID = @CHANGEAGENTID,
     DATECHANGED = @CURRENTDATE,

     SENDNOTIFICATIONS = @SENDNOTIFICATIONS,
     VENDORID = isnull(@VENDORID, '')

      WHERE ID= @ID

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;