OLAS/app/models/Course.php

<?php

class Course extends Eloquent
{

  protected $fillable = array('name', 'code', 'number', 'section', 'program_id', 'user_id', 'semester_id');
  protected $appends = array('proposed_transformative_actions');
  public function program()
  {
    return $this->belongsTo('Program');
  }

  public function students()
  {
    return $this->belongsToMany('Student')->orderBy('name', 'asc')->withTimestamps();
  }

  public function user()
  {
    return $this->belongsTo('User')->orderBy('surnames', 'asc')->orderBy('first_name', 'asc');
  }



  // TODO: Remove
  public function activities()
  {
    return $this->hasMany('Activity')->orderBy('date', 'asc');
  }

  public function activity()
  {
    return $this->belongsTo('Activity');
  }

  //paired_objective, como el typ_semester_course_id esta atado a un solo typ_semester_objective_id pues te devuelve el primero
  public function getPairedObjectiveAttribute()
  {

    return Objective::join('typ_semester_objectives', 'typ_semester_objectives.objective_id', '=', 'objectives.id')
      ->join('typ_semester_courses', 'typ_semester_courses.typ_semester_objective_id', '=', 'typ_semester_objectives.id')
      ->where('typ_semester_courses.id', $this->typ_semester_course_id)
      ->select('typ_semester_objectives.id as typ_semester_objective_id', 'objectives.*')
      ->first();
  }
  //future_transformative_actions
  public function getFutureTransformativeActionsAttribute()
  {
    if (isset($this->typ_semester_course_id)) {

      Log::info("entramos al isset");
      //Big object where  data is going to turn out like 
      /*
      $this->future_transformative_actions = all future transformative actions proposed in annual plan
      and each future_transformative_action has an attribute future_semesters where it will be an 
      array of all future semesters proposed

      */
      $results = $this->getFutureTypSemesterCourses($this->typ_semester_course_id, $this->program_id);
      if (isset($results->future_transformative_actions))
        $results = $results->future_transformative_actions;
      $future_transformative_actions_ids = [];
      foreach ($results as $row) {
        //Log::info($this->typ_semester_course_id);
        //Log::info('row');
        //Log::info($row);

        if (isset($row->trans_id)) {
          if (!isset($future_transformative_actions_ids[$row->trans_id])) {

            $future_transformative_actions_ids[$row->trans_id] = [
              'future_semesters' => []
            ];
          }
          Log::info('arreglo');
          Log::info($future_transformative_actions_ids);
          $future_transformative_actions_ids[$row->trans_id]['future_semesters'][] = $row->semester_id;
        } else continue;
      }

      $trans_actions = [];
      Log::info($future_transformative_actions_ids);
      foreach ($future_transformative_actions_ids as $trans_id => $future_semesters) {
        $ta = TransformativeAction::where('id', $trans_id)->first();
        //$ta->setAttribute('future_semesters', []);

        $temp_future_semesters = [];
        foreach ($future_semesters['future_semesters'] as $index => $semester_id) {
          //$ta->
          $temp_future_semesters[$index] = Semester::where('id', $semester_id)->first();
        }
        $ta->setAttribute('future_semesters', $temp_future_semesters);
        $trans_actions[] = $ta;
      }
      return $trans_actions;
    }
  }
  //proposed_transformative_actions
  public function getProposedTransformativeActionsAttribute()
  {
    if (isset($this->typ_semester_course_id)) {
      //return 
      Log::info(array($this));
      return TransformativeAction::join('annual_plan_transformative', 'transformative_actions.id', '=', 'annual_plan_transformative.trans_id')
        ->where('typ_semester_course_id', $this->typ_semester_course_id)
        ->select('transformative_actions.*', DB::raw("{$this->semester_id} as semester_id"))
        ->get();


      $transformative_actions = DB::table('annual_plan_transformative')
        ->join('transformative_actions', 'transformative_actions.id', '=', 'annual_plan_transformative.trans_id')
        ->where('typ_semester_course_id', $this->typ_semester_course_id)
        ->select('transformative_actions.*')
        ->get();
      $trans_list = [];
      foreach ($transformative_actions as $trans) {
        $ta =  TransformativeAction::where('id', $trans->id)->first();
        $ta->setAttribute("semester_id", $this->semester_id);
        $trans_list[] = $ta;
      }
      return $trans_list;
    }
    Log::info('te la viviste tm');
    return null;
  }



