1 package FS::TaxEngine::billsoft;
4 use vars qw( $DEBUG $TIMEOUT %TAX_CLASSES );
5 use base 'FS::TaxEngine';
7 use FS::Record qw(qsearch qsearchs dbh);
10 use FS::upload_target;
11 use Date::Format qw( time2str );
13 use File::Copy qw(move);
15 use Locale::Country qw(country_code2code);
17 # "use constant" this, for performance?
60 $TIMEOUT = 86400; # absolute time limit on waiting for a response file.
62 FS::UID->install_callback(\&load_tax_classes);
67 manual_tax_location => 1,
71 sub add_sale { } #do nothing
74 $FS::UID::cache_dir . "/Billsoft";
79 my $spooldir = $self->spooldir;
80 mkdir $spooldir, 0700 unless -d $spooldir;
81 my $upload = $self->spooldir . '/upload';
82 mkdir $upload, 0700 unless -d $upload;
83 my $basename = $self->conf->config('billsoft-company_code') .
84 time2str('%Y%m%d', time); # use the real clock time here
86 while ( -e "$upload/$basename$uniq.CSV" ) {
88 # these two letters must be unique within each day
93 =item part_pkg_taxproduct PART_PKG, CLASSNUM
95 Returns the taxproduct string (T-code and S-code concatenated) for
96 PART_PKG with usage class CLASSNUM. CLASSNUM can be a numeric classnum,
97 an empty string (for the package's base taxproduct), 'setup', or 'recur'.
99 Returns undef if the package doesn't have a taxproduct.
103 sub part_pkg_taxproduct {
104 my ($self, $part_pkg, $classnum) = @_;
105 my $pkgpart = $part_pkg->get('pkgpart');
107 $self->{_taxproduct} ||= {};
108 # taxproduct(s) that are relevant to this package
109 my $pkg_taxproduct = $self->{_taxproduct}{$pkgpart} ||= {};
110 my $taxproduct; # return this
112 if (exists($pkg_taxproduct->{$classnum})) {
113 $taxproduct = $pkg_taxproduct->{$classnum};
115 my $part_pkg_taxproduct = $part_pkg->taxproduct($classnum);
116 $taxproduct = $pkg_taxproduct->{$classnum} = (
117 $part_pkg_taxproduct ? $part_pkg_taxproduct->taxproduct : undef
120 $self->log->error("part_pkg $pkgpart, class $_: taxproduct not found");
121 if ( !$self->conf->exists('ignore_incalculable_taxes') ) {
122 die "part_pkg $pkgpart, class $_: taxproduct not found\n";
126 warn "part_pkg $pkgpart, class $classnum: ".
128 "using taxproduct $taxproduct\n" :
129 "taxproduct not found\n")
136 return $self->{_log} ||= FS::Log->new('FS::TaxEngine::billsoft');
141 return $self->{_conf} ||= FS::Conf->new;
145 my ($self, %opt) = @_;
147 my @invoices = qsearch('cust_bill', { pending => 'Y' });
148 $self->log->info(scalar(@invoices)." pending invoice(s) found.");
149 return if @invoices == 0;
153 my $spooldir = $self->spooldir;
154 my $spoolname = $self->spoolname;
155 my $fh = IO::File->new();
156 $self->log->info("Starting batch in $spooldir/upload/$spoolname");
157 $fh->open("$spooldir/upload/$spoolname", '>');
160 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n" });
161 $csv->print($fh, \@input_cols);
162 $csv->column_names(\@input_cols);
164 # XXX limit based on freeside-daily custnum/agentnum options
165 # and maybe invoice date
166 foreach my $cust_bill (@invoices) {
168 my $invnum = $cust_bill->invnum;
169 my $cust_main = $cust_bill->cust_main;
170 my $cust_type = $cust_main->taxstatus;
171 my $invoice_date = time2str('%Y%m%d', $cust_bill->_date);
174 my $location = $cust_main->bill_location;
175 my $zip = $location->zip;
177 if ($location->country eq 'US') {
178 ($zip, $plus4) = split(/-/, $zip);
180 ( BillToCountryISO => uc(country_code2code($location->country,
181 'alpha-2' => 'alpha-3')),
182 BillToPCode => $location->geocode,
183 BillToZipCode => $zip,
184 BillToZipP4 => $plus4,
189 my (%cust_pkg, %part_pkg, %cust_location, %classname);
190 # keys are transaction codes (the first part of the taxproduct string)
191 # and then locationnums; for per-location taxes
194 my @options = $self->conf->config('billsoft-taxconfig');
196 my %bill_properties = (
198 Date => $invoice_date,
199 CustomerType => $cust_type,
200 CustomerNumber => $cust_bill->custnum,
201 InvoiceNumber => $invnum,
202 Facilities => ($options[0] || ''),
203 Franchise => ($options[1] || ''),
204 Regulated => ($options[2] || ''),
205 BusinessClass => ($options[3] || ''),
208 foreach my $cust_bill_pkg ( $cust_bill->cust_bill_pkg ) {
209 my $cust_pkg = $cust_pkg{$cust_bill_pkg->pkgnum}
210 ||= $cust_bill_pkg->cust_pkg;
211 my $pkgpart = $cust_bill_pkg->pkgpart_override || $cust_pkg->pkgpart;
212 my $part_pkg = $part_pkg{$pkgpart} ||= FS::part_pkg->by_key($pkgpart);
213 my $resale_mode = ($part_pkg->option('wholesale',1) ? 'Resale' : 'Sale');
214 my %pkg_properties = (
216 Sale => $resale_mode,
217 Optional => $cust_bill_pkg->billpkgnum, # will be echoed
218 # others at this level? Lifeline?
219 # DiscountType may be relevant...
225 # cursorized joined search on the invoice details, for memory efficiency
226 my $cdr_search = FS::Cursor->new({
228 'hashref' => { freesidestatus => 'done' },
229 'addl_from' => ' JOIN cust_bill_pkg_detail USING (detailnum)',
230 'extra_sql' => "AND cust_bill_pkg_detail.billpkgnum = ".
231 $cust_bill_pkg->billpkgnum
234 while (my $cdr = $cdr_search->fetch) {
235 my $classnum = $cdr->rated_classnum;
237 $classname{$classnum} ||= FS::usage_class->by_key($classnum)->classname;
240 my $taxproduct = $self->part_pkg_taxproduct($part_pkg, $classnum)
242 my $tcode = substr($taxproduct, 0, 6);
243 my $scode = substr($taxproduct, 6, 6);
245 # For CDRs, use the call termination site rather than setting
246 # Termination fields to the service address.
247 $csv->print_hr($fh, {
249 RequestType => 'CalcTaxes',
250 OriginationNpaNxx => substr($cdr->src_lrn || $cdr->src, 0, 6),
251 TerminationNpaNxx => substr($cdr->dst_lrn || $cdr->dst, 0, 6),
252 TransactionType => $tcode,
253 ServiceType => $scode,
254 Charge => $cdr->rated_price,
255 Minutes => ($cdr->duration / 60.0), # floating point
258 $usage_total += $cdr->rated_price;
260 } # while $cdr = $cdr_search->fetch
263 # now write lines for the non-CDR portion of the charges
265 my $locationnum = $cust_pkg->locationnum;
267 # use termination address for the service location
268 my %termination = do {
269 my $location = $cust_location{$locationnum} ||= $cust_pkg->cust_location;
270 my $zip = $location->zip;
272 if ($location->country eq 'US') {
273 ($zip, $plus4) = split(/-/, $zip);
275 ( TerminationCountryISO => uc(country_code2code($location->country,
276 'alpha-2' => 'alpha-3')),
277 TerminationPCode => $location->geocode,
278 TerminationZipCode => $zip,
279 TerminationZipP4 => $plus4,
283 foreach (qw(setup recur)) {
284 my $taxproduct = $self->part_pkg_taxproduct($part_pkg, $_);
285 next unless $taxproduct;
287 my $tcode = substr($taxproduct, 0, 6);
288 my $scode = substr($taxproduct, 6, 6);
289 $sales{$tcode} ||= 0;
290 $recur_tcode = $tcode if $_ eq 'recur';
292 my $price = $cust_bill_pkg->get($_);
293 $sales{$tcode} += $price;
295 $price -= $usage_total if $_ eq 'recur';
297 $csv->print_hr($fh, {
300 RequestType => 'CalcTaxes',
301 TransactionType => $tcode,
302 ServiceType => $scode,
306 } # foreach (setup, recur)
308 # S-code 21: taxes based on number of lines (E911, mostly)
309 # voip_cdr and voip_inbound packages know how to report this. Not all
310 # T-codes are eligible for this; only report it if the /21 taxproduct
313 # (note: the nomenclature of "service" and "transaction" codes is
314 # backward from the way most people would use the terms. you'd think
315 # that in "cellular activation", "cellular" would be the service and
316 # "activation" would be the transaction, but for Billsoft it's the
317 # reverse. I recommend calling them "S" and "T" codes internally just
318 # to avoid confusion.)
321 # XXX this isn't precisely correct. Local exchange service on
322 # high-capacity trunks, Centrex, and PBX trunks are supposed to be
323 # reported as three separate implicit transactions: number of trunks,
324 # of outbound channels, of extensions.
325 # This is also true for VoIP PBX trunks. Come back to this.
326 if ( $recur_tcode ) {
327 my $lines_taxproduct = FS::part_pkg_taxproduct->count(
328 'data_vendor = \'billsoft\' and taxproduct = ?',
329 sprintf('%06d%06d', $recur_tcode, 21)
331 my $lines = $cust_bill_pkg->units;
333 if ( $lines_taxproduct and $lines ) {
334 $csv->print_hr($fh, {
337 RequestType => 'CalcTaxes',
338 TransactionType => $recur_tcode,
346 } # foreach my $cust_bill_pkg
348 foreach my $tcode (keys %sales) {
350 # S-code 43: per-invoice tax (apparently this is a thing)
351 my $invoice_taxproduct = FS::part_pkg_taxproduct->count(
352 'data_vendor = \'billsoft\' and taxproduct = ?',
353 sprintf('%06d%06d', $tcode, 43)
355 if ( $invoice_taxproduct ) {
356 $csv->print_hr($fh, {
357 RequestType => 'CalcTaxes',
359 TransactionType => $tcode,
365 } # foreach $cust_bill
371 sub cust_tax_locations {
373 my $location = shift;
374 if (ref $location eq 'HASH') {
375 $location = FS::cust_location->new($location);
377 my $zip = $location->zip;
378 return () unless $location->country eq 'US';
379 return () unless $zip;
380 # currently the only one supported
381 if ( $zip =~ /^(\d{5})(-\d{4})?$/ ) {
384 die "bad zip code $zip";
387 table => 'cust_tax_location',
388 hashref => { 'data_vendor' => 'billsoft' },
389 extra_sql => " AND ziplo <= '$zip' and ziphi >= '$zip'",
390 order_by => ' ORDER BY default_location',
395 my ($self, %opt) = @_;
397 my $oldAutoCommit = $FS::UID::AutoCommit;
398 local $FS::UID::AutoCommit = 0;
401 eval "use Net::FTP;";
402 # set up directories if they're not already
403 mkdir $self->spooldir unless -d $self->spooldir;
404 local $CWD = $self->spooldir;
405 foreach (qw(upload download)) {
406 mkdir $_ unless -d $_;
408 my $target = qsearchs('upload_target', { hostname => 'ftp.billsoft.com' })
409 or die "No Billsoft upload target defined.\n";
411 local $CWD = $self->spooldir . '/upload';
413 my $upload = $self->create_batch(%opt); # name of the CSV file
414 # returns undef if there were no pending invoices; in that case
415 # skip the rest of this procedure
419 my $ftp = $target->connect;
420 if (!ref $ftp) { # it's an error message
421 die "Error connecting to Billsoft FTP server:\n$ftp\n";
423 my $fh = IO::File->new();
424 $self->log->info("Processing: $upload");
425 if ( stat('FTP.ZIP') ) {
426 unlink('FTP.ZIP') or die "Failed to remove old tax batch:\n$!\n";
428 my $error = system("zip -j -o FTP.ZIP $upload");
429 die "Failed to compress tax batch\n$!\n" if $error;
430 $self->log->debug("Uploading file");
431 $ftp->put('FTP.ZIP');
434 local $CWD = $self->spooldir;
435 my $download = $upload;
436 # naming convention for these is: same as the CSV contained in the
437 # zip file, but with an "R" inserted after the company ID prefix
438 $download =~ s/^(...)(\d{8}..).CSV/$1R$2.ZIP/;
439 $self->log->debug("Waiting for output file ($download)");
440 my $starttime = time;
442 while ( time - $starttime < $TIMEOUT ) {
443 my @ls = $ftp->ls($download);
445 if ($ftp->get($download, "download/$download")) {
446 $self->log->debug("Downloaded '$download'");
450 $self->log->warn("Failed to download '$download': ".$ftp->message);
451 # We know the file exists, so continue trying to download it.
452 # Maybe the problem will get fixed.
458 $self->log->error("No output file received.");
461 $self->log->debug("Decompressing...");
462 system("unzip -o download/$download");
463 my $output = $upload;
464 $output =~ s/.CSV$/_dtl.rpt.csv/i;
465 if ([ -f $output ]) {
466 $self->log->info("Processing '$output'");
467 $fh->open($output, '<') or die "failed to open downloaded file $output";
468 $self->batch_import($fh); # dies on error
470 unlink $output unless $DEBUG;
473 $dbh->commit if $oldAutoCommit;
478 $DB::single = 1; # XXX
480 my ($self, $fh) = @_;
481 $self->{'custnums'} = {};
482 $self->{'cust_bill'} = {};
484 # gather up pending invoices
485 foreach my $cust_bill (qsearch('cust_bill', { pending => 'Y' })) {
486 $self->{'cust_bill'}{ $cust_bill->invnum } = $cust_bill;
490 my $parser = Text::CSV_XS->new({binary => 1});
491 # set column names from header row
492 $parser->column_names($parser->getline($fh));
494 # start parsing the file
497 # the file is functionally a left join of submitted line items with their
498 # taxes; if a line item has no taxes then it will produce an output row
499 # with all the tax fields empty.
500 while ($href = $parser->getline_hr($fh)) {
501 next if $href->{TaxTypeID} eq ''; # then this row has no taxes
502 next if $href->{TaxAmount} == 0; # then the calculated tax is zero
504 my $billpkgnum = $href->{Optional};
505 my $invnum = $href->{InvoiceNumber};
506 my $cust_bill_pkg; # the line item that this tax applies to
507 if ( !exists($self->{cust_bill}->{$invnum}) ) {
508 $self->log->error("invoice #$invnum invoice not in pending state");
513 $cust_bill_pkg = FS::cust_bill_pkg->by_key($billpkgnum);
514 if ( $cust_bill_pkg->invnum != $invnum ) {
515 $self->log->error("invoice #$invnum invoice number mismatch");
520 $cust_bill_pkg = ($self->{cust_bill}->{$invnum}->cust_bill_pkg)[0];
521 $billpkgnum = $cust_bill_pkg->billpkgnum;
524 # resolve the tax definition
525 # base name of the tax type (like "Sales Tax" or "Universal Lifeline
526 # Telephone Service Charge").
527 my $tax_class = $TAX_CLASSES{ $href->{TaxTypeID} };
529 $self->log->warn("Unknown tax type $href->{TaxTypeID}");
530 $tax_class = FS::tax_class->new({
531 'data_vendor' => 'billsoft',
532 'taxclass' => $href->{TaxTypeID},
533 'description' => $href->{TaxType}
535 my $error = $tax_class->insert;
537 $self->log->error("Failed to insert tax_class record: $error");
541 $TAX_CLASSES{ $href->{TaxTypeID} } = $tax_class;
543 my $itemdesc = uc($tax_class->description);
544 my $location = qsearchs('tax_rate_location', {
545 data_vendor => 'billsoft',
547 geocode => $href->{PCode}
550 $location = FS::tax_rate_location->new({
551 'data_vendor' => 'billsoft',
552 'geocode' => $href->{PCode},
553 'country' => uc(country_code2code($href->{CountryISO},
554 'alpha-3' => 'alpha-2')),
555 'state' => $href->{State},
556 'county' => $href->{County},
557 'city' => $href->{Locality},
559 my $error = $location->insert;
561 $self->log->error("Failed to insert tax_class record: $error");
568 if ( $href->{TaxLevelID} == 0 ) { # national-level tax
570 } elsif ( $href->{TaxLevelID} == 1 ) {
571 $prefix = $location->state;
572 } elsif ( $href->{TaxLevelID} == 2 ) {
573 $prefix = $location->county . ' COUNTY';
574 } elsif ( $href->{TaxLevelID} == 3 ) {
575 $prefix = $location->city;
576 } elsif ( $href->{TaxLevelID} == 4 ) { # unincorporated area ta
579 # Some itemdescs start with the jurisdiction name; otherwise, prepend
581 if ( $itemdesc !~ /^(city of )?$prefix\b/i ) {
582 $itemdesc = "$prefix $itemdesc";
584 # Create or locate a tax_rate record, because we need one to foreign-key
585 # the cust_bill_pkg_tax_rate_location record.
586 my $tax_rate = $self->find_or_insert_tax_rate(
587 geocode => $href->{PCode},
588 taxclassnum => $tax_class->taxclassnum,
589 taxname => $itemdesc,
591 my $amount = sprintf('%.2f', $href->{TaxAmount});
592 # and add it to the tax under this name
593 my $tax_item = $self->add_tax_item(
595 itemdesc => $itemdesc,
598 # and link that tax line item to the taxed sale
599 my $subitem = FS::cust_bill_pkg_tax_rate_location->new({
600 billpkgnum => $tax_item->billpkgnum,
601 taxnum => $tax_rate->taxnum,
602 taxtype => 'FS::tax_rate',
603 taxratelocationnum => $location->taxratelocationnum,
605 taxable_billpkgnum => $billpkgnum,
607 my $error = $subitem->insert;
608 die "Error linking tax to taxable item: $error\n" if $error;
613 die "Encountered $errors error(s); rolling back tax import.\n";
616 # remove pending flag from invoices and schedule collect jobs
617 foreach my $cust_bill (values %{ $self->{'cust_bill'} }) {
618 my $invnum = $cust_bill->invnum;
619 $cust_bill->set('pending' => '');
620 my $error = $cust_bill->replace;
621 die "Error updating invoice #$invnum: $error\n"
623 $self->{'custnums'}->{ $cust_bill->custnum } = 1;
626 foreach my $custnum ( keys %{ $self->{'custnums'} } ) {
627 my $queue = FS::queue->new({ 'job' => 'FS::cust_main::queued_collect' });
628 my $error = $queue->insert('custnum' => $custnum);
629 die "Error scheduling collection for customer #$custnum: $error\n"
637 sub find_or_insert_tax_rate {
638 my ($self, %hash) = @_;
640 $hash{'data_vendor'} = 'billsoft';
641 my $tax_rate = qsearchs('tax_rate', \%hash);
643 $tax_rate = FS::tax_rate->new(\%hash);
644 my $error = $tax_rate->insert;
645 die "Error inserting tax definition: $error\n" if $error;
652 my ($self, %hash) = @_;
654 my $amount = delete $hash{'amount'};
656 my $tax_item = qsearchs('cust_bill_pkg', \%hash);
658 $tax_item = FS::cust_bill_pkg->new(\%hash);
659 $tax_item->set('setup', $amount);
660 my $error = $tax_item->insert;
661 die "Error inserting tax: $error\n" if $error;
663 $tax_item->set('setup', $tax_item->get('setup') + $amount);
664 my $error = $tax_item->replace;
665 die "Error incrementing tax: $error\n" if $error;
668 my $cust_bill = $self->{'cust_bill'}->{$tax_item->invnum}
669 or die "Invoice #".$tax_item->{invnum}." is not pending.\n";
670 $cust_bill->set('charged' =>
671 sprintf('%.2f', $cust_bill->get('charged') + $amount));
672 # don't replace the record yet, we'll do that at the end
677 sub load_tax_classes {
678 %TAX_CLASSES = map { $_->taxclass => $_ }
679 qsearch('tax_class', { data_vendor => 'billsoft' });