Биллинговая система Nodeny

DHCP-Agent-Circuit-Id
DHCP-Agent-Remote-Id

<Relay-Agent {Agent-Circuit-ID _000400720019} {Agent-Remote-ID _ 0006ec228025b5e0}>

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64), IN dev_mac VARCHAR(64), IN port VARCHAR(64))
BEGIN
    DECLARE usr_mac VARCHAR(12);
    DECLARE usr_ip VARCHAR(15);
    DECLARE usr_id INT;
    SELECT REPLACE(login, ':', '') INTO usr_mac;
    SELECT REPLACE(dev_mac, ':', '') INTO dev_mac;
    SELECT uid INTO usr_id FROM mac_uid
        WHERE mac=usr_mac AND device_mac=dev_mac AND device_port=port;
    IF usr_id IS NOT NULL AND usr_id>0 THEN
        SELECT get_ip(usr_id) INTO usr_ip;
        UPDATE mac_uid SET ip=0 WHERE ip=INET_ATON(usr_ip) AND uid<>usr_id;
        UPDATE mac_uid SET ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP() WHERE uid=usr_id;
    ELSE
        UPDATE mac_uid SET ip=0 WHERE uid=0 AND time<(UNIX_TIMESTAMP()-3600);
        START TRANSACTION;
        SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool p WHERE uid=0 AND type='dynamic'
            AND NOT EXISTS (SELECT ip FROM mac_uid WHERE ip=p.ip)
            ORDER BY RAND() LIMIT 1 FOR UPDATE;
        INSERT INTO mac_uid VALUES(
            NULL, usr_mac, INET_ATON(usr_ip), 0, UNIX_TIMESTAMP(), dev_mac, port, 0)
        ON DUPLICATE KEY
            UPDATE uid=0, ip=INET_ATON(usr_ip), device_mac=dev_mac, device_port=port,
                time=UNIX_TIMESTAMP();
        COMMIT;
        SELECT INET_NTOA(ip) INTO usr_ip FROM mac_uid
            WHERE mac=usr_mac AND device_mac=dev_mac AND device_port=port;
    END IF;
    SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
    SELECT NULL, login, 'Session-Timeout', '600', '=';
END$$
DELIMITER ;

authorize_check_query = "call radcheck('%{User-Name}', '%{DHCP-Agent-Remote-Id}', '%{DHCP-Agent-Circuit-Id}')"

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64), IN dev_mac VARCHAR(64), IN port VARCHAR(64))
BEGIN
    DECLARE usr_mac VARCHAR(12);
    DECLARE usr_ip VARCHAR(15);
    DECLARE usr_id INT;
    DECLARE one_connect INT;
    SELECT REPLACE(login, ':', '') INTO usr_mac;
    SELECT REPLACE(dev_mac, ':', '') INTO dev_mac;
    SELECT uid, oneconnect INTO usr_id, one_connect FROM mac_uid
        WHERE device_mac=dev_mac AND device_port=port
            AND (mac=usr_mac OR (oneconnect>0 AND device_mac<>''))
        LIMIT 1;
    IF usr_id IS NOT NULL AND usr_id > 0 THEN
        SELECT get_ip(usr_id) INTO usr_ip;
        UPDATE mac_uid SET ip=0 WHERE ip=INET_ATON(usr_ip) AND uid<>usr_id;
        UPDATE mac_uid SET ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP() WHERE uid=usr_id;
        IF one_connect > 0 THEN
            UPDATE mac_uid SET mac=NULL
                WHERE mac=usr_mac AND oneconnect=0;
            UPDATE mac_uid SET mac=usr_mac
                WHERE device_mac=dev_mac AND device_port=port AND oneconnect>0;
        END IF;
    ELSE
        UPDATE mac_uid SET ip=0 WHERE uid=0 AND time<(UNIX_TIMESTAMP()-3600);
        UPDATE mac_uid SET mac=NULL WHERE mac=usr_mac AND oneconnect>0;
        START TRANSACTION;
        SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool p WHERE uid=0 AND type='dynamic'
            AND NOT EXISTS (SELECT ip FROM mac_uid WHERE ip=p.ip)
            ORDER BY RAND() LIMIT 1 FOR UPDATE;
        INSERT INTO mac_uid VALUES(
            NULL, usr_mac, INET_ATON(usr_ip), 0, UNIX_TIMESTAMP(), dev_mac, port, 0)
        ON DUPLICATE KEY
            UPDATE ip=IF(ip>0 AND device_mac=dev_mac AND device_port=port, ip, INET_ATON(usr_ip)),
                uid=0, device_mac=dev_mac, device_port=port, time=UNIX_TIMESTAMP();
        COMMIT;
        SELECT INET_NTOA(ip) INTO usr_ip FROM mac_uid
            WHERE mac=usr_mac AND device_mac=dev_mac AND device_port=port;
    END IF;
    SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
    SELECT NULL, login, 'Session-Timeout', '600', '=';
