import StringIO
import requests
import xypath
import messytables
from hamcrest import equal_to, is_in
from orm import session, Value, DataSet, Indicator, send
import orm
import dl
import re
indicator_list = """
SP.POP.DPND.OL
SP.POP.DPND.YG
SH.DTH.IMRT
SM.POP.TOTL.ZS
AG.LND.TOTL.K2
SP.POP.GROW
EN.POP.DNST
EN.URB.MCTY
NY.GNP.MKTP.CD
SI.POV.DDAY
SP.DYN.AMRT.FE
SP.DYN.AMRT.MA
LP.LPI.OVRL.XQ
IS.ROD.TOTL.KM
IS.RRS.TOTL.KM
NY.GNP.MKTP.PP.CD
NY.GDP.PCAP.PP.CD
NY.GNP.PCAP.PP.CD
DT.ODA.ODAT.PC.ZS
IT.CEL.SETS.P2
IT.NET.USER.P2
NE.CON.PRVT.PP.KD
EG.ELC.ACCS.ZS
SI.POV.GINI""".strip().split('\n')


"""Value: dsID, region, indID, period, value, source, is_number
   DataSet: dsID, last_updated, last_scraped, name
   Indicator: indID, name, units
   """


dataset = {'dsID':'World Bank',
           'last_updated':None,
           'last_scraped':orm.now(),
           'name': 'World Bank'}
    
send(DataSet, dataset)

def getcountrylist():
    for value in session.query(Value).filter(Value.indID == "m49-name").all():
        yield value.region

def getcountry(threeletter="PAK"):
    print threeletter
    value = {'dsID':'World Bank',
             'region':threeletter,
             'source':'World Bank:' + threeletter,
             'is_number':True}
    
    baseurl = "http://api.worldbank.org/datafiles/%s_Country_MetaData_en_EXCEL.xls"
    fh = dl.grab(baseurl%threeletter, [404])
    if not fh: return
    messy = messytables.excel.XLSTableSet(fh)
    table = xypath.Table.from_messy(list(messy.tables)[0])
    indicators = table.filter(is_in(indicator_list))
    indname = indicators.shift(x=-1)
    if not len(indname) == len(indicator_list):
        print "missing indicators", [x.value for x in indname]

    code = table.filter(equal_to('Indicator Code'))

    years = code.extend(x=1)
    junction = indname.junction(years)
    for ind_cell, year_cell, value_cell in junction:
        vdict = dict(value)
        vdict['indID'] = ind_cell.value
        vdict['period'] = year_cell.value
        vdict['value'] = value_cell.value

        indicator = {'indID':vdict['indID']}
        nameunits = re.search('(.*)\((.*)\)',vdict['indID'])
        if nameunits:
            (indicator['name'], indicator['units'])=nameunits.groups()
        else:
            indicator['name']=vdict['indID']
            indicator['units']='uno'
        send(Indicator, indicator)
        send(Value, vdict)
    session.commit()

for country in getcountrylist():
    getcountry(country)

@@ -881,35 +881,27 @@
 k'%7D%0A
-    %0Asend(
+%0A
 DataSet
-, 
+(**
 dataset)
 %0A%0Ade
@@ -896,16 +896,27 @@
 dataset)
+.save()    
 %0A%0Adef ge
@@ -2339,21 +2339,16 @@
         
-send(
 Indicato
@@ -2348,18 +2348,19 @@
 ndicator
-, 
+(**
 indicato
@@ -2361,16 +2361,23 @@
 dicator)
+.save()
 %0A       
@@ -2381,27 +2381,76 @@
     
-send(
+v = 
 Value
-, 
+(**
 vdict)%0A
+        if not v.is_blank: %0A            v.save()%0A
     

refactor save