<?php 
require ABSPATH.'app/lib/phpspreadsheet/vendor/autoload.php';
ini_set('max_execution_time', 300);
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Protection;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;


require_once(dirname(__FILE__)."/pages/bulk-user-upload.php");
require_once(dirname(__FILE__)."/pages/manage-gradebook-with-excel.php");
require_once(dirname(__FILE__)."/pages/manage-mock-gradebook-with-excel.php");
define('_IRESET', 3);
define('_JRESET', 9);

function create_gradebook_for_all_class($args) {
	ini_set('memory_limit', '256M');
	$term = $args['term'];
	$class = $args['class'];
	if(!$args['category']) {
		$category_mapping = get_option("category_mapping");
		if(!$category_mapping) {
			return false;
		}
		$cats = $category_mapping[$class];
	} else {
		$cats = [$args['category']];
	}
	$user = new user;
	$grading = new grading;
	$modes = $grading->get_mode_of_scoring();
	$grades = $grading->get_grades();
	$term_name = get_full_term_name($term);
	$session = Session::getInstance();
	$zip_path = ABSPATH.UPLOADFOLDER."/Result Templates/";
	if(!file_exists($zip_path)) {
        mkdir($zip_path, 0777, true);
    }
	$zip_name = $zip_path."e-Gradebook-".str_replace("/", "", get_class_name($args['class'], $args['category'])."-".$term_name)."-".$session->id.".zip"; 
	if(file_exists($zip_name)) {
		unlink($zip_name);
	}
	// Create new zip class 
	$zip = new ZipArchive; 
	   
	if($zip -> open($zip_name, ZipArchive::CREATE ) === TRUE) { 
	    
		foreach ($cats as $category) {
			// Make category directory
			$class_name = get_class_name($class, $category);
			$file_path = ABSPATH.UPLOADFOLDER.'/Result Templates/'.str_replace('/', '', $term_name).'/'.str_replace('/', '', $class_name).'/';
			if(!file_exists($file_path)) {
		        mkdir($file_path, 0777, true);
		    }
			$class_courses = get_class_courses($class, $category);
			$students = eedu_portal_get_class_students($class, $category, $term);
			if(!_count($class_courses)) {
				continue;
			}
			if(!_count($students)) {
				continue;
			}
			foreach ($class_courses as $course_id => $course_name) {
				set_time_limit(200);
				$file = eedu_create_resultsheet_template([
					'class' => $class,
					'category' => $category,
					'course' => $course_id,
					'term' => $term,
					'students' => $students,
					'modes' => $modes,
					'grades' => $grades,
					'class_name' => $class_name,
					'term_name' => $term_name,
					'file_path' => $file_path
				]);
				if(is_file($file)) { 
					set_time_limit(200);
					$new_name = str_replace(ABSPATH.UPLOADFOLDER."/Result Templates/", "", $file);
		            $zip->addFile($file, $new_name); 
		        }
			}
		}
		$zip->close();
		deleteDir(ABSPATH.UPLOADFOLDER.'/Result Templates/'.str_replace('/', '', $term_name).'/');
	} else {
		return ["status" => false, "file" => $zip_name];
	}
	ini_set('memory_limit', '128M');
	$new_name = str_replace(ABSPATH, "", $zip_name);
	return ["status" => true, "file" => $new_name."?ver=".time()];
}

function eedu_config_worksheet($modes) {
	$excel_password = get_option('excel_password');
	$spreads = new Spreadsheet();
	$spreads->getSecurity()->setLockWindows(true);
	$spreads->getSecurity()->setLockStructure(true);
	$spreads->getSecurity()->setWorkbookPassword($excel_password);
	$sheet = $spreads->getActiveSheet();
	$sheet->setTitle('Electronic Grade Book');
	$sheet->getProtection()->setPassword($excel_password);
	$sheet->getProtection()->setSheet(true);
	$sheet->getProtection()->setSort(true);
	$sheet->getProtection()->setInsertRows(true);
	$sheet->getProtection()->setFormatCells(true);
	$sheet->getProtection()->setSelectLockedCells(true);
	$sheet->setCellValue('A8', 'SN');
	$sheet->setCellValue('B8', 'NAME OF STUDENTS');
	$sheet->setCellValue('C8', 'STUDENT ID');
	$sheet->getColumnDimension('A')->setWidth(4);
	$sheet->getColumnDimension('B')->setAutoSize(true);
	$sheet->getColumnDimension('C')->setAutoSize(true);
	$sheet->getStyle('A1')->getFont()->setBold(true);
	$sheet->getStyle('A8')->getFont()->setBold(true);
	$sheet->getStyle('B8')->getFont()->setBold(true);
	$sheet->getStyle('C8')->getFont()->setBold(true);
	$sheet->getStyle('A2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$sheet->getStyle('B2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$sheet->getStyle('C2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$sheet->getStyle('D2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$i = _IRESET;
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$modes_cells = [];
	foreach ($modes as $key => $value) {
		set_time_limit(80);
		// $sheet->getColumnDimension($alphanum[$i])->setAutoSize(true);
		$sheet->setCellValue($alphanum[$i].'8', strtoupper($value['mode_of_scoring']).' ('.$value['max'].'%)');
		$sheet->getStyle($alphanum[$i].'8')->getFont()->setBold(true);
		$modes_cells[] = $alphanum[$i].'8';
		$sheet->getColumnDimension($alphanum[$i])->setWidth(5);
		$i++;
	}
	$sheet->getColumnDimension($alphanum[$i])->setWidth(5);
	$modes_cells[] = $alphanum[$i].'8';
	$sheet->getRowDimension('8')->setRowHeight(120);
	$styleArr = [
		'font' => [
	        'bold' => true,
	    ],
	    'alignment' => [
	        'textRotation' => 90,
	        'readOrder' => \PhpOffice\PhpSpreadsheet\Style\Alignment::READORDER_RTL,
	        'wrapText' => true,
	    ],
	];
	$start_cell = (isset($modes_cells[0])) ? $modes_cells[0] : '';
	$end_cell = (_count($modes_cells)) ? end($modes_cells) : '';
	$sheet->getStyle($start_cell.':'.$end_cell)->applyFromArray($styleArr);
	// $sheet->getColumnDimension($alphanum[$i])->setAutoSize(true);
	$sheet->setCellValue($alphanum[$i].'8', 'TOTAL (100%)');
	$sheet->getStyle($alphanum[$i].'8')->getFont()->setBold(true);
	$i++;
	$sheet->getColumnDimension($alphanum[$i])->setAutoSize(true);
	$sheet->setCellValue($alphanum[$i].'8', 'GRADE');
	$sheet->getStyle($alphanum[$i].'8')->getFont()->setBold(true);
	$sheet->setCellValue('A3', "INSTRUCTIONS:");
	$sheet->setCellValue('A4', "1. Enter only the scores and the totals will be calculated automatically.");
	$sheet->setCellValue('A5', "2. Leave blank if student did not sit for the paper.");
	$sheet->setCellValue('A6', "3. Note that your entry is not updated online until you re-upload this grade book.");
	$sheet->setCellValue('A7', "4. Remember to save at all times. Use CTRL + S to save faster.");
	return ['workbook' => $spreads, 'worksheet' => $sheet];
}

function eedu_create_resultsheet_template($args) {
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$post = new post;
	if(!isset($args['existing'])) {
		$args['existing'] = db_post_unique(['type' => 'result', 'class' => $args['class'], 'category' => $args['category'], 'course' => $args['course'], 'term' => $args['term']]);
	}
	if($args['existing']) {
		if(!is_array($args['existing']['result'])) {
			$args['existing']['result'] = _unserialize($args['existing']['result']);
		}
	}
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$ireset = _IRESET;
	$jreset = _JRESET;
	$students = $args['students'];
	$workbook = eedu_config_worksheet($args['modes']);
	$sheet = $workbook['worksheet'];
	$spreads = $workbook['workbook'];
	
	$user = new user;
	$post = new post;
	$grading = new grading;
	// $sorting = new sorting;
	$modes = $args['modes'];
	$grades = $args['grades'];

	$title = get_course_name($args['course']).' '.$args['class_name'].' '.$args['term_name'].' e-Grade Book';
	$title = strtoupper($title);
	$file = str_replace('/', '-', str_replace(' ', '_', $title)).'.xlsx';
	$file_path = $args['file_path'];
	$sheet->setCellValue('A1', $title);
	$sheet->setCellValue('A2', $args['class']);
	$sheet->setCellValue('B2', $args['category']);
	$sheet->setCellValue('C2', $args['course']);
	$sheet->setCellValue('D2', $args['term']);
	$j = $jreset;
	$grade_cells = array();
	foreach ($students as $student_meta) {
		set_time_limit(80);
		$sheet->setCellValue('A'.$j, $j-8);
		$sheet->setCellValue('B'.$j, strtoupper($student_meta['surname'].' '.$student_meta['other_names']));
		$sheet->setCellValue('C'.$j, strtoupper($student_meta['username']));
		$i = $ireset;
		$first_cell = $alphanum[$i].$j;
		foreach ($modes as $key => $value) {
			set_time_limit(80);
			$last_cell = $alphanum[$i].$j;
			$sheet->getStyle($alphanum[$i].$j)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
			$validation = $sheet->getCell($alphanum[$i].$j)
			    ->getDataValidation();
			$validation->setType(DataValidation::TYPE_WHOLE );
			$validation->setErrorStyle(DataValidation::STYLE_STOP );
			$validation->setAllowBlank(true);
			$validation->setShowInputMessage(true);
			$validation->setShowErrorMessage(true);
			$validation->setErrorTitle('Input error');
			$validation->setError('Number is not allowed!');
			$validation->setPromptTitle('Allowed input');
			$validation->setPrompt('Only numbers between 0 and '.$value['max'].' are allowed.');
			$validation->setFormula1(0);
			$validation->setFormula2($value['max']);
			if($args['existing']) {
				$sheet->setCellValue($alphanum[$i].$j, $args['existing']['result'][$student_meta['id']][$value['mode_of_scoring']]);
			}
			$i++;
		}
		$total_cell = $alphanum[$i].$j;
		$sheet->setCellValue($alphanum[$i].$j, "=SUM(".$first_cell.":".$last_cell.")");
		$i++;
		$statement = '"NA"';
		$count = 0;
		do {
			if(isset($grades[$count])) {
				$statement = 'IF(('.$total_cell.'<'.($grades[$count]['max'] + 0.01).'), "'.$grades[$count]['grade'].'", '.$statement.')';
			}
			$count++;
		} while ($count <= _count($grades));
		$statement = "=".$statement;
		$sheet->setCellValue($alphanum[$i].$j, $statement);
		$grade_cells[] = $alphanum[$i].$j;
		$j++;
	}
	$internalBorder = [
		'borders' => [
	        // 'diagonalDirection' => Borders::DIAGONAL_BOTH,
	        'allBorders' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFDDDDDD']
	        ],
	    ],
	];
	$outlineBorder = [
		'borders' => [
	        'top' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'FFEBEBEB'],
	        ],
	        'bottom' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'FFEBEBEB'],
	        ],
	        'left' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'FFEBEBEB'],
	        ],
	        'right' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'FFEBEBEB'],
	        ],
	    ],
	];

	$sheet->getStyle('A1:'.end($grade_cells))->applyFromArray($internalBorder);
	$sheet->getStyle('A8:'.end($grade_cells))->applyFromArray($outlineBorder);
	$j++;
	$styleStart = 'A'.$j;
	$sheet->setCellValue('B'.$j, "SUMMARY OF GRADES & PERFORMANCE");
	$sheet->getStyle('B'.$j)->getFont()->setBold(true);
	$j++; 
	$sheet->setCellValue('A'.$j, "SN");
	$sheet->setCellValue('B'.$j, "GRADES");
	$sheet->setCellValue('C'.$j, "COUNT");
	$stylesArray = [
	    'font' => [
	        'bold' => true,
	    ],
	    'fill' => [
	        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
	        'rotation' => 90,
	        'startColor' => [
	            'argb' => 'FFCEE6FA',
	        ],
	        'endColor' => [
	            'argb' => 'FFE7E5E5',
	        ],
	    ],
	];
	$sheet->getStyle('A'.$j.':C'.$j)->applyFromArray($stylesArray);
	$n = 1;
	$start_cell = (isset($grade_cells[0])) ? $grade_cells[0] : '';
	$end_cell = (_count($grade_cells)) ? end($grade_cells) : '';
	foreach ($grades as $key => $grade) {
		set_time_limit(80);
		 $j++;
		$sheet->setCellValue('A'.$j, $n);
		$sheet->setCellValue('B'.$j, $grade['grade'].' --- ('.$grade['description'].')');
		$sheet->setCellValue('C'.$j, "=COUNTIF({$start_cell}:{$end_cell}, \"{$grade['grade']}\")");
		$n++;
	}
	$TopBorders = [
	    'borders' => [
	    	'allBorders' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFDDDDDD']
	        ],
	        'top' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFFF0000'],
	        ],
	        'bottom' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFFF0000'],
	        ],
	        'left' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFFF0000'],
	        ],
	        'right' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFFF0000'],
	        ],
	    ],
	];
	$sheet->getStyle($styleStart.':C'.$j)->applyFromArray($TopBorders);
	$writer = new Xlsx($spreads);
	$writer->save($file_path.$file);
	return $file_path.$file;
}

