<?php
defined('BASEPATH') OR exit('No direct script access allowed');


/**
 * staff Class
 *
 * @package     edusol
 * @subpackage  Report
 * @author      Sabeeh Murtaza
 * @link        http://facebook.com/sabeehking
 */

class Report_model extends CI_Model {
    
    private $is_super;
    private $branch;

    public function __construct()
    {
        parent::__construct();
        $this->is_super = $this->user_model->is_super();
        $this->branch = $this->user_model->getBranch();
    }

 public function StudentWiseHighestGraderep($branch=0,$class=0,$section,$exam=0,$session)
    {
        
        if($branch['id']!='' AND $class!='' AND $exam!='')
        {
            $this->db->select('(LEFT(result.position, (char_length(result.position) - 2)) * 1) as position_refined, result.id as result_id,student.grno,student.dob,student.student_name,student.father_name,result.obtained_marks,result.total_marks, result.position,result.attendance');
            $this->db->from('result');
            $this->db->join('promotion','promotion.id=result.promotion_id');
            $this->db->join('student','student.id=promotion.student_id');
            $this->db->where('student.status',0);
            $this->db->where('promotion.is_active',1);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
            $this->db->where('promotion.session_id',$session);
            $this->db->where('result.position',"1st");
            $this->db->where('promotion.section_id',$section);
            $this->db->where('student.branch',$branch['id']);
            $this->db->where('result.exam_id',$exam);
            $this->db->order_by('position_refined','ASC');
            return $this->db->get()->result_array();
        }
    }
    
      public function StudentWiselowestGraderep($branch=0,$class=0,$section,$exam=0,$session)
    {
        
        if($branch['id']!='' AND $class!='' AND $exam!='')
        {
            $this->db->select('(LEFT(result.position, (char_length(result.position) - 2)) * 1) as position_refined, result.id as result_id,student.grno,student.dob,student.student_name,student.father_name,result.obtained_marks,result.total_marks, result.position,result.attendance');
            $this->db->from('result');
            $this->db->join('promotion','promotion.id=result.promotion_id');
            $this->db->join('student','student.id=promotion.student_id');
            $this->db->where('student.status',0);
            $this->db->where('promotion.is_active',1);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
            $this->db->where('promotion.session_id',$session);
            $this->db->where('promotion.section_id',$section);
            $this->db->where('student.branch',$branch['id']);
            $this->db->where('result.exam_id',$exam);
            $this->db->order_by('position_refined','deSC');
            return $this->db->get()->row_array();
            
        }
    }
   
   
    public function getBranch()
    {
        $this->db->select("id,name"); 
        $this->db->from('branch');
        $this->db->where('is_delete','0');
        if(!$this->is_super)
            $this->db->where('id',$this->branch);
        return $this->db->get()->result_array();
    }

    public function getAllBranches()
    {
        $this->db->select("id,name"); 
        $this->db->from('branch');
        $this->db->where('is_delete','0');
        return $this->db->get()->result_array();
    }
    
     public function getExam($exam)
    {
        $this->db->select("id,name"); 
        $this->db->from('exam');
        $this->db->where('is_delete','0');
        $this->db->where('id',$exam);
        if(!$this->is_super)
            $this->db->where('id',$this->branch);
        return $this->db->get()->result_array();
    }
////////////////////////////////////////////////
//     student attendence report start
////////////////////////////////////////////////
 

////////////////////////////////////////////////
//student attendence report end
///////////////////////////////////////////////



    public function BranchHeader($branch_id = null)
    {
        $this->db->select("*"); 
        $this->db->from('branch');
        $this->db->where('is_delete','0');
        
        if($branch_id){
            $this->db->where('id',$branch_id);
        }
        else{
            $this->db->where('id',$this->branch);
        }
        return $this->db->get()->row_array();
    }
    
