' . "Database Error" . '';
return;
}
$sqlList = array();
?>
0)
set
f.`S_ADDRESS_STREET`=s.`S_ADDRESS_STREET`
, f.`NAME_LIST_SERVICES`=s.`NAME_LIST_SERVICES`
, f.`ID_DEALS_TABLE`=s.`ID_DEALS_TABLE`
, f.`ID_OFFERS`=s.`ID_OFFERS`
";
$sqlList[] = "update `stats_BILLS_FVAT_POS` as f
join `DEALS_TABLE` as d on(d.`ID`=f.`ID_DEALS_TABLE` and f.`S_ADDRESS_STREET`='')
set
f.`S_ADDRESS_STREET`=d.`S_ADDRESS_STREET`
";
$sqlList[] = "update `stats_BILLS_FVAT_POS` f
join `USERS2_MARKETING` m on(m.`S_ADDRESS_STREET`=f.`S_ADDRESS_STREET`)
join `BUILDINGS` b on(b.`S_ADDRESS_STREET`=m.`T_TELBOX_BUILDING_IN`)
set
f.`T_TELBOX_BUILDING_IN`=m.`T_TELBOX_BUILDING_IN`
, f.`M_REWIR`=b.`M_REWIR`
, f.`P_OSIEDLE`=IF(b.`P_OSIEDLE`!=''
, CONCAT(b.`S_ADDRESS_CITY`, ' - ', b.`P_OSIEDLE`)
, b.`S_ADDRESS_CITY`
)
where f.`S_ADDRESS_STREET`!=''
";
$sqlList[] = "update `stats_BILLS_FVAT_POS` f
join `USERS2_OFFERS` o on(o.`ID`=f.`ID_OFFERS`)
left join `USERS2_OFFERS_COSTS_GROUPS` c on(c.`ID`=o.`ID_COSTS_GROUPS`)
set
f.`P_SERVICE`=IF(o.`P_SERVICE`='OPCJE'
, concat(o.`P_SERVICE`, '_', o.`P_SERVICE_PARENT`)
, o.`P_SERVICE`
)
, f.`P_NET_SPEED`=IF(o.`P_SERVICE` in('NET')
, o.`P_S_PARAM1`
, '')
, f.`P_TV_NAME`=IF(o.`P_SERVICE` in('TV','TVC','TVCP','OPCJA_TVC') or (o.`P_SERVICE`='OPCJE' and o.`P_SERVICE_PARENT` in('TVC','TVCP','TV'))
, o.`P_SERVICE_NAME`
, '')
, f.`P_TEL_NAME`=IF(o.`P_SERVICE` in('TEL','OPCJA_TEL')
, o.`P_SERVICE_NAME`
, '')
, f.`P_OFFER_COSTS`=c.`P_OFFER_COSTS`
where f.`ID_OFFERS`>0
";
$sqlList[] = "update `stats_BILLS_FVAT_POS` f
join `USERS2_OFFERS` o on(o.`ID`=f.`ID_OFFERS`)
join `USERS2_PRODUKT` p on(p.`ID`=o.`ID_PRODUKT`)
set
f.`P_NET_GROUP`=p.`P_NAME`
where f.`ID_OFFERS`>0
and o.`ID_PRODUKT`>0
and o.`P_SERVICE`='NET'
";
$sqlList[] = "update `stats_BILLS_FVAT_POS` as f
set
f.`P_OFFER_ZYSK`=(f.`INCOME` - f.`P_OFFER_COSTS`)
";
$sqlList[] = "update `stats_BILLS_FVAT_POS` as f
join `COMPANIES` as c on(c.`ID`=f.`ID_BILLING_USERS`)
set
f.`A_ADM_COMPANY`=c.`A_ADM_COMPANY`
";
}
else if ('FVAT_POS_CHARTS' == $task) {
$DBG = V::get('DBG', '', $_REQUEST);
$args = array();
$args['MONTH'] = V::get('MONTH', '', $_REQUEST);
$args['id_owner'] = V::get('id_owner', '', $_REQUEST);
$args['P_SERVICE'] = V::get('P_SERVICE', '', $_REQUEST);
$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'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'';}
?>
sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'';}
$res = $db->query($sql);
if ($db->has_errors()) {
$errors = true;
if(V::get('DBG','',$_GET)){echo' (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($db->get_errors());echo'';}
}
}
if ($errors) {
echo '' . "Uwaga wystąpiły błędy - w czerwonej ramce" . '
';
} else {
echo '' . "Operacje zakończone pomyślnie" . '
';
}
}