Python MSSQL.dict_sql方法代码示例

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def order_get_ac_ratio_three(account):
	#获取总账户配置的虚拟组的ratio
	ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
	#sql="WITH Emp AS ( SELECT ac,F_ac,ratio FROM  [LogRecord].[dbo].[order_p_follow] WHERE   ac='%s' UNION ALL  SELECT   D.AC,D.F_ac,D.ratio*emp.ratio/100 FROM   Emp         INNER JOIN [LogRecord].[dbo].[order_p_follow] d ON d.ac = Emp.F_ac)SELECT AC,f_AC,ratio FROM  Emp" % (account)
	#sql="select AC,f_AC,ratio from [LogRecord].[dbo].[order_p_follow] where ac='%s'" % (account)
	#检测是否自循环
	sql="select * from [Future].[dbo].[p_follow]  where ac=F_ac and ac='%s'" % (account)
	res=ms.dict_sql(sql)
	if res:
		return []
	sql="WITH Emp AS ( SELECT ac,F_ac,ratio,stock FROM  [Future].[dbo].[p_follow] WHERE   ac='%s' UNION ALL  SELECT   D.AC,D.F_ac,D.ratio*emp.ratio/100,D.stock FROM   Emp         INNER JOIN [Future].[dbo].[p_follow] d ON d.ac = Emp.F_ac)     select '%s' as AC,f_AC+'__'+stock as f_AC,SUM(ratio) as ratio from Emp where  f_ac not in (select ac from Emp)  and ratio<>0 group by F_ac,stock order by F_ac" % (account,account)
	#sql="WITH Emp AS ( SELECT ac,F_ac,ratio FROM  [Future].[dbo].[p_follow] WHERE   ac='%s' UNION ALL  SELECT   D.AC,D.F_ac,D.ratio*emp.ratio/100 FROM   Emp         INNER JOIN [Future].[dbo].[p_follow] d ON d.ac = Emp.F_ac)     select '%s' as AC,f_AC,SUM(ratio) as ratio from Emp where  f_ac not in (select ac from Emp)  and ratio<>0 group by F_ac" % (account,account)
	res=ms.dict_sql(sql)
	accountlist=[]
	aclist=[]
	for item in res:
		accountlist.append(item['AC'])
		aclist.append(item['f_AC'])
	accountlist=list(set(accountlist))
	aclist=list(set(aclist))
	ac_ratio={}
	for item in aclist:
		ac_ratio[item]=0
	for item in res:
		ac_ratio[item['f_AC']]=ac_ratio[item['f_AC']]+item['ratio']
	# print ac_ratio
	#删除基本账户信息
	for key in accountlist:
		if ac_ratio.has_key(key):
			del ac_ratio[key]
	# print ac_ratio
	return ac_ratio

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def get_dailyquanyi(account,fromDdy):
	ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
	ac_ratio=get_ac_ratio(account)
	totalquanyi=[]
	for key in ac_ratio:
		ratio=ac_ratio[key]
		if ratio>0:
			sql="SELECT [quanyisymbol]  FROM [LogRecord].[dbo].[quanyicaculatelist] where acname='%s'" % (key)
			res=ms.dict_sql(sql)
			if not res:
				# print {"ispass":0,"result":"%s does not has equity" % (key)}
				return {"ispass":0,"result":"%s does not has equity" % (key)}
			else:
				symbol=res[0]['quanyisymbol']
				acname=key
				sql="select top 1 D,quanyi as  quanyia from dailyquanyi_V2 where ac='%s' and symbol='%s' and D>=%s order by D" % (acname,symbol,fromDdy)
				tempres=ms.find_sql(sql)
				if tempres==[]:
					initoalquanyi=0
				else:
					initoalquanyi=tempres[0][1]
				sql="select D,(quanyi-%s) as  quanyia from dailyquanyi_V2 where ac='%s' and symbol='%s' and D>=%s order by D" % (initoalquanyi,acname,symbol,fromDdy)
				res1=ms.find_sql(sql)
				#乘以ratio
				newres1=[]
				for item in res1:
					newres1.append([item[0],item[1]*ratio/10.0])
				totalquanyi=add_time_series(totalquanyi,newres1)
				totalquanyi=sorted(totalquanyi,key=lambda a :a[0])
				totalquanyi=[[item[1],item[0]] for item in totalquanyi]
				# for item in totalquanyi:
				# 	print item 
				return {"ispass":1,"result":totalquanyi}

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def get_ac_ratio(account):
	#获取总账户配置的虚拟组的ratio
	ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
	sql="WITH Emp AS ( SELECT ac,F_ac,ratio FROM  p_follow WHERE   ac='%s' UNION ALL  SELECT   D.AC,D.F_ac,D.ratio*emp.ratio/100 FROM   Emp         INNER JOIN p_follow d ON d.ac = Emp.F_ac)SELECT AC,f_AC,ratio FROM  Emp" % (account)
	res=ms.dict_sql(sql)
	accountlist=[]
	aclist=[]
	for item in res:
		accountlist.append(item['AC'])
		aclist.append(item['f_AC'])
	accountlist=list(set(accountlist))
	aclist=list(set(aclist))
	# print accountlist
	# print aclist
	ac_ratio={}
	for item in aclist:
		ac_ratio[item]=0
	for item in res:
		ac_ratio[item['f_AC']]=ac_ratio[item['f_AC']]+item['ratio']
	# print ac_ratio
	for key in accountlist:
		if ac_ratio.has_key(key):
			del ac_ratio[key]
	# print ac_ratio
	return ac_ratio

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def main_fun():
    ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
    ms05 = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
    ms03 = MSSQL(host="192.168.0.3\SQLEXPRESS",user="future",pwd="[email protected]",db="future")
    # get_Kbarinfo(ms05,15,'2016-12-13 10:00:00')
    # get_Kbarinfo(ms03,15,'2016-12-13 10:00:00')

    resultlist=[]
    total_day_generate(fromdate='2016-11-01',interval=15,resultlist=resultlist)
    sql="  select distinct  top(60)   stockdate from [Future].[dbo].[TSymbol_%smin] order by stockdate desc" % (15)
    res=ms.dict_sql(sql)
    firsttime=res[-1]['stockdate']
    lasttime=res[0]['stockdate']
    aaa=[item for item in resultlist if item >=firsttime ]
    aaa=[item for item in aaa if item <=lasttime ]
    for item in aaa:
        print item 
        get_Kbarinfo(ms05,15,item)
        get_Kbarinfo(ms03,15,item)
    write_heart('Kbars','queren')

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def analysis_st(st,period):
	print st 
	ms = MSSQL(host="192.168.0.5", user="future", pwd="[email protected]", db="future")
	nowday=datetime.datetime.now().strftime('%y%m%d')
	nowday=int(nowday)
	sql="select * from real_st_report where st='%s'  and D>='%s' order by stockdate,id" % (st,nowday)
	res=ms.dict_sql(sql)
	# drop replecate  return [[p,stockdate],[],[]]
	res=drop_replacate(res)
	(num60_3,content)=analysis_result(res,60,3)
	contentstr=','.join(content[:5])
	contentstr=contentstr[:800]
	print contentstr
	(num120_3, content) = analysis_result(res, 120, 3)
	(num120_4, content) = analysis_result(res, 120, 4)
	(num120_5, content) = analysis_result(res, 120, 5)
	aa= [st,num60_3,num120_3,num120_4,num120_5]
	if aa<>[st,0,0,0,0]:
		sql = "insert into [LogRecord].[dbo].[st_shandan_analysis](st,[s60_3],[s120_3],[s120_4],[s120_5],s60_content,[period]) values('%s',%s,%s,%s,%s,'%s','%s');" % (aa[0], aa[1], aa[2], aa[3], aa[4],contentstr,period)
		ms.insert_sql(sql)

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def monitor_add_errorinfo(type,myitem):
	#查询发件人
	ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
	sql="select email from [LogRecord].[dbo].[mailtolist] where istomail=1"
	reslist=ms.find_sql(sql)
	mailtolist=''
	sendmessage=''
	for item in reslist:
		if "@" in item[0]:
			mailtolist=mailtolist+','+item[0]
		else:
			sendmessage=sendmessage+','+item[0]
	mailtolist=mailtolist.strip(',')
	sendmessage=sendmessage.strip(',')

	# print mailtolist
	#待检测的ABmachine列表
	sql="select item,starttime,endtime from [LogRecord].[dbo].[monitorconfig] where type='%s' and item='%s' and ismonitor=1" % (type,myitem)
	res=ms.dict_sql(sql)
	for item in res:
		symbol=item['item']
		starttime=item['starttime']
		endtime=item['endtime']
		sql="select getdate()"
		getnow=ms.find_sql(sql)[0][0]
		nowtime=getnow.strftime('%H:%M:%S')
		nowtime=datetime.datetime.strptime(nowtime,'%H:%M:%S')
		starttime=datetime.datetime.strptime(starttime,'%H:%M:%S')
		endtime=datetime.datetime.strptime(endtime,'%H:%M:%S')
		if nowtime>starttime and nowtime<=endtime:
			# print '报警'
			# print "@@@@@@@@@@@@@[email protected]@@@@@@@@@@@@@"
			subject='ctontab出错 %s' % (myitem)
			msg=subject
			sql="insert into [LogRecord].[dbo].[maillist](subject,mailtolist,msg,type,inserttime,sendmessage) values('%s','%s','%s',%s,getdate(),'%s')" % (subject,mailtolist,msg,0,sendmessage)
			print sql 
			ms.insert_sql(sql)
			break

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
		# 	print item[0],item[2]
		# for item in mymewquote:
		# 	print item[0],item[1],item[2] 
		# #分商品和IC  IF 两类	(这个是delta仓位，其实不能去除)
		if 	symbol in ('IC','IF'):
			#去除9:00-9:29分钟的信号
			for item in mymewquote:
				timestr=item[0].strftime("%H%M")
				timestr=int(timestr)
				if timestr>=900 and  timestr<=929:
					mymewquote.remove(item)
			#--end
		##插入数据库
		lastrecordtime=datetime.datetime(2015,01,01,01,00)
		sql="select max(stockdate) as stockdate  from [Future].[dbo].[quanyi_log_groupby_v2] where ac='%s' and symbol='%s'" % (ac,symbol)
		timeinfo=ms.dict_sql(sql)[0]
		if timeinfo['stockdate'] is None:
			lastrecordtime=datetime.datetime(2015,01,01,01,00)
		else:
			lastrecordtime=timeinfo['stockdate']

		insertvalue=""
		numofinsert=0
		for item in mymewquote:
			if item[0]>lastrecordtime:
				insertvalue=insertvalue+","+"('%s','%s',0,%s,'%s',%s,%s)" % (ac,symbol,item[1],item[0],item[2],totalsum)
				numofinsert=numofinsert+1
			if numofinsert>700:
				insertvalue=insertvalue.strip(',')
				sql="insert into [Future].[dbo].[quanyi_log_groupby_v2](ac,symbol,type,ClosePrice,stockdate,totalposition,totalNum) values%s" % insertvalue
				ms.insert_sql(sql)

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]






