SQL queries for ABillS

In this article I will give examples of some SQL queries for the base of the ABillS billing system.

The first step is to connect to the MySQL server and go to the abills database (or you can use phpmyadmin or an external MySQL client):

mysql -u root
use abills;

To specify DNS and clear all IP POOLs (Settings>Access Server>IP POOLs), you can sql such requests:

update ippools SET dns='8.8.8.8,8.8.4.4';
update ippools SET dns='';

You can change the login to the user like this:

update users set id='new_login' where id='old_Login';

Count number of users:

SELECT count(*) FROM users;

Number of users with the specified credit without the date of zeroing:

SELECT count(*) FROM users WHERE credit>0 AND credit_date="0000-00-00";

Number of users with a specified discount without the end date of the discount:

SELECT count(*) FROM users WHERE reduction>0 AND reduction_date="0000-00-00";

The number of users who have a minus in the credit field (and you need to put a credit without a minus):

SELECT count(*) FROM users WHERE credit LIKE '%-%';

Number of users with the specified activation date for the account:

SELECT count(*) FROM users where activate<>0000-00-00;

The number of services for users and the number of services in which the mask is specified 255.255.255.255:

SELECT COUNT(*) FROM internet_main;
SELECT count(*) FROM internet_main where netmask=4294967295;

Number of services in which the mask is not 255.255.255.255:

SELECT count(*) FROM internet_main where netmask<>4294967295;

Number of services with no activation date:

SELECT count(*) FROM internet_main WHERE activate=0000-00-00;

Number of services in which the access server is not specified:

SELECT count(*) FROM internet_main WHERE nas_id=0;

Viewing accounts in which several services are added:

SELECT COUNT(*) AS cnt, `uid` FROM `internet_main` GROUP BY `uid` ORDER BY `cnt` DESC;
SELECT COUNT(*) AS cnt, `uid` FROM `internet_main` GROUP BY `uid` having COUNT(*)>1;
SELECT COUNT(*) FROM (SELECT COUNT(*) AS cnt, `uid` FROM `internet_main` GROUP BY `uid` having COUNT(*)>1) AS T;

Number of accounts in which the activation date is specified:

SELECT count(*) FROM users where activate<>0000-00-00;

Number of accounts in which the group is not indicated:

SELECT count(*) FROM users WHERE gid=0;

View users by group and change group:

SELECT * FROM users WHERE gid=79;
UPDATE users SET gid=28 WHERE gid=79;

To ensure that users do not disconnect if on account 0 and the tariff is paid:

SELECT * FROM tarif_plans where credit=0.00 LIMIT 200;
UPDATE tarif_plans SET credit='0.01' where credit=0.00;

Counting the number of tariffs for which it is not permitted to set a discount and include a discount on all tariffs:

SELECT count(*) FROM tarif_plans WHERE reduction_fee=0;
UPDATE tarif_plans SET reduction_fee=1 WHERE reduction_fee=0;

Tariffs in which no action is chosen with insufficient deposit, and the rates in which “Suspension” is selected:

SELECT * FROM tarif_plans WHERE small_deposit_action=0;
SELECT count(*) FROM tarif_plans WHERE small_deposit_action='-1';

Tariffs in which the negative deposit filter is specified or not specified:

SELECT count(*) FROM tarif_plans WHERE neg_deposit_filter_id='NEG_DEPOSIT';
SELECT count(*) FROM tarif_plans WHERE neg_deposit_filter_id='';
SELECT * FROM tarif_plans WHERE neg_deposit_filter_id='';

View the entire list of payments and for a specific type (for example, 65 this is Privat-Terminal):

SELECT * FROM payments LIMIT 750;
SELECT * FROM payments WHERE method=65;

View logins and passwords of users in decrypted form (“secretkey” look in config.pl):

SELECT id, DECODE(password, 'secretkey') FROM users;
SELECT id, DECODE(password, 'secretkey') FROM users WHERE uid=11;

View logins and passwords of administrators in decrypted form (“secretkey” look in config.pl):

SELECT id, name, DECODE(password, 'secretkey') FROM admins;

Number of Zap Sessions:

SELECT count(*) FROM internet_online WHERE status=2;

Number of guest sessions:

SELECT count(*) FROM internet_online where guest=1;

Number of all sessions:

SELECT count(*) FROM internet_online WHERE (status=1 or status>=3);

Number of sessions without guest and Zap:

