-- swrenrl.sql
--
-- Run CE Enrollment Report

set echo off
set term off
set verify off
set feedback off
set wrap off
set newpage 1
set pagesize 45
set linesize 152

alter session set nls_date_format = 'DD-MON-YYYY';

-- Get ONE_UP number to access parameters in GJBPRUN table

column PARAMTERM new_value TERM noprint
SELECT upper(gjbprun_value) PARAMTERM
  FROM gjbprun
 WHERE gjbprun_job = '&&JOB'
   and gjbprun_one_up_no = &&ONE_UP
   and gjbprun_number = '01';

column PARAMCONTROLLER new_value CONTROLLER noprint
SELECT upper(gjbprun_label) PARAMCONTROLLER
  FROM gjbprun
 WHERE gjbprun_job = '&&JOB'
   and gjbprun_one_up_no = &&ONE_UP
   and gjbprun_number = '91';

column PARAMSORT new_value SORT noprint
SELECT upper(gjbprun_value) PARAMSORT
  FROM gjbprun
 WHERE gjbprun_job = '&&JOB'
   and gjbprun_one_up_no = &&ONE_UP
   and gjbprun_number = '03';

column PARAMSTATUS new_value STATUS noprint
SELECT upper(gjbprun_value) PARAMSTATUS
  FROM gjbprun
 WHERE gjbprun_job = '&&JOB'
   and gjbprun_one_up_no = &&ONE_UP
   and gjbprun_number = '04';

column PARAMCAMPUS new_value CAMPUS noprint
SELECT upper(gjbprun_value) PARAMCAMPUS
  FROM gjbprun
 WHERE gjbprun_job = '&&JOB'
   and gjbprun_one_up_no = &&ONE_UP
   and gjbprun_number = '05';

ttitle center 'M o h a w k   C o l l e g e' -
       left   TTIME skip 1 -
       right   'Report: swrenrl.sql' -
       center 'C.E. Enrollment Report' -
       left   'Page:' format 999 sql.pno skip 1 -
       center  'Course Information for ' TTERM ' as of ' TDATE skip 3

--      left    TDATE skip 1 - 

col HDATE noprint new_value TDATE
col HTIME noprint new_value TTIME
col HTERM noprint new_value TTERM 
--col TOTAL_CRN noprint

col COURSE                   format a10        heading '  Course'
col TITLE                    format a15        heading '     Title'
col GP                       format a2         heading 'GP'	
col CAMPUS                                     heading 'Loc'
col DAY                      format a14        heading 'Day(s)'
col TOTAL_CURRENT_ENROLLMENT format 99999999      heading 'Cur|Enr'
col TOTAL_RESERVED           format 999        heading 'Res'
col TOTAL_ENROLLMENT         format 99999999      heading 'Total| Enr '
col ssbsect_max_enrl         format 99         heading 'Max|Enr'
col SCH                      format 9999       heading ' SCH '
col swvfees_amount           format 9999.99    heading 'Fees'
col STARTDATE                                  heading '   Start   '
col ssrmeet_end_date                           heading '    End    '
col INSTRUCTOR               format a14        heading 'Instructor'
col ssbsect_ssts_code        format a1         heading 'S' 

--break on STARTDATE skip 1 on report

compute sum of TOTAL_CURRENT_ENROLLMENT TOTAL_RESERVED TOTAL_ENROLLMENT on report

break on report

spool /u02/app/sct/jobsub/outMCPROD/$BANUID-swrenrl.txt

--Start here...

