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

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

/**
 * 
 * 订单管理    表格   调 orders（订单表）
 * 
 *         字段展示   用户名  根据 订单表 user_id 去 家长表（wx_user）里查_id是否相同 相同取园所名称（name）
 *                   规格 —— groupbuy_id  根据 订单表 groupbuy_id 去 规格表（wx_groupbuy_groupbuy）里查_id是否相同 相同取园所名称（spec）
 *                   团购名称 —— merchandise_id  根据 订单表 merchandise_id 去 商品表（wx_merchandise）里查_id是否相同 相同取园所名称（name）
 *                   下单数量 —— num_index
 *                   订单状态（0-待付款 1-待收货 2-已完成 3-已取消） —— status
 *                   收货方式（0-邮寄1-自提） —— eventstarttime
 *                   订单号 —— order_id
 *                   物流单号 —— tracking_number
 *                   修改前地址、 修改前手机号、 修改前收货人 这三字段需要根据 地址id（adresses_id）去 地址表（adresses）里查_id是否相同 相同取园所名称（收货人：name、手机号：phone、地址：address ）
 *                   修改后地址 —— updat_adress
 *                   修改后手机号 —— update_phone
 *                   修改后收货人 —— update_name
 *                   下单时间 —— createdAt
 * 
 *          查询  调 orders（订单表）
 *                merchandise_id —— 团购名称  根据 订单表 merchandise_id 去 商品表（wx_merchandise）里查_id是否相同 相同取园所名称（name）
 *                user_id —— 用户名    根据 订单表 user_id 去 家长表（wx_user）里查_id是否相同 相同取园所名称（name）
 *                status —— 订单状态（0-待付款 1-待收货 2-已完成 3-已取消）
 *                order_id —— 订单号
 *                tracking_number —— 物流单号
 * 
 *       编辑订单  改 orders（订单表） 
 *                修改后姓名 —— update_name
 *                修改后手机号 —— update_phone
 *                修改后地址 —— updat_adress
 *   填写物流单号  改 orders（订单表） 
 *                传 _id、 tracking_number 根据 _id 查到 订单表里面的那条数据里面的 tracking_number 更新这个数据
 * 
 *          删除  调 orders（订单表）
 *                传 _id
 * 
 * 
 */

/**
 * 新增
 * @param {*} order_id 订单Id
 * @param {*} user_id 用户Id
 * @param {*} merchandise_id 商品Id 
 * @param {*} groupbuy_id 规格Id
 * @param {*} num_index 购买数量
 * @param {*} real_money 实际付款金额
 * @param {*} adresses_id 地址id
 * @param {*} tracking_number 物流单号
 * @param {*} update_phone 修改后的手机号
 * @param {*} update_name 修改后的收货人
 * @param {*} updat_adress 修改后地址
 * @param {*} status 0-待付款 1-待收货 2-已完成 3-已取消
 * @returns 
 */
async function addOrder(parameter) {
    const { order_id, user_id, merchandise_id, groupbuy_id, num_index, real_money, adresses_id, tracking_number, update_phone, update_name, updat_adress, status } = parameter
    const v1 = validateField(order_id, 'order_id')
    if (!v1.flag) {
        return {
            success: false,
            msg: v1.msg
        }
    }
    const v2 = validateField(user_id, 'user_id')
    if (!v2.flag) {
        return {
            success: false,
            msg: v2.msg
        }
    }
    const v3 = validateField(merchandise_id, 'merchandise_id')
    if (!v3.flag) {
        return {
            success: false,
            msg: v3.msg
        }
    }
    const v4 = validateField(groupbuy_id, 'groupbuy_id')
    if (!v4.flag) {
        return {
            success: false,
            msg: v4.msg
        }
    }
    const v5 = validateField(num_index, 'num_index')
    if (!v5.flag) {
        return {
            success: false,
            msg: v5.msg
        }
    }
    const v6 = validateField(real_money, 'real_money')
    if (!v6.flag) {
        return {
            success: false,
            msg: v6.msg
        }
    }
    const v7 = validateField(adresses_id, 'adresses_id')
    if (!v7.flag) {
        return {
            success: false,
            msg: v7.msg
        }
    }
    const data = {
        order_id, user_id, merchandise_id, groupbuy_id, num_index, real_money, adresses_id, tracking_number, update_phone, update_name, updat_adress, status: status == null ? 0 : status
    }
    return await handleAdd(parameter, data, 'orders')
}
/**
 * 根据Id删除
 * @param {*} _id 主键Id 
 */
async function deleteOrder(parameter) {
    const { _id } = parameter
    try {
        // 更新数据
        const result = await models.orders.update({
            data: { delete: 1 },
            filter: {
                where: {
                    _id: {
                        $eq: _id
                    }
                }
            }
        })
        return {
            success: true,
            msg: '删除数据成功',
            data: result,
        }
    } catch (error) {
        return {
            success: false,
            msg: '删除数据失败',
            error: error,
            parameter
        }
    }
}
/**
 * 编辑
 * @param {*} _id 主键Id
 * @param {*} order_id 订单号
 * @param {*} user_id 用户Id
 * @param {*} merchandise_id 商品Id 
 * @param {*} groupbuy_id 规格Id
 * @param {*} num_index 购买数量
 * @param {*} real_money 实际付款金额
 * @param {*} adresses_id 地址Id
 * @param {*} tracking_number 物流单号
 * @param {*} update_phone 修改后的手机号
 * @param {*} update_name 修改后的收货人
 * @param {*} updat_adress 修改后地址
 * @param {*} status 订单状态
 * @returns 
 */
