se/SE/dev/audit_krs_con_digger/audit_krs_con_digger.php

<?php
$_SERVER['SERVER_NAME'] = 'biuro.biall-net.pl';


//when used in /Library/Server/Web/Data/Sites/bzyk-biale-plamy-php-temp/SE/dev on biuro.biall-net.pl
if( file_exists ( "../../se-lib/bootstrap.php"))
require("../../se-lib/bootstrap.php");
//when used in  in native SE
else require("../../../../SE-production-git/SE/se-lib/bootstrap.php");

Lib::loadClass("Vendor_Geophp");


 $db = DB::getDB();


// DB::getPDO()->query("truncate table Rozdzielcza_test_bzyk_Cables_to_PE");

//unset($ID_Way);

/*foreach($cables_from_joins_to_PE_arr as $id_j=>$Path_Point_val) {
		
		$sql_Path_Point_val="insert into Rozdzielcza_test_bzyk_Cables_to_PE (the_geom,ID_Way,ID_Point,len,PE_ID_Join)
		     values (ST_GeomFromText('".$Path_Point_val['LINESTRING']."'),'".$Path_Point_val['ID_Way']."',
		     '".$Path_Point_val['ID_Point']."' ,'".$Path_Point_val['len']."','".$Path_Point_val['PE_ID_Join']."'  )   ";
		    echo "#216Query path ins:: ".$sql_Path_Point_val."  ";
		    //if(strlen($edge_joins_filled_paths[$ID_Way]['asText'])>2)
		    if(strlen($Path_Point_val['LINESTRING'])>1) 
		      DB::getPDO()->query($sql_Path_Point_val);
		    else echo " ERROR blad danych!!! dla ID_Point:".$Path_Point_val['ID_Point'];
		    //else echo "#296 path nie wygenerowalo sie !! dla ".$ID_Way." i pkt ".$pkt_A." , ".$pkt_B." \n";
			
	
}
*/

$tables['Y']['BI_audit_ENERGA_PRACOWNICY']['rel']['pesel']='pesel';
$tables['Y']['BI_audit_ENERGA_PRACOWNICY']['rel']['nip']='nip';
$tables['Y']['BI_audit_ENERGA_PRACOWNICY']['rel']['regon']='regon';

$tables['Y']['BI_audit_ENERGA_PRACOWNICY']['rel']['ID_BI_audit_ENERGA_PRACOWNICY']='ID';

//$tables['X']['BI_audit_KW_requested']['rel_search']['pesel']='Seller_Person';
//$tables['X']['BI_audit_KW_requested']['rel']['pesel']['search']='like';

$tables['Z']['BI_audit_ENERGA_PRACOWNICY_adresy']['rel']['ID_BI_audit_ENERGA_PRACOWNICY']='ID_BI_audit_ENERGA_PRACOWNICY';
$tables['Z']['BI_audit_ENERGA_PRACOWNICY_adresy']['rel_and']['TERYT']['TERYT_SYM']='TERYT_SYM';
$tables['Z']['BI_audit_ENERGA_PRACOWNICY_adresy']['rel_and']['TERYT']['TERYT_SYM']='TERYT_SYM_UL';
$tables['Z']['BI_audit_ENERGA_PRACOWNICY_adresy']['rel_and']['TERYT']['budynek']='nrBudynku';
$tables['Z']['BI_audit_ENERGA_PRACOWNICY_adresy']['rel_and']['TERYT']['nrLokalu']='nrLokalu';



$tables['X']['BI_audit_ENERGA_RUM_KONTRAHENCI']['rel']['nip']='NIP';
$tables['X']['BI_audit_ENERGA_RUM_KONTRAHENCI']['rel']['regon']='REGON';
$tables['X']['BI_audit_ENERGA_RUM_KONTRAHENCI']['rel']['pesel']='PESEL';
//$tables['X']['BI_audit_ENERGA_RUM_KONTRAHENCI']['rel']['teryt']=1;

$tables['X']['BI_audit_ENERGA_RUM_UMOWY']['rel_like']['nip']='Strony_umowy';