    public function StudentWiseExamStudents($branch=0,$class=0,$section,$student=0,$exam=0,$session)
    {
        
        if($branch['id']!='' AND $class!='' AND $student!='' AND $exam!='')
        {
            $this->db->select('(LEFT(result.position, (char_length(result.position) - 2)) * 1) as position_refined, result.id as result_id,student.grno,student.dob,student.student_name,student.father_name,result.obtained_marks,result.total_marks, result.position,result.attendance,result.grade');
            $this->db->from('result');
            $this->db->join('promotion','promotion.id=result.promotion_id');
            $this->db->join('student','student.id=promotion.student_id');
            $this->db->where('student.status',0);
            $this->db->where('promotion.is_active',1);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
            $this->db->where('promotion.session_id',$session);
            $this->db->where('promotion.student_id',$student);
            $this->db->where('promotion.section_id',$section);
            $this->db->where('student.branch',$branch['id']);
            $this->db->where('result.exam_id',$exam);
            $this->db->order_by('position_refined','ASC');
            return $this->db->get()->result();
        }
    }
    
      public function StudentattAbsent($branch,$class,$section,$student,$session){
             $getatt = $this->db->select('count(status_id) as late')
                             ->from('studentatt')
                             ->join('promotion','promotion.student_id = studentatt.student_id')
                             ->join('class','class.class_id= promotion.class_id')
                             ->join('section','section.section_id= promotion.section_id')
                             ->where('promotion.is_active',1)
                             ->where('promotion.class_id',$class)
                              ->where('promotion.session_id',$session)
                             ->where('promotion.section_id',$section)
                             ->where('promotion.student_id',$student)
                             ->where('studentatt.status_id',4)
                             ->where('studentatt.is_deleted',0)
                             ->get()->result();
                             
                return $getatt;       
         }
        
        public function Studentattot($branch,$class,$section,$student,$session)
        {
                 $getatt = $this->db->select('count(status_id) as att')
                             ->from('studentatt')
                             ->join('promotion','promotion.student_id = studentatt.student_id')
                             ->join('class','class.class_id= promotion.class_id')
                             ->join('section','section.section_id= promotion.section_id')
                             ->where('promotion.is_active',1)
                             ->where('promotion.class_id',$class)
                             ->where('promotion.session_id',$session)
                             ->where('promotion.section_id',$section)
                             ->where('promotion.student_id',$student)
                             ->where('studentatt.is_deleted',0)
                             ->get()->result();
                            //   var_dump($getatt);die;
                return $getatt;
        }
     public function Studentatt($branch,$class,$section,$student,$session){
             $getatt = $this->db->select('count(status_id) as att')
                             ->from('studentatt')
                             ->join('promotion','promotion.student_id = studentatt.student_id')
                             ->join('class','class.class_id= promotion.class_id')
                             ->join('section','section.section_id= promotion.section_id')
                             ->where('promotion.is_active',1)
                             ->where('promotion.class_id',$class)
                             ->where('promotion.session_id',$session)
                             ->where('promotion.section_id',$section)
                             ->where('promotion.student_id',$student)
                             ->where('status_id',1)
                             ->where('studentatt.is_deleted',0)
                             ->get()->result();
                            //   var_dump($getatt);die;
                return $getatt;       
         }
     public function StudentWisealllowestGrade($branch=0,$class=0,$section,$exam=0,$session)
    {
        
        if($branch['id']!='' AND $class!='' AND $exam!='')
        {
            $this->db->select('sum(result.obtained_marks) as total_obtain,sum(result.total_marks) as total_mark');
            $this->db->from('result');
            $this->db->join('promotion','promotion.id=result.promotion_id');
            $this->db->join('student','student.id=promotion.student_id');
            $this->db->where('student.status',0);
            $this->db->where('promotion.is_active',1);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
            $this->db->where('promotion.session_id',$session);
            $this->db->where('promotion.section_id',$section);
            $this->db->where('student.branch',$branch['id']);
            $this->db->where('result.exam_id',$exam);
            // $this->db->order_by('position_refined','deSC');
            return $this->db->get()->result();
            
        }
    }
    

