<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
table {
  border-collapse: collapse;
  border-spacing: 0;
  width: 100%;
  border: 1px solid black;
}

th, td {
  font-family:verdana;
  padding: 8px;
  border: 1px solid black;
}

tr:nth-child(even){background-color: #f2f2f2}
</style>
</head>
<body>

<?php

	$pid	= $_GET['pid'];
require_once('field_names.php');
require_once('r_dbc.php');

$sql = "SELECT x.ordr,x.ordr2,x.code_center,x.desc_center,sum(x.budget) as budget,sum(x.expenses) as expenses
		from (
		SELECT '01' as ordr,r.code_center as ordr2,r.code_center,g.desc_center,sum(r.rab_number) as budget,0 as expenses
		from ccmanager r, centers g
		where r.code_center = g.code_center
		and r.pid = g.pid
		and g.pid = ".$pid."
		and r.mid = 1
		group by r.code_center
		UNION 
		SELECT '02' as ordr,concat(c.code_center_group,'.9999') as ordr2,c.code_center_group as code_center,concat('TOTAL ',g.desc_center_group) as desc_center,sum(r.rab_number) as budget,0 as expenses
		from ccmanager r, centers c, centers_groups g
		where r.code_center = c.code_center
		and r.pid = c.pid
		and c.code_center_group = g.code_center_group
		and r.pid = g.pid
		and g.pid = ".$pid."		
		and r.mid = 1		
		group by c.code_center_group		
		UNION 
		SELECT '03' as ordr,'9999.9999' as ordr2,'TOTAL' as code_center,'' as desc_center,sum(r.rab_number) as budget,0 as expenses
		from ccmanager r
		where r.mid = 1
		and r.pid = ".$pid."		
		UNION
		SELECT '01' as ordr,r.code_center as ordr2,r.code_center,g.desc_center,0 as budget,sum(r.rab_number) as expenses
		from ccmanager r, centers g
		where r.code_center = g.code_center
		and r.pid = g.pid
		and g.pid = ".$pid."		
		and r.mid = 2
		group by r.code_center
		UNION 
		SELECT '02' as ordr,concat(c.code_center_group,'.9999') as ordr2,c.code_center_group as code_center,concat('TOTAL ',g.desc_center_group) as desc_center,0 as budget,sum(r.rab_number) as expenses
		from ccmanager r, centers c, centers_groups g
		where r.code_center = c.code_center
		and c.code_center_group = g.code_center_group
		and r.pid = c.pid
		and c.code_center_group = g.code_center_group
		and r.pid = g.pid
		and g.pid = ".$pid."				
		and r.mid = 2		
		group by c.code_center_group		
		UNION 
		SELECT '03' as ordr,'9999.9999' as ordr2,'TOTAL' as code_center,'' as desc_center,0 as budget,sum(r.rab_number) as expenses
		from ccmanager r
		where r.mid = 2
		and r.pid = ".$pid."		
		) as x
		group by x.ordr,x.ordr2,x.code_center,x.desc_center
		order by x.ordr2";
		
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
	printf("<div style='overflow-x:auto;'>");
	printf("<table>");
		printf("<tr>
			<th style='text-align:center' >Group</td>
			<th style='text-align:center' >$ndesc</td>
			<th style='text-align:center' >A.Budget</td>
			<th style='text-align:center' >B.Expenses</td>
			<th style='text-align:center' >A-B</td>			
			</tr>");	
      while($row = mysqli_fetch_assoc($result)) {
		$ordr 			= $row["ordr"];
        $group 			= $row["code_center"];
		$desc_center 	= $row["desc_center"];
		$budget 		= $row["budget"];	
		$expenses 		= $row["expenses"];			
		$diff 			= $budget - $expenses;			

		$c_budget		= number_format($budget, 2, ",", ".");									
		$c_expenses		= number_format($expenses, 2, ",", ".");											
		$c_diff			= number_format($diff, 2, ",", ".");													
		
		if ($ordr == '01') {
		printf("<tr>
			<td style='text-align:right' >$group</td>
			<td style='text-align:left'  >$desc_center</td>
			<td style='text-align:right' >$c_budget</td>
			<td style='text-align:right' >$c_expenses</td>
			<td style='text-align:right' >$c_diff</td>			
			</tr>");
		}
		if ($ordr == '02' || $ordr == '03') {
		printf("<tr>
			<th style='text-align:right' >$group</th>
			<th style='text-align:left'  >$desc_center</th>
			<th style='text-align:right' >$c_budget</th>
			<th style='text-align:right' >$c_expenses</th>			
			<th style='text-align:right' >$c_diff</th>						
			</tr>");
		}		
    }
} 

mysqli_close($conn);
?> 

</table>
</div>

</body>
</html>