#!/usr/bin/perl
#
#
# VoIP Report Generator - Extract Cisco VoIP call information from a 
# Postgresql database and generate various reports.
#
# Author:       Peter Nixon <codemonkey@peternixon.net>
# Date:         2002-10-07
# Summary:      Cisco,  VoIP, database, radius, report
# Copyright:    2002, Peter Nixon <codemonkey@peternixon.net>
# Copy Policy:  Free to copy and distribute provided all headers are left
#               intact and no charge is made for this program.  I would
#               appreciate copies of any modifications to the script.
# URL:          http://www.peternixon.net/code/
#


#Unbuffered output
$| = 1;

# Various what-not that we need to get things done.

# Include the Perl Modules we need
require "ctime.pl";
require POSIX;
require Getopt::Long;
require DBI;
use Date::Parse;
use Carp;
use Net::DNS::Resolver;

# Set up the basic variables
$progname = "Call Report";
$version = "0.4.2pre";

my $database    = "radius";
my $hostname    = "localhost";
my $port        = "5432";
my $user        = "postgres";
my $password    = "";
my $table = 'calls';

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900; $mon += 1;

my $csvfile = "dailysummary$year" . sprintf("%02d", $mon % 100)
	 . sprintf("%02d", $mday % 100);

my $search_date = "YESTERDAY"; my $timezone = "GMT+02"; 
my $totalmins;

# Add your Customer's NAS IPs and names here. This allows
# the report to group multiple NAS's owned by the same customer together.
my %ip_map = (
 '127.0.0.1' => 'localhost',
 '217.110.228.146' => 'Umit Unel', '217.69.130.11' => 'Some guy',
 '80.239.169.4' => 'Xplorium Paris', '80.239.169.40' => 'Xplorium Paris',
 '65.223.220.30' => 'Xplorium US', '80.86.166.196' => 'Umit Unel',
 '217.71.109.99' => 'Umit Unel', '217.110.228.147' => 'Umit Unel',
);

# Add your NAS IPs and names here.
my %nas_map = (
   '127.0.0.1' => 'localhost',
   '212.50.52.41' => 'ist-gw1', '212.50.52.42' => 'ist-gw2', 
   '212.50.52.12' => 'ist-gw3', '212.50.53.10' => 'ank-gw1',
   '212.50.53.11' => 'ank-gw2', '212.50.53.12' => 'ank-gw3',
   '212.50.53.200' => 'girne-gw1', '212.50.53.201' => 'girne-gw2',
   '212.50.53.202' => 'girne-gw3',
);

# Add your NAS IPs to here with the city they are in. This allows 
# the report to group multiple NAS's in the same location together.

my %nas_city_map = (
   '127.0.0.1' => 'local', '127.0.0.2' => 'local',
   '212.50.52.10' => 'Istanbul', '212.50.52.11' => 'Istanbul',
   '212.50.52.12' => 'Istanbul', '212.50.53.10' => 'Ankara',
   '212.50.53.11' => 'Ankara', '212.50.53.12' => 'Ankara',
   '212.50.53.200' => 'Girne', '212.50.53.201' => 'Girne',
   '212.50.53.202' => 'Girne',
);
my %areacode_map = (
   '911' => 'Emergency', '000' => 'Emergency',
   '392' => 'Kıbrıs',
   '312' => 'Ankara',
   '212' => 'Istanbul', '216' => 'Istanbul',
);

my %nas_call_totals = ();
my %ip_call_totals = ();
my %cust_city_call_totals = ();
my %cust_city_calltype_call_totals = ();
my %HoH = (); my %HoHoH = ();

my @hosts;

# Print usage information
sub print_usage_info {
    print "\n";
    $leader = "$progname $version Usage Information";
    $underbar = $leader;
    $underbar =~ s/./-/g;
    print "$leader\n$underbar\n";
    print "\n";
    print "  Syntax:   report.pl [ options ]\n";
    print "\n";
    print "    -h --help                        Show this usage information\n";
    print "    -x --debug                       Turn on debugging\n";
    print "    -v --version                     Show version and copyright\n";
    print "    -r --report [summary|tech|daily] Create a Summary Report\n";
    print "    -D --date <date>                 Date for the report (Default: Yesterday)\n";
    print "    -H --host --database <host>      Database Host(s) (Multiple)\n";
    #print "    -c --csv <filename>              Output a CSV file by this name\n";
    print "\n";
    exit(SUCCESS);
}

