/[vdw]/trunk/WOU_Schedule.pm
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Annotation of /trunk/WOU_Schedule.pm

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1 - (hide annotations)
Sun Feb 6 05:28:38 2005 UTC (19 years, 3 months ago) by dpavlin
File size: 14948 byte(s)
initial import into svn

1 dpavlin 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 &section &instructor &section_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     \&section_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    

  ViewVC Help
Powered by ViewVC 1.1.26