USP_ACADEMICCATALOG_GETLIST_WITHPARAMETERS

CREATE procedure dbo.USP_ACADEMICCATALOG_GETLIST_WITHPARAMETERS
(
    @NAME nvarchar(150) = null,
    @LOOKUPID nvarchar(50) = null,
    @TYPECODE tinyint = null,
    @DATEFROM as dbo.UDT_FUZZYDATE = null
)
as
begin

    set @NAME = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@NAME,0,'\');
    set @LOOKUPID = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@LOOKUPID,0,'\');

    declare @UNFILTEREDRESULTS as table
    (
        ID uniqueidentifier,
        PROGRAMCODE tinyint,
        NAME nvarchar(150),
        LOOKUPID nvarchar(50),
        TYPECODE int,
        DATEFROM dbo.UDT_FUZZYDATE,
        HASHISTORY bit,
        PARENTID uniqueidentifier
    );

    insert into @UNFILTEREDRESULTS
    select
        INSTITUTION.ID,
        null,
        INSTITUTION.NAME,
        null,
        0, --Institution

        '00000000',
        cast(0 as bit),
        null
    from
        dbo.EDUCATIONALINSTITUTION as INSTITUTION
    where
        INSTITUTION.ISAFFILIATED = 1

    insert into @UNFILTEREDRESULTS        
    select
        PROGRAM.ID,
        PROGRAM.PROGRAMCODE,
        PROGRAM.PROGRAM,
        null,
        1, --Program

        '00000000',
        cast(0 as bit),
        PROGRAM.EDUCATIONALINSTITUTIONID
    from
        dbo.ACADEMICCATALOGPROGRAM as PROGRAM

    insert into @UNFILTEREDRESULTS
    select
        DEGREE.ID,
        null,
        DEGREE.NAME,
        DEGREE.USERID,
        2, --Degree

        DEGREE.DATEFROM,
        case when exists (select ID from dbo.ACADEMICCATALOGDEGREE where CURRENTID = DEGREE.ID) then cast(1 as bit) else cast(0 as bit) end,
        DEGREE.ACADEMICCATALOGPROGRAMID
    from
        dbo.ACADEMICCATALOGDEGREE as DEGREE
    where 
        DEGREE.ISHISTORICAL = 0

    insert into @UNFILTEREDRESULTS
    select
        COLLEGE.ID,
        null,
        COLLEGE.NAME,
        COLLEGE.USERID,
        3, --College

        COLLEGE.DATEFROM,
        case when exists (select ID from dbo.ACADEMICCATALOGCOLLEGE where CURRENTID = COLLEGE.ID) then cast(1 as bit) else cast(0 as bit) end,
        COLLEGE.ACADEMICCATALOGDEGREEID
    from
        dbo.ACADEMICCATALOGCOLLEGE as COLLEGE
    where 
        COLLEGE.ISHISTORICAL = 0

    insert into @UNFILTEREDRESULTS
    select
        DIVISION.ID,
        null,
        DIVISION.NAME,
        DIVISION.USERID,
        4, --Division

        DIVISION.DATEFROM,
        case when exists (select ID from dbo.ACADEMICCATALOGDIVISION where CURRENTID = DIVISION.ID) then cast(1 as bit) else cast(0 as bit) end,
        DIVISION.ACADEMICCATALOGCOLLEGEID
    from
        dbo.ACADEMICCATALOGDIVISION as DIVISION
    where
        DIVISION.ISHISTORICAL = 0

    insert into @UNFILTEREDRESULTS
    select
        DEPARTMENT.ID,
        null,
        DEPARTMENT.NAME,
        DEPARTMENT.USERID,
        5, --Department

        DEPARTMENT.DATEFROM,
        case when exists (select ID from dbo.ACADEMICCATALOGDEPARTMENT where CURRENTID = DEPARTMENT.ID) then cast(1 as bit) else cast(0 as bit) end,
        coalesce(DEPARTMENT.ACADEMICCATALOGCOLLEGEID, DEPARTMENT.ACADEMICCATALOGDIVISIONID)
    from
        dbo.ACADEMICCATALOGDEPARTMENT as DEPARTMENT
    where 
        DEPARTMENT.ISHISTORICAL = 0

    insert into @UNFILTEREDRESULTS
    select
        SUBDEPARTMENT.ID,
        null,
        SUBDEPARTMENT.NAME,
        SUBDEPARTMENT.USERID,
        6, --Sub department

        SUBDEPARTMENT.DATEFROM,
        case when exists (select ID from dbo.ACADEMICCATALOGSUBDEPARTMENT where CURRENTID = SUBDEPARTMENT.ID) then cast(1 as bit) else cast(0 as bit) end,
        SUBDEPARTMENT.ACADEMICCATALOGDEPARTMENTID 
    from
        dbo.ACADEMICCATALOGSUBDEPARTMENT as SUBDEPARTMENT
    where 
        SUBDEPARTMENT.ISHISTORICAL = 0

    insert into @UNFILTEREDRESULTS
    select
        DEGREETYPE.ID,
        null,
        dbo.UFN_ACADEMICCATALOGDEGREETYPECODE_GETDESCRIPTION(ACADEMICCATALOGDEGREETYPECODEID),
        DEGREETYPE.USERID,
        7, --Degree type

        '00000000',
        cast(0 as bit),
        coalesce(DEGREETYPE.ACADEMICCATALOGDEPARTMENTID, DEGREETYPE.ACADEMICCATALOGSUBDEPARTMENTID)
    from
        dbo.ACADEMICCATALOGDEGREETYPE as DEGREETYPE;

    with FILTEREDRESULTS_WITHANCESTORS (ID, PROGRAMCODE, NAME, LOOKUPID, TYPECODE, DATEFROM, HASHISTORY, PARENTID)
    as
    (
        select
            *
        from
            @UNFILTEREDRESULTS
        where
            (@NAME is null or NAME like @NAME)
            and
            (@LOOKUPID is null or LOOKUPID like @LOOKUPID)
            and
            (@TYPECODE is null or TYPECODE = @TYPECODE)
            and
            (@DATEFROM is null or DATEFROM = @DATEFROM)

        union all

        select
            PARENTS.*
        from
            FILTEREDRESULTS_WITHANCESTORS
        inner join
            @UNFILTEREDRESULTS as PARENTS on PARENTS.ID = FILTEREDRESULTS_WITHANCESTORS.PARENTID
    ),
    FILTEREDRESULTS_WITHDESCENDANTS (ID, PROGRAMCODE, NAME, LOOKUPID, TYPECODE, DATEFROM, HASHISTORY, PARENTID)
    as (
        select
            *
        from
            @UNFILTEREDRESULTS
        where
            (@NAME is null or NAME like @NAME)
            and
            (@LOOKUPID is null or LOOKUPID like @LOOKUPID)
            and
            (@TYPECODE is null or TYPECODE = @TYPECODE)
            and
            (@DATEFROM is null or DATEFROM = @DATEFROM)

        union all

        select
            CHILDREN.*
        from
            FILTEREDRESULTS_WITHDESCENDANTS
        inner join
            @UNFILTEREDRESULTS as CHILDREN on CHILDREN.PARENTID = FILTEREDRESULTS_WITHDESCENDANTS.ID 
    ),
    FILTEREDRESULTS (ID, PROGRAMCODE, NAME, LOOKUPID, TYPECODE, DATEFROM, HASHISTORY, PARENTID)
    as (
        select
            *
        from
            FILTEREDRESULTS_WITHANCESTORS

        union all

        select
            *
        from
            FILTEREDRESULTS_WITHDESCENDANTS
    )
    select distinct
        ID,
        PROGRAMCODE,
        NAME,
        LOOKUPID,
        TYPECODE,
        convert( nvarchar(8), DATEFROM , 112),
        HASHISTORY,
        PARENTID
    from    
        FILTEREDRESULTS
    order by 
        TYPECODE, NAME;

end