# -*- coding: utf-8 -*-
import pandas as pd
import tushare as ts
import lxml
from redis import Redis
import hashlib
import pymysql
import time

'''
公司基本信息获取
'''
# =============================================================股票列表:stock_basic=============================================================
def stockBasic(**kwargs):
    # 定义计数器
    success, fail, count = 0, 0, 0

    # 创建空列表
    code_list = []

    # 创建MySQL操作游标
    cursor = conn_mysql.cursor()

    stock_basic = pro.stock_basic(list_status='L', fields='ts_code,name,area,exchange,list_date')
    for index, row in stock_basic.iterrows():
        stk_code = row['ts_code']  # 股票代码
        stk_name = row['name']  # 股票名称
        city = row['area']  # 所属城市
        exchange = row['exchange']  # 所属交易所
        list_date = row['list_date']  # 上市日期

        code_list.append(stk_code)

        # 将解析到的stk_code生成一个唯一的标识进行redis存储
        source = stk_code
        stk_code_id = hashlib.sha256(source.encode()).hexdigest()

        # 将解析内容的唯一表示存储到redis的stk_code中
        ex = conn_redis.sadd('stk_code', stk_code_id)

        # 获取没有爬取的内容
        if ex == 1:
            try:
                sql = '''insert into stock_basic_data(stk_code,stk_name,city,exchange,list_date) values('%s','%s','%s','%s','%s')''' % (stk_code,stk_name,city,exchange,list_date)
                cursor.execute(sql)
                conn_mysql.commit()

                # 统计入库成功数量
                success += 1

            except:
                conn_mysql.rollback()

                # 统计入库失败数量
                fail += 1

        # 统计请求数据量
        count +=1

    # 关闭MySQL操作游标
    cursor.close()
    print('stockBasic程序执行完毕，新入库数据：%d条' % success)
    print('stockBasic程序执行完毕，入库失败数据：%d条' % fail)
    print('stockBasic程序执行完毕，本次共请求数据：%d条' % count)
    return code_list

def stockBk():
    pass

def stockConcept():
    pass







'''
每日基础交易数据
'''


# =============================================================每日指标:daily_basic=============================================================
def dailyBasic(**kwargs):
    '''
    交易日每日15点～17点之间更新
    '''

    # 定义计数器
    success, fail, count = 0, 0, 0

    # 创建MySQL操作游标
    cursor = conn_mysql.cursor()

    for offset in range(0, 12000, 1000):
        daily_basic = pro.daily_basic(trade_date=cur_date,fields='ts_code,trade_date,turnover_rate_f,volume_ratio,pe_ttm,pb,ps_ttm,dv_ttm,total_share,float_share,total_mv,circ_mv',offset=offset, limit=1000)
        for index, row in daily_basic.iterrows():
            stk_code = row['ts_code']  # 股票代码
            trade_date = row['trade_date']  # 交易日期
            turnover_rate_f = row['turnover_rate_f']  # 实际换手率
            volume_ratio = row['volume_ratio']  # 量比
            pe_ttm = row['pe_ttm']  # 动态市盈率，总市值/净利润
            pb = row['pb']  # 市净率，总市值/净资产
            ps_ttm = row['ps_ttm']  # 动态市销率
            dv_ttm = row['dv_ttm']  # 动态股息率，原始单位：%
            total_share = row['total_share']  # 总股本，单位：万股
            float_share = row['float_share']  # 流通股本，单位：万股
            total_mv = row['total_mv']  # 总市值，万元
            circ_mv = row['circ_mv']  # 流通市值，万元

            try:
                sql = '''insert into daily_basic_data(ts_code,trade_date,turnover_rate_f,volume_ratio,pe_ttm,pb,ps_ttm,dv_ttm,total_share,float_share,total_mv,circ_mv) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')''' % (ts_code, trade_date, turnover_rate_f, volume_ratio, pe_ttm, pb, ps_ttm, dv_ttm, total_share,float_share, total_mv, circ_mv)
                cursor.execute(sql)
                conn_mysql.commit()

                # 统计入库成功数量
                success += 1
            except:
                conn_mysql.rollback()

                # 统计入库失败数量
                fail += 1

            # 统计请求数据量
            count += 1

    # 关闭MySQL操作游标
    cursor.close()
    print('dailyData程序执行完毕，新入库数据：%d条' % success)
    print('dailyData程序执行完毕，入库失败数据：%d条' % fail)
    print('dailyData程序执行完毕，本次共请求数据：%d条' % count)

