se/SE/schema/WPS_Functions/BI_audit/cache_relations.php

#!/usr/bin/env php
<?php

mysql_connect('localhost', 'root', 'kontem11on');
mysql_select_db('SES_USERS2');
mysql_query("set names utf8");

class FindCorelations {

	private $TABLE, $TABLE_REF, $TABLE_DEST, $DEPTH;
	public $results = array();
	private $nodesCache = array();

	private $corelations = array(
		'nip' => array('nip'),
		'regon' => array('regon'),
		'krs' => array('krs'),
		'pesel' => array('pesel'),
		'adres' => array('TERYT_SYM', 'TERYT_SYM_UL', 'nrDomu', 'nrLokalu'),
	);

	function __construct($TABLE, $TABLE_REF, $TABLE_DEST, $DEPTH) {
		$this->TABLE = $TABLE;
		$this->TABLE_REF = $TABLE_REF;
		$this->TABLE_DEST = $TABLE_DEST;
		$this->DEPTH = $DEPTH;
	}

	public function findNodes($ID, $corelation = "", $path = array()) {
		if (isset($path[$ID])) {
//			echo $ID . "\n";
			return false;
		}
		$path[$ID] = $corelation;
		$query = "select * from {$this->TABLE} where ID = '{$ID}'";
		if (!($result = mysql_query($query))) return false;
		$row = mysql_fetch_object($result);
		if (in_array($row->REMOTE_TABLE, $this->TABLE_DEST)) {
			$this->results[] = $path;
			return true;
		}

		if (count($path) > $this->DEPTH) {
			return false;
		}

		$dupaNodes = array();
		if (isset($this->nodesCache[$ID])) {
			$nodes = $this->nodesCache[$ID];
		} else {
			$nodes = array();
			$joins = array();
			$items = "";
			foreach ($this->corelations as $name => $columns) {
				$ok = 0;
				$join = array();
				foreach ($columns as $column) {
					$join[] = "t1.{$column} = t2.{$column}";
					if ($row->$column) {
						$ok++;
					}
				}
				if ($ok / count($columns) > 0.5) {
					$joins[] = "(" . implode(" and ", $join) . ")";
					$items .= ", if(" . implode(" and ", $join) . ", 1, 0) as {$name}";
				}
			}
			if (!$joins) return false;
			$query = "select t2.ID as ID {$items} from {$this->TABLE} t1 join {$this->TABLE} t2 on " . implode(" or ", $joins) . " join BI_audit_ALL_usable usable on t2.ID = usable.ID_BI_audit_ALL" . 
//				" where t1.ID = '{$ID}' and t2.ID not in (" . implode(",", array_keys($path)) . ")";
				" where t1.ID = '{$ID}' and t2.ID not in ({$ID})";
//			echo $query."\n";
			if ($result = mysql_query($query)) {
				while ($item = mysql_fetch_object($result)) {
					$names = array();
					foreach ($this->corelations as $name => $v) {
						if (isset($item->$name) && $item->$name) $names[] = $name;
					}
//					if (isset($path[$item->ID])) echo $item->ID . " :: " . implode("->", array_keys($path)) . "\n";
//if (!isset($path[$item->ID])) {
//	$dupaNodes[$item->ID] = $name;
//}
					$nodes[$item->ID] = implode(', ', $names);
				}
			}

//			$query = "select ref.ID2 from {$this->TABLE_REF} ref join BI_audit_ALL_usable usable on ref.ID2 = usable.ID_BI_audit_ALL where ref.ID1 = '{$ID}' and ref.ID2 not in (" . implode(",", array_keys($path)) . ")";
			$query = "select ref.ID2 from {$this->TABLE_REF} ref join BI_audit_ALL_usable usable on ref.ID2 = usable.ID_BI_audit_ALL where ref.ID1 = '{$ID}'";
			if ($result = mysql_query($query)) {
				while ($item = mysql_fetch_object($result)) {
//					if (!isset($path[$item->ID2])) 
//if (!array_key_exists($item->ID2, $path)) $dupaNodes[$item->ID2] = $row->REMOTE_TABLE;
$nodes[$item->ID2] = $row->REMOTE_TABLE;
				}
			}
			if ($nodes) {
				ksort($nodes);
				$this->nodesCache[$ID] = $nodes;
			}
		}

		if ($nodes) {
			foreach ($nodes as $node => $corel) {
//				if (!isset($path[$node]))
$this->findNodes($node, $corel, $path);
			}
		}
//		ksort($dupaNodes);
//		foreach ($dupaNodes as $node => $corel) echo "{$node}\n";
	}
}



$TABLE = "BI_audit_ALL";
$TABLE_REF = "BI_audit_ALL_ref";
$TABLE_DEST = array("BI_audit_ENERGA_RUM_KONTRAHENCI", "BI_audit_KW_requested_person");

$query = "select ID from {$TABLE} where REMOTE_TABLE = 'BI_audit_ENERGA_PRACOWNICY'";
//$query = "select ref.ID2 as ID from {$TABLE} `all` join BI_audit_ALL_ref ref on `all`.ID = ref.ID1 where all.krs='0000457722';";
$result = mysql_query($query);
$results = array();
$test = new FindCorelations($TABLE, $TABLE_REF, $TABLE_DEST, 7);
while ($row = mysql_fetch_object($result)) $test->findNodes($row->ID);
$results = $test->results;
//print_r($test->badNodes);

$data = base64_encode(gzcompress(json_encode($results)));
echo $data;