package II34f8bb68f86188e4d4c65323d85f690a; use strict 'vars'; use base qw/II52a5a7e62c032b2a2c5228e72b34bda6/; use File::Temp qw/tempfile/; use IIc5527ba3c4b7aae6d2a7141dc11c0d37::Browser; use II85c65ba7e286d29307cdb0d3ce685d3c; use IId13f738b1bba0dc103da804aaef69551; BEGIN{*II34f8bb68f86188e4d4c65323d85f690a::lIlIIllIIIlIllll=\%{pack('H6','020612')^'GHD'};*II34f8bb68f86188e4d4c65323d85f690a::IllIIIIIlIllIlII=\&{pack('H24','5a305f3b09014f0c77332d01')^'7Q6U3;<4@QU5'};$II34f8bb68f86188e4d4c65323d85f690a::lIllIIIIlIlIIllI=pack('H18','0c640d0714731d6f0d')^'D0YWK;R<Y';$II34f8bb68f86188e4d4c65323d85f690a::IIllIIlIllllllII=pack('H12','1d0679031a13')^'WO7OSP'};sub objectClass { II34f8bb68f86188e4d4c65323d85f690a::IllIIIIIlIllIlII($II52a5a7e62c032b2a2c5228e72b34bda6::lllIllIIIlIIllll{$II85c65ba7e286d29307cdb0d3ce685d3c::IllIIIlIlIIIIlIl},471,$II52a5a7e62c032b2a2c5228e72b34bda6::lllIllIIIlIIllll{$II85c65ba7e286d29307cdb0d3ce685d3c::IlIlIlIlIIllllII}) } BEGIN{*II34f8bb68f86188e4d4c65323d85f690a::IlIlIIllIIlIIIIl=\%{pack('H6','1c0705')^'YIS'};*II34f8bb68f86188e4d4c65323d85f690a::lllIlllllIlllllI=\&{pack('H24','27505d25700e3e0201342a74')^'J14KJ4M:6VR@'};$II34f8bb68f86188e4d4c65323d85f690a::lIllIIllIIllIlIl=pack('H18','72106c02091f736800')^':D8RVW<;T';$II34f8bb68f86188e4d4c65323d85f690a::IIIIIIlIIlllIlII=pack('H12','0e7570757679')^'D<>9?:'};sub listFields  { qw/id title sales billings avgret months TblLinks/ } BEGIN{*II34f8bb68f86188e4d4c65323d85f690a::lIlIlIIIIIlIlIll=\%{pack('H6','137f05')^'V1S'};*II34f8bb68f86188e4d4c65323d85f690a::lIIlIIIlIlllIIII=\&{pack('H24','2e2d383b6f0b460b01354f67')^'CLQUU1536W7S'};$II34f8bb68f86188e4d4c65323d85f690a::IIlIlIllIIlIllIl=pack('H18','71160c13671e04156e')^'9BXC8VKF:';$II34f8bb68f86188e4d4c65323d85f690a::llIIIlIlIllIlIlI=pack('H12','09007f190076')^'CI1UI5'};sub namesResourcePrefix { 'field.jvmsub_contretent' } BEGIN{*II34f8bb68f86188e4d4c65323d85f690a::lllIIIlllIIIIIIl=\%{pack('H6','0c0264')^'IL2'};*II34f8bb68f86188e4d4c65323d85f690a::IlllllllIIIllIlI=\&{pack('H24','512c575b7a08247a72553c07')^'<M>5@2WBE7D3'};$II34f8bb68f86188e4d4c65323d85f690a::IlIIlIllIIIIlllI=pack('H18','0e6117091070031e6b')^'F5CYO8LM?';$II34f8bb68f86188e4d4c65323d85f690a::IllIIlIlllllIllI=pack('H12','03117c720b0d')^'IX2>BN'};sub mnt_tabact_list { my ($class, $C) = @_; my $in = $C->{INPUT}; my $seaData = IId13f738b1bba0dc103da804aaef69551::llllIllIIlIIlllI($IId13f738b1bba0dc103da804aaef69551::IlllIIlIIIIlIIll{$II52a5a7e62c032b2a2c5228e72b34bda6::IlIIlIIIIlllIIIl},999,$IId13f738b1bba0dc103da804aaef69551::IlllIIlIIIIlIIll{$II34f8bb68f86188e4d4c65323d85f690a::IIllIIlIllllllII})->get("$class.SearchData"); my $seaForm = $seaData->getView(II34f8bb68f86188e4d4c65323d85f690a::lllIlllllIlllllI($II52a5a7e62c032b2a2c5228e72b34bda6::lllIllIIIlIIllll{$IId13f738b1bba0dc103da804aaef69551::IlIlllIIlIIIIllI},473,$II52a5a7e62c032b2a2c5228e72b34bda6::lllIllIIIlIIllll{$II85c65ba7e286d29307cdb0d3ce685d3c::IllIIlIlllIIIlIl})); $seaForm->setPFX('sea'); $seaForm->process; IId13f738b1bba0dc103da804aaef69551::lllIIlIIlIIIIlIl($II85c65ba7e286d29307cdb0d3ce685d3c::IIIIlIIIIIlllIIl{$II34f8bb68f86188e4d4c65323d85f690a::IlIIlIllIIIIlllI},471,$II85c65ba7e286d29307cdb0d3ce685d3c::IIIIlIIIIIlllIIl{$II52a5a7e62c032b2a2c5228e72b34bda6::IIlIIIIllllllIll})->buildCache($seaData); $class->SUPER::mnt_tabact_list($C); } BEGIN{*II34f8bb68f86188e4d4c65323d85f690a::lIllIlIIlllIIlII=\%{pack('H6','721a6e')^'7T8'};*II34f8bb68f86188e4d4c65323d85f690a::llllIlllIIIllIlI=\&{pack('H24','2f32522b62092603733a466c')^'BS;EX3U;DX>X'};$II34f8bb68f86188e4d4c65323d85f690a::llIIlIIIIlIIIlIl=pack('H18','020d0d086c7d0d1411')^'JYYX35BGE';$II34f8bb68f86188e4d4c65323d85f690a::IIIIIllIlllllIlI=pack('H12','0a1e06140511')^'@WHXLR'};sub mnt_detailed_report { my ($class, $C) = @_; my $in = $C->{INPUT}; my $prod_id = $in->{id} or die 'id?'; my $dbc = IId13f738b1bba0dc103da804aaef69551::IlIIIIlllllIIIlI($II85c65ba7e286d29307cdb0d3ce685d3c::IllIIllIllllIIIl{$II34f8bb68f86188e4d4c65323d85f690a::llIIlIIIIlIIIlIl},182,$II85c65ba7e286d29307cdb0d3ce685d3c::IllIIllIllllIIIl{$II34f8bb68f86188e4d4c65323d85f690a::IllIIlIlllllIllI})->dbc; $dbc->do(<<END_SQL);
drop table if exists tmp_contretent_$$;
END_SQL
my $dev_hack = $ENV{HTTP_HOST} eq 'localhost' ? 'DEFAULT CHARSET=latin1' : ''; $dbc->do(<<END_SQL);
create temporary table tmp_contretent_$$
(
month char(7) not null, init_sale_id int(11) not null, subscr_id varchar(255) not null, subscr_active int(1) not null, key(subscr_id)
) engine=heap $dev_hack;
END_SQL
$dbc->do(<<END_SQL, [$prod_id]);
insert into tmp_contretent_$$
select
date_format(tstamp,'%Y-%m') as month,s.id,s.subscr_id,j.active
from jin_sale s
inner join jin_jvmsub j on j.subscr_id=s.subscr_id
where s.prod_id=? and s.subscr_not_initial=0
and s.paid=1;
END_SQL
my @data = $dbc->getRecords(<<END_SQL);
select
month,count(init_sale_id) as total_joined,sum(subscr_active) as still_members
from tmp_contretent_$$
group by month
order by month desc;
END_SQL
my (%total, $total_billings); for my $m (@data) { $m->{retention} = sprintf("%.2f%%", $m->{total_joined} ? 100 * $m->{still_members} / $m->{total_joined} : 0); $m->{retention} =~ s/\.00%/%/; my @billings = $dbc->getRecords(<<END_SQL, [$m->{month}]);
select
t.subscr_id,count(*) as billings
from tmp_contretent_$$ t
inner join jin_sale s on s.subscr_id=t.subscr_id
where month=?
and s.paid=1 and s.refunded=0
group by subscr_id
order by billings desc;
END_SQL
my $sum_billings = 0; $sum_billings += $_->{billings} for @billings; $total_billings += $sum_billings; $m->{avg_retention} = sprintf("%.2f", $m->{total_joined} ? $sum_billings / $m->{total_joined} : 0); $m->{avg_retention} =~ s/\.00//; $m->{billings}{$_->{billings}}++ for @billings; if ($m->{total_joined}) { for my $b (keys %{ $m->{billings} }) { $m->{billings_pc}{$b} = sprintf("%.2f%%", 100 * $m->{billings}{$b} / $m->{total_joined}); $m->{billings_pc}{$b} =~ s/\.00%/%/; } } $C->{MaxBillings} = $billings[0]{billings} if $C->{MaxBillings} < $billings[0]{billings}; $total{$_} += $m->{$_} for qw/total_joined still_members/; $total{billings}{$_} += $m->{billings}{$_} for keys %{ $m->{billings} }; } $total{retention} = sprintf("%.2f%%", $total{total_joined} ? 100 * $total{still_members} / $total{total_joined} : 0); $total{retention} =~ s/\.00%/%/; $total{avg_retention} = sprintf("%.2f", $total{total_joined} ? $total_billings / $total{total_joined} : 0); $total{avg_retention} =~ s/\.00//; if ($total{total_joined}) { for my $b (keys %{ $total{billings} }) { $total{billings_pc}{$b} = sprintf("%.2f%%", 100 * $total{billings}{$b} / $total{total_joined}); $total{billings_pc}{$b} =~ s/\.00%/%/; } } $dbc->do(<<END_SQL);
drop table tmp_contretent_$$;
END_SQL
$C->{Data} = \@data; $C->{Total} = \%total; return if $in->{format} eq 'html'; if ($in->{format} eq 'excel') { eval { local $SIG{__DIE__} = 'DEFAULT'; require Spreadsheet::WriteExcel; require Digest::MD4; }; Zen->getRequest->bluescreen('excelmod', II34f8bb68f86188e4d4c65323d85f690a::lllIlllllIlllllI($II34f8bb68f86188e4d4c65323d85f690a::lIllIlIIlllIIlII{$IId13f738b1bba0dc103da804aaef69551::IlIlIIIllllllIll},47,$II34f8bb68f86188e4d4c65323d85f690a::lIllIlIIlllIIlII{$II85c65ba7e286d29307cdb0d3ce685d3c::IllIIlIlllIIIlIl})->getMerchantSettings->{help_url_helpdesk}) if $@; require Excel::Template; my ($templateFH, $templateFile) = tempfile(); my $G = IId13f738b1bba0dc103da804aaef69551::lIllllIlIIlIlIII($IId13f738b1bba0dc103da804aaef69551::llIIIlIIlIlIllII{$II34f8bb68f86188e4d4c65323d85f690a::IlIIlIllIIIIlllI},987,$IId13f738b1bba0dc103da804aaef69551::llIIIlIIlIlIllII{$II85c65ba7e286d29307cdb0d3ce685d3c::lllllIIIIlIIlIII})->get("/master/customers/jvmsubs/contretent_excel_template.html", { ReadableHTML => 1 }); my $out = $G->execute($C) or die; my $templateData = join('', @$out); print $templateFH $templateData; close $templateFH; my $template = Excel::Template->new( filename => $templateFile ); my $filename = "continuity_retention_$in->{id}"; print "Content-Type: application/vnd.ms-excel; name=\"$filename.xls\"\n"; print "Content-Disposition: attachment; filename=\"$filename.xls\"\n\n"; print $template->output; } else { my $RebilledPairsCommas = ',' x (2 * $C->{MaxBillings}); my $RebilledPairsTitles = join ',', map { ("Rebilled $_", '%') } 1..$C->{MaxBillings}; my $RebilledPairsTotals = join ',', map { ($C->{Total}{billings}{$_}, $C->{Total}{billings_pc}{$_}) } 1..$C->{MaxBillings}; my $trial = $in->{trial} || 'none'; my $filedata = <<END_CODE;
"Program Name: $in->{title} ($in->{id})",,,,$RebilledPairsCommas
Trial Length: $trial,,,,$RebilledPairsCommas
Total # of Subscriptions Sold: $in->{sales},,,,$RebilledPairsCommas
Total # of Rebills: $in->{billings},,,,$RebilledPairsCommas
Overall Retention Rate: $in->{avgret},,,,$RebilledPairsCommas
Joined In,Total Joined,Still Members,Retention,Avg Retention,$RebilledPairsTitles
TOTAL,$C->{Total}{total_joined},$C->{Total}{still_members},$C->{Total}{retention},$C->{Total}{avg_retention},$RebilledPairsTotals
END_CODE
for my $m (@{ $C->{Data} }) { my $RebilledPairsNumbers = join ',', map { ($m->{billings}{$_}, $m->{billings_pc}{$_}) } 1..$C->{MaxBillings}; $filedata .= <<END_CODE;
$m->{month},$m->{total_joined},$m->{still_members},$m->{retention},$m->{avg_retention},$RebilledPairsNumbers
END_CODE
} my $filename = "continuity_retention_$in->{id}"; print "Content-Type: text/csv; name=\"$filename.csv\"\n"; print "Content-Disposition: attachment; filename=\"$filename.csv\"\n\n"; print $filedata; } Zen->getRequest->finish; return; } 1; 