[SR-Users] siremis CDR gneration procedure

[Klaus Darilion]

On 12.02.2013 10:32, Daniel-Constantin Mierla wrote:
>
> 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?

I just found that it is strange that some BYEs have a cdr_id and others 
don't. IMO it would nice to set cdr_id in all transactions which were 
sucessfully used to generate a call-CDR.

regards
Klaus