UFN_SPONSORSHIPOPPORTUNITY_CALCAVAILABILITY_WEB

CREATE function [dbo].[UFN_SPONSORSHIPOPPORTUNITY_CALCAVAILABILITY_WEB](
    @ID uniqueidentifier
)
returns tinyint
with execute as caller
as begin
    -- NOTE:  This program calculates availability based on the program and sponsorships.

    -- It does NOT currently consider location status.


    declare @SPONSORCOUNT int
    declare @SOLESPONSOR bit
    declare @SPONSORSPEROPPORTUNITY int

    select    @SPONSORCOUNT=count(SPONSORSHIP.ID),
                        @SOLESPONSOR=max(isnull(cast(ISSOLESPONSORSHIP as tinyint),0)),
                        @SPONSORSPEROPPORTUNITY=min(dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(SPONSORSHIPOPPORTUNITYGROUP.ID,SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID))
    from dbo.SPONSORSHIPOPPORTUNITY
        inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
        left outer join dbo.SPONSORSHIP on SPONSORSHIP.SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY.ID and STATUSCODE in(0,1)
    where SPONSORSHIPOPPORTUNITY.ID = @ID

    DECLARE @CURRENTSALESORDERITEMCOUNT int

    select @CURRENTSALESORDERITEMCOUNT = COUNT(SALESORDERITEMSPONSORSHIP.ID) from SALESORDERITEMSPONSORSHIP inner join SALESORDERRESERVEDITEM on
    SALESORDERITEMSPONSORSHIP.ID = SALESORDERRESERVEDITEM.ID and SALESORDERITEMSPONSORSHIP.SPONSORSHIPOPPORTUNITYID = @ID
    where (SALESORDERRESERVEDITEM.EXPIRATIONDATE > CURRENT_TIMESTAMP)

    SET @SPONSORCOUNT = @SPONSORCOUNT + @CURRENTSALESORDERITEMCOUNT 

    if (@SOLESPONSOR = 1 or @SPONSORCOUNT >= @SPONSORSPEROPPORTUNITY) and @SPONSORSPEROPPORTUNITY <> 0 --@SPONSORSPEROPPORTUNITY of 0 = unlimited

        return 2;

    return 0;

end