| 1 |
package WOU_Schedule; |
| 2 |
|
| 3 |
# Routines for Banner SIS Schedule |
| 4 |
# |
| 5 |
# Subs in this package that use DBI expect to be passed a database handle |
| 6 |
# ($dbh) that has been granted the appropriate roles by WOU_Secure.pm. |
| 7 |
# |
| 8 |
# Jeremy Hickerson, 3/09/2004 |
| 9 |
|
| 10 |
use strict; |
| 11 |
|
| 12 |
BEGIN { |
| 13 |
use Exporter (); |
| 14 |
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); |
| 15 |
|
| 16 |
# set the version for version checking |
| 17 |
$VERSION = 1.00; |
| 18 |
@ISA = qw(Exporter); |
| 19 |
@EXPORT = qw(&term_sections §ion &instructor §ion_meet |
| 20 |
&primary_instructor &term_schedule); |
| 21 |
%EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ], |
| 22 |
@EXPORT_OK = qw( ); |
| 23 |
|
| 24 |
( grep /gen\$com/i, @INC ) || unshift @INC, "gen\$com"; |
| 25 |
} |
| 26 |
use vars @EXPORT_OK; |
| 27 |
use subs qw(term_sections section instructor section_meet primary_instructor |
| 28 |
term_schedule); |
| 29 |
|
| 30 |
use DBI; |
| 31 |
use WOU_Person qw(id real_ssn); |
| 32 |
use WOU_Util qw(add2hash join_table_subs); |
| 33 |
|
| 34 |
# ============================================================================ |
| 35 |
# Package-Level Stuff |
| 36 |
# ============================================================================ |
| 37 |
|
| 38 |
my %glb_prepared_sql; # Hash that holds $sth's for prepared sql; |
| 39 |
# helps performance. Entries are of the form: |
| 40 |
# ( "sth_qry_section" => $sth_qry_section, ... ) |
| 41 |
|
| 42 |
my ($sth_qry_section, $sth_qry_instructor, $sth_qry_section_meet); |
| 43 |
|
| 44 |
|
| 45 |
# ============================================================================ |
| 46 |
# Routines |
| 47 |
# ============================================================================ |
| 48 |
|
| 49 |
# ================================================ |
| 50 |
# term_sections - all active sections for a term |
| 51 |
# ================================================ |
| 52 |
sub term_sections { |
| 53 |
my ($dbh, $term) = @_; |
| 54 |
|
| 55 |
my ( $crn, $ptrm, $subj, $crse, $ssts_code, $schd_code, $crse_title, |
| 56 |
$credit_hrs, $bill_hrs, $gmod_code, $gradable, $max_enrl, $enrl, |
| 57 |
$seats_avail, $lec_hr, $lab_hr, $oth_hr, $cont_hr, $cip, $start_date, |
| 58 |
$end_date, $campus, @term_sections ); |
| 59 |
|
| 60 |
my $sth_qry_term_sections = $dbh->prepare( q{ |
| 61 |
select ssbsect_crn, |
| 62 |
SSBSECT_PTRM_CODE, |
| 63 |
SSBSECT_SUBJ_CODE, |
| 64 |
SSBSECT_CRSE_NUMB, |
| 65 |
SSBSECT_SSTS_CODE, |
| 66 |
SSBSECT_SCHD_CODE, |
| 67 |
nvl(SSBSECT_CRSE_TITLE, scbcrse_title), |
| 68 |
nvl(SSBSECT_CREDIT_HRS, scbcrse_credit_hr_low), |
| 69 |
SSBSECT_BILL_HRS, |
| 70 |
SSBSECT_GMOD_CODE, |
| 71 |
SSBSECT_GRADABLE_IND, |
| 72 |
SSBSECT_MAX_ENRL, |
| 73 |
SSBSECT_ENRL, |
| 74 |
SSBSECT_SEATS_AVAIL, |
| 75 |
SSBSECT_LEC_HR, |
| 76 |
SSBSECT_LAB_HR, |
| 77 |
SSBSECT_OTH_HR, |
| 78 |
SSBSECT_CONT_HR, |
| 79 |
scbcrse_cipc_code, |
| 80 |
to_char(ssbsect_ptrm_start_date, 'mm/dd/yyyy'), |
| 81 |
to_char(ssbsect_ptrm_end_date, 'mm/dd/yyyy'), |
| 82 |
ssbsect_camp_code |
| 83 |
from ssbsect, |
| 84 |
scbcrse |
| 85 |
where ssbsect_term_code = ? AND |
| 86 |
ssbsect_ssts_code = 'A' AND |
| 87 |
scbcrse_subj_code = ssbsect_subj_code AND |
| 88 |
scbcrse_crse_numb = ssbsect_crse_numb AND |
| 89 |
scbcrse_eff_term = ( |
| 90 |
select max(scbcrse_eff_term) |
| 91 |
from scbcrse |
| 92 |
where scbcrse_subj_code = ssbsect_subj_code and |
| 93 |
scbcrse_crse_numb = ssbsect_crse_numb and |
| 94 |
scbcrse_eff_term <= ssbsect_term_code) } ); |
| 95 |
|
| 96 |
|
| 97 |
$sth_qry_term_sections->execute($term); |
| 98 |
|
| 99 |
while ( ( $crn, |
| 100 |
$ptrm, |
| 101 |
$subj, |
| 102 |
$crse, |
| 103 |
$ssts_code, |
| 104 |
$schd_code, |
| 105 |
$crse_title, |
| 106 |
$credit_hrs, |
| 107 |
$bill_hrs, |
| 108 |
$gmod_code, |
| 109 |
$gradable, |
| 110 |
$max_enrl, |
| 111 |
$enrl, |
| 112 |
$seats_avail, |
| 113 |
$lec_hr, |
| 114 |
$lab_hr, |
| 115 |
$oth_hr, |
| 116 |
$cont_hr, |
| 117 |
$cip, |
| 118 |
$start_date, |
| 119 |
$end_date, |
| 120 |
$campus ) = $sth_qry_term_sections->fetchrow_array ) { |
| 121 |
|
| 122 |
push @term_sections, { "term" => $term, |
| 123 |
"crn" => $crn, |
| 124 |
"ptrm" => $ptrm, |
| 125 |
"subj" => $subj, |
| 126 |
"crse" => $crse, |
| 127 |
"ssts_code" => $ssts_code, |
| 128 |
"schd_code" => $schd_code, |
| 129 |
"crse_title" => $crse_title, |
| 130 |
"credit_hrs" => $credit_hrs, |
| 131 |
"bill_hrs" => $bill_hrs, |
| 132 |
"gmod_code" => $gmod_code, |
| 133 |
"gradable" => $gradable, |
| 134 |
"max_enrl" => $max_enrl, |
| 135 |
"enrl" => $enrl, |
| 136 |
"seats_avail" => $seats_avail, |
| 137 |
"lec_hr" => $lec_hr, |
| 138 |
"lab_hr" => $lab_hr, |
| 139 |
"oth_hr" => $oth_hr, |
| 140 |
"cont_hr" => $cont_hr, |
| 141 |
"cip" => $cip, |
| 142 |
"start_date" => $start_date, |
| 143 |
"end_date" => $end_date, |
| 144 |
"campus" => $campus }; |
| 145 |
} |
| 146 |
return \@term_sections; |
| 147 |
} |
| 148 |
|
| 149 |
|
| 150 |
sub term_schedule { |
| 151 |
my ($dbh, $term) = @_; |
| 152 |
|
| 153 |
return join_table_subs( \&term_sections, |
| 154 |
[ $dbh, $term ], |
| 155 |
\§ion_meet, |
| 156 |
[ $dbh, $term, "\$crn" ], |
| 157 |
'Y' ); # final parm means do outer join; |
| 158 |
# this returns schedule rows even |
| 159 |
# if no time and room assigned |
| 160 |
} |
| 161 |
|
| 162 |
|
| 163 |
# ======================================================== |
| 164 |
# section - returns info about a course section |
| 165 |
# ======================================================== |
| 166 |
sub section { |
| 167 |
my ($dbh, $term, $crn) = @_; |
| 168 |
|
| 169 |
my ( $ptrm, $subj, $crse, $ssts_code, $schd_code, $crse_title, $credit_hrs, |
| 170 |
$bill_hrs, $gmod_code, $gradable, $max_enrl, $enrl, $seats_avail, |
| 171 |
$lec_hr, $lab_hr, $oth_hr, $cont_hr, $cip, $start_date, $end_date, |
| 172 |
$campus ); |
| 173 |
|
| 174 |
# for performance we will only prepare repeated sql once |
| 175 |
if (exists $glb_prepared_sql{"sth_qry_section"} ) { |
| 176 |
$sth_qry_section = $glb_prepared_sql{"sth_qry_section"}; |
| 177 |
} |
| 178 |
else { |
| 179 |
$sth_qry_section = $dbh->prepare( q{ |
| 180 |
select SSBSECT_PTRM_CODE, |
| 181 |
SSBSECT_SUBJ_CODE, |
| 182 |
SSBSECT_CRSE_NUMB, |
| 183 |
SSBSECT_SSTS_CODE, |
| 184 |
SSBSECT_SCHD_CODE, |
| 185 |
nvl(SSBSECT_CRSE_TITLE, scbcrse_title), |
| 186 |
SSBSECT_CREDIT_HRS, |
| 187 |
SSBSECT_BILL_HRS, |
| 188 |
SSBSECT_GMOD_CODE, |
| 189 |
SSBSECT_GRADABLE_IND, |
| 190 |
SSBSECT_MAX_ENRL, |
| 191 |
SSBSECT_ENRL, |
| 192 |
SSBSECT_SEATS_AVAIL, |
| 193 |
SSBSECT_LEC_HR, |
| 194 |
SSBSECT_LAB_HR, |
| 195 |
SSBSECT_OTH_HR, |
| 196 |
SSBSECT_CONT_HR, |
| 197 |
scbcrse_cipc_code, |
| 198 |
to_char(ssbsect_ptrm_start_date, 'mm/dd/yyyy'), |
| 199 |
to_char(ssbsect_ptrm_end_date, 'mm/dd/yyyy'), |
| 200 |
ssbsect_camp_code |
| 201 |
from ssbsect, |
| 202 |
scbcrse |
| 203 |
where ssbsect_term_code = ? AND |
| 204 |
ssbsect_crn = ? AND |
| 205 |
scbcrse_subj_code = ssbsect_subj_code AND |
| 206 |
scbcrse_crse_numb = ssbsect_crse_numb AND |
| 207 |
scbcrse_eff_term = ( |
| 208 |
select max(scbcrse_eff_term) |
| 209 |
from scbcrse |
| 210 |
where scbcrse_subj_code = ssbsect_subj_code and |
| 211 |
scbcrse_crse_numb = ssbsect_crse_numb and |
| 212 |
scbcrse_eff_term <= ssbsect_term_code) } ); |
| 213 |
|
| 214 |
$glb_prepared_sql{"sth_qry_section"} = $sth_qry_section; |
| 215 |
} |
| 216 |
|
| 217 |
$sth_qry_section->execute($term, $crn); |
| 218 |
|
| 219 |
( $ptrm, |
| 220 |
$subj, |
| 221 |
$crse, |
| 222 |
$ssts_code, |
| 223 |
$schd_code, |
| 224 |
$crse_title, |
| 225 |
$credit_hrs, |
| 226 |
$bill_hrs, |
| 227 |
$gmod_code, |
| 228 |
$gradable, |
| 229 |
$max_enrl, |
| 230 |
$enrl, |
| 231 |
$seats_avail, |
| 232 |
$lec_hr, |
| 233 |
$lab_hr, |
| 234 |
$oth_hr, |
| 235 |
$cont_hr, |
| 236 |
$cip, |
| 237 |
$start_date, |
| 238 |
$end_date, |
| 239 |
$campus ) = $sth_qry_section->fetchrow_array; |
| 240 |
|
| 241 |
return { "crn" => $crn, |
| 242 |
"ptrm" => $ptrm, |
| 243 |
"subj" => $subj, |
| 244 |
"crse" => $crse, |
| 245 |
"ssts_code" => $ssts_code, |
| 246 |
"schd_code" => $schd_code, |
| 247 |
"crse_title" => $crse_title, |
| 248 |
"credit_hrs" => $credit_hrs, |
| 249 |
"bill_hrs" => $bill_hrs, |
| 250 |
"gmod_code" => $gmod_code, |
| 251 |
"gradable" => $gradable, |
| 252 |
"max_enrl" => $max_enrl, |
| 253 |
"enrl" => $enrl, |
| 254 |
"seats_avail" => $seats_avail, |
| 255 |
"lec_hr" => $lec_hr, |
| 256 |
"lab_hr" => $lab_hr, |
| 257 |
"oth_hr" => $oth_hr, |
| 258 |
"cont_hr" => $cont_hr, |
| 259 |
"cip" => $cip, |
| 260 |
"start_date" => $start_date, |
| 261 |
"end_date" => $end_date, |
| 262 |
"campus" => $campus }; |
| 263 |
} |
| 264 |
|
| 265 |
|
| 266 |
sub instructor { |
| 267 |
my ($dbh, $term, $crn) = @_; |
| 268 |
|
| 269 |
my (@instructor, $instr_pidm, $pcnt, $primary, $rh_instr); |
| 270 |
|
| 271 |
# for performance we will only prepare repeated sql once |
| 272 |
if (exists $glb_prepared_sql{"sth_qry_instructor"} ) { |
| 273 |
$sth_qry_instructor = $glb_prepared_sql{"sth_qry_instructor"}; |
| 274 |
} |
| 275 |
else { |
| 276 |
$sth_qry_instructor = $dbh->prepare( q{ |
| 277 |
select sirasgn_pidm, |
| 278 |
sirasgn_percent_sess, |
| 279 |
sirasgn_primary_ind |
| 280 |
from sirasgn |
| 281 |
where sirasgn_term_code = ? and |
| 282 |
sirasgn_crn = ? and |
| 283 |
sirasgn_primary_ind = 'Y' } ); |
| 284 |
|
| 285 |
$glb_prepared_sql{"sth_qry_instructor"} = $sth_qry_instructor; |
| 286 |
} |
| 287 |
|
| 288 |
$sth_qry_instructor->execute($term, $crn); |
| 289 |
|
| 290 |
while ( ( $instr_pidm, $pcnt, $primary ) = |
| 291 |
$sth_qry_instructor->fetchrow_array ) { |
| 292 |
|
| 293 |
$rh_instr = { "crn" => $crn, |
| 294 |
"pidm" => $instr_pidm, |
| 295 |
"pc_instruct" => $pcnt, |
| 296 |
"primary" => $primary }; |
| 297 |
|
| 298 |
add2hash($rh_instr, id($dbh, $instr_pidm) ); |
| 299 |
|
| 300 |
$rh_instr->{ssn} = real_ssn($dbh, $instr_pidm); |
| 301 |
|
| 302 |
push @instructor, $rh_instr; |
| 303 |
|
| 304 |
} |
| 305 |
|
| 306 |
return \@instructor; |
| 307 |
} |
| 308 |
|
| 309 |
|
| 310 |
sub primary_instructor { |
| 311 |
my ($dbh, $term, $crn) = @_; |
| 312 |
|
| 313 |
my ($ra_instr, $rh_instr); |
| 314 |
|
| 315 |
$ra_instr = instructor($dbh, $term, $crn); |
| 316 |
|
| 317 |
while ( $rh_instr = shift @{ $ra_instr } ) { |
| 318 |
return $rh_instr if $rh_instr->{primary} eq "Y"; |
| 319 |
} |
| 320 |
|
| 321 |
# fall-through, shouldn't get here |
| 322 |
return { }; |
| 323 |
} |
| 324 |
|
| 325 |
|
| 326 |
sub section_meet { |
| 327 |
my ($dbh, $term, $crn) = @_; |
| 328 |
|
| 329 |
my ( @section_meet, $beg_time, $end_time, $sunday, $monday, $tuesday, |
| 330 |
$wednesday, $thursday, $friday, $saturday, $start_date, $end_date, |
| 331 |
$bldg, $room, $days, $time, $capacity ); |
| 332 |
|
| 333 |
# for performance we will only prepare repeated sql once |
| 334 |
if (exists $glb_prepared_sql{"sth_qry_section_meet"} ) { |
| 335 |
$sth_qry_section_meet = $glb_prepared_sql{"sth_qry_section_meet"}; |
| 336 |
} |
| 337 |
else { |
| 338 |
$sth_qry_section_meet = $dbh->prepare( q{ |
| 339 |
select ssrmeet_begin_time, |
| 340 |
ssrmeet_end_time, |
| 341 |
ssrmeet_sun_day, |
| 342 |
ssrmeet_mon_day, |
| 343 |
ssrmeet_tue_day, |
| 344 |
ssrmeet_wed_day, |
| 345 |
ssrmeet_thu_day, |
| 346 |
ssrmeet_fri_day, |
| 347 |
ssrmeet_sat_day, |
| 348 |
to_char(ssrmeet_start_date, 'mm/dd/yyyy'), |
| 349 |
to_char(ssrmeet_end_date, 'mm/dd/yyyy'), |
| 350 |
ssrmeet_bldg_code, |
| 351 |
ssrmeet_room_code, |
| 352 |
slbrdef_capacity |
| 353 |
from ssrmeet, |
| 354 |
slbrdef |
| 355 |
where ssrmeet_term_code = ? and |
| 356 |
ssrmeet_crn = ? and |
| 357 |
ssrmeet_begin_time is not null and |
| 358 |
ssrmeet_end_time is not null and |
| 359 |
ssrmeet_bldg_code is not null and |
| 360 |
ssrmeet_room_code is not null and |
| 361 |
slbrdef_bldg_code (+)= ssrmeet_bldg_code and |
| 362 |
slbrdef_room_number (+)= ssrmeet_room_code } ); |
| 363 |
|
| 364 |
$glb_prepared_sql{"sth_qry_section_meet"} = $sth_qry_section_meet; |
| 365 |
} |
| 366 |
|
| 367 |
$sth_qry_section_meet->execute($term, $crn); |
| 368 |
|
| 369 |
while ( ( $beg_time, $end_time, $sunday, $monday, $tuesday, $wednesday, |
| 370 |
$thursday, $friday, $saturday, $start_date, $end_date, $bldg, |
| 371 |
$room, $capacity ) = $sth_qry_section_meet->fetchrow_array ) { |
| 372 |
|
| 373 |
# do this before assignment of Y/N values below |
| 374 |
$days = $sunday ? $sunday : '-'; |
| 375 |
$days .= $monday ? $monday : '-'; |
| 376 |
$days .= $tuesday ? $tuesday : '-'; |
| 377 |
$days .= $wednesday ? $wednesday : '-'; |
| 378 |
$days .= $thursday ? $thursday : '-'; |
| 379 |
$days .= $friday ? $friday : '-'; |
| 380 |
$days .= $saturday ? $saturday : '-'; |
| 381 |
|
| 382 |
$sunday = $sunday ? "Y" : "N"; |
| 383 |
$monday = $monday ? "Y" : "N"; |
| 384 |
$tuesday = $tuesday ? "Y" : "N"; |
| 385 |
$wednesday = $wednesday ? "Y" : "N"; |
| 386 |
$thursday = $thursday ? "Y" : "N"; |
| 387 |
$friday = $friday ? "Y" : "N"; |
| 388 |
$saturday = $saturday ? "Y" : "N"; |
| 389 |
|
| 390 |
|
| 391 |
$beg_time = substr($beg_time, 0, 2) . ":" . substr($beg_time, 2); |
| 392 |
$end_time = substr($end_time, 0, 2) . ":" . substr($end_time, 2); |
| 393 |
|
| 394 |
$time = $beg_time . '-' . $end_time; |
| 395 |
$time =~ s/://g; |
| 396 |
|
| 397 |
push @section_meet, { "crn" => $crn, |
| 398 |
"beg_time" => $beg_time, |
| 399 |
"end_time" => $end_time, |
| 400 |
"sunday" => $sunday, |
| 401 |
"monday" => $monday, |
| 402 |
"tuesday" => $tuesday, |
| 403 |
"wednesday" => $wednesday, |
| 404 |
"thursday" => $thursday, |
| 405 |
"friday" => $friday, |
| 406 |
"saturday" => $saturday, |
| 407 |
"start_date" => $start_date, |
| 408 |
"end_date" => $end_date, |
| 409 |
"bldg" => $bldg, |
| 410 |
"room" => $room, |
| 411 |
"days" => $days, |
| 412 |
"time" => $time, |
| 413 |
"capacity" => $capacity }; |
| 414 |
} |
| 415 |
|
| 416 |
return \@section_meet; |
| 417 |
} |
| 418 |
|
| 419 |
|
| 420 |
# ============================================================================ |
| 421 |
# Subroutines |
| 422 |
# ============================================================================ |
| 423 |
|
| 424 |
return 1; |
| 425 |
|
Parent Directory
| 
Revision Log