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

my $sql_get_auth_usr = <<SQL;
    SELECT u.id, u.balance, u.name, u.state, a.auth_start, a.ip, a.properties
        FROM (
        SELECT INET_NTOA(i.ip) AS ip, '' as properties, UNIX_TIMESTAMP() AS auth_start
            FROM users u JOIN ip_pool i ON i.uid=u.id WHERE u.lstate=1
                UNION ALL
        SELECT ip, properties, start AS auth_start FROM auth_now
    ) a
        JOIN ip_pool i ON INET_ATON(a.ip)=i.ip
        JOIN users u ON i.uid=u.id
    WHERE TRUE
SQL

        authorize_check_query = "call radcheck('%{User-Name}')"
        authorize_reply_query = "call radreply('%{User-Name}')"
        postauth_query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}','user=%{Calling-Station-Id};nas=%{NAS-IP-Address}')"
        accounting_update_query = "call radupdate('%{User-Name}','%{Framed-IP-Address}','user=%{Calling-Station-Id};nas=%{NAS-IP-Address}')"

mysql> SELECT * FROM auth_now;
+-----+-------------+------------+------------+----------------------------------------+
| id  | ip          | start      | last       | properties                             |
+-----+-------------+------------+------------+----------------------------------------+
| 417 | 192.168.3.5 | 1519632363 | 1519632408 | mod=pppoe;user=10.1.6.252;nas=10.0.0.8 |
+-----+-------------+------------+------------+----------------------------------------+
1 row in set (0.00 sec)

        postauth_query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}','user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
        accounting_update_query = "call radupdate('%{User-Name}','%{Framed-IP-Address}','user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"

mysql> SELECT * FROM auth_now;
+-----+-------------+------------+------------+-------------------------------------------------------------+
| id  | ip          | start      | last       | properties                                                  |
+-----+-------------+------------+------------+-------------------------------------------------------------+
| 468 | 192.168.3.5 | 1519635668 | 1519636968 | mod=pppoe;user=10.1.6.252;nas=10.0.0.8;ses=1e51614f183fc2ec |
| 477 | 192.168.3.3 | 1519636014 | 1519636981 | mod=pppoe;user=10.0.0.10;nas=10.0.0.8;ses=1e51614f183fc2eb  |
+-----+-------------+------------+------------+-------------------------------------------------------------+

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;

  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  SELECT get_ip(usr_id) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

DROP FUNCTION IF EXISTS `get_ip`;
DELIMITER $$
CREATE FUNCTION `get_ip` ( user_id INTEGER UNSIGNED )
    RETURNS VARCHAR(15) NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15) DEFAULT 0;
    DECLARE row_cnt INTEGER;
    DECLARE ip_id INTEGER;
    DECLARE tries INTEGER DEFAULT 30;
    DECLARE id_min INTEGER;
    DECLARE id_max INTEGER;
    DECLARE blocked INTEGER DEFAULT 0;

    SELECT 1 INTO blocked FROM users WHERE state='off' AND id=user_id LIMIT 1;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type = 'static' AND blocked = 0 LIMIT 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';

    SELECT id, INET_NTOA(ip) INTO ip_id, user_ip FROM ip_pool
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            AND IF(blocked>0, tags LIKE '%,blocked,%', tags NOT LIKE '%,blocked,%')
        LIMIT 1;

    IF( ip_id IS NOT NULL) THEN
        UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id;
        SELECT ROW_COUNT() INTO row_cnt;
        IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
    END IF;

    SELECT MAX(id), MIN(id) INTO id_max, id_min
        FROM ip_pool
        WHERE type = 'dynamic' AND realip = IF(real_ip>0,1,0);

    sel_ip: WHILE tries > 0 DO
        SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
            FROM ip_pool
            WHERE uid = 0
                AND id >= (CEIL(RAND() * (id_max - id_min)) + id_min)
                AND id <= id_max
                AND IF(blocked>0, tags LIKE '%,blocked,%', tags NOT LIKE '%,blocked,%')
                LIMIT 1;
        IF( user_ip IS NOT NULL) THEN
            UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
                WHERE id = ip_id AND uid = 0;
            SELECT ROW_COUNT() INTO row_cnt;
            IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
            SET tries = tries - 5;
        END IF;
        SET tries = tries - 1;
    END WHILE;

