function fn_get_order_reports($view = false, $report_id = 0)
{
$status = empty($view) ? "" : "AND status = 'A'";
$data = db_get_hash_array("SELECT a.*, b.description FROM ?:sales_reports as a LEFT JOIN ?:sales_reports_descriptions as b ON a.report_id = b.report_id AND lang_code = ?s WHERE type = 'O' {$status} ORDER BY position", 'report_id', CART_LANGUAGE);
if (empty($data)) {
return array();
}
// If we manage reports we need only it's name
if (empty($view)) {
return $data;
}
$k = $report_id;
list($data[$k]['time_from'], $data[$k]['time_to']) = fn_create_periods($data[$k]);
$data[$k]['tables'] = db_get_hash_array("SELECT a.*, b.description FROM ?:sales_reports_tables as a LEFT JOIN ?:sales_reports_table_descriptions as b ON a.table_id = b.table_id AND lang_code = ?s WHERE a.report_id = ?i ORDER BY position", 'table_id', CART_LANGUAGE, $report_id);
foreach ($data[$k]['tables'] as $key => $value) {
$data[$k]['tables'][$key]['time_from'] = $data[$k]['time_from'];
$data[$k]['tables'][$key]['time_to'] = $data[$k]['time_to'];
$elements = db_get_array("SELECT a.*, c.code FROM ?:sales_reports_table_elements as a LEFT JOIN ?:sales_reports_elements as c ON a.element_id = c.element_id WHERE a.table_id = ?i ORDER BY a.position", $value['table_id']);
//var_dump($key, $value); echo '+++++<br/>'; var_dump($elements); echo '-----<br/>';
$data[$k]['tables'][$key]['interval_id'] = $value['interval_id'];
$data[$k]['tables'][$key]['elements'] = fn_check_elements($elements, $data[$k]['tables'][$key]['time_from'], $data[$k]['tables'][$key]['time_to'], $value);
$data[$k]['tables'][$key]['intervals'] = fn_check_intervals($data[$k]['tables'][$key]['interval_id'], $data[$k]['tables'][$key]['time_from'], $data[$k]['tables'][$key]['time_to']);
}
return $data;
}
function fn_get_banners($params = array(), $lang_code = CART_LANGUAGE)
{
$default_params = array('items_per_page' => 0);
$params = array_merge($default_params, $params);
$sortings = array('position' => '?:banners.position', 'timestamp' => '?:banners.timestamp', 'name' => '?:banner_descriptions.banner');
$condition = $limit = '';
if (!empty($params['limit'])) {
$limit = db_quote(' LIMIT 0, ?i', $params['limit']);
}
$sorting = db_sort($params, $sortings, 'name', 'asc');
$condition = AREA == 'A' ? '' : " AND ?:banners.status = 'A' ";
$condition .= fn_get_localizations_condition('?:banners.localization');
$condition .= AREA == 'A' ? '' : " AND (?:banners.type != 'G' OR ?:banner_images.banner_image_id IS NOT NULL) ";
if (!empty($params['item_ids'])) {
$condition .= db_quote(' AND ?:banners.banner_id IN (?n)', explode(',', $params['item_ids']));
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:banners.timestamp >= ?i AND ?:banners.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
fn_set_hook('get_banners', $params, $condition, $sorting, $limit, $lang_code);
$fields = array('?:banners.banner_id', '?:banners.type', '?:banners.target', '?:banners.status', '?:banners.position', '?:banner_descriptions.banner', '?:banner_descriptions.description', '?:banner_descriptions.url', '?:banner_images.banner_image_id');
if (fn_allowed_for('ULTIMATE')) {
$fields[] = '?:banners.company_id';
}
$banners = db_get_array("SELECT ?p FROM ?:banners " . "LEFT JOIN ?:banner_descriptions ON ?:banner_descriptions.banner_id = ?:banners.banner_id AND ?:banner_descriptions.lang_code = ?s" . "LEFT JOIN ?:banner_images ON ?:banner_images.banner_id = ?:banners.banner_id AND ?:banner_images.lang_code = ?s" . "WHERE 1 ?p ?p ?p", implode(", ", $fields), $lang_code, $lang_code, $condition, $sorting, $limit);
foreach ($banners as $k => $v) {
$banners[$k]['main_pair'] = fn_get_image_pairs($v['banner_image_id'], 'promo', 'M', true, false, $lang_code);
}
fn_set_hook('get_banners_post', $banners, $params);
return array($banners, $params);
}
function fn_tags_build_conditions($params)
{
$conditions = fn_get_tags_company_condition('?:tags.company_id');
if (!empty($params['object_type'])) {
$conditions .= db_quote(" AND ?:tag_links.object_type = ?s", $params['object_type']);
}
if (!empty($params['status'])) {
$conditions .= db_quote(" AND ?:tags.status IN (?a)", $params['status']);
}
if (!empty($params['object_id'])) {
$conditions .= db_quote(" AND ?:tag_links.object_id = ?i", $params['object_id']);
}
if (!empty($params['user_id'])) {
$conditions .= db_quote(" AND ?:tag_links.user_id = ?i", $params['user_id']);
}
if (isset($params['tag']) && fn_string_not_empty($params['tag'])) {
$conditions .= db_quote(" AND ?:tags.tag LIKE ?l", "%" . trim($params['tag']) . "%");
}
if (!empty($params['user_and_popular'])) {
$conditions .= db_quote(" AND IF(?:tag_links.user_id = ?i, 1, ?:tags.status IN ('A'))", $params['user_and_popular']);
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$conditions .= db_quote(" AND (?:tags.timestamp >= ?i AND ?:tags.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
return $conditions;
}
function fn_get_discussions($params, $items_per_page)
{
// Init filter
$params = LastView::instance()->update('discussion', $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('?:discussion_posts.*', '?:discussion_messages.message', '?:discussion_rating.rating_value', '?:discussion.*');
// Define sort fields
$sortings = array('object' => "?:discussion.object_type", 'name' => "?:discussion_posts.name", 'ip_address' => "?:discussion_posts.ip_address", 'timestamp' => "?:discussion_posts.timestamp", 'status' => "?:discussion_posts.status", 'date' => "?:orders.timestamp", 'total' => "?:orders.total");
$sorting = db_sort($params, $sortings, 'timestamp', 'desc');
$condition = $join = '';
if (isset($params['name']) && fn_string_not_empty($params['name'])) {
$condition .= db_quote(" AND ?:discussion_posts.name LIKE ?l", "%" . trim($params['name']) . "%");
}
if (isset($params['message']) && fn_string_not_empty($params['message'])) {
$condition .= db_quote(" AND ?:discussion_messages.message LIKE ?l", "%" . trim($params['message']) . "%");
}
if (!empty($params['type'])) {
$condition .= db_quote(" AND ?:discussion.type = ?s", $params['type']);
}
if (!empty($params['status'])) {
$condition .= db_quote(" AND ?:discussion_posts.status = ?s", $params['status']);
}
if (!empty($params['post_id'])) {
$condition .= db_quote(" AND ?:discussion_posts.post_id = ?i", $params['post_id']);
}
if (isset($params['ip_address']) && fn_string_not_empty($params['ip_address'])) {
$condition .= db_quote(" AND ?:discussion_posts.ip_address = ?s", fn_ip_to_db(trim($params['ip_address'])));
}
if (!empty($params['rating_value'])) {
$condition .= db_quote(" AND ?:discussion_rating.rating_value = ?i", $params['rating_value']);
}
if (!empty($params['object_type'])) {
$condition .= db_quote(" AND ?:discussion.object_type = ?s", $params['object_type']);
}
$condition .= fn_get_discussion_company_condition('?:discussion.company_id');
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:discussion_posts.timestamp >= ?i AND ?:discussion_posts.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
$join .= " INNER JOIN ?:discussion ON ?:discussion.thread_id = ?:discussion_posts.thread_id";
$join .= " INNER JOIN ?:discussion_messages ON ?:discussion_messages.post_id = ?:discussion_posts.post_id";
$join .= " INNER JOIN ?:discussion_rating ON ?:discussion_rating.post_id = ?:discussion_posts.post_id";
$limit = '';
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(*) FROM ?:discussion_posts {$join} WHERE 1 {$condition}");
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$posts = db_get_array("SELECT " . implode(',', $fields) . " FROM ?:discussion_posts {$join} WHERE 1 {$condition} {$sorting} {$limit}");
foreach ($posts as $k => $post) {
if (isset($post['ip_address'])) {
$posts[$k]['ip_address'] = fn_ip_from_db($post['ip_address']);
}
}
return array($posts, $params);
}
function fn_get_logs($params, $items_per_page = null)
{
// Init filter
$params = fn_init_view('logs', $params);
if ($items_per_page === null) {
$items_per_page = Registry::get('settings.Appearance.admin_elements_per_page');
}
$sortings = array('timestamp' => '?:logs.timestamp', 'user' => array('?:users.lastname', '?:users.firstname'));
$directions = array('asc' => 'asc', 'desc' => 'desc');
$fields = array('?:logs.*', '?:users.firstname', '?:users.lastname');
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'] = 'timestamp';
$params['sort_order'] = 'desc';
}
$params['page'] = empty($params['page']) ? 1 : $params['page'];
if (is_array($sortings[$params['sort_by']])) {
$sorting = join(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) . ' ' . $directions[$params['sort_order']];
} else {
$sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']];
}
$join = "LEFT JOIN ?:users USING(user_id)";
$condition = '';
if (!empty($params['period']) && $params['period'] != 'A') {
list($time_from, $time_to) = fn_create_periods($params);
$condition .= db_quote(" AND (?:logs.timestamp >= ?i AND ?:logs.timestamp <= ?i)", $time_from, $time_to);
}
if (isset($params['q_user']) && fn_string_no_empty($params['q_user'])) {
$condition .= db_quote(" AND (?:users.lastname LIKE ?l OR ?:users.firstname LIKE ?l)", "%" . trim($params['q_user']) . "%", "%" . trim($params['q_user']) . "%");
}
if (!empty($params['q_type'])) {
$condition .= db_quote(" AND (?:logs.type LIKE ?l OR ?:logs.type LIKE ?l)", "%{$params['q_type']}%", "%{$params['q_type']}%");
}
if (!empty($params['q_action'])) {
$condition .= db_quote(" AND (?:logs.action LIKE ?l OR ?:logs.action LIKE ?l)", "%{$params['q_action']}%", "%{$params['q_action']}%");
}
$limit = '';
$total = 0;
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(DISTINCT(?:logs.log_id)) FROM ?:logs ?p WHERE 1 ?p", $join, $condition);
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$data = db_get_array("SELECT " . join(', ', $fields) . " FROM ?:logs ?p WHERE 1 ?p ORDER BY {$sorting} {$limit}", $join, $condition);
if (!$total) {
$total = count($data);
}
foreach ($data as $k => $v) {
$data[$k]['backtrace'] = !empty($v['backtrace']) ? unserialize($v['backtrace']) : array();
$data[$k]['content'] = !empty($v['content']) ? unserialize($v['content']) : array();
}
return array($data, $params, $total);
}
function fn_get_banners($params, $lang_code = CART_LANGUAGE)
{
$default_params = array('items_per_page' => 0, 'sort_by' => 'name');
$params = array_merge($default_params, $params);
$sortings = array('timestamp' => '?:banners.timestamp', 'name' => '?:banner_descriptions.banner');
$directions = array('asc' => 'asc', 'desc' => 'desc');
$condition = $limit = '';
if (!empty($params['limit'])) {
$limit = db_quote(' LIMIT 0, ?i', $params['limit']);
}
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'] = 'name';
}
$sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']];
$condition = AREA == 'A' ? '' : " AND ?:banners.status = 'A' ";
$condition .= fn_get_localizations_condition('?:banners.localization');
if (!empty($params['item_ids'])) {
$condition .= db_quote(' AND ?:banners.banner_id IN (?n)', explode(',', $params['item_ids']));
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:banners.timestamp >= ?i AND ?:banners.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
$banners = db_get_array("SELECT ?:banners.banner_id, ?:banners.type, ?:banners.target, ?:banners.status, ?:banners.url, ?:banner_descriptions.banner, ?:banner_descriptions.description FROM ?:banners LEFT JOIN ?:banner_descriptions ON ?:banner_descriptions.banner_id = ?:banners.banner_id AND ?:banner_descriptions.lang_code = ?s WHERE 1 ?p ORDER BY ?p ?p", $lang_code, $condition, $sorting, $limit);
foreach ($banners as $k => $v) {
$banners[$k]['main_pair'] = fn_get_image_pairs($v['banner_id'], 'banner', 'M', true, false, $lang_code);
}
if (!empty($params['item_ids'])) {
$banners = fn_sort_by_ids($banners, explode(',', $params['item_ids']), 'banner_id');
}
fn_set_hook('get_banners', $banners);
return array($banners, $params);
}
function fn_get_subscribers($params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
// Init filter
$params = LastView::instance()->update('subscribers', $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('?:subscribers.subscriber_id', '?:subscribers.email', '?:subscribers.timestamp', '?:subscribers.subscriber_id', "GROUP_CONCAT(?:user_mailing_lists.list_id) as list_ids");
// Define sort fields
$sortings = array('email' => '?:subscribers.email', 'timestamp' => '?:subscribers.timestamp');
$condition = '';
$group_by = "?:subscribers.subscriber_id";
$join = db_quote(" LEFT JOIN ?:user_mailing_lists ON ?:user_mailing_lists.subscriber_id = ?:subscribers.subscriber_id");
if (isset($params['email']) && fn_string_not_empty($params['email'])) {
$condition .= db_quote(" AND ?:subscribers.email LIKE ?l", "%" . trim($params['email']) . "%");
}
if (!empty($params['list_id'])) {
$condition .= db_quote(" AND ?:user_mailing_lists.list_id = ?i", $params['list_id']);
}
if (!empty($params['confirmed'])) {
$condition .= db_quote(" AND ?:user_mailing_lists.confirmed = ?i", $params['confirmed'] == 'Y');
}
if (!empty($params['language'])) {
$condition .= db_quote(" AND ?:user_mailing_lists.lang_code = ?s", $params['language']);
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:subscribers.timestamp >= ?i AND ?:subscribers.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
$sorting = db_sort($params, $sortings, 'timestamp', 'desc');
$limit = '';
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(DISTINCT(?:subscribers.subscriber_id)) FROM ?:subscribers {$join} WHERE 1 {$condition}");
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$subscribers = db_get_array('SELECT ' . implode(', ', $fields) . " FROM ?:subscribers {$join} WHERE 1 {$condition} GROUP BY {$group_by} {$sorting} {$limit}");
return array($subscribers, $params);
}
function fn_update_sales_report($report_data, $report_id = 0, $lang_code = DESCR_SL)
{
if (empty($report_id)) {
$report_data['type'] = !empty($report_data['type']) ? $report_data['type'] : 'O';
$report_data['period'] = !empty($report_data['period']) ? $report_data['period'] : 'Y';
list($report_data['time_from'], $report_data['time_to']) = fn_create_periods($report_data);
$report_id = db_query("INSERT INTO ?:sales_reports ?e", $report_data);
fn_create_description('sales_reports_descriptions', 'report_id', $report_id, $report_data);
} else {
db_query('UPDATE ?:sales_reports SET ?u WHERE report_id = ?i', $report_data, $report_id);
db_query('UPDATE ?:sales_reports_descriptions SET ?u WHERE report_id = ?i AND lang_code = ?s', $report_data, $report_id, $lang_code);
}
if (!empty($report_data['tables'])) {
foreach ($report_data['tables'] as $k => $value) {
if (!extension_loaded('gd') && $value['type'] != 'T') {
if (empty($_flag)) {
fn_set_notification('W', __('warning'), __('text_gd_not_avail'));
}
$_flag = true;
$value['type'] = 'T';
}
db_query("UPDATE ?:sales_reports_tables SET ?u WHERE table_id = ?i", $value, $k);
db_query('UPDATE ?:sales_reports_table_descriptions SET ?u WHERE table_id = ?i AND lang_code = ?s', $value, $k, $lang_code);
if ($value['type'] == 'P') {
db_query("UPDATE ?:sales_reports_tables SET interval_id = 1 WHERE table_id = ?i", $k);
}
}
}
return $report_id;
}
/**
* Returns store logs
*
* @param array $params Search parameters
* @param int $items_per_page Logs limit
* @return array Logs with search parameters
*/
function fn_get_logs($params, $items_per_page = 0)
{
// Init filter
$params = LastView::instance()->update('logs', $params);
$default_params = array('page' => 1, 'items_per_page' => $items_per_page);
$params = array_merge($default_params, $params);
$sortings = array('timestamp' => array('?:logs.timestamp', '?:logs.log_id'), 'user' => array('?:users.lastname', '?:users.firstname'));
$fields = array('?:logs.*', '?:users.firstname', '?:users.lastname');
$sorting = db_sort($params, $sortings, 'timestamp', 'desc');
$join = "LEFT JOIN ?:users USING(user_id)";
$condition = '';
if (!empty($params['period']) && $params['period'] != 'A') {
list($time_from, $time_to) = fn_create_periods($params);
$condition .= db_quote(" AND (?:logs.timestamp >= ?i AND ?:logs.timestamp <= ?i)", $time_from, $time_to);
}
if (isset($params['q_user']) && fn_string_not_empty($params['q_user'])) {
$condition .= db_quote(" AND (?:users.lastname LIKE ?l OR ?:users.firstname LIKE ?l)", "%" . trim($params['q_user']) . "%", "%" . trim($params['q_user']) . "%");
}
if (!empty($params['q_type'])) {
$condition .= db_quote(" AND ?:logs.type = ?s", $params['q_type']);
}
if (!empty($params['q_action'])) {
$condition .= db_quote(" AND ?:logs.action = ?s", $params['q_action']);
}
if (Registry::get('runtime.company_id')) {
$condition .= db_quote(" AND ?:logs.company_id = ?i", Registry::get('runtime.company_id'));
}
fn_set_hook('admin_get_logs', $params, $condition, $join, $sorting);
$limit = '';
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(DISTINCT(?:logs.log_id)) FROM ?:logs ?p WHERE 1 ?p", $join, $condition);
$limit = db_paginate($params['page'], $params['items_per_page']);
}
$data = db_get_array("SELECT " . join(', ', $fields) . " FROM ?:logs ?p WHERE 1 ?p {$sorting} {$limit}", $join, $condition);
foreach ($data as $k => $v) {
$data[$k]['backtrace'] = !empty($v['backtrace']) ? unserialize($v['backtrace']) : array();
$data[$k]['content'] = !empty($v['content']) ? unserialize($v['content']) : array();
}
return array($data, $params);
}
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_news($params, $lang_code = CART_LANGUAGE)
{
$fields = array('?:news.*', 'descr.news', 'descr.description');
// Define sort fields
$sortings = array('position' => '?:news.position', 'name' => '?:news_descriptions.news', 'date' => '?:news.date');
$directions = array('asc' => 'asc', 'desc' => 'desc');
$limit = $condition = $sorting = '';
$join = db_quote(" LEFT JOIN ?:news_descriptions as descr ON descr.news_id = ?:news.news_id AND descr.lang_code = ?s", $lang_code);
$condition .= AREA == 'A' ? '1 ' : " ?:news.status = 'A'";
$condition .= fn_get_localizations_condition('?:news.localization');
if (!empty($params['b_id'])) {
$join .= " LEFT JOIN ?:block_links ON ?:block_links.object_id = ?:news.news_id AND ?:block_links.location = 'news'";
$condition .= db_quote(' AND ?:block_links.block_id = ?i', $params['b_id']);
}
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;
}
$tmp = array();
$tmp[] = db_quote("descr.news LIKE ?l", "%{$piece}%");
$tmp[] = db_quote("descr.description LIKE ?l", "%{$piece}%");
$_condition[] = '(' . join(' OR ', $tmp) . ')';
}
$_cond = implode($search_type, $_condition);
if (!empty($_condition)) {
$condition .= ' AND (' . $_cond . ') ';
}
}
if (!empty($params['limit'])) {
$limit = db_quote(" LIMIT 0, ?i", $params['limit']);
}
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 = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']];
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:news.date >= ?i AND ?:news.date <= ?i)", $params['time_from'], $params['time_to']);
}
if (!empty($params['item_ids'])) {
$condition .= db_quote(' AND ?:news.news_id IN (?n)', explode(',', $params['item_ids']));
}
// Used for Extended search
if (!empty($params['get_conditions'])) {
return array($fields, $join, $condition);
}
if (!empty($params['paginate'])) {
$params['page'] = empty($params['page']) ? 1 : $params['page'];
$total = db_get_field("SELECT COUNT(?:news.news_id) FROM ?:news ?p WHERE ?p", $join, $condition);
$limit = fn_paginate($params['page'], $total, AREA == 'A' ? Registry::get('settings.Appearance.admin_elements_per_page') : Registry::get('settings.Appearance.elements_per_page'));
}
fn_set_hook('get_news', $params, $fields, $join, $condition, $sorting, $limit, $lang_code);
$fields = join(', ', $fields);
$news = db_get_array("SELECT ?p FROM ?:news ?p WHERE ?p ORDER BY ?p ?p", $fields, $join, $condition, $sorting, $limit);
fn_set_hook('get_news_post', $news);
return array($news, $params);
}
function fn_get_gift_certificates($params, $items_per_page = 0)
{
// Init filter
$params = LastView::instance()->update('gift_certs', $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('?:gift_certificates.gift_cert_id', '?:gift_certificates.gift_cert_code', '?:gift_certificates.timestamp', '?:gift_certificates.amount', '?:gift_certificates.status', '?:gift_certificates.recipient', '?:gift_certificates.sender', '?:gift_certificates.send_via', '?:gift_certificates.email');
if (fn_allowed_for('ULTIMATE')) {
$fields[] = '?:gift_certificates.company_id';
}
// Define sort fields
$sortings = array('timestamp' => "?:gift_certificates.timestamp", 'amount' => "?:gift_certificates.amount", 'recipient' => "?:gift_certificates.recipient", 'sender' => "?:gift_certificates.sender", 'status' => "?:gift_certificates.status", 'gift_cert_code' => "?:gift_certificates.gift_cert_code", 'send_via' => "?:gift_certificates.send_via");
$sorting = db_sort($params, $sortings, 'timestamp', 'desc');
$condition = $join = '';
if (isset($params['sender']) && fn_string_not_empty($params['sender'])) {
$condition .= db_quote(" AND ?:gift_certificates.sender LIKE ?l", "%" . trim($params['sender']) . "%");
}
if (isset($params['recipient']) && fn_string_not_empty($params['recipient'])) {
$condition .= db_quote(" AND ?:gift_certificates.recipient LIKE ?l", "%" . trim($params['recipient']) . "%");
}
if (isset($params['email']) && fn_string_not_empty($params['email'])) {
$condition .= db_quote(" AND ?:gift_certificates.email LIKE ?l", "%" . trim($params['email']) . "%");
}
if (!empty($params['amount_from'])) {
$condition .= db_quote(" AND ?:gift_certificates.amount >= ?d", $params['amount_from']);
}
if (!empty($params['amount_to'])) {
$condition .= db_quote(" AND ?:gift_certificates.amount <= ?d", $params['amount_to']);
}
if (!empty($params['gift_cert_ids'])) {
$condition .= db_quote(" AND ?:gift_certificates.gift_cert_id IN (?n)", $params['gift_cert_ids']);
}
if (!empty($params['status'])) {
$condition .= db_quote(" AND ?:gift_certificates.status IN (?a)", $params['status']);
}
if (isset($params['gift_cert_code']) && fn_string_not_empty($params['gift_cert_code'])) {
$condition .= db_quote(" AND ?:gift_certificates.gift_cert_code LIKE ?l", "%" . trim($params['gift_cert_code']) . "%");
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:gift_certificates.timestamp >= ?i AND ?:gift_certificates.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
$limit = '';
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(*) FROM ?:gift_certificates WHERE 1 ?p", $condition . fn_get_gift_certificate_company_condition('?:gift_certificates.company_id'));
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$gift_certificates = db_get_array("SELECT ?p FROM ?:gift_certificates WHERE 1 ?p ?p ?p", implode(',', $fields), $condition . fn_get_gift_certificate_company_condition('?:gift_certificates.company_id'), $sorting, $limit);
foreach ($gift_certificates as $k => $v) {
$debit_balance = db_get_row("SELECT debit, debit_products FROM ?:gift_certificates_log WHERE gift_cert_id = ?i ORDER BY log_id DESC", $v['gift_cert_id']);
$gift_certificates[$k]['debit'] = empty($debit_balance) ? $v['amount'] : $debit_balance['debit'];
}
LastView::instance()->processResults('gift_certificates', $gift_certificates, $params);
return array($gift_certificates, $params);
}
public final function getOrderConditions($params)
{
$condition = $join = $group = '';
if (!empty($params['cname'])) {
$arr = explode(' ', $params['cname']);
if (sizeof($arr) == 2) {
$condition .= db_quote(" AND ?:orders.firstname LIKE ?l AND ?:orders.lastname LIKE ?l", "%{$arr['0']}%", "%{$arr['1']}%");
} else {
$condition .= db_quote(" AND (?:orders.firstname LIKE ?l OR ?:orders.lastname LIKE ?l)", "%{$params['cname']}%", "%{$params['cname']}%");
}
}
if (!empty($params['tax_exempt'])) {
$condition .= db_quote(" AND ?:orders.tax_exempt = ?s", $params['tax_exempt']);
}
if (!empty($params['email'])) {
$condition .= db_quote(" AND ?:orders.email LIKE ?l", "%{$params['email']}%");
}
if (!empty($params['user_id'])) {
$condition .= db_quote(' AND ?:orders.user_id IN (?n)', $params['user_id']);
}
if (!empty($params['total_from'])) {
$condition .= db_quote(" AND ?:orders.total >= ?d", fn_convert_price($params['total_from']));
}
if (!empty($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['order_id'])) {
$multiple_ids = strpos($params['order_id'], ',') !== false;
$condition .= db_quote(' AND ?:orders.order_id IN (?n)', !is_array($params['order_id']) && $multiple_ids ? explode(',', $params['order_id']) : $params['order_id']);
}
if (!empty($params['p_ids']) || !empty($params['product_view_id'])) {
$multiple_ids = strpos($params['p_ids'], ',') !== false;
$arr = $multiple_ids || !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";
}
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";
}
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";
}
}
return array($condition, $join);
}
/**
* Returns list of pages
* <i>$params</i> - Array of various parameters used for element selection:
* <ul>
* <li>page_id - If defined and not zero, get data for the page with this id; otherwise get data for all pages</li>
* <li>item_ids - A comma-delimited page identifiers list; if defined, get data for the pages with these ids; otherwise get data for all pages</li>
* <li>except_id - Identifier of the page to be excluded from the result</li>
* <li>parent_id - If defined and not zero, get data for the pages with this parent page id</li>
* <li>active_page_id - Identifier of the page being currently shown</li>
* <li>current_page_id - The same as <i>active_page_id</i></li>
*
* <li>add_root - If defined, additionally returns root element data</li>
* <li>subpages - If defined, additionally returns subpages</li>
* <li>get_tree - If defined, pages will be returned as tree; otherwise as list. Possible value: <i>plain</i></li>
* <li>visible - For pages tree: show visible branch only</li>
*
* <li>page - Number of the current page for pagination</li>
*
* <li>pdescr - If defined, additionally returns descriptions. Possible value: <i>Y</i></li>
*
* <li>vendor_pages - If defined, try to return pages for the company defined by <i>company_id</i></li>
* <li>company_id - If <i>vendor_pages</i> is defined: if defined, get data for the company with this id</li>
*
* <li>neighbours - If defined, try to return neighbor pages for the page with the id <i>neighbours_page_id</i></li>
* <li>neighbours_page_id - If <i>neighbours</i> is defined: if defined, get neighbor pages for the page with this id</li>
*
* <li>limit - If defined, used to limit your MySQL query results by this value</li>
* <li>sort_by - Table field to sort by, default is position</li>
* <li>sort_order - Sorting direction, ascending or descending; Possible values: <i>asc</i> or <i>desc</i>, default is <i>asc</i></li>
*
* <li>status - If defined, returns pages only with this status. Can be comma delimited statuses list</li>
*
* <li>period - If defined, get pages by time period. Time period is generated by ::fn_create_periods</li>
* <li>time_from - Returns pages created earlier than this time</li>
* <li>time_to - Returns pages created later than this time</li>
*
* <li>parent_page_id - Deprecated, <i>parent_id</i> used instead</li>
* <li>from_page_id - Deprecated, <i>parent_id</i> used instead</li>
* </ul>
* @param array $params Array of params
* @param int $items_per_page Limit items per page
* @param string $lang_code 2-letter language code
* @return array List of pages, params
*/
function fn_get_pages($params = array(), $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
/**
* Changes params for selecting pages
*
* @param array $params Pages 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_pages_pre', $params, $items_per_page, $lang_code);
$view_type = 'pages';
if (!empty($params['page_type']) && fn_is_exclusive_page_type($params['page_type'])) {
$view_type .= '_' . $params['page_type'];
}
// Init filter
$params = LastView::instance()->update($view_type, $params);
$default_params = array('page_id' => 0, 'page' => 1, 'visible' => false, 'get_tree' => '', 'pdescr' => '', 'subpages' => '', 'match' => '', 'page_type' => '', 'items_per_page' => $items_per_page);
if (is_array($params)) {
$params = array_merge($default_params, $params);
} else {
$params = $default_params;
}
if (empty($params['pname']) && empty($params['pdescr']) && empty($params['subpages'])) {
$params['pname'] = 'Y';
}
$fields = array('?:pages.*');
if (!empty($params['simple'])) {
$fields[] = '?:page_descriptions.page';
} else {
$fields[] = '?:page_descriptions.*';
}
// Define sort fields
$sortings = array('position' => array('?:pages.position', '?:page_descriptions.page'), 'name' => '?:page_descriptions.page', 'timestamp' => '?:pages.timestamp', 'type' => '?:pages.page_type', 'multi_level' => array('?:pages.parent_id', '?:pages.position', '?:page_descriptions.page'));
$auth =& $_SESSION['auth'];
$condition = '1';
$join = $limit = $group_by = '';
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 = array();
if (!empty($params['pname']) && $params['pname'] == 'Y') {
$tmp[] = db_quote("?:page_descriptions.page LIKE ?l", "%{$piece}%");
// check search words
}
if ($params['pdescr'] == 'Y') {
$tmp[] = db_quote("?:page_descriptions.description LIKE ?l", "%{$piece}%");
}
if (!empty($tmp)) {
$_condition[] = '(' . implode(' OR ', $tmp) . ')';
}
}
if (!empty($_condition)) {
$condition .= ' AND (' . implode($search_type, $_condition) . ')';
}
}
$condition .= fn_get_company_condition('?:pages.company_id');
if (isset($params['parent_id']) && $params['parent_id'] !== '') {
$p_ids = array();
if ($params['subpages'] == 'Y') {
$p_ids = db_get_fields("SELECT a.page_id FROM ?:pages as a LEFT JOIN ?:pages as b ON b.page_id = ?i WHERE a.id_path LIKE CONCAT(b.id_path, '/%')", $params['parent_id']);
}
$p_ids[] = $params['parent_id'];
$condition .= db_quote(" AND ?:pages.parent_id IN (?n)", $p_ids);
}
if (isset($params['parent_page_id'])) {
// set parent id, that was set in block properties
$params['from_page_id'] = $params['parent_page_id'];
}
if (!empty($params['from_page_id'])) {
$from_id_path = db_get_field("SELECT id_path FROM ?:pages WHERE page_id = ?i", $params['from_page_id']);
$condition .= db_quote(" AND ?:pages.id_path LIKE ?l", "{$from_id_path}/%");
}
if (!empty($params['status'])) {
$condition .= db_quote(" AND ?:pages.status IN (?a)", $params['status']);
}
if (!empty($params['vendor_pages']) && empty($params['company_id'])) {
return array(array(), $params);
} elseif (!empty($params['company_id'])) {
$condition .= db_quote(" AND ?:pages.company_id = ?i", $params['company_id']);
}
if (empty($params['full_search'])) {
$condition .= db_quote(" AND ?:pages.page_type IN (?a)", array_keys(fn_get_page_type_filter($params['page_type'])));
}
if (!empty($params['visible'])) {
// for pages tree: show visible branch only
$page_ids = array();
if (!empty($params['current_page_id'])) {
$cur_id_path = db_get_field("SELECT id_path FROM ?:pages WHERE page_id = ?i", $params['current_page_id']);
if (!empty($cur_id_path)) {
$page_ids = explode('/', $cur_id_path);
}
}
if (!empty($from_id_path)) {
$_page_ids = explode('/', $from_id_path);
$page_ids = array_merge($page_ids, $_page_ids);
$page_ids = array_unique($page_ids);
}
$page_ids[] = $params['page_id'];
$condition .= db_quote(" AND ?:pages.parent_id IN (?n)", $page_ids);
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:pages.timestamp >= ?i AND ?:pages.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
if (!empty($params['item_ids'])) {
// get only defined pages
$condition .= db_quote(" AND ?:pages.page_id IN (?n)", explode(',', $params['item_ids']));
}
if (!empty($params['except_id']) && (empty($params['item_ids']) || !empty($params['item_ids']) && !in_array($params['except_id'], explode(',', $params['item_ids'])))) {
$condition .= db_quote(' AND ?:pages.page_id != ?i AND ?:pages.parent_id != ?i', $params['except_id'], $params['except_id']);
}
if (AREA != 'A') {
$condition .= " AND (" . fn_find_array_in_set($auth['usergroup_ids'], '?:pages.usergroup_ids', true) . ")";
$condition .= fn_get_localizations_condition('?:pages.localization', true);
$condition .= db_quote(" AND (use_avail_period = ?s OR (use_avail_period = ?s AND avail_from_timestamp <= ?i AND avail_till_timestamp >= ?i))", 'N', 'Y', TIME, TIME);
}
$join = db_quote('LEFT JOIN ?:page_descriptions ON ?:pages.page_id = ?:page_descriptions.page_id AND ?:page_descriptions.lang_code = ?s', $lang_code);
if (!empty($params['limit'])) {
$limit = db_quote(" LIMIT 0, ?i", $params['limit']);
}
if (!empty($params['neighbours'])) {
$parent_ids = array();
if (!empty($params['neighbours_page_id'])) {
$id_path = db_get_field("SELECT id_path FROM ?:pages WHERE page_id = ?i", $params['neighbours_page_id']);
$parent_ids = explode('/', $id_path);
if (count($parent_ids) == 1) {
array_unshift($parent_ids, 0);
}
$params['root_id'] = $parent_ids[0];
} else {
$parent_ids[] = 0;
}
$condition .= db_quote(" AND ?:pages.parent_id IN (?n)", array_unique($parent_ids));
}
fn_set_hook('get_pages', $params, $join, $condition, $fields, $group_by, $sortings, $lang_code);
if (!empty($params['get_tree'])) {
$params['sort_by'] = 'multi_level';
}
$sorting = db_sort($params, $sortings, 'position', 'asc');
if (!empty($group_by)) {
$group_by = ' GROUP BY ' . $group_by;
}
// Get search conditions
if (!empty($params['get_conditions'])) {
return array($fields, $join, $condition);
}
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(DISTINCT(?:pages.page_id)) FROM ?:pages ?p WHERE ?p ?p ?p", $join, $condition, $group_by, $sorting);
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$pages = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:pages ?p WHERE ?p ?p ?p ?p", 'page_id', $join, $condition, $group_by, $sorting, $limit);
if (!empty($params['active_page_id']) && !empty($pages[$params['active_page_id']])) {
$pages[$params['active_page_id']]['active'] = true;
Registry::set('runtime.active_page_ids', explode('/', $pages[$params['active_page_id']]['id_path']));
}
if (!empty($pages)) {
foreach ($pages as $k => $v) {
$pages[$k]['level'] = substr_count($v['id_path'], '/');
}
if (!empty($params['get_tree'])) {
$delete_keys = array();
foreach ($pages as $k => $v) {
if (!empty($v['parent_id']) && !empty($pages[$v['parent_id']])) {
$pages[$v['parent_id']]['subpages'][$v['page_id']] =& $pages[$k];
$delete_keys[] = $k;
}
if (!empty($v['parent_id']) && (!isset($params['root_id']) && empty($pages[$v['parent_id']]) || isset($params['root_id']) && $v['parent_id'] != $params['root_id']) && (empty($params['from_page_id']) || $params['from_page_id'] != $v['parent_id'])) {
// delete pages that don't have parent. FIXME: should be done on database layer
$delete_keys[] = $k;
}
}
foreach ($delete_keys as $k) {
unset($pages[$k]);
}
} elseif (!empty($params['item_ids'])) {
$pages = fn_sort_by_ids($pages, explode(',', $params['item_ids']), 'page_id');
}
if ($params['get_tree'] == 'plain') {
$pages = fn_multi_level_to_plain($pages, 'subpages');
}
if (!empty($params['get_children_count'])) {
$where_condition = !empty($params['except_id']) ? db_quote(' AND page_id != ?i', $params['except_id']) : '';
if ($params['get_tree'] == 'plain') {
$_page_ids = array();
foreach ($pages as $_p) {
$_page_ids[] = $_p['page_id'];
}
} else {
$_page_ids = array_keys($pages);
}
$children = db_get_hash_single_array("SELECT parent_id, COUNT(page_id) as children FROM ?:pages WHERE parent_id IN (?n) ?p GROUP BY parent_id", array('parent_id', 'children'), $_page_ids, $where_condition);
if (!empty($children)) {
if ($params['get_tree'] == 'plain') {
foreach ($pages as $_id => $_p) {
if (!empty($children[$_p['page_id']])) {
$pages[$_id]['has_children'] = true;
}
}
} else {
foreach ($children as $k => $v) {
$pages[$k]['has_children'] = !empty($v);
}
}
}
}
}
if (!empty($params['add_root'])) {
array_unshift($pages, array('page_id' => '', 'page' => $params['add_root']));
}
fn_dropdown_appearance_cut_second_third_levels($pages, 'subpages', $params);
fn_set_hook('post_get_pages', $pages, $params, $lang_code);
LastView::instance()->processResults($view_type, $pages, $params);
return array($pages, $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);
}
function fn_get_tags($params = array(), $items_per_page = 0)
{
// Init filter
$params = fn_init_view('tags', $params);
$default_params = array('page' => 1);
$params = array_merge($default_params, $params);
// Define sort fields
$sortings = array('tag' => '?:tags.tag', 'status' => '?:tags.status', 'popularity' => 'popularity', 'users' => 'users');
$directions = array('asc' => 'asc', 'desc' => 'desc');
$conditions = '';
if (!empty($params['see']) && $params['see'] == 'my' && empty($params['user_id'])) {
return array(array(), array());
}
if (!empty($params['object_type'])) {
$conditions .= db_quote(" AND ?:tag_links.object_type = ?s", $params['object_type']);
}
if (!empty($params['status'])) {
$conditions .= db_quote(" AND ?:tags.status IN (?a)", $params['status']);
}
if (!empty($params['object_id'])) {
$conditions .= db_quote(" AND ?:tag_links.object_id = ?s", $params['object_id']);
}
if (!empty($params['user_id'])) {
$conditions .= db_quote(" AND ?:tag_links.user_id = ?s", $params['user_id']);
}
if (isset($params['tag']) && fn_string_no_empty($params['tag'])) {
$conditions .= db_quote(" AND ?:tags.tag LIKE ?l", "%" . trim($params['tag']) . "%");
}
$my_tag_field = '';
if (!empty($params['user_and_popular'])) {
$conditions .= db_quote(" AND IF(?:tag_links.user_id = ?i, 1, ?:tags.status IN ('A'))", $params['user_and_popular']);
$my_tag_field = db_quote(", COUNT(IF(?:tag_links.user_id = ?i, 1, NULL)) as my_tag", $params['user_and_popular']);
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$conditions .= db_quote(" AND (?:tags.timestamp >= ?i AND ?:tags.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
$limit = '';
if (!empty($params['limit'])) {
$limit = db_quote(' LIMIT 0, ?i', $params['limit']);
}
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(DISTINCT(?:tags.tag_id)) FROM ?:tags LEFT JOIN ?:tag_links ON ?:tags.tag_id = ?:tag_links.tag_id WHERE 1 ?p", $conditions);
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
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'] = 'tag';
}
$sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']];
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
$tags = db_get_hash_array("SELECT ?:tags.tag_id, ?:tag_links.object_id, ?:tag_links.object_type, ?:tag_links.user_id, COUNT(?:tag_links.tag_id) as popularity, COUNT(DISTINCT(?:tag_links.user_id)) as users, ?:tags.tag, ?:tags.status {$my_tag_field} FROM ?:tags LEFT JOIN ?:tag_links ON ?:tag_links.tag_id = ?:tags.tag_id WHERE 1 ?p GROUP BY ?:tags.tag_id ORDER BY {$sorting} {$limit}", 'tag_id', $conditions);
if (!empty($params['count_objects'])) {
$objs = db_get_array("SELECT tag_id, COUNT(DISTINCT(object_id)) as count, object_type FROM ?:tag_links WHERE tag_id IN (?n) GROUP BY tag_id, object_type", array_keys($tags));
foreach ($objs as $v) {
$tags[$v['tag_id']]['objects_count'][$v['object_type']] = $v['count'];
}
}
// Generate popularity level
foreach ($tags as $k => $v) {
$level = ceil(log($v['popularity']));
$tags[$k]['level'] = $level > TAGS_MAX_LEVEL ? TAGS_MAX_LEVEL : $level;
}
if (!empty($params['sort_popular'])) {
$tags = fn_sort_array_by_key($tags, 'tag', SORT_ASC);
}
return array($tags, $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('|', 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);
}
$directions = array('asc' => 'asc', 'desc' => 'desc');
if (empty($sort_order) || empty($directions[$sort_order])) {
$sort_order = 'desc';
}
if (empty($sort_by) || empty($sortings[$sort_by])) {
$sort_by = 'date';
}
$view->assign('sort_order', $sort_order == 'asc' ? 'desc' : 'asc');
$view->assign('sort_by', $sort_by);
$sorting = $sortings[$sort_by] . " " . $directions[$sort_order];
if (!empty($_REQUEST['payout_search'])) {
$payout_search = $_REQUEST['payout_search'];
$payout_search_data = $payout_search;
$payout_search_condition = '1';
if (!empty($_REQUEST['period']) && $_REQUEST['period'] != 'A') {
list($time_from, $time_to) = fn_create_periods($_REQUEST);
$payout_search_data['period'] = $_REQUEST['period'];
$payout_search_data['time_from'] = $time_from;
$payout_search_data['time_to'] = $time_to;
$payout_search_condition .= db_quote(" AND (?:affiliate_payouts.date >= ?i AND ?:affiliate_payouts.date <= ?i)", $time_from, $time_to);
} else {
$payout_search_data['period'] = 'A';
}
if (!empty($payout_search['status'])) {
$payout_search_condition .= db_quote(" AND ?:affiliate_payouts.status = ?s ", $payout_search['status']);
}
$payout_search_data['amount']['from'] = floatval(@$payout_search['amount']['from']);
if (!empty($payout_search_data['amount']['from'])) {
$payout_search_condition .= db_quote(" AND ?:affiliate_payouts.amount >= ?d ", fn_convert_price($payout_search_data['amount']['from']));
} else {
$payout_search_data['amount']['from'] = '';
public function prepare()
{
$condition = array();
$table_name = $this->model->getTableName();
$search_fields = $this->model->getSearchFields();
$primary_field = $this->model->getPrimaryField();
if (isset($this->params['ids'])) {
$condition[] = db_quote("{$table_name}.{$primary_field} IN(?a)", (array) $this->params['ids']);
}
if (isset($this->params['not_ids'])) {
$condition[] = db_quote("{$table_name}.{$primary_field} NOT IN(?a)", (array) $this->params['not_ids']);
}
if (!empty($search_fields['number'])) {
foreach ($search_fields['number'] as $_key => $_field) {
$param = !is_numeric($_key) ? $_key : $_field;
$fields = (array) $_field;
if (isset($this->params[$param]) && fn_string_not_empty($this->params[$param])) {
$sub_condition = array();
foreach ($fields as $field) {
$sub_condition[] = db_quote("{$field} = ?i", $this->params[$param]);
}
$condition[] = $this->mixSubConditions($sub_condition);
}
}
}
if (!empty($search_fields['range'])) {
$ranges = array('from' => '>=', 'to' => '<=');
foreach ($search_fields['range'] as $_key => $_field) {
$param = !is_numeric($_key) ? $_key : $_field;
$fields = (array) $_field;
foreach ($ranges as $_range_name => $_range_symbol) {
if (!empty($this->params[$param . '_' . $_range_name])) {
$sub_condition = array();
foreach ($fields as $field) {
$sub_condition[] = db_quote("{$field} ?p ?i", $_range_symbol, $this->params[$param . '_' . $_range_name]);
}
$condition[] = $this->mixSubConditions($sub_condition);
}
}
}
}
if (!empty($search_fields['in'])) {
foreach ($search_fields['in'] as $_key => $_field) {
$param = !is_numeric($_key) ? $_key : $_field;
$fields = (array) $_field;
if (!empty($this->params[$param])) {
$_in_values = !is_array($this->params[$param]) ? explode(',', $this->params[$param]) : $this->params[$param];
$sub_condition = array();
foreach ($fields as $field) {
$sub_condition[] = db_quote("{$field} IN(?a)", $_in_values);
}
$condition[] = $this->mixSubConditions($sub_condition);
}
}
}
if (!empty($search_fields['not_in'])) {
foreach ($search_fields['not_in'] as $_key => $_field) {
$param = !is_numeric($_key) ? $_key : $_field;
$fields = (array) $_field;
if (!empty($this->params[$param])) {
$_in_values = !is_array($this->params[$param]) ? explode(',', $this->params[$param]) : $this->params[$param];
$sub_condition = array();
foreach ($fields as $field) {
$sub_condition[] = db_quote("{$field} NOT IN(?a)", $_in_values);
}
$condition[] = $this->mixSubConditions($sub_condition);
}
}
}
if (!empty($search_fields['string'])) {
foreach ($search_fields['string'] as $_key => $_field) {
$param = !is_numeric($_key) ? $_key : $_field;
$fields = (array) $_field;
if (isset($this->params[$param]) && fn_string_not_empty($this->params[$param])) {
$sub_condition = array();
foreach ($fields as $field) {
$sub_condition[] = db_quote("{$field} LIKE ?s", trim($this->params[$param]));
}
$condition[] = $this->mixSubConditions($sub_condition);
}
}
}
if (!empty($search_fields['text'])) {
foreach ($search_fields['text'] as $_key => $_field) {
$param = !is_numeric($_key) ? $_key : $_field;
$fields = (array) $_field;
if (isset($this->params[$param]) && fn_string_not_empty($this->params[$param])) {
$sub_condition = array();
$like = '%' . trim($this->params[$param]) . '%';
foreach ($fields as $field) {
$sub_condition[] = db_quote("{$field} LIKE ?l", $like);
}
$condition[] = $this->mixSubConditions($sub_condition);
}
}
}
if (!empty($search_fields['time'])) {
$process_time = function ($time) {
return str_replace('.', '/', $time);
};
foreach ($search_fields['time'] as $_key => $_field) {
$param = !is_numeric($_key) ? $_key : $_field;
$fields = (array) $_field;
$period = !empty($this->params[$param . 'period']) ? $this->params[$param . 'period'] : null;
$from = !empty($this->params[$param . 'time_from']) ? $this->params[$param . 'time_from'] : 0;
$to = !empty($this->params[$param . 'time_to']) ? $this->params[$param . 'time_to'] : 0;
if (!empty($from) || !empty($to)) {
list($from, $to) = fn_create_periods(array('period' => $period, 'time_from' => $process_time($from), 'time_to' => $process_time($to)));
$sub_condition = array();
foreach ($fields as $field) {
$sub_condition[] = db_quote("({$field} >= ?i AND {$field} <= ?i)", $from, $to);
}
$condition[] = $this->mixSubConditions($sub_condition);
} else {
if (!empty($this->params[$param . '_from'])) {
$sub_condition = array();
foreach ($fields as $field) {
$sub_condition[] = db_quote("{$field} >= ?i", $this->params[$param . '_from']);
}
$condition[] = $this->mixSubConditions($sub_condition);
}
if (!empty($this->params[$param . '_to'])) {
$sub_condition = array();
foreach ($fields as $field) {
$sub_condition[] = db_quote("{$field} <= ?i", $this->params[$param . '_to']);
}
$condition[] = $this->mixSubConditions($sub_condition);
}
}
}
}
$this->result = array_filter(array_merge($condition, (array) $this->model->getExtraCondition($this->params)));
}
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_subscribers($params, $lang_code = CART_LANGUAGE)
{
// Init filter
$params = fn_init_view('subscribers', $params);
// Set default values to input params
$default_params = array('page' => 1);
$params = array_merge($default_params, $params);
// Define fields that should be retrieved
$fields = array('?:subscribers.subscriber_id', '?:subscribers.email', '?:subscribers.timestamp', '?:subscribers.subscriber_id', "GROUP_CONCAT(?:user_mailing_lists.list_id) as list_ids");
// Define sort fields
$sortings = array('email' => '?:subscribers.email', 'timestamp' => '?:subscribers.timestamp');
$directions = array('asc' => 'asc', 'desc' => 'desc');
$condition = '';
$group_by = "?:subscribers.subscriber_id";
$join = db_quote(" LEFT JOIN ?:user_mailing_lists ON ?:user_mailing_lists.subscriber_id = ?:subscribers.subscriber_id");
if (isset($params['email']) && fn_string_no_empty($params['email'])) {
$condition .= db_quote(" AND ?:subscribers.email LIKE ?l", "%" . trim($params['email']) . "%");
}
if (!empty($params['list_id'])) {
$condition .= db_quote(" AND ?:user_mailing_lists.list_id = ?i", $params['list_id']);
}
if (!empty($params['confirmed'])) {
$condition .= db_quote(" AND ?:user_mailing_lists.confirmed = ?i", $params['confirmed'] == 'Y');
}
if (!empty($params['format'])) {
$condition .= db_quote(" AND ?:user_mailing_lists.format = ?i", $params['format']);
}
if (!empty($params['language'])) {
$condition .= db_quote(" AND ?:user_mailing_lists.lang_code = ?s", $params['language']);
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:subscribers.timestamp >= ?i AND ?:subscribers.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
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';
}
$sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']];
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
$total = db_get_field("SELECT COUNT(DISTINCT(?:subscribers.subscriber_id)) FROM ?:subscribers {$join} WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total, Registry::get('settings.Appearance.admin_elements_per_page'));
$subscribers = db_get_array('SELECT ' . implode(', ', $fields) . " FROM ?:subscribers {$join} WHERE 1 {$condition} GROUP BY {$group_by} ORDER BY {$sorting} {$limit}");
return array($subscribers, $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_shipments_info($params, $items_per_page = 0)
{
// Init view params
$params = LastView::instance()->update('shipments', $params);
// Set default values to input params
$default_params = array('page' => 1, 'items_per_page' => $items_per_page);
$params = array_merge($default_params, $params);
$fields_list = array('?:shipments.shipment_id', '?:shipments.timestamp AS shipment_timestamp', '?:shipments.comments', '?:shipment_items.order_id', '?:orders.timestamp AS order_timestamp', '?:orders.s_firstname', '?:orders.s_lastname', '?:orders.user_id');
$joins = array('LEFT JOIN ?:shipment_items ON (?:shipments.shipment_id = ?:shipment_items.shipment_id)', 'LEFT JOIN ?:orders ON (?:shipment_items.order_id = ?:orders.order_id)');
$condition = '';
if (Registry::get('runtime.company_id')) {
$joins[] = 'LEFT JOIN ?:companies ON (?:companies.company_id = ?:orders.company_id)';
$condition = db_quote(' AND ?:companies.company_id = ?i', Registry::get('runtime.company_id'));
}
$group = array('?:shipments.shipment_id');
// Define sort fields
$sortings = array('id' => "?:shipments.shipment_id", 'order_id' => "?:orders.order_id", 'shipment_date' => "?:shipments.timestamp", 'order_date' => "?:orders.timestamp", 'customer' => array("?:orders.s_lastname", "?:orders.s_firstname"));
$sorting = db_sort($params, $sortings, 'id', 'desc');
if (isset($params['advanced_info']) && $params['advanced_info']) {
$fields_list[] = '?:shipments.shipping_id';
$fields_list[] = '?:shipping_descriptions.shipping AS shipping';
$fields_list[] = '?:shipments.tracking_number';
$fields_list[] = '?:shipments.carrier';
$joins[] = ' LEFT JOIN ?:shippings ON (?:shipments.shipping_id = ?:shippings.shipping_id)';
$joins[] = db_quote(' LEFT JOIN ?:shipping_descriptions ON (?:shippings.shipping_id = ?:shipping_descriptions.shipping_id AND ?:shipping_descriptions.lang_code = ?s)', DESCR_SL);
}
if (!empty($params['order_id'])) {
$condition .= db_quote(' AND ?:shipment_items.order_id = ?i', $params['order_id']);
}
if (!empty($params['shipment_id'])) {
$condition .= db_quote(' AND ?:shipments.shipment_id = ?i', $params['shipment_id']);
}
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 ?: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['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 ?:shipment_items.product_id IN (?n)", $arr);
} else {
$condition .= db_quote(" AND ?:shipment_items.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)), ','));
}
$joins[] = "LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id";
}
if (!empty($params['shipment_period']) && $params['shipment_period'] != 'A') {
$params['time_from'] = $params['shipment_time_from'];
$params['time_to'] = $params['shipment_time_to'];
$params['period'] = $params['shipment_period'];
list($params['shipment_time_from'], $params['shipment_time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:shipments.timestamp >= ?i AND ?:shipments.timestamp <= ?i)", $params['shipment_time_from'], $params['shipment_time_to']);
}
if (!empty($params['order_period']) && $params['order_period'] != 'A') {
$params['time_from'] = $params['order_time_from'];
$params['time_to'] = $params['order_time_to'];
$params['period'] = $params['order_period'];
list($params['order_time_from'], $params['order_time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:orders.timestamp >= ?i AND ?:orders.timestamp <= ?i)", $params['order_time_from'], $params['order_time_to']);
}
fn_set_hook('get_shipments', $params, $fields_list, $joins, $condition, $group);
$fields_list = implode(', ', $fields_list);
$joins = implode(' ', $joins);
$group = implode(', ', $group);
if (!empty($group)) {
$group = ' GROUP BY ' . $group;
}
$limit = '';
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(DISTINCT(?:shipments.shipment_id)) FROM ?:shipments {$joins} WHERE 1 {$condition}");
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$shipments = db_get_array("SELECT {$fields_list} FROM ?:shipments {$joins} WHERE 1 {$condition} {$group} {$sorting} {$limit}");
if (isset($params['advanced_info']) && $params['advanced_info'] && !empty($shipments)) {
$shipment = reset($shipments);
$order_info = fn_get_order_info($shipment['order_id']);
foreach ($shipments as $id => $shipment) {
$items = db_get_array('SELECT item_id, amount FROM ?:shipment_items WHERE shipment_id = ?i', $shipment['shipment_id']);
if (!empty($items)) {
foreach ($items as $item) {
$shipments[$id]['products'][$item['item_id']] = $item['amount'];
if (!empty($order_info['products'][$item['item_id']]['extra']['group_key'])) {
$shipments[$id]['group_key'] = $order_info['products'][$item['item_id']]['extra']['group_key'];
} else {
$shipments[$id]['group_key'] = 0;
}
}
}
}
if (Settings::instance()->getValue('use_shipments', '', $order_info['company_id']) != 'Y') {
foreach ($shipments as $id => $shipment) {
$shipments[$id]['one_full'] = true;
foreach ($order_info['products'] as $product_key => $product) {
$group_key = !empty($product['extra']['group_key']) ? $product['extra']['group_key'] : 0;
if ($shipment['group_key'] == $group_key) {
if (empty($shipment['products'][$product_key]) || $shipment['products'][$product_key] < $product['amount']) {
$shipments[$id]['one_full'] = false;
break;
}
}
}
}
}
}
/**
* Changes selected shipments
*
* @param array $shipments Array of shipments
* @param array $params Shipments search params
*/
fn_set_hook('get_shipments_info_post', $shipments, $params);
LastView::instance()->processResults('shipments_info', $shipments, $params);
return array($shipments, $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_em_get_subscribers($params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
// Init filter
$params = LastView::instance()->update('em_subscribers', $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('?:em_subscribers.subscriber_id', '?:em_subscribers.email', '?:em_subscribers.timestamp', '?:em_subscribers.name', '?:em_subscribers.unsubscribe_key', '?:em_subscribers.status');
// Define sort fields
$sortings = array('email' => '?:em_subscribers.email', 'name' => '?:em_subscribers.name', 'status' => '?:em_subscribers.status', 'timestamp' => '?:em_subscribers.timestamp');
$condition = '';
$group_by = '';
$join = '';
if (!empty($params['subscriber_id'])) {
$condition .= db_quote(" AND ?:em_subscribers.subscriber_id = ?i", $params['subscriber_id']);
}
if (isset($params['email']) && fn_string_not_empty($params['email'])) {
$condition .= db_quote(" AND ?:em_subscribers.email LIKE ?l", "%" . trim($params['email']) . "%");
}
if (!empty($params['status'])) {
$condition .= db_quote(" AND ?:em_subscribers.status = ?s", $params['status']);
}
if (!empty($params['name'])) {
$condition .= db_quote(" AND ?:em_subscribers.name LIKE ?l", "%" . $params['name'] . "%");
}
if (!empty($params['lang_code'])) {
$condition .= db_quote(" AND ?:em_subscribers.lang_code = ?s", $params['lang_code']);
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:em_subscribers.timestamp >= ?i AND ?:em_subscribers.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
$condition .= fn_em_get_company_condition();
$sorting = db_sort($params, $sortings, 'timestamp', 'desc');
$limit = '';
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(?:em_subscribers.subscriber_id) FROM ?:em_subscribers {$join} WHERE 1 {$condition}");
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$subscribers = db_get_array('SELECT ' . implode(', ', $fields) . " FROM ?:em_subscribers {$join} WHERE 1 {$condition} {$group_by} {$sorting} {$limit}");
return array($subscribers, $params);
}
function fn_get_news($params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
$default_params = array('page' => 1, 'items_per_page' => $items_per_page);
$params = array_merge($default_params, $params);
$fields = array('?:news.*', 'descr.news', 'descr.description');
// Define sort fields
$sortings = array('position' => '?:news.position', 'name' => 'descr.news', 'date' => '?:news.date');
$limit = $condition = '';
$join = db_quote(" LEFT JOIN ?:news_descriptions AS descr ON descr.news_id = ?:news.news_id AND descr.lang_code = ?s", $lang_code);
$condition .= AREA == 'A' ? '1 ' : " ?:news.status = 'A'";
$condition .= fn_get_localizations_condition('?:news.localization');
// Get additional information about companies
if (fn_allowed_for('ULTIMATE')) {
$fields[] = ' ?:companies.company as company';
$sortings['company'] = 'company';
$join .= db_quote(" LEFT JOIN ?:companies ON ?:companies.company_id = ?:news.company_id");
}
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 = array();
$tmp[] = db_quote("descr.news LIKE ?l", "%{$piece}%");
$tmp[] = db_quote("descr.description LIKE ?l", "%{$piece}%");
$_condition[] = '(' . join(' OR ', $tmp) . ')';
}
$_cond = implode($search_type, $_condition);
if (!empty($_condition)) {
$condition .= ' AND (' . $_cond . ') ';
}
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:news.date >= ?i AND ?:news.date <= ?i)", $params['time_from'], $params['time_to']);
}
if (!empty($params['item_ids'])) {
$condition .= db_quote(' AND ?:news.news_id IN (?n)', explode(',', $params['item_ids']));
}
$limit = '';
if (!empty($params['limit'])) {
$limit = db_quote(" LIMIT 0, ?i", $params['limit']);
} elseif (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(?:news.news_id) FROM ?:news ?p WHERE ?p", $join, $condition);
$limit = db_paginate($params['page'], $params['items_per_page']);
}
fn_set_hook('get_news', $params, $fields, $join, $condition, $sorting, $limit, $lang_code);
$sorting = db_sort($params, $sortings, 'date', 'desc');
// Used for Extended search
if (!empty($params['get_conditions'])) {
return array($fields, $join, $condition);
}
$fields = join(', ', $fields);
$news = db_get_array("SELECT ?p FROM ?:news ?p WHERE ?p ?p ?p", $fields, $join, $condition, $sorting, $limit);
/**
* Get additional data for selected news
*
* @param array $news news list
* @param string $lang_code language code
*/
fn_set_hook('get_news_post', $news, $lang_code);
return array($news, $params);
}
$post_fix .= '&time_to=' . $params['time_to'];
}
if (!empty($params['status'])) {
$post_fix .= '&status=' . $params['status'];
}
$suffix = ".manage" . $post_fix;
}
return array(CONTROLLER_STATUS_OK, "sdek_status{$suffix}");
}
if ($mode == 'manage') {
$params = $_REQUEST;
$t_date = date("Y-m-d", TIME);
$shipping = db_get_array("SELECT b.service_params FROM ?:shipping_services as a LEFT JOIN ?:shippings as b ON a.service_id = b.service_id WHERE a.module = 'sdek'");
$data_status = array();
$data['period'] = !empty($params['period']) ? $params['period'] : 'A';
list($data['time_from'], $data['time_to']) = fn_create_periods($_REQUEST);
if ($data['period'] == 'A') {
$data['time_from'] = date("Y-01-1 00:00:00");
$data['time_to'] = date("Y-m-d 23:59:59", $data['time_to']);
} else {
$data['time_from'] = date("Y-m-d 00:00:00", $data['time_from']);
$data['time_to'] = date("Y-m-d 23:59:59", $data['time_to']);
}
foreach ($shipping as $shipping_id => $d_shipping) {
$service_params = unserialize($d_shipping['service_params']);
if (!empty($service_params['authlogin']) && !empty($service_params['authpassword'])) {
$shipping_params['Account'] = $service_params['authlogin'];
$shipping_params['Secure'] = md5($t_date . '&' . $service_params['authpassword']);
$shipping_params['Date'] = $t_date;
$shipping_params['ChangePeriod']['DateFirst'] = $data['time_from'];
$shipping_params['ChangePeriod']['DateLast'] = $data['time_to'];
function fn_get_banners_statistics($params)
{
$condition = '';
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
} else {
$params['period'] = 'A';
$params['time_from'] = '';
$params['time_to'] = '';
}
if (!empty($params['time_from'])) {
$condition .= db_quote(" AND timestamp >= ?i", $params['time_from']);
}
if (!empty($params['time_to'])) {
$condition .= db_quote(" AND timestamp <= ?i", $params['time_to']);
}
$log = db_get_hash_multi_array("SELECT type, COUNT(type) as number, banner_id FROM ?:stat_banners_log WHERE 1 ?p GROUP BY banner_id, type ORDER BY timestamp DESC", array('banner_id', 'type'), $condition);
foreach ($log as $b_id => $v) {
if (!empty($v['C']['number']) && !empty($v['V']['number'])) {
$log[$b_id]['conversion'] = sprintf('%.2f', $v['C']['number'] / $v['V']['number'] * 100);
if (floatval($log[$b_id]['conversion']) == intval($log[$b_id]['conversion'])) {
$log[$b_id]['conversion'] = intval($log[$b_id]['conversion']);
}
}
}
$banner_ids = array_keys($log);
if (!empty($banner_ids)) {
$_params = array('item_ids' => implode(',', $banner_ids));
list($banners) = fn_get_banners($_params);
} else {
$banners = array();
}
$data = array('log' => $log, 'banners' => $banners);
return array($data, $params, count($banner_ids));
}
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);
}
function fn_get_shipments_info($params, $items_per_page = SHIPMENTS_PER_PAGE)
{
// Init view params
$params = fn_init_view('shipments', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// default page is 1
$fields_list = array('?:shipments.shipment_id', '?:shipments.timestamp AS shipment_timestamp', '?:shipments.comments', '?:shipment_items.order_id', '?:orders.timestamp AS order_timestamp', '?:orders.s_firstname', '?:orders.s_lastname');
$joins = array('LEFT JOIN ?:shipment_items ON (?:shipments.shipment_id = ?:shipment_items.shipment_id)', 'LEFT JOIN ?:orders ON (?:shipment_items.order_id = ?:orders.order_id)');
$group = array('?:shipments.shipment_id');
// Define sort fields
$sortings = array('id' => "?:shipments.shipment_id", 'order_id' => "?:orders.order_id", 'shipment_date' => "?:shipments.timestamp", 'order_date' => "?:orders.timestamp", 'customer' => array("?:orders.s_lastname", "?:orders.s_firstname"));
$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'] = 'id';
}
$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 = '';
if (isset($params['advanced_info']) && $params['advanced_info']) {
$fields_list[] = '?:shipping_descriptions.shipping AS shipping';
$fields_list[] = '?:shipments.tracking_number';
$fields_list[] = '?:shipments.carrier';
$joins[] = ' LEFT JOIN ?:shippings ON (?:shipments.shipping_id = ?:shippings.shipping_id)';
$joins[] = ' LEFT JOIN ?:shipping_descriptions ON (?:shippings.shipping_id = ?:shipping_descriptions.shipping_id)';
$condition .= db_quote(' AND ?:shipping_descriptions.lang_code = ?s', DESCR_SL);
}
if (!empty($params['order_id'])) {
$condition .= db_quote(' AND ?:shipment_items.order_id = ?i', $params['order_id']);
}
if (!empty($params['shipment_id'])) {
$condition .= db_quote(' AND ?:shipments.shipment_id = ?i', $params['shipment_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['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 ?:shipment_items.product_id IN (?n)", $arr);
} else {
$condition .= db_quote(" AND ?:shipment_items.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)), ','));
}
$joins[] = "LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id";
}
if (!empty($params['shipment_period']) && $params['shipment_period'] != 'A') {
$params['time_from'] = $params['shipment_time_from'];
$params['time_to'] = $params['shipment_time_to'];
$params['period'] = $params['shipment_period'];
list($params['shipment_time_from'], $params['shipment_time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:shipments.timestamp >= ?i AND ?:shipments.timestamp <= ?i)", $params['shipment_time_from'], $params['shipment_time_to']);
}
if (!empty($params['order_period']) && $params['order_period'] != 'A') {
$params['time_from'] = $params['order_time_from'];
$params['time_to'] = $params['order_time_to'];
$params['period'] = $params['order_period'];
list($params['order_time_from'], $params['order_time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:orders.timestamp >= ?i AND ?:orders.timestamp <= ?i)", $params['order_time_from'], $params['order_time_to']);
}
fn_set_hook('get_shipments', $params, $fields_list, $joins, $condition, $group);
$fields_list = implode(', ', $fields_list);
$joins = implode(' ', $joins);
$group = implode(', ', $group);
if (!empty($group)) {
$group = ' GROUP BY ' . $group;
}
$limit = '';
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(DISTINCT(?:shipments.shipment_id)) FROM ?:shipments {$joins} WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$shipments = db_get_array("SELECT {$fields_list} FROM ?:shipments {$joins} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}");
if (isset($params['advanced_info']) && $params['advanced_info'] && !empty($shipments)) {
foreach ($shipments as $id => $shipment) {
$items = db_get_array('SELECT item_id, amount FROM ?:shipment_items WHERE shipment_id = ?i', $shipment['shipment_id']);
if (!empty($items)) {
foreach ($items as $item) {
$shipments[$id]['items'][$item['item_id']] = $item['amount'];
}
}
}
}
return array($shipments, $params, $total);
}
