# coding:utf-8
from datetime import datetime as dt
import os
import pandas as pd
import pymysql
from xtquant.xttrader import XtQuantTrader, XtQuantTraderCallback
from xtquant.xttype import StockAccount
from xtquant import xtdata, xtconstant
from sqlalchemy import create_engine

pd.set_option('display.max_columns', None) # 设置显示最大行

df = pd.DataFrame(columns=['time', 'open', 'close', 'high', 'low', 'volume', 'amount'])
field = ['time', 'open', 'close', 'high', 'low', 'volume', 'amount']
eng_w = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/qihuo?charset=utf8',)


def avg_price(df):
    print('avg_price')
    daily_avg_price = []
    for i in range(1, len(df) + 1):
        daily_avg_price.append(round(sum(df['amount'][:i]) / sum(df['volume'][:i]) / 10, 2))
    df['daily_avg_price'] = daily_avg_price
    return df


def over_avg(datas):
    global df
    for stock_code in datas:
        df_temp = pd.DataFrame(datas[stock_code])
        df_temp['time'] = df_temp['time'].apply(lambda x: dt.fromtimestamp(x / 1000.0))
        df = pd.concat([df, df_temp], axis=0, ignore_index=True)
        df = avg_price(df)
        df = df[['time', 'open', 'close', 'high', 'low', 'volume', 'amount', 'daily_avg_price']]
        print(df)


if __name__ == '__main__':
    print('start')
    xtdata.subscribe_quote('rb00.SF', '1d', '', '')
    xtdata.download_history_data('rb00.SF', '1d', '', '')

    data = xtdata.get_market_data_ex([], ['rb00.SF'], period='1d')
    print(type(data['rb00.SF']))
    exit()
    db_pool = pymysql.connect(host='localhost',
                              user='root',
                              port=3307,
                              password='r6kEwqWU9!v3',
                              database='qihuo')
    cursor_pool = db_pool.cursor()
    data['rb00.SF'].to_sql('rb00_1d' , con=eng_w, index=False, if_exists='replace', chunksize=20000)

    exit()

    fts = xtdata.get_stock_list_in_sector('中金所主力合约')
    print(fts, len(fts))

    fts_list = ['IF00.CFFEX', 'IH00.CFFEX', 'IC00.CFFEX', 'IO00.CFFEX']
    # t = dt.now().strftime('%Y%m%d')
    s = 'IF00.CFFEX'
    new_s ='IF00.IF'

    data = xtdata.get_market_data_ex([], ['IF00.IF', 'RB01.SHFE'], period='1d', end_time='', count=-1)
    df = data['IF00.IF'][['open', 'close', 'high', 'low', 'volume', 'amount']].copy()
    print('df', df)
    df2 = data['RB01.SHFE'][['open', 'close', 'high', 'low', 'volume', 'amount']].copy()
    print('df2', df2)
    df2['daily_point'] = df2['close'] - df2['open']
    # 计算每日波动点数，开盘-收盘
    df['daily_point'] = df['close'] - df['open']
    # 选取需要的字段
    print(df.describe())

    # 描述统计信息
    describe_stats = df['daily_point'].describe()

    # 正数的平均值
    positive_mean = df[df['daily_point'] > 0]['daily_point'].mean()

    # 负数的平均值
    negative_mean = df[df['daily_point'] < 0]['daily_point'].mean()

    # 绝对值的平均值
    absolute_mean = df['daily_point'].abs().mean()

    # 分位数分布
    percentiles = df['daily_point'].quantile([0.25, 0.5, 0.75])

    # 统计正数的数量
    positive_count = len(df[df['daily_point'] > 0])

    # 统计负数的数量
    negative_count = len(df[df['daily_point'] < 0])

    print("统计信息：\n", describe_stats)
    print("正数的平均值：", positive_mean)
    print("负数的平均值：", negative_mean)
    print("绝对值的平均值：", absolute_mean)
    print("25%、50%、75% 分布：\n", percentiles)
    print("正数的数量：", positive_count)
    print("负数的数量：", negative_count)