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['X']['BI_audit_KW_requested']['rel_search']['pesel']='Seller_Person';
//$tables['X']['BI_audit_KW_requested']['rel']['pesel']['search']='like';

$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['Z']['BI_audit_CEIDG']['rel']['nip']='nip';
$tables['Z']['BI_audit_CEIDG']['rel']['regon']='regon';
$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']['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=12 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>50) die(' loop '.$loop);
	//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 SOUNDEX(`".$z_rel_and_name_cur."`) = SOUNDEX('.".$z_rel_and_name_col_to."') and `".$z_rel_and_name_cur."` not like '%-xxx-%' " ;
				else $sql_rel_and_sql[$T][$z_rel_and_name].="  SOUNDEX(`".$z_rel_and_name_cur."`) = SOUNDEX('".$z_rel_and_name_col_to."') and `".$z_rel_and_name_cur."` not like '%-xxx-%' " ;
				$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];
			 		}
		 		}		 	 
		 	}
	 }
	 
	  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_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>";
			  }
		 }
	 }
	 //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
			  $res=$db->query($sql_ext) or die('211 blad '.$sql_ext);
			  while($h=$db->fetch($res)) {
				  echo "\n<br> #149 found ";// print_r($h);
				 $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
				 if(isset($tables[$T]['ref'])) {
				 echo "<br>#296 try to ref tables <pre>";
				  	print_r($tables[$T]['ref']);
				 echo "</pre>";
				 
					 foreach($tables[$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)){
								 $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);
							 }
						 }
					 }
				 
				 }
				 if(isset($tables[$T]['backref'])) {
				 echo "<br>#296 try to backref tables <pre>";
				  	print_r($tables[$T]['backref']);
				 echo "</pre>";
				 
				 
					 foreach($tables[$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)){
								 $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);
							 }
						 }
					 }
				 
				 }
				 
			 }
		  
}


start_dig($tables);

echo "<hr>end of raport ";