<?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;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Style\Color;

require_once("pages.php");

function sfm_send_fee_invoice_from_id($id) {
	$post = new post;
	$user = new user;
	$session = Session::getInstance();
	$meta = $post->get_post_meta($id);
	$fees = $post->get_post_by_meta_data(['type' => 'sfm_fee', 'class' => $meta['class'], 'category' => $meta['category'], 'term' => $meta['term'], 'gender' => $meta['gender']]);
	$term_meta = $post->get_post_meta($meta['term']);
	$class = get_class_name($meta['class'], $meta['category']);
	if(!$fees) {
		return false;
	}
	if(!_count($fees)) {
		return false;
	}
	foreach ($fees as $key => &$value) {
		$fees[$key]['feeid'] = $fees[$key]['id'];
		$fees[$key]['ref'] = $fees[$key]['description'] ." :: ". $fees[$key]['term'];
		$fees[$key]['narration'] .= " - ".$class." (".get_full_term_name($meta['term']).")";
	}
	$due_date = time();

	switch ($meta['gender']) {
		case 'Male':
			$gender = ["", "Male", "M", "Boy"];
			break;

		case 'Female':
			$gender = ["Female", "F", "Girl"];
			break;
		
		default:
			$gender = ["", "Male", "M", "Boy"];
			break;
	}

	$students = $user->get_user_by_meta_data(['role' => ['student', 'debtor'], 'class' => $meta['class'], 'category' => $meta['category'], 'status' => 'active', 'gender' => $gender]);
	if(!$students) {
		return false;
	}
	if(!_count($students)) {
		return false;
	}
	foreach ($students as $student_meta) {
		set_time_limit(200);
		pm_new_invoice($student_meta['id'], $fees, $due_date, "_sfm_fee_success_method", "_sfm_fee_error_method");
	}

	foreach ($fees as $fee) {
		$post->update_post_meta($fee['id'], ['status' => 'Invoiced', 'invoiced_by' => $session->id]);
	}
	
	return _count($students);
}

function _sfm_fee_success_method($args, $item) {
	return true;
}

function _sfm_fee_error_method($args, $item) {
	return true;
}

function sfm_student_is_exempted($student_id, $fee_id, $term, $exemptionList = false) {
	return false;
}

function download_fees_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;
	$post = new post;
	$term_name = get_full_term_name($term);
	$session = Session::getInstance();
	$file_path = ABSPATH.UPLOADFOLDER."/Fees Templates/";
	if(!file_exists($file_path)) {
        mkdir($file_path, 0777, true);
    }
	$description = $post->get_post_by_meta_data(['type' => 'sfm_description', 'status' => 'active'], 'LIKE', false, 0, 0, 'post_id', 'DESC');
	$file = sfm_create_fees_template([
		'description' => $description,
		'term' => $term,
		'term_name' => $term_name,
		'file_path' => $file_path
	]);
	$new_name = str_replace(ABSPATH, "", $file);
	return ["status" => true, "file" => $new_name];
}

