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

const { models } = require('../db')
const { validateField, handleAdd, handleUpdate, handleDelete, handleFindByField } = require('../common')
/**
 * 
 * 下载量统计 表格   调 microcode（访问下载量统计）
 * 
 *         字段展示   园所 根据 售后管理表 school_id 去 学校表（wx_school）里查_id是否相同 相同取园所名称（name）
 *                   课件名 —— 根据 售后管理表 file_id 去 文件表（file_manage）里查_id是否相同 相同取园所名称（name）
 *                   下载量 —— download
 *                   创建时间 —— createdAt
 * 
 *          查询  调 microcode（访问下载量统计）
 *                  school_id —— 园所_id  园所是下拉  调 wx_school（园所表）
 *                  createdAt —— 创建时间
 * 
 * 
 */
/**
 * 新增访问下载量统计
 * @param {*} file_id 课件Id
 * @param {*} school_id 学校Id
 * @returns 
 */
async function addMicrocode(parameter) {
    const { file_id, school_id } = parameter
    const v1 = validateField(school_id, 'school_id')
    if (!v1.flag) {
        return {
            success: false,
            msg: v1.msg
        }
    }
    const v2 = validateField(file_id, 'file_id')
    if (!v2.flag) {
        return {
            success: false,
            msg: v2.msg
        }
    }

    const data = {
        school_id,
        file_id,
        visits: 0,
        download: 0,
    }
    return await handleAdd(parameter, data, 'microcode')
}
/**
 * 根据Id删除
 * @param {*} _id Id 
 */
async function deleteMicrocode(parameter) {
    const { _id } = parameter
    return handleDelete(parameter, _id, 'microcode')
}
/**
 * 编辑
 * @param {*} _id 主键Id
 * @param {*} file_id 课件Id
 * @param {*} school_id 学校Id
 * @returns 
 */
async function updateMicrocode(parameter) {
    const { _id, school_id, file_id } = parameter
    const obj = {}
    const v0 = validateField(_id, '_id')
    if (!v0.flag) {
        return {
            success: false,
            msg: v0.msg
        }
    }
    const v1 = validateField(school_id, 'school_id')
    if (v1.flag) {
        obj.school_id = school_id
    }
    const v2 = validateField(file_id, 'file_id')
    if (v2.flag) {
        obj.file_id = file_id
    }
    return await handleUpdate(parameter, _id, obj, 'microcode')
}
/**
 * 分页查询
 * @param {*} school_id 学校Id
 * @param {*} startTime 开始时间
 * @param {*} endTime 结束时间
 * @param {*} pageNum 当前页面
 * @param {*} pageSize 每页展示数量
 * @returns 分页数据
 */
async function findMicrocodePage(parameter) {
    const { school_id, startTime, endTime, 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 (startTime != null && endTime != null) {
        if (condition) {
            condition += " and fm.createdAt >= {{startTime}} and fm.createdAt <= {{endTime}} "
        } else {
            condition += " where fm.createdAt >= {{startTime}} and fm.createdAt <= {{endTime}} "
        }
        params.startTime = startTime
        params.endTime = endTime
    }

    const skip = (pageNum - 1) * pageSize
    // 查询sql
    const sql = "select " +
        "s.name     as schoolName, " +
        "fm.name    as fileName, " +
        "m.download as download, " +
        "m.visits   as visits, " +
        "fm.createdAt as createdAt " +
        "from microcode m " +
        "left join wx_school s on m.school_id = s._id " +
        "left join file_manage fm on m.file_id = fm._id " +
        condition +
        " order by fm.createdAt desc " + "limit " + skip + ", " + pageSize
    const countSql = "select count(*) as total from microcode m " +
        "left join wx_school s on m.school_id = s._id " +
        "left join file_manage fm on m.file_id = fm._id " +
        condition
    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
        }
    }
}
/**
 * 根据Id查询
 * @param {*} _id Id 
 */
async function findMicrocodeById(parameter) {
    const { _id } = parameter
    return handleFindByField(parameter, _id, 'microcode')
}

module.exports = {
    addMicrocode,
    deleteMicrocode,
    updateMicrocode,
    findMicrocodePage,
    findMicrocodeById,
}