<?php
 
defined('BASEPATH') OR exit('No direct script access allowed');
 
class Export extends CI_Controller {
    
    // construct
    public function __construct() {
        parent::__construct();
        $this->load->database();
        $this->load->model('ExportModel');
        $this->load->model('WelcomeModel');
        $this->load->model('OpeningBalanceModel');
        $this->load->library('Excel');
        $this->load->library('session');
    }    
    
    
    // public function course_students($course_id){
    //     $course='';
    //     $course_stream='';
    //     $get_course_data=$this->WelcomeModel->get_course_info($course_id);
    //     if($get_course_data){
    //         foreach($get_course_data as $data){
    //             $course=$data['course'];
    //             $course_stream=$data['subject_stream'];
    //         }
    //     }
        
    //     $acedemic_year=$this->session->userdata('ac_year');
    //     $acedemic_session=$this->session->userdata('acedemic_session');
    //   // $course_students=$this->ExportModel->get_acedemic_session_course_students($acedemic_session,$course);
        
    //     $fileName = 'data-'.time().'.xlsx';  
    //     // load excel library
    //     $this->load->library('Excel');
    //     // $listInfo = $this->export->exportList();
    //     $objPHPExcel = new PHPExcel();
    //     $objPHPExcel->setActiveSheetIndex(0);
    //     // $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'accedmic_session');    
    //     // $objPHPExcel->getActiveSheet()->SetCellValue('B1', $acedemic_year);    
    //     // $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'course');
    //     // $objPHPExcel->getActiveSheet()->SetCellValue('D1', $course);
    //     // set Header
    //     $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Sch.No');    
    //     $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Name');
    //     $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Father Name');
    //     $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Mother Name');
    //     $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Mobile No');
    //     $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'DOB');
    //     $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Class');    
    //     $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Section');   
    //     $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Admission Date');
    //     $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Address');
    //     $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Percentage(22-23)');
    //     $rowCount = 2;
    //     $student_table='student';
    //         $get_all_students=$this->WelcomeModel->get_active_data_in_alp_order_new($course_id,$acedemic_session);
    //         if($get_all_students){
    //             foreach($get_all_students as $students){
    //                 $sid=$students['id'];
    //                 $exam_id=4;
    //                 $exam_result_table="exam_result";
    //                 $percentage="";
    //                 $get_student_exam_result=$this->WelcomeModel->get_student_exam_result($sid,$exam_id,$exam_result_table);
    //                 if($get_student_exam_result){
    //                     foreach($get_student_exam_result as $result){
    //                         $percentage=$result['percentage'];
    //                     }
    //                 }
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $students['sch_no']);
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $students['student_name']);
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $students['father_name']);
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $students['mother_name']);
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $students['contact_no']);
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $students['dob']);
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $students['current_class']);
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $students['current_section']);
                                    
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, date('d-m-Y',strtotime($students['admission_date'])));
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, htmlspecialchars_decode($students['address']));
    //                 $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, $percentage);
    //                 $rowCount++;
    //             }
    //         }
    //     $filename = "Class_".$course."_".$course_stream."_". date("d-m-Y H:i:s").".csv";
    //     header('Content-Type: application/vnd.ms-excel'); 
    //     header('Content-Disposition: attachment;filename="'.$filename.'"');
    //     header('Cache-Control: max-age=0'); 
    //     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');  
    //     $objWriter->save('php://output');
    // }
    
    
    public function course_students($course_id){
        $course='';
        $course_stream='';
        $get_course_data=$this->WelcomeModel->get_course_info($course_id);
        if($get_course_data){
            foreach($get_course_data as $data){
                $course=$data['course'];
                $course_stream=$data['subject_stream'];
            }
        }
        
        $acedemic_year=$this->session->userdata('ac_year');
        $acedemic_session=$this->session->userdata('acedemic_session');
       // $course_students=$this->ExportModel->get_acedemic_session_course_students($acedemic_session,$course);
        
        $fileName = 'data-'.time().'.xlsx';  
        // load excel library
        $this->load->library('Excel');
        // $listInfo = $this->export->exportList();
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'accedmic_session');    
        // $objPHPExcel->getActiveSheet()->SetCellValue('B1', $acedemic_year);    
        // $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'course');
        // $objPHPExcel->getActiveSheet()->SetCellValue('D1', $course);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'SID');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Sch.No');    
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Father Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Mother Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Mobile No');
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'DOB');
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Class');    
        $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Section');
        
        
        $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Admission Date');
        $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Address');
        
        $objPHPExcel->getActiveSheet()->SetCellValue('L1', 'Gender');
        $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Category');
        
        
        $objPHPExcel->getActiveSheet()->SetCellValue('N1', 'Percentage(23-24)');
        $objPHPExcel->getActiveSheet()->SetCellValue('O1', 'Roll No');
        $objPHPExcel->getActiveSheet()->SetCellValue('P1', 'Height');
        $objPHPExcel->getActiveSheet()->SetCellValue('Q1', 'Weight');
        $rowCount = 2;
        $student_table='student';
            $get_all_students=$this->WelcomeModel->get_active_data_in_alp_order_new($course_id,$acedemic_session);
            if($get_all_students){
                foreach($get_all_students as $students){
                    $sid=$students['id'];
                    $exam_id=2;
                    $exam_result_table="exam_result";
                    $percentage="";
                    $get_student_exam_result=$this->WelcomeModel->get_student_exam_result($sid,$exam_id,$exam_result_table);
                    if($get_student_exam_result){
                        foreach($get_student_exam_result as $result){
                            $percentage=$result['percentage'];
                        }
                    }
                    $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $students['id']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $students['sch_no']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $students['student_name']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $students['father_name']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $students['mother_name']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $students['contact_no']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $students['dob']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $students['current_class']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, $students['current_section']);
                    $admission_date="";
                    if($students['admission_date']){
                        $admission_date=date('d-m-Y',strtotime($students['admission_date']));    
                    }
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $admission_date);
                    $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, htmlspecialchars_decode($students['address']));
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, htmlspecialchars_decode($students['gender']));
                    $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, htmlspecialchars_decode($students['caste']));
                    
                    $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, $percentage);
                    $objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowCount, $students['roll_no']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('P'.$rowCount, $students['height']);
                    $objPHPExcel->getActiveSheet()->SetCellValue('Q'.$rowCount, $students['weight']);
                    $rowCount++;
                }
            }
        $filename = school_file_name."-Class_".$course."_".$course_stream."_". date("d-m-Y H:i:s").".csv";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
        $objWriter->save('php://output');
    }
    
    public function course_opening_fee($course_id){
        $course_table='courses';
        $course='';
        $subject_stream='';
        $acedemic_session=$this->session->userdata('acedemic_session');
        $get_course_info=$this->WelcomeModel->get_item_data($course_id,$course_table);
        if($get_course_info){
            foreach($get_course_info as $course_info){
                $course=$course_info['course'];
                $subject_stream=$course_info['subject_stream'];
            }
        }
        
        $acedemic_year=$this->session->userdata('ac_year');
        $acedemic_session=$this->session->userdata('acedemic_session');
       // $course_students=$this->ExportModel->get_acedemic_session_course_students($acedemic_session,$course);
        
        $fileName = 'data-'.time().'.xlsx';  
        // load excel library
        $this->load->library('Excel');
        // $listInfo = $this->export->exportList();
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        
        
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'S.Id');    
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Father Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Mobile No');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Opening Baalance');
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Fee Note');    
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Paid Amount'); 
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Balance Opening Fee'); 
        $rowCount = 2;
        $get_student_courses_data=$this->OpeningBalanceModel->get_student_courses_data($course_id,$acedemic_session);
        $s_count=0;
        $expected_fee_total=0;
        $balance_amount_total=0;
        $received_fee_total=0;
        if($get_student_courses_data){
            foreach($get_student_courses_data as $courses_data){
                $expected_course_fee=0;
                $received_fee=0;
                $discount=0;
                $s_count=$s_count + 1;
                $sid=$courses_data['sid'];
                $opening_fee_table='opening_balance';
                $get_opening_expected_balance=$this->OpeningBalanceModel->get_student_opening_fee($sid,$opening_fee_table);
                if($get_opening_expected_balance){
                    foreach($get_opening_expected_balance as $expected_balance){
                                // $d_count=$d_count + 1;
                        $expected_course_fee=$expected_course_fee + $expected_balance['opening_fee_amount'];
                        $get_received_fee=$this->OpeningBalanceModel->get_received_opening_fee_for_student($sid);
                        if($get_received_fee){
                            foreach($get_received_fee as $fee){
                                $received_fee=$received_fee + $fee['received_amount'];
                                $discount=$discount + $fee['discount'];
                    
                    
                            }
                        }         
                        $student_table='student';
                        $get_student_profile=$this->WelcomeModel->get_item_data($sid,$student_table);
                        if($get_student_profile){
                            foreach($get_student_profile as $profile){
                                $balance=$expected_course_fee - $received_fee;
                                $expected_fee_total=$expected_fee_total + $expected_course_fee;
                                $balance_amount_total=$balance_amount_total + $balance;
                                $received_fee_total=$received_fee_total + $received_fee;
                                    
                                $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $profile['id']);
                                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $profile['student_name']);
                                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $profile['father_name']);
                                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $profile['contact_no']);
                                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $expected_course_fee );
                                $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $expected_balance['fee_note']);
                                $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $received_fee);
                                $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $balance);
                        
                                $rowCount++;
                                /*     $objPHPExcel->getActiveSheet()->SetCellValue("A$rowCount", "Total");*/
                        
                       
                            }
                        }
                    }
                }
            }
            /*$rowCount = $rowCount + 1;*/
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, '');
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, '');
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, '');
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, '');
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $expected_fee_total );
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, '');
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $received_fee_total);
            $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $balance_amount_total);
   
            $filename = "Class_".$course."_".$subject_stream."_". date("d-m-Y H:i:s").".csv";
            header('Content-Type: application/vnd.ms-excel'); 
            header('Content-Disposition: attachment;filename="'.$filename.'"');
            header('Cache-Control: max-age=0'); 
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');  
            $objWriter->save('php://output');    
        }
    
    }
}