function sfm_create_fees_template($args) {
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$excel_password = get_option('excel_password');
	$post = new post;
	// $students = $args['students'];
	$spreads = sfm_config_workbook();
	
	$user = new user;
	$post = new post;
	$internalBorder = [
		'borders' => [
	        // 'diagonalDirection' => Borders::DIAGONAL_BOTH,
	        'allBorders' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
	            'color' => ['argb' => 'FFDDDDDD']
	        ],
	    ],
	];
	$outlineBorder = [
		'borders' => [
	        'top' => [
	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
	            'color' => ['argb' => 'adf2a1'],
	        ],
	        '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'],
	        ],
	    ],
	];
	$templateRef = sfm_generate_template_ref();
	$title = 'School Fees '.$args['term_name'];
	$title = strtoupper($title);
	$file = str_replace('/', '-', str_replace(' ', '_', $title)).'.xlsx';
	$file_path = $args['file_path'];
	$class_cat = eedu_portal_all_class_category();
	$summarysheet = $spreads->getActiveSheet();
	$summarysheet->setTitle("Summary");
	$summarysheet->setCellValue('A1', "SUMMARY OF FEE COLLECTION ".$args['term_name']);
	$summarysheet->setCellValue('A3', "SN");
	$summarysheet->setCellValue('B3', "CLASS");
	$summarysheet->setCellValue('C3', "FEE");
	$summarysheet->setCellValue('D3', "TOTAL NEW PAYMENT");
	$summarysheet->setCellValue('E3', "TOTAL RECEIVED");
	$summarysheet->setCellValue('F3', "TOTAL EXPECTED");
	$summarysheet->setCellValue('G3', "TOTAL DEBT");
	$summarysheet->setCellValue('H3', "CLEARED");
	$summarysheet->setCellValue('I3', "IN DEBT");
	$summarysheet->setCellValue('J3', "TOTAL STUDENTS");
	$summarysheet->setCellValue('A2', $templateRef);
	$summarysheet->getColumnDimension('A')->setWidth(4);
	$summarysheet->getColumnDimension('B')->setAutoSize(true);
	$summarysheet->getColumnDimension('C')->setAutoSize(true);
	$summarysheet->getColumnDimension('D')->setAutoSize(true);
	$summarysheet->getColumnDimension('E')->setAutoSize(true);
	$summarysheet->getColumnDimension('F')->setAutoSize(true);
	$summarysheet->getColumnDimension('G')->setAutoSize(true);
	$summarysheet->getColumnDimension('H')->setAutoSize(true);
	$summarysheet->getColumnDimension('I')->setAutoSize(true);
	$summarysheet->getColumnDimension('J')->setAutoSize(true);
	$summarysheet->getStyle('A1')->getFont()->setBold(true);
	$summarysheet->getStyle('A2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
	$summarysheet->getStyle('A3:J3')->getFont()->setBold(true);
	$summarysheet->getStyle('A3:J3')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('adf2a1');
	$summarysheet->getProtection()->setPassword($excel_password);
	$summarysheet->getProtection()->setSheet(true);
	$summarysheet->getProtection()->setSort(true);
	$summarysheet->getProtection()->setInsertRows(true);
	$summarysheet->getProtection()->setFormatCells(true);
	$summarysheet->getProtection()->setSelectLockedCells(true);

	//conditional
	$condition = new Conditional();
	$condition->setConditionType(Conditional::CONDITION_CELLIS)
	    ->setOperatorType(Conditional::OPERATOR_EQUAL)
	    ->addCondition('IN DEBT');
	$condition->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
	// $condition->getStyle()->getFont()->setItalic(true);
	// $condition->getStyle()->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
	$sn = 4;
	if(_count($class_cat)) {
		foreach($class_cat as $class_name => $class_cats) {
			$class = eedu_portal_get_class_selection($class_cats);
			$students = eedu_portal_get_class_students($class['class'], $class['category'], $args['term']);
			if(!_count($students)) {
				continue;
			}
			if(_count($args['description'])) {
				foreach ($args['description'] as $description) {
					$fees = $post->get_post_by_meta_data(['type' => 'sfm_fee', 'description' => $description['id'], 'class' => $class['class'], 'category' => $class['category'], 'term' => $args['term']]);
					if(!_count($fees)) {
						continue;
					}
					$reg_fees = $post->get_post_by_meta_data(['type' => 'registration_fee', 'adm_term' => $args['term'], 'adm_class' => $class['class'], 'cat' => $description['id']]);
					$fees = array_column($fees, null, 'gender');
					$reg_fees = array_column($reg_fees, null, 'gender');
					$sheet_name = substr($class_name.' '.$description['description'], 0, 30);
			        $sheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreads, $sheet_name);
			        $spreads->addSheet($sheet);
				    $sheet_title = strtoupper($class_name.' '.$args['term_name'].' '.$description['description'].' Fees');
				    $sheet->setCellValue('A1', $sheet_title);
					$sheet->setCellValue('A2', $class['class']);
					$sheet->setCellValue('B2', $class['category']);
					$sheet->setCellValue('C2', $description['id']);
					$sheet->setCellValue('D2', $args['term']);
					$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->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('B3', "GO TO SUMMARY PAGE");
					$sheet->getStyle('B3')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('008bc2');
					$sheet->getStyle('B3')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
					$sheet->getStyle('B3')->getAlignment()->setHorizontal('center');
					$sheet->getCell('B3')->getHyperlink()->setUrl("sheet://'Summary'!A1");
					$sheet->getStyle('B3')->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
					$sheet->getCell('A4')->setValue('SN');
					$sheet->getCell('B4')->setValue('NAME OF STUDENTS');
					$sheet->getCell('C4')->setValue('STUDENT ID');
					$sheet->getCell('D4')->setValue('DATE (DD/MM/YY)');
					$sheet->getCell('E4')->setValue('TELLER NO/BANK');
					$sheet->getCell('F4')->setValue('AMOUNT');
					$sheet->getCell('G4')->setValue('PREVIOUSLY PAID');
					$sheet->getCell('H4')->setValue('TOTAL RECEIVED');
					$sheet->getCell('I4')->setValue('AMOUNT DUE');
					$sheet->getCell('J4')->setValue('BALANCE');
					$sheet->getCell('K4')->setValue('STATUS');
					$sheet->getCell('L4')->setValue('NEW STUDENT');
					$sheet->getCell('M4')->setValue('GENDER');
					$sheet->setCellValue('H1', 'NEW MALE');
					$sheet->setCellValue('I1', 'NEW FEMALE');
					$sheet->setCellValue('J1', 'REG. MALE');
					$sheet->setCellValue('K1', 'REG. FEMALE');
					$sheet->setCellValue('H2', $reg_fees['Male']['amount']);
					$sheet->setCellValue('I2', $reg_fees['Female']['amount']);
					$sheet->setCellValue('J2', $fees['Male']['amount']);
					$sheet->setCellValue('K2', $fees['Female']['amount']);
					$sheet->getStyle('H2:I2')->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
					$sheet->getStyle('H2:K2')
						    ->getNumberFormat()
						    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
					$sheet->getStyle('H1:K1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('adf2a1');
					$validation = $sheet->getCell('H2')
					    ->getDataValidation();
					$validation->setType(DataValidation::TYPE_WHOLE );
					$validation->setErrorStyle(DataValidation::STYLE_STOP );
					$validation->setAllowBlank(true);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setErrorTitle('Allowed input');
					$validation->setError('Only numbers are allowed.');
					// $validation->setPromptTitle('Allowed input');
					// $validation->setPrompt('Only numbers are allowed.');
					$validation->setFormula1(0);
					$validation->setFormula2(9999999999);
					$validation = $sheet->getCell('I2')
					    ->getDataValidation();
					$validation->setType(DataValidation::TYPE_WHOLE );
					$validation->setErrorStyle(DataValidation::STYLE_STOP );
					$validation->setAllowBlank(true);
					$validation->setShowInputMessage(false);
					$validation->setShowErrorMessage(true);
					$validation->setErrorTitle('Allowed input');
					$validation->setError('Only numbers are allowed.');
					// $validation->setPromptTitle('Allowed input');
					// $validation->setPrompt('Only numbers are allowed.');
					$validation->setFormula1(0);
					$validation->setFormula2(9999999999);
					$sheet->getStyle('H1:K1')->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')->setWidth(20);
					$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->getColumnDimension('L')->setAutoSize(true);
					$sheet->getColumnDimension('M')->setAutoSize(true);
					$sheet->getStyle('A1')->getFont()->setBold(true);
					$sheet->getStyle('A4:M4')->getFont()->setBold(true);
					$sheet->getStyle('A4:M4')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('adf2a1');
					$j = 5;
					foreach ($students as $student_meta) {
						set_time_limit(80);
						$gender = sfm_convert_gender($student_meta['gender']);
						$amount_due = $fees[$gender]['amount'];
						$ref = $description['id']." :: ".$args['term'];
						$invoice = new pm_invoice($student_meta['id']);
						$inv2 = $invoice->item_in_another_invoice($ref);
						if($inv2) {
							$invoice->set_invoice_by_number($inv2->invoice_number);
							$item = $invoice->get_item($ref);
							$item_amount = $item ? $item['amount'] : 0;
							if($item_amount != $amount_due) {
								$item['amount'] = $amount_due;
								$invoice->update_item($ref, $item);
							}
							$status = $invoice->status();
							$bal = $invoice->total(true);
							$paid = $invoice->total() - $bal;
						} else {
							$paid = 0;
							$bal = $amount_due;
						}
						$sheet->setCellValue('A'.$j, $j-4);
						$sheet->setCellValue('B'.$j, strtoupper($student_meta['surname'].' '.$student_meta['other_names']));
						$sheet->setCellValue('C'.$j, strtoupper($student_meta['username']));
						$sheet->setCellValue('H'.$j, "=SUM(F".$j.":G".$j.")"); //total received
						$sheet->setCellValue('I'.$j, '=IF(M'.$j.'="Female", IF(L'.$j.'="YES", I2, K2), IF(L'.$j.'="YES", H2, J2))'); //amount due
						$sheet->setCellValue('G'.$j, $paid); //amount paid
						$sheet->setCellValue('J'.$j, "=I".$j."-H".$j); //balance
						$sheet->setCellValue('K'.$j, '=IF(J'.$j.'<=0, "OK", "IN DEBT")'); //status
						if ($student_meta['term_of_admission'] === $args['term']) {
							$sheet->setCellValue('L'.$j, 'YES');
						} else $sheet->setCellValue('L'.$j, 'NO');
						$sheet->setCellValue('M'.$j, $gender); //gender
						$sheet->getStyle('L'.$j.':M'.$j)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
						$sheet->getStyle('D'.$j.':F'.$j)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
						$sheet->getStyle('D'.$j)
						    ->getNumberFormat()
						    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY);

						// input validation
						$validation = $sheet->getCell('F'.$j)
						    ->getDataValidation();
						$validation->setType(DataValidation::TYPE_WHOLE );
						$validation->setErrorStyle(DataValidation::STYLE_STOP );
						$validation->setAllowBlank(true);
						$validation->setShowInputMessage(false);
						$validation->setShowErrorMessage(true);
						$validation->setErrorTitle('Allowed input');
						$validation->setError('Only numbers are allowed.');
						$validation->setPromptTitle('Allowed input');
						$validation->setPrompt('Only numbers are allowed.');
						$validation->setFormula1(0);
						$validation->setFormula2(9999999999);

						$validation = $sheet->getCell('M'.$j)
						    ->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"');

						$validation = $sheet->getCell('L'.$j)
						    ->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('"YES,NO"');
						$sheet->getStyle('A1:M'.$j)->applyFromArray($internalBorder);
						$sheet->getStyle('A4:M'.$j)->applyFromArray($outlineBorder);
						$j++;
					}
					$lastRow = $j - 1;
					// $conditionalStyles = $sheet->getStyle('J5:J'.$lastRow)->getConditionalStyles();
					// $conditionalStyles[] = $condition;
					$sheet->setConditionalStyles('K5:K'.$lastRow, $condition);
					$k = $j+1;
					$l = $k+1;
					$sheet->getStyle('G5:K'.$lastRow)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FFEBEBEB');
					$sheet->getStyle('F5:J'.$j)
						    ->getNumberFormat()
						    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
					$sheet->setCellValue('E'.$j, "TOTAL");
					$sheet->setCellValue('F'.$j, "=SUM(F5:F".$lastRow.")");
					$sheet->setCellValue('G'.$j, "=SUM(G5:G".$lastRow.")");
					$sheet->setCellValue('H'.$j, "=SUM(H5:H".$lastRow.")");
					$sheet->setCellValue('I'.$j, "=SUM(I5:I".$lastRow.")");
					$sheet->setCellValue('J'.$j, "=SUM(J5:J".$lastRow.")");
					$sheet->getStyle('E'.$j.':J'.$j)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('adf2a1');
					$sheet->getRowDimension($j)->setRowHeight(20);
					$sheet->getStyle('E'.$j.':J'.$j)->getFont()->setBold(true);
					$sheet->setCellValue('E'.$k, "CLEARED");
					$sheet->setCellValue('F'.$k, '=COUNTIF(K5:K'.$lastRow.', "OK")');
					$sheet->setCellValue('E'.$l, "IN DEBT");
					$sheet->setCellValue('F'.$l, '=COUNTIF(K5:K'.$lastRow.', "IN DEBT")');
					$sheet->getStyle('E'.$k.':F'.$l)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FFEBEBEB');
					$sheet->getStyle('E'.$k.':F'.$l)->getFont()->setBold(true);
					$summarysheet->setCellValue('A'.$sn, $sn-3); //serial number
					$summarysheet->setCellValue('B'.$sn, $class_name); //class name
					$summarysheet->setCellValue('C'.$sn, $description['description']); //fee
					$summarysheet->setCellValue('D'.$sn, "='".$sheet_name."'!F".$j); //total new payment
					$summarysheet->setCellValue('E'.$sn, "='".$sheet_name."'!H".$j); //total received
					$summarysheet->setCellValue('F'.$sn, "='".$sheet_name."'!I".$j); //total due
					$summarysheet->setCellValue('G'.$sn, "='".$sheet_name."'!J".$j); //total debt
					$summarysheet->setCellValue('H'.$sn, "='".$sheet_name."'!F".$k); //total debt
					$summarysheet->setCellValue('I'.$sn, "='".$sheet_name."'!F".$l); //total debt
					$summarysheet->setCellValue('J'.$sn, "='".$sheet_name."'!A".$lastRow); //total debt
					$summarysheet->getCell('B'.$sn)->getHyperlink()->setUrl("sheet://'".$sheet_name."'!B3");
					$summarysheet->getCell('C'.$sn)->getHyperlink()->setUrl("sheet://'".$sheet_name."'!B3");
					$summarysheet->getStyle('B'.$sn)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
					$summarysheet->getStyle('C'.$sn)->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
					$sn++;
				}
			}
		}

	}
	$lastRow = $sn - 1;
	$summarysheet->getStyle('A1:J'.$lastRow)->applyFromArray($internalBorder);
	$summarysheet->getStyle('A3:J'.$lastRow)->applyFromArray($outlineBorder);
	$summarysheet->setCellValue('C'.$sn, "GRAND TOTAL");
	$summarysheet->setCellValue('D'.$sn, "=SUM(D4:D".$lastRow.")");
	$summarysheet->setCellValue('E'.$sn, "=SUM(E4:E".$lastRow.")");
	$summarysheet->setCellValue('F'.$sn, "=SUM(F4:F".$lastRow.")");
	$summarysheet->setCellValue('G'.$sn, "=SUM(G4:G".$lastRow.")");
	$summarysheet->getStyle('C'.$sn.':G'.$sn)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('adf2a1');
	$summarysheet->getRowDimension($sn)->setRowHeight(20);
	$summarysheet->getStyle('C'.$sn.':G'.$sn)->getFont()->setBold(true);
	$summarysheet->getStyle('D4:G'.$sn)
			->getNumberFormat()
			->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
	$writer = new Xlsx($spreads);
	$writer->save($file_path.$file);
	return $file_path.$file;
}

