/**
  * 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('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();
     }
 }
 /**
  * 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();
             }
         }
     });
 }
Пример #4
0
 public static function getBatchShiftsCollege($batch, $collegeid)
 {
     $batchEnd = $batch + 100000;
     $programids = Studentshift::getCollPrograms($collegeid);
     return Studentshift::select('studentid')->where('studentid', '>=', $batch)->where('studentid', '<', $batchEnd)->whereIn('program1id', $programids)->get();
 }
Пример #5
0
 public function getShiftSpecificBatchSTBracketCount($batch)
 {
     $min = $batch * 100000;
     $max = $batch + 100000;
     $bracketArray = [];
     $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');
         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;
 }