* H:\sas\sil\getxlistcrns.sas ;
* PN 2004/ ;
* make a file of all crosslistings -- modified 9/1/05 ;
* includes crosslisted courses and sections crosslisted for admin reasons
  (e.g. grad & undergrad with different credit ;

options nocenter errors=1 noxwait noxsync ls=80 obs=max ;
run;

* location and name of output/input data files;
filename xlists   "h:\data\sil\yr200506\fa05\xlist.txt"  lrecl=102;
run;

* read in parms =;
  * all hardcoded data should be here;
data _NULL_;
call symput('checkterm',"'120058%'") ;   *  select term;
run;

proc format;  * need this to encode schedtype ;
value $stype 
'Bus Transportation   '='BUS'
'Clinical Practice    '='CLP'
'Conference           '='CNF'
'Cooperative Education'='COP'
'Discussion/Recitation'='DIS'
'Flight               '='FL'
'Independent Study    '='IND'
'Laboratory           '='LAB'
'Laboratory-Discussion'='LBD'
'Lecture              '='LEC'
'Lecture-Discussion   '='LCD'
'Online               '='ONL'
'Packaged Section     '='PKG'
'Practice             '='PR'
'Quiz                 '='Q'
'Studio               '='ST'
'Study Abroad         '='STA' ;
run;


** get info from EDW ;
PROC sql ;
%include  'h:\carol\personal\connections\edwDirect.inc';
* has one statement;
*connect to oracle as edw (user=xxxxxx orapw=xxxxxxx path='dsprod01');

* these are crns of courses offered this term mapped to the ctrlling dept subject & number;
drop view links; 
create view links as select * from connection to EDW
(select distinct 
  s.crs_subj_cd as subject,
  s.crs_nbr as crse,
  s.crn as crnc,
  s.term_cd,
  s.part_of_term_cd,
  s.sect_nbr as sect,
  s.sched_type_desc,
  s.crs_id,
  nvl(x.cross_list_group_id,'NONE') as crosslistid,
  nvl(c.ctl_subject,s.crs_subj_cd) as ctl_subject,
  nvl(c.ctl_crs_nbr,s.crs_nbr) as ctl_crs_nbr
    
from EDW.T_SECT_BASE S
left outer join EDW.T_CROSS_LIST_SECT x on (s.crn=x.crn and x.term_cd like &checkterm and x.term_cd=s.term_cd)
left outer join (select distinct crs_subj_cd as ctl_subject,
                 crs_nbr     as ctl_crs_nbr,
				 crs_id,
      CROSS_LIST_CRS_CTRL_CD as ctl_cd             
      from EDW.T_CROSS_LIST_CRS 
          where  term_cd like &checkterm  
           and CROSS_LIST_CRS_CTRL_CD ='C') c on (s.crs_id=c.crs_id)
 where  s.term_cd  like &checkterm
     
    );

create view xlistcrs as select * from connection to EDW
(select distinct 
  term_cd,
  crs_subj_cd as subject,
  crs_id,
  cross_list_crs_ctrl_cd as old_ctl_cd
    
from edw.T_Cross_list_crs 
where  term_cd like &checkterm and  cross_list_crs_ctrl_cd in ('N','C')	  );



       /* packages:  get first meeting time, dow, bldg, room 
           for the CRN to expand the original listing.*/
drop view pkgv;
create view pkgv as select * from connection to EDW 
 (select m.TERM_CD,
         m.CRN as crnc, 
         m.SCHED_TYPE_DESC as meet_sched_type_desc,    /* cannot use sched type code due to linked crses*/
         m.BLDG_CD,
         m.SECT_ROOM_NBR,
         m.SECT_MONDAY_MEET_IND as Mon,
         m.SECT_TUESDAY_MEET_IND as Tue,
         m.SECT_WEDNESDAY_MEET_IND as Wed,
         m.SECT_THURSDAY_MEET_IND as Thu,
         m.SECT_FRIDAY_MEET_IND as Fri,
         m.SECT_SATURDAY_MEET_IND as Sat,
		 m.SECT_BGN_TIME
  from edw.t_sect_meeting m, edw.t_SECT_BASE s
  where m.term_cd like &checkterm 
  and m.term_cd=s.term_cd
  and s.crn=m.crn
  and S.SCHED_TYPE_CD like 'PKG%' 
order by s.crn);

disconnect from EDW;
quit;
data links1; 
set links;  
run;

proc sort data=links1;
by crs_id subject term_cd;

data xlistcrs1;  ** could not get this join to work in oracle, went forever;
set xlistcrs;
run;

proc sort data=xlistcrs1;
by crs_id subject ;
run;


data links2(drop=crnc rename=(ctl_subject=old_ctl_subject )) ;  ;  ** add ctl_cd to each course record ;
merge links1(in=a) xlistcrs1(in=b);
by crs_id subject ;
if a;
if not b then old_ctl_cd='X';
idtype=put(sched_type_desc,$stype.);
crn=1*crnc;
drop sched_type_desc;
if (crosslistid='NONE' or idtype='PKG') and old_ctl_cd='X' then delete; 
* note: added in the test for pkg since pkgs all have crosslistids ;
** most crosslisted course sections have a crosslist id that allows us to 
  identify all crosslistings.  But arranged classes don't need one (crosslist  
  id is required by Banner if class meets same time/place as another class)
  so for these, we need to check section type and section id to determne
  crosslist set.  It may not always be right, but best we can do;
