1 package FS::cust_main_county;
4 use vars qw( @ISA @EXPORT_OK $conf
5 @cust_main_county %cust_main_county $countyflag );
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 = ();
21 #ask FS::UID to run this stuff for us later
22 $FS::UID::callback{'FS::cust_main_county'} = sub {
28 FS::cust_main_county - Object methods for cust_main_county objects
32 use FS::cust_main_county;
34 $record = new FS::cust_main_county \%hash;
35 $record = new FS::cust_main_county { 'column' => 'value' };
37 $error = $record->insert;
39 $error = $new_record->replace($old_record);
41 $error = $record->delete;
43 $error = $record->check;
45 ($county_html, $state_html, $country_html) =
46 FS::cust_main_county::regionselector( $county, $state, $country );
50 An FS::cust_main_county object represents a tax rate, defined by locale.
51 FS::cust_main_county inherits from FS::Record. The following fields are
56 =item taxnum - primary key (assigned automatically for new tax rates)
64 =item tax - percentage
70 =item taxname - if defined, printed on invoices instead of "Tax"
72 =item setuptax - if 'Y', this tax does not apply to setup fees
74 =item recurtax - if 'Y', this tax does not apply to recurring fees
84 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
88 sub table { 'cust_main_county'; }
92 Adds this tax rate to the database. If there is an error, returns the error,
93 otherwise returns false.
97 Deletes this tax rate from the database. If there is an error, returns the
98 error, otherwise returns false.
100 =item replace OLD_RECORD
102 Replaces the OLD_RECORD with this one in the database. If there is an error,
103 returns the error, otherwise returns false.
107 Checks all fields to make sure this is a valid tax rate. If there is an error,
108 returns the error, otherwise returns false. Called by the insert and replace
116 $self->exempt_amount(0) unless $self->exempt_amount;
118 $self->ut_numbern('taxnum')
119 || $self->ut_anything('state')
120 || $self->ut_textn('county')
121 || $self->ut_text('country')
122 || $self->ut_float('tax')
123 || $self->ut_textn('taxclass') # ...
124 || $self->ut_money('exempt_amount')
125 || $self->ut_textn('taxname')
126 || $self->ut_enum('setuptax', [ '', 'Y' ] )
127 || $self->ut_enum('recurtax', [ '', 'Y' ] )
128 || $self->SUPER::check
135 if ( $self->dbdef_table->column('taxname') ) {
136 return $self->setfield('taxname', $_[0]) if @_;
137 return $self->getfield('taxname');
144 if ( $self->dbdef_table->column('setuptax') ) {
145 return $self->setfield('setuptax', $_[0]) if @_;
146 return $self->getfield('setuptax');
153 if ( $self->dbdef_table->column('recurtax') ) {
154 return $self->setfield('recurtax', $_[0]) if @_;
155 return $self->getfield('recurtax');
160 =item sql_taxclass_sameregion
162 Returns an SQL WHERE fragment or the empty string to search for entries
163 with different tax classes.
167 #hmm, description above could be better...
169 sub sql_taxclass_sameregion {
172 my $same_query = 'SELECT taxclass FROM cust_main_county '.
173 ' WHERE taxnum != ? AND country = ?';
174 my @same_param = ( 'taxnum', 'country' );
175 foreach my $opt_field (qw( state county )) {
176 if ( $self->$opt_field() ) {
177 $same_query .= " AND $opt_field = ?";
178 push @same_param, $opt_field;
180 $same_query .= " AND $opt_field IS NULL";
184 my @taxclasses = $self->_list_sql( \@same_param, $same_query );
186 return '' unless scalar(@taxclasses);
188 '( taxclass IS NULL OR ( '. #only if !$self->taxclass ??
189 join(' AND ', map { 'taxclass != '.dbh->quote($_) } @taxclasses ).
194 my( $self, $param, $sql ) = @_;
195 my $sth = dbh->prepare($sql) or die dbh->errstr;
196 $sth->execute( map $self->$_(), @$param )
197 or die "Unexpected error executing statement $sql: ". $sth->errstr;
198 map $_->[0], @{ $sth->fetchall_arrayref };
201 =item taxline TAXABLES, [ OPTIONSHASH ]
203 Returns a listref of a name and an amount of tax calculated for the list of
204 packages or amounts referenced by TAXABLES. Returns a scalar error message
207 OPTIONSHASH includes custnum and invoice_date and are hints to this method
217 if (ref($_[0]) eq 'ARRAY') {
222 # exemptions broken in this case
226 push @exemptions, @{ $_->_cust_tax_exempt_pkg }
227 for grep { ref($_) } @$taxables;
229 local $SIG{HUP} = 'IGNORE';
230 local $SIG{INT} = 'IGNORE';
231 local $SIG{QUIT} = 'IGNORE';
232 local $SIG{TERM} = 'IGNORE';
233 local $SIG{TSTP} = 'IGNORE';
234 local $SIG{PIPE} = 'IGNORE';
236 my $oldAutoCommit = $FS::UID::AutoCommit;
237 local $FS::UID::AutoCommit = 0;
240 my $name = $self->taxname || 'Tax';
243 foreach my $cust_bill_pkg (@$taxables) {
245 my $cust_pkg = $cust_bill_pkg->cust_pkg;
246 my $cust_bill = $cust_pkg->cust_bill if $cust_pkg;
247 my $custnum = $cust_pkg ? $cust_pkg->custnum : $opt{custnum};
248 my $part_pkg = $cust_bill_pkg->part_pkg;
249 my $invoice_date = $cust_bill ? $cust_bill->_date : $opt{invoice_date};
251 my $taxable_charged = 0;
252 $taxable_charged += $cust_bill_pkg->setup
253 unless $part_pkg->setuptax =~ /^Y$/i
254 || $self->setuptax =~ /^Y$/i;
255 $taxable_charged += $cust_bill_pkg->recur
256 unless $part_pkg->recurtax =~ /^Y$/i
257 || $self->recurtax =~ /^Y$/i;
259 next unless $taxable_charged;
261 if ( $self->exempt_amount && $self->exempt_amount > 0 ) {
262 #my ($mon,$year) = (localtime($cust_bill_pkg->sdate) )[4,5];
264 (localtime( $cust_bill_pkg->sdate || $invoice_date ) )[4,5];
266 my $freq = $part_pkg->freq || 1;
267 if ( $freq !~ /(\d+)$/ ) {
268 $dbh->rollback if $oldAutoCommit;
269 return "daily/weekly package definitions not (yet?)".
270 " compatible with monthly tax exemptions";
272 my $taxable_per_month =
273 sprintf("%.2f", $taxable_charged / $freq );
275 #call the whole thing off if this customer has any old
276 #exemption records...
277 my @cust_tax_exempt =
278 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
279 if ( @cust_tax_exempt ) {
280 $dbh->rollback if $oldAutoCommit;
282 'this customer still has old-style tax exemption records; '.
283 'run bin/fs-migrate-cust_tax_exempt?';
286 foreach my $which_month ( 1 .. $freq ) {
288 #maintain the new exemption table now
291 FROM cust_tax_exempt_pkg
292 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
293 LEFT JOIN cust_bill USING ( invnum )
299 my $sth = dbh->prepare($sql) or do {
300 $dbh->rollback if $oldAutoCommit;
301 return "fatal: can't lookup exising exemption: ". dbh->errstr;
309 $dbh->rollback if $oldAutoCommit;
310 return "fatal: can't lookup exising exemption: ". dbh->errstr;
312 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
314 foreach ( grep { $_->taxnum == $self->taxnum &&
316 $_->year == 1900+$year
320 $existing_exemption += $_->amount;
323 my $remaining_exemption =
324 $self->exempt_amount - $existing_exemption;
325 if ( $remaining_exemption > 0 ) {
326 my $addl = $remaining_exemption > $taxable_per_month
328 : $remaining_exemption;
329 $taxable_charged -= $addl;
331 my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg ( {
332 'taxnum' => $self->taxnum,
333 'year' => 1900+$year,
335 'amount' => sprintf("%.2f", $addl ),
337 if ($cust_bill_pkg->billpkgnum) {
338 $cust_tax_exempt_pkg->billpkgnum($cust_bill_pkg->billpkgnum);
339 my $error = $cust_tax_exempt_pkg->insert;
341 $dbh->rollback if $oldAutoCommit;
342 return "fatal: can't insert cust_tax_exempt_pkg: $error";
345 push @exemptions, $cust_tax_exempt_pkg;
346 push @{ $cust_bill_pkg->_cust_tax_exempt_pkg }, $cust_tax_exempt_pkg;
347 } # if $cust_bill_pkg->billpkgnum
348 } # if $remaining_exemption > 0
352 #until ( $mon < 12 ) { $mon -= 12; $year++; }
353 until ( $mon < 13 ) { $mon -= 12; $year++; }
355 } #foreach $which_month
357 } #if $tax->exempt_amount
359 $taxable_charged = sprintf( "%.2f", $taxable_charged);
361 $amount += $taxable_charged * $self->tax / 100
364 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
365 return [ $name, $amount ]
374 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
379 my ( $selected_county, $selected_state, $selected_country,
380 $prefix, $onchange, $disabled ) = @_;
382 $prefix = '' unless defined $prefix;
386 # unless ( @cust_main_county ) { #cache
387 @cust_main_county = qsearch('cust_main_county', {} );
388 foreach my $c ( @cust_main_county ) {
389 $countyflag=1 if $c->county;
390 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
391 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
394 $countyflag=1 if $selected_county;
396 my $script_html = <<END;
398 function opt(what,value,text) {
399 var optionName = new Option(text, value, false, false);
400 var length = what.length;
401 what.options[length] = optionName;
403 function ${prefix}country_changed(what) {
404 country = what.options[what.selectedIndex].text;
405 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
406 what.form.${prefix}state.options[i] = null;
408 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
410 foreach my $country ( sort keys %cust_main_county ) {
411 $script_html .= "\nif ( country == \"$country\" ) {\n";
412 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
413 ( my $dstate = $state ) =~ s/[\n\r]//g;
414 my $text = $dstate || '(n/a)';
415 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
417 $script_html .= "}\n";
420 $script_html .= <<END;
422 function ${prefix}state_changed(what) {
426 $script_html .= <<END;
427 state = what.options[what.selectedIndex].text;
428 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
429 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
430 what.form.${prefix}county.options[i] = null;
433 foreach my $country ( sort keys %cust_main_county ) {
434 $script_html .= "\nif ( country == \"$country\" ) {\n";
435 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
436 $script_html .= "\nif ( state == \"$state\" ) {\n";
437 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
438 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
439 my $text = $county || '(n/a)';
441 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
443 $script_html .= "}\n";
445 $script_html .= "}\n";
449 $script_html .= <<END;
454 my $county_html = $script_html;
456 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
457 $county_html .= '</SELECT>';
460 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
463 my $state_html = qq!<SELECT NAME="${prefix}state" !.
464 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
465 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
466 my $text = $state || '(n/a)';
467 my $selected = $state eq $selected_state ? 'SELECTED' : '';
468 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
470 $state_html .= '</SELECT>';
472 $state_html .= '</SELECT>';
474 my $country_html = qq!<SELECT NAME="${prefix}country" !.
475 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
476 my $countrydefault = $conf->config('countrydefault') || 'US';
477 foreach my $country (
478 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
479 keys %cust_main_county
481 my $selected = $country eq $selected_country ? ' SELECTED' : '';
482 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
484 $country_html .= '</SELECT>';
486 ($county_html, $state_html, $country_html);
494 regionselector? putting web ui components in here? they should probably live
499 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base