<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
 
require_once APPPATH."/third_party/vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

function read_excel($inputFileName,$inputFileType='Xls'){

	try {

		if(file_exists($file_path)){
			// Create a new Reader of the type defined in $inputFileType
			$reader = IOFactory::createReader($inputFileType);
			
			/** Load $inputFileName to a Spreadsheet Object  **/
			$spreadsheet = $reader->load($inputFileName);
			
			$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

			return $sheetData;
		}else{
			return $inputFileName.' not found';
		}
			
	} catch (ReaderException $e) {
		return $e->getMessage();
	}
}

function read_excel_active_sheet($inputFileName,$inputFileType='Xls'){

	try {

		if(file_exists($inputFileName)){
			// Create a new Reader of the type defined in $inputFileType
			$reader = IOFactory::createReader($inputFileType);
			
			/** Load $inputFileName to a Spreadsheet Object  **/

			$spreadsheet = $reader->load($inputFileName);

			return $spreadsheet->getActiveSheet();

			//return $spreadsheet->getActiveSheet()->getCellByColumnAndRow(2, 2)->getValue();
		}else{
			return $inputFileName.' not found';
		}
			
	} catch (ReaderException $e) {
		return $e->getMessage();
	}
}

function validate_admission_excel($inputFileName,$inputFileType='Xls',$rows_limit=200){
	$sheet_data=read_excel_active_sheet($inputFileName,$inputFileType);

	if(!empty($sheet_data)){

		$row_arrays=array(
            'first_name', 
            'middle_name',
            'last_name',
            'dob',
            'gender',
            'blood_group',
            'aadhar_no',
            'mobile_no',
            'whatsapp_no',
            'email_id',
            '10th_board_name',
            '10th_passing_year',
            '10th_total_marks',
            '10th_percentage',
            '10th_division',
            '12th_board_name',
            '12th_passing_year',
            '12th_total_marks',
            '12th_percentage',
            '12th_division',
            'college_name',
            'college_passing_year',
            'college_total_marks',
            'college_percentage',
            'college_division',
            'father_first_name',
            'father_middle_name',
            'father_last_name',
            'father_occupation',
            'father_mobile_no',
            'father_income',
            'mother_first_name',
            'mother_middle_name',
            'mother_last_name',
            'mother_occupation',
            'mother_mobile_no',
            'mother_income',
            'address',
            'city',
            'district',
            'p.o.',
            'p.s.',
            'state',
            'pin',
            'password'
        );

        $firstColumnIndex = Coordinate::columnIndexFromString('A');
		$lastColumnIndex = Coordinate::columnIndexFromString('AS');

		$firstRow = 1;
		$lastRow = 1;
		$data = [];
		$i=0;
		for($row = $firstRow; $row <= $lastRow; ++$row){
		    for($col = $firstColumnIndex; $col <= $lastColumnIndex; ++$col){
		        $data[$i] = trim($sheet_data->getCellByColumnAndRow($col, $row)->getValue());
		        $i++;
		    }			    
		}

		//print_obj($data);die;

		if(!empty($data)){

			if($row_arrays===$data){

				$firstRow = 2;
				$lastRow=$sheet_data->getHighestRow();
				$exceldata=[];

				if($lastRow<=$rows_limit){
					for($row = $firstRow; $row <= $lastRow; ++$row){
					    for($col = $firstColumnIndex; $col <= $lastColumnIndex; ++$col){
					        $exceldata[$row][$col] = $sheet_data->getCellByColumnAndRow($col, $row)->getValue();
					    }			    
					}

					foreach ($exceldata as $_key => $_value) {
						if(!empty($_value)){
							$_exceldata[]=array_filter($_value);
						}                        
                    }

					return ['validated'=>200,'exceldata'=>$_exceldata];
				}else{
					return ['validated'=>400,'error'=>'More than '.$rows_limit.' rows can not be proceessed at once.'];
				}

					

			}else{
				return ['validated'=>400,'table_rows'=>$table_rows,'error'=>'Excel data format headings are wrong.Please download the sample excel for your reference'];
			}

		}else{
			return ['validated'=>400,'error'=>'Sheet data error'];
		}

	}else{
		return ['validated'=>400,'error'=>'Sheet data error2'];
	}
}