<?php
/*
   Copyright 2009-2011 United States Government. 

   This software is licensed under the GNU General Public License
   version 2 (see the file LICENSE for details)
*/

/*
 Purpose: Generates XML for treemap data
 URL : http://it.usaspending.gov/content/current-year-fy2011-likely-enacted
 Parameters : NA
 */
include_once("common.inc");
$PREQUEST = purify_n_prepare();

global $div,$compare1,$compare2,$compare3,$b1,$b2,$b3;

$b1=$PREQUEST['b1'];
$b2=$PREQUEST['b2'];
$b3=$PREQUEST['b3'];


$agency=getUserAgency();

header("Content-type: text/xml");


if($b2=='')
	$b2=$b1;
if($b3=='')
	if(b2=='')
		$b3=$b1;
	else
		$b3=$b2;

switch($b1)
{
	case 0:
		$name1='D.Nombre';
		$compare1='D.IdDepartamento';
		break;
	case 1:
		$name1='E.Nombre';
		$compare1='E.IdEntidad';
		break;
	case 2:
		$name1='F.Descripcion';
		$compare1='F.IdFuncion';
		break;
	default:
		$name1='D.Nombre';
		$compare1='D.IdDepartamento';
		break;
}

switch($b2)
{
	case 0:
		$name2='D.Nombre';
		$compare2='D.IdDepartamento';
		break;
	case 1:
		$name2='E.Nombre';
		$compare2='E.IdEntidad';
		break;
	case 2:
		$name2='F.Descripcion';
		$compare2='F.IdFuncion';
		break;
	default:
		$name2='D.Nombre';
		$compare2='D.IdDepartamento';
		break;
}

switch($b3)
{
	case 0:
		$name3='D.Nombre';
		$compare3='D.IdDepartamento';
		break;
	case 1:
		$name3='E.Nombre';
		$compare3='E.IdEntidad';
		break;
	case 2:
		$name3='F.Descripcion';
		$compare3='F.IdFuncion';
		break;
	default:
		$name3='D.Nombre';
		$compare3='D.IdDepartamento';
		break;
}


$xml='<?xml version="1.0" encoding="utf-8"?><!DOCTYPE tree SYSTEM "http://www.nomencurator.org/InfoVis2003/download/treeml.dtd">';

$xml.='<tree><declarations><attributeDecl name="name" type="String"/><attributeDecl name="Total%" type="Real"/><attributeDecl name="Total" type="Real"/><attributeDecl name="DME" type="Real"/><attributeDecl name="DME%" type="Real"/><attributeDecl name="SS" type="Real"/><attributeDecl name="SS%" type="Real"/><attributeDecl name="N Investments" type="Real"/><attributeDecl name="% Total" type="Real"/><attributeDecl name="path1" type="String"/><attributeDecl name="id1" type="Real"/><attributeDecl name="path2" type="String"/><attributeDecl name="id2" type="Real"/><attributeDecl name="path3" type="String"/><attributeDecl name="id3" type="Real"/></declarations>';


	$param_array=array();
	$sql10="select sum(Actual) as totCY, sum(Anterior) as totPY, NULL as DMECY, NULL as DMEPY, NULL as SSCY, NULL as SSPY, count(*) as cnt, SUM(desviacion) desviacion FROM presupuestos B LEFT JOIN (SELECT (SUM(VariacionCosto) / SUM(CostoTotal * PorcentAvanceReal / 100)) desviacion, IdPrograma FROM proyectos Y LEFT JOIN (SELECT PR.* FROM progreso PR, (SELECT IdProyecto, MAX(Fecha) AS Fecha FROM progreso GROUP BY IdProyecto)PR2 WHERE PR.IdProyecto = PR2.IdProyecto AND PR.Fecha = PR2.Fecha) R ON Y.IdProyecto = R.IdProyecto GROUP BY IdPrograma) DV ON B.IdPrograma = DV.IdPrograma where B.Periodo = ".date('Y');

ConnectionUtil::setActiveConnection(DB_ALIAS_STAGING);
$res10=db_query($sql10,$param_array);
ConnectionUtil::releaseActiveConnection();

