public function showSpecificCollege()
{
$collegeIDInput = Input::get('college-dropdown');
$college = College::find($collegeIDInput);
$collegedepartments = $college->departments()->whereHas('programs', function ($q) {
$q->whereNotIn('programid', array(62, 66, 38));
$q->where('degreelevel', 'U');
})->get();
//ave students per year and ave difference
$programids = $college->programs()->whereNotIn('programid', array(62, 66, 38))->where('degreelevel', 'U')->lists('programid');
//To get batches of program whithin 2000-2009
$yearsArray = Studentterm::whereIn('programid', $programids)->where('year', '>', 1999)->where('year', '<', 2014)->groupBy('year')->orderBy('year', 'asc')->lists('year');
$yearlyStudentAverage = [];
$collegeDepartmentsAverage = [];
foreach ($yearsArray as $yearData) {
$aveStudents = round($college->getYearlyAveStudents($yearData), 2);
if ($aveStudents > 1) {
$yearlyStudentAverage[$yearData] = $aveStudents;
}
}
foreach ($collegedepartments as $collegedepartment) {
$collegeDepartmentsAverage[$collegedepartment->unitname] = round($collegedepartment->getAveStudents(), 2);
}
$aveAttrition = $college->getAveAttrition();
$aveShiftRate = $college->getAveShiftRate();
$aveYearsBeforeDropout = $college->getAveYearsBeforeDropout();
$aveYearsBeforeShifting = $college->getAveYearsBeforeShifting();
$batchAttrition = $college->getBatchAttrition();
$departmentsAttrition = $college->getDepartmentsAveBatchAttrition();
$gradeCount = $college->getGradeCount();
$shiftGradeCount = $college->getShiftGradeCount();
$stbracketCount = $college->getSTBracketCount();
$shiftBracketCount = $college->getShiftSTBracketCount();
return View::make('college.college-specific', ['college' => $college, 'yearlyStudentAverage' => $yearlyStudentAverage, 'collegedepartments' => $collegedepartments, 'collegeDepartmentsAverage' => $collegeDepartmentsAverage, 'aveAttrition' => $aveAttrition, 'batchAttrition' => $batchAttrition, 'aveShiftRate' => $aveShiftRate, 'aveYearsBeforeDropout' => $aveYearsBeforeDropout, 'aveYearsBeforeShifting' => $aveYearsBeforeShifting, 'departmentsAttrition' => $departmentsAttrition, 'gradeCount' => $gradeCount, 'shiftGradeCount' => $shiftGradeCount, 'stbracketCount' => $stbracketCount, 'shiftBracketCount' => $shiftBracketCount]);
}
public function getAveYearsBeforeShifting()
{
//To get batches of program whithin 2000-2009
$programids = $this->programs()->whereNotIn('programid', array(62, 66, 38, 117))->where('degreelevel', 'U')->lists('programid');
$progYears = Studentterm::whereIn('programid', $programids)->groupBy('year')->orderBy('year', 'asc')->lists('year');
if ($this->revisionyear > 2009) {
$max = 2013;
} else {
if ($this->programid == 28) {
$max = 2013 - 4;
} else {
$max = 2013 - $this->numyears;
}
}
$batches = [];
foreach ($progYears as $progYear) {
if ($progYear > 1999 && $progYear < $max + 1) {
array_push($batches, $progYear * 100000);
}
}
$min = min($batches);
$max = max($batches) + 100000;
$dropouts = DB::table('studentdropouts')->where('studentid', '>', $min)->where('studentid', '<', $max)->whereIn('lastprogramid', $programids)->lists('studentid');
$shiftees = DB::table('studentshifts')->join('programs', 'program1id', '=', 'programid')->select('studentid', 'program1years')->where('studentid', '>', $min)->where('studentid', '<', $max)->whereIn('program1id', $programids)->whereNotIn('program2id', $programids)->where('program2id', '!=', 38)->whereRaw('program1years < CAST(numyears AS numeric)')->whereNotIn('studentid', $dropouts)->get();
$sumYears = 0;
foreach ($shiftees as $shiftee) {
$sumYears = $sumYears + $shiftee->program1years;
}
if (count($shiftees) != 0) {
$aveYears = $sumYears / count($shiftees);
} else {
$aveYears = 0;
}
return round($aveYears, 2);
}
