se/SE/budynki-STATYSTYKA_TABELE.php

<?php

die('funkcja wylaczona - pewnie zastapiona przez superedit-STATYSTYKA_TABELE.php');


if($_GET[STATYSTYKA_TABELE]) {
//by plabudda 2012-03-26: removed after ADD FOREIGN KEY (`T_TELBOX_BUILDING_IN`)
// echo "<br> Wykonuje UPDATE USERS2_MARKETING SET T_TELBOX_BUILDING_IN=SUBSTRING_INDEX(S_ADDRESS_STREET,'/',1)<br> ";
# ZAP_SQL("UPDATE USERS2_MARKETING SET T_TELBOX_BUILDING_IN=S_ADDRESS_STREET");
// ZAP_SQL("UPDATE USERS2_MARKETING SET T_TELBOX_BUILDING_IN=SUBSTRING_INDEX(S_ADDRESS_STREET,'/',1)");
  $ZAP_SQL="drop table if exists temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL"; echo "<br><br> Wykonuje $ZAP_SQL \n";
 ZAP_SQL($ZAP_SQL);
  $ZAP_SQL="drop table if exists temp_USERS2_INTERNET_STATUS_VOIP_NORMAL"; echo "<br><br> Wykonuje $ZAP_SQL \n";
 ZAP_SQL($ZAP_SQL);
  $ZAP_SQL="drop table if exists temp_USERS2_INTERNET_STATUS_TV_NORMAL"; echo "<br><br> Wykonuje $ZAP_SQL \n";
 ZAP_SQL($ZAP_SQL);

  $ZAP_SQL="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 USERS2_INTERNET_STATUS not like '%VOIP%' group by S_ADDRESS_STREET ;";echo "<br><br> Wykonuje $ZAP_SQL \n";
 ZAP_SQL($ZAP_SQL);
  $ZAP_SQL="alter table temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL add index(S_ADDRESS_STREET)"; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);
  $ZAP_SQL="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 USERS2_INTERNET_STATUS  like '%VOIP%' group by S_ADDRESS_STREET ;";echo "<br><br> Wykonuje $ZAP_SQL \n";
 ZAP_SQL($ZAP_SQL);
  $ZAP_SQL="alter table temp_USERS2_INTERNET_STATUS_VOIP_NORMAL add index(S_ADDRESS_STREET)"; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);


  $ZAP_SQL="create table temp_USERS2_INTERNET_STATUS_TV_NORMAL select ID,S_ADDRESS_STREET,concat(A_STATUS_BILLING_USERS,'.',A_STATUS,'.',P_SERVICE,'.', SERVICES_ABONAMENT_VALUE) as A_STATUS_BILLING_USERS , DEALS_TABLE_P_DEALDATE_TERM as P_DEALEXPIRE   from SES_TV_A  where A_STATUS_BILLING_USERS like '%NORMAL%' or A_STATUS_BILLING_USERS like '%WAITING%' group by S_ADDRESS_STREET ;";echo "<br><br> Wykonuje $ZAP_SQL \n";
 ZAP_SQL($ZAP_SQL);
  $ZAP_SQL="alter table temp_USERS2_INTERNET_STATUS_TV_NORMAL add index(S_ADDRESS_STREET)"; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);

// update USERS2_MARKETING fields: I_OPER, I_OPER_FIN
  $ZAP_SQL="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 ; "; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);

//Tabela do odlaczen 08-02-22
  $ZAP_SQL="drop table if exists temp_USERS2_INTERNET_STATUS_OFF_HARD"; echo "<br><br> Wykonuje $ZAP_SQL \n";
 ZAP_SQL($ZAP_SQL);


  $ZAP_SQL="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' ; ";  echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);
// update USERS2_MARKETING fields: I_OPER
  $ZAP_SQL="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 ; "; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);


// update USERS2_MARKETING fields: I_OPER, T_OPER_FIN
  $ZAP_SQL="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 ; "; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);

// update USERS2_MARKETING fields: TV_OPER, TV_OPER_FIN
  $ZAP_SQL="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 ; "; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);

  $ZAP_SQL="drop table if exists USERS2_MARKETING_OVERWIEV"; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);

 $ZAP_SQL="drop table if exists USERS2_MARKETING_OVERWIEV_REWIR"; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);


// update `USERS2_MARKETING` fields: `M_REWIR`
  $ZAP_SQL="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  "; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);



