[Freeswitch-trunk] [commit] r7192 - freeswitch/trunk/scripts/contrib/wasim

12 Jan 2008

Author: mikej
Date: Sat Jan 12 15:03:00 2008
New Revision: 7192

Added:
   freeswitch/trunk/scripts/contrib/wasim/
   freeswitch/trunk/scripts/contrib/wasim/cFS.pl
   freeswitch/trunk/scripts/contrib/wasim/cdrload.pl
   freeswitch/trunk/scripts/contrib/wasim/fshour.pl

Log:
add scripts from wasim (MODAPP-69 MODEVENT-11)

Added: freeswitch/trunk/scripts/contrib/wasim/cFS.pl
==============================================================================

--- (empty file)
+++ freeswitch/trunk/scripts/contrib/wasim/cFS.pl	Sat Jan 12 15:03:00 2008
@@ -0,0 +1,84 @@
+#!/usr/bin/perl
+
+# Convergence FreeSwitch Tools Version 7.0 : AGI
+# (c) MMII Convergence. All rights reserved.
+# <info@convergence.pk> http://www.convergence.pk
+
+# This program is free software, distributed under the terms of
+# the GNU General Public License.http://www.gnu.org/licenses.html
+
+#$0 -t day -s 30 -e 20 -p 92 will get stats on daily basis for the last 30 to 20 hours for number beginning with 92 
+# make sure end_timestamp is indexed in the db
+
+#use warnings;
+use strict;
+use DBI();
+use Getopt::Long;
+
+GetOptions (
+    't|time:s' => \(my $t = 'hour'),
+    's|start:i' => \(my $i = '24'),
+    'e|end:i' => \(my $j = '0'),
+    'p|prefix:s' => \(my $prefix = 'WHERE 1')
+);
+
+my $prefix = "WHERE destination_number like '$prefix%' " if ($prefix ne 'WHERE 1');
+
+my $dbh		= DBI->connect("DBI:mysql:database=freeswitch;host=x.x.x.x","USER","PASS") or &die_call;
+
+my ($tcalls,$tans,$tmins,$tamins,$tacd,$tasr,$tbpct,$tbmins,$tpdd);
+while ($i > $j) {
+	&get_stats($i);
+	$i--;
+}
+$tasr 	= eval { ( sprintf("%.2f", $tans / $tcalls * 100)) };
+$tacd 	= eval { ( sprintf("%.2f", $tamins / $tans)) };
+$tbpct 	= eval { ( sprintf("%.2f", $tamins / $tmins * 100)) };
+$tpdd	= eval { ( sprintf("%.2f", (($tbmins - $tamins) * 60) / $tans)) };
+	
+print "\t\t\t       ------ \t        -----\t     ------\t        ------ \t     ---- \t     -----\t       ------\n" if $tans;
+print "\t\t\ttotal: $tcalls\tanswer: $tans\tasr: $tasr%\tminute: $tamins\tacd: $tacd\tpdd: $tpdd\tbill%: $tbpct%\n" if $tans;
+
+exit;
+
+sub get_stats {
+	my ($calls,$ans,$mins,$amins,$acd,$asr,$bpct,$pdd,$bmins);
+	my $time	= shift;
+	my $ntime	= $time-1;
+	my $stm		= "select count(*) as calls, sum(duration)/60 as mins from cdr $prefix and end_timestamp between 
+				date_sub(now(), interval $time $t) and date_sub(now(), interval $ntime $t)";
+	my $res 	= &do_db($stm);
+	$calls 		= $res->{'calls'};
+	$tcalls		= $tcalls + $calls;
+	$mins 		= sprintf("%.0f", $res->{'mins'});
+	$tmins		= $tmins + $mins;
+
+	$stm		= "select count(*) as ans, sum(billsec)/60 as amins, sum(duration)/60 as bmins,date_sub(now(), 
+				interval $ntime $t) as start from cdr $prefix AND HANGUP_CAUSE='NORMAL_CLEARING' 
+				and end_timestamp between date_sub(now(), interval $time $t) and date_sub(now(), interval $ntime $t)";
+	$res 		= &do_db($stm);
+	my $when	= $res->{'start'};
+	$ans		= $res->{'ans'};
+	$tans		= $tans + $ans;
+	$amins		= sprintf("%.0f", $res->{'amins'});
+	$tamins		= $tamins + $amins;
+	$bmins		= sprintf("%.0f", $res->{'bmins'});
+	$tbmins		= $tbmins + $bmins;
+
+	$asr 	= eval { ( sprintf("%.2f", $ans / $calls * 100)) };
+	$acd 	= eval { ( sprintf("%.2f", $amins / $ans)) };
+	$bpct 	= eval { ( sprintf("%.2f", $amins / $mins * 100)) };
+	$pdd	= eval { ( sprintf("%.2f", (($bmins - $amins) * 60) / $ans)) };
+	
+	print "$time $when\tcalls: $calls\tanswer: $ans\tasr: $asr%\tminute: $amins\tacd: $acd\tpdd: $pdd\tbill%: $bpct%\n" if $ans;
+}
+
+sub do_db {
+	my $sql         = shift;
+#	print "$sql\n";
+	my $get         = $dbh->prepare($sql) or die "Couldn't prepare statement: " . $dbh->errstr;
+	$get->execute();
+	my $res         = $get->fetchrow_hashref();
+	$get->finish();
+	return $res;
+}

