VUavYRa9

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
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# Query for .56 below
52#
53#
54#
55
56set @luphonenumber = '4073461117' COLLATE utf8_unicode_ci;
57use asteriskcdrdb;
58drop table if exists asteriskcdrdb.calllogtmp;
59
60create table if not exists calllogtmp(usern varchar(20),
61                                      url varchar(255),
62                                      extension varchar(100),
63                                      uniqueid varchar(20),
64                                      type varchar(10))(
65select 'OUTBOUND' as direction,  src , dst, calldate, duration, billsec, disposition, did,  recordingfile, channel, dstchannel from asteriskcdrdb.cdr
66where dst = @luphonenumber and dstchannel like 'PJSIP/flowroute%'
67);
68
69insert into calllogtmp (direction, src, dst, calldate,duration,billsec,disposition,did, recordingfile, channel, dstchannel)
70select 'INBOUND' as direction,  src , dst, calldate, duration, billsec, disposition, did,  recordingfile, channel, dstchannel from asteriskcdrdb.cdr
71where src = lpad(@luphonenumber, 12, '+1') and channel like 'PJSIP/flowroute%';
72
73
74#select * from calllogtmp limit 1000;
75
76
77select direction, dst as phonenumber, calldate, billsec as seconds, disposition, recordingfile as recording, usern, url, extension, uniqueid, type from asteriskcdrdb.calllogtmp where direction = 'OUTBOUND'
78union
79select direction, dst as phonenumber, calldate, billsec as seconds, disposition, recordingfile as recording, usern, url, extension, uniqueid, type from asteriskcdrdb.calllogtmp where direction = 'INBOUND'
80order by calldate desc
81;