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

const { models } = require('../db')
const { validateField, handleAdd, handleUpdate, handleFindByField, handleDelete } = require('../common')

/**
 * 
 * 团购管理    表格   调 wx_merchandise（团购表）
 * 
 *         字段展示   学校名称  根据 团购表 里面的school_id 去 园所表（wx_school）里查_id是否相同 相同取园所名称（name）
 *                   封面 —— img
 *                   团购名称 —— name
 *                   已拼数量 —— count  
 *                   浏览量 —— browse
 *                   活动开始时间 —— eventstarttime
 *                   活动结束时间 —— eventendstime
 *                   收货方式（0-邮寄1-自提） —— way
 *                   团购状态（0-进行中 1-即将结束 2-已结束） —— groupbuy_state
 * 
 *          查询  调 wx_merchandise（团购表）
 *                school_id —— 园所_id  园所是下拉  调 wx_school（园所表）
 *                name —— 团购名称
 *                groupbuy_state —— 团购状态（0-进行中 1-即将结束 2-已结束）
 * 
 *   新增编辑团购  新增 wx_merchandise（团购表） 
 *                园所id —— school_id
 *                团购名称 —— name
 *                封面 —— img
 *                收货方式（0-邮寄1-自提） —— way
 *                编辑多个 _id
 * 
 *          删除  调 wx_merchandise（团购表）
 *                传 _id
 *                同时调 调 wx_groupbuy_groupbuy（团购规格表）
 *                传 groupbuy_id 相同的全删
 * 
 *      二层表格   调 wx_groupbuy_groupbuy（团购规格表）
 *      
 *      字段展示   详情图 —— detail_images
 *                产品详情 —— details
 *                规格 —— count
 *                原价 —— old_price
 *                价格 —— price
 * 
 *  二层新增编辑团购   商品_id —— groupbuy_id
 *                    详情图 —— detail_images （数组类型）
 *                    产品详情 —— details
 *                    规格 —— spec
 *                    原价 —— old_price
 *                    价格 —— price
 *                    编辑多个 _id
 * 
 *              删除  调 wx_groupbuy_groupbuy（团购规格表）
 *                    传 _id
 * 
 * 
 * 
 */

