# -*- coding: utf-8 -*-
"""
Created on Thu Mar 14 11:04:52 2019

@author: cpace
"""

import json
import requests
import datetime
import MySQLdb

values = []

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 symbol = "' + str(commodity) + '"'
    cur.execute(sql)
    firstyear = cur.fetchall()[0][0]
    return firstyear

def getmonths(commodity):
    monthlist = []
    sql = 'SELECT DISTINCT ShortMonth from MarketCommodityMonths where Symbol = "' + str(commodity) + '"'
    cur.execute(sql)
    months = cur.fetchall()
    for i in range(len(months)):
        monthlist.append(months[i][0])
    return monthlist

monthcodes = ['A', 'F', 'G', 'H', 'J', 'K', 'M', 'N', 'Q', 'U', 'V', 'X', 'Z']
monthdesc = {'jan':'F', 'feb':'G', 'mar':'H', 'apr':'J', 'may':'K', 'jun':'M', 'jul':'N', 'aug':'Q', 'sep':'U', 'oct':'V', 'nov':'X', 'dec':'Z'}

commoditydesc = {'AK':'Ethanol', 'CL':'Crude_Oil', 'HO':'Heating_Oil', \
                 'FC':'Feeder_Cattle', 'LH':'Lean_Hogs', 'LC':'Live_Cattle', 'AD':'Australian_Dollar', \
                 'BP':'British_Pound', 'KC':'Coffee_ICE', 'YM':'Dow_Jones Mini', 'ES':'S&P_500_Mini', \
                 'NQ':'Nasdaq_100_Mini', 'GC':'Gold', 'SI':'Silver', 'PL':'Platinum', 'HG':'Copper', \
                 'PA':'Palladium', 'RB':'Gasoline', 'NG':'Natural_Gas', 'SB':'Sugar_11', 'KC':'Coffee_ICE', \
                 'CT':'Cotton', 'OJ':'Orange_Juice', 'CD':'Canadian_Dollar', 'JY':'Japanese_Yen', 'KW':'KC_Wheat', \
                 'O':'Oats', 'SM':'Soybean_Meal', 'US':'T-Bond_30', 'SF':'Swiss_Franc', 'ZC':'Corn', 'ZS':'Soybeans', 'ZW':'Wheat'}
#commodities = ['AK', 'CL', 'HO', 'FC', 'LH', 'LC', 'AD', 'BP', 'KC', 'YM', 'ES', 'NQ', 'GC', 'SI', 'PL', 'HG', 'PA', 'RB', 'NG', 'SB', 'KC', 'CT']
# commodities = ['US']#, 'OJ', 'PA', 'SI', 'YM']
#commodityyear = {'AK':2005, 'WS':2008, 'HO':1979, 'FC':1972, 'LH':1970, 'LC':1976, 'AD':1987, 'BP':1974}
commodities = ['ZC', 'ZS', 'ZW']

session = requests.Session()
out = []

for commodity in commodities:
    firstyear = 2022
    months = getmonths(commodity)
    for year in range(firstyear,2023):
#    for year in range(2019, 2020):
        for month in months: #['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']:
            print(commodity + ' ' + str(year) + ' - ' + month)
            monthsymbol = monthdesc[month.lower()]
            if 2000 <= year <= 2009:
                shortyear = year - 2000
                symbol = commodity + monthsymbol + str(0) + str(shortyear)
            elif year >= 2010:
                shortyear = year - 2000
                symbol = commodity + monthsymbol + str(shortyear)
            else:
                shortyear = year - 1900
                symbol = commodity + monthsymbol + str(shortyear)
            
            
            apicall=session.get("http://ondemand.websol.barchart.com/getHistory.json?apikey=2d8b3b803594b13e02a7dc827f4a63f8&symbol=" + symbol + "&type=daily&maxRecords=36500")
                
            data=json.loads(apicall.text)['results'] #[2]['close'] #get data in dict format
            try:
                for i in range(len(data)):   
                    split = data[i]['tradingDay'].split('-',2)
                    tradingday = datetime.date(int(split[0]), int(split[1]), int(split[2]))
                    startday = datetime.date(year-1, monthcodes.index(monthsymbol), 15)
                    endday = datetime.date(year, monthcodes.index(monthsymbol), 1)
                    if startday <= tradingday < endday: # and tradingday >= datetime.date(2019,4,18):
                        values.append(((commoditydesc[commodity], month, year, symbol, tradingday, data[i]['open'], data[i]['high'], data[i]['low'], data[i]['close'], data[i]['volume'], data[i]['openInterest'])))
#                        cur.execute("""delete from MarketHistory where Date >= %s and crop = %s and contract = %s""", (tradingday, commoditydesc[commodity], month))
            except Exception as e:
                print(e)
                continue
        cur.executemany("""insert into MarketHistory (Crop, Contract, Year, Symbol, Date, Open, High, Low, Close, Vol, OI) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""", values)
        db.commit()
        values = []