В связи с тем что в 13ой версии CDR стал выдавать информацию несколько иначе чем раньше в связи с чем посыпалась логика подсчета звонков, был написан sql для выдергивания из cel информации и приведению её к виду "старого" cdr.
Что он делает:
Из таблицы cel тащатся записи, которые потом "сворчаиваются" запросом в одну строку с началом разговора, концом разговора, подсчетом секунд разговора, и прочей информацией типа кто кому позвонил. Если вдруг интересно посмотрите пожалуйста у себя?
Если найдете ошибки буду рад замечаниям и исправлениям, ну и конечно дополнениям.
Код: Выделить всё
use asterisk;
select cel_start.id as start_id,
cel_start.eventtime as start_eventtime,
cel_app.eventtime as app_eventtime,
cel_answer.eventtime as answer_eventtime,
cel_end.eventtime as end_eventtime,
cel_start.context as start_context,
cel_start.cid_name as start_cid_name,
cel_start.cid_num as start_cid_num,
cel_start.exten as start_exten,
cel_answer.cid_num as answer_cid_num,
cel_answer.cid_name as answer_cid_name,
cel_start.uniqueid as start_uniqueid,
timestampdiff(SECOND, cel_start.eventtime, cel_end.eventtime) as delta_call_time,
timestampdiff(SECOND, cel_app.eventtime, cel_end.eventtime) as delta_app_time,
timestampdiff(SECOND, cel_answer.eventtime, cel_end.eventtime) as delta_answer_time
from (select * from cel where eventtype like 'CHAN_START' and uniqueid = linkedid and LENGTH(exten) > 3) as cel_start
left join (select min(id) as id, min(eventtime) as eventtime, uniqueid from cel where eventtype like 'APP_START' group by uniqueid) as cel_app
on cel_app.uniqueid = cel_start.uniqueid
left join (select id, eventtime, uniqueid from cel where eventtype like 'CHAN_END') as cel_end
on cel_end.uniqueid = cel_start.uniqueid
left join (select cel_min_brige.id, cel_min_brige.eventtime, cel_min_brige.uniqueid, cel_min_brige.linkedid, cel_min_brige.cid_name, cel_min_brige.cid_num from cel as cel_min_brige
inner join (select if(tran_cel.id_tran is null, min(с0.id), tran_cel.id_secbri) as id from cel as с0
left join (select с1.id as id_tran, с1.linkedid, с2.id as id_secbri from cel as с1
left join (select с3.id, с3.linkedid from cel as с3 where с3.eventtype like 'BRIDGE_ENTER' and с3.uniqueid != с3.linkedid) as с2
on с2.linkedid = с1.linkedid and с2.id > с1.id
where с1.eventtype like '%TRANSF%') as tran_cel
on tran_cel.linkedid = с0.linkedid
where eventtype like 'BRIDGE_ENTER' and с0.uniqueid != с0.linkedid
group by с0.linkedid, tran_cel.id_tran, tran_cel.id_secbri) as min_bridge
on min_bridge.id = cel_min_brige.id) as cel_answer
on cel_answer.linkedid = cel_start.uniqueid