END$$
DELIMITER ;

alter table mac_uid add index (oneconnect);

[sql]   expand: call radcheck_ipoe('%{User-Name}') -> call radcheck_ipoe('ec:08:6b:d9:96:e7')
[sql] User found in radcheck table
[sql]   expand: call radreply_ipoe('%{User-Name}','%{DHCP-Agent-Remote-Id}','%{DHCP-Agent-Circuit-Id}','%{NAS-Identifier}') -> call radreply_ipoe('ec:08:6b:d9:96:e7','0x00060012cf7ef380','0x766c616e323030','accel-ppp-ipoe')

(14) sql: EXPAND call radreply_ipoe('%{User-Name}','%{NAS-Identifier}')
(14) sql:    --> call radreply_ipoe('ec086bd996e7-0012cf7ef380-21','accel-ppp-ipoe')

DELIMITER $$
CREATE DEFINER=`nodeny`@`%` PROCEDURE `radreply_ipoe`(IN `login` VARCHAR(64), IN `tag` VARCHAR(64))
BEGIN
    DECLARE rad_mac VARCHAR(12);
    DECLARE rad_dev_mac VARCHAR(12);
    DECLARE rad_port VARCHAR(6);
    DECLARE usr_id INT;
    DECLARE usr_onecon INT;
    DECLARE usr_ip VARCHAR(15);
    
    SELECT SUBSTRING_INDEX(login, '-', 1) INTO rad_mac;
    SELECT SUBSTRING_INDEX(login, '-', -1) INTO rad_port;
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(login, '-', 2), '-', -1) INTO rad_dev_mac;
    
    SELECT uid, oneconnect INTO usr_id, usr_onecon FROM mac_uid
		WHERE device_mac=rad_dev_mac AND device_port=rad_port
			AND (mac=rad_mac OR (oneconnect>0 AND device_mac<>''))
        		LIMIT 1;
				
	IF usr_id IS NOT NULL AND usr_id > 0 THEN
		SELECT get_ip_by_tag(usr_id, tag) INTO usr_ip;
		UPDATE mac_uid SET ip=0 WHERE ip=INET_ATON(usr_ip) AND uid<>usr_id;
		UPDATE mac_uid SET ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP() WHERE uid=usr_id;
		
        IF usr_onecon > 0 THEN
            UPDATE mac_uid SET mac=NULL WHERE mac=rad_mac AND oneconnect=0;
            UPDATE mac_uid SET mac=rad_mac WHERE device_mac=rad_dev_mac AND device_port=rad_port AND oneconnect>0;
        END IF;
		
    ELSE
        UPDATE mac_uid SET ip=0 WHERE uid=0 AND time<(UNIX_TIMESTAMP()-3600);
        UPDATE mac_uid SET mac=NULL WHERE mac=rad_mac AND oneconnect>0;
		
        START TRANSACTION;
        SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool p WHERE uid=0 AND type='dynamic'
			AND tags LIKE CONCAT('%,', tag ,',%')
			AND NOT EXISTS (SELECT ip FROM mac_uid WHERE ip=p.ip)
			ORDER BY RAND() LIMIT 1 FOR UPDATE;
			
        INSERT INTO mac_uid VALUES(
            NULL, rad_mac, INET_ATON(usr_ip), 0, UNIX_TIMESTAMP(), rad_dev_mac, rad_port, 0)
        ON DUPLICATE KEY
            UPDATE ip=IF(ip>0 AND device_mac=rad_dev_mac AND device_port=rad_port, ip, INET_ATON(usr_ip)),
	         uid=0, device_mac=rad_dev_mac, device_port=rad_port, time=UNIX_TIMESTAMP();
        COMMIT;
		
        SELECT INET_NTOA(ip) INTO usr_ip FROM mac_uid
			WHERE mac=rad_mac AND device_mac=rad_dev_mac AND device_port=rad_port;
			
    END IF;
	
    SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
END$$
DELIMITER ;

(28) sql: EXPAND call radupdate_ipoe('%{User-Name}','%{Framed-IP-Address}','nas=%{NAS-IP-Address}','%{NAS-Identifier}')
(28) sql:    --> call radupdate_ipoe('ec086bd996e7-0012cf7ef380-21','10.194.13.129','nas=172.19.0.12','accel-ppp-ipoe')

