* H:\sas\oracle\SIL\rosterstudents.sas ;
* PN 2005/--- ;
* extract student enrollment information and create a file to load into server for rosters ;
* uses current enrollment data, not snapshot ;
* keep three terms up at a time, current term, last term, next term ;
* to start a new term, make sure that the sil process new term is running;
*    then add the term code below to the checkterm list;
* when we move to a new year in FA05, will need to use multiple files for pkgmap and instr ; 
options nocenter errors=1 ls=80 obs=max compress=no;
run;

* location and name of output/input data files;
filename students "h:\data\ros\students.txt"  lrecl=84; * one rec per st;
filename regs     "h:\data\ros\regs.txt"  lrecl=39;     * one rec per st per crn per term;
filename crns     "h:\data\ros\crns.txt"  lrecl=36;     * one rec per crn per term;
filename instout  "h:\data\ros\inst.txt"  lrecl=62;     * one rec per inst per crn per idtype per term
** files created by sil process ; 
filename pkgmap   ("h:\data\sil\yr200405\pkgmap000.txt" "h:\data\sil\yr200506\pkgmap000.txt") lrecl=19;
filename instr    ("h:\data\sil\yr200405\instr000.txt"  "h:\data\sil\yr200506\instr000.txt")  lrecl=140;
** note: for summer and fall terms, will need to use two ac yr files each for above;
**       use the  syntax ("file1" "file2") instead of single file name;  
**       for spring term, one file only for the AY is fine. ;
data _NULL_;
call symput('faterm',"'120058%'") ;   *  fa term to select ;
call symput('spterm',"'120051%'") ;   *  sp term to select ;
call symput('suterm',"'120055%'") ;   *  su term to select ;
run;

* this has one rec per crn per sched type -- e.g. a pkg with lect & disc has two recs;
data pkg;
infile pkgmap;
input @1 crn 5.    /* original CRN of the pkg */
      @6 sched_type1 $char03.  /* convert to this sched type*/
      @9 crn1 5.               /* and to this main crn */
      @14 term_cd $char06.;
if term_cd =:&faterm or term_cd =:&spterm or term_cd =:&suterm ;
run;

proc sort;
by  term_cd crn;
data instr1;
infile instr;
input @01 uin           $char09.
    @10 ssn             $char09. 
    @19 edw_pers_id     7. 
    @26 lname           $char15.
    @41 fname           $char15.
    @56 mname           $char10.
    @66 crs_id          $char07.
    @73 ctl_CRN         5.        /* ctl dept crn. if pkg, lowest crn for this itype,sectn numbr */
    @78 ctlsubject      $char04.      
    @82 ctlnbr          $char03. 
    @85 sect_nbr        $char03.
	@88 SESS_ASSIGN_CATGRY $char02.
    @90 adjpct          6.2
    @96 priminstr       $char01.
    @97 FAC_INSTRN_ASSIGN_TYPE_CD $char04.
   @101 sess_tot_pct    6.2
   @112 termtype       $char02.
   @114 part_of_term_cd $char06.
   @120 term_cd        $char06.  ;
instr_name=substr(trim(lname)||" "||trim(fname)||" "||trim(mname)||"                    ",1,20);
if term_cd =:&faterm or term_cd =:&spterm or term_cd =:&suterm ;
run ;

proc sort nodupkey data=instr1;
by edw_pers_id term_cd ctl_crn;
run;

  * get xlist  sections info from SQL server ;
  * this has one record per crn ;
proc sql;
%include  'h:\carol\personal\connections\SQL-dmiathenaCRS.inc';
*connect to SQLSERVR as crs (user=xxxxxx pass=xxxxxxx server='DMIATHENA' database='Course');
drop view xlistcrns;
create view xlistcrns as select * from connection to crs 
(select distinct subject,crse as crs_nbr,crn,sect as sect_nbr, idtype as sched_type_cd,
  crs_id,ctl_crn,ctl_cd,ctl_subject,ctl_crse as ctl_crs_nbr, 
   term as term_cd,pkg_mtg_sched_type
from silxlist 
where (term like &faterm or term like &spterm or term like &suterm )
order by term,crn,ctl_subject,ctl_crse,ctl_cd,subject
);
 
disconnect from crs;
quit;
 
* get  crosslisted crns and their map to ctl crns;
data xlist1; 
set xlistcrns;  
run; 
data xlist2 (keep=term_cd crn ctl_crn ctl_subject ctl_crs_nbr);
set xlist1;
if ctl_cd='N';   ** keep one rec per secondary CRN ;
run;

