#!/usr/bin/perl
#
# db2dbcopy - Extract information Radius accounting info from one Postgresql Database
# and insert/compare/update a 2nd Postgresql database.
#
# Author:       Peter Nixon <codemonkey@peternixon.net>
# Date:         12 December 2002
# Summary:      Radius,  User's activity,  Port usage,
# 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/
#
#
# Supported:    PostgreSQL (tested on version 7.2 and 7.3)
#
# Comments on this extremly welcome
#

# Set up the basic variables
$progname = "db2dbcopy";
$version = 0.2;


#Unbuffered output
$| = 1;

# Various what-not that we need to get things done.
my $database    = "radius";
my $port        = "5432";
my $user        = "postgres";
my $password    = "";
my $table = 'StopVoIP';
my $masterhostname    = "localhost";
my $dbuniquefield = "radacctid";
my $datefield = "h323setuptime";
my @datafields = qw( AcctSessionId AcctUniqueId UserName NASIPAddress NASPortType AcctStartTime AcctStopTime AcctSessionTime AcctInputOctets AcctOutputOctets CalledStationId CallingStationId ServiceType AcctStartDelay AcctStopDelay AcctStatusType H323GWID h323CallOrigin h323CallType h323SetupTime h323ConnectTime h323DisconnectTime h323DisconnectCause H323RemoteAddress H323VoiceQuality h323ConfID RadiusServerName );
my @matchfields = qw( AcctUniqueId UserName NASIPAddress AcctSessionTime CalledStationId CallingStationId AcctStatusType H323GWID h323CallType h323SetupTime H323RemoteAddress h323ConfID );


# Include some Perl Modules we use to make things easier
require "ctime.pl";
require POSIX;
require Getopt::Long;
require DBI;
use Date::Parse;
use Carp;

# Set up some query defaults
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900; $mon += 1;
my $search_date = "YESTERDAY"; my $timezone = "GMT+02"; 
my $totalmins; my @hosts; 
my @fields = @datafields; push @fields, $dbuniquefield;	
my $fields = join(', ', @fields);
my $datafields = join(', ', @datafields);
my $masterdbh; my $questionmarks = ""; my $resultvalues = ""; 
my $maxfieldloop = ($#fields + 1); my $maxmatchloop = ($#matchfields + 1); my $maxdataloop = ($#datafields + 1);
my $wherematchvalue = ""; my $questionmatchmarks = "";
my @resultvalues; my %results;

# 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 "    -M --masterhost                  Master Database Host\n";
    print "    -R --remotehost <host>           Remote Database Host(s) (Multiple)\n";
    print "    -D --date <date>                 Date to copy (Default: Yesterday)\n";
    print "\n";
    exit(SUCCESS);
}

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

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

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 dbunzip {
        if (&debug_get()) { print "DEBUG: Connecting to the master database: $masterhostname\n"; }
        $masterdbh = DBI->connect("DBI:Pg:dbname=$database;host=$masterhostname", "$user", "$password")
                or die "Database Error: \"" . DBI->errstr . "\"";
}
sub dbzip {
        if (&debug_get()) { print "DEBUG: Disconnecting from the master database\n"; }
        $masterdbh->disconnect
            or warn "Disconnection failed: $DBI::errstr\n";
}

sub bleh {
	my $loopno = 0; $wherematchvalue = ""; $questionmatchmarks = ""; my $j;
	foreach $j (@matchfields) {
		$wherematchvalue.="$j"; $loopno++;
		if ($loopno == $maxmatchloop) { $questionmatchmarks.="?"; $wherematchvalue.=' = ? '; }
		else { $questionmatchmarks.="?, "; $wherematchvalue.=' = ? AND '; }
	}
	my $loopno = 0; $wherevalue = ""; $questionmarks = ""; my $k;
	foreach $k (@fields) {
		$wherevalue.="$k"; $loopno++;
		if ($loopno == $maxfieldloop) { $questionmarks.="?"; $wherevalue.=' = ? '; }
		else { $questionmarks.="?, "; $wherevalue.=' = ? AND '; }
	}
        my $loopno = 0; $wheredatavalue = ""; $questiondatamarks = ""; my $k;
        foreach $k (@datafields) {
                $wheredatavalue.="$k"; $loopno++;
                if ($loopno == $maxdataloop) { $questiondatamarks.="?"; $wheredatavalue.=' = ? '; }
                else { $questiondatamarks.="?, "; $wheredatavalue.=' = ? AND '; }
        }
        if (&debug_get()) { print "DEBUG: WHERE - $wherematchvalue\n"; }
        if (&debug_get()) { print "DEBUG: QUESTION - $questionmatchmarks\n"; }
}

