@lines;
}
-=item spool_formats
-
-Returns a list of the invoice spool formats.
-
-=cut
-
-sub spool_formats {
- qw(default oneline billco bridgestone)
-}
-
=back
=head1 BUGS
# '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',
'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'] ],
#'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' => [],
#'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' => [],
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;
#set up payment gateways if needed
'pay_batch' => [],
+
+ #flag monthly tax exemptions
+ 'cust_tax_exempt_pkg' => [],
;
\%hash;
# 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
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;
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;
use FS::cust_bill_pkg_tax_rate_location_void;
use FS::cust_tax_exempt_pkg_void;
-
$DEBUG = 0;
$me = '[FS::cust_bill_pkg]';
}
}
- 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";
}
}
}
-# 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
}
+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
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
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(
{
'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;
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 ) {
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} };
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'};
$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 } ||= [];
#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;
}
}
#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 } };
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' => '',
push @tax_line_items, new FS::cust_bill_pkg {
'pkgnum' => 0,
- 'setup' => $tax,
+ 'setup' => $tax_total,
'recur' => 0,
'sdate' => '',
'edate' => '',
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)
)
{
- 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;
} 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 ];
} #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
# 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;
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;
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
=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';
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
AND taxnum = ?
AND year = ?
AND month = ?
+ AND exempt_monthly = 'Y'
";
my $sth = dbh->prepare($sql) or do {
$dbh->rollback if $oldAutoCommit;
$sth->execute(
$custnum,
$self->taxnum,
- 1900+$year,
+ $year,
$mon,
) or do {
$dbh->rollback if $oldAutoCommit;
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;
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,
'amount' => $self->amount,
'percent' => $self->percent,
'months' => $self->months,
- 'setup' => $self->setup,
+ 'setup' => $self->setup,
+ #'linked' => $self->linked,
'disabled' => 'Y',
};
my $error = $discount->insert;
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 );
=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
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
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
$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;
|| $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;
--- /dev/null
+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;
+
--- /dev/null
+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;
+
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');
'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"
--- /dev/null
+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;
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'})
$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'};
'';
}
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
|| $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;
=item process
-Experimental job-queue processor for web interface adds/edits
+Job-queue processor for web interface adds/edits
=cut
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
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) )"
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";
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";
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
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,
--- /dev/null
+#!/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;
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
'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',
'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>
<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>
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 #',
'percent' => 'Percentage ',
'months' => 'Duration (months)',
'setup' => 'Apply to setup fees',
+ #'linked' => 'Apply to add-on packages',
},
'viewall_dir' => 'browse',
'new_callback' => $new_callback,
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 = '';
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';
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 = '';
}
}
'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;
% $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
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+)$/
}
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>
<& 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"
'description' => 'Description',
'attrnum' => 'Attribute',
'priority' => 'Priority',
+ 'speed_down' => 'Download speed',
+ 'speed_up' => 'Upload speed',
},
'viewall_dir' => 'browse',
'menubar' => \@menubar,
'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',
% } 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,
)
%>
+%# <% 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';
<% $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 = '';
<% $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';
<% $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 = '';
}
}
'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(),
},
#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 : '' }
),
],
#'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(),
'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",
"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',
'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>
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 ) {
}
if ( $cgi->param('out') ) {
+ # wtf? how would you ever get exemptions on a non-taxable package location?
push @where, "
0 = (
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) : '';
%
% ) {
%
-% 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;
% 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 ) {
% $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';
% }
% }
<<%$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>
<<%$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>
<<%$tdh%> ALIGN="right">
<% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %>
</TD>
-% }
+% }
+% } # not $out
</TR>
% }
<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>
<% &$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+)$/ ) {
$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
}
};
+# 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;
#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;
#--
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>
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' );
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' );
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',
.
$deps{'FASTCGI'} = [ text_to_hash( << '.') ];
-FCGI
+FCGI 0.74
FCGI::ProcManager
.
$deps{'GRAPHVIZ'} = [ text_to_hash( << '.') ];
GraphViz
-IPC::Run
+IPC::Run 0.90
.
$deps{'GD'} = [ text_to_hash( << '.') ];
my %AVOID = (
'DBD::Oracle' => [qw(1.23)],
+ 'Email::Address' => [qw(1.893 1.894)],
);
if ($args{'download'}) {
$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 = @_;
<%INIT>
use Spreadsheet::WriteExcel;
+use OLE::Storage_Lite;
use List::Util qw( max );
use Date::Format qw( time2str );
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(
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');
+++ /dev/null
-#!/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");
+++ /dev/null
-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',
- );
-}
-
-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";
}
-
use strict;
use warnings;
-use RT::Test tests => 23;
+use RT::Test tests => 44;
use RT::CustomField;
use RT::Queue;
'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.
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;
+}
+
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);
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";
+}
</IfModule>
</IfModule>
+ServerName localhost
Listen %%LISTEN%%
ErrorLog "%%LOG_FILE%%"
</IfModule>
</IfModule>
+ServerName localhost
Listen %%LISTEN%%
ErrorLog "%%LOG_FILE%%"
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' );
use strict;
use warnings;
-use RT::Test tests => 187;
+use RT::Test tests => 181;
use Test::Warn;
use RT::Dashboard::Mailer;
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
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;
($baseurl, $m) = RT::Test->started_ok;
delete_dashboard($dashboard_id);
-delete_subscriptions();
RT::Test->clean_caught_mails;
($baseurl, $m) = RT::Test->started_ok;
delete_dashboard($dashboard_id);
-delete_subscriptions();
RT::Test->clean_caught_mails;
($baseurl, $m) = RT::Test->started_ok;
delete_dashboard($dashboard_id);
-delete_subscriptions();
RT::Test->clean_caught_mails;
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;
$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;
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;
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');
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');
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');
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());
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");
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 = {};
@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)/ },
'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)/ },
'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();
#!/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';
$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");
$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 );
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;
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...");
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";
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");
$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");
$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");
$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");
$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
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;
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";
}
}
+undef $m;
+done_testing;
};
+# 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' );
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(
$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
use strict;
use warnings;
-use RT::Test tests => 15;
+use RT::Test tests => 22;
my $ticket = RT::Test->create_ticket(
Subject => 'test bulk update',
$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
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',
);
projects / freeside.git / commitdiff