4 use vars qw( @ISA $DEBUG $me
5 %tax_unittypes %tax_maxtypes %tax_basetypes %tax_authorities
6 %tax_passtypes %GetInfoType $keep_cch_files );
9 use DateTime::Format::Strptime;
10 use Storable qw( thaw nfreeze );
19 use DBIx::DBSchema::Table;
20 use DBIx::DBSchema::Column;
21 use FS::Record qw( qsearch qsearchs dbh dbdef );
24 use FS::cust_bill_pkg;
25 use FS::cust_tax_location;
26 use FS::tax_rate_location;
27 use FS::part_pkg_taxrate;
28 use FS::part_pkg_taxproduct;
30 use FS::Misc qw( csv_from_fixed );
34 @ISA = qw( FS::Record );
37 $me = '[FS::tax_rate]';
42 FS::tax_rate - Object methods for tax_rate objects
48 $record = new FS::tax_rate \%hash;
49 $record = new FS::tax_rate { 'column' => 'value' };
51 $error = $record->insert;
53 $error = $new_record->replace($old_record);
55 $error = $record->delete;
57 $error = $record->check;
61 An FS::tax_rate object represents a tax rate, defined by locale.
62 FS::tax_rate inherits from FS::Record. The following fields are
69 primary key (assigned automatically for new tax rates)
73 a geographic location code provided by a tax data vendor
81 a location code provided by a tax authority
85 a foreign key into FS::tax_class - the type of tax
86 referenced but FS::part_pkg_taxrate
89 the time after which the tax applies
97 second bracket percentage
101 the amount to which the tax applies (first bracket)
105 a cap on the amount of tax if a cap exists
109 percentage on out of jurisdiction purchases
113 second bracket percentage on out of jurisdiction purchases
117 one of the values in %tax_unittypes
121 amount of tax per unit
125 second bracket amount of tax per unit
129 the number of units to which the fee applies (first bracket)
133 the most units to which fees apply (first and second brackets)
137 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
141 if defined, printed on invoices instead of "Tax"
145 a value from %tax_authorities
149 a value from %tax_basetypes indicating the tax basis
153 a value from %tax_passtypes indicating how the tax should displayed to the customer
157 'Y', 'N', or blank indicating the tax can be passed to the customer
161 if 'Y', this tax does not apply to setup fees
165 if 'Y', this tax does not apply to recurring fees
169 if 'Y', has been manually edited
179 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
183 sub table { 'tax_rate'; }
187 Adds this tax rate to the database. If there is an error, returns the error,
188 otherwise returns false.
192 Deletes this tax rate from the database. If there is an error, returns the
193 error, otherwise returns false.
195 =item replace OLD_RECORD
197 Replaces the OLD_RECORD with this one in the database. If there is an error,
198 returns the error, otherwise returns false.
202 Checks all fields to make sure this is a valid tax rate. If there is an error,
203 returns the error, otherwise returns false. Called by the insert and replace
211 foreach (qw( taxbase taxmax )) {
212 $self->$_(0) unless $self->$_;
215 $self->ut_numbern('taxnum')
216 || $self->ut_text('geocode')
217 || $self->ut_textn('data_vendor')
218 || $self->ut_textn('location')
219 || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
220 || $self->ut_snumbern('effective_date')
221 || $self->ut_float('tax')
222 || $self->ut_floatn('excessrate')
223 || $self->ut_money('taxbase')
224 || $self->ut_money('taxmax')
225 || $self->ut_floatn('usetax')
226 || $self->ut_floatn('useexcessrate')
227 || $self->ut_numbern('unittype')
228 || $self->ut_floatn('fee')
229 || $self->ut_floatn('excessfee')
230 || $self->ut_floatn('feemax')
231 || $self->ut_numbern('maxtype')
232 || $self->ut_textn('taxname')
233 || $self->ut_numbern('taxauth')
234 || $self->ut_numbern('basetype')
235 || $self->ut_numbern('passtype')
236 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
237 || $self->ut_enum('setuptax', [ '', 'Y' ] )
238 || $self->ut_enum('recurtax', [ '', 'Y' ] )
239 || $self->ut_enum('inoutcity', [ '', 'I', 'O' ] )
240 || $self->ut_enum('inoutlocal', [ '', 'I', 'O' ] )
241 || $self->ut_enum('manual', [ '', 'Y' ] )
242 || $self->ut_enum('disabled', [ '', 'Y' ] )
243 || $self->SUPER::check
248 =item taxclass_description
250 Returns the human understandable value associated with the related
255 sub taxclass_description {
257 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
258 $tax_class ? $tax_class->description : '';
263 Returns the human understandable value associated with the unittype column
267 %tax_unittypes = ( '0' => 'access line',
274 $tax_unittypes{$self->unittype};
279 Returns the human understandable value associated with the maxtype column
283 %tax_maxtypes = ( '0' => 'receipts per invoice',
284 '1' => 'receipts per item',
285 '2' => 'total utility charges per utility tax year',
286 '3' => 'total charges per utility tax year',
287 '4' => 'receipts per access line',
288 '9' => 'monthly receipts per location',
293 $tax_maxtypes{$self->maxtype};
298 Returns the human understandable value associated with the basetype column
302 %tax_basetypes = ( '0' => 'sale price',
303 '1' => 'gross receipts',
304 '2' => 'sales taxable telecom revenue',
305 '3' => 'minutes carried',
306 '4' => 'minutes billed',
307 '5' => 'gross operating revenue',
308 '6' => 'access line',
310 '8' => 'gross revenue',
311 '9' => 'portion gross receipts attributable to interstate service',
312 '10' => 'access line',
313 '11' => 'gross profits',
314 '12' => 'tariff rate',
316 '15' => 'prior year gross receipts',
321 $tax_basetypes{$self->basetype};
326 Returns the human understandable value associated with the taxauth column
330 %tax_authorities = ( '0' => 'federal',
335 '5' => 'county administered by state',
336 '6' => 'city administered by state',
337 '7' => 'city administered by county',
338 '8' => 'local administered by state',
339 '9' => 'local administered by county',
344 $tax_authorities{$self->taxauth};
349 Returns the human understandable value associated with the passtype column
353 %tax_passtypes = ( '0' => 'separate tax line',
354 '1' => 'separate surcharge line',
355 '2' => 'surcharge not separated',
356 '3' => 'included in base rate',
361 $tax_passtypes{$self->passtype};
364 =item taxline TAXABLES, [ OPTIONSHASH ]
366 Returns a listref of a name and an amount of tax calculated for the list
367 of packages/amounts referenced by TAXABLES. If an error occurs, a message
368 is returned as a scalar.
378 if (ref($_[0]) eq 'ARRAY') {
383 #exemptions would be broken in this case
386 my $name = $self->taxname;
387 $name = 'Other surcharges'
388 if ($self->passtype == 2);
391 if ( $self->disabled ) { # we always know how to handle disabled taxes
398 my $taxable_charged = 0;
399 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
402 warn "calculating taxes for ". $self->taxnum. " on ".
403 join (",", map { $_->pkgnum } @cust_bill_pkg)
406 if ($self->passflag eq 'N') {
407 # return "fatal: can't (yet) handle taxes not passed to the customer";
408 # until someone needs to track these in freeside
415 my $maxtype = $self->maxtype || 0;
416 if ($maxtype != 0 && $maxtype != 9) {
417 return $self->_fatal_or_null( 'tax with "'.
418 $self->maxtype_name. '" threshold'
424 $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
428 # we treat gross revenue as gross receipts and expect the tax data
429 # to DTRT (i.e. tax on tax rules)
430 if ($self->basetype != 0 && $self->basetype != 1 &&
431 $self->basetype != 5 && $self->basetype != 6 &&
432 $self->basetype != 7 && $self->basetype != 8 &&
433 $self->basetype != 14
436 $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
439 unless ($self->setuptax =~ /^Y$/i) {
440 $taxable_charged += $_->setup foreach @cust_bill_pkg;
442 unless ($self->recurtax =~ /^Y$/i) {
443 $taxable_charged += $_->recur foreach @cust_bill_pkg;
446 my $taxable_units = 0;
447 unless ($self->recurtax =~ /^Y$/i) {
449 if (( $self->unittype || 0 ) == 0) { #access line
451 foreach (@cust_bill_pkg) {
452 $taxable_units += $_->units
453 unless $seen{$_->pkgnum}++;
456 } elsif ($self->unittype == 1) { #minute
457 return $self->_fatal_or_null( 'fee with minute unit type' );
459 } elsif ($self->unittype == 2) { #account
461 my $conf = new FS::Conf;
462 if ( $conf->exists('tax-pkg_address') ) {
463 #number of distinct locations
465 foreach (@cust_bill_pkg) {
467 unless $seen{$_->cust_pkg->locationnum}++;
474 return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
480 # XXX insert exemption handling here
482 # the tax or fee is applied to taxbase or feebase and then
483 # the excessrate or excess fee is applied to taxmax or feemax
486 $amount += $taxable_charged * $self->tax;
487 $amount += $taxable_units * $self->fee;
489 warn "calculated taxes as [ $name, $amount ]\n"
500 my ($self, $error) = @_;
502 my $conf = new FS::Conf;
504 $error = "can't yet handle ". $error;
505 my $name = $self->taxname;
506 $name = 'Other surcharges'
507 if ($self->passtype == 2);
509 if ($conf->exists('ignore_incalculable_taxes')) {
510 warn "WARNING: $error; billing anyway per ignore_incalculable_taxes conf\n";
511 return { name => $name, amount => 0 };
513 return "fatal: $error";
517 =item tax_on_tax CUST_MAIN
519 Returns a list of taxes which are candidates for taxing taxes for the
520 given customer (see L<FS::cust_main>)
528 my $cust_main = shift;
530 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
534 my $geocode = $cust_main->geocode($self->data_vendor);
538 my $extra_sql = ' AND ('.
539 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
544 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
545 my $select = 'DISTINCT ON(taxclassnum) *';
547 # should qsearch preface columns with the table to facilitate joins?
548 my @taxclassnums = map { $_->taxclassnum }
549 qsearch( { 'table' => 'part_pkg_taxrate',
551 'hashref' => { 'data_vendor' => $self->data_vendor,
552 'taxclassnumtaxed' => $self->taxclassnum,
554 'extra_sql' => $extra_sql,
555 'order_by' => $order_by,
558 return () unless @taxclassnums;
561 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
563 qsearch({ 'table' => 'tax_rate',
564 'hashref' => { 'geocode' => $geocode, },
565 'extra_sql' => $extra_sql,
570 =item tax_rate_location
572 Returns an object representing the location associated with this tax
573 (see L<FS::tax_rate_location>)
577 sub tax_rate_location {
580 qsearchs({ 'table' => 'tax_rate_location',
581 'hashref' => { 'data_vendor' => $self->data_vendor,
582 'geocode' => $self->geocode,
586 new FS::tax_rate_location;
600 sub _progressbar_foo {
605 my ($param, $job) = @_;
607 my $fh = $param->{filehandle};
608 my $format = $param->{'format'};
616 my @column_lengths = ();
617 my @column_callbacks = ();
618 if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
619 $format =~ s/-fixed//;
620 my $date_format = sub { my $r='';
621 /^(\d{4})(\d{2})(\d{2})$/ && ($r="$3/$2/$1");
624 my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
625 push @column_lengths, qw( 10 1 1 8 8 5 8 8 8 1 2 2 30 8 8 10 2 8 2 1 2 2 );
626 push @column_lengths, 1 if $format eq 'cch-update';
627 push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
628 $column_callbacks[8] = $date_format;
632 my ( $count, $last, $min_sec ) = _progressbar_foo();
633 if ( $job || scalar(@column_callbacks) ) {
635 csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
636 return $error if $error;
640 if ( $format eq 'cch' || $format eq 'cch-update' ) {
641 #false laziness w/below (sub _perform_cch_diff)
642 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
643 excessrate effective_date taxauth taxtype taxcat taxname
644 usetax useexcessrate fee unittype feemax maxtype passflag
646 push @fields, 'actionflag' if $format eq 'cch-update';
651 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
652 $hash->{'data_vendor'} ='cch';
653 my $parser = new DateTime::Format::Strptime( pattern => "%m/%d/%Y",
654 time_zone => 'floating',
656 my $dt = $parser->parse_datetime( $hash->{'effective_date'} );
657 $hash->{'effective_date'} = $dt ? $dt->epoch : '';
659 $hash->{$_} =~ s/\s//g foreach qw( inoutcity inoutlocal ) ;
660 $hash->{$_} = sprintf("%.2f", $hash->{$_}) foreach qw( taxbase taxmax );
663 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
665 my %tax_class = ( 'data_vendor' => 'cch',
666 'taxclass' => $taxclassid,
669 my $tax_class = qsearchs( 'tax_class', \%tax_class );
670 return "Error updating tax rate: no tax class $taxclassid"
673 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
675 foreach (qw( taxtype taxcat )) {
679 my %passflagmap = ( '0' => '',
683 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
684 if exists $passflagmap{$hash->{'passflag'}};
686 foreach (keys %$hash) {
687 $hash->{$_} = substr($hash->{$_}, 0, 80)
688 if length($hash->{$_}) > 80;
691 my $actionflag = delete($hash->{'actionflag'});
693 $hash->{'taxname'} =~ s/`/'/g;
694 $hash->{'taxname'} =~ s|\\|/|g;
696 return '' if $format eq 'cch'; # but not cch-update
698 if ($actionflag eq 'I') {
699 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
700 }elsif ($actionflag eq 'D') {
701 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
703 return "Unexpected action flag: ". $hash->{'actionflag'};
706 delete($hash->{$_}) for keys %$hash;
712 } elsif ( $format eq 'extended' ) {
713 die "unimplemented\n";
717 die "unknown format $format";
720 my $csv = new Text::CSV_XS;
724 local $SIG{HUP} = 'IGNORE';
725 local $SIG{INT} = 'IGNORE';
726 local $SIG{QUIT} = 'IGNORE';
727 local $SIG{TERM} = 'IGNORE';
728 local $SIG{TSTP} = 'IGNORE';
729 local $SIG{PIPE} = 'IGNORE';
731 my $oldAutoCommit = $FS::UID::AutoCommit;
732 local $FS::UID::AutoCommit = 0;
735 while ( defined($line=<$fh>) ) {
736 $csv->parse($line) or do {
737 $dbh->rollback if $oldAutoCommit;
738 return "can't parse: ". $csv->error_input();
741 if ( $job ) { # progress bar
742 if ( time - $min_sec > $last ) {
743 my $error = $job->update_statustext(
744 int( 100 * $imported / $count ). ",Importing tax rates"
747 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
754 my @columns = $csv->fields();
756 my %tax_rate = ( 'data_vendor' => $format );
757 foreach my $field ( @fields ) {
758 $tax_rate{$field} = shift @columns;
761 if ( scalar( @columns ) ) {
762 $dbh->rollback if $oldAutoCommit;
763 return "Unexpected trailing columns in line (wrong format?) importing tax_rate: $line";
766 my $error = &{$hook}(\%tax_rate);
768 $dbh->rollback if $oldAutoCommit;
772 if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
774 my $tax_rate = new FS::tax_rate( \%tax_rate );
775 $error = $tax_rate->insert;
778 $dbh->rollback if $oldAutoCommit;
779 return "can't insert tax_rate for $line: $error";
788 for (grep { !exists($delete{$_}) } keys %insert) {
789 if ( $job ) { # progress bar
790 if ( time - $min_sec > $last ) {
791 my $error = $job->update_statustext(
792 int( 100 * $imported / $count ). ",Importing tax rates"
795 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
802 my $tax_rate = new FS::tax_rate( $insert{$_} );
803 my $error = $tax_rate->insert;
806 $dbh->rollback if $oldAutoCommit;
807 my $hashref = $insert{$_};
808 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
809 return "can't insert tax_rate for $line: $error";
815 for (grep { exists($delete{$_}) } keys %insert) {
816 if ( $job ) { # progress bar
817 if ( time - $min_sec > $last ) {
818 my $error = $job->update_statustext(
819 int( 100 * $imported / $count ). ",Importing tax rates"
822 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
829 #remove even if the rate doesn't match,
830 # geocode/taxclassnum/taxname/etc. should be enough
831 delete $delete{$_}->{tax};
832 my $old = qsearchs( 'tax_rate', $delete{$_} );
834 $dbh->rollback if $oldAutoCommit;
836 return "can't find tax_rate to replace for: ".
837 #join(" ", map { "$_ => ". $old->{$_} } @fields);
838 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
840 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
841 $new->taxnum($old->taxnum);
842 my $error = $new->replace($old);
845 $dbh->rollback if $oldAutoCommit;
846 my $hashref = $insert{$_};
847 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
848 return "can't replace tax_rate for $line: $error";
855 for (grep { !exists($insert{$_}) } keys %delete) {
856 if ( $job ) { # progress bar
857 if ( time - $min_sec > $last ) {
858 my $error = $job->update_statustext(
859 int( 100 * $imported / $count ). ",Importing tax rates"
862 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
869 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
871 $dbh->rollback if $oldAutoCommit;
872 $tax_rate = $delete{$_};
873 return "can't find tax_rate to delete for: ".
874 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
875 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
877 my $error = $tax_rate->delete;
880 $dbh->rollback if $oldAutoCommit;
881 my $hashref = $delete{$_};
882 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
883 return "can't delete tax_rate for $line: $error";
889 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
891 return "Empty file!" unless ($imported || $format eq 'cch-update');
897 =item process_batch_import
899 Load a batch import as a queued JSRPC job
903 sub process_batch_import {
906 my $oldAutoCommit = $FS::UID::AutoCommit;
907 local $FS::UID::AutoCommit = 0;
910 my $param = thaw(decode_base64(shift));
911 my $args = '$job, encode_base64( nfreeze( $param ) )';
913 my $method = '_perform_batch_import';
914 if ( $param->{reload} ) {
915 $method = 'process_batch_reload';
918 eval "$method($args);";
920 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
925 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
928 sub _perform_batch_import {
931 my $param = thaw(decode_base64(shift));
932 my $format = $param->{'format'}; #well... this is all cch specific
934 my $files = $param->{'uploaded_files'}
935 or die "No files provided.";
937 my (%files) = map { /^(\w+):((taxdata\/\w+\.\w+\/)?[\.\w]+)$/ ? ($1,$2):() }
940 if ( $format eq 'cch' || $format eq 'cch-fixed'
941 || $format eq 'cch-update' || $format eq 'cch-fixed-update' )
944 my $oldAutoCommit = $FS::UID::AutoCommit;
945 local $FS::UID::AutoCommit = 0;
948 my @insert_list = ();
949 my @delete_list = ();
950 my @predelete_list = ();
953 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
955 my @list = ( 'GEOCODE', \&FS::tax_rate_location::batch_import,
956 'CODE', \&FS::tax_class::batch_import,
957 'PLUS4', \&FS::cust_tax_location::batch_import,
958 'ZIP', \&FS::cust_tax_location::batch_import,
959 'TXMATRIX', \&FS::part_pkg_taxrate::batch_import,
960 'DETAIL', \&FS::tax_rate::batch_import,
962 while( scalar(@list) ) {
963 my ( $name, $import_sub ) = splice( @list, 0, 2 );
964 my $file = lc($name). 'file';
966 unless ($files{$file}) {
967 $error = "No $name supplied";
970 next if $name eq 'DETAIL' && $format =~ /update/;
972 my $filename = "$dir/". $files{$file};
974 if ( $format =~ /update/ ) {
976 ( $error, $insertname, $deletename ) =
977 _perform_cch_insert_delete_split( $name, $filename, $dir, $format )
981 unlink $filename or warn "Can't delete $filename: $!"
982 unless $keep_cch_files;
983 push @insert_list, $name, $insertname, $import_sub, $format;
984 if ( $name eq 'GEOCODE' ) { #handle this whole ordering issue better
985 unshift @predelete_list, $name, $deletename, $import_sub, $format;
987 unshift @delete_list, $name, $deletename, $import_sub, $format;
992 push @insert_list, $name, $filename, $import_sub, $format;
999 'DETAIL', "$dir/".$files{detailfile}, \&FS::tax_rate::batch_import, $format
1000 if $format =~ /update/;
1002 $error ||= _perform_cch_tax_import( $job,
1003 [ @predelete_list ],
1009 @list = ( @predelete_list, @insert_list, @delete_list );
1010 while( !$keep_cch_files && scalar(@list) ) {
1011 my ( undef, $file, undef, undef ) = splice( @list, 0, 4 );
1012 unlink $file or warn "Can't delete $file: $!";
1016 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1019 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1023 die "Unknown format: $format";
1029 sub _perform_cch_tax_import {
1030 my ( $job, $predelete_list, $insert_list, $delete_list ) = @_;
1033 foreach my $list ($predelete_list, $insert_list, $delete_list) {
1034 while( scalar(@$list) ) {
1035 my ( $name, $file, $method, $format ) = splice( @$list, 0, 4 );
1036 my $fmt = "$format-update";
1037 $fmt = $format. ( lc($name) eq 'zip' ? '-zip' : '' );
1038 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1039 $error ||= &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1047 sub _perform_cch_insert_delete_split {
1048 my ($name, $filename, $dir, $format) = @_;
1052 open my $fh, "< $filename"
1053 or $error ||= "Can't open $name file $filename: $!";
1055 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
1058 ) or die "can't open temp file: $!\n";
1059 my $insertname = $ifh->filename;
1061 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
1064 ) or die "can't open temp file: $!\n";
1065 my $deletename = $dfh->filename;
1067 my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
1068 my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
1071 $handle = $ifh if $_ =~ /$insert_pattern/;
1072 $handle = $dfh if $_ =~ /$delete_pattern/;
1074 $error = "bad input line: $_" unless $handle;
1083 return ($error, $insertname, $deletename);
1086 sub _perform_cch_diff {
1087 my ($name, $newdir, $olddir) = @_;
1092 open my $oldcsvfh, "$olddir/$name.txt"
1093 or die "failed to open $olddir/$name.txt: $!\n";
1095 while(<$oldcsvfh>) {
1102 open my $newcsvfh, "$newdir/$name.txt"
1103 or die "failed to open $newdir/$name.txt: $!\n";
1105 my $dfh = new File::Temp( TEMPLATE => "$name.diff.XXXXXXXX",
1108 ) or die "can't open temp file: $!\n";
1109 my $diffname = $dfh->filename;
1111 while(<$newcsvfh>) {
1113 if (exists($oldlines{$_})) {
1116 print $dfh $_, ',"I"', "\n";
1121 #false laziness w/above (sub batch_import)
1122 my @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
1123 excessrate effective_date taxauth taxtype taxcat taxname
1124 usetax useexcessrate fee unittype feemax maxtype passflag
1125 passtype basetype );
1126 my $numfields = scalar(@fields);
1128 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1130 for my $line (grep $oldlines{$_}, keys %oldlines) {
1132 $csv->parse($line) or do {
1133 #$dbh->rollback if $oldAutoCommit;
1134 die "can't parse: ". $csv->error_input();
1136 my @columns = $csv->fields();
1138 $csv->combine( splice(@columns, 0, $numfields) );
1140 print $dfh $csv->string, ',"D"', "\n";
1148 sub _cch_fetch_and_unzip {
1149 my ( $job, $urls, $secret, $dir ) = @_;
1151 my $ua = new LWP::UserAgent;
1152 foreach my $url (split ',', $urls) {
1153 my @name = split '/', $url; #somewhat restrictive
1154 my $name = pop @name;
1155 $name =~ /([\w.]+)/; # untaint that which we don't trust so much any more
1158 open my $taxfh, ">$dir/$name" or die "Can't open $dir/$name: $!\n";
1160 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1161 my $res = $ua->request(
1162 new HTTP::Request( GET => $url ),
1164 print $taxfh $_[0] or die "Can't write to $dir/$name: $!\n";
1165 my $content_length = $_[1]->content_length;
1166 $imported += length($_[0]);
1167 if ( time - $min_sec > $last ) {
1168 my $error = $job->update_statustext(
1169 ($content_length ? int(100 * $imported/$content_length) : 0 ).
1170 ",Downloading data from CCH"
1172 die $error if $error;
1177 die "download of $url failed: ". $res->status_line
1178 unless $res->is_success;
1181 my $error = $job->update_statustext( "0,Unpacking data" );
1182 die $error if $error;
1183 $secret =~ /([\w.]+)/; # untaint that which we don't trust so much any more
1185 system('unzip', "-P", $secret, "-d", "$dir", "$dir/$name") == 0
1186 or die "unzip -P $secret -d $dir $dir/$name failed";
1187 #unlink "$dir/$name";
1191 sub _cch_extract_csv_from_dbf {
1192 my ( $job, $dir, $name ) = @_;
1197 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1198 my $error = $job->update_statustext( "0,Unpacking $name" );
1199 die $error if $error;
1200 warn "opening $dir.new/$name.dbf\n" if $DEBUG;
1201 my $table = new XBase 'name' => "$dir.new/$name.dbf";
1202 die "failed to access $dir.new/$name.dbf: ". XBase->errstr
1203 unless defined($table);
1204 my $count = $table->last_record; # approximately;
1205 open my $csvfh, ">$dir.new/$name.txt"
1206 or die "failed to open $dir.new/$name.txt: $!\n";
1208 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1209 my @fields = $table->field_names;
1210 my $cursor = $table->prepare_select;
1212 sub { my $date = shift;
1213 $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1");
1216 while (my $row = $cursor->fetch_hashref) {
1217 $csv->combine( map { my $type = $table->field_type($_);
1219 &{$format_date}($row->{$_}) ;
1220 } elsif ($type eq 'N' && $row->{$_} =~ /e-/i ) {
1221 sprintf('%.8f', $row->{$_}); #db row is numeric(14,8)
1228 print $csvfh $csv->string, "\n";
1230 if ( time - $min_sec > $last ) {
1231 my $error = $job->update_statustext(
1232 int(100 * $imported/$count). ",Unpacking $name"
1234 die $error if $error;
1242 sub _remember_disabled_taxes {
1243 my ( $job, $format, $disabled_tax_rate ) = @_;
1247 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1249 my @items = qsearch( { table => 'tax_rate',
1250 hashref => { disabled => 'Y',
1251 data_vendor => $format,
1253 select => 'geocode, taxclassnum',
1256 my $count = scalar(@items);
1257 foreach my $tax_rate ( @items ) {
1258 if ( time - $min_sec > $last ) {
1259 $job->update_statustext(
1260 int( 100 * $imported / $count ). ",Remembering disabled taxes"
1266 qsearchs( 'tax_class', { taxclassnum => $tax_rate->taxclassnum } );
1267 unless ( $tax_class ) {
1268 warn "failed to find tax_class ". $tax_rate->taxclassnum;
1271 $disabled_tax_rate->{$tax_rate->geocode. ':'. $tax_class->taxclass} = 1;
1275 sub _remember_tax_products {
1276 my ( $job, $format, $taxproduct ) = @_;
1278 # XXX FIXME this loop only works when cch is the only data provider
1280 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1282 my $extra_sql = "WHERE taxproductnum IS NOT NULL OR ".
1283 "0 < ( SELECT count(*) from part_pkg_option WHERE ".
1284 " part_pkg_option.pkgpart = part_pkg.pkgpart AND ".
1285 " optionname LIKE 'usage_taxproductnum_%' AND ".
1286 " optionvalue != '' )";
1287 my @items = qsearch( { table => 'part_pkg',
1288 select => 'DISTINCT pkgpart,taxproductnum',
1290 extra_sql => $extra_sql,
1293 my $count = scalar(@items);
1294 foreach my $part_pkg ( @items ) {
1295 if ( time - $min_sec > $last ) {
1296 $job->update_statustext(
1297 int( 100 * $imported / $count ). ",Remembering tax products"
1302 warn "working with package part ". $part_pkg->pkgpart.
1303 "which has a taxproductnum of ". $part_pkg->taxproductnum. "\n" if $DEBUG;
1304 my $part_pkg_taxproduct = $part_pkg->taxproduct('');
1305 $taxproduct->{$part_pkg->pkgpart}->{''} = $part_pkg_taxproduct->taxproduct
1306 if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format;
1308 foreach my $option ( $part_pkg->part_pkg_option ) {
1309 next unless $option->optionname =~ /^usage_taxproductnum_(\w+)$/;
1312 $part_pkg_taxproduct = $part_pkg->taxproduct($class);
1313 $taxproduct->{$part_pkg->pkgpart}->{$class} =
1314 $part_pkg_taxproduct->taxproduct
1315 if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format;
1320 sub _restore_remembered_tax_products {
1321 my ( $job, $format, $taxproduct ) = @_;
1325 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1326 my $count = scalar(keys %$taxproduct);
1327 foreach my $pkgpart ( keys %$taxproduct ) {
1328 warn "restoring taxproductnums on pkgpart $pkgpart\n" if $DEBUG;
1329 if ( time - $min_sec > $last ) {
1330 $job->update_statustext(
1331 int( 100 * $imported / $count ). ",Restoring tax products"
1337 my $part_pkg = qsearchs('part_pkg', { pkgpart => $pkgpart } );
1338 unless ( $part_pkg ) {
1339 return "somehow failed to find part_pkg with pkgpart $pkgpart!\n";
1342 my %options = $part_pkg->options;
1343 my %pkg_svc = map { $_->svcpart => $_->quantity } $part_pkg->pkg_svc;
1344 my $primary_svc = $part_pkg->svcpart;
1345 my $new = new FS::part_pkg { $part_pkg->hash };
1347 foreach my $class ( keys %{ $taxproduct->{$pkgpart} } ) {
1348 warn "working with class '$class'\n" if $DEBUG;
1349 my $part_pkg_taxproduct =
1350 qsearchs( 'part_pkg_taxproduct',
1351 { taxproduct => $taxproduct->{$pkgpart}->{$class},
1352 data_vendor => $format,
1356 unless ( $part_pkg_taxproduct ) {
1357 return "failed to find part_pkg_taxproduct (".
1358 $taxproduct->{$pkgpart}->{$class}. ") for pkgpart $pkgpart\n";
1361 if ( $class eq '' ) {
1362 $new->taxproductnum($part_pkg_taxproduct->taxproductnum);
1366 $options{"usage_taxproductnum_$class"} =
1367 $part_pkg_taxproduct->taxproductnum;
1371 my $error = $new->replace( $part_pkg,
1372 'pkg_svc' => \%pkg_svc,
1373 'primary_svc' => $primary_svc,
1374 'options' => \%options,
1377 return $error if $error;
1384 sub _restore_remembered_disabled_taxes {
1385 my ( $job, $format, $disabled_tax_rate ) = @_;
1387 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1388 my $count = scalar(keys %$disabled_tax_rate);
1389 foreach my $key (keys %$disabled_tax_rate) {
1390 if ( time - $min_sec > $last ) {
1391 $job->update_statustext(
1392 int( 100 * $imported / $count ). ",Disabling tax rates"
1397 my ($geocode,$taxclass) = split /:/, $key, 2;
1398 my @tax_class = qsearch( 'tax_class', { data_vendor => $format,
1399 taxclass => $taxclass,
1401 return "found multiple tax_class records for format $format class $taxclass"
1402 if scalar(@tax_class) > 1;
1404 unless (scalar(@tax_class)) {
1405 warn "no tax_class for format $format class $taxclass\n";
1410 qsearch('tax_rate', { data_vendor => $format,
1411 geocode => $geocode,
1412 taxclassnum => $tax_class[0]->taxclassnum,
1416 if (scalar(@tax_rate) > 1) {
1417 return "found multiple tax_rate records for format $format geocode ".
1418 "$geocode and taxclass $taxclass ( taxclassnum ".
1419 $tax_class[0]->taxclassnum. " )";
1422 if (scalar(@tax_rate)) {
1423 $tax_rate[0]->disabled('Y');
1424 my $error = $tax_rate[0]->replace;
1425 return $error if $error;
1430 sub _remove_old_tax_data {
1431 my ( $job, $format ) = @_;
1434 my $error = $job->update_statustext( "0,Removing old tax data" );
1435 die $error if $error;
1437 my $sql = "UPDATE public.tax_rate_location SET disabled='Y' ".
1438 "WHERE data_vendor = ". $dbh->quote($format);
1439 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1442 tax_rate part_pkg_taxrate part_pkg_taxproduct tax_class cust_tax_location
1444 foreach my $table ( @table ) {
1445 $sql = "DELETE FROM public.$table WHERE data_vendor = ".
1446 $dbh->quote($format);
1447 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1450 if ( $format eq 'cch' ) {
1451 $sql = "DELETE FROM public.cust_tax_location WHERE data_vendor = ".
1452 $dbh->quote("$format-zip");
1453 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1459 sub _create_temporary_tables {
1460 my ( $job, $format ) = @_;
1463 my $error = $job->update_statustext( "0,Creating temporary tables" );
1464 die $error if $error;
1466 my @table = qw( tax_rate
1473 foreach my $table ( @table ) {
1475 "CREATE TEMPORARY TABLE $table ( LIKE $table INCLUDING DEFAULTS )";
1476 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1482 sub _copy_from_temp {
1483 my ( $job, $format ) = @_;
1486 my $error = $job->update_statustext( "0,Making permanent" );
1487 die $error if $error;
1489 my @table = qw( tax_rate
1496 foreach my $table ( @table ) {
1498 "INSERT INTO public.$table SELECT * from $table";
1499 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1505 =item process_download_and_reload
1507 Download and process a tax update as a queued JSRPC job after wiping the
1508 existing wipable tax data.
1512 sub process_download_and_reload {
1513 _process_reload('process_download_and_update', @_);
1517 =item process_batch_reload
1519 Load and process a tax update from the provided files as a queued JSRPC job
1520 after wiping the existing wipable tax data.
1524 sub process_batch_reload {
1525 _process_reload('_perform_batch_import', @_);
1529 sub _process_reload {
1530 my ( $method, $job ) = ( shift, shift );
1532 my $param = thaw(decode_base64($_[0]));
1533 my $format = $param->{'format'}; #well... this is all cch specific
1535 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1537 if ( $job ) { # progress bar
1538 my $error = $job->update_statustext( 0 );
1539 die $error if $error;
1542 my $oldAutoCommit = $FS::UID::AutoCommit;
1543 local $FS::UID::AutoCommit = 0;
1548 "SELECT count(*) FROM part_pkg_taxoverride JOIN tax_class ".
1549 "USING (taxclassnum) WHERE data_vendor = '$format'";
1550 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1552 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1553 die "Don't (yet) know how to handle part_pkg_taxoverride records."
1554 if $sth->fetchrow_arrayref->[0];
1556 # really should get a table EXCLUSIVE lock here
1558 #remember disabled taxes
1559 my %disabled_tax_rate = ();
1560 $error ||= _remember_disabled_taxes( $job, $format, \%disabled_tax_rate );
1562 #remember tax products
1563 my %taxproduct = ();
1564 $error ||= _remember_tax_products( $job, $format, \%taxproduct );
1567 $error ||= _create_temporary_tables( $job, $format );
1571 my $args = '$job, @_';
1572 eval "$method($args);";
1576 #restore taxproducts
1577 $error ||= _restore_remembered_tax_products( $job, $format, \%taxproduct );
1581 _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate );
1583 #wipe out the old data
1584 $error ||= _remove_old_tax_data( $job, $format );
1587 $error ||= _copy_from_temp( $job, $format );
1590 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1595 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1599 =item process_download_and_update
1601 Download and process a tax update as a queued JSRPC job
1605 sub process_download_and_update {
1608 my $param = thaw(decode_base64(shift));
1609 my $format = $param->{'format'}; #well... this is all cch specific
1611 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1613 if ( $job ) { # progress bar
1614 my $error = $job->update_statustext( 0);
1615 die $error if $error;
1618 my $cache_dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
1619 my $dir = $cache_dir. 'taxdata';
1621 mkdir $dir or die "can't create $dir: $!\n";
1624 if ($format eq 'cch') {
1626 my @namelist = qw( code detail geocode plus4 txmatrix zip );
1628 my $conf = new FS::Conf;
1629 die "direct download of tax data not enabled\n"
1630 unless $conf->exists('taxdatadirectdownload');
1631 my ( $urls, $username, $secret, $states ) =
1632 $conf->config('taxdatadirectdownload');
1633 die "No tax download URL provided. ".
1634 "Did you set the taxdatadirectdownload configuration value?\n"
1642 # really should get a table EXCLUSIVE lock here
1643 # check if initial import or update
1645 # relying on mkdir "$dir.new" as a mutex
1647 my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'";
1648 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1649 $sth->execute() or die $sth->errstr;
1650 my $update = $sth->fetchrow_arrayref->[0];
1652 # create cache and/or rotate old tax data
1657 opendir(my $dirh, "$dir.9") or die "failed to open $dir.9: $!\n";
1658 foreach my $file (readdir($dirh)) {
1659 unlink "$dir.9/$file" if (-f "$dir.9/$file");
1665 for (8, 7, 6, 5, 4, 3, 2, 1) {
1666 if ( -e "$dir.$_" ) {
1667 rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n";
1670 rename "$dir", "$dir.1" or die "can't rename $dir: $!\n";
1674 die "can't find previous tax data\n" if $update;
1678 mkdir "$dir.new" or die "can't create $dir.new: $!\n";
1680 # fetch and unpack the zip files
1682 _cch_fetch_and_unzip( $job, $urls, $secret, "$dir.new" );
1684 # extract csv files from the dbf files
1686 foreach my $name ( @namelist ) {
1687 _cch_extract_csv_from_dbf( $job, $dir, $name );
1690 # generate the diff files
1693 foreach my $name ( @namelist ) {
1694 my $difffile = "$dir.new/$name.txt";
1696 my $error = $job->update_statustext( "0,Comparing to previous $name" );
1697 die $error if $error;
1698 warn "processing $dir.new/$name.txt\n" if $DEBUG;
1699 my $olddir = $update ? "$dir.1" : "";
1700 $difffile = _perform_cch_diff( $name, "$dir.new", $olddir );
1702 $difffile =~ s/^$cache_dir//;
1703 push @list, "${name}file:$difffile";
1706 # perform the import
1707 local $keep_cch_files = 1;
1708 $param->{uploaded_files} = join( ',', @list );
1709 $param->{format} .= '-update' if $update;
1711 _perform_batch_import( $job, encode_base64( nfreeze( $param ) ) );
1713 rename "$dir.new", "$dir"
1714 or die "cch tax update processed, but can't rename $dir.new: $!\n";
1717 die "Unknown format: $format";
1721 =item browse_queries PARAMS
1723 Returns a list consisting of a hashref suited for use as the argument
1724 to qsearch, and sql query string. Each is based on the PARAMS hashref
1725 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
1726 from a form. This conveniently creates the query hashref and count_query
1727 string required by the browse and search elements. As a side effect,
1728 the PARAMS hashref is untainted and keys with unexpected values are removed.
1732 sub browse_queries {
1736 'table' => 'tax_rate',
1738 'order_by' => 'ORDER BY geocode, taxclassnum',
1743 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
1744 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
1746 delete $params->{data_vendor};
1749 if ( $params->{geocode} =~ /^(\w+)$/ ) {
1750 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1751 'geocode LIKE '. dbh->quote($1.'%');
1753 delete $params->{geocode};
1756 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
1757 qsearchs( 'tax_class', {'taxclassnum' => $1} )
1760 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1761 ' taxclassnum = '. dbh->quote($1)
1763 delete $params->{taxclassnun};
1767 if ( $params->{tax_type} =~ /^(\d+)$/ );
1768 delete $params->{tax_type}
1772 if ( $params->{tax_cat} =~ /^(\d+)$/ );
1773 delete $params->{tax_cat}
1776 my @taxclassnum = ();
1777 if ($tax_type || $tax_cat ) {
1778 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
1779 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
1780 @taxclassnum = map { $_->taxclassnum }
1781 qsearch({ 'table' => 'tax_class',
1783 'extra_sql' => "WHERE taxclass $compare",
1787 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
1788 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
1789 if ( @taxclassnum );
1791 unless ($params->{'showdisabled'}) {
1792 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1793 "( disabled = '' OR disabled IS NULL )";
1796 $query->{extra_sql} = $extra_sql;
1798 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
1801 =item queue_liability_report PARAMS
1803 Launches a tax liability report.
1806 sub queue_liability_report {
1808 my $param = thaw(decode_base64(shift));
1811 $cgi->param('beginning', $param->{beginning});
1812 $cgi->param('ending', $param->{ending});
1813 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
1814 my $agentnum = $param->{agentnum};
1815 if ($agentnum =~ /^(\d+)$/) { $agentnum = $1; } else { $agentnum = ''; };
1816 generate_liability_report(
1817 'beginning' => $beginning,
1818 'ending' => $ending,
1819 'agentnum' => $agentnum,
1820 'p' => $param->{RootURL},
1825 =item generate_liability_report PARAMS
1827 Generates a tax liability report. Provide a hash including desired
1828 agentnum, beginning, and ending
1832 #shit, all sorts of false laxiness w/report_newtax.cgi
1833 sub generate_liability_report {
1836 my ( $count, $last, $min_sec ) = _progressbar_foo();
1838 #let us open the temp file early
1839 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
1840 my $report = new File::Temp( TEMPLATE => 'report.tax.liability.XXXXXXXX',
1842 UNLINK => 0, # not so temp
1843 ) or die "can't open report file: $!\n";
1845 my $conf = new FS::Conf;
1846 my $money_char = $conf->config('money_char') || '$';
1849 JOIN cust_bill USING ( invnum )
1850 LEFT JOIN cust_main USING ( custnum )
1854 "LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum )";
1855 my $join_tax_loc = "LEFT JOIN tax_rate_location USING ( taxratelocationnum )";
1857 my $addl_from = " $join_cust $join_loc $join_tax_loc ";
1859 my $where = "WHERE _date >= $args{beginning} AND _date <= $args{ending} ";
1862 if ( $args{agentnum} =~ /^(\d+)$/ ) {
1863 my $agent = qsearchs('agent', { 'agentnum' => $1 } );
1864 die "agent not found" unless $agent;
1865 $agentname = $agent->agent;
1866 $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
1869 #my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' );
1870 my @taxparams = qw( city county state locationtaxid );
1871 my @params = ('itemdesc', @taxparams);
1873 my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city';
1875 #false laziness w/FS::Report::Table::Monthly (sub should probably be moved up
1876 #to FS::Report or FS::Record or who the fuck knows where)
1877 my $scalar_sql = sub {
1878 my( $r, $param, $sql ) = @_;
1879 my $sth = dbh->prepare($sql) or die dbh->errstr;
1880 $sth->execute( map $r->$_(), @$param )
1881 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1882 $sth->fetchrow_arrayref->[0] || 0;
1890 my @tax_and_location = qsearch({ table => 'cust_bill_pkg',
1892 hashref => { pkgpart => 0 },
1893 addl_from => $addl_from,
1894 extra_sql => $where,
1896 $count = scalar(@tax_and_location);
1897 foreach my $t ( @tax_and_location ) {
1900 if ( time - $min_sec > $last ) {
1901 $args{job}->update_statustext( int( 100 * $calculated / $count ).
1908 #my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam;
1909 my $label = join('~', map { $t->$_ } @params);
1910 $label = 'Tax'. $label if $label =~ /^~/;
1911 unless ( exists( $taxes{$label} ) ) {
1912 my ($baselabel, @trash) = split /~/, $label;
1914 $taxes{$label}->{'label'} = join(', ', split(/~/, $label) );
1915 $taxes{$label}->{'url_param'} =
1916 join(';', map { "$_=". uri_escape($t->$_) } @params);
1918 my $payby_itemdesc_loc =
1919 " payby != 'COMP' ".
1920 "AND ( itemdesc = ? OR ? = '' AND itemdesc IS NULL ) ".
1921 "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ }
1926 "FROM cust_bill_pkg $addl_from $where AND $payby_itemdesc_loc";
1928 my $sql = "SELECT SUM(amount) $taxwhere AND cust_bill_pkg.pkgnum = 0";
1930 my $x = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql );
1932 $taxes{$label}->{'tax'} += $x;
1935 "JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum)";
1937 "FROM cust_bill_pkg $addl_from $creditfrom $where AND $payby_itemdesc_loc";
1939 $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
1940 " $creditwhere AND cust_bill_pkg.pkgnum = 0";
1942 my $y = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql );
1944 $taxes{$label}->{'credit'} += $y;
1946 unless ( exists( $taxes{$baselabel} ) ) {
1948 $basetaxes{$baselabel}->{'label'} = $baselabel;
1949 $basetaxes{$baselabel}->{'url_param'} = "itemdesc=$baselabel";
1950 $basetaxes{$baselabel}->{'base'} = 1;
1954 $basetaxes{$baselabel}->{'tax'} += $x;
1955 $basetaxes{$baselabel}->{'credit'} += $y;
1959 # calculate customer-exemption for this tax
1960 # calculate package-exemption for this tax
1961 # calculate monthly exemption (texas tax) for this tax
1962 # count up all the cust_tax_exempt_pkg records associated with
1963 # the actual line items.
1970 $args{job}->update_statustext( "0,Sorted" );
1976 foreach my $tax ( sort { $a cmp $b } keys %taxes ) {
1977 my ($base, @trash) = split '~', $tax;
1978 my $basetax = delete( $basetaxes{$base} );
1980 if ( $basetax->{tax} == $taxes{$tax}->{tax} ) {
1981 $taxes{$tax}->{base} = 1;
1983 push @taxes, $basetax;
1986 push @taxes, $taxes{$tax};
1993 'credit' => $credit,
1998 my $dateagentlink = "begin=$args{beginning};end=$args{ending}";
1999 $dateagentlink .= ';agentnum='. $args{agentnum}
2000 if length($agentname);
2001 my $baselink = $args{p}. "search/cust_bill_pkg.cgi?$dateagentlink";
2002 my $creditlink = $args{p}. "search/cust_credit_bill_pkg.html?$dateagentlink";
2004 print $report <<EOF;
2006 <% include("/elements/header.html", "$agentname Tax Report - ".
2008 ? time2str('%h %o %Y ', $args{beginning} )
2012 ( $args{ending} == 4294967295
2014 : time2str('%h %o %Y', $args{ending} )
2019 <% include('/elements/table-grid.html') %>
2022 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2023 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2024 <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
2025 <TH CLASS="grid" BGCOLOR="#cccccc"> </TH>
2026 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2027 <TH CLASS="grid" BGCOLOR="#cccccc">Tax credited</TH>
2031 my $bgcolor1 = '#eeeeee';
2032 my $bgcolor2 = '#ffffff';
2035 $count = scalar(@taxes);
2037 foreach my $tax ( @taxes ) {
2040 if ( time - $min_sec > $last ) {
2041 $args{job}->update_statustext( int( 100 * $calculated / $count ).
2048 if ( $bgcolor eq $bgcolor1 ) {
2049 $bgcolor = $bgcolor2;
2051 $bgcolor = $bgcolor1;
2055 if ( $tax->{'label'} ne 'Total' ) {
2056 $link = ';'. $tax->{'url_param'};
2059 print $report <<EOF;
2061 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>"><% '$tax->{label}' %></TD>
2062 <% ($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2063 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>" ALIGN="right">
2064 <A HREF="<% '$baselink$link' %>;istax=1"><% '$money_char' %><% sprintf('%.2f', $tax->{'tax'} ) %></A>
2066 <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2067 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>"></TD>
2068 <% ($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2069 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>" ALIGN="right">
2070 <A HREF="<% '$creditlink$link' %>;istax=1;iscredit=rate"><% '$money_char' %><% sprintf('%.2f', $tax->{'credit'} ) %></A>
2072 <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2077 print $report <<EOF;
2084 my $reportname = $report->filename;
2087 my $dropstring = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/report.';
2088 $reportname =~ s/^$dropstring//;
2090 my $reporturl = "%%%ROOTURL%%%/misc/queued_report?report=$reportname";
2091 die "<a href=$reporturl>view</a>\n";
2101 Mixing automatic and manual editing works poorly at present.
2103 Tax liability calculations take too long and arguably don't belong here.
2104 Tax liability report generation not entirely safe (escaped).
2108 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base