function eedu_update_gradebook_any_class($file) {
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$post = new post;
	$user = new user;
	$ireset = _IRESET;
	$jreset = _JRESET;
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$grading = new grading;
	$mode = $grading->get_mode_of_scoring();
	$spreadsheet = IOFactory::load(ABSPATH.$file);
	$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
	//checks for authentication
	if(trim($sheetData[2]["A"]) === "" || trim($sheetData[2]["B"]) === "" || trim($sheetData[2]["C"]) === "" || trim($sheetData[2]["D"]) === "") {
		return false;
	}
	$class = trim($sheetData[2]["A"]);
	$category = trim($sheetData[2]["B"]);
	$course = trim($sheetData[2]["C"]);
	$term = trim($sheetData[2]["D"]);
	$result = array();
	$existing = db_post_unique(['type' => 'result', 'class' => $class, 'category' => $category, 'course' => $course, 'term' => $term]);

	$j = $jreset;
	do {
		$student_meta = $user->get_user_meta(trim($sheetData[$j]['C']), "username");
		$i = $ireset;
		foreach ($mode as $key => $value) {
			set_time_limit(80);
			$score = trim($sheetData[$j][$alphanum[$i]]);
			$result[$student_meta['id']][$value['mode_of_scoring']] = ($score === "") ? "x" : $score;
			$i++;
		}
		$j++;
	} while ($j <= _count($sheetData));
	$proc['class'] = $class;
	$proc['category'] = $category;
	$proc['course'] = $course;
	$proc['term'] = $term;
	$proc['session'] = get_term_session($term);
	$proc['result'] = serialize($result);
	$proc['result'] = base64_encode($proc['result']);
	$proc['type'] = 'result';
	$proc['status'] = get_option('auto_verify_results') ? 'verified' : 'in process';
	if($existing) {
		$id = $existing['id'];
		$post->update_post_meta($id, $proc);
	} else {
		$id = $post->add_post($proc);
	}
	eedu_portal_reset_broadsheet($class, $category, $term);
	eedu_portal_reset_broadsheet($class, "", $term);
	return get_course_name($course)." ".get_class_name($class, $category)." ".get_full_term_name($term)." Grade Book Updated Successfully";
}

function create_mock_gradebook_for_all_class($args) {
	ini_set('memory_limit', '256M');
	$term = $args['term'];
	$exam = $args['ext_exam'];
	$class = $args['class'];
	if(!$args['category']) {
		$category_mapping = get_option("category_mapping");
		if(!$category_mapping) {
			return false;
		}
		$cats = $category_mapping[$class];
	} else {
		$cats = [$args['category']];
	}
	$user = new user;
	$grading = new grading;
	$grades = $grading->get_grades();
	$term_name = get_full_term_name($term);
	$session = Session::getInstance();
	
	$zip_name = ABSPATH.UPLOADFOLDER."/Result Templates/".str_replace('/', '', $exam)."-Mock-Gradebook-".str_replace("/", "", get_class_name($args['class'], $args['category'])."-".$term_name)."-".$session->id.".zip"; 
	if(file_exists($zip_name)) {
		unlink($zip_name);
	}
	// Create new zip class 
	$zip = new ZipArchive; 
	   
	if($zip -> open($zip_name, ZipArchive::CREATE ) === TRUE) { 
	    
	    $class_name = get_class_name($class, $args['category']);
	    $file_path = ABSPATH.UPLOADFOLDER.'/Result Templates/'.str_replace('/', '', $term_name).'/'.str_replace('/', '', $class_name).'/';
		if(!file_exists($file_path)) {
	        mkdir($file_path, 0777, true);
	    }
		$class_courses = get_class_courses($class, $args['category']);
		$students = eedu_portal_get_class_students($class, $args['category'], $term);
		if(!_count($class_courses)) {
			return false;
		}
		if(!_count($students)) {
			return false;
		}
	    foreach ($class_courses as $course_id => $course_name) {
			set_time_limit(200);
			$file = create_mock_gradebook_template([
				'class' => $class,
				'category' => $args['category'],
				'course' => $course_id,
				'term' => $term,
				'students' => $students,
				'grades' => $grades,
				// 'workbook' => $workbook,
				'class_name' => $class_name,
				'term_name' => $term_name,
				'file_path' => $file_path,
				'exam' => $exam
				]);
			if(is_file($file)) { 
				set_time_limit(200);
				$new_name = str_replace(ABSPATH.UPLOADFOLDER."/Result Templates/", "", $file);
	            $zip->addFile($file, $new_name); 
	        }
		}
		$zip->close();
	} else {
		return false;
	}
	ini_set('memory_limit', '128M');
	return $new_name = str_replace(ABSPATH, "", $zip_name);
}

