Revize 1348
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 VARCHAR (255);
|
||
|
DECLARE member_id, is_local, upload, download, local_upload, local_download INT;
|
||
|
DECLARE _ip_address VARCHAR (255);
|
||
|
DECLARE _member_id, _is_local, _upload, _download, _local_upload, _local_download INT;
|
||
|
|
||
|
SET ip = INET_NTOA(_orig_ip_saddr);
|
||
|
SET upload = _orig_raw_pktlen/1024;
|
||
|
SET download = _reply_raw_pktlen/1024;
|
||
|
SET _ip_address = INET_NTOA(_orig_ip_saddr);
|
||
|
|
||
|
SELECT IF(COUNT(*) > 0, 1, 0) INTO is_local
|
||
|
SELECT IF(COUNT(*) > 0, 1, 0) INTO _is_local
|
||
|
FROM local_subnets ls
|
||
|
WHERE INET_ATON(ip) & INET_ATON(netmask) = INET_ATON(network_address);
|
||
|
WHERE INET_ATON(_ip_address) & INET_ATON(netmask) = INET_ATON(network_address);
|
||
|
|
||
|
SET local_upload = is_local * upload;
|
||
|
SET local_download = is_local * download;
|
||
|
SET _upload = _orig_raw_pktlen/1024;
|
||
|
SET _download = _reply_raw_pktlen/1024;
|
||
|
|
||
|
SELECT IFNULL(u.member_id, ip.member_id) INTO member_id
|
||
|
SET _local_upload = _is_local * _upload;
|
||
|
SET _local_download = _is_local * _download;
|
||
|
|
||
|
SELECT IFNULL(u.member_id, ip.member_id) INTO _member_id
|
||
|
FROM
|
||
|
(
|
||
|
SELECT ip_address, ip.member_id, IFNULL(i1.device_id, i2.device_id) AS device_id
|
||
| ... | ... | |
|
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, download, upload, local_download, local_upload, member_id)
|
||
|
VALUES(_ip_address, _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;
|
||
|
UPDATE download = download + _download, upload = upload + _upload, local_download = local_download + _local_download, local_upload = local_upload + _local_upload;
|
||
|
|
||
|
INSERT INTO members_traffics (member_id, download, upload, local_download, local_upload, day)
|
||
|
VALUE(member_id, download, upload, local_download, local_upload, CURDATE())
|
||
|
VALUES(_member_id, _download, _upload, _local_download, _local_upload, CURDATE())
|
||
|
ON DUPLICATE KEY
|
||
|
UPDATE download = download + download, upload = upload + upload, local_download = local_download + local_download, local_upload = local_upload + local_upload;
|
||
|
UPDATE download = download + _download, upload = upload + _upload, local_download = local_download + _local_download, local_upload = local_upload + _local_upload;
|
||
|
|
||
|
RETURN is_local;
|
||
|
RETURN _download;
|
||
|
END
|
||
|
");
|
||
|
$db->query("DROP FUNCTION IF EXISTS `insert_rand`");
|
||
Také k dispozici: Unified diff
Posledni pokus zase opravit MySQL funkci...