 public function StudentWiselowestGrade($branch=0,$class=0,$section,$exam=0,$session)
    {
        
        if($branch['id']!='' AND $class!='' AND $exam!='')
        {
            $this->db->select('(LEFT(result.position, (char_length(result.position) - 2)) * 1) as position_refined, result.id as result_id,student.grno,student.dob,student.student_name,student.father_name,result.obtained_marks,result.total_marks, result.position,result.attendance');
            $this->db->from('result');
            $this->db->join('promotion','promotion.id=result.promotion_id');
            $this->db->join('student','student.id=promotion.student_id');
            $this->db->where('student.status',0);
            $this->db->where('promotion.is_active',1);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
            $this->db->where('promotion.session_id',$session);
            $this->db->where('promotion.section_id',$section);
            $this->db->where('student.branch',$branch['id']);
            $this->db->where('result.exam_id',$exam);
            $this->db->order_by('position_refined','deSC');
            return $this->db->get()->row_array();
            
        }
    }
    
public function StudentWiseHighestGrade($branch=0,$class=0,$section,$exam=0,$session)
    {
        
        if($branch['id']!='' AND $class!='' AND $exam!='')
        {
            $this->db->select('(LEFT(result.position, (char_length(result.position) - 2)) * 1) as position_refined, result.id as result_id,student.grno,student.dob,student.student_name,student.father_name,result.obtained_marks,result.total_marks, result.position,result.attendance');
            $this->db->from('result');
            $this->db->join('promotion','promotion.id=result.promotion_id');
            $this->db->join('student','student.id=promotion.student_id');
            $this->db->where('student.status',0);
            $this->db->where('promotion.is_active',1);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
            $this->db->where('promotion.session_id',$session);
            $this->db->where('result.position',"1st");
            $this->db->where('promotion.section_id',$section);
            $this->db->where('student.branch',$branch['id']);
            $this->db->where('result.exam_id',$exam);
            $this->db->order_by('position_refined','ASC');
            return $this->db->get()->result();
        }
    }
    
	public function ClassInfo($id=0)
	{
// 		if($id!=0)
// 		{
			$this->db->select("class_id,class.class_name");
			$this->db->from("class");
			$this->db->where("class.is_delete",0);
			$this->db->where("class.class_id",$id);
			return $this->db->get()->row();
		
	}

	public function SectionInfo($id=0)
	{
		if($id!=0)
		{
			$this->db->select("section_id,section.section_name");
			$this->db->from("section");
			$this->db->where("section.is_delete",0);
			$this->db->where("section.section_id",$id);
			return $this->db->get()->row();
		}
	}

	public function SessionInfo($id=0)
	{
// 		if($id!=0)
// 		{
			$this->db->select("id,name");
			$this->db->from("session");
			$this->db->where("is_delete",0);
			$this->db->where("id",$id);
			return $this->db->get()->row();
		
	}

	public function ExamInfo($id=0)
	{
// 		if($id!=0)
// 		{
 			$this->db->select("id,name");
			$this->db->from("exam");
			$this->db->where("is_delete",0);
			$this->db->where("id",$id);
			return $this->db->get()->row();
		
	}

    public function StudentDetails($id=0)
    {
        if($id!=0)
        {
            $this->db->select("student.*,class.class_name,section.section_name"); 
            $this->db->from('student');
            $this->db->join('promotion',"promotion.student_id=student.id");
            $this->db->join('class',"promotion.class_id=class.class_id");
            $this->db->join('section',"promotion.section_id=section.section_id");
            //$this->db->where('student.status','0');
            $this->db->where('promotion.id',$id);
            $this->db->where('promotion.is_delete',"0");
            $this->db->where('class.is_delete',"0");
            $this->db->where('section.is_delete',"0");
            return $this->db->get()->row();
        }
    }

    public function ExamsResult($exams=0,$student=0,$session=0)
    {
        if( !empty($exams) AND !empty($student) AND !empty($session))
        {
            $data = [];
            $exam = json_decode(base64_decode($exams),true);
          //  echo "<pre>";print_r($student);exit;
            unset($exam[0]);
          
            foreach($exam as $key=>$value)
            {
                $this->db->select("result.id,exam.name"); 
                $this->db->from('student');
                $this->db->join('promotion',"promotion.student_id=student.id");
                $this->db->join('result',"result.promotion_id=promotion.id");
                $this->db->join('exam',"exam.id=result.exam_id");
                $this->db->where('promotion.session_id',$session);
                $this->db->where('promotion.student_id',$student);
                $this->db->where('result.exam_id',$value);
                $this->db->where('promotion.is_delete',"0");
                $this->db->where('result.is_delete',"0");
                $data[] = $this->db->get()->row();
                 
            }
             
            return $data;
        }
    }
    
