function fn_get_products($params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
/**
* Changes params for selecting products
*
* @param array $params Product search params
* @param int $items_per_page Items per page
* @param string $lang_code Two-letter language code (e.g. 'en', 'ru', etc.)
*/
fn_set_hook('get_products_pre', $params, $items_per_page, $lang_code);
// Init filter
$params = LastView::instance()->update('products', $params);
// Set default values to input params
$default_params = array('area' => AREA, 'extend' => array('product_name', 'prices', 'categories'), 'custom_extend' => array(), 'pname' => '', 'pshort' => '', 'pfull' => '', 'pkeywords' => '', 'feature' => array(), 'type' => 'simple', 'page' => 1, 'action' => '', 'variants' => array(), 'ranges' => array(), 'custom_range' => array(), 'field_range' => array(), 'features_hash' => '', 'limit' => 0, 'bid' => 0, 'match' => '', 'tracking' => array(), 'get_frontend_urls' => false, 'items_per_page' => $items_per_page);
if (empty($params['custom_extend'])) {
$params['extend'] = !empty($params['extend']) ? array_merge($default_params['extend'], $params['extend']) : $default_params['extend'];
} else {
$params['extend'] = $params['custom_extend'];
}
$params = array_merge($default_params, $params);
if ((empty($params['pname']) || $params['pname'] != 'Y') && (empty($params['pshort']) || $params['pshort'] != 'Y') && (empty($params['pfull']) || $params['pfull'] != 'Y') && (empty($params['pkeywords']) || $params['pkeywords'] != 'Y') && (empty($params['feature']) || $params['feature'] != 'Y') && !empty($params['q'])) {
$params['pname'] = 'Y';
}
$auth =& $_SESSION['auth'];
// Define fields that should be retrieved
if (empty($params['only_short_fields'])) {
$fields = array('products.*');
} else {
$fields = array('product_id' => 'products.product_id', 'product_code' => 'products.product_code', 'product_type' => 'products.product_type', 'status' => 'products.status', 'company_id' => 'products.company_id', 'list_price' => 'products.list_price', 'amount' => 'products.amount', 'weight' => 'products.weight', 'tracking' => 'products.tracking', 'is_edp' => 'products.is_edp', 'return_period' => 'products.return_period');
}
//AS started_price
$fields['subtitle'] = 'descr1.subtitle';
$fields['top_title'] = 'descr1.top_title';
$fields['started_price'] = 'prices.price AS started_price';
// Define sort fields
$sortings = array('code' => 'products.product_code', 'status' => 'products.status', 'product' => 'product', 'position' => 'products_categories.position', 'price' => 'price', 'list_price' => 'products.list_price', 'weight' => 'products.weight', 'amount' => 'products.amount', 'timestamp' => 'products.timestamp', 'updated_timestamp' => 'products.updated_timestamp', 'popularity' => 'popularity.total', 'company' => 'company_name', 'null' => 'NULL');
if (!empty($params['get_subscribers'])) {
$sortings['num_subscr'] = 'num_subscr';
$fields['num_subscr'] = 'COUNT(DISTINCT product_subscriptions.subscription_id) as num_subscr';
}
if (!empty($params['order_ids'])) {
$sortings['p_qty'] = 'purchased_qty';
$sortings['p_subtotal'] = 'purchased_subtotal';
$fields['purchased_qty'] = "SUM(?:order_details.amount) as purchased_qty";
$fields['purchased_subtotal'] = "SUM(?:order_details.price * ?:order_details.amount) as purchased_subtotal";
}
if (isset($params['compact']) && $params['compact'] == 'Y') {
$union_condition = ' OR ';
} else {
$union_condition = ' AND ';
}
$join = $condition = $u_condition = $inventory_condition = '';
$having = array();
$extraCondition = array();
// Search string condition for SQL query
if (isset($params['q']) && fn_string_not_empty($params['q'])) {
$params['q'] = trim($params['q']);
if ($params['match'] == 'any') {
$pieces = fn_explode(' ', $params['q']);
$search_type = ' OR ';
} elseif ($params['match'] == 'all') {
$pieces = fn_explode(' ', $params['q']);
$search_type = ' AND ';
} else {
$pieces = array($params['q']);
$search_type = '';
}
$_condition = array();
foreach ($pieces as $piece) {
if (strlen($piece) == 0) {
continue;
}
$tmp = db_quote("(descr1.search_words LIKE ?l)", '%' . $piece . '%');
// check search words
if ($params['pname'] == 'Y') {
$tmp .= db_quote(" OR descr1.product LIKE ?l", '%' . $piece . '%');
}
if ($params['pshort'] == 'Y') {
$tmp .= db_quote(" OR descr1.short_description LIKE ?l", '%' . $piece . '%');
$tmp .= db_quote(" OR descr1.short_description LIKE ?l", '%' . htmlentities($piece, ENT_QUOTES, 'UTF-8') . '%');
}
if ($params['pfull'] == 'Y') {
$tmp .= db_quote(" OR descr1.full_description LIKE ?l", '%' . $piece . '%');
$tmp .= db_quote(" OR descr1.full_description LIKE ?l", '%' . htmlentities($piece, ENT_QUOTES, 'UTF-8') . '%');
}
if ($params['pkeywords'] == 'Y') {
$tmp .= db_quote(" OR (descr1.meta_keywords LIKE ?l OR descr1.meta_description LIKE ?l)", '%' . $piece . '%', '%' . $piece . '%');
}
if (!empty($params['feature']) && $params['action'] != 'feature_search') {
$tmp .= db_quote(" OR ?:product_features_values.value LIKE ?l", '%' . $piece . '%');
}
fn_set_hook('additional_fields_in_search', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $tmp, $piece, $having);
$_condition[] = '(' . $tmp . ')';
}
$_cond = implode($search_type, $_condition);
if (!empty($_condition)) {
$condition .= ' AND (' . $_cond . ') ';
}
if (!empty($params['feature']) && $params['action'] != 'feature_search') {
$join .= " LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id";
$condition .= db_quote(" AND (?:product_features_values.feature_id IN (?n) OR ?:product_features_values.feature_id IS NULL)", array_values($params['feature']));
}
//if perform search we also get additional fields
if ($params['pname'] == 'Y') {
$params['extend'][] = 'product_name';
}
if ($params['pshort'] == 'Y' || $params['pfull'] == 'Y' || $params['pkeywords'] == 'Y') {
$params['extend'][] = 'description';
}
unset($_condition);
}
//
// [Advanced and feature filters]
//
if (!empty($params['apply_limit']) && $params['apply_limit'] && !empty($params['pid'])) {
$pids = array();
foreach ($params['pid'] as $pid) {
if ($pid != $params['exclude_pid']) {
if (count($pids) == $params['limit']) {
break;
} else {
$pids[] = $pid;
}
}
}
$params['pid'] = $pids;
}
if (!empty($params['features_hash']) || !fn_is_empty($params['variants']) || !empty($params['feature_code'])) {
$join .= db_quote(" LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id AND ?:product_features_values.lang_code = ?s", $lang_code);
}
if (!empty($params['variants'])) {
$params['features_hash'] .= implode('.', $params['variants']);
}
// Feature code
if (!empty($params['feature_code'])) {
$join .= db_quote(" LEFT JOIN ?:product_features ON ?:product_features_values.feature_id = ?:product_features.feature_id");
$condition .= db_quote(" AND ?:product_features.feature_code = ?s", $params['feature_code']);
}
$advanced_variant_ids = $simple_variant_ids = $ranges_ids = $fields_ids = $fields_ids_revert = $slider_vals = array();
if (!empty($params['features_hash'])) {
list($av_ids, $ranges_ids, $fields_ids, $slider_vals, $fields_ids_revert) = 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['multiple_variants'])) {
$simple_variant_ids = $params['multiple_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", $lang_code);
$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) . ')';
}
$condition .= ' AND ' . implode(' AND ', $where_and_conditions);
}
if (!empty($simple_variant_ids)) {
$join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS simple_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_simple ON pfv_simple.product_id = products.product_id", $lang_code);
$where_conditions = array();
foreach ($simple_variant_ids as $k => $variant_id) {
$where_conditions[] = db_quote(" FIND_IN_SET('?i', simple_variants)", $variant_id);
}
$condition .= ' AND ' . implode(' AND ', $where_conditions);
}
//
// Ranges from text inputs
//
// Feature ranges
if (!empty($params['custom_range'])) {
foreach ($params['custom_range'] as $k => $v) {
$k = intval($k);
if (isset($v['from']) && fn_string_not_empty($v['from']) || isset($v['to']) && fn_string_not_empty($v['to'])) {
if (!empty($v['type'])) {
if ($v['type'] == 'D') {
$v['from'] = fn_parse_date($v['from']);
$v['to'] = fn_parse_date($v['to']);
}
}
$join .= db_quote(" LEFT JOIN ?:product_features_values as custom_range_{$k} ON custom_range_{$k}.product_id = products.product_id AND custom_range_{$k}.lang_code = ?s", $lang_code);
if (fn_string_not_empty($v['from']) && fn_string_not_empty($v['to'])) {
$condition .= db_quote(" AND (custom_range_{$k}.value_int >= ?i AND custom_range_{$k}.value_int <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i) ", $v['from'], $v['to'], $k);
} else {
$condition .= " AND custom_range_{$k}.value_int" . (fn_string_not_empty($v['from']) ? db_quote(' >= ?i', $v['from']) : db_quote(" <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i ", $v['to'], $k));
}
}
}
}
// Product field ranges
$filter_fields = fn_get_product_filter_fields();
if (!empty($params['field_range'])) {
foreach ($params['field_range'] as $field_type => $v) {
$structure = $filter_fields[$field_type];
if (!empty($structure) && (!empty($v['from']) || !empty($v['to']))) {
if ($field_type == 'P') {
// price
$v['cur'] = !empty($v['cur']) ? $v['cur'] : CART_SECONDARY_CURRENCY;
if (empty($v['orig_cur'])) {
// saving the first user-entered values
// will be always search by it
$v['orig_from'] = $v['from'];
$v['orig_to'] = $v['to'];
$v['orig_cur'] = $v['cur'];
$params['field_range'][$field_type] = $v;
}
if ($v['orig_cur'] != CART_PRIMARY_CURRENCY) {
// calc price in primary currency
$cur_prim_coef = Registry::get('currencies.' . $v['orig_cur'] . '.coefficient');
$decimals = Registry::get('currencies.' . CART_PRIMARY_CURRENCY . '.decimals');
$search_from = round($v['orig_from'] * floatval($cur_prim_coef), $decimals);
$search_to = round($v['orig_to'] * floatval($cur_prim_coef), $decimals);
} else {
$search_from = $v['orig_from'];
$search_to = $v['orig_to'];
}
// if user switch the currency, calc new values for displaying in filter
if ($v['cur'] != CART_SECONDARY_CURRENCY) {
if (CART_SECONDARY_CURRENCY == $v['orig_cur']) {
$v['from'] = $v['orig_from'];
$v['to'] = $v['orig_to'];
} else {
$prev_coef = Registry::get('currencies.' . $v['cur'] . '.coefficient');
$cur_coef = Registry::get('currencies.' . CART_SECONDARY_CURRENCY . '.coefficient');
$v['from'] = floor(floatval($v['from']) * floatval($prev_coef) / floatval($cur_coef));
$v['to'] = ceil(floatval($v['to']) * floatval($prev_coef) / floatval($cur_coef));
}
$v['cur'] = CART_SECONDARY_CURRENCY;
$params['field_range'][$field_type] = $v;
}
}
$params["{$structure['db_field']}_from"] = trim(isset($search_from) ? $search_from : $v['from']);
$params["{$structure['db_field']}_to"] = trim(isset($search_to) ? $search_to : $v['to']);
}
}
}
// Ranges from database
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 => $range_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", $lang_code);
$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)", $range_condition['from'], $range_condition['to'], $range_condition['feature_id']);
}
$condition .= db_quote(" AND (?p)", implode(" OR ", $where_conditions));
$where_conditions = array();
}
}
// Field ranges
//$fields_ids = empty($params['fields_ids']) ? $fields_ids : $params['fields_ids'];
if (!empty($params['fields_ids'])) {
foreach ($fields_ids as $rid => $field_type) {
if (!empty($filter_fields[$field_type])) {
$structure = $filter_fields[$field_type];
if ($structure['condition_type'] == 'D' && empty($structure['slider'])) {
$range_condition = db_get_row("SELECT `from`, `to`, range_id FROM ?:product_filter_ranges WHERE range_id = ?i", $rid);
if (!empty($range_condition)) {
$params["{$structure['db_field']}_from"] = $range_condition['from'];
$params["{$structure['db_field']}_to"] = $range_condition['to'];
}
} elseif ($structure['condition_type'] == 'F') {
$params['filter_params'][$structure['db_field']][] = $rid;
} elseif ($structure['condition_type'] == 'C') {
$params['filter_params'][$structure['db_field']][] = $rid == 1 ? 'Y' : 'N';
}
}
}
} elseif (!empty($fields_ids_revert)) {
foreach ($fields_ids_revert as $field_type => $rids) {
if (!empty($filter_fields[$field_type])) {
$structure = $filter_fields[$field_type];
if ($structure['condition_type'] == 'D' && empty($structure['slider'])) {
foreach ($rids as $rid) {
$range_condition = db_get_row("SELECT `from`, `to`, range_id FROM ?:product_filter_ranges WHERE range_id = ?i", $rid);
if (!empty($range_condition)) {
$params["{$structure['db_field']}_from"] = $range_condition['from'];
$params["{$structure['db_field']}_to"] = $range_condition['to'];
}
}
} elseif ($structure['condition_type'] == 'F') {
$params['filter_params'][$structure['db_field']] = $rids;
} elseif ($structure['condition_type'] == 'C') {
if (count($rids) > 1) {
foreach ($rids as $rid) {
if ($fields_ids[$rid] == $field_type) {
unset($fields_ids[$rid]);
}
$params['features_hash'] = fn_delete_range_from_url($params['features_hash'], array('range_id' => $rid), $field_type);
}
} else {
$params['filter_params'][$structure['db_field']][] = $rids[0] == 1 ? 'Y' : 'N';
}
}
}
}
}
// Slider ranges
$slider_vals = empty($params['slider_vals']) ? $slider_vals : $params['slider_vals'];
if (!empty($slider_vals)) {
foreach ($slider_vals as $field_type => $vals) {
if (!empty($filter_fields[$field_type])) {
if ($field_type == '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);
}
}
$structure = $filter_fields[$field_type];
$params["{$structure['db_field']}_from"] = $vals[0];
$params["{$structure['db_field']}_to"] = $vals[1];
}
}
}
// Checkbox features
if (!empty($params['ch_filters']) && !fn_is_empty($params['ch_filters'])) {
foreach ($params['ch_filters'] as $k => $v) {
// Product field filter
if (is_string($k) == true && !empty($v) && ($structure = $filter_fields[$k])) {
$condition .= db_quote(" AND {$structure['table']}.{$structure['db_field']} IN (?a)", $v == 'A' ? array('Y', 'N') : $v);
// Feature filter
} elseif (!empty($v)) {
$fid = intval($k);
$join .= db_quote(" LEFT JOIN ?:product_features_values as ch_features_{$fid} ON ch_features_{$fid}.product_id = products.product_id AND ch_features_{$fid}.lang_code = ?s", $lang_code);
$condition .= db_quote(" AND ch_features_{$fid}.feature_id = ?i AND ch_features_{$fid}.value IN (?a)", $fid, $v == 'A' ? array('Y', 'N') : $v);
}
}
}
// Text features
if (!empty($params['tx_features'])) {
foreach ($params['tx_features'] as $k => $v) {
if (fn_string_not_empty($v)) {
$fid = intval($k);
$join .= " LEFT JOIN ?:product_features_values as tx_features_{$fid} ON tx_features_{$fid}.product_id = products.product_id";
$condition .= db_quote(" AND tx_features_{$fid}.value LIKE ?l AND tx_features_{$fid}.lang_code = ?s", "%" . trim($v) . "%", $lang_code);
}
}
}
$total = 0;
fn_set_hook('get_products_before_select', $params, $join, $condition, $u_condition, $inventory_condition, $sortings, $total, $items_per_page, $lang_code, $having);
//
// [/Advanced filters]
//
$feature_search_condition = '';
if (!empty($params['feature'])) {
// Extended search by product fields
$_cond = array();
$total_hits = 0;
foreach ($params['feature'] as $f_id) {
if (!empty($f_val)) {
$total_hits++;
$_cond[] = db_quote("(?:product_features_values.feature_id = ?i)", $f_id);
}
}
$params['extend'][] = 'categories';
if (!empty($_cond)) {
$cache_feature_search = db_get_fields("SELECT product_id, COUNT(product_id) as cnt FROM ?:product_features_values WHERE (" . implode(' OR ', $_cond) . ") GROUP BY product_id HAVING cnt = {$total_hits}");
$feature_search_condition .= db_quote(" AND products_categories.product_id IN (?n)", $cache_feature_search);
}
}
// Category search condition for SQL query
if (!empty($params['cid'])) {
$cids = is_array($params['cid']) ? $params['cid'] : explode(',', $params['cid']);
if (!empty($params['subcats']) && $params['subcats'] == 'Y') {
$_ids = db_get_fields("SELECT a.category_id FROM ?:categories as a LEFT JOIN ?:categories as b ON b.category_id IN (?n) WHERE a.id_path LIKE CONCAT(b.id_path, '/%')", $cids);
$cids = fn_array_merge($cids, $_ids, false);
}
$params['extend'][] = 'categories';
$condition .= db_quote(" AND ?:categories.category_id IN (?n)", $cids);
}
// If we need to get the products by IDs and no IDs passed, don't search anything
if (!empty($params['force_get_by_ids']) && empty($params['pid']) && empty($params['product_id'])) {
return array(array(), $params, 0);
}
// Product ID search condition for SQL query
if (!empty($params['pid'])) {
$u_condition .= db_quote($union_condition . ' products.product_id IN (?n)', $params['pid']);
}
// Exclude products from search results
if (!empty($params['exclude_pid'])) {
$condition .= db_quote(' AND products.product_id NOT IN (?n)', $params['exclude_pid']);
}
// Search by feature comparison flag
if (!empty($params['feature_comparison'])) {
$condition .= db_quote(' AND products.feature_comparison = ?s', $params['feature_comparison']);
}
// Search products by localization
$condition .= fn_get_localizations_condition('products.localization', true);
$company_condition = '';
if (fn_allowed_for('MULTIVENDOR')) {
if ($params['area'] == 'C') {
$company_condition .= " AND companies.status = 'A' ";
$params['extend'][] = 'companies';
} else {
$company_condition .= fn_get_company_condition('products.company_id');
}
} else {
$cat_company_condition = '';
if (Registry::get('runtime.company_id')) {
$params['extend'][] = 'categories';
$cat_company_condition .= fn_get_company_condition('?:categories.company_id');
} elseif (!empty($params['company_ids'])) {
$params['extend'][] = 'categories';
$cat_company_condition .= db_quote(' AND ?:categories.company_id IN (?a)', explode(',', $params['company_ids']));
}
$company_condition .= $cat_company_condition;
}
$condition .= $company_condition;
if (!fn_allowed_for('ULTIMATE') && Registry::get('runtime.company_id') && isset($params['company_id'])) {
$params['company_id'] = Registry::get('runtime.company_id');
}
if (isset($params['company_id']) && $params['company_id'] != '') {
$condition .= db_quote(' AND products.company_id = ?i ', $params['company_id']);
}
if (!empty($params['filter_params'])) {
foreach ($params['filter_params'] as $field => $f_vals) {
$condition .= db_quote(' AND products.' . $field . ' IN (?a) ', $f_vals);
}
}
if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) {
$having['price_from'] = db_quote(' price >= ?d', fn_convert_price(trim($params['price_from'])));
$extraCondition["price_from"] = db_quote(' price >= ?d', fn_convert_price(trim($params['price_from'])));
//$condition .= db_quote(' AND prices.price >= ?d', fn_convert_price(trim($params['price_from'])));
$params['extend'][] = 'prices2';
}
if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) {
$having['price_to'] = db_quote(' price <= ?d', fn_convert_price(trim($params['price_to'])));
$extraCondition["price_to"] = db_quote(' price <= ?d', fn_convert_price(trim($params['price_to'])));
//$condition .= db_quote(' AND prices.price <= ?d', fn_convert_price(trim($params['price_to'])));
$params['extend'][] = 'prices2';
}
if (isset($params['weight_from']) && fn_is_numeric($params['weight_from'])) {
$condition .= db_quote(' AND products.weight >= ?d', fn_convert_weight(trim($params['weight_from'])));
}
if (isset($params['weight_to']) && fn_is_numeric($params['weight_to'])) {
$condition .= db_quote(' AND products.weight <= ?d', fn_convert_weight(trim($params['weight_to'])));
}
// search specific inventory status
if (!empty($params['tracking'])) {
$condition .= db_quote(' AND products.tracking IN(?a)', $params['tracking']);
}
if (isset($params['amount_from']) && fn_is_numeric($params['amount_from'])) {
$condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount >= ?i, products.amount >= ?i)", $params['amount_from'], $params['amount_from']);
$inventory_condition .= db_quote(' AND inventory.amount >= ?i', $params['amount_from']);
}
if (isset($params['amount_to']) && fn_is_numeric($params['amount_to'])) {
$condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount <= ?i, products.amount <= ?i)", $params['amount_to'], $params['amount_to']);
$inventory_condition .= db_quote(' AND inventory.amount <= ?i', $params['amount_to']);
}
if (Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && $params['area'] == 'C') {
// FIXME? Registry in model
$condition .= " AND IF(products.tracking = 'O', inventory.amount > 0, products.amount > 0)";
}
if (!empty($params['status'])) {
$condition .= db_quote(' AND products.status IN (?a)', $params['status']);
}
if (!empty($params['shipping_freight_from'])) {
$condition .= db_quote(' AND products.shipping_freight >= ?d', $params['shipping_freight_from']);
}
if (!empty($params['shipping_freight_to'])) {
$condition .= db_quote(' AND products.shipping_freight <= ?d', $params['shipping_freight_to']);
}
if (!empty($params['free_shipping'])) {
$condition .= db_quote(' AND products.free_shipping = ?s', $params['free_shipping']);
}
if (!empty($params['downloadable'])) {
$condition .= db_quote(' AND products.is_edp = ?s', $params['downloadable']);
}
if (isset($params['pcode']) && fn_string_not_empty($params['pcode'])) {
$pcode = trim($params['pcode']);
$fields['combination'] = 'inventory.combination';
$u_condition .= db_quote(" {$union_condition} (inventory.product_code LIKE ?l OR products.product_code LIKE ?l)", "%{$pcode}%", "%{$pcode}%");
$inventory_condition .= db_quote(" AND inventory.product_code LIKE ?l", "%{$pcode}%");
}
if (isset($params['amount_to']) && fn_is_numeric($params['amount_to']) || isset($params['amount_from']) && fn_is_numeric($params['amount_from']) || !empty($params['pcode']) || Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && $params['area'] == 'C') {
$join .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = products.product_id {$inventory_condition}";
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (products.timestamp >= ?i AND products.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
if (!empty($params['item_ids'])) {
$condition .= db_quote(" AND products.product_id IN (?n)", explode(',', $params['item_ids']));
}
if (isset($params['popularity_from']) && fn_is_numeric($params['popularity_from'])) {
$params['extend'][] = 'popularity';
$condition .= db_quote(' AND popularity.total >= ?i', $params['popularity_from']);
}
if (isset($params['popularity_to']) && fn_is_numeric($params['popularity_to'])) {
$params['extend'][] = 'popularity';
$condition .= db_quote(' AND popularity.total <= ?i', $params['popularity_to']);
}
if (!empty($params['order_ids'])) {
$arr = strpos($params['order_ids'], ',') !== false || !is_array($params['order_ids']) ? explode(',', $params['order_ids']) : $params['order_ids'];
$condition .= db_quote(" AND ?:order_details.order_id IN (?n)", $arr);
$join .= " LEFT JOIN ?:order_details ON ?:order_details.product_id = products.product_id";
}
$limit = '';
$group_by = 'products.product_id';
// Show enabled products
$_p_statuses = array('A');
if ($params['p_status']) {
$_p_statuses = array('A', 'H');
}
$condition .= $params['area'] == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], 'products.usergroup_ids', true) . ')' . db_quote(' AND products.status IN (?a)', $_p_statuses) : '';
// -- JOINS --
if (in_array('product_name', $params['extend'])) {
$fields['product'] = 'descr1.product as product';
$join .= db_quote(" LEFT JOIN ?:product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ?s ", $lang_code);
}
// get prices
$price_condition = '';
if (in_array('prices', $params['extend'])) {
// //$fields['price'] = 'MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price';
$join .= " LEFT JOIN ?:product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1";
$price_condition = db_quote(' AND prices.usergroup_id IN (?n)', $params['area'] == 'A' ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids']));
$condition .= $price_condition;
//$price_usergroup_cond_2 = db_quote(' AND usergroup_id IN (?n)', (($params['area'] == 'A') ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids'])));
$extraConditionString = "";
if (count($extraCondition) > 0) {
$extraConditionString = "WHERE " . implode(" AND ", $extraCondition);
}
$fields['price'] = 'case
when p2.min_price > 0 then p2.min_price
else p3.price2
end';
$join .= " LEFT JOIN (SELECT product_id, MIN(price) as min_price FROM ?:product_options_inventory_prices " . $extraConditionString . " GROUP BY product_id) AS p2 ON p2.product_id = products.product_id\n LEFT JOIN (SELECT product_id, MIN(IF(percentage_discount = 0, price, price - (price * percentage_discount)/100)) as price2 FROM ?:product_prices WHERE lower_limit = 1 GROUP by product_id) AS p3 ON p3.product_id=products.product_id ";
}
/*
// get prices for search by price
if (in_array('prices2', $params['extend'])) {
$price_usergroup_cond_2 = db_quote(' AND prices_2.usergroup_id IN (?n)', (($params['area'] == 'A') ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids'])));
$join .= " LEFT JOIN ?:product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price " . $price_usergroup_cond_2;
$condition .= ' AND prices_2.price IS NULL';
$price_condition .= ' AND prices_2.price IS NULL';
}
*/
// if (in_array('prices2', $params['extend'])) {
// $price_usergroup_cond_2 = db_quote(' AND usergroup_id IN (?n)', (($params['area'] == 'A') ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids'])));
// $fields['price'] = 'case
// when p2.min_price > 0 then p2.min_price
// else p3.price2
// end';
// $join .= " LEFT JOIN (SELECT product_id, MIN(price) as min_price FROM ?:product_options_inventory_prices GROUP BY product_id) AS p2 ON p2.product_id = products.product_id
// LEFT JOIN (SELECT product_id, MIN(IF(percentage_discount = 0, price, price - (price * percentage_discount)/100)) as price2 FROM ?:product_prices WHERE lower_limit = 1 ".$price_usergroup_cond_2." GROUP by product_id) AS p3 ON p3.product_id=products.product_id ";
// }
// get short & full description
if (in_array('search_words', $params['extend'])) {
$fields['search_words'] = 'descr1.search_words';
}
// get short & full description
if (in_array('description', $params['extend'])) {
$fields['short_description'] = 'descr1.short_description';
if (in_array('full_description', $params['extend'])) {
$fields['full_description'] = "descr1.full_description";
} else {
$fields['full_description'] = "IF(descr1.short_description = '', descr1.full_description, '') as full_description";
}
}
// get companies
$companies_join = db_quote(" LEFT JOIN ?:companies AS companies ON companies.company_id = products.company_id ");
if (in_array('companies', $params['extend'])) {
$fields['company_name'] = 'companies.company as company_name';
$join .= $companies_join;
}
// for compatibility
if (in_array('category_ids', $params['extend'])) {
$params['extend'][] = 'categories';
}
// get categories
$_c_statuses = array('A', 'H');
// Show enabled categories
$skip_checking_usergroup_permissions = fn_is_preview_action($auth, $params);
if ($skip_checking_usergroup_permissions) {
$category_avail_cond = '';
} else {
$category_avail_cond = $params['area'] == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], '?:categories.usergroup_ids', true) . ')' : '';
}
$category_avail_cond .= $params['area'] == 'C' ? db_quote(" AND ?:categories.status IN (?a) ", $_c_statuses) : '';
$categories_join = " INNER JOIN ?:products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN ?:categories ON ?:categories.category_id = products_categories.category_id {$category_avail_cond} {$feature_search_condition}";
if (!empty($params['order_ids'])) {
// Avoid duplicating by sub-categories
$condition .= db_quote(' AND products_categories.link_type = ?s', 'M');
}
if (in_array('categories', $params['extend'])) {
$fields['category_ids'] = "GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids";
$fields['categories_position'] = 'products_categories.position';
$join .= $categories_join;
$condition .= fn_get_localizations_condition('?:categories.localization', true);
}
// get popularity
$popularity_join = db_quote(" LEFT JOIN ?:product_popularity as popularity ON popularity.product_id = products.product_id");
if (in_array('popularity', $params['extend'])) {
$fields['popularity'] = 'popularity.total as popularity';
$join .= $popularity_join;
}
if (!empty($params['get_subscribers'])) {
$join .= " LEFT JOIN ?:product_subscriptions as product_subscriptions ON product_subscriptions.product_id = products.product_id";
}
// -- \JOINs --
if (!empty($u_condition)) {
$condition .= " {$union_condition} ((" . ($union_condition == ' OR ' ? '0 ' : '1 ') . $u_condition . ')' . $company_condition . $price_condition . ')';
}
/**
* Changes additional params for selecting products
*
* @param array $params Product search params
* @param array $fields List of fields for retrieving
* @param array $sortings Sorting fields
* @param string $condition String containing SQL-query condition possibly prepended with a logical operator (AND or OR)
* @param string $join String with the complete JOIN information (JOIN type, tables and fields) for an SQL-query
* @param string $sorting String containing the SQL-query ORDER BY clause
* @param string $group_by String containing the SQL-query GROUP BY field
* @param string $lang_code Two-letter language code (e.g. 'en', 'ru', etc.)
* @param array $having HAVING condition
*/
fn_set_hook('get_products', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $lang_code, $having);
// -- SORTINGS --
if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) {
$params = array_merge($params, fn_get_default_products_sorting());
if (empty($sortings[$params['sort_by']])) {
$_products_sortings = fn_get_products_sorting();
$params['sort_by'] = key($_products_sortings);
}
}
$default_sorting = fn_get_products_sorting();
if ($params['sort_by'] == 'popularity' && !in_array('popularity', $params['extend'])) {
$join .= $popularity_join;
}
if ($params['sort_by'] == 'company' && !in_array('companies', $params['extend'])) {
$join .= $companies_join;
}
if (empty($params['sort_order'])) {
if (!empty($default_sorting[$params['sort_by']]['default_order'])) {
$params['sort_order'] = $default_sorting[$params['sort_by']]['default_order'];
} else {
$params['sort_order'] = 'asc';
}
}
$sorting = db_sort($params, $sortings);
if (fn_allowed_for('ULTIMATE')) {
if (in_array('sharing', $params['extend'])) {
$fields['is_shared_product'] = "IF(COUNT(IF(?:categories.company_id = products.company_id, NULL, ?:categories.company_id)), 'Y', 'N') as is_shared_product";
if (strpos($join, $categories_join) === false) {
$join .= $categories_join;
}
}
}
// -- \SORTINGS --
// Used for View cascading
if (!empty($params['get_query'])) {
return "SELECT products.product_id FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY products.product_id";
}
// Used for Extended search
if (!empty($params['get_conditions'])) {
return array($fields, $join, $condition);
}
if (!empty($params['limit'])) {
$limit = db_quote(" LIMIT 0, ?i", $params['limit']);
} elseif (!empty($params['items_per_page'])) {
$limit = db_paginate($params['page'], $params['items_per_page']);
}
$calc_found_rows = '';
if (empty($total)) {
$calc_found_rows = 'SQL_CALC_FOUND_ROWS';
}
if (!empty($having)) {
$having = ' HAVING ' . implode(' AND ', $having);
} else {
$having = '';
}
$products = db_get_array("SELECT {$calc_found_rows} " . implode(', ', $fields) . " FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY {$group_by} {$having} {$sorting} {$limit}");
if (!empty($params['items_per_page'])) {
$params['total_items'] = !empty($total) ? $total : db_get_found_rows();
} else {
$params['total_items'] = count($products);
}
// Post processing
if (in_array('categories', $params['extend'])) {
foreach ($products as $k => $v) {
list($products[$k]['category_ids'], $products[$k]['main_category']) = fn_convert_categories($v['category_ids']);
}
}
if (!empty($params['get_frontend_urls'])) {
foreach ($products as &$product) {
$product['url'] = fn_url('products.view?product_id=' . $product['product_id'], 'C');
}
}
if (!empty($params['item_ids'])) {
$products = fn_sort_by_ids($products, explode(',', $params['item_ids']));
}
if (!empty($params['pid']) && !empty($params['apply_limit']) && $params['apply_limit']) {
$products = fn_sort_by_ids($products, $params['pid']);
}
foreach ($products as $keyProduct => $productItem) {
$checkInventoryPrices = array();
$inventory_product = array();
$checkInventoryPrices = db_get_row("SELECT b.price FROM ?:products as a JOIN ?:product_options_inventory_prices as b ON a.product_id=b.product_id WHERE a.product_id = ?i ", $productItem['product_id']);
if ($checkInventoryPrices && count($checkInventoryPrices) > 0) {
$conditionExt = "";
if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) {
$conditionExt .= db_quote(' AND b.price >= ?d', fn_convert_price(trim($params['price_from'])));
}
if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) {
$conditionExt .= db_quote(' AND b.price <= ?d', fn_convert_price(trim($params['price_to'])));
}
$inventory_product = db_get_row("SELECT MIN(b.price ) AS min_price, MAX(b.price ) AS max_price FROM ?:products as a JOIN ?:product_options_inventory_prices as b ON a.product_id=b.product_id WHERE a.product_id = ?i " . $conditionExt . " GROUP BY a.product_id", $productItem['product_id']);
if (!$inventory_product || count($inventory_product) < 1) {
unset($products[$keyProduct]);
} elseif ($inventory_product && count($inventory_product) > 0) {
$products[$keyProduct]['price_range'] = $inventory_product;
}
}
//fn_promotion_apply('catalog', $productItem, $auth);
}
/**
* Changes selected products
*
* @param array $products Array of products
* @param array $params Product search params
* @param string $lang_code Language code
*/
fn_set_hook('get_products_post', $products, $params, $lang_code);
LastView::instance()->processResults('products', $products, $params);
return array($products, $params);
}
/**
* Gets displayable product data to show it in the cart
*
* @param string $hash Unique product HASH
* @param array &$product Product data
* @param bool $skip_promotion Skip promotion calculation
* @param array &$cart Array of cart content and user information necessary for purchase
* @param array &$auth Array with authorization data
* @param array $promotion_amount Amount of product in promotion (like Free products, etc)
* @return array Product data
*/
function fn_get_cart_product_data($hash, &$product, $skip_promotion, &$cart, &$auth, $promotion_amount = 0)
{
/**
* Prepare params before getting product data from cart
*
* @param string $hash Unique product HASH
* @param array &$product Product data
* @param bool $skip_promotion Skip promotion calculation
* @param array &$cart Array of cart content and user information necessary for purchase
* @param array &$auth Array with authorization data
* @param array $promotion_amount Amount of product in promotion (like Free products, etc)
*/
fn_set_hook('get_cart_product_data_pre', $hash, $product, $skip_promotion, $cart, $auth, $promotion_amount);
if (!empty($product['product_id'])) {
$fields = array('?:products.product_id', '?:products.company_id', "GROUP_CONCAT(IF(?:products_categories.link_type = 'M', CONCAT(?:products_categories.category_id, 'M'), ?:products_categories.category_id)) as category_ids", '?:products.product_code', '?:products.weight', '?:products.tracking', '?:product_descriptions.product', '?:product_descriptions.short_description', '?:products.is_edp', '?:products.edp_shipping', '?:products.shipping_freight', '?:products.free_shipping', '?:products.zero_price_action', '?:products.tax_ids', '?:products.qty_step', '?:products.list_qty_count', '?:products.max_qty', '?:products.min_qty', '?:products.amount as in_stock', '?:products.shipping_params', '?:companies.status as company_status', '?:companies.company as company_name');
$join = db_quote("LEFT JOIN ?:product_descriptions ON ?:product_descriptions.product_id = ?:products.product_id AND ?:product_descriptions.lang_code = ?s", CART_LANGUAGE);
$_p_statuses = array('A', 'H');
$_c_statuses = array('A', 'H');
$avail_cond = '';
if (fn_allowed_for('ULTIMATE') && Registry::get('runtime.company_id')) {
if (AREA == 'C') {
$avail_cond .= fn_get_company_condition('?:categories.company_id');
} else {
$avail_cond .= ' AND (' . fn_get_company_condition('?:categories.company_id', false) . ' OR ' . fn_get_company_condition('?:products.company_id', false) . ')';
}
}
$avail_cond .= AREA == 'C' ? " AND (" . fn_find_array_in_set($auth['usergroup_ids'], '?:categories.usergroup_ids', true) . ")" : '';
$avail_cond .= AREA == 'C' ? " AND (" . fn_find_array_in_set($auth['usergroup_ids'], '?:products.usergroup_ids', true) . ")" : '';
$avail_cond .= AREA == 'C' && !(isset($auth['area']) && $auth['area'] == 'A') ? db_quote(' AND ?:categories.status IN (?a) AND ?:products.status IN (?a)', $_c_statuses, $_p_statuses) : '';
$avail_cond .= AREA == 'C' ? fn_get_localizations_condition('?:products.localization') : '';
$join .= " INNER JOIN ?:products_categories ON ?:products_categories.product_id = ?:products.product_id INNER JOIN ?:categories ON ?:categories.category_id = ?:products_categories.category_id {$avail_cond}";
$join .= " LEFT JOIN ?:companies ON ?:companies.company_id = ?:products.company_id";
fn_set_hook('pre_get_cart_product_data', $hash, $product, $skip_promotion, $cart, $auth, $promotion_amount, $fields, $join);
$_pdata = db_get_row("SELECT " . implode(', ', $fields) . " FROM ?:products ?p WHERE ?:products.product_id = ?i GROUP BY ?:products.product_id", $join, $product['product_id']);
// delete product from cart if vendor was disabled.
if (empty($_pdata) || !empty($_pdata['company_id']) && !defined('ORDER_MANAGEMENT') && $_pdata['company_status'] != 'A') {
fn_delete_cart_product($cart, $hash);
return false;
}
if (!empty($_pdata['category_ids'])) {
list($_pdata['category_ids'], $_pdata['main_category']) = fn_convert_categories($_pdata['category_ids']);
} else {
$_pdata['category_ids'] = array();
}
$_pdata['options_count'] = db_get_field("SELECT COUNT(*) FROM ?:product_options WHERE product_id = ?i AND status = 'A'", $product['product_id']);
$amount = !empty($product['amount_total']) ? $product['amount_total'] : $product['amount'];
$_pdata['price'] = fn_get_product_price($product['product_id'], $amount, $auth);
$_pdata['base_price'] = isset($product['stored_price']) && $product['stored_price'] == 'Y' ? $product['price'] : $_pdata['price'];
fn_set_hook('get_cart_product_data', $product['product_id'], $_pdata, $product, $auth, $cart, $hash);
$product['stored_price'] = empty($product['stored_price']) ? 'N' : $product['stored_price'];
$product['stored_discount'] = empty($product['stored_discount']) ? 'N' : $product['stored_discount'];
$product['product_options'] = empty($product['product_options']) ? array() : $product['product_options'];
if (empty($_pdata['product_id'])) {
// FIXME - for deleted products for OM
fn_delete_cart_product($cart, $hash);
return array();
}
if (!empty($_pdata['options_count']) && empty($product['product_options'])) {
$cart['products'][$hash]['product_options'] = fn_get_default_product_options($product['product_id']);
}
if (Registry::get('settings.General.inventory_tracking') == 'Y' && !empty($_pdata['tracking']) && $_pdata['tracking'] == ProductTracking::TRACK_WITH_OPTIONS && !empty($product['selectable_cart_id'])) {
$_pdata['in_stock'] = db_get_field("SELECT amount FROM ?:product_options_inventory WHERE combination_hash = ?i", $product['selectable_cart_id']);
}
$product['amount'] = fn_check_amount_in_stock($product['product_id'], $product['amount'], $product['product_options'], $hash, $_pdata['is_edp'], !empty($product['original_amount']) ? $product['original_amount'] : 0, $cart);
if ($product['amount'] == 0) {
fn_delete_cart_product($cart, $hash);
$out_of_stock = true;
return false;
}
if (!fn_allowed_for('ULTIMATE:FREE')) {
$exceptions = fn_get_product_exceptions($product['product_id'], true);
if (!isset($product['options_type']) || !isset($product['exceptions_type'])) {
$product = array_merge($product, db_get_row('SELECT options_type, exceptions_type FROM ?:products WHERE product_id = ?i', $product['product_id']));
}
if (!fn_is_allowed_options_exceptions($exceptions, $product['product_options'], $product['options_type'], $product['exceptions_type']) && !defined('GET_OPTIONS')) {
fn_set_notification('E', __('notice'), __('product_options_forbidden_combination', array('[product]' => $_pdata['product'])));
fn_delete_cart_product($cart, $hash);
return false;
}
if (!fn_is_allowed_options($product)) {
fn_set_notification('E', __('notice'), __('product_disabled_options', array('[product]' => $_pdata['product'])));
fn_delete_cart_product($cart, $hash);
return false;
}
}
if (isset($product['extra']['custom_files'])) {
$_pdata['extra']['custom_files'] = $product['extra']['custom_files'];
}
$_pdata['calculation'] = array();
if (isset($product['extra']['exclude_from_calculate'])) {
$_pdata['exclude_from_calculate'] = $product['extra']['exclude_from_calculate'];
$_pdata['aoc'] = !empty($product['extra']['aoc']);
$_pdata['price'] = 0;
} else {
if ($product['stored_price'] == 'Y') {
$_pdata['price'] = $product['price'];
}
}
$product['price'] = $_pdata['zero_price_action'] == 'A' && isset($product['custom_user_price']) ? $product['custom_user_price'] : floatval($_pdata['price']);
$cart['products'][$hash]['price'] = $product['price'];
$_pdata['original_price'] = $product['price'];
if ($product['stored_price'] != 'Y' && !isset($product['extra']['exclude_from_calculate'])) {
$_tmp = $product['price'];
$product['price'] = fn_apply_options_modifiers($product['product_options'], $product['price'], 'P', array(), array('product_data' => $product));
$product['modifiers_price'] = $_pdata['modifiers_price'] = $product['price'] - $_tmp;
// modifiers
} else {
$product['modifiers_price'] = $_pdata['modifiers_price'] = 0;
}
if (isset($product['modifiers_price']) && $_pdata['zero_price_action'] == 'A') {
$_pdata['base_price'] = $product['price'] - $product['modifiers_price'];
}
$_pdata['weight'] = fn_apply_options_modifiers($product['product_options'], $_pdata['weight'], 'W', array(), array('product_data' => $product));
$_pdata['amount'] = $product['amount'];
$_pdata['price'] = $_pdata['original_price'] = fn_format_price($product['price']);
$_pdata['stored_price'] = $product['stored_price'];
if ($cart['options_style'] == 'F') {
$_pdata['product_options'] = fn_get_selected_product_options($product['product_id'], $product['product_options'], CART_LANGUAGE);
} elseif ($cart['options_style'] == 'I') {
$_pdata['product_options'] = fn_get_selected_product_options_info($product['product_options'], CART_LANGUAGE);
} else {
$_pdata['product_options'] = $product['product_options'];
}
fn_set_hook('get_cart_product_data_post_options', $product['product_id'], $_pdata, $product);
if (($_pdata['free_shipping'] != 'Y' || AREA == 'A') && ($_pdata['is_edp'] != 'Y' || $_pdata['is_edp'] == 'Y' && $_pdata['edp_shipping'] == 'Y')) {
$cart['shipping_required'] = true;
}
$cart['products'][$hash]['is_edp'] = !empty($_pdata['is_edp']) && $_pdata['is_edp'] == 'Y' ? 'Y' : 'N';
$cart['products'][$hash]['edp_shipping'] = !empty($_pdata['edp_shipping']) && $_pdata['edp_shipping'] == 'Y' ? 'Y' : 'N';
if (empty($cart['products'][$hash]['extra']['parent'])) {
// count only products without parent
if ($skip_promotion == true && !empty($promotion_amount)) {
$cart['amount'] += $promotion_amount;
} else {
$cart['amount'] += $product['amount'];
}
}
if ($skip_promotion == false) {
if (empty($cart['order_id']) || !empty($cart['recalculate_catalog_promotions'])) {
fn_promotion_apply('catalog', $_pdata, $auth);
} else {
if (isset($product['discount'])) {
$_pdata['discount'] = $product['discount'];
$_pdata['price'] -= $product['discount'];
if ($_pdata['price'] < 0) {
$_pdata['discount'] += $_pdata['price'];
$_pdata['price'] = 0;
}
}
}
// apply discount to the product
if (!empty($_pdata['discount'])) {
$cart['use_discount'] = true;
}
}
if (!empty($product['object_id'])) {
$_pdata['object_id'] = $product['object_id'];
}
$_pdata['shipping_params'] = empty($_pdata['shipping_params']) ? array() : unserialize($_pdata['shipping_params']);
$_pdata['stored_discount'] = $product['stored_discount'];
$cart['products'][$hash]['modifiers_price'] = $product['modifiers_price'];
$_pdata['subtotal'] = $_pdata['price'] * $product['amount'];
$cart['original_subtotal'] += $_pdata['original_price'] * $product['amount'];
$cart['subtotal'] += $_pdata['subtotal'];
/**
* Prepare params before getting product data from cart
*
* @param string $hash Unique product HASH
* @param array &$product Product data
* @param bool $skip_promotion Skip promotion calculation
* @param array &$cart Array of cart content and user information necessary for purchase
* @param array &$auth Array with authorization data
* @param array $promotion_amount Amount of product in promotion (like Free products, etc)
* @param array $promotion_amount Product data
*/
fn_set_hook('get_cart_product_data_post', $hash, $product, $skip_promotion, $cart, $auth, $promotion_amount, $_pdata);
return $_pdata;
}
return array();
}
/**
* Lazily loads additional data related to products after they have been fetched from DB.
* Used to ease main product loading SQL-query.
*
* @param array $products List of products
* @param array $params Parameters passed to fn_get_products()
* @param string $lang_code Language code passed to fn_get_products()
*
* @return array List of products with additional data merged into.
*/
function fn_load_products_extra_data($products, $params, $lang_code)
{
$products = fn_array_elements_to_keys($products, 'product_id');
$product_ids = array_keys($products);
$extra_fields = array();
// Fields from "products" table
$extra_fields['?:products'] = array('primary_key' => 'product_id', 'fields' => empty($params['only_short_fields']) ? array('*') : array('product_id', 'product_code', 'product_type', 'status', 'company_id', 'list_price', 'amount', 'weight', 'tracking', 'is_edp'));
// Load prices lazily when they are needed and no sorting or filtering by price is applied
if (in_array('prices', $params['extend']) && $params['sort_by'] != 'price' && !in_array('prices2', $params['extend'])) {
$extra_fields['?:product_prices'] = array('primary_key' => 'product_id', 'fields' => array('price' => 'MIN(IF(' . '?:product_prices.percentage_discount = 0,' . '?:product_prices.price,' . '?:product_prices.price - (?:product_prices.price * ?:product_prices.percentage_discount)/100' . '))'), 'condition' => db_quote(' AND ?:product_prices.lower_limit = 1 AND ?:product_prices.usergroup_id IN (?n)', $params['area'] == 'A' ? USERGROUP_ALL : array_unique(array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids']))), 'group_by' => ' GROUP BY ?:product_prices.product_id');
}
// Descriptions
$extra_fields['?:product_descriptions']['primary_key'] = 'product_id';
$extra_fields['?:product_descriptions']['condition'] = db_quote(" AND ?:product_descriptions.lang_code = ?s", $lang_code);
if (in_array('search_words', $params['extend'])) {
$extra_fields['?:product_descriptions']['fields'][] = 'search_words';
}
if (in_array('description', $params['extend'])) {
$extra_fields['?:product_descriptions']['fields'][] = 'short_description';
if (in_array('full_description', $params['extend'])) {
$extra_fields['?:product_descriptions']['fields'][] = 'full_description';
} else {
$extra_fields['?:product_descriptions']['fields']['full_description'] = "IF(?:product_descriptions.short_description = '', ?:product_descriptions.full_description, '')";
}
}
// Categories
if (in_array('categories', $params['extend'])) {
$categories_join = ' INNER JOIN ?:categories ON ?:categories.category_id = ?:products_categories.category_id';
if ($params['area'] == 'C') {
if (!fn_is_preview_action($_SESSION['auth'], $params)) {
$categories_join .= ' AND (' . fn_find_array_in_set($_SESSION['auth']['usergroup_ids'], '?:categories.usergroup_ids', true) . ')';
}
$categories_join .= db_quote(' AND ?:categories.status IN (?a) ', array('A', 'H'));
}
$extra_fields['?:products_categories'] = array('primary_key' => 'product_id', 'fields' => array('category_ids' => 'GROUP_CONCAT(' . 'IF(?:products_categories.link_type = "M",' . ' CONCAT(?:products_categories.category_id, "M"),' . ' ?:products_categories.category_id)' . ')', 'position' => '?:products_categories.position'), 'condition' => fn_get_localizations_condition('?:categories.localization', true), 'join' => $categories_join, 'group_by' => ' GROUP BY ?:products_categories.product_id');
if (!empty($params['cid'])) {
$extra_fields['?:products_categories']['group_by'] .= db_quote(' ,?:products_categories.category_id = ?i', $params['cid']);
}
}
/**
* Allows you to extend configuration of extra fields that should be lazily loaded for products.
*
* @see fn_load_extra_data_by_item_ids()
* @param array $extra_fields
* @param array $products List of products
* @param array $product_ids List of product identifiers
* @param array $params Parameters passed to fn_get_products()
* @param string $lang_code Language code passed to fn_get_products()
*/
fn_set_hook('load_products_extra_data', $extra_fields, $products, $product_ids, $params, $lang_code);
// Execute extra data loading SQL-queries and merge results into $products array
fn_merge_extra_data_to_entity_list(fn_load_extra_data_by_entity_ids($extra_fields, $product_ids), $products);
// Categories post-processing
if (in_array('categories', $params['extend'])) {
foreach ($products as $k => $v) {
list($products[$k]['category_ids'], $products[$k]['main_category']) = fn_convert_categories($v['category_ids']);
}
}
/**
* Allows you lazily load extra data for products after they were fetched from DB or post-process lazy-loaded
* additional data related to products.
*
* @param array $products List of products
* @param array $product_ids List of product identifiers
* @param array $params Parameters passed to fn_get_products()
* @param string $lang_code Language code passed to fn_get_products()
*/
fn_set_hook('load_products_extra_data_post', $products, $product_ids, $params, $lang_code);
return $products;
}
function fn_get_cart_product_data($hash, &$product, $skip_promotion, &$cart, &$auth, $promotion_amount = 0)
{
if (!empty($product['product_id'])) {
$_p_statuses = array('A', 'H');
$_c_statuses = array('A', 'H');
$avail_cond = AREA == 'C' ? " AND (" . fn_find_array_in_set($auth['usergroup_ids'], '?:categories.usergroup_ids', true) . ")" : '';
$avail_cond .= AREA == 'C' ? " AND (" . fn_find_array_in_set($auth['usergroup_ids'], '?:products.usergroup_ids', true) . ")" : '';
$avail_cond .= AREA == 'C' ? db_quote(' AND ?:categories.status IN (?a) AND ?:products.status IN (?a)', $_c_statuses, $_p_statuses) : '';
$avail_cond .= AREA == 'C' ? fn_get_localizations_condition('?:products.localization') : '';
$join = " INNER JOIN ?:products_categories ON ?:products_categories.product_id = ?:products.product_id INNER JOIN ?:categories ON ?:categories.category_id = ?:products_categories.category_id {$avail_cond}";
$_pdata = db_get_row("SELECT ?:products.product_id, ?:products.company_id, GROUP_CONCAT(IF(?:products_categories.link_type = 'M', CONCAT(?:products_categories.category_id, 'M'), ?:products_categories.category_id)) as category_ids, ?:products.product_code, ?:products.weight, ?:products.tracking, ?:product_descriptions.product, ?:product_descriptions.short_description, ?:products.is_edp, ?:products.edp_shipping, ?:products.shipping_freight, ?:products.free_shipping, ?:products.zero_price_action, ?:products.tax_ids, ?:products.qty_step, ?:products.list_qty_count, ?:products.max_qty, ?:products.min_qty, ?:products.amount as in_stock FROM ?:products LEFT JOIN ?:product_descriptions ON ?:product_descriptions.product_id = ?:products.product_id AND ?:product_descriptions.lang_code = ?s ?p WHERE ?:products.product_id = ?i GROUP BY ?:products.product_id", CART_LANGUAGE, $join, $product['product_id']);
$companies =& Registry::get('s_companies');
// delete product from cart if supplier or vendor was disabled.
if (empty($_pdata) || !empty($_pdata['company_id']) && ($companies[$_pdata['company_id']]['status'] != 'A' || (PRODUCT_TYPE == 'PROFESSIONAL' || PRODUCT_TYPE == 'COMMUNITY') && Registry::get('settings.Suppliers.enable_suppliers') != 'Y')) {
return false;
}
$_pdata['category_ids'] = fn_convert_categories($_pdata['category_ids']);
$_pdata['options_count'] = db_get_field("SELECT COUNT(*) FROM ?:product_options WHERE product_id = ?i AND status = 'A'", $product['product_id']);
$_pdata['price'] = fn_get_product_price($product['product_id'], $product['amount'], $auth);
$_pdata['base_price'] = isset($product['stored_price']) && $product['stored_price'] == 'Y' ? $product['price'] : $_pdata['price'];
fn_set_hook('get_cart_product_data', $product['product_id'], $_pdata, $product);
$product['stored_price'] = empty($product['stored_price']) ? 'N' : $product['stored_price'];
$product['stored_discount'] = empty($product['stored_discount']) ? 'N' : $product['stored_discount'];
$product['product_options'] = empty($product['product_options']) ? array() : $product['product_options'];
if (empty($_pdata['product_id'])) {
// FIXME - for deleted products for OM
unset($cart['products'][$hash]);
return array();
}
if (!empty($_pdata['options_count']) && empty($product['product_options'])) {
$cart['products'][$hash]['product_options'] = fn_get_default_product_options($product['product_id']);
}
if (Registry::get('settings.General.inventory_tracking') == 'Y' && !empty($_pdata['tracking']) && $_pdata['tracking'] == 'O' && !empty($product['selectable_cart_id'])) {
$_pdata['in_stock'] = db_get_field("SELECT amount FROM ?:product_options_inventory WHERE combination_hash = ?i", $product['selectable_cart_id']);
}
if (fn_check_amount_in_stock($product['product_id'], $product['amount'], $product['product_options'], $hash, $_pdata['is_edp'], !empty($product['original_amount']) ? $product['original_amount'] : 0, $cart) == false) {
unset($cart['products'][$hash]);
$out_of_stock = true;
return false;
}
$exceptions = fn_get_product_exceptions($product['product_id'], true);
if (!isset($product['options_type']) || !isset($product['exceptions_type'])) {
$product = array_merge($product, db_get_row('SELECT options_type, exceptions_type FROM ?:products WHERE product_id = ?i', $product['product_id']));
}
if (!fn_is_allowed_options_exceptions($exceptions, $product['product_options'], $product['options_type'], $product['exceptions_type']) && !defined('GET_OPTIONS')) {
fn_set_notification('E', fn_get_lang_var('notice'), str_replace('[product]', $_pdata['product'], fn_get_lang_var('product_options_forbidden_combination')));
unset($cart['products'][$hash]);
return false;
}
if (isset($product['extra']['custom_files'])) {
$_pdata['extra']['custom_files'] = $product['extra']['custom_files'];
}
$_pdata['calculation'] = array();
if (isset($product['extra']['exclude_from_calculate'])) {
$_pdata['exclude_from_calculate'] = $product['extra']['exclude_from_calculate'];
$_pdata['aoc'] = !empty($product['extra']['aoc']);
$_pdata['price'] = 0;
} else {
if ($product['stored_price'] == 'Y') {
$_pdata['price'] = $product['price'];
}
}
// If price defined and zero price action allows add zero priced product to cart, get price from the database
if (isset($_pdata['price']) && $_pdata['zero_price_action'] != 'A') {
$product['price'] = floatval($_pdata['price']);
$cart['products'][$hash]['price'] = $product['price'];
}
$_pdata['original_price'] = $product['price'];
if ($product['stored_price'] != 'Y' && !isset($product['extra']['exclude_from_calculate'])) {
if ($_pdata['zero_price_action'] != 'A' || $_pdata['zero_price_action'] == 'A' && empty($product['modifiers_price'])) {
$_tmp = $product['price'];
$product['price'] = fn_apply_options_modifiers($product['product_options'], $product['price'], 'P');
$product['modifiers_price'] = $_pdata['modifiers_price'] = $product['price'] - $_tmp;
// modifiers
}
} else {
$product['modifiers_price'] = $_pdata['modifiers_price'] = 0;
}
if (isset($product['modifiers_price']) && $_pdata['zero_price_action'] == 'A') {
$_pdata['base_price'] = $product['price'] - $product['modifiers_price'];
}
$_pdata['weight'] = fn_apply_options_modifiers($product['product_options'], $_pdata['weight'], 'W');
$_pdata['amount'] = $product['amount'];
$_pdata['price'] = $_pdata['original_price'] = fn_format_price($product['price']);
$_pdata['stored_price'] = $product['stored_price'];
if ($cart['options_style'] == 'F') {
$_pdata['product_options'] = fn_get_selected_product_options($product['product_id'], $product['product_options'], CART_LANGUAGE);
} elseif ($cart['options_style'] == 'I') {
$_pdata['product_options'] = fn_get_selected_product_options_info($product['product_options'], CART_LANGUAGE);
} else {
$_pdata['product_options'] = $product['product_options'];
}
if (($_pdata['free_shipping'] != 'Y' || AREA == 'A') && ($_pdata['is_edp'] != 'Y' || $_pdata['is_edp'] == 'Y' && $_pdata['edp_shipping'] == 'Y')) {
$cart['shipping_required'] = true;
}
$cart['products'][$hash]['is_edp'] = !empty($_pdata['is_edp']) && $_pdata['is_edp'] == 'Y' ? 'Y' : 'N';
$cart['products'][$hash]['edp_shipping'] = !empty($_pdata['edp_shipping']) && $_pdata['edp_shipping'] == 'Y' ? 'Y' : 'N';
if (empty($cart['products'][$hash]['extra']['parent'])) {
// count only products without parent
if ($skip_promotion == true && !empty($promotion_amount)) {
$cart['amount'] += $promotion_amount;
} else {
$cart['amount'] += $product['amount'];
}
}
if ($skip_promotion == false) {
if (empty($cart['order_id'])) {
fn_promotion_apply('catalog', $_pdata, $auth);
} else {
if (empty($product['original_discount'])) {
$product['original_discount'] = !empty($product['discount']) ? $product['discount'] : 0;
$cart['products'][$hash]['original_discount'] = $product['original_discount'];
}
if (isset($product['discount'])) {
$_pdata['discount'] = $product['discount'];
$_pdata['price'] -= $product['discount'];
if ($_pdata['price'] < 0) {
$_pdata['discount'] += $_pdata['price'];
$_pdata['price'] = 0;
}
}
}
// apply discount to the product
if (!empty($_pdata['discount'])) {
$cart['use_discount'] = true;
}
}
if (!empty($product['object_id'])) {
$_pdata['object_id'] = $product['object_id'];
}
$_pdata['stored_discount'] = $product['stored_discount'];
$cart['products'][$hash]['modifiers_price'] = $product['modifiers_price'];
$_pdata['subtotal'] = $_pdata['price'] * $product['amount'];
$cart['original_subtotal'] += $_pdata['original_price'] * $product['amount'];
$cart['subtotal'] += $_pdata['subtotal'];
return $_pdata;
}
return array();
}
function fn_get_products($params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
if (isset($params['doLinks']) && $params['doLinks'] == 1) {
$neighbours = fn_get_neighbours($_REQUEST['product_id']);
$params['pid'] = $neighbours;
$params['limit'] = 4;
}
if (isset($params['doSales']) && $params['doSales'] == 1) {
$params['pid'] = fn_get_sales_products();
}
if (isset($params['doSameLine']) && $params['doSameLine'] == 1) {
$sameLineProducts = fn_get_same_line_products($_REQUEST['product_id']);
if ($sameLineProducts) {
$params['pid'] = $sameLineProducts;
} else {
return false;
}
}
// Init filter
$relevanceField = '';
$relevanceOrder = '';
$params = fn_init_view('products', $params);
// Set default values to input params
$default_params = array('pname' => '', 'pshort' => '', 'pfull' => '', 'pkeywords' => '', 'feature' => array(), 'type' => 'simple', 'page' => 1, 'action' => '', 'variants' => array(), 'ranges' => array(), 'custom_range' => array(), 'field_range' => array(), 'features_hash' => '', 'limit' => 0, 'bid' => 0, 'match' => '', 'search_tracking_flags' => array());
$params = array_merge($default_params, $params);
if ((empty($params['pname']) || $params['pname'] != 'Y') && (empty($params['pshort']) || $params['pshort'] != 'Y') && (empty($params['pfull']) || $params['pfull'] != 'Y') && (empty($params['pkeywords']) || $params['pkeywords'] != 'Y') && (empty($params['feature']) || $params['feature'] != 'Y') && !empty($params['q'])) {
$params['pname'] = 'Y';
}
$auth =& $_SESSION['auth'];
// Define fields that should be retrieved
$fields = array('products.product_id', 'products.company_id', 'companies.company as company_name', 'descr1.product as product', 'products.tracking', 'products.feature_comparison', 'products.zero_price_action', 'products.product_type', 'products.tax_ids', 'products.weight', "GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids", 'min_qty', 'max_qty', 'products.qty_step', 'products.list_qty_count', 'products.is_edp', 'avail_since', 'buy_in_advance', 'products.options_type', 'products.exceptions_type', 'companies.company as company_name', 'products.timestamp');
// Define sort fields
$sortings = array('code' => 'products.product_code', 'status' => 'products.status', 'product' => 'descr1.product', 'position' => 'products_categories.position', 'price' => 'prices.price', 'list_price' => 'products.list_price', 'weight' => 'products.weight', 'amount' => 'products.amount', 'timestamp' => 'products.timestamp', 'popularity' => 'popularity.total', 'company' => "company_name");
$directions = array('asc' => 'asc', 'desc' => 'desc');
$join = $condition = $inventory_condition = '';
$condition .= fn_get_company_condition('products.company_id');
if (isset($params['company_id']) && $params['company_id'] != '') {
$params['company_id'] = intval($params['company_id']);
$condition .= db_quote(' AND products.company_id = ?i ', $params['company_id']);
}
if (AREA == 'C') {
if (fn_check_suppliers_functionality()) {
// if MVE or suppliers enabled
$condition .= " AND (companies.status = 'A' OR products.company_id = 0) ";
} else {
// if suppliers disabled
$condition .= fn_get_company_condition('products.company_id', true, '0', false, true);
}
}
$join .= db_quote(" LEFT JOIN ?:companies companies ON companies.company_id = products.company_id ");
$join .= db_quote(" LEFT JOIN ?:product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ?s ", $lang_code);
// Search string condition for SQL query
if (isset($params['q']) && fn_string_no_empty($params['q'])) {
$params['q'] = trim($params['q']);
if ($params['match'] == 'any') {
$pieces = fn_explode(' ', $params['q']);
$search_type = ' OR ';
} elseif ($params['match'] == 'all') {
$pieces = fn_explode(' ', $params['q']);
$search_type = ' AND ';
} else {
$pieces = array($params['q']);
$search_type = '';
}
$_condition = array();
foreach ($pieces as $piece) {
if (strlen($piece) == 0) {
continue;
}
$pieceTrimed = str_replace(array(' ', '-'), '', $piece);
$tmp = db_quote("(descr1.search_words LIKE ?l)", "%{$piece}%");
// check search words
if ($params['pname'] == 'Y') {
$tmp .= db_quote(" OR MATCH (product) AGAINST ('" . mysql_real_escape_string($piece) . "')");
//$tmp .= db_quote(" OR MATCH (product) AGAINST ('$piece')");
$tmp .= db_quote(" OR REPLACE( REPLACE(upper(descr1.product), ' ', '' ) , '-', '' ) LIKE ?l", "%{$pieceTrimed}%");
$relevanceField = " ,MATCH(product) AGAINST ('" . mysql_real_escape_string($piece) . "' IN BOOLEAN MODE) AS relevance";
$relevanceOrder = "relevance DESC,";
}
if ($params['pshort'] == 'Y') {
//$tmp .= db_quote(" OR descr1.short_description LIKE ?l", "%$piece%");
}
if ($params['pfull'] == 'Y') {
//$tmp .= db_quote(" OR descr1.full_description LIKE ?l", "%$piece%");
}
if ($params['pkeywords'] == 'Y') {
// $tmp .= db_quote(" OR (descr1.meta_keywords LIKE ?l OR descr1.meta_description LIKE ?l)", "%$piece%", "%$piece%");
}
if (!empty($params['feature']) && $params['action'] != 'feature_search') {
// $tmp .= db_quote(" OR ?:product_features_values.value LIKE ?l", "%$piece%");
}
$tmp .= db_quote(" OR products.product_code LIKE ?l", "%{$piece}%");
fn_set_hook('additional_fields_in_search', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $tmp);
$_condition[] = '(' . $tmp . ')';
}
$_cond = implode($search_type, $_condition);
if (!empty($_condition)) {
$condition .= ' AND (' . $_cond . ') ';
}
if (!empty($params['feature']) && $params['action'] != 'feature_search') {
$join .= " LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id";
$condition .= db_quote(" AND (?:product_features_values.feature_id IN (?n) OR ?:product_features_values.feature_id IS NULL)", array_values($params['feature']));
}
unset($_condition);
}
//
// [Advanced and feature filters]
//
if (!empty($params['apply_limit']) && $params['apply_limit']) {
$pids = array();
foreach ($params['pid'] as $pid) {
if ($pid != $params['exclude_pid']) {
if (count($pids) == $params['limit']) {
break;
} else {
$pids[] = $pid;
}
}
}
$params['pid'] = $pids;
}
if (!empty($params['features_hash']) || !fn_is_empty($params['variants'])) {
$join .= db_quote(" LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id AND ?:product_features_values.lang_code = ?s", CART_LANGUAGE);
}
if (!empty($params['variants'])) {
$params['features_hash'] .= implode('.', $params['variants']);
}
$advanced_variant_ids = $simple_variant_ids = $ranges_ids = $fields_ids = array();
if (!empty($params['features_hash'])) {
if (!empty($params['advanced_filter'])) {
list($av_ids, $ranges_ids, $fields_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);
} else {
list($simple_variant_ids, $ranges_ids, $fields_ids) = fn_parse_features_hash($params['features_hash']);
}
}
if (!empty($params['multiple_variants']) && !empty($params['advanced_filter'])) {
$simple_variant_ids = $params['multiple_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) . ")";
}
$condition .= ' AND ' . implode(' AND ', $where_and_conditions);
}
if (!empty($simple_variant_ids)) {
$join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS simple_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_simple ON pfv_simple.product_id = products.product_id", CART_LANGUAGE);
$where_conditions = array();
foreach ($simple_variant_ids as $k => $variant_id) {
$where_conditions[] = db_quote(" FIND_IN_SET('?i', simple_variants)", $variant_id);
}
$condition .= ' AND ' . implode(' AND ', $where_conditions);
}
//
// Ranges from text inputs
//
// Feature ranges
if (!empty($params['custom_range'])) {
foreach ($params['custom_range'] as $k => $v) {
$k = intval($k);
if (fn_string_no_empty($v['from']) || fn_string_no_empty($v['to'])) {
if (!empty($v['type'])) {
if ($v['type'] == 'D') {
$v['from'] = fn_parse_date($v['from']);
$v['to'] = fn_parse_date($v['to']);
}
}
$join .= db_quote(" LEFT JOIN ?:product_features_values as custom_range_{$k} ON custom_range_{$k}.product_id = products.product_id AND custom_range_{$k}.lang_code = ?s", CART_LANGUAGE);
if (fn_string_no_empty($v['from']) && fn_string_no_empty($v['to'])) {
$condition .= db_quote(" AND (custom_range_{$k}.value_int >= ?i AND custom_range_{$k}.value_int <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i) ", $v['from'], $v['to'], $k);
} else {
$condition .= " AND custom_range_{$k}.value_int" . (fn_string_no_empty($v['from']) ? db_quote(' >= ?i', $v['from']) : db_quote(" <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i ", $v['to'], $k));
}
}
}
}
// Product field ranges
$filter_fields = fn_get_product_filter_fields();
if (!empty($params['field_range'])) {
foreach ($params['field_range'] as $field_type => $v) {
$structure = $filter_fields[$field_type];
if (!empty($structure) && (!empty($v['from']) || !empty($v['to']))) {
$params["{$structure['db_field']}_from"] = trim($v['from']);
$params["{$structure['db_field']}_to"] = trim($v['to']);
}
}
}
// Ranges from database
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 => $range_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);
$condition .= 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) ", $range_condition['from'], $range_condition['to'], $range_condition['feature_id']);
}
}
// Field ranges
$fields_ids = empty($params['fields_ids']) ? $fields_ids : $params['fields'];
if (!empty($fields_ids)) {
foreach ($fields_ids as $rid => $field_type) {
$structure = $filter_fields[$field_type];
if ($structure['condition_type'] == 'D') {
$range_condition = db_get_row("SELECT `from`, `to`, range_id FROM ?:product_filter_ranges WHERE range_id = ?i", $rid);
if (!empty($range_condition)) {
$params["{$structure['db_field']}_from"] = $range_condition['from'];
$params["{$structure['db_field']}_to"] = $range_condition['to'];
}
} elseif ($structure['condition_type'] == 'F') {
$params[$structure['db_field']] = $rid;
} elseif ($structure['condition_type'] == 'C') {
$params[$structure['db_field']] = $rid == 1 ? 'Y' : 'N';
}
}
}
// Checkbox features
if (!empty($params['ch_filters']) && !fn_is_empty($params['ch_filters'])) {
foreach ($params['ch_filters'] as $k => $v) {
// Product field filter
if (is_string($k) == true && !empty($v) && ($structure = $filter_fields[$k])) {
$condition .= db_quote(" AND {$structure['table']}.{$structure['db_field']} IN (?a)", $v == 'A' ? array('Y', 'N') : $v);
// Feature filter
} elseif (!empty($v)) {
$fid = intval($k);
$join .= db_quote(" LEFT JOIN ?:product_features_values as ch_features_{$fid} ON ch_features_{$fid}.product_id = products.product_id AND ch_features_{$fid}.lang_code = ?s", CART_LANGUAGE);
$condition .= db_quote(" AND ch_features_{$fid}.feature_id = ?i AND ch_features_{$fid}.value IN (?a)", $fid, $v == 'A' ? array('Y', 'N') : $v);
}
}
}
// Text features
if (!empty($params['tx_features'])) {
foreach ($params['tx_features'] as $k => $v) {
if (fn_string_no_empty($v)) {
$fid = intval($k);
$join .= " LEFT JOIN ?:product_features_values as tx_features_{$fid} ON tx_features_{$fid}.product_id = products.product_id";
$condition .= db_quote(" AND tx_features_{$fid}.value LIKE ?l AND tx_features_{$fid}.lang_code = ?s", "%" . trim($v) . "%", CART_LANGUAGE);
}
}
}
//
// [/Advanced filters]
//
$feature_search_condition = '';
if (!empty($params['feature'])) {
// Extended search by product fields
$_cond = array();
$total_hits = 0;
foreach ($params['feature'] as $f_id) {
if (!empty($f_val)) {
$total_hits++;
$_cond[] = db_quote("(?:product_features_values.feature_id = ?i)", $f_id);
}
}
if (!empty($_cond)) {
$cache_feature_search = db_get_fields("SELECT product_id, COUNT(product_id) as cnt FROM ?:product_features_values WHERE (" . implode(' OR ', $_cond) . ") GROUP BY product_id HAVING cnt = {$total_hits}");
$feature_search_condition .= db_quote(" AND products_categories.product_id IN (?n)", $cache_feature_search);
}
}
// Category search condition for SQL query
if (!empty($params['cid'])) {
$cids = is_array($params['cid']) ? $params['cid'] : array($params['cid']);
if (!empty($params['subcats']) && $params['subcats'] == 'Y') {
$_ids = db_get_fields("SELECT a.category_id FROM ?:categories as a LEFT JOIN ?:categories as b ON b.category_id IN (?n) WHERE a.id_path LIKE CONCAT(b.id_path, '/%')", $cids);
$cids = fn_array_merge($cids, $_ids, false);
}
$condition .= db_quote(" AND ?:categories.category_id IN (?n)", $cids);
}
// If we need to get the products by IDs and no IDs passed, don't search anything
if (!empty($params['force_get_by_ids']) && empty($params['pid']) && empty($params['product_id'])) {
return array(array(), array(), 0);
}
// Product ID search condition for SQL query
if (!empty($params['pid'])) {
if (isset($params['doLinks']) && $params['doLinks'] == 1) {
$condition .= db_quote(' AND products.product_id IN (' . $params['pid'] . ')', $params['pid']);
} else {
$condition .= db_quote(' AND products.product_id IN (?n)', $params['pid']);
}
}
// Exclude products from search results
if (!empty($params['exclude_pid'])) {
$condition .= db_quote(' AND products.product_id NOT IN (?n)', $params['exclude_pid']);
}
// Search by feature comparison flag
if (!empty($params['feature_comparison'])) {
$condition .= db_quote(' AND products.feature_comparison = ?s', $params['feature_comparison']);
}
// Search products by localization
$condition .= fn_get_localizations_condition('products.localization', true);
$condition .= fn_get_localizations_condition('?:categories.localization', true);
if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) {
$condition .= db_quote(' AND prices.price >= ?d', fn_convert_price(trim($params['price_from'])));
}
if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) {
$condition .= db_quote(' AND prices.price <= ?d', fn_convert_price(trim($params['price_to'])));
}
if (isset($params['weight_from']) && fn_is_numeric($params['weight_from'])) {
$condition .= db_quote(' AND products.weight >= ?d', fn_convert_weight(trim($params['weight_from'])));
}
if (isset($params['weight_to']) && fn_is_numeric($params['weight_to'])) {
$condition .= db_quote(' AND products.weight <= ?d', fn_convert_weight(trim($params['weight_to'])));
}
// search specific inventory status
if (!empty($params['search_tracking_flags'])) {
$condition .= db_quote(' AND products.tracking IN(?a)', $params['search_tracking_flags']);
}
if (isset($params['amount_from']) && fn_is_numeric($params['amount_from'])) {
$condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount >= ?i, products.amount >= ?i)", $params['amount_from'], $params['amount_from']);
$inventory_condition .= db_quote(' AND inventory.amount >= ?i', $params['amount_from']);
}
if (isset($params['amount_to']) && fn_is_numeric($params['amount_to'])) {
$condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount <= ?i, products.amount <= ?i)", $params['amount_to'], $params['amount_to']);
$inventory_condition .= db_quote(' AND inventory.amount <= ?i', $params['amount_to']);
}
if (Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') {
// FIXME? Registry in model
$condition .= " AND IF(products.tracking = 'O', inventory.amount > 0, products.amount > 0)";
}
if (!empty($params['status'])) {
$condition .= db_quote(' AND products.status IN (?a)', $params['status']);
}
if (!empty($params['shipping_freight_from'])) {
$condition .= db_quote(' AND products.shipping_freight >= ?d', $params['shipping_freight_from']);
}
if (!empty($params['shipping_freight_to'])) {
$condition .= db_quote(' AND products.shipping_freight <= ?d', $params['shipping_freight_to']);
}
if (!empty($params['free_shipping'])) {
$condition .= db_quote(' AND products.free_shipping = ?s', $params['free_shipping']);
}
if (!empty($params['downloadable'])) {
$condition .= db_quote(' AND products.is_edp = ?s', $params['downloadable']);
}
if (!empty($params['b_id'])) {
$join .= " LEFT JOIN ?:block_links ON ?:block_links.object_id = products.product_id AND ?:block_links.location = 'products'";
$condition .= db_quote(' AND ?:block_links.block_id = ?i', $params['b_id']);
}
if (isset($params['pcode']) && fn_string_no_empty($params['pcode'])) {
$pcode = trim($params['pcode']);
$fields[] = 'inventory.combination';
$condition .= db_quote(" AND (inventory.product_code LIKE ?l OR products.product_code LIKE ?l)", "%{$pcode}%", "%{$pcode}%");
$inventory_condition .= db_quote(" AND inventory.product_code LIKE ?l", "%{$pcode}%");
}
if (isset($params['amount_to']) && fn_is_numeric($params['amount_to']) || isset($params['amount_from']) && fn_is_numeric($params['amount_from']) || !empty($params['pcode']) || Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') {
$join .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = products.product_id {$inventory_condition}";
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (products.timestamp >= ?i AND products.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
if (!empty($params['item_ids'])) {
$condition .= db_quote(" AND products.product_id IN (?n)", explode(',', $params['item_ids']));
}
if (isset($params['popularity_from']) && fn_is_numeric($params['popularity_from'])) {
$condition .= db_quote(' AND popularity.total >= ?i', $params['popularity_from']);
}
if (isset($params['popularity_to']) && fn_is_numeric($params['popularity_to'])) {
$condition .= db_quote(' AND popularity.total <= ?i', $params['popularity_to']);
}
// Extended search mode condition for SQL query
if ($params['type'] == 'extended') {
array_push($fields, 'products.product_code', 'products.amount', 'MIN(prices.price) as price', 'products.status', 'products.list_price', 'products.temp_price', 'descr1.short_description', "IF(descr1.short_description = '', descr1.full_description, '') as full_description");
if (!empty($params['cid'])) {
$fields[] = 'products_categories.position';
}
}
$price_usergroup_cond = db_quote(' AND prices.usergroup_id IN (?n)', AREA == 'A' ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids']));
$condition .= $price_usergroup_cond;
$price_usergroup_cond_2 = str_replace('prices', 'prices_2', $price_usergroup_cond);
$join .= " LEFT JOIN ?:product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN ?:product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price " . $price_usergroup_cond_2;
$condition .= ' AND prices_2.price IS NULL';
// Show enabled products/categories
$_p_statuses = array('A');
$_c_statuses = array('A', 'H');
$avail_cond = AREA == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], '?:categories.usergroup_ids', true) . ')' : '';
$avail_cond .= AREA == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], 'products.usergroup_ids', true) . ')' : '';
$avail_cond .= AREA == 'C' ? db_quote(" AND ?:categories.status IN (?a) AND products.status IN (?a)", $_c_statuses, $_p_statuses) : '';
$join .= " INNER JOIN ?:products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN ?:categories ON ?:categories.category_id = products_categories.category_id {$avail_cond} {$feature_search_condition}";
$limit = '';
$group_by = 'products.product_id';
fn_set_hook('get_products', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $lang_code);
if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) {
$params['sort_by'] = Registry::get('settings.Appearance.default_products_sorting');
if (empty($sortings[$params['sort_by']])) {
$_products_sortings = fn_get_products_sorting(false);
$params['sort_by'] = key($_products_sortings);
}
}
if ($params['type'] != 'extended' && $params['sort_by'] == 'price') {
$params['sort_by'] = 'product';
}
$default_sorting = fn_get_products_sorting(false);
if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) {
if (!empty($default_sorting[$params['sort_by']]['default_order'])) {
$params['sort_order'] = $default_sorting[$params['sort_by']]['default_order'];
} else {
$params['sort_order'] = 'asc';
}
}
if (AREA == 'A' || $params['sort_by'] == 'popularity') {
$fields[] = 'popularity.total as popularity';
$join .= db_quote(" LEFT JOIN ?:product_popularity as popularity ON popularity.product_id = products.product_id");
}
$sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']];
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
// Used for View cascading
if (!empty($params['get_query'])) {
return "SELECT products.product_id FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY products.product_id";
}
// Used for Extended search
if (!empty($params['get_conditions'])) {
return array($fields, $join, $condition);
}
if (!empty($params['limit'])) {
$limit = db_quote(" LIMIT 0, ?i", $params['limit']);
}
$total = 0;
if (!empty($items_per_page)) {
if (!empty($params['limit']) && $total > $params['limit']) {
$total = $params['limit'];
}
$limit = fn_paginate($params['page'], 0, $items_per_page, true);
}
if (isset($_GET['no_pagination'])) {
ini_set('memory_limit', '128M');
$limit = db_quote("");
}
if (isset($params['doLinks']) && $params['doLinks'] == 1) {
$sorting = "products.amount DESC, descr1.product ASC";
}
$sqlquery = 'SELECT SQL_CALC_FOUND_ROWS ' . implode(', ', $fields) . ",products.amount {$relevanceField} FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY {$group_by} ORDER BY {$relevanceOrder} `products`.`amount` DESC, {$sorting} {$limit}";
$products = db_get_array($sqlquery);
if (!empty($items_per_page)) {
$total = db_get_found_rows();
fn_paginate($params['page'], $total, $items_per_page);
} else {
$total = count($products);
}
// Post processing
foreach ($products as $k => $v) {
$products[$k]['category_ids'] = fn_convert_categories($v['category_ids']);
}
if (!empty($params['item_ids'])) {
$products = fn_sort_by_ids($products, explode(',', $params['item_ids']));
}
if (!empty($params['pid']) && !empty($params['apply_limit']) && $params['apply_limit']) {
$products = fn_sort_by_ids($products, $params['pid']);
}
fn_set_hook('get_products_post', $products);
return array($products, $params, $total);
}