DELIMITER $$
CREATE DEFINER=`nodeny`@`%` PROCEDURE `radupdate_ipoe`(IN login VARCHAR(64), IN ipa VARCHAR(16),
    IN properties VARCHAR(255), tag VARCHAR(64))
BEGIN
	DECLARE rad_mac VARCHAR(12);
    DECLARE rad_dev_mac VARCHAR(12);
    DECLARE rad_port VARCHAR(6);
    DECLARE usr_id INT;
    DECLARE usr_ip VARCHAR(15);
    
    SELECT SUBSTRING_INDEX(login, '-', 1) INTO rad_mac;
    SELECT SUBSTRING_INDEX(login, '-', -1) INTO rad_port;
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(login, '-', 2), '-', -1) INTO rad_dev_mac;
  
    SELECT uid INTO usr_id FROM mac_uid
		WHERE device_mac=rad_dev_mac AND device_port=rad_port
			AND (mac=rad_mac OR (oneconnect>0 AND device_mac<>''))
        	LIMIT 1;

    IF usr_id IS NULL
    THEN
        SELECT ip INTO usr_ip;
    ELSE
        SELECT get_ip_by_tag(usr_id, tag) INTO usr_ip;
    END IF;
	
    CALL set_auth(usr_ip, CONCAT('mod=dhcp;user=', rad_mac, ';', 'dev=', rad_dev_mac, ';', 'port=', rad_port, ';', REPLACE(properties,':','')));
	UPDATE mac_uid SET time=UNIX_TIMESTAMP() WHERE ip=INET_ATON(ipa) LIMIT 1;
END$$
DELIMITER ;

WHERE device_mac=dev_mac AND device_port=port AND (mac=usr_mac OR (oneconnect>0 AND device_mac<>''))

single-session=replace

(9) sql: EXPAND call radupdate_ipoe('%{User-Name}','%{Framed-IP-Address}','nas=%{NAS-IP-Address}','%{NAS-Identifier}','%{Acct-Session-Id}')
(9) sql:    --> call radupdate_ipoe('ec086bd996e7-00060012cf7e-1','10.194.12.110','nas=172.19.0.12','accel-ppp-ipoe','586929edd8884b8f')
(9) sql: Executing query: call radupdate_ipoe('ec086bd996e7-00060012cf7e-1','10.194.12.110','nas=172.19.0.12','accel-ppp-ipoe','586929edd8884b8f')
(9) sql: SQL query returned: success
(9) sql: 1 record(s) updated

my $device_port = ses::input_int('device_port');

my $device_port = ses::input('device_port');

 SELECT * FROM `mac_uid` WHERE mac='ec086bd996e7';
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| id      | mac          | ip        | uid | time       | device_mac   | device_port | oneconnect |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| 6434302 | ec086bd996e7 | 180489656 |   0 | 1518698735 | 0012cf7ef380 | 1/21        |          0 |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+

 SELECT * FROM `mac_uid` WHERE mac='ec086bd996e7';
+---------+--------------+-----------+-----+------------+------------+-------------+------------+
| id      | mac          | ip        | uid | time       | device_mac | device_port | oneconnect |
+---------+--------------+-----------+-----+------------+------------+-------------+------------+
| 6434377 | ec086bd996e7 | 180489656 |   4 | 1518698915 |            |             |          0 |
+---------+--------------+-----------+-----+------------+------------+-------------+------------+

SELECT * FROM `mac_uid` WHERE mac='ec086bd996e7';
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| id      | mac          | ip        | uid | time       | device_mac   | device_port | oneconnect |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| 6434377 | ec086bd996e7 | 180488872 |   0 | 1518699128 | 0012cf7ef380 | 1/21        |          0 |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+

 SELECT * FROM `mac_uid` WHERE mac='ec086bd996e7';
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| id      | mac          | ip        | uid | time       | device_mac   | device_port | oneconnect |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| 6434454 | ec086bd996e7 | 180488872 |   4 | 1518699308 | 0012cf7ef380 | 1/21        |          0 |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+

0.115583	
181 Db.pm::rollback
rollback (fail: DELETE FROM mac_uid WHERE mac=? AND uid=0)

SELECT * FROM `mac_uid` WHERE mac='ec086bd996e7';
+---------+--------------+----+-----+------+--------------+-------------+------------+
| id      | mac          | ip | uid | time | device_mac   | device_port | oneconnect |
+---------+--------------+----+-----+------+--------------+-------------+------------+
| 6434656 | ec086bd996e7 |  0 |   4 |    0 | 0012cf7ef380 | 1/21        |          2 |
+---------+--------------+----+-----+------+--------------+-------------+------------+

