[SR-Users] siremis CDR gneration procedure

[Daniel-Constantin Mierla]

On 2/4/13 5:17 PM, Klaus Darilion wrote:
>
>
> On 28.01.2013 10:32, Daniel-Constantin Mierla wrote:
>> Hello,
>>
>> On 1/17/13 3:38 PM, Klaus Darilion wrote:
>>> Hi!
>>>
>>> IMO the siremis CDR generation procedure is buggy:
>>>
>>> CREATE PROCEDURE `kamailio_cdrs`()
>>>   DECLARE inv_cursor CURSOR FOR SELECT src_user, src_domain, dst_user,
>>>      dst_domain, dst_ouser, time, callid,from_tag, to_tag, src_ip
>>>      FROM acc
>>>      where method='INVITE' and cdr_id='0';
>>> ...
>>>   REPEAT
>>>     FETCH inv_cursor INTO v_src_user,
>>>             v_src_domain, v_dst_user, v_dst_domain,
>>>             v_dst_ouser, v_inv_time, v_callid, v_from_tag,
>>>             v_to_tag, v_src_ip;
>>>     IF NOT done THEN
>>>       SET bye_record = 0;
>>>       SELECT 1, time INTO bye_record, v_bye_time FROM acc WHERE
>>>                  method='BYE' AND callid=v_callid AND
>>>                  ((from_tag=v_from_tag AND to_tag=v_to_tag)
>>>                  OR (from_tag=v_to_tag AND to_tag=v_from_tag))
>>>                  ORDER BY time ASC LIMIT 1;
>>>       IF bye_record = 1 THEN
>>>         INSERT INTO cdrs (src_username,src_domain,dst_username,
>>>                  dst_domain,dst_ousername,call_start_time,
>>>                  duration,sip_call_id,
>>>                  sip_from_tag,sip_to_tag,src_ip,created) VALUES
>>>                  (v_src_user, v_src_domain,v_dst_user,
>>>                  v_dst_domain,v_dst_ouser,v_inv_time,
>>> UNIX_TIMESTAMP(v_bye_time)-UNIX_TIMESTAMP(v_inv_time),
>>>                  v_callid,v_from_tag,v_to_tag,v_src_ip,NOW());
>>>         UPDATE acc SET cdr_id=last_insert_id() WHERE callid=v_callid
>>>                  AND from_tag=v_from_tag AND to_tag=v_to_tag;
>>>       END IF;
>>>       SET done = 0;
>>>     END IF;
>>>   UNTIL done END REPEAT;
>>> ...
>>>
>>> 1. The UPDATE query will not UPDATE BYE records which were sent from
>>> CALLEE to CALLER. This can be easily fixed with:
>>>
>>>         UPDATE acc SET cdr_id=last_insert_id() WHERE callid=v_callid
>>>                  AND ( (from_tag=v_from_tag AND to_tag=v_to_tag) OR
>>>                        (to_tag=v_from_tag AND from_tag=v_to_tag) );
>>>
>>>
>> the idea is to update the INVITE record not to select it in future
>> executions.
>
> I do not get that. AFAIS it will only UPDATE the INVITE, but not the BYE.
BYE does not need to be updated, because the cursor goes on INVITE 
records that have cdr_id=0, then will search for a matching BYE on 
callid and tags.

Putting cdr for all BYEs can be just for aesthetic purposes, but will 
not affect CDRs in a bad or good way.

Or do you see another reason why the BYE record needs the cdr_id?

Cheers,
Daniel

>
> regards
> Klaus

-- 
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio World Conference, April 16-17, 2013, Berlin
  - http://conference.kamailio.com -