$tables['Z']['BI_audit_CEIDG']['rel']['nip']='nip';
$tables['Z']['BI_audit_CEIDG']['rel']['regon']='regon';
$tables['Z']['BI_audit_CEIDG']['rel_and']['adres']['miejscowosc']='miejscowosc';
$tables['Z']['BI_audit_CEIDG']['rel_and']['adres']['ulica']='ulica';
$tables['Z']['BI_audit_CEIDG']['rel_and']['adres']['kodPocztowy']='kodPocztowy';
$tables['Z']['BI_audit_CEIDG']['rel_and']['adres']['budynek']='budynek';
$tables['Z']['BI_audit_CEIDG']['ref'][19]='BI_audit_CEIDG_pelnomocnicy';
$tables['Z']['BI_audit_CEIDG']['ref'][18]='BI_audit_CEIDG_powiazania';

$tables['Z']['BI_audit_CEIDG_pelnomocnicy']['rel']['nip']='nip';
$tables['Z']['BI_audit_CEIDG_pelnomocnicy']['rel_and']['adres']['miejscowosc']='miejscowosc';
$tables['Z']['BI_audit_CEIDG_pelnomocnicy']['rel_and']['adres']['ulica']='ulica';
$tables['Z']['BI_audit_CEIDG_pelnomocnicy']['rel_and']['adres']['kodPocztowy']='kodPocztowy';
$tables['Z']['BI_audit_CEIDG_pelnomocnicy']['backref'][19]='BI_audit_CEIDG';


$tables['Z']['BI_audit_CEIDG_powiazania']['rel']['nip']='nip';
$tables['Z']['BI_audit_CEIDG_powiazania']['rel']['regon']='nip';
$tables['Z']['BI_audit_CEIDG_powiazania']['backref'][18]='BI_audit_CEIDG';


$tables['Z']['BI_audit_KRS']['rel']['nip']='nip';
$tables['Z']['BI_audit_KRS']['rel']['regon']='regon';
$tables['Z']['BI_audit_KRS']['rel']['krs']='krs';
$tables['Z']['BI_audit_KRS']['rel_and']['adres']['miejscowosc']='A_miejscowosc';
$tables['Z']['BI_audit_KRS']['rel_and']['adres']['ulica']='A_ulica';
$tables['Z']['BI_audit_KRS']['rel_and']['adres']['kodPocztowy']='A_kod';
$tables['Z']['BI_audit_KRS']['rel_and']['adres']['budynek']='A_nrDomu';
$tables['Z']['BI_audit_KRS']['ref'][12]='BI_audit_KRS_person';
$tables['Z']['BI_audit_KRS']['ref'][11]='BI_audit_KRS_company';



$tables['Z']['BI_audit_KRS_company']['rel']['regon']='regon';
$tables['Z']['BI_audit_KRS_company']['rel']['krs']='krs';
$tables['Z']['BI_audit_KRS_company']['backref'][11]='BI_audit_KRS';








function start_dig($tables) {
	global $db;
	foreach($tables['Y'] as $Y=>$t)  {
		$sql='select * from '.$Y.' where ID=12 or ID=19 or 1=1 limit 100' ;
		$res=$db->query($sql);
		 while($h=$db->fetch($res)) {
			 $hist_loop[$Y][]=$h;
			 $hist_time[][$Y]=$h;
			 dig_next($hist_loop,$t,$h,1,$hist_time);
			 unset($hist_time);
		 }
	}
}





