<?php
class NewFeeModel extends CI_Model {
    
    public function get_students_in_alpha_order(){
        $table='student'; 
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->order_by('student_name','ASC');
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_acedemic_session_data($acedemic_session,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->order_by('course_id','ASC');
        $info=$this->db->get()->result_array();
        return $info;        
    }
    
    public function get_balance_fee_invoices($sid,$acedemic_session){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('balance_amount_status',1);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;        
    }
    
    public function get_extra_fee_invoices($sid,$acedemic_session){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        // $this->db->where('fee_type',$fee_type);
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('extra_amount_status',1);
        $info=$this->db->get()->result_array();
        return $info;  
    }
    
    public function get_studdents_current_course_data($sid,$acedemic_session){
        $table='student_courses_data';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_data($table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $info=$this->db->get()->result_array();
        return $info;
    }

    public function get_permission($table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('school_management',1);
        $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 insert_data($data_array,$table){
        $this->db->insert($table,$data_array);
        return $this->db->insert_id();
    }


    public function check_session_exist($y1,$y2){
        $info=array();
        $this->db->select('*');
        $this->db->from('academic_session_details');
        $this->db->where('y1',$y1);
        $this->db->where('y2',$y2);
        $info=$this->db->get()->result_array();
        return $info;    
    }

    public function check_fee_head_exist($acedemic_session,$fee_head,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('title',$fee_head);
        // $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;    
    }
    public function get_fee_heads_session_wise($table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        /*$this->db->where('acedemic_session',$id);*/
        $info=$this->db->get()->result_array();
        return $info;   
    }

    public function get_ac_session_active_fee_heads($table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        // $this->db->where('acedemic_session',$id);
        $this->db->where('status',1);
        $info=$this->db->get()->result_array();
        return $info;   
    }

    public function check_fee_structure_exist($course,$ac_session,$fee_head_id){
        $table='fee_structure';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('course',$course);
        $this->db->where('acedemic_session',$ac_session);
        $this->db->where('fee_head',$fee_head_id);
        $info=$this->db->get()->result_array();
        return $info;
    }

    public function get_fee_structure_for_course($course_id,$ac_session,$fee_head_id){
        $table='fee_structure';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('course_id',$course_id);
        $this->db->where('acedemic_session',$ac_session);
        $this->db->where('fee_head',$fee_head_id);
        $info=$this->db->get()->result_array();
        return $info;   
    }
    
    public function fee_structure($course_id,$ac_session){
        $table='fee_structure';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('course_id',$course_id);
        $this->db->where('acedemic_session',$ac_session);
        $info=$this->db->get()->result_array();
        return $info;
    }

    public function get_fee_data_for($sid,$acedemic_session){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        // $this->db->where('fee_head',$fee_head_id);
        $info=$this->db->get()->result_array();
        return $info;   
    }


    public function course_fee_structure_for_session($acedemic_session,$course){
        $table='fee_structure';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('course_id',$course);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;  
    }


    public function course_fee_structure_for_session_with_head($acedemic_session,$course,$head_id){
        $table='fee_structure';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('course_id',$course);
        $this->db->where('fee_head',$head_id);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;  
    }

    public function update_fee_structure($ac_session,$course_id,$fee_head_id,$data_array_update){
        $table='fee_structure';
        $this->db->where("acedemic_session",$ac_session);
        $this->db->where("course_id",$course_id);
        $this->db->where("fee_head",$fee_head_id);
        $this->db->update($table,$data_array_update);    
        return $this->db->affected_rows();    
    }



    public function student_search($field,$no){
    
        $table='student';    
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where($field,$no);

        $this->db->where('status',1);

        $info=$this->db->get()->result_array();
        return $info;    
    }

    public function check_fee_entry($sid,$schno,$status_1,$status_2,$status_3,$status_4,$status_5,$status_6,$status_7,$status_8,$status_9,$status_10,$status_11,$status_12,$total,$acedemic_session){
        $this->db->select('*');
        $this->db->from('student_fee');
        $this->db->where('sid',$sid);
        $this->db->where('sch_no',$schno);
        $this->db->where('january',$status_1);
        $this->db->where('february',$status_2);
        $this->db->where('march',$status_3);
        $this->db->where('april',$status_4);
        $this->db->where('may',$status_5);
        $this->db->where('june',$status_6);
        $this->db->where('july',$status_7);
        $this->db->where('august',$status_8);
        $this->db->where('september',$status_9);
        $this->db->where('october',$status_10);
        $this->db->where('november',$status_11);
        $this->db->where('december',$status_12);
        $this->db->where('acedemic_session',$acedemic_session);
        return $this->db->get()->num_rows(); 
    }

    public function add_fee($data_array,$table){
        $this->db->insert($table,$data_array);
        return $this->db->insert_id();    
    }

    public function get_fee_invoices($sid,$acedemic_session){
        $info=array();
        $this->db->select('*');
        $this->db->from('student_fee');
        //   $this->db->order_by('fee_no');
        $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }  

    public function get_balance_amount($sid,$fee_type,$acedemic_session){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('fee_type',$fee_type);
        // $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('balance_amount_status',1);
        $info=$this->db->get()->result_array();
        return $info;
    }

    public function get_extra_amount($sid,$fee_type,$acedemic_session){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('fee_type',$fee_type);
        // $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('extra_amount_status',1);
        $info=$this->db->get()->result_array();
        return $info;  
    }

    public function update_balance_amount_status($fee_no){
        $table='student_fee';
        $data_array_update=array("balance_amount_status"=>2);
        $this->db->where("fee_no",$fee_no);
        $this->db->update($table,$data_array_update);    
        return $this->db->affected_rows();      
    }

    public function update_extra_fee_amount_status($fee_no){
        $table='student_fee';
        $data_array_update=array("extra_amount_status"=>2);
        $this->db->where("fee_no",$fee_no);
        $this->db->update($table,$data_array_update);    
        return $this->db->affected_rows();    
    }
    
    

    public function get_invoice_data($fee_no){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('fee_no',$fee_no);
        $info=$this->db->get()->result_array();
        return $info;  
    }
    public function get_invoice_info($fee_no,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('fee_no',$fee_no);
        $info=$this->db->get()->result_array();
        return $info;
    }



    public function fee_invoice($fee_no){
        $this->db->where('fee_no', $fee_no);
        $data = $this->db->get('student_fee');

        $output ="<html>
            <head>";
        $output .='<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
            <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" >
            <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" ></script>
            <link rel="preconnect" href="https://fonts.gstatic.com">
            <link href="https://fonts.googleapis.com/css2?family=Bree+Serif&display=swap" rel="stylesheet"> 
    
        <style>';

        $output .="body{font-family: 'Bree Serif', serif;}";
        $output .='table {
            width: 100%;
            margin:0px;padding:0px;
        }
        th, td {
            padding: 5px;
        }
        table tr{padding:0px;}
        th{font-weight:600;font-size:10px;}
        td{font-weight:600;font-size:10px;}


        </style>
        </head>
        <body>';
        // td, th {
        //   border: 1px solid #dddddd;
        //   text-align: left;
        //   padding: 3px;
        // }
        // tr:nth-child(even) {
        //   background-color: #dddddd;
        // }
        $output .='<div class="conatiner-fluid">
            <div class="row>
            <div class="col-sm-12">';

        foreach($data->result() as $fee){
            $sid =$fee->sid;
            $this->db->where('id', $sid);
            $data1 = $this->db->get('student');
            foreach($data1->result() as $row){
                $output .='<div style="height:50%; border:1px solid red;margin:0px;padding:0px;border-collapse:collapse;">';
        
                $output .='<div style="width:100%;height:100px;border-bottom:1px solid red;">';
        
                $output .='<div style="width:20%;float:left;height:auto;padding:10px;align:right;">';
        
                $output .='<img src="'.base_url().'assets/img/logo.jpg" style="width:auto;height:80px;">';
                $output .='</div>';
        
                $output .='<div style="width:80%;height:100px;float:left;">';
        
                $output .='<p class="text-center" style="font-size:25px;font-weight:700;padding:0px;margin:0px;letter-spacing:1px;height:25px;">'.school_full_name.'</p>';
        
                $output .='<p style="text-align:center;padding:0px;margin:0px;font-size:18px;font-weight:600;height:20px;">'.school_affiliation.'</p>';
        
                $output .='<p style="text-align:center;padding:0px;margin:0px;height:20px;">'.school_address.'</p>';
        
                $output .='<p style="text-align:center;padding:0px;margin:0px;height:20px;">Contact : '.contact1.','.contact2.'</p>';
        
                $output .='</div>';
        
                $output .='</div>';
                $output .='<div style="">';
        
                $output .='<div style="width:100%;height:30px;">';
        
                $output .='<div style="width:70%;float:left;padding:0px;margin:0px;">&nbsp; No.:'.$fee->fee_no.'</div>';
        
                $output .='<div style="width:30%;float:left;padding:0px;margin:0px;"> Date : '.date('d-m-Y',strtotime($fee->fee_date)).'</div>';
                $output .='</div>';
        
                $output .='<div style="margin:0px;padding:0px;width:100%;height:30px;">';
        
                $output .='<div style="width:70%;float:left;padding:0px;margin:0px;">&nbsp; Name. : <span style="text-transform:uppercase;">'.$row->student_name.'</span></div>';
        
                $output .='<div style="width:30%;float:left;padding:0px;margin:0px;"> Class : '.$row->class.' '.$row->section.'</div>';
                $output .='</div>';
        
                $output .='<div style="margin:0px;width:100%;height:30px;">';
        
                $output .='<div style="width:40%;float:left;padding:0px;margin:0px;">&nbsp; Estimate Amount. : '.$fee->estimate_amount.' </div>';
        
                $output .='<div style="width:30%;float:left;padding:0px;margin:0px;">Discount : '.$fee->discount.'</div>';
        
                $output .='<div style="width:30%;float:left;padding:0px;margin:0px;"> Received Amount  : '.$fee->total_amount.'</div>';
        
                $output .='</div>';
        
                $output .='</div>';
        
                // $output .='<div style="width:100%;">';
                // $output .='<div style="width:100%;float:left;">&nbsp; Fee Months :<span style="text-transform:uppercase;"> '.$fee->months.'</span></div>';
                // // $output .='<div style="width:30%;float:left;">Payment Mode : <span style="text-transform:uppercase;">'.$fee->payment_mode.'</span></div>';
                // $output .='</div><br><br>';
        
        
                $acedemic_session=$fee->acedemic_session;
                $course=$row->class;

                $status_4=$fee->april;
                $status_5=$fee->may;
                $status_6=$fee->june;

                $status_7=$fee->july;
                $status_8=$fee->august;
                $status_9=$fee->september;

                $status_10=$fee->october;
                $status_11=$fee->november;
                $status_12=$fee->december;

                $status_1=$fee->january;
                $status_2=$fee->february;
                $status_3=$fee->march;
        // $this->db->where('fee_no', $fee_no);
        $fee_head_data = $this->db->get('fee_heads');

        $output .='<div style="width:100%;">';
        $output .='<table class="table table-bordered" style="margin:0px;">';
        $output .='<thead>';
        $output .='<tr>
        <td></td>
        ';        
        if($status_4 ==1){
            $output.= "<td>Apr</td>"; 
        }
        if($status_5 ==1){
            $output.= "<td>May</td>"; 
        }
        if($status_6 ==1){
            $output.= "<td>Jun</td>"; 
        }
        if($status_7 ==1){
            $output.= "<td>Jul</td>"; 
        }
        if($status_8 ==1){
            $output.= "<td>Aug</td>"; 
        }
        if($status_9 ==1){
            $output.= "<td>Sep</td>"; 
        }
        if($status_10 ==1){
            $output.= "<td>Oct</td>"; 
        }
        if($status_11 ==1){
            $output.= "<td>Nov</td>"; 
        }
        if($status_12 ==1){
            $output.= "<td>Dec</td>"; 
        }
        if($status_1 ==1){
            $output.= "<td>Jan</td>"; 
        }
        if($status_2 ==1){
            $output.= "<td>Feb</td>"; 
        }
        if($status_3 ==1){
            $output.= "<td>Mar</td>"; 
        }
        $output .='</tr>';
        $output .='</thead>';
        $output .='<tbody>';
        // $output .='<th>&nbsp;Month</th>';
        foreach($fee_head_data->result() as $fee_head){
        $output .='<tr>';    
        $output .='<th>'.$fee_head->title.'</th>'; 

        
        
        
        if($status_4 ==1){
        $fee_amount=0;
        // $output .="<tr>";
        // $output.= "<td>&nbsp;April</td>";    
        // foreach($fee_head_data->result() as $fee_head){
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->april == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->april == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
        
        }

        if($status_5 ==1){
        $fee_amount=0;
        
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->may == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->may == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
        
        }
        
        if($status_6 ==1){
        $fee_amount=0;
       
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->june == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->june == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
       
        }
        
        if($status_7 ==1){
        $fee_amount=0;
       
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->july == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->july == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
        
        }
        
         if($status_8 ==1){
        $fee_amount=0;
       
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->august == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->august == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
       
        }
        
        if($status_9 ==1){
        $fee_amount=0;
        
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->september == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->september == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
        
        }
        
        if($status_10 ==1){
        $fee_amount=0;
       
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->october == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->october == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
        
        }
        
        if($status_11 ==1){
        $fee_amount=0;
       
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->november == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->november == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
    
        }
        
        if($status_12 ==1){
        $fee_amount=0;
        
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->december == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->december == 0){
        $output .= "<td>-</td>";    
        }
        }
        }

        }
        
        if($status_1 ==1){
        $fee_amount=0;
        
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->january == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->january == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
       
        }
        
        if($status_2 ==1){
        $fee_amount=0;
       
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
        $this->db->where('fee_head',$head_id);
        $get_fee_structure = $this->db->get('fee_structure');
        if($get_fee_structure){
        foreach($get_fee_structure->result() as $fee_structure){
        if($fee_structure->february == 1){
        $fee_amount = $fee_amount + $fee_structure->amount;
        $output .= "<td>".$fee_structure->amount."</td>";
        }else if($fee_structure->february == 0){
        $output .= "<td>-</td>";    
        }
        }
        }
     
        }
        
        if($status_3 ==1){
        $fee_amount=0;
      
        $head_id=$fee_head->id;
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('course',$course);
                        $this->db->where('fee_head',$head_id);
                        $get_fee_structure = $this->db->get('fee_structure');
                        if($get_fee_structure){
                            foreach($get_fee_structure->result() as $fee_structure){
                                if($fee_structure->march == 1){
                                    $fee_amount = $fee_amount + $fee_structure->amount;
                                    $output .= "<td>".$fee_structure->amount."</td>";
                                }else if($fee_structure->march == 0){
                                    $output .= "<td>-</td>";    
                                }
                            }
                        }
  
                    }
                    $output .='</tr>';
                }
        
                $output .='</tbody>';
                $output .='</table>';
                $output .='<p style="padding:0px;margin:0px;">';
                $output .='<span style="width:33%;float:left;">Estimate Amout : '.$fee->estimate_amount.' ,</span>';
                $output .='<span style="width:33%;float:left;">Received Amout : '.$fee->total_amount.' ,</span>';
                $output .='<span style="width:34%;float:left;">Balance : '.$fee->balance_amount.'.</span>';
                $output.='</p>';
                $output .='</div>';
        
                $output .='</div>';
        
                // $output .='<div style="height:1%;width:100%;margin:0px;padding:0px;">
                // <hr></div>';
        
                $output .='<div style="height:49%; border:1px solid blue;width:100%;margin:0px;padding:0px;border-collapse:collapse;"></div>';
                // $output .='</div></div></div>';
            }
    
        }
        $output .='</body></html>';
        return $output;
    }


    public function check_opening_balance_fee_invoice($sid,$discount,$received_amount,$fee_date,$opening_fee_amount){
        $table='opening_balance_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('discount',$discount);
        $this->db->where('received_amount',$received_amount);
        $this->db->where('fee_date',$fee_date);
        $this->db->where('estimate_amount',$opening_fee_amount);
        $info=$this->db->get()->result_array();
        return $info;      
    }
    
    public function check_fee_structure_exist_for_course_id($course_id,$ac_session,$fee_head_id){
        $table='fee_structure';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('course_id',$course_id);
        $this->db->where('acedemic_session',$ac_session);
        $this->db->where('fee_head',$fee_head_id);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function delete_item($id,$table){
            $this->db->where('id',$id);
            $this->db->delete($table);
            return $this->db->affected_rows(); 
    }
    
    public function update_fee_data($fee_no,$data_array_update){
        $table='student_fee';
        $this->db->where("fee_no",$fee_no);
        $this->db->update($table,$data_array_update);    
        return $this->db->affected_rows(); 
    }
    
    public function get_fee_data_by_desc($fee_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->order_by('fee_no',"DESC");
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_course_students_for_ac_sessionn($course_id,$acedemic_session){
        $student_courses_data_table='student_courses_data';
        $info=array();
        $this->db->select('*');
        $this->db->from($student_courses_data_table);
        $this->db->where('course_id',$course_id);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function update_course_data($sid,$acedemic_session,$data_array_update){
        $table='student_courses_data';
        $this->db->where("sid",$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->update($table,$data_array_update);    
        return $this->db->affected_rows(); 
    }
    
    
    public function get_date_fee_collection($d,$m,$y,$acedemic_session){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('day',$d);
        $this->db->where('month',$m);
        $this->db->where('year',$y);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function today_course_wise_fee_collection($fee_date,$course_id,$acedemic_session){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('fee_date',$fee_date);
        $this->db->where('course_id',$course_id);
        // $this->db->where('year',$y);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function today_course_wise_fee_collection_new($from_date,$to_date,$course_id,$acedemic_session){
        $sql="SELECT fee.* FROM student_fee as fee";
        $sql .=" WHERE fee.acedemic_session='$acedemic_session' AND fee.course_id='$course_id' AND fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_userwise_day_fee_collection($staff_id,$fee_date,$fee_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('fee_date',$fee_date);
        $this->db->where('tid',$staff_id);
        // $this->db->where('fee_type',$fee_type);
        // $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;        
    }
    
    public function get_userwise_date_range_fee_collection($staff_id,$from_date,$to_date){
        $sql="SELECT fee.* FROM student_fee as fee";
        $sql .=" WHERE fee.tid='$staff_id' AND fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_userwise_date_range_opening_fee_collection($staff_id,$from_date,$to_date){
        $sql="SELECT fee.* FROM opening_balance_fee as fee";
        $sql .=" WHERE fee.tid='$staff_id' AND fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_staf_fee_collection_by_date($staff_id,$fee_date,$fee_type,$fee_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('fee_date',$fee_date);
        $this->db->where('tid',$staff_id);
        $this->db->where('fee_type',$fee_type);
        // $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    
    public function get_staff_fee_collection_by_date_range($staff_id,$from_date,$to_date,$fee_type,$fee_table){
        // $info=array();
        // $this->db->select('*');
        // $this->db->from($fee_table);
        // $this->db->where('fee_date',$fee_date);
        // $this->db->where('tid',$staff_id);
        // $this->db->where('fee_type',$fee_type);
        // // $this->db->where('acedemic_session',$acedemic_session);
        // $info=$this->db->get()->result_array();
        // return $info;
        
        
        $sql="SELECT fee.* FROM student_fee as fee";
        $sql .=" WHERE fee.tid='$staff_id' AND fee.fee_type='$fee_type' AND fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function day_fee_collection($fee_date,$acedemic_session,$fee_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('fee_date',$fee_date);
        // $this->db->where('tid',$staff_id);
        // $this->db->where('year',$y);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_date_range_fee_collection($from_date,$to_date,$acedemic_session){
        $sql="SELECT fee.* FROM student_fee as fee";
        $sql .=" WHERE fee.acedemic_session='$acedemic_session' AND fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_date_range_transport_fee_collection($from_date,$to_date,$acedemic_session){

        $sql="SELECT fee.*,student.student_name,student.father_name,student.mother_name,student.contact_no,courses.course,courses.subject_stream FROM transport_fee as fee LEFT JOIN student ON student.id=fee.sid LEFT JOIN student_courses_data as scd ON scd.sid=fee.sid LEFT JOIN courses ON courses.id=scd.course_id";
        $sql .=" WHERE fee.acedemic_session='$acedemic_session' AND fee.fee_date BETWEEN '$from_date' AND '$to_date' AND scd.acedemic_session='$acedemic_session'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    
    public function delete_invoice_permanently($fee_no,$table){
        $this->db->where('fee_no',$fee_no);
        $this->db->delete($table);
        return $this->db->affected_rows();
    }
    
    public function check_balance_fee_paid_status($sid,$fee_no,$estimate_fee_amount,$received_fee_amount){
        $fee_table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('sid',$sid);
        $this->db->where('balance_amount_added_refrence',$fee_no);
        $this->db->where('estimate_amount',$estimate_fee_amount);
        $this->db->where('received_amount',$received_fee_amount);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    // public function get_deleted_invoices(){
        
    // }
    
    public function get_balance_fee_invoices_for_fee_no($fee_no,$sid){
        $fee_table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('sid',$sid);
        $this->db->where('balance_amount_added_refrence',$fee_no);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_day_opening_fee_invoices($fee_date){
        $fee_table='opening_balance_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('fee_date',$fee_date);
        $info=$this->db->get()->result_array();
        return $info;        
    }
    
    
    public function get_date_range_opening_fee_invoices($from_date,$to_date){
        $sql="SELECT fee.* FROM opening_balance_fee as fee";
        $sql .=" WHERE fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_day_admission_fee_invoices($fee_date){
        $fee_table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('fee_date',$fee_date);
        $this->db->where('fee_type',"Admission Fee");
        $info=$this->db->get()->result_array();
        return $info;      
    }
    
    public function get_date_range_admission_fee_invoices($from_date,$to_date){
        $sql="SELECT fee.* FROM student_fee as fee";
        $sql .=" WHERE fee.fee_type='Admission Fee' AND fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);
        return $query->result_array();
    }
    
    public function get_day_enquiry_fee_invoices($fee_date){
        $fee_table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('fee_date',$fee_date);
        $this->db->where('fee_type',"Enquiry Fee");
        $info=$this->db->get()->result_array();
        return $info;      
    }
    
    public function get_date_range_enquiry_fee_invoices($from_date,$to_date){
        $sql="SELECT fee.* FROM student_fee as fee";
        $sql .=" WHERE fee.fee_type='Enquiry Fee' AND fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);
        return $query->result_array();
    }
    
    public function get_today_userwise_opening_fee($staff_id,$fee_date,$opening_fee_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($opening_fee_table);
        $this->db->where('fee_date',$fee_date);
        $this->db->where('tid',$staff_id);
        $info=$this->db->get()->result_array();
        return $info;
        
        
    }
    
    public function get_date_range_userwise_opening_fee($staff_id,$from_date,$to_date){
        $sql="SELECT fee.* FROM opening_balance_fee as fee";
        $sql .=" WHERE fee.tid='$staff_id' AND fee.fee_date BETWEEN '$from_date' AND '$to_date'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_students_opening_fee($sid,$acedemic_session){
        $opening_fee_table='opening_balance';
        $info=array();
        $this->db->select('*');
        $this->db->from($opening_fee_table);
        $this->db->where('fee_for_ac_sess',$acedemic_session);
        $this->db->where('sid',$sid);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_opening_fee_invoices($sid,$acedemic_session){
        $opening_fee_table='opening_balance_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($opening_fee_table);
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('sid',$sid);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_opening_fee_invoices_all($sid,$acedemic_session){
        $opening_fee_table='opening_balance_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($opening_fee_table);
        // $this->db->where('acedemic_session',$acedemic_session);
        $this->db->where('sid',$sid);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_student_enquiry_and_admission_fee_invoice_information($enquiry_id){
        $fee_table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('enquiry_id',$enquiry_id);
        // $this->db->where('sid',$sid);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function get_extra_fee_invoices_for_fee_no($fee_no,$sid){
        $fee_table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($fee_table);
        $this->db->where('sid',$sid);
        $this->db->where('extra_amount_used_refrence',$fee_no);
        $info=$this->db->get()->result_array();
        return $info;
    }
    
    public function check_student_rte_status($sid,$acedemic_session){
    $table='rte_students';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info;      
    }
    
    public function get_pagination_data($limit,$start,$table,$acedemic_session){
        $this->db->limit($limit,$start);
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->order_by('fee_no',"DESC");
        $query = $this->db->get($table);
        return $query->result();
    }
    
    public function get_count($acedemic_session,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('acedemic_session',$acedemic_session);
        return $this->db->get()->num_rows();  
    }
    
    public function cy_fee_invoices($acedemic_session,$table){
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        // $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->order_by('fee_no','DESC');
        $info=$this->db->get()->result_array();
        return $info;
    }
    public function get_course_students_combined($course_id,$acedemic_session){
        $sql="SELECT stud_cou.class,stud_cou.section,stud_cou.roll_no,student.student_name,student.father_name,student.id FROM  student_courses_data as stud_cou LEFT JOIN student on student.id= stud_cou.sid WHERE stud_cou.course_id='$course_id' AND stud_cou.acedemic_session ='$acedemic_session' AND student.status='1' ORDER BY student.student_name";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_class_list($courses_table){
        $sql="SELECT DISTINCT course FROM courses ORDER BY id ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function search_invoices($start_date,$end_date,$class,$section,$student_name,$acedemic_session){
        $sql="SELECT DISTINCT fee.fee_no,fee.sid,fee.estimate_amount,fee.received_amount,fee.discount,fee.bill_book_no,fee.student_class,fee.fee_date,stud_co_data.section,student.student_name,student.father_name,courses.subject_stream,courses.course FROM student_fee as fee LEFT JOIN student_courses_data as stud_co_data on fee.sid=stud_co_data.sid LEFT JOIN student on student.id=fee.sid LEFT JOIN courses on stud_co_data.course_id=courses.id ";
        
        $sql.=" WHERE fee.acedemic_session='$acedemic_session' AND stud_co_data.acedemic_session='$acedemic_session' AND fee.fee_date BETWEEN '$start_date' AND '$end_date' ";
        
        if($class !=""){
            $sql.=" AND fee.student_class ='$class'";
        }
        
        if($section !=""){
            $sql.=" AND stud_co_data.section ='$section'";
        }
        
        if($section !=""){
            $sql.=" AND stud_co_data.section ='$section'";
        }
        
        if($student_name !=""){
            $sql.=" AND student.student_name LIKE '%$student_name%' ";
        }
        
        $sql.=" ORDER BY fee.fee_no DESC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    
    public function get_course_sections($course,$acedemic_session){
        $sql="SELECT DISTINCT section FROM student_courses_data WHERE acedemic_session='$acedemic_session' AND class='$course' ORDER BY section ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_student_course_data($sid,$acedemic_session){
        $table='student_courses_data';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $info=$this->db->get()->result_array();
        return $info; 
    }
    
    
    public function get_fee_admins(){
        $sql="SELECT school_staff.* FROM school_staff LEFT JOIN staff_permissions ON staff_permissions.emp_id=school_staff.id WHERE staff_permissions.fee=1 AND school_staff.user_status=1 && school_staff.status=0 ORDER BY school_staff.name ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_student_data_combined($sid,$academic_session){
        $sql="SELECT stud_cou.class as c_class,stud_cou.section as c_section,stud_cou.roll_no,student.student_name,student.father_name,student.id FROM  student_courses_data as stud_cou LEFT JOIN student on student.id= stud_cou.sid WHERE student.id='$sid' AND stud_cou.acedemic_session ='$academic_session'";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_staff_permissons($staff_id,$staff_permission_table){
        $info=array();
        $this->db->select('*');
        $this->db->from($staff_permission_table);
        $this->db->where("emp_id",$staff_id);
        $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_courses_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_course_active_students_combined_data($course_id,$acedemic_session){
        
        $sql="SELECT student.student_name,student.father_name,student.contact_no,scd.course_id FROM student LEFT JOIN student_courses_data as scd ON scd.sid=student.id WHERE scd.acedemic_session='$acedemic_session' AND scd.course_id='$course_id' AND student.status='active' ORDER BY student.student_name ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_student_suggestions_list($keyword,$acedemic_session){
        // $sql="SELECT student.student_name,student.father_name,student.contact_no,scd.course_id FROM student LEFT JOIN student_courses_data as scd ON scd.sid=student.id WHERE scd.acedemic_session='$acedemic_session' AND scd.course_id='$course_id' AND student.status=1 ORDER BY student.student_name ASC";
        
        $sql="SELECT s.id,s.student_name,s.father_name,s.contact_no,scd.class,scd.section FROM student AS s LEFT JOIN student_courses_data AS scd ON scd.sid=s.id WHERE scd.acedemic_session=$acedemic_session AND s.student_name like '%$keyword%' ORDER BY s.student_name LIMIT 0,6";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    
    public function get_student_information($sid,$acedemic_session){
        // $sql="SELECT student.student_name,student.father_name,student.contact_no,scd.course_id FROM student LEFT JOIN student_courses_data as scd ON scd.sid=student.id WHERE scd.acedemic_session='$acedemic_session' AND scd.course_id='$course_id' AND student.status=1 ORDER BY student.student_name ASC";
        
        $sql="SELECT s.id,s.student_name,s.father_name,s.contact_no,scd.class,scd.section,s.status,s.status_description FROM student AS s LEFT JOIN student_courses_data AS scd ON scd.sid=s.id WHERE scd.acedemic_session=$acedemic_session AND s.id= $sid";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_courses(){
        $sql="SELECT DISTINCT course FROM courses ORDER BY id ASC";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function filter_students_for_academic_record($fsar_student_name,$fsar_sid,$fsar_course,$fsar_section,$fsar_mobile_number,$acedemic_session){
        $sql="SELECT s.id,s.student_name,s.father_name,s.contact_no,scd.class,scd.section FROM student AS s LEFT JOIN student_courses_data AS scd ON scd.sid=s.id WHERE scd.acedemic_session=$acedemic_session";
        if($fsar_sid !="" && $fsar_sid !=0){
            $sql.=" AND s.id=$fsar_sid ";
        }
        
        if($fsar_student_name !=""){
            $sql.=" AND s.student_name like '%$fsar_student_name%' ";
        }
        
        if($fsar_course !=""){
            $sql.=" AND scd.class='$fsar_course' ";
        }
        
        if($fsar_section !=""){
            $sql.=" AND scd.section='$fsar_section' ";
        }
        
        if($fsar_mobile_number !=""){
            $sql.=" AND s.contact_no=$fsar_mobile_number ";
        }
        $sql.=" ORDER BY s.student_name ";
        
        
        
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function student_rte_data($sid){
        // $table='';
        // $this->db->where('sid',$sid);
        $sql="SELECT DISTINCT sid FROM rte_students WHERE sid=$sid ";
        $query=$this->db->query($sql);        
        return $query->result_array();
    }
    
    public function get_students_ac_session_by_course_and_class_new($acedemic_session,$class,$section){
        $sql="SELECT courses.subject_stream,courses.course as course_title,scd.sid,scd.course_id,scd.class,scd.section,scd.roll_no,scd.new_admission,student.student_name,student.father_name,student.dob,student.sch_no,student.status,student.status_description,student.contact_no FROM student_courses_data as scd LEFT JOIN student ON scd.sid=student.id LEFT JOIN courses ON courses.id=scd.course_id WHERE scd.acedemic_session='$acedemic_session' AND scd.class='$class' AND scd.section='$section' AND student.status='active' ORDER BY scd.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_student_fee_invoices($sid,$acedemic_session){
        $table='student_fee';
        $info=array();
        $this->db->select('*');
        $this->db->from($table);
        $this->db->where('sid',$sid);
        $this->db->where('acedemic_session',$acedemic_session);
        $this->db->order_by('fee_date','ASC');
        $info=$this->db->get()->result_array();
        return $info;
    }
}
?>