'count_query' => $count_query,
'count_addl' => \@total_desc,
'header' => [
+ @pkgnum_header,
+ emt('Pkg Def'),
emt('Description'),
+ @post_desc_header,
@peritem_desc,
emt('Invoice'),
emt('Date'),
+ emt('Paid'),
+ emt('Credited'),
FS::UI::Web::cust_header(),
],
'fields' => [
+ @pkgnum,
sub { $_[0]->pkgnum > 0
- ? $_[0]->get('pkg') # possibly use override.pkg
- : $_[0]->get('itemdesc') # but i think this correct
+ # possibly use override.pkg but i think this correct
+ ? $_[0]->get('pkgpart')
+ : ''
},
+ sub { $_[0]->pkgnum > 0
+ # possibly use override.pkg but i think this correct
+ ? $_[0]->get('pkg')
+ : $_[0]->get('itemdesc')
+ },
+ @post_desc,
#strikethrough or "N/A ($amount)" or something these when
# they're not applicable to pkg_tax search
@peritem_sub,
'invnum',
sub { time2str('%b %d %Y', shift->_date ) },
+ sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
+ sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
\&FS::UI::Web::cust_fields,
],
'sort_fields' => [
+ @pkgnum_null,
+ '',
'',
+ @post_desc_null,
@peritem,
'invnum',
'_date',
+ #'pay_amount',
+ #'credit_amount',
],
'links' => [
- #'',
+ @pkgnum_null,
'',
+ '',
+ @post_desc_null,
@peritem_null,
$ilink,
$ilink,
+ $pay_link,
+ $credit_link,
( map { $_ ne 'Cust. Status' ? $clink : '' }
FS::UI::Web::cust_header()
),
],
#'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
- 'align' => 'l'.
+ 'align' => $pkgnum_align.
+ 'rl'.
+ $post_desc_align.
$peritem_align.
- 'rc'.
+ 'rcrr'.
FS::UI::Web::cust_aligns(),
'color' => [
- #'',
+ @pkgnum_null,
+ '',
'',
+ @post_desc_null,
@peritem_null,
'',
'',
+ '',
+ '',
FS::UI::Web::cust_colors(),
],
'style' => [
- #'',
+ @pkgnum_null,
+ '',
'',
+ @post_desc_null,
@peritem_null,
'',
'',
+ '',
+ '',
FS::UI::Web::cust_styles(),
],
&>
<%doc>
-Output parameters:
+Output control parameters:
- distribute: Boolean. If true, recurring fees will be "prorated" for the
portion of the package date range (sdate-edate) that falls within the date
range of the report. Line items will be limited to those for which this
portion is > 0. This disables filtering on invoice date.
-- use_usage: Separate usage (cust_bill_pkg_detail records) from
+- usage: Separate usage (cust_bill_pkg_detail records) from
recurring charges. If set to "usage", will show usage instead of
recurring charges. If set to "recurring", will deduct usage and only
show the flat rate charge. If not passed, the "recurring charge" column
- refnum: Filter on customer reference source.
+- cust_classnum: Filter on customer class.
+
- classnum: Filter on package class.
- use_override: Apply "classnum" and "taxclass" filtering based on the
</%doc>
<%init>
-die "access denied"
- unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied" unless $curuser->access_right('Financial reports');
my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';
my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
my @total_desc = ( '%d line items', $money_char.'%.2f total' ); # sprintf strings
+
my @peritem = ( 'setup', 'recur' );
my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
-my ($join_cust, $join_pkg ) = ('', '');
-my $use_usage;
+
+my @pkgnum_header = ();
+my @pkgnum = ();
+my @pkgnum_null;
+my $pkgnum_align = '';
+if ( $curuser->option('show_pkgnum') ) {
+ push @select, 'cust_bill_pkg.pkgnum';
+ push @pkgnum_header, 'Pkg Num';
+ push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
+ push @pkgnum_null, '';
+ $pkgnum_align .= 'r';
+}
+
+my @post_desc_header = ();
+my @post_desc = ();
+my @post_desc_null = ();
+my $post_desc_align = '';
+if ( $conf->exists('enable_taxclasses') ) {
+ push @post_desc_header, 'Tax class';
+ push @post_desc, 'taxclass';
+ push @post_desc_null, '';
+ $post_desc_align .= 'l';
+ push @select, 'part_pkg.taxclass'; # or should this use override?
+}
# valid in both the tax and non-tax cases
-$join_cust =
+my $join_cust =
" LEFT JOIN cust_bill USING (invnum)
LEFT JOIN cust_main USING (custnum)
";
push @where, "cust_main.refnum = $1";
}
-# the non-tax case
-if ( $cgi->param('nottax') ) {
+# cust_classnum
+if ( $cgi->param('cust_classnum') ) {
+ my @classnums = grep /^\d+$/, $cgi->param('cust_classnum');
+ push @where, 'cust_main.classnum IN('.join(',',@classnums).')'
+ if @classnums;
+}
- push @where, 'cust_bill_pkg.pkgnum > 0';
+# custnum
+if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
+ push @where, "cust_main.custnum = $1";
+}
- # then we want the package and its definition
- $join_pkg =
+# we want the package and its definition if available
+my $join_pkg =
' LEFT JOIN cust_pkg USING (pkgnum)
LEFT JOIN part_pkg USING (pkgpart)';
- my $part_pkg = 'part_pkg';
- if ( $cgi->param('use_override') ) {
- # still need the real part_pkg for tax applicability,
- # so alias this one
- $join_pkg .= " LEFT JOIN part_pkg AS override ON (
- COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
- )";
- $part_pkg = 'override';
- }
- push @select, 'part_pkg.pkg'; # or should this use override?
+my $part_pkg = 'part_pkg';
+if ( $cgi->param('use_override') ) {
+ # still need the real part_pkg for tax applicability,
+ # so alias this one
+ $join_pkg .= " LEFT JOIN part_pkg AS override ON (
+ COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
+ )";
+ $part_pkg = 'override';
+}
+push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
+
+# the non-tax case
+if ( $cgi->param('nottax') ) {
+
+ push @where, 'cust_bill_pkg.pkgnum > 0';
my @tax_where; # will go into a subquery
my @exempt_where; # will also go into a subquery
}
# recur/usage separation
- $use_usage = $cgi->param('usage');
- if ( $use_usage eq 'recurring' ) {
+ if ( $cgi->param('usage') eq 'recurring' ) {
my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
push @select, "($recur_no_usage) AS recur_no_usage";
$total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
$total_desc[1] .= ' (excluding usage)';
- } elsif ( $use_usage eq 'usage' ) {
+ } elsif ( $cgi->param('usage') eq 'usage' ) {
my $usage = FS::cust_bill_pkg->usage_sql();
push @select, "($usage) AS _usage";
} elsif ( $cgi->param('out') ) {
- $join_pkg = '
+ $join_pkg .= '
LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
';
push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
} else { # not locationtaxid or 'out'--the normal case
- $join_pkg = '
+ $join_pkg .= '
LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
JOIN cust_main_county USING (taxnum)
';
} # nottax / istax
+
+#total payments
+my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount) AS pay_amount,
+ billpkgnum
+ FROM cust_bill_pay_pkg
+ GROUP BY billpkgnum";
+$join_pkg .= " LEFT JOIN ($pay_sub) AS item_pay USING (billpkgnum)";
+push @select, 'item_pay.pay_amount';
+
+
# credit
if ( $cgi->param('credit') ) {
push @peritem_desc, 'Credited', 'By', 'Reason';
push @total, 'SUM(credit_amount)';
push @total_desc, "$money_char%.2f credited";
-} # if credit
+
+} else {
+
+ #still want a credit total column
+
+ my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
+ billpkgnum
+ FROM cust_credit_bill_pkg
+ GROUP BY billpkgnum";
+ $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
+
+ push @select, 'item_credit.credit_amount';
+
+}
push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
+my $pay_link = ''; #[, 'billpkgnum', ];
+my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
+
warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
if $cgi->param('debug');
+
</%init>
projects / freeside.git / blobdiff