UFN_ACADEMICYEAR_INUSE

CREATE function dbo.UFN_ACADEMICYEAR_INUSE
(
    @ACADEMICYEARID uniqueidentifier
)
returns bit
with execute as caller
as begin
    declare @RETVAL bit = 0;
    --add any tables that use academic year dates


    --see if any sessions are in use

    if exists (select * from dbo.SESSION
                where 
                    SESSION.ACADEMICYEARID = @ACADEMICYEARID
                    and dbo.UFN_SESSION_INUSE(SESSION.ID) = 1)
        set @RETVAL = 1;



    --see if there are any student progressions using the academic year

    if @RETVAL = 0 and exists 
            (select * from dbo.STUDENTPROGRESSION 
            inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.[SCHOOLGRADELEVELID] = SCHOOLGRADELEVEL.[ID]
            where 
                dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOLGRADELEVEL.[SCHOOLID], STUDENTPROGRESSION.[STARTDATE], STUDENTPROGRESSION.[ENDDATE]) = @ACADEMICYEARID)
            set @RETVAL = 1;



    -- check for flat rate billing item using an academic year

    if @RETVAL = 0 and exists 
            (select * 
                from dbo.ACADEMICYEAR 
                inner join (select 
                                STARTDATE, 
                                ENDDATE 
                            from 
                                dbo.BILLINGITEMPRICE 
                            where DATERANGETYPECODE = 2) as V1 on ACADEMICYEAR.BILLINGSTARTDATE = V1.STARTDATE and ACADEMICYEAR.BILLINGENDDATE = V1.ENDDATE 
                 where 
                    ID = @ACADEMICYEARID 
                    and not BILLINGSTARTDATE is null 
                    and not BILLINGENDDATE is null)    
        set @RETVAL = 1;



    -- check for flat rate billing item by grade level using an academic year

    if @RETVAL = 0 and exists 
            (select * 
                from dbo.ACADEMICYEAR 
                inner join (select 
                                STARTDATE, 
                                ENDDATE 
                            from 
                                dbo.BILLINGITEMDATES
                            where DATERANGETYPECODE = 2) as V1 on ACADEMICYEAR.BILLINGSTARTDATE = V1.STARTDATE and ACADEMICYEAR.BILLINGENDDATE = V1.ENDDATE 
                 where 
                    ID = @ACADEMICYEARID 
                    and not BILLINGSTARTDATE is null 
                    and not BILLINGENDDATE is null)    
        set @RETVAL = 1;

    -- check for student skills using an academic year

  if @RETVAL = 0 and exists
          (select * 
            from dbo.STUDENTSKILLCOURSE
            inner join dbo.COURSE on STUDENTSKILLCOURSE.COURSEID = COURSE.ID
            inner join dbo.ACADEMICYEAR on (COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID) and
              ((STUDENTSKILLCOURSE.STARTDATE = ACADEMICYEAR.STARTDATE) or (STUDENTSKILLCOURSE.ENDDATE = ACADEMICYEAR.ENDDATE))
            where ACADEMICYEAR.ID = @ACADEMICYEARID)
    set @RETVAL = 1;

    return @RETVAL;
end