while($row10=mysql_fetch_array($res10))
{
	$div=$row10['totCY'];
	$xml=calc($row10,0,$xml);
}

	$param_array1=array($name3,$name2,$name1,$compare1,$compare2,$compare3,$compare1,$compare2,$compare3);
	$sql1='SELECT %s as name3,  %s as name2,  %s as name1,  %s as compare1,  %s as compare2,  %s as compare3, sum(Actual) AS totCY, sum(Anterior) AS totPY, NULL AS DMECY, NULL AS DMEPY, NULL AS SSCY, NULL AS SSPY, count(*) AS cnt, SUM(desviacion) desviacion FROM programas P INNER JOIN departamentos D ON P.IdDepartamento = D.IdDepartamento INNER JOIN entidades E ON P.IdDepartamento = E.IdDepartamento AND P.IdEntidad = E.IdEntidad LEFT JOIN funciones F ON P.IdFuncion = F.IdFuncion INNER JOIN presupuestos B ON P.IdPrograma = B.IdPrograma LEFT JOIN (SELECT (SUM(VariacionCosto) / SUM(CostoTotal * PorcentAvanceReal / 100)) desviacion, IdPrograma FROM proyectos Y LEFT JOIN (SELECT PR.* FROM progreso PR, (SELECT IdProyecto, MAX(Fecha) AS Fecha FROM progreso GROUP BY IdProyecto)PR2 WHERE PR.IdProyecto = PR2.IdProyecto AND PR.Fecha = PR2.Fecha) R ON Y.IdProyecto = R.IdProyecto GROUP BY IdPrograma) DV ON B.IdPrograma = DV.IdPrograma where B.Periodo =  ' . date('Y') . ' GROUP BY  %s,  %s,  %s';

	$param_arrayx=array($compare1,$compare2,$compare1,$compare2);
	$sqlx='SELECT %s as compare1, %s as compare2, sum(Actual) AS totCY, sum(Anterior) AS totPY, NULL AS DMECY, NULL AS DMEPY, NULL AS SSCY, NULL AS SSPY, count(*) AS cnt, SUM(desviacion) desviacion FROM programas P INNER JOIN departamentos D ON P.IdDepartamento = D.IdDepartamento INNER JOIN entidades E ON P.IdDepartamento = E.IdDepartamento AND P.IdEntidad = E.IdEntidad LEFT JOIN funciones F ON P.IdFuncion = F.IdFuncion INNER JOIN presupuestos B ON P.IdPrograma = B.IdPrograma LEFT JOIN (SELECT (SUM(VariacionCosto) / SUM(CostoTotal * PorcentAvanceReal / 100)) desviacion, IdPrograma FROM proyectos Y LEFT JOIN (SELECT PR.* FROM progreso PR, (SELECT IdProyecto, MAX(Fecha) AS Fecha FROM progreso GROUP BY IdProyecto)PR2 WHERE PR.IdProyecto = PR2.IdProyecto AND PR.Fecha = PR2.Fecha) R ON Y.IdProyecto = R.IdProyecto GROUP BY IdPrograma) DV ON B.IdPrograma = DV.IdPrograma where B.Periodo =  ' . date('Y') . ' GROUP BY %s, %s';

	$param_arrayz=array($compare1,$compare1);
	$sqlz='SELECT  %s as compare1, sum(Actual) AS totCY, sum(Anterior) AS totPY, NULL AS DMECY, NULL AS DMEPY, NULL AS SSCY, NULL AS SSPY, count(*) AS cnt, SUM(desviacion) desviacion FROM programas P INNER JOIN departamentos D ON P.IdDepartamento = D.IdDepartamento INNER JOIN entidades E ON P.IdDepartamento = E.IdDepartamento AND P.IdEntidad = E.IdEntidad LEFT JOIN funciones F ON P.IdFuncion = F.IdFuncion INNER JOIN presupuestos B ON P.IdPrograma = B.IdPrograma LEFT JOIN (SELECT (SUM(VariacionCosto) / SUM(CostoTotal * PorcentAvanceReal / 100)) desviacion, IdPrograma FROM proyectos Y LEFT JOIN (SELECT PR.* FROM progreso PR, (SELECT IdProyecto, MAX(Fecha) AS Fecha FROM progreso GROUP BY IdProyecto)PR2 WHERE PR.IdProyecto = PR2.IdProyecto AND PR.Fecha = PR2.Fecha) R ON Y.IdProyecto = R.IdProyecto GROUP BY IdPrograma) DV ON B.IdPrograma = DV.IdPrograma where B.Periodo =  ' . date('Y') . ' GROUP BY %s';

