<?php

namespace App\Http\Controllers;

use Illuminate\Foundation\Bus\DispatchesJobs;
use Illuminate\Routing\Controller as BaseController;
use Illuminate\Foundation\Validation\ValidatesRequests;
use DB;

abstract class UsersController extends BaseController {

    use DispatchesJobs,
        ValidatesRequests;

    function checkSY($user_id) {
        $query = DB::table('school_year')->where('users_id', $user_id)->get();
        $SYlist = json_decode(json_encode($query), true);
        return $SYlist;
    }

    function checkLoad($user_id) {
        $SYlist = $this->checkSY($user_id);

        foreach ($SYlist as $data) {
            $query = DB::table('faculty_load')->where('school_year_id', $data['id'])->get();
            $loadlist = json_decode(json_encode($query), true);
        }
        return $loadlist;
    }

    function checkStudents($user_id) {
        $loadlist = $this->checkLoad($user_id);
        foreach ($loadlist as $data) {
            $query = DB::table('students')->where('load_id', $data['id'])->get();
            $studentslist[] = json_decode(json_encode($query), true);
        }
        return $studentslist;
    }

    function checkRecords($user_id) {
        $studentslist = $this->checkStudents($user_id);
        foreach ($studentslist as $data) {
            foreach ($data as $data1) {
                $query = DB::table('records')->where('students_id', $data1['id'])->get();
                $recordslist[][] = json_decode(json_encode($query), true);
                $records = array_column($recordslist, 0);
            }
        }
        return $records;
    }

    function getStudentData($subject_id) {
        $query = DB::table('students')->where('load_id', $subject_id)->get();
        $studentlist = json_decode(json_encode($query), true);

        return $studentlist;
    }

    function getActivitiesData($subject_id,$term) {
        
        $query = DB::table('enrollment')->join('students','enrollment.students_id','=','students.id')->where('enrollment.faculty_load_id',$subject_id)->select('enrollment.id as enrollment_id','enrollment.faculty_load_id','students.id as students_id')->get();
        $conn = json_decode(json_encode($query), true);
        $connect = $conn[0];
        $query = DB::table('students')->join('records','records.enrollment_students_id','=','students.id')->where('records.enrollment_faculty_load_id',$subject_id)->where('term',$term)->select('records.enrollment_students_id as students_id','records.*','students.*','records.id as id')->get();
        $records = json_decode(json_encode($query), true);

        if($records==null){
            $activities = null;
            return $activities;
        }
        else {

            $query = \DB::table('selected_activities')->where('type','lec')->where('load_id',$subject_id)->get();
            $selected_activities = json_decode(json_encode($query), true);

            $sel_color = array();
            $fon_color = array();
            foreach ($selected_activities as $data) {
                foreach ($data as $data2) {
                    $sel_color[$data['act_name']] = $data['color'];
                    $fon_color[$data['act_name']] = $data['fontcolor'];
                }
            }
        foreach ($records as $data) {
            $query = DB::table('activities')->join('records', 'activities.records_id','=','records.id')->where('type','lec')->select('records.enrollment_students_id as students_id','records.term','records.exam_score','records.exam_total','records.examlab_score','records.examlab_total','records.exam_grade','records.term_grade','records.remarks','activities.*')->where('records.id',$data['id'])->where('records.enrollment_faculty_load_id',$subject_id)->get();
                //if (json_decode(json_encode($query), true)) {
                $activitylist[][] = json_decode(json_encode($query), true);
                $activities = array_column($activitylist, 0); //}
        }
        //dd($activities);
        $acts = array();
        $count=0;
        foreach ($activities as $key => $data) {
            $count1 = 0;
            foreach ($data as $data2) {
                foreach ($data2 as $data3) {
                    $activities[$count][$count1]['color'] = $sel_color[preg_replace('/[0-9]+/', '', $data2['act_name'])];
                    $activities[$count][$count1]['fontcolor'] = $fon_color[preg_replace('/[0-9]+/', '', $data2['act_name'])];
                    $activities[$count][$count1]['sname'] = $records[$key]['sname'];
                }
            $count1++;
            }
        $count++;
        }
    //dd($activities);
            
        return  $activities;
        }
    }