_dfrom =r'Y:\data_wenhua'

if os.path.exists(_dfrom):
    pass
else:
    _dfrom =r'/home/yuyang/myfile/data_wenhua'
#每天23点运行
sql="select replace(convert(nvarchar(10),getdate(),120),'-','') as mydate"
res=ms.dict_sql(sql)
date = res[0]['mydate']
#date=20160918
if len(sys.argv)>1:
    date=sys.argv[1]

if len(sys.argv)>2:
    mysymbol=sys.argv[2]
else:
    mysymbol=''
timestart ='0000'
timeend = 2359
print date,mysymbol


targetfile=_dfrom+'\\'+str(date)+'.csv'

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def get_delta_info_from_database(account,symbol,symbolid):
	ms1 = MSSQL(host="192.168.0.8", user="future", pwd="[email protected]", db="HF_Future")
	#获取成交回报
	if symbol.lower()=='yy':
		symbol1='y'
		sql="select 'yy' as symbol,case when Direction='buy' then 1 else -1 end as Direction,[Volume],  Price,convert(datetime,convert(nvarchar(10),[Databasetime],20) +' '+[TradeTime],120) as stockdate from [HF_Future].[dbo].[tradelog] where [HF_Future].dbo.m_getstr([InstrumentID]) = '%s' and [InvestorID]='%s' and convert(datetime,convert(nvarchar(10),[Databasetime],20) +' '+[TradeTime],120)>='%s' order by stockdate " % (symbol1,account,begintime)
	else:
		symbol1=symbol
		sql="select [HF_Future].dbo.m_getstr([InstrumentID]) as symbol,case when Direction='buy' then 1 else -1 end as Direction,[Volume],  Price,convert(datetime,convert(nvarchar(10),[Databasetime],20) +' '+[TradeTime],120) as stockdate from [HF_Future].[dbo].[tradelog] where [HF_Future].dbo.m_getstr([InstrumentID]) = '%s' and [InvestorID]='%s' and convert(datetime,convert(nvarchar(10),[Databasetime],20) +' '+[TradeTime],120)>='%s' order by stockdate " % (symbol1,account,begintime)
	res=ms1.dict_sql(sql)

	#如果有记录，则加上初始仓位，算出总仓位
	if res:
		afirsttime=res[0]['stockdate']
	else:
		afirsttime =datetime.datetime.strptime(begintime,'%Y-%m-%d')

	sql = "SELECT top (1) 0 as ClosePrice,convert(datetime,convert(nvarchar(16),[inserttime],120)+':00',120) as stockdate ,[longhave]-[shorthave] as totalposition,bb.Symbol as symbol,  stockID as S_ID FROM [LogRecord].[dbo].[account_position]aa inner join Future.dbo.symbol_id bb on aa.stockID=bb.S_ID and len(bb.symbol)<3  where userid='%s'   and inserttime<='%s' and stockid=%s order by [inserttime] desc " % (account, afirsttime, symbolid)
	tempres = ms.dict_sql(sql)
	if tempres:
		alastposition = tempres[0]['totalposition']
		atime_position = tempres[0]['stockdate']
	else:
		print symbol, '不存在期货账户仓位信息'
		exit()
	sql = "SELECT top 1 C  FROM [Future].[dbo].[TSymbol_ZL] where symbol='%s' and stockdate<='%s' order by stockdate desc" % (symbol, atime_position)
	tempres = ms.dict_sql(sql)
	if tempres:
		atime_price = ms.dict_sql(sql)[0]['C']
	else:
		atime_price = 0
	addline = {'stockdate': atime_position, 'Direction': 0, 'Volume': 0, 'symbol': symbol, 'Price': atime_price}
	res.append(addline)
	deltapd=pd.DataFrame(res)

	#偏移夜盘的时间至加上4小时-24
	deltapd['stockdate']=deltapd['stockdate'].apply(lambda x: shift_time(x))
	deltapd['deltaposition']=deltapd['Direction']*deltapd['Volume']
	deltapd['myindex']=deltapd.index
	deltapd=deltapd.sort_values(['stockdate','myindex'])
	deltapd['totalposition'] = deltapd['deltaposition'].cumsum()+alastposition

	# add everyday edntime
	sql="select stockdate,C as Price from [TSymbol_ZL] where symbol='%s' and stockdate in ( select MAX(StockDate) as stockdate from [TSymbol_ZL] where symbol='%s' and StockDate>='%s' and StockDate<='%s' and t<='15:30'group by D )" % (symbol,symbol,begintime,endtime)
	insertstockdate=ms.dict_sql(sql)
	#计算出对应的仓位
	lastonetime=insertstockdate[-1]['stockdate']
	lasttwotime=insertstockdate[-2]['stockdate']
	sql = "SELECT top (1) 0 as ClosePrice,convert(datetime,convert(nvarchar(16),[inserttime],120)+':00',120) as stockdate ,[longhave]-[shorthave] as totalposition,bb.Symbol as symbol,  stockID as S_ID FROM [LogRecord].[dbo].[account_position]aa inner join Future.dbo.symbol_id bb on aa.stockID=bb.S_ID and len(bb.symbol)<3  where userid='%s'   and inserttime<='%s' and stockid=%s order by [inserttime] desc " % (account, lastonetime, symbolid)
	tempres = ms.dict_sql(sql)
	if tempres:
		alastposition = tempres[0]['totalposition']
		atime_position = tempres[0]['stockdate']
	else:
		print symbol, '不存在期货账户仓位信息'
		exit()
	insertstockdate[-1]['totalposition']=alastposition
	sql = "SELECT top (1) 0 as ClosePrice,convert(datetime,convert(nvarchar(16),[inserttime],120)+':00',120) as stockdate ,[longhave]-[shorthave] as totalposition,bb.Symbol as symbol,  stockID as S_ID FROM [LogRecord].[dbo].[account_position]aa inner join Future.dbo.symbol_id bb on aa.stockID=bb.S_ID and len(bb.symbol)<3  where userid='%s'   and inserttime<='%s' and stockid=%s order by [inserttime] desc " % (account, lasttwotime, symbolid)
	tempres = ms.dict_sql(sql)
	if tempres:
		alastposition = tempres[0]['totalposition']
		atime_position = tempres[0]['stockdate']
	else:
		print symbol, '不存在期货账户仓位信息'
		exit()
	insertstockdate[-2]['totalposition']=alastposition

	insertstockdate_pd=pd.DataFrame(insertstockdate)
	deltapd=deltapd.append(insertstockdate_pd,ignore_index=True)


	deltapd=deltapd.sort_values(['stockdate','myindex'])
	deltapd = deltapd.fillna(method='ffill')
	deltapd['C']=deltapd['Price']
	deltapd=deltapd[deltapd['stockdate']<=endtime]
	#如果现在存在totalposition NAN 则需要手动补全
	nanvalue=deltapd[deltapd['totalposition'].isnull()]
	if len(nanvalue)==0:
		return deltapd[['C', 'stockdate', 'symbol', 'totalposition']]

	atime=nanvalue['stockdate'].values[-1]
	atime=Timestamp(atime)
	sql = "SELECT top (1) 0 as ClosePrice,convert(datetime,convert(nvarchar(16),[inserttime],120)+':00',120) as stockdate ,[longhave]-[shorthave] as totalposition,bb.Symbol as symbol,  stockID as S_ID FROM [LogRecord].[dbo].[account_position]aa inner join Future.dbo.symbol_id bb on aa.stockID=bb.S_ID and len(bb.symbol)<3  where userid='%s'   and inserttime<='%s' and stockid=%s order by [inserttime] desc " % (account, atime, symbolid)
	tempres = ms.dict_sql(sql)
	if tempres:
		alastposition = tempres[0]['totalposition']
		atime_position = tempres[0]['stockdate']
	else:
		print symbol, '不存在期货账户仓位信息1'
		exit()
	sql = "SELECT top 1 C  FROM [Future].[dbo].[TSymbol_ZL] where symbol='%s' and stockdate<='%s' order by stockdate desc" % (symbol, atime_position)
	tempres = ms.dict_sql(sql)
	if tempres:
		atime_price = ms.dict_sql(sql)[0]['C']
	else:
		atime_price = 0
	aa=pd.DataFrame([{'stockdate':atime_position,'totalposition':alastposition,'C':atime_price,'symbol':symbol}])
	deltapd = deltapd.append(aa,ignore_index=True)
	deltapd=deltapd.sort_values(['stockdate','myindex'])
	deltapd = deltapd.fillna(method='ffill')