Added: freeswitch/trunk/scripts/contrib/wasim/cdrload.pl
==============================================================================
--- (empty file)
+++ freeswitch/trunk/scripts/contrib/wasim/cdrload.pl	Sat Jan 12 15:03:00 2008
@@ -0,0 +1,32 @@
+#!/usr/bin/perl -w
+
+# Convergence FreeSwitch Tools Version 7.0 : AGI
+# (c) MMII Convergence. All rights reserved.
+# <info@convergence.pk> http://www.convergence.pk
+
+# This program is free software, distributed under the terms of
+# the GNU General Public License.http://www.gnu.org/licenses.html
+
+use strict;
+use DBI();
+
+my @cc 	= ("killall", "-HUP", "freeswitch");
+system(@cc) == 0
+	or die "$0: system @cc failed: $?";
+
+my $dbh	= DBI->connect("DBI:mysql:database=DB;host=HOST","USER","PASSWORD") 
+	or die "$0: Couldn't connect to database: " . DBI->errstr;
+
+my @LS	= `ls -1t /usr/local/freeswitch/log/cdr-csv/Master.csv.*`;
+foreach my $line (@LS) {
+	chop($line);
+	my $stm	= "load data local infile '$line' into table TABLE fields enclosed by '\"' terminated by ','";
+	my $ul	= $dbh->prepare($stm)
+		or die "$0: Couldn't prepare statement $stm: " . $dbh->errstr;;
+	$ul->execute();
+	$ul->finish;
+	system("cat $line >> /usr/local/freeswitch/log/cdr-csv/FULL_Master.csv");
+	unlink $line;
+}
+
+exit;