  // assessed_paired_criteria
  public function getAssessedPairedCriteriaAttribute()
  {
    $criteria_id = DB::table('annual_plan_objective')
      ->where('typ_semester_course_id', $this->typ_semester_course_id)
      ->lists('criteria_id');
    //Log::info($this->typ_semester_course_id);
    //Log::info("dame ese id");
    $program = Program::where('id', $this->program_id)->first();
    // Log::info($program->code);
    //Si es un programa como core, enseña todos los estudiantes
    if ($program->code == "") {
      $program_ids = DB::table('programs')
        ->where('school_id', $program->school->id)
        ->lists('programs.id');
      //Log::info("estamos");
    } else $program_ids = array($program->id);

    /*tabla es asi por curso codigo
      *
      *criterioDelPlan    EstudiantesDelProgramaQuePasaron   EstudiantesqueIntentaron
      *    
      *    criterio1                 3                             5
      *    criterio2                 2                             2
      *
      *
      */
    return Criterion::join('activity_criterion', 'criteria.id', '=', 'activity_criterion.criterion_id')
      ->join('activities', 'activity_criterion.activity_id', '=', 'activities.id')
      ->join('courses', 'courses.id', '=', 'activities.course_id')
      ->join('course_student', 'course_student.course_id', '=', 'courses.id')
      ->join('program_student_semester', 'program_student_semester.student_id', '=', 'course_student.student_id')
      ->join('rubric_activity', 'rubric_activity.activity_id', '=', 'activities.id')
      ->join('rubrics', 'rubrics.id', '=', 'rubric_activity.rubric_id')

      ->join('assessments', function ($join) {
        $join->on("assessments.student_id", '=', 'program_student_semester.student_id')
          ->on('assessments.activity_criterion_id', '=', 'activity_criterion.id');
      })
      ->whereNotNull('score')
      ->where('courses.semester_id', $this->semester_id)
      ->whereIn('courses.program_id', $program_ids)
      ->where('courses.code', $this->code)
      ->where('courses.number', $this->number)
      ->whereIn('program_student_semester.program_id', $program_ids)
      ->whereIn('activity_criterion.criterion_id', $criteria_id)
      ->whereIn('criteria.id', $criteria_id)
      ->where('program_student_semester.semester_id', $this->semester_id)
      ->select('criteria.*')
      ->addSelect(DB::raw("{$this->typ_semester_course_id} as typ_semester_course_id"))
      ->addSelect(DB::raw('count(case when assessments.score>= rubrics.expected_points then 1 else null end) as criteria_achieved'))
      ->addSelect(DB::raw('count(assessments.activity_criterion_id) as criteria_attempted'))
      ->groupBy('criteria.id')

      ->get();
  }

  //paired_criteria solo funciona para plan ahora_mismo, y esta puerquito
  public function getPairedCriteriaAttribute()
  {
    if (isset($this->typ_semester_course_id)) {

      return Criterion::join("annual_plan_objective", 'annual_plan_objective.criteria_id', '=', 'criteria.id')
        ->where('typ_semester_course_id', $this->typ_semester_course_id)
        ->select(
          'criteria.*',
          'typ_semester_course_id'
        )
        ->get();



      /*.
      $criteria_info = $this->getCriteriaPlanReport($this);
      $criteria_list = [];
      foreach ($criteria_info as $index => $criterion) {
        $criteria_list[$index] = Criterion::where("id", $criterion->id)->first();

        $criteria_list[$index]->setAttribute("criteria_attempted", $criterion->criteria_attempted);
        $criteria_list[$index]->setAttribute("criteria_achieved", $criterion->criteria_achieved);
      }
      return $criteria_list;
    }*/
    }
    return null;
  }





  public function assessedActivities()
  {
    // return $this->hasMany('Activity')->whereNotNull('activities.outcomes_attempted')->orderBy('date', 'asc');
    $activitiesAssessed = DB::table('activities')
      ->join('activity_criterion as ac', 'ac.activity_id', '=', 'activities.id')
      ->join('assessments', 'assessments.activity_criterion_id', '=', 'ac.id')
	  ->whereNotNull('score')
      ->where('activities.course_id', $this->id)
      ->select('activities.id')
      ->distinct()
      ->lists('activities.id');
    //Log::info($activitiesAssessed);

    return $this->hasMany('Activity')->whereIn('activities.id', $activitiesAssessed)->orderBy('date', 'asc');

    //    return $this->hasMany('Activity')->where('activities.draft', 0)->orderBy('date', 'asc');
  }
  public static function get_associated_objective_outcome($typ_semester_course_id)
  {
    return DB::table('typ_semester_outcome')->join('typ_semester_objectives', 'typ_semester_outcome.id', '=', 'typ_semester_objectives.typ_semester_outcome_id')
      ->join('typ_semester_courses', 'typ_semester_courses.typ_semester_objective_id', '=', 'typ_semester_objectives.id')
      ->where('typ_semester_courses.id', $typ_semester_course_id);
  }


