& elements/search.html,
'title' => emt("Unearned revenue - ".ucfirst($unearned_mode)) . ' (' .
time2str('%b %d %Y', $unearned) . ')',
'name' => emt('line items'),
'query' => $query,
'count_query' => $count_query,
'count_addl' => [ $money_char. '%.2f total',
$money_char. '%.2f unearned revenue'
],
'header' => [ map( {emt $_}
'Description',
'Unearned', # depends on mode
'Recurring charge', #recur - usage
'Owed', #recur - usage - credits - payments
'Paid', #payments
'Payment date', #of last payment
'Credit date', #of last credit
'Charge start',
'Charge end',
'Invoice',
'Date'
),
FS::UI::Web::cust_header(),
],
'fields' => [
#Description
sub { $_[0]->pkgnum > 0
? $_[0]->get('pkg') # possibly use override.pkg
: $_[0]->get('itemdesc') # but i think this correct
},
#Unearned
money_sub('unearned_revenue'),
#Recurring charge
money_sub('recur_no_usage'),
#Owed
money_sub('owed_no_usage'),
#Paid
money_sub('paid_no_usage'),
#Payment date
date_sub('last_pay'),
#Credit date
date_sub('last_credit'),
#Charge start
date_sub('sdate'),
#Charge end, minus most of a day
date_sub('before_edate'),
#Invoice
'invnum',
#Invoice date
date_sub('_date'),
\&FS::UI::Web::cust_fields,
],
'sort_fields' => [
'pkg',
# SQL expressions work as sort keys...
'unearned_revenue',
'recur_no_usage',
'owed_no_usage',
'paid_no_usage',
'last_pay',
'last_credit',
'sdate',
'edate',
'invnum',
'_date',
],
'links' => [
('' x 9),
$ilink,
$ilink,
( map { $_ ne 'Cust. Status' ? $clink : '' }
FS::UI::Web::cust_header()
),
],
'align' => 'lrrcrccrc'. FS::UI::Web::cust_aligns(),
'color' => [
('' x 11),
FS::UI::Web::cust_colors(),
],
'style' => [
('' x 11),
FS::UI::Web::cust_styles(),
],
&>
<%init>
# Separated from cust_bill_pkg.cgi to simplify things.
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
my $conf = new FS::Conf;
my $unearned = '';
my $unearned_mode = '';
my $unearned_base = '';
my $unearned_sql = '';
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 @where = ( $agentnums_sql );
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
push @where, "cust_main.agentnum = $1";
}
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
push @where, FS::cust_main->cust_status_sql . " = '$1'";
}
push @where, "cust_bill._date >= $beginning",
"cust_bill._date <= $ending";
# 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, cust_bill_pkg_referral.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;
}
# no pkgclass, no taxclass, no tax location...
# unearned revenue mode
$cgi->param('date') =~ /^(\d+)$/
or die "date required";
$unearned = $1;
$unearned_mode = $cgi->param('mode');
push @where, "cust_bill_pkg.sdate < $unearned",
"cust_bill_pkg.edate > $unearned",
"cust_bill_pkg.recur != 0",
"part_pkg.freq != '0'";
if ( !$cgi->param('include_monthly') ) {
push @where,
"part_pkg.freq != '1'",
"part_pkg.freq NOT LIKE '%h'",
"part_pkg.freq NOT LIKE '%d'",
"part_pkg.freq NOT LIKE '%w'";
}
my @opt = (
$unearned, #before this date
'', #after this date
setuprecur => 'recur',
no_usage => 1
);
my $charged = FS::cust_bill_pkg->charged_sql(@opt);
push @select, "($charged) AS recur_no_usage";
my $owed_sql = FS::cust_bill_pkg->owed_sql(@opt);
push @select, "($owed_sql) AS owed_no_usage";
my $paid_sql = FS::cust_bill_pkg->paid_sql(@opt);
push @select, "$paid_sql AS paid_no_usage";
if ( $unearned_mode eq 'paid' ) {
# then use the amount paid, minus usage charges
$unearned_base = $paid_sql;
}
else {
# use the amount billed, minus usage charges and credits
$unearned_base = "( $charged - " .
FS::cust_bill_pkg->credited_sql(@opt) . ' )';
}
# whatever we're using as the base, only show rows where it's positive
push @where, "$unearned_base > 0";
my $edate_zero = midnight_sql('edate');
my $sdate_zero = midnight_sql('sdate');
# $unearned is one second before midnight on the date requested for the report.
# suppress partial days for more accounting-like behavior
my $period = "CAST( ($edate_zero - $sdate_zero) / 86400.0 AS DECIMAL(10,0) )";
my $remaining = "GREATEST(
CAST( ($edate_zero - $unearned) / 86400.0 AS DECIMAL(10,0) ),
0)";
my $fraction = "$remaining / $period";
$unearned_sql = "CAST( $unearned_base * $fraction AS DECIMAL(10,2) )";
push @select, "$unearned_sql AS unearned_revenue";
# last payment/credit date
my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
foreach my $x (qw(pay credit)) {
my $table = "cust_$x";
my $link_bill = $t{$x};
my $link_pkg = $link_bill.'_pkg';
my %pkey = map { $_ => dbdef->table($_)->primary_key }
( $table, $link_bill );
my $last_date_sql = "SELECT MAX($table._date)
FROM $table JOIN $link_bill USING ($pkey{$table})
JOIN $link_pkg USING ($pkey{$link_bill})
WHERE $link_pkg.billpkgnum = cust_bill_pkg.billpkgnum
AND $table._date <= $unearned";
push @select, "($last_date_sql) AS last_$x";
}
push @select, '(edate - 82799) AS before_edate';
#no itemdesc
#no tax report group kludge
#no tax exemption
#usage always excluded
# always 'nottax', not 'istax'
$join_cust = ' JOIN cust_bill USING ( invnum ) '.
FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
$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 ';
my $where = ' WHERE '. join(' AND ', @where);
my $count_query = "SELECT COUNT(DISTINCT billpkgnum),
SUM( $unearned_base ), SUM( $unearned_sql )
FROM cust_bill_pkg $join_pkg $join_cust $where";
push @select, 'part_pkg.pkg',
'part_pkg.freq',
'cust_main.custnum',
FS::UI::Web::cust_sql_fields();
my $query = {
'table' => 'cust_bill_pkg',
'addl_from' => "$join_pkg $join_cust",
'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') || '$';
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>