function display_aff_sales()
{
global $vars;
global $db, $t;
$t->assign('year_month_options', get_ym_options());
$t->assign('default_month', get_default_ym());
if ($vars['year_month'] == '') {
$vars['year_month'] = get_default_ym();
}
list($y, $m) = split('_', $vars['year_month']);
$y = intval($y);
$m = intval($m);
$m = sprintf('%02d', $m);
$dat1 = "{$y}-{$m}-01";
$dat2 = date('Y-m-t', strtotime($dat1));
$dattm1 = date('Ymd000000', strtotime($dat1));
$dattm2 = date('Ymd235959', strtotime($dat2));
$totaldays = date('t', strtotime($dat1));
$days = array();
$total = array();
for ($i = 1; $i <= $totaldays; $i++) {
$days[$i] = array('dat' => sprintf("{$y}-{$m}-%02d", $i));
}
// get clicks for the month
$q = $db->query("SELECT DAYOFMONTH(ac.time), COUNT(log_id), COUNT(DISTINCT(remote_addr))\n FROM {$db->config[prefix]}aff_clicks ac\n WHERE ac.time BETWEEN {$dattm1} AND {$dattm2}\n GROUP BY DAYOFMONTH(ac.time)\n ");
while (list($d, $r, $u) = mysql_fetch_row($q)) {
$days[$d]['raw'] = $r;
$days[$d]['uniq'] = $u;
}
// get total clicks for the month
$q = $db->query("SELECT COUNT(log_id), COUNT(DISTINCT(remote_addr))\n FROM {$db->config[prefix]}aff_clicks ac\n WHERE ac.time BETWEEN {$dattm1} AND {$dattm2}\n ");
while (list($r, $u) = mysql_fetch_row($q)) {
$total['raw'] = $r;
$total['uniq'] = $u;
}
// get comissions for the month
$q = $db->query("SELECT DAYOFMONTH(ac.date), COUNT(commission_id),\n SUM(IF(record_type='debit', amount, 0)), \n SUM(IF(record_type='credit', amount, 0))\n FROM {$db->config[prefix]}aff_commission ac\n WHERE ac.date BETWEEN '{$dat1}' AND '{$dat2}'\n GROUP BY DAYOFMONTH(ac.date)\n ");
while (list($d, $cnt, $deb, $cre) = mysql_fetch_row($q)) {
$days[$d]['trans'] = $cnt;
$days[$d]['debit'] = $deb != 0 ? -$deb : '';
//$days[$d]['debit'] = $deb;
$days[$d]['credit'] = $cre;
if ($deb || $cre) {
$days[$d]['total'] = $cre - $deb;
}
$total['trans'] += $cnt;
$total['debit'] += $deb;
$total['credit'] += $cre;
$total['total'] += $days[$d]['total'];
}
$total['debit'] = $total['debit'] != 0 ? -$total['debit'] : '';
$t->assign('days', $days);
$t->assign('total', $total);
/// top 20 referrers
$q = $db->query("SELECT referrer, COUNT(log_id) as clog_id, COUNT(DISTINCT(remote_addr)) as cremote_addr\n FROM {$db->config[prefix]}aff_clicks ac\n WHERE referrer > '' AND ac.time BETWEEN {$dattm1} AND {$dattm2}\n GROUP BY referrer\n ORDER BY clog_id DESC, cremote_addr DESC\n LIMIT 0,20\n ");
$refs = array();
while (list($ref, $raw, $uniq) = mysql_fetch_row($q)) {
$refs[] = array('raw' => $raw, 'uniq' => $uniq, 'ref' => $ref);
}
$t->assign('refs', $refs);
$t->display("admin/aff_stats.html");
}
function display_aff_sales()
{
global $member_id, $vars;
global $db, $t;
$t->assign('year_month_options', get_ym_options());
$t->assign('default_month', get_default_ym());
admin_check_permissions('affiliates');
if ($vars['year_month'] == '') {
$vars['year_month'] = get_default_ym();
}
list($y, $m) = split('_', $vars['year_month']);
$m = sprintf('%02d', $m);
$dat1 = "{$y}-{$m}-01";
$dat2 = date('Y-m-t', strtotime($dat1));
$dattm1 = date('Ymd000000', strtotime($dat1));
$dattm2 = date('Ymd235959', strtotime($dat2));
$totaldays = date('t', strtotime($dat1));
$days = array();
$total = array();
for ($i = 1; $i <= $totaldays; $i++) {
$days[$i] = array('dat' => sprintf("{$y}-{$m}-%02d", $i));
}
// get clicks for the month
$q = $db->query("SELECT DAYOFMONTH(ac.time), COUNT(log_id), COUNT(DISTINCT(remote_addr))\n FROM {$db->config[prefix]}aff_clicks ac\n WHERE aff_id={$member_id} AND ac.time BETWEEN {$dattm1} AND {$dattm2}\n GROUP BY DAYOFMONTH(ac.time)\n ");
while (list($d, $r, $u) = mysql_fetch_row($q)) {
$days[$d]['raw'] = $r;
$days[$d]['uniq'] = $u;
}
// get total clicks for the month
$q = $db->query("SELECT COUNT(log_id), COUNT(DISTINCT(remote_addr))\n FROM {$db->config[prefix]}aff_clicks ac\n WHERE aff_id={$member_id} AND ac.time BETWEEN {$dattm1} AND {$dattm2}\n ");
while (list($r, $u) = mysql_fetch_row($q)) {
$total['raw'] = $r;
$total['uniq'] = $u;
}
// get comissions for the month
$q = $db->query("SELECT DAYOFMONTH(ac.date), COUNT(commission_id),\n SUM(IF(record_type='debit', amount, 0)), \n SUM(IF(record_type='credit', amount, 0)),\n SUM(IF(record_type='debit', 1, 0))\n FROM {$db->config[prefix]}aff_commission ac\n WHERE aff_id={$member_id} AND ac.date BETWEEN '{$dat1}' AND '{$dat2}'\n GROUP BY DAYOFMONTH(ac.date)\n ");
while (list($d, $cnt, $deb, $cre, $deb_count) = mysql_fetch_row($q)) {
$days[$d]['trans'] = $cnt;
$days[$d]['debit'] = $deb != 0 ? -$deb . " ({$deb_count})" : '';
$days[$d]['credit'] = $cre;
$dat = "{$y}-{$m}-{$d}";
if ($deb || $cre) {
$rr = $db->query_all("SELECT c.amount as c_amount, c.payment_id, p.member_id, \n \t\t\tpr.title as pr_title,\n \t\t\tm.login, m.name_f, m.name_l, p.amount as p_amount, c.tier as tier, c.record_type\n \t\tFROM {$db->config[prefix]}aff_commission c \n \t\tLEFT JOIN {$db->config[prefix]}payments p USING (payment_id)\n \t\tLEFT JOIN {$db->config[prefix]}members m ON p.member_id = m.member_id\n \t\tLEFT JOIN {$db->config[prefix]}products pr ON p.product_id = pr.product_id \n \t\tWHERE c.date = '{$dat}' AND c.aff_id={$member_id} \n \t");
$days[$d]['detail'] = $rr;
}
if ($deb || $cre) {
$days[$d]['total'] = $cre - $deb;
}
$total['trans'] += $cnt;
$total['debit'] += $deb;
$total['credit'] += $cre;
$total['total'] += $days[$d]['total'];
}
$total['debit'] = $total['debit'] != 0 ? -$total['debit'] : '';
$t->assign('days', $days);
$t->assign('total', $total);
/// top 20 referrers
$q = $db->query("SELECT referrer, COUNT(log_id), COUNT(DISTINCT(remote_addr))\n FROM {$db->config[prefix]}aff_clicks ac\n WHERE aff_id={$member_id} AND referrer > '' AND ac.time BETWEEN {$dattm1} AND {$dattm2}\n GROUP BY referrer\n #ORDER BY COUNT(log_id) DESC, COUNT(DISTINCT(remote_addr)) DESC\n #LIMIT 0,20\n ");
$refs = array();
while (list($ref, $raw, $uniq) = mysql_fetch_row($q)) {
$refs[] = array('raw' => $raw, 'uniq' => $uniq, 'ref' => $ref);
}
$t->assign('u', $db->get_user($member_id));
$t->assign('refs', $refs);
$t->display("admin/user_aff_stats.html");
}
function do_stats()
{
global $db;
$vars = get_input_vars();
$t = new_smarty();
$t->assign('year_month_options', get_ym_options());
$t->assign('default_month', get_default_ym());
if ($vars['year_month'] == '') {
$vars['year_month'] = get_default_ym();
}
list($y, $m) = split('_', $vars['year_month']);
$m = sprintf('%02d', $m);
$dat1 = "{$y}-{$m}-01";
$dat2 = date('Y-m-t', strtotime($dat1));
$dattm1 = date('Ymd000000', strtotime($dat1));
$dattm2 = date('Ymd235959', strtotime($dat2));
$totaldays = date('t', strtotime($dat1));
$days = array();
$total = array();
for ($i = 1; $i <= $totaldays; $i++) {
$days[sprintf("{$y}-{$m}-%02d", $i)] = array();
}
// get clicks for the month
$q = $db->query("SELECT\n\t\tDAYOFMONTH(rl.payment_date), IFNULL(status, 'null'), COUNT(rebill_log_id) as cnt, SUM(amount) as amount\n FROM {$db->config[prefix]}rebill_log rl\n WHERE rl.payment_date BETWEEN {$dattm1} AND {$dattm2}\n GROUP BY DAYOFMONTH(rl.payment_date), IFNULL(status, 'null')\n ");
while (list($d, $status, $cnt, $amount) = mysql_fetch_row($q)) {
$dat = sprintf('%04d-%02d-%02d', $y, $m, $d);
if ($status == 'null') {
$s = 'null';
} elseif ($status == 0) {
$s = 0;
} else {
$s = 1;
}
$days[$dat][$s]['cnt'] += $cnt;
$days[$dat][$s]['amount'] += $amount;
$days[$dat]['total']['cnt'] += $cnt;
$days[$dat]['total']['amount'] += $amount;
}
$t->assign('list', $days);
$t->display('admin/rebill_stats.html');
}
}
return $res;
}
function get_default_ym()
{
$y = date('Y');
$m = date('m');
$m -= 2;
if ($m <= 0) {
$y--;
$m = 12 - $m;
}
return "{$y}_{$m}";
}
/*******************************************/
$t->assign('year_month_options', get_ym_options());
$t->assign('default_month', get_default_ym());
$t->assign('payout_methods', aff_get_payout_methods() + array('' => 'Not selected', 'ALL' => 'All payouts (for reporting)'));
if ($vars['action'] == 'aff_payout_export') {
$rand = $vars['payout_sess_id'];
$rows = array();
foreach ($_SESSION['_amember_aff_commission'][$rand]['rows'] as $member_id => $to_pay) {
$r = $db->get_user($member_id);
$r['to_pay'] = $to_pay;
$rows[] = $r;
}
$dat1 = $_SESSION['_amember_aff_commission'][$rand]['dat1'];
$dat2 = $_SESSION['_amember_aff_commission'][$rand]['dat2'];
$payout_method = $_SESSION['_amember_aff_commission'][$rand]['payout_method'];
$func_name = 'aff_pay_commission_' . $payout_method;
$func_name($dat1, $dat2, $payout_method, $rows);