+=item owed_sql [ BEFORE, AFTER, OPTIONS ]
+
+Returns an SQL expression for the amount owed. BEFORE and AFTER specify
+a date window. OPTIONS may include 'no_usage' (excludes usage charges)
+and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
+
+=cut
+
+sub owed_sql {
+ my $class = shift;
+ '(' . $class->charged_sql(@_) .
+ ' - ' . $class->paid_sql(@_) .
+ ' - ' . $class->credited_sql(@_) . ')'
+}
+
+=item paid_sql [ BEFORE, AFTER, OPTIONS ]
+
+Returns an SQL expression for the sum of payments applied to this item.
+
+=cut
+
+sub paid_sql {
+ my ($class, $start, $end, %opt) = @_;
+ my $s = $start ? "AND cust_pay._date <= $start" : '';
+ my $e = $end ? "AND cust_pay._date > $end" : '';
+ my $setuprecur = $opt{setuprecur} || '';
+ $setuprecur = 'setup' if $setuprecur =~ /^s/;
+ $setuprecur = 'recur' if $setuprecur =~ /^r/;
+ $setuprecur &&= "AND setuprecur = '$setuprecur'";
+
+ my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
+ FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
+ JOIN cust_pay USING (paynum)
+ WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
+ $s $e $setuprecur )";
+
+ if ( $opt{no_usage} ) {
+ # cap the amount paid at the sum of non-usage charges,
+ # minus the amount credited against non-usage charges
+ "LEAST($paid, ".
+ $class->charged_sql($start, $end, %opt) . ' - ' .
+ $class->credited_sql($start, $end, %opt).')';
+ }
+ else {
+ $paid;
+ }
+
+}
+
+sub credited_sql {
+ my ($class, $start, $end, %opt) = @_;
+ my $s = $start ? "AND cust_credit._date <= $start" : '';
+ my $e = $end ? "AND cust_credit._date > $end" : '';
+ my $setuprecur = $opt{setuprecur} || '';
+ $setuprecur = 'setup' if $setuprecur =~ /^s/;
+ $setuprecur = 'recur' if $setuprecur =~ /^r/;
+ $setuprecur &&= "AND setuprecur = '$setuprecur'";
+
+ my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
+ FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
+ JOIN cust_credit USING (crednum)
+ WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
+ $s $e $setuprecur )";
+
+ if ( $opt{no_usage} ) {
+ # cap the amount credited at the sum of non-usage charges
+ "LEAST($credited, ". $class->charged_sql($start, $end, %opt).')';
+ }
+ else {
+ $credited;
+ }
+
+}
+
+sub upgrade_tax_location {
+ # For taxes that were calculated/invoiced before cust_location refactoring
+ # (May-June 2012), there are no cust_bill_pkg_tax_location records unless
+ # they were calculated on a package-location basis. Create them here,
+ # along with any necessary cust_location records and any tax exemption
+ # records.
+
+ my ($class, %opt) = @_;
+ # %opt may include 's' and 'e': start and end date ranges
+ # and 'X': abort on any error, instead of just rolling back changes to
+ # that invoice
+ my $dbh = dbh;
+ my $oldAutoCommit = $FS::UID::AutoCommit;
+ local $FS::UID::AutoCommit = 0;
+
+ eval {
+ use FS::h_cust_main;
+ use FS::h_cust_bill;
+ use FS::h_part_pkg;
+ use FS::h_cust_main_exemption;
+ };
+
+ local $FS::cust_location::import = 1;
+
+ my $conf = FS::Conf->new; # h_conf?
+ return if $conf->exists('enable_taxproducts'); #don't touch this case
+ my $use_ship = $conf->exists('tax-ship_address');
+ my $use_pkgloc = $conf->exists('tax-pkg_address');
+
+ my $date_where = '';
+ if ($opt{s}) {
+ $date_where .= " AND cust_bill._date >= $opt{s}";
+ }
+ if ($opt{e}) {
+ $date_where .= " AND cust_bill._date < $opt{e}";
+ }
+
+ my $commit_each_invoice = 1 unless $opt{X};
+
+ # if an invoice has either of these kinds of objects, then it doesn't
+ # need to be upgraded...probably
+ my $sub_has_tax_link = 'SELECT 1 FROM cust_bill_pkg_tax_location'.
+ ' JOIN cust_bill_pkg USING (billpkgnum)'.
+ ' WHERE cust_bill_pkg.invnum = cust_bill.invnum';
+ my $sub_has_exempt = 'SELECT 1 FROM cust_tax_exempt_pkg'.
+ ' JOIN cust_bill_pkg USING (billpkgnum)'.
+ ' WHERE cust_bill_pkg.invnum = cust_bill.invnum'.
+ ' AND exempt_monthly IS NULL';
+
+ my %all_tax_names = (
+ '' => 1,
+ 'Tax' => 1,
+ map { $_->taxname => 1 }
+ qsearch('h_cust_main_county', { taxname => { op => '!=', value => '' }})
+ );
+
+ my $search = FS::Cursor->new({
+ table => 'cust_bill',
+ hashref => {},
+ extra_sql => "WHERE NOT EXISTS($sub_has_tax_link) ".
+ "AND NOT EXISTS($sub_has_exempt) ".
+ $date_where,
+ });
+
+#print "Processing ".scalar(@invnums)." invoices...\n";
+
+ my $committed;
+ INVOICE:
+ while (my $cust_bill = $search->fetch) {
+ my $invnum = $cust_bill->invnum;
+ $committed = 0;
+ print STDERR "Invoice #$invnum\n";
+ my $pre = '';
+ my %pkgpart_taxclass; # pkgpart => taxclass
+ my %pkgpart_exempt_setup;
+ my %pkgpart_exempt_recur;
+ my $h_cust_bill = qsearchs('h_cust_bill',
+ { invnum => $invnum,
+ history_action => 'insert' });
+ if (!$h_cust_bill) {
+ warn "no insert record for invoice $invnum; skipped\n";
+ #$date = $cust_bill->_date as a fallback?
+ # We're trying to avoid using non-real dates (-d/-y invoice dates)
+ # when looking up history records in other tables.
+ next INVOICE;
+ }
+ my $custnum = $h_cust_bill->custnum;
+
+ # Determine the address corresponding to this tax region.
+ # It's either the bill or ship address of the customer as of the
+ # invoice date-of-insertion. (Not necessarily the invoice date.)
+ my $date = $h_cust_bill->history_date;
+ my $h_cust_main = qsearchs('h_cust_main',
+ { custnum => $custnum },
+ FS::h_cust_main->sql_h_searchs($date)
+ );
+ if (!$h_cust_main ) {
+ warn "no historical address for cust#".$h_cust_bill->custnum."; skipped\n";
+ next INVOICE;
+ # fallback to current $cust_main? sounds dangerous.
+ }
+
+ # This is a historical customer record, so it has a historical address.
+ # If there's no cust_location matching this custnum and address (there
+ # probably isn't), create one.
+ my %tax_loc; # keys are pkgnums, values are cust_location objects
+ my $default_tax_loc;
+ if ( $h_cust_main->bill_locationnum ) {
+ # the location has already been upgraded
+ if ($use_ship) {
+ $default_tax_loc = $h_cust_main->ship_location;
+ } else {
+ $default_tax_loc = $h_cust_main->bill_location;
+ }
+ } else {
+ $pre = 'ship_' if $use_ship and length($h_cust_main->get('ship_last'));
+ my %hash = map { $_ => $h_cust_main->get($pre.$_) }
+ FS::cust_main->location_fields;
+ # not really needed for this, and often result in duplicate locations
+ delete @hash{qw(censustract censusyear latitude longitude coord_auto)};
+
+ $hash{custnum} = $h_cust_main->custnum;
+ $default_tax_loc = FS::cust_location->new(\%hash);
+ my $error = $default_tax_loc->find_or_insert || $default_tax_loc->disable_if_unused;
+ if ( $error ) {
+ warn "couldn't create historical location record for cust#".
+ $h_cust_main->custnum.": $error\n";
+ next INVOICE;
+ }
+ }
+ my $exempt_cust;
+ $exempt_cust = 1 if $h_cust_main->tax;
+
+ # classify line items
+ my @tax_items;
+ my %nontax_items; # taxclass => array of cust_bill_pkg
+ foreach my $item ($h_cust_bill->cust_bill_pkg) {
+ my $pkgnum = $item->pkgnum;
+
+ if ( $pkgnum == 0 ) {
+
+ push @tax_items, $item;
+
+ } else {
+ # (pkgparts really shouldn't change, right?)
+ my $h_cust_pkg = qsearchs('h_cust_pkg', { pkgnum => $pkgnum },
+ FS::h_cust_pkg->sql_h_searchs($date)
+ );
+ if ( !$h_cust_pkg ) {
+ warn "no historical package #".$item->pkgpart."; skipped\n";
+ next INVOICE;
+ }
+ my $pkgpart = $h_cust_pkg->pkgpart;
+
+ if ( $use_pkgloc and $h_cust_pkg->locationnum ) {
+ # then this package already had a locationnum assigned, and that's
+ # the one to use for tax calculation
+ $tax_loc{$pkgnum} = FS::cust_location->by_key($h_cust_pkg->locationnum);
+ } else {
+ # use the customer's bill or ship loc, which was inserted earlier
+ $tax_loc{$pkgnum} = $default_tax_loc;
+ }
+
+ if (!exists $pkgpart_taxclass{$pkgpart}) {
+ my $h_part_pkg = qsearchs('h_part_pkg', { pkgpart => $pkgpart },
+ FS::h_part_pkg->sql_h_searchs($date)
+ );
+ if ( !$h_part_pkg ) {
+ warn "no historical package def #$pkgpart; skipped\n";
+ next INVOICE;
+ }
+ $pkgpart_taxclass{$pkgpart} = $h_part_pkg->taxclass || '';
+ $pkgpart_exempt_setup{$pkgpart} = 1 if $h_part_pkg->setuptax;
+ $pkgpart_exempt_recur{$pkgpart} = 1 if $h_part_pkg->recurtax;
+ }
+
+ # mark any exemptions that apply
+ if ( $pkgpart_exempt_setup{$pkgpart} ) {
+ $item->set('exempt_setup' => 1);
+ }
+
+ if ( $pkgpart_exempt_recur{$pkgpart} ) {
+ $item->set('exempt_recur' => 1);
+ }
+
+ my $taxclass = $pkgpart_taxclass{ $pkgpart };
+
+ $nontax_items{$taxclass} ||= [];
+ push @{ $nontax_items{$taxclass} }, $item;
+ }
+ }
+
+ printf("%d tax items: \$%.2f\n", scalar(@tax_items), map {$_->setup} @tax_items)
+ if @tax_items;
+
+ # Get any per-customer taxname exemptions that were in effect.
+ my %exempt_cust_taxname;
+ foreach (keys %all_tax_names) {
+ my $h_exemption = qsearchs('h_cust_main_exemption', {
+ 'custnum' => $custnum,
+ 'taxname' => $_,
+ },
+ FS::h_cust_main_exemption->sql_h_searchs($date, $date)
+ );
+ if ($h_exemption) {
+ $exempt_cust_taxname{ $_ } = 1;
+ }
+ }