SELECT * FROM `mac_uid` WHERE mac='ec086bd996e7';
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| id      | mac          | ip        | uid | time       | device_mac   | device_port | oneconnect |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| 6434302 | ec086bd996e7 | 180489656 |   0 | 1518698735 | 0012cf7ef380 | 1/21        |          0 |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+

SELECT * FROM `mac_uid` WHERE mac='ec086bd996e7';
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| id      | mac          | ip        | uid | time       | device_mac   | device_port | oneconnect |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+
| 6434302 | ec086bd996e7 | 180489656 |   4 | 1518698735 | 0012cf7ef380 | 1/21        |          0 |
+---------+--------------+-----------+-----+------------+--------------+-------------+------------+

І вказати номер порта, що згенерує cvlan.

окремому полі зберігати данні типу vlan2501.101 - ну це вже суперечить вимогам Стаса про створення додаткових полів в БД.

 ifname |            username             |    calling-sid    |      ip       | type  | comp | state  |   uptime
--------+---------------------------------+-------------------+---------------+-------+------+--------+-------------
 ipoe2  | 10feed5ded85-dcd2fc5cbfe8-0/2   | 10:fe:ed:5d:ed:85 | 10.194.0.74   | ipoe  |      | active | 10.10:43:24
 ipoe7  | f81a678b481d-0012cf8c0260-3/20  | f8:1a:67:8b:48:1d | 10.194.0.167  | ipoe  |      | active | 10.10:11:07
 ipoe0  | 30b5c25e55c3-0012cf8c0260-1/13  | 30:b5:c2:5e:55:c3 | 10.194.9.61   | ipoe  |      | active | 6.16:17:20
 ipoe1  | d46e0e506a69-0012cf8c0260-3/18  | d4:6e:0e:50:6a:69 | 10.194.0.28   | ipoe  |      | active | 5.16:05:37
 ipoe9  | 78447659340e-e067b317a3b8-0/2:6 | 78:44:76:59:34:0e | 10.194.0.6    | ipoe  |      | active | 5.02:49:02

В нього ж немає таких параметрів як svlan-ідентифікатор свіча і cvlan-ідентифікатор порта, а є в нього Мак устройства і Порт устройства

ALTER TABLE `mac_uid` ADD `onedevice` TINYINT(1) UNSIGNED NULL DEFAULT '0' AFTER `oneconnect`;

    $tbl->add('*','lL',
        [ _('[p][p]',
            L('К данному порту не подключены другие абоненты/устройства'),
            L('При подключении мак не будет проверяться')) ],
        [ v::checkbox( name=>'oneconnect', value=>2, checked=>$d->{d}{oneconnect}) ],
     );

    $tbl->add('*','lL',
        [ _('[p][p]',
            L('К данному устройству не подключены другие абоненты/устройства'),
            L('При подключении мак и порт не будут проверяться')) ],
        [ v::checkbox( name=>'onedevice', value=>2, checked=>$d->{d}{onedevice}) ],
    );

    $tbl->add('*','lL',
        [ _('[p][p]',
            L('К данному порту не подключены другие абоненты/устройства'),
            L('При подключении мак не будет проверяться')) ],
        [ v::radio( name=>'oneconnect', value=>1, checked=>$d->{d}{oneconnect}) ],
    );

    $tbl->add('*','lL',
        [ _('[p][p]',
            L('К данному устройству не подключены другие абоненты/устройства'),
            L('При подключении мак и порт не будут проверяться')) ],
        [ v::radio( name=>'oneconnect', value=>2, checked=>$d->{d}{onedevice}) ],
    );

...
    my $device_mac = ses::input('device_mac');
    my $device_port = ses::input_int('device_port');
    my $oneconnect = ses::input_int('oneconnect');
...

if ($oneconnect == 1) {
  my $oneconnect = 1;
  my $onedevice = 0;
} elsif ($oneconnect == 2) {
  my $oneconnect = 0;
  my $onedevice = 1;
}

my $onedevice;
if ($oneconnect == 1) {
  $oneconnect = 1;
  $onedevice = 0;
} elsif ($oneconnect == 2) {
  $oneconnect = 0;
  $onedevice = 1;
}

my $onedevice;
if ($oneconnect == 1) {
  $onedevice = 0;
} elsif ($oneconnect == 2) {
  $oneconnect = 0;
  $onedevice = 1;
}

$oneconnect = $oneconnect == 1 ? 1 : 0;
my $onedevice = 1 - $oneconnect;

$oneconnect = ($oneconnect == 1) + 0;
my $onedevice = 1 - $oneconnect;