# =============================================================个股资金流向：moneyflow=============================================================
def moneyFlow(cur_date):
    '''
    单次最大提取4000行记录，总量不限制
    '''
    for offset in range(0, 12000, 1000):
        money_flow = pro.moneyflow(trade_date=cur_date,
                          fields='ts_code,trade_date,buy_lg_amount,sell_lg_amount,buy_elg_amount,sell_elg_amount,net_mf_amount',
                          offset=offset, limit=1000)
        for index, row in money_flow.iterrows():
            stk_code = row['ts_code']  # 股票代码
            trade_date = row['trade_date']  # 交易日期
            buy_lg_amount = row['buy_lg_amount']  # 大单买入金额（万元）20万～100万
            sell_lg_amount = row['sell_lg_amount']  # 大单卖出金额（万元）20万～100万
            buy_elg_amount = row['buy_elg_amount']  # 特大单买入金额（万元）成交额>=100万
            sell_elg_amount = row['sell_elg_amount']  # 特大单卖出金额（万元）成交额>=100万
            net_mf_amount = row['pct_chg']  # 净流入额（万元）

# =============================================================指数日线行情index_daily=============================================================
def indexDaily(cur_date):
    for offset in range(0, 12000, 1000):
        idx_data = pro.index_daily(trade_date, fields='ts_code,trade_date,close,open,high,low,pre_close,pct_chg,amount', offset=offset, limit=1000)
        for index, row in idx_data.iterrows():
            idx_code = row['ts_code']  # 指数代码
            trade_date = row['trade_date'] # 交易日期
            open = row['open']  # 开盘价
            high = row['high']  # 最高价
            low = row['low']  # 最低价
            close = row['close']  # 收盘价
            pre_close = row['pre_close']  # 昨收价
            pct_chg = round(row['pct_chg'],2)  # 涨跌幅，单位%
            amount = round(row['amount']/10,2)  # 成交额（千元）

'''
每日特色交易数据
'''

# ============================================================每日涨跌停统计:limit_list=============================================================
def limitData(cur_date):
    '''
    单次最大1000，总量不限制
    '''
    for offset in range(0, 12000, 500):
        limit_data = pro.limit_list(trade_date=cur_date,
                          fields='ts_code,trade_date,fc_ratio,fd_amount,first_time,last_time,open_times,strth,limit',
                          offset=offset, limit=1000)
        for index, row in limit_data.iterrows():
            stk_code = row['ts_code']  # 股票代码
            trade_date = row['trade_date']  # 交易日期
            fc_ratio = row['fc_ratio']  # 封单金额/日成交金额
            fd_amount = row['fd_amount']  # 封单金额
            first_time = row['close']  # 首次涨停时间
            last_time = row['last_time']  # 最后封板时间
            open_times = row['open_times']  # 打开次数
            strth = row['strth']  # 涨跌停强度
            limit = row['limit']  # D跌停U涨停

# =============================================================龙虎榜机构明细:top_inst=============================================================
def topInst(cur_date):
    '''
    单次最大10000
    '''
    for offset in range(0, 12000, 1000):
        top_detail = pro.top_inst(trade_date=cur_date, fields='ts_code,trade_date,exalter,buy,sell,net_buy', offset=offset, limit=1000)
        for index, row in top_detail.iterrows():
            stk_code = row['ts_code']  # 股票代码
            trade_date = row['trade_date']  # 交易日期
            exalter = row['exalter']  # 营业部名称
            buy = row['low']  # 买入额（万）
            sell = row['sell']  # 卖出额（万）
            net_buy = row['net_buy']  # 净成交额（万）




# ================================================================主函数================================================================
def main():
    # 初始化tushare.pro接口
    pro = ts.pro_api('ac16b470869c5d82db5033ae9288f77b282d2b5519507d6d2c72fdd7')

    # 获取当天日期
    cur_date = time.strftime("%Y%m%d", time.localtime())

    # 创建MySQL连接对象
    conn_mysql = pymysql.connect(user='root', password='123456', database='stock', charset='utf8')

    # 创建Redis链接对象
    conn_redis = Redis(host='127.0.0.1', port=6379)

    code_list = stockBasic(conn_mysql,conn_redis)

    conn_mysql.close()

if __name__ == '__main__':
    main()