function fn_get_seo_rules($params = array(), $items_per_page = 0, $lang_code = DESCR_SL)
{
$lang_code = fn_get_corrected_seo_lang_code($lang_code);
$global_total = db_get_fields("SELECT dispatch FROM ?:seo_names WHERE object_id = '0' AND type = 's' GROUP BY dispatch");
$local_total = db_get_fields("SELECT dispatch FROM ?:seo_names WHERE object_id = '0' AND type = 's' AND lang_code = ?s", $lang_code);
if ($diff = array_diff($global_total, $local_total)) {
foreach ($diff as $disp) {
fn_create_seo_name(0, 's', str_replace('.', '-', $disp), 0, $disp, DESCR_SL);
}
}
// Init filter
$params = fn_init_view('seo_rules', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// default page is 1
$condition = '';
if (isset($params['name']) && fn_string_no_empty($params['name'])) {
$condition .= db_quote(" AND name LIKE ?l", "%" . trim($params['name']) . "%");
}
if (isset($params['controller']) && fn_string_no_empty($params['controller'])) {
$condition .= db_quote(" AND dispatch LIKE ?l", "%" . trim($params['controller']) . "%");
}
$limit = '';
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(*) FROM ?:seo_names WHERE object_id = '0' AND type = 's' AND lang_code = ?s ?p", $lang_code, $condition);
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$seo_data = db_get_array("SELECT name, dispatch FROM ?:seo_names WHERE object_id = '0' AND type = 's' AND lang_code = ?s ?p ORDER BY dispatch {$limit}", $lang_code, $condition);
return array($seo_data, $params);
}
function fn_get_discussions($params)
{
// Init filter
$params = fn_init_view('discussion', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// 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");
$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';
$condition = $join = '';
if (isset($params['name']) && fn_string_no_empty($params['name'])) {
$condition .= db_quote(" AND ?:discussion_posts.name LIKE ?l", "%" . trim($params['name']) . "%");
}
if (isset($params['message']) && fn_string_no_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_no_empty($params['ip_address'])) {
$condition .= db_quote(" AND ?:discussion_posts.ip_address = ?s", 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']);
}
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";
$total = db_get_field("SELECT COUNT(*) FROM ?:discussion_posts {$join} WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total, Registry::get('settings.Appearance.admin_elements_per_page'));
$posts = db_get_array("SELECT " . implode(',', $fields) . " FROM ?:discussion_posts {$join} WHERE 1 {$condition} ORDER BY {$sort} {$limit}");
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_companies($params, &$auth, $items_per_page = 0)
{
// Init filter
$_view = 'companies';
$params = fn_init_view($_view, $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// Define fields that should be retrieved
$fields = array("?:companies.company_id", "?:companies.email", "?:companies.company", "?:companies.timestamp", "?:companies.status");
// Define sort fields
$sortings = array('id' => "?:users.user_id", 'company' => "?:companies.company", 'email' => "?:companies.email", 'date' => "?:companies.timestamp", 'status' => "?:companies.status");
$directions = array('asc' => 'asc', 'desc' => 'desc');
$condition = $join = $group = '';
$condition .= fn_get_company_condition('?:companies.company_id');
$group .= " GROUP BY ?:companies.company_id";
/*
* TODO search
if (!empty($params['company'])) {
$condition .= db_quote(" AND ?:users.company LIKE ?l", "%$params[company]%");
}
*/
fn_set_hook('get_companies', $params, $fields, $sortings, $condition, $join);
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'] = 'company';
}
$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';
// Paginate search results
$limit = '';
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(DISTINCT(?:companies.company_id)) FROM ?:companies {$join} WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$companies = db_get_array("SELECT " . implode(', ', $fields) . " FROM ?:companies {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}");
return array($companies, $params);
}
/**
* Get promotions
*
* @param array $params array with search params
* @param int $items_per_page
* @param string $lang_code
* @return array list of promotions in first element, filtered parameters in second
*/
function fn_get_promotions($params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
// Init filter
$params = fn_init_view('promotions', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// default page is 1
$params['get_hidden'] = !isset($params['get_hidden']) ? true : $params['get_hidden'];
// always get hidden promotions
// Define fields that should be retrieved
$fields = array("?:promotions.*", "?:promotion_descriptions.name", "?:promotion_descriptions.detailed_description", "?:promotion_descriptions.short_description");
// Define sort fields
$sortings = array('name' => "?:promotion_descriptions.name", 'priority' => "?:promotions.priority", 'zone' => "?:promotions.zone", 'status' => "?:promotions.status");
$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'] = 'name';
}
$sorting = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']];
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
$condition = $join = $group = '';
$condition .= fn_get_company_condition('?:promotions.company_id');
$statuses = array('A');
if (!empty($params['get_hidden'])) {
$statuses[] = 'H';
}
if (!empty($params['promotion_id'])) {
$condition .= db_quote(' AND ?:promotions.promotion_id IN (?n)', $params['promotion_id']);
}
if (!empty($params['active'])) {
$condition .= db_quote(" AND IF(from_date, from_date <= ?i, 1) AND IF(to_date, to_date >= ?i, 1) AND status IN (?a)", TIME, TIME, $statuses);
}
if (!empty($params['zone'])) {
$condition .= db_quote(" AND ?:promotions.zone = ?s", $params['zone']);
}
if (!empty($params['coupon_code'])) {
$condition .= db_quote(" AND ?:promotions.conditions_hash LIKE ?l", "%{$params['coupon_code']}%");
// FIXME, more smart rules
}
if (!empty($params['coupons'])) {
$condition .= db_quote(" AND ?:promotions.conditions_hash LIKE ?l", "%coupon_code=%");
// FIXME
}
if (!empty($params['auto_coupons'])) {
$condition .= db_quote(" AND ?:promotions.conditions_hash LIKE ?l", "%auto_coupons=%");
}
$join .= db_quote(" LEFT JOIN ?:promotion_descriptions ON ?:promotion_descriptions.promotion_id = ?:promotions.promotion_id AND ?:promotion_descriptions.lang_code = ?s", $lang_code);
fn_set_hook('get_promotions', $params, $fields, $sortings, $condition, $join);
$limit = '';
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(*) FROM ?:promotions {$join} WHERE 1 {$condition} {$group}");
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
if (!empty($params['simple'])) {
return db_get_hash_single_array("SELECT ?:promotions.promotion_id, ?:promotion_descriptions.name FROM ?:promotions {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}", array('promotion_id', 'name'));
} else {
$promotions = db_get_hash_array('SELECT ' . implode(', ', $fields) . " FROM ?:promotions {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}", 'promotion_id');
}
if (!empty($params['expand'])) {
foreach ($promotions as $k => $v) {
$promotions[$k]['conditions'] = !empty($v['conditions']) ? unserialize($v['conditions']) : array();
$promotions[$k]['bonuses'] = !empty($v['bonuses']) ? unserialize($v['bonuses']) : array();
}
}
return array($promotions, $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_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_get_recurring_subscriptions($params, $additional_data = array())
{
// Init filter
$params = fn_init_view('subscriptions', $params);
$params['page'] = empty($params['page']) ? 1 : $params['page'];
$fields = array('?:recurring_subscriptions.*');
// Define sort fields
$sortings = array('subscription_id' => 'subscription_id', 'order_id' => 'order_id', 'status' => 'status', 'customer' => array('lastname', 'firstname'), 'email' => 'email', 'date' => 'timestamp', 'price' => 'price', 'last_paid' => 'last_timestamp', 'duration' => 'duration');
$directions = array('asc' => 'asc', 'desc' => 'desc');
if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) {
$params['sort_order'] = 'desc';
}
if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) {
$params['sort_by'] = 'date';
}
$sorting = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']];
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
$condition = $join = $group = '';
if (isset($params['cname']) && fn_string_no_empty($params['cname'])) {
$arr = fn_explode(' ', $params['cname']);
foreach ($arr as $k => $v) {
if (!fn_string_no_empty($v)) {
unset($arr[$k]);
}
}
if (sizeof($arr) == 2) {
$condition .= db_quote(" AND ?:recurring_subscriptions.firstname LIKE ?l AND ?:recurring_subscriptions.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%");
} else {
$condition .= db_quote(" AND (?:recurring_subscriptions.firstname LIKE ?l OR ?:recurring_subscriptions.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%");
}
}
if (isset($params['email']) && fn_string_no_empty($params['email'])) {
$condition .= db_quote(" AND ?:recurring_subscriptions.email LIKE ?l", "%" . trim($params['email']) . "%");
}
if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) {
$condition .= db_quote(" AND ?:recurring_subscriptions.price >= ?d", fn_convert_price($params['price_from']));
}
if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) {
$condition .= db_quote(" AND ?:recurring_subscriptions.price <= ?d", fn_convert_price($params['price_to']));
}
if (!empty($params['status'])) {
$condition .= db_quote(' AND ?:recurring_subscriptions.status = ?s', $params['status']);
}
if (!empty($params['order_id'])) {
$condition .= db_quote(' AND ?:recurring_subscriptions.order_id IN (?n)', $params['order_id']);
}
if (!empty($params['plan_id'])) {
$condition .= db_quote(' AND ?:recurring_subscriptions.plan_id IN (?n)', $params['plan_id']);
}
if (!empty($params['p_ids'])) {
$arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids'];
$condition .= db_quote(" AND ?:order_details.product_id IN (?a)", $arr);
$join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:recurring_subscriptions.order_id AND ?:order_details.extra LIKE '%recurring_plan_id%'";
$group .= " GROUP BY ?:recurring_subscriptions.subscription_id HAVING COUNT(?:recurring_subscriptions.subscription_id) >= " . count($arr);
}
if (!empty($params['period_type']) && !empty($params['period']) && $params['period'] != 'A') {
if ($params['period_type'] == 'D') {
$timestamp = 'timestamp';
} elseif ($params['period_type'] == 'L') {
$timestamp = 'last_timestamp';
} else {
$timestamp = 'end_timestamp';
}
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:recurring_subscriptions.{$timestamp} >= ?i AND ?:recurring_subscriptions.{$timestamp} <= ?i)", $params['time_from'], $params['time_to']);
}
if (!empty($additional_data['plan_options'])) {
$join .= " LEFT JOIN ?:recurring_plans ON ?:recurring_plans.plan_id = ?:recurring_subscriptions.plan_id";
$fields[] = '?:recurring_plans.allow_unsubscribe';
$fields[] = '?:recurring_plans.allow_change_duration';
}
if (!empty($additional_data['user_id'])) {
$condition .= db_quote(" AND ?:recurring_subscriptions.user_id = ?i AND ?:recurring_subscriptions.status = 'A'", $additional_data['user_id']);
} elseif (!empty($additional_data['order_ids'])) {
$condition .= db_quote(" AND FIND_IN_SET(?:recurring_subscriptions.order_id, ?s) AND ?:recurring_subscriptions.status = 'A'", implode(',', $additional_data['order_ids']));
}
$total = db_get_field("SELECT DISTINCT(COUNT(?:recurring_subscriptions.subscription_id)) FROM ?:recurring_subscriptions {$join} WHERE 1 {$condition} {$group}");
$items_per_page = Registry::get('settings.Appearance.admin_elements_per_page');
$limit = fn_paginate($params['page'], $total, $items_per_page);
$subscriptions = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:recurring_subscriptions {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}", 'subscription_id');
return array($subscriptions, $params);
}
function fn_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_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);
}
/**
* Function returns a array of product options using some params
*
* @param array $params - array of params
* @param int $items_per_page - items per page
* @param $lang_code - language code
* @return array ($product_options, $params, $product_options_count)
*/
function fn_get_product_global_options($params = array(), $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
// Init filter
$params = fn_init_view('product_global_options', $params);
$default_params = array('product_id' => 0, 'page' => 1);
$params = array_merge($default_params, $params);
$fields = array('?:product_options.*', '?:product_options_descriptions.*');
$condition = $join = '';
$condition .= fn_get_company_condition('company_id', true, '', true);
$join .= db_quote("LEFT JOIN ?:product_options_descriptions ON ?:product_options_descriptions.option_id = ?:product_options.option_id AND ?:product_options_descriptions.lang_code = ?s ", $lang_code);
$order = 'ORDER BY position';
$params['product_id'] = !empty($params['product_id']) ? $params['product_id'] : 0;
$condition .= db_quote(" AND ?:product_options.product_id = ?i", $params['product_id']);
$limit = '';
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(*) FROM ?:product_options {$join} WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$data = db_get_array("SELECT " . implode(', ', $fields) . " FROM ?:product_options {$join} WHERE 1 {$condition} {$order} {$limit} ");
return array($data, $params, $total);
}
function fn_get_products($params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
if (isset($params['doLinks']) && $params['doLinks'] == 1) {
$neighbours = fn_get_neighbours($_REQUEST['product_id']);
$params['pid'] = $neighbours;
$params['limit'] = 4;
}
if (isset($params['doSales']) && $params['doSales'] == 1) {
$params['pid'] = fn_get_sales_products();
}
if (isset($params['doSameLine']) && $params['doSameLine'] == 1) {
$sameLineProducts = fn_get_same_line_products($_REQUEST['product_id']);
if ($sameLineProducts) {
$params['pid'] = $sameLineProducts;
} else {
return false;
}
}
// Init filter
$relevanceField = '';
$relevanceOrder = '';
$params = fn_init_view('products', $params);
// Set default values to input params
$default_params = array('pname' => '', 'pshort' => '', 'pfull' => '', 'pkeywords' => '', 'feature' => array(), 'type' => 'simple', 'page' => 1, 'action' => '', 'variants' => array(), 'ranges' => array(), 'custom_range' => array(), 'field_range' => array(), 'features_hash' => '', 'limit' => 0, 'bid' => 0, 'match' => '', 'search_tracking_flags' => array());
$params = array_merge($default_params, $params);
if ((empty($params['pname']) || $params['pname'] != 'Y') && (empty($params['pshort']) || $params['pshort'] != 'Y') && (empty($params['pfull']) || $params['pfull'] != 'Y') && (empty($params['pkeywords']) || $params['pkeywords'] != 'Y') && (empty($params['feature']) || $params['feature'] != 'Y') && !empty($params['q'])) {
$params['pname'] = 'Y';
}
$auth =& $_SESSION['auth'];
// Define fields that should be retrieved
$fields = array('products.product_id', 'products.company_id', 'companies.company as company_name', 'descr1.product as product', 'products.tracking', 'products.feature_comparison', 'products.zero_price_action', 'products.product_type', 'products.tax_ids', 'products.weight', "GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids", 'min_qty', 'max_qty', 'products.qty_step', 'products.list_qty_count', 'products.is_edp', 'avail_since', 'buy_in_advance', 'products.options_type', 'products.exceptions_type', 'companies.company as company_name', 'products.timestamp');
// Define sort fields
$sortings = array('code' => 'products.product_code', 'status' => 'products.status', 'product' => 'descr1.product', 'position' => 'products_categories.position', 'price' => 'prices.price', 'list_price' => 'products.list_price', 'weight' => 'products.weight', 'amount' => 'products.amount', 'timestamp' => 'products.timestamp', 'popularity' => 'popularity.total', 'company' => "company_name");
$directions = array('asc' => 'asc', 'desc' => 'desc');
$join = $condition = $inventory_condition = '';
$condition .= fn_get_company_condition('products.company_id');
if (isset($params['company_id']) && $params['company_id'] != '') {
$params['company_id'] = intval($params['company_id']);
$condition .= db_quote(' AND products.company_id = ?i ', $params['company_id']);
}
if (AREA == 'C') {
if (fn_check_suppliers_functionality()) {
// if MVE or suppliers enabled
$condition .= " AND (companies.status = 'A' OR products.company_id = 0) ";
} else {
// if suppliers disabled
$condition .= fn_get_company_condition('products.company_id', true, '0', false, true);
}
}
$join .= db_quote(" LEFT JOIN ?:companies companies ON companies.company_id = products.company_id ");
$join .= db_quote(" LEFT JOIN ?:product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ?s ", $lang_code);
// Search string condition for SQL query
if (isset($params['q']) && fn_string_no_empty($params['q'])) {
$params['q'] = trim($params['q']);
if ($params['match'] == 'any') {
$pieces = fn_explode(' ', $params['q']);
$search_type = ' OR ';
} elseif ($params['match'] == 'all') {
$pieces = fn_explode(' ', $params['q']);
$search_type = ' AND ';
} else {
$pieces = array($params['q']);
$search_type = '';
}
$_condition = array();
foreach ($pieces as $piece) {
if (strlen($piece) == 0) {
continue;
}
$pieceTrimed = str_replace(array(' ', '-'), '', $piece);
$tmp = db_quote("(descr1.search_words LIKE ?l)", "%{$piece}%");
// check search words
if ($params['pname'] == 'Y') {
$tmp .= db_quote(" OR MATCH (product) AGAINST ('" . mysql_real_escape_string($piece) . "')");
//$tmp .= db_quote(" OR MATCH (product) AGAINST ('$piece')");
$tmp .= db_quote(" OR REPLACE( REPLACE(upper(descr1.product), ' ', '' ) , '-', '' ) LIKE ?l", "%{$pieceTrimed}%");
$relevanceField = " ,MATCH(product) AGAINST ('" . mysql_real_escape_string($piece) . "' IN BOOLEAN MODE) AS relevance";
$relevanceOrder = "relevance DESC,";
}
if ($params['pshort'] == 'Y') {
//$tmp .= db_quote(" OR descr1.short_description LIKE ?l", "%$piece%");
}
if ($params['pfull'] == 'Y') {
//$tmp .= db_quote(" OR descr1.full_description LIKE ?l", "%$piece%");
}
if ($params['pkeywords'] == 'Y') {
// $tmp .= db_quote(" OR (descr1.meta_keywords LIKE ?l OR descr1.meta_description LIKE ?l)", "%$piece%", "%$piece%");
}
if (!empty($params['feature']) && $params['action'] != 'feature_search') {
// $tmp .= db_quote(" OR ?:product_features_values.value LIKE ?l", "%$piece%");
}
$tmp .= db_quote(" OR products.product_code LIKE ?l", "%{$piece}%");
fn_set_hook('additional_fields_in_search', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $tmp);
$_condition[] = '(' . $tmp . ')';
}
$_cond = implode($search_type, $_condition);
if (!empty($_condition)) {
$condition .= ' AND (' . $_cond . ') ';
}
if (!empty($params['feature']) && $params['action'] != 'feature_search') {
$join .= " LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id";
$condition .= db_quote(" AND (?:product_features_values.feature_id IN (?n) OR ?:product_features_values.feature_id IS NULL)", array_values($params['feature']));
}
unset($_condition);
}
//
// [Advanced and feature filters]
//
if (!empty($params['apply_limit']) && $params['apply_limit']) {
$pids = array();
foreach ($params['pid'] as $pid) {
if ($pid != $params['exclude_pid']) {
if (count($pids) == $params['limit']) {
break;
} else {
$pids[] = $pid;
}
}
}
$params['pid'] = $pids;
}
if (!empty($params['features_hash']) || !fn_is_empty($params['variants'])) {
$join .= db_quote(" LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id AND ?:product_features_values.lang_code = ?s", CART_LANGUAGE);
}
if (!empty($params['variants'])) {
$params['features_hash'] .= implode('.', $params['variants']);
}
$advanced_variant_ids = $simple_variant_ids = $ranges_ids = $fields_ids = array();
if (!empty($params['features_hash'])) {
if (!empty($params['advanced_filter'])) {
list($av_ids, $ranges_ids, $fields_ids) = fn_parse_features_hash($params['features_hash']);
$advanced_variant_ids = db_get_hash_multi_array("SELECT feature_id, variant_id FROM ?:product_feature_variants WHERE variant_id IN (?n)", array('feature_id', 'variant_id'), $av_ids);
} else {
list($simple_variant_ids, $ranges_ids, $fields_ids) = fn_parse_features_hash($params['features_hash']);
}
}
if (!empty($params['multiple_variants']) && !empty($params['advanced_filter'])) {
$simple_variant_ids = $params['multiple_variants'];
}
if (!empty($advanced_variant_ids)) {
$join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS advanced_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_advanced ON pfv_advanced.product_id = products.product_id", CART_LANGUAGE);
$where_and_conditions = array();
foreach ($advanced_variant_ids as $k => $variant_ids) {
$where_or_conditions = array();
foreach ($variant_ids as $variant_id => $v) {
$where_or_conditions[] = db_quote(" FIND_IN_SET('?i', advanced_variants)", $variant_id);
}
$where_and_conditions[] = "(" . implode(' OR ', $where_or_conditions) . ")";
}
$condition .= ' AND ' . implode(' AND ', $where_and_conditions);
}
if (!empty($simple_variant_ids)) {
$join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS simple_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_simple ON pfv_simple.product_id = products.product_id", CART_LANGUAGE);
$where_conditions = array();
foreach ($simple_variant_ids as $k => $variant_id) {
$where_conditions[] = db_quote(" FIND_IN_SET('?i', simple_variants)", $variant_id);
}
$condition .= ' AND ' . implode(' AND ', $where_conditions);
}
//
// Ranges from text inputs
//
// Feature ranges
if (!empty($params['custom_range'])) {
foreach ($params['custom_range'] as $k => $v) {
$k = intval($k);
if (fn_string_no_empty($v['from']) || fn_string_no_empty($v['to'])) {
if (!empty($v['type'])) {
if ($v['type'] == 'D') {
$v['from'] = fn_parse_date($v['from']);
$v['to'] = fn_parse_date($v['to']);
}
}
$join .= db_quote(" LEFT JOIN ?:product_features_values as custom_range_{$k} ON custom_range_{$k}.product_id = products.product_id AND custom_range_{$k}.lang_code = ?s", CART_LANGUAGE);
if (fn_string_no_empty($v['from']) && fn_string_no_empty($v['to'])) {
$condition .= db_quote(" AND (custom_range_{$k}.value_int >= ?i AND custom_range_{$k}.value_int <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i) ", $v['from'], $v['to'], $k);
} else {
$condition .= " AND custom_range_{$k}.value_int" . (fn_string_no_empty($v['from']) ? db_quote(' >= ?i', $v['from']) : db_quote(" <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i ", $v['to'], $k));
}
}
}
}
// Product field ranges
$filter_fields = fn_get_product_filter_fields();
if (!empty($params['field_range'])) {
foreach ($params['field_range'] as $field_type => $v) {
$structure = $filter_fields[$field_type];
if (!empty($structure) && (!empty($v['from']) || !empty($v['to']))) {
$params["{$structure['db_field']}_from"] = trim($v['from']);
$params["{$structure['db_field']}_to"] = trim($v['to']);
}
}
}
// Ranges from database
if (!empty($ranges_ids)) {
$range_conditions = db_get_array("SELECT `from`, `to`, feature_id FROM ?:product_filter_ranges WHERE range_id IN (?n)", $ranges_ids);
foreach ($range_conditions as $k => $range_condition) {
$join .= db_quote(" LEFT JOIN ?:product_features_values as var_val_{$k} ON var_val_{$k}.product_id = products.product_id AND var_val_{$k}.lang_code = ?s", CART_LANGUAGE);
$condition .= db_quote(" AND (var_val_{$k}.value_int >= ?i AND var_val_{$k}.value_int <= ?i AND var_val_{$k}.value = '' AND var_val_{$k}.feature_id = ?i) ", $range_condition['from'], $range_condition['to'], $range_condition['feature_id']);
}
}
// Field ranges
$fields_ids = empty($params['fields_ids']) ? $fields_ids : $params['fields'];
if (!empty($fields_ids)) {
foreach ($fields_ids as $rid => $field_type) {
$structure = $filter_fields[$field_type];
if ($structure['condition_type'] == 'D') {
$range_condition = db_get_row("SELECT `from`, `to`, range_id FROM ?:product_filter_ranges WHERE range_id = ?i", $rid);
if (!empty($range_condition)) {
$params["{$structure['db_field']}_from"] = $range_condition['from'];
$params["{$structure['db_field']}_to"] = $range_condition['to'];
}
} elseif ($structure['condition_type'] == 'F') {
$params[$structure['db_field']] = $rid;
} elseif ($structure['condition_type'] == 'C') {
$params[$structure['db_field']] = $rid == 1 ? 'Y' : 'N';
}
}
}
// Checkbox features
if (!empty($params['ch_filters']) && !fn_is_empty($params['ch_filters'])) {
foreach ($params['ch_filters'] as $k => $v) {
// Product field filter
if (is_string($k) == true && !empty($v) && ($structure = $filter_fields[$k])) {
$condition .= db_quote(" AND {$structure['table']}.{$structure['db_field']} IN (?a)", $v == 'A' ? array('Y', 'N') : $v);
// Feature filter
} elseif (!empty($v)) {
$fid = intval($k);
$join .= db_quote(" LEFT JOIN ?:product_features_values as ch_features_{$fid} ON ch_features_{$fid}.product_id = products.product_id AND ch_features_{$fid}.lang_code = ?s", CART_LANGUAGE);
$condition .= db_quote(" AND ch_features_{$fid}.feature_id = ?i AND ch_features_{$fid}.value IN (?a)", $fid, $v == 'A' ? array('Y', 'N') : $v);
}
}
}
// Text features
if (!empty($params['tx_features'])) {
foreach ($params['tx_features'] as $k => $v) {
if (fn_string_no_empty($v)) {
$fid = intval($k);
$join .= " LEFT JOIN ?:product_features_values as tx_features_{$fid} ON tx_features_{$fid}.product_id = products.product_id";
$condition .= db_quote(" AND tx_features_{$fid}.value LIKE ?l AND tx_features_{$fid}.lang_code = ?s", "%" . trim($v) . "%", CART_LANGUAGE);
}
}
}
//
// [/Advanced filters]
//
$feature_search_condition = '';
if (!empty($params['feature'])) {
// Extended search by product fields
$_cond = array();
$total_hits = 0;
foreach ($params['feature'] as $f_id) {
if (!empty($f_val)) {
$total_hits++;
$_cond[] = db_quote("(?:product_features_values.feature_id = ?i)", $f_id);
}
}
if (!empty($_cond)) {
$cache_feature_search = db_get_fields("SELECT product_id, COUNT(product_id) as cnt FROM ?:product_features_values WHERE (" . implode(' OR ', $_cond) . ") GROUP BY product_id HAVING cnt = {$total_hits}");
$feature_search_condition .= db_quote(" AND products_categories.product_id IN (?n)", $cache_feature_search);
}
}
// Category search condition for SQL query
if (!empty($params['cid'])) {
$cids = is_array($params['cid']) ? $params['cid'] : array($params['cid']);
if (!empty($params['subcats']) && $params['subcats'] == 'Y') {
$_ids = db_get_fields("SELECT a.category_id FROM ?:categories as a LEFT JOIN ?:categories as b ON b.category_id IN (?n) WHERE a.id_path LIKE CONCAT(b.id_path, '/%')", $cids);
$cids = fn_array_merge($cids, $_ids, false);
}
$condition .= db_quote(" AND ?:categories.category_id IN (?n)", $cids);
}
// If we need to get the products by IDs and no IDs passed, don't search anything
if (!empty($params['force_get_by_ids']) && empty($params['pid']) && empty($params['product_id'])) {
return array(array(), array(), 0);
}
// Product ID search condition for SQL query
if (!empty($params['pid'])) {
if (isset($params['doLinks']) && $params['doLinks'] == 1) {
$condition .= db_quote(' AND products.product_id IN (' . $params['pid'] . ')', $params['pid']);
} else {
$condition .= db_quote(' AND products.product_id IN (?n)', $params['pid']);
}
}
// Exclude products from search results
if (!empty($params['exclude_pid'])) {
$condition .= db_quote(' AND products.product_id NOT IN (?n)', $params['exclude_pid']);
}
// Search by feature comparison flag
if (!empty($params['feature_comparison'])) {
$condition .= db_quote(' AND products.feature_comparison = ?s', $params['feature_comparison']);
}
// Search products by localization
$condition .= fn_get_localizations_condition('products.localization', true);
$condition .= fn_get_localizations_condition('?:categories.localization', true);
if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) {
$condition .= db_quote(' AND prices.price >= ?d', fn_convert_price(trim($params['price_from'])));
}
if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) {
$condition .= db_quote(' AND prices.price <= ?d', fn_convert_price(trim($params['price_to'])));
}
if (isset($params['weight_from']) && fn_is_numeric($params['weight_from'])) {
$condition .= db_quote(' AND products.weight >= ?d', fn_convert_weight(trim($params['weight_from'])));
}
if (isset($params['weight_to']) && fn_is_numeric($params['weight_to'])) {
$condition .= db_quote(' AND products.weight <= ?d', fn_convert_weight(trim($params['weight_to'])));
}
// search specific inventory status
if (!empty($params['search_tracking_flags'])) {
$condition .= db_quote(' AND products.tracking IN(?a)', $params['search_tracking_flags']);
}
if (isset($params['amount_from']) && fn_is_numeric($params['amount_from'])) {
$condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount >= ?i, products.amount >= ?i)", $params['amount_from'], $params['amount_from']);
$inventory_condition .= db_quote(' AND inventory.amount >= ?i', $params['amount_from']);
}
if (isset($params['amount_to']) && fn_is_numeric($params['amount_to'])) {
$condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount <= ?i, products.amount <= ?i)", $params['amount_to'], $params['amount_to']);
$inventory_condition .= db_quote(' AND inventory.amount <= ?i', $params['amount_to']);
}
if (Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') {
// FIXME? Registry in model
$condition .= " AND IF(products.tracking = 'O', inventory.amount > 0, products.amount > 0)";
}
if (!empty($params['status'])) {
$condition .= db_quote(' AND products.status IN (?a)', $params['status']);
}
if (!empty($params['shipping_freight_from'])) {
$condition .= db_quote(' AND products.shipping_freight >= ?d', $params['shipping_freight_from']);
}
if (!empty($params['shipping_freight_to'])) {
$condition .= db_quote(' AND products.shipping_freight <= ?d', $params['shipping_freight_to']);
}
if (!empty($params['free_shipping'])) {
$condition .= db_quote(' AND products.free_shipping = ?s', $params['free_shipping']);
}
if (!empty($params['downloadable'])) {
$condition .= db_quote(' AND products.is_edp = ?s', $params['downloadable']);
}
if (!empty($params['b_id'])) {
$join .= " LEFT JOIN ?:block_links ON ?:block_links.object_id = products.product_id AND ?:block_links.location = 'products'";
$condition .= db_quote(' AND ?:block_links.block_id = ?i', $params['b_id']);
}
if (isset($params['pcode']) && fn_string_no_empty($params['pcode'])) {
$pcode = trim($params['pcode']);
$fields[] = 'inventory.combination';
$condition .= db_quote(" AND (inventory.product_code LIKE ?l OR products.product_code LIKE ?l)", "%{$pcode}%", "%{$pcode}%");
$inventory_condition .= db_quote(" AND inventory.product_code LIKE ?l", "%{$pcode}%");
}
if (isset($params['amount_to']) && fn_is_numeric($params['amount_to']) || isset($params['amount_from']) && fn_is_numeric($params['amount_from']) || !empty($params['pcode']) || Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') {
$join .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = products.product_id {$inventory_condition}";
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (products.timestamp >= ?i AND products.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
if (!empty($params['item_ids'])) {
$condition .= db_quote(" AND products.product_id IN (?n)", explode(',', $params['item_ids']));
}
if (isset($params['popularity_from']) && fn_is_numeric($params['popularity_from'])) {
$condition .= db_quote(' AND popularity.total >= ?i', $params['popularity_from']);
}
if (isset($params['popularity_to']) && fn_is_numeric($params['popularity_to'])) {
$condition .= db_quote(' AND popularity.total <= ?i', $params['popularity_to']);
}
// Extended search mode condition for SQL query
if ($params['type'] == 'extended') {
array_push($fields, 'products.product_code', 'products.amount', 'MIN(prices.price) as price', 'products.status', 'products.list_price', 'products.temp_price', 'descr1.short_description', "IF(descr1.short_description = '', descr1.full_description, '') as full_description");
if (!empty($params['cid'])) {
$fields[] = 'products_categories.position';
}
}
$price_usergroup_cond = db_quote(' AND prices.usergroup_id IN (?n)', AREA == 'A' ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids']));
$condition .= $price_usergroup_cond;
$price_usergroup_cond_2 = str_replace('prices', 'prices_2', $price_usergroup_cond);
$join .= " LEFT JOIN ?:product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN ?:product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price " . $price_usergroup_cond_2;
$condition .= ' AND prices_2.price IS NULL';
// Show enabled products/categories
$_p_statuses = array('A');
$_c_statuses = array('A', 'H');
$avail_cond = AREA == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], '?:categories.usergroup_ids', true) . ')' : '';
$avail_cond .= AREA == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], 'products.usergroup_ids', true) . ')' : '';
$avail_cond .= AREA == 'C' ? db_quote(" AND ?:categories.status IN (?a) AND products.status IN (?a)", $_c_statuses, $_p_statuses) : '';
$join .= " INNER JOIN ?:products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN ?:categories ON ?:categories.category_id = products_categories.category_id {$avail_cond} {$feature_search_condition}";
$limit = '';
$group_by = 'products.product_id';
fn_set_hook('get_products', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $lang_code);
if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) {
$params['sort_by'] = Registry::get('settings.Appearance.default_products_sorting');
if (empty($sortings[$params['sort_by']])) {
$_products_sortings = fn_get_products_sorting(false);
$params['sort_by'] = key($_products_sortings);
}
}
if ($params['type'] != 'extended' && $params['sort_by'] == 'price') {
$params['sort_by'] = 'product';
}
$default_sorting = fn_get_products_sorting(false);
if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) {
if (!empty($default_sorting[$params['sort_by']]['default_order'])) {
$params['sort_order'] = $default_sorting[$params['sort_by']]['default_order'];
} else {
$params['sort_order'] = 'asc';
}
}
if (AREA == 'A' || $params['sort_by'] == 'popularity') {
$fields[] = 'popularity.total as popularity';
$join .= db_quote(" LEFT JOIN ?:product_popularity as popularity ON popularity.product_id = products.product_id");
}
$sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']];
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
// Used for View cascading
if (!empty($params['get_query'])) {
return "SELECT products.product_id FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY products.product_id";
}
// Used for Extended search
if (!empty($params['get_conditions'])) {
return array($fields, $join, $condition);
}
if (!empty($params['limit'])) {
$limit = db_quote(" LIMIT 0, ?i", $params['limit']);
}
$total = 0;
if (!empty($items_per_page)) {
if (!empty($params['limit']) && $total > $params['limit']) {
$total = $params['limit'];
}
$limit = fn_paginate($params['page'], 0, $items_per_page, true);
}
if (isset($_GET['no_pagination'])) {
ini_set('memory_limit', '128M');
$limit = db_quote("");
}
if (isset($params['doLinks']) && $params['doLinks'] == 1) {
$sorting = "products.amount DESC, descr1.product ASC";
}
$sqlquery = 'SELECT SQL_CALC_FOUND_ROWS ' . implode(', ', $fields) . ",products.amount {$relevanceField} FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY {$group_by} ORDER BY {$relevanceOrder} `products`.`amount` DESC, {$sorting} {$limit}";
$products = db_get_array($sqlquery);
if (!empty($items_per_page)) {
$total = db_get_found_rows();
fn_paginate($params['page'], $total, $items_per_page);
} else {
$total = count($products);
}
// Post processing
foreach ($products as $k => $v) {
$products[$k]['category_ids'] = fn_convert_categories($v['category_ids']);
}
if (!empty($params['item_ids'])) {
$products = fn_sort_by_ids($products, explode(',', $params['item_ids']));
}
if (!empty($params['pid']) && !empty($params['apply_limit']) && $params['apply_limit']) {
$products = fn_sort_by_ids($products, $params['pid']);
}
fn_set_hook('get_products_post', $products);
return array($products, $params, $total);
}
function fn_stat_filter_data($params)
{
$params = fn_init_view('statistics', $params);
$join = array();
$where = '';
if (!empty($params)) {
$sql_where = array();
$pattern = "/AND|OR/";
foreach ($params as $param => $value) {
if (empty($value)) {
continue;
}
$conditions = preg_split($pattern, $value);
if (empty($conditions)) {
$conditions = array($value);
}
preg_match_all($pattern, $value, $conjunctions);
$_condition = array();
foreach ($conditions as $k => $v) {
$_condition[] = array('condition' => trim($v), 'conjunction' => empty($k) || empty($conjunctions[0][$k - 1]) ? '' : trim($conjunctions[0][$k - 1]));
}
$__where = '';
foreach ($_condition as $c) {
if ($param == 'ip_address') {
$_long_ip = sprintf('%u', ip2long($c['condition']));
$_where = db_quote("?:stat_sessions.proxy_ip = ?i OR ?:stat_sessions.host_ip = ?i", $_long_ip, $_long_ip);
} elseif ($param == 'language') {
$_where = db_quote("(?:stat_sessions.client_language LIKE ?l OR ?:stat_languages.language LIKE ?l)", "{$c['condition']}%", "{$c['condition']}%");
$join['?:stat_languages'] = "?:stat_sessions.client_language = ?:stat_languages.lang_code";
} elseif ($param == 'search_phrase') {
$_where = db_quote("?:stat_search_phrases.phrase = ?s", $c['condition']);
$join['?:stat_search_phrases'] = "?:stat_sessions.phrase_id = ?:stat_search_phrases.phrase_id";
} elseif ($param == 'url') {
$_where = db_quote("?:stat_requests.url LIKE ?l", "%{$c['condition']}%");
$join['?:stat_requests'] = "?:stat_sessions.sess_id = ?:stat_requests.sess_id";
} elseif ($param == 'page_title') {
$_where = db_quote("?:stat_requests.title LIKE ?l", "%{$c['condition']}%");
$join['?:stat_requests'] = "?:stat_sessions.sess_id = ?:stat_requests.sess_id";
} elseif ($param == 'user_agent') {
$_where = db_quote("?:stat_sessions.user_agent LIKE ?l", "%{$c['condition']}%");
} elseif ($param == 'browser_name') {
$_where = db_quote("?:stat_browsers.browser LIKE ?l", "%{$c['condition']}%");
$join['?:stat_browsers'] = "?:stat_sessions.browser_id = ?:stat_browsers.browser_id";
} elseif ($param == 'browser_version') {
$_where = db_quote("?:stat_browsers.version = ?s", $c['condition']);
$join['?:stat_browsers'] = "?:stat_sessions.browser_id = ?:stat_browsers.browser_id";
} elseif ($param == 'operating_system') {
$_where = db_quote("UPPER(?:stat_sessions.os) LIKE UPPER(?s)", "%{$c['condition']}%");
} elseif ($param == 'country') {
$_where = db_quote("(?:stat_ips.country_code = ?s OR ?:country_descriptions.country LIKE ?l)", $c['condition'], $c['condition']);
$join['?:stat_ips'] = "?:stat_sessions.ip_id = ?:stat_ips.ip_id";
$join['?:country_descriptions'] = db_quote("?:stat_ips.country_code = ?:country_descriptions.code AND ?:country_descriptions.lang_code = ?s", CART_LANGUAGE);
} elseif ($param == 'referrer_url') {
$_where = db_quote("?:stat_sessions.referrer LIKE ?l", "%{$c['condition']}%");
} else {
$_where = '';
}
if (!empty($_where)) {
$__where .= "{$c['conjunction']} {$_where} ";
}
}
if (!empty($__where)) {
$sql_where[] = $__where;
}
}
if (!empty($sql_where)) {
$where .= (empty($where) ? '' : ' AND ') . (!empty($params['exclude_condition']) ? 'NOT' : '') . '(' . implode(') AND ' . (!empty($params['exclude_condition']) ? 'NOT ' : ' ') . '(', $sql_where) . ')';
} else {
$where .= "1";
}
}
if (empty($params['period'])) {
$params['period'] = 'A';
}
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
if (!empty($params['time_from']) || !empty($params['time_to'])) {
$where = (empty($where) ? '' : $where . ' AND ') . fn_get_sql_where_time('?:stat_sessions', $params['time_from'], $params['time_to']);
if (!empty($join['?:stat_requests'])) {
$where .= ' AND ' . fn_get_sql_where_time('?:stat_requests', $params['time_from'], $params['time_to']);
}
}
if (empty($params['limit'])) {
$params['limit'] = STATS_LIMIT;
}
return array($where, $join, $params);
}
function fn_get_carts($params)
{
// Init filter
$params = fn_init_view('carts', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// Define fields that should be retrieved
$fields = array('?:user_session_products.user_id', '?:users.firstname', '?:users.lastname', '?:user_session_products.timestamp AS date');
// Define sort fields
$sortings = array('customer' => "CONCAT(?:users.lastname, ?:users.firstname)", 'date' => "?:user_session_products.timestamp");
$directions = array('asc' => 'asc', 'desc' => 'desc');
if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) {
$params['sort_order'] = 'asc';
}
if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) {
$params['sort_by'] = 'customer';
}
$sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']];
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
$condition = $join = '';
$group = " GROUP BY ?:user_session_products.user_id";
if (isset($params['cname']) && fn_string_no_empty($params['cname'])) {
$arr = fn_explode(' ', $params['cname']);
foreach ($arr as $k => $v) {
if (!fn_string_no_empty($v)) {
unset($arr[$k]);
}
}
if (sizeof($arr) == 2) {
$condition .= db_quote(" AND ?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%");
} else {
$condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%");
}
}
if (isset($params['email']) && fn_string_no_empty($params['email'])) {
$condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%");
}
if (!empty($params['user_id'])) {
$condition .= db_quote(" AND ?:user_session_products.user_id = ?i", $params['user_id']);
}
if (!empty($params['online_only'])) {
$join .= ' LEFT JOIN ?:sessions ON ?:sessions.session_id = ?:user_session_products.session_id';
$condition .= db_quote(" AND ?:sessions.expiry > ?i", TIME + SESSION_ALIVE_TIME - 300);
}
if (!empty($params['with_info_only'])) {
$condition .= db_quote(" AND ?:users.email != ''");
}
if (!empty($params['users_type'])) {
if ($params['users_type'] == 'R') {
$condition .= db_quote(" AND !ISNULL(?:users.user_id)");
} elseif ($params['users_type'] == 'G') {
$condition .= db_quote(" AND ISNULL(?:users.user_id)");
}
}
if (!empty($params['total_from']) || !empty($params['total_to'])) {
$having = '';
if (fn_is_numeric($params['total_from'])) {
$having .= db_quote(" AND SUM(price * amount) >= ?d", $params['total_from']);
}
if (fn_is_numeric($params['total_to'])) {
$having .= db_quote(" AND SUM(price * amount) <= ?d", $params['total_to']);
}
if (!empty($having)) {
$users4total = db_get_fields("SELECT user_id FROM ?:user_session_products GROUP BY user_id HAVING 1 {$having}");
if (!empty($users4total)) {
$condition .= db_quote(" AND (?:user_session_products.user_id IN (?n))", $users4total);
} else {
$condition .= " AND (?:user_session_products.user_id = 'no')";
}
}
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:user_session_products.timestamp >= ?i AND ?:user_session_products.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
$_condition = array();
if (!empty($params['product_type_c'])) {
$_condition[] = "?:user_session_products.type = 'C'";
}
if (!empty($params['product_type_w']) && $params['product_type_w'] == 'Y') {
$_condition[] = "?:user_session_products.type = 'W'";
}
if (!empty($_condition)) {
$condition .= " AND (" . implode(" OR ", $_condition) . ")";
}
if (!empty($params['p_ids']) || !empty($params['product_view_id'])) {
$arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids'];
if (empty($params['product_view_id'])) {
$condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", $arr);
} else {
$condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true))));
}
$group .= " HAVING COUNT(?:user_session_products.user_id) >= " . count($arr);
}
$join .= " LEFT JOIN ?:users ON ?:user_session_products.user_id = ?:users.user_id";
// checking types for retrieving from the database
$type_restrictions = array('C');
fn_set_hook('get_carts', $type_restrictions);
if (!empty($type_restrictions) && is_array($type_restrictions)) {
$condition .= " AND ?:user_session_products.type IN ('" . implode("', '", $type_restrictions) . "')";
}
$total = db_get_field("SELECT COUNT(DISTINCT ?:user_session_products.user_id) FROM ?:user_session_products {$join} WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total);
$carts_list = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:user_session_products {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}", 'user_id');
$_SESSION['abandoned_carts'] = db_get_fields("SELECT ?:user_session_products.user_id FROM ?:user_session_products {$join} WHERE 1 {$condition} GROUP BY user_id");
return array($carts_list, $params);
}
function fn_get_pages($params = array(), $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
// Init filter
$params = fn_init_view('pages', $params);
$default_params = array('page_id' => 0, 'page' => 1, 'visible' => false, 'get_tree' => '', 'items_per_page' => 0, 'pdescr' => '', 'subpages' => '');
$params = array_merge($default_params, $params);
if (empty($params['pname']) && empty($params['pdescr']) && empty($params['subpages'])) {
$params['pname'] = 'Y';
}
$fields = array('?:pages.*', '?: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'));
$directions = array('asc' => 'asc', 'desc' => 'desc');
$auth =& $_SESSION['auth'];
$condition = '1';
$join = $limit = $group_by = '';
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();
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 (!empty($params['page_type'])) {
$condition .= db_quote(" AND ?:pages.page_type = ?s", $params['page_type']);
}
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 (!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['visible'])) {
// for pages tree: show visible branch only
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);
}
}
$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['b_id'])) {
$join .= " LEFT JOIN ?:block_links ON ?:block_links.object_id = ?:pages.page_id AND ?:block_links.location = 'pages'";
$condition .= db_quote(' AND ?:block_links.block_id = ?i', $params['b_id']);
}
if (!empty($params['limit'])) {
$limit = db_quote(" LIMIT 0, ?i", $params['limit']);
}
fn_set_hook('get_pages', $params, $join, $condition, $fields, $group_by, $sortings);
if (!empty($params['get_tree'])) {
$params['sort_by'] = 'multi_level';
}
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'] = 'position';
}
$sorting = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']];
if (!empty($group_by)) {
$group_by = ' GROUP BY ' . $group_by;
}
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
// Get search conditions
if (!empty($params['get_conditions'])) {
return array($fields, $join, $condition);
}
$total = 0;
if (!empty($items_per_page) && !empty($params['paginate'])) {
$total = db_get_field("SELECT COUNT(DISTINCT(?:pages.page_id)) FROM ?:pages ?p WHERE ?p ?p ORDER BY ?p", $join, $condition, $group_by, $sorting);
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$pages = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:pages ?p WHERE ?p ?p ORDER BY ?p ?p", 'page_id', $join, $condition, $group_by, $sorting, $limit);
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' => 0, 'page' => $params['add_root']));
}
fn_set_hook('post_get_pages', $pages, $params, $lang_code);
return array($pages, $params);
}
function fn_get_gift_certificates($params)
{
// Init filter
$params = fn_init_view('gift_certs', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// 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');
// 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");
$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';
$condition = $join = '';
if (isset($params['sender']) && fn_string_no_empty($params['sender'])) {
$condition .= db_quote(" AND ?:gift_certificates.sender LIKE ?l", "%" . trim($params['sender']) . "%");
}
if (isset($params['recipient']) && fn_string_no_empty($params['recipient'])) {
$condition .= db_quote(" AND ?:gift_certificates.recipient LIKE ?l", "%" . trim($params['recipient']) . "%");
}
if (isset($params['email']) && fn_string_no_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_no_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']);
}
$items_per_page = Registry::get('addons.gift_certificates.cert_per_page');
$total = db_get_field("SELECT COUNT(*) FROM ?:gift_certificates WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total, $items_per_page);
$gift_certificates = db_get_array("SELECT " . implode(',', $fields) . " FROM ?:gift_certificates WHERE 1 {$condition} ORDER BY {$sort} {$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 timestamp DESC", $v['gift_cert_id']);
$gift_certificates[$k]['debit'] = empty($debit_balance) ? $v['amount'] : $debit_balance['debit'];
}
return array($gift_certificates, $params);
}
function fn_get_users($params, &$auth, $items_per_page = 0, $custom_view = '')
{
// Init filter
$_view = !empty($custom_view) ? $custom_view : 'users';
$params = fn_init_view($_view, $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// Define fields that should be retrieved
$fields = array("?:users.user_id", "?:users.user_login", "?:users.timestamp", "?:users.user_type", "?:users.status", "?:users.firstname", "?:users.lastname", "?:users.email", "?:users.company", "?:users.company_id", "?:companies.company as company_name");
// Define sort fields
$sortings = array('id' => "?:users.user_id", 'username' => "?:users.user_login", 'email' => "?:users.email", 'name' => array("?:users.lastname", "?:users.firstname"), 'date' => "?:users.timestamp", 'type' => "?:users.user_type", 'status' => "?:users.status", 'company' => "company_name");
$directions = array('asc' => 'asc', 'desc' => 'desc');
$condition = $join = $group = '';
$group .= " GROUP BY ?:users.user_id";
if (isset($params['company']) && fn_string_no_empty($params['company'])) {
$condition .= db_quote(" AND ?:users.company LIKE ?l", "%" . trim($params['company']) . "%");
}
if (isset($params['name']) && fn_string_no_empty($params['name'])) {
$arr = fn_explode(' ', $params['name']);
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['name']) . "%", "%" . trim($params['name']) . "%");
}
}
if (isset($params['user_login']) && fn_string_no_empty($params['user_login'])) {
$condition .= db_quote(" AND ?:users.user_login LIKE ?l", "%" . trim($params['user_login']) . "%");
}
if (!empty($params['tax_exempt'])) {
$condition .= db_quote(" AND ?:users.tax_exempt = ?s", $params['tax_exempt']);
}
if (isset($params['usergroup_id']) && $params['usergroup_id'] != ALL_USERGROUPS) {
if (!empty($params['usergroup_id'])) {
$join .= db_quote(" LEFT JOIN ?:usergroup_links ON ?:usergroup_links.user_id = ?:users.user_id AND ?:usergroup_links.usergroup_id = ?i", $params['usergroup_id']);
$condition .= " AND ?:usergroup_links.status = 'A'";
} else {
$join .= " LEFT JOIN ?:usergroup_links ON ?:usergroup_links.user_id = ?:users.user_id AND ?:usergroup_links.status = 'A'";
$condition .= " AND ?:usergroup_links.user_id IS NULL";
}
}
if (!empty($params['status'])) {
$condition .= db_quote(" AND ?:users.status = ?s", $params['status']);
}
if (isset($params['email']) && fn_string_no_empty($params['email'])) {
$condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%");
}
if (isset($params['address']) && fn_string_no_empty($params['address'])) {
$condition .= db_quote(" AND (?:user_profiles.b_address LIKE ?l OR ?:user_profiles.s_address LIKE ?l)", "%" . trim($params['address']) . "%", "%" . trim($params['address']) . "%");
}
if (isset($params['zipcode']) && fn_string_no_empty($params['zipcode'])) {
$condition .= db_quote(" AND (?:user_profiles.b_zipcode LIKE ?l OR ?:user_profiles.s_zipcode LIKE ?l)", "%" . trim($params['zipcode']) . "%", "%" . trim($params['zipcode']) . "%");
}
if (!empty($params['country'])) {
$condition .= db_quote(" AND (?:user_profiles.b_country LIKE ?l OR ?:user_profiles.s_country LIKE ?l)", "%{$params['country']}%", "%{$params['country']}%");
}
if (isset($params['state']) && fn_string_no_empty($params['state'])) {
$condition .= db_quote(" AND (?:user_profiles.b_state LIKE ?l OR ?:user_profiles.s_state LIKE ?l)", "%" . trim($params['state']) . "%", "%" . trim($params['state']) . "%");
}
if (isset($params['city']) && fn_string_no_empty($params['city'])) {
$condition .= db_quote(" AND (?:user_profiles.b_city LIKE ?l OR ?:user_profiles.s_city LIKE ?l)", "%" . trim($params['city']) . "%", "%" . trim($params['city']) . "%");
}
if (!empty($params['user_type'])) {
$condition .= db_quote(' AND ?:users.user_type = ?s', $params['user_type']);
}
if (!empty($params['user_id'])) {
$condition .= db_quote(' AND ?:users.user_id IN (?n)', $params['user_id']);
}
if (!empty($params['exclude_user_types'])) {
$condition .= db_quote(" AND ?:users.user_type NOT IN (?a)", $params['exclude_user_types']);
}
if (defined('COMPANY_ID')) {
if (empty($params['user_type']) || !empty($params['user_type']) && $params['user_type'] == 'C' || !empty($params['exclude_user_types']) && !in_array('C', $params['exclude_user_types'])) {
$_cond = db_quote("(?:users.user_type = 'A' && ?:users.company_id = ?i)", COMPANY_ID);
$company_customers = db_get_fields("SELECT user_id FROM ?:orders WHERE company_id = ?i", COMPANY_ID);
if ($company_customers) {
$_cond = db_quote("((?:users.user_type = 'C' && ?:users.user_id IN (?n)) OR {$_cond})", $company_customers);
}
$condition .= " AND {$_cond} ";
} else {
$condition .= fn_get_company_condition('?:users.company_id');
}
}
if (!empty($params['p_ids']) || !empty($params['product_view_id'])) {
$arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids'];
if (empty($params['product_view_id'])) {
$condition .= db_quote(" AND ?:order_details.product_id IN (?n)", $arr);
} else {
$condition .= db_quote(" AND ?:order_details.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true))));
}
$join .= db_quote(" LEFT JOIN ?:orders ON ?:orders.user_id = ?:users.user_id LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id");
}
if (defined('RESTRICTED_ADMIN')) {
// FIXME: NOT GOOD
$condition .= db_quote(" AND (?:users.user_type != 'A' || (?:users.user_type = 'A' AND ?:users.user_id = ?i))", $auth['user_id']);
}
$active_user_types = fn_get_user_types();
$condition .= db_quote(" AND ?:users.user_type IN(?a)", array_keys($active_user_types));
$join .= db_quote(" LEFT JOIN ?:user_profiles ON ?:user_profiles.user_id = ?:users.user_id");
$join .= db_quote(" LEFT JOIN ?:companies ON ?:companies.company_id = ?:users.company_id");
fn_set_hook('get_users', $params, $fields, $sortings, $condition, $join);
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 = (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';
// Paginate search results
$limit = '';
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(DISTINCT(?:users.user_id)) FROM ?:users {$join} WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$users = db_get_array("SELECT " . implode(', ', $fields) . " FROM ?:users {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}");
return array($users, $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_add_payouts($params, $lang_code = CART_LANGUAGE)
{
// Init filter
$params = fn_init_view('pay_affiliates', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// default page is 1
// Define sort fields
$sortings = array('action' => "actions.action", 'date' => "actions.date", 'cost' => "actions.amount", 'banner' => "banner");
$directions = array('asc' => 'asc', 'desc' => 'desc');
if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) {
$params['sort_order'] = 'desc';
}
if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) {
$params['sort_by'] = 'date';
}
$sorting = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']];
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
$new_payouts = array();
if (!empty($params['partner_ids'])) {
$date_condition = "";
if (!empty($params['last_payout']) && !empty($params['time_from'])) {
$date_condition = db_quote(" AND date < ?i", $params['time_from']);
}
foreach ($params['partner_ids'] as $_partner_id) {
$condition = "1 " . $date_condition . db_quote(" AND approved = 'Y' AND payout_id = 0 AND partner_id = ?i", $_partner_id);
$new_payouts[$_partner_id]['amount'] = db_get_field("SELECT SUM(amount) FROM ?:aff_partner_actions WHERE {$condition}");
if (empty($new_payouts[$_partner_id]['amount'])) {
unset($new_payouts[$_partner_id]);
} else {
$new_payouts[$_partner_id]['partner'] = fn_get_partner_data($_partner_id);
if (!empty($new_payouts[$_partner_id]['partner']['plan_id'])) {
$new_payouts[$_partner_id]['plan'] = fn_get_affiliate_plan_data($new_payouts[$_partner_id]['partner']['plan_id'], $lang_code);
}
$_params = array('sort_by' => $params['sort_by'], 'sort_order' => $params['sort_order'] == 'asc' ? 'desc' : 'asc');
$new_payouts[$_partner_id]['actions'] = fn_get_affiliate_actions($condition, $_params, true, @$params['page']);
$new_payouts[$_partner_id]['date_range']['min'] = db_get_field("SELECT MIN(date) FROM ?:aff_partner_actions WHERE {$condition}");
$new_payouts[$_partner_id]['date_range']['max'] = db_get_field("SELECT MAX(date) FROM ?:aff_partner_actions WHERE {$condition}");
}
}
}
return array($new_payouts, $params);
}
function fn_get_events($params)
{
// Init filter
$params = fn_init_view('events', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// Define fields that should be retrieved
$fields = array('*');
$condition = $join = '';
if (!empty($params['type'])) {
$condition .= db_quote(" AND type IN (?a)", $params['type']);
}
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (start_date >= ?i AND end_date <= ?i)", $params['time_from'], $params['time_to']);
}
if (isset($params['owner']) && fn_string_no_empty($params['owner'])) {
$condition .= db_quote(" AND (owner LIKE ?l OR ?:giftreg_events.email LIKE ?l)", "%" . trim($params['owner']) . "%", "%" . trim($params['owner']) . "%");
}
if (isset($params['title']) && fn_string_no_empty($params['title'])) {
$condition .= db_quote(" AND title LIKE ?l", "%" . trim($params['title']) . "%");
}
if (!empty($params['type'])) {
$condition .= db_quote(" AND type IN (?a)", $params['type']);
}
if (!empty($params['status'])) {
$condition .= db_quote(" AND status IN (?a)", $params['status']);
}
if (isset($params['subscriber']) && fn_string_no_empty($params['subscriber'])) {
$join .= " INNER JOIN ?:giftreg_event_subscribers ON ?:giftreg_event_subscribers.event_id = ?:giftreg_events.event_id";
$condition .= db_quote(" AND (?:giftreg_event_subscribers.name LIKE ?l OR ?:giftreg_event_subscribers.email LIKE ?l)", "%" . trim($params['subscriber']) . "%", "%" . trim($params['subscriber']) . "%");
}
if (!empty($params['search_fields'])) {
$_cond = array();
$total_hits = 0;
foreach ($params['search_fields'] as $f_id => $f_val) {
$_condition = array();
if (substr_count($f_value, '/') == 2) {
// FIXME: it's date field
$_condition[] = db_quote("?:giftreg_event_fields.value = ?s", fn_parse_date($f_val));
} else {
$_condition[] = db_quote("?:giftreg_event_fields.value LIKE ?l", "%{$f_val}%");
}
if (!empty($f_val)) {
$total_hits++;
$_cond[] = db_quote("(?:giftreg_event_fields.field_id = ?i AND ", $f_id) . implode(" AND ", $_condition) . ')';
}
}
if (!empty($_cond)) {
$cache_field_search = db_get_fields("SELECT event_id, COUNT(event_id) as cnt FROM ?:giftreg_event_fields WHERE (" . implode(' OR ', $_cond) . ") GROUP BY event_id HAVING cnt = {$total_hits}");
$condition .= db_quote(" AND event_id IN (?n)", $cache_field_search);
}
}
if (!empty($params['today_events'])) {
$condition .= db_quote("AND (start_date <= ?i AND end_date > ?i)", TIME, TIME);
}
$total = db_get_field("SELECT COUNT(*) FROM ?:giftreg_events {$join} WHERE 1 {$condition}");
$limit = fn_paginate($params['page'], $total, Registry::get('settings.Appearance.' . (AREA == 'A' ? 'admin_' : '') . 'elements_per_page'));
// FIXME
$events = db_get_array("SELECT " . implode(',', $fields) . " FROM ?:giftreg_events {$join} WHERE 1 {$condition} ORDER BY start_date ASC {$limit}");
return array($events, $params);
}
