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); }