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

Mon Oct 12 00:00:00 2009
        Service-Type = Framed-User
        Framed-Protocol = PPP
        NAS-Port = 17315
        NAS-Port-Type = Ethernet
        User-Name = "c-08-1372"
        Calling-Station-Id = "00:08:54:04:6A:20"
        NAS-Port-Id = "vlan6_obj"
        Acct-Session-Id = "818016ef"
        Framed-IP-Address = 10.5.251.191
        Acct-Authentic = RADIUS
        Event-Timestamp = "Oct 11 2009 23:59:47 MSD"
        Acct-Session-Time = 550
        Acct-Input-Octets = 916728
        Acct-Input-Gigawords = 0
        Acct-Input-Packets = 6394
        Acct-Output-Octets = 7974217
        Acct-Output-Gigawords = 0
        Acct-Output-Packets = 8032
        Acct-Status-Type = Interim-Update
        NAS-Identifier = "MikroTik"
        NAS-IP-Address = 192.168.0.10
        Acct-Delay-Time = 0
        Client-IP-Address = 192.168.0.10
        Acct-Unique-Session-Id = "fc01597bc77c3744"
        Timestamp = 1255291200

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 dopdata.field_value FROM users,dopdata WHERE dopdata.parent_id=users.id 
  AND dopdata.template_num=1 AND dopdata.field_alias='_mac' AND users.name=login 
  ORDER BY dopdata.revision DESC LIMIT 1) <> '') THEN

  SELECT users.id,users.name,'Calling-Station-Id' AS Attribute,dopdata.field_value AS Value,'==' AS Op 
  FROM users,dopdata WHERE dopdata.parent_id=users.id AND dopdata.template_num=1 
  AND dopdata.field_alias='_mac' AND users.name=login ORDER BY dopdata.revision DESC LIMIT 1;
END IF;
END$$
DELIMITER ;

mysql> use bill;
Database changed

mysql> call radcheck('seroj');
+----+-------+-----------+--------+----+
| id | name  | Attribute | Value  | Op |
+----+-------+-----------+--------+----+
|  1 | seroj | Password  | pass11 | == |
+----+-------+-----------+--------+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call radcheck('yazva');
+----+-------+-----------+---------+----+
| id | name  | Attribute | Value   | Op |
+----+-------+-----------+---------+----+
|  2 | yazva | Password  | pass222 | == |
+----+-------+-----------+---------+----+
1 row in set (0.00 sec)

+----+-------+--------------------+-------------------+----+
| id | name  | Attribute          | Value             | Op |
+----+-------+--------------------+-------------------+----+
|  2 | yazva | Calling-Station-Id | 00:34:58:5D:21:43 | == |
+----+-------+--------------------+-------------------+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ORDER BY dopdata.revision DESC LIMIT 1

SELECT field_value FROM dopdata WHERE revision IN (SELECT rev FROM rev_users WHERE id IN (SELECT id FROM users where name=login)) AND field_alias='_mac';

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;

accounting_start_query = "insert into mac_log (id,mac) values ('%{SQL-User-Name}', ('%{Calling-Station-Id}'))"

testerlogin	00:40:F4:AE:DF:5F

postauth_query = "call radpostauth('%{SQL-User-Name}','%{Calling-Station-Id}')"

CREATE PROCEDURE `radpostauth` (IN login VARCHAR(64), IN mac VARCHAR(20))

update dopdata set field_value=mac WHERE parent_id=(SELECT id FROM users WHERE name=login) 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,'mykey') 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 ;