Code for Measure U17


Regional Economic Needs Programs Credentials
Number of all credentials (technical certificates and above) earned by a student for an academic year regardless of enrollment status in programs identified by the institution and approved by the Arkansas Higher Education Coordinating Board

SQL statements are in RED.


<body>
<?php
// THIS REPORT WRITTEN IN PHP SCRIPTING LANGAUGE
// CONNECTION INFO REMOVED
$acadyr = $_POST['acadyr'];
$year = $_POST['acadyr'];
$degree_level = $_POST['degree_level'];
$inst_type = $_POST['inst_type'];
$credgrad = $_POST['credgrad'];

if($credgrad == 'A')
{
$distinct = " ";
$distinct_text = "All Credentials";
}
if($credgrad == 'B')
{
$distinct = "DISTINCT ";
$distinct_text = "Unduplicated Graduates";
}

switch($degree_level)
{
case "A": $dl_code = " ";
$degree_text = "All Credentials";
break;

case "B": $dl_code = "AND g.degree_level IN ('02', '03', '04', '05', '06', '07', '08', '09', '10', '11','12', '17', '18', '19', 'A') ";
$degree_text = "All Credentials without CPs";
break;

case "C": $dl_code = "AND g.degree_level IN ('05') ";
$degree_text = "Bachelors Only";
break;

case "D": $dl_code = "AND g.degree_level IN ('03') ";
$degree_text = "Associates Only";
break;

case "E": $dl_code = "AND g.degree_level IN ('01', '02', '03', '04', '05') ";
$degree_text = "Undergraduates Only";
break;

case "F": $dl_code = "AND g.degree_level IN ('02', '03', '04', '05') ";
$degree_text = "Undergraduates Only without CPs";
break;

case "G": $dl_code = "AND g.degree_level IN ('02', '03', '04', '06', '07', '08', '09', '10', '11','12', '17', '18', '19', 'A') ";
$degree_text = "All Credentials without CPs and BAs";
break;
}

// Set up report headings
$year = array($year - 4, $year -3, $year -2, $year -1, $year);

// FIRST ROW OF LABELS

