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_UL']='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']['rel_and']['TERYT']['TERYT_SYM']='TERYT_SYM';
$tables['Z']['BI_audit_CEIDG']['rel_and']['TERYT']['TERYT_SYM_UL']='TERYT_SYM_UL';
$tables['Z']['BI_audit_CEIDG']['rel_and']['TERYT']['nrLokalu']='lokal';
$tables['Z']['BI_audit_CEIDG']['rel_and']['TERYT']['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']['rel_and']['TERYT']['TERYT_SYM']='TERYT_SYM';
$tables['Z']['BI_audit_KRS']['rel_and']['TERYT']['TERYT_SYM_UL']='TERYT_SYM_UL';
$tables['Z']['BI_audit_KRS']['rel_and']['TERYT']['nrLokalu']='A_nrLokalu';
$tables['Z']['BI_audit_KRS']['rel_and']['TERYT']['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,$Y);
unset($hist_time);
}
}
}
function dig_next($hist_loop,$prev_tbl,$prev_dig,$loop,$hist_time,$last_tbl='n/s') {
global $db,$tables;
$loop++;
echo "\n
###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_col_to) ) {//and ( strlen($prev_dig->$z_rel_and_name_cur) > 2)
if($rel_and_ok<>0) $rel_and_ok=2;
// echo "\n
121 rel_and from:".$last_tbl." 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 "
#127 UNSET ".$prev_dig->$z_rel_and_name_cur." / ".$z_rel_and_name_col_to." ";
$rel_and_ok=0;
}
}
if($rel_and_ok==2) {
// echo "\n
#133 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 "\n
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 "
#137 error rel_and_ok=".$rel_and_ok;
}
}
}
}
// echo "
135:sql_rel_and ";
// print_r($sql_rel_and);
// echo " .EOF
";
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 "
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,$last_tbl);
}
}
}
//echo "
164:sql_rel_and_sql ";
// print_r($sql_rel_and_sql);
// echo " .EOF
";
//X test
//echo "
129 prev ";
// print_r($prev_dig);
// echo "
prevtbl";
// print_r($prev_tbl);
// echo "
";
foreach($tables['X'] as $X=>$x) {
//rels in tbls
if( isset($x['rel']) ) {
foreach($x['rel'] as $x_rel_from=>$x_rel_to) {
//echo "\n
#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 "
141 rel from ".$x_rel_from." exists .. testing field ".$prev_tbl['rel'][$x_rel_from]."
";//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
if( isset($x['rel_like'])) {
foreach($x['rel_like'] as $x_rel_from=>$x_rel_to) {
//echo "\n
#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 "
141 rel from ".$x_rel_from." exists .. testing field ".$prev_tbl['rel'][$x_rel_from]."
";//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
sql_X";
// print_r($sql_X);
// echo "
";
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 "
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."` like '%".$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
174 Querying :".$sql_ext ;
$res=$db->query($sql_ext) or die('175 blad');
while($h=$db->fetch($res)) {
echo "\n
#177 POWIAZANIE ZNALEZIONE DLA X ";
print_r($h);
echo "
Historia sledzen:";
print_r($hist_time);
echo "#177 eof
";
}
}
}
if( isset($sql_x1)) {
foreach($sql_x1 as $X => $sql_ext) {
echo "\n
306 Querying :".$sql_ext ;
$res=$db->query($sql_ext) or die('307 blad');
while($h=$db->fetch($res)) {
echo "\n
#309 POWIAZANIE ZNALEZIONE DLA X2 ";
print_r($h);
echo "
Historia sledzen:";
print_r($hist_time);
echo "#177 eof
";
}
}
}
//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 "
hist loop cnt: ". count($hist_loop[$T]);
foreach($hist_loop[$T] as $hist_rec) {
$sql_t[$T].=" and ID!='".$hist_rec->ID."' ";
}
}
}
// echo "
202 hist loop cnt: ". count($hist_loop);
// print_r($hist_loop);
// echo "
";
/* echo "sql_t";
print_r($sql);
echo "
sql_t ";
print_r($sql_t);
echo "
";
*/
if( isset($sql_t)) {
foreach($sql_t as $T => $sql_ext) {
dig_arr_cur($hist_loop,$T,$sql_ext,$hist_time,$loop,$last_tbl);
}
}
//todo look in Y and die()
}
echo "
#243 glebokosc:".$loop;
}
function dig_arr_cur($hist_loop,$T,$sql_ext,$hist_time,$loop,$last_tbl) {
global $db,$tables;
echo "\n
216: from:".$last_tbl." Querying :T ".$T." ext: ".$sql_ext ;//$sql_ext
// echo "\n
las fou"
flush();
$res=$db->query($sql_ext) or die('211 blad '.$sql_ext);
while($h=$db->fetch($res)) {
echo "\n
#149 found ".$h->ID;
// echo ""; print_r($h); echo "
";
$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 "
#296 try to ref tables for T ".$T."";
// print_r($tables['Z'][$T]['ref']);
// echo "
";
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 "
#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 "
#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 "
#296 try to backref tables T ".$T."";
// print_r($tables['Z'][$T]['backref']);
//echo "
";
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 "
#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 "
#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 "
end of raport ";