my $onedevice = --$oneconnect == 1?  $oneconnect-- : $oneconnect++;

   # у клиента сейчас гостевой ip - выдадим новый
    my $new_ip = $usr->{ips}[0]{ipn};
    $rows = Db->do("UPDATE mac_uid SET uid=?, ip=? WHERE ip=INET_ATON(?)", $uid, $new_ip, $ses::ip);
    Db->ok or Error $lang::user::soft_error;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `radreply`(IN login VARCHAR(64), IN r_id VARCHAR(64), IN c_id VARCHAR(64))
    DETERMINISTIC
BEGIN
DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE add_attr MEDIUMTEXT;
  DECLARE line MEDIUMTEXT;
  DECLARE i INT DEFAULT 1;
  DECLARE usr_mac VARCHAR(12);
  DECLARE c_ID_m  VARCHAR(12) DEFAULT NULL; 
  DECLARE r_ID_m  VARCHAR(12) DEFAULT NULL; 
 
 SELECT REPLACE(c_id,'0x', '') INTO c_ID_m;   
 SELECT REPLACE(r_id,'0x', '') INTO r_ID_m;
  
  
    SELECT REPLACE(login, ':', '') INTO usr_mac;
    SELECT uid INTO usr_id FROM mac_uid WHERE device_mac=r_ID_m AND device_port=c_ID_m AND (mac=usr_mac OR (oneconnect>0 AND device_mac<>''));
    SELECT radius_attr INTO add_attr FROM users_services WHERE uid=usr_id AND tags LIKE '%,inet,%' LIMIT 1;
    
    IF usr_id IS NOT NULL AND usr_id>0 THEN
    
    SELECT get_ip(usr_id) INTO usr_ip;
        UPDATE mac_uid SET ip=0 WHERE ip=INET_ATON(usr_ip) AND uid<>usr_id;
        UPDATE mac_uid SET mac=usr_mac, ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP() WHERE device_mac=r_ID_m AND device_port=c_ID_m AND (mac=usr_mac OR (oneconnect>0 AND device_mac<>''));
        SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