proc sort nodupkey data=xlist2;
by term_cd crn;
run;


proc sql;
create view pkgxlst as select p.*,max(x.ctl_crn,0) as ctl_crn,
x.ctl_subject,x.ctl_crs_nbr as ctl_crse  from
pkg p left outer join xlist1 x on p.term_cd=x.term_cd and p.crn1=x.crn
and p.sched_type1=x.pkg_mtg_sched_type 
order by p.term_cd,p.crn;
run;

data pkgxlst1;
set pkgxlst;
run;
proc sort nodupkey data=xlist2 out=xlist3;
by term_cd crn ;  * need this to elim pkg dups;
run;
/* Sign on to IRPROD not needed if autodone*/

*signon irt.unxspawn;

rsubmit irtprod.unxspawn;


proc upload data=pkgxlst out=pkgxlst1;
run;
proc upload data=instr1 out=instr1;
run;
proc upload data=xlist3 out=xlist3;
run;

options nocenter errors=1 ls=80 obs=max compress=no;
run;


* read in parms with current academic year, final pay period info ;
  * all hardcoded data should be here;
  * keep three terms going at once ;
data _NULL_;
call symput('faterm',"'120058%'") ;   *  fa term to select ;
call symput('spterm',"'120051%'") ;   *  sp term to select ;
call symput('suterm',"'120055%'") ;   *  su term to select ;
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 current student enrollment info ;
PROC sql ;
%include  '/IR/UIUC/home/livngstn/connections/edwdirect.inc';
* has one statement;
*connect to oracle as edw (user=xxxxxx orapw=xxxxxx path='dsprod01');
drop view crn;
create view crn as select * from connection to EDW
(select distinct crn,
  crs_subj_cd              as subject, 
  crs_nbr                  as crse,
  sect_status_cd, 
  sect_nbr                 as section,
  nvl(crs_id,'99999999')   as crs_id,
  sched_type_desc,
  term_cd  
  from edw.t_sect_base
  where (term_cd like &faterm or term_cd like &spterm or term_cd like &suterm)
  order by term_cd, crn);

drop view stuenr;

create view stuenr as select * from connection to EDW
(select 
e.edw_pers_id,
e.crn_credit_hour          as secthrs,
e.crs_reg_status_cd        as regstat,
e.crn_grade                as grade,
e.crn_grading_mode_cd      as grademode,
e.term_cd                  as term_cd,
ah.level_gpa_earned_hour    as overallhrs,
/*ah.calc_cls_cd             as stclass,*/
st.student_type_cd         as sttype,
st.student_level_cd        as stlevel,
st.student_acad_pgm_cd     as program,
st.coll_cd                 as acadcoll,
st.dept_cd                 as dept,
st.student_curr_1_major_name as major,
st.student_curr_1_deg_cd   as degree,
p.pers_lname               as lname,
p.pers_fname               as fname,
p.pers_mname               as mname,
p.uin,
p.pers_confidentiality_ind      as ferpa,
s.part_of_term_cd          as termtype,
s.sched_type_desc,                       /* cannot use code, multiple per desc */ 
s.instrn_method_cd         as imethod,  /* need for some exm crses*/
s.crs_subj_cd              as subject, 
s.crs_nbr                  as crse,
s.sect_status_cd, 
s.sect_nbr                 as section,
e.crn                      as crnc,   /*as registerd. this is character data, we want numeric*/
nvl(s.crs_id,'99999999')   as crs_id,
s.sect_gradable_ind        as graded  /* Y/N*/


from EDW.T_STUDENT_CRS_REG_HIST e
left outer join EDW.T_STUDENT_TERM st on (e.edw_pers_id=st.edw_pers_id 
            and st.term_cd=e.term_cd)
left outer join edw.t_student_ah_level_gpa_hist ah   /* need to get hours to calc class code for undergrads */
			       on ( student_level_gpa_cur_info_ind='Y' and 
                     e.edw_pers_id=ah.edw_pers_id and ah.level_gpa_type_ind='O'
					 and gpa_level_cd='1U')       
/*left outer join EDW.T_STUDENT_AH_TERM ah on (e.edw_pers_id=ah.edw_pers_id and e.term_cd=ah.term_cd)*/
left outer join EDW.T_PERS_HIST p on (e.edw_pers_id=p.edw_pers_id and p.pers_cur_info_ind='Y' )
left outer join EDW.T_SECT_BASE s on (s.term_cd=e.term_cd  
       and e.crn=s.crn and s.sect_status_cd='A' )
where e.student_crs_reg_cur_info_ind='Y' 
 and (e.term_cd like &faterm or e.term_cd like &spterm or e.term_cd like &suterm)
order by e.term_cd, crn )
;
drop view emails;

