<?php 
	$pid			= $_GET['pid'];
	$lang 			= $_GET['lang'];	
	$det 			= $_GET['det'];		
	$code_categx 	= $_GET['code_categx'];
	$min_datec 		= $_GET['min_datec'];		
	$max_datec 		= $_GET['max_datec'];	
	
	$min_date 		= $_POST['min_date'];		
	$max_date 		= $_POST['max_date'];	

	if ($min_date != "") {
	$min_datec	= substr($min_date,6,4)."-".substr($min_date,3,2)."-".substr($min_date,0,2);
	$max_datec	= substr($max_date,6,4)."-".substr($max_date,3,2)."-".substr($max_date,0,2);
	}
	if ($min_date == "") {
	$min_date	= substr($min_datec,8,2)."-".substr($min_datec,5,2)."-".substr($min_datec,0,4);
	$max_date	= substr($max_datec,8,2)."-".substr($max_datec,5,2)."-".substr($max_datec,0,4);
	}	
	
	
	$repid1 = date("YmdHis");
	$repid2 = date("Y")*date("m")*date("d")+date("s")+rand(10,100);
	$repid3 = date("d")*date("d")*date("s")+date("s")+rand(100,10000);
	$repid = $repid1.$repid2.$repid3;	
	
	
	include('config.php');
	include('field_names.php'); 
	
	
// FIND MONTHS

include('r_dbc.php');

$sqlm = "select count(distinct date_format(calday,'%m-%Y')) as count_months
		from calendar
		where calday >= '".$min_datec."' and calday <= '".$max_datec."'";
$resultm = mysqli_query($conn, $sqlm);		
if (mysqli_num_rows($resultm) > 0) {
      while($rowm = mysqli_fetch_assoc($resultm)) {
		$count_months	= $rowm["count_months"];
		}		
    }	
mysqli_close($conn);

if ($count_months <= 18) 
	{ 
		$dateqry0 = "date_format(calday,'%b-%y')";
		$dateqry01 = "date_format(calday,'%Y%m')";
		$dateqry1 = "date_format(r.rab_date,'%b-%y') as mongt";
		$dateqry11 = "date_format(min(r.rab_date),'%Y%m')";
		$dateqry2 = "date_format(r.rab_date,'%b-%y')"; 
		$title_chart = $title_chart_line1_monthly;
	}
if ($count_months > 18 and $count_months <= 72) 
	{ 
		$dateqry0 = "concat('Q',quarter(calday),'-',date_format(calday,'%Y'))"; 
		$dateqry01 = "concat(date_format(calday,'%Y'),'Q',quarter(calday))";
		$dateqry1 = "concat('Q',quarter(r.rab_date),'-',date_format(r.rab_date,'%Y')) as mongt"; 
		$dateqry1 = "concat('Q',quarter(r.rab_date),'-',date_format(r.rab_date,'%Y')) as mongt"; 		
		$dateqry11 = "concat(date_format(r.rab_date,'%Y'),'Q',quarter(r.rab_date))";		
		$dateqry2 = "quarter(r.rab_date),date_format(r.rab_date,'%Y')"; 
		$title_chart = $title_chart_line1_quarter;
	}
if ($count_months > 72) 
	{ 
		$dateqry0 = "date_format(calday,'%Y')"; 
		$dateqry01 = "date_format(calday,'%Y')";
		$dateqry1 = "date_format(r.rab_date,'%Y') as mongt"; 
		$dateqry1 = "date_format(r.rab_date,'%Y') as mongt"; 		
		$dateqry11 = "date_format(r.rab_date,'%Y')";		
		$dateqry2 = "date_format(r.rab_date,'%Y')"; 
		$title_chart = $title_chart_line1_year;
	}


	
?>

<?php	
// EXPENSES DESC MANAGER
include('r_dbc.php');
$sqlmid = "select desc_manager
			from ccmanager_id 
			where pid='".$pid."' 
			and expenses = 1";

$resultmid = mysqli_query($conn, $sqlmid);

if (mysqli_num_rows($resultmid) > 0) {
      while($rowmid = mysqli_fetch_assoc($resultmid)) {
		$desc_manager_expenses		= $rowmid["desc_manager"];
    }
} 
mysqli_close($conn);
?>

