expiration process

[Kristian Hoffmann]

I just realized I had an error in that.

  svc_acct__radius_active_flag char(1),
  svc_acct__radius_active char(1)
should be
  svc_acct__active_flag char(1),
  svc_acct__active char(1)
and
  radius_active char(1)
should be
  active char(1)

Sorry about that,

-Krstian


On Tue, 6 Jul 1999, Kristian Hoffmann wrote:

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