public static function find2($query, $offset) { if (!strlen($query)) { return false; } $words = WordStatTable::parseQuery($query); return WordChainTable::search($words, $offset); }
public static function search($words) { $dbConnection = Main\HttpApplication::getConnection(); $dbHelper = Main\HttpApplication::getConnection()->getSqlHelper(); $wordStatTableName = WordStatTable::getTableName(); $preparedLike = array(); foreach ($words as $word) { $preparedLike[] = "%TABLE_NAME%.WORD like '" . $dbHelper->forSql($word) . "%'"; } $preparedLike = implode(' or ', $preparedLike); $sql = "\n\t\t\tselect C.*, WS_CITY.WORD as CWORD, WS_VILLAGE.WORD as VWORD, WS_STREET.WORD as SWORD from " . static::getTableName() . " C\n\n\t\t\t\tinner join b_sale_loc_word_stat WS_STREET on \n\n\t\t\t\t\t(\n\t\t\t\t\t\tWS_STREET.TYPE_ID = '7'\n\t\t\t\t\t\tand\n\t\t\t\t\t\tWS_STREET.LOCATION_ID = C.STREET_ID\n\t\t\t\t\t\tand\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(" . str_replace(array('%TABLE_NAME%'), array('WS_STREET'), $preparedLike) . ")\n\t\t\t\t\t\t\tor\n\t\t\t\t\t\t\t(WS_STREET.LOCATION_ID = '0')\n\t\t\t\t\t\t)\n\t\t\t\t\t)\n\n\t\t\t\tinner join b_sale_loc_word_stat WS_VILLAGE on \n\n\t\t\t\t\t(\n\t\t\t\t\t\tWS_VILLAGE.TYPE_ID = '6'\n\t\t\t\t\t\tand\n\t\t\t\t\t\tWS_VILLAGE.LOCATION_ID = C.VILLAGE_ID\n\t\t\t\t\t\tand\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(" . str_replace(array('%TABLE_NAME%'), array('WS_VILLAGE'), $preparedLike) . ")\n\t\t\t\t\t\t\tor\n\t\t\t\t\t\t\t(WS_VILLAGE.LOCATION_ID = '0')\n\t\t\t\t\t\t)\n\t\t\t\t\t)\n\n\t\t\t\tinner join b_sale_loc_word_stat WS_CITY on \n\n\t\t\t\t\t(\n\t\t\t\t\t\tWS_CITY.TYPE_ID = '3'\n\t\t\t\t\t\tand\n\t\t\t\t\t\tWS_CITY.LOCATION_ID = C.CITY_ID\n\t\t\t\t\t\tand\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(" . str_replace(array('%TABLE_NAME%'), array('WS_CITY'), $preparedLike) . ")\n\t\t\t\t\t\t\tor\n\t\t\t\t\t\t\t(WS_CITY.LOCATION_ID = '0')\n\t\t\t\t\t\t)\n\t\t\t\t\t)\n\n\t\t\torder by C.TYPE_SORT desc\n\t\t\tlimit 5\n\t\t"; /* $sql = " select * from ".static::getTableName()." C where ( C.CITY_ID = 0 or C.CITY_ID in ( select LOCATION_ID from b_sale_loc_word_stat where TYPE_ID = 3 and (".$preparedLike.") ) ) and ( C.VILLAGE_ID = 0 or C.VILLAGE_ID in ( select LOCATION_ID from b_sale_loc_word_stat where TYPE_ID = 6 and (".$preparedLike.") ) ) and ( C.STREET_ID = 0 or C.STREET_ID in ( select LOCATION_ID from b_sale_loc_word_stat where TYPE_ID = 7 and (".$preparedLike.") ) ) order by C.TYPE_SORT desc limit 5 "; */ print '<pre>'; print_r($sql); print '</pre>'; return $dbConnection->query($sql); }
public static function reInitData($parameters = array()) { static::createTables(); $offset = 0; $stat = array(); $types = array(); $typeSort = array(); $res = Location\TypeTable::getList(array('select' => array('ID', 'CODE', 'SORT'))); $allowedTypes = array('REGION', 'SUBREGION', 'CITY', 'VILLAGE', 'STREET'); while ($item = $res->fetch()) { if (in_array($item['CODE'], $allowedTypes)) { $types[$item['CODE']] = $item['ID']; } $typeSort[$item['ID']] = $item['SORT']; } $typesBack = array_flip($types); //print_r($types); //_print_r($typeSort); $wordChain = array(); $pathChain = array(); //_dump_r('GO!'); $prevDepth = 0; while (true) { $res = Location\LocationTable::getList(array('select' => array('ID', 'TYPE_ID', 'LNAME' => 'NAME.NAME', 'DEPTH_LEVEL', 'SORT'), 'filter' => array('=TYPE_ID' => array_values($types), '=NAME.LANGUAGE_ID' => LANGUAGE_ID), 'order' => array('LEFT_MARGIN' => 'asc'), 'limit' => self::STEP_SIZE, 'offset' => $offset)); $cnt = 0; while ($item = $res->fetch()) { if ($item['DEPTH_LEVEL'] < $prevDepth) { //print('DROP to '.$item['DEPTH_LEVEL'].'<br />'); // drop chain to DEPTH_LEVEL inclusively $newWC = array(); $newPC = array(); foreach ($wordChain as $dl => $name) { if ($dl >= $item['DEPTH_LEVEL']) { break; } $newWC[$dl] = $name; } $wordChain = $newWC; foreach ($pathChain as $dl => $id) { if ($dl >= $item['DEPTH_LEVEL']) { break; } $newPC[$dl] = $id; } $pathChain = $newPC; } $wordChain[$item['DEPTH_LEVEL']] = $item['LNAME']; $pathChain[$item['DEPTH_LEVEL']] = array('TYPE' => $item['TYPE_ID'], 'ID' => $item['ID']); $prevDepth = $item['DEPTH_LEVEL']; //print($item['DEPTH_LEVEL'].' - '.implode(' ', WordStatTable::parseQuery(implode(' ', $wordChain))).'<br />'); $parsed = WordStatTable::parseQuery(implode(' ', $wordChain)); $wordMap = array(); $i = 1; foreach ($parsed as $word) { $wordMap['W_' . $i] = $word; $i++; } $pathMap = array(); foreach ($pathChain as $elem) { $pathMap[$typesBack[$elem['TYPE']] . '_ID'] = $elem['ID']; } $data = array_merge($wordMap, $pathMap, array('LOCATION_ID' => $item['ID'], 'TYPE_ID' => $item['TYPE_ID'], 'TYPE_SORT' => $typeSort[$item['TYPE_ID']], 'SORT' => $item['SORT'], 'WORD_COUNT' => count($wordMap))); //print('<pre>'); //print('</pre>'); try { static::add($data); } catch (\Exception $e) { _dump_r('Cant add ' . implode(' ', $wordChain) . ' (' . count($wordMap) . ')<br />'); // duplicate or smth } $cnt++; } if (!$cnt) { break; } $offset += self::STEP_SIZE; } }