$unearned ? ( $money_char. '%.2f unearned revenue' ) : (),
],
'header' => [
+ @pkgnum_header,
+ emt('Pkg Def'),
emt('Description'),
+ @post_desc_header,
( $unearned
? ( emt('Unearned'),
emt('Owed'), # useful in 'paid' mode?
),
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
sub { my $cust_bill_pkg = shift;
),
'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,
'setup', #broken in $unearned case i guess
( $unearned ? ('', '') : () ),
( $use_usage eq 'recurring' or $unearned
( $unearned ? ('sdate', 'edate') : () ),
'invnum',
'_date',
+ #'pay_amount',
+ #'credit_amount',
],
'links' => [
- #'',
+ @pkgnum_null,
+ '',
'',
+ @post_desc_null,
'',
( $unearned ? ( '', '' ) : () ),
'',
( $unearned ? ( '', '' ) : () ),
$ilink,
$ilink,
+ $pay_link,
+ $credit_link,
( map { $_ ne 'Cust. Status' ? $clink : '' }
FS::UI::Web::cust_header()
),
],
#'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
- 'align' => 'lr'.
+ 'align' => $pkgnum_align.
+ 'rl'.
+ $post_desc_align.
+ 'r'.
( $unearned ? 'rc' : '' ).
'r'.
( $unearned ? 'cc' : '' ).
- 'rc'.
+ 'rcrr'.
FS::UI::Web::cust_aligns(),
'color' => [
- #'',
+ @pkgnum_null,
+ '',
'',
+ @post_desc_null,
'',
( $unearned ? ( '', '' ) : () ),
'',
( $unearned ? ( '', '' ) : () ),
'',
'',
+ '',
+ '',
FS::UI::Web::cust_colors(),
],
'style' => [
- #'',
+ @pkgnum_null,
+ '',
'',
+ @post_desc_null,
'',
( $unearned ? ( '', '' ) : () ),
'',
( $unearned ? ( '', '' ) : () ),
'',
'',
+ '',
+ '',
FS::UI::Web::cust_styles(),
],
&>
#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 $unearned_sql = '';
my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
-my ($join_cust, $join_pkg ) = ('', '');
+
+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?
+}
#here is the agent virtualization
my $agentnums_sql =
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";
}
-$join_cust = ' JOIN cust_bill USING ( invnum )
- LEFT JOIN cust_main USING ( custnum ) ';
+my $join_cust = ' JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum ) ';
+
+# 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') ) { #"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) = override.pkgpart
+ )";
+ $part_pkg = 'override';
+}
if ( $cgi->param('nottax') ) {
- $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 ';
$join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) '
if $conf->exists('tax-pkg_address');
} 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;
}
}
-} else {
+} # nottax / istax
- #die?
- warn "neiether nottax nor istax parameters specified";
- #same as before?
- $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum )
- LEFT JOIN part_pkg USING ( pkgpart ) ';
-}
+#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";
+
+#total credits
+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();
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', ];
my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';
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>
projects / freeside.git / blobdiff