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

Примеры изменения поля «фильтр негативного депозита» в тарифах, если что, оставлять это поле пустым вроде нельзя, ну можно указать NEG_DEPOSIT (если гостевым клиентам не передавать скорость через Radius, то ее можно указать в accel-ppp в секции shaper, например rate-limit=1000/510, она будет применятся, когда через радиус не пришли атрибуты скорости, также если гостевым клиентам выдавать отдельный ip pool, то можно его блокировать через iptables например и тогда нет необходимости использовать 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';

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

Триггер установки текущей даты в дату активации для новых услуг:

DELIMITER //
CREATE TRIGGER default_activate_date_curdate BEFORE INSERT ON internet_main
FOR EACH ROW BEGIN
  SET NEW.activate=curdate();
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;

Просмотр и очистка истории админов когда они удаляли сессии пользователей (например когда админ чтобы перезапустить accep-ppp отправил все сессии в zap и потом удалил их из zap, после этого в истории собирается очень много записей):

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;

Очистка истории завершения сессий админами:

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;

Однажды понадобилось собирать статистику и рисовать графики в Zabbix по количеству сервисов в которых отмечен сервер доступа и порт, а точнее GPON и EPON ONU по этому придумал запросы ниже (_connection_type у меня определяет тип подключения):

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;

Пример очистки таблицы internet_log:

SELECT COUNT(*) FROM internet_log where start < '2020-01-01';
DELETE FROM internet_log where start < '2020-01-01';

У кого есть оплата (в моем случае за маршрутизатор) и не заведена услуга IP:

SELECT u.uid as "UID", up.contract_date as "Дата договора", up.fio as "ФИО", uc.value as "Номер",CONCAT(d.name," ", st.name," д.", b.number, " кв.", up.address_flat) as "Адрес"
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

У кого есть списание (в моем случае за маршрутизатор) и не заведена услуга IP:

SELECT u.uid as "UID", up.contract_date as "Дата договора", up.fio as "ФИО", uc.value as "Номер",CONCAT(d.name," ", st.name," д.", b.number, " кв.", up.address_flat) as "Адрес"
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

Пример сохранения и очистки устаревших списаний:

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

Удаление устаревших таблиц s_detail:

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

Кредиты без даты (для тарифов с датой активации):

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;

Выборка IP адресов по району:

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;

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

Список тарифов Интернет:

SELECT name as "Название", month_fee as "Абонплата за месяц", period_alignment as "Выравнивание месяца"
FROM tarif_plans
WHERE status=0
AND module="Internet"
ORDER BY name ASC;

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

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

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

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;

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

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;

Мастер отчет суммы платежей по типам оплат за указанный период:

SELECT pt.name AS "Тип оплаты", SUM(sum) AS "Сумма платежей"
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

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

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

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

Больше на IT Blog

Оформите подписку, чтобы продолжить чтение и получить доступ к полному архиву.

Continue reading