se/SE/superedit-USERS2_MARKETING_STATS_CURRENT_COST_VIEW.php

<?php

function USERS2_MARKETING_STATS_CURRENT_COST_VIEW() {
	$db = DB::getDB();
	$sql = "
		select s.`PAY_RANGE_LIMIT`, s.`NET`, s.`NET_plus_TEL_or_TV`
		from `USERS2_MARKETING_STATS_CURRENT_COST_VIEW` as s
	";
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		$stats[] = $r;
	}

	$zasobInfo = null;
	$sql = "
		select z.`DESC`, z.`DESC_PL`, z.`OPIS`
		from `CRM_LISTA_ZASOBOW` z
		where z.`DESC`='index.php?MENU_INIT=USERS2_MARKETING_STATS_CURRENT_COST_VIEW'
	";
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		$zasobInfo = $r;
	}

	SE_Layout::menu();
	?>
<div class="container">
		<?php if ($zasobInfo) : ?>
			<h1><?php echo $zasobInfo->DESC_PL; ?>
				<br><small><?php echo $zasobInfo->OPIS; ?></small>
			</h1>
		<?php endif; ?>
		<script src="stuff/RGraph/libraries/RGraph.common.core.js"></script>
		<script src="stuff/RGraph/libraries/RGraph.common.dynamic.js"></script>
		<script src="stuff/RGraph/libraries/RGraph.common.key.js"></script>
		<script src="stuff/RGraph/libraries/RGraph.drawing.rect.js"></script>
		<script src="stuff/RGraph/libraries/RGraph.common.tooltips.js"></script>
		<script src="stuff/RGraph/libraries/RGraph.bar.js"></script>
		<script src="stuff/RGraph/libraries/RGraph.hbar.js"></script>
		<script src="stuff/RGraph/libraries/RGraph.line.js"></script>

<?php
	$wykresWidth = 940;
	$wykresHeight = 300;
	$jsonLabels = array();
	$jsonColors = array();
	$jsonData = array();
	$jsonTooltips = array();

	$jsonColors = array('Gradient(#fe783e:#EC561B:#F59F7D)', 'Gradient(#99f:#27afe9:#058DC7:#058DC7)');
    $jsonKeys = array('NET', 'NET plus TEL lub TV');
	// , 'Gradient(#94f776:#50B332:#B1E59F)', 'Gradient(#CC76F7:#8832B3:#CE9FE5)', 'Gradient(#768CF7:#3247B3:#9FABE5)', 'Gradient(#F77681:#B3323C:#E59FA5)');
	foreach ($stats as $vSalesByRangeLimit) {
		$payRangeLimit = $vSalesByRangeLimit->PAY_RANGE_LIMIT;
		$lastPayRangeLimit = $payRangeLimit - 10;
		$jsonPriceDataByRangeLimit = array();

		$vValue = $vSalesByRangeLimit->NET;
		$jsonPriceDataByRangeLimit["NET-{$lastPayRangeLimit}-{$payRangeLimit}"] = (int)$vValue;
		$kLabel = 'NET';
		$jsonTooltips[] = "Ilość klientów płącących za {$kLabel} w przedziale od {$lastPayRangeLimit}zł do {$payRangeLimit}zł: {$vValue}";

		$vValue = $vSalesByRangeLimit->NET_plus_TEL_or_TV;
		$jsonPriceDataByRangeLimit["NET-PLUS-{$lastPayRangeLimit}-{$payRangeLimit}"] = (int)$vValue;
		$kLabel = 'NET plus TEL lub TV';
		$jsonTooltips[] = "Ilość klientów płącących za {$kLabel} w przedziale od {$lastPayRangeLimit}zł do {$payRangeLimit}zł: {$vValue}";

		$jsonData[] = array_values($jsonPriceDataByRangeLimit);
		$jsonLabels[] = "od {$lastPayRangeLimit}zł do {$payRangeLimit}zł";
	}

	$wykresMinItemHeight = 14;
	$wykresTestHeight = count($stats) * 3 * $wykresMinItemHeight;
	if ($wykresTestHeight > $wykresHeight - 60 - 20) {
		$wykresHeight = $wykresTestHeight + 60 + 20;
	}
	$jsonColorsAll = array();
	for ($i = 0, $mi = 0, $mc = count($jsonColors); $i < 12; $i++) {
		$jsonColorsAll[] = $jsonColors[$mi];
		if (++$mi >= $mc) {
			$mi = 0;
		}
	}
	$jsonColors = $jsonColorsAll;
?>
<!--	<div style="width:100%; margin:10px 0; border:1px solid gray; overflow:auto"> -->
	<div style="margin:10px 0;">
		<canvas id="wykres-stats" width="<?php echo $wykresWidth; ?>" height="<?php echo $wykresHeight; ?>">[No canvas support]</canvas>
	</div>
		<script>
