#导入falsk框架相关库
from flask import Flask, url_for, request, render_template, redirect, session, flash, jsonify,json
#导入sql库
from util.mysql_util import MysqlUtil
#导入注册类
from entity.forms import RegisterForm
#导入权限管理的库
from functools import wraps
#导入加密库
from passlib.hash import sha256_crypt
#导入时间库
import time
from datetime import date, timedelta
#导入分页类
from util.page_utils import Pagination
#导入os库
import os
# 导入爬虫启动库
import subprocess
#导入pandas库
from pandas import DataFrame
#导入支付宝沙箱库
from alipay import AliPay
#导入tushare等完成k线图
import tushare as ts
from flask_bootstrap import Bootstrap
from pyecharts import options as opts
from pyecharts.charts import Kline
#导入连接mysql库
from sqlalchemy import create_engine

app = Flask(__name__)
bootstrap = Bootstrap(app)


# 爬虫启动命令(行业新闻)
subprocess.check_output(['scrapy', 'crawl', 'jrjspider'])

##文件上传
UPLOAD_FOLDER = 'static/uploads'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER  # 设置文件上传的目标文件夹
basedir = os.path.abspath(os.path.dirname(__file__))  # 获取当前项目的绝对路径
ALLOWED_EXTENSIONS = set(['txt', 'png', 'jpg', 'xls', 'JPG', 'PNG', 'xlsx', 'gif', 'GIF'])

# 判断文件是否合法
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS


## 提取tushare相关内容，并有相关方法
#接入tushare的token码
pro = ts.pro_api('8c41c210d9b1f9c59ab3e0da0ca2e1197fd5bec6484cf314f73d4e58') #加载接口
# 获取股票列表
stock_list = pro.stock_basic()[['ts_code', 'name']]
# 连接mysql
engine = create_engine('mysql+pymysql://root:[email protected]:3306/financial?charset=utf8', encoding="utf-8")
# 导入mysql
stock_list.to_sql('stock_list', engine, if_exists='replace', index=False)
# 获取最近的股票信息并session保存日期
i = 1
trade_date = date.today().strftime("%Y%m%d") #获取当天日期并格式化
# 获取所有股票信息并导入mysql,设置如果当天获取不到股票可提取前一天，以此类推。
pro.daily_basic(ts_code='', trade_date=trade_date, fields='ts_code,trade_date,close,pe,pb,ps,total_mv') \
    .to_sql('stock_total', engine, if_exists='replace', index=False)
while not MysqlUtil().fetchall("select * from stock_total"):
    trade_date = (date.today() + timedelta(days=-i)).strftime("%Y%m%d")
    stock_total = pro.daily_basic(ts_code='', trade_date=trade_date,
                                      fields='ts_code,trade_date,close,pe,pb,ps,total_mv')
    stock_total.to_sql('stock_total', engine, if_exists='replace', index=False)
    i += 1
#判断股票代码是否正确，并获取股票代码
def check_stock(code):
    n,l = 0,[]
    for i in stock_list.values.tolist():
        if code in i:
            n += 1
            l = i
        else:
            continue
    return n, l

#获取股票数据
def get_stock_data(code, ctime):
    df = pro.daily(ts_code=code)
    mydate = df[:ctime].index.tolist()
    mydata = df[:ctime][['open', 'close', 'low', 'high']].values.tolist()
    return [mydate, mydata]


#绘画K线图
def kline_base(mydate, data) -> Kline:
    c = (
        Kline()
        .add_xaxis(mydate)
        .add_yaxis("kline", data)
        .set_global_opts(
            yaxis_opts=opts.AxisOpts(is_scale=True,
                                    splitarea_opts=opts.SplitAreaOpts(
                    is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
                ),
            ),
            xaxis_opts=opts.AxisOpts(is_scale=True,
                                    axislabel_opts=opts.LabelOpts(rotate=-30)),
            title_opts=opts.TitleOpts(title="股票走势"),
            datazoom_opts=[opts.DataZoomOpts()],
        )
    )
    return c

#初始页面
@app.route("/")
def hello():
    # 提取股票最近交易日期
    trade_date = MysqlUtil().fetchone("select * from stock_total").get("trade_date")
    # 保存获取股票信息的日期
    session['today'] = trade_date
    # 跳转页面
    return render_template('index.html') # 渲染模板

#跳转主页
@app.route("/index")
def index():
    return render_template('index.html')  # 渲染模板

#权限管理:判断是否有用户登录
def is_logged_in(f):
    @wraps(f)
    def wrap(*args,**kwargs):
        #判断用户是否登录
        if 'logged_in' in session:
            return f(*args,**kwargs)
        #如果没有登录，提示无权访问
        else:
            flash("您还未登录")
            return redirect(url_for('index'))
    return wrap

#用户注册
@app.route('/register',methods=['GET','POST'])
def register():
    #实例化表单类
    form = RegisterForm(request.form)
    #如果提交表单，并且字段符合要求
    if request.method == 'POST' and form.validate():
        #获取表单的字段 用户名
        username = form.username.data
        #获取表单的字段 密码，并对密码加密
        password = sha256_crypt.encrypt(str(form.password.data))
        # 根据用户查询该用户信息存不存在
        result = MysqlUtil().fetchone("select * from users where username = '%s'" % (username))
        if result:  # 如果查询到记录，则跳转页面，并传入参数
            return render_template('reg.html', error='用户名已存在',form=form)
        else:
            MysqlUtil().insert("INSERT INTO users(username,password)values('%s','%s')"%(username,password))
            flash("您已经注册成功，请登录",'success')#闪存信息
            # 跳转页面
            return redirect(url_for('login'))#跳转到登录页面

    return render_template('reg.html',form=form)

