<?php
class ExamManagementModel extends CI_Model {
    
    public function insert_data($data_array,$table){
        $this->db->insert($table,$data_array);
        return $this->db->insert_id();
    }
    
    public function get_data($table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->order_by('id','DESC');
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_data_asc($table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->order_by('id','ASC');
        $info=$this->db->get()->result_array();
        return $info;
    }

    public function get_item_data($id,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('id',$id);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function check_exam_subject($class,$subject,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('class',$class);
        $this->db->where('subject',$subject);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function update_item_data($id,$data_array_update,$table){
        $this->db->where("id",$id);
        $this->db->update($table,$data_array_update);
        return $this->db->affected_rows();
    }
    
    
    public function get_subject_master_data($class,$exam_type,$subject_type,$status,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        if($class !=""){
            $this->db->where("class",$class);
        }
        
        if($exam_type !=""){
            $this->db->where("exam_type",$exam_type);
        }
        
        if($subject_type !=""){
            $this->db->where("subject_type",$subject_type);
        }
        
        if($status !=""){
            $this->db->where("status",$status);
        }
        
        $this->db->order_by('id','DESC');
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_exams($academic_session,$table){
        // $info=array();
        // $this->db->select('*');
        // $this->db->from($table);
        // $this->db->where('acedemic_session',$academic_session);
        // $info=$this->db->get()->result_array();
        // return $info;
        
        $sql="SELECT all_exams.*,academic_session_details.year_long FROM all_exams LEFT JOIN academic_session_details ON academic_session_details.id=all_exams.acedemic_session WHERE all_exams.acedemic_session='$academic_session'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_exam_heads($academic_session,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('academic_session',$academic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_exam_heads_with_filter($class,$status,$exam_id,$freeze_status,$academic_session,$table){
        $sql="SELECT exam_heads.*,exams.exam_title FROM $table as exam_heads LEFT JOIN all_exams as exams ON exam_heads.exam_id = exams.id WHERE exam_heads.academic_session='$academic_session'";
        if($class !=""){
            $sql.=" AND exam_heads.class='$class' ";
        }
        if($status !=""){
            $sql.=" AND exam_heads.status='$status' ";
        }
        
        if($exam_id !=""){
            $sql.=" AND exam_heads.exam_id='$exam_id' ";
        }
        
        if($freeze_status !=""){
            $sql.=" AND exam_heads.freeze='$freeze_status' ";
        }
        
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function check_exam_exists($academic_session,$exam_title,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('acedemic_session',$academic_session);
        $this->db->where('exam_title',$exam_title);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function check_exam_head($exam_id,$exam_head,$class,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('exam_id',$exam_id);
        $this->db->where('exam_head',$exam_head);
        $this->db->where('class',$class);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_class_subjects($class,$academic_session,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('academic_session',$academic_session);
        $this->db->where('class',$class);
        $this->db->order_by('subject_order','ASC');
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_class_exam_schedule($class,$ehid,$sch_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($sch_table);
        $this->db->where('exam_head',$ehid);
        $this->db->where('class',$class);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_class_exam_schedule_last_date($class,$ehid,$sch_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($sch_table);
        $this->db->where('exam_head',$ehid);
        $this->db->where('class',$class);
        $this->db->order_by("exam_date","DESC");
        $this->db->limit(1);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    
    public function check_schedule_exists($subject_id,$exam_head,$academic_session,$class,$sch_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($sch_table);
        
        $this->db->where('subject_id',$subject_id);
        $this->db->where('exam_head',$exam_head);
        $this->db->where('academic_session',$academic_session);
        $this->db->where('class',$class);
        
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function update_exam_schedule($academic_session,$exam_head,$subject_id,$data_array_update,$sch_table){
        $this->db->where("academic_session",$academic_session);
        $this->db->where("exam_head",$exam_head);
        $this->db->where("subject_id",$subject_id);
        $this->db->update($sch_table,$data_array_update);
        return $this->db->affected_rows();
    }
    public function get_students_ac_session_by_course_and_class($acedemic_session,$class,$section){
        // $table='student_courses_data';
        // $info=array();
        // $this->db->select('*');
        // $this->db->from($table);
        // $this->db->where('class',$class);
        // $this->db->where('section',$section);
        // // $this->db->where('course_id',$course_id);
        // $this->db->where('acedemic_session',$acedemic_session);
        // $this->db->order_by('roll_no','ASC');
        // $info=$this->db->get()->result_array();
        // return $info; 
        
        $sql="SELECT student.id,student.student_name,student.father_name,student.mother_name,student.status,scd.class,scd.section,scd.roll_no,scd.course_id FROM student LEFT JOIN student_courses_data as scd ON student.id = scd.sid WHERE scd.class='$class' AND scd.section='$section' AND scd.acedemic_session ='$acedemic_session' AND student.status=1 ORDER BY student.student_name ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
        
    }
    
    public function get_class_section_structure($academic_session){
        $sql="SELECT scs.*,school_staff.name,school_staff.salutation FROM school_classes_structure as scs LEFT JOIN school_staff ON scs.class_teacher=school_staff.id WHERE scs.acedemic_session='$academic_session' ORDER BY scs.course_id ASC,scs.section ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_class_sections($calss,$academic_session){
        $sql="SELECT scs.*,school_staff.name,school_staff.salutation FROM school_classes_structure as scs LEFT JOIN school_staff ON scs.class_teacher=school_staff.id WHERE scs.acedemic_session='$academic_session' AND scs.class='$calss' ORDER BY scs.course_id ASC,scs.section ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }

    public function get_student_combined_data($sid,$academic_session,$table){
        $sql="SELECT student.*,scd.class as scd_class,scd.section as scd_section,scd.course_id,scd.roll_no,scd.height,scd.weight,courses.course,courses.subject_stream FROM student LEFT JOIN student_courses_data as scd ON scd.sid=student.id LEFT JOIN courses ON scd.course_id = courses.id WHERE scd.acedemic_session='$academic_session' AND scd.sid='$sid'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_class_active_subjects($class,$subjects_table){
        $sql="SELECT * FROM $subjects_table WHERE class='$class' ANd status='active'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function student_subject_info($sid,$academic_session,$subject_id,$student_subjects_table){
        // $sql="SELECT * FROM $student_subjects_table WHERE sid='$sid' AND academic_session='$academic_session' AND subject_id='$subject_id'";
        // $query=$this->db->query($sql);        
        // return $query->result_array();
        $info=array();
        $this->db->select('*');
        $this->db->from($student_subjects_table);
        $this->db->where('sid',$sid);
        $this->db->where('academic_session',$academic_session);
        $this->db->where('subject_id',$subject_id);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function delete_student_subject($sid,$subjectid,$class,$acedemic_session,$table){
        $this->db->where('sid',$sid);
        $this->db->where('subject_id',$subjectid);
        $this->db->where('class',$class);
        $this->db->where('academic_session',$acedemic_session);
        $this->db->delete($table);
        return $this->db->affected_rows(); 
    }
    
    public function student_subjects_combined($sid,$class,$academic_session,$student_subjects_table,$exam_subject_master_table){
        $sql="SELECT std_sub.*,exam_subjects.subject,subject_order FROM $student_subjects_table as std_sub LEFT JOIN $exam_subject_master_table as exam_subjects ON exam_subjects.id=std_sub.subject_id WHERE std_sub.sid='$sid' AND std_sub.academic_session='$academic_session' AND std_sub.class='$class' ORDER BY exam_subjects.subject_order";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_student_docs_data($sid,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    
    public function get_course_exam_heads($class,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('status','active');
        $this->db->where('class',$class);
        $this->db->order_by('head_order','ASC');
        $info=$this->db->get()->result_array();
        return $info;
    }
    public function get_course_exam_wise_heads($class,$exam_id,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('status','active');
        $this->db->where('class',$class);
        $this->db->where('exam_id',$exam_id);
        $this->db->order_by('head_order','ASC');
        $info=$this->db->get()->result_array();
        return $info;    
    }
    
    public function get_course_active_subjects($class,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->order_by('subject_order','ASC');
        $this->db->where('class',$class);
        $this->db->where('status','active');
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_log_item_data($sid,$ehid,$subject_id,$elog_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($elog_table);
        $this->db->where('sid',$sid);
        $this->db->where('subject_id',$subject_id);
        $this->db->where('exam_head',$ehid);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    
    public function check_course_exam_data($exam,$course,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('exam_id',$exam);
        $this->db->where('course_id',$course);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_student_exam_data($sid,$exam_id,$course_id,$table){
         $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('exam_id',$exam_id);
        $this->db->where('course_id',$course_id);
        $this->db->where('sid',$sid);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    
    public function update_elog_data($sid,$ehid,$subject_id,$data_array_update,$elog_table){
        $this->db->where('sid',$sid);
        $this->db->where('subject_id',$subject_id);
        $this->db->where('exam_head',$ehid);
        $this->db->update($elog_table,$data_array_update);
        return $this->db->affected_rows();
    }
    
    public function get_session_exam_courses_data($academic_session){
        $sql="SELECT exam_course_data.*,all_exams.exam_title,courses.course,courses.subject_stream FROM exam_course_data LEFT JOIN courses ON exam_course_data.course_id = courses.id LEFT JOIN all_exams ON all_exams.id=exam_course_data.exam_id WHERE exam_course_data.academic_session='$academic_session'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_session_exam_course_data($exam_id,$course_id,$academic_session){
        $sql="SELECT exam_course_data.*,all_exams.exam_title,courses.course,courses.subject_stream FROM exam_course_data LEFT JOIN courses ON exam_course_data.course_id = courses.id LEFT JOIN all_exams ON all_exams.id=exam_course_data.exam_id WHERE exam_course_data.academic_session='$academic_session' AND exam_course_data.exam_id='$exam_id' AND exam_course_data.course_id='$course_id'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_student_subjects($sid,$course_id,$acedemic_session,$student_subjects_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($student_subjects_table);
        $this->db->where('sid',$sid);
        $this->db->where('course_id',$course_id);
        $this->db->where('academic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function update_student_subjects_data($sid,$subject_id,$course_id,$acedemic_session,$student_subjects_table,$data_array_update){
        
        $this->db->where('subject_id',$subject_id);
        $this->db->where("course_id",$course_id);
        $this->db->where("sid",$sid);
        $this->db->where("academic_session",$acedemic_session);
        $this->db->update($student_subjects_table,$data_array_update);
        return $this->db->affected_rows();
    }
    
    public function students_exam_log($sid,$acedemic_session,$elog_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($elog_table);
        $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function student_result($sid,$acedemic_session,$exam_id,$course_id,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('exam_id',$exam_id);
        $this->db->where('course_id',$course_id);
        $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function update_student_exam_data($sid,$exam_id,$course_id,$data_array_update,$table){
        $this->db->where("exam_id",$exam_id);
        $this->db->where("course_id",$course_id);
        $this->db->where("sid",$sid);
        $this->db->update($table,$data_array_update);
        return $this->db->affected_rows();
    }
    
    
    public function get_class_section_result($exam_id,$acedemic_session,$class,$section,$exam_result_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($exam_result_table);
        $this->db->where('exam_id',$exam_id);
        $this->db->where('class',$class);
        $this->db->where('section',$section);
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->order_by('percentage','DESC');
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    
    public function get_hic_data_manual($subject_id,$class,$section,$acedemic_session){
        $table="student_subjects_".$acedemic_session;
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('subject_id',$subject_id);
        $this->db->where('class',$class);
        $this->db->where('section',$section);
        $this->db->where('academic_session',$acedemic_session);
        $this->db->order_by('hy_total_marks','DESC');
        $this->db->limit(1); 
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    
    public function get_students_ac_session_by_course_and_class_new($acedemic_session,$class,$section){
        $sql="SELECT scd.sid,scd.course_id,scd.class,scd.section,scd.roll_no,scd.height,scd.weight,student.student_name,student.father_name,student.mother_name,student.blood_group,student.dob,student.sch_no FROM student_courses_data as scd LEFT JOIN student ON scd.sid=student.id WHERE scd.acedemic_session='$acedemic_session' AND scd.class='$class' AND scd.section='$section' AND student.status=1 ORDER BY scd.roll_no ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_class_teacher_info($class,$section,$acedemic_session){
        $sql="SELECT school_classes_structure.*,school_staff.salutation,school_staff.name,school_staff.signature FROM  school_classes_structure LEFT JOIN school_staff ON school_staff.id=school_classes_structure.class_teacher WHERE school_classes_structure.class='$class' AND school_classes_structure.section='$section' AND school_classes_structure.acedemic_session='$acedemic_session' ";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
}