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 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)
67 =item tax - percentage
73 =item taxname - if defined, printed on invoices instead of "Tax"
75 =item setuptax - if 'Y', this tax does not apply to setup fees
77 =item recurtax - if 'Y', this tax does not apply to recurring fees
87 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
91 sub table { 'cust_main_county'; }
95 Adds this tax rate to the database. If there is an error, returns the error,
96 otherwise returns false.
100 Deletes this tax rate from the database. If there is an error, returns the
101 error, otherwise returns false.
103 =item replace OLD_RECORD
105 Replaces the OLD_RECORD with this one in the database. If there is an error,
106 returns the error, otherwise returns false.
110 Checks all fields to make sure this is a valid tax rate. If there is an error,
111 returns the error, otherwise returns false. Called by the insert and replace
119 $self->exempt_amount(0) unless $self->exempt_amount;
121 $self->ut_numbern('taxnum')
122 || $self->ut_textn('city')
123 || $self->ut_textn('county')
124 || $self->ut_anything('state')
125 || $self->ut_text('country')
126 || $self->ut_float('tax')
127 || $self->ut_textn('taxclass') # ...
128 || $self->ut_money('exempt_amount')
129 || $self->ut_textn('taxname')
130 || $self->ut_enum('setuptax', [ '', 'Y' ] )
131 || $self->ut_enum('recurtax', [ '', 'Y' ] )
132 || $self->SUPER::check
139 if ( $self->dbdef_table->column('taxname') ) {
140 return $self->setfield('taxname', $_[0]) if @_;
141 return $self->getfield('taxname');
148 if ( $self->dbdef_table->column('setuptax') ) {
149 return $self->setfield('setuptax', $_[0]) if @_;
150 return $self->getfield('setuptax');
157 if ( $self->dbdef_table->column('recurtax') ) {
158 return $self->setfield('recurtax', $_[0]) if @_;
159 return $self->getfield('recurtax');
164 =item sql_taxclass_sameregion
166 Returns an SQL WHERE fragment or the empty string to search for entries
167 with different tax classes.
171 #hmm, description above could be better...
173 sub sql_taxclass_sameregion {
176 my $same_query = 'SELECT taxclass FROM cust_main_county '.
177 ' WHERE taxnum != ? AND country = ?';
178 my @same_param = ( 'taxnum', 'country' );
179 foreach my $opt_field (qw( state county )) {
180 if ( $self->$opt_field() ) {
181 $same_query .= " AND $opt_field = ?";
182 push @same_param, $opt_field;
184 $same_query .= " AND $opt_field IS NULL";
188 my @taxclasses = $self->_list_sql( \@same_param, $same_query );
190 return '' unless scalar(@taxclasses);
192 '( taxclass IS NULL OR ( '. #only if !$self->taxclass ??
193 join(' AND ', map { 'taxclass != '.dbh->quote($_) } @taxclasses ).
198 my( $self, $param, $sql ) = @_;
199 my $sth = dbh->prepare($sql) or die dbh->errstr;
200 $sth->execute( map $self->$_(), @$param )
201 or die "Unexpected error executing statement $sql: ". $sth->errstr;
202 map $_->[0], @{ $sth->fetchall_arrayref };
205 =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
207 Returns a listref of a name and an amount of tax calculated for the list of
208 packages or amounts referenced by TAXABLES_ARRAYREF. Returns a scalar error
211 Options include custnum and invoice_date and are hints to this method
216 my( $self, $taxables, %opt ) = @_;
219 push @exemptions, @{ $_->_cust_tax_exempt_pkg }
220 for grep { ref($_) } @$taxables;
222 local $SIG{HUP} = 'IGNORE';
223 local $SIG{INT} = 'IGNORE';
224 local $SIG{QUIT} = 'IGNORE';
225 local $SIG{TERM} = 'IGNORE';
226 local $SIG{TSTP} = 'IGNORE';
227 local $SIG{PIPE} = 'IGNORE';
229 my $oldAutoCommit = $FS::UID::AutoCommit;
230 local $FS::UID::AutoCommit = 0;
233 my $name = $self->taxname || 'Tax';
236 foreach my $cust_bill_pkg (@$taxables) {
238 my $cust_pkg = $cust_bill_pkg->cust_pkg;
239 my $cust_bill = $cust_pkg->cust_bill if $cust_pkg;
240 my $custnum = $cust_pkg ? $cust_pkg->custnum : $opt{custnum};
241 my $part_pkg = $cust_bill_pkg->part_pkg;
242 my $invoice_date = $cust_bill ? $cust_bill->_date : $opt{invoice_date};
244 my $taxable_charged = 0;
245 $taxable_charged += $cust_bill_pkg->setup
246 unless $part_pkg->setuptax =~ /^Y$/i
247 || $self->setuptax =~ /^Y$/i;
248 $taxable_charged += $cust_bill_pkg->recur
249 unless $part_pkg->recurtax =~ /^Y$/i
250 || $self->recurtax =~ /^Y$/i;
252 next unless $taxable_charged;
254 if ( $self->exempt_amount && $self->exempt_amount > 0 ) {
255 #my ($mon,$year) = (localtime($cust_bill_pkg->sdate) )[4,5];
257 (localtime( $cust_bill_pkg->sdate || $invoice_date ) )[4,5];
259 my $freq = $cust_bill_pkg->freq;
261 $freq = $part_pkg->freq || 1; # less trustworthy fallback
263 if ( $freq !~ /(\d+)$/ ) {
264 $dbh->rollback if $oldAutoCommit;
265 return "daily/weekly package definitions not (yet?)".
266 " compatible with monthly tax exemptions";
268 my $taxable_per_month =
269 sprintf("%.2f", $taxable_charged / $freq );
271 #call the whole thing off if this customer has any old
272 #exemption records...
273 my @cust_tax_exempt =
274 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
275 if ( @cust_tax_exempt ) {
276 $dbh->rollback if $oldAutoCommit;
278 'this customer still has old-style tax exemption records; '.
279 'run bin/fs-migrate-cust_tax_exempt?';
282 foreach my $which_month ( 1 .. $freq ) {
284 #maintain the new exemption table now
287 FROM cust_tax_exempt_pkg
288 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
289 LEFT JOIN cust_bill USING ( invnum )
295 my $sth = dbh->prepare($sql) or do {
296 $dbh->rollback if $oldAutoCommit;
297 return "fatal: can't lookup exising exemption: ". dbh->errstr;
305 $dbh->rollback if $oldAutoCommit;
306 return "fatal: can't lookup exising exemption: ". dbh->errstr;
308 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
310 foreach ( grep { $_->taxnum == $self->taxnum &&
312 $_->year == 1900+$year
316 $existing_exemption += $_->amount;
319 my $remaining_exemption =
320 $self->exempt_amount - $existing_exemption;
321 if ( $remaining_exemption > 0 ) {
322 my $addl = $remaining_exemption > $taxable_per_month
324 : $remaining_exemption;
325 $taxable_charged -= $addl;
327 my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg ( {
328 'taxnum' => $self->taxnum,
329 'year' => 1900+$year,
331 'amount' => sprintf("%.2f", $addl ),
333 if ($cust_bill_pkg->billpkgnum) {
334 $cust_tax_exempt_pkg->billpkgnum($cust_bill_pkg->billpkgnum);
335 my $error = $cust_tax_exempt_pkg->insert;
337 $dbh->rollback if $oldAutoCommit;
338 return "fatal: can't insert cust_tax_exempt_pkg: $error";
341 push @exemptions, $cust_tax_exempt_pkg;
342 push @{ $cust_bill_pkg->_cust_tax_exempt_pkg }, $cust_tax_exempt_pkg;
343 } # if $cust_bill_pkg->billpkgnum
344 } # if $remaining_exemption > 0
348 #until ( $mon < 12 ) { $mon -= 12; $year++; }
349 until ( $mon < 13 ) { $mon -= 12; $year++; }
351 } #foreach $which_month
353 } #if $tax->exempt_amount
355 $taxable_charged = sprintf( "%.2f", $taxable_charged);
357 $amount += $taxable_charged * $self->tax / 100
360 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
375 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
380 my ( $selected_county, $selected_state, $selected_country,
381 $prefix, $onchange, $disabled ) = @_;
383 $prefix = '' unless defined $prefix;
387 # unless ( @cust_main_county ) { #cache
388 @cust_main_county = qsearch('cust_main_county', {} );
389 foreach my $c ( @cust_main_county ) {
390 $countyflag=1 if $c->county;
391 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
392 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
395 $countyflag=1 if $selected_county;
397 my $script_html = <<END;
399 function opt(what,value,text) {
400 var optionName = new Option(text, value, false, false);
401 var length = what.length;
402 what.options[length] = optionName;
404 function ${prefix}country_changed(what) {
405 country = what.options[what.selectedIndex].text;
406 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
407 what.form.${prefix}state.options[i] = null;
409 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
411 foreach my $country ( sort keys %cust_main_county ) {
412 $script_html .= "\nif ( country == \"$country\" ) {\n";
413 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
414 ( my $dstate = $state ) =~ s/[\n\r]//g;
415 my $text = $dstate || '(n/a)';
416 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
418 $script_html .= "}\n";
421 $script_html .= <<END;
423 function ${prefix}state_changed(what) {
427 $script_html .= <<END;
428 state = what.options[what.selectedIndex].text;
429 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
430 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
431 what.form.${prefix}county.options[i] = null;
434 foreach my $country ( sort keys %cust_main_county ) {
435 $script_html .= "\nif ( country == \"$country\" ) {\n";
436 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
437 $script_html .= "\nif ( state == \"$state\" ) {\n";
438 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
439 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
440 my $text = $county || '(n/a)';
442 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
444 $script_html .= "}\n";
446 $script_html .= "}\n";
450 $script_html .= <<END;
455 my $county_html = $script_html;
457 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
458 $county_html .= '</SELECT>';
461 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
464 my $state_html = qq!<SELECT NAME="${prefix}state" !.
465 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
466 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
467 my $text = $state || '(n/a)';
468 my $selected = $state eq $selected_state ? 'SELECTED' : '';
469 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
471 $state_html .= '</SELECT>';
473 $state_html .= '</SELECT>';
475 my $country_html = qq!<SELECT NAME="${prefix}country" !.
476 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
477 my $countrydefault = $conf->config('countrydefault') || 'US';
478 foreach my $country (
479 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
480 keys %cust_main_county
482 my $selected = $country eq $selected_country ? ' SELECTED' : '';
483 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
485 $country_html .= '</SELECT>';
487 ($county_html, $state_html, $country_html);
495 regionselector? putting web ui components in here? they should probably live
500 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base