public function __construct()
{
$conn = new Connection();
$conn->setConnectionParams('127.0.0.1', 9307);
$this->conn = $conn;
SphinxQL::create($this->conn)->query('TRUNCATE RTINDEX rt')->execute();
}
public function search_products($search_string)
{
$sphinxQL = new Connection();
$sphinxQL->setConnectionParams(SPHINX_IP, SPHINX_PORT);
$match_search_string = $search_string;
$select = "`id_m_c_products`, `in_stock` AS order_in_stock, weight()";
$this->load->model('warehouse/mwarehouses');
if ($wh_id = $this->mwarehouses->get_shop_wh_id()) {
$select = "`id_m_c_products`, IF (qty, in_stock, 0) AS order_in_stock, weight()";
}
$search_query = "SELECT " . $select . "\n\t\tFROM `" . self::SPHINX_INDEX . "`\n\t\tWHERE `id_users` = " . $this->id_users . " AND `status` = 1 AND MATCH('(" . $match_search_string . ") | (@sku *" . $match_search_string . "*)')\n\t\tGROUP BY `id_m_c_products`\n\t\tORDER BY `order_in_stock` DESC, weight() DESC\n\t\tLIMIT 0, 2000\n\t\tOPTION max_matches = 2000,\n\t\tfield_weights = (name = 100, short_description = 30, full_description = 20, seo_title = 50, seo_description = 30, seo_keywords = 100, sku = 200)";
$sphinx_result = $sphinxQL->query($search_query);
$search_count = count($sphinx_result);
$this->load->model('catalogue/mproducts');
$products_settings = $this->mproducts->get_settings();
$search_limit = $products_settings['products_count_to_page'];
list($pages, $limit_data) = $this->set_limit($search_count, $search_limit, $search_string);
$products_array = array();
$products_id = array();
$seo = array('seo_title' => $match_search_string, 'seo_description' => $match_search_string);
$this->template->set_TDK($seo);
if ($search_count > 0) {
for ($i = $limit_data[0]; $i < $limit_data[1]; $i++) {
$products_id[$sphinx_result[$i]['id_m_c_products']] = $sphinx_result[$i]['id_m_c_products'];
}
$products_temp = $this->mproducts->get_search_products_collection($products_id);
foreach ($products_id as $key => $ms) {
if (isset($products_temp['products'][$key])) {
$products_array[] = $products_temp['products'][$key];
}
}
}
return array('products' => $products_array, 'pages' => $pages, 'search_keywords' => $match_search_string);
}
public function sphinx_update_pr_qty_array($wh_id, $pr_array)
{
$this->db->select("`" . self::ID_WH . "`, `i_s_wh`")->from("`" . self::WH . "`")->where("`" . self::ID_WH . "`", $wh_id)->where("`" . self::ID_USERS . "`", $this->id_users)->limit(1);
$result = $this->db->get()->row_array();
if ($result['i_s_wh'] == 1) {
$sphinxQL = new Connection();
$sphinxQL->setConnectionParams(SPHINX_IP, SPHINX_PORT);
foreach ($pr_array as $pr_id => $ms) {
$sphinxQL->query("UPDATE `" . self::SPHINX_INDEX . "` SET\n\t\t\t\t\t`qty` = " . $ms['qty'] . "\n\t\t\t\t\tWHERE `" . self::ID_PR . "` = " . $pr_id);
}
}
}
/**
* Create and connect to Sphinx
*/
public function getSphinx()
{
$conn = new Connection();
$conn->setConnectionParams($this->Config->Sphinx->Host, $this->Config->Sphinx->Port);
return SphinxQL::create($conn);
}
public function change_pr_aditional_param($id, $field, $value = 1)
{
if (is_array($id)) {
$this->db->where("`" . self::ID_USERS . "`", $this->id_users)->where_in("`" . self::ID_PR . "`", $id);
} else {
$this->db->where("`" . self::ID_USERS . "`", $this->id_users)->where("`" . self::ID_PR . "`", $id);
}
$this->db->set("`" . $field . "`", $value)->update(self::PR);
if ($field == 'new') {
if (is_array($id)) {
$this->db->where("`" . self::ID_USERS . "`", $this->id_users)->where_in("`" . self::ID_PR . "`", $id);
} else {
$this->db->where("`" . self::ID_USERS . "`", $this->id_users)->where("`" . self::ID_PR . "`", $id);
}
$this->db->set("`" . $field . "`", $value)->update(self::PR_CAT);
}
if ($field == 'in_stock' || $field == 'new' || $field == 'sale' || $field == 'bestseller' || $field == 'status') {
$sphinxQL = new Connection();
$sphinxQL->setConnectionParams(SPHINX_IP, SPHINX_PORT);
$sphinx_query = "UPDATE `" . self::SPHINX_INDEX . "` SET\n \t\t\t`" . $field . "` = " . $value . "\n \t\t";
if (is_array($id)) {
$sphinx_query .= "WHERE `" . self::ID_PR . "` IN(" . implode(',', $id) . ")";
} else {
$sphinx_query .= "WHERE `" . self::ID_PR . "` = " . $id;
}
$sphinxQL->query($sphinx_query);
}
}
public function sphinx_import_dev()
{
$sphinx_conn = new Connection();
$sphinx_conn->setConnectionParams('127.0.0.1', 3312);
$this->db->select("`id_users`, `warehouse`")->from("`users`")->order_by("id_users");
$users = $this->db->get()->result_array();
foreach ($users as $usr) {
$wh_select_part = "0 AS qty, ";
$wh_join_part = "";
if ($usr['warehouse'] == 1) {
$this->db->select("`id_wh`")->from("`wh`")->where("`id_users`", $usr['id_users'])->where("`i_s_wh`", 1)->limit(1);
$wh = $this->db->get()->row_array();
if (count($wh) > 0) {
$wh_select_part = 'IF(WH_PR.`qty`, WH_PR.`qty`, 0) AS qty, ';
$wh_join_part = 'LEFT JOIN `wh_products` AS WH_PR ON WH_PR.`id_m_c_products` = B.`id_m_c_products` && WH_PR.`id_wh` = ' . $wh['id_wh'];
}
}
/*$start = 33000;
$step = 1000;
$end = $start + $step;
$this->db->select("count(*) AS COUNT")
->from("m_c_products");
$count = $this->db->get()->row_array();
//$count = $count['COUNT'];
$count = 70000;
$for_count = ceil($count/$step);*/
$sql_query = '
SELECT B.`id_m_c_products_description` as id, A.`id_m_c_products`, A.`sku`, A.`status`, A.`in_stock`, A.`new`, A.`bestseller`, A.`sale`, ' . $wh_select_part . 'A.`id_users`, B.`id_langs`, UNIX_TIMESTAMP(A.`create_date`) AS create_date ,
(SELECT `price` FROM `m_c_products_price` WHERE `id_m_c_products` = B.`id_m_c_products` ORDER BY `id_m_c_products` LIMIT 1) AS price,
B.`name` , B.`short_description` , B.`full_description` , B.`seo_title` , B.`seo_description` , B.`seo_keywords` ,
GROUP_CONCAT( DISTINCT(CAST( PNC.`id_m_c_categories` AS CHAR )) ORDER BY PNC.`id_m_c_categories` SEPARATOR "," ) AS id_m_c_categories,
GROUP_CONCAT( DISTINCT(CAST( PNT.`id_m_c_products_types` AS CHAR )) ORDER BY PNT.`id_m_c_products_types` SEPARATOR "," ) AS id_m_c_products_types,
GROUP_CONCAT( DISTINCT(CAST( PNT.`id_m_c_products_properties` AS CHAR )) ORDER BY PNT.`id_m_c_products_properties` SEPARATOR "," ) AS id_m_c_products_properties,
GROUP_CONCAT( DISTINCT(CAST( PNA.`id_m_c_products_attributes` AS CHAR )) ORDER BY PNA.`id_m_c_products_attributes` SEPARATOR "," ) AS id_m_c_products_attributes,
GROUP_CONCAT( DISTINCT(CAST( PNA.`id_m_c_products_attributes_options` AS CHAR )) ORDER BY PNA.`id_m_c_products_attributes_options` SEPARATOR "," ) AS id_m_c_products_attributes_options
FROM `m_c_products_description` AS B
INNER JOIN `m_c_products` AS A ON A.`id_m_c_products` = B.`id_m_c_products` && A.`id_users` = ' . $usr['id_users'] . '
' . $wh_join_part . '
LEFT JOIN `m_c_productsNcategories` AS PNC ON PNC.`id_m_c_products` = B.`id_m_c_products`
LEFT JOIN `m_c_productsNtypes` AS PNT ON PNT.`id_m_c_products` = B.`id_m_c_products`
LEFT JOIN `m_c_productsNattributes` AS PNA ON PNA.`id_m_c_products` = B.`id_m_c_products`
GROUP BY (B.`id_m_c_products_description`);';
$insert_part = "";
$DB = $this->db->query($sql_query);
$DB_res = $DB->result_array();
if (count($DB_res) > 0) {
foreach ($DB_res as $ms) {
$insert_part .= "(" . $ms['id'] . ", " . $ms['id_m_c_products'] . ", '" . $ms['sku'] . "', " . $ms['status'] . ", " . $ms['in_stock'] . ", " . $ms['new'] . ", " . $ms['bestseller'] . ", " . $ms['sale'] . ", " . $ms['qty'] . ", " . $ms['id_users'] . ", " . $ms['id_langs'] . ", " . $ms['create_date'] . ",\n\t\t\t\t\t" . floatval($ms['price']) . ",\n\t\t\t\t\t'" . addslashes(trim(htmlspecialchars(str_replace(' ', '', strip_tags($ms['name'])), ENT_COMPAT, 'UTF-8'))) . "',\n\t\t\t\t\t'" . addslashes(trim(htmlspecialchars(str_replace(' ', '', strip_tags($ms['short_description'])), ENT_COMPAT, 'UTF-8'))) . "',\n\t\t\t\t\t'" . addslashes(trim(htmlspecialchars(str_replace(' ', '', strip_tags($ms['full_description'])), ENT_COMPAT, 'UTF-8'))) . "',\n\t\t\t\t\t'" . addslashes($ms['seo_title']) . "',\n\t\t\t\t\t'" . addslashes($ms['seo_description']) . "',\n\t\t\t\t\t'" . addslashes($ms['seo_keywords']) . "',\n\t\t\t\t\t(" . $ms['id_m_c_categories'] . "), (" . $ms['id_m_c_products_types'] . "), (" . $ms['id_m_c_products_properties'] . "), (" . $ms['id_m_c_products_attributes'] . "), (" . $ms['id_m_c_products_attributes_options'] . ")),";
}
$insert_part = substr($insert_part, 0, strlen($insert_part) - 1);
echo $usr['id_users'] . "<BR>";
$sphinx_query = "INSERT INTO `gbc_users_m_c_products_index_rt` (`id`, `id_m_c_products`, `sku`, `status`, `in_stock`, `new`, `bestseller`, `sale`, `qty`, `id_users`, `id_langs`, `create_date`, `price`, `name`, `short_description`, `full_description`, `seo_title`, `seo_description`, `seo_keywords`, `id_m_c_categories`, `id_m_c_products_types`, `id_m_c_products_properties`, `id_m_c_products_attributes`, `id_m_c_products_attributes_options`) VALUES";
$sphinx_query .= $insert_part;
//echo $sphinx_query; exit;
$result = $sphinx_conn->query($sphinx_query);
}
}
$result = $sphinx_conn->query("SELECT count(*) FROM `gbc_users_m_c_products_index_rt` OPTION max_matches=50000");
echo var_dump($result);
}