function eedu_config_mock_worksheet() {
	$excel_password = get_option('excel_password');
	$spreads = new Spreadsheet();
	$spreads->getSecurity()->setLockWindows(true);
	$spreads->getSecurity()->setLockStructure(true);
	$spreads->getSecurity()->setWorkbookPassword($excel_password);
	$sheet = $spreads->getActiveSheet();
	$sheet->setTitle('Electronic Grade Book');
	$sheet->getProtection()->setPassword($excel_password);
	$sheet->getProtection()->setSheet(true);
	$sheet->getProtection()->setSort(true);
	$sheet->getProtection()->setInsertRows(true);
	$sheet->getProtection()->setFormatCells(true);
	$sheet->getProtection()->setSelectLockedCells(true);
	$sheet->setCellValue('A8', 'SN');
	$sheet->setCellValue('B8', 'NAME OF STUDENTS');
	$sheet->setCellValue('C8', 'STUDENT ID');
	$sheet->getColumnDimension('A')->setWidth(4);
	$sheet->getColumnDimension('B')->setAutoSize(true);
	$sheet->getColumnDimension('C')->setAutoSize(true);
	$sheet->setCellValue('D8', 'TOTAL (100%)');
	$sheet->getStyle('D8')->getFont()->setBold(true);
	$sheet->getColumnDimension('D')->setWidth(5);

	$sheet->getStyle('A1')->getFont()->setBold(true);
	$sheet->getStyle('A8')->getFont()->setBold(true);
	$sheet->getStyle('B8')->getFont()->setBold(true);
	$sheet->getStyle('C8')->getFont()->setBold(true);
	$sheet->getStyle('A2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$sheet->getStyle('B2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$sheet->getStyle('C2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$sheet->getStyle('D2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$sheet->getStyle('E2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$i = _IRESET;
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$sheet->getColumnDimension($alphanum[$i])->setWidth(5);
	$modes_cells[] = $alphanum[$i].'8';
	$sheet->getRowDimension('8')->setRowHeight(120);
	$styleArr = [
		'font' => [
	        'bold' => true,
	    ],
	    'alignment' => [
	        'textRotation' => 90,
	        'readOrder' => \PhpOffice\PhpSpreadsheet\Style\Alignment::READORDER_RTL,
	        'wrapText' => true,
	    ],
	];
	$sheet->getStyle('D8')->applyFromArray($styleArr);
	$sheet->setCellValue('E8', 'GRADE');
	$sheet->getStyle('E8')->getFont()->setBold(true);
	$sheet->setCellValue('A3', "INSTRUCTIONS:");
	$sheet->setCellValue('A4', "1. Enter only the scores and the grades will be calculated automatically.");
	$sheet->setCellValue('A5', "2. Leave blank if student did not sit for the paper.");
	$sheet->setCellValue('A6', "3. Note that your entry is not updated online until you re-upload this grade book.");
	$sheet->setCellValue('A7', "4. Remember to save at all times. Use CTRL + S to save faster.");
	return ['workbook' => $spreads, 'worksheet' => $sheet];
}

function create_mock_gradebook_template($args) {
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$post = new post;
	if(!isset($args['existing'])) {
		$args['existing'] = db_post_unique(['type' => 'mock_exam_gradebook', 'class' => $args['class'], 'category' => $args['category'], 'course' => $args['course'], 'term' => $args['term'], 'exam' => $args['exam']]);
	}
	if($args['existing']) {
		if(!is_array($args['existing']['scores'])) {
			$args['existing']['scores'] = unserialize($args['existing']['scores']);
		}
	}
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$ireset = _IRESET;
	$jreset = _JRESET;
	$students = $args['students'];
	$workbook = eedu_config_mock_worksheet();
	$sheet = $workbook['worksheet'];
	$spreads = $workbook['workbook'];
	
	$user = new user;
	$post = new post;
	$grading = new grading;
	// $sorting = new sorting;
	// $modes = $args['modes'];
	$grades = $args['grades'];

	$title = get_course_name($args['course']).' '.$args['class_name'].' '.$args['term_name'].' Mock '.$args['exam'].' e-Grade Book';
	$title = strtoupper($title);
	$file = str_replace('/', '-', str_replace(' ', '_', $title)).'.xlsx';
	$file_path = $args['file_path'];
	$sheet->setCellValue('A1', $title);
	$sheet->setCellValue('A2', $args['class']);
	$category = !$args['category'] ? "" : $args['category'];
	$sheet->setCellValue('B2', $category);
	$sheet->setCellValue('C2', $args['course']);
	$sheet->setCellValue('D2', $args['term']);
	$sheet->setCellValue('E2', $args['exam']);
	$j = $jreset;
	$grade_cells = array();
	foreach ($students as $student_meta) {
		set_time_limit(80);
		$sheet->setCellValue('A'.$j, $j-8);
		$sheet->setCellValue('B'.$j, strtoupper($student_meta['surname'].' '.$student_meta['other_names']));
		$sheet->setCellValue('C'.$j, strtoupper($student_meta['username']));
		$sheet->getStyle('D'.$j)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
		$validation = $sheet->getCell('D'.$j)
		    ->getDataValidation();
		$validation->setType(DataValidation::TYPE_WHOLE );
		$validation->setErrorStyle(DataValidation::STYLE_STOP );
		$validation->setAllowBlank(true);
		$validation->setShowInputMessage(true);
		$validation->setShowErrorMessage(true);
		$validation->setErrorTitle('Input error');
		$validation->setError('Number is not allowed!');
		$validation->setPromptTitle('Allowed input');
		$validation->setPrompt('Only numbers between 0 and 100 are allowed.');
		$validation->setFormula1(0);
		$validation->setFormula2(100);
		if($args['existing']) {
			$sheet->setCellValue('D'.$j, $args['existing']['scores'][$student_meta['id']]);
		}
		$statement = '"NA"';
		$count = 0;
		do {
			if(isset($grades[$count])) {
				$statement = 'IF((D'.$j.'<'.($grades[$count]['max'] + 0.01).'), "'.$grades[$count]['grade'].'", '.$statement.')';
			}
			$count++;
		} while ($count <= _count($grades));
		$statement = "=".$statement;
		$sheet->setCellValue('E'.$j, $statement);
		$grade_cells[] = 'E'.$j;
		$j++;
	}
	$internalBorder = [
		'borders' => [
	        // 'diagonalDirection' => Borders::DIAGONAL_BOTH,
	        'allBorders' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFDDDDDD']
	        ],
	    ],
	];
	$outlineBorder = [
		'borders' => [
	        'top' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'FFEBEBEB'],
	        ],
	        'bottom' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'FFEBEBEB'],
	        ],
	        'left' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'FFEBEBEB'],
	        ],
	        'right' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'FFEBEBEB'],
	        ],
	    ],
	];

	$sheet->getStyle('A1:'.end($grade_cells))->applyFromArray($internalBorder);
	$sheet->getStyle('A8:'.end($grade_cells))->applyFromArray($outlineBorder);
	$j++;
	$styleStart = 'A'.$j;
	$sheet->setCellValue('B'.$j, "SUMMARY OF GRADES & PERFORMANCE");
	$sheet->getStyle('B'.$j)->getFont()->setBold(true);
	$j++; 
	$sheet->setCellValue('A'.$j, "SN");
	$sheet->setCellValue('B'.$j, "GRADES");
	$sheet->setCellValue('C'.$j, "COUNT");
	$stylesArray = [
	    'font' => [
	        'bold' => true,
	    ],
	    'fill' => [
	        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
	        'rotation' => 90,
	        'startColor' => [
	            'argb' => 'FFCEE6FA',
	        ],
	        'endColor' => [
	            'argb' => 'FFE7E5E5',
	        ],
	    ],
	];
	$sheet->getStyle('A'.$j.':C'.$j)->applyFromArray($stylesArray);
	$n = 1;
	$start_cell = (isset($grade_cells[0])) ? $grade_cells[0] : '';
	$end_cell = (_count($grade_cells)) ? end($grade_cells) : '';
	foreach ($grades as $key => $grade) {
		set_time_limit(80);
		 $j++;
		$sheet->setCellValue('A'.$j, $n);
		$sheet->setCellValue('B'.$j, $grade['grade'].' --- ('.$grade['description'].')');
		$sheet->setCellValue('C'.$j, "=COUNTIF({$start_cell}:{$end_cell}, \"{$grade['grade']}\")");
		$n++;
	}
	$TopBorders = [
	    'borders' => [
	    	'allBorders' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFDDDDDD']
	        ],
	        'top' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFFF0000'],
	        ],
	        'bottom' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFFF0000'],
	        ],
	        'left' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFFF0000'],
	        ],
	        'right' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED,
	            'color' => ['argb' => 'FFFF0000'],
	        ],
	    ],
	];
	$sheet->getStyle($styleStart.':C'.$j)->applyFromArray($TopBorders);
	$writer = new Xlsx($spreads);
	$writer->save($file_path.$file);
	return $file_path.$file;
}

function eedu_update_mock_gradebook_any_class($cron_id, $file, $user_id) {
	$cron = new cronjobs;
	ignore_user_abort(true);
	$post = new post;
	$user = new user;
	$ireset = _IRESET;
	$jreset = _JRESET;
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$grading = new grading;
	$spreadsheet = IOFactory::load(ABSPATH.$file);
	$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
	//checks for authentication
	if(trim($sheetData[2]["A"]) === "" || trim($sheetData[2]["C"]) === "" || trim($sheetData[2]["D"]) === "" || trim($sheetData[2]["E"]) === "") {
		return false;
	}
	$class = trim($sheetData[2]["A"]);
	$category = trim($sheetData[2]["B"]);
	$course = trim($sheetData[2]["C"]);
	$term = trim($sheetData[2]["D"]);
	$exam = trim($sheetData[2]["E"]);
	if($category !== "") {
		$scores = array();
		$existing = db_post_unique(['type' => 'mock_exam_gradebook', 'class' => $class, 'category' => $category, 'course' => $course, 'term' => $term, 'exam' => $exam]);

		$j = $jreset;
		do {
			set_time_limit(60);
			$student_meta = $user->get_user_meta(trim($sheetData[$j]['C']), "username");
			$i = $ireset;
			$score = trim($sheetData[$j]['D']);
			$scores[$student_meta['id']] = ($score === "") ? "x" : $score;
			$j++;
		} while ($j <= _count($sheetData));
		$proc = array();
		$proc['class'] = $class;
		$proc['category'] = $category;
		$proc['course'] = $course;
		$proc['term'] = $term;
		$proc['exam'] = $exam;
		$proc['session'] = get_term_session($term);
		$proc['scores'] = serialize($scores);
		$proc['scores'] = base64_encode($proc['scores']);
		$proc['type'] = 'mock_exam_gradebook';
		$proc['status'] = get_option('auto_verify_results') ? 'verified' : 'in process';
		if($existing) {
			$id = $existing['id'];
			$post->update_post_meta($id, $proc);
		} else {
			$id = $post->add_post($proc);
		}
		ext_exam_reset_broadsheet($class, $category, $term, $exam);
		ext_exam_reset_broadsheet($class, "", $term, $exam);
		$cron->cancel_cron($cron_id);
		$msg = get_course_name($course)." ".get_class_name($class, $category)." ".get_full_term_name($term)." ".$exam." Grade Book Updated Successfully";
		inform_user($user_id, $msg, "", false, NOTIFICATION);
	} else {
		$category_mapping = get_option("category_mapping");
		$cats = $category_mapping[$class];
		foreach($cats as $category) {
			set_time_limit(200);
			$scores = array();
			$students = eedu_portal_get_class_students($class, $category, $term);
			$students = array_column($students, NULL, "id");
			$existing = db_post_unique(['type' => 'mock_exam_gradebook', 'class' => $class, 'category' => $category, 'course' => $course, 'term' => $term, 'exam' => $exam]);
			$j = $jreset;
			do {
				set_time_limit(60);
				$username = trim($sheetData[$j]['C']);
				$student_meta = $user->get_user_meta($username, "username");
				if(isset($students[$student_meta['id']])) {
					$i = $ireset;
					$score = trim($sheetData[$j]['D']);
					$scores[$student_meta['id']] = ($score === "") ? "x" : $score;
				}
				$j++;
			} while ($j <= _count($sheetData));
			if(_count($scores)) {
				$proc = array();
				$proc['class'] = $class;
				$proc['category'] = $category;
				$proc['course'] = $course;
				$proc['term'] = $term;
				$proc['exam'] = $exam;
				$proc['session'] = get_term_session($term);
				$proc['scores'] = serialize($scores);
				$proc['scores'] = base64_encode($proc['scores']);
				$proc['type'] = 'mock_exam_gradebook';
				$proc['status'] = get_option('auto_verify_results') ? 'verified' : 'in process';
				if($existing) {
					$id = $existing['id'];
					$post->update_post_meta($id, $proc);
				} else {
					$id = $post->add_post($proc);
				}
				ext_exam_reset_broadsheet($class, $category, $term, $exam);
				ext_exam_reset_broadsheet($class, "", $term, $exam);
			}
		}
		$cron->cancel_cron($cron_id);
		$msg = get_course_name($course)." ".get_class_name($class)." ".get_full_term_name($term)." ".$exam." Grade Book Updated Successfully";
		inform_user($user_id, $msg, "", false, NOTIFICATION);
	}
	
}

