import pandas as pd
from jqdatasdk import *
auth('18616891214', 'Ea?*7f68nD.dafcW34d!')

import threading
from sqlalchemy import create_engine
import pymysql
from datetime import datetime as dt

start = dt.now()
# 确定级别
fre = '1d'
# 连接数据库
db = pymysql.connect(host='localhost',
                     user='root',
                     port=3307,
                     password='r6kEwqWU9!v3',
                     database='hlfx')
# engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8')
engine2 = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/hlfx?charset=utf8')

# 获取所有表名——确定计算范围
cursor = db.cursor()
cursor.execute("show tables like '%%%s%%' "% fre)
# stocks = [tuple[0] for tuple in cursor.fetchall()]
stocks = list(get_all_securities(['stock'], date='2021-12-31').index)
# stocks = ['301058.XSHE']
# stocks = stocks[0:500]
print(dt.now(), 'stocks范围已获取！')

# 获取各stock的去包含dataframe
stk = locals()
for stock in stocks:
    try:
        stk[stock] = pd.read_sql_query('select date,open,close,high,low,volume,money,HL from `stk%s_%s`' % (stock, fre),
                                        engine2)
    except BaseException:
        continue



print(dt.now(), '数据库数据已赋值！')

thd = threading.local()


def qbh_hlfx(stocks, df):
    for stock in stocks:
        try:
            # thd.new_df = pd.DataFrame(columns=('date', 'open', 'close', 'high', 'low', 'volume', 'money', 'HL'))
            thd.df_day = stk[stock]
            thd.get_bars = df.loc[stock]
            stk_len = len(thd.df_day)
            # 先处理去包含
            for i in thd.get_bars.index:
                # 不包含
                if (thd.df_day.iloc[-1, 3] > thd.get_bars.loc[i, 'high']
                    and thd.df_day.iloc[-1, 4] > thd.get_bars.loc[i, 'low']) \
                        or (thd.df_day.iloc[-1, 3] < thd.get_bars.loc[i, 'high']
                            and thd.df_day.iloc[-1, 4] < thd.get_bars.loc[i, 'low']):
                    thd.df_day = pd.concat([thd.df_day, thd.get_bars.iloc[[i]]], ignore_index=True)
                # 包含
                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_bars.loc[i, 'high'])
                        thd.df_day.iloc[-1, 4] = min(thd.df_day.iloc[-1, 4], thd.get_bars.loc[i, 'low'])
                    else:
                        # 右高，上升
                        thd.df_day.iloc[-1, 3] = max(thd.df_day.iloc[-1, 3], thd.get_bars.loc[i, 'high'])
                        thd.df_day.iloc[-1, 4] = max(thd.df_day.iloc[-1, 4], thd.get_bars.loc[i, 'low'])
        # return thd.df_day
            if len(thd.df_day.index) > 2:
                # 寻找顶底分型
                for x in range(stk_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'] = '-'
                    print(thd.df_day)
            else:
                pass
        except BaseException:
            continue


# while True:
df = get_bars(stocks, count=2, unit=fre,
              fields=['date', 'open', 'close', 'high', 'low', 'volume', 'money'], include_now=True, df=True)
print(dt.now(), 'get_bars 成功')
# strattime = dt.now()
qbh_hlfx(stocks, df)
# endtime = dt.now()


# end = dt.now()
# print('单次时长为：', (endtime - strattime).seconds, '\n', '全时长为：', (end - start).seconds)