#!/usr/bin/env python

import MySQLdb
import datetime
import sys

sys.path.insert(0, '../ajax')

import tdameritradedata as td

now = datetime.datetime.now()


db = MySQLdb.connect(host="a2nlmysql13plsk.secureserver.net",  # your host 
                     user="coreypace",       # username
                     passwd="Aud12rey",     # password
                     db="FarmBusiness")   # name of the database

# Create a Cursor object to execute queries.
cur = db.cursor()

# print the first and second columns      
#for row in cur.fetchall() :
#    print(row[3], " ", row[6])

data = td.dataoutput

for i in range (len(data)): 
    symbol = data[i]['quoteCode'][:3] + str(data[i]['contractYear'])
    open = data[i]['open']
    high = data[i]['high']
    low = data[i]['low']
    close = data[i]['last']
    vol = data[i]['volume']
    tradedate = data[i]['tradeDate']
    date = now.strftime("%Y/%m/%d")
    contractmonth = data[i]['monthCode']
    contractyear = data[i]['contractYear']
    crop = data[i]['cropCode']

# Select index base price for contract
    sql = "SELECT min(MarketHistory.Date), MarketHistory.Close from MarketHistory where Symbol = '" + symbol + "';"
    cur.execute(sql)
    for row in cur.fetchall() :
        if (row[1] == None):
            priceindex = 1;
        else :
            priceindex = float(close)/row[1]
    insertsql = "INSERT INTO MarketHistory (Crop, Contract, Year, Symbol, Date, Open, High, Low, Close, Vol, PriceIndex) \
                Values ('" + str(crop) + "','" + str(contractmonth) + "'," + str(contractyear) + ",'" + str(symbol) + "','" \
                + str(tradedate) + "'," + str(open) + "," + str(high) + "," + str(low) + "," + str(close) + "," + str(vol) \
                + "," + str(priceindex) + ");"
    print(insertsql)
    cur.execute(insertsql)
    db.commit()
        

db.close()