async function findGroupBuyPageOld(parameter) {
    const { school_id, groupbuy_name, groupbuy_state, 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 = {}
    // 拼接查询条件
    let condition = ""
    if (school_id != null) {
        condition += " where m.school_id = {{school_id}} "
        params.school_id = school_id
    }
    if (groupbuy_name != null) {
        if (condition) {
            condition += " and m.name like {{groupbuy_name}} "
        } else {
            condition += " where m.name like {{groupbuy_name}} "
        }
        params.groupbuy_name = groupbuy_name
    }
    if (groupbuy_state != null) {
        if (condition) {
            condition += " and m.groupbuy_state = {{groupbuy_state}} "
        } else {
            condition += " where m.groupbuy_state = {{groupbuy_state}} "
        }
        params.groupbuy_state = groupbuy_state
    }
    if (startTime != null && endTime != null) {
        if (condition) {
            condition += " and m.eventstarttime >= {{startTime}} and m.eventendstime <= {{endTime}} "
        } else {
            condition += " where m.eventstarttime >= {{startTime}} and m.eventendstime <= {{endTime}} "
        }
        params.startTime = startTime
        params.endTime = endTime
    }

    const skip = (pageNum - 1) * pageSize
    // 查询sql
    const sql = "select s.name as schoolName, " +
        "m.img as fengmian, " +
        "m.name as tuangouName, " +
        // 已拼数量
        // 浏览量
        "m.eventstarttime as kaishishijian, " +
        "m.eventendstime as jieshushijian, " +
        "m.way as shouhuofangshi, " +
        "m.groupbuy_state as tuangouzhuangtai " +
        "from wx_merchandise m " +
        "left join wx_school s on m.school_id = s._id " +
        condition +
        " order by m.createdAt " + "limit " + skip + ", " + pageSize

    // 查询数据sql
    const records = await models.$runSQL(sql, params)
    return {
        sql,
        params: { ...params },
        success: true,
        msg: '查询成功',
        records: records.data.executeResultList,
    }
}
async function findGroupBuyCountOld(parameter) {
    const { school_id, groupbuy_name, groupbuy_state, startTime, endTime } = parameter
    // 查询参数
    const params = {}
    // 拼接查询条件
    let condition = ""
    if (school_id != null) {
        condition += " where m.school_id = {{school_id}} "
        params.school_id = school_id
    }
    if (groupbuy_name != null) {
        if (condition) {
            condition += " and m.name like {{groupbuy_name}} "
        } else {
            condition += " where m.name like {{groupbuy_name}} "
        }
        params.groupbuy_name = groupbuy_name
    }
    if (groupbuy_state != null) {
        if (condition) {
            condition += " and m.groupbuy_state = {{groupbuy_state}} "
        } else {
            condition += " where m.groupbuy_state = {{groupbuy_state}} "
        }
        params.groupbuy_state = groupbuy_state
    }
    if (startTime != null && endTime != null) {
        if (condition) {
            condition += " and m.eventstarttime >= {{startTime}} and m.eventendstime <= {{endTime}} "
        } else {
            condition += " where m.eventstarttime >= {{startTime}} and m.eventendstime <= {{endTime}} "
        }
        params.startTime = startTime
        params.endTime = endTime
    }
    // 查询总数sql
    const sql = "select count(*) as total from wx_merchandise m " +
        "left join wx_school s on m.school_id = s._id " +
        condition

    const count = await models.$runSQL(sql, params)
    return {
        sql,
        success: true,
        msg: '查询成功',
        total: count.data.executeResultList[0].total
    }
}
/**
 * 分页查询团购数据
 * @param {*} school_id 学校Id
 * @param {*} groupbuy_name 团购名称
 * @param {*} groupbuy_state 团购状态
 * @param {*} startTime 开始时间
 * @param {*} endTime 结束时间
 * @param {*} pageNum 当前页
 * @param {*} pageSize 每页展示数量
 * @returns 分页数据
 */
async function findGroupBuyPage(parameter) {
    const { school_id, groupbuy_name, groupbuy_state, 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 m.school_id = {{school_id}} "
        params.school_id = school_id
    }
    if (groupbuy_name != null) {
        if (condition) {
            condition += " and m.name like {{groupbuy_name}} "
        } else {
            condition += " where m.name like {{groupbuy_name}} "
        }
        params.groupbuy_name = groupbuy_name
    }
    if (groupbuy_state != null) {
        if (condition) {
            condition += " and m.groupbuy_state = {{groupbuy_state}} "
        } else {
            condition += " where m.groupbuy_state = {{groupbuy_state}} "
        }
        params.groupbuy_state = groupbuy_state
    }
    if (startTime != null && endTime != null) {
        if (condition) {
            condition += " and m.eventstarttime >= {{startTime}} and m.eventendstime <= {{endTime}} "
        } else {
            condition += " where m.eventstarttime >= {{startTime}} and m.eventendstime <= {{endTime}} "
        }
        params.startTime = startTime
        params.endTime = endTime
    }

    const skip = (pageNum - 1) * pageSize
    // 查询sql
    const sql = "select m._id as id, " +
        "s.name as schoolName, " +
        "m.img as fengmian, " +
        "m.name as tuangouName, " +
        "m.browse as browse, " +
        "m.count as count, " +
        "m.school_id      as school_id, " +
        "m.tag_id         as tag_id, " +
        "m.eventstarttime as kaishishijian, " +
        "m.eventendstime as jieshushijian, " +
        "m.way as shouhuofangshi, " +
        "m.groupbuy_state as tuangouzhuangtai " +
        "from wx_merchandise m " +
        "left join wx_school s on m.school_id = s._id " +
        condition +
        " order by m.createdAt " + "limit " + skip + ", " + pageSize
    const countSql = "select count(*) as total from wx_merchandise m " + "left join wx_school s on m.school_id = s._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
        }
    }
}
/**
 * 新增团购
 * @param {*} school_id 园所Id
 * @param {*} name 团购名称
 * @param {*} img 封面
 * @param {*} startTime 开始时间按
 * @param {*} endTime 结束时间
 * @param {*} way 收货方式
 * @returns 
 */
