CREATE OR REPLACE PACKAGE admin_tools_package
IS
procedure show_missing_grades (p_start_date IN date DEFAULT ADD_MONTHS(SYSDATE,-12),p_end_date IN date DEFAULT SYSDATE);
FUNCTION count_classes_per_course (p_course_id IN classes.course_id%type) return number;
PROCEDURE show_class_offerings(p_start_date IN classes.start_date%type,p_end_date IN classes.start_date%type );
end admin_tools_package;

CREATE OR REPLACE PACKAGE BODY admin_tools_package IS
procedure show_missing_grades (p_start_date IN date DEFAULT ADD_MONTHS(SYSDATE,-12),p_end_date IN date DEFAULT SYSDATE)
as
cursor missing_grades_cur
is
select class_id,stu_id,status
from enrollments
where enrollment_date between p_start_date and p_end_date and
final_numeric_grade is null and final_letter_grade is null
order by enrollment_date;
begin
for v_rec in missing_grades_cur
loop
dbms_output.put_line('class_id '||v_rec.class_id||' student_id '||v_rec.stu_id||' status '||v_rec.status);
end loop;
end show_missing_grades;
FUNCTION compute_avarage_grade (p_class_id IN enrollments.class_id%type)
RETURN NUMBER
IS
v_avg enrollments.final_numeric_grade%type;
begin
select avg(final_numeric_grade)
into v_avg
from enrollments
where class_id=p_class_id;
RETURN NVL(v_avg,0));
end compute_avarage_garde;
FUNCTION count_classes_per_course (p_course_id IN classes.course_id%type)
RETURN NUMBER
IS
v_nr_c number(6);
begin
select count(class_id) into v_nr_c
from classes
where course_id=p_course_id;
RETURN v_nr_c;
end count_classes_per_course;
PROCEDURE show_class_offerings(p_start_date IN classes.start_date%type,p_end_date IN classes.start_date%type )
AS
cursor show_c_o_cur is
select cl.class_id, cl.start_date, a.first_name, a.last_name, cl.course_id, c.title, c.section_code
from classes cl join instructors a on cl.instr_id=a.instructor_id join courses c on cl.course_id=c.course_id
where cl.start_date between p_start_date and p_end_date;
begin
for show_c_o_rec in show_c_o_cur
loop
dbms_output.put_line(show_c_o_rec.class_id||' '||show_c_o_rec.start_date||' '||show_c_o_rec.first_name||' ' ||show_c_o_rec.last_name||' '||show_c_o_rec.title||' '||show_c_o_rec.section_code||compute_avarage_grade(show_c_o_rec.course_id));
end loop;
end show_class_offerings;
end admin_tools_package;
