navi/run.py

import datetime,time
import os
import sqlite3
from gevent.pywsgi import WSGIServer
from flask import Flask, abort, render_template, g, escape, request, make_response, session, redirect, url_for
from flask_caching import Cache

app = Flask(__name__)
# 从环境变量读取密钥，未设置时生成临时随机密钥（重启后失效，生产环境请务必设置）
import os
app.secret_key = os.environ.get('NAVI_SECRET_KEY') or os.urandom(24)
# ===========db
DATABASE = './navi.db'
cache = Cache(config={"DEBUG": True,'CACHE_TYPE': 'SimpleCache',"CACHE_DEFAULT_TIMEOUT": 300})
cache.init_app(app)


def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
    return db


@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()


def init_db():
    with app.app_context():
        db = get_db()
        # 显式以 utf-8 读取，避免 Windows(GBK) 下因中文注释解码失败
        with app.open_resource('./schema.sql', mode='r', encoding='utf-8') as f:
            db.cursor().executescript(f.read())
        db.commit()


def ensure_db():
    """确保数据库文件及表结构存在，首次运行自动按 schema.sql 初始化。"""
    if not os.path.exists(DATABASE):
        print("数据库不存在，正在初始化：%s" % DATABASE)
        init_db()


# 启动时检查并初始化数据库
ensure_db()
# init_db()  # 如需重置数据库结构可手动调用

def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = [dict((cur.description[idx][0], value)
               for idx, value in enumerate(row)) for row in cur.fetchall()]
    cur.close()
    return (rv[0] if rv else None) if one else rv


def execute_db(query, args=()):
    cur = get_db().execute(query, args)
    cur.connection.commit()
    cur.close()


# ===========web
@app.route('/')
def index():
    add_views()
    current_online()
    return render_template('index.html',
                           search_list=customize_search_list(),
                           quick_group=customize_quick_group())

@app.route('/customize.html')
def customize():
    if get_user() is None:
        return redirect(url_for('login'))
    return render_template('customize.html')

@app.route('/icon.html')
def icon():
    return render_template('icon.html')

@app.route('/analytics.html')
def analytics():
    return render_template('analytics.html')



@app.route('/search.html')
def search():
    return render_template('search.html')

@app.route('/go/<string:type>/<string:id>.html')
def go(type,id):
    if get_user() is not None:
        query_sql="select * from quick where id = "+id
        item = query_db(query_sql,[],True)
    else:
        query_sql="select * from library where id = "+id
        item = query_db(query_sql,[],True)
    return render_template('go.html',item = item)


# ===========global
white_url = ["/", '/login']
auth_url = ["/customize.html"]


# 权限认证
# 不在白名单、不是静态文件、没有session =》 403
def permission_required():
    def decorator(f):
        def decorated_function(*args, **kwargs):
            try:
                if auth_url.__contains__(request.path) is True and request.path.startswith('/static/') is False \
                        and session.get("username") is None:
                    abort(403)
                return f(*args, **kwargs)
            except Exception as e:
                print(e)
                abort(403)

        return decorated_function

    return decorator


# 每一次请求前执行
# @app.before_request
# @permission_required()
# def before_request_handler():
# app.logger.info('【请求方法】{}【请求路径】{}【请求地址】{}'.format(request.method, request.path, request.remote_addr))


@app.after_request
def after_request_handler(resp):
    resp = make_response(resp)
    resp.headers['Access-Control-Allow-Origin'] = '*'
    resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
    resp.headers['Access-Control-Allow-Headers'] = 'x-requested-with,content-type'
    return resp

# ===========error
@app.errorhandler(401)
def page_not_found(error):
    return render_template('error/401.html'), 401

@app.errorhandler(404)
def page_not_found(error):
    return render_template('error/404.html'), 404


@app.errorhandler(403)
def page_not_found(error):
    return render_template('error/403.html'), 403


