# -*- 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 = 'Gasoline'
month = 'Aug'
pnpline = 'downhigh'
totalyearsincluded = 30
final = []
loser = 0
winner = 0
sumwins = 0
sumdays = 0


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
    a = pnp(crop, month, year)   
    while count < len(df)-2:
       if df.Close[count-1] - df.Close[count] >= .044: 
           final.append([year, df.Date[count], df.Open[count+1] - df.Close[count+1], df.Low[count] - df.Open[count+1]])
       count += 1
           
for i in range(len(final)):
    if final[i][2] < 0:
        loser += 1
    else:
        winner += 1
        sumwins += final[i][2]

print(str(winner) + ' Wins')
print(str(loser) + ' Losers')
print(str(winner/(winner+loser)*100) + '% Win Rate')
print('Avg Win - ' + str(sumwins/winner))

  


cur.close()