[SR-Users] siremis CDR gneration procedure

[Klaus Darilion]

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.

regards
Klaus