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)
193 my @options = $self->conf->config('billsoft-taxconfig');
195 my %bill_properties = (
197 Date => $invoice_date,
198 CustomerType => $cust_type,
199 CustomerNumber => $cust_bill->custnum,
200 InvoiceNumber => $invnum,
201 Facilities => ($options[0] || ''),
202 Franchise => ($options[1] || ''),
203 Regulated => ($options[2] || ''),
204 BusinessClass => ($options[3] || ''),
207 foreach my $cust_bill_pkg ( $cust_bill->cust_bill_pkg ) {
208 my $cust_pkg = $cust_pkg{$cust_bill_pkg->pkgnum}
209 ||= $cust_bill_pkg->cust_pkg;
210 my $pkgpart = $cust_bill_pkg->pkgpart_override || $cust_pkg->pkgpart;
211 my $part_pkg = $part_pkg{$pkgpart} ||= FS::part_pkg->by_key($pkgpart);
212 my $resale_mode = ($part_pkg->option('wholesale',1) ? 'Resale' : 'Sale');
213 my %pkg_properties = (
215 Sale => $resale_mode,
216 Optional => $cust_bill_pkg->billpkgnum, # will be echoed
217 # others at this level? Lifeline?
218 # DiscountType may be relevant...
224 # cursorized joined search on the invoice details, for memory efficiency
225 my $cdr_search = FS::Cursor->new({
227 'hashref' => { freesidestatus => 'done' },
228 'addl_from' => ' JOIN cust_bill_pkg_detail USING (detailnum)',
229 'extra_sql' => "AND cust_bill_pkg_detail.billpkgnum = ".
230 $cust_bill_pkg->billpkgnum
233 while (my $cdr = $cdr_search->fetch) {
234 my $classnum = $cdr->rated_classnum;
236 $classname{$classnum} ||= FS::usage_class->by_key($classnum)->classname;
239 my $taxproduct = $self->part_pkg_taxproduct($part_pkg, $classnum)
241 my ($tcode, $scode) = split(':', $taxproduct);
243 # For CDRs, use the call termination site rather than setting
244 # Termination fields to the service address.
245 $csv->print_hr($fh, {
247 RequestType => 'CalcTaxes',
248 OriginationNpaNxx => substr($cdr->src_lrn || $cdr->src, 0, 6),
249 TerminationNpaNxx => substr($cdr->dst_lrn || $cdr->dst, 0, 6),
250 TransactionType => $tcode,
251 ServiceType => $scode,
252 Charge => $cdr->rated_price,
253 Minutes => ($cdr->duration / 60.0), # floating point
256 $usage_total += $cdr->rated_price;
258 } # while $cdr = $cdr_search->fetch
260 my $locationnum = $cust_pkg->locationnum;
262 # use termination address for the service location
263 my %termination = do {
264 my $location = $cust_location{$locationnum} ||= $cust_pkg->cust_location;
265 my $zip = $location->zip;
267 if ($location->country eq 'US') {
268 ($zip, $plus4) = split(/-/, $zip);
270 ( TerminationCountryISO => uc(country_code2code($location->country,
271 'alpha-2' => 'alpha-3')),
272 TerminationPCode => $location->geocode,
273 TerminationZipCode => $zip,
274 TerminationZipP4 => $plus4,
278 foreach (qw(setup recur)) {
279 my $taxproduct = $self->part_pkg_taxproduct($part_pkg, $_);
280 next unless $taxproduct;
282 my ($tcode, $scode) = split(':', $taxproduct);
283 $all_tcodes{$tcode} ||= 1;
285 my $price = $cust_bill_pkg->get($_);
287 $price -= $usage_total if $_ eq 'recur';
289 $csv->print_hr($fh, {
292 RequestType => 'CalcTaxes',
293 TransactionType => $tcode,
294 ServiceType => $scode,
298 } # foreach (setup, recur)
300 # taxes based on number of lines (E911, mostly)
301 # mostly S-code 21 but can be others, as they want to know about
302 # Centrex trunks, PBX extensions, etc.
304 # (note: the nomenclature of "service" and "transaction" codes is
305 # backward from the way most people would use the terms. you'd think
306 # that in "cellular activation", "cellular" would be the service and
307 # "activation" would be the transaction, but for Billsoft it's the
308 # reverse. I recommend calling them "S" and "T" codes internally just
309 # to avoid confusion.)
312 if ( my $lines_taxproduct = $part_pkg->units_taxproduct ) {
313 my $lines = $cust_bill_pkg->units;
314 my $taxproduct = $lines_taxproduct->taxproduct;
315 my ($tcode, $scode) = split(':', $taxproduct);
316 $all_tcodes{$tcode} ||= 1;
318 $csv->print_hr($fh, {
321 RequestType => 'CalcTaxes',
322 TransactionType => $tcode,
323 ServiceType => $scode,
330 } # foreach my $cust_bill_pkg
332 foreach my $tcode (keys %all_tcodes) {
334 # S-code 43: per-invoice tax
335 # XXX not exactly correct; there's "Invoice Bundle" (7:94) and
336 # "Centrex Invoice" (7:623). Local Exchange service would benefit from
337 # more high-level selection of the tax properties. (Infer from the FCC
338 # reporting options?)
339 my $invoice_taxproduct = FS::part_pkg_taxproduct->count(
340 'data_vendor = \'billsoft\' and taxproduct = ?',
343 if ( $invoice_taxproduct ) {
344 $csv->print_hr($fh, {
345 RequestType => 'CalcTaxes',
347 TransactionType => $tcode,
353 } # foreach $cust_bill
359 sub cust_tax_locations {
361 my $location = shift;
362 if (ref $location eq 'HASH') {
363 $location = FS::cust_location->new($location);
365 my $zip = $location->zip;
366 return () unless $location->country eq 'US';
367 return () unless $zip;
368 # currently the only one supported
369 if ( $zip =~ /^(\d{5})(-\d{4})?$/ ) {
372 die "bad zip code $zip";
375 table => 'cust_tax_location',
376 hashref => { 'data_vendor' => 'billsoft' },
377 extra_sql => " AND ziplo <= '$zip' and ziphi >= '$zip'",
378 order_by => ' ORDER BY default_location',
383 my ($self, %opt) = @_;
385 my $oldAutoCommit = $FS::UID::AutoCommit;
386 local $FS::UID::AutoCommit = 0;
389 eval "use Net::FTP;";
390 # set up directories if they're not already
391 mkdir $self->spooldir unless -d $self->spooldir;
392 local $CWD = $self->spooldir;
393 foreach (qw(upload download)) {
394 mkdir $_ unless -d $_;
396 my $target = qsearchs('upload_target', { hostname => 'ftp.billsoft.com' })
397 or die "No Billsoft upload target defined.\n";
399 local $CWD = $self->spooldir . '/upload';
401 my $upload = $self->create_batch(%opt); # name of the CSV file
402 # returns undef if there were no pending invoices; in that case
403 # skip the rest of this procedure
407 my $ftp = $target->connect;
408 if (!ref $ftp) { # it's an error message
409 die "Error connecting to Billsoft FTP server:\n$ftp\n";
411 my $fh = IO::File->new();
412 $self->log->info("Processing: $upload");
413 if ( stat('FTP.ZIP') ) {
414 unlink('FTP.ZIP') or die "Failed to remove old tax batch:\n$!\n";
416 my $error = system("zip -j -o FTP.ZIP $upload");
417 die "Failed to compress tax batch\n$!\n" if $error;
418 $self->log->debug("Uploading file");
419 $ftp->put('FTP.ZIP');
422 local $CWD = $self->spooldir;
423 my $download = $upload;
424 # naming convention for these is: same as the CSV contained in the
425 # zip file, but with an "R" inserted after the company ID prefix
426 $download =~ s/^(...)(\d{8}..).CSV/$1R$2.ZIP/;
427 $self->log->debug("Waiting for output file ($download)");
428 my $starttime = time;
430 while ( time - $starttime < $TIMEOUT ) {
431 my @ls = $ftp->ls($download);
433 if ($ftp->get($download, "download/$download")) {
434 $self->log->debug("Downloaded '$download'");
438 $self->log->warn("Failed to download '$download': ".$ftp->message);
439 # We know the file exists, so continue trying to download it.
440 # Maybe the problem will get fixed.
446 $self->log->error("No output file received.");
449 $self->log->debug("Decompressing...");
450 system("unzip -o download/$download");
451 my $output = $upload;
452 $output =~ s/.CSV$/_dtl.rpt.csv/i;
453 if ([ -f $output ]) {
454 $self->log->info("Processing '$output'");
455 $fh->open($output, '<') or die "failed to open downloaded file $output";
456 $self->batch_import($fh); # dies on error
458 unlink $output unless $DEBUG;
461 $dbh->commit if $oldAutoCommit;
466 $DB::single = 1; # XXX
468 my ($self, $fh) = @_;
469 $self->{'custnums'} = {};
470 $self->{'cust_bill'} = {};
472 # gather up pending invoices
473 foreach my $cust_bill (qsearch('cust_bill', { pending => 'Y' })) {
474 $self->{'cust_bill'}{ $cust_bill->invnum } = $cust_bill;
478 my $parser = Text::CSV_XS->new({binary => 1});
479 # set column names from header row
480 $parser->column_names($parser->getline($fh));
482 # start parsing the file
485 # the file is functionally a left join of submitted line items with their
486 # taxes; if a line item has no taxes then it will produce an output row
487 # with all the tax fields empty.
488 while ($href = $parser->getline_hr($fh)) {
489 next if $href->{TaxTypeID} eq ''; # then this row has no taxes
490 next if $href->{TaxAmount} == 0; # then the calculated tax is zero
492 my $billpkgnum = $href->{Optional};
493 my $invnum = $href->{InvoiceNumber};
494 my $cust_bill_pkg; # the line item that this tax applies to
495 if ( !exists($self->{cust_bill}->{$invnum}) ) {
496 $self->log->error("invoice #$invnum invoice not in pending state");
501 $cust_bill_pkg = FS::cust_bill_pkg->by_key($billpkgnum);
502 if ( $cust_bill_pkg->invnum != $invnum ) {
503 $self->log->error("invoice #$invnum invoice number mismatch");
508 $cust_bill_pkg = ($self->{cust_bill}->{$invnum}->cust_bill_pkg)[0];
509 $billpkgnum = $cust_bill_pkg->billpkgnum;
512 # resolve the tax definition
513 # base name of the tax type (like "Sales Tax" or "Universal Lifeline
514 # Telephone Service Charge").
515 my $tax_class = $TAX_CLASSES{ $href->{TaxTypeID} };
517 $self->log->warn("Unknown tax type $href->{TaxTypeID}");
518 $tax_class = FS::tax_class->new({
519 'data_vendor' => 'billsoft',
520 'taxclass' => $href->{TaxTypeID},
521 'description' => $href->{TaxType}
523 my $error = $tax_class->insert;
525 $self->log->error("Failed to insert tax_class record: $error");
529 $TAX_CLASSES{ $href->{TaxTypeID} } = $tax_class;
531 my $itemdesc = uc($tax_class->description);
532 my $location = qsearchs('tax_rate_location', {
533 data_vendor => 'billsoft',
535 geocode => $href->{PCode}
538 $location = FS::tax_rate_location->new({
539 'data_vendor' => 'billsoft',
540 'geocode' => $href->{PCode},
541 'country' => uc(country_code2code($href->{CountryISO},
542 'alpha-3' => 'alpha-2')),
543 'state' => $href->{State},
544 'county' => $href->{County},
545 'city' => $href->{Locality},
547 my $error = $location->insert;
549 $self->log->error("Failed to insert tax_class record: $error");
556 if ( $href->{TaxLevelID} == 0 ) { # national-level tax
558 } elsif ( $href->{TaxLevelID} == 1 ) {
559 $prefix = $location->state;
560 } elsif ( $href->{TaxLevelID} == 2 ) {
561 $prefix = $location->county . ' COUNTY';
562 } elsif ( $href->{TaxLevelID} == 3 ) {
563 $prefix = $location->city;
564 } elsif ( $href->{TaxLevelID} == 4 ) { # unincorporated area ta
567 # Some itemdescs start with the jurisdiction name; otherwise, prepend
569 if ( $itemdesc !~ /^(city of )?$prefix\b/i ) {
570 $itemdesc = "$prefix $itemdesc";
572 # Create or locate a tax_rate record, because we need one to foreign-key
573 # the cust_bill_pkg_tax_rate_location record.
574 my $tax_rate = $self->find_or_insert_tax_rate(
575 geocode => $href->{PCode},
576 taxclassnum => $tax_class->taxclassnum,
577 taxname => $itemdesc,
579 my $amount = sprintf('%.2f', $href->{TaxAmount});
580 # and add it to the tax under this name
581 my $tax_item = $self->add_tax_item(
583 itemdesc => $itemdesc,
586 # and link that tax line item to the taxed sale
587 my $subitem = FS::cust_bill_pkg_tax_rate_location->new({
588 billpkgnum => $tax_item->billpkgnum,
589 taxnum => $tax_rate->taxnum,
590 taxtype => 'FS::tax_rate',
591 taxratelocationnum => $location->taxratelocationnum,
593 taxable_billpkgnum => $billpkgnum,
595 my $error = $subitem->insert;
596 die "Error linking tax to taxable item: $error\n" if $error;
601 die "Encountered $errors error(s); rolling back tax import.\n";
604 # remove pending flag from invoices and schedule collect jobs
605 foreach my $cust_bill (values %{ $self->{'cust_bill'} }) {
606 my $invnum = $cust_bill->invnum;
607 $cust_bill->set('pending' => '');
608 my $error = $cust_bill->replace;
609 die "Error updating invoice #$invnum: $error\n"
611 $self->{'custnums'}->{ $cust_bill->custnum } = 1;
614 foreach my $custnum ( keys %{ $self->{'custnums'} } ) {
615 my $queue = FS::queue->new({ 'job' => 'FS::cust_main::queued_collect' });
616 my $error = $queue->insert('custnum' => $custnum);
617 die "Error scheduling collection for customer #$custnum: $error\n"
625 sub find_or_insert_tax_rate {
626 my ($self, %hash) = @_;
628 $hash{'data_vendor'} = 'billsoft';
629 my $tax_rate = qsearchs('tax_rate', \%hash);
631 $tax_rate = FS::tax_rate->new(\%hash);
632 my $error = $tax_rate->insert;
633 die "Error inserting tax definition: $error\n" if $error;
640 my ($self, %hash) = @_;
642 my $amount = delete $hash{'amount'};
644 my $tax_item = qsearchs('cust_bill_pkg', \%hash);
646 $tax_item = FS::cust_bill_pkg->new(\%hash);
647 $tax_item->set('setup', $amount);
648 my $error = $tax_item->insert;
649 die "Error inserting tax: $error\n" if $error;
651 $tax_item->set('setup', $tax_item->get('setup') + $amount);
652 my $error = $tax_item->replace;
653 die "Error incrementing tax: $error\n" if $error;
656 my $cust_bill = $self->{'cust_bill'}->{$tax_item->invnum}
657 or die "Invoice #".$tax_item->{invnum}." is not pending.\n";
658 $cust_bill->set('charged' =>
659 sprintf('%.2f', $cust_bill->get('charged') + $amount));
660 # don't replace the record yet, we'll do that at the end
665 sub load_tax_classes {
666 %TAX_CLASSES = map { $_->taxclass => $_ }
667 qsearch('tax_class', { data_vendor => 'billsoft' });