# -*- coding: utf-8 -*-

from dateutil.parser import parse
from Modules.contractsymbol import contractsymbol as symbol
#import csv
import MySQLdb
import sys
from datetime import datetime

string = str(sys.argv[1])

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.
cursor = db.cursor()

#file = 'C:\\Users\\cpace\\Documents\\texts.csv'

update = []
append = 0
output = []

def getdate(splitstring):
    try:
        for i in range(len(splitstring)):
            if '/' not in splitstring[i]:
                return False
            try: 
                return parse(splitstring[i])
            except ValueError:
                return False
    except:
        False
        
def preprocess(string):
    string = string.lower().replace("_"," _")
    string = string.lower().replace(',', '').split()
    for i in range(len(string)):
        try:
            del string[[x[0] for x in string].index("_")]
        except:
            True
    return string

def checkforupdate(output, cur):
    for curoutput in output:
        if cur[0] == curoutput[0] and cur[1] == curoutput[1] and cur[2] == curoutput[2]:
            return True

def main(string):
    out = []
    splitstring = preprocess(string) 
    if getdate(splitstring):
        date = datetime.strftime(getdate(splitstring), '%Y-%m-%d')
    for i in range(len(splitstring)):
        try:
            cur = [date, getmonth(splitstring[i], date), getcrop(splitstring[i+1]), splitstring[i+2]] #cur=[date, month, crop, price]
            sym = symbol(cur[2],cur[1],date)
            sql = "select close from MarketHistory where symbol = '" + sym.symbol + "' and date = '" + cur[0] + "';"
            cursor.execute(sql)
            close = cursor.fetchall()[0][0]
            basis = float(cur[3])*100 - float(close) 
            cur.append(sym.deliveryyear)
            cur.append(basis)
            if len(out)==0:
                out.append(cur)
            else:
                if checkforupdate(out, cur):
                    out[[x[0:3] for x in out].index(cur[0:3])] = cur
                else:
                    out.append(cur)
        except:
            True
    return out

def getmonth(month, date):
    if month == 'cash':
        x = datetime.strptime(date, '%Y-%m-%d')
    else:
        x = datetime.now()
        
    a = {'jan':'jan', 'feb':'feb', 'mar':'mar', 'apr':'apr', 'may':'may', \
         'jun':'jun', 'jul':'jul', 'aug':'aug', 'sep':'sep', 'oct':'oct', \
         'nov':'nov', 'dec':'dec', 'july':'jul', 'fall':'oct', 'j/j':'jun', \
         'o/n':'oct', 'cash':x.strftime('%b').lower(), 'january':'jan', \
         'february':'feb', 'march':'mar', 'april':'apr', 'june':'jun', \
         'july':'jul', 'august':'aug', 'september':'sep', 'october':'oct', \
         'november':'nov', 'december':'dec'}
    return a[month.lower()]


def getcrop(crop):
    a = {'corn':'corn', 'beans':'soybeans', 'soybeans':'soybeans', 'b':'soybeans', \
        'c':'corn', 'w':'wheat', 'wheat':'wheat'}
    return a[crop.lower()] 

def getoutput():
    out = []
    sql = 'select date, deliverymonth, crop, "10", deliveryyear, basis from Basis1;'
    cursor.execute(sql)
    for x in list(cursor.fetchall()):
        out.append(list(x))
    return out

output = getoutput();
#with open(file) as f:
#    reader = csv.reader(f)
#    for string in reader:
#        if len(string)>1:
#            continue
for sublist in main(string):
    if checkforupdate(output, sublist):
        output[output.index(sublist)] = sublist
        update.append(sublist)
        sql1 = "update Basis1 set deliverymonth = '" + sublist[1] + \
                "', date = '" + sublist[0] + "', crop = '" + sublist[2] + \
                "', basis = " + str(sublist[5]) + " where deliverymonth = '" + sublist[1] + \
                "' and date = '" + sublist[0] + "' and crop = '" + sublist[2] + "' and deliveryyear = '" + str(sublist[4]) + "';"
    else:
        output.append(sublist)
        append = append + 1
        sql1 = "insert into Basis1 (deliverymonth, date, crop, basis, deliveryyear) values ('" + sublist[1] + \
        "','" + sublist[0] + "', '" + sublist[2] + "', " + str(sublist[5]) + ", " + str(sublist[4]) + ");" 
        cursor.execute(sql1)
        db.commit()
                    

db.close()