USP_DATAFORMTEMPLATE_EDIT_REGISTRANTBATCHROW_6

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REGISTRANTBATCHROW_6
(
  @ID uniqueidentifier,
  @REGISTRANTROLECODE int,
  @EVENTID uniqueidentifier,
  @SEQUENCE int,
  @USERID nvarchar(100),
  @PASSWORD nvarchar(20),
  @FIRSTNAME nvarchar(100),
  @LASTNAME nvarchar(100),
  @EMAILADDRESS UDT_EMAILADDRESS,
  @ADDRESSTYPECODEID uniqueidentifier,
  @COUNTRYID uniqueidentifier,
  @ADDRESS nvarchar(300),
  @CITY nvarchar(100),
  @STATEID uniqueidentifier,
  @POSTCODE nvarchar(24),
  @MIDDLENAME nvarchar(100),
  @TITLECODEID uniqueidentifier,
  @SUFFIXCODEID uniqueidentifier,
  @NICKNAME nvarchar(100),
  @MAIDENNAME nvarchar(200),
  @BIRTHDATE UDT_FUZZYDATE,

  @GENDERCODE int,
  @MARITALSTATUSCODEID uniqueidentifier,
  @PHONETYPECODEID uniqueidentifier,
  @PHONENUMBER nvarchar(200),
  @DONOTMAIL bit,
  @DONOTMAILREASONCODEID uniqueidentifier,
  @FUNDRAISINGGROUPNAME nvarchar(200),
  @TEAMFUNDRAISINGTEAMID uniqueidentifier,

  @SAVETRIBUTEEASCONSTITUENT bit,
  @TRIBUTEEFIRSTNAME nvarchar(400),
  @TRIBUTEELASTNAME nvarchar(400),
  @TRIBUTEEEMAIL UDT_EMAILADDRESS,
  @TRIBUTEEADDRESS nvarchar(600),
  @TRIBUTEECITY nvarchar(200),
  @TRIBUTEEPOSTCODE nvarchar(48),
  @TRIBUTEESTATEID uniqueidentifier,
  @TRIBUTETYPECODEID uniqueidentifier,
  @TRIBUTETEXT nvarchar(510),
  @TRIBUTEANONYMOUS bit,
  @SAVEASDEFAULTTRIBUTEE bit,
  @SENDTOACKNOWLEDGEE bit,
  @ACKNOWLEDGEEFIRSTNAME  nvarchar(400),
  @ACKNOWLEDGEELASTNAME nvarchar(400),
  @ACKNOWLEDGEEEMAIL UDT_EMAILADDRESS,
  @MESSAGETOACKNOWLEDGEE  nvarchar(2000),

  @PARTICIPANTPAGEURL nvarchar(200),
  @TEAMPAGEURL nvarchar(200),
  @COMPANYPAGEURL nvarchar(200),
  @HOUSEHOLDPAGEURL nvarchar(200),
  @REGISTRATIONOPTIONID uniqueidentifier,
  @DECLINEBENEFITS bit,
  @BENEFITS xml,
  @WAIVERACCEPTED bit,

  @FUNDRAISINGGOAL money,
  @TARGETFUNDRAISINGGOAL money,
  @PARTICIPANTGOAL    int,
  @DONORRETENTIONGOAL    decimal(7,4),
  @COMMUNICATIONGOAL    int,
  @TEAMFUNDRAISINGGOAL    money,
  @TEAMMEMBERGOAL    int,
  @TEAMMEMBERRETENTIONGOAL decimal(7,4),
  @TEAMCOMMUNICATIONGOAL    int,
  @TEAMPARTICIPANTGOAL    int,
  @COFUNDRAISINGGOAL    money,
  @COTEAMSGOAL    int,
  @COTEAMRETENTIONGOAL decimal(7,4),
  @COMEMBERGOAL    int,
  @COMEMBERRETENTIONGOAL decimal(7,4),
  @COPARTICIPANTGOAL    int,
  @COCOMMUNICATIONGOAL    int,
  @HOFUNDRAISINGGOAL    money,
  @HOTARGETFUNDRAISINGGOAL    money,
  @HOPARTICIPANTGOAL    int,
  @HODONORRETENTIONGOAL decimal(7,4),
  @HOCOMMUNICATIONGOAL    int,  
  @ADDITIONALDONATIONAMOUNT money,
  @PAYMENTMETHODCODE nvarchar(20),  
  @CREDITCARDHOLDER nvarchar(200),
  @CREDITCARDNUMBER nvarchar(20),
  @CREDITCARDTYPE uniqueidentifier,
  @EXPIRATIONDATE UDT_FUZZYDATE,
  @CREDITCARDTOKEN uniqueidentifier,
  @CREDITCARDID uniqueidentifier,

  @CHECKDATE UDT_FUZZYDATE,
  @CHECKNUMBER nvarchar(20),
  @BANKACCOUNTTYPECODE nvarchar(20),
  @BANKACCOUNTHOLDER nvarchar(200),
  @BANKACCOUNTNUMBER nvarchar(200),
  @BANKACCOUNTROUTINGCODE nvarchar(200),

  @SAMEASPRIMARYADDRESS bit,
  @BILLING_COUNTRYID uniqueidentifier,
  @BILLING_ADDRESSBLOCK nvarchar(300),
  @BILLING_CITY nvarchar(50),
  @BILLING_STATEID uniqueidentifier,
  @BILLING_POSTCODE nvarchar(24),

  @SUBSCRIBEDNEWSLETTERIDS xml,
  @MERCHANTACCOUNTID uniqueidentifier,
  @ISORGANIZATION bit,
  @ORGANIZATIONID uniqueidentifier,
  @ORGANIZATION_NAME nvarchar(100),
  @ORGANIZATION_WEBADDRESS nvarchar(500),
  @ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier,
  @ORGANIZATION_COUNTRYID uniqueidentifier,
  @ORGANIZATION_ADDRESSBLOCK nvarchar(150),
  @ORGANIZATION_CITY nvarchar(50),
  @ORGANIZATION_STATEID uniqueidentifier,
  @ORGANIZATION_POSTCODE nvarchar(12),
  @ORGANIZATION_DONOTMAIL bit,
  @ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier,
  @ORGANIZATION_PHONETYPECODEID uniqueidentifier,
  @ORGANIZATION_NUMBER nvarchar(100),
  @ORGANIZATION_EMAILADDRESS UDT_EMAILADDRESS,
  @ORIGINALCLIENTUSERID integer,
  @ORIGINALCONSTITUENTID uniqueidentifier,
  @PHONEID uniqueidentifier,
  @ADDRESSID uniqueidentifier,
  @EMAILADDRESSID uniqueidentifier,

  @CHANGEAGENTID uniqueidentifier = null,
  @AUTHORIZATIONCODE nvarchar(20),
  @TRANSACTIONID uniqueidentifier,
  @REJECTIONMESSAGE nvarchar(250),
  @TYPECODE tinyint,
  @AMOUNT money,
  @CONSTITUENTID uniqueidentifier,
  @BILLING_STATE nvarchar(10),
  @BILLING_COUNTRY nvarchar(4),

  @HOMEMBERGOAL    int,
  @HOMEMBERRETENTIONGOAL decimal(7,4),

  @SENDNOTIFICATIONS bit,
  @BNUMBER nvarchar(100),
  @VENDORID nvarchar(50)
)
as
  if @TEAMCOMMUNICATIONGOAL is null
    set @TEAMCOMMUNICATIONGOAL = 0

  if @TEAMFUNDRAISINGGOAL is null
    set @TEAMFUNDRAISINGGOAL = 0

  if @TEAMMEMBERGOAL is null
    set @TEAMMEMBERGOAL = 0

  if @TEAMMEMBERRETENTIONGOAL is null
    set @TEAMMEMBERRETENTIONGOAL = 0

  if @TEAMPARTICIPANTGOAL is null
    set @TEAMPARTICIPANTGOAL = 0

  if @ADDITIONALDONATIONAMOUNT is null
    set @ADDITIONALDONATIONAMOUNT = 0

  if @COCOMMUNICATIONGOAL is null
    set @COCOMMUNICATIONGOAL = 0

  if @COFUNDRAISINGGOAL is null
    set @COFUNDRAISINGGOAL = 0

  if @COMEMBERGOAL is null
    set @COMEMBERGOAL = 0

  if @COMEMBERRETENTIONGOAL is null
    set @COMEMBERRETENTIONGOAL = 0

  if @COMMUNICATIONGOAL is null
    set @COMMUNICATIONGOAL = 0

  if @COPARTICIPANTGOAL is null
    set @COPARTICIPANTGOAL = 0

  if @COTEAMRETENTIONGOAL is null
    set @COTEAMRETENTIONGOAL = 0

  if @COTEAMSGOAL is null
    set @COTEAMSGOAL = 0

  if @DONORRETENTIONGOAL is null
    set @DONORRETENTIONGOAL = 0

  if @FUNDRAISINGGOAL is null
    set @FUNDRAISINGGOAL = 0

  if @HOCOMMUNICATIONGOAL is null
    set @HOCOMMUNICATIONGOAL = 0

  if @HOFUNDRAISINGGOAL is null
    set @HOFUNDRAISINGGOAL = 0

  if @HOMEMBERGOAL is null
    set @HOMEMBERGOAL = 0

  if @HOMEMBERRETENTIONGOAL is null
    set @HOMEMBERRETENTIONGOAL = 0

  if @HOPARTICIPANTGOAL is null
    set @HOPARTICIPANTGOAL = 0

  if @HODONORRETENTIONGOAL is null
    set @HODONORRETENTIONGOAL = 0

  if @HOTARGETFUNDRAISINGGOAL is null
    set @HOTARGETFUNDRAISINGGOAL = 0

  if @PARTICIPANTGOAL is null
    set @PARTICIPANTGOAL = 0

  if @TARGETFUNDRAISINGGOAL is null
    set @TARGETFUNDRAISINGGOAL = 0

  if @ORIGINALCLIENTUSERID = 0
    set @ORIGINALCLIENTUSERID = 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

