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

#!/usr/bin/perl

use DBI;

my %config = (
    # MySQL
    'mysql_host' => 'localhost',
    'mysql_user' => 'bill_kernel',
    'mysql_password' => 'hardpass',
    'mysql_database' => 'bill',
    # NoDeny MAC dopfield id
    'dopfield_id' => '4',
);

##
## MySQL init
##
$dbh = DBI->connect("dbi:mysql:dbname=".$config{'mysql_database'}.";host=".$config{'mysql_host'}, $config{'mysql_user'}, $config{'mysql_password'});

# Select All ip <-> Mac pair
$query="SELECT u.ip AS ip, d.field_value AS mac FROM users u, dopvalues d, (
                SELECT u.ip AS ip, MAX(d.revision) AS rev FROM users u, dopvalues d
                    WHERE u.id=d.parent_id AND d.dopfield_id=".$config{'dopfield_id'}."
                    GROUP BY ip
                ) AS tmp
            WHERE u.id=d.parent_id
            AND d.dopfield_id=".$config{'dopfield_id'}."
            AND u.ip=tmp.ip
            AND d.revision=tmp.rev
            AND TRIM(d.field_value) != ''";


($sth = $dbh->prepare($query)) or die $DBI::errstr;
($sth->execute) or die $DBI::errstr;

# Processing MySQL ip-mac pairs
while(($ip,$mac)=$sth->fetchrow){
    print "$ip -> $mac\n";
}

AND TRIM(d.field_value) != ''

AND TRIM(d.field_value) = ''