//by plabudda 2012-03-26: removed after ADD FOREIGN KEY (`T_TELBOX_BUILDING_IN`)
// $ZAP_SQL="select ID,T_TELBOX_BUILDING_IN from USERS2_MARKETING where T_TELBOX_BUILDING_IN like '%' ;";
// ZAP_SQL($ZAP_SQL);
// $result2=$result;
// while($h=mysql_fetch_row($result2)) { 
//echo "$h[0] $h[1] ";

//by Bzyk 2007-03-12
#$T_TELBOX_BUILDING_IN=ereg_replace("([[:alpha:]]*)([[:digit:]]*)?([[:alpha:]])?(/[[:alnum:]]*)?$", "\\1\\2", $h[1]);
//$T_TELBOX_BUILDING_IN=ereg_replace("([[:alpha:]]*)([[:digit:]]*)?([[:alpha:]])?$", "\\1\\2", $h[1]);

#$T_TELBOX_BUILDING_IN=ereg_replace("()[[:alpha:]]?(/[0-9]*)?[[:alpha:]]?$", "\\1", $h[1]);  //ereg_replace("()[[:alpha]?(/[0-9]*)?[[:alpha]?$", "\\1", $string);
// $ZAP_SQL="update USERS2_MARKETING set T_TELBOX_BUILDING_IN='$T_TELBOX_BUILDING_IN' where ID='".$h[0]."' ; ";
//  echo " $ZAP_SQL; <br>\n";
// ZAP_SQL("$ZAP_SQL");
// }


# TODO: RMME - to samo jest wyzej
  $ZAP_SQL="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  "; echo "<br><br> Wykonuje $ZAP_SQL \n";
  ZAP_SQL($ZAP_SQL);


