Wykonuje UPDATE USERS2_MARKETING SET T_TELBOX_BUILDING_IN=SUBSTRING_INDEX(S_ADDRESS_STREET,'/',1)
";
# 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 "
Wykonuje $ZAP_SQL \n";
ZAP_SQL($ZAP_SQL);
$ZAP_SQL="drop table if exists temp_USERS2_INTERNET_STATUS_VOIP_NORMAL"; echo "
Wykonuje $ZAP_SQL \n";
ZAP_SQL($ZAP_SQL);
$ZAP_SQL="drop table if exists temp_USERS2_INTERNET_STATUS_TV_NORMAL"; echo "
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 "
Wykonuje $ZAP_SQL \n";
ZAP_SQL($ZAP_SQL);
$ZAP_SQL="alter table temp_USERS2_INTERNET_STATUS_INTERNET_NORMAL add index(S_ADDRESS_STREET)"; echo "
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 "
Wykonuje $ZAP_SQL \n";
ZAP_SQL($ZAP_SQL);
$ZAP_SQL="alter table temp_USERS2_INTERNET_STATUS_VOIP_NORMAL add index(S_ADDRESS_STREET)"; echo "
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 "
Wykonuje $ZAP_SQL \n";
ZAP_SQL($ZAP_SQL);
$ZAP_SQL="alter table temp_USERS2_INTERNET_STATUS_TV_NORMAL add index(S_ADDRESS_STREET)"; echo "
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 "
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 "
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 "
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 "
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 "
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 "
Wykonuje $ZAP_SQL \n";
ZAP_SQL($ZAP_SQL);
$ZAP_SQL="drop table if exists USERS2_MARKETING_OVERWIEV"; echo "
Wykonuje $ZAP_SQL \n";
ZAP_SQL($ZAP_SQL);
$ZAP_SQL="drop table if exists USERS2_MARKETING_OVERWIEV_REWIR"; echo "
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 "
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;
\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 "
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 "
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 "
Wykonuje $ZAP_SQL
\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 "
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 "
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 "
Wykonuje $ZAP_SQL \n";
// ZAP_SQL($ZAP_SQL);
}