'count_query' => $count_query,
'count_addl' => \@total_desc,
'header' => [
+ @pkgnum_header,
+ emt('Pkg Def'),
emt('Description'),
+ @post_desc_header,
@peritem_desc,
+ @currency_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
+ ? $_[0]->get('pkgpart')
+ : ''
},
+ 'itemdesc', # is part_pkg.pkg if applicable
+ @post_desc,
#strikethrough or "N/A ($amount)" or something these when
# they're not applicable to pkg_tax search
@peritem_sub,
+ @currency_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,
+ @currency,
'invnum',
'_date',
+ '', #'pay_amount',
+ '', #'credit_amount',
+ FS::UI::Web::cust_sort_fields(),
],
'links' => [
- #'',
+ @pkgnum_null,
+ '',
'',
+ @post_desc_null,
@peritem_null,
+ @currency_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'.
+ $currency_align.
+ 'rcrr'.
FS::UI::Web::cust_aligns(),
'color' => [
- #'',
+ @pkgnum_null,
+ '',
'',
+ @post_desc_null,
@peritem_null,
+ @currency_null,
+ '',
+ '',
'',
'',
FS::UI::Web::cust_colors(),
],
'style' => [
- #'',
+ @pkgnum_null,
'',
+ '',
+ @post_desc_null,
@peritem_null,
+ @currency_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.
+- report_optionnum: Filter on package report class. Can be a single report
+ class number or a comma-separated list (where 0 is "no report class"), or the
+ word "multiple".
+
- use_override: Apply "classnum" and "taxclass" filtering based on the
override (bundle) pkgpart, rather than always using the true pkgpart.
-- nottax: Limit to items that are not taxes (pkgnum > 0).
+- nottax: Limit to items that are not taxes (pkgnum > 0 or feepart > 0).
-- istax: Limit to items that are taxes (pkgnum == 0).
+- istax: Limit to items that are taxes (pkgnum == 0 and feepart = null).
- taxnum: Limit to items whose tax definition matches this taxnum.
With "nottax" that means items that are subject to that tax;
- with "istax" it's the tax charges themselves. Can be specified
- more than once to include multiple taxes.
+ with "istax" it's the tax charges themselves. Can be a comma-separated
+ list to include multiple taxes.
- country, state, county, city: Limit to items whose tax location
matches these fields. If "nottax" it's the tax location of the package;
</%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 @total_desc = ( $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 @currency_desc = ();
+my @currency_sub = ();
+my @currency = ();
+if ( $conf->config('currencies') ) {
+ @currency_desc = ( 'Setup billed', 'Recurring billed' );
+ @currency_sub = (
+ map {
+ my $what = $_;
+ sub { my $currency = $_[0]->get($what.'_billed_currency') or return '';
+ $currency. ' '. currency_symbol($currency, SYM_HTML).
+ $_[0]->get($what.'_billed_amount');
+ };
+ } qw( setup recur )
+ );
+ @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting
+}
+
+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';
+}
+
+# used in several places
+my $itemdesc = 'COALESCE(part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)';
# valid in both the tax and non-tax cases
-$join_cust =
- " LEFT JOIN cust_bill USING (invnum)
- LEFT JOIN cust_main USING (custnum)
- ";
+my $join_cust =
+ " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
+ # use cust_pkg.locationnum if it exists
+ FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
#agent virtualization
my $agentnums_sql =
push @where, "cust_main.agentnum = $1";
}
+# salesnum--see below
# refnum
if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
push @where, "cust_main.refnum = $1";
}
+# 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_referral.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;
+}
+
+
+# custnum
+if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
+ push @where, "cust_main.custnum = $1";
+}
+
+# we want the package and its definition if available
+my $join_pkg =
+' LEFT JOIN cust_pkg USING (pkgnum)
+ LEFT JOIN part_pkg USING (pkgpart)
+ LEFT JOIN part_fee USING (feepart)';
+
+my $part_pkg = 'part_pkg';
+# "Separate sub-packages from parents"
+my $use_override = $cgi->param('use_override') ? 1 : 0;
+if ( $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) = override.pkgpart
+ )";
+ $part_pkg = 'override';
+}
+push @select, "$part_pkg.pkgpart", "$part_pkg.pkg";
+push @select, "COALESCE($part_pkg.taxclass, part_fee.taxclass) AS taxclass"
+ if $conf->exists('enable_taxclasses');
+
# the non-tax case
if ( $cgi->param('nottax') ) {
- push @where, 'cust_bill_pkg.pkgnum > 0';
+ push @select, "($itemdesc) AS itemdesc";
- # then we want the package and its definition
- $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?
+ push @where,
+ '(cust_bill_pkg.pkgnum > 0 OR cust_bill_pkg.feepart IS NOT NULL)';
my @tax_where; # will go into a subquery
my @exempt_where; # will also go into a subquery
# not specified: all classes
# 0: empty class
# N: classnum
- if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
- push @where, "COALESCE($part_pkg.classnum, 0) = $1";
+ if ( grep { $_ eq 'classnum' } $cgi->param ) {
+ my @classnums = grep /^\d*$/, $cgi->param('classnum');
+ push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
+ join(',', @classnums ).
+ ' )'
+ if @classnums;
+ }
+
+ if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
+ my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
+ my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
+ my $all = $cgi->param('all_report_options') ? 1 : 0;
+ push @where, # code reuse FTW
+ FS::Report::Table->with_report_option(
+ report_optionnum => $num,
+ not_report_optionnum => $not_num,
+ use_override => $use_override,
+ all_report_options => $all,
+ );
}
# taxclass
# effective taxclass, not the real one
push @tax_where, 'cust_main_county.taxclass IS NULL'
} elsif ( $cgi->param('taxclass') ) {
- push @tax_where, "$part_pkg.taxclass IN (" .
+ push @tax_where, "COALESCE(part_fee.taxclass, $part_pkg.taxclass) IN (" .
join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
')';
}
# we don't handle exempt_monthly here
if ( $cgi->param('taxname') ) { # specific taxname
- push @tax_where, 'cust_main_county.taxname = '.
+ push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
dbh->quote($cgi->param('taxname'));
- } elsif ( $cgi->param('taxnameNULL') ) {
- push @tax_where, 'cust_main_county.taxname IS NULL OR '.
- 'cust_main_county.taxname = \'Tax\'';
}
# country:state:county:city:district (may be repeated)
}
# specific taxnums
- if ( $cgi->param('taxnum') ) {
- my $taxnum_in = join(',',
- grep /^\d+$/, $cgi->param('taxnum')
- );
- push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
- if $taxnum_in;
+ if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
+ push @tax_where, "cust_main_county.taxnum IN ($1)";
}
# If we're showing exempt items, we need to find those with
$join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
USING (billpkgnum)";
- }
- if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
# process tax restrictions
unshift @tax_where,
+ 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
'cust_main_county.tax > 0';
+ }
- my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
+ my $tax_sub = "SELECT 1
FROM cust_bill_pkg_tax_location
JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
JOIN cust_main_county USING (taxnum)
- WHERE ". join(' AND ', @tax_where).
- " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
-
- $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
- ON (item_tax.invnum = cust_bill_pkg.invnum AND
- item_tax.pkgnum = cust_bill_pkg.pkgnum)";
- }
+ WHERE ". join(' AND ', @tax_where);
# now do something with that
if ( @exempt_where ) {
my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
'- COALESCE(item_exempt.exempt_amount, 0)';
- push @where, 'item_tax.invnum IS NOT NULL';
push @select, "($taxable) AS taxable_amount";
+ push @where, "EXISTS($tax_sub)";
push @peritem, 'taxable_amount';
push @peritem_desc, 'Taxable';
push @total, "SUM($taxable)";
push @total_desc, "$money_char%.2f taxable";
- } elsif ( $cgi->param('out') ) {
-
- push @where, 'item_tax.invnum IS NULL',
- 'item_exempt.billpkgnum IS NULL';
-
} elsif ( @tax_where ) {
# union of taxable + all exempt_ cases
- push @where,
- '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
+ push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
}
# 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";
$peritem[1] = 'recur_no_usage';
$total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
- $total_desc[1] .= ' (excluding usage)';
+ $total_desc[0] .= ' (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";
$peritem[1] = '_usage';
$peritem_desc[1] = 'Usage charge';
$total[1] = "SUM($usage)";
- $total_desc[1] .= ' usage charges';
+ $total_desc[0] .= ' usage charges';
}
} elsif ( $cgi->param('istax') ) {
push @where, 'cust_bill_pkg.pkgnum = 0';
# tax location when using tax_rate_location
- if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
+ if ( $cgi->param('vendortax') ) {
$join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
- push @where, FS::tax_rate_location->location_sql(
- map { $_ => (scalar($cgi->param($_)) || '') }
- qw( district city county state locationtaxid )
- );
+ foreach (qw( state county city locationtaxid)) {
+ if ( scalar($cgi->param($_)) ) {
+ my $place = dbh->quote( $cgi->param($_) );
+ push @where, "tax_rate_location.$_ = $place";
+ }
+ }
$total[1] = 'SUM(
COALESCE(cust_bill_pkg_tax_rate_location.amount,
} 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)
';
# don't double-count the components of consolidated taxes
$total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
$total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
+
+ # package classnum
+ if ( grep { $_ eq 'classnum' } $cgi->param ) {
+ my @classnums = grep /^\d*$/, $cgi->param('classnum');
+ $join_pkg .= '
+ JOIN cust_pkg AS taxed_pkg
+ ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
+ JOIN part_pkg AS taxed_part_pkg
+ ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
+ ';
+ push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
+ join(',', @classnums ).
+ ' )'
+ if @classnums;
+ }
}
# taxclass
dbh->quote($cgi->param('taxname'));
}
- # specific taxnums
- if ( $cgi->param('taxnum') ) {
- my $taxnum_in = join(',',
- grep /^\d+$/, $cgi->param('taxnum')
- );
- push @where, "cust_main_county.taxnum IN ($taxnum_in)"
- if $taxnum_in;
- }
-
- # report group (itemdesc)
- if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
- my ( $group_op, $group_value ) = ( $1, $2 );
- if ( $group_op eq '=' ) {
- #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
- push @where, 'itemdesc = '. dbh->quote($group_value);
- } elsif ( $group_op eq '!=' ) {
- push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
- } else {
- die "guru meditation #00de: group_op $group_op\n";
- }
- }
-
- # itemdesc, for some reason
+ # itemdesc, for breakdown from the vendor tax report
if ( $cgi->param('itemdesc') ) {
if ( $cgi->param('itemdesc') eq 'Tax' ) {
- push @where, "(itemdesc='Tax' OR itemdesc is null)";
+ push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
} else {
- push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
+ push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
}
}
} # nottax / istax
+
+#total payments
+my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
+ FROM cust_bill_pay_pkg
+ WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
+ ";
+push @select, "($pay_sub) AS 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)
+ FROM cust_credit_bill_pkg
+ WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
+ ";
+ push @select, "($credit_sub) AS credit_amount";
+
+}
push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
+#salesnum
+if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
+
+ my $salesnum = $1;
+ my $sales = FS::sales->by_key($salesnum)
+ or die "salesnum $salesnum not found";
+
+ my $subsearch = $sales->cust_bill_pkg_search('', '',
+ 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
+ 'paid' => ($cgi->param('paid') ? 1 : 0),
+ 'classnum' => scalar($cgi->param('classnum'))
+ );
+ $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_fee.classnum, part_pkg.classnum, 0) )";
+
+ my $extra_sql = $subsearch->{extra_sql};
+ $extra_sql =~ s/^WHERE//;
+ push @where, $extra_sql;
+
+ $cgi->param('classnum', 0) unless $cgi->param('classnum');
+}
+
+
my $where = join(' AND ', @where);
$where &&= "WHERE $where";
my $query = {
'table' => 'cust_bill_pkg',
- 'addl_from' => "$join_cust $join_pkg",
+ 'addl_from' => "$join_pkg $join_cust",
'hashref' => {},
'select' => join(",\n", @select ),
'extra_sql' => $where,
my $count_query =
'SELECT ' . join(',', @total) .
- " FROM cust_bill_pkg $join_cust $join_pkg
+ " FROM cust_bill_pkg $join_pkg $join_cust
$where";
-shift @total_desc; #the first one is implicit
-
@peritem_desc = map {emt($_)} @peritem_desc;
my @peritem_sub = map {
my $field = $_;
my @peritem_null = map { '' } @peritem; # placeholders
my $peritem_align = 'r' x scalar(@peritem);
+@currency_desc = map {emt($_)} @currency_desc;
+my @currency_null = map { '' } @currency; # placeholders
+my $currency_align = 'r' x scalar(@currency);
+
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