#用户登录
@app.route('/login',methods=['GET','POST'])
def login():
    if 'logged_in' in session: #如果已经登录，则直接跳转到控制台
        return redirect(url_for('index')) #跳转到控制台

    if request.method == 'POST': #如果提交表单
        #从表单中获取用户名和密码
        username = request.form['username']
        password = request.form['password']

        # 根据用户查询该用户信息
        result = MysqlUtil().fetchone("select * from users where username = '%s'"%(username)) #获取一条记录
        # 判断该用户是否被停用
        if result.get('state') == '停用':
            return render_template('login.html', error='该用户已被停用')
        # 如果有用户信息，判断密码是否正确
        if result :
            password_result = result['password']
            # 密码相等则成功登录，保存相关参数
            if sha256_crypt.verify(password,password_result):
                #将用户名保存在session信息
                session['logged_in'] = True
                session['username'] = username
                session['password'] = password
                #判断是否为管理员
                emark = result['emark']
                if emark == '管理员':
                    return redirect(url_for('terrace'))  # 跳转到管理员平台
                else:
                    #重定向
                    return redirect(url_for('index')) #跳转到主页
            else:
                # 跳转到登录页面，并提示错误
                return render_template('login.html',error='用户名和密码不匹配')
        else:#用户名不存在
            # 跳转到登录页面，并提示错误
            return render_template('login.html',error='用户名不存在')
    # 跳转页面
    return render_template('login.html')

#logout 退出
@app.route('/logout')
@is_logged_in
def logout():
    # 清除session里面的值
    session.pop('logged_in', None)
    session.pop('username', None)
    session.pop('password', None)
    # 跳转页面
    return redirect(url_for('login'))  # 跳转到控制台

#管理员平台-用户管理
@app.route('/terrace',methods=['GET','POST'])
@is_logged_in
def terrace():
    # 查询股票订单信息
    stock_orders = MysqlUtil().fetchall("select * from stock_orders where username='%s'" % (session['username']))
    fund_orders = MysqlUtil().fetchall("select * from fund_orders where username='%s'" % (session['username']))
    # 计算昨日收益和持有收益
    hold_earnings = 0
    old_earnings = 0
    stock_asset = 0
    fund_asset = 0
    # 计算股票有关
    for code in stock_orders:
        # 当日收盘价
        now_close = MysqlUtil().fetchone("select * from stock_total where ts_code='%s'" % (code.get('ts_code'))).get(
            'close')
        # 涨跌幅
        pct_chg = (float(now_close) - float(code.get('bid_close'))) / float(code.get('bid_close')) * 100
        # 更改当日收盘价和涨跌幅
        MysqlUtil().update("update stock_orders set now_close='%s',pct_chg='%s' where id='%s'" % (now_close, pct_chg, code.get('id')))
        # 持有收益
        stock_order = MysqlUtil().fetchone("select * from stock_orders where ts_code='%s'" % (code.get('ts_code')))
        hold_earnings += (stock_order.get('now_close') - stock_order.get('bid_close')) * stock_order.get('stock_count')
        # 昨日收益, 若当天交易则不计算昨日收益
        if session['today'] != stock_order.get('trade_date').strftime("%Y%m%d"):
            df = pro.daily(ts_code=code.get('ts_code'), trade_date=session['today'])
            old_earnings += (df['close'].values - df['pre_close'].values)[0] * stock_order.get('stock_count')
        orders = MysqlUtil().fetchone("select * from stock_orders where ts_code='%s'" % (code.get('ts_code')))
        hold_earnings += (orders.get('now_close') - orders.get('bid_close')) * orders.get('stock_count')
        # 昨日收益, 若当天交易则不计算昨日收益
        if session['today'] != orders.get('trade_date').strftime("%Y%m%d"):
            df = pro.daily(ts_code=code.get('ts_code'), trade_date=session['today'])
            old_earnings += (df['close'].values - df['pre_close'].values)[0] * orders.get('stock_count')
        # 股票资产
        stock_asset += stock_order.get('now_close') * stock_order.get('stock_count')
    # 计算基金有关
    for code in fund_orders:
        # 最新净值和日增长率
        fund = MysqlUtil().fetchone("select * from fund_total where fund_name='%s'" % (code.get('fund_name')))
        now_npv = fund.get('fund_npv')
        day_chg = fund.get('fund_chg')
        # 涨跌幅
        pct_chg = (float(now_npv) - float(code.get('bid_npv'))) / float(code.get('bid_npv')) * 100
        # 更改最新净值、日增长率和涨跌幅
        MysqlUtil().update(
            "update fund_orders set now_npv='%s',day_chg='%s',pct_chg='%s' where id='%s'" % (
                now_npv, day_chg, pct_chg, code.get('id')))
        # 持有收益
        fund_order = MysqlUtil().fetchone("select * from fund_orders where fund_code='%s'" % (code.get('fund_code')))
        hold_earnings += (fund_order.get('now_npv') - fund_order.get('bid_npv')) * fund_order.get('fund_count')
        # 昨日收益, 若买入净值和最新净值相同则不计算昨日收益
        if fund_order.get('now_npv') != fund_order.get('bid_npv'):
            day_chg = float(fund_order.get('day_chg').replace('%', '')) / 100
            old_earnings += (fund_order.get('now_npv') - fund_order.get('now_npv') / (1 + day_chg)) * fund_order.get(
                'fund_count')
        # 股票资产
        fund_asset += fund_order.get('now_npv') * fund_order.get('fund_count')
    # 更改持有收益、昨日收益、股票资产、基金资产
    MysqlUtil().update(
        "update users set hold_earnings='%s',old_earnings='%s',stock_asset='%s',fund_asset='%s' where username='%s'" % (
            hold_earnings, old_earnings, stock_asset, fund_asset, session['username']))

    #模糊查询指定用户
    if request.method == 'POST': #如果提交表单
        #从表单中获取用户名和密码
        username = request.form['username']
        # 执行sql语句
        users = MysqlUtil().fetchall("select * from users where emark='用户' and username like '%%%s%%'"%(username))
    #查询所有用户
    else:
        users = MysqlUtil().fetchall('select * from users where emark="用户"')
    #判断是否查找成功
    if users:  # result存在,遍历数据
        pager_obj = Pagination(request.args.get("page", 1), len(users), request.path, request.args, per_page_count=10)
        index_list = users[pager_obj.start:pager_obj.end]
        html = pager_obj.page_html()
        return render_template('admin/terrace.html', index_list=index_list,html=html)  # 渲染模板
    else:  # 如果不存在，提示“无用户”
        return render_template('admin/terrace.html', msg="无用户")  # 渲染模板
    return render_template('admin/terrace.html')

