In 2018, I ran into a problem when the user ran out of money on the account, at midnight he turned off the billing, the user immediately made a payment, the money was debited and the service turned on, and at 1 am it was turned off again, because the billing system tried to withdraw the monthly fee again. I’ll just post a simple script here that allows you to receive email notifications with a list of users who made a payment at night between 00:00 and 01:01, so that later you can manually correct it.
Script content:
mysql -D abills -u ixnfo -pPASSWORD -Bse "SELECT pay.* FROM internet_main im LEFT JOIN payments pay ON (pay.uid=im.uid) WHERE pay.date BETWEEN '$today' AND '$today 01:01:00' AND im.disable=5;"
The -s switch allows you to display less information, -B makes it more readable, -e executes the SQL query.
I added the script to /etc/crontab:
MAILTO=test@ixnfo.com
10 1 * * * root /scripts/check_pays_from_00_to_01.sh
Thus, if the script does not find anything, then the email will not come, if it does, it will be sent to the root user at 01:10.
I also changed the date in some payments so that they do not appear in the script output, these payments were added when migrating from self-written billing:
SELECT * FROM payments LIMIT 300
SELECT * FROM payments WHERE date LIKE "0000-00-00%" LIMIT 300
SELECT * FROM payments WHERE date="0000-00-00 00:00:00" LIMIT 300
SELECT * FROM payments WHERE dsc="ABon compenstation" LIMIT 300
SELECT * FROM payments WHERE inner_describe="Migration" LIMIT 300
UPDATE payments SET date='2018-07-11 05:59:41' WHERE date="0000-00-00 00:00:00" AND dsc="ABon compenstation";
See also my articles:
Redirecting mail for the root user
My other articles about ABillS