SELECT to_char(sysdate, 'DD-MON-YYYY') HDATE, 
       to_char(sysdate, 'HH24:MI:SS') HTIME, 
       ssbsect_term_code HTERM,
       ssbsect_subj_code || ' ' || ssbsect_crse_numb || ' ' || ssbsect_seq_numb COURSE, 
       nvl(ssbsect_crse_title, scbcrse_title) TITLE,
       ssbsect_crn CRN,
	decode(ssrxlst_xlst_group, 'NA', ' ') GR,
       ssrattr_attr_code CONTROLLER,
       ssbsect_camp_code CAMPUS,
       decode(ssrmeet_sun_day, null, null,  'SU') ||
       decode(ssrmeet_mon_day, null, null,
	      decode(ssrmeet_sun_day, null, 'MO', '/MO')) ||
       decode(ssrmeet_tue_day, null, null,
	      decode(ssrmeet_sun_day || ssrmeet_mon_day, null, 'TU', '/TU')) ||
       decode(ssrmeet_wed_day, null, null, 
              decode(ssrmeet_sun_day || ssrmeet_mon_day || ssrmeet_tue_day, null, 'WE', '/WE')) ||
       decode(ssrmeet_thu_day, null, null,
              decode(ssrmeet_sun_day || ssrmeet_mon_day || ssrmeet_tue_day || ssrmeet_wed_day, null, 'TH', '/TH')) ||
       decode(ssrmeet_fri_day, null, null,
          decode(ssrmeet_sun_day || ssrmeet_mon_day || ssrmeet_tue_day || ssrmeet_wed_day, null || ssrmeet_thu_day, 'FR', '/FR')) ||
       decode(ssrmeet_sat_day, null, null,
          decode(ssrmeet_sun_day || ssrmeet_mon_day || ssrmeet_tue_day || ssrmeet_wed_day, null || ssrmeet_thu_day || ssrmeet_fri_day, 'SA', '/SA')) DAY,
       sum(decode(sfrstcr_rsts_code, 'RW', 1, 'RI', 1, 'RE', 1, 'RS', 1, 0)) TOTAL_CURRENT_ENROLLMENT,
       sum(decode(sfrstcr_rsts_code, 'RV', 1, 0)) TOTAL_RESERVED,
       sum(decode(sfrstcr_rsts_code, 'RW', 1, 'RI', 1, 'RE', 1, 'RS', 1, 0)) +
       sum(decode(sfrstcr_rsts_code, 'RV', 1, 0)) TOTAL_ENROLLMENT,
       ssbsect_max_enrl,
	ssbsect_enrl * ssbsect_bill_hrs SCH,
       swvfees_amount,
       ssrmeet_start_date STARTDATE,
       ssrmeet_end_date,
       substr(ssrtext_text, instr(ssrtext_text, ' ') + 1) INSTRUCTOR,
       ssbsect_ssts_code
  FROM sfrstcr, swrmeet, ssbsect, scbcrse, ssrtext, ssrattr, swvfees, ssrxlst
 WHERE substr(ssbsect_term_code, 5, 2) in ('10', '20', '30')
   and ssbsect_term_code like '&&TERM%' 
   and ssbsect_term_code = sfrstcr_term_code (+)
   and ssbsect_crn = sfrstcr_crn (+)
   and ssbsect_subj_code = scbcrse_subj_code
   and ssbsect_crse_numb = scbcrse_crse_numb
   and scbcrse_eff_term = (SELECT max(scbcrse_eff_term)
                             FROM scbcrse
                            WHERE scbcrse_subj_code = ssbsect_subj_code
                              and scbcrse_crse_numb = ssbsect_crse_numb
                              and scbcrse_eff_term <= ssbsect_term_code)
   and ssbsect_term_code = ssrmeet_term_code (+)
   and ssbsect_crn = ssrmeet_crn (+)
   and ssbsect_term_code = ssrattr_term_code
   and ssbsect_crn = ssrattr_crn
   and ssrattr_attr_code &&CONTROLLER
   and ssbsect_term_code = ssrtext_term_code (+)
   and ssbsect_crn = ssrtext_crn (+)
   and ssbsect_term_code = swvfees_term_code (+)
   and ssbsect_crn = swvfees_crn (+)
   and ssbsect_term_code = ssrxlst_term_code (+)
   and ssbsect_crn = ssrxlst_crn (+)
   and ssbsect_ssts_code like NVL('&&STATUS%','%')
   and ssbsect_camp_code like NVL('&&CAMPUS%','%')
   and (ssrtext_text like 'Instructor:%' or ssrtext_text not like 'Instructor:%')
 GROUP BY ssbsect_term_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_crse_title, scbcrse_title,
	  ssbsect_crn, ssrxlst_xlst_group, ssrattr_attr_code, ssbsect_camp_code, ssbsect_max_enrl, ssbsect_enrl, ssbsect_bill_hrs, swvfees_amount,
	  ssrmeet_start_date, ssrmeet_end_date, ssbsect_ssts_code,
	  ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_day, ssrmeet_fri_day, ssrmeet_sat_day, 
	  ssrtext_text, ssbsect_seq_numb
 ORDER BY &&SORT;

