se/SE/superedit-STATYSTYKA_TABELE.php

<?php

/**
 * Aktualizacja tabel statystycznych.
 * 
 * Dotyczy tabel:
 *   temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL
 *   temp_USERS2_INTERNET_STATUS_VOIP_NORMAL
 *   temp_USERS2_INTERNET_STATUS_TV_NORMAL
 *   temp_USERS2_INTERNET_STATUS_OFF_HARD
 *   USERS2_MARKETING_OVERWIEV
 *   USERS2_MARKETING_OVERWIEV_REWIR
 * Pobiera dane z tabeli temp_USERS2_INTERNET_STATUS - TODO gdzie jest tworzona ta tabela
 * 
 */
function STATYSTYKA_TABELE() {

	$db = DB::getDB();
	if (!$db) {
		echo '<div class="alert alert-error">' . "Database Error" . '</div>';
		return;
	}

	$sqlList = array();

	$task = V::get('task', '', $_GET);
	if ($task == 'FVAT_POS_CHARTS') {

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

		$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>';}

		?>
    <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="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;
	}
	else if ($task == 'FVAT_POS') {

		$args = array();
		$args['no_clear'] = ('1' == V::get('no_clear', '', $_GET));
		$args['update_year'] = V::get('update_year', '', $_GET, 'int');

		$sql_date_filter = date('Y');

		if (!$args['no_clear']) {
			$sqlList[] = "DROP TABLE `stats_BILLS_FVAT_POS`; ";

			$sqlList[] = "CREATE TABLE IF NOT EXISTS `stats_BILLS_FVAT_POS` (
					`ID` int(11) NOT NULL,
					`ID_BILLING_USERS` int(11) NOT NULL,
					`id_owner` int(11) NOT NULL,
					`MONTH` date NOT NULL,
					`ID_SERVICES` int(11) NOT NULL DEFAULT '0',
					`ID_DEALS_TABLE` int(11) NOT NULL DEFAULT '0',
					`INCOME` decimal(10,2) NOT NULL DEFAULT '0.00',
					`P_OFFER_COSTS` decimal(10,2) NOT NULL DEFAULT '0.00',
					`P_OFFER_ZYSK` decimal(10,2) NOT NULL DEFAULT '0.00',
					`ID_OFFERS` int(11) NOT NULL DEFAULT '0',
					`S_ADDRESS_STREET` varchar(32) NOT NULL DEFAULT '',
					`T_TELBOX_BUILDING_IN` varchar(16) NOT NULL DEFAULT '',
					`P_OSIEDLE` varchar(64) NOT NULL DEFAULT '',
					`M_REWIR` varchar(255) NOT NULL DEFAULT '',
					`NAME_LIST_SERVICES` varchar(16) NOT NULL DEFAULT '',
					`P_SERVICE` varchar(16) NOT NULL DEFAULT '',
					`P_NET_SPEED` varchar(16) NOT NULL DEFAULT '',
					`P_NET_GROUP` varchar(32) NOT NULL DEFAULT '',
					`P_TV_NAME` varchar(64) NOT NULL DEFAULT '',
					`P_TEL_NAME` varchar(64) NOT NULL DEFAULT '',
					`FVAT_POS_DESCR` varchar(64) NOT NULL DEFAULT '',
					KEY `MONTH` (`MONTH`)
				) ENGINE=MyISAM DEFAULT CHARSET=latin2;
			";

			$sqlList[] = "TRUNCATE TABLE `stats_BILLS_FVAT_POS`; ";
		}

		if ($args['update_year']) {
			$sql_date_filter = $args['update_year'];
			$sqlList[] = "delete from `stats_BILLS_FVAT_POS` where `MONTH` like '{$sql_date_filter}-%' ; ";
		}

		//			--	DATE_FORMAT(f.`DATE_FROM`,'%Y-%m-00')
		//			--	(f.`DATE_FROM` like '{$sql_date_filter}-%')
		$sqlList[] = "insert into `SES_USERS2`.`stats_BILLS_FVAT_POS`
			(`ID`, `ID_SERVICES`, `MONTH`, `INCOME`, `ID_OFFERS`
				, `ID_BILLING_USERS`, `id_owner`
				, `FVAT_POS_DESCR`
			)
			select f.`ID`, f.`ID_SERVICES`, DATE_FORMAT(fv.`BILL_DATE`,'%Y-%m-00'), f.`PRICE` * f.`AMMOUNT`, f.`ID_OFFERS`
				, fv.`ID_BILLING_USERS`, fv.`id_owner`
				, f.`DESCR`
			from `billing2013`.`BILLS_FVAT_POS` as f, `billing2013`.`BILLS_FVAT` as fv
			where
				(fv.`BILL_DATE` like '{$sql_date_filter}-%')
				and f.`ID_BILLS_FVAT`=fv.`ID`
		";

		$sqlList[] = "update `stats_BILLS_FVAT_POS` as f, `SERVICES` as s
			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`
			where s.`ID`=f.`ID_SERVICES`
				and f.`ID_SERVICES`>0
		";

		$sqlList[] = "update `stats_BILLS_FVAT_POS` as f, `DEALS_TABLE` as d
			set
				f.`S_ADDRESS_STREET`=d.`S_ADDRESS_STREET`
			where d.`ID`=f.`ID_DEALS_TABLE`
				and f.`S_ADDRESS_STREET`=''
		";

		$sqlList[] = "update `stats_BILLS_FVAT_POS` as f, `USERS2_MARKETING` as m, `BUILDINGS` as b
			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 m.`S_ADDRESS_STREET`=f.`S_ADDRESS_STREET`
				and b.`S_ADDRESS_STREET`=m.`T_TELBOX_BUILDING_IN`
				and f.`S_ADDRESS_STREET`!=''
		";

		$sqlList[] = "update `stats_BILLS_FVAT_POS` as f, `USERS2_OFFERS` as o, `USERS2_OFFERS_COSTS_GROUPS` as c
			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')
					, o.`P_SERVICE_NAME`
					, '')
				, f.`P_TEL_NAME`=IF(o.`P_SERVICE` in('TEL')
					, o.`P_SERVICE_NAME`
					, '')
				, f.`P_OFFER_COSTS`=c.`P_OFFER_COSTS`
			where o.`ID`=f.`ID_OFFERS`
				and c.`ID`=o.`ID_COSTS_GROUPS`
				and f.`ID_OFFERS`>0
		";

		$sqlList[] = "update `stats_BILLS_FVAT_POS` as f, `USERS2_OFFERS` as o, `USERS2_PRODUKT` as p
			set
				f.`P_NET_GROUP`=p.`P_NAME`
			where o.`ID`=f.`ID_OFFERS`
				and p.`ID`=o.`ID_PRODUKT`
				and 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`
		";

	}
	else {

		{// temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL
			$sqlList[] = "drop table if exists temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL";
			$sqlList[] = "create table temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL
				select ID, S_ADDRESS_STREET, USERS2_INTERNET_STATUS, P_DEALEXPIRE
				from temp_USERS2_INTERNET_STATUS
				where
					( USERS2_INTERNET_STATUS like 'NORMAL%' or USERS2_INTERNET_STATUS like 'WAITING%' )
					and NAME_LIST_SERVICES like 'USERS2'
				group by S_ADDRESS_STREET
			";
			$sqlList[] = "alter table temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL add index(S_ADDRESS_STREET)";
		}

		{// temp_USERS2_INTERNET_STATUS_VOIP_NORMAL
			$sqlList[] = "drop table if exists temp_USERS2_INTERNET_STATUS_VOIP_NORMAL";
			$sqlList[] = "create table temp_USERS2_INTERNET_STATUS_VOIP_NORMAL
				select ID, S_ADDRESS_STREET, USERS2_INTERNET_STATUS, P_DEALEXPIRE
				from temp_USERS2_INTERNET_STATUS
				where
					( USERS2_INTERNET_STATUS like 'NORMAL%' or USERS2_INTERNET_STATUS like 'WAITING%' )
					and NAME_LIST_SERVICES like 'VOIP'
				group by S_ADDRESS_STREET
			";
			$sqlList[] = "alter table temp_USERS2_INTERNET_STATUS_VOIP_NORMAL add index(S_ADDRESS_STREET)";
		}

		{// temp_USERS2_INTERNET_STATUS_TV_NORMAL
			$sqlList[] = "drop table if exists temp_USERS2_INTERNET_STATUS_TV_NORMAL";
			$sqlList[] = "create table temp_USERS2_INTERNET_STATUS_TV_NORMAL
				select ID, S_ADDRESS_STREET, USERS2_INTERNET_STATUS, P_DEALEXPIRE
				from temp_USERS2_INTERNET_STATUS
				where
					( USERS2_INTERNET_STATUS like 'NORMAL%' or USERS2_INTERNET_STATUS like 'WAITING%' )
					and NAME_LIST_SERVICES like 'TV'
				group by S_ADDRESS_STREET
			";
			$sqlList[] = "alter table temp_USERS2_INTERNET_STATUS_TV_NORMAL add index(S_ADDRESS_STREET)";
		}

		{// temp_USERS2_INTERNET_STATUS_OFF_HARD
			$sqlList[] = "drop table if exists temp_USERS2_INTERNET_STATUS_OFF_HARD";
			$sqlList[] = "create table temp_USERS2_INTERNET_STATUS_OFF_HARD
				select t1.ID, t1.S_ADDRESS_STREET, t2.ID as T2_ID
				from USERS2_MARKETING as t1
					left join temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL as t2 on(t2.S_ADDRESS_STREET=t1.S_ADDRESS_STREET)
				where
					t2.ID is NULL
					and t1.I_OPER='BN'
			";
		}

		{// update USERS2_MARKETING fields: I_OPER, I_OPER_FIN
			// by NORMAL - temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL
			$sqlList[] = "update USERS2_MARKETING, temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL
				set USERS2_MARKETING.I_OPER='BN',
						USERS2_MARKETING.I_OPER_FIN=temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL.P_DEALEXPIRE
				where USERS2_MARKETING.S_ADDRESS_STREET=temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL.S_ADDRESS_STREET
			";

			// update USERS2_MARKETING fields: I_OPER by OFF_HARD
			$sqlList[] = "update USERS2_MARKETING, temp_USERS2_INTERNET_STATUS_OFF_HARD
				set USERS2_MARKETING.I_OPER='NIEWIADOMO'
				where USERS2_MARKETING.I_OPER='BN'
					and USERS2_MARKETING.S_ADDRESS_STREET=temp_USERS2_INTERNET_STATUS_OFF_HARD.S_ADDRESS_STREET
			";

			// update USERS2_MARKETING fields: I_OPER, T_OPER_FIN by VOIP
			$sqlList[] = "update USERS2_MARKETING, temp_USERS2_INTERNET_STATUS_VOIP_NORMAL
				set USERS2_MARKETING.T_OPER='BN',
					USERS2_MARKETING.T_OPER_FIN=temp_USERS2_INTERNET_STATUS_VOIP_NORMAL.P_DEALEXPIRE
				where USERS2_MARKETING.S_ADDRESS_STREET=temp_USERS2_INTERNET_STATUS_VOIP_NORMAL.S_ADDRESS_STREET
			";

			// update USERS2_MARKETING fields: TV_OPER, TV_OPER_FIN
			$sqlList[] = "update USERS2_MARKETING, temp_USERS2_INTERNET_STATUS_TV_NORMAL
				set USERS2_MARKETING.TV_OPER='BN',
					USERS2_MARKETING.TV_OPER_FIN=temp_USERS2_INTERNET_STATUS_TV_NORMAL.P_DEALEXPIRE
				where USERS2_MARKETING.S_ADDRESS_STREET=temp_USERS2_INTERNET_STATUS_TV_NORMAL.S_ADDRESS_STREET
			";
		}

		{// USERS2_MARKETING_OVERWIEV
			$sqlList[] = "drop table if exists USERS2_MARKETING_OVERWIEV";

			// update `USERS2_MARKETING` fields: `M_REWIR`
			$sqlList[] = "update  USERS2_MARKETING , BUILDINGS
				set USERS2_MARKETING.M_REWIR=BUILDINGS.M_REWIR
				where
					USERS2_MARKETING.T_TELBOX_BUILDING_IN=BUILDINGS.S_ADDRESS_STREET
					and BUILDINGS.M_REWIR!=''
					and BUILDINGS.M_REWIR is not null
			";

			$COLSFORSTATS = array('I','T','TV');
			$COLSFORSTATS_OPERS = array('INNY','UPC','NSM','TP','EIA','BRAK','JARSAT');

			//BEGIN MIESZKANIA STATSY
			$sql = "select 0 as ID
				, USERS2_MARKETING.T_TELBOX_BUILDING_IN
				, USERS2_MARKETING.M_REWIR
				, BUILDINGS.T_NETWORK_SERVER
				, sum(if(USERS2_MARKETING.I_OPER='BN',1,0) ) as ABO_I ,count(USERS2_MARKETING.S_ADDRESS_STREET) as MIESZKAN
				, round(( sum( if(
					(	USERS2_MARKETING.I_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						or USERS2_MARKETING.T_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						or USERS2_MARKETING.TV_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
					), 1, 0) ) / count(USERS2_MARKETING.S_ADDRESS_STREET) * 100), 2) as BADAN_I
				, round(( sum( if(
					(	USERS2_MARKETING.I_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						or USERS2_MARKETING.T_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						or USERS2_MARKETING.TV_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						or USERS2_MARKETING.I_OPER='BN'
						or USERS2_MARKETING.T_OPER='BN'
						or USERS2_MARKETING.TV_OPER='BN'
					), 1, 0) ) / count(USERS2_MARKETING.S_ADDRESS_STREET) * 100), 2) as ANKIET_I
				, BUILDINGS.A_ADM_COMPANY
			";
			foreach ($COLSFORSTATS as $FFS) {
				$sql .= " , round((sum(if( USERS2_MARKETING.{$FFS}_OPER='BN',1,0) ) / count(USERS2_MARKETING.S_ADDRESS_STREET) * 100), 2) as NAS_{$FFS} ";
				foreach ($COLSFORSTATS_OPERS as $FFFS) {
					$sql .= " , round((
							sum(
								if( USERS2_MARKETING.{$FFS}_OPER='{$FFFS}', 1, 0)
							) / sum(
									if(
										(USERS2_MARKETING.{$FFS}_BAD_DATE!='0000-00-00')
										and (USERS2_MARKETING.{$FFS}_OPER!='BN'), 1, 0
									)
								) * (
									count(USERS2_MARKETING.S_ADDRESS_STREET)
									-
									sum(if(USERS2_MARKETING.{$FFS}_OPER='BN', 1, 0))
								) / count(USERS2_MARKETING.S_ADDRESS_STREET)
						) * 100, 2) as PR_{$FFS}_{$FFFS}
					";
				}
				$sql .= "
					, round((
						sum(
							if(
								(USERS2_MARKETING.{$FFS}_OPER!='BN'
								and USERS2_MARKETING.{$FFS}_OPER_ZAINT > 2
								and USERS2_MARKETING.{$FFS}_OPER_ZAINT != 'NIEWIADOMO'), 1, 0)
							) / sum(
								if( (USERS2_MARKETING.{$FFS}_BAD_DATE!='0000-00-00') and (USERS2_MARKETING.{$FFS}_OPER!='BN'), 1, 0)
							) * (
								count(USERS2_MARKETING.S_ADDRESS_STREET)
								-
								sum(if(USERS2_MARKETING.{$FFS}_OPER='BN',1,0))
							) / count(USERS2_MARKETING.S_ADDRESS_STREET)
						) * 100, 2) as PR_{$FFS}_ZAINT
					, round ((
							sum(
								if(
									(USERS2_MARKETING.{$FFS}_OPER!='BN'
										and USERS2_MARKETING.{$FFS}_OPER!='BRAK'
										and USERS2_MARKETING.{$FFS}_OPER!='NIEWIADOMO'), 1, 0)
							) / sum(
								if(
									(USERS2_MARKETING.{$FFS}_BAD_DATE!='0000-00-00') and (USERS2_MARKETING.{$FFS}_OPER!='BN')  ,1,0 )
							) * (
								count(USERS2_MARKETING.S_ADDRESS_STREET)
								-
								sum(if(USERS2_MARKETING.{$FFS}_OPER='BN', 1, 0))
							) / count(USERS2_MARKETING.S_ADDRESS_STREET)
						) * 100, 2) as PR_{$FFS}_KONKUR
					";
			}
			$sql .= "
				from USERS2_MARKETING as USERS2_MARKETING
					left join BUILDINGS on (BUILDINGS.S_ADDRESS_STREET=USERS2_MARKETING.T_TELBOX_BUILDING_IN)
				where
					USERS2_MARKETING.A_STATUS!='DELETED'
					and BUILDINGS.A_STATUS!='DELETED'
				group by USERS2_MARKETING.T_TELBOX_BUILDING_IN
				order by USERS2_MARKETING.M_REWIR DESC, USERS2_MARKETING.T_TELBOX_BUILDING_IN
			";
			$sqlList[] = " create table `USERS2_MARKETING_OVERWIEV` \n {$sql} \n limit 1";
			$sqlList[] = " truncate table `USERS2_MARKETING_OVERWIEV` ";
			$sqlList[] = " ALTER TABLE `USERS2_MARKETING_OVERWIEV` ENGINE=MYISAM ";
			$sqlList[] = " ALTER TABLE `USERS2_MARKETING_OVERWIEV` CHANGE `ID`  `ID` INT(11) NOT NULL DEFAULT '0' ";
			$sqlList[] = " ALTER TABLE `USERS2_MARKETING_OVERWIEV` ADD PRIMARY KEY(`ID`) ";
			$sqlList[] = " ALTER TABLE `USERS2_MARKETING_OVERWIEV` CHANGE `ID`  `ID` INT(11) NOT NULL AUTO_INCREMENT ";
			$sqlList[] = " insert into `USERS2_MARKETING_OVERWIEV` \n {$sql}";
		}

		{// REWIRY STATSY - USERS2_MARKETING_OVERWIEV_REWIR
			$sqlList[] = "drop table if exists USERS2_MARKETING_OVERWIEV_REWIR";

			$sql = " select 0 as ID
				, group_concat(distinct ' ', USERS2_MARKETING.T_TELBOX_BUILDING_IN) as T_TELBOX_BUILDING_IN
				, USERS2_MARKETING.M_REWIR
				, BUILDINGS.T_NETWORK_SERVER
				, sum(if(USERS2_MARKETING.I_OPER='BN',1,0) ) as ABO_I
				, count(USERS2_MARKETING.S_ADDRESS_STREET) as MIESZKAN
				, round((sum(if(USERS2_MARKETING.I_OPER='BN', 1, 0)) / count(USERS2_MARKETING.S_ADDRESS_STREET) * 100), 2) as NAS_I
				, round(( sum( if(
						(	USERS2_MARKETING.I_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
							or USERS2_MARKETING.T_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
							or USERS2_MARKETING.TV_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						), 1, 0)
					) / count(USERS2_MARKETING.S_ADDRESS_STREET) * 100), 2) as BADAN_I
				, round(( sum( if(
						(	USERS2_MARKETING.I_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						or USERS2_MARKETING.T_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						or USERS2_MARKETING.TV_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)
						or USERS2_MARKETING.I_OPER='BN'
						or USERS2_MARKETING.T_OPER='BN'
						or USERS2_MARKETING.TV_OPER='BN'), 1, 0)
					) / count(USERS2_MARKETING.S_ADDRESS_STREET) * 100), 2) as ANKIET_I
				, BUILDINGS.A_ADM_COMPANY
			";
			foreach ($COLSFORSTATS as $FFS) {
				foreach ($COLSFORSTATS_OPERS as $FFFS) {
					$sql .= ", round(( sum(
							if(USERS2_MARKETING.{$FFS}_OPER='{$FFFS}', 1, 0)
						) / sum(
							if( (USERS2_MARKETING.{$FFS}_BAD_DATE!='0000-00-00') and (USERS2_MARKETING.{$FFS}_OPER!='BN'), 1, 0)
						) * (
							count(USERS2_MARKETING.S_ADDRESS_STREET)
							- sum(if(USERS2_MARKETING.{$FFS}_OPER='BN', 1, 0))
						) / count(USERS2_MARKETING.S_ADDRESS_STREET)) * 100, 2) as PR_{$FFS}_{$FFFS}
					";
				}
				$sql .= ", round(( sum(
						if(
							(USERS2_MARKETING.{$FFS}_OPER!='BN'
							and USERS2_MARKETING.{$FFS}_OPER_ZAINT > 2
							and USERS2_MARKETING.{$FFS}_OPER_ZAINT!='NIEWIADOMO'), 1, 0)
					) /	sum(
						if( (USERS2_MARKETING.{$FFS}_BAD_DATE!='0000-00-00') and (USERS2_MARKETING.{$FFS}_OPER!='BN'), 1, 0)
					) * (
						count(USERS2_MARKETING.S_ADDRESS_STREET)
						- sum(if(USERS2_MARKETING.{$FFS}_OPER='BN', 1, 0))
					) / count(USERS2_MARKETING.S_ADDRESS_STREET)) * 100, 2) as PR_{$FFS}_ZAINT
				, round((
					sum(
						if( (USERS2_MARKETING.{$FFS}_OPER!='BN'  and USERS2_MARKETING.{$FFS}_OPER!='BRAK' and USERS2_MARKETING.{$FFS}_OPER!='NIEWIADOMO'), 1, 0)
					) / sum(
						if( (USERS2_MARKETING.{$FFS}_BAD_DATE!='0000-00-00') and (USERS2_MARKETING.{$FFS}_OPER!='BN'), 1, 0)
					) * (
							count(USERS2_MARKETING.S_ADDRESS_STREET)
							- sum(if(USERS2_MARKETING.{$FFS}_OPER='BN',1,0))
						) / count(USERS2_MARKETING.S_ADDRESS_STREET)
				) * 100, 2) as PR_{$FFS}_KONKUR
				";
			}
			$sql .= "
				from USERS2_MARKETING as USERS2_MARKETING
					left join BUILDINGS on BUILDINGS.S_ADDRESS_STREET=USERS2_MARKETING.T_TELBOX_BUILDING_IN
				where USERS2_MARKETING.A_STATUS!='DELETED' and BUILDINGS.A_STATUS!='DELETED'
				group by USERS2_MARKETING.M_REWIR
				order by USERS2_MARKETING.M_REWIR DESC ,USERS2_MARKETING.T_TELBOX_BUILDING_IN 
			";
			$sqlList[] = " create table `USERS2_MARKETING_OVERWIEV_REWIR` \n {$sql} \n limit 1";
			$sqlList[] = " truncate table `USERS2_MARKETING_OVERWIEV_REWIR` ";
			$sqlList[] = " ALTER TABLE `USERS2_MARKETING_OVERWIEV_REWIR` ENGINE=MYISAM ";
			$sqlList[] = " ALTER TABLE `USERS2_MARKETING_OVERWIEV_REWIR` CHANGE `ID`  `ID` INT(11) NOT NULL DEFAULT '0' ";
			$sqlList[] = " ALTER TABLE `USERS2_MARKETING_OVERWIEV_REWIR` ADD PRIMARY KEY(`ID`) ";
			$sqlList[] = " ALTER TABLE `USERS2_MARKETING_OVERWIEV_REWIR` CHANGE `ID`  `ID` INT(11) NOT NULL AUTO_INCREMENT ";
			$sqlList[] = " insert into `USERS2_MARKETING_OVERWIEV_REWIR` \n {$sql}";
		}

		{// add fields to USERS2_MARKETING_OVERWIEV
			/*
			$sqlList[] = "ALTER TABLE `USERS2_MARKETING_OVERWIEV` CHANGE `ABO_I` `ABO_I` CHAR( 10 ) NOT NULL DEFAULT '0',
				CHANGE `MIESZKAN` `MIESZKAN` CHAR( 10 ) NOT NULL DEFAULT '0',
				CHANGE `NAS_I` `NAS_I` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `ANKIET_I` `ANKIET_I` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_UPC` `PR_I_UPC` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_INNY` `PR_I_INNY` CHAR( 10 ) NULL DEFAULT NULL ,  CHANGE `PR_I_NSM` `PR_I_NSM` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_TP` `PR_I_TP` CHAR( 10 ) NULL DEFAULT NULL ,  CHANGE `PR_I_EIA` `PR_I_EIA` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_BRAK` `PR_I_BRAK` CHAR( 10 ) NULL DEFAULT NULL ,  CHANGE `PR_I_KONKUR` `PR_I_KONKUR` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `BADAN_I` `BADAN_I` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_ZAINT` `PR_I_ZAINT` CHAR( 10 ) NULL DEFAULT NULL,
				CHANGE `PR_I_JARSAT` `PR_I_JARSAT` CHAR( 10 ) NULL DEFAULT NULL
			";
			*/

			/*
			$sqlList[] = "ALTER TABLE `USERS2_MARKETING_OVERWIEV_REWIR` CHANGE `ABO_I` `ABO_I` CHAR( 10 ) NOT NULL DEFAULT '0',
				CHANGE `MIESZKAN` `MIESZKAN` CHAR( 10 ) NOT NULL DEFAULT '0',
				CHANGE `NAS_I` `NAS_I` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `ANKIET_I` `ANKIET_I` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_UPC` `PR_I_UPC` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_INNY` `PR_I_INNY` CHAR( 10 ) NULL DEFAULT NULL ,  CHANGE `PR_I_NSM` `PR_I_NSM` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_TP` `PR_I_TP` CHAR( 10 ) NULL DEFAULT NULL ,  CHANGE `PR_I_EIA` `PR_I_EIA` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_BRAK` `PR_I_BRAK` CHAR( 10 ) NULL DEFAULT NULL ,  CHANGE `PR_I_KONKUR` `PR_I_KONKUR` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `BADAN_I` `BADAN_I` CHAR( 10 ) NULL DEFAULT NULL ,
				CHANGE `PR_I_ZAINT` `PR_I_ZAINT` CHAR( 10 ) NULL DEFAULT NULL
			";
			*/

			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_RECORD_CREATE_DATE varchar(30)  ;"; 
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_RECORD_CREATE_AUTHOR varchar(100) not null  ;"; 
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_RECORD_UPDATE_DATE varchar(30)  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_RECORD_UPDATE_AUTHOR varchar(100) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add L_APPOITMENT_DATE varchar(30) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add L_APPOITMENT_USER varchar(100) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add L_APPOITMENT_PERIOD varchar(4) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add L_APPOITMENT_INFO varchar(255) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add L_APPOITMENT_TYPE enum('', 'ARRANGED', 'TO_ARRANGE', 'CANCELLED', 'TO_CANCEL', 'RELATE', 'WAIT')  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_PROBLEM enum('', 'WARNING', 'PROBLEM', 'SERIOUS', 'UNVERIFIED')  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_PROBLEM_DESC varchar(255) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_PROBLEM_DATE varchar(30) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_STATUS enum('WAITING', 'NORMAL', 'MONITOR', 'WARNING', 'OFF_SOFT', 'OFF_HARD', 'DELETED')  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_STATUS_INFO varchar(255) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add A_CLASSIFIED varchar(100) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add T_WORKPOINTS varchar(100) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add T_WORKPOINTS_VALUE varchar(100) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add T_WORKPOINTS_TYPE varchar(100) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add T_WORKPOINTS_USER varchar(100) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add T_WORKPOINTS_DATE varchar(100) not null  ; ";
			$sqlList[] = " alter table USERS2_MARKETING_OVERWIEV add P_ACCOUNT varchar(40) not null  ; ";
		}

	}

	$errors = false;
	foreach ($sqlList as $sql) {
		echo'<pre style="max-height:200px;overflow:auto;border:1px solid green;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';
		$res = $db->query($sql);
		if ($db->has_errors()) {
			$errors = true;
			echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;"> (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($db->get_errors());echo'</pre>';
		}
	}

	if ($errors) {
		echo '<div class="alert alert-error">' . "Uwaga wystąpiły błędy - w czerwonej ramce" . '</div>';
	} else {
		echo '<div class="alert alert-success">' . "Operacje zakończone pomyślnie" . '</div>';
	}

}