index.py

import math

from flask import Blueprint, jsonify, request
from flask import render_template
from sqlalchemy import not_, and_, or_, text

from ..model import Csdn  # 导入上级模块
from app import db

cs = Blueprint('csdn', __name__, url_prefix='/csdn')
nav = dict();
nav['csdn'] = 'active'


@cs.route('/blogstar')
def user_list():
    user = []
    page = request.args.get("page", 0)
    try:
        page = int(page)
    except Exception as e:
        return "what are you 弄啥嘞？ <a href='/'>返回首页</a>", 403
    if page == 0:
        user = Csdn.query.order_by(-Csdn.totalScore).all()
    elif page == 1:
        user = Csdn.query.filter(Csdn.cateName == "IT其他").order_by(-Csdn.totalScore).all()
    elif page == 2:
        user = Csdn.query.filter(Csdn.cateName == "前端").order_by(-Csdn.totalScore).all()
    elif page == 3:
        user = Csdn.query.filter(Csdn.cateName == "后端").order_by(-Csdn.totalScore).all()

    elif page == 4:
        user = Csdn.query.filter(Csdn.cateName == "大数据与算法").order_by(-Csdn.totalScore).all()

    elif page == 5:
        user = Csdn.query.filter(Csdn.cateName == "云原生与云平台").order_by(-Csdn.totalScore).all()
    elif page == 6:
        user = Csdn.query.filter(Csdn.cateName == "前沿技术").order_by(-Csdn.totalScore).all()
    elif page == 7:
        user = Csdn.query.filter(Csdn.cateName == "人工智能").order_by(-Csdn.totalScore).all()
    elif page == 8:
        user = Csdn.query.filter(Csdn.cateName == "运维与安全").order_by(-Csdn.totalScore).all()
    elif page == 9:
        user = Csdn.query.filter(Csdn.cateName == "移动开发").order_by(-Csdn.totalScore).all()

    elif page == 10:
        user = Csdn.query.filter(Csdn.cateName == "物联网与嵌入式").order_by(-Csdn.totalScore).all()

    for u in user:
        if u.regtime is not None:
            u.star = "博客新星" if u.regtime.startswith("2022") else "博客之星"
        else:
            u.star = "---"

    bg_green = 200 if page == 0 else 10
    return render_template('csdn/blogstar.html', user=user, bg_green=bg_green, nav=nav)


@cs.route('/newstar')
def new_star_list():
    user = Csdn.query.order_by(-Csdn.totalScore).filter(Csdn.regtime.startswith("2022")).all()
    return render_template('csdn/newstar.html', user=user, nav=nav)


@cs.route('/zong')
def zong_star_list():
    user = []
    t = request.args.get("t", 0)
    try:
        t = int(t)
    except Exception as e:
        return "what are you 弄啥嘞？ <a href='/'>返回首页</a>", 403

    if t == 0:
        user = Csdn.query.order_by(-Csdn.totalScore).all()

    elif t == 1:
        user = Csdn.query.order_by(-Csdn.yuanli).all()
    elif t == 2:
        user = Csdn.query.order_by(-Csdn.fensi).all()
    elif t == 3:
        user = Csdn.query.order_by(-Csdn.fangwen).all()
    elif t == 4:
        user = Csdn.query.order_by(-Csdn.yuanchuang).all()
    elif t == 5:
        user = Csdn.query.order_by(Csdn.zongbang).all()
    elif t == 6:
        user = Csdn.query.order_by(-Csdn.tie).all()
    elif t == 7:
        # sql  = "SELECT * FROM score ORDER BY CONVERT(ip USING gbk) DESC"
        sql = "SELECT * FROM score ORDER BY CONVERT(ip USING gbk) DESC"
        result = db.engine.execute(text(sql))
        user = [row for row in result]
    """
<a class="btn btn-success" href="/csdn/zong?t=1">按原力排名</a>
<a class="btn btn-success" href="/csdn/zong?t=2">按粉丝排名</a>
<a class="btn btn-success" href="/csdn/zong?t=3">按访问数排名</a>
<a class="btn btn-success" href="/csdn/zong?t=4">按文章数排名</a>
<a class="btn btn-success" href="/csdn/zong?t=5">按总榜数排名</a>
<a class="btn btn-success" href="/csdn/zong?t=6">按铁粉数排名</a>
<a class="btn btn-success" href="/csdn/zong?t=7">按省市排序</a>
    """

    return render_template('csdn/blogstar_d.html', user=user, nav=nav)


from datetime import date, timedelta


@cs.route('/rank')
def column():
    yesterday = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")

    sql = text(f"""select * from (
select zhuanlan_user,sum(price) as total,count(id) as ren from subscribe where subscribe_time like '{yesterday}%'
GROUP BY zhuanlan_user
ORDER BY total desc 
limit 50
) abc 
LEFT JOIN zhuanlan z ON z.userName = abc.zhuanlan_user""")

    result = db.engine.execute(sql)
    data = result.fetchall()
    nav['csdn'] = ''
    nav['csdn_col'] = 'active'
    return render_template('csdn/rank.html', data=data, nav=nav, yesterday=yesterday)


@cs.route('/m_rank')
def m_column():
    yesterday = (date.today() + timedelta(days=-1)).strftime("%Y-%m")

    sql = text(f"""select * from (
select zhuanlan_user,sum(price) as total,count(id) as ren from subscribe where subscribe_time like '{yesterday}%'
GROUP BY zhuanlan_user
ORDER BY total desc 
limit 50
) abc 
LEFT JOIN zhuanlan z ON z.userName = abc.zhuanlan_user""")

    result = db.engine.execute(sql)
    data = result.fetchall()

    sql2 = text(f"""
    select title,count(id) n from subscribe where subscribe_time like '{yesterday}%' GROUP BY title
ORDER BY n desc limit 10 """)
    result2 = db.engine.execute(sql2)
    data2 = result2.fetchall()

    nav['csdn'] = ''
    nav['csdn_col'] = 'active'
    return render_template('csdn/m_rank.html', data=data, nav=nav, yesterday=yesterday, hot_col=data2)