async function addGroupBuy(parameter) {
    const { schoolId, name, img, startTime, endTime, way, tagId } = parameter
    const v1 = validateField(schoolId)
    if (!v1.flag) {
        return {
            success: false,
            msg: v1.msg
        }
    }
    const v2 = validateField(name)
    if (!v2.flag) {
        return {
            success: false,
            msg: v2.msg
        }
    }
    const v3 = validateField(img)
    if (!v3.flag) {
        return {
            success: false,
            msg: v3.msg
        }
    }
    const v4 = validateField(startTime)
    if (!v4.flag) {
        return {
            flag: false,
            msg: v4.msg
        }
    }
    const v5 = validateField(endTime)
    if (!v5.flag) {
        return {
            flag: false,
            msg: v5.msg
        }
    }
    const v6 = validateField(way)
    if (!v6.flag) {
        return {
            success: false,
            msg: v6.msg
        }
    }
    const v7 = validateField(tagId)
    if (!v7.flag) {
        return {
            success: false,
            msg: v7.msg
        }
    }

    const data = {
        school_id: schoolId,
        name: name,
        img: img,
        eventstarttime: startTime,
        eventendstime: endTime,
        way: way,
        tag_id: tagId,
        count: 0,
        browse: 0,
    }
    return await handleAdd(parameter, data, 'wx_merchandise')
}
/**
 * 编辑团购
 * @param {*} school_id 园所Id
 * @param {*} name 团购名称
 * @param {*} img 团购封面
 * @param {*} way 收货方式（0-邮寄1-自提）
 * @param {*} id 团购Id
 * @returns 是否成功
 */
async function updateGroupBuy(parameter) {
    const { id, schoolId, name, img, startTime, endTime, way, tagId } = parameter
    const obj = {}
    const v0 = validateField(id)
    if (!v0.flag) {
        return {
            success: false,
            records: [],
            msg: v0.msg
        }
    }
    const v1 = validateField(schoolId)
    if (v1.flag) {
        obj.school_id = schoolId
    }
    const v2 = validateField(name)
    if (v2.flag) {
        obj.name = name
    }
    const v3 = validateField(img)
    if (v3.flag) {
        obj.img = img
    }
    const v4 = validateField(startTime)
    if (v4.flag) {
        obj.eventstarttime = startTime
    }
    const v5 = validateField(endTime)
    if (v5.flag) {
        obj.eventendstime = endTime
    }
    const v6 = validateField(way)
    if (v6.flag) {
        obj.way = way
    }
    const v9 = validateField(tagId)
    if (v9.flag) {
        obj.tag_id = tagId
    }

    return await handleUpdate(parameter, id, obj, 'wx_merchandise')

}
/**
 * 删除团购
 * @param {*} id 团购Id
 * @returns 是否成功
 */
async function deleteGroupBuy(parameter) {
    const { id } = parameter
    try {
        // 删除商品表数据
        const result1 = await models.wx_merchandise.delete({
            filter: {
                where: {
                    $and: [
                        {
                            _id: {
                                $eq: id,
                            },
                        },
                    ]
                }
            },
            envType: "prod",
        })
        // 删除商品规格表数据
        const result2 = await models.wx_groupbuy_groupbuy.delete({
            filter: {
                where: {
                    $and: [
                        {
                            groupbuy_id: {
                                $eq: id,
                            },
                        },
                    ]
                }
            },
            envType: "prod",
        })
        return {
            success: true,
            msg: '删除数据成功',
            data: {
                shangpincount: result1.data.count,
                shangpinguigecount: result2.data.count
            },
        }
    } catch (error) {
        return {
            success: false,
            msg: '删除数据失败',
            error: error,
            parameter
        }
    }
}
/**
 * 根据团购Id查询规格
 * @param {*} tuangou_id 团购Id
 * @returns 团购规格数据List
 */
