def reserve_accounts(context, conf):
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
logger = log.get_logger(category="ReserveAccounts")
logger.info("[reserve accounts with %s] begin" % (json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
sql = """UPDATE siminfo.t_investor t SET t.investorstatus = '6' WHERE t.investorstatus = '0' AND t.investorid < '00001000'"""
cursor.execute(sql)
sql = """UPDATE siminfo.t_investor t SET t.investorstatus = '6' WHERE t.investorstatus = '0' AND t.investorid REGEXP '^0*(1{2,8}|2{2,8}|3{2,8}|5{2,8}|6{2,8}|7{2,8}|8{2,8}|9{2,8})$'"""
cursor.execute(sql)
mysql_conn.commit()
except Exception as e:
logger.error("[reserve accounts] Error: %s" % (e))
finally:
mysql_conn.close()
logger.info("[reserve accounts with %s] end" % (json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
def snap_data(context, conf):
result_code = 0
logger = log.get_logger(category="SnapData")
broker_system_id = conf.get("brokerSystemId")
logger.info("[snap data %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
logger.info("[snap t_activityinvestorevaluation]......")
sql = """INSERT INTO snap.t_s_activityinvestorevaluation(TradingDay,ActivityID,TermNo,InvestorID,InitialAsset,PreAsset,CurrentAsset,TotalReturnRate,ReturnRateOf1Day,RankingStatus,PreRanking,Ranking)
SELECT DATE_FORMAT(NOW(), '%Y%m%d'),ActivityID,TermNo,InvestorID,InitialAsset,PreAsset,CurrentAsset,TotalReturnRate,ReturnRateOf1Day,RankingStatus,PreRanking,Ranking
FROM siminfo.t_activityinvestorevaluation"""
cursor.execute(sql)
mysql_conn.commit()
except Exception as e:
logger.error("[snap data] Error: %s" % e)
result_code = -1
finally:
mysql_conn.close()
logger.info("[snap data] end")
return result_code
def publish_future(context, conf):
result_code = 0
logger = log.get_logger(category="PublishfutureExchange")
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
trade_system_id = conf.get("tradeSystemId")
logger.info("[publish future exchange %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
logger.info("[get current trading day]......")
sql = """SELECT DISTINCT t1.tradingday
FROM siminfo.t_tradesystemtradingday t1
WHERE t1.tradesystemid = %s"""
cursor.execute(sql, (trade_system_id, ))
row = cursor.fetchone()
current_trading_day = str(row[0])
logger.info("[get current trading day] current_trading_day = %s" %
current_trading_day)
logger.info("[get next trading day]......")
# 判断是否跳过节假日
holiday = conf.get("holiday")
if holiday is True or holiday is None:
sql = """SELECT DAY FROM siminfo.t_TradingCalendar t WHERE t.day > %s AND t.tra = '1' ORDER BY DAY LIMIT 1"""
else:
sql = """SELECT DAY FROM siminfo.t_TradingCalendar t WHERE t.day > %s ORDER BY DAY LIMIT 1"""
cursor.execute(sql, (current_trading_day, ))
row = cursor.fetchone()
next_trading_day = str(row[0])
logger.info("[get next trading day] next_trading_day = %s" %
next_trading_day)
# 更新交易所系统交易日
logger.info("[update trade system tradingday]......")
sql = """UPDATE siminfo.t_tradesystemtradingday t1
SET t1.tradingday = %s, t1.lasttradingday = %s
WHERE t1.tradingday = %s AND t1.tradesystemid = %s"""
cursor.execute(sql, (next_trading_day, current_trading_day,
current_trading_day, trade_system_id))
mysql_conn.commit()
except Exception as e:
logger.error("[publish future exchange] Error: %s" % e)
result_code = -1
finally:
mysql_conn.close()
logger.info("[publish future exchange] end")
return result_code
def get_current_tradingday(context, conf):
logger = log.get_logger(category="GetTradingDay")
logger.info("[get tradingday with %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False), ))
current_trading_day = ""
trade_system_id = conf.get("tradeSystemId")
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
cursor = mysql_conn.cursor()
logger.info("[get current trading day]......")
sql = """SELECT t1.tradingday FROM siminfo.t_tradesystemtradingday t1 WHERE t1.tradesystemid = %s"""
cursor.execute(sql, (trade_system_id, ))
row = cursor.fetchone()
current_trading_day = str(row[0])
logger.info("[get current trading day] current_trading_day = %s" %
(current_trading_day))
finally:
mysql_conn.close()
logger.info("[get tradingday with %s] end" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False), ))
return current_trading_day
def start_md_service(context, conf):
logger = log.get_logger(category="MdService")
logger.info("[start stock md service with %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
# 获取API连接地址
exchange_conf = context.get("exchange").get(conf.get("targetExchangeId"))
exchange_front_addr = str(exchange_conf["mdAddress"])
# 获取行情用户
user_id = conf["userId"]
password = conf["password"]
# 建立mysql数据库连接
mysqlDB = mysql(configs=context.get("mysql")[conf.get("mysqlId")])
# 建立redis RAW库
redis_conf = context.get("redis").get(conf.get("redis_row"))
pool = redis.ConnectionPool(host=redis_conf.get('host'),
port=redis_conf.get('port'),
password=redis_conf.get('password'),
db=redis_conf.get('db'))
redis_raw = redis.Redis(connection_pool=pool)
# 建立redis ADV库
redis_conf = context.get("redis").get(conf.get("redis_adv"))
pool = redis.ConnectionPool(host=redis_conf.get('host'),
port=redis_conf.get('port'),
password=redis_conf.get('password'),
db=redis_conf.get('db'))
redis_adv = redis.Redis(connection_pool=pool)
# 建立API对象
md_api = shfemdapi.CShfeFtdcMduserApi_CreateFtdcMduserApi(
str(conf.get("exchange")))
md_handler = MdHandler(api=md_api,
uid=user_id,
pwd=password,
mysql=mysqlDB,
redis_raw=redis_raw,
redis_adv=redis_adv,
settlementgroup=conf.get("settlementgroup"),
exchange=conf.get("exchange"),
file=conf.get("filepath"))
sql = "select TopicID from siminfo.t_marketdatatopic where SettlementGroupID = %s"
result = mysqlDB.select(sql, (conf.get("settlementgroup"), ))
for topic_id in result:
md_api.SubscribeMarketDataTopic(topic_id[0], shfemdapi.TERT_RESTART)
md_api.RegisterFront(exchange_front_addr)
md_api.RegisterSpi(md_handler)
md_api.Init()
while not md_handler.is_login:
time.sleep(1)
md_api.Join()
def gen_investors(context, conf):
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
logger = log.get_logger(category="GenAccount")
balance_conf = conf["balance"]
settlementgroupid = conf["settlementgroupid"]
logger.info("[gen investors %s] begin", balance_conf)
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
config_brokers = ""
for broker_system_id in balance_conf.keys():
if broker_system_id != "default":
broker_system_balance = balance_conf[broker_system_id]
sql = '''INSERT INTO siminfo.t_investorfund(BrokerSystemID,InvestorID,PreBalance,CurrMargin,CloseProfit,Premium,Deposit,Withdraw,Balance,Available,PreMargin,FuturesMargin,OptionsMargin,PositionProfit,Profit,Interest,Fee,
TotalCollateral,CollateralForMargin,PreAccmulateInterest,AccumulateInterest,AccumulateFee,ForzenDeposit,AccountStatus,InitialAsset,PreMonthAsset,PreWeekAsset,PreAsset,CurrentAsset,PreStockValue,StockValue)
SELECT %s,t2.investorid,%s,0,0,0,0,0,%s,%s,0,0,0,0,0,0,0,0,0,0,0,0,0,'0',%s,%s,%s,%s,%s,0,0
FROM siminfo.t_investor t2 '''
cursor.execute(sql,
(broker_system_id, broker_system_balance,
broker_system_balance, broker_system_balance,
broker_system_balance, broker_system_balance,
broker_system_balance, broker_system_balance,
broker_system_balance))
config_brokers = "'%s'" % broker_system_id if len(
config_brokers) == 0 else "%s,'%s'" % (config_brokers,
broker_system_id)
for sgid in settlementgroupid:
# siminfo.t_partclient;
sql = """insert into siminfo.t_partclient(SettlementGroupID, ClientID, ParticipantID)
select %s, ClientID, ParticipantID from siminfo.t_partclient where SettlementGroupID = %s"""
cursor.execute(sql, (settlementgroupid.get(sgid), sgid))
# siminfo.t_client;
sql = """insert into siminfo.t_client(SettlementGroupID,ClientID,ClientName,IdentifiedCardType,IdentifiedCardNo,TradingRole,ClientType,IsActive,HedgeFlag)
select %s,ClientID,ClientName,IdentifiedCardType,IdentifiedCardNo,TradingRole,ClientType,IsActive,HedgeFlag from siminfo.t_client where SettlementGroupID = %s"""
cursor.execute(sql, (settlementgroupid.get(sgid), sgid))
# siminfo.t_investorclient;
sql = """insert into siminfo.t_investorclient(SettlementGroupID, InvestorID, ClientID)
select %s, InvestorID, ClientID from siminfo.t_investorclient where SettlementGroupID = %s"""
cursor.execute(sql, (settlementgroupid.get(sgid), sgid))
mysql_conn.commit()
except Exception as e:
logger.error(e)
finally:
mysql_conn.close()
logger.info("[gen investors %s] end", balance_conf)
def mdf_investors(context, conf):
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
logger = log.get_logger(category="MdfAccount")
logger.info("[mdf investor with %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
for account in conf["accounts"]:
investor_id = account["id"]
investor_name = account["name"]
open_id = account["openId"]
investor_password = account["password"]
logger.error(
"[mdf investor with {id=%s, name=%s, openId=%s, password=%s}]......"
% (investor_id, investor_name, open_id, investor_password))
sql = '''SELECT investorid FROM siminfo.t_investor WHERE investorid = %s for update'''
cursor.execute(sql, (investor_id, ))
row = cursor.fetchone()
if row is None:
sys.stderr.write("Error: Investor %s is not existed.\n" %
(investor_id, ))
logger.error(
"[mdf investor with {id=%s, name=%s, openId=%s, password=%s}] Error: Investor %s is not existed."
% (investor_id, investor_name, open_id, investor_password,
investor_id))
else:
sql = '''UPDATE siminfo.t_investor set investorname = %s, openid = %s, password = %s WHERE investorid = %s'''
cursor.execute(sql, (
investor_name,
open_id,
investor_password,
investor_id,
))
mysql_conn.commit()
except Exception as e:
logger.error(
"[mdf investor with %s] Error: %s" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False), e))
finally:
mysql_conn.close()
logger.info("[mdf investor with %s] end" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
def sync_rankable_activity_investors(context, conf):
#os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
oracle_pool = oracle(configs=context.get("oracle").get(conf.get("oracleId")))
logger = log.get_logger(category="SyncRankableActivityInvestors")
logger.info("[sync activity rankable investors with %s] begin" % (json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
oracle_conn = oracle_pool.get_cnx()
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
oracle_cursor = oracle_conn.cursor()
mysql_conn.start_transaction()
mysql_cursor = mysql_conn.cursor()
for activity_info in conf["activities"]:
activity_id = activity_info["id"]
depart_id = activity_info["depart"]
start_date = activity_info["startDate"]
sql = '''select distinct :ActivityID as activityid, sj as openid from hxcenter.vkhxx3620 where yyb= :DepartID'''# and khzt='0' and khrq>=:StartDate'''
#oracle_cursor.execute(sql, {"ActivityID": activity_id, "DepartID": depart_id, "StartDate": start_date})
oracle_cursor.execute(sql, {"ActivityID": activity_id, "DepartID": depart_id})
rows = oracle_cursor.fetchall()
activity_investors = []
for row in rows:
activity_investors.append((str(row[0]), str(row[1]).strip()))
sql = """DELETE FROM siminfo.t_activityrankableinvestor where activityid = %s"""
mysql_cursor.execute(sql, (activity_id,))
if len(activity_investors) > 0:
sql = """INSERT INTO siminfo.t_activityrankableinvestor(activityid, investorid, openid) VALUES (%s, '0', %s)"""
mysql_cursor.executemany(sql, activity_investors)
sql = """UPDATE siminfo.t_activityrankableinvestor t, siminfo.t_investor t1 SET t.investorid = t1.investorid WHERE t.activityid = %s AND t.openid = t1.openid"""
mysql_cursor.execute(sql, (activity_id,))
mysql_conn.commit()
except Exception as e:
logger.error("[sync activity rankable investors with %s] Error: %s" % (json.dumps(conf, encoding="UTF-8", ensure_ascii=False), e))
finally:
oracle_conn.close()
mysql_conn.close()
logger.info("[sync activity rankable investors with %s] end" % (json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
def gen_robots(context, conf):
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
logger = log.get_logger(category="GenRobots")
settlement_group_id = conf.get("settlementGroupId")
participant_id = conf.get("participantId")
id_prefix = conf["prefix"]
id_int = conf["start"]
count = conf["count"]
logger.info("[gen %d robots start with %d] begin" % (count, id_int))
id_list_1 = []
id_list_2 = []
for i in range(0, count):
id_str = "%s%s" % (id_prefix, str(id_int).rjust(5, "0"))
id_int += 1
id_list_1.append((
settlement_group_id,
id_str,
id_str,
))
id_list_2.append((
settlement_group_id,
participant_id,
id_str,
))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
sql = '''INSERT INTO siminfo.t_client(settlementgroupid, clientid, clientname, identifiedcardtype, identifiedcardno, tradingrole, clienttype, isactive, hedgeflag)
VALUES (%s, %s, %s, '', '', '1', '0', '1', '1')'''
cursor.executemany(sql, id_list_1)
sql = '''INSERT INTO siminfo.t_partclient(settlementgroupid, participantid, clientid)
VALUES (%s, %s, %s)'''
cursor.executemany(sql, id_list_2)
mysql_conn.commit()
except Exception as e:
logger.error("[gen %d robots start with %d] Error: %s" %
(count, id_int, e))
finally:
mysql_conn.close()
logger.info("[gen %d robots start with %d] end" % (count, id_int))
def __init__(self, context, configs):
tradeSystemID = configs.get("tradeSystemID")
log_conf = None if context.get("log") is None else context.get("log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="toSyncAll", configs=log_conf)
if log_conf is None:
self.logger.warning("toSyncAll未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化tradeSystemID
self.tradeSystemID = tradeSystemID
self.__convert_sync()
def __init__(self, context, configs):
log_conf = None if context.get("log") is None else context.get(
"log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="initScript", configs=log_conf)
if log_conf is None:
self.logger.warning("initScript未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(
configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化模板路径
self.initTemplate = context.get("init")[configs.get("initId")]
self.__load()
def snap_data(context, conf):
result_code = 0
logger = log.get_logger(category="SnapSettleData")
broker_system_id = conf.get("brokerSystemId")
logger.info("[snap settle data %s] begin" % (json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
logger.info("[get current trading day]......")
sql = """SELECT
DISTINCT t1.tradingday
FROM
siminfo.t_tradesystemtradingday t1,
siminfo.t_tradesystemsettlementgroup t2,
siminfo.t_brokersystemsettlementgroup t3
WHERE t1.tradesystemid = t2.tradesystemid
AND t2.settlementgroupid = t3.settlementgroupid
AND t3.brokersystemid = %s"""
cursor.execute(sql, (broker_system_id,))
row = cursor.fetchone()
current_trading_day = str(row[0])
logger.info("[get current trading day] current_trading_day = %s" % (current_trading_day))
logger.info("[snap order]......")
sql = """INSERT INTO snap.t_s_order(TradingDay,SettlementGroupID,SettlementID,OrderSysID,ParticipantID,ClientID,UserID,InstrumentID,OrderPriceType,Direction,CombOffsetFlag,CombHedgeFlag,LimitPrice,VolumeTotalOriginal,TimeCondition,GTDDate,VolumeCondition,MinVolume,ContingentCondition,StopPrice,ForceCloseReason,OrderLocalID,IsAutoSuspend,OrderSource,OrderStatus,OrderType,VolumeTraded,VolumeTotal,InsertDate,InsertTime,ActiveTime,SuspendTime,UpdateTime,CancelTime,ActiveUserID,Priority,TimeSortID,ClearingPartID,BusinessUnit)
SELECT TradingDay,SettlementGroupID,SettlementID,OrderSysID,ParticipantID,ClientID,UserID,InstrumentID,OrderPriceType,Direction,CombOffsetFlag,CombHedgeFlag,LimitPrice,VolumeTotalOriginal,TimeCondition,GTDDate,VolumeCondition,MinVolume,ContingentCondition,StopPrice,ForceCloseReason,OrderLocalID,IsAutoSuspend,OrderSource,OrderStatus,OrderType,VolumeTraded,VolumeTotal,InsertDate,InsertTime,ActiveTime,SuspendTime,UpdateTime,CancelTime,ActiveUserID,Priority,TimeSortID,ClearingPartID,BusinessUnit
FROM dbclear.t_order WHERE tradingday = %s AND settlementgroupid in (SELECT settlementgroupid FROM siminfo.t_brokersystemsettlementgroup where brokersystemid = %s)"""
cursor.execute(sql, (current_trading_day, broker_system_id,))
logger.info("[snap trade]......")
sql = """INSERT INTO snap.t_s_trade(TradingDay,SettlementGroupID,SettlementID,TradeID,Direction,OrderSysID,ParticipantID,ClientID,TradingRole,AccountID,InstrumentID,OffsetFlag,HedgeFlag,Price,Volume,TradeTime,TradeType,PriceSource,UserID,OrderLocalID,ClearingPartID,BusinessUnit)
SELECT TradingDay,SettlementGroupID,SettlementID,TradeID,Direction,OrderSysID,ParticipantID,ClientID,TradingRole,AccountID,InstrumentID,OffsetFlag,HedgeFlag,Price,Volume,TradeTime,TradeType,PriceSource,UserID,OrderLocalID,ClearingPartID,BusinessUnit
FROM dbclear.t_trade WHERE tradingday = %s AND settlementgroupid in (SELECT settlementgroupid FROM siminfo.t_brokersystemsettlementgroup where brokersystemid = %s)"""
cursor.execute(sql, (current_trading_day, broker_system_id,))
mysql_conn.commit()
except Exception as e:
logger.error("[snap settle data] Error: %s" % (e))
result_code = -1
finally:
mysql_conn.close()
logger.info("[snap settle data] end")
return result_code
def gen_investors(context, conf):
logger = log.get_logger(category="Investors")
_mysql = mysql(configs=context.get("mysql")[conf.get("mysqlId")])
User = dict(columns=("UserID", "Passwd"),
sql="""select InvestorID, Password from siminfo.t_investor"""),
csv_data = _mysql.select(User[0]['sql'])
output = path.convert(context.get("csv")[conf.get("csv")]['quant'])
if not os.path.exists(str(output)):
os.makedirs(str(output))
csv_path = os.path.join(output, "user.csv")
produce_csv(User[0]["columns"], csv_data, csv_path)
def __init__(self, context, configs):
self.TradeSystemID = configs.get("tradeSystemID")
self.SettlementGroupID = configs.get("settlementGroupID")
self.initialfunds = configs.get("initialfunds")
log_conf = None if context.get("log") is None else context.get("log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="exchange_stock_csv", configs=log_conf)
if log_conf is None:
self.logger.warning("exchange_stock_csv未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化生成CSV文件路径
output = path.convert(context.get("csv")[configs.get("csv")]['exchange'])
self.csv_path = os.path.join(output, str(configs.get("tradeSystemID")))
self.__append_extra_content()
def __init__(self, context, configs):
# 初始化settlementGroupID
self.brokerSystemID = configs.get("brokerSystemID")
log_conf = None if context.get("log") is None else context.get(
"log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="sse_to_csv", configs=log_conf)
if log_conf is None:
self.logger.warning("broker_stock_csv未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(
configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化生成柜台CSV文件路径
output = path.convert(context.get("csv")[configs.get("csv")]['broker'])
self.csv_path = os.path.join(output, str(configs.get("csvRoute")))
self.__to_csv()
def settle_stock_userpwd(context, conf):
result_code = 0
logger = log.get_logger(category="SettleStockUserPwd")
logger.info("[settle stock user pwd %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
mdf_list_all = []
mdf_list_1000 = []
user_file = path.convert(conf.get("dumpUserFile"))
input_file = csv.reader(open(user_file))
for line in islice(input_file, 1, None):
if line[7] == "3":
mdf_list_1000.append((
line[4],
line[0],
))
if len(mdf_list_1000) == 1000:
mdf_list_all.append(mdf_list_1000)
mdf_list_1000 = []
if 0 < len(mdf_list_1000) < 1000:
mdf_list_all.append(mdf_list_1000)
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
for mdf_list in mdf_list_all:
sql = """UPDATE siminfo.t_investor SET password = %s, investorstatus = '3' WHERE investorid = %s AND investorstatus != '0'"""
cursor.executemany(sql, mdf_list)
mysql_conn.commit()
except Exception as e:
logger.error("[settle stock user pwd] Error: %s" % (e))
result_code = -1
finally:
mysql_conn.close()
logger.info("[settle stock user pwd] end")
return result_code
def __init__(self, context, configs):
log_conf = None if context.get("log") is None else context.get(
"log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="trans_etfinfo",
configs=log_conf)
if log_conf is None:
self.logger.warning("trans_etfinfo未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(
configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化模板路径
self.initTemplate = context.get("init")[configs.get("initId")]
self.etf_filename = "reff03"
self.SettlementGroupID = configs.get("settlementGroupID")
self.tradesystemid = configs.get("tradesystemid")
self.__transform()
def __init__(self, context, configs):
self.brokerSystemID = configs.get("brokerSystemID")
log_conf = None if context.get("log") is None else context.get(
"log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="stock_market", configs=log_conf)
if log_conf is None:
self.logger.warning("stock_market未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(
configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化生成柜台CSV文件路径
output = path.convert(
context.get("csv")[configs.get("csv")]['stock_exp'])
self.csv_path = output
# 获取行情信息并且生成csv文件
self.__get_stock_market()
def __init__(self, context, configs):
log_conf = None if context.get("log") is None else context.get(
"log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="trans_gold", configs=log_conf)
if log_conf is None:
self.logger.warning("trans_goldinfo未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(
configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化模板路径
self.initTemplate = context.get("init")[configs.get("initId")]
self.tradesystemid = configs.get("tradesystemid")
self.SettlementGroupID = configs.get("settlementGroupID")
self.file_instrument = "gold_instrument.csv"
self.file_marketdata = "gold_depthmarketdata.csv"
# 交易所和结算组对应关系
self.__transform()
def __init__(self, context, configs):
log_conf = None if context.get("log") is None else context.get("log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="trans_future", configs=log_conf)
if log_conf is None:
self.logger.warning("trans_futureinfo未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化模板路径
self.initTemplate = context.get("init")[configs.get("initId")]
self.file_instrument = "future_instrument.csv"
self.file_marketdata = "future_depthmarketdata.csv"
self.file_product = "future_product.csv"
self.file_marginrate = "future_marginrate.csv"
self.tradesystemid = configs.get("tradesystemid")
self.exclude = configs.get("exclude")
# 交易所和结算组对应关系
self.exchange_conf = self.__get_exchange()
self.__transform()
def __init__(self, context, configs):
log_conf = None if context.get("log") is None else context.get(
"log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="trans_stock", configs=log_conf)
if log_conf is None:
self.logger.warning("trans_stock未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(
configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化模板路径
self.initTemplate = context.get("init")[configs.get("initId")]
# 股票文件
self.stock_filename = self.byteify(configs.get("file"))
# DBF文件
self.dbf_file = ["PAR_QY_INFO%y%m%d.dbf"]
self.market = self.byteify(configs.get("market"))
self.tradesystemid = self.byteify(configs.get("tradesystemid"))
self.__transform()
def __init__(self, context, configs):
tradeSystemID = configs.get("tradeSystemID")
log_conf = None if context.get("log") is None else context.get(
"log").get(configs.get("logId"))
# 初始化日志
self.logger = log.get_logger(category="exchange_future_csv",
configs=log_conf)
if log_conf is None:
self.logger.warning("exchange_future_csv未配置Log日志")
# 初始化数据库连接
self.mysqlDB = mysql(
configs=context.get("mysql")[configs.get("mysqlId")])
# 初始化tradeSystemID
self.tradeSystemID = tradeSystemID
# 初始化生成CSV文件路径
output = path.convert(
context.get("csv")[configs.get("csv")]['exchange'])
self.csv_path = os.path.join(output, str(configs.get("tradeSystemID")))
self.__to_csv()
def tinit_md(context, conf):
result_code = 0
logger = log.get_logger(category="tinitMD")
# 初始化数据库连接
mysqlDB = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
# 查询当前交易日
sql = """SELECT t1.tradingday FROM siminfo.t_tradesystemtradingday t1 WHERE t1.tradesystemid = %s"""
res = mysqlDB.select(sql, (conf.get("tradesystemid"), ))
current_trading_day = str(res[0][0])
# 拷贝目标地址
data_target_dir = conf.get("baseDataHome")
real_dir_path = path.convert(data_target_dir)
if not os.path.exists(real_dir_path):
os.makedirs(str(real_dir_path))
syncs = conf.get("SyncsFrom")
for sources in syncs:
for index, file in enumerate(sources['items']):
file = file.replace("%y", current_trading_day[0:4]) \
.replace("%m", current_trading_day[4:6]) \
.replace("%d", current_trading_day[6:8])
sources['items'][index] = file
rsync_config = {"type": "get", "target": data_target_dir, "Syncs": syncs}
logger.info("[sync all csvs with %s] begin" %
json.dumps(rsync_config, encoding="UTF-8", ensure_ascii=False))
try:
rsync.rsync_groups(context, rsync_config)
except Exception as e:
logger.info("[sync all csvs with %s] Error: %s" % (json.dumps(
rsync_config, encoding="UTF-8", ensure_ascii=False), e))
result_code = -1
logger.info("[sync all csvs with %s] end" %
json.dumps(rsync_config, encoding="UTF-8", ensure_ascii=False))
rsync.rsync_groups(context, conf)
return result_code
def start_http_server(context, conf):
logger = log.get_logger(category="HttpServer")
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
app = tornado.web.Application([
(r"/openAccount", OpenAccountHandler,
dict(database=mysql_pool, logger=logger)),
(r"/openVIPAccount", OpenVIPAccountHandler,
dict(database=mysql_pool, logger=logger)),
(r"/joinActivity", JoinActivityHandler,
dict(database=mysql_pool, logger=logger)),
(r"/queryActivityRanking", QueryActivityRankingHandler,
dict(database=mysql_pool, logger=logger)),
(r"/queryActivityJoinStatus", QueryActivityJoinStatusHandler,
dict(database=mysql_pool, logger=logger)),
])
app.listen(conf["port"])
tornado.ioloop.IOLoop.current().start()
def sync_dump_csvs(context, conf):
result_code = 0
logger = log.get_logger(category="SyncDumpCsvs")
trade_system_id = conf.get("tradeSystemId")
settlement_id = conf.get("settlementId")
# 初始化数据库连接
mysqlDB = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
# 查询当前交易日
sql = """select DAY from siminfo.t_tradingcalendar where day <=
(select TradingDay from siminfo.t_tradesystemtradingday where TradeSystemID = %s)
and Tra = 1 order by Day desc limit 1"""
res = mysqlDB.select(sql, (trade_system_id, ))
current_trading_day = str(res[0][0])
# 拷贝目标地址
base_dir = conf.get("baseDataHome")
data_target_dir = os.path.join(base_dir, trade_system_id, settlement_id)
syncs = conf.get("Syncs")
for sources in syncs:
for index, file in enumerate(sources['items']):
file = file.replace("%y", current_trading_day[0:4]) \
.replace("%m", current_trading_day[4:6]) \
.replace("%d", current_trading_day[6:8])
sources['items'][index] = file
rsync_config = {"type": "get", "target": data_target_dir, "Syncs": syncs}
logger.info("[sync dump csvs with %s] begin" %
json.dumps(rsync_config, encoding="UTF-8", ensure_ascii=False))
try:
rsync.rsync_groups(context, rsync_config)
except Exception as e:
logger.info("[sync dump csvs with %s] Error: %s" % (json.dumps(
rsync_config, encoding="UTF-8", ensure_ascii=False), e))
result_code = -1
logger.info("[sync dump csvs with %s] end" %
json.dumps(rsync_config, encoding="UTF-8", ensure_ascii=False))
return result_code
def publish_etf(context, conf):
result_code = 0
logger = log.get_logger(category="PublishEtfBroker")
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
broker_system_id = conf.get("brokerSystemId")
logger.info("[publish etf broker %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
logger.info("[get current trading day]......")
sql = """SELECT DISTINCT t1.tradingday, t1.lasttradingday
FROM siminfo.t_tradesystemtradingday t1,
siminfo.t_tradesystemsettlementgroup t2,
siminfo.t_brokersystemsettlementgroup t3
WHERE t1.tradesystemid = t2.tradesystemid
AND t2.settlementgroupid = t3.settlementgroupid
AND t3.brokersystemid = %s"""
cursor.execute(sql, (broker_system_id, ))
row = cursor.fetchone()
current_trading_day = str(row[0])
last_trading_day = str(row[1])
logger.info(
"[get current trading day] current_trading_day = %s, last_trading_day = %s"
% (current_trading_day, last_trading_day))
logger.info("[get next trading day]......")
# 判断是否跳过节假日
holiday = conf.get("holiday")
if holiday is True or holiday is None:
sql = """SELECT DAY FROM siminfo.t_TradingCalendar t WHERE t.day > %s AND t.tra = '1' ORDER BY DAY LIMIT 1"""
else:
sql = """SELECT DAY FROM siminfo.t_TradingCalendar t WHERE t.day > %s ORDER BY DAY LIMIT 1"""
cursor.execute(sql, (current_trading_day, ))
row = cursor.fetchone()
next_trading_day = str(row[0])
logger.info("[get next trading day] next_trading_day = %s" %
next_trading_day)
# 投资者资金预处理
sql = """UPDATE siminfo.t_investorfund t1
SET t1.prebalance = t1.balance,
t1.available = t1.balance,
t1.prestockvalue = t1.stockvalue,
t1.PreMargin = t1.CurrMargin,
t1.stockvalue = 0,
t1.premium = 0,
t1.currmargin = 0,
t1.fee = 0,
t1.premonthasset = IF(MONTH(%s) - MONTH(%s) = 0, t1.premonthasset, t1.currentasset),
t1.preweekasset = IF(WEEK(%s, 1) - WEEK(%s, 1) = 0, t1.preweekasset, t1.currentasset),
t1.preasset = t1.currentasset,
t1.currentasset = t1.balance
WHERE t1.brokersystemid = %s"""
cursor.execute(sql, (
current_trading_day,
last_trading_day,
current_trading_day,
last_trading_day,
broker_system_id,
))
for settlement_groups in conf.get("settlementGroups"):
settlement_group_id = settlement_groups.get("settlementGroupId")
settlement_id = settlement_groups.get("settlementId")
# 检查结算状态
logger.info("[check %s settlement status]......" %
settlement_group_id)
sql = """SELECT t1.tradingday, t1.settlementgroupid, t1.settlementid, t1.settlementstatus
FROM dbclear.t_settlement t1
WHERE t1.tradingday = %s
AND t1.settlementgroupid = %s
AND t1.settlementid = %s for update"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
row = cursor.fetchone()
if row is None:
logger.error(
"[publish etf broker] Error: There is no data for %s-%s." %
(settlement_group_id, settlement_id))
result_code = -1
elif row[3] == '0':
logger.error(
"[publish etf broker] Error: Settlement for %s-%s has not done."
% (settlement_group_id, settlement_id))
result_code = -1
elif row[3] == '2':
logger.error(
"[publish etf broker] Error: Settlement for %s-%s has been published."
% (settlement_group_id, settlement_id))
result_code = -1
else:
# 更新客户持仓 t_clientposition
logger.info("[update %s client position]......" %
settlement_group_id)
sql = """DELETE FROM siminfo.t_clientposition WHERE settlementgroupid = %s"""
cursor.execute(sql, (settlement_group_id, ))
sql = """INSERT INTO siminfo.t_clientposition (TradingDay,SettlementGroupID,SettlementID,HedgeFlag,
PosiDirection,YdPosition,Position,LongFrozen,ShortFrozen,YdLongFrozen,
YdShortFrozen,BuyTradeVolume,SellTradeVolume,PositionCost,YdPositionCost,
UseMargin,FrozenMargin,LongFrozenMargin,ShortFrozenMargin,FrozenPremium,
InstrumentID,ParticipantID,ClientID
)SELECT %s,
SettlementGroupID,
SettlementID,
HedgeFlag,
PosiDirection,
Position,
0,
0,
0,
LongFrozen,
ShortFrozen,
0,
0,
0,
PositionCost + YdPositionCost,
UseMargin,
0,
0,
0,
FrozenPremium,
InstrumentID,
ParticipantID,
ClientID
FROM
dbclear.t_clientposition t
WHERE
t.tradingday = %s AND t.settlementgroupid = %s AND t.settlementid = %s
AND t.Position != 0"""
cursor.execute(sql, (next_trading_day, current_trading_day,
settlement_group_id, settlement_id))
# 更新会员持仓 t_partposition
logger.info("[update %s part position]......" %
settlement_group_id)
sql = """DELETE FROM siminfo.t_partposition WHERE settlementgroupid = %s"""
cursor.execute(sql, (settlement_group_id, ))
sql = """INSERT INTO siminfo.t_partposition ( TradingDay, SettlementGroupID, SettlementID, HedgeFlag, PosiDirection, YdPosition, Position, LongFrozen, ShortFrozen, YdLongFrozen, YdShortFrozen, InstrumentID, ParticipantID, TradingRole ) SELECT
%s,
SettlementGroupID,
SettlementID,
HedgeFlag,
PosiDirection,
Position,
0,
0,
0,
LongFrozen,
ShortFrozen,
InstrumentID,
ParticipantID,
TradingRole
FROM
dbclear.t_partposition t
WHERE
t.tradingday = %s
AND t.settlementgroupid = %s
AND t.settlementid = %s"""
cursor.execute(sql, (next_trading_day, current_trading_day,
settlement_group_id, settlement_id))
# 更新客户资金 t_clientfund
logger.info("[update %s client fund]......" %
settlement_group_id)
sql = """DELETE FROM siminfo.t_clientfund WHERE settlementgroupid = %s"""
cursor.execute(sql, (settlement_group_id, ))
sql = """INSERT INTO siminfo.t_clientfund ( TradingDay, SettlementGroupID, SettlementID, ParticipantID, ClientID, AccountID, Available, TransFee, DelivFee, PositionMargin, Profit, StockValue )SELECT
%s,
SettlementGroupID,
SettlementID,
ParticipantID,
ClientID,
AccountID,
Available,
TransFee,
DelivFee,
PositionMargin,
Profit,
StockValue
FROM
dbclear.t_clientfund t
WHERE
t.tradingday = %s
AND t.settlementgroupid = %s
AND t.settlementid = %s
AND (
t.available != 0
OR t.transfee != 0
OR t.delivfee != 0
OR t.positionmargin != 0
OR t.profit != 0
OR t.stockvalue != 0 )"""
cursor.execute(sql, (next_trading_day, current_trading_day,
settlement_group_id, settlement_id))
# 更新投资者资金 t_investorfund
logger.info("[update %s investor fund]......" %
settlement_group_id)
sql = """UPDATE siminfo.t_investorfund t1,
(SELECT
t3.brokersystemid,
t1.investorid,
t2.available,
t2.transfee,
t2.DelivFee,
t2.positionmargin,
t2.profit,
t2.stockvalue
FROM
siminfo.t_investorclient t1,
siminfo.t_clientfund t2,
siminfo.t_brokersystemsettlementgroup t3
WHERE
t1.settlementgroupid = t2.settlementgroupid
AND t1.settlementgroupid = t3.settlementgroupid
AND t1.clientid = t2.clientid
AND t2.tradingday = %s
AND t1.settlementgroupid = %s
AND t2.settlementid = %s
) t2
SET t1.balance = t1.balance + t2.available - t2.transfee - t2.DelivFee + t2.profit,
t1.available = t1.available + t2.available - t2.transfee - t2.DelivFee + t2.profit - t2.positionmargin,
t1.fee = t1.fee + t2.transfee,
t1.currmargin = t1.currmargin + t2.positionmargin,
t1.premium = t1.premium + t2.available,
t1.currentasset = t1.currentasset + t2.available - t2.transfee - t2.DelivFee + t2.stockvalue + t2.profit
WHERE
t1.brokersystemid = t2.brokersystemid
AND t1.investorid = t2.investorid"""
cursor.execute(
sql,
(next_trading_day, settlement_group_id, settlement_id))
# 更新行情 t_marketdata
logger.info("[update %s marketdata]......" %
settlement_group_id)
sql = """DELETE FROM siminfo.t_marketdata WHERE settlementgroupid = %s"""
cursor.execute(sql, (settlement_group_id, ))
sql = """INSERT INTO siminfo.t_marketdata (TradingDay,SettlementGroupID,LastPrice,PreSettlementPrice,
PreClosePrice,UnderlyingClosePx,PreOpenInterest,OpenPrice,HighestPrice,LowestPrice,Volume,Turnover,
OpenInterest,ClosePrice,SettlementPrice,UpperLimitPrice,LowerLimitPrice,PreDelta,
CurrDelta,UpdateTime,UpdateMillisec,InstrumentID) SELECT
%s,
SettlementGroupID,
NULL,
SettlementPrice,
ClosePrice,
UnderlyingClosePx,
OpenInterest,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
UpdateTime,
UpdateMillisec,
InstrumentID
FROM dbclear.t_marketdata t
WHERE t.tradingday = %s AND t.settlementgroupid = %s AND t.settlementid = %s"""
cursor.execute(sql, (next_trading_day, current_trading_day,
settlement_group_id, settlement_id))
# 更新结算状态
logger.info("[update %s settlement status]......" %
settlement_group_id)
sql = """UPDATE dbclear.t_settlement SET settlementstatus = '2'
WHERE tradingday = %s AND settlementgroupid = %s
AND settlementid = %s AND settlementstatus = '1'"""
cursor.execute(
sql,
(current_trading_day, settlement_group_id, settlement_id))
mysql_conn.commit()
except Exception as e:
logger.error("[publish etf broker] Error: %s" % e)
result_code = -1
finally:
mysql_conn.close()
logger.info("[publish etf broker] end")
return result_code
def settle_activity(context, conf):
result_code = 0
logger = log.get_logger(category="SettleActivity")
bench_stock_id = conf.get("BenchStockID", "sh000300")
statistic_rank_no = conf.get("StatisticRankNo", 10)
logger.info("[settle activity %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
# 结算正在进行的赛事数据
sql = """SELECT activityid, termno FROM siminfo.t_activity WHERE activitystatus != '2'"""
cursor.execute(sql)
rows = cursor.fetchall()
activities = []
for row in rows:
activities.append((str(row[0]), int(row[1])))
for activity_id, term_no in activities:
# 获取当前交易日
logger.info(
"[get current trading day, last trading day for activity %s]......"
% activity_id)
sql = """SELECT DISTINCT t1.tradingday, t1.lasttradingday FROM siminfo.t_tradesystemtradingday t1, siminfo.t_tradesystemsettlementgroup t2, siminfo.t_activitysettlementgroup t3
WHERE t1.tradesystemid = t2.tradesystemid AND t2.settlementgroupid = t3.settlementgroupid AND t3.activityid = %s"""
cursor.execute(sql, (activity_id, ))
rows = cursor.fetchall()
row = rows[0]
current_trading_day = str(row[0])
last_trading_day = str(row[1])
logger.info(
"[get current trading day, last trading day for activity %s] current_trading_day = %s, last_trading_day = %s"
% (activity_id, current_trading_day, last_trading_day))
sql = """SELECT activitystatus FROM siminfo.t_activity WHERE activityid = %s AND termno = %s"""
cursor.execute(sql, (
activity_id,
term_no,
))
row = cursor.fetchone()
activity_status = str(row[0])
if "0" == activity_status:
sql = """DELETE FROM siminfo.t_activityinvestorevaluation WHERE activityid = %s AND termno = %s"""
cursor.execute(sql, (
activity_id,
term_no,
))
# 赛事开始状态设置
sql = """UPDATE siminfo.t_activity
SET
activitystatus =
CASE
WHEN begindate <= %s
AND enddate > %s
THEN '1'
ELSE activitystatus
END
WHERE activityid = %s AND termno = %s AND activitystatus = '0'"""
cursor.execute(sql, (current_trading_day, current_trading_day,
activity_id, term_no))
sql = """SELECT activitystatus,initialbalance,joinmode,rankingrule,activitytype FROM siminfo.t_activity WHERE activityid = %s AND termno = %s"""
cursor.execute(sql, (activity_id, term_no))
row = cursor.fetchone()
if "0" == str(row[0]):
continue
initial_balance = str(row[1])
join_mode = str(row[2])
ranking_rule = str(row[3])
activity_type = str(row[4])
if activity_status != str(row[0]) and "1" == str(row[0]):
sql = """UPDATE siminfo.t_activity
SET
termno = %s
WHERE activityid = %s AND termno = %s"""
cursor.execute(sql, (term_no + 1, activity_id, term_no))
term_no += 1
if activity_type == '0':
# 默认赛事投资者数据
logger.info("[insert default activity investor]......")
sql = """INSERT INTO siminfo.t_activityinvestor(activityid, termno, investorid, joindate, joinstatus)
SELECT %s, %s, t.investorid, DATE_FORMAT(NOW(), '%Y%m%d'), '0'
FROM siminfo.t_investor t
WHERE t.investoraccounttype = '0' and t.investorstatus = '1'
AND (t.investorid > (SELECT MAX(investorid) FROM siminfo.t_activityinvestor t1 WHERE t1.activityid = %s)
OR t.investorid < (SELECT MIN(investorid) FROM siminfo.t_activityinvestor t2 WHERE t2.activityid = %s)
OR (SELECT count(investorid) FROM siminfo.t_activityinvestor t2 WHERE t2.activityid = %s AND t.investorid = t2.investorid) = 0)"""
cursor.execute(sql, (activity_id, term_no, activity_id,
activity_id, activity_id))
if join_mode == '2':
# 赛事新参与投资者数据重置
logger.info("[reset new activity investor data]......")
sql = """UPDATE siminfo.t_investorfund t1, siminfo.t_activityinvestor t2
SET
t1.PreBalance = 0,
t1.CurrMargin = 0,
t1.CloseProfit = 0,
t1.Premium = 0,
t1.Deposit = 0,
t1.Withdraw = 0,
t1.Balance = t1.InitialAsset,
t1.Available = t1.InitialAsset,
t1.PreMargin = 0,
t1.FuturesMargin = 0,
t1.OptionsMargin = 0,
t1.PositionProfit = 0,
t1.Profit = 0,
t1.Interest = 0,
t1.Fee = 0,
t1.TotalCollateral = 0,
t1.CollateralForMargin = 0,
t1.PreAccmulateInterest = 0,
t1.AccumulateInterest = 0,
t1.AccumulateFee = 0,
t1.ForzenDeposit = 0,
t1.AccountStatus = 0,
t1.PreMonthAsset = t1.InitialAsset,
t1.PreWeekAsset = t1.InitialAsset,
t1.PreAsset = t1.InitialAsset,
t1.CurrentAsset = t1.InitialAsset,
t1.PreStockValue = 0,
t1.StockValue = 0
WHERE t1.brokersystemid IN (SELECT DISTINCT
t2.brokersystemid
FROM
siminfo.t_activitysettlementgroup t1,
siminfo.t_brokersystemsettlementgroup t2
WHERE t1.settlementgroupid = t2.settlementgroupid
AND t1.activityid = %s)
AND t1.investorid = t2.investorid
AND t2.activityid = %s
AND t2.joinstatus = '0'"""
cursor.execute(sql, (initial_balance, initial_balance,
activity_id, activity_id))
sql = """DELETE FROM siminfo.t_clientposition
WHERE clientid IN (SELECT clientid FROM siminfo.t_investorclient t1, siminfo.t_activityinvestor t2, siminfo.t_activitysettlementgroup t3
WHERE t1.investorid = t2.investorid AND t2.joinstatus = '0' AND t1.settlementgroupid = t3.settlementgroupid
AND t2.activityid = t3.activityid AND t2.activityid = %s)"""
cursor.execute(sql, (activity_id, ))
sql = """DELETE FROM siminfo.t_clientpositionforsecurityprofit
WHERE clientid IN (SELECT clientid FROM siminfo.t_investorclient t1, siminfo.t_activityinvestor t2, siminfo.t_activitysettlementgroup t3
WHERE t1.investorid = t2.investorid AND t2.joinstatus = '0' AND t1.settlementgroupid = t3.settlementgroupid
AND t2.activityid = t3.activityid AND t2.activityid = %s)"""
cursor.execute(sql, (activity_id, ))
# 赛事新参与投资者评估信息
sql = """INSERT INTO siminfo.t_activityinvestorevaluation(ActivityID,TermNo, InvestorID,InitialAsset,PreMonthAsset, PreWeekAsset,PreAsset,CurrentAsset,TotalReturnRate,ReturnRateOfMonth,ReturnRateOfWeek,ReturnRateOf1Day)
SELECT t2.activityid, %s, t1.investorid, SUM(t1.preasset) AS initialasset, SUM(t1.premonthasset) AS premonthasset, SUM(t1.preweekasset) AS preweekasset, SUM(t1.preasset) AS preasset, SUM(t1.currentasset) AS currasset, 0, 0, 0, 0 FROM siminfo.t_investorfund t1,
(SELECT DISTINCT t1.activityid, t2.brokersystemid, t3.investorid FROM siminfo.t_activitysettlementgroup t1, siminfo.t_brokersystemsettlementgroup t2, siminfo.t_activityinvestor t3, siminfo.t_activity t4
WHERE t1.activityid = %s AND t3.joinstatus = '0' AND t1.settlementgroupid = t2.settlementgroupid AND t1.activityid = t3.activityid AND t1.activityid = t4.activityid AND t4.activitystatus = '1') t2
WHERE t1.investorid = t2.investorid AND t1.brokersystemid = t2.brokersystemid
GROUP BY t2.activityid, t1.investorid"""
cursor.execute(sql, (
term_no,
activity_id,
))
# 更新投资者参赛状态
sql = """UPDATE siminfo.t_activityinvestor SET joinstatus = '1' WHERE activityid = %s AND joinstatus = '0'"""
cursor.execute(sql, (activity_id, ))
# 更新投资者赛事活动评估信息
logger.info("[calculate investor activity evaluation]......")
sql = """UPDATE siminfo.t_activityinvestorevaluation t1
SET t1.preasset = t1.currentasset,
t1.preranking = t1.ranking, t1.ranking = 0, t1.rankingstatus = '0'
WHERE t1.activityid = %s and t1.termno = %s"""
cursor.execute(sql, (activity_id, term_no))
sql = """UPDATE siminfo.t_activityinvestorevaluation t1,(
SELECT t2.activityid, t1.investorid, SUM(t1.premonthasset) AS premonthasset, SUM(t1.preweekasset) AS preweekasset, SUM(t1.preasset) AS preasset, SUM(t1.currentasset) AS currasset FROM siminfo.t_investorfund t1,
(SELECT DISTINCT t1.activityid, t2.brokersystemid, t3.investorid FROM siminfo.t_activitysettlementgroup t1, siminfo.t_brokersystemsettlementgroup t2, siminfo.t_activityinvestor t3
WHERE t1.activityid = %s AND t1.settlementgroupid = t2.settlementgroupid AND t1.activityid = t3.activityid) t2
WHERE t1.investorid = t2.investorid AND t1.brokersystemid = t2.brokersystemid
GROUP BY t2.activityid, t1.investorid) t2
SET t1.currentasset = t2.currasset, t1.premonthasset = t2.premonthasset, t1.preweekasset = t2.preweekasset, t1.preasset = t2.preasset
WHERE t1.activityid = t2.activityid AND t1.investorid = t2.investorid AND t1.termno = %s"""
cursor.execute(sql, (activity_id, term_no))
# 计算月盈利率、周盈利率
sql = """UPDATE siminfo.t_activityinvestorevaluation t1
SET t1.totalreturnrate = IF(t1.initialasset =0 , 0, round((t1.currentasset - t1.initialasset) / t1.initialasset, 4)),
t1.returnrateof1day = IF(t1.preasset = 0, 0, round((t1.currentasset - t1.preasset) / t1.preasset, 4)),
t1.returnrateofmonth = IF(t1.premonthasset = 0, 0, round((t1.currentasset - t1.premonthasset) / t1.premonthasset, 4)),
t1.returnrateofweek = IF(t1.preweekasset = 0, 0, round((t1.currentasset - t1.preweekasset) / t1.preweekasset, 4))
WHERE t1.activityid = %s AND t1.termno = %s"""
cursor.execute(sql, (activity_id, term_no))
if ranking_rule == "00":
# 排序规则为00时，全部参与排序
sql = """UPDATE siminfo.t_activityinvestorevaluation t
SET t.rankingstatus = 1
WHERE t.activityid = %s and t.termno = %s"""
cursor.execute(sql, (
activity_id,
term_no,
))
elif ranking_rule == "01":
# 排序规则为01时，根据投资者设置确定是否参与排名
sql = """UPDATE siminfo.t_activityinvestorevaluation t, siminfo.t_activityinvestor t1
SET t.rankingstatus = '1'
WHERE t.activityid = %s AND t.termno = %s AND t.activityid = t1.activityid AND t.investorid = t1.investorid AND t1.rankable = '1'"""
cursor.execute(sql, (
activity_id,
term_no,
))
elif ranking_rule == "02":
# 排序规则为02时，不排名
sql = """UPDATE siminfo.t_activityinvestorevaluation t
SET t.rankingstatus = '0'
WHERE t.activityid = %s AND t.termno = %s"""
cursor.execute(sql, (
activity_id,
term_no,
))
else:
# 根据是否真实开户设置rankingstatus，真实开户置为1，否则置为0
sql = """UPDATE siminfo.t_activityinvestorevaluation t, (SELECT activityid, investorid FROM siminfo.t_activityrankableinvestor WHERE activityid = %s) t1
SET t.rankingstatus = 1
WHERE t.activityid = %s AND t.termno = %s AND t.activityid = t1.activityid AND t.investorid = t1.investorid"""
cursor.execute(sql, (
activity_id,
activity_id,
term_no,
))
# 废弃用户不参与排名
sql = """UPDATE siminfo.t_activityinvestorevaluation t1, (SELECT investorid FROM siminfo.t_investor WHERE openid LIKE '%_o') t2
SET t1.rankingstatus = '0' WHERE t1.activityid = %s AND t1.termno = %s AND t1.investorid = t2.investorid"""
cursor.execute(sql, (
activity_id,
term_no,
))
# 根据是否参与交易设置rankingstatus，昨资产或今资产不为初始资产置为1，否则置为0
sql = """UPDATE siminfo.t_activityinvestorevaluation t
SET t.rankingstatus = 0
WHERE t.activityid = %s AND t.termno = %s AND t.preasset = t.initialasset AND t.currentasset = t.initialasset"""
cursor.execute(sql, (
activity_id,
term_no,
))
# 设置总收益率排名
sql = """UPDATE siminfo.t_activityinvestorevaluation t,
(SELECT t.activityid, t.termno, t.investorid, t.newranking FROM (SELECT t.activityid, t.termno, t.investorid, (@i:=@i+1) AS newranking FROM siminfo.t_activityinvestorevaluation t,(SELECT @i:=0) AS it
WHERE t.activityid = %s AND t.termno = %s AND t.rankingstatus = '1'
ORDER BY t.totalreturnrate DESC, t.currentasset DESC, t.returnrateof1day DESC, t.investorid) t) t1
SET t.ranking = t1.newranking
WHERE t.activityid = t1.activityid AND t.termno = t1.termno AND t.investorid = t1.investorid"""
cursor.execute(sql, (
activity_id,
term_no,
))
# 设置参与人数
logger.info("[update activity joincount]......")
sql = """UPDATE siminfo.t_activity t,
(SELECT COUNT(1) as joincount FROM siminfo.t_activityinvestor t3 WHERE t3.activityid = %s) t1
SET t.joincount = t1.joincount
WHERE t.activityid = %s AND t.termno = %s"""
cursor.execute(sql, (
activity_id,
activity_id,
term_no,
))
# 更新赛事持仓、资金数据
logger.info("[update activity investor fund and position]......")
sql = """DELETE FROM siminfo.t_activityinvestorfund WHERE activityid = %s AND termno = %s"""
cursor.execute(sql, (
activity_id,
term_no,
))
sql = """INSERT INTO siminfo.t_activityinvestorfund(TradingDay,ActivityID,TermNo,BrokerSystemID,InvestorID,PreBalance,CurrMargin,CloseProfit,Premium,Deposit,Withdraw,Balance,Available,PreMargin,FuturesMargin,OptionsMargin,PositionProfit,Profit,Interest,Fee,TotalCollateral,CollateralForMargin,PreAccmulateInterest,AccumulateInterest,AccumulateFee,ForzenDeposit,
AccountStatus,InitialAsset,PreMonthAsset,PreWeekAsset,PreAsset,CurrentAsset,PreStockValue,StockValue)
SELECT distinct %s,%s,%s,t1.BrokerSystemID,t1.InvestorID,t1.PreBalance,t1.CurrMargin,t1.CloseProfit,t1.Premium,t1.Deposit,t1.Withdraw,t1.Balance,t1.Available,t1.PreMargin,t1.FuturesMargin,t1.OptionsMargin,t1.PositionProfit,t1.Profit,t1.Interest,t1.Fee,t1.TotalCollateral,t1.CollateralForMargin,t1.PreAccmulateInterest,t1.AccumulateInterest,t1.AccumulateFee,t1.ForzenDeposit,t1.AccountStatus,
t1.InitialAsset,t1.PreMonthAsset,t1.PreWeekAsset,t1.PreAsset,t1.CurrentAsset,t1.PreStockValue,t1.StockValue
FROM siminfo.t_investorfund t1, (SELECT DISTINCT
t2.brokersystemid
FROM
siminfo.t_activitysettlementgroup t1,
siminfo.t_brokersystemsettlementgroup t2
WHERE t1.settlementgroupid = t2.settlementgroupid
AND t1.activityid = %s) t2, siminfo.t_activityinvestor t3
WHERE t1.brokersystemid = t2.brokersystemid AND t1.investorid = t3.investorid AND t3.activityid = %s"""
cursor.execute(sql, (
last_trading_day,
activity_id,
term_no,
activity_id,
activity_id,
))
sql = """DELETE FROM siminfo.t_activityinvestorposition WHERE activityid = %s and termno = %s"""
cursor.execute(sql, (
activity_id,
term_no,
))
sql = """INSERT INTO siminfo.t_activityinvestorposition(TradingDay,SettlementGroupID,SettlementID,HedgeFlag,PosiDirection,YdPosition,Position,LongFrozen,ShortFrozen,YdLongFrozen,YdShortFrozen,BuyTradeVolume,SellTradeVolume,PositionCost,YdPositionCost,UseMargin,FrozenMargin,LongFrozenMargin,ShortFrozenMargin,
FrozenPremium,InstrumentID,ParticipantID,ClientID,InvestorID,ActivityID,TermNo)
SELECT distinct %s,t1.SettlementGroupID,t1.SettlementID,t1.HedgeFlag,t1.PosiDirection,t1.YdPosition,t1.Position,t1.LongFrozen,t1.ShortFrozen,t1.YdLongFrozen,t1.YdShortFrozen,t1.BuyTradeVolume,t1.SellTradeVolume,t1.PositionCost,t1.YdPositionCost,t1.UseMargin,t1.FrozenMargin,t1.LongFrozenMargin,t1.ShortFrozenMargin,
t1.FrozenPremium,t1.InstrumentID,t1.ParticipantID,t1.ClientID,t3.InvestorID,%s,%s
FROM siminfo.t_clientposition t1, (SELECT DISTINCT
t1.settlementgroupid
FROM
siminfo.t_activitysettlementgroup t1
WHERE t1.activityid = %s) t2, siminfo.t_investorclient t3, siminfo.t_activityinvestor t4
WHERE t1.settlementgroupid = t2.settlementgroupid AND t1.clientid = t3.clientid AND t1.settlementgroupid = t3.settlementgroupid
AND t3.investorid = t4.investorid AND t4.activityid = %s AND t1.tradingday = %s"""
cursor.execute(sql, (
last_trading_day,
activity_id,
term_no,
activity_id,
activity_id,
current_trading_day,
))
# 更新赛事前N平均收益率和基准收益率
sql = """INSERT INTO siminfo.t_tradematchdailyavgreturndata(TradingDay, StatisticRankNo, ActivityID, TermNo, MatchTotalProfit, BenchmarkTotalProfit)
SELECT
%s AS tradingday,
%s AS statisticrankno,
t1.activityid,
t1.termno,
t1.MatchTotalProfit,
ROUND(
(
t3.closingprice - t2.initialclosingprice
) / t2.initialclosingprice,
4
) AS BenchmarkTotalProfit
FROM
(SELECT
ActivityID,
TermNo,
ROUND(AVG(TotalReturnRate), 4) AS MatchTotalProfit
FROM
(SELECT
a.activityid,
a.termno,
a.investorid,
(@i := @i + 1) AS newranking,
a.totalreturnrate
FROM
siminfo.t_activityinvestorevaluation a,
(SELECT
@i := 0) AS it
WHERE a.ActivityID = %s
-- AND a.RankingStatus = '1'
AND a.termNo = %s
ORDER BY ActivityID,
TermNo,
a.totalreturnrate DESC,
a.currentasset DESC,
a.returnrateof1day DESC,
a.investorid) b
WHERE newranking <= %s
GROUP BY ActivityID,
TermNo) t1,
(SELECT
t.lastclosingprice AS initialclosingprice
FROM
siminfo.t_benchmarkmarket t
WHERE t.stockid = %s AND t.tradingday =
(SELECT
MIN(t1.day)
FROM
siminfo.t_tradingcalendar t1
WHERE t1.day >=
(SELECT
t.begindate
FROM
siminfo.t_activity t
WHERE t.activityid = %s
AND t.termno = %s)
AND t1.tra = 1)) t2,
siminfo.t_benchmarkmarket t3
WHERE t3.stockid = %s AND t3.tradingday = %s"""
#cursor.execute(sql, (last_trading_day, statistic_rank_no, activity_id, term_no, statistic_rank_no, bench_stock_id, activity_id, term_no, bench_stock_id, last_trading_day, ))
# 赛事结束状态设置
sql = """UPDATE siminfo.t_activity
SET
activitystatus =
CASE
WHEN enddate < %s
THEN '2'
ELSE activitystatus
END
WHERE activityid = %s AND termno = %s AND activitystatus = '1'"""
cursor.execute(sql, (
current_trading_day,
activity_id,
term_no,
))
# 循环赛生成新一期赛事
if activity_type == "2":
sql = """SELECT activitystatus,initialbalance,joinmode,rankingrule,activitytype,circlefreq,duration FROM siminfo.t_activity WHERE activityid = %s AND termno = %s"""
cursor.execute(sql, (activity_id, term_no))
row = cursor.fetchone()
if "2" == str(row[0]):
circle_freq = str(row[5])
duration = int(row[6])
begin_date = current_trading_day[0:6] + "01"
end_date = None
if circle_freq == "1":
sql = """SELECT MAX(t.day) FROM siminfo.t_tradingcalendar t WHERE t.day LIKE CONCAT(SUBSTR(DATE_FORMAT(DATE_ADD(%s, INTERVAL %s QUARTER), '%Y%m%d'), 1, 6), '%') """
cursor.execute(sql, (last_trading_day, duration))
row = cursor.fetchone()
end_date = str(row[0])
elif circle_freq == "2":
sql = """SELECT MAX(t.day) FROM siminfo.t_tradingcalendar t WHERE t.day LIKE CONCAT(SUBSTR(DATE_FORMAT(DATE_ADD(%s, INTERVAL %s MONTH), '%Y%m%d'), 1, 6), '%') """
cursor.execute(sql, (last_trading_day, duration))
row = cursor.fetchone()
end_date = str(row[0])
if end_date is not None:
logger.info(
"[gen term %s of circle activity %s]......" % (
term_no + 1,
activity_id,
))
sql = """INSERT INTO siminfo.t_activity(activityid, termno, activityname, activitytype, activitystatus, initialbalance, joinmode, rankingrule, CircleFreq, Duration, JoinCount, createdate, createtime, begindate, enddate, updatedate, updatetime)
SELECT %s, t.termno+1, activityname, activitytype, '1', initialbalance, joinmode, rankingrule, CircleFreq, Duration, JoinCount, DATE_FORMAT(NOW(), '%Y%m%d'), DATE_FORMAT(NOW(), '%H:%i:%S'), %s, %s, DATE_FORMAT(NOW(), '%Y%m%d'), DATE_FORMAT(NOW(), '%H:%i:%S')
FROM siminfo.t_activity t
WHERE t.activityid = %s AND t.termno = %s"""
cursor.execute(sql, (activity_id, begin_date, end_date,
activity_id, term_no))
sql = """INSERT INTO siminfo.t_activityinvestorevaluation(ActivityID,TermNo, InvestorID,InitialAsset,PreMonthAsset, PreWeekAsset,PreAsset,CurrentAsset,TotalReturnRate,ReturnRateOfMonth,ReturnRateOfWeek,ReturnRateOf1Day)
SELECT %s, termno+1,InvestorID,CurrentAsset,PreMonthAsset, PreWeekAsset,PreAsset,CurrentAsset,0,0,0,0
FROM siminfo.t_activityinvestorevaluation
WHERE activityid = %s AND termno = %s"""
cursor.execute(sql, (
activity_id,
activity_id,
term_no,
))
# 插入资金、持仓数据
sql = """INSERT INTO siminfo.t_activityinvestorfund(TradingDay,ActivityID,TermNo,BrokerSystemID,InvestorID,PreBalance,CurrMargin,CloseProfit,Premium,Deposit,Withdraw,Balance,Available,PreMargin,FuturesMargin,OptionsMargin,PositionProfit,Profit,Interest,Fee,TotalCollateral,CollateralForMargin,PreAccmulateInterest,AccumulateInterest,AccumulateFee,ForzenDeposit,
AccountStatus,InitialAsset,PreMonthAsset,PreWeekAsset,PreAsset,CurrentAsset,PreStockValue,StockValue)
SELECT %s,%s,%s,t1.BrokerSystemID,t1.InvestorID,t1.PreBalance,t1.CurrMargin,t1.CloseProfit,t1.Premium,t1.Deposit,t1.Withdraw,t1.Balance,t1.Available,t1.PreMargin,t1.FuturesMargin,t1.OptionsMargin,t1.PositionProfit,t1.Profit,t1.Interest,t1.Fee,t1.TotalCollateral,t1.CollateralForMargin,t1.PreAccmulateInterest,t1.AccumulateInterest,t1.AccumulateFee,t1.ForzenDeposit,t1.AccountStatus,
t1.InitialAsset,t1.PreMonthAsset,t1.PreWeekAsset,t1.PreAsset,t1.CurrentAsset,t1.PreStockValue,t1.StockValue
FROM siminfo.t_investorfund t1, (SELECT DISTINCT
t2.brokersystemid
FROM
siminfo.t_activitysettlementgroup t1,
siminfo.t_brokersystemsettlementgroup t2
WHERE t1.settlementgroupid = t2.settlementgroupid
AND t1.activityid = %s) t2, siminfo.t_activityinvestor t3
WHERE t1.brokersystemid = t2.brokersystemid AND t1.investorid = t3.investorid AND t3.activityid = %s"""
cursor.execute(sql, (
last_trading_day,
activity_id,
term_no + 1,
activity_id,
activity_id,
))
sql = """INSERT INTO siminfo.t_activityinvestorposition(TradingDay,SettlementGroupID,SettlementID,HedgeFlag,PosiDirection,YdPosition,Position,LongFrozen,ShortFrozen,YdLongFrozen,YdShortFrozen,BuyTradeVolume,SellTradeVolume,PositionCost,YdPositionCost,UseMargin,FrozenMargin,LongFrozenMargin,ShortFrozenMargin,
FrozenPremium,InstrumentID,ParticipantID,ClientID,InvestorID,ActivityID,TermNo)
SELECT %s,t1.SettlementGroupID,t1.SettlementID,t1.HedgeFlag,t1.PosiDirection,t1.YdPosition,t1.Position,t1.LongFrozen,t1.ShortFrozen,t1.YdLongFrozen,t1.YdShortFrozen,t1.BuyTradeVolume,t1.SellTradeVolume,t1.PositionCost,t1.YdPositionCost,t1.UseMargin,t1.FrozenMargin,t1.LongFrozenMargin,t1.ShortFrozenMargin,
t1.FrozenPremium,t1.InstrumentID,t1.ParticipantID,t1.ClientID,t3.InvestorID,%s,%s
FROM siminfo.t_clientposition t1, (SELECT DISTINCT
t1.settlementgroupid
FROM
siminfo.t_activitysettlementgroup t1
WHERE t1.activityid = %s) t2, siminfo.t_investorclient t3, siminfo.t_activityinvestor t4
WHERE t1.settlementgroupid = t2.settlementgroupid AND t1.clientid = t3.clientid AND t1.settlementgroupid = t3.settlementgroupid
AND t3.investorid = t4.investorid AND t4.activityid = %s AND t1.tradingday = %s"""
cursor.execute(sql, (
last_trading_day,
activity_id,
term_no + 1,
activity_id,
activity_id,
current_trading_day,
))
mysql_conn.commit()
except Exception as e:
logger.error("[settle activity] Error: %s" % (e))
result_code = -1
finally:
mysql_conn.close()
logger.info("[settle activity] end")
return result_code
def settle_etf(context, conf):
result_code = 0
logger = log.get_logger(category="Settleetf")
settlement_group_id = conf.get("settlementGroupId")
settlement_id = conf.get("settlementId")
stock_settle = conf.get("stock_settle")
logger.info("[settle etf %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
logger.info("[get current trading day]......")
sql = """SELECT t1.tradingday
FROM siminfo.t_tradesystemtradingday t1, siminfo.t_tradesystemsettlementgroup t2
WHERE t1.tradesystemid = t2.tradesystemid AND t2.settlementgroupid = %s"""
cursor.execute(sql, (settlement_group_id, ))
row = cursor.fetchone()
current_trading_day = str(row[0])
logger.info("[get current trading day] current_trading_day = %s" %
current_trading_day)
logger.info("[get next trading day]......")
# 判断是否跳过节假日
holiday = conf.get("holiday")
if holiday is True or holiday is None:
sql = """SELECT DAY FROM siminfo.t_TradingCalendar t WHERE t.day > %s AND t.tra = '1' ORDER BY DAY LIMIT 1"""
else:
sql = """SELECT DAY FROM siminfo.t_TradingCalendar t WHERE t.day > %s ORDER BY DAY LIMIT 1"""
cursor.execute(sql, (current_trading_day, ))
row = cursor.fetchone()
next_trading_day = str(row[0])
logger.info("[get next trading day] next_trading_day = %s" %
next_trading_day)
# 检查结算状态
logger.info("[check settlement status]......")
sql = """SELECT t1.tradingday, t1.settlementgroupid, t1.settlementid, t1.settlementstatus
FROM dbclear.t_settlement t1
WHERE t1.tradingday = %s
AND t1.settlementgroupid = %s
AND t1.settlementid = %s for update"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
row = cursor.fetchone()
if row is None:
logger.error("[settle etf] Error: There is no data for %s-%s." %
(settlement_group_id, settlement_id))
result_code = -1
elif row[3] != '0':
logger.error("[settle etf] Error: Settlement for %s-%s has done." %
(settlement_group_id, settlement_id))
result_code = -1
else:
# 更新标的收盘价
logger.info("[calculate settlement price] is processing......")
sql = """UPDATE dbclear.t_marketdata t1,
(
SELECT
t.instrumentid,
t.UnderlyingInstrID,
t2.ClosePrice
FROM
siminfo.t_instrument t
LEFT JOIN (select * from dbclear.t_marketdata where tradingday = %s and settlementgroupid = %s and settlementid = %s) t2 ON (t.UnderlyingInstrID = t2.InstrumentID)
where t.settlementgroupid = %s
) t2
SET t1.UnderlyingClosePx = t2.ClosePrice
WHERE
t1.InstrumentID = t2.instrumentid
AND t1.TradingDay = %s
AND t1.SettlementID = %s
AND t1.SettlementGroupID = %s """
cursor.execute(sql,
(current_trading_day, stock_settle, settlement_id,
settlement_group_id, current_trading_day,
settlement_id, settlement_group_id))
# 结算价为零赋值为昨结算
sql = """UPDATE dbclear.t_marketdata t
SET t.SettlementPrice = t.PreSettlementPrice
WHERE
t.TradingDay = %s
AND t.SettlementID = %s
AND t.SettlementGroupID = %s
AND t.SettlementPrice = %s"""
cursor.execute(
sql,
(current_trading_day, settlement_id, settlement_group_id, 0))
# 交收持仓处理
logger.info("[Move DelivPosition] is processing......")
sql = "delete from dbclear.t_delivinstrument where settlementgroupid = %s and settlementid = %s and tradingday = %s "
cursor.execute(
sql, (settlement_group_id, settlement_id, current_trading_day))
# 1）插入到t_delivinstrument表
sql = """insert into dbclear.t_delivinstrument(TradingDay, SettlementGroupID, SettlementID, InstrumentID
)select %s, t.SettlementGroupID, %s, t.instrumentid
from siminfo.t_instrumentproperty t
where t.settlementgroupid = %s and t.startdelivdate <= %s"""
cursor.execute(sql, (current_trading_day, settlement_id,
settlement_group_id, next_trading_day))
# 2）插入到t_clientdelivposition
sql = """insert into dbclear.t_clientdelivposition(TradingDay,SettlementGroupID,SettlementID,HedgeFlag,
PosiDirection,YdPosition,Position,LongFrozen,ShortFrozen,YdLongFrozen,YdShortFrozen,
BuyTradeVolume,SellTradeVolume,PositionCost,YdPositionCost,UseMargin,FrozenMargin,
LongFrozenMargin,ShortFrozenMargin,FrozenPremium,InstrumentID,ParticipantID,ClientID
)select TradingDay,SettlementGroupID,SettlementID,HedgeFlag,PosiDirection,YdPosition,Position,
LongFrozen,ShortFrozen,YdLongFrozen,YdShortFrozen,BuyTradeVolume,SellTradeVolume,PositionCost,
YdPositionCost,UseMargin,FrozenMargin,LongFrozenMargin,ShortFrozenMargin,FrozenPremium,
InstrumentID,ParticipantID,ClientID from dbclear.t_clientposition
where tradingday = %s
and settlementgroupid = %s
and settlementid = %s
and Position != '0'
and instrumentid in
(select t.instrumentid
from dbclear.t_delivinstrument t
where t.tradingday = %s
and t.settlementgroupid = %s
and t.settlementid = %s)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id,
current_trading_day, settlement_group_id, settlement_id))
# 3) 删除t_clientposition
sql = """delete from dbclear.t_clientposition
where (tradingday = %s
and settlementgroupid = %s
and settlementid = %s
and instrumentid in
(select t.instrumentid
from dbclear.t_delivinstrument t
where t.tradingday = %s
and t.settlementgroupid = %s
and t.settlementid = %s))
or Position = '0'"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id,
current_trading_day, settlement_group_id, settlement_id))
# 4) 计算行权盈亏
sql = """INSERT INTO dbclear.t_clientdelivprofit(TradingDay, SettlementGroupID, SettlementID, ParticipantID, AccountID, ClientID, HedgeFlag, InstrumentID, PosiDirection, POSITION, OptionsType, VolumeMultiple, UnderlyingMultiple, StrikePrice, SettlementPrice, Profit)
SELECT
t1.tradingday,
t1.settlementgroupid,
t1.settlementid,
t1.participantid,
t2.accountid,
t1.clientid,
t1.hedgeflag,
t1.instrumentid,
t1.posidirection,
t1.position,
t3.optionstype,
t3.volumemultiple,
t3.underlyingmultiple,
t3.strikeprice,
t3.settlementprice,
CASE
WHEN t3.optionstype = '1'
THEN IF(t1.posidirection = '2', 1, - 1) * (
t3.settlementprice - t3.strikeprice
) * t1.position * t3.volumemultiple * t3.underlyingmultiple
WHEN t3.optionstype = '2'
THEN IF(t1.posidirection = '2', - 1, 1) * (
t3.settlementprice - t3.strikeprice
) * t1.position * t3.volumemultiple * t3.underlyingmultiple
ELSE 0
END AS delivprofit
FROM
(SELECT
t1.*,
t2.tradingrole
FROM
dbclear.t_clientdelivposition t1,
siminfo.t_client t2
WHERE t1.clientid = t2.clientid) t1,
siminfo.t_PartRoleAccount t2,
(SELECT
t2.tradingday,
t1.settlementgroupid,
t2.settlementid,
t1.instrumentid,
t1.strikeprice,
t1.optionstype,
t1.volumemultiple,
t1.underlyingmultiple,
t2.UnderlyingClosePx as settlementprice
FROM
siminfo.t_instrument t1,
dbclear.t_marketdata t2
WHERE t1.settlementgroupid = %s
AND t2.tradingday = %s
AND t2.settlementid = %s
AND (
(
t1.optionstype = '1'
AND t1.strikeprice < t2.settlementprice
)
OR (
t1.optionstype = '2'
AND t1.strikeprice > t2.settlementprice
)
)
AND t1.SettlementGroupID = t2.SettlementGroupID
AND t1.instrumentid = t2.instrumentid) t3
WHERE t2.TradingRole = t1.TradingRole
AND t2.SettlementGroupID = t1.SettlementGroupID
AND t2.ParticipantID = t1.ParticipantID
AND t1.instrumentid = t3.instrumentid
AND t1.tradingday = t3.tradingday
AND t1.settlementgroupid = t3.settlementgroupid
AND t1.settlementid = t3.settlementid
AND (
t1.posidirection = '2'
OR t1.posidirection = '3'
)
AND t1.tradingday = %s
AND t1.settlementgroupid = %s
AND t1.settlementid = %s
"""
cursor.execute(sql, (
settlement_group_id,
current_trading_day,
settlement_id,
current_trading_day,
settlement_group_id,
settlement_id,
))
# 交割手续费
sql = """"""
# 交易手续费
logger.info("[Calculate TransFee] is processing......")
sql = "delete from dbclear.t_clienttransfee where settlementgroupid = %s and settlementid = %s and tradingday = %s "
cursor.execute(
sql, (settlement_group_id, settlement_id, current_trading_day))
# 插入t_clienttransfee表中(CloseYesterdayFeeRatio存放的是佣金)
sql = """insert into dbclear.t_clienttransfee(TradingDay, SettlementGroupID, SettlementID, ParticipantID, ClientID, AccountID, ProductGroupID, ProductID, UnderlyingInstrID, InstrumentID, TradeID, Direction, TradingRole, HedgeFlag, OffsetFlag, Volume, Price, TransFeeRatio, ValueMode, TransFee, OrderSysID, MinFee, MaxFee
)select t1.tradingday,t1.settlementgroupid,t1.settlementid,t1.participantid,t1.clientid,
t1.accountid,t3.productgroupid,t3.productid,t3.underlyinginstrid,t1.instrumentid,
t1.tradeid,t1.direction,t1.tradingrole,t1.hedgeflag,t1.offsetflag,t1.volume,t1.price,
case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end as transfeeratio,
t2.valuemode,
if(t2.valuemode='2',
round(((case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end) * t3.volumemultiple + t2.CloseYesterdayFeeRatio) * t1.volume, 2),
round(((case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end) * t1.price * t3.volumemultiple + t2.CloseYesterdayFeeRatio) * t1.volume ,
2)) as transfee,
t1.OrderSysID,
'0' as Minfee,
'0' as MaxFee
from dbclear.t_trade t1,dbclear.t_clienttransfeeratio t2, siminfo.t_instrument t3
where t1.TradingDay = t2.TradingDay
and t1.SettlementID = t2.SettlementID
and t1.settlementgroupid = t2.settlementgroupid
and t2.participantid = '00000000'
and t2.clientid = '00000000'
and t1.instrumentid = t2.instrumentid
and t1.tradingrole = t2.tradingrole
and t1.hedgeflag = t2.hedgeflag
and t1.settlementgroupid = t3.settlementgroupid
and t1.instrumentid = t3.instrumentid
and t1.tradingday = %s
and t1.settlementgroupid = %s
and t1.settlementid = %s"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
sql = """insert into dbclear.t_clienttransfee(TradingDay, SettlementGroupID, SettlementID, ParticipantID, ClientID, AccountID, ProductGroupID, ProductID, UnderlyingInstrID, InstrumentID, TradeID, Direction, TradingRole, HedgeFlag, OffsetFlag, Volume, Price, TransFeeRatio, ValueMode, TransFee, OrderSysID, MinFee, MaxFee
)select t1.tradingday,t1.settlementgroupid,t1.settlementid,t1.participantid,t1.clientid,
t1.accountid,t3.productgroupid,t3.productid,t3.underlyinginstrid,t1.instrumentid,
t1.tradeid,t1.direction,t1.tradingrole,t1.hedgeflag,t1.offsetflag,t1.volume,t1.price,
case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end as transfeeratio,
t2.valuemode,
if(t2.valuemode='2',
round(((case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end) * t3.volumemultiple + t2.CloseYesterdayFeeRatio) * t1.volume, 2),
round(((case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end) * t1.price * t3.volumemultiple + t2.CloseYesterdayFeeRatio) * t1.volume ,
2)) as transfee,
t1.OrderSysID,
'0' as Minfee,
'0' as MaxFee
from dbclear.t_trade t1,dbclear.t_clienttransfeeratio t2, siminfo.t_instrument t3
where t1.TradingDay = t2.TradingDay
and t1.SettlementID = t2.SettlementID
and t1.settlementgroupid = t2.settlementgroupid
and t2.participantid = t1.participantid
and t2.clientid = '00000000'
and t1.instrumentid = t2.instrumentid
and t1.tradingrole = t2.tradingrole
and t1.hedgeflag = t2.hedgeflag
and t1.settlementgroupid = t3.settlementgroupid
and t1.instrumentid = t3.instrumentid
and t1.tradingday = %s
and t1.settlementgroupid = %s
and t1.settlementid = %s
ON DUPLICATE KEY UPDATE transfeeratio = VALUES(transfeeratio), valuemode = VALUES(valuemode),
transfee = VALUES(transfee)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
sql = """insert into dbclear.t_clienttransfee(TradingDay, SettlementGroupID, SettlementID, ParticipantID, ClientID, AccountID, ProductGroupID, ProductID, UnderlyingInstrID, InstrumentID, TradeID, Direction, TradingRole, HedgeFlag, OffsetFlag, Volume, Price, TransFeeRatio, ValueMode, TransFee, OrderSysID, MinFee, MaxFee
)select t1.tradingday,t1.settlementgroupid,t1.settlementid,t1.participantid,t1.clientid,
t1.accountid,t3.productgroupid,t3.productid,t3.underlyinginstrid,t1.instrumentid,
t1.tradeid,t1.direction,t1.tradingrole,t1.hedgeflag,t1.offsetflag,t1.volume,t1.price,
case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end as transfeeratio,
t2.valuemode,
if(t2.valuemode='2',
round(((case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end) * t3.volumemultiple + t2.CloseYesterdayFeeRatio) * t1.volume, 2),
round(((case
when t1.offsetflag = '0' or
t1.offsetflag = '2' then
t2.openfeeratio
when t1.offsetflag = '3' or t1.offsetflag = '1' or t1.offsetflag = '4' then
t2.closetodayfeeratio
end) * t1.price * t3.volumemultiple + t2.CloseYesterdayFeeRatio) * t1.volume ,
2)) as transfee,
t1.OrderSysID,
'0' as Minfee,
'0' as MaxFee
from dbclear.t_trade t1,dbclear.t_clienttransfeeratio t2, siminfo.t_instrument t3
where t1.TradingDay = t2.TradingDay
and t1.SettlementID = t2.SettlementID
and t1.settlementgroupid = t2.settlementgroupid
and t2.participantid = t1.participantid
and t2.clientid = t1.clientid
and t1.instrumentid = t2.instrumentid
and t1.tradingrole = t2.tradingrole
and t1.hedgeflag = t2.hedgeflag
and t1.settlementgroupid = t3.settlementgroupid
and t1.instrumentid = t3.instrumentid
and t1.tradingday = %s
and t1.settlementgroupid = %s
and t1.settlementid = %s
ON DUPLICATE KEY UPDATE transfeeratio = VALUES(transfeeratio), valuemode = VALUES(valuemode),
transfee = VALUES(transfee)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 持仓保证金
# 认购期权虚值=行权价-50ETF（前）收盘价（如果差值为负则虚值为零）
# 认沽期权虚值=50ETF（前）收盘价-行权价（如果差值为负则虚值为零）
# 认购期权义务仓维持保证金＝[合约结算价+Max（12%×合约标的收盘价-认购期权虚值，7%×合约标的收盘价）]×合约单位
# MarginRatio = [SettlementPrice + Max(0.12 * UnderlyingClosePx - Max(strikeprice - UnderlyingClosePx, 0) , 0.07 * UnderlyingClosePx)] * underlyingmultiple
# 认沽期权义务仓维持保证金＝Min[合约结算价 +Max（12%×合标的收盘价-认沽期权虚值，7%×行权价格），行权价格]×合约单位
# MarginRatio = Min[SettlementPrice + Max(0.12 * UnderlyingClosePx - Max(UnderlyingClosePx - strikeprice,0), 0.07 * strikeprice), strikeprice] * underlyingmultiple
logger.info("[Calculate PositionMargin] is processing......")
sql = "delete from dbclear.t_clientpositionmargin where settlementgroupid = %s and settlementid = %s and tradingday = %s "
cursor.execute(
sql, (settlement_group_id, settlement_id, current_trading_day))
# 插入t_clientpositionmargin表中
sql = """insert into dbclear.t_clientpositionmargin(TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,ProductGroupID,ProductID,UnderlyingInstrID,InstrumentID,TradingRole,HedgeFlag,PosiDirection,Position,MarginRatio,ValueMode,SettlementPrice,PositionMargin
)SELECT t1.tradingday,
t1.settlementgroupid,
t1.settlementid,
t1.participantid,
t1.clientid,
t2.AccountID,
t4.productgroupid,
t4.productid,
t4.underlyinginstrid,
t1.instrumentid,
t1.tradingrole,
t1.hedgeflag,
t1.posidirection,
t1.position,
if (t1.posidirection = '3',if ( t4.OptionsType = '1',
(t3.SettlementPrice + greatest(0.12 * t3.UnderlyingClosePx - greatest(t4.strikeprice - t3.UnderlyingClosePx, 0) , 0.07 * t3.UnderlyingClosePx)) * t4.underlyingmultiple ,
LEAST(t3.SettlementPrice + greatest(0.12 * t3.UnderlyingClosePx - greatest(t3.UnderlyingClosePx - t4.strikeprice,0), 0.07 * t4.strikeprice), t4.strikeprice) * t4.underlyingmultiple ),0) AS MarginRatio ,
'2' as valuemode,
t3.SettlementPrice,
if (t1.posidirection = '3',if ( t4.OptionsType = '1',
(t3.SettlementPrice + greatest(0.12 * t3.UnderlyingClosePx - greatest(t4.strikeprice - t3.UnderlyingClosePx, 0) , 0.07 * t3.UnderlyingClosePx)) * t4.underlyingmultiple ,
LEAST(t3.SettlementPrice + greatest(0.12 * t3.UnderlyingClosePx - greatest(t3.UnderlyingClosePx - t4.strikeprice,0), 0.07 * t4.strikeprice), t4.strikeprice) * t4.underlyingmultiple ),0) * (t1.position + t1.YdPosition) AS positionmargin
FROM
( SELECT t1.*, t2.tradingrole FROM dbclear.t_clientposition t1, siminfo.t_client t2 WHERE t1.clientid = t2.clientid and t1.settlementgroupid = t2.settlementgroupid) t1,
siminfo.t_PartRoleAccount t2,
dbclear.t_marketdata t3,
siminfo.t_instrument t4
WHERE t2.TradingRole = t1.TradingRole
and t2.SettlementGroupID = t1.SettlementGroupID
and t2.ParticipantID = t1.ParticipantID
and t1.instrumentid = t3.instrumentid
AND t1.tradingday = t3.tradingday
AND t1.settlementgroupid = t3.settlementgroupid
AND t1.settlementid = t3.settlementid
AND t1.settlementgroupid = t4.settlementgroupid
AND t1.instrumentid = t4.instrumentid
AND ( t1.posidirection = '2' OR t1.posidirection = '3' )
and t1.tradingday = %s
and t1.settlementgroupid = %s
and t1.settlementid = %s"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 持仓权利金
logger.info("[Calculate PositionPremium] is processing......")
sql = "delete from dbclear.t_clientpositionpremium where settlementgroupid = %s and settlementid = %s and tradingday = %s "
cursor.execute(
sql, (settlement_group_id, settlement_id, current_trading_day))
# 插入t_clientpositionpremium表中
sql = """INSERT INTO dbclear.t_clientpositionpremium
(TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,InstrumentID,Volume,UserID,Premium)
SELECT
TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,InstrumentID,sum(Volume),UserID,sum( Premium )
FROM
(
SELECT
t1.TradingDay,t1.SettlementGroupID,t1.SettlementID,t1.Direction,t1.ParticipantID,t1.ClientID,t1.AccountID,
t1.InstrumentID,if (t1.OffsetFlag = '0',t1.Volume, -1 * t1.Volume ) as Volume,t1.UserID,
ROUND( IF (t1.Direction = '0', - 1 * Price * t2.UnderlyingMultiple, Price * t2.UnderlyingMultiple) * t1.Volume , 2 ) AS Premium
FROM
dbclear.t_trade t1,siminfo.t_instrument t2
WHERE
t1.settlementgroupid = t2.settlementgroupid
AND t1.instrumentid = t2.instrumentid
AND t1.tradingday = %s
AND t1.settlementgroupid = %s
AND t1.settlementid = %s
) t
GROUP BY TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,
InstrumentID,UserID"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 客户持仓
logger.info("[update client position]......")
sql = """UPDATE dbclear.t_clientposition t,
(
SELECT
t.tradingday,
t.settlementgroupid,
t.settlementid,
t.participantid,
t.clientid,
t.accountid,
t.transfee,
t1.Premium,
t2.positionmargin,
t.InstrumentID
FROM
(
SELECT
t.tradingday,
t.settlementgroupid,
t.settlementid,
t.participantid,
t.clientid,
t.accountid,
sum( t.transfee ) AS transfee,
t.InstrumentID
FROM
dbclear.t_clienttransfee t
WHERE
t.tradingday = %s
AND t.settlementgroupid = %s
AND t.settlementid = %s
GROUP BY
t.tradingday,
t.settlementgroupid,
t.settlementid,
t.participantid,
t.clientid,
t.accountid,
t.InstrumentID
) t,
dbclear.t_clientpositionpremium t1,
dbclear.t_clientpositionmargin t2
WHERE
t.TradingDay = t1.TradingDay
AND t.TradingDay = t2.TradingDay
AND t.SettlementGroupID = t1.SettlementGroupID
AND t.SettlementGroupID = t2.SettlementGroupID
AND t.SettlementID = t1.SettlementID
AND t.SettlementID = t2.SettlementID
AND t.ParticipantID = t1.ParticipantID
AND t.ParticipantID = t2.ParticipantID
AND t.ClientID = t1.ClientID
AND t.ClientID = t2.ClientID
AND t.accountid = t1.AccountID
AND t.accountid = t2.AccountID
) t1
SET t.PositionCost = abs( t1.Premium ) + t1.transfee
WHERE
t.InstrumentID = t1.InstrumentID
AND t.TradingDay = t1.TradingDay
AND t.SettlementGroupID = t1.SettlementGroupID
AND t.SettlementID = t1.SettlementID
AND t.ClientID = t1.ClientID
AND t.ParticipantID = t1.ParticipantID
AND t.tradingday = %s
AND t.settlementgroupid = %s
AND t.settlementid = %s"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id,
current_trading_day, settlement_group_id, settlement_id))
# 客户资金
logger.info("[Calculate ClientFund] is processing......")
# 1）更新transfee
sql = """insert into dbclear.t_clientfund (TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,TransFee,DelivFee,PositionMargin,Profit,available,StockValue)
(select t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid,sum(t.transfee) as transfee,0,0,0,0,0
from dbclear.t_clienttransfee t where t.tradingday = %s and t.settlementgroupid = %s and t.settlementid = %s
group by t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid)
ON DUPLICATE KEY UPDATE dbclear.t_clientfund.transfee = values(transfee)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 2）更新delivfee
sql = """insert into dbclear.t_clientfund (TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,TransFee,DelivFee,PositionMargin,Profit,available,StockValue)
(select t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid,0,sum(t.delivfee) as delivfee,0,0,0,0
from dbclear.t_clientdelivfee t where t.tradingday = %s and t.settlementgroupid = %s and t.settlementid = %s
group by t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid)
ON DUPLICATE KEY UPDATE t_clientfund.delivfee = values(delivfee)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 3）更新profit
sql = """insert into dbclear.t_clientfund (TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,TransFee,DelivFee,PositionMargin,Profit,available,StockValue)
(select t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid,0,0,0,sum(t.profit) as profit,0,0
from dbclear.t_clientdelivprofit t where t.tradingday = %s and t.settlementgroupid = %s and t.settlementid = %s
group by t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid)
ON DUPLICATE KEY UPDATE dbclear.t_clientfund.profit = values(profit)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 4）更新premium
sql = """insert into dbclear.t_clientfund (TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,TransFee,DelivFee,PositionMargin,Profit,available,StockValue)
(select t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid,0,0,0,0,sum( t.Premium ) AS available,0
from dbclear.t_clientpositionpremium t where t.tradingday = %s and t.settlementgroupid = %s and t.settlementid = %s
group by t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid)
ON DUPLICATE KEY UPDATE dbclear.t_clientfund.available = values(available)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 5）更新positionmargin
sql = """insert into dbclear.t_clientfund (TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,TransFee,DelivFee,PositionMargin,Profit,available,StockValue)
(select t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid,0,0,sum(t.positionmargin) as positionmargin,0,0,0
from dbclear.t_clientpositionmargin t where t.tradingday = %s and t.settlementgroupid = %s and t.settlementid = %s
group by t.tradingday,t.settlementgroupid,t.settlementid,t.participantid,t.clientid,t.accountid)
ON DUPLICATE KEY UPDATE dbclear.t_clientfund.positionmargin = values(positionmargin)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 6）更新stockvalue
sql = """insert into dbclear.t_clientfund (TradingDay,SettlementGroupID,SettlementID,ParticipantID,ClientID,AccountID,TransFee,DelivFee,PositionMargin,Profit,available,StockValue)
(SELECT t1.tradingday, t1.settlementgroupid, t1.settlementid, t1.participantid, t2.accountid, t1.clientid, 0 AS transfee, 0 AS delivfee, 0 AS positionmargin, 0 AS profit, 0 AS available,
ROUND(SUM(
CASE
WHEN t1.posidirection = '2'
THEN t1.position * t3.settlementprice * t4.underlyingmultiple
WHEN t1.posidirection = '3'
THEN - 1 * t1.position * t3.settlementprice * t4.underlyingmultiple
ELSE 0
END
), 2) AS stockvalue
FROM
(SELECT
t1.*,
t2.tradingrole
FROM
dbclear.t_clientposition t1,
siminfo.t_client t2
WHERE t1.clientid = t2.clientid) t1,
siminfo.t_PartRoleAccount t2,
dbclear.t_marketdata t3,
siminfo.t_instrument t4
WHERE t2.TradingRole = t1.TradingRole
AND t2.SettlementGroupID = t1.SettlementGroupID
AND t2.ParticipantID = t1.ParticipantID
AND t1.instrumentid = t3.instrumentid
AND t1.tradingday = t3.tradingday
AND t1.settlementgroupid = t3.settlementgroupid
AND t1.settlementid = t3.settlementid
AND t1.settlementgroupid = t4.settlementgroupid
AND t1.instrumentid = t4.instrumentid
AND (
t1.posidirection = '2'
OR t1.posidirection = '3'
)
AND t1.tradingday = %s
AND t1.settlementgroupid = %s
AND t1.settlementid = %s
GROUP BY t1.tradingday,
t1.settlementgroupid,
t1.settlementid,
t1.participantid,
t2.accountid,
t1.clientid)
ON DUPLICATE KEY UPDATE dbclear.t_clientfund.stockvalue = values(stockvalue)"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
# 更新结算状态
logger.info("[update settlement status] is processing......")
sql = """UPDATE dbclear.t_settlement SET settlementstatus = '1'
WHERE tradingday = %s AND settlementgroupid = %s AND settlementid = %s AND settlementstatus = '0'"""
cursor.execute(
sql, (current_trading_day, settlement_group_id, settlement_id))
mysql_conn.commit()
except Exception as e:
logger.error("[settle etf] Error: %s" % e)
result_code = -1
finally:
mysql_conn.close()
logger.info("[settle etf] end")
return result_code
def gen_activity(context, conf):
mysql_pool = mysql(configs=context.get("mysql").get(conf.get("mysqlId")))
logger = log.get_logger(category="GenActivity")
logger.info("[gen activity with %s] begin" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
mysql_conn = mysql_pool.get_cnx()
mysql_conn.set_charset_collation('utf8')
try:
mysql_conn.start_transaction()
cursor = mysql_conn.cursor()
for activity in conf["activities"]:
code = activity["code"]
name = activity["name"]
atype = activity["type"]
initial_balance = activity["balance"]
join_mode = activity["joinMode"]
ranking_rule = activity["rankingRule"]
begin = activity["begin"]
end = activity["end"]
settlement_groups = activity["settlement_groups"]
logger.info(
"[gen activity with {code=%s, name=%s, type= %s, balance=%s, joinMode=%s, rankingRule=%s, begin=%s, end=%s, settlementgroups=%s}]......"
% (code, name, atype, initial_balance, join_mode, ranking_rule,
begin, end, settlement_groups))
sql = '''SELECT activityid FROM siminfo.t_activity WHERE activityid = %s'''
cursor.execute(sql, (code, ))
row = cursor.fetchone()
if row is not None:
sys.stderr.write("Error: Activity %s is existed.\n" % (code, ))
logger.error(
"[gen activity with {code=%s, name=%s, type= %s, balance=%s, joinMode=%s, rankingRule=%s, begin=%s, end=%s, settlementgroups=%s}] Error: Activity %s is existed."
% (code, name, atype, initial_balance, join_mode,
ranking_rule, begin, end, settlement_groups, code))
else:
sql = '''INSERT INTO siminfo.t_activity(activityid, activityname, activitytype, activitystatus, initialbalance, joinmode, rankingrule, createdate, createtime, begindate, enddate, updatedate, updatetime)
VALUES (%s, %s, %s, '0', %s, %s, %s, DATE_FORMAT(NOW(), '%Y%m%d'), DATE_FORMAT(NOW(), '%H:%i:%S'), %s, %s, DATE_FORMAT(NOW(), '%Y%m%d'), DATE_FORMAT(NOW(), '%H:%i:%S'))'''
cursor.execute(sql, (
code,
name,
atype,
initial_balance,
join_mode,
ranking_rule,
begin,
end,
))
relations = []
for settlement_group_id in settlement_groups:
relations.append((
code,
settlement_group_id,
))
sql = '''INSERT INTO siminfo.t_activitysettlementgroup(activityid, settlementgroupid) values (%s, %s)'''
cursor.executemany(sql, relations)
mysql_conn.commit()
except Exception as e:
logger.error(
"[gen activity with %s] Error: %s" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False), e))
finally:
mysql_conn.close()
logger.info("[gen activity with %s] end" %
(json.dumps(conf, encoding="UTF-8", ensure_ascii=False)))
from utils import load_config
from logger import setup_log
from flask import Flask, request, render_template, session, redirect, url_for,Blueprint
from utils import mysql
import math
import json
from engine import RecommendationEngine
main = Blueprint('app', __name__)
config = load_config()
logger = setup_log(__name__)
# main = Flask(__name__)
nextbook = 1;
nextuser = 1;
mysql = mysql(config['mysql'])
@main.route("/")
def root():
"""
主页
:return: home.html
"""
login, userid = False, ''
if 'userid' in session:
login, userid = True, session['userid']
# 热门书籍
hot_books = []
# sql: SELECT BookID,sum(Rating) as score FROM Book.Bookrating group by BookID order by score desc limit 10;
sql = "SELECT BookTitle, BookAuthor ,BookID, ImageM FROM Books where BookID = '" + \
"' or BookID = '".join(config['bookid']) + "'"
import utils
import sys
rf=utils.ReadFile('splitCarM.txt')
pt=utils.getnow() if len(sys.argv)==1 else sys.argv[1]
rf=rf.replace('$pt',pt )
with utils.hiveClient('180.97.232.57', 10000) as hc:
map(lambda sql:hc.execute(sql),rf.split(';'))
hc.execute('select * from t_splitdistance2')
m= hc.fetchAll()
with utils.mysql(host='180.97.232.57',user='******',passwd='test',db='aichuche_db') as my:
for i in xrange(len(m)):
sql="replace into t_distance_classification (dt,device_id,serial_number,\
start_dt,start_gpsx,start_gpsy,end_dt,end_gpsx,end_gpsy,create_time,update_time,remark)\
values ('%s','%s',%s,'%s',%s,%s,'%s',%s,%s,now(),now(),'')" % tuple(m[i].split('\t'))
my.execute(sql)
my.commit()