#冻结和解封用户
@app.route('/frost/<string:id>')
@is_logged_in
def frost(id):
    # 查询该用户的信息
    user = MysqlUtil().fetchone("select * from users where id='%s'" % (id))
    # 若状态为"正常",则停用；若"停用"，则正常
    if user.get('state') == '正常':
        MysqlUtil().update("update users set state='停用' where id='%s'" % (id))
    elif user.get('state') == '停用':
        MysqlUtil().update("update users set state='正常' where id='%s'" % (id))
    #跳转页面
    return redirect(url_for('terrace')) #跳转到管理员平台

#股票推荐管理
@app.route('/stockAdmin',methods=['GET','POST'])
@is_logged_in
def stockAdmin():
    stock_admin =  MysqlUtil().fetchone("select * from stock_admin")
    return render_template('admin/stockAdmin.html',stock_admin=stock_admin)

#股票管理
@app.route('/stock_admin',methods=['GET','POST'])
@is_logged_in
def stock_admin():
    # 从表单中获取用户名和密码
    total_mv = request.args.get("total_mv","")
    pe = request.args.get("pe","")
    pb = request.args.get("pb","")
    ps = request.args.get("ps","")
    if not MysqlUtil().fetchone("select * from stock_admin where emark = '股票'"):
        MysqlUtil().insert("insert into stock_admin(total_mv,pe,pb,ps) values ('%s','%s','%s','%s')" % (total_mv, pe, pb, ps))
    else:
        MysqlUtil().update("update stock_admin set total_mv='%s',pe='%s',pb='%s',ps='%s' where emark = '股票'" % (total_mv, pe, pb, ps))
        flash("股票推荐管理修改成功", 'success')  # 闪存信息
    return jsonify({'result':'true'})

#基金推荐管理
@app.route('/fundAdmin',methods=['GET','POST'])
@is_logged_in
def fundAdmin():
    fund_admin =  MysqlUtil().fetchone("select * from fund_admin")
    return render_template('admin/fundAdmin.html',fund_admin=fund_admin)

#基金管理
@app.route('/fund_admin',methods=['GET','POST'])
@is_logged_in
def fund_admin():
    # 从表单中获取用户名和密码
    month = request.args.get("month","")
    month6 = request.args.get("month6","")
    year = request.args.get("year","")
    year3 = request.args.get("year3","")
    total = request.args.get("total", "")
    if not MysqlUtil().fetchone("select * from fund_admin where emark = '基金'"):
        MysqlUtil().insert("insert into fund_admin(month,month6,year,year3,total) values ('%s','%s','%s','%s','%s')" % (month, month6, year, year3,total))
    else:
        MysqlUtil().update("update fund_admin set month='%s',month6='%s',year='%s',year3='%s',total='%s' where emark = '基金'" % (month, month6, year, year3,total))
        flash("基金推荐管理修改成功", 'success')  # 闪存信息
    return jsonify({'result':'true'})

#平台收入管理
@app.route('/income')
@is_logged_in
def income():
    admin = MysqlUtil().fetchone("select * from users where username = 'admin' and emark ='管理员'")
    return render_template('admin/income.html',admin=admin)

#个人中心
@app.route('/personal')
@is_logged_in
def personal():
    ## 更改股票订单状态
    # 查询股票订单信息
    stock_orders = MysqlUtil().fetchall("select * from stock_orders where username='%s'" % (session['username']))
    # 计算当日收盘价、涨跌幅并进行mysql更改
    for code in stock_orders:
        now_close = MysqlUtil().fetchone("select * from stock_total where ts_code='%s'" % (code.get('ts_code'))).get(
            'close')
        pct_chg = (float(now_close) - float(code.get('bid_close'))) / float(code.get('bid_close')) * 100
        MysqlUtil().update(
            "update stock_orders set now_close='%s',pct_chg='%s' where id='%s'" % (now_close, pct_chg, code.get('id')))

    ## 更改基金订单状态
    # 查询基金订单信息
    fund_orders = MysqlUtil().fetchall("select * from fund_orders where username='%s'" % (session['username']))
    # 计算最新净值、日增长率、涨跌幅并进行mysql更改
    for order in fund_orders:
        fund = MysqlUtil().fetchone("select * from fund_total where fund_name='%s'" % (order.get('fund_name')))
        now_npv = fund.get('fund_npv')
        day_chg = fund.get('fund_chg')
        pct_chg = (float(now_npv) - float(order.get('bid_npv'))) / float(order.get('bid_npv')) * 100
        MysqlUtil().update(
            "update fund_orders set now_npv='%s',day_chg='%s',pct_chg='%s' where id='%s'" % (
            now_npv, day_chg, pct_chg, order.get('id')))
    # 查询用户信息
    user = MysqlUtil().fetchone("select * from users where username = '%s'" % (session['username']))
    # 若为管理员则跳转"管理员平台"
    if user.get('emark') == '管理员':
        return redirect(url_for('terrace'))
    else:
        return render_template('user/personal.html',user=user)

#修改个人资料
@app.route('/edit_personal')
@is_logged_in
def edit_personl():
    # 获取修改框数据
    name = request.args.get("name","")
    sex = request.args.get("sex", "")
    phone = request.args.get("phone", "")
    email = request.args.get("email", "")
    home = request.args.get("home", "")
    # 更改个人资料
    MysqlUtil().update("update users set name='%s',sex='%s',phone='%s',email='%s',home='%s' where username='%s'" % (
        name, sex, phone, email,home, session['username']))
    #ajax返回数据
    return jsonify({'result':'true'})

#更改头像
@app.route('/upload', methods=['POST'], strict_slashes=False)
def api_upload():
    file_dir = os.path.join(basedir, app.config['UPLOAD_FOLDER'])  # 拼接成合法文件夹地址
    if not os.path.exists(file_dir):
        os.makedirs(file_dir)  # 文件夹不存在就创建
    f = request.files['myfile']  # 从表单的file字段获取文件，myfile为该表单的name值
    if f and allowed_file(f.filename):  # 判断是否是允许上传的文件类型
        fname=f.filename
        ext = fname.rsplit('.', 1)[1]  # 获取文件后缀
        unix_time = int(time.time())
        new_filename = str(unix_time)+'.'+ext   # 修改文件名
        #查询用户信息
        user = MysqlUtil().fetchone("select * from users where username='%s'" % ( session['username']))
        if user.get("photo"):
             os.remove(os.path.join(file_dir,user.get("photo")))
        f.save(os.path.join(file_dir, new_filename))  #保存文件到upload目录
        #更改头像
        MysqlUtil().update("update users set photo='%s' where username='%s'" % (new_filename,session['username']))
        flash("上传成功")
        return redirect(url_for('personal'))
    else:
        return redirect(url_for('personal'))

