1 package FS::cust_main_county;
2 use base qw( FS::Record );
5 use vars qw( @EXPORT_OK $conf
6 @cust_main_county %cust_main_county $countyflag ); # $cityflag );
9 use FS::Record qw( qsearch qsearchs dbh );
10 use FS::cust_bill_pkg;
14 use FS::cust_tax_exempt;
15 use FS::cust_tax_exempt_pkg;
17 use FS::upgrade_journal;
19 @EXPORT_OK = qw( regionselector );
21 @cust_main_county = ();
25 #ask FS::UID to run this stuff for us later
26 $FS::UID::callback{'FS::cust_main_county'} = sub {
32 FS::cust_main_county - Object methods for cust_main_county objects
36 use FS::cust_main_county;
38 $record = new FS::cust_main_county \%hash;
39 $record = new FS::cust_main_county { 'column' => 'value' };
41 $error = $record->insert;
43 $error = $new_record->replace($old_record);
45 $error = $record->delete;
47 $error = $record->check;
49 ($county_html, $state_html, $country_html) =
50 FS::cust_main_county::regionselector( $county, $state, $country );
54 An FS::cust_main_county object represents a tax rate, defined by locale.
55 FS::cust_main_county inherits from FS::Record. The following fields are
60 =item taxnum - primary key (assigned automatically for new tax rates)
62 =item district - tax district (optional)
72 =item tax - percentage
78 =item taxname - if defined, printed on invoices instead of "Tax"
80 =item setuptax - if 'Y', this tax does not apply to setup fees
82 =item recurtax - if 'Y', this tax does not apply to recurring fees
84 =item source - the tax lookup method that created this tax record. For records
85 created manually, this will be null.
95 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
99 sub table { 'cust_main_county'; }
103 Adds this tax rate to the database. If there is an error, returns the error,
104 otherwise returns false.
108 Deletes this tax rate from the database. If there is an error, returns the
109 error, otherwise returns false.
111 =item replace OLD_RECORD
113 Replaces the OLD_RECORD with this one in the database. If there is an error,
114 returns the error, otherwise returns false.
118 Checks all fields to make sure this is a valid tax rate. If there is an error,
119 returns the error, otherwise returns false. Called by the insert and replace
127 $self->trim_whitespace(qw(district city county state country));
128 $self->set('city', uc($self->get('city'))); # also county?
130 $self->exempt_amount(0) unless $self->exempt_amount;
132 $self->ut_numbern('taxnum')
133 || $self->ut_alphan('district')
134 || $self->ut_textn('city')
135 || $self->ut_textn('county')
136 || $self->ut_anything('state')
137 || $self->ut_text('country')
138 || $self->ut_float('tax')
139 || $self->ut_textn('taxclass') # ...
140 || $self->ut_money('exempt_amount')
141 || $self->ut_textn('taxname')
142 || $self->ut_enum('setuptax', [ '', 'Y' ] )
143 || $self->ut_enum('recurtax', [ '', 'Y' ] )
144 || $self->ut_textn('source')
145 || $self->SUPER::check
152 Returns a label looking like "Anytown, Alameda County, CA, US".
154 If the taxname field is set, it will look like
155 "CA Sales Tax (Anytown, Alameda County, CA, US)".
157 If the taxclass is set, then it will be
158 "Anytown, Alameda County, CA, US (International)".
160 OPTIONS may contain "with_taxclass", "with_city", and "with_district" to show
161 those fields. It may also contain "out", in which case, if this region
162 (district+city+county+state+country) contains no non-zero taxes, the label
163 will read "Out of taxable region(s)".
168 my ($self, %opt) = @_;
171 and !defined(qsearchs('cust_main_county', {
172 'district' => $self->district,
173 'city' => $self->city,
174 'county' => $self->county,
175 'state' => $self->state,
176 'country' => $self->country,
177 'tax' => { op => '>', value => 0 },
180 return 'Out of taxable region(s)';
182 my $label = $self->country;
183 $label = $self->state.", $label" if $self->state;
184 $label = $self->county." County, $label" if $self->county;
185 if ($opt{with_city}) {
186 $label = $self->city.", $label" if $self->city;
187 if ($opt{with_district} and $self->district) {
188 $label = $self->district . ", $label";
191 # ugly labels when taxclass and taxname are both non-null...
192 # but this is how the tax report does it
193 if ($opt{with_taxclass}) {
194 $label = "$label (".$self->taxclass.')' if $self->taxclass;
196 $label = $self->taxname." ($label)" if $self->taxname;
201 =item sql_taxclass_sameregion
203 Returns an SQL WHERE fragment or the empty string to search for entries
204 with different tax classes.
208 #hmm, description above could be better...
210 sub sql_taxclass_sameregion {
213 my $same_query = 'SELECT DISTINCT taxclass FROM cust_main_county '.
214 ' WHERE taxnum != ? AND country = ?';
215 my @same_param = ( 'taxnum', 'country' );
216 foreach my $opt_field (qw( state county )) {
217 if ( $self->$opt_field() ) {
218 $same_query .= " AND $opt_field = ?";
219 push @same_param, $opt_field;
221 $same_query .= " AND $opt_field IS NULL";
225 my @taxclasses = $self->_list_sql( \@same_param, $same_query );
227 return '' unless scalar(@taxclasses);
229 '( taxclass IS NULL OR ( '. #only if !$self->taxclass ??
230 join(' AND ', map { 'taxclass != '.dbh->quote($_) } @taxclasses ).
235 my( $self, $param, $sql ) = @_;
236 my $sth = dbh->prepare($sql) or die dbh->errstr;
237 $sth->execute( map $self->$_(), @$param )
238 or die "Unexpected error executing statement $sql: ". $sth->errstr;
239 map $_->[0], @{ $sth->fetchall_arrayref };
242 =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
244 Takes an arrayref of L<FS::cust_bill_pkg> objects representing taxable
245 line items, and returns a new L<FS::cust_bill_pkg> object representing
246 the tax on them under this tax rate.
248 This will have a pseudo-field, "cust_bill_pkg_tax_location", containing
249 an arrayref of L<FS::cust_bill_pkg_tax_location> objects. Each of these
250 will in turn have a "taxable_cust_bill_pkg" pseudo-field linking it to one
251 of the taxable items. All of these links must be resolved as the objects
254 In addition to calculating the tax for the line items, this will calculate
255 any appropriate tax exemptions and attach them to the line items.
257 Options may include 'custnum' and 'invoice_time' in case the cust_bill_pkg
258 objects belong to an invoice that hasn't been inserted yet.
260 Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg>
261 objects belonging to the same customer, to be counted against the monthly
262 tax exemption limit if there is one.
266 # XXX change tax_rate.pm to work like this
269 my( $self, $taxables, %opt ) = @_;
270 return 'taxline called with no line items' unless @$taxables;
272 local $SIG{HUP} = 'IGNORE';
273 local $SIG{INT} = 'IGNORE';
274 local $SIG{QUIT} = 'IGNORE';
275 local $SIG{TERM} = 'IGNORE';
276 local $SIG{TSTP} = 'IGNORE';
277 local $SIG{PIPE} = 'IGNORE';
279 my $oldAutoCommit = $FS::UID::AutoCommit;
280 local $FS::UID::AutoCommit = 0;
283 my $name = $self->taxname || 'Tax';
284 my $taxable_cents = 0;
287 my $cust_bill = $taxables->[0]->cust_bill;
288 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
289 my $invoice_time = $cust_bill ? $cust_bill->_date : $opt{'invoice_time'};
290 my $cust_main = FS::cust_main->by_key($custnum) if $custnum;
291 # (to avoid complications with estimated tax on quotations, assume it's
292 # taxable if there is no customer)
294 #die "unable to calculate taxes for an unknown customer\n";
297 # set a flag if the customer is tax-exempt
298 my ($exempt_cust, $exempt_cust_taxname);
299 my $conf = FS::Conf->new;
301 if ( $conf->exists('cust_class-tax_exempt') ) {
302 my $cust_class = $cust_main->cust_class;
303 $exempt_cust = $cust_class->tax if $cust_class;
305 $exempt_cust = $cust_main->tax;
308 # set a flag if the customer is exempt from this tax here
309 if ( $self->taxname ) {
310 $exempt_cust_taxname = $cust_main->tax_exemption($self->taxname);
314 # Gather any exemptions that are already attached to these cust_bill_pkgs
315 # so that we can deduct them from the customer's monthly limit.
316 my @existing_exemptions = @{ $opt{'exemptions'} };
317 push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
320 my $tax_item = FS::cust_bill_pkg->new({
329 foreach my $cust_bill_pkg (@$taxables) {
330 # careful... may be a cust_bill_pkg or a quotation_pkg
332 my $cust_pkg = $cust_bill_pkg->cust_pkg;
333 my $part_pkg = $cust_bill_pkg->part_pkg;
334 my $part_fee = $cust_bill_pkg->part_fee;
336 my $locationnum = $cust_bill_pkg->tax_locationnum
337 || $cust_main->ship_locationnum;
340 my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur
341 or next; # don't create zero-amount exemptions
343 # XXX the following procedure should probably be in cust_bill_pkg
345 if ( $exempt_cust ) {
347 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
348 amount => $taxable_charged,
351 $taxable_charged = 0;
353 } elsif ( $exempt_cust_taxname ) {
355 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
356 amount => $taxable_charged,
357 exempt_cust_taxname => 'Y',
359 $taxable_charged = 0;
363 my $setup_exempt = ( ($part_fee and not $part_fee->taxable)
364 or ($part_pkg and $part_pkg->setuptax)
365 or $self->setuptax );
368 and $cust_bill_pkg->setup > 0
369 and $taxable_charged > 0 ) {
371 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
372 amount => $cust_bill_pkg->setup,
375 $taxable_charged -= $cust_bill_pkg->setup;
379 my $recur_exempt = ( ($part_fee and not $part_fee->taxable)
380 or ($part_pkg and $part_pkg->recurtax)
381 or $self->recurtax );
384 and $cust_bill_pkg->recur > 0
385 and $taxable_charged > 0 ) {
387 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
388 amount => $cust_bill_pkg->recur,
391 $taxable_charged -= $cust_bill_pkg->recur;
395 if ( $self->exempt_amount && $self->exempt_amount > 0
396 and $taxable_charged > 0
399 # XXX monthly exemptions currently don't work on quotations
401 # If the billing period extends across multiple calendar months,
402 # there may be several months of exemption available.
403 my $sdate = $cust_bill_pkg->sdate || $invoice_time;
404 my $start_month = (localtime($sdate))[4] + 1;
405 my $start_year = (localtime($sdate))[5] + 1900;
406 my $edate = $cust_bill_pkg->edate || $invoice_time;
407 my $end_month = (localtime($edate))[4] + 1;
408 my $end_year = (localtime($edate))[5] + 1900;
410 # If the partial last month + partial first month <= one month,
411 # don't use the exemption in the last month
412 # (unless the last month is also the first month, e.g. one-time
414 if ( (localtime($sdate))[3] >= (localtime($edate))[3]
415 and ($start_month != $end_month or $start_year != $end_year)
418 if ( $end_month == 0 ) {
424 # number of months of exemption available
425 my $freq = ($end_month - $start_month) +
426 ($end_year - $start_year) * 12 +
429 # divide equally among all of them
430 my $permonth = sprintf('%.2f', $taxable_charged / $freq);
432 #call the whole thing off if this customer has any old
433 #exemption records...
434 my @cust_tax_exempt =
435 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
436 if ( @cust_tax_exempt ) {
437 $dbh->rollback if $oldAutoCommit;
439 'this customer still has old-style tax exemption records; '.
440 'run bin/fs-migrate-cust_tax_exempt?';
443 my ($mon, $year) = ($start_month, $start_year);
444 while ($taxable_charged > 0.005 and
445 ($year < $end_year or
446 ($year == $end_year and $mon <= $end_month)
450 # find the sum of the exemption used by this customer, for this tax,
454 FROM cust_tax_exempt_pkg
455 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
456 LEFT JOIN cust_bill USING ( invnum )
461 AND exempt_monthly = 'Y'
463 my $sth = dbh->prepare($sql) or do {
464 $dbh->rollback if $oldAutoCommit;
465 return "fatal: can't lookup existing exemption: ". dbh->errstr;
473 $dbh->rollback if $oldAutoCommit;
474 return "fatal: can't lookup existing exemption: ". dbh->errstr;
476 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
478 # add any exemption we're already using for another line item
479 foreach ( grep { $_->taxnum == $self->taxnum &&
480 $_->exempt_monthly eq 'Y' &&
483 } @existing_exemptions
486 $existing_exemption += $_->amount;
489 my $remaining_exemption =
490 $self->exempt_amount - $existing_exemption;
491 if ( $remaining_exemption > 0 ) {
492 my $addl = $remaining_exemption > $permonth
494 : $remaining_exemption;
495 $addl = $taxable_charged if $addl > $taxable_charged;
497 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
498 amount => sprintf('%.2f', $addl),
499 exempt_monthly => 'Y',
503 $taxable_charged -= $addl;
505 # if they're using multiple months of exemption for a multi-month
506 # package, then record the exemptions in separate months
514 } # if exempt_amount and $cust_main
516 $_->taxnum($self->taxnum) foreach @new_exemptions;
518 # attach them to the line item
519 push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions;
520 push @existing_exemptions, @new_exemptions;
522 $taxable_charged = sprintf( "%.2f", $taxable_charged);
523 next if $taxable_charged == 0;
525 my $this_tax_cents = int($taxable_charged * $self->tax);
526 my $location = FS::cust_bill_pkg_tax_location->new({
527 'taxnum' => $self->taxnum,
528 'taxtype' => ref($self),
529 'cents' => $this_tax_cents,
530 'pkgnum' => $cust_bill_pkg->pkgnum,
531 'locationnum' => $locationnum,
532 'taxable_cust_bill_pkg' => $cust_bill_pkg,
533 'tax_cust_bill_pkg' => $tax_item,
535 push @tax_location, $location;
537 $taxable_cents += $taxable_charged;
538 $tax_cents += $this_tax_cents;
539 } #foreach $cust_bill_pkg
541 # now round and distribute
542 my $extra_cents = sprintf('%.2f', $taxable_cents * $self->tax / 100) * 100
544 # make sure we have an integer
545 $extra_cents = sprintf('%.0f', $extra_cents);
546 if ( $extra_cents < 0 ) {
547 die "nonsense extra_cents value $extra_cents";
549 $tax_cents += $extra_cents;
551 foreach (@tax_location) { # can never require more than a single pass, yes?
552 my $cents = $_->get('cents');
553 if ( $extra_cents > 0 ) {
557 $_->set('amount', sprintf('%.2f', $cents/100));
559 $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100));
560 $tax_item->set('cust_bill_pkg_tax_location', \@tax_location);
565 =head1 find_wa_tax_dupes
567 Return a list of cust_main_county Record objects that are detected
568 as duplicate washington state sales tax rows (source=wa_state)
569 within their respective tax classes
573 sub find_wa_tax_dupes {
574 my %cust_main_county;
577 for my $row ( qsearch( cust_main_county => { source => 'wa_sales' } ) ) {
578 my $taxclass = $row->taxclass || 'none';
579 $cust_main_county{$taxclass} ||= {};
581 my $district = $row->district || 'none';
582 $cust_main_county{$taxclass}->{$district} ||= [];
584 push @{ $cust_main_county{$taxclass}->{$district} }, $row;
587 for my $taxclass ( keys %cust_main_county ) {
588 for my $district ( keys %{ $cust_main_county{$taxclass} } ) {
589 my $tax_rows = $cust_main_county{$taxclass}->{$district};
590 if ( scalar @$tax_rows > 1 ) {
591 push @dupes, @$tax_rows;
605 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
610 my ( $selected_county, $selected_state, $selected_country,
611 $prefix, $onchange, $disabled ) = @_;
613 $prefix = '' unless defined $prefix;
617 # unless ( @cust_main_county ) { #cache
618 @cust_main_county = qsearch('cust_main_county', {} );
619 foreach my $c ( @cust_main_county ) {
620 $countyflag=1 if $c->county;
621 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
622 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
625 $countyflag=1 if $selected_county;
627 my $script_html = <<END;
629 function opt(what,value,text) {
630 var optionName = new Option(text, value, false, false);
631 var length = what.length;
632 what.options[length] = optionName;
634 function ${prefix}country_changed(what) {
635 country = what.options[what.selectedIndex].text;
636 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
637 what.form.${prefix}state.options[i] = null;
639 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
641 foreach my $country ( sort keys %cust_main_county ) {
642 $script_html .= "\nif ( country == \"$country\" ) {\n";
643 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
644 ( my $dstate = $state ) =~ s/[\n\r]//g;
645 my $text = $dstate || '(n/a)';
646 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
648 $script_html .= "}\n";
651 $script_html .= <<END;
653 function ${prefix}state_changed(what) {
657 $script_html .= <<END;
658 state = what.options[what.selectedIndex].text;
659 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
660 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
661 what.form.${prefix}county.options[i] = null;
664 foreach my $country ( sort keys %cust_main_county ) {
665 $script_html .= "\nif ( country == \"$country\" ) {\n";
666 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
667 $script_html .= "\nif ( state == \"$state\" ) {\n";
668 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
669 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
670 my $text = $county || '(n/a)';
672 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
674 $script_html .= "}\n";
676 $script_html .= "}\n";
680 $script_html .= <<END;
685 my $county_html = $script_html;
687 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
688 $county_html .= '</SELECT>';
691 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
694 my $state_html = qq!<SELECT NAME="${prefix}state" !.
695 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
696 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
697 my $text = $state || '(n/a)';
698 my $selected = $state eq $selected_state ? 'SELECTED' : '';
699 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
701 $state_html .= '</SELECT>';
703 $state_html .= '</SELECT>';
705 my $country_html = qq!<SELECT NAME="${prefix}country" !.
706 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
707 my $countrydefault = $conf->config('countrydefault') || 'US';
708 foreach my $country (
709 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
710 keys %cust_main_county
712 my $selected = $country eq $selected_country ? ' SELECTED' : '';
713 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
715 $country_html .= '</SELECT>';
717 ($county_html, $state_html, $country_html);
724 # When given two cust_main_county row objects, rewrite all database foreign
725 # key references referring to $row_to_merge->taxnum as references to
726 # $row_to_keep->taxnum, so $row_to_merge can be safely deleted from
729 # Usage (class method):
730 # $row_to_merge->_merge_into( $row_to_keep )
732 # Usage (package function):
733 # FS::cust_main_county::_merge_into( $row_to_merge, $row_to_keep )
735 # Optionally, allow merge when records don't match
736 # (useful during tax table update routines)
737 # $row_to_merge->_merge_info(
739 # { identical_record_check => 0 }
742 my $row_to_merge = shift;
743 my $row_to_keep = shift
744 or croak 'record to merge into must be provided';
746 my $args = shift || { identical_record_check => 1 };
747 croak 'invalid arguments hashref' unless ref $args;
749 my $log = FS::Log->new('FS::cust_main_county');
751 my $keep_taxnum = $row_to_keep->taxnum;
752 my $merge_taxnum = $row_to_merge->taxnum;
755 $args->{identical_record_check}
757 $row_to_keep->tax != $row_to_merge->tax
758 || $row_to_keep->exempt_amount != $row_to_merge->exempt_amount
761 my $msg = "Found duplicate taxes (#$keep_taxnum and #$merge_taxnum) "
762 . "but they have different rates and can't be merged.";
768 my $msg = "Merging tax #$merge_taxnum into #$keep_taxnum";
772 foreach my $table (qw(
773 cust_bill_pkg_tax_location
774 cust_bill_pkg_tax_location_void
776 cust_tax_exempt_pkg_void
778 foreach my $row (qsearch($table, { 'taxnum' => $merge_taxnum })) {
779 $row->set('taxnum' => $keep_taxnum);
780 if ( my $error = $row->replace ) {
781 $log->error( $error );
787 if ( my $error = $row_to_merge->delete ) {
788 $log->error( $error );
795 # assume taxes in Washington with district numbers, and null name, or
796 # named 'sales tax', are looked up via the wa_sales method. mark them.
797 my $journal = 'cust_main_county__source_wa_sales_201611';
798 if (!FS::upgrade_journal->is_done($journal)) {
799 my @taxes = qsearch({
800 'table' => 'cust_main_county',
801 'extra_sql' => " WHERE tax > 0 AND country = 'US' AND state = 'WA'".
802 " AND district IS NOT NULL AND ( taxname IS NULL OR ".
803 " taxname ~* 'sales tax' )",
806 warn "Flagging Washington state sales taxes: ".scalar(@taxes)." records.\n";
808 $_->set('source', 'wa_sales');
809 my $error = $_->replace;
810 die $error if $error;
813 FS::upgrade_journal->set_done($journal);
815 my @key_fields = (qw(city county state country district taxname taxclass));
817 # trim whitespace and convert to uppercase in the 'city' field.
818 foreach my $record (qsearch({
819 table => 'cust_main_county',
820 extra_sql => " WHERE city LIKE ' %' OR city LIKE '% ' OR city != UPPER(city)",
822 # any with-trailing-space records probably duplicate other records
823 # from the same city, and if we just fix the record in place, we'll
824 # create an exact duplicate.
825 # so find the record this one would duplicate, and merge them.
826 $record->check; # trims whitespace
827 my %match = map { $_ => $record->get($_) } @key_fields;
828 my $other = qsearchs('cust_main_county', \%match);
830 $record->_merge_into($other);
832 # else there is no record this one duplicates, so just fix it
833 my $error = $record->replace;
834 die $error if $error;
838 # separate wa_sales taxes by tax class as needed
839 my $district_taxname = $conf->config('tax_district_taxname');
840 $journal = 'cust_main_county__district_taxclass';
841 if (!FS::upgrade_journal->is_done($journal)
842 and $conf->exists('enable_taxclasses')) {
843 eval "use FS::part_pkg_taxclass";
844 my @taxes = qsearch({
845 'table' => 'cust_main_county',
846 'extra_sql' => " WHERE tax > 0 AND country = 'US' AND state = 'WA'".
847 " AND district IS NOT NULL AND source = 'wa_sales'".
848 " AND taxclass IS NULL"
850 my @classes = FS::part_pkg_taxclass->taxclass_names;
852 warn "Separating WA sales taxes: ".scalar(@taxes)." records.\n";
853 foreach my $oldtax (@taxes) {
855 my $taxnum = $oldtax->taxnum;
856 warn "Separating tax #$taxnum into classes\n";
857 foreach my $taxclass (@classes) {
858 # ensure that we end up with a single copy of the tax in this
859 # jurisdiction+class. there may already be one (or more) there.
860 # if so, they all represent the same tax; merge them together.
864 'city' => $oldtax->city,
865 'district' => $oldtax->district,
866 'taxclass' => $taxclass,
867 'source' => 'wa_sales',
869 my @taxes_in_class = qsearch('cust_main_county', {
871 'tax' => { op => '>', value => 0 },
875 my $newtax = shift @taxes_in_class;
877 foreach (@taxes_in_class) {
878 # allow the merge, even if this somehow differs.
879 $_->set('tax', $newtax->tax);
880 $_->_merge_into($newtax);
883 $newtax ||= FS::cust_main_county->new(\%newtax_hash);
884 # copy properties from the pre-split tax
885 $newtax->set('tax', $oldtax->tax);
886 $newtax->set('setuptax', $oldtax->setuptax);
887 $newtax->set('recurtax', $oldtax->recurtax);
888 # and assign the defined tax name
889 $newtax->set('taxname', $district_taxname);
890 $error = ($newtax->taxnum ? $newtax->replace : $newtax->insert);
891 die "splitting taxnum ".$oldtax->taxnum.": $error\n" if $error;
892 } # foreach $taxclass
893 $oldtax->set('tax', 0);
894 $error = $oldtax->replace;
895 die "splitting taxnum ".$oldtax->taxnum.": $error\n" if $error;
898 FS::upgrade_journal->set_done($journal);
901 # also ensure they all have the chosen taxname now
902 if ($district_taxname) {
903 my @taxes = qsearch('cust_main_county', {
904 'source' => 'wa_sales',
905 'taxname' => { op => '!=', value => $district_taxname }
908 warn "Renaming WA sales taxes: ".scalar(@taxes)." records.\n";
909 foreach my $tax (@taxes) {
910 $tax->set('taxname', $district_taxname);
911 my $error = $tax->replace;
912 die "renaming taxnum ".$tax->taxnum.": $error\n" if $error;
917 # remove duplicates (except disabled records)
918 my @duplicate_sets = qsearch({
919 table => 'cust_main_county',
920 select => FS::Record::group_concat_sql('taxnum', ',') . ' AS taxnums, ' .
921 join(',', @key_fields),
922 extra_sql => ' WHERE tax > 0
923 GROUP BY city, county, state, country, district, taxname, taxclass
926 warn "Found ".scalar(@duplicate_sets)." set(s) of duplicate tax definitions\n"
928 foreach my $set (@duplicate_sets) {
929 my @taxnums = split(',', $set->get('taxnums'));
930 my $first = FS::cust_main_county->by_key(shift @taxnums);
931 foreach my $taxnum (@taxnums) {
932 my $record = FS::cust_main_county->by_key($taxnum);
933 $record->_merge_into($first);
945 regionselector? putting web ui components in here? they should probably live
950 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base