  public static function getFutureTypSemesterCourses($typ_semester_course_id, $program_id)
  {
    $typ_info = new stdClass();
    $typ_info->future_semesters = DB::table('typ_semester_outcome')
      ->join('typ_semester_objectives', 'typ_semester_outcome.id', '=', 'typ_semester_objectives.typ_semester_outcome_id')
      ->join('typ_semester_courses', 'typ_semester_courses.typ_semester_objective_id', '=', 'typ_semester_objectives.id')
      ->join('typ_semester_outcome as typ_o_2', 'typ_o_2.outcome_id', '=', 'typ_semester_outcome.outcome_id')
      ->join('semesters as sem1', 'sem1.id', '=', 'typ_semester_outcome.semester_id')
      ->join('typ_semester_objectives as typ_ob_2', function ($query) {
        $query->on('typ_ob_2.typ_semester_outcome_id', '=', 'typ_o_2.id')
          ->on('typ_semester_objectives.objective_id', '=', 'typ_ob_2.objective_id');
      })
      ->join('typ_semester_courses as typ_course_2', function ($join) {
        $join->on('typ_course_2.typ_semester_objective_id', '=', 'typ_ob_2.id')
          ->on('typ_semester_courses.course_id', '=', 'typ_course_2.course_id');
      })
      ->join('typ_program', 'typ_o_2.typ_program_id', '=', 'typ_program.id')
      ->join('semesters', 'semesters.id', '=', 'typ_o_2.semester_id')
      ->leftJoin('annual_plan_transformative', 'annual_plan_transformative.typ_semester_course_id', '=', 'typ_course_2.id')
      ->leftJoin('transformative_actions', 'transformative_actions.id', '=', 'annual_plan_transformative.trans_id')

      ->where('typ_semester_courses.id', $typ_semester_course_id)
      ->where('typ_program.program_id', $program_id)
      ->whereRaw('sem1.code < semesters.code')
      ->groupBy('typ_o_2.semester_id')
      ->select(
        'typ_o_2.semester_id',
        'typ_o_2.outcome_id',
        'typ_o_2.id',
        'typ_semester_courses.course_id',
        'typ_semester_objectives.objective_id',
        'typ_ob_2.id as typ_future_objective_id',
        'typ_course_2.id as typ_future_course_id',
        'semesters.name as semester_name',
        'semesters.code as semester_code',
        'transformative_actions.id as trans_id',
        'transformative_actions.type_of_TA',
        'transformative_actions.at_text',
        'transformative_actions.description'

      ); //->get();
    //Log::info('El sql');
    $typ_info->future_transformative_actions  = DB::table('typ_semester_outcome')
      ->join('typ_semester_objectives', 'typ_semester_outcome.id', '=', 'typ_semester_objectives.typ_semester_outcome_id')
      ->join('typ_semester_courses', 'typ_semester_courses.typ_semester_objective_id', '=', 'typ_semester_objectives.id')
      ->join('typ_semester_outcome as typ_o_2', 'typ_o_2.outcome_id', '=', 'typ_semester_outcome.outcome_id')
      ->join('semesters as sem1', 'sem1.id', '=', 'typ_semester_outcome.semester_id')
      ->join('typ_semester_objectives as typ_ob_2', function ($query) {
        $query->on('typ_ob_2.typ_semester_outcome_id', '=', 'typ_o_2.id')
          ->on('typ_semester_objectives.objective_id', '=', 'typ_ob_2.objective_id');
      })
      ->join('typ_semester_courses as typ_course_2', function ($join) {
        $join->on('typ_course_2.typ_semester_objective_id', '=', 'typ_ob_2.id')
          ->on('typ_semester_courses.course_id', '=', 'typ_course_2.course_id');
      })
      ->join('typ_program', 'typ_o_2.typ_program_id', '=', 'typ_program.id')
      ->join('semesters', 'semesters.id', '=', 'typ_o_2.semester_id')
      ->leftJoin('annual_plan_transformative', 'annual_plan_transformative.typ_semester_course_id', '=', 'typ_course_2.id')
      ->leftJoin('transformative_actions', 'transformative_actions.id', '=', 'annual_plan_transformative.trans_id')

      ->where('typ_semester_courses.id', $typ_semester_course_id)
      ->where('typ_program.program_id', $program_id)
      ->whereRaw('sem1.code < semesters.code')
      //->groupBy('typ_o_2.semester_id')
      ->select(
        'typ_o_2.semester_id',
        'typ_o_2.outcome_id',
        'typ_o_2.id',
        'typ_semester_courses.course_id',
        'typ_semester_objectives.objective_id',
        'typ_ob_2.id as typ_future_objective_id',
        'typ_course_2.id as typ_future_course_id',
        'semesters.name as semester_name',
        'semesters.code as semester_code',
        'transformative_actions.id as trans_id',
        'transformative_actions.type_of_TA',
        'transformative_actions.at_text',
        'transformative_actions.description'

      );
    //Log::info("typ_info");
    //Log::info($typ_info->future_semesters->toSql());
    $typ_info->future_semesters = $typ_info->future_semesters->get();
    //Log::info($typ_info->future_transformative_actions->toSql());
    $typ_info->future_transformative_actions = $typ_info->future_transformative_actions->get();
    //Log::info($typ_info->toSql());
    Log::info($typ_semester_course_id);
    Log::info($program_id);
    return $typ_info;
  }
  public function publishedActivities()
  {
    $activitiesAssessed = DB::table('activities')
      ->join('activity_criterion as ac', 'ac.activity_id', '=', 'activities.id')
      ->join('assessments', 'assessments.activity_criterion_id', '=', 'ac.id')
	  ->whereNotNull('score')
      ->where('activities.course_id', $this->id)
      ->where('activities.draft', 0)
      ->where('activities.diagnostic', 0)
      ->select('activities.id')
      ->distinct()
      ->lists('activities.id');
    //$this->hasMany('Activity')->whereNotNull('activities.outcomes_attempted')->where('activities.draft', 0)->orderBy('date', 'asc');
    return  $this->hasMany('Activity')->whereIn('activities.id', $activitiesAssessed)->orderBy('date', 'asc');
  }
  public function diagnosticActivities()
  {
    $activitiesAssessed = DB::table('activities')
      ->join('activity_criterion as ac', 'ac.activity_id', '=', 'activities.id')
      ->join('assessments', 'assessments.activity_criterion_id', '=', 'ac.id')
	  ->whereNotNull('score')
      ->where('activities.course_id', $this->id)
      ->where('activities.draft', 0)
      ->where('activities.diagnostic', 1)
      ->select('activities.id')
      ->distinct()
      ->lists('activities.id');
    //$this->hasMany('Activity')
    return  $this->hasMany('Activity')->whereIn('activities.id', $activitiesAssessed)->orderBy('date', 'asc');
  }