jQuery(document).ready(function(){
//			.Set('hmargin', 0)
		var bar = new RGraph.HBar('wykres-stats', <?php echo json_encode($jsonData); ?>)
			.Set('labels', <?php echo json_encode($jsonLabels); ?>)
			.Set('colors', <?php echo json_encode($jsonColors); ?>)
            .Set('key', <?php echo json_encode($jsonKeys); ?>)
			.Set('strokestyle', 'white')
			.Set('linewidth', 1)
			.Set('shadow', true)
			.Set('shadow.color', '#ccc')
			.Set('shadow.offsetx', 0)
			.Set('shadow.offsety', 0)
			.Set('shadow.blur', 10)
//			.Set('gutter.right', 20)
			.Set('gutter.bottom', 30)
			.Set('gutter.left', 160)
//			.Set('text.angle', 90)
			.Set('tooltips', RGraph.ISOLD ? null : <?php echo json_encode($jsonTooltips); ?>)
			.Set('tooltips.event', 'click')
			.Set('labels.above', true)
//			.Set('hmargin', 10)
			.Set('vmargin', 10)
//			.Set('background.grid.dotted', true)
			.Set('background.grid.autofit', true)
			.Set('background.grid.autofit.numhlines', <?php echo count($stats); ?>)
			.Draw();
});
		</script>

	<table class="table table-bordered" style="width:auto">
		<tr>
			<th>przedział cenowy</th>
			<th>ilość klientów płacących za NET</th>
			<th>ilość klientów płacących za NET plus TEL lub TV</th>
		</tr>
		<?php foreach ($stats as $vStat) : ?>
			<?php $lastRangeLimit = $vStat->PAY_RANGE_LIMIT - 10; ?>
			<tr>
				<td>od <?php echo $lastRangeLimit; ?>zł do <?php echo $vStat->PAY_RANGE_LIMIT; ?>zł </td>
				<td><?php echo $vStat->NET; ?></td>
				<td><?php echo $vStat->NET_plus_TEL_or_TV; ?></td>
			</tr>
		<?php endforeach; ?>
	</table>
</div>
	<?php

	//echo '<pre>';print_r($stats);echo'</pre>';// TODO: RMME

	$sqlCreateView = "
		CREATE VIEW `USERS2_MARKETING_STATS_CURRENT_COST_VIEW` AS
			select `USERS2_MARKETING_STATS_CURRENT_COST_TEMP_VIEW`.`PAY` AS `PAY_RANGE_LIMIT`
				, sum(if((`USERS2_MARKETING_STATS_CURRENT_COST_TEMP_VIEW`.`SERVICES` = 'I'),1,0)) AS `NET`
				, sum(if((`USERS2_MARKETING_STATS_CURRENT_COST_TEMP_VIEW`.`SERVICES` = 'ITV'),1,0)) AS `NET_plus_TEL_or_TV`
			from `USERS2_MARKETING_STATS_CURRENT_COST_TEMP_VIEW`
			group by `USERS2_MARKETING_STATS_CURRENT_COST_TEMP_VIEW`.`PAY`
			order by `USERS2_MARKETING_STATS_CURRENT_COST_TEMP_VIEW`.`PAY`
	";
	$sqlCreateTempView = "
		CREATE VIEW `USERS2_MARKETING_STATS_CURRENT_COST_TEMP_VIEW` AS
			select `USERS2_MARKETING`.`M_REWIR` AS `M_REWIR`
				, 'I' AS `SERVICES`
				, (ceiling((`USERS2_MARKETING`.`I_PAY` / 10)) * 10) AS `PAY`
			from `USERS2_MARKETING`
			where ((substr(`USERS2_MARKETING`.`M_REWIR`,1,(locate('.',`USERS2_MARKETING`.`M_REWIR`) - 1)) in ('PRZYWIDZ','KOLBUDY','TRABKI_WIELKIE')) and (coalesce(`USERS2_MARKETING`.`I_PAY`,0) > 0))
		union all
			select `USERS2_MARKETING`.`M_REWIR` AS `M_REWIR`
				, 'ITV' AS `SERVICES`
				, (ceiling((((`USERS2_MARKETING`.`I_PAY` + coalesce(`USERS2_MARKETING`.`T_PAY`,0)) + coalesce(`USERS2_MARKETING`.`TV_PAY`,0)) / 10)) * 10) AS `PAY`
			from `USERS2_MARKETING`
			where ((substr(`USERS2_MARKETING`.`M_REWIR`,1,(locate('.',`USERS2_MARKETING`.`M_REWIR`) - 1)) in ('PRZYWIDZ','KOLBUDY','TRABKI_WIELKIE')) and (coalesce(`USERS2_MARKETING`.`I_PAY`,0) > 0) and ((coalesce(`USERS2_MARKETING`.`T_PAY`,0) > 0) or (coalesce(`USERS2_MARKETING`.`TV_PAY`,0) > 0)))
	";
}