<?php
class Informe{

	private $f_inicio = null;
	private $f_termino = null;
	private $est_id = null;

	function __construct($f_inicio,$f_termino,$est_id){
		$f_termino = explode('-', $f_termino);
		$this->f_inicio = $f_inicio ;
		$this->f_termino = date('H:i:s d-m-Y',mktime(23,59,59,$f_termino[1],$f_termino[0],$f_termino[2]));
		$this->est_id = $est_id;
		$this->cn = conectarDB();
	}

	public function informe_11(){
		$query = 'SELECT DATE_FORMAT(cli_entrada, "%d-%m-%Y") AS fecha, COUNT(cli_patente) AS cant_cr, COUNT(DISTINCT cli_patente) AS cant_sr
					FROM cliente_estacionado LEFT JOIN usu_registra_cli ON cli_id = cliente_estacionado_cli_id
					WHERE cli_entrada >= STR_TO_DATE(?, "%d-%m-%Y") AND cli_entrada <= STR_TO_DATE(?, "%H:%i:%s %d-%m-%Y") AND estacionamiento_est_id = ?
					GROUP BY fecha
					ORDER BY cli_entrada ASC;';
		$rs = $this->cn->Execute($query,array($this->f_inicio,$this->f_termino,$this->est_id));
		if(!empty($rs) && !$rs->EOF){
			$array = $rs->getArray();
			$rs->Close();
			return $array;
		}
		return false;
	}

	public function informe_12(){
		$query = 'SELECT COUNT(DISTINCT cli_patente) AS cant_sr, COUNT(cli_patente) AS cant_cr
					FROM cliente_estacionado LEFT JOIN usu_registra_cli ON cli_id = cliente_estacionado_cli_id
					WHERE cli_entrada >= STR_TO_DATE(?, "%d-%m-%Y") AND cli_entrada <= STR_TO_DATE(?, "%H:%i:%s %d-%m-%Y") AND estacionamiento_est_id = ?;';
		$rs = $this->cn->Execute($query,array($this->f_inicio,$this->f_termino,$this->est_id));
		if(!empty($rs) && !$rs->EOF){
			$array = $rs->getArray();
			$rs->Close();
			return $array[0];
		}
		return false;
	}

	public function informe_21(){
		$query = 'SELECT DATE_FORMAT(cli_entrada,"%d-%m-%Y") AS fecha, COUNT(cli_id) AS cant, ROUND(AVG(TIME_TO_SEC(TIMEDIFF(cli_salida,cli_entrada))/(60))) AS minutos_promedio
					FROM cliente_estacionado LEFT JOIN usu_registra_cli ON cli_id = cliente_estacionado_cli_id
					WHERE cli_entrada IS NOT NULL AND cli_salida IS NOT NULL AND cli_entrada >= STR_TO_DATE(?, "%d-%m-%Y") AND cli_entrada <= STR_TO_DATE(?, "%H:%i:%s %d-%m-%Y") AND estacionamiento_est_id = ?
					GROUP BY fecha ORDER BY cli_entrada ASC;';
		$rs = $this->cn->Execute($query,array($this->f_inicio,$this->f_termino,$this->est_id));
		if(!empty($rs) && !$rs->EOF){
			$array = $rs->getArray();
			$rs->Close();
			return $array;
		}
		return false;
	}

	public function informe_22($f1,$f2){
		$query = 'SELECT ? AS fecha, COUNT(*) AS cant
					FROM cliente_estacionado
					WHERE estacionamiento_est_id = ? AND cli_entrada IS NOT NULL AND cli_salida IS NOT NULL AND cli_entrada < STR_TO_DATE(?,"%H:%i:%s %d-%m-%Y") AND cli_salida >= STR_TO_DATE(?,"%H:%i:%s %d-%m-%Y");';
		$rs = $this->cn->Execute($query,array($f1,$this->est_id,$f2,$f1));
		if(!empty($rs) && !$rs->EOF){
			$array = $rs->getArray();
			$rs->Close();
			return $array[0];
		}
		return false;
	}

	public function informe_3(){
		$query = 'SELECT DATE_FORMAT(cli_entrada,"%d-%m-%Y") AS fecha, CONCAT(CONCAT(ROUND((@horas := TIME_TO_SEC(TIMEDIFF(cli_salida,cli_entrada))/(60))/60),":"),IF(TRUNCATE(@horas%60,0)<10,CONCAT("0",TRUNCATE(@horas%60,0)),TRUNCATE(@horas%60,0))) AS horas_efectivas
					FROM cliente_estacionado LEFT JOIN usu_registra_cli ON cli_id = cliente_estacionado_cli_id
					WHERE cli_entrada IS NOT NULL AND cli_salida IS NOT NULL AND cli_entrada >= STR_TO_DATE(?, "%d-%m-%Y") AND cli_entrada <= STR_TO_DATE(?, "%H:%i:%s %d-%m-%Y") AND estacionamiento_est_id = ?
					GROUP BY fecha ORDER BY cli_entrada ASC;';
		$rs = $this->cn->Execute($query,array($this->f_inicio,$this->f_termino,$this->est_id));
		if(!empty($rs) && !$rs->EOF){
			$array = $rs->getArray();
			$rs->Close();
			return $array;
		}
		return false;
	}

	public function tabla($thead,$tbody){
		$tabla = '<table cellspacing="0" cellpadding="5" border="1">';
		$tabla .= '<thead>';
		$tabla .= '<tr>';
		foreach($thead as $valor){
			$tabla .= '<th>'.$valor.'</th>';
		}
		$tabla .= '</tr>';
		$tabla .= '</thead>';

		$tabla .= '<tbody>';
		foreach($tbody as $tr){
			$tabla .= '<tr>';
			foreach($tr as $valor){
				$tabla .= '<td>'.$valor.'</td>';
			}
			$tabla .= '</tr>';
		}
		$tabla .= '</tbody>';
		$tabla .= '</table>';
		return $tabla;
	}

	function __destruct(){
		$this->cn->Close();
	}
}
?>