import MySQLdb
import datetime
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, profitdata.profitdate - profitdata.startdate as daystoprofit, min(MarketHistory.Date) - profitdata.startdate as daystodrawdown \
    from \
    	MarketHistory, \
    	(select \
    		year, min(profit.date) as profitdate, (profit.profit) as profit, profit.startprice as startprice, profit.startdate as startdate \
    	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, startprice.startdate as startdate \
    		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) as startdate, 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, profitdata.profitdate - profitdata.startdate as daystoprofit, min(MarketHistory.Date) - profitdata.startdate as daystodrawdown \
    from \
    	MarketHistory, \
    	(select \
    		year, min(profit.date) as profitdate, (profit.profit) as profit, profit.startprice as startprice, profit.startdate as startdate \
    	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, startprice.startdate as startdate \
    		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) as startdate, 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

dollars = getdollarsperincrement()
finalfinal = []

#windows = gettradewindows(start = '5-6')
#for window in windows:
if True:
    final = []
#    startdate = window[0]
#    enddate = window[1]
#    crop = window[2]
#    contract = window[3]
#    position = window[4]
    crop = 'Heating_Oil'
    contract = 'Jul'
    startdate = '5-16'
    enddate = '5-23'
    position = 'long'
    
    targetprofit = 60
    
    while targetprofit < 2560:
        targetprofit += 100
        try:
            if position == 'long':
                a = getlongtrades(crop, contract, startdate, enddate, dollars, targetprofit)
            else:
                a = getshorttrades(crop, contract, startdate, enddate, dollars, targetprofit)
            winrate = round(len(a)/15,2)
            avgdrawdown = round(sum([pair[4] for pair in a])/len(a),0)
            maxdrawdown = round(min([pair[4] for pair in a]),0) - 60
            avgdaytoprofit = round(sum([pair[5] for pair in a])/len(a),0)
            avgdaytodrawdown = round(sum([pair[6] for pair in a])/len(a),0)
            maxdaytoprofit = round(max([pair[5] for pair in a]),0)
            maxdaytodrawdown = round(max([pair[6] for pair in a]),0)
            final.append([crop, contract, startdate, enddate, position, targetprofit, winrate*100, maxdrawdown,targetprofit*winrate+maxdrawdown*(1-winrate), avgdaytoprofit, avgdaytodrawdown, maxdaytoprofit, maxdaytodrawdown])
        except:
            continue
    
    finalfinal.append(max(final,key=itemgetter(8)))