diff options
| author | Ivan Kohler <ivan@freeside.biz> | 2012-09-27 20:27:43 -0700 |
|---|---|---|
| committer | Ivan Kohler <ivan@freeside.biz> | 2012-09-27 20:27:43 -0700 |
| commit | 3185fe4edea62dd3fa9818cf80902e96fe2a2d21 (patch) | |
| tree | 824a6cdb4b8ccc163127e00e1e86435b4c523476 /httemplate | |
| parent | f50a821d306b561d602edbdac0dac958b862ec0c (diff) | |
| parent | 39533c66139210655fc47404a17fd4e9b9ca8a00 (diff) | |
Merge branch 'master' of git.freeside.biz:/home/git/freeside
Conflicts:
FS/FS/cust_main/Billing.pm
Diffstat (limited to 'httemplate')
20 files changed, 905 insertions, 876 deletions
diff --git a/httemplate/browse/cust_note_class.html b/httemplate/browse/cust_note_class.html index f5d450b9f..7928199b3 100644 --- a/httemplate/browse/cust_note_class.html +++ b/httemplate/browse/cust_note_class.html @@ -3,7 +3,7 @@ 'html_init' => $html_init, 'name' => 'customer note classes', 'disableable' => 1, - 'disabled_statuspos' => 2, + 'disabled_statuspos' => 1, 'query' => { 'table' => 'cust_note_class', 'hashref' => {}, 'order_by' => 'ORDER BY classnum', diff --git a/httemplate/browse/radius_group.html b/httemplate/browse/radius_group.html index fbf6d3766..98e81ab86 100644 --- a/httemplate/browse/radius_group.html +++ b/httemplate/browse/radius_group.html @@ -5,15 +5,26 @@ 'query' => { 'table' => 'radius_group' }, 'count_query' => 'SELECT COUNT(*) FROM radius_group', 'header' => [ '#', 'RADIUS Group', 'Description', 'Priority', - 'Check', 'Reply' ], + 'Check', 'Reply', 'Speed' ], 'fields' => [ 'groupnum', 'groupname', 'description', 'priority', - $check_attr, $reply_attr + $check_attr, $reply_attr, + sub { + my $group = shift; + if ($group->speed_down and $group->speed_up) { + return join (' / ', $group->speed_down, $group->speed_up); + } elsif ( $group->speed_down ) { + return $group->speed_down . ' down'; + } elsif ( $group->speed_up ) { + return $group->speed_up . ' up'; + } + ''; + }, ], - 'align' => 'lllcll', - 'links' => [ $link, $link, '', '', '', '', + 'align' => 'lllcllc', + 'links' => [ $link, $link, '', '', '', '', '' ], &> <%init> diff --git a/httemplate/docs/license.html b/httemplate/docs/license.html index fab8cd09f..e40b2436b 100644 --- a/httemplate/docs/license.html +++ b/httemplate/docs/license.html @@ -6,7 +6,7 @@ <P> -Copyright © 2005-2009 Freeside Internet Services, Inc.<BR> +Copyright © 2005-2012 Freeside Internet Services, Inc.<BR> Copyright © 2000-2005 Ivan Kohler<BR> Copyright © 1999 Silicon Interactive Software Design<BR> All rights reserved<BR> diff --git a/httemplate/edit/discount.html b/httemplate/edit/discount.html index b195eb37b..9bcd1e724 100644 --- a/httemplate/edit/discount.html +++ b/httemplate/edit/discount.html @@ -22,6 +22,7 @@ postfix => '<BR><FONT SIZE="-1"><I>(blank for non-expiring discount)</I></FONT>', }, { field => 'setup', type => 'checkbox', value=>'Y', }, + #{ field => 'linked', type => 'checkbox', value=>'Y', }, ], 'labels' => { 'discountnum' => 'Discount #', @@ -32,6 +33,7 @@ 'percent' => 'Percentage ', 'months' => 'Duration (months)', 'setup' => 'Apply to setup fees', + #'linked' => 'Apply to add-on packages', }, 'viewall_dir' => 'browse', 'new_callback' => $new_callback, @@ -114,6 +116,10 @@ my $javascript = <<END; document.getElementById('percent_label').style.visibility = 'hidden'; document.getElementById('percent_input0').style.display = 'none'; document.getElementById('percent_input0').style.visibility = 'hidden'; +// document.getElementById('linked_label').style.display = 'none'; +// document.getElementById('linked_label').style.visibility = 'hidden'; +// document.getElementById('linked').style.display = 'none'; +// document.getElementById('linked').style.visibility = 'hidden'; } else if ( _type == 'Amount' ) { document.getElementById('amount_label').style.display = ''; document.getElementById('amount_label').style.visibility = ''; @@ -123,6 +129,10 @@ my $javascript = <<END; document.getElementById('percent_label').style.visibility = 'hidden'; document.getElementById('percent_input0').style.display = 'none'; document.getElementById('percent_input0').style.visibility = 'hidden'; +// document.getElementById('linked_label').style.display = 'none'; +// document.getElementById('linked_label').style.visibility = 'hidden'; +// document.getElementById('linked').style.display = 'none'; +// document.getElementById('linked').style.visibility = 'hidden'; } else if ( _type == 'Percentage' ) { document.getElementById('amount_label').style.display = 'none'; document.getElementById('amount_label').style.visibility = 'hidden'; @@ -132,6 +142,10 @@ my $javascript = <<END; document.getElementById('percent_label').style.visibility = ''; document.getElementById('percent_input0').style.display = ''; document.getElementById('percent_input0').style.visibility = ''; +// document.getElementById('linked_label').style.display = ''; +// document.getElementById('linked_label').style.visibility = ''; +// document.getElementById('linked').style.display = ''; +// document.getElementById('linked').style.visibility = ''; } } diff --git a/httemplate/edit/process/cust_pkg_discount.html b/httemplate/edit/process/cust_pkg_discount.html index 6f97a791e..4a71f6975 100644 --- a/httemplate/edit/process/cust_pkg_discount.html +++ b/httemplate/edit/process/cust_pkg_discount.html @@ -39,7 +39,8 @@ my $cust_pkg_discount = new FS::cust_pkg_discount { 'amount' => scalar($cgi->param('discountnum_amount')), 'percent' => scalar($cgi->param('discountnum_percent')), 'months' => scalar($cgi->param('discountnum_months')), - 'setup' => scalar($cgi->param('discountnum_setup')), + 'setup' => scalar($cgi->param('discountnum_setup')), + #'linked' => scalar($cgi->param('discountnum_linked')), #'disabled' => $self->discountnum_disabled, }; my $error = $cust_pkg_discount->insert; diff --git a/httemplate/edit/process/quick-cust_pkg.cgi b/httemplate/edit/process/quick-cust_pkg.cgi index ba4c5b1b6..c5eee0cb8 100644 --- a/httemplate/edit/process/quick-cust_pkg.cgi +++ b/httemplate/edit/process/quick-cust_pkg.cgi @@ -2,19 +2,24 @@ % $cgi->param('error', $error); <% $cgi->redirect(popurl(3). 'misc/order_pkg.html?'. $cgi->query_string ) %> %} else { -% my $frag = "cust_pkg". $cust_pkg->pkgnum; % my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/ % ? '' % : ';show=packages'; -% my $redir_url = popurl(3) -% ."view/cust_main.cgi?custnum=$custnum$show;fragment=$frag#$frag"; +% +% my $redir_url = popurl(3); +% if ( $svcpart ) { # for going straight to service provisining after ordering +% $redir_url .= 'edit/'.$part_svc->svcdb.'.cgi?'. +% 'pkgnum='.$cust_pkg->pkgnum. ";svcpart=$svcpart"; +% $redir_url .= ";qualnum=$qualnum" if $qualnum; +% } elsif ( $quotationnum ) { +% $redir_url .= "view/quotation.html?quotationnum=$quotationnum"; +% } else { +% my $custnum = $cust_main->custnum; +% my $frag = "cust_pkg". $cust_pkg->pkgnum; +% $redir_url .= +% "view/cust_main.cgi?custnum=$custnum$show;fragment=$frag#$frag"; +% } % -% # for going right to a provision service after ordering a package -% if ( $svcpart ) { -% $redir_url = popurl(3)."edit/".$part_svc->svcdb.".cgi?". -% "pkgnum=".$cust_pkg->pkgnum. ";svcpart=$svcpart"; -% $redir_url .= ";qualnum=$qualnum" if $qualnum; -% } <% header('Package ordered') %> <SCRIPT TYPE="text/javascript"> // XXX fancy ajax rebuild table at some point, but a page reload will do for now @@ -33,16 +38,27 @@ my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->access_right('Order customer package'); -#untaint custnum (probably not necessary, searching for it is escape enough) -$cgi->param('custnum') =~ /^(\d+)$/ - or die 'illegal custnum '. $cgi->param('custnum'); -my $custnum = $1; -my $cust_main = qsearchs({ - 'table' => 'cust_main', - 'hashref' => { 'custnum' => $custnum }, - 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, -}); -die 'unknown custnum' unless $cust_main; +my $cust_main; +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + my $custnum = $1; + $cust_main = qsearchs({ + 'table' => 'cust_main', + 'hashref' => { 'custnum' => $custnum }, + 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, + }); +} + +my $prospect_main; +if ( $cgi->param('prospectnum') =~ /^(\d+)$/ ) { + my $prospectnum = $1; + $prospect_main = qsearchs({ + 'table' => 'prospect_main', + 'hashref' => { 'prospectnum' => $prospectnum }, + 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, + }); +} + +die 'no custnum or prospectnum' unless $cust_main || $prospect_main; #probably not necessary, taken care of by cust_pkg::check $cgi->param('pkgpart') =~ /^(\d+)$/ @@ -72,47 +88,70 @@ if ( $cgi->param('svcpart') ) { } my $qualnum = ''; -if ( $cgi->param('qualnum') ) { - $cgi->param('qualnum') =~ /^(\d+)$/ or die 'illegal qualnum'; +if ( $cgi->param('qualnum') =~ /^(\d+)$/ ) { $qualnum = $1; } +my $quotationnum = ''; +if ( $cgi->param('quotationnum') =~ /^(\d+)$/ ) { + $quotationnum = $1; +} +# verify this quotation is visible to this user +my $cust_pkg = ''; +my $quotation_pkg = ''; +my $error = ''; -my $cust_pkg = new FS::cust_pkg { - 'custnum' => $custnum, - 'pkgpart' => $pkgpart, - 'quantity' => $quantity, - 'start_date' => ( scalar($cgi->param('start_date')) - ? parse_datetime($cgi->param('start_date')) - : '' - ), - 'no_auto' => scalar($cgi->param('no_auto')), - 'refnum' => $refnum, - 'locationnum' => $locationnum, - 'discountnum' => $discountnum, - #for the create a new discount case - 'discountnum__type' => scalar($cgi->param('discountnum__type')), - 'discountnum_amount' => scalar($cgi->param('discountnum_amount')), - 'discountnum_percent' => scalar($cgi->param('discountnum_percent')), - 'discountnum_months' => scalar($cgi->param('discountnum_months')), - 'discountnum_setup' => scalar($cgi->param('discountnum_setup')), - 'contract_end' => ( scalar($cgi->param('contract_end')) - ? parse_datetime($cgi->param('contract_end')) - : '' - ), - 'waive_setup' => ( $cgi->param('waive_setup') eq 'Y' ? 'Y' : '' ), -}; - -my %opt = ( 'cust_pkg' => $cust_pkg ); - -if ( $locationnum == -1 ) { - my $cust_location = new FS::cust_location { - map { $_ => scalar($cgi->param($_)) } - qw( custnum address1 address2 city county state zip country geocode ) - }; - $opt{'cust_location'} = $cust_location; -} +my %hash = ( + 'pkgpart' => $pkgpart, + 'quantity' => $quantity, + 'start_date' => ( scalar($cgi->param('start_date')) + ? parse_datetime($cgi->param('start_date')) + : '' + ), + 'refnum' => $refnum, + 'locationnum' => $locationnum, + 'discountnum' => $discountnum, + #for the create a new discount case + 'discountnum__type' => scalar($cgi->param('discountnum__type')), + 'discountnum_amount' => scalar($cgi->param('discountnum_amount')), + 'discountnum_percent' => scalar($cgi->param('discountnum_percent')), + 'discountnum_months' => scalar($cgi->param('discountnum_months')), + 'discountnum_setup' => scalar($cgi->param('discountnum_setup')), + 'contract_end' => ( scalar($cgi->param('contract_end')) + ? parse_datetime($cgi->param('contract_end')) + : '' + ), + 'waive_setup' => ( $cgi->param('waive_setup') eq 'Y' ? 'Y' : '' ), +); +$hash{'custnum'} = $cust_main->custnum if $cust_main; + +if ( $quotationnum ) { + + $quotation_pkg = new FS::quotation_pkg \%hash; + $quotation_pkg->quotationnum($quotationnum); + $quotation_pkg->prospectnum($prospect_main->prospectnum) if $prospect_main; -my $error = $cust_main->order_pkg( \%opt ); + #XXX handle new location + $error = $quotation_pkg->insert; + +} else { + + $cust_pkg = new FS::cust_pkg \%hash; + + $cust_pkg->no_auto( scalar($cgi->param('no_auto')) ); + + my %opt = ( 'cust_pkg' => $cust_pkg ); + + if ( $locationnum == -1 ) { + my $cust_location = new FS::cust_location { + map { $_ => scalar($cgi->param($_)) } + qw( custnum address1 address2 city county state zip country geocode ) + }; + $opt{'cust_location'} = $cust_location; + } + + $error = $cust_main->order_pkg( \%opt ); + +} </%init> diff --git a/httemplate/edit/process/svc_broadband.cgi b/httemplate/edit/process/svc_broadband.cgi index 90eab4aad..25644e547 100644 --- a/httemplate/edit/process/svc_broadband.cgi +++ b/httemplate/edit/process/svc_broadband.cgi @@ -1,11 +1,10 @@ <& elements/svc_Common.html, - table => 'svc_broadband', - fields => [ fields('svc_broadband'), fields('nas'), 'usergroup' ], + table => 'svc_broadband', + fields => [ fields('svc_broadband'), fields('nas'), 'usergroup' ], precheck_callback => \&precheck, &> <%init> -# for historical reasons, process_m2m for usergroup tables is done -# in the svc_x::insert/replace/delete methods, not here + my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" diff --git a/httemplate/edit/radius_group.html b/httemplate/edit/radius_group.html index 0c99b4c4c..d3ef40c5d 100644 --- a/httemplate/edit/radius_group.html +++ b/httemplate/edit/radius_group.html @@ -7,6 +7,8 @@ 'description' => 'Description', 'attrnum' => 'Attribute', 'priority' => 'Priority', + 'speed_down' => 'Download speed', + 'speed_up' => 'Upload speed', }, 'viewall_dir' => 'browse', 'menubar' => \@menubar, @@ -28,6 +30,16 @@ 'size' => 2, 'colspan' => 6, # just to not interfere with radius_attr columns }, + { 'field' => 'speed_down', + 'type' => 'text', + 'size' => 8, + 'colspan' => 6, + }, + { 'field' => 'speed_up', + 'type' => 'text', + 'size' => 8, + 'colspan' => 6, + }, { 'field' => 'attrnum', 'type' => 'radius_attr', diff --git a/httemplate/elements/tr-select-discount.html b/httemplate/elements/tr-select-discount.html index 30a60ec85..ee862519f 100644 --- a/httemplate/elements/tr-select-discount.html +++ b/httemplate/elements/tr-select-discount.html @@ -6,7 +6,7 @@ % } else { <TR> - <TD ALIGN="right" WIDTH="176"><% $opt{'label'} || '<B>'.emt('Discount').'</B>' %></TD> + <TD ALIGN="right" WIDTH="275"><% $opt{'label'} || '<B>'.emt('Discount').'</B>' %></TD> <TD <% $colspan %>> <% include( '/elements/select-discount.html', 'curr_value' => $discountnum, @@ -74,6 +74,16 @@ ) %> +%# <% include( '/elements/tr-checkbox.html', +%# 'label' => '<B>Apply discount to add-on packages</B>', +%# 'field' => $name.'_linked', +%# 'id' => $name.'_linked', +%# 'curr_value' => scalar($cgi->param($name.'_linked')), +%# 'value' => 'Y', +%# 'colspan' => $opt{'colspan'}, +%# ) +%# %> + <SCRIPT TYPE="text/javascript"> % my $ge = 'document.getElementById'; @@ -136,6 +146,10 @@ <% $ge %>('<% $name %>_percent_label0').style.visibility = 'hidden'; <% $ge %>('<% $name %>_percent_input0').style.display = 'none'; <% $ge %>('<% $name %>_percent_input0').style.visibility = 'hidden'; +// <% $ge %>('<% $name %>_linked_label0').style.display = 'none'; +// <% $ge %>('<% $name %>_linked_label0').style.visibility = 'hidden'; +// <% $ge %>('<% $name %>_linked').style.display = 'none'; +// <% $ge %>('<% $name %>_linked').style.visibility = 'hidden'; } else if ( <% $name %>__type == 'Amount' ) { <% $ge %>('<% $name %>_amount_label0').style.display = ''; <% $ge %>('<% $name %>_amount_label0').style.visibility = ''; @@ -145,6 +159,11 @@ <% $ge %>('<% $name %>_percent_label0').style.visibility = 'hidden'; <% $ge %>('<% $name %>_percent_input0').style.display = 'none'; <% $ge %>('<% $name %>_percent_input0').style.visibility = 'hidden'; + <% $ge %>('<% $name %>_percent_input0').style.visibility = 'hidden'; +// <% $ge %>('<% $name %>_linked_label0').style.display = 'none'; +// <% $ge %>('<% $name %>_linked_label0').style.visibility = 'hidden'; +// <% $ge %>('<% $name %>_linked').style.display = 'none'; +// <% $ge %>('<% $name %>_linked').style.visibility = 'hidden'; } else if ( <% $name %>__type == 'Percentage' ) { <% $ge %>('<% $name %>_amount_label0').style.display = 'none'; <% $ge %>('<% $name %>_amount_label0').style.visibility = 'hidden'; @@ -154,6 +173,11 @@ <% $ge %>('<% $name %>_percent_label0').style.visibility = ''; <% $ge %>('<% $name %>_percent_input0').style.display = ''; <% $ge %>('<% $name %>_percent_input0').style.visibility = ''; + <% $ge %>('<% $name %>_percent_input0').style.visibility = ''; +// <% $ge %>('<% $name %>_linked_label0').style.display = ''; +// <% $ge %>('<% $name %>_linked_label0').style.visibility = ''; +// <% $ge %>('<% $name %>_linked').style.display = ''; +// <% $ge %>('<% $name %>_linked').style.visibility = ''; } } diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index b6b70a080..4c0fa4a56 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -3,14 +3,10 @@ 'name' => emt('line items'), 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], + 'count_addl' => \@total_desc, 'header' => [ emt('Description'), - emt('Setup charge'), - ( $use_usage eq 'usage' - ? emt('Usage charge') - : emt('Recurring charge') - ), + @peritem_desc, emt('Invoice'), emt('Date'), FS::UI::Web::cust_header(), @@ -22,41 +18,21 @@ }, #strikethrough or "N/A ($amount)" or something these when # they're not applicable to pkg_tax search - sub { my $cust_bill_pkg = shift; - sprintf($money_char.'%.2f', $cust_bill_pkg->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 ); - }, + @peritem_sub, 'invnum', sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, ], 'sort_fields' => [ '', - 'setup', - ( $use_usage eq 'recurring' - ? 'recur - usage' : - $use_usage eq 'usage' - ? 'usage' - : 'recur' - ), + @peritem, 'invnum', '_date', ], 'links' => [ #'', '', - '', - '', + @peritem_null, $ilink, $ilink, ( map { $_ ne 'Cust. Status' ? $clink : '' } @@ -64,15 +40,14 @@ ), ], #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), - 'align' => 'lr'. - 'r'. + 'align' => 'l'. + $peritem_align. 'rc'. FS::UI::Web::cust_aligns(), 'color' => [ #'', '', - '', - '', + @peritem_null, '', '', FS::UI::Web::cust_colors(), @@ -80,37 +55,126 @@ 'style' => [ #'', '', - '', - '', + @peritem_null, '', '', FS::UI::Web::cust_styles(), ], &> -<%init> +<%doc> + +Output 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 + 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 + will include usage charges also. + +Filtering parameters: +- begin, end: Date range. Applies to invoice date, not necessarily package + date range. But see "distribute". + +- status: Customer status (active, suspended, etc.). This will filter on + _current_ customer status, not status at the time the invoice was generated. + +- agentnum: Filter on customer agent. + +- refnum: Filter on customer reference source. + +- classnum: Filter on package class. + +- 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). + +- istax: Limit to items that are taxes (pkgnum == 0). + +- 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. + +- country, state, county, city: Limit to items whose tax location + matches these fields. If "nottax" it's the tax location of the package; + if "istax" the location of the tax. + +- taxname, taxnameNULL: With "nottax", limit to items whose tax location + matches a tax with this name. With "istax", limit to items that have + this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname + = 'Tax'". + +- out: With "nottax", limit to items that don't match any tax definition. + With "istax", find tax items that are unlinked to their tax definitions. + Current Freeside (> July 2012) always creates tax links, but unlinked + items may result from an incomplete upgrade of legacy data. + +- locationtaxid: With "nottax", limit to packages matching this + tax_rate_location ID; with "tax", limit to taxes generated from that + location. + +- taxclass: Filter on package taxclass. + +- taxclassNULL: With "nottax", limit to items that would be subject to the + tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass + is NULL; it also includes taxclasses that don't have a tax in this region. + +- itemdesc: Limit to line items with this description. Note that non-tax + packages usually have a description of NULL. (Deprecated.) + +- report_group: Can contain '=' or '!=' followed by a string to limit to + line items where itemdesc starts with, or doesn't start with, the string. + +- cust_tax: Limit to customers who are tax-exempt. If "taxname" is also + specified, limit to customers who are also specifically exempt from that + tax. + +- pkg_tax: Limit to packages that are tax-exempt, and only include the + exempt portion (setup, recurring, or both) when calculating totals. + +- taxable: Limit to packages that are subject to tax, i.e. where a + cust_bill_pkg_tax_location record exists. -#LOTS of false laziness below w/cust_credit_bill_pkg.cgi +- credit: Limit to line items that received a credit application. The + amount of the credit will also be shown. + +</%doc> +<%init> die "access denied" unless $FS::CurrentUser::CurrentUser->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; + +# valid in both the tax and non-tax cases +$join_cust = + " LEFT JOIN cust_bill USING (invnum) + LEFT JOIN cust_main USING (custnum) + "; -#here is the agent virtualization +#agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' ); my @where = ( $agentnums_sql ); +# date range my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { - push @where, FS::cust_main->cust_status_sql . " = '$1'"; -} - if ( $cgi->param('distribute') == 1 ) { push @where, "sdate <= $ending", "edate > $beginning", @@ -121,381 +185,371 @@ else { "cust_bill._date <= $ending"; } +# status +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + push @where, FS::cust_main->cust_status_sql . " = '$1'"; +} + +# agentnum if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } +# refnum if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { push @where, "cust_main.refnum = $1"; } -#classnum -# not specified: all classes -# 0: empty class -# N: classnum -my $use_override = $cgi->param('use_override'); -if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { - my $comparison = ''; - if ( $1 == 0 ) { - $comparison = "IS NULL"; - } else { - $comparison = "= $1"; - } +# the non-tax case +if ( $cgi->param('nottax') ) { - if ( $use_override ) { - push @where, "( - part_pkg.classnum $comparison AND pkgpart_override IS NULL OR - override.classnum $comparison AND pkgpart_override IS NOT NULL + push @where, 'cust_bill_pkg.pkgnum > 0'; + + # 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 )"; - } else { - push @where, "part_pkg.classnum $comparison"; + $part_pkg = 'override'; } -} + push @select, 'part_pkg.pkg'; # or should this use override? -if ( $cgi->param('taxclass') - && ! $cgi->param('istax') #no part_pkg.taxclass in this case - #(should we save a taxclass or a link to taxnum - # in cust_bill_pkg or something like - # cust_bill_pkg_tax_location?) - ) -{ + my @tax_where; # will go into a subquery + my @exempt_where; # will also go into a subquery - #override taxclass when use_override is specified? probably + # classnum (of override pkgpart if applicable) + # not specified: all classes + # 0: empty class + # N: classnum + if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { + push @where, "COALESCE($part_pkg.classnum, 0) = $1"; + } - push @where, ' part_pkg.taxclass IN ( '. - join(', ', map dbh->quote($_), $cgi->param('taxclass') ). - ' ) '; + # taxclass + if ( $cgi->param('taxclassNULL') ) { + # a little different from 'taxclass' in that it applies to the + # 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 (" . + join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ). + ')'; + } -} + if ( $cgi->param('exempt_cust') eq 'Y' ) { + # tax-exempt customers + push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')"; -my @loc_param = qw( district city county state country ); + } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package + # non-taxable package charges + push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')"; + } + # we don't handle exempt_monthly here + + if ( $cgi->param('taxname') ) { # specific taxname + push @tax_where, 'cust_main_county.taxname = '. + dbh->quote($cgi->param('taxname')); + } elsif ( $cgi->param('taxnameNULL') ) { + push @tax_where, 'cust_main_county.taxname IS NULL OR '. + 'cust_main_county.taxname = \'Tax\''; + } -if ( $cgi->param('out') ) { + # country:state:county:city:district (may be repeated) + # You can also pass a big list of taxnums but that leads to huge URLs. + # Note that this means "packages whose tax is in this region", not + # "packages in this region". It's meant for links from the tax report. + if ( $cgi->param('region') ) { + my @orwhere; + foreach ( $cgi->param('region') ) { + my %loc; + @loc{qw(country state county city district)} = + split(':', $cgi->param('region')); + my $string = join(' AND ', + map { + if ( $loc{$_} ) { + "$_ = ".dbh->quote($loc{$_}); + } else { + "$_ IS NULL"; + } + } keys(%loc) + ); + push @orwhere, "($string)"; + } + push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere; + } - my ( $loc_sql, @param ) = FS::cust_location->in_county_sql( 'ornull' => 1 ); -# while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution -# $loc_sql =~ s/\?/'cust_main_county.'.shift(@param)/e; -# } + # 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; + } - push @where, " - 0 = ( - SELECT COUNT(*) FROM cust_main_county - WHERE cust_main_county.tax > 0 - AND $loc_sql - ) - "; + # If we're showing exempt items, we need to find those with + # cust_tax_exempt_pkg records matching the selected taxes. + # If we're showing taxable items, we need to find those with + # cust_bill_pkg_tax_location records. We also need to find the + # exemption records so that we can show the taxable amount. + # If we're showing all items, we need the union of those. + # If we're showing 'out' (items that aren't region/class taxable), + # then we need the set of all items minus the union of those. - #not linked to by anything, but useful for debugging "out of taxable region" - if ( grep $cgi->param($_), @loc_param ) { + my $exempt_sub; - my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param; + if ( @exempt_where or @tax_where + or $cgi->param('taxable') or $cgi->param('out') ) + { + # process exemption restrictions, including @tax_where + my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum + FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)'; - my ( $loc_sql, @param ) = FS::cust_location->in_county_sql(param => 1); - while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution - $loc_sql =~ s/\?/$ph{shift(@param)}/e; - } + $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) + if (@tax_where or @exempt_where); - push @where, $loc_sql; + $exempt_sub .= ' GROUP BY billpkgnum'; + $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_main_county.tax > 0'; + + my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum + 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)"; } -} elsif ( $cgi->param('country') ) { # and not $cgi->param('out') + # now do something with that + if ( @exempt_where ) { - my @counties = $cgi->param('county'); - - if ( scalar(@counties) > 1 ) { + push @where, 'item_exempt.billpkgnum IS NOT NULL'; + push @select, 'item_exempt.exempt_amount'; + push @peritem, 'exempt_amount'; + push @peritem_desc, 'Exempt'; + push @total, 'SUM(exempt_amount)'; + push @total_desc, "$money_char%.2f tax-exempt"; - #hacky, could be more efficient. care if it is ever used for more than the - # tax-report_groups filtering kludge + } elsif ( $cgi->param('taxable') ) { - my $locs_sql = - ' ( '. join(' OR ', map { + my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. + '- COALESCE(item_exempt.exempt_amount, 0)'; - my %ph = ( 'county' => dbh->quote($_), - map { $_ => dbh->quote( $cgi->param($_) ) } - qw( district city state country ) - ); + push @where, 'item_tax.invnum IS NOT NULL'; + push @select, "($taxable) AS taxable_amount"; + push @peritem, 'taxable_amount'; + push @peritem_desc, 'Taxable'; + push @total, "SUM($taxable)"; + push @total_desc, "$money_char%.2f taxable"; - my ( $loc_sql, @param ) = FS::cust_location->in_county_sql(param => 1); - while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution - $loc_sql =~ s/\?/$ph{shift(@param)}/e; - } + } elsif ( $cgi->param('out') ) { + + push @where, 'item_tax.invnum IS NULL', + 'item_exempt.billpkgnum IS NULL'; - $loc_sql; + } elsif ( @tax_where ) { - } @counties + # union of taxable + all exempt_ cases + push @where, + '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)'; - ). ' ) '; + } - push @where, $locs_sql; + # recur/usage separation + $use_usage = $cgi->param('usage'); + if ( $use_usage eq 'recurring' ) { - } else { #scalar(@counties) <= 1 + 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)'; - my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param; + } elsif ( $use_usage eq 'usage' ) { - - my ( $loc_sql, @param ) = FS::cust_location->in_county_sql(param => 1); - while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution - $loc_sql =~ s/\?/$ph{shift(@param)}/e; - } + my $usage = FS::cust_bill_pkg->usage_sql(); + push @select, "($usage) AS _usage"; + # there's already a method named 'usage' + $peritem[1] = '_usage'; + $peritem_desc[1] = 'Usage charge'; + $total[1] = "SUM($usage)"; + $total_desc[1] .= ' usage charges'; + } - push @where, $loc_sql; +} elsif ( $cgi->param('istax') ) { - } - - if ( $cgi->param('istax') ) { - if ( $cgi->param('taxname') ) { - push @where, 'itemdesc = '. dbh->quote( $cgi->param('taxname') ); - #} elsif ( $cgi->param('taxnameNULL') { - } else { - push @where, "( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - } - } elsif ( $cgi->param('nottax') ) { - #what can we usefully do with "taxname" ???? look up a class??? - } else { - #warn "neither nottax nor istax parameters specified"; - } + @peritem = ( 'setup' ); # taxes only have setup + @peritem_desc = ( 'Tax charge' ); - if ( $cgi->param('taxclassNULL') - && ! $cgi->param('istax') #no part_pkg.taxclass in this case - #(see comment above?) - ) - { - my %hash = ( 'country' => scalar($cgi->param('country')) ); - foreach (qw( state county )) { - $hash{$_} = scalar($cgi->param($_)) if $cgi->param($_); - } - my $cust_main_county = qsearchs('cust_main_county', \%hash); - die "unknown base region for empty taxclass" unless $cust_main_county; + push @where, 'cust_bill_pkg.pkgnum = 0'; - my $same_sql = $cust_main_county->sql_taxclass_sameregion; - $same_sql =~ s/taxclass/part_pkg.taxclass/g; - push @where, $same_sql if $same_sql; + # tax location when using tax_rate_location + if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { - } + $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 ) + ); -} elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { -# and not $cgi->param('out' or 'country') + $total[1] = 'SUM( + COALESCE(cust_bill_pkg_tax_rate_location.amount, + cust_bill_pkg.setup + cust_bill_pkg.recur) + )'; - push @where, FS::tax_rate_location->location_sql( - map { $_ => (scalar($cgi->param($_)) || '') } - qw( district city county state locationtaxid ) - ); + } elsif ( $cgi->param('out') ) { -} + $join_pkg = ' + LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) + '; + push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL'; -if ( $cgi->param('itemdesc') ) { - if ( $cgi->param('itemdesc') eq 'Tax' ) { - push @where, "(itemdesc='Tax' OR itemdesc is null)"; - } else { - push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc')); + # each billpkgnum should appear only once + $total[0] = 'COUNT(*)'; + $total[1] = 'SUM(cust_bill_pkg.setup)'; + + } else { # not locationtaxid or 'out'--the normal case + + $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)'; } -} -if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ && $cgi->param('istax') ) { - 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"; + # taxclass + if ( $cgi->param('taxclassNULL') ) { + push @where, 'cust_main_county.taxclass IS NULL'; } - -} -push @where, 'cust_bill_pkg.pkgnum != 0' if $cgi->param('nottax'); -push @where, 'cust_bill_pkg.pkgnum = 0' if $cgi->param('istax'); - -if ( $cgi->param('cust_tax') ) { - #false laziness -ish w/report_tax.cgi - my $cust_exempt; - if ( $cgi->param('taxname') ) { - my $q_taxname = dbh->quote($cgi->param('taxname')); - $cust_exempt = - "( tax = 'Y' - OR EXISTS ( SELECT 1 FROM cust_main_exemption - WHERE cust_main_exemption.custnum = cust_main.custnum - AND cust_main_exemption.taxname = $q_taxname ) - ) - "; - } else { - $cust_exempt = " tax = 'Y' "; + # taxname + if ( $cgi->param('taxnameNULL') ) { + push @where, 'cust_main_county.taxname IS NULL OR '. + 'cust_main_county.taxname = \'Tax\''; + } elsif ( $cgi->param('taxname') ) { + push @where, 'cust_main_county.taxname = '. + dbh->quote($cgi->param('taxname')); } - push @where, $cust_exempt; -} + # 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; + } -my $use_usage = $cgi->param('use_usage'); - -my $count_query; -if ( $cgi->param('pkg_tax') ) { - - $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') ) { - - 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 )", # assume this was intended? - #not covered in full by a monthly tax exemption (texas tax) - "0 < ( $setup_taxable + $recur_taxable - $exempt )"; - -} else { - - if ( $use_usage ) { - $count_query = "SELECT COUNT(*), "; - } else { - $count_query = "SELECT COUNT(DISTINCT billpkgnum), "; + # 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"; + } } - if ( $use_usage eq 'recurring' ) { - $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - usage)"; - } elsif ( $use_usage eq 'usage' ) { - $count_query .= "SUM(usage)"; - } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { - $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))"; - } elsif ( $cgi->param('iscredit') eq 'rate') { - $count_query .= "SUM( cust_credit_bill_pkg.amount )"; - } else { - $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; + # itemdesc, for some reason + if ( $cgi->param('itemdesc') ) { + if ( $cgi->param('itemdesc') eq 'Tax' ) { + push @where, "(itemdesc='Tax' OR itemdesc is null)"; + } else { + push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc')); + } } -} +} # nottax / istax -$join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +# credit +if ( $cgi->param('credit') ) { -if ( $cgi->param('nottax') ) { + my $credit_sub; - $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 - LEFT JOIN cust_location - ON cust_location.locationnum = '. - FS::cust_pkg->tax_locationnum_sql; + if ( $cgi->param('istax') ) { + # then we need to group/join by billpkgtaxlocationnum, to get only the + # relevant part of partial taxes + my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount, + reason.reason as reason_text, access_user.username AS username_text, + billpkgtaxlocationnum, billpkgnum + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_credit USING (crednum) + LEFT JOIN reason USING (reasonnum) + LEFT JOIN access_user USING (usernum) + GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason, + access_user.username"; + + if ( $cgi->param('out') ) { + + # find credits that are applied to the line items, but not to + # a cust_bill_pkg_tax_location link + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit + USING (billpkgnum)"; + push @where, 'item_credit.billpkgtaxlocationnum IS NULL'; -} elsif ( $cgi->param('istax') ) { + } else { - #false laziness w/report_tax.cgi $taxfromwhere - if ( scalar( grep( /locationtaxid/, $cgi->param ) ) || - $cgi->param('iscredit') eq 'rate') { + # find credits that are applied to the CBPTL links that are + # considered "interesting" by the report criteria + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit + USING (billpkgtaxlocationnum)"; - # using tax_rate_location and friends - $join_pkg .= - ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. - ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; + } - #} elsif ( $conf->exists('tax-pkg_address') ) { } else { - - # using cust_bill_pkg_tax_location to relate tax items to locations - # ...but for consolidated taxes we don't want to duplicate this - my $tax_item_location = '(SELECT DISTINCT billpkgnum, locationnum - FROM cust_bill_pkg_tax_location) AS tax_item_location'; - - $join_pkg .= " LEFT JOIN $tax_item_location USING ( billpkgnum ) - LEFT JOIN cust_location - ON tax_item_location.locationnum = - cust_location.locationnum "; - - #quelle kludge, somewhat false laziness w/report_tax.cgi - s/cust_pkg\.locationnum/tax_item_location.locationnum/g for @where; - } - - if ( $cgi->param('iscredit') ) { - $join_pkg .= ' JOIN cust_credit_bill_pkg 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"; - } else { - $join_pkg .= ' )'; - push @where, "billpkgtaxratelocationnum IS NULL"; - } + # then only group by billpkgnum + my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount, + reason.reason as reason_text, access_user.username AS username_text, + billpkgnum + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_credit USING (crednum) + LEFT JOIN reason USING (reasonnum) + LEFT JOIN access_user USING (usernum) + GROUP BY billpkgnum, reason.reason, access_user.username"; + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)"; } -} else { - - #die? - warn "neither nottax nor istax parameters specified"; - #same as before? - $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) '; - -} - -my $where = ' WHERE '. join(' AND ', @where); - -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_bill_pkg $join_cust $join_pkg $where"; -} + push @where, 'item_credit.billpkgnum IS NOT NULL'; + push @select, 'item_credit.credit_amount', + 'item_credit.username_text', + 'item_credit.reason_text'; + push @peritem, 'credit_amount', 'username_text', 'reason_text'; + push @peritem_desc, 'Credited', 'By', 'Reason'; + push @total, 'SUM(credit_amount)'; + push @total_desc, "$money_char%.2f credited"; +} # if credit -push @select, 'part_pkg.pkg', - 'part_pkg.freq', - unless $cgi->param('istax'); +push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); -push @select, 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(); +my $where = join(' AND ', @where); +$where &&= "WHERE $where"; my $query = { 'table' => 'cust_bill_pkg', @@ -503,25 +557,31 @@ my $query = { 'hashref' => {}, 'select' => join(",\n", @select ), 'extra_sql' => $where, - 'order_by' => 'ORDER BY cust_bill._date, billpkgnum', + 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum', }; +my $count_query = + 'SELECT ' . join(',', @total) . + " FROM cust_bill_pkg $join_cust $join_pkg + $where"; + +shift @total_desc; #the first one is implicit + +@peritem_desc = map {emt($_)} @peritem_desc; +my @peritem_sub = map { + my $field = $_; + if ($field =~ /_text$/) { # kludge for credit reason/username fields + sub {$_[0]->get($field)}; + } else { + sub { sprintf($money_char.'%.2f', $_[0]->get($field)) } + } +} @peritem; +my @peritem_null = map { '' } @peritem; # placeholders +my $peritem_align = 'r' x scalar(@peritem); + my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - -my $owed_sub = sub { - $money_char . shift->get('owed') # owed_recur is not correct here -}; -my $payment_date_sub = sub { - #my $cust_bill_pkg = shift; - my @cust_pay = sort { $a->_date <=> $b->_date } - map $_->cust_bill_pay->cust_pay, - shift->cust_bill_pay_pkg('recur') #recur :/ - or return ''; - time2str('%b %d %Y', $cust_pay[-1]->_date ); -}; -warn $count_query; +warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n" + if $cgi->param('debug'); </%init> diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi index 3a5155ae8..1b767f846 100644 --- a/httemplate/search/cust_tax_exempt_pkg.cgi +++ b/httemplate/search/cust_tax_exempt_pkg.cgi @@ -103,7 +103,7 @@ my $join = " die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('View customer tax exemptions'); -my @where = (); +my @where = ("exempt_monthly = 'Y'"); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); if ( $beginning || $ending ) { @@ -121,6 +121,7 @@ if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { } if ( $cgi->param('out') ) { + # wtf? how would you ever get exemptions on a non-taxable package location? push @where, " 0 = ( @@ -151,6 +152,11 @@ if ( $cgi->param('out') ) { push @where, 'taxclass = '. dbh->quote( $cgi->param('taxclass') ) if $cgi->param('taxclass'); +} elsif ( $cgi->param('taxnum') ) { + + my $taxnum_in = join(',', grep /^\d+$/, $cgi->param('taxnum') ); + push @where, "taxnum IN ($taxnum_in)" if $taxnum_in; + } my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html index cf60d8f82..d7e81282b 100644 --- a/httemplate/search/elements/search-html.html +++ b/httemplate/search/elements/search-html.html @@ -350,7 +350,8 @@ % % ) { % -% my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; +%# my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; +% my $class = 'grid'; % % my $align = $aligns ? shift @$aligns : ''; % $align = " ALIGN=$align" if $align; diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 2786f571b..42a52d154 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -60,9 +60,9 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea % my $link = ''; % if ( $region->{'label'} eq $out ) { % $link = ';out=1'; -% } else { -% $link = ';'. $region->{'url_param'} -% if $region->{'url_param'}; +% } elsif ( $region->{'taxnums'} ) { +% # might be nicer to specify this as country:state:city +% $link = ';'.join(';', map { "taxnum=$_" } @{ $region->{'taxnums'} }); % } % % if ( $bgcolor eq $bgcolor1 ) { @@ -71,15 +71,12 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea % $bgcolor = $bgcolor1; % } % -% #my $diff = 0; % my $hicolor = $bgcolor; % unless ( $cgi->param('show_taxclasses') ) { % my $diff = abs( sprintf( '%.2f', $region->{'owed'} ) % - sprintf( '%.2f', $region->{'tax'} ) % ); % if ( $diff > 0.02 ) { -% # $hicolor = $hicolor eq '#eeeeee' ? '#eeee66' : '#ffff99'; -% #} elsif ( $diff ) { % $hicolor = $hicolor eq '#eeeeee' ? '#eeee99' : '#ffffcc'; % } % } @@ -94,16 +91,19 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <<%$td%>><% $region->{'label'} %></TD> <<%$td%> ALIGN="right"> <A HREF="<% $baselink. $link %>;nottax=1" - ><% &$money_sprintf( $region->{'total'} ) %></A> + ><% &$money_sprintf( $region->{'sales'} ) %></A> </TD> +% if ( $region->{'label'} eq $out ) { + <<%$td%> COLSPAN=12></TD> +% } else { #not $out <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD> <<%$td%> ALIGN="right"> - <A HREF="<% $baselink. $link %>;nottax=1;cust_tax=Y" + <A HREF="<% $baselink. $link %>;nottax=1;exempt_cust=Y" ><% &$money_sprintf( $region->{'exempt_cust'} ) %></A> </TD> <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD> <<%$td%> ALIGN="right"> - <A HREF="<% $baselink. $link %>;nottax=1;pkg_tax=Y" + <A HREF="<% $baselink. $link %>;nottax=1;exempt_pkg=Y" ><% &$money_sprintf( $region->{'exempt_pkg'} ) %></A> </TD> <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD> @@ -122,12 +122,24 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'owed'} ) %> </TD> - -% unless ( $cgi->param('show_taxclasses') ) { +% } # if !$out +% unless ( $cgi->param('show_taxclasses') ) { % my $invlink = $region->{'url_param_inv'} % ? ';'. $region->{'url_param_inv'} % : $link; +% if ( $region->{'label'} eq $out ) { + <<%$td%> ALIGN="right"> + <A HREF="<% $baselink. $invlink %>;istax=1" + ><% &$money_sprintf_nonzero( $region->{'tax'} ) %></A> + </TD> + <<%$td%>></TD> + <<%$td%> ALIGN="right"> + <A HREF="<% $creditlink. $invlink %>;istax=1" + ><% &$money_sprintf_nonzero( $region->{'credit'} ) %></A> + </TD> + <<%$td%> COLSPAN=2></TD> +% } else { #not $out <<%$tdh%> ALIGN="right"> <A HREF="<% $baselink. $invlink %>;istax=1" ><% &$money_sprintf( $region->{'tax'} ) %></A> @@ -141,7 +153,8 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> </TD> -% } +% } +% } # not $out </TR> % } @@ -190,6 +203,18 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <TR> <<%$td%>><% $region->{'label'} %></TD> +% if ( $region->{'label'} eq $out ) { + <<%$td%> ALIGN="right"> + <A HREF="<% $baselink. $invlink %>;istax=1" + ><% &$money_sprintf_nonzero( $region->{'tax'} ) %></A> + </TD> + <<%$td%>></TD> + <<%$td%> ALIGN="right"> + <A HREF="<% $creditlink. $invlink %>;istax=1" + ><% &$money_sprintf_nonzero( $region->{'credit'} ) %></A> + </TD> + <<%$td%> COLSPAN=2></TD> +% } else { #not $out <<%$td%> ALIGN="right"> <A HREF="<% $baselink. $link %>;istax=1" ><% &$money_sprintf( $region->{'tax'} ) %></A> @@ -204,70 +229,52 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> </TD> </TR> - -% } - -% if ( $bgcolor eq $bgcolor1 ) { -% $bgcolor = $bgcolor2; -% } else { -% $bgcolor = $bgcolor1; -% } -% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor"); - - <TR> - <<%$td%>>Total</TD> - <<%$td%> ALIGN="right"> - <A HREF="<% $baselink %>;istax=1" - ><% &$money_sprintf( $tot_tax ) %></A> - </TD> - <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD> - <<%$td%> ALIGN="right"> - <A HREF="<% $creditlink %>;istax=1" - ><% &$money_sprintf( $tot_credit ) %></A> - </TD> - <<%$td%>><FONT SIZE="+1"><B> = </B></FONT></TD> - <<%$td%> ALIGN="right"> - <% &$money_sprintf( $tot_tax - $tot_credit ) %> - </TD> - </TR> +% } # if $out +% } #foreach $region </TABLE> -% } +% } # if show_taxclasses <% include('/elements/footer.html') %> <%init> -my $DEBUG = $cgi->param('debug') || 0; - die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $DEBUG = $cgi->param('debug') || 0; + my $conf = new FS::Conf; -my $user = getotaker; +my $out = 'Out of taxable region(s)'; + +my %label_opt = ( out => 1 ); #enable 'Out of Taxable Region' label +$label_opt{no_city} = 1 unless $cgi->param('show_cities'); +$label_opt{no_taxclass} = 1 unless $cgi->param('show_taxclasses'); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); my $join_cust = ' JOIN cust_bill USING ( invnum ) LEFT JOIN cust_main USING ( custnum ) '; + my $join_cust_pkg = $join_cust. ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN cust_location - ON ( cust_location.locationnum = ' . - FS::cust_pkg->tax_locationnum_sql . ' )'; + LEFT JOIN part_pkg USING ( pkgpart ) '; my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; -my $where = "WHERE _date >= $beginning AND _date <= $ending "; +# either or both of these can be used to link cust_bill_pkg to cust_main_county +my $pkg_tax = "SELECT SUM(amount) as tax_amount, invnum, taxnum, ". + "cust_bill_pkg_tax_location.pkgnum ". + "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ". + "GROUP BY billpkgnum, invnum, taxnum, cust_bill_pkg_tax_location.pkgnum"; -# this query will be run once per cust_main_county, -# or maybe once per country/state/city tuple, -# or maybe once per country/state...it's hard to say. -my ($location_sql, @base_param) = FS::cust_location->in_county_sql(param => 1); -$where .= " AND $location_sql "; +my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". + "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum"; + +my $where = "WHERE _date >= $beginning AND _date <= $ending "; +my $group = "GROUP BY cust_main_county.taxnum"; my $agentname = ''; if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -277,270 +284,188 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $where .= ' AND cust_main.agentnum = '. $agent->agentnum; } -sub gotcust { - my $table = shift; - my $prefix = @_ ? shift : ''; - " - ( $table.district = cust_main_county.district - OR cust_main_county.district = '' - OR cust_main_county.district IS NULL ) - AND ( $table.${prefix}city = cust_main_county.city - OR cust_main_county.city = '' - OR cust_main_county.city IS NULL ) - AND ( $table.${prefix}county = cust_main_county.county - OR cust_main_county.county = '' - OR cust_main_county.county IS NULL ) - AND ( $table.${prefix}state = cust_main_county.state - OR cust_main_county.state = '' - OR cust_main_county.state IS NULL ) - AND ( $table.${prefix}country = cust_main_county.country ) - "; -} - -#non-parameterized form -my $location_in_county = FS::cust_location->in_county_sql; -my $gotcust = "WHERE EXISTS( - SELECT 1 FROM cust_location WHERE $location_in_county AND disabled IS NULL +my $nottax = 'cust_bill_pkg.pkgnum != 0'; + +# one query for each column of the report +# plus separate queries for the totals row +my (%sql, %all_sql); + +# general form +my $exempt = "SELECT cust_main_county.taxnum, SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust $where AND $nottax $group"; + +my $all_exempt = "SELECT SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust $where AND $nottax"; + +# sales to tax-exempt customers +$sql{exempt_cust} = $exempt; +$sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; +$all_sql{exempt_cust} = $all_exempt; +$all_sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + +# sales of tax-exempt packages +$sql{exempt_pkg} = $exempt; +$sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; +$all_sql{exempt_pkg} = $all_exempt; +$all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; + +# monthly per-customer exemptions +$sql{exempt_monthly} = $exempt; +$sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; +$all_sql{exempt_monthly} = $all_exempt; +$all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; + +# taxable sales +$sql{taxable} = "SELECT cust_main_county.taxnum, + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum + AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + $join_cust $where AND $nottax $group"; + +# Here we're going to sum all line items that are taxable _at all_, +# under any tax. exempt_charged is the sum of all exemptions for a +# particular billpkgnum + taxnum; we take the taxnum that has the +# smallest sum of exemptions and subtract that from the charged amount. +$all_sql{taxable} = "SELECT + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(min_exempt, 0)) + FROM cust_bill_pkg + JOIN ( + SELECT invnum, pkgnum, MIN(exempt_charged) AS min_exempt + FROM ($pkg_tax) AS pkg_tax + JOIN cust_bill_pkg USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + GROUP BY invnum, pkgnum + ) AS pkg_is_taxable + USING (invnum, pkgnum) + $join_cust $where AND $nottax"; + # we don't join pkg_tax_exempt.taxnum here, because + +$sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted +$all_sql{taxable} =~ s/EXEMPT_WHERE//; + +# there isn't one for 'sales', because we calculate sales by adding up +# the taxable and exempt columns. + +# sum of billed tax: +# join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location +my $taxfrom = " FROM cust_bill_pkg + $join_cust + LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) + LEFT JOIN cust_main_county USING ( taxnum )"; + +my $istax = "cust_bill_pkg.pkgnum = 0"; +my $named_tax = "( + taxname = itemdesc + OR ( taxname IS NULL + AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' ) + ) )"; -my $out = 'Out of taxable region(s)'; -# these are actually tax labels, not regions -my %regions = (); - -# Phase 1: Taxable and exempt sales -# Collect for each cust_main_county, and assign to a bin based on label. -# Note that "label" includes city if show_cities is on, and taxclass if -# show_taxclasses is on. -foreach my $r ( qsearch({ 'table' => 'cust_main_county', - 'extra_sql' => $gotcust, - 'debug' => $DEBUG, - }) - ) -{ - warn $r->county. ' '. $r->state. ' '. $r->country. "\n" if $DEBUG > 1; - - # set up a %regions entry for this region's tax label - my $label = getlabel($r); - $regions{$label}->{'label'} = $label; - - $regions{$label}->{$_} = $r->$_() for (qw( county state country )); #taxname? - - my @url_param = qw( county state country taxname ); - push @url_param, 'city' if $cgi->param('show_cities') && $r->city(); - - $regions{$label}->{'url_param'} = - join(';', map "$_=".uri_escape($r->$_()), @url_param ); - - my @param = @base_param; - my $mywhere = $where; - - if ( $r->taxclass ) { - - $mywhere .= " AND taxclass = ? "; - push @param, 'taxclass'; - $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass); - #no, always# if $cgi->param('show_taxclasses'); - - $regions{$label}->{'taxclass'} = $r->taxclass; - - } else { - - # SQL for "taxclass doesn't match any other tax in the region" - my $same_sql = $r->sql_taxclass_sameregion; - $mywhere .= " AND $same_sql" if $same_sql; - - $regions{$label}->{'url_param'} .= ';taxclassNULL=1' - if $cgi->param('show_taxclasses') - || $same_sql; - - } - - # FROM cust_bill_pkg JOIN (whatever is needed to determine tax location) - # WHERE (matches tax location and agentnum and taxclass) - # takes parameters in @base_param, plus taxclass if there is one - my $fromwhere = "$from_join_cust_pkg $mywhere"; # AND payby != 'COMP' "; - - my $nottax = 'pkgnum != 0'; - - ## calculate total of sales (non-tax line items) for this region - - my $t_sql = - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"; - my $t = scalar_sql($r, \@param, $t_sql); - $regions{$label}->{'total'} += $t; - - #$regions{$label}->{subtotals}->{$r->taxnum} = $t; #useful debug - - ## calculate customer-exemption for this region - - #false laziness -ish w/report_tax.cgi - my $cust_exempt; - if ( $r->taxname ) { - my $q_taxname = dbh->quote($r->taxname); - $cust_exempt = - "( tax = 'Y' - OR EXISTS ( SELECT 1 FROM cust_main_exemption - WHERE cust_main_exemption.custnum = cust_main.custnum - AND cust_main_exemption.taxname = $q_taxname - ) - ) - "; - } else { - $cust_exempt = " tax = 'Y' "; - } - - my $x_cust = scalar_sql($r, \@param, - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) - $fromwhere AND $nottax AND $cust_exempt " - ); - - $regions{$label}->{'exempt_cust'} += $x_cust; - - ## calculate package-exemption for this region - - my $x_pkg = scalar_sql($r, \@param, - "SELECT 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 - ) - ) - $fromwhere - AND $nottax - AND ( - ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) - ) - AND ( tax != 'Y' OR tax IS NULL ) - " - ); - $regions{$label}->{'exempt_pkg'} += $x_pkg; - - ## calculate monthly exemption (texas tax) for this region - - # count up all the cust_tax_exempt_pkg records associated with - # the actual line items. - - my $x_monthly = scalar_sql($r, \@param, - "SELECT SUM(amount) - FROM cust_tax_exempt_pkg - JOIN cust_bill_pkg USING ( billpkgnum ) - $join_cust_pkg - $mywhere" - ); - $regions{$label}->{'exempt_monthly'} += $x_monthly; - - my $taxable = $t - $x_cust - $x_pkg - $x_monthly; - $regions{$label}->{'taxable'} += $taxable; - - $regions{$label}->{'owed'} += $taxable * ($r->tax/100); - - if ( defined($regions{$label}->{'rate'}) - && $regions{$label}->{'rate'} != $r->tax.'%' ) { - $regions{$label}->{'rate'} = 'variable'; - } else { - $regions{$label}->{'rate'} = $r->tax.'%'; - } +$sql{tax} = "SELECT cust_main_county.taxnum, + SUM(cust_bill_pkg_tax_location.amount) + $taxfrom + $where AND $istax AND $named_tax + $group"; + +$all_sql{tax} = "SELECT SUM(cust_bill_pkg.setup) + FROM cust_bill_pkg + $join_cust + $where AND $istax"; + +# sum of credits applied against billed tax +my $creditfrom = $taxfrom . + ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)'; +my $creditfromwhere = $where . + ' AND billpkgtaxratelocationnum IS NULL'; + +$sql{credit} = "SELECT cust_main_county.taxnum, + SUM(cust_credit_bill_pkg.amount) + $creditfrom + $creditfromwhere AND $istax AND $named_tax + $group"; + +$all_sql{credit} = "SELECT SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust + $where AND $istax"; + +my %data; +my %total = (owed => 0); +foreach my $k (keys(%sql)) { + my $stmt = $sql{$k}; + warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG; + my $sth = dbh->prepare($stmt); + # two columns => key/value + $sth->execute + or die "failed to execute $k query: ".$sth->errstr; + $data{$k} = +{ map { @$_ } @{ $sth->fetchall_arrayref([]) } }; + + warn "\n".$all_sql{$k}."\n" if $DEBUG; + $total{$k} = FS::Record->scalar_sql( $all_sql{$k} ); + warn Dumper($data{$k}) if $DEBUG > 1; } -warn Dumper(\%regions) if $DEBUG > 1; -# $regions{$label} now contains 'total', 'exempt_cust', 'exempt_pkg', -# 'exempt_monthly', summed over each set of regions with the same label. - -my $distinct = "country, state, county, city, district, - CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname"; -my $taxclass_distinct = - #a little bit unsure of this part... test? - #ah, it looks like it winds up being irrelevant as ->{'tax'} - # from $regions is not displayed when show_taxclasses is on - ( $cgi->param('show_taxclasses') - ? " CASE WHEN taxclass IS NULL THEN '' ELSE taxclass END " - : " '' " - )." AS taxclass"; - - -# Phase 2: invoiced/credited tax items -# Collect this data for each country/state/city/district/taxname(/taxclass). -my %qsearch = ( - 'select' => "DISTINCT $distinct, $taxclass_distinct", - 'table' => 'cust_main_county', - 'hashref' => {}, - 'extra_sql' => $gotcust, - 'debug' => $DEBUG, +# so $data{tax}, for example, is now a hash with one entry +# for each taxnum, containing the tax billed on that taxnum. + +# oddball cases: +# "out of taxable region" sales +my %out; +my $out_sales_sql = + "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM (cust_bill_pkg $join_cust) + LEFT JOIN ($pkg_tax) AS pkg_tax USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum) + $where AND $nottax + AND pkg_tax.taxnum IS NULL AND pkg_tax_exempt.taxnum IS NULL" +; + +$out_sales_sql =~ s/EXEMPT_WHERE//; + +$out{sales} = FS::Record->scalar_sql($out_sales_sql); + +# unlinked tax collected (for diagnostics) +my $out_tax_sql = + "SELECT SUM(cust_bill_pkg.setup) + FROM (cust_bill_pkg $join_cust) + LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) + $where AND $istax AND cust_bill_pkg_tax_location.billpkgnum IS NULL" +; +$out{tax} = FS::Record->scalar_sql($out_tax_sql); +# unlinked tax credited (for diagnostics) +my $out_credit_sql = + "SELECT SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust + $where AND $istax AND cust_credit_bill_pkg.billpkgtaxlocationnum IS NULL" +; +$out{credit} = FS::Record->scalar_sql($out_credit_sql); + +# all sales +$total{sales} = FS::Record->scalar_sql( + "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM cust_bill_pkg $join_cust $where AND $nottax" ); -# Join to cust_main the same as before (we need agentnum) -# but not to cust_pkg (because tax line items don't have a package) -# and then to cust_location via cust_bill_pkg_tax_location -my $taxfromwhere = "FROM cust_bill_pkg $join_cust - LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) - LEFT JOIN cust_location USING ( locationnum ) - "; -my $taxwhere = $where; - -my $creditfromwhere = $taxfromwhere. - " JOIN cust_credit_bill_pkg USING (billpkgnum, billpkgtaxlocationnum)"; - -$taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' "; -$creditfromwhere .= " $taxwhere AND billpkgtaxratelocationnum IS NULL"; #AND payby != 'COMP' "; - -#should i be a cust_main_county method or something -# yes. yes, you should. - -# $taxfromwhere: Most of a query to find cust_bill_pkg records linked to a -# customer matching a given state/county/city/district (and within the date -# range for the report). -# @base_param: A list of the fields from cust_main_county to use as parameters. - -# $_taxamount_sub: Takes a cust_main_county and returns the sum of taxes billed -# within the report period for all customers located in that county. If -# the cust_main_county has a taxname, limits to taxes with that name; otherwise -# includes all line items with pkgnum = 0 and description either 'Tax' or empty. - -my $_taxamount_sub = sub { - my $r = shift; - - #match itemdesc if necessary! - my $named_tax = - $r->taxname - ? 'AND itemdesc = '. dbh->quote($r->taxname) - : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - - my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ". - " $taxfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax"; - - scalar_sql($r, [ @base_param ], $sql ); -}; - -# $_creditamount_sub: As above, but returns the sum of credits applied - -my $_creditamount_sub = sub { - my $r = shift; - - #match itemdesc if necessary! - my $named_tax = - $r->taxname - ? 'AND itemdesc = '. dbh->quote($r->taxname) - : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - - my $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ". - " $creditfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax"; - - scalar_sql($r, [ @base_param ], $sql ); -}; - #tax-report_groups filtering my($group_op, $group_value) = ( '', '' ); if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) { ( $group_op, $group_value ) = ( $1, $2 ); } -my $group_test = sub { +my $group_test = sub { # to be applied to a tax label my $label = shift; return 1 unless $group_op; #in case we get called inadvertantly if ( $label eq $out ) { #don't display "out of taxable region" in this case @@ -554,90 +479,83 @@ my $group_test = sub { } }; +# if show_taxclasses is on, %base_regions will contain the same data +# as %regions, but with taxclasses merged together (and ignoring report_group +# filtering). +my (%regions, %base_regions); my $tot_tax = 0; my $tot_credit = 0; -#foreach my $label ( keys %regions ) { -foreach my $r ( qsearch(\%qsearch) ) { - #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; +my @loc_params = qw(country state county); +push @loc_params, qw(city district) if $cgi->param('show_cities'); - my $label = getlabel($r); - if ( $group_op ) { - next unless &{$group_test}($label); +foreach my $r ( qsearch({ 'table' => 'cust_main_county', })) { + my $taxnum = $r->taxnum; + # set up a %regions entry for this region's tax label + my $label = $r->label(%label_opt); + next if $label eq $out; + $regions{$label} ||= { label => $label }; + + $regions{$label}->{$_} = $r->get($_) foreach @loc_params; + $regions{$label}->{taxnums} ||= []; + push @{ $regions{$label}->{taxnums} }, $r->taxnum; + + my %x; # keys are data items (like 'tax', 'exempt_cust', etc.) + foreach my $k (keys %data) { + next unless exists($data{$k}->{$taxnum}); + $x{$k} = $data{$k}->{$taxnum}; + $regions{$label}->{$k} += $x{$k}; + if ( $k eq 'taxable' or $k =~ /^exempt/ ) { + $regions{$label}->{'sales'} += $x{$k}; + } } - #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' "; - #my @param = @base_param; + my $owed = $data{'taxable'}->{$taxnum} * ($r->tax/100); + $regions{$label}->{'owed'} += $owed; + $total{'owed'} += $owed; - my $x = &{$_taxamount_sub}($r); - - $regions{$label}->{'tax'} += $x; - $tot_tax += $x unless $cgi->param('show_taxclasses'); - - ## calculate credit for this region - - $x = &{$_creditamount_sub}($r); - - $regions{$label}->{'credit'} += $x; - $tot_credit += $x unless $cgi->param('show_taxclasses'); - -} - -# Phase 3: Non-taxclassed totals for invoiced/credited tax -# (If show_taxclasses is not in use, this was phase 2, but it -# displays somewhere different.) -# Don't filter by report_groups. -my %base_regions = (); -if ( $cgi->param('show_taxclasses') ) { - - $qsearch{'select'} = "DISTINCT $distinct"; - foreach my $r ( qsearch(\%qsearch) ) { - - my $x = &{$_taxamount_sub}($r); - - my $base_label = getlabel($r, 'no_taxclass'=>1 ); - $base_regions{$base_label}->{'label'} = $base_label; - - $base_regions{$base_label}->{'url_param'} = - join(';', map "$_=". uri_escape($r->$_()), - qw( county state country taxname ) - ); - - $base_regions{$base_label}->{'tax'} += $x; - $tot_tax += $x; - - ## calculate credit for this region - - $x = &{$_creditamount_sub}($r); - - $base_regions{$base_label}->{'credit'} += $x; - $tot_credit += $x; + if ( defined($regions{$label}->{'rate'}) + && $regions{$label}->{'rate'} != $r->tax.'%' ) { + $regions{$label}->{'rate'} = 'variable'; + } else { + $regions{$label}->{'rate'} = $r->tax.'%'; + } + if ( $cgi->param('show_taxclasses') ) { + my $base_label = $r->label(%label_opt, 'no_taxclass' => 1); + $base_regions{$base_label} ||= + { + label => $base_label, + tax => 0, + credit => 0, + }; + $base_regions{$base_label}->{tax} += $x{tax}; + $base_regions{$base_label}->{credit} += $x{credit}; } } -my @regions = keys %regions; +my @regions = map { $_->{label} } + sort { + ($b eq $out) <=> ($a eq $out) + or $a->{country} cmp $b->{country} + or $a->{state} cmp $b->{state} + or $a->{county} cmp $b->{county} + or $a->{city} cmp $b->{city} + } + grep { $_->{sales} > 0 or $_->{tax} > 0 or $_->{credit} > 0 } + values %regions; #tax-report_groups filtering @regions = grep &{$group_test}($_), @regions if $group_op; #calculate totals -my( $total, $tot_taxable, $tot_owed ) = ( 0, 0, 0 ); -my( $exempt_cust, $exempt_pkg, $exempt_monthly, $tot_credit ) = ( 0, 0, 0, 0 ); my %taxclasses = (); my %county = (); my %state = (); my %country = (); -foreach (@regions) { - $total += $regions{$_}->{'total'}; - $tot_taxable += $regions{$_}->{'taxable'}; - $tot_owed += $regions{$_}->{'owed'}; - $exempt_cust += $regions{$_}->{'exempt_cust'}; - $exempt_pkg += $regions{$_}->{'exempt_pkg'}; - $exempt_monthly += $regions{$_}->{'exempt_monthly'}; - $tot_credit += $regions{$_}->{'credit'}; +foreach my $label (@regions) { $taxclasses{$regions{$_}->{'taxclass'}} = 1 if $regions{$_}->{'taxclass'}; $county{$regions{$_}->{'county'}} = 1; @@ -672,29 +590,27 @@ if ( $group_op ) { #ordering @regions = map $regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } + sort { $a cmp $b } @regions; my @base_regions = map $base_regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } + sort { $a cmp $b } keys %base_regions; -#add total line -push @regions, { - 'label' => 'Total', - 'url_param' => $total_url_param, - 'url_param_inv' => $total_url_param_invoiced, - 'total' => $total, - 'exempt_cust' => $exempt_cust, - 'exempt_pkg' => $exempt_pkg, - 'exempt_monthly' => $exempt_monthly, - 'taxable' => $tot_taxable, - 'rate' => '', - 'owed' => $tot_owed, - 'tax' => $tot_tax, - 'credit' => $tot_credit, -}; +#add "Out of taxable" and total lines +%out = ( %out, + 'label' => $out, + 'rate' => '' +); +%total = ( %total, + 'label' => 'Total', + 'url_param' => $total_url_param, + 'url_param_inv' => $total_url_param_invoiced, + 'rate' => '', +); +push @regions, \%out, \%total; +push @base_regions, \%out, \%total; #-- @@ -702,69 +618,15 @@ my $money_char = $conf->config('money_char') || '$'; my $money_sprintf = sub { $money_char. sprintf('%.2f', shift ); }; - -sub getlabel { - my $r = shift; - my %opt = @_; - - my $label; - if ( - $r->tax == 0 - && ! scalar( qsearch('cust_main_county', { 'district'=> $r->district, - 'city' => $r->city, - 'county' => $r->county, - 'state' => $r->state, - 'country' => $r->country, - 'tax' => { op=>'>', value=>0 }, - } - ) - ) - - ) { - #kludge to avoid "will not stay shared" warning - my $out = 'Out of taxable region(s)'; - $label = $out; - } else { - $label = $r->country; - $label = $r->state.", $label" if $r->state; - $label = $r->county." county, $label" if $r->county; - $label = $r->city. ", $label" if $r->city && $cgi->param('show_cities'); - $label = "$label (". $r->taxclass. ")" - if $r->taxclass - && $cgi->param('show_taxclasses') - && ! $opt{'no_taxclass'}; - $label = $r->taxname. " ($label)" if $r->taxname; - } - return $label; -} - -#my %count_taxname = (); #cache -#sub count_taxname { -# my $taxname = shift; -# return $count_taxname{$taxname} if exists $count_taxname{$taxname}; -# my $sql = 'SELECT COUNT(*) FROM cust_main_county WHERE taxname = ?'; -# my $sth = dbh->prepare($sql) or die dbh->errstr; -# $sth->execute( $taxname ) -# or die "Unexpected error executing statement $sql: ". $sth->errstr; -# $count_taxname{$taxname} = $sth->fetchrow_arrayref->[0]; -#} - -#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up -#to FS::Report or FS::Record or who the fuck knows where) -sub scalar_sql { - my( $r, $param, $sql ) = @_; - #warn "$sql\n"; - my $sth = dbh->prepare($sql) or die dbh->errstr; - $sth->execute( map $r->$_(), @$param ) - or die "Unexpected error executing statement $sql: ". $sth->errstr; - $sth->fetchrow_arrayref->[0] || 0; -} +my $money_sprintf_nonzero = sub { + $_[0] == 0 ? '' : &$money_sprintf($_[0]) +}; my $dateagentlink = "begin=$beginning;end=$ending"; $dateagentlink .= ';agentnum='. $cgi->param('agentnum') if length($agentname); my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink"; my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; -my $creditlink = $p. "search/cust_credit_bill_pkg.html?$dateagentlink"; +my $creditlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;credit=1"; </%init> diff --git a/httemplate/view/cust_main/custom_content/.birthdate.html.swp b/httemplate/view/cust_main/custom_content/.birthdate.html.swp Binary files differdeleted file mode 100644 index 9571d22cf..000000000 --- a/httemplate/view/cust_main/custom_content/.birthdate.html.swp +++ /dev/null diff --git a/httemplate/view/cust_main/custom_content/.small_custview.html.swp b/httemplate/view/cust_main/custom_content/.small_custview.html.swp Binary files differdeleted file mode 100644 index a39f52dde..000000000 --- a/httemplate/view/cust_main/custom_content/.small_custview.html.swp +++ /dev/null diff --git a/httemplate/view/cust_main/custom_content/.spouse_birthdate.html.swp b/httemplate/view/cust_main/custom_content/.spouse_birthdate.html.swp Binary files differdeleted file mode 100644 index 0042012f7..000000000 --- a/httemplate/view/cust_main/custom_content/.spouse_birthdate.html.swp +++ /dev/null diff --git a/httemplate/view/cust_main/custom_content/.svc_Common.html.swp b/httemplate/view/cust_main/custom_content/.svc_Common.html.swp Binary files differdeleted file mode 100644 index 15591b96d..000000000 --- a/httemplate/view/cust_main/custom_content/.svc_Common.html.swp +++ /dev/null diff --git a/httemplate/view/cust_main/custom_content/.svc_acct.html.swp b/httemplate/view/cust_main/custom_content/.svc_acct.html.swp Binary files differdeleted file mode 100644 index e2db6d5d1..000000000 --- a/httemplate/view/cust_main/custom_content/.svc_acct.html.swp +++ /dev/null diff --git a/httemplate/view/cust_main/custom_content/.svc_hardware.html.swp b/httemplate/view/cust_main/custom_content/.svc_hardware.html.swp Binary files differdeleted file mode 100644 index 1106f9ed5..000000000 --- a/httemplate/view/cust_main/custom_content/.svc_hardware.html.swp +++ /dev/null diff --git a/httemplate/view/cust_main/custom_content/.svc_phone.html.swp b/httemplate/view/cust_main/custom_content/.svc_phone.html.swp Binary files differdeleted file mode 100644 index 79b8185e1..000000000 --- a/httemplate/view/cust_main/custom_content/.svc_phone.html.swp +++ /dev/null |