<?php

namespace App\Exports;

use App\Models\User;
use App\Helpers\Frontend;
use App\Models\Testresult;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromArray;
// use Maatwebsite\Excel\Concerns\WithHeadings;
// use Maatwebsite\Excel\Concerns\FromCollection;

class TestresultExport implements FromArray
{
	use Exportable;
	protected $request;

	public function __construct($request)
	{
		$this->request = $request;
	}

	public function array(): array
	{
		// $schoolCodes = Frontend::get_schools();

		// Students
		$data['subjects'] = [];
		$data['results'] = [];

		// subject
		$subject = User::with('school', 'class', 'section', 'attendance');
		$subject->Join('users_admissions', 'users.user_unique_id', '=', 'users_admissions.user_unique_id');
		$subject->Join('testresults', 'testresults.users_admission_id', '=', 'users_admissions.id');

		// result
		$query = User::with('school', 'class', 'section', 'attendance');
		$query->Join('guardians_users', 'guardians_users.user_id', '=', 'users.id');
		$query->Join('guardians', 'guardians.id', '=', 'guardians_users.guardian_id');
		$query->Join('users_admissions', 'users.user_unique_id', '=', 'users_admissions.user_unique_id');
		$query->Join('testresults', 'testresults.users_admission_id', '=', 'users_admissions.id');

		if (!empty($this->request->roll_no)) {
			$query->where('users_admissions.enrollment_no', 'like', '%' . $this->request->roll_no . '%');
			$subject->where('users_admissions.enrollment_no', 'like', '%' . $this->request->roll_no . '%');
		}

		if (!empty($this->request->school) && !empty($this->request->class)) {

			// School filter
			$query->whereHas('school', function ($q) {
				$q->where('schools.id', $this->request->school);
			});

			// Class filter
			$query->whereHas('class', function ($q) {
				$q->where('classes.id', $this->request->class);
			});

			// Subject School filter
			$subject->whereHas('school', function ($q) {
				$q->where('schools.id', $this->request->school);
			});

			// Subject Class filter
			$subject->whereHas('class', function ($q) {
				$q->where('classes.id', $this->request->class);
			});

			// Optional Section Filter ===================
			if (!empty($this->request->section)) {
				// dd($request->section);
				$query->whereHas('section', function ($q) {
					$q->where('sections.id', $this->request->section);
				});
			}

			// Date optional =======================
			if (!empty($this->request->date)) {
				$date = explode(',', $this->request->date);
				$from = date("Y-m-d", strtotime(trim($date[0])));
				$to = date("Y-m-d", strtotime(trim($date[1])));
				$query->whereBetween('testresults.date', [$from, $to]);
			}

			$subject->groupBy('testresults.testcode');

			if (!empty($this->request->date)) {
				$subject->whereBetween('testresults.date', [$from, $to]);
			}

			$subjects = $subject->get(['testresults.testcode', 'testresults.max_marks', 'users_admissions.enrollment_no', DB::raw('date(testresults.date) as date'), 'testresults.created_at'])->toArray();

			$query->groupBy('testresults.users_admission_id');
			$results  = 	$query->get(['users.id as user_id', 'users.name', 'users.user_unique_id', 'users_admissions.id as admissionId', 'users_admissions.enrollment_no', 'users.phone', 'users_admissions.previous_admission_no', 'guardians.guardian_name', 'testresults.testcode'])->toArray();

			$clas = Frontend::getClass($this->request->class ?? '');
			$section = Frontend::getSection($this->request->section ?? '');

			$title = $clas->class;
			if (!empty($section)) {
				$title .= " - " . $section->section;
			}

			$row = array();

			$_date[] = "-";
			$_date[] = $title;
			$_date[] = "-";
			$_date[] = "-";
			$_date[] = "-";
			$_date[] = "-";
			$_date[] = "DATE";

			$head[] = "-";
			$head[] = "-";
			$head[] = "-";
			$head[] = "-";
			$head[] = "-";
			$head[] = "-";
			$head[] = "TEST CODE";
			$max_marks[] = "-";
			$max_marks[] = "-";
			$max_marks[] = "-";
			$max_marks[] = "-";
			$max_marks[] = "-";
			$max_marks[] = "-";
			$max_marks[] = "MARKS";
			$head1[] = 'SNO';
			$head1[] = 'OLD ADMISSION CODE';
			$head1[] = 'BATCH';
			$head1[] = 'CODE';
			$head1[] = 'NAME';
			$head1[] = 'MOBILE';
			$head1[] = 'FATHER NAME';
			foreach ($subjects as $subject) {
				$_date[] = $subject['date'];
				$head[] = $subject['testcode'];
				$max_marks[] = $subject['max_marks'];
				$head1[] = 'MARKS';
			}
			$head1[] = 'TOTAL TEST';
			$head1[] = 'TOTAL ABSENT';
			$head1[] = 'TOTAL PRESENT';
			$head1[] = '(%) OF PRESENT';
			$head1[] = 'TOTAL OBTAIN';
			$head1[] = 'TOTAL MARKS';
			$head1[] = '(%)AGE';

			array_push($row, $_date);
			array_push($row, $head);
			array_push($row, $max_marks);
			array_push($row, $head1);

			$resultArr[] = array();
			$i = 1;
			foreach ($results as  $result) {
				$resultData = array();
				$resultData[] = $i++;
				$resultData[] = $result['previous_admission_no'];
				$resultData[] = $result['enrollment_no'];
				$resultData[] = $result['admissionId'];
				$resultData[] = $result['name'];
				$resultData[] = $result['phone'];
				$resultData[] = $result['guardian_name'];
				$totalObtain = 0;
				foreach ($subjects ?? '' as $subject) {
					
					$marks = Frontend::getUserMarks($result['admissionId'], $subject['testcode']);
					$resultData[] = $marks ?? '';
					$totalObtain = $totalObtain + (int) $marks ?? '';
				}
				$mark = 0;


				foreach ($subjects ?? '' as $subject) {
					$marks = Frontend::getUserMarks($result['admissionId'], $subject['testcode']);
					if ($marks != '---') {
						$mark;
						$mark++;
					}
				}
				$resultData[] = $mark;
				$absent = 0;

				foreach ($subjects ?? '' as $subject) {
					$___absent = Frontend::getUserAbsent($result['admissionId'], $subject['testcode']);

					$absent += $___absent;
				}

				$resultData[] = $absent ?? '';


				$present = 0;

				foreach ($subjects ?? '' as $subject) {
					$___present = Frontend::getUserPresent($result['admissionId'], $subject['testcode']);
					if ($___present !== '---') {
						$present += 1;
					}
				}

				$resultData[] = $present ?? '';


				if ($mark > 0) {
					$resultData[] = round(($present * 100) / $mark, 2);
				} else {
					$resultData[] = 0;
				}

				$resultData[] = $totalObtain;
				$_max_marks = 0;

				foreach ($subjects ?? '' as $subject) {
					$max_marks = Frontend::getUserTotalMarks($result['admissionId'], $subject['testcode']);

					$_max_marks = $_max_marks + (int) $max_marks;
				}


				$resultData[] =	$_max_marks;

				if ($_max_marks > 0) {
					$resultData[] = round(($totalObtain * 100) / $_max_marks, 2);
				} else {
					$resultData[] = 0;
				}
				$resultArr[] = $resultData;
				// SNO
			}
			array_push($row, $resultArr);
			return $row;
		}
	}
}