<?php
if (!defined('BASEPATH'))
	exit('No direct script access allowed');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Export extends CI_Controller {
	public function __construct() {
        parent::__construct();
        $this->load->database();
        $this->load->helper('url');
        $this->load->model("Result_model");
        $this->lang->load('basic', $this->config->item('language'));
         // redirect if not loggedin
         if(!$this->session->userdata('logged_in')){
             redirect('login');
             
         }
         $logged_in=$this->session->userdata('logged_in');
         if($logged_in['base_url'] != base_url()){
         $this->session->unset_userdata('logged_in');		
         redirect('login');
         }
         
    }    
	public function index() {
     
        $data['page'] = 'export-excel';
        $data['title'] = 'Export Excel data';
        $data['category_list']=$this->result_model->category_list();
		//$this->load->view('student_export', $data);
    }
	public function createExcel() {
        $data['studentData'] = $this->Result_model->export();
		$fileName = 'Qualified_student.xlsx';  
		$studentData = $this->Result_model->export();
		$spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
       	$sheet->setCellValue('A1', 'Email');
        $sheet->setCellValue('B1', 'Name');
        $sheet->setCellValue('C1', 'Reg_No');
        $sheet->setCellValue('D1', 'Branch');
	    $sheet->setCellValue('E1', 'Contact_No');
        $sheet->setCellValue('F1', 'Aptitude Score'); 
        $sheet->setCellValue('G1', 'English Score'); 
        $sheet->setCellValue('H1', 'Reasoning Score');
        $sheet->setCellValue('I1', 'Technical Score');     
        $rows = 2;
        foreach ($studentData as $val){
            $sheet->setCellValue('A' . $rows, $val['email']);
            $sheet->setCellValue('B' . $rows, $val['name']);
            $sheet->setCellValue('C' . $rows, $val['reg_no']);
            $sheet->setCellValue('D' . $rows, $val['branch']);
	        $sheet->setCellValue('E' . $rows, $val['contact_no']);
            $sheet->setCellValue('F' . $rows, $val['apti_score']);
            $sheet->setCellValue('G' . $rows, $val['english_score']);
            $sheet->setCellValue('H' . $rows, $val['reasoning_score']);
            $sheet->setCellValue('I' . $rows, $val['technical_score']);
            $rows++;
        } 
        $writer = new Xlsx($spreadsheet);
		$writer->save("upload/".$fileName);
		header("Content-Type: application/vnd.ms-excel");
        redirect(base_url()."/upload/".$fileName);              
    }    


public function qualifiedexport() {
    $category_list = $this->Result_model->category_list();
    foreach ($category_list as $key => $value1) {
        if($value1['category_name']=="Aptitude"){
            $apti_cutoff= $value1['cut-off'];
        }
        if($value1['category_name']=="Reasoning"){
            $reasoning_cutoff= $value1['cut-off'];
        }
        if($value1['category_name']=="Technical"){
            $tech_cutoff = $value1['cut-off'];
        }
         if($value1['category_name']=="English"){
            $eng_cutoff= $value1['cut-off'];
        }
  }
    $fileName = 'Qualified_student.xlsx';
    $data['studentD'] = $this->Result_model->export_qualified($apti_cutoff,$eng_cutoff,$reasoning_cutoff,$tech_cutoff);  
    $studentD = $this->Result_model->export_qualified($apti_cutoff,$eng_cutoff,$reasoning_cutoff,$tech_cutoff);
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
       $sheet->setCellValue('A1', 'Email');
    $sheet->setCellValue('B1', 'Name');
    $sheet->setCellValue('C1', 'Reg_No');
    $sheet->setCellValue('D1', 'Branch');
    $sheet->setCellValue('E1', 'Contact_No');
    // $sheet->setCellValue('F1', 'Aptitude Score'); 
    // $sheet->setCellValue('G1', 'English Score'); 
    // $sheet->setCellValue('H1', 'Reasoning Score');
    // $sheet->setCellValue('I1', 'Technical Score');     
    $rows = 2;
    foreach ($studentD as $val){
        $sheet->setCellValue('A' . $rows, $val['email']);
        $sheet->setCellValue('B' . $rows, $val['name']);
        $sheet->setCellValue('C' . $rows, $val['reg_no']);
        $sheet->setCellValue('D' . $rows, $val['branch']);
        $sheet->setCellValue('E' . $rows, $val['contact_no']);
        // $sheet->setCellValue('F' . $rows, $val['apti_score']);
        // $sheet->setCellValue('G' . $rows, $val['english_score']);
        // $sheet->setCellValue('H' . $rows, $val['reasoning_score']);
        // $sheet->setCellValue('I' . $rows, $val['technical_score']);
        $rows++;
    } 
    $writer = new Xlsx($spreadsheet);
    $writer->save("upload/".$fileName);
    header("Content-Type: application/vnd.ms-excel");
    redirect(base_url()."/upload/".$fileName);              
}    


public function exportnegative() {
    $category_list = $this->Result_model->category_list();
    $data['studentDa'] = $this->Result_model->export_negative();
    $studentDa = $this->Result_model->export_negative();
    $fileName = 'Qualified_student.xlsx';
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
       $sheet->setCellValue('A1', 'Email');
    $sheet->setCellValue('B1', 'Name');
    $sheet->setCellValue('C1', 'Reg_No');
    $sheet->setCellValue('D1', 'Branch');
    $sheet->setCellValue('E1', 'Contact_No');
    $sheet->setCellValue('F1', 'Aptitude Score'); 
    $sheet->setCellValue('G1', 'English Score'); 
    $sheet->setCellValue('H1', 'Reasoning Score');
    $sheet->setCellValue('I1', 'Technical Score');     
    $rows = 2;

    foreach($studentDa as $key => $val){
        foreach ($category_list as $key => $value1) {
            if($value1['category_name']=="Aptitude"){
                $aptiscore=($val['apti_score']- ($val['apti_negative']*$value1['negative_mark']));
                
            }
            if($value1['category_name']=="Reasoning"){
                $reasoning_score=($val['reasoning_score']- $val['reasoning_negative']*$value1['negative_mark']);
            }
            if($value1['category_name']=="Technical"){
                $technical_score=($val['technical_score']- ($val['technical_negative']*$value1['negative_mark']));
            }
             if($value1['category_name']=="English"){
                $english_score=($val['english_score']-($val['english_negative']*$value1['negative_mark']));
            }
      }
        $sheet->setCellValue('A' . $rows, $val['email']);
        $sheet->setCellValue('B' . $rows, $val['name']);
        $sheet->setCellValue('C' . $rows, $val['reg_no']);
        $sheet->setCellValue('D' . $rows, $val['branch']);
        $sheet->setCellValue('E' . $rows, $val['contact_no']);
        $sheet->setCellValue('F' . $rows, $aptiscore);
        $sheet->setCellValue('G' . $rows, $english_score);
        $sheet->setCellValue('H' . $rows, $reasoning_score);
        $sheet->setCellValue('I' . $rows, $technical_score);
        $rows++;
    } 
    $writer = new Xlsx($spreadsheet);
    $writer->save("upload/".$fileName);
    header("Content-Type: application/vnd.ms-excel");
    redirect(base_url()."/upload/".$fileName);              
} 
public function export_qualified_negative() {
    $category_list = $this->Result_model->category_list();
    $data['studentDat'] = $this->Result_model->export_qualified_negative();
    $studentDat = $this->Result_model->export_qualified_negative();
    $fileName = 'Qualified_student.xlsx';
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
       $sheet->setCellValue('A1', 'Email');
    $sheet->setCellValue('B1', 'Name');
    $sheet->setCellValue('C1', 'Reg_No');
    $sheet->setCellValue('D1', 'Branch');
    $sheet->setCellValue('E1', 'Contact_No');
    // $sheet->setCellValue('F1', 'Aptitude Score'); 
    // $sheet->setCellValue('G1', 'English Score'); 
    // $sheet->setCellValue('H1', 'Reasoning Score');
    // $sheet->setCellValue('I1', 'Technical Score');     
    $rows = 2;

    foreach($studentDat as $key => $val){
        foreach ($category_list as $key => $value1) {
            if($value1['category_name']=="Aptitude"){
                $apti_cutoff= $value1['cut-off'];
                $aptiscore=($val['apti_score']- ($val['apti_negative']*$value1['negative_mark']));
                
            }
            if($value1['category_name']=="Reasoning"){
                $reasoning_cutoff= $value1['cut-off'];
                $reasoning_score=($val['reasoning_score']- $val['reasoning_negative']*$value1['negative_mark']);
            }
            if($value1['category_name']=="Technical"){
                $tech_cutoff = $value1['cut-off'];
                $technical_score=($val['technical_score']- ($val['technical_negative']*$value1['negative_mark']));
            }
             if($value1['category_name']=="English"){
                $eng_cutoff= $value1['cut-off'];
                $english_score=($val['english_score']-($val['english_negative']*$value1['negative_mark']));
            }
      }
      if($aptiscore>=$apti_cutoff AND $reasoning_score >=$reasoning_cutoff AND $technical_score >=$tech_cutoff AND $english_score >=$eng_cutoff  ){
        $sheet->setCellValue('A' . $rows, $val['email']);
        $sheet->setCellValue('B' . $rows, $val['name']);
        $sheet->setCellValue('C' . $rows, $val['reg_no']);
        $sheet->setCellValue('D' . $rows, $val['branch']);
        $sheet->setCellValue('E' . $rows, $val['contact_no']);
        // $sheet->setCellValue('F' . $rows, $aptiscore);
        // $sheet->setCellValue('G' . $rows, $english_score);
        // $sheet->setCellValue('H' . $rows, $reasoning_score);
        // $sheet->setCellValue('I' . $rows, $technical_score);
        $rows++;
    } }
    $writer = new Xlsx($spreadsheet);
    $writer->save("upload/".$fileName);
    header("Content-Type: application/vnd.ms-excel");
    redirect(base_url()."/upload/".$fileName);              
}      
}
?>