#钱包
@app.route('/purse',methods=['GET','POST'])
@is_logged_in
def purse():
    # 查询股票订单信息
    stock_orders = MysqlUtil().fetchall("select * from stock_orders where username='%s'" % (session['username']))
    fund_orders = MysqlUtil().fetchall("select * from fund_orders where username='%s'" % (session['username']))
    # 计算昨日收益和持有收益
    hold_earnings = 0
    old_earnings = 0
    stock_asset = 0
    fund_asset = 0
    #计算股票有关
    for code in stock_orders:
        #持有收益
        stock_order = MysqlUtil().fetchone("select * from stock_orders where ts_code='%s'" % (code.get('ts_code')))
        hold_earnings += (stock_order.get('now_close')-stock_order.get('bid_close'))*stock_order.get('stock_count')
        #昨日收益, 若当天交易则不计算昨日收益
        if session['today'] != stock_order.get('trade_date').strftime("%Y%m%d"):
            df = pro.daily(ts_code=code.get('ts_code'),trade_date=session['today'])
            old_earnings += (df['close'].values-df['pre_close'].values)[0]*stock_order.get('stock_count')

        orders = MysqlUtil().fetchone("select * from stock_orders where ts_code='%s'" % (code.get('ts_code')))
        hold_earnings += (orders.get('now_close')-orders.get('bid_close'))*orders.get('stock_count')
        #昨日收益, 若当天交易则不计算昨日收益
        if session['today'] != orders.get('trade_date').strftime("%Y%m%d"):
            df = pro.daily(ts_code=code.get('ts_code'),trade_date=session['today'])
            old_earnings += (df['close'].values-df['pre_close'].values)[0]*orders.get('stock_count')
        #股票资产
        stock_asset += stock_order.get('now_close')*stock_order.get('stock_count')
    #计算基金有关
    for code in fund_orders:
        #持有收益
        fund_order = MysqlUtil().fetchone("select * from fund_orders where fund_code='%s'" % (code.get('fund_code')))
        hold_earnings += (fund_order.get('now_npv')-fund_order.get('bid_npv'))*fund_order.get('fund_count')
        #昨日收益, 若买入净值和最新净值相同则不计算昨日收益
        if fund_order.get('now_npv') != fund_order.get('bid_npv'):
            day_chg = float(fund_order.get('day_chg').replace('%', '')) / 100
            old_earnings += (fund_order.get('now_npv') - fund_order.get('now_npv') / (1 + day_chg)) * fund_order.get('fund_count')
        #股票资产
        fund_asset += fund_order.get('now_npv')*fund_order.get('fund_count')
    # 更改持有收益、昨日收益、股票资产、基金资产
    MysqlUtil().update("update users set hold_earnings='%s',old_earnings='%s',stock_asset='%s',fund_asset='%s' where username='%s'" % (
        hold_earnings,old_earnings,stock_asset,fund_asset,session['username']))
    # 查询余额、股票资产、基金资产、昨日收益、总收益
    user = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username']))
    #启用支付宝沙箱
    if request.method == 'POST':
        # 个人私钥
        app_private_key_string = """-----BEGIN RSA PRIVATE KEY-----
                MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCK67eiQr3yggxqeyRv6+i1PdMkD6BG+USfs/Aa4X9joCv5sqjVPm6+wWeyclouvxUV5elS4h+YJ1fZIp+M+hqbQuSHyIjE38ZpyIbzFly1UESuJdo+h4YctSbqXp54mO13d4MkMZ7n+AymofjbfZWI4aQ+CjJmvvIZ0EDrEM6mGvbav2YncX9RiSBRdeqViNJzy5z6/KziPdM7hrUqAW5xsa+OQdcBofwOkCjO6QmJvOaznRvHKgm52z7yH7UgXXrScA5HATLO22USzYdKlpV0/hD6V6GR3NgLEW84c7285LkqB5arCYG4FR/Yym0f2DPjbMitgcEh2kyhkCxl2iBHAgMBAAECggEAVqeSdHSNZ6gXdpQ5tlbBYcye1++nWp5CYBwtuZbPj6VpyOfYyKuZCQQ7WuRZvK8UyAQR9tF2Q8LG2Xrva1DXJ+IiC1Dbf3LWoUWkaB2a1r6rGLYj3L1MuruE+szqrO83sT2rX/hT1v6zZX3DbaZ4kFmvycMyjUSuX8u3sulY/eME6lsSRGwYTF0nKOLbElPKwGvxX60a4HnZJifQtnbP9Anu1LjpJG3fCcNlSqHAU7zeYnNueS1gtpPTz8634D+ng24qsiu/B0Ouj/dAzhye/XLeq2YqbM99aEDAoO3+SZEs6mkOJu2i7ja0ftEer36X2aXpFPy7P9wRSiegXS7kAQKBgQDhlsVUqxvYK453stJ5jIZYUedyQXLSHFF15zlyakbu0yLzl98GA8y+nvO4AyWXYf59ykozcCAh/MznY3du2Mt3IreTUrkJNg8IFjE0x1r/jfsa4Q9mZktaq+bt0ywADxKMySRtrcakQSTGY6uzjvKW1z0fGAALqHoXE9/qcsU6ZwKBgQCdpffimOaOcn6YJldEnbULSiDGJ90NFiaEmyJVfw4fKcSFduN7I/avApU7gRLVHT2lIh+aAhlF/3UT5wOa2ei8OkF1pEkOhnIM6FWt9Svfe7C5l2Xz6ZPUl/0Pqbj3i3UNC3u0XJLcFn2CGjUVirOrhj0SUgnfk+HBSZmMKhf/IQKBgQCst6Pz9GIIOykEClzNhULi4iqjNurzZY0VCWjZafdPKUZsXM37q/MZy1dsfrRMWFHh+3yeL23at9QmIOizbLczSthkqoRTQhl2Jo+cCjpoyUsFRGVnEFEHZfAUDId2sBOSyPnkI5Wf8uR2JJ+0hXDE/pykbtqyElcPzj61IRRSZwKBgADT1jFCTDODNlbmHJ8nlMOyZ+miW+6tpxYbGlsIh7JyLa3lqdwDnE/e25msW8Tymlg7jZ7pU+iiJqMsG0C81BPo2l8btaZ13jNjJM/88XYC2DKhMM/O8g+znIhwg6AJA1xGKygPNFYjaBU+vfiW+cp0se1esN6TkCPLlxq9PdMBAoGAVbI7D2jaxWE8fBwX+YReRz2VawXhE6dBzmQga3B3DAzwN/WdxWbuRlfp3Pv+ZXhOaPZwLiiNe9NZILKAuOPECdhH4nCePM7FNngT3gzOx7oozt6x6SsTf1yt2W7hI70InF50Fk0WleW8qKD/lS5y6mOxyDujWUHjBR2u4RVMUr4=
                -----END RSA PRIVATE KEY-----"""
        # 支付宝公钥
        alipay_public_key_string = """-----BEGIN PUBLIC KEY-----
                MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAjGP8hLFobNws0MzzYCv5VAYZbFDfIq6i5Ju1HxOjCB9XsGXHIN1r4msguLZhlrgGezDYpIMup2Go6zkD4k4HCHYL6tySKEcev1e6tcGwwcUcoqJ8Ogxn4uF8ZvkEjOJT3bMy0nfUtS4H+Pq9yTV8akAqEJD3eCGzIwevwA4xlbpiE5IgfSzul31XKB7Ok2jecopkgm9wZI6dnAvhJITcCxobcRZgy0pVMWv7102bDOyjmJC3RUJ99PSLp7NkWxb6wpdC2iq+TyqtGcJQYNzuxrEHQSw9Yc6QkGdFjO0hppGzr45T3McOa+ooWTYBBdHBn4qZBwWhzWnRI2fG7sD5awIDAQAB
                -----END PUBLIC KEY-----"""
        alipay = AliPay(
            appid='2016110300788871',  # 第3步中的APPID
            app_notify_url=None,  # 默认回调url
            app_private_key_string=app_private_key_string,
            alipay_public_key_string=alipay_public_key_string,
            sign_type="RSA2",
            debug=False
        )

        # 电脑网站支付
        money = request.form['money'] #从表单获取充值的数量
        if money=='' or money==[]:
            flash("充值数目不能为空")
            return render_template('user/purse.html', user=user)
        unix_time = int(time.time())
        order_string = alipay.api_alipay_trade_page_pay(
            out_trade_no=str(unix_time),  # 你自己生成的订单编号, 字符串格式
            total_amount=money,  # 订单总金额, 字符串格式
            subject="充值",  # 订单主题,可随便写
            return_url="http://127.0.0.1:5000/purse",  # 支付完成后要跳转的页面, 完整的url地址,包括域名
            notify_url=None  # 可选, 不填则使用默认notify url
        )
        url = "https://openapi.alipaydev.com/gateway.do?" + order_string
        # 修改余额
        MysqlUtil().update("update users set money='%d'where username='%s'" % ((user.get("money")+int(money)), session['username']))
        return redirect(url)
    return render_template('user/purse.html',user=user)