     public function Examscustom2($exams=0,$student=0)
    {
        if( !empty($exams) AND !empty($student))
        {
            $data = [];
           
            $exam = json_decode(base64_decode($exams),true);
           
            foreach($exam as $key=>$value)
            {
                $this->db->select("result.id,exam.name"); 
                $this->db->from('result');
                $this->db->join('exam',"exam.id=result.exam_id");
                $this->db->join('result_subject',"result.id=result_subject.result_id");
                $this->db->where('promotion_id',$student);
                $this->db->where('result.exam_id',$value);
                $this->db->where('result.is_delete',"0");
                $this->db->where('result_subject.subexam_id >',"0");
                $qury= $this->db->get()->row();
                if(!empty($qury))
                {
                  $data[]=$qury;  
                }
                
                 
            }
             
            return $data;
        }
    }

   public function Examscustom($exams=0,$student=0)
    {
        if( !empty($exams) AND !empty($student))
        {
            $data = [];
            $exam = json_decode(base64_decode($exams),true);
      
            foreach($exam as $key=>$value)
            {
                $this->db->select("result.id,exam.name"); 
                $this->db->from('result');
                $this->db->join('exam',"exam.id=result.exam_id");
                $this->db->join('result_subject',"result.id=result_subject.result_id");
                $this->db->where('promotion_id',$student);
                $this->db->where('result.exam_id',$value);
                $this->db->where('result.is_delete',"0");
                //$this->db->where('result_subject.subexam_id',"0");
                $qury= $this->db->get()->row();
                if(!empty($qury))
                {
                  $data[]=$qury;  
                }
                
                 
            }
             
            return $data;
        }
    }
   public function ExamsResult2($exams=0,$student=0)
    {
        if( !empty($exams) AND !empty($student))
        {
            $data = [];
            $exam = json_decode(base64_decode($exams),true);
            unset($exam[0]);
           
            foreach($exam as $key=>$value)
            {
                $this->db->select("result.id,exam.name"); 
                $this->db->from('result');
                $this->db->join('exam',"exam.id=result.exam_id");
                $this->db->where('promotion_id',$student);
                $this->db->where('result.exam_id',$value);
                $this->db->where('result.is_delete',"0");
                $qury= $this->db->get()->row();
                if(!empty($qury))
                {
                  $data[]=$qury;  
                }
                
                 
            }
             
            return $data;
        }
    }
    
     public function ExamsResult2all($exams=0,$student=0)
    {
        if( !empty($exams) AND !empty($student))
        {
            $data = [];
            $exam = json_decode(base64_decode($exams),true);
           // echo "<pre>";print_r($student);exit;
            unset($exam[0]);
           
         foreach($student as $row=>$std)
         {
          foreach($exam as $key=>$value)
            {
                $this->db->select("result.id,exam.name"); 
                $this->db->from('result');
                $this->db->join('exam',"exam.id=result.exam_id");
                $this->db->where_in('promotion_id',$std->id);
                $this->db->where('result.exam_id',$value);
                $this->db->where('result.is_delete',"0");
                $data[] = $this->db->get()->row();
                 
            }
         }
           
             
            return $data;
        }
    }
    
    public function getSessions()
    {
        return $this->db->query("SELECT * FROM `session` WHERE `is_delete`='0'")->result_array();
    }

    public function SectionWiseExamStudents($branch=0,$class=0,$section,$exam=0)
    {
        // if($branch!=0 AND $class!=0  AND $exam!=0)
        // {
           
            $this->db->select('(LEFT(result.position, (char_length(result.position) - 2)) * 1) as position_refined,grade,result.id as result_id,student.sid,student.dob,student.grno,student.student_name,student.father_name,result.obtained_marks,result.total_marks, result.position,result.attendance');
            $this->db->from('result');
            $this->db->join('promotion','promotion.id=result.promotion_id');
            $this->db->join('student','student.id=promotion.student_id');
            //$this->db->where('student.status',0);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
             $this->db->where('promotion.section_id',$section);   
            $this->db->where('student.branch',$branch);
            $this->db->where('result.exam_id',$exam);
            if($class)
            $this->db->where('promotion.class_id',$class);
            if($section)
             $this->db->where('promotion.section_id',$section);
             if($exam)
              $this->db->where('result.exam_id',$exam);
            $this->db->order_by('result.obtained_marks','DESC');
            return $this->db->get()->result();
           
        
    }
    
