def getDataFromPostgres(self):
#Now = getNow('%Y-%m-%d')
Now = self.Now.split()[0]
post = postgersql()
colname1 = [
'id', 'trade_id', 'currency_pair', 'sell_currency', 'buy_currency',
'sell_amount', 'buy_amount', 'trade_date', 'determined_date',
'delivery_date', 'exe_doexrate', 'exe_upexrate', 'type',
'consign_entity', 'trade_type'
]
wherestring1 = """ delivery_date>='{}' and trade_date<='{}'""".format(
Now, Now)
optiondata = post.select(self.table, colname1, wherestring1)
optiondata = filter(
lambda x: x['currency_pair'] in getrate() and Now >= getrate()[x[
'currency_pair']], optiondata)
colname2 = ['der_id']
wherestring2 = """date='{}'""".format(Now)
savedata = map(lambda x: x['der_id'],
post.select(self.writetable, colname2, wherestring2))
data = []
for lst in optiondata:
if lst['trade_type'] + '_' + str(lst['id']) not in savedata:
data.append(lst)
if data is not None and data != []:
self.data = data
self.getDataFromMongo() ##从mongo提取数据并更新损益
def getdailysum(com_code):
data = []
try:
postgre = postgersql()
if len(com_code) == 0:
data = postgre.view(
'''select sum(gal) from frs_t_daily_position group by date''',
['sum'])
elif len(com_code) == 1:
where = com_code[0]
data = postgre.view(
'''select sum(gal) from frs_t_daily_position where com_code in ('%s') group by date'''
% where, ['sum'])
else:
where = str(tuple(map(str, com_code)))
data = postgre.view(
'''select sum(gal) from frs_t_daily_position where com_code in %s group by date'''
% where, ['sum'])
postgre.close()
except:
traceback.print_exc()
return None
if data != []:
data = map(lambda x: x.get('sum'), data)
return data
def getTime(self):
"""
获取最小起始日期和min{最大交割日日期,当前日期}
"""
post = postgersql()
colname = ['trade_date','delivery_date','currency_pair','dates']
sql = """select op.trade_date,op.delivery_date,op.currency_pair,
case
when t.dates<op.delivery_date then date(t.dates)+1
when t.dates=op.delivery_date then current_date
end as dates
from %s op
left join
(select max(pt.date) as dates,pt.der_id from %s pt
group by pt.der_id) t
on op.trade_type||'_'||op.id = t.der_id
"""%(self.table,self.querytable)
time = post.view(sql,colname)
if time==[] or time==None:
self.begintime = None
self.endtime = None
else:
time = filter(lambda x:x['currency_pair'] in getrate() and dateTostr(x['delivery_date'])>=getrate()[x['currency_pair']],time)
#print len(time)
for lst in time:
if lst['dates'] is None:
lst['dates'] = dateTostr(lst['trade_date']) if dateTostr(lst['trade_date'])>getrate()[lst['currency_pair']] else getrate()[lst['currency_pair']]
else:
lst['dates'] = dateTostr(lst['dates'])
begintime = time[0]['dates'] if len(time)==1 else min(map(lambda x:x['dates'],time))
self.begintime = begintime if getNow('%Y-%m-%d')>=begintime else None
endtime = dateTostr(time[0]['delivery_date'] if len(time)==1 else max(map(lambda x:x['delivery_date'],time)))
self.endtime = endtime if getNow('%Y-%m-%d')>endtime else timedelta(getNow('%Y-%m-%d'),-1)
def updateData(self):
"""
全量更新
'date','com_code','der_id','cap_currency','cap_amount','gal','exrate','data_date':
日期，公司代码，衍生品编号，本金币种，本金金额，汇兑损益，当天的本金/人民币汇率，数据生成日期
"""
from database.database import postgersql
column = ['date','com_code','der_id','cap_currency','cap_amount','gal','exrate','data_date']
columnname = '(date,com_code,der_id,cap_currency,cap_amount,gal,exrate,data_date)'
insertdata = map(lambda x:map(lambda y:x[y],column),self.forwarddict)
self.Lock.acquire()
post = postgersql()
#post.deltetable(self.writetable)
post.insert(self.writetable,columnname,insertdata)
post.close()
self.Lock.release()
def getDataFromPostgres(self):
"""
获取结构性产品的订单数据
"""
post = postgersql()
colname = [
'id', 'trade_id', 'currency_pair', 'sell_currency', 'buy_currency',
'sell_amount', 'buy_amount', 'trade_date', 'determined_date',
'delivery_date', 'trp', 'rate', 'type', 'consign_entity',
'trade_type'
]
#wherestring = None
#orderby = "order by trade_id, delivery_date"
#Now = getNow('%Y-%m-%d')
Now = dateTostr(self.Now)
sql = """select %s from %s t join
(select trade_id from %s group by trade_id having max(delivery_date)>=date'%s' ) b
on t.trade_id=b.trade_id
order by t.trade_id, t.delivery_date
""" % (','.join(map(lambda x: 't.' + x, colname)),
self.table, self.table, Now) ##过滤已交割完成的结构产品
#self.data = post.view(sql,colname)
optiondata = post.view(sql, colname)
optiondata = filter(
lambda x: x['currency_pair'] in getrate() and Now >= getrate()[x[
'currency_pair']], optiondata)
colname2 = ['der_id']
wherestring2 = """date='{}'""".format(Now)
savedata = map(lambda x: x['der_id'],
post.select(self.writetable, colname2, wherestring2))
data = []
for lst in optiondata:
if lst['trade_type'] + '_' + str(lst['id']) not in savedata:
data.append(lst)
if data is not None and data != []:
self.data = data
self.getDataFromMongo() ##从mongo提取数据并更新损益
self.cumputeLost()
thds = []
items = [
participateforwards, CollaOptions, knockoptions, forwards, SwapOptions,
TargetRedemptionForwards
]
Lock = threading.RLock()
#SwapOptions(Lock)
try:
for item in items:
thds.append(threading.Thread(target=item, args=(Lock, )))
for thd in thds:
thd.start()
for thd in thds:
thd.join()
except:
traceback.print_exc()
post = postgersql()
post.deltetable(table_write, 'date<current_date') ##清空数据库
sql = """insert into %s select * from %s where date<current_date""" % (
table_write, table_write_tmp)
post.view(sql)
post.close()
#try:
#for items in [participateforwards,CollaOptions,knockoptions,forwards,SwapOptions,TargetRedemptionForwards]:
#items()
#except:
#traceback.print_exc()