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 );
8 use FS::Record qw( qsearch qsearchs dbh );
13 use FS::cust_tax_exempt;
14 use FS::cust_tax_exempt_pkg;
15 use FS::upgrade_journal;
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->exempt_amount(0) unless $self->exempt_amount;
127 $self->ut_numbern('taxnum')
128 || $self->ut_alphan('district')
129 || $self->ut_textn('city')
130 || $self->ut_textn('county')
131 || $self->ut_anything('state')
132 || $self->ut_text('country')
133 || $self->ut_float('tax')
134 || $self->ut_textn('taxclass') # ...
135 || $self->ut_money('exempt_amount')
136 || $self->ut_textn('taxname')
137 || $self->ut_enum('setuptax', [ '', 'Y' ] )
138 || $self->ut_enum('recurtax', [ '', 'Y' ] )
139 || $self->ut_textn('source')
140 || $self->SUPER::check
147 Returns a label looking like "Anytown, Alameda County, CA, US".
149 If the taxname field is set, it will look like
150 "CA Sales Tax (Anytown, Alameda County, CA, US)".
152 If the taxclass is set, then it will be
153 "Anytown, Alameda County, CA, US (International)".
155 OPTIONS may contain "with_taxclass", "with_city", and "with_district" to show
156 those fields. It may also contain "out", in which case, if this region
157 (district+city+county+state+country) contains no non-zero taxes, the label
158 will read "Out of taxable region(s)".
163 my ($self, %opt) = @_;
166 and !defined(qsearchs('cust_main_county', {
167 'district' => $self->district,
168 'city' => $self->city,
169 'county' => $self->county,
170 'state' => $self->state,
171 'country' => $self->country,
172 'tax' => { op => '>', value => 0 },
175 return 'Out of taxable region(s)';
177 my $label = $self->country;
178 $label = $self->state.", $label" if $self->state;
179 $label = $self->county." County, $label" if $self->county;
180 if ($opt{with_city}) {
181 $label = $self->city.", $label" if $self->city;
182 if ($opt{with_district} and $self->district) {
183 $label = $self->district . ", $label";
186 # ugly labels when taxclass and taxname are both non-null...
187 # but this is how the tax report does it
188 if ($opt{with_taxclass}) {
189 $label = "$label (".$self->taxclass.')' if $self->taxclass;
191 $label = $self->taxname." ($label)" if $self->taxname;
196 =item sql_taxclass_sameregion
198 Returns an SQL WHERE fragment or the empty string to search for entries
199 with different tax classes.
203 #hmm, description above could be better...
205 sub sql_taxclass_sameregion {
208 my $same_query = 'SELECT DISTINCT taxclass FROM cust_main_county '.
209 ' WHERE taxnum != ? AND country = ?';
210 my @same_param = ( 'taxnum', 'country' );
211 foreach my $opt_field (qw( state county )) {
212 if ( $self->$opt_field() ) {
213 $same_query .= " AND $opt_field = ?";
214 push @same_param, $opt_field;
216 $same_query .= " AND $opt_field IS NULL";
220 my @taxclasses = $self->_list_sql( \@same_param, $same_query );
222 return '' unless scalar(@taxclasses);
224 '( taxclass IS NULL OR ( '. #only if !$self->taxclass ??
225 join(' AND ', map { 'taxclass != '.dbh->quote($_) } @taxclasses ).
230 my( $self, $param, $sql ) = @_;
231 my $sth = dbh->prepare($sql) or die dbh->errstr;
232 $sth->execute( map $self->$_(), @$param )
233 or die "Unexpected error executing statement $sql: ". $sth->errstr;
234 map $_->[0], @{ $sth->fetchall_arrayref };
237 =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
239 Takes an arrayref of L<FS::cust_bill_pkg> objects representing taxable
240 line items, and returns a new L<FS::cust_bill_pkg> object representing
241 the tax on them under this tax rate.
243 This will have a pseudo-field, "cust_bill_pkg_tax_location", containing
244 an arrayref of L<FS::cust_bill_pkg_tax_location> objects. Each of these
245 will in turn have a "taxable_cust_bill_pkg" pseudo-field linking it to one
246 of the taxable items. All of these links must be resolved as the objects
249 In addition to calculating the tax for the line items, this will calculate
250 any appropriate tax exemptions and attach them to the line items.
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 return 'taxline called with no line items' unless @$taxables;
267 local $SIG{HUP} = 'IGNORE';
268 local $SIG{INT} = 'IGNORE';
269 local $SIG{QUIT} = 'IGNORE';
270 local $SIG{TERM} = 'IGNORE';
271 local $SIG{TSTP} = 'IGNORE';
272 local $SIG{PIPE} = 'IGNORE';
274 my $oldAutoCommit = $FS::UID::AutoCommit;
275 local $FS::UID::AutoCommit = 0;
278 my $name = $self->taxname || 'Tax';
279 my $taxable_cents = 0;
282 my $cust_bill = $taxables->[0]->cust_bill;
283 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
284 my $invoice_time = $cust_bill ? $cust_bill->_date : $opt{'invoice_time'};
285 my $cust_main = FS::cust_main->by_key($custnum) if $custnum;
286 # (to avoid complications with estimated tax on quotations, assume it's
287 # taxable if there is no customer)
289 #die "unable to calculate taxes for an unknown customer\n";
292 # set a flag if the customer is tax-exempt
293 my ($exempt_cust, $exempt_cust_taxname);
294 my $conf = FS::Conf->new;
296 if ( $conf->exists('cust_class-tax_exempt') ) {
297 my $cust_class = $cust_main->cust_class;
298 $exempt_cust = $cust_class->tax if $cust_class;
300 $exempt_cust = $cust_main->tax;
303 # set a flag if the customer is exempt from this tax here
304 if ( $self->taxname ) {
305 $exempt_cust_taxname = $cust_main->tax_exemption($self->taxname);
309 # Gather any exemptions that are already attached to these cust_bill_pkgs
310 # so that we can deduct them from the customer's monthly limit.
311 my @existing_exemptions = @{ $opt{'exemptions'} };
312 push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
315 my $tax_item = FS::cust_bill_pkg->new({
324 foreach my $cust_bill_pkg (@$taxables) {
325 # careful... may be a cust_bill_pkg or a quotation_pkg
327 my $cust_pkg = $cust_bill_pkg->cust_pkg;
328 my $part_pkg = $cust_bill_pkg->part_pkg;
329 my $part_fee = $cust_bill_pkg->part_fee;
331 my $locationnum = $cust_bill_pkg->tax_locationnum
332 || $cust_main->ship_locationnum;
335 my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur
336 or next; # don't create zero-amount exemptions
338 # XXX the following procedure should probably be in cust_bill_pkg
340 if ( $exempt_cust ) {
342 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
343 amount => $taxable_charged,
346 $taxable_charged = 0;
348 } elsif ( $exempt_cust_taxname ) {
350 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
351 amount => $taxable_charged,
352 exempt_cust_taxname => 'Y',
354 $taxable_charged = 0;
358 my $setup_exempt = ( ($part_fee and not $part_fee->taxable)
359 or ($part_pkg and $part_pkg->setuptax)
360 or $self->setuptax );
363 and $cust_bill_pkg->setup > 0
364 and $taxable_charged > 0 ) {
366 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
367 amount => $cust_bill_pkg->setup,
370 $taxable_charged -= $cust_bill_pkg->setup;
374 my $recur_exempt = ( ($part_fee and not $part_fee->taxable)
375 or ($part_pkg and $part_pkg->recurtax)
376 or $self->recurtax );
379 and $cust_bill_pkg->recur > 0
380 and $taxable_charged > 0 ) {
382 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
383 amount => $cust_bill_pkg->recur,
386 $taxable_charged -= $cust_bill_pkg->recur;
390 if ( $self->exempt_amount && $self->exempt_amount > 0
391 and $taxable_charged > 0
394 # XXX monthly exemptions currently don't work on quotations
396 # If the billing period extends across multiple calendar months,
397 # there may be several months of exemption available.
398 my $sdate = $cust_bill_pkg->sdate || $invoice_time;
399 my $start_month = (localtime($sdate))[4] + 1;
400 my $start_year = (localtime($sdate))[5] + 1900;
401 my $edate = $cust_bill_pkg->edate || $invoice_time;
402 my $end_month = (localtime($edate))[4] + 1;
403 my $end_year = (localtime($edate))[5] + 1900;
405 # If the partial last month + partial first month <= one month,
406 # don't use the exemption in the last month
407 # (unless the last month is also the first month, e.g. one-time
409 if ( (localtime($sdate))[3] >= (localtime($edate))[3]
410 and ($start_month != $end_month or $start_year != $end_year)
413 if ( $end_month == 0 ) {
419 # number of months of exemption available
420 my $freq = ($end_month - $start_month) +
421 ($end_year - $start_year) * 12 +
424 # divide equally among all of them
425 my $permonth = sprintf('%.2f', $taxable_charged / $freq);
427 #call the whole thing off if this customer has any old
428 #exemption records...
429 my @cust_tax_exempt =
430 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
431 if ( @cust_tax_exempt ) {
432 $dbh->rollback if $oldAutoCommit;
434 'this customer still has old-style tax exemption records; '.
435 'run bin/fs-migrate-cust_tax_exempt?';
438 my ($mon, $year) = ($start_month, $start_year);
439 while ($taxable_charged > 0.005 and
440 ($year < $end_year or
441 ($year == $end_year and $mon <= $end_month)
445 # find the sum of the exemption used by this customer, for this tax,
449 FROM cust_tax_exempt_pkg
450 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
451 LEFT JOIN cust_bill USING ( invnum )
456 AND exempt_monthly = 'Y'
458 my $sth = dbh->prepare($sql) or do {
459 $dbh->rollback if $oldAutoCommit;
460 return "fatal: can't lookup existing exemption: ". dbh->errstr;
468 $dbh->rollback if $oldAutoCommit;
469 return "fatal: can't lookup existing exemption: ". dbh->errstr;
471 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
473 # add any exemption we're already using for another line item
474 foreach ( grep { $_->taxnum == $self->taxnum &&
475 $_->exempt_monthly eq 'Y' &&
478 } @existing_exemptions
481 $existing_exemption += $_->amount;
484 my $remaining_exemption =
485 $self->exempt_amount - $existing_exemption;
486 if ( $remaining_exemption > 0 ) {
487 my $addl = $remaining_exemption > $permonth
489 : $remaining_exemption;
490 $addl = $taxable_charged if $addl > $taxable_charged;
492 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
493 amount => sprintf('%.2f', $addl),
494 exempt_monthly => 'Y',
498 $taxable_charged -= $addl;
500 # if they're using multiple months of exemption for a multi-month
501 # package, then record the exemptions in separate months
509 } # if exempt_amount and $cust_main
511 $_->taxnum($self->taxnum) foreach @new_exemptions;
513 # attach them to the line item
514 push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions;
515 push @existing_exemptions, @new_exemptions;
517 $taxable_charged = sprintf( "%.2f", $taxable_charged);
518 next if $taxable_charged == 0;
520 my $this_tax_cents = int($taxable_charged * $self->tax);
521 my $location = FS::cust_bill_pkg_tax_location->new({
522 'taxnum' => $self->taxnum,
523 'taxtype' => ref($self),
524 'cents' => $this_tax_cents,
525 'pkgnum' => $cust_bill_pkg->pkgnum,
526 'locationnum' => $locationnum,
527 'taxable_cust_bill_pkg' => $cust_bill_pkg,
528 'tax_cust_bill_pkg' => $tax_item,
530 push @tax_location, $location;
532 $taxable_cents += $taxable_charged;
533 $tax_cents += $this_tax_cents;
534 } #foreach $cust_bill_pkg
536 # now round and distribute
537 my $extra_cents = sprintf('%.2f', $taxable_cents * $self->tax / 100) * 100
539 # make sure we have an integer
540 $extra_cents = sprintf('%.0f', $extra_cents);
541 if ( $extra_cents < 0 ) {
542 die "nonsense extra_cents value $extra_cents";
544 $tax_cents += $extra_cents;
546 foreach (@tax_location) { # can never require more than a single pass, yes?
547 my $cents = $_->get('cents');
548 if ( $extra_cents > 0 ) {
552 $_->set('amount', sprintf('%.2f', $cents/100));
554 $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100));
555 $tax_item->set('cust_bill_pkg_tax_location', \@tax_location);
566 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
571 my ( $selected_county, $selected_state, $selected_country,
572 $prefix, $onchange, $disabled ) = @_;
574 $prefix = '' unless defined $prefix;
578 # unless ( @cust_main_county ) { #cache
579 @cust_main_county = qsearch('cust_main_county', {} );
580 foreach my $c ( @cust_main_county ) {
581 $countyflag=1 if $c->county;
582 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
583 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
586 $countyflag=1 if $selected_county;
588 my $script_html = <<END;
590 function opt(what,value,text) {
591 var optionName = new Option(text, value, false, false);
592 var length = what.length;
593 what.options[length] = optionName;
595 function ${prefix}country_changed(what) {
596 country = what.options[what.selectedIndex].text;
597 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
598 what.form.${prefix}state.options[i] = null;
600 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
602 foreach my $country ( sort keys %cust_main_county ) {
603 $script_html .= "\nif ( country == \"$country\" ) {\n";
604 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
605 ( my $dstate = $state ) =~ s/[\n\r]//g;
606 my $text = $dstate || '(n/a)';
607 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
609 $script_html .= "}\n";
612 $script_html .= <<END;
614 function ${prefix}state_changed(what) {
618 $script_html .= <<END;
619 state = what.options[what.selectedIndex].text;
620 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
621 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
622 what.form.${prefix}county.options[i] = null;
625 foreach my $country ( sort keys %cust_main_county ) {
626 $script_html .= "\nif ( country == \"$country\" ) {\n";
627 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
628 $script_html .= "\nif ( state == \"$state\" ) {\n";
629 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
630 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
631 my $text = $county || '(n/a)';
633 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
635 $script_html .= "}\n";
637 $script_html .= "}\n";
641 $script_html .= <<END;
646 my $county_html = $script_html;
648 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
649 $county_html .= '</SELECT>';
652 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
655 my $state_html = qq!<SELECT NAME="${prefix}state" !.
656 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
657 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
658 my $text = $state || '(n/a)';
659 my $selected = $state eq $selected_state ? 'SELECTED' : '';
660 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
662 $state_html .= '</SELECT>';
664 $state_html .= '</SELECT>';
666 my $country_html = qq!<SELECT NAME="${prefix}country" !.
667 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
668 my $countrydefault = $conf->config('countrydefault') || 'US';
669 foreach my $country (
670 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
671 keys %cust_main_county
673 my $selected = $country eq $selected_country ? ' SELECTED' : '';
674 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
676 $country_html .= '</SELECT>';
678 ($county_html, $state_html, $country_html);
684 # assume taxes in Washington with district numbers, and null name, or
685 # named 'sales tax', are looked up via the wa_sales method. mark them.
686 my $journal = 'cust_main_county__source_wa_sales';
687 if (!FS::upgrade_journal->is_done($journal)) {
688 my @taxes = qsearch({
689 'table' => 'cust_main_county',
690 'extra_sql' => " WHERE tax > 0 AND country = 'US' AND state = 'WA'".
691 " AND district IS NOT NULL AND ( taxname IS NULL OR ".
692 " taxname ~* 'sales tax' )",
695 warn "Flagging Washington state sales taxes: ".scalar(@taxes)." records.\n";
697 $_->set('source', 'wa_sales');
698 my $error = $_->replace;
699 die $error if $error;
702 FS::upgrade_journal->set_done($journal);
711 regionselector? putting web ui components in here? they should probably live
716 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base