//deprecating
function _update_mock_result_from_template($args) {
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$inputFileName = $args['file'];
	$post = new post;
	$user = new user;
	$ireset = _IRESET;
	$jreset = _JRESET;
	$grading = new grading;
	$mode = $grading->get_mode_of_scoring();
	$result = array();
	$category_meta = $post->get_post_meta($args['category']);
	$class_meta = $post->get_post_meta($args['class']);
	$course_meta = $post->get_post_meta($args['course']);
	$title = get_class_name($class_meta, $category_meta).' '.$course_meta['course_name'].' '.get_term_name($args['term']).' '.get_session_name($args['session']).' '.$args['exam'].' mock resultsheet';
	$title = strtoupper($title);
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$spreadsheet = IOFactory::load($inputFileName);
	$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
	//checks for title authentication
	if($sheetData[1]["A"] !== $title) {
		$response['msg'] = "Template does not match the course";
		$response['error'] = 1;
		return $response;
	}
	$response['j'] = $jreset;
	$response['sheetdata'] = $sheetData;
	$response['args'] = $args;
	$response['msg'] = 'Result sheet uploaded successfully. Please wait while the system updates the records';

	return $response;
}

function create_bulk_student_template() {
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$post = new post;
	$session = Session::getInstance();
	$classes = $post->get_post_by_meta_data(['type' => 'class']);
    $categories = $post->get_post_by_meta_data(['type' => 'category']);
    $category_mapping = get_option('category_mapping');
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$ireset = _IRESET;
	$jreset = _JRESET;
	$first_sheet = true;
	$spreads = new Spreadsheet();

	$title = strtoupper(get_option("sch_name"))." UPLOAD BULK STUDENT DATA";
	$file = str_replace('/', '-', $title.$session->id).'.xlsx';
	$file_path = ABSPATH.UPLOADFOLDER.'/Other Templates/';
	$next_id = get_next_username(true);
	if(!file_exists($file_path)) {
        mkdir($file_path, 0777, true);
    }

	if(_count($classes) > 0) :
		$s = 10;
		$instSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreads, "INSTRUCTIONS");
        $instSheet->getProtection()->setPassword(get_option('excel_password'));
        $instSheet->getProtection()->setSheet(true);
        $instSheet->getProtection()->setSort(true);
        $instSheet->getProtection()->setInsertRows(true);
        $instSheet->getProtection()->setFormatCells(true);
        $instSheet->getProtection()->setSelectLockedCells(true);
        $instSheet->setCellValue('A1', 'INSTRUCTIONS');
        $instSheet->setCellValue('A2', '1. To edit the details of a student, ensure that the student ID remains the same then make your edits.');
        $instSheet->setCellValue('A3', '2. To add a new student, fill in all the data and leave the student ID blank.');
        $instSheet->setCellValue('A4', '3. To change a student\'s class, cut the student details including the student ID to the new class.');
        $instSheet->setCellValue('A4', '3. To remove a student entirely from class list, change the status to \'inactive\'. Any other data entered in the status column will be treated as active.');
        $instSheet->setCellValue('A5', '4. Separate multiple phone numbers with commas.');
        $instSheet->setCellValue('A6', '5. In a worksheet do not leave more than two rows blank else subsequent data will not be read.');
        $instSheet->setCellValue('A7', '6. This, takes some time to update, so when you upload, allow it for at least 5 minutes to process.');
        $instSheet->setCellValue('A9', 'Class Name');
        $instSheet->setCellValue('B9', 'Class ID');
        $instSheet->setCellValue('C9', 'Category ID');
		foreach($classes as $class_meta) :
			set_time_limit(80);
            foreach($categories as $category_meta):
            	set_time_limit(80);
            	if(!in_array($category_meta['id'], $category_mapping[$class_meta['id']]))
						continue;
                if($first_sheet) {
                    $sheet = $spreads->getActiveSheet();
                    $sheet->setTitle(substr(get_class_name($class_meta, $category_meta), 0, 30));
                    $first_sheet = false;
                } else {
                    $sheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreads, substr(get_class_name($class_meta, $category_meta), 0, 30));
                    $spreads->addSheet($sheet);
                }
                $instSheet->setCellValue('A'.$s, get_class_name($class_meta, $category_meta));
                $instSheet->setCellValue('B'.$s, $class_meta['id']);
				$instSheet->setCellValue('C'.$s, $category_meta['id']);

                $user = new user;
                $post = new post;

                $sheet->setCellValue('A1', get_class_name($class_meta, $category_meta)." Class list");
                $sheet->setCellValue('ZZ1', $class_meta['id']);
                $sheet->setCellValue('ZZ2', $category_meta['id']);
                $sheet->setCellValue('A2', 'STUDENT ID');
                $sheet->setCellValue('B2', 'SURNAME');
                $sheet->setCellValue('C2', 'OTHER NAMES');
                $sheet->setCellValue('D2', 'DATE OF BIRTH');
                $sheet->setCellValue('E2', 'GENDER');
                $sheet->setCellValue('F2', 'STATE REG. NUMBER');
                $sheet->setCellValue('G2', 'PARENT FULLNAME');
                $sheet->setCellValue('H2', 'PHONE NUMBER');
                $sheet->setCellValue('I2', 'STATUS');

                $sheet->getColumnDimension('A')->setWidth(20);
                $sheet->getColumnDimension('B')->setWidth(15);
                $sheet->getColumnDimension('C')->setWidth(25);
                $sheet->getColumnDimension('D')->setAutoSize(true);
                $sheet->getColumnDimension('E')->setAutoSize(true);
                $sheet->getColumnDimension('F')->setAutoSize(true);
                $sheet->getColumnDimension('G')->setAutoSize(true);
                $sheet->getColumnDimension('H')->setWidth(18);
                $sheet->getColumnDimension('I')->setAutoSize(true);

                $sheet->getStyle('A1')->getFont()->setBold(true);
                $sheet->getStyle('A2')->getFont()->setBold(true);
                $sheet->getStyle('B2')->getFont()->setBold(true);
                $sheet->getStyle('C2')->getFont()->setBold(true);
                $sheet->getStyle('D2')->getFont()->setBold(true);
                $sheet->getStyle('E2')->getFont()->setBold(true);
                $sheet->getStyle('F2')->getFont()->setBold(true);
                $sheet->getStyle('G2')->getFont()->setBold(true);
                $sheet->getStyle('H2')->getFont()->setBold(true);
                $sheet->getStyle('I2')->getFont()->setBold(true);
                $sheet->getStyle('ZZ1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
                $sheet->getStyle('ZZ2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);

                $students = get_class_students(['class_id' => $class_meta['id'], 'category_id' => $category_meta['id']]);

                if(_count($students) && is_array($students)) {
                	$i = 3;
                	foreach ($students as $student) {
                		$sheet->setCellValue('A'.$i, strtoupper($student['username']));
		                $sheet->setCellValue('B'.$i, $student['surname']);
		                $sheet->setCellValue('C'.$i, $student['other_names']);
		                $sheet->setCellValue('D'.$i, $student['dob']);
		                $sheet->setCellValue('E'.$i, $student['gender']);
		                $sheet->setCellValue('F'.$i, $student['state_reg_number']);
		                $sheet->setCellValue('G'.$i, $student['n1_fullname']);
		                $sheet->setCellValue('H'.$i, $student['n1_phone']);
		                $sheet->setCellValue('I'.$i, $student['status']);
		                $i++;
                	}
                }

                $s++;
            endforeach;
		endforeach;
        $spreads->addSheet($instSheet);
        // $instSheet->getStyle('A1')->getFont()->setBold(true);
	endif;
	$writer = new Xlsx($spreads);
	$writer->save($file_path.$file);
	$new_name = str_replace(ABSPATH, "", $file_path.$file);
	return ["status" => true, "file" => $new_name];
}

function _create_bulk_student_template() {
	ignore_user_abort(true);
	$post = new post;
	$user = new user;
	$sorting = new sorting;
	$session = Session::getInstance();
	$classes = $post->get_post_by_meta_data(['type' => 'class']);
	$categories = $post->get_post_by_meta_data(['type' => 'category']);
	$category_mapping = get_option("category_mapping");
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$ireset = _IRESET;
	$jreset = _JRESET;
	$spreads = new Spreadsheet();

	$title = "STUDENTS - ".strtoupper(get_option("company_name"));
	$file = str_replace('/', '-', $title.$session->id).'.xlsx';
	$file_path = ABSPATH.UPLOADFOLDER.'/Other Templates/';
	$next_id = get_next_username(true);
	if(!file_exists($file_path)) {
        mkdir($file_path, 0777, true);
    }
    $categoryList = "";
    if(_count($categories)) {
    	foreach($categories as $category_meta) {
    		$categoryList .= get_category_name($category_meta['id']).",";
    	}
    	$categoryList = rtrim($categoryList, ",");
    }

	if(_count($classes) > 0) :
		$s = 10;
		$summarySheet = $spreads->getActiveSheet();
    	$summarySheet->setTitle("SUMMARY");
    	$classList = "";
    	foreach($classes as $class_meta) {
    		$classList .= get_class_name($class_meta['id']).",";
    	}
    	$classList = rtrim($classList, ",");
    	$templateRef = exrc_generate_template_ref();
    	$sheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreads, "STUDENTS");
        $spreads->addSheet($sheet);
        $sheet->setCellValue('A1', strtoupper(get_option("company_name")." Class list"));
        $sheet->setCellValue('A2', 'SN');
        $sheet->setCellValue('B2', 'SURNAME');
        $sheet->setCellValue('C2', 'OTHER NAMES');
        $sheet->setCellValue('D2', 'CLASS');
        $sheet->setCellValue('E2', 'STREAM');
        $sheet->setCellValue('F2', 'STATUS');
        $sheet->setCellValue('G2', 'GENDER');
        $sheet->setCellValue('H2', 'DATE OF BIRTH');
        $sheet->setCellValue('I2', 'STATE REG. NUMBER');
        $sheet->setCellValue('J2', 'PHONE NUMBER');
        $sheet->setCellValue('K2', 'PARENT FULLNAME');
        $sheet->setCellValue('L2', 'ADDRESS');
        $sheet->setCellValue('M2', 'STUDENT ID');
        $sheet->setCellValue('N1', '=COUNTA(A:A)');
        $sheet->setCellValue('O1', $templateRef);
        $sheet->getStyle('A2:M2')->getFont()->setBold(true);
		$sheet->getColumnDimension('A')->setWidth(4);
		$sheet->getColumnDimension('B')->setAutoSize(true);
		$sheet->getColumnDimension('C')->setAutoSize(true);
		$sheet->getColumnDimension('D')->setAutoSize(true);
		$sheet->getColumnDimension('E')->setAutoSize(true);
		$sheet->getColumnDimension('F')->setAutoSize(true);
		$sheet->getColumnDimension('G')->setAutoSize(true);
		$sheet->getColumnDimension('J')->setAutoSize(true);
		$sheet->getColumnDimension('K')->setAutoSize(true);
		$sheet->getProtection()->setPassword(get_option("excel_password"));
		$sheet->getProtection()->setSheet(true);
		$sheet->getProtection()->setSort(true);
		$sheet->getProtection()->setInsertRows(true);
		$sheet->getProtection()->setFormatCells(true);
		$sheet->getProtection()->setSelectLockedCells(true);
		$summarySheet->getProtection()->setPassword(get_option("excel_password"));
		$summarySheet->getProtection()->setSheet(true);
		$summarySheet->getProtection()->setSort(true);
		$summarySheet->getProtection()->setInsertRows(true);
		$summarySheet->getProtection()->setFormatCells(true);
		$summarySheet->getProtection()->setSelectLockedCells(true);
		$sheet->getStyle('N1:O1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
        $i = 3;
        $j = 6;
        foreach($classes as $class_meta) :
        	set_time_limit(60);
	        $students = get_class_students($class_meta['id']);
	        $students = $sorting->asort_by_meta_value($students, ['category', 'gender', 'other_names', 'surname'], SORT_ASC);
	        if(_count($category_mapping)) {
	        	if(_count($category_mapping[$class_meta['id']])) {
	        		foreach ($category_mapping[$class_meta['id']] as $key => $category_id) {
	        			$category_name = get_category_name($category_id);
	        			$summarySheet->setCellValue('A'.$j, get_class_name($class_meta['id'], $category_id));
	        			$summarySheet->setCellValue('B'.$j, "=COUNTIFS(INDIRECT(\"STUDENTS!D3:D\"&STUDENTS!N1), \"".$class_meta['class_name']."\", INDIRECT(\"STUDENTS!E3:E\"&STUDENTS!N1), \"".$category_name."\", INDIRECT(\"STUDENTS!G3:G\"&STUDENTS!N1), \"Male\", INDIRECT(\"STUDENTS!F3:F\"&STUDENTS!N1), \"active\")");
	        			$summarySheet->setCellValue('C'.$j, "=COUNTIFS(INDIRECT(\"STUDENTS!D3:D\"&STUDENTS!N1), \"".$class_meta['class_name']."\", INDIRECT(\"STUDENTS!E3:E\"&STUDENTS!N1), \"".$category_name."\", INDIRECT(\"STUDENTS!G3:G\"&STUDENTS!N1), \"Female\", INDIRECT(\"STUDENTS!F3:F\"&STUDENTS!N1), \"active\")");
	        			$summarySheet->setCellValue('D'.$j, "=COUNTIFS(INDIRECT(\"STUDENTS!D3:D\"&STUDENTS!N1), \"".$class_meta['class_name']."\", INDIRECT(\"STUDENTS!E3:E\"&STUDENTS!N1), \"".$category_name."\", INDIRECT(\"STUDENTS!G3:G\"&STUDENTS!N1), \"NA\", INDIRECT(\"STUDENTS!F3:F\"&STUDENTS!N1), \"active\")");
	        			$summarySheet->setCellValue('E'.$j, "=SUM(B".$j.":D".$j.")");
	        			$summarySheet->setCellValue('F'.$j, "=COUNTIFS(INDIRECT(\"STUDENTS!D3:D\"&STUDENTS!N1), \"".$class_meta['class_name']."\", INDIRECT(\"STUDENTS!E3:E\"&STUDENTS!N1), \"".$category_name."\", INDIRECT(\"STUDENTS!F3:F\"&STUDENTS!N1), \"inactive\")");
	        			$j++;
	        		}
	        		$endRow = $j-1;
	        		$summarySheet->setCellValue('A'.$j, "TOTAL");
	        		$summarySheet->setCellValue('B'.$j, "=SUM(B6:B".$endRow.")");
	        		$summarySheet->setCellValue('C'.$j, "=SUM(C6:C".$endRow.")");
	        		$summarySheet->setCellValue('D'.$j, "=SUM(D6:D".$endRow.")");
	        		$summarySheet->setCellValue('E'.$j, "=SUM(E6:E".$endRow.")");
	        		$summarySheet->setCellValue('F'.$j, "=SUM(F6:F".$endRow.")");
	        	}
	        }
	        if(_count($students) && is_array($students)) {
	        	$sn = 1;
	        	foreach ($students as $student) {
	        		$gender = display_gender($student['gender'] ?? '');
	        		$sheet->setCellValue('A'.$i, $sn);
	                $sheet->setCellValue('B'.$i, $student['surname'] ?? '');
	                $sheet->setCellValue('C'.$i, $student['other_names'] ?? '');
	                $sheet->setCellValue('D'.$i, get_class_name($student['class']));
	                $sheet->setCellValue('E'.$i, get_category_name($student['category']));
	                $sheet->setCellValue('F'.$i, $student['status'] ?? '');
	                $sheet->setCellValue('G'.$i, $gender);
	                $sheet->setCellValue('H'.$i, $student['dob'] ?? '');
	                $sheet->setCellValue('I'.$i, $student['state_reg_number'] ?? '');
	                $sheet->setCellValue('J'.$i, $student['n1_phone'] ?? '');
	                $sheet->setCellValue('K'.$i, $student['n1_fullname'] ?? '');
	                $sheet->setCellValue('L'.$i, $student['address'] ?? '');
	                $sheet->setCellValue('M'.$i, strtoupper($student['username'] ?? ''));
	                $sheet->getStyle('B'.$i.':L'.$i)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);

	                $validation = $sheet->getCell('D'.$i)
					    ->getDataValidation();
					$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
					$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
					$validation->setAllowBlank(false);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setShowDropDown(true);
					$validation->setErrorTitle('Input error');
					$validation->setError('Value is not in list.');
					$validation->setPromptTitle('Pick from list');
					$validation->setPrompt('Please pick a value from the drop-down list.');
					$validation->setFormula1('"'.$classList.'"');

					$validation = $sheet->getCell('E'.$i)
					    ->getDataValidation();
					$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
					$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
					$validation->setAllowBlank(false);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setShowDropDown(true);
					$validation->setErrorTitle('Input error');
					$validation->setError('Value is not in list.');
					$validation->setPromptTitle('Pick from list');
					$validation->setPrompt('Please pick a value from the drop-down list.');
					$validation->setFormula1('"'.$categoryList.'"');

	                $validation = $sheet->getCell('F'.$i)
					    ->getDataValidation();
					$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
					$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
					$validation->setAllowBlank(false);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setShowDropDown(true);
					$validation->setErrorTitle('Input error');
					$validation->setError('Value is not in list.');
					$validation->setPromptTitle('Pick from list');
					$validation->setPrompt('Please pick a value from the drop-down list.');
					$validation->setFormula1('"active,inactive"');

					$validation = $sheet->getCell('G'.$i)
					    ->getDataValidation();
					$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
					$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
					$validation->setAllowBlank(false);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setShowDropDown(true);
					$validation->setErrorTitle('Input error');
					$validation->setError('Value is not in list.');
					$validation->setPromptTitle('Pick from list');
					$validation->setPrompt('Please pick a value from the drop-down list.');
					$validation->setFormula1('"Male,Female"');
	                $i++;
	                $sn++;
	        	}
	        }
	        $i++;
	    endforeach;
	    $i++;
	    $sheet->setCellValue('A'.$i, "/=========INACTIVE STUDENTS=========/");
	    $i++;
        // $spreads->addSheet($instSheet);
        // $instSheet->getStyle('A1')->getFont()->setBold(true);
        foreach($classes as $class_meta) :
        	set_time_limit(60);
	        $students = $user->get_user_by_meta_data(['role' => 'student', 'class' => $class_meta['id'], 'status' => 'inactive']);
	        $students = $sorting->asort_by_meta_value($students, ['category', 'gender', 'other_names', 'surname'], SORT_ASC);
	        if(_count($students) && is_array($students)) {
	        	$sn = 1;
	        	foreach ($students as $student) {
	        		$gender = display_gender($student['gender'] ?? '');
	        		$sheet->setCellValue('A'.$i, $sn);
	                $sheet->setCellValue('B'.$i, $student['surname'] ?? '');
	                $sheet->setCellValue('C'.$i, $student['other_names'] ?? '');
	                $sheet->setCellValue('D'.$i, get_class_name($student['class']));
	                $sheet->setCellValue('E'.$i, get_category_name($student['category']));
	                $sheet->setCellValue('F'.$i, $student['status'] ?? '');
	                $sheet->setCellValue('G'.$i, $gender);
	                $sheet->setCellValue('H'.$i, $student['dob'] ?? '');
	                $sheet->setCellValue('I'.$i, $student['state_reg_number'] ?? '');
	                $sheet->setCellValue('J'.$i, $student['n1_phone'] ?? '');
	                $sheet->setCellValue('K'.$i, $student['n1_fullname'] ?? '');
	                $sheet->setCellValue('L'.$i, $student['address'] ?? '');
	                $sheet->setCellValue('M'.$i, strtoupper($student['username'] ?? ''));
	                $sheet->getStyle('B'.$i.':L'.$i)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);

	                $validation = $sheet->getCell('D'.$i)
					    ->getDataValidation();
					$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
					$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
					$validation->setAllowBlank(false);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setShowDropDown(true);
					$validation->setErrorTitle('Input error');
					$validation->setError('Value is not in list.');
					$validation->setPromptTitle('Pick from list');
					$validation->setPrompt('Please pick a value from the drop-down list.');
					$validation->setFormula1('"'.$classList.'"');

					$validation = $sheet->getCell('E'.$i)
					    ->getDataValidation();
					$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
					$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
					$validation->setAllowBlank(false);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setShowDropDown(true);
					$validation->setErrorTitle('Input error');
					$validation->setError('Value is not in list.');
					$validation->setPromptTitle('Pick from list');
					$validation->setPrompt('Please pick a value from the drop-down list.');
					$validation->setFormula1('"'.$categoryList.'"');

	                $validation = $sheet->getCell('F'.$i)
					    ->getDataValidation();
					$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
					$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
					$validation->setAllowBlank(false);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setShowDropDown(true);
					$validation->setErrorTitle('Input error');
					$validation->setError('Value is not in list.');
					$validation->setPromptTitle('Pick from list');
					$validation->setPrompt('Please pick a value from the drop-down list.');
					$validation->setFormula1('"active,inactive,delete"');

					$validation = $sheet->getCell('G'.$i)
					    ->getDataValidation();
					$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
					$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
					$validation->setAllowBlank(false);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setShowDropDown(true);
					$validation->setErrorTitle('Input error');
					$validation->setError('Value is not in list.');
					$validation->setPromptTitle('Pick from list');
					$validation->setPrompt('Please pick a value from the drop-down list.');
					$validation->setFormula1('"Male,Female"');
	                $i++;
	                $sn++;
	        	}
	        }
	        $i++;
	    endforeach;
	    // $sheet->setCellValue('A'.$i, "END OF DATA");
	    $newStudents = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreads, "NEW STUDENTS");
        $spreads->addSheet($newStudents);
        $newStudents->setCellValue('A1', strtoupper(get_option("company_name")." Class list"));
        $newStudents->setCellValue('A2', 'SN');
        $newStudents->setCellValue('B2', 'SURNAME');
        $newStudents->setCellValue('C2', 'OTHER NAMES');
        $newStudents->setCellValue('D2', 'CLASS');
        $newStudents->setCellValue('E2', 'STREAM');
        $newStudents->setCellValue('F2', 'STATUS');
        $newStudents->setCellValue('G2', 'GENDER');
        $newStudents->setCellValue('H2', 'DATE OF BIRTH');
        $newStudents->setCellValue('I2', 'STATE REG. NUMBER');
        $newStudents->setCellValue('J2', 'PHONE NUMBER');
        $newStudents->setCellValue('K2', 'PARENT FULLNAME');
        $newStudents->setCellValue('L2', 'ADDRESS');
        $newStudents->getStyle('A2:M2')->getFont()->setBold(true);
		$newStudents->getColumnDimension('A')->setWidth(4);
		$newStudents->getColumnDimension('B')->setAutoSize(true);
		$newStudents->getColumnDimension('C')->setAutoSize(true);
		$newStudents->getColumnDimension('D')->setAutoSize(true);
		$newStudents->getColumnDimension('E')->setAutoSize(true);
		$newStudents->getColumnDimension('F')->setAutoSize(true);
		$newStudents->getColumnDimension('G')->setAutoSize(true);
		$newStudents->getColumnDimension('J')->setAutoSize(true);
		$newStudents->getColumnDimension('K')->setAutoSize(true);
		for ($i=1; $i <= 200; $i++) { 
			$row = $i + 2;
    		$newStudents->setCellValue('A'.$row, $i);
            $newStudents->setCellValue('F'.$row, 'active');
            $newStudents->getStyle('B'.$row.':L'.$row)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);

            $validation = $newStudents->getCell('D'.$row)
			    ->getDataValidation();
			$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
			$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
			$validation->setAllowBlank(false);
			$validation->setShowInputMessage(false);
			$validation->setShowErrorMessage(true);
			$validation->setShowDropDown(true);
			$validation->setErrorTitle('Input error');
			$validation->setError('Value is not in list.');
			$validation->setPromptTitle('Pick from list');
			$validation->setPrompt('Please pick a value from the drop-down list.');
			$validation->setFormula1('"'.$classList.'"');

			$validation = $newStudents->getCell('E'.$row)
			    ->getDataValidation();
			$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
			$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
			$validation->setAllowBlank(false);
			$validation->setShowInputMessage(false);
			$validation->setShowErrorMessage(true);
			$validation->setShowDropDown(true);
			$validation->setErrorTitle('Input error');
			$validation->setError('Value is not in list.');
			$validation->setPromptTitle('Pick from list');
			$validation->setPrompt('Please pick a value from the drop-down list.');
			$validation->setFormula1('"'.$categoryList.'"');

            $validation = $newStudents->getCell('F'.$row)
			    ->getDataValidation();
			$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
			$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
			$validation->setAllowBlank(false);
			$validation->setShowInputMessage(false);
			$validation->setShowErrorMessage(true);
			$validation->setShowDropDown(true);
			$validation->setErrorTitle('Input error');
			$validation->setError('Value is not in list.');
			$validation->setPromptTitle('Pick from list');
			$validation->setPrompt('Please pick a value from the drop-down list.');
			$validation->setFormula1('"active,inactive"');

			$validation = $newStudents->getCell('G'.$row)
			    ->getDataValidation();
			$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
			$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
			$validation->setAllowBlank(false);
			$validation->setShowInputMessage(false);
			$validation->setShowErrorMessage(true);
			$validation->setShowDropDown(true);
			$validation->setErrorTitle('Input error');
			$validation->setError('Value is not in list.');
			$validation->setPromptTitle('Pick from list');
			$validation->setPrompt('Please pick a value from the drop-down list.');
			$validation->setFormula1('"Male,Female"');
		}
	endif;
	$summarySheet->setCellValue('A1', "ACTIVE STUDENTS");
	$summarySheet->setCellValue('B1', "=COUNTIF(INDIRECT(\"STUDENTS!F3:F\"&STUDENTS!N1), \"active\")");
	$summarySheet->setCellValue('A2', "INACTIVE STUDENTS");
	$summarySheet->setCellValue('B2', "=COUNTIF(INDIRECT(\"STUDENTS!F3:F\"&STUDENTS!N1), \"inactive\")");
	$summarySheet->setCellValue('A3', "NEW STUDENTS");
	$summarySheet->setCellValue('B3', "=COUNTA('NEW STUDENTS'!B:B) - 1");
	$summarySheet->setCellValue('A4', "TOTAL STUDENTS");
	$summarySheet->setCellValue('B4', '=SUM(B1:B3)');
	$summarySheet->setCellValue('A5', 'CLASS');
	$summarySheet->setCellValue('B5', 'ACTIVE MALE');
	$summarySheet->setCellValue('C5', 'ACTIVE FEMALE');
	$summarySheet->setCellValue('D5', 'OTHERS');
	$summarySheet->setCellValue('E5', 'TOTAL ACTIVE');
	$summarySheet->setCellValue('F5', 'TOTAL INACTIVE');
	$summarySheet->getStyle('A5:F5')->getFont()->setBold(true);
	$summarySheet->getColumnDimension('A')->setWidth(20);
	$summarySheet->getColumnDimension('B')->setWidth(15);
	$summarySheet->getColumnDimension('C')->setWidth(15);
	$summarySheet->getColumnDimension('D')->setWidth(15);
	$summarySheet->getColumnDimension('E')->setWidth(15);
	$summarySheet->getColumnDimension('F')->setWidth(15);
	$summarySheet->getStyle('A'.$j.':F'.$j)->getFont()->setBold(true);
	$writer = new Xlsx($spreads);
	$writer->setPreCalculateFormulas(false);
	$writer->save($file_path.$file);
	$new_name = str_replace(ABSPATH, "", $file_path.$file);
	return ["status" => true, "file" => $new_name];
}