create view emails as  select * from connection to EDW
(select m.edw_pers_id,m.EMAIL_ADDR  as email
from EDW.T_EMAIL_ADDR M 
where   M.EMAIL_STATUS_IND = 'A'
    and M.EMAIL_TYPE_CD = 'UI'
	and M.EMAIL_STATUS_DESC = 'Active'
    and instr(email_addr,'@uiuc.edu')>0
   order by edw_pers_id)    ; 

disconnect from EDW;
quit;



data stuenrbyCRN;   * get student enrollments by crn ; 
length lname fname $ 12. mname $ 1. ;
set stuenr;
sched_type=put(sched_type_desc,$stype.);
crn=1*crnc; ** convert character crn to number ;
drop sched_type_desc crnc;
run;

proc sort data=stuenrbycrn;
by term_cd crn;
run;  ** this took 6 minutes but was needed ;


** add controlling dept info to student record;
data addctl;
merge stuenrbycrn(in=a) xlist3(in=b);  * one per term per crn, pkg will be random;
by term_cd crn;
if a;
if not b then do;
   ctl_crn=crn;
   ctl_subject=subject;
   ctl_crse_nbr=crse;
end;
run;

  * now add multple sections for each pkg, and 
   change ctl crn to be the first found for this section type;
  * this should add about 5%  to the number of obs;
  ** many to many, need to use SQL ;

proc sql ;
drop view addpkg1;
create view addpkg1 as select a.*,p.crn1,p.sched_type1,
p.ctl_crn as ctl_crn1,p.ctl_subject as ctl_subject1,p.ctl_crse as ctl_crse1 from 
 addctl a left outer join pkgxlst1 p
 on a.crn=p.crn  and a.term_cd=p.term_cd;

data addpkg2;   ** replace sched type with mult sched types;
set addpkg1;
if crn1>0 then   sched_type=sched_type1;  * expanded section type; 
if ctl_crn1>0 then do;               * for xlisted packages ;
   ctl_crn    =ctl_crn1;
   ctl_subject=ctl_subject1;
   ctl_crse   =ctl_crse1;
end;
run;

proc sort data=addpkg2;
by edw_pers_id term_cd;

data fincrs(keep=uin  crn ctl_crn secthrs regstat grade sched_type term_cd);
set addpkg2;
if regstat=:'R' and grademode=:'Honors' then regstat='RH';
run;



data finstu;  * get most recent data for student ;
set addpkg2;
by edw_pers_id term_cd;
if last.edw_pers_id;
run;

data emails1;
set emails;
run;


data finstu1;
merge finstu(in=a) emails1(in=b);
by edw_pers_id;
if a;
if not b then email='                  ';
name=trim(lname)||", "||trim(fname)||' '||trim(mname);
pl=index(email,'@') ;
if pl >1 then email=substr(email,1,pl-1);
dept=substr(dept,2,3) ; * eliminate campus from dept;
if not(ferpa='Y') then ferpa='N';
stlevel=substr(stlevel,2,1);
 * this will have all students enrolled during the terms covered, 1 rec per student;
 * info will be the most recent for each student ;

* compute student class ; 
stclass=stlevel;  * default ;
if stlevel='U' then do;                           
   if degree='NDEG' then stclass='5';
   else if overallhrs>=90 then stclass='4';
   else if overallhrs>=60 then stclass='3';
   else if overallhrs>=30 then stclass='2';
   else stclass='1' ;
end;

run;