### This sub checks if the record exists already
sub dbfondle {
	my $loopno = 0; @resultmatchvalues = ();
	foreach $j (@matchfields) {
		push @resultmatchvalues, $results{$j};	
		$loopno++;
	}
        if (&debug_get()) { print "DEBUG: RESULT - @resultmatchvalues\n"; }

	my $fondlesth = $masterdbh->prepare("SELECT $datafields FROM $table WHERE $wherematchvalue ")
                or die "Couldn't prepare statement: " . $masterdbh->errstr;
        $fondlesth->execute( @resultmatchvalues )             # Execute the query
           or die "Couldn't execute statement: " . $fondlesth->errstr;

        my $returned_rows = $fondlesth->rows;
        if (&debug_get()) { print "DEBUG: $returned_rows rows found matching this record.\n"; }
          if ($fondlesth->rows == 0) {


                if (&debug_get()) { print "DEBUG: Adding - $results{h323SetupTime}\t$results{AcctSessionTime}\t$results{CalledStationId}\t\t$results{h323RemoteAddress}\n"; }
		else { print "Adding: `$results{h323ConfID}'\n"; }
                &dbswallow;
          } else {
                if (&debug_get()) { print "DEBUG: Exists - $results{h323SetupTime}\t$results{AcctSessionTime}\t$results{CalledStationId}\t\t$results{h323RemoteAddress}\n"; }
		else { print "Exists: $results{h323ConfID}\n"; }
		# We should Delete it from the remote DB here also
		#&dbspit;
        }

        $fondlesth->finish;
}

### This sub deletes the record from the Remote DB
sub dbspit {
        if (&debug_get()) { print "DEBUG: I am about to delete: $results{$dbuniquefield} from Remote BD\n"; }
        my $spitsth = $dbh->prepare("DELETE FROM $table WHERE $dbuniquefield = $results{$dbuniquefield} ")
                or die "Couldn't prepare statement: " . $dbh->errstr;
	$spitsth->execute() or die "Couldn't execute statement: " . $spitsth->errstr;
}

### This sub inserts the record into the Master DB
sub dbswallow {
	my $loopno = 0; @resultvalues = ();
	foreach $j (@datafields) {
		push @resultvalues, $results{$j};	
		$loopno++;
	}

#        if (&debug_get()) { print "DEBUG: FIELDS - $fields\n"; }
#        if (&debug_get()) { print "DEBUG: QUESTION - $questionmarks\n"; }
#        if (&debug_get()) { print "DEBUG: RESULT - @resultvalues\n"; }

	my $swallowsth = $masterdbh->prepare("INSERT into $table ( $datafields ) values( $questiondatamarks )")
                or die "Couldn't prepare statement: " . $masterdbh->errstr;

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

           my $returned_rows = $swallowsth->rows;
        if (&debug_get()) { print "DEBUG: $returned_rows record(s) added\n"; }
         $swallowsth->finish();
	
	# I guess the record was added successfully, so we may as well nuke it in the remote DB now.
	&dbspit;
}

### This sub sucks records from the Remote DB
sub dbsuck($) {
	@results; my $hostname = $_[0];
        my $nasname; my $gwname;
#        if (&debug_get()) { print "DEBUG: Select Fields: $fields\n"; }

        ### Connect to the remote database
        $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")
                or die "Couldn't prepare statement: " . $dbh->errstr;
#	WHERE $datefield BETWEEN DATE'$search_date $timezone' AND (DATE'$search_date $timezone' + INTERVAL'3 months')")

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

	@results{@fields} = ();
        $sth->bind_columns(map { \$results{$_} } @fields);
	
	my $i = 0;
        while ($sth->fetch()) {
		$i++;
        if (&debug_get()) { print "DEBUG: $i) $results{RadAcctId} $results{h323SetupTime}\t$results{AcctSessionTime}\t$results{CalledStationId}\t\t$results{h323RemoteAddress}\n\n"; }
	else { print "Host: $hostname DB $database Record #$i) "; }
	&dbfondle;
        }

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

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

sub dbjiggy {
        &print_program_header;
	# Connect to Master DB
	&dbunzip;
	# Setup vars
	&bleh;
        # Loop through the remote Database hosts
        foreach $hostname (@hosts) {
                if (&debug_get()) { print "DEBUG: Connecting to Database Server: $hostname\n";}
                &dbsuck($hostname);
        }
	# Disconnect from Master DB
	&dbzip;

}

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", "M|masterhost=s", "D|date=s", "R|remotehost=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 ($opt_D) {
        $search_date = $opt_D;
    }


    if ($opt_M) {
	$masterhostname = $opt_M;
	if (&debug_get()) { print "Setting Master Database Server to: $masterhostname\n"; }
    }

    if (scalar(@opt_R)) {
        @hosts = @opt_R;
	&dbjiggy;
    } else {
        print "You didn't specify a remote database host.\n";
	exit(FAILURE);
    }

}

exit &main();