# Utility functions
# Get debugging state
sub debug_get() {
    return $debug;
}

sub debug_set($) {
    $debug = $_[0];
}

sub resolve_nasname($) {
	my $nasipaddress = $_[0];
	my $nasname = $nas_map{$nasipaddress};
            if(!defined $nasname){
		$nasname = &resolve($nasipaddress);
            	if(!defined $nasname){
                   $nasname = "Unknown";
	  	   if (&debug_get()) { carp "DEBUG: Unknown NAS Address $nasipaddress\n"; }
		}
	}
	return $nasname;
}

sub resolve_nas_city($) {
        my $nasipaddress = $_[0];
        my $nasname = $nas_city_map{$nasipaddress};
        if(!defined $nasname){
         $nasname = "Unknown";
        if (&debug_get()) { carp "DEBUG: Unknown City for NAS $nasipaddress\n"; }
        }
        return $nasname;
}

sub resolve_gwname($) {
	my $h323remoteaddress = $_[0];
	my $gwname = $ip_map{$h323remoteaddress};
        if(!defined $gwname){
		$gwname = &resolve($h323remoteaddress);
        	if(!defined $gwname){
                	$gwname = "Unknown";
                	if (&debug_get()) { carp "DEBUG: Unknown Customer Address $h323remoteaddress\n"; }
		}
        }
	return $gwname;
}
sub resolve_areacode($) {
        my $areacode = $_[0];
        my $areacode_name = $areacode_map{$areacode};
        if(!defined $areacode_name){
		if($areacode =~ /^5[0-9]+/) {
			$areacode_name = "Mobile";
		} else {
		         $areacode_name = "Other";
		}
        if (&debug_get()) { carp "DEBUG: Unknown City for Area Code $areacode\n"; }
        }
        return $areacode_name;
}

sub resolve_gw_cust($) {
        my $h323remoteaddress = $_[0];
        my $gwname = $ip_map{$h323remoteaddress};
        if(!defined $gwname){
           if (&debug_get()) { carp "DEBUG: Unknown Customer Address $h323remoteaddress\n"; }
           $gwname = "Unknown";
        }
        return $gwname;
}


sub resolve($) {
	my $address = $_[0];
	
#	my $dnsresult = $res->query("$address");
#	return $dnsresult;
}

sub summary_report_ip {
        print "Gateway Breakout by IP\n";
        # print the whole thing
         foreach $h323remoteaddress ( keys %HoH ) {
             my $gwname = &resolve_gwname($h323remoteaddress);
             print "$h323remoteaddress ($gwname):\n";
             for $nasipaddress ( keys %{ $HoH{$h323remoteaddress} } ) {
                 my $nasname = &resolve_nasname($nasipaddress);
                 print "\t$nasipaddress ($nasname) = $HoH{$h323remoteaddress}{$nasipaddress} Min\n";
             }
             print "\n";
         }
}

sub summary_report($) {
	my $report_type = $_[0];
	
	# Loop through the defined Database hosts
        foreach $hostname (@hosts) {
		if (&debug_get()) { print "DEBUG: Connecting to Database Server: $hostname\n";}
		&summary($hostname);
	}

	&print_program_header;

	# Output the appropriate summary
	if ($report_type eq "summary") {
		&summary_report_cust_calltype;
	} elsif ($report_type eq "tech") {
		&summary_report_cust_ac;
	} elsif ($report_type eq "total") {
        } else {
		print "Houston, we have a problem. \n";
	}
	#&summary_report_ip;
	print "Total Minutes: $totalmins \n\n";

}

sub summary_report_cust {
        print "Customer calls per city\n";
        # print the whole thing
         foreach $custname ( keys %cust_city_call_totals ) {
             print "$custname:\n";
             for $nascity ( keys %{ $cust_city_call_totals{$custname} } ) {
                 print "\t$nascity = $cust_city_call_totals{$custname}{$nascity} Min\n";
             }
	 print "\n";
         }


#	&output_to_csv;
}

