<?php

include_once '../session/admin-session.php';
include_once '../classes/class_admin.php';
include_once '../classes/validation.php';
include_once '../classes/common.php';
include_once 'read-configration.php';

require '../vendor/autoload.php'; // Composer autoloader

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

ob_start();

$ObjDB = new class_db();
$objadm = new class_admin();
$val = new validation();

// create spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$output = '';
$i = 1;
$for_sem = $current_sem_num;

$query_str = "select * from isr_sub_available_tbl where b1_sem = '$for_sem'  order by sub_code";
$ObjDB->sproc_name = $query_str;
$result_str = $ObjDB->SelectQuery();

while ($row = mysqli_fetch_array($result_str)) {
    $sub_code = $row['sub_code'];
    $qlist = "select a.stu_roll, b.name, b.email from isr_reg_b19_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_b14_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_b15_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_b16_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_b17_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_b18_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	
	select a.stu_roll, b.name, b.email from isr_reg_r14_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_r12_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_r13_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_m1_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_m2_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	UNION
	select a.stu_roll, b.name, b.email from isr_reg_m3_tbl a, isr_stu_data_more_tbl b where a.stu_roll = b.roll and a.b1_sem = '$for_sem' and a.sub_code = '$sub_code' and a.sub_flag = 'Y'
	order by stu_roll";
   
  // echo "<br/><br/>";

    $ObjDB->sproc_name = $qlist;
    $result_str2 = $ObjDB->SelectQuery();

    while ($row2 = mysqli_fetch_array($result_str2)) {
        $stu_roll = $row2['stu_roll'];
        $name = $row2['name'];
        $email = $row2['email'];

        $sheet->setCellValue('A' . $i, $i);
        $sheet->setCellValue('B' . $i, $sub_code);
        $sheet->setCellValue('C' . $i, $stu_roll);
        $sheet->setCellValue('D' . $i, $name);
        $sheet->setCellValue('E' . $i, $email);

        $i++;
    }
}
$filename = 'REG_LIST.xlsx';


header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"$filename\"");
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
 
exit;
?>