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 {
219 if ( $self->taxtype eq 'FS::cust_main_county' ) {
220 $result = qsearchs( 'cust_main_county', { 'taxnum' => $self->taxnum } );
227 use Date::Parse 'str2time';
230 my $upgrade = 'tax_location_taxable_billpkgnum';
231 return if FS::upgrade_journal->is_done($upgrade);
232 my $job = FS::queue->new({ job =>
233 'FS::cust_bill_pkg_tax_location::upgrade_taxable_billpkgnum'
235 $job->insert($class, 's' => str2time('2012-01-01'));
236 FS::upgrade_journal->set_done($upgrade);
239 sub upgrade_taxable_billpkgnum {
240 # Associate these records to the correct taxable line items.
241 # The cust_bill_pkg upgrade now does this also for pre-3.0 records that
242 # aren't broken out by pkgnum, so we only need to deal with the case of
243 # multiple line items for the same pkgnum.
244 # Despite appearances, this has almost no relation to the upgrade in
247 my ($class, %opt) = @_;
248 my $dbh = FS::UID::dbh();
249 my $oldAutoCommit = $FS::UID::AutoCommit;
250 local $FS::UID::AutoCommit = 0;
251 my $log = FS::Log->new('upgrade_taxable_billpkgnum');
255 $date_where .= " AND cust_bill._date >= $opt{s}";
258 $date_where .= " AND cust_bill._date < $opt{e}";
261 my @need_to_upgrade = qsearch({
262 select => 'cust_bill_pkg_tax_location.*',
263 table => 'cust_bill_pkg_tax_location',
264 hashref => { taxable_billpkgnum => '' },
265 addl_from => 'JOIN cust_bill_pkg USING (billpkgnum)'.
266 'JOIN cust_bill USING (invnum)',
267 extra_sql => $date_where,
269 $log->info('Starting upgrade of '.scalar(@need_to_upgrade).
270 ' cust_bill_pkg_tax_location records.');
272 # keys are billpkgnums
275 foreach (@need_to_upgrade) {
276 my $tax_billpkgnum = $_->billpkgnum;
277 $cust_bill_pkg{ $tax_billpkgnum } ||= FS::cust_bill_pkg->by_key($tax_billpkgnum);
278 $tax_location{ $tax_billpkgnum } ||= [];
279 push @{ $tax_location{ $tax_billpkgnum } }, $_;
282 TAX_ITEM: foreach my $tax_item (values %cust_bill_pkg) {
283 my $tax_locations = $tax_location{ $tax_item->billpkgnum };
284 my $invnum = $tax_item->invnum;
285 my $cust_bill = FS::cust_bill->by_key($tax_item->invnum);
286 my %tax_on_pkg; # keys are tax identifiers
287 TAX_LOCATION: foreach my $tax_location (@$tax_locations) {
288 # recapitulate the "cust_main_county $taxnum $pkgnum" tax identifier,
290 my $taxid = join(' ',
291 $tax_location->taxtype,
292 $tax_location->taxnum,
293 $tax_location->pkgnum,
294 $tax_location->locationnum
296 $tax_on_pkg{$taxid} ||= [];
297 push @{ $tax_on_pkg{$taxid} }, $tax_location;
299 PKGNUM: foreach my $taxid (keys %tax_on_pkg) {
300 my ($taxtype, $taxnum, $pkgnum, $locationnum) = split(' ', $taxid);
301 $log->info("tax#$taxnum, pkg#$pkgnum", object => $cust_bill);
302 my @pkg_items = $cust_bill->cust_bill_pkg_pkgnum($pkgnum);
304 # then how is there tax on it? should never happen
305 $log->error("no line items with pkg#$pkgnum", object => $cust_bill);
309 foreach my $pkg_item (@pkg_items) {
310 # find the taxable amount of each one
311 my $amount = $pkg_item->setup + $pkg_item->recur;
312 # subtract any exemptions that apply to this taxdef
313 foreach (qsearch('cust_tax_exempt_pkg', {
315 billpkgnum => $pkg_item->billpkgnum
318 $amount -= $_->amount;
320 $pkg_item->set('amount' => $pkg_item->setup + $pkg_item->recur);
321 $pkg_amount += $amount;
323 next PKGNUM if $pkg_amount == 0; # probably because it's fully exempted
324 # now sort them descending by taxable amount
325 @pkg_items = sort { $b->amount <=> $a->amount }
327 # and do the same with the tax links
328 # (there should be one per taxed item)
329 my @tax_links = sort { $b->amount <=> $a->amount }
330 @{ $tax_on_pkg{$taxid} };
332 if (scalar(@tax_links) == scalar(@pkg_items)) {
333 # the relatively simple case: they match 1:1
334 for my $i (0 .. scalar(@tax_links) - 1) {
335 $tax_links[$i]->set('taxable_billpkgnum',
336 $pkg_items[$i]->billpkgnum);
337 my $error = $tax_links[$i]->replace;
339 $log->error("failed to set taxable_billpkgnum in tax on pkg#$pkgnum",
340 object => $cust_bill);
345 # the more complicated case
346 $log->warn("mismatched charges and tax links in pkg#$pkgnum",
347 object => $cust_bill);
348 my $tax_amount = sum(map {$_->amount} @tax_links);
349 # remove all tax link records and recreate them to be 1:1 with
351 my (%billpaynum, %creditbillnum);
353 foreach my $tax_link (@tax_links) {
354 $link_type ||= ref($tax_link);
355 my $error = $tax_link->delete;
357 $log->error("error unlinking tax#$taxnum pkg#$pkgnum",
358 object => $cust_bill);
361 my $pkey = $tax_link->primary_key;
362 # also remove all applications that reference this tax link
363 # (they will be applications to the tax item)
364 my %hash = ($pkey => $tax_link->get($pkey));
365 foreach (qsearch('cust_bill_pay_pkg', \%hash)) {
366 $billpaynum{$_->billpaynum} += $_->amount;
367 my $error = $_->delete;
368 die "error unapplying payment: $error" if ( $error );
370 foreach (qsearch('cust_credit_bill_pkg', \%hash)) {
371 $creditbillnum{$_->creditbillnum} += $_->amount;
372 my $error = $_->delete;
373 die "error unapplying credit: $error" if ( $error );
377 my $cents_remaining = int(100 * $tax_amount);
378 foreach my $pkg_item (@pkg_items) {
379 my $cents = int(100 * $pkg_item->amount * $tax_amount / $pkg_amount);
380 my $tax_link = $link_type->new({
381 taxable_billpkgnum => $pkg_item->billpkgnum,
382 billpkgnum => $tax_item->billpkgnum,
386 locationnum => $locationnum,
389 push @tax_links, $tax_link;
390 $cents_remaining -= $cents;
392 my $nlinks = scalar @tax_links;
394 while ($cents_remaining) {
395 $tax_links[$i % $nlinks]->set('cents' =>
396 $tax_links[$i % $nlinks]->cents + 1
401 foreach my $tax_link (@tax_links) {
402 $tax_link->set('amount' => sprintf('%.2f', $tax_link->cents / 100));
403 my $error = $tax_link->insert;
405 $log->error("error relinking tax#$taxnum pkg#$pkgnum",
406 object => $cust_bill);
413 my $left = 0; # the amount "left" on the last tax link after
414 # applying payments, but before credits, so that
415 # it can receive both a payment and a credit if
417 # reapply payments/credits...this sucks
418 foreach my $billpaynum (keys %billpaynum) {
419 my $pay_amount = $billpaynum{$billpaynum};
420 while ($i < $nlinks and $pay_amount > 0) {
421 my $this_amount = min($pay_amount, $tax_links[$i]->amount);
422 $left = $tax_links[$i]->amount - $this_amount;
423 my $app = FS::cust_bill_pay_pkg->new({
424 billpaynum => $billpaynum,
425 billpkgnum => $tax_links[$i]->billpkgnum,
426 billpkgtaxlocationnum => $tax_links[$i]->billpkgtaxlocationnum,
427 amount => $this_amount,
428 setuprecur => 'setup',
429 # sdate/edate are null
431 my $error ||= $app->insert;
432 $pay_amount -= $this_amount;
436 foreach my $creditbillnum (keys %creditbillnum) {
437 my $credit_amount = $creditbillnum{$creditbillnum};
438 while ($i < $nlinks and $credit_amount > 0) {
439 my $this_amount = min($left, $credit_amount, $tax_links[$i]->amount);
440 $left = $credit_amount * 2; # just so it can't be selected twice
441 $i++ if $this_amount == $left
442 or $this_amount == $tax_links[$i]->amount;
443 my $app = FS::cust_credit_bill_pkg->new({
444 creditbillnum => $creditbillnum,
445 billpkgnum => $tax_links[$i]->billpkgnum,
446 billpkgtaxlocationnum => $tax_links[$i]->billpkgtaxlocationnum,
447 amount => $this_amount,
448 setuprecur => 'setup',
449 # sdate/edate are null
451 my $error ||= $app->insert;
452 $credit_amount -= $this_amount;
456 # we've just unapplied a bunch of stuff, so if it won't reapply
457 # we really need to revert the whole transaction
458 die "error reapplying payments/credits: $error; upgrade halted";
460 } # scalar(@tax_links) ?= scalar(@pkg_items)
464 $log->info('finish');
466 $dbh->commit if $oldAutoCommit;
476 The presence of FS::cust_main_county::delete makes the cust_main_county method
479 Pre-3.0 versions of Freeside would only create one cust_bill_pkg_tax_location
480 per tax definition (taxtype/taxnum) per invoice. The pkgnum and locationnum
481 fields were arbitrarily set to those of the first line item subject to the
482 tax. This created problems if the tax contribution of each line item ever
483 needed to be determined (for example, when applying credits). For several
484 months in 2012, this was changed to create one record per tax definition
485 per I<package> per invoice, which was still not specific enough to identify
488 The current behavior is to create one record per tax definition per taxable
489 line item, and to store the billpkgnum of the taxed line item in the record.
490 The upgrade will try to convert existing records to the new format, but this
491 is not perfectly reliable.
495 L<FS::Record>, schema.html from the base documentation.