sub summary_report_cust_ac {
        print "Customer calls per city and Area Code\n";
        # print the whole thing
         foreach $h323remoteaddress ( keys %HoHoH ) {
             my $gwname = &resolve_gwname($h323remoteaddress);
             print "$h323remoteaddress ($gwname):\n";
             for $nasipaddress ( keys %{ $HoHoH{$h323remoteaddress} } ) {
                 my $nasname = &resolve_nasname($nasipaddress);
                 print "\t$nasipaddress ($nasname)\n";
	             for $areacode ( keys %{ $HoHoH{$h323remoteaddress}{$nasipaddress} } ) {
	                 my $mins = sprintf("%1.2f", $HoHoH{$h323remoteaddress}{$nasipaddress}{$areacode} );
	                 print "\t\tArea Code: $areacode = $mins Min\n";
                     }
             }
             print "\n";
         }

}

sub summary_report_cust_calltype {
        print "Customer calls per city and Call Type\n";
        # print the whole thing
         foreach $custname ( keys %cust_city_calltype_call_totals ) {
             print "$custname:\n";
             for $nascity ( keys %{ $cust_city_calltype_call_totals{$custname} } ) {
                 print "\t$nascity\n";
	             for $areacode_name ( keys %{ $cust_city_calltype_call_totals{$custname}{$nascity} } ) {
			my $mins = sprintf("%1.2f", $cust_city_calltype_call_totals{$custname}{$nascity}{$areacode_name} );
			print "\t\tCall Destination: $areacode_name = $mins Min\n";
		}
             }
         print "\n";
         }

}

sub summary($) {

        my %results; my $nasname; my $gwname;
	$hostname = $_[0];
        $dbh = DBI->connect("DBI:Pg:dbname=$database;host=$hostname", "$user", "$password")
                or die "Database Error: \"" . DBI->errstr . "\"";

        my $sth = $dbh->prepare("SELECT h323remoteaddress, nasipaddress, 
	substring(CalledStationId from 2 for 3) as areacode, sum(acctsessiontime)/60 AS mins FROM calls
	WHERE h323connecttime BETWEEN DATE'$search_date $timezone' AND (DATE'$search_date $timezone' + INTERVAL'1 day') 
	group by h323remoteaddress, nasipaddress, substring(CalledStationId from 2 for 3)")
        or die "Couldn't prepare statement: " . $dbh->errstr;
	#WHERE h323connecttime BETWEEN DATE'2002-10-01' AND (DATE'today') 


        $sth->execute()             # Execute the query
           or die "Couldn't execute statement: " . $sth->errstr;

        my ($h323remoteaddress, $nasipaddress, $mins, $areacode);
        $sth->bind_columns(\$h323remoteaddress, \$nasipaddress, \$areacode, \$mins);


        while ($sth->fetch()) {
		# Count Total Minutes
		$totalmins += $mins;

		# Resolve a customer address to a name
		my $custname = &resolve_gw_cust($h323remoteaddress);
		# Resolve a NAS IP to a City
		my $nascity = &resolve_nas_city($nasipaddress); 
		# Resolve an Area Code to something useful
		my $areacode_name = &resolve_areacode($areacode); 

		# Record minutes per Customer and City
		$cust_city_call_totals{$custname}{$nascity} += $mins;

		# Record minutes per GW and NAS
		$HoH{$h323remoteaddress}{$nasipaddress} += $mins;

		# Record minutes per GW, NAS and Area Code
		$HoHoH{$h323remoteaddress}{$nasipaddress}{$areacode} += $mins;
		
		# Record minutes per GW, NAS and Area Code
		$cust_city_calltype_call_totals{$custname}{$nascity}{$areacode_name} += $mins;

		$nas_call_totals{$nasipaddress} += $mins;
		$ip_call_totals{$h323remoteaddress} += $mins;
                if (&debug_get()) { print "DEBUG: $gwname ($h323remoteaddress)\t$nasname ($nasipaddress)\t$mins\n"; }
        }


        $sth->finish;

        # Now, disconnect from the database
        $dbh->disconnect
            or warn "Disconnection failed: $DBI::errstr\n";
}


sub daily_report {

        my @fields = qw( h323connecttime acctsessiontime calledstationid h323remoteaddress );
        my %results;
        my $fields = join(', ', @fields);

        $dbh = DBI->connect("DBI:Pg:dbname=$database;host=$hostname", "$user", "$password")
                or die "Couldn't connect to database: " . DBI->errstr;

        my $sth = $dbh->prepare("SELECT $fields FROM $table WHERE h323connecttime BETWEEN DATE'YESTERDAY' AND DATE'TODAY'")
                or die "Couldn't prepare statement: " . $dbh->errstr;

        $sth->execute()             # Execute the query
           or die "Couldn't execute statement: " . $sth->errstr;

        @results{@fields} = ();
        $sth->bind_columns(map { \$results{$_} } @fields);

        print "h323connecttime\t\tacctsessiontime\tcalledstationid\t\th323remoteaddress\n";

        while ($sth->fetch()) {
                print "$results{h323connecttime}\t$results{acctsessiontime}\t$results{calledstationid}\t\t$results{h323remoteaddress}\n";
        }


        $sth->finish;
        print "\n";

        ### Now, disconnect from the database
        $dbh->disconnect
            or warn "Disconnection failed: $DBI::errstr\n";
}

sub output_to_csv {
	# If the file exists already, delete it.
	unlink $csvfile;

        my @fields; my @fieldvalues;
	
	push @fields, "Date";
	push @fieldvalues, "$year" . "-" . sprintf("%02d", $mon % 100)
         . "-" . sprintf("%02d", $mday % 100);
        foreach $custname ( keys %cust_city_call_totals ) {
             for $nascity ( keys %{ $cust_city_call_totals{$custname} } ) {
		push @fields, "$custname-$nascity";
		push @fieldvalues, "$cust_city_call_totals{$custname}{$nascity}";
             }
             print "\n";
         }

        my $fields = join(', ', @fields);
	print "My Fields: $fields\n";

	my $fieldvalues = join('", "', @fieldvalues);
	print "My Field Values: $fieldvalues\n";

	my $dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\;})
                or die "Couldn't open CSV directory: " . DBI->errstr;

	my $sth1 = $dbh->prepare("CREATE TABLE $csvfile (\"$fields)")
		or carp "Cannot prepare: " . $dbh->errstr();
	$sth1->execute() or die "Cannot execute: " . $sth->errstr();
	$sth1->finish();

                my $gwname = &resolve_gwname($h323remoteaddress);
        	my $sth2 = $dbh->prepare("INSERT into $csvfile ($fields) values($fieldvalues)");
	        $sth2->execute();
	        $sth2->finish();
		my $returned_rows = $sth2->rows;
	        if (&debug_get()) {print " $returned_rows record(s) added to file: $csvfile\n"; }
	$dbh->disconnect();

}

