Since in the ABillS bilingual system on tariffs with daily accrual, when the user has no money in the account, the status of the service does not automatically change to Too little deposit, and the customers are driven into a negative balance and then only turned off, I had to make a script from a SQL query and add it to Cron, as there were often cases when users complained that they were driven into a minus.
First, let’s see the result of executing the SELECT query, for example, in the menu “Settings” – “SQL Terminal” or create a script file and execute it:
#!/bin/bash
mysql -u root -D abills -e "SELECT im.uid, b.deposit FROM internet_main im
LEFT JOIN users u ON (im.uid = u.uid)
LEFT JOIN bills b ON (u.bill_id= b.id)
LEFT JOIN tarif_plans tp ON (tp.tp_id = im.tp_id)
WHERE tp.day_fee>0 AND b.deposit+u.credit<tp.day_fee
AND im.disable=0 AND u.reduction=0 AND u.company_id=0;"
If everything is OK and a list of users is displayed which should be disabled, then we will create a script file with an SQL query that will change their status to “Too small deposit”:
#!/bin/bash
mysql -u root -D abills -e "UPDATE internet_main im
LEFT JOIN users u ON (im.uid = u.uid)
LEFT JOIN bills b ON (u.bill_id = b.id)
LEFT JOIN tarif_plans tp ON (tp.tp_id = im.tp_id)
SET im.disable=5
WHERE tp.day_fee>0 AND b.deposit+u.credit<tp.day_fee
AND im.disable=0 AND u.reduction=0 AND u.company_id=0;"
It must be executed at midnight before the “periodic” scripts, for example, at 23:57, since the user will be disconnected immediately, and if executed later, the write-off will be performed:
57 23 * * * root /ixnfo.com/scripts/internet_main_change_status.sh
See my other articles about ABillS