#!/usr/bin/perl
#
# $Id$
#
# TrafficCounter by IBCL
#
# Copyleft IBCL 2010
#
# Author: Christoph Lechleitner <lech@ibcl.at>
#


use strict;
use Date::Format;
use DBI;

# centos packages:
# yum install perl-TimeDate 

require("/usr/lib/trafficcounter/tc-functions.pl");

$main::helpMessage='
 tc-file2db reads data tc-collected previously and insert them in a database

 Syntax:
  tc-file2db [options]  Copies traffic data to the database
  tc-file2db --help     Prints this help text and exits.
  tc-file2db --version  Prints the version info and exits.

 Common tc-* options are:
  -c configfile    Read config from configfile, ignore /etc/trafficcounter.conf
  -d               Debug mode, giving even debug infos.
  -f dataformat    Use given rather than configured dataformat. For testing.
  -i interface     Process interface, not the first of the configured list.
  -q               Quiet mode, supressing informative messages.
  -v               Verbose mode, giving more info, can be repeated.
 
 tc-report specific options are:
  -H hostname      Data are from specified hostname, not this host.
  -I infile        Read infile rather than /var/lib/trafficcounter/*.traffic

 More report options will be added as experience will suggest useful.

 More information on the values for interfaces and dataformat can be found in 
 the config file template /etc/trafficcounter.conf.template.

';

init("H:I:");

#print "tc-collect not implemented yet ;-((\n";

my $dataFile = "";
my $dataHostname = `hostname -s`;
my $showAvg = 0;
if ( $main::opts{"H"} ) {
  $dataHostname = $main::opts{"H"};
}
my $dataFile = $main::opts{"I"};

my $interface = "";
if (scalar (@main::interfaceList) gt 0) {
  if (scalar (@main::interfaceList) gt 1) {
    if ($main::verbosity gt 0) {
      print "WARNING: tc-file2db only processes the first interface specified.\n";
    } 
  }
  $interface = $main::interfaceList[0];
} 
if ($interface eq "") { die "FATAL: No interface specified"; }

if ($dataFile eq "") {
  $dataFile = "/var/lib/trafficcounter/".$interface.".traffic";
  ($main::verbosity gt 1) && print "Using default filename: ".$dataFile."\n";
}
if (not -e $dataFile) { die "FATAL: No such file: ".$dataFile; }

# database connection

my $dbsource = "DBI:mysql:".$main::params{"mysql_db"}.":".$main::params{"mysql_host"};
my $dbh = DBI->connect($dbsource,$main::params{"mysql_user"},$main::params{"mysql_password"}) or die "Cannot connect to DB ".$main::params{"mysql_db"}."\n";

my $insertSql = "INSERT INTO TRAFFIC ";
$insertSql .= "( HOSTNAME, INTERFACE, PERIOD_TO_UTCSECS, PERIOD_FROM_UTCSECS, PERIOD_TO_PRETTY, PERIOD_FROM_PRETTY, BYTES_OUT, BYTES_IN, BYTES_OUT_PER_SEC, BYTES_IN_PER_SEC ) VALUES ";
$insertSql .= "( ?,        ?,         ?,                 ?,                   ?,                ?,                  ?,         ?,        ?,                 ?                )";
my $insertStmt = $dbh->prepare($insertSql);
# $insertStmt->execute($dataHostname, $interface, $periodToSecs, $periodFromSecs, $periodToPretty, $periodFromPretty, $bytesOut, $bytesIn);

my $deleteSql = "DELETE FROM TRAFFIC WHERE HOSTNAME = ? AND INTERFACE = ?";
my $deleteStmt = $dbh->prepare($deleteSql);
if ($main::verbosity gt 0) {
  print "Deleting eventual old data for ".$dataHostname.".".$interface." at ".time2str("%Y-%m-%d %H:%M:%S",time())."\n";
}
$deleteStmt->execute($dataHostname, $interface) || die "FATAL: Database error deleting old data for ".$dataHostname.".".$interface;

$dbh->begin_work() || die "FATAL: Unable to start database transaction";

my $now = time();

open (DATA, "<".$dataFile) || die "FATAL: Cannot read ".$dataFile;
my $lastPeriodUtcSeconds = 0;
my $firstLine = <DATA>; # eats informative column names line
my $lineNr = 0;
my $maxBatchSize = 1500;
my $batchSize = 0;
my $workStartedWhen = time();
if ($main::verbosity gt 0) {
  print "Starting to copy data at ".time2str("%Y-%m-%d %H:%M:%S",$workStartedWhen)."\n";
}
while (my $curLine = <DATA>) {
  $lineNr++;
  #if ($main::verbosity gt 2) { print "Line: '".$curLine."'\n"; }
  chomp $curLine;
  my ($curUtcSeconds, $curRxBytes, $curTxBytes, $curRxDiff, $curTxDiff) = split /\t/, $curLine;
  if ($lastPeriodUtcSeconds == $curUtcSeconds) {
    print "Skipping line with repeated timestamp ".$curUtcSeconds."\n";
    next;
  }
  my $periodLength = $curUtcSeconds - $lastPeriodUtcSeconds;
  my $curRxBytesPerSec = int($curRxDiff / $periodLength);
  my $curTxBytesPerSec = int($curTxDiff / $periodLength);
  my $curTotalDiff = $curRxDiff + $curTxDiff;
  # shall we count $curUtcSeconds or the halftime of that period?
  my $periodHalfTime = $curUtcSeconds;
  if ($lastPeriodUtcSeconds gt 0) 
  { $periodHalfTime = ($curUtcSeconds + $lastPeriodUtcSeconds) / 2; }
  #if ($main::verbosity gt 2) {
  #  print "Matching periods based on period half time ".time2str("%Y-%m-%d %H:%M:%S",$periodHalfTime)."\n";
  #}
  if ($lastPeriodUtcSeconds gt 0) {
    my $periodToPretty = time2str("%Y-%m-%d %H:%M:%S",$curUtcSeconds);
    my $periodFromPretty = time2str("%Y-%m-%d %H:%M:%S",$lastPeriodUtcSeconds);
    #print "Inserting values $dataHostname, $interface, $curUtcSeconds, $lastPeriodUtcSeconds, $periodToPretty, $periodFromPretty, $curTxDiff, $curRxDiff\n";
    $insertStmt->execute($dataHostname, $interface, $curUtcSeconds, $lastPeriodUtcSeconds, $periodToPretty, $periodFromPretty, $curTxDiff, $curRxDiff, $curTxBytesPerSec, $curRxBytesPerSec) || die "FATAL: Database error inserting data.";
    $batchSize++;
    if ($batchSize >= $maxBatchSize) {
      if ($main::verbosity gt 1) {
        print "After line ".$lineNr." commiting ".$batchSize." rows.\n";
      }
      $batchSize = 0;
      $dbh->commit() || die "FATAL: Unable to commit intermediate database transaction";
      $dbh->begin_work() || die "FATAL: Unable to start next database transaction";
    } 
  }
  $lastPeriodUtcSeconds = $curUtcSeconds;
}
close DATA;
$dbh->commit() || die "FATAL: Unable to commit database transaction";
$dbh->disconnect();
my $workFinishedWhen = time();
if ($main::verbosity gt 0) {
  print "Finished copying data at ".time2str("%Y-%m-%d %H:%M:%S",$workFinishedWhen)."\n";
}

if ($main::verbosity gt 1) {
  print "Inserted data from ".$lineNr." lines.";
}

exit 0;


