function Check_DB_Access()
{
global $db_conn;
//check if cscart database can be acessed or not
$shipping = db_get_array('SHOW COLUMNS FROM ?:orders', 'O');
if (db_get_found_rows() > 0) {
$this->display_msg = DB_SUCCESS_MSG;
} else {
$this->display_msg = DB_ERROR_MSG;
}
}
/**
* Gets products default navigation
*
* @param array $params Request params
* @return array navigation data
*/
function fn_lv_get_product_default_navigation($params)
{
if (empty($params['product_id'])) {
return false;
}
$update_data = array();
$product_id = $params['product_id'];
if (fn_allowed_for('ULTIMATE') && Registry::get('runtime.company_id')) {
$company_condition = fn_get_company_condition('c.company_id');
$category_id = db_get_field("SELECT c.category_id, IF(pc.link_type = ?s, 1, 0) as is_main FROM ?:categories AS c LEFT JOIN ?:products_categories AS pc ON c.category_id = pc.category_id WHERE pc.product_id = ?i {$company_condition} ORDER BY is_main DESC", 'M', $product_id);
} else {
$category_id = db_get_field("SELECT category_id FROM ?:products_categories WHERE product_id = ?i AND link_type = ?s", $product_id, 'M');
}
if (empty($category_id)) {
return false;
}
$search_params = array('cid' => $category_id, 'get_conditions' => true);
list($fields, $join, $condition) = fn_get_products($search_params);
$sorting = "ORDER BY descr1.product asc";
// get product position in full list
db_query("SET @r = 0;");
$product_position = db_get_field("SELECT a.row FROM (SELECT products.product_id, @r := @r + 1 as row FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY products.product_id {$sorting}) AS a WHERE a.product_id = ?i", $product_id);
$items_per_page = Registry::get('settings.Appearance.products_per_page');
if (empty($product_position) || empty($items_per_page)) {
return false;
}
$page = ceil($product_position / $items_per_page);
$limit = db_paginate($page, $items_per_page);
$stored_items_ids[$page] = db_get_fields("SELECT SQL_CALC_FOUND_ROWS products.product_id FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY products.product_id {$sorting} {$limit}");
$total_items = db_get_found_rows();
$total_pages = ceil($total_items / $items_per_page);
unset($search_params['get_conditions']);
$update_data['params'] = serialize($search_params);
$update_data['view_results'] = array('items_ids' => $stored_items_ids, 'total_pages' => $total_pages, 'items_per_page' => $items_per_page, 'total_items' => $total_items);
$update_data['view_results'] = serialize($update_data['view_results']);
return $update_data;
}
$general_stats = array();
/* Products */
if (fn_check_view_permissions('products.manage', 'GET')) {
$general_stats['products'] = array();
$params = array('only_short_fields' => true, 'extend' => array('companies', 'sharing'), 'status' => 'A', 'get_conditions' => true);
list($fields, $join, $condition) = fn_get_products($params);
db_query('SELECT SQL_CALC_FOUND_ROWS 1 FROM ?:products AS products' . $join . ' WHERE 1 ' . $condition . 'GROUP BY products.product_id');
$general_stats['products']['total_products'] = db_get_found_rows();
$params = array('amount_to' => 0, 'tracking' => array('B', 'O'), 'get_conditions' => true);
$params['extend'][] = 'companies';
if (fn_allowed_for('ULTIMATE')) {
$params['extend'][] = 'sharing';
}
list($fields, $join, $condition) = fn_get_products($params);
db_query('SELECT SQL_CALC_FOUND_ROWS ' . implode(', ', $fields) . ' FROM ?:products AS products' . $join . ' WHERE 1 ' . $condition . ' GROUP BY products.product_id');
$general_stats['products']['out_of_stock_products'] = db_get_found_rows();
}
/* Customers */
if (fn_check_view_permissions('profiles.manage', 'GET')) {
$general_stats['customers'] = array();
$users_company_condition = fn_get_company_condition('?:users.company_id');
$general_stats['customers']['registered_customers'] = db_get_field('SELECT COUNT(*) FROM ?:users WHERE user_type = ?s ?p', 'C', $users_company_condition);
}
/* Categories */
if (fn_check_view_permissions('categories.manage', 'GET')) {
$general_stats['categories'] = array();
list($fields, $join, $condition, $group_by, $sorting, $limit) = fn_get_categories(array('get_conditions' => true));
$general_stats['categories']['total_categories'] = db_get_field('SELECT COUNT(*) FROM ?:categories WHERE 1 ?p', $condition);
}
/* Storefronts */
if (fn_check_view_permissions('companies.manage', 'GET')) {
exit;
}
$pattern = !empty($_REQUEST['pattern']) ? $_REQUEST['pattern'] : '';
$start = !empty($_REQUEST['start']) ? $_REQUEST['start'] : 0;
$limit = (!empty($_REQUEST['limit']) ? $_REQUEST['limit'] : 10) + 1;
$sorting = db_quote("?:product_feature_variants.position, ?:product_feature_variant_descriptions.variant");
$join = db_quote(" LEFT JOIN ?:product_feature_variant_descriptions ON ?:product_feature_variant_descriptions.variant_id = ?:product_feature_variants.variant_id AND ?:product_feature_variant_descriptions.lang_code = ?s", DESCR_SL);
$condition = db_quote(" AND ?:product_feature_variants.feature_id = ?i", $_REQUEST['feature_id']);
fn_set_hook('get_feature_variants_list', $condition, $join, $pattern, $start, $limit);
$objects = db_get_hash_array("SELECT SQL_CALC_FOUND_ROWS ?:product_feature_variants.variant_id AS value, ?:product_feature_variant_descriptions.variant AS name FROM ?:product_feature_variants {$join} WHERE 1 {$condition} AND ?:product_feature_variant_descriptions.variant LIKE ?l ORDER BY ?p LIMIT ?i, ?i", 'value', '%' . $pattern . '%', $sorting, $start, $limit);
if (defined('AJAX_REQUEST') && sizeof($objects) < $limit) {
Registry::get('ajax')->assign('completed', true);
} else {
array_pop($objects);
}
if (empty($_REQUEST['enter_other']) || $_REQUEST['enter_other'] != 'N') {
$total = db_get_found_rows();
if (!Registry::get('runtime.company_id') || fn_allowed_for('ULTIMATE') && fn_check_company_id('product_features', 'feature_id', $_REQUEST['feature_id'])) {
if ($start + $limit >= $total + 1) {
$objects[] = array('value' => 'disable_select', 'name' => '-' . __('enter_other') . '-');
}
}
}
if (!$start) {
array_unshift($objects, array('value' => '', 'name' => '-' . __('none') . '-'));
}
Registry::get('view')->assign('objects', $objects);
Registry::get('view')->assign('id', $_REQUEST['result_ids']);
Registry::get('view')->display('common/ajax_select_object.tpl');
exit;
}
/**
* Gets language variable by name
*
* @param string $var_name Language variable name
* @param string $lang_code 2-letter language code
*
* @return string Language variable value; in case the value is absent, language variable name with "_" prefix is returned
*/
public static function getLangVar($var_name, $lang_code = CART_LANGUAGE)
{
Registry::registerCache('lang_cache', array('language_values', 'ult_language_values'), Registry::cacheLevel('dispatch'), true);
if (empty($var_name)) {
return '';
}
$values = Registry::get('lang_cache.' . $lang_code);
if (empty($values)) {
$values = array();
}
$var_name = strtolower($var_name);
if (!isset($values[$var_name])) {
$params = array();
$left_join = array();
$fields = array('lang.value' => true);
$tables = array('?:language_values lang');
$condition = array(db_quote('lang.lang_code = ?s', $lang_code), db_quote('lang.name = ?s', $var_name));
fn_set_hook('get_lang_var', $fields, $tables, $left_join, $condition, $params);
$joins = !empty($left_join) ? ' LEFT JOIN ' . implode(', ', $left_join) : '';
$values[$var_name] = db_get_field('SELECT ' . implode(', ', array_keys($fields)) . ' FROM ' . implode(', ', $tables) . $joins . ' WHERE ' . implode(' AND ', $condition));
if (!db_get_found_rows()) {
unset($values[$var_name]);
}
Registry::set('lang_cache.' . $lang_code, $values);
}
if (!isset($values[$var_name])) {
return '_' . $var_name;
}
if (Registry::get('runtime.customization_mode.live_editor')) {
return '[lang name=' . $var_name . (preg_match('/\\[[\\w]+\\]/', $values[$var_name]) ? ' cm-pre-ajax' : '') . ']' . $values[$var_name] . '[/lang]';
}
return $values[$var_name];
}
/**
* Gets categefories and products totals data
*
* @return array Array with categories and products totals
*/
function fn_get_categories_stats()
{
$stats = array();
$params = array('only_short_fields' => true, 'extend' => array('companies', 'sharing'), 'get_conditions' => true);
list($fields, $join, $condition) = fn_get_products($params);
db_query('SELECT SQL_CALC_FOUND_ROWS 1 FROM ?:products AS products' . $join . ' WHERE 1 ' . $condition . 'GROUP BY products.product_id');
$stats['products_total'] = db_get_found_rows();
$params = array('get_conditions' => true);
list($fields, $join, $condition, $group_by, $sorting, $limit) = fn_get_categories($params);
$stats['categories_total'] = db_get_field('SELECT COUNT(*) FROM ?:categories WHERE 1 ?p', $condition);
$params = array('get_conditions' => true, 'status' => 'A');
list($fields, $join, $condition, $group_by, $sorting, $limit) = fn_get_categories($params);
$stats['categories_active'] = db_get_field('SELECT COUNT(*) FROM ?:categories WHERE 1 ?p', $condition);
$params = array('get_conditions' => true, 'status' => 'H');
list($fields, $join, $condition, $group_by, $sorting, $limit) = fn_get_categories($params);
$stats['categories_hidden'] = db_get_field('SELECT COUNT(*) FROM ?:categories WHERE 1 ?p', $condition);
$params = array('get_conditions' => true, 'status' => 'D');
list($fields, $join, $condition, $group_by, $sorting, $limit) = fn_get_categories($params);
$stats['categories_disabled'] = db_get_field('SELECT COUNT(*) FROM ?:categories WHERE 1 ?p', $condition);
return $stats;
}
function fn_twg_get_product_stats_by_params($params = array())
{
$default_params = array('only_short_fields' => true, 'extend' => array('companies', 'sharing'), 'get_conditions' => true);
$params = array_merge($default_params, $params);
list($fields, $join, $condition) = fn_get_products($params);
if (isset($params['product_type'])) {
$condition .= db_quote(' AND products.product_type=?s', $params['product_type']);
}
db_query('SELECT SQL_CALC_FOUND_ROWS 1 FROM ?:products AS products' . $join . ' WHERE 1 ' . $condition . 'GROUP BY products.product_id');
return db_get_found_rows();
}
function fn_companies_get_payouts($params = array(), $items_per_page = 0)
{
$params = LastView::instance()->update('balance', $params);
$default_params = array('page' => 1, 'items_per_page' => $items_per_page);
$params = array_merge($default_params, $params);
$fields = array();
$join = ' ';
// Define sort fields
$sortings = array('sort_vendor' => 'companies.company', 'sort_period' => 'payouts.start_date', 'sort_amount' => 'payout_amount', 'sort_date' => 'payouts.payout_date');
$condition = $date_condition = ' 1 ';
$join .= ' LEFT JOIN ?:orders AS orders ON (payouts.order_id = orders.order_id)';
$join .= ' LEFT JOIN ?:companies AS companies ON (payouts.company_id = companies.company_id)';
// If the sales period not defined, specify it as 'All'
if (empty($params['time_from']) && empty($params['time_to'])) {
$params['period'] = 'A';
}
if (empty($params['time_from']) && empty($params['period'])) {
$params['time_from'] = mktime(0, 0, 0, date('n', TIME), 1, date('Y', time()));
} elseif (!empty($params['time_from'])) {
$params['time_from'] = fn_parse_date($params['time_from']);
} else {
$time_from = true;
}
if (empty($params['time_to']) && empty($params['period'])) {
$params['time_to'] = time();
} elseif (!empty($params['time_to'])) {
$params['time_to'] = fn_parse_date($params['time_to']) + 24 * 60 * 60 - 1;
//Get the day ending time
} else {
$time_to = true;
}
if (isset($time_from) || isset($time_to)) {
$dates = db_get_row('SELECT MIN(start_date) AS time_from, MAX(end_date) AS time_to FROM ?:vendor_payouts');
if (isset($time_from)) {
$params['time_from'] = $dates['time_from'];
}
if (isset($time_to)) {
$params['time_to'] = $dates['time_to'];
}
}
// Order statuses condition
$statuses = db_get_fields('SELECT status FROM ?:status_data WHERE `type` = ?s AND param = ?s AND `value` = ?s', 'O', 'calculate_for_payouts', 'Y');
if (!empty($statuses)) {
$condition .= db_quote(' AND (orders.status IN (?a) OR payouts.order_id = 0)', $statuses);
} else {
$condition .= db_quote(' AND payouts.order_id = 0');
}
$date_condition .= db_quote(' AND ((payouts.start_date >= ?i AND payouts.end_date <= ?i AND payouts.order_id != ?i) OR (payouts.order_id = ?i AND (payouts.start_date BETWEEN ?i AND ?i OR payouts.end_date BETWEEN ?i AND ?i)))', $params['time_from'], $params['time_to'], 0, 0, $params['time_from'], $params['time_to'], $params['time_from'], $params['time_to']);
// Filter by the transaction type
if (!empty($params['transaction_type']) && ($params['transaction_type'] == 'income' || $params['transaction_type'] == 'expenditure')) {
if ($params['transaction_type'] == 'income') {
$condition .= ' AND (payouts.order_id != 0 OR payouts.payout_amount > 0)';
} else {
$condition .= ' AND payouts.payout_amount < 0';
}
}
// Filter by vendor
if (Registry::get('runtime.company_id')) {
$params['vendor'] = Registry::get('runtime.company_id');
}
if (!empty($params['vendor']) && $params['vendor'] != 'all') {
$condition .= db_quote(' AND payouts.company_id = ?i', $params['vendor']);
}
if (!empty($params['payment'])) {
$condition .= db_quote(' AND payouts.payment_method like ?l', '%' . $params['payment'] . '%');
}
$sorting = db_sort($params, $sortings, 'sort_vendor', 'asc');
$limit = '';
$items = db_get_array("SELECT SQL_CALC_FOUND_ROWS * FROM ?:vendor_payouts AS payouts {$join} WHERE {$condition} AND {$date_condition} GROUP BY payouts.payout_id {$sorting} {$limit}");
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_found_rows();
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
// Calculate balance for the selected period
$total = array('BCF' => 0, 'NO' => 0, 'TPP' => 0, 'LPM' => 0, 'TOB' => 0);
$bcf_query = db_quote("SELECT SUM(payouts.order_amount) - SUM(payouts.payout_amount) * (-1) - SUM(payouts.commission_amount) AS BCF FROM ?:vendor_payouts AS payouts {$join} WHERE {$condition} AND payouts.start_date < ?i", $params['time_from']);
$current_payouts_query = db_quote("SELECT SUM(payouts.order_amount) AS NO, SUM(payouts.payout_amount) * (-1) AS TTP, SUM(payouts.order_amount) - SUM(payouts.commission_amount) + SUM(payouts.payout_amount) AS LPM FROM ?:vendor_payouts AS payouts LEFT JOIN ?:orders AS orders ON (payouts.order_id = orders.order_id) WHERE {$condition} AND {$date_condition}");
$payouts_query = db_quote("SELECT payouts.*, companies.company, IF(payouts.order_id <> 0,orders.total,payouts.payout_amount) AS payout_amount, IF(payouts.order_id <> 0, payouts.end_date, '') AS date FROM ?:vendor_payouts AS payouts {$join} WHERE {$condition} AND {$date_condition} GROUP BY payouts.payout_id {$sorting} {$limit}");
fn_set_hook('mve_companies_get_payouts', $bcf_query, $current_payouts_query, $payouts_query, $join, $total, $condition, $date_condition);
$payouts = db_get_array($payouts_query);
$total['BCF'] += db_get_field($bcf_query);
$current_payouts = db_get_row($current_payouts_query);
$total['NO'] = $current_payouts['NO'];
$total['TPP'] = $current_payouts['TTP'];
$total['LPM'] = $current_payouts['LPM'];
$total['TOB'] += fn_format_price($total['BCF'] + $total['LPM']);
$total['LPM'] = $total['LPM'] < 0 ? 0 : $total['LPM'];
$total['new_period_date'] = db_get_field('SELECT MAX(end_date) FROM ?:vendor_payouts');
return array($payouts, $params, $total);
}
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_get_ebay_templates($params, $items_per_page = 0, $lang_code = CART_LANGUAGE, $get_simple = false)
{
// Init filter
$params = LastView::instance()->update('ebay_templates', $params);
$fields = array('templates.template_id', 'templates.status', 'descr.name', 'templates.company_id');
// Define sort fields
$sortings = array('status' => 'templates.status', 'name' => 'descr.name');
$condition = '';
//fn_get_company_condition('templates.company_id')
$join = db_quote('LEFT JOIN ?:ebay_template_descriptions as descr ON templates.template_id = descr.template_id AND descr.lang_code = ?s', $lang_code);
if (!empty($params['product_id'])) {
if (fn_allowed_for('ULTIMATE')) {
if (Registry::get('runtime.simple_ultimate')) {
$condition = '';
} else {
$company_ids = fn_ult_get_shared_product_companies($params['product_id']);
$tempalte_ids = db_get_fields("SELECT share_object_id FROM ?:ult_objects_sharing WHERE share_object_type = 'ebay_templates' AND share_company_id IN (?n)", $company_ids);
$condition = db_quote(' AND templates.template_id IN (?n)', $tempalte_ids);
}
} elseif (fn_allowed_for('MULTIVENDOR')) {
if (Registry::get('runtime.company_id')) {
$condition = fn_get_company_condition('templates.company_id');
} else {
$company_id = db_get_field("SELECT company_id FROM ?:products WHERE product_id = ?i", $params['product_id']);
$condition = db_quote(" AND templates.company_id = ?i", $company_id);
}
}
} else {
if (fn_allowed_for('ULTIMATE') && !Registry::get('runtime.simple_ultimate') && Registry::get('runtime.company_id')) {
$join .= db_quote(" INNER JOIN ?:ult_objects_sharing ON (?:ult_objects_sharing.share_object_id = templates.template_id AND ?:ult_objects_sharing.share_company_id = ?i AND ?:ult_objects_sharing.share_object_type = 'ebay_templates')", Registry::get('runtime.company_id'));
}
}
$limit = '';
$group_by = 'templates.template_id';
// -- SORTINGS --
if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) {
$params['sort_by'] = 'name';
}
if (empty($params['sort_order'])) {
$params['sort_order'] = 'asc';
}
$sorting = db_sort($params, $sortings);
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']);
}
Registry::set('runtime.skip_sharing_selection', true);
$templates = db_get_array("SELECT SQL_CALC_FOUND_ROWS " . implode(', ', $fields) . " FROM ?:ebay_templates as templates {$join} WHERE 1 {$condition} GROUP BY {$group_by} {$sorting} {$limit}");
Registry::set('runtime.skip_sharing_selection', false);
if (!empty($params['items_per_page'])) {
$params['total_items'] = !empty($total) ? $total : db_get_found_rows();
} else {
$params['total_items'] = count($templates);
}
if ($get_simple == true) {
$_templates = array();
foreach ($templates as $template) {
$_templates[$template['template_id']] = $template['name'];
}
return $_templates;
}
return array($templates, $params);
}
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);
}
/**
* 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);
}
