use Text::CSV_XS;
use FS::Record qw( qsearch qsearchs dbh );
use FS::cust_pkg;
-use FS::cust_bill;
use FS::cust_bill_pkg_detail;
use FS::cust_bill_pkg_display;
use FS::cust_bill_pkg_discount;
+use FS::cust_bill_pkg_fee;
use FS::cust_bill_pay_pkg;
use FS::cust_credit_bill_pkg;
use FS::cust_tax_exempt_pkg;
use FS::cust_bill_pkg_tax_location_void;
use FS::cust_bill_pkg_tax_rate_location_void;
use FS::cust_tax_exempt_pkg_void;
+use FS::cust_bill_pkg_fee_void;
+use FS::reason;
+use FS::reason_type;
+
+use FS::Cursor;
$DEBUG = 0;
$me = '[FS::cust_bill_pkg]';
=head1 DESCRIPTION
An FS::cust_bill_pkg object represents an invoice line item.
-FS::cust_bill_pkg inherits from FS::Record. The following fields are currently
-supported:
+FS::cust_bill_pkg inherits from FS::Record. The following fields are
+currently supported:
=over 4
}
}
- my $tax_location = $self->get('cust_bill_pkg_tax_location');
- if ( $tax_location ) {
+ foreach my $tax_link_table (qw(cust_bill_pkg_tax_location
+ cust_bill_pkg_tax_rate_location))
+ {
+ my $tax_location = $self->get($tax_link_table) || [];
foreach my $link ( @$tax_location ) {
- next if $link->billpkgtaxlocationnum; # don't try to double-insert
+ my $pkey = $link->primary_key;
+ next if $link->get($pkey); # don't try to double-insert
# This cust_bill_pkg can be linked on either side (i.e. it can be the
# tax or the taxed item). If the other side is already inserted,
# then set billpkgnum to ours, and insert the link. Otherwise,
my $taxable_cust_bill_pkg = $link->get('taxable_cust_bill_pkg');
if ( $taxable_cust_bill_pkg && $taxable_cust_bill_pkg->billpkgnum ) {
$link->set('taxable_billpkgnum', $taxable_cust_bill_pkg->billpkgnum);
- # XXX if we ever do tax-on-tax for these, this will have to change
- # since pkgnum will be zero
+ # XXX pkgnum is zero for tax on tax; it might be better to use
+ # the underlying package?
$link->set('pkgnum', $taxable_cust_bill_pkg->pkgnum);
- $link->set('locationnum',
- $taxable_cust_bill_pkg->cust_pkg->tax_locationnum);
+ $link->set('locationnum', $taxable_cust_bill_pkg->tax_locationnum);
$link->set('taxable_cust_bill_pkg', '');
}
return "error inserting cust_bill_pkg_tax_location: $error";
}
} else { # handoff
- my $other;
+ my $other; # the as yet uninserted cust_bill_pkg
$other = $link->billpkgnum ? $link->get('taxable_cust_bill_pkg')
: $link->get('tax_cust_bill_pkg');
- my $link_array = $other->get('cust_bill_pkg_tax_location') || [];
+ my $link_array = $other->get( $tax_link_table ) || [];
push @$link_array, $link;
- $other->set('cust_bill_pkg_tax_location' => $link_array);
+ $other->set( $tax_link_table => $link_array);
}
} #foreach my $link
}
# someday you will be as awesome as cust_bill_pkg_tax_location...
- # but not today
- my $tax_rate_location = $self->get('cust_bill_pkg_tax_rate_location');
- if ( $tax_rate_location ) {
- foreach my $cust_bill_pkg_tax_rate_location ( @$tax_rate_location ) {
- $cust_bill_pkg_tax_rate_location->billpkgnum($self->billpkgnum);
- $error = $cust_bill_pkg_tax_rate_location->insert;
+ # and today is that day
+ #my $tax_rate_location = $self->get('cust_bill_pkg_tax_rate_location');
+ #if ( $tax_rate_location ) {
+ # foreach my $cust_bill_pkg_tax_rate_location ( @$tax_rate_location ) {
+ # $cust_bill_pkg_tax_rate_location->billpkgnum($self->billpkgnum);
+ # $error = $cust_bill_pkg_tax_rate_location->insert;
+ # if ( $error ) {
+ # $dbh->rollback if $oldAutoCommit;
+ # return "error inserting cust_bill_pkg_tax_rate_location: $error";
+ # }
+ # }
+ #}
+
+ my $fee_links = $self->get('cust_bill_pkg_fee');
+ if ( $fee_links ) {
+ foreach my $link ( @$fee_links ) {
+ # very similar to cust_bill_pkg_tax_location, for obvious reasons
+ next if $link->billpkgfeenum; # don't try to double-insert
+
+ my $target = $link->get('cust_bill_pkg'); # the line item of the fee
+ my $base = $link->get('base_cust_bill_pkg'); # line item it was based on
+
+ if ( $target and $target->billpkgnum ) {
+ $link->set('billpkgnum', $target->billpkgnum);
+ # base_invnum => null indicates that the fee is based on its own
+ # invoice
+ $link->set('base_invnum', $target->invnum) unless $link->base_invnum;
+ $link->set('cust_bill_pkg', '');
+ }
+
+ if ( $base and $base->billpkgnum ) {
+ $link->set('base_billpkgnum', $base->billpkgnum);
+ $link->set('base_cust_bill_pkg', '');
+ } elsif ( $base ) {
+ # it's based on a line item that's not yet inserted
+ my $link_array = $base->get('cust_bill_pkg_fee') || [];
+ push @$link_array, $link;
+ $base->set('cust_bill_pkg_fee' => $link_array);
+ next; # don't insert the link yet
+ }
+
+ $error = $link->insert;
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
- return "error inserting cust_bill_pkg_tax_rate_location: $error";
+ return "error inserting cust_bill_pkg_fee: $error";
}
+ } # foreach my $link
+ }
+
+ if ( my $fee_origin = $self->get('fee_origin') ) {
+ $fee_origin->set('billpkgnum' => $self->billpkgnum);
+ $error = $fee_origin->replace;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "error updating fee origin record: $error";
}
}
}
-=item void
+=item void [ REASON [ , REPROCESS_CDRS ] ]
Voids this line item: deletes the line item and adds a record of the voided
line item to the FS::cust_bill_pkg_void table (and related tables).
sub void {
my $self = shift;
my $reason = scalar(@_) ? shift : '';
+ my $reprocess_cdrs = scalar(@_) ? shift : '';
+
+ unless (ref($reason) || !$reason) {
+ $reason = FS::reason->new_or_existing(
+ 'class' => 'I',
+ 'type' => 'Invoice void',
+ 'reason' => $reason
+ );
+ }
local $SIG{HUP} = 'IGNORE';
local $SIG{INT} = 'IGNORE';
my $cust_bill_pkg_void = new FS::cust_bill_pkg_void ( {
map { $_ => $self->get($_) } $self->fields
} );
- $cust_bill_pkg_void->reason($reason);
+ $cust_bill_pkg_void->reasonnum($reason->reasonnum) if $reason;
my $error = $cust_bill_pkg_void->insert;
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
return $error;
}
+ #more efficiently than below, because there could be lots
+ $self->void_cust_bill_pkg_detail($reprocess_cdrs);
+
foreach my $table (qw(
- cust_bill_pkg_detail
cust_bill_pkg_display
cust_bill_pkg_discount
cust_bill_pkg_tax_location
cust_bill_pkg_tax_rate_location
cust_tax_exempt_pkg
+ cust_bill_pkg_fee
)) {
-
foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
my $vclass = 'FS::'.$table.'_void';
}
+sub void_cust_bill_pkg_detail {
+ my( $self, $reprocess_cdrs ) = @_;
+
+ my $from_cust_bill_pkg_detail =
+ 'FROM cust_bill_pkg_detail WHERE billpkgnum = ?';
+ my $where_detailnum =
+ "WHERE detailnum IN ( SELECT detailnum $from_cust_bill_pkg_detail )";
+
+ if ( $reprocess_cdrs ) {
+ #well, technically this could have been on other invoices / termination
+ # partners... separate flag?
+ $self->scalar_sql(
+ "DELETE FROM cdr_termination
+ WHERE acctid IN ( SELECT acctid FROM cdr $where_detailnum )
+ ",
+ $self->billpkgnum
+ );
+ }
+
+ my $setstatus = $reprocess_cdrs ? ', freesidestatus = NULL' : '';
+ $self->scalar_sql(
+ "UPDATE cdr SET detailnum = NULL $setstatus $where_detailnum",
+ $self->billpkgnum
+ );
+
+ $self->scalar_sql("INSERT INTO cust_bill_pkg_detail_void
+ SELECT * $from_cust_bill_pkg_detail",
+ $self->billpkgnum
+ );
+
+ $self->scalar_sql("DELETE $from_cust_bill_pkg_detail", $self->billpkgnum);
+
+}
+
=item delete
Not recommended.
cust_tax_exempt_pkg
cust_bill_pay_pkg
cust_credit_bill_pkg
+ cust_bill_pkg_fee
)) {
foreach my $linked ( qsearch($table, { billpkgnum=>$self->billpkgnum }) ) {
}
}
+ #fix the invoice amount
+
+ my $cust_bill = $self->cust_bill;
+ $cust_bill->charged( $cust_bill->charged - $self->setup - $self->recur );
+
+ #not adding a cc surcharge, but this override lets us modify charged
+ $cust_bill->{'Hash'}{'cc_surcharge_replace_hack'} = 1;
+
+ my $error = $cust_bill->replace;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+
my $error = $self->SUPER::delete(@_);
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
|| $self->ut_snumber('pkgnum')
|| $self->ut_number('invnum')
|| $self->ut_money('setup')
+ || $self->ut_moneyn('unitsetup')
+ || $self->ut_currencyn('setup_billed_currency')
+ || $self->ut_moneyn('setup_billed_amount')
|| $self->ut_money('recur')
+ || $self->ut_moneyn('unitrecur')
+ || $self->ut_currencyn('recur_billed_currency')
+ || $self->ut_moneyn('recur_billed_amount')
|| $self->ut_numbern('sdate')
|| $self->ut_numbern('edate')
|| $self->ut_textn('itemdesc')
return;
}
+=item set_exemptions TAXOBJECT, OPTIONS
+
+Sets up tax exemptions. TAXOBJECT is the L<FS::cust_main_county> or
+L<FS::tax_rate> record for the tax.
+
+This will deal with the following cases:
+
+=over 4
+
+=item Fully exempt customers (cust_main.tax flag) or customer classes
+(cust_class.tax).
+
+=item Customers exempt from specific named taxes (cust_main_exemption
+records).
+
+=item Taxes that don't apply to setup or recurring fees
+(cust_main_county.setuptax and recurtax, tax_rate.setuptax and recurtax).
+
+=item Packages that are marked as tax-exempt (part_pkg.setuptax,
+part_pkg.recurtax).
+
+=item Fees that aren't marked as taxable (part_fee.taxable).
+
+=back
+
+It does NOT deal with monthly tax exemptions, which need more context
+than this humble little method cares to deal with.
+
+OPTIONS should include "custnum" => the customer number if this tax line
+hasn't been inserted (which it probably hasn't).
+
+Returns a list of exemption objects, which will also be attached to the
+line item as the 'cust_tax_exempt_pkg' pseudo-field. Inserting the line
+item will insert these records as well.
+
+=cut
+
+sub set_exemptions {
+ my $self = shift;
+ my $tax = shift;
+ my %opt = @_;
+
+ my $part_pkg = $self->part_pkg;
+ my $part_fee = $self->part_fee;
+
+ my $cust_main;
+ my $custnum = $opt{custnum};
+ $custnum ||= $self->cust_bill->custnum if $self->cust_bill;
+
+ $cust_main = FS::cust_main->by_key( $custnum )
+ or die "set_exemptions can't identify customer (pass custnum option)\n";
+
+ my @new_exemptions;
+ my $taxable_charged = $self->setup + $self->recur;
+ return unless $taxable_charged > 0;
+
+ ### Fully exempt customer ###
+ 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;
+ }
+
+ ### Exemption from named tax ###
+ my $exempt_cust_taxname;
+ if ( !$exempt_cust and $tax->taxname ) {
+ $exempt_cust_taxname = $cust_main->tax_exemption($tax->taxname);
+ }
+
+ 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;
+
+ }
+
+ my $exempt_setup = ( ($part_fee and not $part_fee->taxable)
+ or ($part_pkg and $part_pkg->setuptax)
+ or $tax->setuptax );
+
+ if ( $exempt_setup
+ and $self->setup > 0
+ and $taxable_charged > 0 ) {
+
+ push @new_exemptions, FS::cust_tax_exempt_pkg->new({
+ amount => $self->setup,
+ exempt_setup => 'Y'
+ });
+ $taxable_charged -= $self->setup;
+
+ }
+
+ my $exempt_recur = ( ($part_fee and not $part_fee->taxable)
+ or ($part_pkg and $part_pkg->recurtax)
+ or $tax->recurtax );
+
+ if ( $exempt_recur
+ and $self->recur > 0
+ and $taxable_charged > 0 ) {
+
+ push @new_exemptions, FS::cust_tax_exempt_pkg->new({
+ amount => $self->recur,
+ exempt_recur => 'Y'
+ });
+ $taxable_charged -= $self->recur;
+
+ }
+
+ foreach (@new_exemptions) {
+ $_->set('taxnum', $tax->taxnum);
+ $_->set('taxtype', ref($tax));
+ }
+
+ push @{ $self->cust_tax_exempt_pkg }, @new_exemptions;
+ return @new_exemptions;
+
+}
+
=item cust_bill
Returns the invoice (see L<FS::cust_bill>) for this invoice line item.
+=item cust_main
+
+Returns the customer (L<FS::cust_main> object) for this line item.
+
=cut
-sub cust_bill {
+sub cust_main {
+ carp "->cust_main called" if $DEBUG;
+ # required for cust_main_Mixin equivalence
+ # and use cust_bill instead of cust_pkg because this might not have a
+ # cust_pkg
my $self = shift;
- qsearchs( 'cust_bill', { 'invnum' => $self->invnum } );
+ my $cust_bill = $self->cust_bill or return '';
+ $cust_bill->cust_main;
}
=item previous_cust_bill_pkg
$self->pkgnum ? $self->part_pkg->calc_units($self->cust_pkg) : 0; # 1?
}
+=item _item_discount
+
+If this item has any discounts, returns a hashref in the format used
+by L<FS::Template_Mixin/_items_cust_bill_pkg> to describe the discount(s)
+on an invoice. This will contain the keys 'description', 'amount',
+'ext_description' (an arrayref of text lines describing the discounts),
+and '_is_discount' (a flag).
+
+The value for 'amount' will be negative, and will be scaled for the package
+quantity.
+
+=cut
+
+sub _item_discount {
+ my $self = shift;
+ my %options = @_;
+
+ my $d; # this will be returned.
+
+ my @pkg_discounts = $self->pkg_discount;
+ if (@pkg_discounts) {
+ # special case: if there are old "discount details" on this line item,
+ # don't show discount line items
+ if ( FS::cust_bill_pkg_detail->count("detail LIKE 'Includes discount%' AND billpkgnum = ?", $self->billpkgnum || 0) > 0 ) {
+ return;
+ }
+
+ my @ext;
+ $d = {
+ _is_discount => 1,
+ description => $self->mt('Discount'),
+ setup_amount => 0,
+ recur_amount => 0,
+ ext_description => \@ext,
+ pkgpart => $self->pkgpart,
+ feepart => $self->feepart,
+ # maybe should show quantity/unit discount?
+ };
+ foreach my $pkg_discount (@pkg_discounts) {
+ push @ext, $pkg_discount->description;
+ my $setuprecur = $pkg_discount->cust_pkg_discount->setuprecur;
+ $d->{$setuprecur.'_amount'} -= $pkg_discount->amount;
+ }
+ }
+
+ # show introductory rate as a pseudo-discount
+ if (!$d) { # this will conflict with showing real discounts
+ my $part_pkg = $self->part_pkg;
+ if ( $part_pkg and $part_pkg->option('show_as_discount',1) ) {
+ my $cust_pkg = $self->cust_pkg;
+ my $intro_end = $part_pkg->intro_end($cust_pkg);
+ my $_date = $self->cust_bill->_date;
+ if ( $intro_end > $_date ) {
+ $d = $part_pkg->item_discount($cust_pkg);
+ }
+ }
+ }
+
+ if ( $d ) {
+ $d->{setup_amount} *= $self->quantity || 1; # ??
+ $d->{recur_amount} *= $self->quantity || 1; # ??
+ }
+
+ $d;
+}
=item set_display OPTION => VALUE ...
sub disintegrate {
my $self = shift;
# XXX this goes away with cust_bill_pkg refactor
+ # or at least I wish it would, but it turns out to be harder than
+ # that.
- my $cust_bill_pkg = new FS::cust_bill_pkg { $self->hash };
+ #my $cust_bill_pkg = new FS::cust_bill_pkg { $self->hash }; # wha huh?
my %cust_bill_pkg = ();
- $cust_bill_pkg{setup} = $cust_bill_pkg if $cust_bill_pkg->setup;
- $cust_bill_pkg{recur} = $cust_bill_pkg if $cust_bill_pkg->recur;
-
-
- #split setup and recur
- if ($cust_bill_pkg->setup && $cust_bill_pkg->recur) {
- my $cust_bill_pkg_recur = new FS::cust_bill_pkg { $cust_bill_pkg->hash };
- $cust_bill_pkg->set('details', []);
- $cust_bill_pkg->recur(0);
- $cust_bill_pkg->unitrecur(0);
- $cust_bill_pkg->type('');
- $cust_bill_pkg_recur->setup(0);
- $cust_bill_pkg_recur->unitsetup(0);
- $cust_bill_pkg{recur} = $cust_bill_pkg_recur;
-
+ my $usage_total;
+ foreach my $classnum ($self->usage_classes) {
+ my $amount = $self->usage($classnum);
+ next if $amount == 0; # though if so we shouldn't be here
+ my $usage_item = FS::cust_bill_pkg->new({
+ $self->hash,
+ 'setup' => 0,
+ 'recur' => $amount,
+ 'taxclass' => $classnum,
+ 'inherit' => $self
+ });
+ $cust_bill_pkg{$classnum} = $usage_item;
+ $usage_total += $amount;
}
- #split usage from recur
- my $usage = sprintf( "%.2f", $cust_bill_pkg{recur}->usage )
- if exists($cust_bill_pkg{recur});
- warn "usage is $usage\n" if $DEBUG > 1;
- if ($usage) {
- my $cust_bill_pkg_usage =
- new FS::cust_bill_pkg { $cust_bill_pkg{recur}->hash };
- $cust_bill_pkg_usage->recur( $usage );
- $cust_bill_pkg_usage->type( 'U' );
- my $recur = sprintf( "%.2f", $cust_bill_pkg{recur}->recur - $usage );
- $cust_bill_pkg{recur}->recur( $recur );
- $cust_bill_pkg{recur}->type( '' );
- $cust_bill_pkg{recur}->set('details', []);
- $cust_bill_pkg{''} = $cust_bill_pkg_usage;
+ foreach (qw(setup recur)) {
+ next if ($self->get($_) == 0);
+ my $item = FS::cust_bill_pkg->new({
+ $self->hash,
+ 'setup' => 0,
+ 'recur' => 0,
+ 'taxclass' => $_,
+ 'inherit' => $self,
+ });
+ $item->set($_, $self->get($_));
+ $cust_bill_pkg{$_} = $item;
}
- #subdivide usage by usage_class
- if (exists($cust_bill_pkg{''})) {
- foreach my $class (grep { $_ } $self->usage_classes) {
- my $usage = sprintf( "%.2f", $cust_bill_pkg{''}->usage($class) );
- my $cust_bill_pkg_usage =
- new FS::cust_bill_pkg { $cust_bill_pkg{''}->hash };
- $cust_bill_pkg_usage->recur( $usage );
- $cust_bill_pkg_usage->set('details', []);
- my $classless = sprintf( "%.2f", $cust_bill_pkg{''}->recur - $usage );
- $cust_bill_pkg{''}->recur( $classless );
- $cust_bill_pkg{$class} = $cust_bill_pkg_usage;
- }
- warn "Unexpected classless usage value: ". $cust_bill_pkg{''}->recur
- if ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur < 0);
- delete $cust_bill_pkg{''}
- unless ($cust_bill_pkg{''}->recur && $cust_bill_pkg{''}->recur > 0);
+ if ($usage_total) {
+ $cust_bill_pkg{recur}->set('recur',
+ sprintf('%.2f', $cust_bill_pkg{recur}->get('recur') - $usage_total)
+ );
}
-# # sort setup,recur,'', and the rest numeric && return
-# my @result = map { $cust_bill_pkg{$_} }
-# sort { my $ad = ($a=~/^\d+$/); my $bd = ($b=~/^\d+$/);
-# ( $ad cmp $bd ) || ( $ad ? $a<=>$b : $b cmp $a )
-# }
-# keys %cust_bill_pkg;
-#
-# return (@result);
-
- %cust_bill_pkg;
+ %cust_bill_pkg;
}
=item usage CLASSNUM
my $sql = 'SELECT SUM(COALESCE(amount,0)) FROM cust_bill_pkg_detail '.
' WHERE billpkgnum = '. $self->billpkgnum;
- $sql .= " AND classnum = $classnum" if defined($classnum);
+ if (defined $classnum) {
+ if ($classnum =~ /^(\d+)$/) {
+ $sql .= " AND classnum = $1";
+ } elsif ($classnum eq '') {
+ $sql .= " AND classnum IS NULL";
+ }
+ }
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute or die $sth->errstr;
sub cust_tax_exempt_pkg {
my ( $self ) = @_;
- $self->{Hash}->{cust_tax_exempt_pkg} ||= [];
+ my $array = $self->{Hash}->{cust_tax_exempt_pkg} ||= [];
}
=item cust_bill_pkg_tax_Xlocation
=item recur_show_zero
-=cut
+Whether to show a zero recurring amount. This is true if the package or its
+definition has the recur_show_zero flag, and the recurring fee is actually
+zero for this period.
-sub recur_show_zero { shift->_X_show_zero('recur'); }
-sub setup_show_zero { shift->_X_show_zero('setup'); }
+=cut
-sub _X_show_zero {
+sub recur_show_zero {
my( $self, $what ) = @_;
- return 0 unless $self->$what() == 0 && $self->pkgnum;
+ return 0 unless $self->get('recur') == 0 && $self->pkgnum;
- $self->cust_pkg->_X_show_zero($what);
+ $self->cust_pkg->_X_show_zero('recur');
+}
+
+=item setup_show_zero
+
+Whether to show a zero setup charge. This requires the package or its
+definition to have the setup_show_zero flag, but it also returns false if
+the package's setup date is before this line item's start date.
+
+=cut
+
+sub setup_show_zero {
+ my $self = shift;
+ return 0 unless $self->get('setup') == 0 && $self->pkgnum;
+ my $cust_pkg = $self->cust_pkg;
+ return 0 if ( $self->sdate || 0 ) > ( $cust_pkg->setup || 0 );
+ return $cust_pkg->_X_show_zero('setup');
}
=item credited [ BEFORE, AFTER, OPTIONS ]
$self->scalar_sql('SELECT '. $self->credited_sql(@_).' FROM cust_bill_pkg WHERE billpkgnum = ?', $self->billpkgnum);
}
+=item tax_locationnum
+
+Returns the L<FS::cust_location> number that this line item is in for tax
+purposes. For package sales, it's the package tax location; for fees,
+it's the customer's default service location.
+
+=cut
+
+sub tax_locationnum {
+ my $self = shift;
+ if ( $self->pkgnum ) { # normal sales
+ return $self->cust_pkg->tax_locationnum;
+ } elsif ( $self->feepart ) { # fees
+ my $custnum = $self->fee_origin->custnum;
+ if ( $custnum ) {
+ return FS::cust_main->by_key($custnum)->ship_locationnum;
+ }
+ } else { # taxes
+ return '';
+ }
+}
+
+sub tax_location {
+ my $self = shift;
+ if ( $self->pkgnum ) { # normal sales
+ return $self->cust_pkg->tax_location;
+ } elsif ( $self->feepart ) { # fees
+ my $fee_origin = $self->fee_origin;
+ if ( $fee_origin ) {
+ my $custnum = $fee_origin->custnum;
+ if ( $custnum ) {
+ return FS::cust_main->by_key($custnum)->ship_location;
+ }
+ }
+ } else { # taxes
+ return;
+ }
+}
+
=back
=head1 CLASS METHODS
# this makes owed_sql, etc. much more concise
sub charged_sql {
my ($class, $start, $end, %opt) = @_;
+ my $setuprecur = $opt{setuprecur} || '';
my $charged =
- $opt{setuprecur} =~ /^s/ ? 'cust_bill_pkg.setup' :
- $opt{setuprecur} =~ /^r/ ? 'cust_bill_pkg.recur' :
+ $setuprecur =~ /^s/ ? 'cust_bill_pkg.setup' :
+ $setuprecur =~ /^r/ ? 'cust_bill_pkg.recur' :
'cust_bill_pkg.setup + cust_bill_pkg.recur';
if ($opt{no_usage} and $charged =~ /recur/) {
sub paid_sql {
my ($class, $start, $end, %opt) = @_;
- my $s = $start ? "AND cust_bill_pay._date <= $start" : '';
- my $e = $end ? "AND cust_bill_pay._date > $end" : '';
- my $setuprecur =
- $opt{setuprecur} =~ /^s/ ? 'setup' :
- $opt{setuprecur} =~ /^r/ ? 'recur' :
- '';
+ my $s = $start ? "AND cust_pay._date <= $start" : '';
+ my $e = $end ? "AND cust_pay._date > $end" : '';
+ my $setuprecur = $opt{setuprecur} || '';
+ $setuprecur = 'setup' if $setuprecur =~ /^s/;
+ $setuprecur = 'recur' if $setuprecur =~ /^r/;
$setuprecur &&= "AND setuprecur = '$setuprecur'";
my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
+ JOIN cust_pay USING (paynum)
WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
$s $e $setuprecur )";
sub credited_sql {
my ($class, $start, $end, %opt) = @_;
- my $s = $start ? "AND cust_credit_bill._date <= $start" : '';
- my $e = $end ? "AND cust_credit_bill._date > $end" : '';
- my $setuprecur =
- $opt{setuprecur} =~ /^s/ ? 'setup' :
- $opt{setuprecur} =~ /^r/ ? 'recur' :
- '';
+ my $s = $start ? "AND cust_credit._date <= $start" : '';
+ my $e = $end ? "AND cust_credit._date > $end" : '';
+ my $setuprecur = $opt{setuprecur} || '';
+ $setuprecur = 'setup' if $setuprecur =~ /^s/;
+ $setuprecur = 'recur' if $setuprecur =~ /^r/;
$setuprecur &&= "AND setuprecur = '$setuprecur'";
my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
+ JOIN cust_credit USING (crednum)
WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
$s $e $setuprecur )";
local $FS::cust_location::import = 1;
my $conf = FS::Conf->new; # h_conf?
- return if $conf->exists('enable_taxproducts'); #don't touch this case
+ return if $conf->config('tax_data_vendor'); #don't touch this case
my $use_ship = $conf->exists('tax-ship_address');
+ my $use_pkgloc = $conf->exists('tax-pkg_address');
my $date_where = '';
if ($opt{s}) {
' WHERE cust_bill_pkg.invnum = cust_bill.invnum'.
' AND exempt_monthly IS NULL';
- my @invnums = map { $_->invnum } qsearch({
- select => 'cust_bill.invnum',
+ my %all_tax_names = (
+ '' => 1,
+ 'Tax' => 1,
+ map { $_->taxname => 1 }
+ qsearch('h_cust_main_county', { taxname => { op => '!=', value => '' }})
+ );
+
+ my $search = FS::Cursor->new({
table => 'cust_bill',
hashref => {},
extra_sql => "WHERE NOT EXISTS($sub_has_tax_link) ".
$date_where,
});
- print "Processing ".scalar(@invnums)." invoices...\n";
+#print "Processing ".scalar(@invnums)." invoices...\n";
my $committed;
INVOICE:
- foreach my $invnum (@invnums) {
+ while (my $cust_bill = $search->fetch) {
+ my $invnum = $cust_bill->invnum;
$committed = 0;
print STDERR "Invoice #$invnum\n";
my $pre = '';
# 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;
+ local($FS::Record::qsearch_qualify_columns) = 0;
my $h_cust_main = qsearchs('h_cust_main',
- { custnum => $custnum },
+ { custnum => $custnum },
FS::h_cust_main->sql_h_searchs($date)
);
if (!$h_cust_main ) {
# 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 = FS::cust_location->new_or_existing(\%hash);
- if ( !$tax_loc->locationnum ) {
- $tax_loc->disabled('Y');
- my $error = $tax_loc->insert;
+ my %tax_loc; # keys are pkgnums, values are cust_location objects
+ my $default_tax_loc;
+ if ( $h_cust_main->bill_locationnum ) {
+ # the location has already been upgraded
+ if ($use_ship) {
+ $default_tax_loc = $h_cust_main->ship_location;
+ } else {
+ $default_tax_loc = $h_cust_main->bill_location;
+ }
+ } else {
+ $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;
+ $default_tax_loc = FS::cust_location->new(\%hash);
+ my $error = $default_tax_loc->find_or_insert || $default_tax_loc->disable_if_unused;
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)
- );
+ my $exempt_cust;
+ $exempt_cust = 1 if $h_cust_main->tax;
# classify line items
my @tax_items;
} else {
# (pkgparts really shouldn't change, right?)
+ local($FS::Record::qsearch_qualify_columns) = 0;
my $h_cust_pkg = qsearchs('h_cust_pkg', { pkgnum => $pkgnum },
FS::h_cust_pkg->sql_h_searchs($date)
);
}
my $pkgpart = $h_cust_pkg->pkgpart;
+ if ( $use_pkgloc and $h_cust_pkg->locationnum ) {
+ # then this package already had a locationnum assigned, and that's
+ # the one to use for tax calculation
+ $tax_loc{$pkgnum} = FS::cust_location->by_key($h_cust_pkg->locationnum);
+ } else {
+ # use the customer's bill or ship loc, which was inserted earlier
+ $tax_loc{$pkgnum} = $default_tax_loc;
+ }
+
if (!exists $pkgpart_taxclass{$pkgpart}) {
+ local($FS::Record::qsearch_qualify_columns) = 0;
my $h_part_pkg = qsearchs('h_part_pkg', { pkgpart => $pkgpart },
FS::h_part_pkg->sql_h_searchs($date)
);
push @{ $nontax_items{$taxclass} }, $item;
}
}
+
printf("%d tax items: \$%.2f\n", scalar(@tax_items), map {$_->setup} @tax_items)
if @tax_items;
+ # Get any per-customer taxname exemptions that were in effect.
+ my %exempt_cust_taxname;
+ foreach (keys %all_tax_names) {
+ local($FS::Record::qsearch_qualify_columns) = 0;
+ my $h_exemption = qsearchs('h_cust_main_exemption', {
+ 'custnum' => $custnum,
+ 'taxname' => $_,
+ },
+ FS::h_cust_main_exemption->sql_h_searchs($date, $date)
+ );
+ if ($h_exemption) {
+ $exempt_cust_taxname{ $_ } = 1;
+ }
+ }
+
# 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} = '';
+ foreach my $orig_item (@{ $nontax_items{$taxclass} }) {
+ my $my_tax_loc = $tax_loc{ $orig_item->pkgnum };
+ my %myhash = map { $_ => $my_tax_loc->get($pre.$_) } @loc_keys;
+ 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);
- }
- $myhash{ shift @elim } = '';
- } while scalar(@elim) and !@taxdefs;
+ 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;
+ 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} }) {
+ $taxable_items{$taxdef->taxnum} ||= [];
# 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 });
next INVOICE;
}
} #foreach @new_exempt
- } #foreach $item
- } #foreach $taxdef
+ } #foreach $taxdef
+ } #foreach $item
} #foreach $taxclass
# Now go through the billed taxes and match them up with the line items.
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";
+ warn "no definition found for tax item '$taxname', custnum $custnum\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;
printf("\t$taxclass: %.2f\n", $this_est_tax->{$taxclass}/$est_total);
foreach my $nontax (@items) {
+ my $my_tax_loc = $tax_loc{ $nontax->pkgnum };
my $part = int($real_tax
# class allocation
* ($this_est_tax->{$taxclass}/$est_total)
push @tax_links, {
taxnum => $taxdef->taxnum,
pkgnum => $nontax->pkgnum,
+ locationnum => $my_tax_loc->locationnum,
billpkgnum => $nontax->billpkgnum,
cents => $part,
};
my $i = 0;
my $nlinks = scalar(@tax_links);
if ( $nlinks ) {
- while (int($cents_remaining) > 0) {
+ # ensure that it really is an integer
+ $cents_remaining = sprintf('%.0f', $cents_remaining);
+ while ($cents_remaining > 0) {
$tax_links[$i % $nlinks]->{cents} += 1;
$cents_remaining--;
$i++;
my $link = FS::cust_bill_pkg_tax_location->new({
billpkgnum => $tax_item->billpkgnum,
taxtype => 'FS::cust_main_county',
- locationnum => $tax_loc->locationnum,
+ locationnum => $_->{locationnum},
taxnum => $_->{taxnum},
pkgnum => $_->{pkgnum},
amount => sprintf('%.2f', $_->{cents} / 100),
'';
}
+sub _pkg_tax_list {
+ # Return an array of hashrefs for each cust_bill_pkg_tax_location
+ # applied to this bill for this cust_bill_pkg.pkgnum.
+ #
+ # ! Important Note:
+ # In some situations, this list will contain more tax records than the
+ # ones directly related to $self->billpkgnum. The returned list contains
+ # all records, for this bill, charged against this billpkgnum's pkgnum.
+ #
+ # One must keep this in mind when using data returned by this method.
+ #
+ # An unaddressed deficiency in the cust_bill_pkg_tax_location model makes
+ # this necessary: When a linked-hidden package generates a tax/fee as a row
+ # in cust_bill_pkg_tax_location, there is not enough information to surmise
+ # with specificity which billpkgnum row represents the direct parent of the
+ # the linked-hidden package's tax row. The closest we can get to this
+ # backwards reassociation is to use the pkgnum. Therefore, when multiple
+ # billpkgnum's appear with the same pkgnum, this method is going to return
+ # the tax records for ALL of those billpkgnum's, not just $self->billpkgnum.
+ #
+ # This could be addressed with an update to the model, and to the billing
+ # routine that generates rows into cust_bill_pkg_tax_location. Perhaps a
+ # column, link_billpkgnum or parent_billpkgnum, recording the link. I'm not
+ # doing that now, because there would be no possible repair of data stored
+ # historically prior to such a fix. I need _pkg_tax_list() to not be
+ # broken for already-generated bills.
+ #
+ # Any code you write relying on _pkg_tax_list() MUST be aware of, and
+ # account for, the possible return of duplicated tax records returned
+ # when method is called on multiple cust_bill_pkg_tax_location rows.
+ # Duplicates can be identified by billpkgtaxlocationnum column.
+
+ my $self = shift;
+
+ my $search_selector;
+ if ( $self->pkgnum ) {
+
+ # For taxes applied to normal billing items
+ $search_selector =
+ ' cust_bill_pkg_tax_location.pkgnum = '
+ . dbh->quote( $self->pkgnum );
+
+ } elsif ( $self->feepart ) {
+
+ # For taxes applied to fees, when the fee is not attached to a package
+ # i.e. late fees, billing events fees
+ $search_selector =
+ ' cust_bill_pkg_tax_location.taxable_billpkgnum = '
+ . dbh->quote( $self->billpkgnum );
+
+ } else {
+ warn "_pkg_tax_list() unhandled case breaking taxes into sections";
+ warn "_pkg_tax_list() $_: ".$self->$_
+ for qw(pkgnum billpkgnum feepart);
+ return;
+ }
+
+ map +{
+ billpkgtaxlocationnum => $_->billpkgtaxlocationnum,
+ billpkgnum => $_->billpkgnum,
+ taxnum => $_->taxnum,
+ amount => $_->amount,
+ taxname => $_->taxname,
+ },
+ qsearch({
+ table => 'cust_bill_pkg_tax_location',
+ addl_from => '
+ LEFT JOIN cust_bill_pkg
+ ON cust_bill_pkg.billpkgnum
+ = cust_bill_pkg_tax_location.taxable_billpkgnum
+ ',
+ select => join( ', ', (qw|
+ cust_bill_pkg.billpkgnum
+ cust_bill_pkg_tax_location.billpkgtaxlocationnum
+ cust_bill_pkg_tax_location.taxnum
+ cust_bill_pkg_tax_location.amount
+ |)),
+ extra_sql =>
+ ' WHERE '.
+ ' cust_bill_pkg.invnum = ' . dbh->quote( $self->invnum ) .
+ ' AND '.
+ $search_selector
+ });
+
+}
+
sub _upgrade_data {
# Create a queue job to run upgrade_tax_location from January 1, 2012 to
# the present date.
});
# call it kind of like a class method, not that it matters much
$job->insert($class, 's' => str2time('2012-01-01'));
+ # if there's a customer location upgrade queued also, wait for it to
+ # finish
+ my $location_job = qsearchs('queue', {
+ job => 'FS::cust_main::Location::process_upgrade_location'
+ });
+ if ( $location_job ) {
+ $job->depend_insert($location_job->jobnum);
+ }
# Then mark the upgrade as done, so that we don't queue the job twice
# and somehow run two of them concurrently.
FS::upgrade_journal->set_done($upgrade);
=cut
1;
-
projects / freeside.git / blobdiff