attr_loop:  WHILE TRUE DO
    SELECT strSplit(add_attr, '
', i) INTO line;
    IF LENGTH(line) = 0 OR i > 20 THEN LEAVE attr_loop; END IF;
    IF line LIKE '%+=%' THEN
        SELECT NULL,login,strSplit(line, '+=', 1),REPLACE (strSplit(line, '+=', 2),'\r',''),'+=';
    ELSEIF line LIKE '%=%' THEN
        SELECT NULL,login,strSplit(line, '=', 1),REPLACE (strSplit(line, '=', 2),'\r',''),'=';
    END IF;
    SET i = i + 1;
            END WHILE;
ELSE
        UPDATE mac_uid SET ip=0 WHERE uid=0 AND time<(UNIX_TIMESTAMP()-360);
        START TRANSACTION;
        SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool p WHERE uid=0
        AND type='dynamic'
        AND NOT EXISTS (SELECT ip FROM mac_uid WHERE ip=p.ip)
            ORDER BY RAND() LIMIT 1 FOR UPDATE;
        INSERT INTO mac_uid VALUES(
            NULL, usr_mac, INET_ATON(usr_ip), 0, UNIX_TIMESTAMP(), r_ID_m,c_ID_m , 0, '')
        ON DUPLICATE KEY
            UPDATE ip=IF(ip>0,ip,INET_ATON(usr_ip)), time=UNIX_TIMESTAMP(),device_mac=r_ID_m, device_port=c_ID_m;
        COMMIT;
        SELECT INET_NTOA(ip) INTO usr_ip FROM mac_uid WHERE mac=usr_mac;
SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `radupdate`(IN login VARCHAR(64), IN ipa VARCHAR(16), IN properties VARCHAR(255), IN ses VARCHAR(32), IN r_id VARCHAR(64), IN c_id VARCHAR(64), IN trafin BIGINT(20), IN trafout BIGINT(20))
    DETERMINISTIC
BEGIN
 DECLARE usr_id INT;
 DECLARE usr_mac VARCHAR(12) DEFAULT NULL;
 DECLARE c_ID_m  VARCHAR(12) DEFAULT NULL; 
 DECLARE r_ID_m  VARCHAR(12) DEFAULT NULL; 
 
 SELECT REPLACE(c_id,'0x', '') INTO c_ID_m;   
 SELECT REPLACE(r_id,'0x', '') INTO r_ID_m;

 
 SELECT REPLACE(login, ':', '') INTO usr_mac;
 SELECT uid INTO usr_id FROM `mac_uid` WHERE device_mac=r_ID_m AND device_port=c_ID_m AND (mac=usr_mac OR (oneconnect>0 AND device_mac<>'')) limit 1;   

 UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 300 WHERE ip = INET_ATON(ipa);

 IF( usr_id > 0 ) THEN
  
  CALL set_auth(ipa, CONCAT('mod=ipoe;',REPLACE(properties,':','')));
  INSERT INTO ses_traf SET ses_id=ses, traf_in=trafin, traf_out=trafout, time=UNIX_TIMESTAMP(), uid=usr_id;
  
 END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`nodeny`@`%` PROCEDURE `radreply_ipoe`(IN `login` VARCHAR(64), IN `tag` VARCHAR(64))
BEGIN
	DECLARE rad_mac VARCHAR(12);
    DECLARE rad_dev_mac VARCHAR(12);
    DECLARE rad_port VARCHAR(12);
    DECLARE usr_id INT;
	DECLARE usr_ip VARCHAR(15);
    DECLARE usr_onecon INT;
	DECLARE usr_onedev INT;
    DECLARE usr_state VARCHAR(3);
    DECLARE add_attr MEDIUMTEXT;
    DECLARE line MEDIUMTEXT;
    DECLARE i INT DEFAULT 1;

	SELECT SUBSTRING_INDEX(login, '-', 1) INTO rad_mac;
    SELECT SUBSTRING_INDEX(login, '-', -1) INTO rad_port;
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(login, '-', 2), '-', -1) INTO rad_dev_mac;
    
    SELECT uid, oneconnect, onedevice INTO usr_id, usr_onecon, usr_onedev FROM mac_uid
		WHERE (device_mac=rad_dev_mac AND device_mac<>'' AND onedevice>0) 
		OR (device_mac=rad_dev_mac AND device_mac<>'' AND device_port=rad_port 
			AND device_port<>'' AND oneconnect>0 AND onedevice=0)
		OR (mac=rad_mac AND oneconnect=0 AND onedevice=0) LIMIT 1;
				
	IF usr_id IS NOT NULL AND usr_id > 0 THEN
		SELECT get_ip_by_tag(usr_id, tag) INTO usr_ip;
		SELECT state INTO usr_state  FROM users WHERE id = usr_id;
		SELECT radius_attr INTO add_attr FROM users_services
    			WHERE uid=usr_id AND tags LIKE '%,inet,%' LIMIT 1;
		
		UPDATE mac_uid SET ip=0 WHERE ip=INET_ATON(usr_ip) AND uid<>usr_id;
		UPDATE mac_uid SET ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP() WHERE uid=usr_id;

		IF usr_onecon > 0 OR usr_onedev > 0 THEN
            UPDATE mac_uid SET mac=NULL WHERE mac=rad_mac AND oneconnect=0 AND onedevice=0;
			IF usr_onecon > 0 THEN
            		UPDATE mac_uid SET mac=rad_mac WHERE device_mac=rad_dev_mac 
					AND device_port=rad_port AND oneconnect>0 AND onedevice=0;
			END IF; 	
			IF usr_onedev > 0 THEN
            		UPDATE mac_uid SET mac=rad_mac, device_port=rad_port
					WHERE device_mac=rad_dev_mac AND onedevice>0;
			END IF; 	
        END IF; 
		
		IF usr_onecon = 0 AND usr_onedev = 0 THEN
			UPDATE mac_uid SET device_mac=rad_dev_mac, device_port=rad_port 
				WHERE mac=rad_mac AND uid=usr_id AND oneconnect=0 AND onedevice=0;	
		END IF;
		
    ELSE
	
	    UPDATE mac_uid SET ip=0 WHERE uid=0 AND time<(UNIX_TIMESTAMP()-3600);
        UPDATE mac_uid SET mac=NULL WHERE mac=rad_mac AND (oneconnect>0 OR onedevice>0);
				
        START TRANSACTION;
        SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool WHERE uid=0
			AND type='dynamic'
			AND `release` < UNIX_TIMESTAMP()
			AND tags LIKE CONCAT('%,', tag, ',%')
		ORDER BY RAND() LIMIT 1 FOR UPDATE;
			
        INSERT INTO mac_uid VALUES(
            NULL, rad_mac, INET_ATON(usr_ip), 0, UNIX_TIMESTAMP(), rad_dev_mac, rad_port, 0, 0)
        ON DUPLICATE KEY
            UPDATE ip=IF(ip>0 AND device_mac=rad_dev_mac AND device_port=rad_port, ip, INET_ATON(usr_ip)),
				uid=0, device_mac=rad_dev_mac, device_port=rad_port, time=UNIX_TIMESTAMP();
        COMMIT;
		
        SELECT INET_NTOA(ip) INTO usr_ip FROM mac_uid
			WHERE mac=rad_mac AND device_mac=rad_dev_mac AND device_port=rad_port;
			
    END IF;
	
    SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
	
    IF usr_id IS NOT NULL AND usr_id > 0 AND usr_state = 'on' AND add_attr IS NOT NULL
		THEN
			SELECT NULL, login, 'L4-Redirect', '1', '=';
			
	    		attr_loop: WHILE TRUE DO
			    SELECT strSplit(add_attr, '\n', i) INTO line;
			    IF LENGTH(line) = 0 OR i > 20 THEN LEAVE attr_loop; END IF;
			    IF line LIKE '%+=%' THEN
			        SELECT NULL,login,strSplit(line, '+=', 1),strSplit(line, '+=', 2),'+=';
			    ELSEIF line LIKE '%=%' THEN
			        SELECT NULL,login,strSplit(line, '=', 1),strSplit(line, '=', 2),'=';
			    END IF;
			    SET i = i + 1;
			END WHILE;
			
		ELSE
		    	SELECT NULL, login, 'L4-Redirect', '0', '=';
	      	SELECT NULL, login, 'Filter-Id', '1024', '=';
    END IF;
	
	IF usr_id IS NOT NULL AND usr_id > 0 THEN
		SELECT NULL, login, 'User-Id', usr_id, '=';
	ELSE
		SELECT NULL, login, 'User-Id', '0', '=';
	END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`nodeny`@`%` PROCEDURE `radupdate_ipoe`(IN `ipa` VARCHAR(16), IN `properties` VARCHAR(255))
