# routines to access data from the penthouse database

DB_SERVER = 'bora2.ssec.wisc.edu'
DB_NAME = 'rig'
DB_USER = 'rig'

METEOROLOGY_GROUP_ID=-1
ENGINEERING_GROUP_ID=-2

import pgdb
import re

# make a connection to the database
def connect():
    return pgdb.connect('%s:%s:%s' % (DB_SERVER, DB_NAME, DB_USER))

# close the connection
def disconnect(con):
    con.close()

# get a dataset
def get_dataset(symbol, res, start, end):

	con = connect()
	c = con.cursor()
	c.execute("SELECT symbol_id FROM symbols WHERE name='%s'" % symbol)
	symbol_id = c.fetchone()[0]
	c.execute("SELECT stamp, value " +
	          "FROM data NATURAL JOIN samples " +
	          ("WHERE symbol_id=%d " % symbol_id) +
	          ("AND stamp >= '%s' AND stamp <= '%s' " % (start, end)) +
	          ("AND resolution=%d") % res)
	tmp = c.fetchall()
	disconnect(con)

	return tmp

# get a daraset with the specified units (not checked for errors)
def get_dataset_with_units(symbol, res, start, end, units):

	con = connect()
	c = con.cursor()
	c.execute("SELECT symbol_id FROM symbols WHERE name='%s'" % symbol)
	symbol_id = c.fetchone()[0]
	c.execute(("SELECT stamp, convert_units(value, '%s') " % units) +
              "FROM data NATURAL JOIN samples " +
              ("WHERE symbol_id=%d " % symbol_id) +
              ("AND stamp >= '%s' AND stamp <= '%s' " % (start, end)) +
              ("AND resolution=%d") % res)
	tmp = c.fetchall()
	disconnect(con)

	return tmp


# makes a text file from a dataset - ready for gnuplot
def makefile(dataset, filename):

	f = open(filename, 'w')

	#dataset = map(lambda x: (x[0][0:x[0].index('.')], x[1]), dataset)
	for row in dataset:
		f.write(row[0] + ' ' + str(row[1]) + '\n')

	return (dataset[0][0], dataset[-1][0])

# returns data for the specified symbol
def get_symbol_data(symbol):

    sym_dict = {}

    con = connect()
    c = con.cursor()
    c.execute("SELECT * FROM symbols WHERE name='%s'" % symbol)
    vals = c.fetchone()
    for i in range(len(c.description)):
        sym_dict[c.description[i][0]] = vals[i]

    disconnect(con)

    return sym_dict

# gets all symbol data in a dictionary keyed by name
# dictionary entries are: (id, long name, units, description)
def get_all_symbol_data():

    sym_data = {}

    con = connect()
    c = con.cursor()
    c.execute("SELECT name, symbol_id, long_name, units, description " +
              "FROM symbols")
    for row in c.fetchall():
        sym_data[row[0]] = tuple(row[1:])

    disconnect(con)

    return sym_data

# returns the names of the symbols
def get_symbol_names():

    sym_list = []

    con = connect()
    c = con.cursor()
    c.execute("SELECT s.name FROM symbols s, group_items g " +
              "    WHERE g.symbol_id=s.symbol_id AND g.group_id > -3" +
              "        ORDER BY g.group_id DESC, g.list_order")
    for l in c.fetchall():
        if l[0] != "TIME":
            sym_list.append(l[0])

    disconnect(con)

    return sym_list

# returns long-name data
def get_long_names():

    names = {}
    
    con = connect()
    c = con.cursor()
    c.execute("SELECT name, long_name FROM symbols")
    for name, long_name in c.fetchall():
        names[name] = long_name

    disconnect(con)

    return names

# gets possible units for the given symbols
def get_alternate_units(units):

	con = connect()
	c = con.cursor()
	c.execute("SELECT type FROM units WHERE label='%s'" % units)
	type = c.fetchone()[0]
	c.execute("SELECT label FROM units WHERE type='%s'" % type)
	tmp = map(lambda x: x[0], c.fetchall())

	disconnect(con)

	return tmp

# returns the names of the symbols for meteorology
def get_met_symbol_names():

    return get_group_symbol_names(METEOROLOGY_GROUP_ID)

# returns the names of the symbols for engineering sensors
def get_eng_symbol_names():

	return get_group_symbol_names(ENGINEERING_GROUP_ID)

def get_group_symbol_names(gid):

    sym_list = []

    con = connect()
    c = con.cursor()
    c.execute("SELECT s.name FROM symbols s, group_items g " +
              "    WHERE g.symbol_id=s.symbol_id AND g.group_id=%d" % gid +
              "        ORDER BY g.list_order")
    for l in c.fetchall():
        if l[0] != "TIME":
            sym_list.append(l[0])

    disconnect(con)

    return sym_list

# returns units data
def get_units():

    units_dict = {}

    con = connect()
    c = con.cursor()
    c.execute("SELECT name, units FROM symbols")
    for name, units in c.fetchall():
        units_dict[name] = units

    disconnect(con)

    return units_dict

# returns current conditions tuple: (time_stamp, value_dict)
def get_current_conds():

    data = {}

    con = connect()
    c = con.cursor()
    c.execute("SELECT sample_id, stamp FROM samples WHERE resolution=60 " +
              "ORDER BY stamp DESC LIMIT 1")
    sample_id, stamp = tuple(c.fetchone())
    c.execute("SELECT symbol_id, value from data WHERE sample_id=%d" %
              sample_id)
    for row in c.fetchall():
        data[row[0]] = row[1]

    disconnect(con)

    return (stamp, data)

# returns a dictionary with global values
def get_globals():

    gd = {}

    con = connect()
    c = con.cursor()
    c.execute('SELECT * FROM globals')
    vals = c.fetchone()
    for i in range(len(c.description)):
        gd[c.description[i][0]] = vals[i]

    disconnect(con)

    return gd