/* //BYLO przed 2012-04-23 zle dzialalo.
  $ZAP_SQL="

create table USERS2_MARKETING_OVERWIEV select '' as ID , '' as A_ADM_COMPANY ,
USERS2_MARKETING.T_TELBOX_BUILDING_IN , USERS2_MARKETING.M_REWIR , USERS2_MARKETING.T_NETWORK_SERVER , 
count(temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL.S_ADDRESS_STREET) as ABO_I ,count(USERS2_MARKETING.S_ADDRESS_STREET) as MIESZKAN,
(count(temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL.S_ADDRESS_STREET)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as NAS_I ,
(count(UM_I_BADAN.ID)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as BADAN_I,
(count(UM_I_ANKIET.ID)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as ANKIET_I,
count(UM_I_UPC.ID)/count(UM_I_ANKIET.ID)*100 as PR_I_UPC,
count(UM_I_INNY.ID)/count(UM_I_ANKIET.ID)*100 as PR_I_INNY,
count(UM_I_NSM.ID)/count(UM_I_ANKIET.ID)*100 as PR_I_NSM,
count(UM_I_TP.ID)/count(UM_I_ANKIET.ID)*100 as PR_I_TP,
count(UM_I_EIA.ID)/count(UM_I_ANKIET.ID)*100 as PR_I_EIA,
count(UM_I_BRAK.ID)/count(UM_I_ANKIET.ID)*100 as PR_I_BRAK,
count(UM_I_KONKUR.ID)/count(UM_I_ANKIET.ID)*100 as PR_I_KONKUR,
count(UM_I_ZAINT.ID)/count(UM_I_ANKIET.ID)*100 as PR_I_ZAINT,

count(temp_USERS2_INTERNET_STATUS_VOIP_NORMAL.S_ADDRESS_STREET) as ABO_T,
(count(temp_USERS2_INTERNET_STATUS_VOIP_NORMAL.S_ADDRESS_STREET)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as NAS_T ,
(count(UM_T_BADAN.ID)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as BADAN_T,
(count(UM_T_ANKIET.ID)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as ANKIET_T,
count(UM_T_BRAK.ID)/count(UM_T_ANKIET.ID)*100 as PR_T_BRAK,
count(UM_T_KONKUR.ID)/count(UM_T_ANKIET.ID)*100 as PR_T_KONKUR,
count(UM_T_ZAINT.ID)/count(UM_T_ANKIET.ID)*100 as PR_T_ZAINT ,


count(temp_USERS2_INTERNET_STATUS_TV_NORMAL.S_ADDRESS_STREET) as ABO_TV,
(count(temp_USERS2_INTERNET_STATUS_TV_NORMAL.S_ADDRESS_STREET)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as NAS_TV ,
(count(UM_TV_BADAN.ID)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as BADAN_TV,
(count(UM_TV_ANKIET.ID)/count(USERS2_MARKETING.S_ADDRESS_STREET)*100) as ANKIET_TV,
count(UM_TV_UPC.ID)/count(UM_TV_ANKIET.ID)*100 as PR_TV_UPC,
count(UM_TV_INNY.ID)/count(UM_TV_ANKIET.ID)*100 as PR_TV_INNY,
count(UM_TV_JARSAT.ID)/count(UM_TV_ANKIET.ID)*100 as PR_TV_JARSAT,
count(UM_TV_BRAK.ID)/count(UM_T_ANKIET.ID)*100 as PR_TV_BRAK,
count(UM_TV_KONKUR.ID)/count(UM_T_ANKIET.ID)*100 as PR_TV_KONKUR,
count(UM_TV_ZAINT.ID)/count(UM_T_ANKIET.ID)*100 as PR_TV_ZAINT




from USERS2_MARKETING as USERS2_MARKETING
left join temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL on (temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL.S_ADDRESS_STREET=USERS2_MARKETING.S_ADDRESS_STREET)
left join USERS2_MARKETING as UM_I_BADAN on (UM_I_BADAN.ID=USERS2_MARKETING.ID and UM_I_BADAN.I_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)   )
left join USERS2_MARKETING as UM_I_ANKIET on (UM_I_ANKIET.ID=USERS2_MARKETING.ID and (UM_I_ANKIET.I_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000) or UM_I_ANKIET.I_OPER='BN' )   )
left join USERS2_MARKETING as UM_I_UPC on UM_I_UPC.ID=UM_I_ANKIET.ID and UM_I_UPC.I_OPER='UPC'
left join USERS2_MARKETING as UM_I_INNY on UM_I_INNY.ID=UM_I_ANKIET.ID and UM_I_INNY.I_OPER='INNY'
left join USERS2_MARKETING as UM_I_NSM on UM_I_NSM.ID=UM_I_ANKIET.ID and UM_I_NSM.I_OPER='NSM'
left join USERS2_MARKETING as UM_I_TP on UM_I_TP.ID=UM_I_ANKIET.ID and UM_I_TP.I_OPER='TP'
left join USERS2_MARKETING as UM_I_EIA on UM_I_EIA.ID=UM_I_ANKIET.ID and UM_I_EIA.I_OPER='EIA'
left join USERS2_MARKETING as UM_I_BRAK on UM_I_BRAK.ID=UM_I_ANKIET.ID and UM_I_BRAK.I_OPER='BRAK'
left join USERS2_MARKETING as UM_I_KONKUR on (UM_I_KONKUR.ID=UM_I_ANKIET.ID and (UM_I_KONKUR.I_OPER!='BN' and UM_I_KONKUR.I_OPER!='BRAK' and  UM_I_KONKUR.I_OPER!='NIEWIADOMO'))
left join USERS2_MARKETING as UM_I_ZAINT on UM_I_ZAINT.ID=UM_I_ANKIET.ID and UM_I_ZAINT.I_OPER_ZAINT>2 and  UM_I_ZAINT.I_OPER_ZAINT!='NIEWIADOMO'

left join temp_USERS2_INTERNET_STATUS_VOIP_NORMAL on (temp_USERS2_INTERNET_STATUS_VOIP_NORMAL.S_ADDRESS_STREET=USERS2_MARKETING.S_ADDRESS_STREET)
left join USERS2_MARKETING as UM_T_BADAN on (UM_T_BADAN.ID=USERS2_MARKETING.ID and UM_T_BADAN.T_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)   )
left join USERS2_MARKETING as UM_T_ANKIET on (UM_T_ANKIET.ID=USERS2_MARKETING.ID and (UM_T_ANKIET.T_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000) or UM_T_ANKIET.T_OPER='BN' )   )
left join USERS2_MARKETING as UM_T_BRAK on UM_T_BRAK.ID=UM_T_ANKIET.ID and UM_T_BRAK.T_OPER='BRAK'
left join USERS2_MARKETING as UM_T_KONKUR on (UM_T_KONKUR.ID=UM_T_ANKIET.ID and (UM_T_KONKUR.T_OPER!='BN' and UM_T_KONKUR.T_OPER!='BRAK' and  UM_T_KONKUR.T_OPER!='NIEWIADOMO'))
left join USERS2_MARKETING as UM_T_ZAINT on UM_T_ZAINT.ID=UM_T_ANKIET.ID and UM_T_ZAINT.T_OPER_ZAINT>2 and  UM_T_ZAINT.T_OPER_ZAINT!='NIEWIADOMO'

left join temp_USERS2_INTERNET_STATUS_TV_NORMAL on (temp_USERS2_INTERNET_STATUS_TV_NORMAL.S_ADDRESS_STREET=USERS2_MARKETING.S_ADDRESS_STREET)
left join USERS2_MARKETING as UM_TV_BADAN on (UM_TV_BADAN.ID=USERS2_MARKETING.ID and UM_TV_BADAN.TV_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000)   )
left join USERS2_MARKETING as UM_TV_ANKIET on (UM_TV_ANKIET.ID=USERS2_MARKETING.ID and (UM_TV_ANKIET.TV_BAD_DATE> from_unixtime(unix_timestamp(now())-31536000) or UM_TV_ANKIET.TV_OPER='BN' )   )
left join USERS2_MARKETING as UM_TV_UPC on UM_TV_UPC.ID=UM_I_ANKIET.ID and UM_TV_UPC.TV_OPER='UPC'
left join USERS2_MARKETING as UM_TV_INNY on UM_TV_INNY.ID=UM_TV_ANKIET.ID and UM_TV_INNY.TV_OPER='INNY'
left join USERS2_MARKETING as UM_TV_JARSAT on UM_TV_JARSAT.ID=UM_TV_ANKIET.ID and UM_TV_JARSAT.TV_OPER='JARSAT'
left join USERS2_MARKETING as UM_TV_BRAK on UM_TV_BRAK.ID=UM_TV_ANKIET.ID and UM_TV_BRAK.TV_OPER='BRAK'
left join USERS2_MARKETING as UM_TV_KONKUR on (UM_TV_KONKUR.ID=UM_TV_ANKIET.ID and (UM_TV_KONKUR.TV_OPER!='BN' and UM_TV_KONKUR.TV_OPER!='BRAK' and  UM_TV_KONKUR.TV_OPER!='NIEWIADOMO'))
left join USERS2_MARKETING as UM_TV_ZAINT on UM_TV_ZAINT.ID=UM_TV_ANKIET.ID and UM_TV_ZAINT.TV_OPER_ZAINT>2 and  UM_TV_ZAINT.TV_OPER_ZAINT!='NIEWIADOMO'

group by USERS2_MARKETING.T_TELBOX_BUILDING_IN 
order by USERS2_MARKETING.M_REWIR DESC ,USERS2_MARKETING.T_TELBOX_BUILDING_IN

;

  "; echo "<br><br> Wykonuje $ZAP_SQL \n ";
 ZAP_SQL($ZAP_SQL);
 
 */
 
