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 Returns an hashref of a name and an amount of tax calculated for the
262 line items (L<FS::cust_bill_pkg> objects) in TAXABLES_ARRAYREF. The line
263 items must come from the same invoice. Returns a scalar error message
266 In addition to calculating the tax for the line items, this will calculate
267 any appropriate tax exemptions and attach them to the line items.
269 Options may include 'custnum' and 'invoice_date' in case the cust_bill_pkg
270 objects belong to an invoice that hasn't been inserted yet.
272 Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg>
273 objects belonging to the same customer, to be counted against the monthly
274 tax exemption limit if there is one.
278 # XXX this should just return a cust_bill_pkg object for the tax,
279 # but that requires changing stuff in tax_rate.pm also.
282 my( $self, $taxables, %opt ) = @_;
283 return 'taxline called with no line items' unless @$taxables;
285 local $SIG{HUP} = 'IGNORE';
286 local $SIG{INT} = 'IGNORE';
287 local $SIG{QUIT} = 'IGNORE';
288 local $SIG{TERM} = 'IGNORE';
289 local $SIG{TSTP} = 'IGNORE';
290 local $SIG{PIPE} = 'IGNORE';
292 my $oldAutoCommit = $FS::UID::AutoCommit;
293 local $FS::UID::AutoCommit = 0;
296 my $name = $self->taxname || 'Tax';
299 my $cust_bill = $taxables->[0]->cust_bill;
300 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
301 my $invoice_date = $cust_bill ? $cust_bill->_date : $opt{'invoice_date'};
302 my $cust_main = FS::cust_main->by_key($custnum) if $custnum > 0;
304 # better way to handle this? should we just assume that it's taxable?
305 die "unable to calculate taxes for an unknown customer\n";
308 # set a flag if the customer is tax-exempt
310 my $conf = FS::Conf->new;
311 if ( $conf->exists('cust_class-tax_exempt') ) {
312 my $cust_class = $cust_main->cust_class;
313 $exempt_cust = $cust_class->tax if $cust_class;
315 $exempt_cust = $cust_main->tax;
318 # set a flag if the customer is exempt from this tax here
319 my $exempt_cust_taxname = $cust_main->tax_exemption($self->taxname)
322 # Gather any exemptions that are already attached to these cust_bill_pkgs
323 # so that we can deduct them from the customer's monthly limit.
324 my @existing_exemptions = @{ $opt{'exemptions'} };
325 push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
328 foreach my $cust_bill_pkg (@$taxables) {
330 my $cust_pkg = $cust_bill_pkg->cust_pkg;
331 my $part_pkg = $cust_bill_pkg->part_pkg;
334 my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur
335 or next; # don't create zero-amount exemptions
337 # XXX the following procedure should probably be in cust_bill_pkg
339 if ( $exempt_cust ) {
341 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
342 amount => $taxable_charged,
345 $taxable_charged = 0;
347 } elsif ( $exempt_cust_taxname ) {
349 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
350 amount => $taxable_charged,
351 exempt_cust_taxname => 'Y',
353 $taxable_charged = 0;
357 if ( ($part_pkg->setuptax eq 'Y' or $self->setuptax eq 'Y')
358 and $cust_bill_pkg->setup > 0 and $taxable_charged > 0 ) {
360 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
361 amount => $cust_bill_pkg->setup,
364 $taxable_charged -= $cust_bill_pkg->setup;
367 if ( ($part_pkg->recurtax eq 'Y' or $self->recurtax eq 'Y')
368 and $cust_bill_pkg->recur > 0 and $taxable_charged > 0 ) {
370 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
371 amount => $cust_bill_pkg->recur,
374 $taxable_charged -= $cust_bill_pkg->recur;
378 if ( $self->exempt_amount && $self->exempt_amount > 0
379 and $taxable_charged > 0 ) {
380 #my ($mon,$year) = (localtime($cust_bill_pkg->sdate) )[4,5];
382 (localtime( $cust_bill_pkg->sdate || $invoice_date ) )[4,5];
385 my $freq = $cust_bill_pkg->freq;
387 $freq = $part_pkg->freq || 1; # less trustworthy fallback
389 if ( $freq !~ /(\d+)$/ ) {
390 $dbh->rollback if $oldAutoCommit;
391 return "daily/weekly package definitions not (yet?)".
392 " compatible with monthly tax exemptions";
394 my $taxable_per_month =
395 sprintf("%.2f", $taxable_charged / $freq );
397 #call the whole thing off if this customer has any old
398 #exemption records...
399 my @cust_tax_exempt =
400 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
401 if ( @cust_tax_exempt ) {
402 $dbh->rollback if $oldAutoCommit;
404 'this customer still has old-style tax exemption records; '.
405 'run bin/fs-migrate-cust_tax_exempt?';
408 foreach my $which_month ( 1 .. $freq ) {
410 #maintain the new exemption table now
413 FROM cust_tax_exempt_pkg
414 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
415 LEFT JOIN cust_bill USING ( invnum )
420 AND exempt_monthly = 'Y'
422 my $sth = dbh->prepare($sql) or do {
423 $dbh->rollback if $oldAutoCommit;
424 return "fatal: can't lookup exising exemption: ". dbh->errstr;
432 $dbh->rollback if $oldAutoCommit;
433 return "fatal: can't lookup exising exemption: ". dbh->errstr;
435 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
437 foreach ( grep { $_->taxnum == $self->taxnum &&
438 $_->exempt_monthly eq 'Y' &&
441 } @existing_exemptions
444 $existing_exemption += $_->amount;
447 my $remaining_exemption =
448 $self->exempt_amount - $existing_exemption;
449 if ( $remaining_exemption > 0 ) {
450 my $addl = $remaining_exemption > $taxable_per_month
452 : $remaining_exemption;
453 push @new_exemptions, FS::cust_tax_exempt_pkg->new({
454 amount => sprintf('%.2f', $addl),
455 exempt_monthly => 'Y',
459 $taxable_charged -= $addl;
461 last if $taxable_charged < 0.005;
462 # if they're using multiple months of exemption for a multi-month
463 # package, then record the exemptions in separate months
470 } #foreach $which_month
473 $_->taxnum($self->taxnum) foreach @new_exemptions;
475 #if ( $cust_bill_pkg->billpkgnum ) {
477 #no, need to do this to e.g. calculate tax credit amounts
478 #die "tried to calculate tax exemptions on a previously billed line item\n";
480 # this is unnecessary
481 # foreach my $cust_tax_exempt_pkg (@new_exemptions) {
482 # my $error = $cust_tax_exempt_pkg->insert;
484 # $dbh->rollback if $oldAutoCommit;
485 # return "can't insert cust_tax_exempt_pkg: $error";
490 # attach them to the line item
491 push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions;
492 push @existing_exemptions, @new_exemptions;
494 # If we were smart, we'd also generate a cust_bill_pkg_tax_location
495 # record at this point, but that would require redesigning more stuff.
496 $taxable_charged = sprintf( "%.2f", $taxable_charged);
498 $amount += $taxable_charged * $self->tax / 100;
499 } #foreach $cust_bill_pkg
514 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
519 my ( $selected_county, $selected_state, $selected_country,
520 $prefix, $onchange, $disabled ) = @_;
522 $prefix = '' unless defined $prefix;
526 # unless ( @cust_main_county ) { #cache
527 @cust_main_county = qsearch('cust_main_county', {} );
528 foreach my $c ( @cust_main_county ) {
529 $countyflag=1 if $c->county;
530 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
531 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
534 $countyflag=1 if $selected_county;
536 my $script_html = <<END;
538 function opt(what,value,text) {
539 var optionName = new Option(text, value, false, false);
540 var length = what.length;
541 what.options[length] = optionName;
543 function ${prefix}country_changed(what) {
544 country = what.options[what.selectedIndex].text;
545 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
546 what.form.${prefix}state.options[i] = null;
548 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
550 foreach my $country ( sort keys %cust_main_county ) {
551 $script_html .= "\nif ( country == \"$country\" ) {\n";
552 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
553 ( my $dstate = $state ) =~ s/[\n\r]//g;
554 my $text = $dstate || '(n/a)';
555 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
557 $script_html .= "}\n";
560 $script_html .= <<END;
562 function ${prefix}state_changed(what) {
566 $script_html .= <<END;
567 state = what.options[what.selectedIndex].text;
568 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
569 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
570 what.form.${prefix}county.options[i] = null;
573 foreach my $country ( sort keys %cust_main_county ) {
574 $script_html .= "\nif ( country == \"$country\" ) {\n";
575 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
576 $script_html .= "\nif ( state == \"$state\" ) {\n";
577 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
578 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
579 my $text = $county || '(n/a)';
581 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
583 $script_html .= "}\n";
585 $script_html .= "}\n";
589 $script_html .= <<END;
594 my $county_html = $script_html;
596 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
597 $county_html .= '</SELECT>';
600 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
603 my $state_html = qq!<SELECT NAME="${prefix}state" !.
604 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
605 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
606 my $text = $state || '(n/a)';
607 my $selected = $state eq $selected_state ? 'SELECTED' : '';
608 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
610 $state_html .= '</SELECT>';
612 $state_html .= '</SELECT>';
614 my $country_html = qq!<SELECT NAME="${prefix}country" !.
615 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
616 my $countrydefault = $conf->config('countrydefault') || 'US';
617 foreach my $country (
618 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
619 keys %cust_main_county
621 my $selected = $country eq $selected_country ? ' SELECTED' : '';
622 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
624 $country_html .= '</SELECT>';
626 ($county_html, $state_html, $country_html);
634 regionselector? putting web ui components in here? they should probably live
639 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base