wx-parent/cloud/database/interface/home.js

const { models } = require('../db')


// 获取今日起止时间
function getTodayRange() {
    const now = new Date();

    // 当天起始时间（00:00:00.000）
    const startOfDay = new Date(now);
    startOfDay.setHours(0, 0, 0, 0);

    // 当天结束时间（23:59:59.999）
    const endOfDay = new Date(now);
    endOfDay.setHours(23, 59, 59, 999);

    return { startOfDay, endOfDay };
}

// 获取本年起止时间
function getYearRange() {
    const now = new Date();
    const currentYear = now.getFullYear();

    // 本年起始时间（1月1日 00:00:00.000）
    const startOfYear = new Date(currentYear, 0, 1);

    // 本年结束时间（12月31日 23:59:59.999）
    const endOfYear = new Date(currentYear + 1, 0, 0); // 下一年1月0日即本年最后一天

    return { startOfYear, endOfYear };
}

/**
 * 今日今年下单付款统计
 */
async function findCountAndTotal() {
    const todayRange = getTodayRange()
    const sql = "select IF(count(*) <=> null, 0, count(*)) as ri_xiadan, IF(sum(real_money) <=> null, 0, sum(real_money)) as ri_jine from orders where createdAt >= {{start}} AND createdAt <= {{end}} and (status = 1 or status = 2)"
    try {
        const today = await models.$runSQL(sql, {
            start: todayRange.startOfDay.getTime(),
            end: todayRange.endOfDay.getTime(),
        })
        const todayData = today.data.executeResultList[0]
        const yearRange = getYearRange()
        const params = {
            start: yearRange.startOfYear.getTime(),
            end: yearRange.endOfYear.getTime(),
        }
        const year = await models.$runSQL(sql, params)
        const yearData = year.data.executeResultList[0]
        const result = {
            ri_jine: todayData.ri_jine,
            ri_xiadan: todayData.ri_xiadan,
            nian_jine: yearData.ri_jine,
            nian_xiadan: yearData.ri_xiadan
        }
        return {
            success: true,
            msg: '查询成功',
            data: result,
        }
    } catch (error) {
        console.error('error =>', error)
        return {
            success: false,
            msg: '查询失败',
            error: error,
        }
    }
}

/**
 * 异常行为预警
 */
async function findBehaviorAlarm() {
    try {
        const sql = "select aba._id       as _id, " +
            "       aba.file_name as file_name, " +
            "       s.name        as school_name, " +
            "       aba.reason    as reason, " +
            "       aba.createdAt, " +
            "       tu.name       as teacher_name " +
            "from abnormal_behavior_alarm aba " +
            "         left join wx_teacher_user tu on aba.user_id = tu._id " +
            "         left join wx_school s on tu.school_id = s._id " +
            "where aba.type = 0 " +
            "order by aba.createdAt desc " +
            "limit 4 "
        const result = await models.$runSQL(sql, {})
        return {
            success: true,
            msg: '查询成功',
            data: result.data.executeResultList,
        }
    } catch (error) {
        console.error('error =>', error)
        return {
            success: false,
            msg: '查询失败',
            error: error,
        }
    }
}
/**
 * 按月份统计下单量
 * @param {*} parameter 年份 e.g: 2025
 */
async function findOrderCountByMonth(parameter) {
    const { year } = parameter
    try {
        // const sql = "WITH RECURSIVE months AS ( " +
        //     "    SELECT 1 AS month_num " +
        //     "    UNION ALL " +
        //     "    SELECT month_num + 1 " +
        //     "    FROM months " +
        //     "    WHERE month_num < 12 " +
        //     "), " +
        //     "target_year AS ( " +
        //     "    SELECT COALESCE(YEAR(FROM_UNIXTIME(MIN(createdAt)/1000)), YEAR(CURDATE())) AS year " +
        //     "    FROM orders " +
        //     ") " +
        //     "SELECT " +
        //     "    ty.year, " +
        //     "    CONCAT(ty.year, '-', LPAD(m.month_num, 2, '0')) AS month, " +
        //     "    COUNT(o._id) AS order_count " +
        //     "FROM target_year ty " +
        //     "CROSS JOIN months m " +
        //     "LEFT JOIN orders o " +
        //     "    ON DATE_FORMAT(FROM_UNIXTIME(o.createdAt/1000), '%Y-%m') = CONCAT(ty.year, '-', LPAD(m.month_num, 2, '0')) " +
        //     "where ty.year = {{searchYear}}" +
        //     " GROUP BY ty.year, m.month_num " +
        //     "ORDER BY ty.year, m.month_num "
        const sql = "select * " +
            "from (select COUNT(*)                                                     as count, " +
            "             DATE_FORMAT(FROM_UNIXTIME(orders.createdAt / 1000), '%Y-%m') as month, " +
            "             DATE_FORMAT(FROM_UNIXTIME(orders.createdAt / 1000), '%Y')    as year " +
            "      from orders " +
            "      group by month, year " +
            "      order by year, month) t " +
            "where year = {{searchYear}}"
        const result = await models.$runSQL(sql, { searchYear: year })
        console.log(result.data);
        return {
            success: true,
            msg: '查询成功',
            data: result.data.executeResultList,
        }
    } catch (error) {
        console.error('error =>', error)
        return {
            success: false,
            msg: '查询失败',
            error: error,
        }
    }
}
/**
 * 按天统计下单量
 * @param {*} parameter 年份 e.g: 2025
 * @returns 
 */
async function findOrderCountByDay(parameter) {
    const { year } = parameter
    try {
        const sql = "select * " +
            "from (select COUNT(*)                                                        as count, " +
            "             DATE_FORMAT(FROM_UNIXTIME(orders.createdAt / 1000), '%Y-%m-%d') as day, " +
            "             DATE_FORMAT(FROM_UNIXTIME(orders.createdAt / 1000), '%Y')       as year " +
            "      from orders " +
            "      group by day, year " +
            "      order by year, day) t " +
            "where year = {{searchYear}}"
        const params = {
            searchYear: year
        }
        const result = await models.$runSQL(sql, params)
        return {
            success: true,
            msg: '查询成功',
            data: result.data.executeResultList,
        }
    } catch (error) {
        console.error('error =>', error)
        return {
            success: false,
            msg: '查询失败',
            error: error,
        }
    }
}
/**
 * 按月份分组查询课件上传统计
 * @param {*} parameter 年份 e.g: 2025
 */
async function findFileCount(parameter) {
    const { year } = parameter
    try {
        const sql = "select * " +
            "from (select COUNT(*)                                                     as count, " +
            "             DATE_FORMAT(FROM_UNIXTIME(createdAt / 1000), '%Y-%m') as month, " +
            "             DATE_FORMAT(FROM_UNIXTIME(createdAt / 1000), '%Y')    as year " +
            "      from file_manage " +
            "      group by month, year " +
            "      order by year, month) t " +
            "where year = {{searchYear}}"
        const params = {
            searchYear: year
        }
        const result = await models.$runSQL(sql, params)
        return {
            success: true,
            msg: '查询成功',
            data: result.data.executeResultList,
        }
    } catch (error) {
        console.error('error =>', error)
        return {
            success: false,
            msg: '查询失败',
            error: error,
        }
    }
}

module.exports = {
    findCountAndTotal,
    findBehaviorAlarm,
    findOrderCountByMonth,
    findOrderCountByDay,
    findFileCount,
}