Code for Measures U07-U08


This report works for both the STEM and HIGH DEMAND Credentials report. A fliter is used on a preceding page to determine which report is selected. See the variable below called $whichlist. This determines which report is generated.

A = New Stem Only
B = New High Demand Only
C = Both New STEM and High Demand

STEM Credentials
This is an overall headcount of all certificates and degrees (Technical Certificates and above) awarded by the institution in the STEM CIP Codes. The 2011 version of the STEM CIP Codes were obtained from ICE (US Immigration and Customs Enforcement).

High Demand Credentials
This is an overall headcount of all certificates and degrees (Technical Certificates and above) awarded by the institution in the HIGH DEMAND CIP Codes. The 2011 version of the HIGH DEMAND CIP Codes were obtained from ADWS (Arkansas Department of Workforce Services).

The STEM CIP Codes are imported by reference. See the STEM CIP Codes.
The HIGH DEMAND CIP Codes are imported by reference. See the HIGH DEMAND CIP Codes.


SQL Statement are in RED.

<body>

<?php
// THIS REPORT WRITTEN IN PHP SCRIPTING LANGAUGE
// CONNECTION INFO REMOVED

$acadyr = $_POST['acadyr'];
$inst_type = $_POST['inst_type'];
$degree_level = $_POST['degree_level'];
$whichlist = $_POST['whichlist'];

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') ";
$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;
}

if($whichlist == 'A')
{
require("stem_cip_new.php");
$stem_hi_code = "AND (".$stem_cip_new.") ";
$list_text = "New STEM Only";
}
if($whichlist == 'B')
{
require("hi-demand_cip_new.php");
$stem_hi_code = "AND (".$hidemand_cip.") ";
$list_text = "New Hi-Demand (New ADWS Version) Only";
}
if($whichlist == 'C')
{
require("stem_cip_new.php");
require("hi-demand_cip_new.php");
$stem_hi_code = "AND (".$stem_cip_new." OR ".$hidemand_cip.") ";
$list_text = "New STEM and New Hi-Demand";
}

echo("<h2>".$list_text."</h2><hr />");

