[OpenSIPS-Users] OpenSIPS-CP and cdrviewer

[Iulia Bublea]

Have you changed in the porcedure the name of the database to the db you 
are using?

It appears in 3 places "opensips"...change that to your db.

Regards,
Iulia

Gavin Henry wrote:
> 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.
>
> _______________________________________________
> Users mailing list
> Users at lists.opensips.org
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>
>