$COLSFORSTATS=array('I','T','TV');
//$COLSFORSTATS=array('I');

$COLSFORSTATS_OPERS=array('INNY','UPC','NSM','TP','EIA','BRAK');

 
 
 //BEGIN MIESZKANIA STATSY
 
  $ZAP_SQL="
create table USERS2_MARKETING_OVERWIEV 
select '' 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
-- FOREACH

,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 ) {
	$ZAP_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 ) {
	$ZAP_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."
	               
	
	        ";

   }

$ZAP_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 
               ";


}
$ZAP_SQL.="
-- EOF FOREACH

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 
;

  "; echo "<br><br> Wykonuje <pre> $ZAP_SQL </pre> \n ";
 ZAP_SQL($ZAP_SQL);
 
 

 //EOF BEGIN MIESZKANIA STATSY
 // REEWIRY STATSY
  $ZAP_SQL="
create table USERS2_MARKETING_OVERWIEV_REWIR 
select '' 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
-- FOREACH

,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 ) {
		$ZAP_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."
	               
	
	        ";
  }

$ZAP_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 
               ";


}
$ZAP_SQL.="
-- EOF FOREACH

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 
;

  "; echo "<br><br> Wykonuje $ZAP_SQL \n ";
 ZAP_SQL($ZAP_SQL);
 
  //EOF REEWIRY STATSY

 
 
 $ZAP_SQL="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 "; echo "<br><br> Wykonuje $ZAP_SQL \n";
// ZAP_SQL($ZAP_SQL);


 $ZAP_SQL="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 "; echo "<br><br> Wykonuje $ZAP_SQL \n";
// ZAP_SQL($ZAP_SQL);



}