# -*- coding: utf-8 -*-
"""
Created on Mon Feb 11 09:51:02 2019

@author: cpace
"""

import MySQLdb
import pandas as pd
from Modules.pnp import pnp as pnp

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

class dataseries:
    def __init__(self, crop, month, year, range='all'):
        sql = "select date, open, high, low, close " \
        "from "\
        	"MarketHistory " 
        if range == 'pnp':
            pnpdata = pnp(crop, month, year)
            sql = sql + "where Date between '" + str(pnpdata.startDate) + "' and '" + str(pnpdata.endDate) + "' and crop = '" + crop + "' and contract = '" + month + "';"
        else:
            sql = sql + "where year = '" + str(year) + "' and crop = '" + crop + "' and contract = '" + month + "' order by date asc;"
        cur.execute(sql)
        data = pd.DataFrame(list(cur.fetchall()))
        data.rename(columns={0:'Date', 1:'Open', 2:'High', 3:'Low', 4:'Close'}, inplace=True)
        self.dataseries = data

    def closeconn(self):
        return db.close()