echo("<h2>REGIONAL CRITICAL NEEDS: <br />
".$degree_text.", ".$distinct_text."</h2>");

echo("<table border=1 cellspacing=0 cellpadding=0>");
echo("<tr>");
echo("<th>#</th>");
echo("<th>Inst Type</th>");
echo("<th>Institution</th>");
echo("<th>AY".$year[0]."</th>");
echo("<th>AY".$year[1]."</th>");
echo("<th>AY".$year[2]."</th>");
echo("<th>AY".$year[3]."</th>");
echo("<th>AY".$year[4]."</th>");
echo("</tr>");

$query1 = "SELECT DISTINCT
f.inst_type,
f.school_abbr,
f.fice_code
FROM fice_table f
WHERE f.inst_type in ('1')
ORDER BY f.inst_type, f.school_abbr
";

$result1 = odbc_exec($conn_id, $query1);

$n = 1;
while (odbc_fetch_row($result1))
{
$earners_total = 0;
$seekers_total = 0;
$inst_type = (odbc_result($result1, "inst_type"));
$fice_code = (odbc_result($result1, "fice_code"));
$school_abbr = (odbc_result($result1, "school_abbr"));

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

// regional critical list of CIP codes
switch($fice_code)
{
case '001090':
$cip_list = "AND (
( g.cip_2000_code = '13' AND
(g.cip_2000_detail = '0301' OR cip_2000_detail = '0401' OR cip_2000_detail = '0408'
OR g.cip_2000_detail = '1001' OR g.cip_2000_detail = '1004' OR g.cip_2000_detail = '1101'
OR g.cip_2000_detail = '1102' OR g.cip_2000_detail = '1202' OR g.cip_2000_detail = '1203'
OR g.cip_2000_detail = '1210' OR g.cip_2000_detail = '1299' OR g.cip_2000_detail = '1303'
OR g.cip_2000_detail = '1305' OR g.cip_2000_detail = '1306' OR g.cip_2000_detail = '1311'
OR g.cip_2000_detail = '1312' OR g.cip_2000_detail = '1314' OR g.cip_2000_detail = '1315'
OR g.cip_2000_detail = '1317' OR g.cip_2000_detail = '1322' OR g.cip_2000_detail = '1323'
OR g.cip_2000_detail = '1329'))
OR
( g.cip_2000_code = '44' AND g.cip_2000_detail = '0701')
OR
( g.cip_2000_code = '51' AND
(g.cip_2000_detail = '0203' OR g.cip_2000_detail = '0204' OR g.cip_2000_detail = '0701'
OR g.cip_2000_detail = '0806' OR g.cip_2000_detail = '0907'))
OR
( g.cip_2010_code = '13' AND
(g.cip_2010_detail = '0301' OR cip_2010_detail = '0401' OR cip_2010_detail = '0408'
OR g.cip_2010_detail = '1001' OR g.cip_2010_detail = '1004' OR g.cip_2010_detail = '1101'
OR g.cip_2010_detail = '1102' OR g.cip_2010_detail = '1202' OR g.cip_2010_detail = '1203'
OR g.cip_2010_detail = '1210' OR g.cip_2010_detail = '1299' OR g.cip_2010_detail = '1303'
OR g.cip_2010_detail = '1305' OR g.cip_2010_detail = '1306' OR g.cip_2010_detail = '1311'
OR g.cip_2010_detail = '1312' OR g.cip_2010_detail = '1314' OR g.cip_2010_detail = '1315'
OR g.cip_2010_detail = '1317' OR g.cip_2010_detail = '1322' OR g.cip_2010_detail = '1323'
OR g.cip_2010_detail = '1329'))
OR
( g.cip_2010_code = '44' AND g.cip_2010_detail = '0701')
OR
( g.cip_2010_code = '51' AND
(g.cip_2010_detail = '0203' OR g.cip_2010_detail = '0204' OR g.cip_2010_detail = '0701'
OR g.cip_2010_detail = '0806' OR g.cip_2010_detail = '0907'))
)
";
$cip_group = ("<p>ASUJ = ".$cip_list."</p>");
break;

case '001089':
$cip_list = "AND (
( g.cip_2000_code = '13' AND g.cip_2000_detail = '9999'
AND (g.degree_1 IN ('3975') OR g.degree_2 IN ('3975') OR g.degree_3 IN ('3975')) )
OR
( g.cip_2000_code = '43' AND g.cip_2000_detail = '0302'
AND (g.degree_1 IN ('2490', '6490') OR g.degree_2 IN ('2490', '6490') OR g.degree_3 IN ('2490', '6490')) )
OR
( g.cip_2000_code = '52' AND g.cip_2000_detail = '0901'
AND (g.degree_1 IN ('2760') OR g.degree_2 IN ('2760') OR g.degree_3 IN ('2760')) )

OR
( g.cip_2010_code = '13' AND g.cip_2010_detail = '9999'
AND (g.degree_1 IN ('3975') OR g.degree_2 IN ('3975') OR g.degree_3 IN ('3975')) )
OR
( g.cip_2010_code = '43' AND g.cip_2010_detail = '0302'
AND (g.degree_1 IN ('2490', '6490') OR g.degree_2 IN ('2490', '6490') OR g.degree_3 IN ('2490', '6490')) )
OR
( g.cip_2010_code = '52' AND g.cip_2010_detail = '0901'
AND (g.degree_1 IN ('2760') OR g.degree_2 IN ('2760') OR g.degree_3 IN ('2760')) )
)
";
$cip_group = ($cip_group."<p>ATU = ".$cip_list."</p>");
break;

case '001098':
$cip_list = "AND (
( g.cip_2000_code = '13' AND
(g.cip_2000_detail = '0301' OR cip_2000_detail = '0408' OR cip_2000_detail = '1001'
OR g.cip_2000_detail = '1015' OR g.cip_2000_detail = '1203' OR g.cip_2000_detail = '1317'
OR g.cip_2000_detail = '1401'))
OR
( g.cip_2000_code = '26' AND g.cip_2000_detail = '0101')
OR
( g.cip_2000_code = '40' AND
(g.cip_2000_detail = '0501' OR g.cip_2000_detail = '0801'))
OR
( g.cip_2010_code = '13' AND
(g.cip_2010_detail = '0301' OR cip_2010_detail = '0408' OR cip_2010_detail = '1001'
OR g.cip_2010_detail = '1015' OR g.cip_2010_detail = '1203' OR g.cip_2010_detail = '1317'
OR g.cip_2010_detail = '1401'))
OR
( g.cip_2010_code = '26' AND g.cip_2010_detail = '0101')
OR
( g.cip_2010_code = '40' AND
(g.cip_2010_detail = '0501' OR g.cip_2010_detail = '0801'))
)
";
$cip_group = ($cip_group."<p>HSU = ".$cip_list."</p>");
break;