ConnectionUtil::setActiveConnection(DB_ALIAS_STAGING);
$resz=db_query($sqlz,$param_arrayz);
ConnectionUtil::releaseActiveConnection();

if($b3==4 || $b2==4 || $b1==4)
{
		$param_array1=array($name3,$name2,$name1,$compare1,$compare2,$compare3,$compare1,$compare2,$compare3);
		$sql1='SELECT %s as name3, %s as name2, %s as name1, Abrev as agName, %s as compare1, %s as compare2, %s as compare3, sum(Actual) AS totCY, sum(Anterior) AS totPY, NULL AS DMECY, NULL AS DMEPY, NULL AS SSCY, NULL AS SSPY, count(*) AS cnt, SUM(desviacion) desviacion FROM programas P INNER JOIN departamentos D ON P.IdDepartamento = D.IdDepartamento INNER JOIN entidades E ON P.IdDepartamento = E.IdDepartamento AND P.IdEntidad = E.IdEntidad LEFT JOIN funciones F ON P.IdFuncion = F.IdFuncion INNER JOIN presupuestos B ON P.IdPrograma = B.IdPrograma LEFT JOIN (SELECT (SUM(VariacionCosto) / SUM(CostoTotal * PorcentAvanceReal / 100)) desviacion, IdPrograma FROM proyectos Y LEFT JOIN (SELECT PR.* FROM progreso PR, (SELECT IdProyecto, MAX(Fecha) AS Fecha FROM progreso GROUP BY IdProyecto)PR2 WHERE PR.IdProyecto = PR2.IdProyecto AND PR.Fecha = PR2.Fecha) R ON Y.IdProyecto = R.IdProyecto GROUP BY IdPrograma) DV ON B.IdPrograma = DV.IdPrograma where B.Periodo =  ' . date('Y') . ' GROUP BY %s, %s, %s';

}


$fl=0;
$fl1=0;
$l2=0;
$l1=0;

ConnectionUtil::setActiveConnection(DB_ALIAS_STAGING);
$res1=db_query($sql1,$param_array1);
ConnectionUtil::releaseActiveConnection();

$i=0;

ConnectionUtil::setActiveConnection(DB_ALIAS_STAGING);
$resx=db_query($sqlx,$param_arrayx);
ConnectionUtil::releaseActiveConnection();

while($rowx=mysql_fetch_array($resx))
{

	$a[$i]['compare1']=$rowx['compare1'];
	$a[$i]['compare2']=$rowx['compare2'];
	$a[$i]['totCY']=$rowx['totCY'];
	$a[$i]['totPY']=$rowx['totPY'];
	$a[$i]['DMECY']=$rowx['DMECY'];
	$a[$i]['DMEPY']=$rowx['DMEPY'];
	$a[$i]['SSCY']=$rowx['SSCY'];
	$a[$i]['SSPY']=$rowx['SSPY'];
	$a[$i]['cnt']=$rowx['cnt'];
	$a[$i]['desviacion']=$rowx['desviacion'];
	$i++;
}

$i=0;

ConnectionUtil::setActiveConnection(DB_ALIAS_STAGING);
$resz=db_query($sqlz,$param_arrayz);
ConnectionUtil::releaseActiveConnection();

while($rowz=mysql_fetch_array($resz))
{

	$b[$i]['compare1']=$rowz['compare1'];
	$b[$i]['totCY']=$rowz['totCY'];
	$b[$i]['totPY']=$rowz['totPY'];
	$b[$i]['DMECY']=$rowz['DMECY'];
	$b[$i]['DMEPY']=$rowz['DMEPY'];
	$b[$i]['SSCY']=$rowz['SSCY'];
	$b[$i]['SSPY']=$rowz['SSPY'];
	$b[$i]['cnt']=$rowz['cnt'];
	$b[$i]['desviacion']=$rowz['desviacion'];
	$i++;
}


while($row1=mysql_fetch_array($res1))
{
$c2=$row1['compare2'];
$c1=$row1['compare1'];
	if($c1!=0)
	{
		if($c1==$l1)
		{
			if($c2==$l2)
			{
				$xml=calc($row1,3,$xml);		//print c3;

			}
			else
			{
				$xml.='</branch>';				//close existing c2 branch
				$i=0;
				$xml=calc2($row1,$a,$xml);			//print c2,c3
				$xml=calc($row1,3,$xml);
				$l2=$row1['compare2'];
			}
		}

		else
		{
			if($fl==1)
				$xml.='</branch></branch>';		//if not first c1 close existing c2,c1
			$xml=calc3($row1,$b,$xml);				//print c1,c2,c3
			$xml=calc2($row1,$a,$xml);
			$xml=calc($row1,3,$xml);
			$l1=$row1['compare1'];
			$l2=$row1['compare2'];
			$fl=1;
		}
	}
}


