USP_DATAFORMTEMPLATE_ADD_BATCHEVENTREGISTRANTBATCHROW

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHEVENTREGISTRANTBATCHROW
(
  @ID uniqueidentifier = null output,
  @BATCHID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @SEQUENCE int,
  @EVENTID uniqueidentifier = null,
  @CONSTITUENTID uniqueidentifier = null,
  @DATEPURCHASED datetime = null,
  @EVENTPRICEID uniqueidentifier = null,
  @REGISTRANTSTATUSCODE tinyint = 0,
  @WAIVEBENEFITS bit = 0,
  @QUANTITY int = 1,
  @GUESTS xml = null,
  @REGISTRANTLOOKUPID nvarchar(100) = '',
  @DESIGNATIONID uniqueidentifier = null,
  @REGISTRATIONTYPECODE tinyint = 0,
  @REGISTRATIONSTATUSCODE tinyint = 0,
  @REGISTRATIONATTENDEDCODE tinyint = 0,
  @EVENT_IMPORTLOOKUPID nvarchar(100) = '',
  @OPPORTUNITYID uniqueidentifier = null
)
as
set nocount on;

  declare @CURRENTDATE datetime;

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

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

  set @CURRENTDATE = getdate();

  begin try
  -- Find the Event ID from the Event Lookup ID

  if len(@EVENT_IMPORTLOOKUPID) > 0
  begin
    if @EVENTID is null
    begin
      with LOOKUPIDS_CTE as
      (
        select top(2) EVENT.ID ID
        from dbo.EVENT
        where EVENT.LOOKUPID = @EVENT_IMPORTLOOKUPID
      )
      select distinct @EVENTID = ID
      from LOOKUPIDS_CTE;

      if @@rowcount > 1
        raiserror('More than one event exists with the supplied lookup ID.', 13, 1);
    end
  end

  insert into dbo.BATCHEVENTREGISTRANT
  (
    [ID],
    [BATCHID],
    [SEQUENCE],
    [ADDEDBYID],
    [CHANGEDBYID],
    [DATEADDED],
    [DATECHANGED],
    [EVENTID],
    [CONSTITUENTID],
    [DATEPURCHASED],
    [EVENTPRICEID],
    [REGISTRANTSTATUSCODE],
    [WAIVEBENEFITS],
    [QUANTITY],
    [REGISTRANTLOOKUPID],
    [DESIGNATIONID],
    [ISWALKIN],
    [REGISTRATIONSTATUSCODE],
    [REGISTRATIONATTENDEDCODE],
    [OPPORTUNITYID]
  )
  values
  (
    @ID,
    @BATCHID,
    @SEQUENCE,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE,
    @EVENTID,
    @CONSTITUENTID,
    @DATEPURCHASED,
    @EVENTPRICEID,
    coalesce(@REGISTRANTSTATUSCODE, 0),
    coalesce(@WAIVEBENEFITS, 0),
    coalesce(@QUANTITY, 1),
    coalesce(@REGISTRANTLOOKUPID, ''),
    @DESIGNATIONID,
    ISNULL(@REGISTRATIONTYPECODE,0),
    case @REGISTRATIONTYPECODE
      when 1 then 0 --Walkin = registered

      else ISNULL(@REGISTRATIONSTATUSCODE,0)
    end,
    case
      when @REGISTRATIONTYPECODE = 1 then 1 --Walkin then attended

      when @REGISTRATIONSTATUSCODE in (1,2) then 2 --Will not attend or canceled then not attended

      else ISNULL(@REGISTRATIONATTENDEDCODE,0)
    end,
    @OPPORTUNITYID
  );

  declare @UNKNOWNGUESTWELLKNOWNGUID uniqueidentifier;
  set @UNKNOWNGUESTWELLKNOWNGUID = 'C776DAB5-65B2-4258-ADAE-AE396D28E251'; --Well-known GUID for unknown guest


  declare @GUESTTABLE table
  (
    ATTENDED bit,
    CONSTITUENTID uniqueidentifier,
    ID uniqueidentifier,
    WAIVEREGISTRATIONFEE bit
  );

  insert into @GUESTTABLE
  (
    ATTENDED,
    CONSTITUENTID,
    ID,
    WAIVEREGISTRATIONFEE
  )
  select
    ATTENDED,
    CONSTITUENTID,
    ID,
    WAIVEREGISTRATIONFEE
  from dbo.UFN_BATCHEVENTREGISTRANT_GETGUESTS_FROMITEMLISTXML(@GUESTS);

  update @GUESTTABLE
  set CONSTITUENTID = null
  where CONSTITUENTID = @UNKNOWNGUESTWELLKNOWNGUID;

  set @GUESTS =
  (
    select
      [ATTENDED],
      [CONSTITUENTID],
      [ID],
      [WAIVEREGISTRATIONFEE]
    from @GUESTTABLE
    for xml raw('ITEM'),type,elements,root('GUESTS'),BINARY BASE64
  )

  exec dbo.USP_BATCHEVENTREGISTRANT_GETGUESTS_ADDFROMXML @ID, @GUESTS, @CHANGEAGENTID;

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

  return 0;