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

bash# mysql --password=`grep sql_root_pass /usr/local/nodeny/history.nod | sed s/\\\$sql_root_pass=\'//g | sed "s/\';//"` bill 

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

  SELECT ip INTO usr_ip FROM users WHERE name=login;

  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 PROCEDURE IF EXISTS `radpostauth`;
DELIMITER $$  
CREATE PROCEDURE `radpostauth` (IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;  
  SELECT id INTO usr_id FROM users WHERE name=login;
  INSERT into dblogin (mid,act,time) VALUES (usr_id,47,unix_timestamp());
END$$
DELIMITER ;

bash# ee /usr/local/etc/raddb/sql.conf

postauth_query = "call radpostauth('%{SQL-User-Name}')"

bash# ee /usr/local/etc/raddb/radiusd.conf

bash# /usr/local/etc/rc.d/radiusd restart

SELECT * FROM dopdata WHERE revision=(SELECT MAX(revision) FROM dopdata WHERE parent_id=5 AND field_alias='_mac')

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$  
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'=='
    FROM users WHERE name=login;
  SELECT NULL,login,'Calling-Station-Id' AS Attribute,field_value AS Value, '=='
    FROM dopdata WHERE revision=(SELECT MAX(revision) FROM dopdata WHERE parent_id=(SELECT id FROM users WHERE name=login)) AND field_alias='_mac';
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$  
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'==' AS Op FROM users WHERE name=login;

IF ((SELECT field_value from dopvalues where revision IN (SELECT rev FROM rev_users WHERE id IN (SELECT id FROM users where name=login)) and dopfield_id=4) <> '') THEN

SELECT id,name,'Calling-Station-Id' AS Attribute,(SELECT field_value from dopvalues where revision IN (SELECT rev FROM rev_users WHERE id IN (SELECT id FROM users where name=login)) 
and dopfield_id=4) AS Value,'==' AS Op from users where name=login;

END IF;

END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$  
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'=='
    FROM users WHERE name=login;
  SELECT id,login,'Calling-Station-Id' AS Attribute, LOWER(REPLACE(field_value,':','')) AS Value, '==' FROM dopdata WHERE revision=(SELECT MAX(revision) FROM dopdata WHERE parent_id=(SELECT id FROM users WHERE name=login)) AND field_alias='_mac'; 
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$  
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass3') AS Value,'==' AS Op FROM users WHERE name=login;

IF ((SELECT field_value from dopvalues where revision IN (SELECT rev FROM rev_users WHERE id IN (SELECT id FROM users where name=login)) and dopfield_id=4) <> '') THEN

SELECT id,login,'Calling-Station-Id' AS Attribute, LOWER(REPLACE(field_value,':','')) AS Value, '==' FROM dopdata WHERE revision=(SELECT MAX(revision) FROM dopdata WHERE parent_id=(SELECT id FROM users WHERE name=login)) AND field_alias='_mac';

END IF;

END$$
DELIMITER ;

echo '/var/log/mpd.log 600 5 100 * JC' >> /etc/newsyslog.conf

/var/log/radius.log                    600  5     *    @T00     JC    /var/run/radiusd/radiusd.pid