# 股票订单
@app.route('/stock_order',methods=['GET','POST'])
@is_logged_in
def stock_order():
    #查询股票订单信息
    stock_orders = MysqlUtil().fetchall("select * from stock_orders where username='%s'" % (session['username']))
    # 计算当日收盘价、涨跌幅并进行mysql更改
    for code in stock_orders:
         now_close = MysqlUtil().fetchone("select * from stock_total where ts_code='%s'" % (code.get('ts_code'))).get('close')
         pct_chg = (float(now_close)-float(code.get('bid_close')))/float(code.get('bid_close'))*100
         MysqlUtil().update("update stock_orders set now_close='%s',pct_chg='%s' where id='%s'"%(now_close,pct_chg,code.get('id')))
    #修改后再次查询，并加入模糊查询
    if request.method == 'POST':  # 如果提交表单
        # 从表单中获取用户名和密码
        stock_name = request.form['stock_name']
        stock_orders = MysqlUtil().fetchall("select * from stock_orders where username='%s' and stock_name like '%%%s%%'" % (session['username'],stock_name))
    else:
        stock_orders = MysqlUtil().fetchall("select * from stock_orders where username='%s'" % (session['username']))
    #分页操作
    pager_obj = Pagination(request.args.get("page", 1), len(stock_orders), request.path, request.args, per_page_count=10)
    index_list = stock_orders[pager_obj.start:pager_obj.end]
    html = pager_obj.page_html()
    return render_template('user/stock_order.html',index_list=index_list,html=html)

# 股票详细
@app.route('/stock_minute/<string:stock_name>&&<int:stock_count>',methods=['GET','POST'])
@is_logged_in
def stock_minute(stock_name,stock_count):
    # 判断股票代码是否正确
    status, stock_code = check_stock(stock_name)
    if status == 0:
        return 'error stock code or name'
    # 获取该股票的行情
    df = pro.daily(ts_code=stock_code[0])[['trade_date','open','high','low','close','change','pct_chg']].values.tolist()
    # 分页操作
    pager_obj = Pagination(request.args.get("page", 1), len(df), request.path, request.args, per_page_count=10)
    index_list = df[pager_obj.start:pager_obj.end]
    html = pager_obj.page_html()
    # 画k线图
    if request.method == 'POST' and pager_obj.start==0:
        mydate, mydata = get_stock_data(stock_code[0], int(90))
        c = kline_base(mydate, mydata)
        return c.dump_options()
    return render_template('user/stock_sell.html',stock_name=stock_name,stock_count=stock_count,index_list=index_list,html=html)

