& elements/search.html,
'title' => emt('Sales with advertising source'),
'name' => emt('line items'),
'query' => $query,
'count_query' => $count_query,
'count_addl' => [
($setup ? $money_char. '%.2f setup' : ()),
($recur ? $money_char. '%.2f recurring' : ()),
($usage ? $money_char. '%.2f usage' : ()),
],
'header' => [
emt('Description'),
($setup ? emt('Setup') : ()),
($recur ? emt('Recurring') : ()),
($usage ? emt('Usage') : ()),
emt('Invoice'),
emt('Invoice date'),
emt('Paid'),
emt('Payment date'),
emt('Pkg. status'),
emt('Pkg. class'),
'', #report class
emt('Cust#'),
emt('Customer'),
emt('Ad source'),
emt('Agent'),
],
'fields' => [
'pkg',
($setup ? money_sub('setup') : ()),
($recur ? money_sub('recur_no_usage') : ()),
($usage ? money_sub('recur_usage') : ()),
'invnum',
date_sub('_date'),
money_sub('paid'),
date_sub('last_pay'),
sub {
my $cust_pkg = shift->cust_pkg;
$cust_pkg ? ucfirst($cust_pkg->status) : '';
},
'classname',
sub { # report_option
my $cust_bill_pkg = shift;
my $pkgpart = $cust_bill_pkg->pkgpart_override;
unless ( $pkgpart ) {
my $cust_pkg = $cust_bill_pkg->cust_pkg or return '';
$pkgpart = $cust_pkg->pkgpart;
}
if ( !exists($report_classes{$pkgpart}) ) {
my $part_pkg = FS::part_pkg->by_key($pkgpart);
my %opts = $part_pkg->options;
$report_classes{$pkgpart} = [
map { /^report_option_(\d+)/ ?
$report_option_name{$1} :
() }
keys %opts
];
}
join( '
', @{ $report_classes{$pkgpart} });
},
'custnum',
'name',
'referral', # from query
'agent',
],
'sort_fields' => [
'',
($setup ? 'setup' : ()),
($recur ? 'recur_no_usage' : ()),
($usage ? 'recur_usage' : ()),
'invnum',
'_date',
'paid',
'last_pay',
'', #package status
'classname',
'', #report_option
'custnum',
'',
'referral',
'agent',
],
'links' => [
'', #package/item desc
('') x $x, #setup/recur/usage
$ilink, #invnum
$ilink, #invoice date
'', #paid amt
'', #payment date
'', #pkg status
'', #classnum
'', #report class
$clink, #custnum
$clink, #customer name
'', #referral
'', #agent
],
#'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
'align' => 'l' . ('r' x $x) . 'rcrccccrlll',
'color' => [ ('') x (5 + $x),
sub {
my $cust_pkg = shift->cust_pkg;
$cust_pkg ? $cust_pkg->statuscolor : '';
},
('') x 6,
],
'style' => [
('') x (5 + $x),
'b',
('') x 6
],
&>
<%init>
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
my $conf = new FS::Conf;
my $setup = $cgi->param('setup') ? 1 : 0;
my $recur = $cgi->param('recur') ? 1 : 0;
my $usage = $cgi->param('usage') ? 1 : 0;
my $x = $setup + $recur + $usage;
my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
my ($join_cust, $join_pkg ) = ('', '');
#here is the agent virtualization
my $agentnums_sql =
$FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
my @where = ( $agentnums_sql,
# exclude taxes
'(cust_bill_pkg.pkgnum != 0 OR '.
'cust_bill_pkg.feepart IS NOT NULL)',
"cust_bill._date >= $beginning",
"cust_bill._date <= $ending",
);
my @status_where;
foreach my $status ($cgi->param('status')) {
if ( $status =~ /^([- a-z]+)$/ ) { #"one-time charge"
push @status_where, "'$status'";
}
}
if ( @status_where ) {
push @where, '('. FS::cust_pkg->status_sql.
') IN (' . join(',', @status_where) .')';
}
my @refnum;
foreach my $refnum ($cgi->param('refnum')) {
if ( $refnum =~ /^\d+$/ ) {
push @refnum, $refnum;
}
}
if ( @refnum ) {
push @where, 'cust_main.refnum IN ('.join(',', @refnum).')';
}
# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql)
if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
join(',', map { $_ || '0' } @classnums ).
' )'
if @classnums;
}
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
push @where, "cust_main.agentnum = $1";
}
#classnum
# not specified: all classes
# 0: empty class
# N: classnum
my $use_override = 1; #$cgi->param('use_override');
if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
my $comparison = '';
if ( $1 == 0 ) {
$comparison = "IS NULL";
} else {
$comparison = "= $1";
}
if ( $use_override ) {
push @where, "(
(part_pkg.classnum $comparison AND pkgpart_override IS NULL) OR
(override.classnum $comparison AND pkgpart_override IS NOT NULL) OR
(part_fee.classnum $comparison AND feepart IS NOT NULL)
)";
} else {
push @where,
"(part_pkg.classnum $comparison) OR (part_fee.classnum $comparison)";
}
}
# report option
my @report_option = grep /^\d+$/, ( $cgi->param('report_option') );
if ( @report_option ) {
@report_option = map { "'report_option_$_'" } @report_option;
push @where, "EXISTS(
SELECT 1 FROM part_pkg_option WHERE optionname IN (".
join(',', @report_option).") AND (
part_pkg_option.pkgpart = cust_pkg.pkgpart AND pkgpart_override IS NULL
OR part_pkg_option.pkgpart = pkgpart_override
)
)";
}
my $setup_sql =
FS::cust_bill_pkg->charged_sql('', '', setuprecur => 'setup');
my $recur_sql =
FS::cust_bill_pkg->charged_sql('', '', setuprecur => 'recur', no_usage => 1);
my $usage_sql = FS::cust_bill_pkg->usage_sql;
# exclude zero-amount items
my @orwhere;
push @orwhere, "(cust_bill_pkg.setup > 0)" if $setup;
push @orwhere, "($recur_sql > 0)" if $recur;
push @orwhere, "($usage_sql > 0)" if $usage;
push @where, '('.join(' OR ', @orwhere).')' if @orwhere;
$join_cust = ' JOIN cust_bill USING ( invnum )
LEFT JOIN cust_main USING ( custnum )
LEFT JOIN part_referral USING ( refnum )
LEFT JOIN agent ON cust_main.agentnum = agent.agentnum
';
$join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum )
LEFT JOIN part_pkg USING ( pkgpart )
LEFT JOIN part_pkg AS override
ON pkgpart_override = override.pkgpart
LEFT JOIN part_fee USING ( feepart )
LEFT JOIN pkg_class ON '; #...
if ( $use_override ) {
# join to whichever pkgpart is appropriate
$join_pkg .= '
( pkgpart_override IS NULL AND part_pkg.classnum = pkg_class.classnum )
OR ( pkgpart_override IS NOT NULL AND override.classnum = pkg_class.classnum )
OR ( feepart IS NOT NULL AND part_fee.classnum = pkg_class.classnum )';
} else {
$join_pkg .= '
( part_pkg.classnum = pkg_class.classnum )
OR ( part_fee.classnum = pkg_class.classnum )';
}
my $where = ' WHERE '. join(' AND ', @where);
# setup and recurring only
my $count_query = "SELECT
COUNT(billpkgnum)".
($setup ? ", SUM($setup_sql)" : '').
($recur ? ", SUM($recur_sql)" : '').
($usage ? ", SUM($usage_sql)" : '').
" FROM cust_bill_pkg
$join_cust
$join_pkg
$where
";
my $paid_sql = FS::cust_bill_pkg->paid_sql('', '');
my $last_pay_sql = "SELECT MAX(_date)
FROM cust_bill_pay JOIN cust_bill_pay_pkg USING (billpaynum)
WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum";
push @select, 'COALESCE(part_pkg.pkg, part_fee.itemdesc) AS pkg',
'part_pkg.freq',
'cust_main.custnum',
'cust_main.first',
'cust_main.last',
'cust_main.company',
'part_referral.referral',
"($paid_sql) AS paid",
"($last_pay_sql) AS last_pay",
"($recur_sql) AS recur_no_usage",
"($usage_sql) AS recur_usage",
'pkg_class.classname',
'agent.agent',
;
my $query = {
'table' => 'cust_bill_pkg',
'addl_from' => "$join_cust $join_pkg",
'hashref' => {},
'select' => join(",\n", @select ),
'extra_sql' => $where,
'order_by' => 'ORDER BY cust_bill._date, billpkgnum',
};
my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';
my %report_classes; #cache
my %report_option_name =
map { $_->num => $_->name } qsearch('part_pkg_report_option', {});
# should this be in Mason.pm or something?
sub money_sub {
$conf ||= new FS::Conf;
$money_char ||= $conf->config('money_char') || '$';
my $field = shift;
sub {
$money_char . sprintf('%.2f', $_[0]->get($field));
};
}
sub date_sub {
my $field = shift;
sub {
my $value = $_[0]->get($field);
$value ? time2str('%b %d %Y', $value) : '';
};
}
%init>