[SR-Users] siremis CDR gneration procedure

[Klaus Darilion]

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) );


2. (minor) It does not consider reINVITEs. I know the default Kamailio 
config does not account reINVITEs, but I think when reINVITEs are 
accounted too, this can mess up the CDR generation.


regards
Klaus