# ===========manager
@app.route('/logout')
def logout():
    session.pop('user_id', None)
    session.pop('user_role', None)
    session.pop('username', None)
    return redirect(url_for('index'))


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        query_sql = 'select * from user where username=?;'
        user = query_db(query_sql, [username], True)
        print(user['username'], '--', user['id'], '，登录:', datetime.datetime.now())
        if user['password'] is not None and user['password'] == password:
            session['username'] = user['username']
            session['user_id'] = user['id']
            if user['type'] ==1:
                session['user_role'] = "admin"
            else:
                session['user_role'] = "user"
            return redirect(url_for('index'))
        return render_template('auth.html', type="login", attention="用户不存在或密码错误")
    return render_template('auth.html', type="login")


@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        query_sql = 'select * from user where username=?;'
        user = query_db(query_sql, [username], True)
        if user['id'] is None:
            insert_sql = 'insert into user (username,password,status,type) values (?,?,1,0);'
            execute_db(insert_sql, [username, password])
            app.logger.info(username, '注册:', datetime.datetime.now())
            return redirect(url_for('index'))
        return render_template('auth.html', type="register", attention="用户名已存在")
    return render_template('auth.html', type="register")


@app.route('/customize/search', methods=['GET', 'POST', 'DELETE'])
def customize_search_api():
    user_id = session.get('user_id')
    username = session.get('username')
    insert_sql = 'insert into search (user_id,name,icon,url,sort,status) values (?,?,?,?,?,?);'
    update_sql = 'update search set name=?,icon=?,url=?,sort=?,status=? where id=? '
    query_sql = 'select * from search where id=?'
    delete_sql = 'delete from search where id=?'
    if request.method == 'POST':
        id = request.form['id']
        name = request.form['name']
        icon = request.form['icon']
        url = request.form['url']
        sort = request.form['sort']
        status = request.form['status']
        if id is not None and len(id) > 0:
            item = query_db(query_sql, [id], one=True)
            if item['user_id'] == user_id:
                execute_db(update_sql, [name, icon, url, sort, status, id])
        else:
            execute_db(insert_sql, [user_id, name, icon, url, sort, status])
    elif request.method == 'GET':
        id = request.values.get('id')
        item = query_db(query_sql, [id], one=True)
        return {"code": 1, "data": item}
    elif request.method == 'DELETE':
        id = request.values.get('id')
        item = query_db(query_sql, [id], one=True)
        if item['user_id'] == user_id:
            execute_db(delete_sql, [id])
    else:
        return {"code": 0}
    return {"code": 1}


@app.route('/customize/quick', methods=['GET', 'POST', 'DELETE'])
def customize_quick_api():
    user_id = session.get('user_id')
    insert_sql = 'insert into quick (user_id,name,icon,img,color,url,type,cate,tags,summary,sort,status) ' \
                 'values (?,?,?,?,?,?,?,?,?,?,?,?);'
    update_sql = 'update quick set name=?,icon=?,img=?,color=?,url=?,type=?,cate=?,tags=?,summary=?,sort=?,status=? ' \
                 'where id=? '
    query_sql = 'select * from quick where id=?'
    delete_sql = 'delete from quick where id=?'
    if request.method == 'POST':
        id = request.form['id']
        name = request.form['name']
        color = request.form['color']
        icon = request.form['icon']
        img = request.form['img']
        url = request.form['url']
        type = request.form['type']
        cate = request.form['cate']
        tags = request.form['tags']
        summary = request.form['summary']
        sort = request.form['sort']
        status = request.form['status']
        if id is not None and len(id) > 0:
            item = query_db(query_sql, [id], one=True)
            if item['user_id'] == user_id:
                execute_db(update_sql, [name, icon,img, color, url, type, cate, tags, summary, sort, status, id])
        else:
            execute_db(insert_sql, [user_id, name, icon,img, color, url, type, cate, tags, summary, sort, status])
    elif request.method == 'GET':
        id = request.values.get('id')
        item = query_db(query_sql, [id], one=True)
        return {"code": 1, "data": item}
    elif request.method == 'DELETE':
        id = request.values.get('id')
        item = query_db(query_sql, [id], one=True)
        if item['user_id'] == user_id:
            execute_db(delete_sql, [id])
    else:
        return {"code": 0}
    return {"code": 1}