if($fl==1)
	$xml.='</branch></branch>';



$xml.='</branch></tree>';
echo $xml;



function calc($row1,$lvl,$xml)
{
global $div,$compare1,$compare2,$compare3,$b3;


$per = ($row1['totCY']/$div);


$totper = $row1['desviacion'];
$dmeper = $row1['desviacion'];
$ssper = $row1['desviacion'];

$row1['name3']=str_replace('&','&amp;',$row1['name3']);

if($lvl==3)
{
	if($b3==4)
		$xml.='<leaf><attribute name="name" value="'.$row1["name3"].' ('.$row1["agName"].')"/><attribute name="N Investments" value="'.$row1["cnt"].'"/><attribute name="% Total" value="'.$per.'"/><attribute name="Total%" value="'.$totper.'"/><attribute name="Total" value="'.$row1["totCY"].'"/><attribute name="DME%" value="'.$dmeper.'"/><attribute name="DME" value="'.$row1["DMECY"].'"/><attribute name="SS%" value="'.$ssper.'"/><attribute name="SS" value="'.$row1["SSCY"].'"/><attribute name="path1" value="'.$compare1.'"/><attribute name="id1" value="'.$row1["compare1"].'"/><attribute name="path2" value="'.$compare2.'"/><attribute name="id2" value="'.$row1["compare2"].'"/><attribute name="path3" value="'.$compare3.'"/><attribute name="id3" value="'.$row1["compare3"].'"/></leaf>';
	else
		$xml.='<leaf><attribute name="name" value="'.$row1["name3"].'"/><attribute name="N Investments" value="'.$row1["cnt"].'"/><attribute name="% Total" value="'.$per.'"/><attribute name="Total%" value="'.$totper.'"/><attribute name="Total" value="'.$row1["totCY"].'"/><attribute name="DME%" value="'.$dmeper.'"/><attribute name="DME" value="'.$row1["DMECY"].'"/><attribute name="SS%" value="'.$ssper.'"/><attribute name="SS" value="'.$row1["SSCY"].'"/><attribute name="path1" value="'.$compare1.'"/><attribute name="id1" value="'.$row1["compare1"].'"/><attribute name="path2" value="'.$compare2.'"/><attribute name="id2" value="'.$row1["compare2"].'"/><attribute name="path3" value="'.$compare3.'"/><attribute name="id3" value="'.$row1["compare3"].'"/></leaf>';
}


if($lvl==0)
	$xml.='<branch><attribute name="name" value="Todos los Programas"/><attribute name="N Investments" value="'.$row1["cnt"].'"/><attribute name="% Total" value="'.$per.'"/><attribute name="Total%" value="'.$totper.'"/><attribute name="Total" value="'.$row1["totCY"].'"/><attribute name="DME%" value="'.$dmeper.'"/><attribute name="DME" value="'.$row1["DMECY"].'"/><attribute name="SS%" value="'.$ssper.'"/><attribute name="SS" value="'.$row1["SSCY"].'"/><attribute name="path1" value=""/><attribute name="id1" value=""/><attribute name="path2" value=""/><attribute name="id2" value=""/><attribute name="path3" value=""/><attribute name="id3" value=""/>';

return $xml;

}