async function updateOrder(parameter) {
    const { _id, order_id, user_id, merchandise_id, groupbuy_id, num_index, real_money, adresses_id, tracking_number, update_phone, update_name, updat_adress, status } = parameter
    const obj = {}
    const v0 = validateField(_id, '_id')
    if (!v0.flag) {
        return {
            success: false,
            msg: v0.msg
        }
    }
    const v1 = validateField(order_id, 'order_id')
    if (v1.flag) {
        obj.order_id = order_id
    }
    const v2 = validateField(user_id, 'user_id')
    if (v2.flag) {
        obj.user_id = user_id
    }
    const v3 = validateField(merchandise_id, 'merchandise_id')
    if (v3.flag) {
        obj.merchandise_id = merchandise_id
    }
    const v4 = validateField(groupbuy_id, 'groupbuy_id')
    if (v4.flag) {
        obj.groupbuy_id = groupbuy_id
    }
    const v5 = validateField(num_index, 'num_index')
    if (v5.flag) {
        obj.num_index = num_index
    }
    const v6 = validateField(real_money, 'real_money')
    if (v6.flag) {
        obj.real_money = real_money
    }
    const v7 = validateField(adresses_id, 'adresses_id')
    if (v7.flag) {
        obj.adresses_id = adresses_id
    }
    const v8 = validateField(tracking_number, 'tracking_number')
    if (v8.flag) {
        obj.tracking_number = tracking_number
    }
    const v9 = validateField(update_phone, 'update_phone')
    if (v9.flag) {
        obj.update_phone = update_phone
    }
    const v10 = validateField(update_name, 'update_name')
    if (v10.flag) {
        obj.update_name = update_name
    }
    const v11 = validateField(updat_adress, 'updat_adress')
    if (v11.flag) {
        obj.updat_adress = updat_adress
    }
    const v12 = validateField(status, 'status')
    if (v12.flag) {
        obj.status = status
    }
    return await handleUpdate(parameter, _id, obj, 'orders')
}
/**
 * 分页查询
 * @param {*} name 团购名称
 * @param {*} user_name 用户名
 * @param {*} status 订单状态
 * @param {*} order_id 订单号
 * @param {*} tracking_number 物流单号 
 * @param {*} startTime 开始时间
 * @param {*} endTime 结束时间
 * @param {*} pageNum 当前页数
 * @param {*} pageSize 每页展示数量
 * @returns 
 */
async function findOrderPage(parameter) {
    const { name, user_name, status, order_id, tracking_number, 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 (name != null) {
        condition += " where m.name like {{name}} "
        params.name = '%' + name + '%'
    }
    if (user_name != null) {
        if (condition) {
            condition += " and u.user_name like {{user_name}} "
        } else {
            condition += " where u.user_name like {{user_name}} "
        }
        params.user_name = '%' + user_name + '%'
    }
    if (status != null) {
        if (condition) {
            condition += " and o.status = {{status}} "
        } else {
            condition += " where o.status = {{status}} "
        }
        params.status = status
    }
    if (order_id != null) {
        if (condition) {
            condition += " and o.order_id = {{order_id}} "
        } else {
            condition += " where o.order_id = {{order_id}} "
        }
        params.order_id = order_id
    }
    if (tracking_number != null) {
        if (condition) {
            condition += " and o.tracking_number = {{tracking_number}} "
        } else {
            condition += " where o.tracking_number = {{tracking_number}} "
        }
        params.tracking_number = tracking_number
    }
    if (startTime != null && endTime != null) {
        if (condition) {
            condition += " and o.createdAt >= {{startTime}} and o.createdAt <= {{endTime}} "
        } else {
            condition += " where o.createdAt >= {{startTime}} and o.createdAt <= {{endTime}} "
        }
        params.startTime = startTime
        params.endTime = endTime
    }

    if (condition) {
        condition += " and o.delete = 0"
    } else {
        condition += " where o.delete = 0"
    }

    const skip = (pageNum - 1) * pageSize
    // 查询sql
    const sql = "select " +
        "gg.spec     as spec, " +
        "o._id             as _id, " +
        "u.user_name       as username, " +
        "m.name            as tuangoumingcheng, " +
        "gg.spec           as guige, " +
        "o.num_index       as xiadanshuliang, " +
        "o.status          as dingdanzhuangtai, " +
        "m.way             as shouhuofangshi, " +
        "o.order_id        as dingdanhao, " +
        "o.tracking_number as wuliudanhao, " +
        "addr.address      as addressBefore, " +
        "o.updat_adress    as addressAfter, " +
        "addr.phone        as phoneBefore, " +
        "o.update_phone    as phoneAfter, " +
        "addr.name         as shouhuorenBefore, " +
        "o.update_name     as shouhuorenAfter, " +
        "o.createdAt       as createdAt " +
        "from orders o " +
        "left join wx_user u on o.user_id = u._id " +
        "left join wx_merchandise m on o.merchandise_id = m._id " +
        "left join wx_groupbuy_groupbuy gg on o.groupbuy_id = gg._id " +
        "left join adresses addr on o.adresses_id = addr._id " +
        condition +
        " order by o.createdAt desc " + "limit " + skip + ", " + pageSize
    const countSql = "select count(*) as total from orders o " +
        "left join wx_user u on o.user_id = u._id " +
        "left join wx_merchandise m on o.merchandise_id = m._id " +
        "left join wx_groupbuy_groupbuy gg on o.groupbuy_id = gg._id " +
        "left join adresses addr on o.adresses_id = addr._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 findOrderById(parameter) {
    const { _id } = parameter
    return handleFindByField(parameter, _id, 'orders')
}

module.exports = {
    addOrder,
    deleteOrder,
    updateOrder,
    findOrderPage,
    findOrderById,
}