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

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

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

CREATE TABLE IF NOT EXISTS `ses_traf` (
  `id` bigint(21) NOT NULL auto_increment,
  `ses_id` varchar(32) NOT NULL default '',
  `time` int(10) unsigned NOT NULL DEFAULT '0',
  `traf_in` bigint(12) unsigned NOT NULL DEFAULT '0',
  `traf_out` bigint(12) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ses_id` (`ses_id`),
  KEY `time` (`time`)
) ENGINE=MyISAM AUTO_INCREMENT=1;


DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16),
    IN properties VARCHAR(255), IN ses VARCHAR(32), IN trafin BIGINT(20))
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;
  CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
  UPDATE users_trf SET in1=in1 + trafin - IFNULL(
    (SELECT traf_in FROM ses_traf WHERE ses_id=ses LIMIT 1),0)
    WHERE uid=usr_id;
  INSERT INTO ses_traf SET ses_id=ses, traf_in=trafin, time=UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE traf_in=trafin, time=UNIX_TIMESTAMP();
END$$
DELIMITER ;

root@mxradius:/etc/freeradius/sql/mysql# cat tinco.conf
# -*- text -*-
##
## dialup.conf -- MySQL configuration for default schema (schema.sql)
##
##      $Id$

sql_user_name = "%{User-Name}"

authorize_check_query = "call radcheck('%{SQL-User-Name}')"
authorize_reply_query = "call mxvrfreply('%{SQL-User-Name}')"
accounting_update_query = "call radupdate('%{SQL-User-Name}')"
accounting_stop_query = "call radstop('%{SQL-User-Name}')"

DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16),
    IN properties VARCHAR(255), IN ses VARCHAR(32), IN trafin BIGINT(20))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE t_in BIGINT;
  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  SELECT get_ip(usr_id) INTO usr_ip;
  CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
  SELECT trafin - IFNULL((SELECT traf_in FROM ses_traf WHERE ses_id=ses LIMIT 1),0) INTO t_in;
  UPDATE users_trf SET in1=in1 + t_in WHERE uid=usr_id;
  INSERT INTO ses_traf SET ses_id=ses, traf_in=trafin, time=UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE traf_in=trafin, time=UNIX_TIMESTAMP();
  SET @s = CONCAT('INSERT INTO X', YEAR(NOW()), '_', MONTH(NOW()), '_', DAY(NOW()), ' VALUES(?,?,?,?,?,?)');
  PREPARE stmt1 FROM @s; 
  SET @iface = 0;
  SET @cls = 1;
  SET @ts = CEIL(UNIX_TIMESTAMP() / 60) * 60;
  SET @usr_id = usr_id;
  SET @t_in = t_in;
  SET @t_out = 0;
  EXECUTE stmt1 USING @usr_id, @iface, @ts, @cls, @t_in, @t_out;
  DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;