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

const { models } = require('../db')
const { validateField } = require('../common')
/**
 * 
 * 付款统计
 * 
 */
/**
 * 分页查询付款统计数据(按照班级分组)
 * @param {*} school_id 学校Id
 * @param {*} grade 年级
 * @param {*} pageNum 当前页
 * @param {*} pageSize 每页展示数量
 * @returns 分页数据
 */
async function findPaymentPage(parameter) {
    const { school_id, grade, pageNum, pageSize } = parameter
    const v1 = validateField(pageNum)
    if (!v1.flag) {
        return {
            success: false,
            records: [],
            msg: v1.msg
        }
    }
    const v2 = validateField(pageSize)
    if (!v2.flag) {
        return {
            success: false,
            records: [],
            msg: v2.msg
        }
    }
    // 查询参数
    const params = {
        pageNum,
        pageSize
    }
    // 拼接查询条件
    let condition = ""
    if (school_id != null) {
        condition += " where s._id = {{school_id}} "
        params.school_id = school_id
    }
    if (grade != null) {
        if (condition) {
            condition += " and u.grade = {{grade}} "
        } else {
            condition += " where u.grade = {{grade}} "
        }
        params.grade = grade
    }

    const skip = (pageNum - 1) * pageSize
    // 查询sql
    const sql = "select * " +
        "from (select s.name                                       as schoolName, " +
        "             u.grade                                      as grade, " +
        "             count(IF(o.status IN (0, 3), o._id, NULL))   as unpaidCount, " +
        "             count(IF(o.status IN (1, 2), o._id, NULL))   as paidCount, " +
        "             sum(IF(o.status IN (0, 3), o.real_money, 0)) as unpaidMoney, " +
        "             sum(IF(o.status IN (1, 2), o.real_money, 0)) as paidMoney " +
        "      from orders o " +
        "               left join wx_user u on o.user_id = u._id " +
        "               left join wx_school s on u.school_id = s._id " +
        condition +
        "      group by s.name, u.grade) as t " +
        "limit " + skip + ", " + pageSize
    const countSql = "select count(*) as total " +
        "from (select s.name                                       as schoolName," +
        "             u.grade                                      as grade, " +
        "             count(IF(o.status IN (0, 3), o._id, NULL))   as unpaidCount, " +
        "             count(IF(o.status IN (1, 2), o._id, NULL))   as paidCount, " +
        "             sum(IF(o.status IN (0, 3), o.real_money, 0)) as unpaidMoney, " +
        "             sum(IF(o.status IN (1, 2), o.real_money, 0)) as paidMoney " +
        "      from orders o " +
        "               left join wx_user u on o.user_id = u._id " +
        "               left join wx_school s on u.school_id = s._id " +
        condition +
        "       group by s.name, u.grade) as t "
    try {
        // 查询数据sql
        const records = await models.$runSQL(sql, params)
        const count = await models.$runSQL(countSql, params)
        return {
            sql,
            countSql,
            params,
            success: true,
            msg: '查询成功',
            records: records.data.executeResultList,
            total: count.data.executeResultList[0].total
        }
    } catch (error) {
        return {
            success: false,
            msg: '查询失败',
            params,
            sql,
            countSql,
            error: error
        }
    }
}

/**
 * 分页查询付款统计数据(按照学校分组)
 * @param {*} school_id 学校Id
 * @param {*} pageNum 当前页
 * @param {*} pageSize 每页展示数量
 * @returns 分页数据
 */
async function findPaymentPageBySchool(parameter) {
    const { school_id, pageNum, pageSize } = parameter
    const v1 = validateField(pageNum)
    if (!v1.flag) {
        return {
            success: false,
            records: [],
            msg: v1.msg
        }
    }
    const v2 = validateField(pageSize)
    if (!v2.flag) {
        return {
            success: false,
            records: [],
            msg: v2.msg
        }
    }
    // 查询参数
    const params = {
        pageNum,
        pageSize
    }
    // 拼接查询条件
    let condition = ""
    if (school_id != null) {
        condition += " where s._id = {{school_id}} "
        params.school_id = school_id
    }

    const skip = (pageNum - 1) * pageSize
    // 查询sql
    const sql = "select * " +
        "from (select s.name                                       as schoolName, " +
        "             count(IF(o.status IN (0, 3), o._id, NULL))   as unpaidCount, " +
        "             count(IF(o.status IN (1, 2), o._id, NULL))   as paidCount, " +
        "             sum(IF(o.status IN (0, 3), o.real_money, 0)) as unpaidMoney, " +
        "             sum(IF(o.status IN (1, 2), o.real_money, 0)) as paidMoney " +
        "      from orders o " +
        "               left join wx_user u on o.user_id = u._id " +
        "               left join wx_school s on u.school_id = s._id " +
        condition +
        "      group by s.name) as t " +
        "limit " + skip + ", " + pageSize
    const countSql = "select count(*) as total " +
        "from (select s.name                                       as schoolName," +
        "             u.grade                                      as grade, " +
        "             count(IF(o.status IN (0, 3), o._id, NULL))   as unpaidCount, " +
        "             count(IF(o.status IN (1, 2), o._id, NULL))   as paidCount, " +
        "             sum(IF(o.status IN (0, 3), o.real_money, 0)) as unpaidMoney, " +
        "             sum(IF(o.status IN (1, 2), o.real_money, 0)) as paidMoney " +
        "      from orders o " +
        "               left join wx_user u on o.user_id = u._id " +
        "               left join wx_school s on u.school_id = s._id " +
        condition +
        "       group by s.name, u.grade) as t "
    try {
        // 查询数据sql
        const records = await models.$runSQL(sql, params)
        const count = await models.$runSQL(countSql, params)
        return {
            sql,
            countSql,
            params,
            success: true,
            msg: '查询成功',
            records: records.data.executeResultList,
            total: count.data.executeResultList[0].total
        }
    } catch (error) {
        return {
            success: false,
            msg: '查询失败',
            params,
            sql,
            countSql,
            error: error
        }
    }
}

module.exports = {
    findPaymentPage,
    findPaymentPageBySchool,
}