USP_DATALIST_FAFREGISTRATIONPRODUCT

CREATE procedure dbo.USP_DATALIST_FAFREGISTRATIONPRODUCT
    (
        @EVENTID uniqueidentifier, 
        @EVENTPRICEID uniqueidentifier = null,
        @ROLETYPE tinyint = 0 -- 0: Individual, 1: Team, 2: Company, 3:Head of household, 4:household members, 5:Team Members

    ) with execute as owner
as
    set nocount on;

    DECLARE @EVENTPRICELIST TABLE (EVENTPRICEID uniqueidentifier)

    IF @ROLETYPE = 0
    INSERT INTO @EVENTPRICELIST(EVENTPRICEID)
    SELECT EP.ID
    from dbo.EVENTPRICE EP
    inner join dbo.FAFREGISTRATIONTYPE ERT
    ON EP.ID = ERT.EVENTPRICEID
    WHERE EP.EVENTID = @EVENTID AND EP.ID = ISNULL(@EVENTPRICEID,EP.ID)
    AND ERT.ISREGTYPEAVAILABLEINDIVIDUAL = 1

    IF @ROLETYPE = 1
    INSERT INTO @EVENTPRICELIST(EVENTPRICEID)
    SELECT EP.ID
    from dbo.EVENTPRICE EP
    inner join dbo.FAFREGISTRATIONTYPE ERT
    ON EP.ID = ERT.EVENTPRICEID
    WHERE EP.EVENTID = @EVENTID AND EP.ID = ISNULL(@EVENTPRICEID,EP.ID)
    AND ERT.ISREGTYPEAVAILABLETEAMLEADER = 1

    IF @ROLETYPE = 2
    INSERT INTO @EVENTPRICELIST(EVENTPRICEID)
    SELECT EP.ID
    from dbo.EVENTPRICE EP
    inner join dbo.FAFREGISTRATIONTYPE ERT
    ON EP.ID = ERT.EVENTPRICEID
    WHERE EP.EVENTID = @EVENTID AND EP.ID = ISNULL(@EVENTPRICEID,EP.ID)
    AND ERT.ISREGTYPEAVAILABLECOMPANYLEADER = 1

    IF @ROLETYPE = 3
    INSERT INTO @EVENTPRICELIST(EVENTPRICEID)
    SELECT EP.ID
    from dbo.EVENTPRICE EP
    inner join dbo.FAFREGISTRATIONTYPE ERT
    ON EP.ID = ERT.EVENTPRICEID
    WHERE EP.EVENTID = @EVENTID AND EP.ID = ISNULL(@EVENTPRICEID,EP.ID)
    AND ERT.ISREGTYPEAVAILABLEHEADHOUSEHOLD = 1

    IF @ROLETYPE = 4
    INSERT INTO @EVENTPRICELIST(EVENTPRICEID)
    SELECT EP.ID
    from dbo.EVENTPRICE EP
    inner join dbo.FAFREGISTRATIONTYPE ERT
    ON EP.ID = ERT.EVENTPRICEID
    WHERE EP.EVENTID = @EVENTID AND EP.ID = ISNULL(@EVENTPRICEID,EP.ID)
    AND ERT.ISREGTYPEAVAILABLEFAMILYMEMBER = 1

    IF @ROLETYPE = 5
    INSERT INTO @EVENTPRICELIST(EVENTPRICEID)
    SELECT EP.ID
    from dbo.EVENTPRICE EP
    inner join dbo.FAFREGISTRATIONTYPE ERT
    ON EP.ID = ERT.EVENTPRICEID
    WHERE EP.EVENTID = @EVENTID AND EP.ID = ISNULL(@EVENTPRICEID,EP.ID)
    AND ERT.ISREGTYPEAVAILABLETEAMMEMBER = 1

    IF @ROLETYPE IN (4,5)
    SET @ROLETYPE = 0

    SELECT  
    EVENTPRICE.ID,
    EVENTPRICE.AMOUNT,
    EVENTPRICE.RECEIPTAMOUNT,
    EVENTPRICE.REGISTRATIONCOUNT,
    EVENTPRICE.NAME,
    EVENTREGISTRATIONTYPE.DESCRIPTION as REGISTRATIONTYPE,
    FAFREGISTRATIONTYPE.DESCRIPTION,
    FAFREGISTRATIONTYPE.MINIMUMAGE,
    FAFREGISTRATIONTYPE.MAXIMUMAGE,
    FAFREGISTRATIONTYPE.EARLYREGISTRATIONDATE,
    FAFREGISTRATIONTYPE.EARLYREGISTRATIONDISCOUNT,
    FAFREGISTRATIONTYPE.USEDQUANTITY,
    FAFREGISTRATIONTYPE.MAXQUANTITY,
    FAFREGISTRATIONTYPE.LATEFEEDATE,
    FAFREGISTRATIONTYPE.LATEFEE,
    FAFREGISTRATIONTYPE.TURNONWAITLIST as ISWAITLISTENABLED,
    FAFREGISTRATIONTYPE.WAITLISTMESSAGE,
    FAFREGISTRATIONTYPE.DISPLAYSPOTSAVAILABLE,
    FAFREGISTRATIONTYPE.ALLOWPARTICIPANTSWAIVEBENEFITS,

    (CASE WHEN @ROLETYPE = 3 THEN FAFREGISTRATIONTYPEGOAL.PARTICIPANTMUSTPAYBALANCE ELSE INDGOAL.PARTICIPANTMUSTPAYBALANCE END) as PARTICIPANTMUSTPAYBALANCE,
    (CASE WHEN @ROLETYPE = 3 THEN FAFREGISTRATIONTYPEGOAL.PARTICIPANTPAYSBALANCEMESSAGE ELSE INDGOAL.PARTICIPANTPAYSBALANCEMESSAGE END) as PARTICIPANTPAYSBALANCEMESSAGE,
  FAFREGISTRATIONTYPE.SENDEVENTCONTACTALERTMAXREACHED

from @EVENTPRICELIST EL
    inner join dbo.EVENTPRICE on EL.EVENTPRICEID = EVENTPRICE.ID
    inner join dbo.EVENTREGISTRATIONTYPE on EVENTREGISTRATIONTYPE.ID = EVENTPRICE.EVENTREGISTRATIONTYPEID
    inner join dbo.FAFREGISTRATIONTYPE on EVENTPRICE.ID = FAFREGISTRATIONTYPE.EVENTPRICEID AND FAFREGISTRATIONTYPE.ISACTIVE = 1
    inner join dbo.FAFREGISTRATIONTYPEGOAL ON FAFREGISTRATIONTYPEGOAL.EVENTPRICEID = EVENTPRICE.ID AND FAFREGISTRATIONTYPEGOAL.REGTYPEGOALCODE = @ROLETYPE
    inner join dbo.FAFREGISTRATIONTYPEGOAL INDGOAL ON INDGOAL.EVENTPRICEID = EVENTPRICE.ID AND INDGOAL.REGTYPEGOALCODE = 0
where                         
    FAFREGISTRATIONTYPE.ISNOTAVAILABLEONLINEREG = 0
order by
    REGISTRATIONTYPE,EVENTPRICE.AMOUNT,EVENTPRICE.NAME;