  public function outcomes_attempted()
  {
    return DB::table('courses')
      ->join('activities', 'activities.course_id', '=', 'courses.id')
      ->join('activity_criterion', 'activity_criterion.activity_id', '=', 'activities.id')
      ->join('criteria', 'activity_criterion.criterion_id', '=', 'criteria.id')
      ->join('criterion_objective_outcome', 'criterion_objective_outcome.criterion_id', '=', 'criteria.id')
      ->where('courses.id', '=', $this->id)
      ->select('criterion_objective_outcome.outcome_id')
      ->distinct()
      ->orderBy('criterion_objective_outcome.outcome_id')
      ->get();

    //   	SELECT distinct cr.outcome_id FROM  courses c, activities a, activity_criterion ac, criteria cr WHERE c.id=a.course_id and a.id=ac.activity_id and ac.criterion_id=cr.id and c.id=1
    // 	return $this->id;
  }


  public function outcomes_att()
  {
    $criteria_attempted = DB::table('courses')
      ->join('activities', 'activities.course_id', '=', 'courses.id')
      ->join('activity_criterion', 'activities.id', '=', 'activity_criterion.activity_id')
      ->select('criterion_id', 'activity_criterion.id as activity_criterion_id')
      ->where('courses.id', $this->id)
      ->where('draft', 0)
      ->where('activities.diagnostic', 0)
      ->get();

    // Old data was formed like { "outcome1": cuantity_of_criteria_associated, "outcome2":cuantity_of_criteria}
    $outcomes_attempted = [];



    foreach ($criteria_attempted as $criterion) {
      $outcomes = DB::table('criterion_objective_outcome')
        ->where('criterion_id', $criterion->criterion_id)
        ->select('outcome_id')
        ->distinct()
        ->lists('outcome_id');

      foreach ($outcomes as $outcome_id) {
        if (array_key_exists($outcome_id, $outcomes_attempted)) $outcomes_attempted[$outcome_id] += 1;
        else $outcomes_attempted[$outcome_id] = 1;
      }
    }
    Log::info("outcomes_attempted");
    Log::info($outcomes_attempted);

    return $outcomes_attempted;
  }
  public static function getCriteriaPlanReport($course_code = null)
  {
    if ($course_code) {
      $criteria_id = DB::table('annual_plan_objective')
        ->where('typ_semester_course_id', $course_code->typ_semester_course_id)
        ->lists('criteria_id');
      //Log::info($course_code->typ_semester_course_id);
      //Log::info("dame ese id");
      $program = Program::where('id', $course_code->program_id)->first();
      // Log::info($program->code);
      //Si es un programa como core, enseña todos los estudiantes
      if ($program->code == "") {
        $program_ids = DB::table('programs')
          ->where('school_id', $program->school->id)
          ->lists('programs.id');
        //Log::info("estamos");
      } else $program_ids = array($program->id);

      /*tabla es asi por curso codigo
      *
      *criterioDelPlan    EstudiantesDelProgramaQuePasaron   EstudiantesqueIntentaron
      *    
      *    criterio1                 3                             5
      *    criterio2                 2                             2
      *
      *
      */
      $table_per_criterion = DB::table('program_student_semester')
        ->join('course_student', 'program_student_semester.student_id', '=', 'course_student.student_id')
        ->join('courses', 'courses.id', '=', 'course_student.course_id')
        ->join('activities', 'activities.course_id', '=', 'courses.id')
        ->join('activity_criterion', 'activities.id', '=', 'activity_criterion.activity_id')
        ->join('criteria', 'criteria.id', '=', 'activity_criterion.criterion_id')
        ->join('rubric_activity', 'rubric_activity.activity_id', '=', 'activities.id')
        ->join('rubrics', 'rubrics.id', '=', 'rubric_activity.rubric_id')

        ->join('assessments', function ($join) {
          $join->on("assessments.student_id", '=', 'program_student_semester.student_id')
            ->on('assessments.activity_criterion_id', '=', 'activity_criterion.id');
        })
  	    ->whereNotNull('score')
        ->where('courses.semester_id', $course_code->semester_id)
        ->whereIn('courses.program_id', $program_ids)
        ->where('courses.code', $course_code->code)
        ->where('courses.number', $course_code->number)
        ->whereIn('program_student_semester.program_id', $program_ids)
        ->whereIn('activity_criterion.criterion_id', $criteria_id)
        ->whereIn('criteria.id', $criteria_id)
        ->where('program_student_semester.semester_id', $course_code->semester_id)
        ->select('criteria.*')
        ->addSelect(DB::raw('count(case when assessments.score>= rubrics.expected_points then 1 else null end) as criteria_achieved'))
        ->addSelect(DB::raw('count(assessments.activity_criterion_id) as criteria_attempted'))
        ->groupBy('criteria.id');

      /*Log::info($table_per_criterion->toSql());
      Log::info(array($course_code));
      Log::info($program_ids);
      Log::info($criteria_id);
      Log::info('mera 1');
      Log::info(array($course_code));
      Log::info($table_per_criterion->get()); */
      return $table_per_criterion->get();
    }
  }

