import MySQLdb
import datetime
import numpy as np
from operator import itemgetter

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.
dbcur = db.cursor()


def getdollarsperincrement():
#if True:
    dollardict = {}
    sql = 'SELECT MarketCommodity.DBName, MarketCommodity.DollarsPerIncrement \
            FROM MarketCommodity'
    dbcur.execute(sql)
    out = dbcur.fetchall()
    if len(out) > 0:
        for i in range(len(out)):
            dollardict[out[i][0]] = out[i][1]
    return dollardict

def getshorttrades(crop, contract, startdate, enddate, dollars, targetprofit):
    sql = 'select \
    	MarketHistory.year, profitdata.profit, profitdata.profitdate as profitdate, min(MarketHistory.Date) as drawdowndate, (profitdata.startprice - max(MarketHistory.High))*' + str(dollars[crop]) + ' as drawdown \
    from \
    	MarketHistory, \
    	(select \
    		year, min(profit.date) as profitdate, (profit.profit) as profit, profit.startprice as startprice \
    	from \
    		(select \
    			startprice.year, lowestprice.date as date, startprice.startprice as startprice, lowestprice.lowprice as lowprice, ' + str(dollars[crop]) + '*(startprice.startprice - lowestprice.lowprice) as profit \
    		from \
    			(select year, MarketHistory.Date, MarketHistory.Low as lowprice from MarketHistory where crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and date > concat(year, "-", "' + str(startdate) + '") and date <= concat(year, "-", "' + str(enddate) + '")) lowestprice, \
    			(select year, min(MarketHistory.Date), MarketHistory.Close as startprice from MarketHistory where crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and date >= concat(year, "-", "' + str(startdate) + '") group by year) startprice \
    		where \
    			startprice.year = lowestprice.year) profit \
    	where \
    		(profit.profit) >= ' + str(targetprofit) + ' \
    	group by \
    		year) profitdata \
    where \
    	crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and MarketHistory.Date > concat(MarketHistory.year, "-", "' + str(startdate) + '") and MarketHistory.Date <= concat(MarketHistory.year, "-", "' + str(enddate) + '") and MarketHistory.Date <= profitdata.profitdate and MarketHistory.year = profitdata.year and MarketHistory.year >= 2004 \
    group by \
    	year'
    dbcur.execute(sql)
    out = dbcur.fetchall()
    return out

def getlongtrades(crop, contract, startdate, enddate, dollars, targetprofit):
    sql = 'select \
    	MarketHistory.year, profitdata.profit, profitdata.profitdate as profitdate, min(MarketHistory.Date) as drawdowndate, (min(MarketHistory.Low) - profitdata.startprice)*' + str(dollars[crop]) + ' as drawdown \
    from \
    	MarketHistory, \
    	(select \
    		year, min(profit.date) as profitdate, (profit.profit) as profit, profit.startprice as startprice \
    	from \
    		(select \
    			startprice.year, highestprice.date as date, startprice.startprice as startprice, highestprice.highprice as highprice, ' + str(dollars[crop]) + '*(highestprice.highprice - startprice.startprice) as profit \
    		from \
    			(select year, MarketHistory.Date, MarketHistory.High as highprice from MarketHistory where crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and date > concat(year, "-", "' + str(startdate) + '") and date <= concat(year, "-", "' + str(enddate) + '")) highestprice, \
    			(select year, min(MarketHistory.Date), MarketHistory.Close as startprice from MarketHistory where crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and date >= concat(year, "-", "' + str(startdate) + '") group by year) startprice \
    		where \
    			startprice.year = highestprice.year) profit \
    	where \
    		(profit.profit) >= ' + str(targetprofit) + ' \
    	group by \
    		year) profitdata \
    where \
    	crop = "' + str(crop) + '" and contract = "' + str(contract) + '" and MarketHistory.Date > concat(MarketHistory.year, "-", "' + str(startdate) + '") and MarketHistory.Date <= concat(MarketHistory.year, "-", "' + str(enddate) + '") and MarketHistory.Date <= profitdata.profitdate and MarketHistory.year = profitdata.year and MarketHistory.year >= 2004 \
    group by \
    	year'
    dbcur.execute(sql)
    out = dbcur.fetchall()
    return out


def gettradewindows(start = (str(datetime.date.today().month) + '-' + str(datetime.date.today().day))):
    sql = 'select start, end, commodity, month, position, winrate, mean from MarketTradeWindows where start = "' + str(start) + '"'
    dbcur.execute(sql)
    out = dbcur.fetchall()
    return out

final = []
#crop = 'Copper'
#contract = 'Jul'
#startdate = '5-3'
#enddate = '5-14'
dollars = getdollarsperincrement()
targetprofit = 760

windows = gettradewindows(start = '5-6')
for window in windows:
    startdate = window[0]
    enddate = window[1]
    crop = window[2]
    contract = window[3]
    position = window[4]
    try:
        if position == 'short':
            trades = getshorttrades(crop, contract, startdate, enddate, dollars, targetprofit)
        else:
            trades = getlongtrades(crop, contract, startdate, enddate, dollars, targetprofit)
        winrate = round(len(trades)/15,2)
        if winrate >= 0.7:
            final.append([startdate, enddate, crop, contract, position, round(min(trades,key=itemgetter(4))[4],0), round(sum([pair[4] for pair in trades])/len(trades),0), winrate])
    except:
        continue