      public function PositionWiseExamStudents($branch=0,$class=0,$section=0,$exam=0)
    {
        // if($branch!=0 AND $class!=0 AND $section!=0 AND $exam!=0)
        // { 
            
            $pos= array("1st","2nd","3rd");
            $this->db->select('(LEFT(result.position, (char_length(result.position) - 2)) * 1) as position_refined, result.id as result_id,student.sid,student.dob,student.student_name,student.father_name,result.obtained_marks,result.total_marks, result.position,result.attendance');
            $this->db->from('result');
            $this->db->join('promotion','promotion.id=result.promotion_id');
            $this->db->join('student','student.id=promotion.student_id');
            //$this->db->where('student.status',0);
            $this->db->where('promotion.is_delete',0);
            
            if($class)
            {
                $this->db->where('promotion.class_id',$class); 
            }
            if($section)
            {
            $this->db->where('promotion.section_id',$section);
            }
            if($exam)
            {
             $this->db->where('result.exam_id',$exam);
            }
             
           // $this->db->where('promotion.class_id',$class);
          //  $this->db->where('promotion.section_id',$section);
            $this->db->where('student.branch',$branch);
            
          //  $this->db->where('result.exam_id',$exam);
            //$this->db->where_in("result.position",$pos);
            //$this->db->order_by('result.obtained_marks','DESC');
            $this->db->order_by("CAST(SUBSTRING(result.position, 1, LENGTH(result.position) - 2) AS UNSIGNED)", "asc", FALSE);
            
            
            return $this->db->get()->result();
           
        
    }

    // public function SectionWiseStudents($branch=0,$class=0,$section=0,$session=0)
    // {
    //     if($branch!=0 AND $class!=0 AND $section!=0 AND $session!=0)
    //     {
    //         $this->db->select('promotion.id, student.sid, student.student_name, student.father_name');
    //         $this->db->from('student');
    //         $this->db->join('promotion','student.id=promotion.student_id');
    //         //$this->db->where('student.status',0);
    //         $this->db->where('promotion.is_delete',0);
    //         $this->db->where('promotion.class_id',$class);
    //         $this->db->where('promotion.section_id',$section);
    //         $this->db->where('promotion.session_id',$session);
    //         $this->db->where('student.branch',$branch);
    //         $this->db->where('promotion.is_delete',0);
    //         $this->db->where('promotion.is_active',1);
    //         $this->db->where('student.is_delete',0);
    //         $this->db->where('student.status',0);
    //         return $this->db->get()->result();
    //     }

    // }
    
    public function SectionWiseStudents($branch=0,$class=0,$section=0,$session=0)
    {
       
            $this->db->select('promotion.id, student.sid, student.student_name, student.father_name');
            $this->db->from('student');
            $this->db->join('promotion','student.id=promotion.student_id');
            //$this->db->where('student.status',0);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
            $this->db->where('promotion.section_id',$section);
            $this->db->where('promotion.session_id',$session);
            $this->db->where('student.branch',$branch);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.is_active',1);
            $this->db->where('student.is_delete',0);
            $this->db->where('student.status',0);
            
                      if($class)
          $this->db->where('promotion.class_id',$class);
          if($section)
          $this->db->where('promotion.section_id',$section);
          if($session)
          $this->db->where('promotion.session_id',$session);
            return $this->db->get()->result();
        

    }
    
       public function SectionWiseStudents2($branch=0,$class=0,$section=0,$session=0)
    {
        
            $this->db->select('promotion.id, student.sid, student.student_name, student.father_name');
            $this->db->from('student');
            $this->db->join('promotion','student.id=promotion.student_id');
            //$this->db->where('student.status',0);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.class_id',$class);
            $this->db->where('promotion.section_id',$section);
            $this->db->where('promotion.session_id',$session);
            $this->db->where('student.branch',$branch);
            $this->db->where('promotion.is_delete',0);
            $this->db->where('promotion.is_active',1);
            $this->db->where('student.is_delete',0);
             if($class)
          $this->db->where('promotion.class_id',$class);
          if($section)
          $this->db->where('promotion.section_id',$section);
          if($session)
          $this->db->where('promotion.session_id',$session);
             if($branch)
          $this->db->where('student.branch',$branch);
            return $this->db->get()->result();
        }