# 卖出股票
@app.route('/stock_sell/<string:stock_name>&&<int:stock_count>',methods=['GET','POST'])
@is_logged_in
def sell(stock_name, stock_count):
    # 判断股票代码是否正确
    status, stock_code = check_stock(stock_name)
    # 查询余额
    user = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username']))
    money = user.get('money')
    stock_asset = user.get('stock_asset')
    # 获取卖出框数据
    count = int(request.args.get("count", ""))
    # 查询收盘价、买入价
    orders = MysqlUtil().fetchone("select * from stock_orders where ts_code='%s'" % (stock_code[0]))
    bid_close = orders.get('bid_close')
    now_close = orders.get('now_close')
    # 判断卖出股票数量是否超过持有数量
    if count <= stock_count:
        #若全部卖出，则删除该订单
        if stock_count == count:
            MysqlUtil().delete("delete from stock_orders where username='%s' and stock_name='%s' and bid_close='%s'"%(
                session['username'],stock_name,bid_close))
        #若部分卖出，则更改订单数量
        else:
            MysqlUtil().update("update stock_orders set stock_count ='%s' where username='%s' and stock_name='%s' and bid_close='%s'"%(
                (stock_count-count),session['username'],stock_name,bid_close))
        # 增加钱包余额，减少股票资产
        MysqlUtil().update("update users set money='%s' ,stock_asset='%s' where username='%s'" % (
            (money + count * now_close-count * now_close*0.01), (stock_asset - count * now_close), session['username']))
        #计算实际收益，并更改
        reality_earnings = MysqlUtil().fetchone("select * from users where username='%s'"%(session['username'])).get('reality_earnings')
        reality_earnings += (now_close - bid_close) * count-count * now_close*0.01
        MysqlUtil().update("update users set reality_earnings='%s' where username='%s'" % (reality_earnings, session['username']))
        #查询管理平台收入
        admin_money = MysqlUtil().fetchone("select * from users where username='admin'").get('money')
        MysqlUtil().update("update users set money='%s' where username='admin'" % (admin_money + count * now_close*0.01))
        # 卖出成功
        return jsonify({'success':True}), 200, {'ContentType':'application/json'}
    else:
        # 卖出失败
        return jsonify({'error':True}),10001, {'ContentType':'application/json'}


#基金订单
@app.route('/fund_order',methods=['GET','POST'])
@is_logged_in
def fund_order():
    #更改完重新查询
    if request.method == 'POST':  # 如果提交表单
        # 从表单中获取用户名和密码
        fund_name = request.form['fund_name']
        fund_orders = MysqlUtil().fetchall(
            "select * from fund_orders where username='%s' and fund_name like '%%%s%%'" % (session['username'], fund_name))
    else:
        fund_orders = MysqlUtil().fetchall("select * from fund_orders where username='%s'" % (session['username']))
    # 分页操作
    pager_obj = Pagination(request.args.get("page", 1), len(fund_orders), request.path, request.args,
                           per_page_count=10)
    index_list = fund_orders[pager_obj.start:pager_obj.end]
    html = pager_obj.page_html()
    return render_template('user/fund_order.html',index_list=index_list,html=html)

# 基金详细
@app.route('/fund_minute/<int:id>&&<float:fund_count>',methods=['GET','POST'])
@is_logged_in
def fund_minute(id,fund_count):
    fund = MysqlUtil().fetchone("select * from fund_orders where id='%s'" % id)
    # 查询爬取的网页
    url = 'https://fundf10.eastmoney.com/jjjz_%s.html' % fund.get('fund_code')
    # 查询余额
    money = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username'])).get('money')
    return render_template('user/fund_sell.html', fund=fund, money=money, url=url,fund_count=fund_count)

# 卖出基金
@app.route('/fund_sell/<int:id>&&<float:fund_count>',methods=['GET','POST'])
@is_logged_in
def fund_sell(id, fund_count):
    # 查询余额、股票资产
    user = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username']))
    myMoney = user.get('money')
    fund_asset = user.get('fund_asset')
    # 获取卖出框数据
    count = float(request.args.get("count", ""))
    # 查询基金净值以及交易日期
    fund = MysqlUtil().fetchone("select * from fund_orders where id='%s'" % id)
    now_npv = fund.get('now_npv')
    if count <= fund_count:
        # 若全部卖出，则删除该订单
        if count == fund_count:
            MysqlUtil().delete("delete from fund_orders where id='%s'" % id)
        # 若部分卖出，则更改订单数量
        else:
            MysqlUtil().update("update fund_orders set fund_count ='%s' where id='%s'" % (fund_count - count,id))
        # 增加钱包余额，减少股票资产（内含手续费）
        MysqlUtil().update("update users set money='%s' ,fund_asset='%s' where username='%s'" % (
            (myMoney + now_npv*count - now_npv*count*0.01), (fund_asset - now_npv*count), session['username']))
        # 计算实际收益，并更改
        reality_earnings = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username'])).get('reality_earnings')
        reality_earnings += (now_npv - fund.get('bid_npv')) * count - now_npv*count*0.01
        MysqlUtil().update("update users set reality_earnings='%s' where username='%s'" % (reality_earnings, session['username']))
        #计算管理平台收入
        # 查询管理平台收入
        admin_money = MysqlUtil().fetchone("select * from users where username='admin'").get('money')
        MysqlUtil().update("update users set money='%s' where username='admin'" % (admin_money + now_npv * count * 0.01))
        # 卖出成功
        return jsonify({'success': True}), 200, {'ContentType': 'application/json'}
    else:
        # 卖出失败
        return jsonify({'error': True}), 10001, {'ContentType': 'application/json'}

#修改密码
@app.route('/uppass',methods=['GET','POST'])
@is_logged_in
def uppass():
    #提交表单
    if request.method == 'POST':
        # 从表单中获取用户名和密码
        oldPwd = request.form['oldPwd']
        newPwd = request.form['newPwd']
        if session["password"] == oldPwd:
            # 更改密码
            MysqlUtil().update("update users set password= '%s' where username = '%s'"%(sha256_crypt.encrypt(str(newPwd)),session['username']))
            # 清除session里面的值
            session.clear()
            flash("修改密码成功，请重新登录！","ok")
            # 跳转页面
            return redirect(url_for('login'))
        else:
            return render_template('user/uppass.html', msg="旧密码错误")  # 渲染模板
    return render_template('user/uppass.html')

