1 package FS::cust_main_county;
4 use vars qw( @ISA @EXPORT_OK $conf
5 @cust_main_county %cust_main_county $countyflag ); # $cityflag );
7 use FS::Record qw( qsearch qsearchs dbh );
12 use FS::cust_tax_exempt;
13 use FS::cust_tax_exempt_pkg;
14 use FS::upgrade_journal;
16 @ISA = qw( FS::Record );
17 @EXPORT_OK = qw( regionselector );
19 @cust_main_county = ();
23 #ask FS::UID to run this stuff for us later
24 $FS::UID::callback{'FS::cust_main_county'} = sub {
30 FS::cust_main_county - Object methods for cust_main_county objects
34 use FS::cust_main_county;
36 $record = new FS::cust_main_county \%hash;
37 $record = new FS::cust_main_county { 'column' => 'value' };
39 $error = $record->insert;
41 $error = $new_record->replace($old_record);
43 $error = $record->delete;
45 $error = $record->check;
47 ($county_html, $state_html, $country_html) =
48 FS::cust_main_county::regionselector( $county, $state, $country );
52 An FS::cust_main_county object represents a tax rate, defined by locale.
53 FS::cust_main_county inherits from FS::Record. The following fields are
58 =item taxnum - primary key (assigned automatically for new tax rates)
60 =item district - tax district (optional)
70 =item tax - percentage
76 =item taxname - if defined, printed on invoices instead of "Tax"
78 =item setuptax - if 'Y', this tax does not apply to setup fees
80 =item recurtax - if 'Y', this tax does not apply to recurring fees
82 =item source - the tax lookup method that created this tax record. For records
83 created manually, this will be null.
93 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
97 sub table { 'cust_main_county'; }
101 Adds this tax rate to the database. If there is an error, returns the error,
102 otherwise returns false.
106 Deletes this tax rate from the database. If there is an error, returns the
107 error, otherwise returns false.
109 =item replace OLD_RECORD
111 Replaces the OLD_RECORD with this one in the database. If there is an error,
112 returns the error, otherwise returns false.
116 Checks all fields to make sure this is a valid tax rate. If there is an error,
117 returns the error, otherwise returns false. Called by the insert and replace
125 $self->trim_whitespace(qw(district city county state country));
126 $self->set('city', uc($self->get('city'))); # also county?
128 $self->exempt_amount(0) unless $self->exempt_amount;
130 $self->ut_numbern('taxnum')
131 || $self->ut_alphan('district')
132 || $self->ut_textn('city')
133 || $self->ut_textn('county')
134 || $self->ut_anything('state')
135 || $self->ut_text('country')
136 || $self->ut_float('tax')
137 || $self->ut_textn('taxclass') # ...
138 || $self->ut_money('exempt_amount')
139 || $self->ut_textn('taxname')
140 || $self->ut_enum('setuptax', [ '', 'Y' ] )
141 || $self->ut_enum('recurtax', [ '', 'Y' ] )
142 || $self->ut_textn('source')
143 || $self->SUPER::check
150 Returns a label looking like "Anytown, Alameda County, CA, US".
152 If the taxname field is set, it will look like
153 "CA Sales Tax (Anytown, Alameda County, CA, US)".
155 If the taxclass is set, then it will be
156 "Anytown, Alameda County, CA, US (International)".
158 OPTIONS may contain "with_taxclass", "with_city", and "with_district" to show
159 those fields. It may also contain "out", in which case, if this region
160 (district+city+county+state+country) contains no non-zero taxes, the label
161 will read "Out of taxable region(s)".
166 my ($self, %opt) = @_;
169 and !defined(qsearchs('cust_main_county', {
170 'district' => $self->district,
171 'city' => $self->city,
172 'county' => $self->county,
173 'state' => $self->state,
174 'country' => $self->country,
175 'tax' => { op => '>', value => 0 },
178 return 'Out of taxable region(s)';
180 my $label = $self->country;
181 $label = $self->state.", $label" if $self->state;
182 $label = $self->county." County, $label" if $self->county;
183 if ($opt{with_city}) {
184 $label = $self->city.", $label" if $self->city;
185 if ($opt{with_district} and $self->district) {
186 $label = $self->district . ", $label";
189 # ugly labels when taxclass and taxname are both non-null...
190 # but this is how the tax report does it
191 if ($opt{with_taxclass}) {
192 $label = "$label (".$self->taxclass.')' if $self->taxclass;
194 $label = $self->taxname." ($label)" if $self->taxname;
199 =item sql_taxclass_sameregion
201 Returns an SQL WHERE fragment or the empty string to search for entries
202 with different tax classes.
206 #hmm, description above could be better...
208 sub sql_taxclass_sameregion {
211 my $same_query = 'SELECT DISTINCT taxclass FROM cust_main_county '.
212 ' WHERE taxnum != ? AND country = ?';
213 my @same_param = ( 'taxnum', 'country' );
214 foreach my $opt_field (qw( state county )) {
215 if ( $self->$opt_field() ) {
216 $same_query .= " AND $opt_field = ?";
217 push @same_param, $opt_field;
219 $same_query .= " AND $opt_field IS NULL";
223 my @taxclasses = $self->_list_sql( \@same_param, $same_query );
225 return '' unless scalar(@taxclasses);
227 '( taxclass IS NULL OR ( '. #only if !$self->taxclass ??
228 join(' AND ', map { 'taxclass != '.dbh->quote($_) } @taxclasses ).
233 my( $self, $param, $sql ) = @_;
234 my $sth = dbh->prepare($sql) or die dbh->errstr;
235 $sth->execute( map $self->$_(), @$param )
236 or die "Unexpected error executing statement $sql: ". $sth->errstr;
237 map $_->[0], @{ $sth->fetchall_arrayref };
240 =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
242 Takes an arrayref of L<FS::cust_bill_pkg> objects representing taxable
243 line items, and returns a new L<FS::cust_bill_pkg> object representing
244 the tax on them under this tax rate.
246 This will have a pseudo-field, "cust_bill_pkg_tax_location", containing
247 an arrayref of L<FS::cust_bill_pkg_tax_location> objects. Each of these
248 will in turn have a "taxable_cust_bill_pkg" pseudo-field linking it to one
249 of the taxable items. All of these links must be resolved as the objects
252 Options may include 'custnum' and 'invoice_time' in case the cust_bill_pkg
253 objects belong to an invoice that hasn't been inserted yet.
255 Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg>
256 objects belonging to the same customer, to be counted against the monthly
257 tax exemption limit if there is one.
261 # XXX change tax_rate.pm to work like this
264 my( $self, $taxables, %opt ) = @_;
265 $taxables = [ $taxables ] unless ref($taxables) eq 'ARRAY';
266 # remove any charge class identifiers; they're not supported here
267 @$taxables = grep { ref $_ } @$taxables;
269 return 'taxline called with no line items' unless @$taxables;
271 local $SIG{HUP} = 'IGNORE';
272 local $SIG{INT} = 'IGNORE';
273 local $SIG{QUIT} = 'IGNORE';
274 local $SIG{TERM} = 'IGNORE';
275 local $SIG{TSTP} = 'IGNORE';
276 local $SIG{PIPE} = 'IGNORE';
278 my $oldAutoCommit = $FS::UID::AutoCommit;
279 local $FS::UID::AutoCommit = 0;
282 my $name = $self->taxname || 'Tax';
283 my $taxable_total = 0;
286 my $round_per_line_item = $conf->exists('tax-round_per_line_item');
288 my $cust_bill = $taxables->[0]->cust_bill;
289 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
290 my $invoice_time = $cust_bill ? $cust_bill->_date : $opt{'invoice_time'};
291 my $cust_main = FS::cust_main->by_key($custnum) if $custnum > 0;
292 # (to avoid complications with estimated tax on quotations, assume it's
293 # taxable if there is no customer)
295 #die "unable to calculate taxes for an unknown customer\n";
298 # Gather any exemptions that are already attached to these cust_bill_pkgs
299 # so that we can deduct them from the customer's monthly limit.
300 my @existing_exemptions = @{ $opt{'exemptions'} };
301 push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
304 my $tax_item = FS::cust_bill_pkg->new({
313 foreach my $cust_bill_pkg (@$taxables) {
314 # careful... may be a cust_bill_pkg or a quotation_pkg
316 my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur;
317 foreach ( grep { $_->taxnum == $self->taxnum }
318 @{ $cust_bill_pkg->cust_tax_exempt_pkg }
320 # deal with exemptions that have been set on this line item, and
321 # pertain to this tax def
322 $taxable_charged -= $_->amount;
325 # can't determine the tax_locationnum directly for fees; they're not
326 # yet linked to an invoice
327 my $locationnum = $cust_bill_pkg->tax_locationnum
328 || $cust_main->ship_locationnum;
330 ### Monthly capped exemptions ###
331 if ( $self->exempt_amount && $self->exempt_amount > 0
332 and $taxable_charged > 0
335 # XXX monthly exemptions currently don't work on quotations
337 # If the billing period extends across multiple calendar months,
338 # there may be several months of exemption available.
339 my $sdate = $cust_bill_pkg->sdate || $invoice_time;
340 my $start_month = (localtime($sdate))[4] + 1;
341 my $start_year = (localtime($sdate))[5] + 1900;
342 my $edate = $cust_bill_pkg->edate || $invoice_time;
343 my $end_month = (localtime($edate))[4] + 1;
344 my $end_year = (localtime($edate))[5] + 1900;
346 # If the partial last month + partial first month <= one month,
347 # don't use the exemption in the last month
348 # (unless the last month is also the first month, e.g. one-time
350 if ( (localtime($sdate))[3] >= (localtime($edate))[3]
351 and ($start_month != $end_month or $start_year != $end_year)
354 if ( $end_month == 0 ) {
360 # number of months of exemption available
361 my $freq = ($end_month - $start_month) +
362 ($end_year - $start_year) * 12 +
365 # divide equally among all of them
366 my $permonth = sprintf('%.2f', $taxable_charged / $freq);
368 #call the whole thing off if this customer has any old
369 #exemption records...
370 my @cust_tax_exempt =
371 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
372 if ( @cust_tax_exempt ) {
373 $dbh->rollback if $oldAutoCommit;
375 'this customer still has old-style tax exemption records; '.
376 'run bin/fs-migrate-cust_tax_exempt?';
379 my ($mon, $year) = ($start_month, $start_year);
380 while ($taxable_charged > 0.005 and
381 ($year < $end_year or
382 ($year == $end_year and $mon <= $end_month)
386 # find the sum of the exemption used by this customer, for this tax,
390 FROM cust_tax_exempt_pkg
391 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
392 LEFT JOIN cust_bill USING ( invnum )
397 AND exempt_monthly = 'Y'
399 my $sth = dbh->prepare($sql) or do {
400 $dbh->rollback if $oldAutoCommit;
401 return "fatal: can't lookup existing exemption: ". dbh->errstr;
409 $dbh->rollback if $oldAutoCommit;
410 return "fatal: can't lookup existing exemption: ". dbh->errstr;
412 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
414 # add any exemption we're already using for another line item
415 foreach ( grep { $_->taxnum == $self->taxnum &&
416 $_->exempt_monthly eq 'Y' &&
419 } @existing_exemptions
422 $existing_exemption += $_->amount;
425 my $remaining_exemption =
426 $self->exempt_amount - $existing_exemption;
427 if ( $remaining_exemption > 0 ) {
428 my $addl = $remaining_exemption > $permonth
430 : $remaining_exemption;
431 $addl = $taxable_charged if $addl > $taxable_charged;
434 FS::cust_tax_exempt_pkg->new({
435 amount => sprintf('%.2f', $addl),
436 exempt_monthly => 'Y',
439 taxnum => $self->taxnum,
440 taxtype => ref($self)
442 $taxable_charged -= $addl;
444 # create a record of it
445 push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, $new_exemption;
446 # and allow it to be counted against the limit for other packages
447 push @existing_exemptions, $new_exemption;
449 # if they're using multiple months of exemption for a multi-month
450 # package, then record the exemptions in separate months
458 } # if exempt_amount and $cust_main
460 $taxable_charged = sprintf( "%.2f", $taxable_charged);
461 next if $taxable_charged == 0;
463 my $this_tax_cents = $taxable_charged * $self->tax;
464 if ( $round_per_line_item ) {
465 # Round the tax to the nearest cent for each line item, instead of
466 # across the whole invoice.
467 $this_tax_cents = sprintf('%.0f', $this_tax_cents);
469 # Otherwise truncate it so that rounding error is always positive.
470 $this_tax_cents = int($this_tax_cents);
473 my $location = FS::cust_bill_pkg_tax_location->new({
474 'taxnum' => $self->taxnum,
475 'taxtype' => ref($self),
476 'cents' => $this_tax_cents,
477 'pkgnum' => $cust_bill_pkg->pkgnum,
478 'locationnum' => $locationnum,
479 'taxable_cust_bill_pkg' => $cust_bill_pkg,
480 'tax_cust_bill_pkg' => $tax_item,
482 push @tax_location, $location;
484 $taxable_total += $taxable_charged;
485 $tax_cents += $this_tax_cents;
486 } #foreach $cust_bill_pkg
489 # calculate tax and rounding error for the whole group: total taxable
490 # amount times tax rate (as cents per dollar), minus the tax already
492 # and force 0.5 to round up
493 my $extra_cents = sprintf('%.0f',
494 ($taxable_total * $self->tax) - $tax_cents + 0.00000001
497 # if we're rounding per item, then ignore that and don't distribute any
499 if ( $round_per_line_item ) {
503 if ( $extra_cents < 0 ) {
504 die "nonsense extra_cents value $extra_cents";
506 $tax_cents += $extra_cents;
508 foreach (@tax_location) { # can never require more than a single pass, yes?
509 my $cents = $_->get('cents');
510 if ( $extra_cents > 0 ) {
514 $_->set('amount', sprintf('%.2f', $cents/100));
516 $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100));
517 $tax_item->set('cust_bill_pkg_tax_location', \@tax_location);
528 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
533 my ( $selected_county, $selected_state, $selected_country,
534 $prefix, $onchange, $disabled ) = @_;
536 $prefix = '' unless defined $prefix;
540 # unless ( @cust_main_county ) { #cache
541 @cust_main_county = qsearch('cust_main_county', {} );
542 foreach my $c ( @cust_main_county ) {
543 $countyflag=1 if $c->county;
544 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
545 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
548 $countyflag=1 if $selected_county;
550 my $script_html = <<END;
552 function opt(what,value,text) {
553 var optionName = new Option(text, value, false, false);
554 var length = what.length;
555 what.options[length] = optionName;
557 function ${prefix}country_changed(what) {
558 country = what.options[what.selectedIndex].text;
559 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
560 what.form.${prefix}state.options[i] = null;
562 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
564 foreach my $country ( sort keys %cust_main_county ) {
565 $script_html .= "\nif ( country == \"$country\" ) {\n";
566 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
567 ( my $dstate = $state ) =~ s/[\n\r]//g;
568 my $text = $dstate || '(n/a)';
569 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
571 $script_html .= "}\n";
574 $script_html .= <<END;
576 function ${prefix}state_changed(what) {
580 $script_html .= <<END;
581 state = what.options[what.selectedIndex].text;
582 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
583 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
584 what.form.${prefix}county.options[i] = null;
587 foreach my $country ( sort keys %cust_main_county ) {
588 $script_html .= "\nif ( country == \"$country\" ) {\n";
589 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
590 $script_html .= "\nif ( state == \"$state\" ) {\n";
591 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
592 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
593 my $text = $county || '(n/a)';
595 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
597 $script_html .= "}\n";
599 $script_html .= "}\n";
603 $script_html .= <<END;
608 my $county_html = $script_html;
610 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
611 $county_html .= '</SELECT>';
614 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
617 my $state_html = qq!<SELECT NAME="${prefix}state" !.
618 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
619 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
620 my $text = $state || '(n/a)';
621 my $selected = $state eq $selected_state ? 'SELECTED' : '';
622 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
624 $state_html .= '</SELECT>';
626 $state_html .= '</SELECT>';
628 my $country_html = qq!<SELECT NAME="${prefix}country" !.
629 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
630 my $countrydefault = $conf->config('countrydefault') || 'US';
631 foreach my $country (
632 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
633 keys %cust_main_county
635 my $selected = $country eq $selected_country ? ' SELECTED' : '';
636 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
638 $country_html .= '</SELECT>';
640 ($county_html, $state_html, $country_html);
646 # assume taxes in Washington with district numbers, and null name, or
647 # named 'sales tax', are looked up via the wa_sales method. mark them.
648 my $journal = 'cust_main_county__source_wa_sales';
649 if (!FS::upgrade_journal->is_done($journal)) {
650 my @taxes = qsearch({
651 'table' => 'cust_main_county',
652 'extra_sql' => " WHERE tax > 0 AND country = 'US' AND state = 'WA'".
653 " AND district IS NOT NULL AND ( taxname IS NULL OR ".
654 " taxname ~* 'sales tax' )",
657 warn "Flagging Washington state sales taxes: ".scalar(@taxes)." records.\n";
659 $_->set('source', 'wa_sales');
660 my $error = $_->replace;
661 die $error if $error;
664 FS::upgrade_journal->set_done($journal);
666 # trim whitespace and convert to uppercase in the 'city' field.
667 foreach my $record (qsearch({
668 table => 'cust_main_county',
669 extra_sql => " WHERE city LIKE ' %' OR city LIKE '% ' OR city != UPPER(city)",
671 # any with-trailing-space records probably duplicate other records
672 # from the same city, and if we just fix the record in place, we'll
673 # create an exact duplicate.
674 # so find the record this one would duplicate, and merge them.
675 $record->check; # trims whitespace
676 my %match = map { $_ => $record->get($_) }
677 qw(city county state country district taxname taxclass);
678 my $other = qsearchs('cust_main_county', \%match);
680 my $new_taxnum = $other->taxnum;
681 my $old_taxnum = $record->taxnum;
682 if ($other->tax != $record->tax or
683 $other->exempt_amount != $record->exempt_amount) {
684 # don't assume these are the same.
685 warn "Found duplicate taxes (#$new_taxnum and #$old_taxnum) but they have different rates and can't be merged.\n";
687 warn "Merging tax #$old_taxnum into #$new_taxnum\n";
688 foreach my $table (qw(
689 cust_bill_pkg_tax_location
690 cust_bill_pkg_tax_location_void
692 cust_tax_exempt_pkg_void
694 foreach my $row (qsearch($table, { 'taxnum' => $old_taxnum })) {
695 $row->set('taxnum' => $new_taxnum);
696 my $error = $row->replace;
697 die $error if $error;
700 my $error = $record->delete;
701 die $error if $error;
704 # else there is no record this one duplicates, so just fix it
705 my $error = $record->replace;
706 die $error if $error;
716 regionselector? putting web ui components in here? they should probably live
721 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base