<?php
	include("../../php_crud/my_class.php");
	$obj = new my_class();
	error_reporting(E_ALL); // Report all types of errors
	ini_set('display_errors', 1); // Display errors on the screen

	require_once('../../asset/vendor/autoload.php');

	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
	use PhpOffice\PhpSpreadsheet\Style\Color;
	use PhpOffice\PhpSpreadsheet\Style\Fill;
	use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
	use PhpOffice\PhpSpreadsheet\Shared\Date;
	use PhpOffice\PhpSpreadsheet\Cell\DataType; 

	extract($_REQUEST);
	$file_name = "ground_water_real_time_station_status";
	$label = "Ground Water Real Time Stations Status Information";
	$where = "STAT.DATA_READ_TYPE IN ('AUTO','BOTH') AND STATUS_TBL.RAW_PARAMETER_ID='474' AND STATUS_TBL.STATION_TYPE='AUTO'";
// print_r($where);

	$hydro_div_label = "";
	$hydro_sub_div_label = "";
	$district_label = "";
	
	if ($hydro_div == "all") {
		$where = "DATA_READ_TYPE IN ('AUTO','BOTH') AND RAW_PARAMETER_ID='474' AND STATION_TYPE='AUTO'";
		$hydro_div_label = "All";
		// print_r($where);
	} else {
		if ($hydro_div != "" && $hydro_sub_div == "all" && $district == "") {
			$hydro_div_info = $obj->Details_By_Cond("HYDROLOGY.DIVCODE_TBL@HYDRO10", "HYDDIVCODE='$hydro_div'");
			$hydro_div_label = $hydro_div_info['DIVISION_NAME'];
			$where = "DATA_READ_TYPE IN ('AUTO','BOTH') AND STATION_TYPE='AUTO' AND RAW_PARAMETER_ID='474' AND HYDDIVCODE='$hydro_div'";
		} else if ($hydro_div != "" && $hydro_sub_div == "all" && $district != "") {
			$hydro_div_info = $obj->Details_By_Cond("HYDROLOGY.DIVCODE_TBL@HYDRO10", "HYDDIVCODE='$hydro_div'");
			$hydro_div_label = $hydro_div_info['DIVISION_NAME'];
			
			$dist_info = $obj->Details_By_Cond("HYDROLOGY.DISTRICT_TBL@HYDRO10", "DISTRICT_NO='$district'");
			$district_label = $dist_info['DISTRICT_NAME'];
			$where = "DATA_READ_TYPE IN ('AUTO','BOTH') AND STATION_TYPE='AUTO' AND RAW_PARAMETER_ID='474' 
			AND HYDDIVCODE='$hydro_div' AND DISTRICT_NO='$district'";
		} else if ($hydro_div != "" && $hydro_sub_div != "" && $district == "") {
			$hydro_div_info = $obj->Details_By_Cond("HYDROLOGY.DIVCODE_TBL@HYDRO10", "HYDDIVCODE='$hydro_div'");
			$hydro_div_label = $hydro_div_info['DIVISION_NAME'];
			
			$hydro_sub_div_info = $obj->Details_By_Cond("HYDROLOGY.GW_SUBDIVCODE_TBL@HYDRO10", "HYDSUBDIVCODE='$hydro_sub_div'");
			$hydro_sub_div_label = $hydro_sub_div_info['SUBDIVISION_NAME'];
			$where = "DATA_READ_TYPE IN ('AUTO','BOTH') AND STATION_TYPE='AUTO' AND RAW_PARAMETER_ID='474' 
			AND HYDDIVCODE='$hydro_div' AND HYDSUBDIVCODE='$hydro_sub_div'";
		} else if ($hydro_div != "" && $hydro_sub_div != "" && $district != "") {
			$hydro_div_info = $obj->Details_By_Cond("HYDROLOGY.DIVCODE_TBL@HYDRO10", "HYDDIVCODE='$hydro_div'");
			$hydro_div_label = $hydro_div_info['DIVISION_NAME'];
			
			$hydro_sub_div_info = $obj->Details_By_Cond("HYDROLOGY.GW_SUBDIVCODE_TBL@HYDRO10", "HYDSUBDIVCODE='$hydro_sub_div'");
			$hydro_sub_div_label = $hydro_sub_div_info['SUBDIVISION_NAME'];
			
			$dist_info = $obj->Details_By_Cond("HYDROLOGY.DISTRICT_TBL@HYDRO10", "DISTRICT_NO='$district'");
			$district_label = $dist_info['DISTRICT_NAME'];
			$where = "DATA_READ_TYPE IN ('AUTO','BOTH') AND STATION_TYPE='AUTO' AND RAW_PARAMETER_ID='474' 
			AND HYDDIVCODE='$hydro_div' AND HYDSUBDIVCODE='$hydro_sub_div' AND DISTRICT_NO='$district'";
		} else {
			$where = "DATA_READ_TYPE IN ('AUTO','BOTH') AND STATION_TYPE='AUTO' AND RAW_PARAMETER_ID='474'";
		}
	}


	$status_label = "";
	if ($sw_status == 0) {
		$where = $where . "";
		$status_label = "Active & Inactive";
	} else if ($sw_status == 3) {
		$where = $where . " AND STATION_STATUS='ACTIVE'";
		$status_label = "Active";
	} else if ($sw_status == 2) {
		$where = $where . " AND STATION_STATUS='INACTIVE' AND DIFF_HOURS<='72' AND DIFF_HOURS>='24'";
		$status_label = "Inactive for 24-72 Hours";
	} else if ($sw_status == 1) {
		$where = $where . " AND STATION_STATUS='INACTIVE' AND DIFF_HOURS>'72'";
		$status_label = "Inactive for more than 72 Hours";
	} else if ($sw_status == 4) {
		$diff_hours_input = $inactive_days * 24;
		$where = $where . " AND STATION_STATUS='INACTIVE' AND DIFF_HOURS>'$diff_hours_input'";
		$status_label = "Inactive for more than $inactive_days days<";
	} else if ($sw_status == 5) {
		$where = $where . " AND STATION_STATUS='INACTIVE'";
		$status_label = "Inactive";
	}  else {
		$where = $where . "";
		$status_label .= "Active & Inactive";
	}
	
	$spreadsheet = new Spreadsheet();
	$sheet = $spreadsheet->getActiveSheet();
	$sheet->mergeCells('A1:H1'); // Example of merging cells
	$sheet->setCellValue('A1',$label);
	$sheet->getStyle('A1:H1')->getAlignment()->setHorizontal('center');
	$sheet->getStyle('A1:H1')->getAlignment()->setWrapText(true);
	$sheet->getStyle('A1:H1')->getFont()->setBold(true);
	
	$sheet->mergeCells('A2:D2'); // Example of merging cells
	$sheet->setCellValue('A2',"Hydro-Division: ".$hydro_div_label);
	$sheet->getStyle('A2:D2')->getAlignment()->setWrapText(true);
	$sheet->getStyle('A2:D2')->getFont()->setBold(true);
	
	$sheet->mergeCells('F2:H2'); // Example of merging cells
	$sheet->setCellValue('F2',"Hydro-Subdivision: ".$hydro_sub_div_label);
	$sheet->getStyle('F2:H2')->getAlignment()->setWrapText(true);
	$sheet->getStyle('F2:H2')->getFont()->setBold(true);
	
	$sheet->mergeCells('A3:D3'); // Example of merging cells
	$sheet->setCellValue('A3',"District: ".$district_label);
	$sheet->getStyle('A3:D3')->getAlignment()->setWrapText(true);
	$sheet->getStyle('A3:D3')->getFont()->setBold(true);
	
	$sheet->mergeCells('F3:H3'); // Example of merging cells
	$sheet->setCellValue('F3',"Station Status: ".$status_label);
	$sheet->getStyle('F3:H3')->getAlignment()->setWrapText(true);
	$sheet->getStyle('F3:H3')->getFont()->setBold(true);
	
	$sheet->setCellValue('A4', 'Sl.');
	$sheet->setCellValue('B4', 'Well ID');
	$sheet->setCellValue('C4', 'District Name');
	$sheet->setCellValue('D4', 'Upazila');
	$sheet->setCellValue('E4', 'Latitude');
	$sheet->setCellValue('F4', 'Longitude');
	$sheet->setCellValue('G4', 'First Received Date');
	$sheet->setCellValue('H4', 'Last Received Date');
	
	$sheet->getStyle('A4:H4')->getAlignment()->setHorizontal('center');;
	$sheet->getStyle('A4:H4')->getFont()->setBold(true);
	
	$row_num = 4;
	$sl = 0;
	foreach ($obj->load_graph_datatable($where) as $value) {
		$sl++;
		$row_num++;
		$well_id = $value['STATION_ID'];
		$rl_id = $value['AUTO_RL'];
		$dist_name = $value['DISTRICT_NAME'];
		$upzl_name = $value['UPZL_NAME'];
		$latitude = $value['AUTO_LAT'];
		$longitude = $value['AUTO_LONG'];
		$first_date_value =  $value['FIRSTRECEIVE_DATE'];
		$last_date_value =  $value['LASTRECEIVE_DATE'];
		
		$first_date_obj = DateTime::createFromFormat('d-M-y',$first_date_value);
		$last_date_obj = DateTime::createFromFormat('d-M-y',$last_date_value);
		
		$current_date_obj = new DateTime();
		$current_date_obj->setTime(0, 0, 0);
		
		$sheet->setCellValue('A'.$row_num, $sl);
		$sheet->setCellValue('B'.$row_num, $well_id);
		$sheet->setCellValue('C'.$row_num, $dist_name);
		$sheet->setCellValue('D'.$row_num, $upzl_name);
		$sheet->setCellValue('E'.$row_num, $latitude);
		$sheet->setCellValue('F'.$row_num, $longitude);
		if ($first_date_obj !== false) {
			$first_date_obj->setTime(0, 0, 0); // Strip the time to 00:00:00

			$excelFirstDate = Date::PHPToExcel($first_date_obj);

			// Set value and format in the sheet
			$sheet->setCellValue('G'.$row_num, $excelFirstDate);
			$sheet->getStyle('G'.$row_num)->getNumberFormat()
				  ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY); // 'dd/mm/yyyy'
		} else {
			// Fallback if format doesn't match
			// It's good to log or handle this case more robustly in a real application
			$sheet->setCellValueExplicit('G'.$row_num, $first_date_value, DataType::TYPE_STRING);
		}
		if ($last_date_obj !== false) {
			$last_date_obj->setTime(0, 0, 0); // Strip the time to 00:00:00
				
			$excelLastDate = Date::PHPToExcel($last_date_obj);
	
			// Set value and format in the sheet
			$sheet->setCellValue('H'.$row_num, $excelLastDate);
			$sheet->getStyle('H'.$row_num)->getNumberFormat()
				  ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY); // 'dd-mmm-yy'
			if ($last_date_obj > $current_date_obj) {
				$sheet->getStyle('H'.$row_num)->getFont()->setBold(true)->getColor()->setRGB(Color::COLOR_RED); 
			}
		} else {
			// Fallback if format doesn't match
			$sheet->setCellValueExplicit('H'.$row_num, $last_date_value, DataType::TYPE_STRING);
		}
	}
	
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="'.$file_name.'.xlsx"');
	header('Cache-Control: max-age=0');

	// If you're using IE9 or earlier
	header('Cache-Control: max-age=1');

	// Disable caching
	header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
	header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
	header('Cache-Control: cache, must-revalidate');
	header('Pragma: public');

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