hhhh

from pyecharts import options as opts  # 全局、系列配置
from pyecharts.charts import Bar, Grid, Line  # 柱形图
from pyecharts.globals import ThemeType  # 主题风格
from pyecharts.charts import Kline, Line, Bar, Gauge, Pie, Scatter, TreeMap  # K线图、线形图、柱状图、仪表盘、饼图、散点图、矩形树图
from pyecharts.charts import Grid, Tab  # 组合组件、分页组件
from pyecharts.components import Table  # 表格组件
import time  # 时间模块
import pandas as pd  # Pandas模块
import numpy as np  # Numpy模块
from sqlalchemy import create_engine  # 数据引擎模块

conn = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')  # 创建引擎

##########################################################################################################################################################
# 统计数据

# 上证指数
sql01 = '''
            SELECT
                trade_date,     -- 交易日期
                `open`,            -- 开盘价
                high,           -- 最高价
                low,            -- 最低价
                `close`,        -- 收盘价
                pct_chg         -- 涨跌幅
            FROM
                stock_daily 
            WHERE
                ts_code = '000001.SH'
'''

# 深证指数
sql02 = '''
            SELECT
                trade_date,     -- 交易日期
                `open`,            -- 开盘价
                high,           -- 最高价
                low,            -- 最低价
                `close`,        -- 收盘价
                pct_chg         -- 涨跌幅
            FROM
                stock_daily
            WHERE
                ts_code = '399001.SZ'
'''

# 创业板指
sql03 = '''
            SELECT
                trade_date,     -- 交易日期
                `open`,            -- 开盘价
                high,           -- 最高价
                low,            -- 最低价
                `close`,        -- 收盘价
                pct_chg         -- 涨跌幅
            FROM
                stock_daily
            WHERE
                ts_code = '399006.SZ'
'''

# shibor
sql04 = '''
            SELECT
                date,           -- 交易日期
                `on`            -- shibor利率
            FROM
                shibor
'''

# 全市场成交额
sql05 = '''
            SELECT
                trade_date,                             -- 交易日期
                SUM(amount)/100000000 AS 'amount'       -- 成交总额，亿
            FROM
                stock_daily
            WHERE 
                ts_code NOT IN ('000001.SH','399001.SZ','399006.SZ')
            GROUP BY
                trade_date
'''

# 全市场竞价总额
sql06 = '''
            SELECT
                date,                             -- 交易日期
                sum(money)/100000000 AS money     -- 竞价总额，亿
            FROM
                call_auction
            GROUP BY
                date
'''

# 北向资金
sql07 = '''
            SELECT
                trade_date,         -- 交易日期
                hgt/100,            -- 沪股通，亿
                sgt/100,            -- 深股通，亿
                north_money/100     -- 北向资金，亿
            FROM
                hsgt_moneyflow
'''

# 大单流向
sql08 = '''
            SELECT
                trade_date,                                                -- 交易日期
                SUM(buy_md_amount-sell_md_amount)/1000 AS 'md_amount',     -- 中单净额/亿
                SUM(buy_lg_amount-sell_lg_amount)/1000 AS 'lg_amount',     -- 大单净额/亿
                SUM(buy_elg_amount-sell_elg_amount)/1000 AS 'elg_amount',  -- 超大单净额/亿
                SUM(net_mf_amount)/1000 AS 'net_mf_amount'                 -- 资金净额/亿
            FROM
                stock_moneyflow 
            GROUP BY
                trade_date
'''

# 游资资金流向
sql09 = '''
            SELECT 
                date,                                                                                                      -- 交易日期
                SUM(CASE WHEN (youziicon=1 AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'yxb',                   -- 一线游资买入，亿
                SUM(CASE WHEN (youziicon=2 AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'djb',                   -- 顶级游资买入，亿
                SUM(CASE WHEN (youziicon=1 AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'zmb',                   -- 知名游资买入，亿
                SUM(CASE WHEN (dname='机构专用' AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'jgb',               -- 机构买入，亿
                SUM(CASE WHEN (dname LIKE '%股通专用' AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'wzb',           -- 外资买入，亿
                SUM(CASE WHEN (youziicon=1 AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'yxs',                 -- 一线游资卖出，亿
                SUM(CASE WHEN (youziicon=2 AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'djs',                 -- 顶级游资卖出，亿
                SUM(CASE WHEN (youziicon=1 AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'zms',                 -- 知名游资卖出，亿
                SUM(CASE WHEN (dname='机构专用' AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'jgs',             -- 机构卖出，亿
                SUM(CASE WHEN (dname LIKE '%股通专用' AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'wzs'        -- 外资卖出，亿
            FROM 
                lhb_list
            GROUP BY 
                date
'''