Added: freeswitch/trunk/scripts/contrib/wasim/fshour.pl
==============================================================================
--- (empty file)
+++ freeswitch/trunk/scripts/contrib/wasim/fshour.pl	Sat Jan 12 15:03:00 2008
@@ -0,0 +1,87 @@
+#!/usr/bin/perl
+
+# Convergence FreeSwitch Tools Version 7.0 : AGI
+# (c) MMII Convergence. All rights reserved.
+# <info@convergence.pk> http://www.convergence.pk
+
+# This program is free software, distributed under the terms of
+# the GNU General Public License.http://www.gnu.org/licenses.html
+
+# usage: $0 interval start end prefix
+# for example: $0 day 30 20 92 will get stats on daily basis for the last 30 to 30 hours for number beginning with 92
+# make sure end_timestamp is indexed in the db
+
+#use warnings;
+my $debug=0;
+use strict;
+use DBI();
+use Getopt::Long;
+
+GetOptions (
+    't|time:s' => \(my $t = 'hour'),
+    's|start:i' => \(my $i = '24'),
+    'e|end:i' => \(my $j = '0'),
+    'p|prefix:s' => \(my $p = 'WHERE 1'),
+    'n|notprefix:s' => \(my $n = 'AND 1'),
+    'd|date:s'	=> \(my $d = 'NOW()')
+);
+
+$p		= "WHERE destination_number like '$p%' " if ($p ne 'WHERE 1');
+$n		= "AND destination_number not like '$n%' " if ($n ne 'AND 1');
+my $dbh		= DBI->connect("DBI:mysql:database=freeswitch;host=x.x.x.x","USER","PASS") or &die_call;
+
+my ($tcalls,$tans,$tmins,$tamins,$tacd,$tasr,$tbpct,$tbmins,$tpdd);
+
+while ($i > $j) {
+	&get_stats($d,$i);
+	$i--;
+}
+$tasr 	= eval { ( sprintf("%.2f", $tans / $tcalls * 100)) };
+$tacd 	= eval { ( sprintf("%.2f", $tamins / $tans)) };
+$tbpct 	= eval { ( sprintf("%.2f", $tamins / $tmins * 100)) };
+$tpdd	= eval { ( sprintf("%.2f", (($tbmins - $tamins) * 60) / $tans)) };
+	
+print "\t\t\t       ------ \t        -----\t     ------\t        ------ \t     ---- \t     -----\t       ------\n" if $tans;
+print "\t\t\ttotal: $tcalls\tanswer: $tans\tasr: $tasr%\tminute: $tamins\tacd: $tacd\tpdd: $tpdd\tbill%: $tbpct%\n" if $tans;
+
+exit;
+
+sub get_stats {
+	my $x		= shift;
+	my $i		= shift;
+	my $j		= $i-1;
+	my ($calls,$ans,$mins,$amins,$acd,$asr,$bpct,$pdd,$bmins);
+	my $stm		= "SELECT COUNT(*) AS calls, SUM(duration)/60 AS mins FROM cdr $p $n AND end_timestamp BETWEEN DATE_SUB($d, INTERVAL $i $t) AND DATE_SUB($d, INTERVAL $j $t)";
+	my $res 	= &do_db($stm);
+	$calls 		= $res->{'calls'};
+	$tcalls		= $tcalls + $calls;
+	$mins 		= sprintf("%.0f", $res->{'mins'});
+	$tmins		= $tmins + $mins;
+
+	$stm		= "SELECT COUNT(*) AS ans, SUM(billsec)/60 AS amins, SUM(duration)/60 AS bmins, DATE_SUB($d, INTERVAL $i $t) AS start FROM cdr $p $n AND HANGUP_CAUSE='NORMAL_CLEARING' AND end_timestamp BETWEEN DATE_SUB($d, INTERVAL $i $t) AND DATE_SUB($d, INTERVAL $j $t)";
+	$res 		= &do_db($stm);
+	my $when	= $res->{'start'};
+	$ans		= $res->{'ans'};
+	$tans		= $tans + $ans;
+	$amins		= sprintf("%.0f", $res->{'amins'});
+	$tamins		= $tamins + $amins;
+	$bmins		= sprintf("%.0f", $res->{'bmins'});
+	$tbmins		= $tbmins + $bmins;
+
+	$asr 	= eval { ( sprintf("%.2f", $ans / $calls * 100)) };
+	$acd 	= eval { ( sprintf("%.2f", $amins / $ans)) };
+	$bpct 	= eval { ( sprintf("%.2f", $amins / $mins * 100)) };
+	$pdd	= eval { ( sprintf("%.2f", (($bmins - $amins) * 60) / $ans)) };
+	
+	print "$i $when\tcalls: $calls\tanswer: $ans\tasr: $asr%\tminute: $amins\tacd: $acd\tpdd: $pdd\tbill%: $bpct%\n" if $ans;
+}
+
+sub do_db {
+	my $sql         = shift;
+	print "$sql\n" if $debug;
+	my $get         = $dbh->prepare($sql) or die "Couldn't prepare statement: " . $dbh->errstr;
+	$get->execute();
+	my $res         = $get->fetchrow_hashref();
+	$get->finish();
+	return $res;
+}

    

[Freeswitch-trunk] [commit] r7192 - freeswitch/trunk/scripts/contrib/wasim

Freeswitch SVN