function dig_next($hist_loop,$prev_tbl,$prev_dig,$loop,$hist_time) {
	 global $db,$tables;
	 $loop++;
	 echo "\n<hr>###LOOP ".$loop;
	 if($loop<15) {
			//detect next ref tbls
			 $sql = array();
			 $sql_RELAND = array();
			 foreach($tables['Z'] as $Z=>$z) {
				 	//rels in tbls
				 	foreach($z['rel'] as $z_rel_from=>$z_rel_to) {
				 	//	echo "110 test z_rel_fr ".$z_rel_from." to z_rel_to ".$z_rel_to ;
				 		if( isset($prev_tbl['rel'][$z_rel_from])) {
					 		if( strlen($prev_dig->$prev_tbl['rel'][$z_rel_from]) > 1  ) {
						 		$sql[$Z]['rel'][$z_rel_to]= $prev_dig->$prev_tbl['rel'][$z_rel_from];
					 		}
				 		}
				 	}
				 	
				 	
				 	//search addr
				 	if(isset($z['rel_and'])) {
					 	foreach($z['rel_and'] as $z_rel_and=>$z_rel_and_name) {
						 	$rel_and_ok=1;
		
						 	foreach($z_rel_and_name as $z_rel_and_name_cur=>$z_rel_and_name_col_to) {
							 	if(isset($prev_dig->$z_rel_and_name_cur)  ) {//and ( strlen($prev_dig->$z_rel_and_name_cur) > 2) 
								 	$rel_and_ok=2;
								// 	echo "<br>121 rel_and OK for tbl ".$Z." z_rel_and_name_col_to: ".$z_rel_and_name_col_to." value:".$prev_dig->$z_rel_and_name_cur." rel_and_ok=".$rel_and_ok;
								}
							 	else {
							//	 	echo "<br> #127 UNSET ".$prev_dig->$z_rel_and_name_cur." / ".$z_rel_and_name_col_to." ";
								 	$rel_and_ok=1;
								 	}
						 	}
						  
						  if($rel_and_ok==2) {
							  //echo "<br>#133 Z=".$Z."/z=".$z." ok rel_and_ok=".$rel_and_ok;
							  foreach($z_rel_and_name as $z_rel_and_name_cur=>$z_rel_and_name_col_to) {
							//	  echo "<br> 135... ok. Z=".$Z."/ z_rel_and_name=".$z_rel_and_name_cur."/ z_rel_and_name_col_to=".$z_rel_and_name_col_to."/ prev_dig-z_rel_and_name_cur=".$prev_dig->$z_rel_and_name_cur;
							  	$sql_rel_and[$Z][$z_rel_and][$z_rel_and_name_col_to]=$prev_dig->$z_rel_and_name_cur;
		
							  }
						  } else {
							//  echo "<br>#137 error rel_and_ok=".$rel_and_ok;
						  }
					 	}
					 	
				 	}
			 }
			 
			 
			// echo "<br>135:sql_rel_and <pre>";
			//		 	print_r($sql_rel_and);
			//		 	echo " .EOF</pre>";
			if( isset($sql_rel_and)) {
			foreach($sql_rel_and as $T=>$z_rel_and_arr) {
				foreach($z_rel_and_arr as $z_rel_and_name=>$z_rel_and_name_col) {
					//echo "<br>156 z_rel_and_name=".$z_rel_and_name." ";
					$sql_rel_and_sql[$T][$z_rel_and_name]="select * from ".$T." where ( ";
					$and=0;
					foreach($z_rel_and_name_col as $z_rel_and_name_cur=>$z_rel_and_name_col_to) {
						if( $and==1) $sql_rel_and_sql[$T][$z_rel_and_name].=" and `".$z_rel_and_name_cur."` like '".$z_rel_and_name_col_to."' and `".$z_rel_and_name_cur."` not like '%--%' and   `".$z_rel_and_name_cur."` not like '' " ;
						else $sql_rel_and_sql[$T][$z_rel_and_name].="  `".$z_rel_and_name_cur."` like '".$z_rel_and_name_col_to."' and `".$z_rel_and_name_cur."` not like '%--%' and   `".$z_rel_and_name_cur."` not like ''  " ;
					//	if($z_rel_and_name_cur=='miejscowosc') $sql_rel_and_sql[$T][$z_rel_and_name].=" and `miejscowosc` not like '' " ; 
					//	if($z_rel_and_name_cur=='A_ulica') $sql_rel_and_sql[$T][$z_rel_and_name].=" and `A_ulica` not like '' " ; 
					//	if($z_rel_and_name_cur=='ulica') $sql_rel_and_sql[$T][$z_rel_and_name].=" and `ulica` not like '' " ; 						
					//	if($z_rel_and_name_cur=='kodPocztowy') $sql_rel_and_sql[$T][$z_rel_and_name].=" and `kodPocztowy` not like '' " ; 						
					//	if($z_rel_and_name_cur=='budynek') $sql_rel_and_sql[$T][$z_rel_and_name].=" and `budynek` like '".$z_rel_and_name_col_to."' " ; 	
					//	if($z_rel_and_name_cur=='A_nrDomu') $sql_rel_and_sql[$T][$z_rel_and_name].=" and `A_nrDomu` like '".$z_rel_and_name_col_to."' " ; 	
					//	if($z_rel_and_name_cur=='kodPocztowy') $sql_rel_and_sql[$T][$z_rel_and_name].=" and `kodPocztowy` like '".$z_rel_and_name_col_to."' " ; 							
						
											
						$and=1;
					}
					$sql_rel_and_sql[$T][$z_rel_and_name].="  ) " ;
					if( isset($hist_loop[$T])) {
					 	 foreach($hist_loop[$T] as $hist_rec) {
						 	 $sql_rel_and_sql[$T][$z_rel_and_name].=" and ID!='".$hist_rec->ID."' ";
					 	 }
				 	 }
					$sql_rel_and_sql[$T][$z_rel_and_name].=" ; " ;
				 	 dig_arr_cur($hist_loop,$T,$sql_rel_and_sql[$T][$z_rel_and_name],$hist_time,$loop);
				}
			}
			}
			 
			 //echo "<br>164:sql_rel_and_sql <pre>";
				//	 	print_r($sql_rel_and_sql);
				//	 	echo " .EOF</pre>";
			
			 //X test
			 
			 //echo " <br> 129 prev <pre>";
			 // print_r($prev_dig);
			 // echo "<br> prevtbl";
			  //	  print_r($prev_tbl);
			 
			//  echo "</pre>";
			 
			 foreach($tables['X'] as $X=>$x) {
				 	//rels in tbls
				 	foreach($x['rel'] as $x_rel_from=>$x_rel_to) {
				 		//echo "\n<br> #133test tbl ".$X." z_rel_fr ".$x_rel_from." to z_rel_to ".$x_rel_to." prev tbl " ;
				 		if( isset($prev_tbl['rel'][$x_rel_from]) ) { //isset($prev_dig->$prev_tbl['rel'][$x_rel_to])  
					// 	echo "<br>141 rel from ".$x_rel_from." exists .. testing field ".$prev_tbl['rel'][$x_rel_from]." <br>";//strlen(".$prev_dig->$prev_tbl['rel'][$x_rel_to].") 
		
					 		if( isset($prev_dig->$prev_tbl['rel'][$x_rel_from])  and strlen($prev_dig->$prev_tbl['rel'][$x_rel_from]) > 1 ) { // 
						 		$sql_X[$X]['rel'][$x_rel_to]= $prev_dig->$prev_tbl['rel'][$x_rel_from];
					 		}
				 		}		 	 
				 	}
				 	//rel_like
				 	foreach($x['rel_like'] as $x_rel_from=>$x_rel_to) {
				 		//echo "\n<br> #133test tbl ".$X." z_rel_fr ".$x_rel_from." to z_rel_to ".$x_rel_to." prev tbl " ;
				 		if( isset($prev_tbl['rel'][$x_rel_from]) ) { //isset($prev_dig->$prev_tbl['rel'][$x_rel_to])  
					// 	echo "<br>141 rel from ".$x_rel_from." exists .. testing field ".$prev_tbl['rel'][$x_rel_from]." <br>";//strlen(".$prev_dig->$prev_tbl['rel'][$x_rel_to].") 
		
					 		if( isset($prev_dig->$prev_tbl['rel'][$x_rel_from])  and strlen($prev_dig->$prev_tbl['rel'][$x_rel_from]) > 1 ) { // 
						 		$sql_X1[$X]['rel_like'][$x_rel_to]= $prev_dig->$prev_tbl['rel'][$x_rel_from];
					 		}
				 		}		 	 
				 	}
			 }
			 
			  flush();
		//	 echo "\n<br>sql_X<pre>";
		//	 print_r($sql_X);
		//	 echo "</pre>";
			 if( isset($sql_X)) {
			 foreach($sql_X as $T => $rel) {
				 	 $sql_x[$T]=" select * from ".$T." where ( ";
				 	 foreach($rel['rel'] as $col=>$val) {
					 	 if( isset($sql_x_str[$T]))  $sql_x_str[$T].=" or `".$col."` = '".$val."' " ;
					 	 else $sql_x_str[$T]=" `".$col."` = '".$val."' " ;
				 	 }
				 	 $sql_x[$T].=$sql_x_str[$T]." ) ";
				 	 //not loop
				 	 if( isset($hist_loop[$T])) {
					 	 foreach($hist_loop[$T] as $hist_rec) {
						 	 $sql_t[$T].=" and ID!='".$hist_rec->ID."' ";
					 	 }
				 	 }
			 	 }
			 }
			// echo "<br> sql_x" ;
			 //print_r($sql_x);
			 
			 if( isset($sql_X1)) {
			 foreach($sql_X1 as $T => $rel) {
				 	 $sql_x1[$T]=" select * from ".$T." where ( ";
				 	 foreach($rel['rel_like'] as $col=>$val) {
					 	 if( isset($sql_x1_str[$T]))  $sql_x1_str[$T].=" or `".$col."` like '%".$val."'%" ;
					 	 else $sql_x1_str[$T]=" `".$col."` = '".$val."' " ;
				 	 }
				 	 $sql_x1[$T].=$sql_x1_str[$T]." ) ";
				 	 //not loop
				 	 if( isset($hist_loop[$T])) {
					 	 foreach($hist_loop[$T] as $hist_rec) {
						 	 $sql_t[$T].=" and ID!='".$hist_rec->ID."' ";
					 	 }
				 	 }
			 	 }
			 }
			 
			 
			 if( isset($sql_x)) {
				 foreach($sql_x as $X => $sql_ext) {
					 echo "\n<br>174 Querying :".$sql_ext ;
					  $res=$db->query($sql_ext) or die('175 blad');
					  while($h=$db->fetch($res)) {
						  echo "\n<hr> #177 POWIAZANIE ZNALEZIONE DLA X <pre>";
						  print_r($h);
						  echo "<br> Historia sledzen:";
						  print_r($hist_time);
						  echo "#177 eof </pre>";
					  }
				 }
			 }
			 if( isset($sql_x1)) {
				 foreach($sql_x1 as $X => $sql_ext) {
					 echo "\n<br>306 Querying :".$sql_ext ;
					  $res=$db->query($sql_ext) or die('307 blad');
					  while($h=$db->fetch($res)) {
						  echo "\n<hr> #309 POWIAZANIE ZNALEZIONE DLA X2 <pre>";
						  print_r($h);
						  echo "<br> Historia sledzen:";
						  print_r($hist_time);
						  echo "#177 eof </pre>";
					  }
				 }
			 }
			 //X test eof
			 
			 
			 
			 	 foreach($sql as $T => $rel) {
				 	 $sql_t[$T]=" select * from ".$T." where ( ";
				 	 foreach($rel['rel'] as $col=>$val) {
					 	 if( isset($sql_t_str[$T]))  $sql_t_str[$T].=" or `".$col."` = '".$val."' " ;
					 	 else $sql_t_str[$T]=" `".$col."` = '".$val."' " ;
				 	 }
				 	 $sql_t[$T].=$sql_t_str[$T]." ) ";
				 	 //not loop
				 	 if( isset($hist_loop[$T])) {
					 	 echo "<br> hist loop cnt: ". count($hist_loop[$T]);
					 	 foreach($hist_loop[$T] as $hist_rec) {
						 	 $sql_t[$T].=" and ID!='".$hist_rec->ID."' ";
					 	 }
				 	 }
				 	 
			 	 }
			 	 
			// echo "<br> 202 hist  loop cnt: <pre> ". count($hist_loop);
			//	 	  print_r($hist_loop);
			//	 	 echo "</pre>";
			/* echo "<pre>sql_t";
			  print_r($sql);
			 echo "<br>sql_t ";
			  print_r($sql_t);
			 echo "</pre>";
			  */
			  if( isset($sql_t)) {
				  foreach($sql_t as $T => $sql_ext)  { 
					  dig_arr_cur($hist_loop,$T,$sql_ext,$hist_time,$loop);
				  }
			  }
			  //todo look in Y and die()
			  
	}	
	echo "<br>#243 glebokosc:".$loop;
}