function update_students_from_template($cron_id, $file, $user_id) {
	$cron = new cronjobs;
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$post = new post;
	$user = new user;
	$home_url = get_option("home_url");
	$updatedStud = $addedStud = 0;
	$addedList = $updatedList = '<ol>';

	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$spreadsheet = IOFactory::load(ABSPATH.$file);
	$worksheets = $spreadsheet->getSheetNames();
	if(_count($worksheets) < 1) {
		$msg = 'Your uploaded classlist was not updated because, no sheet was found in the template you uploaded.';
		inform_user($user_id, $msg, "CLASS LIST UPDATE FAILED", false, NOTIFICATION, EMAIL);
		$cron->cancel_cron($cron_id);
	}
	$classes = $post->get_post_by_meta_data(['type' => 'class']);
	$categories = $post->get_post_by_meta_data(['type' => 'category']);
	$classes = array_column($classes, NULL, "class_name");
	$categories = array_column($categories, NULL, "category_name");
	//update old students
	$sheet = $spreadsheet->getSheetByName($worksheets[1]);
	$sheetData = $sheet->toArray(null, false, true, true);
	$templateRef = $sheetData[1]["O"];
	if($record = get_record($templateRef)) {
		$msg = "This template was already uploaded on ".date(_DATE_FORMAT_." "._TIME_FORMAT_, $record)." and cannot be used again. Therefore your recent update did not reflect. You should download a new template if you want to make further changes.";
		inform_user($user_id, $msg, "CLASS LIST UPDATED", false, NOTIFICATION, EMAIL);
		$cron->cancel_cron($cron_id);
		return;
	}
	$highestRow = $sheet->getHighestRow();
	for ($i=3; $i <= $highestRow; $i++) {
		set_time_limit(80);
		$userData = ['role' => 'student'];
		if($sheetData[$i]["B"] == null) {
			continue;
		} else $userData['surname'] = ucwords($sheetData[$i]["B"]);

		if($sheetData[$i]["C"] == null) {
			continue;
		} else $userData['other_names'] = ucwords($sheetData[$i]["C"]);

		$userData['class'] = $classes[$sheetData[$i]["D"]]['id'];
		$userData['category'] = $categories[$sheetData[$i]["E"]]['id'];
		$userData['status'] = $sheetData[$i]["F"];
		$userData['gender'] = display_gender($sheetData[$i]["G"]);
		$userData['dob'] = ucwords($sheetData[$i]["H"]);
		$userData['state_reg_number'] = strtoupper($sheetData[$i]["I"]);
		$userData['n1_phone'] = internationalize_phone_number('0'.$sheetData[$i]["J"]);
		$userData['n1_fullname'] = ucwords($sheetData[$i]["K"]);
		$userData['address'] = ucwords($sheetData[$i]["L"]);
		$userData['username'] = $sheetData[$i]["M"];
		$student_meta = $user->get_user_meta($userData['username'], 'username');
		$res = $user->update_user_meta($student_meta['id'], $userData);
		if($res) {
			$updatedStud++;
			$updatedList .= "<li>".get_fullname($userData)." - ".get_class_name($userData['class'], $userData['category'])." - <a href=\"".$home_url."profile?id=".$student_meta['id']."\"><em><small>View Profile</small></em></a></li>";
		}
	}

	//add new students
	$sheet = $spreadsheet->getSheetByName($worksheets[2]);
	$sheetData = $sheet->toArray(null, false, true, true);
	$highestRow = $sheet->getHighestRow();
	for ($i=3; $i <= $highestRow; $i++) {
		set_time_limit(80);
		$userData = ['role' => 'student', 'term_of_admission' => get_option("current_academic_term")];
		if($sheetData[$i]["B"] == null) {
			continue;
		} else $userData['surname'] = ucwords($sheetData[$i]["B"]);

		if($sheetData[$i]["C"] == null) {
			continue;
		} else $userData['other_names'] = ucwords($sheetData[$i]["C"]);

		if($sheetData[$i]["D"] == null) {
			continue;
		} else $userData['class'] = $classes[$sheetData[$i]["D"]]['id'];
		
		$userData['category'] = $categories[$sheetData[$i]["E"]]['id'];
		$userData['status'] = $sheetData[$i]["F"];
		$userData['gender'] = display_gender($sheetData[$i]["G"]);
		$userData['dob'] = ucwords($sheetData[$i]["H"]);
		$userData['state_reg_number'] = strtoupper($sheetData[$i]["I"]);
		$userData['n1_phone'] = internationalize_phone_number('0'.$sheetData[$i]["J"]);
		$userData['n1_fullname'] = ucwords($sheetData[$i]["K"]);
		$userData['address'] = ucwords($sheetData[$i]["L"]);
		$userData['username'] = $userData['password'] = get_next_username(true);
		$res = $user->register_user($userData);
		if($res['error'] == 0) {
			$addedStud++;
			$addedList .= "<li>".get_fullname($userData)." - ".get_class_name($userData['class'], $userData['category'])." - <a href=\"".$home_url."profile?id=".$res['user_id']."\"><em><small>View Profile</small></em></a></li>";
		}
	}
	update_students_id_in_class();
	$addedList .= '</ol>';
	$updatedList .= '</ol>';
	$info = "<p>".get_fullname($user_id)." updated the class lists ".$addedStud." student(s) were added while ".$updatedStud." student(s) profile were updated. Details are as follows:</p>";
	if($addedStud > 0) {
		$info .= "<h3>NEWLY ADDED STUDENT(S)</h3>".$addedList."<hr />";
	}
	if($updatedStud > 0) {
		$info .= "<h3>UPDATED STUDENT(S)</h3>".$updatedList."<hr />";
	}
	if($addedStud > 0 || $updatedStud > 0) {
		inform_admins($info, "Class List Update", false, "software_engineer", "super_admin_tech", "super_admin", "admin");
	}
	$msg = "Class list was successfully updated. ".$addedStud." student(s) were added while ".$updatedStud." student(s) profile were updated Check your mail for details";
	inform_user($user_id, $msg, "CLASS LIST UPDATED", false, NOTIFICATION, EMAIL);
	$cron->cancel_cron($cron_id);
	update_record($templateRef, time());
}

