You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
68 lines
2.6 KiB
68 lines
2.6 KiB
import math
|
|
from utils.Database import *
|
|
|
|
|
|
# 查询数据条数
|
|
async def get_total_data_count(total_data_sql):
|
|
total_data_count = 0
|
|
total_data_count_sql = "select count(*) as num from (" + total_data_sql + ") as temp_table"
|
|
result = await find_by_sql(total_data_count_sql,())
|
|
row = result[0] if result else None
|
|
if row:
|
|
total_data_count = row.get("num")
|
|
return total_data_count
|
|
|
|
|
|
def get_page_by_total_row(total_data_count, page_number, page_size):
|
|
total_page = (page_size != 0) and math.floor((total_data_count + page_size - 1) / page_size) or 0
|
|
if page_number <= 0:
|
|
page_number = 1
|
|
if 0 < total_page < page_number:
|
|
page_number = total_page
|
|
offset = page_size * page_number - page_size
|
|
limit = page_size
|
|
return total_data_count, total_page, offset, limit
|
|
|
|
|
|
async def get_page_data_by_sql(total_data_sql: str, page_number: int, page_size: int):
|
|
total_row: int = 0
|
|
total_page: int = 0
|
|
total_data_sql = total_data_sql.replace(";", "")
|
|
total_data_sql = total_data_sql.replace(" FROM ", " from ")
|
|
|
|
# 查询总数
|
|
total_data_count = await get_total_data_count(total_data_sql)
|
|
if total_data_count == 0:
|
|
return {"page_number": page_number, "page_size": page_size, "total_row": 0, "total_page": 0, "list": []}
|
|
else:
|
|
total_row, total_page, offset, limit = get_page_by_total_row(total_data_count, page_number, page_size)
|
|
|
|
# 构造执行分页查询的sql语句
|
|
page_data_sql = total_data_sql + " LIMIT %d offset %d " % (limit, offset)
|
|
print(page_data_sql)
|
|
# 执行分页查询
|
|
page_data = await find_by_sql(page_data_sql, ())
|
|
if page_data:
|
|
return {"page_number": page_number, "page_size": page_size, "total_row": total_row, "total_page": total_page, "list": page_data}
|
|
else:
|
|
return {"page_number": page_number, "page_size": page_size, "total_row": 0, "total_page": 0, "list": []}
|
|
|
|
|
|
# 翻译person_name, bureau_id
|
|
async def translate_person_bureau_name(page):
|
|
for item in page["list"]:
|
|
if item["person_id"] is not None:
|
|
person_id = str(item["person_id"])
|
|
person_name = await find_person_name_by_id(person_id)
|
|
if person_name:
|
|
item["person_name"] = person_name
|
|
else:
|
|
item["person_name"] = ""
|
|
if item["bureau_id"] is not None:
|
|
bureau_id = str(item["bureau_id"])
|
|
bureau_name = await find_bureau_name_by_id(bureau_id)
|
|
if bureau_name:
|
|
item["bureau_name"] = bureau_name
|
|
else:
|
|
item["bureau_name"] = ""
|
|
return page |