USP_DATAFORMTEMPLATE_ADD_BATCHEVENTREGISTRANTBATCHCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHEVENTREGISTRANTBATCHCOMMIT
(
  @ID uniqueidentifier = null output,
  @VALIDATEONLY bit = 0,
  @CHANGEAGENTID uniqueidentifier,
  @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) = '',
  @BATCHROWID uniqueidentifier = null,
  @DESIGNATIONID uniqueidentifier = null,
  @REGISTRATIONTYPECODE tinyint = 0,
  @REGISTRATIONSTATUSCODE tinyint = 0,
  @REGISTRATIONATTENDEDCODE tinyint = 0,
  @EVENT_IMPORTLOOKUPID nvarchar(100) = '',
  @OPPORTUNITYID uniqueidentifier = null
)
as
set nocount on;

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


    -- Set automatic values 

    if @REGISTRATIONTYPECODE = 1 --walkin

    begin
      set @REGISTRATIONSTATUSCODE = 0;   --Registered

      set @REGISTRATIONATTENDEDCODE = 1; --Attended

    end

    if @REGISTRATIONSTATUSCODE > 0       --Will not attend or canceled

      set @REGISTRATIONATTENDEDCODE = 2; --Not attended


    -- always run validations, whether user is just validating or trying to commit


    if dbo.UFN_CONSTITUENT_ISGROUP(@CONSTITUENTID) = 1
      raiserror('ERR_EVENTREGISTRANTBATCHCOMMIT_VALIDCONSTITUENTTYPE : Registrants must be individuals or organizations.',13,1);

    declare @GUESTLIST table (CONSTITUENTID uniqueidentifier);

    insert into @GUESTLIST
    select CONSTITUENTID
    from dbo.UFN_BATCHEVENTREGISTRANT_GETGUESTS_FROMITEMLISTXML(@GUESTS);

    if exists
    (
      select 1
      from @GUESTLIST
      where dbo.UFN_CONSTITUENT_ISGROUP(CONSTITUENTID) = 1
    )
      raiserror('ERR_EVENTREGISTRANTBATCHCOMMIT_VALIDCONSTITUENTTYPE_GUESTS : Registrants must be individuals or organizations.',13,1);

    if exists
    (
      select COUNT(*)
      from @GUESTLIST
      where CONSTITUENTID <> @UNKNOWNGUESTWELLKNOWNGUID
      group by CONSTITUENTID
      having COUNT(*) > 1
    )
      raiserror('ERR_APPENDREGISTRATIONGUESTS_DUPLICATEGUESTS : The same guest cannot be mapped to a registration more than once.',13,1);

    declare @REGISTRANTCOUNT as int;
    declare @VALIDEVENTPRICE as bit = 0;

    select
      @VALIDEVENTPRICE = 1,
      @REGISTRANTCOUNT = (EVENTPRICE.REGISTRATIONCOUNT * @QUANTITY)
    from dbo.EVENTPRICE
    where EVENTPRICE.ID = @EVENTPRICEID;

    if @VALIDEVENTPRICE = 0
      raiserror('ERR_REGISTRANTREGISTRATION_INVALIDEVENTPRICEID', 13, 1);

    declare @REGISTRANTCOUNTDIFFERENCE int;
    set @REGISTRANTCOUNTDIFFERENCE = (@REGISTRANTCOUNT - (select count(*) from @GUESTLIST));

    if @REGISTRANTCOUNTDIFFERENCE < 0
      raiserror('ERR_APPENDREGISTRATIONGUESTS_MOREGUESTSTHANREGISTRATIONS : The number of guests cannot exceed the number of registrations.',13,1);

    if @QUANTITY is null or @QUANTITY < 1
      raiserror('ERR_REGISTRANTREGISTRATION_INVALIDQUANTITY',13,1);

    declare @BATCHID uniqueidentifier;
    select @BATCHID = BATCHID
    from dbo.BATCHEVENTREGISTRANT
    where ID = @BATCHROWID;

    declare @RELATEDBATCHREGISTRANTS table
    (
      ID uniqueidentifier,
      CONSTITUENTID uniqueidentifier
    );

    insert into @RELATEDBATCHREGISTRANTS(ID, CONSTITUENTID)
    select
      ID,
      CONSTITUENTID
    from dbo.BATCHEVENTREGISTRANT
    where
      BATCHID = @BATCHID and
      ID <> @BATCHROWID and
      EVENTID = @EVENTID;

    if exists
    (
      select 1
      from @GUESTLIST G
        inner join @RELATEDBATCHREGISTRANTS RELATED on G.CONSTITUENTID = RELATED.CONSTITUENTID
    )
      raiserror('ERR_APPENDREGISTRATIONGUESTS_GUESTWHILEHOSTONANOTHERRECORD : A guest cannot be a host for the same event on another registration in the same batch.',13,1);

    if exists
    (
      select 1
      from @GUESTLIST G
        inner join dbo.BATCHEVENTREGISTRANTGUEST BATCHGUEST on G.CONSTITUENTID = BATCHGUEST.CONSTITUENTID
        inner join @RELATEDBATCHREGISTRANTS RELATED on BATCHGUEST.BATCHEVENTREGISTRANTID = RELATED.ID
    )
      raiserror('ERR_APPENDREGISTRATIONGUESTS_GUESTREGISTEREDTWICEINBATCH : You cannot register a guest twice for the same event in the same batch.',13,1);

    if exists
    (
      select 1
      from @GUESTLIST G
        inner join dbo.REGISTRANT on G.CONSTITUENTID = REGISTRANT.CONSTITUENTID
      where REGISTRANT.EVENTID = @EVENTID
    )
      raiserror('ERR_APPENDREGISTRATIONGUESTS_GUESTEXISTINGREGISTRATION : A guest has already been registered for the same event.',13,1);

    if exists
    (
      select 1
      from dbo.REGISTRANT
      where
        CONSTITUENTID = @CONSTITUENTID and
        EVENTID = @EVENTID
    )
      raiserror('ERR_APPENDREGISTRATIONGUESTS_REGISTRANTEXISTINGREGISTRATION : The registrant is already a guest on a selected event.',13,1);

    if @REGISTRATIONTYPECODE = 1 and not ((@REGISTRANTSTATUSCODE in (1,2)) and @REGISTRANTSTATUSCODE <> 4) and @REGISTRATIONSTATUSCODE is null
      raiserror('ERR_EVENTREGISTRANT_VALIDWALKIN : The value for registration type is invalid.',13,1);

    if @REGISTRATIONTYPECODE = 1 and @REGISTRATIONSTATUSCODE is not null and not (@REGISTRATIONSTATUSCODE =0)
      raiserror('ERR_EVENTREGISTRANT_VALIDWALKIN : The value for registration type is invalid.',13,1);

    if @REGISTRATIONSTATUSCODE = 2 and not (@REGISTRATIONATTENDEDCODE in (2,3))
      raiserror('ERR_EVENTREGISTRANT_VALIDCANCELED : The value for attended is not valid with this status.',13,1);

    if @REGISTRATIONSTATUSCODE = 1 and not (@REGISTRATIONATTENDEDCODE in (2,3))
      raiserror('ERR_EVENTREGISTRANT_VALIDWILLNOTATTEND : The value for attended is not valid with this status.',13,1);

    --Bug#795705

    declare @WAIVEDREGISTRATIONFEEGUESTCOUNT integer;
    declare @EVENTPRICEREGISTRATIONCOUNT integer;

    select                    
                    @EVENTPRICEREGISTRATIONCOUNT = EVENTPRICE.REGISTRATIONCOUNT          
                from
                    dbo.EVENTPRICE
                where
                    EVENTPRICE.ID = @EVENTPRICEID;

    select
                    @WAIVEDREGISTRATIONFEEGUESTCOUNT = count([GUESTS].ITEM.value('WAIVEREGISTRATIONFEE[1]', 'bit'))
                from
                    @GUESTS.nodes('/GUESTS/ITEM') as [GUESTS](ITEM)
       where 
         [GUESTS].ITEM.value('WAIVEREGISTRATIONFEE[1]', 'bit') = 1;

    -- Waived registration fee guest count should be a multiple of Registration options registration count - Since in One-Off form this gets automatically set by UIModel, we need to maintain this in batch

        if (@WAIVEDREGISTRATIONFEEGUESTCOUNT % @EVENTPRICEREGISTRATIONCOUNT <> 0) 
            raiserror('ERR_EVENTREGISTRANT_WAIVEDGUESTCOUNT',13,1);

    if @VALIDATEONLY = 0
    begin
      exec dbo.USP_REGISTRANT_APPENDREGISTRATIONS
        @ID = @ID output,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @EVENTID = @EVENTID,
        @CONSTITUENTID = @CONSTITUENTID,
        @DATEPURCHASED = @DATEPURCHASED,
        @EVENTPRICEID = @EVENTPRICEID,
        @REGISTRANTSTATUS = @REGISTRANTSTATUSCODE,
        @WAIVEBENEFITS = @WAIVEBENEFITS,
        @QUANTITY = @QUANTITY,
        @GUESTS = @GUESTS,
        @REGISTRANTLOOKUPID = @REGISTRANTLOOKUPID,
        @DESIGNATIONID = @DESIGNATIONID,
        @REGISTRATIONTYPECODE = @REGISTRATIONTYPECODE,
        @REGISTRATIONSTATUSCODE = @REGISTRATIONSTATUSCODE,
        @REGISTRATIONATTENDEDCODE = @REGISTRATIONATTENDEDCODE;

      if @OPPORTUNITYID is not null
      begin
        exec dbo.USP_DATAFORMTEMPLATE_ADD_EVENTREGISTRATIONOPPORTUNITYLINK
          @REGISTRANTID = @ID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @OPPORTUNITYID = @OPPORTUNITYID;
      end
    end
  end try
  begin catch
    exec.dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;