Code for Measure C13


Transfer Students
This is an overall headcount of any student
•  earning at least 12 hours at the “sending” institution
•  that transfers to an Arkansas two-year college or four-year university. Includes remedial hours. The institution “sending” the student is counted. An institution may assign up to 2.00 points to this measure. Point(s) earned if number improves. Optional measures may not exceed 4 points.

SQL statements are in RED.


<body>

<?php
// THIS REPORT WRITTEN IN PHP SCRIPTING LANGAUGE
// CONNECTION INFO REMOVED
$acadyr = $_POST['acadyr'];
$rcit = $_POST['rcit'];
$tsit = $_POST['tsit'];
$hours = $_POST['hours'];
$remed = $_POST['remed'];
//$acadyr = '2006';
//$rcit = "'1'";
//$tsit = "'2'";
//$hours = 12;
//$fice_code = '020753';

echo("<h2>Transfer Students with 12 or More Earned Credit Hours</h2>");

switch($rcit)
{
case "'1'":
$rcitx = "4-Year Universities";
$fice_code_list = "'001090', '001089', '001098', '001107', '001108', '001110', '001101', '001085', '001109', '001086', '001092' ";
break;
case "'2'":
$rcitx = "2-Year Colleges";
$fice_code_list = "'012860', '001091', '901090', '011949', '020522', '022209', '009976', '012260', '023482', '012261', '012105', '030633', '020870', '001104', '020753', '021111', '020746', '007738', '005707', '020735', '005732', '005245' ";
break;
case "'1', '2'":
$rcitx = "Any Public Institution";
$fice_code_list = "'001090', '001089', '001098', '001107', '001108', '001110', '001101', '001085', '001109', '001086', '001092', '012860', '001091', '901090', '011949', '020522', '022209', '009976', '012260', '023482', '012261', '012105', '030633', '020870', '001104', '020753', '021111', '020746', '007738', '005707', '020735', '005732', '005245' ";
break;
}
switch($tsit)
{
case "'1'":
$tsitx = "4-Year Universities";
// $fice_code_list = "'001090', '001089', '001098', '001107', '001108', '001110', '001101', '001085', '001109', '001086', '001092' ";
break;
case "'2'":
$tsitx = "2-Year Colleges";
// $fice_code_list = "'012860', '001091', '901090', '011949', '020522', '022209', '009976', '012260', '023482', '012261', '012105', '030633', '020870', '001104', '020753', '021111', '020746', '007738', '005707', '020735', '005732', '005245' ";
break;
case "'1', '2'":
$tsitx = "Any Public Institution";
// $fice_code_list = "'001090', '001089', '001098', '001107', '001108', '001110', '001101', '001085', '001109', '001086', '001092', '012860', '001091', '901090', '011949', '020522', '022209', '009976', '012260', '023482', '012261', '012105', '030633', '020870', '001104', '020753', '021111', '020746', '007738', '005707', '020735', '005732', '005245' ";
break;
}

if($remed == 'Y')
{
$remed_code = "AND c.course_level IN ('0', '1', '2', '7', '8', '9') ";
$remed_text = "Includes Remedial Credit Hours";
}
if($remed == 'N')
{
$remed_code = "AND c.course_level IN ('1', '2', '7', '8', '9') ";
$remed_text = "Does <u>Not</u> Include Remedial Credit Hours";
}

echo("<p>Transfer undergraduates and undergraduate credit hours only.");
echo("<br />Academic Year = <b>".$acadyr."</b> from ".$tsitx." to ".$rcitx." <br />");
echo("and ".$remed_text.".");

echo("<table border=1 cellspacing=0 cellpadding=0>");
echo("<tr valign=center>");
echo("<th>#</th>");
echo("<th>Inst. Type</th>");
echo("<th>Transferring Institution</th>");
echo("<th>UG Transfer Students (03)</th>");
echo("<th>No. >= ".$hours." CrHrs</th>");
echo("<th>Percent</th>");

echo("</tr>");

