/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('studentdropouts', function (Blueprint $table) {
$table->increments('id');
$table->integer('studentid');
$table->integer('programid');
$table->integer('collegeid');
$table->integer('semesters');
$table->timestamps();
});
$students = DB::table('studentterms')->join('programs', 'studentterms.programid', '=', 'programs.programid')->select('studentterms.studentid as studentid')->where('studentterms.studentid', '>', 200000000)->where('studentterms.studentid', '<', 201000000)->where('programs.degreelevel', '=', 'U')->groupBy('studentterms.studentid')->orderBy('studentterms.studentid')->get();
foreach ($students as $student) {
$studentId = $student->studentid;
//It won't matter if student shifted or whatever (more years in student's stay), the point here is he will be counted as a dropout if his stay is less than his required stay (assuming there are no early graduates)
$programId = DB::table('studentterms')->select('studentid', 'programid', 'unitid', 'aysem')->where('studentid', '=', $studentId)->first()->programid;
if ($programId === 0 || $programId === 1 || $programId === 49 || $programId === 54 || $programId === 100 || $programId === 114) {
$programSems = 8;
} else {
$programSems = DB::table('programs')->where('programid', '=', $programId)->first()->numyears * 2;
}
//for Intarmed, etc
if ($programSems > 10 || $programSems < 8) {
$programSems = 8;
}
$studentSems = 0;
$studentOnes = DB::table('studentterms')->select('studentid', 'programid', 'unitid', 'aysem')->where('studentid', '=', $studentId)->get();
foreach ($studentOnes as $studentOne) {
$studentSemstr = substr($studentOne->aysem, -1);
if ($studentSemstr === '1') {
$studentSems++;
}
if ($studentSemstr === '2') {
$studentSems++;
}
}
if ($studentSems < $programSems) {
$newDropout = new Studentdropout();
$newDropout->studentid = $studentId;
$newDropout->programid = $programId;
if ($programId === 0 || $programId === 1 || $programId === 49 || $programId === 54 || $programId === 100 || $programId === 114) {
$collegeId = 0;
} else {
$collegeId = DB::table('programs')->select('unitid')->where('programid', '=', $programId)->first()->unitid;
}
$newDropout->collegeid = $collegeId;
$newDropout->semesters = $studentSems;
$newDropout->save();
}
}
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('correlations', function (Blueprint $table) {
//
});
$entries = Correlation::where('unittype', 'program')->get();
foreach ($entries as $entry) {
$batch = $entry->batch;
$programid = $entry->programid;
$dropoutsRaw = Studentdropout::getBatchDropoutsProgram($batch * 100000, $programid);
$shiftsRaw = Studentshift::getBatchShiftsProgram($batch * 100000, $programid);
$delayedRaw = Studentdelayed::getBatchDelayedProgram($batch * 100000, $programid);
$studentids = [];
foreach ($dropoutsRaw as $doRaw) {
array_push($studentids, $doRaw->studentid);
}
foreach ($shiftsRaw as $sRaw) {
array_push($studentids, $sRaw->studentid);
}
foreach ($delayedRaw as $dRaw) {
array_push($studentids, $dRaw->studentid);
}
$studentids = array_values(array_unique($studentids));
$students = Studentterm::getBatchStudentsCountProgram($batch * 100000, $programid);
if ($students > 0) {
$entry->dropouts = count($studentids) / $students;
} else {
$entry->dropouts = 0;
}
$entry->save();
}
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('studentdelayed', function (Blueprint $table) {
$table->increments('id');
$table->integer('studentid');
$table->integer('programid');
$table->integer('collegeid');
$table->integer('semesters');
$table->timestamps();
});
$students = Studentdropout::select('studentid', 'programid', 'collegeid', 'semesters')->where('semesters', '>', 8)->get();
foreach ($students as $student) {
$programid = $student->programid;
$semesters = $student->semesters;
$programYears = Program::select('numyears')->where('programid', $programid)->first()->numyears;
if ($semesters > $programYears * 2) {
$newEntry = new Studentdelayed();
$newEntry->studentid = $student->studentid;
$newEntry->programid = $programid;
$newEntry->collegeid = $student->collegeid;
$newEntry->semesters = $semesters;
$newEntry->save();
}
}
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('studentdropouts', function (Blueprint $table) {
//
});
$students = Studentterm::getAllStudents();
foreach ($students as $student) {
$studentid = $student->studentid;
$programid = $this->getFinProgram($studentid);
$programYears = Program::select('numyears')->where('programid', $programid)->first()->numyears;
$studentsems = $this->countStudentSem($studentid, $programid);
if ($studentsems > $programYears * 2) {
$newEntry = new Studentdropout();
$newEntry->studentid = $studentid;
$newEntry->programid = $programid;
$newEntry->collegeid = Program::select('unitid')->where('programid', $programid)->first()->unitid;
$newEntry->semesters = $studentsems;
$newEntry->save();
}
}
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('studentdropouts', function (Blueprint $table) {
//
});
$students = Studentdropout::select('id', 'programid', 'semesters')->where('semesters', '>', 8)->get();
foreach ($students as $student) {
$programYears = Program::select('numyears')->where('programid', $student->programid)->first()->numyears;
if ($student->semesters > $programYears * 2) {
Studentdropout::find($student->id)->delete();
}
}
}
public function seedProgram($entry)
{
$batch = $entry->batch;
$programid = $entry->programid;
$dropouts = Studentdropout::getBatchDropoutsCountProgram($batch * 100000, $programid);
$students = Studentterm::getBatchStudentsCountProgram($batch * 100000, $programid);
if ($entry->dropouts > 0) {
$entry->dropouts = $dropouts / $students;
} else {
$entry->dropouts = 0;
}
$entry->save();
}
/**
* 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();
}
}
});
}
/**
* 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();
}
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('correlations', function (Blueprint $table) {
//
});
$entries = Correlation::where('unittype', 'campus')->get();
foreach ($entries as $entry) {
$batch = $entry->batch;
$dropouts = Studentdropout::getBatchDropoutsCount($batch * 100000);
$delayed = Studentdelayed::getBatchDelayedCount($batch * 100000);
$students = Studentterm::getBatchStudentsCount($batch * 100000);
if ($students > 0) {
$entry->dropouts = ($dropouts + $delayed) / $students;
} else {
$entry->dropouts = 0;
}
$entry->save();
}
}
public function getSpecificBatchRegionCount($batch)
{
$batch = $batch * 100000;
$batchEnd = $batch + 100000;
$regionArray = [];
$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();
$luzon = 0;
$visayas = 0;
$mindanao = 0;
$unstated = 0;
$luzonRegions = ['NCR', 'I', 'CAR', 'II', 'III', 'IV', 'V'];
$visayasRegions = ['VI', 'VII', 'VIII'];
$mindanaoRegions = ['IX', 'X', 'XI', 'XII', 'XIII', 'ARMM'];
foreach ($collegeDropouts as $dropout) {
$regionHolder = Studentaddress::getOneRegion($dropout->studentid);
if (count($regionHolder) > 0) {
$regionCode = preg_replace('/\\s+/', '', $regionHolder->regioncode);
if (in_array($regionCode, $luzonRegions)) {
$luzon++;
} elseif (in_array($regionCode, $visayasRegions)) {
$visayas++;
} elseif (in_array($regionCode, $mindanaoRegions)) {
$mindanao++;
}
} else {
$unstated++;
}
}
$regionArray['Luzon'] = $luzon;
$regionArray['Visayas'] = $visayas;
$regionArray['Mindanao'] = $mindanao;
$regionArray['Unstated'] = $unstated;
return $regionArray;
}
public static function getBatchDropoutsCollege($batch, $collegeid)
{
$batchEnd = $batch + 100000;
$programids = Studentdropout::getCollPrograms($collegeid);
return Studentdropout::select('studentid')->where('studentid', '>=', $batch)->where('studentid', '<', $batchEnd)->whereIn('programid', $programids)->get();
}
public function getSpecificBatchRegionCount($batch)
{
$batch = $batch * 100000;
$batchEnd = $batch + 100000;
$regionArray = [];
$programDropouts = Studentdropout::where('lastprogramid', $this->programid)->where('studentid', '>', $batch)->where('studentid', '<', $batchEnd)->get();
$luzon = 0;
$visayas = 0;
$mindanao = 0;
$unstated = 0;
$luzonRegions = ['NCR', 'I', 'CAR', 'II', 'III', 'IV', 'V'];
$visayasRegions = ['VI', 'VII', 'VIII'];
$mindanaoRegions = ['IX', 'X', 'XI', 'XII', 'XIII', 'ARMM'];
foreach ($programDropouts as $dropout) {
$regionHolder = Studentaddress::getOneRegion($dropout->studentid);
if (count($regionHolder) > 0) {
if (in_array($regionHolder->regioncode, $luzonRegions)) {
$luzon++;
} elseif (in_array($regionHolder->regioncode, $visayasRegions)) {
$visayas++;
} elseif (in_array($regionHolder->regioncode, $mindanaoRegions)) {
$mindanao++;
}
} else {
$unstated++;
}
}
$regionArray['Luzon'] = $luzon;
$regionArray['Visayas'] = $visayas;
$regionArray['Mindanao'] = $mindanao;
$regionArray['Unstated'] = $unstated;
return $regionArray;
}