#平台简介
@app.route('/intro')
def intro():
    return render_template('classify/intro.html')

#行业新闻
@app.route('/news')
def news():
    #主体新闻
    news = MysqlUtil().fetchall("select * from news")
    return render_template('classify/news.html',news=news)

#股票推荐
@app.route('/stock',methods=['GET','POST'])
def stock():
    # 因子选股
    stocks = MysqlUtil().fetchall("select * from stock_total")
    stock = DataFrame(stocks).describe()
    # 股票推荐管理
    stock_admin = MysqlUtil().fetchone("select * from stock_admin")
    #查询所有符合推荐管理的股票信息以及股票名称
    if request.method == 'POST' or session['stock_name']:  # 如果提交表单
        # 从表单中获取用户名和密码
        stock_name = request.form['stock_name']
        session['stock_name'] = stock_name
        #模糊查询
        stocks = MysqlUtil().fetchall("select t.*,l.name from stock_total t,stock_list l where t.ts_code = l.ts_code "
                                      "and name like '%%%s%%'"% stock_name)
    else:
        stocks = MysqlUtil().fetchall("select t.*,l.name from stock_total t,stock_list l where t.ts_code = l.ts_code "
                                  "and pe>'%s' and pb<'%s' and ps<'%s' and total_mv>'%s'"
                                  %(stock['pe'].loc[stock_admin.get('pe')], stock['pb'].loc[stock_admin.get('pb')], stock['ps'].loc[stock_admin.get('ps')],
                                         stock['total_mv'].loc[stock_admin.get('total_mv')]))
    #分页操作
    pager_obj = Pagination(request.args.get("page", 1), len(stocks), request.path, request.args,per_page_count=10)
    index_list = stocks[pager_obj.start:pager_obj.end]
    html = pager_obj.page_html()
    # 跳转页面并传入参数
    return render_template('classify/stock.html',index_list=index_list,html=html)

# 股票详细
@app.route('/stock_min/<string:stock_name>',methods=['GET','POST'])
@is_logged_in
def stock_min(stock_name):
    # 判断股票是否正确
    status, stock_code = check_stock(stock_name)
    if status == 0:
        return 'error stock code or name'
    # 查询该股票行情
    df = pro.daily(ts_code=stock_code[0])[['trade_date','open','high','low','close','change','pct_chg']].values.tolist()
    # 分页操作
    pager_obj = Pagination(request.args.get("page", 1), len(df), request.path, request.args, per_page_count=10)
    index_list = df[pager_obj.start:pager_obj.end]
    html = pager_obj.page_html()
    # 画k线图
    if request.method == 'POST' and pager_obj.start==0:
        mydate, mydata = get_stock_data(stock_code[0], int(90))
        c = kline_base(mydate, mydata)
        return c.dump_options()
    # 查询余额
    money = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username'])).get('money')
    # 跳转页面并传入参数
    return render_template('classify/stock_buy.html',stock_name=stock_name,index_list=index_list,html=html,money=money)

# 购买股票
@app.route('/stock_buy/<string:stock_name>',methods=['GET','POST'])
@is_logged_in
def stock_buy(stock_name):
    status, stock_code = check_stock(stock_name)
    # 查询余额、股票资产
    user = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username']))
    money = user.get('money')
    stock_asset = user.get('stock_asset')
    # 获取购买框数据
    count = request.args.get("count", "")
    # 查询股价以及交易日期
    stock_total = MysqlUtil().fetchone("select * from stock_total where ts_code='%s'" % (stock_code[0]))
    close = stock_total.get('close')
    trade_date = stock_total.get('trade_date')
    if int(count)*close < money:
        # 若本天有购买此股票，则增加数量
        orders =  MysqlUtil().fetchone("select * from stock_orders where username='%s' and stock_name='%s' and trade_date='%s'" % (
            session['username'], stock_name, trade_date))
        if orders:
            MysqlUtil().update("update stock_orders set count = '%s' where username='%s' and stock_name='%s' and trade_date='%s'" % (
                (orders.get('stock_count')+int(count)),session['username'], stock_name, trade_date))
        else:
            MysqlUtil().insert("insert into stock_orders(username,stock_name,ts_code,bid_close,trade_date,stock_count) values ('%s','%s','%s','%s','%s','%s')" % (
                session['username'], stock_name,stock_code[0],close, trade_date, count))
        #减少钱包余额，增加股票资产
        MysqlUtil().update("update users set money='%s' ,stock_asset='%s' where username='%s'" % (
            (money-int(count)*close),(stock_asset+int(count)*close),session['username']))
        # 购买成功
        return jsonify({'success':True}), 200, {'ContentType':'application/json'}
    else:
        # 购买失败
        return jsonify({'error':True}),10001, {'ContentType':'application/json'}

#基金推荐
@app.route('/fund')
def fund():
    return render_template('classify/fund.html')

#股票型基金推荐
@app.route('/stock_fund',methods=['GET','POST'])
def stock_fund():
    # 因子选股
    funds = MysqlUtil().fetchall("select * from fund_total where fund_remark='%s'"%'股票型基金')
    fund = DataFrame(funds).describe()
    # 基金推荐管理
    fund_admin = MysqlUtil().fetchone("select * from fund_admin")
    # 查询基金信息以及基金名称
    if request.method == 'POST':  # 如果提交表单
        # 从表单中获取用户名和密码
        fund_name = request.form['fund_name']
        # 模糊查询
        funds = MysqlUtil().fetchall("select * from fund_total where fund_remark='股票型基金' and fund_name like '%%%s%%'" %fund_name)
    else:
        funds = MysqlUtil().fetchall("select * from fund_total where fund_remark='股票型基金' and fund_month>'%s' and fund_6month>'%s' and fund_year>'%s' and fund_3year>'%s' and fund_total>'%s'"%(
            fund['fund_month'].loc[fund_admin.get('month')],fund['fund_6month'].loc[fund_admin.get('month6')],fund['fund_year'].loc[fund_admin.get('year')],
            fund['fund_3year'].loc[fund_admin.get('year3')],fund['fund_total'].loc[fund_admin.get('total')]))
    # 分页操作
    pager_obj = Pagination(request.args.get("page", 1), len(funds), request.path, request.args, per_page_count=10)
    index_list = funds[pager_obj.start:pager_obj.end]
    html = pager_obj.page_html()
    return render_template('classify/stock_fund.html',index_list=index_list,html=html)

