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_foreign_key('pkgnum', 'cust_pkg', 'pkgnum' )
126 || $self->ut_foreign_key('locationnum', 'cust_location', 'locationnum' )
127 || $self->ut_money('amount')
128 || $self->ut_foreign_key('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 a description for this tax line item constituent. Currently this
150 is the desc of the associated line item followed by the state/county/city
151 for the location in parentheses.
157 my $cust_location = $self->cust_location;
158 my $location = join('/', grep { $_ } # leave in?
159 map { $cust_location->$_ }
160 qw( state county city ) # country?
162 my $cust_bill_pkg_desc = $self->billpkgnum
163 ? $self->cust_bill_pkg->desc
164 : $self->cust_bill_pkg_desc;
165 "$cust_bill_pkg_desc ($location)";
170 Returns the amount owed (still outstanding) on this tax line item which is
171 the amount of this record minus all payment applications and credit
178 my $balance = $self->amount;
179 $balance -= $_->amount foreach ( $self->cust_bill_pay_pkg('setup') );
180 $balance -= $_->amount foreach ( $self->cust_credit_bill_pkg('setup') );
181 $balance = sprintf( '%.2f', $balance );
182 $balance =~ s/^\-0\.00$/0.00/; #yay ieee fp
186 sub cust_bill_pay_pkg {
188 qsearch( 'cust_bill_pay_pkg',
189 { map { $_ => $self->$_ } qw( billpkgtaxlocationnum billpkgnum ) }
193 sub cust_credit_bill_pkg {
195 qsearch( 'cust_credit_bill_pkg',
196 { map { $_ => $self->$_ } qw( billpkgtaxlocationnum billpkgnum ) }
200 sub cust_main_county {
202 return '' unless $self->taxtype eq 'FS::cust_main_county';
203 qsearchs( 'cust_main_county', { 'taxnum' => $self->taxnum } );
209 use Date::Parse 'str2time';
212 my $upgrade = 'tax_location_taxable_billpkgnum';
213 return if FS::upgrade_journal->is_done($upgrade);
214 my $job = FS::queue->new({ job =>
215 'FS::cust_bill_pkg_tax_location::upgrade_taxable_billpkgnum'
217 $job->insert($class, 's' => str2time('2012-01-01'));
218 FS::upgrade_journal->set_done($upgrade);
221 sub upgrade_taxable_billpkgnum {
222 # Associate these records to the correct taxable line items.
223 # The cust_bill_pkg upgrade now does this also for pre-3.0 records that
224 # aren't broken out by pkgnum, so we only need to deal with the case of
225 # multiple line items for the same pkgnum.
226 # Despite appearances, this has almost no relation to the upgrade in
229 my ($class, %opt) = @_;
231 my $oldAutoCommit = $FS::UID::AutoCommit;
232 local $FS::UID::AutoCommit = 0;
233 my $log = FS::Log->new('upgrade_taxable_billpkgnum');
237 $date_where .= " AND cust_bill._date >= $opt{s}";
240 $date_where .= " AND cust_bill._date < $opt{e}";
243 my @need_to_upgrade = qsearch({
244 select => 'cust_bill_pkg_tax_location.*',
245 table => 'cust_bill_pkg_tax_location',
246 hashref => { taxable_billpkgnum => '' },
247 addl_from => 'JOIN cust_bill_pkg USING (billpkgnum)'.
248 'JOIN cust_bill USING (invnum)',
249 extra_sql => $date_where,
251 $log->info('Starting upgrade of '.scalar(@need_to_upgrade).
252 ' cust_bill_pkg_tax_location records.');
254 # keys are billpkgnums
257 foreach (@need_to_upgrade) {
258 my $tax_billpkgnum = $_->billpkgnum;
259 $cust_bill_pkg{ $tax_billpkgnum } ||= FS::cust_bill_pkg->by_key($tax_billpkgnum);
260 $tax_location{ $tax_billpkgnum } ||= [];
261 push @{ $tax_location{ $tax_billpkgnum } }, $_;
264 TAX_ITEM: foreach my $tax_item (values %cust_bill_pkg) {
265 my $tax_locations = $tax_location{ $tax_item->billpkgnum };
266 my $invnum = $tax_item->invnum;
267 my $cust_bill = FS::cust_bill->by_key($tax_item->invnum);
268 my %tax_on_pkg; # keys are tax identifiers
269 TAX_LOCATION: foreach my $tax_location (@$tax_locations) {
270 # recapitulate the "cust_main_county $taxnum $pkgnum" tax identifier,
272 my $taxid = join(' ',
273 $tax_location->taxtype,
274 $tax_location->taxnum,
275 $tax_location->pkgnum,
276 $tax_location->locationnum
278 $tax_on_pkg{$taxid} ||= [];
279 push @{ $tax_on_pkg{$taxid} }, $tax_location;
281 PKGNUM: foreach my $taxid (keys %tax_on_pkg) {
282 my ($taxtype, $taxnum, $pkgnum, $locationnum) = split(' ', $taxid);
283 $log->info("tax#$taxnum, pkg#$pkgnum", object => $cust_bill);
284 my @pkg_items = $cust_bill->cust_bill_pkg_pkgnum($pkgnum);
286 # then how is there tax on it? should never happen
287 $log->error("no line items with pkg#$pkgnum", object => $cust_bill);
291 foreach my $pkg_item (@pkg_items) {
292 # find the taxable amount of each one
293 my $amount = $pkg_item->setup + $pkg_item->recur;
294 # subtract any exemptions that apply to this taxdef
295 foreach (qsearch('cust_tax_exempt_pkg', {
297 billpkgnum => $pkg_item->billpkgnum
300 $amount -= $_->amount;
302 $pkg_item->set('amount' => $pkg_item->setup + $pkg_item->recur);
303 $pkg_amount += $amount;
305 next PKGNUM if $pkg_amount == 0; # probably because it's fully exempted
306 # now sort them descending by taxable amount
307 @pkg_items = sort { $b->amount <=> $a->amount }
309 # and do the same with the tax links
310 # (there should be one per taxed item)
311 my @tax_links = sort { $b->amount <=> $a->amount }
312 @{ $tax_on_pkg{$taxid} };
314 if (scalar(@tax_links) == scalar(@pkg_items)) {
315 # the relatively simple case: they match 1:1
316 for my $i (0 .. scalar(@tax_links) - 1) {
317 $tax_links[$i]->set('taxable_billpkgnum',
318 $pkg_items[$i]->billpkgnum);
319 my $error = $tax_links[$i]->replace;
321 $log->error("failed to set taxable_billpkgnum in tax on pkg#$pkgnum",
322 object => $cust_bill);
327 # the more complicated case
328 $log->warn("mismatched charges and tax links in pkg#$pkgnum",
329 object => $cust_bill);
330 my $tax_amount = sum(map {$_->amount} @tax_links);
331 # remove all tax link records and recreate them to be 1:1 with
333 my (%billpaynum, %creditbillnum);
335 foreach my $tax_link (@tax_links) {
336 $link_type ||= ref($tax_link);
337 my $error = $tax_link->delete;
339 $log->error("error unlinking tax#$taxnum pkg#$pkgnum",
340 object => $cust_bill);
343 my $pkey = $tax_link->primary_key;
344 # also remove all applications that reference this tax link
345 # (they will be applications to the tax item)
346 my %hash = ($pkey => $tax_link->get($pkey));
347 foreach (qsearch('cust_bill_pay_pkg', \%hash)) {
348 $billpaynum{$_->billpaynum} += $_->amount;
349 my $error = $_->delete;
350 die "error unapplying payment: $error" if ( $error );
352 foreach (qsearch('cust_credit_bill_pkg', \%hash)) {
353 $creditbillnum{$_->creditbillnum} += $_->amount;
354 my $error = $_->delete;
355 die "error unapplying credit: $error" if ( $error );
359 my $cents_remaining = int(100 * $tax_amount);
360 foreach my $pkg_item (@pkg_items) {
361 my $cents = int(100 * $pkg_item->amount * $tax_amount / $pkg_amount);
362 my $tax_link = $link_type->new({
363 taxable_billpkgnum => $pkg_item->billpkgnum,
364 billpkgnum => $tax_item->billpkgnum,
368 locationnum => $locationnum,
371 push @tax_links, $tax_link;
372 $cents_remaining -= $cents;
374 my $nlinks = scalar @tax_links;
376 while ($cents_remaining) {
377 $tax_links[$i % $nlinks]->set('cents' =>
378 $tax_links[$i % $nlinks]->cents + 1
383 foreach my $tax_link (@tax_links) {
384 $tax_link->set('amount' => sprintf('%.2f', $tax_link->cents / 100));
385 my $error = $tax_link->insert;
387 $log->error("error relinking tax#$taxnum pkg#$pkgnum",
388 object => $cust_bill);
395 my $left = 0; # the amount "left" on the last tax link after
396 # applying payments, but before credits, so that
397 # it can receive both a payment and a credit if
399 # reapply payments/credits...this sucks
400 foreach my $billpaynum (keys %billpaynum) {
401 my $pay_amount = $billpaynum{$billpaynum};
402 while ($i < $nlinks and $pay_amount > 0) {
403 my $this_amount = min($pay_amount, $tax_links[$i]->amount);
404 $left = $tax_links[$i]->amount - $this_amount;
405 my $app = FS::cust_bill_pay_pkg->new({
406 billpaynum => $billpaynum,
407 billpkgnum => $tax_links[$i]->billpkgnum,
408 billpkgtaxlocationnum => $tax_links[$i]->billpkgtaxlocationnum,
409 amount => $this_amount,
410 setuprecur => 'setup',
411 # sdate/edate are null
413 my $error ||= $app->insert;
414 $pay_amount -= $this_amount;
418 foreach my $creditbillnum (keys %creditbillnum) {
419 my $credit_amount = $creditbillnum{$creditbillnum};
420 while ($i < $nlinks and $credit_amount > 0) {
421 my $this_amount = min($left, $credit_amount, $tax_links[$i]->amount);
422 $left = $credit_amount * 2; # just so it can't be selected twice
423 $i++ if $this_amount == $left
424 or $this_amount == $tax_links[$i]->amount;
425 my $app = FS::cust_credit_bill_pkg->new({
426 creditbillnum => $creditbillnum,
427 billpkgnum => $tax_links[$i]->billpkgnum,
428 billpkgtaxlocationnum => $tax_links[$i]->billpkgtaxlocationnum,
429 amount => $this_amount,
430 setuprecur => 'setup',
431 # sdate/edate are null
433 my $error ||= $app->insert;
434 $credit_amount -= $this_amount;
438 # we've just unapplied a bunch of stuff, so if it won't reapply
439 # we really need to revert the whole transaction
440 die "error reapplying payments/credits: $error; upgrade halted";
442 } # scalar(@tax_links) ?= scalar(@pkg_items)
446 $log->info('finish');
448 $dbh->commit if $oldAutoCommit;
458 The presence of FS::cust_main_county::delete makes the cust_main_county method
461 Pre-3.0 versions of Freeside would only create one cust_bill_pkg_tax_location
462 per tax definition (taxtype/taxnum) per invoice. The pkgnum and locationnum
463 fields were arbitrarily set to those of the first line item subject to the
464 tax. This created problems if the tax contribution of each line item ever
465 needed to be determined (for example, when applying credits). For several
466 months in 2012, this was changed to create one record per tax definition
467 per I<package> per invoice, which was still not specific enough to identify
470 The current behavior is to create one record per tax definition per taxable
471 line item, and to store the billpkgnum of the taxed line item in the record.
472 The upgrade will try to convert existing records to the new format, but this
473 is not perfectly reliable.
477 L<FS::Record>, schema.html from the base documentation.