sub print_program_header {
    print "\n";
    $leader = "$progname for $search_date $timezone";
    $underbar = $leader;
    $underbar =~ s/./-/g;
    print "$leader\n$underbar\n";
    print "\n";
}

sub main
{
	&print_usage_info() if (!scalar(@ARGV));
    umask 022;

    # See the Getopt::Long man page for details on the syntax of this line
    @valid_opts = ("h|help", "v|version", "x|debug", "s|summary", "d|daily", "D|date=s", "H|host|database=s@", "c|csv=s", "r|report=s");
    Getopt::Long::Configure("no_getopt_compat", "bundling", "no_ignore_case");
    Getopt::Long::GetOptions(@valid_opts);

    # Post-parse the options stuff
    select STDOUT; $| = 1;
    if ($opt_v) {
        print "\n";
        print "$progname $version by Peter Nixon <codemonkey\@peternixon.net>\n";
        print "Copyright (c) 2002, Peter Nixon\n";
        print "\n";
        return SUCCESS;
    } elsif ($opt_h) {
        &print_usage_info();   # Never returns
    }
    &debug_set($opt_x);


    if (scalar(@opt_H)) {
        @hosts = @opt_H;
    } else {
	push @hosts, "$hostname";
    }

    if ($opt_D) {
        $search_date = $opt_D;
    }


    if ($opt_c) {
	$csvfile = $opt_c;
	if (&debug_get()) { print "Setting CSV ouptut file to: $csvfile\n"; }
    }

    if ($opt_r) {
        my $report_type = $opt_r;
        if (&debug_get()) { print "Report Type Specified: $report_type\n"; }
	if (($report_type eq "summary") or ($report_type eq "tech") or ($report_type eq "total")) {
		&summary_report($report_type);
	} elsif ($report_type eq "daily") {
	&daily_report;
	} else {
        print "You didn't specify a valid report type.\n";
	exit(FAILURE);
	}
    } else {
        print "You didn't specify a report type.\n";
	exit(FAILURE);
    }

}

exit &main();
