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_discussion_posts($thread_id = 0, $page = 0, $first_limit = '', $random = 'N')
{
$sets = Registry::get('addons.discussion');
$discussion_object_types = fn_get_discussion_objects();
if (empty($thread_id)) {
return false;
}
$thread_data = db_get_row("SELECT type, object_type FROM ?:discussion WHERE thread_id = ?i", $thread_id);
if ($thread_data['type'] == 'D') {
return false;
}
$join = $fields = '';
if ($thread_data['type'] == 'C' || $thread_data['type'] == 'B') {
$join .= " LEFT JOIN ?:discussion_messages ON ?:discussion_messages.post_id = ?:discussion_posts.post_id ";
$fields .= ", ?:discussion_messages.message";
}
if ($thread_data['type'] == 'R' || $thread_data['type'] == 'B') {
$join .= " LEFT JOIN ?:discussion_rating ON ?:discussion_rating.post_id = ?:discussion_posts.post_id ";
$fields .= ", ?:discussion_rating.rating_value";
}
$status_cond = AREA == 'A' ? '' : " AND ?:discussion_posts.status = 'A'";
$total_pages = db_get_field("SELECT COUNT(*) FROM ?:discussion_posts WHERE thread_id = ?i {$status_cond}", $thread_id);
if ($first_limit != '') {
$limit = "LIMIT {$first_limit}";
} else {
$limit = fn_paginate($page, $total_pages, $sets[$discussion_object_types[$thread_data['object_type']] . '_posts_per_page']);
}
$order_by = $random == 'N' ? '?:discussion_posts.timestamp DESC' : 'RAND()';
return db_get_array("SELECT ?:discussion_posts.* {$fields} FROM ?:discussion_posts {$join} WHERE ?:discussion_posts.thread_id = ?i {$status_cond} ORDER BY ?p {$limit}", $thread_id, $order_by);
}
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_states($country = '', $lang_code = CART_LANGUAGE, $paginate = false)
{
if (empty($country)) {
return '';
}
$limit = '';
if ($paginate == true) {
$state_count = db_get_field("SELECT COUNT(*) FROM ?:states WHERE country_code = ?s", $country);
$limit = fn_paginate(@$_REQUEST['page'], $state_count, Registry::get('settings.Appearance.admin_elements_per_page'));
}
return db_get_array("SELECT a.state_id, a.country_code, a.code, a.status, b.state FROM ?:states as a LEFT JOIN ?:state_descriptions as b ON b.state_id = a.state_id AND b.lang_code = ?s WHERE a.country_code = ?s ORDER BY b.state {$limit}", $lang_code, $country);
}
function fn_get_store_locations($params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
$default_params = array('page' => 1, 'q' => '', 'match' => 'any');
$params = array_merge($default_params, $params);
$fields = array('?:store_locations.*', '?:store_location_descriptions.*', '?:country_descriptions.country as country_title');
$join = db_quote(" LEFT JOIN ?:store_location_descriptions ON ?:store_locations.store_location_id = ?:store_location_descriptions.store_location_id AND ?:store_location_descriptions.lang_code = ?s", $lang_code);
$join .= db_quote(" LEFT JOIN ?:country_descriptions ON ?:store_locations.country = ?:country_descriptions.code AND ?:country_descriptions.lang_code = ?s", $lang_code);
$condition = 1;
if (AREA == 'C') {
$condition .= " AND status = 'A'";
}
// Search string condition for SQL query
if (!empty($params['q'])) {
if ($params['match'] == 'any') {
$pieces = explode(' ', $params['q']);
$search_type = ' OR ';
} elseif ($params['match'] == 'all') {
$pieces = explode(' ', $params['q']);
$search_type = ' AND ';
} else {
$pieces = array($params['q']);
$search_type = '';
}
$_condition = array();
foreach ($pieces as $piece) {
$tmp = db_quote("?:store_location_descriptions.name LIKE ?l", "%{$piece}%");
// check search words
$tmp .= db_quote(" OR ?:store_location_descriptions.description LIKE ?l", "%{$piece}%");
$tmp .= db_quote(" OR ?:store_location_descriptions.city LIKE ?l", "%{$piece}%");
$tmp .= db_quote(" OR ?:country_descriptions.country LIKE ?l", "%{$piece}%");
$_condition[] = '(' . $tmp . ')';
}
$_cond = implode($search_type, $_condition);
if (!empty($_condition)) {
$condition .= ' AND (' . $_cond . ') ';
}
unset($_condition);
}
$condition .= AREA == 'C' && defined('CART_LOCALIZATION') ? fn_get_localizations_condition('?:store_locations.localization') : '';
$sorting = "?:store_locations.position, ?:store_location_descriptions.name";
$limit = '';
$total = 0;
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(?:store_locations.store_location_id) FROM ?:store_locations ?p WHERE ?p", $join, $condition);
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$data = db_get_array('SELECT ?p FROM ?:store_locations ?p WHERE ?p GROUP BY ?:store_locations.store_location_id ORDER BY ?p ?p', implode(', ', $fields), $join, $condition, $sorting, $limit);
if (!$total) {
$total = count($data);
}
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);
}
}
return array(CONTROLLER_STATUS_OK, "mailing_lists.manage");
}
if ($mode == 'update') {
fn_add_breadcrumb(fn_get_lang_var('newsletters'), "newsletters.manage");
$view->assign('autoresponders', fn_get_newsletters(array('type' => NEWSLETTER_TYPE_AUTORESPONDER, 'only_available' => false), DESCR_SL));
$view->assign('mailing_list', fn_get_mailing_list_data($_REQUEST['list_id'], DESCR_SL));
} elseif ($mode == 'delete') {
if (!empty($_REQUEST['list_id'])) {
db_query("DELETE FROM ?:common_descriptions WHERE object_id = ?i AND object_holder='mailing_lists'", $_REQUEST['list_id']);
db_query("DELETE FROM ?:mailing_lists WHERE list_id = ?i", $_REQUEST['list_id']);
db_query("DELETE FROM ?:user_mailing_lists WHERE list_id = ?i", $_REQUEST['list_id']);
$_mailing_lists = fn_get_mailing_lists(array('only_available' => false, 'limit' => $limit), DESCR_SL);
if (empty($_mailing_lists)) {
$view->display('addons/news_and_emails/views/mailing_lists/manage.tpl');
}
}
exit;
} elseif ($mode == 'manage') {
$total_pages = db_get_field("SELECT COUNT(*) FROM ?:mailing_lists");
$limit = fn_paginate(@$_REQUEST['page'], $total_pages, Registry::get('settings.Appearance.admin_elements_per_page'));
$mailing_lists = fn_get_mailing_lists(array('only_available' => false, 'limit' => $limit), DESCR_SL);
$subscribers = db_get_hash_array("SELECT * FROM ?:subscribers", 'subscriber_id');
foreach ($mailing_lists as &$list) {
$list['subscribers_num'] = db_get_field("SELECT COUNT(*) FROM ?:user_mailing_lists WHERE list_id={$list['list_id']}");
}
$view->assign('mailing_lists', $mailing_lists);
$view->assign('autoresponders', fn_get_newsletters(array('type' => NEWSLETTER_TYPE_AUTORESPONDER, 'only_available' => false), DESCR_SL));
$view->assign('subscribers', $subscribers);
}
/** /Body **/
function fn_get_usergroup_requests($params, $items_per_page = 0, $status = 'P', $lang_code = CART_LANGUAGE)
{
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// default page is 1
$fields = array("?:usergroup_links.user_id", "?:usergroup_links.link_id", "?:usergroup_links.usergroup_id", "?:usergroup_links.status", "?:users.firstname", "?:users.lastname", "?:usergroup_descriptions.usergroup");
$sortings = array('customer' => array("?:users.lastname", "?:users.firstname"), 'usergroup' => "?:usergroup_descriptions.usergroup", 'status' => "?:usergroup_links.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'] = 'customer';
}
$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 (!empty($params['cname'])) {
$arr = explode(' ', $params['cname']);
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['cname']}%", "%{$params['cname']}%");
}
}
if (!empty($params['ugname'])) {
$condition .= db_quote(" AND ?:usergroup_descriptions.usergroup LIKE ?l", "%{$params['ugname']}%");
}
$join = db_quote("LEFT JOIN ?:users ON ?:usergroup_links.user_id = ?:users.user_id LEFT JOIN ?:usergroup_descriptions ON ?:usergroup_links.usergroup_id = ?:usergroup_descriptions.usergroup_id AND ?:usergroup_descriptions.lang_code = ?s", $lang_code);
$limit = '';
if (!empty($items_per_page)) {
$total = db_get_field("SELECT COUNT(?:usergroup_links.link_id) FROM ?:usergroup_links {$join} WHERE ?:usergroup_links.status = ?s {$condition}", $status);
$limit = fn_paginate($params['page'], $total, $items_per_page);
}
$requests = db_get_array("SELECT " . implode(', ', $fields) . " FROM ?:usergroup_links {$join} WHERE ?:usergroup_links.status = ?s {$condition} ORDER BY {$sorting} {$limit}", $status);
return array($requests, $params);
}
$view->assign('avail_positions', fn_get_available_group($selected_section, 0, DESCR_SL));
$view->assign('specific_settings', fn_process_specific_settings(fn_get_block_specific_settings()));
$view->assign('location', $selected_section);
$view->assign('blocks', $blocks);
} elseif ($mode == 'manage_items') {
$view->assign('location', $_REQUEST['location']);
$view->assign('block', fn_get_block_data($_REQUEST['block_id'], CART_LANGUAGE, false, $_REQUEST['location']));
$object_id = empty($_REQUEST['object_id']) ? '' : $_REQUEST['object_id'];
$view->assign('object_id', $object_id);
$view->assign('redir_url', empty($_REQUEST['redir_url']) ? '' : $_REQUEST['redir_url']);
$block_items = db_get_field("SELECT item_ids FROM ?:block_links WHERE block_id = ?i AND object_id = ?i", $_REQUEST['block_id'], $object_id);
if (!empty($block_items)) {
$items_ids = explode(',', $block_items);
$page = empty($_REQUEST['page']) ? 1 : $_REQUEST['page'];
$items_per_page = Registry::get('settings.Appearance.admin_elements_per_page');
fn_paginate($page, count($items_ids), $items_per_page);
if (!empty($_SESSION['items_per_page'])) {
$items_per_page = $_SESSION['items_per_page'];
}
$start_pos = ($page - 1) * $items_per_page;
$view->assign('start_position', $start_pos);
$view->assign('block_items', array_slice($items_ids, $start_pos, $items_per_page));
} else {
list($blocks) = fn_get_blocks(array('location' => $_REQUEST['location']));
if (!empty($blocks)) {
$view->assign('block', fn_get_selected_block_data(array('selected_block_id' => $_REQUEST['block_id']), $blocks, $_REQUEST['object_id'], $_REQUEST['location']));
}
}
} elseif ($mode == 'specific_settings') {
$specific_settings = fn_get_block_specific_settings();
if (!empty($specific_settings[$_REQUEST['type']]) && !empty($specific_settings[$_REQUEST['type']][$_REQUEST['value']])) {
function fn_pay_affiliates($params, $do_pagination = false, $page = 1)
{
// Init filter
$params = fn_init_view('pay_affiliates', $params);
// Set default values to input params
$params['page'] = empty($params['page']) ? 1 : $params['page'];
// default page is 1
// Define sort fields
$sortings = array('username' => 'user_login', 'email' => 'email', 'partner' => 'firstname', 'amount' => 'amount', 'awaiting_amount' => 'awaiting_amount', 'date' => 'date');
$directions = array('asc' => 'asc', 'desc' => 'desc');
if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) {
$params['sort_order'] = 'desc';
}
$sorting_direction = $directions[$params['sort_order']];
if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) {
$params['sort_by'] = 'username';
}
$sort_by = $params['sort_by'];
if ($sort_by == 'date' || $sort_by == 'awaiting_amount') {
$sort_by = 'username';
}
$sorting = (is_array($sortings[$sort_by]) ? implode(' ' . $sorting_direction . ', ', $sortings[$sort_by]) : $sortings[$sort_by]) . " " . $sorting_direction;
// Reverse sorting (for usage in view)
$params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc';
$pay_filter = array();
$pay_filter['min_payment'] = empty($params['min_payment']) ? '' : 'Y';
$pay_filter['last_payout'] = empty($params['last_payout']) ? '' : 'Y';
$pay_filter['amount_from'] = empty($params['amount_from']) ? '' : floatval($params['amount_from']);
$pay_filter['amount_to'] = empty($params['amount_to']) ? '' : floatval($params['amount_to']);
$_SESSION['pay_filter'] = $pay_filter;
$join = $condition = $group = '';
$having = array();
if (!empty($params['min_payment'])) {
$having[] = 'SUM(pa.amount) >= AVG(ap.min_payment)';
}
if (isset($params['amount_from']) && fn_is_numeric($params['amount_from'])) {
$having[] = db_quote("SUM(pa.amount) >= ?d", $params['amount_from']);
}
if (isset($params['amount_to']) && fn_is_numeric($params['amount_to'])) {
$having[] = db_quote("SUM(pa.amount) <= ?d", $params['amount_to']);
}
if (!empty($params['last_payout'])) {
$condition .= db_quote(" AND pa.date < ?i", fn_parse_date(fn_get_date_of_payment_period(), true));
}
$group = 'GROUP BY pa.partner_id' . (empty($having) ? ' ' : ' HAVING (' . implode(') AND (', $having) . ') ');
if ($do_pagination) {
if (empty($page)) {
$page = 1;
}
$cnt_list_stats = db_get_fields("SELECT DISTINCT(pa.partner_id) FROM ?:aff_partner_actions as pa LEFT JOIN ?:aff_partner_profiles as pp ON pa.partner_id = pp.user_id LEFT JOIN ?:affiliate_plans as ap ON ap.plan_id = pp.plan_id WHERE pa.approved = 'Y' AND pa.payout_id = 0 ?p ?p", $condition, $group);
$limit = fn_paginate($page, count($cnt_list_stats));
} else {
$limit = '';
}
$partner_balances = db_get_hash_array("SELECT pa.partner_id, u.user_login, u.firstname, u.lastname, u.email, SUM(amount) as amount FROM ?:aff_partner_actions as pa LEFT JOIN ?:users as u ON pa.partner_id = u.user_id LEFT JOIN ?:aff_partner_profiles as pp ON pa.partner_id = pp.user_id LEFT JOIN ?:affiliate_plans as ap ON ap.plan_id = pp.plan_id WHERE pa.approved = 'Y' AND payout_id = 0 ?p ?p ORDER BY {$sorting} {$limit}", 'partner_id', $condition, $group);
$_partners = db_get_hash_array("SELECT pa.partner_id, SUM(amount) as amount FROM ?:aff_partner_actions as pa LEFT JOIN ?:aff_partner_profiles as pp ON pa.partner_id = pp.user_id LEFT JOIN ?:affiliate_plans as ap ON ap.plan_id = pp.plan_id WHERE pa.approved = 'N' AND payout_id = 0 GROUP BY pa.partner_id ORDER BY amount {$sorting_direction}", 'partner_id');
$last_payout_dates = db_get_hash_array("SELECT partner_id, MAX(date) as date FROM ?:affiliate_payouts GROUP BY partner_id ORDER BY date {$sorting_direction}", 'partner_id');
if ($params['sort_by'] != 'date' && $params['sort_by'] != 'awaiting_amount') {
foreach ($partner_balances as $_partner_id => $_partner_data) {
$partner_balances[$_partner_id]['awaiting_amount'] = empty($_partners[$_partner_id]['amount']) ? '' : floatval($_partners[$_partner_id]['amount']);
$partner_balances[$_partner_id]['last_payout_date'] = @$last_payout_dates[$_partner_id]['date'];
}
} else {
$temp_balances = array();
if ($params['sort_by'] == 'awaiting_amount') {
foreach ($_partners as $_partner_id => $_partner_data) {
if (!empty($partner_balances[$_partner_id])) {
$temp_balances[$_partner_id] = $partner_balances[$_partner_id];
$temp_balances[$_partner_id]['awaiting_amount'] = floatval($_partner_data['amount']);
$temp_balances[$_partner_id]['last_payout_date'] = @$last_payout_dates[$_partner_id]['date'];
unset($partner_balances[$_partner_id]);
}
}
} else {
foreach ($last_payout_dates as $_partner_id => $_date) {
if (!empty($partner_balances[$_partner_id])) {
$temp_balances[$_partner_id] = $partner_balances[$_partner_id];
$temp_balances[$_partner_id]['awaiting_amount'] = empty($_partners[$_partner_id]['amount']) ? '' : floatval($_partners[$_partner_id]['amount']);
$temp_balances[$_partner_id]['last_payout_date'] = $_date['date'];
unset($partner_balances[$_partner_id]);
}
}
}
$temp_balances2 = array();
foreach ($partner_balances as $_partner_id => $_partner_data) {
$temp_balances2[$_partner_id] = $_partner_data;
$temp_balances2[$_partner_id]['awaiting_amount'] = empty($_partners[$_partner_id]['amount']) ? '' : floatval($_partners[$_partner_id]['amount']);
$temp_balances2[$_partner_id]['last_payout_date'] = @$last_payout_dates[$_partner_id]['date'];
}
$partner_balances = $sorting_direction == 'desc' ? $temp_balances + $temp_balances2 : $temp_balances2 + $temp_balances;
}
return array($partner_balances, $params);
}
function fn_get_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_configurator_groups($params, $items_per_page = 0)
{
$default_params = array('page' => 1);
$params = array_merge($default_params, $params);
$sortings = array('group_id' => "?:conf_groups.group_id", 'group_name' => "?:conf_group_descriptions.configurator_group_name", 'step_name' => "?:conf_groups.step_id", 'display_type' => "?:conf_groups.configurator_group_type", 'status' => "?:conf_groups.status");
$directions = array('asc' => 'asc', 'desc' => 'desc');
if (empty($params['group_sort_order']) || empty($directions[$params['group_sort_order']])) {
$params['group_sort_order'] = 'asc';
}
if (empty($params['group_sort_by']) || empty($sortings[$params['group_sort_by']])) {
$params['group_sort_by'] = 'group_name';
}
$sorting = $sortings[$params['group_sort_by']] . ' ' . $directions[$params['group_sort_order']];
// Reverse sorting (for usage in view)
$params['group_sort_order'] = $params['group_sort_order'] == 'asc' ? 'desc' : 'asc';
$limit = '';
if (!empty($items_per_page)) {
$group_data_count = db_get_field("SELECT COUNT(*) FROM ?:conf_groups");
$limit = fn_paginate($params['page'], $group_data_count, $items_per_page);
}
$groups = db_get_hash_array("SELECT ?:conf_groups.group_id, ?:conf_group_descriptions.configurator_group_name, ?:conf_group_descriptions.full_description, ?:conf_groups.step_id, ?:conf_groups.status, ?:conf_groups.configurator_group_type FROM ?:conf_groups LEFT JOIN ?:conf_group_descriptions ON ?:conf_group_descriptions.group_id = ?:conf_groups.group_id WHERE ?:conf_group_descriptions.lang_code = ?s ORDER BY {$sorting} {$limit}", 'group_id', DESCR_SL);
return array($groups, $params);
}
function fn_twg_set_response_categories(&$response, $params, $items_per_page = 0, $lang_code = CART_LANGUAGE)
{
if (empty($items_per_page)) {
$result = fn_twg_api_get_categories($params, $lang_code);
$response->setMeta(db_get_field("SELECT COUNT(*) FROM ?:categories"), 'total_items');
$response->setResponseList($result);
} else {
$default_params = array('depth' => 0, 'page' => 1);
$params = array_merge($default_params, $params);
$params['type'] = 'plain_tree';
$categories = fn_twg_api_get_categories($params, $lang_code);
if (!empty($categories)) {
$total = count($categories['category']);
$params['page'] = !empty($params['page']) ? $params['page'] : 1;
fn_paginate($params['page'], $total, $items_per_page);
$view = fn_twg_get_view_object();
$pagination = $view->getTemplateVars('pagination');
$start = $pagination['prev_page'] * $pagination['items_per_page'];
$end = $start + $items_per_page;
$result = array();
for ($i = $start; $i < $end; $i++) {
if (!isset($categories['category'][$i])) {
break;
}
$result[] = $categories['category'][$i];
}
$response->setResponseList(array('category' => $result));
$pagination_params = array('items_per_page' => !empty($items_per_page) ? $items_per_page : TWG_RESPONSE_ITEMS_LIMIT, 'page' => !empty($_REQUEST['page']) ? $_REQUEST['page'] : 1);
fn_twg_set_response_pagination($response, $pagination_params);
}
}
$category_id = !empty($params['id']) ? $params['id'] : 0;
if (!empty($category_id)) {
$parent_data = db_get_row("SELECT a.parent_id, b.category\n FROM ?:categories AS a\n LEFT JOIN ?:category_descriptions AS b\n ON a.parent_id = b.category_id\n WHERE a.category_id = ?i AND b.lang_code = ?s", $category_id, $lang_code);
if (!empty($parent_data)) {
$response->setMeta($parent_data['parent_id'], 'grand_id');
$response->setMeta($parent_data['category'], 'grand_category');
}
$response->setMeta($category_id, 'category_id');
$category_data = array_pop(db_get_array("SELECT category, description\n FROM ?:category_descriptions\n WHERE category_id = ?i AND lang_code = ?s", $params['category_id'], $lang_code));
$response->setMeta($category_data['category'], 'category_name');
$response->setMeta($category_data['description'], 'description');
}
}
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);
}
}
$q = empty($_REQUEST['q']) ? '' : $_REQUEST['q'];
return array(CONTROLLER_STATUS_OK, "languages.manage?q={$q}");
}
//
// Get language variables values
//
if ($mode == 'manage') {
if (isset($_REQUEST['q']) && fn_string_no_empty($_REQUEST['q'])) {
$_query = db_quote(" lang_code = ?s AND (name LIKE ?l OR value LIKE ?l)", DESCR_SL, "%" . trim($_REQUEST['q']) . "%", "%" . trim($_REQUEST['q']) . "%");
} else {
$_query = db_quote(" lang_code = ?s", DESCR_SL);
}
$page = empty($_REQUEST['page']) ? 1 : $_REQUEST['page'];
$lang_data_count = db_get_field("SELECT COUNT(*) FROM ?:language_values WHERE {$_query}");
$limit = fn_paginate($page, $lang_data_count, Registry::get('settings.Appearance.admin_elements_per_page'));
$lang_data = db_get_array("SELECT name, value FROM ?:language_values WHERE {$_query} ORDER BY name {$limit}");
Registry::set('navigation.tabs', array('translations' => array('title' => fn_get_lang_var('translations'), 'js' => true), 'languages' => array('title' => fn_get_lang_var('languages'), 'js' => true)));
$view->assign('lang_data', $lang_data);
$view->assign('langs', Registry::get('languages'));
} elseif ($mode == 'delete_variable') {
if (!empty($_REQUEST['name'])) {
db_query("DELETE FROM ?:language_values WHERE name = ?s", $_REQUEST['name']);
}
return array(CONTROLLER_STATUS_REDIRECT, "languages.manage");
//
// Delete languages
//
} elseif ($mode == 'delete_language') {
if (!empty($_REQUEST['lang_code'])) {
fn_delete_languages($_REQUEST['lang_code']);
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 fn_get_event_product($event_id, $items_per_page, $page, $lang_code = CART_LANGUAGE)
{
$total = db_get_field("SELECT DISTINCT(COUNT(*)) FROM ?:giftreg_event_products LEFT JOIN ?:product_descriptions ON ?:product_descriptions.product_id = ?:giftreg_event_products.product_id AND ?:product_descriptions.lang_code = ?s WHERE event_id = ?i", $lang_code, $event_id);
$limit = fn_paginate($page, $total, $items_per_page);
return db_get_hash_array("SELECT * FROM ?:giftreg_event_products LEFT JOIN ?:product_descriptions ON ?:product_descriptions.product_id = ?:giftreg_event_products.product_id AND ?:product_descriptions.lang_code = ?s WHERE event_id = ?i {$limit}", 'item_id', $lang_code, $event_id);
}
function fn_search_simple($params, $search, $object, $items_per_page)
{
$entry = $search['conditions']['values'][$object];
$entry['table'] = !empty($entry['table']) ? $entry['table'] : "?:" . $object;
$total = 0;
if (empty($params['page'])) {
$params['page'] = 1;
}
if ($items_per_page) {
$total = db_get_field("SELECT COUNT(DISTINCT({$entry['table']}.{$entry['key']})) FROM ?:{$object} as {$entry['table']} {$entry['join']} WHERE {$entry['condition']}");
$limit = fn_paginate($params['page'], $total, $items_per_page);
if (preg_match("/\\s+(\\d+),/", $limit, $begin)) {
$begin = intval($begin[1]);
} else {
$begin = 0;
}
} else {
$limit = '';
$total = 0;
$begin = 0;
}
$data = db_get_hash_array("SELECT " . implode(', ', $entry['fields']) . " FROM ?:{$object} as {$entry['table']} {$entry['join']} WHERE {$entry['condition']} GROUP BY {$entry['table']}.{$entry['key']} ORDER BY {$entry['sort']} " . $limit, $entry['key']);
$num = 0;
foreach ($data as $key => $entry) {
$data[$key]['id'] = $key;
$data[$key]['object'] = $object;
if (!empty($search['more_data'][$object])) {
$search['more_data'][$object]($data[$key]);
}
if ($num == 0) {
$data[$key]['first'] = true;
}
$data[$key]['result_number'] = $begin + $num + 1;
$data[$key]['result_type'] = 'full';
$num++;
}
if (!$total) {
$total = count($data);
}
return array($data, $params, $total);
}
function fn_get_newsletters($params = array(), $lang_code = CART_LANGUAGE)
{
$default_params = array('type' => NEWSLETTER_TYPE_NEWSLETTER, 'only_available' => true);
$params = array_merge($default_params, $params);
$limit = '';
if (!empty($params['paginate'])) {
$params['page'] = empty($params['page']) ? 1 : $params['page'];
$total = db_get_field("SELECT COUNT(*) FROM ?:newsletters");
$limit = fn_paginate($params['page'], $total, AREA == 'A' ? Registry::get('settings.Appearance.admin_elements_per_page') : Registry::get('settings.Appearance.elements_per_page'));
}
$_conditions = array();
if ($params['only_available']) {
$_conditions[] = "?:newsletters.status = 'A'";
}
if ($params['type']) {
$_conditions[] = db_quote("?:newsletters.type = ?s", $params['type']);
}
if (!empty($_conditions)) {
$_conditions = implode(' AND ', $_conditions);
} else {
$_conditions = '1';
}
$newsletters = db_get_array("SELECT ?:newsletters.newsletter_id, ?:newsletters.status, ?:newsletters.sent_date, ?:newsletters.status, ?:newsletter_descriptions.newsletter FROM ?:newsletters LEFT JOIN ?:newsletter_descriptions ON ?:newsletter_descriptions.newsletter_id=?:newsletters.newsletter_id AND ?:newsletter_descriptions.lang_code= ?s WHERE ?p ORDER BY ?:newsletters.sent_date DESC, ?:newsletters.status {$limit}", $lang_code, $_conditions);
return $newsletters;
}
} else {
$sortings = array('value' => 'a.value', 'reason' => 'b.reason', 'created' => 'a.timestamp', 'status' => 'a.status');
}
$directions = array('asc' => 'asc', 'desc' => 'desc');
$sort_order = !empty($_REQUEST['sort_order']) ? $_REQUEST['sort_order'] : '';
$sort_by = !empty($_REQUEST['sort_by']) ? $_REQUEST['sort_by'] : '';
if (empty($sort_order) || empty($directions[$sort_order])) {
$sort_order = 'desc';
}
if (empty($sort_by) || empty($sortings[$sort_by])) {
$sort_by = 'created';
}
$sort = $sortings[$sort_by] . " " . $directions[$sort_order];
$items_per_page = Registry::get('settings.Appearance.admin_elements_per_page');
$total_items = db_get_field("SELECT COUNT(a.item_id) FROM ?:access_restriction as a WHERE a.type IN (?a)", $types[$selected_section]);
$limit = fn_paginate(@$_REQUEST['page'], $total_items, $items_per_page);
// fixme
$access[$selected_section] = db_get_array("SELECT a.*, b.reason FROM ?:access_restriction as a LEFT JOIN ?:access_restriction_reason_descriptions as b ON a.item_id = b.item_id AND b.type = a.type AND lang_code = ?s WHERE a.type IN (?a) ORDER BY {$sort} {$limit}", DESCR_SL, $types[$selected_section]);
$ip = fn_get_ip(true);
$view->assign('sort_order', $sort_order == 'asc' ? 'desc' : 'asc');
$view->assign('sort_by', $sort_by);
$view->assign('show_mp', db_get_field("SELECT item_id FROM ?:access_restriction WHERE type = ?s", $selected_section == 'ip' ? 'ipb' : 'aab'));
$view->assign('selected_section', $selected_section);
$view->assign('access', $access);
$view->assign('access_types', $types);
$view->assign('host_ip', $ip['host']);
} elseif ($mode == 'delete') {
if (!empty($_REQUEST['item_id'])) {
db_query("DELETE FROM ?:access_restriction WHERE item_id = ?i", $_REQUEST['item_id']);
}
return array(CONTROLLER_STATUS_REDIRECT, "access_restrictions.manage?selected_section={$_REQUEST['selected_section']}");
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_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);
}
/**
* 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_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_affiliate_plans($params, $lang_code = CART_LANGUAGE)
{
$params['page'] = empty($params['page']) ? 1 : $params['page'];
$total = db_get_field("SELECT COUNT(*) FROM ?:affiliate_plans as plans LEFT JOIN ?:common_descriptions as com_descr ON object_holder = 'affiliate_plans' AND object_id = plan_id AND lang_code = ?s WHERE 1 ORDER BY object", $lang_code);
$items_per_page = AREA == 'A' ? Registry::get('settings.Appearance.admin_elements_per_page') : Registry::get('settings.Appearance.elements_per_page');
$limit = fn_paginate($params['page'], $total, $items_per_page);
return db_get_hash_array("SELECT plans.plan_id, object as name, COUNT(user_id) as count_partners, status FROM ?:affiliate_plans as plans LEFT JOIN ?:common_descriptions as com_descr ON object_holder = 'affiliate_plans' AND object_id = plans.plan_id AND lang_code = ?s LEFT JOIN ?:aff_partner_profiles ON plans.plan_id = ?:aff_partner_profiles.plan_id AND ?:aff_partner_profiles.approved = 'A' WHERE 1 GROUP BY plans.plan_id ORDER BY object {$limit} ", 'plan_id', $lang_code);
}
function fn_get_affiliate_actions($params, $sorting = 'date DESC', $do_pagination = false, $page = 1, $lang_code = CART_LANGUAGE)
{
$payout_types =& Registry::get('payout_types');
// Define sort fields
$sortings = array('action' => "actions.action", 'date' => "actions.date", 'cost' => "actions.amount", 'customer' => array("customers.lastname", "customers.firstname"), 'partner' => "partners.firstname", 'banner' => "?:aff_banner_descriptions.title", 'status' => "actions.payout_id, actions.approved");
$directions = array('asc' => 'asc', 'desc' => 'desc');
if (is_array($params)) {
$condition = '1';
if (!empty($params['action_id'])) {
$condition .= db_quote(" AND actions.action_id = ?i", $params['action_id']);
}
if (!empty($params['object_type'])) {
$condition .= db_quote(" AND alinks.object_type = ?s", $params['object_type']);
}
if (!empty($params['object_data'])) {
$condition .= db_quote(" AND alinks.object_data = ?s", $params['object_data']);
}
if (!empty($params['payout_id'])) {
$condition .= db_quote(" AND payout_id = ?i", $params['payout_id']);
}
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'] = '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']];
} else {
$condition = $params;
// FIXME
if (is_array($sorting)) {
$sorting = (is_array($sortings[$sorting['sort_by']]) ? implode(' ' . $directions[$sorting['sort_order']] . ', ', $sortings[$sorting['sort_by']]) : $sortings[$sorting['sort_by']]) . " " . $directions[$sorting['sort_order']];
}
}
if ($do_pagination) {
if (empty($page)) {
$page = 1;
}
$cnt_list_stats = db_get_field("SELECT COUNT(*) FROM ?:aff_partner_actions as actions WHERE {$condition}");
$limit = fn_paginate($page, $cnt_list_stats);
} else {
$limit = '';
}
$actions = db_get_hash_array("SELECT actions.*, alinks.object_data as parent_action_id, customers.firstname as customer_firstname, customers.lastname as customer_lastname, partners.firstname as partner_firstname, partners.lastname as partner_lastname, ?:common_descriptions.object as plan, ?:common_descriptions.description as plan_description, ?:aff_banners.type as banner_type, ?:aff_banner_descriptions.title as banner FROM ?:aff_partner_actions as actions LEFT JOIN ?:users as customers ON customers.user_id = actions.customer_id LEFT JOIN ?:users as partners ON partners.user_id = actions.partner_id LEFT JOIN ?:common_descriptions ON ?:common_descriptions.object_holder = 'affiliate_plans' AND ?:common_descriptions.object_id = actions.plan_id AND ?:common_descriptions.lang_code = ?s LEFT JOIN ?:aff_banners ON ?:aff_banners.banner_id = actions.banner_id LEFT JOIN ?:aff_banner_descriptions ON ?:aff_banner_descriptions.banner_id = actions.banner_id AND ?:aff_banner_descriptions.lang_code = ?s LEFT JOIN ?:aff_action_links as alinks ON alinks.action_id = actions.action_id AND alinks.object_type = 'A' WHERE ?p ORDER BY {$sorting} {$limit}", 'action_id', $lang_code, $lang_code, $condition);
if (!empty($actions)) {
$extra_data = array();
foreach ($actions as $action_id => $action_data) {
$action_data['data'] = db_get_hash_single_array("SELECT object_data, object_type FROM ?:aff_action_links WHERE action_id = ?i", array('object_type', 'object_data'), $action_id);
$actions[$action_id]['data'] = $action_data['data'];
if (!empty($action_data['data'])) {
if (!empty($actions[$action_id]['data']['P'])) {
$actions[$action_id]['data']['product_name'] = fn_get_product_name($actions[$action_id]['data']['P'], $lang_code);
}
if (!empty($actions[$action_id]['data']['O'])) {
$tmp_order = fn_get_order_info($actions[$action_id]['data']['O']);
$actions[$action_id]['data']['order_status'] = $tmp_order['status'];
}
if (!empty($actions[$action_id]['data']['D'])) {
$actions[$action_id]['data']['coupon'] = fn_get_promotion_data($actions[$action_id]['data']['D']);
}
} else {
$actions[$action_id]['data'] = array();
}
if (!empty($action_data['parent_action_id']) && empty($extra_data[$action_data['parent_action_id']])) {
$extra_data[$action_data['parent_action_id']] = db_get_hash_array("SELECT actions.action_id, actions.action, actions.amount, actions.partner_id, alinks.object_data as tier, partners.firstname, partners.lastname FROM ?:aff_partner_actions as actions LEFT JOIN ?:aff_action_links as alinks ON alinks.action_id = actions.action_id AND alinks.object_type = 'L' LEFT JOIN ?:users as partners ON partners.user_id = actions.partner_id WHERE actions.action_id = ?i OR actions.action_id IN(SELECT links.action_id FROM ?:aff_action_links as links WHERE links.object_type = 'A' AND links.object_data = ?i) ORDER BY actions.action_id ASC", 'action_id', $action_data['parent_action_id'], $action_data['parent_action_id']);
if (!empty($extra_data[$action_data['parent_action_id']])) {
foreach ($extra_data[$action_data['parent_action_id']] as $related_id => $related_data) {
$extra_data[$action_data['parent_action_id']][$related_id]['title'] = empty($payout_types[$related_data['action']]['title']) ? '' : fn_get_lang_var($payout_types[$related_data['action']]['title'], $lang_code);
}
}
}
if (!empty($extra_data[$action_data['parent_action_id']])) {
$actions[$action_id]['extra_data'] = $extra_data[$action_data['parent_action_id']];
}
$actions[$action_id]['title'] = empty($payout_types[$action_data['action']]['title']) ? '' : fn_get_lang_var($payout_types[$action_data['action']]['title'], $lang_code);
if (!empty($actions[$action_id]['data']['L']) && !empty($actions[$action_id]['title'])) {
$_level = strval($actions[$action_id]['data']['L']);
$actions[$action_id]['title'] .= ' (' . $_level . ' ' . fn_get_lang_var('tier_account', $lang_code) . ')';
}
}
}
return empty($actions) ? false : $actions;
}
if (AREA == 'A') {
// FIXME: What do we need it for? Replace with get_user_info possibly
$params = array('user_id' => $user_id, 'exclude_user_types' => array('A', 'S'));
list($users) = fn_get_users($params, $auth, Registry::get('settings.Appearance.admin_elements_per_page'));
$view->assign('users', $users);
}
$sortings = array('timestamp' => 'timestamp', 'amount' => 'amount');
$directions = array('asc' => 'asc', 'desc' => 'desc');
$sort_order = empty($_REQUEST['sort_order']) ? '' : $_REQUEST['sort_order'];
$sort_by = empty($_REQUEST['sort_by']) ? '' : $_REQUEST['sort_by'];
if (empty($sort_order) || !isset($directions[$sort_order])) {
$sort_order = 'desc';
}
if (empty($sort_by) || !isset($sortings[$sort_by])) {
$sort_by = 'timestamp';
}
$log_count = db_get_field("SELECT COUNT(change_id) FROM ?:reward_point_changes WHERE user_id = ?i", $user_id);
$limit = fn_paginate(@$_REQUEST['page'], $log_count, Registry::get('addons.reward_points.log_per_page'));
// FIXME
$userlog = db_get_array("SELECT change_id, action, timestamp, amount, reason FROM ?:reward_point_changes WHERE user_id = ?i ORDER BY {$sort_by} {$sort_order} {$limit}", $user_id);
$view->assign('sort_order', $sort_order == 'asc' ? 'desc' : 'asc');
$view->assign('sort_by', $sort_by);
$view->assign('userlog', $userlog);
} else {
if (empty($auth['user_id'])) {
return array(CONTROLLER_STATUS_REDIRECT, "auth.login_form?return_url=" . urlencode(Registry::get('config.current_url')));
} else {
return array(CONTROLLER_STATUS_NO_PAGE);
}
}
}
$group_date = 'timestamp';
$_period = in_array($params['period'], array('D', 'LD', 'HH')) ? STAT_PERIOD_HOUR : STAT_PERIOD_DAY;
if ($_period == STAT_PERIOD_HOUR) {
$group_date = "DATE_FORMAT(FROM_UNIXTIME(?:stat_sessions.timestamp), '%Y-%m-%d %H:00')";
} elseif ($_period == STAT_PERIOD_MONTH) {
$group_date = "DATE_FORMAT(FROM_UNIXTIME(?:stat_sessions.timestamp), '%Y-%m-01')";
} else {
$_period = STAT_PERIOD_DAY;
$group_date = "DATE_FORMAT(FROM_UNIXTIME(?:stat_sessions.timestamp), '%Y-%m-%d')";
// Day
}
if (!empty($where)) {
$where = "WHERE {$where}";
}
$total = db_get_field("SELECT COUNT(DISTINCT {$group_date}) FROM ?:stat_sessions {$filter_condition} {$where}");
$limit = fn_paginate($page, $total, $params['limit'], 'general_pagination_content');
$data = array();
$_data = db_get_hash_array("SELECT {$group_date} as date, COUNT(*) as total FROM ?:stat_sessions {$filter_condition} {$where} GROUP BY date ORDER BY date DESC {$limit}", 'date');
$_where = (empty($where) ? 'WHERE ' : $where . ' AND ') . "client_type = 'U'";
$__data = db_get_hash_array("SELECT {$group_date} as date, COUNT(*) as visitors, COUNT(DISTINCT host_ip) as hosts FROM ?:stat_sessions {$filter_condition} {$_where} GROUP BY date ORDER BY date DESC {$limit}", 'date');
foreach ($_data as $_k => $_v) {
$_data[$_k]['visitors'] = empty($__data[$_k]['visitors']) ? '0' : $__data[$_k]['visitors'];
$_data[$_k]['hosts'] = empty($__data[$_k]['hosts']) ? '0' : $__data[$_k]['hosts'];
$_data[$_k]['robots'] = !empty($_data[$_k]['total']) ? $_data[$_k]['total'] - $_data[$_k]['visitors'] : 0;
$_data[$_k]['time_from'] = strtotime($_k);
}
$data['data'] = $_data;
if ($chart_type != 'table') {
$view->assign('chart_data', fn_stat_map_data($chart_type, $data['data'], array('total', 'visitors', 'hosts', 'robots'), array(fn_get_lang_var('total'), fn_get_lang_var('visits'), fn_get_lang_var('hosts'), fn_get_lang_var('robots')), true));
}
$view->assign('statistic_period', $_period);
function fn_get_groups($links_to = array('C', 'P', 'U'), $is_avail = false, $page = 1, $lang_code = CART_LANGUAGE)
{
$condition = '';
if (!empty($links_to)) {
$condition .= db_quote(" AND ?:aff_groups.link_to IN (?a)", $links_to);
}
if (!empty($is_avail)) {
$condition .= " AND ?:status = 'A'";
}
$limit = '';
if (!empty($page)) {
$total_items = db_get_field("SELECT COUNT(*) FROM ?:aff_groups LEFT JOIN ?:aff_group_descriptions ON ?:aff_group_descriptions.group_id = ?:aff_groups.group_id AND ?:aff_group_descriptions.lang_code = ?s WHERE 1 ?p ORDER BY ?:aff_group_descriptions.name", $lang_code, $condition);
if (!empty($total_items)) {
$items_per_page = AREA == 'A' ? Registry::get('settings.Appearance.admin_elements_per_page') : Registry::get('settings.Appearance.elements_per_page');
$limit = fn_paginate($page, $total_items, $items_per_page);
}
}
$groups = db_get_hash_array("SELECT * FROM ?:aff_groups LEFT JOIN ?:aff_group_descriptions ON ?:aff_group_descriptions.group_id = ?:aff_groups.group_id AND ?:aff_group_descriptions.lang_code = ?s WHERE 1 ?p ORDER BY ?:aff_group_descriptions.name {$limit}", 'group_id', $lang_code, $condition);
if (!empty($groups) && is_array($groups)) {
foreach ($groups as $group_id => $group_data) {
$groups[$group_id] = fn_convert_group_data($group_data);
}
}
return !empty($groups) ? $groups : false;
}
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);
}
