1 package FS::cust_tax_location;
5 use FS::Record qw( qsearch qsearchs dbh );
6 use FS::Misc qw ( csv_from_fixed );
12 FS::cust_tax_location - Object methods for cust_tax_location records
16 use FS::cust_tax_location;
18 $record = new FS::cust_tax_location \%hash;
19 $record = new FS::cust_tax_location { 'column' => 'value' };
21 $error = $record->insert;
23 $error = $new_record->replace($old_record);
25 $error = $record->delete;
27 $error = $record->check;
31 An FS::cust_tax_location object represents a classification rule for
32 determining a tax region code ('geocode') for a service location. These
33 records are used when editing customer locations to help the user choose the
34 correct tax jurisdiction code. The jurisdiction codes are actually defined
35 in L<FS::tax_rate_location>, and appear directly in records in
38 FS::cust_tax_location is used in tax calculation (for CCH) to determine
39 "implied" geocodes for customers and locations that have a complete U.S.
40 ZIP+4 code and thus can be exactly placed in a jurisdiction. For those that
41 don't, the user is expected to choose the geocode when entering the customer
44 FS::cust_tax_location inherits from FS::Record. The following fields are
49 =item custlocationnum - primary key
51 =item data_vendor - a tax data vendor and "style" of record
53 =item country - the two-letter country code
55 =item state - the two-letter state code (though CCH uses this differently;
58 =item zip - an exact zip code (again, see QUIRKS)
60 =item ziplo - the lower bound of the zip code range (requires zip to be null)
62 =item ziphi - the upper bound of the zip code range (requires zip to be null)
64 =item plus4lo - the lower bound of the last 4 zip code digits
66 =item plus4hi - the upper bound of the last 4 zip code digits
68 =item default_location - 'Y' when this record represents the default. The UI
69 will list default locations before non-default locations.
71 =item geocode - the foreign key into L<FS::part_pkg_tax_rate>,
72 L<FS::tax_rate>, L<FS::tax_rate_location>, etc.
82 Creates a new cust_tax_location. To add the cust_tax_location to the database,
85 Note that this stores the hash reference, not a distinct copy of the hash it
86 points to. You can ask the object for a copy with the I<hash> method.
90 sub table { 'cust_tax_location'; }
94 Adds this record to the database. If there is an error, returns the error,
95 otherwise returns false.
101 Delete this record from the database.
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.
114 Checks all fields to make sure this is a valid cust_tax_location. If there is
115 an error, returns the error, otherwise returns false. Called by the insert
124 $self->ut_numbern('custlocationnum')
125 || $self->ut_text('data_vendor')
126 || $self->ut_textn('city')
127 || $self->ut_textn('postalcity')
128 || $self->ut_textn('county')
129 || $self->ut_text('state')
130 || $self->ut_numbern('plus4hi')
131 || $self->ut_numbern('plus4lo')
132 || $self->ut_enum('default_location', [ '', 'Y' ] )
133 || $self->ut_enum('cityflag', [ '', 'I', 'O', 'B' ] )
134 || $self->ut_alpha('geocode')
136 if ( $self->country ) {
137 $error ||= $self->ut_country('country')
138 || $self->ut_zip('ziphi', $self->country)
139 || $self->ut_zip('ziplo', $self->country);
141 return $error if $error;
143 if ($self->state eq 'CN' && $self->data_vendor eq 'cch-zip' ) {
144 $error = "Illegal canadian zip"
145 unless $self->zip =~ /^[A-Z]$/;
146 } elsif ($self->state =~ /^E([B-DFGILNPR-UW])$/ && $self->data_vendor eq 'cch-zip' ) {
147 $error = "Illegal european zip"
148 unless $self->zip =~ /^E$1$/;
149 } elsif ($self->data_vendor =~ /^cch/) {
150 $error = $self->ut_numbern('zip', $self->state eq 'CN' ? 'CA' : 'US');
152 return $error if $error;
157 # annoyingly incompatible with FS::Record::batch_import.
160 my ($param, $job) = @_;
162 my $fh = $param->{filehandle};
163 my $format = $param->{'format'};
169 my @column_lengths = ();
170 my @column_callbacks = ();
171 if ( $format =~ /^cch-fixed/ ) {
172 $format =~ s/-fixed//;
175 $f =~ s/-update// && ($update = 1);
177 push @column_lengths, qw( 5 2 4 4 10 1 );
178 } elsif ( $f eq 'cch-zip' ) {
179 push @column_lengths, qw( 5 28 25 2 28 5 1 1 10 1 2 );
181 return "Unknown format: $format";
183 push @column_lengths, 1 if $update;
187 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
188 if ( $job || scalar(@column_lengths) ) {
189 my $error = csv_from_fixed(\$fh, \$count, \@column_lengths);
190 return $error if $error;
193 if ( $format eq 'cch' || $format eq 'cch-update' ) {
194 @fields = qw( zip state plus4lo plus4hi geocode default_location );
195 push @fields, 'actionflag' if $format eq 'cch-update';
197 $imported++ if $format eq 'cch-update'; #empty file ok
202 $hash->{'data_vendor'} = 'cch';
203 $hash->{'default_location'} =~ s/ //g;
205 if (exists($hash->{actionflag}) && $hash->{actionflag} eq 'D') {
206 delete($hash->{actionflag});
208 my @cust_tax_location = qsearch('cust_tax_location', $hash);
209 return "Can't find cust_tax_location to delete: ".
210 join(" ", map { "$_ => ". $hash->{$_} } @fields)
211 unless scalar(@cust_tax_location) || $param->{'delete_only'} ;
213 foreach my $cust_tax_location (@cust_tax_location) {
214 my $error = $cust_tax_location->delete;
215 return $error if $error;
218 delete($hash->{$_}) foreach (keys %$hash);
221 delete($hash->{'actionflag'});
227 } elsif ( $format eq 'cch-zip' || $format eq 'cch-update-zip' ) {
228 @fields = qw( zip city county state postalcity countyfips countydef default_location geocode cityflag unique );
229 push @fields, 'actionflag' if $format eq 'cch-update-zip';
231 $imported++ if $format eq 'cch-update'; #empty file ok
236 $hash->{'data_vendor'} = 'cch-zip';
237 delete($hash->{$_}) foreach qw( countyfips countydef unique );
239 $hash->{'cityflag'} =~ s/ //g;
240 $hash->{'default_location'} =~ s/ //g;
242 if (exists($hash->{actionflag}) && $hash->{actionflag} eq 'D') {
243 delete($hash->{actionflag});
245 my @cust_tax_location = qsearch('cust_tax_location', $hash);
246 return "Can't find cust_tax_location to delete: ".
247 join(" ", map { "$_ => ". $hash->{$_} } @fields)
248 unless scalar(@cust_tax_location) || $param->{'delete_only'} ;
250 foreach my $cust_tax_location (@cust_tax_location) {
251 my $error = $cust_tax_location->delete;
252 return $error if $error;
255 delete($hash->{$_}) foreach (keys %$hash);
258 delete($hash->{'actionflag'});
264 } elsif ( $format eq 'billsoft' ) {
266 @fields = qw( geocode alt_location country state county city
270 $hash->{data_vendor} = 'billsoft';
271 $hash->{default_location} = ($hash->{alt_location} ? '' : 'Y');
272 $hash->{city} =~ s/[^\w ]//g; # remove asterisks and other bad things
273 $hash->{country} = substr($hash->{country}, 0, 2);
274 if ( $hash->{state} =~ /^ *$/
275 or $hash->{county} =~ /^ *$/
276 or $hash->{country} !~ /^US|CA$/ ) {
277 # remove whole-country rows, whole-state rows, and non-CAN/USA rows
283 } elsif ( $format eq 'extended' ) {
284 die "unimplemented\n";
287 die "unknown format $format";
290 eval "use Text::CSV_XS;";
293 my $csv = new Text::CSV_XS;
295 local $SIG{HUP} = 'IGNORE';
296 local $SIG{INT} = 'IGNORE';
297 local $SIG{QUIT} = 'IGNORE';
298 local $SIG{TERM} = 'IGNORE';
299 local $SIG{TSTP} = 'IGNORE';
300 local $SIG{PIPE} = 'IGNORE';
302 my $oldAutoCommit = $FS::UID::AutoCommit;
303 local $FS::UID::AutoCommit = 0;
306 while ( defined($line=<$fh>) ) {
307 $csv->parse($line) or do {
308 $dbh->rollback if $oldAutoCommit;
309 return "can't parse: ". $csv->error_input();
312 if ( $job ) { # progress bar
313 if ( time - $min_sec > $last ) {
314 my $error = $job->update_statustext(
315 int( 100 * $imported / $count ). ",Importing tax locations"
317 die $error if $error;
322 my @columns = $csv->fields();
324 my %cust_tax_location = ( 'data_vendor' => $format );;
325 foreach my $field ( @fields ) {
326 $cust_tax_location{$field} = shift @columns;
328 if ( scalar( @columns ) ) {
329 $dbh->rollback if $oldAutoCommit;
330 return "Unexpected trailing columns in line (wrong format?) importing cust_tax_location: $line";
333 my $error = &{$hook}(\%cust_tax_location);
335 $dbh->rollback if $oldAutoCommit;
339 # $hook can delete the contents of the hash to prevent the row from
341 next unless scalar(keys %cust_tax_location);
343 my $cust_tax_location = new FS::cust_tax_location( \%cust_tax_location );
344 $error = $cust_tax_location->insert;
347 $dbh->rollback if $oldAutoCommit;
348 return "can't insert cust_tax_location for $line: $error";
354 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
356 return "Empty file!" unless ( $imported || $format =~ /^cch-update/ );
368 =item process_batch_import JOB, PARAMS
370 Starts a batch import given JOB (an L<FS::queue>) and PARAMS (a
371 Base64-Storable hash). PARAMS should contain 'format' and 'uploaded_files'.
373 Currently only usable for Billsoft imports; CCH's agglomeration of update
374 files need to be imported through L<FS::tax_rate::process_batch_import>.
378 sub process_batch_import {
382 my $files = $param->{'uploaded_files'};
384 my ($file) = ($files =~ /^zipfile:(.*)$/)
385 or die "No files provided.\n";
387 my $dir = $FS::UID::cache_dir . '/cache.' . $FS::UID::datasrc;
388 open ( $param->{'filehandle'}, '<', "$dir/$file" )
389 or die "unable to open '$file': $!\n";
391 my $error = batch_import($param, $job);
392 die $error if $error;
399 CCH doesn't have a "country" field; for addresses in Canada it uses state
400 = 'CN', and zip = the one-letter postal code prefix for the province. Or
401 maybe that's just our CCH implementation. This doesn't apply to Billsoft,
402 and shouldn't apply to any other tax vendor that may somehow be implemented.
404 CCH also has two styles of records in this table: cch and cch-zip. cch
405 records define a unique
413 L<FS::Record>, schema.html from the base documentation.