async function findguigeBytuangouId(parameter) {
    const { tuangou_id } = parameter
    // const sql = "select _id, price, old_price, spec, details, detail_images, groupbuy_id from wx_groupbuy_groupbuy where groupbuy_id = {{tuangou_id}}"
    // const params = {
    //     tuangou_id
    // }
    // const records = await models.$runSQL(sql, params)
    const select = {
        "_id": true,
        "price": true,
        "old_price": true,
        "spec": true,
        "details": true,
        "detail_images": true,
        "groupbuy_id": true
    }
    return await handleFindByField(select, 'groupbuy_id', tuangou_id, 'wx_groupbuy_groupbuy')
}
/**
 * 新增商品规格
 * @param {*} price 价格
 * @param {*} old_price 原价
 * @param {*} spec 规格名称
 * @param {*} details 商品详情
 * @param {*} detail_images 商品图片(数组) 
 * @param {*} groupbuy_id 商品Id
 * @returns 新增结果
 */
async function addguige(parameter) {
    const { price, old_price, spec, details, detail_images, groupbuy_id } = parameter
    const v1 = validateField(price)
    if (!v1.flag) {
        return {
            success: false,
            msg: v1.msg
        }
    }
    const v2 = validateField(old_price)
    if (!v2.flag) {
        return {
            success: false,
            msg: v2.msg
        }
    }
    const v3 = validateField(spec)
    if (!v3.flag) {
        return {
            success: false,
            msg: v3.msg
        }
    }
    const v4 = validateField(groupbuy_id)
    if (!v4.flag) {
        return {
            success: false,
            msg: v4.msg
        }
    }
    const data = {
        price,
        old_price,
        spec,
        details,
        detail_images,
        groupbuy_id
    }
    return await handleAdd(parameter, data, 'wx_groupbuy_groupbuy')
}
/**
 * 编辑商品规格
 * @param {*} id 规格Id
 * @param {*} price 价格
 * @param {*} old_price 原价
 * @param {*} spec 规格名称
 * @param {*} details 商品详情
 * @param {*} detail_images 商品图片(数组) 
 * @param {*} groupbuy_id 商品Id
 * @returns 编辑结果
 */
async function updateguigeById(parameter) {
    const { id, price, old_price, spec, details, detail_images, groupbuy_id } = parameter
    const obj = {
        details,
        detail_images,
    }
    const v0 = validateField(id)
    if (!v0.flag) {
        return {
            success: false,
            msg: v0.msg
        }
    }
    const v1 = validateField(price)
    if (v1.flag) {
        obj.price = price
    }
    const v2 = validateField(old_price)
    if (v2.flag) {
        obj.old_price = old_price
    }
    const v3 = validateField(spec)
    if (v3.flag) {
        obj.spec = spec
    }
    const v4 = validateField(groupbuy_id)
    if (v4.flag) {
        obj.old_price = groupbuy_id
    }
    return await handleUpdate(parameter, id, obj, 'wx_groupbuy_groupbuy')
}

/**
 * 根据Id删除商品规格
 * @param {*} id 规格Id 
 */
async function deleteguigeById(parameter) {
    const { id } = parameter
    return handleDelete(parameter, id, 'wx_groupbuy_groupbuy')
}

module.exports = {
    findGroupBuyPage,
    addGroupBuy,
    updateGroupBuy,
    deleteGroupBuy,
    findguigeBytuangouId,
    addguige,
    updateguigeById,
    deleteguigeById
};