  //dirty code repeated code
  public function getStudentReportForOutcomeAttribute()
  {
    return $this->getStudentReportForOutcome($this);
  }

  //cuantos estudiantes tiene este cursos que son del programa

  public function getQuantityAttribute()
  {
    $query = DB::table('courses')
      ->join('course_student', 'course_student.course_id', '=', 'courses.id')
      ->join('program_student_semester as pcs', function ($j) {
        $j->on('courses.program_id', '=', 'pcs.program_id')
          ->on('course_student.student_id', '=', 'pcs.student_id')
          ->on('courses.semester_id', '=', 'pcs.semester_id');
      });
    if ($this->grouped) {
      $query = $query->where('courses.name', $this->name)
        ->where('courses.code', $this->code)
        ->where('courses.number', $this->number)
        ->where('courses.semester_id', $this->semester_id);
    } else {
      $query = $query->where("courses.id", $this->id);
    }
    $query = $query->select(DB::raw("count( distinct pcs.student_id) as quantity"));

    $query =  $query->first();
    if (isset($query)) {
      return $query->quantity;
    }
    return 0;
  }


  //user id is for groupped professor sections. so el query es  code, number, semester y user_id
  public static function getStudentReportForOutcome($course_code = null, $user_id = null)
  {
    if ($course_code) {
      //Log::info('entré');

      //Si entra aqui, significa que son los criterios dentro del annual plan, bajo un dominio en particular. 


      if (isset($course_code->typ_semester_course_id)) {
        $criteria_id = DB::table('annual_plan_objective')
          ->where('typ_semester_course_id', $course_code->typ_semester_course_id)
          ->lists('criteria_id');
      }
      //si entra aqui, estamos buscando del curso (o grupo de cursos independientes

      else {
        $criteria_id = DB::table('courses')
          ->join('activities', 'courses.id', '=', 'activities.course_id')
          ->join('activity_criterion', 'activities.id', '=', 'activity_criterion.activity_id');

        //if this is 1, significa que son todos los cursos de ese semestre con nombre. 
        if (isset($course_code->grouped)) {
          $criteria_id = $criteria_id->where('courses.name', $course_code->name)
            ->where('courses.code', $course_code->code)
            ->where('courses.number', $course_code->number)
            ->where('courses.semester_id', $course_code->semester_id);

          //if user_id is set, this means its goruped courses of said professor.
          if ($user_id) {
            $criteria_id = $criteria_id->where('courses.user_id', $user_id);
          }
          $criteria_id = $criteria_id
            ->groupBy('activity_criterion.criterion_id')
            ->lists('activity_criterion.criterion_id');
        }

        //si es un solo curso. 
        else {
          $criteria_id = $criteria_id->where('courses.id', $course_code->id)
            ->groupBy('activity_criterion.criterion_id')
            ->lists('activity_criterion.criterion_id');
        }
      }
      //Log::info($criteria_id);
      $program = Program::where('id', $course_code->program_id)->first();
      // // Log::info($program->code);
      if ($program->code == "") {
        $program_ids = DB::table('programs')
          ->where('school_id', $program->school->id)
          ->lists('programs.id');
        //    Log::info("estamos");
      } else $program_ids = array($program->id);

      // Log::info(array($course_code));


      //ESTA ES LA TABLA QUERY PPOR STUDENT

      $table_per_student =  DB::table('assessments')
        ->join('activity_criterion', 'activity_criterion.id', '=', 'assessments.activity_criterion_id')
        ->join('activities', 'activities.id', '=', 'activity_criterion.activity_id')
        ->join('courses', 'courses.id', '=', 'activities.course_id')
        ->join('students', 'students.id', '=', 'assessments.student_id')
        ->join('criterion_objective_outcome', 'criterion_objective_outcome.criterion_id', '=', 'activity_criterion.criterion_id')
        ->join('rubric_activity', 'rubric_activity.activity_id', '=', 'activities.id')
        ->join('rubrics', 'rubrics.id', '=', 'rubric_activity.rubric_id')
        ->join('semesters', 'semesters.id', '=', 'courses.semester_id')
        ->join('outcomes', 'criterion_objective_outcome.outcome_id', '=', 'outcomes.id')
	    ->whereNotNull('score')
        ->whereIn('students.program_id', $program_ids)
        ->where('semester_id', $course_code->semester_id)
        ->where('semesters.is_visible', '=', 1)
        ->where('activities.draft', 0)
        ->where('activities.diagnostic', 0)

        ->select('student_id', 'students.program_id', 'semesters.code', 'outcome_id', 'outcomes.expected_outcome', 'criterion_objective_outcome.criterion_id', 'score', 'expected_points')
        ->distinct()
        ->get();


      //Table
      /* Student  criterion_attempted criterion passed
      *     1             2                   1
      *     2             7                   4
      *
      *
      */


      $table_per_student = DB::table('course_student')

        //->join('program_student_semester', 'program_student_semester.student_id', '=', 'course_student.student_id')
        ->join("students", 'course_student.student_id', '=', 'students.id')
        ->join('courses', 'courses.id', '=', 'course_student.course_id')
        ->join('activities', 'activities.course_id', '=', 'courses.id')
        ->join('activity_criterion', 'activities.id', '=', 'activity_criterion.activity_id')
        ->join('criteria', 'criteria.id', '=', 'activity_criterion.criterion_id')
        ->join('rubric_activity', 'rubric_activity.activity_id', '=', 'activities.id')
        ->join('rubrics', 'rubrics.id', '=', 'rubric_activity.rubric_id')

        ->join('assessments', function ($join) {
          $join->on("assessments.student_id", '=', 'course_student.student_id')
            ->on('assessments.activity_criterion_id', '=', 'activity_criterion.id');
        })
	    ->whereNotNull('score')
        ->where('activities.draft', 0)
        ->where('activities.diagnostic', 0);


      // si typ_semester_course esta prendido
      // o, si with_program_report está prendido
      //, then significa que es de los estudiantes del programa
      //

      if (isset($course_code->typ_semester_course_id)) {
        $table_per_student = $table_per_student->join('program_student_semester', 'program_student_semester.student_id', '=', 'course_student.student_id')
          ->whereIn('program_student_semester.program_id', $program_ids)
          ->where('program_student_semester.semester_id', $course_code->semester_id);
      }
      if (isset($course_code->with_program_report)) {
        $table_per_student = $table_per_student->join('program_student_semester', 'program_student_semester.student_id', '=', 'course_student.student_id')
          ->whereIn('program_student_semester.program_id', $program_ids)
          ->where('program_student_semester.semester_id', $course_code->semester_id);
      }
      //si entra aqui, no especifica dominio, y hay que join todos los dominios
      if (!isset($course_code->typ_semester_course_id)) {

        $table_per_student = $table_per_student->join('criterion_objective_outcome', 'criterion_objective_outcome.criterion_id', '=', 'activity_criterion.criterion_id')
          ->join('outcomes', 'criterion_objective_outcome.outcome_id', '=', 'outcomes.id');
      }

      //si estan grouped
      if (isset($course_code->grouped)) {
        $table_per_student = $table_per_student->where('courses.semester_id', $course_code->semester_id)
          ->where('courses.code', $course_code->code)
          ->where('courses.number', $course_code->number);
      }
      //si estan solo
      else {
        $table_per_student = $table_per_student->where('courses.id', $course_code->id);
      }
      ///// significa que depende
      /////->where('courses.semester_id', $course_code->semester_id)
      //->whereIn('courses.program_id', $program_ids)
      /////->where('courses.code', $course_code->code)
      /////->where('courses.number', $course_code->number)
      //->whereIn('program_student_semester.program_id', $program_ids)
      $table_per_student = $table_per_student->whereIn('activity_criterion.criterion_id', $criteria_id)
        //->where('program_student_semester.semester_id', $course_code->semester_id)
        ->select('students.number as student_id')
        ->addSelect(DB::raw('count(assessments.activity_criterion_id) as criteria_attempted'))
        ->addSelect(DB::raw('count(case when assessments.score>=rubrics.expected_points then 1 else NULL end) as criteria_achieved'));



      //si entra aqui, significa que va agrupar los estudiantes para un solo dominio. 
      if (isset($course_code->typ_semester_course_id)) {

        $table_per_student = $table_per_student->groupBy('students.id');
        $students = $table_per_student->get();
      } else {
        $table_per_student = $table_per_student->addSelect('outcome_id', 'outcomes.expected_outcome')
          ->groupBy(array('students.id', 'outcome_id'));

        Log::info($table_per_student->toSql());
        Log::info($criteria_id);
        Log::info($course_code->id);
        Log::info($program_ids);
        Log::info($course_code->semester_id);
        Log::info("Amiga ponle a br");

        $query = $table_per_student->get();







        //Aqui queremos crear un arreglo que sea
        /*
          students = {
            outcome_id: { 
              'students':{
              {student x,  crit_atttempted, crit_achieved},
              {student x2, crit_attempted, crit_achieved},
              ...
              }
              'calculation':{
                student_achieved, student_attempted
              }

            }
            outcome_id2:
            { 
              'students':{
              {student x,  crit_atttempted, crit_achieved},
              {student x2, crit_attempted, crit_achieved},
              ...
              }
              'calculations':{
                student_achieved, student_attempted
              }
            },
          }
          */

        $students = [];

        foreach ($query as $q) {

          if (array_key_exists($q->outcome_id, $students)) {
            $students[$q->outcome_id]['students'][] = $q;
          } else {
            $students[$q->outcome_id]['students'] = [];
            $students[$q->outcome_id]['students'][] = $q;
          }

          $criteria_attempted = $q->criteria_attempted;
          $criteria_achieved = $q->criteria_achieved;
          $expected_outcome = $q->expected_outcome;

          //PREGUNTA??? ES 66.66??????

          $achieved = 0;
          if ($criteria_attempted != 0 && $criteria_achieved / $criteria_attempted * 100 >= $expected_outcome) {
            $achieved = 1;
          }


          if (isset($students[$q->outcome_id]['calculations']) && $criteria_attempted) {
            $students[$q->outcome_id]['calculations']['student_attempted'] += 1;
            $students[$q->outcome_id]['calculations']['student_achieved'] += $achieved;
          } else if ($criteria_attempted) {
            $students[$q->outcome_id]['calculations']['student_attempted'] = 1;
            $students[$q->outcome_id]['calculations']['student_achieved'] = $achieved;
          }
        }
      }






      //Log::info($criteria_id);
      // Log::info('los courseCode');
      // Log::info(array($course_code));
      //Log::info('los programas');
      //Log::info($program_ids);
      //Log::info("crit");
      //Log::info($criteria_id);

      //Log::info($table_per_student->toSql());

      //Log::info($students);



      return $students;
    }
  }
  public function getReportObject()
  {
    $reportObject = new stdClass();

    $reportObject->criteriaReport = DB::table('courses')
      ->join('activities', 'activities.course_id', '=', 'courses.id')
      ->join('rubric_activity', 'rubric_activity.activity_id', '=', 'activities.id')
      ->join('rubrics', 'rubrics.id', '=', 'rubric_activity.rubric_id')
      ->join('activity_criterion', 'activity_criterion.activity_id', '=', 'activities.id')
      ->join('criteria', 'criteria.id', '=', 'activity_criterion.criterion_id')
      ->join('assessments', 'activity_criterion.id', '=', 'assessments.activity_criterion_id')
      ->select(DB::raw(
        "count(assessments.score) as assessed,
       count(case when assessments.score >= rubrics.expected_points then 1 else NULL end) as achieved,
       criteria.*"
      ))
	  ->whereNotNull('score')
      ->where('courses.id', $this->id)
      ->groupBy('criteria.id')
      ->get();
    //Log::info($this->id);
    $reportObject->outcomesReport = DB::table('courses')
      ->join('activities', 'activities.course_id', '=', 'courses.id')
      ->join('rubric_activity', 'rubric_activity.activity_id', '=', 'activities.id')
      ->join('rubrics', 'rubrics.id', '=', 'rubric_activity.rubric_id')
      ->join('activity_criterion', 'activity_criterion.activity_id', '=', 'activities.id')
      ->join('assessments', 'activity_criterion.id', '=', 'assessments.activity_criterion_id')
      ->join('criterion_objective_outcome', 'activity_criterion.criterion_id', '=', 'criterion_objective_outcome.criterion_id')
      ->join('outcomes', 'criterion_objective_outcome.outcome_id', '=', 'outcomes.id')
      ->select(
        DB::raw(
          "student_id, outcomes.name, outcomes.id,
        count(activity_criterion.criterion_id) as attempted,
        count(case when assessments.score >= rubrics.expected_points then 1 else null end) as achieved"
        )
      )
	  ->whereNotNull('score')
      ->where('courses.id', $this->id)
      ->groupBy(array('outcome_id', 'student_id'))
      ->get();

    return $reportObject;
  }