function calc2($row1,$a,$xml)
{
	global $div,$compare1,$compare2,$compare3,$b2;
	for($i=0;$i<count($a);$i++)
	{
		if(($row1['compare1']==$a[$i]['compare1']) && ($row1['compare2']==$a[$i]['compare2']))
		{
			$totCY=$a[$i]['totCY'];
			$totPY=$a[$i]['totPY'];
			$DMECY=$a[$i]['DMECY'];
			$DMEPY=$a[$i]['DMEPY'];
			$SSCY=$a[$i]['SSCY'];
			$SSPY=$a[$i]['SSPY'];
			$cnt=$a[$i]['cnt'];
			$desviacion=$a[$i]['desviacion'];


			$per = ($totCY/$div);

			$totper = $desviacion;
			$dmeper = $desviacion;
			$ssper = $desviacion;

		}
	}

	$row1['name2']=str_replace('&','&amp;',$row1['name2']);
	if($b2==4)
		$xml.='<branch><attribute name="name" value="'.$row1['name2'].' ('.$row1['agName'].')"/><attribute name="N Investments" value="'.$cnt.'"/><attribute name="% Total" value="'.$per.'"/><attribute name="Total%" value="'.$totper.'"/><attribute name="Total" value="'.$totCY.'"/><attribute name="DME%" value="'.$dmeper.'"/><attribute name="DME" value="'.$DMECY.'"/><attribute name="SS%" value="'.$ssper.'"/><attribute name="SS" value="'.$SSCY.'"/><attribute name="path1" value="'.$compare1.'"/><attribute name="id1" value="'.$row1['compare1'].'"/><attribute name="path2" value="'.$compare2.'"/><attribute name="id2" value="'.$row1['compare2'].'"/><attribute name="path3" value=""/><attribute name="id3" value=""/>';
	else
		$xml.='<branch><attribute name="name" value="'.$row1['name2'].'"/><attribute name="N Investments" value="'.$cnt.'"/><attribute name="% Total" value="'.$per.'"/><attribute name="Total%" value="'.$totper.'"/><attribute name="Total" value="'.$totCY.'"/><attribute name="DME%" value="'.$dmeper.'"/><attribute name="DME" value="'.$DMECY.'"/><attribute name="SS%" value="'.$ssper.'"/><attribute name="SS" value="'.$SSCY.'"/><attribute name="path1" value="'.$compare1.'"/><attribute name="id1" value="'.$row1['compare1'].'"/><attribute name="path2" value="'.$compare2.'"/><attribute name="id2" value="'.$row1['compare2'].'"/><attribute name="path3" value=""/><attribute name="id3" value=""/>';
	return $xml;
}

function calc3($row1,$b, $xml)
{
global $div,$compare1,$compare2,$compare3,$b1;
	for($i=0;$i<count($b);$i++)
	{
		if($row1['compare1']==$b[$i]['compare1'])
		{
			$totCY=$b[$i]['totCY'];
			$totPY=$b[$i]['totPY'];
			$DMECY=$b[$i]['DMECY'];
			$DMEPY=$b[$i]['DMEPY'];
			$SSCY=$b[$i]['SSCY'];
			$SSPY=$b[$i]['SSPY'];
			$cnt=$b[$i]['cnt'];
			$desviacion=$b[$i]['desviacion'];


			$per = ($totCY/$div);

			$totper = $desviacion;
			$dmeper = $desviacion;
			$ssper = $desviacion;
		
		}
	}

	$row1['name1']=str_replace('&','&amp;',$row1['name1']);
	if($b1==4)
		$xml.='<branch><attribute name="name" value="'.$row1['name1'].' ('.$row1['agName'].')"/><attribute name="N Investments" value="'.$cnt.'"/><attribute name="% Total" value="'.$per.'"/><attribute name="Total%" value="'.$totper.'"/><attribute name="Total" value="'.$totCY.'"/><attribute name="DME%" value="'.$dmeper.'"/><attribute name="DME" value="'.$DMECY.'"/><attribute name="SS%" value="'.$ssper.'"/><attribute name="SS" value="'.$SSCY.'"/><attribute name="path1" value="'.$compare1.'"/><attribute name="id1" value="'.$row1['compare1'].'"/><attribute name="path2" value=""/><attribute name="id2" value=""/><attribute name="path3" value=""/><attribute name="id3" value=""/>';
	else
		$xml.='<branch><attribute name="name" value="'.$row1['name1'].'"/><attribute name="N Investments" value="'.$cnt.'"/><attribute name="% Total" value="'.$per.'"/><attribute name="Total%" value="'.$totper.'"/><attribute name="Total" value="'.$totCY.'"/><attribute name="DME%" value="'.$dmeper.'"/><attribute name="DME" value="'.$DMECY.'"/><attribute name="SS%" value="'.$ssper.'"/><attribute name="SS" value="'.$SSCY.'"/><attribute name="path1" value="'.$compare1.'"/><attribute name="id1" value="'.$row1['compare1'].'"/><attribute name="path2" value=""/><attribute name="id2" value=""/><attribute name="path3" value=""/><attribute name="id3" value=""/>';
	return $xml;
}

?>
