[OpenSIPS-Users] OpenSIPS-CP and cdrviewer

[Gavin Henry]

Hi,


Can anyoen help debug the stored procedure for the cdrs table?

mysql> desc acc;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| method     | char(16)         | NO   |     |         |                |
| from_tag   | char(64)         | NO   |     |         |                |
| to_tag     | char(64)         | NO   |     |         |                |
| callid     | char(64)         | NO   | MUL |         |                |
| sip_code   | char(3)          | NO   |     |         |                |
| sip_reason | char(32)         | NO   |     |         |                |
| time       | datetime         | NO   |     | NULL    |                |
| cdr_id     | varchar(32)      | YES  |     | NULL    |                |
| from_uri   | varchar(64)      | NO   |     | NULL    |                |
| to_uri     | varchar(64)      | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> desc cdrs;
+-----------------+------------------+------+-----+---------------------+----------------+
| Field           | Type             | Null | Key | Default
 | Extra          |
+-----------------+------------------+------+-----+---------------------+----------------+
| cdr_id          | bigint(20)       | NO   | PRI | NULL
 | auto_increment |
| call_start_time | datetime         | NO   |     | 0000-00-00
00:00:00 |                |
| duration        | int(10) unsigned | NO   |     | 0
 |                |
| sip_call_id     | varchar(128)     | NO   |     |
 |                |
| sip_from_tag    | varchar(128)     | NO   |     |
 |                |
| sip_to_tag      | varchar(128)     | NO   |     |
 |                |
| created         | datetime         | NO   |     | 0000-00-00
00:00:00 |                |
+-----------------+------------------+------+-----+---------------------+----------------+
7 rows in set (0.00 sec)

We are using the
/var/www/opensips-cp/web/tools/cdrviewer/opensips_cdrs_1_5.sql from
version 2.0 which has:

USE opensips ;
DROP PROCEDURE IF EXISTS `opensips_cdrs_1_5` ;
DELIMITER //
CREATE PROCEDURE opensips_cdrs_1_5()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE bye_record INT DEFAULT 0;
  DECLARE v_callid,v_from_tag, v_to_tag VARCHAR(64);
  DECLARE v_inv_time, v_bye_time DATETIME;
  DECLARE inv_cursor CURSOR FOR SELECT time, callid, from_tag, to_tag
FROM opensips.acc where method='INVITE' and cdr_id='0';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN inv_cursor;
  REPEAT
    FETCH inv_cursor INTO v_inv_time, v_callid, v_from_tag, v_to_tag;
    IF NOT done THEN
      SET bye_record = 0;
      SELECT 1, time INTO bye_record, v_bye_time FROM opensips.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
(call_start_time,duration,sip_call_id,sip_from_tag,sip_to_tag,created)
VALUES (v_inv_time,UNIX_TIMESTAMP(v_bye_time)-UNIX_TIMESTAMP(v_inv_time),v_callid,v_from_tag,v_to_tag,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) OR (from_tag=v_to_tag
AND to_tag=v_from_tag));
      END IF;
      SET done = 0;
    END IF;
  UNTIL done END REPEAT;
END
//
DELIMITER ;


There is data with BYE and INVITE in our acc table.

Thanks.