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);
14 use Parse::FixedLength;
18 $TIMEOUT = 86400; # absolute time limit on waiting for a response file.
20 FS::UID->install_callback(\&load_tax_classes);
25 manual_tax_location => 1,
29 sub add_sale { } #do nothing
32 $FS::UID::cache_dir . "/Billsoft";
37 my $conf = FS::Conf->new;;
38 my $spooldir = $self->spooldir;
39 mkdir $spooldir, 0700 unless -d $spooldir;
40 my $basename = $conf->config('billsoft-company_code') .
41 time2str('%Y%m%d', time); # use the real clock time here
43 while ( -e "$spooldir/$basename$uniq.CDF" ) {
45 # these two letters must be unique within each day
51 '%10s' . # Origination
52 '%1s' . # Origination Flag (NPA-NXX)
53 '%10s' . # Termination
54 '%1s' . # Termination Flag (NPA-NXX)
55 '%10s' . # Service Location
56 '%1s' . # Service Location Flag (Pcode)
57 '%1s' . # Customer Type ('B'usiness or 'R'esidential)
58 '%8s' . # Invoice Date
59 '+' . # Taxable Amount Sign
60 '%011d' . # Taxable Amount (5 decimal places)
63 '%12s' . # Transaction Type + Service Type
64 '%1s' . # Client Resale Flag ('S'ale or 'R'esale)
65 '%1s' . # Inc-Code ('I'n an incorporated city, or 'O'utside)
66 ' ' . # Fed/State/County/Local Exempt
67 '%1s' . # Primary Output Key, flag (our field)
68 '%019d' . # Primary Output Key, numeric (our field)
69 'R' . # 'R'egulated (or 'U'nregulated)
70 '%011d' . # Call Duration (tenths of minutes)
71 'C' . # Telecom Type ('C'alls, other things)
72 '%1s' . # Service Class ('L'ocal, Long 'D'istance)
73 ' NNC' . # non-lifeline, non-facilities based,
75 # (gross assumptions, may need a config option
76 "\r\n"; # at least that's what was in the samples
80 my ($self, %opt) = @_;
84 my $spooldir = $self->spooldir;
85 my $spoolname = $self->spoolname;
86 my $fh = IO::File->new();
87 $fh->open("$spooldir/$spoolname", '>>');
90 # XXX limit based on freeside-daily custnum/agentnum options
91 # and maybe invoice date
92 my @invoices = qsearch('cust_bill', { pending => 'Y' });
93 warn scalar(@invoices)." pending invoice(s) found.\n";
94 foreach my $cust_bill (@invoices) {
96 my $invnum = $cust_bill->invnum;
97 my $cust_main = $cust_bill->cust_main;
98 my $cust_type = $cust_main->company ? 'B' : 'R';
99 my $invoice_date = time2str('%Y%m%d', $cust_bill->_date);
102 my (%cust_pkg, %part_pkg, %cust_location, %classname);
103 # keys are transaction codes (the first part of the taxproduct string)
104 # and then locationnums; for per-location taxes
107 foreach my $cust_bill_pkg ( $cust_bill->cust_bill_pkg ) {
109 my $cust_pkg = $cust_pkg{$cust_bill_pkg->pkgnum}
110 ||= $cust_bill_pkg->cust_pkg;
111 my $pkgpart = $cust_bill_pkg->pkgpart_override || $cust_pkg->pkgpart;
112 my $part_pkg = $part_pkg{$pkgpart} ||= FS::part_pkg->by_key($pkgpart);
113 my $resale_mode = ($part_pkg->option('wholesale',1) ? 'R' : 'S');
114 my $locationnum = $cust_pkg->locationnum;
115 my $location = $cust_location{$locationnum} ||= $cust_pkg->cust_location;
116 my %taxproduct; # CDR rated_classnum => taxproduct
119 # go back to the original call details
120 my $detailnums = FS::Record->scalar_sql(
121 "SELECT array_to_string(array_agg(detailnum), ',') ".
122 "FROM cust_bill_pkg_detail WHERE billpkgnum = ".
123 $cust_bill_pkg->billpkgnum
126 # With summary details, even the number of CDRs returned from a single
127 # invoice detail could be scary large. Avoid running out of memory.
128 if (length $detailnums > 0) {
129 my $cdr_search = FS::Cursor->new({
131 'hashref' => { freesidestatus => 'done' },
132 'extra_sql' => "AND detailnum IN($detailnums)",
135 while (my $cdr = $cdr_search->fetch) {
136 my $classnum = $cdr->rated_classnum;
137 $classname{$classnum} ||= FS::usage_class->by_key($classnum)->classname
139 $taxproduct{$classnum} ||= $part_pkg->taxproduct($classnum);
140 if (!$taxproduct{$classnum}) {
141 warn "part_pkg $pkgpart, class $classnum: ".
142 ($taxproduct{$classnum} ?
143 "using taxproduct ".$taxproduct{$classnum}->description."\n" :
144 "taxproduct not found\n")
149 my $line = sprintf($format,
150 substr($cdr->src, 0, 6), 'N',
151 substr($cdr->dst, 0, 6), 'N',
152 $location->geocode, 'P',
155 100000 * $cdr->rated_price, # price (5 decimal places)
158 $taxproduct{$classnum}->taxproduct,
160 ($location->incorporated ? 'I' : 'O'),
163 # Call duration (tenths of minutes)
165 # Service class indicator ('L'ocal, Long 'D'istance)
167 (lc($classname{$classnum}) eq 'local' ? 'L' : 'D'),
172 $usage_total += $cdr->rated_price;
174 } # while $cdr = $cdr_search->fetch
175 } # if @$detailnums; otherwise there are no usage details for this line
178 # now write lines for the non-CDR portion of the charges
179 foreach (qw(setup recur)) {
180 my $taxproduct = $part_pkg->taxproduct($_);
181 warn "part_pkg $pkgpart, class $_: ".
183 "using taxproduct ".$taxproduct->description."\n" :
184 "taxproduct not found\n")
186 next unless $taxproduct;
188 my ($tcode) = $taxproduct->taxproduct =~ /^(\d{6})/;
189 $sales{$tcode} ||= {};
190 $sales{$tcode}{$location->locationnum} ||= 0;
191 $recur_tcode = $tcode if $_ eq 'recur';
193 my $price = $cust_bill_pkg->get($_);
194 $sales{$tcode}{$location->locationnum} += $price;
196 $price -= $usage_total if $_ eq 'recur';
198 my $line = sprintf($format,
199 $location->geocode, 'P', # all 3 locations the same
200 $location->geocode, 'P',
201 $location->geocode, 'P',
204 100000 * $price, # price (5 decimal places)
207 $taxproduct->taxproduct,
209 ($location->incorporated ? 'I' : 'O'),
210 substr(uc($_), 0, 1), # 'S'etup or 'R'ecur
211 $cust_bill_pkg->billpkgnum,
213 'D' # service class indicator
218 } # foreach (setup, recur)
220 # S-code 23: taxes based on number of lines (E911, mostly)
221 # voip_cdr and voip_inbound packages know how to report this. Not all
222 # T-codes are eligible for this; only report it if the /23 taxproduct
225 # (note: the nomenclature of "service" and "transaction" codes is
226 # backward from the way most people would use the terms. you'd think
227 # that in "cellular activation", "cellular" would be the service and
228 # "activation" would be the transaction, but for Billsoft it's the
229 # reverse. I recommend calling them "S" and "T" codes internally just
230 # to avoid confusion.)
232 my $lines_taxproduct = qsearchs('part_pkg_taxproduct', {
233 'taxproduct' => sprintf('%06d%06d', $recur_tcode, 21)
235 my $lines = $cust_bill_pkg->units;
237 if ( $lines_taxproduct and $lines ) {
239 my $line = sprintf($format,
240 $location->geocode, 'P', # all 3 locations the same
241 $location->geocode, 'P',
242 $location->geocode, 'P',
245 0, # price (5 decimal places)
248 $lines_taxproduct->taxproduct,
250 ($location->incorporated ? 'I' : 'O'),
252 $cust_bill_pkg->billpkgnum,
254 'D' # service class indicator
259 } # foreach my $cust_bill_pkg
261 # Implicit transactions
262 foreach my $tcode (keys %sales) {
264 # S-code 23: number of locations (rare)
265 my $locations_taxproduct =
266 qsearchs('part_pkg_taxproduct', {
267 'taxproduct' => sprintf('%06d%06d', $tcode, 23)
270 if ( $locations_taxproduct and keys %{ $sales{$tcode} } > 0 ) {
271 my $location = $cust_main->bill_location;
272 my $line = sprintf($format,
273 $location->geocode, 'P', # all 3 locations the same
274 $location->geocode, 'P',
275 $location->geocode, 'P',
278 0, # price (5 decimal places)
280 keys(%{ $sales{$tcode} }),# locations
281 $locations_taxproduct->taxproduct,
283 ($location->incorporated ? 'I' : 'O'),
285 sprintf('%07d%06d%06d', $invnum, $tcode, 0),
287 'D' # service class indicator
293 # S-code 43: per-invoice tax (apparently this is a thing)
294 my $invoice_taxproduct =
295 qsearchs('part_pkg_taxproduct', {
296 'taxproduct' => sprintf('%06d%06d', $tcode, 43)
298 if ( $invoice_taxproduct ) {
299 my $location = $cust_main->bill_location;
300 my $line = sprintf($format,
301 $location->geocode, 'P', # all 3 locations the same
302 $location->geocode, 'P',
303 $location->geocode, 'P',
306 0, # price (5 decimal places)
309 $invoice_taxproduct->taxproduct,
311 ($location->incorporated ? 'I' : 'O'),
313 sprintf('%07d%06d%06d', $invnum, $tcode, 0),
315 'D' # service class indicator
321 } # foreach $cust_bill
327 sub cust_tax_locations {
329 my $location = shift;
330 if (ref $location eq 'HASH') {
331 $location = FS::cust_location->new($location);
333 my $zip = $location->zip;
334 return () unless $location->country eq 'US';
335 # currently the only one supported
336 if ( $zip =~ /^(\d{5})(-\d{4})?$/ ) {
339 die "bad zip code $zip";
342 table => 'cust_tax_location',
343 hashref => { 'data_vendor' => 'billsoft' },
344 extra_sql => " AND ziplo <= '$zip' and ziphi >= '$zip'",
345 order_by => ' ORDER BY default_location',
350 my ($self, %opt) = @_;
352 my $oldAutoCommit = $FS::UID::AutoCommit;
353 local $FS::UID::AutoCommit = 0;
356 eval "use Net::FTP;";
357 # set up directories if they're not already
358 mkdir $self->spooldir unless -d $self->spooldir;
359 local $CWD = $self->spooldir;
360 foreach (qw(upload download)) {
361 mkdir $_ unless -d $_;
363 my $target = qsearchs('upload_target', { hostname => 'ftp.billsoft.com' })
364 or die "No Billsoft upload target defined.\n";
367 my $upload = $self->create_batch(%opt);
370 my $ftp = $target->connect;
371 if (!ref $ftp) { # it's an error message
372 die "Error connecting to Billsoft FTP server:\n$ftp\n";
374 my $fh = IO::File->new();
375 warn "Processing: $upload\n";
376 my $error = system("zip -j -o FTP.ZIP $upload");
377 die "Failed to compress tax batch\n$!\n" if $error;
378 warn "Uploading file...\n";
379 $ftp->put('FTP.ZIP');
381 my $download = $upload;
382 # naming convention for these is: same as the CDF contained in the
383 # zip file, but with an "R" inserted after the company ID prefix
384 $download =~ s/^(...)(\d{8}..).CDF/$1R$2.ZIP/;
385 warn "Waiting for output file ($download)...\n";
386 my $starttime = time;
388 while ( time - $starttime < $TIMEOUT ) {
389 my @ls = $ftp->ls($download);
391 if ($ftp->get($download, "download/$download")) {
392 warn "Downloaded '$download'.\n";
396 warn "Failed to download '$download': ".$ftp->message."\n";
397 # We know the file exists, so continue trying to download it.
398 # Maybe the problem will get fixed.
404 warn "No output file received.\n";
407 warn "Decompressing...\n";
408 system("unzip -o download/$download");
409 foreach my $csf (glob "*.CSF") {
410 warn "Processing '$csf'...\n";
411 $fh->open($csf, '<') or die "failed to open downloaded file $csf";
412 $self->batch_import($fh); # dies on error
414 unlink $csf unless $DEBUG;
417 move($upload, "upload/$upload");
419 $dbh->commit if $oldAutoCommit;
424 $DB::single = 1; # XXX
426 my ($self, $fh) = @_;
427 $self->{'custnums'} = {};
428 $self->{'cust_bill'} = {};
430 # gather up pending invoices
431 foreach my $cust_bill (qsearch('cust_bill', { pending => 'Y' })) {
432 $self->{'cust_bill'}{ $cust_bill->invnum } = $cust_bill;
436 my $parser = Parse::FixedLength->new(
438 # key => 20, # for our purposes we split it up
449 # start parsing the input file
452 foreach my $line (<$fh>) {
453 warn $line if $DEBUG > 1;
455 $href = $parser->parse($line);
456 # convert some of these to integers
457 $href->{$_} += 0 foreach(qw(pkey taxtype amount pcode));
458 next if $href->{amount} == 0; # then nobody cares
460 my $flag = $href->{flag};
461 my $pkey = $href->{pkey};
462 my $cust_bill_pkg; # the line item that this tax applies to
463 # resolve the taxable object
464 if ( $flag eq 'C' ) {
465 # this line represents a CDR.
466 my $cdr = FS::cdr->by_key($pkey);
468 warn "[$row]\tCDR #$pkey not found.\n";
469 } elsif (!$cdr->detailnum) {
470 warn "[$row]\tCDR #$pkey has not been billed.\n";
474 my $detail = FS::cust_bill_pkg_detail->by_key($cdr->detailnum);
475 $cust_bill_pkg = $detail->cust_bill_pkg;
477 } elsif ( $flag =~ /S|R|L/ ) {
478 # this line represents a setup or recur fee, or a number of lines.
479 $cust_bill_pkg = FS::cust_bill_pkg->by_key($pkey);
480 if (!$cust_bill_pkg) {
481 warn "[$row]\tLine item #$pkey not found.\n";
483 } elsif ( $flag =~ /O|I/ ) {
484 warn "Per-invoice taxes are not implemented.\n";
486 warn "[$row]\tFlag '$flag' not recognized.\n";
488 if (!$cust_bill_pkg) {
489 $errors++; # this will trigger a rollback of the transaction
492 # resolve the tax definition
493 # base name of the tax type (like "Sales Tax" or "Universal Lifeline
494 # Telephone Service Charge").
495 my $tax_class = $TAX_CLASSES{ $href->{taxtype} + 0 };
497 warn "[$row]\tUnknown tax type $href->{taxtype}.\n";
501 my $itemdesc = uc($tax_class->description);
502 my $location = qsearchs('tax_rate_location',
503 { geocode => $href->{pcode} }
506 warn "Unknown tax authority location ".$href->{pcode}."\n";
512 if ( $href->{authority} == 0 ) { # national-level tax
514 } elsif ( $href->{authority} == 1 ) {
515 $prefix = $location->state;
516 } elsif ( $href->{authority} == 2 ) {
517 $prefix = $location->county . ' COUNTY';
518 } elsif ( $href->{authority} == 3 ) {
519 $prefix = $location->city;
520 } elsif ( $href->{authority} == 4 ) { # unincorporated area ta
523 # Some itemdescs start with the jurisdiction name; otherwise, prepend
525 if ( $itemdesc !~ /^(city of )?$prefix\b/i ) {
526 $itemdesc = "$prefix $itemdesc";
528 # Create or locate a tax_rate record, because we need one to foreign-key
529 # the cust_bill_pkg_tax_rate_location record.
530 my $tax_rate = $self->find_or_insert_tax_rate(
531 geocode => $href->{pcode},
532 taxclassnum => $tax_class->taxclassnum,
533 taxname => $itemdesc,
535 # Convert amount from 10^-5 dollars to dollars/cents
536 my $amount = sprintf('%.2f', $href->{amount} / 100000);
537 # and add it to the tax under this name
538 my $tax_item = $self->add_tax_item(
539 invnum => $cust_bill_pkg->invnum,
540 itemdesc => $itemdesc,
543 # and link that tax line item to the taxed sale
544 my $subitem = FS::cust_bill_pkg_tax_rate_location->new({
545 billpkgnum => $tax_item->billpkgnum,
546 taxnum => $tax_rate->taxnum,
547 taxtype => 'FS::tax_rate',
548 taxratelocationnum => $location->taxratelocationnum,
550 taxable_billpkgnum => $cust_bill_pkg->billpkgnum,
552 my $error = $subitem->insert;
553 die "Error linking tax to taxable item: $error\n" if $error;
558 die "Encountered $errors error(s); rolling back tax import.\n";
561 # remove pending flag from invoices and schedule collect jobs
562 foreach my $cust_bill (values %{ $self->{'cust_bill'} }) {
563 my $invnum = $cust_bill->invnum;
564 $cust_bill->set('pending' => '');
565 my $error = $cust_bill->replace;
566 die "Error updating invoice #$invnum: $error\n"
568 $self->{'custnums'}->{ $cust_bill->custnum } = 1;
571 foreach my $custnum ( keys %{ $self->{'custnums'} } ) {
572 my $queue = FS::queue->new({ 'job' => 'FS::cust_main::queued_collect' });
573 my $error = $queue->insert('custnum' => $custnum);
574 die "Error scheduling collection for customer #$custnum: $error\n"
582 sub find_or_insert_tax_rate {
583 my ($self, %hash) = @_;
585 $hash{'data_vendor'} = 'billsoft';
586 my $tax_rate = qsearchs('tax_rate', \%hash);
588 $tax_rate = FS::tax_rate->new(\%hash);
589 my $error = $tax_rate->insert;
590 die "Error inserting tax definition: $error\n" if $error;
597 my ($self, %hash) = @_;
599 my $amount = delete $hash{'amount'};
601 my $tax_item = qsearchs('cust_bill_pkg', \%hash);
603 $tax_item = FS::cust_bill_pkg->new(\%hash);
604 $tax_item->set('setup', $amount);
605 my $error = $tax_item->insert;
606 die "Error inserting tax: $error\n" if $error;
608 $tax_item->set('setup', $tax_item->get('setup') + $amount);
609 my $error = $tax_item->replace;
610 die "Error incrementing tax: $error\n" if $error;
613 my $cust_bill = $self->{'cust_bill'}->{$tax_item->invnum}
614 or die "Invoice #".$tax_item->{invnum}." is not pending.\n";
615 $cust_bill->set('charged' =>
616 sprintf('%.2f', $cust_bill->get('charged') + $amount));
617 # don't replace the record yet, we'll do that at the end
622 sub load_tax_classes {
623 %TAX_CLASSES = map { $_->taxclass => $_ }
624 qsearch('tax_class', { data_vendor => 'billsoft' });