}
$statistic_search_data['amount_from'] = empty($statistic_search_data['amount_from']) ? 0 : floatval($statistic_search_data['amount_from']);
if (!empty($statistic_search_data['amount_from'])) {
$statistic_conditions .= db_quote(" AND (amount >= ?d) ", fn_convert_price($statistic_search_data['amount_from']));
}
$statistic_search_data['amount_to'] = empty($statistic_search_data['amount_to']) ? 0 : floatval($statistic_search_data['amount_to']);
if (!empty($statistic_search_data['amount_to'])) {
$statistic_conditions .= db_quote(" AND (amount <= ?d) ", fn_convert_price($statistic_search_data['amount_to']));
}
$view->assign('statistic_search', $statistic_search_data);
$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 {$statistic_conditions} GROUP BY action", 'action');
$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 {$statistic_conditions}");
$view->assign('general_stats', $general_stats);
$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']) . ')');
$view->assign('additional_stats', $additional_stats);
$list_plans = fn_get_affiliate_plans_list();
$view->assign('list_plans', $list_plans);
$view->assign('affiliate_plan', fn_get_affiliate_plan_data_by_partner_id($auth['user_id']));
$sort_order = empty($_REQUEST['sort_order']) ? 'desc' : $_REQUEST['sort_order'];
$sort_by = empty($_REQUEST['sort_by']) ? 'date' : $_REQUEST['sort_by'];
$list_stats = fn_get_affiliate_actions($_SESSION['statistic_conditions'], array('sort_order' => $sort_order, 'sort_by' => $sort_by), true, @$_REQUEST['page']);
$view->assign('sort_order', $sort_order == 'asc' ? 'desc' : 'asc');
$view->assign('sort_by', $sort_by);
if (!empty($list_stats)) {
$view->assign('list_stats', $list_stats);
}
$order_status_descr = fn_get_statuses(STATUSES_ORDER, true, true, true);
$view->assign('order_status_descr', $order_status_descr);
}
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);
}
// FIXME: page
$payouts = db_get_hash_array("SELECT ?:affiliate_payouts.*, ?:users.user_login, ?:users.firstname, ?:users.lastname FROM ?:affiliate_payouts LEFT JOIN ?:users ON ?:affiliate_payouts.partner_id=?:users.user_id WHERE ?p AND ?:users.user_id = ?i ORDER BY {$sorting} {$limit}", 'payout_id', $payout_search_condition, $auth['user_id']);
$view->assign('payouts', $payouts);
} elseif ($mode == 'update') {
if (empty($_REQUEST['payout_id'])) {
return array(CONTROLLER_STATUS_NO_PAGE);
} else {
$payout_data = db_get_row("SELECT * FROM ?:affiliate_payouts WHERE payout_id = ?i", $_REQUEST['payout_id']);
if (empty($payout_data)) {
return array(CONTROLLER_STATUS_NO_PAGE);
} else {
if (!empty($payout_data['partner_id'])) {
$payout_data['partner'] = fn_get_partner_data($payout_data['partner_id']);
}
if (!empty($payout_data['partner']['plan_id'])) {
$payout_data['plan'] = fn_get_affiliate_plan_data($payout_data['partner']['plan_id']);
}
$payout_data['actions'] = fn_get_affiliate_actions($_REQUEST);
$payout_data['date_range']['min'] = db_get_field("SELECT MIN(date) FROM ?:aff_partner_actions WHERE payout_id = ?i", $_REQUEST['payout_id']);
$payout_data['date_range']['max'] = db_get_field("SELECT MAX(date) FROM ?:aff_partner_actions WHERE payout_id = ?i", $_REQUEST['payout_id']);
// [Breadcrumbs]
fn_add_breadcrumb(fn_get_lang_var('payouts'), "payouts.manage");
// [/Breadcrumbs]
$view->assign('affiliate_plan', fn_get_affiliate_plan_data_by_partner_id($auth['user_id']));
$view->assign('payouts', array($payout_data['partner_id'] => $payout_data));
}
$view->assign('sort_order', empty($sort_order) || $sort_order == 'asc' ? 'desc' : 'asc');
$view->assign('sort_by', empty($sort_by) ? 'date' : $sort_by);
}
}
/** /Body **/
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);
}
