} $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); }