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
|
class ChangeCallsActive < ActiveRecord::Migration
def self.up
execute "DROP VIEW IF EXISTS calls_active"
if ActiveRecord::Base.connection_config[:adapter] != 'sqlite3'
execute <<-SQL
CREATE VIEW calls_active AS SELECT
a.uuid AS uuid,
a.direction AS direction,
a.created_epoch AS start_stamp,
a.cid_name AS caller_id_name,
a.cid_num AS caller_id_number,
a.dest AS destination,
d.id AS sip_account_id,
d.caller_name AS sip_caller_name,
a.callee_name AS callee_name,
a.callee_num AS callee_number,
a.callstate AS callstate,
a.read_codec AS read_codec,
a.read_rate AS read_rate,
a.read_bit_rate AS read_bit_rate,
a.write_codec AS write_codec,
a.write_rate AS write_rate,
a.write_bit_rate AS write_bit_rate,
a.secure AS secure,
b.uuid AS b_uuid,
b.cid_name AS b_caller_id_name,
b.cid_num AS b_caller_id_number,
b.callstate AS b_callstate,
e.id AS b_sip_account_id,
e.caller_name AS b_sip_caller_name,
b.callee_name AS b_callee_name,
b.callee_num AS b_callee_number,
b.secure AS b_secure
FROM channels a
LEFT JOIN calls c ON a.uuid = c.caller_uuid AND a.hostname = c.hostname
LEFT JOIN channels b ON b.uuid = c.callee_uuid AND b.hostname = c.hostname
LEFT JOIN sip_accounts d ON a.presence_id LIKE CONCAT(d.auth_name, "@%")
LEFT JOIN sip_accounts e ON b.presence_id LIKE CONCAT(e.auth_name, "@%")
WHERE a.uuid = c.caller_uuid OR a.uuid NOT IN (select callee_uuid from calls)
SQL
else
execute <<-SQL
CREATE VIEW calls_active AS SELECT
a.uuid AS uuid,
a.direction AS direction,
a.created_epoch AS start_stamp,
a.cid_name AS caller_id_name,
a.cid_num AS caller_id_number,
a.dest AS destination,
d.id AS sip_account_id,
d.caller_name AS sip_caller_name,
a.callee_name AS callee_name,
a.callee_num AS callee_number,
a.callstate AS callstate,
a.read_codec AS read_codec,
a.read_rate AS read_rate,
a.read_bit_rate AS read_bit_rate,
a.write_codec AS write_codec,
a.write_rate AS write_rate,
a.write_bit_rate AS write_bit_rate,
a.secure AS secure,
b.uuid AS b_uuid,
b.cid_name AS b_caller_id_name,
b.cid_num AS b_caller_id_number,
b.callstate AS b_callstate,
e.id AS b_sip_account_id,
e.caller_name AS b_sip_caller_name,
b.callee_name AS b_callee_name,
b.callee_num AS b_callee_number,
b.secure AS b_secure
FROM channels a
LEFT JOIN calls c ON a.uuid = c.caller_uuid AND a.hostname = c.hostname
LEFT JOIN channels b ON b.uuid = c.callee_uuid AND b.hostname = c.hostname
LEFT JOIN sip_accounts d ON a.presence_id LIKE (d.auth_name || "@%")
LEFT JOIN sip_accounts e ON b.presence_id LIKE (e.auth_name || "@%")
WHERE a.uuid = c.caller_uuid OR a.uuid NOT IN (select callee_uuid from calls)
SQL
end
end
def self.down
end
end
|