BEGIN
	CALL set_auth(ipa, CONCAT('mod=dhcp;',REPLACE(properties,':','')));
	UPDATE mac_uid SET time=UNIX_TIMESTAMP() WHERE ip=INET_ATON(ipa) LIMIT 1;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `radreply`(IN login VARCHAR(64), IN r_id VARCHAR(64), IN c_id VARCHAR(64))
    DETERMINISTIC
BEGIN
DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE add_attr MEDIUMTEXT;
  DECLARE line MEDIUMTEXT;
  DECLARE i INT DEFAULT 1;
  DECLARE usr_mac VARCHAR(12);
  DECLARE c_ID_m  VARCHAR(12) DEFAULT NULL; 
  DECLARE r_ID_m  VARCHAR(12) DEFAULT NULL; 
 
 SELECT REPLACE(c_id,'0x', '') INTO c_ID_m;   
 SELECT REPLACE(r_id,'0x', '') INTO r_ID_m;
  
  
    SELECT REPLACE(login, ':', '') INTO usr_mac;
    SELECT uid INTO usr_id FROM mac_uid WHERE device_mac=r_ID_m AND device_port=c_ID_m AND (mac=usr_mac OR (oneconnect>0 AND device_mac<>''));
    SELECT radius_attr INTO add_attr FROM users_services WHERE uid=usr_id AND tags LIKE '%,inet,%' LIMIT 1;
    
    IF usr_id IS NOT NULL AND usr_id>0 THEN
    
    SELECT get_ip(usr_id) INTO usr_ip;
        UPDATE mac_uid SET ip=0 WHERE ip=INET_ATON(usr_ip) AND uid<>usr_id;
        UPDATE mac_uid SET mac=usr_mac, ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP() WHERE device_mac=r_ID_m AND device_port=c_ID_m AND (mac=usr_mac OR (oneconnect>0 AND device_mac<>''));
        SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