END$$
DELIMITER ;

 ifname | username | calling-sid |     ip     | rate-limit | type | comp | state  |  uptime
--------+----------+-------------+------------+------------+------+------+--------+----------
 ppp0   | test     | 10.1.6.252  | 172.1.1.52 | 168/359    | pptp | mppe | active | 00:00:13

#!/usr/bin/perl
use Control::CLI;
use Data::Dumper;
use Logger::Syslog;
use File::Basename;

logger_prefix(basename($0));
info("Starting at ".localtime());

my $hostname="127.0.0.1";
my $port="2000";
my $username='123';
my $password='12345';
my $cmd_1 = 'show sessions ifname,rate-limit';
my $cmd_2 = 'show sessions ifname,username,rate-limit match rate-limit 168/359';
my $cmd_3 = 'terminate if $iface soft ';

$cli = new Control::CLI('TELNET');
$cli->connect(Host      => $hostname,
              Port      => $port,);
$cli->login(Username    => $username,
            Password        => $password,);
my @arr = split("\n",$cli->cmd($cmd_2));
@arr = get_iface_array(\@arr);

foreach $elem (@arr) {
    my @in_array = @{$elem};
    my $iface = $in_array[0];
    info ("Delete iface: ".$iface." for username: ".$in_array[1]);
    $cli->cmd("terminate if ${iface} soft");
}


$cli->disconnect;
info("Stopped at ".localtime());

sub get_iface_array() {
        my @result;
        my @in_array = @{$_[0]};
        foreach $elem (@in_array) {
                @parts = ($elem =~ m/(ppp\d+)|([\d\/]+)/g);
                my @res;
                foreach $elem2 (@parts) {
                        if ($elem2 ne undef) {
                                push @res,$elem2;
                        }
                }
                if ($#res > 0) {
                        push @result, \@res;
                }
        }
        return @result;
}

DROP FUNCTION IF EXISTS `get_ip`;
DELIMITER $$
CREATE FUNCTION `get_ip` ( user_id INTEGER UNSIGNED )
    RETURNS VARCHAR(15) NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15) DEFAULT 0;
    DECLARE row_cnt INTEGER;
    DECLARE ip_id INTEGER;
    DECLARE tries INTEGER DEFAULT 30;
    DECLARE id_min INTEGER;
    DECLARE id_max INTEGER;
    DECLARE blocked INTEGER DEFAULT 0;

    SELECT 1 INTO blocked FROM users WHERE state='off' AND id=user_id LIMIT 1;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type = 'static' AND blocked = 0 LIMIT 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';

    SELECT id, INET_NTOA(ip) INTO ip_id, user_ip FROM ip_pool
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            AND IF(blocked>0, tags LIKE '%,blocked,%', tags NOT LIKE '%,blocked,%')
        LIMIT 1;

    IF( ip_id IS NOT NULL) THEN
        UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id;
        SELECT ROW_COUNT() INTO row_cnt;
        IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
    END IF;

    SELECT MAX(id), MIN(id) INTO id_max, id_min
        FROM ip_pool
        WHERE type = 'dynamic' AND realip = IF(real_ip>0,1,0);

    sel_ip: WHILE tries > 0 DO
        SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
            FROM ip_pool
            WHERE uid = 0
                AND id >= (CEIL(RAND() * (id_max - id_min)) + id_min)
                AND id <= id_max
                AND IF(blocked>0, tags LIKE '%,blocked,%', tags NOT LIKE '%,blocked,%')
                LIMIT 1;
        IF( user_ip IS NOT NULL) THEN
            UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
                WHERE id = ip_id AND uid = 0;
            SELECT ROW_COUNT() INTO row_cnt;
            IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
            SET tries = tries - 5;
        END IF;
        SET tries = tries - 1;
    END WHILE;

END$$
DELIMITER ;