Cron routine to update city/district sales tax rates in I<cust_main_county>.
Currently supports sales tax in the state of Washington.
+=head2 wa_sales
+
+=item Tax Rate Download
+
+Once each month, update the tax tables from the WA DOR website.
+
+=item Customer Address Rate Classification
+
+Find cust_location rows in WA with no tax district. Try to determine
+a tax district. Otherwise, generate a log error that address needs
+to be correctd.
+
=cut
use strict;
use warnings;
-use FS::Conf;
-use FS::Record qw(qsearch qsearchs dbh);
-use FS::cust_main_county;
-use FS::part_pkg_taxclass;
+use feature 'state';
+
+use Exporter;
+our @EXPORT_OK = qw(
+ tax_rate_update
+ wa_sales_update_tax_table
+ wa_sales_log_customer_without_tax_district
+);
+
+use Carp qw(croak);
use DateTime;
-use LWP::UserAgent;
use File::Temp 'tempdir';
use File::Slurp qw(read_file write_file);
+use LWP::UserAgent;
+use Spreadsheet::XLSX;
use Text::CSV;
-use Exporter;
-our @EXPORT_OK = qw(tax_rate_update);
+use FS::Conf;
+use FS::cust_main;
+use FS::cust_main_county;
+use FS::geocode_Mixin;
+use FS::Log;
+use FS::part_pkg_taxclass;
+use FS::Record qw(qsearch qsearchs dbh);
+use FS::upgrade_journal;
+
our $DEBUG = 0;
+=head1 FUNCTIONS
+
+=head2 tax_rate_update
+
+Cron routine for freeside_daily.
+
+Run one of the available cron functions based on conf value tax_district_method
+
+=cut
+
sub tax_rate_update {
- my %opt = @_;
- my $oldAutoCommit = $FS::UID::AutoCommit;
- local $FS::UID::AutoCommit = 0;
- my $dbh = dbh;
-
- my $conf = FS::Conf->new;
- my $method = $conf->config('tax_district_method');
- return if !$method;
-
- my $taxname = $conf->config('tax_district_taxname') || '';
-
- FS::cust_main_county->lock_table;
- if ($method eq 'wa_sales') {
- # download the update file
- my $now = DateTime->now;
- my $yr = $now->year;
- my $qt = $now->quarter;
- my $file = "Rates${yr}Q${qt}.zip";
- my $url = 'http://dor.wa.gov/downloads/Add_Data/'.$file;
- my $dir = tempdir();
- chdir($dir);
- my $ua = LWP::UserAgent->new;
- warn "Downloading $url...\n" if $DEBUG;
- my $response = $ua->get($url);
- if ( ! $response->is_success ) {
- die $response->status_line;
- }
- write_file($file, $response->decoded_content);
+ # Currently only wa_sales is supported
+ my $tax_district_method = conf_tax_district_method();
+
+ return unless $tax_district_method;
+
+ if ( exists &{$tax_district_method} ) {
+ my $func = \&{$tax_district_method};
+ $func->();
+ } else {
+ my $log = FS::Log->new('tax_rate_update');
+ $log->error( "Unhandled tax_district_method($tax_district_method)" );
+ }
+
+}
+
+=head2 wa_sales
+
+Monthly: Update the complete WA state tax tables
+Every Run: Log errors for cust_location records without a district
+
+=cut
+
+sub wa_sales {
+
+ return
+ unless conf_tax_district_method()
+ && conf_tax_district_method() eq 'wa_sales';
+
+ my $dt_now = DateTime->now;
+ my $year = $dt_now->year;
+ my $quarter = $dt_now->quarter;
+
+ my $journal_label =
+ sprintf 'wa_sales_update_tax_table_%sQ%s', $year, $quarter;
+
+ unless ( FS::upgrade_journal->is_done( $journal_label ) ) {
+ local $@;
+
+ eval{ wa_sales_update_tax_table(); };
+ log_error_and_die( "Error updating tax tables: $@" )
+ if $@;
+ FS::upgrade_journal->set_done( $journal_label );
+ }
+
+ wa_sales_log_customer_without_tax_district();
+
+ '';
+
+}
- # parse it
- system('unzip', $file);
- $file =~ s/\.zip$/.csv/;
- if (! -f $file) {
- die "$file not found in zip archive.\n";
+=head2 wa_sales_log_customer_without_tax_district
+
+For any cust_location records
+* In WA state
+* Attached to non cancelled packages
+* With no tax district
+
+Classify the tax district for the record using the WA State Dept of
+Revenue API. If this fails, generate an error into system log so
+address can be corrected
+
+=cut
+
+sub wa_sales_log_customer_without_tax_district {
+
+ return
+ unless conf_tax_district_method()
+ && conf_tax_district_method() eq 'wa_sales';
+
+ my %qsearch_cust_location = (
+ table => 'cust_location',
+ select => '
+ cust_location.locationnum,
+ cust_location.custnum,
+ cust_location.address1,
+ cust_location.city,
+ cust_location.state,
+ cust_location.zip
+ ',
+ addl_from => '
+ LEFT JOIN cust_main USING (custnum)
+ LEFT JOIN cust_pkg ON cust_location.locationnum = cust_pkg.locationnum
+ ',
+ extra_sql => sprintf(q{
+ WHERE cust_location.state = 'WA'
+ AND (
+ cust_location.district IS NULL
+ or cust_location.district = ''
+ )
+ AND cust_pkg.pkgnum IS NOT NULL
+ AND (
+ cust_pkg.cancel > %s
+ OR cust_pkg.cancel IS NULL
+ )
+ }, time()
+ ),
+ );
+
+ for my $cust_location ( qsearch( \%qsearch_cust_location )) {
+ local $@;
+ log_info_and_warn(
+ sprintf
+ 'Attempting to classify district for cust_location ' .
+ 'locationnum(%s) address(%s)',
+ $cust_location->locationnum,
+ $cust_location->address1,
+ );
+
+ eval {
+ FS::geocode_Mixin::process_district_update(
+ 'FS::cust_location',
+ $cust_location->locationnum
+ );
+ };
+
+ if ( $@ ) {
+ # Error indicates a crash, not an error looking up district
+ # process_district_udpate will generate log messages for those errors
+ log_error_and_warn(
+ sprintf "Classify district error for cust_location(%s): %s",
+ $cust_location->locationnum,
+ $@
+ );
}
- open my $fh, '<', $file
- or die "couldn't open $file: $!\n";
- my $csv = Text::CSV->new;
- my $header = $csv->getline($fh);
- $csv->column_names(@$header);
- # columns we care about are headed 'Code' and 'Rate'
-
- my $total_changed = 0;
- my $total_skipped = 0;
- while ( !$csv->eof ) {
- my $line = $csv->getline_hr($fh);
- my $district = $line->{Code} or next;
- $district = sprintf('%04d', $district);
- my $tax = sprintf('%.1f', $line->{Rate} * 100);
- my $changed = 0;
- my $skipped = 0;
- # find rate(s) in this country+state+district+taxclass that have the
- # wa_sales flag and the configured taxname, and haven't been disabled.
- my @rates = qsearch('cust_main_county', {
- country => 'US',
- state => 'WA', # this is specific to WA
- district => $district,
- taxname => $taxname,
- source => 'wa_sales',
- tax => { op => '>', value => '0' },
- });
- foreach my $rate (@rates) {
- if ( $rate->tax == $tax ) {
- $skipped++;
- } else {
- $rate->set('tax', $tax);
- my $error = $rate->replace;
- die "error updating district $district: $error\n" if $error;
- $changed++;
+
+ sleep 1; # Be polite to WA DOR API
+ }
+
+ for my $cust_location ( qsearch( \%qsearch_cust_location )) {
+ log_error_and_warn(
+ sprintf
+ "Customer address in WA lacking tax district classification. ".
+ "custnum(%s) ".
+ "locationnum(%s) ".
+ "address(%s, %s %s, %s) ".
+ "[https://webgis.dor.wa.gov/taxratelookup/SalesTax.aspx]",
+ map { $cust_location->$_ }
+ qw( custnum locationnum address1 city state zip )
+ );
+ }
+
+}
+
+
+=head2 wa_sales_update_tax_table \%args
+
+Update city/district sales tax rates in L<FS::cust_main_county> from the
+Washington State Department of Revenue published data files.
+
+Creates, or updates, a L<FS::cust_main_county> row for every tax district
+in Washington state. Some cities have different tax rates based on the
+address, within the city. Because of this, some cities have multiple
+districts.
+
+If tax classes are enabled, a row is created in every tax class for
+every district.
+
+Customer addresses aren't classified into districts here. Instead,
+when a Washington state address is inserted or changed in L<FS::cust_location>,
+a job is queued for FS::geocode_Mixin::process_district_update, to ask the
+Washington state API which tax district to use for this address.
+
+All arguments are optional:
+
+ filename: Skip file download, and process the specified filename instead
+
+ taxname: Updated or created records will be set to the given tax name.
+ If not specified, conf value 'tax_district_taxname' is used
+
+ year: Specify year for tax table download. Defaults to current year
+
+ quarter: Specify quarter for tax table download. Defaults to current quarter
+
+=head3 Washington State Department of Revenue Resources
+
+The state of Washington makes data files available via their public website.
+It's possible the availability or format of these files may change. As of now,
+the only data file that contains both city and county names is published in
+XLSX format.
+
+=over 4
+
+=item WA Dept of Revenue
+
+https://dor.wa.gov
+
+=item Data file downloads
+
+https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
+
+=item XLSX file example
+
+https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
+
+=item CSV file example
+
+https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
+
+
+=item Address lookup API tool
+
+http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
+
+=back
+
+=cut
+
+sub wa_sales_update_tax_table {
+ my $args = shift;
+
+ croak 'wa_sales_update_tax_table requires \$args hashref'
+ if $args && !ref $args;
+
+ return
+ unless conf_tax_district_method()
+ && conf_tax_district_method() eq 'wa_sales';
+
+ $args->{taxname} ||= FS::Conf->new->config('tax_district_taxname');
+ $args->{year} ||= DateTime->now->year;
+ $args->{quarter} ||= DateTime->now->quarter;
+
+ log_info_and_warn(
+ "Begin wa_sales_update_tax_table() ".
+ join ', ' => (
+ map{ "$_ => ". ( $args->{$_} || 'undef' ) }
+ sort keys %$args
+ )
+ );
+
+ $args->{temp_dir} ||= tempdir();
+
+ $args->{filename} ||= wa_sales_fetch_xlsx_file( $args );
+
+ $args->{tax_districts} = wa_sales_parse_xlsx_file( $args );
+
+ wa_sales_update_cust_main_county( $args );
+
+ log_info_and_warn( 'Finished wa_sales_update_tax_table()' );
+}
+
+=head2 wa_sales_update_cust_main_county \%args
+
+Create or update the L<FS::cust_main_county> records with new data
+
+=cut
+
+sub wa_sales_update_cust_main_county {
+ my $args = shift;
+
+ return
+ unless conf_tax_district_method()
+ && conf_tax_district_method() eq 'wa_sales';
+
+ croak 'wa_sales_update_cust_main_county requires $args hashref'
+ unless ref $args
+ && ref $args->{tax_districts};
+
+ my $insert_count = 0;
+ my $update_count = 0;
+ my $same_count = 0;
+
+ # Work within a SQL transaction
+ local $FS::UID::AutoCommit = 0;
+
+ for my $taxclass ( FS::part_pkg_taxclass->taxclass_names ) {
+ $taxclass ||= undef; # trap empty string when taxclasses are disabled
+
+ my %cust_main_county =
+ map { $_->district => $_ }
+ qsearch(
+ cust_main_county => {
+ district => { op => '!=', value => undef },
+ state => 'WA',
+ country => 'US',
+ source => 'wa_sales',
+ taxclass => $taxclass,
}
- } # foreach $taxclass
- print "$district: updated $changed, skipped $skipped\n"
- if $DEBUG and ($changed or $skipped);
- $total_changed += $changed;
- $total_skipped += $skipped;
+ );
+
+ for my $district ( @{ $args->{tax_districts} } ) {
+ if ( my $row = $cust_main_county{ $district->{district} } ) {
+
+ # District already exists in this taxclass, update if necessary
+ #
+ # If admin updates value of conf tax_district_taxname, instead of
+ # creating an entire separate set of tax rows with
+ # the new taxname, update the taxname on existing records
+
+ {
+ # Supress warning on taxname comparison, when taxname is undef
+ no warnings 'uninitialized';
+
+ if (
+ $row->tax == ( $district->{tax_combined} * 100 )
+ && $row->taxname eq $args->{taxname}
+ && uc $row->county eq uc $district->{county}
+ && uc $row->city eq uc $district->{city}
+ ) {
+ $same_count++;
+ next;
+ }
+ }
+
+ $row->city( uc $district->{city} );
+ $row->county( uc $district->{county} );
+ $row->taxclass( $taxclass );
+ $row->taxname( $args->{taxname} || undef );
+ $row->tax( $district->{tax_combined} * 100 );
+
+ if ( my $error = $row->replace ) {
+ dbh->rollback;
+ local $FS::UID::AutoCommit = 1;
+ log_error_and_die(
+ sprintf
+ "Error updating cust_main_county row %s for district %s: %s",
+ $row->taxnum,
+ $district->{district},
+ $error
+ );
+ }
+
+ $update_count++;
+
+ } else {
+
+ # District doesn't exist, create row
+
+ my $row = FS::cust_main_county->new({
+ district => $district->{district},
+ city => uc $district->{city},
+ county => uc $district->{county},
+ state => 'WA',
+ country => 'US',
+ taxclass => $taxclass,
+ taxname => $args->{taxname} || undef,
+ tax => $district->{tax_combined} * 100,
+ source => 'wa_sales',
+ });
+
+ if ( my $error = $row->insert ) {
+ dbh->rollback;
+ local $FS::UID::AutoCommit = 1;
+ log_error_and_die(
+ sprintf
+ "Error inserting cust_main_county row for district %s: %s",
+ $district->{district},
+ $error
+ );
+ }
+
+ $cust_main_county{ $district->{district} } = $row;
+ $insert_count++;
+ }
+
+ } # /foreach $district
+ } # /foreach $taxclass
+
+ dbh->commit;
+
+ local $FS::UID::AutoCommit = 1;
+ log_info_and_warn(
+ sprintf
+ "WA tax table update completed. ".
+ "Inserted %s rows, updated %s rows, identical %s rows",
+ $insert_count,
+ $update_count,
+ $same_count
+ );
+
+}
+
+=head2 wa_sales_parse_xlsx_file \%args
+
+Parse given XLSX file for tax district information
+Return an arrayref of district information hashrefs
+
+=cut
+
+sub wa_sales_parse_xlsx_file {
+ my $args = shift;
+
+ croak 'wa_sales_parse_xlsx_file requires $args hashref containing a filename'
+ unless ref $args
+ && $args->{filename};
+
+ # About the file format:
+ #
+ # The current spreadsheet contains the following @columns.
+ # Rows 1 and 2 are a marquee header
+ # Row 3 is the column labels. We will test these to detect
+ # changes in the data format
+ # Rows 4+ are the tax district data
+ #
+ # The "city" column is being parsed from "Location"
+
+ my @columns = qw( city county district tax_local tax_state tax_combined );
+
+ log_error_and_die( "Unable to access XLSX file: $args->{filename}" )
+ unless -r $args->{filename};
+
+ my $xls_parser = Spreadsheet::XLSX->new( $args->{filename} )
+ or log_error_and_die( "Error parsing XLSX file: $!" );
+
+ my $sheet = $xls_parser->{Worksheet}->[0]
+ or log_error_and_die(" Unable to access worksheet 1 in XLSX file" );
+
+ my $cells = $sheet->{Cells}
+ or log_error_and_die( "Unable to read cells in XLSX file" );
+
+ # Read the column labels and verify
+ my %labels =
+ map{ $columns[$_] => $cells->[2][$_]->{Val} }
+ 0 .. scalar(@columns)-1;
+
+ my %expected_labels = (
+ city => 'Location',
+ county => 'County',
+ district => 'Location Code',
+ tax_local => 'Local Rate',
+ tax_state => 'State Rate',
+ tax_combined => 'Combined Sales Tax',
+ );
+
+ if (
+ my @error_labels =
+ grep { lc $labels{$_} ne lc $expected_labels{$_} }
+ @columns
+ ) {
+ my $error = "Error parsing XLS file - ".
+ "Data format may have been updated with WA DOR! ";
+ $error .= "Expected column $expected_labels{$_}, found $labels{$_}! "
+ for @error_labels;
+ log_error_and_die( $error );
+ }
+
+ # Parse the rows into an array of hashes
+ my @districts;
+ for my $row ( 3..$sheet->{MaxRow} ) {
+ my %district = (
+ map { $columns[$_] => $cells->[$row][$_]->{Val} }
+ 0 .. scalar(@columns)-1
+ );
+
+ if (
+ $district{city}
+ && $district{county}
+ && $district{district} =~ /^\d+$/
+ && $district{tax_local} =~ /^\d?\.\d+$/
+ && $district{tax_state} =~ /^\d?\.\d+$/
+ && $district{tax_combined} =~ /^\d?\.\d+$/
+ ) {
+
+ # For some reason, city may contain line breaks!
+ $district{city} =~ s/[\r\n]//g;
+
+ push @districts, \%district;
+ } else {
+ log_warn_and_warn(
+ "Non-usable row found in spreadsheet:\n" . Dumper( \%district )
+ );
}
- print "Updated $total_changed tax rates.\nSkipped $total_skipped unchanged rates.\n" if $DEBUG;
- dbh->commit;
- } # else $method isn't wa_sales, no other methods exist yet
- '';
+
+ }
+
+ log_error_and_die( "No \@districts found in data file!" )
+ unless @districts;
+
+ log_info_and_warn(
+ sprintf "Parsed %s districts from data file", scalar @districts
+ );
+
+ \@districts;
+
}
+=head2 wa_sales_fetch_xlsx_file \%args
+
+Download data file from WA state DOR to temporary storage,
+return filename
+
+=cut
+
+sub wa_sales_fetch_xlsx_file {
+ my $args = shift;
+
+ return
+ unless conf_tax_district_method()
+ && conf_tax_district_method() eq 'wa_sales';
+
+ croak 'wa_sales_fetch_xlsx_file requires \$args hashref'
+ unless ref $args
+ && $args->{temp_dir};
+
+ my $url_base = 'https://dor.wa.gov'.
+ '/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx';
+
+ my $year = $args->{year} || DateTime->now->year;
+ my $quarter = $args->{quarter} || DateTime->now->quarter;
+ $year = substr( $year, 2, 2 ) if $year >= 1000;
+
+ my $fn = sprintf( 'ExcelLocalSlsUserates_%s_Q%s.xlsx', $year, $quarter );
+ my $url = "$url_base/$fn";
+
+ my $write_fn = "$args->{temp_dir}/$fn";
+
+ log_info_and_warn( "Begin download from url: $url" );
+
+ my $ua = LWP::UserAgent->new;
+ my $res = $ua->get( $url );
+
+ log_error_and_die( "Download error: ".$res->status_line )
+ unless $res->is_success;
+
+ local $@;
+ eval { write_file( $write_fn, $res->decoded_content ); };
+ log_error_and_die( "Problem writing download to disk: $@" )
+ if $@;
+
+ log_info_and_warn( "Temporary file: $write_fn" );
+ $write_fn;
+
+}
+
+sub log {
+ state $log = FS::Log->new('tax_rate_update');
+ $log;
+}
+
+sub log_info_and_warn {
+ my $log_message = shift;
+ warn "$log_message\n";
+ &log()->info( $log_message );
+}
+
+sub log_warn_and_warn {
+ my $log_message = shift;
+ warn "$log_message\n";
+ &log()->warn( $log_message );
+}
+
+sub log_error_and_die {
+ my $log_message = shift;
+ &log()->error( $log_message );
+ die( "$log_message\n" );
+}
+
+sub log_error_and_warn {
+ my $log_message = shift;
+ warn "$log_message\n";
+ &log()->error( $log_message );
+}
+
+sub conf_tax_district_method {
+ state $tax_district_method = FS::Conf->new->config('tax_district_method');
+ $tax_district_method;
+}
+
+
1;
use FS::Conf;
use FS::Log;
use Locale::Country;
+use XML::LibXML;
FS::UID->install_callback( sub {
$conf = new FS::Conf;
&$method($location);
}
+
+=head2 wa_sales location_hash
+
+Expects output of location_hash() as parameter
+
+Returns undef on error, or if tax rate cannot be found using given address
+
+Query the WA State Dept of Revenue API with an address, and return
+tax district information for that address.
+
+Documentation for the API can be found here:
+
+L<https://dor.wa.gov/find-taxes-rates/retail-sales-tax/destination-based-sales-tax-and-streamlined-sales-tax/wa-sales-tax-rate-lookup-url-interface>
+
+This API does not return consistent usable county names, as the county
+name may include appreviations or labels referring to PTBA (public transport
+benefit area) or CEZ (community empowerment zone). It's recommended to use
+the tool wa_tax_rate_update to fully populate the city/county/districts for
+WA state every financial quarter.
+
+Returns a hashref with the following keys:
+
+ - district the wa state tax district id
+ - tax the combined total tax rate, as a percentage
+ - city the API rate name
+ - county The API address PTBA
+ - state WA
+ - country US
+ - exempt_amount 0
+
+If api returns no district for address, generates system log error
+and returns undef
+
+=cut
+
sub wa_sales {
- my $location = shift;
- my $error = '';
- return '' if $location->{state} ne 'WA';
- my $return = { %$location };
- $return->{'exempt_amount'} = 0.00;
+ #
+ # no die():
+ # freeside-queued will issue dbh->rollback on die() ... this will
+ # also roll back system log messages about errors :/ freeside-queued
+ # doesn't propgate die messages into the system log.
+ #
- my $url = 'http://webgis2.dor.wa.gov/TaxRateLookup_AGS/TaxReport.aspx';
- my $ua = new LWP::UserAgent;
+ my $location_hash = shift;
+
+ # Return when called with pointless context
+ return
+ unless $location_hash
+ && ref $location_hash
+ && $location_hash->{state} eq 'WA'
+ && $location_hash->{address1}
+ && $location_hash->{zip}
+ && $location_hash->{city};
+
+ my $log = FS::Log->new('wa_sales');
+
+ warn "wa_sales() called with location_hash:\n".Dumper( $location_hash)."\n"
+ if $DEBUG;
+
+ my $api_url = 'http://webgis.dor.wa.gov/webapi/AddressRates.aspx';
+ my @api_response_codes = (
+ 'The address was found',
+ 'The address was not found, but the ZIP+4 was located.',
+ 'The address was updated and found, the user should validate the address record',
+ 'The address was updated and Zip+4 located, the user should validate the address record',
+ 'The address was corrected and found, the user should validate the address record',
+ 'Neither the address or ZIP+4 was found, but the 5-digit ZIP was located.',
+ 'The address, ZIP+4, and ZIP could not be found.',
+ 'Invalid Latitude/Longitude',
+ 'Internal error'
+ );
- my $delim = '<|>'; # yes, <|>
- my $year = (localtime)[5] + 1900;
- my $month = (localtime)[4] + 1;
- my @zip = split('-', $location->{zip});
-
- my @args = (
- 'TaxType=S', #sales; 'P' = property
- 'Src=0', #does something complicated
- 'TAXABLE=',
- 'Addr='.uri_escape($location->{address1}),
- 'City='.uri_escape($location->{city}),
- 'Zip='.$zip[0],
- 'Zip1='.($zip[1] || ''), #optional
- 'Year='.$year,
- 'SYear='.$year,
- 'Month='.$month,
- 'EMon='.$month,
+ my %get_query = (
+ output => 'xml',
+ addr => $location_hash->{address1},
+ city => $location_hash->{city},
+ zip => substr( $location_hash->{zip}, 0, 5 ),
+ );
+ my $get_string = join '&' => (
+ map{ sprintf "%s=%s", $_, uri_escape( $get_query{$_} ) }
+ keys %get_query
);
-
- my $query_string = join($delim, @args );
- $url .= "?$query_string";
- warn "\nrequest: $url\n\n" if $DEBUG > 1;
- my $res = $ua->request( GET( "$url?$query_string" ) );
+ my $prepared_url = "${api_url}?$get_string";
- warn $res->as_string
- if $DEBUG > 2;
+ warn "API call to URL: $prepared_url\n"
+ if $DEBUG;
- if ($res->code ne '200') {
- $error = $res->message;
- }
+ my $dom;
+ local $@;
+ eval { $dom = XML::LibXML->load_xml( location => $prepared_url ); };
+ if ( $@ ) {
+ my $error =
+ sprintf "Problem parsing XML from API URL(%s): %s",
+ $prepared_url, $@;
- my $content = $res->content;
- my $p = new HTML::TokeParser \$content;
- my $js = '';
- while ( my $t = $p->get_tag('script') ) {
- my $u = $p->get_token; #either enclosed text or the </script> tag
- if ( $u->[0] eq 'T' and $u->[1] =~ /tblSales/ ) {
- $js = $u->[1];
- last;
- }
+ $log->error( $error );
+ warn $error;
+ return;
}
- if ( $js ) { #found it
- # strip down to the quoted string, which contains escaped single quotes.
- $js =~ s/.*\('tblSales'\);c.innerHTML='//s;
- $js =~ s/(?<!\\)'.*//s; # (?<!\\) means "not preceded by a backslash"
- warn "\n\n innerHTML:\n$js\n\n" if $DEBUG > 2;
-
- $p = new HTML::TokeParser \$js;
- TD: while ( my $td = $p->get_tag('td') ) {
- while ( my $u = $p->get_token ) {
- next TD if $u->[0] eq 'E' and $u->[1] eq 'td';
- next if $u->[0] ne 'T'; # skip non-text
- my $text = $u->[1];
-
- if ( lc($text) eq 'location code' ) {
- $p->get_tag('td'); # skip to the next column
- undef $u;
- $u = $p->get_token until ($u->[0] || '') eq 'T'; # and then skip non-text
- $return->{'district'} = $u->[1];
- }
- elsif ( lc($text) eq 'total tax rate' ) {
- $p->get_tag('td');
- undef $u;
- $u = $p->get_token until ($u->[0] || '') eq 'T';
- $return->{'tax'} = $u->[1];
- }
- } # get_token
- } # TD
-
- # just to make sure
- if ( $return->{'district'} =~ /^\d+$/ and $return->{'tax'} =~ /^.\d+$/ ) {
- $return->{'tax'} *= 100; #percentage
- warn Dumper($return) if $DEBUG > 1;
- return $return;
- }
- else {
- $error = 'district code/tax rate not found';
- }
+
+ my ($res_root) = $dom->findnodes('/response');
+ my ($res_addressline) = $dom->findnodes('/response/addressline');
+ my ($res_rate) = $dom->findnodes('/response/rate');
+
+ my $res_code = $res_root->getAttribute('code')
+ if $res_root;
+
+ unless (
+ ref $res_root
+ && ref $res_addressline
+ && ref $res_rate
+ && $res_code <= 5
+ && $res_root->getAttribute('rate') > 0
+ ) {
+ my $error =
+ sprintf
+ "Problem querying WA DOR tax district - " .
+ "code( %s %s ) " .
+ "address( %s ) " .
+ "url( %s )",
+ $res_code || 'n/a',
+ $res_code ? $api_response_codes[$res_code] : 'n/a',
+ $location_hash->{address1},
+ $prepared_url;
+
+ $log->error( $error );
+ warn "$error\n";
+ return;
}
- else {
- $error = "failed to parse document";
+
+ my %response = (
+ exempt_amount => 0,
+ state => 'WA',
+ country => 'US',
+ district => $res_root->getAttribute('loccode'),
+ tax => $res_root->getAttribute('rate') * 100,
+ county => uc $res_addressline->getAttribute('ptba'),
+ city => uc $res_rate->getAttribute('name')
+ );
+
+ $response{county} =~ s/ PTBA//i;
+
+ if ( $DEBUG ) {
+ warn "XML document: $dom\n";
+ warn "API parsed response: ".Dumper( \%response )."\n";
}
- die "WA tax district lookup error: $error";
+ my $info_message =
+ sprintf
+ "Tax district(%s) selected for address(%s %s %s %s)",
+ $response{district},
+ $location_hash->{address1},
+ $location_hash->{city},
+ $location_hash->{state},
+ $location_hash->{zip};
+
+ $log->info( $info_message );
+ warn "$info_message\n"
+ if $DEBUG;
+
+ \%response;
+
}
###### USPS Standardization ######
'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt
'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt
'source', 'varchar', 'NULL', $char_d, '', '',
+ 'charge_prediscount', 'char', 'NULL', 1, '', '', # Y = charge this tax pre discount
],
'primary_key' => 'taxnum',
'unique' => [],
my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur
or next; # don't create zero-amount exemptions
+ ## re-add the discounted amount if the tax needs to be charged pre discount
+ if ($tax_object->charge_prediscount) {
+ my $discount_amount = 0;
+ foreach my $discount (@{$cust_bill_pkg->discounts}) {
+ $discount_amount += $discount->amount;
+ }
+ $taxable_charged += $discount_amount;
+ }
+
# XXX the following procedure should probably be in cust_bill_pkg
if ( $exempt_cust ) {
# (maybe we should be using FS::UI::Web::join_cust_main instead?)
$addl_from .= ' LEFT JOIN (select refnum, referral from part_referral) AS part_referral_x ON (cust_main.refnum = part_referral_x.refnum) ';
- my $count_query = "SELECT COUNT(*) FROM cust_main $addl_from $extra_sql";
-
my @select = (
'cust_main.custnum',
'cust_main.salesnum',
}
+ my $count_query = "SELECT COUNT(DISTINCT cust_main.custnum) FROM cust_main $addl_from $extra_sql";
+
if ($params->{'flattened_pkgs'}) {
#my $pkg_join = '';
use strict;
use vars qw( @EXPORT_OK $conf
- @cust_main_county %cust_main_county $countyflag $DEBUG $me); # $cityflag );
+ @cust_main_county %cust_main_county $countyflag ); # $cityflag );
use Exporter;
use FS::Record qw( qsearch qsearchs dbh );
use FS::cust_bill_pkg;
use FS::cust_tax_exempt_pkg;
use FS::upgrade_journal;
-$DEBUG = 0;
-$me = '[FS::cust_main_county]';
-
@EXPORT_OK = qw( regionselector );
@cust_main_county = ();
}
}
-=item process_edit_import
-
-=cut
-
-use Data::Dumper;
-sub process_edit_import {
- my $job = shift;
-
- my $opt = { 'table' => 'cust_main_county',
- 'params' => [], #required, apparantly
- 'formats' => { 'default' => [
- 'country',
- 'state',
- 'county',
- 'city',
- '', #tax class
- 'taxname',
- 'tax',
- 'old_tax', #old tax
- ] },
- 'format_headers' => { 'default' => 1, },
- 'format_types' => { 'default' => 'xls' },
- };
-
- #false laziness w/
- #FS::Record::process_batch_import( $job, $opt, @_ );
-
- my $table = $opt->{table};
- my @pass_params = @{ $opt->{params} };
- my %formats = %{ $opt->{formats} };
-
- my $param = shift;
- warn Dumper($param) if $DEBUG;
-
- my $files = $param->{'uploaded_files'}
- or die "No files provided.\n";
-
- my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
-
- my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
- my $file = $dir. $files{'file'};
-
- my $error =
- #false laziness w/
- #FS::Record::batch_import( {
- FS::cust_main_county::edit_import( {
- #class-static
- table => $table,
- formats => \%formats,
- format_types => $opt->{format_types},
- format_headers => $opt->{format_headers},
- format_sep_chars => $opt->{format_sep_chars},
- format_fixedlength_formats => $opt->{format_fixedlength_formats},
- #per-import
- job => $job,
- file => $file,
- #type => $type,
- format => $param->{format},
- params => { map { $_ => $param->{$_} } @pass_params },
- #?
- default_csv => $opt->{default_csv},
- } );
-
- unlink $file;
-
- die "$error\n" if $error;
-
-}
-
-=item edit_import
-
-=cut
-
-#false laziness w/ #FS::Record::batch_import, grep "edit_import" for differences
-#could be turned into callbacks or something
-use Text::CSV_XS;
-sub edit_import {
- my $param = shift;
-
- warn "$me edit_import call with params: \n". Dumper($param)
- if $DEBUG;
-
- my $table = $param->{table};
- my $formats = $param->{formats};
-
- my $job = $param->{job};
- my $file = $param->{file};
- my $format = $param->{'format'};
- my $params = $param->{params} || {};
-
- die "unknown format $format" unless exists $formats->{ $format };
-
- my $type = $param->{'format_types'}
- ? $param->{'format_types'}{ $format }
- : $param->{type} || 'csv';
-
- unless ( $type ) {
- if ( $file =~ /\.(\w+)$/i ) {
- $type = lc($1);
- } else {
- #or error out???
- warn "can't parse file type from filename $file; defaulting to CSV";
- $type = 'csv';
- }
- $type = 'csv'
- if $param->{'default_csv'} && $type ne 'xls';
- }
-
- my $header = $param->{'format_headers'}
- ? $param->{'format_headers'}{ $param->{'format'} }
- : 0;
-
- my $sep_char = $param->{'format_sep_chars'}
- ? $param->{'format_sep_chars'}{ $param->{'format'} }
- : ',';
-
- my $fixedlength_format =
- $param->{'format_fixedlength_formats'}
- ? $param->{'format_fixedlength_formats'}{ $param->{'format'} }
- : '';
-
- my @fields = @{ $formats->{ $format } };
-
- my $row = 0;
- my $count;
- my $parser;
- my @buffer = ();
- my @header = (); #edit_import
- if ( $type eq 'csv' || $type eq 'fixedlength' ) {
-
- if ( $type eq 'csv' ) {
-
- my %attr = ();
- $attr{sep_char} = $sep_char if $sep_char;
- $parser = new Text::CSV_XS \%attr;
-
- } elsif ( $type eq 'fixedlength' ) {
-
- eval "use Parse::FixedLength;";
- die $@ if $@;
- $parser = new Parse::FixedLength $fixedlength_format;
-
- } else {
- die "Unknown file type $type\n";
- }
-
- @buffer = split(/\r?\n/, slurp($file) );
- splice(@buffer, 0, ($header || 0) );
- $count = scalar(@buffer);
-
- } elsif ( $type eq 'xls' ) {
-
- eval "use Spreadsheet::ParseExcel;";
- die $@ if $@;
-
- eval "use DateTime::Format::Excel;";
- #for now, just let the error be thrown if it is used, since only CDR
- # formats bill_west and troop use it, not other excel-parsing things
- #die $@ if $@;
-
- my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($file);
-
- $parser = $excel->{Worksheet}[0]; #first sheet
-
- $count = $parser->{MaxRow} || $parser->{MinRow};
- $count++;
-
- $row = $header || 0;
-
- #edit_import - need some magic to parse the header
- if ( $header ) {
- my @header_row = @{ $parser->{Cells}[$0] };
- @header = map $_->{Val}, @header_row;
- }
-
- } else {
- die "Unknown file type $type\n";
- }
-
- #my $columns;
-
- local $SIG{HUP} = 'IGNORE';
- local $SIG{INT} = 'IGNORE';
- local $SIG{QUIT} = 'IGNORE';
- local $SIG{TERM} = 'IGNORE';
- local $SIG{TSTP} = 'IGNORE';
- local $SIG{PIPE} = 'IGNORE';
-
- my $oldAutoCommit = $FS::UID::AutoCommit;
- local $FS::UID::AutoCommit = 0;
- my $dbh = dbh;
-
- my $line;
- my $imported = 0;
- my( $last, $min_sec ) = ( time, 5 ); #progressbar foo
- while (1) {
-
- my @columns = ();
- if ( $type eq 'csv' ) {
-
- last unless scalar(@buffer);
- $line = shift(@buffer);
-
- $parser->parse($line) or do {
- $dbh->rollback if $oldAutoCommit;
- return "can't parse: ". $parser->error_input();
- };
- @columns = $parser->fields();
-
- } elsif ( $type eq 'fixedlength' ) {
-
- @columns = $parser->parse($line);
-
- } elsif ( $type eq 'xls' ) {
-
- last if $row > ($parser->{MaxRow} || $parser->{MinRow})
- || ! $parser->{Cells}[$row];
-
- my @row = @{ $parser->{Cells}[$row] };
- @columns = map $_->{Val}, @row;
-
- #my $z = 'A';
- #warn $z++. ": $_\n" for @columns;
-
- } else {
- die "Unknown file type $type\n";
- }
-
- #edit_import loop
-
- my %hash = %$params;
- my @later;
-
- foreach my $field ( @fields ) {
-
- my $value = shift @columns;
-
- if ( ref($field) eq 'CODE' ) {
- #&{$field}(\%hash, $value);
- push @later, $field, $value;
- } elsif ($field) { #edit_import
- $hash{$field} = $value if defined($value) && length($value);
- }
-
- }
-
- my $class = "FS::$table";
-
- my $record = $class->new( \%hash );
-
- while ( scalar(@later) ) {
- my $sub = shift @later;
- my $data = shift @later;
- &{$sub}($record, $data); #edit_import - don't have $conf
- }
-
- #edit_import update or insert, not just insert
- my $old = qsearchs({
- 'table' => $table,
- 'hashref' => { map { $_ => $record->$_() } qw(country state county city taxname) },
- });
-
- my $error;
- if ( $old ) {
- $record->taxnum($old->taxnum);
- $error = $record->replace($old)
- } else {
- $record->insert;
- }
-
- if ( $error ) {
- $dbh->rollback if $oldAutoCommit;
- return "can't insert record". ( $line ? " for $line" : '' ). ": $error";
- }
-
- $row++;
- $imported++;
-
- if ( $job && time - $min_sec > $last ) { #progress bar
- $job->update_statustext( int(100 * $imported / $count) );
- $last = time;
- }
-
- }
-
- $dbh->commit or die $dbh->errstr if $oldAutoCommit;;
-
- return "Empty file!" unless $imported || $param->{empty_ok};
-
- ''; #no error
-
-}
-
sub _upgrade_data {
my $class = shift;
# assume taxes in Washington with district numbers, and null name, or
9 => { map {$_=>1} 3 }, #labour day
10 => { map {$_=>1} 8 }, #thanksgiving
},
+ 2019 => { 2 => { map {$_=>1} 18 }, #family day
+ 4 => { map {$_=>1} 19 }, #good friday
+ 4 => { map {$_=>1} 22 }, #easter monday
+ 5 => { map {$_=>1} 20 }, #victoria day
+ 8 => { map {$_=>1} 5 }, #First Monday of August Civic Holiday
+ 9 => { map {$_=>1} 2 }, #labour day
+ 10 => { map {$_=>1} 14 }, #thanksgiving
+ },
+ 2020 => { 2 => { map {$_=>1} 17 }, #family day
+ 4 => { map {$_=>1} 10 }, #good friday
+ 4 => { map {$_=>1} 13 }, #easter monday
+ 5 => { map {$_=>1} 18 }, #victoria day
+ 8 => { map {$_=>1} 3 }, #First Monday of August Civic Holiday
+ 9 => { map {$_=>1} 7 }, #labour day
+ 10 => { map {$_=>1} 12 }, #thanksgiving
+ },
+ 2021 => { 2 => { map {$_=>1} 15 }, #family day
+ 4 => { map {$_=>1} 2 }, #good friday
+ 4 => { map {$_=>1} 5 }, #easter monday
+ 5 => { map {$_=>1} 24 }, #victoria day
+ 8 => { map {$_=>1} 2 }, #First Monday of August Civic Holiday
+ 9 => { map {$_=>1} 6 }, #labour day
+ 10 => { map {$_=>1} 11 }, #thanksgiving
+ },
+ 2022 => { 2 => { map {$_=>1} 21 }, #family day
+ 4 => { map {$_=>1} 15 }, #good friday
+ 4 => { map {$_=>1} 18 }, #easter monday
+ 5 => { map {$_=>1} 23 }, #victoria day
+ 8 => { map {$_=>1} 1 }, #First Monday of August Civic Holiday
+ 9 => { map {$_=>1} 5 }, #labour day
+ 10 => { map {$_=>1} 10 }, #thanksgiving
+ },
+ 2023 => { 2 => { map {$_=>1} 20 }, #family day
+ 4 => { map {$_=>1} 7 }, #good friday
+ 4 => { map {$_=>1} 10 }, #easter monday
+ 5 => { map {$_=>1} 22 }, #victoria day
+ 8 => { map {$_=>1} 7 }, #First Monday of August Civic Holiday
+ 9 => { map {$_=>1} 4 }, #labour day
+ 10 => { map {$_=>1} 9 }, #thanksgiving
+ },
+ 2024 => { 2 => { map {$_=>1} 19 }, #family day
+ 3 => { map {$_=>1} 29 }, #good friday
+ 4 => { map {$_=>1} 1 }, #easter monday
+ 5 => { map {$_=>1} 20 }, #victoria day
+ 8 => { map {$_=>1} 5 }, #First Monday of August Civic Holiday
+ 9 => { map {$_=>1} 2 }, #labour day
+ 10 => { map {$_=>1} 14 }, #thanksgiving
+ },
+ 2025 => { 2 => { map {$_=>1} 17 }, #family day
+ 4 => { map {$_=>1} 18 }, #good friday
+ 4 => { map {$_=>1} 21 }, #easter monday
+ 5 => { map {$_=>1} 19 }, #victoria day
+ 8 => { map {$_=>1} 4 }, #First Monday of August Civic Holiday
+ 9 => { map {$_=>1} 1 }, #labour day
+ 10 => { map {$_=>1} 13 }, #thanksgiving
+ },
);
sub is_holiday {
use Date::Parse 'str2time';
use Date::Format 'time2str';
use FS::UID qw(adminsuidsetup dbh);
+use FS::Log;
use FS::cdr;
use DBI;
use Getopt::Std;
$dsn .= ":database=$opt{D}"; # if $opt{D};
$dsn .= ";host=$opt{H}" if $opt{H};
-my $dbi = DBI->connect($dsn, $opt{U}, $opt{P})
- or die $DBI::errstr;
-
adminsuidsetup $user;
+my $log = FS::Log->new( 'freeside-cdr-asterisk_sql' );
+
+my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) ;
+
+if ( $dbi ) {
+ log_msg( info => "Established connection to CDR database at dsn($dsn)" );
+} else {
+ log_and_die( error =>
+ sprintf 'Fatal error connecting to CDR database at dsn(%s): %s',
+ $dsn,
+ $DBI::errstr
+ );
+}
+
my $fsdbh = FS::UID::dbh;
my $table = $opt{T} || 'cdr';
if ( $engine =~ /^mysql/ ) {
my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
if( ! @$status ) {
- warn "Adding freesidestatus column...\n" if $DEBUG;
+ log_msg( warn => "Adding freesidestatus column" );
$dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
- or die $dbi->errstr;
+ or log_and_die( error => $dbi->errstr );
} else {
- warn "freesidestatus column present\n" if $DEBUG;
+ log_msg( info => "freesidestatus column present" );
}
}
my $sql =
'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
my $sth = $dbi->prepare($sql);
-$sth->execute;
-warn "Importing ".$sth->rows." records...\n" if $DEBUG;
+$sth->execute
+ or log_and_die( error => $sth->errstr );
+
+log_msg( info => sprintf 'Importing %s records', $sth->rows );
my $cdr_batch = new FS::cdr_batch({
'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time),
});
-my $error = $cdr_batch->insert;
-die $error if $error;
+if ( my $error = $cdr_batch->insert ) {
+ log_and_die( error => $error );
+} else {
+ log_msg( info =>
+ sprintf 'cdrbatch %s %s',
+ $cdr_batch->cdrbatch,
+ $cdr_batch->cdrbatchnum
+ );
+}
+
my $cdrbatchnum = $cdr_batch->cdrbatchnum;
my $imports = 0;
$cdr->cdrbatchnum($cdrbatchnum);
- my $error = $cdr->insert;
- if ($error) {
- warn "failed import: $error\n";
+ if ( my $error = $cdr->insert ) {
+ log_msg( error =>
+ sprintf 'Non-fatal failure to import acctid(%s) from table(%s): %s',
+ $row->acctid,
+ $table,
+ $error
+ );
} else {
$imports++;
if ( $dbi->do($usql, @args) ) {
$updates++;
} else {
- warn "failed to set status: ".$dbi->errstr."\n";
+ log_msg( error =>
+ sprintf 'Non-fatal failure set status(done) acctid(%s) table(%s): %s',
+ $row->acctid,
+ $table,
+ $dbi->errstr
+ );
}
}
}
-warn "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
+log_and_warn(
+ info => "Done.\nImported $imports CDRs, marked $updates CDRs as done"
+);
+
$dbi->disconnect;
+sub log_and_die {
+ my ( $level, $message ) = @_;
+ $log->$level( $message );
+ die "[$level] $message\n";
+}
+
+sub log_msg {
+ my ( $level, $message ) = @_;
+ $log->$level( $message );
+ warn "[$level] $message\n"
+ if $opt{v};
+}
+
+sub log_and_warn {
+ my ( $level, $message ) = @_;
+ $log->$level( $message );
+ warn "$message\n";
+}
+
+
sub usage {
"Usage: \n freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]n\t-D database\n\t[ -T table ]\n\t[ -V asterisk_version]\n\t-U user\n\t-P password\n\tfreesideuser\n";
}
use FS::pay_batch;
use FS::cust_pay_batch;
use FS::Conf;
+use FS::Log;
use vars qw( $opt_v $opt_a );
getopts('va:');
my $user = shift or die &HELP_MESSAGE;
adminsuidsetup $user;
+my $log = FS::Log->new('freeside-eftca-download');
+log_info( "EFT Canada download started\n" );
+
if ( $opt_a ) {
- die "no such directory: $opt_a\n"
+ log_error_and_die( "no such directory: $opt_a\n" )
unless -d $opt_a;
- die "archive directory $opt_a is not writable by the freeside user\n"
- unless -w $opt_a;
+ log_error_and_die(
+ "archive directory $opt_a is not writable by the freeside user\n"
+ ) unless -w $opt_a;
}
#my $tmpdir = File::Temp->newdir();
if ( $conf->exists('batch-spoolagent') ) {
@batchconf = $conf->config('batchconfig-eft_canada', $agent->agentnum, 1);
if ( !length($batchconf[0]) ) {
- warn "agent '".$agent->agent."' has no batchconfig-eft_canada setting; skipped.\n";
+ log_info(
+ "agent '".$agent->agent.
+ "' has no batchconfig-eft_canada setting; skipped.\n"
+ );
next;
}
} else {
@batchconf = $conf->config('batchconfig-eft_canada');
}
# user, password, transaction code, delay days
- my $user = $batchconf[0] or die "no EFT Canada batch username configured\n";
- my $pass = $batchconf[1] or die "no EFT Canada batch password configured\n";
+ my $user = $batchconf[0]
+ or log_error_and_die( "no EFT Canada batch username configured\n" );
+ my $pass = $batchconf[1]
+ or log_error_and_die( "no EFT Canada batch password configured\n" );
my $host = 'ftp.eftcanada.com';
- print STDERR "Connecting to $user\@$host...\n" if $opt_v;
+ log_info( "Connecting to $user\@$host...\n" );
my $sftp = Net::SFTP::Foreign->new( host => $host,
user => $user,
password => $pass,
timeout => 30,
);
- die "failed to connect to '$user\@$host'\n(".$sftp->error.")\n" if $sftp->error;
+ log_error_and_die("failed to connect to '$user\@$host'\n(".$sftp->error.")\n")
+ if $sftp->error;
$sftp->setcwd('/Returns');
my $files = $sftp->ls('.', wanted => qr/\.txt$/, names_only => 1);
- die "no response files found\n" if !@$files;
+ log_info_and_die( "Finished: No response files found\n" )
+ if !@$files;
FILE: foreach my $filename (@$files) {
- print STDERR "Retrieving $filename\n" if $opt_v;
+ log_info( "Retrieving $filename\n" );
$sftp->get("$filename", "$tmpdir/$filename");
if($sftp->error) {
- warn "failed to download $filename\n";
+ log_info( "failed to download $filename\n" );
next FILE;
}
#move to server archive dir
$sftp->rename("$filename", "Archive/$filename");
if($sftp->error) {
- warn "failed to archive $filename on server\n";
+ log_info( "failed to archive $filename on server\n" );
} # process it anyway though
#copy to local archive dir
if ( $opt_a ) {
- print STDERR "Copying $tmpdir/$filename to archive dir $opt_a\n"
- if $opt_v;
+ log_info( "Copying $tmpdir/$filename to archive dir $opt_a\n" );
system 'cp', "$tmpdir/$filename", $opt_a;
- warn "failed to copy $tmpdir/$filename to $opt_a: $@" if $@;
+ log_info( "failed to copy $tmpdir/$filename to $opt_a: $@" )
+ if $@;
}
open my $fh, "<$tmpdir/$filename";
while (my $line = <$fh>) {
next if $line =~ /^\s*$/;
$csv->parse($line) or do {
- warn "can't parse $filename: ".$csv->error_input."\n";
+ log_info( "can't parse $filename: ".$csv->error_input."\n" );
next FILE; #parsing errors = reading the wrong kind of file
};
@hash{@fields} = $csv->fields();
- print STDERR "voiding paybatchnum#$hash{paybatchnum}\n" if $opt_v;
+ log_info( "voiding paybatchnum#$hash{paybatchnum}\n" );
my $cpb = qsearchs('cust_pay_batch',
{ paybatchnum => $hash{'paybatchnum'} });
if ( !$cpb ) {
- warn "can't find paybatchnum #$hash{paybatchnum} ($hash{first} $hash{last}, $hash{paid})\n";
+ log_info(
+ "can't find paybatchnum #$hash{paybatchnum} ".
+ "($hash{first} $hash{last}, $hash{paid})\n"
+ );
next;
}
my $error = $cpb->decline("Returned payment ($hash{returncode})");
if ( $error ) {
- warn "can't void paybatchnum #$hash{paybatchnum}: $error\n";
+ log_info( "can't void paybatchnum #$hash{paybatchnum}: $error\n" );
}
}
close $fh;
}
-print STDERR "Finished!\n" if $opt_v;
+log_info( "Finished!\n" );
+
+sub log_info {
+ my $log_message = shift;
+ $log->info( $log_message );
+ print STDERR $log_message if $opt_v;
+}
+
+sub log_info_and_die {
+ my $log_message = shift;
+ $log->info( $log_message );
+ die $log_message;
+}
+
+sub log_error_and_die {
+ my $log_message = shift;
+ $log->error( $log_message );
+ die $log_message;
+}
=head1 NAME
use FS::Record qw(qsearch qsearchs);
use FS::pay_batch;
use FS::Conf;
+use FS::Log;
use vars qw( $opt_a $opt_v );
getopts('av');
my $user = shift or die &HELP_MESSAGE;
adminsuidsetup $user;
+my $log = FS::Log->new('freeside-eftca-upload');
+log_info( "EFT Canada upload started\n" );
+
my @batches;
if($opt_a) {
@batches = qsearch('pay_batch', { 'status' => 'O', 'payby' => 'CHEK' })
- or die "No open batches found.\n";
+ or log_info_and_die( "Finished: No open batches found.\n" );
}
else {
my $batchnum = shift;
die &HELP_MESSAGE if !$batchnum;
@batches = qsearchs('pay_batch', { batchnum => $batchnum } );
- die "Can't find payment batch '$batchnum'\n" if !@batches;
+ log_error_and_die( "Can't find payment batch '$batchnum'\n" ) if !@batches;
}
my $conf = new FS::Conf;
my $batchnum = $pay_batch->batchnum;
my $filename = time2str('%Y%m%d', time) . '-' . sprintf('%06d.csv',$batchnum);
- print STDERR "Exporting batch $batchnum to $filename...\n" if $opt_v;
+ log_info( "Exporting batch $batchnum to $filename...\n" );
my $text = $pay_batch->export_batch(format => 'eft_canada');
unless ($text) {
- print STDERR "Batch is empty, resolving..." if $opt_v;
+ log_info( "Batch is empty, resolving..." );
next;
}
open OUT, ">$tmpdir/$filename";
close OUT;
my @batchconf = $conf->config('batchconfig-eft_canada', $pay_batch->agentnum);
- my $user = $batchconf[0] or die "no EFT Canada batch username configured\n";
- my $pass = $batchconf[1] or die "no EFT Canada batch password configured\n";
+ my $user = $batchconf[0]
+ or log_error_and_die( "no EFT Canada batch username configured\n" );
+ my $pass = $batchconf[1]
+ or log_error_and_die( "no EFT Canada batch password configured\n" );
my $host = 'ftp.eftcanada.com';
- print STDERR "Connecting to $user\@$host...\n" if $opt_v;
+ log_info( "Connecting to $user\@$host...\n" );
my $sftp = Net::SFTP::Foreign->new( host => $host,
user => $user,
password => $pass,
timeout => 30,
);
- die "failed to connect to '$user\@$host'\n(".$sftp->error.")\n"
+ log_error_and_die("failed to connect to '$user\@$host'\n(".$sftp->error.")\n")
if $sftp->error;
$sftp->put("$tmpdir/$filename", "$filename")
- or die "failed to upload file (".$sftp->error.")\n";
+ or log_error_and_die( "failed to upload file (".$sftp->error.")\n" );
undef $sftp; #$sftp->disconnect;
last if $error;
}
$error ||= $pay_batch->set_status('R');
- die "error closing batch $batchnum: $error\n\n" if $error;
+ log_error_and_die( "error closing batch $batchnum: $error\n\n" )
+ if $error;
+}
+
+log_info( "Finished!\n" );
+
+sub log_info {
+ my $log_message = shift;
+ $log->info( $log_message );
+ print STDERR $log_message if $opt_v;
}
-print STDERR "Finished!\n" if $opt_v;
+sub log_info_and_die {
+ my $log_message = shift;
+ $log->info( $log_message );
+ die $log_message;
+}
+
+sub log_error_and_die {
+ my $log_message = shift;
+ $log->error( $log_message );
+ die $log_message;
+}
=head1 NAME
my $name = $cf->{'name'};
$name = lc($name) unless driver_name =~ /^mysql/i;
- @statements = grep { $_ !~ /^\s*ALTER\s+TABLE\s+(h_|)$tbl\s+DROP\s+COLUMN\s+cf_$name\s*$/i }
+ @statements = grep { $_ !~ /^\s*ALTER\s+TABLE\s+(h_|)$tbl DROP\s+COLUMN\s+cf_$name/i }
@statements;
push @statements,
"ALTER TABLE $tbl ADD COLUMN cf_$name varchar(".$cf->{'length'}.")"
ln -sf ${FREESIDE_PATH}/FS/blib/lib/FS ${PERL_INC_DEV_KLUDGE}/FS
install-texmf:
+ mkdir -p /usr/local/share/texmf/tex/latex
install -D -o freeside -m 444 etc/*.sty \
/usr/local/share/texmf/tex/latex/
texhash /usr/local/share/texmf
-#!/usr/bin/perl
+#!/usr/bin/env perl
=head1 NAME
Tool to update city/district sales tax rates in I<cust_main_county> from
the Washington State Department of Revenue website.
-This does not handle address standardization or geocoding addresses to
-Washington tax district codes. That logic is still in FS::Misc::Geo,
-and relies on a heinous screen-scraping of the interactive search tool.
-This script just updates the cust_main_county records that already exist
-with the latest quarterly tax rates.
+Creates, or updates, a L<FS::cust_main_county> row for every tax district
+in Washington state. Some cities have different tax rates based on the
+address, within the city. Because of this, some cities have
+district.
+
+If tax classes are enabled, a row is created in every tax class for
+every district.
+
+Customer addresses aren't classified into districts here. Instead,
+when a Washington state address is inserted or changed in L<FS::cust_location>,
+a job is queued for FS::geocode_Mixin::process_district_update, to ask the
+Washington state API which tax district to use for this address.
Options:
--c <taxclass>: operate only on records with the named tax class. If not
-specified, this operates on records with null tax class.
+ -f <filename>: Skip downloading, and process the given excel file
+
+ -t <taxname>: Updated or created records will be set to the given tax name.
+ If not specified, conf value 'tax_district_taxname' will be used
+
+ -y <year>: Specify year for tax table - defaults to current year
+
+ -q <quarter>: Specify quarter for tax table - defaults to current quarter
+
+ -l <lookup>: Attempt to look up the tax district classification for
+ unclassified cust_location records in Washington. Will
+ notify of records that cannot be classified
+
+=head1 Washington State Department of Revenue Resources
+
+The state of Washington makes data files available via their public website.
+It's possible the availability or format of these files may change. As of now,
+the only data file that contains both city and county names is published in
+XLSX format.
+
+=item WA Dept of Revenue
+
+https://dor.wa.gov
--t <taxname>: operate only on records with that tax name. If not specified,
-it operates on records where the tax name is either null or 'Tax'.
+=item Data file downloads
+
+https://dor.wa.gov/find-taxes-rates/sales-and-use-tax-rates/downloadable-database
+
+=item XLSX file example
+
+https://dor.wa.gov/sites/default/files/legacy/Docs/forms/ExcsTx/LocSalUseTx/ExcelLocalSlsUserates_19_Q1.xlsx
+
+=item CSV file example
+
+https://dor.wa.gov/sites/default/files/legacy/downloads/Add_DataRates2018Q4.zip
+
+
+=item Address lookup API tool
+
+http://webgis.dor.wa.gov/webapi/AddressRates.aspx?output=xml&addr=410 Terry Ave. North&city=&zip=98100
=cut
-use FS::Record qw(qsearch qsearchs dbh);
-use FS::cust_main_county;
-use FS::UID qw(adminsuidsetup);
-use DateTime;
-use LWP::UserAgent;
-use File::Temp 'tempdir';
-use File::Slurp qw(read_file write_file);
-use Text::CSV;
+use strict;
+use warnings;
+
+our $VERSION = '0.02'; # Make Getopt:Std happy
+
use Getopt::Std;
-getopts('c:t:');
-my $user = shift or die usage();
-
-# download the update file
-my $now = DateTime->now;
-my $yr = $now->year;
-my $qt = $now->quarter;
-my $file = "Rates${yr}Q${qt}.zip";
-my $url = 'http://dor.wa.gov/downloads/Add_Data/'.$file;
-my $dir = tempdir();
-chdir($dir);
-my $ua = LWP::UserAgent->new;
-warn "Downloading $url...\n";
-my $response = $ua->get($url);
-if ( ! $response->is_success ) {
- die $response->status_line;
-}
-write_file($file, $response->decoded_content);
+use FS::Cron::tax_rate_update qw(
+ wa_sales_update_tax_table
+ wa_sales_log_customer_without_tax_district
+);
+use FS::Log;
+use FS::UID qw(adminsuidsetup);
-# parse it
-system('unzip', $file);
-$file =~ s/\.zip$/.csv/;
-if (! -f $file) {
- die "$file not found in zip archive.\n";
-}
-open my $fh, '<', $file
- or die "couldn't open $file: $!\n";
-my $csv = Text::CSV->new;
-my $header = $csv->getline($fh);
-$csv->column_names(@$header);
-# columns we care about are headed 'Code' and 'Rate'
-
-# connect to the DB
-adminsuidsetup($user) or die "bad username '$user'\n";
-$FS::UID::AutoCommit = 0;
-
-$opt_c ||= ''; # taxclass
-$opt_t ||= ''; # taxname
-my $total_changed = 0;
-my $total_skipped = 0;
-while ( !$csv->eof ) {
- my $line = $csv->getline_hr($fh);
- my $district = $line->{Code} or next;
- $district = sprintf('%04d', $district);
- my $tax = sprintf('%.1f', $line->{Rate} * 100);
- my $changed = 0;
- my $skipped = 0;
- # find all rates in WA
- my @rates = qsearch('cust_main_county', {
- country => 'US',
- state => 'WA', # this is specific to WA
- district => $district,
- taxclass => $opt_c,
- taxname => $opt_t,
- tax => { op => '>', value => '0' },
- });
- if ($opt_t eq '') {
- push @rates, qsearch('cust_main_county', {
- country => 'US',
- state => 'WA', # this is specific to WA
- district => $district,
- taxclass => $opt_c,
- taxname => 'Tax',
- tax => { op => '>', value => '0' },
+my %opts;
+getopts( 't:y:q:f:l', \%opts );
+
+my $user = shift
+ or die HELP_MESSAGE();
+
+adminsuidsetup( $user )
+ or die "bad username '$user'\n";
+
+my $log = FS::Log->new('wa_tax_rate_update');
+
+$log->info('Begin wa_tax_rate_update');
+
+{
+ local $@;
+ eval {
+ wa_sales_update_tax_table({
+ $opts{f} ? ( filename => $opts{f} ) : (),
+ $opts{t} ? ( taxname => $opts{t} ) : (),
+ $opts{y} ? ( year => $opts{y} ) : (),
+ $opts{q} ? ( quarter => $opts{q} ) : (),
});
+ };
+
+ if ( $@ ) {
+ $log->error( "Error: $@" );
+ warn "Error: $@\n";
+ } else {
+ $log->info( 'Finished wa_tax_rate_update' );
+ warn "Finished wa_tax_rate_update\n";
}
- foreach my $rate (@rates) {
- if ( $rate->tax == $tax ) {
- $skipped++;
- } else {
- $rate->set('tax', $tax);
- my $error = $rate->replace;
- die "error updating district $district: $error\n" if $error;
- $changed++;
- }
- }
- print "$district: updated $changed, skipped $skipped\n"
- if $changed or $skipped;
- $total_changed += $changed;
- $total_skipped += $skipped;
}
-print "Updated $total_changed tax rates.\nSkipped $total_skipped unchanged rates.\n";
-dbh->commit;
-sub usage {
- "usage:
- wa_tax_rate_update [ -c taxclass ] [ -t taxname ] user
-";
+
+if ( $opts{l} ) {
+ $log->info( 'Begin wa_sales_log_customer_without_tax_district' );
+
+ wa_sales_log_customer_without_tax_district();
+
+ $log->info( 'Finished wa_sales_log_customer_without_tax_district' );
+ warn "Finished wa_sales_log_customer_without_tax_district\n";
+}
+
+exit;
+
+sub HELP_MESSAGE {
+ print "
+ Tool to update city/district sales tax rates in I<cust_main_county> from
+ the Washington State Department of Revenue website.
+
+ Usage: wa_tax_rate_update [-f filename] [-t taxname] [-y year] [-q quarter] [-l] freeside_username
+
+ Optional Options:
+ -f filename Skip download, and process the specified filename
+ -t taxname Apply tax name value to created or updated records
+ defaults as conf value 'tax_district_taxname'
+ -y year Year for data file download
+ -q quarter Quarter of data file to download
+ -l lookup Try to fix cust_location records without a district
+
+ ";
+ exit;
}
+
libmap-splat-perl, libdatetime-format-ical-perl, librest-client-perl,
libgeo-streetaddress-us-perl, libbusiness-onlinepayment-perl,
libnet-vitelity-perl (>= 0.05), libnet-sslglue-perl, libexpect-perl,
- libspreadsheet-parsexlsx-perl, libunicode-truncate-perl (>= 0.303-1)
+ libspreadsheet-parsexlsx-perl, libunicode-truncate-perl (>= 0.303-1),
+ libspreadsheet-xlsx-perl
Conflicts: libparams-classify-perl (>= 0.013-6)
Replaces: freeside (<<4)
Breaks: freeside (<<4)
);
## Don't show CHEK or DCHK option if ACH is read only
- delete( $payby_index{'CHEK'} ) unless !$ach_read_only;
- delete( $payby_index{'DCHK'} ) unless !$ach_read_only;
+ delete( $payby_index{'CHEK'} ) if ($ach_read_only && $payby ne "CHEK");
+ delete( $payby_index{'DCHK'} ) if ($ach_read_only && $payby ne "DCHK");
tie my %options, 'Tie::IxHash', ();
foreach my $payby_option ( grep { exists( $payby_index{$_} ) } @paybys ) {
+<%=
+ $OUT = '';
+ if ($ach_read_only) {
+ $OUT .= qq!<TR><TD COLSPAN=2><FONT COLOR="red">You only have read only access</TD></TR>!;
+ }
+%>
<TR>
<TD ALIGN="right">Account type</TD>
<TD <%= ($ach_read_only ? ' BGCOLOR="#ffffff"' : '') %> >
}
$cgi->delete('county');
+my $city = '';
+if ( $country && $state && $county &&
+ $cgi->param('city') =~
+ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]]+)$/
+ )
+{
+ $city = $1;
+ if ( $city eq '__NONE__' ) {
+ $title = "No city, $title";
+ } else {
+ $title = "$city city, $title";
+ }
+}
+$cgi->delete('city');
+
$title = " for $title" if $title;
my $taxclass = '';
"window.location = '". $cgi->self_url.
";country=' + encodeURIComponent( document.getElementById('country').options[document.getElementById('country').selectedIndex].value ) + ".
"';state=' + encodeURIComponent( document.getElementById('state').options[document.getElementById('state').selectedIndex].value ) +".
- "';county=' + encodeURIComponent( document.getElementById('county').options[document.getElementById('county').selectedIndex].value );";
+ "';county=' + encodeURIComponent( document.getElementById('county').options[document.getElementById('county').selectedIndex].value )";
+
+$filter_change .= " +';city=' + encodeURIComponent( document.getElementById('city').options[document.getElementById('city').selectedIndex].value )"
+ if $conf->exists('enable_taxclasses');
+
+$filter_change .= ";";
#restore this so pagination works
$cgi->param('country', $country) if $country;
$cgi->param('state', $state ) if $state;
$cgi->param('county', $county ) if $county;
+$cgi->param('city', $city ) if $city;
$cgi->param('taxclass', $county ) if $taxclass;
my $html_posttotal =
'</SELECT>';
}
+if ( $conf->exists('enable_taxclasses') ) {
+ my @cities = ( $country && $state && $county ) ? cities($county, $state, $country) : ();
+ if ( scalar(@cities) > 1 ) {
+ $html_posttotal .=
+ ' show city: '.
+ include('/elements/select-city.html',
+ 'country' => $country,
+ 'state' => $state,
+ 'county' => $county,
+ 'city' => $city,
+ 'onchange' => $filter_change,
+ 'empty_label' => '(all)',
+ 'empty_data_label' => '(none)',
+ 'empty_data_value' => '__NONE__',
+ 'disable_empty' => 0,
+ 'disable_cityupdate' => 1,
+ );
+ } else {
+ $html_posttotal .=
+ '<SELECT NAME="city" ID="city" STYLE="display:none">'.
+ ' <OPTION VALUE="" SELECTED>'.
+ '</SELECT>';
+ }
+}
+
$html_posttotal .= ' )';
my $bulk_popup_link =
|
<A HREF="javascript:void(0);" onClick="bulkPopup('edit');">Bulk edit selected</A>
|
-<A HREF="${p}misc/tax_edit_excel.html",">bulk edit with excel file</A>
+<A HREF="javascript:void(0);" onClick="bulkPopup('edit_rate_only');">Bulk edit rate only selected</A>
END
my $hashref = {};
$count_query .= ' AND county = '. dbh->quote($county);
}
}
+if ( $city ) {
+ if ( $city eq '__NONE__' ) {
+ $hashref->{'city'} = '';
+ $count_query .= " AND ( city = '' OR city IS NULL ) ";
+ } else {
+ $hashref->{'city'} = $city;
+ $count_query .= ' AND city = '. dbh->quote($city);
+ }
+}
if ( $taxclass ) {
$hashref->{'taxclass'} = $taxclass;
$count_query .= ( $count_query =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
<FORM ACTION="<% popurl(1)."process/bulk-cust_main_county.html" %>" METHOD="POST">
<INPUT TYPE="hidden" NAME="action" VALUE="<% $action %>">
+<INPUT TYPE="hidden" NAME="rate_only" VALUE="<% $rate_only %>">
<INPUT TYPE="hidden" NAME="taxnum" VALUE="<% join(',', @taxnum) %>">
<TABLE BGCOLOR="#cccccc" BORDER=0 CELLSPACING=0>
</TR>
% }
-<% include('/elements/tr-input-text.html',
+% unless ($rate_only) {
+ <% include('/elements/tr-input-text.html',
'field' => 'taxname',
'label' => 'Tax name'
)
-%>
+ %>
+% }
<% include('/elements/tr-input-percentage.html',
'field' => 'tax',
)
%>
-<% include('/elements/tablebreak-tr-title.html', value=>'Exemptions' ) %>
+% unless ($rate_only) {
+ <% include('/elements/tablebreak-tr-title.html', value=>'Exemptions' ) %>
-<% include('/elements/tr-checkbox.html',
+ <% include('/elements/tr-checkbox.html',
'field' => 'setuptax',
'value' => 'Y',
'label' => 'This tax not applicable to setup fees',
)
-%>
+ %>
-<% include('/elements/tr-checkbox.html',
+ <% include('/elements/tr-checkbox.html',
'field' => 'recurtax',
'value' => 'Y',
'label' => 'This tax not applicable to recurring fees',
)
-%>
+ %>
-<% include('/elements/tr-input-money.html',
+ <% include('/elements/tr-input-money.html',
'field' => 'exempt_amount',
'label' => 'Monthly exemption per customer ($25 "Texas tax")',
)
-%>
+ %>
+% }
</TABLE>
or $m->comp('/elements/errorpage-popup.html', $cgi->param('error') || 'Nothing selected');
my @taxnum = split(',', $1);
-$cgi->param('action') =~ /^(add|edit)$/ or die "unknown action";
+$cgi->param('action') =~ /^(add|edit|edit_rate_only)$/ or die "unknown action";
my $action = $1;
+my $rate_only;
+if ($action eq "edit_rate_only") {
+ $action = "edit";
+ $rate_only = 1;
+}
my $title = "Bulk $action tax rate";
my @cust_main_county =
'setuptax' => 'This tax not applicable to setup fees',
'recurtax' => 'This tax not applicable to recurring fees',
'exempt_amount' => 'Monthly exemption per customer ($25 "Texas tax")',
+ 'charge_prediscount' => 'Charge this tax prior to any discounts',
},
'fields' => \@fields,
)
'taxname',
{ field=>'tax', type=>'percentage', },
+ { type=>'tablebreak-tr-title', value=>'Charging options' },
+ { field=>'charge_prediscount', type=>'checkbox', value=>'Y', },
+
{ type=>'tablebreak-tr-title', value=>'Exemptions' },
{ field=>'setuptax', type=>'checkbox', value=>'Y', },
{ field=>'recurtax', type=>'checkbox', value=>'Y', },
$cgi->param('action') =~ /^(add|edit)$/ or die "unknown action";
my $action = $1;
+my $rate_only = $cgi->param('rate_only') if $cgi->param('rate_only');
+
my $error = '';
foreach my $taxnum ( @taxnum ) {
if ( $action eq 'edit' || $cust_main_county->tax == 0 ) { #let's replace
- foreach (qw( taxname tax exempt_amount setuptax recurtax )) {
- $cust_main_county->set( $_ => scalar($cgi->param($_)) )
+ if ($rate_only) {
+ $cust_main_county->set( tax => scalar($cgi->param('tax')) );
+ }
+ else {
+ foreach (qw( taxname tax exempt_amount setuptax recurtax )) {
+ $cust_main_county->set( $_ => scalar($cgi->param($_)) )
+ }
}
$error = $cust_main_county->replace and last;
my $pre = $opt{'prefix'};
my $conf = new FS::Conf;
-# Using tax_district_method implies that there's not a preloaded city/county
-# tax district table.
-my $disable_select = 1 if $conf->config('tax_district_method');
+my $disable_select = 0;
$opt{'disable_empty'} = 1 unless exists($opt{'disable_empty'});
border: none;
}
+% if ( $opt{id} !~ /customer_/ ) {
+% # Fix for changes to how jQuery UI applies state classes
+
#<% $opt{id} %> .ui-state-active {
color: inherit;
background-color: transparent;
border-color: transparent;
}
+% }
+
#<% $opt{id} %> li {
float: left;
padding: .25em;
--- /dev/null
+<%doc>
+
+Example:
+
+ <& /elements/select-city.html,
+ #recommended
+ country => $current_country,
+ state => $current_state,
+ county => $current_county,
+ city => $current_city,
+
+ #optional
+ prefix => $optional_unique_prefix,
+ onchange => $javascript,
+ disabled => 0, #bool
+ disable_empty => 1, #defaults to 1, set to 0 to disable the empty option
+ empty_label => 'all', #label for empty option
+ style => [ 'attribute:value', 'another:value' ],
+ &>
+
+</%doc>
+% if ( $cityflag ) {
+
+ <% include('/elements/xmlhttp.html',
+ 'url' => $p.'misc/cities.cgi',
+ 'subs' => [ $pre. 'get_cities' ],
+ )
+ %>
+
+ <SCRIPT TYPE="text/javascript">
+
+ function opt(what,value,text) {
+ var optionName = new Option(text, value, false, false);
+ var length = what.length;
+ what.options[length] = optionName;
+ }
+
+ function <% $pre %>county_changed(what, callback) {
+
+ what.form.<% $pre %>city.disabled = 'disabled';
+
+ county = what.form.<% $pre %>county.options[what.form.<% $pre %>county.selectedIndex].value;
+ state = what.options[what.selectedIndex].value;
+ country = what.form.<% $pre %>country.options[what.form.<% $pre %>country.selectedIndex].value;
+
+ function <% $pre %>update_cities(cities) {
+
+ // blank the current city list
+ for ( var i = what.form.<% $pre %>city.length; i >= 0; i-- )
+ what.form.<% $pre %>city.options[i] = null;
+
+% unless ( $opt{disable_empty} ) {
+ opt( what.form.<% $pre %>city, '', <% $opt{empty_label} |js_string %> );
+% }
+
+ // add the new cities
+ var citiesArray = eval('(' + cities + ')' );
+ for ( var s = 0; s < citiesArray.length; s++ ) {
+ var cityLabel = citiesArray[s];
+ if ( cityLabel == "" )
+ cityLabel = '(n/a)';
+ opt(what.form.<% $pre %>city, citiesArray[s], cityLabel);
+ }
+
+ var cityFormLabel = document.getElementById('<% $pre %>citylabel');
+
+ if ( citiesArray.length > 1 ) {
+ what.form.<% $pre %>city.style.display = '';
+ if ( cityFormLabel ) {
+ //cityFormLabel.style.visibility = 'visible';
+ cityFormLabel.style.display = '';
+ }
+ } else {
+ what.form.<% $pre %>city.style.display = 'none';
+ if ( cityFormLabel ) {
+ //cityFormLabel.style.visibility = 'hidden';
+ cityFormLabel.style.display = 'none';
+ }
+ }
+
+ what.form.<% $pre %>city.disabled = '';
+
+ //run the callback
+ if ( callback != null ) {
+ callback();
+ } else {
+ <% $pre %>city_changed(what.form.<% $pre %>city);
+ }
+ }
+
+ // go get the new cities
+ <% $pre %>get_cities( state, country, <% $pre %>update_cities );
+
+ }
+
+ </SCRIPT>
+
+ <SELECT NAME = "<% $pre %>city"
+ ID = "<% $pre %>city"
+ onChange= "<% $onchange %>"
+ <% $opt{'disabled'} %>
+ <% $style %>
+ >
+
+% unless ( $opt{'disable_empty'} ) {
+ <OPTION VALUE="" <% $opt{county} eq '' ? 'SELECTED' : '' %>><% $opt{empty_label} %>
+% }
+
+% foreach my $city ( @cities ) {
+
+ <OPTION VALUE="<% $city |h %>"
+ <% $city eq $opt{'city'} ? 'SELECTED' : '' %>
+ ><% $city eq $opt{'empty_data_value'} ? $opt{'empty_data_label'} : $city %>
+
+% }
+
+ </SELECT>
+
+% } else {
+
+ <SCRIPT TYPE="text/javascript">
+ function <% $pre %>city_changed(what) {
+ }
+ </SCRIPT>
+
+ <SELECT NAME = "<% $pre %>city"
+ ID = "<% $pre %>city"
+ STYLE = "display:none"
+ >
+ <OPTION SELECTED VALUE="<% $opt{'city'} |h %>">
+ </SELECT>
+
+% }
+
+<%init>
+
+my %opt = @_;
+foreach my $opt (qw( city county state country prefix onchange disabled
+ empty_value )) {
+ $opt{$opt} = '' unless exists($opt{$opt}) && defined($opt{$opt});
+}
+
+$opt{'disable_empty'} = 1 unless exists($opt{'disable_empty'});
+
+my $pre = $opt{'prefix'};
+
+my $onchange = $opt{'onchange'};
+
+my $city_style = $opt{'style'} ? [ @{ $opt{'style'} } ] : [];
+
+my @cities = ();
+if ( $cityflag ) {
+
+ @cities = map { length($_) ? $_ : $opt{'empty_data_value'} }
+ cities( $opt{'county'}, $opt{'state'}, $opt{'country'} );
+
+ push @$city_style, 'display:none'
+ unless scalar(@cities) > 1;
+
+}
+
+my $style =
+ scalar(@$city_style)
+ ? 'STYLE="'. join(';', @$city_style). '"'
+ : '';
+
+</%init>
+<%once>
+
+my $sql = "SELECT COUNT(*) FROM cust_main_county".
+ " WHERE city IS NOT NULL AND city != ''";
+my $sth = dbh->prepare($sql) or die dbh->errstr;
+$sth->execute or die $sth->errstr;
+my $cityflag = $sth->fetchrow_arrayref->[0];
+
+</%once>
\ No newline at end of file
+++ /dev/null
-<% $server->process %>
-<%init>
-
-die "access denied"
- unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
-
-my $server = new FS::UI::Web::JSRPC 'FS::cust_main_county::process_edit_import', $cgi;
-
-</%init>
\ No newline at end of file
+++ /dev/null
-<% include('/elements/header.html', 'Edit tax rates with Excel' ) %>
-
-% # 'name' => 'RateImportForm',
-
-<& /elements/form-file_upload.html,
- 'name' => 'TaxEditForm',
- 'action' => 'process/tax_edit_excel.html',
- 'num_files' => 1,
- 'fields' => [ 'format' ],
- 'message' => 'Tax Rate edit successful',
- 'url' => $p."browse/cust_main_county.cgi",
- 'onsubmit' => "document.TaxEditForm.submitButton.disabled=true;"
-&>
-
-<% &ntable("#cccccc", 2) %>
-
- <TR>
- <TD ALIGN="left" COLSPAN=2>File format should be as follows:<BR>
- <TABLE>
- <TR><TD><B>Country</B> as standard two letter code</TD></TR>
- <TR><TD><B>State</B> as standard two letter code</TD></TR>
- <TR><TD><B>County name</B></TD></TR>
- <TR><TD><B>City name</B></TD></TR>
- <TR><TD><B>Tax name</B></TD></TR>
- <TR><TD><B>Tax rate</B></TD></TR>
- </TABLE><BR>
- * first row should be blank or contain headers<BR>
- * Tax rate should be formated as a number not percentage.
- <P>
- </TD>
- </TR>
- <TR>
- <TH ALIGN="left" COLSPAN=2>Upload tax rates with Excel (or other .XLS-compatible application)</TH>
- </TR>
-
-
- <% include( '/elements/file-upload.html',
- 'field' => 'file',
- 'label' => '',
- 'label_align' => 'left',
- )
- %>
-
- <INPUT TYPE="hidden" NAME="format" VALUE="default">
-
- <TR>
- <TD COLSPAN=2 ALIGN="center" STYLE="padding-top:6px">
- <INPUT TYPE = "submit"
- ID = "submitButton"
- NAME = "submitButton"
- VALUE = "Upload"
- >
- </TD>
- </TR>
-
-
-</TABLE>
-
-<% include('/elements/footer.html') %>
-<%init>
-
-die "access denied"
- unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
-
-my $sth = dbh->prepare('SELECT COUNT(*) FROM rate_detail WHERE conn_charge > 0 OR conn_sec > 0 LIMIT 1')
- or die dbh->errstr;
-$sth->execute or die $sth->errstr;
-my $have_conn = $sth->fetchrow_arrayref->[0];
-
-</%init>
\ No newline at end of file
projects / freeside.git / commitdiff