<% include( 'elements/search.html',
- 'title' => 'Tax credits', #well, actually application of
- 'name' => 'tax credits', # credit to line item
- 'query' => $query,
- 'count_query' => $count_query,
- 'count_addl' => [ $money_char. '%.2f total', ],
- 'header' => [
+ 'title' => 'Credit application detail', #to line item
+ 'name_singular' => 'credit application',
+ 'query' => $query,
+ 'count_query' => $count_query,
+ 'count_addl' => [ $money_char. '%.2f total', ],
+ 'header' => [
#'#',
'Amount',
- # credit date, By, Reason,
- # line item, invoice
+ #credit
+ 'Date',
+ 'By',
+ 'Reason',
+
+ # line item
'Description',
- 'Setup charge',
- ( $use_usage eq 'usage'
- ? 'Usage charge'
- : 'Recurring charge'
- ),
+ @post_desc_header,
+
+ #invoice
'Invoice',
'Date',
FS::UI::Web::cust_header(),
- ],
- 'fields' => [
+ ],
+ 'fields' => [
#'creditbillpkgnum',
- 'amount',
+ sub { sprintf($money_char.'%.2f', shift->amount ) },
+
+ sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) },
+ sub { shift->cust_credit_bill->cust_credit->otaker },
+ sub { shift->cust_credit_bill->cust_credit->reason },
sub { $_[0]->pkgnum > 0
? $_[0]->get('pkg') # possibly use override.pkg
: $_[0]->get('itemdesc') # but i think this correct
},
- #strikethrough or "N/A ($amount)" or something these when
- # they're not applicable to pkg_tax search
- sub { sprintf($money_char.'%.2f', shift->setup ) },
- sub { my $row = shift;
- my $value = 0;
- if ( $use_usage eq 'recurring' ) {
- $value = $row->recur - $row->usage;
- } elsif ( $use_usage eq 'usage' ) {
- $value = $row->usage;
- } else {
- $value = $row->recur;
- }
- sprintf($money_char.'%.2f', $value );
- },
+ @post_desc,
'invnum',
sub { time2str('%b %d %Y', shift->_date ) },
\&FS::UI::Web::cust_fields,
- ],
- 'links' => [
+ ],
+ 'sort_fields' => [
+ 'amount',
+ 'cust_credit_date',
+ '', #'otaker',
+ '', #reason
+ '', #line item description
+ @post_desc_null,
+ 'invnum',
+ '_date',
+ #cust fields
+ ],
+ 'links' => [
'',
'',
'',
'',
+ '',
+ @post_desc_null,
$ilink,
$ilink,
( map { $_ ne 'Cust. Status' ? $clink : '' }
FS::UI::Web::cust_header()
),
- ],
- 'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
- 'color' => [
+ ],
+ 'align' => 'rrlll'.
+ $post_desc_align.
+ 'rr'.
+ FS::UI::Web::cust_aligns(),
+ 'color' => [
+ '',
'',
'',
'',
'',
+ @post_desc_null,
'',
'',
FS::UI::Web::cust_colors(),
],
- 'style' => [
+ 'style' => [
'',
'',
'',
'',
'',
+ @post_desc_null,
+ '',
'',
FS::UI::Web::cust_styles(),
- ],
+ ],
)
%>
<%init>
#LOTS of false laziness below w/cust_bill_pkg.cgi
+# and a little w/cust_credit.html
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
my @where = ( $agentnums_sql );
+if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
+ push @where, "cust_credit.usernum = $1";
+}
+
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
push @where, "cust_bill._date >= $beginning",
"cust_bill._date <= $ending";
-push @where , " payby != 'COMP' "
- unless $cgi->param('include_comp_cust');
+my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit');
+push @where, "cust_credit._date >= $cr_begin",
+ "cust_credit._date <= $cr_end";
+
+#credit amount? seems more what is expected than the applied amount
+my @lt_gt = FS::UI::Web::parse_lt_gt($cgi, 'amount' );
+s/amount/cust_credit.amount/g foreach (@lt_gt);
+push @where, @lt_gt;
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
push @where, "cust_main.agentnum = $1";
}
+if ( $cgi->param('billpkgnum') =~ /^(\d+)$/ ) {
+ push @where, "billpkgnum = $1";
+}
+
#classnum
# not specified: all classes
# 0: empty class
}
-my @loc_param = qw( city county state country );
+my @loc_param = qw( district city county state country );
if ( $cgi->param('out') ) {
my %ph = ( 'county' => dbh->quote($_),
map { $_ => dbh->quote( $cgi->param($_) ) }
- qw( city state country )
+ qw( district city state country )
);
my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
#warn "neither nottax nor istax parameters specified";
}
- if ( $cgi->param('taxclassNULL') ) {
+ if ( $cgi->param('taxclassNULL')
+ && ! $cgi->param('istax') #no taxclass in this case
+ )
+ {
my %hash = ( 'country' => scalar($cgi->param('country')) );
foreach (qw( state county )) {
push @where, $cust_exempt;
}
-my $use_usage = $cgi->param('use_usage');
-
-my $count_query;
-if ( $cgi->param('pkg_tax') ) { #does this mean anything here?
-
- $count_query =
- "SELECT COUNT(*),
- SUM(
- ( CASE WHEN part_pkg.setuptax = 'Y'
- THEN cust_bill_pkg.setup
- ELSE 0
- END
- )
- +
- ( CASE WHEN part_pkg.recurtax = 'Y'
- THEN cust_bill_pkg.recur
- ELSE 0
- END
- )
- )
- ";
-
- push @where, "( ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 )
- OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) )",
- "( tax != 'Y' OR tax IS NULL )";
-
-} elsif ( $cgi->param('taxable') ) { #again, meaningful?
-
- my $setup_taxable = "(
- CASE WHEN part_pkg.setuptax = 'Y'
- THEN 0
- ELSE cust_bill_pkg.setup
- END
- )";
-
- my $recur_taxable = "(
- CASE WHEN part_pkg.recurtax = 'Y'
- THEN 0
- ELSE cust_bill_pkg.recur
- END
- )";
-
- my $exempt = "(
- SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg
- WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum
- )";
-
- $count_query =
- "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )";
-
- push @where,
- #not tax-exempt package (setup or recur)
- "(
- ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL )
- AND cust_bill_pkg.setup > 0 )
- OR
- ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL )
- AND cust_bill_pkg.recur > 0 )
- )",
- #not a tax_exempt customer
- "( tax != 'Y' OR tax IS NULL )";
- #not covered in full by a monthly tax exemption (texas tax)
- "0 < ( $setup_taxable + $recur_taxable - $exempt )",
-
-} else {
-
- $count_query = "SELECT COUNT(*), ";
-
- if ( $use_usage eq 'recurring' ) { #mean anything?
- $count_query .= "SUM(setup + recur - usage)";
- } elsif ( $use_usage eq 'usage' ) { #mean anything?
- $count_query .= "SUM(usage)";
- } else {
- $count_query .= "SUM(cust_credit_bill_pkg.amount)";
- }
-
-}
+my $count_query = "SELECT COUNT(DISTINCT creditbillpkgnum),
+ SUM(cust_credit_bill_pkg.amount)";
my $join_cust =
' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )
my $join_pkg;
-my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum ';
+my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum )';
if ( $cgi->param('nottax') ) {
} elsif ( $cgi->param('istax') ) {
#false laziness w/report_tax.cgi $taxfromwhere
- if ( $conf->exists('tax-pkg_address') ) {
- $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
- LEFT JOIN cust_location USING ( locationnum ) ';
-
- #quelle kludge, somewhat false laziness w/report_tax.cgi
- s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where;
- } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ||
+ if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ||
$cgi->param('iscredit') eq 'rate') {
- $join_pkg .=
- ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
- ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) ';
- }
- if ( $conf->exists('tax-pkg_address') ) {
- $join_cust_bill_pkg .= ', billpkgtaxlocationnum )';
+ $join_pkg .=
+ ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ';
+ if ( $cgi->param('iscredit') eq 'rate' ) {
+ $join_pkg .= ', billpkgtaxratelocationnum )';
+ } elsif ( $conf->exists('tax-pkg_address') ) {
+ $join_pkg .= ', billpkgtaxlocationnum )';
push @where, "billpkgtaxratelocationnum IS NULL";
- #} elsif ( $cgi->param('iscredit') eq 'rate' ) {
- # $join_pkg .= ', billpkgtaxratelocationnum )';
} else {
- $join_cust_bill_pkg .= ' )';
+ $join_pkg .= ' )';
push @where, "billpkgtaxratelocationnum IS NULL";
}
+ $join_pkg .= ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) ';
+
+ } elsif ( $conf->exists('tax-pkg_address') ) {
+
+ $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
+ LEFT JOIN cust_location USING ( locationnum ) ';
+
+ #quelle kludge, somewhat false laziness w/report_tax.cgi
+ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where;
+ }
+
} else {
#die?
my $join_credit = ' LEFT JOIN cust_credit_bill USING ( creditbillnum )
LEFT JOIN cust_credit USING ( crednum ) ';
-#if ($use_usage) {
-# $count_query .=
-# " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur,
-# ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail
-# WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum
-# ) AS usage FROM cust_bill_pkg $join_cust $join_pkg $where
-# ) AS countquery";
-#} else {
- $count_query .= " FROM cust_credit_bill_pkg
- $join_pkg
- $join_cust_bill_pkg
- $join_credit
- $join_cust
- $where";
-#}
-
-my @select = ( 'cust_credit_bill_pkg.amount',
+$count_query .= " FROM cust_credit_bill_pkg
+ $join_cust_bill_pkg
+ $join_pkg
+ $join_credit
+ $join_cust
+ $where";
+
+my @select = ( 'cust_credit_bill_pkg.*',
'cust_bill_pkg.*',
+ 'cust_credit.otaker',
+ 'cust_credit._date AS cust_credit_date',
'cust_bill._date',
);
push @select, 'part_pkg.pkg' unless $cgi->param('istax');
push @select, 'cust_main.custnum',
FS::UI::Web::cust_sql_fields();
+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?
+}
+
my $query = {
'table' => 'cust_credit_bill_pkg',
- 'addl_from' => "$join_pkg
- $join_cust_bill_pkg
+ 'addl_from' => "$join_cust_bill_pkg
+ $join_pkg
$join_credit
$join_cust",
'hashref' => {},
projects / freeside.git / blobdiff