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;
15 @ISA = qw( FS::Record );
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 Options may include 'custnum' and 'invoice_time' in case the cust_bill_pkg
245 objects belong to an invoice that hasn't been inserted yet.
247 Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg>
248 objects belonging to the same customer, to be counted against the monthly
249 tax exemption limit if there is one.
253 # XXX change tax_rate.pm to work like this
256 my( $self, $taxables, %opt ) = @_;
257 $taxables = [ $taxables ] unless ref($taxables) eq 'ARRAY';
258 # remove any charge class identifiers; they're not supported here
259 @$taxables = grep { ref $_ } @$taxables;
261 return 'taxline called with no line items' unless @$taxables;
263 local $SIG{HUP} = 'IGNORE';
264 local $SIG{INT} = 'IGNORE';
265 local $SIG{QUIT} = 'IGNORE';
266 local $SIG{TERM} = 'IGNORE';
267 local $SIG{TSTP} = 'IGNORE';
268 local $SIG{PIPE} = 'IGNORE';
270 my $oldAutoCommit = $FS::UID::AutoCommit;
271 local $FS::UID::AutoCommit = 0;
274 my $name = $self->taxname || 'Tax';
275 my $taxable_cents = 0;
278 my $round_per_line_item = $conf->exists('tax-round_per_line_item');
280 my $cust_bill = $taxables->[0]->cust_bill;
281 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
282 my $invoice_time = $cust_bill ? $cust_bill->_date : $opt{'invoice_time'};
283 my $cust_main = FS::cust_main->by_key($custnum) if $custnum > 0;
284 # (to avoid complications with estimated tax on quotations, assume it's
285 # taxable if there is no customer)
287 #die "unable to calculate taxes for an unknown customer\n";
290 # Gather any exemptions that are already attached to these cust_bill_pkgs
291 # so that we can deduct them from the customer's monthly limit.
292 my @existing_exemptions = @{ $opt{'exemptions'} };
293 push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
296 my $tax_item = FS::cust_bill_pkg->new({
305 foreach my $cust_bill_pkg (@$taxables) {
306 # careful... may be a cust_bill_pkg or a quotation_pkg
308 my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur;
309 foreach ( grep { $_->taxnum == $self->taxnum }
310 @{ $cust_bill_pkg->cust_tax_exempt_pkg }
312 # deal with exemptions that have been set on this line item, and
313 # pertain to this tax def
314 $taxable_charged -= $_->amount;
317 # can't determine the tax_locationnum directly for fees; they're not
318 # yet linked to an invoice
319 my $locationnum = $cust_bill_pkg->tax_locationnum
320 || $cust_main->ship_locationnum;
322 ### Monthly capped exemptions ###
323 if ( $self->exempt_amount && $self->exempt_amount > 0
324 and $taxable_charged > 0
327 # XXX monthly exemptions currently don't work on quotations
329 # If the billing period extends across multiple calendar months,
330 # there may be several months of exemption available.
331 my $sdate = $cust_bill_pkg->sdate || $invoice_time;
332 my $start_month = (localtime($sdate))[4] + 1;
333 my $start_year = (localtime($sdate))[5] + 1900;
334 my $edate = $cust_bill_pkg->edate || $invoice_time;
335 my $end_month = (localtime($edate))[4] + 1;
336 my $end_year = (localtime($edate))[5] + 1900;
338 # If the partial last month + partial first month <= one month,
339 # don't use the exemption in the last month
340 # (unless the last month is also the first month, e.g. one-time
342 if ( (localtime($sdate))[3] >= (localtime($edate))[3]
343 and ($start_month != $end_month or $start_year != $end_year)
346 if ( $end_month == 0 ) {
352 # number of months of exemption available
353 my $freq = ($end_month - $start_month) +
354 ($end_year - $start_year) * 12 +
357 # divide equally among all of them
358 my $permonth = sprintf('%.2f', $taxable_charged / $freq);
360 #call the whole thing off if this customer has any old
361 #exemption records...
362 my @cust_tax_exempt =
363 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
364 if ( @cust_tax_exempt ) {
365 $dbh->rollback if $oldAutoCommit;
367 'this customer still has old-style tax exemption records; '.
368 'run bin/fs-migrate-cust_tax_exempt?';
371 my ($mon, $year) = ($start_month, $start_year);
372 while ($taxable_charged > 0.005 and
373 ($year < $end_year or
374 ($year == $end_year and $mon <= $end_month)
378 # find the sum of the exemption used by this customer, for this tax,
382 FROM cust_tax_exempt_pkg
383 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
384 LEFT JOIN cust_bill USING ( invnum )
389 AND exempt_monthly = 'Y'
391 my $sth = dbh->prepare($sql) or do {
392 $dbh->rollback if $oldAutoCommit;
393 return "fatal: can't lookup existing exemption: ". dbh->errstr;
401 $dbh->rollback if $oldAutoCommit;
402 return "fatal: can't lookup existing exemption: ". dbh->errstr;
404 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
406 # add any exemption we're already using for another line item
407 foreach ( grep { $_->taxnum == $self->taxnum &&
408 $_->exempt_monthly eq 'Y' &&
411 } @existing_exemptions
414 $existing_exemption += $_->amount;
417 my $remaining_exemption =
418 $self->exempt_amount - $existing_exemption;
419 if ( $remaining_exemption > 0 ) {
420 my $addl = $remaining_exemption > $permonth
422 : $remaining_exemption;
423 $addl = $taxable_charged if $addl > $taxable_charged;
426 FS::cust_tax_exempt_pkg->new({
427 amount => sprintf('%.2f', $addl),
428 exempt_monthly => 'Y',
431 taxnum => $self->taxnum,
432 taxtype => ref($self)
434 $taxable_charged -= $addl;
436 # create a record of it
437 push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, $new_exemption;
438 # and allow it to be counted against the limit for other packages
439 push @existing_exemptions, $new_exemption;
441 # if they're using multiple months of exemption for a multi-month
442 # package, then record the exemptions in separate months
450 } # if exempt_amount and $cust_main
452 $taxable_charged = sprintf( "%.2f", $taxable_charged);
453 next if $taxable_charged == 0;
455 my $this_tax_cents = $taxable_charged * $self->tax;
456 if ( $round_per_line_item ) {
457 # Round the tax to the nearest cent for each line item, instead of
458 # across the whole invoice.
459 $this_tax_cents = sprintf('%.0f', $this_tax_cents);
461 # Otherwise truncate it so that rounding error is always positive.
462 $this_tax_cents = int($this_tax_cents);
465 my $location = FS::cust_bill_pkg_tax_location->new({
466 'taxnum' => $self->taxnum,
467 'taxtype' => ref($self),
468 'cents' => $this_tax_cents,
469 'pkgnum' => $cust_bill_pkg->pkgnum,
470 'locationnum' => $locationnum,
471 'taxable_cust_bill_pkg' => $cust_bill_pkg,
472 'tax_cust_bill_pkg' => $tax_item,
474 push @tax_location, $location;
476 $taxable_cents += $taxable_charged;
477 $tax_cents += $this_tax_cents;
478 } #foreach $cust_bill_pkg
480 # calculate tax and rounding error for the whole group
481 my $extra_cents = sprintf('%.2f', $taxable_cents * $self->tax / 100) * 100
483 # make sure we have an integer
484 $extra_cents = sprintf('%.0f', $extra_cents);
486 # if we're rounding per item, then ignore that and don't distribute any
488 if ( $round_per_line_item ) {
492 if ( $extra_cents < 0 ) {
493 die "nonsense extra_cents value $extra_cents";
495 $tax_cents += $extra_cents;
497 foreach (@tax_location) { # can never require more than a single pass, yes?
498 my $cents = $_->get('cents');
499 if ( $extra_cents > 0 ) {
503 $_->set('amount', sprintf('%.2f', $cents/100));
505 $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100));
506 $tax_item->set('cust_bill_pkg_tax_location', \@tax_location);
517 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
522 my ( $selected_county, $selected_state, $selected_country,
523 $prefix, $onchange, $disabled ) = @_;
525 $prefix = '' unless defined $prefix;
529 # unless ( @cust_main_county ) { #cache
530 @cust_main_county = qsearch('cust_main_county', {} );
531 foreach my $c ( @cust_main_county ) {
532 $countyflag=1 if $c->county;
533 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
534 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
537 $countyflag=1 if $selected_county;
539 my $script_html = <<END;
541 function opt(what,value,text) {
542 var optionName = new Option(text, value, false, false);
543 var length = what.length;
544 what.options[length] = optionName;
546 function ${prefix}country_changed(what) {
547 country = what.options[what.selectedIndex].text;
548 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
549 what.form.${prefix}state.options[i] = null;
551 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
553 foreach my $country ( sort keys %cust_main_county ) {
554 $script_html .= "\nif ( country == \"$country\" ) {\n";
555 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
556 ( my $dstate = $state ) =~ s/[\n\r]//g;
557 my $text = $dstate || '(n/a)';
558 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
560 $script_html .= "}\n";
563 $script_html .= <<END;
565 function ${prefix}state_changed(what) {
569 $script_html .= <<END;
570 state = what.options[what.selectedIndex].text;
571 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
572 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
573 what.form.${prefix}county.options[i] = null;
576 foreach my $country ( sort keys %cust_main_county ) {
577 $script_html .= "\nif ( country == \"$country\" ) {\n";
578 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
579 $script_html .= "\nif ( state == \"$state\" ) {\n";
580 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
581 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
582 my $text = $county || '(n/a)';
584 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
586 $script_html .= "}\n";
588 $script_html .= "}\n";
592 $script_html .= <<END;
597 my $county_html = $script_html;
599 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
600 $county_html .= '</SELECT>';
603 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
606 my $state_html = qq!<SELECT NAME="${prefix}state" !.
607 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
608 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
609 my $text = $state || '(n/a)';
610 my $selected = $state eq $selected_state ? 'SELECTED' : '';
611 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
613 $state_html .= '</SELECT>';
615 $state_html .= '</SELECT>';
617 my $country_html = qq!<SELECT NAME="${prefix}country" !.
618 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
619 my $countrydefault = $conf->config('countrydefault') || 'US';
620 foreach my $country (
621 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
622 keys %cust_main_county
624 my $selected = $country eq $selected_country ? ' SELECTED' : '';
625 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
627 $country_html .= '</SELECT>';
629 ($county_html, $state_html, $country_html);
637 regionselector? putting web ui components in here? they should probably live
642 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base