FS/bin/freeside-apply_payments_and_credits

   1 #!/usr/bin/perl -w
   2
   3 use strict;
   4 use vars qw( $DEBUG );
   5 use FS::UID qw(adminsuidsetup);
   6 use FS::Record qw(qsearch qsearchs);
   7 use FS::cust_main;
   8 use DBI;
   9
  10 $DEBUG = 1;
  11
  12 my $user = shift or die &usage;
  13 my $dbh = adminsuidsetup $user;
  14
  15 my $unapplied_payments_sql = <<EOF;
  16 SELECT custnum FROM cust_pay WHERE paid >
  17   ( ( SELECT coalesce(sum(amount),0) FROM cust_bill_pay
  18         WHERE cust_pay.paynum = cust_bill_pay.paynum )
  19   + ( SELECT coalesce(sum(amount),0) FROM cust_pay_refund
  20         WHERE cust_pay.paynum = cust_pay_refund.paynum)
  21   )
  22 EOF
  23
  24 my $unapplied_credits_sql = <<EOF;
  25 SELECT custnum FROM cust_credit WHERE cust_credit.amount >
  26   ( ( SELECT coalesce(sum(cust_credit_bill.amount),0) FROM cust_credit_bill
  27         WHERE cust_credit.crednum = cust_credit_bill.crednum )
  28   + ( SELECT coalesce(sum(cust_Credit_refund.amount),0) FROM cust_credit_refund
  29         WHERE cust_credit.crednum = cust_credit_refund.crednum)
  30   )
  31 EOF
  32
  33 my %custnum = ();
  34
  35 my $sth = $dbh->prepare($unapplied_payments_sql) or die $dbh->errstr;
  36 $sth->execute or die "unapplied payment search failed: ". $sth->errstr;
  37
  38 map { $custnum{$_->[0]} = 1 } @{ $sth->fetchall_arrayref };
  39
  40 $sth = $dbh->prepare($unapplied_credits_sql) or die $dbh->errstr;
  41 $sth->execute or die "unapplied credit search failed: ". $sth->errstr;
  42
  43 map { $custnum{$_->[0]} = 1 } @{ $sth->fetchall_arrayref };
  44
  45 foreach my $custnum ( keys %custnum ) {
  46
  47   warn "processing customer $custnum\n" if $DEBUG;
  48
  49   my $cust_main = qsearchs('cust_main', { 'custnum' => $custnum } )
  50     or die "customer $custnum no longer exists!\n";
  51
  52   my $error = $cust_main->apply_payments_and_credits;
  53   die $error if $error;
  54
  55 }
  56
  57 sub usage {
  58   die "Usage:\n\n  freeside-apply_payments_and_credits user\n";
  59 }
  60
  61 =head1 NAME
  62
  63 freeside-apply_payments_and_credits - Command line interface to apply payments and credits to invoice
  64
  65 =head1 SYNOPSIS
  66
  67   freeside-apply_payments_and_credits username
  68
  69 =head1 DESCRIPTION
  70
  71 Finds unapplied payment and credit amounts and applies them to any outstanding
  72 uncovered invoice amounts.
  73
  74 B<username> is a username added by freeside-adduser.
  75
  76 =cut
  77
  78
  79