	public function getSubjectMarks_overall($result=0,$exam=null)
	{
		if($result!=0)
		{
			$this->db->select("rs.total_marks,rs.passing_marks,rs.obtained_marks,rs.subexam_id");
			$this->db->from("result_subject as rs");
            $this->db->join('subject','subject.id=rs.subject_id');
			$this->db->where("rs.is_delete",0)->where('subject.is_deleted',0);
			$this->db->where("rs.result_id",$result);
			//$this->db->where("rs.subexam_id",$exam);
			$this->db->group_by("rs.subject_id");
			return $this->db->get()->result();
		}
	}
	
	public function getSubjectMarks($result=0)
	{
		if($result!=0)
		{
			$this->db->select("rs.total_marks,rs.passing_marks,rs.obtained_marks");
			$this->db->from("result_subject as rs");
            $this->db->join('subject','subject.id=rs.subject_id');
			$this->db->where("rs.is_delete",0)->where('subject.is_deleted',0);
			$this->db->where("rs.result_id",$result);
			return $this->db->get()->result();
		}
	}

	public function getSubjectMark($id=0)
	{
	    
		if($id!=0)
		{
			$this->db->select("rs.total_marks,rs.passing_marks,rs.obtained_marks,rs.paper_date");
			$this->db->from("result_subject as rs");
			$this->db->where("rs.is_delete",0);
			$this->db->where("rs.id",$id);
			return $this->db->get()->row();
		}
	}

	public function getSubjectNames($result=0)
	{
	    
                                         
		if($result!=0)
		{
			$this->db->select("result_subject.id, subject.name,result_subject.passing_marks,result_subject.total_marks,result_subject.subject_id,result_subject.subexam_id");
			$this->db->from("subject");
			$this->db->join("result_subject","result_subject.subject_id=subject.id");
			$this->db->where("result_subject.is_delete",0);
			$this->db->where("subject.is_deleted",0);
	    	//$this->db->where("result_subject.subexam_id",0);
			$this->db->where("result_subject.result_id",$result);
			$this->db->group_by("result_subject.subject_id");
			return $this->db->get()->result();
		}
	}

	public function getClassSubjectNames($class=0,$section=0)
	{
		if($class!=0 AND $section!=0)
		{
			$this->db->select("id,name");
			$this->db->from("subject");
			$this->db->where("is_deleted",0);
			$this->db->where("class_id",$class);
			return $this->db->get()->result();
		}
	}

    public function getHeader($exam,$session)
    {
        return "                            </table>

                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class='super_container' style='margin-top:100px;'>
                <div class='page_break clear'>
                    <div class='watermark_wrapper'>
                        <div class='watermark'></div>
                    </div>
                    <div class='super_wrapper'>
                        <div class='wrapper left2 left'>
                            <div class='header'>
                                <div class='wd-100 clear'>
                                    <div class=' text-left left '>
                                        <img src='http://192.168.10.50/public_projects/SLM_edusol/images/2030233026578e95e4267f4.jpg' style='width:50px;height:50px;'>
                                    </div>
                                    <div class=' text-left right '>
                                        <img src='http://192.168.10.50/public_projects/SLM_edusol/images/13793611495791f3b370099.png' style='width:50px;height:50px;'>
                                    </div>
                                    <div style=''>
                                        <center><h1>Shah Latif Model High School Daharki ®</h1></center>
                                        <center><i><h3 style='color:#000 !important;'>$exam Consolidate Sheet For session $session</h3></i></center>
                                        <center><h3 class='boxed' style='margin-top:0px !important;font-size:23px;'>Result Sheet</h3></center>
                                    </div>
                                </div>
                            
                            </div>
                            <div class='body'>
                                <table class='tbl1'>
                                   
                                ";
    }




    public function get_all_users()
    {
        $this->db->select("id,first_name,last_name,username,email,contact_no"); 
        $this->db->from('admin');
        $this->db->where("is_delete","0");
         $this->db->where("role_id!=","0");
        $query = $this->db->get()->result_array();
        return $query;
    }


