1 |
dpavlin |
1 |
package WOU_Admit; |
2 |
|
|
|
3 |
|
|
# Routines for Banner SIS Admit |
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/11/2002 |
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(&admit &admit_for_term &tests &hs_gpa &appls_for_term |
20 |
|
|
&app_decision &appl &app_dec_detl &hs_subj &tr_coll |
21 |
|
|
&scarf_tests &term_admits &SAT &ACT &last_tr_coll |
22 |
|
|
&term_recruits &recruit &recr_status &sbgi_info &coll_info |
23 |
|
|
&app_decsn &contact); |
24 |
|
|
%EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ], |
25 |
|
|
@EXPORT_OK = qw( ); |
26 |
|
|
|
27 |
|
|
( grep /gen\$com/i, @INC ) || unshift @INC, "gen\$com"; |
28 |
|
|
} |
29 |
|
|
use vars @EXPORT_OK; |
30 |
|
|
use subs qw(admit admit_for_term tests hs_gpa appls_for_term app_decision appl |
31 |
|
|
app_dec_detl hs_subj tr_coll scarf_tests term_admits SAT ACT |
32 |
|
|
last_tr_coll term_recruits recruit recr_status sbgi_info coll_info |
33 |
|
|
app_decsn contact); |
34 |
|
|
|
35 |
|
|
use DBI; |
36 |
|
|
use WOU_Util; |
37 |
|
|
|
38 |
|
|
|
39 |
|
|
# ============================================================================ |
40 |
|
|
# Package-Level Stuff |
41 |
|
|
# ============================================================================ |
42 |
|
|
my %glb_prepared_sql; # Hash that holds $sth's for prepared sql; |
43 |
|
|
# helps performance. Entries are of the form: |
44 |
|
|
# ( "sth_qry_admit" => $sth_qry_admit, ... ) |
45 |
|
|
|
46 |
|
|
my ($sth_qry_admit, $sth_qry_appl, $sth_qry_admit_for_term, $sth_qry_tests, |
47 |
|
|
$sth_qry_hs_gpa, $sth_qry_appls_for_term1, $sth_qry_appls_for_term2, |
48 |
|
|
$sth_qry_app_decision, $sth_qry_hs_subj, $sth_qry_tr_coll1, |
49 |
|
|
$sth_qry_tr_coll2, $sth_qry_scarf_tests, $sth_qry_term_admits, |
50 |
|
|
$sth_qry_recr_status1, $sth_qry_recr_status2, $sth_qry_sbgi_info, |
51 |
|
|
$sth_qry_recruit, $sth_qry_contact); |
52 |
|
|
|
53 |
|
|
|
54 |
|
|
# ============================================================================ |
55 |
|
|
# Routines |
56 |
|
|
# ============================================================================ |
57 |
|
|
|
58 |
|
|
sub admit { |
59 |
|
|
my ($dbh, $pidm, $levl, $term) = @_; |
60 |
|
|
|
61 |
|
|
my (@row, $appl_term, $level, $app_decision, %applications, $ra_row, |
62 |
|
|
$min_term); |
63 |
|
|
|
64 |
|
|
# for performance we will only prepare repeated sql once |
65 |
|
|
if (exists $glb_prepared_sql{"sth_qry_admit"} ) { |
66 |
|
|
$sth_qry_admit = $glb_prepared_sql{"sth_qry_admit"}; |
67 |
|
|
} |
68 |
|
|
else { |
69 |
|
|
# add fields as needed |
70 |
|
|
$sth_qry_admit = $dbh->prepare( q{ |
71 |
|
|
select sarappd_term_code_entry, |
72 |
|
|
saradap_levl_code, |
73 |
|
|
sarappd_apdc_code |
74 |
|
|
from saradap a, |
75 |
|
|
sarappd |
76 |
|
|
where saradap_pidm = ? AND |
77 |
|
|
decode(saradap_levl_code, 'GR', 'GR', |
78 |
|
|
'UG') = ? AND |
79 |
|
|
saradap_term_code_entry <= ? AND |
80 |
|
|
saradap_appl_no = ( |
81 |
|
|
select max(saradap_appl_no) |
82 |
|
|
from saradap b |
83 |
|
|
where b.saradap_pidm = a.saradap_pidm and |
84 |
|
|
b.saradap_term_code_entry = a.saradap_term_code_entry |
85 |
|
|
and |
86 |
|
|
decode(b.saradap_levl_code, 'GR', 'GR', |
87 |
|
|
'UG') = |
88 |
|
|
decode(a.saradap_levl_code, 'GR', 'GR', |
89 |
|
|
'UG') ) AND |
90 |
|
|
sarappd_pidm = saradap_pidm AND |
91 |
|
|
sarappd_term_code_entry = saradap_term_code_entry AND |
92 |
|
|
sarappd_appl_no = saradap_appl_no AND |
93 |
|
|
TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
94 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0') = ( |
95 |
|
|
SELECT MAX(TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
96 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0')) |
97 |
|
|
FROM sarappd |
98 |
|
|
WHERE sarappd_pidm = saradap_pidm and |
99 |
|
|
sarappd_term_code_entry = saradap_term_code_entry and |
100 |
|
|
sarappd_appl_no = saradap_appl_no) } ); |
101 |
|
|
|
102 |
|
|
$glb_prepared_sql{"sth_qry_admit"} = $sth_qry_admit; |
103 |
|
|
} |
104 |
|
|
|
105 |
|
|
$sth_qry_admit->execute($pidm, $levl, $term); |
106 |
|
|
|
107 |
|
|
# handle multiple applications for admission |
108 |
|
|
while (@row = $sth_qry_admit->fetchrow_array) { |
109 |
|
|
|
110 |
|
|
($appl_term, $level, $app_decision) = @row; |
111 |
|
|
|
112 |
|
|
# track min term admitted |
113 |
|
|
if ( (!defined($min_term) ) || $appl_term < $min_term) { |
114 |
|
|
if (substr($app_decision, 0, 1) eq "A") { $min_term = $appl_term } |
115 |
|
|
} |
116 |
|
|
|
117 |
|
|
$ra_row = [ ]; # alloc new memory |
118 |
|
|
push @{$ra_row}, @row; |
119 |
|
|
$applications{$appl_term} = $ra_row; |
120 |
|
|
} |
121 |
|
|
|
122 |
|
|
if (defined($min_term) ) { |
123 |
|
|
|
124 |
|
|
($appl_term, $level, $app_decision) = @{$applications{$min_term} }; |
125 |
|
|
|
126 |
|
|
return { "admit_term" => $appl_term, |
127 |
|
|
"level" => $level, |
128 |
|
|
"decision" => $app_decision }; # return ref to anon hash |
129 |
|
|
} |
130 |
|
|
|
131 |
|
|
# fall-through means not admitted |
132 |
|
|
return; |
133 |
|
|
} |
134 |
|
|
|
135 |
|
|
|
136 |
|
|
sub appl { |
137 |
|
|
my ($dbh, $pidm, $levl, $term) = @_; |
138 |
|
|
|
139 |
|
|
my ($appl_term, $level, $app_decision, %applications, $ra_row, |
140 |
|
|
$min_term, @today, $today, $appl_no, $rh_app_dec, $last_appl_term); |
141 |
|
|
|
142 |
|
|
# for performance we will only prepare repeated sql once |
143 |
|
|
if (exists $glb_prepared_sql{"sth_qry_appl"} ) { |
144 |
|
|
$sth_qry_appl = $glb_prepared_sql{"sth_qry_appl"}; |
145 |
|
|
} |
146 |
|
|
else { |
147 |
|
|
# add fields as needed |
148 |
|
|
$sth_qry_appl = $dbh->prepare( q{ |
149 |
|
|
select saradap_term_code_entry, |
150 |
|
|
saradap_levl_code, |
151 |
|
|
saradap_appl_no |
152 |
|
|
from saradap a |
153 |
|
|
where saradap_pidm = ? AND |
154 |
|
|
decode(saradap_levl_code, 'GR', 'GR', |
155 |
|
|
'UG') = ? AND |
156 |
|
|
saradap_term_code_entry <= ? AND |
157 |
|
|
saradap_appl_no = ( |
158 |
|
|
select max(saradap_appl_no) |
159 |
|
|
from saradap b |
160 |
|
|
where b.saradap_pidm = a.saradap_pidm and |
161 |
|
|
b.saradap_term_code_entry = a.saradap_term_code_entry |
162 |
|
|
and |
163 |
|
|
decode(b.saradap_levl_code, 'GR', 'GR', |
164 |
|
|
'UG') = |
165 |
|
|
decode(a.saradap_levl_code, 'GR', 'GR', |
166 |
|
|
'UG') ) |
167 |
|
|
order by saradap_term_code_entry } ); |
168 |
|
|
|
169 |
|
|
$glb_prepared_sql{"sth_qry_appl"} = $sth_qry_appl; |
170 |
|
|
|
171 |
|
|
} |
172 |
|
|
|
173 |
|
|
$sth_qry_appl->execute($pidm, $levl, $term); |
174 |
|
|
|
175 |
|
|
@today = localtime; |
176 |
|
|
$today = sprintf "%.2d/%.2d/%.4d", |
177 |
|
|
$today[4] + 1, |
178 |
|
|
$today[3], |
179 |
|
|
1900 + $today[5]; |
180 |
|
|
|
181 |
|
|
# handle multiple applications for admission |
182 |
|
|
# take the min term admitted, otherwise the max term applied |
183 |
|
|
while ( ($appl_term, $level, $appl_no) = $sth_qry_appl->fetchrow_array ) { |
184 |
|
|
|
185 |
|
|
$last_appl_term = $appl_term; |
186 |
|
|
|
187 |
|
|
$rh_app_dec = app_dec_detl($dbh, $pidm, $appl_term, $appl_no, $today); |
188 |
|
|
|
189 |
|
|
# track min term admitted |
190 |
|
|
if ( !defined($min_term) ) { |
191 |
|
|
if ( $rh_app_dec->{accepted} ) { $min_term = $appl_term } |
192 |
|
|
} |
193 |
|
|
|
194 |
|
|
push @{ $applications{$appl_term} }, |
195 |
|
|
($appl_term, $level, $rh_app_dec->{decision} ); |
196 |
|
|
} |
197 |
|
|
|
198 |
|
|
if (defined($min_term) ) { |
199 |
|
|
($appl_term, $level, $app_decision) = @{$applications{$min_term} }; |
200 |
|
|
} |
201 |
|
|
else { |
202 |
|
|
# student may not have been admitted in SAAADMS |
203 |
|
|
if ( defined($last_appl_term) ) { |
204 |
|
|
($appl_term, $level, $app_decision) = |
205 |
|
|
@{$applications{$last_appl_term} }; |
206 |
|
|
} |
207 |
|
|
else { ($appl_term, $level, $app_decision) = ("", "", "") } |
208 |
|
|
} |
209 |
|
|
|
210 |
|
|
return { "admit_term" => $appl_term, |
211 |
|
|
"level" => $level, |
212 |
|
|
"decision" => $app_decision }; |
213 |
|
|
} |
214 |
|
|
|
215 |
|
|
|
216 |
|
|
sub admit_for_term { |
217 |
|
|
my ($dbh, $stu_pidm, $level, $term) = @_; |
218 |
|
|
|
219 |
|
|
my (@row, $decision, $apdc_date, $appl_date, $admt_code, $accepted, $resd); |
220 |
|
|
|
221 |
|
|
# for performance we will only prepare repeated sql once |
222 |
|
|
if (exists $glb_prepared_sql{"sth_qry_admit_for_term"} ) { |
223 |
|
|
$sth_qry_admit_for_term = $glb_prepared_sql{"sth_qry_admit_for_term"}; |
224 |
|
|
} |
225 |
|
|
else { |
226 |
|
|
# add fields to below as needed |
227 |
|
|
$sth_qry_admit_for_term = $dbh->prepare( q{ |
228 |
|
|
select sarappd_apdc_code, |
229 |
|
|
to_char(sarappd_apdc_date, 'mm/dd/yyyy'), |
230 |
|
|
to_char(saradap_appl_date, 'mm/dd/yyyy'), |
231 |
|
|
saradap_admt_code, |
232 |
|
|
decode(nvl(stvapdc_inst_acc_ind, 0), 'Y', 1, 0), |
233 |
|
|
saradap_resd_code |
234 |
|
|
from saradap a, |
235 |
|
|
sarappd, |
236 |
|
|
stvapdc |
237 |
|
|
where saradap_pidm = ? AND |
238 |
|
|
saradap_levl_code = ? AND |
239 |
|
|
saradap_term_code_entry = ? AND |
240 |
|
|
saradap_appl_no = ( |
241 |
|
|
select max(saradap_appl_no) |
242 |
|
|
from saradap b |
243 |
|
|
where b.saradap_pidm = a.saradap_pidm and |
244 |
|
|
b.saradap_term_code_entry = a.saradap_term_code_entry) |
245 |
|
|
AND |
246 |
|
|
sarappd_pidm = saradap_pidm AND |
247 |
|
|
sarappd_term_code_entry = saradap_term_code_entry AND |
248 |
|
|
sarappd_appl_no = saradap_appl_no AND |
249 |
|
|
TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
250 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0') = ( |
251 |
|
|
SELECT MAX(TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
252 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0')) |
253 |
|
|
FROM sarappd |
254 |
|
|
WHERE sarappd_pidm = saradap_pidm and |
255 |
|
|
sarappd_term_code_entry = saradap_term_code_entry and |
256 |
|
|
sarappd_appl_no = saradap_appl_no) AND |
257 |
|
|
stvapdc_code = sarappd_apdc_code } ); |
258 |
|
|
|
259 |
|
|
$glb_prepared_sql{"sth_qry_admit_for_term"} = $sth_qry_admit_for_term; |
260 |
|
|
} |
261 |
|
|
|
262 |
|
|
$sth_qry_admit_for_term->execute($stu_pidm, $level, $term); |
263 |
|
|
|
264 |
|
|
if (@row = $sth_qry_admit_for_term->fetchrow_array) { |
265 |
|
|
|
266 |
|
|
($decision, $apdc_date, $appl_date, $admt_code, $accepted, |
267 |
|
|
$resd) = @row; |
268 |
|
|
|
269 |
|
|
# add fields to below as needed |
270 |
|
|
return { "pidm" => $stu_pidm, |
271 |
|
|
"level" => $level, |
272 |
|
|
"decision" => $decision, |
273 |
|
|
"decision_date" => $apdc_date, |
274 |
|
|
"appl_date" => $appl_date, |
275 |
|
|
"admt_code" => $admt_code, |
276 |
|
|
"accepted" => $accepted, |
277 |
|
|
"resident" => $resd }; |
278 |
|
|
} |
279 |
|
|
|
280 |
|
|
# fall-through means no admission info |
281 |
|
|
return; |
282 |
|
|
} |
283 |
|
|
|
284 |
|
|
sub term_admits { |
285 |
|
|
my ($dbh, $term, $level) = @_; |
286 |
|
|
|
287 |
|
|
my (@row, $decision, $apdc_date, $appl_date, $admt_code, $accepted, $resd, |
288 |
|
|
$pidm, @term_admits, $levl); |
289 |
|
|
|
290 |
|
|
# for performance we will only prepare repeated sql once |
291 |
|
|
if (exists $glb_prepared_sql{"sth_qry_term_admits"} ) { |
292 |
|
|
$sth_qry_term_admits = $glb_prepared_sql{"sth_qry_term_admits"}; |
293 |
|
|
} |
294 |
|
|
else { |
295 |
|
|
# add fields to below as needed |
296 |
|
|
$sth_qry_term_admits = $dbh->prepare( q{ |
297 |
|
|
select sarappd_pidm, |
298 |
|
|
saradap_levl_code, |
299 |
|
|
sarappd_apdc_code, |
300 |
|
|
to_char(sarappd_apdc_date, 'mm/dd/yyyy'), |
301 |
|
|
to_char(saradap_appl_date, 'mm/dd/yyyy'), |
302 |
|
|
saradap_admt_code, |
303 |
|
|
decode(nvl(stvapdc_inst_acc_ind, 0), 'Y', 1, 0), |
304 |
|
|
saradap_resd_code |
305 |
|
|
from saradap a, |
306 |
|
|
sarappd, |
307 |
|
|
stvapdc |
308 |
|
|
where saradap_levl_code like ? AND |
309 |
|
|
saradap_term_code_entry = ? AND |
310 |
|
|
saradap_appl_no = ( |
311 |
|
|
select max(saradap_appl_no) |
312 |
|
|
from saradap b |
313 |
|
|
where b.saradap_pidm = a.saradap_pidm and |
314 |
|
|
b.saradap_term_code_entry = a.saradap_term_code_entry) |
315 |
|
|
AND |
316 |
|
|
sarappd_pidm = saradap_pidm AND |
317 |
|
|
sarappd_term_code_entry = saradap_term_code_entry AND |
318 |
|
|
sarappd_appl_no = saradap_appl_no AND |
319 |
|
|
TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
320 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0') = ( |
321 |
|
|
SELECT MAX(TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
322 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0')) |
323 |
|
|
FROM sarappd |
324 |
|
|
WHERE sarappd_pidm = saradap_pidm and |
325 |
|
|
sarappd_term_code_entry = saradap_term_code_entry and |
326 |
|
|
sarappd_appl_no = saradap_appl_no) AND |
327 |
|
|
stvapdc_code = sarappd_apdc_code } ); |
328 |
|
|
|
329 |
|
|
$glb_prepared_sql{"sth_qry_term_admits"} = $sth_qry_term_admits; |
330 |
|
|
} |
331 |
|
|
|
332 |
|
|
$sth_qry_term_admits->execute($level, $term); |
333 |
|
|
|
334 |
|
|
while ( (@row = $sth_qry_term_admits->fetchrow_array) ) { |
335 |
|
|
|
336 |
|
|
($pidm, $levl, $decision, $apdc_date, $appl_date, $admt_code, $accepted, |
337 |
|
|
$resd) = @row; |
338 |
|
|
|
339 |
|
|
# add fields to below as needed |
340 |
|
|
push @term_admits, { "pidm" => $pidm, |
341 |
|
|
"term" => $term, |
342 |
|
|
"level" => $levl, |
343 |
|
|
"decision" => $decision, |
344 |
|
|
"decision_date" => $apdc_date, |
345 |
|
|
"appl_date" => $appl_date, |
346 |
|
|
"admt_code" => $admt_code, |
347 |
|
|
"accepted" => $accepted, |
348 |
|
|
"accepted_flg" => $accepted ? "Y" : "N", |
349 |
|
|
"resident" => $resd }; |
350 |
|
|
} |
351 |
|
|
|
352 |
|
|
return \@term_admits; |
353 |
|
|
} |
354 |
|
|
|
355 |
|
|
|
356 |
|
|
sub appls_for_term { |
357 |
|
|
my ($dbh, $term, $level) = @_; |
358 |
|
|
|
359 |
|
|
my ($pidm, $levl, $resd, $appl_date, $admt_code, $appl_no, $state, $sbgi, |
360 |
|
|
$college, @appls, $major); |
361 |
|
|
|
362 |
|
|
# for performance we will only prepare repeated sql once |
363 |
|
|
if (exists $glb_prepared_sql{"sth_qry_appls_for_term1"} ) { |
364 |
|
|
$sth_qry_appls_for_term1 = $glb_prepared_sql{"sth_qry_appls_for_term1"}; |
365 |
|
|
} |
366 |
|
|
else { |
367 |
|
|
|
368 |
|
|
# add fields to below as needed |
369 |
|
|
$sth_qry_appls_for_term1 = $dbh->prepare( q{ |
370 |
|
|
select saradap_pidm, |
371 |
|
|
saradap_levl_code, |
372 |
|
|
saradap_resd_code, |
373 |
|
|
to_char(saradap_appl_date, 'mm/dd/yyyy'), |
374 |
|
|
saradap_admt_code, |
375 |
|
|
saradap_appl_no, |
376 |
|
|
stvmajr_desc |
377 |
|
|
from saradap a, |
378 |
|
|
stvmajr |
379 |
|
|
where saradap_term_code_entry = ? AND |
380 |
|
|
saradap_levl_code like ? AND |
381 |
|
|
saradap_appl_no = ( |
382 |
|
|
select max(saradap_appl_no) |
383 |
|
|
from saradap b |
384 |
|
|
where b.saradap_pidm = a.saradap_pidm and |
385 |
|
|
b.saradap_term_code_entry = a.saradap_term_code_entry) |
386 |
|
|
AND |
387 |
|
|
stvmajr_code (+)= saradap_majr_code_1 } ); |
388 |
|
|
|
389 |
|
|
$glb_prepared_sql{"sth_qry_appls_for_term1"} = $sth_qry_appls_for_term1; |
390 |
|
|
} |
391 |
|
|
|
392 |
|
|
# for performance we will only prepare repeated sql once |
393 |
|
|
if (exists $glb_prepared_sql{"sth_qry_appls_for_term2"} ) { |
394 |
|
|
$sth_qry_appls_for_term2 = $glb_prepared_sql{"sth_qry_appls_for_term2"}; |
395 |
|
|
} |
396 |
|
|
else { |
397 |
|
|
|
398 |
|
|
# add fields to below as needed |
399 |
|
|
$sth_qry_appls_for_term2 = $dbh->prepare( q{ |
400 |
|
|
select shrtrit_sbgi_code, |
401 |
|
|
stvsbgi_desc, |
402 |
|
|
nvl(sobsbgi_stat_code, '') |
403 |
|
|
from shrtrit a, |
404 |
|
|
sobsbgi, |
405 |
|
|
stvsbgi |
406 |
|
|
where shrtrit_pidm = ? AND |
407 |
|
|
shrtrit_seq_no = ( |
408 |
|
|
select max(shrtrit_seq_no) |
409 |
|
|
from shrtrit b |
410 |
|
|
where b.shrtrit_pidm = a.shrtrit_pidm) AND |
411 |
|
|
sobsbgi_sbgi_code (+)= shrtrit_sbgi_code AND |
412 |
|
|
stvsbgi_code = shrtrit_sbgi_code } ); |
413 |
|
|
|
414 |
|
|
$glb_prepared_sql{"sth_qry_appls_for_term2"} = $sth_qry_appls_for_term2; |
415 |
|
|
} |
416 |
|
|
|
417 |
|
|
$sth_qry_appls_for_term1->execute($term, $level); |
418 |
|
|
|
419 |
|
|
while ( ($pidm, $levl, $resd, $appl_date, $admt_code, $appl_no, $major) = |
420 |
|
|
$sth_qry_appls_for_term1->fetchrow_array ) { |
421 |
|
|
|
422 |
|
|
$sth_qry_appls_for_term2->execute($pidm); |
423 |
|
|
($sbgi, $college, $state) = $sth_qry_appls_for_term2->fetchrow_array; |
424 |
|
|
|
425 |
|
|
# add fields to below as needed |
426 |
|
|
push @appls, { "pidm" => $pidm, |
427 |
|
|
"term" => $term, |
428 |
|
|
"level" => $levl, |
429 |
|
|
"residence" => $resd, |
430 |
|
|
"appl_date" => $appl_date, |
431 |
|
|
"admt_code" => $admt_code, |
432 |
|
|
"appl_no" => $appl_no, |
433 |
|
|
"major" => $major, |
434 |
|
|
"college_code" => defined($sbgi) ? $sbgi : "", |
435 |
|
|
"college" => defined($college) ? $college : "", |
436 |
|
|
"college_state" => defined($state) ? $state : "" }; |
437 |
|
|
} |
438 |
|
|
|
439 |
|
|
return \@appls; |
440 |
|
|
} |
441 |
|
|
|
442 |
|
|
|
443 |
|
|
sub app_decision { |
444 |
|
|
my ($dbh, $pidm, $term, $appl_no, $max_date) = @_; |
445 |
|
|
|
446 |
|
|
my ($decision, $accepted); |
447 |
|
|
|
448 |
|
|
# for performance we will only prepare repeated sql once |
449 |
|
|
if (exists $glb_prepared_sql{"sth_qry_app_decision"} ) { |
450 |
|
|
$sth_qry_app_decision = $glb_prepared_sql{"sth_qry_app_decision"}; |
451 |
|
|
} |
452 |
|
|
else { |
453 |
|
|
|
454 |
|
|
# add fields to below as needed |
455 |
|
|
$sth_qry_app_decision = $dbh->prepare( q{ |
456 |
|
|
select sarappd_apdc_code, |
457 |
|
|
decode(nvl(stvapdc_inst_acc_ind, 0), 'Y', 1, 0) |
458 |
|
|
from sarappd a, |
459 |
|
|
stvapdc |
460 |
|
|
where sarappd_pidm = ? AND |
461 |
|
|
sarappd_appl_no = ? AND |
462 |
|
|
sarappd_term_code_entry = ? AND |
463 |
|
|
TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
464 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0') = ( |
465 |
|
|
select MAX(TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
466 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0')) |
467 |
|
|
from sarappd b |
468 |
|
|
where b.sarappd_term_code_entry = |
469 |
|
|
a.sarappd_term_code_entry and |
470 |
|
|
b.sarappd_pidm = a.sarappd_pidm and |
471 |
|
|
sarappd_apdc_date <= TO_DATE(?, 'mm/dd/yyyy') and |
472 |
|
|
b.sarappd_appl_no = a.sarappd_appl_no) AND |
473 |
|
|
stvapdc_code = sarappd_apdc_code } ); |
474 |
|
|
|
475 |
|
|
$glb_prepared_sql{"sth_qry_app_decision"} = $sth_qry_app_decision; |
476 |
|
|
} |
477 |
|
|
|
478 |
|
|
$sth_qry_app_decision->execute($pidm, $appl_no, $term, $max_date); |
479 |
|
|
|
480 |
|
|
($decision, $accepted) = $sth_qry_app_decision->fetchrow_array; |
481 |
|
|
|
482 |
|
|
|
483 |
|
|
if ($accepted) { $decision = "A" } |
484 |
|
|
|
485 |
|
|
$decision = $decision ? $decision : "P"; |
486 |
|
|
# pending |
487 |
|
|
return $decision; |
488 |
|
|
} |
489 |
|
|
|
490 |
|
|
|
491 |
|
|
sub app_dec_detl { |
492 |
|
|
my ($dbh, $pidm, $term, $appl_no, $max_date) = @_; |
493 |
|
|
|
494 |
|
|
my ($decision, $accepted); |
495 |
|
|
|
496 |
|
|
# for performance we will only prepare repeated sql once |
497 |
|
|
if (exists $glb_prepared_sql{"sth_qry_app_decision"} ) { |
498 |
|
|
$sth_qry_app_decision = $glb_prepared_sql{"sth_qry_app_decision"}; |
499 |
|
|
} |
500 |
|
|
else { |
501 |
|
|
|
502 |
|
|
# add fields to below as needed |
503 |
|
|
$sth_qry_app_decision = $dbh->prepare( q{ |
504 |
|
|
select sarappd_apdc_code, |
505 |
|
|
decode(nvl(stvapdc_inst_acc_ind, 0), 'Y', 1, 0) |
506 |
|
|
from sarappd a, |
507 |
|
|
stvapdc |
508 |
|
|
where sarappd_pidm = ? AND |
509 |
|
|
sarappd_appl_no = ? AND |
510 |
|
|
sarappd_term_code_entry = ? AND |
511 |
|
|
TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
512 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0') = ( |
513 |
|
|
select MAX(TO_CHAR(sarappd_apdc_date,'YYYYMMDD')|| |
514 |
|
|
LPAD(TO_CHAR(sarappd_seq_no),2,'0')) |
515 |
|
|
from sarappd b |
516 |
|
|
where b.sarappd_term_code_entry = |
517 |
|
|
a.sarappd_term_code_entry and |
518 |
|
|
b.sarappd_pidm = a.sarappd_pidm and |
519 |
|
|
sarappd_apdc_date <= TO_DATE(?, 'mm/dd/yyyy') and |
520 |
|
|
b.sarappd_appl_no = a.sarappd_appl_no) AND |
521 |
|
|
stvapdc_code = sarappd_apdc_code } ); |
522 |
|
|
|
523 |
|
|
$glb_prepared_sql{"sth_qry_app_decision"} = $sth_qry_app_decision; |
524 |
|
|
} |
525 |
|
|
|
526 |
|
|
$sth_qry_app_decision->execute($pidm, $appl_no, $term, $max_date); |
527 |
|
|
|
528 |
|
|
($decision, $accepted) = $sth_qry_app_decision->fetchrow_array; |
529 |
|
|
|
530 |
|
|
$decision = $decision ? $decision : "P"; # pending |
531 |
|
|
|
532 |
|
|
return { "decision" => $decision, |
533 |
|
|
"accepted" => $accepted }; |
534 |
|
|
} |
535 |
|
|
|
536 |
|
|
|
537 |
|
|
# wrapper for app_dec_detl that returns Y/N instead of 1/0 for accepted |
538 |
|
|
sub app_decsn { |
539 |
|
|
my ($dbh, $pidm, $term, $appl_no, $max_date) = @_; |
540 |
|
|
|
541 |
|
|
my $rh_app_decsn = app_dec_detl($dbh, $pidm, $term, $appl_no, $max_date); |
542 |
|
|
|
543 |
|
|
if ( defined($rh_app_decsn->{accepted} ) ) { |
544 |
|
|
|
545 |
|
|
$rh_app_decsn->{accepted} =~ s/1/Y/; |
546 |
|
|
$rh_app_decsn->{accepted} =~ s/0/N/; |
547 |
|
|
} |
548 |
|
|
else { |
549 |
|
|
$rh_app_decsn->{accepted} = "N"; |
550 |
|
|
} |
551 |
|
|
|
552 |
|
|
return $rh_app_decsn; |
553 |
|
|
} |
554 |
|
|
|
555 |
|
|
|
556 |
|
|
sub tests { |
557 |
|
|
my ($dbh, $pidm) = @_; |
558 |
|
|
|
559 |
|
|
my (@row, $test, $score, %tests); |
560 |
|
|
|
561 |
|
|
# for performance we will only prepare repeated sql once |
562 |
|
|
if (exists $glb_prepared_sql{"sth_qry_tests"} ) { |
563 |
|
|
$sth_qry_tests = $glb_prepared_sql{"sth_qry_tests"}; |
564 |
|
|
} |
565 |
|
|
else { |
566 |
|
|
# add fields to below as needed |
567 |
|
|
$sth_qry_tests = $dbh->prepare( q{ |
568 |
|
|
select stvtesc_desc, |
569 |
|
|
sortest_test_score |
570 |
|
|
from sortest a, |
571 |
|
|
stvtesc |
572 |
|
|
where sortest_pidm = ? AND |
573 |
|
|
sortest_test_score = ( |
574 |
|
|
select max(to_number(sortest_test_score) ) |
575 |
|
|
from sortest b |
576 |
|
|
where b.sortest_pidm = a.sortest_pidm and |
577 |
|
|
b.sortest_tesc_code = a.sortest_tesc_code) AND |
578 |
|
|
stvtesc_code = sortest_tesc_code } ); |
579 |
|
|
|
580 |
|
|
$glb_prepared_sql{"sth_qry_tests"} = $sth_qry_tests; |
581 |
|
|
} |
582 |
|
|
|
583 |
|
|
$sth_qry_tests->execute($pidm); |
584 |
|
|
while (@row = $sth_qry_tests->fetchrow_array) { |
585 |
|
|
|
586 |
|
|
($test, $score) = @row; |
587 |
|
|
|
588 |
|
|
$tests{$test} = $score; |
589 |
|
|
} |
590 |
|
|
|
591 |
|
|
return \%tests; |
592 |
|
|
} |
593 |
|
|
|
594 |
|
|
|
595 |
|
|
sub scarf_tests { |
596 |
|
|
my ($dbh, $pidm) = @_; |
597 |
|
|
|
598 |
|
|
my (@tests, $test_code, $test, $test_date, $raw_score, $score, $assess_mthd); |
599 |
|
|
|
600 |
|
|
# for performance we will only prepare repeated sql once |
601 |
|
|
if (exists $glb_prepared_sql{"sth_qry_scarf_tests"} ) { |
602 |
|
|
$sth_qry_scarf_tests = $glb_prepared_sql{"sth_qry_scarf_tests"}; |
603 |
|
|
} |
604 |
|
|
else { |
605 |
|
|
# add fields to below as needed |
606 |
|
|
$sth_qry_scarf_tests = $dbh->prepare( q{ |
607 |
|
|
select sortest_tesc_code, |
608 |
|
|
stvtesc_desc, |
609 |
|
|
to_char(sortest_test_date, 'mm/dd/yyyy'), |
610 |
|
|
sortest_test_score, |
611 |
|
|
sortest_teac_code, |
612 |
|
|
sortest_tein_code |
613 |
|
|
from sortest a, |
614 |
|
|
stvtesc |
615 |
|
|
where sortest_pidm = ? AND |
616 |
|
|
sortest_test_score = ( |
617 |
|
|
select max(to_number(sortest_test_score) ) |
618 |
|
|
from sortest b |
619 |
|
|
where b.sortest_pidm = a.sortest_pidm and |
620 |
|
|
b.sortest_tesc_code = a.sortest_tesc_code) AND |
621 |
|
|
stvtesc_code = sortest_tesc_code } ); |
622 |
|
|
|
623 |
|
|
$glb_prepared_sql{"sth_qry_scarf_tests"} = $sth_qry_scarf_tests; |
624 |
|
|
} |
625 |
|
|
|
626 |
|
|
$sth_qry_scarf_tests->execute($pidm); |
627 |
|
|
while ( ($test_code, $test, $test_date, $raw_score, $score, $assess_mthd) = |
628 |
|
|
$sth_qry_scarf_tests->fetchrow_array ) { |
629 |
|
|
|
630 |
|
|
push @tests, { "pidm" => $pidm, |
631 |
|
|
"test_code" => $test_code, |
632 |
|
|
"test" => $test, |
633 |
|
|
"test_date" => $test_date, |
634 |
|
|
"raw_score" => $raw_score, |
635 |
|
|
"score" => $score, |
636 |
|
|
"assess_mthd" => $assess_mthd }; |
637 |
|
|
} |
638 |
|
|
|
639 |
|
|
return \@tests; |
640 |
|
|
} |
641 |
|
|
|
642 |
|
|
|
643 |
|
|
sub hs_gpa { |
644 |
|
|
my ($dbh, $pidm) = @_; |
645 |
|
|
|
646 |
|
|
my (@row); |
647 |
|
|
|
648 |
|
|
# for performance we will only prepare repeated sql once |
649 |
|
|
if (exists $glb_prepared_sql{"sth_qry_hs_gpa"} ) { |
650 |
|
|
$sth_qry_hs_gpa = $glb_prepared_sql{"sth_qry_hs_gpa"}; |
651 |
|
|
} |
652 |
|
|
else { |
653 |
|
|
# add fields to below as needed |
654 |
|
|
$sth_qry_hs_gpa = $dbh->prepare( q{ |
655 |
|
|
select sorhsch_sbgi_code, |
656 |
|
|
stvsbgi_desc, |
657 |
|
|
sorhsch_gpa, |
658 |
|
|
sorhsch_class_rank, |
659 |
|
|
sorhsch_percentile, |
660 |
|
|
to_char(sorhsch_graduation_date, 'mm/dd/yyyy') |
661 |
|
|
from sorhsch a, |
662 |
|
|
stvsbgi |
663 |
|
|
where sorhsch_pidm = ? AND |
664 |
|
|
sorhsch_activity_date = ( |
665 |
|
|
select max(sorhsch_activity_date) |
666 |
|
|
from sorhsch b |
667 |
|
|
where b.sorhsch_pidm = a.sorhsch_pidm) AND |
668 |
|
|
stvsbgi_code = sorhsch_sbgi_code } ); |
669 |
|
|
|
670 |
|
|
$glb_prepared_sql{"sth_qry_hs_gpa"} = $sth_qry_hs_gpa; |
671 |
|
|
} |
672 |
|
|
|
673 |
|
|
$sth_qry_hs_gpa->execute($pidm); |
674 |
|
|
|
675 |
|
|
@row = $sth_qry_hs_gpa->fetchrow_array; |
676 |
|
|
|
677 |
|
|
if (@row) { |
678 |
|
|
# add fields to below as needed |
679 |
|
|
return { "hs_code" => $row[0], |
680 |
|
|
"hs_desc" => $row[1], |
681 |
|
|
"gpa" => $row[2], |
682 |
|
|
"rank" => $row[3], |
683 |
|
|
"percentile" => $row[4], |
684 |
|
|
"grad_date" => $row[5] }; |
685 |
|
|
} |
686 |
|
|
|
687 |
|
|
# fall-through |
688 |
|
|
return { "hs_code" => "", |
689 |
|
|
"hs_desc" => "", |
690 |
|
|
"gpa" => "", |
691 |
|
|
"rank" => "", |
692 |
|
|
"percentile" => "", |
693 |
|
|
"grad_date" => "" }; |
694 |
|
|
} |
695 |
|
|
|
696 |
|
|
|
697 |
|
|
sub hs_subj { |
698 |
|
|
my ($dbh, $pidm) = @_; |
699 |
|
|
|
700 |
|
|
my (@row, @hs_subj); |
701 |
|
|
|
702 |
|
|
# for performance we will only prepare repeated sql once |
703 |
|
|
if (exists $glb_prepared_sql{"sth_qry_hs_subj"} ) { |
704 |
|
|
$sth_qry_hs_subj = $glb_prepared_sql{"sth_qry_hs_subj"}; |
705 |
|
|
} |
706 |
|
|
else { |
707 |
|
|
# add fields to below as needed |
708 |
|
|
$sth_qry_hs_subj = $dbh->prepare( q{ |
709 |
|
|
select sorhsbj_sbgi_code, |
710 |
|
|
sorhsbj_sbjc_code, |
711 |
|
|
nvl(sorhsbj_subj_years, 0) |
712 |
|
|
from sorhsbj |
713 |
|
|
where sorhsbj_pidm = ? } ); |
714 |
|
|
|
715 |
|
|
$glb_prepared_sql{"sth_qry_hs_subj"} = $sth_qry_hs_subj; |
716 |
|
|
} |
717 |
|
|
|
718 |
|
|
$sth_qry_hs_subj->execute($pidm); |
719 |
|
|
|
720 |
|
|
while (@row = $sth_qry_hs_subj->fetchrow_array ) { |
721 |
|
|
|
722 |
|
|
if ( $row[2] ) { # subj_years |
723 |
|
|
|
724 |
|
|
push @hs_subj, { "pidm" => $pidm, |
725 |
|
|
"hs_code" => $row[0], |
726 |
|
|
"subj_code" => $row[1], |
727 |
|
|
"subj_years" => $row[2] }; |
728 |
|
|
} |
729 |
|
|
} |
730 |
|
|
|
731 |
|
|
return \@hs_subj; |
732 |
|
|
} |
733 |
|
|
|
734 |
|
|
|
735 |
|
|
sub tr_coll { |
736 |
|
|
my ($dbh, $pidm) = @_; |
737 |
|
|
|
738 |
|
|
my (%tr_coll, @tr_coll, $sbgi, $degc, $acat, $end_date, $hrs_earned, |
739 |
|
|
$gpa_hrs, $qual_pts, $gpa, $term, $activity_date, $seq, |
740 |
|
|
$coll_desc); |
741 |
|
|
|
742 |
|
|
# for performance we will only prepare repeated sql once |
743 |
|
|
# shrtrit/shrtram |
744 |
|
|
if (exists $glb_prepared_sql{"sth_qry_tr_coll1"} ) { |
745 |
|
|
$sth_qry_tr_coll1 = $glb_prepared_sql{"sth_qry_tr_coll1"}; |
746 |
|
|
} |
747 |
|
|
else { |
748 |
|
|
$sth_qry_tr_coll1 = $dbh->prepare( q{ |
749 |
|
|
select shrtrit_sbgi_code, |
750 |
|
|
shrtram_degc_code, |
751 |
|
|
stvdegc_acat_code, |
752 |
|
|
to_char(shrtram_attn_end_date, 'mm/dd/yyyy'), |
753 |
|
|
shrtgpa_hours_earned, |
754 |
|
|
shrtgpa_gpa_hours, |
755 |
|
|
shrtgpa_quality_points, |
756 |
|
|
shrtram_term_code_entered, |
757 |
|
|
shrtrit_seq_no, |
758 |
|
|
stvsbgi_desc |
759 |
|
|
from shrtrit, |
760 |
|
|
shrtram, |
761 |
|
|
stvdegc, |
762 |
|
|
shrtgpa, |
763 |
|
|
stvsbgi |
764 |
|
|
where shrtrit_pidm = ? and |
765 |
|
|
/* exclude Credit by Exam, CLEP, and Advanced Placement */ |
766 |
|
|
shrtrit_sbgi_code not in ('CBE', 'CLEP', 'AP') and |
767 |
|
|
shrtram_pidm (+)= shrtrit_pidm and |
768 |
|
|
shrtram_trit_seq_no (+)= shrtrit_seq_no and |
769 |
|
|
stvdegc_code (+)= shrtram_degc_code and |
770 |
|
|
shrtgpa_pidm (+)= shrtram_pidm and |
771 |
|
|
shrtgpa_trit_seq_no (+)= shrtram_trit_seq_no and |
772 |
|
|
shrtgpa_gpa_type_ind (+)= 'T' and |
773 |
|
|
shrtgpa_levl_code (+)= shrtram_levl_code and |
774 |
|
|
shrtgpa_tram_seq_no (+)= shrtram_seq_no and |
775 |
|
|
stvsbgi_code (+)= shrtrit_sbgi_code } ); |
776 |
|
|
|
777 |
|
|
$glb_prepared_sql{"sth_qry_tr_coll1"} = $sth_qry_tr_coll1; |
778 |
|
|
} |
779 |
|
|
|
780 |
|
|
# sorpcol |
781 |
|
|
if (exists $glb_prepared_sql{"sth_qry_tr_coll2"} ) { |
782 |
|
|
$sth_qry_tr_coll2 = $glb_prepared_sql{"sth_qry_tr_coll2"}; |
783 |
|
|
} |
784 |
|
|
else { |
785 |
|
|
$sth_qry_tr_coll2 = $dbh->prepare( q{ |
786 |
|
|
select sorpcol_sbgi_code, |
787 |
|
|
sordegr_degc_code, |
788 |
|
|
stvdegc_acat_code, |
789 |
|
|
to_char(sordegr_attend_to, 'mm/dd/yyyy'), |
790 |
|
|
sordegr_hours_transferred, |
791 |
|
|
sordegr_gpa_transferred, |
792 |
|
|
to_char(sorpcol_activity_date, 'mm/dd/yyyy'), |
793 |
|
|
stvsbgi_desc |
794 |
|
|
from sorpcol, |
795 |
|
|
sordegr, |
796 |
|
|
stvdegc, |
797 |
|
|
stvsbgi |
798 |
|
|
where sorpcol_pidm = ? and |
799 |
|
|
sordegr_pidm (+)= sorpcol_pidm and |
800 |
|
|
sordegr_sbgi_code (+)= sorpcol_sbgi_code and |
801 |
|
|
stvdegc_code (+)= sordegr_degc_code and |
802 |
|
|
stvsbgi_code (+)= sorpcol_sbgi_code } ); |
803 |
|
|
|
804 |
|
|
$glb_prepared_sql{"sth_qry_tr_coll2"} = $sth_qry_tr_coll2; |
805 |
|
|
} |
806 |
|
|
|
807 |
|
|
# shrtrit/shrtram |
808 |
|
|
$sth_qry_tr_coll1->execute($pidm); |
809 |
|
|
|
810 |
|
|
while ( ( $sbgi, $degc, $acat, $end_date, $hrs_earned, $gpa_hrs, $qual_pts, |
811 |
|
|
$term, $seq, $coll_desc) = $sth_qry_tr_coll1->fetchrow_array ) { |
812 |
|
|
|
813 |
|
|
$degc = defined($degc) ? $degc : ""; |
814 |
|
|
$acat = defined($acat) ? $acat : ""; |
815 |
|
|
$end_date = defined($end_date) ? $end_date : ""; |
816 |
|
|
$hrs_earned = defined($hrs_earned) ? $hrs_earned : 0; |
817 |
|
|
$gpa_hrs = defined($gpa_hrs) ? $gpa_hrs : 0; |
818 |
|
|
$qual_pts = defined($qual_pts) ? $qual_pts : 0; |
819 |
|
|
|
820 |
|
|
# sum by school and degree code (including "" degree code) |
821 |
|
|
# should probably just be one attendance period per school/non-null degree, |
822 |
|
|
# but null degree could have several attendance periods per school |
823 |
|
|
if ( !exists ( $tr_coll{$sbgi}->{$degc} ) ) { |
824 |
|
|
$tr_coll{$sbgi}->{$degc} = { "acat" => $acat, |
825 |
|
|
"end_date" => $end_date, |
826 |
|
|
"hrs_earned" => $hrs_earned, |
827 |
|
|
"gpa_hrs" => $gpa_hrs, |
828 |
|
|
"qual_pts" => $qual_pts, |
829 |
|
|
"term" => $term, |
830 |
|
|
"max_seq" => $seq, |
831 |
|
|
"coll_desc" => $coll_desc, |
832 |
|
|
"activity_date" => "" }; |
833 |
|
|
} |
834 |
|
|
else { |
835 |
|
|
if ( $tr_coll{$sbgi}->{$degc}->{end_date} ) { |
836 |
|
|
if ( $end_date and |
837 |
|
|
date_compare($end_date, ">", |
838 |
|
|
$tr_coll{$sbgi}->{$degc}->{end_date} ) ) { |
839 |
|
|
|
840 |
|
|
$tr_coll{$sbgi}->{$degc}->{end_date} = $end_date; |
841 |
|
|
} |
842 |
|
|
} |
843 |
|
|
else { $tr_coll{$sbgi}->{$degc}->{end_date} = $end_date } |
844 |
|
|
|
845 |
|
|
$tr_coll{$sbgi}->{$degc}->{hrs_earned} += $hrs_earned; |
846 |
|
|
$tr_coll{$sbgi}->{$degc}->{gpa_hrs} += $gpa_hrs; |
847 |
|
|
$tr_coll{$sbgi}->{$degc}->{qual_pts} += $qual_pts; |
848 |
|
|
|
849 |
|
|
|
850 |
|
|
if ($seq > $tr_coll{$sbgi}->{$degc}->{max_seq} ) { |
851 |
|
|
$tr_coll{$sbgi}->{$degc}->{max_seq} = $seq; |
852 |
|
|
} |
853 |
|
|
|
854 |
|
|
} |
855 |
|
|
} |
856 |
|
|
|
857 |
|
|
|
858 |
|
|
# sorpcol/sordegr |
859 |
|
|
$sth_qry_tr_coll2->execute($pidm); |
860 |
|
|
|
861 |
|
|
while ( ( $sbgi, $degc, $acat, $end_date, $hrs_earned, $gpa, $activity_date, |
862 |
|
|
$coll_desc) = $sth_qry_tr_coll2->fetchrow_array ) { |
863 |
|
|
|
864 |
|
|
$degc = defined($degc) ? $degc : ""; |
865 |
|
|
$acat = defined($acat) ? $acat : ""; |
866 |
|
|
$end_date = defined($end_date) ? $end_date : ""; |
867 |
|
|
$hrs_earned = defined($hrs_earned) ? $hrs_earned : 0; |
868 |
|
|
$gpa = defined($gpa) ? $gpa : 0; |
869 |
|
|
|
870 |
|
|
if ( defined($sbgi) and |
871 |
|
|
!exists ( $tr_coll{$sbgi}->{$degc} ) ) { |
872 |
|
|
$tr_coll{$sbgi}->{$degc} = { "acat" => $acat, |
873 |
|
|
"end_date" => $end_date, |
874 |
|
|
"hrs_earned" => $hrs_earned, |
875 |
|
|
"gpa" => $gpa, |
876 |
|
|
"term" => "", |
877 |
|
|
"coll_desc" => $coll_desc, |
878 |
|
|
"activity_date" => $activity_date }; |
879 |
|
|
} |
880 |
|
|
} |
881 |
|
|
|
882 |
|
|
|
883 |
|
|
# put results on @tr_coll and calculate gpa |
884 |
|
|
foreach $sbgi (keys %tr_coll) { |
885 |
|
|
foreach $degc (keys %{ $tr_coll{$sbgi} } ) { |
886 |
|
|
|
887 |
|
|
# shrtrit/shrtram |
888 |
|
|
if ( exists($tr_coll{$sbgi}->{$degc}->{gpa_hrs} ) ) { |
889 |
|
|
if ( $tr_coll{$sbgi}->{$degc}->{gpa_hrs} > 0 ) { |
890 |
|
|
$gpa = sprintf("%.2f", $tr_coll{$sbgi}->{$degc}->{qual_pts} / |
891 |
|
|
$tr_coll{$sbgi}->{$degc}->{gpa_hrs} ); |
892 |
|
|
} |
893 |
|
|
else { $gpa = "" }; |
894 |
|
|
} |
895 |
|
|
# sorpcol/sordegr |
896 |
|
|
else { $gpa = $tr_coll{$sbgi}->{$degc}->{gpa} } |
897 |
|
|
|
898 |
|
|
push @tr_coll, { "pidm" => $pidm, |
899 |
|
|
"prior_coll" => $sbgi, |
900 |
|
|
"degree" => $degc, |
901 |
|
|
"acat" => $tr_coll{$sbgi}->{$degc}->{acat}, |
902 |
|
|
"attend_to_date" => |
903 |
|
|
$tr_coll{$sbgi}->{$degc}->{end_date}, |
904 |
|
|
"hrs_trans" => $tr_coll{$sbgi}->{$degc}->{hrs_earned}, |
905 |
|
|
"gpa_trans" => $gpa, |
906 |
|
|
"term" => $tr_coll{$sbgi}->{$degc}->{term}, |
907 |
|
|
"max_seq" => |
908 |
|
|
defined($tr_coll{$sbgi}->{$degc}->{max_seq} ) ? |
909 |
|
|
$tr_coll{$sbgi}->{$degc}->{max_seq} : |
910 |
|
|
undef, |
911 |
|
|
"coll_desc" => $tr_coll{$sbgi}->{$degc}->{coll_desc}, |
912 |
|
|
"activity_date" => |
913 |
|
|
$tr_coll{$sbgi}->{$degc}->{activity_date} }; |
914 |
|
|
} |
915 |
|
|
} |
916 |
|
|
|
917 |
|
|
return \@tr_coll; |
918 |
|
|
} |
919 |
|
|
|
920 |
|
|
|
921 |
|
|
sub last_tr_coll { |
922 |
|
|
|
923 |
|
|
my ($dbh, $pidm) = @_; |
924 |
|
|
|
925 |
|
|
my ($ra_tr_coll, $rh_tr_coll, $max_seq, $rh_last_tr_coll); |
926 |
|
|
|
927 |
|
|
$ra_tr_coll = tr_coll($dbh, $pidm); |
928 |
|
|
|
929 |
|
|
$max_seq = 0; |
930 |
|
|
foreach $rh_tr_coll ( @{$ra_tr_coll} ) { |
931 |
|
|
|
932 |
|
|
if ( defined($rh_tr_coll->{max_seq} ) and |
933 |
|
|
$rh_tr_coll->{max_seq} > $max_seq ) { |
934 |
|
|
|
935 |
|
|
$rh_last_tr_coll = $rh_tr_coll; |
936 |
|
|
$max_seq = $rh_tr_coll->{max_seq}; |
937 |
|
|
} |
938 |
|
|
} |
939 |
|
|
|
940 |
|
|
if ($max_seq) { |
941 |
|
|
return $rh_last_tr_coll; |
942 |
|
|
} |
943 |
|
|
else { |
944 |
|
|
# ignore if only sorpcol/sordegr, since can't determine which one |
945 |
|
|
# was last, and need to pick just one (don't want to randomly pick). |
946 |
|
|
return { "pidm" => $pidm, |
947 |
|
|
"prior_coll" => "", |
948 |
|
|
"degree" => "", |
949 |
|
|
"acat" => "", |
950 |
|
|
"attend_to_date" => "", |
951 |
|
|
"hrs_trans" => "", |
952 |
|
|
"gpa_trans" => "", |
953 |
|
|
"term" => "", |
954 |
|
|
"max_seq" => "", |
955 |
|
|
"coll_desc" => "", |
956 |
|
|
"activity_date" => "" }; |
957 |
|
|
} |
958 |
|
|
} |
959 |
|
|
|
960 |
|
|
|
961 |
|
|
# SAT Scores |
962 |
|
|
sub SAT { |
963 |
|
|
my ($dbh, $pidm) = @_; |
964 |
|
|
|
965 |
|
|
my ($rh_tests, $test, $SAT, $SATM, $SATV); |
966 |
|
|
|
967 |
|
|
|
968 |
|
|
$rh_tests = tests($dbh, $pidm); |
969 |
|
|
|
970 |
|
|
foreach $test (keys %{$rh_tests} ) { |
971 |
|
|
if ( $test eq "SAT Composite" ) { |
972 |
|
|
$SAT = $rh_tests->{$test}; |
973 |
|
|
} |
974 |
|
|
elsif ( $test eq "SAT Mathematics" ) { |
975 |
|
|
$SATM = $rh_tests->{$test}; |
976 |
|
|
} |
977 |
|
|
elsif ( $test eq "SAT Verbal" ) { |
978 |
|
|
$SATV = $rh_tests->{$test}; |
979 |
|
|
} |
980 |
|
|
} |
981 |
|
|
|
982 |
|
|
$SAT = defined($SAT) ? $SAT : 0; |
983 |
|
|
$SATM = defined($SATM) ? $SATM : 0; |
984 |
|
|
$SATV = defined($SATV) ? $SATV : 0; |
985 |
|
|
|
986 |
|
|
return { "pidm" => $pidm, |
987 |
|
|
"SAT" => $SAT, |
988 |
|
|
"SATM" => $SATM, |
989 |
|
|
"SATV" => $SATV }; |
990 |
|
|
} |
991 |
|
|
|
992 |
|
|
|
993 |
|
|
# ACT Scores |
994 |
|
|
sub ACT { |
995 |
|
|
my ($dbh, $pidm) = @_; |
996 |
|
|
|
997 |
|
|
my ($rh_tests, $test, $ACT, $ACTE, $ACTM, $ACTN, $ACTS); |
998 |
|
|
|
999 |
|
|
|
1000 |
|
|
$rh_tests = tests($dbh, $pidm); |
1001 |
|
|
|
1002 |
|
|
foreach $test (keys %{$rh_tests} ) { |
1003 |
|
|
if ( $test eq "ACT Composite" ) { |
1004 |
|
|
$ACT = $rh_tests->{$test}; |
1005 |
|
|
} |
1006 |
|
|
elsif ( $test eq "ACT English" ) { |
1007 |
|
|
$ACTE = $rh_tests->{$test}; |
1008 |
|
|
} |
1009 |
|
|
elsif ( $test eq "ACT Math" ) { |
1010 |
|
|
$ACTM = $rh_tests->{$test}; |
1011 |
|
|
} |
1012 |
|
|
elsif ( $test eq "ACT Natural Science" ) { |
1013 |
|
|
$ACTN = $rh_tests->{$test}; |
1014 |
|
|
} |
1015 |
|
|
elsif ( $test eq "ACT Social Science" ) { |
1016 |
|
|
$ACTS = $rh_tests->{$test}; |
1017 |
|
|
} |
1018 |
|
|
} |
1019 |
|
|
|
1020 |
|
|
$ACT = defined($ACT) ? $ACT : 0; |
1021 |
|
|
$ACTE = defined($ACTE) ? $ACTE : 0; |
1022 |
|
|
$ACTM = defined($ACTM) ? $ACTM : 0; |
1023 |
|
|
$ACTN = defined($ACTN) ? $ACTN : 0; |
1024 |
|
|
$ACTS = defined($ACTS) ? $ACTS : 0; |
1025 |
|
|
|
1026 |
|
|
return { "pidm" => $pidm, |
1027 |
|
|
"ACT" => $ACT, |
1028 |
|
|
"ACTE" => $ACTE, |
1029 |
|
|
"ACTM" => $ACTM, |
1030 |
|
|
"ACTN" => $ACTN, |
1031 |
|
|
"ACTS" => $ACTS }; |
1032 |
|
|
} |
1033 |
|
|
|
1034 |
|
|
|
1035 |
|
|
# ======================================================================= |
1036 |
|
|
# term_recruits - All recruits and applicants for given term and level. |
1037 |
|
|
# Level may use wildcards. |
1038 |
|
|
# ======================================================================= |
1039 |
|
|
sub term_recruits { |
1040 |
|
|
my ($dbh, $term, $level) = @_; |
1041 |
|
|
|
1042 |
|
|
my (@row, @recruits); |
1043 |
|
|
|
1044 |
|
|
|
1045 |
|
|
my $sth_qry_term_recruits = $dbh->prepare( q{ |
1046 |
|
|
select SRBRECR_PIDM, |
1047 |
|
|
srbrecr_levl_code, |
1048 |
|
|
upper(nvl(spbpers_confid_ind, 'N') ), |
1049 |
|
|
stvmajr_desc, |
1050 |
|
|
sorhsch_sbgi_code, |
1051 |
|
|
nvl(stvsbgi_desc, 'NONE'), |
1052 |
|
|
nvl(decode(substr(spbpers_ethn_code, 1, 1), |
1053 |
|
|
'A', 'Asian', |
1054 |
|
|
'B', 'Black African', |
1055 |
|
|
'H', 'Hispanic', |
1056 |
|
|
'I', 'American Indian', |
1057 |
|
|
'M', 'Middle Easterner', |
1058 |
|
|
'N', 'North African', |
1059 |
|
|
'O', 'none of the above', |
1060 |
|
|
'P', 'Pacific Islander', |
1061 |
|
|
'W', 'White-Euro-American', |
1062 |
|
|
'X', 'Multiple ethnics', |
1063 |
|
|
'D', 'Declined to Respond ', |
1064 |
|
|
'none of the above'), |
1065 |
|
|
'none of the above'), |
1066 |
|
|
substr(sorhsch_gpa, 1, 4), /* bad data if longer than this */ |
1067 |
|
|
nvl(stvrtyp_desc, 'Unknown'), |
1068 |
|
|
nvl(stvrsta_desc, 'N/A'), |
1069 |
|
|
srbrecr_resd_code |
1070 |
|
|
from srbrecr a, |
1071 |
|
|
spbpers, |
1072 |
|
|
stvmajr, |
1073 |
|
|
sorhsch, |
1074 |
|
|
stvsbgi, |
1075 |
|
|
stvrtyp, |
1076 |
|
|
stvrsta |
1077 |
|
|
where srbrecr_term_code = ? AND |
1078 |
|
|
srbrecr_levl_code like ? AND |
1079 |
|
|
srbrecr_admin_seqno = ( |
1080 |
|
|
select max(srbrecr_admin_seqno) |
1081 |
|
|
from srbrecr b |
1082 |
|
|
where b.srbrecr_pidm = a.srbrecr_pidm and |
1083 |
|
|
b.srbrecr_levl_code = a.srbrecr_levl_code and |
1084 |
|
|
b.srbrecr_term_code = a.srbrecr_term_code) AND |
1085 |
|
|
spbpers_pidm = srbrecr_pidm AND |
1086 |
|
|
stvmajr_code (+)= srbrecr_majr_code AND |
1087 |
|
|
sorhsch_pidm (+)= srbrecr_pidm AND |
1088 |
|
|
stvsbgi_code (+) = sorhsch_sbgi_code AND |
1089 |
|
|
stvrtyp_code (+) = srbrecr_rtyp_code AND |
1090 |
|
|
stvrsta_code (+) = srbrecr_rsta_code |
1091 |
|
|
UNION |
1092 |
|
|
select saradap_pidm, |
1093 |
|
|
saradap_levl_code, |
1094 |
|
|
upper(nvl(spbpers_confid_ind, 'N') ), |
1095 |
|
|
stvmajr_desc, |
1096 |
|
|
sorhsch_sbgi_code, |
1097 |
|
|
nvl(stvsbgi_desc, 'NONE'), |
1098 |
|
|
nvl(decode(substr(spbpers_ethn_code, 1, 1), |
1099 |
|
|
'A', 'Asian', |
1100 |
|
|
'B', 'Black African', |
1101 |
|
|
'H', 'Hispanic', |
1102 |
|
|
'I', 'American Indian', |
1103 |
|
|
'M', 'Middle Easterner', |
1104 |
|
|
'N', 'North African', |
1105 |
|
|
'O', 'none of the above', |
1106 |
|
|
'P', 'Pacific Islander', |
1107 |
|
|
'W', 'White-Euro-American', |
1108 |
|
|
'X', 'Multiple ethnics', |
1109 |
|
|
'D', 'Declined to Respond ', |
1110 |
|
|
'none of the above'), |
1111 |
|
|
'none of the above'), |
1112 |
|
|
substr(sorhsch_gpa, 1, 4), |
1113 |
|
|
stvadmt_desc, |
1114 |
|
|
'Unknown', |
1115 |
|
|
saradap_resd_code |
1116 |
|
|
from saradap a, |
1117 |
|
|
spbpers, |
1118 |
|
|
stvmajr, |
1119 |
|
|
sorhsch, |
1120 |
|
|
stvsbgi, |
1121 |
|
|
stvadmt |
1122 |
|
|
where saradap_term_code_entry = ? AND |
1123 |
|
|
saradap_levl_code like ? AND |
1124 |
|
|
saradap_appl_no = ( |
1125 |
|
|
select max(saradap_appl_no) |
1126 |
|
|
from saradap b |
1127 |
|
|
where b.saradap_pidm = a.saradap_pidm and |
1128 |
|
|
b.saradap_levl_code = a.saradap_levl_code and |
1129 |
|
|
b.saradap_term_code_entry = a.saradap_term_code_entry) AND |
1130 |
|
|
spbpers_pidm = saradap_pidm AND |
1131 |
|
|
stvmajr_code (+)= nvl(saradap_majr_code_1, saradap_majr_code_2) |
1132 |
|
|
AND |
1133 |
|
|
sorhsch_pidm (+)= saradap_pidm AND |
1134 |
|
|
stvsbgi_code (+) = sorhsch_sbgi_code AND |
1135 |
|
|
stvadmt_code (+) = saradap_admt_code AND |
1136 |
|
|
not exists ( |
1137 |
|
|
select 'x' |
1138 |
|
|
from srbrecr |
1139 |
|
|
where srbrecr_pidm = saradap_pidm and |
1140 |
|
|
srbrecr_levl_code = saradap_levl_code and |
1141 |
|
|
srbrecr_term_code = saradap_term_code_entry) } ); |
1142 |
|
|
|
1143 |
|
|
$sth_qry_term_recruits->execute($term, $level, $term, $level); |
1144 |
|
|
|
1145 |
|
|
while (@row = $sth_qry_term_recruits->fetchrow_array) { |
1146 |
|
|
push @recruits, { "pidm" => $row[0], # for obj_srvr |
1147 |
|
|
"term" => $term, # for obj_srvr |
1148 |
|
|
"level" => $row[1], |
1149 |
|
|
"confidential" => $row[2], |
1150 |
|
|
"major" => $row[3], |
1151 |
|
|
"hs_code" => $row[4], |
1152 |
|
|
"high_school" => $row[5], |
1153 |
|
|
"ethnicity" => $row[6], |
1154 |
|
|
"hs_gpa" => $row[7], |
1155 |
|
|
"recruit_type" => $row[8], |
1156 |
|
|
"level_of_interest" => $row[9], |
1157 |
|
|
"residence" => $row[10] }; |
1158 |
|
|
} |
1159 |
|
|
return \@recruits; |
1160 |
|
|
} |
1161 |
|
|
|
1162 |
|
|
# ======================================================================= |
1163 |
|
|
# contact - first contact for pidm |
1164 |
|
|
# ======================================================================= |
1165 |
|
|
sub contact { |
1166 |
|
|
my ($dbh, $pidm) = @_; |
1167 |
|
|
|
1168 |
|
|
my (@row); |
1169 |
|
|
|
1170 |
|
|
# for performance we will only prepare repeated sql once |
1171 |
|
|
if (exists $glb_prepared_sql{"sth_qry_contact"} ) { |
1172 |
|
|
$sth_qry_contact = $glb_prepared_sql{"sth_qry_contact"}; |
1173 |
|
|
} |
1174 |
|
|
else { |
1175 |
|
|
$sth_qry_contact = $dbh->prepare( q{ |
1176 |
|
|
select stvctyp_desc |
1177 |
|
|
from sorcont a, |
1178 |
|
|
stvctyp |
1179 |
|
|
where sorcont_pidm = ? AND |
1180 |
|
|
sorcont_contact_date = ( |
1181 |
|
|
select min(b.sorcont_contact_date) |
1182 |
|
|
from sorcont b |
1183 |
|
|
where b.sorcont_pidm = a.sorcont_pidm) AND |
1184 |
|
|
stvctyp_code = sorcont_ctyp_code } ); |
1185 |
|
|
} |
1186 |
|
|
|
1187 |
|
|
$sth_qry_contact->execute($pidm); |
1188 |
|
|
|
1189 |
|
|
if (@row = $sth_qry_contact->fetchrow_array) { |
1190 |
|
|
return { "contact" => $row[0] }; |
1191 |
|
|
} |
1192 |
|
|
else { |
1193 |
|
|
return { "contact" => "" }; |
1194 |
|
|
} |
1195 |
|
|
} |
1196 |
|
|
|
1197 |
|
|
|
1198 |
|
|
# ======================================================================= |
1199 |
|
|
# recruit - most recent (<= $term) recruit record for pidm/level |
1200 |
|
|
# ======================================================================= |
1201 |
|
|
sub recruit { |
1202 |
|
|
my ($dbh, $pidm, $term, $level) = @_; |
1203 |
|
|
|
1204 |
|
|
my (@row); |
1205 |
|
|
|
1206 |
|
|
# for performance we will only prepare repeated sql once |
1207 |
|
|
if (exists $glb_prepared_sql{"sth_qry_recruit"} ) { |
1208 |
|
|
$sth_qry_recruit = $glb_prepared_sql{"sth_qry_recruit"}; |
1209 |
|
|
} |
1210 |
|
|
else { |
1211 |
|
|
$sth_qry_recruit = $dbh->prepare( q{ |
1212 |
|
|
select upper(nvl(spbpers_confid_ind, 'N') ), |
1213 |
|
|
stvmajr_desc, |
1214 |
|
|
sorhsch_sbgi_code, |
1215 |
|
|
nvl(stvsbgi_desc, 'NONE'), |
1216 |
|
|
nvl(decode(substr(spbpers_ethn_code, 1, 1), |
1217 |
|
|
'A', 'Asian', |
1218 |
|
|
'B', 'Black African', |
1219 |
|
|
'H', 'Hispanic', |
1220 |
|
|
'I', 'American Indian', |
1221 |
|
|
'M', 'Middle Easterner', |
1222 |
|
|
'N', 'North African', |
1223 |
|
|
'O', 'none of the above', |
1224 |
|
|
'P', 'Pacific Islander', |
1225 |
|
|
'W', 'White-Euro-American', |
1226 |
|
|
'X', 'Multiple ethnics', |
1227 |
|
|
'D', 'Declined to Respond ', |
1228 |
|
|
'none of the above'), |
1229 |
|
|
'none of the above'), |
1230 |
|
|
substr(sorhsch_gpa, 1, 4), /* bad data if longer than this */ |
1231 |
|
|
nvl(stvrtyp_desc, 'Unknown'), |
1232 |
|
|
nvl(stvrsta_desc, 'N/A'), |
1233 |
|
|
srbrecr_resd_code |
1234 |
|
|
from srbrecr a, |
1235 |
|
|
spbpers, |
1236 |
|
|
stvmajr, |
1237 |
|
|
sorhsch, |
1238 |
|
|
stvsbgi, |
1239 |
|
|
stvrtyp, |
1240 |
|
|
stvrsta |
1241 |
|
|
where srbrecr_pidm = ? AND |
1242 |
|
|
srbrecr_levl_code = ? AND |
1243 |
|
|
srbrecr_term_code = ( |
1244 |
|
|
select max(srbrecr_term_code) |
1245 |
|
|
from srbrecr b |
1246 |
|
|
where b.srbrecr_pidm = a.srbrecr_pidm and |
1247 |
|
|
b.srbrecr_levl_code = a.srbrecr_levl_code and |
1248 |
|
|
b.srbrecr_term_code <= ?) AND |
1249 |
|
|
srbrecr_admin_seqno = ( |
1250 |
|
|
select max(srbrecr_admin_seqno) |
1251 |
|
|
from srbrecr c |
1252 |
|
|
where c.srbrecr_pidm = a.srbrecr_pidm and |
1253 |
|
|
c.srbrecr_levl_code = a.srbrecr_levl_code and |
1254 |
|
|
c.srbrecr_term_code = a.srbrecr_term_code) AND |
1255 |
|
|
spbpers_pidm = srbrecr_pidm AND |
1256 |
|
|
stvmajr_code (+)= srbrecr_majr_code AND |
1257 |
|
|
sorhsch_pidm (+)= srbrecr_pidm AND |
1258 |
|
|
stvsbgi_code (+) = sorhsch_sbgi_code AND |
1259 |
|
|
stvrtyp_code (+) = srbrecr_rtyp_code AND |
1260 |
|
|
stvrsta_code (+) = srbrecr_rsta_code } ); |
1261 |
|
|
} |
1262 |
|
|
|
1263 |
|
|
$sth_qry_recruit->execute($pidm, $level, $term); |
1264 |
|
|
|
1265 |
|
|
if (@row = $sth_qry_recruit->fetchrow_array) { |
1266 |
|
|
return { "confidential" => $row[0], |
1267 |
|
|
"major" => $row[1], |
1268 |
|
|
"hs_code" => $row[2], |
1269 |
|
|
"high_school" => $row[3], |
1270 |
|
|
"ethnicity" => $row[4], |
1271 |
|
|
"hs_gpa" => $row[5], |
1272 |
|
|
"recruit_type" => $row[6], |
1273 |
|
|
"level_of_interest" => $row[7], |
1274 |
|
|
"residence" => $row[8] }; |
1275 |
|
|
} |
1276 |
|
|
else { return { "confidential" => "", |
1277 |
|
|
"major" => "", |
1278 |
|
|
"hs_code" => "", |
1279 |
|
|
"high_school" => "", |
1280 |
|
|
"ethnicity" => "", |
1281 |
|
|
"hs_gpa" => 0, |
1282 |
|
|
"recruit_type" => "", |
1283 |
|
|
"level_of_interest" => "", |
1284 |
|
|
"residence" => "" }; |
1285 |
|
|
} |
1286 |
|
|
} |
1287 |
|
|
|
1288 |
|
|
|
1289 |
|
|
sub recr_status { |
1290 |
|
|
my ($dbh, $pidm, $level, $term_code) = @_; |
1291 |
|
|
|
1292 |
|
|
my (@row, $status); |
1293 |
|
|
|
1294 |
|
|
# for performance we will only prepare repeated sql once |
1295 |
|
|
if (exists $glb_prepared_sql{"sth_qry_recr_status1"} ) { |
1296 |
|
|
$sth_qry_recr_status1 = $glb_prepared_sql{"sth_qry_recr_status1"}; |
1297 |
|
|
$sth_qry_recr_status2 = $glb_prepared_sql{"sth_qry_recr_status2"}; |
1298 |
|
|
} |
1299 |
|
|
else { |
1300 |
|
|
|
1301 |
|
|
$sth_qry_recr_status1 = $dbh->prepare( q{ |
1302 |
|
|
select 1 |
1303 |
|
|
from saradap a |
1304 |
|
|
where saradap_pidm = ? AND |
1305 |
|
|
saradap_levl_code = ? AND |
1306 |
|
|
saradap_term_code_entry = ? AND |
1307 |
|
|
saradap_appl_no = ( |
1308 |
|
|
select max(saradap_appl_no) |
1309 |
|
|
from saradap b |
1310 |
|
|
where b.saradap_pidm = a.saradap_pidm and |
1311 |
|
|
b.saradap_levl_code = a.saradap_levl_code and |
1312 |
|
|
b.saradap_term_code_entry = a.saradap_term_code_entry) } ); |
1313 |
|
|
|
1314 |
|
|
$glb_prepared_sql{"sth_qry_recr_status1"} = $sth_qry_recr_status1; |
1315 |
|
|
|
1316 |
|
|
$sth_qry_recr_status2 = $dbh->prepare( q{ |
1317 |
|
|
select sarappd_apdc_code |
1318 |
|
|
from saradap a, |
1319 |
|
|
sarappd |
1320 |
|
|
where saradap_pidm = ? AND |
1321 |
|
|
saradap_levl_code = ? AND |
1322 |
|
|
saradap_term_code_entry = ? AND |
1323 |
|
|
saradap_appl_no = ( |
1324 |
|
|
select max(saradap_appl_no) |
1325 |
|
|
from saradap b |
1326 |
|
|
where b.saradap_pidm = a.saradap_pidm and |
1327 |
|
|
b.saradap_term_code_entry = a.saradap_term_code_entry) |
1328 |
|
|
AND |
1329 |
|
|
sarappd_pidm = saradap_pidm AND |
1330 |
|
|
sarappd_term_code_entry = saradap_term_code_entry AND |
1331 |
|
|
sarappd_appl_no = saradap_appl_no AND |
1332 |
|
|
to_char(sarappd_apdc_date,'YYYYMMDD')|| |
1333 |
|
|
lpad(to_char(sarappd_seq_no),2,'0') = ( |
1334 |
|
|
select max(to_char(sarappd_apdc_date,'YYYYMMDD')|| |
1335 |
|
|
lpad(to_char(sarappd_seq_no),2,'0')) |
1336 |
|
|
from sarappd |
1337 |
|
|
where sarappd_pidm = saradap_pidm and |
1338 |
|
|
sarappd_term_code_entry = saradap_term_code_entry and |
1339 |
|
|
sarappd_appl_no = saradap_appl_no) } ); |
1340 |
|
|
|
1341 |
|
|
$glb_prepared_sql{"sth_qry_recr_status2"} = $sth_qry_recr_status2; |
1342 |
|
|
} |
1343 |
|
|
|
1344 |
|
|
|
1345 |
|
|
# check if they applied; if not, status is "Recruit" |
1346 |
|
|
$sth_qry_recr_status1->execute($pidm, $level, $term_code); |
1347 |
|
|
@row = $sth_qry_recr_status1->fetchrow_array; |
1348 |
|
|
if ($row[0] ) { |
1349 |
|
|
|
1350 |
|
|
# check if they have an admission decision; if not, status is "Applied" |
1351 |
|
|
$sth_qry_recr_status2->execute($pidm, $level, $term_code); |
1352 |
|
|
@row = $sth_qry_recr_status2->fetchrow_array; |
1353 |
|
|
if ($row[0] ) { |
1354 |
|
|
|
1355 |
|
|
if (substr($row[0], 0, 1) eq "A") { $status = "Admitted" } |
1356 |
|
|
if ($row[0] eq "R") { $status = "Denied" } |
1357 |
|
|
if (substr($row[0], 0, 1) eq "C") { $status = "Cancelled" } |
1358 |
|
|
} |
1359 |
|
|
else { |
1360 |
|
|
$status = "Applied"; |
1361 |
|
|
} |
1362 |
|
|
} |
1363 |
|
|
else { |
1364 |
|
|
$status = "Recruited"; |
1365 |
|
|
} |
1366 |
|
|
|
1367 |
|
|
$status || ($status = "Unknown"); # just in case |
1368 |
|
|
|
1369 |
|
|
|
1370 |
|
|
return { "recr_status" => $status }; |
1371 |
|
|
} |
1372 |
|
|
|
1373 |
|
|
|
1374 |
|
|
sub sbgi_info { |
1375 |
|
|
my($dbh, $sbgi) = @_; |
1376 |
|
|
|
1377 |
|
|
my ($stat_code, $county, $city); |
1378 |
|
|
|
1379 |
|
|
# for performance we will only prepare repeated sql once |
1380 |
|
|
if (exists $glb_prepared_sql{"sth_qry_sbgi_info"} ) { |
1381 |
|
|
$sth_qry_sbgi_info = $glb_prepared_sql{"sth_qry_sbgi_info"}; |
1382 |
|
|
} |
1383 |
|
|
else { |
1384 |
|
|
# rtrim(sobsbgi_cnty_code) needed because somehow the cnty_codes in |
1385 |
|
|
# sobsbgi have a trailing space, but stvcnty_code doesn't. |
1386 |
|
|
$sth_qry_sbgi_info = $dbh->prepare( q{ |
1387 |
|
|
select sobsbgi_stat_code, |
1388 |
|
|
stvcnty_desc, |
1389 |
|
|
sobsbgi_city |
1390 |
|
|
from sobsbgi, |
1391 |
|
|
stvcnty |
1392 |
|
|
where sobsbgi_sbgi_code = ? and |
1393 |
|
|
stvcnty_code (+)= rtrim(sobsbgi_cnty_code) } ); |
1394 |
|
|
|
1395 |
|
|
$glb_prepared_sql{"sth_qry_sbgi_info"} = $sth_qry_sbgi_info; |
1396 |
|
|
} |
1397 |
|
|
|
1398 |
|
|
$sth_qry_sbgi_info->execute($sbgi); |
1399 |
|
|
($stat_code, $county, $city) = $sth_qry_sbgi_info->fetchrow_array; |
1400 |
|
|
|
1401 |
|
|
return { "sbgi_state" => $stat_code, |
1402 |
|
|
"sbgi_county" => $county, |
1403 |
|
|
"sbgi_city" => $city }; |
1404 |
|
|
} |
1405 |
|
|
|
1406 |
|
|
|
1407 |
|
|
sub coll_info { |
1408 |
|
|
my($dbh, $pidm) = @_; |
1409 |
|
|
|
1410 |
|
|
my ($rh_tr_coll); |
1411 |
|
|
|
1412 |
|
|
$rh_tr_coll = last_tr_coll($dbh, $pidm); |
1413 |
|
|
|
1414 |
|
|
add2hash($rh_tr_coll, sbgi_info($dbh, $rh_tr_coll->{prior_coll} ) ); |
1415 |
|
|
|
1416 |
|
|
return $rh_tr_coll; |
1417 |
|
|
} |
1418 |
|
|
|
1419 |
|
|
|
1420 |
|
|
# ============================================================================ |
1421 |
|
|
# Subroutines |
1422 |
|
|
# ============================================================================ |
1423 |
|
|
|
1424 |
|
|
return 1; |
1425 |
|
|
|
1426 |
|
|
|