bm70pm3M

1use asterisk;
2drop table if exists t_calllog;
3
4create table if not exists t_calllog (
5  usern     varchar(20),
6  url       varchar(255),
7  extension varchar(100)
8)(select 'OUTBOUND' AS direction,
9 c.uniqueid,
10 c.type,
11 c.start_time,
12 c.number_dialed,
13 c.length_in_sec
14from call_log c
15where type in ('Local') AND start_time >= '2018-11-30'
16);
17
18insert into t_calllog(direction,uniqueid,type,start_time,number_dialed,length_in_sec)
19    (select 'INBOUND' AS direction, c.uniqueid, c.type, c.start_time, c.number_dialed, c.length_in_sec
20     from call_log c
21     where type in ('SIP') AND start_time >= '2018-11-30'
22    );
23
24update t_calllog, recording_log
25set usern = recording_log.user,
26    url = recording_log.location,
27    t_calllog.extension = recording_log.extension
28where recording_log.vicidial_id = t_calllog.uniqueid;
29
30select direction, type, start_time, number_dialed, URL, extension, usern, length_in_sec from t_calllog where direction = 'OUTBOUND'
31union
32select direction, type, start_time, number_dialed, URL, extension, usern, length_in_sec from t_calllog where direction = 'INBOUND'
33order by extension;
34
35set @numberdialed = '7045330873' COLLATE utf8_unicode_ci;
36select * from t_calllog where number_dialed = lpad(@numberdialed, 12, '91');