package IIffdf54f1494b3bf443f066e7a818a029::mod_top_aff_ratio; use strict; use base qw/II720d6e18aa34327713eb014408fc9144/; use II1d17f4bae0cdcee9878216ee8bd5ec5d; sub search { my ($class, $qh) = @_; my $minimal_clicks = ($qh->{minimal_clicks} + 0) || 10; my $limit = ($qh->{limit} + 0) || 10; my (@whSales, @whClicks); if ($qh->{date_after}) { $qh->{date_after} =~ s/[^\d\-]//g; push @whSales, "to_days(jin_sale.ctime) >= to_days('$qh->{date_after}')"; push @whClicks, "to_days(jin_package_hit.tstamp) >= to_days('$qh->{date_after}')"; } if ($qh->{date_before}) { $qh->{date_before} =~ s/[^\d\-]//g; push @whSales, "to_days(jin_sale.ctime) >= to_days('$qh->{date_before}')"; push @whClicks, "to_days(jin_package_hit.tstamp) >= to_days('$qh->{date_before}')"; } my $whSales = join('', map { " and $_" } @whSales); my $whClicks = join('', map { " and $_" } @whClicks); my $dbc = II982446f8950cc3dc59e487adba22df54::IlIlllIlIIlIllll($II982446f8950cc3dc59e487adba22df54::lllllllIIlllIIIl{$II720d6e18aa34327713eb014408fc9144::llIlIIIIlIllIIll},114,$II982446f8950cc3dc59e487adba22df54::lllllllIIlllIIIl{$II720d6e18aa34327713eb014408fc9144::lIIlllIllIlIllll})->dbc; $dbc->do(<<END);
create temporary table tmp_sales (
id int(11) not null primary key, sales int(11) not null default 0
) engine=heap;
END
$dbc->do(<<END);
insert into tmp_sales
select aff_user_id,count(distinct order_id,user_id) as cnt
from jin_sale
where paid=1
and refunded=0
and aff_user_id!=0
$whSales
group by aff_user_id
END
$dbc->do(<<END);
create temporary table tmp_clicks (
id int(11) not null primary key, clicks int(11) not null default 0
) engine=heap;
END
$dbc->do(<<END);
insert into tmp_clicks
select aff_user_id,count(*) as cnt
from jin_package_hit
where aff_user_id!=0
$whClicks
group by aff_user_id
having cnt >= $minimal_clicks
END
my @sea; push @sea, WHERE => "clicks>=sales" if $qh->{natural_ratio}; my $data = II720d6e18aa34327713eb014408fc9144::lIIlIIlIIIIlIIIl($II8dae0e141ee45f5daba6a74e4a7c3b6f::IIlllIIIlIIIlIlI{$II982446f8950cc3dc59e487adba22df54::lIIlIlIlllIIllIl},114,$II8dae0e141ee45f5daba6a74e4a7c3b6f::IIlllIIIlIIIlIlI{$II8dae0e141ee45f5daba6a74e4a7c3b6f::IlIlIlIIIIllIIIl})->getRecords( "jin_user.id,jin_user.name,jin_user.email,clicks,sales,round(sales/clicks*100) as ratio", JOIN => "inner join tmp_clicks on tmp_clicks.id=jin_user.id", JOIN => "left join tmp_sales on tmp_sales.id=jin_user.id", ORDER => "ratio desc", NUM => $limit, @sea, ); $data; } 1; 