@app.route('/customize/library', methods=['GET', 'POST', 'DELETE'])
def customize_library_api():
    user_role = get_user_role()
    insert_sql = 'insert into library (name,icon,img,color,url,type,cate,tags,summary,sort,status) ' \
                 'values (?,?,?,?,?,?,?,?,?,?,?);'
    update_sql = 'update library set name=?,icon=?,img=?,color=?,url=?,type=?,cate=?,tags=?,summary=?,sort=?,status=? ' \
                 'where id=? '
    query_sql = 'select * from library where id=?'
    delete_sql = 'delete from library where id=?'
    if request.method == 'POST':
        if user_role != "admin":
            abort(403)
        id = request.form['id']
        name = request.form['name']
        color = request.form['color']
        icon = request.form['icon']
        img = request.form['img']
        url = request.form['url']
        type = request.form['type']
        cate = request.form['cate']
        tags = request.form['tags']
        summary = request.form['summary']
        sort = request.form['sort']
        status = request.form['status']
        if id is not None and len(id) > 0:
            execute_db(update_sql, [name, icon,img, color, url, type, cate, tags, summary, sort, status, id])
        else:
            execute_db(insert_sql, [name, icon,img, color, url, type, cate, tags, summary, sort, status])
    elif request.method == 'GET':
        id = request.values.get('id')
        item = query_db(query_sql, [id], one=True)
        return {"code": 1, "data": item}
    elif request.method == 'DELETE':
        if user_role != "admin":
            abort(403)
        id = request.values.get('id')
        execute_db(delete_sql, [id])
    else:
        return {"code": 0}
    return {"code": 1}

def customize_search_list():
    user_id = session.get('user_id')
    if user_id is None:
        query_sql = 'select * from library where type = 1 order by sort'
        data_list = query_db(query_sql, [], False)
        return data_list
    else:
        query_sql = 'select * from search where user_id = ? order by sort'
        data_list = query_db(query_sql, [user_id], False)
        return data_list


def customize_quick_list():
    user_id = session.get('user_id')
    if user_id is None:
        query_sql = 'select * from library where type = 2 order by sort'
        data_list = query_db(query_sql, [], False)
        return data_list
    else:
        query_sql = 'select * from quick where user_id = ? order by sort'
        data_list = query_db(query_sql, [user_id], False)
        return data_list
    
def customize_library_list():
    query_sql = 'select * from library where type > 0 order by sort'
    data_list = query_db(query_sql, [], False)
    return data_list

def page_handler(page,total,query_sql):
    size = 3
    offset = 0
    if page is not None:
        if page > 1:
            offset = (page-1) * size
        query_sql = query_sql+' limit 0 offset '+offset
    return query_sql


def customize_quick_group():
    data_list = customize_quick_list()
    data_group = {}
    for data in data_list:
        if data['cate'] not in data_group:
            data_group[data['cate']] = [data]
        else:
            data_group[data['cate']].append(data)
    # print(data_group)
    data_grooup_list = []
    for i in sorted(data_group): 
        data_grooup_list.append(data_group[i])
    return data_grooup_list


@app.route('/customize/search.html')
def customize_search():
    return render_template('customize/search.html', page_type="search",data_list=customize_search_list())


@app.route('/customize/quick.html')
def customize_quick():
    return render_template('customize/quick.html', page_type="quick",data_list=customize_quick_list())

@app.route('/customize/library.html')
def customize_library():
    return render_template('customize/library.html',page_type="library",data_list=customize_library_list())

@app.route('/customize/monitor.html')
def customize_monitor():
    return render_template('customize/monitor.html',page_type="monitor")


