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 ) {
108 my $cust_pkg = $cust_pkg{$cust_bill_pkg->pkgnum}
109 ||= $cust_bill_pkg->cust_pkg;
110 my $pkgpart = $cust_bill_pkg->pkgpart_override || $cust_pkg->pkgpart;
111 my $part_pkg = $part_pkg{$pkgpart} ||= FS::part_pkg->by_key($pkgpart);
112 my $resale_mode = ($part_pkg->option('wholesale',1) ? 'R' : 'S');
113 my $locationnum = $cust_pkg->locationnum;
114 my $location = $cust_location{$locationnum} ||= $cust_pkg->cust_location;
115 my %taxproduct; # CDR rated_classnum => taxproduct
118 # go back to the original call details
119 my $detailnums = FS::Record->scalar_sql(
120 "SELECT array_to_string(array_agg(detailnum), ',') ".
121 "FROM cust_bill_pkg_detail WHERE billpkgnum = ".
122 $cust_bill_pkg->billpkgnum
125 # With summary details, even the number of CDRs returned from a single
126 # invoice detail could be scary large. Avoid running out of memory.
127 if (length $detailnums > 0) {
128 my $cdr_search = FS::Cursor->new({
130 'hashref' => { freesidestatus => 'done' },
131 'extra_sql' => "AND detailnum IN($detailnums)",
134 while (my $cdr = $cdr_search->fetch) {
135 my $classnum = $cdr->rated_classnum;
136 $classname{$classnum} ||= FS::usage_class->by_key($classnum)->classname
138 $taxproduct{$classnum} ||= $part_pkg->taxproduct($classnum);
139 if (!$taxproduct{$classnum}) {
140 warn "part_pkg $pkgpart, class $classnum: ".
141 ($taxproduct{$classnum} ?
142 "using taxproduct ".$taxproduct{$classnum}->description."\n" :
143 "taxproduct not found\n")
148 my $line = sprintf($format,
149 substr($cdr->src, 0, 6), 'N',
150 substr($cdr->dst, 0, 6), 'N',
151 $location->geocode, 'P',
154 100000 * $cdr->rated_price, # price (5 decimal places)
157 $taxproduct{$classnum}->taxproduct,
159 ($location->incorporated ? 'I' : 'O'),
162 # Call duration (tenths of minutes)
164 # Service class indicator ('L'ocal, Long 'D'istance)
166 (lc($classname{$classnum}) eq 'local' ? 'L' : 'D'),
171 $usage_total += $cdr->rated_price;
173 } # while $cdr = $cdr_search->fetch
174 } # if @$detailnums; otherwise there are no usage details for this line
177 # now write lines for the non-CDR portion of the charges
178 foreach (qw(setup recur)) {
179 my $taxproduct = $part_pkg->taxproduct($_);
180 warn "part_pkg $pkgpart, class $_: ".
182 "using taxproduct ".$taxproduct->description."\n" :
183 "taxproduct not found\n")
185 next unless $taxproduct;
187 my ($tcode) = $taxproduct->taxproduct =~ /^(\d{6})/;
188 $sales{$tcode} ||= {};
189 $sales{$tcode}{$location->locationnum} ||= 0;
190 $recur_tcode = $tcode if $_ eq 'recur';
192 my $price = $cust_bill_pkg->get($_);
193 $sales{$tcode}{$location->locationnum} += $price;
195 $price -= $usage_total if $_ eq 'recur';
197 my $line = sprintf($format,
198 $location->geocode, 'P', # all 3 locations the same
199 $location->geocode, 'P',
200 $location->geocode, 'P',
203 100000 * $price, # price (5 decimal places)
206 $taxproduct->taxproduct,
208 ($location->incorporated ? 'I' : 'O'),
209 substr(uc($_), 0, 1), # 'S'etup or 'R'ecur
210 $cust_bill_pkg->billpkgnum,
212 'D' # service class indicator
217 } # foreach (setup, recur)
219 # S-code 23: taxes based on number of lines (E911, mostly)
220 # voip_cdr and voip_inbound packages know how to report this. Not all
221 # T-codes are eligible for this; only report it if the /23 taxproduct
224 # (note: the nomenclature of "service" and "transaction" codes is
225 # backward from the way most people would use the terms. you'd think
226 # that in "cellular activation", "cellular" would be the service and
227 # "activation" would be the transaction, but for Billsoft it's the
228 # reverse. I recommend calling them "S" and "T" codes internally just
229 # to avoid confusion.)
231 my $lines_taxproduct = qsearchs('part_pkg_taxproduct', {
232 'taxproduct' => sprintf('%06d%06d', $recur_tcode, 21)
234 my $lines = $cust_bill_pkg->units;
236 if ( $lines_taxproduct and $lines ) {
238 my $line = sprintf($format,
239 $location->geocode, 'P', # all 3 locations the same
240 $location->geocode, 'P',
241 $location->geocode, 'P',
244 0, # price (5 decimal places)
247 $lines_taxproduct->taxproduct,
249 ($location->incorporated ? 'I' : 'O'),
251 $cust_bill_pkg->billpkgnum,
253 'D' # service class indicator
258 } # foreach my $cust_bill_pkg
260 # Implicit transactions
261 foreach my $tcode (keys %sales) {
263 # S-code 23: number of locations (rare)
264 my $locations_taxproduct =
265 qsearchs('part_pkg_taxproduct', {
266 'taxproduct' => sprintf('%06d%06d', $tcode, 23)
269 if ( $locations_taxproduct and keys %{ $sales{$tcode} } > 0 ) {
270 my $location = $cust_main->bill_location;
271 my $line = sprintf($format,
272 $location->geocode, 'P', # all 3 locations the same
273 $location->geocode, 'P',
274 $location->geocode, 'P',
277 0, # price (5 decimal places)
279 keys(%{ $sales{$tcode} }),# locations
280 $locations_taxproduct->taxproduct,
282 ($location->incorporated ? 'I' : 'O'),
284 sprintf('%07d%06d%06d', $invnum, $tcode, 0),
286 'D' # service class indicator
292 # S-code 43: per-invoice tax (apparently this is a thing)
293 my $invoice_taxproduct =
294 qsearchs('part_pkg_taxproduct', {
295 'taxproduct' => sprintf('%06d%06d', $tcode, 43)
297 if ( $invoice_taxproduct ) {
298 my $location = $cust_main->bill_location;
299 my $line = sprintf($format,
300 $location->geocode, 'P', # all 3 locations the same
301 $location->geocode, 'P',
302 $location->geocode, 'P',
305 0, # price (5 decimal places)
308 $invoice_taxproduct->taxproduct,
310 ($location->incorporated ? 'I' : 'O'),
312 sprintf('%07d%06d%06d', $invnum, $tcode, 0),
314 'D' # service class indicator
320 } # foreach $cust_bill
326 sub cust_tax_locations {
328 my $location = shift;
329 if (ref $location eq 'HASH') {
330 $location = FS::cust_location->new($location);
332 my $zip = $location->zip;
333 return () unless $location->country eq 'US';
334 # currently the only one supported
335 if ( $zip =~ /^(\d{5})(-\d{4})?$/ ) {
338 die "bad zip code $zip";
341 table => 'cust_tax_location',
342 hashref => { 'data_vendor' => 'billsoft' },
343 extra_sql => " AND ziplo <= '$zip' and ziphi >= '$zip'",
344 order_by => ' ORDER BY default_location',
349 my ($self, %opt) = @_;
351 my $oldAutoCommit = $FS::UID::AutoCommit;
352 local $FS::UID::AutoCommit = 0;
355 eval "use Net::FTP;";
356 # set up directories if they're not already
357 mkdir $self->spooldir unless -d $self->spooldir;
358 local $CWD = $self->spooldir;
359 foreach (qw(upload download)) {
360 mkdir $_ unless -d $_;
362 my $target = qsearchs('upload_target', { hostname => 'ftp.billsoft.com' })
363 or die "No Billsoft upload target defined.\n";
366 my $upload = $self->create_batch(%opt);
369 my $ftp = $target->connect;
370 if (!ref $ftp) { # it's an error message
371 die "Error connecting to Billsoft FTP server:\n$ftp\n";
373 my $fh = IO::File->new();
374 warn "Processing: $upload\n";
375 my $error = system("zip -j -o FTP.ZIP $upload");
376 die "Failed to compress tax batch\n$!\n" if $error;
377 warn "Uploading file...\n";
378 $ftp->put('FTP.ZIP');
380 my $download = $upload;
381 # naming convention for these is: same as the CDF contained in the
382 # zip file, but with an "R" inserted after the company ID prefix
383 $download =~ s/^(...)(\d{8}..).CDF/$1R$2.ZIP/;
384 warn "Waiting for output file ($download)...\n";
385 my $starttime = time;
387 while ( time - $starttime < $TIMEOUT ) {
388 my @ls = $ftp->ls($download);
390 if ($ftp->get($download, "download/$download")) {
391 warn "Downloaded '$download'.\n";
395 warn "Failed to download '$download': ".$ftp->message."\n";
396 # We know the file exists, so continue trying to download it.
397 # Maybe the problem will get fixed.
403 warn "No output file received.\n";
406 warn "Decompressing...\n";
407 system("unzip -o download/$download");
408 foreach my $csf (glob "*.CSF") {
409 warn "Processing '$csf'...\n";
410 $fh->open($csf, '<') or die "failed to open downloaded file $csf";
411 $self->batch_import($fh); # dies on error
413 unlink $csf unless $DEBUG;
416 move($upload, "upload/$upload");
418 $dbh->commit if $oldAutoCommit;
423 $DB::single = 1; # XXX
425 my ($self, $fh) = @_;
426 $self->{'custnums'} = {};
427 $self->{'cust_bill'} = {};
429 # gather up pending invoices
430 foreach my $cust_bill (qsearch('cust_bill', { pending => 'Y' })) {
431 $self->{'cust_bill'}{ $cust_bill->invnum } = $cust_bill;
435 my $parser = Parse::FixedLength->new(
437 # key => 20, # for our purposes we split it up
448 # start parsing the input file
451 foreach my $line (<$fh>) {
452 warn $line if $DEBUG > 1;
454 $href = $parser->parse($line);
455 # convert some of these to integers
456 $href->{$_} += 0 foreach(qw(pkey taxtype amount pcode));
457 next if $href->{amount} == 0; # then nobody cares
459 my $flag = $href->{flag};
460 my $pkey = $href->{pkey};
461 my $cust_bill_pkg; # the line item that this tax applies to
462 # resolve the taxable object
463 if ( $flag eq 'C' ) {
464 # this line represents a CDR.
465 my $cdr = FS::cdr->by_key($pkey);
467 warn "[$row]\tCDR #$pkey not found.\n";
468 } elsif (!$cdr->detailnum) {
469 warn "[$row]\tCDR #$pkey has not been billed.\n";
473 my $detail = FS::cust_bill_pkg_detail->by_key($cdr->detailnum);
474 $cust_bill_pkg = $detail->cust_bill_pkg;
476 } elsif ( $flag =~ /S|R|L/ ) {
477 # this line represents a setup or recur fee, or a number of lines.
478 $cust_bill_pkg = FS::cust_bill_pkg->by_key($pkey);
479 if (!$cust_bill_pkg) {
480 warn "[$row]\tLine item #$pkey not found.\n";
482 } elsif ( $flag =~ /O|I/ ) {
483 warn "Per-invoice taxes are not implemented.\n";
485 warn "[$row]\tFlag '$flag' not recognized.\n";
487 if (!$cust_bill_pkg) {
488 $errors++; # this will trigger a rollback of the transaction
491 # resolve the tax definition
492 # base name of the tax type (like "Sales Tax" or "Universal Lifeline
493 # Telephone Service Charge").
494 my $tax_class = $TAX_CLASSES{ $href->{taxtype} + 0 };
496 warn "[$row]\tUnknown tax type $href->{taxtype}.\n";
500 my $itemdesc = uc($tax_class->description);
501 my $location = qsearchs('tax_rate_location',
502 { geocode => $href->{pcode} }
505 warn "Unknown tax authority location ".$href->{pcode}."\n";
511 if ( $href->{authority} == 0 ) { # national-level tax
513 } elsif ( $href->{authority} == 1 ) {
514 $prefix = $location->state;
515 } elsif ( $href->{authority} == 2 ) {
516 $prefix = $location->county . ' COUNTY';
517 } elsif ( $href->{authority} == 3 ) {
518 $prefix = $location->city;
519 } elsif ( $href->{authority} == 4 ) { # unincorporated area ta
522 # Some itemdescs start with the jurisdiction name; otherwise, prepend
524 if ( $itemdesc !~ /^(city of )?$prefix\b/i ) {
525 $itemdesc = "$prefix $itemdesc";
527 # Create or locate a tax_rate record, because we need one to foreign-key
528 # the cust_bill_pkg_tax_rate_location record.
529 my $tax_rate = $self->find_or_insert_tax_rate(
530 geocode => $href->{pcode},
531 taxclassnum => $tax_class->taxclassnum,
532 taxname => $itemdesc,
534 # Convert amount from 10^-5 dollars to dollars/cents
535 my $amount = sprintf('%.2f', $href->{amount} / 100000);
536 # and add it to the tax under this name
537 my $tax_item = $self->add_tax_item(
538 invnum => $cust_bill_pkg->invnum,
539 itemdesc => $itemdesc,
542 # and link that tax line item to the taxed sale
543 my $subitem = FS::cust_bill_pkg_tax_rate_location->new({
544 billpkgnum => $tax_item->billpkgnum,
545 taxnum => $tax_rate->taxnum,
546 taxtype => 'FS::tax_rate',
547 taxratelocationnum => $location->taxratelocationnum,
549 taxable_billpkgnum => $cust_bill_pkg->billpkgnum,
551 my $error = $subitem->insert;
552 die "Error linking tax to taxable item: $error\n" if $error;
557 die "Encountered $errors error(s); rolling back tax import.\n";
560 # remove pending flag from invoices and schedule collect jobs
561 foreach my $cust_bill (values %{ $self->{'cust_bill'} }) {
562 my $invnum = $cust_bill->invnum;
563 $cust_bill->set('pending' => '');
564 my $error = $cust_bill->replace;
565 die "Error updating invoice #$invnum: $error\n"
567 $self->{'custnums'}->{ $cust_bill->custnum } = 1;
570 foreach my $custnum ( keys %{ $self->{'custnums'} } ) {
571 my $queue = FS::queue->new({ 'job' => 'FS::cust_main::queued_collect' });
572 my $error = $queue->insert('custnum' => $custnum);
573 die "Error scheduling collection for customer #$custnum: $error\n"
581 sub find_or_insert_tax_rate {
582 my ($self, %hash) = @_;
584 $hash{'data_vendor'} = 'billsoft';
585 my $tax_rate = qsearchs('tax_rate', \%hash);
587 $tax_rate = FS::tax_rate->new(\%hash);
588 my $error = $tax_rate->insert;
589 die "Error inserting tax definition: $error\n" if $error;
596 my ($self, %hash) = @_;
598 my $amount = delete $hash{'amount'};
600 my $tax_item = qsearchs('cust_bill_pkg', \%hash);
602 $tax_item = FS::cust_bill_pkg->new(\%hash);
603 $tax_item->set('setup', $amount);
604 my $error = $tax_item->insert;
605 die "Error inserting tax: $error\n" if $error;
607 $tax_item->set('setup', $tax_item->get('setup') + $amount);
608 my $error = $tax_item->replace;
609 die "Error incrementing tax: $error\n" if $error;
612 my $cust_bill = $self->{'cust_bill'}->{$tax_item->invnum}
613 or die "Invoice #".$tax_item->{invnum}." is not pending.\n";
614 $cust_bill->set('charged' =>
615 sprintf('%.2f', $cust_bill->get('charged') + $amount));
616 # don't replace the record yet, we'll do that at the end
621 sub load_tax_classes {
622 %TAX_CLASSES = map { $_->taxclass => $_ }
623 qsearch('tax_class', { data_vendor => 'billsoft' });