/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('studentdropouts', function (Blueprint $table) {
//
/*Add data to include Applied Physics (programid = 117) and batch 2011 and 2012
1. Get all students from 2011 and 2012 who is taking applied physics
2. Remove students who shifted out
3. If batch 2011 and has no studentterm in 2012 and 2013 then add student to studentdropouts table
4. If batch 2012 and has no studentterm in 2013 then add student to studentdropouts table
*/
$program = Program::where('programid', 117)->first();
$shiftees = Studentshift::where('program1id', $program->programid)->lists('studentid');
$min = 201100000;
$max = 201300000;
$studentids = Studentterm::select('studentid')->where('studentid', '>', $min)->where('studentid', '<', $max)->whereNotIn('studentid', $shiftees)->where('programid', $program->programid)->groupBy('studentid')->lists('studentid');
foreach ($studentids as $studentid) {
$student = Student::where('studentid', $studentid)->first();
if ($student->studentid < 201200000) {
//batch 2011
$stayed = $student->studentterms()->where('year', 2012)->orWhere('year', 2013)->count();
} else {
//batch 2012
$stayed = $student->studentterms()->where('year', 2013)->count();
}
if ($stayed === 0) {
//insert to studentdropout
$semcount = $student->studentterms()->where('programid', $program->programid)->whereRaw('CAST(aysem AS TEXT) NOT LIKE \'%3\'')->count();
$newDropout = new Studentdropout();
$newDropout->studentid = $student->studentid;
$newDropout->programid = $program->programid;
$newDropout->lastprogramid = $program->programid;
$newDropout->collegeid = $program->department->college->unitid;
$newDropout->semesters = $semcount;
$newDropout->save();
}
}
});
}
public function getAveYearsOfStay()
{
//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 = 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;
//Get list of dropouts. Remove them from computation.
$dropouts = DB::table('studentdropouts')->lists('studentid');
//Get list of students from shift table whose progam 1 is this program and whose program1years are less than numyears of program. place their studentids in an array. Exclude them from computation.
if ($this->programid == 28) {
$shiftees = DB::table('studentshifts')->where('studentid', '>', $min)->where('studentid', '<', $max)->where('program1id', '=', $this->programid)->where('program2id', '!=', 38)->where('program1years', '<=', $this->numyears)->whereNotIn('studentid', $dropouts)->lists('studentid');
$domShiftees = DB::table('studentshifts')->where('studentid', '>', $min)->where('studentid', '<', $max)->whereNotIn('studentid', $dropouts)->where('program1id', '=', $this->programid)->where('program2id', '=', 38)->where(DB::raw('program1years + program2years'), '<', 4)->lists('studentid');
foreach ($domShiftees as $domShiftee) {
array_push($shiftees, $domShiftee);
}
} else {
$shiftees = DB::table('studentshifts')->where('studentid', '>', $min)->where('studentid', '<', $max)->where('program1id', '=', $this->programid)->where('program1years', '<', $this->numyears)->lists('studentid');
}
if ($this->programid == 28) {
$studentids = DB::table('studentterms')->select('studentid')->where('programid', $this->programid)->where('studentid', '>', $min)->where('studentid', '<', $max)->whereNotIn('studentid', $dropouts)->whereNotIn('studentid', $shiftees)->groupBy('studentid')->lists('studentid');
$totalYears = 0;
foreach ($studentids as $studentid) {
//check if student shifted to Doctor of Medicine
//if he shifted to DoM and if his number of years in (intarmed + DoM) is greater than 4 then let his stay be 4
//else get his real years of stay
$dom = Studentshift::where('studentid', $studentid)->where('program1id', 28)->where('program2id', 38)->first();
if ($dom != NULL) {
//if record exists
if ($dom->program2years >= 2) {
$domYears = 2;
}
if ($dom->program1years == 1) {
//Some students shift to DoM after 1 year of Intarmed
if ($dom->program2years >= 3) {
$domYears = 3;
}
}
$numYears = $dom->program1years + $domYears;
} else {
$numYears = DB::table('studentterms')->where('studentid', $studentid)->where('programid', $this->programid)->whereRaw('CAST(aysem AS TEXT) NOT LIKE \'%3\'')->count() / 2;
}
$totalYears = $totalYears + $numYears;
}
$aveYearsOfStay = round($totalYears / count($studentids), 2);
} else {
$numberOfYearsPerStudent = DB::table('studentterms')->select(DB::raw('COUNT(*)/2 as numYears'))->where('programid', $this->programid)->where('studentid', '>', $min)->where('studentid', '<', $max)->whereNotIn('studentid', $dropouts)->whereNotIn('studentid', $shiftees)->whereRaw('CAST(aysem AS TEXT) NOT LIKE \'%3\'')->groupBy('studentid')->get();
$numberOfStudents = count($numberOfYearsPerStudent);
$totalYears = 0;
foreach ($numberOfYearsPerStudent as $key => $val) {
$totalYears = $totalYears + $val->numyears;
}
$aveYearsOfStay = round($totalYears / $numberOfStudents, 2);
}
return $aveYearsOfStay;
}
