use Date::Parse;
use DateTime;
use DateTime::Format::Strptime;
-use Storable qw( thaw nfreeze );
use IO::File;
use File::Temp;
use Text::CSV_XS;
use LWP::UserAgent;
use HTTP::Request;
use HTTP::Response;
-use MIME::Base64;
use DBIx::DBSchema;
use DBIx::DBSchema::Table;
use DBIx::DBSchema::Column;
=item taxclassnum
-a foreign key into FS::tax_class - the type of tax
-referenced but FS::part_pkg_taxrate
-eitem effective_date
+a foreign key into FS::tax_class - the type of tax referenced by
+FS::part_pkg_taxrate
+
+=item effective_date
the time after which the tax applies
|| $self->ut_text('geocode')
|| $self->ut_textn('data_vendor')
|| $self->ut_cch_textn('location')
- || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
+ || $self->ut_foreign_keyn('taxclassnum', 'tax_class', 'taxclassnum')
|| $self->ut_snumbern('effective_date')
|| $self->ut_float('tax')
|| $self->ut_floatn('excessrate')
=item maxtype_name
-Returns the human understandable value associated with the maxtype column
+Returns the human understandable value associated with the maxtype column.
=cut
+# XXX these are non-functional, and most of them are horrible to implement
+# in our current model
+
%tax_maxtypes = ( '0' => 'receipts per invoice',
'1' => 'receipts per item',
'2' => 'total utility charges per utility tax year',
'3' => 'total charges per utility tax year',
'4' => 'receipts per access line',
+ '7' => 'total utility charges per calendar year',
'9' => 'monthly receipts per location',
+ '10' => 'monthly receipts exceeds taxbase and total tax per month does not exceed maxtax', # wtf?
+ '11' => 'receipts/units per access line',
+ '14' => 'units per invoice',
+ '15' => 'units per month',
+ '18' => 'units per account',
);
sub maxtype_name {
$tax_passtypes{$self->passtype};
}
-=item taxline TAXABLES, [ OPTIONSHASH ]
+=item taxline_cch TAXABLES, [ OPTIONSHASH ]
Returns a listref of a name and an amount of tax calculated for the list
of packages/amounts referenced by TAXABLES. If an error occurs, a message
=cut
-sub taxline {
+sub taxline_cch {
my $self = shift;
+ # this used to accept a hash of options but none of them did anything
+ # so it's been removed.
my $taxables;
- my %opt = ();
if (ref($_[0]) eq 'ARRAY') {
$taxables = shift;
- %opt = @_;
}else{
$taxables = [ @_ ];
#exemptions would be broken in this case
}
my $maxtype = $self->maxtype || 0;
- if ($maxtype != 0 && $maxtype != 1 && $maxtype != 9) {
+ if ($maxtype != 0 && $maxtype != 1
+ && $maxtype != 14 && $maxtype != 15
+ && $maxtype != 18 # sigh
+ ) {
return $self->_fatal_or_null( 'tax with "'.
$self->maxtype_name. '" threshold'
);
- }
-
- if ($maxtype == 9) {
- return
- $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
- # "texas" tax
- }
+ } # I don't know why, it's not like there are maxtypes that we DO support
# we treat gross revenue as gross receipts and expect the tax data
# to DTRT (i.e. tax on tax rules)
# the tax or fee is applied to taxbase or feebase and then
# the excessrate or excess fee is applied to taxmax or feemax
+ if ( ($self->taxmax > 0 and $taxable_charged > $self->taxmax) or
+ ($self->feemax > 0 and $taxable_units > $self->feemax) ) {
+ # throw an error
+ # (why not just cap taxable_charged/units at the taxmax/feemax? because
+ # it's way more complicated than that. this won't even catch every case
+ # where a bracket maximum should apply.)
+ return $self->_fatal_or_null( 'tax base > taxmax/feemax for tax'.$self->taxnum );
+ }
+
$amount += $taxable_charged * $self->tax;
$amount += $taxable_units * $self->fee;
sub _fatal_or_null {
my ($self, $error) = @_;
+ $DB::single = 1; # not a mistake
+
my $conf = new FS::Conf;
$error = "can't yet handle ". $error;
}
+
+=item find_or_insert
+
+Finds an existing tax definition matching the data_vendor, taxname,
+taxclassnum, and geocode of this one, if one exists, and sets the contents of
+this tax rate equal to that one (including its taxnum). If an existing
+definition is not found, inserts this one. Returns an error string if
+inserting a record failed.
+
+=cut
+
+sub find_or_insert {
+ my $self = shift;
+ # this doesn't uniquely identify CCH taxes (kinda goofy, I know)
+ die "find_or_insert is not compatible with CCH taxes\n"
+ if $self->data_vendor eq 'cch';
+
+ my @keys = (qw(data_vendor taxname taxclassnum geocode));
+ my %hash = map { $_ => $self->get($_) } @keys;
+ my $existing = qsearchs('tax_rate', \%hash);
+ if ($existing) {
+ foreach ($self->fields) {
+ $self->set($_, $existing->get($_));
+ }
+ return;
+ } else {
+ return $self->insert;
+ }
+}
+
=back
=head1 SUBROUTINES
}
my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
- unless ($tax_rate) {
+ if (!$tax_rate) {
$dbh->rollback if $oldAutoCommit;
$tax_rate = $delete{$_};
- return "can't find tax_rate to delete for: ".
- #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
- join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
- }
- my $error = $tax_rate->delete;
+ warn "WARNING: can't find tax_rate to delete for: ".
+ join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) ).
+ " (ignoring)\n";
+ } else {
+ my $error = $tax_rate->delete; # XXX we really should not do this
+ # (it orphans CBPTRL records)
- if ( $error ) {
- $dbh->rollback if $oldAutoCommit;
- my $hashref = $delete{$_};
- $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
- return "can't delete tax_rate for $line: $error";
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ my $hashref = $delete{$_};
+ $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
+ return "can't delete tax_rate for $line: $error";
+ }
}
$imported++;
=cut
sub process_batch_import {
- my $job = shift;
-
- my $oldAutoCommit = $FS::UID::AutoCommit;
- local $FS::UID::AutoCommit = 0;
- my $dbh = dbh;
-
- my $param = thaw(decode_base64(shift));
- my $args = '$job, encode_base64( nfreeze( $param ) )';
+ my ($job, $param) = @_;
- my $method = '_perform_batch_import';
if ( $param->{reload} ) {
- $method = 'process_batch_reload';
- }
-
- eval "$method($args);";
- if ($@) {
- $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
- die $@;
+ process_batch_reload($job, $param);
+ } else {
+ # '_perform', yuck
+ _perform_batch_import($job, $param);
}
- #success!
- $dbh->commit or die $dbh->errstr if $oldAutoCommit;
}
sub _perform_batch_import {
- my $job = shift;
+ my ($job, $param) = @_;
- my $param = thaw(decode_base64(shift));
- my $format = $param->{'format'}; #well... this is all cch specific
+ my $oldAutoCommit = $FS::UID::AutoCommit;
+ local $FS::UID::AutoCommit = 0;
+ my $dbh = dbh;
+
+ my $format = $param->{'format'};
my $files = $param->{'uploaded_files'}
or die "No files provided.";
my (%files) = map { /^(\w+):((taxdata\/\w+\.\w+\/)?[\.\w]+)$/ ? ($1,$2):() }
split /,/, $files;
+ my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
+ my $error = '';
+
if ( $format eq 'cch' || $format eq 'cch-fixed'
|| $format eq 'cch-update' || $format eq 'cch-fixed-update' )
{
- my $oldAutoCommit = $FS::UID::AutoCommit;
- local $FS::UID::AutoCommit = 0;
- my $dbh = dbh;
- my $error = '';
my @insert_list = ();
my @delete_list = ();
my @predelete_list = ();
my $insertname = '';
my $deletename = '';
- my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
my @list = ( 'GEOCODE', \&FS::tax_rate_location::batch_import,
'CODE', \&FS::tax_class::batch_import,
unlink $file or warn "Can't delete $file: $!";
}
- if ($error) {
- $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
- die $error;
- }else{
- $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+ } elsif ( $format =~ /^billsoft-(\w+)$/ ) {
+ my $mode = $1;
+ my $file = $dir.'/'.$files{'file'};
+ open my $fh, "< $file" or $error ||= "Can't open file $file: $!";
+ my @param = (
+ {
+ filehandle => $fh,
+ format => 'billsoft',
+ }, $job);
+ if ( $mode eq 'pcode' ) {
+ $error ||= FS::cust_tax_location::batch_import(@param);
+ seek $fh, 0, 0;
+ $error ||= FS::tax_rate_location::batch_import(@param);
+ } elsif ( $mode eq 'taxclass' ) {
+ $error ||= FS::tax_class::batch_import(@param);
+ } elsif ( $mode eq 'taxproduct' ) {
+ $error ||= FS::part_pkg_taxproduct::batch_import(@param);
+ } else {
+ die "unknown import mode 'billsoft-$mode'\n";
}
- }else{
+ } else {
die "Unknown format: $format";
}
+ if ($error) {
+ $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+ die $error;
+ } else {
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+ }
+
}
+#
+#
+# EVERYTHING THAT FOLLOWS IS CCH-SPECIFIC.
+#
+#
sub _perform_cch_tax_import {
my ( $job, $predelete_list, $insert_list, $delete_list, $addl_param ) = @_;
=item process_download_and_reload
Download and process a tax update as a queued JSRPC job after wiping the
-existing wipable tax data.
+existing wipeable tax data.
=cut
sub process_download_and_reload {
- _process_reload('process_download_and_update', @_);
+ _process_reload(\&process_download_and_update, @_);
}
-
+#
+#
+# END OF CCH STUFF
+#
+#
+
=item process_batch_reload
Load and process a tax update from the provided files as a queued JSRPC job
=cut
sub process_batch_reload {
- _process_reload('_perform_batch_import', @_);
+ _process_reload(\&_perform_batch_import, @_);
}
-
sub _process_reload {
- my ( $method, $job ) = ( shift, shift );
-
- my $param = thaw(decode_base64($_[0]));
- my $format = $param->{'format'}; #well... this is all cch specific
+ my ( $continuation, $job, $param ) = @_;
+ my $format = $param->{'format'};
my ( $imported, $last, $min_sec ) = _progressbar_foo();
my $dbh = dbh;
my $error = '';
- my $sql =
- "SELECT count(*) FROM part_pkg_taxoverride JOIN tax_class ".
- "USING (taxclassnum) WHERE data_vendor = '$format'";
- my $sth = $dbh->prepare($sql) or die $dbh->errstr;
- $sth->execute
- or die "Unexpected error executing statement $sql: ". $sth->errstr;
- die "Don't (yet) know how to handle part_pkg_taxoverride records."
- if $sth->fetchrow_arrayref->[0];
-
- # really should get a table EXCLUSIVE lock here
-
- #remember disabled taxes
- my %disabled_tax_rate = ();
- $error ||= _remember_disabled_taxes( $job, $format, \%disabled_tax_rate );
-
- #remember tax products
- my %taxproduct = ();
- $error ||= _remember_tax_products( $job, $format, \%taxproduct );
-
- #create temp tables
- $error ||= _create_temporary_tables( $job, $format );
-
- #import new data
- unless ($error) {
- my $args = '$job, @_';
- eval "$method($args);";
- $error = $@ if $@;
- }
+ if ( $format =~ /^cch/ ) {
+ # no, THIS part is CCH specific
- #restore taxproducts
- $error ||= _restore_remembered_tax_products( $job, $format, \%taxproduct );
+ my $sql =
+ "SELECT count(*) FROM part_pkg_taxoverride JOIN tax_class ".
+ "USING (taxclassnum) WHERE data_vendor = '$format'";
+ my $sth = $dbh->prepare($sql) or die $dbh->errstr;
+ $sth->execute
+ or die "Unexpected error executing statement $sql: ". $sth->errstr;
+ die "Don't (yet) know how to handle part_pkg_taxoverride records."
+ if $sth->fetchrow_arrayref->[0];
+
+ # really should get a table EXCLUSIVE lock here
+
+ #remember disabled taxes
+ my %disabled_tax_rate = ();
+ $error ||= _remember_disabled_taxes( $job, $format, \%disabled_tax_rate );
+
+ #remember tax products
+ my %taxproduct = ();
+ $error ||= _remember_tax_products( $job, $format, \%taxproduct );
+
+ #create temp tables
+ $error ||= _create_temporary_tables( $job, $format );
+
+ #import new data
+ unless ($error) {
+ eval { &{$continuation}( $job, $param ) };
+ $error = $@ if $@;
+ }
+
+ #restore taxproducts
+ $error ||= _restore_remembered_tax_products( $job, $format, \%taxproduct );
- #disable tax_rates
- $error ||=
- _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate );
+ #disable tax_rates
+ $error ||=
+ _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate );
+
+ #wipe out the old data
+ $error ||= _remove_old_tax_data( $job, $format );
+
+ #untemporize
+ $error ||= _copy_from_temp( $job, $format );
+
+ } elsif ( $format =~ /^billsoft-(\w+)/ ) {
+
+ my $mode = $1;
+ my @sql;
+ if ( $mode eq 'pcode' ) {
+ push @sql,
+ "DELETE FROM cust_tax_location WHERE data_vendor = 'billsoft'",
+ "UPDATE tax_rate_location SET disabled = 'Y' WHERE data_vendor = 'billsoft'";
+ } elsif ( $mode eq 'taxclass' ) {
+ push @sql,
+ "DELETE FROM tax_class WHERE data_vendor = 'billsoft'";
+ } elsif ( $mode eq 'taxproduct' ) {
+ push @sql,
+ "DELETE FROM part_pkg_taxproduct WHERE data_vendor = 'billsoft'";
+ }
- #wipe out the old data
- $error ||= _remove_old_tax_data( $job, $format );
+ foreach (@sql) {
+ if (!$dbh->do($_)) {
+ $error = $dbh->errstr;
+ last;
+ }
+ }
- #untemporize
- $error ||= _copy_from_temp( $job, $format );
+ unless ( $error ) {
+ local $@;
+ eval { &{ $continuation }($job, $param) };
+ $error = $@;
+ }
+ } # if ($format ...)
if ($error) {
$dbh->rollback or die $dbh->errstr if $oldAutoCommit;
sub process_download_and_update {
my $job = shift;
- my $param = thaw(decode_base64(shift));
+ my $param = shift;
my $format = $param->{'format'}; #well... this is all cch specific
my ( $imported, $last, $min_sec ) = _progressbar_foo();
$param->{uploaded_files} = join( ',', @list );
$param->{format} .= '-update' if $update;
$error ||=
- _perform_batch_import( $job, encode_base64( nfreeze( $param ) ) );
+ _perform_batch_import( $job, $param );
rename "$dir.new", "$dir"
or die "cch tax update processed, but can't rename $dir.new: $!\n";
=item queue_liability_report PARAMS
Launches a tax liability report.
+
+PARAMS needs to be a base64-encoded Storable hash containing:
+- beginning: the start date, as a I<user-readable string> (not a timestamp).
+- end: the end date of the report, likewise.
+- agentnum: the agent to limit the report to, if any.
+
=cut
sub queue_liability_report {
my $job = shift;
- my $param = thaw(decode_base64(shift));
+ my $param = shift;
my $cgi = new CGI;
$cgi->param('beginning', $param->{beginning});
=item generate_liability_report PARAMS
-Generates a tax liability report. Provide a hash including desired
-agentnum, beginning, and ending
+Generates a tax liability report. PARAMS must include:
+
+- beginning, as a timestamp
+- ending, as a timestamp
+- p: the Freeside root URL, for generating links
+- agentnum (optional)
=cut
my %taxes = ();
my %basetaxes = ();
my $calculated = 0;
+
+ # get all distinct tuples of (tax name, state, county, city, locationtaxid)
+ # for taxes that have been charged
+ # (state, county, city are from tax_rate_location, not from customer data)
my @tax_and_location = qsearch({ table => 'cust_bill_pkg',
select => $select,
hashref => { pkgpart => 0 },
addl_from => $addl_from,
extra_sql => $where,
+ debug => 1,
});
$count = scalar(@tax_and_location);
foreach my $t ( @tax_and_location ) {
$taxes{$label}->{'url_param'} =
join(';', map { "$_=". uri_escape($t->$_) } @params);
- my $payby_itemdesc_loc =
- " payby != 'COMP' ".
- "AND ( itemdesc = ? OR ? = '' AND itemdesc IS NULL ) ".
+ my $itemdesc_loc =
+ # " payby != 'COMP' ". # breaks the entire report under 4.x
+ # # and unnecessary since COMP accounts don't
+ # # get taxes calculated in the first place
+ " ( itemdesc = ? OR ? = '' AND itemdesc IS NULL ) ".
"AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ }
@taxparams
);
my $taxwhere =
- "FROM cust_bill_pkg $addl_from $where AND $payby_itemdesc_loc";
+ "FROM cust_bill_pkg $addl_from $where AND $itemdesc_loc";
my $sql = "SELECT SUM(amount) $taxwhere AND cust_bill_pkg.pkgnum = 0";
my $creditfrom =
"JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum)";
my $creditwhere =
- "FROM cust_bill_pkg $addl_from $creditfrom $where AND $payby_itemdesc_loc";
+ "FROM cust_bill_pkg $addl_from $creditfrom $where AND $itemdesc_loc";
$sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
" $creditwhere AND cust_bill_pkg.pkgnum = 0";
my $dateagentlink = "begin=$args{beginning};end=$args{ending}";
$dateagentlink .= ';agentnum='. $args{agentnum}
if length($agentname);
- my $baselink = $args{p}. "search/cust_bill_pkg.cgi?$dateagentlink";
+ my $baselink = $args{p}. "search/cust_bill_pkg.cgi?vendortax=1;" .
+ $dateagentlink;
my $creditlink = $args{p}. "search/cust_credit_bill_pkg.html?$dateagentlink";
print $report <<EOF;
=head1 BUGS
+ Highly specific to CCH taxes. This should arguably go in some kind of
+ subclass (FS::tax_rate::CCH) with auto-reblessing, similar to part_pkg
+ subclasses. But currently there aren't any other options, so.
+
Mixing automatic and manual editing works poorly at present.
Tax liability calculations take too long and arguably don't belong here.
Tax liability report generation not entirely safe (escaped).
+ Sparse documentation.
+
=head1 SEE ALSO
L<FS::Record>, L<FS::cust_location>, L<FS::cust_bill>
projects / freeside.git / blobdiff