[freeside] Useful script for voiding invoices

[ivan]

On Sat, Feb 03, 2001 at 11:15:58PM -0600, Brian wrote:
> 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.

May I suggest the `-d' option to freeside-bill ?

> 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.

Perhaps if you rewrite it as you describe below.

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

-- 
meow
_ivan