SELECT count(*) FROM internet_online WHERE (status=1 or status>=3) AND guest=0;

Sample IP from online sessions:

SELECT INET_NTOA(framed_ip_address) FROM internet_online WHERE (status=1 or status>=3) AND guest=0;

See also my ipset script – The script for adding IP addresses from a file to ipset

Database size:

SELECT SUM( data_length + index_length ) AS 'size' FROM information_schema.TABLES WHERE table_schema = 'abills' LIMIT 1;

The total credit amount of all users, the amount of the deposit, the amount of debt:

SELECT SUM(credit) FROM users;
SELECT SUM(deposit) FROM bills WHERE deposit>0;
SELECT SUM(deposit) FROM bills WHERE deposit<0;

Number of services for users with a certain status (0 – Active, 1 – Disabled, 2 – Not activated, 3 – Suspension, 4 – Disabled: Non-payment, 5 – Too small deposit):

SELECT count(*) FROM internet_main WHERE disable=0;

Services with an unspecified CID or IP field:

SELECT * FROM internet_main WHERE cid='';
SELECT * FROM internet_main WHERE ip=0;

Changing phone format:

UPDATE users_pi SET phone=REPLACE(phone, '+38050', '050') WHERE phone like '+38050%';

Counting users by additional fields:

SELECT count(*) FROM users_pi WHERE _connection_type=1;
SELECT count(*) FROM users_pi WHERE _connection_type IN(2,3);

Counting active users by additional fields:

SELECT count(*) FROM internet_main im LEFT JOIN users u ON (im.uid = u.uid) LEFT JOIN users_pi u_pi ON (im.uid = u_pi.uid) WHERE u_pi._connection_type=1 AND u.disable=0 AND im.disable=0;

Number of services with daily rates and the indicated activation date (where tp_id is your tariffs):

SELECT * FROM internet_main WHERE tp_id IN(2,4,8,9,10,63,64,65) AND activate !='0000-00-00';

Number of services with non-daily rates and without the indicated activation date:

SELECT * FROM internet_main WHERE tp_id NOT IN(2,4,8,9,10,63,64,65,106,107,109) AND activate='0000-00-00';

An example of deletion of the activation date for services with a daily tariff (noted that the script periodic, at daily rates with the specified activation date, drives users into the negative, and with the not specified puts the status “Too little deposit”):

UPDATE internet_main SET activate='0000-00-00' WHERE tp_id IN(2,4,8,9,10,63,64,65);

I wrote the following SQL query to perform in Cron before the “periodic daily” script, it puts the “Too small deposit” status on services with daily charges that do not have enough deposit + credit for write-off. (By default, “periodic daily” makes the write-off a minus and leaves the status “Active”):

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;

In new versions of abills, the rights to “Do not display a deposit” are changed to “Show a deposit”, and “registration requires additional confirmation”, “registration does not require additional confirmation”, that is, in the case of an update, they will need to be changed by administrators to the contrary:

REPLACE INTO admin_permits (aid, section, actions) 
SELECT aid, 0, 12
FROM admins
WHERE aid > 3;

REPLACE INTO admin_permits (aid, section, actions) 
SELECT aid, 0, 13
FROM admins
WHERE aid > 3;

Viewing users with services with the status “Active”, tariff with a distributed monthly fee, no money and disabled account:

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.month_fee>0 AND abon_distribution=1 AND b.deposit+u.credit<(tp.month_fee/30) AND im.disable=0 AND u.disable=1;

Setting the “Too Small Deposit” status to users with services with the “Active” status, tariff with a distributed monthly fee, no money and a disabled account:

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.month_fee>0      
AND abon_distribution=1
AND b.deposit+u.credit<(tp.month_fee/30)
AND im.disable=0
AND u.disable=1;

Viewing users with services with the status “Active”, a tariff with a monthly charge, no money and a disabled account:

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.month_fee>0 AND abon_distribution=0 AND b.deposit+u.credit<tp.month_fee AND im.disable=0 AND u.disable=1;

Setting the “Too small deposit” status to users with services with the “Active” status, with a monthly charge, no money and a disabled account:

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.month_fee>0      
AND abon_distribution=0
AND b.deposit+u.credit<tp.month_fee
AND im.disable=0
AND u.disable=1;

See also my article:
Script to send SMS via Goip4 gateway for ABillS

Подписаться на IT Blog (RU) по Email
Subscribe to IT Blog (EN) by Email

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a Reply