1 package FS::cust_bill_pkg_tax_location;
2 use base qw( FS::Record );
5 use List::Util qw(sum min);
6 use FS::Record qw( dbh qsearch qsearchs );
9 use FS::cust_bill_pay_pkg;
10 use FS::cust_credit_bill_pkg;
11 use FS::cust_main_county;
16 FS::cust_bill_pkg_tax_location - Object methods for cust_bill_pkg_tax_location records
20 use FS::cust_bill_pkg_tax_location;
22 $record = new FS::cust_bill_pkg_tax_location \%hash;
23 $record = new FS::cust_bill_pkg_tax_location { 'column' => 'value' };
25 $error = $record->insert;
27 $error = $new_record->replace($old_record);
29 $error = $record->delete;
31 $error = $record->check;
35 An FS::cust_bill_pkg_tax_location object represents an record of taxation
36 based on package location. FS::cust_bill_pkg_tax_location inherits from
37 FS::Record. The following fields are currently supported:
41 =item billpkgtaxlocationnum
69 =item taxable_billpkgnum
71 The billpkgnum of the L<FS::cust_bill_pkg> that this tax was charged on.
72 It may specifically be on any portion of that line item (setup, recurring,
83 Creates a new record. To add the record to the database, see L<"insert">.
85 Note that this stores the hash reference, not a distinct copy of the hash it
86 points to. You can ask the object for a copy with the I<hash> method.
90 sub table { 'cust_bill_pkg_tax_location'; }
94 Adds this record to the database. If there is an error, returns the error,
95 otherwise returns false.
99 Delete this record from the database.
101 =item replace OLD_RECORD
103 Replaces the OLD_RECORD with this one in the database. If there is an error,
104 returns the error, otherwise returns false.
108 Checks all fields to make sure this is a valid record. If there is
109 an error, returns the error, otherwise returns false. Called by the insert
114 # the check method should currently be supplied - FS::Record contains some
115 # data checking routines
121 $self->ut_numbern('billpkgtaxlocationnum')
122 || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg', 'billpkgnum' )
123 || $self->ut_number('taxnum') #cust_bill_pkg/tax_rate key, based on taxtype
124 || $self->ut_enum('taxtype', [ qw( FS::cust_main_county FS::tax_rate ) ] )
125 || $self->ut_number('pkgnum', 'cust_pkg', 'pkgnum' )
126 || $self->ut_foreign_key('locationnum', 'cust_location', 'locationnum' )
127 || $self->ut_money('amount')
128 || $self->ut_foreign_keyn('taxable_billpkgnum', 'cust_bill_pkg', 'billpkgnum')
130 return $error if $error;
137 Returns the associated cust_bill_pkg object (i.e. the tax charge).
139 =item taxable_cust_bill_pkg
141 Returns the cust_bill_pkg object for the I<taxable> charge.
145 Returns the associated cust_location object
149 Returns the tax name (for populating the itemdesc field).
155 my $cust_main_county = FS::cust_main_county->by_key($self->taxnum)
157 $cust_main_county->taxname || 'Tax';
162 Returns a description for this tax line item constituent. Currently this
163 is the desc of the associated line item followed by the state/county/city
164 for the location in parentheses.
170 my $cust_location = $self->cust_location;
171 my $location = join('/', grep { $_ } # leave in?
172 map { $cust_location->$_ }
173 qw( state county city ) # country?
175 my $cust_bill_pkg_desc = $self->billpkgnum
176 ? $self->cust_bill_pkg->desc
177 : $self->cust_bill_pkg_desc;
178 "$cust_bill_pkg_desc ($location)";
183 Returns the amount owed (still outstanding) on this tax line item which is
184 the amount of this record minus all payment applications and credit
191 my $balance = $self->amount;
192 $balance -= $_->amount foreach ( $self->cust_bill_pay_pkg('setup') );
193 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg('setup') );
194 $balance = sprintf( '%.2f', $balance );
195 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
199 sub cust_bill_pay_pkg {
201 qsearch( 'cust_bill_pay_pkg',
202 { map { $_ => $self->$_ } qw( billpkgtaxlocationnum billpkgnum ) }
206 sub cust_credit_bill_pkg {
208 qsearch( 'cust_credit_bill_pkg',
209 { map { $_ => $self->$_ } qw( billpkgtaxlocationnum billpkgnum ) }
213 sub cust_main_county {
215 return '' unless $self->taxtype eq 'FS::cust_main_county';
216 qsearchs( 'cust_main_county', { 'taxnum' => $self->taxnum } );
222 use Date::Parse 'str2time';
225 my $upgrade = 'tax_location_taxable_billpkgnum';
226 return if FS::upgrade_journal->is_done($upgrade);
227 my $job = FS::queue->new({ job =>
228 'FS::cust_bill_pkg_tax_location::upgrade_taxable_billpkgnum'
230 $job->insert($class, 's' => str2time('2012-01-01'));
231 FS::upgrade_journal->set_done($upgrade);
234 sub upgrade_taxable_billpkgnum {
235 # Associate these records to the correct taxable line items.
236 # The cust_bill_pkg upgrade now does this also for pre-3.0 records that
237 # aren't broken out by pkgnum, so we only need to deal with the case of
238 # multiple line items for the same pkgnum.
239 # Despite appearances, this has almost no relation to the upgrade in
242 my ($class, %opt) = @_;
244 my $oldAutoCommit = $FS::UID::AutoCommit;
245 local $FS::UID::AutoCommit = 0;
246 my $log = FS::Log->new('upgrade_taxable_billpkgnum');
250 $date_where .= " AND cust_bill._date >= $opt{s}";
253 $date_where .= " AND cust_bill._date < $opt{e}";
256 my @need_to_upgrade = qsearch({
257 select => 'cust_bill_pkg_tax_location.*',
258 table => 'cust_bill_pkg_tax_location',
259 hashref => { taxable_billpkgnum => '' },
260 addl_from => 'JOIN cust_bill_pkg USING (billpkgnum)'.
261 'JOIN cust_bill USING (invnum)',
262 extra_sql => $date_where,
264 $log->info('Starting upgrade of '.scalar(@need_to_upgrade).
265 ' cust_bill_pkg_tax_location records.');
267 # keys are billpkgnums
270 foreach (@need_to_upgrade) {
271 my $tax_billpkgnum = $_->billpkgnum;
272 $cust_bill_pkg{ $tax_billpkgnum } ||= FS::cust_bill_pkg->by_key($tax_billpkgnum);
273 $tax_location{ $tax_billpkgnum } ||= [];
274 push @{ $tax_location{ $tax_billpkgnum } }, $_;
277 TAX_ITEM: foreach my $tax_item (values %cust_bill_pkg) {
278 my $tax_locations = $tax_location{ $tax_item->billpkgnum };
279 my $invnum = $tax_item->invnum;
280 my $cust_bill = FS::cust_bill->by_key($tax_item->invnum);
281 my %tax_on_pkg; # keys are tax identifiers
282 TAX_LOCATION: foreach my $tax_location (@$tax_locations) {
283 # recapitulate the "cust_main_county $taxnum $pkgnum" tax identifier,
285 my $taxid = join(' ',
286 $tax_location->taxtype,
287 $tax_location->taxnum,
288 $tax_location->pkgnum,
289 $tax_location->locationnum
291 $tax_on_pkg{$taxid} ||= [];
292 push @{ $tax_on_pkg{$taxid} }, $tax_location;
294 PKGNUM: foreach my $taxid (keys %tax_on_pkg) {
295 my ($taxtype, $taxnum, $pkgnum, $locationnum) = split(' ', $taxid);
296 $log->info("tax#$taxnum, pkg#$pkgnum", object => $cust_bill);
297 my @pkg_items = $cust_bill->cust_bill_pkg_pkgnum($pkgnum);
299 # then how is there tax on it? should never happen
300 $log->error("no line items with pkg#$pkgnum", object => $cust_bill);
304 foreach my $pkg_item (@pkg_items) {
305 # find the taxable amount of each one
306 my $amount = $pkg_item->setup + $pkg_item->recur;
307 # subtract any exemptions that apply to this taxdef
308 foreach (qsearch('cust_tax_exempt_pkg', {
310 billpkgnum => $pkg_item->billpkgnum
313 $amount -= $_->amount;
315 $pkg_item->set('amount' => $pkg_item->setup + $pkg_item->recur);
316 $pkg_amount += $amount;
318 next PKGNUM if $pkg_amount == 0; # probably because it's fully exempted
319 # now sort them descending by taxable amount
320 @pkg_items = sort { $b->amount <=> $a->amount }
322 # and do the same with the tax links
323 # (there should be one per taxed item)
324 my @tax_links = sort { $b->amount <=> $a->amount }
325 @{ $tax_on_pkg{$taxid} };
327 if (scalar(@tax_links) == scalar(@pkg_items)) {
328 # the relatively simple case: they match 1:1
329 for my $i (0 .. scalar(@tax_links) - 1) {
330 $tax_links[$i]->set('taxable_billpkgnum',
331 $pkg_items[$i]->billpkgnum);
332 my $error = $tax_links[$i]->replace;
334 $log->error("failed to set taxable_billpkgnum in tax on pkg#$pkgnum",
335 object => $cust_bill);
340 # the more complicated case
341 $log->warning("mismatched charges and tax links in pkg#$pkgnum",
342 object => $cust_bill);
343 my $tax_amount = sum(map {$_->amount} @tax_links);
344 # remove all tax link records and recreate them to be 1:1 with
346 my (%billpaynum, %creditbillnum);
348 foreach my $tax_link (@tax_links) {
349 $link_type ||= ref($tax_link);
350 my $error = $tax_link->delete;
352 $log->error("error unlinking tax#$taxnum pkg#$pkgnum",
353 object => $cust_bill);
356 my $pkey = $tax_link->primary_key;
357 # also remove all applications that reference this tax link
358 # (they will be applications to the tax item)
359 my %hash = ($pkey => $tax_link->get($pkey));
360 foreach (qsearch('cust_bill_pay_pkg', \%hash)) {
361 $billpaynum{$_->billpaynum} += $_->amount;
362 my $error = $_->delete;
363 die "error unapplying payment: $error" if ( $error );
365 foreach (qsearch('cust_credit_bill_pkg', \%hash)) {
366 $creditbillnum{$_->creditbillnum} += $_->amount;
367 my $error = $_->delete;
368 die "error unapplying credit: $error" if ( $error );
372 my $cents_remaining = int(100 * $tax_amount);
373 foreach my $pkg_item (@pkg_items) {
374 my $cents = int(100 * $pkg_item->amount * $tax_amount / $pkg_amount);
375 my $tax_link = $link_type->new({
376 taxable_billpkgnum => $pkg_item->billpkgnum,
377 billpkgnum => $tax_item->billpkgnum,
381 locationnum => $locationnum,
384 push @tax_links, $tax_link;
385 $cents_remaining -= $cents;
387 my $nlinks = scalar @tax_links;
389 while ($cents_remaining) {
390 $tax_links[$i % $nlinks]->set('cents' =>
391 $tax_links[$i % $nlinks]->cents + 1
396 foreach my $tax_link (@tax_links) {
397 $tax_link->set('amount' => sprintf('%.2f', $tax_link->cents / 100));
398 my $error = $tax_link->insert;
400 $log->error("error relinking tax#$taxnum pkg#$pkgnum",
401 object => $cust_bill);
408 my $left = 0; # the amount "left" on the last tax link after
409 # applying payments, but before credits, so that
410 # it can receive both a payment and a credit if
412 # reapply payments/credits...this sucks
413 foreach my $billpaynum (keys %billpaynum) {
414 my $pay_amount = $billpaynum{$billpaynum};
415 while ($i < $nlinks and $pay_amount > 0) {
416 my $this_amount = min($pay_amount, $tax_links[$i]->amount);
417 $left = $tax_links[$i]->amount - $this_amount;
418 my $app = FS::cust_bill_pay_pkg->new({
419 billpaynum => $billpaynum,
420 billpkgnum => $tax_links[$i]->billpkgnum,
421 billpkgtaxlocationnum => $tax_links[$i]->billpkgtaxlocationnum,
422 amount => $this_amount,
423 setuprecur => 'setup',
424 # sdate/edate are null
426 my $error ||= $app->insert;
427 $pay_amount -= $this_amount;
431 foreach my $creditbillnum (keys %creditbillnum) {
432 my $credit_amount = $creditbillnum{$creditbillnum};
433 while ($i < $nlinks and $credit_amount > 0) {
434 my $this_amount = min($left, $credit_amount, $tax_links[$i]->amount);
435 $left = $credit_amount * 2; # just so it can't be selected twice
436 $i++ if $this_amount == $left
437 or $this_amount == $tax_links[$i]->amount;
438 my $app = FS::cust_credit_bill_pkg->new({
439 creditbillnum => $creditbillnum,
440 billpkgnum => $tax_links[$i]->billpkgnum,
441 billpkgtaxlocationnum => $tax_links[$i]->billpkgtaxlocationnum,
442 amount => $this_amount,
443 setuprecur => 'setup',
444 # sdate/edate are null
446 my $error ||= $app->insert;
447 $credit_amount -= $this_amount;
451 # we've just unapplied a bunch of stuff, so if it won't reapply
452 # we really need to revert the whole transaction
453 die "error reapplying payments/credits: $error; upgrade halted";
455 } # scalar(@tax_links) ?= scalar(@pkg_items)
459 $log->info('finish');
461 $dbh->commit if $oldAutoCommit;
471 The presence of FS::cust_main_county::delete makes the cust_main_county method
474 Pre-3.0 versions of Freeside would only create one cust_bill_pkg_tax_location
475 per tax definition (taxtype/taxnum) per invoice. The pkgnum and locationnum
476 fields were arbitrarily set to those of the first line item subject to the
477 tax. This created problems if the tax contribution of each line item ever
478 needed to be determined (for example, when applying credits). For several
479 months in 2012, this was changed to create one record per tax definition
480 per I<package> per invoice, which was still not specific enough to identify
483 The current behavior is to create one record per tax definition per taxable
484 line item, and to store the billpkgnum of the taxed line item in the record.
485 The upgrade will try to convert existing records to the new format, but this
486 is not perfectly reliable.
490 L<FS::Record>, schema.html from the base documentation.