"; $phrase = str_replace(" mit ", " ", $phrase); $phrase = str_replace(" und ", " ", $phrase); $phrase = str_replace(" oder ", " ", $phrase); $phrase = str_replace(" GmbH", "", $phrase); $phrase = str_replace(" Co.", "", $phrase); $phrase = str_replace(" KG", "", $phrase); $phrase = str_replace("&", "", $phrase); $phrase = str_replace("#", "", $phrase); $phrase = str_replace("'", "", $phrase); // echo $phrase . "
"; // Signs to be escaped for reg exp: . \ + * ? [ ^ ] $ ( ) { } = ! < > | : - $specialSignsArray = array("/\!/", "/§/", "/%/", "/\-/", "/_/", "/\./", "/\:/", "/,/", "/;/", "/\$/", "/\?/", "/\+/", "/\*/", "/\|/", "/\=/", "/\(/", "/\)/", "/\[/", "/\]/", "/\/"); $phrase = preg_replace ($specialSignsArray, "", $phrase); // Remove special signs // echo $phrase . "
"; // die(); // endif; $wordArray = spliti(" ", $phrase); $wordArrayLen = count($wordArray); if ($wordArrayLen > 3) : $wordArray = array($wordArray[0], $wordArray[1], $wordArray[2]); $wordArrayLen = count($wordArray); endif; // Executes similarity search for the current headquarters logged in only $parFilterHQ = getParameterValue("0", "SIMILARITY_SEARCH_FILTER_HQ", "0"); $whereClauseHQ = ""; if (!isset($compareType)) : $compareType = "cs"; endif; $compareField = " cmp.cmp_comp "; if ($compareType == "cs") : $compareField = " cmp.cmp_comp "; if ($parFilterHQ == "1" && $hq_id != "") : $whereClauseHQ = " AND cs.hq_id = '" . $hq_id . "' "; endif; endif; if ($compareType == "cr") : $compareField = " cmp.cmp_comp "; if ($parFilterHQ == "1" && $hq_id != "") : $whereClauseHQ = " AND cr.hq_id = '" . $hq_id . "' "; endif; endif; for ($i = 0; $i < $wordArrayLen; $i++) : $phrase = $wordArray[$i]; $phraseLen = strlen($phrase); $regexpSQLArray[$i] = array(); $numOfChecksArray[$i] = 0; if ($phraseLen > 0) : // [1.] Exact match by subtring $regexpSQLArray[$i][] = " LIKE '%" . $phrase . "%'"; // [2.] All letters have to be found in the same sort sequence following directly // Example: "test" => ("blah test blah", "blah tttteeeesssstttt blah", "teeeeest") $tmpRegexp = ""; for ($j = 0; $j < $phraseLen; $j++) : $char = substr($phrase, $j, 1); $tmpRegexp .= $char . "+"; endfor; $regexpSQLArray[$i][] = " REGEXP '" . $tmpRegexp . "'"; // [3.] One of the letters could be another, but the rest has to match // Example: "test" => ("xxxxrestxxxxtastxxxxtertxxxxtesaxxxx") for ($j = 0; $j < $phraseLen; $j++) : $tmpSubstrLeft = substr($phrase, 0, $phraseLen - $j - 1); $tmpSubstrRight = substr($phrase, $phraseLen - $j); $regexpSQLArray[$i][] = " REGEXP '" . $tmpSubstrLeft . "[[:alnum:][:blank:]]?" . $tmpSubstrRight . "'"; endfor; // [4.] All letters have to be found in the same sort sequence // Example: "test" => ("blahxxxxxtyyyyyyyezzzzzzsxxxxtyyyyyblah") [look for letters "t","e","s","t"] $tmpRegexp = ""; for ($j = 0; $j < $phraseLen; $j++) : $char = substr($phrase, $j, 1); $tmpRegexp .= $char . "+[[:alnum:][:blank:]]*"; endfor; // $regexpSQLArray[$i][] = " REGEXP '" . $tmpRegexp . "'"; // SELECT cmp_comp, SOUNDEX(cmp_comp) FROM company WHERE (CAST(SUBSTRING(SOUNDEX(cmp_comp),2) AS UNSIGNED) - CAST(SUBSTRING(SOUNDEX('Frie'),2) AS UNSIGNED)) < 10 $numOfChecksArray[$i] = count($regexpSQLArray[$i]); endif; endfor; // ---- DEBUG ---- if ($debugON) : echo "regexpSQLArray:
"; print_r($regexpSQLArray); echo "

