function fn_bestsellers_get_products(&$params, &$fields, &$sortings, &$condition, &$join, &$sorting, &$group_by, &$lang_code, &$having) { if (!empty($params['bestsellers'])) { $fields[] = 'SUM(?:product_sales.amount) as sales_amount'; $sortings['sales_amount'] = 'sales_amount'; $join .= ' INNER JOIN ?:product_sales ON ?:product_sales.product_id = products.product_id AND ?:product_sales.category_id = products_categories.category_id '; $group_by = '?:product_sales.product_id'; if (!empty($params['category_id'])) { $condition .= db_quote(" AND ?:product_sales.category_id = ?i", $params['category_id']); } } elseif (!empty($params['on_sale'])) { $fields[] = '100 - ((prices.price * 100) / list_price) AS sales_discount'; $sortings['on_sale'] = 'sales_discount'; $having[] = db_quote('sales_discount > 0'); } $sortings['bestsellers'] = '?:product_sales.amount'; $sortings['on_sale'] = 'sales_discount'; if (isset($params['sales_amount_from']) && fn_is_numeric($params['sales_amount_from'])) { $condition .= db_quote(' AND ?:product_sales.amount >= ?i', trim($params['sales_amount_from'])); } if (isset($params['sales_amount_to']) && fn_is_numeric($params['sales_amount_to'])) { $condition .= db_quote(' AND ?:product_sales.amount <= ?i', trim($params['sales_amount_to'])); } if (in_array('sales', $params['extend']) && empty($params['bestsellers'])) { $join .= ' LEFT JOIN ?:product_sales ON ?:product_sales.product_id = products.product_id AND ?:product_sales.category_id = products_categories.category_id '; } elseif (in_array('on_sale', $params['extend']) && empty($params['on_sale'])) { $fields[] = '100 - ((prices.price * 100) / list_price) AS sales_discount'; } return true; }
function fn_bestsellers_get_products(&$params, &$fields, &$sortings, &$condition, &$join, &$sorting, &$group_by) { if (!empty($params['bestsellers'])) { $fields[] = 'SUM(?:product_sales.amount) as sales_amount'; $sortings['sales_amount'] = 'sales_amount'; $join .= ' INNER JOIN ?:product_sales ON ?:product_sales.product_id = products.product_id AND ?:product_sales.category_id = products_categories.category_id '; $group_by = '?:product_sales.product_id'; if (!empty($params['category_id'])) { $condition .= db_quote(" AND ?:product_sales.category_id = ?i", $params['category_id']); } } else { $join .= ' LEFT JOIN ?:product_sales ON ?:product_sales.product_id = products.product_id AND ?:product_sales.category_id = products_categories.category_id '; } $sortings['bestsellers'] = '?:product_sales.amount'; if (isset($params['sales_amount_from']) && fn_is_numeric($params['sales_amount_from'])) { $condition .= db_quote(' AND ?:product_sales.amount >= ?i', trim($params['sales_amount_from'])); } if (isset($params['sales_amount_to']) && fn_is_numeric($params['sales_amount_to'])) { $condition .= db_quote(' AND ?:product_sales.amount <= ?i', trim($params['sales_amount_to'])); } return true; }
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); }
/** * Returns orders * * @param array $params array with search params * @param int $items_per_page * @param bool $get_totals * @param string $lang_code * @return array */ function fn_get_orders($params, $items_per_page = 0, $get_totals = false, $lang_code = CART_LANGUAGE) { // Init filter $params = LastView::instance()->update('orders', $params); // Set default values to input params $default_params = array('page' => 1, 'items_per_page' => $items_per_page); $params = array_merge($default_params, $params); if (AREA != 'C') { $params['include_incompleted'] = empty($params['include_incompleted']) ? false : $params['include_incompleted']; // default incomplited orders should not be displayed if (!empty($params['status']) && (is_array($params['status']) && in_array(STATUS_INCOMPLETED_ORDER, $params['status']) || !is_array($params['status']) && $params['status'] == STATUS_INCOMPLETED_ORDER)) { $params['include_incompleted'] = true; } } else { $params['include_incompleted'] = false; } // Define fields that should be retrieved $fields = array("distinct ?:orders.order_id", "?:orders.issuer_id", "?:orders.user_id", "?:orders.is_parent_order", "?:orders.parent_order_id", "?:orders.company_id", "?:orders.timestamp", "?:orders.firstname", "?:orders.lastname", "?:orders.email", "?:orders.phone", "?:orders.status", "?:orders.total", "invoice_docs.doc_id as invoice_id", "memo_docs.doc_id as credit_memo_id"); // Define sort fields $sortings = array('order_id' => "?:orders.order_id", 'status' => "?:orders.status", 'customer' => array("?:orders.lastname", "?:orders.firstname"), 'email' => "?:orders.email", 'date' => array("?:orders.timestamp", "?:orders.order_id"), 'total' => "?:orders.total"); fn_set_hook('pre_get_orders', $params, $fields, $sortings, $get_totals, $lang_code); if (isset($params['compact']) && $params['compact'] == 'Y') { $union_condition = ' OR '; } else { $union_condition = ' AND '; } $condition = $_condition = $join = $group = ''; $condition .= " AND ?:orders.is_parent_order != 'Y' "; $condition .= fn_get_company_condition('?:orders.company_id'); if (isset($params['cname']) && fn_string_not_empty($params['cname'])) { $customer_name = fn_explode(' ', $params['cname']); $customer_name = array_filter($customer_name, "fn_string_not_empty"); if (sizeof($customer_name) == 2) { $_condition .= db_quote(" {$union_condition} ?:orders.firstname LIKE ?l AND ?:orders.lastname LIKE ?l", "%" . array_shift($customer_name) . "%", "%" . array_shift($customer_name) . "%"); } else { $_condition .= db_quote(" {$union_condition} (?:orders.firstname LIKE ?l OR ?:orders.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['issuer']) && fn_string_not_empty($params['issuer'])) { $issuer_name = fn_explode(' ', $params['issuer']); $issuer_name = array_filter($issuer_name, "fn_string_not_empty"); if (sizeof($issuer_name) == 2) { $issuer_ids = db_get_field("SELECT user_id FROM ?:users WHERE user_type IN ('A', 'V') AND firstname LIKE ?l AND lastname LIKE ?l", "%" . array_shift($issuer_name) . "%", "%" . array_shift($issuer_name) . "%"); } else { $issuer_ids = db_get_field("SELECT user_id FROM ?:users WHERE user_type IN ('A', 'V') AND (firstname LIKE ?l OR lastname LIKE ?l)", "%" . trim($params['issuer']) . "%", "%" . trim($params['issuer']) . "%"); } $_condition .= db_quote(" {$union_condition} ?:orders.issuer_id IN (?a)", $issuer_ids); } if (isset($params['company_id']) && $params['company_id'] != '') { $condition .= db_quote(' AND ?:orders.company_id = ?i ', $params['company_id']); } if (!empty($params['tax_exempt'])) { $condition .= db_quote(" AND ?:orders.tax_exempt = ?s", $params['tax_exempt']); } if (isset($params['email']) && fn_string_not_empty($params['email'])) { $_condition .= db_quote(" {$union_condition} ?:orders.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (!empty($params['user_id'])) { $condition .= db_quote(' AND ?:orders.user_id IN (?n)', $params['user_id']); } if (isset($params['total_from']) && fn_is_numeric($params['total_from'])) { $condition .= db_quote(" AND ?:orders.total >= ?d", fn_convert_price($params['total_from'])); } if (!empty($params['total_to']) && fn_is_numeric($params['total_to'])) { $condition .= db_quote(" AND ?:orders.total <= ?d", fn_convert_price($params['total_to'])); } if (!empty($params['status'])) { $condition .= db_quote(' AND ?:orders.status IN (?a)', $params['status']); } if (empty($params['include_incompleted'])) { $condition .= db_quote(' AND ?:orders.status != ?s', STATUS_INCOMPLETED_ORDER); } if (!empty($params['order_id'])) { $_condition .= db_quote($union_condition . ' ?:orders.order_id IN (?n)', !is_array($params['order_id']) && strpos($params['order_id'], ',') !== false ? explode(',', $params['order_id']) : $params['order_id']); } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"; $group .= " GROUP BY ?:orders.order_id "; } if (!empty($params['admin_user_id'])) { $condition .= db_quote(" AND ?:new_orders.user_id = ?i", $params['admin_user_id']); $join .= " LEFT JOIN ?:new_orders ON ?:new_orders.order_id = ?:orders.order_id"; } $docs_conditions = array(); if (!empty($params['invoice_id']) || !empty($params['has_invoice'])) { if (!empty($params['has_invoice'])) { $docs_conditions[] = "invoice_docs.doc_id IS NOT NULL"; } elseif (!empty($params['invoice_id'])) { $docs_conditions[] = db_quote("invoice_docs.doc_id = ?i", $params['invoice_id']); } } $join .= " LEFT JOIN ?:order_docs as invoice_docs ON invoice_docs.order_id = ?:orders.order_id AND invoice_docs.type = 'I'"; if (!empty($params['credit_memo_id']) || !empty($params['has_credit_memo'])) { if (!empty($params['has_credit_memo'])) { $docs_conditions[] = "memo_docs.doc_id IS NOT NULL"; } elseif (!empty($params['credit_memo_id'])) { $docs_conditions[] = db_quote("memo_docs.doc_id = ?i", $params['credit_memo_id']); } } $join .= " LEFT JOIN ?:order_docs as memo_docs ON memo_docs.order_id = ?:orders.order_id AND memo_docs.type = 'C'"; if (!empty($docs_conditions)) { $condition .= ' AND (' . implode(' OR ', $docs_conditions) . ')'; } if (!empty($params['shippings'])) { $set_conditions = array(); foreach ($params['shippings'] as $v) { $set_conditions[] = db_quote("FIND_IN_SET(?s, ?:orders.shipping_ids)", $v); } $condition .= ' AND (' . implode(' OR ', $set_conditions) . ')'; } if (!empty($params['payments'])) { $condition .= db_quote(" AND ?:orders.payment_id IN (?a)", $params['payments']); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:orders.timestamp >= ?i AND ?:orders.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['custom_files']) && $params['custom_files'] == 'Y') { $condition .= db_quote(" AND ?:order_details.extra LIKE ?l", '%custom_files%'); if (empty($params['p_ids']) && empty($params['product_view_id'])) { $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"; } } if (!empty($params['company_name'])) { $fields[] = '?:companies.company as company_name'; $join .= " LEFT JOIN ?:companies ON ?:companies.company_id = ?:orders.company_id"; } if (!empty($_condition)) { $condition .= ' AND (' . ($union_condition == ' OR ' ? '0 ' : '1 ') . $_condition . ')'; } fn_set_hook('get_orders', $params, $fields, $sortings, $condition, $join, $group); $sorting = db_sort($params, $sortings, 'date', 'desc'); // Used for Extended search if (!empty($params['get_conditions'])) { return array($fields, $join, $condition); } $limit = ''; if (!empty($params['items_per_page'])) { $params['total_items'] = db_get_field("SELECT COUNT(DISTINCT (?:orders.order_id)) FROM ?:orders {$join} WHERE 1 {$condition}"); $limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']); } $orders = db_get_array('SELECT ' . implode(', ', $fields) . " FROM ?:orders {$join} WHERE 1 {$condition} {$group} {$sorting} {$limit}"); fn_set_hook('get_orders_post', $params, $orders); foreach ($orders as $k => $order) { if (isset($order['ip_address'])) { $order['ip_address'] = fn_ip_from_db($order['ip_address']); } } if ($get_totals == true) { $paid_statuses = array('P', 'C'); fn_set_hook('get_orders_totals', $paid_statuses, $join, $condition, $group); $totals = array('gross_total' => db_get_field("SELECT sum(t.total) FROM ( SELECT total FROM ?:orders {$join} WHERE 1 {$condition} {$group}) as t"), 'totally_paid' => db_get_field("SELECT sum(t.total) FROM ( SELECT total FROM ?:orders {$join} WHERE ?:orders.status IN (?a) {$condition} {$group}) as t", $paid_statuses)); $params['paid_statuses'] = $paid_statuses; } LastView::instance()->processResults('orders', $orders, $params); return array($orders, $params, $get_totals == true ? $totals : array()); }
function fn_se_prepare_request_params($params) { $restrict_by = $query_by = $union = array(); // // Hide products with empty categories and wrong usergroup categories // $restrict_by['empty_categories'] = 'N'; $restrict_by['category_usergroup_ids'] = join('|', Tygh::$app['session']['auth']['usergroup_ids']); // // Visibility // if (AREA == 'C') { $restrict_by['status'] = 'A'; if (!fn_allowed_for('ULTIMATE:FREE')) { $restrict_by['usergroup_ids'] = join('|', Tygh::$app['session']['auth']['usergroup_ids']); } if (Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') { $restrict_by['amount'] = '1,'; } // // Company_id // if (!fn_allowed_for('ULTIMATE') && !isset($restrict_by['company_id'])) { if (Registry::get('runtime.company_id') && isset($params['company_id'])) { $restrict_by['company_id'] = Registry::get('runtime.company_id'); } if (isset($params['company_id']) && $params['company_id'] != '') { $restrict_by['company_id'] = $params['company_id']; } } } // // Filters coditions for facets request // if (!empty($params['filters_category_id'])) { $c_condition = ''; if (AREA == 'C') { $_c_statuses = array('A', 'H'); // Show enabled categories $cids = db_get_fields("SELECT a.category_id FROM ?:categories as a WHERE a.category_id IN (?n) AND a.status IN (?a)", $params['filters_category_id'], $_c_statuses); $c_condition = db_quote('AND a.status IN (?a) AND (' . fn_find_array_in_set(Tygh::$app['session']['auth']['usergroup_ids'], 'a.usergroup_ids', true) . ')', $_c_statuses); } if (Registry::get('settings.General.show_products_from_subcategories') == 'Y') { $sub_categories_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, '/%') ?p", $params['filters_category_id'], $c_condition); $sub_categories_ids = fn_array_merge($cids, $sub_categories_ids, false); $restrict_by['category_id'] = join('|', $sub_categories_ids); } else { $restrict_by['category_id'] = join('|', $cids); } } // // Timestamp // if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $restrict_by['timestamp'] = "{$params['time_from']},{$params['time_to']}"; } // // Price Union // if (!fn_allowed_for('"ULTIMATE:FREE"')) { if (count(Tygh::$app['session']['auth']['usergroup_ids']) > 1) { foreach (Tygh::$app['session']['auth']['usergroup_ids'] as $usergroup_id) { $_prices[] = 'price_' . $usergroup_id; } $union['price']['min'] = join('|', $_prices); } } // // Price // $is_price_from = isset($params['price_from']) && fn_is_numeric($params['price_from']); $is_price_to = isset($params['price_to']) && fn_is_numeric($params['price_to']); if ($is_price_from || $is_price_to) { $restrict_by['price'] = ($is_price_from ? $params['price_from'] : '') . ',' . ($is_price_to ? $params['price_to'] : ''); } // // Weight // $is_weight_from = isset($params['weight_from']) && fn_is_numeric($params['weight_from']); $is_weight_to = isset($params['weight_to']) && fn_is_numeric($params['weight_to']); if ($is_weight_from || $is_weight_to) { $restrict_by['weight'] = ($is_weight_from ? $params['weight_from'] : '') . ',' . ($is_weight_to ? $params['weight_to'] : ''); } // // Amount // $is_amount_from = isset($params['amount_from']) && fn_is_numeric($params['amount_from']); $is_amount_to = isset($params['amount_to']) && fn_is_numeric($params['amount_to']); if ($is_amount_from || $is_amount_to) { $restrict_by['amount'] = ($is_amount_from ? $params['amount_from'] : '') . ',' . ($is_amount_to ? $params['amount_to'] : ''); } // // Popularity // $is_popularity_from = isset($params['popularity_from']) && fn_is_numeric($params['popularity_from']); $is_popularity_to = isset($params['popularity_to']) && fn_is_numeric($params['popularity_to']); if ($is_popularity_from || $is_popularity_to) { $restrict_by['popularity'] = ($is_popularity_from ? $params['popularity_from'] : '') . ',' . ($is_popularity_to ? $params['popularity_to'] : ''); } if (!empty($params['free_shipping'])) { $restrict_by['free_shipping'] = $params['free_shipping']; } if (isset($params['pcode']) && fn_string_not_empty($params['pcode'])) { if (empty($params['pcode_from_q'])) { // FIXME This is a workaround. See @1-14957 for details. $query_by['product_code'] = trim($params['pcode']); } } return array($restrict_by, $query_by, $union); }
function fn_get_carts($params, $items_per_page = 0) { // Init filter $params = LastView::instance()->update('carts', $params); // Set default values to input params $default_params = array('page' => 1, 'items_per_page' => $items_per_page); $params = array_merge($default_params, $params); // Define fields that should be retrieved $fields = array('?:user_session_products.user_id', '?:users.firstname', '?:users.lastname', '?:user_session_products.timestamp AS date'); // Define sort fields $sortings = array('customer' => "CONCAT(?:users.lastname, ?:users.firstname)", 'date' => "?:user_session_products.timestamp"); if (fn_allowed_for('ULTIMATE')) { $sortings['company_id'] = "?:user_session_products.company_id"; } $sorting = db_sort($params, $sortings, 'customer', 'asc'); $condition = $join = ''; $group = " GROUP BY ?:user_session_products.user_id"; $group_post = ''; if (isset($params['cname']) && fn_string_not_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_not_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_not_empty($params['email'])) { $condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (!empty($params['user_id'])) { $condition .= db_quote(" AND ?:user_session_products.user_id = ?i", $params['user_id']); } if (!empty($params['online_only'])) { $sessions = Session::getOnline('C'); if (!empty($sessions)) { $condition .= db_quote(" AND ?:user_session_products.session_id IN (?a)", $sessions); } else { $condition .= db_quote(" AND 0"); } } if (!empty($params['with_info_only'])) { $condition .= db_quote(" AND ?:users.email != ''"); } if (!empty($params['users_type'])) { if ($params['users_type'] == 'R') { $condition .= db_quote(" AND !ISNULL(?:users.user_id)"); } elseif ($params['users_type'] == 'G') { $condition .= db_quote(" AND ISNULL(?:users.user_id)"); } } if (!empty($params['total_from']) || !empty($params['total_to'])) { $having = ''; if (fn_is_numeric($params['total_from'])) { $having .= db_quote(" AND SUM(price * amount) >= ?d", $params['total_from']); } if (fn_is_numeric($params['total_to'])) { $having .= db_quote(" AND SUM(price * amount) <= ?d", $params['total_to']); } if (!empty($having)) { $users4total = db_get_fields("SELECT user_id FROM ?:user_session_products GROUP BY user_id HAVING 1 {$having}"); if (!empty($users4total)) { $condition .= db_quote(" AND (?:user_session_products.user_id IN (?n))", $users4total); } else { $condition .= " AND (?:user_session_products.user_id = 'no')"; } } } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:user_session_products.timestamp >= ?i AND ?:user_session_products.timestamp <= ?i)", $params['time_from'], $params['time_to']); } $_condition = array(); if (!empty($params['product_type_c'])) { $_condition[] = "?:user_session_products.type = 'C'"; } if (!empty($params['product_type_w']) && $params['product_type_w'] == 'Y') { $_condition[] = "?:user_session_products.type = 'W'"; } if (!empty($_condition)) { $condition .= " AND (" . implode(" OR ", $_condition) . ")"; } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $group_post .= " HAVING COUNT(?:user_session_products.user_id) >= " . count($arr); } $join .= " LEFT JOIN ?:users ON ?:user_session_products.user_id = ?:users.user_id"; // checking types for retrieving from the database $type_restrictions = array('C'); fn_set_hook('get_carts', $type_restrictions, $params, $condition, $join, $fields, $group, $array_index_field); if (!empty($type_restrictions) && is_array($type_restrictions)) { $condition .= " AND ?:user_session_products.type IN ('" . implode("', '", $type_restrictions) . "')"; } $carts_list = array(); $group .= $group_post; $limit = ''; if (!empty($params['items_per_page'])) { $limit = db_paginate($params['page'], $params['items_per_page']); } if (fn_allowed_for('ULTIMATE')) { $group = " GROUP BY ?:user_session_products.user_id, ?:user_session_products.company_id"; } $carts_list = db_get_array("SELECT SQL_CALC_FOUND_ROWS " . implode(', ', $fields) . " FROM ?:user_session_products {$join} WHERE 1 {$condition} {$group} {$sorting} {$limit}"); if (!empty($params['items_per_page'])) { $params['total_items'] = db_get_found_rows(); } unset($_SESSION['abandoned_carts']); return array($carts_list, $params); }
function fn_se_prepare_request_params($params) { $restrict_by = $query_by = $union = array(); // // Hide products with empty categories and wrong usergroup categories // $restrict_by['empty_categories'] = 'N'; $restrict_by['category_usergroup_ids'] = join('|', $_SESSION['auth']['usergroup_ids']); // // Filters // $filter_fields = fn_get_product_filter_fields(); $advanced_variant_ids = $simple_variant_ids = $av_ids = $ranges_ids = $fields_ids = $slider_vals = array(); if (!empty($params['features_hash'])) { list($av_ids, $ranges_ids, $fields_ids, $slider_vals) = fn_parse_features_hash($params['features_hash']); } if (!empty($params['multiple_variants']) && !empty($params['advanced_filter'])) { $simple_variant_ids = $params['multiple_variants']; } if (!empty($av_ids)) { $features_variants_ids = db_get_hash_single_array("SELECT feature_id, GROUP_CONCAT(variant_id) as variant_ids FROM ?:product_feature_variants WHERE variant_id IN (?n) GROUP BY feature_id", array('feature_id', 'variant_ids'), $av_ids); foreach ($features_variants_ids as $feature_id => $variant_ids) { $restrict_by['feature_' . $feature_id] = str_replace(',', '|', $variant_ids); } } if (!empty($simple_variant_ids)) { $features_variants_ids = db_get_hash_single_array("SELECT feature_id, GROUP_CONCAT(variant_id) as variant_ids FROM ?:product_feature_variants WHERE variant_id IN (?n) GROUP BY feature_id", array('feature_id', 'variant_ids'), $simple_variant_ids); foreach ($features_variants_ids as $feature_id => $variant_ids) { $restrict_by['feature_' . $feature_id] = $variant_ids; } } // Feature ranges if (!empty($params['custom_range'])) { foreach ($params['custom_range'] as $feature_id => $v) { $is_from = isset($v['from']) && fn_string_not_empty($v['from']); $is_to = isset($v['to']) && fn_string_not_empty($v['to']); if ($is_from || $is_to) { if (!empty($v['type'])) { if ($v['type'] == 'D') { $v['from'] = fn_parse_date($v['from']); $v['to'] = fn_parse_date($v['to']); } } $restrict_by['feature_' . $feature_id] = ($is_from ? $v['from'] : '') . ',' . ($is_to ? $v['to'] : ''); } } } // Product field ranges 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']); } } } foreach ($ranges_ids as $range_id) { $range = db_get_row("SELECT * FROM ?:product_filter_ranges WHERE range_id = ?i", $range_id); if (!empty($range)) { $feature = 'feature_' . $range['feature_id']; $restrict_by[$feature] = empty($restrict_by[$feature]) ? "{$range['from']},{$range['to']}" : $restrict_by[$feature] . "|{$range['from']},{$range['to']}"; } } foreach ($fields_ids as $range_id => $field_type) { $feature = $filter_fields[$field_type]['db_field']; if ($field_type == 'S') { $restrict_by[$feature] = empty($restrict_by[$feature]) ? $range_id : $restrict_by[$feature] . "|{$range_id}"; } if ($field_type == 'F') { $restrict_by[$feature] = $range_id == '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 $key => $value) { if (is_string($key) && !empty($filter_fields[$key])) { $restrict_by[$filter_fields[$key]['db_field']] = $value == 'A' ? 'Y|N' : $value; } else { if (!empty($value)) { $feature_id = $key; $restrict_by['feature_' . $feature_id] = $value == 'A' ? 'Y|N' : $value; } } } } // // Visibility // if (AREA == 'C') { $restrict_by['status'] = 'A'; if (!fn_allowed_for('ULTIMATE:FREE')) { $restrict_by['usergroup_ids'] = join('|', $_SESSION['auth']['usergroup_ids']); } if (Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') { $restrict_by['amount'] = '1,'; } // // Company_id // if (!fn_allowed_for('ULTIMATE') && !isset($restrict_by['company_id'])) { if (Registry::get('runtime.company_id') && isset($params['company_id'])) { $restrict_by['company_id'] = Registry::get('runtime.company_id'); } if (isset($params['company_id']) && $params['company_id'] != '') { $restrict_by['company_id'] = $params['company_id']; } } } // // Filters coditions for facets request // if (!empty($params['filters_category_id'])) { $c_condition = ''; if (AREA == 'C') { $_c_statuses = array('A', 'H'); // Show enabled categories $cids = db_get_fields("SELECT a.category_id FROM ?:categories as a WHERE a.category_id IN (?n) AND a.status IN (?a)", $params['filters_category_id'], $_c_statuses); $c_condition = db_quote('AND a.status IN (?a) AND (' . fn_find_array_in_set($_SESSION['auth']['usergroup_ids'], 'a.usergroup_ids', true) . ')', $_c_statuses); } if (Registry::get('settings.General.show_products_from_subcategories') == 'Y') { $sub_categories_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, '/%') ?p", $params['filters_category_id'], $c_condition); $sub_categories_ids = fn_array_merge($cids, $sub_categories_ids, false); $restrict_by['category_id'] = join('|', $sub_categories_ids); } else { $restrict_by['category_id'] = join('|', $cids); } } // // Timestamp // if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $restrict_by['timestamp'] = "{$params['time_from']},{$params['time_to']}"; } // // Price Union // if (!fn_allowed_for('"ULTIMATE:FREE"')) { if (count($_SESSION['auth']['usergroup_ids']) > 1) { foreach ($_SESSION['auth']['usergroup_ids'] as $usergroup_id) { $_prices[] = 'price_' . $usergroup_id; } $union['price']['min'] = join('|', $_prices); } } // // Price // $is_price_from = isset($params['price_from']) && fn_is_numeric($params['price_from']); $is_price_to = isset($params['price_to']) && fn_is_numeric($params['price_to']); if ($is_price_from || $is_price_to) { $restrict_by['price'] = ($is_price_from ? $params['price_from'] : '') . ',' . ($is_price_to ? $params['price_to'] : ''); } // // Weight // $is_weight_from = isset($params['weight_from']) && fn_is_numeric($params['weight_from']); $is_weight_to = isset($params['weight_to']) && fn_is_numeric($params['weight_to']); if ($is_weight_from || $is_weight_to) { $restrict_by['weight'] = ($is_weight_from ? $params['weight_from'] : '') . ',' . ($is_weight_to ? $params['weight_to'] : ''); } // // Amount // $is_amount_from = isset($params['amount_from']) && fn_is_numeric($params['amount_from']); $is_amount_to = isset($params['amount_to']) && fn_is_numeric($params['amount_to']); if ($is_amount_from || $is_amount_to) { $restrict_by['amount'] = ($is_amount_from ? $params['amount_from'] : '') . ',' . ($is_amount_to ? $params['amount_to'] : ''); } // // Popularity // $is_popularity_from = isset($params['popularity_from']) && fn_is_numeric($params['popularity_from']); $is_popularity_to = isset($params['popularity_to']) && fn_is_numeric($params['popularity_to']); if ($is_popularity_from || $is_popularity_to) { $restrict_by['popularity'] = ($is_popularity_from ? $params['popularity_from'] : '') . ',' . ($is_popularity_to ? $params['popularity_to'] : ''); } if (!empty($params['free_shipping'])) { $restrict_by['free_shipping'] = $params['free_shipping']; } if (isset($params['pcode']) && fn_string_not_empty($params['pcode'])) { $query_by['product_code'] = trim($params['pcode']); } return array($restrict_by, $query_by, $union); }
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); }
/** * Returns orders * * @param array $params array with search params * @param int $items_per_page * @param bool $get_totals * @param string $lang_code * @return array */ function fn_get_orders($params, $items_per_page = 0, $get_totals = false, $lang_code = CART_LANGUAGE, $f_notJoin = 0) { // Init filter $params = fn_init_view('orders', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // default page is 1 $params['include_incompleted'] = empty($params['include_incompleted']) ? false : $params['include_incompleted']; // default incomplited orders should not be displayed if (!empty($params['status']) && (is_array($params['status']) && in_array(STATUS_INCOMPLETED_ORDER, $params['status']) || !is_array($params['status']) && $params['status'] == STATUS_INCOMPLETED_ORDER)) { $params['include_incompleted'] = true; } // Define fields that should be retrieved $fields = array("distinct ?:orders.order_id", "?:orders.user_id", "?:orders.is_parent_order", "?:orders.parent_order_id", "?:orders.company_id", "?:orders.timestamp", "?:orders.date_to_delivery", "?:orders.firstname", "?:orders.lastname", "?:orders.email", "?:orders.status", "?:orders.total", "?:payment_descriptions.payment"); if ($f_notJoin == 0) { $fields[] = 'invoice_docs.doc_id as invoice_id'; $fields[] = 'memo_docs.doc_id as credit_memo_id'; } // Define sort fields $sortings = array('order_id' => "?:orders.order_id", 'status' => "?:orders.status", 'customer' => array("?:orders.lastname", "?:orders.firstname"), 'email' => "?:orders.email", 'date' => "?:orders.timestamp", 'deliv' => "?:orders.date_to_delivery", 'total' => "?:orders.total", 'payment' => "?:orders.payment_id"); $directions = array('asc' => 'asc', 'desc' => 'desc'); if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { $params['sort_order'] = 'desc'; } if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = 'date'; } $sorting = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $condition = $join = $group = ''; $condition .= " AND ?:orders.is_parent_order != 'Y' "; $condition .= fn_get_company_condition('?:orders.company_id'); if (isset($params['cname']) && fn_string_no_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:orders.firstname LIKE ?l AND ?:orders.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:orders.firstname LIKE ?l OR ?:orders.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (!empty($params['tax_exempt'])) { $condition .= db_quote(" AND ?:orders.tax_exempt = ?s", $params['tax_exempt']); } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:orders.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (!empty($params['user_id'])) { $condition .= db_quote(' AND ?:orders.user_id IN (?n)', $params['user_id']); } if (isset($params['total_from']) && fn_is_numeric($params['total_from'])) { $condition .= db_quote(" AND ?:orders.total >= ?d", fn_convert_price($params['total_from'])); } if (!empty($params['total_to']) && fn_is_numeric($params['total_to'])) { $condition .= db_quote(" AND ?:orders.total <= ?d", fn_convert_price($params['total_to'])); } if (!empty($params['status'])) { $condition .= db_quote(' AND ?:orders.status IN (?a)', $params['status']); } if (empty($params['include_incompleted'])) { $condition .= db_quote(' AND ?:orders.status != ?s', STATUS_INCOMPLETED_ORDER); } if (!empty($params['order_id'])) { $condition .= db_quote(' AND ?:orders.order_id IN (?n)', !is_array($params['order_id']) && strpos($params['order_id'], ',') !== false ? explode(',', $params['order_id']) : $params['order_id']); } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"; $group .= " GROUP BY ?:orders.order_id "; } $join .= " LEFT JOIN ?:payment_descriptions ON ?:payment_descriptions.payment_id = ?:orders.payment_id AND ?:payment_descriptions.lang_code='" . $lang_code . "'"; if (!empty($params['admin_user_id'])) { $condition .= db_quote(" AND ?:new_orders.user_id = ?i", $params['admin_user_id']); $join .= " LEFT JOIN ?:new_orders ON ?:new_orders.order_id = ?:orders.order_id"; } //$join .= " LEFT JOIN ?:profile_fields_data ON orders.user_id = profile_fields_data.object_id"; $docs_conditions = array(); if (!empty($params['invoice_id']) || !empty($params['has_invoice'])) { if (!empty($params['has_invoice'])) { $docs_conditions[] = "invoice_docs.doc_id IS NOT NULL"; } elseif (!empty($params['invoice_id'])) { $docs_conditions[] = db_quote("invoice_docs.doc_id = ?i", $params['invoice_id']); } } if ($f_notJoin == 0) { $join .= " LEFT JOIN ?:order_docs as invoice_docs ON invoice_docs.order_id = ?:orders.order_id AND invoice_docs.type = 'I'"; } if (!empty($params['credit_memo_id']) || !empty($params['has_credit_memo'])) { if (!empty($params['has_credit_memo'])) { $docs_conditions[] = "memo_docs.doc_id IS NOT NULL"; } elseif (!empty($params['credit_memo_id'])) { $docs_conditions[] = db_quote("memo_docs.doc_id = ?i", $params['credit_memo_id']); } } if ($f_notJoin == 0) { $join .= " LEFT JOIN ?:order_docs as memo_docs ON memo_docs.order_id = ?:orders.order_id AND memo_docs.type = 'C'"; } if (!empty($docs_conditions)) { $condition .= " AND (" . implode(' OR ', $docs_conditions) . ")"; } if (!empty($params['shippings'])) { $set_conditions = array(); foreach ($params['shippings'] as $v) { $set_conditions[] = db_quote("FIND_IN_SET(?s, ?:orders.shipping_ids)", $v); } $condition .= " AND (" . implode(' OR ', $set_conditions) . ")"; } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:orders.timestamp >= ?i AND ?:orders.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['custom_files']) && $params['custom_files'] == 'Y') { $condition .= db_quote(" AND ?:order_details.extra LIKE ?l", "%custom_files%"); if (empty($params['p_ids']) && empty($params['product_view_id'])) { $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"; } } //$condition .= db_quote(" AND profile_fields_data.field_id = 44"); fn_set_hook('get_orders', $params, $fields, $sortings, $condition, $join); $limit = ''; if (!empty($items_per_page)) { $total = db_get_field("SELECT COUNT(DISTINCT (?:orders.order_id)) FROM ?:orders {$join} WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total, $items_per_page); } $ts = 'SELECT ' . implode(', ', $fields) . " FROM ?:orders {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}"; //var_dump('SELECT ' . implode(", ", $fields) . ' FROM ?:orders '.$join.' WHERE 1 '.$condition.' '.$group.' ORDER BY '.$sorting.' '.$limit.''); $orders = db_get_array('SELECT ' . implode(', ', $fields) . " FROM ?:orders {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}"); if ($get_totals == true) { $totals = array('gross_total' => db_get_field("SELECT sum(t.total) FROM ( SELECT total FROM ?:orders {$join} WHERE 1 {$condition} {$group}) as t"), 'totally_paid' => db_get_field("SELECT sum(t.total) FROM ( SELECT total FROM ?:orders {$join} WHERE status IN ('P', 'C') {$condition} {$group}) as t")); } return array($orders, $params, $get_totals == true ? $totals : array()); }
function fn_pay_affiliates($params, $do_pagination = false, $page = 1) { // Init filter $params = fn_init_view('pay_affiliates', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // default page is 1 // Define sort fields $sortings = array('username' => 'user_login', 'email' => 'email', 'partner' => 'firstname', 'amount' => 'amount', 'awaiting_amount' => 'awaiting_amount', 'date' => 'date'); $directions = array('asc' => 'asc', 'desc' => 'desc'); if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { $params['sort_order'] = 'desc'; } $sorting_direction = $directions[$params['sort_order']]; if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = 'username'; } $sort_by = $params['sort_by']; if ($sort_by == 'date' || $sort_by == 'awaiting_amount') { $sort_by = 'username'; } $sorting = (is_array($sortings[$sort_by]) ? implode(' ' . $sorting_direction . ', ', $sortings[$sort_by]) : $sortings[$sort_by]) . " " . $sorting_direction; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $pay_filter = array(); $pay_filter['min_payment'] = empty($params['min_payment']) ? '' : 'Y'; $pay_filter['last_payout'] = empty($params['last_payout']) ? '' : 'Y'; $pay_filter['amount_from'] = empty($params['amount_from']) ? '' : floatval($params['amount_from']); $pay_filter['amount_to'] = empty($params['amount_to']) ? '' : floatval($params['amount_to']); $_SESSION['pay_filter'] = $pay_filter; $join = $condition = $group = ''; $having = array(); if (!empty($params['min_payment'])) { $having[] = 'SUM(pa.amount) >= AVG(ap.min_payment)'; } if (isset($params['amount_from']) && fn_is_numeric($params['amount_from'])) { $having[] = db_quote("SUM(pa.amount) >= ?d", $params['amount_from']); } if (isset($params['amount_to']) && fn_is_numeric($params['amount_to'])) { $having[] = db_quote("SUM(pa.amount) <= ?d", $params['amount_to']); } if (!empty($params['last_payout'])) { $condition .= db_quote(" AND pa.date < ?i", fn_parse_date(fn_get_date_of_payment_period(), true)); } $group = 'GROUP BY pa.partner_id' . (empty($having) ? ' ' : ' HAVING (' . implode(') AND (', $having) . ') '); if ($do_pagination) { if (empty($page)) { $page = 1; } $cnt_list_stats = db_get_fields("SELECT DISTINCT(pa.partner_id) FROM ?:aff_partner_actions as pa LEFT JOIN ?:aff_partner_profiles as pp ON pa.partner_id = pp.user_id LEFT JOIN ?:affiliate_plans as ap ON ap.plan_id = pp.plan_id WHERE pa.approved = 'Y' AND pa.payout_id = 0 ?p ?p", $condition, $group); $limit = fn_paginate($page, count($cnt_list_stats)); } else { $limit = ''; } $partner_balances = db_get_hash_array("SELECT pa.partner_id, u.user_login, u.firstname, u.lastname, u.email, SUM(amount) as amount FROM ?:aff_partner_actions as pa LEFT JOIN ?:users as u ON pa.partner_id = u.user_id LEFT JOIN ?:aff_partner_profiles as pp ON pa.partner_id = pp.user_id LEFT JOIN ?:affiliate_plans as ap ON ap.plan_id = pp.plan_id WHERE pa.approved = 'Y' AND payout_id = 0 ?p ?p ORDER BY {$sorting} {$limit}", 'partner_id', $condition, $group); $_partners = db_get_hash_array("SELECT pa.partner_id, SUM(amount) as amount FROM ?:aff_partner_actions as pa LEFT JOIN ?:aff_partner_profiles as pp ON pa.partner_id = pp.user_id LEFT JOIN ?:affiliate_plans as ap ON ap.plan_id = pp.plan_id WHERE pa.approved = 'N' AND payout_id = 0 GROUP BY pa.partner_id ORDER BY amount {$sorting_direction}", 'partner_id'); $last_payout_dates = db_get_hash_array("SELECT partner_id, MAX(date) as date FROM ?:affiliate_payouts GROUP BY partner_id ORDER BY date {$sorting_direction}", 'partner_id'); if ($params['sort_by'] != 'date' && $params['sort_by'] != 'awaiting_amount') { foreach ($partner_balances as $_partner_id => $_partner_data) { $partner_balances[$_partner_id]['awaiting_amount'] = empty($_partners[$_partner_id]['amount']) ? '' : floatval($_partners[$_partner_id]['amount']); $partner_balances[$_partner_id]['last_payout_date'] = @$last_payout_dates[$_partner_id]['date']; } } else { $temp_balances = array(); if ($params['sort_by'] == 'awaiting_amount') { foreach ($_partners as $_partner_id => $_partner_data) { if (!empty($partner_balances[$_partner_id])) { $temp_balances[$_partner_id] = $partner_balances[$_partner_id]; $temp_balances[$_partner_id]['awaiting_amount'] = floatval($_partner_data['amount']); $temp_balances[$_partner_id]['last_payout_date'] = @$last_payout_dates[$_partner_id]['date']; unset($partner_balances[$_partner_id]); } } } else { foreach ($last_payout_dates as $_partner_id => $_date) { if (!empty($partner_balances[$_partner_id])) { $temp_balances[$_partner_id] = $partner_balances[$_partner_id]; $temp_balances[$_partner_id]['awaiting_amount'] = empty($_partners[$_partner_id]['amount']) ? '' : floatval($_partners[$_partner_id]['amount']); $temp_balances[$_partner_id]['last_payout_date'] = $_date['date']; unset($partner_balances[$_partner_id]); } } } $temp_balances2 = array(); foreach ($partner_balances as $_partner_id => $_partner_data) { $temp_balances2[$_partner_id] = $_partner_data; $temp_balances2[$_partner_id]['awaiting_amount'] = empty($_partners[$_partner_id]['amount']) ? '' : floatval($_partners[$_partner_id]['amount']); $temp_balances2[$_partner_id]['last_payout_date'] = @$last_payout_dates[$_partner_id]['date']; } $partner_balances = $sorting_direction == 'desc' ? $temp_balances + $temp_balances2 : $temp_balances2 + $temp_balances; } return array($partner_balances, $params); }
function fn_get_affiliates_for_approve($params, $auth, $items_per_page = 0) { // Init filter $params = fn_init_view('aff_stats', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; $condition = '1'; if (!empty($params['name'])) { // Check if first and last names are entered $arr = explode(' ', $params['name']); if (sizeof($arr) == 2) { $condition .= db_quote(" AND (?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l)", "%{$arr['0']}%", "%{$arr['1']}%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%{$params['name']}%", "%{$params['name']}%"); } } if (!empty($params['user_login'])) { $condition .= db_quote(" AND ?:users.user_login LIKE ?l", "%{$params['user_login']}%"); } if (!empty($params['partner_id']) && AREA == 'A') { $condition .= db_quote(" AND actions.partner_id = ?i", $params['partner_id']); } elseif (AREA == 'P') { $condition .= db_quote(" AND actions.partner_id = ?i", $auth['user_id']); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (actions.date >= ?i AND actions.date <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['plan_id'])) { $condition .= db_quote(" AND actions.plan_id = ?i", $params['plan_id']); } if (!empty($params['payout_id'])) { $_conditions = ''; foreach ($params['payout_id'] as $_act) { $_conditions .= (empty($_conditions) ? '' : 'OR') . db_quote(" action = ?s", $_act); } $condition .= " AND ({$_conditions}) "; } if (!empty($params['status'])) { $_conditions = ''; foreach ($params['status'] as $_status) { $_conditions .= empty($_conditions) ? '' : 'OR'; if ($_status == 'P') { $_conditions .= " (actions.payout_id != 0) "; } elseif ($_status == 'A') { $_conditions .= " (actions.payout_id = 0 AND actions.approved = 'Y') "; } else { $_conditions .= " (actions.approved = 'N' AND actions.payout_id = 0) "; } } $condition .= " AND ({$_conditions}) "; } if (!empty($params['zero_actions']) && $params['zero_actions'] == 'Y' && AREA == 'A') { $condition .= " AND actions.amount = 0"; } elseif (empty($params['zero_actions']) || AREA != 'A') { $condition .= " AND actions.amount != 0"; } if (isset($params['amount_from']) && fn_is_numeric($params['amount_from'])) { $condition .= db_quote(" AND actions.amount >= ?d", $params['amount_from']); } if (isset($params['amount_to']) && fn_is_numeric($params['amount_to'])) { $condition .= db_quote(" AND actions.amount <= ?d", $params['amount_to']); } if (empty($params['sort_order'])) { $params['sort_order'] = 'desc'; } if (empty($params['sort_by'])) { $params['sort_by'] = 'date'; } $affiliate_commissions = fn_get_affiliate_actions($condition, array('sort_order' => $params['sort_order'], 'sort_by' => $params['sort_by']), true, $params['page']); // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; // Get general statistics $general_stats = db_get_hash_array("SELECT action, COUNT(action) as count, SUM(amount) as sum, AVG(amount) as avg, COUNT(distinct partner_id) as partners FROM ?:aff_partner_actions as actions WHERE ?p GROUP BY action", 'action', $condition); $general_stats['total'] = db_get_row("SELECT 'total' as action, COUNT(action) as count, SUM(amount) as sum, AVG(amount) as avg, COUNT(DISTINCT partner_id) as partners FROM ?:aff_partner_actions as actions WHERE ?p", $condition); // Get additional statistics $additional_stats = array(); $additional_stats['click_vs_show'] = empty($general_stats['show']['count']) ? '---' : (empty($general_stats['click']['count']) ? '0' : round($general_stats['click']['count'] / $general_stats['show']['count'] * 100, 1) . '% (' . intval($general_stats['click']['count']) . '/' . intval($general_stats['show']['count']) . ')'); $additional_stats['sale_vs_click'] = empty($general_stats['click']['count']) ? '---' : (empty($general_stats['sale']['count']) ? '0' : round($general_stats['sale']['count'] / $general_stats['click']['count'] * 100, 1) . '% (' . intval($general_stats['sale']['count']) . '/' . intval($general_stats['click']['count']) . ')'); return array($affiliate_commissions, $general_stats, $additional_stats, $params); }
function fn_get_carts($params) { // Init filter $params = fn_init_view('carts', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // Define fields that should be retrieved $fields = array('?:user_session_products.user_id', '?:users.firstname', '?:users.lastname', '?:user_session_products.timestamp AS date'); // Define sort fields $sortings = array('customer' => "CONCAT(?:users.lastname, ?:users.firstname)", 'date' => "?:user_session_products.timestamp"); $directions = array('asc' => 'asc', 'desc' => 'desc'); if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { $params['sort_order'] = 'asc'; } if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = 'customer'; } $sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $condition = $join = ''; $group = " GROUP BY ?:user_session_products.user_id"; if (isset($params['cname']) && fn_string_no_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (!empty($params['user_id'])) { $condition .= db_quote(" AND ?:user_session_products.user_id = ?i", $params['user_id']); } if (!empty($params['online_only'])) { $join .= ' LEFT JOIN ?:sessions ON ?:sessions.session_id = ?:user_session_products.session_id'; $condition .= db_quote(" AND ?:sessions.expiry > ?i", TIME + SESSION_ALIVE_TIME - 300); } if (!empty($params['with_info_only'])) { $condition .= db_quote(" AND ?:users.email != ''"); } if (!empty($params['users_type'])) { if ($params['users_type'] == 'R') { $condition .= db_quote(" AND !ISNULL(?:users.user_id)"); } elseif ($params['users_type'] == 'G') { $condition .= db_quote(" AND ISNULL(?:users.user_id)"); } } if (!empty($params['total_from']) || !empty($params['total_to'])) { $having = ''; if (fn_is_numeric($params['total_from'])) { $having .= db_quote(" AND SUM(price * amount) >= ?d", $params['total_from']); } if (fn_is_numeric($params['total_to'])) { $having .= db_quote(" AND SUM(price * amount) <= ?d", $params['total_to']); } if (!empty($having)) { $users4total = db_get_fields("SELECT user_id FROM ?:user_session_products GROUP BY user_id HAVING 1 {$having}"); if (!empty($users4total)) { $condition .= db_quote(" AND (?:user_session_products.user_id IN (?n))", $users4total); } else { $condition .= " AND (?:user_session_products.user_id = 'no')"; } } } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:user_session_products.timestamp >= ?i AND ?:user_session_products.timestamp <= ?i)", $params['time_from'], $params['time_to']); } $_condition = array(); if (!empty($params['product_type_c'])) { $_condition[] = "?:user_session_products.type = 'C'"; } if (!empty($params['product_type_w']) && $params['product_type_w'] == 'Y') { $_condition[] = "?:user_session_products.type = 'W'"; } if (!empty($_condition)) { $condition .= " AND (" . implode(" OR ", $_condition) . ")"; } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $group .= " HAVING COUNT(?:user_session_products.user_id) >= " . count($arr); } $join .= " LEFT JOIN ?:users ON ?:user_session_products.user_id = ?:users.user_id"; // checking types for retrieving from the database $type_restrictions = array('C'); fn_set_hook('get_carts', $type_restrictions); if (!empty($type_restrictions) && is_array($type_restrictions)) { $condition .= " AND ?:user_session_products.type IN ('" . implode("', '", $type_restrictions) . "')"; } $total = db_get_field("SELECT COUNT(DISTINCT ?:user_session_products.user_id) FROM ?:user_session_products {$join} WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total); $carts_list = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:user_session_products {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}", 'user_id'); $_SESSION['abandoned_carts'] = db_get_fields("SELECT ?:user_session_products.user_id FROM ?:user_session_products {$join} WHERE 1 {$condition} GROUP BY user_id"); return array($carts_list, $params); }
/** * Gets products list by search params * * @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.) * @return array Products list and Search params */ 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, 'use_caching' => true, 'extend' => array('product_name', 'prices', 'categories'), 'custom_extend' => array(), 'pname' => '', 'pshort' => '', 'pfull' => '', 'pkeywords' => '', 'feature' => array(), 'type' => 'simple', 'page' => 1, 'action' => '', 'filter_variants' => 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['q'])) { $params['pname'] = 'Y'; } $total = !empty($params['total']) ? intval($params['total']) : 0; $auth =& $_SESSION['auth']; $fields = array('product_id' => 'products.product_id'); // 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(); // 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 = ''; } if (!empty($params['pcode_from_q'])) { $params['pcode'] = $params['q']; } $_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_variants'])) { $tmp .= db_quote(" OR ?:product_features_values.value LIKE ?l", '%' . $piece . '%'); $params['extend'][] = 'feature_values'; } if (isset($params['pcode']) && fn_string_not_empty($params['pcode'])) { $pcode = trim($params['pcode']); $fields['combination'] = 'inventory.combination'; $tmp .= db_quote(" OR inventory.product_code LIKE ?l OR products.product_code LIKE ?l", "%{$pcode}%", "%{$pcode}%"); } fn_set_hook('additional_fields_in_search', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $tmp, $piece, $having); $_condition[] = '(' . $tmp . ')'; } if (isset($params['pcode']) && fn_string_not_empty($params['pcode'])) { $pcode = trim($params['pcode']); $inventory_condition .= db_quote(" AND inventory.product_code LIKE ?l", "%{$pcode}%"); } $_cond = implode($search_type, $_condition); if (!empty($_condition)) { $condition .= ' AND (' . $_cond . ') '; } //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; } // Feature code if (!empty($params['feature_code'])) { $condition .= db_quote(" AND ?:product_features.feature_code = ?s", $params['feature_code']); $params['extend'][] = 'features'; $params['extend'][] = 'feature_values'; } // find with certain variant if (!empty($params['variant_id'])) { $join .= db_quote(" INNER JOIN ?:product_features_values as c_var ON c_var.product_id = products.product_id AND c_var.lang_code = ?s AND c_var.variant_id = ?i", $lang_code, $params['variant_id']); } if (!empty($params['features_hash']) || !empty($params['filter_variants'])) { $selected_filters = !empty($params['filter_variants']) ? $params['filter_variants'] : fn_parse_filters_hash($params['features_hash']); $filters = db_get_hash_array("SELECT ?:product_filters.*, ?:product_features.feature_type FROM ?:product_filters LEFT JOIN ?:product_features ON ?:product_features.feature_id = ?:product_filters.feature_id WHERE ?:product_filters.filter_id IN (?n) AND ?:product_filters.status = 'A'", 'filter_id', array_keys($selected_filters)); list($join, $condition) = fn_generate_feature_conditions($filters, $selected_filters, $join, $condition, $lang_code); $params = fn_generate_filter_field_params($params, $filters, $selected_filters); } 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_variants'])) { $feature_params = array('plain' => true, 'variants' => false, 'exclude_group' => true, 'feature_id' => array_keys($params['feature_variants'])); list($features, ) = fn_get_product_features($feature_params, PRODUCT_FEATURES_THRESHOLD); list($join, $condition) = fn_generate_feature_conditions($features, $params['feature_variants'], $join, $condition, $lang_code); } // Filter by category ID if (!empty($params['cid'])) { $cids = is_array($params['cid']) ? $params['cid'] : explode(',', $params['cid']); if (isset($params['subcats']) && $params['subcats'] == 'Y') { $_ids = db_get_fields("SELECT a.category_id" . "\n FROM ?:categories as a" . "\n LEFT JOIN ?:categories as b" . "\n ON b.category_id IN (?n)" . "\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(), $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'])) { $condition .= db_quote(' AND prices.price >= ?d', fn_convert_price(trim($params['price_from']))); $params['extend'][] = 'prices2'; $params['extend'][] = 'prices'; } 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']))); $params['extend'][] = 'prices2'; $params['extend'][] = 'prices'; } 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 = ?s, inventory.amount >= ?i, products.amount >= ?i)", ProductTracking::TRACK_WITH_OPTIONS, $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 = ?s, inventory.amount <= ?i, products.amount <= ?i)", ProductTracking::TRACK_WITH_OPTIONS, $params['amount_to'], $params['amount_to']); $inventory_condition .= db_quote(' AND inventory.amount <= ?i', $params['amount_to']); } // Cut off out of stock products if (Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && $params['area'] == 'C') { $condition .= db_quote(' AND (CASE products.tracking' . ' WHEN ?s THEN inventory.amount > 0' . ' WHEN ?s THEN products.amount > 0' . ' ELSE 1' . ' END)', ProductTracking::TRACK_WITH_OPTIONS, ProductTracking::TRACK_WITHOUT_OPTIONS); } 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['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'); $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 -- // Feature values and features if (in_array('feature_values', $params['extend'])) { $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 (in_array('features', $params['extend'])) { $join .= db_quote(" LEFT JOIN ?:product_features ON ?:product_features_values.feature_id = ?:product_features.feature_id"); } } 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'])) { $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; } // 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'; } // 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'])) { $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 . ')'; } // -- 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 -- // Load prices in main SQL-query when they are needed and sorting or filtering by price is applied if (in_array('prices', $params['extend']) && (isset($params['sort_by']) && $params['sort_by'] == 'price' || in_array('prices2', $params['extend']))) { $fields['price'] = 'MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price'; } /** * 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); // 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 = ''; } $sql_query_body = "SELECT {$calc_found_rows} " . implode(', ', $fields) . " FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY {$group_by} {$having} {$sorting} {$limit}"; $fn_load_products = function ($query, $params) use($total) { $products = db_get_array($query); $total_found_rows = empty($params['items_per_page']) ? count($products) : (empty($total) ? db_get_found_rows() : $total); return array($products, $total_found_rows); }; // Caching conditions if ($params['use_caching'] && isset($params['dispatch']) && $params['dispatch'] == 'categories.view' && $params['area'] == 'C' && $auth['usergroup_ids'] == array(0, 1) && !empty($params['cid']) && empty($params['q']) && empty($params['pid']) && empty($params['exclude_pid']) && empty($params['feature_comparison']) && empty($params['features_hash']) && empty($params['feature_code']) && empty($params['multiple_variants']) && empty($params['custom_range']) && empty($params['field_range']) && empty($params['fields_ids']) && empty($params['slider_vals']) && empty($params['ch_filters']) && empty($params['tx_features']) && empty($params['feature_variants']) && empty($params['filter_params']) && !isset($params['price_from']) && !isset($params['price_to']) && !isset($params['weight_from']) && !isset($params['weight_to']) && empty($params['tracking']) && !isset($params['amount_from']) && !isset($params['amount_to']) && empty($params['status']) && empty($params['shipping_freight_from']) && empty($params['shipping_freight_to']) && empty($params['free_shipping']) && empty($params['downloadable']) && !isset($params['pcode']) && empty($params['period']) && empty($params['item_ids']) && !isset($params['popularity_from']) && !isset($params['popularity_to']) && empty($params['order_ids'])) { $cache_prefix = __FUNCTION__; $cache_key = md5($sql_query_body); Registry::registerCache($cache_prefix . '_' . $cache_key, array('products', 'categories', 'products_categories'), Registry::cacheLevel('static'), true); if ($cache = Registry::get($cache_prefix . '_' . $cache_key)) { list($products, $params['total_items']) = $cache; } else { list($products, $params['total_items']) = $fn_load_products($sql_query_body, $params); if ($params['total_items'] > Registry::get('config.tweaks.products_found_rows_no_cache_limit')) { Registry::set($cache_prefix . '_' . $cache_key, array($products, $params['total_items'])); } } } else { list($products, $params['total_items']) = $fn_load_products($sql_query_body, $params); } 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']); } $products = fn_load_products_extra_data($products, $params, $lang_code); /** * 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); }
function fn_get_recurring_subscriptions($params, $additional_data = array()) { // Init filter $params = fn_init_view('subscriptions', $params); $params['page'] = empty($params['page']) ? 1 : $params['page']; $fields = array('?:recurring_subscriptions.*'); // Define sort fields $sortings = array('subscription_id' => 'subscription_id', 'order_id' => 'order_id', 'status' => 'status', 'customer' => array('lastname', 'firstname'), 'email' => 'email', 'date' => 'timestamp', 'price' => 'price', 'last_paid' => 'last_timestamp', 'duration' => 'duration'); $directions = array('asc' => 'asc', 'desc' => 'desc'); if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { $params['sort_order'] = 'desc'; } if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = 'date'; } $sorting = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $condition = $join = $group = ''; if (isset($params['cname']) && fn_string_no_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:recurring_subscriptions.firstname LIKE ?l AND ?:recurring_subscriptions.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:recurring_subscriptions.firstname LIKE ?l OR ?:recurring_subscriptions.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:recurring_subscriptions.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) { $condition .= db_quote(" AND ?:recurring_subscriptions.price >= ?d", fn_convert_price($params['price_from'])); } if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) { $condition .= db_quote(" AND ?:recurring_subscriptions.price <= ?d", fn_convert_price($params['price_to'])); } if (!empty($params['status'])) { $condition .= db_quote(' AND ?:recurring_subscriptions.status = ?s', $params['status']); } if (!empty($params['order_id'])) { $condition .= db_quote(' AND ?:recurring_subscriptions.order_id IN (?n)', $params['order_id']); } if (!empty($params['plan_id'])) { $condition .= db_quote(' AND ?:recurring_subscriptions.plan_id IN (?n)', $params['plan_id']); } if (!empty($params['p_ids'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; $condition .= db_quote(" AND ?:order_details.product_id IN (?a)", $arr); $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:recurring_subscriptions.order_id AND ?:order_details.extra LIKE '%recurring_plan_id%'"; $group .= " GROUP BY ?:recurring_subscriptions.subscription_id HAVING COUNT(?:recurring_subscriptions.subscription_id) >= " . count($arr); } if (!empty($params['period_type']) && !empty($params['period']) && $params['period'] != 'A') { if ($params['period_type'] == 'D') { $timestamp = 'timestamp'; } elseif ($params['period_type'] == 'L') { $timestamp = 'last_timestamp'; } else { $timestamp = 'end_timestamp'; } list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:recurring_subscriptions.{$timestamp} >= ?i AND ?:recurring_subscriptions.{$timestamp} <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($additional_data['plan_options'])) { $join .= " LEFT JOIN ?:recurring_plans ON ?:recurring_plans.plan_id = ?:recurring_subscriptions.plan_id"; $fields[] = '?:recurring_plans.allow_unsubscribe'; $fields[] = '?:recurring_plans.allow_change_duration'; } if (!empty($additional_data['user_id'])) { $condition .= db_quote(" AND ?:recurring_subscriptions.user_id = ?i AND ?:recurring_subscriptions.status = 'A'", $additional_data['user_id']); } elseif (!empty($additional_data['order_ids'])) { $condition .= db_quote(" AND FIND_IN_SET(?:recurring_subscriptions.order_id, ?s) AND ?:recurring_subscriptions.status = 'A'", implode(',', $additional_data['order_ids'])); } $total = db_get_field("SELECT DISTINCT(COUNT(?:recurring_subscriptions.subscription_id)) FROM ?:recurring_subscriptions {$join} WHERE 1 {$condition} {$group}"); $items_per_page = Registry::get('settings.Appearance.admin_elements_per_page'); $limit = fn_paginate($params['page'], $total, $items_per_page); $subscriptions = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:recurring_subscriptions {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}", 'subscription_id'); return array($subscriptions, $params); }
function fn_bestsellers_get_products(&$params, &$fields, &$sortings, &$condition, &$join, &$sorting, &$group_by, &$lang_code, &$having) { if (!empty($params['bestsellers'])) { $fields[] = 'SUM(?:product_sales.amount) as sales_amount'; $sortings['sales_amount'] = 'sales_amount'; $join .= ' INNER JOIN ?:product_sales ON ?:product_sales.product_id = products.product_id AND ?:product_sales.category_id = products_categories.category_id '; $group_by = '?:product_sales.product_id'; if (!empty($params['category_id'])) { $condition .= db_quote(" AND ?:product_sales.category_id = ?i", $params['category_id']); } } elseif (!empty($params['on_sale'])) { $fields[] = '100 - ((prices.price * 100) / list_price) AS sales_discount'; if (empty($params['on_sale_from'])) { $having[] = db_quote('sales_discount > 0'); } else { $_having = db_quote('sales_discount >= ?i', $params['on_sale_from']); if (!empty($params['on_sale_to'])) { $_having .= db_quote(' AND sales_discount <= ?i', $params['on_sale_to']); } $having[] = $_having; } } $sortings['bestsellers'] = '?:product_sales.amount'; $sortings['on_sale'] = 'sales_discount'; if (isset($params['sales_amount_from']) && fn_is_numeric($params['sales_amount_from'])) { $condition .= db_quote(' AND ?:product_sales.amount >= ?i', trim($params['sales_amount_from'])); } if (isset($params['sales_amount_to']) && fn_is_numeric($params['sales_amount_to'])) { $condition .= db_quote(' AND ?:product_sales.amount <= ?i', trim($params['sales_amount_to'])); } if (in_array('sales', $params['extend']) && empty($params['bestsellers'])) { $join .= ' LEFT JOIN ?:product_sales ON ?:product_sales.product_id = products.product_id AND ?:product_sales.category_id = products_categories.category_id '; } elseif (in_array('on_sale', $params['extend']) && empty($params['on_sale'])) { $fields[] = '100 - ((prices.price * 100) / list_price) AS sales_discount'; } if (!empty($params['similar_in_stock']) && $params['similar_in_stock'] == 'Y') { $condition .= db_quote(" AND IF(products.tracking = ?s, inventory_b.amount >= 1, products.amount >= 1) OR (products.tracking = 'D')", ProductTracking::TRACK_WITH_OPTIONS); $join .= " LEFT JOIN ?:product_options_inventory as inventory_b ON inventory_b.product_id = products.product_id AND inventory_b.amount >= 1"; } return true; }
function fn_get_rma_returns($params) { // Init filter $params = fn_init_view('rma', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // Define fields that should be retrieved $fields = array('DISTINCT ?:rma_returns.return_id', '?:rma_returns.order_id', '?:rma_returns.timestamp', '?:rma_returns.status', '?:rma_returns.total_amount', '?:rma_property_descriptions.property AS action', '?:users.firstname', '?:users.lastname'); // Define sort fields $sortings = array('return_id' => "?:rma_returns.return_id", 'timestamp' => "?:rma_returns.timestamp", 'order_id' => "?:rma_returns.order_id", 'status' => "?:rma_returns.status", 'amount' => "?:rma_returns.total_amount", 'action' => "?:rma_returns.action", 'customer' => "?:users.lastname"); $directions = array('asc' => 'asc', 'desc' => 'desc'); if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { $params['sort_order'] = 'desc'; } if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = 'timestamp'; } $sort = $sortings[$params['sort_by']] . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $join = $condition = $group = ''; if (isset($params['cname']) && fn_string_no_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (isset($params['rma_amount_from']) && fn_is_numeric($params['rma_amount_from'])) { $condition .= db_quote("AND ?:rma_returns.total_amount >= ?d", $params['rma_amount_from']); } if (isset($params['rma_amount_to']) && fn_is_numeric($params['rma_amount_to'])) { $condition .= db_quote("AND ?:rma_returns.total_amount <= ?d", $params['rma_amount_to']); } if (!empty($params['action'])) { $condition .= db_quote(" AND ?:rma_returns.action = ?s", $params['action']); } if (!empty($params['return_id'])) { $condition .= db_quote(" AND ?:rma_returns.return_id = ?i", $params['return_id']); } if (!empty($params['request_status'])) { $condition .= db_quote(" AND ?:rma_returns.status IN (?a)", $params['request_status']); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:rma_returns.timestamp >= ?i AND ?:rma_returns.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['order_id'])) { $condition .= db_quote(" AND ?:rma_returns.order_id = ?i", $params['order_id']); } if (!empty($params['user_id'])) { $condition .= db_quote(" AND ?:rma_returns.user_id = ?i", $params['user_id']); } if (!empty($params['order_status'])) { $condition .= db_quote(" AND ?:orders.status IN (?a)", $params['order_status']); } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"; $group .= db_quote(" GROUP BY ?:rma_returns.return_id HAVING COUNT(?:orders.order_id) >= ?i", count($arr)); } $items_per_page = Registry::get('settings.Appearance.' . (AREA == 'A') ? 'admin_elements_per_page' : 'elements_per_page'); $total = db_get_field("SELECT COUNT(DISTINCT ?:rma_returns.return_id) FROM ?:rma_returns LEFT JOIN ?:rma_return_products ON ?:rma_return_products.return_id = ?:rma_returns.return_id LEFT JOIN ?:rma_property_descriptions ON ?:rma_property_descriptions.property_id = ?:rma_returns.action LEFT JOIN ?:users ON ?:rma_returns.user_id = ?:users.user_id LEFT JOIN ?:orders ON ?:rma_returns.order_id = ?:orders.order_id {$join} WHERE 1 {$condition} {$group}"); $limit = fn_paginate($params['page'], $total, $items_per_page); // FIXME $return_requests = db_get_array("SELECT " . implode(', ', $fields) . " FROM ?:rma_returns LEFT JOIN ?:rma_return_products ON ?:rma_return_products.return_id = ?:rma_returns.return_id LEFT JOIN ?:rma_property_descriptions ON (?:rma_property_descriptions.property_id = ?:rma_returns.action AND ?:rma_property_descriptions.lang_code = ?s) LEFT JOIN ?:users ON ?:rma_returns.user_id = ?:users.user_id LEFT JOIN ?:orders ON ?:rma_returns.order_id = ?:orders.order_id {$join} WHERE 1 {$condition} {$group} ORDER BY {$sort} {$limit}", AREA == 'C' ? CART_LANGUAGE : DESCR_SL); return array($return_requests, $params); }
function fn_get_rma_returns($params, $items_per_page = 0) { // Init filter $params = LastView::instance()->update('rma', $params); // Set default values to input params $default_params = array('page' => 1, 'items_per_page' => $items_per_page); $params = array_merge($default_params, $params); // Define fields that should be retrieved $fields = array('DISTINCT ?:rma_returns.return_id', '?:rma_returns.order_id', '?:rma_returns.timestamp', '?:rma_returns.status', '?:rma_returns.total_amount', '?:rma_property_descriptions.property AS action', '?:users.firstname', '?:users.lastname'); // Define sort fields $sortings = array('return_id' => "?:rma_returns.return_id", 'timestamp' => "?:rma_returns.timestamp", 'order_id' => "?:rma_returns.order_id", 'status' => "?:rma_returns.status", 'amount' => "?:rma_returns.total_amount", 'action' => "?:rma_returns.action", 'customer' => "?:users.lastname"); $sorting = db_sort($params, $sortings, 'timestamp', 'desc'); $join = $condition = $group = ''; if (isset($params['cname']) && fn_string_not_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_not_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_not_empty($params['email'])) { $condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (isset($params['rma_amount_from']) && fn_is_numeric($params['rma_amount_from'])) { $condition .= db_quote("AND ?:rma_returns.total_amount >= ?d", $params['rma_amount_from']); } if (isset($params['rma_amount_to']) && fn_is_numeric($params['rma_amount_to'])) { $condition .= db_quote("AND ?:rma_returns.total_amount <= ?d", $params['rma_amount_to']); } if (!empty($params['action'])) { $condition .= db_quote(" AND ?:rma_returns.action = ?s", $params['action']); } if (!empty($params['return_id'])) { $condition .= db_quote(" AND ?:rma_returns.return_id = ?i", $params['return_id']); } if (!empty($params['request_status'])) { $condition .= db_quote(" AND ?:rma_returns.status IN (?a)", $params['request_status']); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:rma_returns.timestamp >= ?i AND ?:rma_returns.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['order_id'])) { $condition .= db_quote(" AND ?:rma_returns.order_id = ?i", $params['order_id']); } if (isset($params['user_id'])) { $condition .= db_quote(" AND ?:rma_returns.user_id = ?i", $params['user_id']); } if (!empty($params['order_status'])) { $condition .= db_quote(" AND ?:orders.status IN (?a)", $params['order_status']); } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"; $group .= db_quote(" GROUP BY ?:rma_returns.return_id HAVING COUNT(?:orders.order_id) >= ?i", count($arr)); } $limit = ''; if (!empty($params['items_per_page'])) { $params['total_items'] = db_get_field("SELECT COUNT(DISTINCT ?:rma_returns.return_id) FROM ?:rma_returns LEFT JOIN ?:rma_return_products ON ?:rma_return_products.return_id = ?:rma_returns.return_id LEFT JOIN ?:rma_property_descriptions ON ?:rma_property_descriptions.property_id = ?:rma_returns.action LEFT JOIN ?:users ON ?:rma_returns.user_id = ?:users.user_id LEFT JOIN ?:orders ON ?:rma_returns.order_id = ?:orders.order_id {$join} WHERE 1 {$condition} {$group}"); $limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']); } $return_requests = db_get_array("SELECT " . implode(', ', $fields) . " FROM ?:rma_returns LEFT JOIN ?:rma_return_products ON ?:rma_return_products.return_id = ?:rma_returns.return_id LEFT JOIN ?:rma_property_descriptions ON (?:rma_property_descriptions.property_id = ?:rma_returns.action AND ?:rma_property_descriptions.lang_code = ?s) LEFT JOIN ?:users ON ?:rma_returns.user_id = ?:users.user_id LEFT JOIN ?:orders ON ?:rma_returns.order_id = ?:orders.order_id {$join} WHERE 1 {$condition} {$group} {$sorting} {$limit}", AREA == 'C' ? CART_LANGUAGE : DESCR_SL); LastView::instance()->processResults('rma_returns', $return_requests, $params); return array($return_requests, $params); }
/** * Gets abandoned/live carts * * @param array $params Query parameters * @param int $items_per_page Number of carts per page * @return array Abandoned/live carts * array( * 0: array List of carts * 1: array Query parameters * 2: array User IDs (grouped by companies for ultimate) * ) */ function fn_get_carts($params, $items_per_page = 0) { // Init filter $params = LastView::instance()->update('carts', $params); // Set default values to input params $default_params = array('page' => 1, 'items_per_page' => $items_per_page); $params = array_merge($default_params, $params); // Define fields that should be retrieved $fields = array('?:user_session_products.user_id', '?:users.firstname', '?:users.lastname', '?:user_session_products.timestamp AS date', '?:user_session_products.ip_address'); // Define sort fields $sortings = array('customer' => "CONCAT(?:users.lastname, ?:users.firstname)", 'date' => "?:user_session_products.timestamp"); if (fn_allowed_for('ULTIMATE')) { $sortings['company_id'] = "?:user_session_products.company_id"; } $sorting = db_sort($params, $sortings, 'customer', 'asc'); $condition = $join = ''; $group = " GROUP BY ?:user_session_products.user_id"; $group_post = ''; if (isset($params['cname']) && fn_string_not_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_not_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_not_empty($params['email'])) { $condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (!empty($params['user_id'])) { $condition .= db_quote(" AND ?:user_session_products.user_id = ?i", $params['user_id']); } if (!empty($params['online_only'])) { $sessions = Tygh::$app['session']->getStorageDriver()->getOnline('C'); if (!empty($sessions)) { $condition .= db_quote(" AND ?:user_session_products.session_id IN (?a)", $sessions); } else { $condition .= db_quote(" AND 0"); } } if (!empty($params['with_info_only'])) { $condition .= db_quote(" AND ?:users.email != ''"); } if (!empty($params['users_type'])) { if ($params['users_type'] == 'R') { $condition .= db_quote(" AND !ISNULL(?:users.user_id)"); } elseif ($params['users_type'] == 'G') { $condition .= db_quote(" AND ISNULL(?:users.user_id)"); } } if (!empty($params['total_from']) || !empty($params['total_to'])) { $having = ''; if (!empty($params['total_from']) && fn_is_numeric($params['total_from'])) { $having .= db_quote(" AND SUM(price * amount) >= ?d", $params['total_from']); } if (!empty($params['total_to']) && fn_is_numeric($params['total_to'])) { $having .= db_quote(" AND SUM(price * amount) <= ?d", $params['total_to']); } if (!empty($having)) { $users4total = db_get_fields("SELECT user_id FROM ?:user_session_products GROUP BY user_id HAVING 1 {$having}"); if (!empty($users4total)) { $condition .= db_quote(" AND (?:user_session_products.user_id IN (?n))", $users4total); } else { $condition .= " AND (?:user_session_products.user_id = 'no')"; } } } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:user_session_products.timestamp >= ?i AND ?:user_session_products.timestamp <= ?i)", $params['time_from'], $params['time_to']); } $_condition = array(); if (!empty($params['product_type_c'])) { $_condition[] = "?:user_session_products.type = 'C'"; } if (!empty($params['product_type_w'])) { $_condition[] = "?:user_session_products.type = 'W'"; } if (!empty($_condition)) { $condition .= " AND (" . implode(" OR ", $_condition) . ")"; } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $group_post .= " HAVING COUNT(?:user_session_products.user_id) >= " . count($arr); } $join .= " LEFT JOIN ?:users ON ?:user_session_products.user_id = ?:users.user_id"; // checking types for retrieving from the database $type_restrictions = array('C'); /** * Sets getting abandoned/live carts parameters * * @param array $type_restrictions Product types * @param array $params Query params * @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 array $fields SQL fields to be selected in an SQL-query * @param string $group String containing the SQL-query GROUP BY field * @param $array_index_field Deprecated unused parameter */ fn_set_hook('get_carts', $type_restrictions, $params, $condition, $join, $fields, $group, $array_index_field); if (!empty($type_restrictions) && is_array($type_restrictions)) { $condition .= " AND ?:user_session_products.type IN ('" . implode("', '", $type_restrictions) . "')"; } $carts_list = array(); $group .= $group_post; $limit = ''; if (!empty($params['items_per_page'])) { $limit = db_paginate($params['page'], $params['items_per_page']); } if (fn_allowed_for('ULTIMATE')) { $group = " GROUP BY ?:user_session_products.user_id, ?:user_session_products.company_id"; } /** * Gets abandoned/live carts * * @param array $params Query params * @param int $items_per_page Amount of carts per page * @param array $fields 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 $condition String containing SQL-query condition possibly prepended with a logical operator (AND or OR) * @param string $group String containing the SQL-query GROUP BY field * @param string $sorting String containing the SQL-query ORDER BY field * @param string $limit String containing the SQL-query LIMIT field */ fn_set_hook('get_carts_before_select', $params, $items_per_page, $fields, $join, $condition, $group, $sorting, $limit); $carts_list = db_get_hash_array("SELECT SQL_CALC_FOUND_ROWS " . implode(', ', $fields) . " FROM ?:user_session_products {$join}" . " WHERE 1 {$condition} {$group} {$sorting} {$limit}", 'user_id'); if (!empty($params['items_per_page'])) { $params['total_items'] = db_get_found_rows(); } $extra_data = db_get_hash_array("SELECT user_id, SUM(amount) as cart_products, SUM(amount * price) as total, order_id" . " FROM ?:user_session_products" . " WHERE user_id IN(?n) AND type = 'C'" . " GROUP BY user_id", 'user_id', array_keys($carts_list)); if ($extra_data) { $carts_list = fn_array_merge($carts_list, $extra_data); } $user_ids = array(); foreach ($carts_list as &$item) { $item['ip_address'] = fn_ip_from_db($item['ip_address']); $item['user_data'] = fn_get_user_info($item['user_id'], true); unset($item['user_data']['password'], $item['user_data']['salt'], $item['user_data']['last_passwords'], $item['user_data']['password_change_timestamp'], $item['user_data']['api_key']); if (fn_allowed_for('ULTIMATE')) { $user_ids[$item['company_id']][] = $item['user_id']; } else { $user_ids[] = $item['user_id']; } } /** * Actions after getting abandoned/live carts * * @param array $carts_list List of abandoned/live carts * @param array $params Query params * @param array $user_ids Cart User IDs. Grouped by companies for ultimate * @param int $items_per_page Amount of carts per page * @param array $fields 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 $condition String containing SQL-query condition possibly prepended with a logical operator (AND or OR) * @param string $group String containing the SQL-query GROUP BY field * @param string $sorting String containing the SQL-query ORDER BY field * @param string $limit String containing the SQL-query LIMIT field */ fn_set_hook('get_carts_post', $carts_list, $params, $user_ids, $items_per_page, $fields, $join, $condition, $group, $sorting, $limit); LastView::instance()->processResults('carts', $carts_list, $params); return array($carts_list, $params, $user_ids); }