# 市场情绪
sql10 = '''
            SELECT
                t1.trade_date,                    -- 交易日期
                t1.zt_fd_amount/100000000,        -- 涨停封单/亿
                t2.dt_fd_amount/100000000,        -- 跌停封单/亿
                t3.1b,                            -- 1板
                t3.2b,                            -- 2板
                t3.3b,                            -- 3板
                t3.4b,                            -- 4板
                t3.gdb,                            -- 高度板
                t4.dr,                            -- 大肉股
                t4.dm,                            -- 大面股
                t5.szd5,                        -- 涨幅大于5%
                t5.xdd5,                        -- 跌幅大于5%
                t6.zrzt,                        -- 自然涨停
                t6.ztzb,                        -- 自然跌停
                t6.szjs,                        -- 上涨家数
                t6.xdjs,                        -- 下跌家数
                t6.zt,                            -- 涨停家数
                t6.dt,                            -- 跌停家数
                t6.zbl,                            -- 炸板率
                t6.cgl,                            -- 昨日打板成功率
                t6.yll                            -- 昨日打板盈利率
            FROM
                -- 涨停封单金额
                (SELECT trade_date,sum(fd_amount) AS 'zt_fd_amount' FROM limit_stock_list WHERE `limit` = 'U' GROUP BY trade_date) t1
                
                LEFT JOIN
                        -- 跌停封单金额
                        (SELECT trade_date,sum(fd_amount) AS 'dt_fd_amount' FROM limit_stock_list WHERE `limit` = 'D' GROUP BY trade_date) t2 ON t1.trade_date=t2.trade_date
                
                LEFT JOIN
                        -- 一字板,2板,3板以上,高度板
                        (SELECT date,COUNT(DISTINCT CASE WHEN lbc = 1 THEN `code` ELSE 0 END ) AS '1b',COUNT(DISTINCT CASE WHEN lbc = 2 THEN `code` ELSE 0 END ) AS '2b',COUNT(DISTINCT CASE WHEN lbc = 3 THEN `code` ELSE 0 END ) AS '3b',COUNT(DISTINCT CASE WHEN lbc = 4 THEN `code` ELSE 0 END ) AS '4b',COUNT(DISTINCT CASE WHEN lbc > 4 THEN `code` ELSE 0 END ) AS 'gdb' FROM limit_stock_lbc GROUP BY date) t3 ON t1.trade_date=t3.date
                
                LEFT JOIN
                        -- 大肉股,大面股
                        (SELECT trade_date,COUNT(DISTINCT CASE WHEN ((`close`-`open`)/`open`)>=0.12 THEN ts_code ELSE 0 END) AS 'dr',COUNT(DISTINCT CASE WHEN ((`close`-`open`)/`open`)<-0.12 THEN ts_code ELSE 0 END) AS 'dm' FROM stock_daily GROUP BY trade_date) t4 ON t1.trade_date=t4.trade_date
            
                LEFT JOIN
                        -- 涨幅5%以上,跌幅5%以上
                        (SELECT trade_date,COUNT(DISTINCT CASE WHEN pct_chg >= 5 THEN ts_code ELSE 0 END) AS 'szd5',COUNT(DISTINCT CASE WHEN pct_chg <=- 5 THEN ts_code ELSE 0 END) AS 'xdd5' FROM stock_daily GROUP BY trade_date) t5 ON t1.trade_date=t5.trade_date
                
                LEFT JOIN
                        -- 自然涨停,涨停炸板,上涨家数,下跌家数,涨停家数,跌停家数,炸板率,昨日打板成功率,昨日打板盈利率
                        (SELECT `day`,zrzt, ztzb, szjs, xdjs, zt, dt, zbl, cgl, yll FROM scqx) t6 ON t1.trade_date=t6.`day`
            ORDER BY t1.trade_date DESC
'''

