---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@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;