SQL запросы для ABillS

В этой статье я приведу примеры некоторых SQL запросов для базы биллинговой системы ABillS.

Первым делом подключимся к MySQL серверу и перейдем к базе abills (либо можно использовать phpmyadmin или внешний MySQL клиент):

mysql -u root
use abills;

Указать DNS и очистить во всех IP POOLs (Настройка>Сервер доступа>IP POOLs) можно sql такими запросами:

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

Изменить логин пользователю можно так:

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

Посчитать количество пользователей:

SELECT count(*) FROM users;

Количество пользователей с указанным кредитом без даты обнуления кредита:

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

Количество пользователей у которых в поле кредит есть минус (а кредит нужно ставить без минуса):

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

Количество пользователей с указанной скидкой без даты окончания скидки:

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

Количество пользователей с указанной датой активации для учетной записи:

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

Количество сервисов для пользователей и количество сервисов в которых указана маска 255.255.255.255:

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

Количество сервисов в которых маска не равна 255.255.255.255:

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

Количество сервисов в которых не указана дата активации:

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

Количество сервисов в которых не указан сервер доступа:

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

Просмотр учетных записей в которых добавлено несколько услуг:

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(*) FROM users where activate<>0000-00-00;

Количество учетных записей в которых не указана группа:

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

Просмотр пользователей по группе и изменение группы:

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

Чтобы абонентов не отключало если на счету 0 и тариф оплачен:

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

Подсчет количества тарифов на которых не разрешено устанавливать скидку и включение скидки на всех тарифах:

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

Тарифы в которых не выбрано действие при недостаточном депозите, и тарифы в котором выбрано «Приостановление»:

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

Тарифы в которых указан или не указан фильтр негативного депозита:

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

Просмотр всего списка платежей и по конкретному типу (например 65 это Privat-Terminal):

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

Просмотр логинов и паролей пользователей в расшифрованном виде («secretkey» смотрим в config.pl):

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

Просмотр логинов и паролей администраторов в расшифрованном виде («secretkey» смотрим в config.pl):

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

Количество Zap сессий:

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

Количество гостевых сессий:

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

Количество всех сессий:

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

Количество сессий без гостевых и Zap:

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

Выборка IP из онлайн сессий:

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

Смотрите также мой скрипт ipset — Скрипт добавления IP адресов из файла в ipset

Размер базы данных:

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

Общая сумма кредита всех пользователей, сумма депозита, сумма долга:

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

Количество сервисов для пользователей с определенным статусом (0 — Активно, 1 — Отключено, 2 — Не активизирован, 3 — Приостановление, 4 — Отключено: Неуплата, 5 — Cлишком маленький депозит):

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

Сервисы с неуказанным полем CID или IP:

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

Изменение формата телефонов:

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

Подсчет пользователей по дополнительным полям:

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

Подсчет активных пользователей по дополнительным полям:

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;

Количество услуг с дневными тарифами и указанной датой активации (где tp_id — ваши тарифы):

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

Количество услуг с не дневными тарифами и без указанной даты активации:

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 internet_main WHERE tp_id NOT IN(2,4,8,9,10,63,64,65,106,107,109) AND activate='0000-00-00';

Пример удаления даты активации на услугах с дневным тарифом (заметил что скрипт periodic, на дневных тарифах с указанной датой активации, загоняет пользователей в минус, а с не указанной ставит статус «Слишком маленький депозит»):

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

Следующий SQL запрос я написал чтобы выполнять в Cron перед скриптом «periodic daily», он ставит статус «Слишком маленький депозит» на услугах с дневным начислением у которых не хватает депозит+кредит для списания. (По умолчанию «periodic daily» делает списание в минус и оставляет статус «Активно»):

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;

В новых версиях abills права «Не отображать депозит» изменено на «Отображать депозит», а «регистрация требует дополнительного подтверждения», на «регистрация не требует дополнительного подтверждения», то есть в случае обновления, нужно будет изменить их у администраторов на обратные:

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;

Просмотр пользователей с услугами со статусом «Активно», тарифом с распределенной абонплатой, без денег и отключенной учетной записью:

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;

Установка статуса «Слишком маленький депозит» пользователям с услугами со статусом «Активно», тарифом с распределенной абонплатой, без денег и отключенной учетной записью:

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;

Просмотр пользователей с услугами со статусом «Активно», тарифом с месячным начислением, без денег и отключенной учетной записью:

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;

Установка статуса «Слишком маленький депозит» пользователям с услугами со статусом «Активно», тарифом с месячным начислением, без денег и отключенной учетной записью:

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;

Смотрите также мою статью:
Скрипт отправки СМС через Goip4 шлюз для 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.

Добавить комментарий