length sortcode $ 10. ;
if crosslistid='NONE' then sortcode=crosslistid||sect||idtype;
 else  sortcode=crosslistid||'      ' ;
* the latter allows them to use any idtype and section they want ;
run;        


proc sort data=links2;
by crs_id sortcode old_ctl_cd crn;
run;


data links3;     ** now create xlist sets. must be same crs_id to elim concurrent;
set links2 ;
by crs_id sortcode old_ctl_cd crn ;
retain ctl_crn ctl_subject ctl_crse ;
ctl_cd='N';   
credited=0; 
if first.sortcode then do;
   ctl_crn=crn;
   ctl_subject=subject;
   ctl_crse=crse;
   ctl_cd='C';   *  *first ctl crn from xlist table is kept;
   credited=1;
end;
run;

data links4 concurr;
set links3;
by crs_id sortcode  ;
if first.sortcode and last.sortcode then output concurr;   ** this eliminates concurrnet;
else output links4;
run;

proc sort data=links4;
by crn;

data pkg (drop=crnc mon tue wed thu fri sat );
set pkgv;
crn=1*crnc;
dow=mon||tue||wed||thu||fri||sat;
pkg_mtg_sched_type=put(meet_sched_type_desc,$stype.);
run;

data links5;  ** expand crns out for packaged sections ;
merge links4(in=a) pkg(in=b);
by crn;
if a;
if not b then pkg_mtg_sched_type=idtype; 
run;

proc sort data=links5;
by crs_id sortcode ctl_cd;
run;
options obs=99;
run;

proc print data=links5;
var crs_id crosslistid crn subject crse sect idtype ctl_crn ctl_cd credited pkg_mtg_sched_type; 
run;
options obs=max;
run;


proc sort nodupkey data=links5;
by term_cd crn pkg_mtg_sched_type;

options missing='';
run;

data _NULL_;
set links5;
file xlists;
lastupdate=today();
*if term_cd le &checkterm;
put @01 subject          $char04.
    @05 crse             $char03.
    @08 crn              6. 
    @14 part_of_term_cd  $char01.
    @15 sect             $char03.
    @18 idtype           $char03.
    @21 crs_id           $char08.
    @29 ctl_subject      $char04.
    @33 ctl_crse         $char03.
    @36 ctl_cd           $char01.
    @37 term_cd          $char06. 
    @43 credited         1.          /* default is credited dept is ctrl dept */
	@44 ctl_crn          6. 
    @50 pkg_mtg_sched_type   $char03. 
    @53 dow              $char06.      /* only populated for packaged crns*/
    @59 BLDG_cd          $char06.      /* only populated for packaged crns*/
    @65 sect_room_nbr    $char10.      /* only populated for packaged crns*/
    @75 sect_bgn_time    $char05.      /* only populated for packaged crns*/ 
	@80 'default' 
	@88 lastupdate       mmddyy10. 
    @98 crosslistid      $char04.
   @102 'x'  ;


run;

proc tabulate missing data =links5;
class term_cd ;
table term_cd,n;
run;