function create_bulk_staff_template() {
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$post = new post;
	$session = Session::getInstance();
	$roles = get_option('user_roles');
	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$ireset = _IRESET;
	$jreset = _JRESET;
	$first_sheet = true;
	$spreads = new Spreadsheet();

	$title = strtoupper(get_option("sch_name"))." UPLOAD BULK STAFF DATA";
	$file = str_replace('/', '-', $title.$session->id).'.xlsx';
	$next_id = get_next_username(false);
	$file_path = UPLOADFOLDER.'/Other Templates/'.str_replace('/', '-', get_session_name($args['session'])).'/'.get_term_name($args['term']).'/';
	if(!file_exists($file_path)) {
        mkdir($file_path, 0777, true);
    }

	if(_count($roles) > 0) :
		foreach($roles as $role => $capabilities) :
			set_time_limit(80);
				if($role == 'student')
					continue;
				if(!current_user_can('super_admin')) {
					if($role == 'super_admin')
						continue;
				}
				if(!current_user_can('update_software')) {
					if($role == 'software_engineer')
						continue;
				}
				if($role == 'contingent' || $role == 'candidate' || $role == 'fresher')
					continue;

                if($first_sheet) {
                    $sheet = $spreads->getActiveSheet();
                    $sheet->setTitle(substr($role, 0, 30));
                    $first_sheet = false;
                } else {
                    $sheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreads, substr($role, 0, 30));
                    $spreads->addSheet($sheet);
                }

                $sheet->getProtection()->setPassword(get_option('excel_password'));
                $sheet->getProtection()->setSheet(true);
                $sheet->getProtection()->setSort(true);
                $sheet->getProtection()->setInsertRows(true);
                $sheet->getProtection()->setFormatCells(true);
                $sheet->getProtection()->setSelectLockedCells(true);

                $user = new user;
                $post = new post;

                $sheet->setCellValue('A1', strtoupper(str_replace('_', ' ', $role)." DATA"));
                $sheet->setCellValue('A2', "ROLE SLUG: ".$role);
                $sheet->setCellValue('A3', 'SN');
                $sheet->setCellValue('B3', 'SURNAME');
                $sheet->setCellValue('C3', 'OTHER NAMES');
                $sheet->setCellValue('D3', 'GENDER');
                $sheet->setCellValue('E3', 'EMAIL');
                $sheet->setCellValue('F3', 'PHONE NUMBER');
                $sheet->setCellValue('G3', 'RESIDENTIAL ADDRESS');
                $sheet->setCellValue('H3', 'STAFF ID');
                $sheet->setCellValue('I3', 'PASSWORD');
                $sheet->setCellValue('J3', 'STATE OF ORIGIN');
                $sheet->setCellValue('K3', 'LGA');

                $sheet->getColumnDimension('A')->setWidth(4);
                $sheet->getColumnDimension('B')->setWidth(15);
                $sheet->getColumnDimension('C')->setWidth(25);
                $sheet->getColumnDimension('D')->setAutoSize(true);
                $sheet->getColumnDimension('E')->setAutoSize(true);
                $sheet->getColumnDimension('F')->setAutoSize(true);
                $sheet->getColumnDimension('G')->setAutoSize(true);
                $sheet->getColumnDimension('H')->setAutoSize(true);
                $sheet->getColumnDimension('I')->setAutoSize(true);
                $sheet->getColumnDimension('J')->setAutoSize(true);
                $sheet->getColumnDimension('K')->setAutoSize(true);

                $sheet->getStyle('A1')->getFont()->setBold(true);
                $sheet->getStyle('A3')->getFont()->setBold(true);
                $sheet->getStyle('B3')->getFont()->setBold(true);
                $sheet->getStyle('C3')->getFont()->setBold(true);
                $sheet->getStyle('D3')->getFont()->setBold(true);
                $sheet->getStyle('E3')->getFont()->setBold(true);
                $sheet->getStyle('F3')->getFont()->setBold(true);
                $sheet->getStyle('G3')->getFont()->setBold(true);
                $sheet->getStyle('H3')->getFont()->setBold(true);
                $sheet->getStyle('I3')->getFont()->setBold(true);
                $sheet->getStyle('J3')->getFont()->setBold(true);
                $sheet->getStyle('K3')->getFont()->setBold(true);

                for ($i=4; $i < 104; $i++) { 
                    $sheet->setCellValue('A'.$i, $i-3);
                    if($next_id) {
                    	$sheet->setCellValue('G'.$i, $next_id);
                    	$sheet->setCellValue('H'.$i, $next_id);
                    	$next_id = false;
                    }
                    for ($j=1; $j < 11; $j++) { 
                        $sheet->getStyle($alphanum[$j].$i)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
                    }
                }
		endforeach;
	endif;
	$writer = new Xlsx($spreads);
	$writer->save($file_path.$file);
	return $file_path.$file;
}

