def stream_y_fund_info(fund_ids=None):
sql = "\
SELECT TB_MAIN.version, TB_MAIN.fund_id, fund_name, fund_full_name, fund_status, liquidation_cause, end_date \
FROM y_fund_info TB_MAIN \
JOIN (SELECT fund_id, MAX(version) as latest_ver FROM y_fund_info WHERE is_used = 1 GROUP BY fund_id) as TB_LATEST \
ON TB_MAIN.version = TB_LATEST.latest_ver AND TB_MAIN.fund_id = TB_LATEST.fund_id "
if fund_ids is not None:
fids = SQL.values4sql(fund_ids)
sql += "WHERE TB_MAIN.fund_id IN {fids}".format(fids=fids)
inp = MysqlNativeInput(engine_c, sql)
ac = transform.AddConst({"source_id": "000001"})
dd = transform.SortDropduplicate(sort_by=["version", "fund_id"],
ascending=[False, True],
subset=["fund_id"])
km = transform.MapSelectKeys({
"fund_id": None,
"fund_name": None,
"fund_full_name": None,
"fund_name_en": None,
"source_id": None,
"fund_status": None,
"liquidation_cause": None,
"end_date": None
})
stream = Stream(inp, (
ac,
dd,
km,
))
return stream
def stream_x_fund_info_010004(fund_ids=None):
def clean_fund_status(limit_date, limit_time):
if limit_date is not None:
if limit_date <= dt.date.today():
return "终止"
else:
return "运行中"
if limit_time is not None:
if limit_time == "无期限":
return "运行中"
return None
sql = "\
SELECT TB_MAIN.version, im.matched_id, TB_MAIN.fund_id, fund_name_amac, reg_code_amac, foundation_date_amac, fund_time_limit_amac \
FROM x_fund_info_securities TB_MAIN \
JOIN (SELECT fund_id, MAX(version) latest_ver FROM x_fund_info_securities WHERE is_used = 1 GROUP BY fund_id) as TB_LATEST \
ON TB_MAIN.version = TB_LATEST.latest_ver AND TB_MAIN.fund_id = TB_LATEST.fund_id \
JOIN base.id_match im ON im.source_id = TB_MAIN.fund_id AND id_type = 1 AND source = '010004' AND im.is_used = 1 "
if fund_ids is not None:
fids = SQL.values4sql(fund_ids)
sql += "WHERE im.matched_id IN {fids}".format(fids=fids)
inp = MysqlNativeInput(engine_c, sql)
vm = transform.ValueMap(
OrderedDict([("fund_name_amac", lambda x: x.strip()),
("reg_code_amac", lambda x: x.strip()),
("limit_date",
(lambda x: dt.datetime.strptime(x, "%Y-%m-%d").date(),
"fund_time_limit_amac")),
("fund_time_limit_amac", lambda x: x
if x == "无期限" else None),
("fund_status", (lambda x, y: clean_fund_status(x, y),
"limit_date", "fund_time_limit_amac"))]))
ac = transform.AddConst({"source_id": "010004"})
cln = transform.CleanWrongToNone({"reg_code_amac": ""}, repls=None)
dd = transform.SortDropduplicate(sort_by=["version", "matched_id"],
ascending=[False, True],
subset="matched_id",
keep="first")
km = transform.MapSelectKeys({
# "version": None,
"matched_id": "fund_id",
"source_id": None,
"fund_name_amac": "fund_full_name",
"reg_code_amac": "reg_code",
"foundation_date_amac": "foundation_date",
"fund_time_limit_amac": "limit_time",
"limit_date": "limit_date",
"fund_status": "fund_status"
})
stream = Stream(inp, (vm, cln, ac, dd, km), name="x_fund_info_security")
return stream
def stream_x_fund_info_010005(fund_ids=None):
sql = "\
SELECT TB_MAIN.version, im.matched_id, TB_MAIN.fund_id, fund_name_amac, reg_code_amac, foundation_date_amac, issuing_scale_amac, number_clients_amac \
FROM x_fund_info_futures TB_MAIN \
JOIN (SELECT fund_id, MAX(version) latest_ver FROM x_fund_info_futures WHERE is_used = 1 GROUP BY fund_id) as TB_LATEST \
ON TB_MAIN.version = TB_LATEST.latest_ver AND TB_MAIN.fund_id = TB_LATEST.fund_id \
JOIN base.id_match im ON im.source_id = TB_MAIN.fund_id AND id_type = 1 AND source = '010005' AND im.is_used = 1 "
if fund_ids is not None:
fids = SQL.values4sql(fund_ids)
sql += "WHERE im.matched_id IN {fids}".format(fids=fids)
inp = MysqlNativeInput(engine_c, sql)
vm = transform.ValueMap(
OrderedDict([
("fund_name_amac", lambda x: x.strip()),
("reg_code_amac", lambda x: x.strip()),
("number_clients_amac", lambda x: int(x)),
]))
cln = transform.CleanWrongToNone({"reg_code_amac": ""}, repls=None)
ac = transform.AddConst({"source_id": "010005"})
# jn = transform.Join(inp2, left_on="fund_id", right_on="source_id")
dd = transform.SortDropduplicate(sort_by=["version", "matched_id"],
ascending=[False, True],
subset="matched_id",
keep="first")
km = transform.MapSelectKeys({
"matched_id": "fund_id",
"source_id": None,
"fund_name_amac": "fund_full_name",
"reg_code_amac": "reg_code",
"foundation_date_amac": "foundation_date",
"issuing_scale_amac": "issuing_scale",
"number_clients_amac": "number_clients",
})
stream = Stream(inp, (vm, cln, ac, dd, km), name="x_fund_info_future")
return stream
def stream_x_fund_info_010002(fund_ids=None):
sql = "\
SELECT TB_MAIN.version, im.matched_id, TB_MAIN.fund_id, fund_name_amac, reg_code_amac, reg_time_amac, fund_time_limit_amac, issuing_scale_amac, number_clients_amac \
FROM x_fund_info_fundaccount TB_MAIN \
JOIN (SELECT fund_id, MAX(version) latest_ver FROM x_fund_info_fundaccount WHERE is_used = 1 GROUP BY fund_id) as TB_LATEST \
ON TB_MAIN.version = TB_LATEST.latest_ver AND TB_MAIN.fund_id = TB_LATEST.fund_id \
JOIN base.id_match im ON im.source_id = TB_MAIN.fund_id AND id_type = 1 AND source = '010002' AND im.is_used = 1 "
if fund_ids is not None:
fids = SQL.values4sql(fund_ids)
sql += "WHERE im.matched_id IN {fids}".format(fids=fids)
inp = MysqlNativeInput(engine_c, sql)
def clean_fund_status(reg_time, limit_time):
from dateutil.relativedelta import relativedelta
now = dt.date.today()
if reg_time is not None and limit_time is not None and limit_time > 0:
if limit_time > 1200: # 有些无限期的limit_time会被标记为9999月
return "运行中"
limit_date = reg_time + relativedelta(months=int(limit_time))
if limit_date <= now:
return "终止"
else:
return "运行中"
return None
vm = transform.ValueMap({
"fund_name_amac":
lambda x: x.strip(),
"reg_code_amac":
lambda x: x.strip(),
"fund_time_limit_amac":
lambda x: int(x),
"issuing_scale_amac":
lambda x: x * 1e4 if x is not None else None,
"number_clients_amac":
lambda x: int(x),
})
vm2 = transform.ValueMap({
"fund_status": (lambda x, y: clean_fund_status(x, y), "reg_time_amac",
"fund_time_limit_amac")
})
cln = transform.CleanWrongToNone({"reg_code_amac": ""}, repls=None)
# jn = transform.Join(
# inp2, left_on="fund_id", right_on="source_id"
# )
ac = transform.AddConst({"source_id": "010002"})
dd = transform.SortDropduplicate(sort_by=["version", "matched_id"],
ascending=[False, True],
subset="matched_id",
keep="first")
km = transform.MapSelectKeys({
# "version": None,
"matched_id": "fund_id",
"source_id": None,
"reg_code_amac": "reg_code",
"reg_time_amac": "reg_time",
"fund_status": "fund_status",
"fund_time_limit_amac": "limit_time",
"issuing_scale_amac": "issuing_scale",
"number_clients_amac": "number_clients",
"fund_name_amac": "fund_full_name",
})
stream = Stream(inp, (vm, vm2, cln, dd, ac, km),
name="x_fund_info_account")
return stream
def stream_x_fund_info_010003(fund_ids=None):
def clean_currency(x):
patt = "(?P<currency>人民币|美元|多币种|欧元|港元|澳元|其他|其它|日元|英镑)?(?P<type>现钞|现汇)?"
cpatt = re.compile(patt)
sre = cpatt.search(x)
if sre is not None:
return sre.groupdict()
return {}
sql = "\
SELECT TB_MAIN.version, im.matched_id, TB_MAIN.fund_id, fund_name_amac, reg_code_amac, reg_time_amac, foundation_date_amac, currency_name_amac, \
fund_status_amac, final_report_time_amac \
FROM x_fund_info_private TB_MAIN \
JOIN (SELECT fund_id, MAX(version) latest_ver FROM x_fund_info_private WHERE is_used = 1 GROUP BY fund_id) as TB_LATEST \
ON TB_MAIN.version = TB_LATEST.latest_ver AND TB_MAIN.fund_id = TB_LATEST.fund_id \
JOIN base.id_match im ON im.source_id = TB_MAIN.fund_id AND id_type = 1 AND source = '010003' AND im.is_used = 1 "
if fund_ids is not None:
fids = SQL.values4sql(fund_ids)
sql += "WHERE im.matched_id IN {fids}".format(fids=fids)
inp = MysqlNativeInput(engine_c, sql)
vm = transform.ValueMap(
OrderedDict([
("fund_name_amac", lambda x: x.strip()),
("reg_code_amac", lambda x: x.strip()),
("is_abnormal_liquidation", (lambda x: {
"延期清算": 1,
"提前清算": 1,
"投顾协议已终止": 1,
"正常清算": 0
}.get(x), "fund_status_amac")),
("liquidation_cause", (lambda x: {
"延期清算": "延期清算",
"提前清算": "提前清算",
"投顾协议已终止": "投顾协议已终止"
}.get(x), "fund_status_amac")),
("fund_status_amac", {
"正常清算": "终止",
"正在运作": "运行中",
"延期清算": "终止",
"提前清算": "终止",
"投顾协议已终止": "终止"
}),
("currency_type", (lambda x: clean_currency(x).get("type"),
"currency_name_amac")),
("currency_name_amac",
lambda x: clean_currency(x).get("currency")),
]))
cln = transform.CleanWrongToNone({"reg_code_amac": ""}, repls=None)
# jn = transform.Join(
# inp2, left_on="fund_id", right_on="source_id"
# )
dd = transform.SortDropduplicate(sort_by=["version", "matched_id"],
ascending=[False, True],
subset="matched_id",
keep="first")
ac = transform.AddConst({"source_id": "010003"})
km = transform.MapSelectKeys({
# "version": None,
"matched_id": "fund_id",
"source_id": None,
"fund_name_amac": "fund_full_name",
"reg_code_amac": "reg_code",
"reg_time_amac": "reg_time",
"foundation_date_amac": "foundation_date",
"currency_name_amac": "currency",
"currency_type": None,
"fund_status_amac": "fund_status",
"is_abnormal_liquidation": None,
"liquidation_cause": None
})
stream = Stream(inp, (vm, cln, dd, ac, km), name="x_fund_info_private")
return stream
def stream_020003():
session = dbsession(bind=engine_r)
stmt = session.query(DBondInfo).filter(
DBondInfo.source_id == "020003").with_entities(
DBondInfo.bond_id, DBondInfo.bond_name, DBondInfo.bond_full_name,
DBondInfo.bond_type, DBondInfo.consigner)
inp = MysqlInput(session.bind, stmt)
def clean_bondtype(bond_type, bond_name, bond_full_name, consigner,
bond_id):
from functools import reduce
bond_type, bond_name, bond_full_name, consigner = [
(x or "")
for x in (bond_type, bond_name, bond_full_name, consigner)
]
# bond_type, bond_name, bond_full_name, consigner = ["深国债", "山西16Z1", "2016年山西省政府一般债券(六期)", "山西省政府"]
DIRECT_TYPE_MAP = {
"短期融资券": ("短期融资券", None),
"资产证券化": ("资产支持证券", None),
"可转债": ("可转债", None),
}
if bond_type in DIRECT_TYPE_MAP.keys():
type_l1, type_l2 = DIRECT_TYPE_MAP[bond_type]
else:
patts_subfname = "".join(".*?(%s)?" % x for x in [
"|".join(x) for x in (
("国债", ),
("政府", ),
("银行", ),
("PPN", ),
("SCP", ),
("MTN", ),
("CP", ),
("中国铁路建设", ),
("可交换", ),
("企业债", ),
("项目收益债券", ),
("证券", "期货", "保险", "中国国际金融"),
("中小企业私募债", ),
("非公开发行", ),
("建设债", ),
("集合债", ),
("公开发行"),
("公司债", ),
("中央汇金", ),
)
])
patts_subname = "".join(".*?(%s)?" % x for x in [
"|".join(x)
for x in (("ppn", ), ("scp", ), ("mtn", ), ("cp", ),
("mtn", "smecn", "prn", "gn"), ("npb", ))
])
patts_subcsg = "".join(".*?(%s)?" % x for x in [
"|".join(x)
for x in (("政府", ), ("国家开发银行", "中国农业发展银行",
"中国进出口银行"), ("银行", ), ("铁道部", ),
("证券", "期货", "保险", "中国国际金融"), ("城市建设", ), ("中央汇金", ))
])
subfname = reduce(
lambda x, y: [(x[i] or y[i]) for i in range(len(x))],
re.findall(patts_subfname, bond_full_name.lower()))
subcsg = reduce(
lambda x, y: [(x[i] or y[i]) for i in range(len(x))],
re.findall(patts_subcsg, consigner))
subname = reduce(
lambda x, y: [(x[i] or y[i]) for i in range(len(x))],
re.findall(patts_subname, bond_name.lower()))
if subname[0]:
return "定向工具", None
elif subname[1]:
return "短期融资券", None
elif subname[4]:
return "中期票据", None
elif subname[2]:
return "中期票据", None
elif subname[3]:
return "短期融资券", None
elif subname[5]:
return "企业债", None
elif bond_type == "国债":
if subfname[0]:
type_l1, type_l2 = "国债", None
elif subfname[1] or subcsg[0]:
type_l1, type_l2 = "地方政府债", None
else:
type_l1, type_l2 = None, None
elif bond_type == "金融债":
if subcsg[1]:
type_l1, type_l2 = "金融债", "政策性银行债"
elif subfname[18] or subcsg[6]:
type_l1, type_l2 = "政府支持机构债", None
elif subfname[2] or subcsg[2]:
type_l1, type_l2 = "金融债", "商业银行债"
else:
type_l1, type_l2 = "金融债", "非银金融机构债"
elif bond_type == "企债":
if subfname[7] or subcsg[3]:
type_l1, type_l2 = "政府支持机构债", None
elif subfname[8]:
type_l1, type_l2 = "可交换债", None
elif subfname[9] or subfname[10] or subfname[14] or subfname[
15] or (bond_id[-2:] == "IB") or subcsg[5]:
type_l1, type_l2 = "企业债", None
elif subfname[11] and subcsg[4]:
type_l1, type_l2 = "金融债", "非银金融机构债"
elif subfname[12]:
type_l1, type_l2 = "公司债", "中小企业私募债"
elif subfname[13]:
type_l1, type_l2 = "公司债", "非公开发行公司债"
elif subfname[16] and subfname[17]:
type_l1, type_l2 = "公司债", "一般公司债"
else:
type_l1, type_l2 = None, None
else:
type_l1, type_l2 = None, None
return type_l1, type_l2
vm = transform.ValueMap(
OrderedDict([
("__tmpcol",
(lambda bondtype, bondname, bondfname, consigner, bond_id:
clean_bondtype(bondtype, bondname, bondfname, consigner, bond_id
), DBondInfo.bond_type.name,
DBondInfo.bond_name.name, DBondInfo.bond_full_name.name,
DBondInfo.consigner.name, DBondInfo.bond_id.name)),
(BondTypeSource.type.name, (lambda x: x[0], "__tmpcol")),
(BondTypeSource.stype.name, (lambda x: x[1], "__tmpcol")),
]))
ac = transform.AddConst({
BondTypeSource.dimension.name: "债券品种",
BondTypeSource.source_id.name: "020003",
})
dp = transform.Dropna(subset=[BondTypeSource.type.name])
km = transform.MapSelectKeys({
DBondInfo.bond_id.name:
BondTypeSource.bond_id.name,
BondTypeSource.source_id.name:
None,
BondTypeSource.dimension.name:
None,
BondTypeSource.type.name:
None,
BondTypeSource.stype.name:
None,
})
s = Stream(inp, [vm, ac, dp, km])
return s
def stream_020002():
session = dbsession(bind=engine_r)
stmt = session.query(DBondInfo).filter(
DBondInfo.source_id == "020002").with_entities(
DBondInfo.bond_id, DBondInfo.bond_name, DBondInfo.bond_full_name,
DBondInfo.bond_type)
inp = MysqlInput(session.bind, stmt)
def clean_bondtype(bond_type, bond_name, bond_full_name):
from functools import reduce
bond_type, bond_name, bond_full_name = [
(x or "") for x in (bond_type, bond_name, bond_full_name)
]
DIRECT_TYPE_MAP = {
"国债": ("国债", None),
"政策性银行债": ("金融债", "政策性银行债"),
"可转换债券": ("可转债", None),
"政府支持机构债": ("政府支持机构债", None),
"企业债": ("企业债", None),
"证券公司债": ("金融债", "非银金融机构债"),
"可交换债券": ("可交换债", None),
"证券公司次级债": ("金融债", "非银金融机构债"),
"券商专项资产管理": ("资产支持证券", None),
"不动产投资信托(REITs)": ("资产支持证券", None),
"中小企业集合债券": ("企业债", None),
"项目收益债券": ("企业债", None),
}
if bond_type in DIRECT_TYPE_MAP.keys():
type_l1, type_l2 = DIRECT_TYPE_MAP[bond_type]
else:
patts_subfname = "".join(
".*?(%s)?" % x
for x in ["|".join(x) for x in (("中小企业私募债", ), ("非公开发行", ))])
patts_subname = "".join(".*?(%s)?" % x for x in [
"|".join(x)
for x in (("ppn", ), ("scp", ), ("mtn", ), ("cp", ),
("mtn", "smecn", "prn", "gn"), ("npb", ))
])
subfname = reduce(
lambda x, y: [(x[i] or y[i]) for i in range(len(x))],
re.findall(patts_subfname, bond_full_name.lower()))
subname = reduce(
lambda x, y: [(x[i] or y[i]) for i in range(len(x))],
re.findall(patts_subname, bond_name.lower()))
if subname[0]:
return "定向工具", None
elif subname[1]:
return "短期融资券", None
elif subname[4]:
return "中期票据", None
elif subname[2]:
return "中期票据", None
elif subname[3]:
return "短期融资券", None
elif subname[5]:
return "企业债", None
elif bond_type == "公司债":
if subfname[0]:
type_l1, type_l2 = "公司债", "中小企业私募债"
elif subfname[1]:
type_l1, type_l2 = "公司债", "非公开发行公司债"
else:
type_l1, type_l2 = "公司债", "一般公司债"
else:
type_l1, type_l2 = None, None
return type_l1, type_l2
vm = transform.ValueMap(
OrderedDict([
("__tmpcol",
(lambda bondtype, bondname, bondfname: clean_bondtype(
bondtype, bondname, bondfname), DBondInfo.bond_type.name,
DBondInfo.bond_name.name, DBondInfo.bond_full_name.name)),
(BondTypeSource.type.name, (lambda x: x[0], "__tmpcol")),
(BondTypeSource.stype.name, (lambda x: x[1], "__tmpcol")),
]))
ac = transform.AddConst({
BondTypeSource.dimension.name: "债券品种",
BondTypeSource.source_id.name: "020002",
})
dp = transform.Dropna(subset=[BondTypeSource.type.name])
km = transform.MapSelectKeys({
DBondInfo.bond_id.name:
BondTypeSource.bond_id.name,
BondTypeSource.source_id.name:
None,
BondTypeSource.dimension.name:
None,
BondTypeSource.type.name:
None,
BondTypeSource.stype.name:
None,
})
s = Stream(inp, [vm, ac, dp, km])
return s
def stream_010001():
session = dbsession(bind=engine_r)
stmt = session.query(DBondInfo).filter(
DBondInfo.source_id == "010001").with_entities(
DBondInfo.bond_id, DBondInfo.bond_full_name, DBondInfo.bond_type,
DBondInfo.consigner)
inp = MysqlInput(session.bind, stmt)
def clean_bondtype(bond_type, bond_full_name, consigner):
from functools import reduce
bond_type = bond_type or ""
bond_full_name = bond_full_name or ""
consigner = consigner or ""
DIRECT_TYPE_MAP = {
"记账式国债": ("国债", None),
"金融债": ("金融债", "政策性银行债"),
"地方政府债券": ("地方政府债", None),
"可交换公司债券": ("可交换债", None),
"企业债券": ("企业债", None),
"企业资产支持证券": ("资产支持证券", None),
"信贷资产支持证券": ("资产支持证券", None),
"中小企业私募债券": ("公司债", "中小企业私募债"),
}
if bond_type in DIRECT_TYPE_MAP.keys():
type_l1, type_l2 = DIRECT_TYPE_MAP[bond_type]
else:
# bond_type = "公司债券"
# bond_full_name, consigner = "招商证券股份有限公司2012年公司债券（3+2）", "招商证券股份有限公司"
patts_subname = "".join(".*?(%s)?" % x for x in [
"|".join(x) for x in (("可转换", ), ("证券", "期货", "保险", "中国国际金融"),
("中国铁路建设", ))
])
patts_subcsg = "".join(".*?(%s)?" % x for x in [
"|".join(x)
for x in (("银行", ), ("证券", "期货", "保险", "中国国际金融"), ("铁道部", ))
])
subname = reduce(
lambda x, y: [(x[i] or y[i]) for i in range(len(x))],
re.findall(patts_subname, bond_full_name))
subcsg = reduce(
lambda x, y: [(x[i] or y[i]) for i in range(len(x))],
re.findall(patts_subcsg, consigner))
if bond_type == "次级债券":
if subcsg[0]:
type_l1, type_l2 = "金融债", None
else:
type_l1, type_l2 = "金融债", "非银金融机构债"
elif bond_type == "非公开发行公司债券":
if subname[0]:
type_l1, type_l2 = "可转债", None
elif subname[1] and subcsg[1]:
type_l1, type_l2 = "金融债", "非银金融机构债"
elif subname[2] or subcsg[2]:
type_l1, type_l2 = "政府支持机构债", None
else:
type_l1, type_l2 = "公司债", "非公开发行公司债"
elif bond_type == "公司债券":
if subname[1] and subcsg[1]:
type_l1, type_l2 = "金融债", "非银金融机构债"
else:
type_l1, type_l2 = "公司债", "一般公司债"
else:
type_l1, type_l2 = None, None
return type_l1, type_l2
vm = transform.ValueMap(
OrderedDict([
("__tmpcol",
(lambda bondtype, bondfname, consigner: clean_bondtype(
bondtype, bondfname, consigner), DBondInfo.bond_type.name,
DBondInfo.bond_full_name.name, DBondInfo.consigner.name)),
(BondTypeSource.type.name, (lambda x: x[0], "__tmpcol")),
(BondTypeSource.stype.name, (lambda x: x[1], "__tmpcol")),
]))
ac = transform.AddConst({
BondTypeSource.dimension.name: "债券品种",
BondTypeSource.source_id.name: "010001",
})
dp = transform.Dropna(subset=[BondTypeSource.type.name])
km = transform.MapSelectKeys({
DBondInfo.bond_id.name:
BondTypeSource.bond_id.name,
BondTypeSource.source_id.name:
None,
BondTypeSource.dimension.name:
None,
BondTypeSource.type.name:
None,
BondTypeSource.stype.name:
None,
})
s = Stream(inp, [vm, ac, dp, km])
return s