function dig_arr_cur($hist_loop,$T,$sql_ext,$hist_time,$loop) {
	global $db,$tables;
	
	  echo "\n<br>216: Querying :T ".$T." ext: ".$sql_ext ;//$sql_ext
//	  echo "\n<br> las fou"
	  flush();
			  $res=$db->query($sql_ext) or die('211 blad '.$sql_ext);
			  while($h=$db->fetch($res)) {
				  echo "\n<br> #149 found ".$h->ID;
				 // echo "<pre>"; print_r($h); echo "</pre>";
				 $hist_loop[$T][]=$h;
				 $hist_time[][$T]=$sql_ext;
				 $hist_time[][$T]=$h;
				// print_r($hist_loop);
				 
				 dig_next($hist_loop, $tables['Z'][$T],$h,$loop,$hist_time);
				 //rel dzieci $tables['Z']['BI_audit_CEIDG']['ref'][19]='BI_audit_CEIDG_pelnomocnicy';
				 if(isset($tables['Z'][$T]['ref'])) {
				// echo "<br>#296 try to ref tables for T ".$T."<pre>";
				//  	print_r($tables['Z'][$T]['ref']);
				// echo "</pre>";
				 
					 foreach($tables['Z'][$T]['ref'] as $ref_id => $ref_table) {
						 $sql_ref=" select REMOTE_PRIMARY_KEY from `CRM__#REF_TABLE__".$ref_id."` where `PRIMARY_KEY`=".$h->ID." ";
						 $res_rel=$db->query($sql_ref) or die('211 blad '.$sql_ref);
						 echo "<br>#297 ".$sql_ref;
						 while($h_rel=$db->fetch($res_rel)){
							 $sql_rel_rec="select * from `".$ref_table."` where ID=".$h_rel->REMOTE_PRIMARY_KEY." ";
							 $res_rel_rec=$db->query($sql_rel_rec) or die('211 blad '.$sql_rel_rec);
							 echo "<br>#159 ref tbl ".$sql_rel_rec;
							 while($h_rel_rec=$db->fetch($res_rel_rec)){
								 echo "#315 fnd:".$h_rel_rec->ID;
								 $hist_loop[$ref_table][]=$h_rel_rec;
								 $hist_time[][$ref_table]=$sql_rel_rec;
								 $hist_time[][$ref_table]=$h_rel_rec;
								 dig_next($hist_loop,$tables['Z'][$ref_table],$h_rel_rec,$loop,$hist_time);
							 }
						 }
					 }
				 
				 } else echo "#322 not ref to ".$T;
				 if(isset($tables['Z'][$T]['backref'])) {
				 //echo "<br>#296 try to backref tables  T ".$T."<pre>";
				 // 	print_r($tables['Z'][$T]['backref']);
				 //echo "</pre>";
				 
				 
					 foreach($tables['Z'][$T]['backref'] as $ref_id => $ref_table) {
						 $sql_ref=" select PRIMARY_KEY from `CRM__#REF_TABLE__".$ref_id."` where `REMOTE_PRIMARY_KEY`=".$h->ID." ";
						 $res_rel=$db->query($sql_ref) or die('318 blad '.$sql_ref);
						 echo "<br>#319 backref ".$sql_ref;
						 while($h_rel=$db->fetch($res_rel)){
							 $sql_rel_rec="select * from `".$ref_table."` where ID=".$h_rel->PRIMARY_KEY." ";
							 $res_rel_rec=$db->query($sql_rel_rec) or die('211 blad '.$sql_rel_rec);
							 echo "<br>#324 ref tbl ".$sql_rel_rec;
							 while($h_rel_rec=$db->fetch($res_rel_rec)){
								 echo "#315 fnd:".$h_rel_rec->ID;
								 $hist_loop[$ref_table][]=$h_rel_rec;
								 $hist_time[][$ref_table]=$sql_rel_rec;
								 $hist_time[][$ref_table]=$h_rel_rec;
								 dig_next($hist_loop,$tables['Z'][$ref_table],$h_rel_rec,$loop,$hist_time);
							 }
						 }
					 }
				 
				 } else echo "#322 not backref to ".$T;
				 
			 } 
		  
}


start_dig($tables);

echo "<hr>end of raport ";