function fn_searchanise_get_filters_products_count($params) { if (AREA == 'A' || fn_se_check_disabled() || !empty($params['disable_searchanise']) || empty($params['q']) && fn_se_get_simple_setting('use_navigation') !== 'Y' || fn_se_get_import_status(fn_se_get_company_id(), CART_LANGUAGE) != 'done' || Registry::ifGet('addons.age_verification.status', 'D') == 'A' || Registry::ifGet('addons.vendor_data_premoderation.status', 'D') == 'A') { return fn_get_filters_products_count($params); } $key = 'pfilters_se_' . md5(serialize($params)); Registry::registerCache($key, array('products', 'product_features', 'product_filters', 'product_features_values', 'categories'), Registry::cacheLevel('user')); // Check exist cache. if (Registry::isExist($key) == true) { list($filters, $view_all) = Registry::get($key); } else { if (!fn_se_check_product_filter_block()) { return array(); } if (!empty($params['check_location'])) { // FIXME: this is bad style, should be refactored $valid_locations = array('index.index', 'products.search', 'categories.view', 'product_features.view'); if (!in_array($params['dispatch'], $valid_locations)) { return array(); } if ($params['dispatch'] == 'categories.view') { $params['simple_link'] = true; // this parameter means that extended filters on this page should be displayed as simple $params['filter_custom_advanced'] = true; // this parameter means that extended filtering should be stayed on the same page } else { if ($params['dispatch'] == 'product_features.view') { $params['simple_link'] = true; $params['features_hash'] = (!empty($params['features_hash']) ? $params['features_hash'] . '.' : '') . 'V' . $params['variant_id']; //$params['exclude_feature_id'] = db_get_field("SELECT feature_id FROM ?:product_features_values WHERE variant_id = ?i", $params['variant_id']); } $params['get_for_home'] = 'Y'; } } // hide filters block on the advanced search page if (!empty($params['skip_if_advanced']) && !empty($params['advanced_filter']) && $params['advanced_filter'] == 'Y') { return array(); } // End copied code. $get_custom = !empty($params['get_custom']); $received_facets = Registry::get('searchanise.received_facets'); $r_filters = $view_all = $variants_ids = $feature_variants = $fields_ids = $slider_vals = $category_facets = array(); $params['filters_category_id'] = empty($params['category_id']) ? 0 : $params['category_id']; if (is_null($received_facets) || $get_custom) { list($restrict_by, $query_by, $union) = fn_se_prepare_request_params($params); $request_params = array('items' => 'false', 'facets' => 'true', 'union' => $union, 'queryBy' => $query_by, 'restrictBy' => $restrict_by); $result = fn_searchanise_send_search_request($request_params); if (empty($result)) { return fn_get_filters_products_count($params); } $received_facets = $result['facets']; } if (empty($received_facets)) { // Nothing found return array(); } if (!empty($params['features_hash'])) { list(, , $fields_ids, $slider_vals) = fn_parse_features_hash($params['features_hash']); // // Get without // list($restrict_by, $query_by, $union) = fn_se_prepare_request_params(array_merge($params, array('features_hash' => ''))); $request_params = array('items' => 'false', 'facets' => 'true', 'union' => $union, 'queryBy' => $query_by, 'restrictBy' => $restrict_by); $result = fn_searchanise_send_search_request($request_params); if (empty($result)) { return fn_get_filters_products_count($params); } else { $category_facets = $result['facets']; } } $params_for_filters = array('get_variants' => true); if (!empty($params['item_ids'])) { $params_for_filters['filter_id'] = $params['item_ids']; } $params_for_filters = array_merge($params_for_filters, $params); list($filters, ) = fn_get_product_filters($params_for_filters); if (empty($filters)) { return array(array(), false); } $fields = fn_get_product_filter_fields(); foreach ($filters as $filter_id => $filter) { $r_facet = $c_facet = array(); foreach ($received_facets as $r) { $r_feature_id = str_replace('feature_', '', $r['attribute']); if (!empty($filter['feature_id']) && $r_feature_id == $filter['feature_id'] || !empty($filter['field_type']) && !empty($fields[$filter['field_type']]['db_field']) && $fields[$filter['field_type']]['db_field'] == $r_feature_id) { $r_facet = $r; break; } } if (empty($r_facet) && $get_custom == false) { unset($filters[$filter_id]); continue; } foreach ($category_facets as $c) { if ($c['attribute'] == $r_facet['attribute']) { $c_facet = $c; break; } } if ($filter['field_type'] == 'F') { $filters[$filter_id]['ranges'] = $filter['ranges'] = array('N' => array('range_id' => 0, 'range_name' => __('no'), 'products' => 0), 'Y' => array('range_id' => 1, 'range_name' => __('yes'), 'products' => 0)); } elseif ($filter['field_type'] == 'S' && (count($r_facet['buckets']) == 1 && $r_facet['buckets'][0]['value'] == 0) == false) { //skip if only default vendor (id=0) range passed $_companies = array(); $companies = db_get_hash_single_array("SELECT ?:companies.company_id, ?:companies.company FROM ?:companies WHERE status = 'A' ORDER BY ?:companies.company", array('company_id', 'company')); foreach ($companies as $company_id => $company) { $_companies[$company_id] = array('range_id' => $company_id, 'range_name' => $company, 'products' => 0); } $filters[$filter_id]['ranges'] = $filter['ranges'] = $_companies; } $ranges_count = 0; $tmp_ranges = array('selected' => array(), 'used' => array(), 'disabled' => array()); $filter['ranges'] = isset($filter['ranges']) ? $filter['ranges'] : array(); // // Speed up for many variants! // if (!empty($filter['feature_id']) && !in_array($filter['feature_type'], array('D', 'N', 'O'))) { $rr_ranges = array(); foreach ($r_facet['buckets'] as $r) { $rr_ranges[$r['value']] = $r; } $cc_ranges = array(); if (!empty($c_facet)) { foreach ($c_facet['buckets'] as $cc) { $cc_ranges[$cc['value']] = $cc; } } } foreach ($filter['ranges'] as $s_range_id => $s_range) { $r_range = array(); if (!empty($filter['feature_id']) && !in_array($filter['feature_type'], array('D', 'N', 'O'))) { // features with variants $r_range = isset($rr_ranges[$s_range['variant_id']]) ? $rr_ranges[$s_range['variant_id']] : array(); } elseif ($filter['field_type'] == 'F') { // Free shipping foreach ($r_facet['buckets'] as $r) { if ($r['value'] == $s_range_id) { $r_range = $r; break; } } } elseif ($filter['field_type'] == 'S') { // Vendors foreach ($r_facet['buckets'] as $r) { if ($r['value'] == $s_range_id) { unset($r['selected']); foreach ($fields_ids as $fr_id => $ff_type) { if ($ff_type == 'S' && $fr_id == $r['value']) { $r['selected'] = true; } } $r_range = $r; break; } } } else { // range foreach ($r_facet['buckets'] as $r) { if (abs($r['from'] - $s_range['from']) < 0.01 && abs($r['to'] - $s_range['to']) < 0.01) { $r_range = $r; break; } } } $range_id = isset($s_range['variant_id']) ? $s_range['variant_id'] : $s_range['range_id']; $new_range = array('feature_id' => $filter['feature_id'], 'range_id' => $range_id, 'range_name' => isset($s_range['variant']) ? $s_range['variant'] : $s_range['range_name'], 'feature_type' => $filter['feature_type'], 'filter_id' => $filter_id); if (!empty($r_range)) { $new_range['products'] = $r_range['count']; } if (empty($r_range['selected']) && fn_check_selected_filter($new_range['range_id'], !empty($new_range['feature_type']) ? $new_range['feature_type'] : '', $params, $filter['field_type'])) { $new_range['checked'] = true; } if (!empty($r_range['selected'])) { $is_select_found = true; $new_range['selected'] = true; $tmp_ranges['selected'][$range_id] = $new_range; } elseif (!empty($r_range)) { $tmp_ranges['used'][$range_id] = $new_range; } elseif (!empty($c_facet['buckets'])) { $c_range = false; if (!empty($filter['feature_id']) && !in_array($filter['feature_type'], array('D', 'N', 'O'))) { // features with variants $c_range = isset($cc_ranges[$s_range['variant_id']]) ? $cc_ranges[$s_range['variant_id']] : array(); } elseif ($filter['field_type'] == 'F') { // Free shipping foreach ($c_facet['buckets'] as $c) { if ($c['value'] == $s_range_id) { $c_range = $c; break; } } } elseif ($filter['field_type'] == 'S') { // Suppliers foreach ($c_facet['buckets'] as $c) { if ($c['value'] == $s_range_id) { $c_range = $c; break; } } } else { // range foreach ($c_facet['buckets'] as $c) { if (abs($c['from'] - $s_range['from']) < 0.01 && abs($c['to'] - $s_range['to']) < 0.01) { $c_range = $c; break; } } } if (!empty($c_range)) { $new_range['disabled'] = true; $tmp_ranges['disabled'][$range_id] = $new_range; } } $ranges_count++; } // \ by store filter ranges if (!empty($filters[$filter_id]['slider'])) { $is_select_found = true; $r_range = $r_facet['buckets'][0]; $r = array('min' => $r_range['from'], 'max' => $r_range['to']); $field_type = $filters[$filter_id]['field_type']; if ($field_type == 'P' && CART_SECONDARY_CURRENCY != CART_PRIMARY_CURRENCY) { $coef = Registry::get('currencies.' . CART_SECONDARY_CURRENCY . '.coefficient'); $r['min'] = floatval($r['min']) / floatval($coef); $r['max'] = floatval($r['max']) / floatval($coef); } $r['min'] = floor($r['min'] / $filters[$filter_id]['round_to']) * $filters[$filter_id]['round_to']; $r['max'] = ceil($r['max'] / $filters[$filter_id]['round_to']) * $filters[$filter_id]['round_to']; if ($r['max'] - $r['min'] <= $filters[$filter_id]['round_to']) { $r['max'] = $r['min'] + $filters[$filter_id]['round_to']; } if (!empty($slider_vals[$field_type])) { if ($field_type == 'P' && $slider_vals['P'][2] != CART_SECONDARY_CURRENCY) { $prev_coef = Registry::get('currencies.' . $slider_vals['P'][2] . '.coefficient'); $cur_coef = Registry::get('currencies.' . CART_SECONDARY_CURRENCY . '.coefficient'); $slider_vals['P'][0] = floor(floatval($slider_vals['P'][0]) * floatval($prev_coef) / floatval($cur_coef)); $slider_vals['P'][1] = ceil(floatval($slider_vals['P'][1]) * floatval($prev_coef) / floatval($cur_coef)); } $r['left'] = $slider_vals[$field_type][0]; $r['right'] = $slider_vals[$field_type][1]; if ($r['left'] < $r['min']) { $r['left'] = $r['min']; } if ($r['left'] > $r['max']) { $r['left'] = $r['max']; } if ($r['right'] > $r['max']) { $r['right'] = $r['max']; } if ($r['right'] < $r['min']) { $r['right'] = $r['min']; } if ($r['right'] < $r['left']) { $tmp = $r['right']; $r['right'] = $r['left']; $r['left'] = $tmp; } $r['left'] = floor($r['left'] / $filters[$filter_id]['round_to']) * $filters[$filter_id]['round_to']; $r['right'] = ceil($r['right'] / $filters[$filter_id]['round_to']) * $filters[$filter_id]['round_to']; } $filters[$filter_id]['range_values'] = $r; } else { if (empty($tmp_ranges)) { unset($filters[$filter_id]); } else { if (!empty($tmp_ranges['selected'])) { $filters[$filter_id]['selected_ranges'] = $tmp_ranges['selected']; } $filters[$filter_id]['ranges'] = $tmp_ranges['used'] + $tmp_ranges['disabled']; // Calculate number of ranges and compare with displaying count if (empty($params['get_all'])) { if (!empty($filters[$filter_id]['ranges'])) { $count = count($filters[$filter_id]['ranges']); } else { $count = 1; } if ($count > $filters[$filter_id]['display_more_count']) { $filters[$filter_id]['more_ranges'] = array_slice($filters[$filter_id]['ranges'], 0, $filters[$filter_id]['display_more_count'], true); $count = $filters[$filter_id]['display_more_count']; $filters[$filter_id]['more_cut'] = true; } else { $filters[$filter_id]['more_ranges'] = $filters[$filter_id]['ranges']; } $filters[$filter_id]['ranges'] = array_slice($filters[$filter_id]['more_ranges'], 0, $filters[$filter_id]['display_count'], true); $filters[$filter_id]['more_ranges'] = array_slice($filters[$filter_id]['more_ranges'], $filters[$filter_id]['display_count'], $count, true); } if (!empty($params['simple_link']) && $filters[$filter_id]['feature_type'] == 'E') { $filters[$filter_id]['simple_link'] = true; } } } } if (empty($is_select_found) && empty($params['skip_other_variants']) && !empty($params['features_hash'])) { fn_set_notification('W', __('text_nothing_found'), __('text_nothing_found_filter_message')); if (defined('AJAX_REQUEST')) { die; } elseif (!empty($_SERVER['HTTP_REFERER'])) { fn_redirect($_SERVER['HTTP_REFERER'], true); } else { $_params = $params; $_params['skip_advanced_variants'] = true; $_params['only_selected'] = true; if (!empty($params['features_hash']) && empty($params['skip_advanced_variants'])) { list(, , , , $field_ranges_ids) = fn_parse_features_hash($params['features_hash']); } list($_f, $_view_all) = fn_get_filters_products_count($_params); foreach ($_f as $filter_id => $filter) { if (!empty($field_range_values[$filter_id])) { $_f[$filter_id]['range_values'] = $field_range_values[$filter_id]; } } return array($_f, $_view_all); } } // Adding to the cache. Registry::set($key, array($filters, $view_all)); } return array($filters, $view_all); }
/** * Gets product filters with ranges * * @param array $params Products filter search params * @return array Products and filters data * array $filters - Product filters data * array $view_all - All ranges filters */ function fn_get_filters_products_count($params = array()) { /** * Change parameters for getting product filters count * * @param array $params Products filter search params */ fn_set_hook('get_filters_products_count_pre', $params); $key = 'pfilters_' . md5(serialize($params)); Registry::registerCache($key, array('products', 'product_features', 'product_filters', 'product_features_values', 'categories'), Registry::cacheLevel('user')); if (Registry::isExist($key) == false) { if (!empty($params['check_location'])) { // FIXME: this is bad style, should be refactored $valid_locations = array('index.index', 'products.search', 'categories.view', 'product_features.view'); if (!in_array($params['dispatch'], $valid_locations)) { return array(); } if ($params['dispatch'] == 'categories.view') { $params['simple_link'] = true; // this parameter means that extended filters on this page should be displayed as simple $params['filter_custom_advanced'] = true; // this parameter means that extended filtering should be stayed on the same page } else { if ($params['dispatch'] == 'product_features.view') { $params['simple_link'] = true; $params['features_hash'] = (!empty($params['features_hash']) ? $params['features_hash'] . '.' : '') . 'V' . $params['variant_id']; //$params['exclude_feature_id'] = db_get_field("SELECT feature_id FROM ?:product_features_values WHERE variant_id = ?i", $params['variant_id']); } $params['get_for_home'] = 'Y'; } } // hide filters block on the advanced search page if (!empty($params['skip_if_advanced']) && !empty($params['advanced_filter']) && $params['advanced_filter'] == 'Y') { return array(); } // Base fields for the SELECT queries $values_fields = array('?:product_features_values.feature_id', 'COUNT(DISTINCT ?:products.product_id) as products', '?:product_features_values.variant_id as range_id', '?:product_feature_variant_descriptions.variant as range_name', '?:product_features.feature_type', '?:product_filters.filter_id'); $ranges_fields = array('?:product_features_values.feature_id', 'COUNT(DISTINCT ?:products.product_id) as products', '?:product_filter_ranges.range_id', '?:product_filter_ranges_descriptions.range_name', '?:product_filter_ranges.filter_id', '?:product_features.feature_type'); $condition = $where = $join = $filter_vq = $filter_rq = ''; $advanced_variant_ids = $ranges_ids = $field_filters = $feature_ids = $field_ranges_ids = $field_ranges_counts = $field_range_values = $slider_vals = array(); if (!empty($params['features_hash']) && empty($params['skip_advanced_variants'])) { list($av_ids, $ranges_ids, $_field_ranges_ids, $slider_vals, $field_ranges_ids) = fn_parse_features_hash($params['features_hash']); $advanced_variant_ids = db_get_hash_multi_array("SELECT feature_id, variant_id FROM ?:product_feature_variants WHERE variant_id IN (?n)", array('feature_id', 'variant_id'), $av_ids); } if (!empty($params['category_id'])) { if (Registry::get('settings.General.show_products_from_subcategories') == 'Y') { $id_path = db_get_field("SELECT id_path FROM ?:categories WHERE category_id = ?i", $params['category_id']); $category_ids = db_get_fields("SELECT category_id FROM ?:categories WHERE id_path LIKE ?l", $id_path . '/%'); } else { $category_ids = array(); } $category_ids[] = $params['category_id']; $condition .= db_quote(" AND (categories_path = '' OR FIND_IN_SET(?i, categories_path))", $params['category_id']); $where .= db_quote(" AND ?:products_categories.category_id IN (?n)", $category_ids); } elseif (empty($params['get_for_home']) && empty($params['get_custom'])) { $condition .= " AND categories_path = ''"; } if (!empty($params['filter_id'])) { $condition .= db_quote(" AND ?:product_filters.filter_id = ?i", $params['filter_id']); } if (!empty($params['item_ids'])) { $condition .= db_quote(" AND ?:product_filters.filter_id IN (?a)", explode(',', $params['item_ids'])); } if (!empty($params['get_for_home'])) { $condition .= db_quote(" AND ?:product_filters.show_on_home_page = ?s", $params['get_for_home']); } if (!empty($params['exclude_feature_id'])) { $condition .= db_quote(" AND ?:product_filters.feature_id NOT IN (?n)", $params['exclude_feature_id']); } if (fn_allowed_for('ULTIMATE')) { $condition .= fn_get_company_condition('?:product_filters.company_id'); } $sf_fields = db_quote("?:product_filters.feature_id, ?:product_filters.filter_id, ?:product_filters.field_type, ?:product_filters.round_to, ?:product_filters.display, ?:product_filters.display_count, ?:product_filters.display_more_count, ?:product_filter_descriptions.filter, ?:product_features_descriptions.prefix, ?:product_features_descriptions.suffix"); $sf_join = db_quote("LEFT JOIN ?:product_filter_descriptions ON ?:product_filter_descriptions.filter_id = ?:product_filters.filter_id AND ?:product_filter_descriptions.lang_code = ?s LEFT JOIN ?:product_features_descriptions ON ?:product_features_descriptions.feature_id = ?:product_filters.feature_id AND ?:product_features_descriptions.lang_code = ?s", CART_LANGUAGE, CART_LANGUAGE); $sf_sorting = db_quote("position, filter"); /** * Change SQL parameters before select product filters * * @param array $sf_fields String of comma-separated SQL fields to be selected in an SQL-query * @param string $sf_join String with the complete JOIN information (JOIN type, tables and fields) for an SQL-query * @param string $condition String containing SQL-query condition possibly prepended with a logical operator (AND or OR) * @param string $sf_sorting String containing the SQL-query ORDER BY clause * @param array $params Products filter search params */ fn_set_hook('get_filters_products_count_before_select_filters', $sf_fields, $sf_join, $condition, $sf_sorting, $params); $limit = ''; if (fn_allowed_for('ULTIMATE:FREE')) { $limit = db_quote(' LIMIT ?i', FILTERS_LIMIT); } $filters = db_get_hash_array("SELECT {$sf_fields} FROM ?:product_filters {$sf_join} WHERE ?:product_filters.status = 'A' ?p ORDER BY {$sf_sorting} {$limit}", 'filter_id', $condition); $fields = fn_get_product_filter_fields(); if (empty($filters) && empty($params['advanced_filter'])) { return array(array(), false); } else { foreach ($filters as $k => $v) { if (!empty($v['feature_id'])) { // Feature filters $feature_ids[] = $v['feature_id']; } else { // Product field filters if (!empty($fields[$v['field_type']])) { $_field = $fields[$v['field_type']]; $field_filters[$v['filter_id']] = array_merge($v, $_field); $filters[$k]['condition_type'] = $_field['condition_type']; if (!empty($_field['slider'])) { $filters[$k]['slider'] = $_field['slider']; } } } } } // Variants if (!empty($advanced_variant_ids)) { $join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS advanced_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_advanced ON pfv_advanced.product_id = ?:products.product_id", CART_LANGUAGE); $where_and_conditions = array(); foreach ($advanced_variant_ids as $k => $variant_ids) { $where_or_conditions = array(); foreach ($variant_ids as $variant_id => $v) { $where_or_conditions[] = db_quote(" FIND_IN_SET('?i', advanced_variants)", $variant_id); } $where_and_conditions[] = '(' . implode(' OR ', $where_or_conditions) . ')'; } $where .= ' AND ' . implode(' AND ', $where_and_conditions); } // Ranges if (!empty($ranges_ids)) { $filter_conditions = db_get_hash_multi_array("SELECT `from`, `to`, feature_id, filter_id, range_id FROM ?:product_filter_ranges WHERE range_id IN (?n)", array('filter_id', 'range_id'), $ranges_ids); $where_conditions = array(); foreach ($filter_conditions as $fid => $range_conditions) { foreach ($range_conditions as $k => $condition) { $k = $fid . "_" . $k; $join .= db_quote(" LEFT JOIN ?:product_features_values as var_val_{$k} ON var_val_{$k}.product_id = ?:products.product_id AND var_val_{$k}.lang_code = ?s", CART_LANGUAGE); $where_conditions[] = db_quote("(var_val_{$k}.value_int >= ?i AND var_val_{$k}.value_int <= ?i AND var_val_{$k}.value = '' AND var_val_{$k}.feature_id = ?i)", $condition['from'], $condition['to'], $condition['feature_id']); } $where .= db_quote(" AND (?p)", implode(" OR ", $where_conditions)); $where_conditions = array(); } } if (!empty($params['filter_id']) && empty($params['view_all'])) { $filter_vq .= db_quote(" AND ?:product_filters.filter_id = ?i", $params['filter_id']); $filter_rq .= db_quote(" AND ?:product_filter_ranges.filter_id = ?i", $params['filter_id']); } if (!empty($params['view_all'])) { $values_fields[] = "UPPER(SUBSTRING(?:product_feature_variant_descriptions.variant, 1, 1)) AS `index`"; } $_join = $join; // Build condition for the standart fields if (!empty($field_ranges_ids)) { foreach ($field_ranges_ids as $field_type => $rids) { $structure = $fields[$field_type]; if (empty($fields[$field_type])) { continue; } if ($structure['table'] !== 'products' && strpos($join, 'JOIN ?:' . $structure['table']) === false) { $join .= " LEFT JOIN ?:{$structure['table']} ON ?:{$structure['table']}.product_id = ?:products.product_id"; } if ($structure['condition_type'] == 'D' && empty($structure['slider'])) { foreach ($rids as $rid) { $range_condition = db_get_row("SELECT `from`, `to` FROM ?:product_filter_ranges WHERE range_id = ?i", $rid); if (!empty($range_condition)) { $where .= db_quote(" AND ?:{$structure['table']}.{$structure['db_field']} >= ?i AND ?:{$structure['table']}.{$structure['db_field']} <= ?i", $range_condition['from'], $range_condition['to']); } } } elseif ($structure['condition_type'] == 'F') { $where .= db_quote(" AND ?:{$structure['table']}.{$structure['db_field']} IN (?a)", $rids); } elseif ($structure['condition_type'] == 'C') { if (count($rids) > 1) { unset($field_ranges_ids[$field_type]); foreach ($rids as $rid) { $params['features_hash'] = fn_delete_range_from_url($params['features_hash'], array('range_id' => $rid), $field_type); } } else { $where .= db_quote(" AND ?:{$structure['table']}.{$structure['db_field']} = ?s", $rids[0] == 1 ? 'Y' : 'N'); } } if (!empty($structure['join_params'])) { foreach ($structure['join_params'] as $field => $param) { $join .= db_quote(" AND ?:{$structure['table']}.{$field} = ?s ", $param); } } } } // Product availability conditions $where .= ' AND (' . fn_find_array_in_set($_SESSION['auth']['usergroup_ids'], '?:categories.usergroup_ids', true) . ')'; $where .= ' AND (' . fn_find_array_in_set($_SESSION['auth']['usergroup_ids'], '?:products.usergroup_ids', true) . ')'; $where .= db_quote(" AND ?:categories.status IN (?a) AND ?:products.status IN (?a)", array('A', 'H'), array('A')); if (fn_allowed_for('ULTIMATE') && Registry::get('runtime.company_id')) { $categories_join_condition = db_quote(' AND ?:categories.company_id = ?i', Registry::get('runtime.company_id')); } else { $categories_join_condition = ''; } $_j = " INNER JOIN ?:products_categories ON ?:products_categories.product_id = ?:products.product_id LEFT JOIN ?:categories ON ?:categories.category_id = ?:products_categories.category_id {$categories_join_condition}"; if (fn_allowed_for('MULTIVENDOR') && AREA == 'C') { if (!empty($_REQUEST['company_id'])) { $where .= " AND (?:products.company_id = " . !empty($_REQUEST['company_id']) . ") "; } $where .= " AND (companies.status = 'A')"; $_j .= " LEFT JOIN ?:companies as companies ON companies.company_id = ?:products.company_id"; } $_join .= $_j; $join .= $_j; $inventory_join = ''; if (Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') { $inventory_join .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = ?:products.product_id"; $where .= " AND IF(?:products.tracking = 'O', inventory.amount > 0, ?:products.amount > 0)"; } $join .= $inventory_join; // Localization $where .= fn_get_localizations_condition('?:products.localization', true); $where .= fn_get_localizations_condition('?:categories.localization', true); $sliders_join = $sliders_where = ''; /** * Change SQL parameters before select filter variants and products count * * @param array $values_fields Array of SQL fields to be selected in an SQL-query * @param string $join String with the complete JOIN information (JOIN type, tables and fields) for an SQL-query * @param string $sliders_join String with the additional complete JOIN information (JOIN type, tables and fields) for an SQL-query (for slider range filters) * @param array $feature_ids Array of feature IDs. * @param string $where String containing SQL-query condition possibly prepended with a logical operator (AND or OR) * @param string $sliders_where String containing additional SQL-query condition possibly prepended with a logical operator (AND or OR) (for slider range filters) * @param string $filter_vq String containing additional SQL-query condition for filter with variants possibly prepended with a logical operator (AND or OR) (for slider range filters) * @param string $filter_rq String containing additional SQL-query condition for filter with ranges possibly prepended with a logical operator (AND or OR) (for slider range filters) */ fn_set_hook('get_filters_products_count_query_params', $values_fields, $join, $sliders_join, $feature_ids, $where, $sliders_where, $filter_vq, $filter_rq); //var_dump($field_filters);echo"<br/>____________________________<br/>"; if (!empty($field_filters)) { // Field ranges foreach ($field_filters as $filter_id => $field) { $fields_join = $fields_where = ''; // Dinamic ranges (price, amount etc) if ($field['condition_type'] == 'D') { $_fields_join = " LEFT JOIN ?:{$field['table']} ON ?:{$field['table']}.{$field['db_field']} >= ?:product_filter_ranges.from AND ?:{$field['table']}.{$field['db_field']} <= ?:product_filter_ranges.to "; if ($field['field_type'] != 'A') { if (strpos($_join, 'JOIN ?:products ') === false) { $fields_join .= " LEFT JOIN ?:products ON ?:products.product_id = ?:product_prices.product_id"; } elseif (strpos($fields_join . $_join, 'JOIN ?:product_prices ') === false) { $fields_join .= db_quote(" LEFT JOIN ?:product_prices ON ?:product_prices.product_id = ?:products.product_id AND ?:product_prices.lower_limit = 1 AND ?:product_prices.usergroup_id IN (?n)", array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids'])); } } if ($field['table'] == 'product_prices') { $fields_join .= db_quote(" LEFT JOIN ?:product_prices as prices_2 ON ?:product_prices.product_id = prices_2.product_id AND ?:product_prices.price > prices_2.price AND prices_2.lower_limit = 1 AND prices_2.usergroup_id IN (?n)", array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids'])); $fields_join .= db_quote(" LEFT JOIN ?:product_options_inventory_prices as prices_3 ON ?:product_prices.product_id = prices_3.product_id "); $fields_where .= db_quote(" AND ?:product_prices.lower_limit = 1 AND ?:product_prices.usergroup_id IN (?n)", array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids'])); $fields_where .= " AND prices_2.price IS NULL"; } if (empty($field['slider'])) { $fields_join = $_fields_join . $fields_join . $inventory_join; $field_ranges_counts[$filter_id] = db_get_hash_array("SELECT COUNT(DISTINCT ?:{$field['table']}.product_id) as products, ?:product_filter_ranges.range_id, ?:product_filter_ranges_descriptions.range_name, ?:product_filter_ranges.filter_id FROM ?:product_filter_ranges LEFT JOIN ?:product_filter_ranges_descriptions ON ?:product_filter_ranges_descriptions.range_id = ?:product_filter_ranges.range_id AND ?:product_filter_ranges_descriptions.lang_code = ?s ?p WHERE ?:products.status IN ('A') AND ?:product_filter_ranges.filter_id = ?i ?p GROUP BY ?:product_filter_ranges.range_id HAVING products != 0 ORDER BY ?:product_filter_ranges.position, ?:product_filter_ranges_descriptions.range_name", 'range_id', CART_LANGUAGE, $fields_join . $_join, $filter_id, $where . $fields_where); } else { if ($field['field_type'] == 'A') { $db_field = "IF(?:products.tracking = 'O', inventory.amount, ?:products.amount)"; $fields_join .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = ?:products.product_id"; } else { $db_field = "?:{$field['table']}.{$field['db_field']}"; $fields_join .= $inventory_join; if (fn_allowed_for('ULTIMATE') && $field['field_type'] == 'P' && Registry::get('runtime.company_id')) { $db_field = "IF(prices_3.price IS NOT NULL, prices_3.price, IF(shared_prices.product_id IS NOT NULL, shared_prices.price, ?:product_prices.price))"; //$db_field = " IF(shared_prices.product_id IS NOT NULL, shared_prices.price, ?:product_prices.price)"; $fields_join .= db_quote(" LEFT JOIN ?:ult_product_prices AS shared_prices ON shared_prices.product_id = ?:products.product_id" . " AND shared_prices.lower_limit = 1" . " AND shared_prices.usergroup_id IN (?n)" . " AND shared_prices.company_id = ?i", array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids']), Registry::get('runtime.company_id')); } } $field_range_values[$filter_id] = db_get_row("SELECT MIN({$db_field}) min, MAX({$db_field}) max FROM ?:{$field['table']} ?p WHERE ?:products.status IN ('A') ?p", $fields_join . $_join, $where . $fields_where); //var_dump($field_range_values);echo"<br/>________________________<br/>"; if (fn_is_empty($field_range_values[$filter_id])) { unset($field_range_values[$filter_id]); } else { if ($field['field_type'] == 'P' && CART_SECONDARY_CURRENCY != CART_PRIMARY_CURRENCY) { $coef = Registry::get('currencies.' . CART_SECONDARY_CURRENCY . '.coefficient'); $field_range_values[$filter_id]['min'] = floatval($field_range_values[$filter_id]['min']) / floatval($coef); $field_range_values[$filter_id]['max'] = floatval($field_range_values[$filter_id]['max']) / floatval($coef); } $field_range_values[$filter_id]['min'] = floor($field_range_values[$filter_id]['min'] / $filters[$filter_id]['round_to']) * $filters[$filter_id]['round_to']; $field_range_values[$filter_id]['max'] = ceil($field_range_values[$filter_id]['max'] / $filters[$filter_id]['round_to']) * $filters[$filter_id]['round_to']; if ($field_range_values[$filter_id]['max'] - $field_range_values[$filter_id]['min'] <= $filters[$filter_id]['round_to']) { $field_range_values[$filter_id]['max'] = $field_range_values[$filter_id]['min'] + $filters[$filter_id]['round_to']; } if (!empty($slider_vals[$field['field_type']])) { $_slider_vals[$field['field_type']] = $slider_vals[$field['field_type']]; if ($field['field_type'] == 'P' && $slider_vals['P'][2] != CART_SECONDARY_CURRENCY) { $prev_coef = Registry::get('currencies.' . $slider_vals['P'][2] . '.coefficient'); $cur_coef = Registry::get('currencies.' . CART_SECONDARY_CURRENCY . '.coefficient'); $slider_vals['P'][0] = floor(floatval($slider_vals['P'][0]) * floatval($prev_coef) / floatval($cur_coef)); $slider_vals['P'][1] = ceil(floatval($slider_vals['P'][1]) * floatval($prev_coef) / floatval($cur_coef)); } $field_range_values[$filter_id]['left'] = $slider_vals[$field['field_type']][0]; $field_range_values[$filter_id]['right'] = $slider_vals[$field['field_type']][1]; if ($field_range_values[$filter_id]['left'] < $field_range_values[$filter_id]['min']) { $field_range_values[$filter_id]['left'] = $field_range_values[$filter_id]['min']; } if ($field_range_values[$filter_id]['left'] > $field_range_values[$filter_id]['max']) { $field_range_values[$filter_id]['left'] = $field_range_values[$filter_id]['max']; } if ($field_range_values[$filter_id]['right'] > $field_range_values[$filter_id]['max']) { $field_range_values[$filter_id]['right'] = $field_range_values[$filter_id]['max']; } if ($field_range_values[$filter_id]['right'] < $field_range_values[$filter_id]['min']) { $field_range_values[$filter_id]['right'] = $field_range_values[$filter_id]['min']; } if ($field_range_values[$filter_id]['right'] < $field_range_values[$filter_id]['left']) { $tmp = $field_range_values[$filter_id]['right']; $field_range_values[$filter_id]['right'] = $field_range_values[$filter_id]['left']; $field_range_values[$filter_id]['left'] = $tmp; } $field_range_values[$filter_id]['left'] = floor($field_range_values[$filter_id]['left'] / $filters[$filter_id]['round_to']) * $filters[$filter_id]['round_to']; $field_range_values[$filter_id]['right'] = ceil($field_range_values[$filter_id]['right'] / $filters[$filter_id]['round_to']) * $filters[$filter_id]['round_to']; /*if ($field_range_values[$filter_id]['right'] - $field_range_values[$filter_id]['left'] <= $filters[$filter_id]['round_to']) { $field_range_values[$filter_id]['right'] = $field_range_values[$filter_id]['left'] + $filters[$filter_id]['round_to']; }*/ if (!empty($field_range_values[$filter_id]['left']) || !empty($field_range_values[$filter_id]['right'])) { if ($field['field_type'] == 'P') { if (strpos($sliders_join, 'JOIN ?:product_prices ') === false) { if (strpos($join, 'JOIN ?:product_prices ') === false) { $sliders_join .= db_quote(" LEFT JOIN ?:product_prices ON ?:product_prices.product_id = ?:products.product_id AND ?:product_prices.lower_limit = 1 AND ?:product_prices.usergroup_id IN (?n)", array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids'])); } $vals = $_slider_vals['P']; $currency = !empty($vals[2]) ? $vals[2] : CART_PRIMARY_CURRENCY; if ($currency != CART_PRIMARY_CURRENCY) { $coef = Registry::get('currencies.' . $currency . '.coefficient'); $decimals = Registry::get('currencies.' . CART_PRIMARY_CURRENCY . '.decimals'); $vals[0] = round(floatval($vals[0]) * floatval($coef), $decimals); $vals[1] = round(floatval($vals[1]) * floatval($coef), $decimals); } $sliders_where .= db_quote(" AND ?:product_prices.price >= ?i AND ?:product_prices.price <= ?i", $vals[0], $vals[1]); } } elseif ($field['field_type'] == 'A') { if (strpos($sliders_join, 'JOIN ?:product_options_inventory ') === false) { if (strpos($join, 'JOIN ?:product_options_inventory ') === false) { $sliders_join .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = ?:products.product_id"; } $sliders_where .= db_quote(" AND {$db_field} >= ?i AND {$db_field} <= ?i", $field_range_values[$filter_id]['left'], $field_range_values[$filter_id]['right']); } } } } } } // Char values (free shipping etc) } elseif ($field['condition_type'] == 'C') { $field_ranges_counts[$filter_id] = db_get_hash_array("SELECT COUNT(DISTINCT ?:{$field['table']}.product_id) as products, ?:{$field['table']}.{$field['db_field']} as range_name, ?s AS field_type FROM ?:{$field['table']} ?p WHERE ?:products.status = 'A' ?p GROUP BY ?:{$field['table']}.{$field['db_field']}", 'range_name', $field['field_type'], $join, $where); if (!empty($field_ranges_counts[$filter_id])) { foreach ($field_ranges_counts[$filter_id] as $range_key => $range) { $field_ranges_counts[$filter_id][$range_key]['range_name'] = $field['variant_descriptions'][$range['range_name']]; $field_ranges_counts[$filter_id][$range_key]['range_id'] = $range['range_name'] == 'Y' ? 1 : 0; } } // Fixed values (supplier etc) } elseif ($field['condition_type'] == 'F') { $field_ranges_counts[$filter_id] = db_get_hash_array("SELECT COUNT(DISTINCT ?:{$field['table']}.product_id) as products, ?:{$field['foreign_table']}.{$field['range_name']} as range_name, UPPER(SUBSTRING(?:{$field['foreign_table']}.{$field['range_name']}, 1, 1)) AS `index`, ?:{$field['foreign_table']}.{$field['foreign_index']} as range_id, ?s AS field_type FROM ?:{$field['table']} LEFT JOIN ?:{$field['foreign_table']} ON ?:{$field['foreign_table']}.{$field['foreign_index']} = ?:{$field['table']}.{$field['db_field']} ?p WHERE ?:products.status IN ('A') ?p GROUP BY ?:{$field['table']}.{$field['db_field']} ORDER BY ?:{$field['foreign_table']}.{$field['range_name']} ", 'range_id', $field['field_type'], $join, $where); } } } $filter_company_condition = ""; if (fn_allowed_for('ULTIMATE') && Registry::get('runtime.company_id')) { $filters_list = db_get_fields("SELECT ?:product_filters.filter_id FROM ?:product_filters"); if (!empty($filters_list)) { $filter_company_condition = db_quote(" AND ?:product_filters.filter_id IN (?a)", $filters_list); } } if (!fn_allowed_for('ULTIMATE') || !empty($filters_list)) { $variants_counts = db_get_hash_multi_array("SELECT " . implode(', ', $values_fields) . " FROM ?:product_features_values LEFT JOIN ?:products ON ?:products.product_id = ?:product_features_values.product_id LEFT JOIN ?:product_filters ON ?:product_filters.feature_id = ?:product_features_values.feature_id AND ?:product_filters.status = 'A' LEFT JOIN ?:product_feature_variants ON ?:product_feature_variants.variant_id = ?:product_features_values.variant_id LEFT JOIN ?:product_feature_variant_descriptions ON ?:product_feature_variant_descriptions.variant_id = ?:product_feature_variants.variant_id AND ?:product_feature_variant_descriptions.lang_code = ?s LEFT JOIN ?:product_features ON ?:product_features.feature_id = ?:product_filters.feature_id ?p WHERE ?:product_features_values.feature_id IN (?n) AND ?:product_features_values.lang_code = ?s AND ?:product_features_values.variant_id ?p ?p AND ?:product_features.feature_type IN ('S', 'M', 'E') GROUP BY ?:product_features_values.variant_id, ?:product_filters.filter_id ORDER BY ?:product_feature_variants.position, ?:product_feature_variant_descriptions.variant", array('filter_id', 'range_id'), CART_LANGUAGE, $join . $sliders_join, $feature_ids, CART_LANGUAGE, $where . $sliders_where . $filter_company_condition, $filter_vq); } else { $variants_counts = array(); } $ranges_counts = db_get_hash_multi_array("SELECT " . implode(', ', $ranges_fields) . " FROM ?:product_filter_ranges LEFT JOIN ?:product_features_values ON ?:product_features_values.feature_id = ?:product_filter_ranges.feature_id AND ?:product_features_values.value_int >= ?:product_filter_ranges.from AND ?:product_features_values.value_int <= ?:product_filter_ranges.to LEFT JOIN ?:products ON ?:products.product_id = ?:product_features_values.product_id LEFT JOIN ?:product_filter_ranges_descriptions ON ?:product_filter_ranges_descriptions.range_id = ?:product_filter_ranges.range_id AND ?:product_filter_ranges_descriptions.lang_code = ?s LEFT JOIN ?:product_features ON ?:product_features.feature_id = ?:product_filter_ranges.feature_id ?p WHERE ?:product_features_values.feature_id IN (?n) AND ?:product_features_values.lang_code = ?s ?p ?p GROUP BY ?:product_filter_ranges.range_id ORDER BY ?:product_filter_ranges.position, ?:product_filter_ranges_descriptions.range_name", array('filter_id', 'range_id'), CART_LANGUAGE, $join . $sliders_join, $feature_ids, CART_LANGUAGE, $where . $sliders_where, $filter_rq); $merged = fn_array_merge($variants_counts, $ranges_counts, $field_ranges_counts); $view_all = array(); if (empty($merged) && empty($params['skip_other_variants']) && (!empty($av_ids) || !empty($ranges_ids) || !empty($_field_ranges_ids))) { fn_set_notification('W', __('text_nothing_found'), __('text_nothing_found_filter_message')); if (defined('AJAX_REQUEST')) { exit; } elseif (!empty($_SERVER['HTTP_REFERER'])) { fn_redirect($_SERVER['HTTP_REFERER']); } else { $_params = $params; $_params['skip_advanced_variants'] = true; $_params['only_selected'] = true; list($_f, $_view_all) = fn_get_filters_products_count($_params); foreach ($_f as $filter_id => $filter) { if (!empty($field_range_values[$filter_id])) { $_f[$filter_id]['range_values'] = $field_range_values[$filter_id]; } } return array($_f, $_view_all); } } foreach ($filters as $filter_id => $filter) { if (!empty($field_range_values[$filter_id]) || !empty($merged[$filter_id]) && empty($params['view_all']) || !empty($params['filter_id']) && $params['filter_id'] != $filter_id) { // Check if filter range was selected if (!empty($merged[$filter_id])) { if (empty($filters[$filter_id]['feature_id'])) { $intersect = array_intersect(array_keys($merged[$filter_id]), $field_ranges_ids); } else { $intersect = array_intersect(array_keys($merged[$filter_id]), $ranges_ids); } } if (!empty($intersect)) { foreach ($merged[$filter_id] as $k => $v) { if (!in_array($v['range_id'], $intersect)) { // Unset unselected ranges unset($merged[$filter_id][$k]); } } } if (!empty($field_range_values[$filter_id])) { $filters[$filter_id]['range_values'] = $field_range_values[$filter_id]; } $filters[$filter_id]['ranges'] =& $merged[$filter_id]; // Add feature type to the filter if (!empty($merged[$filter_id])) { $_first = reset($merged[$filter_id]); if (!empty($_first['feature_type'])) { $filters[$filter_id]['feature_type'] = $_first['feature_type']; } } if (!empty($params['simple_link']) && $filters[$filter_id]['feature_type'] == 'E') { $filters[$filter_id]['simple_link'] = true; } if (empty($params['advanced_filter']) && empty($params['skip_other_variants']) && !empty($filters[$filter_id]['ranges'])) { $selected = array(); $features_hash = !empty($params['features_hash']) ? $params['features_hash'] : ''; foreach ($filters[$filter_id]['ranges'] as $_k => $r) { if (fn_check_selected_filter($r['range_id'], !empty($r['feature_type']) ? $r['feature_type'] : '', $params, $filters[$filter_id]['field_type'])) { // selected variant $selected[$_k] = $r; $selected[$_k]['selected'] = true; unset($filters[$filter_id]['ranges'][$_k]); $features_hash = fn_delete_range_from_url($features_hash, $r, $filters[$filter_id]['field_type']); } } if (!empty($selected)) { $selected_range_ids = array_keys($selected); // Get other variants $_params = $params; //$_params['filter_id'] = $filter_id; $_params['features_hash'] = $features_hash; $_params['skip_other_variants'] = true; unset($_params['variant_id'], $_params['check_location']); list($_f) = fn_get_filters_products_count($_params); if (!empty($_f[$filter_id])) { $_f = $_f[$filter_id]; if (!empty($_f['ranges'])) { // delete current range foreach ($_f['ranges'] as $_rid => $_rv) { if (in_array($_rid, $selected_range_ids)) { unset($_f['ranges'][$_rid]); } } $filters[$filter_id]['ranges'] = $_f['ranges']; $filters[$filter_id]['more_cut'] = !empty($_f['more_cut']) ? $_f['more_cut'] : false; } } $filters[$filter_id]['selected_ranges'] = $selected; } if (!empty($params['only_selected'])) { //unset($filters[$filter_id]['ranges']); foreach ($filters[$filter_id]['ranges'] as $k => $v) { $filters[$filter_id]['ranges'][$k]['disabled'] = true; } } else { $_params = $params; $_params['filter_id'] = $filter_id; $_params['features_hash'] = ''; $_params['get_custom'] = true; $_params['skip_other_variants'] = true; unset($_params['variant_id'], $_params['check_location']); list($_f) = fn_get_filters_products_count($_params); if (!empty($_f[$filter_id])) { $_f = $_f[$filter_id]; if (!empty($_f['ranges'])) { foreach ($_f['ranges'] as $_rid => $_rv) { if (!isset($filters[$filter_id]['ranges'][$_rid]) && !isset($filters[$filter_id]['selected_ranges'][$_rid])) { $filters[$filter_id]['ranges'][$_rid] = $_rv; $filters[$filter_id]['ranges'][$_rid]['disabled'] = true; if (fn_check_selected_filter($_rv['range_id'], !empty($_rv['feature_type']) ? $_rv['feature_type'] : '', $params, !empty($_f[$filter_id]) ? $_f[$filter_id]['field_type'] : $_f['field_type'])) { $filters[$filter_id]['ranges'][$_rid]['checked'] = true; } } } } } } // Calculate number of ranges and compare with displaying count if (empty($params['get_all'])) { if (!empty($filters[$filter_id]['ranges'])) { $count = count($filters[$filter_id]['ranges']); } else { $count = 1; } if ($count > $filters[$filter_id]['display_more_count']) { $filters[$filter_id]['more_ranges'] = array_slice($filters[$filter_id]['ranges'], 0, $filters[$filter_id]['display_more_count'], true); $count = $filters[$filter_id]['display_more_count']; $filters[$filter_id]['more_cut'] = true; } else { $filters[$filter_id]['more_ranges'] = $filters[$filter_id]['ranges']; } $filters[$filter_id]['ranges'] = array_slice($filters[$filter_id]['more_ranges'], 0, $filters[$filter_id]['display_count'], true); $filters[$filter_id]['more_ranges'] = array_slice($filters[$filter_id]['more_ranges'], $filters[$filter_id]['display_count'], $count, true); } } else { if (!empty($params['variant_id']) && !empty($filters[$filter_id]['ranges'][$params['variant_id']])) { $filters[$filter_id]['ranges'][$params['variant_id']]['selected'] = true; // mark selected variant } } continue; // If its "view all" page, return all ranges } elseif (!empty($params['filter_id']) && $params['filter_id'] == $filter_id && !empty($merged[$filter_id])) { if (empty($params['return_view_all'])) { $filters[$filter_id]['ranges'] =& $merged[$filter_id]; $selected = array(); $features_hash = !empty($params['features_hash']) ? $params['features_hash'] : ''; foreach ($filters[$filter_id]['ranges'] as $_k => $r) { if (fn_check_selected_filter($r['range_id'], !empty($r['feature_type']) ? $r['feature_type'] : '', $params, $filters[$filter_id]['field_type'])) { $selected[$_k] = $r; $selected[$_k]['selected'] = true; unset($filters[$filter_id]['ranges'][$_k]); $features_hash = fn_delete_range_from_url($features_hash, $r, $filters[$filter_id]['field_type']); continue; } } if (!empty($selected)) { $selected_range_ids = array_keys($selected); // Get other variants $_params = $params; $_params['filter_id'] = $filter_id; $_params['features_hash'] = $features_hash; $_params['skip_other_variants'] = true; $_params['return_view_all'] = true; unset($_params['variant_id'], $_params['check_location']); list($_f, $view_all) = fn_get_filters_products_count($_params); } } if (empty($view_all)) { foreach ($merged[$filter_id] as $range) { if (!empty($range['index']) || strlen($range['index']) > 0) { // feature $view_all[$range['index']][] = $range; } else { // custom range $view_all[$filters[$range['filter_id']]['filter']][] = $range; } } ksort($view_all); } } // Unset filter if it's empty unset($filters[$filter_id]); } if (!empty($params['advanced_filter'])) { $_params = array('feature_types' => array('C', 'T'), 'plain' => true, 'category_ids' => array(empty($params['category_id']) ? 0 : $params['category_id'])); list($features) = fn_get_product_features($_params); if (!empty($features)) { $filters = array_merge($filters, $features); } } /** * Change product filters data * * @param array $filters Product filters data * @param array $view_all All ranges filters * @param array $params Products filter search params */ fn_set_hook('get_filters_products_count_before_select', $filters, $view_all, $params); Registry::set($key, array($filters, $view_all)); } else { list($filters, $view_all) = Registry::get($key); } return array($filters, $view_all); }
function fn_get_filters_products_count($params = array()) { $key = 'pfilters_' . md5(serialize($params)); Registry::register_cache($key, array('products', 'product_features', 'product_filters', 'product_features_values'), CACHE_LEVEL_USER); if (Registry::is_exist($key) == false) { if (!empty($params['check_location'])) { // FIXME: this is bad style, should be refactored $valid_locations = array('index.index', 'products.search', 'categories.view', 'product_features.view'); if (!in_array($params['dispatch'], $valid_locations)) { return array(); } if ($params['dispatch'] == 'categories.view') { $params['simple_link'] = true; // this parameter means that extended filters on this page should be displayed as simple $params['filter_custom_advanced'] = true; // this parameter means that extended filtering should be stayed on the same page } else { if ($params['dispatch'] == 'product_features.view') { $params['simple_link'] = true; $params['features_hash'] = (!empty($params['features_hash']) ? $params['features_hash'] . '.' : '') . 'V' . $params['variant_id']; //$params['exclude_feature_id'] = db_get_field("SELECT feature_id FROM ?:product_features_values WHERE variant_id = ?i", $params['variant_id']); } $params['get_for_home'] = 'Y'; } } if (!empty($params['skip_if_advanced']) && !empty($params['advanced_filter']) && $params['advanced_filter'] == 'Y') { return array(); } // Base fields for the SELECT queries $values_fields = array('?:product_features_values.feature_id', 'COUNT(DISTINCT ?:products.product_id) as products', '?:product_features_values.variant_id as range_id', '?:product_feature_variant_descriptions.variant as range_name', '?:product_features.feature_type', '?:product_filters.filter_id'); $ranges_fields = array('?:product_features_values.feature_id', 'COUNT(DISTINCT ?:products.product_id) as products', '?:product_filter_ranges.range_id', '?:product_filter_ranges_descriptions.range_name', '?:product_filter_ranges.filter_id', '?:product_features.feature_type'); $condition = $where = $join = $filter_vq = $filter_rq = ''; $variants_ids = $ranges_ids = $field_filters = $feature_ids = $field_ranges_ids = $field_ranges_counts = array(); if (!empty($params['features_hash'])) { list($variants_ids, $ranges_ids, $_field_ranges_ids) = fn_parse_features_hash($params['features_hash']); $field_ranges_ids = array_flip($_field_ranges_ids); } if (!empty($params['category_id'])) { $id_path = db_get_field("SELECT id_path FROM ?:categories WHERE category_id = ?i", $params['category_id']); $category_ids = db_get_fields("SELECT category_id FROM ?:categories WHERE id_path LIKE ?l", $id_path . '/%'); $category_ids[] = $params['category_id']; $condition .= db_quote(" AND (categories_path = '' OR FIND_IN_SET(?i, categories_path))", $params['category_id']); $where .= db_quote(" AND ?:products_categories.category_id IN (?n)", $category_ids); } elseif (empty($params['get_for_home']) && empty($params['get_custom'])) { $condition .= " AND categories_path = ''"; } if (!empty($params['filter_id'])) { $condition .= db_quote(" AND ?:product_filters.filter_id = ?i", $params['filter_id']); } if (!empty($params['get_for_home'])) { $condition .= db_quote(" AND ?:product_filters.show_on_home_page = ?s", $params['get_for_home']); } if (!empty($params['exclude_feature_id'])) { $condition .= db_quote(" AND ?:product_filters.feature_id NOT IN (?n)", $params['exclude_feature_id']); } $filters = db_get_hash_array("SELECT ?:product_filters.feature_id, ?:product_filters.filter_id, ?:product_filters.field_type, ?:product_filter_descriptions.filter, ?:product_features_descriptions.prefix, ?:product_features_descriptions.suffix FROM ?:product_filters LEFT JOIN ?:product_filter_descriptions ON ?:product_filter_descriptions.filter_id = ?:product_filters.filter_id AND ?:product_filter_descriptions.lang_code = ?s LEFT JOIN ?:product_features_descriptions ON ?:product_features_descriptions.feature_id = ?:product_filters.feature_id AND ?:product_features_descriptions.lang_code = ?s WHERE ?:product_filters.status = 'A' ?p ORDER by position", 'filter_id', CART_LANGUAGE, CART_LANGUAGE, $condition); $fields = fn_get_product_filter_fields(); if (empty($filters) && empty($params['advanced_filter'])) { return array(array(), false); } else { foreach ($filters as $k => $v) { if (!empty($v['feature_id'])) { // Feature filters $feature_ids[] = $v['feature_id']; } else { // Product field filters $_field = $fields[$v['field_type']]; $field_filters[$v['filter_id']] = array_merge($v, $_field); $filters[$k]['condition_type'] = $_field['condition_type']; } } } // Variants if (!empty($variants_ids)) { $join .= " LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS simple_variants FROM ?:product_features_values WHERE lang_code = '" . CART_LANGUAGE . "' GROUP BY product_id) AS pfv_simple ON pfv_simple.product_id = ?:products.product_id"; $where_condtions = array(); foreach ($variants_ids as $k => $variant_id) { $where_condtions[] = db_quote(" FIND_IN_SET('?i', simple_variants)", $variant_id); } $where .= ' AND ' . implode(' AND ', $where_condtions); } // Ranges if (!empty($ranges_ids)) { $range_conditions = db_get_array("SELECT `from`, `to`, feature_id FROM ?:product_filter_ranges WHERE range_id IN (?n)", $ranges_ids); foreach ($range_conditions as $k => $condition) { $join .= db_quote(" LEFT JOIN ?:product_features_values as var_val_{$k} ON var_val_{$k}.product_id = ?:products.product_id AND var_val_{$k}.lang_code = ?s", CART_LANGUAGE); $where .= db_quote(" AND (var_val_{$k}.value_int >= ?i AND var_val_{$k}.value_int <= ?i AND var_val_{$k}.value = '' AND var_val_{$k}.feature_id = ?i)", $condition['from'], $condition['to'], $condition['feature_id']); } } if (!empty($params['filter_id']) && empty($params['view_all'])) { $filter_vq .= db_quote(" AND ?:product_filters.filter_id = ?i", $params['filter_id']); $filter_rq .= db_quote(" AND ?:product_filter_ranges.filter_id = ?i", $params['filter_id']); } if (!empty($params['view_all'])) { $values_fields[] = "UPPER(SUBSTRING(?:product_feature_variant_descriptions.variant, 1, 1)) AS `index`"; } $_join = $join; // Build condition for the standart fields if (!empty($_field_ranges_ids)) { foreach ($_field_ranges_ids as $rid => $field_type) { $structure = $fields[$field_type]; if ($structure['table'] !== 'products' && strpos($join, 'JOIN ?:' . $structure['table']) === false) { $join .= " LEFT JOIN ?:{$structure['table']} ON ?:{$structure['table']}.product_id = ?:products.product_id"; } if ($structure['condition_type'] == 'D') { $range_condition = db_get_row("SELECT `from`, `to` FROM ?:product_filter_ranges WHERE range_id = ?i", $rid); if (!empty($range_condition)) { $where .= db_quote(" AND ?:{$structure['table']}.{$structure['db_field']} >= ?i AND ?:{$structure['table']}.{$structure['db_field']} <= ?i", $range_condition['from'], $range_condition['to']); } } elseif ($structure['condition_type'] == 'F') { $where .= db_quote(" AND ?:{$structure['table']}.{$structure['db_field']} = ?i", $rid); } elseif ($structure['condition_type'] == 'C') { $where .= db_quote(" AND ?:{$structure['table']}.{$structure['db_field']} = ?s", $rid == 1 ? 'Y' : 'N'); } if (!empty($structure['join_params'])) { foreach ($structure['join_params'] as $field => $param) { $join .= db_quote(" AND ?:{$structure['table']}.{$field} = ?s ", $param); } } } } // Product availability conditions $where .= ' AND (' . fn_find_array_in_set($_SESSION['auth']['usergroup_ids'], '?:categories.usergroup_ids', true) . ')'; $where .= ' AND (' . fn_find_array_in_set($_SESSION['auth']['usergroup_ids'], '?:products.usergroup_ids', true) . ')'; $where .= db_quote(" AND ?:categories.status IN (?a) AND ?:products.status IN (?a)", array('A', 'H'), array('A')); $_j = " INNER JOIN ?:products_categories ON ?:products_categories.product_id = ?:products.product_id LEFT JOIN ?:categories ON ?:categories.category_id = ?:products_categories.category_id"; if (Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') { $_j .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = ?:products.product_id"; $where .= " AND IF(?:products.tracking = 'O', inventory.amount > 0, ?:products.amount > 0)"; } $_join .= $_j; $join .= $_j; // Localization $where .= fn_get_localizations_condition('?:products.localization', true); $where .= fn_get_localizations_condition('?:categories.localization', true); $variants_counts = db_get_hash_multi_array("SELECT " . implode(', ', $values_fields) . " FROM ?:product_features_values LEFT JOIN ?:products ON ?:products.product_id = ?:product_features_values.product_id LEFT JOIN ?:product_filters ON ?:product_filters.feature_id = ?:product_features_values.feature_id LEFT JOIN ?:product_feature_variants ON ?:product_feature_variants.variant_id = ?:product_features_values.variant_id LEFT JOIN ?:product_feature_variant_descriptions ON ?:product_feature_variant_descriptions.variant_id = ?:product_feature_variants.variant_id AND ?:product_feature_variant_descriptions.lang_code = ?s LEFT JOIN ?:product_features ON ?:product_features.feature_id = ?:product_filters.feature_id ?p WHERE ?:product_features_values.feature_id IN (?n) AND ?:product_features_values.lang_code = ?s AND ?:product_features_values.variant_id ?p ?p AND ?:product_features.feature_type IN ('S', 'M', 'E') GROUP BY ?:product_features_values.variant_id ORDER BY ?:product_feature_variants.position, ?:product_feature_variant_descriptions.variant", array('filter_id', 'range_id'), CART_LANGUAGE, $join, $feature_ids, CART_LANGUAGE, $where, $filter_vq); $ranges_counts = db_get_hash_multi_array("SELECT " . implode(', ', $ranges_fields) . " FROM ?:product_filter_ranges LEFT JOIN ?:product_features_values ON ?:product_features_values.feature_id = ?:product_filter_ranges.feature_id AND ?:product_features_values.value_int >= ?:product_filter_ranges.from AND ?:product_features_values.value_int <= ?:product_filter_ranges.to LEFT JOIN ?:products ON ?:products.product_id = ?:product_features_values.product_id LEFT JOIN ?:product_filter_ranges_descriptions ON ?:product_filter_ranges_descriptions.range_id = ?:product_filter_ranges.range_id AND ?:product_filter_ranges_descriptions.lang_code = ?s LEFT JOIN ?:product_features ON ?:product_features.feature_id = ?:product_filter_ranges.feature_id ?p WHERE ?:product_features_values.feature_id IN (?n) AND ?:product_features_values.lang_code = ?s ?p ?p GROUP BY ?:product_filter_ranges.range_id ORDER BY ?:product_filter_ranges.position, ?:product_filter_ranges_descriptions.range_name", array('filter_id', 'range_id'), CART_LANGUAGE, $join, $feature_ids, CART_LANGUAGE, $where, $filter_rq); if (!empty($field_filters)) { // Field ranges foreach ($field_filters as $filter_id => $field) { $fields_join = $fields_where = ''; // Dinamic ranges (price, amount etc) if ($field['condition_type'] == 'D') { $fields_join = " LEFT JOIN ?:{$field['table']} ON ?:{$field['table']}.{$field['db_field']} >= ?:product_filter_ranges.from AND ?:{$field['table']}.{$field['db_field']} <= ?:product_filter_ranges.to "; if (strpos($fields_join . $_join, 'JOIN ?:products ') === false) { $fields_join .= db_quote(" LEFT JOIN ?:products ON ?:products.product_id = ?:product_prices.product_id AND ?:product_prices.lower_limit = 1 AND ?:product_prices.usergroup_id IN (?n)", array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids'])); } elseif (strpos($fields_join . $_join, 'JOIN ?:product_prices ') === false) { $fields_join .= " LEFT JOIN ?:product_prices ON ?:product_prices.product_id = ?:products.product_id"; } if ($field['table'] == 'product_prices') { $fields_join .= db_quote(" LEFT JOIN ?:product_prices as prices_2 ON ?:product_prices.product_id = prices_2.product_id AND ?:product_prices.price > prices_2.price AND prices_2.lower_limit = 1 AND prices_2.usergroup_id IN (?n)", array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids'])); $fields_where .= " AND prices_2.price IS NULL"; } $field_ranges_counts[$filter_id] = db_get_hash_array("SELECT COUNT(DISTINCT ?:{$field['table']}.product_id) as products, ?:product_filter_ranges.range_id, ?:product_filter_ranges_descriptions.range_name, ?:product_filter_ranges.filter_id FROM ?:product_filter_ranges LEFT JOIN ?:product_filter_ranges_descriptions ON ?:product_filter_ranges_descriptions.range_id = ?:product_filter_ranges.range_id AND ?:product_filter_ranges_descriptions.lang_code = ?s ?p WHERE ?:products.status IN ('A') AND ?:product_filter_ranges.filter_id = ?i ?p GROUP BY ?:product_filter_ranges.range_id HAVING products != 0 ORDER BY ?:product_filter_ranges.position, ?:product_filter_ranges_descriptions.range_name", 'range_id', CART_LANGUAGE, $fields_join . $_join, $filter_id, $where . $fields_where); // Char values (free shipping etc) } elseif ($field['condition_type'] == 'C') { $field_ranges_counts[$filter_id] = db_get_hash_array("SELECT COUNT(DISTINCT ?:{$field['table']}.product_id) as products, ?:{$field['table']}.{$field['db_field']} as range_name FROM ?:{$field['table']} ?p WHERE ?:products.status = 'A' ?p GROUP BY ?:{$field['table']}.{$field['db_field']}", 'range_name', $join, $where); if (!empty($field_ranges_counts[$filter_id])) { foreach ($field_ranges_counts[$filter_id] as $range_key => $range) { $field_ranges_counts[$filter_id][$range_key]['range_name'] = $field['variant_descriptions'][$range['range_name']]; $field_ranges_counts[$filter_id][$range_key]['range_id'] = $range['range_name'] == 'Y' ? 1 : 0; } } // Fixed values (supplier etc) } elseif ($field['condition_type'] == 'F') { $field_ranges_counts[$filter_id] = db_get_hash_array("SELECT COUNT(DISTINCT ?:{$field['table']}.product_id) as products, ?:{$field['foreign_table']}.{$field['range_name']} as range_name, ?:{$field['foreign_table']}.{$field['foreign_index']} as range_id FROM ?:{$field['table']} LEFT JOIN ?:{$field['foreign_table']} ON ?:{$field['foreign_table']}.{$field['foreign_index']} = ?:{$field['table']}.{$field['db_field']} ?p WHERE ?:products.status IN ('A') ?p GROUP BY ?:{$field['table']}.{$field['db_field']}", 'range_id', $join, $where); } } } $merged = fn_array_merge($variants_counts, $ranges_counts, $field_ranges_counts); $view_all = array(); foreach ($filters as $filter_id => $filter) { if (!empty($merged[$filter_id]) && empty($params['view_all']) || !empty($params['filter_id']) && $params['filter_id'] != $filter_id) { // Check if filter range was selected if (empty($filters[$filter_id]['feature_id'])) { $intersect = array_intersect(array_keys($merged[$filter_id]), $field_ranges_ids); } else { $intersect = array_intersect(array_keys($merged[$filter_id]), $variants_ids); } if (!empty($intersect)) { foreach ($merged[$filter_id] as $k => $v) { if (!in_array($v['range_id'], $intersect)) { // Unset unselected ranges unset($merged[$filter_id][$k]); } } } // Calculate number of ranges and compare with constant $count = count($merged[$filter_id]); if ($count > FILTERS_RANGES_MORE_COUNT && empty($params['get_all'])) { $merged[$filter_id] = array_slice($merged[$filter_id], 0, FILTERS_RANGES_MORE_COUNT, true); $filters[$filter_id]['more_cut'] = true; } $filters[$filter_id]['ranges'] =& $merged[$filter_id]; // Add feature type to the filter $_first = reset($merged[$filter_id]); if (!empty($_first['feature_type'])) { $filters[$filter_id]['feature_type'] = $_first['feature_type']; } if (!empty($params['simple_link']) && $filters[$filter_id]['feature_type'] == 'E') { $filters[$filter_id]['simple_link'] = true; } if (empty($params['skip_other_variants'])) { foreach ($filters[$filter_id]['ranges'] as $_k => $r) { if (!fn_check_selected_filter($r['range_id'], !empty($r['feature_type']) ? $r['feature_type'] : '', $params, $filters[$filter_id]['field_type'])) { // selected variant $filters[$filter_id]['ranges'] = array($_k => $r); $filters[$filter_id]['ranges'][$_k]['selected'] = true; // mark selected variant // Get other variants $_params = $params; $_params['filter_id'] = $filter_id; $_params['req_range_id'] = $r['range_id']; $_params['features_hash'] = fn_delete_range_from_url($params['features_hash'], $r, $filters[$filter_id]['field_type']); $_params['skip_other_variants'] = true; unset($_params['variant_id'], $_params['check_location']); list($_f) = fn_get_filters_products_count($_params); if (!empty($_f)) { $_f = reset($_f); // delete current range foreach ($_f['ranges'] as $_rid => $_rv) { if ($_rv['range_id'] == $r['range_id']) { unset($_f['ranges'][$_rid]); break; } } $filters[$filter_id]['other_variants'] = $_f['ranges']; } break; } } } else { if (!empty($params['variant_id']) && !empty($filters[$filter_id]['ranges'][$params['variant_id']])) { $filters[$filter_id]['ranges'][$params['variant_id']]['selected'] = true; // mark selected variant } } continue; // If its "view all" page, return all ranges } elseif (!empty($params['filter_id']) && $params['filter_id'] == $filter_id && !empty($merged[$filter_id])) { foreach ($merged[$filter_id] as $range) { if (!empty($range['index'])) { // feature $view_all[$range['index']][] = $range; } else { // custom range $view_all[$filters[$range['filter_id']]['filter']][] = $range; } } ksort($view_all); } // Unset filter if he is empty unset($filters[$filter_id]); } if (!empty($params['advanced_filter'])) { $_params = array('feature_types' => array('C', 'T'), 'plain' => true, 'category_ids' => array(empty($params['category_id']) ? 0 : $params['category_id'])); list($features) = fn_get_product_features($_params); if (!empty($features)) { $filters = array_merge($filters, $features); } } Registry::set($key, array($filters, $view_all)); } else { list($filters, $view_all) = Registry::get($key); } return array($filters, $view_all); }