diff options
61 files changed, 3062 insertions, 583 deletions
diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index 9cac9e1..77267cf 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -2453,8 +2453,9 @@ and customer address. Include units.', { 'key' => 'enable_taxproducts', 'section' => 'billing', - 'description' => 'Enable per-package mapping to vendor tax data from CCH or elsewhere.', - 'type' => 'checkbox', + 'description' => 'Tax data vendor you are using.', + 'type' => 'select', + 'select_enum' => [ 'cch', 'billsoft', 'avalara' ], }, { @@ -2472,6 +2473,20 @@ and customer address. Include units.', }, { + 'key' => 'billsoft-company_code', + 'section' => 'billing', + 'description' => 'Billsoft tax service company code (3 letters)', + 'type' => 'text', + }, + + { + 'key' => 'avalara-taxconfig', + 'section' => 'billing', + 'description' => 'Avalara tax service configuration. Four lines: company code, account number, license key, test mode (1 to enable).', + 'type' => 'textarea', + }, + + { 'key' => 'welcome_msgnum', 'section' => 'notification', 'description' => 'Template to use for welcome messages when a svc_acct record is created.', diff --git a/FS/FS/Cron/tax_batch.pm b/FS/FS/Cron/tax_batch.pm new file mode 100644 index 0000000..59da135 --- /dev/null +++ b/FS/FS/Cron/tax_batch.pm @@ -0,0 +1,26 @@ +package FS::Cron::tax_batch; + +use FS::TaxEngine; +use FS::queue; +use base qw( Exporter ); +@EXPORT_OK = 'process_tax_batch'; + +sub process_tax_batch { + my %opt = @_; + my $engine = FS::TaxEngine->new; + return unless $engine->info->{batch}; + if ( $opt{'m'} ) { + # then there may be queued_bill jobs running; wait for them to finish + while(1) { + my $num_jobs = + FS::queue->count("job = 'FS::cust_main::queued_bill' AND ". + "status != 'failed'"); + last if $num_jobs == 0; + warn "Waiting for billing jobs to finish ($num_jobs still active)...\n"; + sleep(30); + } + } + $engine->transfer_batch(%opt); +} + +1; diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm index 93eca5e..900da10 100644 --- a/FS/FS/Mason.pm +++ b/FS/FS/Mason.pm @@ -390,6 +390,8 @@ if ( -e $addl_handler_use_file ) { use FS::deploy_zone; use FS::deploy_zone_block; use FS::deploy_zone_vertex; + use FS::TaxEngine; + use FS::tax_status; # Sammath Naur if ( $FS::Mason::addl_handler_use ) { diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 51cb6dc..a667f4e 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -1766,17 +1766,14 @@ files. Currently only supports a single file named "file". =cut -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process_batch_import { - my($job, $opt) = ( shift, shift ); + my($job, $opt, $param) = @_; my $table = $opt->{table}; my @pass_params = $opt->{params} ? @{ $opt->{params} } : (); my %formats = %{ $opt->{formats} }; - my $param = thaw(decode_base64(shift)); warn Dumper($param) if $DEBUG; my $files = $param->{'uploaded_files'} diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index ae34bba..53e78de 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -691,6 +691,8 @@ sub tables_hashref { 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy 'promised_date', @date_type, '', '', + + 'pending', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'invnum', 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh @@ -1026,7 +1028,8 @@ sub tables_hashref { 'cust_bill_pkg_detail' => { 'columns' => [ - 'detailnum', 'serial', '', '', '', '', + 'detailnum', 'serial', '', '', '', '', + # bigserial? this table will eventually be as big as cdr... 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable 'pkgnum', 'int', 'NULL', '', '', '', # deprecated 'invnum', 'int', 'NULL', '', '', '', # deprecated @@ -1038,7 +1041,7 @@ sub tables_hashref { 'accountcode', 'varchar', 'NULL', 20, '', '', 'startdate', @date_type, '', '', 'regionname', 'varchar', 'NULL', $char_d, '', '', - 'detail', 'varchar', '', 255, '', '', + 'detail', 'varchar', '', 255, '', '', ], 'primary_key' => 'detailnum', 'unique' => [], @@ -1625,6 +1628,7 @@ sub tables_hashref { 'message_noemail', 'char', 'NULL', 1, '', '', 'bill_locationnum', 'int', 'NULL', '', '', '', 'ship_locationnum', 'int', 'NULL', '', '', '', + 'taxstatusnum', 'char', 'NULL', 32, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], @@ -1929,6 +1933,7 @@ sub tables_hashref { 'location_type', 'varchar', 'NULL', 20, '', '', 'location_number', 'varchar', 'NULL', 20, '', '', 'location_kind', 'char', 'NULL', 1, '', '', + 'incorporated', 'char', 'NULL', 1, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'locationnum', @@ -2158,7 +2163,7 @@ sub tables_hashref { 'geocode', 'varchar', 'NULL', $char_d, '', '',#cch provides 10 char 'data_vendor', 'varchar', 'NULL', $char_d, '', '',#auto update source 'location', 'varchar', 'NULL', $char_d, '', '',#provided by tax authority - 'taxclassnum', 'int', '', '', '', '', + 'taxclassnum', 'int', 'NULL', '', '', '', 'effective_date', @date_type, '', '', 'tax', @taxrate_type, '', '', # tax % 'excessrate', @taxrate_typen, '', '', # second tax % @@ -2202,6 +2207,7 @@ sub tables_hashref { 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', 'state', 'char', 'NULL', 2, '', '', + 'country', 'char', 'NULL', 2, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'taxratelocationnum', @@ -2216,8 +2222,11 @@ sub tables_hashref { 'city', 'varchar', 'NULL', $char_d, '', '', 'postalcity', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', - 'zip', 'char', '', 5, '', '', + 'zip', 'char', 'NULL', 5, '', '', 'state', 'char', '', 2, '', '', + 'country', 'char', 'NULL', 2, '', '', # should not actually be NULL, but cch... + 'ziphi', 'char', 'NULL', 10, '', '', + 'ziplo', 'char', 'NULL', 10, '', '', 'plus4hi', 'char', 'NULL', 4, '', '', 'plus4lo', 'char', 'NULL', 4, '', '', 'default_location','char', 'NULL', 1, '', '', # Y = default for zip @@ -2226,7 +2235,7 @@ sub tables_hashref { ], 'primary_key' => 'custlocationnum', 'unique' => [], - 'index' => [ [ 'zip', 'plus4lo', 'plus4hi' ] ], + 'index' => [ [ 'zip', 'plus4lo', 'plus4hi', 'ziphi', 'ziplo' ] ], }, 'tax_class' => { @@ -2241,6 +2250,18 @@ sub tables_hashref { 'index' => [], }, + 'tax_status' => { + 'columns' => [ + 'taxstatusnum', 'serial', '', '', '', '', + 'data_vendor', 'varchar', 'NULL', $char_d, '', '', + 'taxstatus', 'varchar', '', $char_d, '', '', + 'description', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'taxstatusnum', + 'unique' => [ [ 'data_vendor', 'taxstatus' ] ], + 'index' => [], + }, + 'cust_pay_pending' => { 'columns' => [ 'paypendingnum', 'serial', '', '', '', '', @@ -3312,6 +3333,7 @@ sub tables_hashref { 'data_vendor', 'varchar', 'NULL', $char_d, '', '', 'taxproduct', 'varchar', '', $char_d, '', '', 'description', 'varchar', '', 3*$char_d, '', '', + 'note', 'text', 'NULL', '', '', '', ], 'primary_key' => 'taxproductnum', 'unique' => [ [ 'data_vendor', 'taxproduct' ] ], @@ -5285,6 +5307,9 @@ sub tables_hashref { #new 'cdrbatchnum', 'int', 'NULL', '', '', '', + # FK to cust_bill_pkg_detail; having a value here absolutely means + # that the CDR appears on an invoice + 'detailnum', 'bigint', 'NULL', '', '', '', ], 'primary_key' => 'acctid', 'unique' => [], diff --git a/FS/FS/TaxEngine.pm b/FS/FS/TaxEngine.pm new file mode 100644 index 0000000..a146c54 --- /dev/null +++ b/FS/FS/TaxEngine.pm @@ -0,0 +1,239 @@ +package FS::TaxEngine; + +use strict; +use vars qw( $DEBUG ); +use FS::Conf; +use FS::Record qw(qsearch qsearchs); + +$DEBUG = 0; + +=head1 NAME + +FS::TaxEngine - Base class for tax calculation engines. + +=head1 USAGE + +1. At the start of creating an invoice, create an FS::TaxEngine object. +2. Each time a sale item is added to the invoice, call C<add_sale> on the + TaxEngine. + +- If the TaxEngine is "batch" style (Billsoft): +3. Set the "pending" flag on the invoice. +4. Insert the invoice and its line items. +5. After creating all invoices for the day, call + FS::TaxEngine::process_tax_batch. This will create the tax items for + all of the pending invoices, clear the "pending" flag, and call + C<collect> on each of the billed customers. + +- If not (the internal tax system, CCH): +3. After adding all sale items, call C<calculate_taxes> on the TaxEngine to + produce a list of tax line items. +4. Append the tax line items to the invoice. +5. Insert the invoice. + +=head1 CLASS METHODS + +=over 4 + +=item new 'cust_main' => CUST_MAIN, 'invoice_time' => TIME, OPTIONS... + +Creates an L<FS::TaxEngine> object. The subclass will be chosen by the +'enable_taxproducts' configuration setting. + +CUST_MAIN and TIME are required. OPTIONS can include "cancel" => 1 to +indicate that the package is being billed on cancellation. + +=cut + +sub new { + my $class = shift; + my %opt = @_; + if ($class eq 'FS::TaxEngine') { + my $conf = FS::Conf->new; + my $subclass = $conf->config('enable_taxproducts') || 'internal'; + $class .= "::$subclass"; + local $@; + eval "use $class"; + die "couldn't load $class: $@\n" if $@; + } + my $self = { items => [], taxes => {}, %opt }; + bless $self, $class; +} + +=item info + +Returns a hashref of metadata about this tax method, including: +- batch: whether this is a batch-style engine (requires different usage) +- override: whether this engine uses tax overrides +- manual_tax_location: whether this engine requires the user to select a "tax + location" separate from the address/city/state/zip fields +- rate_table: the table that stores the tax rates + (the 'taxline' method of that class will be used to calculate line-item + taxes) +- link_table: the table that links L<FS::cust_bill_pkg> records for taxes + to the C<rate_table> entry that generated them, and to the item they + represent tax on. + +=back + +=head1 METHODS + +=over 4 + +=item add_sale CUST_BILL_PKG + +Adds the CUST_BILL_PKG object as a taxable sale on this invoice. + +=item calculate_taxes CUST_BILL + +Calculates the taxes on the taxable sales and returns a list of +L<FS::cust_bill_pkg> objects to add to the invoice. There is a base +implementation of this, which calls the C<taxline> method to calculate +each individual tax. + +=cut + +sub calculate_taxes { + my $self = shift; + my $conf = FS::Conf->new; + + my $cust_bill = shift; + + my @tax_line_items; + # keys are tax names (as printed on invoices / itemdesc ) + # values are arrayrefs of taxlines + my %taxname; + + # keys are taxnums + # values are (cumulative) amounts + my %tax_amount; + + # keys are taxnums + # values are arrayrefs of cust_tax_exempt_pkg objects + my %tax_exemption; + + # For each distinct tax rate definition, calculate the tax and exemptions. + foreach my $taxnum ( keys %{ $self->{taxes} } ) { + + my $taxables = $self->{taxes}{$taxnum}; + my $tax_object = shift @$taxables; + # $tax_object is a cust_main_county or tax_rate + # (with billpkgnum, pkgnum, locationnum set) + # the rest of @{ $taxlisthash->{$tax} } is cust_bill_pkg component objects + # (setup, recurring, usage classes) + + my $taxline = $self->taxline('tax' => $tax_object, 'sales' => $taxables); + # taxline methods are now required to return real line items + # with their link records + die $taxline unless ref($taxline); + + push @{ $taxname{ $taxline->itemdesc } }, $taxline; + + } #foreach $taxnum + + my $link_table = $self->info->{link_table}; + # For each distinct tax name (the values set as $taxline->itemdesc), + # create a consolidated tax item with the total amount and all the links + # of all tax items that share that name. + foreach my $taxname ( keys %taxname ) { + my @tax_links; + my $tax_cust_bill_pkg = FS::cust_bill_pkg->new({ + 'invnum' => $cust_bill->invnum, + 'pkgnum' => 0, + 'recur' => 0, + 'sdate' => '', + 'edate' => '', + 'itemdesc' => $taxname, + $link_table => \@tax_links, + }); + + my $tax_total = 0; + warn "adding $taxname\n" if $DEBUG > 1; + + foreach my $taxitem ( @{ $taxname{$taxname} } ) { + # then we need to transfer the amount and the links from the + # line item to the new one we're creating. + $tax_total += $taxitem->setup; + foreach my $link ( @{ $taxitem->get($link_table) } ) { + $link->set('tax_cust_bill_pkg', $tax_cust_bill_pkg); + push @tax_links, $link; + } + } # foreach $taxitem + next unless $tax_total; + + # we should really neverround this up...I guess it's okay if taxline + # already returns amounts with 2 decimal places + $tax_total = sprintf('%.2f', $tax_total ); + $tax_cust_bill_pkg->set('setup', $tax_total); + + my $pkg_category = qsearchs( 'pkg_category', { 'categoryname' => $taxname, + 'disabled' => '', + }, + ); + + my @display = (); + if ( $pkg_category and + $conf->config('invoice_latexsummary') || + $conf->config('invoice_htmlsummary') + ) + { + my %hash = ( 'section' => $pkg_category->categoryname ); + push @display, new FS::cust_bill_pkg_display { type => 'S', %hash }; + } + $tax_cust_bill_pkg->set('display', \@display); + + push @tax_line_items, $tax_cust_bill_pkg; + } + + \@tax_line_items; +} + +=head1 CLASS METHODS + +=item cust_tax_locations LOCATION + +Given an L<FS::cust_location> object (or a hash of location fields), +returns a list of all tax jurisdiction locations that could possibly +match it. This is meant for interactive use: the location editing UI +displays the candidate locations to the user so they can choose the +best match. + +=cut + +sub cust_tax_locations { + (); +} # shouldn't even get called unless info->{manual_tax_location} is true + +=item add_taxproduct DESCRIPTION + +If the module allows manually adding tax products (categories of taxable +items/services), this method will be called to do it. (If not, the UI in +browse/part_pkg_taxproduct/* should prevent adding an unlisted tax product. +That is the default behavior, so by default this method simply fails.) + +DESCRIPTION is the contents of the taxproduct_description form input, which +will normally be filled in by browse/part_pkg_taxproduct/*. + +Must return the newly inserted part_pkg_taxproduct object on success, or +a string on failure. + +=cut + +sub add_taxproduct { + my $class = shift; + "$class does not allow manually adding taxproducts"; +} + +=item transfer_batch (batch-style only) + +Submits the pending transaction batch for processing, receives the +results, and appends the calculated taxes to all invoices that were +included in the batch. Then clears their pending flags, and queues +a job to run C<FS::cust_main::Billing::collect> on each affected +customer. + +=back + +=cut + +1; diff --git a/FS/FS/TaxEngine/avalara.pm b/FS/FS/TaxEngine/avalara.pm new file mode 100644 index 0000000..183555d --- /dev/null +++ b/FS/FS/TaxEngine/avalara.pm @@ -0,0 +1,302 @@ +package FS::TaxEngine::avalara; + +use strict; +use base 'FS::TaxEngine'; +use FS::Conf; +use FS::Record qw(qsearch qsearchs dbh); +use FS::cust_pkg; +use FS::cust_location; +use FS::cust_bill_pkg; +use FS::tax_rate; +use JSON; +use Geo::StreetAddress::US; + +our $DEBUG = 2; +our $json = JSON->new->pretty(1); + +our $conf; + +sub info { + { batch => 0, + override => 0 } +} + +FS::UID->install_callback( sub { + $conf = FS::Conf->new; +}); + +#sub cust_tax_locations { +#} +# Avalara address standardization would be nice but isn't necessary + +# XXX this is just here to avoid reworking the framework right now. By the +# 4.0 release, ALL tax calculations should be done after the invoice has +# been inserted into the database. + +# nothing to do here +sub add_sale {} + +sub build_request { + my ($self, %opt) = @_; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my $cust_bill = $self->{cust_bill}; + my $cust_main = $cust_bill->cust_main; + + # unfortunately we can't directly use the Business::Tax::Avalara get_tax() + # interface, because we have multiple customer addresses + my %address_seen; + + # assemble invoice line items + my @lines; + # conventions we are using here: + # P#### = part pkg# + # F#### = part_fee# + # L#### = cust_location# (address code) + # L0 = company address + foreach my $sale ( $cust_bill->cust_bill_pkg ) { + my $part = $sale->part_X; + my $item_code = ($part->isa('FS::part_pkg') ? 'P'.$part->pkgpart : + 'F'.$part->feepart + ); + my $addr_code = 'L'.$sale->tax_locationnum; + my $taxproductnum = $part->taxproductnum; + next unless $taxproductnum; + my $taxproduct = FS::part_pkg_taxproduct->by_key($taxproductnum); + my $itemdesc = $part->itemdesc || $part->pkg; + + $address_seen{$sale->tax_locationnum} = 1; + + my $line = { + 'LineNo' => $sale->billpkgnum, + 'DestinationCode' => $addr_code, + 'OriginCode' => 'L0', + 'ItemCode' => $item_code, + 'TaxCode' => $taxproduct->taxproduct, + 'Description' => $itemdesc, + 'Qty' => $sale->quantity, + 'Amount' => ($sale->setup + $sale->recur), + # also available: + # 'ExemptionNo', 'Discounted', 'TaxIncluded', 'Ref1', 'Ref2', 'Ref3', + # 'TaxOverride' + }; + push @lines, $line; + } + + # assemble address records for any cust_locations we used here, plus + # the company address + # XXX these should just be separate config opts + my $our_address = join(' ', + $conf->config('company_address', $cust_main->agentnum) + ); + my $company_address = Geo::StreetAddress::US->parse_address($our_address); + my $address1 = join(' ', grep $_, @{$company_address}{qw( + number prefix street type suffix + )}); + my $address2 = join(' ', grep $_, @{$company_address}{qw( + sec_unit_type sec_unit_num + )}); + my @addrs = ( + { + 'AddressCode' => 'L0', + 'Line1' => $address1, + 'Line2' => $address2, + 'City' => $company_address->{city}, + 'Region' => $company_address->{state}, + 'Country' => ($company_address->{country} + || $conf->config('countrydefault') + || 'US'), + 'PostalCode' => $company_address->{zip}, + 'Latitude' => ($conf->config('company_latitude') || ''), + 'Longitude' => ($conf->config('company_longitude') || ''), + } + ); + + foreach my $locationnum (keys %address_seen) { + my $cust_location = FS::cust_location->by_key($locationnum); + my $addr = { + 'AddressCode' => 'L'.$locationnum, + 'Line1' => $cust_location->address1, + 'Line2' => $cust_location->address2, + 'Line3' => '', + 'City' => $cust_location->city, + 'Region' => $cust_location->state, + 'Country' => $cust_location->country, + 'PostalCode' => $cust_location->zip, + 'Latitude' => $cust_location->latitude, + 'Longitude' => $cust_location->longitude, + #'TaxRegionId', probably not necessary + }; + push @addrs, $addr; + } + + my @avalara_conf = $conf->config('avalara-taxconfig'); + # 1. company code + # 2. user name (account number) + # 3. password (license) + # 4. test mode (1 to enable) + + # create the top level object + my $date = DateTime->from_epoch(epoch => $self->{invoice_time}); + return { + 'CustomerCode' => $cust_main->custnum, + 'DocDate' => $date->strftime('%Y-%m-%d'), + 'CompanyCode' => $avalara_conf[0], + 'Client' => "Freeside $FS::VERSION", + 'DocCode' => $cust_bill->invnum, + 'DetailLevel' => 'Tax', + 'Commit' => 'false', + 'DocType' => 'SalesInvoice', # ??? + 'CustomerUsageType' => $cust_main->taxstatus, + # ExemptionNo, Discount, TaxOverride, PurchaseOrderNo, + 'Addresses' => \@addrs, + 'Lines' => \@lines, + }; +} + +sub calculate_taxes { + $DB::single = 1; # XXX + my $self = shift; + + my $cust_bill = shift; + if (!$cust_bill->invnum) { + warn "FS::TaxEngine::avalara: can't calculate taxes on a non-inserted invoice"; + return; + } + $self->{cust_bill} = $cust_bill; + + my $invnum = $cust_bill->invnum; + if (FS::cust_bill_pkg->count("invnum = $invnum") == 0) { + # don't even bother making the request + return []; + } + + # instantiate gateway + eval "use Business::Tax::Avalara"; + die "error loading Business::Tax::Avalara:\n$@\n" if $@; + + my @avalara_conf = $conf->config('avalara-taxconfig'); + if (scalar @avalara_conf < 3) { + die "Your Avalara configuration is incomplete. +The 'avalara-taxconfig' parameter must have three rows: company code, +account number, and license key. +"; + } + + my $gateway = Business::Tax::Avalara->new( + customer_code => $self->{cust_main}->custnum, + company_code => $avalara_conf[0], + user_name => $avalara_conf[1], + password => $avalara_conf[2], + is_development => ($avalara_conf[3] ? 1 : 0), + ); + + # assemble the request hash + my $request = $self->build_request; + + warn "sending Avalara tax request\n" if $DEBUG; + my $request_json = $json->encode($request); + warn $request_json if $DEBUG > 1; + + my $response_json = $gateway->_make_request_json($request_json); + warn "received response\n" if $DEBUG; + warn $response_json if $DEBUG > 1; + my $response = $json->decode($response_json); + + my %tax_item_named; + + if ( $response->{ResultCode} ne 'Success' ) { + return "invoice#".$cust_bill->invnum.": ". + join("\n", @{ $response->{Messages} }); + } + warn "creating taxes for inv#$invnum\n" if $DEBUG > 1; + foreach my $TaxLine (@{ $response->{TaxLines} }) { + my $taxable_billpkgnum = $TaxLine->{LineNo}; + warn " item #$taxable_billpkgnum\n" if $DEBUG > 1; + foreach my $TaxDetail (@{ $TaxLine->{TaxDetails} }) { + # in this case the tax doesn't apply (just informational) + next unless $TaxDetail->{Taxable}; + + my $taxname = $TaxDetail->{TaxName}; + warn " $taxname\n" if $DEBUG > 1; + + # create a tax line item + my $tax_item = $tax_item_named{$taxname} ||= FS::cust_bill_pkg->new({ + invnum => $cust_bill->invnum, + pkgnum => 0, + setup => 0, + recur => 0, + itemdesc => $taxname, + cust_bill_pkg_tax_rate_location => [], + }); + # create a tax_rate record if there isn't one yet. + # we're not actually going to do anything with it, just tie related + # taxes together. + my $tax_rate = FS::tax_rate->new({ + data_vendor => 'avalara', + taxname => $taxname, + taxclassnum => '', + geocode => $TaxDetail->{JurisCode}, + location => $TaxDetail->{JurisName}, + tax => 0, + fee => 0, + }); + my $error = $tax_rate->find_or_insert; + return "error inserting tax_rate record for '$taxname': $error\n" + if $error; + + # create a tax_rate_location record + my $tax_rate_location = FS::tax_rate_location->new({ + data_vendor => 'avalara', + geocode => $TaxDetail->{JurisCode}, + state => $TaxDetail->{Region}, + city => ($TaxDetail->{JurisType} eq 'City' ? + $TaxDetail->{JurisName} : ''), + county => ($TaxDetail->{JurisType} eq 'County' ? + $TaxDetail->{JurisName} : ''), + # country? + }); + $error = $tax_rate_location->find_or_insert; + return "error inserting tax_rate_location record for ". + $TaxDetail->{JurisCode} .": $error\n" + if $error; + + # create a link record + my $tax_link = FS::cust_bill_pkg_tax_rate_location->new({ + cust_bill_pkg => $tax_item, + taxtype => 'FS::tax_rate', + taxnum => $tax_rate->taxnum, + taxratelocationnum => $tax_rate_location->taxratelocationnum, + amount => $TaxDetail->{Tax}, + taxable_billpkgnum => $taxable_billpkgnum, + }); + + # append the tax link and increment the amount + push @{ $tax_item->get('cust_bill_pkg_tax_rate_location') }, $tax_link; + $tax_item->set('setup', $tax_item->get('setup') + $TaxDetail->{Tax}); + } # foreach $TaxDetail + } # foreach $TaxLine + + return [ values(%tax_item_named) ]; +} + +sub add_taxproduct { + my $class = shift; + my $desc = shift; # tax code and description, separated by a space. + if ($desc =~ s/^(\w+) //) { + my $part_pkg_taxproduct = FS::part_pkg_taxproduct->new({ + 'data_vendor' => 'avalara', + 'taxproduct' => $1, + 'description' => $desc, + }); + # $obj_or_error + return $part_pkg_taxproduct->insert || $part_pkg_taxproduct; + } else { + return "illegal avalara tax code '$desc'"; + } +} + +1; diff --git a/FS/FS/TaxEngine/billsoft.pm b/FS/FS/TaxEngine/billsoft.pm new file mode 100644 index 0000000..d262aa4 --- /dev/null +++ b/FS/FS/TaxEngine/billsoft.pm @@ -0,0 +1,627 @@ +package FS::TaxEngine::billsoft; + +use strict; +use vars qw( $DEBUG $TIMEOUT %TAX_CLASSES ); +use base 'FS::TaxEngine'; +use FS::Conf; +use FS::Record qw(qsearch qsearchs dbh); +use FS::part_pkg; +use FS::cdr; +use FS::upload_target; +use Date::Format qw( time2str ); +use File::chdir; +use File::Copy qw(move); +use Parse::FixedLength; + +$DEBUG = 1; + +$TIMEOUT = 86400; # absolute time limit on waiting for a response file. + +FS::UID->install_callback(\&load_tax_classes); + +sub info { + { batch => 1, + override => 0, + manual_tax_location => 1, + }, +} + +sub add_sale { } #do nothing + +sub spooldir { + $FS::UID::cache_dir . "/Billsoft"; +} + +sub spoolname { + my $self = shift; + my $conf = FS::Conf->new;; + my $spooldir = $self->spooldir; + mkdir $spooldir, 0700 unless -d $spooldir; + my $basename = $conf->config('billsoft-company_code') . + time2str('%Y%m%d', time); # use the real clock time here + my $uniq = 'AA'; + while ( -e "$spooldir/$basename$uniq.CDF" ) { + $uniq++; + # these two letters must be unique within each day + } + "$basename$uniq.CDF"; +} + +my $format = + '%10s' . # Origination + '%1s' . # Origination Flag (NPA-NXX) + '%10s' . # Termination + '%1s' . # Termination Flag (NPA-NXX) + '%10s' . # Service Location + '%1s' . # Service Location Flag (Pcode) + '%1s' . # Customer Type ('B'usiness or 'R'esidential) + '%8s' . # Invoice Date + '+' . # Taxable Amount Sign + '%011d' . # Taxable Amount (5 decimal places) + '%6d' . # Lines + '%6d' . # Locations + '%12s' . # Transaction Type + Service Type + '%1s' . # Client Resale Flag ('S'ale or 'R'esale) + '%1s' . # Inc-Code ('I'n an incorporated city, or 'O'utside) + ' ' . # Fed/State/County/Local Exempt + '%1s' . # Primary Output Key, flag (our field) + '%019d' . # Primary Output Key, numeric (our field) + 'R' . # 'R'egulated (or 'U'nregulated) + '%011d' . # Call Duration (tenths of minutes) + 'C' . # Telecom Type ('C'alls, other things) + '%1s' . # Service Class ('L'ocal, Long 'D'istance) + ' NNC' . # non-lifeline, non-facilities based, + # non-franchise, CLEC + # (gross assumptions, may need a config option + "\r\n"; # at least that's what was in the samples + + +sub create_batch { + my ($self, %opt) = @_; + + $DB::single=1; # XXX + + my $spooldir = $self->spooldir; + my $spoolname = $self->spoolname; + my $fh = IO::File->new(); + $fh->open("$spooldir/$spoolname", '>>'); + $self->{fh} = $fh; + + # XXX limit based on freeside-daily custnum/agentnum options + # and maybe invoice date + my @invoices = qsearch('cust_bill', { pending => 'Y' }); + warn scalar(@invoices)." pending invoice(s) found.\n"; + foreach my $cust_bill (@invoices) { + + my $invnum = $cust_bill->invnum; + my $cust_main = $cust_bill->cust_main; + my $cust_type = $cust_main->company ? 'B' : 'R'; + my $invoice_date = time2str('%Y%m%d', $cust_bill->_date); + + # cache some things + my (%cust_pkg, %part_pkg, %cust_location, %classname); + # keys are transaction codes (the first part of the taxproduct string) + # and then locationnums; for per-location taxes + my %sales; + + foreach my $cust_bill_pkg ( $cust_bill->cust_bill_pkg ) { + my $cust_pkg = $cust_pkg{$cust_bill_pkg->pkgnum} + ||= $cust_bill_pkg->cust_pkg; + my $pkgpart = $cust_bill_pkg->pkgpart_override || $cust_pkg->pkgpart; + my $part_pkg = $part_pkg{$pkgpart} ||= FS::part_pkg->by_key($pkgpart); + my $resale_mode = ($part_pkg->option('wholesale',1) ? 'R' : 'S'); + my $locationnum = $cust_pkg->locationnum; + my $location = $cust_location{$locationnum} ||= $cust_pkg->cust_location; + my %taxproduct; # CDR rated_classnum => taxproduct + + my $usage_total = 0; + # go back to the original call details + my $detailnums = FS::Record->scalar_sql( + "SELECT array_to_string(array_agg(detailnum), ',') ". + "FROM cust_bill_pkg_detail WHERE billpkgnum = ". + $cust_bill_pkg->billpkgnum + ); + + # With summary details, even the number of CDRs returned from a single + # invoice detail could be scary large. Avoid running out of memory. + if (length $detailnums > 0) { + my $cdr_search = FS::Cursor->new({ + 'table' => 'cdr', + 'hashref' => { freesidestatus => 'done' }, + 'extra_sql' => "AND detailnum IN($detailnums)", + }); + + while (my $cdr = $cdr_search->fetch) { + my $classnum = $cdr->rated_classnum; + $classname{$classnum} ||= FS::usage_class->by_key($classnum)->classname + if $classnum; + $taxproduct{$classnum} ||= $part_pkg->taxproduct($classnum); + if (!$taxproduct{$classnum}) { + warn "part_pkg $pkgpart, class $classnum: ". + ($taxproduct{$classnum} ? + "using taxproduct ".$taxproduct{$classnum}->description."\n" : + "taxproduct not found\n") + if $DEBUG; + next; + } + + my $line = sprintf($format, + substr($cdr->src, 0, 6), 'N', + substr($cdr->dst, 0, 6), 'N', + $location->geocode, 'P', + $cust_type, + $invoice_date, + 100000 * $cdr->rated_price, # price (5 decimal places) + 0, # lines + 0, # locations + $taxproduct{$classnum}->taxproduct, + $resale_mode, + ($location->incorporated ? 'I' : 'O'), + 'C', # for Call + $cdr->acctid, + # Call duration (tenths of minutes) + $cdr->duration / 6, + # Service class indicator ('L'ocal, Long 'D'istance) + # stupid hack + (lc($classname{$classnum}) eq 'local' ? 'L' : 'D'), + ); + + print $fh $line; + + $usage_total += $cdr->rated_price; + + } # while $cdr = $cdr_search->fetch + } # if @$detailnums; otherwise there are no usage details for this line + + my $recur_tcode; + # now write lines for the non-CDR portion of the charges + foreach (qw(setup recur)) { + my $taxproduct = $part_pkg->taxproduct($_); + warn "part_pkg $pkgpart, class $_: ". + ($taxproduct ? + "using taxproduct ".$taxproduct->description."\n" : + "taxproduct not found\n") + if $DEBUG; + next unless $taxproduct; + + my ($tcode) = $taxproduct->taxproduct =~ /^(\d{6})/; + $sales{$tcode} ||= {}; + $sales{$tcode}{$location->locationnum} ||= 0; + $recur_tcode = $tcode if $_ eq 'recur'; + + my $price = $cust_bill_pkg->get($_); + $sales{$tcode}{$location->locationnum} += $price; + + $price -= $usage_total if $_ eq 'recur'; + + my $line = sprintf($format, + $location->geocode, 'P', # all 3 locations the same + $location->geocode, 'P', + $location->geocode, 'P', + $cust_type, + $invoice_date, + 100000 * $price, # price (5 decimal places) + 0, # lines + 0, # locations + $taxproduct->taxproduct, + $resale_mode, + ($location->incorporated ? 'I' : 'O'), + substr(uc($_), 0, 1), # 'S'etup or 'R'ecur + $cust_bill_pkg->billpkgnum, + 0, # call duration + 'D' # service class indicator + ); + + print $fh $line; + + } # foreach (setup, recur) + + # S-code 23: taxes based on number of lines (E911, mostly) + # voip_cdr and voip_inbound packages know how to report this. Not all + # T-codes are eligible for this; only report it if the /23 taxproduct + # exists. + # + # (note: the nomenclature of "service" and "transaction" codes is + # backward from the way most people would use the terms. you'd think + # that in "cellular activation", "cellular" would be the service and + # "activation" would be the transaction, but for Billsoft it's the + # reverse. I recommend calling them "S" and "T" codes internally just + # to avoid confusion.) + + my $lines_taxproduct = qsearchs('part_pkg_taxproduct', { + 'taxproduct' => sprintf('%06d%06d', $recur_tcode, 21) + }); + my $lines = $cust_bill_pkg->units; + + if ( $lines_taxproduct and $lines ) { + + my $line = sprintf($format, + $location->geocode, 'P', # all 3 locations the same + $location->geocode, 'P', + $location->geocode, 'P', + $cust_type, + $invoice_date, + 0, # price (5 decimal places) + $lines, # lines + 0, # locations + $lines_taxproduct->taxproduct, + $resale_mode, + ($location->incorporated ? 'I' : 'O'), + 'L', # 'L'ines + $cust_bill_pkg->billpkgnum, + 0, # call duration + 'D' # service class indicator + ); + + } + + } # foreach my $cust_bill_pkg + + # Implicit transactions + foreach my $tcode (keys %sales) { + + # S-code 23: number of locations (rare) + my $locations_taxproduct = + qsearchs('part_pkg_taxproduct', { + 'taxproduct' => sprintf('%06d%06d', $tcode, 23) + }); + + if ( $locations_taxproduct and keys %{ $sales{$tcode} } > 0 ) { + my $location = $cust_main->bill_location; + my $line = sprintf($format, + $location->geocode, 'P', # all 3 locations the same + $location->geocode, 'P', + $location->geocode, 'P', + $cust_type, + $invoice_date, + 0, # price (5 decimal places) + 0, # lines + keys(%{ $sales{$tcode} }),# locations + $locations_taxproduct->taxproduct, + 'S', + ($location->incorporated ? 'I' : 'O'), + 'O', # l'O'cations + sprintf('%07d%06d%06d', $invnum, $tcode, 0), + 0, # call duration + 'D' # service class indicator + ); + + print $fh $line; + } + + # S-code 43: per-invoice tax (apparently this is a thing) + my $invoice_taxproduct = + qsearchs('part_pkg_taxproduct', { + 'taxproduct' => sprintf('%06d%06d', $tcode, 43) + }); + if ( $invoice_taxproduct ) { + my $location = $cust_main->bill_location; + my $line = sprintf($format, + $location->geocode, 'P', # all 3 locations the same + $location->geocode, 'P', + $location->geocode, 'P', + $cust_type, + $invoice_date, + 0, # price (5 decimal places) + 0, # lines + 0, # locations + $invoice_taxproduct->taxproduct, + 'S', # resale mode + ($location->incorporated ? 'I' : 'O'), + 'I', # 'I'nvoice tax + sprintf('%07d%06d%06d', $invnum, $tcode, 0), + 0, # call duration + 'D' # service class indicator + ); + + print $fh $line; + } + } # foreach $tcode + } # foreach $cust_bill + + $fh->close; + return $spoolname; +} + +sub cust_tax_locations { + my $class = shift; + my $location = shift; + if (ref $location eq 'HASH') { + $location = FS::cust_location->new($location); + } + my $zip = $location->zip; + return () unless $location->country eq 'US'; + # currently the only one supported + if ( $zip =~ /^(\d{5})(-\d{4})?$/ ) { + $zip = $1; + } else { + die "bad zip code $zip"; + } + return qsearch({ + table => 'cust_tax_location', + hashref => { 'data_vendor' => 'billsoft' }, + extra_sql => " AND ziplo <= '$zip' and ziphi >= '$zip'", + order_by => ' ORDER BY default_location', + }); +} + +sub transfer_batch { + my ($self, %opt) = @_; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + eval "use Net::FTP;"; + # set up directories if they're not already + mkdir $self->spooldir unless -d $self->spooldir; + local $CWD = $self->spooldir; + foreach (qw(upload download)) { + mkdir $_ unless -d $_; + } + my $target = qsearchs('upload_target', { hostname => 'ftp.billsoft.com' }) + or die "No Billsoft upload target defined.\n"; + + # create the batch + my $upload = $self->create_batch(%opt); + + # upload it + my $ftp = $target->connect; + if (!ref $ftp) { # it's an error message + die "Error connecting to Billsoft FTP server:\n$ftp\n"; + } + my $fh = IO::File->new(); + warn "Processing: $upload\n"; + my $error = system("zip -j -o FTP.ZIP $upload"); + die "Failed to compress tax batch\n$!\n" if $error; + warn "Uploading file...\n"; + $ftp->put('FTP.ZIP'); + + my $download = $upload; + # naming convention for these is: same as the CDF contained in the + # zip file, but with an "R" inserted after the company ID prefix + $download =~ s/^(...)(\d{8}..).CDF/$1R$2.ZIP/; + warn "Waiting for output file ($download)...\n"; + my $starttime = time; + my $downloaded = 0; + while ( time - $starttime < $TIMEOUT ) { + my @ls = $ftp->ls($download); + if ( @ls ) { + if ($ftp->get($download, "download/$download")) { + warn "Downloaded '$download'.\n"; + $downloaded = 1; + last; + } else { + warn "Failed to download '$download': ".$ftp->message."\n"; + # We know the file exists, so continue trying to download it. + # Maybe the problem will get fixed. + } + } + sleep 30; + } + if (!$downloaded) { + warn "No output file received.\n"; + next BATCH; + } + warn "Decompressing...\n"; + system("unzip -o download/$download"); + foreach my $csf (glob "*.CSF") { + warn "Processing '$csf'...\n"; + $fh->open($csf, '<') or die "failed to open downloaded file $csf"; + $self->batch_import($fh); # dies on error + $fh->close; + unlink $csf unless $DEBUG; + } + unlink 'FTP.ZIP'; + move($upload, "upload/$upload"); + warn "Finished.\n"; + $dbh->commit if $oldAutoCommit; + return; +} + +sub batch_import { + $DB::single = 1; # XXX + # the hard part + my ($self, $fh) = @_; + $self->{'custnums'} = {}; + $self->{'cust_bill'} = {}; + + # gather up pending invoices + foreach my $cust_bill (qsearch('cust_bill', { pending => 'Y' })) { + $self->{'cust_bill'}{ $cust_bill->invnum } = $cust_bill; + } + + my $href; + my $parser = Parse::FixedLength->new( + [ + # key => 20, # for our purposes we split it up + flag => 1, + pkey => 19, + taxtype => 6, + authority => 1, + sign => 1, + amount => 11, + pcode => 9, + ], + ); + + # start parsing the input file + my $errors = 0; + my $row = 1; + foreach my $line (<$fh>) { + warn $line if $DEBUG > 1; + %$href = (); + $href = $parser->parse($line); + # convert some of these to integers + $href->{$_} += 0 foreach(qw(pkey taxtype amount pcode)); + next if $href->{amount} == 0; # then nobody cares + + my $flag = $href->{flag}; + my $pkey = $href->{pkey}; + my $cust_bill_pkg; # the line item that this tax applies to + # resolve the taxable object + if ( $flag eq 'C' ) { + # this line represents a CDR. + my $cdr = FS::cdr->by_key($pkey); + if (!$cdr) { + warn "[$row]\tCDR #$pkey not found.\n"; + } elsif (!$cdr->detailnum) { + warn "[$row]\tCDR #$pkey has not been billed.\n"; + $errors++; + next; + } else { + my $detail = FS::cust_bill_pkg_detail->by_key($cdr->detailnum); + $cust_bill_pkg = $detail->cust_bill_pkg; + } + } elsif ( $flag =~ /S|R|L/ ) { + # this line represents a setup or recur fee, or a number of lines. + $cust_bill_pkg = FS::cust_bill_pkg->by_key($pkey); + if (!$cust_bill_pkg) { + warn "[$row]\tLine item #$pkey not found.\n"; + } + } elsif ( $flag =~ /O|I/ ) { + warn "Per-invoice taxes are not implemented.\n"; + } else { + warn "[$row]\tFlag '$flag' not recognized.\n"; + } + if (!$cust_bill_pkg) { + $errors++; # this will trigger a rollback of the transaction + next; + } + # resolve the tax definition + # base name of the tax type (like "Sales Tax" or "Universal Lifeline + # Telephone Service Charge"). + my $tax_class = $TAX_CLASSES{ $href->{taxtype} + 0 }; + if (!$tax_class) { + warn "[$row]\tUnknown tax type $href->{taxtype}.\n"; + $errors++; + next; + } + my $itemdesc = uc($tax_class->description); + my $location = qsearchs('tax_rate_location', + { geocode => $href->{pcode} } + ); + if (!$location) { + warn "Unknown tax authority location ".$href->{pcode}."\n"; + $errors++; + next; + } + # jurisdiction name + my $prefix = ''; + if ( $href->{authority} == 0 ) { # national-level tax + # do nothing + } elsif ( $href->{authority} == 1 ) { + $prefix = $location->state; + } elsif ( $href->{authority} == 2 ) { + $prefix = $location->county . ' COUNTY'; + } elsif ( $href->{authority} == 3 ) { + $prefix = $location->city; + } elsif ( $href->{authority} == 4 ) { # unincorporated area ta + # do nothing + } + # Some itemdescs start with the jurisdiction name; otherwise, prepend + # it. + if ( $itemdesc !~ /^(city of )?$prefix\b/i ) { + $itemdesc = "$prefix $itemdesc"; + } + # Create or locate a tax_rate record, because we need one to foreign-key + # the cust_bill_pkg_tax_rate_location record. + my $tax_rate = $self->find_or_insert_tax_rate( + geocode => $href->{pcode}, + taxclassnum => $tax_class->taxclassnum, + taxname => $itemdesc, + ); + # Convert amount from 10^-5 dollars to dollars/cents + my $amount = sprintf('%.2f', $href->{amount} / 100000); + # and add it to the tax under this name + my $tax_item = $self->add_tax_item( + invnum => $cust_bill_pkg->invnum, + itemdesc => $itemdesc, + amount => $amount, + ); + # and link that tax line item to the taxed sale + my $subitem = FS::cust_bill_pkg_tax_rate_location->new({ + billpkgnum => $tax_item->billpkgnum, + taxnum => $tax_rate->taxnum, + taxtype => 'FS::tax_rate', + taxratelocationnum => $location->taxratelocationnum, + amount => $amount, + taxable_billpkgnum => $cust_bill_pkg->billpkgnum, + }); + my $error = $subitem->insert; + die "Error linking tax to taxable item: $error\n" if $error; + + $row++; + } #foreach $line + if ( $errors > 0 ) { + die "Encountered $errors error(s); rolling back tax import.\n"; + } + + # remove pending flag from invoices and schedule collect jobs + foreach my $cust_bill (values %{ $self->{'cust_bill'} }) { + my $invnum = $cust_bill->invnum; + $cust_bill->set('pending' => ''); + my $error = $cust_bill->replace; + die "Error updating invoice #$invnum: $error\n" + if $error; + $self->{'custnums'}->{ $cust_bill->custnum } = 1; + } + + foreach my $custnum ( keys %{ $self->{'custnums'} } ) { + my $queue = FS::queue->new({ 'job' => 'FS::cust_main::queued_collect' }); + my $error = $queue->insert('custnum' => $custnum); + die "Error scheduling collection for customer #$custnum: $error\n" + if $error; + } + + ''; +} + + +sub find_or_insert_tax_rate { + my ($self, %hash) = @_; + $hash{'tax'} = 0; + $hash{'data_vendor'} = 'billsoft'; + my $tax_rate = qsearchs('tax_rate', \%hash); + if (!$tax_rate) { + $tax_rate = FS::tax_rate->new(\%hash); + my $error = $tax_rate->insert; + die "Error inserting tax definition: $error\n" if $error; + } + return $tax_rate; +} + + +sub add_tax_item { + my ($self, %hash) = @_; + $hash{'pkgnum'} = 0; + my $amount = delete $hash{'amount'}; + + my $tax_item = qsearchs('cust_bill_pkg', \%hash); + if (!$tax_item) { + $tax_item = FS::cust_bill_pkg->new(\%hash); + $tax_item->set('setup', $amount); + my $error = $tax_item->insert; + die "Error inserting tax: $error\n" if $error; + } else { + $tax_item->set('setup', $tax_item->get('setup') + $amount); + my $error = $tax_item->replace; + die "Error incrementing tax: $error\n" if $error; + } + + my $cust_bill = $self->{'cust_bill'}->{$tax_item->invnum} + or die "Invoice #".$tax_item->{invnum}." is not pending.\n"; + $cust_bill->set('charged' => + sprintf('%.2f', $cust_bill->get('charged') + $amount)); + # don't replace the record yet, we'll do that at the end + + $tax_item; +} + +sub load_tax_classes { + %TAX_CLASSES = map { $_->taxclass => $_ } + qsearch('tax_class', { data_vendor => 'billsoft' }); +} + + +1; diff --git a/FS/FS/TaxEngine/cch.pm b/FS/FS/TaxEngine/cch.pm new file mode 100644 index 0000000..6bad69e --- /dev/null +++ b/FS/FS/TaxEngine/cch.pm @@ -0,0 +1,202 @@ +package FS::TaxEngine::cch; + +use strict; +use vars qw( $DEBUG ); +use base 'FS::TaxEngine'; +use FS::Record qw(dbh qsearch qsearchs); +use FS::Conf; + +=head1 SUMMARY + +FS::TaxEngine::cch CCH published tax tables. Uses multiple tables: +- tax_rate: definition of specific taxes, based on tax class and geocode. +- cust_tax_location: definition of geocodes, using zip+4 codes. +- tax_class: definition of tax classes. +- part_pkg_taxproduct: definition of taxable products (foreign key in + part_pkg.taxproductnum and the "usage_taxproductnum_*" part_pkg options). + The 'taxproduct' string in this table can implicitly include other + taxproducts. +- part_pkg_taxrate: links (geocode, taxproductnum) of a sold product to a + tax class. Many records here have partial-length geocodes which act + as wildcards. +- part_pkg_taxoverride: manual link from a part_pkg to a specific tax class. + +=cut + +$DEBUG = 0; + +my %part_pkg_cache; + +sub add_sale { + my ($self, $cust_bill_pkg, %options) = @_; + + my $part_item = $options{part_item} || $cust_bill_pkg->part_X; + my $location = $options{location} || $cust_bill_pkg->tax_location; + + push @{ $self->{items} }, $cust_bill_pkg; + + my $conf = FS::Conf->new; + + my @classes; + push @classes, $cust_bill_pkg->usage_classes if $cust_bill_pkg->usage; + # debatable + push @classes, 'setup' if ($cust_bill_pkg->setup && !$self->{cancel}); + push @classes, 'recur' if ($cust_bill_pkg->recur && !$self->{cancel}); + + my %taxes_for_class; + + my $exempt = $conf->exists('cust_class-tax_exempt') + ? ( $self->cust_class ? $self->cust_class->tax : '' ) + : $self->{cust_main}->tax; + # standardize this just to be sure + $exempt = ($exempt eq 'Y') ? 'Y' : ''; + + if ( !$exempt ) { + + foreach my $class (@classes) { + my $err_or_ref = $self->_gather_taxes( $part_item, $class, $location ); + return $err_or_ref unless ref($err_or_ref); + $taxes_for_class{$class} = $err_or_ref; + } + unless (exists $taxes_for_class{''}) { + my $err_or_ref = $self->_gather_taxes( $part_item, '', $location ); + return $err_or_ref unless ref($err_or_ref); + $taxes_for_class{''} = $err_or_ref; + } + + } + + my %tax_cust_bill_pkg = $cust_bill_pkg->disintegrate; # grrr + foreach my $key (keys %tax_cust_bill_pkg) { + # $key is "setup", "recur", or a usage class name. ('' is a usage class.) + # $tax_cust_bill_pkg{$key} is a cust_bill_pkg for that component of + # the line item. + # $taxes_for_class{$key} is an arrayref of tax_rate objects that + # apply to $key-class charges. + my @taxes = @{ $taxes_for_class{$key} || [] }; + my $tax_cust_bill_pkg = $tax_cust_bill_pkg{$key}; + + my %localtaxlisthash = (); + foreach my $tax ( @taxes ) { + + my $taxnum = $tax->taxnum; + $self->{taxes}{$taxnum} ||= [ $tax ]; + push @{ $self->{taxes}{$taxnum} }, $tax_cust_bill_pkg; + + $localtaxlisthash{ $taxnum } ||= [ $tax ]; + push @{ $localtaxlisthash{$taxnum} }, $tax_cust_bill_pkg; + + } + + warn "finding taxed taxes...\n" if $DEBUG > 2; + foreach my $taxnum ( keys %localtaxlisthash ) { + my $tax_object = shift @{ $localtaxlisthash{$taxnum} }; + + foreach my $tot ( $tax_object->tax_on_tax( $location ) ) { + my $totnum = $tot->taxnum; + + # I'm not sure why, but for some reason we only add ToT if that + # tax_rate already applies to a non-tax item on the same invoice. + next unless exists( $localtaxlisthash{ $totnum } ); + warn "adding #$totnum to taxed taxes\n" if $DEBUG > 2; + # calculate the tax amount that the tax_on_tax will apply to + my $taxline = + $self->taxline( 'tax' => $tax_object, + 'sales' => $localtaxlisthash{$taxnum} + ); + return $taxline unless ref $taxline; + # and append it to the list of taxable items + $self->{taxes}->{$totnum} ||= [ $tot ]; + push @{ $self->{taxes}->{$totnum} }, $taxline->setup; + + } # foreach $tot (tax-on-tax) + } # foreach $tax + } # foreach $key (i.e. usage class) +} + +sub _gather_taxes { # interface for this sucks + my $self = shift; + my $part_item = shift; + my $class = shift; + my $location = shift; + + my $geocode = $location->geocode('cch'); + + my @taxes = $part_item->tax_rates('cch', $geocode, $class); + + warn "Found taxes ". + join(',', map{ ref($_). " ". $_->get($_->primary_key) } @taxes). "\n" + if $DEBUG; + + \@taxes; + +} + +sub taxline { + # FS::tax_rate::taxline() ridiculously returns a description and amount + # instead of a real line item. Fix that here. + # + # XXX eventually move the code from tax_rate to here + # but that's not necessary yet + my ($self, %opt) = @_; + my $tax_object = $opt{tax}; + my $taxables = $opt{sales}; + my $hashref = $tax_object->taxline_cch($taxables); + return $hashref unless ref $hashref; # it's an error message + + my $tax_amount = sprintf('%.2f', $hashref->{amount}); + my $tax_item = FS::cust_bill_pkg->new({ + 'itemdesc' => $hashref->{name}, + 'pkgnum' => 0, + 'recur' => 0, + 'sdate' => '', + 'edate' => '', + 'setup' => $tax_amount, + }); + my $tax_link = FS::cust_bill_pkg_tax_rate_location->new({ + 'taxnum' => $tax_object->taxnum, + 'taxtype' => ref($tax_object), #redundant + 'amount' => $tax_amount, + 'locationtaxid' => $tax_object->location, + 'taxratelocationnum' => + $tax_object->tax_rate_location->taxratelocationnum, + 'tax_cust_bill_pkg' => $tax_item, + # XXX still need to get taxable_cust_bill_pkg in here + # but that requires messing around in the taxline code + }); + $tax_item->set('cust_bill_pkg_tax_rate_location', [ $tax_link ]); + + return $tax_item; +} + +sub cust_tax_locations { + my $class = shift; + my $location = shift; + $location = FS::cust_location->new($location) if ref($location) eq 'HASH'; + + # limit to CCH zip code prefix records, not zip+4 range records + my $hashref = { 'data_vendor' => 'cch-zip' }; + if ( $location->country eq 'CA' ) { + # weird CCH convention: treat Canadian provinces as localities, using + # their one-letter postal codes. + $hashref->{zip} = substr($location->zip, 0, 1); + } elsif ( $location->country eq 'US' ) { + $hashref->{zip} = substr($location->zip, 0, 5); + } else { + return (); + } + + return qsearch('cust_tax_location', $hashref); +} + +sub info { + +{ + batch => 0, + override => 1, + manual_tax_location => 1, + rate_table => 'tax_rate', + link_table => 'cust_bill_pkg_tax_rate_location', + } +} + +1; diff --git a/FS/FS/TaxEngine/internal.pm b/FS/FS/TaxEngine/internal.pm new file mode 100644 index 0000000..60f7aad --- /dev/null +++ b/FS/FS/TaxEngine/internal.pm @@ -0,0 +1,318 @@ +package FS::TaxEngine::internal; + +use strict; +use base 'FS::TaxEngine'; +use FS::Record qw(dbh qsearch qsearchs); + +=head1 SUMMARY + +FS::TaxEngine::internal: the classic Freeside "internal tax engine". +Uses tax rates and localities defined in L<FS::cust_main_county>. + +=cut + +my %part_pkg_cache; + +sub add_sale { + my ($self, $cust_bill_pkg) = @_; + my $cust_pkg = $cust_bill_pkg->cust_pkg; + my $pkgpart = $cust_bill_pkg->pkgpart_override || $cust_pkg->pkgpart; + my $part_pkg = $part_pkg_cache{$pkgpart} ||= FS::part_pkg->by_key($pkgpart) + or die "pkgpart $pkgpart not found"; + push @{ $self->{items} }, $cust_bill_pkg; + + my $location = $cust_pkg->tax_location; # cacheable? + + my @loc_keys = qw( district city county state country ); + my %taxhash = map { $_ => $location->get($_) } @loc_keys; + + $taxhash{'taxclass'} = $part_pkg->taxclass; + + my @taxes = (); # entries are cust_main_county objects + my %taxhash_elim = %taxhash; + my @elim = qw( district city county state ); + do { + + #first try a match with taxclass + @taxes = qsearch( 'cust_main_county', \%taxhash_elim ); + + if ( !scalar(@taxes) && $taxhash_elim{'taxclass'} ) { + #then try a match without taxclass + my %no_taxclass = %taxhash_elim; + $no_taxclass{ 'taxclass' } = ''; + @taxes = qsearch( 'cust_main_county', \%no_taxclass ); + } + + $taxhash_elim{ shift(@elim) } = ''; + } while ( !scalar(@taxes) && scalar(@elim) ); + + foreach (@taxes) { + my $taxnum = $_->taxnum; + $self->{taxes}->{$taxnum} ||= [ $_ ]; + push @{ $self->{taxes}->{$taxnum} }, $cust_bill_pkg; + } +} + +sub taxline { + my ($self, %opt) = @_; + my $tax_object = $opt{tax}; + my $taxables = $opt{sales}; + my $taxnum = $tax_object->taxnum; + my $exemptions = $self->{exemptions}->{$taxnum} ||= []; + + my $name = $tax_object->taxname || 'Tax'; + my $taxable_cents = 0; + my $tax_cents = 0; + + my $cust_main = $self->{cust_main}; + my $custnum = $cust_main->custnum; + my $invoice_time = $self->{invoice_time}; + + # set a flag if the customer is tax-exempt + my $exempt_cust; + my $conf = FS::Conf->new; + if ( $conf->exists('cust_class-tax_exempt') ) { + my $cust_class = $cust_main->cust_class; + $exempt_cust = $cust_class->tax if $cust_class; + } else { + $exempt_cust = $cust_main->tax; + } + # set a flag if the customer is exempt from this tax here + my $exempt_cust_taxname = $cust_main->tax_exemption($tax_object->taxname) + if $tax_object->taxname; + + # Gather any exemptions that are already attached to these cust_bill_pkgs + # so that we can deduct them from the customer's monthly limit. + my @existing_exemptions = @{ $exemptions }; + push @existing_exemptions, @{ $_->cust_tax_exempt_pkg } + foreach @$taxables; + + my $tax_item = FS::cust_bill_pkg->new({ + 'pkgnum' => 0, + 'recur' => 0, + 'sdate' => '', + 'edate' => '', + 'itemdesc' => $name, + }); + my @tax_location; + + foreach my $cust_bill_pkg (@$taxables) { + + my $cust_pkg = $cust_bill_pkg->cust_pkg; + my $part_pkg = $cust_bill_pkg->part_pkg; + my @new_exemptions; + my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur + or next; # don't create zero-amount exemptions + + # XXX the following procedure should probably be in cust_bill_pkg + + if ( $exempt_cust ) { + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => $taxable_charged, + exempt_cust => 'Y', + }); + $taxable_charged = 0; + + } elsif ( $exempt_cust_taxname ) { + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => $taxable_charged, + exempt_cust_taxname => 'Y', + }); + $taxable_charged = 0; + + } + + if ( ($part_pkg->setuptax eq 'Y' or $tax_object->setuptax eq 'Y') + and $cust_bill_pkg->setup > 0 and $taxable_charged > 0 ) { + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => $cust_bill_pkg->setup, + exempt_setup => 'Y' + }); + $taxable_charged -= $cust_bill_pkg->setup; + + } + if ( ($part_pkg->recurtax eq 'Y' or $tax_object->recurtax eq 'Y') + and $cust_bill_pkg->recur > 0 and $taxable_charged > 0 ) { + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => $cust_bill_pkg->recur, + exempt_recur => 'Y' + }); + $taxable_charged -= $cust_bill_pkg->recur; + + } + + if ( $tax_object->exempt_amount && $tax_object->exempt_amount > 0 + and $taxable_charged > 0 ) { + # If the billing period extends across multiple calendar months, + # there may be several months of exemption available. + my $sdate = $cust_bill_pkg->sdate || $invoice_time; + my $start_month = (localtime($sdate))[4] + 1; + my $start_year = (localtime($sdate))[5] + 1900; + my $edate = $cust_bill_pkg->edate || $invoice_time; + my $end_month = (localtime($edate))[4] + 1; + my $end_year = (localtime($edate))[5] + 1900; + + # If the partial last month + partial first month <= one month, + # don't use the exemption in the last month + # (unless the last month is also the first month, e.g. one-time + # charges) + if ( (localtime($sdate))[3] >= (localtime($edate))[3] + and ($start_month != $end_month or $start_year != $end_year) + ) { + $end_month--; + if ( $end_month == 0 ) { + $end_year--; + $end_month = 12; + } + } + + # number of months of exemption available + my $freq = ($end_month - $start_month) + + ($end_year - $start_year) * 12 + + 1; + + # divide equally among all of them + my $permonth = sprintf('%.2f', $taxable_charged / $freq); + + #call the whole thing off if this customer has any old + #exemption records... + my @cust_tax_exempt = + qsearch( 'cust_tax_exempt' => { custnum=> $custnum } ); + if ( @cust_tax_exempt ) { + return + 'this customer still has old-style tax exemption records; '. + 'run bin/fs-migrate-cust_tax_exempt?'; + } + + my ($mon, $year) = ($start_month, $start_year); + while ($taxable_charged > 0.005 and + ($year < $end_year or + ($year == $end_year and $mon <= $end_month) + ) + ) { + + # find the sum of the exemption used by this customer, for this tax, + # in this month + my $sql = " + SELECT SUM(amount) + FROM cust_tax_exempt_pkg + LEFT JOIN cust_bill_pkg USING ( billpkgnum ) + LEFT JOIN cust_bill USING ( invnum ) + WHERE custnum = ? + AND taxnum = ? + AND year = ? + AND month = ? + AND exempt_monthly = 'Y' + "; + my $sth = dbh->prepare($sql) or + return "fatal: can't lookup existing exemption: ". dbh->errstr; + $sth->execute( + $custnum, + $tax_object->taxnum, + $year, + $mon, + ) or + return "fatal: can't lookup existing exemption: ". dbh->errstr; + my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0; + + # add any exemption we're already using for another line item + foreach ( grep { $_->taxnum == $tax_object->taxnum && + $_->exempt_monthly eq 'Y' && + $_->month == $mon && + $_->year == $year + } @existing_exemptions + ) + { + $existing_exemption += $_->amount; + } + + my $remaining_exemption = + $tax_object->exempt_amount - $existing_exemption; + if ( $remaining_exemption > 0 ) { + my $addl = $remaining_exemption > $permonth + ? $permonth + : $remaining_exemption; + $addl = $taxable_charged if $addl > $taxable_charged; + + push @new_exemptions, FS::cust_tax_exempt_pkg->new({ + amount => sprintf('%.2f', $addl), + exempt_monthly => 'Y', + year => $year, + month => $mon, + }); + $taxable_charged -= $addl; + } + # if they're using multiple months of exemption for a multi-month + # package, then record the exemptions in separate months + $mon++; + if ( $mon > 12 ) { + $mon -= 12; + $year++; + } + + } + } # if exempt_amount + + $_->taxnum($tax_object->taxnum) foreach @new_exemptions; + + # attach them to the line item + push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions; + push @existing_exemptions, @new_exemptions; + + $taxable_charged = sprintf( "%.2f", $taxable_charged); + next if $taxable_charged == 0; + + my $this_tax_cents = int($taxable_charged * $tax_object->tax); + my $location = FS::cust_bill_pkg_tax_location->new({ + 'taxnum' => $tax_object->taxnum, + 'taxtype' => ref($tax_object), + 'cents' => $this_tax_cents, + 'pkgnum' => $cust_bill_pkg->pkgnum, + 'locationnum' => $cust_bill_pkg->cust_pkg->tax_locationnum, + 'taxable_cust_bill_pkg' => $cust_bill_pkg, + 'tax_cust_bill_pkg' => $tax_item, + }); + push @tax_location, $location; + + $taxable_cents += $taxable_charged; + $tax_cents += $this_tax_cents; + } #foreach $cust_bill_pkg + + # now round and distribute + my $extra_cents = sprintf('%.2f', $taxable_cents * $tax_object->tax / 100) + * 100 - $tax_cents; + # make sure we have an integer + $extra_cents = sprintf('%.0f', $extra_cents); + if ( $extra_cents < 0 ) { + die "nonsense extra_cents value $extra_cents"; + } + $tax_cents += $extra_cents; + my $i = 0; + foreach (@tax_location) { # can never require more than a single pass, yes? + my $cents = $_->get('cents'); + if ( $extra_cents > 0 ) { + $cents++; + $extra_cents--; + } + $_->set('amount', sprintf('%.2f', $cents/100)); + } + $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100)); + $tax_item->set('cust_bill_pkg_tax_location', \@tax_location); + + return $tax_item; +} + +sub info { + +{ + batch => 0, + override => 0, + rate_table => 'cust_main_county', + link_table => 'cust_bill_pkg_tax_location', + } +} + +1; diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm index 291cea7..bab7e30 100644 --- a/FS/FS/UI/Web.pm +++ b/FS/FS/UI/Web.pm @@ -694,14 +694,9 @@ sub start_job { #too slow to insert all the cgi params as individual args..,? #my $error = $queue->insert('_JOB', $cgi->Vars); - #warn 'froze string of size '. length(nfreeze(\%param)). " for job args\n" - # if $DEBUG; - # - # XXX FS::queue::insert knows how to do this. - # not changing it here because that requires changing it everywhere else, - # too, but we should eventually fix it + #rely on FS::queue smartness to freeze/encode the param hash - my $error = $job->insert( '_JOB', encode_base64(nfreeze(\%param)) ); + my $error = $job->insert( '_JOB', \%param ); if ( $error ) { diff --git a/FS/FS/Upgrade.pm b/FS/FS/Upgrade.pm index cb16ead..d76379f 100644 --- a/FS/FS/Upgrade.pm +++ b/FS/FS/Upgrade.pm @@ -125,6 +125,14 @@ If you need to continue using the old Form 477 report, turn on the $conf->set($newname, 'location'); } + # boolean enable_taxproducts is now enable_taxproducts = 'cch' + if ( $conf->exists('enable_taxproducts') and + $conf->config('enable_taxproducts') eq '' ) { + + $conf->set('enable_taxproducts', 'cch'); + + } + } sub upgrade_overlimit_groups { @@ -374,6 +382,9 @@ sub upgrade_data { #populate state FIPS codes if not already done 'state' => [], + + #populate tax statuses + 'tax_status' => [], ; \%hash; diff --git a/FS/FS/bill_batch.pm b/FS/FS/bill_batch.pm index d4f2aec..da6420c 100644 --- a/FS/FS/bill_batch.pm +++ b/FS/FS/bill_batch.pm @@ -131,13 +131,11 @@ Returns the agent (see L<FS::agent>) for this invoice batch. =cut -use Storable 'thaw'; use Data::Dumper; -use MIME::Base64; sub process_print_pdf { my $job = shift; - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; die "no batchnum specified!\n" if ! exists($param->{batchnum}); my $batch = FS::bill_batch->by_key($param->{batchnum}); diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm index 4c02b06..306dbd0 100644 --- a/FS/FS/cdr.pm +++ b/FS/FS/cdr.pm @@ -161,6 +161,8 @@ following fields are currently supported: =item cdrbatch +=item detailnum - Link to invoice detail (L<FS::cust_bill_pkg_detail>) + =back =head1 METHODS @@ -226,6 +228,7 @@ sub table_info { 'freesiderewritestatus' => 'Freeside rewrite status', 'cdrbatch' => 'Legacy batch', 'cdrbatchnum' => 'Batch', + 'detailnum' => 'Freeside invoice detail line', }, }; @@ -337,8 +340,12 @@ sub check { #check the foreign keys even? #do we want to outright *reject* the CDR? - my $error = - $self->ut_numbern('acctid'); + my $error = $self->ut_numbern('acctid'); + return $error if $error; + + if ( $self->freesidestatus ne 'done' ) { + $self->set('detailnum', ''); # can't have this on an unbilled call + } #add a config option to turn these back on if someone needs 'em # @@ -351,8 +358,6 @@ sub check { # # Telstra =1, Optus = 2, RSL COM = 3 # || $self->ut_foreign_keyn('carrierid', 'cdr_carrier', 'carrierid' ) - return $error if $error; - $self->SUPER::check; } diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index 1b9b347..e7c799f 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -125,6 +125,8 @@ Specific use cases =item promised_date - customer promised payment date, for collection +=item pending - invoice is still being generated, empty or 'Y' + =back =head1 METHODS @@ -334,6 +336,7 @@ sub replace_check { #return "Can't change _date!" unless $old->_date eq $new->_date; return "Can't change _date" unless $old->_date == $new->_date; return "Can't change charged" unless $old->charged == $new->charged + || $old->pending eq 'Y' || $old->charged == 0 || $new->{'Hash'}{'cc_surcharge_replace_hack'}; @@ -388,6 +391,7 @@ sub check { || $self->ut_enum('closed', [ '', 'Y' ]) || $self->ut_foreign_keyn('statementnum', 'cust_statement', 'statementnum' ) || $self->ut_numbern('agent_invid') #varchar? + || $self->ut_flag('pending') ; return $error if $error; @@ -3173,14 +3177,12 @@ sub process_respool { process_re_X('spool', @_); } -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process_re_X { my( $method, $job ) = ( shift, shift ); warn "$me process_re_X $method for job $job\n" if $DEBUG; - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; re_X( diff --git a/FS/FS/cust_bill_event.pm b/FS/FS/cust_bill_event.pm index c91283b..adaa13e 100644 --- a/FS/FS/cust_bill_event.pm +++ b/FS/FS/cust_bill_event.pm @@ -304,13 +304,11 @@ sub process_refax { process_re_X('fax', @_); } -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process_re_X { my( $method, $job ) = ( shift, shift ); - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; re_X( diff --git a/FS/FS/cust_bill_pkg_detail.pm b/FS/FS/cust_bill_pkg_detail.pm index d0cbdbe..dd118c1 100644 --- a/FS/FS/cust_bill_pkg_detail.pm +++ b/FS/FS/cust_bill_pkg_detail.pm @@ -86,15 +86,52 @@ sub table { 'cust_bill_pkg_detail'; } Adds this record to the database. If there is an error, returns the error, otherwise returns false. +=cut + +sub insert { + my $self = shift; + my $error = $self->SUPER::insert(@_); + return $error if $error; + + # link CDRs + my $acctids = $self->get('acctid') or return ''; + $acctids = [ $acctids ] unless ref $acctids; + foreach my $acctid ( @$acctids ) { + my $cdr = FS::cdr->by_key($acctid); + $cdr->set('detailnum', $self->detailnum); + $error = $cdr->replace; + # this should never happen + return "error linking CDR #$acctid: $error" if $error; + } + ''; +} + =item delete Delete this record from the database. +=cut + +sub delete { + my $self = shift; + my $error = $self->SUPER::delete; + return $error if $error; + foreach my $cdr (qsearch('cdr', { detailnum => $self->detailnum })) { + $cdr->set('detailnum', ''); + $error = $cdr->replace; + return "error unlinking CDR #" . $cdr->acctid . ": $error" if $error; + } +} + =item replace OLD_RECORD Replaces the OLD_RECORD with this one in the database. If there is an error, returns the error, otherwise returns false. +=cut + +# the replace method can be inherited from FS::Record (doesn't touch CDRs) + =item check Checks all fields to make sure this is a valid line item detail. If there is diff --git a/FS/FS/cust_class.pm b/FS/FS/cust_class.pm index 2536058..fdc92ba 100644 --- a/FS/FS/cust_class.pm +++ b/FS/FS/cust_class.pm @@ -102,8 +102,6 @@ sub check { =item cust_category -=item category - Returns the cust_category record associated with this class, or false if there is none. diff --git a/FS/FS/cust_event.pm b/FS/FS/cust_event.pm index b29ab71..b5436d0 100644 --- a/FS/FS/cust_event.pm +++ b/FS/FS/cust_event.pm @@ -434,13 +434,9 @@ sub process_refax { process_re_X('fax', @_); } -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process_re_X { - my( $method, $job ) = ( shift, shift ); - - my $param = thaw(decode_base64(shift)); + my( $method, $job, $param ) = @_; warn Dumper($param) if $DEBUG; re_X( diff --git a/FS/FS/cust_location.pm b/FS/FS/cust_location.pm index 66fd7a0..e5d2e72 100644 --- a/FS/FS/cust_location.pm +++ b/FS/FS/cust_location.pm @@ -94,6 +94,11 @@ Geocode Tax district code (optional) +=item incorporated + +Incorporated city flag: set to 'Y' if the address is in the legal borders +of an incorporated city. + =item disabled Disabled flag; set to 'Y' to disable the location. @@ -340,6 +345,7 @@ sub check { || $self->ut_alphan('geocode') || $self->ut_alphan('district') || $self->ut_numbern('censusyear') + || $self->ut_flag('incorporated') ; return $error if $error; if ( $self->censustract ne '' ) { diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index f3447cc..e5982a9 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -28,8 +28,6 @@ use vars qw( $DEBUG $me $conf use Carp; use Scalar::Util qw( blessed ); use Time::Local qw(timelocal); -use Storable qw(thaw); -use MIME::Base64; use Data::Dumper; use Tie::IxHash; use Digest::MD5 qw(md5_base64); @@ -61,6 +59,7 @@ use FS::part_referral; use FS::cust_main_county; use FS::cust_location; use FS::cust_class; +use FS::tax_status; use FS::cust_main_exemption; use FS::cust_tax_adjustment; use FS::cust_tax_location; @@ -1746,6 +1745,7 @@ sub check { || $self->ut_foreign_keyn('ship_locationnum', 'cust_location','locationnum') || $self->ut_foreign_keyn('classnum', 'cust_class', 'classnum') || $self->ut_foreign_keyn('salesnum', 'sales', 'salesnum') + || $self->ut_foreign_keyn('taxstatusnum', 'tax_status', 'taxstatusnum') || $self->ut_textn('custbatch') || $self->ut_name('last') || $self->ut_name('first') @@ -2445,6 +2445,36 @@ sub classname { : ''; } +=item tax_status + +Returns the external tax status, as an FS::tax_status object, or the empty +string if there is no tax status. + +=cut + +sub tax_status { + my $self = shift; + if ( $self->taxstatusnum ) { + qsearchs('tax_status', { 'taxstatusnum' => $self->taxstatusnum } ); + } else { + return ''; + } +} + +=item taxstatus + +Returns the tax status code if there is one. + +=cut + +sub taxstatus { + my $self = shift; + my $tax_status = $self->tax_status; + $tax_status + ? $tax_status->taxstatus + : ''; +} + =item BILLING METHODS Documentation on billing methods has been moved to @@ -4998,9 +5028,24 @@ sub queued_bill { $cust_main->bill_and_collect( %args ); } +=item queued_collect 'custnum' => CUSTNUM [ , OPTION => VALUE ... ] + +Like queued_bill, but instead of C<bill_and_collect>, just runs the +C<collect> part. This is used in batch tax calculation, where invoice +generation and collection events have to be completely separated. + +=cut + +sub queued_collect { + my (%args) = @_; + my $cust_main = FS::cust_main->by_key($args{'custnum'}); + + $cust_main->collect(%args); +} + sub process_bill_and_collect { my $job = shift; - my $param = thaw(decode_base64(shift)); + my $param = shift; my $cust_main = qsearchs( 'cust_main', { custnum => $param->{'custnum'} } ) or die "custnum '$param->{custnum}' not found!\n"; $param->{'job'} = $job; diff --git a/FS/FS/cust_main/Billing.pm b/FS/FS/cust_main/Billing.pm index 29f7e8e..f65d495 100644 --- a/FS/FS/cust_main/Billing.pm +++ b/FS/FS/cust_main/Billing.pm @@ -23,6 +23,7 @@ use FS::part_event_condition; use FS::pkg_category; use FS::cust_event_fee; use FS::Log; +use FS::TaxEngine; # 1 is mostly method/subroutine entry and options # 2 traces progress of some operations @@ -171,9 +172,22 @@ sub bill_and_collect { else { warn $error; } } - unless ( $conf->exists('cancelled_cust-noevents') - && ! $self->num_ncancelled_pkgs - ) { + # In a batch tax environment, do not run collection if any pending + # invoices were created. Collection will run after the next tax batch. + my $tax = FS::TaxEngine->new; + if ( $tax->info->{batch} and + qsearch('cust_bill', { custnum => $self->custnum, pending => 'Y' }) + ) + { + warn "skipped collection for custnum ".$self->custnum. + " due to pending invoices\n" if $DEBUG; + } elsif ( $conf->exists('cancelled_cust-noevents') + && ! $self->num_ncancelled_pkgs ) + { + warn "skipped collection for custnum ".$self->custnum. + " because they have no active packages\n" if $DEBUG; + } else { + # run collection normally $job->update_statustext('70,running collection events') if $job; $log->debug('running collection events', %logopt); $error = $self->collect( %options ); @@ -450,11 +464,19 @@ sub bill { my %total_setup = map { my $z = 0; $_ => \$z; } @passes; my %total_recur = map { my $z = 0; $_ => \$z; } @passes; - my %taxlisthash = map { $_ => {} } @passes; - my @precommit_hooks = (); $options{'pkg_list'} ||= [ $self->ncancelled_pkgs ]; #param checks? + + my %tax_engines; + my $tax_is_batch = ''; + foreach (@passes) { + $tax_engines{$_} = FS::TaxEngine->new(cust_main => $self, + invoice_time => $invoice_time, + cancel => $options{cancel} + ); + $tax_is_batch ||= $tax_engines{$_}->info->{batch}; + } foreach my $cust_pkg ( @{ $options{'pkg_list'} } ) { @@ -515,7 +537,7 @@ sub bill { 'line_items' => $cust_bill_pkg{$pass}, 'setup' => $total_setup{$pass}, 'recur' => $total_recur{$pass}, - 'tax_matrix' => $taxlisthash{$pass}, + 'tax_engine' => $tax_engines{$pass}, 'time' => $time, 'real_pkgpart' => $real_pkgpart, 'options' => \%options, @@ -640,13 +662,9 @@ sub bill { my $part_fee = $fee_item->part_fee; my $fee_location = $self->ship_location; # I think? + + my $error = $tax_engines{''}->add_sale($fee_item); - my $error = $self->_handle_taxes( - $taxlisthash{$pass}, - $fee_item, - location => $fee_location - # probably not right to pass cancel => 1 for fees - ); return $error if $error; } @@ -683,7 +701,7 @@ sub bill { 'line_items' => \@cust_bill_pkg, 'setup' => $total_setup{$pass}, 'recur' => $total_recur{$pass}, - 'tax_matrix' => $taxlisthash{$pass}, + 'tax_engine' => $tax_engines{$pass}, 'time' => $time, 'real_pkgpart' => $real_pkgpart, 'options' => \%postal_options, @@ -701,21 +719,8 @@ sub bill { } - my $listref_or_error = - $self->calculate_taxes( \@cust_bill_pkg, $taxlisthash{$pass}, $invoice_time); - - unless ( ref( $listref_or_error ) ) { - $dbh->rollback if $oldAutoCommit && !$options{no_commit}; - return $listref_or_error; - } - - foreach my $taxline ( @$listref_or_error ) { - ${ $total_setup{$pass} } = - sprintf('%.2f', ${ $total_setup{$pass} } + $taxline->setup ); - push @cust_bill_pkg, $taxline; - } - #add tax adjustments + #XXX does this work with batch tax engines? warn "adding tax adjustments...\n" if $DEBUG > 2; foreach my $cust_tax_adjustment ( qsearch('cust_tax_adjustment', { 'custnum' => $self->custnum, @@ -767,12 +772,63 @@ sub bill { 'previous_balance' => $previous_balance, 'invoice_terms' => $options{'invoice_terms'}, 'cust_bill_pkg' => \@cust_bill_pkg, + 'pending' => 'Y', # clear this after doing taxes } ); - $error = $cust_bill->insert unless $options{no_commit}; - if ( $error ) { - $dbh->rollback if $oldAutoCommit && !$options{no_commit}; - return "can't create invoice for customer #". $self->custnum. ": $error"; + + if (!$options{no_commit}) { + # probably we ought to insert it as pending, and then rollback + # without ever un-pending it + $error = $cust_bill->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit && !$options{no_commit}; + return "can't create invoice for customer #". $self->custnum. ": $error"; + } + } + + # calculate and append taxes + if ( ! $tax_is_batch) { + my $arrayref_or_error = $tax_engines{$pass}->calculate_taxes($cust_bill); + + unless ( ref( $arrayref_or_error ) ) { + $dbh->rollback if $oldAutoCommit && !$options{no_commit}; + return $arrayref_or_error; + } + + # or should this be in TaxEngine? + my $total_tax = 0; + foreach my $taxline ( @$arrayref_or_error ) { + $total_tax += $taxline->setup; + $taxline->set('invnum' => $cust_bill->invnum); # just to be sure + push @cust_bill_pkg, $taxline; # for return_bill + + if (!$options{no_commit}) { + my $error = $taxline->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + + } + + # add tax to the invoice amount and finalize it + ${ $total_setup{$pass} } = sprintf('%.2f', ${ $total_setup{$pass} } + $total_tax); + $charged = sprintf('%.2f', $charged + $total_tax); + $cust_bill->set('charged', $charged); + $cust_bill->set('pending', ''); + + if (!$options{no_commit}) { + my $error = $cust_bill->replace; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + + } # if !$tax_is_batch + # if it IS batch, then we'll do all this in process_tax_batch + push @{$options{return_bill}}, $cust_bill if $options{return_bill}; } #foreach my $pass ( keys %cust_bill_pkg ) @@ -845,204 +901,6 @@ sub _omit_zero_value_bundles { } -=item calculate_taxes LINEITEMREF TAXHASHREF INVOICE_TIME - -Generates tax line items (see L<FS::cust_bill_pkg>) for this customer. -Usually used internally by bill method B<bill>. - -If there is an error, returns the error, otherwise returns reference to a -list of line items suitable for insertion. - -=over 4 - -=item LINEITEMREF - -An array ref of the line items being billed. - -=item TAXHASHREF - -A strange beast. The keys to this hash are internal identifiers consisting -of the name of the tax object type, a space, and its unique identifier ( e.g. - 'cust_main_county 23' ). The values of the hash are listrefs. The first -item in the list is the tax object. The remaining items are either line -items or floating point values (currency amounts). - -The taxes are calculated on this entity. Calculated exemption records are -transferred to the LINEITEMREF items on the assumption that they are related. - -Read the source. - -=item INVOICE_TIME - -This specifies the date appearing on the associated invoice. Some -jurisdictions (i.e. Texas) have tax exemptions which are date sensitive. - -=back - -=cut - -sub calculate_taxes { - my ($self, $cust_bill_pkg, $taxlisthash, $invoice_time) = @_; - - # $taxlisthash is a hashref - # keys are identifiers, values are arrayrefs - # each arrayref starts with a tax object (cust_main_county or tax_rate) - # then any cust_bill_pkg objects the tax applies to - - local($DEBUG) = $FS::cust_main::DEBUG if $FS::cust_main::DEBUG > $DEBUG; - - warn "$me calculate_taxes\n" - #.Dumper($self, $cust_bill_pkg, $taxlisthash, $invoice_time). "\n" - if $DEBUG > 2; - - my @tax_line_items = (); - - # keys are tax names (as printed on invoices / itemdesc ) - # values are arrayrefs of taxlisthash keys (internal identifiers) - my %taxname = (); - - # keys are taxlisthash keys (internal identifiers) - # values are (cumulative) amounts - my %tax_amount = (); - - # keys are taxlisthash keys (internal identifiers) - # values are arrayrefs of cust_bill_pkg_tax_location hashrefs - my %tax_location = (); - - # keys are taxlisthash keys (internal identifiers) - # values are arrayrefs of cust_bill_pkg_tax_rate_location hashrefs - my %tax_rate_location = (); - - # keys are taxlisthash keys (internal identifiers!) - # values are arrayrefs of cust_tax_exempt_pkg objects - my %tax_exemption; - - foreach my $tax ( keys %$taxlisthash ) { - # $tax is a tax identifier (intersection of a tax definition record - # and a cust_bill_pkg record) - my $tax_object = shift @{ $taxlisthash->{$tax} }; - # $tax_object is a cust_main_county or tax_rate - # (with billpkgnum, pkgnum, locationnum set) - # the rest of @{ $taxlisthash->{$tax} } is cust_bill_pkg component objects - # (setup, recurring, usage classes) - warn "found ". $tax_object->taxname. " as $tax\n" if $DEBUG > 2; - warn " ". join('/', @{ $taxlisthash->{$tax} } ). "\n" if $DEBUG > 2; - # taxline calculates the tax on all cust_bill_pkgs in the - # first (arrayref) argument, and returns a hashref of 'name' - # (the line item description) and 'amount'. - # It also calculates exemptions and attaches them to the cust_bill_pkgs - # in the argument. - my $taxables = $taxlisthash->{$tax}; - my $exemptions = $tax_exemption{$tax} ||= []; - my $taxline = $tax_object->taxline( - $taxables, - 'custnum' => $self->custnum, - 'invoice_time' => $invoice_time, - 'exemptions' => $exemptions, - ); - return $taxline unless ref($taxline); - - unshift @{ $taxlisthash->{$tax} }, $tax_object; - - if ( $tax_object->isa('FS::cust_main_county') ) { - # then $taxline is a real line item - push @{ $taxname{ $taxline->itemdesc } }, $taxline; - - } else { - # leave this as is for now - - my $name = $taxline->{'name'}; - my $amount = $taxline->{'amount'}; - - #warn "adding $amount as $name\n"; - $taxname{ $name } ||= []; - push @{ $taxname{ $name } }, $tax; - - $tax_amount{ $tax } += $amount; - - # link records between cust_main_county/tax_rate and cust_location - $tax_rate_location{ $tax } ||= []; - my $taxratelocationnum = - $tax_object->tax_rate_location->taxratelocationnum; - push @{ $tax_rate_location{ $tax } }, - { - 'taxnum' => $tax_object->taxnum, - 'taxtype' => ref($tax_object), - 'amount' => sprintf('%.2f', $amount ), - 'locationtaxid' => $tax_object->location, - 'taxratelocationnum' => $taxratelocationnum, - }; - } #if ref($tax_object)... - } #foreach keys %$taxlisthash - - #consolidate and create tax line items - warn "consolidating and generating...\n" if $DEBUG > 2; - foreach my $taxname ( keys %taxname ) { - my @cust_bill_pkg_tax_location; - my @cust_bill_pkg_tax_rate_location; - my $tax_cust_bill_pkg = FS::cust_bill_pkg->new({ - 'pkgnum' => 0, - 'recur' => 0, - 'sdate' => '', - 'edate' => '', - 'itemdesc' => $taxname, - 'cust_bill_pkg_tax_location' => \@cust_bill_pkg_tax_location, - 'cust_bill_pkg_tax_rate_location' => \@cust_bill_pkg_tax_rate_location, - }); - - my $tax_total = 0; - my %seen = (); - warn "adding $taxname\n" if $DEBUG > 1; - foreach my $taxitem ( @{ $taxname{$taxname} } ) { - if ( ref($taxitem) eq 'FS::cust_bill_pkg' ) { - # then we need to transfer the amount and the links from the - # line item to the new one we're creating. - $tax_total += $taxitem->setup; - foreach my $link ( @{ $taxitem->get('cust_bill_pkg_tax_location') } ) { - $link->set('tax_cust_bill_pkg', $tax_cust_bill_pkg); - push @cust_bill_pkg_tax_location, $link; - } - } else { - # the tax_rate way - next if $seen{$taxitem}++; - warn "adding $tax_amount{$taxitem}\n" if $DEBUG > 1; - $tax_total += $tax_amount{$taxitem}; - push @cust_bill_pkg_tax_rate_location, - map { new FS::cust_bill_pkg_tax_rate_location $_ } - @{ $tax_rate_location{ $taxitem } }; - } - } - next unless $tax_total; - - # we should really neverround this up...I guess it's okay if taxline - # already returns amounts with 2 decimal places - $tax_total = sprintf('%.2f', $tax_total ); - $tax_cust_bill_pkg->set('setup', $tax_total); - - my $pkg_category = qsearchs( 'pkg_category', { 'categoryname' => $taxname, - 'disabled' => '', - }, - ); - - my @display = (); - if ( $pkg_category and - $conf->config('invoice_latexsummary') || - $conf->config('invoice_htmlsummary') - ) - { - - my %hash = ( 'section' => $pkg_category->categoryname ); - push @display, new FS::cust_bill_pkg_display { type => 'S', %hash }; - - } - $tax_cust_bill_pkg->set('display', \@display); - - push @tax_line_items, $tax_cust_bill_pkg; - } - - \@tax_line_items; -} - sub _make_lines { my ($self, %params) = @_; @@ -1055,10 +913,11 @@ sub _make_lines { my $cust_bill_pkgs = $params{line_items} or die "no line buffer specified"; my $total_setup = $params{setup} or die "no setup accumulator specified"; my $total_recur = $params{recur} or die "no recur accumulator specified"; - my $taxlisthash = $params{tax_matrix} or die "no tax accumulator specified"; my $time = $params{'time'} or die "no time specified"; my (%options) = %{$params{options}}; + my $tax_engine = $params{tax_engine}; + if ( $part_pkg->freq ne '1' and ($options{'freq_override'} || 0) > 0 ) { # this should never happen die 'freq_override billing attempted on non-monthly package '. @@ -1377,9 +1236,8 @@ sub _make_lines { ### # handle taxes ### - - my $error = $self->_handle_taxes( $taxlisthash, $cust_bill_pkg, - cancel => $options{cancel} ); + + my $error = $tax_engine->add_sale($cust_bill_pkg); return $error if $error; $cust_bill_pkg->set_display( @@ -1476,6 +1334,8 @@ sub _transfer_balance { return @transfers; } +#### vestigial code #### + =item handle_taxes TAXLISTHASH CUST_BILL_PKG [ OPTIONS ] This is _handle_taxes. It's called once for each cust_bill_pkg generated @@ -1685,6 +1545,8 @@ sub _gather_taxes { } +#### end vestigial code #### + =item collect [ HASHREF | OPTION => VALUE ... ] (Attempt to) collect money for this customer's outstanding invoices (see @@ -2526,10 +2388,7 @@ sub apply_payments { bill (do_cust_event pre-bill) _make_lines - _handle_taxes - (vendor-only) _gather_taxes _omit_zero_value_bundles - _handle_taxes (for fees) calculate_taxes apply_payments_and_credits diff --git a/FS/FS/cust_main/Import.pm b/FS/FS/cust_main/Import.pm index 0fd34ee..a243b27 100644 --- a/FS/FS/cust_main/Import.pm +++ b/FS/FS/cust_main/Import.pm @@ -2,9 +2,7 @@ package FS::cust_main::Import; use strict; use vars qw( $DEBUG $conf ); -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; use File::Slurp qw( slurp ); use FS::Misc::DateTime qw( parse_datetime ); use FS::UID qw( dbh ); @@ -67,8 +65,7 @@ Load a batch import as a queued JSRPC job sub process_batch_import { my $job = shift; - - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; my $files = $param->{'uploaded_files'} diff --git a/FS/FS/cust_main_Mixin.pm b/FS/FS/cust_main_Mixin.pm index 40c0ae9..4b151e7 100644 --- a/FS/FS/cust_main_Mixin.pm +++ b/FS/FS/cust_main_Mixin.pm @@ -526,7 +526,7 @@ sub process_email_search_result { my $job = shift; #warn "$me process_re_X $method for job $job\n" if $DEBUG; - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; $param->{'job'} = $job; diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index 6d3ed2e..16cecdf 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -2458,12 +2458,10 @@ sub modify_charge { -use Storable 'thaw'; -use MIME::Base64; use Data::Dumper; sub process_bulk_cust_pkg { my $job = shift; - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; my $old_part_pkg = qsearchs('part_pkg', diff --git a/FS/FS/cust_pkg/Import.pm b/FS/FS/cust_pkg/Import.pm index 694d814..6c8703c 100644 --- a/FS/FS/cust_pkg/Import.pm +++ b/FS/FS/cust_pkg/Import.pm @@ -2,9 +2,7 @@ package FS::cust_pkg::Import; use strict; use vars qw( $DEBUG ); #$conf ); -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; use FS::Misc::DateTime qw( parse_datetime ); use FS::Record qw( qsearchs ); use FS::cust_pkg; @@ -60,8 +58,7 @@ Load a batch import as a queued JSRPC job sub process_batch_import { my $job = shift; - - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; my $files = $param->{'uploaded_files'} diff --git a/FS/FS/cust_tax_location.pm b/FS/FS/cust_tax_location.pm index 4293b2c..cccebaf 100644 --- a/FS/FS/cust_tax_location.pm +++ b/FS/FS/cust_tax_location.pm @@ -28,38 +28,48 @@ FS::cust_tax_location - Object methods for cust_tax_location records =head1 DESCRIPTION -An FS::cust_tax_location object represents a mapping between a customer and -a tax location. FS::cust_tax_location inherits from FS::Record. The -following fields are currently supported: +An FS::cust_tax_location object represents a classification rule for +determining a tax region code ('geocode') for a service location. These +records are used when editing customer locations to help the user choose the +correct tax jurisdiction code. The jurisdiction codes are actually defined +in L<FS::tax_rate_location>, and appear directly in records in +L<FS::tax_rate>. + +FS::cust_tax_location is used in tax calculation (for CCH) to determine +"implied" geocodes for customers and locations that have a complete U.S. +ZIP+4 code and thus can be exactly placed in a jurisdiction. For those that +don't, the user is expected to choose the geocode when entering the customer +record. + +FS::cust_tax_location inherits from FS::Record. The following fields are +currently supported: =over 4 -=item custlocationnum +=item custlocationnum - primary key -primary key +=item data_vendor - a tax data vendor and "style" of record -=item data_vendor +=item country - the two-letter country code -a tax data vendor +=item state - the two-letter state code (though CCH uses this differently; +see QUIRKS) -=item zip +=item zip - an exact zip code (again, see QUIRKS) -=item state +=item ziplo - the lower bound of the zip code range (requires zip to be null) -=item plus4hi +=item ziphi - the upper bound of the zip code range (requires zip to be null) -the upper bound of the last 4 zip code digits +=item plus4lo - the lower bound of the last 4 zip code digits -=item plus4lo +=item plus4hi - the upper bound of the last 4 zip code digits -the lower bound of the last 4 zip code digits - -=item default_location - -'Y' when this record represents the default for zip - -=item geocode - the foreign key into FS::part_pkg_tax_rate and FS::tax_rate +=item default_location - 'Y' when this record represents the default. The UI +will list default locations before non-default locations. +=item geocode - the foreign key into L<FS::part_pkg_tax_rate>, +L<FS::tax_rate>, L<FS::tax_rate_location>, etc. =back @@ -123,32 +133,28 @@ sub check { || $self->ut_enum('cityflag', [ '', 'I', 'O', 'B' ] ) || $self->ut_alpha('geocode') ; + if ( $self->country ) { + $error ||= $self->ut_country('country') + || $self->ut_zip('ziphi', $self->country) + || $self->ut_zip('ziplo', $self->country); + } return $error if $error; - #ugh! cch canada weirdness and more if ($self->state eq 'CN' && $self->data_vendor eq 'cch-zip' ) { - $error = "Illegal cch canadian zip" + $error = "Illegal canadian zip" unless $self->zip =~ /^[A-Z]$/; } elsif ($self->state =~ /^E([B-DFGILNPR-UW])$/ && $self->data_vendor eq 'cch-zip' ) { - $error = "Illegal cch european zip" + $error = "Illegal european zip" unless $self->zip =~ /^E$1$/; - } else { - $error = $self->ut_number('zip', $self->state eq 'CN' ? 'CA' : 'US'); + } elsif ($self->data_vendor =~ /^cch/) { + $error = $self->ut_numbern('zip', $self->state eq 'CN' ? 'CA' : 'US'); } return $error if $error; - #ugh! cch canada weirdness and more - return "must specify either city/county or plus4lo/plus4hi" - unless ( $self->plus4lo && $self->plus4hi || - ( $self->city || - $self->state eq 'CN' || - $self->state =~ /^E([B-DFGILNPR-UW])$/ - ) && $self->county - ); - $self->SUPER::check; } +# annoyingly incompatible with FS::Record::batch_import. sub batch_import { my ($param, $job) = @_; @@ -255,6 +261,25 @@ sub batch_import { }; + } elsif ( $format eq 'billsoft' ) { + + @fields = qw( geocode alt_location country state county city + ziplo ziphi ); + $hook = sub { + my $hash = shift; + $hash->{data_vendor} = 'billsoft'; + $hash->{default_location} = ($hash->{alt_location} ? '' : 'Y'); + $hash->{city} =~ s/[^\w ]//g; # remove asterisks and other bad things + $hash->{country} = substr($hash->{country}, 0, 2); + if ( $hash->{state} =~ /^ *$/ + or $hash->{county} =~ /^ *$/ + or $hash->{country} !~ /^US|CA$/ ) { + # remove whole-country rows, whole-state rows, and non-CAN/USA rows + %$hash = (); + } + ''; + }; + } elsif ( $format eq 'extended' ) { die "unimplemented\n"; @fields = qw( ); @@ -287,7 +312,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ). ",Importing locations" + int( 100 * $imported / $count ). ",Importing tax locations" ); die $error if $error; $last = time; @@ -311,6 +336,8 @@ sub batch_import { return $error; } + # $hook can delete the contents of the hash to prevent the row from + # being inserted next unless scalar(keys %cust_tax_location); my $cust_tax_location = new FS::cust_tax_location( \%cust_tax_location ); @@ -334,9 +361,52 @@ sub batch_import { =back +=head1 SUBROUTINES + +=over 4 + +=item process_batch_import JOB, PARAMS + +Starts a batch import given JOB (an L<FS::queue>) and PARAMS (a +Base64-Storable hash). PARAMS should contain 'format' and 'uploaded_files'. + +Currently only usable for Billsoft imports; CCH's agglomeration of update +files need to be imported through L<FS::tax_rate::process_batch_import>. + +=cut + +sub process_batch_import { + my $job = shift; + my $param = shift; + + my $files = $param->{'uploaded_files'}; + + my ($file) = ($files =~ /^zipfile:(.*)$/) + or die "No files provided.\n"; + + my $dir = $FS::UID::cache_dir . '/cache.' . $FS::UID::datasrc; + open ( $param->{'filehandle'}, '<', "$dir/$file" ) + or die "unable to open '$file': $!\n"; + + my $error = batch_import($param, $job); + die $error if $error; +} + +=back + +=head1 QUIRKS + +CCH doesn't have a "country" field; for addresses in Canada it uses state += 'CN', and zip = the one-letter postal code prefix for the province. Or +maybe that's just our CCH implementation. This doesn't apply to Billsoft, +and shouldn't apply to any other tax vendor that may somehow be implemented. + +CCH also has two styles of records in this table: cch and cch-zip. cch +records define a unique + =head1 BUGS -The author should be informed of any you find. +CCH clutter. =head1 SEE ALSO diff --git a/FS/FS/detail_format.pm b/FS/FS/detail_format.pm index b072ff5..e49a9f9 100644 --- a/FS/FS/detail_format.pm +++ b/FS/FS/detail_format.pm @@ -178,6 +178,7 @@ Takes a single CDR and returns an invoice detail to describe it. By default, this maps the following fields from the CDR: +acctid => acctid rated_price => amount rated_classnum => classnum rated_seconds => duration @@ -208,6 +209,7 @@ sub single_detail { $price = 0 if $cdr->freesidestatus eq 'no-charge'; FS::cust_bill_pkg_detail->new( { + 'acctid' => $cdr->acctid, 'amount' => $price, 'classnum' => $cdr->rated_classnum, 'duration' => $cdr->rated_seconds, diff --git a/FS/FS/detail_format/sum_count.pm b/FS/FS/detail_format/sum_count.pm index c40fcb8..253956f 100644 --- a/FS/FS/detail_format/sum_count.pm +++ b/FS/FS/detail_format/sum_count.pm @@ -24,6 +24,7 @@ sub header_detail { sub append { my $self = shift; my $svcnums = ($self->{svcnums} ||= {}); + my $acctids = $self->{acctids} ||= []; foreach my $cdr (@_) { my $object = $self->{inbound} ? $cdr->cdr_termination(1) : $cdr; my $svcnum = $object->svcnum; # yes, $object->svcnum. @@ -33,6 +34,8 @@ sub append { $subtotal->{count}++; $subtotal->{amount} += $object->rated_price if $object->freesidestatus ne 'no-charge'; + + push @$acctids, $cdr->acctid; } } @@ -68,6 +71,7 @@ sub finish { startdate => '', #could use the earliest startdate in the bunch? regionname => '', #no, we're using prefix instead detail => $self->csv->string, + acctid => $self->{acctids}, }); } #foreach $svcnum diff --git a/FS/FS/detail_format/sum_duration.pm b/FS/FS/detail_format/sum_duration.pm index 1b967b4..c41bed3 100644 --- a/FS/FS/detail_format/sum_duration.pm +++ b/FS/FS/detail_format/sum_duration.pm @@ -24,6 +24,7 @@ sub header_detail { sub append { my $self = shift; my $svcnums = ($self->{svcnums} ||= {}); + my $acctids = ($self->{acctids} ||= []); foreach my $cdr (@_) { my $object = $self->{inbound} ? $cdr->cdr_termination(1) : $cdr; my $svcnum = $object->svcnum; # yes, $object->svcnum. @@ -34,6 +35,8 @@ sub append { $subtotal->{duration} += $object->rated_seconds; $subtotal->{amount} += $object->rated_price if $object->freesidestatus ne 'no-charge'; + + push @$acctids, $cdr->acctid; } } @@ -70,6 +73,7 @@ sub finish { startdate => '', #could use the earliest startdate in the bunch? regionname => '', #no, we're using prefix instead detail => $self->csv->string, + acctid => $self->{acctids}, }); } #foreach $svcnum diff --git a/FS/FS/detail_format/sum_duration_prefix.pm b/FS/FS/detail_format/sum_duration_prefix.pm index cd7bbe3..3c33dc1 100644 --- a/FS/FS/detail_format/sum_duration_prefix.pm +++ b/FS/FS/detail_format/sum_duration_prefix.pm @@ -24,6 +24,7 @@ my $prefix_length = 6; sub append { my $self = shift; my $prefixes = ($self->{prefixes} ||= {}); + my $acctids = ($self->{acctids} ||= []); foreach my $cdr (@_) { my (undef, $phonenum) = $cdr->parse_number( column => ( $self->{inbound} ? 'src' : 'dst' ), @@ -52,6 +53,8 @@ sub append { $subtotal->{duration} += $object->rated_seconds; $subtotal->{amount} += $object->rated_price if $object->freesidestatus ne 'no-charge'; + + push @$acctids, $cdr->acctid; } } @@ -91,6 +94,7 @@ sub finish { startdate => '', #could use the earliest startdate in the bunch? regionname => '', #no, we're using prefix instead detail => $self->csv->string, + acctid => $self->{acctids}, }); } #foreach $prefix } diff --git a/FS/FS/part_export/huawei_hlr.pm b/FS/FS/part_export/huawei_hlr.pm index aa09a1c..9b405d5 100644 --- a/FS/FS/part_export/huawei_hlr.pm +++ b/FS/FS/part_export/huawei_hlr.pm @@ -9,8 +9,6 @@ use FS::inventory_class; use FS::inventory_item; use IO::Socket::INET; use Data::Dumper; -use MIME::Base64 qw(decode_base64); -use Storable qw(thaw); use strict; @@ -251,7 +249,7 @@ sub command { sub process_import_sim { my $job = shift; - my $param = thaw(decode_base64(shift)); + my $param = shift; $param->{'job'} = $job; my $exportnum = delete $param->{'exportnum'}; my $export = __PACKAGE__->by_key($exportnum); diff --git a/FS/FS/part_pkg/voip_cdr.pm b/FS/FS/part_pkg/voip_cdr.pm index d3eff35..8278afe 100644 --- a/FS/FS/part_pkg/voip_cdr.pm +++ b/FS/FS/part_pkg/voip_cdr.pm @@ -10,9 +10,6 @@ use FS::Conf; use FS::Record qw(qsearchs qsearch); use FS::cdr; use FS::detail_format; -#use FS::rate; -#use FS::rate_prefix; -#use FS::rate_detail; #for ::granularities $DEBUG = 0; diff --git a/FS/FS/part_pkg_taxclass.pm b/FS/FS/part_pkg_taxclass.pm index 824fd17..055c778 100644 --- a/FS/FS/part_pkg_taxclass.pm +++ b/FS/FS/part_pkg_taxclass.pm @@ -30,8 +30,16 @@ FS::part_pkg_taxclass - Object methods for part_pkg_taxclass records =head1 DESCRIPTION -An FS::part_pkg_taxclass object represents a tax class. FS::part_pkg_taxclass -inherits from FS::Record. The following fields are currently supported: +An FS::part_pkg_taxclass object declares the existence of a taxable sales +class. FS::part_pkg_taxclass inherits from FS::Record. + +FS::part_pkg_taxclass is not used in tax calculation. It is only used to +list a set of valid tax class names for use in the user interface. When +using internal taxes, the actual matching of tax definitions to package +tax class is a string match between tax class names. This is arguably +a bug. + +The following fields are currently supported: =over 4 diff --git a/FS/FS/part_pkg_taxproduct.pm b/FS/FS/part_pkg_taxproduct.pm index ddea1da..c12a432 100644 --- a/FS/FS/part_pkg_taxproduct.pm +++ b/FS/FS/part_pkg_taxproduct.pm @@ -3,6 +3,7 @@ package FS::part_pkg_taxproduct; use strict; use vars qw( @ISA $delete_kludge ); use FS::Record qw( qsearch dbh ); +use Text::CSV_XS; @ISA = qw(FS::Record); $delete_kludge = 0; @@ -28,7 +29,7 @@ FS::part_pkg_taxproduct - Object methods for part_pkg_taxproduct records =head1 DESCRIPTION -An FS::part_pkg_taxproduct object represents a tax product. +An FS::part_pkg_taxproduct object represents a tax product. FS::part_pkg_taxproduct inherits from FS::Record. The following fields are currently supported: @@ -199,6 +200,55 @@ sub expand_cch_taxproduct { =cut +sub batch_import { + my ($param, $job) = @_; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my $fh = $param->{filehandle}; + my $format = $param->{format}; + die "unsupported part_pkg_taxproduct format '$format'" + unless $format eq 'billsoft'; + + # this is slightly silly + my @lines = <$fh>; + my $lines = scalar @lines; + seek($fh, 0, 0); + + my $imported = 0; + my $csv = Text::CSV_XS->new; + # fields: taxproduct, description + while ( my $row = $csv->getline($fh) ) { + if (!defined $row) { + $dbh->rollback if $oldAutoCommit; + return "can't parse: ". $csv->error_input(); + } + + if ( $job ) { + $job->update_statustext( + int( 100 * $imported / $lines ) . ',Inserting tax product records' + ); + } + + my $new = FS::part_pkg_taxproduct->new({ + 'data_vendor' => 'billsoft', + 'taxproduct' => $row->[0], + 'description' => $row->[1], + }); + my $error = $new->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "error inserting part_pkg_taxproduct: $error\n"; + } + $imported++; + } + + $dbh->commit if $oldAutoCommit; + return ''; +} + =head1 BUGS Confusingly named. It has nothing to do with part_pkg. diff --git a/FS/FS/part_svc.pm b/FS/FS/part_svc.pm index 01f3034..9ed56eb 100644 --- a/FS/FS/part_svc.pm +++ b/FS/FS/part_svc.pm @@ -722,13 +722,10 @@ Job-queue processor for web interface adds/edits =cut -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process { my $job = shift; - - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; my $old = qsearchs('part_svc', { 'svcpart' => $param->{'svcpart'} }) @@ -802,13 +799,10 @@ Job-queue processor for web interface bulk customer service changes =cut -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process_bulk_cust_svc { my $job = shift; - - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; local($FS::svc_Common::noexport_hack) = 1 diff --git a/FS/FS/pay_batch.pm b/FS/FS/pay_batch.pm index 9ce6a83..b6b69f3 100644 --- a/FS/FS/pay_batch.pm +++ b/FS/FS/pay_batch.pm @@ -436,12 +436,10 @@ sub import_results { } -use MIME::Base64; -use Storable 'thaw'; use Data::Dumper; sub process_import_results { my $job = shift; - my $param = thaw(decode_base64(shift)); + my $param = shift; $param->{'job'} = $job; warn Dumper($param) if $DEBUG; my $gatewaynum = delete $param->{'gatewaynum'}; diff --git a/FS/FS/rate.pm b/FS/FS/rate.pm index 9a5b905..a3826bf 100644 --- a/FS/FS/rate.pm +++ b/FS/FS/rate.pm @@ -422,13 +422,10 @@ Job-queue processor for web interface adds/edits =cut -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process { my $job = shift; - - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; my $old = qsearchs('rate', { 'ratenum' => $param->{'ratenum'} } ) diff --git a/FS/FS/rate_detail.pm b/FS/FS/rate_detail.pm index d50c89f..fcd9f58 100644 --- a/FS/FS/rate_detail.pm +++ b/FS/FS/rate_detail.pm @@ -310,9 +310,7 @@ sub conn_secs { =cut -use Storable qw(thaw); use Data::Dumper; -use MIME::Base64; sub process_edit_import { my $job = shift; @@ -351,7 +349,7 @@ sub process_edit_import { my @pass_params = @{ $opt->{params} }; my %formats = %{ $opt->{formats} }; - my $param = thaw(decode_base64(shift)); + my $param = shift; warn Dumper($param) if $DEBUG; my $files = $param->{'uploaded_files'} diff --git a/FS/FS/tax_class.pm b/FS/FS/tax_class.pm index 04e9d37..904b575 100644 --- a/FS/FS/tax_class.pm +++ b/FS/FS/tax_class.pm @@ -31,26 +31,24 @@ FS::tax_class - Object methods for tax_class records =head1 DESCRIPTION -An FS::tax_class object represents a tax class. FS::tax_class -inherits from FS::Record. The following fields are currently supported: +An FS::tax_class object represents a class of tax definitions. FS::tax_class +inherits from FS::Record. -=over 4 - -=item taxclassnum - -Primary key +This should not be confused with L<FS::part_pkg_taxclass>, which defines tax +classes for I<package> definitions. The two kinds of tax classes are +completely unrelated. -=item data_vendor +The following fields are currently supported: -Vendor of the tax data +=over 4 -=item taxclass +=item taxclassnum - Primary key -Tax class +=item data_vendor - Vendor of the tax data ('cch' or 'billsoft') -=item description +=item taxclass - The identifier used in the tax tables for this class. -Human readable description of the tax class +=item description - Human readable description of the tax class. =back @@ -321,6 +319,13 @@ sub batch_import { ''; }; + } elsif ( $format eq 'billsoft' ) { + # Billsoft doesn't actually have a format for this; it's just my own + # invention to have a way to load the list of tax classes from the + # documentation. + @fields = qw( taxclass description ); + $endhook = $hook = sub {}; + } elsif ( $format eq 'extended' ) { die "unimplemented\n"; @fields = qw( ); diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm index d773ff5..a6da3d1 100644 --- a/FS/FS/tax_rate.pm +++ b/FS/FS/tax_rate.pm @@ -8,7 +8,6 @@ use vars qw( $DEBUG $me use Date::Parse; use DateTime; use DateTime::Format::Strptime; -use Storable qw( thaw nfreeze ); use IO::File; use File::Temp; use Text::CSV_XS; @@ -16,7 +15,6 @@ use URI::Escape; use LWP::UserAgent; use HTTP::Request; use HTTP::Response; -use MIME::Base64; use DBIx::DBSchema; use DBIx::DBSchema::Table; use DBIx::DBSchema::Column; @@ -80,9 +78,10 @@ a location code provided by a tax authority =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 @@ -214,7 +213,7 @@ sub check { || $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') @@ -380,7 +379,7 @@ sub passtype_name { $tax_passtypes{$self->passtype}; } -=item taxline TAXABLES +=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 @@ -388,7 +387,7 @@ is returned as a scalar. =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. @@ -614,6 +613,36 @@ sub tax_rate_location { } + +=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 @@ -933,35 +962,25 @@ Load a batch import as a queued JSRPC job =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."; @@ -969,20 +988,18 @@ sub _perform_batch_import { 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, @@ -1051,19 +1068,45 @@ sub _perform_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 ) = @_; @@ -1549,15 +1592,20 @@ sub _copy_from_temp { =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 @@ -1566,15 +1614,12 @@ after wiping the existing wipable tax data. =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(); @@ -1588,47 +1633,79 @@ sub _process_reload { 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]; - #disable tax_rates - $error ||= - _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate ); + # really should get a table EXCLUSIVE lock here - #wipe out the old data - $error ||= _remove_old_tax_data( $job, $format ); + #remember disabled taxes + my %disabled_tax_rate = (); + $error ||= _remember_disabled_taxes( $job, $format, \%disabled_tax_rate ); - #untemporize - $error ||= _copy_from_temp( $job, $format ); + #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 ); + + #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'"; + } + + foreach (@sql) { + if (!$dbh->do($_)) { + $error = $dbh->errstr; + last; + } + } + + unless ( $error ) { + local $@; + eval { &{ $continuation }($job, $param) }; + $error = $@; + } + } # if ($format ...) if ($error) { $dbh->rollback or die $dbh->errstr if $oldAutoCommit; @@ -1649,7 +1726,7 @@ Download and process a tax update as a queued JSRPC job 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(); @@ -1752,7 +1829,7 @@ sub process_download_and_update { $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"; @@ -1855,7 +1932,7 @@ PARAMS needs to be a base64-encoded Storable hash containing: 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}); @@ -2160,11 +2237,17 @@ 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> diff --git a/FS/FS/tax_rate_location.pm b/FS/FS/tax_rate_location.pm index b4be8b9..d9646e4 100644 --- a/FS/FS/tax_rate_location.pm +++ b/FS/FS/tax_rate_location.pm @@ -26,39 +26,32 @@ FS::tax_rate_location - Object methods for tax_rate_location records =head1 DESCRIPTION -An FS::tax_rate_location object represents an example. FS::tax_rate_location inherits from -FS::Record. The following fields are currently supported: +An FS::tax_rate_location object represents a tax jurisdiction. The only +functional field is "geocode", a foreign key to tax rates (L<FS::tax_rate>) +that apply in the jurisdiction. The city, county, state, and country fields +are provided for description and reporting. -=over 4 - -=item taxratelocationnum - -Primary key (assigned automatically for new tax_rate_locations) - -=item data_vendor +FS::tax_rate_location inherits from FS::Record. The following fields are +currently supported: -The tax data vendor - -=item geocode - -A unique geographic location code provided by the data vendor - -=item city +=over 4 -City +=item taxratelocationnum - Primary key (assigned automatically for new +tax_rate_locations) -=item county +=item data_vendor - The tax data vendor ('cch' or 'billsoft'). -County +=item geocode - A unique geographic location code provided by the data vendor -=item state +=item city - City -State +=item county - County -=item disabled +=item state - State (2-letter code) -If 'Y' this record is no longer active. +=item country - Country (2-letter code, optional) +=item disabled - If 'Y' this record is no longer active. =back @@ -149,6 +142,40 @@ sub check { $self->SUPER::check; } +=item find_or_insert + +Finds an existing, non-disabled tax jurisdiction matching the data_vendor +and geocode fields. If there is one, updates its city, county, state, and +country to match this record. If there is no existing record, inserts this +record. + +=cut + +sub find_or_insert { + my $self = shift; + my $existing = qsearchs('tax_rate_location', { + disabled => '', + data_vendor => $self->data_vendor, + geocode => $self->geocode + }); + if ($existing) { + my $update = 0; + foreach (qw(city county state country)) { + if ($self->get($_) ne $existing->get($_)) { + $update++; + } + } + $self->set(taxratelocationnum => $existing->taxratelocationnum); + if ($update) { + return $self->replace($existing); + } else { + return; + } + } else { + return $self->insert; + } +} + =back =head1 CLASS METHODS @@ -186,10 +213,17 @@ sub location_sql { =over 4 -=item batch_import +=item batch_import HASHREF, JOB + +Starts importing tax_rate_location records from a file. HASHREF must contain +'filehandle' (an open handle to the input file) and 'format' (one of 'cch', +'cch-fixed', 'cch-update', 'cch-fixed-update', or 'billsoft'). JOB is an +L<FS::queue> object to receive progress messages. =cut +# XXX move this into TaxEngine modules at some point + sub batch_import { my ($param, $job) = @_; @@ -214,7 +248,7 @@ sub batch_import { my $line; my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar - if ( $job || scalar(@column_callbacks) ) { + if ( $job || scalar(@column_callbacks) ) { # this makes zero sense my $error = csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks); return $error if $error; @@ -251,6 +285,29 @@ sub batch_import { }; + } elsif ( $format eq 'billsoft' ) { + @fields = ( qw( geocode alt_location country state county city ), '', '' ); + + $hook = sub { + my $hash = shift; + if ($hash->{alt_location}) { + # don't import these; the jurisdiction should be named using its + # primary city + %$hash = (); + return; + } + + $hash->{data_vendor} = 'billsoft'; + # unlike cust_tax_location, keep the whole-country and whole-state + # rows, but strip the whitespace + $hash->{county} =~ s/^ //g; + $hash->{state} =~ s/^ //g; + $hash->{country} =~ s/^ //g; + $hash->{city} =~ s/[^\w ]//g; # remove asterisks and other bad things + $hash->{country} = substr($hash->{country}, 0, 2); + ''; + } + } elsif ( $format eq 'extended' ) { die "unimplemented\n"; @fields = qw( ); @@ -286,7 +343,8 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ) . + ',Creating tax jurisdiction records' ); die $error if $error; $last = time; diff --git a/FS/FS/tax_status.pm b/FS/FS/tax_status.pm new file mode 100644 index 0000000..f03eeca --- /dev/null +++ b/FS/FS/tax_status.pm @@ -0,0 +1,163 @@ +package FS::tax_status; + +use strict; +use base qw( FS::Record ); +use FS::Record qw( qsearch qsearchs ); + +our %initial_data; + +=head1 NAME + +FS::tax_status - Object methods for tax_status records + +=head1 SYNOPSIS + + use FS::tax_status; + + $record = new FS::tax_status \%hash; + $record = new FS::tax_status { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::tax_status object represents a customer tax status for use with +an external tax table. FS::tax_status inherits from FS::Record. The +following fields are currently supported: + +=over 4 + +=item taxstatusnum + +primary key + +=item data_vendor + +Data vendor name (corresponds to the value of the C<taxproduct> config +variable.) + +=item taxstatus + +The data vendor's name or code for the tax status. + +=item description + +Description for use in the Freeside UI. + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new tax status. To add the record to the database, see L<"insert">. + +=cut + +sub table { 'tax_status'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=item delete + +Delete this record from the database. + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=item check + +Checks all fields to make sure this is a valid example. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('taxstatusnum') + || $self->ut_textn('data_vendor') + || $self->ut_text('taxstatus') + || $self->ut_text('description') + ; + return $error if $error; + + $self->SUPER::check; +} + +sub _upgrade_data { + my $self = shift; + my $error; + foreach my $data_vendor ( keys %initial_data ) { + my $status_hash = $initial_data{$data_vendor}; + foreach my $taxstatus (sort keys %$status_hash) { + my $description = $status_hash->{$taxstatus}; + my $tax_status; + if ($tax_status = qsearchs('tax_status', { + data_vendor => $data_vendor, + taxstatus => $taxstatus + })) + { + if ($tax_status->description ne $description) { + $tax_status->set(description => $description); + $error = $tax_status->replace; + } + # else it's already correct + } else { + $tax_status = FS::tax_status->new({ + data_vendor => $data_vendor, + taxstatus => $taxstatus, + description => $description + }); + $error = $tax_status->insert; + } + die $error if $error; + } + } +} + +%initial_data = ( + 'avalara' => { + 'A' => 'Federal Government', + 'B' => 'State/Local Government', + 'C' => 'Tribal Government', + 'D' => 'Foreign Diplomat', + 'E' => 'Charitable Organization', + 'F' => 'Religious/Education', + 'G' => 'Resale', + 'H' => 'Agricultural Production', + 'I' => 'Industrial Production', + 'J' => 'Direct Pay Permit', + 'K' => 'Direct Mail', + 'L' => 'Other', + 'M' => 'Local Government', + # P, Q, R: Canada, not yet supported + # MED1/MED2: totally irrelevant to our users + }, +); + +=back + +=head1 SEE ALSO + +L<FS::Record>, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index 9a9573d..4811e10 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -818,3 +818,5 @@ FS/deploy_zone_block.pm t/deploy_zone_block.t FS/deploy_zone_vertex.pm t/deploy_zone_vertex.t +FS/tax_status.pm +t/tax_status.t diff --git a/FS/bin/freeside-daily b/FS/bin/freeside-daily index 294099a..f14e2b3 100755 --- a/FS/bin/freeside-daily +++ b/FS/bin/freeside-daily @@ -27,6 +27,10 @@ expire_banned_pay(%opt); use FS::Cron::bill qw(bill); bill(%opt); +#you can skip this by not setting enable_taxproducts to 'billsoft' +use FS::Cron::tax_batch qw(process_tax_batch); +process_tax_batch(%opt); + #you can skip this just by not having the config use FS::Cron::breakage qw(reconcile_breakage); reconcile_breakage(%opt); diff --git a/FS/bin/freeside-tax-location-update b/FS/bin/freeside-tax-location-update new file mode 100644 index 0000000..f45e5e1 --- /dev/null +++ b/FS/bin/freeside-tax-location-update @@ -0,0 +1,75 @@ +#!/usr/bin/perl + +use strict; +use Getopt::Std; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch dbh); +use FS::TaxEngine; +use FS::cust_location; + +my %opt; +getopts('d', \%opt); + +my $user = shift or die &usage; +adminsuidsetup($user); +$FS::UID::AutoCommit = 0; +my $dbh = dbh; + +my $engine = FS::TaxEngine->new; +my %hash = ( 'geocode' => '', + 'country' => 'US' ); +$hash{'disabled'} = '' unless $opt{d}; +my @locations = qsearch('cust_location', \%hash); +foreach my $location (@locations) { + print $location->location_label . "..."; + # only take the first one (the 'default') + my ($cust_tax_location) = $engine->cust_tax_locations($location); + if ($cust_tax_location) { + print $cust_tax_location->geocode; + $location->set('geocode', $cust_tax_location->geocode); + # geocode is not an immutable location field, so this is safe + my $error = $location->replace; + if ( $error ) { + print "$error\n"; + } + } else { + print "not found."; + } + print "\n"; +} +$dbh->commit; +print "Finished!\n"; + +sub usage { + "Usage:\n\n freeside-tax-location-update [ -d ] user\n\n" + } + +=head1 NAME + +freeside-tax-location-update - Update service locations with tax data vendor +codes. + +=head1 SYNOPSIS + + freeside-tax-location-update [ -d ] user + +=head1 DESCRIPTION + +When using tax tables from an external vendor, there's a table of tax +jurisdiction codes that act as a foreign key to the tax rate definitions. +The jurisdiction is usually chosen based on the customer's postal code. + +This script finds all non-disabled customer locations that don't have a +value in the 'geocode' field, finds the most likely matching geocode in the +cust_tax_location table, and stores that geocode in the cust_location record. +This is not guaranteed to be accurate. There may be multiple correct +geocodes for a given zip code; the script chooses the one that's marked +as "default". + +The -d option tells the script to work on disabled location records as well, +which is not likely to be necessary. + +Updating the geocode this way is not a location change and does not trigger +a cancel/reorder of the customer's packages. + +=cut diff --git a/FS/t/tax_status.t b/FS/t/tax_status.t new file mode 100644 index 0000000..9c99492 --- /dev/null +++ b/FS/t/tax_status.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::tax_status; +$loaded=1; +print "ok 1\n"; diff --git a/httemplate/browse/part_pkg_taxproduct/avalara.html b/httemplate/browse/part_pkg_taxproduct/avalara.html new file mode 100755 index 0000000..e8da589 --- /dev/null +++ b/httemplate/browse/part_pkg_taxproduct/avalara.html @@ -0,0 +1,84 @@ +<& /elements/header-popup.html, { title => 'Select tax product' } &> +<form NAME="myform"> +<table class="inv" width="100%"> +<& /elements/tr-select-table.html, + 'label' => 'Tax product', + 'field' => 'taxproductnum', + 'table' => 'part_pkg_taxproduct', + 'hashref' => { data_vendor => 'avalara' }, + 'name_col' => 'taxproduct', # for sorting + 'label_callback' => $label_callback, + 'curr_value' => $taxproductnum, + 'empty_label' => 'none', + 'onchange' => 'select_onchange', +&> +</table> +<table class="inv" width="100%"> +<tr> + <td style="border-top: 1px solid #7e0079; text-align: center" colspan=2> + Add a new tax product</td> +</tr> +<tr> + <td style="text-align: right">Avalara tax code</td> + <td><input name="taxproduct" size=8></td> +</tr> +<tr> + <td style="text-align: right">Description</td> + <td><input name="description" size=20></td> +</tr> +<tr> + <td colspan="2" style="text-align: center"> + <input type="button" onclick="add_new()" value="Add" /> + </td> +</tr> +</table> +</form> +<SCRIPT TYPE="text/javascript"> +function select_onchange() { + var select = document.forms['myform']['taxproductnum']; + parent.document.getElementById('<% $id %>').value = select.value; + parent.document.getElementById('<% $id %>_description').value = + select.options[select.selectedIndex].text; + parent.nd(1); +} +function add_new() { + parent.document.getElementById('<% $id %>').value = -1; + parent.document.getElementById('<% $id %>_description').value = + document.forms['myform']['taxproduct'].value + ' ' + + document.forms['myform']['description'].value; + parent.nd(1); +} +</SCRIPT> +</BODY> +</HTML> +<%once> + +my $conf = new FS::Conf; + +</%once> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Edit package definitions'); + +warn Dumper({ $cgi->Vars }); + +# id: where to put the taxproductnum (in the parent document) after the user +# selects it +$cgi->param('id') =~ /^([ \w]+)$/ + or die "id parameter required"; +my $id = $1; + +# current value of taxproductnum +my $taxproductnum = ''; +if ($cgi->param('taxproductnum') =~ /^(\d+)$/) { + $taxproductnum = $1; +} + +my $label_callback = sub { + my $part_pkg_taxproduct = shift; + join(' ', $part_pkg_taxproduct->taxproduct, + $part_pkg_taxproduct->description); +}; + +</%init> diff --git a/httemplate/browse/part_pkg_taxproduct/billsoft.html b/httemplate/browse/part_pkg_taxproduct/billsoft.html new file mode 100755 index 0000000..c58ac30 --- /dev/null +++ b/httemplate/browse/part_pkg_taxproduct/billsoft.html @@ -0,0 +1,146 @@ +<& /elements/header-popup.html, $title &> +<& /browse/elements/browse.html, + 'name_singular' => 'tax product', + 'html_form' => include('.form', $service_code, $trans_code), + 'query' => { + 'table' => 'part_pkg_taxproduct', + 'hashref' => $hashref, + 'order_by' => 'ORDER BY taxproduct', + }, + 'count_query' => $count_query, + 'header' => \@header, + 'fields' => \@fields, + 'align' => $align, + 'links' => [], + 'link_onclicks' => \@link_onclicks, + 'nohtmlheader' => 1, +&> +<%shared> +# populate dropdowns + +# taxproduct is 12 digits. First half is the service type code, second +# half is the transaction code. Description is also two parts, corresponding +# to those codes, separated with a :. + +my (@service_codes, @trans_codes, %service_labels, %trans_labels); +foreach my $row ( qsearch({ + table => 'part_pkg_taxproduct', + select => 'DISTINCT substr(taxproduct, 1, 6) AS code, '. + "substring(description from '(.*):') AS label", + })) +{ + $service_labels{$row->get('code')} = + sprintf('%02d %s', $row->get('code'), $row->get('label')); +} +foreach my $row ( qsearch({ + table => 'part_pkg_taxproduct', + select => 'DISTINCT substr(taxproduct, 7, 6) AS code, '. + "substring(description from ':(.*)') AS label", + })) +{ + $trans_labels{$row->get('code')} = + sprintf('%02d %s', $row->get('code'), $row->get('label')); +} +$service_labels{''} = $trans_labels{''} = ''; + +@service_codes = sort {$a <=> $b} keys %service_labels; +@trans_codes = sort {$a <=> $b} keys %trans_labels; + +</%shared> +<%def .form> +% my ($service_code, $trans_code) = @_; +<FORM ACTION="<% $cgi->url %>" METHOD="GET"> +<& /elements/select.html, + field => 'service_code', + options => \@service_codes, + labels => \%service_labels, + curr_value => $service_code, + onchange => 'this.form.submit()', +&> + +<& /elements/select.html, + field => 'trans_code', + options => \@trans_codes, + labels => \%trans_labels, + curr_value => $trans_code, + onchange => 'this.form.submit()', +&> +<& /elements/hidden.html, + field => 'id', + curr_value => $cgi->param('id'), +&> +</%def> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +$cgi->param('id') =~ /^\w+$/ or die "missing id parameter"; +my $id = $cgi->param('id'); + +my $select_onclick = sub { + my $row = shift; + my $taxnum = $row->taxproductnum; + my $desc = $row->description; + "parent.document.getElementById('$id').value = $taxnum;". + "parent.document.getElementById('${id}_description').value = '$desc';". + "parent.cClick();"; +} + if $id; + +my @menubar; +my $title = 'Tax Products'; + +my $hashref = { data_vendor => 'billsoft' }; + +my ($service_code, $trans_code, $taxproduct); +if ( $cgi->param('service_code') =~ /^(\d+)$/ ) { + $service_code = $1; + $taxproduct = sprintf('%06d', $service_code); +} else { + $taxproduct = '%'; +} + +if ( $cgi->param('trans_code') =~ /^(\d+)$/ ) { + $trans_code = $1; + $taxproduct .= sprintf('%06d', $trans_code); +} elsif ( $service_code ) { + $taxproduct .= '%'; +} + +$hashref->{taxproduct} = { op => 'LIKE', value => $taxproduct }; + +my $count_query = "SELECT COUNT(*) FROM part_pkg_taxproduct ". + "WHERE data_vendor = 'billsoft' AND ". + "taxproduct LIKE '$taxproduct'"; + +my $sub_service_desc = sub { + my $ppt = shift; #part_pkg_taxproduct + my @codes = ($ppt->taxproduct =~ /(\d{6})(\d{6})/); + my @descs = split(':', $ppt->description); + $ppt->set('service_desc' => sprintf('%02d %s', $codes[0], $descs[0])); + $ppt->set('trans_desc' => sprintf('%02d %s', $codes[1], $descs[1])); + $ppt->service_desc; +}; + +my $sub_trans_desc = sub { + my $ppt = shift; + $ppt->trans_desc; +}; + +my @fields = ( + $sub_service_desc, + $sub_trans_desc, + 'note' +); + +my @header = ( + 'Service Type', + 'Transaction', + '', +); + +my $align = 'lll'; +my @link_onclicks = ( $select_onclick, $select_onclick ); + +</%init> diff --git a/httemplate/browse/part_pkg_taxproduct.cgi b/httemplate/browse/part_pkg_taxproduct/cch.html index 7e0cb81..b901bad 100755 --- a/httemplate/browse/part_pkg_taxproduct.cgi +++ b/httemplate/browse/part_pkg_taxproduct/cch.html @@ -1,4 +1,4 @@ -<% include( 'elements/browse.html', +<% include( '../elements/browse.html', 'title' => "Tax Products $title", 'name_singular' => 'tax product', 'menubar' => \@menubar, @@ -33,12 +33,7 @@ my @menubar; my $title = ''; my $onclick = 'cClick'; -my $data_vendor = ''; -if ( $cgi->param('data_vendor') =~ /^(\w+)$/ ) { - $data_vendor = $1; - $title = "$data_vendor"; -} -$cgi->delete('data_vendor'); +my $data_vendor = 'cch'; $title = " for $title" if $title; @@ -83,10 +78,7 @@ if ($taxproductnum) { } my $hashref = {}; -my $extra_sql = ''; -if ( $data_vendor ) { - $extra_sql .= ' WHERE data_vendor = '. dbh->quote($data_vendor); -} +my $extra_sql .= ' WHERE data_vendor = '. dbh->quote($data_vendor); if ($tax_group || $tax_item || $tax_customer || $tax_provider) { my $compare = "LIKE '". ( $tax_group || "%" ). " : ". ( $tax_item || "%" ). " : ". @@ -111,7 +103,6 @@ if ( $tax_group || $tax_item || $tax_provider || $tax_customer ) { $cgi->param('dummy', 1); #restore this so pagination works -$cgi->param('data_vendor', $data_vendor) if $data_vendor; $cgi->param('tax_group', $tax_group) if $tax_group; $cgi->param('tax_item', $tax_item ) if $tax_item; $cgi->param('tax_provider', $tax_provider ) if $tax_provider; @@ -120,23 +111,20 @@ $cgi->param('onclick', $onclick ) if $onclick; my $count_query = "SELECT COUNT(*) FROM part_pkg_taxproduct $extra_sql"; -my @header = ( 'Data Vendor', 'Group', 'Item', 'Provider', 'Customer' ); +my @header = ( 'Group', 'Item', 'Provider', 'Customer' ); my @links = ( $select_link, $select_link, $select_link, $select_link, - $select_link, ); my @link_onclicks = ( $select_onclick, $select_onclick, $select_onclick, $select_onclick, - $select_onclick, ); -my $align = 'lllll'; +my $align = 'llll'; my @fields = ( - 'data_vendor', sub { shift->description =~ /^(.*):.*:.*:.*$/; $1;}, sub { shift->description =~ /^.*:(.*):.*:.*$/; $1;}, sub { shift->description =~ /^.*:.*:(.*):.*$/; $1;}, @@ -162,31 +150,16 @@ $html_init .= qq( <INPUT NAME="id" TYPE="hidden" VALUE="$id"> <TABLE> <TR> - <TD><SELECT NAME="data_vendor" onChange="this.form.submit()"> -); - -my $sql = "SELECT DISTINCT data_vendor FROM part_pkg_taxproduct ORDER BY data_vendor"; -my $dbh = dbh; -my $sth = $dbh->prepare($sql) or die $dbh->errstr; -$sth->execute or die $sth->errstr; -for (['(choose data vendor)'], @{$sth->fetchall_arrayref}) { - $html_init .= '<OPTION VALUE="'. $_->[0]. '"'. - ($_->[0] eq $data_vendor ? " SELECTED" : ""). - '">'. $_->[0]; -} -$html_init .= qq( - </SELECT> - -<!-- cch specific --> <TD><SELECT NAME="tax_group" onChange="this.form.submit()"> ); -$sql = "SELECT DISTINCT ". +my $sql = "SELECT DISTINCT ". qq!substring(description from '#"%#" : % : % : %' for '#'),!. qq!substring(description from '#"%#" : % : % : %' for '#')!. "FROM part_pkg_taxproduct ORDER BY 1"; -$sth = $dbh->prepare($sql) or die $dbh->errstr; +my $dbh = dbh; +my $sth = $dbh->prepare($sql) or die $dbh->errstr; $sth->execute or die $sth->errstr; for (['', '(choose group)'], @{$sth->fetchall_arrayref}) { $html_init .= '<OPTION VALUE="'. $_->[0]. '"'. diff --git a/httemplate/edit/cust_main.cgi b/httemplate/edit/cust_main.cgi index ddb61fa..ae5085e 100755 --- a/httemplate/edit/cust_main.cgi +++ b/httemplate/edit/cust_main.cgi @@ -25,7 +25,7 @@ %# agent, agent_custid, refnum (advertising source), referral_custnum %# better section title for this? <FONT CLASS="fsinnerbox-title"><% mt('Basics') |h %></FONT> -<& cust_main/top_misc.html, $cust_main, 'custnum' => $custnum &> +<& cust_main/basics.html, $cust_main, 'custnum' => $custnum &> %# birthdate % if ( $conf->config('national_id-country') diff --git a/httemplate/edit/cust_main/top_misc.html b/httemplate/edit/cust_main/basics.html index 41dd563..91868d4 100644 --- a/httemplate/edit/cust_main/top_misc.html +++ b/httemplate/edit/cust_main/basics.html @@ -145,6 +145,13 @@ 'label' => emt("Class"), &> +%# tax status +<& /elements/tr-select-tax_status.html, + 'curr_value' => $cust_main->taxstatusnum, + 'disable_empty' => 0, + 'empty_label' => ' ', +&> + %#sales person <& /elements/tr-select-sales.html, 'curr_value' => $cust_main->salesnum, diff --git a/httemplate/edit/process/part_pkg.cgi b/httemplate/edit/process/part_pkg.cgi index d27ddb0..0343cc0 100755 --- a/httemplate/edit/process/part_pkg.cgi +++ b/httemplate/edit/process/part_pkg.cgi @@ -96,11 +96,31 @@ my $args_callback = sub { grep { $_ !~ /^report_option_/ } @options; - foreach ( split(',', $cgi->param('taxproductnums') ) ) { - my $value = $cgi->param("taxproductnum_$_"); - $error ||= "Illegal taxproductnum_$_: $value" + foreach my $class ( '', split(',', $cgi->param('taxproductnums') ) ) { + my $param = 'taxproductnum'; + $param .= "_$class" if length($class); # gah, "_$class"? + my $value = $cgi->param($param); + + if ( $value == -1 ) { + my $desc = $cgi->param($param.'_description'); + # insert a new part_pkg_taxproduct + my $engine = FS::TaxEngine->new; + my $obj_or_error = $engine->add_taxproduct($desc); + if (ref $obj_or_error) { + $value = $obj_or_error->taxproductnum; + $cgi->param($param, $value); # for error handling + } else { + die "$obj_or_error (adding tax product)"; + } + } + + $error ||= "Illegal $param: $value" unless ( $value =~ /^\d*$/ ); - $options{"usage_taxproductnum_$_"} = $value; + if (length($class)) { + $options{"usage_taxproductnum_$_"} = $value; + } else { + $new->set('taxproductnum', $value); + } } foreach ( grep $_, $cgi->param('report_option') ) { diff --git a/httemplate/elements/select-table.html b/httemplate/elements/select-table.html index e736388..0b04fee 100644 --- a/httemplate/elements/select-table.html +++ b/httemplate/elements/select-table.html @@ -89,10 +89,12 @@ Example: <OPTION VALUE=""><% $opt{'empty_label'} || 'all' %> % } -% foreach my $record ( sort { $a->$name_col() cmp $b->$name_col() -% || $a->$key() <=> $b->$key() -% } -% @records +% foreach my $record ( +% # we have the order_by parameter for this +% #sort { $a->$name_col() cmp $b->$name_col() +% # || $a->$key() <=> $b->$key() +% # } +% @records % ) % { % my $recvalue = $record->$key(); @@ -174,7 +176,8 @@ if ( $opt{'agent_virt'} ) { my @records = (); if ( $opt{'records'} ) { - @records = @{ $opt{'records'} }; + @records = sort { $a->get($name_col) cmp $b->get($name_col) } + @{ $opt{'records'} }; } else { @records = qsearch( { 'table' => $opt{'table'}, diff --git a/httemplate/elements/select-taxproduct.html b/httemplate/elements/select-taxproduct.html index 0f6ef55..07e5549 100644 --- a/httemplate/elements/select-taxproduct.html +++ b/httemplate/elements/select-taxproduct.html @@ -23,6 +23,9 @@ unless ( $description || ! $value ) { if $part_pkg_taxproduct; } -my $onclick = $opt{onclick} || "overlib( OLiframeContent('${p}/browse/part_pkg_taxproduct.cgi?_type=select&id=${name}&taxproductnum='+document.getElementById('${name}').value, 1000, 400, 'tax_product_popup'), CAPTION, 'Select product', STICKY, AUTOSTATUSCAP, MIDX, 0, MIDY, 0, DRAGGABLE, CLOSECLICK); return false;"; +my $conf = FS::Conf->new; +my $vendor = lc($conf->config('enable_taxproducts')); +my $onclick = $opt{onclick} || + "overlib( OLiframeContent('${p}/browse/part_pkg_taxproduct/$vendor.html?_type=select&id=${name}&taxproductnum='+document.getElementById('${name}').value, 1000, 400, 'tax_product_popup'), CAPTION, 'Select product', STICKY, AUTOSTATUSCAP, MIDX, 0, MIDY, 0, DRAGGABLE, CLOSECLICK); return false;"; </%init> diff --git a/httemplate/elements/standardize_locations.js b/httemplate/elements/standardize_locations.js index 2d1fc74..817a2e3 100644 --- a/httemplate/elements/standardize_locations.js +++ b/httemplate/elements/standardize_locations.js @@ -179,7 +179,7 @@ function confirm_manual_address() { function post_standardization() { -% if ( $conf->exists('enable_taxproducts') ) { +% if ( $need_tax_location ) { var cf = document.<% $formname %>; @@ -204,9 +204,8 @@ function post_standardization() { var state_el = cf.elements[prefix + 'state']; var state = state_el.options[ state_el.selectedIndex ].value; - var url = "<% $p %>/misc/choose_tax_location.html" + - "?data_vendor=cch-zip" + - ";city=" + cf.elements[prefix + 'city'].value + + var url = "<% $p %>/misc/choose_tax_location.html?" + + "city=" + cf.elements[prefix + 'city'].value + ";state=" + state + ";zip=" + cf.elements[prefix + 'zip'].value + ";country=" + country + @@ -252,11 +251,9 @@ function update_geocode() { prefix = 'bill_'; } - //alert(what.options[what.selectedIndex].value); - var argsHash = eval('(' + what.options[what.selectedIndex].value + ')'); - cf.elements[prefix + 'city'].value = argsHash['city']; - setselect(cf.elements[prefix + 'state'], argsHash['state']); - cf.elements[prefix + 'zip'].value = argsHash['zip']; +%# this used to set the city/state/zip to the selected value; I think +%# that's wrong. + var argsHash = JSON.parse(what.value); cf.elements[prefix + 'geocode'].value = argsHash['geocode']; <% $post_geocode %>; @@ -343,4 +340,7 @@ if ( $census_functions ) { $post_geocode = 'confirm_censustract()'; } +my $tax_engine = FS::TaxEngine->new; +my $need_tax_location = $tax_engine->info->{manual_tax_location} ? 1 : 0; + </%init> diff --git a/httemplate/elements/tr-select-tax_status.html b/httemplate/elements/tr-select-tax_status.html new file mode 100644 index 0000000..9c2de15 --- /dev/null +++ b/httemplate/elements/tr-select-tax_status.html @@ -0,0 +1,24 @@ +% if ( !$vendor ) { + + <INPUT TYPE="hidden" NAME="<% $opt{'element_name'} || $opt{'field'} || 'taxstatusnum' %>" VALUE=""> + +% } else { + + <& tr-select-table.html, + label => 'Customer tax status', + table => 'tax_status', + name_col => 'description', + hashref => { data_vendor => $vendor }, + order_by => 'order by taxstatus', + %opt + &> + +% } + +<%shared> +my $conf = FS::Conf->new; +my $vendor = $conf->config('enable_taxproducts'); +</%shared> +<%init> +my %opt = @_; +</%init> diff --git a/httemplate/elements/tr-select-taxoverride.html b/httemplate/elements/tr-select-taxoverride.html index e20d37e..ee3ee41 100644 --- a/httemplate/elements/tr-select-taxoverride.html +++ b/httemplate/elements/tr-select-taxoverride.html @@ -1,4 +1,4 @@ -% if ( $conf->exists('enable_taxproducts') ) { +% if ( FS::TaxEngine->new->info->{override} ) { <%include('tr-td-label.html', @_) %> <TD <% $cell_style %>><% include('select-taxoverride.html', @_) %></TD> </TR> @@ -9,8 +9,6 @@ <%init> -my $conf = new FS::Conf; - my %opt = @_; my $cell_style = $opt{'cell_style'}? 'STYLE="'. $opt{cell_style}. '"' : ''; my $name = $opt{element_name} || $opt{field} || 'tax_override'; diff --git a/httemplate/elements/tr-td-label.html b/httemplate/elements/tr-td-label.html index 8125541..c318722 100644 --- a/httemplate/elements/tr-td-label.html +++ b/httemplate/elements/tr-td-label.html @@ -15,5 +15,8 @@ $style .= '; '. $opt{'cell_style'} if $opt{'cell_style'}; my $required = $opt{'required'} ? '<font color="#ff0000">*</font> ' : ''; +if ($required) { + $style .= ';font-weight: bold'; +} </%init> diff --git a/httemplate/misc/choose_tax_location.html b/httemplate/misc/choose_tax_location.html index 23099c4..9c5881f 100644 --- a/httemplate/misc/choose_tax_location.html +++ b/httemplate/misc/choose_tax_location.html @@ -4,9 +4,8 @@ <SELECT NAME='geocodes' ID='geocodes' STYLE="<% $style %>"> % foreach my $location (@cust_tax_location) { -% my %value = ( zip => $zip5, -% map { $_ => $location->$_ } -% qw ( city state geocode ) +% my %value = ( map { $_ => $location->$_ } +% qw ( zip city state geocode ) % ); % map { $value{$_} = $location{$_} } qw ( city state ) % if $location{country} eq 'CA'; @@ -36,10 +35,10 @@ <%init> my $conf = new FS::Conf; +my $tax_engine = FS::TaxEngine->new; -my %location = (); - -($location{data_vendor}) = $cgi->param('data_vendor') =~ /^([-\w]+)$/; +my %location; +($location{data_vendor}) = $conf->config('enable_taxproducts'); ($location{city}) = $cgi->param('city') =~ /^([\w ]+)$/; ($location{state}) = $cgi->param('state') =~ /^(\w+)$/; ($location{zip}) = $cgi->param('zip') =~ /^([-\w ]+)$/; @@ -50,22 +49,7 @@ my($geocode) = $cgi->param('geocode') =~ /^([\w]+)$/; my($formname) = $cgi->param('formname') =~ /^([\w]*)$/; $formname ||= 'CustomerForm'; -my($zip5, $zip4) = split('-', $location{zip}); - -#only support US & CA -my $hashref = { 'data_vendor' => $location{data_vendor} }; -$hashref->{zip} = $location{country} eq 'CA' ? substr($zip5,0,1) : $zip5, - -my @keys = keys(%$hashref); -my @cust_tax_location = (); -until ( @cust_tax_location ) { - @cust_tax_location = qsearch({ table => 'cust_tax_location', - hashref => $hashref, - order_by => 'LIMIT 50', - }); - last unless scalar(@keys); - delete $hashref->{ shift @keys }; -} +my @cust_tax_location = $tax_engine->cust_tax_locations(\%location); my %max = ( city => 4, county => 6, state => 5); foreach my $location (@cust_tax_location) { diff --git a/httemplate/misc/tax-import.cgi b/httemplate/misc/tax-import.cgi index 25414b2..7e72c74 100644 --- a/httemplate/misc/tax-import.cgi +++ b/httemplate/misc/tax-import.cgi @@ -1,4 +1,4 @@ -<% include("/elements/header.html",'Batch Tax Rate Import') %> +<& /elements/header.html,'Batch Tax Rate Import' &> Import a CSV file set containing tax rate records. <BR><BR> @@ -6,22 +6,22 @@ Import a CSV file set containing tax rate records. <& /elements/form-file_upload.html, 'name' => 'TaxRateUpload', 'action' => 'process/tax-import.cgi', - 'num_files' => 6, 'fields' => [ 'format', 'reload' ], + 'num_files' => $vendor_info{$data_vendor}->{num_files}, 'message' => 'Tax rates imported', 'onsubmit' => "document.TaxRateUpload.submitButton.disabled=true;", &> -<% &ntable("#cccccc", 2) %> +<& /elements/table-grid.html &> <TR> <TH ALIGN="right">Format</TH> <TD> <SELECT NAME="format"> - <!-- <OPTION VALUE="cch-update" SELECTED>CCH update (CSV) --> - <OPTION VALUE="cch">CCH import (CSV) - <!-- <OPTION VALUE="cch-fixed-update">CCH update (fixed length) --> - <OPTION VALUE="cch-fixed">CCH import (fixed length) +% my @formats = @{ $vendor_info{$data_vendor}->{formats} }; +% while (@formats) { + <OPTION VALUE="<% shift @formats %>"><% shift @formats %></OPTION> +% } </SELECT> </TD> </TR> @@ -33,24 +33,11 @@ Import a CSV file set containing tax rate records. </TD> </TR> - <% include( '/elements/file-upload.html', - 'field' => [ 'geocodefile', - 'codefile', - 'plus4file', - 'zipfile', - 'txmatrixfile', - 'detailfile', - ], - 'label' => [ 'geocode filename', - 'code filename', - 'plus4 filename', - 'zip filename', - 'txmatrix filename', - 'detail filename', - ], + <& /elements/file-upload.html, + 'field' => $vendor_info{$data_vendor}->{field}, + 'label' => $vendor_info{$data_vendor}->{label}, 'debug' => 0, - ) - %> + &> <TR> <TD COLSPAN=2 ALIGN="center" STYLE="padding-top:6px"> @@ -72,4 +59,37 @@ Import a CSV file set containing tax rate records. die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Import'); +my $conf = FS::Conf->new; +my $data_vendor = $conf->config('enable_taxproducts'); + +my %vendor_info = ( + CCH => { + 'num_files' => 6, + 'formats' => [ 'cch' => 'CCH import (CSV)', + 'cch-fixed' => 'CCH import (fixed length)' ], + 'field' => [ 'geocodefile', + 'codefile', + 'plus4file', + 'zipfile', + 'txmatrixfile', + 'detailfile', + ], + 'label' => [ 'geocode filename', + 'code filename', + 'plus4 filename', + 'zip filename', + 'txmatrix filename', + 'detail filename', + ], + }, + Billsoft => { + 'num_files' => 1, + 'formats' => [ 'billsoft-pcode' => 'Billsoft PCodes', + 'billsoft-taxclass' => 'Tax classes', + 'billsoft-taxproduct' => 'Tax products' ], + 'field' => [ 'file' ], + 'label' => [ 'Filename' ], + }, +); + </%init> |