expiration process

[Kristian Hoffmann]

The patch for ascend radius that works with this message can be downloaded
at http://www.softagency.co.jp/mysql/.  It might be in Japanese but
there's a link for an english version if it is.  You also need to add

  svc_acct__radius_active_flag char(1),
  svc_acct__radius_active char(1)

to your part_svc and

  radius_active char(1)

to svc_acct.

Here's the script.  You just need to run this daily from cron or something
like that.  This disables accounts after 10 days of nonpayment.  I'll post
the script that enables accounts once a payment is made tomorrow.  The
sysadmin at my work has been trying to modify cust_pay.pm so that it
reactivates accounts when a payment making cust_bill.owed <= 0 is made.
That may take a couple of days though. Ivan, can you give any suggestions
for that?  Anyway, here it is.

---disable-overdue.sh---
#!/bin/bash
DBNAME=freeside
DBUSER=dbusername
DBPASS=dbpassword
FIELD="cust_svc.svcnum,svc_acct.username,cust_bill.owed"
TABLE="cust_pkg,cust_bill,cust_svc,svc_acct"
FILTER_A="cust_pkg.custnum=cust_bill.custnum"
FILTER_B="cust_pkg.pkgnum=cust_svc.pkgnum"
FILTER_C="cust_svc.svcnum=svc_acct.svcnum"
FILTER_D="(TO_DAYS(FROM_UNIXTIME(cust_pkg.bill)) - TO_DAYS(NOW()) = 10)"
FILTER_E="(cust_bill.owed > 0)"
TMPFILE="/tmp/overdue.list"
TARGET_TABLE="svc_acct"
TARGET_FIELD="radius_active"
TARGET_VALUE=N

rm -f $TMPFILE

{
    echo "select $FIELD from $TABLE"
        echo "where $FILTER_A and $FILTER_B and $FILTER_C and $FILTER_D"
        echo "and $FILTER_E INTO OUTFILE \"$TMPFILE\";"
} | /usr/local/bin/mysql -u $DBUSER -p$DBPASS $DBNAME

cat $TMPFILE | while read SVCNUM USERNAME OWED; do
    echo "Disabling $USERNAME - Balance Overdue \$$OWED - (svcnum:
$SVCNUM)"
done

cat $TMPFILE | while read SVCNUM USERNAME OWED; do
    echo "UPDATE $TARGET_TABLE SET $TARGET_FIELD='$TARGET_VALUE'
        WHERE svcnum = $SVCNUM;"
done | /usr/local/bin/mysql -u $DBUSER -p$DBPASS $DBNAME

---disable-overdue.sh---

For some strange reason, mysql doesn't accept the password when it's run
the first time in the script so you may have to edit it by hand.

Let me know if you have problems.

-Kristian