    function getActivitiesDatalab($subject_id,$term) {
        
        $query = DB::table('enrollment')->join('students','enrollment.students_id','=','students.id')->where('enrollment.faculty_load_id',$subject_id)->select('enrollment.id as enrollment_id','enrollment.faculty_load_id','students.id as students_id')->get();
        $conn = json_decode(json_encode($query), true);
        $connect = $conn[0];
        $query = DB::table('students')->join('records','records.enrollment_students_id','=','students.id')->where('records.enrollment_faculty_load_id',$subject_id)->where('term',$term)->select('records.enrollment_students_id as students_id','records.*','students.*','records.id as id')->get();
        $records = json_decode(json_encode($query), true);

        if($records==null){
            $activities = null;
            return $activities;
        }
        else {

            $query = \DB::table('selected_activities')->where('type','lab')->where('load_id',$subject_id)->get();
            $selected_activities = json_decode(json_encode($query), true);
            //dd($selected_activities);
            $sel_color = array();
            $fon_color = array();
            foreach ($selected_activities as $data) {
                foreach ($data as $data2) {
                    $sel_color[$data['act_name']] = $data['color'];
                    $fon_color[$data['act_name']] = $data['fontcolor'];
                }
            }
        foreach ($records as $data) {
            $query = DB::table('activities')->join('records', 'activities.records_id','=','records.id')->where('type','lab')->select('records.enrollment_students_id as students_id','records.term','records.exam_score','records.exam_total','records.examlab_score','records.examlab_total','records.exam_grade','records.term_grade','records.remarks','activities.*')->where('records.id',$data['id'])->where('records.enrollment_faculty_load_id',$subject_id)->get();
                //if (json_decode(json_encode($query), true)) {
                $activitylist[][] = json_decode(json_encode($query), true);
                $activities = array_column($activitylist, 0); //}
        }
        //dd($activities);
        $acts = array();
        $count=0;
        foreach ($activities as $key => $data) {
            $count1 = 0;
            foreach ($data as $data2) {
                foreach ($data2 as $data3) {
                    $activities[$count][$count1]['color'] = $sel_color[preg_replace('/[0-9]+/', '', $data2['act_name'])];
                    $activities[$count][$count1]['fontcolor'] = $fon_color[preg_replace('/[0-9]+/', '', $data2['act_name'])];
                    $activities[$count][$count1]['sname'] = $records[$key]['sname'];
                }
            $count1++;
            }
        $count++;
        }
    //dd($activities);
            
        return  $activities;
        }
    }

    function getRecordsData($subject_id, $term) {
        $query = DB::table('records')->where('term', $term)
                ->where('enrollment_faculty_load_id', $subject_id)
                ->get();
        $records = json_decode(json_encode($query), true);
        return $records;
    }

    function getActivitiesData1($record_id) {
        $query = DB::table('activities')->where('records_id', $record_id)->get();
        $activities = json_decode(json_encode($query), true);
        return $activities;
    }

    /* function getRecordsData($studentlist, $term) {

      foreach ($studentlist as $data) {
      $query = DB::table('records')->where('students_id', $data['id'])
      ->where('term', $term)->get();
      $recordslist[] = json_decode(json_encode($query), true);
      $records = array_column($recordslist, 0);
      }
      //dd($records);
      return $records;
      }
     */

    function getRData($subject_id, $term) {
        $studentlist = $this->getStudentData($subject_id);

        foreach ($studentlist as $data) {
            $query = DB::table('records')->where('students_id', $data['id'])
                            ->where('term', $term)->get();
            $recordslist[] = json_decode(json_encode($query), true);
            $records = array_column($recordslist, 0);
        }
        //dd($records);
        foreach ($records as $records) {
            $query = DB::table('activities')->join('records', 'records.id', '=', 'activities.records_id')
                            ->join('students', 'students.id', '=', 'records.students_id')
                            ->select('activities.*', 'students.id', 'students.students_name')
                            ->where('records_id', $records['id'])->get();
            $activitylist[][] = json_decode(json_encode($query), true);
            $activities = array_column($activitylist, 0);
        }
        //dd($activities);
        return $activities;
    }

    function getActivitiesList($subject_id) {
        $students_list = getStudentData($subject_id);
        $query = DB::table('activities')->where('records_id', $subject_id)->get();
        $studentlist = json_decode(json_encode($query), true);
        return $activities;
    }