  public function outcomes_ach()
  {
    $criteria = DB::table('criteria')
      ->join('activity_criterion', 'activity_criterion.criterion_id', '=', 'criteria.id')
      ->join('activities', 'activity_criterion.activity_id', '=', 'activities.id')
      ->join('rubric_activity', 'rubric_activity.activity_id', '=', 'activities.id')
      ->join('rubrics', 'rubrics.id', '=', 'rubric_activity.rubric_id')
      ->join('courses', 'activities.course_id', '=', 'courses.id')
      ->join('criterion_objective_outcome', 'criterion_objective_outcome.criterion_id', '=', 'criteria.id')
      ->where('courses.id', '=', $this->id)
      ->where('activities.draft', 0)
      ->where('activities.diagnostic', 0)
      ->select(
        'criteria.id',
        'rubrics.expected_percentage as expected_percentage_students_achieving'/*'expected_percentage_students_achieving',*/,
        'activity_criterion.activity_id'
      )
      ->distinct()
      ->get();
    // Old data was formed like { "outcome1": cuantity_of_criteria_associated_passed, "outcome2":cuantity_of_criteria}

    $outcomes_achieved = [];

    foreach ($criteria as $criterion) {
      $students_attempted = Criterion::students_attempted($criterion->id, $criterion->activity_id);
      $students_achieved = Criterion::students_achieved($criterion->id, $criterion->activity_id);

      //Log::info('estamos aqui');
      Log::info($students_attempted);

      if ($students_attempted) {
        $percentage_students_who_achieved = 100.0 * $students_achieved / $students_attempted;
      } else {
        $percentage_students_who_achieved = 0;
      }
      if ($percentage_students_who_achieved >= $criterion->expected_percentage_students_achieving) {
        $outcomes = DB::table('criterion_objective_outcome')
          ->where('criterion_id', $criterion->id)
          ->select('outcome_id')
          ->distinct()
          ->lists('outcome_id');

        foreach ($outcomes as $outcome_id) {
          if (array_key_exists($outcome_id, $outcomes_achieved)) $outcomes_achieved[$outcome_id] += 1;
          else $outcomes_achieved[$outcome_id] = 1;
        }
      }
    }
    Log::info(
      "OUTCOMES_ACHIEVED: "
    );
    Log::info(
      $outcomes_achieved
    );
    return $outcomes_achieved;
  }

