se/SE/superedit-FVAT_POS_CHARTS.php

<?php

/**
 * Aktualizacja tabel statystycznych.
 * 
 * Dotyczy tabel:
 *   [12582] stats_BILLS_FVAT_POS
 * 
 */
function FVAT_POS_CHARTS() {

	$db = DB::getDB();
	if (!$db) die('<div class="alert alert-danger">' . "Database Error" . '</div>');

	$DBG = V::get('DBG', '', $_GET);

	$fvat = new FvatPosCharts();

	$fltr = new stdClass();
	$fltr->osX_range_min = '2014-01-00';
	$fltr->osX_range_max = date('Y-m') . '-00';
	$fltr->service = V::get('fltr_service', '', $_GET);
	$fltr->sumType = V::get('sum_type', '', $_GET);

	$chart = new stdClass();
	$chart->labels = array();
	$chart->data = array();
	$chart->colors = array();// ['red', 'green', 'yellow']

	if (in_array($fltr->service, array('NET', 'TEL', 'TV'))) {
		$chart->colors = array('silver', 'red');
	} else {
		$chart->colors = array('red', 'green', 'yellow');
	}

	$yStart = (int)substr($fltr->osX_range_min, 0, 4);
	$yEnd   = (int)substr($fltr->osX_range_max, 0, 4);
	$mStart = (int)substr($fltr->osX_range_min, 5, 2);
	$mEnd   = (int)substr($fltr->osX_range_max, 5, 2);
	for ($y = $yStart; $y <= $yEnd; $y++) {
		$lmStart = ($y == $yStart)? $mStart :  1;
		$lmEnd   = ($y == $yEnd)?   $mEnd   : 12;
		for ($m = $lmStart; $m <= $lmEnd; $m++) {
			$chart->labels[] = sprintf("%04d-%02d-%02d", $y, $m, 0);
		}
	}

	foreach ($chart->labels as $vMonth) {
		if ($fltr->sumType == 'ZYSK') {
			$sumData = $fvat->getSumZysk($vMonth, $fltr->service, array());
		} else {
			$sumData = $fvat->getSumIncome($vMonth, $fltr->service, array());
		}
		$chart->data[] = array_values($sumData);
	}

	?>
	<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.bar.js"></script>
	<script src="stuff/RGraph/libraries/RGraph.line.js"></script>

	<canvas id="fvat_pos_chart_main" width="800" height="500" style="float:left; margin:0 20px; border:1px solid #666;">[No canvas support]</canvas>
	<script>
jQuery(document).ready(function(){
	var data = <?php echo json_encode($chart->data); ?>;

	var bar = new RGraph.Bar('fvat_pos_chart_main', data)
		.Set('labels', <?php echo json_encode($chart->labels); ?>)
		.Set('labels.above', true)
		.Set('gutter.left', 50)
		.Set('colors', <?php echo json_encode($chart->colors); ?>)
		.Set('bevel', true)
		<?php if ($fltr->service == 'ALL') echo " .Set('grouping', 'stacked') "; ?>
		.Draw();
		//.Set('grouping', 'stacked')
});
	</script>
	<?php

	$args = array();
	$args['MONTH'] = V::get('MONTH', '', $_GET);
	$args['id_owner'] = V::get('id_owner', '', $_GET);
	$args['P_SERVICE'] = V::get('P_SERVICE', '', $_GET);

	$db = DB::getDB();

	$chart = new stdClass();
	$chart->select = array();
	$chart->groupBy = array();
	$chart->osY = '';
	$chart->osY_values = array();
	$chart->osX = 'MONTH';
	$chart->osX_values = array();
	$chart->osX_range_min = '2013-01-00';
	$chart->osX_range_max = '2014-05-00';

	$chart->groupBy[] = 'MONTH';
	$chart->groupBy[] = 'id_owner';
	$chart->groupBy[] = 'P_SERVICE';
	$chart->select['cnt'] = 'count(1)';
	$chart->select['sum_INCOME'] = 'sum(`INCOME`)';

	$sql_select = array();
	foreach ($chart->select as $kLabel => $vField) {
		$sql_select[] = "{$vField} as {$kLabel}";
	}
	foreach ($chart->groupBy as $vField) {
		if (!array_key_exists($vField, $chart->select)) {
			$sql_select[] = "{$vField} as {$vField}";
		}
	}
	$sql_select = implode(',', $sql_select);
	$sql_group_by = implode(',', $chart->groupBy);

	$chart->data = array();

	$sql = "select {$sql_select}
		from `stats_BILLS_FVAT_POS`
		where `{$chart->osX}` between '{$chart->osX_range_min}' and '{$chart->osX_range_max}'
		group by {$sql_group_by}
	";
	if($DBG){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		if (empty($r->P_SERVICE)) {
			$r->P_SERVICE = 'Unknown';
		}

		if (isset($r->MONTH) && substr($r->MONTH, -3) == '-00') {
			$r->MONTH = substr($r->MONTH, 0, -3);
		}

		$r->cnt = (int)$r->cnt;
		$r->sum_INCOME = (double)$r->sum_INCOME;

		$chart->data[] = $r;

		foreach ($chart->groupBy as $vField) {
			if ($vField == $chart->osX) {
				$chart->osX_values[$r->{$chart->osX}] = true;
			}
			else if (!array_key_exists($vField, $chart->select)) {
				$chart->osY_values[$vField][$r->{$vField}] = true;
			}
		}
	}

	foreach ($chart->osY_values as $k => $v) {
		$chart->osY_values[$k] = array_keys($v);
		sort($chart->osY_values[$k]);
	}
	$chart->osX_values = array_keys($chart->osX_values);
	sort($chart->osX_values);

	if($DBG){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">$chart->data (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($chart->data);echo'</pre>';}
	if($DBG){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">$chart->osY_values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($chart->osY_values);echo'</pre>';}

	?>
	<canvas id="chart-canvas" width="800" height="500" style="float:left; margin:0 20px; border:1px solid #666;">[No canvas support]</canvas>
	<form action="" method="post" id="chart-frm">
		<div class="chart-frm-cnt">
		</div>
		<button id="chart-frm-btn" class="btn btn-primary">Rysuj wykres</button>
	</form>
	<script>
		var frm = jQuery('#chart-frm')
			, frmCnt = frm.find('.chart-frm-cnt')
			, chart_data = <?php echo json_encode($chart->data); ?>
			, chart_select = <?php echo json_encode($chart->select); ?>
			, chart_groupBy = <?php echo json_encode($chart->groupBy); ?>
			, chart_osX = <?php echo json_encode($chart->osX); ?>
			, chart_osX_values = <?php echo json_encode($chart->osX_values); ?>
			, chart_values = <?php echo json_encode($chart->osY_values); ?>;

		for (var i in chart_values) {
			var selWrap = jQuery('<div></div>');
			jQuery('<label for="' + chart_values[i][j] + '">' + i + ': </label>').appendTo(selWrap);
			var sel = jQuery('<select name="' + i + '"></select>').appendTo(selWrap);
			sel.append('<option value="' + "all" + '">' + "dowolne" + '</select>');
			for (var j in chart_values[i]) {
				sel.append('<option value="' + chart_values[i][j] + '">' + chart_values[i][j] + '</select>');
			}
			selWrap.appendTo(frmCnt);
		}
		var selWrap = jQuery('<div></div>');
		jQuery('<label for="chart_select">Wartość: </label>').appendTo(selWrap);
		var sel = jQuery('<select name="chart_select"></select>').appendTo(selWrap);
		for (var i in chart_select) {
			sel.append('<option value="' + i + '">' + i + ': ' + chart_select[i] + '</select>');
		}
		selWrap.appendTo(frmCnt);

		jQuery('#chart-frm-btn').on('click', function(evt){
			var data = []
				, canvas = document.getElementById('chart-canvas')
				, frmNode = document.getElementById('chart-frm')
				, fld_name = jQuery(frmNode['chart_select']).val();
console.log('chart_data', chart_data);
			var chart_data_filtered = chart_data.filter(function (item) {
				for (var i in chart_groupBy) {
					if (chart_groupBy[i] == chart_osX) continue;
					var frmVal = jQuery(frmNode[chart_groupBy[i]]).val();
					if (frmVal == 'all') continue;
					if (item[chart_groupBy[i]] != frmVal) {
						return false;
					}
				}
				return true;
			});
console.log('chart_data_filtered', chart_data_filtered);

			chart_data_filtered.sort(function(a, b) {
				if (a[chart_osX] > b[chart_osX]) {
					return 1;
				}
				else if (a[chart_osX] < b[chart_osX]) {
					return -1;
				}
				return 0;
			});

			var d = chart_data_filtered.shift();
console.log('chart_data_filtered.shift', d);
			for (var v in chart_osX_values) {
				if (!d || d[chart_osX] != chart_osX_values[v]) {
					data.push(0);
					continue;
				}
				data.push(d[fld_name]);
				d = chart_data_filtered.shift();
			}

			RGraph.Clear(canvas);
			var bar = new RGraph.Bar('chart-canvas', data)
				.Set('labels', chart_osX_values)
				.Set('gutter.left', 50)
				.Set('hmargin', 15)
				.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)
				.Draw();
			//	.Set('colors', ['Gradient(#99f:#27afe9:#058DC7:#058DC7)', 'Gradient(#94f776:#50B332:#B1E59F)', 'Gradient(#fe783e:#EC561B:#F59F7D)'])
			return false;
		});
	</script>
	<?php
	return;
}


class FvatPosCharts {

	function getSumIncome($month, $service = '', $params = array()) {
		$sumIncome = array();
		if (in_array($service, array('NET', 'TEL', 'TV'))) {
			$sumIncome['inne'] = 0;
			$sumIncome['abonament'] = 0;
		} else {
			$sumIncome['abonament_NET'] = 0;
			$sumIncome['abonament_TV']  = 0;
			$sumIncome['abonament_TEL'] = 0;
		}

		$db = DB::getDB();
		$sqlPosKey = "IF(f.`ID_OFFERS` > 0, 'abonament', 'inne')";
		$sqlSrv = '';
		if ($service == 'NET') {
			$sqlSrv = "and f.`NAME_LIST_SERVICES` in('USERS2','EMAIL')";
		} else if ($service == 'TEL') {
			$sqlSrv = "and f.`NAME_LIST_SERVICES`='VOIP'";
		} else if ($service == 'TV') {
			$sqlSrv = "and f.`NAME_LIST_SERVICES`='TV'";
		} else {
			$sqlPosKey = "IF(f.`ID_OFFERS` > 0
				, IF(f.`NAME_LIST_SERVICES` in('USERS2','EMAIL')
					, 'abonament_NET'
					, IF (f.`NAME_LIST_SERVICES`='VOIP'
						, 'abonament_TEL'
						, IF (f.`NAME_LIST_SERVICES`='TV'
							, 'abonament_TV'
							, 'abonament'
							)
						)
					)
				, 'inne')
			";

			$sqlPosKey = "IF(f.`NAME_LIST_SERVICES` in('USERS2','EMAIL')
				, 'abonament_NET'
				, IF (f.`NAME_LIST_SERVICES`='VOIP'
					, 'abonament_TEL'
					, IF (f.`NAME_LIST_SERVICES`='TV'
						, 'abonament_TV'
						, 'abonament'
						)
					)
				)
			";
		}
		$sql = "select sum(f.`INCOME`) as sum_INCOME
				, {$sqlPosKey} as posKey
			from `stats_BILLS_FVAT_POS` as f
			where f.`ID_DEALS_TABLE`>0
				and f.`MONTH`='{$month}'
				{$sqlSrv}
			group by posKey
		";
		if(V::get('DBG', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$sumIncome[$r->posKey] = (double)$r->sum_INCOME;
		}
		if(V::get('DBG', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sumIncome (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sumIncome);echo'</pre>';}

		return $sumIncome;
	}

	function getSumZysk($month, $service = '', $params = array()) {
		$sumZysk = array();
		if (in_array($service, array('NET', 'TEL', 'TV'))) {
			$sumZysk['inne'] = 0;
			$sumZysk['abonament'] = 0;
		} else {
			$sumZysk['abonament_NET'] = 0;
			$sumZysk['abonament_TV']  = 0;
			$sumZysk['abonament_TEL'] = 0;
		}

		$db = DB::getDB();
		$sqlPosKey = "IF(f.`ID_OFFERS` > 0, 'abonament', 'inne')";
		$sqlSrv = '';
		if ($service == 'NET') {
			$sqlSrv = "and f.`NAME_LIST_SERVICES` in('USERS2','EMAIL')";
		} else if ($service == 'TEL') {
			$sqlSrv = "and f.`NAME_LIST_SERVICES`='VOIP'";
		} else if ($service == 'TV') {
			$sqlSrv = "and f.`NAME_LIST_SERVICES`='TV'";
		} else {
			$sqlPosKey = "IF(f.`ID_OFFERS` > 0
				, IF(f.`NAME_LIST_SERVICES` in('USERS2','EMAIL')
					, 'abonament_NET'
					, IF (f.`NAME_LIST_SERVICES`='VOIP'
						, 'abonament_TEL'
						, IF (f.`NAME_LIST_SERVICES`='TV'
							, 'abonament_TV'
							, 'abonament'
							)
						)
					)
				, 'inne')
			";

			$sqlPosKey = "IF(f.`NAME_LIST_SERVICES` in('USERS2','EMAIL')
				, 'abonament_NET'
				, IF (f.`NAME_LIST_SERVICES`='VOIP'
					, 'abonament_TEL'
					, IF (f.`NAME_LIST_SERVICES`='TV'
						, 'abonament_TV'
						, 'abonament'
						)
					)
				)
			";
		}
		$sql = "select sum(f.`P_OFFER_ZYSK`) as sum_ZYSK
				, {$sqlPosKey} as posKey
			from `stats_BILLS_FVAT_POS` as f
			where f.`ID_DEALS_TABLE`>0
				and f.`MONTH`='{$month}'
				{$sqlSrv}
			group by posKey
		";
		if(V::get('DBG', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$sumZysk[$r->posKey] = (double)$r->sum_ZYSK;
		}
		if(V::get('DBG', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sumZysk (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sumZysk);echo'</pre>';}

		return $sumZysk;
	}

}