<?php

include_once("../../formularios/data/connect.php");

if (stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml"))
	header("Content-type: application/xhtml+xml");
else
	header("Content-type: text/xml");
				
$op = $_GET['op'] ? $_GET['op'] : '';
$depto = $_GET['depto'] ? $_GET['depto'] : 0;
$ente = $_GET['ente'] ? $_GET['ente'] : 0;

$filtro = '';
if($depto>0)
{
	$filtro .= " AND P.IdDepartamento = $depto ";
}
if($ente>0)
{
	$filtro .= " AND P.IdEntidad = $ente ";
}

print '<?xml version="1.0" encoding="utf-8"?>'."\n".
		'<rows>'."\n";

switch($op)
{
	case 'costo':
		$query = "SELECT SUM(CASE WHEN E.EvaluacionCosto <= 3 OR E.EvaluacionCosto IS NULL THEN 1 ELSE 0 END) AS red_count,
                 SUM(CASE WHEN E.EvaluacionCosto > 3 AND E.EvaluacionCosto <= 7 THEN 1 ELSE 0 END) AS yellow_count,
                 SUM(CASE WHEN E.EvaluacionCosto > 7 THEN 1 ELSE 0 END) AS green_count,
                 SUM(CASE WHEN E.EvaluacionCosto <= 3 OR E.EvaluacionCosto IS NULL THEN Actual ELSE 0 END) AS red_amount,
                 SUM(CASE WHEN E.EvaluacionCosto > 3 AND E.EvaluacionCosto <= 7 THEN Actual ELSE 0 END) AS yellow_amount,
                 SUM(CASE WHEN E.EvaluacionCosto > 7 THEN Actual ELSE 0 END) AS green_amount 
				  FROM programas P INNER JOIN presupuestos B ON P.IdPrograma = B.IdPrograma LEFT JOIN (SELECT EV.* FROM evaluaciones EV, (SELECT IdPrograma, MAX(Fecha) AS Fecha FROM evaluaciones GROUP BY IdPrograma)E2 WHERE EV.IdPrograma = E2.IdPrograma AND EV.Fecha = E2.Fecha) E ON P.IdPrograma = E.IdPrograma WHERE B.Periodo = ".date('Y')." $filtro";

		$RS = mysql_query($query);
		if(mysql_num_rows($RS))
		{
			while($row = mysql_fetch_array($RS))
			{
				print '<row id="1">
						   <cell>'.$row['green_count'].'</cell>
						   <cell>'.$row['yellow_count'].'</cell>
						   <cell>'.$row['red_count'].'</cell>
						   <cell>'.number_format($row['green_amount'],0).' €</cell>
						   <cell>'.number_format($row['yellow_amount'],0).' €</cell>
						   <cell>'.number_format($row['red_amount'],0).' €</cell>
					   </row>';
			}
		}
		break;

	case 'gene':
		$query = "SELECT SUM(CASE WHEN E.EvaluacionGeneral <=3 OR E.EvaluacionGeneral IS NULL THEN 1 ELSE 0 END) AS red_count,
						 SUM(CASE WHEN E.EvaluacionGeneral >3 AND E.EvaluacionGeneral <7 THEN 1 ELSE 0 END) AS yellow_count,
						 SUM(CASE WHEN E.EvaluacionGeneral >=7 THEN 1 ELSE 0 END) AS green_count,
					 SUM(CASE WHEN E.EvaluacionGeneral <=3 OR E.EvaluacionGeneral IS NULL THEN Actual ELSE 0 END) AS red_amount, 
					 SUM(CASE WHEN E.EvaluacionGeneral >3 AND E.EvaluacionGeneral <7 THEN Actual ELSE 0 END) AS yellow_amount, 
					 SUM(CASE WHEN E.EvaluacionGeneral >=7 THEN Actual ELSE 0 END) AS green_amount 
				  FROM programas P INNER JOIN presupuestos B ON P.IdPrograma = B.IdPrograma LEFT JOIN (SELECT EV.* FROM evaluaciones EV, (SELECT IdPrograma, MAX(Fecha) AS Fecha FROM evaluaciones GROUP BY IdPrograma)E2 WHERE EV.IdPrograma = E2.IdPrograma AND EV.Fecha = E2.Fecha) E ON P.IdPrograma = E.IdPrograma WHERE B.Periodo = ".date('Y')." $filtro";

		$RS = mysql_query($query);
		if(mysql_num_rows($RS))
		{
			while($row = mysql_fetch_array($RS))
			{
				print '<row id="1">
						   <cell>'.$row['green_count'].'</cell>
						   <cell>'.$row['yellow_count'].'</cell>
						   <cell>'.$row['red_count'].'</cell>
						   <cell>'.number_format($row['green_amount'],0).' €</cell>
						   <cell>'.number_format($row['yellow_amount'],0).' €</cell>
						   <cell>'.number_format($row['red_amount'],0).' €</cell>
					   </row>';
			}
		}
		break;

	case 'hist':
		$query = "SELECT Fecha, AVG(EvaluacionCosto) EvaluacionCosto, AVG(EvaluacionPlan) EvaluacionPlan, AVG(EvaluacionResponsable) EvaluacionResponsable, AVG(EvaluacionGeneral) EvaluacionGeneral FROM programas P LEFT JOIN evaluaciones E ON P.IdPrograma = E.IdPrograma WHERE 1=1 $filtro GROUP BY Fecha ORDER BY Fecha DESC";

		$RS = mysql_query($query);
		if(mysql_num_rows($RS))
		{
			while($row = mysql_fetch_array($RS))
			{
				print '<row id="'.$row['Fecha'].'">
						   <cell>'.$row['Fecha'].'</cell>
						   <cell>'.number_format($row['EvaluacionResponsable'],2).'</cell>
						   <cell>'.number_format($row['EvaluacionCosto'],2).'</cell>
						   <cell>'.number_format($row['EvaluacionPlan'],2).'</cell>
						   <cell>'.number_format($row['EvaluacionGeneral'],2).'</cell>
					   </row>';
			}
		}
		break;

	case 'plan':
		$query = "SELECT SUM(CASE WHEN E.EvaluacionPlan > 7 THEN 1 ELSE 0 END) AS green_count,
                 SUM(CASE WHEN E.EvaluacionPlan > 3 AND E.EvaluacionPlan <= 7 THEN 1 ELSE 0 END) AS yellow_count,
                 SUM(CASE WHEN E.EvaluacionPlan <= 3 OR E.EvaluacionPlan IS NULL THEN 1 ELSE 0 END) AS red_count,
                 SUM(CASE WHEN E.EvaluacionPlan > 7 THEN Actual ELSE 0 END) AS green_amount,
                 SUM(CASE WHEN E.EvaluacionPlan > 3 AND E.EvaluacionPlan <= 7 THEN Actual ELSE 0 END) AS yellow_amount,
                 SUM(CASE WHEN E.EvaluacionPlan <= 3 OR E.EvaluacionPlan IS NULL THEN Actual ELSE 0 END) AS red_amount 
				  FROM programas P INNER JOIN presupuestos B ON P.IdPrograma = B.IdPrograma LEFT JOIN (SELECT EV.* FROM evaluaciones EV, (SELECT IdPrograma, MAX(Fecha) AS Fecha FROM evaluaciones GROUP BY IdPrograma)E2 WHERE EV.IdPrograma = E2.IdPrograma AND EV.Fecha = E2.Fecha) E ON P.IdPrograma = E.IdPrograma WHERE B.Periodo = ".date('Y')." $filtro";

		$RS = mysql_query($query);
		if(mysql_num_rows($RS))
		{
			while($row = mysql_fetch_array($RS))
			{
				print '<row id="1">
						   <cell>'.$row['green_count'].'</cell>
						   <cell>'.$row['yellow_count'].'</cell>
						   <cell>'.$row['red_count'].'</cell>
						   <cell>'.number_format($row['green_amount'],0).' €</cell>
						   <cell>'.number_format($row['yellow_amount'],0).' €</cell>
						   <cell>'.number_format($row['red_amount'],0).' €</cell>
					   </row>';
			}
		}
		break;

	case 'resp':
		$query = "SELECT SUM(CASE WHEN E.EvaluacionResponsable <= 2 OR E.EvaluacionResponsable IS NULL THEN 1 ELSE 0 END) AS red_count,
				 SUM(CASE WHEN E.EvaluacionResponsable > 2 AND E.EvaluacionResponsable <= 3.5 THEN 1 ELSE 0 END) AS yellow_count,
                 SUM(CASE WHEN E.EvaluacionResponsable > 3.5 THEN 1 ELSE 0 END) AS green_count,
                 SUM(CASE WHEN E.EvaluacionResponsable <= 2 OR E.EvaluacionResponsable IS NULL THEN Actual ELSE 0 END) AS red_amount,
                 SUM(CASE WHEN E.EvaluacionResponsable > 2 AND E.EvaluacionResponsable <= 3.5 THEN Actual ELSE 0 END) AS yellow_amount,
                 SUM(CASE WHEN E.EvaluacionResponsable > 3.5 THEN Actual ELSE 0 END) AS green_amount 
				  FROM programas P INNER JOIN presupuestos B ON P.IdPrograma = B.IdPrograma LEFT JOIN (SELECT EV.* FROM evaluaciones EV, (SELECT IdPrograma, MAX(Fecha) AS Fecha FROM evaluaciones GROUP BY IdPrograma)E2 WHERE EV.IdPrograma = E2.IdPrograma AND EV.Fecha = E2.Fecha) E ON P.IdPrograma = E.IdPrograma WHERE B.Periodo = ".date('Y')." $filtro";

		$RS = mysql_query($query);
		if(mysql_num_rows($RS))
		{
			while($row = mysql_fetch_array($RS))
			{
				print '<row id="1">
						   <cell>'.$row['green_count'].'</cell>
						   <cell>'.$row['yellow_count'].'</cell>
						   <cell>'.$row['red_count'].'</cell>
						   <cell>'.number_format($row['green_amount'],0).' €</cell>
						   <cell>'.number_format($row['yellow_amount'],0).' €</cell>
						   <cell>'.number_format($row['red_amount'],0).' €</cell>
					   </row>';
			}
		}
		break;
}

print '</rows>';


?>