se/SE/superedit-SYNC_GIS_TABLES.php

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

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_relations($table_to_set,$field_index,$field_to_set,$table_from,$field_from,$ST_Relate='******F**',$filter_sql='') {
			 global $gdb;
			 $sql='
			 
			 	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 
			 	CROSS JOIN "'.$table_from.'"  as t2;
			 	
			 	 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='') {
			 			 global $gdb;

			 $sql='
			 drop table if exists "'.$table_from.'_AGR" ;
			 select t1.'.$field_index.'  , t2.'.$field_index.' as gid_t2
				, ST_Intersects(t1."'.$field_from_geom.'",t2."'.$field_from_geom.'")
				, case ST_Relate(t1."'.$field_from_geom.'",t2."'.$field_from_geom.'",\''.$ST_Relate.'\') 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
				;
				
				update  "'.$table_to_set.'"  t1
				set "'.$field_to_set.'"=(
				select 
				 round(sum(t2.length)) 
				 from "'.$table_from.'_AGR" as t2 where t2.'.$field_index.'=t1.'.$field_index.'
				)
				
				;';
				
				echo "<br>sql to :  ".$sql;
				$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='') {
			 			 global $gdb;

			 $sql='
			 drop table if exists "'.$table_from.'_AGR" ;
			 select t1.'.$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.'\') 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
				;
				
				
				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.'
				)
				
				;';
				
				echo "<br>sql to :  ".$sql;
				$gdb->query($sql);
		 }
		 
		gis_opto_calculations('Inwestycje_Planowane','gid','Agr_metrow_mikrorurek_5szt','Rozdzielcza_Pakiet5MikrorurekMagistrala_WSG84','the_geom','T********');
		gis_opto_calculations('Inwestycje_Planowane','gid','Agr_metrow_mikrorurek','Rozdzielcza_Mikrokanalizacja_do_klienta','the_geom','T********');
		gis_opto_calculations('Inwestycje_Planowane','gid','Agr_ilosc_wykopu','Rozdzielcza_Wykop_przedmiar_na_mikrorurki','the_geom','******F**');
		gis_opto_calculations('Inwestycje_Planowane','gid','Agr_ilosc_przeciskow','Rozdzielcza_Przeciski_110mm','the_geom','******F**');
		gis_opto_calculations('Inwestycje_Planowane','gid','Agr_ilosc_zabrukow','Rozdzielcza_Zabruki','the_geom','******F**');
		gis_opto_calculations_count('Inwestycje_Planowane','gid','Agr_ilosc_domow','MK_Mieszkania','the_geom','******F**');

	    $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 "Inwestycje_Planowane" set "Agr_ilosc_zabrukow"=0 where "Agr_ilosc_zabrukow" is null;';
		$gdb->query($sql_cena);

		$sql_cena='update "Inwestycje_Planowane" set 
					koszt_wspolny="Agr_ilosc_wykopu"*10
					+"Agr_metrow_mikrorurek"*3
					+"Agr_metrow_mikrorurek_5szt"*7
					+"Agr_ilosc_przeciskow"*80
					+"Agr_ilosc_zabrukow"*40 
					
					;'; //"Agr_metrow_w_tpsa*3 +Agr_metrow_kabel*
					//+ studnia +
		 
		 $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);

		 
		 
		 
		 
		 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');


		
		 die("zapytanie: ".$sql);
//	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 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__);

	}
	
	
	
		$trigger_psql_do_timestamp="CREATE OR REPLACE FUNCTION update_modified_column()
			RETURNS TRIGGER AS $$
			BEGIN
			NEW.modified = now(); 
			RETURN NEW;
			END;";
		
		
		
		

	
}