"; endif; // --------------- // Function to generate the WHERE clause according to the phrases function generateWhereClause ($addedVector = array()) { global $whereClauseArray, $weightArray, $compareField; $whereClauseArrayLen = count($whereClauseArray); $addedVectorLen = count($addedVector); $newWhereClauseArray = array(); $newWeightArray = array(); if ($addedVectorLen > 0) : $count = 0; if ($whereClauseArrayLen > 0) : for ($i = 0; $i < $whereClauseArrayLen; $i++) : for ($j = 0; $j < $addedVectorLen; $j++) : $newWhereClauseArray[$count] = $whereClauseArray[$i]; array_push($newWhereClauseArray[$count], $compareField . $addedVector[$j] . " "); $newWeightArray[$count] = max($weightArray[$i] + (10 - $j), 50); $count++; endfor; endfor; else : for ($j = 0; $j < $addedVectorLen; $j++) : $newWhereClauseArray[$count] = array($compareField . $addedVector[$j] . " "); $newWeightArray[$count] = (10 - $j); $count++; endfor; endif; endif; $whereClauseArray = $newWhereClauseArray; $weightArray = $newWeightArray; } // Generate WHERE clause array $whereClauseArray = array(); $weightArray = array(); $numOfChecksArrayLen = count($numOfChecksArray); if ($numOfChecksArrayLen > 0) : for ($k = 0; $k < $numOfChecksArrayLen; $k++) : generateWhereClause($regexpSQLArray[$k]); endfor; endif; // Extend WHERE clause for exact match for ($i = 0; $i < $wordArrayLen; $i++) : $whereClauseArrayLen = count($whereClauseArray); $whereClauseArray[$whereClauseArrayLen][0] = $compareField . "= '" . $wordArray[$i] . "' "; $weightArray[$whereClauseArrayLen] = 100; endfor; // Extend WHERE clause for exact match by prefix for ($i = 0; $i < $wordArrayLen; $i++) : $whereClauseArrayLen = count($whereClauseArray); $whereClauseArray[$whereClauseArrayLen][0] = $compareField . "LIKE '" . $wordArray[$i] . "%' "; $weightArray[$whereClauseArrayLen] = 70; endfor; // Extend WHERE clause for exact match by prefix AND combination with other expressions for ($i = 0; $i < $wordArrayLen; $i++) : $numOfChecksArrayLen = count($numOfChecksArray); if ($numOfChecksArrayLen > 0) : for ($k = 0; $k < $numOfChecksArrayLen; $k++) : if ($i != $k) : $addedVectorLen = count($regexpSQLArray[$k]); for ($j = 0; $j < $addedVectorLen; $j++) : $whereClauseArrayLen = count($whereClauseArray); $whereClauseArray[$whereClauseArrayLen][0] = $compareField . "LIKE '" . $wordArray[$i] . "%' "; $whereClauseArray[$whereClauseArrayLen][1] = $compareField . $regexpSQLArray[$k][$j] . " "; $weightArray[$whereClauseArrayLen] = 90 - $j; endfor; endif; endfor; endif; endfor; // --------------- if ($debugON) : echo "whereClauseArray:
"; print_r($whereClauseArray); echo "

"; echo "weightArray:
"; print_r($weightArray); echo "

"; endif; // --------------- // Iterate WHERE clause array and define SQL statements $output = ""; $sqlStatementArray = array(); $whereClauseArrayLen = count($whereClauseArray); if ($whereClauseArrayLen > 0) : // Iterate word vector (each component is a word) with different number of check statements for ($k = 0; $k < $whereClauseArrayLen; $k++) : // Generate statements according to the regular expressions $whereClause = implode(" AND ", $whereClauseArray[$k]); $whereClause .= " AND "; if ($compareType == "cr") : $sqlStatementArray[] = "SELECT cmp.cmp_id, cmp.cmp_comp, cmp.cmp_comp2, cr.cr_eid, hq.hq_mnemonic, '" . $weightArray[$k] . "' AS mysort" . " FROM company AS cmp, courier AS cr, user AS usr, address AS ad, headquarters AS hq" . " WHERE " . $whereClause . " cmp.cmp_id = cr.cmp_id AND" . " cr.usr_id = usr.usr_id AND" . " cmp.ad_id = ad.ad_id AND" . " cr.hq_id = hq.hq_id" . $whereClauseHQ; else : // "cs" $sqlStatementArray[] = "SELECT cmp.cmp_id, cmp.cmp_comp, cmp.cmp_comp2, cs.cs_eid, hq.hq_mnemonic, '" . $weightArray[$k] . "' AS mysort" . " FROM company AS cmp, customer AS cs, employee AS emp, user AS usr, address AS ad, headquarters AS hq" . " WHERE " . $whereClause . " cmp.cmp_id = cs.cmp_id AND" . " cs.cs_admin = emp.emp_id AND" . " emp.usr_id = usr.usr_id AND" . " cmp.ad_id = ad.ad_id AND" . " cs.hq_id = hq.hq_id" . $whereClauseHQ; endif; endfor; // Generate UNION statement $sqlQuery = "(" . implode(") UNION (", $sqlStatementArray) . ") ORDER BY mysort DESC, cmp_comp"; // ---- DEBUG ---- if ($debugON) : echo "

" . $sqlQuery . "

"; endif; // --------------- $result = $db->query($sqlQuery); if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage()); // Generate unique array $rowArray = array(); while ($row = $result->fetch_assoc()): if (!is_array($rowArray[$row["cs_eid"]])) : $rowArray[$row["cs_eid"]] = array($row["mysort"], $row["cmp_comp"], $row["cmp_comp2"], $row["cs_eid"], $row["cmp_id"]); endif; endwhile; $result->free(); // Output /* $keyArray = array_keys($rowArray); $keyArrayLen = count($keyArray); for ($j = 0; $j < $regexpSQLArrayLen; $j++) : for ($i = 0; $i < $keyArrayLen; $i++) : if ($rowArray[$keyArray[$i]][0] == $j) : echo $rowArray[$keyArray[$i]][0] . "    " . $rowArray[$keyArray[$i]][1] . "    " . $rowArray[$keyArray[$i]][2] . "    " . $rowArray[$keyArray[$i]][3] . "    " . $rowArray[$keyArray[$i]][4] . "
"; endif; endfor; endfor; */ $keyArray = array_keys($rowArray); $keyArrayLen = count($keyArray); $output .= ""; for ($i = 0; $i < $keyArrayLen; $i++) : $output .= ""; $output .= ""; $output .= ""; endfor; $output .= "
" . $rowArray[$keyArray[$i]][3] . "" . $rowArray[$keyArray[$i]][1] . "" . $rowArray[$keyArray[$i]][2] . "
"; endif; ?> <?php echo $pageTitel ?>