'''
 Scrape out the Soil Bulk Density and Texture data from Google Drive
'''
import util
import sys
import gdata.docs.client
import ConfigParser
import psycopg2

YEAR = sys.argv[1]

config = ConfigParser.ConfigParser()
config.read('mytokens.cfg')

pgconn = psycopg2.connect(database='sustainablecorn',
                          host=config.get('database', 'host'))
pcursor = pgconn.cursor()

# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)
docs_client = util.get_docs_client(config)

query = gdata.docs.client.DocsQuery(show_collections='false', 
                                    title='Soil Bulk Density and Water Retention Data')
feed = docs_client.GetAllResources(query=query)

for entry in feed:
    if entry.get_resource_type() != 'spreadsheet':
        continue
    spreadsheet = util.Spreadsheet(docs_client, spr_client, entry)
    spreadsheet.get_worksheets()
    siteid = spreadsheet.title.split()[0]
    worksheet = spreadsheet.worksheets.get(YEAR)
    if worksheet is None:
        print 'Missing %s sheet for %s' % (YEAR, siteid)
        continue
    worksheet.get_cell_feed()
    #if siteid == 'DPAC':
    #    print 'ERROR: Skipping DPAC Soil Texture sheet as it has subsamples'
    #    continue
    #print 'Processing %s Soil BD & Water Retention Year %s' % (siteid, YEAR),
    if (worksheet.get_cell_value(1, 1) != 'plotid' or
        worksheet.get_cell_value(1, 2) != 'depth' or
        worksheet.get_cell_value(1, 3) != 'subsample'):
        print 'FATAL site: %s(%s) bd & wr has bad header 1:%s 2:%s 3:%s' % (
            siteid, YEAR, worksheet.get_cell_value(1, 1), 
            worksheet.get_cell_value(1, 2), 
            worksheet.get_cell_value(1, 3))
        continue
    for row in range(4,worksheet.rows+1):
        plotid = worksheet.get_cell_value(row, 1)
        depth = worksheet.get_cell_value(row, 2)
        subsample = worksheet.get_cell_value(row, 3)
        if plotid is None or depth is None:
            continue
        for col in range(4, worksheet.cols+1):
            varname = worksheet.get_cell_value(1,col).strip().split()[0]
            if varname[:4] != 'SOIL':
                #print 'Invalid varname: %s site: %s year: %s' % (
                #                    worksheet.get_cell_value(1,col).strip(),
                #                    siteid, YEAR)
                continue
            val = worksheet.get_cell_value(row, col)
            try:
                pcursor.execute("""
                    INSERT into soil_data(site, plotid, varname, year, 
                    depth, value, subsample)
                    values (%s, %s, %s, %s, %s, %s, %s)
                    """, (siteid, plotid, varname, YEAR, depth, val, subsample))
            except Exception, exp:
                print 'HARVEST_SOIL_BD TRACEBACK'
                print exp
                print '%s %s %s %s %s %s' % (siteid, plotid, varname, depth, 
                                             val, subsample)
                sys.exit()
    #print "...done"
pcursor.close()
pgconn.commit()
pgconn.close()
@@ -1,9 +1,7 @@
-'''%0A 
+%22%22%22
 Scra
@@ -67,12 +67,11 @@
 rive
-%0A'''
+%22%22%22
 %0Aimp
@@ -1709,24 +1709,347 @@
    continue%0A
+    %0A    #Load up current data, incase we need to do some deleting%0A    current = %7B%7D%0A    pcursor.execute(%22%22%22SELECT plotid, varname, depth, subsample%0A    from soil_data WHERE site = %25s and year = %25s%22%22%22, (siteid, YEAR))%0A    for row in pcursor:%0A        key = %22%25s%7C%25s%7C%25s%7C%25s%22 %25 row%0A        current%5Bkey%5D = True%0A    found_vars = %5B%5D%0A
     for row 
@@ -2666,32 +2666,117 @@
        continue%0A
+            if not varname in found_vars:%0A                found_vars.append(varname)%0A
             val 
@@ -3361,32 +3361,32 @@
 val, subsample)%0A
-
                 
@@ -3396,16 +3396,696 @@
 .exit()%0A
+            key = %22%25s%7C%25s%7C%25s%7C%25s%22 %25 (plotid, varname, depth, subsample)%0A            if current.has_key(key):%0A                del(current%5Bkey%5D)%0A    for key in current:%0A        (plotid, varname, depth, subsample) = key.split(%22%7C%22)%0A        if varname in found_vars:%0A            print 'harvest_soil_bd REMOVE %25s %25s %25s %25s %25s' %25 (siteid, plotid, %0A                                                    varname, depth, subsample)%0A            pcursor.execute(%22%22%22DELETE from soil_data where site = %25s and %0A            plotid = %25s and varname = %25s and year = %25s and depth = %25s and%0A            subsample = %25s%22%22%22, (siteid, plotid, varname, YEAR, depth,%0A                                subsample))%0A%0A
     #pri

Remove cruft from database