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 = $part_pkg->freq || 1;
260 if ( $freq !~ /(\d+)$/ ) {
261 $dbh->rollback if $oldAutoCommit;
262 return "daily/weekly package definitions not (yet?)".
263 " compatible with monthly tax exemptions";
265 my $taxable_per_month =
266 sprintf("%.2f", $taxable_charged / $freq );
268 #call the whole thing off if this customer has any old
269 #exemption records...
270 my @cust_tax_exempt =
271 qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
272 if ( @cust_tax_exempt ) {
273 $dbh->rollback if $oldAutoCommit;
275 'this customer still has old-style tax exemption records; '.
276 'run bin/fs-migrate-cust_tax_exempt?';
279 foreach my $which_month ( 1 .. $freq ) {
281 #maintain the new exemption table now
284 FROM cust_tax_exempt_pkg
285 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
286 LEFT JOIN cust_bill USING ( invnum )
292 my $sth = dbh->prepare($sql) or do {
293 $dbh->rollback if $oldAutoCommit;
294 return "fatal: can't lookup exising exemption: ". dbh->errstr;
302 $dbh->rollback if $oldAutoCommit;
303 return "fatal: can't lookup exising exemption: ". dbh->errstr;
305 my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
307 foreach ( grep { $_->taxnum == $self->taxnum &&
309 $_->year == 1900+$year
313 $existing_exemption += $_->amount;
316 my $remaining_exemption =
317 $self->exempt_amount - $existing_exemption;
318 if ( $remaining_exemption > 0 ) {
319 my $addl = $remaining_exemption > $taxable_per_month
321 : $remaining_exemption;
322 $taxable_charged -= $addl;
324 my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg ( {
325 'taxnum' => $self->taxnum,
326 'year' => 1900+$year,
328 'amount' => sprintf("%.2f", $addl ),
330 if ($cust_bill_pkg->billpkgnum) {
331 $cust_tax_exempt_pkg->billpkgnum($cust_bill_pkg->billpkgnum);
332 my $error = $cust_tax_exempt_pkg->insert;
334 $dbh->rollback if $oldAutoCommit;
335 return "fatal: can't insert cust_tax_exempt_pkg: $error";
338 push @exemptions, $cust_tax_exempt_pkg;
339 push @{ $cust_bill_pkg->_cust_tax_exempt_pkg }, $cust_tax_exempt_pkg;
340 } # if $cust_bill_pkg->billpkgnum
341 } # if $remaining_exemption > 0
345 #until ( $mon < 12 ) { $mon -= 12; $year++; }
346 until ( $mon < 13 ) { $mon -= 12; $year++; }
348 } #foreach $which_month
350 } #if $tax->exempt_amount
352 $taxable_charged = sprintf( "%.2f", $taxable_charged);
354 $amount += $taxable_charged * $self->tax / 100
357 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
372 =item regionselector [ COUNTY STATE COUNTRY [ PREFIX [ ONCHANGE [ DISABLED ] ] ] ]
377 my ( $selected_county, $selected_state, $selected_country,
378 $prefix, $onchange, $disabled ) = @_;
380 $prefix = '' unless defined $prefix;
384 # unless ( @cust_main_county ) { #cache
385 @cust_main_county = qsearch('cust_main_county', {} );
386 foreach my $c ( @cust_main_county ) {
387 $countyflag=1 if $c->county;
388 #push @{$cust_main_county{$c->country}{$c->state}}, $c->county;
389 $cust_main_county{$c->country}{$c->state}{$c->county} = 1;
392 $countyflag=1 if $selected_county;
394 my $script_html = <<END;
396 function opt(what,value,text) {
397 var optionName = new Option(text, value, false, false);
398 var length = what.length;
399 what.options[length] = optionName;
401 function ${prefix}country_changed(what) {
402 country = what.options[what.selectedIndex].text;
403 for ( var i = what.form.${prefix}state.length; i >= 0; i-- )
404 what.form.${prefix}state.options[i] = null;
406 #what.form.${prefix}state.options[0] = new Option('', '', false, true);
408 foreach my $country ( sort keys %cust_main_county ) {
409 $script_html .= "\nif ( country == \"$country\" ) {\n";
410 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
411 ( my $dstate = $state ) =~ s/[\n\r]//g;
412 my $text = $dstate || '(n/a)';
413 $script_html .= qq!opt(what.form.${prefix}state, "$dstate", "$text");\n!;
415 $script_html .= "}\n";
418 $script_html .= <<END;
420 function ${prefix}state_changed(what) {
424 $script_html .= <<END;
425 state = what.options[what.selectedIndex].text;
426 country = what.form.${prefix}country.options[what.form.${prefix}country.selectedIndex].text;
427 for ( var i = what.form.${prefix}county.length; i >= 0; i-- )
428 what.form.${prefix}county.options[i] = null;
431 foreach my $country ( sort keys %cust_main_county ) {
432 $script_html .= "\nif ( country == \"$country\" ) {\n";
433 foreach my $state ( sort keys %{$cust_main_county{$country}} ) {
434 $script_html .= "\nif ( state == \"$state\" ) {\n";
435 #foreach my $county ( sort @{$cust_main_county{$country}{$state}} ) {
436 foreach my $county ( sort keys %{$cust_main_county{$country}{$state}} ) {
437 my $text = $county || '(n/a)';
439 qq!opt(what.form.${prefix}county, "$county", "$text");\n!;
441 $script_html .= "}\n";
443 $script_html .= "}\n";
447 $script_html .= <<END;
452 my $county_html = $script_html;
454 $county_html .= qq!<SELECT NAME="${prefix}county" onChange="$onchange" $disabled>!;
455 $county_html .= '</SELECT>';
458 qq!<INPUT TYPE="hidden" NAME="${prefix}county" VALUE="$selected_county">!;
461 my $state_html = qq!<SELECT NAME="${prefix}state" !.
462 qq!onChange="${prefix}state_changed(this); $onchange" $disabled>!;
463 foreach my $state ( sort keys %{ $cust_main_county{$selected_country} } ) {
464 my $text = $state || '(n/a)';
465 my $selected = $state eq $selected_state ? 'SELECTED' : '';
466 $state_html .= qq(\n<OPTION $selected VALUE="$state">$text</OPTION>);
468 $state_html .= '</SELECT>';
470 $state_html .= '</SELECT>';
472 my $country_html = qq!<SELECT NAME="${prefix}country" !.
473 qq!onChange="${prefix}country_changed(this); $onchange" $disabled>!;
474 my $countrydefault = $conf->config('countrydefault') || 'US';
475 foreach my $country (
476 sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
477 keys %cust_main_county
479 my $selected = $country eq $selected_country ? ' SELECTED' : '';
480 $country_html .= qq(\n<OPTION$selected VALUE="$country">$country</OPTION>");
482 $country_html .= '</SELECT>';
484 ($county_html, $state_html, $country_html);
492 regionselector? putting web ui components in here? they should probably live
497 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base