<!DOCTYPE html>
<html>
<head>
<title>
<?php 
	include('title.php'); 
	  $rep_font_size12  = "9px";				// FONT SIZE ON DETAILS OF REPORTS REDUCE ON THIS REPORT
	  $rep_font_size14  = "11px";				// FONT SIZE ON HEADERS OF REPORTS REDUCE ON THIS REPORT
?>
</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
table {
  border-collapse: collapse;
  border-spacing: 0;
  width: 100%;
  border: 0px solid black;
}

th, td {
  font-family:verdana;
  padding: 8px;
  border: 1px solid black;
  font-size: <?php echo $rep_font_size12 ?>;        
}


p.a {
  font-family:verdana;
  text-align:right;
  font-size: <?php echo $rep_font_size14 ?>;        
}

</style>

    <script src="./chartjs/dist/Chart.bundle.js"></script>

</head>
<body>

<p class="a">
<?php
		printf("<b>$htitle</b>");
		printf("<br>");		
		printf("<b>$n_categs_progress $desc_manager_expenses</b>");
		printf("<br>");		
		printf("$nfrom : <b> $min_date </b>");
		printf("<br>");		
		printf("$nto : <b> $max_date</b>");
		printf("<br><br>");				
?>
</p>




<?php

$myear_td = "";

include('r_dbc.php');

$sql = "SELECT DISTINCT ".$dateqry0." as mong
		from calendar
		where calday >= '".$min_datec."' and calday <= '".$max_datec."'		
		order by calday";

$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;background-color:#eaeaea' >$n_categs</th>");
      while($row = mysqli_fetch_assoc($result)) {
			$mong 			= $row["mong"];
			printf("<th style='text-align:center;background-color:#eaeaea' >$mong</th>");
			$myear_td = $myear_td.",'".$mong."'";		
		}		
		printf("<th style='text-align:center;background-color:#eaeaea' >$ntotal</th>");
		printf("</tr>");		

    }
mysqli_close($conn);
?> 


<?php

// CENTERS TOTALS 
include('r_dbc.php');
	$sqlg = "SELECT DISTINCt r.code_categ, c.desc_categ
			from ccmanager r, categs c
			where r.rab_date >= '".$min_datec."' and r.rab_date <= '".$max_datec."'
			and r.pid = c.pid and r.code_categ = c.code_categ
			and r.pid = '".$pid."'
			order by 1";
		$resultg = mysqli_query($conn, $sqlg);
			if (mysqli_num_rows($resultg) > 0) {
				while($rowg = mysqli_fetch_assoc($resultg)) {
				$code_categ 	= $rowg["code_categ"];
				$desc_categ 	= $rowg["desc_categ"];			
		printf("<tr>");
		
		if ($det == '1' && $code_categ == $code_categx) 
			{ 
				$tdbg = ";background-color:#eaeaea"; 
				$ib0 = "<b>"; 
				$ib1 = "</b>"; 
		printf("<tr>");
		printf("<td style='text-align:right' colspan=50>&nbsp</td>");
		printf("</tr>");			
			}
		else { $tdbg = ""; $ib0 = ""; $ib1 = ""; }
		
		printf("<td style='text-align:left$tdbg' ><a href='r_categs_progress_rep.php?pid=$pid&lang=$lang&min_datec=$min_datec&max_datec=$max_datec&code_categx=$code_categ&det=1'>$ib0 $code_categ - $desc_categ $ib1</a></td>");
		
	$sqlm = "select x.mongt,sum(x.mong_total) as mong_totals,x.mindate
			from
			(
			SELECT ".$dateqry1.",sum(r.rab_number) as mong_total,".$dateqry11." as mindate
			from ccmanager r
			where r.pid = '".$pid."'
			and r.mid in (select x.mid from ccmanager_id x where x.pid = r.pid and x.expenses = 1)
			and r.code_categ = '".$code_categ."'
			and r.rab_date >= '".$min_datec."' and r.rab_date <= '".$max_datec."'
			group by ".$dateqry2."
			UNION
			SELECT distinct ".$dateqry0." as mongt,0 as mong_total,".$dateqry01." as mindate
			from calendar
			where calday >= '".$min_datec."' and calday <= '".$max_datec."'
			order by 3
			) as x
			group by x.mindate,x.mongt
			";
			// echo $sqlm;
			// echo '<br><br><br>';
			
				
			
		$resultm = mysqli_query($conn, $sqlm);
			if (mysqli_num_rows($resultm) > 0) {
				
				$rmong_totals = 0;
				
				while($rowm = mysqli_fetch_assoc($resultm)) {
				$mongt 			= $rowm["mongt"];
				$mong_totals 	= $rowm["mong_totals"];
				$rmong_totals	= $rmong_totals + $mong_totals;
				
				if ($mong_totals > 0 || $mong_totals < 0) {		$c_mong_totals	= number_format($mong_totals, $totdc, $septh, $sepdc); }
				if ($mong_totals == 0) {	$c_mong_totals	= ""; }
				
		
					printf("<td style='text-align:right$tdbg' >$ib0 $c_mong_totals $ib1</td>");
				}
				
			$c_rmong_totals	= number_format($rmong_totals, $totdc, $septh, $sepdc);
			printf("<td style='text-align:right$tdbg' >$ib0 $c_rmong_totals $ib1</td>");
				
			}

		printf("</tr>");

  include ('r_categs_progress_rep_det.php');		
			}
			}





