def getRecentWeekData(username):
new_db = newConnection()
new_cursor = new_db.cursor()
# 取一个月前的日期
oneMonthAgoDate, dateSequence = Utils.getWeekDate()
# sql_pass获取通过的数量
sql_pass = """SELECT
DATE_FORMAT(`VideoUploadTime`, '%Y-%m-%d') days,
count(*) sum
FROM
video
WHERE `VideoUploadTime` > '{}' and videostatus='审核通过' and VideoUploaderName='{}'
GROUP BY days ;
""".format(oneMonthAgoDate, username)
new_cursor.execute(sql_pass)
records = new_cursor.fetchall()
data = {}
for re in records:
data[re[0]] = re[1]
data_pass = Utils.DateListMatchSql(dateSequence, data)
# sql_process获取审核中的数量
sql_process = """SELECT
DATE_FORMAT(`VideoUploadTime`, '%Y-%m-%d') days,
count(*) sum
FROM
video
WHERE `VideoUploadTime` > '{}' and videostatus='审核中' and VideoUploaderName='{}'
GROUP BY days ;
""".format(oneMonthAgoDate, username)
new_cursor.execute(sql_process)
records = new_cursor.fetchall()
data = {}
for re in records:
data[re[0]] = re[1]
data_process = Utils.DateListMatchSql(dateSequence, data)
# sql_pass获取审核失败的数量
sql_fail = """SELECT
DATE_FORMAT(`VideoUploadTime`, '%Y-%m-%d') days,
count(*) sum
FROM
video
WHERE `VideoUploadTime` > '{}' and videostatus='不通过' and VideoUploaderName='{}'
GROUP BY days ;
""".format(oneMonthAgoDate, username)
new_cursor.execute(sql_fail)
records = new_cursor.fetchall()
data = {}
for re in records:
data[re[0]] = re[1]
data_fail = Utils.DateListMatchSql(dateSequence, data)
new_db.close()
return {
'process': data_process,
'pass': data_pass,
'fail': data_fail,
'dateSequence': dateSequence
}
def getRecentYearData():
new_db = Connection.newConnection()
new_cursor = new_db.cursor()
lastYearToday = Utils.getLastYearTodayTime()
sql = """SELECT
DATE_FORMAT(`VideoUploadTime`, '%Y-%m') months,
count(*) sum
FROM
video
WHERE `VideoUploadTime` > '{}'
GROUP BY months ;
""".format(lastYearToday)
new_cursor.execute(sql)
records = new_cursor.fetchall()
dateSequence = Utils.getYearDateSequence()
data = {}
for re in records:
data[re[0]] = re[1]
print(Utils.DateListMatchSql(dateSequence, data))