case '001107':
$cip_list = "AND (
(g.cip_2000_code IN ('01') AND g.cip_2000_detail IN ('0000') AND g.degree_level IN ('07'))
OR (g.cip_2000_code IN ('01') AND g.cip_2000_detail IN ('0101') AND g.degree_level IN ('05'))
OR (g.cip_2000_code IN ('01') AND g.cip_2000_detail IN ('0102') AND g.degree_level IN ('03'))
OR (g.cip_2000_code IN ('24') AND g.cip_2000_detail IN ('0102') AND g.degree_level IN ('03', '05'))
OR (g.cip_2000_code IN ('51') AND g.cip_2000_detail IN ('3801') AND g.degree_level IN ('03', '05'))
OR (g.cip_2010_code IN ('01') AND g.cip_2010_detail IN ('0000') AND g.degree_level IN ('07'))
OR (g.cip_2010_code IN ('01') AND g.cip_2010_detail IN ('0101') AND g.degree_level IN ('05'))
OR (g.cip_2010_code IN ('01') AND g.cip_2010_detail IN ('0102') AND g.degree_level IN ('03'))
OR (g.cip_2010_code IN ('24') AND g.cip_2010_detail IN ('0102') AND g.degree_level IN ('03', '05'))
OR (g.cip_2010_code IN ('51') AND g.cip_2010_detail IN ('3801') AND g.degree_level IN ('03', '05'))
)
";
$cip_group = ($cip_group."<p>SAUM = ".$cip_list."</p>");
break;

case '001108':
$cip_list = "AND (g.cip_2000_code IN ('14', '26', '40') OR g.cip_2010_code IN ('14', '26', '40')) ";
$cip_group = ($cip_group."<p>UAF = ".$cip_list."</p>");
break;

case '001110':
$cip_list = "AND (
( g.cip_2000_code = '30' AND g.cip_2000_detail = '9999'
AND (g.degree_1 IN ('2856', '5335') OR g.degree_2 IN ('2856', '5335') OR g.degree_3 IN ('2856', '5335')) )
OR
( g.cip_2000_code = '43' AND g.cip_2000_detail = '0103'
AND (g.degree_1 IN ('3390') OR g.degree_2 IN ('3390') OR g.degree_3 IN ('3390')) )
OR
( g.cip_2000_code = '51' AND g.cip_2000_detail = '3801'
AND (g.degree_1 IN ('4240') OR g.degree_2 IN ('4240') OR g.degree_3 IN ('4240')) )

OR
( g.cip_2010_code = '30' AND g.cip_2010_detail = '9999'
AND (g.degree_1 IN ('2856', '5335') OR g.degree_2 IN ('2856', '5335') OR g.degree_3 IN ('2856', '5335')) )
OR
( g.cip_2010_code = '43' AND g.cip_2010_detail = '0103'
AND (g.degree_1 IN ('3390') OR g.degree_2 IN ('3390') OR g.degree_3 IN ('3390')) )
OR
( g.cip_2010_code = '51' AND g.cip_2010_detail = '3801'
AND (g.degree_1 IN ('4240') OR g.degree_2 IN ('4240') OR g.degree_3 IN ('4240')) )
)
";
$cip_group = ($cip_group."<p>UAFS = ".$cip_list."</p>");
break;

case '001101':
$cip_list = "AND (g.cip_2000_code IN ('14', '51') OR g.cip_2010_code IN ('14', '51')) ";
$cip_group = ($cip_group."<p>UALR = ".$cip_list."</p>");
break;

case '001085':
$cip_list = "AND (
( g.cip_2000_code = '03' AND
(g.cip_2000_detail = '0501' OR cip_2000_detail = '0506' OR cip_2000_detail = '0601'))
OR
( g.cip_2000_code = '13' AND
(g.cip_2000_detail = '0401' OR g.cip_2000_detail = '1203' OR g.cip_2000_detail = '1314'))
OR
( g.cip_2000_code = '15' AND
(g.cip_2000_detail = '0403' OR g.cip_2000_detail = '1102'))
OR
( g.cip_2010_code = '03' AND
(g.cip_2010_detail = '0501' OR cip_2010_detail = '0506' OR cip_2010_detail = '0601'))
OR
( g.cip_2010_code = '13' AND
(g.cip_2010_detail = '0401' OR g.cip_2010_detail = '1203' OR g.cip_2010_detail = '1314'))
OR
( g.cip_2010_code = '15' AND
(g.cip_2010_detail = '0403' OR g.cip_2010_detail = '1102'))
)
";
$cip_group = ($cip_group."<p>UAM = ".$cip_list."</p>");
break;

