1#@!#!123s

: / home / schoolcampuserp / mdpsteghra.co.in / temp / application / models /
Filename : Examresult_model.php
back
<?php

if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Examresult_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->current_session = $this->setting_model->getCurrentSession();
    }

    /**
     * This funtion takes id as a parameter and will fetch the record.
     * If id is not provided, then it will fetch all the records form the table.
     * @param int $id
     * @return mixed
     */
    public function get($id = null) {
        $this->db->select()->from('exam_results');
        if ($id != null) {
            $this->db->where('id', $id);
        } else {
            $this->db->order_by('id');
        }
        $query = $this->db->get();
        if ($id != null) {
            return $query->row_array();
        } else {
            return $query->result_array();
        }
    }

    /**
     * This function will delete the record based on the id
     * @param $id
     */
    public function remove($id) {
        $this->db->where('id', $id);
        $this->db->delete('exam_results');
    }

    /**
     * This function will take the post data passed from the controller
     * If id is present, then it will do an update
     * else an insert. One function doing both add and edit.
     * @param $data
     */
    public function add($data) {
        if (isset($data['id'])) {
            $this->db->where('id', $data['id']);
            $this->db->update('exam_results', $data);
        } else {
            $this->db->insert('exam_results', $data);
            return $this->db->insert_id();
        }
    }

    public function add_exam_result($data) {
        $this->db->where('exam_schedule_id', $data['exam_schedule_id']);
        $this->db->where('student_id', $data['student_id']);
        $q = $this->db->get('exam_results');
        $result = $q->row();
        if ($q->num_rows() > 0) {
            $this->db->where('id', $result->id);
            $this->db->update('exam_results', $data);
            if ($result->get_marks != $data['get_marks']) {
                return $result->id;
            }
        } else {
            $this->db->insert('exam_results', $data);
            $insert_id = $this->db->insert_id();
            return $insert_id;
        }
        return false;
    }

    public function get_exam_result($exam_schedule_id = null, $student_id = null) {
        $this->db->select()->from('exam_results');
        $this->db->where('exam_schedule_id', $exam_schedule_id);
        $this->db->where('student_id', $student_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            $obj = new stdClass();
            $obj->attendence = 'pre';
            $obj->get_marks = "0.00";
            return $obj;
        }
    }

    public function get_result($exam_schedule_id = null, $student_id = null) {
        $this->db->select()->from('exam_results');
        $this->db->where('exam_schedule_id', $exam_schedule_id);
        $this->db->where('student_id', $student_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            
        }
    }

    public function checkexamresultpreparebyexam($exam_id, $class_id, $section_id) {
        $query = $this->db->query("SELECT count(*) `counter` FROM `exam_results`,exam_schedules,student_session WHERE exam_results.exam_schedule_id=exam_schedules.id and student_session.student_id=exam_results.student_id and student_session.class_id=" . $this->db->escape($class_id) . " and student_session.section_id=" . $this->db->escape($section_id) . " and exam_schedules.session_id=" . $this->db->escape($this->current_session) . " and exam_schedules.exam_id=" . $this->db->escape($exam_id));
        if ($query->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
        return $query->result_array();
    }

    public function getStudentExamResultByStudent($exam_id, $student_id, $exam_schedule) {
        $sql = "SELECT exam_schedules.id as `exam_schedules_id`,exam_results.id as `exam_results_id`,exam_schedules.exam_id,exam_schedules.date_of_exam,exam_schedules.full_marks,exam_schedules.passing_marks,exam_results.student_id,exam_results.get_marks,students.firstname,students.middlename,students.lastname,students.guardian_phone,students.email ,exams.name as `exam_name` FROM `exam_schedules` INNER JOIN exams on exams.id=exam_schedules.exam_id INNER JOIN exam_results ON exam_results.exam_schedule_id=exam_schedules.id INNER JOIN students on students.id=exam_results.student_id WHERE exam_schedules.session_id =" . $this->db->escape($this->current_session) . " and exam_schedules.exam_id =" . $this->db->escape($exam_id) . " and exam_results.student_id =" . $this->db->escape($student_id) . " and exam_schedules.id in (" . $exam_schedule . ") ORDER BY `exam_results`.`id` ASC";

        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function getExamResults($exam_id, $post_exam_group_id, $students,$i,$examination_module = null) {
         //yuvi       
//   $this->load->helper('debug');  // Load the helper

    // clear_logs(); 
    
//echo $exam_id.'=='.$post_exam_group_id.'=='.$i."<br>";
//print_r()."<br>";
//print_r($students)."<br>";
        // $result['i'] = $i;
        // return $result;
    //  log_debug("Inside model - examination_module", $examination_module);
 $result = array('exam_connection' => 0, 'students' => array(), 'exams' => array(), 'exam_connection_list' => array());
 
        // $result['exams']=$students;
        // return $result;
$exam_connection = false;
$exam_weightage_map= array();
$calculate_total_term= array();

$exam_total_term_map=array();
$calculate_exam_blocks = [];
$calculate_grade_map=[];
$exam_connections = $this->examgroup_model->getExamGroupConnectionList($post_exam_group_id);

foreach($exam_connections as $connection){
  
    $exam_weightage_map[$connection->exam_group_class_batch_exams_id] = $connection->exam_weightage;
    $calculate_total_term['exam_group_class_batch_exams_id']=$connection->exam_group_class_batch_exams_id;
}

        //$result['exam_connection_list2'] = $exam_connections;
        //$result['exam2']=$exam_connections;

  

 if($i==4 || $i==2 || $i==6 || $i==8 || $i==3){
     
    $array_exam_id = explode(",", $exam_id);
            // $result['exam2']=$array_exam_id;
            // $result['exam_connection2']=$exam_connections;
            
    if (!empty($exam_connections)) {
        $lastkey = key(array_slice($exam_connections, -1, 1, true));
                //if ($exam_connections[$lastkey]->exam_group_class_batch_exams_id == $exam_id) {
                
        //  log_debug("lastkey in if part",$lastkey);       
        if(in_array($exam_connections[$lastkey]->exam_group_class_batch_exams_id, $array_exam_id)){
             $exam_connection = true;
            $result['exam_connection'] = 1;
         }
     }

 } else{
      if (!empty($exam_connections)) {
         $lastkey = key(array_slice($exam_connections, -1, 1, true));
        //   log_debug("lastkey in else part",$lastkey);  
            if ($exam_connections[$lastkey]->exam_group_class_batch_exams_id == $exam_id) {
                $exam_connection = true;
                $result['exam_connection'] = 1;
            }
        }
    }
    //  $result['exam_connection'] = 1;
 $result['exam_connection_list'] = $exam_connections;

//print_r($students);
//echo $exam_connection;
//var_dump($students);



 foreach ($students as $student_key => $student_value) {
     
     $student = $this->examstudent_model->getExamStudentByID($student_value);
    //  log_debug("student list from ".$student_value,$student);
          //print_r($student)."<br>";
          
    $student['exam_result'] = array();
            //$student['exam_result2'] = array();
            
    if ($exam_connection) {
        
        foreach ($exam_connections as $exam_connection_key => $exam_connection_value) {
            
            $exam_group_class_batch_exam_student = $this->examstudent_model->getStudentByExamAndStudentID($student_value, $exam_connection_value->exam_group_class_batch_exams_id);

            $exam = $this->examgroup_model->getExamByID($exam_connection_value->exam_group_class_batch_exams_id);
            
         $exam_total_term_map[$exam->id] = $exam->exam;
         
            //   log_debug("find exam",$exam);      
            $student['exam_result']['exam_roll_no_' . $exam_connection_value->exam_group_class_batch_exams_id] =  $student['roll_no'];
            if($i==4 || $i==2 || $i==6 || $i==8 || $i==3)
             {
                for($a=0;$a<count($students);$a++){
                //  $student['exam_result']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id][$a] =$this->getStudentResultByExam($exam_id, $students[$a],$i);

                   // $student['exam_result2']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id][$a] =$this->getStudentResultByExam2($exam_id, $students[$a],$i);
                   
                   // lets integrate weitage functionality
                   $calculate_weitage=$this->getStudentResultByExam($exam_id, $students[$a],$i);
               
                    foreach($calculate_weitage as $key =>$value){
                        
                //  log_debug("calculate_weitage value",$value);
                         $exam_group_class_batch_exams_id_result=$value->exam_group_class_batch_exams_id;
                   
                            
                        if(isset($exam_weightage_map[$exam_group_class_batch_exams_id_result])){
                            $weightage=$exam_weightage_map[$exam_group_class_batch_exams_id_result];
                   
                            if($weightage != 0.00){
                                $weightage_factor=$weightage / 100;
                                //keep the originl marks
                              $calculate_weitage[$key]->original_max_marks =$value->max_marks;
                               $calculate_weitage[$key]->original_min_marks =$value->min_marks;
                               $calculate_weitage[$key]->original_max_get_marks =$value->get_marks;
                             $calculate_weitage[$key]->applied_weightage =$weightage;
                                 
                                 // Apply weightage to marks
                                $calculate_weitage[$key]->max_marks =round($value->max_marks*$weightage_factor,2);
                                $calculate_weitage[$key]->min_marks =round($value->min_marks*$weightage_factor,2);
                                 $calculate_weitage[$key]->get_marks =round($value->get_marks*$weightage_factor,2);
                            }
                                 // term calculations
                                if(!empty($exam_total_term_map[$exam_group_class_batch_exams_id_result])){
                                    
                                 $calculate_total_term[$exam_total_term_map[$exam_group_class_batch_exams_id_result]."_".$value->name]=$value->max_marks;     
                                // $calculate_total_term[$value->name][]=$value->max_marks;
                                $calculate_exam_blocks[$exam_total_term_map[$exam_group_class_batch_exams_id_result]][$value->name] = $value->max_marks;
                                
                                // map subject with marks
                               
                                
                                 if($student['admission_no'] ){
                                      $calculate_grade_map[$student['admission_no']][$exam_total_term_map[$exam_group_class_batch_exams_id_result]][$value->name]['full_mark']=$value->max_marks;
                                    $calculate_grade_map[$student['admission_no']][$exam_total_term_map[$exam_group_class_batch_exams_id_result]][$value->name]['secured_mark']=$value->get_marks; 
                                 }
                                 
                                }
                                
                        }                   
                    }
                    
                   
                    $student['exam_result']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id][$a]=$calculate_weitage;
                    // log_debug("find the get results", $calculate_weitage['get_marks']);
                
                } 
                  
                //   log_debug("Term calculations",$calculate_total_term);
                   
                  //Add term grouping functionality here
                  
                  if(!empty($calculate_exam_blocks)){
                      
                     if($examination_module == "6a"  || $examination_module == 3){
    
                            $calculate_total_term_grouped = [
                                'term1' => [],
                                'term2' => [],
                                'term3' => [],
                            ];
                            
                     // group the full marks ans secured marks with exam then with terms
                        $calculate_grade_map_term_group =[
                            'term1'=>[],
                            'term2'=>[],
                            'term3' => [],
                            ];
                            
                            // STEP 2: Split exams evenly into term1, term2, term3
                            $exam_names = array_keys($calculate_exam_blocks);
                            $total_exams = count($exam_names);
                            
                            // ISSUE 2: Logic error - only handles 6 exams case
                            // IMPROVED: Handle different numbers of exams
                            if(($total_exams == 3) ||( $total_exams == 6 )) {
                                $exams_per_term = (int) ceil($total_exams / 3);
                                $term1_exams = array_slice($exam_names, 0, $exams_per_term);
                                $term2_exams = array_slice($exam_names, $exams_per_term, $exams_per_term);
                                $term3_exams = array_slice($exam_names, $exams_per_term * 2);
                            } elseif($total_exams == 4) {
                                // Handle cases with fewer than 3 exams
                                $exams_per_term = (int) ceil($total_exams / 2);
                                $term1_exams = array_slice($exam_names, 0, $exams_per_term);
                                $term2_exams = array_slice($exam_names, $exams_per_term);
                                $term3_exams = [];
         
                            }else{
                                $term1_exams = $exam_names;
                                $term2_exams = [];
                                $term3_exams=[];
                            }
                            
                            // for grade starts
                            foreach ($calculate_grade_map as $exam => $subjects) {
                                $term = 'term3'; // default
                                
                                if(in_array($exam, $term1_exams)){
                                    $term = 'term1';
                                } elseif(in_array($exam, $term2_exams)){
                                    $term = 'term2';
                                }
    
                                foreach ($subjects as $subject => $value) {
                                    $calculate_grade_map_term_group[$term][$exam][$subject] = $value;
                                }
                            }
                            // for grade ends
                            
                            // ISSUE 3: Redundant logic in term assignment
                            // IMPROVED: Cleaner term assignment
                            foreach ($calculate_exam_blocks as $exam => $subjects) {
                                $term = 'term3'; // default
                                
                                if(in_array($exam, $term1_exams)){
                                    $term = 'term1';
                                } elseif(in_array($exam, $term2_exams)){
                                    $term = 'term2';
                                }
                                
                                foreach ($subjects as $subject => $value) {
                                    $calculate_total_term_grouped[$term][$exam][$subject] = $value;
                                }
                            }
                            
                            // STEP 4: Calculate Term Totals
                            $term_totals = [
                                'term1' => 0,
                                'term2' => 0,
                                'term3' => 0,
                                'overall' => 0
                            ];
                            
                            foreach ($calculate_total_term_grouped as $term => $exams) {
                                $term_sum = 0;
                                
                                foreach ($exams as $exam_name => $subjects) {
                                    $marks = array_values($subjects);
                                    $unique_marks = array_unique($marks);
                                    
                                    if (count($unique_marks) === 1) {
                                        // All subjects have same full marks → pick one
                                        $term_sum += $marks[0];
                                    } else {
                                        // Mixed marks → add all unique marks
                                        $term_sum += array_sum($unique_marks);
                                    }
                                }
                                
                                $term_totals[$term] = $term_sum;
                            }
                            
                            $term_totals['overall'] = $term_totals['term1'] + $term_totals['term2'] + $term_totals['term3'];
                            
                            // log_debug("Term Totals", $term_totals);
                            
                            // Add term data to result
                             $result['for_calculate_grade']=$calculate_grade_map_term_group;
                            $result['term_grouped_marks'] = $calculate_total_term_grouped;
                            $result['term_totals'] = $term_totals;
                       
                        }else{
                            
                            $calculate_total_term_grouped = [
                                'term1' => [],
                                'term2' => []
                            ];
                            
                        // group the full marks ans secured marks with exam then with terms
                        $calculate_grade_map_term_group =[
                            'term1'=>[],
                            'term2'=>[],
                            ];
                            
                         // STEP 2: Split exams evenly into term1 and term2
                            $exam_names = array_keys($calculate_exam_blocks);
                            $total_exams = count($exam_names);
                            
                            if ($total_exams <= 3) {
                                $term1_exams = $exam_names;
                                $term2_exams = [];
                            } elseif( $examination_module== 8 && $total_exams == 4){
                                $term1_exams = $exam_names;
                                $term2_exams = [];
                            } else {
                                $split_index = (int) ceil($total_exams / 2);
                                $term1_exams = array_slice($exam_names, 0, $split_index);
                                $term2_exams = array_slice($exam_names, $split_index);
                            }
                            
                            //structure the exam with terms for the
                            
                         foreach ($calculate_grade_map['admission_no'] as $exam => $subjects) {
                                $term = in_array($exam, $term1_exams) ? 'term1' : 'term2';
    
                                foreach ($subjects as $subject => $value) {
                                    $calculate_grade_map_term_group[$term][$exam][$subject] = $value;
                                }
                            }
                        // log_debug("Structured Term-wise Marks for grades", $calculate_grade_map_term_group);
                               
                         // STEP 3: Structure result properly
                            foreach ($calculate_exam_blocks as $exam => $subjects) {
                                $term = in_array($exam, $term1_exams) ? 'term1' : 'term2';
    
                                foreach ($subjects as $subject => $value) {
                                    $calculate_total_term_grouped[$term][$exam][$subject] = $value;
                                }
                            }
                        // log_debug("Structured Term-wise Marks", $calculate_total_term_grouped);
                        
                           // STEP 4: Calculate Term Totals
                            $term_totals = [
                                'term1' => 0,
                                'term2' => 0,
                                'overall' => 0
                            ];
                            // foreach ($calculate_total_term_grouped as $term => $exams) {
                            //     $term_sum = 0;
                            //     $marks=[];
                            //     foreach ($exams as $exam_name => $subjects) {
                            //         $marks = array_values($subjects);
                            //         foreach($subjects as $subject => $mark){
                            //             $marks[]=$mark;
                            //         }
                            //     }
                            //     //  log_debug("for_term_marks", $marks);
                            //      $unique_marks=array_unique($marks);
                            //         if (count($unique_marks) === 1) {
                            //             // All subjects have same full marks → pick one
                            //             $term_sum = $marks[0];
                            //         } else {
                            //             // Mixed marks → add all
                            //             $term_sum += array_sum($marks);
                            //             // foreach($unique_marks as $mark){
                            //             //     $term_sum += $mark;
                            //             // }
                            //         }
                            //     $term_totals[$term] = $term_sum;
                            // }
                            
                            foreach ($calculate_total_term_grouped as $term => $exams) {
                                $term_sum = 0;
    
                                foreach ($exams as $exam_name => $subjects) {
                                    $marks = array_values($subjects);
                                    $unique_marks=array_unique($marks);
                                    if (count($unique_marks) === 1) {
                                        // All subjects have same full marks → pick one
                                        $term_sum += $marks[0];
                                    } else {
                                        // Mixed marks → add all
                                        $term_sum += array_sum($unique_marks);
                                    }
                                }
    
                                $term_totals[$term] = $term_sum;
                            }
    
                            $term_totals['overall'] = $term_totals['term1'] + $term_totals['term2'];
    
                            // log_debug("Term Totals", $term_totals);
                                
                             // Add term data to result
                             $student['for_calculate_grade']=$calculate_grade_map_term_group;
                            $result['term_grouped_marks'] = $calculate_total_term_grouped;
                            $result['term_totals'] = $term_totals;
                        }
                        
                    }
              
             }else{
                
                $student['exam_result']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id] =$this->getStudentResultByExam($exam_id, $student['id'],$i);
                    //  log_debug("what in else of suspect",$student['exam_result']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id]);
                   //$student['exam_result2']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id] =$this->getStudentResultByExam2($exam_id, $student['id'],$i);
             }

          $result['exams']['exam_' . $exam_connection_value->exam_group_class_batch_exams_id] = $exam_total_term_map[$exam_connection_value->exam_group_class_batch_exams_id];
          
            
                    //$result['exam_result3']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id]=$exam;
         }

        //   log_debug("calculate_grade_map",$calculate_grade_map);      
        $result['students'][] = $student;
        
                // unset($exam_connections);
                // unset($student);
                // unset($exam);
                
    } else {                
        $student['exam_roll_no'] = $student['roll_no'];
        
        if($i==4 || $i==2 || $i==6 || $i==8 || $i==3){
            
            for($a=0;$a<count($students);$a++){
                        //echo $exam_id.'=='.$students[$a].'=='.$i."<br>";
                $student['exam_result']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id][$a] =$this->getStudentResultByExam($exam_id, $students[$a],$i);
                        //$student['exam_result2']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id][$a] =$this->getStudentResultByExam2($exam_id, $students[$a],$i);
             }

        }else{
                    //echo $student_key."<br>";
            if($i==1){
                     
                 $student['exam_result'][$student_key] = $this->getStudentResultByExam($exam_id, $students[$student_key],$i);
                        
             }else{
                 
                  $student['exam_result'] = $this->getStudentResultByExam($exam_id, $student['id'],$i);
             }
               // $student['exam_result2'] = $this->getStudentResultByExam2($exam_id, $student['id'],$i);
         }
         
        $result['students'][] = $student;
                // unset($student);
     }
 }