  public function getBadDebtors()
    {
       $this->db->select('promotion.id as promotion_id,student.id as student_id,student.student_name,student.status');
       $this->db->join('promotion','promotion.student_id=student.id');
       $this->db->where('student.status!=',0);
       $this->db->where('student.branch',$this->user_model->getBranch());
       $non_active_students= $this->db->get('student')->result_array();

 
       foreach ($non_active_students as $key=> $selected_student) {
           $this->db->select('*');
           $this->db->where('student_id',$selected_student['promotion_id']);
           $this->db->where('is_delete',0);
           $this->db->order_by('id','DESC');
           $non_active_students[$key]['vouchers']=$this->db->get('invoice')->row_array();
       }

       foreach ($non_active_students as $key => $selected_student) {
           if(empty($selected_student['vouchers']))
                unset($non_active_students[$key]);
       }

       return $non_active_students; 

    }
    //   public function get_all_roles()
    // {
    //      $branch=$this->user_model->getbranch();

    //     $this->db->select("title"); 
    //     $this->db->from('role');
    //     $this->db->where("is_delete","0")->where('branch_id',$branch);
    //     $query = $this->db->get()->result_array();
    //     return $query;   
    // }


     public function get_all_roles()
     {
         $branch=$this->user_model->getbranch();
         $this->db->select("first_name,last_name,username,email,contact_no,title"); 
         $this->db->from('admin');
         $this->db->join('role', 'role.id = admin.role_id');
         $this->db->where("admin.is_delete","0");
         $this->db->where("role.is_delete","0")->where('role.branch_id',$branch);
         $query = $this->db->get()->result_array();
         return $query;   
     }


  public function get_all_permissions()
    {
        $this->db->select("first_name,last_name,username,email,contact_no,menu.name as menu, submenu.name as submenu");
        $this->db->from('admin');
        $this->db->join('role', 'role.id = admin.role_id');
        $this->db->join('permission','permission.role_id=role.id');
        $this->db->join('menu', 'menu.id = permission.menu_id');
        $this->db->join('submenu', 'submenu.id = permission.submenu_id');
        $this->db->where("admin.is_delete","0");
        $query = $this->db->get()->result_array();
        return $query;     
    }

 public function getBadDebtorsAmount()
    {   
        $bad_debtors_amount=0;
        $BadDebtors=$this->getBadDebtors();
        //var_dump('<pre>',$BadDebtors);die;
        foreach ($BadDebtors as $selectedBadDebtors) {
            //foreach ($selectedBadDebtors['vouchers'] as $selected_voucher) {
                $fee_pack=$selectedBadDebtors['vouchers']['fee_pack'];
                if($selectedBadDebtors['vouchers']['is_admitted']==1)
                $fee_pack+=$selectedBadDebtors['vouchers']['admin_fee'];
                $bad_debtors_amount+=$fee_pack;
                $sum_add_months=@$this->db->query("select sum(fee) as total_add_months from fee_add_months where invoice_id=".$selectedBadDebtors['vouchers']['id'])->row_array()['total_add_months'];
                $bad_debtors_amount+=$sum_add_months;
            //}
        }
        return $bad_debtors_amount;
    }


 public function All_cashdeposit($year){
        
        $branch=$this->user_model->getbranch();
        $this->db->select("bank_def.b_name,program.name as programename,project.p_name,main_head.name as headname,level_2.name as level2name,level_3.name as level3name,cash_deposit.amount,cash_deposit.date,cash_deposit.submit_by,cash_deposit.id,cash_deposit.description"); 
        $this->db->from('cash_deposit');
        $this->db->join('project', 'project.id=cash_deposit.project_id');
        $this->db->join('bank_def', 'bank_def.id=cash_deposit.bank_id');
        $this->db->join('main_head', 'main_head.id=cash_deposit.main_head_id');
        $this->db->join('level_2', ' level_2.id=cash_deposit.level_2_id');
        $this->db->join('level_3', ' level_3.id=cash_deposit.level_3_id');
        $this->db->join('program','program.id=project.program_id');
        $query = $this->db->where('cash_deposit.is_delete',0)->where('cash_deposit.branch',$branch)->where('LEFT(cash_deposit.date,4)',$year)->get()->result_array();
        return $query;

         }