case '001109':
$cip_list = "AND (g.cip_2000_code IN ('00') OR g.cip_2010_code IN ('00')) ";
$cip_group = ($cip_group."<p>UAMS = ".$cip_list."</p>");
break;

case '001086':
$cip_list = "AND (
(g.cip_2000_code = '13' AND (g.cip_2000_detail = '1210' OR g.cip_2000_detail = '1314'))
OR (g.cip_2000_code = '43' AND g.cip_2000_detail = '0104')
OR (g.cip_2000_code = '40' AND (g.cip_2000_detail = '0501' OR g.cip_2000_detail = '0801'))
OR (g.cip_2010_code = '13' AND (g.cip_2010_detail = '1210' OR g.cip_2010_detail = '1314'))
OR (g.cip_2010_code = '43' AND g.cip_2010_detail = '0104')
OR (g.cip_2010_code = '40' AND (g.cip_2010_detail = '0501' OR g.cip_2010_detail = '0801'))
)
";
$cip_group = ($cip_group."<p>UAPB = ".$cip_list."</p>");
break;

case '001092':
$cip_list = "AND (
(g.cip_2000_code = '13' AND (g.cip_2000_detail = '1311' OR g.cip_2000_detail = '1316'))
OR (g.cip_2000_code = '44' AND g.cip_2000_detail = '0201')
OR (g.cip_2000_code = '51' AND
(g.cip_2000_detail = '1501' OR g.cip_2000_detail = '2306' OR g.cip_2000_detail = '2308' OR g.cip_2000_detail = '3801'))
OR (g.cip_2010_code = '13' AND (g.cip_2010_detail = '1311' OR g.cip_2010_detail = '1316'))
OR (g.cip_2010_code = '44' AND g.cip_2010_detail = '0201')
OR (g.cip_2010_code = '51' AND
(g.cip_2010_detail = '1501' OR g.cip_2010_detail = '2306' OR g.cip_2010_detail = '2308' OR g.cip_2010_detail = '3801'))
) ";
$cip_group = ($cip_group."<p>UCA = ".$cip_list."</p>");
break;
default:
$cip_list = "AND (g.cip_2000_code IN ('00') OR g.cip_2010_code IN ('00'))
";
// $cip_group = ($cip_group."CLEAR = ".$cip_list."<br />");
break;
}

for ($b = 0; $b <= 4; $b++) // BEGIN LOOP #2 FOR EACH YEAR
{
$cnt = 0;

$queryX = "SELECT COUNT(".$distinct."(g.ssn_id)) AS earners
FROM graduated_student_table g
WHERE g.fice_code = '".$fice_code."'
AND g.academic_year = '".$year[$b]."'
".$dl_code."
".$cip_list." ;
";
// echo("<p>QueryX = ".$queryX."</p><br />");

$resultX = odbc_exec($conn_id, $queryX);

while (odbc_fetch_row($resultX))
{
$earners = odbc_result($resultX, "earners");
$totalearnercount[$b] = $totalearnercount[$b] + $earners;

if ($inst_type == '1')
{
$earnercount_1[$b] = $earnercount_1[$b] + $earners;
}
if ($inst_type == '2')
{
$earnercount_2[$b] = $earnercount_2[$b] + $earners;
}
}

echo("<td align=right>".number_format($earners,0). "</td>");

$earners_total = $earners_total + $earners;

}

echo("</tr>");
}

echo("<tr>");
echo("<td colspan=3 align=left>4-Year Universities</td>");
// year 1
echo("<td align=right>".number_format($earnercount_1[0],0)."</td>");
// year 2
echo("<td align=right>".number_format($earnercount_1[1],0). "</td>");
// year 3
echo("<td align=right>".number_format($earnercount_1[2],0). "</td>");
// year 4
echo("<td align=right>".number_format($earnercount_1[3],0). "</td>");
// year 5
echo("<td align=right>".number_format($earnercount_1[4],0). "</td>");

echo("</tr>");

echo("</table>");

echo("<p>The CIP Codes are: <br />".$cip_group."</p>");

odbc_free_result($result1);
odbc_free_result($result2);
odbc_free_result($result3);
odbc_free_result($result4);
odbc_free_result($result5);
odbc_free_result($result6);
odbc_free_result($result7);
odbc_free_result($result8);
odbc_free_result($resultX);
odbc_free_result($resultY);
odbc_error();
odbc_close($conn_id);
?>
</body>