set scan off set echo on -- Created for Loyola Marymount University -- By Robert M. Nitsos -- 17-JUN-2004 -- -- This package creates Banner Web pages that displays a -- student's schedule by day and time in the Faculty -- module. -- -- It is essentially a copy of bwskfsh1.sql which was modified -- to run for faculty members. -- create or replace package lmubwfkssdt is PROCEDURE p_crseschd ( term_in IN stvterm.stvterm_code%TYPE DEFAULT NULL, STUPIDM IN number DEFAULT NULL, start_date_in IN VARCHAR2 DEFAULT NULL, end_date_in IN VARCHAR2 DEFAULT NULL); END lmubwfkssdt; / show errors CREATE OR REPLACE PACKAGE BODY lmubwfkssdt AS NEWLINE constant varchar2(1) := ' '; curr_release CONSTANT VARCHAR2 (10) := '5.6'; global_pidm spriden.spriden_pidm%TYPE; -- -- F_TIME_SLOT_ROUND. -- Round a time to the nearest time slot. -- ================================================== FUNCTION f_time_slot_round (time_in VARCHAR2, slot_length_in NUMBER) RETURN VARCHAR2 IS temp_date DATE; minutes_rounded_to_slot NUMBER; minutes_per_day CONSTANT NUMBER := 1440; lnew_time VARCHAR2 (4); BEGIN temp_date := TO_DATE ('01-JAN-99' || time_in, 'DD-MON-YYHH24MI'); temp_date := TRUNC (temp_date, 'HH24'); minutes_rounded_to_slot := ROUND (TO_NUMBER (SUBSTR (time_in, 3, 2)) / slot_length_in) * slot_length_in; temp_date := temp_date + (minutes_rounded_to_slot / minutes_per_day); lnew_time := TO_CHAR (temp_date, 'HH24MI'); RETURN lnew_time; END f_time_slot_round; -- -- P_CALC_ROWSPAN. -- Convert time differences to number of slots. -- ================================================== PROCEDURE p_calc_rowspan ( class_begin_time_in IN VARCHAR2, class_end_time_in IN VARCHAR2, slot_length_in IN INTEGER, class_rowspan_inout IN OUT INTEGER, class_begin_slot_inout IN OUT INTEGER, class_end_slot_inout IN OUT INTEGER ) IS rounded_begin_time VARCHAR2 (4); rounded_end_time VARCHAR2 (4); begin_hr NUMBER; begin_mi NUMBER; end_hr NUMBER; end_mi NUMBER; begin_minutes NUMBER; end_minutes NUMBER; BEGIN -- -- Work with times that are rounded to the nearest slot. -- ================================================== rounded_begin_time := f_time_slot_round (class_begin_time_in, slot_length_in); rounded_end_time := f_time_slot_round (class_end_time_in, slot_length_in); -- -- Extract hours and minutes. -- ================================================== begin_hr := TO_NUMBER (SUBSTR (rounded_begin_time, 1, 2)); begin_mi := TO_NUMBER (SUBSTR (rounded_begin_time, 3, 2)); end_hr := TO_NUMBER (SUBSTR (rounded_end_time, 1, 2)); end_mi := TO_NUMBER (SUBSTR (rounded_end_time, 3, 2)); -- -- Calculate begin slot for the class. -- ================================================== begin_minutes := (begin_hr * 60) + begin_mi; class_begin_slot_inout := (begin_minutes / slot_length_in) + 1; -- -- Calculate number of slots used by class. -- ================================================== end_minutes := (end_hr * 60) + end_mi; class_rowspan_inout := (end_minutes - begin_minutes) / slot_length_in; -- -- Calculate end slot for the class. -- ================================================== class_end_slot_inout := class_begin_slot_inout + class_rowspan_inout - 1; END p_calc_rowspan; -- -- P_CRSESCHD. -- Prints the student schedule by day and time in a -- grid format. -- ================================================== PROCEDURE p_crseschd ( term_in IN stvterm.stvterm_code%TYPE DEFAULT NULL, STUPIDM IN number DEFAULT NULL, start_date_in IN VARCHAR2 DEFAULT NULL, end_date_in IN VARCHAR2 DEFAULT NULL ) IS -- -- Cursor to read meeting records. -- ================================================== CURSOR sfvstumc ( pidm_in NUMBER, term_in VARCHAR2, start_date_in DATE DEFAULT NULL, end_date_in DATE DEFAULT NULL ) IS SELECT sfvstum.* FROM sfvstum, stvrsts WHERE sfvstum_pidm = pidm_in AND sfvstum_term_code = term_in AND ( sfvstum_start_date BETWEEN start_date_in AND end_date_in OR sfvstum_end_date BETWEEN start_date_in AND end_date_in OR start_date_in BETWEEN sfvstum_start_date AND sfvstum_end_date OR end_date_in BETWEEN sfvstum_start_date AND sfvstum_end_date ) AND sfvstum_begin_time IS NOT NULL AND sfvstum_end_time IS NOT NULL AND ( sfvstum_mon_day IS NOT NULL OR sfvstum_tue_day IS NOT NULL OR sfvstum_wed_day IS NOT NULL OR sfvstum_thu_day IS NOT NULL OR sfvstum_fri_day IS NOT NULL OR sfvstum_sat_day IS NOT NULL OR sfvstum_sun_day IS NOT NULL ) AND stvrsts_code = sfvstum_rsts_code AND stvrsts_sb_print_ind = 'Y' ORDER BY sfvstum_begin_time, sfvstum_mon_day, sfvstum_tue_day, sfvstum_wed_day, sfvstum_thu_day, sfvstum_fri_day, sfvstum_sat_day, sfvstum_sun_day; -- -- Cursor to get weeks that have schedule changes in them. -- Use the next_day function to get the start of the week -- for start dates and the end of the week for end dates. -- Use a dynamic view to normalize the start and end dates, -- then use the sum function to denormalize and count the -- number of starts and ends in a week. -- ================================================== CURSOR sfvstum_weeks_c (pidm_in NUMBER, term_in VARCHAR2) IS SELECT sfvstum_mon_date, SUM (DECODE (sfvstum_tran_type, 'S', 1, 0)) start_count, SUM (DECODE (sfvstum_tran_type, 'E', 1, 0)) end_count FROM (SELECT sfvstum_start_date sfvstum_mon_date, 'S' sfvstum_tran_type FROM sfvstum, stvrsts WHERE sfvstum_pidm = pidm_in AND sfvstum_term_code = term_in AND sfvstum_begin_time IS NOT NULL AND sfvstum_end_time IS NOT NULL AND ( sfvstum_mon_day IS NOT NULL OR sfvstum_tue_day IS NOT NULL OR sfvstum_wed_day IS NOT NULL OR sfvstum_thu_day IS NOT NULL OR sfvstum_fri_day IS NOT NULL OR sfvstum_sat_day IS NOT NULL OR sfvstum_sun_day IS NOT NULL ) AND stvrsts_code = sfvstum_rsts_code AND stvrsts_sb_print_ind = 'Y' UNION ALL SELECT sfvstum_end_date, 'E' FROM sfvstum, stvrsts WHERE sfvstum_pidm = pidm_in AND sfvstum_term_code = term_in AND sfvstum_begin_time IS NOT NULL AND sfvstum_end_time IS NOT NULL AND ( sfvstum_mon_day IS NOT NULL OR sfvstum_tue_day IS NOT NULL OR sfvstum_wed_day IS NOT NULL OR sfvstum_thu_day IS NOT NULL OR sfvstum_fri_day IS NOT NULL OR sfvstum_sat_day IS NOT NULL OR sfvstum_sun_day IS NOT NULL ) AND stvrsts_code = sfvstum_rsts_code AND stvrsts_sb_print_ind = 'Y') GROUP BY sfvstum_mon_date ORDER BY sfvstum_mon_date; -- -- Classes that do not have meeting times in the current -- template. -- ================================================== CURSOR nonschdc ( pidm_in NUMBER, term_in VARCHAR2, start_date_in DATE DEFAULT NULL, end_date_in DATE DEFAULT NULL ) IS SELECT DISTINCT ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_crn FROM ssbsect, sfrstcr, stvrsts WHERE sfrstcr_pidm = pidm_in AND sfrstcr_term_code = term_in AND ssbsect_term_code = sfrstcr_term_code AND ssbsect_crn = sfrstcr_crn AND NOT EXISTS (SELECT 1 FROM sfvstum WHERE sfvstum_crn = sfrstcr_crn AND sfvstum_pidm = sfrstcr_pidm AND sfvstum_term_code = sfrstcr_term_code AND ( sfvstum_start_date BETWEEN start_date_in AND end_date_in OR sfvstum_end_date BETWEEN start_date_in AND end_date_in OR start_date_in BETWEEN sfvstum_start_date AND sfvstum_end_date OR end_date_in BETWEEN sfvstum_start_date AND sfvstum_end_date ) AND sfvstum_begin_time IS NOT NULL AND sfvstum_end_time IS NOT NULL AND ( sfvstum_mon_day IS NOT NULL OR sfvstum_tue_day IS NOT NULL OR sfvstum_wed_day IS NOT NULL OR sfvstum_thu_day IS NOT NULL OR sfvstum_fri_day IS NOT NULL OR sfvstum_sat_day IS NOT NULL OR sfvstum_sun_day IS NOT NULL )) AND stvrsts_code = sfrstcr_rsts_code AND stvrsts_sb_print_ind = 'Y'; -- -- Record types. -- ================================================ TYPE slot_rec_type IS RECORD( mon_class INTEGER, mon_rowspan INTEGER, tue_class INTEGER, tue_rowspan INTEGER, wed_class INTEGER, wed_rowspan INTEGER, thu_class INTEGER, thu_rowspan INTEGER, fri_class INTEGER, fri_rowspan INTEGER, sat_class INTEGER, sat_rowspan INTEGER, sun_class INTEGER, sun_rowspan INTEGER); TYPE template_rec_type IS RECORD( start_date DATE, end_date DATE, start_end_count NUMBER); -- -- Table types. -- ================================================== TYPE sfvstum_tab_type IS TABLE OF sfvstum%ROWTYPE INDEX BY BINARY_INTEGER; TYPE conflict_tab_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; TYPE slot_tab_type IS TABLE OF slot_rec_type INDEX BY BINARY_INTEGER; TYPE prev_time_tab_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; TYPE template_tab_type IS TABLE OF template_rec_type INDEX BY BINARY_INTEGER; -- -- Tables. -- ================================================== sfvstum_tab sfvstum_tab_type; conflict_tab conflict_tab_type; slot_tab slot_tab_type; prev_time_tab prev_time_tab_type; prev_time_tab2 prev_time_tab_type; template_tab template_tab_type; -- -- Variables/Constants. -- ================================================== row_count INTEGER; stvterm_rec stvterm%ROWTYPE; sorrtrm_rec sorrtrm%ROWTYPE; lname VARCHAR2 (60); min_time VARCHAR2 (4); max_time VARCHAR2 (4); template_start_date DATE; template_end_date DATE; min_hh NUMBER; max_hh NUMBER; min_mi NUMBER; max_mi NUMBER; hold_date DATE; prior_start_date DATE; prior_end_date DATE; next_start_date DATE; next_end_date DATE; term_start_date DATE; term_end_date DATE; no_schd BOOLEAN; tot_credit_hr NUMBER; tot_bill_hr NUMBER; tot_ceu NUMBER; term stvterm.stvterm_code%TYPE; slots_in_hour CONSTANT INTEGER := 4; slot_length CONSTANT INTEGER := 60 / slots_in_hour; class_rowspan INTEGER; class_begin_slot INTEGER; class_end_slot INTEGER; current_class INTEGER; current_rowspan INTEGER; min_slot INTEGER; max_slot INTEGER; current_hour INTEGER; building VARCHAR2 (80); room sfvstum.sfvstum_room_code%TYPE; conflict_exists BOOLEAN; full_day VARCHAR2 (10); crn_dump VARCHAR2 (250); template_index INTEGER; today CONSTANT DATE := TRUNC ( SYSDATE ); currency_label VARCHAR2 (50); start_end_count NUMBER; target_date DATE; skipped_blank_template BOOLEAN; currently_viewing BOOLEAN; something_put_out BOOLEAN := FALSE; hold_stupidm spriden.spriden_pidm%type default null; hold_stupidm_char varchar2(30) DEFAULT NULL; qm varchar2(1) := '?'; amp varchar2(1) := '&'; msg VARCHAR2 (255); -- BEGIN -- -- Check for valid user. -- ================================================== /* Check for valid user */ IF NOT twbkwbis.F_ValidUser (global_pidm) THEN RETURN; END IF; /* set paramater to indicate we are a faculty user */ twbkwbis.P_SetParam (global_pidm, 'STUFAC_IND', 'FAC'); /* If stupidm has not been passed as a param, then try to get */ /* it from the general table, gorwprm */ IF STUPIDM IS NULL THEN hold_stupidm_char := twbkwbis.F_GetParam (global_pidm, 'STUPIDM'); /* Otherwise, store the value of the param in GORWPRM */ ELSE twbkwbis.P_SetParam (global_pidm, 'STUPIDM', TO_CHAR (STUPIDM, '999999999')); bwlkoids.P_FacResetPin (global_pidm); hold_stupidm := STUPIDM; END IF; /* If stupidm came from the table, then change it to a number */ IF hold_stupidm_char IS NOT NULL THEN hold_stupidm := TO_NUMBER (hold_stupidm_char, '999999999'); END IF; /* Make sure a student PIDM has been selected */ IF hold_stupidm IS NULL THEN bwlkoids.P_FacIDSel (term, 'lmubwfkssdt.P_CrseSchd', 'lmubwfkssdt.P_CrseSchd'); RETURN; END IF; -- /* If you came from the menu, try to select the term from the */ /* general table, gorwprm, by using F_GetParam. */ IF term_in IS NULL THEN term := TWBKWBIS.F_GetParam (global_pidm, 'TERM'); /* otherwise, you came from P_FacSelTerm, and need to write */ /* the param to the gorwprm table and set your local, hold_term */ ELSE TWBKWBIS.P_SetParam (global_pidm, 'TERM', term_in); term := term_in; END IF; /* Make sure a term has been selected */ IF term IS NULL THEN bwlkostm.P_FacSelTerm (calling_proc_name => 'lmubwfkssdt.P_CrseSchd'); RETURN; END IF; -- -- -- Start the web page. -- ================================================== twbkwbis.p_opendoc ( 'lmubwfkssdt.P_CrseSchd', header_text => stvterm_rec.stvterm_desc ); /* If the user is not a valid faculty member for the selected */ /* term, print a message, close the page, and exit. */ IF NOT bwlkilib.F_ValidFac (term, global_pidm) THEN msg := G$_NLS.Get ( 'BWLKFST1-0019', 'SQL', 'You must be a valid faculty member for the selected term to access this page.' ); twbkfrmt.p_printmessage (msg, 'ERROR'); twbkwbis.p_closedoc (curr_release); RETURN; END IF; -- -- Display info text. -- ================================================== twbkwbis.p_dispinfo ('lmubwfkssdt.P_CrseSchd', 'DEFAULT'); bwcklibs.P_ConfidStudInfo (hold_stupidm, term); twbkwbis.P_DispInfo ('lmubwfkssdt.P_CrseSchd', 'CONFID'); -- -- Populate a pl/sql table with the start and end dates -- for each template. A template is a frame of time when the -- user's schedule remains static. A template starts on the -- week that a class starts or the week after a class ends. -- The template end dates can be derived as one week before -- the start dates. -- ================================================== start_end_count := 0; template_index := 0; <> FOR sfvstum_weeks_rec IN sfvstum_weeks_c (hold_stupidm, term) LOOP template_index := template_index + 1; -- -- Populate the template start date. -- ================================================== template_tab (template_index).start_date := sfvstum_weeks_rec.sfvstum_mon_date; -- -- Populate the template end date. Also, keep track of -- term start and end dates. -- ================================================== IF template_index > 1 THEN IF sfvstum_weeks_rec.end_count > 0 THEN template_tab (template_index).start_date := sfvstum_weeks_rec.sfvstum_mon_date + 1; ELSE template_tab (template_index).start_date := sfvstum_weeks_rec.sfvstum_mon_date; END IF; IF sfvstum_weeks_rec.start_count > 0 THEN template_tab (template_index - 1).end_date := sfvstum_weeks_rec.sfvstum_mon_date - 1; ELSE template_tab (template_index - 1).end_date := sfvstum_weeks_rec.sfvstum_mon_date; END IF; term_end_date := sfvstum_weeks_rec.sfvstum_mon_date; ELSE term_start_date := sfvstum_weeks_rec.sfvstum_mon_date; END IF; -- -- Add and subtract starts and ends. If count = 0 for -- a template, then there are no classes during that template. -- ================================================== start_end_count := start_end_count + sfvstum_weeks_rec.start_count - sfvstum_weeks_rec.end_count; template_tab (template_index).start_end_count := start_end_count; END LOOP load_templates_loop; -- -- Delete the last entry in the template pl/sql table -- because it is the week after all classes end. -- ================================================== template_tab.DELETE (template_index); -- -- Establish the start and end dates for the current -- template. -- ================================================== IF start_date_in IS NOT NULL THEN template_start_date := TO_DATE (start_date_in, 'DD-MM-YYYY'); template_end_date := TO_DATE (end_date_in, 'DD-MM-YYYY'); ELSE -- -- If today is outside the term, use term start as -- template start date. If today is inside term, get -- the current template date range from the pl/sql -- table. -- ================================================== IF today NOT BETWEEN term_start_date AND term_end_date THEN target_date := term_start_date; ELSE target_date := today; END IF; skipped_blank_template := FALSE; <> FOR template_index IN 1 .. NVL (template_tab.LAST, 0) LOOP IF target_date BETWEEN template_tab (template_index).start_date AND template_tab (template_index).end_date OR skipped_blank_template THEN IF template_tab (template_index).start_end_count = 0 OR template_tab (template_index).start_date > template_tab (template_index).end_date THEN skipped_blank_template := TRUE; ELSE template_start_date := template_tab (template_index).start_date; template_end_date := template_tab (template_index).end_date; skipped_blank_template := FALSE; END IF; END IF; END LOOP check_templates_loop; END IF; -- -- Loop through schedule records for the current template. -- ================================================== row_count := 0; <> FOR sfvstum_rec IN sfvstumc ( hold_stupidm, term, template_start_date, template_end_date ) LOOP row_count := row_count + 1; -- -- Keep track of the earliest and latest times for the -- current template for purposes of determining length -- of html table. -- ================================================== min_time := LEAST ( NVL (min_time, sfvstum_rec.sfvstum_begin_time), sfvstum_rec.sfvstum_begin_time ); max_time := GREATEST ( NVL (max_time, sfvstum_rec.sfvstum_end_time), sfvstum_rec.sfvstum_end_time ); min_time := f_time_slot_round (min_time, slot_length); max_time := f_time_slot_round (max_time, slot_length); -- -- Put the class record into an array. -- ================================================== sfvstum_tab (row_count) := sfvstum_rec; END LOOP read_classes_loop; IF row_count = 0 THEN no_schd := TRUE; GOTO non_schd; ELSE no_schd := FALSE; END IF; -- -- Begin display of schedule info. -- First display begin and end dates of template. -- ================================================== twbkfrmt.p_printtext ( TO_CHAR (template_start_date, twbklibs.date_display_fmt) || ' - ' || TO_CHAR (template_end_date, twbklibs.date_display_fmt) ); HTP.br; -- -- Calculate and display total credit hours for schedule -- by day and time. -- ================================================== SELECT SUM (DECODE (stvlevl_ceu_ind, 'Y', 0, NVL (sfrstcr_credit_hr, 0))) INTO tot_credit_hr FROM stvlevl, sfrstcr WHERE stvlevl_code = sfrstcr_levl_code AND sfrstcr_pidm = hold_stupidm AND sfrstcr_term_code = term AND ( ( sfrstcr_error_flag <> 'F' OR sfrstcr_error_flag IS NULL) OR ( sfrstcr_error_flag = 'F' AND SUBSTR (sfrstcr_message, 1, 4) = 'MAXI' ) ); twbkfrmt.p_printtext ( G$_NLS.Get ( 'BWSKFSH1-0015', 'SQL', 'Total Credit Hours: %01%', LTRIM (TO_CHAR (tot_credit_hr, '999D99')) ) ); htp.br; htp.br; -- -- Start the html table for the display of schedule. -- Print a row of column headers with day of week labels. -- ================================================== twbkfrmt.p_tableopen ( 'DATADISPLAY', cattributes => 'SUMMARY="' || g$_nls.get ( 'BWSKFSH1-0016', 'SQL', 'This layout table is used to present the course schedule by day and time' ) || '."', ccaption => g$_nls.get ('BWSKFSH1-0017', 'SQL', 'Course Schedule') ); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledataheader; twbkfrmt.p_tabledataheader ( amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;' || G$_NLS.Get ('BWSKFSH1-0021', 'SQL', 'Monday') || amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;',calign=>'center' ); twbkfrmt.p_tabledataheader ( amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;' || G$_NLS.Get ('BWSKFSH1-0022', 'SQL', 'Tuesday') || amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;',calign=>'center' ); twbkfrmt.p_tabledataheader ( amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;' || G$_NLS.Get ('BWSKFSH1-0023', 'SQL', 'Wednesday') || amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;',calign=>'center' ); twbkfrmt.p_tabledataheader ( amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;' || G$_NLS.Get ('BWSKFSH1-0023', 'SQL', 'Thursday') || amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;',calign=>'center' ); twbkfrmt.p_tabledataheader ( amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;' || G$_NLS.Get ('BWSKFSH1-0023', 'SQL', 'Friday') || amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;',calign=>'center' ); twbkfrmt.p_tabledataheader ( amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;' || G$_NLS.Get ('BWSKFSH1-0023', 'SQL', 'Saturday') || amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;',calign=>'center' ); twbkfrmt.p_tabledataheader ( amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;' || G$_NLS.Get ('BWSKFSH1-0023', 'SQL', 'Sunday') || amp ||'nbsp;' || amp ||'nbsp;' || amp ||'nbsp;',calign=>'center' ); twbkfrmt.p_tablerowclose; -- -- Extract min/max hours from min/max time. -- Convert to min/max slots. -- ================================================== min_hh := TO_NUMBER (SUBSTR (min_time, 1, 2)); min_mi := TO_NUMBER (SUBSTR (min_time, 3, 2)); max_hh := TO_NUMBER (SUBSTR (max_time, 1, 2)); max_mi := TO_NUMBER (SUBSTR (max_time, 3, 2)); min_slot := (min_hh * slots_in_hour) + 1; max_slot := ( (max_hh + SIGN (max_mi)) * slots_in_hour); -- -- Initialize slot table. -- ================================================== <> FOR slot_index IN 1 .. 24 * slots_in_hour LOOP slot_tab (slot_index).mon_class := NULL; slot_tab (slot_index).mon_rowspan := 1; slot_tab (slot_index).tue_class := NULL; slot_tab (slot_index).tue_rowspan := 1; slot_tab (slot_index).wed_class := NULL; slot_tab (slot_index).wed_rowspan := 1; slot_tab (slot_index).thu_class := NULL; slot_tab (slot_index).thu_rowspan := 1; slot_tab (slot_index).fri_class := NULL; slot_tab (slot_index).fri_rowspan := 1; slot_tab (slot_index).sat_class := NULL; slot_tab (slot_index).sat_rowspan := 1; slot_tab (slot_index).sun_class := NULL; slot_tab (slot_index).sun_rowspan := 1; END LOOP init_slots_loop; -- -- Initialize previous time table. -- ================================================== <> FOR day_index IN 1 .. 7 LOOP prev_time_tab (day_index) := 0; prev_time_tab2 (day_index) := 0; END LOOP init_prev_time_loop; -- -- Place classes in slot table. -- ================================================== <> FOR class_index IN 1 .. sfvstum_tab.LAST LOOP <> FOR day_index IN 1 .. 7 LOOP -- -- As we loop through the days, check if the current -- class is scheduled for the current day. -- ================================================== IF ( day_index = 1 AND sfvstum_tab (class_index).sfvstum_mon_day IS NOT NULL ) OR ( day_index = 2 AND sfvstum_tab (class_index).sfvstum_tue_day IS NOT NULL ) OR ( day_index = 3 AND sfvstum_tab (class_index).sfvstum_wed_day IS NOT NULL ) OR ( day_index = 4 AND sfvstum_tab (class_index).sfvstum_thu_day IS NOT NULL ) OR ( day_index = 5 AND sfvstum_tab (class_index).sfvstum_fri_day IS NOT NULL ) OR ( day_index = 6 AND sfvstum_tab (class_index).sfvstum_sat_day IS NOT NULL ) OR ( day_index = 7 AND sfvstum_tab (class_index).sfvstum_sun_day IS NOT NULL ) THEN -- -- Check if the current class will overlap a class that -- is already in the slot table. If so, put an entry in -- the conflict table and do not put an entry in the -- slot table. -- ================================================== conflict_exists := FALSE; IF sfvstum_tab.EXISTS (prev_time_tab (day_index)) THEN IF sfvstum_tab (class_index).sfvstum_begin_time BETWEEN sfvstum_tab ( prev_time_tab ( day_index ) ).sfvstum_begin_time AND sfvstum_tab (prev_time_tab (day_index)).sfvstum_end_time THEN conflict_tab (class_index) := prev_time_tab (day_index); conflict_exists := TRUE; END IF; END IF; IF NOT conflict_exists AND sfvstum_tab.EXISTS (prev_time_tab2 (day_index)) THEN IF sfvstum_tab (class_index).sfvstum_begin_time BETWEEN sfvstum_tab ( prev_time_tab2 ( day_index ) ).sfvstum_begin_time AND sfvstum_tab (prev_time_tab2 (day_index)).sfvstum_end_time THEN conflict_tab (class_index) := prev_time_tab2 (day_index); conflict_exists := TRUE; END IF; END IF; IF NOT conflict_exists THEN p_calc_rowspan ( sfvstum_tab (class_index).sfvstum_begin_time, sfvstum_tab (class_index).sfvstum_end_time, slot_length, class_rowspan, class_begin_slot, class_end_slot ); -- -- Loop through the slots that are spanned by the -- current class and make entries in the slot table. -- For the first slot, set the rowspan to the number -- of slots taken up by the class. For subsequent slots -- set the rowspan to zero. -- ================================================== <> FOR class_slot_index IN class_begin_slot .. class_end_slot LOOP IF class_slot_index = class_begin_slot THEN IF day_index = 1 THEN slot_tab (class_slot_index).mon_class := class_index; slot_tab (class_slot_index).mon_rowspan := class_rowspan; ELSIF day_index = 2 THEN slot_tab (class_slot_index).tue_class := class_index; slot_tab (class_slot_index).tue_rowspan := class_rowspan; ELSIF day_index = 3 THEN slot_tab (class_slot_index).wed_class := class_index; slot_tab (class_slot_index).wed_rowspan := class_rowspan; ELSIF day_index = 4 THEN slot_tab (class_slot_index).thu_class := class_index; slot_tab (class_slot_index).thu_rowspan := class_rowspan; ELSIF day_index = 5 THEN slot_tab (class_slot_index).fri_class := class_index; slot_tab (class_slot_index).fri_rowspan := class_rowspan; ELSIF day_index = 6 THEN slot_tab (class_slot_index).sat_class := class_index; slot_tab (class_slot_index).sat_rowspan := class_rowspan; ELSIF day_index = 7 THEN slot_tab (class_slot_index).sun_class := class_index; slot_tab (class_slot_index).sun_rowspan := class_rowspan; END IF; ELSE IF day_index = 1 THEN slot_tab (class_slot_index).mon_rowspan := 0; ELSIF day_index = 2 THEN slot_tab (class_slot_index).tue_rowspan := 0; ELSIF day_index = 3 THEN slot_tab (class_slot_index).wed_rowspan := 0; ELSIF day_index = 4 THEN slot_tab (class_slot_index).thu_rowspan := 0; ELSIF day_index = 5 THEN slot_tab (class_slot_index).fri_rowspan := 0; ELSIF day_index = 6 THEN slot_tab (class_slot_index).sat_rowspan := 0; ELSIF day_index = 7 THEN slot_tab (class_slot_index).sun_rowspan := 0; END IF; END IF; END LOOP span_class_loop; END IF; IF prev_time_tab (day_index) = 0 THEN prev_time_tab (day_index) := class_index; ELSE prev_time_tab2 (day_index) := class_index; END IF; END IF; END LOOP place_classes_day_loop; END LOOP place_classes_loop; -- -- Print slot table. -- ================================================== <> FOR slot_index IN min_slot .. max_slot LOOP something_put_out := FALSE; twbkfrmt.p_tablerowopen; IF slot_index = min_slot OR MOD (slot_index - 1, slots_in_hour) = 0 THEN -- -- Print the current hour in an element that spans the -- number of rows indicated by slots_in_hour. Add AM -- or PM. -- ================================================== current_hour := FLOOR ( (slot_index - 1) / slots_in_hour); IF current_hour < 12 THEN twbkfrmt.p_tabledatalabel ( G$_NLS.Get ( 'BWSKFSH1-0029', 'SQL', '%01%am', LTRIM (TO_NUMBER (current_hour, 99)) ), crowspan => LTRIM (TO_CHAR (slots_in_hour)) ); ELSE IF current_hour = 12 THEN twbkfrmt.p_tabledatalabel ( G$_NLS.Get ('BWSKFSH1-0032', 'SQL', '12pm'), crowspan => LTRIM (TO_CHAR (slots_in_hour)) ); ELSE twbkfrmt.p_tabledatalabel ( G$_NLS.Get ( 'BWSKFSH1-0034', 'SQL', '%01%pm', LTRIM (TO_NUMBER (current_hour - 12, 99)) ), crowspan => LTRIM (TO_CHAR (slots_in_hour)) ); END IF; END IF; END IF; -- -- Loop through the days. -- ================================================== <> FOR day_index IN 1 .. 7 LOOP IF day_index = 1 THEN current_class := slot_tab (slot_index).mon_class; current_rowspan := slot_tab (slot_index).mon_rowspan; ELSIF day_index = 2 THEN current_class := slot_tab (slot_index).tue_class; current_rowspan := slot_tab (slot_index).tue_rowspan; ELSIF day_index = 3 THEN current_class := slot_tab (slot_index).wed_class; current_rowspan := slot_tab (slot_index).wed_rowspan; ELSIF day_index = 4 THEN current_class := slot_tab (slot_index).thu_class; current_rowspan := slot_tab (slot_index).thu_rowspan; ELSIF day_index = 5 THEN current_class := slot_tab (slot_index).fri_class; current_rowspan := slot_tab (slot_index).fri_rowspan; ELSIF day_index = 6 THEN current_class := slot_tab (slot_index).sat_class; current_rowspan := slot_tab (slot_index).sat_rowspan; ELSIF day_index = 7 THEN current_class := slot_tab (slot_index).sun_class; current_rowspan := slot_tab (slot_index).sun_rowspan; END IF; -- -- If current_class is null and current rowspan = 1, -- then just print a table data element. Else, if rowspan -- is greater than zero, print the class info. -- ================================================== IF current_class IS NULL AND current_rowspan = 1 THEN twbkfrmt.p_tabledata; something_put_out := TRUE; ELSIF current_rowspan > 0 THEN something_put_out := TRUE; -- -- Check for building code. -- ================================================== IF sfvstum_tab (current_class).sfvstum_bldg_code IS NOT NULL THEN building := sfvstum_tab (current_class).sfvstum_bldg_code; room := sfvstum_tab (current_class).sfvstum_room_code; ELSE building := '' || g$_nls.get ('BWSKFSH1-0037', 'SQL', 'TBA') || ''; room := NULL; END IF; -- -- Print class info. -- ================================================== twbkfrmt.p_tabledatalabel ( twbkfrmt.f_printanchor ( twbkfrmt.f_encodeurl ( twbkwbis.f_cgibin || 'bwlkfstu.P_FacStuSchd' || '?stupidm=' || hold_stupidm || amp || 'term=' || term || amp || 'crn=' || sfvstum_tab (current_class).sfvstum_crn ), sfvstum_tab (current_class).sfvstum_subj_code || ' ' || sfvstum_tab (current_class).sfvstum_crse_numb || '-' || sfvstum_tab (current_class).sfvstum_seq_numb || HTF.br || building || ' ' || room || HTF.br || LTRIM ( TO_CHAR ( TO_DATE ( sfvstum_tab (current_class).sfvstum_begin_time, 'HH24MI' ), twbklibs.twgbwrul_rec.twgbwrul_time_fmt ), ' 0' ) || '-' || LTRIM ( TO_CHAR ( TO_DATE ( sfvstum_tab (current_class).sfvstum_end_time, 'HH24MI' ), twbklibs.twgbwrul_rec.twgbwrul_time_fmt ), ' 0' ) ), crowspan => current_rowspan ); END IF; END LOOP print_slots_day_loop; IF NOT something_put_out THEN twbkfrmt.p_tabledata; END IF; twbkfrmt.p_tablerowclose; END LOOP print_slots_loop; -- -- Close the table. -- ================================================== twbkfrmt.p_tableclose; -- -- Print classes not within date range or without -- assigned times. -- ================================================== <> row_count := 0; <> FOR crse_rec IN nonschdc ( hold_stupidm, term, template_start_date, template_end_date ) LOOP row_count := nonschdc%rowcount; IF nonschdc%rowcount = 1 THEN HTP.br; twbkfrmt.p_printtext ( G$_NLS.Get ( 'BWSKFSH1-0044', 'SQL', 'Courses not within date range or without assigned times' ) || ':' ); HTP.br; END IF; twbkfrmt.p_printanchor ( twbkfrmt.f_encodeurl ( twbkwbis.f_cgibin || 'bwlkfstu.P_FacStuSchd' || '?stupidm=' || hold_stupidm || amp || 'term=' || term || amp || 'crn=' || crse_rec.sfrstcr_crn ), crse_rec.ssbsect_subj_code || ' ' || crse_rec.ssbsect_crse_numb || '-' || crse_rec.ssbsect_seq_numb ); HTP.br; END LOOP print_nonscheduled_loop; IF row_count > 0 THEN no_schd := FALSE; END IF; row_count := 0; -- -- Print classes with time conflicts. -- ================================================== crn_dump := ','; <> FOR conflict_index IN 1 .. NVL (conflict_tab.LAST, 0) LOOP IF conflict_tab.EXISTS (conflict_index) THEN IF conflict_tab (conflict_index) <> 0 AND INSTR (crn_dump, sfvstum_tab (conflict_index).sfvstum_crn) = 0 THEN crn_dump := crn_dump || sfvstum_tab (conflict_index).sfvstum_crn || ','; row_count := row_count + 1; IF row_count = 1 THEN HTP.br; twbkfrmt.p_printtext ( G$_NLS.Get ( 'BWSKFSH1-0046', 'SQL', 'Courses with time conflict' ) || ':' ); HTP.br; END IF; twbkfrmt.p_printanchor ( twbkfrmt.f_encodeurl ( twbkwbis.f_cgibin || 'bwlkfstu.P_FacStuSchd' || '?stupidm=' || hold_stupidm || amp || 'term=' || term || amp || 'crn=' || sfvstum_tab (conflict_index).sfvstum_crn ), sfvstum_tab (conflict_index).sfvstum_subj_code || ' ' || sfvstum_tab (conflict_index).sfvstum_crse_numb || '-' || sfvstum_tab (conflict_index).sfvstum_seq_numb ); HTP.br; END IF; END IF; END LOOP conflict_loop; IF row_count > 0 THEN no_schd := FALSE; END IF; -- -- Display links to other templates. -- ================================================== <> FOR template_index IN 1 .. NVL (template_tab.LAST, 0) LOOP -- -- If it's not an empty template... -- ================================================== IF template_tab (template_index).start_end_count <> 0 AND template_tab (template_index).start_date <= template_tab (template_index).end_date THEN -- -- Open a table to format the links into neat rows. -- ================================================== IF template_index = 1 THEN HTP.br; twbkfrmt.p_tableopen ( 'DATADISPLAY', cattributes => 'SUMMARY="' || g$_nls.get ( 'BWSKFSH1-0051', 'SQL', 'This layout table is used to present links to the other date ranges for this course schedule.' ) || '."', ccaption => g$_nls.get ( 'BWSKFSH1-0047', 'SQL', 'Other Date Ranges' ) ); END IF; twbkfrmt.p_tablerowopen; -- -- Check if the next template link pertains to the -- template that is currently on display. -- ================================================== currency_label := NULL; currently_viewing := FALSE; IF template_start_date = template_tab (template_index).start_date AND template_tab.LAST > 1 THEN currency_label := G$_NLS.Get ('BWSKFSH1-0048', 'SQL', 'Currently Displayed'); currently_viewing := TRUE; END IF; -- -- Check if the next template link pertains to today's template. -- ================================================== IF today BETWEEN template_tab (template_index).start_date AND template_tab (template_index).end_date THEN IF currently_viewing THEN currency_label := G$_NLS.Get ( 'BWSKFSH1-0049', 'SQL', '%01%, Today''s Schedule', currency_label ); ELSE currency_label := G$_NLS.Get ('BWSKFSH1-0050', 'SQL', 'Today''s Schedule'); END IF; END IF; IF currency_label IS NOT NULL THEN currency_label := '(' || currency_label || ')'; END IF; IF currently_viewing THEN twbkfrmt.p_tabledataopen; twbkfrmt.p_printtext ( text => G$_NLS.Get ( 'BWSKFSH1-0052', 'SQL', 'Schedule between %01% and %02%', TO_CHAR ( template_tab (template_index).start_date, twbklibs.date_display_fmt ), TO_CHAR ( template_tab (template_index).end_date, twbklibs.date_display_fmt ) ) ); twbkfrmt.p_tabledataclose; ELSE twbkfrmt.p_tabledataopen; twbkfrmt.p_printanchor ( twbkfrmt.f_encodeurl ( twbkwbis.f_cgibin || 'lmubwfkssdt.P_CrseSchd' || '?stupidm=' || hold_stupidm || amp || 'term_in=' || term || amp || 'start_date_in=' || TO_CHAR ( template_tab (template_index).start_date, 'DD-MM-YYYY' ) || amp || 'end_date_in=' || TO_CHAR ( template_tab (template_index).end_date, 'DD-MM-YYYY' ) ), ctext => G$_NLS.Get ( 'BWSKFSH1-0053', 'SQL', 'Schedule between %01% and %02%', TO_CHAR ( template_tab (template_index).start_date, twbklibs.date_display_fmt ), TO_CHAR ( template_tab (template_index).end_date, twbklibs.date_display_fmt ) ) ); twbkfrmt.p_tabledataclose; END IF; twbkfrmt.p_tabledataopen; twbkfrmt.p_printtext (currency_label); twbkfrmt.p_tabledataclose; twbkfrmt.p_tablerowclose; IF template_index = template_tab.LAST THEN twbkfrmt.p_tableclose; END IF; END IF; HTP.br; END LOOP display_template_loop; -- -- Handle no schedule. -- ================================================== IF no_schd THEN twbkfrmt.p_printmessage ( G$_NLS.Get ( 'BWSKFSH1-0063', 'SQL', 'You are not currently registered for the term' ), 'ERROR' ); END IF; -- -- Close out. -- ================================================== HTP.br; twbkwbis.p_closedoc (curr_release); END p_crseschd; end lmubwfkssdt; / show errors whenever sqlerror continue; drop public synonym lmubwfkssdt; whenever sqlerror exit rollback; create public synonym lmubwfkssdt for lmubwfkssdt; grant execute on lmubwfkssdt to WWW_USER; set scan on