<?php
//require_once('vendor_classes/PHPExcel.php');

class lcs_db_report_xls extends lcs_db_report_base implements report_interface {
	
	protected $objPHPExcel;
	protected $sheet;
	protected $sheet0;
	protected $row = 0;
	protected $col = 0;
	protected $max_row = 0;
	protected $max_col = 0;
	protected $data_start_row = 0;
	protected $style_header = [
		'fill' 	=> ['fillType'	=> \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,	'startColor'	=> ['argb' => '3E4B62']],
		'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 'color' => ['argb' => \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE]]],
		'font' => ['color' => ['argb' => \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE]],
		'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP],
	];
	protected $style_classes = [
		'large' => [
			'fill' 	=> ['fillType'	=> \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,	'startColor'	=> ['argb' => 'FFFFFF']],
			'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 'color' => ['argb' => \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE]]],
			'font' => ['color' => ['argb' => \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK], 'size' => 16],
			'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP],
		],
	];
	
	public function __construct() {
		$this->objPHPExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
		$this->objPHPExcel->getProperties()->setCreator(APP_COMPANY)
									 ->setLastModifiedBy(APP_COMPANY)
									 ->setTitle(APP_COMPANY)
									 ->setSubject(APP_COMPANY)
									 ->setDescription(APP_COMPANY)
									 ->setKeywords(APP_COMPANY)
									 ->setCategory(APP_COMPANY);
	}
	
	public function report_start()
	{
		$this->sheet = $this->objPHPExcel->getActiveSheet();
		$this->sheet0 = $this->objPHPExcel->setActiveSheetIndex(0);
		if ($_POST['orientation'] == 'L') :
			$this->sheet0->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
		else :
			$this->sheet0->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);
		endif;
		$this->data_row_start();
		$this->data_cell_add(APP_COMPANY . ' - ' . APP_TITLE, ['colspan'=>10]);
		$this->data_row_end();
		$this->data_row_start();
		$this->data_cell_add('Report: '.$this->get_report_title()."   -   ".'Report Date: '.date('m/d/Y')."   -   ".'User: '.$_SESSION['user_full_name'], ['colspan'=>10]);
		$this->data_row_end();
		$this->max_col = 0;
	}

	public function report_end()
	{
		$alpha_col_max = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($this->max_col - 1);
		$this->sheet->getStyle('A'.$this->data_start_row.':'.$alpha_col_max.$this->max_row)->applyFromArray([
			'fill' 	=>['fillType'	=> \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,	'startColor'	=> ['argb' => 'ECEDEF']],
			'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 'color' => ['argb' => \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE]]],
		]);
		foreach (range(0, $this->max_col) as $col) :
			$this->sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
		endforeach;
		$this->sheet->setTitle('Report');
		$this->sheet0;
	}

	public function header_start()
	{
	}

	public function header_end()
	{
	}

	public function header_row_start()
	{
		$this->row++;
		$this->col = 1;
		$this->max_row = max($this->max_row, $this->row);
	}

	public function header_row_end()
	{
	}

	public function header_cell_add($data = '', $args = [])
	{
		$this->format_cell($data, $args, $this->style_header);
	}

	public function footer_start()
	{
	}

	public function footer_end()
	{
	}

	public function footer_row_start()
	{
		$this->row++;
		$this->col = 1;
		$this->max_row = max($this->max_row, $this->row);
	}

	public function footer_row_end()
	{
	}

	public function footer_cell_add($data = '', $args = [])
	{
		$this->format_cell($data, $args);
	}

	public function data_start()
	{
		$this->data_start_row = $this->row + 1;
	}

	public function data_end()
	{
	}

	public function data_row_start()
	{
		$this->row++;
		$this->col = 1;
		$this->max_row = max($this->max_row, $this->row);
	}

	public function data_row_end()
	{
	}

	public function data_cell_add($data = '', $args = [])
	{
		$this->format_cell($data, $args);
	}

	public function output_report($filename = '')
	{
		if (APP_EXCEL_FORMAT == 'xls') :
			$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($this->objPHPExcel, 'Xls');
			//$objWriter->setOffice2003Compatibility(true);
			// Redirect output to a client’s web browser (Excel5)
			header('Content-Type: application/vnd.ms-excel');
			header('Content-Disposition: attachment;filename="Report.xls"');
		elseif (APP_EXCEL_FORMAT == 'xlsx') :
			$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($this->objPHPExcel, 'Xlsx');
			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			header('Content-Disposition: attachment;filename="Report.xlsx"');
		endif;
		header("Content-Description: File Transfer");
		header('Content-Transfer-Encoding: binary');
		header('Cache-Control: max-age=0');
		// If you're serving to IE 9, then the following may be needed
		header('Cache-Control: max-age=1');
		// If you're serving to IE over SSL, then the following may be needed
		header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
		header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
		//header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		//header('Expires: 0');
		header ('Pragma: public'); // HTTP/1.0
		$objWriter->save('php://output');
	}
	
	private function format_cell($data, $args, $cell_style = null)
	{
		$attr = '';
		$class = '';
		$colspan = 1;
		$align = 'left';
		foreach ($args as $key => $value) :
			$attr .= ' '.$key.'="'.$value.'"';
			switch ($key) :
				case 'class' :
					$cell_style = $this->style_classes[$value];
					break;
				case 'colspan' :
					$colspan = intval($value);
					break;
				case 'align' :
					$align = $value;
					break;
			endswitch;
		endforeach;
		
		$data = str_ireplace('<br />', '<br>', $data);
		$data = str_ireplace('<br>', "\n", $data);
		if (strpos($data, "\n") !== false) :
			$cell_style['alignment']['wrap'] = true;
		endif;
		//$this->sheet0->setCellValueByColumnAndRow($this->col, $this->row, $data);
		if ($align == 'right' && is_numeric(str_ireplace(',', '', $data))) :
			$data = str_ireplace(',', '', $data);
			$data_type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC;
		else :
			$data_type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING;
		endif;
		if ($align == 'right') :
			$cell_style['alignment']['horizontal'] = \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT;
		elseif ($align == 'center') :
			$cell_style['alignment']['horizontal'] = \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER;
		endif;
		$this->sheet0->setCellValueExplicitByColumnAndRow($this->col, $this->row, html_entity_decode($data, ENT_QUOTES), $data_type);
		if ($cell_style) :
			$this->sheet->getStyleByColumnAndRow($this->col, $this->row)->applyFromArray($cell_style);
		endif;
		if ($colspan > 1) :
			$alpha_col_start = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($this->col);
			$alpha_col_end = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($this->col + $colspan - 1);
			$this->sheet0->mergeCells($alpha_col_start.$this->row.':'.$alpha_col_end.$this->row);
		endif;
		$this->col += $colspan;
		$this->max_col = max($this->max_col, $this->col);
	}
	
}