function sfm_config_workbook() {
	$excel_password = get_option('excel_password');
	$spreads = new Spreadsheet();
	$spreads->getSecurity()->setLockWindows(true);
	$spreads->getSecurity()->setLockStructure(true);
	$spreads->getSecurity()->setWorkbookPassword($excel_password);
	return $spreads;
}

function sfm_update_fees($cron_id, $file) {
	$cron = new cronjobs;
	$session = Session::getInstance();
	ini_set('max_execution_time', 300);
	ignore_user_abort(true);
	$post = new post;
	$user = new user;
	$spreadsheet = IOFactory::load(ABSPATH.$file);
	$worksheets = $spreadsheet->getSheetNames();
	if(count($worksheets) < 1) {
		$response['error'] = 1;
		$response['msg'] = 'No sheet was found in the uploaded template';
		return $response;
	}
	if(!in_array("Summary", $worksheets)) {
		$response['error'] = 1;
		$response['msg'] = 'There is no Summary sheet in the uploaded template';
		return $response;
	}
	$summarysheetData = $spreadsheet->getSheetByName("Summary")->toArray(null, true, true, true);
	$templateRef = $summarysheetData[2]["A"];
	$error = $success = "<ol>";
	$hasError = false;
	$updated = 0;
	foreach ($worksheets as $key => $worksheet) {
		if($worksheet == "Summary") {
			continue;
		}
		$sheetData = $spreadsheet->getSheetByName($worksheet)->toArray(null, true, true, true);
		$class = (int) trim($sheetData[2]["A"]);
		$category = (int) trim($sheetData[2]["B"]);
		$feeid = (int) trim($sheetData[2]["C"]);
		$term = (int) trim($sheetData[2]["D"]);
		$male_reg_fee_amount = trim($sheetData[2]["H"]);
		$female_reg_fee_amount = trim($sheetData[2]["I"]);
		$term_name = get_full_term_name($term);
		$fee_meta = $post->get_post_meta($feeid);
		$fees = $post->get_post_by_meta_data(['type' => 'sfm_fee', 'description' => $feeid, 'class' => $class, 'category' => $category, 'term' => $term]);
		if(!_count($fees)) {
			continue;
		}
		$fees = array_column($fees, null, 'gender');
		$reg_fees_meta = ['type' => 'registration_fee', 'adm_term' => $term, 'adm_class' => $class, 'cat' => $feeid, 'gender' => 'Male', 'status' => 'ok'];
		$male_reg_fee = db_post_unique($reg_fees_meta);
		$male_reg_fee_amount = (float) str_replace(",", "", $male_reg_fee_amount);
		$female_reg_fee_amount = (float) str_replace(",", "", $male_reg_fee_amount);
		if ($male_reg_fee) {
			if($male_reg_fee_amount != $male_reg_fee['amount']) {
				$post->update_post_meta($male_reg_fee['id'], ['amount' => $male_reg_fee_amount]);
			}
		} else {
			$post->add_post($reg_fees_meta);
		}
		$reg_fees_meta['gender'] = 'Female';
		$female_reg_fee = db_post_unique($reg_fees_meta);
		if ($female_reg_fee) {
			if(($female_reg_fee_amount != $female_reg_fee['amount'])) {
				$post->update_post_meta($female_reg_fee['id'], ['amount' => $female_reg_fee_amount]);
			}
		} else {
			$post->add_post($reg_fees_meta);
		}
		
		$row = 5;
		do {
			$amount = trim($sheetData[$row]["F"]);
			if($amount !== "" && $amount != 0) {
				$args = ['ref' => $templateRef.$sheetData[$row]["C"].$feeid.$term, 'mode' => 'Bank Teller: '.$sheetData[$row]["E"]];
				$args['amount'] = (float) str_replace(",", "", $amount);
				$student_meta = $user->get_user_meta($sheetData[$row]["C"], "username");
				$gender = sfm_convert_gender($student_meta['gender']);
				if(preg_match("/^\d{2}\/\d{2}\/\d{2}$/", $sheetData[$row]["D"])) {
					$args['posted_on'] = DateTime::createFromFormat('d/m/y', $sheetData[$row]["D"])->getTimestamp();
				}
				$amount_due = (float) str_replace(",", "", trim($sheetData[$row]["I"]));
				$new_student = trim($sheetData[$row]["L"]) === "YES" ? true : false;
				$student_update = ['gender' => trim($sheetData[$row]["M"])];
				if($new_student) {
					$student_update['term_of_admission'] = $term;
					$student_update['class_of_admission'] = $class;
				}
				$user->update_user_meta($student_meta['id'], $student_update);
				$ref = $feeid." :: ".$term;
				$invoice = new pm_invoice($student_meta['id']);
				$inv2 = $invoice->item_in_another_invoice($ref);
				if($inv2) {
					$invoice->set_invoice_by_number($inv2->invoice_number);
					if($invoice->status() !== "paid") {
						$item = $invoice->get_item($ref);
						$item_amount = $item ? $item['amount'] : 0;
						$item_amount = (float) $item_amount;
						if($item_amount != $amount_due) {
							$item['amount'] = $amount_due;
							$invoice->update_item($ref, $item);
						}
					}
					
				} else {
					// create invoice
					$invoice->add_item(
						$amount_due,
						$fees[$gender]['description']." - ".get_class_name($class, $category)." (".get_full_term_name($term).")", 
						$fees[$gender]['id'], 
						1, 
						$ref, 
						"_pm_default_success_method",
						"_pm_default_error_method",
						false
					);
					$invoice->save();
				}
				$response = pm_process_add_payment($args, $invoice);
				if($response['error'] === 1) {
					$hasError = true;
					$error .= "<li>Sheet: ".$worksheet." <br/>Row: ".$row." <br/>Student: ".$sheetData[$row]["C"]." <br/>Error Message: ".$response['msg']."</li>";
				} else {
					$success .= "<li>Fee: ".$worksheet." <br/>Term: ".$term_name." <br/>Student: ".$sheetData[$row]["C"]." <br/>Amount: ".toMoney($args['amount'])."</li>";
					$updated++;
				}
			}
			$row++;
		} while(trim($sheetData[$row]["A"]) !== "");
		$success .= "</ol>";
		$error .= "</ol>";
	}
	$report = "<p>Your payment upload was completed successfully.</p>";
	if($updated > 0) {
		$report .= "<h3>The following records were newly updated</h3>";
		$report .= $success."<hr/>";
	}
	if($hasError) {
		$report .= "<h3>The following records generated error messages</h3>";
		$report .= $error;
	}
	inform_user($session->id, $report, "PAYMENT UPLOAD SUCCESSFUL", false, EMAIL);
	$cron->cancel_cron($cron_id);
}

function sfm_convert_gender($gender) {
	switch (strtolower($gender)) {
		case 'male':
		case 'm':
		case '':
		case 'boy':
		case 'man':
			return "Male";
			break;

		case 'female':
		case 'f':
		case 'woman':
		case 'girl':
			return "Female";
			break;
		
		default:
			return "Male";
			break;
	}
}

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