# 异动统计
sql11 = '''
            SELECT
                t1.`code`,                                                                                                                                                  -- 股票代码
                t1.`name`,                                                                                                                                                  -- 股票名称
                COUNT(CASE WHEN (chg_type IN ('8201', '8202', '8193', '4', '32', '64', '8207', '8209', '8211', '8213', '8215')) THEN t1.`code` ELSE 0 END) AS 'now_df',     -- 当天多方异动统计
                COUNT(CASE WHEN (chg_type IN ('8204', '8203', '8194', '8', '16', '128', '8208', '8210', '8212', '8214', '8216')) THEN t1.`code` ELSE 0 END) AS 'now_kf',    -- 当天空方异动统计
                t2.thrity_df,                                                                                                                                               -- 近30日多方异动统计
                t2.thrity_kf                                                                                                                                                -- 近30日空方异动统计
            FROM
                stock_change t1
                LEFT JOIN (
                SELECT
                    `code`,
                    -- 近30日多方异动统计
                    COUNT(CASE WHEN (chg_type IN ('8201', '8202', '8193', '4', '32', '64', '8207', '8209', '8211', '8213', '8215')) THEN `code` ELSE 0 END)AS 'thrity_df',
                    -- 近30日空方异动统计
                    COUNT(CASE WHEN (chg_type IN ('8204', '8203', '8194', '8', '16', '128', '8208', '8210', '8212', '8214', '8216')) THEN `code` ELSE 0 END)AS 'thrity_kf'
                FROM
                    stock_change 
                WHERE
                    date(chg_time) BETWEEN date_SUB(date(NOW()), INTERVAL 30 DAY ) AND date(NOW()) 
                GROUP BY
                    `code` 
            ) t2 ON t1.`code` = t2.`code` 
            WHERE
                date(chg_time) = date(now()) 
            GROUP BY
                `code`
'''
##########################################################################################################################################################
# 公共数据

# 所属板块
sql13 = '''
            SELECT
                ts_code,            -- 股票代码
                industry            -- 所属行业
            FROM
                stock_basic
'''

# 所属概念
sql14 = '''
            SELECT
                ts_code,                                                -- 股票代码
                GROUP_CONCAT(concept_name) AS 'concept_name'            -- 所属概念
            FROM
                concept_detail 
            GROUP BY
                ts_code
'''

##########################################################################################################################################################
# 明细数据

# 主力净额
sql15 = '''
            SELECT
                t1.trade_date,                                  -- 交易日期
                t1.ts_code,                                     -- 股票代码
                t3.stk_name,                                    -- 股票名称
                t1.`close`,                                     -- 股票价格
                t1.pct_chg,                                     -- 涨跌幅
                t1.amount/100000 AS 'amount',                   -- 成交额,亿
                t2.net_mf_amount/10000 AS 'net_mf_amount'       -- 主力金额，亿
            FROM
                stock_daily t1
                LEFT JOIN stock_moneyflow t2 ON t1.ts_code = t2.ts_code AND t1.trade_date = t2.trade_date
            WHERE
                t1.ts_code NOT IN ('000001.SH', '399001.SZ', '399006.SZ') AND t1.trade_date = date(NOW()) 
            ORDER BY 
                net_mf_amount DESC
'''

# 持股低于5%所有流通股市值
sql16 = '''
            SELECT
                t1.trade_date,                                                             -- 交易日期
                t1.ts_code,                                                                -- 股票代码
                t2.stk_name,                                                               -- 股票名称
                (t1.float_share * 10000- t2.cir_num)/100000000 AS 'flow_share'             -- 持股低于5%自有流通股市值，亿
            FROM
                stock_daily_basic t1
                LEFT JOIN flow_holder t2 ON t1.ts_code = t2.stk_code 
            WHERE
                t1.trade_date = date(now())
'''


# 北向资金排行榜明细
sql17 = '''
            SELECT
                t1.date,                                                            -- 交易日期
                t1.`code`,                                                          -- 股票代码
                t1.sharesz / 100000000 AS sharesz,                                  -- 今日持股市值，亿
                t1.sharesz_chg_one / 100000000 AS sharesz_chg_one,                  -- 今日增持市值，亿
                t2.thrity_sharesz_chg_one / 100000000 AS thrity_sharesz_chg_one     -- 近30日增持市值，亿
            FROM
                hsgt_detail t1
                LEFT JOIN (
                SELECT
                    `code`,
                    SUM( sharesz_chg_one ) AS thrity_sharesz_chg_one 
                FROM
                    hsgt_detail 
                WHERE
                    date BETWEEN date_SUB(date(NOW()), INTERVAL 30 DAY ) AND date(NOW()) 
                GROUP BY
                    `code` 
                ) t2 ON t1.`code` = t2.`code` 
            WHERE
                date = ( SELECT DISTINCT date FROM hsgt_detail ORDER BY date DESC LIMIT 1, 1 )
'''


