Useful script for voiding invoices

[Brian]

It's been quite sometime since I've read this list. Tonight however I 
wasn't just reading the list but actually searching for a script to get 
myself out of hot water. I was trying to trick the system to bill a 
customer a few days earlier so I could get him for both the current 
month of service as well as the next month of service when we do the 
regular cycle billing tomorrow. This whole problem can be attributed to 
another I hack I made which actually causes freeside to bill in a true 
monthly fashion so everybody's account aniversary is on the first of the 
month.

Any how, thanks to a Id10T error I messed up the date and trigger a cron 
job which started the cycle billing for next month. The only problem was 
I did this twice before I noticed and caused a big mess with about 1000 
invoices billed incorrectly.

A quick check of the mail archives didn't reveal any scripts or 
additions to the code that provided a void or delete function. Needless 
to say I didn't cherish the idea of manually deleting 1000 invoices by 
hand through phpMyAdmin.


Here's the logic to my script.

First you have to decide if you want a record of the invoice, if so 
uncomment the lines in clear_invoice that update the cust_bill and 
cust_bill_pkg tables. Don't forget to comment out the lines above them 
that actually delete the records from those tables

The script first clears out any entries you have for the bad invoice in 
cust_pay_batch. If you aren't batching your charges this is pretty 
useless. I guess you might need to create an interface to the 
appropriate CCVS system calls to void the charge in a real time system.

Next the script does the really important part. It removes the Next Bill 
date, and if neccessary clears the setup date as well from the cust_pkg 
table. This should allow the next billing run to properly bill the 
package and not miss the valuable setup charges.

Finally the script goes after the actual invoice, it all depends on what 
you choose in the script whether it will delete or actually void.



I thought this would be a good little script to post to the list. Ivan, 
maybe it can even be added as a script in the next distro. Hopefully 
someone can take the logic I've started with and actually integrate it 
with the freeside interface and the cust_*.pm module. At the very least 
folks can use this to clear out a misbilled invoice without having to 
use phpMyAdmin or raw SQL.


--
Brian






---Script-----


#!/usr/bin/perl

# I hacked this together one night when I accidently billed customers 2 
times in a row due to a date
# change I had done to bill 1 customer in the rear by a few days.

# Brian Walters <brian at mylinuxisp.com>

# Don't ask for support, this was done to get me out of hot water it may 
not work in all cases, it might not
# work in your case particularly. Use at your own risk!!!!

use strict;
use DBI;


my $database = "freeside";
my $user = "root";
my $password = "";

# I like things like db handles to be global, it's just me.
my $dbh;


sub delete_cc_trans {
     # I'm not checking for the existence. If there's no transaction 
nothing is going to happen

     my ($invoicenum) = @_;
     $dbh->do("delete from cust_pay_batch where invnum=$invoicenum");

}


sub clear_bill_dates {
     # I have to clear the next bill date and possibly the setup date if 
it was billed too

     my ($invoicenum) = @_;
     my ($pkgnum,$recur,$setup,$sqlstring);


     my $sth = $dbh->prepare("select pkgnum, recur, setup from 
cust_bill_pkg where invnum=$invoicenum");

     $sth->execute;

     my $ary_ref = $sth->fetch;

     while ($ary_ref) {
         $pkgnum = $$ary_ref[0];
         $recur = $$ary_ref[1];
         $setup = $$ary_ref[2];
        
         # we have to make sure we don't get the sales tax entry
         if ($pkgnum!=0) {
    
             $sqlstring = "UPDATE cust_pkg SET ";

             if ($setup > 0) {
                $sqlstring = $sqlstring . "setup=0,";
             }
             $sqlstring = $sqlstring . "bill=0";
    
             $sqlstring = $sqlstring . " WHERE pkgnum=$pkgnum";
             $dbh->do($sqlstring);       
         }
         $ary_ref = $sth->fetch;
     }

}

sub clear_invoice {
     # I have to clear the next bill date and possibly the setup date if 
it was billed too

     my ($invoicenum) = @_;

# If you actually want to delete the invoice completely!
     $dbh->do("delete from cust_bill_pkg where invnum=$invoicenum");
     $dbh->do("delete from cust_bill where invnum=$invoicenum");

# If you simply want to void it but keep a record.
#    $dbh->do("update cust_bill_pkg SET recur=0,setup=0 where 
invnum=$invoicenum");
#    $dbh->do("update cust_bill SET charged=0, owed=0 where 
invnum=$invoicenum");

}


# MAIN CODE


# This line should probably be rewritten if you plan to run the script 
from another host.
$dbh = DBI->connect("DBI:mysql:$database", $user, $password)
        or die "Can't connect to $database: $DBI::errstr\n";

my $invoicenum = $ARGV[0];


# ___VERY___ limited argument checking
$invoicenum>0 or die "Bad invoice number!!\n";


print "Deleting invoice number $invoicenum\n";

delete_cc_trans ($invoicenum);
clear_bill_dates ($invoicenum);
clear_invoice ($invoicenum);   

$dbh->disconnect;