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->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 Options may include 'custnum' and 'invoice_time' in case the cust_bill_pkg
250 objects belong to an invoice that hasn't been inserted yet.
252 Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg>
253 objects belonging to the same customer, to be counted against the monthly
254 tax exemption limit if there is one.
258 # XXX change tax_rate.pm to work like this
261 my( $self, $taxables, %opt ) = @_;
262 $taxables = [ $taxables ] unless ref($taxables) eq 'ARRAY';
263 # remove any charge class identifiers; they're not supported here
264 @$taxables = grep { ref $_ } @$taxables;
266 return 'taxline called with no line items' unless @$taxables;
268 local $SIG{HUP} = 'IGNORE';
269 local $SIG{INT} = 'IGNORE';
270 local $SIG{QUIT} = 'IGNORE';
271 local $SIG{TERM} = 'IGNORE';
272 local $SIG{TSTP} = 'IGNORE';
273 local $SIG{PIPE} = 'IGNORE';
275 my $oldAutoCommit = $FS::UID::AutoCommit;
276 local $FS::UID::AutoCommit = 0;
279 my $name = $self->taxname || 'Tax';
280 my $taxable_cents = 0;
283 my $round_per_line_item = $conf->exists('tax-round_per_line_item');
285 my $cust_bill = $taxables->[0]->cust_bill;
286 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
287 my $invoice_time = $cust_bill ? $cust_bill->_date : $opt{'invoice_time'};
288 my $cust_main = FS::cust_main->by_key($custnum) if $custnum > 0;
289 # (to avoid complications with estimated tax on quotations, assume it's
290 # taxable if there is no customer)
292 #die "unable to calculate taxes for an unknown customer\n";
295 # Gather any exemptions that are already attached to these cust_bill_pkgs
296 # so that we can deduct them from the customer's monthly limit.
297 my @existing_exemptions = @{ $opt{'exemptions'} };
298 push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
301 my $tax_item = FS::cust_bill_pkg->new({
310 foreach my $cust_bill_pkg (@$taxables) {
311 # careful... may be a cust_bill_pkg or a quotation_pkg
313 my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur;
314 foreach ( grep { $_->taxnum == $self->taxnum }
315 @{ $cust_bill_pkg->cust_tax_exempt_pkg }
317 # deal with exemptions that have been set on this line item, and
318 # pertain to this tax def
319 $taxable_charged -= $_->amount;
322 # can't determine the tax_locationnum directly for fees; they're not
323 # yet linked to an invoice
324 my $locationnum = $cust_bill_pkg->tax_locationnum
325 || $cust_main->ship_locationnum;
327 ### Monthly capped exemptions ###
328 if ( $self->exempt_amount && $self->exempt_amount > 0
329 and $taxable_charged > 0
332 # XXX monthly exemptions currently don't work on quotations
334 # If the billing period extends across multiple calendar months,
335 # there may be several months of exemption available.
336 my $sdate = $cust_bill_pkg->sdate || $invoice_time;
337 my $start_month = (localtime($sdate))[4] + 1;
338 my $start_year = (localtime($sdate))[5] + 1900;
339 my $edate = $cust_bill_pkg->edate || $invoice_time;
340 my $end_month = (localtime($edate))[4] + 1;
341 my $end_year = (localtime($edate))[5] + 1900;
343 # If the partial last month + partial first month <= one month,
344 # don't use the exemption in the last month
345 # (unless the last month is also the first month, e.g. one-time
347 if ( (localtime($sdate))[3] >= (localtime($edate))[3]
348 and ($start_month != $end_month or $start_year != $end_year)
351 if ( $end_month == 0 ) {
357 # number of months of exemption available
358 my $freq = ($end_month - $start_month) +
359 ($end_year - $start_year) * 12 +
362 # divide equally among all of them
363 my $permonth = sprintf('%.2f', $taxable_charged / $freq);
365 #call the whole thing off if this customer has any old
366 #exemption records...
367 my @cust_tax_exempt =
368 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
369 if ( @cust_tax_exempt ) {
370 $dbh->rollback if $oldAutoCommit;
372 'this customer still has old-style tax exemption records; '.
373 'run bin/fs-migrate-cust_tax_exempt?';
376 my ($mon, $year) = ($start_month, $start_year);
377 while ($taxable_charged > 0.005 and
378 ($year < $end_year or
379 ($year == $end_year and $mon <= $end_month)
383 # find the sum of the exemption used by this customer, for this tax,
387 FROM cust_tax_exempt_pkg
388 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
389 LEFT JOIN cust_bill USING ( invnum )
394 AND exempt_monthly = 'Y'
396 my $sth = dbh->prepare($sql) or do {
397 $dbh->rollback if $oldAutoCommit;
398 return "fatal: can't lookup existing exemption: ". dbh->errstr;
406 $dbh->rollback if $oldAutoCommit;
407 return "fatal: can't lookup existing exemption: ". dbh->errstr;
409 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
411 # add any exemption we're already using for another line item
412 foreach ( grep { $_->taxnum == $self->taxnum &&
413 $_->exempt_monthly eq 'Y' &&
416 } @existing_exemptions
419 $existing_exemption += $_->amount;
422 my $remaining_exemption =
423 $self->exempt_amount - $existing_exemption;
424 if ( $remaining_exemption > 0 ) {
425 my $addl = $remaining_exemption > $permonth
427 : $remaining_exemption;
428 $addl = $taxable_charged if $addl > $taxable_charged;
431 FS::cust_tax_exempt_pkg->new({
432 amount => sprintf('%.2f', $addl),
433 exempt_monthly => 'Y',
436 taxnum => $self->taxnum,
437 taxtype => ref($self)
439 $taxable_charged -= $addl;
441 # create a record of it
442 push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, $new_exemption;
443 # and allow it to be counted against the limit for other packages
444 push @existing_exemptions, $new_exemption;
446 # if they're using multiple months of exemption for a multi-month
447 # package, then record the exemptions in separate months
455 } # if exempt_amount and $cust_main
457 $taxable_charged = sprintf( "%.2f", $taxable_charged);
458 next if $taxable_charged == 0;
460 my $this_tax_cents = $taxable_charged * $self->tax;
461 if ( $round_per_line_item ) {
462 # Round the tax to the nearest cent for each line item, instead of
463 # across the whole invoice.
464 $this_tax_cents = sprintf('%.0f', $this_tax_cents);
466 # Otherwise truncate it so that rounding error is always positive.
467 $this_tax_cents = int($this_tax_cents);
470 my $location = FS::cust_bill_pkg_tax_location->new({
471 'taxnum' => $self->taxnum,
472 'taxtype' => ref($self),
473 'cents' => $this_tax_cents,
474 'pkgnum' => $cust_bill_pkg->pkgnum,
475 'locationnum' => $locationnum,
476 'taxable_cust_bill_pkg' => $cust_bill_pkg,
477 'tax_cust_bill_pkg' => $tax_item,
479 push @tax_location, $location;
481 $taxable_cents += $taxable_charged;
482 $tax_cents += $this_tax_cents;
483 } #foreach $cust_bill_pkg
485 # calculate tax and rounding error for the whole group
486 my $extra_cents = sprintf('%.2f', $taxable_cents * $self->tax / 100) * 100
488 # make sure we have an integer
489 $extra_cents = sprintf('%.0f', $extra_cents);
491 # if we're rounding per item, then ignore that and don't distribute any
493 if ( $round_per_line_item ) {
497 if ( $extra_cents < 0 ) {
498 die "nonsense extra_cents value $extra_cents";
500 $tax_cents += $extra_cents;
502 foreach (@tax_location) { # can never require more than a single pass, yes?
503 my $cents = $_->get('cents');
504 if ( $extra_cents > 0 ) {
508 $_->set('amount', sprintf('%.2f', $cents/100));
510 $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100));
511 $tax_item->set('cust_bill_pkg_tax_location', \@tax_location);
522 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
527 my ( $selected_county, $selected_state, $selected_country,
528 $prefix, $onchange, $disabled ) = @_;
530 $prefix = '' unless defined $prefix;
534 # unless ( @cust_main_county ) { #cache
535 @cust_main_county = qsearch('cust_main_county', {} );
536 foreach my $c ( @cust_main_county ) {
537 $countyflag=1 if $c->county;
538 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
539 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
542 $countyflag=1 if $selected_county;
544 my $script_html = <<END;
546 function opt(what,value,text) {
547 var optionName = new Option(text, value, false, false);
548 var length = what.length;
549 what.options[length] = optionName;
551 function ${prefix}country_changed(what) {
552 country = what.options[what.selectedIndex].text;
553 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
554 what.form.${prefix}state.options[i] = null;
556 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
558 foreach my $country ( sort keys %cust_main_county ) {
559 $script_html .= "\nif ( country == \"$country\" ) {\n";
560 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
561 ( my $dstate = $state ) =~ s/[\n\r]//g;
562 my $text = $dstate || '(n/a)';
563 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
565 $script_html .= "}\n";
568 $script_html .= <<END;
570 function ${prefix}state_changed(what) {
574 $script_html .= <<END;
575 state = what.options[what.selectedIndex].text;
576 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
577 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
578 what.form.${prefix}county.options[i] = null;
581 foreach my $country ( sort keys %cust_main_county ) {
582 $script_html .= "\nif ( country == \"$country\" ) {\n";
583 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
584 $script_html .= "\nif ( state == \"$state\" ) {\n";
585 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
586 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
587 my $text = $county || '(n/a)';
589 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
591 $script_html .= "}\n";
593 $script_html .= "}\n";
597 $script_html .= <<END;
602 my $county_html = $script_html;
604 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
605 $county_html .= '</SELECT>';
608 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
611 my $state_html = qq!<SELECT NAME="${prefix}state" !.
612 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
613 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
614 my $text = $state || '(n/a)';
615 my $selected = $state eq $selected_state ? 'SELECTED' : '';
616 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
618 $state_html .= '</SELECT>';
620 $state_html .= '</SELECT>';
622 my $country_html = qq!<SELECT NAME="${prefix}country" !.
623 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
624 my $countrydefault = $conf->config('countrydefault') || 'US';
625 foreach my $country (
626 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
627 keys %cust_main_county
629 my $selected = $country eq $selected_country ? ' SELECTED' : '';
630 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
632 $country_html .= '</SELECT>';
634 ($county_html, $state_html, $country_html);
640 # assume taxes in Washington with district numbers, and null name, or
641 # named 'sales tax', are looked up via the wa_sales method. mark them.
642 my $journal = 'cust_main_county__source_wa_sales';
643 if (!FS::upgrade_journal->is_done($journal)) {
644 my @taxes = qsearch({
645 'table' => 'cust_main_county',
646 'extra_sql' => " WHERE tax > 0 AND country = 'US' AND state = 'WA'".
647 " AND district IS NOT NULL AND ( taxname IS NULL OR ".
648 " taxname ~* 'sales tax' )",
651 warn "Flagging Washington state sales taxes: ".scalar(@taxes)." records.\n";
653 $_->set('source', 'wa_sales');
654 my $error = $_->replace;
655 die $error if $error;
658 FS::upgrade_journal->set_done($journal);
667 regionselector? putting web ui components in here? they should probably live
672 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base