Date: Fri, 29 Mar 2024 12:01:08 +0200 (EET) Message-ID: <1315097140.6699.1711706468488@abills> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_6698_1080010834.1711706468487" ------=_Part_6698_1080010834.1711706468487 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
SELECT u= .id as login, u.uid, pi.fio, tp.name as tp_name, i.id as service_id, tp.mon= th_fee as tp_sum, u.registration FROM internet_main i JOIN users u ON (i.uid =3D u.uid) JOIN users_pi pi ON (i.uid =3D pi.uid) JOIN tarif_plans tp ON (i.tp_id =3D tp.tp_id) WHERE u.registration >=3D DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -1= MONTH), "%Y-%m-01") AND u.registration <=3D LAST_DAY(DATE_ADD(CURRENT_DATE, INTERVAL -1 MONT= H)) ORDER BY %SORT% %DESC%;=20
SELECT o= nu.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=3Donu.port_id) LEFT JOIN nas n ON (p.nas_id=3Dn.id) WHERE onu.onu_mac_serial IN=20 (SELECT onu_mac_serial FROM equipment_pon_onu GROUP BY onu_mac_serial HAVING COUNT(*)> 1) ORDER BY onu.onu_mac_serial=20
SELECT= =20 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=3Donu.port_id) LEFT JOIN internet_main i ON (onu.onu_dhcp_port =3D i.port AND p.nas_id =3D= i.nas_id) WHERE onu.onu_mac_serial<>i.cpe_mac=20
SELECT i= d as login, uid FROM users WHERE uid NOT IN (SELECT uid FROM internet_main) AND uid NOT IN (SELECT uid FROM iptv_main);=20
SELECT CASE WHEN b.deposit < 0 THEN "1. =D0=9C=D0=B5=D0=BD=D1=8C=D1=88=D0=B5 0" WHEN b.deposit >=3D 0 AND b.deposit < 100 THEN "2. =D0=9E=D1=82 0 = =D0=B4=D0=BE 100" WHEN b.deposit >=3D 100 AND b.deposit < 500 THEN "3. =D0=9E=D1=82 = 100 =D0=B4=D0=BE 500" ELSE "4. =D0=91=D0=BE=D0=BB=D1=8C=D1=88=D0=B5 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=3Db.uid) group by deposit_value order by deposit_value;=20