# ===========seo
@app.route('/sitemap.xml', methods=['GET'])
def sitemap():
    try:
        """Generate sitemap.xml. Makes a list of urls and date modified."""
        pages = []
        ten_days_ago = (datetime.datetime.now() - datetime.timedelta(days=7)).date().isoformat()
        # static pages
        site_url = os.environ.get('NAVI_SITE_URL', '').rstrip('/')
        for rule in app.url_map.iter_rules():
            if "GET" in rule.methods and len(rule.arguments) == 0:
                pages.append(
                    [site_url + str(rule.rule), ten_days_ago]
                )
        sitemap_xml = render_template('sitemap_template.xml', pages=pages)
        response = make_response(sitemap_xml)
        response.headers["Content-Type"] = "application/xml"
        return response
    except Exception as e:
        return (str(e))



# ===========db-handler
def add_views():
    views_name='views'
    today = datetime.date.today() 
    query_sql = 'select * from site where name=? and time=?'
    update_sql = 'update site set value=? where name=? and time=?'
    insert_sql = 'insert into site (name,value,time) values(?,?,?)'
    views_today = query_db(query_sql,[views_name,today],one=True)
    if views_today is None:
        execute_db(insert_sql,[views_name,1,today])
    else:
        new_value = int(views_today['value'])+1
        execute_db(update_sql,[new_value,views_name,today])

def current_online():
    user_id = session.get('user_id')
    online_user = cache.get("online_user")
    if online_user is None:
        online_user = []
    # print(user_id)
    if user_id is not None:
        if user_id not in online_user:
            online_user.append(user_id)
    # print(online_user)
    cache.set(key="online_user",value=online_user,timeout=10800)
    online_name='online'
    online_num=len(online_user)
    today = datetime.date.today() 
    query_sql = 'select * from site where name=? and time=?'
    update_sql = 'update site set value=? where name=? and time=?'
    insert_sql = 'insert into site (name,value,time) values(?,?,?)'
    views_today = query_db(query_sql,[online_name,today],one=True)
    if views_today is None:
        execute_db(insert_sql,[online_name,online_num,today])
    else:
        execute_db(update_sql,[online_num,online_name,today])


def get_site_num(time):
    query_sql="select time,sum(case name when 'views' then value else 0 end ) as views, \
        sum(case name when 'online' then value else 0 end ) as online \
        from site where time=?;"
    time_data = query_db(query_sql,[time],one=True)
    if time_data['time'] is None:
        return {'views':0,'online':0}
    return time_data

@cache.cached(timeout=30)
def get_site_all_num():
    query_sql="select time,sum(case name when 'views' then value else 0 end ) as views, \
        sum(case name when 'online' then value else 0 end ) as online \
        from site;"
    time_data_list = query_db(query_sql,[],one=False)
    if time_data_list is None:
        return []
    return time_data_list

# ===========template_global
@app.template_global()
def get_user():
    return session.get("username")

@app.template_global()
def get_user_id():
    return session.get("user_id")

@app.template_global()
def get_user_role():
    return session.get("user_role")

@app.template_global()
def get_today_data():
    today=datetime.date.today() 
    return get_site_num(today)

@app.template_global()
def get_yesterday_data():
    today=datetime.date.today() 
    oneday=datetime.timedelta(days=1) 
    yesterday=today-oneday
    return get_site_num(yesterday)

@app.template_global()
def get_library_data():
    query_sql='SELECT sum(case "type" when "1" then total else 0 end ) as search, \
    sum(case "type" when "2" then total else 0 end ) as quick from (SELECT "type",COUNT(1) as total from library group by "type");'
    library_total = query_db(query_sql,[],one=True)
    return library_total

@app.template_global()
def get_user_total():
    query_sql='SELECT count(1) as total from user;'
    user_total = query_db(query_sql,[],one=True)
    return user_total

# http_server = WSGIServer(("0.0.0.0", 18100), app)
# http_server.serve_forever()
if __name__ == "__main__":
    app.run('0.0.0.0', 18100, debug=True)