function update_staff_from_template($args) {
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$inputFileName = $args['file'];
	$post = new post;
	$user = new user;
	$totalStaff = $addedStaff = 0;
	$log = '';

	$alphanum = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
	$spreadsheet = IOFactory::load($inputFileName);
	$worksheets = $spreadsheet->getSheetNames();
	if(_count($worksheets) < 1) {
		$response['error'] = 1;
		$response['msg'] = 'No sheet was found in the uploaded template';
		return $response;
	}

	foreach ($worksheets as $key => $worksheet) {
		set_time_limit(80);
		$sheetData = $spreadsheet->getSheetByName($worksheet)->toArray(null, true, true, true);
		$role = str_replace('ROLE SLUG: ', '', $sheetData[2]['A']);

		if($role == '') {
			continue;
		}

		if($sheetData[1]['A'] !== strtoupper(str_replace('_', ' ', $role)." DATA")) {
			continue;
		}

		for ($i=4; $i < 104; $i++) { 
			set_time_limit(80);
			$userData = ['role' => $role, 'status' => 'active'];
			if($sheetData[$i]["B"] == null) {
				continue;
			} else $userData['surname'] = ucwords($sheetData[$i]["B"]);

			if($sheetData[$i]["C"] == null) {
				continue;
			} else $userData['other_names'] = ucwords($sheetData[$i]["C"]);
			$userData['gender'] = display_gender($sheetData[$i]["D"]);
			$userData['email'] = $sheetData[$i]["E"];
			$userData['phone'] = internationalize_phone_number('0'.$sheetData[$i]["F"]);
			$userData['address'] = ucwords($sheetData[$i]["G"]);

			if($sheetData[$i]["H"] == null) {
				continue;
			} else $userData['username'] = $sheetData[$i]["H"];

			if($sheetData[$i]["I"] == null) {
				continue;
			} else $userData['password'] = $sheetData[$i]["I"];

			$userData['state'] = ucwords($sheetData[$i]["J"]);
			$userData['lga'] = ucwords($sheetData[$i]["K"]);
			$totalStaff++;
			//register user
			$res = $user->register_user($userData);

			if($res['error'] == 0) {
				$addedStaff++;
			} else {
				$log .= $res['msg'].' ref: "'.$worksheet.'" Cell G'.$i;
			}
		}
	}

	if($addedStaff > 0) {
		$response['msg'] = $addedStaff." Staff(s) records created successfully.";
		$response['error'] = 0;
		if($addedStaff < $totalStaff) {
			$diff = $totalStaff - $addedStaff;
			$response['msg'] = $response['msg'].' '.$diff.' Staff(s) was not added. Reason:<br>'.$log;
		}
	} else {
		$response['msg'] = "No new record was found. Check if Staff IDs are unique.";
		$response['error'] = 1;
	}	
	
	return $response;
}