mysqli_close($conn);

?>




<?php

// TOTALS MONTHS
include('r_dbc.php');
		printf("<tr>");
		printf("<th style='text-align:left;background-color:#eaeaea' ><a href='r_categs_progress_rep.php?pid=$pid&lang=$lang&min_datec=$min_datec&max_datec=$max_datec'>$ntotal</th>");
		
	$sqls = "select x.mongt as mongtf,sum(x.mong_total) as mong_totalsf,x.mindate as mindatef
			from
			(
			SELECT ".$dateqry1.",sum(r.rab_number) as mong_total,".$dateqry11." as mindate
			from ccmanager r
			where r.pid = '".$pid."'
			and r.mid in (select x.mid from ccmanager_id x where x.pid = r.pid and x.expenses = 1)
			and r.rab_date >= '".$min_datec."' and r.rab_date <= '".$max_datec."'			
			group by ".$dateqry2."
			UNION
			SELECT distinct ".$dateqry0." as mongt,0 as mong_total,".$dateqry01." as mindate
			from calendar
			where calday >= '".$min_datec."' and calday <= '".$max_datec."'
			) as x
			group by x.mindate,x.mongt";

			// echo '<br><br><br>';
			// echo $sqls;
			
			
				
			
		$results = mysqli_query($conn, $sqls);
			if (mysqli_num_rows($results) > 0) {
				
				$rmong_totalsf = 0;
				
				while($rows = mysqli_fetch_assoc($results)) {
				$mongtf 		= $rows["mongtf"];
				$mong_totalsf 	= $rows["mong_totalsf"];
				$rmong_totalsf	= $rmong_totalsf + $mong_totalsf;
				
				if ($mong_totalsf > 0) {	$c_mong_totalsf	= number_format($mong_totalsf, $totdc, $septh, $sepdc); }
				if ($mong_totalsf == 0) {	$c_mong_totalsf	= ""; }

				$mtotalf   = $mtotalf.",".$mong_totalsf;						
				
		
					printf("<th style='text-align:right;background-color:#eaeaea' >$c_mong_totalsf</th>");
				}
				
			
			$c_rmong_totalsf	= number_format($rmong_totalsf, $totdc, $septh, $sepdc);
			printf("<th style='text-align:right;background-color:#eaeaea' >$c_rmong_totalsf</th>");				
				
			}

		printf("</tr>");


mysqli_close($conn);
?>




<?php 

$myear_td 		= "[".substr($myear_td,1)."]";
$mtotalf 		= "[".substr($mtotalf,1)."]";

?>

<tr>
<td style="border: 0px;"></td>
<td style="border: 0px;" colspan=100 >

<?php if ($det == "") { ?>
<canvas id="bar-chart" width=auto height="80"></canvas>
<?php } ?>
</td>
</table>
</div>



<script>
// Bar chart
Chart.defaults.global.defaultFontFamily = "Verdana";
Chart.defaults.global.defaultFontSize = 12;
new Chart(document.getElementById("bar-chart"), {
    type: 'bar',
    data: {
      labels: <?php echo $myear_td ?>,
      datasets: [
        {
          label: "<?php echo $title_categs_progress ?>",
          backgroundColor: ["#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850"],
          data: <?php echo $mtotalf ?>
        }
      ]
    },
    options: {
      legend: { display: false },
      title: {
        display: true,
      }
    }
});
</script>


<?php
	include('system_date.php');	
?>	

</body>
</html>