begin 
if @PARTICIPANTPAGEURL is not null
  set @PARTICIPANTPAGEURL = ltrim(rtrim(@PARTICIPANTPAGEURL))
  if len(@PARTICIPANTPAGEURL) =1
    raiserror('PARTICIPANTPAGEURLONECHAR',13,1)
  else if patindex('%[^0-9a-zA-Z_-]%',@PARTICIPANTPAGEURL) <> 0
    raiserror('PARTICIPANTPAGEURLVALID',13,1)

if @COMPANYPAGEURL is not null
  set @COMPANYPAGEURL = ltrim(rtrim(@COMPANYPAGEURL))
  if len(@COMPANYPAGEURL) =1
    raiserror('COMPANYPAGEURLONECHAR',13,1)
  else if patindex('%[^0-9a-zA-Z_-]%',@COMPANYPAGEURL) <> 0
    raiserror('COMPANYPAGEURLVALID',13,1)

if @TEAMPAGEURL is not null
  set @TEAMPAGEURL = ltrim(rtrim(@TEAMPAGEURL))
  if len(@TEAMPAGEURL) =1
    raiserror('TEAMPAGEURLONECHAR',13,1)
  else if patindex('%[^0-9a-zA-Z_-]%',@TEAMPAGEURL) <> 0
    raiserror('TEAMPAGEURLVALID',13,1)