#.........这里部分代码省略.........

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
	equity_day['myday']=equity_day['day']
	write_to_database_equity(equity_day,acname=acname,symbol=quanyi_symbol)



# sub_main('RB3rangel','RB','RB')


if __name__=="__main__":
    threads_N=1
    multiprocessing.freeze_support()

    pool = multiprocessing.Pool(processes = threads_N)

    sql = "SELECT id, [acname] ,[positionsymbol] ,[quanyisymbol] ,[iscaculate]  ,[isstatistic] FROM [LogRecord].[dbo].[quanyicaculatelist] where iscaculate=1 and isyepan in (0,1,12)  AND quanyisymbol not in ('IF','IC','IH') order by id desc "
    res1 = ms.dict_sql(sql)

    for item in res1:
        acname=item['acname']
        day_night_symbol=item['positionsymbol']
        quanyi_symbol=item['quanyisymbol']
        #print item['id'],acname,day_night_symbol,quanyi_symbol
        # add process to pool
        if threads_N > 1:
            #print multiprocessing.current_process().name
            pool.apply_async(sub_main, (acname,day_night_symbol,quanyi_symbol) )
        else:
            sub_main(acname,day_night_symbol,quanyi_symbol)

    print "Process Pool Closed, Waiting for sub Process Finishing..."
    pool.close()

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
#coding=utf-8 
#!/usr/bin/env python
# pymssql
import sys
import pandas as pd
reload(sys)
sys.setdefaultencoding('utf8')
from dbconn import MSSQL
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
resList = ms.dict_sql("select top 2 * from st_report")
df1=pd.DataFrame(resList)
print df1

