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";
SELECT id AS login,uid,credit,credit_date FROM users WHERE credit > 0 AND credit_date = '0000-00-00';
Disabled users with credit and without a credit reset date:
SELECT u.uid FROM users u LEFT JOIN internet_main im ON (im.uid = u.uid)
WHERE u.credit > 0 AND u.credit_date = '0000-00-00' AND im.disable != 0 AND im.disable != 5;
Users with credit and reset date:
SELECT id AS login,uid, credit AS credit, credit_date AS credit_date FROM users WHERE credit > 0 AND credit_date != '0000-00-00' ORDER BY credit_date desc;
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)
ORDER BY uid ASC;
Users who do not have an address:
SELECT u.id as login, u.uid, up.location_id FROM users u inner join users_pi as up on u.uid = up.uid WHERE up.location_id = 0;
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;
SELECT COUNT(*) AS cnt, users.id AS login, users.uid FROM users LEFT JOIN internet_main ON users.uid = internet_main.uid GROUP BY users.uid HAVING count(*) > 1;
Who has several IPTV services:
SELECT COUNT(*) AS cnt, users.id AS login, users.uid FROM users LEFT JOIN iptv_main ON users.uid = iptv_main.uid GROUP BY users.uid HAVING count(*) > 1;
Duplicated MAC and IP addresses in users services:
SELECT COUNT(*) AS cnt, `cid` FROM `internet_main` GROUP BY `cid` ORDER BY `cnt` DESC LIMIT 30;
SELECT COUNT(*) AS cnt, `ip` FROM `internet_main` GROUP BY `ip` ORDER BY `cnt` DESC LIMIT 30;
SELECT uid FROM `internet_main` WHERE ip=2886733761
Duplicated ONUs (by mac/SN and description):
SELECT COUNT(*) AS cnt, `onu_mac_serial` FROM `equipment_pon_onu` GROUP BY `onu_mac_serial` ORDER BY `cnt` DESC LIMIT 50;
SELECT COUNT(*) AS cnt, `onu_desc` FROM `equipment_pon_onu` GROUP BY `onu_desc` ORDER BY `cnt` DESC LIMIT 50;
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=0;
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;
Example of a mass indication of IP Pool negative deposit in tariffs:
UPDATE tarif_plans SET neg_deposit_ippool="149" where neg_deposit_ippool="";
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='';
Examples of changing the “negative deposit filter” field in the tariffs, if anything, it seems impossible to leave this field empty, well, you can specify NEG_DEPOSIT (if the rate is not transmitted to guest clients via Radius, then it can be specified in accel-ppp in the shaper section, for example rate-limit=1000/510, it will be applied when the rate is not received through the radius speed attributes, also if you give guest clients a separate ip pool, then you can block it through iptables for example, and then there is no need to use ipset):
UPDATE tarif_plans SET neg_deposit_filter_id='RAD: L4-Redirect=1, L4-Redirect-ipset=denyip, PPPD-Downstream-Speed-Limit=1000, PPPD-Upstream-Speed-Limit=500' WHERE neg_deposit_filter_id='NEG_DEPOSIT';
UPDATE tarif_plans SET neg_deposit_filter_id='RAD: ERX-Ingress-Policy-Name=svc-filter-in-nomoney, ERX-Egress-Policy-Name=1Mbps' WHERE neg_deposit_filter_id='RAD: L4-Redirect=1, L4-Redirect-ipset=denyip, PPPD-Downstream-Speed-Limit=1000, PPPD-Upstream-Speed-Limit=500';
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;
Users without a cash account:
SELECT id AS login,uid FROM users WHERE bill_id = 0;
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';
SELECT count(*) FROM abills.internet_main im
LEFT JOIN users u ON (im.uid = u.uid)
LEFT JOIN tarif_plans tp ON (tp.tp_id = im.tp_id)
WHERE tp.day_fee>0 AND tp.abon_distribution=0
AND tp.period_alignment=0 AND im.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';
SELECT count(*) FROM abills.internet_main im
LEFT JOIN users u ON (im.uid = u.uid)
LEFT JOIN tarif_plans tp ON (tp.tp_id = im.tp_id)
WHERE tp.month_fee>0 AND tp.abon_distribution=0
AND tp.period_alignment=0 AND im.activate = 0000-00-00
AND u.disable=0 AND im.disable=0;
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 ;
Trigger for setting the current date to the activation date for new services:
DELIMITER //
CREATE TRIGGER default_activate_date_curdate BEFORE INSERT ON internet_main
FOR EACH ROW BEGIN
SET NEW.activate=curdate();
END;
//
DELIMITER ;
Users who have email in contacts:
SELECT * FROM users_contacts WHERE value LIKE "%@%";
SELECT value AS login,uid FROM users_contacts WHERE value LIKE "%@%";
An example of turning debug on and off for a specific IPTV service (you can also use the “Settings” – “Television” menu):
SELECT * FROM iptv_services LIMIT 10
UPDATE iptv_services SET debug=4 WHERE id=1;
UPDATE iptv_services SET debug=0 WHERE id=1;
If you use Internet +, and in the tariff settings there is a tariff in which Dv is indicated, then you can change it like this:
update tarif_plans set module='Internet' where module='Dv';
View NAS passwords (where “ixnfo.com” is the value of $conf{secretkey} from config.pl):
select decode(mng_password,"ixnfo.com") from nas;
Users who have quotes in the name (I noticed that Privat24 does not accept such a character when paying via the Internet and such users will not be able to make payment):
SELECT * FROM users_pi where fio like '%"%';
An example of changing the field length with VARCHAR(64):
ALTER TABLE `cablecat_cables` CHANGE `name` `name` VARCHAR(128) NOT NULL;
View IP addresses for the selected IP POOL (once I encountered a problem that when adding IP POOL, IP addresses outside the network mask range were added to the table):
SELECT INET_NTOA(ip) FROM ippools_ips where ippool_id=149;
Change SN for an item (Storage module):
UPDATE storage_sn SET serial = '485754431708892C' WHERE serial = 'vgYsEnmpjf';
Users whose field “Mobile phone is not equal to 10 characters” (once noticed that operators can enter several numbers in one field with and without a city code, etc., and my SMS sending script used only the correct numbers):
SELECT *, LENGTH(value) as length, uc.uid as login
FROM users_contacts uc
WHERE LENGTH(value)<>10
AND type_id=1;
SELECT *, LENGTH(value) as length, uc.uid as login
FROM users_contacts uc
WHERE LENGTH(value)<>10
AND type_id=2;
SELECT u.id as login, u.uid, uc.value, LENGTH(value) as length
FROM users_contacts uc
LEFT JOIN users u ON (u.uid = uc.uid)
LEFT JOIN users_pi u_pi ON (u_pi.uid = uc.uid)
WHERE uc.type_id=1
AND u_pi._actual_mob='1'
AND LENGTH(value)<>10;
SELECT count(*)
FROM users_contacts uc
LEFT JOIN users u ON (u.uid = uc.uid)
LEFT JOIN users_pi u_pi ON (u_pi.uid = uc.uid)
WHERE uc.type_id=1
AND u_pi._actual_mob='1'
AND LENGTH(value)<>10;
Once I discovered in the Storage module that one product is not located by its serial number and cannot be added since it already exists in the database, so I solved the problem by manually deleting it from the database (and then added it as usual through abills):
SELECT * FROM storage_sn WHERE serial="414C434CF220F92E";
SELECT * FROM storage_incoming_articles WHERE id=593;
DELETE FROM storage_sn WHERE serial="414C434CF220F92E" AND id="593";
Number of ONUs on ports:
SELECT COUNT(*) AS cnt, `port_id` FROM `equipment_pon_onu` GROUP BY `port_id` ORDER BY `cnt` DESC;
SELECT COUNT(*) FROM (SELECT COUNT(*) AS cnt, `port_id` FROM `equipment_pon_onu` GROUP BY `port_id` having COUNT(*)>125) AS T;
Viewing and clearing the history of admins when they deleted user sessions (for example, when the admin sent all sessions to zap to restart accep-ppp and then deleted them from zap, after that a lot of records are collected in the history):
SELECT count(*) FROM admin_actions WHERE datetime LIKE "2020-09-08 04:%" AND action_type=13;
SELECT count(*) FROM admin_actions WHERE action_type=13;
SELECT count(*) FROM admin_actions WHERE action_type=13 AND aid=14;
DELETE FROM admin_actions WHERE datetime LIKE "2020-09-08 04:%" AND action_type=13;
DELETE FROM admin_actions WHERE action_type=13;
Clearing the history of ending sessions by admins:
SELECT count(*) FROM admin_actions WHERE action_type=15
SELECT * FROM admin_actions WHERE action_type=15 LIMIT 500
DELETE FROM admin_actions WHERE action_type=15;
Once it was necessary to collect statistics and draw graphs in Zabbix on the number of services in which the access server and port are marked, or rather GPON and EPON ONU, for this reason, I came up with requests below (_connection_type determines the type of connection for me):
SELECT count(*) FROM internet_main im
LEFT JOIN users_pi up ON (im.uid = up.uid)
WHERE im.nas_id=0
AND im.port=0
AND up._connection_type IN(9,10)
AND im.disable=0;
SELECT count(*) FROM internet_main im
LEFT JOIN users_pi up ON (im.uid = up.uid)
WHERE im.nas_id !=0
AND im.port !=0
AND up._connection_type IN(9,10)
AND im.disable=0;
An example of clearing the internet_log table:
SELECT COUNT(*) FROM internet_log where start < '2020-01-01';
DELETE FROM internet_log where start < '2020-01-01';
Who has a payment (in my case for a router) and does not have an IP service:
SELECT u.uid as "UID", up.contract_date as "Contract_ID", up.fio as "FIO", uc.value as "Number",CONCAT(d.name," ", st.name," d.", b.number, " kv.", up.address_flat) as "Address"
FROM users u
JOIN users_pi up ON u.uid = up.uid
LEFT JOIN users_contacts uc ON u.uid = uc.uid AND uc.type_id = 1
LEFT JOIN builds b ON up.location_id = b.id
LEFT JOIN streets st ON b.street_id = st.id
LEFT JOIN districts d ON st.district_id = d.id
LEFT JOIN payments p ON p.uid = u.uid
WHERE u.uid NOT IN (SELECT uid FROM internet_main)
AND u.uid NOT IN (SELECT uid FROM iptv_main)
AND u.uid NOT IN (SELECT uid FROM cams_main)
AND p.method IN (19,20)
GROUP BY u.uid
Who has a fees (in my case, for a router) and does not have an IP service:
SELECT u.uid as "UID", up.contract_date as "Contract_ID", up.fio as "FIO", uc.value as "Number",CONCAT(d.name," ", st.name," d.", b.number, " kv.", up.address_flat) as "Address"
FROM users u
JOIN users_pi up ON u.uid = up.uid
LEFT JOIN users_contacts uc ON u.uid = uc.uid AND uc.type_id = 1
LEFT JOIN builds b ON up.location_id = b.id
LEFT JOIN streets st ON b.street_id = st.id
LEFT JOIN districts d ON st.district_id = d.id
LEFT JOIN fees f ON f.uid = u.uid
WHERE u.uid NOT IN (SELECT uid FROM internet_main)
AND u.uid NOT IN (SELECT uid FROM iptv_main)
AND u.uid NOT IN (SELECT uid FROM cams_main)
AND f.method IN (7,8)
GROUP BY u.uid
An example of saving and clearing obsolete fees:
mysqldump -u root -p abills fees > abills.fees-dump.sql
SELECT count(*) FROM fees WHERE date LIKE "2018-%";
DELETE FROM fees WHERE date LIKE "2018-%";
Removing obsolete s_detail tables:
DROP TABLE s_detail_2020_07_31;
DROP TABLE s_detail_2020_08_31;
DROP TABLE s_detail_2020_09_30;
DELETE FROM s_detail WHERE nas_id=11;
OPTIMIZE TABLE `abills`.`s_detail`;
Credits without a date (for tariffs with an activation date)
SELECT u.credit, u.uid, u.id as login FROM users u
LEFT JOIN internet_main im ON (u.uid = im.uid)
LEFT JOIN tarif_plans tp ON (im.tp_id = tp.tp_id)
WHERE u.credit > 0 AND u.credit_date = '0000-00-00'
AND tp.month_fee>0 AND tp.abon_distribution=0 AND tp.period_alignment=0
ORDER BY u.credit ASC;
Selection of IP addresses by district:
SELECT INET_NTOA(ip) FROM internet_main im
LEFT JOIN users u ON (u.uid = im.uid)
LEFT JOIN users_pi u_pi ON (u_pi.uid = im.uid)
LEFT JOIN builds bu ON (u_pi.location_id = bu.id)
LEFT JOIN streets s ON (bu.street_id = s.id)
LEFT JOIN districts d ON (s.district_id = d.id)
WHERE INET_NTOA(`ip`) LIKE '172.19.%'
AND d.id=18;
An example of a selection of mobile phones according to a tariff plan, with the service status “Active” and “Deposit too small” (for example, I later used this list to send messages to Viber via TurboSMS):
SELECT uc.value
FROM users_contacts uc
LEFT JOIN internet_main im ON (uc.uid = im.uid)
LEFT JOIN tarif_plans tp ON (im.tp_id = tp.tp_id)
WHERE uc.type_id=1
AND (im.disable=0 OR im.disable=5)
AND tp.name="ixnfo.com 30";
SELECT uc.value
FROM users_contacts uc
LEFT JOIN iptv_main iptvm ON (uc.uid = iptvm.uid)
LEFT JOIN tarif_plans tp ON (iptvm.tp_id = tp.tp_id)
WHERE uc.type_id=1
AND (iptvm.disable=0)
AND tp.name="ixnfo.com IPTV 30";
SELECT uc.value
FROM users_contacts uc
LEFT JOIN internet_main im ON (uc.uid = im.uid)
LEFT JOIN tarif_plans tp ON (im.tp_id = tp.tp_id)
LEFT JOIN users_pi u_pi ON (u_pi.uid = uc.uid)
LEFT JOIN builds bu ON (u_pi.location_id = bu.id)
LEFT JOIN streets s ON (bu.street_id = s.id)
LEFT JOIN districts d ON (s.district_id = d.id)
WHERE uc.type_id=1
AND (im.disable=0 OR im.disable=5)
AND tp.name="ixnfo.com 30"
AND d.name="New York";
List of Internet tariffs:
SELECT name as "Name", month_fee as "Monthly subscription fee", period_alignment as "Month alignment"
FROM tarif_plans
WHERE status=0
AND module="Internet"
ORDER BY name ASC;
List of user mobile phones with specified tariff and addresses by house range:
SELECT uc.value
FROM users_contacts uc
LEFT JOIN internet_main im ON (uc.uid = im.uid)
LEFT JOIN tarif_plans tp ON (im.tp_id = tp.tp_id)
LEFT JOIN users_pi u_pi ON (u_pi.uid = uc.uid)
LEFT JOIN builds bu ON (u_pi.location_id = bu.id)
LEFT JOIN streets s ON (bu.street_id = s.id)
LEFT JOIN districts d ON (s.district_id = d.id)
WHERE uc.type_id=1
AND (im.disable=0 OR im.disable=5)
AND tp.name LIKE "Tafir%"
AND s.name="StreetName"
AND bu.number BETWEEN 3 AND 68
AND d.name="New York";
The number of active Internet services users by groups (wrote when in paysys different counterparties of the same payment system were linked to groups and it was necessary to look often):
SELECT g.name AS "Group name", count(im.uid) AS "Active services"
FROM groups g
LEFT JOIN users u ON (u.gid = g.gid)
LEFT JOIN internet_main im ON (u.uid = im.uid)
WHERE im.disable=0
GROUP BY g.name;
Viewing the settings of payment system counterparties for groups:
SELECT g.name, pms.merchant_name FROM paysys_merchant_to_groups_settings pmtgs
LEFT JOIN paysys_merchant_settings pms ON (pms.id = pmtgs.merch_id)
LEFT JOIN groups g ON (g.gid = pmtgs.gid)
WHERE pmtgs.paysys_id=1
ORDER BY g.name ASC;
Master report of payment amounts by payment type for the specified period:
SELECT pt.name AS "Payment type", SUM(sum) AS "Payment amount"
FROM payments p
LEFT JOIN payments_type pt ON (pt.id = p.method)
WHERE DATE_FORMAT(date, '%Y-%m-%d')>='%DATE_FROM%' AND DATE_FORMAT(date, '%Y-%m-%d')<='%DATE_TO%'
AND p.method BETWEEN 135 AND 139
GROUP BY 1
See also my articles:
Adding BDCOM OLT on ABillS
Script to send SMS via Goip4 gateway for ABillS
Service status change script for tariffs with daily charge