#!/usr/bin/perl -w
# $Id: ipfwcount,v 1.4 2003/04/30 23:15:37 candy Exp $
use strict;
use POSIX;
use Time::Local;
use Getopt::Std;
use Pg; # ports/database/p5-Pg

my $pg_host = "localhost";
my $pg_user = "pgsql";
my $pg_pass = "";
my $pg_db = "pgsql";
my $output = 0; # 0=counter, 1=add, 2=percent;

sub comp { $a <=> $b; };

sub nz($;$) {
	return defined($_[0]) ? $_[0] : (defined($_[1]) ? $_[1] : "");
}

sub autoflush($$) {
	my($so, $flag) = @_;
	select((select($so), $| = $flag)[0]);
}

# gmtoff : 32400 = JST
sub get_gmtoff() {
	my $t0 = timegm(0, 0, 0, 1, 0, 100); # 2000-01-01 00:00:00 +00
	my $t1 = mktime(0, 0, 0, 1, 0, 100); # 2000-01-01 00:00:00 (LOCAL)
	my $gmtoff = $t0 - $t1;
	return $gmtoff;
}

sub parse_date($) {
	my($str) = @_;
	my($Y, $m, $d, $H, $M, $S);
	my $t = 0;
	if ($str =~ /(\d+)-(\d+)-(\d+)( +(\d+)(:(\d+)(:(\d+))?)?)?/) {
		($Y, $m, $d, $H, $M, $S) = ($1, $2, $3, nz($5, 0), nz($7, 0), nz($9, 0));
		$t = mktime($S, $M, $H, $d, $m - 1, $Y - 1900);
	}
	return $t;
}



sub errmsg($) {
	my($e) = @_;
	return "PGRES_EMPTY_QUERY" if ($e eq PGRES_EMPTY_QUERY);
	return "PGRES_COMMAND_OK" if ($e eq PGRES_COMMAND_OK);
	return "PGRES_TUPLES_OK" if ($e eq PGRES_TUPLES_OK);
	return "PGRES_COPY_OUT" if ($e eq PGRES_COPY_OUT);
	return "PGRES_COPY_IN" if ($e eq PGRES_COPY_IN);
	return "PGRES_BAD_RESPONSE" if ($e eq PGRES_BAD_RESPONSE);
	return "PGRES_NONFATAL_ERROR" if ($e eq PGRES_NONFATAL_ERROR);
	return "PGRES_FATAL_ERROR" if ($e eq PGRES_FATAL_ERROR);
	return sprintf("PGRES_UNKNOWN(%d)", $e);
}

sub query1($) {
	my($sql) = @_;
	my $conn_arg = "host=$pg_host dbname=$pg_db user=$pg_user password=$pg_pass";
	my $conn = Pg::connectdb($conn_arg);
	my $result;
	if($conn->status == PGRES_CONNECTION_OK) {
		printf(STDERR "Q:%s\n", $sql);
		$result = $conn->exec($sql);
		if ($result->resultStatus == PGRES_TUPLES_OK) {
			my $ntuples = $result->ntuples;
			my $nfields = $result->nfields;
			#printf(STDERR "got (%d x %d)\n", $ntuples, $nfields);
		}
		else {
			printf(STDERR "Pg::exec: %s\n", $sql);
			printf(STDERR "Pg::exec: %s\n", errmsg($result->resultStatus));
			printf(STDERR "Pg::exec: %s\n", $conn->errorMessage);
		}
	}
	else {
		printf(STDERR "Pg::connectdb: %s\n", $conn->errorMessage);
	}
	return $result;
}


sub count_dport($$) {
	my($t0, $t1) = @_;
	my $st0 = strftime("%Y-%m-%d %H:%M:%S", localtime($t0));
	my $st1 = strftime("%Y-%m-%d %H:%M:%S", localtime($t1));
	my $result = query1("SELECT dport, count(dport) FROM ipfw WHERE date >= '$st0' AND date < '$st1' GROUP BY dport HAVING count(dport) > 1;");
	my %dd;
	if ($result->resultStatus == PGRES_TUPLES_OK) {
		my $ntuples = $result->ntuples;
		my $nfields = $result->nfields;
		my ($i, $j);
		for ($i = 0; $i < $ntuples; $i++) {
			my $k = $result->getvalue($i, 0);
			my $v = $result->getvalue($i, 1);
			$dd{$k} = $v;
		}
	}
	return %dd;
}

sub nain($$$) {
	my($t0, $t1, $tstep) = @_;
	my $Y = (localtime())[5] + 1900;
	my $lno = 1;
	autoflush(\*STDOUT, 1);
	my @dp;
	my $i = 0;
	my($t, $k);
	for ($i = 0, $t = $t0; $t < $t0 + $t1; $i++, $t += $tstep) {
		my %dp =  count_dport($t, $t + $tstep);
		printf("%s", strftime("%Y-%m-%d-%H:%M:%S", localtime($t + $tstep)));
		foreach $k (sort comp keys %dp) {
			my $x = nz($dp{$k}, 0);
			printf(" %d %d", $k, $x);
		}
		printf("\n");
	}
	return 0;
}

my $usage =
	"usage: %s [-d database][-h host][-U user][-W password][-l len(sec)][-i interval(sec)][-s start_date]\n"
	. "\t-c print only. (for COPY)\n"
	;

use vars qw($opt_d);
use vars qw($opt_h);
use vars qw($opt_i);
use vars qw($opt_l);
use vars qw($opt_s);
use vars qw($opt_t);
use vars qw($opt_U);
use vars qw($opt_V);
use vars qw($opt_W);

sub printt($) {
	printf("%s %d\n", strftime("%Y-%m-%d %H:%M:%S", localtime($_[0])), $_[0]);
}

sub floor_localtime($$) {
	my($t, $tstep) = @_;
	my $gmtoff = get_gmtoff();
	return int(($t + $gmtoff) / $tstep) * $tstep - $gmtoff;
}

sub main() {
	my($ex);
	$ex = 1;
	getopts('d:h:i:l:s:t:U:VW:');
	if (defined($opt_V)) {
		printf(STDERR $usage, $0);
	}
	else {
		my $now = time();
		my $t0 = 0;
		my $t1 = 0;
		my $tstep = 3600;
		defined($opt_d) && ($pg_db = $opt_d);
		defined($opt_h) && ($pg_host = $opt_h);
		defined($opt_s) && ($t0 = parse_date($opt_s));
		defined($opt_l) && ($t1 = $opt_l);
		defined($opt_i) && ($tstep = $opt_i);
		defined($opt_W) && ($pg_pass = $opt_W);
		defined($opt_U) && ($pg_user = $opt_U);
		if (nz($opt_t) eq "0" || nz($opt_t) eq "1" || nz($opt_t) eq "2") {
			$output = $opt_t + 0;
		}
		if ($t0 == 0) {
			if ($t1 == 0) {
				$t1 = 3600;
			}
			$t0 = floor_localtime($now - $t1, $tstep);
		}
		else {
			if ($t1 == 0) {
				$t1 = $now - $t0;
			}
			if ($t0 + $t1 >= $now) {
				$t1 = floor_localtime($now, $tstep) - $t0;
			}
		}
		nain($t0, $t1, $tstep);
		$ex = 0;
	}
	return $ex;
}

exit main();