  public function someSectionAssessed()
  {
    $all_sections = DB::table('courses')
      ->join('activities', 'activities.course_id', '=', 'courses.id')
      ->join('activity_criterion as ac', 'ac.activity_id', '=', 'activities.id')
      ->join('assessments', 'assessments.activity_criterion_id', '=', 'ac.id')
      ->where('courses.name', $this->name)
      ->where('code', $this->code)
      ->where('number', $this->number)
      ->where('semester_id', $this->semester_id)
      ->where(
        'program_id',
        $this->program_id
      )
	  ->whereNotNull('score')
      ->where('activities.draft', 0)
      ->where('activities.diagnostic', 0)
      ->groupBy('courses.id')
      ->count();

    return $all_sections;
  }

  public function isAssessed()
  {
    $assessed = DB::table('assessments')
      ->join('activity_criterion', 'assessments.activity_criterion_id', '=', 'activity_criterion.id')
      ->join('activities', 'activity_criterion.activity_id', '=', 'activities.id')
	  ->whereNotNull('score')
      ->where('activities.course_id', $this->id)
      ->count();
    //             Log::info("aqui".$assessed);

    if ($assessed) return true;
    else return false;
  }


  public function outcomes_achieved()
  {
    $criteria = DB::table('criteria')
      ->join('activity_criterion', 'activity_criterion.criterion_id', '=', 'criteria.id')
      ->join('activities', 'activity_criterion.activity_id', '=', 'activities.id')
      ->join('rubric_activity', 'rubric_activity.activity_id', '=', 'activities.id')
      ->join('rubrics', 'rubrics.id', '=', 'rubric_activity.rubric_id')
      ->join('courses', 'activities.course_id', '=', 'courses.id')
      ->join('criterion_objective_outcome', 'criterion_objective_outcome.criterion_id', '=', 'criteria.id')
      ->where('courses.id', '=', $this->id)
      ->select(
        'criteria.id',
        'rubrics.expected_percentage as expected_percentage_students_achieving'/*'expected_percentage_students_achieving',*/,
        'activity_criterion.activity_id'
      )
      ->distinct()
      ->get();

    $conteo = 0;
    foreach ($criteria as $criterion) {
      $students_attempted = Criterion::students_attempted($criterion->id, $criterion->activity_id);
      $students_achieved = Criterion::students_achieved($criterion->id, $criterion->activity_id);

      if ($students_attempted) {
        $percentage_students_who_achieved = 100.0 * $students_achieved / $students_attempted;
      } else {
        $percentage_students_who_achieved = 0;
      }
      if ($percentage_students_who_achieved >= $criterion->expected_percentage_students_achieving) {
        $conteo++;
      }
    }
    return $conteo;
  }

  public function semester()
  {
    return $this->belongsTo('Semester');
  }

  public function sectionsWithOutcome($outcome_id)
  {
    return Course::has('activities')->with(array('activities' => function ($query) use (&$outcome_id) {
      $query->where('outcomes_attempted', 'not like', '\'%"' . $outcome_id . '":0%\'');
    }))->where('code', $this->code)->where('number', $this->number)->get();
  }
}