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