ic-ctbu-backend/apis/Lepao/Record/GetLepaoRecords.js

const API = require("../../../lib/API.js");
const db = require("../../../plugin/DataBase/db.js");
const { BaseStdResponse } = require("../../../BaseStdResponse.js");
const AccessControl = require("../../../lib/AccessControl.js");

class GetLepaoRecords extends API {
    constructor() {
        super();

        this.setPath('/Lepao/Records')
        this.setMethod('GET')
    }

    async onRequest(req, res) {
        let { uuid, session, name, lepao_account, email, queryTime, pagesize, current } = req.query

        if ([uuid, session, pagesize, current].some(value => value === '' || value === null || value === undefined))
            return res.json({
                ...BaseStdResponse.MISSING_PARAMETER,
                endpoint: 1513126
            })

        // 校验分页参数
        if (isNaN(pagesize) || pagesize <= 0) {
            return res.json({
                ...BaseStdResponse.ERR,
                msg: '参数错误'
            })
        }

        if (isNaN(current) || current <= 0) {
            return res.json({
                ...BaseStdResponse.ERR,
                msg: '参数错误'
            })
        }

        if (!await AccessControl.checkSession(uuid, session))
            return res.status(401).json({
                ...BaseStdResponse.ACCESS_DENIED
            })

        // 计算分页的 offset
        const offset = (current - 1) * pagesize

        let sql = `
                SELECT 
                    r.id,
                    r.uuid,
                    r.time,
                    r.area,
                    r.lepao_account,
                    r.startTime,
                    r.endTime,
                    r.distance,
                    r.state,
                    a.name,
                    a.user_avatar
                FROM 
                    lepao_record r
                LEFT JOIN 
                    lepao_account a
                ON 
                    r.lepao_account = a.student_num
                WHERE 
                    (r.uuid = ? OR a.create_user = ?)
            `

        let countSql = `
            SELECT COUNT(*) AS total
            FROM lepao_record r
            JOIN lepao_account a ON r.lepao_account = a.student_num
            WHERE (r.uuid = ? OR a.create_user = ?)
        `

        let params = [uuid, uuid]
        let countParams = [uuid, uuid]

        if (lepao_account) {
            sql += ` AND r.lepao_account LIKE ?`
            countSql += ` AND r.lepao_account LIKE ?`
            params.push(`%${lepao_account}%`)
            countParams.push(`%${lepao_account}%`)
        }

        if (email) {
            sql += ` AND a.email = ?`
            countSql += ` AND a.email = ?`
            params.push(email)
            countParams.push(email)
        }

        if (name) {
            sql += ` AND a.name LIKE ?`
            countSql += ` AND a.name LIKE ?`
            params.push(`%${name}%`)
            countParams.push(`%${name}%`)
        }

        if (queryTime && Array.isArray(queryTime) && queryTime.length === 2) {
            sql += `
                AND STR_TO_DATE(r.startTime, '%Y-%m-%d %H:%i:%s')
                    >= FROM_UNIXTIME(? / 1000)
                AND STR_TO_DATE(r.startTime, '%Y-%m-%d %H:%i:%s')
                    < FROM_UNIXTIME(? / 1000)
            `

            countSql += `
                AND STR_TO_DATE(r.startTime, '%Y-%m-%d %H:%i:%s')
                    >= FROM_UNIXTIME(? / 1000)
                AND STR_TO_DATE(r.startTime, '%Y-%m-%d %H:%i:%s')
                    < FROM_UNIXTIME(? / 1000)
            `

            params.push(queryTime[0], queryTime[1])
            countParams.push(queryTime[0], queryTime[1])
        }

        sql += `
            ORDER BY id DESC
            LIMIT ? OFFSET ?;
        `
        params.push(String(pagesize), String(offset))

        let rows = await db.query(sql, params)
        let countResult = await db.query(countSql, countParams)

        if (!rows || !countResult)
            return res.json({
                ...BaseStdResponse.DATABASE_ERR
            })

        let total = countResult[0].total

        res.json({
            ...BaseStdResponse.OK,
            data: rows,
            pagination: {
                current,
                pagesize,
                total
            }
        })
    }
}

module.exports.GetLepaoRecords = GetLepaoRecords