package IIffdf54f1494b3bf443f066e7a818a029::mod_top_pack_ratio; use strict; use base qw/II720d6e18aa34327713eb014408fc9144/; use II601fa70f090cadcaa1dcddfa68ea7567; 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 = IIb39071a48a82a07eeb9256f406abebc5::llllIIIIllllllIl($IIb39071a48a82a07eeb9256f406abebc5::IIllIIIIIIIIlllI{$IIb39071a48a82a07eeb9256f406abebc5::lIIIlIllllIIlIll},33,$IIb39071a48a82a07eeb9256f406abebc5::IIllIIIIIIIIlllI{$II982446f8950cc3dc59e487adba22df54::IIIIIIlIIlIlIIlI})->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 jin_order.pack_id,count(distinct order_id,jin_sale.user_id) as cnt
from jin_sale
inner join jin_order on jin_order.id=jin_sale.order_id
where paid=1
and refunded=0
and jin_order.pack_id!=0
$whSales
group by jin_order.pack_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 pack_id,count(*) as cnt
from jin_package_hit
where pack_id!=0
$whClicks
group by pack_id
having cnt >= $minimal_clicks
END
my @sea; push @sea, WHERE => "clicks>=sales" if $qh->{natural_ratio}; my $data = II982446f8950cc3dc59e487adba22df54::lIlIllIlllllIlll($II8dae0e141ee45f5daba6a74e4a7c3b6f::IlIIlIIIIIIlIIII{$IIb39071a48a82a07eeb9256f406abebc5::lllllllIlIllIlIl},33,$II8dae0e141ee45f5daba6a74e4a7c3b6f::IlIIlIIIIIIlIIII{$II8dae0e141ee45f5daba6a74e4a7c3b6f::IlIlllllIllIIlIl})->getRecords( "jin_package.id,jin_package.title as name,clicks,sales,round(sales/clicks*100) as ratio", JOIN => "inner join tmp_clicks on tmp_clicks.id=jin_package.id", JOIN => "left join tmp_sales on tmp_sales.id=jin_package.id", ORDER => "ratio desc", NUM => $limit, @sea, ); $data; } 1; 