В этой статье я приведу примеры некоторых 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 '%"%';
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';
OPTIMIZE TABLE `abills`.`internet_log`;
SELECT * FROM paysys_log WHERE datetime LIKE "2023-%" ORDER BY datetime desc LIMIT 100
DELETE FROM paysys_log WHERE datetime LIKE "2023-%";
OPTIMIZE TABLE `abills`.`paysys_log`;
DELETE FROM sms_log WHERE datetime LIKE "2022-%";
OPTIMIZE TABLE `abills`.`sms_log`;
DELETE FROM ureports_log WHERE execute LIKE "2022-%";
OPTIMIZE TABLE `abills`.`ureports_log`;
DELETE FROM callcenter_calls_handler WHERE date LIKE "2020-%";
OPTIMIZE TABLE `abills`.`callcenter_calls_handler`;
У кого есть оплата (в моем случае за маршрутизатор) и не заведена услуга 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
Обнуление отчета ureports, например для тестовых рассылок:
UPDATE ureports_users_reports SET date="0000-00-00" WHERE uid=7992 AND report_id=11;
Смотрите также мои статьи:
Добавление BDCOM OLT в ABillS
Скрипт отправки СМС через Goip4 шлюз для ABillS
Скрипт смены статуса услуги для тарифов с дневным начислением