buoys2db(sqlite)

####################################################################
#
#    Created: 2013/01/18
#
#    Buoys data 2 sqlite data base
#
#    Modified: 2013/01/21
#
#####################################################################

import sqlite3
import re
import urllib
import os
import shapefile as sf
import datetime

def makeShp(list,id):
    w.poly(parts=[list[:]], shapeType=sf.POLYLINE)
    w.record(id)

def makePrj(shp_filename):
    prj = open("%s.prj" % shp_filename, "w")
    epsg = 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]]'
    prj.write(epsg)
    prj.close()

def b2db(str):
    urllib.urlretrieve (str, 'buoys_tmp.txt')
    ffile = open('buoys_tmp.txt')
    lines = ffile.readlines()
    #Empty lists for coordinates, dates
    data = []
    i=0
    for line in lines:
        i_wmo_id = line.split(';')[1]
        yyyy = line.split(';')[4]
        mm = line.split(';')[5]
        dd = line.split(';')[6]
        hh = line.split(';')[7]
        min = line.split(';')[8]
        iidate = yyyy + '-' + mm + '-' + dd + ' ' + hh + ':' + min
        lat = line.split(';')[2]
        lon = line.split(';')[3]
        islp = line.split(';')[10]
        #print 'islp: ', islp
        itemperature = line.split(';')[9]
        #print 'it: ', itemperature
        idata = [i_wmo_id, iidate, lat, lon,itemperature,islp,'\n']
        idata.pop()
        data.append(idata)
        i = i + 1
    ffile.close()
    os.system('DEL buoys_tmp.txt')
    return data

def url2db():
    retrive_str = 'http://www.aari.nw.ru/clgmi/gts/buoy/buoy.daily.csv.n.txt'
    data = b2db(retrive_str)
    for t in data:
        try:
            c.execute("INSERT OR FAIL INTO trajectories VALUES (?,?,?,?,?,?)", t)
            print 'a new data added to database!'
        except:
            pass

def id_data(id):
    ''' retrieve data by ID '''
    ll = []
    idate2 = date2[0:4] + '-' + date2[4:6] + '-' + date2[6:8] + ' 23:59'
    idate1 = date1[0:4] + '-' + date1[4:6] + '-' + date1[6:8] + ' 00:00'
    print '\nidate1: ', idate1
    print 'idate2: ', idate2
    t = (id, idate1, idate2)
    c.execute("SELECT * FROM trajectories WHERE (id=? AND date>=? AND date<=?) ORDER BY date", t) #AND date>=(?) AND date<=(?))
    print "\nData for buoy#%s:" % (id)
    for row in c:
        print row
        lon = float(row[3])
        lat = float(row[2])
        ll.append([lon,lat])
    return ll

def ids():
    ''' retrieve IDs of buoys '''
    ids = []
    c.execute("SELECT id FROM trajectories")
    for row in c:
        ids.append(row)
    return ids

def create_db(db_name):
    ''' create db and tables '''
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()

    # create a table
    cursor.execute("""CREATE TABLE trajectories (id integer, date text, lat text, lon text, temperature text, slp text)""")
    cursor.execute("""CREATE UNIQUE INDEX IDX_ll ON trajectories(id,date)""")
    conn.commit()

def date_gen():
    today = datetime.date.today()
    today_yyyy = str(today.timetuple()[0])
    if today.timetuple()[1]<10:
        today_mm   = '0' + str(today.timetuple()[1])
    else:
        today_mm   = str(today.timetuple()[1])
    if today.timetuple()[2]<10:
        today_dd   = '0' + str(today.timetuple()[2])
    else:
        today_dd   = str(today.timetuple()[1])
    today_dd   = str(today.timetuple()[2])
    # 7 days ago
    Ndays_ago = datetime.datetime.now() - datetime.timedelta(days=7)
    yyyy1 = str(Ndays_ago.timetuple()[0])
    if Ndays_ago.timetuple()[1]<10:
        mm1 = '0' + str(Ndays_ago.timetuple()[1])
    else:
        mm1 = str(Ndays_ago.timetuple()[1])
    if Ndays_ago.timetuple()[2]<10:
        dd1 = '0' + str(Ndays_ago.timetuple()[2])
    else:
        dd1 = str(Ndays_ago.timetuple()[2])
    date1 = yyyy1 + mm1 + dd1
    date2 = today_yyyy + today_mm + today_dd
    return date1, date2



# main programm
try:
    date1, date2 = date_gen()
    conn = sqlite3.connect('buoys')
    c = conn.cursor()
    #create DB
    #create_db('buoys')
    #data from url 2 data base
    db=url2db()
    # retrieve ids
    idss = ids()
    # remove duplicate ids
    ids_list = list(set(idss))

    try:
        os.system(r"NET USE Y: \\193.227.233.12\gmo\arc %s /USER:%s " % ('aaridem', 'denis'))
        print "NET USE Y: - OK!"
    except:
        print 'Mount ERROR!'
    try:

        shp_path = "Y:\\" + date2[0:4] + "\\" + date2[4:6] + "\\" + date2[6:8] + "\\ice_drift\\buoys\\"
        print shp_path
        md_dir = "MD " + shp_path
        try:
            os.system(md_dir)
            print "MD dir - OK"
        except:
            print "MD dir ERROR!"

        w = sf.Writer(sf.POLYLINE)
        w.field('id','C','40')
        shp_filename = shp_path + date1 + '-' + date2

        for id in ids_list:
            id = id[0]
            # retrieve data for a buoy by id
            list = id_data(id)
            makeShp(list, str(id))
        w.save(shp_filename)
        makePrj(shp_filename)
        os.system(r"NET USE Y: /DELETE")
    except:
        print 'Make shape error'
        os.system(r"NET USE Y: /DELETE")

    conn.commit()
except:
    print 'Conncetion error.'