import pandas as pd
import numpy as np
import json
from six import StringIO
from jqdata import *
from jqdata import finance
pd.set_option('display.max_colwidth',3000)
pd.set_option('display.max_columns', 500)
pd.set_option('display.max_rows', 5000)

#查询数据
end_date='2019-01-04' # 查询截止日期
count=5 # 查询的天数
stock_list=['600000.XSHG',
            
           ]  # 查询的股票列表（自己需要经常观察的股票）

# 获得财务与价格、成交量数据（panel格式）
df_all,df_yeji=get_data(end_date,count,stock_list)


#查看个股数据
stock='600000.XSHG'

df_all_stock=df_all.major_xs(stock)  # 查询个股财务与价格、成交量数据
print(df_all_stock) 

# 各类数据获取函数


def get_price_s(stock_list,end_date,count=10):


    price=get_price(stock_list, None, end_date, '1d', ['close','volume'],skip_paused = False, fq = 'pre', count =count)
    df_price=price['close']
    #print(df_price)
    df_volume=price['volume']
    #print(df_volume)

    return price



#获取多日股票财务数据
def get_fund(stock_list,end_date,count=10):
    q=query(
            income.code,
              #income.np_parent_company_owners,#净利润
              #ncome.basic_eps,
              indicator.eps,#每股收益
              valuation.pe_ratio,# pe
              valuation.pb_ratio,#pb
              indicator.inc_net_profit_year_on_year,#净利润同比增长率
              indicator.inc_revenue_year_on_year,#营业收入同比增长率'
              indicator.roe,#roe
              indicator.gross_profit_margin,#销售毛利率
              balance.total_assets, # 总资产
              balance.total_liability,#总负债
              indicator.ocf_to_operating_profit,#净现金/利润比
             ).filter(valuation.code.in_(stock_list))


    df= get_fundamentals_continuously(q, end_date, count=count)

    df=df.rename({
                    'np_parent_company_owners': '净利润',
                    'eps': '每股收益',
                    'pe_ratio': 'PE',
                     'inc_net_profit_year_on_year': '净利润同比增长率',
                    'inc_revenue_year_on_year':'营业收入同比增长率',
                    'roe':'ROE',
                     'pb_ratio':'PB',
        #'total_assets':'总资产',
        #'total_liability':'总负债',
                    'ocf_to_operating_profit': '净现金/利润比',
                    'gross_profit_margin': '销售毛利率'

                        })


    df['资产负债率']=df['total_liability']/df['total_assets']*100
    del df['total_liability']
    del df['total_assets']
    del df['code.1']
    return df


# 获取融资融券与买卖盘数据
def get_money(stock_list,end_date,count=5):
    df_mtss=get_mtss(stock_list, start_date=None, end_date=end_date,count=count)
    df_mtss['融资融券余额变化率']=df_mtss['fin_sec_value'].pct_change()
    df_mtss['融资净买入']=df_mtss['fin_buy_value']-df_mtss['fin_refund_value']
    df_mtss['融券净卖出入']=df_mtss['sec_sell_value']-df_mtss['sec_refund_value']
    df_mtss=df_mtss.drop([ 'fin_value','fin_buy_value','fin_refund_value','sec_value',
                          'sec_sell_value','sec_refund_value'],axis=1)

    df_mtss=df_mtss.rename(columns={
                         'fin_value': '融资余额(元)',
                        'fin_buy_value': '融资买入额(元)',
                        'fin_refund_value': '融资偿还额(元)',
                         'sec_value': '融券余额(元)',
                        'sec_sell_value': '融券卖出额(元)',
                         'sec_refund_value': '融券偿还额(元)',
                        'fin_sec_value': '融资融券余额(元)',
                            })
    
    
    # 添加融券数据
    df_cash = get_money_flow(stock_list,None,end_date,fields=["date", "sec_code", "change_pct", 
                                                              "net_amount_main", 
                                                              #"net_pct_l",
                                                              #"net_amount_m"
                                                             ]
                                                                 ,count=count)
    df_cash=df_cash.rename(columns={
                                #'date':'日期',
                                #'sec_code':'股票代码',
                                'change_pct':'股价涨跌幅(%)',
                                'net_amount_main':'主力净额(万)', #主力净额 = 超大单净额 + 大单净额
                                'net_pct_main':'主力净占比(%)',  #主力净占比 = 主力净额 / 成交额
                                'net_amount_xl':'超大单净额(万)',#超大单：大于等于50万股或者100万元的成交单
                                'net_pct_xl':'超大单净占比(%)',
                                'net_amount_l':'大单净额(万)',
                                'net_pct_l':'大单净占比(%)',
                                'net_amount_m':'中单净额(万)',
                                'net_pct_m':'中单净占比(%)',
                                'net_amount_s':'小单净额(万)',
                                'net_pct_s':'小单净占比(%)',
                            })

    
    # 合并融券与资金数据
    df_money=pd.merge(df_cash,df_mtss, how='outer',on=['date','sec_code'])

    df_money=df_money.sort_index(by=["sec_code"],ascending = [False])  
    df_money=df_money.set_index(["sec_code","date"])
    #df_money=df_money.set_index("sec_code")
    #print(df_money)

    #p_df_money=df_money.to_panel()

    
    return df_money



# 合并数据

def get_data(end_date,count,stock_list):

    #价格与成交量数据
    price=get_price_s(stock_list,end_date,count)

    #财务数据
    df=get_fund(stock_list,end_date,count)
    df=df.rename_axis(None, axis=1, copy=True, inplace=False)
    df=df.rename_axis(None, axis=2, copy=True, inplace=False)
    #print(df.to_frame())

    #合并数据
    df_all=pd.concat([price,df])
    df_all=df_all.transpose(0, 2, 1)

    #print(df_all.to_frame())


    #融资融券数据
    df_money=get_money(stock_list,end_date,count)
    #print(df_money)


    # 业绩预告
    df_yeji=finance.run_query(query(finance.STK_FIN_FORCAST).filter(finance.STK_FIN_FORCAST.pub_date>='2018-10-01',finance.STK_FIN_FORCAST.code.in_(stock_list)).limit(1000))
    #print(df_yeji)
    
    # 合并融资融券数据与财务数据、价格成交量数据
    df_money=df_money.fillna('空值')
    p_df_money=df_money.to_panel()

    p_df_money=p_df_money.rename_axis(None, axis=1, copy=True, inplace=False)
    p_df_money=p_df_money.rename_axis(None, axis=2, copy=True, inplace=False)

    df_all_all=pd.concat([df_all,p_df_money])
   
    
    return df_all_all,df_yeji