# Mine SNET data, since we aren't writing flat files anymore!

import mx.DateTime, os
from pyIEM import iemdb
i = iemdb.iemdb()
access = i['iem']
snet = i['snet']

ts = mx.DateTime.now() - mx.DateTime.RelativeDateTime(days=1)

# Collect obs from iemaccess
sql = "SELECT c.*, t.id from current_log c JOIN stations t ON (t.iemid = c.iemid) WHERE date(valid) = '%s' and t.network IN ('KELO','KCCI','KIMT')" % (ts.strftime("%Y-%m-%d"),)
rs = access.query(sql).dictresult()

# Dump them into snet archive...
"""
 station | character varying(5)     | 
 valid   | timestamp with time zone | 
 tmpf    | smallint                 | 
 dwpf    | smallint                 | 
 drct    | smallint                 | 
 sknt    | real                     | 
 pday    | real                     | 
 pmonth  | real                     | 
 srad    | real                     | 
 relh    | real                     | 
 alti    | real                     | 
 gust    | smallint                 | 
"""
out = open('/tmp/snet_dbinsert.sql', 'w')
out.write("DELETE from t%s WHERE date(valid) = '%s';\n" % (ts.strftime("%Y_%m"), ts.strftime("%Y-%m-%d") ))
out.write("COPY t%s FROM stdin;\n" % (ts.strftime("%Y_%m"),) )
for i in range(len(rs)):
  if (rs[i]['pmonth'] is None):
    rs[i]['pmonth'] = 0
  s = "%(id)s\t%(valid)s\t%(tmpf)s\t%(dwpf)s\t%(drct)s\t%(sknt)s\t%(pday)s\t%(pmonth)s\t%(srad)s\t%(relh)s\t%(pres)s\t%(gust)s\n" % rs[i]
  out.write(s.replace("None","null"))
  if (i > 0 and i % 1000 == 0):
    out.write("\.\nCOPY t%s FROM stdin;\n" % (ts.strftime("%Y_%m"),) )

out.write("\.\n")
out.close()

so, si = os.popen4("psql -h iemdb -f /tmp/snet_dbinsert.sql snet")

@@ -1299,17 +1299,19 @@
 t%25(tmpf)
-s
+.0f
 %5Ct%25(dwpf
@@ -1311,17 +1311,19 @@
 t%25(dwpf)
-s
+.0f
 %5Ct%25(drct
@@ -1323,17 +1323,19 @@
 t%25(drct)
-s
+.0f
 %5Ct%25(sknt
@@ -1397,17 +1397,19 @@
 t%25(gust)
-s
+.0f
 %5Cn%22 %25 rs
@@ -1590,13 +1590,14 @@
 )%0A%0As
-o, si
+i, soe
  = o
@@ -1653,8 +1653,25 @@
  snet%22)%0A
+print soe.read()%0A

fix database import bugs