• VUE+Nodejs 商城项目练习项目（Node部分）

    文章目录

    项目结构

    在这里插入图片描述

    数据库设计

    总共是12张表，但是sp_category这块没有做，所以暂时用不到，activities对应的是前台部分的那些促销活动，channel对应的是购物首页右侧导航栏中的数据，goods_pic用来存储上传的商品图片的地址
    在这里插入图片描述

    登录部分

    1. 判断用户帐号/手机号/邮箱在数据库中是否存在，由于购物部分和后台管理部分用的是同一张表，因此在登录的时候需要判断一下用户的角色
      select * from user where (username = ? or phone = ? or email = ? ) and roleId in (4,5)
    2. 对数据库中的密码进行解密并于输入的密码进行对比
      bcrypt.compareSync(userinfo.password,result[0].password)
    3. 生成token
      const tokenStr = jwt.sign({ username: result[0].nickName,id:result[0].id,avator:result[0].userPic }, config.jwtSecretKey, { expiresIn: '30h' })
    4. 将token返回
    exports.login = (req, res) => {
    let userinfo = req.body
    // 用户名是否存在验证
    db.query("select * from user where username =? or phone = ? or email = ? and roleId <> 4 and roleId <> 5 and mg_state = 1",[userinfo.username,userinfo.username,userinfo.username],function(err,result){
        if(err){
            return res.cc(err)
        }
        if(result.length != 1) return res.cc('用户名不存在！','-1')
        if(!bcrypt.compareSync(userinfo.password,result[0].password)){
            return res.cc('密码错误，请重新输入','-1')
        }
        const tokenStr = jwt.sign({ username: result[0].nickName,id:result[0].id,avator:result[0].userPic }, config.jwtSecretKey, { expiresIn: '300h' })
        res.cookie("token",tokenStr).send({
            code: 200,
            msg: '登录成功！',
            // 为了方便客户端使用 Token，在服务器端直接拼接上 Bearer 的前缀
            token: 'Bearer ' + tokenStr,

          })
    })
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    注册部分

    1. 检查数据表中是否已经存在该用户，这边检测的是随机生成的用户帐号是否有重复
    2. 对用户输入的密码进行加密
      userinfo.password = bcrypt.hashSync(userinfo.password, 10)
    3. 向数据表中插入用户数据
    exports.register = (req, res) => {
    // 接收表单数据
    const userinfo = req.body
    userinfo['username'] = getRandomString(10);
    userinfo['createTime'] = today
    userinfo['is_delete'] = 0
    userinfo['roleId'] = 4    // 注册用户默认为普通用户
    userinfo['userPic']='user.png' // 设置默认头像
    db.query('select * from user where username = ?',userinfo.username,function(err,result){
        if(err){
            return res.cc(err)
        }
        if(result.length > 0){
            return res.cc("用户名已被占用，请更换其他用户名！",-1)
        }
    })
    //对用户密码进行加密
    userinfo.password = bcrypt.hashSync(userinfo.password, 10)
    // 插入新用户
    db.query("insert into user set ?",userinfo,function(err,result){
        if(err){
            return res.cc(err)
        }
        if(result.affectedRows != 1){
            return res.cc("注册用户失败，请稍后重试！")
        }
        return res.cc("新增用户成功",200)
    })
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    随机生成字符串函数

    /**
 * 随机生成字符串
 * @param len 指定生成字符串长度
 */
 function getRandomString(len){
    let _charStr = 'abacdefghjklmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789',
        min = 0, 
        max = _charStr.length-1, 
        _str = '';                    //定义随机字符串 变量
    //判断是否指定长度，否则默认长度为15
    len = len || 15;
    //循环生成字符串
    for(var i = 0, index; i < len; i++){
        index = (function(randomIndexFunc, i){         
                    return randomIndexFunc(min, max, i, randomIndexFunc);
                })(function(min, max, i, _self){
                    let indexTemp = Math.floor(Math.random()*(max-min+1)+min),
                        numStart = _charStr.length - 10;
                    if(i==0&&indexTemp >=numStart){
                        indexTemp = _self(min, max, i, _self);
                    }
                    return indexTemp ;
                }, i);
        _str += _charStr[index];
    }
    return _str;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    获取token中的用户信息

    exports.getUserInfo = (req,res)=>{
    // console.log(req);
    return res.send({
        code: 200,
        msg: '请求成功!',
        data: {
          username: req.user.username, //这里的username就来自于jwt字符串的解析,
          id:req.user.id,
          avator:req.user.avator
        }
      })
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    购物页面

    获取首页右侧导航数据

    在这里插入图片描述
    数据表
    在这里插入图片描述

    • channel_level 为 0 表示是图片部分数据（即鼠标未移入时的状态）,channel_pid为0说明是大类如数码，美食等
    • channel_level为1表示鼠标移入以后显示部分的大类,如下图所示，channel_level为2则表示移入后显示的小类如针织衫，开衫,T恤等
      在这里插入图片描述
        getAllChannel:(req,res)=>{  
        db.query("select channelName,channel_id,channel_pid,channel_level,icon from channel where channel_level = 0;select channelName,channel_id,channel_pid ,channel_level from channel where channel_level != 0",function(err,result){
            if(err) return res.cc(err)
            // let data = lodash.keyBy(result, 'channel_pid')
            // console.log(result);
            let data = {}
            for(let i = 0 ;i < result[0].length;i++){
                
                if(result[0][i].channel_pid == 0){
                    data[result[0][i].channel_id]= {
                        category:result[0][i].channelName,
                        icon:result[0][i].icon,
                        channel_simple:[],
                        channem_detail:[]
                    }
                }
                else if(data.hasOwnProperty(result[0][i].channel_pid)){
                    data[result[0][i].channel_pid].channel_simple.push(result[0][i].channelName)
                    // temp[result[i].channel_pid].channel_simple = lodash.values(lodash.keyBy(result[i],'channelName'))
                }
                
            }
            let temp = {}
            for(let j = 0; j < result[1].length;j++){
                if(result[1][j].channel_level == 1 && !temp.hasOwnProperty(result[1][j].channel_pid)){
                    temp[result[1][j].channel_id] = {
                        main:result[1][j].channelName,
                        pid:result[1][j].channel_pid,
                        goods:[]
    
                    }
                }else if(temp.hasOwnProperty(result[1][j].channel_pid)){
                    temp[result[1][j].channel_pid].goods.push(result[1][j].channelName)
                }
                
            }
            temp = lodash.values(temp)
            temp.forEach(item => {
                data[item.pid].channem_detail.push(item)
                
            });
           res.send({
            code:200,
            msg:"获取列表数据成功！",
            data:lodash.values(data)
           })

        })

    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    获取商品列表

    在这里插入图片描述

        getGoodsList:(req,res)=>{
        let page = req.body.page
        let limit = req.body.limit
        let start = parseInt((page - 1 )*limit)
        let end = page*limit
        db.query('select goods.id as goodId,goods.shopId, goods.goodName ,goods.keyWord,goods.price as priceNow,goods.priceAgo ,goods.saleCount,goods_pic.picName,shopId,shop.shopName from goods,goods_pic,shop where goods.shopId = shop.Id and goods_pic.id in( goods.fileId)  limit ? ,? ',[start,end],function(err,result){
            if(err) return res.cc(err)
            res.send({
                code:200,
                msg:"获取商品列表成功！",
                data:[...result]
            })
        })
    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    获取活动列表

    在这里插入图片描述

        getActivity:(req,res)=>{
        db.query("select activityName as tagContent, activitySource as tagName , activityType as tagType from activities where startTime<= ? and endTime >= ?",[today,today],function(err,result){
            if(err) return res.cc(err)
            return res.send({
                code:200,
                msg:"获取活动列表成功",
                data:[...result]
            })
        })
    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    获取用户购物车列表
        getUserCart:(req,res)=>{
        let userId = req.body.id
        db.query("select  user_cart.shopId ,goods.id as id,(user_cart.count * goods.price) as sum,goods_pic.picName as imgSrc,user_cart.count as number ,shop.shopName,goods.goodName as title,goods.keyWord as type,goods.model,goods.price as nowPrice,goods.priceAgo as originalPrice from user_cart,shop,goods,goods_pic where userId = ? and shop.id = user_cart.shopId and goods.id in (user_cart.goodsId) and goods.fileId = goods_pic.id and user_cart.is_delete = 0  ",userId,function(err,result){
            if(err)  return res.cc(err)
            let dataList = _.groupBy(result,'shopName')
            let resultList=[]
            for(item in dataList){
                dataList[item].isChecked = false
                let temp = {
                    'shopName':item,
                    'isChecked':false,
                    'id':dataList[item][0].shopId,
                    items:dataList[item]
                }
                resultList.push(temp)
            }
            res.send({
                code:200,
                msg:"获取购物车列表成功！",
                data:resultList
            })
        })
    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    添加到购物车
    1. 根据店铺ID，用户ID以及商品的ID来查询购物车中商品的数量
    2. 如果购物车中已经存在该物品，则需要更新物品的数量
    3. 如果购物车中还没有此物品，则需要插入该物品信息
        addCartItem:(req,res)=>{
        let goodId = req.body.goodId
        let shopId = req.body.shopId
        let userId = req.body.userId
        db.query("select count from user_cart where userId =? and goodsId = ? and shopId =?",[userId,goodId,shopId],function(err,result){
            if(err) return res.cc(err)
            if(result.length == 0){
                db.query("insert into user_cart(userId,goodsId,shopId,count) values (?,?,?,1)",[userId,goodId,shopId],function(err,result){
                    if(err) return res.cc(err)
                    return res.cc("添加购物车成功！",200)
                })
            }else{
                let count = result[0].count
                count+=1
                db.query("update user_cart set  count = ? where userId =? and goodsId = ? and shopId =?",[count,userId,goodId,shopId],function(err,result){
                    if(err) return res.cc(err)
                    return res.cc("添加购物车成功！",200)
                })
            }
        })
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    提交订单
    1. 考虑到一家店添加多个商品，需要往订单表中批量插入多条数据
    2. 提交订单后，用户的购物车中的对应商品信息应该删除，因此需要批量的更新购物车表中的数据
    3. 随机生成订单的单号
    4. 插入订单数据之后，需要对商品的销量进行更新
        // 创建订单
    createOrder: (req, res) => {
        let tableData = req.body.info
        let userId = req.body.userId
        let str = []
        let updateStr = []
        let orderNum = orderCode()
        tableData.forEach(e => {
            str.push(`('${orderNum}',${e.id},${userId},${e.shopId},${e.number},${e.sum},'${now}',${e.order_sumup})`)
            updateStr.push(`(${userId},${e.shopId},${e.id})`)
        });
        str = str.join(",")
        updateStr.join(',')
        db.query(`insert into user_order(orderNum,goodId,userId,shopId,number,sum,createTime,order_sumup) values ${str}`, function (err, result) {
            if (err) return res.cc(err)
            db.query(`update user_cart set is_delete = 1 where (userId,shopId,goodsId) in (${updateStr}) `, function (err, result) {
                if (err) res.cc(err)
                updateGoodsSale()
                return res.cc("创建订单成功！", 200)
            })

        })
    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    随机生成订单单号函数

    // 随机生成订单号：时间戳+6位随机数
function orderCode() {
    var orderCode = '';
    for (var i = 0; i < 6; i++) //6位随机数，用以加在时间戳后面。
    {
        orderCode += Math.floor(Math.random() * 10);
    }
    orderCode = new Date().getTime() + orderCode; //时间戳，用来生成订单号。
    return orderCode;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    更新商品销量的函数

    // 更新商品总销量
function updateGoodsSale() {
    db.query("select sum(number) as saleCount ,shopId,goodId from user_order group by goodId,shopId;", function (err, result) {
        if (err) return res.cc(err)
        let data = _.groupBy(result, function (n) {
            return n.shopId + "," + n.goodId;
        })
        let str = `update goods set saleCount =  case  `
        let obj = []
        for (item in data) {
            let temp = item.split(',')
            str += `when shopId = ${temp[0]} and id = ${temp[1]} then ${data[item][0].saleCount} `
            obj.push(`(${item})`)
        }
        str += `end where (shopId,id) in (${obj.join(",")})`
    })
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    获取我的订单
        getUserOrderList: (req, res) => {
        let userId = req.body.id
        db.query("select a.id,a.shopId,a.orderNum,a.number,a.createTime,a.order_status,a.is_pay,a.is_deliver,a.order_sumup,b.goodName,b.priceAgo,b.model,b.price,c.shopName,d.picName from user_order a,goods b,shop c,goods_pic d where userId = ? and a.goodId = b.id and a.shopId = c.id and b.fileId = d.id and a.is_delete = 0 order by a.order_status,a.createTime desc", userId, function (err, result) {
            if (err) return res.cc(err)
            let data = []
            let resByOrderNum = _.groupBy(result, 'orderNum')
            for (item in resByOrderNum) {

                let orderByShop = _.groupBy(resByOrderNum[item], 'shopName')
                for (i2 in orderByShop) {
                    let temp = {
                        'id': resByOrderNum[item][0].id,
                        'orderNum': item,
                        'shopId': orderByShop[i2][0].shopId,
                        'is_pay': orderByShop[i2][0].is_pay,
                        'is_deliver': orderByShop[i2][0].is_deliver,
                        'createTime': resByOrderNum[item][0].createTime.split(" ")[0],
                        'shopName': i2,
                        'order_status': resByOrderNum[item][0].order_status,
                        'list': orderByShop[i2]
                    }
                    data.push(temp)
                }

            }
            return res.send({
                code: 200,
                msg: '获取用户订单成功',
                data: data
            })
        })
    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    延长收货时间
        // 延长收货时间
    delayTime: (req, res) => {
        let id = req.body.id
        db.query('select finishTime from user_order where id = ?', id, function (err, result) {
            if (err) return res.cc(err)
            let finishTime = delayTimes(result[0].finishTime, 15)
            db.query('update user_order set finishTime = ? where id = ?', [finishTime, id], function (err, result) {
                if (err) return res.cc(err.msg)
                if (result.affectedRows !== 1) return res.cc("延长收货时间失败！")
                return res.cc("延长收货时间成功！", 200)
            })
        })

    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    计算延长时间函数

    function delayTimes(data, delay) {
    console.log(data);
    data = new Date(data)
    let year = data.getFullYear()
    let month = data.getMonth() + 1
    let day = data.getDate() + delay
    //小月
    if (month == 4 || month == 6 || month == 9 || month == 11) {
        if (day > 30) {
            day -= 30
            month += 1
        }
    }
    //2月
    else if (month == 2) {
        if (isLeapYear(date.getFullYear())) {
            if (day > 29) {
                day -= 29
                month + 1
            } else {
                day -= 28
                month += 1
            }
        }
        if (day > 28) {
            day -= 28
            month += 1
        }
    }
    //大月
    else {
        if (day > 31) {
            day -= 31
            month += 1
        }
    }
    return year + '-' + month + '-' + day
}
//JS判断闰年代码
function isLeapYear(Year) {
    if (((Year % 4) == 0) && ((Year % 100) != 0) || ((Year % 400) == 0)) {
        return (true);
    } else {
        return (false);
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    后台管理

    统计今日用户增长情况
            getUserLine: (req, res) => {
            let str1 = `SELECT
        IFNULL(SUM(CASE HOUR(createTime) WHEN 0 THEN 1 ELSE 0 END),0)as '0',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 1 THEN 1 ELSE 0 END),0)as '1',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
        FROM user
        WHERE date(createTime) >= CURDATE() and date(createTime) <= CURDATE();
        `
            let str2 = `SELECT

        IFNULL(SUM(CASE HOUR(createTime) WHEN 0 THEN 1 ELSE 0 END),0)as '0',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 1 THEN 1 ELSE 0 END),0)as '1',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
        
        FROM user
        
        WHERE date(createTime) >= date_sub(CURDATE() , INTERVAL 1 DAY) AND date(createTime) < CURDATE()
        `
        db.query(str1, function (err, result) {
            if (err) return res.cc(err)
            let data = {
                today: _.values(result[0]),
                category: _.keys(result[0])
            }
            db.query(str2, function (err, result2) {
                if (err) return res.cc(err)
                data['yesterday'] = _.values(result2[0])
                // conn.sendText(JSON.stringify({
                //     code: 200,
                //     msg: "获取数据成功！",
                //     data: data
                // }))
                return res.send({
                    code:200,
                    msg:"获取数据成功！",
                    data:data
                })
            })
        })
            }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    获取商品列表
        // 获取商品列表
    getGoods: (req, res) => {
        const param = req.body
        let end = param.limit
        let start = (param.page - 1) * end

        let sql = 'select goods.*,brand.brandName as brand,shop.shopName from goods,brand,shop where 1 =1 and goods.brand = brand.id and goods.shopId = shop.id'
        let arr = []
        if (param.goodName != "" && param.goodName != undefined) {
            let goodName = '%' + param.goodName + '%';
            sql += ' and goodName like ?'
            arr.push(goodName)
        }
        if (param.brand != null && param.brand != "") {
            sql += ' and brand = ?'
            arr.push(param.brand)
        }
        sql += ' and is_delete = 0 order by createTime desc limit ?,? ;select count(*) as total from goods'
        arr.push(start, parseInt(end))
        db.query(sql, arr, function (err, result) {
            if (err) return res.cc(err)
            return res.send({
                code: 200,
                msg: '获取商品列表成功！',
                count: result[1][0].total,
                data: [...result[0]]
            })
        })
    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    上传商品图片
        // 上传图片
    uploadImg: (req, res) => {
        var filePath = './' + req.files[0].path;
        // 文件类型
        var temp = req.files[0].originalname.split('.');
        var fileType = temp[temp.length - 1];
        var lastName = '.' + fileType;
        // 构建图片名
        var fileName = Date.now() + lastName;
        // 图片重命名
        fs.rename(filePath, './images/' + fileName, async (err) => {
            if (err) {
                return ''
            } else {
                var localFile = './images/' + fileName;
                var key = fileName
                let param = {
                    'pic_url': localFile,
                    'picName': key
                }
                let str = `insert into goods_pic  set ?`
                let str2 = `select id,picName from goods_pic order by id desc limit  1`
                db.query(str, param, function (err, result) {
                    if (err) return res.cc(err)
                    if (result.affectedRows !== 1) return res.cc("上传图片失败")
                    db.query(str2, function (err, result) {
                        if (err) return res.cc(err)
                        return res.send({
                            code: 200,
                            msg: "上传图片成功",
                            data: {
                                id: result[0].id,
                                url: result[0].picName
                            }
                        })
                    })
                })
            }
        });
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    分页获取角色及其权限

    在这里插入图片描述

        getRoleByPage: (req, res) => {
        let queryInfo = req.body
        let sql = `select id,roleName,createTime,role_right, comment from role where is_delete = 0 `
        let start = parseInt(queryInfo.num * (queryInfo.page - 1))
        let end = queryInfo.page * queryInfo.num
        if (queryInfo.query != '') {
            sql += ` and roleName like '${queryInfo.query}'`
        }
        sql += ` limit ${start},${end} ; select count(*) as total from role;`
        db.query(sql, function (err, roles) {
            if (err) return res.cc(err)
            db.query("select * from userright", function (err, rights) {
                if (err) return res.cc(err)
                for (idx in roles[0]) {
                    if(roles[0][idx].role_right!= null){
                        let role_rights = roles[0][idx].role_right.split(',')
                        roles[0][idx]['children'] =lodash.values(filterData(role_rights, lodash.keyBy(rights, 'id'))) 
                    }

                }
                return res.send({
                    code: 200,
                    msg: "获取角色列表成功！",
                    count: roles[1][0].total,
                    data: [...roles[0]]
                })
            })

        })
    },

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    数据处理函数

    function filterData(role_rights, rights) {
    var permissionsResult = {};
    for (let i = 0; i < role_rights.length; i++) {
        let rightId = role_rights[i]
        if (rights[rightId].level == 1) {
            let temp = {
                'id': rights[rightId].id,
                'rightName': rights[rightId].rightName,
                'children': []
            }
            permissionsResult[rightId] = temp
        }
        else if(permissionsResult.hasOwnProperty(rights[rightId].pid)){
            permissionsResult[rights[rightId].pid].children.push(rights[rightId])
        }else{
            let temp = {
                'id': rights[rightId].pid,
                'rightName': rights[ rights[rightId].pid].rightName,
                'children': [{
                    'id': rights[rightId].id,
                    'rightName': rights[ rightId].rightName,
                }]
            }
            permissionsResult[rightId] = temp
        }
    }

    return permissionsResult

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    源代码地址

    https://gitee.com/lyy_97591/node_practise.git

  • 相关阅读:
    Hive 时间函数加8小时
    tensorrt安装使用教程
    四、基本组件
    java中判断对象类型的三种方法
    SpringData jpa 详解
    spring三级缓存
    JS踩坑: for let 和 for var的区别
    Java多线程 - Java锁有了解吗？Synchronized和ReentrantLock区别？说说如何ReentrantLock如何实现超时锁的等待？
    Spring源码：SpringBean 的注册-XML源码解析
    如何进行网络编程和套接字操作？
  • 原文地址：https://blog.csdn.net/qq_46258819/article/details/127400912