#!/usr/local/bin/python

import re
import datetime
import psycopg2
import codecs, sys
from random import *
import string
from time import sleep
import time
import psycopg2, psycopg2.extensions, psycopg2.errorcodes
import ipaddr
import socket

filename = sys.argv[1]

conn = psycopg2.connect("host='XXXXXXXX' dbname='XXXXXXX' user='XXXXXXXXXXX' password='XXXXXXXXXXX'")
trf=dict()
ins = open( filename , "r" )
for line in ins:
         if ( re.match('([0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3})\s+([0-9]+)\s+([0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3})\s+([0-9]+)\s+([0-9]+)\s+([0-9]+)\s+([0-9]+)\s+([0-9]+)',line) ):
                s = re.match('([0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3})\s+([0-9]+)\s+([0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3})\s+([0-9]+)\s+([0-9]+)\s+([0-9]+)\s+([0-9]+)\s+([0-9]+)',line)
                date = datetime.datetime.fromtimestamp(float(s.group(8))).strftime("%Y-%m-%d")
                src_ip = s.group(1)
                #srp_pr = s.group(2)
                dst_ip = s.group(3)
                #dst_pr = s.group(4)
                #proto  = s.group(5)
                #packet = s.group(6)
                byte   = float(s.group(7))

                if ( ipaddr.IPAddress(src_ip) in ipaddr.IPNetwork('10.0.0.0/8') or ipaddr.IPAddress(src_ip) in ipaddr.IPNetwork('172.16.0.0/16')):

                        if trf.has_key(date) != True:
                                trf[date] = dict()

                        if trf[date].has_key(src_ip) != True:
                                trf[date][src_ip] = dict()

                        if trf[date][src_ip].has_key("out"):
                                trf[date][src_ip]["out"] = trf[date][src_ip]["out"] + byte
                        else:
                                trf[date][src_ip]["out"] =  byte

                elif ( ipaddr.IPAddress(dst_ip) in ipaddr.IPNetwork('10.0.0.0/8') or ipaddr.IPAddress(dst_ip) in ipaddr.IPNetwork('172.16.0.0/16')):
                        if trf.has_key(date) != True:
                                trf[date] = dict()

                        if trf[date].has_key(dst_ip) != True:
                                trf[date][dst_ip] = dict()

                        if trf[date][dst_ip].has_key("in"):
                                trf[date][dst_ip]["in"] = trf[date][dst_ip]["in"] + byte
                        else:
                                trf[date][dst_ip]["in"] =  byte


#print trf
#print trf["10.0.8.41"]["in"]
#print type(byte)
#####################
# Analize OUT traffic
#####################

#####################
# Analize IN traffic
#####################
cur = conn.cursor()

for date in trf:
 for ipaddr in trf[date]:
        if trf[date][ipaddr].has_key("out"):
                out = trf[date][ipaddr]["out"]
        else:
                out = 0

        if trf[date][ipaddr].has_key("in"):
                inn = trf[date][ipaddr]["in"]
        else:
                inn = 0

        lock_tries = 1
        while lock_tries < 100000:
                try:
                   cur.execute("LOCK TABLE traffic_day_traff  IN ACCESS EXCLUSIVE MODE NOWAIT;")
                   break
                except Exception, e:
                    if e.pgcode == psycopg2.errorcodes.LOCK_NOT_AVAILABLE:
                         conn.rollback()
                         lock_tries += 1
                         time.sleep(3)
                    else:
                         raise e
        else:
                sys.exit("unable to lock table reports")

        cur.execute("SELECT * FROM traffic_day_traff WHERE ip='%s' and date='%s';" % (ipaddr, date))
        if cur.rowcount > 0:
                rows = cur.fetchall()
                for row in rows:
                        #print ipaddr, row
                        #row = cur.fetchone()
                        #if cur.fetchone()[4] != None :
                        #print ipaddr,cur.fetchone()[4]
                        b_in=row[3] + inn
                        b_out = row[4] + out
                        cur.execute("UPDATE traffic_day_traff SET  byte_in='%s', byte_out=%s  WHERE ip='%s' and date='%s';" % (b_in, b_out, ipaddr, date))
                        #row = cur.fetchone()

        else:
                b_in= float(inn)
                b_out=float(out)
                cur.execute("INSERT INTO traffic_day_traff  (\"ip\", \"byte_in\", \"byte_out\", \"date\") VALUES ('%s', '%s','%s','%s'  )" % ( ipaddr, b_in, b_out,date))
        conn.commit()

conn.close()