Revize 1346
Přidáno uživatelem Michal Kliment před více než 12 roky(ů)
| freenetis/branches/testing/application/models/ulog2_ct.php | ||
|---|---|---|
|
RETURNS bigint(20) unsigned
|
||
|
READS SQL DATA
|
||
|
BEGIN
|
||
|
DECLARE ip_address VARCHAR (255);
|
||
|
DECLARE ip VARCHAR (255);
|
||
|
DECLARE member_id, is_local, upload, download, local_upload, local_download INT;
|
||
|
|
||
|
SET ip_address = ip2str(_orig_ip_saddr);
|
||
|
SET ip = INET_NTOA(_orig_ip_saddr);
|
||
|
SET upload = _orig_raw_pktlen/1024;
|
||
|
SET download = _reply_raw_pktlen/1024;
|
||
|
|
||
|
SELECT IF(COUNT(*) > 0, 1, 0) INTO is_local
|
||
|
FROM local_subnets ls
|
||
|
WHERE INET_ATON(ip_address) & INET_ATON(netmask) = INET_ATON(network_address);
|
||
|
|
||
|
SET local_upload = is_local * upload;
|
||
|
WHERE INET_ATON(ip) & INET_ATON(netmask) = INET_ATON(network_address);
|
||
|
|
||
|
SET local_upload = is_local * upload;
|
||
|
SET local_download = is_local * download;
|
||
|
|
||
|
SELECT IFNULL(u.member_id, ip.member_id) INTO member_id
|
||
|
SELECT IFNULL(u.member_id, ip.member_id) INTO member_id
|
||
|
FROM
|
||
|
(
|
||
|
SELECT ip.member_id, IFNULL(i1.device_id, i2.device_id) AS device_id
|
||
|
SELECT ip_address, ip.member_id, IFNULL(i1.device_id, i2.device_id) AS device_id
|
||
|
FROM ip_addresses ip
|
||
|
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
|
||
|
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
|
||
|
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
|
||
|
WHERE ip_address = ip_address
|
||
|
) ip
|
||
|
LEFT JOIN devices d ON ip.device_id = d.id
|
||
|
LEFT JOIN users u ON d.user_id = u.id
|
||
|
LIMIT 0,1;
|
||
|
WHERE INET_ATON(ip.ip_address) = _orig_ip_saddr;
|
||
|
|
||
|
INSERT INTO ip_addresses_traffics (ip_address, download, upload, local_download, local_upload, member_id)
|
||
|
VALUE(ip_address, download, upload, local_download, local_upload, member_id)
|
||
|
VALUE(ip, download, upload, local_download, local_upload, member_id)
|
||
|
ON DUPLICATE KEY
|
||
|
UPDATE download = download + download, upload = upload + upload, local_download = local_download + local_download, local_upload = local_upload + local_upload;
|
||
|
|
||
| ... | ... | |
|
ON DUPLICATE KEY
|
||
|
UPDATE download = download + download, upload = upload + upload, local_download = local_download + local_download, local_upload = local_upload + local_upload;
|
||
|
|
||
|
RETURN 1;
|
||
|
RETURN is_local;
|
||
|
END
|
||
|
");
|
||
|
$db->query("DROP FUNCTION IF EXISTS `insert_rand`");
|
||
Také k dispozici: Unified diff
Druhy pokus - snad konecna oprava spatne MySQL funkce pro zpracovani provozu.