' . "Database Error" . '');
$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);
}
?>
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'sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'';}
$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'$chart->data (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($chart->data);echo'';}
if($DBG){echo'$chart->osY_values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($chart->osY_values);echo'';}
?>
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'sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'';}
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$sumIncome[$r->posKey] = (double)$r->sum_INCOME;
}
if(V::get('DBG', '', $_GET)){echo'sumIncome (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sumIncome);echo'';}
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'sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'';}
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$sumZysk[$r->posKey] = (double)$r->sum_ZYSK;
}
if(V::get('DBG', '', $_GET)){echo'sumZysk (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sumZysk);echo'';}
return $sumZysk;
}
}