[Freeswitch-users] "show channels" command with duration - patch included

[freeswitch-users at lists.freeswitch.org]

Hi,

I usually find it very useful when I can retrieve a list of the currents 
calls along with durations. I noticed that the 'show channels' format 
does not include the duration (or the answered timestamp - so that one 
can extract it from there). So, I made a patch that includes the 
answered timestamp, the answered timestamp in epoch, and the duration in 
seconds. Of course these fields remain empty when the call hasn't been
answered yet.

I don't know if anyone else finds this functionality useful, so I am 
posting this patch here first (instead of JIRA) in order to get feedback 
from the users. If many of you (or the maintainers) find it interesting 
I can then proceed in posting it to JIRA.

-- 
-------------------------------------------
Apostolos Pantsiopoulos
Kinetix Tele.com R & D
email: regs at kinetix.gr
-------------------------------------------
-------------- next part --------------
Index: src/mod/applications/mod_commands/mod_commands.c
===================================================================
--- src/mod/applications/mod_commands/mod_commands.c	(revision 14256)
+++ src/mod/applications/mod_commands/mod_commands.c	(working copy)
@@ -2827,10 +2827,10 @@
 				}
 			}
 			if (strchr(argv[2], '%')) {
-				sprintf(sql, "select * from channels where uuid like '%s' or name like '%s' or cid_name like '%s' or cid_num like '%s' order by created_epoch",
+				sprintf(sql, "select *,strftime('%%s',DATETIME('NOW'))-answered_epoch as duration from channels where uuid like '%s' or name like '%s' or cid_name like '%s' or cid_num like '%s' order by created_epoch",
 						argv[2], argv[2], argv[2], argv[2]);
 			} else {
-				sprintf(sql, "select * from channels where uuid like '%%%s%%' or name like '%%%s%%' or cid_name like '%%%s%%' or cid_num like '%%%s%%' order by created_epoch",
+				sprintf(sql, "select *,strftime('%%s',DATETIME('NOW'))-answered_epoch as duration from channels where uuid like '%%%s%%' or name like '%%%s%%' or cid_name like '%%%s%%' or cid_num like '%%%s%%' order by created_epoch",
 						argv[2], argv[2], argv[2], argv[2]);
 
 			}
@@ -2839,10 +2839,10 @@
 				as = argv[4];
 		    }
 		} else {
-			sprintf(sql, "select * from channels order by created_epoch");
+			sprintf(sql, "select *,strftime('%%s',DATETIME('NOW'))-answered_epoch as duration from channels order by created_epoch");
 		}
 	} else if (!strcasecmp(command, "channels")) {
-		sprintf(sql, "select * from channels order by created_epoch");
+		sprintf(sql, "select *,strftime('%%s',DATETIME('NOW'))-answered_epoch as duration from channels order by created_epoch");
 		if (argv[1] && !strcasecmp(argv[1],"count")) {
 		    holder.justcount = 1;
 		    if (argv[3] && !strcasecmp(argv[2], "as")) {
@@ -2850,7 +2850,7 @@
 		    }
 		}
 	} else if (!strcasecmp(command, "distinct_channels")) {
-		sprintf(sql, "select * from channels left join calls on "
+		sprintf(sql, "select *,strftime('%%s',DATETIME('NOW'))-answered_epoch as duration from channels left join calls on "
 				"channels.uuid=calls.caller_uuid where channels.uuid not in (select callee_uuid from calls) order by created_epoch");
 		if (argv[2] && !strcasecmp(argv[1], "as")) {
 			as = argv[2];
Index: src/switch_core_sqldb.c
===================================================================
--- src/switch_core_sqldb.c	(revision 14256)
+++ src/switch_core_sqldb.c	(working copy)
@@ -309,9 +309,21 @@
 							 );
 
 		break;
+	case SWITCH_EVENT_CHANNEL_ANSWER:
+		{
+
+			sql = switch_mprintf("update channels set answered='%s',answered_epoch='%ld' where uuid='%s'",
+                                                         switch_event_get_header_nil(event, "event-date-local"),
+							 (long)switch_epoch_time_now(NULL),
+							 switch_event_get_header_nil(event, "unique-id")
+                                                         );
+
+		}
+		break;
 	case SWITCH_EVENT_CHANNEL_STATE:
 		{
 			char *state = switch_event_get_header_nil(event, "channel-state-number");
+
 			switch_channel_state_t state_i = CS_DESTROY;
 
 			if (!switch_strlen_zero(state)) {
@@ -492,7 +504,9 @@
 			"   read_rate  VARCHAR(255),\n" 
 			"   write_codec  VARCHAR(255),\n" 
 			"   write_rate  VARCHAR(255),\n" 
-			"   secure VARCHAR(255)\n"
+			"   secure VARCHAR(255),\n"
+			"   answered  VARCHAR(255),\n"
+                        "   answered_epoch  INTEGER\n"
 			");\ncreate index uuindex on channels (uuid);\n";
 		char create_calls_sql[] =
 			"CREATE TABLE calls (\n"