# 异动明细
sql18 = '''
            SELECT
                (case when time(chg_time) between '09:00:00' and '10:00:00' then '09:00-10:00'
                     when time(chg_time) between '10:00:00' and '10:30:00' then '10:00-10:30'
                     when time(chg_time) between '10:30:00' and '11:00:00' then '10:30-11:00'
                     when time(chg_time) between '11:00:00' and '11:30:00' then '11:00-11:30'
                     
                     when time(chg_time) between '13:00:00' and '13:30:00' then '13:00-13:30'
                     when time(chg_time) between '13:30:00' and '14:00:00' then '13:30-14:00'
                     when time(chg_time) between '14:00:00' and '14:30:00' then '14:00-14:30'
                     when time(chg_time) between '14:30:00' and '15:00:00' then '14:30-15:00'
                     else 0 end) as 'chg_time',                                                                                                                             -- 时间区间
                t1.`code`,                                                                                                                                                  -- 股票代码
                t1.`name`,                                                                                                                                                  -- 股票名称
                COUNT(CASE WHEN (t1.chg_type IN ('8201', '8202', '8193', '4', '32', '64', '8207', '8209', '8211', '8213', '8215')) THEN t1.`code` ELSE 0 END) AS 'now_df',  -- 当天多方异动统计
                COUNT(CASE WHEN (t1.chg_type IN ('8204', '8203', '8194', '8', '16', '128', '8208', '8210', '8212', '8214', '8216')) THEN t1.`code` ELSE 0 END) AS 'now_kf'  -- 当天空方异动统计
            FROM
                stock_change t1
                LEFT JOIN change_type t2 ON t1.chg_type = t2.chg_type
            WHERE
                date(chg_time)='20200706'
            GROUP BY
                chg_time,`code`
'''

# 涨停板明细
sql19 = '''
            SELECT
                t1.ts_code,                   -- 股票代码
                t1.`close`,                   -- 股票价格
                t1.pct_chg,                   -- 涨跌幅
                t1.fd_amount/10000,           -- 封单金额，万
                t1.last_time,                 -- 最后一次涨停时间
                t1.open_times,                -- 封单次数
                t2.lbc                        -- 连板数
            FROM
                limit_stock_list t1
                LEFT JOIN limit_stock_lbc t2 ON t1.trade_date = t2.date AND t1.ts_code = t2.CODE 
            WHERE
                t1.trade_date = NOW() AND t1.`limit` = 'U'

'''


# 龙虎榜明细
sql19 = '''
            SELECT
                t1.`code`,                                                                            -- 股票代码
                t2.`name`,                                                                            -- 股票名称
                (CASE
                        WHEN youziicon = 1 THEN '一线游资' 
                        WHEN youziicon = 2 THEN '顶级游资' 
                        WHEN youziicon = 1 THEN '知名游资' 
                        WHEN youziicon = 0 AND ( dname = '机构专用' ) THEN '机构' 
                        WHEN youziicon = 0 AND ( dname LIKE '%股通专用' ) THEN '外资' 
                        ELSE '其他' END) AS 'fund_type',                                               -- 资金类型
                    groupicon,                                                                        -- 资金标签
                    buy/10000 AS 'buy',                                                             -- 买入金额，万
                    sell/10000 AS 'sell'                                                            -- 卖出金额，万
            FROM
                    lhb_list t1
            LEFT JOIN hot_concept t2 ON t1.date=t2.date AND t1.`code`=t2.`code`
            WHERE
                    t1.date = date(now()) AND buy>=50000000            
            GROUP BY
                t1.`code`

'''

# 竞价明细
sql20 = '''
            SELECT
                `code`,                         -- 股票代码
                money/10000 AS money          -- 竞价总额，万
            FROM
                `call_auction` 
            WHERE
                date = date(now())
'''


##########################################################################################################################################################
# 图形绘制


#1、近90日上证指数走势图
data01 = pd.read_sql(sql01,conn)

