from jqdata import finance

def get_menber_vol_opid(day,code):
    """
    输入日期、代码
    输出9张龙虎榜数据
    """

    import pandas as pd

    #获取数据
    q=query(finance.FUT_MEMBER_POSITION_RANK.day,
            finance.FUT_MEMBER_POSITION_RANK.code,
            finance.FUT_MEMBER_POSITION_RANK.rank_type,
            finance.FUT_MEMBER_POSITION_RANK.rank,
            finance.FUT_MEMBER_POSITION_RANK.member_name,
            finance.FUT_MEMBER_POSITION_RANK.indicator,
            finance.FUT_MEMBER_POSITION_RANK.indicator_increase).\
            filter(finance.FUT_MEMBER_POSITION_RANK.code==code,
                   finance.FUT_MEMBER_POSITION_RANK.day==day)
    df=finance.run_query(q)
    
    #数据分离，得3张表
    df1 = df[df['rank_type']=='成交量排名'][['rank','member_name','indicator','indicator_increase']]
    df2 = df[df['rank_type']=='持买单量排名'][['rank','member_name','indicator','indicator_increase']]
    df3 = df[df['rank_type']=='持卖单量排名'][['rank','member_name','indicator','indicator_increase']]
    
    #-------------------------------------基础龙虎榜数据
    #成交量龙虎榜
    df1.columns = ['名次','会员简称','成交量','增减']
    sum1a = sum(df1['成交量'])
    sum1b = sum(df1['增减'])
    df1 = df1.append(pd.DataFrame(['成交量','本日合计',sum1a,sum1b],index=['名次','会员简称','成交量','增减']).T,sort=False)
    df1 = df1.reset_index(drop=True)
    
    #多头持仓龙虎榜
    df2.columns = ['名次','会员简称','多单量','增减']
    sum2a = sum(df2['多单量'])
    sum2b = sum(df2['增减'])
    df2 = df2.append(pd.DataFrame(['多头','本日合计',sum2a,sum2b],index=['名次','会员简称','多单量','增减']).T,sort=False)
    df2 = df2.reset_index(drop=True)
    
    #空头持仓龙虎榜
    df3.columns = ['名次','会员简称','空单量','增减']
    sum3a = sum(df3['空单量'])
    sum3b = sum(df3['增减'])
    df3 = df3.append(pd.DataFrame(['空头','本日合计',sum3a,sum3b],index=['名次','会员简称','空单量','增减']).T,sort=False)
    df3 = df3.reset_index(drop=True)
    
    #-----------------------------------计算持仓龙虎榜
    #持仓数据
    temp1 = df2[['会员简称','多单量']].set_index('会员简称')
    temp1.columns = ['持仓']
    temp2 = df3[['会员简称','空单量']].set_index('会员简称')
    temp2.columns = ['持仓']
    opid = temp1.iloc[:-1,:]-temp2.iloc[:-1,:]  #最后一行的本日合计不参与
    
    #增减数据
    temp3 = df2[['会员简称','增减']].set_index('会员简称')
    temp4 = df3[['会员简称','增减']].set_index('会员简称')
    opid_inc = temp3.iloc[:-1,:]-temp4.iloc[:-1,:]
    
    #合并持仓、增减
    #注意东方财富的结果不对（如果多头持仓龙虎榜中有，而空头持仓龙虎榜中没有，东财直接把多持仓龙虎榜中持仓当作净持仓）
    opid_result = pd.concat([opid,opid_inc],axis=1).sort_values(by='持仓',ascending=False)
    
    #---------净多头龙虎榜
    df4 = opid_result[opid_result['持仓']>0]
    temp1 = sum(df4['持仓'])
    temp2 = sum(df4['增减'])
    df4 = df4.reset_index()
    df4['名次']=range(1,len(df4)+1)
    df4 = df4[['名次','会员简称','持仓','增减']]
    #插入空行
    insert_row=pd.DataFrame([[' ',' ',' ',' ']],columns=['名次','会员简称','持仓','增减'])
    for i in range(20-len(df4)):
        df4=df4.append(insert_row,ignore_index=True)
    df4 = df4.append(pd.DataFrame(['净多','本日合计',temp1,temp2],index=['名次','会员简称','持仓','增减']).T,sort=False)
    df4 = df4.reset_index(drop=True)
    df4.columns=['名次','会员简称','净多量','增减']
    
    #---------净空头龙虎榜
    df7 = opid_result[opid_result['持仓']<0]
    df7 = 0-df7
    df7 = df7.sort_values(by='持仓',ascending=False)
    
    temp1 = sum(df7['持仓'])
    temp2 = sum(df7['增减'])
    df7 = df7.reset_index()
    df7['名次']=range(1,len(df7)+1)
    df7 = df7[['名次','会员简称','持仓','增减']]
    #插入空行
    insert_row=pd.DataFrame([[' ',' ',' ',' ']],columns=['名次','会员简称','持仓','增减'])
    for i in range(20-len(df7)):
        df7=df7.append(insert_row,ignore_index=True)
    df7 = df7.append(pd.DataFrame(['净空','本日合计',temp1,temp2],index=['名次','会员简称','持仓','增减']).T,sort=False)
    df7 = df7.reset_index(drop=True)
    df7.columns=['名次','会员简称','净空量','增减']
    
    
    #----------------多头增减仓龙虎榜
    import warnings
    warnings.filterwarnings('ignore')
    #多头增仓龙虎榜
    temp = df2.iloc[:-1,:].sort_values(by='增减',ascending=False)
    df5 = temp[temp['增减']>0]
    
    temp1 = sum(df5['多单量'])
    temp2 = sum(df5['增减'])
    
    df5.loc[:,'名次']=range(1,len(df5)+1)
    #插入空行
    insert_row=pd.DataFrame([[' ',' ',' ',' ']],columns=['名次','会员简称','多单量','增减'])
    for i in range(20-len(df5)):
        df5=df5.append(insert_row,ignore_index=True)
    df5 = df5.append(pd.DataFrame(['多增','本日合计',temp1,temp2],index=['名次','会员简称','多单量','增减']).T,sort=False)
    df5 = df5.reset_index(drop=True)
    df5 = df5[['名次','会员简称','增减','多单量']]
    
    #多头减仓龙虎榜
    temp = df2.iloc[:-1,:].sort_values(by='增减')
    df6 = temp[temp['增减']<0]
    
    temp1 = sum(df6['多单量'])
    temp2 = sum(df6['增减'])
    
    df6.loc[:,'名次']=range(1,len(df6)+1)
    #插入空行
    insert_row=pd.DataFrame([[' ',' ',' ',' ']],columns=['名次','会员简称','多单量','增减'])
    for i in range(20-len(df6)):
        df6=df6.append(insert_row,ignore_index=True)
    df6 = df6.append(pd.DataFrame(['多减','本日合计',temp1,temp2],index=['名次','会员简称','多单量','增减']).T,sort=False)
    df6 = df6.reset_index(drop=True)
    df6 = df6[['名次','会员简称','增减','多单量']]
    
    
    #---------------空头增减仓龙虎榜
    #空头增仓龙虎榜
    temp = df3.iloc[:-1,:].sort_values(by='增减',ascending=False)
    df8 = temp[temp['增减']>0]
    
    temp1 = sum(df8['空单量'])
    temp2 = sum(df8['增减'])
    
    df8.loc[:,'名次']=range(1,len(df8)+1)
    #插入空行
    insert_row=pd.DataFrame([[' ',' ',' ',' ']],columns=['名次','会员简称','空单量','增减'])
    for i in range(20-len(df8)):
        df8=df8.append(insert_row,ignore_index=True)
    df8 = df8.append(pd.DataFrame(['空增','本日合计',temp1,temp2],index=['名次','会员简称','空单量','增减']).T,sort=False)
    df8 = df8.reset_index(drop=True)
    df8 = df8[['名次','会员简称','增减','空单量']]
    
    #空头减仓龙虎榜
    temp = df3.iloc[:-1,:].sort_values(by='增减')
    df9 = temp[temp['增减']<0]
    
    temp1 = sum(df9['空单量'])
    temp2 = sum(df9['增减'])
    
    df9.loc[:,'名次']=range(1,len(df9)+1)
    #插入空行
    insert_row=pd.DataFrame([[' ',' ',' ',' ']],columns=['名次','会员简称','空单量','增减'])
    for i in range(20-len(df9)):
        df9=df9.append(insert_row,ignore_index=True)
    df9 = df9.append(pd.DataFrame(['空减','本日合计',temp1,temp2],index=['名次','会员简称','空单量','增减']).T,sort=False)
    df9 = df9.reset_index(drop=True)
    df9 = df9[['名次','会员简称','增减','空单量']]
    
    #-------------------------合并9个龙虎榜
    all_data = pd.concat([df1,df2,df3,df4,df5,df6,df7,df8,df9],axis=1)
    columns1 = all_data.columns
    all_data=pd.DataFrame(all_data.values,columns=[['成交量龙虎榜']*4+\
                                                   ['多头持仓龙虎榜']*4+\
                                                   ['空头持仓龙虎榜']*4+\
                                                   ['净多头龙虎榜']*4+\
                                                   ['多头增仓龙虎榜']*4+\
                                                   ['多头减仓龙虎榜']*4+\
                                                   ['净空头龙虎榜']*4+\
                                                   ['空头增仓龙虎榜']*4+\
                                                   ['空头减仓龙虎榜']*4,columns1])
    filename = day+'日'+code+'会员成交持仓龙虎榜数据.xlsx'
    all_data.to_excel(filename)
    return all_data


def test1():
    #大连品种
    code = 'M1909.XDCE'
    day = '2019-04-19'
    all_data = get_menber_vol_opid(day,code)
    return all_data

def test2():
    code = 'RB1905.XSGE'
    day = '2019-02-13'
    all_data = get_menber_vol_opid(day,code)
    return all_data

if __name__=='__main__':
    all_data = test1()
    #all_data2 = test2()
    

all_data