public function showSpecificProgram()
{
$programIDInput = Input::get('program-dropdown');
$program = Program::find($programIDInput);
//ave students per year and ave difference
$yearsArray = Studentterm::where('programid', $program->programid)->where('year', '>', 1999)->where('year', '<', 2014)->groupBy('year')->orderBy('year', 'asc')->lists('year');
$yearlyStudentAverage = [];
$yearlySemDifference = [];
foreach ($yearsArray as $yearData) {
$aveStudents = $program->getYearlyAveStudents($yearData);
if ($aveStudents > 1) {
$yearlyStudentAverage[$yearData] = $aveStudents;
}
}
$aveYearsOfStay = $program->getAveYearsOfStay();
$aveYearsBeforeDropout = $program->getAveYearsBeforeDropout();
$aveYearsBeforeShifting = $program->getAveYearsBeforeShifting();
$aveAttrition = $program->getAveAttrition();
$aveShiftRate = $program->getAveShiftRate();
$batchAttrition = $program->getBatchAttrition();
$batchShiftRate = $program->getBatchShiftRate();
$division = $program->getDivision();
$numYears = $program->getNumYears();
$gradeCount = $program->getGradeCount();
$shiftGradeCount = $program->getShiftGradeCount();
$stbracketCount = $program->getSTBracketCount();
$shiftBracketCount = $program->getShiftSTBracketCount();
return View::make('program.program-specific', ['program' => $program, 'aveYearsOfStay' => $aveYearsOfStay, 'yearlyStudentAverage' => $yearlyStudentAverage, 'aveYearsBeforeShifting' => $aveYearsBeforeShifting, 'aveYearsBeforeDropout' => $aveYearsBeforeDropout, 'aveAttrition' => $aveAttrition, 'batchAttrition' => $batchAttrition, 'aveShiftRate' => $aveShiftRate, 'batchShiftRate' => $batchShiftRate, 'division' => $division, 'numYears' => $numYears, 'gradeCount' => $gradeCount, 'shiftGradeCount' => $shiftGradeCount, 'stbracketCount' => $stbracketCount, 'shiftBracketCount' => $shiftBracketCount]);
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('studentdropouts', function (Blueprint $table) {
//
$table->integer('lastprogramid')->nullable();
});
$studentdropouts = Studentdropout::all();
foreach ($studentdropouts as $dropout) {
$lastsem = DB::table('studentterms')->select(DB::raw('MAX(aysem) AS lastsem'))->where('studentid', $dropout->studentid)->lists(DB::raw('lastsem'));
$lastsemdata = Studentterm::where('studentid', $dropout->studentid)->where('aysem', $lastsem[0])->first();
$dropout->lastprogramid = $lastsemdata->programid;
$dropout->save();
}
}
public function getYearlySemDifference($year)
{
$programs = $this->programs()->whereNotIn('programid', array(62, 66, 38, 117))->where('degreelevel', 'U')->lists('programid');
$studentsSem1 = Studentterm::where('aysem', strval($year) . '1')->whereIn('programid', $programs)->count();
$studentsSem2 = Studentterm::where('aysem', strval($year) . '2')->whereIn('programid', $programs)->count();
//if College of Medicine
if ($this->unitid === 7) {
$domProgram = Program::where('programid', 38)->first();
$domSem1 = $domProgram->studentterms()->whereIn('yearlevel', array(3, 4))->where('aysem', strval($year) . '1')->count();
$domSem2 = $domProgram->studentterms()->whereIn('yearlevel', array(3, 4))->where('aysem', strval($year) . '2')->count();
$studentsSem1 = $studentsSem1 + $domSem1;
$studentsSem2 = $studentsSem2 + $domSem2;
}
$semDifference = $studentsSem2 - $studentsSem1;
return $semDifference;
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('studentshifts', function (Blueprint $table) {
//
$table->increments('id');
$table->integer('studentid');
$table->integer('program1id');
$table->decimal('program1years');
$table->integer('program2id');
$table->decimal('program2years');
$table->timestamps();
});
//create array of programids of graduate programs
$mastersArray = DB::table('programs')->where('degreelevel', '!=', 'U')->where('programid', '!=', 38)->lists('programid');
/*get list of students who shifted out
1. Join studentterms to itself
2. Get studentids of students who have studentterms with different programids
3. Get their first and second programs
4. Make sure the programs are not graduate programs
5. Shifting should be done directly after the program1 aysem (ex. program1 aysem = 20101 and program2 = 20102 or program1 aysem = 20102 and program2 = 20111)
*/
$shiftees = DB::table('studentterms AS e1')->join('studentterms AS e2', DB::raw('e1.studentid'), '=', DB::raw('e2.studentid'))->select(DB::raw('e1.studentid AS studentid'), DB::raw('e1.programid AS program1'), DB::raw('e2.programid AS program2'))->where(DB::raw('e1.programid'), '!=', DB::raw('e2.programid'))->where(DB::raw('e1.studentid'), '>', 200000000)->whereNotIn(DB::raw('e1.programid'), $mastersArray)->whereNotIn(DB::raw('e2.programid'), $mastersArray)->whereIn(DB::raw('e2.aysem::Int4 - e1.aysem::Int4'), array(1, 9))->whereRaw('CAST(e1.aysem AS TEXT) NOT LIKE \'%3\'')->whereRaw('CAST(e2.aysem AS TEXT) NOT LIKE \'%3\'')->groupBy(DB::raw('e1.studentid'), DB::raw('e1.programid'), DB::raw('e2.programid'))->get();
/*Populate table:
1. For each student, take note of their programs
2. Get the number of sems they spent in each program, exclude summer (aysem ending in 3)
3. Divide number of sems by 2 to get the number of years
*/
foreach ($shiftees as $shiftee) {
$studentid = $shiftee->studentid;
$program1id = $shiftee->program1;
$program2id = $shiftee->program2;
$program1sems = Studentterm::where('studentid', $studentid)->where('programid', $program1id)->whereRaw('CAST(aysem AS TEXT) NOT LIKE \'%3\'')->count();
$program1years = $program1sems / 2;
$program2sems = Studentterm::where('studentid', $studentid)->where('programid', $program2id)->whereRaw('CAST(aysem AS TEXT) NOT LIKE \'%3\'')->count();
$program2years = $program2sems / 2;
$studentshift = new Studentshift();
$studentshift->studentid = $studentid;
$studentshift->program1id = $program1id;
$studentshift->program1years = $program1years;
$studentshift->program2id = $program2id;
$studentshift->program2years = $program2years;
$studentshift->save();
}
}
public function getShiftSTBracketCount()
{
$bracketArray = [];
//To get batches of program whithin 2000-2009
$progYears = Studentterm::where('programid', $this->programid)->groupBy('year')->orderBy('year', 'asc')->lists('year');
if ($this->revisionyear > 2009) {
$max = 2012;
} 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('lastprogramid', $this->programid)->lists('studentid');
if ($this->programid == 28) {
$programShiftees = Studentshift::select('studentid')->where('studentid', '>', $min)->where('studentid', '<', $max)->where('program1id', $this->programid)->where('program2id', '!=', 38)->where('program1years', '<=', $this->numyears)->whereNotIn('studentid', $dropouts)->groupBy('studentid')->lists('studentid');
} else {
$programShiftees = Studentshift::select('studentid')->where('studentid', '>', $min)->where('studentid', '<', $max)->where('program1id', $this->programid)->where('program1years', '<', $this->numyears)->whereNotIn('studentid', $dropouts)->groupBy('studentid')->lists('studentid');
}
$programShiftees = array_unique($programShiftees);
$shifteeCount = count($programShiftees);
$bracketA = 0;
$bracketB = 0;
$bracketC = 0;
$bracketD = 0;
$bracketE1 = 0;
$bracketE2 = 0;
$unstated = 0;
foreach ($programShiftees as $shiftee) {
$results = Studentterm::select('stfapbracket')->where('studentid', $shiftee)->where('programid', $this->programid)->groupBy('stfapbracket')->lists('stfapbracket');
$results = array_unique($results);
foreach ($results as $result) {
switch ($result) {
case strpos($result, 'A') !== false || strpos($result, '9') !== false:
$bracketA++;
break;
case strpos($result, 'B') !== false:
$bracketB++;
break;
case strpos($result, 'C') !== false:
$bracketC++;
break;
case strpos($result, 'D') !== false:
$bracketD++;
break;
case strpos($result, 'E1') !== false:
$bracketE1++;
break;
case strpos($result, 'E2') !== false || strpos($result, '1') !== false:
$bracketE2++;
break;
default:
}
}
}
if ($shifteeCount != 0) {
$tagged = $bracketA + $bracketB + $bracketC + $bracketD + $bracketE1 + $bracketE2;
$bracketArray["A"] = round($bracketA / $tagged * 100, 2);
$bracketArray["B"] = round($bracketB / $tagged * 100, 2);
$bracketArray["C"] = round($bracketC / $tagged * 100, 2);
$bracketArray["D"] = round($bracketD / $tagged * 100, 2);
$bracketArray["E1"] = round($bracketE1 / $tagged * 100, 2);
$bracketArray["E2"] = round($bracketE2 / $tagged * 100, 2);
} else {
$bracketArray["No Shiftees"] = 0;
}
return $bracketArray;
}
