38p3y2Bi

1use asterisk;
2drop table if exists t_calllog;
3#create the initial table. First paren to be filled by update below, second to be filled by insert and sub-select.
4create table if not exists t_calllog (
5  usern     varchar(20),
6  url       varchar(255),
7  extension varchar(100),
8  recordingfile varchar(255),
9  disposition varchar(45)
10)(select 'OUTBOUND' AS direction,
11 c.uniqueid,
12 c.type,
13 c.start_time,
14 c.number_dialed,
15 c.length_in_sec,
16 c.server_ip
17from call_log c
18where type in ('Local') AND start_time >= '2018-11-30'
19);
20
21
22#inserts inbound call routes into the table.
23insert into t_calllog(direction,uniqueid,type,start_time,number_dialed,length_in_sec, server_ip)
24    (select 'INBOUND' AS direction, c.uniqueid, c.type, c.start_time, c.number_dialed, c.length_in_sec, c.server_ip
25     from call_log c
26     where channel_group in ('DID_INBOUND') AND caller_code = lpad(@numberdialed, 11, '1') AND start_time >= '2018-11-30'
27    );
28create index t_calllog_number_index
29on t_calllog(number_dialed);
30#inserts information from the recording log, matching vicidial_id and uniqueid
31update t_calllog, recording_log
32set usern = recording_log.user,
33    url = recording_log.location,
34    t_calllog.extension = recording_log.extension
35where lpad(recording_log.extension, 12, '9') = t_calllog.number_dialed;
36
37#simple union to bring the table together
38select direction, type, start_time, number_dialed, URL, extension, usern, length_in_sec from t_calllog where direction = 'OUTBOUND'
39union
40select direction, type, start_time, number_dialed, URL, extension, usern, length_in_sec from t_calllog where direction = 'INBOUND'
41order by extension;
42
43#set the number dialed from the php, where clause pads the number on the left with 91 which is
44#necessary to get the correct information from the table. This only works for outbound and we will need another for inbound.
45set @numberdialed = '6056513123' COLLATE utf8_unicode_ci;
46select * from t_calllog where number_dialed = lpad(@numberdialed, 12, '91');
47
48
49#
50#
51#
52#
53#
54# Next query is for .56 PBX
55#
56#
57#
58#
59
60set @luphonenumber = '4073461117' COLLATE utf8_unicode_ci;
61use asteriskcdrdb;
62drop table if exists asteriskcdrdb.calllogtmp;
63
64create table if not exists calllogtmp(usern varchar(20),
65                                      url varchar(255),
66                                      extension varchar(100),
67                                      uniqueid varchar(20),
68                                      type varchar(10))(
69select 'OUTBOUND' as direction,  src , dst, calldate, duration, billsec, disposition, did,  recordingfile, channel, dstchannel from asteriskcdrdb.cdr
70where dst = @luphonenumber and dstchannel like 'PJSIP/flowroute%'
71);
72
73insert into calllogtmp (direction, src, dst, calldate,duration,billsec,disposition,did, recordingfile, channel, dstchannel)
74select 'INBOUND' as direction,  src , dst, calldate, duration, billsec, disposition, did,  recordingfile, channel, dstchannel from asteriskcdrdb.cdr
75where src = lpad(@luphonenumber, 12, '+1') and channel like 'PJSIP/flowroute%';
76
77
78#select * from calllogtmp limit 1000;
79
80
81select direction, dst as phonenumber, calldate, billsec as seconds, disposition, recordingfile as recording, usern, url, extension, uniqueid, type from asteriskcdrdb.calllogtmp where direction = 'OUTBOUND'
82union
83select direction, dst as phonenumber, calldate, billsec as seconds, disposition, recordingfile as recording, usern, url, extension, uniqueid, type from asteriskcdrdb.calllogtmp where direction = 'INBOUND'
84order by calldate desc
85;