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;
16 @EXPORT_OK = qw( regionselector );
18 @cust_main_county = ();
22 #ask FS::UID to run this stuff for us later
23 $FS::UID::callback{'FS::cust_main_county'} = sub {
29 FS::cust_main_county - Object methods for cust_main_county objects
33 use FS::cust_main_county;
35 $record = new FS::cust_main_county \%hash;
36 $record = new FS::cust_main_county { 'column' => 'value' };
38 $error = $record->insert;
40 $error = $new_record->replace($old_record);
42 $error = $record->delete;
44 $error = $record->check;
46 ($county_html, $state_html, $country_html) =
47 FS::cust_main_county::regionselector( $county, $state, $country );
51 An FS::cust_main_county object represents a tax rate, defined by locale.
52 FS::cust_main_county inherits from FS::Record. The following fields are
57 =item taxnum - primary key (assigned automatically for new tax rates)
59 =item district - tax district (optional)
69 =item tax - percentage
75 =item taxname - if defined, printed on invoices instead of "Tax"
77 =item setuptax - if 'Y', this tax does not apply to setup fees
79 =item recurtax - if 'Y', this tax does not apply to recurring fees
89 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
93 sub table { 'cust_main_county'; }
97 Adds this tax rate to the database. If there is an error, returns the error,
98 otherwise returns false.
102 Deletes this tax rate from the database. If there is an error, returns the
103 error, otherwise returns false.
105 =item replace OLD_RECORD
107 Replaces the OLD_RECORD with this one in the database. If there is an error,
108 returns the error, otherwise returns false.
112 Checks all fields to make sure this is a valid tax rate. If there is an error,
113 returns the error, otherwise returns false. Called by the insert and replace
121 $self->exempt_amount(0) unless $self->exempt_amount;
123 $self->ut_numbern('taxnum')
124 || $self->ut_alphan('district')
125 || $self->ut_textn('city')
126 || $self->ut_textn('county')
127 || $self->ut_anything('state')
128 || $self->ut_text('country')
129 || $self->ut_float('tax')
130 || $self->ut_textn('taxclass') # ...
131 || $self->ut_money('exempt_amount')
132 || $self->ut_textn('taxname')
133 || $self->ut_enum('setuptax', [ '', 'Y' ] )
134 || $self->ut_enum('recurtax', [ '', 'Y' ] )
135 || $self->SUPER::check
142 Returns a label looking like "Anytown, Alameda County, CA, US".
144 If the taxname field is set, it will look like
145 "CA Sales Tax (Anytown, Alameda County, CA, US)".
147 If the taxclass is set, then it will be
148 "Anytown, Alameda County, CA, US (International)".
150 OPTIONS may contain "with_taxclass", "with_city", and "with_district" to show
151 those fields. It may also contain "out", in which case, if this region
152 (district+city+county+state+country) contains no non-zero taxes, the label
153 will read "Out of taxable region(s)".
158 my ($self, %opt) = @_;
161 and !defined(qsearchs('cust_main_county', {
162 'district' => $self->district,
163 'city' => $self->city,
164 'county' => $self->county,
165 'state' => $self->state,
166 'country' => $self->country,
167 'tax' => { op => '>', value => 0 },
170 return 'Out of taxable region(s)';
172 my $label = $self->country;
173 $label = $self->state.", $label" if $self->state;
174 $label = $self->county." County, $label" if $self->county;
175 if ($opt{with_city}) {
176 $label = $self->city.", $label" if $self->city;
177 if ($opt{with_district} and $self->district) {
178 $label = $self->district . ", $label";
181 # ugly labels when taxclass and taxname are both non-null...
182 # but this is how the tax report does it
183 if ($opt{with_taxclass}) {
184 $label = "$label (".$self->taxclass.')' if $self->taxclass;
186 $label = $self->taxname." ($label)" if $self->taxname;
191 =item sql_taxclass_sameregion
193 Returns an SQL WHERE fragment or the empty string to search for entries
194 with different tax classes.
198 #hmm, description above could be better...
200 sub sql_taxclass_sameregion {
203 my $same_query = 'SELECT DISTINCT taxclass FROM cust_main_county '.
204 ' WHERE taxnum != ? AND country = ?';
205 my @same_param = ( 'taxnum', 'country' );
206 foreach my $opt_field (qw( state county )) {
207 if ( $self->$opt_field() ) {
208 $same_query .= " AND $opt_field = ?";
209 push @same_param, $opt_field;
211 $same_query .= " AND $opt_field IS NULL";
215 my @taxclasses = $self->_list_sql( \@same_param, $same_query );
217 return '' unless scalar(@taxclasses);
219 '( taxclass IS NULL OR ( '. #only if !$self->taxclass ??
220 join(' AND ', map { 'taxclass != '.dbh->quote($_) } @taxclasses ).
225 my( $self, $param, $sql ) = @_;
226 my $sth = dbh->prepare($sql) or die dbh->errstr;
227 $sth->execute( map $self->$_(), @$param )
228 or die "Unexpected error executing statement $sql: ". $sth->errstr;
229 map $_->[0], @{ $sth->fetchall_arrayref };
232 =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
234 Takes an arrayref of L<FS::cust_bill_pkg> objects representing taxable
235 line items, and returns a new L<FS::cust_bill_pkg> object representing
236 the tax on them under this tax rate.
238 This will have a pseudo-field, "cust_bill_pkg_tax_location", containing
239 an arrayref of L<FS::cust_bill_pkg_tax_location> objects. Each of these
240 will in turn have a "taxable_cust_bill_pkg" pseudo-field linking it to one
241 of the taxable items. All of these links must be resolved as the objects
244 In addition to calculating the tax for the line items, this will calculate
245 any appropriate tax exemptions and attach them to the line items.
247 Options may include 'custnum' and 'invoice_time' in case the cust_bill_pkg
248 objects belong to an invoice that hasn't been inserted yet.
250 Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg>
251 objects belonging to the same customer, to be counted against the monthly
252 tax exemption limit if there is one.
256 # XXX change tax_rate.pm to work like this
259 my( $self, $taxables, %opt ) = @_;
260 return 'taxline called with no line items' unless @$taxables;
262 local $SIG{HUP} = 'IGNORE';
263 local $SIG{INT} = 'IGNORE';
264 local $SIG{QUIT} = 'IGNORE';
265 local $SIG{TERM} = 'IGNORE';
266 local $SIG{TSTP} = 'IGNORE';
267 local $SIG{PIPE} = 'IGNORE';
269 my $oldAutoCommit = $FS::UID::AutoCommit;
270 local $FS::UID::AutoCommit = 0;
273 my $name = $self->taxname || 'Tax';
274 my $taxable_cents = 0;
277 my $cust_bill = $taxables->[0]->cust_bill;
278 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
279 my $invoice_time = $cust_bill ? $cust_bill->_date : $opt{'invoice_time'};
280 my $cust_main = FS::cust_main->by_key($custnum) if $custnum;
281 # (to avoid complications with estimated tax on quotations, assume it's
282 # taxable if there is no customer)
284 #die "unable to calculate taxes for an unknown customer\n";
287 # set a flag if the customer is tax-exempt
288 my ($exempt_cust, $exempt_cust_taxname);
289 my $conf = FS::Conf->new;
291 if ( $conf->exists('cust_class-tax_exempt') ) {
292 my $cust_class = $cust_main->cust_class;
293 $exempt_cust = $cust_class->tax if $cust_class;
295 $exempt_cust = $cust_main->tax;
298 # set a flag if the customer is exempt from this tax here
299 if ( $self->taxname ) {
300 $exempt_cust_taxname = $cust_main->tax_exemption($self->taxname);
304 # Gather any exemptions that are already attached to these cust_bill_pkgs
305 # so that we can deduct them from the customer's monthly limit.
306 my @existing_exemptions = @{ $opt{'exemptions'} };
307 push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
310 my $tax_item = FS::cust_bill_pkg->new({
319 foreach my $cust_bill_pkg (@$taxables) {
320 # careful... may be a cust_bill_pkg or a quotation_pkg
322 my $cust_pkg = $cust_bill_pkg->cust_pkg;
323 my $part_pkg = $cust_bill_pkg->part_pkg;
324 my $part_fee = $cust_bill_pkg->part_fee;
326 my $locationnum = $cust_bill_pkg->tax_locationnum
327 || $cust_main->ship_locationnum;
330 my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur
331 or next; # don't create zero-amount exemptions
333 # XXX the following procedure should probably be in cust_bill_pkg
335 if ( $exempt_cust ) {
337 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
338 amount => $taxable_charged,
341 $taxable_charged = 0;
343 } elsif ( $exempt_cust_taxname ) {
345 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
346 amount => $taxable_charged,
347 exempt_cust_taxname => 'Y',
349 $taxable_charged = 0;
353 my $setup_exempt = ( ($part_fee and not $part_fee->taxable)
354 or ($part_pkg and $part_pkg->setuptax)
355 or $self->setuptax );
358 and $cust_bill_pkg->setup > 0
359 and $taxable_charged > 0 ) {
361 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
362 amount => $cust_bill_pkg->setup,
365 $taxable_charged -= $cust_bill_pkg->setup;
369 my $recur_exempt = ( ($part_fee and not $part_fee->taxable)
370 or ($part_pkg and $part_pkg->recurtax)
371 or $self->recurtax );
374 and $cust_bill_pkg->recur > 0
375 and $taxable_charged > 0 ) {
377 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
378 amount => $cust_bill_pkg->recur,
381 $taxable_charged -= $cust_bill_pkg->recur;
385 if ( $self->exempt_amount && $self->exempt_amount > 0
386 and $taxable_charged > 0
389 # XXX monthly exemptions currently don't work on quotations
391 # If the billing period extends across multiple calendar months,
392 # there may be several months of exemption available.
393 my $sdate = $cust_bill_pkg->sdate || $invoice_time;
394 my $start_month = (localtime($sdate))[4] + 1;
395 my $start_year = (localtime($sdate))[5] + 1900;
396 my $edate = $cust_bill_pkg->edate || $invoice_time;
397 my $end_month = (localtime($edate))[4] + 1;
398 my $end_year = (localtime($edate))[5] + 1900;
400 # If the partial last month + partial first month <= one month,
401 # don't use the exemption in the last month
402 # (unless the last month is also the first month, e.g. one-time
404 if ( (localtime($sdate))[3] >= (localtime($edate))[3]
405 and ($start_month != $end_month or $start_year != $end_year)
408 if ( $end_month == 0 ) {
414 # number of months of exemption available
415 my $freq = ($end_month - $start_month) +
416 ($end_year - $start_year) * 12 +
419 # divide equally among all of them
420 my $permonth = sprintf('%.2f', $taxable_charged / $freq);
422 #call the whole thing off if this customer has any old
423 #exemption records...
424 my @cust_tax_exempt =
425 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
426 if ( @cust_tax_exempt ) {
427 $dbh->rollback if $oldAutoCommit;
429 'this customer still has old-style tax exemption records; '.
430 'run bin/fs-migrate-cust_tax_exempt?';
433 my ($mon, $year) = ($start_month, $start_year);
434 while ($taxable_charged > 0.005 and
435 ($year < $end_year or
436 ($year == $end_year and $mon <= $end_month)
440 # find the sum of the exemption used by this customer, for this tax,
444 FROM cust_tax_exempt_pkg
445 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
446 LEFT JOIN cust_bill USING ( invnum )
451 AND exempt_monthly = 'Y'
453 my $sth = dbh->prepare($sql) or do {
454 $dbh->rollback if $oldAutoCommit;
455 return "fatal: can't lookup existing exemption: ". dbh->errstr;
463 $dbh->rollback if $oldAutoCommit;
464 return "fatal: can't lookup existing exemption: ". dbh->errstr;
466 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
468 # add any exemption we're already using for another line item
469 foreach ( grep { $_->taxnum == $self->taxnum &&
470 $_->exempt_monthly eq 'Y' &&
473 } @existing_exemptions
476 $existing_exemption += $_->amount;
479 my $remaining_exemption =
480 $self->exempt_amount - $existing_exemption;
481 if ( $remaining_exemption > 0 ) {
482 my $addl = $remaining_exemption > $permonth
484 : $remaining_exemption;
485 $addl = $taxable_charged if $addl > $taxable_charged;
487 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
488 amount => sprintf('%.2f', $addl),
489 exempt_monthly => 'Y',
493 $taxable_charged -= $addl;
495 # if they're using multiple months of exemption for a multi-month
496 # package, then record the exemptions in separate months
504 } # if exempt_amount and $cust_main
506 $_->taxnum($self->taxnum) foreach @new_exemptions;
508 # attach them to the line item
509 push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions;
510 push @existing_exemptions, @new_exemptions;
512 $taxable_charged = sprintf( "%.2f", $taxable_charged);
513 next if $taxable_charged == 0;
515 my $this_tax_cents = int($taxable_charged * $self->tax);
516 my $location = FS::cust_bill_pkg_tax_location->new({
517 'taxnum' => $self->taxnum,
518 'taxtype' => ref($self),
519 'cents' => $this_tax_cents,
520 'pkgnum' => $cust_bill_pkg->pkgnum,
521 'locationnum' => $locationnum,
522 'taxable_cust_bill_pkg' => $cust_bill_pkg,
523 'tax_cust_bill_pkg' => $tax_item,
525 push @tax_location, $location;
527 $taxable_cents += $taxable_charged;
528 $tax_cents += $this_tax_cents;
529 } #foreach $cust_bill_pkg
531 # now round and distribute
532 my $extra_cents = sprintf('%.2f', $taxable_cents * $self->tax / 100) * 100
534 # make sure we have an integer
535 $extra_cents = sprintf('%.0f', $extra_cents);
536 if ( $extra_cents < 0 ) {
537 die "nonsense extra_cents value $extra_cents";
539 $tax_cents += $extra_cents;
541 foreach (@tax_location) { # can never require more than a single pass, yes?
542 my $cents = $_->get('cents');
543 if ( $extra_cents > 0 ) {
547 $_->set('amount', sprintf('%.2f', $cents/100));
549 $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100));
550 $tax_item->set('cust_bill_pkg_tax_location', \@tax_location);
561 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
566 my ( $selected_county, $selected_state, $selected_country,
567 $prefix, $onchange, $disabled ) = @_;
569 $prefix = '' unless defined $prefix;
573 # unless ( @cust_main_county ) { #cache
574 @cust_main_county = qsearch('cust_main_county', {} );
575 foreach my $c ( @cust_main_county ) {
576 $countyflag=1 if $c->county;
577 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
578 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
581 $countyflag=1 if $selected_county;
583 my $script_html = <<END;
585 function opt(what,value,text) {
586 var optionName = new Option(text, value, false, false);
587 var length = what.length;
588 what.options[length] = optionName;
590 function ${prefix}country_changed(what) {
591 country = what.options[what.selectedIndex].text;
592 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
593 what.form.${prefix}state.options[i] = null;
595 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
597 foreach my $country ( sort keys %cust_main_county ) {
598 $script_html .= "\nif ( country == \"$country\" ) {\n";
599 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
600 ( my $dstate = $state ) =~ s/[\n\r]//g;
601 my $text = $dstate || '(n/a)';
602 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
604 $script_html .= "}\n";
607 $script_html .= <<END;
609 function ${prefix}state_changed(what) {
613 $script_html .= <<END;
614 state = what.options[what.selectedIndex].text;
615 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
616 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
617 what.form.${prefix}county.options[i] = null;
620 foreach my $country ( sort keys %cust_main_county ) {
621 $script_html .= "\nif ( country == \"$country\" ) {\n";
622 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
623 $script_html .= "\nif ( state == \"$state\" ) {\n";
624 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
625 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
626 my $text = $county || '(n/a)';
628 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
630 $script_html .= "}\n";
632 $script_html .= "}\n";
636 $script_html .= <<END;
641 my $county_html = $script_html;
643 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
644 $county_html .= '</SELECT>';
647 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
650 my $state_html = qq!<SELECT NAME="${prefix}state" !.
651 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
652 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
653 my $text = $state || '(n/a)';
654 my $selected = $state eq $selected_state ? 'SELECTED' : '';
655 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
657 $state_html .= '</SELECT>';
659 $state_html .= '</SELECT>';
661 my $country_html = qq!<SELECT NAME="${prefix}country" !.
662 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
663 my $countrydefault = $conf->config('countrydefault') || 'US';
664 foreach my $country (
665 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
666 keys %cust_main_county
668 my $selected = $country eq $selected_country ? ' SELECTED' : '';
669 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
671 $country_html .= '</SELECT>';
673 ($county_html, $state_html, $country_html);
681 regionselector? putting web ui components in here? they should probably live
686 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base