1 package FS::cust_bill_pkg_tax_location;
4 use base qw( FS::Record );
5 use FS::Record qw( qsearch qsearchs );
9 use FS::cust_bill_pay_pkg;
10 use FS::cust_credit_bill_pkg;
11 use FS::cust_main_county;
14 use List::Util qw(sum min);
18 FS::cust_bill_pkg_tax_location - Object methods for cust_bill_pkg_tax_location records
22 use FS::cust_bill_pkg_tax_location;
24 $record = new FS::cust_bill_pkg_tax_location \%hash;
25 $record = new FS::cust_bill_pkg_tax_location { 'column' => 'value' };
27 $error = $record->insert;
29 $error = $new_record->replace($old_record);
31 $error = $record->delete;
33 $error = $record->check;
37 An FS::cust_bill_pkg_tax_location object represents an record of taxation
38 based on package location. FS::cust_bill_pkg_tax_location inherits from
39 FS::Record. The following fields are currently supported:
43 =item billpkgtaxlocationnum
71 =item taxable_billpkgnum
73 The billpkgnum of the L<FS::cust_bill_pkg> that this tax was charged on.
74 It may specifically be on any portion of that line item (setup, recurring,
85 Creates a new record. To add the record to the database, see L<"insert">.
87 Note that this stores the hash reference, not a distinct copy of the hash it
88 points to. You can ask the object for a copy with the I<hash> method.
92 sub table { 'cust_bill_pkg_tax_location'; }
96 Adds this record to the database. If there is an error, returns the error,
97 otherwise returns false.
101 Delete this record from the database.
103 =item replace OLD_RECORD
105 Replaces the OLD_RECORD with this one in the database. If there is an error,
106 returns the error, otherwise returns false.
110 Checks all fields to make sure this is a valid record. If there is
111 an error, returns the error, otherwise returns false. Called by the insert
116 # the check method should currently be supplied - FS::Record contains some
117 # data checking routines
123 $self->ut_numbern('billpkgtaxlocationnum')
124 || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg', 'billpkgnum' )
125 || $self->ut_number('taxnum') #cust_bill_pkg/tax_rate key, based on taxtype
126 || $self->ut_enum('taxtype', [ qw( FS::cust_main_county FS::tax_rate ) ] )
127 || $self->ut_foreign_key('pkgnum', 'cust_pkg', 'pkgnum' )
128 || $self->ut_foreign_key('locationnum', 'cust_location', 'locationnum' )
129 || $self->ut_money('amount')
130 || $self->ut_foreign_key('taxable_billpkgnum', 'cust_bill_pkg', 'billpkgnum')
132 return $error if $error;
139 Returns the associated cust_bill_pkg object (i.e. the tax charge).
145 qsearchs( 'cust_bill_pkg', { 'billpkgnum' => $self->billpkgnum } );
148 =item taxable_cust_bill_pkg
150 Returns the cust_bill_pkg object for the I<taxable> charge.
154 Returns the associated cust_location object
160 qsearchs( 'cust_location', { 'locationnum' => $self->locationnum } );
165 Returns a description for this tax line item constituent. Currently this
166 is the desc of the associated line item followed by the state/county/city
167 for the location in parentheses.
173 my $cust_location = $self->cust_location;
174 my $location = join('/', grep { $_ } # leave in?
175 map { $cust_location->$_ }
176 qw( state county city ) # country?
178 my $cust_bill_pkg_desc = $self->billpkgnum
179 ? $self->cust_bill_pkg->desc
180 : $self->cust_bill_pkg_desc;
181 "$cust_bill_pkg_desc ($location)";
186 Returns the amount owed (still outstanding) on this tax line item which is
187 the amount of this record minus all payment applications and credit
194 my $balance = $self->amount;
195 $balance -= $_->amount foreach ( $self->cust_bill_pay_pkg('setup') );
196 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg('setup') );
197 $balance = sprintf( '%.2f', $balance );
198 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
202 sub cust_bill_pay_pkg {
204 qsearch( 'cust_bill_pay_pkg',
205 { map { $_ => $self->$_ } qw( billpkgtaxlocationnum billpkgnum ) }
209 sub cust_credit_bill_pkg {
211 qsearch( 'cust_credit_bill_pkg',
212 { map { $_ => $self->$_ } qw( billpkgtaxlocationnum billpkgnum ) }
216 sub cust_main_county {
218 return '' unless $self->taxtype eq 'FS::cust_main_county';
219 qsearchs( 'cust_main_county', { 'taxnum' => $self->taxnum } );
225 use Date::Parse 'str2time';
228 my $upgrade = 'tax_location_taxable_billpkgnum';
229 return if FS::upgrade_journal->is_done($upgrade);
230 my $job = FS::queue->new({ job =>
231 'FS::cust_bill_pkg_tax_location::upgrade_taxable_billpkgnum'
233 $job->insert($class, 's' => str2time('2012-01-01'));
234 FS::upgrade_journal->set_done($upgrade);
237 sub upgrade_taxable_billpkgnum {
238 # Associate these records to the correct taxable line items.
239 # The cust_bill_pkg upgrade now does this also for pre-3.0 records that
240 # aren't broken out by pkgnum, so we only need to deal with the case of
241 # multiple line items for the same pkgnum.
242 # Despite appearances, this has almost no relation to the upgrade in
245 my ($class, %opt) = @_;
246 my $dbh = FS::UID::dbh();
247 my $oldAutoCommit = $FS::UID::AutoCommit;
248 local $FS::UID::AutoCommit = 0;
249 my $log = FS::Log->new('upgrade_taxable_billpkgnum');
253 $date_where .= " AND cust_bill._date >= $opt{s}";
256 $date_where .= " AND cust_bill._date < $opt{e}";
259 my @need_to_upgrade = qsearch({
260 select => 'cust_bill_pkg_tax_location.*',
261 table => 'cust_bill_pkg_tax_location',
262 hashref => { taxable_billpkgnum => '' },
263 addl_from => 'JOIN cust_bill_pkg USING (billpkgnum)'.
264 'JOIN cust_bill USING (invnum)',
265 extra_sql => $date_where,
267 $log->info('Starting upgrade of '.scalar(@need_to_upgrade).
268 ' cust_bill_pkg_tax_location records.');
270 # keys are billpkgnums
273 foreach (@need_to_upgrade) {
274 my $tax_billpkgnum = $_->billpkgnum;
275 $cust_bill_pkg{ $tax_billpkgnum } ||= FS::cust_bill_pkg->by_key($tax_billpkgnum);
276 $tax_location{ $tax_billpkgnum } ||= [];
277 push @{ $tax_location{ $tax_billpkgnum } }, $_;
280 TAX_ITEM: foreach my $tax_item (values %cust_bill_pkg) {
281 my $tax_locations = $tax_location{ $tax_item->billpkgnum };
282 my $invnum = $tax_item->invnum;
283 my $cust_bill = FS::cust_bill->by_key($tax_item->invnum);
284 my %tax_on_pkg; # keys are tax identifiers
285 TAX_LOCATION: foreach my $tax_location (@$tax_locations) {
286 # recapitulate the "cust_main_county $taxnum $pkgnum" tax identifier,
288 my $taxid = join(' ',
289 $tax_location->taxtype,
290 $tax_location->taxnum,
291 $tax_location->pkgnum,
292 $tax_location->locationnum
294 $tax_on_pkg{$taxid} ||= [];
295 push @{ $tax_on_pkg{$taxid} }, $tax_location;
297 PKGNUM: foreach my $taxid (keys %tax_on_pkg) {
298 my ($taxtype, $taxnum, $pkgnum, $locationnum) = split(' ', $taxid);
299 $log->info("tax#$taxnum, pkg#$pkgnum", object => $cust_bill);
300 my @pkg_items = $cust_bill->cust_bill_pkg_pkgnum($pkgnum);
302 # then how is there tax on it? should never happen
303 $log->error("no line items with pkg#$pkgnum", object => $cust_bill);
307 foreach my $pkg_item (@pkg_items) {
308 # find the taxable amount of each one
309 my $amount = $pkg_item->setup + $pkg_item->recur;
310 # subtract any exemptions that apply to this taxdef
311 foreach (qsearch('cust_tax_exempt_pkg', {
313 billpkgnum => $pkg_item->billpkgnum
316 $amount -= $_->amount;
318 $pkg_item->set('amount' => $pkg_item->setup + $pkg_item->recur);
319 $pkg_amount += $amount;
321 next PKGNUM if $pkg_amount == 0; # probably because it's fully exempted
322 # now sort them descending by taxable amount
323 @pkg_items = sort { $b->amount <=> $a->amount }
325 # and do the same with the tax links
326 # (there should be one per taxed item)
327 my @tax_links = sort { $b->amount <=> $a->amount }
328 @{ $tax_on_pkg{$taxid} };
330 if (scalar(@tax_links) == scalar(@pkg_items)) {
331 # the relatively simple case: they match 1:1
332 for my $i (0 .. scalar(@tax_links) - 1) {
333 $tax_links[$i]->set('taxable_billpkgnum',
334 $pkg_items[$i]->billpkgnum);
335 my $error = $tax_links[$i]->replace;
337 $log->error("failed to set taxable_billpkgnum in tax on pkg#$pkgnum",
338 object => $cust_bill);
343 # the more complicated case
344 $log->warn("mismatched charges and tax links in pkg#$pkgnum",
345 object => $cust_bill);
346 my $tax_amount = sum(map {$_->amount} @tax_links);
347 # remove all tax link records and recreate them to be 1:1 with
349 my (%billpaynum, %creditbillnum);
351 foreach my $tax_link (@tax_links) {
352 $link_type ||= ref($tax_link);
353 my $error = $tax_link->delete;
355 $log->error("error unlinking tax#$taxnum pkg#$pkgnum",
356 object => $cust_bill);
359 my $pkey = $tax_link->primary_key;
360 # also remove all applications that reference this tax link
361 # (they will be applications to the tax item)
362 my %hash = ($pkey => $tax_link->get($pkey));
363 foreach (qsearch('cust_bill_pay_pkg', \%hash)) {
364 $billpaynum{$_->billpaynum} += $_->amount;
365 my $error = $_->delete;
366 die "error unapplying payment: $error" if ( $error );
368 foreach (qsearch('cust_credit_bill_pkg', \%hash)) {
369 $creditbillnum{$_->creditbillnum} += $_->amount;
370 my $error = $_->delete;
371 die "error unapplying credit: $error" if ( $error );
375 my $cents_remaining = int(100 * $tax_amount);
376 foreach my $pkg_item (@pkg_items) {
377 my $cents = int(100 * $pkg_item->amount * $tax_amount / $pkg_amount);
378 my $tax_link = $link_type->new({
379 taxable_billpkgnum => $pkg_item->billpkgnum,
380 billpkgnum => $tax_item->billpkgnum,
384 locationnum => $locationnum,
387 push @tax_links, $tax_link;
388 $cents_remaining -= $cents;
390 my $nlinks = scalar @tax_links;
392 while ($cents_remaining) {
393 $tax_links[$i % $nlinks]->set('cents' =>
394 $tax_links[$i % $nlinks]->cents + 1
399 foreach my $tax_link (@tax_links) {
400 $tax_link->set('amount' => sprintf('%.2f', $tax_link->cents / 100));
401 my $error = $tax_link->insert;
403 $log->error("error relinking tax#$taxnum pkg#$pkgnum",
404 object => $cust_bill);
411 my $left = 0; # the amount "left" on the last tax link after
412 # applying payments, but before credits, so that
413 # it can receive both a payment and a credit if
415 # reapply payments/credits...this sucks
416 foreach my $billpaynum (keys %billpaynum) {
417 my $pay_amount = $billpaynum{$billpaynum};
418 while ($i < $nlinks and $pay_amount > 0) {
419 my $this_amount = min($pay_amount, $tax_links[$i]->amount);
420 $left = $tax_links[$i]->amount - $this_amount;
421 my $app = FS::cust_bill_pay_pkg->new({
422 billpaynum => $billpaynum,
423 billpkgnum => $tax_links[$i]->billpkgnum,
424 billpkgtaxlocationnum => $tax_links[$i]->billpkgtaxlocationnum,
425 amount => $this_amount,
426 setuprecur => 'setup',
427 # sdate/edate are null
429 my $error ||= $app->insert;
430 $pay_amount -= $this_amount;
434 foreach my $creditbillnum (keys %creditbillnum) {
435 my $credit_amount = $creditbillnum{$creditbillnum};
436 while ($i < $nlinks and $credit_amount > 0) {
437 my $this_amount = min($left, $credit_amount, $tax_links[$i]->amount);
438 $left = $credit_amount * 2; # just so it can't be selected twice
439 $i++ if $this_amount == $left
440 or $this_amount == $tax_links[$i]->amount;
441 my $app = FS::cust_credit_bill_pkg->new({
442 creditbillnum => $creditbillnum,
443 billpkgnum => $tax_links[$i]->billpkgnum,
444 billpkgtaxlocationnum => $tax_links[$i]->billpkgtaxlocationnum,
445 amount => $this_amount,
446 setuprecur => 'setup',
447 # sdate/edate are null
449 my $error ||= $app->insert;
450 $credit_amount -= $this_amount;
454 # we've just unapplied a bunch of stuff, so if it won't reapply
455 # we really need to revert the whole transaction
456 die "error reapplying payments/credits: $error; upgrade halted";
458 } # scalar(@tax_links) ?= scalar(@pkg_items)
462 $log->info('finish');
464 $dbh->commit if $oldAutoCommit;
474 The presence of FS::cust_main_county::delete makes the cust_main_county method
477 Pre-3.0 versions of Freeside would only create one cust_bill_pkg_tax_location
478 per tax definition (taxtype/taxnum) per invoice. The pkgnum and locationnum
479 fields were arbitrarily set to those of the first line item subject to the
480 tax. This created problems if the tax contribution of each line item ever
481 needed to be determined (for example, when applying credits). For several
482 months in 2012, this was changed to create one record per tax definition
483 per I<package> per invoice, which was still not specific enough to identify
486 The current behavior is to create one record per tax definition per taxable
487 line item, and to store the billpkgnum of the taxed line item in the record.
488 The upgrade will try to convert existing records to the new format, but this
489 is not perfectly reliable.
493 L<FS::Record>, schema.html from the base documentation.