// list of schools - transferring students
$query1 = " SELECT DISTINCT f.inst_type,
f.school_abbr,
f.fice_code
FROM fice_table f
WHERE f.inst_type IN (".$tsit.")
ORDER BY f.inst_type, f.school_abbr;
";

$n = 1;
$result1 = odbc_exec($conn_id, $query1);
while (odbc_fetch_row($result1))
{
$inst_type = odbc_result($result1, "inst_type");
$school_abbr = odbc_result($result1, "school_abbr");
$fice_code = odbc_result($result1, "fice_code");

echo("<tr valign=top>");
echo("<td align=center>".$n++."</td>");
echo("<td align=center>".$inst_type."</td>");
echo("<td align=left>".$school_abbr."</td>");
//echo("<td align=center>".$fice_code."</td>");

// transferring students
$cnt = 0;
$list = '';
$listx = '';
$query2 = "SELECT DISTINCT s.ssn_id,
MAX(s.academic_year) AS ay,
MAX(s.term) AS trm
FROM student_table s
JOIN fice_table f
ON s.transfer_fice = f.fice_code
WHERE s.transfer_fice = '".$fice_code."'
AND s.enroll_status = '03'
AND s.transfer_fice IS NOT NULL
AND s.academic_year = '".$acadyr."'
AND s.term IN ('0', '1', '2', '3')
AND s.fice_code IN (".$fice_code_list.")
GROUP BY s.ssn_id
ORDER BY s.ssn_id;
";
//echo("<p>".$query2."</p><br />");

$ssch_cnt = 0;
$result2 = odbc_exec($conn_id, $query2);
while (odbc_fetch_row($result2))
{
$cnt++;
$ssn_id = odbc_result($result2, "ssn_id");
$ay = odbc_result($result2, "ay");
$trm = odbc_result($result2, "trm");
$ayx = $ay - 1;

//echo($ssn_id."-".$ay."-".$trm."<br />");

// credit hours for each transfer student
$query3 = "SELECT r.ssn_id,
SUM(c.credit_hours) AS ssch
FROM registration_table r
JOIN course_table c
ON r.fice_code = c.fice_code
AND r.academic_year = c.academic_year
AND r.term = c.term
AND r.sequence = c.sequence
AND r.level_sequence = c.level_sequence
WHERE r.ssn_id = '".$ssn_id."'
AND r.fice_code = '".$fice_code."'
AND ((r.academic_year = '".$ay."' AND r.term < '".$trm."')
OR (r.academic_year <= '".$ayx."'))
AND (UPPER(r.end_of_term) IN ('A', 'B', 'C', 'D', 'S', 'K', 'R', '1')
OR r.end_of_term IS NULL)
".$remed_code."
AND (UPPER(c.tech_inst_funding) NOT IN ('O', 'C', 'M', 'T')
OR c.tech_inst_funding IS NULL)
GROUP BY r.ssn_id
ORDER BY r.ssn_id;
";
// echo("<p>".$query3."<p><hr />");

$result3 = odbc_exec($conn_id, $query3);
while (odbc_fetch_row($result3))
{
$ssch = odbc_result($result3, "ssch");

if($ssch >= $hours)
{ $ssch_cnt++; }
}

}
echo("<td align=right>".number_format($cnt,0)."</td>");
$cnt_total = $cnt_total + $cnt;
echo("<td align=right>".number_format($ssch_cnt,0)."</td>");
$ssch_cnt_total = $ssch_cnt_total + $ssch_cnt;

$ssch_perc = ($ssch_cnt * 100) / $cnt;
echo("<td align=right>".number_format($ssch_perc,1)."%</td>");

echo("</tr>");
}
// this bracket ends list of schools

// all totals
echo("<tr>");
echo("<td colspan=3>Totals</td>");
echo("<td align=right>".number_format($cnt_total,0)."</td>");
echo("<td align=right>".number_format($ssch_cnt_total,0)."</td>");
$perc = ($ssch_cnt_total * 100) / $cnt_total;
echo("<td align=right>".number_format($perc,1)."%</td>");

echo("</tr>");

echo("</table>");

odbc_error();
odbc_close($conn_id);
?>
<p></p>

</body>