from jqdatasdk import *
import pandas as pd
import pymysql
from sqlalchemy import create_engine
import threading
from datetime import datetime as dt
import datetime

auth('18019403367', 'Qwer4321')

stocks = list(get_all_securities(['stock'], date=dt.today().strftime('%Y-%m-%d')).index)
stocks = stocks[0:1]

start = dt.now()
# 确定级别
# 注意修改time delta
fre = '1d'
# 连接数据库
db = pymysql.connect(host='localhost',
                     user='root',
                     port=3307,
                     password='r6kEwqWU9!v3',
                     database='hlfx')
engine2 = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/hlfx?charset=utf8')
engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8')
cursor = db.cursor()
cursor.execute('show tables like {}'.format('\'%{}\''.format(fre)))
table_list = [tuple[0] for tuple in cursor.fetchall()]

print(table_list)
stk = locals()
thd = threading.local()

for stock in stocks:
    print(stock)
    if ('stk%s_%s' % (stock, fre)) in table_list:
        # 有历史数据
        index_len = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine2).iloc[-1, 0]
        # 注意修改time delta
        startdate = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine2).iloc[-1, 1]
        # startdate = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine2).iloc[-1, 1] + datetime.timedelta(minutes= 5)
        thd.get_price = pd.read_sql_query(
            'select date,open,close,high,low,volume,money from `stk%s_%s`' % (stock, fre), engine)
        thd.get_price = thd.get_price.loc[thd.get_price['date'] > startdate]
        thd.df_day = pd.read_sql_query(
            'select date,open,close,high,low,volume,money,HL from `stk%s_%s`' % (stock, fre), engine2)
        # 先处理去包含
        for i in thd.get_price.index:
            # 不包含
            if (thd.df_day.iloc[-1, 3] > thd.get_price.loc[i, 'high']
                and thd.df_day.iloc[-1, 4] > thd.get_price.loc[i, 'low']) \
                    or (thd.df_day.iloc[-1, 3] < thd.get_price.loc[i, 'high']
                        and thd.df_day.iloc[-1, 4] < thd.get_price.loc[i, 'low']):
                thd.df_day = pd.concat([thd.df_day, thd.get_price.loc[[i]]], ignore_index=True)
                print(thd.df_day)
            # 包含
            else:
                # (new_df.iloc[-1,3]>=df_day.loc[i,'high'] and new_df.iloc[-1,4]<= df_day.loc[i,'low']):
                # 左高，下降
                if thd.df_day.iloc[-2, 3] > thd.df_day.iloc[-1, 3]:
                    thd.df_day.iloc[-1, 3] = min(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
                    thd.df_day.iloc[-1, 4] = min(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
                else:
                    # 右高，上升
                    thd.df_day.iloc[-1, 3] = max(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
                    thd.df_day.iloc[-1, 4] = max(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
                    # 寻找顶底分型
        if len(thd.df_day.index) > 2:
            # 寻找顶底分型
            for x in range(index_len, len(thd.df_day.index)):
                m = x - 1
                # 底
                if ((thd.df_day.loc[x, 'high'] > thd.df_day.loc[x - 1, 'high']) and (
                        thd.df_day.loc[x - 2, 'high'] > thd.df_day.loc[x - 1, 'high'])):
                    # if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
                    # stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
                    thd.df_day.loc[x, 'HL'] = 'L*'
                    while m:
                        if thd.df_day.loc[m, 'HL'] == 'H':
                            if (x - m) > 3:
                                thd.df_day.loc[x, 'HL'] = 'L'
                                if x == len(thd.df_day.index) - 1:
                                    print(stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '买买买买买！！')
                            break
                        elif (thd.df_day.loc[m, 'HL'] == 'L'):
                            if thd.df_day.loc[x - 1, 'low'] < thd.df_day.loc[m - 1, 'low']:
                                # 前一个为底，且中间存在不包含 or 更低的底
                                thd.df_day.loc[x, 'HL'] = 'L'
                                if x == len(thd.df_day.index) - 1:
                                    # pass
                                    print(stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '中继后的底————买吗？！')
                                break
                            else:
                                break
                        m = m - 1
                # 顶
                elif ((thd.df_day.loc[x, 'high'] < thd.df_day.loc[x - 1, 'high']) and (
                        thd.df_day.loc[x - 2, 'high'] < thd.df_day.loc[x - 1, 'high'])):
                    # if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
                    #     stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
                    thd.df_day.loc[x, 'HL'] = 'H*'
                    while m:
                        if thd.df_day.loc[m, 'HL'] == 'L':
                            if x - m > 3:
                                thd.df_day.loc[x, 'HL'] = 'H'
                                if x == len(thd.df_day.index) - 1:
                                    # print(stock, '!!!!!!!', '\n', '卖卖卖卖卖卖卖!')
                                    pass
                                thd.df_day.loc[x, 9] = thd.df_day.loc[x, 'close'] - thd.df_day.loc[m, 'close']
                            break
                        elif (thd.df_day.loc[m, 'HL'] == 'H'):
                            if thd.df_day.loc[x - 1, 'high'] > thd.df_day.loc[m - 1, 'high']:
                                # 前一个为顶，且中间存在不包含 or 更高的顶
                                thd.df_day.loc[x, 'HL'] = 'H'
                                if x == len(thd.df_day.index) - 1:
                                    pass
                                    # print(stock, '/\/\/\/\/\/\/', '一顶更有一顶高！')
                                break
                            break
                        m = m - 1
                else:
                    thd.df_day.loc[x, 'HL'] = '-'

        # 更新数据库
        thd.df_day[index_len + 1:].to_sql('stk%s_%s' % (stock, fre), con=engine2, index=True, if_exists='append')
    else:
        # 没有历史数据表
        thd.df_day = pd.DataFrame(columns=('date', 'open', 'close', 'high', 'low', 'volume', 'money', 'HL'))
        thd.get_price = pd.read_sql_query(
            'select date,open,close,high,low,volume,money from `stk%s_%s`' % (stock, fre), engine)
        # 先处理去包含
        for i in thd.get_price.index:
            if i == 0 or i == 1:
                thd.df_day = pd.concat([thd.df_day, thd.get_price.iloc[[i]]], ignore_index=True)
            # 不包含
            elif (thd.df_day.iloc[-1, 3] > thd.get_price.loc[i, 'high']
                  and thd.df_day.iloc[-1, 4] > thd.get_price.loc[i, 'low']) \
                    or (thd.df_day.iloc[-1, 3] < thd.get_price.loc[i, 'high']
                        and thd.df_day.iloc[-1, 4] < thd.get_price.loc[i, 'low']):
                thd.df_day = pd.concat([thd.df_day, thd.get_price.loc[[i]]], ignore_index=True)
            # 包含
            else:
                # 左高，下降
                if thd.df_day.iloc[-2, 3] > thd.df_day.iloc[-1, 3]:
                    thd.df_day.iloc[-1, 3] = min(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
                    thd.df_day.iloc[-1, 4] = min(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
                else:
                    # 右高，上升
                    thd.df_day.iloc[-1, 3] = max(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
                    thd.df_day.iloc[-1, 4] = max(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
        if len(thd.df_day.index) > 2:
            # 寻找顶底分型
            for x in range(index_len, len(thd.df_day.index)):
                m = x - 1
                # 底
                if ((thd.df_day.loc[x, 'high'] > thd.df_day.loc[x - 1, 'high']) and (
                        thd.df_day.loc[x - 2, 'high'] > thd.df_day.loc[x - 1, 'high'])):
                    # if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
                    # stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
                    thd.df_day.loc[x, 'HL'] = 'L*'
                    while m:
                        if thd.df_day.loc[m, 'HL'] == 'H':
                            if (x - m) > 3:
                                thd.df_day.loc[x, 'HL'] = 'L'
                                if x == len(thd.df_day.index) - 1:
                                    print(stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '买买买买买！！')
                            break
                        elif (thd.df_day.loc[m, 'HL'] == 'L'):
                            if thd.df_day.loc[x - 1, 'low'] < thd.df_day.loc[m - 1, 'low']:
                                # 前一个为底，且中间存在不包含 or 更低的底
                                thd.df_day.loc[x, 'HL'] = 'L'
                                if x == len(thd.df_day.index) - 1:
                                    # pass
                                    print(stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '中继后的底————买吗？！')
                                break
                            else:
                                break
                        m = m - 1
                # 顶
                elif ((thd.df_day.loc[x, 'high'] < thd.df_day.loc[x - 1, 'high']) and (
                        thd.df_day.loc[x - 2, 'high'] < thd.df_day.loc[x - 1, 'high'])):
                    # if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
                    #     stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
                    thd.df_day.loc[x, 'HL'] = 'H*'
                    while m:
                        if thd.df_day.loc[m, 'HL'] == 'L':
                            if x - m > 3:
                                thd.df_day.loc[x, 'HL'] = 'H'
                                if x == len(thd.df_day.index) - 1:
                                    # print(stock, '!!!!!!!', '\n', '卖卖卖卖卖卖卖!')
                                    pass
                                thd.df_day.loc[x, 9] = thd.df_day.loc[x, 'close'] - thd.df_day.loc[m, 'close']
                            break
                        elif (thd.df_day.loc[m, 'HL'] == 'H'):
                            if thd.df_day.loc[x - 1, 'high'] > thd.df_day.loc[m - 1, 'high']:
                                # 前一个为顶，且中间存在不包含 or 更高的顶
                                thd.df_day.loc[x, 'HL'] = 'H'
                                if x == len(thd.df_day.index) - 1:
                                    pass
                                    # print(stock, '/\/\/\/\/\/\/', '一顶更有一顶高！')
                                break
                            break
                        m = m - 1
                else:
                    thd.df_day.loc[x, 'HL'] = '-'
        # 更新数据库
        thd.df_day.to_sql('stk%s_%s' % (stock, fre), con=engine2, index=True, if_exists='append')