import psycopg2, psycopg2.extras, telnetlib

quote = 200
connection2 = "host=localhost user=nimnull dbname=billing"

conn = psycopg2.connect(connection2)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

addr_traf = dict()
#selecting traffic from monhtly summary table
cur.execute("select ipaddr, sum(total) as total from traf_daily \
JOIN billingv3 ON out = ipaddr \
JOIN users_states ON billingv3.usr_id = users_states.usr_id \
WHERE traf_daily.time_stamp > date_trunc('month', now()) \
AND \
users_states.new_state in (0,2,7) \
GROUP BY ipaddr \
ORDER BY total ")

for row in cur.fetchall():
    addr_traf[row['ipaddr']] = int(row['total'])
#selectin' traf from daile & summarazin' it
cur.execute(" \
SELECT ipaddr, sum(bytes)/1024/1024 AS total \
FROM traf \
JOIN billingv3 ON out = ipaddr \
JOIN users_states ON billingv3.usr_id = users_states.usr_id \
WHERE traf.time_stamp::date = now()::date \
AND users_states.new_state IN (0,2,7) \
GROUP BY ipaddr \
HAVING sum(bytes)/1024/1024 > 1 \
ORDER BY total DESC")

for row in cur.fetchall():
    if(addr_traf.has_key(row['ipaddr'])):
    if(addr_traf.has_key(row['ipaddr'])):
        addr_traf[row['ipaddr']] += int(row['total'])
    else:
        addr_traf[row['ipaddr']] = int(row['total'])

acl = open('/.1/tftp/no_out','w')
acl.write('!\nno access-l 90\n!\n')
#writin' down those with exceeded quote
for addr, traf_m in addr_traf.items():
    if traf_m >= quote:
        acl.write("access-l 90 perm h %s\n"%(addr,))
#       print "%s - %d Mb"%(addr, traf_m)

acl.write('!\nend\n!\n')
acl.close()


CAT = "ххх.ххх.ххх.ххх"
tn = telnetlib.Telnet(CAT)

print tn.read_until("Username: ")
tn.write("******\n")
print tn.read_until("Password: ")
tn.write("******\n")
print tn.read_until("opp-gw0>")
tn.write("en\n")
print tn.read_until("Password: ")
tn.write("******\n")
print tn.read_until("opp-gw0#")
tn.write("copy tftp://ххх.ххх.ххх.ххх/no_out running-config\n")
print tn.read_until("Destination filename [running-config]? ")
tn.write("\n")
print tn.read_until("opp-gw0#")
tn.write("logout\n")
tn.close()