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

Поиск логинов в кириллице:

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

Поиск логинов с пробелом:

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

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

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 id AS login,uid,credit,credit_date FROM users WHERE credit > 0 AND credit_date = '0000-00-00';

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

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;

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

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;

Пользователи без сервисов:

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);

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

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;

Количество сервисов для пользователей и количество сервисов в которых указана маска 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 * FROM internet_main WHERE filter_id !="";

Сервисы пользователей с индивидуальной скоростью (приоритет выше чем у тарифного плана):

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

У кого несколько IPTV услуг:

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; 

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

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

Чтобы абонентов не отключало если на счету 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='';

Пример изменения поля «фильтр негативного депозита» в тарифах:

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

Просмотр всего списка платежей и по конкретному типу (например 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;

Пример изменения IP адреса в услуге клиента:

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

Смотрите также мой скрипт 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;

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

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;

Пользователи без денежного счёта:

SELECT id AS login,uid FROM users WHERE bill_id = 0;

Счета пользователей (по диапазонам):

SELECT
CASE
  WHEN b.deposit < 0 THEN "1. Меньше 0"
  WHEN b.deposit >= 0  AND b.deposit < 100 THEN "2. От 0 до 100"
  WHEN b.deposit >= 100  AND b.deposit < 500 THEN "3. От 100 до 500"
  ELSE "4. Больше 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;

Количество сервисов для пользователей с определенным статусом (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», он ставит статус «Слишком маленький депозит» на услугах с дневным начислением у которых не хватает депозит+кредит для списания, можно еще добавить deleted=0. (По умолчанию «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;

Очистка значений PIN в IPTV услугах (например при использовании модуля TrinityTV значение PIN не нужно хранить, иначе при смене тарифа или изменениях услуги будет ошибка что PIN устарел):

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

Очистка таблицы с историей ONU:

TRUNCATE TABLE equipment_pon_onu;

Придумал sql запрос, который в услуге клиента ставит определенный статус, назвал «Платная активация», если от даты активации прошло больше 90 дней, то есть клиент не пользовался услугой 2 месяца и статус услуги «Отключено», «Приостановление» или «Слишком маленький депозит», пример:

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;

При необходимости можно добавить SQL запросы в /etc/crontab или в ABillS меню «Настройка» — «Расписание», указав необходимое количество выполнения.

Чтобы заполнить номер договора номером uid и заполнить дату договора датой регистрации клиента:

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 который при регистрации пользователя автоматически заполняет дату договора текущей датой и номер контракта равный 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 ;

Смотрите также мою статью:
Скрипт отправки СМС через Goip4 шлюз для ABillS

Оставьте комментарий

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