def generate_detail(key, is_login_user):
cur = get_cursor(
"""
SELECT DISTINCT d.*,
e.strategy_type as 策略,
e.hold_id,
e.hold_price as 持有成本,
e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid,
case when cb_mov2_id > 0 then 1 when cb_mov2_id = 0 then 0 ELSE -1 end as _sign,
c.bond_code,
c.stock_code,
c.cb_name_id as 名称,
round(cb_premium_id * 100, 2) || '%' as 溢价率,
cb_price2_id as '转债价格',
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
remain_amount as '余额(亿元)',
round(cb_trade_amount2_id * 100, 2) || '%' as '换手率(%)',
cb_trade_amount_id as '成交额(百万)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(bt_yield * 100, 2) || '%' as 到期收益率,
c.stock_name as 正股名称,
c.industry as '行业',
c.sub_industry as '子行业',
rank_gross_rate || '【' || level_gross_rate || '】' as 毛利率排名,
rank_net_margin || '【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio || '【' || level_net_profit_ratio || '】' as 利润率排名,
rank_roe || '【' || level_roe || '】' as ROE排名,
rank_pe || '【' || level_pe || '】' as PE排名,
rank_pb || '【' || level_pb || '】' as PB排名,
rank_net_asset || '【' || level_net_asset || '】' as 净资产排名,
rank_market_cap || '【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue, 2) as '营收(亿元)',
s.yoy_revenue_rate || '%' as '营收同比',
gross_rate || '|' || avg_gross_rate as '毛利率|行业均值',
round(s.net, 2) || '|' || avg_net_margin as '净利润|均值(亿元)',
s.yoy_net_rate || '%' as '净利润同比',
s.margin || '|' || avg_net_profit_ratio as '利润率|行业均值',
s.yoy_margin_rate || '%' as '利润率同比',
s.roe || '|' || avg_roe as 'ROE|行业均值',
s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio, 2) || '%' as 负债率,
s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe || '|' || avg_pe as 'PE(动)|均值',
c.stock_pb || '|' || avg_pb as 'PB|行业均值',
net_asset || '|' || avg_net_asset as '净资产|行业均值',
market_cap || '|' || avg_market_cap as '市值|均值(亿元)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' ||
fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用',
duration as 续存期,
cb_ma20_deviate as 'ma20乖离',
cb_hot as 热门度
from (select *
from (SELECT DISTINCT c.*
from changed_bond c WHERE sub_industry = :sub_industry)
) c
LEFT join stock_report s on c.stock_code = s.stock_code) d
left join
(select id as hold_id, bond_code, hold_price, hold_amount, strategy_type
from hold_bond
where id in (select id
from hold_bond
where id
in (SELECT min(id)
from hold_bond
where hold_owner = 'me' and hold_amount != -1
group by bond_code))
) e
on d.bond_code = e.bond_code
order by 溢价率
""", {'sub_industry': key})
table, table_html = generate_table_html_with_data(
'',
cur,
'',
need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
ignore_fields=['持有数量'],
is_login_user=is_login_user)
# html = generate_scatter_html_with_one_table(table,
# title=key + '行业可转债',
# use_personal_features=is_login_user)
# html = table_html
return table_html
def draw_view(is_login_user, key, rise, url):
try:
html = ''
cur = get_cursor("""
select a.industry, round(b.涨跌幅 / a.个数*100, 2) as 涨跌, 余额
from (SELECT industry, count(industry) as 个数 from changed_bond group by industry) a,
(SELECT industry, sum(cb_mov2_id) as 涨跌幅 from changed_bond group by industry) b,
(SELECT industry, round(sum(remain_amount),2) as 余额 from changed_bond group by industry) c
where a.industry = b.industry
and a.industry = c.industry
order by 涨跌 desc
""")
html += generate_treemap_html(
cur,
'=========可转债涨跌行业分布=========',
'industry',
'余额',
'/view_tree_map_industry.html',
area_data_name='余额',
area_data_unit='亿元',
)
if key is not None and key.strip(' ') != '' and rise is not None:
rise = float(rise)
up = 0
down = 0
if rise >= 0:
up = 10
else:
down = 10
cur = get_cursor(
"""
SELECT DISTINCT d.*,
e.strategy_type as 策略,
e.hold_id,
e.hold_price as 持有成本,
e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid,
case when cb_mov2_id > 0 then 1 when cb_mov2_id = 0 then 0 ELSE -1 end as _sign,
c.bond_code,
c.stock_code,
c.cb_name_id as 名称,
cb_mov2_id,
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
cb_price2_id as '转债价格',
round(cb_premium_id * 100, 2) || '%' as 溢价率,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
remain_amount as '余额(亿元)',
round(cb_trade_amount2_id * 100, 2) || '%' as '换手率(%)',
cb_trade_amount_id as '成交额(百万)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(bt_yield * 100, 2) || '%' as 到期收益率,
c.stock_name as 正股名称,
c.industry as '行业',
c.sub_industry as '子行业',
rank_gross_rate || '【' || level_gross_rate || '】' as 毛利率排名,
rank_net_margin || '【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio || '【' || level_net_profit_ratio || '】' as 利润率排名,
rank_roe || '【' || level_roe || '】' as ROE排名,
rank_pe || '【' || level_pe || '】' as PE排名,
rank_pb || '【' || level_pb || '】' as PB排名,
rank_net_asset || '【' || level_net_asset || '】' as 净资产排名,
rank_market_cap || '【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue, 2) as '营收(亿元)',
s.yoy_revenue_rate || '%' as '营收同比',
gross_rate || '|' || avg_gross_rate as '毛利率|行业均值',
round(s.net, 2) || '|' || avg_net_margin as '净利润|均值(亿元)',
s.yoy_net_rate || '%' as '净利润同比',
s.margin || '|' || avg_net_profit_ratio as '利润率|行业均值',
s.yoy_margin_rate || '%' as '利润率同比',
s.roe || '|' || avg_roe as 'ROE|行业均值',
s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio, 2) || '%' as 负债率,
s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe || '|' || avg_pe as 'PE(动)|均值',
c.stock_pb || '|' || avg_pb as 'PB|行业均值',
net_asset || '|' || avg_net_asset as '净资产|行业均值',
market_cap || '|' || avg_market_cap as '市值|均值(亿元)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' ||
fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用',
duration as 续存期,
cb_ma20_deviate as 'ma20乖离',
cb_hot as 热门度
from (select *
from (SELECT DISTINCT c.*
from changed_bond c WHERE industry = :industry
order by cb_mov2_id DESC
limit :up_size)
UNION
select *
from (SELECT DISTINCT c.*
from changed_bond_view c WHERE industry = :industry
order by cb_mov2_id ASC
limit :down_size)) c
LEFT join stock_report s on c.stock_code = s.stock_code) d
left join
(select id as hold_id, bond_code, hold_price, hold_amount, strategy_type
from hold_bond
where id in (select id
from hold_bond
where id
in (SELECT min(id)
from hold_bond
where hold_owner = 'me' and hold_amount != -1
group by bond_code))
) e
on d.bond_code = e.bond_code
order by _sign desc, abs(cb_mov2_id) DESC
""", {
'industry': key,
'up_size': up,
'down_size': down
})
table, table_html = generate_table_html_with_data(
'',
cur,
'',
need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
is_login_user=is_login_user)
html += "<div id='cb_detail_list'>"
html += generate_scatter_html_with_one_table(
table,
title=key + '行业可转债分布',
sub_title='仅展示涨/跌幅top10的可转债',
use_personal_features=is_login_user)
html += table_html
html += '</div>'
return '可转债涨跌分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view():
try:
html = ''
nav_html_list = {}
# 用来画收益曲线图的数据
cur = get_cursor("""
SELECT
date as 时间,
day_rate_1 || '%' as 低溢价率策略日收益率,
day_rate_2 || '%' as 高收益率策略日收益率,
day_rate_3 || '%' as '低余额+双低策略日收益率',
day_rate_4 || '%' as '低溢价率+双低策略日收益率',
--cb_day_yield || '%' as 可转债指数日收益率,
--hs_day_yield || '%' as 沪深300日收益率,
--round(cb_all_yield-1.84, 2) as 可转债指数累积收益率,
--round(hs_all_yield-0.06, 2) as 沪深300累积收益率,
all_rate_1 as 低溢价率策略累积收益率,
all_rate_2 as 高收益率策略累积收益率,
all_rate_3 as '低余额+双低策略累积收益率',
all_rate_4 as '低溢价率+双低策略累积收益率'
from strategy_group_yield
order by date desc
""")
# 日收益率看表格, 累积收益率看折线图
table, table_html = generate_table_html_with_data(
None,
cur,
html,
need_title=False,
remark_fields=[
'可转债指数日收益率', '沪深300日收益率', '低溢价率策略日收益率', '高收益率策略日收益率',
'低余额+双低策略日收益率', '低溢价率+双低策略日收益率'
],
ignore_fields=[
'可转债指数累积收益率', '沪深300累积收益率', '低余额+双低策略累积收益率', '低溢价率+双低策略累积收益率',
'高收益率策略累积收益率', '低溢价率策略累积收益率'
],
nav_html_list=nav_html_list,
table_width='800px')
rows = []
dict_rows = []
for row in table._rows:
rows.append(row)
dict_row = db_utils.get_dict_row(cur, row)
dict_rows.append(dict_row)
line_html = generate_line_html(dict_rows)
html = """<center>
""" + line_html + "<br/><br/><br/>" + table_html + """
</center>
"""
return html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view(is_login_user, key, start, end, rise, url):
try:
html = ''
cur = get_cursor("""
select i as 价格区间, round(s_m / c_i*100, 2) as 涨跌, c_i as 数量
from (select DISTINCT(_interval) as i, count(_interval) as c_i, sum(cb_mov2_id) as s_m
from (SELECT cb_price2_id,
cb_mov2_id,
case
when cb_price2_id <= 80 then '<=80元'
when cb_price2_id > 80 and cb_price2_id <= 90 then '80~90元'
when cb_price2_id > 90 and cb_price2_id <= 100 then '90~100元'
when cb_price2_id > 100 and cb_price2_id <= 110 then '100~110元'
when cb_price2_id > 110 and cb_price2_id <= 120 then '110~120元'
when cb_price2_id > 120 and cb_price2_id <= 130 then '120~130元'
when cb_price2_id > 130 and cb_price2_id <= 150 then '130~150元'
when cb_price2_id > 150 and cb_price2_id <= 200 then '150~200元'
when cb_price2_id > 200 then '>200元' end
as _interval,
case
when cb_price2_id <= 80 then 1
when cb_price2_id > 80 and cb_price2_id <= 90 then 2
when cb_price2_id > 90 and cb_price2_id <= 100 then 3
when cb_price2_id > 100 and cb_price2_id <= 110 then 4
when cb_price2_id > 110 and cb_price2_id <= 120 then 5
when cb_price2_id > 120 and cb_price2_id <= 130 then 6
when cb_price2_id > 130 and cb_price2_id <= 150 then 7
when cb_price2_id > 150 and cb_price2_id <= 200 then 8
when cb_price2_id > 200 then 9 end
as _interval_idx
from changed_bond)
GROUP by _interval
order by _interval_idx)
""")
html += generate_treemap_html(cur, '=========可转债涨跌价格分布=========',
'价格区间', '数量',
'/view_tree_map_price.html')
if start is not None or end is not None and rise is not None:
rise = float(rise)
up = 0
down = 0
if rise >= 0:
up = 10
else:
down = 10
cur = get_cursor(
"""
SELECT DISTINCT d.*,
e.strategy_type as 策略,
e.hold_id,
e.hold_price as 持有成本,
e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid,
case when cb_mov2_id > 0 then 1 when cb_mov2_id = 0 then 0 ELSE -1 end as _sign,
c.bond_code,
c.stock_code,
c.cb_name_id as 名称,
cb_mov2_id,
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
cb_price2_id as '转债价格',
round(cb_premium_id * 100, 2) || '%' as 溢价率,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
remain_amount as '余额(亿元)',
round(cb_trade_amount2_id * 100, 2) || '%' as '换手率(%)',
cb_trade_amount_id as '成交额(百万)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(bt_yield * 100, 2) || '%' as 到期收益率,
c.stock_name as 正股名称,
c.industry as '行业',
c.sub_industry as '子行业',
rank_gross_rate || '【' || level_gross_rate || '】' as 毛利率排名,
rank_net_margin || '【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio || '【' || level_net_profit_ratio || '】' as 利润率排名,
rank_roe || '【' || level_roe || '】' as ROE排名,
rank_pe || '【' || level_pe || '】' as PE排名,
rank_pb || '【' || level_pb || '】' as PB排名,
rank_net_asset || '【' || level_net_asset || '】' as 净资产排名,
rank_market_cap || '【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue, 2) as '营收(亿元)',
s.yoy_revenue_rate || '%' as '营收同比',
gross_rate || '|' || avg_gross_rate as '毛利率|行业均值',
round(s.net, 2) || '|' || avg_net_margin as '净利润|均值(亿元)',
s.yoy_net_rate || '%' as '净利润同比',
s.margin || '|' || avg_net_profit_ratio as '利润率|行业均值',
s.yoy_margin_rate || '%' as '利润率同比',
s.roe || '|' || avg_roe as 'ROE|行业均值',
s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio, 2) || '%' as 负债率,
s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe || '|' || avg_pe as 'PE(动)|均值',
c.stock_pb || '|' || avg_pb as 'PB|行业均值',
net_asset || '|' || avg_net_asset as '净资产|行业均值',
market_cap || '|' || avg_market_cap as '市值|均值(亿元)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' ||
fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用',
duration as 续存期,
cb_ma20_deviate as 'ma20乖离',
cb_hot as 热门度
from (select *
from (SELECT DISTINCT c.*
from changed_bond c WHERE cb_price2_id > :start and cb_price2_id <= :end
order by cb_mov2_id DESC
limit :up_size)
UNION
select *
from (SELECT DISTINCT c.*
from changed_bond_view c WHERE cb_price2_id > :start and cb_price2_id <= :end
order by cb_mov2_id ASC
limit :down_size)) c
LEFT join stock_report s on c.stock_code = s.stock_code) d
left join
(select id as hold_id, bond_code, hold_price, hold_amount, strategy_type
from hold_bond
where id in (select id
from hold_bond
where id
in (SELECT min(id)
from hold_bond
where hold_owner = 'me' and hold_amount != -1
group by bond_code))
) e
on d.bond_code = e.bond_code
order by _sign desc, abs(cb_mov2_id) DESC
""", {
'start': start,
'end': end,
'up_size': up,
'down_size': down
})
table, table_html = generate_table_html_with_data(
'',
cur,
'',
need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
is_login_user=is_login_user)
html += "<div id='cb_detail_list'>"
html += generate_scatter_html_with_one_table(
table,
title='价格' + key + '的可转债涨跌分布',
sub_title='仅展示涨/跌幅top10的可转债',
use_personal_features=is_login_user)
html += table_html
html += '</div>'
return '可转债涨跌分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def generate_detail(key, is_login_user):
cur = get_cursor(
"""
SELECT DISTINCT d.*, e.hold_id, e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid,
c.bond_code,
c.stock_code,
c.cb_name_id as 名称,
round(cb_premium_id * 100, 2) || '%' as 溢价率,
cb_price2_id as '转债价格',
cb_t_id as 距离转股日,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(bt_yield * 100, 2) || '%' as 到期收益率,
round(cb_trade_amount2_id * 100, 2) || '%' as '换手率(%)',
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
c.stock_name as 正股名称,
c.industry as '行业',
c.sub_industry as '子行业',
e.theme as 题材概念,
rank_gross_rate || '【' || level_gross_rate || '】' as 毛利率排名,
rank_net_margin || '【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio || '【' || level_net_profit_ratio || '】' as 利润率排名,
rank_roe || '【' || level_roe || '】' as ROE排名,
rank_pe || '【' || level_pe || '】' as PE排名,
rank_pb || '【' || level_pb || '】' as PB排名,
rank_net_asset || '【' || level_net_asset || '】' as 净资产排名,
rank_market_cap || '【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue, 2) as '营收(亿元)',
s.yoy_revenue_rate || '%' as '营收同比',
gross_rate || '|' || avg_gross_rate as '毛利率|行业均值',
round(s.net, 2) || '|' || avg_net_margin as '净利润|均值(亿元)',
s.yoy_net_rate || '%' as '净利润同比',
s.margin || '|' || avg_net_profit_ratio as '利润率|行业均值',
s.yoy_margin_rate || '%' as '利润率同比',
s.roe || '|' || avg_roe as 'ROE|行业均值',
s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio, 2) || '%' as 负债率,
s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe || '|' || avg_pe as 'PE(动)|均值',
c.stock_pb || '|' || avg_pb as 'PB|行业均值',
net_asset || '|' || avg_net_asset as '净资产|行业均值',
market_cap || '|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)',
round(cb_to_share_shares * 100, 2) || '%' as '余额/股本(%)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' ||
fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用',
duration as 续存期,
cb_ma20_deviate as 'ma20乖离',
cb_hot as 热门度,
cb_trade_amount_id as '成交额(百万)',
e.interest as 各期利息,
case when e.ensure is not null then '有' else '无' END as 担保,
case when e.buy_back_term is not null then e.buy_back_term else '无' END as 回售条款,
case when e.down_revise_term is not null then e.down_revise_term else '无' END as 下修条款,
case when e.enforce_get_term is not null then e.enforce_get_term else '无' END as 强赎条款
from (changed_bond c left join stock_report s on c.stock_code = s.stock_code)
left join changed_bond_extend e on c.bond_code = e.bond_code where theme like :theme
) d
left join
(select id as hold_id, bond_code, cb_name_id, hold_price, hold_amount
from hold_bond
where id in (select id
from hold_bond
where id
in
(SELECT min(id) from hold_bond where hold_owner = 'me' and hold_amount > 0 group by bond_code))
) e
on d.bond_code = e.bond_code
""", {'theme': '%' + key + '%'})
table, table_html = generate_table_html_with_data(
'',
cur,
'',
need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
ignore_fields=['持有数量'],
is_login_user=is_login_user)
html = generate_scatter_html_with_one_table(
table, title=key + '概念相关可转债', use_personal_features=is_login_user)
html += table_html
return html