<?php 
//incluir la conexion de base de datos
require_once(LIB_PATH_INC.'load.php');

//Cambiar la hora en curdate -INTERVAL 0.45 DAY
function totalserviciohoy(){
	global $db;
	$sql="SELECT COUNT(id) as total FROM programa as p WHERE DATE(p.ProFechaRegistro )=curdate()-INTERVAL 0.4 DAY";
	$query= $db->query($sql);
	return $query;
}
//Cambiar la hora en curdate -INTERVAL 0.45 DAY
function totalpredioshoy(){
	global $db;
	$sql="SELECT COUNT(id) as total FROM operacion as o WHERE DATE(o.FechaRegistroOperacion )=curdate()-INTERVAL 0.4 DAY";
	$query= $db->query($sql);
	return $query;
}

function ultimos_7dias(){
	global $db;
	$sql="SELECT DATE_FORMAT(ProFechaRegistro,'%d/%m/%Y') as fecha,COUNT(id) as total FROM programa where ProFechaRegistro BETWEEN date_add(NOW(), INTERVAL -7 DAY) AND NOW() GROUP BY DATE_FORMAT(ProFechaRegistro, '%d/%m/%Y')";
	$query= $db->query($sql);
	return $query;
}

function ultimos_12meses(){
	global $db;
	$sql="SELECT DATE_FORMAT(FechaRegistroOperacion,'%d/%m/%Y') as fecha,COUNT(id) as total FROM operacion where FechaRegistroOperacion BETWEEN date_add(NOW(), INTERVAL -7 DAY) AND NOW() GROUP BY DATE_FORMAT(FechaRegistroOperacion, '%d/%m/%Y')";
	$query= $db->query($sql);
	return $query;
}
//SELECT ELT(MONTH(fecha), 'Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 'Julio', 'Agosto', 'Septiembre','Octubre', 'Noviembre', 'Diciembre') as fecha,SUM(suma.uno*suma.dos) as total FROM ( SELECT c.created_at AS fecha,sr.q as uno, s.price AS dos from servicios as s INNER JOIN servicios_realizados AS sr ON s.id = sr.servicio_id INNER JOIN clientes as c on sr.cliente_id = c.id UNION ALL SELECT c.created_at as fecha, pr.year as uno,pr.q_predio as dos from predio_realizados AS pr INNER JOIN clientes as c ON pr.cliente_id = c.id ) suma GROUP BY Month(fecha) DESC LIMIT 0,12
 ?>

