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

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

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)
ORDER BY uid ASC;

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

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; 

Дублированные MAC и IP адреса в услугах клиентов:

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=X

Дублированные ONU (по mac/SN и описанию):

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;

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

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;

Пример массового указания IP Pool негативного депозита в тарифах:

UPDATE tarif_plans SET neg_deposit_ippool="149" where neg_deposit_ippool="";

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

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

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

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

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;

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

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

SELECT * FROM users_contacts WHERE value LIKE "%@%";
SELECT value AS login,uid FROM users_contacts WHERE value LIKE "%@%";

Пример включения и выключение debug для определенного IPTV сервиса (также можно через меню «Настройка» — «Телевидение»):

SELECT * FROM iptv_services LIMIT 10
UPDATE iptv_services SET debug=4 WHERE id=1;
UPDATE iptv_services SET debug=0 WHERE id=1;

Если используется Internet+, а в настройках тарифов есть тариф в котором указан Dv, то можно изменить так:

update tarif_plans set module='Internet' where module='Dv';

Просмотр паролей NAS (где «ixnfo.com» это значение $conf{secretkey} из config.pl):

select decode(mng_password,"ixnfo.com") from nas;

Пользователи у которых в имени есть кавычки (я заметил что Приват24 не принимает такой символ при оплате через интернет и такие пользователи не смогут совершить оплату):

SELECT * FROM users_pi where fio like '%"%';

Пример изменения длины поля с VARCHAR(64):

ALTER TABLE `cablecat_cables` CHANGE `name` `name`  VARCHAR(128) NOT NULL;

Просмотр IP адресов для выбранного IP POOL (однажды столкнулся с проблемой что при добавлении IP POOL, в таблицу добавились IP адреса вне диапазона маски сети):

SELECT INET_NTOA(ip) FROM ippools_ips where ippool_id=149;

Изменение SN для товара (модуль Storage):

UPDATE storage_sn SET serial = '485754431708892C' WHERE serial = 'vgYsEnmpjf';

Пользователи у которых поле «Мобильный телефон не равно 10 символов» (однажды заметил что операторы могут заводить по несколько номеров в одно поле и с кодом города и без него и т.д., а мой скрипт рассылки смс использовал только правильные номера):

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;

Однажды обнаружил в модуле Склад, что один товар не находится по серийному номеру и добавить его нельзя так как он уже существует в базе данных, по этому решил проблему ручным удалением из базы данных (а потом добавил его как обычно через 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";

Количество ONU на портах:

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;

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

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

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