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']['ref'][]='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']['ref'][]='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';
function start_dig($tables) {
global $db;
foreach($tables['Y'] as $Y=>$t) {
$sql='select * from '.$Y.' limit 100' ;
$res=$db->query($sql);
while($h=$db->fetch($res)) {
//$hist_loop[$Y][]=$h;
dig_next($hist_loop,$t,$h,1);
}
}
}
function dig_next($hist_loop,$prev_tbl,$prev_dig,$loop) {
global $db,$tables;
$loop++;
echo "\n
###LOOP ".$loop;
if($loop>20) 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)) {
$rel_and_ok=2;
// echo "
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 "
#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 "
#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 "
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
";
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 ( ";
foreach($z_rel_and_name_col as $z_rel_and_name_cur=>$z_rel_and_name_col_to) {
if( isset($sql_rel_and_sql[$T][$z_rel_and_name])) $sql_rel_and_sql[$T][$z_rel_and_name].=" and `".$z_rel_and_name_cur."` like '%.".$z_rel_and_name_col_to."%'" ;
else $sql_rel_and_sql[$T][$z_rel_and_name]=" `".$z_rel_and_name_cur."` like '%.".$z_rel_and_name_col_to."%'" ;
}
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].=" ; " ;
}
}
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
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];
}
}
}
}
flush();
// echo "\n
sql_X";
// print_r($sql_X);
// echo "
";
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_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 ";
print_r($h);
print_r($hist_loop);
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(,,$sql_ext);
/*
echo "\n
216: Querying :T ".$T." ext: ".$sql_ext ;
$res=$db->query($sql_ext) or die('211 blad '.$sql_ext);
while($h=$db->fetch($res)) {
echo "\n
#149 found "; print_r($h);
$hist_loop[$T][]=$h;
// print_r($hist_loop);
dig_next($hist_loop, $tables['Z'][$T],$h,$loop);
//rel dzieci
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);
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)){
$hist_loop[$ref_table][]=$h_rel_rec;
dig_next($hist_loop,$tables['Z'][$ref_table],$h_rel_rec,$loop);
}
}
}
} */
}
}
//todo look in Y and die()
echo "
#243 loop ".$loop;
}
function dig_arr($hist_loop,$T,$sql) {
global $db,$tables;
{
echo "\n
216: Querying :T ".$T." ext: ".$sql_ext ;
$res=$db->query($sql_ext) or die('211 blad '.$sql_ext);
while($h=$db->fetch($res)) {
echo "\n
#149 found "; print_r($h);
$hist_loop[$T][]=$h;
// print_r($hist_loop);
dig_next($hist_loop, $tables['Z'][$T],$h,$loop);
//rel dzieci
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);
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)){
$hist_loop[$ref_table][]=$h_rel_rec;
dig_next($hist_loop,$tables['Z'][$ref_table],$h_rel_rec,$loop);
}
}
}
}
}
}
start_dig($tables);
echo "
end of raport ";