USP_FAF_REGISTRATION

CREATE procedure dbo.USP_FAF_REGISTRATION
(
    @ID uniqueidentifier = null output,  
    @CURRENTAPPUSERID uniqueidentifier = null,  
    @CHANGEAGENTID uniqueidentifier = null,  
    @CURRENTDATE datetime = null,  
    @EVENTID uniqueidentifier,  
    @CONSTITUENTID uniqueidentifier,  
    @DATEPURCHASED datetime,  
    @SINGLEEVENTREGISTRATIONS xml,  
    @WAIVEBENEFITS bit,  
    @ISADD bit = 0,  
    @BYPASSSECURITY bit = 0,  
    @ISWALKIN bit = 0  

)
    with execute as caller  
    as
    set nocount on;  

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

    -- check if registrant exists

    declare @REGISTRANTEXISTSWITHID bit;  
    if exists(select REGISTRANT.ID from dbo.REGISTRANT where REGISTRANT.ID = @ID)  
        set @REGISTRANTEXISTSWITHID = 1;  
    else  
        set @REGISTRANTEXISTSWITHID = 0; 

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

    set @CURRENTDATE = getdate();  

     --List the registrations that will be created or updated  

    declare @REGISTRATIONTOADDTABLE table  
    (  
     REGISTRATIONSCOLLECTIONID uniqueidentifier,  
     EVENTID uniqueidentifier,  
     EVENTPRICEID uniqueidentifier,  
     AMOUNT money,  
     RECEIPTAMOUNT money,  
     QUANTITY int,  
     DATEPURCHASED datetime,  
     REGISTRATIONPACKAGEID uniqueidentifier,  
     REGISTRANTREGISTRATIONID uniqueidentifier,  
     NEWREGISTRANTREGISTRATION bit  
    ); 

    insert into @REGISTRATIONTOADDTABLE  
    (  
       REGISTRATIONSCOLLECTIONID,  
       EVENTID,  
       EVENTPRICEID,  
       AMOUNT,  
       RECEIPTAMOUNT,  
       QUANTITY,  
       DATEPURCHASED,  
       REGISTRATIONPACKAGEID,  
       REGISTRANTREGISTRATIONID,  
       NEWREGISTRANTREGISTRATION  
    )  
    select  
       SELECTEDREGISTRATION.SINGLEEVENTREGISTRATIONID,  
       EVENTPRICE.EVENTID,  
       SELECTEDREGISTRATION.EVENTPRICEID,  
       SELECTEDREGISTRATION.AMOUNT,  
       SELECTEDREGISTRATION.RECEIPTAMOUNT,  
       SELECTEDREGISTRATION.QUANTITY,  
       @DATEPURCHASED,  
       null,  
       SELECTEDREGISTRATION.SINGLEEVENTREGISTRANTREGISTRATIONID,  
       0  
    from  
     (  
        --Directly selecting from collection field rather than using FROMITEMLISTXML function  

        SELECT  
        T.c.value('(SINGLEEVENTREGISTRATIONID)[1]','uniqueidentifier') AS 'SINGLEEVENTREGISTRATIONID',  
        T.c.value('(SINGLEEVENTREGISTRATIONEVENTPRICEID)[1]','uniqueidentifier') AS 'EVENTPRICEID',  
        T.c.value('(QUANTITY)[1]','int') AS 'QUANTITY',  
        T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',  
        T.c.value('(RECEIPTAMOUNT)[1]','money') AS 'RECEIPTAMOUNT',  
        T.c.value('(REGISTRATIONCOUNT)[1]','int') AS 'REGISTRATIONCOUNT',  
        T.c.value('(SINGLEEVENTREGISTRANTREGISTRATIONID)[1]','uniqueidentifier') AS 'SINGLEEVENTREGISTRANTREGISTRATIONID'  
      FROM @SINGLEEVENTREGISTRATIONS.nodes('/SINGLEEVENTREGISTRATIONS/ITEM') T(c)  
     ) SELECTEDREGISTRATION  
     inner join dbo.EVENTPRICE on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICE.ID; 

    -- for existing registrant

    if @REGISTRANTEXISTSWITHID = 1 
        begin  
           update dbo.REGISTRANT set  
                  ATTENDED = 0,  
                  WILLNOTATTEND = 1,  
                  USERMARKEDATTENDANCE = 0,  
                  CHANGEDBYID = @CHANGEAGENTID,  
                  DATECHANGED = @CURRENTDATE  
           where  ID = @ID;  
        end
    -- new registrant

    else
       Begin
           insert into dbo.REGISTRANT  
              (  
               [ID],  
               [EVENTID],  
               [CONSTITUENTID],  
               [ATTENDED],  
               [WILLNOTATTEND],  
               [GUESTOFREGISTRANTID],  
               [BENEFITSWAIVED],  
               [ISWALKIN],  
               [ADDEDBYID],  
               [CHANGEDBYID],  
               [DATEADDED],  
               [DATECHANGED]  
              )  
           values  
              (  
               @ID,  
               @EVENTID,  
               @CONSTITUENTID,  
               @ISWALKIN, --Walk-in registrants attended by definition, pre-registrations set to not attended by default.  

               0, --Will attend  

               null,  
               @WAIVEBENEFITS,  
               @ISWALKIN,  
               @CHANGEAGENTID,  
               @CHANGEAGENTID,  
               @CURRENTDATE,  
               @CURRENTDATE  
              );  

         insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)  
             select  
                  @ID,  
                  CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID,  
                  @CHANGEAGENTID,  
                  @CHANGEAGENTID,  
                  @CURRENTDATE,  
                  @CURRENTDATE  
             from   
                 dbo.CONSTITUENTRESTRICTIONOPTION  
                 left join dbo.REGISTRANTRESTRICTIONOPTION on REGISTRANTRESTRICTIONOPTION.REGISTRANTID = @ID and REGISTRANTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID = CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID  
             where   
                 CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID = @CONSTITUENTID  
              and REGISTRANTRESTRICTIONOPTION.ID is null  
              and exists(select 1 from dbo.REGISTRANT where REGISTRANT.ID = @ID)  
              and @ISADD = 1;  

         --default registrant restriction options from the constituent record  

          insert into dbo.REGISTRANTRESTRICTIONOPTION(REGISTRANTID, EVENTRESTRICTIONOPTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)  
           select  
                @ID,  
                CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID,  
                @CHANGEAGENTID,  
                @CHANGEAGENTID,  
                @CURRENTDATE,  
                @CURRENTDATE  
           from   
               dbo.CONSTITUENTRESTRICTIONOPTION  
               left join dbo.REGISTRANTRESTRICTIONOPTION on REGISTRANTRESTRICTIONOPTION.REGISTRANTID = @ID 
               and REGISTRANTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID = CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID  
           where   
                CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID = @CONSTITUENTID  
                and REGISTRANTRESTRICTIONOPTION.ID is null  
                and exists(select 1 from dbo.REGISTRANT where REGISTRANT.ID = @ID)  
                and @ISADD = 1; 

          update @REGISTRATIONTOADDTABLE  
              set  
                 REGISTRANTREGISTRATIONID = newid(),  
                 NEWREGISTRANTREGISTRATION = 1  
              where  
                REGISTRANTREGISTRATIONID is null;  

          --Add the registrations  

          declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(); 

          insert into dbo.REGISTRANTREGISTRATION  
          (  
               ID,  
               REGISTRANTID,  
               EVENTPRICEID,  
               QUANTITY,  
               AMOUNT,  
               RECEIPTAMOUNT,  
               DATEPURCHASED,  
               ORGANIZATIONAMOUNT,  
               ORGANIZATIONEXCHANGERATEID,  
               ADDEDBYID,  
               CHANGEDBYID,  
               DATEADDED,  
               DATECHANGED  
          )  
          select  
               SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID,  
               REGISTRANT.ID,  
               SELECTEDREGISTRATION.EVENTPRICEID,  
               SELECTEDREGISTRATION.QUANTITY,  
               SELECTEDREGISTRATION.AMOUNT,  
               SELECTEDREGISTRATION.RECEIPTAMOUNT,  
               SELECTEDREGISTRATION.DATEPURCHASED,  
               case  
                when EVENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID  
                 then SELECTEDREGISTRATION.AMOUNT  
                else  
                 dbo.UFN_CURRENCY_CONVERT(SELECTEDREGISTRATION.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null))  
               end,  
               case  
                when EVENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID  
                 then null  
                else  
                 dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEPURCHASED, 0, null)  
               end,  
               @CHANGEAGENTID,  
               @CHANGEAGENTID,  
               @CURRENTDATE,  
               @CURRENTDATE  
          from  
             dbo.REGISTRANT  
             inner join @REGISTRATIONTOADDTABLE SELECTEDREGISTRATION on  
              SELECTEDREGISTRATION.EVENTID = REGISTRANT.EVENTID  
             inner join dbo.EVENT on SELECTEDREGISTRATION.EVENTID = EVENT.ID  
          where  
             @CONSTITUENTID = REGISTRANT.CONSTITUENTID  
             and SELECTEDREGISTRATION.QUANTITY > 0  
             and SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID not in  
               (select ID from dbo.REGISTRANTREGISTRATION); 

          --On an add form, the ID may be passed in, make sure a record exists.  

          if not exists(select REGISTRANT.ID from dbo.REGISTRANT where REGISTRANT.ID = @ID)  
              set @ID = null;  

          if @ID is null  
             select @ID = ID from  dbo.REGISTRANT  
             where CONSTITUENTID = @CONSTITUENTID and EVENTID = @EVENTID;  


       End