USP_DATAFORMTEMPLATE_VIEW_DAILYSALESMEMBERSHIPINFO

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALESMEMBERSHIPINFO
(
    @ID nvarchar(72),
    @DATALOADED bit = 0 output,
    @NUMBEROFMEMBERSALLOWED integer = null output,
    @NUMBEROFMEMBERS integer = null output,
    @NUMBEROFCARDSALLOWED integer = null output,
    @NUMBEROFCARDS integer = null output,
    @HASADDONS bit = null output,
    @HASLEVELCONFLICT bit = null output,
    @HASLEVELCONFLICTTOOMANYMEMBERS bit = null output,
    @HASLEVELCONFLICTTOOMANYCARDS bit = null output
)
as
    set nocount on;
    declare @CURRENTDATE date = getdate()

    declare @MEMBERSHIPID uniqueidentifier = left(@ID, 36)
    declare @MEMBERSHIPLEVELID uniqueidentifier;
    if len(@ID) = 72
        set @MEMBERSHIPLEVELID = right(@ID, 36)

    declare @LASTTRANSACTIONID uniqueidentifier;
    declare @NUMBEROFMEMBERADDONS tinyint = 0
    set @DATALOADED = 1

    select 
        top 1 @LASTTRANSACTIONID = ID 
    from 
        dbo.MEMBERSHIPTRANSACTION 
    where 
        MEMBERSHIPID = @MEMBERSHIPID 
        and REVENUESPLITID is not null
    order by 
        TRANSACTIONDATE desc, DATEADDED desc;

    declare @MEMBERSHIPPROGRAMADDONSTABLE table  (ADDONID uniqueidentifier, NUMBEROFADDONS integer, ADDONTYPECODE tinyint)
    insert into @MEMBERSHIPPROGRAMADDONSTABLE
    select
        ADDON.ID,
        ADDONTOTALS.QUANTITY,
        ADDON.ADDONTYPECODE
    from 
        dbo.MEMBERSHIP
    inner join 
        dbo.MEMBERSHIPADDON on MEMBERSHIP.ID = MEMBERSHIPADDON.MEMBERSHIPID
    left join 
        dbo.MEMBERSHIPPROGRAM MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
    left join 
        dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
    cross apply 
    (
        select
            MEMBERSHIPADDON.QUANTITY - MEMBERSHIPADDON.NUMCANCELLED as QUANTITY
    ) ADDONTOTALS
    where 
        MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = @LASTTRANSACTIONID and
        MEMBERSHIP.ID = @MEMBERSHIPID and 
        ADDONTOTALS.QUANTITY > 0 and
        @CURRENTDATE <= convert(date, MEMBERSHIPADDON.EXPIRATIONDATE)

    if exists
    (
        select
            1
        from 
            @MEMBERSHIPPROGRAMADDONSTABLE
        where NUMBEROFADDONS > 0
    )
    begin
        set @HASADDONS = 1

        select
            @NUMBEROFMEMBERADDONS = sum(NUMBEROFADDONS)    
        from 
            @MEMBERSHIPPROGRAMADDONSTABLE
        where 
            ADDONTYPECODE = 1
    end

    select 
        @NUMBEROFMEMBERS = count(*)
    from 
        dbo.UFN_MEMBERSHIP_GETACTIVEMEMBERS(@ID)

    if @MEMBERSHIPLEVELID is null
        select
            @NUMBEROFCARDSALLOWED = CARDSALLOWED,
            @NUMBEROFMEMBERSALLOWED = MEMBERSALLOWED
        from
            dbo.MEMBERSHIPLEVEL
        inner join 
            dbo.MEMBERSHIP on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
        where
            MEMBERSHIP.ID = @MEMBERSHIPID
    else
        select
            @NUMBEROFCARDSALLOWED = CARDSALLOWED,
            @NUMBEROFMEMBERSALLOWED = MEMBERSALLOWED
        from
            dbo.MEMBERSHIPLEVEL
        where
            MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID    

    select 
        @NUMBEROFCARDS = count(MEMBERSHIPCARD.ID)
    from 
        dbo.MEMBERSHIPCARD 
    inner join 
        dbo.MEMBER on MEMBER.ID = MEMBERSHIPCARD.MEMBERID
    where 
        MEMBER.MEMBERSHIPID = @MEMBERSHIPID
        and MEMBERSHIPCARD.STATUSCODE <> 2    
        and MEMBER.ISDROPPED = 0

    if (@NUMBEROFMEMBERS  > @NUMBEROFMEMBERSALLOWED + @NUMBEROFMEMBERADDONS)
        set @HASLEVELCONFLICTTOOMANYMEMBERS = 1

    if (@NUMBEROFCARDS > @NUMBEROFCARDSALLOWED)
        set @HASLEVELCONFLICTTOOMANYCARDS = 1

    if @HASLEVELCONFLICTTOOMANYMEMBERS = 1 or @HASLEVELCONFLICTTOOMANYCARDS = 1
        set @HASLEVELCONFLICT = 1

    return 0;