echo("<p><b>Academic Year = ".$acadyr." Inst. Type = ".$inst_type."
for ".$degree_text." for ".$list_text."</b></p>");

echo("<table border=1 cellspacing=0 cellpadding=0>");
echo("<tr>");
echo("<th rowspan=1>#</th>");
echo("<th rowspan=1>IT</th>");
echo("<th rowspan=1>College</th>");
echo("<th colspan=1>Doctoral or Professional Degrees</th>");
echo("<th colspan=1>Specialists & Post-Masters Degrees/Cert.</th>");
echo("<th colspan=1>Masters Degrees</th>");
echo("<th colspan=1>Post-Bachelor Degrees</th>");
echo("<th colspan=1>Bachelor Degrees</th>");
echo("<th colspan=1>Advanced Certificates</th>");
echo("<th colspan=1>Associate Degrees</th>");
echo("<th colspan=1>Technical Certificates</th>");
echo("<th colspan=1>Certificates of Profiency</th>");
echo("<th colspan=1>TOTAL</th>");

echo("</tr>");

// LIST OF COLLEGES
$query1 = "SELECT f.school_abbr AS abbr,
f.fice_code
FROM sisdb.dbo.fice_table f
WHERE f.inst_type IN ('".$inst_type."')
ORDER BY f.school_abbr
";

$result1 = odbc_exec($conn_id, $query1);
$n = 1;
while (odbc_fetch_row($result1))
{
$abbr = odbc_result($result1, "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>".$abbr."</td>");
//echo("<td align=center>".$fice_code."</td>");

// initialize all degree levels - set to 0 (zero)
$cp = 0;
$tc = 0;
$assoc = 0;
$ac = 0;
$bacc = 0;
$postbacc = 0;
$master = 0;
$special = 0;
$doctor = 0;
$prof = 0;
$pprofc = 0;
$pprofd = 0;
$doctorrs = 0;
$doctorprof = 0;
$doctorother = 0;

$total_cat = 0;
$total_wt = 0;

// all
$query2 = "SELECT
COUNT(CASE g.degree_level WHEN 01 THEN g.degree_level ELSE NULL END) AS cp,
COUNT(CASE g.degree_level WHEN 02 THEN g.degree_level ELSE NULL END) AS tc,
COUNT(CASE g.degree_level WHEN 03 THEN g.degree_level ELSE NULL END) AS assoc,
COUNT(CASE g.degree_level WHEN 04 THEN g.degree_level ELSE NULL END) AS ac,
COUNT(CASE g.degree_level WHEN 05 THEN g.degree_level ELSE NULL END) AS bacc,
COUNT(CASE g.degree_level WHEN 06 THEN g.degree_level ELSE NULL END) AS postbacc,
COUNT(CASE g.degree_level WHEN 07 THEN g.degree_level ELSE NULL END) AS master,
COUNT(CASE g.degree_level WHEN 08 THEN g.degree_level ELSE NULL END) AS special,
COUNT(CASE g.degree_level WHEN 09 THEN g.degree_level ELSE NULL END) AS doctor,
COUNT(CASE g.degree_level WHEN 10 THEN g.degree_level ELSE NULL END) AS prof,
COUNT(CASE g.degree_level WHEN 11 THEN g.degree_level ELSE NULL END) AS pprofc,
COUNT(CASE g.degree_level WHEN 12 THEN g.degree_level ELSE NULL END) AS pprofd,
COUNT(CASE g.degree_level WHEN 17 THEN g.degree_level ELSE NULL END) AS doctorrs,
COUNT(CASE g.degree_level WHEN 18 THEN g.degree_level ELSE NULL END) AS doctorprof,
COUNT(CASE g.degree_level WHEN 19 THEN g.degree_level ELSE NULL END) AS doctorother
FROM sisdb.dbo.graduated_student_table g
JOIN sisdb.dbo.fice_table f
ON g.fice_code = f.fice_code
WHERE f.fice_code = '".$fice_code."'
".$dl_code."
".$stem_hi_code."
AND g.academic_year = '".$acadyr."'
;";

//echo("<p>".$query2."</p><hr />");

$result2 = odbc_exec($conn_id, $query2);
while (odbc_fetch_row($result2))
{
$cp = odbc_result($result2, "cp");
$tc = odbc_result($result2, "tc");
$assoc = odbc_result($result2, "assoc");
$ac = odbc_result($result2, "ac");
$bacc = odbc_result($result2, "bacc");
$postbacc = odbc_result($result2, "postbacc");
$master = odbc_result($result2, "master");
$special = odbc_result($result2, "special");
$doctor = odbc_result($result2, "doctor");
$prof = odbc_result($result2, "prof");
$pprofc = odbc_result($result2, "pprofc");
$pprofd = odbc_result($result2, "pprofd");
$doctorrs = odbc_result($result2, "doctorrs");
$doctorprof = odbc_result($result2, "doctorprof");
$doctorother = odbc_result($result2, "doctorother");
}

$cat_doc = $doctor + $prof + $doctorrs + $doctorprof + $doctorother;
$cat_special = $special;
$cat_mast = $master;
$cat_postbacc = $postbacc;
$cat_bacc = $bacc;
$cat_ac = $ac;
$cat_assoc = $assoc;
$cat_tc = $tc;
$cat_cp = $cp;

$wt_cat_doc = $cat_doc * $wt_docprof;
$wt_cat_special = $cat_special * $wt_special;
$wt_cat_mast = $cat_mast * $wt_master;
$wt_cat_postbacc = $cat_postbacc * $wt_postbacc;
$wt_cat_bacc = $cat_bacc * $wt_bacc;
$wt_cat_ac = $cat_ac * $wt_ac;
$wt_cat_assoc = $cat_assoc * $wt_assoc;
$wt_cat_tc = $cat_tc * $wt_tc;
$wt_cat_cp = $cat_cp * $wt_cp;

echo("<td align=right>".number_format($cat_doc,0)."</td>");
$cat_doc_sum = $cat_doc_sum + $cat_doc;
$wt_cat_doc_sum = $wt_cat_doc_sum + $wt_cat_doc;

echo("<td align=right>".number_format($cat_special,0)."</td>");
$cat_special_sum = $cat_special_sum + $cat_special;
$wt_cat_special_sum = $wt_cat_special_sum + $wt_cat_special;

echo("<td align=right>".number_format($cat_mast,0)."</td>");
$cat_mast_sum = $cat_mast_sum + $cat_mast;
$wt_cat_mast_sum = $wt_cat_mast_sum + $wt_cat_mast;

echo("<td align=right>".number_format($cat_postbacc,0)."</td>");
$cat_postbacc_sum = $cat_postbacc_sum + $cat_postbacc;
$wt_cat_postbacc_sum = $wt_cat_postbacc_sum + $wt_cat_postbacc;

echo("<td align=right>".number_format($cat_bacc,0)."</td>");
$cat_bacc_sum = $cat_bacc_sum + $cat_bacc;
$wt_cat_bacc_sum = $wt_cat_bacc_sum + $wt_cat_bacc;

echo("<td align=right>".number_format($cat_ac,0)."</td>");
$cat_ac_sum = $cat_ac_sum + $cat_ac;
$wt_cat_ac_sum = $wt_cat_ac_sum + $wt_cat_ac;

echo("<td align=right>".number_format($cat_assoc,0)."</td>");
$cat_assoc_sum = $cat_assoc_sum + $cat_assoc;
$wt_cat_assoc_sum = $wt_cat_assoc_sum + $wt_cat_assoc;

echo("<td align=right>".number_format($cat_tc,0)."</td>");
$cat_tc_sum = $cat_tc_sum + $cat_tc;
$wt_cat_tc_sum = $wt_cat_tc_sum + $wt_cat_tc;

echo("<td align=right>".number_format($cat_cp,0)."</td>");
$cat_cp_sum = $cat_cp_sum + $cat_cp;
$wt_cat_cp_sum = $wt_cat_cp_sum + $wt_cat_cp;

$total_cat = $cat_doc + $cat_special + $cat_mast + $cat_postbacc + $cat_bacc + $cat_ac + $cat_assoc + $cat_tc + $cat_cp;
$total_wt = $wt_cat_doc + $wt_cat_special + $wt_cat_mast + $wt_cat_postbacc + $wt_cat_bacc + $wt_cat_ac + $wt_cat_assoc + $wt_cat_tc + $wt_cat_cp;

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

$sum_total_cat = $sum_total_cat + $total_cat;
$sum_total_wt = $sum_total_wt + $total_wt;

$grand_total_cat = $grand_total_cat + $total_cat;
$grand_total_wt = $grand_total_wt + $total_wt;

echo("</tr>");
}

// all totals
echo("<tr>");
echo("<td colspan=3>Grand Totals</td>");
echo("<td align=right>".number_format($cat_doc_sum,0)."</td>");
echo("<td align=right>".number_format($cat_special_sum,0)."</td>");
echo("<td align=right>".number_format($cat_mast_sum,0)."</td>");
echo("<td align=right>".number_format($cat_postbacc_sum,0)."</td>");
echo("<td align=right>".number_format($cat_bacc_sum,0)."</td>");
echo("<td align=right>".number_format($cat_ac_sum,0)."</td>");
echo("<td align=right>".number_format($cat_assoc_sum,0)."</td>");
echo("<td align=right>".number_format($cat_tc_sum,0)."</td>");
echo("<td align=right>".number_format($cat_cp_sum,0)."</td>");
echo("<td align=right>".number_format($grand_total_cat,0)."</td>");
echo("</tr>");

echo("</table>");

odbc_error();
odbc_close($conn_id);
?>

</body>
</html>