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

# Sql выборки всех авторизованных клиентов, которым не запрещен доступ и у которых подключена услуга с тегом `inet`

my $sql_get_auth_usr = <<SQL;
    SELECT u.id, u.balance, u.name, u.state, a.auth_start, a.ip, a.properties
        FROM (
        SELECT ip, properties, start AS auth_start FROM auth_now
                UNION ALL
        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.
    ) a.
        JOIN ip_pool i ON INET_ATON(a.ip)=i.ip
        JOIN users u ON i.uid=u.id
    WHERE TRUE
SQL

my $db = Db->sql( $sql_get_auth_usr );

my $db = Db->sql( $sql_get_auth_usr.' GROUP BY ip' );

    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
                AND NOT EXISTS (SELECT 1 FROM auth_now WHERE INET_ATON(ip)=i.ip)
                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