· 8 years ago · Mar 06, 2018, 07:50 AM
1CREATE TABLE IF NOT EXISTS `ses_traf` (
2 `id` bigint(21) NOT NULL auto_increment,
3 `ses_id` varchar(32) NOT NULL default '',
4 `time` int(10) unsigned NOT NULL DEFAULT '0',
5 `traf_in` bigint(12) unsigned NOT NULL DEFAULT '0',
6 `traf_out` bigint(12) unsigned NOT NULL DEFAULT '0',
7 PRIMARY KEY (`id`),
8 UNIQUE KEY `ses_id` (`ses_id`),
9 KEY `time` (`time`)
10) ENGINE=MyISAM AUTO_INCREMENT=1;
11
12CREATE PROCEDURE radupdate(IN login VARCHAR(64), IN aip VARCHAR(16), IN properties VARCHAR(255), IN ses VARCHAR(32),
13 IN trafin BIGINT, IN trafout BIGINT)
14 BEGIN
15 DECLARE usr_id INT;
16 DECLARE usr_mac VARCHAR(16);
17 DECLARE t_in BIGINT;
18 DECLARE t_out BIGINT;
19
20 SELECT REPLACE(login, ':', '') INTO usr_mac;
21 CALL set_auth(aip, CONCAT('mod=ipoe;user=', usr_mac, ';', REPLACE(properties,';','')));
22
23 SELECT uid INTO usr_id FROM ip_pool WHERE INET_ATON(aip) = ip LIMIT 1;
24 IF ses IS NOT NULL AND ( usr_id > 0 ) THEN
25 SELECT trafin - IFNULL((SELECT traf_in FROM ses_traf WHERE ses_id = ses LIMIT 1),0) INTO t_in;
26 SELECT trafout - IFNULL((SELECT traf_out FROM ses_traf WHERE ses_id = ses LIMIT 1),0) INTO t_out;
27
28 UPDATE users_trf SET in1=in1 + t_in, out1=out1 + t_out WHERE uid=usr_id;
29 INSERT INTO ses_traf SET ses_id = ses, traf_in = trafin, time = UNIX_TIMESTAMP()
30 ON DUPLICATE KEY UPDATE traf_in = trafin, traf_out = trafout, time = UNIX_TIMESTAMP();
31 SET @s = CONCAT('INSERT INTO X', YEAR(NOW()), '_', MONTH(NOW()), '_', DAY(NOW()), ' VALUES(?,?,?,?,?,?)');
32 PREPARE stmt1 FROM @s;
33 SET @iface = 0;
34 SET @cls = 1;
35 SET @ts = CEIL(UNIX_TIMESTAMP() / 60) * 60;
36 SET @usr_id = usr_id;
37 SET @t_in = t_in;
38 SET @t_out = t_out;
39 EXECUTE stmt1 USING @usr_id, @iface, @ts, @cls, @t_in, @t_out;
40 DEALLOCATE PREPARE stmt1;
41 END IF;
42 UPDATE mac_uid SET time=UNIX_TIMESTAMP() WHERE ip=INET_ATON(aip) LIMIT 1;
43 END;
44
45
46sql {
47 driver = "rlm_sql_mysql"
48 mysql {
49 warnings = auto
50 }
51 server = "localhost"
52 port = 3306
53 login = "login_db"
54 password = "password"
55 radius_db = "radius_db"
56 authorize_check_query = "call radcheck('%{User-Name}')"
57 authorize_reply_query = "call radreply('%{User-Name}', 'accel')"
58 accounting {
59 reference = "%{tolower:type.%{Acct-Status-Type}.query}"
60 type {
61 start {
62 query = "call radupdate('%{User-Name}','%{Framed-IP-Address}','nas=%{NAS-IP-Address}', NULL, NULL, NULL)"
63 }
64 stop {
65 query = "call radstop('%{Framed-IP-Address}')"
66 }
67 interim-update {
68 query = "call radupdate('%{User-Name}','%{Framed-IP-Address}','nas=%{NAS-IP-Address}',\
69 '%{Acct-Session-Id}',\
70 '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',\
71 '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}')"
72 }
73 }
74 }
75}