data instr2(keep= ctl_CRN uin name priminstr netid edw_pers_id term_cd) ;
merge instr1(in=a) emails(in=b);
by edw_pers_id;
if a;
if not b then select (edw_pers_id) ;
    when (1801)	        email='h-dickel@uiuc.edu'   ;  *Dickel, Helene R    ;
    when (2011)	        email='sshawki2@uiuc.edu'   ;  *Shawki, Tarek G     ;
    when (2680)	        email='r-wyer@uiuc.edu'     ;  *Wyer, Robert S      ;
    when (3393)	        email='r-ranga@uiuc.edu'    ;  *Rao, R Ranga        ;
    when (8801)	        email='w-seitz@uiuc.edu'    ;  *Seitz, Wesley D     ;
    when (11400)	email='l-bouton@uiuc.edu'   ;  *Bouton, Lawrence F  ;
    when (29387)	email='w-geibel@uiuc.edu'   ;  *Geibel, William D   ;
    when (36756)	email='f-newman@uiuc.edu'   ;  *Newman, Frances S   ;
    when (50340)	email='pecknold@uiuc.edu'   ;  *Pecknold, David     ;
    when (67098)	email='j-klein3@uiuc.edu'   ;  *Klein, Joan L       ;
    when (69207)	email='d-rowan@uiuc.edu'    ;  *Rowan, Dennis M     ;
    when (69864)	email='j-hill@uiuc.edu'     ;  *Hill, Jacquetta F   ;
    when (71515)	email='p-saylor@uiuc.edu'   ;  *Saylor, Paul E      ;
    when (75030)	email='t-ebrey @uiuc.edu'   ;  *Ebrey, Thomas G     ;
    when (77317)	email='f-kamber@uiuc.edu'   ;  *Kamber, Franz W     ;
    when (77756)	email='d-sorlie@uiuc.edu'   ;  *Essex-Sorlie, Diane ;
    when (78114)	email='a-parker@uiuc.edu'   ;  *Parker, Alan J      ;
    when (78766)	email='i-hajj@uiuc.edu'     ;  *Hajj, Ibrahim N     ;
    when (162928)	email='rbenso1@uiuc.edu'    ;  *Benson, Richard D   ;
    when (483862)	email='clhulin@uiuc.edu'    ;  *Hulin, Charles L    ;
    when (1903055)	email='s-rosen@uiuc.edu'    ;  *Rosen, Sidney       ;
    when (1990648)	email='dhprice@uiuc.edu'    ;  *Price, David H      ;
    when (2119257)	email='jungpark@uiuc.edu'   ;  *Park, Jung M        ;
    when (2393185)	email='danshao@uiuc.edu'    ;  *Shao, Dan           ;
    when (2534010)	email='gamberg@uiuc.edu'    ;  *AMBERG, EUGENE L    ;
    when (2555125)	email='hsm@uiuc.edu'        ;  *Middleton, Holly    ;
otherwise email='          ';
end;
name=trim(lname)||', '||trim(fname)||' '||substr(mname,1,1);
pl=index(email,'@') ;
if pl >1 then netid=substr(email,1,pl-1);
else netid='        ';
run;

data crns1;
set crn;
run;

proc sort nodupkey;
by term_cd crn;

proc download in=work out=work;
select instr2 crns1 finstu1 fincrs ;
run;


endrsubmit;

data fincrs;
set fincrs;
file regs;
put @1 uin $char09.
   @10 crn 5.
   @15 ctl_crn 5.
   @20 secthrs 5.2
   @25 regstat $char02.
   @27 grade   $char03.
   @30 sched_type $char03.
   @33 term_cd $char06.
   @39 'x' ;
run;

data instr3;
set instr2;
file instout;
if edw_pers_id>0 ;
put     @01 ctl_CRN         5.        
    @06 uin             $char09.
    @15 name            $char25.
	@40 priminstr       $char01.
    @41 netid           $char8. 
    @49 edw_pers_id     7.
	@56 term_cd         $char06.
	@62 'x'             ;
run;



data crns1;
set crns1;
file crns;
put @1 crn 5.
    @6 subject $char04. 
	@10 crse   $char03.
	@13 section $char03.
	@16 crs_id    7.
    @23 sect_status_cd $char06.
	@30 term_cd $char06.
    @36 'x' ;
run;

proc sort data=finstu1 nodupkey;
by edw_pers_id;
run;
data finstu2;
set finstu1;
file students;              
put @01 edw_pers_id 7.0       
    @08 UIN        $char09.
    @17 name       $char20. 
	@37 ferpa      $char01.
	@38 stlevel    $char01.
	@39 acadcoll   $char02.
	@41 dept       $char03.
	@44 major      $char25.
	@69 degree     $char05.
	@74 stclass    $char01.
	@76 email      $char08.
    @84 'x' ;
run;
 
*proc tabulate data=finstu1;
*class stclass;
*table stclass,n;
*run;