# -*- coding: utf-8 -*-
"""
Created on Tue Apr  2 19:33:11 2019

@author: cpace
"""

from Modules.dataseries import dataseries
from Modules.pnp import pnp
import numpy as np
import matplotlib.pyplot as plt
import MySQLdb
import datetime

crop = 'Corn'
month = 'Jul'
totalyearsincluded = 15
final = []
d = []
high = []
loser = 0
winner = 0
sumwins = 0
sumdays = 0
percenthigh = 0
nextdaywin = 0
nextdaywinnings = 0
nextdaydrawdown = 0

print('Commodity ' + str(crop))
print('Month ' + str(month))

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

cur = db.cursor()

def getfirstyear(commodity):
    sql = 'select FirstYear from MarketCommodity where DBName = "' + str(commodity) + '"'
    cur.execute(sql)
    firstyear = cur.fetchall()[0][0]
    return max(firstyear,datetime.datetime.now().year - totalyearsincluded)

for year in range(getfirstyear(crop),datetime.datetime.now().year):
    count = 1
    looking = True
    df = dataseries(crop, month, year).dataseries
    
    while count < len(df)-1:
       if df.Low[count] > df.High[count-1]: #next day low higher than previous day high
           abovehigh = []
           start = df.Open[count]
           startcount = count + 1
           while df.Low[startcount] > start:   #while it stays above the starting price
               abovehigh.append(df.Close[startcount]) #append closing price
               if startcount == len(df)-1:  #if end of dataset
                   break
               else:
                   startcount += 1
           if startcount == count + 1:   
               final.append([year, 0, 0, 0, df.High[count+1] - df.Open[count+1],df.Open[count+1] - df.Low[count+1]])
           else:
               final.append([year, abovehigh.index(max(abovehigh)) + 1, 0, max(abovehigh) - start, df.High[count+1] - df.Open[count+1],df.Open[count+1] - df.Low[count+1]])
           count += 1
       elif count == len(df)-1:
           break
       else:
           count += 1
   
for i in range(len(final)):
    if final[i][1] == 0:
        loser += 1
    else:
        winner += 1
        sumwins += final[i][3]
        sumdays += final[i][1]
        percenthigh += final[i][2]
#        d.append(final[i][2])
    
    if final[i][4] > 0:
        nextdaywin += 1
        nextdaywinnings += final[i][4]
        d.append(final[i][4])
        nextdaydrawdown += final[i][5]
        
    if final[i][3] > 0:
        high.append(final[i][3])
        
try:
    print(str(winner) + ' Wins (Closed below line at least 2 days)')
    print(str(loser) + ' Losers (Closed above line next day)')
    print(str(round(winner/(winner+loser)*100,1)) + '% Win Rate')
    print('Avg Win - ' + str(round(sumwins/winner,3)))
    print('Avg Days - ' + str(round(sumdays/winner,1)))
    print('Avg Percent of High - ' + str(round(percenthigh/winner,1)) + '%')
    print('Next Day % Winners - ' + str(round(nextdaywin/(winner+loser)*100,1)) + '%')
    print('Average Next Day Winnings - $' + str(round(nextdaywinnings/nextdaywin,3)))
    print('Average Next Day Drawdown - $' + str(round(nextdaydrawdown/nextdaywin,3)))
except:
    True




## An "interface" to matplotlib.axes.Axes.hist() method
#d = np.asarray(high)
#n, bins, patches = plt.hist(x=d, bins='auto', color='#0504aa',
#                            alpha=0.7, rwidth=0.85)
#plt.grid(axis='y', alpha=0.75)
#plt.xlabel('$0.01 gain')
#plt.ylabel('Frequency')
#plt.title('Histogram of Jul Corn Rising Thru Up Year Low')
#maxfreq = n.max()
## Set a clean upper y-axis limit.
#plt.ylim(ymax=np.ceil(maxfreq / 10) * 10 if maxfreq % 10 else maxfreq + 10)

cur.close()