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() {
	global $A_STATUS_L2_SQL;
	$db = DB::getDB();
	if (!$db) {
		echo '<div class="alert alert-danger">' . "Database Error" . '</div>';
		return;
	}

	$sqlList = array();

	$task = V::get('task', '', $_GET);
	if ($task == 'FVAT_POS_CHARTS') {
		echo "moved to RECOUNT_STATS_BILLS_FVAT_POS";
		return;
	}
	else if ($task == 'FVAT_POS') {
		echo "moved to RECOUNT_STATS_BILLS_FVAT_POS";
		return;
	}
	else {

		{ //@2016-07-11 bindera moved from old superedit-USERS_FIELDS_PROFILE_PRE_INIT.php $_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY']


			//TODO zastapic to czyms Piotrowym @2016-06-11
			if(!isset($A_STATUS_L2_SQL)) {

				echo "<br> WARNING variable A_STATUS_L2_SQL is not set globally to emulate correct active client status.";

				$A_STATUS_L2_SQL="if(SV.HANGUP_STATUS=SV.A_STATUS,coalesce(SV.A_STATUS),
    if( ( unix_timestamp(NOW()) > COALESCE(unix_timestamp(SV.HANGUP_FROM), 0) and  ( SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
    and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )) , coalesce(SV.HANGUP_STATUS),
    if( ( unix_timestamp(NOW()) < COALESCE(unix_timestamp(SV.HANGUP_FROM), 0) and  ( SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
    and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )) , coalesce(SV.A_STATUS),
    if( ( unix_timestamp(NOW()) > COALESCE(unix_timestamp(SV.HANGUP_FROM), 0) and  unix_timestamp(NOW()) < COALESCE(unix_timestamp(SV.HANGUP_TILL), 0)
    and  ( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
    and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )) , coalesce(SV.HANGUP_STATUS),
    if( ( unix_timestamp(NOW()) < COALESCE(unix_timestamp(SV.HANGUP_FROM), 0) and  unix_timestamp(NOW())< COALESCE(unix_timestamp(SV.HANGUP_TILL), 0)
    and  ( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
    and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )) , coalesce(SV.A_STATUS),
    if( (
      ( SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
    and ( SV.HANGUP_FROM='0000-00-00' or SV.HANGUP_FROM is null )) , coalesce(SV.A_STATUS),
                if( unix_timestamp(NOW()) > COALESCE(unix_timestamp(SV.HANGUP_FROM), 0) and  unix_timestamp(NOW()) > COALESCE(unix_timestamp(SV.HANGUP_TILL), 0)  and (
      ( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
           and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )) , coalesce(SV.A_STATUS),
                if( unix_timestamp(NOW()) > COALESCE(unix_timestamp(SV.HANGUP_FROM), 0) and  unix_timestamp(NOW()) > COALESCE(unix_timestamp(SV.HANGUP_TILL), 0)  and
                SV.HANGUP_FROM<SV.HANGUP_TILL and (
         ( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
           and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )) , 'ERROR1',
     'ERROR2'
      )
      )
      )
      )
      )
     )
    )
   )
 ";
 }

 			{
	 			//@2016-07 bindera: dodanie prostych raportow bledow brakujacych projektow do budynkow:
		 $sqlList[]="drop view `BUILDINGS_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_ERROR`;";
		 		 $sqlList[]=" create view `BUILDINGS_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_ERROR` as
		 			 select b.ID, b.A_STATUS ,  b.S_ADDRESS_STREET, b.M_REWIR ,
		 			 rks.ID as ID_KABEL, rks.A_STATUS as A_STATUS_KABEL, 'Brak zaplanowanego przylacza' as ERROR
		 			 , null as USERS2_MARKETING_ID, null as USERS2_DEALS_ID

		 			 from BUILDINGS as b
		 			 left join Rozdzielcza_Kabel_Swiatlowodowy_wsg84 as rks on b.ID=rks.ID_BUILDINGS
		 			 where rks.ID is NULL  and b.A_STATUS in ('NORMAL','WAITING')


		 			 union
		 			  -- bledy braku wprowadzonych umow z wlasciwymi statusami do budynkow jak jest umowa na usluge, to budynek musi byc WAITING lub NORMAL
		 			  select b.ID, b.A_STATUS ,  b.S_ADDRESS_STREET, b.M_REWIR ,
		 			  null as ID_KABEL, NULL as A_STATUS_KABEL, 'Zakupiona usluga na budynku, gdzie nie ma umowy lub brak aktualizacji statusu' as ERROR
		 			  , um.ID as USERS2_MARKETING_ID, ud.ID as USERS2_DEALS_ID
		 			  from USERS2_DEALS as ud
		 			  left join USERS2_MARKETING as um on um.S_ADDRESS_STREET=ud.S_ADDRESS_STREET
		 			  left join BUILDINGS as b on b.S_ADDRESS_STREET=um.T_TELBOX_BUILDING_IN
		 			 where ud.A_STATUS!='DELETED' and b.A_STATUS not in ('NORMAL','WAITING')
		 			  group by ud.S_ADDRESS_STREET

		 			 -- bledy nie aktualizowanego statusu budynku dla dzialajacej uslugi
		 			  union
		 			  select b.ID, b.A_STATUS ,  b.S_ADDRESS_STREET, b.M_REWIR ,
		 			  null as ID_KABEL, NULL as A_STATUS_KABEL, 'Usługa działająca na budynku, który nie ma statusu NORMAL' as ERROR
		 			  , um.ID as USERS2_MARKETING_ID, ud.ID as USERS2_DEALS_ID
		 			  from USERS2_DEALS as ud
		 			  left join USERS2_MARKETING as um on um.S_ADDRESS_STREET=ud.S_ADDRESS_STREET
		 			  left join BUILDINGS as b on b.S_ADDRESS_STREET=um.T_TELBOX_BUILDING_IN
		 			  left join SES_USERS2_A as internet on internet.ID_SERVICES=ud.ID_SERVICES_NEW
		 			 where ud.A_STATUS!='DELETED' and b.A_STATUS!='NORMAL'
		 			  and internet.A_STATUS='NORMAL'
		 			  group by ud.S_ADDRESS_STREET

		 			  -- brak mieszkan powiazanych do budynku
		 			  UNION

		 			  select b.ID, b.A_STATUS ,  b.S_ADDRESS_STREET, b.M_REWIR ,
		 			  NULL as ID_KABEL, NULL as A_STATUS_KABEL, 'Brak mieszkan dowiazanych do budynku' as ERROR
		 			 , um.ID as USERS2_MARKETING_ID, null as USERS2_DEALS_ID

		 			 from BUILDINGS as b
		 			 left join USERS2_MARKETING as um on um.T_TELBOX_BUILDING_IN=b.S_ADDRESS_STREET
		 			 where um.ID is NULL  and b.A_STATUS!='DELETED'




		 			 ";


		 $sqlList[]="update BUILDINGS set `__Rozdzielcza_Kabel_Swiatlowodowy_wsg84`=null ; ";
		 $sqlList[]="update BUILDINGS t1, Rozdzielcza_Kabel_Swiatlowodowy_wsg84 t2 set t1.__Rozdzielcza_Kabel_Swiatlowodowy_wsg84=t2.ID where t1.ID=t2.ID_BUILDINGS ; ";
		 $sqlList[]="update BUILDINGS t1, MK_Rewiry t2 set t1.`__MK_Rewiry__STATUS`=t2.A_STATUS where t1.M_REWIR=t2.NAZWA_REWI and t1.M_REWIR like '%_%' ; ";
		 $sqlList[]="update USERS2_MARKETING t1, MK_Rewiry t2 set t1.`__MK_Rewiry__STATUS`=t2.A_STATUS where t1.M_REWIR=t2.NAZWA_REWI and t1.M_REWIR like '%_%' ; ";



		 $sqlList[]=" update USERS2_MARKETING set T_OPER='NIEWIADOMO' where T_OPER=''";
		 $sqlList[]=" update USERS2_MARKETING set TV_OPER='NIEWIADOMO' where TV_OPER=''";
		 $sqlList[]=" update USERS2_MARKETING set I_OPER='NIEWIADOMO' where I_OPER=''";




 			}


 			{ //posprzatanie filtrow operatorow - todo tabela konkurencji
		 	$oper_fix['DVB%S']='DVBS';
	 		$oper_fix['%N_KA%']='NC_PLUS';
	 		$oper_fix['%UPS%']='UPC';
	 		$oper_fix['%WEKTRA%']='VECTRA';
	 		$oper_fix['%MM_NET%']='MMNET';
	 		$oper_fix['%ORAN%']='ORANGE';
		 	$oper_fix['%T%MOBLILE%']='T-MOBILE';
		 	$oper_fix['%T%MOBILE%']='T-MOBILE';		 	
		 	$oper_fix['%TMOBI%']='T-MOBILE';		 	

		 	$oper_fix['%TP%LIVE%BOX%']='ORANGE';
		 	$oper_fix['%TP%ORANE%']='ORANGE';
		 	$oper_fix['TP']='ORANGE';
		 	$oper_fix['%BRAK%']='BRAK';
		 	$oper_fix['%W%KOMURCE%']='GSM';
		 	$oper_fix['CANAL%']='CANAL_PLUS';
		 	$oper_fix['%CYFRA%']='CYFRA_PLUS';
		 	$oper_fix['KABL%WKA']='INNY';
		 	$oper_fix['NAZ%EMNA']='DVBC';
		 	$oper_fix['NC%']='NC_PLUS';
		 	$oper_fix['POLSAT%']='POLSAT';
		 	$oper_fix['POLAT']='POLSAT';
		 	$oper_fix['SATELITA%']='DVB-S';
		 	$oper_fix['POLAT']='POLSAT';
		 	$oper_fix['S__UZBOWY']='INNY';
		 	$oper_fix['POLAT']='POLSAT';
		 	$oper_fix['NA_KART%']='INNY';
		 	$oper_fix['%BRAK%DANYCH%']='NIEWIADOMO';
		 	$oper_fix['%CYFROWA%NAZIEMNA%']='DVBC';
		 	$oper_fix['KABL__WKA']='INNY';
		 	$oper_fix['%YOUTUBE%']='YOUTUBE';
		 	$oper_fix['_']='NIEWIADOMO';



	 		  foreach($oper_fix as $filtr=>$set) {
		 		  $sqlList[]="update USERS2_MARKETING set I_OPER='".$set."' where I_OPER like '".$filtr."'" ;
		 		  $sqlList[]="update USERS2_MARKETING set TV_OPER='".$set."' where TV_OPER like '".$filtr."'" ;
		 		  $sqlList[]="update USERS2_MARKETING set T_OPER='".$set."' where T_OPER like '".$filtr."'" ;
	 		  }

 			}


 			{ //stworzenie widoku bledow w USERS2_MARKETING dot budynkow
	 			$sqlList[]="drop  view if exists `USERS2_MARKETING__BUILDINGS_ERROR` ;";
	 			$sqlList[]="create ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER view USERS2_MARKETING__BUILDINGS_ERROR as
	 					SELECT t1.ID, t1.M_REWIR, t1.S_ADDRESS_STREET, t1.T_TELBOX_BUILDING_IN
	 					, t1.A_ADM_COMPANY, t1.A_CLASSIFIED, t1.A_RECORD_UPDATE_DATE,
	 					t1.A_RECORD_CREATE_DATE, t1.A_RECORD_CREATE_AUTHOR, t1.A_RECORD_UPDATE_AUTHOR,
	 					'ERROR Nieprawidlowe powiazanie budynku' AS ERROR, '' as A_STATUS
						FROM USERS2_MARKETING AS t1
						LEFT JOIN BUILDINGS AS t2 ON t1.T_TELBOX_BUILDING_IN = t2.S_ADDRESS_STREET
						WHERE t2.ID IS NULL  and ( t2.A_STATUS!='DELETED' or t2.A_STATUS is NULL )
						AND t1.T_TELBOX_BUILDING_IN LIKE  '_%' and t1.A_STATUS!='DELETED'
						UNION
						SELECT t1.ID, t1.M_REWIR, t1.S_ADDRESS_STREET, t1.T_TELBOX_BUILDING_IN
						, t1.A_ADM_COMPANY, t1.A_CLASSIFIED, t1.A_RECORD_UPDATE_DATE, t1.A_RECORD_CREATE_DATE, t1.A_RECORD_CREATE_AUTHOR, t1.A_RECORD_UPDATE_AUTHOR,
						'ERROR Brak powiazania budynku' AS ERROR, '' as A_STATUS
						FROM USERS2_MARKETING AS t1
						WHERE t1.T_TELBOX_BUILDING_IN LIKE  '' and t1.A_STATUS!='DELETED'



						;";

				$sqlList[]="drop  view if exists `BUILDINGS__MARKETING_ERROR` ;";
				$sqlList[]="create ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW BUILDINGS__MARKETING_ERROR as
					 SELECT t1.ID, t1.M_REWIR, t1.S_ADDRESS_STREET, t1.A_ADM_COMPANY, t1.A_CLASSIFIED, t1.A_RECORD_UPDATE_DATE, t1.A_RECORD_CREATE_DATE, t1.A_RECORD_CREATE_AUTHOR, t1.A_RECORD_UPDATE_AUTHOR
					 	,t1.T_BUILDING_MIESZKANIA , 0 as ILOSC_POW_MIESZKAN
					 	,'ERROR Brak powiazanych mieszkan do budynku' AS ERROR
						FROM BUILDINGS AS t1
						LEFT JOIN  USERS2_MARKETING AS t2 ON t2.T_TELBOX_BUILDING_IN = t1.S_ADDRESS_STREET
						WHERE t2.ID IS NULL  and ( t2.A_STATUS!='DELETED' or t2.A_STATUS is NULL )
						 and t1.A_STATUS!='DELETED'
						UNION
						SELECT t1.ID, t1.M_REWIR, t1.S_ADDRESS_STREET, t1.A_ADM_COMPANY, t1.A_CLASSIFIED, t1.A_RECORD_UPDATE_DATE, t1.A_RECORD_CREATE_DATE, t1.A_RECORD_CREATE_AUTHOR, t1.A_RECORD_UPDATE_AUTHOR
						, t1.T_BUILDING_MIESZKANIA
						,count(t2.ID) as ILOSC_POW_MIESZKAN
						, 'ERROR Niewlasciwa ilosc powiazanych mieszkan' AS ERROR
						FROM BUILDINGS AS t1
						LEFT JOIN  USERS2_MARKETING AS t2 ON t2.T_TELBOX_BUILDING_IN = t1.S_ADDRESS_STREET
						WHERE t2.ID IS NOT NULL and ( t2.A_STATUS!='DELETED' )
						and t1.A_STATUS!='DELETED'
 						group by t2.T_TELBOX_BUILDING_IN
						having T_BUILDING_MIESZKANIA!=ILOSC_POW_MIESZKAN


						;";



 			}



			$sqlList[]=" drop table if exists temp_USERS2_INTERNET_STATUS; ";
			$sqlList[]="create table temp_USERS2_INTERNET_STATUS
			select SV.ID,
			US2.S_ADDRESS_STREET, concat_ws('', ".$A_STATUS_L2_SQL." ,'.', coalesce(US2.A_STATUS_CURRENT), ' USLUGA:INTERNET(USERS2)' , ' GNIAZDO/USL:',US2.P_SERVICE,' CENA:',coalesce(SV.ABONAMENT_VALUE),
			' SPEED:',coalesce(US2.P_SERVICE_QUOTA),' DEALD:(',coalesce(DT.ID),coalesce(DT.P_DEALPREFIX), '):', coalesce(DT.P_DEALDATE),'--',coalesce(DT.P_DEALDATE_TERM),
			' KONTAKT_L2:',coalesce(BUA.P_PHONE),',', coalesce(BUA.user_mail_contact),',' , coalesce(BUA.P_FAX)) as USERS2_INTERNET_STATUS
			, DT.P_DEALDATE_TERM as P_DEALEXPIRE , SV.NAME_LIST_SERVICES
			, ".$A_STATUS_L2_SQL." as A_STATUS , SV.HANGUP_STATUS
			from SERVICES as SV
			inner JOIN  SES_USERS2_A as US2  on SV.ID=US2.ID_SERVICES
			inner JOIN BILLING_USERS_ADD  as BUA on BUA.id_users=SV.ID_BILLING_USERS
			left join DEALS_TABLE as DT on DT.ID=SV.ID_DEALS_TABLE
			where SV.NAME_LIST_SERVICES='USERS2' and ( SV.P_ID_SERVICES is NULL   or SV.P_ID_SERVICES=0)
			;";

			$sqlList[]="insert ignore into temp_USERS2_INTERNET_STATUS
                select SV.ID,
                US2.S_ADDRESS_STREET,
                concat_ws('',".$A_STATUS_L2_SQL.",'.', coalesce(US2.A_STATUS_CURRENT), ' USLUGA:',SV.NAME_LIST_SERVICES , ' GNIAZDO/USL:',US2.P_SERVICE,' CENA:',coalesce(SV.ABONAMENT_VALUE)
                ,' DEALD:(',coalesce(DT.ID),coalesce(DT.P_DEALPREFIX), '):', coalesce(DT.P_DEALDATE),'--',coalesce(DT.P_DEALDATE_TERM),
                    ' KONTAKT_L2:',coalesce(BUA.P_PHONE),',' ,coalesce(BUA.user_mail_contact),',' , coalesce(BUA.P_FAX)) as USERS2_INTERNET_STATUS
                 , DT.P_DEALDATE_TERM as P_DEALEXPIRE , SV.NAME_LIST_SERVICES
                 , ".$A_STATUS_L2_SQL." as A_STATUS, SV.HANGUP_STATUS
                 from SERVICES as SV
                inner JOIN  SES_TV_A as US2  on SV.ID=US2.ID_SERVICES
                                inner JOIN BILLING_USERS_ADD  as BUA on BUA.id_users=SV.ID_BILLING_USERS
                left join DEALS_TABLE as DT on DT.ID=SV.ID_DEALS_TABLE
                where SV.NAME_LIST_SERVICES='TV' and ( SV.P_ID_SERVICES is NULL   or SV.P_ID_SERVICES=0) ; ";

			$sqlList[]="insert ignore into temp_USERS2_INTERNET_STATUS
			select SV.ID,
			US22.S_ADDRESS_STREET,
			concat_ws('',".$A_STATUS_L2_SQL.",'.', coalesce(US2.A_STATUS_CURRENT), ' USLUGA:',SV.NAME_LIST_SERVICES ,' KLIENT_ID/P_NAME:',concat_ws('',SV.ID_BILLING_USERS,'/',BUA.P_NAME,' ',BUA.P_NAME_SECOND,';'), ' GNIAZDO/USL:',US2.S_PHONE_NUMBER,' CENA:',coalesce(SV.ABONAMENT_VALUE),' DEALD:(',coalesce(DT.ID),coalesce(DT.P_DEALPREFIX), '):', coalesce(DT.P_DEALDATE),'--',coalesce(DT.P_DEALDATE_TERM) ,
		    ' KONTAKT_L2:',coalesce(BUA.P_PHONE),',' ,coalesce(BUA.user_mail_contact),',' , coalesce(BUA.P_FAX)) as USERS2_INTERNET_STATUS
			, DT.P_DEALDATE_TERM as P_DEALEXPIRE , SV.NAME_LIST_SERVICES
			,  ".$A_STATUS_L2_SQL." as A_STATUS, SV.HANGUP_STATUS
			from SERVICES as SV
			inner JOIN  SES_VOIP_A as US2  on SV.ID=US2.ID_SERVICES
			inner JOIN BILLING_USERS_ADD  as BUA on BUA.id_users=SV.ID_BILLING_USERS
		   inner JOIN SERVICES as SV2 on SV2.P_ID_SERVICES=SV.ID
  		   inner JOIN SES_USERS2_A as US22 on US22.ID_SERVICES=SV2.ID
  		   left join DEALS_TABLE as DT on DT.ID=SV.ID_DEALS_TABLE
  		   where SV.NAME_LIST_SERVICES='VOIP' and ( SV.P_ID_SERVICES is NULL   or SV.P_ID_SERVICES=0)
  		   and ".$A_STATUS_L2_SQL."!='OFF_HARD';";

            $sqlList[]="ALTER TABLE  temp_USERS2_INTERNET_STATUS CHANGE  USERS2_INTERNET_STATUS  USERS2_INTERNET_STATUS VARCHAR( 255 ) NOT NULL;";
			//wyglada na dodanie rekordow z uslug do bazy marketingu
            $sqlList[]="insert ignore into USERS2_MARKETING (A_RECORD_CREATE_DATE,A_RECORD_CREATE_AUTHOR,S_ADDRESS_STREET,T_NETWORK_SERVER, P_PHONE,P_OTHER_INFO,A_STATUS,A_STATUS_INFO,S_ALIAS)  SELECT A_RECORD_CREATE_DATE,A_RECORD_CREATE_AUTHOR,S_ADDRESS_STREET,T_NETWORK_SERVER,P_PHONE,P_OTHER_INFO,A_STATUS,A_STATUS_INFO,S_ALIAS FROM USERS2 ORDER BY A_RECORD_UPDATE_DATE DESC ;";
			//wyglada na aktualizacje telefonu z innego miejsca -ale wyremowana
            $sqlList[]="UPDATE USERS2_MARKETING AS USERS2_MARKETING , USERS2 AS USERS2 SET USERS2_MARKETING.S_ALIAS=USERS2.S_ALIAS ,
						USERS2_MARKETING.P_PHONE=USERS2.P_PHONE WHERE USERS2_MARKETING.S_ADDRESS_STREET=USERS2.S_ADDRESS_STREET AND USERS2.A_RECORD_UPDATE_DATE>USERS2_MARKETING.A_RECORD_UPDATE_DATE and 1=0 ;";
            $sqlList[]="alter table temp_USERS2_INTERNET_STATUS add index(S_ADDRESS_STREET) , add  index(USERS2_INTERNET_STATUS) ;";
			////@2012-04-24 aktualizacja ADM_COMPANY z BUILDINGS

            $sqlList[]="UPDATE USERS2_MARKETING AS USERS2_MARKETING , BUILDINGS AS BUILDINGS
            			SET USERS2_MARKETING.A_ADM_COMPANY=BUILDINGS.A_ADM_COMPANY , USERS2_MARKETING.A_CLASSIFIED=BUILDINGS.A_CLASSIFIED
            			, USERS2_MARKETING.ID_PROJECT=BUILDINGS.ID_PROJECT
						WHERE USERS2_MARKETING.T_TELBOX_BUILDING_IN=BUILDINGS.S_ADDRESS_STREET;";
						
			$sqlList[]="UPDATE USERS2_MARKETING AS USERS2_MARKETING , DEALS_TABLE AS DEALS_TABLE
            			SET DEALS_TABLE.ID_PROJECT=USERS2_MARKETING.ID_PROJECT
						WHERE DEALS_TABLE.S_ADDRESS_STREET=USERS2_MARKETING.S_ADDRESS_STREET and DEALS_TABLE.ID_PROJECT=0;";


		}

		{// 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 ignore 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_default = array('INNY','UPC','NSM','TP','EIA','BRAK','JARSAT');


			//@2016-07-01 automatyczne przetwarzanie roznych operatorow i ich skrotow na podstawie analizy obecnie dostepnych
						$sql_OPERS="select distinct(I_OPER) as OPERS from USERS2_MARKETING
						union select distinct(T_OPER) as OPERS from USERS2_MARKETING
						union select distinct(TV_OPER) as OPERS from USERS2_MARKETING
						group by OPERS
						";
			 $res_OPERS=$db->query($sql_OPERS);
			 while($row_OPERS=$db->fetch($res_OPERS)) {
				if(strlen($row_OPERS->OPERS)>0)
					if(!in_array(strtolower(preg_replace('/[^a-zA-Z0-9_\-]/','_',$row_OPERS->OPERS), $COLSFORSTATS_OPERS)))
						 if(!in_array(strtoupper(preg_replace('/[^a-zA-Z0-9_\-]/','_',$row_OPERS->OPERS), $COLSFORSTATS_OPERS)))
						 	if(!in_array(preg_replace('/[^a-zA-Z0-9_\-]/','_',$row_OPERS->OPERS, $COLSFORSTATS_OPERS))) {
							 	//echo "dodaje ".strtoupper(preg_replace('/[^a-zA-Z0-9\-_]/','_',$row_OPERS->OPERS));
						 		$COLSFORSTATS_OPERS[]=strtoupper(preg_replace('/[^a-zA-Z0-9_]/','_',$row_OPERS->OPERS));
						 	}
				if($row_OPERS->OPERS!=preg_replace('/[^a-zA-Z0-9_\-]/','_',$row_OPERS->OPERS)) {
					//aktualizacja w bazie nazwy operatora aby byla bez dziwnych znakow
					foreach($COLSFORSTATS as $FFS) {
						$sql_upd="update USERS2_MARKETING set `{$FFS}_OPER`='".strtoupper(preg_replace('/[^a-zA-Z0-9_\-]/','_',$row_OPERS->OPERS))."' where `{$FFS}_OPER`='".$row_OPERS->OPERS."'";
						$res = $db->query($sql_upd);
						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>';
						}
					}
				}

			 }

			 //upewnienie sie, ze sa domyslne nazwy firm-operatorow
			 foreach($COLSFORSTATS_OPERS_default as $OPER ) {
				if(!in_array($OPER, $COLSFORSTATS_OPERS))	$COLSFORSTATS_OPERS[]=$OPER;
			 }


			 echo'<pre style="max-height:200px;overflow:auto;border:1px solid green;text-align:left;">Wykryci operatorzy do analizy: COLSFORSTATS_OPERS (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($COLSFORSTATS_OPERS);echo'</pre>';


			//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())-315360000,'%Y-%m-%d')
						or USERS2_MARKETING.T_BAD_DATE> from_unixtime(unix_timestamp(now())-315360000,'%Y-%m-%d')
						or USERS2_MARKETING.TV_BAD_DATE> from_unixtime(unix_timestamp(now())-315360000,'%Y-%m-%d')
						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
				, sum(  if( USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY < 50, 1 ,0 ) )  as PAY_50_less 
				, sum(  if( USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  > 50 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  < 70, 1 ,0 ) )  as PAY_50_70
				, sum(  if( USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  > 70 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  < 100, 1 ,0 ) )  as PAY_70_100
				, sum(  if( USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  > 100 , 1 ,0 ) )  as PAY_100_more
				, BUILDINGS.A_ADM_COMPANY
				, BUILDINGS.the_geom, BUILDINGS.A_STATUS,  BUILDINGS.ID_BILLING_USERS 
			";
			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 
				-- and 1=1
				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())-315360000,'%Y-%m-%d')
						or USERS2_MARKETING.T_BAD_DATE> from_unixtime(unix_timestamp(now())-315360000,'%Y-%m-%d')
						or USERS2_MARKETING.TV_BAD_DATE> from_unixtime(unix_timestamp(now())-315360000,'%Y-%m-%d')
						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
					, sum(  if( USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY < 50, 1 ,0 ) )  as PAY_50_less 
				, sum(  if( USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  > 50 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  < 70, 1 ,0 ) )  as PAY_50_70
				, sum(  if( USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  > 70 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  < 100, 1 ,0 ) )  as PAY_70_100
				, sum(  if( USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY > 0 and USERS2_MARKETING.I_PAY + USERS2_MARKETING.T_PAY + USERS2_MARKETING.TV_PAY  > 100 , 1 ,0 ) )  as PAY_100_more
				, BUILDINGS.A_ADM_COMPANY
				, MK_Rewiry.the_geom , MK_Rewiry.A_STATUS
				, count(BUILDINGS.ID) as BUDYNKOW -- To jest bledne - poprawka potem - nie da sie tu policzyc
				, sum(if(BUILDINGS.A_STATUS='NORMAL',1,0)) as BUDYNKOW_NORMAL -- to jest bledne - poprawka potem - nie da sie tu policzyc
				, sum(if(BUILDINGS.A_STATUS='WAITING',1,0)) as BUDYNKOW_WAITING -- to jest bledne - poprawka potem - nie da sie tu policzyc
				, MK_Rewiry.gis_square_ha_area
				, count(BUILDINGS.ID)/MK_Rewiry.gis_square_ha_area as REWIR_BUILDINGS_DENSITY -- to jest bledne - poprawka potem - nie da sie tu policzyc
				, count(USERS2_MARKETING.S_ADDRESS_STREET)/MK_Rewiry.gis_square_ha_area as REWIR_HOMES_DENSITY
				, BUILDINGS.A_CLASSIFIED

			";
			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
					left join MK_Rewiry on MK_Rewiry.NAZWA_REWI=BUILDINGS.M_REWIR
				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` CHANGE `M_REWIR`  `M_REWIR` CHAR(255) NOT NULL DEFAULT '' ";
			$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}";


			$sqlList[]="UPDATE  `USERS2_MARKETING_OVERWIEV_REWIR` AS t1 SET t1.BUDYNKOW = ( SELECT COUNT( t2.ID )  FROM BUILDINGS AS t2 WHERE t1.M_REWIR = t2.M_REWIR ) ";
			$sqlList[]="UPDATE  `USERS2_MARKETING_OVERWIEV_REWIR` AS t1 SET t1.BUDYNKOW_NORMAL = ( SELECT COUNT( t2.ID )  FROM BUILDINGS AS t2 WHERE t1.M_REWIR = t2.M_REWIR and t2.A_STATUS='NORMAL') ";
			$sqlList[]="UPDATE  `USERS2_MARKETING_OVERWIEV_REWIR` AS t1 SET t1.BUDYNKOW_WAITING = ( SELECT COUNT( t2.ID )  FROM BUILDINGS AS t2 WHERE t1.M_REWIR = t2.M_REWIR and t2.A_STATUS='WAITING') ";
			$sqlList[]="UPDATE  `USERS2_MARKETING_OVERWIEV_REWIR` AS t1 SET t1.REWIR_BUILDINGS_DENSITY = t1.BUDYNKOW/t1.gis_square_ha_area  ";


			//22913_PODMIOT_NETDAY_zoo_podmiot

			$sqlList[]="UPDATE BUILDINGS t1, MK_Rewiry t2 set t1.A_ADM_COMPANY=t2.A_ADM_COMPANY, t1.A_CLASSIFIED=t2.A_CLASSIFIED where t2.NAZWA_REWI=t1.M_REWIR";
			$sqlList[]="UPDATE USERS2_MARKETING t1, MK_Rewiry t2 set t1.A_ADM_COMPANY=t2.A_ADM_COMPANY, t1.A_CLASSIFIED=t2.A_CLASSIFIED where t2.NAZWA_REWI=t1.M_REWIR";
			
			$sqlList[]="UPDATE BUILDINGS t1, USERS2_MARKETING t2 set t2.M_REWIR=t1.M_REWIR where t1.S_ADDRESS_STREET=t2.T_TELBOX_BUILDING_IN";

			$sqlList[]="UPDATE USERS2_OFFERS_GROUPS set A_CLASSIFIED='22913_PODMIOT_NETDAY_zoo_podmiot' , A_ADM_COMPANY='22913_PODMIOT_NETDAY_zoo_podmiot' where P_SERVICE_COMPANY='NETDAY' ";
			$sqlList[]="UPDATE USERS2_OFFERS t1, USERS2_OFFERS_GROUPS t2 set t1.A_ADM_COMPANY=t2.A_ADM_COMPANY, t1.A_CLASSIFIED=t2.A_CLASSIFIED , t1.P_SERVICE_COMPANY=t2.P_SERVICE_COMPANY where t2.ID_NET=t1.ID or t2.ID_TEL=t1.ID or t2.ID_TV=t1.ID";
			$sqlList[]="UPDATE USERS2_OFFERS_COSTS_GROUPS t1, USERS2_OFFERS t2 set t1.A_ADM_COMPANY=t2.A_ADM_COMPANY, t1.A_CLASSIFIED=t2.A_CLASSIFIED  where t2.ID_COSTS_GROUPS=t1.ID";


		}

		{// 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  ; ";
			$sqlList[] = "ALTER TABLE `USERS2_MARKETING_OVERWIEV_REWIR` CHANGE `the_geom` `the_geom` POLYGON NULL DEFAULT NULL;";
			$sqlList[] = "ALTER TABLE `USERS2_MARKETING_OVERWIEV` CHANGE `the_geom` `the_geom` POLYGON NULL DEFAULT NULL;";
			$sqlList[] = "update BUILDINGS as b 
							inner join (
							select p.ID , p.the_geom from IN7_MK_BAZA_DYSTRYBUCJI as p    group by p.ID order by ST_Area(p.the_geom) 
							) as p on  ST_Intersects(p.the_geom,b.the_geom)
							set b.ID_PROJECT=p.ID where  b.ID_PROJECT<1 " ; //zrobic update BUILDINGS do ID_PROJECT na podstawie the_geom najmniejszegoi
			
			$sqlList[] = "update BADANIA_W_TERENIE as b 
							inner join (
							select p.ID , p.the_geom from IN7_MK_BAZA_DYSTRYBUCJI as p    group by p.ID order by ST_Area(p.the_geom) 
							) as p on  ST_Intersects(p.the_geom,b.the_geom)
							set b.ID_PROJECT=p.ID where  b.ID_PROJECT<1 " ; //zrobic update BUILDINGS do ID_PROJECT na podstawie the_geom najmniejszegoi
			
			$sqlList[] = "	create temporary table PROBLEMS_temp select * from PROBLEMS where ID_PROJECT < 1 ;";
							
			$sqlList[] = "				update PROBLEMS_temp as b 
							inner join (
							select p.ID , p.the_geom from IN7_MK_BAZA_DYSTRYBUCJI as p    group by p.ID order by ST_Area(p.the_geom) 
							) as p on   ST_Intersects(p.the_geom,b.the_geom)
							set b.ID_PROJECT=p.ID where  b.ID_PROJECT<1  ;";
							
			$sqlList[] = "	update PROBLEMS_temp as b, PROBLEMS b1 set b1.ID_PROJECT=b.ID_PROJECT where b1.ID=b.ID;";
							
			$sqlList[] = "	drop table IF EXISTS PROBLEMS_temp; ";

							

		}

	}

	$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-danger">' . "Uwaga wystąpiły błędy - w czerwonej ramce" . '</div>';
	} else {
		echo '<div class="alert alert-success">' . "Operacje zakończone pomyślnie" . '</div>';
	}

}