attr_loop:  WHILE TRUE DO
    SELECT strSplit(add_attr, '
', i) INTO line;
    IF LENGTH(line) = 0 OR i > 20 THEN LEAVE attr_loop; END IF;
    IF line LIKE '%+=%' THEN
        SELECT NULL,login,strSplit(line, '+=', 1),REPLACE (strSplit(line, '+=', 2),'\r',''),'+=';
    ELSEIF line LIKE '%=%' THEN
        SELECT NULL,login,strSplit(line, '=', 1),REPLACE (strSplit(line, '=', 2),'\r',''),'=';
    END IF;
    SET i = i + 1;
            END WHILE;
ELSE
        UPDATE mac_uid SET ip=0 WHERE uid=0 AND time<(UNIX_TIMESTAMP()-360);
        START TRANSACTION;
        SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool p WHERE uid=0
        AND type='dynamic'
        AND NOT EXISTS (SELECT ip FROM mac_uid WHERE ip=p.ip)
            ORDER BY RAND() LIMIT 1 FOR UPDATE;
        INSERT INTO mac_uid VALUES(
            NULL, usr_mac, INET_ATON(usr_ip), 0, UNIX_TIMESTAMP(), r_ID_m,c_ID_m , 0, '')
        ON DUPLICATE KEY
            UPDATE ip=IF(ip>0,ip,INET_ATON(usr_ip)), time=UNIX_TIMESTAMP(),device_mac=r_ID_m, device_port=c_ID_m;
        COMMIT;
        SELECT INET_NTOA(ip) INTO usr_ip FROM mac_uid WHERE mac=usr_mac;
SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `radupdate`(IN login VARCHAR(64), IN ipa VARCHAR(16), IN properties VARCHAR(255), IN ses VARCHAR(32), IN r_id VARCHAR(64), IN c_id VARCHAR(64), IN trafin BIGINT(20), IN trafout BIGINT(20))
    DETERMINISTIC
BEGIN
 DECLARE usr_id INT;
 DECLARE usr_mac VARCHAR(12) DEFAULT NULL;
 DECLARE c_ID_m  VARCHAR(12) DEFAULT NULL; 
 DECLARE r_ID_m  VARCHAR(12) DEFAULT NULL; 
 
 SELECT REPLACE(c_id,'0x', '') INTO c_ID_m;   
 SELECT REPLACE(r_id,'0x', '') INTO r_ID_m;

 
 SELECT REPLACE(login, ':', '') INTO usr_mac;
 SELECT uid INTO usr_id FROM `mac_uid` WHERE device_mac=r_ID_m AND device_port=c_ID_m AND (mac=usr_mac OR (oneconnect>0 AND device_mac<>'')) limit 1;   

 UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 300 WHERE ip = INET_ATON(ipa);

 IF( usr_id > 0 ) THEN
  
  CALL set_auth(ipa, CONCAT('mod=ipoe;',REPLACE(properties,':','')));
  INSERT INTO ses_traf SET ses_id=ses, traf_in=trafin, traf_out=trafout, time=UNIX_TIMESTAMP(), uid=usr_id;
  
 END IF;
END$$
DELIMITER ;

/etc/freeradius/3.0/mods-available# cat sql
sql {
    driver = "rlm_sql_mysql"
    mysql {
        warnings = auto
    }
    server = "localhost"
    port = 3306
    login = "nodeny"
    password = "hardpass"
    radius_db = "nodeny"

    authorize_check_query = "call radcheck('%{User-Name}')"
    authorize_reply_query = "call radreply('%{User-Name}','%{DHCP-Agent-Remote-Id}','%{DHCP-Agent-Circuit-Id}')" ####, '%{User-Password}')"

    accounting {
        reference = "%{tolower:type.%{Acct-Status-Type}.query}"
        type {
            start {
                query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address}','%{Acct-Session-Id}',\
                '%{DHCP-Agent-Remote-Id}','%{DHCP-Agent-Circuit-Id}',\
                (%{%{Acct-Input-Gigawords}:-0} * POWER(2, 32)) + %{%{Acct-Input-Octets}:-0},\
                (%{%{Acct-Output-Gigawords}:-0} * POWER(2, 32)) + %{%{Acct-Output-Octets}:-0})"
            }
            interim-update {
                query = "${..start.query}"
            }
            stop {
                query = "call radstop('%{User-Name}')"
            }
        }
    }

    post-auth {
                query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address}','%{Acct-Session-Id}',\
                '%{DHCP-Agent-Remote-Id}','%{DHCP-Agent-Circuit-Id}',\
                (%{%{Acct-Input-Gigawords}:-0} * POWER(2, 32)) + %{%{Acct-Input-Octets}:-0},\
                (%{%{Acct-Output-Gigawords}:-0} * POWER(2, 32)) + %{%{Acct-Output-Octets}:-0})"
    }
}

[ipoe]
verbose=1
level=5

username=lua:username
password=ipoe
unit-cache=0
lua-file=/etc/accel-ppp.lua
lease-time=120 #120   #60
renew-time=90  #30
max-lease-time=600  #90
attr-dhcp-client-ip=Framed-IP-Address
gw-ip-address=10.111.0.1/20
#### guest ip network
#gw-ip-address=172.16.0.1/20
### redirect
attr-l4-redirect=L4-Redirect
attr-l4-redirect-ipset=L4-Redirect-ipset
#attr-l4-redirect-table=L4-Redirect-table
#l4-redirect-ipset=l4
#L4-Redirect-table=4051
####
proxy-arp=1
shared=1
ifcfg=1
mode=L2
start=dhcpv4
interface=re:ens160
vlan-mon=re:ens160,4080
vlan-timeout=60
vlan-name=%I.%N
#agent-remote-id=accel-ppp
attr-dhcp-opt82=DHCP-Option82
attr-dhcp-opt82-remote-id=DHCP-Agent-Remote-Id
attr-dhcp-opt82-circuit-id=DHCP-Agent-Circuit-Id