OLAS/app/controllers/SchoolCoordinatorsControlle...

<?php

class SchoolCoordinatorsController extends \BaseController {
    
    private function participatingPrograms($school)
    {
        return DB::table('VIEW_participating_programs')
            ->where('school_id', $school->id)
            ->whereIn('semester_id', Session::get('semesters_ids'))
            ->lists('id');
    }

    public function overview()
    {
        DB::disableQueryLog();
        Log::debug('user'.Auth::user()->id);

        //TODO Find a better way to prevent non-scoords from reaching the page
        switch(Auth::user()->role)
        {
        case 1:
            return Redirect::to('administrator');
            break;
        case 3:
            return Redirect::to('program-coordinator');
            break;
        }

        $schools = School::orderBy('name', 'asc')->get();
        $school = Auth::user()->school;
        $title = $school->name.' '.'Overview';
        $id = $school->id;

        $outcomes = Outcome::orderBy('name', 'asc')->get();
        $outcomeCount = Outcome::all()->count();

            /**
             * List of grouped courses (grouped sections)
             */

            $program_ids= $school->programs->lists('id');

            $undergrad_programs = DB::table('programs')
            ->select('id', 'name', 'school_id', 'is_graduate')
            ->where('is_graduate', '=', 0)
            ->where('school_id', '=', $id)
            ->orderBy('name', 'ASC')
            ->get();

            $grad_programs = DB::table('programs')
            ->select('id', 'name', 'school_id', 'is_graduate')
            ->where('is_graduate', '=', 1)
            ->where('school_id', '=', $id)
            ->orderBy('name', 'ASC')
            ->get();
    

            $grad_grouped_courses = Course::
                select(DB::raw('courses.name, courses.code, courses.number, max(courses.outcomes_attempted) as outcomes_attempted, courses.semester_id, courses.program_id'))
                ->with('semester')
                ->with('program')
                ->whereIn('courses.program_id', $program_ids)
                ->whereIn('courses.semester_id', Session::get('semesters_ids'))
                ->leftJoin('programs', 'courses.program_id', '=', 'programs.id')
                ->where('programs.is_graduate', '=', 1)
                ->groupBy(array('courses.code', 'courses.number', 'courses.semester_id'))
                ->orderBy('courses.code')
                ->orderBy('courses.number')
                ->orderBy('courses.semester_id')
                ->get();

            $undergrad_grouped_courses = Course::
                select(DB::raw('courses.name, courses.code, courses.number, max(courses.outcomes_attempted) as outcomes_attempted, courses.semester_id, courses.program_id'))
                ->with('semester')
                ->with('program')
                ->whereIn('courses.program_id', $program_ids)
                ->whereIn('courses.semester_id', Session::get('semesters_ids'))
                ->leftJoin('programs', 'courses.program_id', '=', 'programs.id')
                ->where('programs.is_graduate', '=', 0)
                ->groupBy(array('courses.code', 'courses.number', 'courses.semester_id'))
                ->orderBy('courses.code')
                ->orderBy('courses.number')
                ->orderBy('courses.semester_id')
                ->get();

            // Fetch programs with participation
            $participating_programs = $this->participatingPrograms($school);


            /**
             * Calculate how many sections are doing assessment
             */

            $undergrad_outcomes_achieved = array_fill(1, $outcomeCount, 0);
            $undergrad_outcomes_attempted = array_fill(1, $outcomeCount, 0);
            $undergrad_assessed_sections_count = 0;
            $undergrad_school_sections_count = 0;

            $grad_outcomes_achieved = array_fill(1, $outcomeCount, 0);
            $grad_outcomes_attempted = array_fill(1, $outcomeCount, 0);
            $grad_assessed_sections_count = 0;
            $grad_school_sections_count = 0;

            foreach($school->programs as $program)
            {
                foreach ($program->courses as $course)
                {
                    if(!$course->program->is_graduate)
                    {
                        if($course->outcomes_achieved != NULL)
                        {
                            $course_outcomes_achieved =json_decode($course->outcomes_achieved, true);
                            $course_outcomes_attempted =json_decode($course->outcomes_attempted, true);
                            for($i=1; $i<=count($undergrad_outcomes_attempted); $i++)
                            {
                                $undergrad_outcomes_achieved[$i]+=$course_outcomes_achieved[$i];
                                $undergrad_outcomes_attempted[$i]+=$course_outcomes_attempted[$i];
                            }
                            $undergrad_assessed_sections_count+=1;
                        }
                        $undergrad_school_sections_count+=1;
                    }
                    else
                    {
                        if($course->outcomes_achieved != NULL)
                        {
                            $course_outcomes_achieved =json_decode($course->outcomes_achieved, true);
                            $course_outcomes_attempted =json_decode($course->outcomes_attempted, true);
                            for($i=1; $i<=count($grad_outcomes_attempted); $i++)
                            {
                                $grad_outcomes_achieved[$i]+=$course_outcomes_achieved[$i];
                                $grad_outcomes_attempted[$i]+=$course_outcomes_attempted[$i];
                            }
                            $grad_assessed_sections_count+=1;
                        }
                        $grad_school_sections_count+=1;
                    }
                }    
            }

            /**
             * Calculate how many programs achieved and attempted each outcome in this school
             */

            // Number of programs that achieved a particular learning outcome
            $achievedUndergradProgramsPerOutcome = array_fill(1, $outcomeCount, 0);

            // Number of programs that attempted a particular learning outcome
            $attemptedUndergradProgramsPerOutcome = array_fill(1, $outcomeCount, 0);

            // Fetch programs with participation for the school
            $participating_undergrad_programs = DB::table('VIEW_participating_programs')
                ->whereIn('semester_id', Session::get('semesters_ids'))
                ->where('is_graduate', 0)
                ->where('school_id', $school->id)
                ->groupBy('id')
                ->get();

            $output = array();


            // For each outcome 
            foreach ($outcomes as $outcome)
            {
                // For each program with courses that do assessment
                $programs_with_courses = Program::with(array('courses' => function($query){
                    $query->whereNotNull('outcomes_attempted');
                    $query->whereIn('semester_id', Session::get('semesters_ids'));
                }))->where('is_graduate', 0)->where('school_id', $school->id)->orderBy('name', 'asc')->get();

                foreach ($programs_with_courses as $program)
                {
                    // To acummulate all criteria for one program
                    $achieved_outcomes_per_undergrad_program = array_fill(1, $outcomeCount, 0);
                    $attempted_outcomes_per_undergrad_program = array_fill(1, $outcomeCount, 0);

                    //Flag for counting programs
                    $flag =false;

                    // For each course in the program
                    foreach ($program->courses as $course)
                    {
                        // If the outcome in question is being evaluated
                        $course_outcomes_attempted2 = json_decode($course->outcomes_attempted, true);
                        $course_outcomes_achieved2 = json_decode($course->outcomes_achieved, true);
                        if(array_key_exists($outcome->id, $course_outcomes_attempted2 )
                            && $course_outcomes_attempted2[$outcome->id]>0)
                        {
                            $achieved_outcomes_per_undergrad_program[$outcome->id]+=$course_outcomes_achieved2[$outcome->id];
                            $attempted_outcomes_per_undergrad_program[$outcome->id]+=$course_outcomes_attempted2[$outcome->id];

                            // Add one to the programs assessing, if it wasn't added before
                            if(!$flag)
                            {
                                $attemptedUndergradProgramsPerOutcome[$outcome->id]+=1;
                                $flag= true;
                            }
                        }

                        // $output[] = 'ACHIEVED: '.$program->name.'-'.json_encode($achieved_outcomes_per_undergrad_program);
                        // $output[] = 'ATTEMPTED: '.$program->name.'-'.json_encode($attempted_outcomes_per_program);

                    }
                    //If the accumulated achieved criteria for a specific outcome in a program divided by the accumulated attempted criteria for a specific outcome in a program is greated than the expected outcome
                    if($attempted_outcomes_per_undergrad_program[$outcome->id]!=0 && (float)$achieved_outcomes_per_undergrad_program[$outcome->id]/$attempted_outcomes_per_undergrad_program[$outcome->id]*100 >= $outcome->expected_outcome)
                    {
                        $achievedUndergradProgramsPerOutcome[$outcome->id]+=1;
                        // $output[]= 'END OF PROGRAM: '.$program->name.'-'.json_encode($achievedProgramsPerOutcome);
                    }
                }
            }

            /**
             * Calculate how many programs achieved and attempted each outcome in this school
             */

            // Number of programs that achieved a particular learning outcome
            $achievedGradProgramsPerOutcome = array_fill(1, $outcomeCount, 0);

            // Number of programs that attempted a particular learning outcome
            $attemptedGradProgramsPerOutcome = array_fill(1, $outcomeCount, 0);

            // Fetch programs with participation for the school
            $participating_grad_programs = DB::table('VIEW_participating_programs')
                ->whereIn('semester_id', Session::get('semesters_ids'))
                ->where('is_graduate', 1)
                ->where('school_id', $school->id)
                ->groupBy('id')
                ->get();

            $output = array();


            // For each outcome 
            foreach ($outcomes as $outcome)
            {
                // For each program with courses that do assessment
                $programs_with_courses = Program::with(array('courses' => function($query){
                    $query->whereNotNull('outcomes_attempted');
                    $query->whereIn('semester_id', Session::get('semesters_ids'));
                }))->where('is_graduate', 1)->where('school_id', $school->id)->orderBy('name', 'asc')->get();

                foreach ($programs_with_courses as $program)
                {
                    // To acummulate all criteria for one program
                    $achieved_outcomes_per_grad_program = array_fill(1, $outcomeCount, 0);
                    $attempted_outcomes_per_grad_program = array_fill(1, $outcomeCount, 0);

                    //Flag for counting programs
                    $flag =false;

                    // For each course in the program
                    foreach ($program->courses as $course)
                    {
                        // If the outcome in question is being evaluated
                        $course_outcomes_attempted2 = json_decode($course->outcomes_attempted, true);
                        $course_outcomes_achieved2 = json_decode($course->outcomes_achieved, true);
                        if(array_key_exists($outcome->id, $course_outcomes_attempted2 )
                            && $course_outcomes_attempted2[$outcome->id]>0)
                        {
                            $achieved_outcomes_per_grad_program[$outcome->id]+=$course_outcomes_achieved2[$outcome->id];
                            $attempted_outcomes_per_grad_program[$outcome->id]+=$course_outcomes_attempted2[$outcome->id];

                            // Add one to the programs assessing, if it wasn't added before
                            if(!$flag)
                            {
                                $attemptedGradProgramsPerOutcome[$outcome->id]+=1;
                                $flag= true;
                            }
                        }

                        // $output[] = 'ACHIEVED: '.$program->name.'-'.json_encode($achieved_outcomes_per_grad_program);
                        // $output[] = 'ATTEMPTED: '.$program->name.'-'.json_encode($attempted_outcomes_per_program);

                    }
                    //If the accumulated achieved criteria for a specific outcome in a program divided by the accumulated attempted criteria for a specific outcome in a program is greated than the expected outcome
                    if($attempted_outcomes_per_grad_program[$outcome->id]!=0 && (float)$achieved_outcomes_per_grad_program[$outcome->id]/$attempted_outcomes_per_grad_program[$outcome->id]*100 >= $outcome->expected_outcome)
                    {
                        $achievedGradProgramsPerOutcome[$outcome->id]+=1;
                        // $output[]= 'END OF PROGRAM: '.$program->name.'-'.json_encode($achievedProgramsPerOutcome);
                    }
                }
            }
            return View::make('local.managers.shared.school', compact('title', 'outcomes','undergrad_programs', 'grad_programs', 'undergrad_outcomes_attempted', 'grad_outcomes_attempted', 'undergrad_outcomes_achieved', 'grad_outcomes_achieved', 'schools', 'school', 'undergrad_assessed_sections_count', 'grad_assessed_sections_count', 'undergrad_school_sections_count', 'grad_school_sections_count', 'achievedUndergradProgramsPerOutcome', 'achievedGradProgramsPerOutcome', 'attemptedUndergradProgramsPerOutcome', 'attemptedGradProgramsPerOutcome', 'grad_grouped_courses', 'undergrad_grouped_courses', 'participating_programs', 'participating_undergrad_programs', 'participating_grad_programs'));
        }

}