KafMcJ0K

1CREATE TABLE IF NOT EXISTS `data_month` (
2  `calledstationid` VARCHAR(50),
3  `in_octets`  BIGINT(20) DEFAULT 0,
4  `out_octets` BIGINT(20) DEFAULT 0,
5  `year`       INT(4),
6  `month`      INT(2),
7  `last_update` DATETIME,
8  PRIMARY KEY (`calledstationid`, `year`, `month`)
9) ENGINE MyISAM;
10
11CREATE TRIGGER `monthDataStatistics` BEFORE UPDATE ON `radacct` FOR EACH ROW
12  INSERT INTO data_month (
13    `calledstationid`,
14    `in_octets`,
15    `out_octets`,
16    `year`,
17    `month`,
18    `last_update`)
19  VALUES (
20    old.calledstationid,
21    new.acctinputoctets - old.acctinputoctets,
22    new.acctoutputoctets - old.acctoutputoctets,
23    YEAR(CURDATE()),
24    MONTH(CURDATE()),
25    CURRENT_TIMESTAMP()
26  )
27  ON DUPLICATE KEY UPDATE
28    `in_octets` = (`in_octets` + (new.acctinputoctets - old.acctinputoctets)),
29    `out_octets` = (`out_octets` + (new.acctoutputoctets - old.acctoutputoctets)),
30    `last_update` = CURRENT_TIMESTAMP();