if @HOUSEHOLDPAGEURL is not null
  set @HOUSEHOLDPAGEURL = ltrim(rtrim(@HOUSEHOLDPAGEURL))
  if len(@HOUSEHOLDPAGEURL) =1
    raiserror('HOUSEHOLDPAGEURLONECHAR',13,1)
  else if patindex('%[^0-9a-zA-Z_-]%',@HOUSEHOLDPAGEURL) <> 0
    raiserror('HOUSEHOLDPAGEURLVALID',13,1)


if @PARTICIPANTPAGEURL is not null and @COMPANYPAGEURL is not null and len(@PARTICIPANTPAGEURL)>0
  if @PARTICIPANTPAGEURL = @COMPANYPAGEURL
    raiserror('COMPANYPAGEURLSAME',13,1)


if @PARTICIPANTPAGEURL is not null and @TEAMPAGEURL is not null and len(@PARTICIPANTPAGEURL)>0
  if @PARTICIPANTPAGEURL = @TEAMPAGEURL
    raiserror('TEAMPAGEURLSAME',13,1)

if @PARTICIPANTPAGEURL is not null and @HOUSEHOLDPAGEURL is not null and len(@PARTICIPANTPAGEURL)>0
  if @PARTICIPANTPAGEURL = @HOUSEHOLDPAGEURL
    raiserror('HOUSEHOLDPAGEURLSAME',13,1)
