[Freeswitch-svn] [commit] r7192 - freeswitch/trunk/scripts/contrib/wasim
Freeswitch SVN
mikej at freeswitch.org
Sat Jan 12 15:03:00 EST 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 at 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 at 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 at 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;
+}
More information about the Freeswitch-svn
mailing list