Меню для создания отчёта Настройка>Мастер отчётов.
| Code Block |
|---|
| language | sql |
|---|
| title | Абоненты подключенные за предыдущий месяц |
|---|
|
SELECT u.id as login, u.uid, pi.fio, tp.name as tp_name, i.id as service_id, tp.month_fee as tp_sum, u.registration
FROM internet_main i
JOIN users u ON (i.uid = u.uid)
JOIN users_pi pi ON (i.uid = pi.uid)
JOIN tarif_plans tp ON (i.tp_id = tp.tp_id)
WHERE u.registration >= DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -1 MONTH), "%Y-%m-01")
AND u.registration <= LAST_DAY(DATE_ADD(CURRENT_DATE, INTERVAL -1 MONTH))
ORDER BY %SORT% %DESC%; |
| Code Block |
|---|
| language | sql |
|---|
| title | Список ONU с одинаковым маком |
|---|
|
SELECT onu.port_id, onu.onu_mac_serial, onu.onu_dhcp_port, p.nas_id, INET_NTOA(n.ip) AS ip
FROM equipment_pon_onu onu
LEFT JOIN equipment_pon_ports p ON (p.id=onu.port_id)
LEFT JOIN nas n ON (p.nas_id=n.id)
WHERE onu.onu_mac_serial IN
(SELECT onu_mac_serial
FROM equipment_pon_onu
GROUP BY onu_mac_serial
HAVING COUNT(*)> 1)
ORDER BY onu.onu_mac_serial |
| Code Block |
|---|
| language | sql |
|---|
| title | Пользователи с неправильными CPE_MAC |
|---|
|
SELECT
i.uid,
i.cpe_mac,
onu.onu_dhcp_port AS onu_port,
p.nas_id AS onu_nas,
onu.onu_mac_serial AS onu_cpe
FROM equipment_pon_onu onu
LEFT JOIN equipment_pon_ports p ON (p.id=onu.port_id)
LEFT JOIN internet_main i ON (onu.onu_dhcp_port = i.port AND p.nas_id = i.nas_id)
WHERE onu.onu_mac_serial<>i.cpe_mac |
| Code Block |
|---|
| language | sql |
|---|
| title | Пользователи без сервисов |
|---|
|
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); |
...