diff options
81 files changed, 2658 insertions, 1434 deletions
diff --git a/FS/FS/Misc.pm b/FS/FS/Misc.pm index 2be9ec2..297e39f 100644 --- a/FS/FS/Misc.pm +++ b/FS/FS/Misc.pm @@ -913,16 +913,6 @@ sub ocr_image { @lines; } -=item spool_formats - -Returns a list of the invoice spool formats. - -=cut - -sub spool_formats { - qw(default oneline billco bridgestone) -} - =back =head1 BUGS diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 7be8c66..6ad4b74 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -1133,7 +1133,7 @@ sub tables_hashref { # 'middle', 'varchar', 'NULL', $char_d, '', '', 'first', 'varchar', '', $char_d, '', '', 'title', 'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer - 'comment', 'varchar', 'NULL', $char_d, '', '', + 'comment', 'varchar', 'NULL', 255, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'contactnum', @@ -2688,6 +2688,8 @@ sub tables_hashref { 'groupname', 'varchar', '', $char_d, '', '', 'description', 'varchar', 'NULL', $char_d, '', '', 'priority', 'int', '', '', '1', '', + 'speed_up', 'int', 'NULL', '', '', '', + 'speed_down', 'int', 'NULL', '', '', '', ], 'primary_key' => 'groupnum', 'unique' => [ ['groupname'] ], @@ -2740,10 +2742,16 @@ sub tables_hashref { #'custnum', 'int', '', '', '', '' 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', - 'year', 'int', '', '', '', '', - 'month', 'int', '', '', '', '', + 'year', 'int', 'NULL', '', '', '', + 'month', 'int', 'NULL', '', '', '', 'creditbillpkgnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', + # exemption type flags + 'exempt_cust', 'char', 'NULL', 1, '', '', + 'exempt_setup', 'char', 'NULL', 1, '', '', + 'exempt_recur', 'char', 'NULL', 1, '', '', + 'exempt_cust_taxname', 'char', 'NULL', 1, '', '', + 'exempt_monthly', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'exemptpkgnum', 'unique' => [], @@ -2760,10 +2768,16 @@ sub tables_hashref { #'custnum', 'int', '', '', '', '' 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', - 'year', 'int', '', '', '', '', - 'month', 'int', '', '', '', '', + 'year', 'int', 'NULL', '', '', '', + 'month', 'int', 'NULL', '', '', '', 'creditbillpkgnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', + # exemption type flags + 'exempt_cust', 'char', 'NULL', 1, '', '', + 'exempt_setup', 'char', 'NULL', 1, '', '', + 'exempt_recur', 'char', 'NULL', 1, '', '', + 'exempt_cust_taxname', 'char', 'NULL', 1, '', '', + 'exempt_monthly', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'exemptpkgnum', 'unique' => [], diff --git a/FS/FS/TicketSystem/RT_Internal.pm b/FS/FS/TicketSystem/RT_Internal.pm index b09647e..01e2e29 100644 --- a/FS/FS/TicketSystem/RT_Internal.pm +++ b/FS/FS/TicketSystem/RT_Internal.pm @@ -50,7 +50,7 @@ sub access_right { sub session { my( $self, $session ) = @_; - if ( $session && $session->{'Current_User'} ) { # does this even work? + if ( $session && $session->{'CurrentUser'} ) { # does this even work? warn "$me session: using existing session and CurrentUser: \n". Dumper($session->{'CurrentUser'}) if $DEBUG; diff --git a/FS/FS/Upgrade.pm b/FS/FS/Upgrade.pm index 417b202..8e697d3 100644 --- a/FS/FS/Upgrade.pm +++ b/FS/FS/Upgrade.pm @@ -278,6 +278,9 @@ sub upgrade_data { #set up payment gateways if needed 'pay_batch' => [], + + #flag monthly tax exemptions + 'cust_tax_exempt_pkg' => [], ; \%hash; diff --git a/FS/FS/cust_bill_ApplicationCommon.pm b/FS/FS/cust_bill_ApplicationCommon.pm index cadb8a7..cb07050 100644 --- a/FS/FS/cust_bill_ApplicationCommon.pm +++ b/FS/FS/cust_bill_ApplicationCommon.pm @@ -337,6 +337,7 @@ sub calculate_applications { # could expand @open above, instead, for a slightly different magic effect my @result = (); foreach my $apply ( @apply ) { + # $apply = [ FS::cust_bill_pkg_tax_location record, amount ] my @sub_lines = $apply->[0]->cust_bill_pkg_tax_Xlocation; my $amount = $apply->[1]; warn "applying ". $apply->[1]. " to ". $apply->[0]->desc @@ -346,6 +347,10 @@ sub calculate_applications { my $owed = $subline->owed; push @result, [ $apply->[0], sprintf('%.2f', min($amount, $owed) ), + # $subline->primary_key is "billpkgtaxlocationnum" + # or "billpkgtaxratelocationnum" + # This is the ONLY place either of those fields will + # be set. { $subline->primary_key => $subline->get($subline->primary_key) }, ]; $amount -= $owed; diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm index 96fa408a..b8ae81d 100644 --- a/FS/FS/cust_bill_pkg.pm +++ b/FS/FS/cust_bill_pkg.pm @@ -4,7 +4,7 @@ use base qw( FS::TemplateItem_Mixin FS::cust_main_Mixin FS::Record ); use strict; use vars qw( @ISA $DEBUG $me ); use Carp; -use List::Util qw( sum ); +use List::Util qw( sum min ); use Text::CSV_XS; use FS::Record qw( qsearch qsearchs dbh ); use FS::cust_pkg; @@ -26,7 +26,6 @@ use FS::cust_bill_pkg_tax_location_void; use FS::cust_bill_pkg_tax_rate_location_void; use FS::cust_tax_exempt_pkg_void; - $DEBUG = 0; $me = '[FS::cust_bill_pkg]'; @@ -191,14 +190,12 @@ sub insert { } } - if ( $self->_cust_tax_exempt_pkg ) { - foreach my $cust_tax_exempt_pkg ( @{$self->_cust_tax_exempt_pkg} ) { - $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum); - $error = $cust_tax_exempt_pkg->insert; - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "error inserting cust_tax_exempt_pkg: $error"; - } + foreach my $cust_tax_exempt_pkg ( @{$self->cust_tax_exempt_pkg} ) { + $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum); + $error = $cust_tax_exempt_pkg->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "error inserting cust_tax_exempt_pkg: $error"; } } @@ -787,14 +784,10 @@ sub usage_classes { } -# reserving this name for my friends FS::{tax_rate|cust_main_county}::taxline -# and FS::cust_main::bill -sub _cust_tax_exempt_pkg { +sub cust_tax_exempt_pkg { my ( $self ) = @_; - $self->{Hash}->{_cust_tax_exempt_pkg} or - $self->{Hash}->{_cust_tax_exempt_pkg} = []; - + $self->{Hash}->{cust_tax_exempt_pkg} ||= []; } =item cust_bill_pkg_tax_Xlocation @@ -941,6 +934,465 @@ sub credited_sql { } +sub upgrade_tax_location { + # For taxes that were calculated/invoiced before cust_location refactoring + # (May-June 2012), there are no cust_bill_pkg_tax_location records unless + # they were calculated on a package-location basis. Create them here, + # along with any necessary cust_location records and any tax exemption + # records. + # + # This probably shouldn't run from freeside-upgrade. + + my ($class, %opt) = @_; + # %opt may include 's' and 'e': start and end date ranges + # and 'X': abort on any error, instead of just rolling back changes to + # that invoice + my $dbh = dbh; + $FS::UID::AutoCommit = 0; + + eval { + use FS::h_cust_main; + use FS::h_cust_bill; + use FS::h_part_pkg; + use FS::h_cust_main_exemption; + }; + + local $FS::cust_location::import = 1; + + my $conf = FS::Conf->new; # h_conf? + return if $conf->exists('enable_taxproducts'); #don't touch this case + my $use_ship = $conf->exists('tax-ship_address'); + + my $date_where = ''; + if ($opt{s}) { + $date_where .= " AND cust_bill._date >= $opt{s}"; + } + if ($opt{e}) { + $date_where .= " AND cust_bill._date < $opt{e}"; + } + + my $commit_each_invoice = 1 unless $opt{X}; + + # if an invoice has either of these kinds of objects, then it doesn't + # need to be upgraded...probably + my $sub_has_tax_link = 'SELECT 1 FROM cust_bill_pkg_tax_location'. + ' JOIN cust_bill_pkg USING (billpkgnum)'. + ' WHERE cust_bill_pkg.invnum = cust_bill.invnum'; + my $sub_has_exempt = 'SELECT 1 FROM cust_tax_exempt_pkg'. + ' JOIN cust_bill_pkg USING (billpkgnum)'. + ' WHERE cust_bill_pkg.invnum = cust_bill.invnum'. + ' AND exempt_monthly IS NULL'; + + my @invnums = map { $_->invnum } qsearch({ + select => 'cust_bill.invnum', + table => 'cust_bill', + hashref => {}, + extra_sql => "WHERE NOT EXISTS($sub_has_tax_link) ". + "AND NOT EXISTS($sub_has_exempt) ". + $date_where, + }); + + print "Processing ".scalar(@invnums)." invoices...\n"; + + my $committed; + INVOICE: + foreach my $invnum (@invnums) { + $committed = 0; + print STDERR "Invoice #$invnum\n"; + my $pre = ''; + my %pkgpart_taxclass; # pkgpart => taxclass + my %pkgpart_exempt_setup; + my %pkgpart_exempt_recur; + my $h_cust_bill = qsearchs('h_cust_bill', + { invnum => $invnum, + history_action => 'insert' }); + if (!$h_cust_bill) { + warn "no insert record for invoice $invnum; skipped\n"; + #$date = $cust_bill->_date as a fallback? + # We're trying to avoid using non-real dates (-d/-y invoice dates) + # when looking up history records in other tables. + next INVOICE; + } + my $custnum = $h_cust_bill->custnum; + + # Determine the address corresponding to this tax region. + # It's either the bill or ship address of the customer as of the + # invoice date-of-insertion. (Not necessarily the invoice date.) + my $date = $h_cust_bill->history_date; + my $h_cust_main = qsearchs('h_cust_main', + { custnum => $custnum }, + FS::h_cust_main->sql_h_searchs($date) + ); + if (!$h_cust_main ) { + warn "no historical address for cust#".$h_cust_bill->custnum."; skipped\n"; + next INVOICE; + # fallback to current $cust_main? sounds dangerous. + } + + # This is a historical customer record, so it has a historical address. + # If there's no cust_location matching this custnum and address (there + # probably isn't), create one. + $pre = 'ship_' if $use_ship and length($h_cust_main->get('ship_last')); + my %hash = map { $_ => $h_cust_main->get($pre.$_) } + FS::cust_main->location_fields; + # not really needed for this, and often result in duplicate locations + delete @hash{qw(censustract censusyear latitude longitude coord_auto)}; + + $hash{custnum} = $h_cust_main->custnum; + my $tax_loc = qsearchs('cust_location', \%hash) # unlikely + || FS::cust_location->new({ %hash }); + if ( !$tax_loc->locationnum ) { + $tax_loc->disabled('Y'); + my $error = $tax_loc->insert; + if ( $error ) { + warn "couldn't create historical location record for cust#". + $h_cust_main->custnum.": $error\n"; + next INVOICE; + } + } + my $exempt_cust = 1 if $h_cust_main->tax; + + # Get any per-customer taxname exemptions that were in effect. + my %exempt_cust_taxname = map { + $_->taxname => 1 + } qsearch('h_cust_main_exemption', { 'custnum' => $custnum }, + FS::h_cust_main_exemption->sql_h_searchs($date) + ); + + # classify line items + my @tax_items; + my %nontax_items; # taxclass => array of cust_bill_pkg + foreach my $item ($h_cust_bill->cust_bill_pkg) { + my $pkgnum = $item->pkgnum; + + if ( $pkgnum == 0 ) { + + push @tax_items, $item; + + } else { + # (pkgparts really shouldn't change, right?) + my $h_cust_pkg = qsearchs('h_cust_pkg', { pkgnum => $pkgnum }, + FS::h_cust_pkg->sql_h_searchs($date) + ); + if ( !$h_cust_pkg ) { + warn "no historical package #".$item->pkgpart."; skipped\n"; + next INVOICE; + } + my $pkgpart = $h_cust_pkg->pkgpart; + + if (!exists $pkgpart_taxclass{$pkgpart}) { + my $h_part_pkg = qsearchs('h_part_pkg', { pkgpart => $pkgpart }, + FS::h_part_pkg->sql_h_searchs($date) + ); + if ( !$h_part_pkg ) { + warn "no historical package def #$pkgpart; skipped\n"; + next INVOICE; + } + $pkgpart_taxclass{$pkgpart} = $h_part_pkg->taxclass || ''; + $pkgpart_exempt_setup{$pkgpart} = 1 if $h_part_pkg->setuptax; + $pkgpart_exempt_recur{$pkgpart} = 1 if $h_part_pkg->recurtax; + } + + # mark any exemptions that apply + if ( $pkgpart_exempt_setup{$pkgpart} ) { + $item->set('exempt_setup' => 1); + } + + if ( $pkgpart_exempt_recur{$pkgpart} ) { + $item->set('exempt_recur' => 1); + } + + my $taxclass = $pkgpart_taxclass{ $pkgpart }; + + $nontax_items{$taxclass} ||= []; + push @{ $nontax_items{$taxclass} }, $item; + } + } + printf("%d tax items: \$%.2f\n", scalar(@tax_items), map {$_->setup} @tax_items); + + # Use a variation on the procedure in + # FS::cust_main::Billing::_handle_taxes to identify taxes that apply + # to this bill. + my @loc_keys = qw( district city county state country ); + my %taxhash = map { $_ => $h_cust_main->get($pre.$_) } @loc_keys; + my %taxdef_by_name; # by name, and then by taxclass + my %est_tax; # by name, and then by taxclass + my %taxable_items; # by taxnum, and then an array + + foreach my $taxclass (keys %nontax_items) { + my %myhash = %taxhash; + my @elim = qw( district city county state ); + my @taxdefs; # because there may be several with different taxnames + do { + $myhash{taxclass} = $taxclass; + @taxdefs = qsearch('cust_main_county', \%myhash); + if ( !@taxdefs ) { + $myhash{taxclass} = ''; + @taxdefs = qsearch('cust_main_county', \%myhash); + } + $myhash{ shift @elim } = ''; + } while scalar(@elim) and !@taxdefs; + + print "Class '$taxclass': ". scalar(@{ $nontax_items{$taxclass} }). + " items, ". scalar(@taxdefs)." tax defs found.\n"; + foreach my $taxdef (@taxdefs) { + next if $taxdef->tax == 0; + $taxdef_by_name{$taxdef->taxname}{$taxdef->taxclass} = $taxdef; + + $taxable_items{$taxdef->taxnum} ||= []; + foreach my $orig_item (@{ $nontax_items{$taxclass} }) { + # clone the item so that taxdef-dependent changes don't + # change it for other taxdefs + my $item = FS::cust_bill_pkg->new({ $orig_item->hash }); + + # these flags are already set if the part_pkg declares itself exempt + $item->set('exempt_setup' => 1) if $taxdef->setuptax; + $item->set('exempt_recur' => 1) if $taxdef->recurtax; + + my @new_exempt; + my $taxable = $item->setup + $item->recur; + # credits + # h_cust_credit_bill_pkg? + # NO. Because if these exemptions HAD been created at the time of + # billing, and then a credit applied later, the exemption would + # have been adjusted by the amount of the credit. So we adjust + # the taxable amount before creating the exemption. + # But don't deduct the credit from taxable, because the tax was + # calculated before the credit was applied. + foreach my $f (qw(setup recur)) { + my $credited = FS::Record->scalar_sql( + "SELECT SUM(amount) FROM cust_credit_bill_pkg ". + "WHERE billpkgnum = ? AND setuprecur = ?", + $item->billpkgnum, + $f + ); + $item->set($f, $item->get($f) - $credited) if $credited; + } + my $existing_exempt = FS::Record->scalar_sql( + "SELECT SUM(amount) FROM cust_tax_exempt_pkg WHERE ". + "billpkgnum = ? AND taxnum = ?", + $item->billpkgnum, $taxdef->taxnum + ) || 0; + $taxable -= $existing_exempt; + + if ( $taxable and $exempt_cust ) { + push @new_exempt, { exempt_cust => 'Y', amount => $taxable }; + $taxable = 0; + } + if ( $taxable and $exempt_cust_taxname{$taxdef->taxname} ){ + push @new_exempt, { exempt_cust_taxname => 'Y', amount => $taxable }; + $taxable = 0; + } + if ( $taxable and $item->exempt_setup ) { + push @new_exempt, { exempt_setup => 'Y', amount => $item->setup }; + $taxable -= $item->setup; + } + if ( $taxable and $item->exempt_recur ) { + push @new_exempt, { exempt_recur => 'Y', amount => $item->recur }; + $taxable -= $item->recur; + } + + $item->set('taxable' => $taxable); + push @{ $taxable_items{$taxdef->taxnum} }, $item + if $taxable > 0; + + # estimate the amount of tax (this is necessary because different + # taxdefs with the same taxname may have different tax rates) + # and sum that for each taxname/taxclass combination + # (in cents) + $est_tax{$taxdef->taxname} ||= {}; + $est_tax{$taxdef->taxname}{$taxdef->taxclass} ||= 0; + $est_tax{$taxdef->taxname}{$taxdef->taxclass} += + $taxable * $taxdef->tax; + + foreach (@new_exempt) { + next if $_->{amount} == 0; + my $cust_tax_exempt_pkg = FS::cust_tax_exempt_pkg->new({ + %$_, + billpkgnum => $item->billpkgnum, + taxnum => $taxdef->taxnum, + }); + my $error = $cust_tax_exempt_pkg->insert; + if ($error) { + my $pkgnum = $item->pkgnum; + warn "error creating tax exemption for inv$invnum pkg$pkgnum:". + "\n$error\n\n"; + next INVOICE; + } + } #foreach @new_exempt + } #foreach $item + } #foreach $taxdef + } #foreach $taxclass + + # Now go through the billed taxes and match them up with the line items. + TAX_ITEM: foreach my $tax_item ( @tax_items ) + { + my $taxname = $tax_item->itemdesc; + $taxname = '' if $taxname eq 'Tax'; + + if ( !exists( $taxdef_by_name{$taxname} ) ) { + # then we didn't find any applicable taxes with this name + warn "no definition found for tax item '$taxname'.\n". + '('.join(' ', @hash{qw(country state county city district)}).")\n"; + # possibly all of these should be "next TAX_ITEM", but whole invoices + # are transaction protected and we can go back and retry them. + next INVOICE; + } + # classname => cust_main_county + my %taxdef_by_class = %{ $taxdef_by_name{$taxname} }; + + # Divide the tax item among taxclasses, if necessary + # classname => estimated tax amount + my $this_est_tax = $est_tax{$taxname}; + if (!defined $this_est_tax) { + warn "no taxable sales found for inv#$invnum, tax item '$taxname'.\n"; + next INVOICE; + } + my $est_total = sum(values %$this_est_tax); + if ( $est_total == 0 ) { + # shouldn't happen + warn "estimated tax on invoice #$invnum is zero.\n"; + next INVOICE; + } + + my $real_tax = $tax_item->setup; + printf ("Distributing \$%.2f tax:\n", $real_tax); + my $cents_remaining = $real_tax * 100; # for rounding error + my @tax_links; # partial CBPTL hashrefs + foreach my $taxclass (keys %taxdef_by_class) { + my $taxdef = $taxdef_by_class{$taxclass}; + # these items already have "taxable" set to their charge amount + # after applying any credits or exemptions + my @items = @{ $taxable_items{$taxdef->taxnum} }; + my $subtotal = sum(map {$_->get('taxable')} @items); + printf("\t$taxclass: %.2f\n", $this_est_tax->{$taxclass}/$est_total); + + foreach my $nontax (@items) { + my $part = int($real_tax + # class allocation + * ($this_est_tax->{$taxclass}/$est_total) + # item allocation + * ($nontax->get('taxable'))/$subtotal + # convert to cents + * 100 + ); + $cents_remaining -= $part; + push @tax_links, { + taxnum => $taxdef->taxnum, + pkgnum => $nontax->pkgnum, + cents => $part, + }; + } #foreach $nontax + } #foreach $taxclass + # Distribute any leftover tax round-robin style, one cent at a time. + my $i = 0; + my $nlinks = scalar(@tax_links); + if ( $nlinks ) { + while (int($cents_remaining) > 0) { + $tax_links[$i % $nlinks]->{cents} += 1; + $cents_remaining--; + $i++; + } + } else { + warn "Can't create tax links--no taxable items found.\n"; + next INVOICE; + } + + # Gather credit/payment applications so that we can link them + # appropriately. + my @unlinked = ( + qsearch( 'cust_credit_bill_pkg', + { billpkgnum => $tax_item->billpkgnum, billpkgtaxlocationnum => '' } + ), + qsearch( 'cust_bill_pay_pkg', + { billpkgnum => $tax_item->billpkgnum, billpkgtaxlocationnum => '' } + ) + ); + + # grab the first one + my $this_unlinked = shift @unlinked; + my $unlinked_cents = int($this_unlinked->amount * 100) if $this_unlinked; + + # Create tax links (yay!) + printf("Creating %d tax links.\n",scalar(@tax_links)); + foreach (@tax_links) { + my $link = FS::cust_bill_pkg_tax_location->new({ + billpkgnum => $tax_item->billpkgnum, + taxtype => 'FS::cust_main_county', + locationnum => $tax_loc->locationnum, + taxnum => $_->{taxnum}, + pkgnum => $_->{pkgnum}, + amount => sprintf('%.2f', $_->{cents} / 100), + }); + my $error = $link->insert; + if ( $error ) { + warn "Can't create tax link for inv#$invnum: $error\n"; + next INVOICE; + } + + my $link_cents = $_->{cents}; + # update/create subitem links + # + # If $this_unlinked is undef, then we've allocated all of the + # credit/payment applications to the tax item. If $link_cents is 0, + # then we've applied credits/payments to all of this package fraction, + # so go on to the next. + while ($this_unlinked and $link_cents) { + # apply as much as possible of $link_amount to this credit/payment + # link + my $apply_cents = min($link_cents, $unlinked_cents); + $link_cents -= $apply_cents; + $unlinked_cents -= $apply_cents; + # $link_cents or $unlinked_cents or both are now zero + $this_unlinked->set('amount' => sprintf('%.2f',$apply_cents/100)); + $this_unlinked->set('billpkgtaxlocationnum' => $link->billpkgtaxlocationnum); + my $pkey = $this_unlinked->primary_key; #creditbillpkgnum or billpaypkgnum + if ( $this_unlinked->$pkey ) { + # then it's an existing link--replace it + $error = $this_unlinked->replace; + } else { + $this_unlinked->insert; + } + # what do we do with errors at this stage? + if ( $error ) { + warn "Error creating tax application link: $error\n"; + next INVOICE; # for lack of a better idea + } + + if ( $unlinked_cents == 0 ) { + # then we've allocated all of this payment/credit application, + # so grab the next one + $this_unlinked = shift @unlinked; + $unlinked_cents = int($this_unlinked->amount * 100) if $this_unlinked; + } elsif ( $link_cents == 0 ) { + # then we've covered all of this package tax fraction, so split + # off a new application from this one + $this_unlinked = $this_unlinked->new({ + $this_unlinked->hash, + $pkey => '', + }); + # $unlinked_cents is still what it is + } + + } #while $this_unlinked and $link_cents + } #foreach (@tax_links) + } #foreach $tax_item + + $dbh->commit if $commit_each_invoice; + $committed = 1; + + } #foreach $invnum + continue { + if (!$committed) { + $dbh->rollback; + die "Upgrade halted.\n" unless $commit_each_invoice; + } + } + + $dbh->commit unless $commit_each_invoice; + ''; +} + =back =head1 BUGS @@ -958,6 +1410,8 @@ owed_setup and owed_recur could then be repaced by just owed, and cust_bill::open_cust_bill_pkg and cust_bill_ApplicationCommon::apply_to_lineitems could be simplified. +The upgrade procedure is pretty sketchy. + =head1 SEE ALSO L<FS::Record>, L<FS::cust_bill>, L<FS::cust_pkg>, L<FS::cust_main>, schema.html diff --git a/FS/FS/cust_credit_bill_pkg.pm b/FS/FS/cust_credit_bill_pkg.pm index 64f1f29..4189007 100644 --- a/FS/FS/cust_credit_bill_pkg.pm +++ b/FS/FS/cust_credit_bill_pkg.pm @@ -103,18 +103,22 @@ sub insert { return $error; } - my $payable = $self->cust_bill_pkg->payable($self->setuprecur); - my $taxable = $self->_is_taxable ? $payable : 0; - my $part_pkg = $self->cust_bill_pkg->part_pkg; - my $freq = $self->cust_bill_pkg->freq; + my $cust_bill_pkg = $self->cust_bill_pkg; + #'payable' is the amount charged (either setup or recur) + # minus any credit applications, including this one + my $payable = $cust_bill_pkg->payable($self->setuprecur); + my $part_pkg = $cust_bill_pkg->part_pkg; + my $freq = $cust_bill_pkg->freq; unless ($freq) { $freq = $part_pkg ? ($part_pkg->freq || 1) : 1;#fallback.. assumes unchanged } - my $taxable_per_month = sprintf("%.2f", $taxable / $freq ); + my $taxable_per_month = sprintf("%.2f", $payable / $freq ); my $credit_per_month = sprintf("%.2f", $self->amount / $freq ); #pennies? if ($taxable_per_month >= 0) { #panic if its subzero? - my $groupby = 'taxnum,year,month'; + my $groupby = join(',', + qw(taxnum year month exempt_monthly exempt_cust + exempt_cust_taxname exempt_setup exempt_recur)); my $sum = 'SUM(amount)'; my @exemptions = qsearch( { @@ -124,25 +128,55 @@ sub insert { 'extra_sql' => "GROUP BY $groupby HAVING $sum > 0", } ); + # each $exemption is now the sum of all monthly exemptions applied to + # this line item for a particular taxnum and month. foreach my $exemption ( @exemptions ) { - next if $taxable_per_month >= $exemption->amount; - my $amount = $exemption->amount - $taxable_per_month; - if ($amount > $credit_per_month) { - "cust_bill_pkg ". $self->billpkgnum. " Reducing.\n"; - $amount = $credit_per_month; + my $amount = 0; + if ( $exemption->exempt_monthly ) { + # finite exemptions + # $taxable_per_month is AFTER inserting the credit application, so + # if it's still larger than the exemption, we don't need to adjust + next if $taxable_per_month >= $exemption->amount; + # the amount of 'excess' exemption already in place (above the + # remaining charged amount). We'll de-exempt that much, or the + # amount of the new credit, whichever is smaller. + $amount = $exemption->amount - $taxable_per_month; + # $amount is the amount of 'excess' exemption already existing + # (above the remaining taxable charge amount). We'll "de-exempt" + # that much, or the amount of the new credit, whichever is smaller. + if ($amount > $credit_per_month) { + "cust_bill_pkg ". $self->billpkgnum. " Reducing.\n"; + $amount = $credit_per_month; + } + } elsif ( $exemption->exempt_setup or $exemption->exempt_recur ) { + # package defined exemptions: may be setup only, recur only, or both + my $method = 'exempt_'.$self->setuprecur; + if ( $exemption->$method ) { + # then it's exempt from the portion of the charge that this + # credit is being applied to + $amount = $self->amount; + } + } else { + # other types of exemptions: always equal to the amount of + # the charge + $amount = $self->amount; } + next if $amount == 0; + + # create a negative exemption my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg { + $exemption->hash, # for exempt_ flags, taxnum, month/year 'billpkgnum' => $self->billpkgnum, 'creditbillpkgnum' => $self->creditbillpkgnum, 'amount' => sprintf('%.2f', 0-$amount), - map { $_ => $exemption->$_ } split(',', $groupby) }; + my $error = $cust_tax_exempt_pkg->insert; if ( $error ) { $dbh->rollback if $oldAutoCommit; return "error inserting cust_tax_exempt_pkg: $error"; } - } + } #foreach $exemption } $dbh->commit or die $dbh->errstr if $oldAutoCommit; @@ -233,7 +267,7 @@ sub delete { return "error calculating taxes: $hashref_or_error"; } - push @generated_exemptions, @{ $cust_bill_pkg->_cust_tax_exempt_pkg || [] }; + push @generated_exemptions, @{ $cust_bill_pkg->cust_tax_exempt_pkg }; } foreach my $taxnum ( keys %seen ) { diff --git a/FS/FS/cust_main/Billing.pm b/FS/FS/cust_main/Billing.pm index 85cafd6..11247a2 100644 --- a/FS/FS/cust_main/Billing.pm +++ b/FS/FS/cust_main/Billing.pm @@ -735,21 +735,25 @@ sub calculate_taxes { my @tax_line_items = (); # keys are tax names (as printed on invoices / itemdesc ) - # values are listrefs of taxlisthash keys (internal identifiers) + # values are arrayrefs of taxlisthash keys (internal identifiers) my %taxname = (); # keys are taxlisthash keys (internal identifiers) # values are (cumulative) amounts - my %tax = (); + my %tax_amount = (); # keys are taxlisthash keys (internal identifiers) - # values are listrefs of cust_bill_pkg_tax_location hashrefs + # values are arrayrefs of cust_bill_pkg_tax_location hashrefs my %tax_location = (); # keys are taxlisthash keys (internal identifiers) - # values are listrefs of cust_bill_pkg_tax_rate_location hashrefs + # values are arrayrefs of cust_bill_pkg_tax_rate_location hashrefs my %tax_rate_location = (); + # keys are taxnums (not internal identifiers!) + # values are arrayrefs of cust_tax_exempt_pkg objects + my %tax_exemption; + foreach my $tax ( keys %$taxlisthash ) { # $tax is a tax identifier my $tax_object = shift @{ $taxlisthash->{$tax} }; @@ -759,14 +763,24 @@ sub calculate_taxes { warn "found ". $tax_object->taxname. " as $tax\n" if $DEBUG > 2; warn " ". join('/', @{ $taxlisthash->{$tax} } ). "\n" if $DEBUG > 2; # taxline calculates the tax on all cust_bill_pkgs in the - # first (arrayref) argument + # first (arrayref) argument, and returns a hashref of 'name' + # (the line item description) and 'amount'. + # It also calculates exemptions and attaches them to the cust_bill_pkgs + # in the argument. + my $taxables = $taxlisthash->{$tax}; + my $exemptions = $tax_exemption{$tax_object->taxnum} ||= []; my $hashref_or_error = - $tax_object->taxline( $taxlisthash->{$tax}, + $tax_object->taxline( $taxables, 'custnum' => $self->custnum, - 'invoice_time' => $invoice_time + 'invoice_time' => $invoice_time, + 'exemptions' => $exemptions, ); return $hashref_or_error unless ref($hashref_or_error); + # then collect any new exemptions generated for this tax + push @$exemptions, @{ $_->cust_tax_exempt_pkg } + foreach @$taxables; + unshift @{ $taxlisthash->{$tax} }, $tax_object; my $name = $hashref_or_error->{'name'}; @@ -776,7 +790,7 @@ sub calculate_taxes { $taxname{ $name } ||= []; push @{ $taxname{ $name } }, $tax; - $tax{ $tax } += $amount; + $tax_amount{ $tax } += $amount; # link records between cust_main_county/tax_rate and cust_location $tax_location{ $tax } ||= []; @@ -809,17 +823,21 @@ sub calculate_taxes { #move the cust_tax_exempt_pkg records to the cust_bill_pkgs we will commit my %packagemap = map { $_->pkgnum => $_ } @$cust_bill_pkg; foreach my $tax ( keys %$taxlisthash ) { - foreach ( @{ $taxlisthash->{$tax} }[1 ... scalar(@{ $taxlisthash->{$tax} })] ) { - next unless ref($_) eq 'FS::cust_bill_pkg'; - - my @cust_tax_exempt_pkg = splice( @{ $_->_cust_tax_exempt_pkg } ); + my $taxables = $taxlisthash->{$tax}; + my $tax_object = shift @$taxables; # the rest are line items + foreach my $cust_bill_pkg ( @$taxables ) { + next unless ref($cust_bill_pkg) eq 'FS::cust_bill_pkg'; - next unless @cust_tax_exempt_pkg; #just avoiding the prob when irrelevant? - die "can't distribute tax exemptions: no line item for ". Dumper($_). - " in packagemap ". join(',', sort {$a<=>$b} keys %packagemap). "\n" - unless $packagemap{$_->pkgnum}; + my @cust_tax_exempt_pkg = splice @{ $cust_bill_pkg->cust_tax_exempt_pkg }; - push @{ $packagemap{$_->pkgnum}->_cust_tax_exempt_pkg }, + next unless @cust_tax_exempt_pkg; + # get the non-disintegrated version + my $real_cust_bill_pkg = $packagemap{$cust_bill_pkg->pkgnum} + or die "can't distribute tax exemptions: no line item for ". + Dumper($_). " in packagemap ". + join(',', sort {$a<=>$b} keys %packagemap). "\n"; + + push @{ $real_cust_bill_pkg->cust_tax_exempt_pkg }, @cust_tax_exempt_pkg; } } @@ -827,15 +845,15 @@ sub calculate_taxes { #consolidate and create tax line items warn "consolidating and generating...\n" if $DEBUG > 2; foreach my $taxname ( keys %taxname ) { - my $tax = 0; + my $tax_total = 0; my %seen = (); my @cust_bill_pkg_tax_location = (); my @cust_bill_pkg_tax_rate_location = (); warn "adding $taxname\n" if $DEBUG > 1; foreach my $taxitem ( @{ $taxname{$taxname} } ) { next if $seen{$taxitem}++; - warn "adding $tax{$taxitem}\n" if $DEBUG > 1; - $tax += $tax{$taxitem}; + warn "adding $tax_amount{$taxitem}\n" if $DEBUG > 1; + $tax_total += $tax_amount{$taxitem}; push @cust_bill_pkg_tax_location, map { new FS::cust_bill_pkg_tax_location $_ } @{ $tax_location{ $taxitem } }; @@ -843,9 +861,9 @@ sub calculate_taxes { map { new FS::cust_bill_pkg_tax_rate_location $_ } @{ $tax_rate_location{ $taxitem } }; } - next unless $tax; + next unless $tax_total; - $tax = sprintf('%.2f', $tax ); + $tax_total = sprintf('%.2f', $tax_total ); my $pkg_category = qsearchs( 'pkg_category', { 'categoryname' => $taxname, 'disabled' => '', @@ -866,7 +884,7 @@ sub calculate_taxes { push @tax_line_items, new FS::cust_bill_pkg { 'pkgnum' => 0, - 'setup' => $tax, + 'setup' => $tax_total, 'recur' => 0, 'sdate' => '', 'edate' => '', @@ -1197,8 +1215,11 @@ sub _handle_taxes { my $exempt = $conf->exists('cust_class-tax_exempt') ? ( $self->cust_class ? $self->cust_class->tax : '' ) : $self->tax; + # standardize this just to be sure + $exempt = ($exempt eq 'Y') ? 'Y' : ''; - if ( $exempt !~ /Y/i && $self->payby ne 'COMP' ) { + #if ( $exempt !~ /Y/i && $self->payby ne 'COMP' ) { + if ( $self->payby ne 'COMP' ) { if ( $conf->exists('enable_taxproducts') && ( scalar($part_pkg->part_pkg_taxoverride) @@ -1207,19 +1228,26 @@ sub _handle_taxes { ) { - foreach my $class (@classes) { - my $err_or_ref = $self->_gather_taxes( $part_pkg, $class, $cust_pkg ); - return $err_or_ref unless ref($err_or_ref); - $taxes{$class} = $err_or_ref; - } + if ( !$exempt ) { + + foreach my $class (@classes) { + my $err_or_ref = $self->_gather_taxes( $part_pkg, $class, $cust_pkg ); + return $err_or_ref unless ref($err_or_ref); + $taxes{$class} = $err_or_ref; + } + + unless (exists $taxes{''}) { + my $err_or_ref = $self->_gather_taxes( $part_pkg, '', $cust_pkg ); + return $err_or_ref unless ref($err_or_ref); + $taxes{''} = $err_or_ref; + } - unless (exists $taxes{''}) { - my $err_or_ref = $self->_gather_taxes( $part_pkg, '', $cust_pkg ); - return $err_or_ref unless ref($err_or_ref); - $taxes{''} = $err_or_ref; } - } else { + } else { # cust_main_county tax system + + # We fetch taxes even if the customer is completely exempt, + # because we need to record that fact. my @loc_keys = qw( district city county state country ); my $location = $cust_pkg->tax_location; @@ -1248,19 +1276,11 @@ sub _handle_taxes { } while ( !scalar(@taxes) && scalar(@elim) ); - @taxes = grep { ! $_->taxname || ! $self->tax_exemption($_->taxname) } - @taxes - if $self->cust_main_exemption; #just to be safe - - warn "using taxes:\n". Dumper(@taxes) if $DEBUG > 2; - - # all packages now have a locationnum and should get a - # cust_bill_pkg_tax_location record. The tax_locationnum - # may be the package's locationnum, or the customer's bill - # or service location. foreach (@taxes) { - $_->set('pkgnum', $cust_pkg->pkgnum); - $_->set('locationnum', $cust_pkg->tax_locationnum); + # These could become cust_bill_pkg_tax_location records, + # or cust_tax_exempt_pkg. We'll decide later. + $_->set('pkgnum', $cust_pkg->pkgnum); + $_->set('locationnum', $cust_pkg->tax_locationnum); } $taxes{''} = [ @taxes ]; @@ -1277,7 +1297,7 @@ sub _handle_taxes { } #if $conf->exists('enable_taxproducts') ... - } + } # if $self->payby eq 'COMP' #what's this doing in the middle of _handle_taxes? probably should split #this into three parts above in _make_lines @@ -1300,14 +1320,15 @@ sub _handle_taxes { # this is the tax identifier, not the taxname my $taxname = ref( $tax ). ' '. $tax->taxnum; -# $taxname .= ' pkgnum'. $cust_pkg->pkgnum. -# ' locationnum'. $cust_pkg->locationnum -# if $conf->exists('tax-pkg_address') && $cust_pkg->locationnum; + $taxname .= ' pkgnum'. $cust_pkg->pkgnum; + # We need to create a separate $taxlisthash entry for each pkgnum + # on the invoice, so that cust_bill_pkg_tax_location records will + # be linked correctly. - # $taxlisthash: keys are "setup", "recur", and usage classes - # values are arrayrefs, first the tax object (cust_main_county + # $taxlisthash: keys are "setup", "recur", and usage classes. + # Values are arrayrefs, first the tax object (cust_main_county # or tax_rate) and then any cust_bill_pkg objects that the - # tax applies to + # tax applies to. $taxlisthash->{ $taxname } ||= [ $tax ]; push @{ $taxlisthash->{ $taxname } }, $tax_cust_bill_pkg; diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm index 6316f23..143f62e 100644 --- a/FS/FS/cust_main_county.pm +++ b/FS/FS/cust_main_county.pm @@ -4,7 +4,7 @@ use strict; use vars qw( @ISA @EXPORT_OK $conf @cust_main_county %cust_main_county $countyflag ); # $cityflag ); use Exporter; -use FS::Record qw( qsearch dbh ); +use FS::Record qw( qsearch qsearchs dbh ); use FS::cust_bill_pkg; use FS::cust_bill; use FS::cust_pkg; @@ -164,6 +164,57 @@ sub recurtax { return ''; } +=item label OPTIONS + +Returns a label looking like "Anytown, Alameda County, CA, US". + +If the taxname field is set, it will look like +"CA Sales Tax (Anytown, Alameda County, CA, US)". + +If the taxclass is set, then it will be +"Anytown, Alameda County, CA, US (International)". + +Currently it will not contain the district, even if the city+county+state +is not unique. + +OPTIONS may contain "no_taxclass" (hides taxclass) and/or "no_city" +(hides city). It may also contain "out", in which case, if this +region (district+city+county+state+country) contains no non-zero +taxes, the label will read "Out of taxable region(s)". + +=cut + +sub label { + my ($self, %opt) = @_; + if ( $opt{'out'} + and $self->tax == 0 + and !defined(qsearchs('cust_main_county', { + 'district' => $self->district, + 'city' => $self->city, + 'county' => $self->county, + 'state' => $self->state, + 'country' => $self->country, + 'tax' => { op => '>', value => 0 }, + })) ) + { + return 'Out of taxable region(s)'; + } + my $label = $self->country; + $label = $self->state.", $label" if $self->state; + $label = $self->county." County, $label" if $self->county; + if (!$opt{no_city}) { + $label = $self->city.", $label" if $self->city; + } + # ugly labels when taxclass and taxname are both non-null... + # but this is how the tax report does it + if (!$opt{no_taxclass}) { + $label = "$label (".$self->taxclass.')' if $self->taxclass; + } + $label = $self->taxname." ($label)" if $self->taxname; + + $label; +} + =item sql_taxclass_sameregion Returns an SQL WHERE fragment or the empty string to search for entries @@ -207,21 +258,30 @@ sub _list_sql { =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ] -Returns a listref of a name and an amount of tax calculated for the list of -packages or amounts referenced by TAXABLES_ARRAYREF. Returns a scalar error -message on error. +Returns an hashref of a name and an amount of tax calculated for the +line items (L<FS::cust_bill_pkg> objects) in TAXABLES_ARRAYREF. The line +items must come from the same invoice. Returns a scalar error message +on error. + +In addition to calculating the tax for the line items, this will calculate +any appropriate tax exemptions and attach them to the line items. -Options include custnum and invoice_date and are hints to this method +Options may include 'custnum' and 'invoice_date' in case the cust_bill_pkg +objects belong to an invoice that hasn't been inserted yet. + +Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg> +objects belonging to the same customer, to be counted against the monthly +tax exemption limit if there is one. =cut +# XXX this should just return a cust_bill_pkg object for the tax, +# but that requires changing stuff in tax_rate.pm also. + sub taxline { my( $self, $taxables, %opt ) = @_; + return 'taxline called with no line items' unless @$taxables; - my @exemptions = (); - push @exemptions, @{ $_->_cust_tax_exempt_pkg } - for grep { ref($_) } @$taxables; - local $SIG{HUP} = 'IGNORE'; local $SIG{INT} = 'IGNORE'; local $SIG{QUIT} = 'IGNORE'; @@ -236,29 +296,92 @@ sub taxline { my $name = $self->taxname || 'Tax'; my $amount = 0; + my $cust_bill = $taxables->[0]->cust_bill; + my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'}; + my $invoice_date = $cust_bill ? $cust_bill->_date : $opt{'invoice_date'}; + my $cust_main = FS::cust_main->by_key($custnum) if $custnum > 0; + if (!$cust_main) { + # better way to handle this? should we just assume that it's taxable? + die "unable to calculate taxes for an unknown customer\n"; + } + + # set a flag if the customer is tax-exempt + my $exempt_cust; + my $conf = FS::Conf->new; + if ( $conf->exists('cust_class-tax_exempt') ) { + my $cust_class = $cust_main->cust_class; + $exempt_cust = $cust_class->tax if $cust_class; + } else { + $exempt_cust = $cust_main->tax; + } + + # set a flag if the customer is exempt from this tax here + my $exempt_cust_taxname = $cust_main->tax_exemption($self->taxname) + if $self->taxname; + + # Gather any exemptions that are already attached to these cust_bill_pkgs + # so that we can deduct them from the customer's monthly limit. + my @existing_exemptions = @{ $opt{'exemptions'} }; + push @existing_exemptions, @{ $_->cust_tax_exempt_pkg } + for @$taxables; + foreach my $cust_bill_pkg (@$taxables) { my $cust_pkg = $cust_bill_pkg->cust_pkg; - my $cust_bill = $cust_pkg->cust_bill if $cust_pkg; - my $custnum = $cust_pkg ? $cust_pkg->custnum : $opt{custnum}; my $part_pkg = $cust_bill_pkg->part_pkg; - my $invoice_date = $cust_bill ? $cust_bill->_date : $opt{invoice_date}; - - my $taxable_charged = 0; - $taxable_charged += $cust_bill_pkg->setup - unless $part_pkg->setuptax =~ /^Y$/i - || $self->setuptax =~ /^Y$/i; - $taxable_charged += $cust_bill_pkg->recur - unless $part_pkg->recurtax =~ /^Y$/i - || $self->recurtax =~ /^Y$/i; - - next unless $taxable_charged; + + my @new_exemptions; + my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur + or next; # don't create zero-amount exemptions + + # XXX the following procedure should probably be in cust_bill_pkg + + if ( $exempt_cust ) { + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => $taxable_charged, + exempt_cust => 'Y', + }); + $taxable_charged = 0; + + } elsif ( $exempt_cust_taxname ) { + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => $taxable_charged, + exempt_cust_taxname => 'Y', + }); + $taxable_charged = 0; + + } + + if ( ($part_pkg->setuptax eq 'Y' or $self->setuptax eq 'Y') + and $cust_bill_pkg->setup > 0 and $taxable_charged > 0 ) { + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => $cust_bill_pkg->setup, + exempt_setup => 'Y' + }); + $taxable_charged -= $cust_bill_pkg->setup; + + } + if ( ($part_pkg->recurtax eq 'Y' or $self->recurtax eq 'Y') + and $cust_bill_pkg->recur > 0 and $taxable_charged > 0 ) { + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => $cust_bill_pkg->recur, + exempt_recur => 'Y' + }); + $taxable_charged -= $cust_bill_pkg->recur; + + } - if ( $self->exempt_amount && $self->exempt_amount > 0 ) { + if ( $self->exempt_amount && $self->exempt_amount > 0 + and $taxable_charged > 0 ) { #my ($mon,$year) = (localtime($cust_bill_pkg->sdate) )[4,5]; my ($mon,$year) = (localtime( $cust_bill_pkg->sdate || $invoice_date ) )[4,5]; $mon++; + $year += 1900; my $freq = $cust_bill_pkg->freq; unless ($freq) { $freq = $part_pkg->freq || 1; # less trustworthy fallback @@ -294,6 +417,7 @@ sub taxline { AND taxnum = ? AND year = ? AND month = ? + AND exempt_monthly = 'Y' "; my $sth = dbh->prepare($sql) or do { $dbh->rollback if $oldAutoCommit; @@ -302,7 +426,7 @@ sub taxline { $sth->execute( $custnum, $self->taxnum, - 1900+$year, + $year, $mon, ) or do { $dbh->rollback if $oldAutoCommit; @@ -311,9 +435,10 @@ sub taxline { my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0; foreach ( grep { $_->taxnum == $self->taxnum && + $_->exempt_monthly eq 'Y' && $_->month == $mon && - $_->year == 1900+$year - } @exemptions + $_->year == $year + } @existing_exemptions ) { $existing_exemption += $_->amount; @@ -325,42 +450,50 @@ sub taxline { my $addl = $remaining_exemption > $taxable_per_month ? $taxable_per_month : $remaining_exemption; + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => sprintf('%.2f', $addl), + exempt_monthly => 'Y', + year => $year, + month => $mon, + }); $taxable_charged -= $addl; - - my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg ( { - 'taxnum' => $self->taxnum, - 'year' => 1900+$year, - 'month' => $mon, - 'amount' => sprintf('%.2f', $addl ), - } ); - if ($cust_bill_pkg->billpkgnum) { - $cust_tax_exempt_pkg->billpkgnum($cust_bill_pkg->billpkgnum); - my $error = $cust_tax_exempt_pkg->insert; - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "fatal: can't insert cust_tax_exempt_pkg: $error"; - } - }else{ - push @exemptions, $cust_tax_exempt_pkg; - push @{ $cust_bill_pkg->_cust_tax_exempt_pkg }, $cust_tax_exempt_pkg; - } # if $cust_bill_pkg->billpkgnum - } # if $remaining_exemption > 0 - - #++ + } + last if $taxable_charged < 0.005; + # if they're using multiple months of exemption for a multi-month + # package, then record the exemptions in separate months $mon++; - #until ( $mon < 12 ) { $mon -= 12; $year++; } - until ( $mon < 13 ) { $mon -= 12; $year++; } + if ( $mon > 12 ) { + $mon -= 12; + $year++; + } } #foreach $which_month + } # if exempt_amount + + $_->taxnum($self->taxnum) foreach @new_exemptions; + + if ( $cust_bill_pkg->billpkgnum ) { + die "tried to calculate tax exemptions on a previously billed line item\n"; + # this is unnecessary +# foreach my $cust_tax_exempt_pkg (@new_exemptions) { +# my $error = $cust_tax_exempt_pkg->insert; +# if ( $error ) { +# $dbh->rollback if $oldAutoCommit; +# return "can't insert cust_tax_exempt_pkg: $error"; +# } +# } + } - } #if $tax->exempt_amount + # attach them to the line item + push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions; + push @existing_exemptions, @new_exemptions; + # If we were smart, we'd also generate a cust_bill_pkg_tax_location + # record at this point, but that would require redesigning more stuff. $taxable_charged = sprintf( "%.2f", $taxable_charged); - $amount += $taxable_charged * $self->tax / 100 - } - - $dbh->commit or die $dbh->errstr if $oldAutoCommit; + $amount += $taxable_charged * $self->tax / 100; + } #foreach $cust_bill_pkg return { 'name' => $name, diff --git a/FS/FS/cust_pkg_discount.pm b/FS/FS/cust_pkg_discount.pm index a207940..5f4d0dc 100644 --- a/FS/FS/cust_pkg_discount.pm +++ b/FS/FS/cust_pkg_discount.pm @@ -106,7 +106,8 @@ sub insert { 'amount' => $self->amount, 'percent' => $self->percent, 'months' => $self->months, - 'setup' => $self->setup, + 'setup' => $self->setup, + #'linked' => $self->linked, 'disabled' => 'Y', }; my $error = $discount->insert; diff --git a/FS/FS/cust_tax_exempt_pkg.pm b/FS/FS/cust_tax_exempt_pkg.pm index e63b84b..bbabb5b 100644 --- a/FS/FS/cust_tax_exempt_pkg.pm +++ b/FS/FS/cust_tax_exempt_pkg.pm @@ -7,6 +7,10 @@ use FS::cust_main_Mixin; use FS::cust_bill_pkg; use FS::cust_main_county; use FS::cust_credit_bill_pkg; +use FS::UID qw(dbh); +use FS::upgrade_journal; + +# some kind of common ancestor with cust_bill_pkg_tax_location would make sense @ISA = qw( FS::cust_main_Mixin FS::Record ); @@ -32,22 +36,45 @@ FS::cust_tax_exempt_pkg - Object methods for cust_tax_exempt_pkg records =head1 DESCRIPTION An FS::cust_tax_exempt_pkg object represents a record of a customer tax -exemption. Currently this is only used for "texas tax". FS::cust_tax_exempt -inherits from FS::Record. The following fields are currently supported: +exemption. Whenever a package would be taxed (based on its location and +taxclass), but some or all of it is exempt from taxation, an +FS::cust_tax_exempt_pkg record is created. + +FS::cust_tax_exempt inherits from FS::Record. The following fields are +currently supported: =over 4 =item exemptpkgnum - primary key -=item billpkgnum - invoice line item (see L<FS::cust_bill_pkg>) +=item billpkgnum - invoice line item (see L<FS::cust_bill_pkg>) that +was exempted from tax. =item taxnum - tax rate (see L<FS::cust_main_county>) -=item year +=item year - the year in which the exemption occurred. NULL if this +is a customer or package exemption rather than a monthly exemption. + +=item month - the month in which the exemption occurred. NULL if this +is a customer or package exemption. + +=item amount - the amount of revenue exempted. For monthly exemptions +this may be anything up to the monthly exemption limit defined in +L<FS::cust_main_county> for this tax. For customer exemptions it is +always the full price of the line item. For package exemptions it +may be the setup fee, the recurring fee, or the sum of those. + +=item exempt_cust - flag indicating that the customer is tax-exempt +(cust_main.tax = 'Y'). -=item month +=item exempt_cust_taxname - flag indicating that the customer is exempt +from the tax with this name (see L<FS::cust_main_exemption). -=item amount +=item exempt_setup, exempt_recur: flag indicating that the package's setup +or recurring fee is not taxable (part_pkg.setuptax and part_pkg.recurtax). + +=item exempt_monthly: flag indicating that this is a monthly per-customer +exemption (Texas tax). =back @@ -109,18 +136,44 @@ and replace methods. sub check { my $self = shift; - $self->ut_numbern('exemptnum') -# || $self->ut_foreign_key('custnum', 'cust_main', 'custnum') + my $error = $self->ut_numbern('exemptnum') || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg', 'billpkgnum') || $self->ut_foreign_key('taxnum', 'cust_main_county', 'taxnum') || $self->ut_foreign_keyn('creditbillpkgnum', 'cust_credit_bill_pkg', 'creditbillpkgnum') - || $self->ut_number('year') #check better - || $self->ut_number('month') #check better + || $self->ut_numbern('year') #check better + || $self->ut_numbern('month') #check better || $self->ut_money('amount') + || $self->ut_flag('exempt_cust') + || $self->ut_flag('exempt_setup') + || $self->ut_flag('exempt_recur') + || $self->ut_flag('exempt_cust_taxname') || $self->SUPER::check ; + + return $error if $error; + + if ( $self->get('exempt_cust') ) { + $self->set($_ => '') for qw( + exempt_cust_taxname exempt_setup exempt_recur exempt_monthly month year + ); + } elsif ( $self->get('exempt_cust_taxname') ) { + $self->set($_ => '') for qw( + exempt_setup exempt_recur exempt_monthly month year + ); + } elsif ( $self->get('exempt_setup') || $self->get('exempt_recur') ) { + $self->set($_ => '') for qw(exempt_monthly month year); + } elsif ( $self->get('exempt_monthly') ) { + $self->year =~ /^\d{4}$/ + or return "illegal exemption year: '".$self->year."'"; + $self->month >= 1 && $self->month <= 12 + or return "illegal exemption month: '".$self->month."'"; + } else { + return "no exemption type selected"; + } + + ''; } =item cust_main_county @@ -135,6 +188,18 @@ sub cust_main_county { qsearchs( 'cust_main_county', { 'taxnum', $self->taxnum } ); } +sub _upgrade_data { + my $class = shift; + + my $journal = 'cust_tax_exempt_pkg_flags'; + if ( !FS::upgrade_journal->is_done($journal) ) { + my $sql = "UPDATE cust_tax_exempt_pkg SET exempt_monthly = 'Y' ". + "WHERE month IS NOT NULL"; + dbh->do($sql) or die dbh->errstr; + FS::upgrade_journal->set_done($journal); + } +} + =back =head1 BUGS diff --git a/FS/FS/cust_tax_exempt_pkg_void.pm b/FS/FS/cust_tax_exempt_pkg_void.pm index 51c85b4..bfbc8c7 100644 --- a/FS/FS/cust_tax_exempt_pkg_void.pm +++ b/FS/FS/cust_tax_exempt_pkg_void.pm @@ -114,10 +114,15 @@ sub check { $self->ut_number('exemptpkgnum') || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg_void', 'billpkgnum' ) || $self->ut_foreign_key('taxnum', 'cust_main_county', 'taxnum') - || $self->ut_number('year') - || $self->ut_number('month') + || $self->ut_numbern('year') + || $self->ut_numbern('month') || $self->ut_numbern('creditbillpkgnum') #no FK check, will have been del'ed || $self->ut_money('amount') + || $self->ut_flag('exempt_cust') + || $self->ut_flag('exempt_setup') + || $self->ut_flag('exempt_recur') + || $self->ut_flag('exempt_cust_taxname') + || $self->ut_flag('exempt_monthly') ; return $error if $error; diff --git a/FS/FS/discount.pm b/FS/FS/discount.pm index 88cbdd4..f6f9945 100644 --- a/FS/FS/discount.pm +++ b/FS/FS/discount.pm @@ -136,6 +136,7 @@ sub check { || $self->ut_floatn('months') #actually decimal, but this will do || $self->ut_enum('disabled', [ '', 'Y' ]) || $self->ut_enum('setup', [ '', 'Y' ]) + #|| $self->ut_enum('linked', [ '', 'Y' ]) ; return $error if $error; diff --git a/FS/FS/h_cust_main_exemption.pm b/FS/FS/h_cust_main_exemption.pm new file mode 100644 index 0000000..072c412 --- /dev/null +++ b/FS/FS/h_cust_main_exemption.pm @@ -0,0 +1,19 @@ +package FS::h_cust_main_exemption; + +use strict; +use base qw( FS::h_Common FS::cust_main_exemption ); + +sub table { 'h_cust_main_exemption' }; + +=head1 NAME + +FS::h_cust_main_exemption - Historical customer tax exemption records. + +=head1 SEE ALSO + +L<FS::cust_main_exemption>, L<FS::h_Common>, L<FS::Record>. + +=cut + +1; + diff --git a/FS/FS/h_part_pkg.pm b/FS/FS/h_part_pkg.pm new file mode 100644 index 0000000..2c0e65f --- /dev/null +++ b/FS/FS/h_part_pkg.pm @@ -0,0 +1,37 @@ +package FS::h_part_pkg; + +use strict; +use vars qw( @ISA ); +use base qw(FS::h_Common FS::part_pkg); + +sub table { 'h_part_pkg' }; + +sub _rebless {}; # don't try to rebless these + +=head1 NAME + +FS::h_part_pkg - Historical record of package definition. + +=head1 SYNOPSIS + +=head1 DESCRIPTION + +An FS::h_part_pkg object represents historical changes to package +definitions. + +=head1 BUGS + +Many important properties of a part_pkg are in other tables, especially +plan options, service allotments, and link/bundle relationships. The +methods to access those from the part_pkg will work, but they're +really accessing current, not historical, data. Be careful. + +=head1 SEE ALSO + +L<FS::part_pkg>, L<FS::h_Common>, L<FS::Record>, schema.html from the base +documentation. + +=cut + +1; + diff --git a/FS/FS/part_event/Action/pkg_agent_credit.pm b/FS/FS/part_event/Action/pkg_agent_credit.pm index 4bcee98..e1c77be 100644 --- a/FS/FS/part_event/Action/pkg_agent_credit.pm +++ b/FS/FS/part_event/Action/pkg_agent_credit.pm @@ -18,7 +18,7 @@ sub do_action { my $agent_cust_main = $agent->agent_cust_main; #? or return "No customer record for agent ". $agent->agent; - my $amount = $self->_calc_credit($cust_pkg); + my $amount = $self->_calc_credit($cust_pkg); return '' unless $amount > 0; my $reasonnum = $self->option('reasonnum'); @@ -29,6 +29,7 @@ sub do_action { 'eventnum' => $cust_event->eventnum, 'addlinfo' => 'for customer #'. $cust_main->display_custnum. ': '.$cust_main->name, + #'commission_agentnum' => $agent->agentnum, ); die "Error crediting customer ". $agent_cust_main->custnum. " for agent commission: $error" diff --git a/FS/FS/part_export/dma_radiusmanager.pm b/FS/FS/part_export/dma_radiusmanager.pm new file mode 100644 index 0000000..ab77c46 --- /dev/null +++ b/FS/FS/part_export/dma_radiusmanager.pm @@ -0,0 +1,336 @@ +package FS::part_export::dma_radiusmanager; + +use strict; +use vars qw($DEBUG %info %options); +use base 'FS::part_export'; +use FS::part_svc; +use FS::svc_acct; +use FS::radius_group; +use Tie::IxHash; +use Digest::MD5 'md5_hex'; + +tie %options, 'Tie::IxHash', + 'dbname' => { label=>'Database name', default=>'radius' }, + 'username' => { label=>'Database username' }, + 'password' => { label=>'Database password' }, + 'manager' => { label=>'Manager name' }, + 'groupid' => { label=>'Group ID', default=>'1' }, + 'service_prefix' => { label=>'Service name prefix' }, + 'nasnames' => { label=>'NAS IDs/addresses' }, + 'debug' => { label=>'Enable debugging', type=>'checkbox' }, +; + +%info = ( + 'svc' => 'svc_acct', + 'desc' => 'Export to DMA Radius Manager', + 'options' => \%options, + 'nodomain' => 'Y', + 'notes' => '', #XXX +); + +$DEBUG = 0; + +sub connect { + my $self = shift; + my $datasrc = 'dbi:mysql:host='.$self->machine. + ':database='.$self->option('dbname'); + DBI->connect( + $datasrc, + $self->option('username'), + $self->option('password'), + { AutoCommit => 0 } + ) or die $DBI::errstr; +} + +sub export_insert { my $self = shift; $self->dma_rm_queue('insert', @_) } +sub export_delete { my $self = shift; $self->dma_rm_queue('delete', @_) } +sub export_replace { my $self = shift; $self->dma_rm_queue('replace', @_) } +sub export_suspend { my $self = shift; $self->dma_rm_queue('suspend', @_) } +sub export_unsuspend { my $self = shift; $self->dma_rm_queue('unsuspend', @_) } + +sub dma_rm_queue { + my ($self, $action, $svc_acct, $old) = @_; + + my $svcnum = $svc_acct->svcnum; + + my $cust_pkg = $svc_acct->cust_svc->cust_pkg; + my $cust_main = $cust_pkg->cust_main; + my $location = $cust_pkg->cust_location; + + my %params = ( + # for the remote side + username => $svc_acct->username, + password => md5_hex($svc_acct->_password), + groupid => $self->option('groupid'), + enableuser => 1, + firstname => $cust_main->first, + lastname => $cust_main->last, + company => $cust_main->company, + phone => ($cust_main->daytime || $cust_main->night), + mobile => $cust_main->mobile, + address => $location->address1, # address2? + city => $location->city, + state => $location->state, + zip => $location->zip, + country => $location->country, + gpslat => $location->latitude, + gpslong => $location->longitude, + comment => 'svcnum'.$svcnum, + createdby => $self->option('manager'), + owner => $self->option('manager'), + email => $cust_main->invoicing_list_emailonly_scalar, + + # used internally by the export + exportnum => $self->exportnum, + svcnum => $svcnum, + action => $action, + svcpart => $svc_acct->cust_svc->svcpart, + _password => $svc_acct->_password, + ); + if ( $action eq 'replace' ) { + $params{'old_username'} = $old->username; + $params{'old_password'} = $old->_password; + } + my $queue = FS::queue->new({ + 'svcnum' => $svcnum, + 'job' => "FS::part_export::dma_radiusmanager::dma_rm_action", + }); + $queue->insert(%params); +} + +sub dma_rm_action { + my %params = @_; + my $svcnum = delete $params{svcnum}; + my $action = delete $params{action}; + my $svcpart = delete $params{svcpart}; + my $exportnum = delete $params{exportnum}; + + my $username = $params{username}; + my $password = delete $params{_password}; + + my $self = FS::part_export->by_key($exportnum); + my $dbh = $self->connect; + local $DEBUG = 1 if $self->option('debug'); + + # export the part_svc if needed, and get its srvid + my $part_svc = FS::part_svc->by_key($svcpart); + my $srvid = $self->export_part_svc($part_svc, $dbh); # dies on error + $params{srvid} = $srvid; + + if ( $action eq 'insert' ) { + warn "rm_users: inserting svcnum$svcnum\n" if $DEBUG; + my $sth = $dbh->prepare( 'INSERT INTO rm_users ( '. + join(', ', keys(%params)). + ') VALUES ('. + join(', ', ('?') x keys(%params)). + ')' + ); + $sth->execute(values(%params)) or die $dbh->errstr; + + # minor false laziness w/ sqlradius_insert + warn "radcheck: inserting $username\n" if $DEBUG; + $sth = $dbh->prepare( 'INSERT INTO radcheck ( + username, attribute, op, value + ) VALUES (?, ?, ?, ?)' ); + $sth->execute( + $username, + 'Cleartext-Password', + ':=', # :=( + $password, + ) or die $dbh->errstr; + + $sth->execute( + $username, + 'Simultaneous-Use', + ':=', + 1, # should this be an option? + ) or die $dbh->errstr; + # also, we don't support exporting any other radius attrs... + # those should go in 'custattr' if we need them + } elsif ( $action eq 'replace' ) { + + my $old_username = delete $params{old_username}; + my $old_password = delete $params{old_password}; + # svcnum is invariant and on the remote side, so we don't need any + # of the old fields to do this + warn "rm_users: updating svcnum$svcnum\n" if $DEBUG; + my $sth = $dbh->prepare( 'UPDATE rm_users SET '. + join(', ', map { "$_ = ?" } keys(%params)). + ' WHERE comment = ?' + ); + $sth->execute(values(%params), $params{comment}) or die $dbh->errstr; + # except for username/password changes + if ( $old_password ne $password ) { + warn "radcheck: changing password for $old_username\n" if $DEBUG; + $sth = $dbh->prepare( 'UPDATE radcheck SET value = ? '. + 'WHERE username = ? and attribute = \'Cleartext-Password\'' + ); + $sth->execute($password, $old_username) or die $dbh->errstr; + } + if ( $old_username ne $username ) { + warn "radcheck: changing username $old_username to $username\n" + if $DEBUG; + $sth = $dbh->prepare( 'UPDATE radcheck SET username = ? '. + 'WHERE username = ?' + ); + $sth->execute($username, $old_username) or die $dbh->errstr; + } + + } elsif ( $action eq 'suspend' ) { + + # this is sufficient + warn "rm_users: disabling svcnum#$svcnum\n" if $DEBUG; + my $sth = $dbh->prepare( 'UPDATE rm_users SET enableuser = 0 '. + 'WHERE comment = ?' + ); + $sth->execute($params{comment}) or die $dbh->errstr; + + } elsif ( $action eq 'unsuspend' ) { + + warn "rm_users: enabling svcnum#$svcnum\n" if $DEBUG; + my $sth = $dbh->prepare( 'UPDATE rm_users SET enableuser = 1 '. + 'WHERE comment = ?' + ); + $sth->execute($params{comment}) or die $dbh->errstr; + + } elsif ( $action eq 'delete' ) { + + warn "rm_users: deleting svcnum#$svcnum\n" if $DEBUG; + my $sth = $dbh->prepare( 'DELETE FROM rm_users WHERE comment = ?' ); + $sth->execute($params{comment}) or die $dbh->errstr; + + warn "radcheck: deleting $username\n" if $DEBUG; + $sth = $dbh->prepare( 'DELETE FROM radcheck WHERE username = ?' ); + $sth->execute($username) or die $dbh->errstr; + + # if this were smarter it would also delete the rm_services record + # if it was no longer in use, but that's not really necessary + } + + $dbh->commit; + ''; +} + +=item export_part_svc PART_SVC DBH + +Query Radius Manager for a service definition matching the name of +PART_SVC (optionally with a prefix defined in the export options). +If there is one, update it to match the attributes of PART_SVC; if +not, create one. Then return its srvid. + +=cut + +sub export_part_svc { + my ($self, $part_svc, $dbh) = @_; + + my $name = $self->option('service_prefix').$part_svc->svc; + + my %params = ( + 'srvname' => $name, + 'enableservice' => 1, + 'nextsrvid' => -1, + 'dailynextsrvid' => -1, + ); + my @fixed_groups; + # use speed settings from fixed usergroups configured on this part_svc + if ( my $psc = $part_svc->part_svc_column('usergroup') ) { + if ( $psc->columnflag eq 'F' ) { + # each part_svc really should only have one fixed group with non-null + # speed settings, but go by priority order for consistency + @fixed_groups = + sort { $a->priority <=> $b->priority } + grep { $_ } + map { FS::radius_group->by_key($_) } + split(/\s*,\s*/, $psc->columnvalue); + } + } # otherwise there are no fixed groups, so leave speed empty + + foreach (qw(down up)) { + my $speed = "speed_$_"; + foreach my $group (@fixed_groups) { + if ( ($group->$speed || 0) > 0 ) { + $params{$_.'rate'} = $group->$speed; + last; + } + } + } + # anything else we need here? poolname, maybe? + + warn "rm_services: looking for '$name'\n" if $DEBUG; + my $sth = $dbh->prepare( + 'SELECT srvid FROM rm_services WHERE srvname = ? AND enableservice = 1' + ); + $sth->execute($name) or die $dbh->errstr; + if ( $sth->rows > 1 ) { + die "Multiple services with name '$name' found in Radius Manager.\n"; + } elsif ( $sth->rows == 1 ) { + my $row = $sth->fetchrow_arrayref; + my $srvid = $row->[0]; + warn "rm_services: updating srvid#$srvid\n" if $DEBUG; + $sth = $dbh->prepare( + 'UPDATE rm_services SET '.join(', ', map {"$_ = ?"} keys %params) . + ' WHERE srvid = ?' + ); + $sth->execute(values(%params), $srvid) or die $dbh->errstr; + return $srvid; + } else { # $sth->rows == 0 + # create a new one + # but first... get the next available srvid + $sth = $dbh->prepare('SELECT MAX(srvid) FROM rm_services'); + $sth->execute or die $dbh->errstr; + my $srvid = 1; # just in case you somehow have nothing in your database + if ( $sth->rows ) { + $srvid = $sth->fetchrow_arrayref->[0] + 1; + } + $params{'srvid'} = $srvid; + # NOW create a new one + warn "rm_services: inserting '$name' as srvid#$srvid\n" if $DEBUG; + $sth = $dbh->prepare( + 'INSERT INTO rm_services ('.join(', ', keys %params). + ') VALUES ('.join(', ', map {'?'} keys %params).')' + ); + $sth->execute(values(%params)) or die $dbh->errstr; + # also link it to our manager name + warn "rm_services: linking to manager\n" if $DEBUG; + $sth = $dbh->prepare( + 'INSERT INTO rm_allowedmanagers (srvid, managername) VALUES (?, ?)' + ); + $sth->execute($srvid, $self->option('manager')) or die $dbh->errstr; + # and allow it on our NAS + $sth = $dbh->prepare( + 'INSERT INTO rm_allowednases (srvid, nasid) VALUES (?, ?)' + ); + foreach my $nasid ($self->nas_ids($dbh)) { + warn "rm_services: linking to nasid#$nasid\n" if $DEBUG; + $sth->execute($srvid, $nasid) or die $dbh->errstr; + } + return $srvid; + } +} + +=item nas_ids DBH + +Convert the 'nasnames option into a list of real NAS ids. + +=cut + +sub nas_ids { + my $self = shift; + my $dbh = shift; + + my @nasnames = split(/\s*,\s*/, $self->option('nasnames')); + return unless @nasnames; + # pass these through unchanged + my @ids = grep { /^\d+$/ } @nasnames; + @nasnames = grep { not /^\d+$/ } @nasnames; + my $in_nasnames = join(',', map {$dbh->quote($_)} @nasnames); + + my $sth = $dbh->prepare("SELECT id FROM nas WHERE nasname IN ($in_nasnames)"); + $sth->execute or die $dbh->errstr; + my $rows = $sth->fetchall_arrayref; + push @ids, $_->[0] foreach @$rows; + + return @ids; +} + +1; diff --git a/FS/FS/part_export/shellcommands.pm b/FS/FS/part_export/shellcommands.pm index ca4e524..f964af3 100644 --- a/FS/FS/part_export/shellcommands.pm +++ b/FS/FS/part_export/shellcommands.pm @@ -490,7 +490,7 @@ sub ssh_cmd { #subroutine, not method my ($output, $errput) = $ssh->capture2($ssh_opt, $opt->{'command'}); return if $opt->{'ignore_all_errors'}; - die "Error running SSH command: ". $ssh->error if $ssh->error; + #die "Error running SSH command: ". $ssh->error if $ssh->error; if ( ($output || $errput) && $opt->{'ignored_errors'} && length($opt->{'ignored_errors'}) @@ -504,7 +504,9 @@ sub ssh_cmd { #subroutine, not method $errput =~ s/[\s\n]//g; } - die "$errput\n" if $errput; + die (($errput || $ssh->error). "\n") if $errput || $ssh->error; + #die "$errput\n" if $errput; + die "$output\n" if $output and $opt->{'fail_on_output'}; ''; } diff --git a/FS/FS/radius_group.pm b/FS/FS/radius_group.pm index 37aa0f3..f1a4efe 100644 --- a/FS/FS/radius_group.pm +++ b/FS/FS/radius_group.pm @@ -47,6 +47,8 @@ description priority - for export +=item speed_up, speed_down - connection speeds in bits per second. Some +exports may use this to generate appropriate RADIUS attributes. =back @@ -176,6 +178,8 @@ sub check { || $self->ut_text('groupname') || $self->ut_textn('description') || $self->ut_numbern('priority') + || $self->ut_numbern('speed_up') + || $self->ut_numbern('speed_down') ; return $error if $error; diff --git a/FS/FS/rate.pm b/FS/FS/rate.pm index 02d8250..a2511cf 100644 --- a/FS/FS/rate.pm +++ b/FS/FS/rate.pm @@ -387,7 +387,7 @@ sub rate_detail { =item process -Experimental job-queue processor for web interface adds/edits +Job-queue processor for web interface adds/edits =cut diff --git a/FS/FS/svc_Tower_Mixin.pm b/FS/FS/svc_Tower_Mixin.pm index 0b55884..6adbc6f 100644 --- a/FS/FS/svc_Tower_Mixin.pm +++ b/FS/FS/svc_Tower_Mixin.pm @@ -52,5 +52,4 @@ sub tower_sector_sql { @where; } - 1; diff --git a/FS/MANIFEST b/FS/MANIFEST index 479dcad..b5ee87e 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -94,6 +94,7 @@ FS/h_cust_pkg_reason.pm FS/h_cust_svc.pm FS/h_cust_tax_exempt.pm FS/h_domain_record.pm +FS/h_part_pkg.pm FS/h_svc_acct.pm FS/h_svc_broadband.pm FS/h_svc_domain.pm diff --git a/bin/231commit b/bin/231commit index ca28ede..6d09863 100755 --- a/bin/231commit +++ b/bin/231commit @@ -20,8 +20,8 @@ die "no files!" unless @ARGV; system join('', "( cd /home/$USER/freeside2.3/$prefix; git pull ) && ", "( cd /home/$USER/freeside2.1/$prefix; git pull ) && ", - "git diff -u @ARGV | ( cd /home/$USER/freeside2.3/$prefix; patch ) ", - " && git diff -u @ARGV | ( cd /home/$USER/freeside2.1/$prefix; patch ) ", + "git diff -u @ARGV | ( cd /home/$USER/freeside2.3/$prefix; patch -p1 ) ", + " && git diff -u @ARGV | ( cd /home/$USER/freeside2.1/$prefix; patch -p1 ) ", " && ( ( git commit -m $desc @ARGV && git push); ", "( cd /home/$USER/freeside2.3/$prefix; git commit -m $desc @ARGV && git push); ", "( cd /home/$USER/freeside2.1/$prefix; git commit -m $desc @ARGV && git push) )" @@ -3,7 +3,8 @@ my $file = shift; chomp(my $dir = `pwd`); -$dir =~ s/freeside\//freeside2.3\//; +$dir =~ s/freeside(\/?)/freeside2.3$1/; +warn $dir; #$cmd = "diff -u $file $dir/$file"; $cmd = "diff -u $dir/$file $file"; diff --git a/bin/cdr.import b/bin/cdr.import index 36266ef..36266ef 100644..100755 --- a/bin/cdr.import +++ b/bin/cdr.import diff --git a/bin/cust_main-bill_now b/bin/cust_main-bill_now index 17e48fb..f8a1580 100644..100755 --- a/bin/cust_main-bill_now +++ b/bin/cust_main-bill_now @@ -13,7 +13,9 @@ my $custnum = shift or die &usage; my $cust_main = qsearchs('cust_main', { 'custnum' => $custnum } ) or die "unknown custnum $custnum\n"; -$cust_main->bill_and_collect( debug=>2, check_freq=>'1d' ); +$FS::cust_main::DEBUG = 3; + +$cust_main->bill_and_collect( debug=>3, check_freq=>'1d' ); sub usage { die "Usage:\n cust_main-bill_now user custnum\n"; @@ -7,12 +7,15 @@ chomp( my $mw_password = `cat .mw-password` ); my $site_perl = "./FS"; #my $html = "Freeside:1.7:Documentation:Developer"; -my $html = "Freeside:1.9:Documentation:Developer"; +#my $html = "Freeside:1.9:Documentation:Developer"; +my $html = "Freeside:3:Documentation:Developer"; foreach my $dir ( $html, - map "$html/$_", qw( bin FS FS/UI FS/part_export FS/part_pkg + map "$html/$_", qw( bin FS + FS/cdr FS/cust_main FS/cust_pkg FS/detail_format FS/part_event FS/part_event/Condition FS/part_event/Action + FS/part_export FS/part_pkg FS/pay_batch FS/ClientAPI FS/Cron FS/Misc FS/Report FS/Report/Table FS/TicketSystem FS/UI FS/SelfService @@ -43,6 +46,7 @@ foreach my $file ( use WWW::Mediawiki::Client; my $mvs = WWW::Mediawiki::Client->new( 'host' => 'www.freeside.biz', + 'protocol' => 'https', 'wiki_path' => 'mediawiki/index.php', 'username' => $mw_username, 'password' => $mw_password, diff --git a/bin/tax_location.upgrade b/bin/tax_location.upgrade new file mode 100755 index 0000000..8140945 --- /dev/null +++ b/bin/tax_location.upgrade @@ -0,0 +1,31 @@ +#!/usr/bin/perl + +use FS::UID qw(adminsuidsetup); +use FS::Record; +use FS::cust_bill_pkg; +use Date::Parse qw(str2time); +use Getopt::Std; +getopts('s:e:'); +my $username = shift @ARGV; + +if (!$username) { + print +"Usage: tax_location.upgrade [ -s START ] [ -e END ] username + +This script creates cust_bill_pkg_tax_location and cust_tax_exempt_pkg records +for existing sales tax records prior to the 3.0 cust_location changes. Changes +will be committed immediately; back up your data and run 'make +install-perl-modules' and 'freeside-upgrade' before running this script. +START and END specify an optional range of invoice dates to upgrade. + +"; + exit(1); +} + +my %opt; +$opt{s} = str2time($opt_s) if $opt_s; +$opt{e} = str2time($opt_e) if $opt_e; + +adminsuidsetup($username); +FS::cust_bill_pkg->upgrade_tax_location(%opt); +1; diff --git a/fs_selfservice/DEPLOY b/fs_selfservice/DEPLOY index e73012f..bedb5ec 100755 --- a/fs_selfservice/DEPLOY +++ b/fs_selfservice/DEPLOY @@ -11,7 +11,7 @@ perl Makefile.PL && make && make install cd .. #( cd ..; make deploy; cd fs_selfservice ) -( cd ..; make clean; make install-perl-modules; /etc/init.d/freeside restart; cd fs_selfservice ) +( cd ..; make clean; make configure-rt; make install-perl-modules; /etc/init.d/freeside restart; cd fs_selfservice ) #cp /home/ivan/freeside/fs_selfservice/FS-SelfService/cgi/* /var/www/MyAccount #chown freeside /var/www/MyAccount/*.cgi diff --git a/fs_selfservice/FS-SelfService/cgi/agent.cgi b/fs_selfservice/FS-SelfService/cgi/agent.cgi index 0af94cd..0af94cd 100644..100755 --- a/fs_selfservice/FS-SelfService/cgi/agent.cgi +++ b/fs_selfservice/FS-SelfService/cgi/agent.cgi diff --git a/fs_selfservice/FS-SelfService/cgi/cust_bill-logo.cgi b/fs_selfservice/FS-SelfService/cgi/cust_bill-logo.cgi index 253f853..253f853 100644..100755 --- a/fs_selfservice/FS-SelfService/cgi/cust_bill-logo.cgi +++ b/fs_selfservice/FS-SelfService/cgi/cust_bill-logo.cgi diff --git a/fs_selfservice/FS-SelfService/cgi/xmlrpc.cgi b/fs_selfservice/FS-SelfService/cgi/xmlrpc.cgi index d5a8e20..d5a8e20 100644..100755 --- a/fs_selfservice/FS-SelfService/cgi/xmlrpc.cgi +++ b/fs_selfservice/FS-SelfService/cgi/xmlrpc.cgi diff --git a/httemplate/browse/cust_note_class.html b/httemplate/browse/cust_note_class.html index f5d450b..7928199 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 fbf6d37..98e81ab 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 fab8cd0..e40b243 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 b195eb3..9bcd1e7 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 6f97a79..4a71f69 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 ba4c5b1..c5eee0c 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 90eab4a..25644e5 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 0c99b4c..d3ef40c 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 30a60ec..ee86251 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 b6b70a0..4c0fa4a 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 3a5155a..1b767f8 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 cf60d8f..d7e8128 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 2786f57..42a52d1 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 9571d22..0000000 --- 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 a39f52d..0000000 --- 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 0042012..0000000 --- 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 15591b9..0000000 --- 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 e2db6d5..0000000 --- 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 1106f9e..0000000 --- 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 79b8185..0000000 --- a/httemplate/view/cust_main/custom_content/.svc_phone.html.swp +++ /dev/null diff --git a/rt/sbin/rt-server.fcgi.in b/rt/sbin/rt-server.fcgi.in index 45c3770..f84f6c1 100644 --- a/rt/sbin/rt-server.fcgi.in +++ b/rt/sbin/rt-server.fcgi.in @@ -172,7 +172,7 @@ if (caller) { require Plack::Runner; my $is_fastcgi = $0 =~ m/fcgi$/; -my $r = Plack::Runner->new( $0 =~ 'standalone' ? ( server => 'Standalone' ) : +my $r = Plack::Runner->new( $0 =~ /standalone/ ? ( server => 'Standalone' ) : $is_fastcgi ? ( server => 'FCGI' ) : (), env => 'deployment' ); diff --git a/rt/sbin/rt-server.in b/rt/sbin/rt-server.in index 45c3770..f84f6c1 100644 --- a/rt/sbin/rt-server.in +++ b/rt/sbin/rt-server.in @@ -172,7 +172,7 @@ if (caller) { require Plack::Runner; my $is_fastcgi = $0 =~ m/fcgi$/; -my $r = Plack::Runner->new( $0 =~ 'standalone' ? ( server => 'Standalone' ) : +my $r = Plack::Runner->new( $0 =~ /standalone/ ? ( server => 'Standalone' ) : $is_fastcgi ? ( server => 'FCGI' ) : (), env => 'deployment' ); diff --git a/rt/sbin/rt-test-dependencies.in b/rt/sbin/rt-test-dependencies.in index 37ef32f..960d640 100644 --- a/rt/sbin/rt-test-dependencies.in +++ b/rt/sbin/rt-test-dependencies.in @@ -56,9 +56,10 @@ no warnings qw(numeric redefine); use Getopt::Long; my %args; my %deps; +my @orig_argv = @ARGV; GetOptions( \%args, 'v|verbose', - 'install', 'with-MYSQL', + 'install!', 'with-MYSQL', 'with-POSTGRESQL|with-pg|with-pgsql', 'with-SQLITE', 'with-ORACLE', 'with-FASTCGI', 'with-MODPERL1', 'with-MODPERL2', @@ -293,7 +294,7 @@ Test::LongString . $deps{'FASTCGI'} = [ text_to_hash( << '.') ]; -FCGI +FCGI 0.74 FCGI::ProcManager . @@ -344,7 +345,7 @@ URI 1.59 $deps{'GRAPHVIZ'} = [ text_to_hash( << '.') ]; GraphViz -IPC::Run +IPC::Run 0.90 . $deps{'GD'} = [ text_to_hash( << '.') ]; @@ -359,6 +360,7 @@ Convert::Color my %AVOID = ( 'DBD::Oracle' => [qw(1.23)], + 'Email::Address' => [qw(1.893 1.894)], ); if ($args{'download'}) { @@ -403,7 +405,12 @@ foreach my $type (sort grep $args{$_}, keys %args) { $Missing_By_Type{$type} = \%missing if keys %missing; } -conclude(%Missing_By_Type); +if ( $args{'install'} && keys %Missing_By_Type ) { + exec($0, @orig_argv, '--no-install'); +} +else { + conclude(%Missing_By_Type); +} sub test_deps { my @deps = @_; diff --git a/rt/share/html/Search/Results.xls b/rt/share/html/Search/Results.xls index 52a05da..8b94e22 100644 --- a/rt/share/html/Search/Results.xls +++ b/rt/share/html/Search/Results.xls @@ -54,6 +54,7 @@ $Format => undef <%INIT> use Spreadsheet::WriteExcel; +use OLE::Storage_Lite; use List::Util qw( max ); use Date::Format qw( time2str ); diff --git a/rt/t/api/config.t b/rt/t/api/config.t index a986c3c..62b77df 100644 --- a/rt/t/api/config.t +++ b/rt/t/api/config.t @@ -1,7 +1,8 @@ use strict; use warnings; use RT; -use RT::Test nodb => 1, tests => 9; +use RT::Test nodb => 1, tests => 11; +use Test::Warn; ok( RT::Config->AddOption( @@ -31,3 +32,12 @@ is( $meta->{Widget}, '/Widgets/Form/Boolean', 'widget is updated to boolean' ); ok( RT::Config->DeleteOption( Name => 'foo' ), 'removed option foo' ); is( RT::Config->Meta('foo'), undef, 'foo is indeed deleted' ); +# Test EmailInputEncodings PostLoadCheck code +RT::Config->Set('EmailInputEncodings', qw(utf-8 iso-8859-1 us-ascii foo)); +my @encodings = qw(utf-8-strict iso-8859-1 ascii); + +warning_is {RT::Config->PostLoadCheck} "Unknown encoding 'foo' in \@EmailInputEncodings option", + 'Correct warning for encoding foo'; + +my @canonical_encodings = RT::Config->Get('EmailInputEncodings'); +is_deeply(\@encodings, \@canonical_encodings, 'Got correct encoding list'); diff --git a/rt/t/api/template-insert.t b/rt/t/api/template-insert.t deleted file mode 100644 index 1bf5fc3..0000000 --- a/rt/t/api/template-insert.t +++ /dev/null @@ -1,26 +0,0 @@ -#!/usr/bin/perl - -use warnings; -use strict; - - -use RT; -use RT::Test tests => 7; - - - -# This tiny little test script triggers an interaction bug between DBD::Oracle 1.16, SB 1.15 and RT 3.4 - -use_ok('RT::Template'); -my $template = RT::Template->new(RT->SystemUser); - -isa_ok($template, 'RT::Template'); -my ($val,$msg) = $template->Create(Queue => 1, - Name => 'InsertTest', - Content => 'This is template content'); -ok($val,$msg); -is($template->Name, 'InsertTest'); -is($template->Content, 'This is template content', "We created the object right"); -($val, $msg) = $template->SetContent( 'This is new template content'); -ok($val,$msg); -is($template->Content, 'This is new template content', "We managed to _Set_ the content"); diff --git a/rt/t/api/template-simple.t b/rt/t/api/template-simple.t deleted file mode 100644 index bbdebb3..0000000 --- a/rt/t/api/template-simple.t +++ /dev/null @@ -1,275 +0,0 @@ -use strict; -use warnings; -use RT; -use RT::Test tests => 231; -use Test::Warn; - -my $queue = RT::Queue->new(RT->SystemUser); -$queue->Load("General"); - -my $ticket_cf = RT::CustomField->new(RT->SystemUser); -$ticket_cf->Create( - Name => 'Department', - Queue => '0', - Type => 'FreeformSingle', -); - -my $txn_cf = RT::CustomField->new(RT->SystemUser); -$txn_cf->Create( - Name => 'Category', - LookupType => RT::Transaction->CustomFieldLookupType, - Type => 'FreeformSingle', -); -$txn_cf->AddToObject($queue); - -my $ticket = RT::Ticket->new(RT->SystemUser); -my ($id, $msg) = $ticket->Create( - Subject => "template testing", - Queue => "General", - Owner => 'root@localhost', - Requestor => ["dom\@example.com"], - "CustomField-" . $txn_cf->id => "Special", -); -ok($id, "Created ticket: $msg"); -my $txn = $ticket->Transactions->First; - -$ticket->AddCustomFieldValue( - Field => 'Department', - Value => 'Coolio', -); - -TemplateTest( - Content => "\ntest", - PerlOutput => "test", - SimpleOutput => "test", -); - -TemplateTest( - Content => "\ntest { 5 * 5 }", - PerlOutput => "test 25", - SimpleOutput => "test { 5 * 5 }", -); - -TemplateTest( - Content => "\ntest { \$Requestor }", - PerlOutput => "test dom\@example.com", - SimpleOutput => "test dom\@example.com", -); - -TemplateTest( - Content => "\ntest { \$TicketSubject }", - PerlOutput => "test ", - SimpleOutput => "test template testing", -); - -SimpleTemplateTest( - Content => "\ntest { \$TicketQueueId }", - Output => "test 1", -); - -SimpleTemplateTest( - Content => "\ntest { \$TicketQueueName }", - Output => "test General", -); - -SimpleTemplateTest( - Content => "\ntest { \$TicketOwnerId }", - Output => "test 12", -); - -SimpleTemplateTest( - Content => "\ntest { \$TicketOwnerName }", - Output => "test root", -); - -SimpleTemplateTest( - Content => "\ntest { \$TicketOwnerEmailAddress }", - Output => "test root\@localhost", -); - -SimpleTemplateTest( - Content => "\ntest { \$TicketStatus }", - Output => "test new", -); - -SimpleTemplateTest( - Content => "\ntest #{ \$TicketId }", - Output => "test #" . $ticket->id, -); - -SimpleTemplateTest( - Content => "\ntest { \$TicketCFDepartment }", - Output => "test Coolio", -); - -SimpleTemplateTest( - Content => "\ntest #{ \$TransactionId }", - Output => "test #" . $txn->id, -); - -SimpleTemplateTest( - Content => "\ntest { \$TransactionType }", - Output => "test Create", -); - -SimpleTemplateTest( - Content => "\ntest { \$TransactionCFCategory }", - Output => "test Special", -); - -SimpleTemplateTest( - Content => "\ntest { \$TicketDelete }", - Output => "test { \$TicketDelete }", -); - -SimpleTemplateTest( - Content => "\ntest { \$Nonexistent }", - Output => "test { \$Nonexistent }", -); - -warning_like { - TemplateTest( - Content => "\ntest { \$Ticket->Nonexistent }", - PerlOutput => undef, - SimpleOutput => "test { \$Ticket->Nonexistent }", - ); -} qr/RT::Ticket::Nonexistent Unimplemented/; - -warning_like { - TemplateTest( - Content => "\ntest { \$Nonexistent->Nonexistent }", - PerlOutput => undef, - SimpleOutput => "test { \$Nonexistent->Nonexistent }", - ); -} qr/Can't call method "Nonexistent" on an undefined value/; - -TemplateTest( - Content => "\ntest { \$Ticket->OwnerObj->Name }", - PerlOutput => "test root", - SimpleOutput => "test { \$Ticket->OwnerObj->Name }", -); - -warning_like { - TemplateTest( - Content => "\ntest { *!( }", - SyntaxError => 1, - PerlOutput => undef, - SimpleOutput => "test { *!( }", - ); -} qr/Template parsing error: syntax error/; - -TemplateTest( - Content => "\ntest { \$rtname ", - SyntaxError => 1, - PerlOutput => undef, - SimpleOutput => undef, -); - -is($ticket->Status, 'new', "test setup"); -SimpleTemplateTest( - Content => "\ntest { \$Ticket->SetStatus('resolved') }", - Output => "test { \$Ticket->SetStatus('resolved') }", -); -is($ticket->Status, 'new', "simple templates can't call ->SetStatus"); - -# Make sure changing the template's type works -my $template = RT::Template->new(RT->SystemUser); -$template->Create( - Name => "type chameleon", - Type => "Perl", - Content => "\ntest { 10 * 7 }", -); -ok($id = $template->id, "Created template"); -$template->Parse; -is($template->MIMEObj->stringify_body, "test 70", "Perl output"); - -$template = RT::Template->new(RT->SystemUser); -$template->Load($id); -is($template->Name, "type chameleon"); - -$template->SetType('Simple'); -$template->Parse; -is($template->MIMEObj->stringify_body, "test { 10 * 7 }", "Simple output"); - -$template = RT::Template->new(RT->SystemUser); -$template->Load($id); -is($template->Name, "type chameleon"); - -$template->SetType('Perl'); -$template->Parse; -is($template->MIMEObj->stringify_body, "test 70", "Perl output"); - -undef $ticket; - -my $counter = 0; -sub IndividualTemplateTest { - local $Test::Builder::Level = $Test::Builder::Level + 1; - - my %args = ( - Name => "Test-" . ++$counter, - Type => "Perl", - @_, - ); - - my $t = RT::Template->new(RT->SystemUser); - $t->Create( - Name => $args{Name}, - Type => $args{Type}, - Content => $args{Content}, - ); - - ok($t->id, "Created $args{Type} template"); - is($t->Name, $args{Name}, "$args{Type} template name"); - is($t->Content, $args{Content}, "$args{Type} content"); - is($t->Type, $args{Type}, "template type"); - - # this should never blow up! - my ($ok, $msg) = $t->CompileCheck; - - # we don't need to syntax check simple templates since if you mess them up - # it's safe to just use the input directly as the template's output - if ($args{SyntaxError} && $args{Type} eq 'Perl') { - ok(!$ok, "got a syntax error"); - } - else { - ok($ok, $msg); - } - - ($ok, $msg) = $t->Parse( - TicketObj => $ticket, - TransactionObj => $txn, - ); - if (defined $args{Output}) { - ok($ok, $msg); - is($t->MIMEObj->stringify_body, $args{Output}, "$args{Type} template's output"); - } - else { - ok(!$ok, "expected a failure"); - } -} - -sub TemplateTest { - local $Test::Builder::Level = $Test::Builder::Level + 1; - my %args = @_; - - for my $type ('Perl', 'Simple') { - next if $args{"Skip$type"}; - - IndividualTemplateTest( - %args, - Type => $type, - Output => $args{$type . 'Output'}, - ); - } -} - -sub SimpleTemplateTest { - local $Test::Builder::Level = $Test::Builder::Level + 1; - my %args = @_; - - IndividualTemplateTest( - %args, - Type => 'Simple', - ); -} - diff --git a/rt/t/api/template.t b/rt/t/api/template.t index 2fadede..331d9f9 100644 --- a/rt/t/api/template.t +++ b/rt/t/api/template.t @@ -1,25 +1,34 @@ -use strict; use warnings; -use RT; -use RT::Test tests => 2; - - -{ - -ok(require RT::Template); +use strict; +use RT; +use RT::Test tests => 10; -} +my $queue = RT::Test->load_or_create_queue( Name => 'Templates' ); +ok $queue && $queue->id, "loaded or created a queue"; { - -my $t = RT::Template->new(RT->SystemUser); -$t->Create(Name => "Foo", Queue => 1); -my $t2 = RT::Template->new(RT->Nobody); -$t2->Load($t->Id); -ok($t2->QueueObj->id, "Got the template's queue objet"); - - + my $template = RT::Template->new( RT->SystemUser ); + isa_ok($template, 'RT::Template'); + my ($val,$msg) = $template->Create( + Queue => $queue->id, + Name => 'InsertTest', + Content => 'This is template content' + ); + ok $val, "created a template" or diag "error: $msg"; + ok my $id = $template->id, "id is defined"; + is $template->Name, 'InsertTest'; + is $template->Content, 'This is template content', "We created the object right"; + + ($val, $msg) = $template->SetContent( 'This is new template content'); + ok $val, "changed content" or diag "error: $msg"; + + is $template->Content, 'This is new template content', "We managed to _Set_ the content"; + + ($val, $msg) = $template->Delete; + ok $val, "deleted template"; + + $template->Load($id); + ok !$template->id, "can not load template after deletion"; } - diff --git a/rt/t/articles/search-interface.t b/rt/t/articles/search-interface.t index eb3a4f7..bcba311 100644 --- a/rt/t/articles/search-interface.t +++ b/rt/t/articles/search-interface.t @@ -3,7 +3,7 @@ use strict; use warnings; -use RT::Test tests => 23; +use RT::Test tests => 44; use RT::CustomField; use RT::Queue; @@ -67,7 +67,12 @@ my %cvals = ('article1q' => 'Some question about swallows', 'article3q' => 'Why should I eat my supper?', 'article3a' => 'There are starving children in Africa', 'article4q' => 'What did Brian originally write?', - 'article4a' => 'Romanes eunt domus'); + 'article4a' => 'This is an answer that is longer than 255 ' + . 'characters so these tests will be sure to use the LargeContent ' + . 'SQL as well as the normal SQL that would be generated if this ' + . 'was an answer that was shorter than 255 characters. This second ' + . 'sentence has a few extra characters to get this string to go ' + . 'over the 255 character boundary. Lorem ipsum.'); # Create an article or two with our custom field values. @@ -108,6 +113,52 @@ isa_ok($m, 'Test::WWW::Mechanize'); ok($m->login, 'logged in'); $m->follow_link_ok( { text => 'Articles', url_regex => qr!^/Articles/! }, 'UI -> Articles' ); -$m->follow_link_ok( {text => 'Search'}, 'Articles -> Search'); -$m->follow_link_ok( {text => 'in class '.$class->Name}, 'Articles in class '.$class->Name); -$m->content_contains($article1->Name); + +# In all of the search results below, the results page should +# have the summary text of the article it occurs in. + +# Case sensitive search on small field. +DoArticleSearch($m, $class->Name, 'Africa'); +$m->text_contains('Search results'); # Did we do a search? +$m->text_contains('blah blah 1'); + +# Case insensitive search on small field. +DoArticleSearch($m, $class->Name, 'africa'); +$m->text_contains('Search results'); # Did we do a search? +$m->text_contains('blah blah 1'); + +# Case sensitive search on large field. +DoArticleSearch($m, $class->Name, 'ipsum'); +$m->text_contains('Search results'); # Did we do a search? +$m->text_contains('hoi polloi 4'); + +# Case insensitive search on large field. +DoArticleSearch($m, $class->Name, 'lorem'); +$m->text_contains('Search results'); # Did we do a search? +TODO:{ + local $TODO = 'Case insensitive search on LONGBLOB not available in MySQL' + if RT->Config->Get('DatabaseType') eq 'mysql'; + $m->text_contains('hoi polloi 4'); +} + +# When you send $m to this sub, it must be on a page with +# a Search link. +sub DoArticleSearch{ + my $m = shift; + my $class_name = shift; + my $search_text = shift; + + $m->follow_link_ok( {text => 'Search'}, 'Articles -> Search'); + $m->follow_link_ok( {text => 'in class '. $class_name}, 'Articles in class '. $class_name); + $m->text_contains('First article'); + + $m->submit_form_ok( { + form_number => 3, + fields => { + 'Article~' => $search_text + }, + }, "Search for $search_text" + ); + return; +} + diff --git a/rt/t/articles/uri-a.t b/rt/t/articles/uri-a.t index 82d0f1b..5c1fdaf 100644 --- a/rt/t/articles/uri-a.t +++ b/rt/t/articles/uri-a.t @@ -3,7 +3,7 @@ use strict; use warnings; -use RT::Test tests => 7; +use RT::Test tests => 15; use_ok("RT::URI::a"); my $uri = RT::URI::a->new($RT::SystemUser); @@ -26,3 +26,39 @@ is(ref($uri->Object), "RT::Article", "Object loaded is an article"); is($uri->Object->Id, $article->Id, "Object loaded has correct ID"); is($article->URI, 'fsck.com-article://example.com/article/'.$article->Id, "URI object has correct URI string"); + +{ + my $aid = $article->id; + my $ticket = RT::Ticket->new( RT->SystemUser ); + my ($id, $msg) = $ticket->Create( + Queue => 1, + Subject => 'test ticket', + ); + ok $id, "Created a test ticket"; + + # Try searching + my $tickets = RT::Tickets->new( RT->SystemUser ); + $tickets->FromSQL(" RefersTo = 'a:$aid' "); + is $tickets->Count, 0, "No results yet"; + + # try with the full uri + $tickets->FromSQL(" RefersTo = '@{[ $article->URI ]}' "); + is $tickets->Count, 0, "Still no results"; + + # add the link + $ticket->AddLink( Type => 'RefersTo', Target => "a:$aid" ); + + # verify the ticket has it + my @links = @{$ticket->RefersTo->ItemsArrayRef}; + is scalar @links, 1, "Has one RefersTo link"; + is ref $links[0]->TargetObj, "RT::Article", "Link points to an article"; + is $links[0]->TargetObj->id, $aid, "Link points to the article we specified"; + + # search again + $tickets->FromSQL(" RefersTo = 'a:$aid' "); + is $tickets->Count, 1, "Found one ticket with short URI"; + + # search with the full uri + $tickets->FromSQL(" RefersTo = '@{[ $article->URI ]}' "); + is $tickets->Count, 1, "Found one ticket with full URI"; +} diff --git a/rt/t/data/configs/apache2.2+fastcgi.conf.in b/rt/t/data/configs/apache2.2+fastcgi.conf.in index 3ec36dd..03eaa9a 100644 --- a/rt/t/data/configs/apache2.2+fastcgi.conf.in +++ b/rt/t/data/configs/apache2.2+fastcgi.conf.in @@ -12,6 +12,7 @@ Group @WEB_GROUP@ </IfModule> </IfModule> +ServerName localhost Listen %%LISTEN%% ErrorLog "%%LOG_FILE%%" diff --git a/rt/t/data/configs/apache2.2+mod_perl.conf.in b/rt/t/data/configs/apache2.2+mod_perl.conf.in index 3b1f3f6..20d2f44 100644 --- a/rt/t/data/configs/apache2.2+mod_perl.conf.in +++ b/rt/t/data/configs/apache2.2+mod_perl.conf.in @@ -30,6 +30,7 @@ Group @WEB_GROUP@ </IfModule> </IfModule> +ServerName localhost Listen %%LISTEN%% ErrorLog "%%LOG_FILE%%" diff --git a/rt/t/mail/dashboard-chart-with-utf8.t b/rt/t/mail/dashboard-chart-with-utf8.t index 6d07b96..79f5f0e 100644 --- a/rt/t/mail/dashboard-chart-with-utf8.t +++ b/rt/t/mail/dashboard-chart-with-utf8.t @@ -1,7 +1,17 @@ use strict; use warnings; -use RT::Test tests => 15; +BEGIN { + require RT::Test; + + if (eval { require GD }) { + RT::Test->import(tests => 15); + } + else { + RT::Test->import(skip_all => 'GD required.'); + } +} + use utf8; my $root = RT::Test->load_or_create_user( Name => 'root' ); diff --git a/rt/t/mail/dashboards.t b/rt/t/mail/dashboards.t index 7a7a54c..00cfc6a 100644 --- a/rt/t/mail/dashboards.t +++ b/rt/t/mail/dashboards.t @@ -2,7 +2,7 @@ use strict; use warnings; -use RT::Test tests => 187; +use RT::Test tests => 181; use Test::Warn; use RT::Dashboard::Mailer; @@ -138,17 +138,6 @@ sub delete_dashboard { # {{{ ok($ok, $msg); } # }}} -sub delete_subscriptions { # {{{ - my $subscription_id = shift; - # delete the dashboard and make sure we get exactly one subscription failure - # notice - my $user = RT::User->new(RT->SystemUser); - $user->Load('root'); - for my $subscription ($user->Attributes->Named('Subscription')) { - $subscription->Delete; - } -} # }}} - my $good_time = 1290423660; # 6:01 EST on a monday my $bad_time = 1290427260; # 7:01 EST on a monday @@ -223,21 +212,9 @@ SKIP: { delete_dashboard($dashboard_id); -warning_like { - RT::Dashboard::Mailer->MailDashboards(All => 1); -} qr/Unable to load dashboard $dashboard_id of subscription $subscription_id for user root/; - -@mails = RT::Test->fetch_caught_mails; -is(@mails, 1, "one mail for subscription failure"); -$mail = parse_mail($mails[0]); -is($mail->head->get('Subject'), "[example.com] Missing dashboard!\n"); -is($mail->head->get('From'), "dashboard\@example.com\n"); -is($mail->head->get('X-RT-Dashboard-Id'), "$dashboard_id\n"); -is($mail->head->get('X-RT-Dashboard-Subscription-Id'), "$subscription_id\n"); - RT::Dashboard::Mailer->MailDashboards(All => 1); @mails = RT::Test->fetch_caught_mails; -is(@mails, 0, "no mail because the subscription notice happens only once"); +is(@mails, 0, "no mail because the subscription is deleted"); RT::Test->stop_server; RT::Test->clean_caught_mails; @@ -277,7 +254,6 @@ RT->Config->Set('EmailDashboardRemove' => (qr/My dashboards/, "Testing!")); ($baseurl, $m) = RT::Test->started_ok; delete_dashboard($dashboard_id); -delete_subscriptions(); RT::Test->clean_caught_mails; @@ -330,7 +306,6 @@ RT->Config->Set('EmailDashboardRemove' => (qr/My dashboards/, "Testing!")); ($baseurl, $m) = RT::Test->started_ok; delete_dashboard($dashboard_id); -delete_subscriptions(); RT::Test->clean_caught_mails; @@ -373,7 +348,6 @@ RT->Config->Set('EmailDashboardRemove' => (qr/My dashboards/, "Testing!")); ($baseurl, $m) = RT::Test->started_ok; delete_dashboard($dashboard_id); -delete_subscriptions(); RT::Test->clean_caught_mails; diff --git a/rt/t/mail/gateway.t b/rt/t/mail/gateway.t index 9f0e669..98eabd5 100644 --- a/rt/t/mail/gateway.t +++ b/rt/t/mail/gateway.t @@ -57,7 +57,7 @@ use strict; use warnings; -use RT::Test config => 'Set( $UnsafeEmailCommands, 1);', tests => 221, actual_server => 1; +use RT::Test config => 'Set( $UnsafeEmailCommands, 1);', tests => 228, actual_server => 1; my ($baseurl, $m) = RT::Test->started_ok; use RT::Tickets; @@ -608,6 +608,35 @@ EOF $m->no_warnings_ok; } +diag "make sure we check that UTF-8 is really UTF-8"; +{ + my $text = <<EOF; +From: root\@localhost +To: rtemail\@@{[RT->Config->Get('rtname')]} +Subject: This is test wrong utf-8 chars +Content-Type: text/plain; charset="utf-8" + +utf-8: informaci\303\263n confidencial +latin1: informaci\363n confidencial + +bye +EOF + my ($status, $id) = RT::Test->send_via_mailgate_and_http($text); + is ($status >> 8, 0, "The mail gateway exited normally"); + ok ($id, "created ticket"); + + my $tick = RT::Test->last_ticket; + is ($tick->Id, $id, "correct ticket"); + + my $content = $tick->Transactions->First->Content; + Encode::_utf8_off($content); + + like $content, qr{informaci\303\263n confidencial}; + like $content, qr{informaci\357\277\275n confidencial}; + + $m->no_warnings_ok; +} + diag "check that mailgate doesn't suffer from empty Reply-To:"; { my $text = <<EOF; diff --git a/rt/t/shredder/01ticket.t b/rt/t/shredder/01ticket.t index 7dff16d..a7abeef 100644 --- a/rt/t/shredder/01ticket.t +++ b/rt/t/shredder/01ticket.t @@ -78,7 +78,11 @@ cmp_deeply( dump_current_and_savepoint('clean'), "current DB equal to savepoint" my $shredder = shredder_new(); $shredder->PutObjects( Objects => $child ); $shredder->WipeoutAll; - cmp_deeply( dump_current_and_savepoint('parent_ticket'), "current DB equal to savepoint"); + + TODO: { + local $TODO = "Shredder doesn't delete all links and transactions"; + cmp_deeply( dump_current_and_savepoint('parent_ticket'), "current DB equal to savepoint"); + } $shredder->PutObjects( Objects => $parent ); $shredder->WipeoutAll; diff --git a/rt/t/shredder/03plugin_tickets.t b/rt/t/shredder/03plugin_tickets.t index 092b570..e63eef8 100644 --- a/rt/t/shredder/03plugin_tickets.t +++ b/rt/t/shredder/03plugin_tickets.t @@ -34,6 +34,7 @@ use_ok('RT::Tickets'); my $child = RT::Ticket->new( RT->SystemUser ); my ($cid) = $child->Create( Subject => 'child', Queue => 1, MemberOf => $pid ); ok( $cid, "created new ticket" ); + $_->ApplyTransactionBatch for $parent, $child; my $plugin = RT::Shredder::Plugin::Tickets->new; isa_ok($plugin, 'RT::Shredder::Plugin::Tickets'); @@ -77,6 +78,8 @@ cmp_deeply( dump_current_and_savepoint('clean'), "current DB equal to savepoint" my ($status, $msg) = $child->AddLink( Target => $pid, Type => 'DependsOn' ); ok($status, "added reqursive link") or diag "error: $msg"; + $_->ApplyTransactionBatch for $parent, $child; + my $plugin = RT::Shredder::Plugin::Tickets->new; isa_ok($plugin, 'RT::Shredder::Plugin::Tickets'); @@ -121,6 +124,8 @@ cmp_deeply( dump_current_and_savepoint('clean'), "current DB equal to savepoint" ok( $cid2, "created new ticket" ); $child2->SetStatus('resolved'); + $_->ApplyTransactionBatch for $parent, $child1, $child2; + my $plugin = RT::Shredder::Plugin::Tickets->new; isa_ok($plugin, 'RT::Shredder::Plugin::Tickets'); diff --git a/rt/t/shredder/03plugin_users.t b/rt/t/shredder/03plugin_users.t index 4f4ecc8..1f4cb49 100644 --- a/rt/t/shredder/03plugin_users.t +++ b/rt/t/shredder/03plugin_users.t @@ -5,8 +5,8 @@ use warnings; use Test::Deep; use File::Spec; -use Test::More tests => 9; -use RT::Test nodb => 1; +use Test::More tests => 21; +use RT::Test (); BEGIN { my $shredder_utils = RT::Test::get_relocatable_file('utils.pl', File::Spec->curdir()); @@ -38,3 +38,61 @@ use_ok('RT::Shredder::Plugin::Users'); ok(!$status, "bad 'status' arg value"); } +init_db(); + +RT::Test->set_rights( + { Principal => 'Everyone', Right => [qw(CreateTicket)] }, +); + +create_savepoint('clean'); + +{ # Create two users and a ticket. Shred second user and replace relations with first user + my ($uidA, $uidB, $msg); + my $userA = RT::User->new( RT->SystemUser ); + ($uidA, $msg) = $userA->Create( Name => 'userA', Privileged => 1, Disabled => 0 ); + ok( $uidA, "created user A" ) or diag "error: $msg"; + + my $userB = RT::User->new( RT->SystemUser ); + ($uidB, $msg) = $userB->Create( Name => 'userB', Privileged => 1, Disabled => 0 ); + ok( $uidB, "created user B" ) or diag "error: $msg"; + + my ($tid, $trid); + my $ticket = RT::Ticket->new( RT::CurrentUser->new($userB) ); + ($tid, $trid, $msg) = $ticket->Create( Subject => 'UserB Ticket', Queue => 1 ); + ok( $tid, "created new ticket") or diag "error: $msg"; + + my $transaction = RT::Transaction->new( RT->SystemUser ); + $transaction->Load($trid); + is ( $transaction->Creator, $uidB, "ticket creator is user B" ); + + my $plugin = RT::Shredder::Plugin::Users->new; + isa_ok($plugin, 'RT::Shredder::Plugin::Users'); + + my $status; + ($status, $msg) = $plugin->TestArgs( status => 'any', name => 'userB', replace_relations => $uidA ); + ok($status, "plugin arguments are ok") or diag "error: $msg"; + + my @objs; + ($status, @objs) = $plugin->Run; + ok($status, "executed plugin successfully") or diag "error: @objs"; + @objs = RT::Shredder->CastObjectsToRecords( Objects => \@objs ); + is(scalar @objs, 1, "one object in the result set"); + + my $shredder = shredder_new(); + + ($status, $msg) = $plugin->SetResolvers( Shredder => $shredder ); + ok($status, "set conflicts resolver") or diag "error: $msg"; + + $shredder->PutObjects( Objects => \@objs ); + $shredder->WipeoutAll; + + $ticket->Load( $tid ); + is($ticket->id, $tid, 'loaded ticket'); + + $transaction->Load($trid); + is ( $transaction->Creator, $uidA, "ticket creator is now user A" ); + + $shredder->Wipeout( Object => $ticket ); + $shredder->Wipeout( Object => $userA ); +} +cmp_deeply( dump_current_and_savepoint('clean'), "current DB equal to savepoint"); diff --git a/rt/t/shredder/utils.pl b/rt/t/shredder/utils.pl index 5f5c182..9b848c6 100644 --- a/rt/t/shredder/utils.pl +++ b/rt/t/shredder/utils.pl @@ -283,7 +283,7 @@ sub dump_sqlite my $old_fhkn = $dbh->{'FetchHashKeyName'}; $dbh->{'FetchHashKeyName'} = 'NAME_lc'; - my $sth = $dbh->table_info( '', '', '%', 'TABLE' ) || die $DBI::err; + my $sth = $dbh->table_info( '', '%', '%', 'TABLE' ) || die $DBI::err; my @tables = keys %{$sth->fetchall_hashref( 'table_name' )}; my $res = {}; diff --git a/rt/t/ticket/search_by_watcher.t b/rt/t/ticket/search_by_watcher.t index 809450b..cfc7b1c 100644 --- a/rt/t/ticket/search_by_watcher.t +++ b/rt/t/ticket/search_by_watcher.t @@ -142,8 +142,8 @@ sub run_auto_tests { @conditions = ( 'Cc = "not@exist"' => sub { 0 }, 'Cc != "not@exist"' => sub { 1 }, - 'Cc IS NULL' => sub { $_[0] =~ 'c:-;' }, - 'Cc IS NOT NULL' => sub { $_[0] !~ 'c:-;' }, + 'Cc IS NULL' => sub { $_[0] =~ /c:-;/ }, + 'Cc IS NOT NULL' => sub { $_[0] !~ /c:-;/ }, 'Cc = "x@foo.com"' => sub { $_[0] =~ /c:[^;]*x/ }, 'Cc != "x@foo.com"' => sub { $_[0] !~ /c:[^;]*x/ }, 'Cc LIKE "@bar.com"' => sub { $_[0] =~ /c:[^;]*(?:y|z)/ }, @@ -152,8 +152,8 @@ sub run_auto_tests { 'Requestor = "not@exist"' => sub { 0 }, 'Requestor != "not@exist"' => sub { 1 }, - 'Requestor IS NULL' => sub { $_[0] =~ 'r:-;' }, - 'Requestor IS NOT NULL' => sub { $_[0] !~ 'r:-;' }, + 'Requestor IS NULL' => sub { $_[0] =~ /r:-;/ }, + 'Requestor IS NOT NULL' => sub { $_[0] !~ /r:-;/ }, 'Requestor = "x@foo.com"' => sub { $_[0] =~ /r:[^;]*x/ }, 'Requestor != "x@foo.com"' => sub { $_[0] !~ /r:[^;]*x/ }, 'Requestor LIKE "@bar.com"' => sub { $_[0] =~ /r:[^;]*(?:y|z)/ }, @@ -174,7 +174,7 @@ sub run_auto_tests { 'Subject LIKE "ne"' => sub { 0 }, 'Subject NOT LIKE "ne"' => sub { 1 }, 'Subject = "r:x;c:y;"' => sub { $_[0] eq 'r:x;c:y;' }, - 'Subject LIKE "x"' => sub { $_[0] =~ 'x' }, + 'Subject LIKE "x"' => sub { $_[0] =~ /x/ }, ); @tickets = generate_tix(); diff --git a/rt/t/web/attachments.t b/rt/t/web/attachments.t index 8c75f6c..784cbbe 100644 --- a/rt/t/web/attachments.t +++ b/rt/t/web/attachments.t @@ -1,10 +1,11 @@ #!/usr/bin/perl -w use strict; -use RT::Test tests => 25; +use RT::Test tests => 33; use constant LogoFile => $RT::MasonComponentRoot .'/NoAuth/images/bpslogo.png'; use constant FaviconFile => $RT::MasonComponentRoot .'/NoAuth/images/favicon.png'; +use constant TextFile => $RT::MasonComponentRoot .'/NoAuth/css/print.css'; my ($baseurl, $m) = RT::Test->started_ok; ok $m->login, 'logged in'; @@ -30,9 +31,18 @@ $m->content_contains('Attachments test', 'we have subject on the page'); $m->content_contains('Some content', 'and content'); $m->content_contains('Download bpslogo.png', 'page has file name'); +open LOGO, "<", LogoFile or die "Can't open logo file: $!"; +binmode LOGO; +my $logo_contents = do {local $/; <LOGO>}; +close LOGO; +$m->follow_link_ok({text => "Download bpslogo.png"}); +is($m->content_type, "image/png"); +is($m->content, $logo_contents, "Binary content matches"); + +$m->back; $m->follow_link_ok({text => 'Reply'}, "reply to the ticket"); $m->form_name('TicketUpdate'); -$m->field('Attach', LogoFile); +$m->field('Attach', TextFile); $m->click('AddMoreAttach'); is($m->status, 200, "request successful"); @@ -44,7 +54,16 @@ is($m->status, 200, "request successful"); $m->content_contains('Download bpslogo.png', 'page has file name'); $m->content_contains('Download favicon.png', 'page has file name'); +$m->content_contains('Download print.css', 'page has file name'); + +$m->follow_link_ok( { text => 'Download bpslogo.png' } ); +is( $m->response->header('Content-Type'), 'image/png', 'Content-Type of png lacks charset' ); + +$m->back; +$m->follow_link_ok( { text => 'Download print.css' } ); +is( $m->response->header('Content-Type'), + 'text/css;charset=UTF-8', 'Content-Type of text has charset' ); diag "test mobile ui"; $m->get_ok( $baseurl . '/m/ticket/create?Queue=' . $qid ); diff --git a/rt/t/web/command_line.t b/rt/t/web/command_line.t index 1fed8e6..394daab 100644 --- a/rt/t/web/command_line.t +++ b/rt/t/web/command_line.t @@ -3,7 +3,7 @@ use strict; use File::Spec (); use Test::Expect; -use RT::Test tests => 303, actual_server => 1; +use RT::Test tests => 315, actual_server => 1; my ($baseurl, $m) = RT::Test->started_ok; use RT::User; @@ -480,6 +480,8 @@ expect_like(qr/Merged into ticket #$merge_ticket_A by root/, 'Merge recorded in expect_like(qr/Created link $link1_id $reln $link2_id/, 'Linked'); expect_send("show -s ticket/$link1_id/links", "Checking creation of $reln..."); expect_like(qr/$display_relns{$reln}: [\w\d\.\-]+:\/\/[\w\d\.]+\/ticket\/$link2_id/, "Created link $reln"); + expect_send("show ticket/$link1_id/links", "Checking show links without format"); + expect_like(qr/$display_relns{$reln}: [\w\d\.\-]+:\/\/[\w\d\.]+\/ticket\/$link2_id/, "Found link $reln"); # delete link expect_send("link -d $link1_id $reln $link2_id", "Delete $reln..."); diff --git a/rt/t/web/command_line_with_unknown_field.t b/rt/t/web/command_line_with_unknown_field.t index 736be4d..d63956b 100644 --- a/rt/t/web/command_line_with_unknown_field.t +++ b/rt/t/web/command_line_with_unknown_field.t @@ -3,7 +3,7 @@ use strict; use File::Spec (); use Test::Expect; -use RT::Test tests => 14, actual_server => 1; +use RT::Test tests => 17, actual_server => 1; my ($baseurl, $m) = RT::Test->started_ok; my $rt_tool_path = "$RT::BinPath/rt"; @@ -19,6 +19,11 @@ expect_run( prompt => 'rt> ', quit => 'quit', ); + +expect_send( q{create -t ticket set foo=bar}, "create ticket with unknown field" ); +expect_like(qr/foo: Unknown field/, 'foo is unknown field'); +expect_like(qr/Could not create ticket/, 'ticket is not created'); + expect_send(q{create -t ticket set subject='new ticket' add cc=foo@example.com}, "Creating a ticket..."); expect_like(qr/Ticket \d+ created/, "Created the ticket"); diff --git a/rt/t/web/crypt-gnupg.t b/rt/t/web/crypt-gnupg.t index 6bdefda..8c0eb57 100644 --- a/rt/t/web/crypt-gnupg.t +++ b/rt/t/web/crypt-gnupg.t @@ -53,6 +53,7 @@ RT::Test->clean_caught_mails; $m->goto_create_ticket( $queue ); $m->form_name('TicketCreate'); +$m->field('Requestors', 'recipient@example.com'); $m->field('Subject', 'Encryption test'); $m->field('Content', 'Some content'); ok($m->value('Encrypt', 2), "encrypt tick box is checked"); @@ -122,6 +123,7 @@ RT::Test->clean_caught_mails; $m->goto_create_ticket( $queue ); $m->form_name('TicketCreate'); +$m->field('Requestors', 'recipient@example.com'); $m->field('Subject', 'Signing test'); $m->field('Content', 'Some other content'); ok(!$m->value('Encrypt', 2), "encrypt tick box is unchecked"); @@ -195,6 +197,7 @@ RT::Test->clean_caught_mails; $m->goto_create_ticket( $queue ); $m->form_name('TicketCreate'); +$m->field('Requestors', 'recipient@example.com'); $m->field('Subject', 'Crypt+Sign test'); $m->field('Content', 'Some final? content'); ok($m->value('Encrypt', 2), "encrypt tick box is checked"); @@ -260,6 +263,7 @@ RT::Test->clean_caught_mails; $m->goto_create_ticket( $queue ); $m->form_name('TicketCreate'); +$m->field('Requestors', 'recipient@example.com'); $m->field('Subject', 'Test crypt-off on encrypted queue'); $m->field('Content', 'Thought you had me figured out didya'); $m->field(Encrypt => undef, 2); # turn off encryption diff --git a/rt/t/web/googleish_search.t b/rt/t/web/googleish_search.t index e2a4e91..f4c8fa4 100644 --- a/rt/t/web/googleish_search.t +++ b/rt/t/web/googleish_search.t @@ -2,7 +2,8 @@ use strict; use warnings; -use RT::Test tests => 96, config => 'Set( %FullTextSearch, Enable => 1, Indexed => 0 );'; +use RT::Test tests => undef, + config => 'Set( %FullTextSearch, Enable => 1, Indexed => 0 );'; my ($baseurl, $m) = RT::Test->started_ok; my $url = $m->rt_base_url; @@ -57,6 +58,7 @@ ok $two_words_queue && $two_words_queue->id, 'loaded or created a queue'; is $parser->QueryToSQL("'me'"), "$active AND ( Subject LIKE 'me' )", "correct parsing"; is $parser->QueryToSQL("owner:me"), "( Owner.id = '__CurrentUser__' ) AND $active", "correct parsing"; is $parser->QueryToSQL("owner:'me'"), "( Owner = 'me' ) AND $active", "correct parsing"; + is $parser->QueryToSQL('owner:root@localhost'), "( Owner.EmailAddress = 'root\@localhost' ) AND $active", "Email address as owner"; is $parser->QueryToSQL("resolved me"), "( Owner.id = '__CurrentUser__' ) AND ( Status = 'resolved' )", "correct parsing"; is $parser->QueryToSQL("resolved active me"), "( Owner.id = '__CurrentUser__' ) AND ( Status = 'resolved' OR Status = 'new' OR Status = 'open' OR Status = 'stalled' )", "correct parsing"; @@ -217,3 +219,5 @@ for my $quote ( q{'}, q{"} ) { } } +undef $m; +done_testing; diff --git a/rt/t/web/query_builder_queue_limits.t b/rt/t/web/query_builder_queue_limits.t index a3b9765..f583d64 100644 --- a/rt/t/web/query_builder_queue_limits.t +++ b/rt/t/web/query_builder_queue_limits.t @@ -11,6 +11,9 @@ $lifecycles->{foo} = { }; +# explicitly Set so RT::Test can catch our change +RT->Config->Set( Lifecycles => %$lifecycles ); + RT::Lifecycle->FillCache(); my $general = RT::Test->load_or_create_queue( Name => 'General' ); diff --git a/rt/t/web/search_simple.t b/rt/t/web/search_simple.t index 1efc9a5..a1a3ce8 100644 --- a/rt/t/web/search_simple.t +++ b/rt/t/web/search_simple.t @@ -1,7 +1,7 @@ use strict; use warnings; -use RT::Test tests => 16; +use RT::Test tests => 30; my ( $baseurl, $m ) = RT::Test->started_ok; RT::Test->create_tickets( @@ -19,4 +19,58 @@ $m->content_contains( 'Show Results', "has page menu" ); $m->title_is( 'Found 1 ticket', 'title' ); $m->content_contains( 'ticket foo', 'has ticket foo' ); +# Test searches on custom fields +my $cf1 = RT::Test->load_or_create_custom_field( + Name => 'Location', + Queue => 'General', + Type => 'FreeformSingle', ); +isa_ok( $cf1, 'RT::CustomField' ); + +my $cf2 = RT::Test->load_or_create_custom_field( + Name => 'Server-name', + Queue => 'General', + Type => 'FreeformSingle', ); +isa_ok( $cf2, 'RT::CustomField' ); + +my $t = RT::Ticket->new(RT->SystemUser); + +{ + my ($id,undef,$msg) = $t->Create( + Queue => 'General', + Subject => 'Test searching CFs'); + ok( $id, "Created ticket - $msg" ); +} + +{ + my ($status, $msg) = $t->AddCustomFieldValue( + Field => $cf1->id, + Value => 'Downtown'); + ok( $status, "Added CF value - $msg" ); +} + +{ + my ($status, $msg) = $t->AddCustomFieldValue( + Field => $cf2->id, + Value => 'Proxy'); + ok( $status, "Added CF value - $msg" ); +} + +# Regular search +my $search = 'cf.Location:Downtown'; +$m->get_ok("/Search/Simple.html?q=$search"); +$m->title_is( 'Found 1 ticket', 'Found 1 ticket' ); +$m->text_contains( 'Test searching CFs', "Found test CF ticket with $search" ); + +# Case insensitive +$search = "cf.Location:downtown"; +$m->get_ok("/Search/Simple.html?q=$search"); +$m->title_is( 'Found 1 ticket', 'Found 1 ticket' ); +$m->text_contains( 'Test searching CFs', "Found test CF ticket with $search" ); + +# With dash in CF name +$search = "cf.Server-name:Proxy"; +$m->get_ok("/Search/Simple.html?q=$search"); +$m->title_is( 'Found 1 ticket', 'Found 1 ticket' ); +$m->text_contains( 'Test searching CFs', "Found test CF ticket with $search" ); + # TODO more simple search tests diff --git a/rt/t/web/ticket_modify_all.t b/rt/t/web/ticket_modify_all.t index c9dd7e7..2f0c4d1 100644 --- a/rt/t/web/ticket_modify_all.t +++ b/rt/t/web/ticket_modify_all.t @@ -1,7 +1,7 @@ use strict; use warnings; -use RT::Test tests => 15; +use RT::Test tests => 22; my $ticket = RT::Test->create_ticket( Subject => 'test bulk update', @@ -40,5 +40,44 @@ $m->click('SubmitTicket'); $m->form_name('TicketModifyAll'); is($m->value('Owner'), 'root', 'owner was successfully changed to root'); -# XXX TODO test other parts, i.e. basic, dates, people and links +$m->get_ok($url . "/Ticket/ModifyAll.html?id=" . $ticket->id); +$m->form_name('TicketModifyAll'); +$m->field('Starts_Date' => "2013-01-01 00:00:00"); +$m->click('SubmitTicket'); +$m->text_contains("Starts: (Tue Jan 01 00:00:00 2013)", 'start date successfully updated'); + +$m->form_name('TicketModifyAll'); +$m->field('Started_Date' => "2014-01-01 00:00:00"); +$m->click('SubmitTicket'); +$m->text_contains("Started: (Wed Jan 01 00:00:00 2014)", 'started date successfully updated'); + +$m->form_name('TicketModifyAll'); +$m->field('Told_Date' => "2015-01-01 00:00:00"); +$m->click('SubmitTicket'); +$m->text_contains("Last Contact: (Thu Jan 01 00:00:00 2015)", 'told date successfully updated'); + +$m->form_name('TicketModifyAll'); +$m->field('Due_Date' => "2016-01-01 00:00:00"); +$m->click('SubmitTicket'); +$m->text_contains("Due: (Fri Jan 01 00:00:00 2016)", 'due date successfully updated'); + +$m->get( $url . '/Ticket/ModifyAll.html?id=' . $ticket->id ); +$m->form_name('TicketModifyAll'); +$m->field(WatcherTypeEmail => 'Requestor'); +$m->field(WatcherAddressEmail => 'root@localhost'); +$m->click('SubmitTicket'); +$m->text_contains( + "Added principal as a Requestor for this ticket", + 'watcher is added', +); +$m->form_name('TicketModifyAll'); +$m->field(WatcherTypeEmail => 'Requestor'); +$m->field(WatcherAddressEmail => 'root@localhost'); +$m->click('SubmitTicket'); +$m->text_contains( + "That principal is already a Requestor for this ticket", + 'no duplicate watchers', +); + +# XXX TODO test other parts, i.e. links diff --git a/rt/t/web/transaction_batch.t b/rt/t/web/transaction_batch.t index ae04e1f..12d01fb 100644 --- a/rt/t/web/transaction_batch.t +++ b/rt/t/web/transaction_batch.t @@ -12,7 +12,14 @@ my ($val, $msg) =$s1->Create( Queue => $q->Id, ScripAction => 'User Defined', CustomIsApplicableCode => 'return ($self->TransactionObj->Field||"") eq "TimeEstimated"', CustomPrepareCode => 'return 1', - CustomCommitCode => '$self->TicketObj->SetPriority($self->TicketObj->Priority + 2); return 1;', + CustomCommitCode => ' +if ( $self->TicketObj->CurrentUser->Name ne "RT_System" ) { + warn "Ticket obj has incorrect CurrentUser (should be RT_System) ".$self->TicketObj->CurrentUser->Name +} +if ( $self->TicketObj->QueueObj->CurrentUser->Name ne "RT_System" ) { + warn "Queue obj has incorrect CurrentUser (should be RT_System) ".$self->TicketObj->QueueObj->CurrentUser->Name +} +$self->TicketObj->SetPriority($self->TicketObj->Priority + 2); return 1;', Template => 'Blank', Stage => 'TransactionBatch', ); |