function upload_template_button($args) {
	global $post_meta;
	?>
<a class="btn btn-info" href="upload-result-sheet?class=<?php echo $args['class'] ?>&course=<?php echo $args['course'] ?>&term=<?php echo $args['term'] ?>&session=<?php echo $args['session'] ?>&category=<?php echo $args['category'] ?>"><i class="fas fa-upload"></i>&nbsp;Upload Result</a>
<?php
}

function download_mock_template_button($args) {
	$file = create_mock_resultsheet_template($args);
	?>
<a class="btn btn-success" href="<?php echo $file; ?>" download><i class="fas fa-download"></i>&nbsp;Download Template</a>
<?php
}

function upload_mock_template_button($args) {
	global $post_meta;
	?>
	<form id="upload_mock_template_form" method="post" style="display: contents;">
    <div><em class="alert" style="display: none">Uploading...</em></div><br>
    <div style="display: inline-block !important">
    	<input type="hidden" id="class" name="class" value="<?php echo $args['class'] ?>">
    	<input type="hidden" id="category" name="category" value="<?php echo $args['category'] ?>">
    	<input type="hidden" id="course" name="course" value="<?php echo $args['course'] ?>">
    	<input type="hidden" id="exam" name="exam" value="<?php echo $args['exam'] ?>">
    	<input type="hidden" id="term" name="term" value="<?php echo $args['term'] ?>">
    	<input type="hidden" id="acsession" name="acsession" value="<?php echo $args['acsession'] ?>">
        <input type="hidden" id="action" name="action" value="upload_mock_result_sheet">
        <input type="file" name="upload-file" id="mock-template-upload-file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" style="display: none">
        <button id="uploadMockTemplateBtn" class="btn btn-info"><i class="fas fa-upload"></i>&nbsp;Upload Template</button>
    </div>

</form>
<?php
	add_page_javascript('app/assets/js/page-scripts/js-functions.js');
	add_page_javascript('contents/plugins/excel_result_compilation/js/upload-mock-template.js');
}

function download_bulk_student_template_button() {
	$file = create_bulk_student_template();
	?>
<a class="btn btn-success" href="<?php echo $file; ?>" download><i class="fas fa-download"></i>&nbsp;Download Template</a>
<?php
}

function download_bulk_staff_template_button() {
	$file = create_bulk_staff_template();
	?>
<a class="btn btn-success" href="<?php echo $file; ?>" download><i class="fas fa-download"></i>&nbsp;Download Template</a>
<?php
}

function ft_comment_offline_download_button() {
	if(isset($_POST['id'])) {
		$id = $_POST['id'];
	} else {
		$id = '';
	}
	$file = create_ft_comment_template($id);
	?>
	<div class="col-md-6">
<a class="btn btn-success" href="<?php echo $file; ?>" download><i class="fas fa-download"></i>&nbsp;Download Template</a>
</div>
<?php
}

function upload_bulk_student_template_button() {
	global $post_meta;
	?>
<form id="upload_bulk_stud_form" method="post" style="display: contents;">
    <div><em class="alert" style="display: none">Uploading...</em></div><br>
    <div style="display: inline-block !important">
        <input type="hidden" id="action" name="action" value="upload_bulk_students_sheet">
        <input type="file" name="upload-file" id="bulk-stud-upload-file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" style="display: none">
        <button id="uploadBulkStudentTemplateBtn" class="btn btn-info"><i class="fas fa-upload"></i>&nbsp;Upload Students</button>
    </div>

</form>
<?php
	$post_meta['plugin_name'] = excel_result_compilation::$plugin_name;
	add_page_javascript('contents/plugins/excel_result_compilation/js/upload-bulk-stud.js');
}

function upload_bulk_staff_template_button() {
	global $post_meta;
	?>
<form id="upload_bulk_staff_form" method="post" style="display: contents;">
    <div><em class="alert" style="display: none">Uploading...</em></div><br>
    <div style="display: inline-block !important">
        <input type="hidden" id="action" name="action" value="upload_bulk_staff_sheet">
        <input type="file" name="upload-file" id="bulk-staff-upload-file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" style="display: none">
        <button id="uploadBulkStaffTemplateBtn" class="btn btn-info"><i class="fas fa-upload"></i>&nbsp;Upload Staff Members</button>
    </div>

</form>
<?php
	$post_meta['plugin_name'] = excel_result_compilation::$plugin_name;
	add_page_javascript('contents/plugins/excel_result_compilation/js/upload-bulk-staff.js');
}

function ft_comment_offline_template_button() {
	global $post_meta;
	$session = Session::getInstance();
	$search_id = (isset($_POST['id'])) ? $_POST['id'] : $session->id;
	?>
<div class="col-md-6">
<form id="upload_bulk_stud_form" method="post" style="display: contents;">
    <em class="alert" style="display: none">Uploading...</em>
    <div style="display: inline-block !important">
    	<input type="hidden" id="search_id" name="search_id" value="<?php echo $search_id; ?>">
        <input type="hidden" id="action" name="action" value="upload_ft_comments_sheet">
        <input type="file" name="upload-file" id="bulk-stud-upload-file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" style="display: none">
        <button id="uploadBulkStudentTemplateBtn" class="btn btn-info"><i class="fas fa-upload"></i>&nbsp;Upload Comments</button>
    </div>

</form>
</div>
<?php
	$post_meta['plugin_name'] = excel_result_compilation::$plugin_name;
	add_page_javascript('contents/plugins/excel_result_compilation/js/upload-bulk-stud.js');
}


function search_by_form_teachers() {
	global $post_meta;
	if(current_user_can('admin')) :
		$fts = get_all_form_teachers(true);
		$options = get_select_options_from_user($fts, $val = 'id', false);
		?>
		<div class="panel panel-custom bg-white">
			<div class="panel-heading">
		        <h1>Search</h1>
		    </div>
		    <div class="panel-body instruction">
		    	<form method="post" id="search-form">
		    		<?php select(['label' => 'Select Teacher', 'id' => 'id', 'data-form' => 'search-form', 'required' => 'required'], $options, $_POST['id']) ?>
		    	</form>
		    </div>
		</div>
		<?php
	endif;
	$post_meta['plugin_name'] = excel_result_compilation::$plugin_name;
	add_page_javascript('contents/plugins/excel_result_compilation/js/search-by-ft.js');
}

function _upload_result_link() {
	?>
	<a class="btn btn-info" href="upload-result-sheet"><i class="fas fa-upload"></i>&nbsp;Upload Result</a>
	<?php
}

function exrc_generate_template_ref() {
	$session = Session::getInstance();
	$ref = "T".time()."-U".$session->id."-R".rand(1,999);
	return $ref;
}