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='';

Search for logins with Cyrillic characters:

SELECT * FROM users WHERE id REGEXP '^.*[а-я].*$';

Search for logins with a space:

SELECT * FROM users WHERE id LIKE '% %';

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;

Users without services:

SELECT id as login, uid
FROM users
WHERE uid NOT IN (SELECT uid FROM internet_main)
AND uid NOT IN (SELECT uid FROM iptv_main);

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;

Services in which, in the field “Filter” something is indicated:

SELECT * FROM internet_main WHERE filter_id !="";

User services with individual speed (priority is higher than the tariff plan):

SELECT * FROM internet_main WHERE speed !=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='';

An example of changing the “negative deposit filter” field in tariffs:

UPDATE tarif_plans SET neg_deposit_filter_id='RAD: L4-Redirect=1,L4-Redirect-ipset=denyip,PPPD-Downstream-Speed-Limit=3000,PPPD-Upstream-Speed-Limit=1000' WHERE neg_deposit_filter_id='NEG_DEPOSIT';

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;

An example of changing the IP address in the client’s service:

UPDATE internet_main SET ip = inet_aton('0.0.0.0') WHERE uid=5;
UPDATE internet_main SET ip = inet_aton('0.0.0.0') WHERE ip = inet_aton('10.0.0.5');

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;

Users who have more than one cash account:

SELECT COUNT(*) AS cnt, `uid` FROM `bills` GROUP BY `uid` having COUNT(*)>1;
SELECT COUNT(*) AS cnt, `uid` FROM `bills` GROUP BY `uid` ORDER BY `cnt` DESC LIMIT 10;

User cash accounts (by range):

SELECT
CASE
  WHEN b.deposit < 0 THEN "1. Less than 0"
  WHEN b.deposit >= 0  AND b.deposit < 100 THEN "2. From 0 to 100"
  WHEN b.deposit >= 100  AND b.deposit < 500 THEN "3. From 100 to 500"
  ELSE "4. More than 500"
END as deposit_value,
count(u.uid) as user_count, SUM(b.deposit) as total_sum
FROM users u
LEFT JOIN bills b ON (u.uid=b.uid)
group by deposit_value
order by deposit_value;

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, you can also add deleted = 0. (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;

Clearing PIN values in IPTV services (for example, when using the TrinityTV module, the PIN value does not need to be stored, otherwise when changing the tariff or changing the service, there will be an error that the PIN is outdated):

SELECT * FROM iptv_main WHERE NOT pin='';
UPDATE iptv_main SET pin='' WHERE NOT pin='';

Clear table with ONU history:

TRUNCATE TABLE equipment_pon_onu;

I came up with a sql request that puts a certain status in the client’s service, called “Paid Activation”, if more than 90 days have passed since the activation date, that is, the client has not used the service for 2 months and the service status is “Disabled”, “Suspension” or “Too Small deposit”, example:

SELECT im.uid, im.activate FROM internet_main im
LEFT JOIN tarif_plans tp ON (tp.tp_id = im.tp_id)
LEFT JOIN users u ON (im.uid = u.uid)
WHERE im.activate !=0000-00-00
AND DATEDIFF(CURDATE(), im.activate) > 90
AND im.disable IN (12,5,3,1)
AND u.reduction=0
AND u.company_id=0
AND tp.abon_distribution=0
AND tp.month_fee>0;
UPDATE internet_main im
LEFT JOIN tarif_plans tp ON (tp.tp_id = im.tp_id)
LEFT JOIN users u ON (im.uid = u.uid)
SET im.disable=13
WHERE im.activate !=0000-00-00
AND DATEDIFF(CURDATE(), im.activate) > 90
AND im.disable IN (12,5,3,1)
AND u.reduction=0
AND u.company_id=0
AND tp.abon_distribution=0
AND tp.month_fee>0;

If necessary, you can add SQL queries in /etc/crontab or in ABillS menu “Settings” – “Schedule”, specifying the required number of execution.

To fill in the contract number with uid and fill in the contract date with the date of registration of the client:

UPDATE users_pi SET contract_id=uid;
UPDATE users_pi pi SET pi.contract_date = (SELECT u.registration FROM users u WHERE u.uid=pi.uid );

MySQL trigger which, when registering a user, automatically fills the contract date with the current date and the contract number is equal to the UID:

DELIMITER //
CREATE TRIGGER next_contract_id BEFORE INSERT ON users_pi
FOR EACH ROW BEGIN
   IF NEW.contract_id = '' THEN
    SET NEW.contract_id=NEW.uid;
  END IF;
 
  IF NEW.contract_date = '' or NEW.contract_date = '0000-00-00' THEN
    SET NEW.contract_date=curdate();
  END IF;
 
END; 
//
DELIMITER ;

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

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

Leave a comment

Leave a Reply