G’day krigevr.
Hope the consequences of the rugby haven’t been too hard on the brain-cells.
Your previous post crossed with mine but I’ve plundered your ideas shamelessly and come up with a script that (fingers crossed!) works for me.
Our set-up is a lot less complex than yours. Our Trust buys a single monthly plan from our DSL provider and divides it among our trust members/subscribers for a fixed monthly data ration and unlimited time/max speed. If the Trust exceeds its plan-cap it has to pay for the excess MB’s so we have to recover it from the subscriber/s who exceeded their allowances, but purely at cost. If a subscriber exceeds his allowance but others come in under theirs, as usually happens, the Trust itself doesn’t go over its cap so the excess charges levied on the subscriber goes into the Repair and Replacement Fund (or Admin’s R&R fund!)
This script will be scheduled to run at perhaps 2.00am daily and is thus effectively a report up-to and including the previous day – which is somewhat coarse but enough for our situation.
If a user has exceeded his allowance or clocked up some more over his allowance since the last ‘excess’ email, but not if he gone quiet, he gets a statement of total usage plus a warning that he’s now paying an additional 3c. MB for the excess data over his subscription. Every ‘excess’ email is echoed to me as admin.
If a user hasn’t gone over his limit he just gets a usage email at 100 MB stages, which might mean at 4-5 day intervals or even more.
IF it’s the first of the month the emails are sent regardless of usage as they are effectively reports for the full preceding month and all are copied to me. Any with excess MBs record the fact. I would like to go further and use the emails as an Invoice for excess MBs but haven’t found a way to multiply the excess by $0.03 without either getting the digits in the wrong places vis-à-vis the decimal point or creating sums like $33.3333.
The end of the ‘First-of-the-Month’ tail also resets the counters and the flags, and this seems to work. Any subsequent accounting updates with the same session id are reported correctly by a user print command from a zero start but any attempt to use the ‘close session’ command brings the script to a screeching halt with a ‘session is not active’ complaint. I don’t yet know if this is enough.
I’ve attached the script below in its ‘test’ state with the emails commented out and a couple of :put statements for testing in the terminal. I’m sure there are a lot of things that could be done more elegantly and/or efficiently and there’s probably a horrible hole or two I haven’t fallen into yet. Please feel free to comment and/or improve.
Nb. The script utilises as archives or flags several fields in the /tool user-manager user database some of which are also accessible by the user via the User-man user web pages, so beware! Either use different fields or don’t give the User access to the router.
Ps. As this thread has become a fully-fledged scripting matter I’ve posted on the scripting forum drawing attention to it. Hopefully it might form the seed for something suitable for the Scripting Wiki.
[EDIT - THIS ALMOST WORKS, BUT RETURNS TWO 'EXCESS EMAILS' THE FIRST TIME THE USER GOES OVER HIS CAP. A DIFFERENT APPROACH WHICH SOLVES THIS WILL APPEAR BELOW SHORTLY!!!]
#define the required locals
:local clientname
:local cap
:local location
:local bytesin
:local Mbytesin
:local bytesout
:local Mbytesout
:local Mbytestotal
:local email
:local date
:local month
:local Oldmonth
:local day
:local oldMbytes
:local admail
:local climail
:local excess
# create 'global' locals
:set date [/system clock get date]
:set day [:pick [/system clock get date] 4 6]
:set month [:pick [/system clock get date] 0 3]
:set email [:resolve smtp.xtra.co.nz]
:set admail "admin@xxxx.net.nz"
# Scan each user record in User-Manager
/tool user-manager user
:foreach i in=[ /tool user-manager user find subscriber=admin] do={
# and set required values for each scan
:set Oldmonth [get $i pool-name]
:set oldMbytes [get $i group-name]
:set clientname [get $i last-name]
:set cap [get $i phone]
:set bytesin [get $i download-used]
:set Mbytesin ($bytesin / 1000000)
:set bytesout [get $i upload-used]
:set Mbytesout ($bytesout / 1000000)
:set Mbytestotal ($Mbytesin + $Mbytesout)
:set climail [get $i email]
:set location [get $i location]
:put $clientname
# if the user has exceeded his limit
:if ([$Mbytestotal] >= $cap) do={
# for the first time
:if ($location = "a") do={
# and if it isn't the first of the month
:if ($day > 01) do={
:put "excess cap email sent"
:put ...
# send a notification email
#/tool e-mail send to=$climail from=$admail server=$email subject="Limit exceeded" body="
#You have exceeded your subscribed data limit for the current month.
#Usage over your subscribed data limit is charged for \$0.03c per MB.
#Date: $date
#Subscriber: $clientname
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Your monthly cap: $cap MB"
# with a copy to admin
#/tool e-mail send to=$admail from=$climail server=$email subject="Limit exceeded" body="
#$clientname has exceeded their subscribed data limit for the current month.
#Date: $date
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Monthly cap: $cap MB"
# update the total MBs used so far
/tool user-manager user set $i group-name=$Mbytestotal
# and stop the standard 100MB step report going out
set $i location="b"
}}}
# if the user has exceeded his limit
:if ([$Mbytestotal] >= $cap) do={
# but only if he has clocked up more MBs since the last excess email
:if ([$Mbytestotal] > [$oldMbytes]) do={
# and if it isn't the first of the month
:if ($day > 01) do={
:put "excess cap email sent"
:put ...
# send another excess email
#/tool e-mail send to=$climail from=$admail server=$email subject="Limit exceeded" body="
#You have exceeded your subscribed data limit for the current month.
#Usage over your subscribed data limit is charged for \$0.03c per MB.
#Date: $date
#Subscriber: $clientname
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Your monthly cap: $cap MB"
# and notification to admin
#/tool e-mail send to=$admail from=$climail server=$email subject="Limit exceeded" body="
#$clientname has exceeded their subscribed data limit for the current month.
#Date: $date
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Monthly cap: $cap MB"
# update the total MBs used so far
/tool user-manager user set $i group-name= $Mbytestotal
}}}
# see if the user has clocked up 100Mb usage since the last email report
:if ([$Mbytestotal] >= ([$oldMbytes] + 99)) do={
#but not if he's already had an excess email
:if ([$Mbytestotal] < [$cap]) do={
# and it isn't the first of the month
:if ($day > 01) do={
:put "100MB step email sent"
:put ...
# send a usage update email to the client
#/tool e-mail send to=$climail from=$admail server=$email subject="Usage report" body="
#Data usage for the current month.
#Date: $date
#Subscriber: $clientname
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Your monthly cap: $cap MB"
# update usage to date
/tool user-manager user set $i group-name=$Mbytestotal
}}}
# if it is the first of the month
:if ($day = 01) do={
# and the client has exceeded his limit
:if ([$Mbytestotal] >= $cap) do={
:set excess ($[Mbytestotal] – $cap]
:put "First of the month excess email sent"
#/tool e-mail send to=$climail from=$admail server=$email subject="Usage report" body="
#Data usage for $Oldmonth
#Date: $date
#Subscriber: $clientname
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Your monthly cap: $cap MB
#You exceeded your monthly subscription by $excess MBs @ 3c."
# with a copy to accounts
#/tool e-mail send to=$admail from=$climail server=$email subject="Excess usage report" body="
#Data usage for $Oldmonth
#Date: $date
#Subscriber: $clientname
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Monthly cap: $cap MB
#Excess MBs: $excess"
# cancel the excess flag
set $i location="a"
# but if he hasn't exceeded his limit
} else={
:put "First of the month report sent"
# send a standard usage report for the previous month
#/tool e-mail send to=$climail from=$admail server=$email subject="Usage report" body="
#Data usage for $Oldmonth
#Date: $date
#Subscriber: $clientname
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Your monthly cap: $cap MB"
# with a copy to accounts
#/tool e-mail send to=admail from=climail server=$email subject="Usage report" body="
#Data usage for $Oldmonth
#Date: $date
#Subscriber: $clientname
#Downloaded: $Mbytesin MB
#Uploaded: $Mbytesout MB
#Total: $Mbytestotal MB
#Monthly cap: $cap MB"
}
:put "month end actions"
# close off the radius session for the previous month
#/tool user-manager session close-session $i
# reset the counters
/tool user-manager user reset-counters $i
# reset the Mbytestotal
/tool user-manager user set $i group-name=0
#change the month field to the current month
/tool user-manager user set $i pool-name=$month
}
}
