se/SE/superedit-SYNC_GIS_TABLES.php

<?php
//
// synchronizacja do serwera postgres GIS
//


die('@2016-08-08 bindera: wylaczam mozliwosc synchronizacji - tabele do przepisania, przyda sie historycznie ten kod aby spojrzec na relacje przestrzenne w praktyce');
include_once('se-lib/DB_Dump.php');
include_once('se-lib/UsersHelper.php');


function SYNC_GIS_TABLES() {
	global $db,$gdb;

	$mysql_zas_id=36;
	$gis_zas_id=13102;
	$loc_sync_table="_sync_to_gis_36_to_13102";
	$sql_cr="";
	
	$db = DB::getDB();
	$gdb= DB::getDB($gis_zas_id);
	
	
	
	
	
	DEBUG_S(-3,'db',$db);
		DEBUG_S(-3,'gis',$gdb);
		
	
	$tbls_to_sync[]='BUILDINGS';

	//$tbls_to_sync[]='MK_Budynki';

	$rem_field_timestamp='TIMESTAMP';
	
	
	
	//nadanie uprawnien do tabeli GIS
	//="dodanie wszystkich mozliwych zasobow jako role-stanowiska";
			function add_pg_roles() {
				global $db,$gdb;

				$groups=UsersHelper::get_group_list();
				DEBUG_S(-3,'Lista group ',$groups,__FILE__,__FUNCTION__,__LINE__);
				foreach($groups as $idg=>$val) {


					$sql="drop role if exists \"".$idg."\"";
					echo "<br> dodaje role: ".$sql;
					$gdb->query($sql);

					$sql="create role \"".$idg."\"";
					echo "<br> dodaje role: ".$sql;
					$gdb->query($sql);
					
				}
			}
			
			
			echo "<br> aktualziacja relacji obiektow do paszportyzacji optycznej";
			
			
		 function gis_opto_intersect($table_to_set,$field_index,$field_to_set,$table_from,$field_from,$filter_table_from='') {
			global $gdb;

			 $sql='drop table if exists gis_opto_relations;
			 	-- gis_opto_intersect
			 	select t1."'.$field_index.'" as "field_index", t2."'.$field_from.'" as "field_from"
			 	, 1
			 	as if_true , t2.*
			 	into temporary table gis_opto_relations 
			 	FROM "'.$table_to_set.'" as t1 
			 	INNER JOIN "'.$table_from.'"  as t2 ON ST_Intersects(t1.the_geom, t2.the_geom) '.$filter_table_from.' ;
			 	
			 	 CREATE  INDEX myfield ON gis_opto_relations ("field_from");
			 	
			 	update "'.$table_to_set.'"  t1
			 	set "'.$field_to_set.'"=t2.field_from 
			 	
			 	from  gis_opto_relations t2
			 	where   t1."'.$field_index.'"=t2.field_index and t2.if_true=1
			 	 '.$filter_sql.'
			 	;
			 	
			    drop table gis_opto_relations;';
			 DEBUG_S(-3,"Zapytanie sql aktualizujace ".$table_to_set,$sql,__FILE__,__FUNCTION__,__LINE__);
			 $gdb->query($sql);

		 }
			
		 function gis_opto_relations($table_to_set,$field_index,$field_to_set,$table_from,$field_from,$ST_Relate='******F**',$filter_sql='',$filter_table_from=' on 1=1 ') {
			 global $gdb;
			 $sql='
			 	-- gis_opto_relations
			 	drop table if exists gis_opto_relations;

			 	select t1."'.$field_index.'" as "field_index", t2."'.$field_from.'" as "field_from"
			 	
			 	, case ST_Relate(t1."the_geom",t2."the_geom",\''.$ST_Relate.'\') when true then
			 	1
			 	else 0 end
			 	as if_true , t2.*
			 	into temporary table gis_opto_relations 
			 	FROM "'.$table_to_set.'" as t1 
			 	LEFT JOIN "'.$table_from.'"  as t2 '.$filter_table_from.' ;
			 	
			 	 CREATE  INDEX myfield ON gis_opto_relations (field_from);
			 	
			 	update "'.$table_to_set.'"  t1
			 	set "'.$field_to_set.'"=t2.field_from 
			 	
			 	from  gis_opto_relations t2
			 	where   t1."'.$field_index.'"=t2.field_index and t2.if_true=1
			 	 '.$filter_sql.'
			 	;
			 	
			    drop table gis_opto_relations;
			 	
			 --	select t1.'.$field_index.' as "field_index", t2."'.$field_from.'" as "field_from" ,ST_Relate(t1."the_geom",t2."the_geom",\''.$ST_Relate.'\') as if_true	into table gis_opto_relations_ FROM "'.$table_to_set.'" as t1  CROSS JOIN "'.$table_from.'"  as t2;

			 	
			 	';
			 DEBUG_S(-3,"Zapytanie sql aktualizujace",$sql,__FILE__,__FUNCTION__,__LINE__);
			 $gdb->query($sql);
		 }
		 
		 
		 
		 function gis_opto_calculations($table_to_set,$field_index,$field_to_set,$table_from,$field_from_geom,$ST_Relate='******F**',$filter_sql='',$price_column='',$lenght_column='') {
			 			 global $gdb;
			 			 $sql_select='';$sql_price='';
			 			 if(is_array($field_index)) {
				 			 $field_index_t1=$field_index[0];
				 			 $field_index_t2=$field_index[1];
			 			 } else {
				 			 $field_index_t1=$field_index;
				 			 $field_index_t2=$field_index;
			 			 }
			 			 
			 			 if(strlen($price_column)>0) { $sql_select=', t2."'.$price_column.'"';
				 			 						   $sql_price='*t2."'.$price_column.'"';
			 			 }
			 			// if(!empty($filter_sql)) {
				 		//	 $ST_Relate=" 1=1 ";
				 		//	 $ST_Intersects=" 1=1 ";
			 			// } else {
				 			 $ST_Relate='ST_Relate(t1."'.$field_from_geom.'",t2."'.$field_from_geom.'",\''.$ST_Relate.'\')';
				 			 $ST_Intersects='ST_Intersects(t1."'.$field_from_geom.'",t2."'.$field_from_geom.'")';
			 			// }



			 $sql='
			 -- gis_opto_calculations
			 drop table if exists "'.$table_from.'_AGR" ;
			 select t1."'.$field_index_t1.'" as field_index '.$sql_select.'
				, '.$ST_Intersects.'
				, case '.$ST_Relate.' '.$filter_sql.' when true then
				 ST_Length_Spheroid(t2."'.$field_from_geom.'",\'SPHEROID["WGS 84",6378137,298.257223563]\')
				 else \'0\' end
				as length
				 into  temporary table "'.$table_from.'_AGR" 
				 FROM "'.$table_to_set.'" as t1 
				CROSS JOIN "'.$table_from.'"  as t2
				;
				CREATE INDEX ON "'.$table_from.'_AGR" ("field_index");

				update  "'.$table_to_set.'"  t1
				set "'.$field_to_set.'"=(
				select case   when round(sum(t2.length'.$sql_price.')) is null then
				 0 else 
				 round(sum(t2.length'.$sql_price.')) 
				 end
				 from "'.$table_from.'_AGR" as t2 where t2."field_index"=t1."'.$field_index_t1.'"
				)
				
				;';
				
				if(!empty($lenght_column)) { //wylicamy dlugosc
					
					
					
				}
				

				
				DEBUG_S(-3,'Zapyt gis_opt '.$table_to_set,$sql,__FILE__,__FUNCTION__,__LINE__);
				flush();
				$gdb->query($sql);
		 }
		 
		 function gis_opto_calculations_count($table_to_set,$field_index,$field_to_set,$table_from,$field_from_geom,$ST_Relate='******F**',$filter_sql='',$price_column='') {
			 			 global $gdb;
			 			 $sql_select='';$sql_price='';
			 			 if(strlen($price_column)>0) { $sql_select=', t2."'.$price_column.'"';
				 			 						   $sql_price='*t2."'.$price_column.'"';
			 			 }
			 			 if(is_array($field_index)) {
				 			 $field_index_t1=$field_index[0];
				 			 $field_index_t2=$field_index[1];
			 			 } else {
				 			 $field_index_t1=$field_index;
				 			 $field_index_t2=$field_index;
			 			 }

			 $sql='
			 -- gis_opto_calculations_count
			 drop table if exists "'.$table_from.'_AGR" ;
			 select t1."'.$field_index_t1.'" as field_index '.$sql_select.'
				, ST_Intersects(t1."'.$field_from_geom.'",t2."'.$field_from_geom.'")
				, case ST_Relate(t1."'.$field_from_geom.'",t2."'.$field_from_geom.'",\''.$ST_Relate.'\') '.$filter_sql.' when true then
				 1
				 else 0 end
				as length
				 into  temporary table "'.$table_from.'_AGR" 
				 FROM "'.$table_to_set.'" as t1 
				CROSS JOIN "'.$table_from.'"  as t2
				;
				
				CREATE INDEX ON "'.$table_from.'_AGR" ("field_index");

				update  "'.$table_to_set.'"  t1
				set "'.$field_to_set.'"=(
				select 
				 sum(t2.length'.$sql_price.')
				 from "'.$table_from.'_AGR" as t2 where t2."field_index"=t1."'.$field_index_t1.'"
				)
				
				;';
				
				DEBUG_S(-3,'sql',array($sql,debug_backtrace()),__FILE__,__FUNCTION__,__LINE__);
				
				$gdb->query($sql);
		 }
		 
		 
		 	 function gis_opto_calculations_sum($table_to_set,$field_index,$field_to_set,$table_from,$field_from_geom,$ST_Relate='******F**',$filter_sql='',$field_to_sum) {
			 			 global $gdb;
			 			 if(is_array($field_index)) {
				 			 $field_index_t1=$field_index[0];
				 			 $field_index_t2=$field_index[1];
			 			 } else {
				 			 $field_index_t1=$field_index;
				 			 $field_index_t2=$field_index;
			 			 }

			 $sql='
			 -- gis_opto_calculations_sum
			 drop table if exists "'.$table_from.'_AGR" ;
			 select t1."'.$field_index_t1.'" as field_index
				, ST_Intersects(t1."'.$field_from_geom.'",t2."'.$field_from_geom.'")
				, case ST_Relate(t1."'.$field_from_geom.'",t2."'.$field_from_geom.'",\''.$ST_Relate.'\') '.$filter_sql.' when true then
				 t2."'.$field_to_sum.'"
				 else 0 end
				as length
				 into  temporary table "'.$table_from.'_AGR" 
				 FROM "'.$table_to_set.'" as t1 
				CROSS JOIN "'.$table_from.'"  as t2
				;
				
				CREATE INDEX ON "'.$table_from.'_AGR" ("field_index");

				
				update  "'.$table_to_set.'"  t1
				set "'.$field_to_set.'"=(
				select 
				 sum(t2.length)
				 from "'.$table_from.'_AGR" as t2 where t2."field_index"=t1."'.$field_index_t1.'"
				)
				
				;';
				
				DEBUG_S(-3,'sql calc_sum '.$table_to_set,$sql,__FILE__,__FUNCTION__,__LINE__);
				$gdb->query($sql);
		 }
		 
		 
		 		function gis_opto_calculations_set_lenght($table_to_set,$field_to_set) {
			 		global $gdb;
			 		$sql='
			 		-- gis_opto_calculations_set_lenght
			 		update "'.$table_to_set.'" set "'.$field_to_set.'"=round(ST_Length_Spheroid("the_geom",\'SPHEROID["WGS 84",6378137,298.257223563]\')) ';
			 		DEBUG_S(-3,'sql set_lenght '.$table_to_set,$sql,__FILE__,__FUNCTION__,__LINE__);
			 		$gdb->query($sql);
		 		}
		 
		 		function USERS2_MARKETING_OVERWIEV_to_MK_Budynki(){
			 			global $db,$gdb;
			 			  $sql='select t1.* ,  t2.A_STATUS as BUILDING_STATUS , t2.geoportal_gml_punkty_adr_gml_id  from USERS2_MARKETING_OVERWIEV  t1 inner join
			 			  	    BUILDINGS as t2 on t2.S_ADDRESS_STREET=t1.T_TELBOX_BUILDING_IN where t2.geoportal_gml_punkty_adr_gml_id!=\'\' or S_ADDRESS_STREET!=\'\'';
			 			  $res=$db->query($sql);
			 			 
			 			 echo "<br> To update USERS2_MARKETING_OVERWIEV use ?FUNCTION_INIT=STATYSTYKA_TABELE ".$sql;
			 			 
			 			 $row=0;
			 			 
			 			  while($h=$db->fetch($res)) {
				 			  if($row==0) { //add columns
					 			  foreach($h as $col=>$val) {
						 			 $sql="SELECT column_name FROM information_schema.columns WHERE table_name='MK_Budynki' and column_name='".$col."'";
						 			 $res2=$gdb->query($sql);
						 			 if($gdb->count($res2)==0) {
						 			 
						 			  $sql='alter table "MK_Budynki" add "'.$col.'" char(255) not null default \'\' '; echo "<br> ".$sql; flush();
						 			  $gdb->query($sql);
						 			  } else echo " Field ".$col." exists. ";
					 			  }
				 			  }
				 			  $set=array();
				 			  foreach($h as $col=>$val) {
					 			  $set[]="\"".$col."\"='".$val."'";
				 			  }
				 			  
				 			  $sql='update "MK_Budynki" set '.implode(',', $set).' where ( geoportal_gml_punkty_adr_gml_id=\''.$h->geoportal_gml_punkty_adr_gml_id.'\' and geoportal_gml_punkty_adr_gml_id!=\'\' ) or ( "T_TELBOX_BUILDING_IN"=\''.$h->T_TELBOX_BUILDING_IN.'\' and (  geoportal_gml_punkty_adr_gml_id=\'\' or geoportal_gml_punkty_adr_gml_id is null ) )';
				 			  echo "<br> ".$sql;
				 			  $gdb->query($sql);

				 			  $row++;
				 			  
			 			  }

			 		
		 		}
		 
		 
		 		 	function PROCESY5_to_GIS($p5_table,$p5_key,$gis_table,$gis_key,$p5_what_extra='',$p5_join='',$p5_limit=''){
			 			global $db,$gdb;
			 			
			 			//! pierw trigger
			 			$trigger_psql_do_timestamp="CREATE OR REPLACE FUNCTION update_A_RECORD_UPDATE_DATE_column()
								RETURNS TRIGGER AS $$
								BEGIN
								NEW.\"A_RECORD_UPDATE_DATE\" = now(); 
								RETURN NEW;
								END;
								$$ language 'plpgsql';";
								$gdb->query($sql);

			 			  $sql='select t1.* '.$p5_what.' from '.$p5_table.' as t1 
			 			  where t1.'.$p5_key.'!=\'\' or t1.'.$p5_key.' is not null ';
			 			  $res=$db->query($sql);
			 			 
			 			 echo "<br> f.PROCESY5_to_GIS use  ".$sql;
			 			 
			 			 $row=0;
			 			 
			 			  while($h=$db->fetch($res)) {
				 			  if($row==0) { //add columns
					 			  foreach($h as $col=>$val) {
						 			 $sql="SELECT column_name FROM information_schema.columns WHERE table_name='".$gis_table."' and column_name='".$col."'";
						 			 $res2=$gdb->query($sql);
						 			 if($gdb->count($res2)==0) {
						 			 
						 			  $sql='alter table "'.$gis_table.'" add "'.$col.'" char(255) not null default \'\' '; echo "<br> ".$sql; flush();
						 			  $gdb->query($sql);
						 			  
						 			  if($col=='A_RECORD_UPDATE_DATE') {
							 			  $gdb->query("CREATE DEFINER=`root`@`localhost` TRIGGER update_A_RECORD_UPDATE_DATE BEFORE UPDATE
							 			 			 ON \"".$gis_table."\" FOR EACH ROW EXECUTE PROCEDURE 
							 			 			 update_A_RECORD_UPDATE_DATE_column();");
						 			  }
						 			  
						 			  
						 			  } else echo " Field ".$col." exists. ";
					 			  }
				 			  }
				 			  $set=array();
				 			  $insert=array();
				 			  foreach($h as $col=>$val) {
					 			  if($col=='the_geom') continue; //todo GEOM tez kopiowac trzeba ale inaczej
					 			  if($col==$gis_key) continue;
					 			  $set[]="\"".$col."\"='".$gdb->_($val)."'";
					 			  $insert['row'][]='"'.$col.'"';
					 			  $insert['vals'][]="'".$gdb->_($val)."'";
				 			  }
				 			  
				 			  $sql='update "'.$gis_table.'" set '.implode(',', $set).' where ( "'.$gis_key.'"=\''.$h->$p5_key.'\' and "'.$gis_key.'"!=\'\'  )
				 			  ';
				 			 //			 			   --  and "A_RECORD_UPDATE_DATE"<\''.$h->A_RECORD_UPDATE_DATE.'\''; //Jak poprawi Piotr aktualizacje GIS to bedzie OK. 
				 			//  echo "<br> ".$sql;

				 			  $gdb->query($sql);

				 			  if(!empty($h->the_geom)) { //new record dodajemy jak jest oznaczona geometria
				 			  	$sql='insert into "'.$his_table.'" ( '.implode(',', $insert['row']).' values ( '.implode(',',$insert['vals']).' )';
					 			// echo "<br> ".$sql;	  
				 			  }

				 			  $row++;
				 			  
			 			  }


			 			  { //! teraz aktualizujemy po timestampie w prawo
				 			  
	  
			 			  }
			 			  
			 		
		 		}
		 
		 function POSTGIS_to_MYSQL_Tables($local_table_mysql,$local_key,$remote_table_postgis,$remote_key){
			 	global $db,$gdb;
			 		$sql='select "'.$remote_key.'", ST_AsText(the_geom) as the_geom from "'.$remote_table_postgis.'" where "'.$remote_key.'"!=\'\' and "'.$remote_key.'" is not null and the_geom!=\'\' and the_geom is not null ';
			 		$res=$gdb->query($sql);
			 		while($h=$gdb->fetch($res)) {
				 		$sql2="update `".$local_table_mysql."` set `the_geom`=GeomFromText('".$h->the_geom."') where `".$local_key."`='".trim($h->$remote_key)."' and `".$local_key."`!='' and `".$local_key."` is not null
				 		  and ( the_geom='' or the_geom is null )";
				 		echo "<br> ".$sql2;
				 		$db->query($sql2) or die('Problem with sql 228: '.$sql);
			 		}
  
		 }

		 
		 		
		
		
		
		 gis_opto_calculations_set_lenght('Rozdzielcza_rurociag_wsg84','dlugosc');
		 gis_opto_calculations_set_lenght('Rozdzielcza_Kabel_Swiatlowodowy_wsg84','Dlugosc');


		
		 gis_opto_intersect('geoportal_gml_punkty_adr','id_0','Mk_Rewir_set','MK_Rewiry','gid');
		 gis_opto_intersect('Rozdzielcza_rurociag_wsg84','gid','Mk_Rewir_set','MK_Rewiry','gid');


		 gis_opto_intersect('MK_Budynki','gid','geoportal_gml_punkty_adr_gml_id','geoportal_gml_punkty_adr','gml_id',' and t2."Mk_Rewir_set">0 ');

//	gis_opto_intersect('Rozdzielcza_Wykop_przedmiar_na_mikrorurki','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
		 gis_opto_intersect('Rozdzielcza_Wykop_przedmiar_na_mikrorurki','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');

//	gis_opto_intersect('Rozdzielcza_Mikrokanalizacja_do_klienta','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
		 gis_opto_intersect('Rozdzielcza_Mikrokanalizacja_do_klienta','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
		
//	 gis_opto_intersect('Rozdzielcza_Pakiet5MikrorurekMagistrala_WSG84','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	   gis_opto_intersect('Rozdzielcza_Pakiet5MikrorurekMagistrala_WSG84','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
//	 gis_opto_intersect('Rozdzielcza_Przeciski_110mm','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	   gis_opto_intersect('Rozdzielcza_Przeciski_110mm','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');

//	gis_opto_intersect('Rozdzielcza_Zabruki','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	   gis_opto_intersect('Rozdzielcza_Zabruki','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');

//	gis_opto_intersect('Rozdzielcza_koszty_dodatkowe_wsg84','id_0','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	   gis_opto_intersect('Rozdzielcza_koszty_dodatkowe_wsg84','id_0','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');

//	gis_opto_intersect('Rozdzielcza_rurociag_wsg84','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	   gis_opto_intersect('Rozdzielcza_rurociag_wsg84','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID','  and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');

//	gis_opto_intersect('Rozdzielcza_wezly','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	   gis_opto_intersect('TELBOXES','ID','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');

//	gis_opto_intersect('Rozdzielcza_Kabel_Swiatlowodowy_wsg84','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	  gis_opto_intersect('Rozdzielcza_Kabel_Swiatlowodowy_wsg84','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');

//	gis_opto_intersect('MK_Budynki','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	  gis_opto_intersect('MK_Budynki','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
//	gis_opto_intersect('MK_Mieszkania','gid','ID_PROJECT','Inwestycje_Planowane','ID_PROJECT',' and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');
	  gis_opto_intersect('MK_Mieszkania','gid','ID_PROJECT','IN7_MK_BAZA_DYSTRYBUCJI','ID',' and t2."ID"!=0 and (  t1."ID_PROJECT"=\'0\' or t1."ID_PROJECT"=\'\' or t1."ID_PROJECT" is  null ) ');

//		 gis_opto_intersect('MK_Budynki','gid','geoportal_gml_punkty_adr_gml_id','geoportal_gml_punkty_adr','gml_id','');
	
		 //gis_opto_relations('MK_Budynki','gid','geoportal_gml_punkty_adr_gml_id','geoportal_gml_punkty_adr','gml_id','T********',''," on t2.\"Mk_Rewir_set\" > 0 ");
		

		// die('celowe 228');


		DEBUG_S(-3,'Agr rurociag',null,__FILE__,__FUNCTION__,__LINE__);
//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_Rozdzielcza_rurociag_wsg84','Rozdzielcza_rurociag_wsg84','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
		gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI','ID','Agr_Rozdzielcza_rurociag_wsg84_ilosc','Rozdzielcza_rurociag_wsg84','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0 ');
//	die('431');

//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_Rozdzielcza_rurociag_wsg84_koszt','Rozdzielcza_rurociag_wsg84','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ','cena_jedn');
   		gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI','ID','Agr_Rozdzielcza_rurociag_wsg84_koszt','Rozdzielcza_rurociag_wsg84','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0 ','cena_jedn');

//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84','Rozdzielcza_Kabel_Swiatlowodowy_wsg84','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
	    gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_ilosc','Rozdzielcza_Kabel_Swiatlowodowy_wsg84','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0');

//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_koszt','Rozdzielcza_Kabel_Swiatlowodowy_wsg84','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ','cena_jedn');
		gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_koszt','Rozdzielcza_Kabel_Swiatlowodowy_wsg84','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0','cena_jedn');
	
//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_metrow_mikrorurek_5szt','Rozdzielcza_Pakiet5MikrorurekMagistrala_WSG84','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
	    gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_metrow_mikrorurek_5szt','Rozdzielcza_Pakiet5MikrorurekMagistrala_WSG84','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0');
DEBUG_S(-3,'CheckPoint',null,__FILE__,__FUNCTION__,__LINE__);

//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_metrow_mikrorurek','Rozdzielcza_Mikrokanalizacja_do_klienta','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
	    gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_Mikrokanalizacja_do_klienta_ilosc','Rozdzielcza_Mikrokanalizacja_do_klienta','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0');

//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_ilosc_wykopu','Rozdzielcza_Wykop_przedmiar_na_mikrorurki','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
		gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_Wykop_przedmiar_na_mikrorurki_ilosc','Rozdzielcza_Wykop_przedmiar_na_mikrorurki','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0');
DEBUG_S(-3,'CheckPoint Wykop prev',null,__FILE__,__FUNCTION__,__LINE__);
	
//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_ilosc_przeciskow','Rozdzielcza_Przeciski_110mm','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
		gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_Przeciski_110mm_ilosc','Rozdzielcza_Przeciski_110mm','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0');

//	gis_opto_calculations('Inwestycje_Planowane','gid','Agr_ilosc_zabrukow','Rozdzielcza_Zabruki','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
		gis_opto_calculations('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_Zabruki_ilosc','Rozdzielcza_Zabruki','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0');
DEBUG_S(-3,'CheckPoint',null,__FILE__,__FUNCTION__,__LINE__);
//	gis_opto_calculations_count('Inwestycje_Planowane','gid','Agr_ilosc_domow','MK_Mieszkania','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
		gis_opto_calculations_count('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_USERS2_MARKETING_ilosc','MK_Mieszkania','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0');

//	gis_opto_calculations_count('Inwestycje_Planowane','gid','Agr_ilosc_wezlow','Rozdzielcza_wezly','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ');
		gis_opto_calculations_count('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_wezly_ilosc','TELBOXES','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0');

//	gis_opto_calculations_count('Inwestycje_Planowane','gid','Agr_ilosc_wezlow_koszt','Rozdzielcza_wezly','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT" ','cena_jedn');
		gis_opto_calculations_count('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_wezly_koszt','TELBOXES','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0','cena_jedn');
	
//	gis_opto_calculations_sum('Inwestycje_Planowane','gid','Agr_Rozdzielcza_koszty_dodatkowe_wsg84','Rozdzielcza_koszty_dodatkowe_wsg84','the_geom','******F**','and t1."ID_PROJECT"=t2."ID_PROJECT"','Wartosc_kosztu');
		gis_opto_calculations_sum('IN7_MK_BAZA_DYSTRYBUCJI',array('ID','gid'),'Agr_Rozdzielcza_koszty_dodatkowe_wsg84','Rozdzielcza_koszty_dodatkowe_wsg84','the_geom','******F**','and t1."ID"::int=t2."ID_PROJECT"::int and t1."ID"!=0','Wartosc_kosztu');	
		 
		// PROCESY5_to_GIS('TELBOXES','ID','Rozdzielcza_lokalizacje','ID_TELBOXES');		
		// PROCESY5_to_GIS('IN7_MK_BAZA_DYSTRYBUCJI','ID','Inwestycje_Planowane','ID_PROJECT');		
	
		// POSTGIS_to_MYSQL_Tables('BUILDINGS','geoportal_gml_punkty_adr_gml_id','MK_Budynki','geoportal_gml_punkty_adr_gml_id');
		// POSTGIS_to_MYSQL_Tables('IN7_MK_BAZA_DYSTRYBUCJI','ID','Inwestycje_Planowane','ID_PROJECT');

		// USERS2_MARKETING_OVERWIEV_to_MK_Budynki();
	
		

//	  $sql_cena='update "Inwestycje_Planowane" set	"Agr_metrow_mikrorurek_5szt"=0 where "Agr_metrow_mikrorurek_5szt" is null;'; 
//	  $gdb->query($sql_cena);

	    $sql_cena='update "IN7_MK_BAZA_DYSTRYBUCJI" set	"Agr_metrow_mikrorurek_5szt"=0 where "Agr_metrow_mikrorurek_5szt" is null;'; 
		$gdb->query($sql_cena);

//	  $sql_cena='update "Inwestycje_Planowane" set "Agr_ilosc_zabrukow"=0 where "Agr_ilosc_zabrukow" is null;';
//	  $gdb->query($sql_cena);

		$sql_cena='update "IN7_MK_BAZA_DYSTRYBUCJI" set "Agr_Rozdzielcza_Zabruki_ilosc"=0 where "Agr_Rozdzielcza_Zabruki_ilosc" is null;';
		$gdb->query($sql_cena);


/*		$sql_cena='update "Inwestycje_Planowane" set 
					koszt_wspolny="Agr_ilosc_wykopu"*"Agr_ilosc_wykopu_cena"
					+"Agr_metrow_mikrorurek"*"Agr_metrow_mikrorurek_cena"
					+"Agr_metrow_mikrorurek_5szt"*"Agr_metrow_mikrorurek_5szt_cena"
					+"Agr_ilosc_przeciskow"*"Agr_ilosc_przeciskow_cena"
					+"Agr_ilosc_zabrukow"*"Agr_ilosc_zabrukow_cena"
					+"Agr_ilosc_wezlow_koszt"
					+"Agr_Rozdzielcza_koszty_dodatkowe_wsg84"
					+"Agr_Rozdzielcza_rurociag_wsg84_koszt"
					+"Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_koszt"
					+"Agr_ilosc_domow"*"Agr_MK_Mieszkania_cena_jedn"
					;'; //"Agr_metrow_w_tpsa*3 +Agr_metrow_kabel*
					////+ studnia1300 z pioh+ mufa 300 + spliter 200 = 1800 PLN za wezel 
		 $gdb->query($sql_cena);
*/
		 $sql_cena='update "IN7_MK_BAZA_DYSTRYBUCJI" set 
					koszt_wspolny="Agr_Rozdzielcza_Wykop_przedmiar_na_mikrorurki_ilosc"*"Agr_Rozdzielcza_Wykop_przedmiar_na_mikrorurki_cena"
					+"Agr_Rozdzielcza_Mikrokanalizacja_do_klienta_ilosc"*"Agr_Rozdzielcza_Mikrokanalizacja_do_klienta_cena"
					+"Agr_metrow_mikrorurek_5szt"*"Agr_metrow_mikrorurek_5szt_cena"
					+"Agr_Rozdzielcza_Przeciski_110mm_ilosc"*"Agr_Rozdzielcza_Przeciski_110mm_cena"
					+"Agr_Rozdzielcza_Zabruki_ilosc"*"Agr_Rozdzielcza_Zabruki_cena"
					+"Agr_Rozdzielcza_wezly_koszt"
					+"Agr_Rozdzielcza_koszty_dodatkowe_wsg84"
					+"Agr_Rozdzielcza_rurociag_wsg84_koszt"
					+"Agr_Rozdzielcza_Kabel_Swiatlowodowy_wsg84_koszt"
					+"Agr_USERS2_MARKETING_ilosc"*"Agr_USERS2_MARKETING_cena"
					;'; //"Agr_metrow_w_tpsa*3 +Agr_metrow_kabel*
					////+ studnia1300 z pioh+ mufa 300 + spliter 200 = 1800 PLN za wezel 
		  $gdb->query($sql_cena);

		 
//		 $sql_cena='update "Inwestycje_Planowane" set "Agr_MK_Mieszkania_koszt"=round("Agr_MK_Mieszkania_cena_jedn"*"Agr_ilosc_domow") where "Agr_ilosc_domow">0 '  ;
//		 $gdb->query($sql_cena);
//		 $sql_cena='update "Inwestycje_Planowane" set "koszt_na_dom"=round("koszt_wspolny"/"Agr_ilosc_domow") where "Agr_ilosc_domow">0 '  ;
//		 $gdb->query($sql_cena);


		 $sql_cena='update "IN7_MK_BAZA_DYSTRYBUCJI" set "Agr_USERS2_MARKETING_koszt"=round("Agr_USERS2_MARKETING_cena"*"Agr_USERS2_MARKETING_ilosc") where "Agr_USERS2_MARKETING_ilosc">0 '  ;
		 $gdb->query($sql_cena);
		 $sql_cena='update "IN7_MK_BAZA_DYSTRYBUCJI" set "koszt_na_mieszkanie"=round("koszt_wspolny"/"Agr_USERS2_MARKETING_ilosc") where "Agr_USERS2_MARKETING_ilosc">0 '  ;
		 $gdb->query($sql_cena);

		 ///  	

			 die("celowe zabicie zapytanie: ");
	 
		 
		 gis_opto_relations('opt_tacki','id_0','id_przelac','opt_przelacznice_mufy','id_0','T********');
		 gis_opto_relations('opt_spawy','id_0','id_tacka','opt_tacki','id_0','T********');
		 $gdb->query('update opt_wlokna set id_tuba=null '); 
		 gis_opto_relations('opt_wlokna','id','id_tuba','opt_tuby','id_0','T********');
		 gis_opto_relations('opt_tuby','id_0','id_kabel','opt_kable','id_0','T********');
		 $gdb->query('update opt_wlokna set id_tacka_a=null, id_tacka_b=null; '); 
		 
		 gis_opto_relations('opt_wlokna','id','id_tacka_a','opt_tacki','id_0','T********','and t1.id_tacka_a is null ');
		 gis_opto_relations('opt_wlokna','id','id_tacka_b','opt_tacki','id_0','T********','and t1.id_tacka_a!=t2.field_from and t1.id_tacka_b is null');
		 $gdb->query('update opt_przelacznice_mufy set id_opt_lokalizacja=null; '); 	 
		 
   	     gis_opto_relations('opt_przelacznice_mufy','id_0','id_opt_lokalizacja','opt_lokalizacje','id_0','T********');
	

		 $gdb->query('update opt_wlokna set id_spaw_a=null, id_spaw_b=null; '); 	 
		 gis_opto_relations('opt_wlokna','id','id_spaw_a','opt_spawy','id_0','FF10F0FF2','and t1.id_spaw_a is null');
		 gis_opto_relations('opt_wlokna','id','id_spaw_b','opt_spawy','id_0','FF10F0FF2','and t1.id_spaw_a!=t2.field_from and t1.id_spaw_b is null');


		
//	add_pg_roles();
	//dodanie wszystkich uzytkownikow z haslem
//	public static function &get_users_list( $params = array(), $limit = 10, $limit_start = 0, $order_by = '', $order_dir = '' ) {


	//$tbl_perms=UserAcl::getTableAcl();

	include_once('se-lib/UserAcl.php');

	$Users=UsersHelper::get_users_list();
	DEBUG_S(-3,'UsersHelper::get_users_list()',$Users,__FILE__,__FUNCTION__,__LINE__);	
	
	$userAcl = UsersHelper::getUserAcl($Users[9]->ID);
//	$userAcl->fetchGroups();
	
	$userAcl->fetchAllPerms();
	//$getTablesAcl=$userAcl->getTablesAcl();
	getAcl();
	DEBUG_S(-3,'UserAcl::getTablesAcl',$getTablesAcl,__FILE__,__FUNCTION__,__LINE__);	
	
	if ($userAcl->hasTableAcl($zasobObj->ID)) {
		$tblAcl = $usrAcl->getTableAcl($tableZasobID);
		$tblAcl->init();
	}
	DEBUG_S(-3,'UsersHelper::getUserAcl',$userAcl,__FILE__,__FUNCTION__,__LINE__);

	//odlaczenie wszystkich rol od uzytkownikow
	
	
	
	
	
	
	
	die('zabilem skrypt');
	//$sql="select ID from CRM_LISTA_ZASOBOW where `TYPE`='STANOWISKO' and "
	
	
	
		
	
	
	foreach($tbls_to_sync as $tbl) {
		$res=$gdb->describe_table($tbl);
		
		
		$ile=$gdb->count($res);
			$struct=$db->STRUCTURE_GENERATE(array($tbl),null,null);
			DEBUG_S(-3,'Structure local',$struct,__FILE__,__FUNCTION__,__LINE__);

		
		if(!$ile) {
			
			$gdb->STRUCTURE_GENERATE_PARSE($struct);
			$gdb->query('alter table "'.$tbl.'" add  "gid" serial primary key ;');	 //todo before "ID" 
			$gdb->query("CREATE OR REPLACE FUNCTION update_A_RECORD_UPDATE_DATE_column()
						RETURNS TRIGGER AS $$
						BEGIN
						NEW.\"A_RECORD_UPDATE_DATE\" = now(); 
						RETURN NEW;
						END;
						$$ language 'plpgsql';");
						
						
						 
			$gdb->query("CREATE DEFINER=`root`@`localhost` TRIGGER update_A_RECORD_UPDATE_DATE BEFORE UPDATE
						ON \"".$tbl."\" FOR EACH ROW EXECUTE PROCEDURE 
						update_A_RECORD_UPDATE_DATE_column();");
			//$gdb->query('alter table "'.$tbl.'" add  "the_geom" serial primary key ;');	 //todo before "ID" 
		} else { 
		$gdb->STRUCTURE_GENERATE_PARSE($struct);
		echo "<br> Znalazlem cos, wiec !ile nie zadzialalo!(".$ile.")"; 


		}
		



//1) try to put newer data

	$sql="select t1.* from ".$tbl." as t1 left join _SYNC_to_gis_36_to_13102 as t2 on t1.ID=t2.LID and t2.LTABLE='".$tbl."'
		  where  t2.LID is NULL; 	"; //unix_timestamp(t1.A_RECORD_UPDATE_DATE)>unix_timestamp(t2.LTS) or

	echo "<br>".$sql;
	
	
		

	$lres=$db->query($sql);
	while($h=$db->fetch($lres)) {
	$data=array();
			foreach($h as $ind=>$val) {
			
						//if($ind=='the_geom') 	
						  //$data[$ind]="'01030000000100000005000000A681BD96FAAB32402B9771AC13334B40A430199F0DAC3240E91B087D12334B4092AA81BA0DAC3240A8DFAECE15334B40004534E3FAAB32401568C3D418334B40A681BD96FAAB32402B9771AC13334B40'";
						//else
						 if(empty($val))
							$data[$ind]="null";
						else 
						 $data[$ind]="'".$gdb->_($val)."'";
			//		
			}
     DEBUG_S(-3,'zminne H to ',$data);
//$array=array('lastname', 'email', 'phone');
//$comma_separated = implode(",", $array);

	//uczylem dzieciaki
	$keys=array_keys($data);
	$comma_separated = '"'.implode('","', $keys).'"';
	DEBUG_S(-3,'comma_separated',$comma_separated);
	$values_comma_separated=implode(",",$data);

	$sql="insert into \"".$tbl."\" ( ".$comma_separated." ) values ( ".$values_comma_separated." ) ";

	DEBUG_S(-3,'nasze zapytanie SQL',$sql);
	$gdb->query($sql) or die('blad 104');
	$sql="insert into _SYNC_to_gis_36_to_13102 ( LTABLE, LID, RID, RIDCOLUMN , RTS, LTS  ) 
	                                  values ( '".$tbl."',".$data['ID'].",'".$gdb->insert_id()."','gid',now(),now()) ";
		 DEBUG_S(-3,'sql to synctable',array($sql,$gdb->insert_id()),__FILE__,__FUNCTION__,__LINE__);

	$db->query($sql) or die('blad 107');							






	//die('123');








//		
//		DEBUG_S(-3,'rec data',$data);
		//	$sql="insert into \"".$tbl."\" ( \"".implode('","', array_keys($data))."\") values (".implode(',', $data).") ";
		//	
		//	die($sql);
		
	}

	/*	$sql_rem="create table if not exists ".$tbl." (GIS_TIMESTAMP timestamp)  ;";
		$gdb->query($sql_rem);
		
		$sql_rem="select max(GIS_TIMESTAMP) as max from ".$tbl.""; 
		$res=$gdb->query($sql_rem);
		$last_rem=$gdb->fetch_assoc($res);
		if(empty($last_rem['max'])) $last_rem['max']=0;
		DEBUG_S(-3,'Last ts z rem',$last_rem['max']);
		$sql_loc="select * from ".$tbl." where unix_timestamp(A_RECORD_UPDATE_DATE)>".$last_rem['max']." ";
		echo $sql_loc;
		$res=$db->query($sql_loc);
		$i=0;
		while($h=$db->fetch($res)) {
			
			print_r($h);
		}
		*/
	//	$structure_local=DB_Dump::get_db_structure_by_table_prefix($db,$tbl);
	//	$structure_remote=DB_Dump::get_db_structure_by_table_prefix($gdb,$tbl);
	//	DEBUG_S(-3,'structure_local',$structure_local,__FILE__,__FUNCTION__,__LINE__);
	//	DEBUG_S(-3,'structure_remote',$structure_remote,__FILE__,__FUNCTION__,__LINE__);

	}
	
	
	
				
		
		
		

	
}