--- /dev/null
+package FS::TaxEngine::billsoft;
+
+use strict;
+use vars qw( $DEBUG $TIMEOUT %TAX_CLASSES );
+use base 'FS::TaxEngine';
+use FS::Conf;
+use FS::Record qw(qsearch qsearchs dbh);
+use FS::part_pkg;
+use FS::cdr;
+use FS::upload_target;
+use Date::Format qw( time2str );
+use File::chdir;
+use File::Copy qw(move);
+use Parse::FixedLength;
+
+$DEBUG = 1;
+
+$TIMEOUT = 86400; # absolute time limit on waiting for a response file.
+
+FS::UID->install_callback(\&load_tax_classes);
+
+sub info {
+ { batch => 1,
+ override => 0,
+ manual_tax_location => 1,
+ },
+}
+
+sub add_sale { } #do nothing
+
+sub spooldir {
+ $FS::UID::cache_dir . "/Billsoft";
+}
+
+sub spoolname {
+ my $self = shift;
+ my $conf = FS::Conf->new;;
+ my $spooldir = $self->spooldir;
+ mkdir $spooldir, 0700 unless -d $spooldir;
+ my $basename = $conf->config('billsoft-company_code') .
+ time2str('%Y%m%d', time); # use the real clock time here
+ my $uniq = 'AA';
+ while ( -e "$spooldir/$basename$uniq.CDF" ) {
+ $uniq++;
+ # these two letters must be unique within each day
+ }
+ "$basename$uniq.CDF";
+}
+
+my $format =
+ '%10s' . # Origination
+ '%1s' . # Origination Flag (NPA-NXX)
+ '%10s' . # Termination
+ '%1s' . # Termination Flag (NPA-NXX)
+ '%10s' . # Service Location
+ '%1s' . # Service Location Flag (Pcode)
+ '%1s' . # Customer Type ('B'usiness or 'R'esidential)
+ '%8s' . # Invoice Date
+ '+' . # Taxable Amount Sign
+ '%011d' . # Taxable Amount (5 decimal places)
+ '%6d' . # Lines
+ '%6d' . # Locations
+ '%12s' . # Transaction Type + Service Type
+ '%1s' . # Client Resale Flag ('S'ale or 'R'esale)
+ '%1s' . # Inc-Code ('I'n an incorporated city, or 'O'utside)
+ ' ' . # Fed/State/County/Local Exempt
+ '%1s' . # Primary Output Key, flag (our field)
+ '%019d' . # Primary Output Key, numeric (our field)
+ 'R' . # 'R'egulated (or 'U'nregulated)
+ '%011d' . # Call Duration (tenths of minutes)
+ 'C' . # Telecom Type ('C'alls, other things)
+ '%1s' . # Service Class ('L'ocal, Long 'D'istance)
+ ' NNC' . # non-lifeline, non-facilities based,
+ # non-franchise, CLEC
+ # (gross assumptions, may need a config option
+ "\r\n"; # at least that's what was in the samples
+
+
+sub create_batch {
+ my ($self, %opt) = @_;
+
+ $DB::single=1; # XXX
+
+ my $spooldir = $self->spooldir;
+ my $spoolname = $self->spoolname;
+ my $fh = IO::File->new();
+ $fh->open("$spooldir/$spoolname", '>>');
+ $self->{fh} = $fh;
+
+ # XXX limit based on freeside-daily custnum/agentnum options
+ # and maybe invoice date
+ my @invoices = qsearch('cust_bill', { pending => 'Y' });
+ warn scalar(@invoices)." pending invoice(s) found.\n";
+ foreach my $cust_bill (@invoices) {
+
+ my $invnum = $cust_bill->invnum;
+ my $cust_main = $cust_bill->cust_main;
+ my $cust_type = $cust_main->company ? 'B' : 'R';
+ my $invoice_date = time2str('%Y%m%d', $cust_bill->_date);
+
+ # cache some things
+ my (%cust_pkg, %part_pkg, %cust_location, %classname);
+ # keys are transaction codes (the first part of the taxproduct string)
+ # and then locationnums; for per-location taxes
+ my %sales;
+
+ foreach my $cust_bill_pkg ( $cust_bill->cust_bill_pkg ) {
+ my $cust_pkg = $cust_pkg{$cust_bill_pkg->pkgnum}
+ ||= $cust_bill_pkg->cust_pkg;
+ my $pkgpart = $cust_bill_pkg->pkgpart_override || $cust_pkg->pkgpart;
+ my $part_pkg = $part_pkg{$pkgpart} ||= FS::part_pkg->by_key($pkgpart);
+ my $resale_mode = ($part_pkg->option('wholesale',1) ? 'R' : 'S');
+ my $locationnum = $cust_pkg->locationnum;
+ my $location = $cust_location{$locationnum} ||= $cust_pkg->cust_location;
+ my %taxproduct; # CDR rated_classnum => taxproduct
+
+ my $usage_total = 0;
+ # go back to the original call details
+ my $detailnums = FS::Record->scalar_sql(
+ "SELECT array_to_string(array_agg(detailnum), ',') ".
+ "FROM cust_bill_pkg_detail WHERE billpkgnum = ".
+ $cust_bill_pkg->billpkgnum
+ );
+
+ # With summary details, even the number of CDRs returned from a single
+ # invoice detail could be scary large. Avoid running out of memory.
+ if (length $detailnums > 0) {
+ my $cdr_search = FS::Cursor->new({
+ 'table' => 'cdr',
+ 'hashref' => { freesidestatus => 'done' },
+ 'extra_sql' => "AND detailnum IN($detailnums)",
+ });
+
+ while (my $cdr = $cdr_search->fetch) {
+ my $classnum = $cdr->rated_classnum;
+ $classname{$classnum} ||= FS::usage_class->by_key($classnum)->classname
+ if $classnum;
+ $taxproduct{$classnum} ||= $part_pkg->taxproduct($classnum);
+ if (!$taxproduct{$classnum}) {
+ warn "part_pkg $pkgpart, class $classnum: ".
+ ($taxproduct{$classnum} ?
+ "using taxproduct ".$taxproduct{$classnum}->description."\n" :
+ "taxproduct not found\n")
+ if $DEBUG;
+ next;
+ }
+
+ my $line = sprintf($format,
+ substr($cdr->src, 0, 6), 'N',
+ substr($cdr->dst, 0, 6), 'N',
+ $location->geocode, 'P',
+ $cust_type,
+ $invoice_date,
+ 100000 * $cdr->rated_price, # price (5 decimal places)
+ 0, # lines
+ 0, # locations
+ $taxproduct{$classnum}->taxproduct,
+ $resale_mode,
+ ($location->incorporated ? 'I' : 'O'),
+ 'C', # for Call
+ $cdr->acctid,
+ # Call duration (tenths of minutes)
+ $cdr->duration / 6,
+ # Service class indicator ('L'ocal, Long 'D'istance)
+ # stupid hack
+ (lc($classname{$classnum}) eq 'local' ? 'L' : 'D'),
+ );
+
+ print $fh $line;
+
+ $usage_total += $cdr->rated_price;
+
+ } # while $cdr = $cdr_search->fetch
+ } # if @$detailnums; otherwise there are no usage details for this line
+
+ my $recur_tcode;
+ # now write lines for the non-CDR portion of the charges
+ foreach (qw(setup recur)) {
+ my $taxproduct = $part_pkg->taxproduct($_);
+ warn "part_pkg $pkgpart, class $_: ".
+ ($taxproduct ?
+ "using taxproduct ".$taxproduct->description."\n" :
+ "taxproduct not found\n")
+ if $DEBUG;
+ next unless $taxproduct;
+
+ my ($tcode) = $taxproduct->taxproduct =~ /^(\d{6})/;
+ $sales{$tcode} ||= {};
+ $sales{$tcode}{$location->locationnum} ||= 0;
+ $recur_tcode = $tcode if $_ eq 'recur';
+
+ my $price = $cust_bill_pkg->get($_);
+ $sales{$tcode}{$location->locationnum} += $price;
+
+ $price -= $usage_total if $_ eq 'recur';
+
+ my $line = sprintf($format,
+ $location->geocode, 'P', # all 3 locations the same
+ $location->geocode, 'P',
+ $location->geocode, 'P',
+ $cust_type,
+ $invoice_date,
+ 100000 * $price, # price (5 decimal places)
+ 0, # lines
+ 0, # locations
+ $taxproduct->taxproduct,
+ $resale_mode,
+ ($location->incorporated ? 'I' : 'O'),
+ substr(uc($_), 0, 1), # 'S'etup or 'R'ecur
+ $cust_bill_pkg->billpkgnum,
+ 0, # call duration
+ 'D' # service class indicator
+ );
+
+ print $fh $line;
+
+ } # foreach (setup, recur)
+
+ # S-code 23: taxes based on number of lines (E911, mostly)
+ # voip_cdr and voip_inbound packages know how to report this. Not all
+ # T-codes are eligible for this; only report it if the /23 taxproduct
+ # exists.
+ #
+ # (note: the nomenclature of "service" and "transaction" codes is
+ # backward from the way most people would use the terms. you'd think
+ # that in "cellular activation", "cellular" would be the service and
+ # "activation" would be the transaction, but for Billsoft it's the
+ # reverse. I recommend calling them "S" and "T" codes internally just
+ # to avoid confusion.)
+
+ my $lines_taxproduct = qsearchs('part_pkg_taxproduct', {
+ 'taxproduct' => sprintf('%06d%06d', $recur_tcode, 21)
+ });
+ my $lines = $cust_bill_pkg->units;
+
+ if ( $lines_taxproduct and $lines ) {
+
+ my $line = sprintf($format,
+ $location->geocode, 'P', # all 3 locations the same
+ $location->geocode, 'P',
+ $location->geocode, 'P',
+ $cust_type,
+ $invoice_date,
+ 0, # price (5 decimal places)
+ $lines, # lines
+ 0, # locations
+ $lines_taxproduct->taxproduct,
+ $resale_mode,
+ ($location->incorporated ? 'I' : 'O'),
+ 'L', # 'L'ines
+ $cust_bill_pkg->billpkgnum,
+ 0, # call duration
+ 'D' # service class indicator
+ );
+
+ }
+
+ } # foreach my $cust_bill_pkg
+
+ # Implicit transactions
+ foreach my $tcode (keys %sales) {
+
+ # S-code 23: number of locations (rare)
+ my $locations_taxproduct =
+ qsearchs('part_pkg_taxproduct', {
+ 'taxproduct' => sprintf('%06d%06d', $tcode, 23)
+ });
+
+ if ( $locations_taxproduct and keys %{ $sales{$tcode} } > 0 ) {
+ my $location = $cust_main->bill_location;
+ my $line = sprintf($format,
+ $location->geocode, 'P', # all 3 locations the same
+ $location->geocode, 'P',
+ $location->geocode, 'P',
+ $cust_type,
+ $invoice_date,
+ 0, # price (5 decimal places)
+ 0, # lines
+ keys(%{ $sales{$tcode} }),# locations
+ $locations_taxproduct->taxproduct,
+ 'S',
+ ($location->incorporated ? 'I' : 'O'),
+ 'O', # l'O'cations
+ sprintf('%07d%06d%06d', $invnum, $tcode, 0),
+ 0, # call duration
+ 'D' # service class indicator
+ );
+
+ print $fh $line;
+ }
+
+ # S-code 43: per-invoice tax (apparently this is a thing)
+ my $invoice_taxproduct =
+ qsearchs('part_pkg_taxproduct', {
+ 'taxproduct' => sprintf('%06d%06d', $tcode, 43)
+ });
+ if ( $invoice_taxproduct ) {
+ my $location = $cust_main->bill_location;
+ my $line = sprintf($format,
+ $location->geocode, 'P', # all 3 locations the same
+ $location->geocode, 'P',
+ $location->geocode, 'P',
+ $cust_type,
+ $invoice_date,
+ 0, # price (5 decimal places)
+ 0, # lines
+ 0, # locations
+ $invoice_taxproduct->taxproduct,
+ 'S', # resale mode
+ ($location->incorporated ? 'I' : 'O'),
+ 'I', # 'I'nvoice tax
+ sprintf('%07d%06d%06d', $invnum, $tcode, 0),
+ 0, # call duration
+ 'D' # service class indicator
+ );
+
+ print $fh $line;
+ }
+ } # foreach $tcode
+ } # foreach $cust_bill
+
+ $fh->close;
+ return $spoolname;
+}
+
+sub cust_tax_locations {
+ my $class = shift;
+ my $location = shift;
+ if (ref $location eq 'HASH') {
+ $location = FS::cust_location->new($location);
+ }
+ my $zip = $location->zip;
+ return () unless $location->country eq 'US';
+ # currently the only one supported
+ if ( $zip =~ /^(\d{5})(-\d{4})?$/ ) {
+ $zip = $1;
+ } else {
+ die "bad zip code $zip";
+ }
+ return qsearch({
+ table => 'cust_tax_location',
+ hashref => { 'data_vendor' => 'billsoft' },
+ extra_sql => " AND ziplo <= '$zip' and ziphi >= '$zip'",
+ order_by => ' ORDER BY default_location',
+ });
+}
+
+sub transfer_batch {
+ my ($self, %opt) = @_;
+
+ my $oldAutoCommit = $FS::UID::AutoCommit;
+ local $FS::UID::AutoCommit = 0;
+ my $dbh = dbh;
+
+ eval "use Net::FTP;";
+ # set up directories if they're not already
+ mkdir $self->spooldir unless -d $self->spooldir;
+ local $CWD = $self->spooldir;
+ foreach (qw(upload download)) {
+ mkdir $_ unless -d $_;
+ }
+ my $target = qsearchs('upload_target', { hostname => 'ftp.billsoft.com' })
+ or die "No Billsoft upload target defined.\n";
+
+ # create the batch
+ my $upload = $self->create_batch(%opt);
+
+ # upload it
+ my $ftp = $target->connect;
+ if (!ref $ftp) { # it's an error message
+ die "Error connecting to Billsoft FTP server:\n$ftp\n";
+ }
+ my $fh = IO::File->new();
+ warn "Processing: $upload\n";
+ my $error = system("zip -j -o FTP.ZIP $upload");
+ die "Failed to compress tax batch\n$!\n" if $error;
+ warn "Uploading file...\n";
+ $ftp->put('FTP.ZIP');
+
+ my $download = $upload;
+ # naming convention for these is: same as the CDF contained in the
+ # zip file, but with an "R" inserted after the company ID prefix
+ $download =~ s/^(...)(\d{8}..).CDF/$1R$2.ZIP/;
+ warn "Waiting for output file ($download)...\n";
+ my $starttime = time;
+ my $downloaded = 0;
+ while ( time - $starttime < $TIMEOUT ) {
+ my @ls = $ftp->ls($download);
+ if ( @ls ) {
+ if ($ftp->get($download, "download/$download")) {
+ warn "Downloaded '$download'.\n";
+ $downloaded = 1;
+ last;
+ } else {
+ warn "Failed to download '$download': ".$ftp->message."\n";
+ # We know the file exists, so continue trying to download it.
+ # Maybe the problem will get fixed.
+ }
+ }
+ sleep 30;
+ }
+ if (!$downloaded) {
+ warn "No output file received.\n";
+ next BATCH;
+ }
+ warn "Decompressing...\n";
+ system("unzip -o download/$download");
+ foreach my $csf (glob "*.CSF") {
+ warn "Processing '$csf'...\n";
+ $fh->open($csf, '<') or die "failed to open downloaded file $csf";
+ $self->batch_import($fh); # dies on error
+ $fh->close;
+ unlink $csf unless $DEBUG;
+ }
+ unlink 'FTP.ZIP';
+ move($upload, "upload/$upload");
+ warn "Finished.\n";
+ $dbh->commit if $oldAutoCommit;
+ return;
+}
+
+sub batch_import {
+ $DB::single = 1; # XXX
+ # the hard part
+ my ($self, $fh) = @_;
+ $self->{'custnums'} = {};
+ $self->{'cust_bill'} = {};
+
+ # gather up pending invoices
+ foreach my $cust_bill (qsearch('cust_bill', { pending => 'Y' })) {
+ $self->{'cust_bill'}{ $cust_bill->invnum } = $cust_bill;
+ }
+
+ my $href;
+ my $parser = Parse::FixedLength->new(
+ [
+ # key => 20, # for our purposes we split it up
+ flag => 1,
+ pkey => 19,
+ taxtype => 6,
+ authority => 1,
+ sign => 1,
+ amount => 11,
+ pcode => 9,
+ ],
+ );
+
+ # start parsing the input file
+ my $errors = 0;
+ my $row = 1;
+ foreach my $line (<$fh>) {
+ warn $line if $DEBUG > 1;
+ %$href = ();
+ $href = $parser->parse($line);
+ # convert some of these to integers
+ $href->{$_} += 0 foreach(qw(pkey taxtype amount pcode));
+ next if $href->{amount} == 0; # then nobody cares
+
+ my $flag = $href->{flag};
+ my $pkey = $href->{pkey};
+ my $cust_bill_pkg; # the line item that this tax applies to
+ # resolve the taxable object
+ if ( $flag eq 'C' ) {
+ # this line represents a CDR.
+ my $cdr = FS::cdr->by_key($pkey);
+ if (!$cdr) {
+ warn "[$row]\tCDR #$pkey not found.\n";
+ } elsif (!$cdr->detailnum) {
+ warn "[$row]\tCDR #$pkey has not been billed.\n";
+ $errors++;
+ next;
+ } else {
+ my $detail = FS::cust_bill_pkg_detail->by_key($cdr->detailnum);
+ $cust_bill_pkg = $detail->cust_bill_pkg;
+ }
+ } elsif ( $flag =~ /S|R|L/ ) {
+ # this line represents a setup or recur fee, or a number of lines.
+ $cust_bill_pkg = FS::cust_bill_pkg->by_key($pkey);
+ if (!$cust_bill_pkg) {
+ warn "[$row]\tLine item #$pkey not found.\n";
+ }
+ } elsif ( $flag =~ /O|I/ ) {
+ warn "Per-invoice taxes are not implemented.\n";
+ } else {
+ warn "[$row]\tFlag '$flag' not recognized.\n";
+ }
+ if (!$cust_bill_pkg) {
+ $errors++; # this will trigger a rollback of the transaction
+ next;
+ }
+ # resolve the tax definition
+ # base name of the tax type (like "Sales Tax" or "Universal Lifeline
+ # Telephone Service Charge").
+ my $tax_class = $TAX_CLASSES{ $href->{taxtype} + 0 };
+ if (!$tax_class) {
+ warn "[$row]\tUnknown tax type $href->{taxtype}.\n";
+ $errors++;
+ next;
+ }
+ my $itemdesc = uc($tax_class->description);
+ my $location = qsearchs('tax_rate_location',
+ { geocode => $href->{pcode} }
+ );
+ if (!$location) {
+ warn "Unknown tax authority location ".$href->{pcode}."\n";
+ $errors++;
+ next;
+ }
+ # jurisdiction name
+ my $prefix = '';
+ if ( $href->{authority} == 0 ) { # national-level tax
+ # do nothing
+ } elsif ( $href->{authority} == 1 ) {
+ $prefix = $location->state;
+ } elsif ( $href->{authority} == 2 ) {
+ $prefix = $location->county . ' COUNTY';
+ } elsif ( $href->{authority} == 3 ) {
+ $prefix = $location->city;
+ } elsif ( $href->{authority} == 4 ) { # unincorporated area ta
+ # do nothing
+ }
+ # Some itemdescs start with the jurisdiction name; otherwise, prepend
+ # it.
+ if ( $itemdesc !~ /^(city of )?$prefix\b/i ) {
+ $itemdesc = "$prefix $itemdesc";
+ }
+ # Create or locate a tax_rate record, because we need one to foreign-key
+ # the cust_bill_pkg_tax_rate_location record.
+ my $tax_rate = $self->find_or_insert_tax_rate(
+ geocode => $href->{pcode},
+ taxclassnum => $tax_class->taxclassnum,
+ taxname => $itemdesc,
+ );
+ # Convert amount from 10^-5 dollars to dollars/cents
+ my $amount = sprintf('%.2f', $href->{amount} / 100000);
+ # and add it to the tax under this name
+ my $tax_item = $self->add_tax_item(
+ invnum => $cust_bill_pkg->invnum,
+ itemdesc => $itemdesc,
+ amount => $amount,
+ );
+ # and link that tax line item to the taxed sale
+ my $subitem = FS::cust_bill_pkg_tax_rate_location->new({
+ billpkgnum => $tax_item->billpkgnum,
+ taxnum => $tax_rate->taxnum,
+ taxtype => 'FS::tax_rate',
+ taxratelocationnum => $location->taxratelocationnum,
+ amount => $amount,
+ taxable_billpkgnum => $cust_bill_pkg->billpkgnum,
+ });
+ my $error = $subitem->insert;
+ die "Error linking tax to taxable item: $error\n" if $error;
+
+ $row++;
+ } #foreach $line
+ if ( $errors > 0 ) {
+ die "Encountered $errors error(s); rolling back tax import.\n";
+ }
+
+ # remove pending flag from invoices and schedule collect jobs
+ foreach my $cust_bill (values %{ $self->{'cust_bill'} }) {
+ my $invnum = $cust_bill->invnum;
+ $cust_bill->set('pending' => '');
+ my $error = $cust_bill->replace;
+ die "Error updating invoice #$invnum: $error\n"
+ if $error;
+ $self->{'custnums'}->{ $cust_bill->custnum } = 1;
+ }
+
+ foreach my $custnum ( keys %{ $self->{'custnums'} } ) {
+ my $queue = FS::queue->new({ 'job' => 'FS::cust_main::queued_collect' });
+ my $error = $queue->insert('custnum' => $custnum);
+ die "Error scheduling collection for customer #$custnum: $error\n"
+ if $error;
+ }
+
+ '';
+}
+
+
+sub find_or_insert_tax_rate {
+ my ($self, %hash) = @_;
+ $hash{'tax'} = 0;
+ $hash{'data_vendor'} = 'billsoft';
+ my $tax_rate = qsearchs('tax_rate', \%hash);
+ if (!$tax_rate) {
+ $tax_rate = FS::tax_rate->new(\%hash);
+ my $error = $tax_rate->insert;
+ die "Error inserting tax definition: $error\n" if $error;
+ }
+ return $tax_rate;
+}
+
+
+sub add_tax_item {
+ my ($self, %hash) = @_;
+ $hash{'pkgnum'} = 0;
+ my $amount = delete $hash{'amount'};
+
+ my $tax_item = qsearchs('cust_bill_pkg', \%hash);
+ if (!$tax_item) {
+ $tax_item = FS::cust_bill_pkg->new(\%hash);
+ $tax_item->set('setup', $amount);
+ my $error = $tax_item->insert;
+ die "Error inserting tax: $error\n" if $error;
+ } else {
+ $tax_item->set('setup', $tax_item->get('setup') + $amount);
+ my $error = $tax_item->replace;
+ die "Error incrementing tax: $error\n" if $error;
+ }
+
+ my $cust_bill = $self->{'cust_bill'}->{$tax_item->invnum}
+ or die "Invoice #".$tax_item->{invnum}." is not pending.\n";
+ $cust_bill->set('charged' =>
+ sprintf('%.2f', $cust_bill->get('charged') + $amount));
+ # don't replace the record yet, we'll do that at the end
+
+ $tax_item;
+}
+
+sub load_tax_classes {
+ %TAX_CLASSES = map { $_->taxclass => $_ }
+ qsearch('tax_class', { data_vendor => 'billsoft' });
+}
+
+
+1;
projects / freeside.git / blobdiff