#混合型基金推荐
@app.route('/mixed_fund',methods=['GET','POST'])
def mixed_fund():
    # 因子选股
    funds = MysqlUtil().fetchall("select * from fund_total where fund_remark='混合型基金'")
    fund = DataFrame(funds).describe()
    # 基金推荐管理
    fund_admin = MysqlUtil().fetchone("select * from fund_admin")
    # 查询基金信息以及基金名称
    if request.method == 'POST':  # 如果提交表单
        # 从表单中获取用户名和密码
        fund_name = request.form['fund_name']
        # 模糊查询
        funds = MysqlUtil().fetchall(
            "select * from fund_total where fund_remark='混合型基金' and fund_name like '%%%s%%'" %fund_name)
    else:
        funds = MysqlUtil().fetchall(
            "select * from fund_total where fund_remark='混合型基金' and fund_month>'%s' and fund_6month>'%s' and fund_year>'%s' and fund_3year>'%s' and fund_total>'%s'" % (
                fund['fund_month'].loc[fund_admin.get('month')],fund['fund_6month'].loc[fund_admin.get('month6')], fund['fund_year'].loc[fund_admin.get('year')],
                fund['fund_3year'].loc[fund_admin.get('year3')], fund['fund_total'].loc[fund_admin.get('total')]))
    # 分页操作
    pager_obj = Pagination(request.args.get("page", 1), len(funds), request.path, request.args, per_page_count=10)
    index_list = funds[pager_obj.start:pager_obj.end]
    html = pager_obj.page_html()
    return render_template('classify/mixed_fund.html',index_list=index_list,html=html)

#债券型基金推荐
@app.route('/bond_fund',methods=['GET','POST'])
def bond_fund():
    # 因子选股
    funds = MysqlUtil().fetchall("select * from fund_total where fund_remark='债券型基金'")
    fund = DataFrame(funds).describe()
    # 基金推荐管理
    fund_admin = MysqlUtil().fetchone("select * from fund_admin")
    # 查询基金信息以及基金名称
    if request.method == 'POST':  # 如果提交表单
        # 从表单中获取用户名和密码
        fund_name = request.form['fund_name']
        # 模糊查询
        funds = MysqlUtil().fetchall(
            "select * from fund_total where fund_remark='债券型基金' and fund_name like '%%%s%%'" % fund_name)
    else:
        funds = MysqlUtil().fetchall(
            "select * from fund_total where fund_remark='债券型基金' and fund_month>'%s' and fund_6month>'%s' and fund_year>'%s' and fund_3year>'%s' and fund_total>'%s'" % (
                fund['fund_month'].loc[fund_admin.get('month')],fund['fund_6month'].loc[fund_admin.get('month6')], fund['fund_year'].loc[fund_admin.get('year')],
                fund['fund_3year'].loc[fund_admin.get('year3')], fund['fund_total'].loc[fund_admin.get('total')]))
    # 分页操作
    pager_obj = Pagination(request.args.get("page", 1), len(funds), request.path, request.args, per_page_count=10)
    index_list = funds[pager_obj.start:pager_obj.end]
    html = pager_obj.page_html()
    return render_template('classify/bond_fund.html',index_list=index_list,html=html)

#基金详细
@app.route('/fund_min/<string:fund_name>&&<string:fund_code>')
@is_logged_in
def fund_min(fund_name,fund_code):
    #查询爬取的网页
    url = 'https://fundf10.eastmoney.com/jjjz_%s.html' % fund_code
    #查询单位净值
    fund_npv = MysqlUtil().fetchone("select * from fund_total where fund_name='%s'"%fund_name).get('fund_npv')
    # 查询余额
    money = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username'])).get('money')
    return render_template('classify/fund_buy.html', fund_name=fund_name,fund_code=fund_code,money=money,fund_npv=fund_npv,url=url)

# 购买基金
@app.route('/fund_buy/<string:fund_name>',methods=['GET','POST'])
@is_logged_in
def fund_buy(fund_name):
    # 查询余额、股票资产
    user = MysqlUtil().fetchone("select * from users where username='%s'" % (session['username']))
    myMoney = user.get('money')
    fund_asset = user.get('fund_asset')
    # 获取购买框数据
    money = request.args.get("money", "")
    # 查询基金净值以及交易日期
    funds = MysqlUtil().fetchone("select * from fund_total where fund_name='%s'"%fund_name)
    fund_npv = float(funds.get('fund_npv'))
    fund_date = '2020'+funds.get('fund_date').replace('-', '')
    if float(money) < myMoney and float(money)  > fund_npv:
        orders = MysqlUtil().fetchone(
            "select * from fund_orders where username='%s' and fund_name='%s' and trade_date='%s'" % (
                session['username'], fund_name, fund_date))
        # 若本天有购买此基金订单，则增加数量
        if orders:
            MysqlUtil().update(
                "update fund_orders set count = '%s' where username='%s' and fund_name='%s' and trade_date='%s'" % (
                    (orders.get('fund_count') + float(money) / fund_npv), session['username'], fund_name, fund_date))
        # 无则添加订单
        else:
            MysqlUtil().insert(
                "insert into fund_orders(username,fund_name,fund_code,trade_date,bid_npv,fund_count) values ('%s','%s','%s','%s','%s','%s')" % (
                    session['username'], fund_name, funds.get('fund_code'), session['today'], fund_npv, float(money) / fund_npv))
        # 减少钱包余额，增加股票资产
        MysqlUtil().update("update users set money='%s' ,fund_asset='%s' where username='%s'" % (
            (myMoney - int(money)), (fund_asset + int(money)), session['username']))
        # 购买成功
        return jsonify({'success':True}), 200, {'ContentType':'application/json'}
    else:
        # 购买失败
        return jsonify({'error':True}),10001, {'ContentType':'application/json'}

if __name__ == '__main__':
    # 设置session的key
    app.secret_key = 'secret123'
    #开启调试模式
    app.run(debug=True)