--ssrmeet_start_date, HTERM,
TTitle center '   '
col TOTAL_CRN format 9990 heading 'TOT|CRN'
col TOTAL_SCH format 99999999.990 heading 'TOT|SCH'

SELECT count(distinct ssbsect_crn) TOTAL_CRN,
       sum(ssbsect_enrl * ssbsect_bill_hrs) TOTAL_SCH
--       sum(decode(sfrstcr_rsts_code, 'RV', 1, 0)) TOTAL_ENROLLMENT
FROM sfrstcr, swrmeet, ssbsect, scbcrse, ssrtext, ssrattr, swvfees, ssrxlst
 WHERE substr(ssbsect_term_code, 5, 2) in ('10', '20', '30')
   and ssbsect_term_code like '&&TERM%' 
   and ssbsect_term_code = sfrstcr_term_code (+)
   and ssbsect_crn = sfrstcr_crn (+)
   and ssbsect_subj_code = scbcrse_subj_code
   and ssbsect_crse_numb = scbcrse_crse_numb
   and scbcrse_eff_term = (SELECT max(scbcrse_eff_term)
                             FROM scbcrse
                            WHERE scbcrse_subj_code = ssbsect_subj_code
                              and scbcrse_crse_numb = ssbsect_crse_numb
                              and scbcrse_eff_term <= ssbsect_term_code)
   and ssbsect_term_code = ssrmeet_term_code (+)
   and ssbsect_crn = ssrmeet_crn (+)
   and ssbsect_term_code = ssrattr_term_code
   and ssbsect_crn = ssrattr_crn
   and ssrattr_attr_code &&CONTROLLER
   and ssbsect_term_code = ssrtext_term_code (+)
   and ssbsect_crn = ssrtext_crn (+)
   and ssbsect_term_code = swvfees_term_code (+)
   and ssbsect_crn = swvfees_crn (+)
   and ssbsect_ssts_code like NVL('&&STATUS%','%')
   and ssbsect_camp_code like NVL('&&CAMPUS%','%');


set heading off
CLEAR BREAKS
CLEAR COLUMNS
TTitle center '   '
col AVGCRN format 990.99

SELECT 'Average CRN Enrollment ', 
	(sum(decode(sfrstcr_rsts_code, 'RW', 1, 'RI', 1, 'RE', 1, 'RS', 1, 0)) +
       sum(decode(sfrstcr_rsts_code, 'RV', 1, 0))) / (count(distinct ssbsect_crn)) AVGCRN
FROM sfrstcr, swrmeet, ssbsect, scbcrse, ssrtext, ssrattr, swvfees
 WHERE substr(ssbsect_term_code, 5, 2) in ('10', '20', '30')
   and ssbsect_term_code like '&&TERM%' 
   and ssbsect_term_code = sfrstcr_term_code (+)
   and ssbsect_crn = sfrstcr_crn (+)
   and ssbsect_subj_code = scbcrse_subj_code
   and ssbsect_crse_numb = scbcrse_crse_numb
   and scbcrse_eff_term = (SELECT max(scbcrse_eff_term)
                             FROM scbcrse
                            WHERE scbcrse_subj_code = ssbsect_subj_code
                              and scbcrse_crse_numb = ssbsect_crse_numb
                              and scbcrse_eff_term <= ssbsect_term_code)
   and ssbsect_term_code = ssrmeet_term_code (+)
   and ssbsect_crn = ssrmeet_crn (+)
   and ssbsect_term_code = ssrattr_term_code
   and ssbsect_crn = ssrattr_crn
   and ssrattr_attr_code &&CONTROLLER
   and ssbsect_term_code = ssrtext_term_code (+)
   and ssbsect_crn = ssrtext_crn (+)
   and ssbsect_term_code = swvfees_term_code (+)
   and ssbsect_crn = swvfees_crn (+)
   and ssbsect_ssts_code like NVL('&&STATUS%','%')
   and ssbsect_camp_code like NVL('&&CAMPUS%','%');

spool off