end

  exec dbo.USP_REGISTRANTBATCH_ADD_EDIT_2
    @ID = @ID output,
    @REGISTRANTROLECODE = @REGISTRANTROLECODE,
    @BATCHID = null,
    @EVENTID = @EVENTID,
    @SEQUENCE = @SEQUENCE,
    @USERID = @USERID, 
    @PASSWORD = @PASSWORD,
    @FIRSTNAME = @FIRSTNAME,
    @LASTNAME = @LASTNAME,
    @EMAILADDRESS = @EMAILADDRESS,
    @ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
    @COUNTRYID = @COUNTRYID,
    @ADDRESS = @ADDRESS,
    @CITY = @CITY,
    @STATEID = @STATEID,
    @POSTCODE = @POSTCODE,
    @MIDDLENAME = @MIDDLENAME,
    @TITLECODEID = @TITLECODEID,
    @SUFFIXCODEID = @SUFFIXCODEID,
    @NICKNAME = @NICKNAME,
    @MAIDENNAME = @MAIDENNAME,
    @BIRTHDATE = @BIRTHDATE,

    @GENDERCODE = @GENDERCODE,
    @MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
    @PHONETYPECODEID = @PHONETYPECODEID,
    @PHONENUMBER = @PHONENUMBER,
    @DONOTMAIL = @DONOTMAIL,
    @DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
    @FUNDRAISINGGROUPNAME = @FUNDRAISINGGROUPNAME,
    @TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID,

    @SAVETRIBUTEEASCONSTITUENT = @SAVETRIBUTEEASCONSTITUENT,
    @TRIBUTEEFIRSTNAME = @TRIBUTEEFIRSTNAME,
    @TRIBUTEELASTNAME = @TRIBUTEELASTNAME,
    @TRIBUTEEEMAIL = @TRIBUTEEEMAIL,
    @TRIBUTEEADDRESS = @TRIBUTEEADDRESS,
    @TRIBUTEECITY = @TRIBUTEECITY,
    @TRIBUTEEPOSTCODE = @TRIBUTEEPOSTCODE,
    @TRIBUTEESTATEID = @TRIBUTEESTATEID,
    @TRIBUTETYPECODEID = @TRIBUTETYPECODEID,
    @TRIBUTETEXT = @TRIBUTETEXT,
    @TRIBUTEANONYMOUS = @TRIBUTEANONYMOUS,
    @SAVEASDEFAULTTRIBUTEE = @SAVEASDEFAULTTRIBUTEE,
    @SENDTOACKNOWLEDGEE = @SENDTOACKNOWLEDGEE,
    @ACKNOWLEDGEEFIRSTNAME  = @ACKNOWLEDGEEFIRSTNAME,
    @ACKNOWLEDGEELASTNAME = @ACKNOWLEDGEELASTNAME,
    @ACKNOWLEDGEEEMAIL = @ACKNOWLEDGEEEMAIL,
    @MESSAGETOACKNOWLEDGEE = @MESSAGETOACKNOWLEDGEE,

    @PARTICIPANTPAGEURL = @PARTICIPANTPAGEURL,
    @TEAMPAGEURL = @TEAMPAGEURL,
    @COMPANYPAGEURL = @COMPANYPAGEURL,
    @HOUSEHOLDPAGEURL = @HOUSEHOLDPAGEURL,
    @REGISTRATIONOPTIONID = @REGISTRATIONOPTIONID,


    @WAIVERACCEPTED = @WAIVERACCEPTED,

    @FUNDRAISINGGOAL = @FUNDRAISINGGOAL,
    @TARGETFUNDRAISINGGOAL = @TARGETFUNDRAISINGGOAL,
    @PARTICIPANTGOAL = @PARTICIPANTGOAL,
    @DONORRETENTIONGOAL    = @DONORRETENTIONGOAL,
    @COMMUNICATIONGOAL = @COMMUNICATIONGOAL,
    @TEAMFUNDRAISINGGOAL = @TEAMFUNDRAISINGGOAL,
    @TEAMMEMBERGOAL    = @TEAMMEMBERGOAL,
    @TEAMMEMBERRETENTIONGOAL = @TEAMMEMBERRETENTIONGOAL,
    @TEAMCOMMUNICATIONGOAL = @TEAMCOMMUNICATIONGOAL,
    @TEAMPARTICIPANTGOAL = @TEAMPARTICIPANTGOAL,
    @COFUNDRAISINGGOAL = @COFUNDRAISINGGOAL,
    @COTEAMSGOAL = @COTEAMSGOAL,
    @COTEAMRETENTIONGOAL = @COTEAMRETENTIONGOAL,
    @COMEMBERGOAL = @COMEMBERGOAL,
    @COMEMBERRETENTIONGOAL = @COMEMBERRETENTIONGOAL,
    @COPARTICIPANTGOAL = @COPARTICIPANTGOAL,
    @COCOMMUNICATIONGOAL = @COCOMMUNICATIONGOAL,
    @HOFUNDRAISINGGOAL = @HOFUNDRAISINGGOAL,
    @HOMEMBERGOAL    = @HOMEMBERGOAL,
    @HOMEMBERRETENTIONGOAL = @HOMEMBERRETENTIONGOAL,
    @HOCOMMUNICATIONGOAL = @HOCOMMUNICATIONGOAL,
    @HOPARTICIPANTGOAL = @HOPARTICIPANTGOAL,
    @ADDITIONALDONATIONAMOUNT = @ADDITIONALDONATIONAMOUNT,
    @PAYMENTMETHODCODE = @PAYMENTMETHODCODE, 
    @CREDITCARDHOLDER = @CREDITCARDHOLDER,
    @CREDITCARDNUMBER = @CREDITCARDNUMBER,
    @CREDITCARDTYPE = @CREDITCARDTYPE,
    @EXPIRATIONDATE = @EXPIRATIONDATE,
    @CREDITCARDTOKEN = @CREDITCARDTOKEN,
    @AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
    @TRANSACTIONID = @TRANSACTIONID,
    @REJECTIONMESSAGE = @REJECTIONMESSAGE,
    @CHECKDATE = @CHECKDATE,
    @CHECKNUMBER = @CHECKNUMBER,
    @BANKACCOUNTTYPECODE = @BANKACCOUNTTYPECODE,
    @BANKACCOUNTHOLDER = @BANKACCOUNTHOLDER,
    @BANKACCOUNTNUMBER = @BANKACCOUNTNUMBER,
    @BANKACCOUNTROUTINGCODE = @BANKACCOUNTROUTINGCODE,

    @SAMEASPRIMARYADDRESS = @SAMEASPRIMARYADDRESS,
    @BILLING_COUNTRYID = @BILLING_COUNTRYID,
 @BILLING_ADDRESSBLOCK = @BILLING_ADDRESSBLOCK,
    @BILLING_CITY = @BILLING_CITY,
    @BILLING_STATEID = @BILLING_STATEID,
    @BILLING_POSTCODE = @BILLING_POSTCODE,

    @ISORGANIZATION = @ISORGANIZATION,
    @ORGANIZATIONID = @ORGANIZATIONID, 
    @ORGANIZATION_NAME = @ORGANIZATION_NAME, 
    @ORGANIZATION_WEBADDRESS = @ORGANIZATION_WEBADDRESS, 
    @ORGANIZATION_ADDRESSTYPECODEID = @ORGANIZATION_ADDRESSTYPECODEID, 
    @ORGANIZATION_COUNTRYID = @ORGANIZATION_COUNTRYID,
    @ORGANIZATION_ADDRESSBLOCK = @ORGANIZATION_ADDRESSBLOCK, 
    @ORGANIZATION_CITY = @ORGANIZATION_CITY, 
    @ORGANIZATION_STATEID = @ORGANIZATION_STATEID, 
    @ORGANIZATION_POSTCODE = @ORGANIZATION_POSTCODE, 
    @ORGANIZATION_DONOTMAIL = @ORGANIZATION_DONOTMAIL, 
    @ORGANIZATION_DONOTMAILREASONCODEID = @ORGANIZATION_DONOTMAILREASONCODEID,
    @ORGANIZATION_PHONETYPECODEID = @ORGANIZATION_PHONETYPECODEID, 
    @ORGANIZATION_NUMBER = @ORGANIZATION_NUMBER, 
    @ORGANIZATION_EMAILADDRESS = @ORGANIZATION_EMAILADDRESS,
    @ORIGINALCLIENTUSERID = @ORIGINALCLIENTUSERID,
    @ORIGINALCONSTITUENTID = @ORIGINALCONSTITUENTID,
    @PHONEID = @PHONEID,
    @ADDRESSID = @ADDRESSID,
    @EMAILADDRESSID = @EMAILADDRESSID,

    @CHANGEAGENTID = @CHANGEAGENTID,

    @SENDNOTIFICATIONS = @SENDNOTIFICATIONS,

    @DECLINEBENEFITS = @DECLINEBENEFITS,
    @BENEFITS = @BENEFITS,
    @VENDORID=@VENDORID

  -- exec dbo.USP_REGISTRANTBATCH_EMAILLIST_ADDFROMXML @ID, @SUBSCRIBEDNEWSLETTERIDS, @CHANGEAGENTID;


return 0;