print resList

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]



#权益入数据库 daily_equity_V3 记录delta 权益

#这是夜盘的权益计算
#看看 日盘 或者 日夜连做的权益是否一样计算
root=r'C:\Users\YuYang\Documents\Tencent Files\794513386\FileRecv\jieti1_zhuli_zhishu'
acname='StepMultiI300w_up'
# drop_replicate_st_report_test()
# input_to_database(root,acname)

#sql="truncate table quanyi_log_groupby_v3"
#ms.insert_sql(sql)
sql="select p.F_ac,a.Symbol from p_follow p inner join symbol_id a on p.stock=a.S_ID and LEN(a.symbol)<3 and p.AC='%s' ORDER BY f_ac" % (acname)
forlist=ms.dict_sql(sql)
i=0
for item in forlist:
	i=i+1
	print i, item
	position = get_position(acname=item['F_ac'],symbol=item['Symbol'],stepname=acname)
	newposition = generate_new_position(position,symbol=item['Symbol'])
	write_to_database_position(df1=newposition,symbol=item['Symbol'],acname=item['F_ac'])

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def cal_position_lilun():
	#1 put lilun equity into account_position_lilun,添加不存在报警机制
	ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future") 
	sql="truncate table [LogRecord].[dbo].account_position_lilun"
	ms.insert_sql(sql)
	sql="select distinct userid from [LogRecord].[dbo].[account_position]  where userid<>'05810058' order by userid"
	res=ms.dict_sql(sql)
	totalsql=""
	for item in res:
		userid=item['userid']
		ispass=0
		try:
			sql="select 1 from Fun_account_position_all_V2('%s')" % (userid)
			ms.insert_sql(sql)
			ispass=1
		except:
			sql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime],beizhu) values('%s',0,'%s',getdate(),'%s')" % (userid,0,'No view in 0.5 database')
			ms.insert_sql(sql)
		if ispass==1:
			tempsql="select '%s' as [userID],STOCK as [stockID],Expr1 as position,GETDATE() as nowtime from Fun_account_position_all_V2('%s')" % (userid,userid)
			totalsql=totalsql+" union all "+tempsql
	totalsql=totalsql.strip(" union all ")
	totalsql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime]) "+ totalsql
	ms.insert_sql(totalsql)

	#2 shangpin yingshe
	totalsql=""
	ms1 = MSSQL(host="139.196.104.105",user="future",pwd="[email protected]",db="Future")
	res=['05810058']
	for item in res:
		userid=item
		ispass=0
		try:
			sql="select 1 from future.dbo.view_%s" % (userid)
			ms1.insert_sql(sql)
			ispass=1
		except:
			sql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime],beizhu) values('%s',0,'%s',getdate(),'%s')" % (userid,0,'No view in 0.5 database')
			print sql
			ms.insert_sql(sql)
		if ispass==1:
			tempsql="select '%s' as [userID],STOCK as [stockID],Expr1 as position,GETDATE() as inserttime from future.dbo.view_%s" % (userid,userid)
			totalsql=totalsql+" union all "+tempsql
	totalsql=totalsql.strip(" union all ")
	#totalsql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime]) "+ totalsql
	tempres=ms1.dict_sql(totalsql)

	#3 stock yinghse
	sql="SELECT [Symbol]  ,[S_ID] FROM [Future].[dbo].[Symbol_ID] where Symbol in ('IC','IF','IH','TF','T')"
	res=ms.dict_sql(sql)
	symboldict={}
	for item in res:
		symboldict[item['Symbol']]=item['S_ID']
	sql="SELECT a.account,a.symbol,sum(a.ratio*b.position ) as Position  FROM [future].[dbo].[account_position_stock_yingshe] a left join [future].[dbo].[RealPosition] b on a.acanme=b.Name group by a.account,a.symbol having a.account not in ('666061008')"
	res=ms1.dict_sql(sql)
	for item in res:
		#print item 
		symbol_id=symboldict[item['symbol']]
		sql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime]) values('%s','%s','%s',getdate())" % (item['account'],symbol_id,item['Position'])
		#print sql 
		ms.insert_sql(sql)

	totalsql=""
	tempv=""
	for item in tempres:
		tempv=",('%s','%s','%s','%s')" % (item['userID'],item['stockID'],int(item['position']),item['inserttime'].strftime("%Y-%m-%d %H:%M:%S"))
		totalsql=totalsql+tempv
	totalsql=totalsql.strip(",")
	if len(totalsql)>10:
		totalsql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime]) values%s" % (totalsql)
		print totalsql
		ms.insert_sql(totalsql)

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import dict_sql [as 别名]
def order_get_dailyquanyi_margin(account,fromDdy):
	ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
	ac_ratio=order_get_ac_ratio_three(account)
	totalquanyi=[]
	#获取对齐时间
	Dlist=[]
	tempresult=""
	configinfo=[]
	Dlist.append(fromDdy)
	if ac_ratio==[]:
		return {"ispass":0,"result":"存在有自己跟随自己的配置，请修正"}
	if ac_ratio=={}:
		return {"ispass":0,"result":"P_follow中不存在相应配置，请确认是否正确"}
	for key in ac_ratio:
		realac=key.split("__")[0]
		quanyisymbols_id=key.split("__")[-1]
		sql="select top(1) [positionsymbol] from [LogRecord].[dbo].[quanyicaculatelist]  where acname='%s'" % (realac)
		res11=ms.dict_sql(sql)
		if res11:
			positionsymbol=res11[0]['positionsymbol']
		else:
			return {"ispass":0,"result":"虚拟组权益没有准备，请联系俞洋--%s" % (realac)}
		sql="select  a.acname,s.S_ID,s.Symbol from LogRecord.dbo.quanyicaculatelist a left join Symbol_ID s on a.quanyisymbol=s.Symbol where a.acname='%s' and  s.S_ID='%s'" % (realac,quanyisymbols_id)
		quanyisymbol=ms.dict_sql(sql)[0]['Symbol']
		sql="SELECT top 1  (convert(int,replace(convert(varchar(10),DATEADD(day,1,stockdate),120),'-',''))-20000000) as D  FROM [Future].[dbo].[quanyi_log_groupby_v2] where ac='%s' and symbol='%s' order by stockdate" % (realac,positionsymbol)
		# or
		sql="select top 1 D from dailyquanyi_V2 where ac='%s' and symbol='%s' and not (position=0 and quanyi=0 and times=0) order by D" % (realac,positionsymbol)
		tempD=ms.dict_sql(sql)
		if tempD:
			Dlist.append(tempD[0]['D'])
			configinfo.append([key,ac_ratio[key],tempD[0]['D']])
		else:
			Dlist.append(200000)
			sql="select ac from [LogRecord].[dbo].[order_p_follow]  where F_ac='%s' and stock='%s'" % (realac,quanyisymbol)
			tempres=ms.dict_sql(sql)
			tempresult=tempresult+" 基本账户 %s 中 %s 没有产生过信号,请补全近两年策略信号</br>" % (tempres[0]['ac'],key)
			configinfo.append([key,ac_ratio[key],200000])
	fromDdy=max(Dlist)
	if 200000 in Dlist:
		return {"ispass":0,"result":tempresult,"configinfo":configinfo}

		
	for key in ac_ratio:

		realac=key.split("__")[0]
		quanyisymbols_id=key.split("__")[-1]
		sql="select  a.acname,s.S_ID,s.Symbol from LogRecord.dbo.quanyicaculatelist a left join Symbol_ID s on a.quanyisymbol=s.Symbol where a.acname='%s' and  s.S_ID='%s'" % (realac,quanyisymbols_id)
		quanyisymbol=ms.dict_sql(sql)[0]['Symbol']


		ratio=ac_ratio[key]
		if ratio<>0:
			sql="SELECT [quanyisymbol]  FROM [LogRecord].[dbo].[quanyicaculatelist] where acname='%s' and quanyisymbol='%s'" % (realac,quanyisymbol)
			res=ms.dict_sql(sql)
			if not res:
				# print {"ispass":0,"result":"%s does not has equity" % (key)}
				return {"ispass":0,"result":"%s 不在配置表 quanyicaculatelist 中，请加上并获得历史信号" % (key),"configinfo":configinfo}
			else:
				symbol=res[0]['quanyisymbol']
				acname=realac
				sql="select top 1 D,quanyi as  quanyia from dailyquanyi_V2 where ac='%s' and symbol='%s' and D>=%s order by D" % (acname,symbol,fromDdy)
				tempres=ms.find_sql(sql)
				if tempres==[]:
					initoalquanyi=0
				else:
					initoalquanyi=tempres[0][1]
				initoalquanyi=0
				sql="select D,(quanyi-(%s)) as  quanyia from dailyquanyi_V2 where ac='%s' and symbol='%s' and D>=%s order by D" % (initoalquanyi,acname,symbol,fromDdy)
				res1=ms.find_sql(sql)
				#乘以ratio
				newres1=[]
				for item in res1:
					newres1.append([item[0],item[1]*ratio/10.0])
				totalquanyi=add_time_series(totalquanyi,newres1)
				totalquanyi=sorted(totalquanyi,key=lambda a :a[0])
	totalquanyi=[[item[1],item[0]] for item in totalquanyi]
				# for item in totalquanyi:
				# 	print item 
	return {"ispass":1,"result":totalquanyi,"configinfo":configinfo}