public function countStudentSem($studentid, $programid)
{
$aysems = Studentterm::select('aysem')->where('studentid', $studentid)->where('programid', $programid)->get();
$sems = 0;
foreach ($aysems as $aysem) {
$sem = substr($aysem->aysem, -1);
if ($sem === '1' || $sem === '2') {
$sems = $sems + 1;
}
}
return $sems;
}
/**
* 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 getSpecificBatchSTBracketCount($batch)
{
$batch = $batch * 100000;
$batchEnd = $batch + 100000;
$bracketArray = [];
$programids = Program::whereNotIn('programid', array(62, 66, 38, 22))->where('degreelevel', 'U')->lists('programid');
$collegeDropouts = Studentdropout::whereIn('lastprogramid', $programids)->where('studentid', '>', $batch)->where('studentid', '<', $batchEnd)->get();
$bracketA = 0;
$bracketB = 0;
$bracketC = 0;
$bracketD = 0;
$bracketE1 = 0;
$bracketE2 = 0;
$unstated = 0;
foreach ($collegeDropouts as $dropout) {
$results = Studentterm::select('stfapbracket')->where('studentid', $dropout->studentid)->whereIn('programid', $programids)->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:
$unstated++;
}
}
}
$bracketArray["A"] = $bracketA;
$bracketArray["B"] = $bracketB;
$bracketArray["C"] = $bracketC;
$bracketArray["D"] = $bracketD;
$bracketArray["E1"] = $bracketE1;
$bracketArray["E2"] = $bracketE2;
$bracketArray["Unstated"] = $unstated;
return $bracketArray;
}
public function getShiftSpecificBatchSTBracketCount($batch)
{
$min = $batch * 100000;
$max = $batch + 100000;
$bracketArray = [];
$programids = $this->programs()->whereNotIn('programid', array(62, 66, 38, 117))->where('degreelevel', 'U')->lists('programid');
$dropouts = DB::table('studentdropouts')->whereIn('lastprogramid', $programids)->lists('studentid');
$programShiftees = DB::table('studentshifts')->join('programs', 'program1id', '=', 'programid')->select('studentid')->where('studentid', '>', $min)->where('studentid', '<', $max)->whereIn('program1id', $programids)->whereNotIn('program2id', $programids)->where('program2id', '!=', 38)->whereRaw('program1years < CAST(numyears AS numeric)')->whereNotIn('studentid', $dropouts)->groupBy('studentid')->get();
$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)->whereIn('programid', $programids)->groupBy('stfapbracket')->lists('stfapbracket');
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:
$unstated++;
}
}
}
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;
}
public static function getOneStbracketCollege($studentId, $collegeid)
{
$programids = Studentterm::getCollPrograms($collegeid);
return Studentterm::select('stfapbracket')->where('studentid', $studentId)->whereIn('programid', $programids)->get();
}
public static function getOneAveUnitsCollege($studentid, $collegeid)
{
$programids = Assessment::getCollPrograms($collegeid);
$semesters = Assessment::select('total_units', 'aysem')->where('studentid', $studentid)->get();
$totalUnits = 0;
$totalSemesters = 0;
$aysemGet = Studentterm::select('aysem')->where('studentid', $studentid)->whereIn('programid', $programids)->get();
$aysems = [];
foreach ($aysemGet as $aysem) {
array_push($aysems, $aysem->aysem);
}
foreach ($semesters as $semester) {
$semesterAysem = $semester->aysem;
if (in_array($semesterAysem, $aysems) && (substr($semesterAysem, -1) === '1' || substr($semesterAysem, -1) === '2')) {
$units = $semester->total_units;
if ($units > 0 && $units <= 21) {
$totalUnits = $totalUnits + $units;
$totalSemesters = $totalSemesters + 1;
}
}
}
if ($totalSemesters > 0) {
return $totalUnits / $totalSemesters;
} else {
return -1;
}
}