Szymon Kajewski wrote:
Droga Listo !
Coś w ten deseń: ;)
SELECT nodes.ownerid as owner_id, customers.lastname as owner_name , nodes.id as node_id, nodes.name as node_name , nodes.mac as mac, nodes.chkmac as check_mac, inet_ntoa(nodes.ipaddr) as lan_ip, t1.owner_up_limit, t1.owner_max_up_limit, t1.owner_down_limit, t1.owner_max_down_limit, t1.owner_burst_threshold, t1.owner_burst_limit, t1.owner_burst_time, t2.node_up_limit, t2.node_max_up_limit, t2.node_down_limit, t2.node_max_down_limit, t2.node_burst_threshold, t2.node_burst_limit, t2.node_burst_time, bit_count(inet_aton(n1.mask)) AS lan_mask, n1.interface AS wan_ip, bit_count(inet_aton(n2.mask)) AS wan_mask, n2.interface AS wan_interface FROM nodes JOIN customers ON (customers.id = nodes.ownerid) LEFT JOIN ( SELECT customerid, SUM( uprate ) AS owner_up_limit, SUM( upceil ) AS owner_max_up_limit, SUM( downrate ) AS owner_down_limit, SUM( downceil ) AS owner_max_down_limit, SUM( quota_www_limit ) AS owner_burst_threshold, SUM( quota_sql_limit ) AS owner_burst_limit, SUM( quota_ftp_limit ) AS owner_burst_time FROM tariffs,assignments WHERE assignments.tariffid = tariffs.id GROUP BY customerid ) t1 ON (t1.customerid = nodes.ownerid) LEFT JOIN ( SELECT nodeid, SUM( uprate ) AS node_up_limit, SUM( upceil ) AS node_max_up_limit, SUM( downrate ) AS node_down_limit, SUM( downceil ) AS node_max_down_limit, SUM( quota_www_limit ) AS node_burst_threshold, SUM( quota_sql_limit ) AS node_burst_limit, SUM( quota_ftp_limit ) AS node_burst_time FROM tariffs,assignments,nodeassignments WHERE assignments.tariffid = tariffs.id AND assignments.id = nodeassignments.assignmentid GROUP BY nodeid ) t2 ON (t2.nodeid = nodes.id) JOIN networks n1 ON ((inet_aton(n1.mask) & nodes.ipaddr) = n1.address) LEFT JOIN networks n2 ON ((inet_aton(n2.mask) & nodes.ipaddr_pub) = n2.address) WHERE nodes.access = 1 ORDER BY ownerid