date = list(data01['trade_date'])
list = []
for index,row in data01.iterrows():
    list.append([row['open'],row['close'],row['low'],row['high'])

kline01 = (
    Kline()
    .add_xaxis(date)
    .add_yaxis("上证指数", list)
    .set_global_opts(
        xaxis_opts=opts.AxisOpts(is_scale=True),
        yaxis_opts=opts.AxisOpts(
            is_scale=True,
            splitarea_opts=opts.SplitAreaOpts(
                is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
            ),
        ),
        title_opts=opts.TitleOpts(title="近90日上证指数走势图"),
    )
    .render("kline_split_area.html")
)






# 2、近90日shibor利率趋势图
data02 = pd.read_sql(sql04,conn)

line02 = (
    Line(init_opts=opts.InitOpts(theme=ThemeType.DARK))
        .add_xaxis(list(data03['date']))
        .add_yaxis("shibor利率", list(data01['on']))
        .set_global_opts(
                        title_opts=opts.TitleOpts(title="shibor利率")
        )
        .render("gauge.html")
)


# 3、近90日全天量能、竞价量能
data03 = pd.merge(pd.read_sql(sql05,conn),pd.read_sql(sql06,conn),how='left',left_on='trade_date',right_on='date')

line03 = (
    Line()
    .add_xaxis(list(data03['trade_date']))
    .add_yaxis("全天量能", list(data03['amount']))
    .add_yaxis("竞价量能", list(data03['money']))
    .set_global_opts(title_opts=opts.TitleOpts(title="近90日量能趋势"))
    .render("line_base.html")
)

# 4、近90日北向资金趋势
data04 = pd.read_sql(sql07,conn)
line04 = (
    Line()
    .add_xaxis(list(data04['trade_date']))
    .add_yaxis("沪股通", list(data04['hgt']))
    .add_yaxis("深股通", list(data04['sgt']))
    .add_yaxis("净流入", list(data04['north_money']))
    .set_global_opts(title_opts=opts.TitleOpts(title="近90日北向资金趋势"))
    .render("line_base.html")
)

# 5、近90日大单资金趋势
data05 = pd.read_sql(sql08,conn)
line04 = (
    Line()
    .add_xaxis(list(data04['trade_date']))
    .add_yaxis("超大单净额", list(data04['elg_amount']))
    .add_yaxis("大单净额", list(data04['lg_amount']))
    .add_yaxis("中单净额", list(data04['md_amount']))
    .add_yaxis("净流入", list(data04['net_mf_amount']))
    .set_global_opts(title_opts=opts.TitleOpts(title="近90日大单资金趋势"))
    .render("line_base.html")
)

# 大单流向
sql08 = '''
            SELECT
                trade_date,                                                -- 交易日期
                SUM(buy_md_amount-sell_md_amount)/1000 AS 'md_amount',     -- 中单净额/亿
                SUM(buy_lg_amount-sell_lg_amount)/1000 AS 'lg_amount',     -- 大单净额/亿
                SUM(buy_elg_amount-sell_elg_amount)/1000 AS 'elg_amount',  -- 超大单净额/亿
                SUM(net_mf_amount)/1000 AS 'net_mf_amount'                 -- 资金净额/亿
            FROM
                stock_moneyflow 
            GROUP BY
                trade_date
'''
                
                
grid = (
    Grid()
        .add(bar, grid_opts=opts.GridOpts(pos_bottom="60%", pos_right='50%'))
        .add(line, grid_opts=opts.GridOpts(pos_top="60%"))
    # .render("grid_vertical.html")
)

grid1 = (
    Grid()
        .add(bar, grid_opts=opts.GridOpts(pos_bottom="60%", pos_right='50%'))
        .add(line, grid_opts=opts.GridOpts(pos_top="60%"))
    # .render("grid_vertical.html")
)

tab = Tab()
tab.add(grid, "bar-example")
# tab.add(line_markpoint(), "line-example")
# tab.add(pie_rosetype(), "pie-example")
# tab.add(grid_mutil_yaxis(), "grid-example")
tab.render("tab_base.html")



from pyecharts import options as opts
from pyecharts.charts import Bar
from pyecharts.faker import Faker


x = Faker.dogs + Faker.animal
xlen = len(x)
y = []
for idx, item in enumerate(x):
    if idx <= xlen / 2:
        y.append(
            opts.BarItem(
                name=item,
                value=(idx + 1) * 10,
                itemstyle_opts=opts.ItemStyleOpts(color="#749f83"),
            )
        )
    else:
        y.append(
            opts.BarItem(
                name=item,
                value=(xlen + 1 - idx) * 10,
                itemstyle_opts=opts.ItemStyleOpts(color="#d48265"),
            )
        )

c = (
    Bar()
    .add_xaxis(x)
    .add_yaxis("series0", y, category_gap=0, color=Faker.rand_color())
    .set_global_opts(title_opts=opts.TitleOpts(title="Bar-直方图（颜色区分）"))
    .render("bar_histogram_color.html")
)