Podsyłam zapytanko (w formie T-Script'a) do wyciągania prędkości użytkowników do pliku.
{speeds = SELECT n.id, INET_NTOA(n.ipaddr) as ip,
s.assignmentid as node_assignment,
a.id as customer_assignment,
IFNULL(t.downceil, 0) as downceil, IFNULL(t.upceil, 0) as upceil,
IFNULL(t.downrate, 0) as downrate, IFNULL(t.uprate, 0) as uprate
FROM nodes n
LEFT JOIN customers c on c.id = n.ownerid /* jeśli nie potrzebujemy urządzeń, to można zrobić zwykłego join'a */
LEFT JOIN nodeassignments s on n.id=s.nodeid
LEFT JOIN assignments a ON ((a.id = s.assignmentid or a.customerid = c.id)
AND a.datefrom <= unix_timestamp()
AND (a.dateto > unix_timestamp() or a.dateto = 0))
LEFT JOIN tariffs t ON a.tariffid = t.id
WHERE n.access = 1 /* IP ma dostęp */
AND (c.id is NULL OR c.deleted = 0) /* klient nie skasowany, jeśli mamy joina z customers można usunąć c.id is NULL */
AND (s.assignmentid IS NULL OR s.assignmentid = a.id OR a.id IS NULL)
AND (a.id is NULL OR not (t.downceil <= 0 AND t.upceil <= 0 AND t.downrate <= 0 AND t.uprate <= 0))
ORDER BY n.ipaddr, a.id;
without_assignment=""
duplicates=""
last=null
last_a=null
for (i=0; i<number(speeds);i++)
if(speeds[i].downceil == 0 && speeds[i].upceil == 0)
without_assignment = without_assignment + speeds[i].ip + "\n"
continue
/if
if (last==speeds[i].ip && (last_a==null || last!=last_a))
duplicates = duplicates + last + "\n"
last_a=last
/if
speeds[i].ip" "speeds[i].downceil" "speeds[i].upceil"\n"
last = speeds[i].ip
/for
"\n"}#ip without assignments:{"\n"
without_assignment
"\n"}#ip with more than one assignments:{"\n"
duplicates
}\
Sam nie wiem czemu T-Script ;)
--
KC
