$unearned ? ( $money_char. '%.2f unearned revenue' ) : (),
],
'header' => [
+ @pkgnum_header,
+ emt('Pkg Def'),
emt('Description'),
@post_desc_header,
( $unearned
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
\&FS::UI::Web::cust_fields,
],
'sort_fields' => [
+ @pkgnum_null,
+ '',
'',
@post_desc_null,
'setup', #broken in $unearned case i guess
#'credit_amount',
],
'links' => [
- #'',
+ @pkgnum_null,
+ '',
'',
@post_desc_null,
'',
),
],
#'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
- 'align' => 'l'.
+ 'align' => $pkgnum_align.
+ 'rl'.
$post_desc_align.
'r'.
( $unearned ? 'rc' : '' ).
'rcrr'.
FS::UI::Web::cust_aligns(),
'color' => [
- #'',
+ @pkgnum_null,
+ '',
'',
@post_desc_null,
'',
FS::UI::Web::cust_colors(),
],
'style' => [
- #'',
+ @pkgnum_null,
+ '',
'',
@post_desc_null,
'',
#LOTS of false laziness below w/cust_credit_bill_pkg.cgi
-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 @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
+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 = ();
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";
' LEFT JOIN cust_pkg USING (pkgnum)
LEFT JOIN part_pkg USING (pkgpart)';
-#my $part_pkg = 'part_pkg';
-#if ( $cgi->param('use_override') ) {
+my $part_pkg = 'part_pkg';
+if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
# 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
+ COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
)";
-# $part_pkg = 'override';
-#}
+ $part_pkg = 'override';
+}
if ( $cgi->param('nottax') ) {
} elsif ( $conf->exists('tax-pkg_address') ) {
- $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
- LEFT JOIN cust_location USING ( locationnum ) ';
+ $join_pkg .= '
+ LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
+ LEFT JOIN cust_location ON cust_bill_pkg_tax_location.locationnum
+ = cust_location.locationnum
+ ';
#quelle kludge, somewhat false laziness w/report_tax.cgi
s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where;
#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';
+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";
#total credits
-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';
+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";
my $where = ' WHERE '. join(' AND ', @where);
$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
}
-push @select, 'part_pkg.pkg',
- 'part_pkg.freq',
- unless $cgi->param('istax');
+push @select, 'part_pkg.pkgpart',
+ 'part_pkg.pkg',
+ 'part_pkg.freq';
push @select, 'cust_main.custnum',
FS::UI::Web::cust_sql_fields();
time2str('%b %d %Y', $cust_pay[-1]->_date );
};
+warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
+ if $cgi->param('debug');
+
</%init>