322 lines
8.9 KiB
PHP
322 lines
8.9 KiB
PHP
<?php
|
|
|
|
class ItemSearch {
|
|
|
|
private $config;
|
|
protected $base_object;
|
|
protected $db;
|
|
|
|
private $searchData = array();
|
|
|
|
|
|
public function __construct($base_object) {
|
|
$this->config = $base_object->config;
|
|
$this->db = $base_object->db;
|
|
$this->base_object = $base_object;
|
|
}
|
|
|
|
public function searchItems($searchString) {
|
|
$timer_start = microtime(true);
|
|
|
|
// $searchString_trimmed = trim($searchString);
|
|
// $searchString_filtered = preg_replace("@[^\p{L}\p{N}]@sui", '', $searchString_trimmed);
|
|
|
|
$results = false;
|
|
if (ctype_digit($searchString)) {
|
|
$results = $this->_getItemByNumber($searchString);
|
|
}
|
|
if (!$results) {
|
|
$results = $this->_searchItemWithinItemsAndAssociations('', $searchString);
|
|
}
|
|
if (!$results) {
|
|
$results = $this->_getItembyKremplNumber($searchString);
|
|
}
|
|
if (!$results) {
|
|
$results = Array();
|
|
}
|
|
|
|
$running_time = microtime(true) - $timer_start;
|
|
|
|
$result = array(
|
|
'searchstring' => $searchString,
|
|
'results' => count($results),
|
|
'queryTime' => $running_time,
|
|
'data' => $results
|
|
);
|
|
|
|
$this->searchData = array(
|
|
'string1' => $searchManufacturer,
|
|
'string2' => $searchString,
|
|
'used1' => $searchManufacturer_ready,
|
|
'used2' => $searchString_ready,
|
|
'results' => count($results)
|
|
);
|
|
|
|
return $result;
|
|
}
|
|
|
|
private function _searchItemWithinItemsAndAssociations($searchManufacturer, $searchString, $idsOnly = false, $searchManufacturer_filtered = false, $searchString_filtered = false, $searchRefinement_filtered = false) {
|
|
$queries = $this->_queryLike($searchManufacturer_filtered, $searchString);
|
|
|
|
$results = array();
|
|
|
|
$idArray = array();
|
|
foreach ($queries as $query) {
|
|
|
|
$rs = $this->db->query($query);
|
|
while ($I = $rs->fetch_object()) {
|
|
if ($I->anumber) {
|
|
$results[$I->anumber] = $I->anumber;
|
|
$idArray[] = $I->anumber;
|
|
}
|
|
}
|
|
}
|
|
|
|
//$idArray = $this->_getIdsByNumbers($results, true, $searchRefinement_filtered);
|
|
|
|
|
|
return $idArray;
|
|
}
|
|
|
|
private function _getIdsByNumbers($numbers, $withAlternatives = true, $refinement = false) {
|
|
if (is_array($numbers) && count($numbers) > 0) {
|
|
$numberList = implode("','", $numbers);
|
|
|
|
$queryAddAlternatives = '';
|
|
if ($withAlternatives) {
|
|
$queryAddAlternatives = "
|
|
OR
|
|
attribute_17 IN ('" . $numberList . "')
|
|
";
|
|
}
|
|
|
|
$queryAddRefinement = '';
|
|
if ($refinement) {
|
|
$refinement = $this->db->real_escape_string($refinement);
|
|
$queryAddRefinement = "
|
|
AND (
|
|
name LIKE '%" . $refinement . "%'
|
|
OR
|
|
detail_description LIKE '%" . $refinement . "%'
|
|
OR
|
|
detail_description LIKE '%" . htmlentities($refinement, null, 'UTF-8') . "%'
|
|
OR
|
|
attribute_1 LIKE '%" . $refinement . "%'
|
|
OR
|
|
attribute_9 LIKE '%" . $refinement . "%'
|
|
OR
|
|
attribute_20 LIKE '%" . $refinement . "%'
|
|
OR
|
|
attribute_6 LIKE '%" . $refinement . "%'
|
|
)
|
|
";
|
|
$queryAddRefinement = "
|
|
AND (
|
|
name LIKE '%" . $refinement . "%'
|
|
OR
|
|
attribute_1 LIKE '%" . $refinement . "%'
|
|
OR
|
|
attribute_9 LIKE '%" . $refinement . "%'
|
|
OR
|
|
attribute_20 LIKE '%" . $refinement . "%'
|
|
OR
|
|
attribute_6 LIKE '%" . $refinement . "%'
|
|
)
|
|
";
|
|
}
|
|
|
|
// $query = "
|
|
// SELECT
|
|
// id
|
|
// FROM
|
|
// items
|
|
// WHERE (
|
|
// number IN ('" . $numberList . "') OR
|
|
// attribute_7 IN ('" . $numberList . "')
|
|
// " . $queryAddAlternatives . "
|
|
// )
|
|
// " . $queryAddRefinement . "
|
|
// ";
|
|
$query = "
|
|
SELECT
|
|
id
|
|
FROM
|
|
items
|
|
WHERE
|
|
attribute_7 IN ('" . $numberList . "')
|
|
";
|
|
$rs = $this->db->query($query);
|
|
// echo '<pre>';echo $query;exit();
|
|
$idArray = array();
|
|
while ($I = $rs->fetch_object()) {
|
|
$idArray[$I->id] = $I->id;
|
|
}
|
|
|
|
return $idArray;
|
|
}
|
|
|
|
return array();
|
|
}
|
|
|
|
private function _queryLike($searchManufacturer_filtered, $searchString_filtered) {
|
|
$searchManufacturer_filtered = $this->db->real_escape_string($searchManufacturer_filtered);
|
|
$searchString_filtered = $this->db->real_escape_string($searchString_filtered);
|
|
|
|
$queries = array();
|
|
|
|
if (ctype_alpha($searchString_filtered)) {
|
|
$queries[] = "
|
|
SELECT
|
|
id AS anumber
|
|
FROM
|
|
items
|
|
WHERE
|
|
active = 1
|
|
AND
|
|
parent_id = 0
|
|
AND (
|
|
REPLACE(REPLACE(REPLACE(vendor_item_number, ' ', ''), '/', ''), '-', '') = '" . $searchString_filtered . "'
|
|
OR
|
|
REPLACE(REPLACE(REPLACE(name, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
OR
|
|
-- mögliche Aufschrift
|
|
REPLACE(REPLACE(REPLACE(attribute_4, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
OR
|
|
-- Aufdrucke
|
|
REPLACE(REPLACE(REPLACE(attribute_9, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
OR
|
|
-- Merkmale
|
|
REPLACE(REPLACE(REPLACE(attribute_8, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
OR
|
|
-- Internes DB-Feld
|
|
REPLACE(REPLACE(REPLACE(attribute_20, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
OR
|
|
-- Synonyme
|
|
REPLACE(REPLACE(REPLACE(attribute_6, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
)
|
|
GROUP BY
|
|
number
|
|
LIMIT 150
|
|
";
|
|
}
|
|
else {
|
|
|
|
|
|
if (!ctype_digit($searchString_filtered) || (ctype_digit($searchString_filtered) && strlen($searchString_filtered) >= 7)) {
|
|
$queries[] = "
|
|
SELECT
|
|
DISTINCT i.id AS anumber
|
|
FROM
|
|
krempl_geraete kg
|
|
LEFT JOIN krempl_artikelgeraet ka on ka.geraet=kg.id
|
|
LEFT JOIN items i on i.krempl_id=ka.ersatzteil
|
|
WHERE
|
|
REPLACE(REPLACE(REPLACE(kg.nr, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
OR
|
|
REPLACE(REPLACE(REPLACE(kg.bezeichnung1, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
LIMIT 150
|
|
";
|
|
}
|
|
|
|
$queries[] = "
|
|
SELECT
|
|
DISTINCT i.id AS anumber
|
|
FROM
|
|
passendwie_2018 pw
|
|
LEFT JOIN
|
|
items i on i.attribute_7=pw.navisionid
|
|
WHERE
|
|
REPLACE(REPLACE(REPLACE(bestellcode, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
LIMIT 150
|
|
";
|
|
|
|
$queries[] = "
|
|
SELECT
|
|
id AS anumber
|
|
FROM
|
|
items
|
|
WHERE
|
|
active = 1
|
|
AND
|
|
REPLACE(REPLACE(REPLACE(attribute_20, ' ', ''), '/', ''), '-', '') LIKE '%" . $searchString_filtered . "%'
|
|
GROUP BY
|
|
number
|
|
LIMIT 50
|
|
";
|
|
|
|
}
|
|
return $queries;
|
|
}
|
|
|
|
private function _getItemByNumber($searchString_ready) {
|
|
$query = "
|
|
SELECT
|
|
id
|
|
FROM
|
|
items
|
|
WHERE
|
|
number = '" . $this->db->real_escape_string($searchString_ready) . "' OR
|
|
ean_code = '" . $this->db->real_escape_string($searchString_ready) . "' OR
|
|
name LIKE '%" . $this->db->real_escape_string($searchString_ready) . "%' OR
|
|
attribute_20 like '%" . $this->db->real_escape_string($searchString_ready) . "%' OR
|
|
attribute_7 = '" . $this->db->real_escape_string($searchString_ready) . "'
|
|
LIMIT 150
|
|
";
|
|
$rs = $this->db->query($query);
|
|
|
|
$idArray = array();
|
|
if ($rs->num_rows > 0) {
|
|
while ($I = $rs->fetch_object()) {
|
|
$idArray[$I->id] = $I->id;
|
|
}
|
|
return $idArray;
|
|
} else {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
private function _getItembyKremplNumber($searchString_ready) {
|
|
$query = "
|
|
SELECT i.id FROM krempl_artikelgeraet_22 ka
|
|
LEFT JOIN krempl_geraete_22 kg ON kg.id = ka.geraet
|
|
LEFT JOIN krempl_id2022 ki ON ersatzteil = ki.id
|
|
LEFT JOIN items i ON ki.navisionid = i.attribute_7
|
|
WHERE not isnull(nr) AND nr LIKE '%".$this->db->real_escape_string($searchString_ready)."%' LIMIT 150;
|
|
";
|
|
$rs = $this->db->query($query);
|
|
|
|
$idArray = array();
|
|
if ($rs->num_rows > 0) {
|
|
while ($I = $rs->fetch_object()) {
|
|
if (isset($I->id))
|
|
$idArray[$I->id] = $I->id;
|
|
}
|
|
return $idArray;
|
|
} else {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function makeHistory() {
|
|
$indexString = $this->searchData['used1'] . $this->searchData['used2'];
|
|
|
|
$query = "
|
|
INSERT INTO
|
|
search_history (id, string1, string2, used1, used2, results)
|
|
VALUES (
|
|
'" . $this->db->real_escape_string($indexString) . "',
|
|
'" . $this->db->real_escape_string($this->searchData['string1']) . "',
|
|
'" . $this->db->real_escape_string($this->searchData['string2']) . "',
|
|
'" . $this->db->real_escape_string($this->searchData['used1']) . "',
|
|
'" . $this->db->real_escape_string($this->searchData['used2']) . "',
|
|
'" . $this->db->real_escape_string($this->searchData['results']) . "'
|
|
)
|
|
ON DUPLICATE KEY
|
|
UPDATE
|
|
cnt = cnt + 1,
|
|
results = VALUES(results)
|
|
";
|
|
|
|
$this->db->query($query);
|
|
}
|
|
} |