return $result;
}

    // public function getStudentResultByExam2($exam_id, $student_id,$i=null) {
    //     if($i==4 || $i==2){
    //         //$sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.note,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id IN (" . $student_id . ") INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id IN (" . $exam_id . ")";
        
    //          $sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.note,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id IN (" . $student_id . ") INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id IN (" . $exam_id . ") ORDER BY `exam_group_class_batch_exams_id` ASC";
    //     //$query = $this->db->query($sql);
    //         //return $query->result();
    //         $query = $this->db->last_query($sql);
    
    // // Echo or log the query for debugging purposes
    // return $query;
    //         //return $this->db->last_query();

    //     }else{
    //          $sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.note,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id=" . $this->db->escape($student_id) . " INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id=" . $this->db->escape($exam_id);
    //     $query = $this->db->query($sql);
    //     //return $query->result();
    //     return $this->db->last_query();

    //     }
    // }

    public function getStudentResultByExam($exam_id, $student_id,$i=null) {
        if($i==4 || $i==2 || $i==6 || $i==8 || $i==3){
        $sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.note,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id IN (" . $student_id . ") INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id IN (" . $exam_id . ") ORDER BY `exam_group_class_batch_exam_subjects`.`date_from` ASC,`exam_group_class_batch_exam_subjects`.`time_from` ASC";//`exam_group_class_batch_exam_subjects`.`id` ASC
        //`exam_group_class_batch_exams_id`
    //   $sql = "SELECT exam_group_class_batch_exam_subjects.*, exam_group_exam_results.id as `exam_group_exam_results_id`, exam_group_exam_results.attendance, exam_group_exam_results.get_marks, exam_group_exam_results.note, subjects.name, subjects.code 
    //     FROM `exam_group_class_batch_exam_subjects` 
    //     INNER JOIN exam_group_exam_results ON exam_group_exam_results.exam_group_class_batch_exam_subject_id = exam_group_class_batch_exam_subjects.id 
    //     INNER JOIN exam_group_class_batch_exam_students ON exam_group_exam_results.exam_group_class_batch_exam_student_id = exam_group_class_batch_exam_students.id 
    //                                                   AND exam_group_class_batch_exam_students.id IN (" . $student_id . ") 
    //     INNER JOIN subjects ON subjects.id = exam_group_class_batch_exam_subjects.subject_id 
    //     WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id IN (" . $exam_id . ") 
    //     ORDER BY `exam_group_class_batch_exam_subjects`.`exam_group_class_batch_exams_id` ASC";

       // $sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.note,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id IN (" . $student_id . ") INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id IN (" . $exam_id . ") ORDER BY `exam_group_class_batch_exams_id` ASC, `subject_id` ASC";
        
        //$sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.note,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id IN (" . $student_id . ") INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id IN (" . $exam_id . ")";
       
        $query = $this->db->query($sql);
       // return $this->db->last_query();
        return $query->result();

        }
        else{
            if($i==1){
                $sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.note,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id IN (" . $student_id . ") INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id IN (" . $exam_id . ") ORDER BY `exam_group_class_batch_exam_subjects`.`date_from` ASC,`exam_group_class_batch_exam_subjects`.`time_from` ASC";//`exam_group_class_batch_exam_subjects`.`id` ASC
        
            }
            else{
            $sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.note,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id=" . $this->db->escape($student_id) . " INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id=" . $this->db->escape($exam_id) ."ORDER BY `exam_group_class_batch_exam_subjects`.`date_from` ASC,`exam_group_class_batch_exam_subjects`.`time_from` ASC";//`exam_group_class_batch_exam_subjects`.`id` ASC
            }
        // $sql = "SELECT exam_group_class_batch_exam_subjects.*, exam_group_exam_results.id as `exam_group_exam_results_id`, exam_group_exam_results.attendance, exam_group_exam_results.get_marks, exam_group_exam_results.note, subjects.name, subjects.code FROM `exam_group_class_batch_exam_subjects` 
        // INNER JOIN exam_group_exam_results ON exam_group_exam_results.exam_group_class_batch_exam_subject_id = exam_group_class_batch_exam_subjects.id 
        // INNER JOIN exam_group_class_batch_exam_students ON exam_group_exam_results.exam_group_class_batch_exam_student_id = exam_group_class_batch_exam_students.id 
        //                                               AND exam_group_class_batch_exam_students.id = " . $this->db->escape($student_id) . " 
        // INNER JOIN subjects ON subjects.id = exam_group_class_batch_exam_subjects.subject_id 
        // WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id = " . $this->db->escape($exam_id) . "
        // ORDER BY `exam_group_class_batch_exam_subjects`.`exam_group_class_batch_exams_id` ASC";

        $query = $this->db->query($sql);
        //return $this->db->last_query();
        return $query->result();
        }
        
    }
    public function searchAttendenceReport($class_id, $section_id, $date) {

       // $sql = "select student_sessions.attendence_id,students.firstname,students.middlename,student_sessions.date,student_sessions.remark,students.roll_no,students.admission_no,students.lastname,student_sessions.attendence_type_id,student_sessions.id as student_session_id, attendence_type.type as `att_type`,attendence_type.key_value as `key` from students ,(SELECT student_session.id,student_session.student_id ,IFNULL(student_attendences.date, 'xxx') as date,student_attendences.remark, IFNULL(student_attendences.id, 0) as attendence_id,student_attendences.attendence_type_id FROM `student_session` LEFT JOIN student_attendences ON student_attendences.student_session_id=student_session.id  and student_attendences.date=" . $this->db->escape($date) . " where  student_session.session_id=" . $this->db->escape($this->current_session) . " and student_session.class_id=" . $this->db->escape($class_id) . " and student_session.section_id=" . $this->db->escape($section_id) . ") as student_sessions   LEFT JOIN attendence_type ON attendence_type.id=student_sessions.attendence_type_id where student_sessions.student_id=students.id  and students.is_active = 'yes' ";
// Assuming $date is an array of dates
// $date = array('2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04', '2024-01-05', '2024-01-06', '2024-01-07', '2024-01-08', '2024-01-09', '2024-01-10', '2024-01-11', '2024-01-12', '2024-01-13', '2024-01-14', '2024-01-15', '2024-01-16', '2024-01-17', '2024-01-18', '2024-01-19', '2024-01-20', '2024-01-21', '2024-01-22', '2024-01-23', '2024-01-24', '2024-01-25', '2024-01-26', '2024-01-27', '2024-01-28', '2024-01-29', '2024-01-30', '2024-01-31');

// Convert the date array to a comma-separated string
$dateString = implode(",", $date);

// Construct the SQL query with the date string
//student_attendences.date as att_date,
$sql = "SELECT student_sessions.attendence_id, students.firstname, students.middlename, student_sessions.date, student_sessions.remark, students.roll_no, students.admission_no, students.lastname, student_sessions.attendence_type_id, student_sessions.id AS student_session_id, attendence_type.type AS `att_type`, attendence_type.key_value AS `key` FROM students, (SELECT student_session.id, student_session.student_id, IFNULL(student_attendences.date, 'xxx') AS date, student_attendences.remark, IFNULL(student_attendences.id, 0) AS attendence_id, student_attendences.attendence_type_id FROM `student_session` LEFT JOIN student_attendences ON student_attendences.student_session_id = student_session.id AND student_attendences.date IN (" . $dateString . ") WHERE student_session.session_id = " . $this->db->escape($this->current_session) . " AND student_session.class_id = " . $this->db->escape($class_id) . " AND student_session.section_id = " . $this->db->escape($section_id) . ") AS student_sessions LEFT JOIN attendence_type ON attendence_type.id = student_sessions.attendence_type_id WHERE student_sessions.student_id = students.id AND students.is_active = 'yes'";

        $query = $this->db->query($sql);
        //return $this->db->last_query();
        return $query->result_array();
    }
    public function getStudentExamResults($exam_id, $post_exam_group_id, $exam_group_class_batch_exam_student_id, $student_id) {

        $result = array('exam_connection' => 0, 'result' => array(), 'exams' => array(), 'exam_connection_list' => array());
        $exam_connection = false;
        $exam_connections = $this->examgroup_model->getExamGroupConnectionList($post_exam_group_id);
        if (!empty($exam_connections)) {
            $lastkey = key(array_slice($exam_connections, -1, 1, true));
            if ($exam_connections[$lastkey]->exam_group_class_batch_exams_id == $exam_id) {
                $exam_connection = true;
                $result['exam_connection'] = 1;
            }
        }
        $result['exam_connection_list'] = $exam_connections;
        if ($exam_connection) {
            $new_array = array(); 
			
            foreach ($exam_connections as $exam_connection_key => $exam_connection_value) {

                $exam_group_class_batch_exam_student = $this->examstudent_model->getStudentByExamAndStudentID($student_id, $exam_connection_value->exam_group_class_batch_exams_id);
				
                $exam = $this->examgroup_model->getExamByID($exam_connection_value->exam_group_class_batch_exams_id);
				if(!empty($exam_group_class_batch_exam_student->id)){
                $result['exam_result']['exam_result_' . $exam_connection_value->exam_group_class_batch_exams_id] 
				= $this->getStudentResultByExam($exam_connection_value->exam_group_class_batch_exams_id, $exam_group_class_batch_exam_student->id);
				}
                $result['exams']['exam_' . $exam_connection_value->exam_group_class_batch_exams_id] = $exam;
            }

        } else {

            $result['exam_connection_list'] = $exam_connections;

            $result['result'] = $this->getStudentResultByExam($exam_id, $exam_group_class_batch_exam_student_id);
        }
        
  
        return $result;
    }

}