    function generateSubject($schoolYear, $semester, $id) {
        //dd($semester);
        $query = \DB::table('faculty_load')->where('school_year_sy', $schoolYear)
                        ->where('school_year_users_id', $id)->where('semester', $semester)->get();
        $resultArray = json_decode(json_encode($query), true);
        //dd($resultArray);
        return $resultArray;
    }

    function generateInstructor($department) {
        $query = \DB::table('users')->where('department', $department)
                        ->where('userType', 'Instructor')->get();
        $resultArray = json_decode(json_encode($query), true);
        //dd($resultArray);
        return $resultArray;
    }

    function generateInstructorSubject($id) {
        $query = \DB::table('faculty_load')->where('semester', '1st Semester')
                        ->where('school_year_users_id', $id)->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function generateInstructorData($id) {
        $query = \DB::table('users')
                        ->where('id', $id)->orderBy('users.name', 'asc')->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getInstructors($department) {
        $query = \DB::table('users')->where('department', $department)->where('userType', 'Instructor')->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getChairmanName($department) {
        $query = \DB::table('users')->where('department', $department)->where('userType', 'Chairman')->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getDepartmentList($college) {
        $query = \DB::table('users')->where('college', $college)->where('userType', 'Chairman')->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getLoadSY($id) {
        $query = \DB::table('faculty_load')->distinct()->select('school_year_sy')->where('school_year_users_id', $id)->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }
 
    function getCollegeDepartment($college) {
        $query = \DB::table('users')->where('college', $college)->where('userType', 'Instructor')->OrderBy('users.name', 'asc')->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getInstructorLoad($id) {
        $query = \DB::table('faculty_load')->where('school_year_users_id', $id)->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getSY($sy, $id) {
        $query = \DB::table('faculty_load')->where('school_year_users_id', $id)->where('school_year_sy', $sy)->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getRecordId($term, $loadID, $studentID) {
        $query = \DB::table('records')->where('term', $term)->where('enrollment_faculty_load_id', $loadID)->where('enrollment_students_id', $studentID)->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getClassInfo($id) {
        $query = \DB::table('faculty_load')->where('id', $id)->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
    }

    function getCombineData($id, $term) {
        $query = \DB::table('records')->where('students_id', $id)->where('term', $term)->get();
        $resultArray = json_decode(json_encode($query), true);
        foreach ($resultArray as $data) {
            $query = \DB::table('activities')->where('records_id', $data['id'])->get();
            $resultArray1 = json_decode(json_encode($query), true);
            $resultArray[] = $resultArray1;
        }
        return $resultArray;
    }

       function getGradesData1($id) {
        $query = \DB::table('overall')->join('students', 'students.id', '=', 'overall.enrollment_students_id')
                        ->select('overall.*', 'students.id', 'students.sname')->orderBy('students.sname', 'asc')
                        ->where('enrollment_faculty_load_id', $id)->get();
        $resultArray = json_decode(json_encode($query), true);
        return $resultArray;
       }
        
    function getGradesData($id) {
        $query = \DB::table('overall')->join('students', 'students.id', '=', 'overall.enrollment_students_id')
                        ->select('overall.*', 'students.id', 'students.sname')->orderBy('students.sname', 'asc')
                        ->where('enrollment_faculty_load_id', $id)->get();
        $resultArray = json_decode(json_encode($query), true);
        
        foreach ($resultArray as $key => $data) {
            foreach ($data as $key1 => $data2) {
                $gdataArr[$key][$key1] = $data2;
                if($key1 == 'midterm_grade' || $key1 == 'prefinal_grade' || $key1 == 'final_grade') {
                    $gdataArr[$key][$key1] = number_format($data2, 1, '.', '');
                }
            }
        }

        return $gdataArr;
    }

    function getSelectedActivities($id) {
        $query = \DB::table('selected_activities')->where('load_id', $id)->get();
        $resultArray = json_decode(json_encode($query), true);
        //  dd($resultArray);

        return $resultArray;
    }

 
    function getEnrolmentID($id,$student_id) {
        $query = \DB::table('enrollment')->where('faculty_load_id', $id)->where('students_id', $student_id)->get();
        $resultArray = json_decode(json_encode($query), true);
        //dd($resultArray);

        return $resultArray;
    }
    function getLoadId($subj_code,$class) {
        $query = \DB::table('faculty_load')->where('class', $class)->where('subj_code', $subj_code)->get();
        $resultArray = json_decode(json_encode($query), true);
           //     dd($resultArray);
        return $resultArray;
    }
}