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 if ( $self->dbdef_table->column('taxname') ) {
143 return $self->setfield('taxname', $_[0]) if @_;
144 return $self->getfield('taxname');
151 if ( $self->dbdef_table->column('setuptax') ) {
152 return $self->setfield('setuptax', $_[0]) if @_;
153 return $self->getfield('setuptax');
160 if ( $self->dbdef_table->column('recurtax') ) {
161 return $self->setfield('recurtax', $_[0]) if @_;
162 return $self->getfield('recurtax');
169 Returns a label looking like "Anytown, Alameda County, CA, US".
171 If the taxname field is set, it will look like
172 "CA Sales Tax (Anytown, Alameda County, CA, US)".
174 If the taxclass is set, then it will be
175 "Anytown, Alameda County, CA, US (International)".
177 Currently it will not contain the district, even if the city+county+state
180 OPTIONS may contain "no_taxclass" (hides taxclass) and/or "no_city"
181 (hides city). It may also contain "out", in which case, if this
182 region (district+city+county+state+country) contains no non-zero
183 taxes, the label will read "Out of taxable region(s)".
188 my ($self, %opt) = @_;
191 and !defined(qsearchs('cust_main_county', {
192 'district' => $self->district,
193 'city' => $self->city,
194 'county' => $self->county,
195 'state' => $self->state,
196 'country' => $self->country,
197 'tax' => { op => '>', value => 0 },
200 return 'Out of taxable region(s)';
202 my $label = $self->country;
203 $label = $self->state.", $label" if $self->state;
204 $label = $self->county." County, $label" if $self->county;
205 if (!$opt{no_city}) {
206 $label = $self->city.", $label" if $self->city;
208 # ugly labels when taxclass and taxname are both non-null...
209 # but this is how the tax report does it
210 if (!$opt{no_taxclass}) {
211 $label = "$label (".$self->taxclass.')' if $self->taxclass;
213 $label = $self->taxname." ($label)" if $self->taxname;
218 =item sql_taxclass_sameregion
220 Returns an SQL WHERE fragment or the empty string to search for entries
221 with different tax classes.
225 #hmm, description above could be better...
227 sub sql_taxclass_sameregion {
230 my $same_query = 'SELECT DISTINCT taxclass FROM cust_main_county '.
231 ' WHERE taxnum != ? AND country = ?';
232 my @same_param = ( 'taxnum', 'country' );
233 foreach my $opt_field (qw( state county )) {
234 if ( $self->$opt_field() ) {
235 $same_query .= " AND $opt_field = ?";
236 push @same_param, $opt_field;
238 $same_query .= " AND $opt_field IS NULL";
242 my @taxclasses = $self->_list_sql( \@same_param, $same_query );
244 return '' unless scalar(@taxclasses);
246 '( taxclass IS NULL OR ( '. #only if !$self->taxclass ??
247 join(' AND ', map { 'taxclass != '.dbh->quote($_) } @taxclasses ).
252 my( $self, $param, $sql ) = @_;
253 my $sth = dbh->prepare($sql) or die dbh->errstr;
254 $sth->execute( map $self->$_(), @$param )
255 or die "Unexpected error executing statement $sql: ". $sth->errstr;
256 map $_->[0], @{ $sth->fetchall_arrayref };
259 =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
261 Takes an arrayref of L<FS::cust_bill_pkg> objects representing taxable
262 line items, and returns a new L<FS::cust_bill_pkg> object representing
263 the tax on them under this tax rate.
265 This will have a pseudo-field, "cust_bill_pkg_tax_location", containing
266 an arrayref of L<FS::cust_bill_pkg_tax_location> objects. Each of these
267 will in turn have a "taxable_cust_bill_pkg" pseudo-field linking it to one
268 of the taxable items. All of these links must be resolved as the objects
271 In addition to calculating the tax for the line items, this will calculate
272 any appropriate tax exemptions and attach them to the line items.
274 Options may include 'custnum' and 'invoice_date' in case the cust_bill_pkg
275 objects belong to an invoice that hasn't been inserted yet.
277 Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg>
278 objects belonging to the same customer, to be counted against the monthly
279 tax exemption limit if there is one.
283 # XXX change tax_rate.pm to work like this
286 my( $self, $taxables, %opt ) = @_;
287 return 'taxline called with no line items' unless @$taxables;
289 local $SIG{HUP} = 'IGNORE';
290 local $SIG{INT} = 'IGNORE';
291 local $SIG{QUIT} = 'IGNORE';
292 local $SIG{TERM} = 'IGNORE';
293 local $SIG{TSTP} = 'IGNORE';
294 local $SIG{PIPE} = 'IGNORE';
296 my $oldAutoCommit = $FS::UID::AutoCommit;
297 local $FS::UID::AutoCommit = 0;
300 my $name = $self->taxname || 'Tax';
301 my $taxable_cents = 0;
304 my $cust_bill = $taxables->[0]->cust_bill;
305 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
306 my $invoice_date = $cust_bill ? $cust_bill->_date : $opt{'invoice_date'};
307 my $cust_main = FS::cust_main->by_key($custnum) if $custnum > 0;
309 # better way to handle this? should we just assume that it's taxable?
310 die "unable to calculate taxes for an unknown customer\n";
313 # set a flag if the customer is tax-exempt
315 my $conf = FS::Conf->new;
316 if ( $conf->exists('cust_class-tax_exempt') ) {
317 my $cust_class = $cust_main->cust_class;
318 $exempt_cust = $cust_class->tax if $cust_class;
320 $exempt_cust = $cust_main->tax;
323 # set a flag if the customer is exempt from this tax here
324 my $exempt_cust_taxname = $cust_main->tax_exemption($self->taxname)
327 # Gather any exemptions that are already attached to these cust_bill_pkgs
328 # so that we can deduct them from the customer's monthly limit.
329 my @existing_exemptions = @{ $opt{'exemptions'} };
330 push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
333 my $tax_item = FS::cust_bill_pkg->new({
342 foreach my $cust_bill_pkg (@$taxables) {
344 my $cust_pkg = $cust_bill_pkg->cust_pkg;
345 my $part_pkg = $cust_bill_pkg->part_pkg;
348 my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur
349 or next; # don't create zero-amount exemptions
351 # XXX the following procedure should probably be in cust_bill_pkg
353 if ( $exempt_cust ) {
355 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
356 amount => $taxable_charged,
359 $taxable_charged = 0;
361 } elsif ( $exempt_cust_taxname ) {
363 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
364 amount => $taxable_charged,
365 exempt_cust_taxname => 'Y',
367 $taxable_charged = 0;
371 if ( ($part_pkg->setuptax eq 'Y' or $self->setuptax eq 'Y')
372 and $cust_bill_pkg->setup > 0 and $taxable_charged > 0 ) {
374 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
375 amount => $cust_bill_pkg->setup,
378 $taxable_charged -= $cust_bill_pkg->setup;
381 if ( ($part_pkg->recurtax eq 'Y' or $self->recurtax eq 'Y')
382 and $cust_bill_pkg->recur > 0 and $taxable_charged > 0 ) {
384 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
385 amount => $cust_bill_pkg->recur,
388 $taxable_charged -= $cust_bill_pkg->recur;
392 if ( $self->exempt_amount && $self->exempt_amount > 0
393 and $taxable_charged > 0 ) {
394 #my ($mon,$year) = (localtime($cust_bill_pkg->sdate) )[4,5];
396 (localtime( $cust_bill_pkg->sdate || $invoice_date ) )[4,5];
399 my $freq = $cust_bill_pkg->freq;
401 $freq = $part_pkg->freq || 1; # less trustworthy fallback
403 if ( $freq !~ /(\d+)$/ ) {
404 $dbh->rollback if $oldAutoCommit;
405 return "daily/weekly package definitions not (yet?)".
406 " compatible with monthly tax exemptions";
408 my $taxable_per_month =
409 sprintf("%.2f", $taxable_charged / $freq );
411 #call the whole thing off if this customer has any old
412 #exemption records...
413 my @cust_tax_exempt =
414 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
415 if ( @cust_tax_exempt ) {
416 $dbh->rollback if $oldAutoCommit;
418 'this customer still has old-style tax exemption records; '.
419 'run bin/fs-migrate-cust_tax_exempt?';
422 foreach my $which_month ( 1 .. $freq ) {
424 #maintain the new exemption table now
427 FROM cust_tax_exempt_pkg
428 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
429 LEFT JOIN cust_bill USING ( invnum )
434 AND exempt_monthly = 'Y'
436 my $sth = dbh->prepare($sql) or do {
437 $dbh->rollback if $oldAutoCommit;
438 return "fatal: can't lookup exising exemption: ". dbh->errstr;
446 $dbh->rollback if $oldAutoCommit;
447 return "fatal: can't lookup exising exemption: ". dbh->errstr;
449 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
451 foreach ( grep { $_->taxnum == $self->taxnum &&
452 $_->exempt_monthly eq 'Y' &&
455 } @existing_exemptions
458 $existing_exemption += $_->amount;
461 my $remaining_exemption =
462 $self->exempt_amount - $existing_exemption;
463 if ( $remaining_exemption > 0 ) {
464 my $addl = $remaining_exemption > $taxable_per_month
466 : $remaining_exemption;
467 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
468 amount => sprintf('%.2f', $addl),
469 exempt_monthly => 'Y',
473 $taxable_charged -= $addl;
475 last if $taxable_charged < 0.005;
476 # if they're using multiple months of exemption for a multi-month
477 # package, then record the exemptions in separate months
484 } #foreach $which_month
487 $_->taxnum($self->taxnum) foreach @new_exemptions;
489 # attach them to the line item
490 push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions;
491 push @existing_exemptions, @new_exemptions;
493 $taxable_charged = sprintf( "%.2f", $taxable_charged);
494 next if $taxable_charged == 0;
496 my $this_tax_cents = int($taxable_charged * $self->tax);
497 my $location = FS::cust_bill_pkg_tax_location->new({
498 'taxnum' => $self->taxnum,
499 'taxtype' => ref($self),
500 'cents' => $this_tax_cents,
501 'pkgnum' => $cust_bill_pkg->pkgnum,
502 'locationnum' => $cust_bill_pkg->cust_pkg->tax_locationnum,
503 'taxable_cust_bill_pkg' => $cust_bill_pkg,
504 'tax_cust_bill_pkg' => $tax_item,
506 push @tax_location, $location;
508 $taxable_cents += $taxable_charged;
509 $tax_cents += $this_tax_cents;
510 } #foreach $cust_bill_pkg
512 # now round and distribute
513 my $extra_cents = sprintf('%.2f', $taxable_cents * $self->tax / 100) * 100
515 if ( $extra_cents < 0 ) {
516 die "nonsense extra_cents value $extra_cents"; # because seriously, wtf
518 $tax_cents += $extra_cents;
520 foreach (@tax_location) { # can never require more than a single pass, yes?
521 my $cents = $_->get('cents');
522 if ( $extra_cents > 0 ) {
526 $_->set('amount', sprintf('%.2f', $cents/100));
528 $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100));
529 $tax_item->set('cust_bill_pkg_tax_location', \@tax_location);
540 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
545 my ( $selected_county, $selected_state, $selected_country,
546 $prefix, $onchange, $disabled ) = @_;
548 $prefix = '' unless defined $prefix;
552 # unless ( @cust_main_county ) { #cache
553 @cust_main_county = qsearch('cust_main_county', {} );
554 foreach my $c ( @cust_main_county ) {
555 $countyflag=1 if $c->county;
556 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
557 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
560 $countyflag=1 if $selected_county;
562 my $script_html = <<END;
564 function opt(what,value,text) {
565 var optionName = new Option(text, value, false, false);
566 var length = what.length;
567 what.options[length] = optionName;
569 function ${prefix}country_changed(what) {
570 country = what.options[what.selectedIndex].text;
571 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
572 what.form.${prefix}state.options[i] = null;
574 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
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 ( my $dstate = $state ) =~ s/[\n\r]//g;
580 my $text = $dstate || '(n/a)';
581 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
583 $script_html .= "}\n";
586 $script_html .= <<END;
588 function ${prefix}state_changed(what) {
592 $script_html .= <<END;
593 state = what.options[what.selectedIndex].text;
594 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
595 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
596 what.form.${prefix}county.options[i] = null;
599 foreach my $country ( sort keys %cust_main_county ) {
600 $script_html .= "\nif ( country == \"$country\" ) {\n";
601 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
602 $script_html .= "\nif ( state == \"$state\" ) {\n";
603 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
604 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
605 my $text = $county || '(n/a)';
607 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
609 $script_html .= "}\n";
611 $script_html .= "}\n";
615 $script_html .= <<END;
620 my $county_html = $script_html;
622 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
623 $county_html .= '</SELECT>';
626 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
629 my $state_html = qq!<SELECT NAME="${prefix}state" !.
630 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
631 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
632 my $text = $state || '(n/a)';
633 my $selected = $state eq $selected_state ? 'SELECTED' : '';
634 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
636 $state_html .= '</SELECT>';
638 $state_html .= '</SELECT>';
640 my $country_html = qq!<SELECT NAME="${prefix}country" !.
641 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
642 my $countrydefault = $conf->config('countrydefault') || 'US';
643 foreach my $country (
644 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
645 keys %cust_main_county
647 my $selected = $country eq $selected_country ? ' SELECTED' : '';
648 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
650 $country_html .= '</SELECT>';
652 ($county_html, $state_html, $country_html);
660 regionselector? putting web ui components in here? they should probably live
665 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base