/
mod.install.siremis.sql
175 lines (156 loc) · 6.43 KB
/
mod.install.siremis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
DROP TABLE IF EXISTS acc;
CREATE TABLE `acc` (
`id` int(10) unsigned NOT NULL auto_increment,
`method` varchar(16) NOT NULL default '',
`from_tag` varchar(64) NOT NULL default '',
`to_tag` varchar(64) NOT NULL default '',
`callid` varchar(128) NOT NULL default '',
`sip_code` char(3) NOT NULL default '',
`sip_reason` varchar(32) NOT NULL default '',
`time` datetime NOT NULL default '2000-01-01 00:00:00',
`src_ip` varchar(64) NOT NULL default '',
`dst_ouser` VARCHAR(64) NOT NULL DEFAULT '',
`dst_user` varchar(64) NOT NULL default '',
`dst_domain` varchar(128) NOT NULL default '',
`src_user` varchar(64) NOT NULL default '',
`src_domain` varchar(128) NOT NULL default '',
`cdr_id` integer NOT NULL default '0',
INDEX acc_callid (`callid`),
PRIMARY KEY (`id`)
);
DROP TABLE IF EXISTS missed_calls;
CREATE TABLE `missed_calls` (
`id` int(10) unsigned NOT NULL auto_increment,
`method` varchar(16) NOT NULL default '',
`from_tag` varchar(64) NOT NULL default '',
`to_tag` varchar(64) NOT NULL default '',
`callid` varchar(128) NOT NULL default '',
`sip_code` char(3) NOT NULL default '',
`sip_reason` varchar(32) NOT NULL default '',
`time` datetime NOT NULL default '2000-01-01 00:00:00',
`src_ip` varchar(64) NOT NULL default '',
`dst_ouser` VARCHAR(64) NOT NULL DEFAULT '',
`dst_user` varchar(64) NOT NULL default '',
`dst_domain` varchar(128) NOT NULL default '',
`src_user` varchar(64) NOT NULL default '',
`src_domain` varchar(128) NOT NULL default '',
`cdr_id` integer NOT NULL default '0',
INDEX mc_callid (`callid`),
PRIMARY KEY (`id`)
);
DROP TABLE IF EXISTS `cdrs`;
CREATE TABLE `cdrs` (
`cdr_id` bigint(20) NOT NULL auto_increment,
`src_username` varchar(64) NOT NULL default '',
`src_domain` varchar(128) NOT NULL default '',
`dst_username` varchar(64) NOT NULL default '',
`dst_domain` varchar(128) NOT NULL default '',
`dst_ousername` varchar(64) NOT NULL default '',
`call_start_time` datetime NOT NULL default '2000-01-01 00:00:00',
`duration` int(10) unsigned NOT NULL default '0',
`sip_call_id` varchar(128) NOT NULL default '',
`sip_from_tag` varchar(128) NOT NULL default '',
`sip_to_tag` varchar(128) NOT NULL default '',
`src_ip` varchar(64) NOT NULL default '',
`cost` integer NOT NULL default '0',
`rated` integer NOT NULL default '0',
`created` datetime NOT NULL,
PRIMARY KEY (`cdr_id`),
UNIQUE KEY `uk_cft` (`sip_call_id`,`sip_from_tag`,`sip_to_tag`)
);
DROP TABLE IF EXISTS `billing_rates`;
CREATE TABLE `billing_rates` (
`rate_id` bigint(20) NOT NULL auto_increment,
`rate_group` varchar(64) NOT NULL default 'default',
`prefix` varchar(64) NOT NULL default '',
`rate_unit` integer NOT NULL default '0',
`time_unit` integer NOT NULL default '60',
PRIMARY KEY (`rate_id`),
UNIQUE KEY `uk_rp` (`rate_group`,`prefix`)
);
DROP PROCEDURE IF EXISTS `kamailio_cdrs`;
DROP PROCEDURE IF EXISTS `kamailio_rating`;
DELIMITER %%
CREATE PROCEDURE `kamailio_cdrs`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE bye_record INT DEFAULT 0;
DECLARE v_src_user,v_src_domain,v_dst_user,v_dst_domain,v_dst_ouser,v_callid,
v_from_tag,v_to_tag,v_src_ip VARCHAR(64);
DECLARE v_inv_time, v_bye_time DATETIME;
DECLARE inv_cursor CURSOR FOR SELECT src_user, src_domain, dst_user,
dst_domain, dst_ouser, time, callid,from_tag, to_tag, src_ip
FROM acc
where method='INVITE' and cdr_id='0';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN inv_cursor;
REPEAT
FETCH inv_cursor INTO v_src_user, v_src_domain, v_dst_user, v_dst_domain,
v_dst_ouser, v_inv_time, v_callid, v_from_tag, v_to_tag, v_src_ip;
IF NOT done THEN
SET bye_record = 0;
SELECT 1, time INTO bye_record, v_bye_time FROM acc WHERE
method='BYE' AND callid=v_callid AND ((from_tag=v_from_tag
AND to_tag=v_to_tag)
OR (from_tag=v_to_tag AND to_tag=v_from_tag))
ORDER BY time ASC LIMIT 1;
IF bye_record = 1 THEN
INSERT INTO cdrs (src_username,src_domain,dst_username,
dst_domain,dst_ousername,call_start_time,duration,sip_call_id,
sip_from_tag,sip_to_tag,src_ip,created) VALUES (v_src_user,
v_src_domain,v_dst_user,v_dst_domain,v_dst_ouser,v_inv_time,
UNIX_TIMESTAMP(v_bye_time)-UNIX_TIMESTAMP(v_inv_time),
v_callid,v_from_tag,v_to_tag,v_src_ip,NOW());
UPDATE acc SET cdr_id=last_insert_id() WHERE callid=v_callid
AND from_tag=v_from_tag AND to_tag=v_to_tag;
END IF;
SET done = 0;
END IF;
UNTIL done END REPEAT;
END
%%
CREATE PROCEDURE `kamailio_rating`(`rgroup` varchar(64))
BEGIN
DECLARE done, rate_record, vx_cost INT DEFAULT 0;
DECLARE v_cdr_id BIGINT DEFAULT 0;
DECLARE v_duration, v_rate_unit, v_time_unit INT DEFAULT 0;
DECLARE v_dst_username VARCHAR(64);
DECLARE cdrs_cursor CURSOR FOR SELECT cdr_id, dst_username, duration
FROM cdrs WHERE rated=0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cdrs_cursor;
REPEAT
FETCH cdrs_cursor INTO v_cdr_id, v_dst_username, v_duration;
IF NOT done THEN
SET rate_record = 0;
SELECT 1, rate_unit, time_unit INTO rate_record, v_rate_unit, v_time_unit
FROM billing_rates
WHERE rate_group=rgroup AND v_dst_username LIKE concat(prefix, '%')
ORDER BY prefix DESC LIMIT 1;
IF rate_record = 1 THEN
SET vx_cost = v_rate_unit * CEIL(v_duration/v_time_unit);
UPDATE cdrs SET rated=1, cost=vx_cost WHERE cdr_id=v_cdr_id;
END IF;
SET done = 0;
END IF;
UNTIL done END REPEAT;
END
%%
DELIMITER ;
DROP TABLE IF EXISTS `statistics`;
CREATE TABLE `statistics` (
`id` int(10) unsigned NOT NULL auto_increment,
`time_stamp` int(10) unsigned NOT NULL default '0',
`shm_used_size` int(10) unsigned NOT NULL default '0',
`shm_real_used_size` int(10) unsigned NOT NULL default '0',
`shm_max_used_size` int(10) unsigned NOT NULL default '0',
`shm_free_used_size` int(10) unsigned NOT NULL default '0',
`ul_users` int(10) unsigned NOT NULL default '0',
`ul_contacts` int(10) unsigned NOT NULL default '0',
`tm_active` int(10) unsigned NOT NULL default '0',
`rcv_req_diff` int(10) unsigned NOT NULL default '0',
`fwd_req_diff` int(10) unsigned NOT NULL default '0',
`2xx_trans_diff` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
);
INSERT INTO domain (domain, did) VALUES ('127.0.0.1', 'default');