<?php

/**
 * only ['RELATIVEDB']['SQL_QUERY']
 * 
 * @used in function USERS_COLUMN_INIT / USERS_COLUMN_INIT2
 */
function USERS_FIELDS_PROFILE_PRE_INIT() {
global $A_STATUS_L2_SQL ;  


$_SESSION['USERS2_OFFERS_COSTS_GROUPS_COLUMN']['RELATIVEDB']['SQL_QUERY'][1]="

--	update `USERS2_OFFERS_COSTS_GROUPS` ug set
--		`P_OFFER_COSTS`=(select sum(if(us.`COST_ACCOUNTED_ON`='MONTHLY',us.`COST_POWER`,us.`COST_POWER`/12))
--			from `USERS2_OFFERS_COSTS` as us
--			where us.`ID_COSTS_GROUPS`=ug.`ID` and us.`A_STATUS` in('WAITING','NORMAL')
--		) ; 

update `USERS2_OFFERS_COSTS_GROUPS` ug
		set
			`P_OFFER_COSTS`=(select sum(us.`COST_POWER`)
				from `USERS2_OFFERS_COSTS` as us
				where
					us.`ID_COSTS_GROUPS`=ug.`ID`
					and us.`A_STATUS` in('WAITING','NORMAL')
					and us.`COST_ACCOUNTED_ON`='MONTHLY'
			)
			, `P_OFFER_COSTS_START`=(select sum(us.`COST_POWER`)
				from `USERS2_OFFERS_COSTS` as us
				where
					us.`ID_COSTS_GROUPS`=ug.`ID`
					and us.`A_STATUS` in('WAITING','NORMAL')
					and us.`COST_ACCOUNTED_ON`='START'
			)
";

unset($_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY']);

//TODO madrzejsza inicjalizacja zapytania
$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" drop table if exists temp_USERS2_INTERNET_STATUS; ";

//@2012-09-24_inicjalizacja danych dla handlowcow by SQIX
//$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][2]=" create table temp_USERS2_INTERNET_STATUS select ID,S_ADDRESS_STREET,concat(A_STATUS,'.',A_STATUS_CURRENT,'.',P_SERVICE,' SPEED:',P_SERVICE_QUOTA,' DEALD:',P_DEALNUMBER,' VIP:',S_VIP_LEVEL) as USERS2_INTERNET_STATUS , P_DEALEXPIRE  from USERS2 ; ";
$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" 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) 

		
;


"; //eof 

$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" 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) 
		

";


$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" 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 , ' 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) 
		

";

$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" ALTER TABLE  temp_USERS2_INTERNET_STATUS CHANGE  USERS2_INTERNET_STATUS  USERS2_INTERNET_STATUS VARCHAR( 255 ) NOT NULL;";

$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" 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 ; ";
$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" 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 ;";
$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" alter table temp_USERS2_INTERNET_STATUS add index(S_ADDRESS_STREET) , add  index(USERS2_INTERNET_STATUS) ; ";
//@2012-04-24 aktualizacja ADM_COMPANY z BUILDINGS
$_SESSION['USERS2_MARKETING_COLUMN']['RELATIVEDB']['SQL_QUERY'][]=" UPDATE USERS2_MARKETING AS USERS2_MARKETING , BUILDINGS AS BUILDINGS SET USERS2_MARKETING.A_ADM_COMPANY=BUILDINGS.A_ADM_COMPANY WHERE USERS2_MARKETING.T_TELBOX_BUILDING_IN=BUILDINGS.S_ADDRESS_STREET;";

//TODO czy to dziala ?
$_SESSION['KSIEG_DOKUMENTY_COLUMN']['RELATIVEDB']['SQL_QUERY'][1]=" update KSIEG_DOKUMENTY set wartosc=ILOSC*CENA_JEDN_NETTO; ";

$_SESSION['IN7_MK_BAZA_DYSTRYBUCJI_COLUMN']['RELATIVEDB']['SQL_QUERY'][1]="
update IN7_MK_BAZA_DYSTRYBUCJI set T_WORKPOINTS_TYPE=if(M_DIST_INVOICE_IN_KSIEG!='0000-00-00','7) OK',
if(M_DIST_CLAIM_DATE!='0000-00-00','6) BRAK FAKTURY',

if(unix_timestamp(now())>(unix_timestamp(M_DIST_TERM_OF_CLAIM)-404800) and M_DIST_TERM_OF_CLAIM!='0000-00-00','1) PILNIE ODDAJ MATERIALY termin mniej niz 4 dni !!!',
if(unix_timestamp(now())>(unix_timestamp(M_DIST_TERM_OF_CLAIM)-604800) and M_DIST_TERM_OF_CLAIM!='0000-00-00','5) ODDAJ MATERIALY termin mniej niz 7 dni',
if(unix_timestamp(now())<=(unix_timestamp(M_DIST_TERM_OF_CLAIM)-604800) and M_DIST_TERM_OF_CLAIM!='0000-00-00' and M_DIST_DATE!='0000-00-00' ,'4) ODDAJ MATERIALY termin ponad 7 dni ',
if(unix_timestamp(now())>(unix_timestamp(M_DIST_DATE)-1204800) and M_DIST_ORDER_DATE='0000-00-00','2) PILNIE WYPISZ ZAMOWIENIE !!!',

if(M_DIST_ORDER_DATE='0000-00-00','3) WYPISZ ZAMOWIENIE','ERROR IF')
)
)
)
)
)
); ";

//$_SESSION['IN7_MK_BAZA_DYSTRYBUCJI_COLUMN']['RELATIVEDB'][SQL_QUERY][2]="update IN7_MK_BAZA_DYSTRYBUCJI as t1, KSIEG_DOKUMENTY as t2 set t1.M_DIST_DOCUMENTED_COST=sum(t2.wartosc) where t1.ID=t2.ID_PROJECT group by t1.ID";


$_SESSION['SES_USERS2_A_COLUMN']['RELATIVEDB']['SQL_QUERY'][]="update SES_USERS2_A as sxa, SERVICES as srv, USERS2_OFFERS as o
		set sxa.S_TECHNOLOGY=o.S_TECHNOLOGY
		where srv.ID=sxa.ID_SERVICES and o.ID=srv.ID_OFFERS
			and sxa.ID_SERVICES>0 and srv.ID_OFFERS>0 and o.ID is not null
			and o.S_TECHNOLOGY!='' and sxa.S_TECHNOLOGY!=o.S_TECHNOLOGY;
";
$_SESSION['SES_USERS2_A_COLUMN']['RELATIVEDB']['SQL_QUERY'][]="update SES_USERS2_A as sxa set sxa.S_TECHNOLOGY='ETHERNET' where sxa.S_TECHNOLOGY='';";
$_SESSION['SES_TV_A_COLUMN']['RELATIVEDB']['SQL_QUERY'][]="update SES_TV_A as sxa, SERVICES as srv, USERS2_OFFERS as o
		set sxa.S_TECHNOLOGY=o.S_TECHNOLOGY
		where srv.ID=sxa.ID_SERVICES and o.ID=srv.ID_OFFERS
			and sxa.ID_SERVICES>0 and srv.ID_OFFERS>0 and o.ID is not null
			and o.S_TECHNOLOGY!='' and sxa.S_TECHNOLOGY!=o.S_TECHNOLOGY;
";
$_SESSION['SES_TV_A_COLUMN']['RELATIVEDB']['SQL_QUERY'][]="update SES_TV_A as sxa set sxa.S_TECHNOLOGY='ETHERNET' where sxa.S_TECHNOLOGY='';";
$_SESSION['SES_VOIP_A_COLUMN']['RELATIVEDB']['SQL_QUERY'][]="update SES_VOIP_A as sxa, SERVICES as srv, USERS2_OFFERS as o
		set sxa.S_TECHNOLOGY=o.S_TECHNOLOGY
		where srv.ID=sxa.ID_SERVICES and o.ID=srv.ID_OFFERS
			and sxa.ID_SERVICES>0 and srv.ID_OFFERS>0 and o.ID is not null
			and o.S_TECHNOLOGY!='' and sxa.S_TECHNOLOGY!=o.S_TECHNOLOGY;
";
$_SESSION['SES_VOIP_A_COLUMN']['RELATIVEDB']['SQL_QUERY'][]="update SES_VOIP_A as sxa set sxa.S_TECHNOLOGY='ETHERNET' where sxa.S_TECHNOLOGY='';";


}