  public function All_bankrecipt($year)
    {
        $branch=$this->user_model->getbranch();
        $this->db->select("bank_def.b_name,program.name as programename,project.p_name,main_head.name as headname,level_2.name as level2name,level_3.name as level3name,bank_recpt.amount,bank_recpt.date,bank_recpt.from_recpt,bank_recpt.id,bank_recpt.description"); 
        $this->db->from('bank_recpt');
        $this->db->join('project', 'project.id=bank_recpt.project_id');
        $this->db->join('bank_def', 'bank_def.id=bank_recpt.bank_id');
        $this->db->join('main_head', 'main_head.id=bank_recpt.main_head_id');
        $this->db->join('level_2', ' level_2.id=bank_recpt.level_2_id');
        $this->db->join('level_3', ' level_3.id=bank_recpt.level_3_id');
        $this->db->join('program','program.id=project.program_id');
        $query = $this->db->where('bank_recpt.is_delete',0)->where('bank_recpt.branch',$branch)->where('LEFT(bank_recpt.date,4)',$year)->get()->result_array();;

        return $query;     
    }

    public function All_expenses($year)
    {
        $branch=$this->user_model->getbranch();
        $this->db->select("program.name as programename,project.p_name,main_head.name as headname,level_2.name as level2name,level_3.name as level3name,cash_receipt.amount,cash_receipt.date,cash_receipt.id,cash_receipt.description"); 
        $this->db->from('cash_receipt');
        $this->db->join('project', 'project.id=cash_receipt.project_id');
        $this->db->join('main_head', 'main_head.id=cash_receipt.main_head_id');
        $this->db->join('level_2', ' level_2.id=cash_receipt.level_2_id');
        $this->db->join('level_3', ' level_3.id=cash_receipt.level_3_id');
        $this->db->join('program','program.id=project.program_id');
        $query = $this->db->where('cash_receipt.is_delete',0)->where('cash_receipt.branch',$branch)->where('LEFT(cash_receipt.date,4)',$year)->get()->result_array();

        return $query;     
    }


    public function getAllRecords($table,$where) {
        $this->db->select()->from($table)->where($where);
        return $query=$this->db->get()->result_array();
    }

    public function get_fee()
    {
        return $this->db->select()->from('get_fee')->get()->result_array();
    }

    public function get_all_addmissions()
    {
       return $this->db->select()->from('get_all_admissions')->get()->result_array();
    }


    // public function get_total_stdCount()
    // {
    //    return $this->db->query("select COUNT(student_id) as stdTotal,`student`.`sid` AS `sid`,`student`.`student_name` AS `student_name`,`student`.`gender` AS `gender`,`student`.`branch` AS `branch`,`student`.`class_admited` AS `class_admited`,`student`.`date_of_admission` AS `date_of_admission`,`class`.`class_name` AS `class_name`,`section`.`section_name` AS `section_name`,`promotion`.`section_id` AS `section_id`,`promotion`.`class_id` AS `class_id` from (((`promotion` join `student` on((`student`.`id` = `promotion`.`student_id`))) join `section` on((`section`.`section_id` = `promotion`.`section_id`))) join `class` on((`class`.`class_id` = `promotion`.`class_id`))) where ((`promotion`.`is_active` = 1) and (`promotion`.`is_delete` = 0) and (`student`.`is_delete` = 0))")->result_array();
         
        
    // }

    public function getReportData($view,$params,$startdate,$enddate)
        {
            $this->db->select()->from($view);
            $this->db->where($params);
            if($startdate!=""){
             $this->db->where('date_of_admission>=', $startdate);
             }
            if($enddate!=""){
             $this->db->where('date_of_admission <=', $enddate);
            }
            return $this->db->get()->result_array();
        }

    public function getFeeData($view,$params,$startdate,$enddate,$class)
        {
            $this->db->select()->from($view);
            $this->db->where($params);
            if($startdate!=""){
            $this->db->where('date>=', $startdate);
            }
            if($class!=""){
            $this->db->where_in('status',$class);
            }
            if($enddate!="")
             $this->db->where('date <=', $enddate);
            return $this->db->get()->result_array();
        }

    public  function gettotalFee($id,$date)
        {
            //var_dump($id);die();
            $this->db->where('id',$id);
            $result = $this->db->get('invoice')->result_array();
            $total =0;
            $this->db->where('invoice_id',$id);
            $next_month_fee = $this->db->get('fee_add_months')->result_array();
            $total= $total + $next_month_fee[0]['fee'];
           
            foreach ($result as $key => $value) {
                   if ($value['is_admitted'] == 1) {
                       $total  = $total + $value['fee_pack'] + $value['admin_fee']+$value['remaining'];

                   }
                   else{
                        $total  =$total + $value['fee_pack']+$value['remaining'];
                   }
            }

            return $total;


        }



}

