node 接口(小滴)

图形验证码插件

yarn add svg-captcha@1.4.0

路由

const express = require('express')
const router = express.Router()
const NotifyController = require('../controller/NotifyController')

// 图形验证码接口
router.get('/captcha',NotifyController.catcha)

module.exports = router

控制器

const NotifyService = require('../service/NotifyService')

const NotifyController = {
    catcha: async(req,res) => {
        let hanleRes = await NotifyService.captcha()
        res.set('content-type', 'image/svg+xml')
        
        res.send(hanleRes)
    }
}

module.exports = NotifyController

服务

const svgCaptcha = require('svg-captcha')

const NotifyService = {
    captcha: async() => {
        let captcha = svgCaptcha.create({
            size:4,// 验证吗长度
            ignoreChars:'0oli', // 验证码字符中排除
            noise:1,// 干扰线
            background:'#aaa'
        })
        console.log('654654',captcha.text)

        return captcha.data
    }
}

module.exports = NotifyService

通知相关的接口

const notifyRouter = require('./router/notify')
app.use('/api/notify/v1',notifyRouter)

查询bannert图接口

路由

const express = require('express')
const router = express.Router()
const BannerController = require('../controller/BannerController')
// banner接口
router.get('/list',BannerController.list)

module.exports = router

数据层

const DB = require('../config/sequelize')
const BackCode = require('../utils/BackCode')

const BannerService = {
    list:async(location) => {
        const data = await DB.banner.findOne({wehere:{location}})
        return BackCode.buildSuccessAndData({data})
    }
}

module.exports = BannerService

控制器

/***
 * @params list banner接口
 * 
 */
const BannerService = require('../service/BannerService')

const BannerController = {
    list:async(req,res)=>{
        let {location} = req.query
        let handleRes = await BannerService.list(location)
        res.send(handleRes)
    }
}

module.exports = BannerController

请求接口

// banner 接口
const bannerRouter = require('./router/banner')
app.use('/api/banner/v1',bannerRouter)

个人信息接口

http://127.0.0.1:8081/api/user/v1/detail

路由

const express = require('express')
const router = express.Router()
const UserController = require('../controller/UserController')

//用户信息接口
router.get('/detail',UserController.detail)

module.exports = router

都差不多 查询单个

课程分类导航开发 一对多

服务层

const DB = require('../config/sequelize')
const category = require('../models/category')
const BackCode = require('../utils/BackCode')
const {Op} = require('sequelize')


const ProductService = {
    category:async()=>{
        // 无关联查询方案
        let parentList = await DB.category.findAll({where :{pid:0},order:[['id']], raw:true})
        // Op.ne]: 20,               // id != 20 查询 id不等于20的数据
        let childList = await DB.category.findAll({where:{pid:{[Op.ne]:0}},order:[['id']],raw:true})

        parentList.map((item) =>{
            item['subCategoryList'] = []
            childList.map((subItem)=>{
                if(subItem.pid == item.id){
                    return item.subCategoryList.push(subItem)
                }
            })
        })
        return BackCode.buildSuccessAndData({data:parentList})
    }
}

module.exports = ProductService

sequelize 的op模块

const Op = Sequelize.Op
 
[Op.and]: {a: 5}           // 且 (a = 5)
[Op.or]: [{a: 5}, {a: 6}]  // (a = 5 或 a = 6)
[Op.gt]: 6,                // id > 6
[Op.gte]: 6,               // id >= 6
[Op.lt]: 10,               // id < 10
[Op.lte]: 10,              // id <= 10
[Op.ne]: 20,               // id != 20
[Op.eq]: 3,                // = 3
[Op.not]: true,            // 不是 TRUE
[Op.between]: [6, 10],     // 在 6 和 10 之间
[Op.notBetween]: [11, 15], // 不在 11 和 15 之间
[Op.in]: [1, 2],           // 在 [1, 2] 之中
[Op.notIn]: [1, 2],        // 不在 [1, 2] 之中
[Op.like]: '%hat',         // 包含 '%hat'
[Op.notLike]: '%hat'       // 不包含 '%hat'
[Op.iLike]: '%hat'         // 包含 '%hat' (不区分大小写)  (仅限 PG)
[Op.notILike]: '%hat'      // 不包含 '%hat'  (仅限 PG)
[Op.regexp]: '^[h|a|t]'    // 匹配正则表达式/~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.notRegexp]: '^[h|a|t]' // 不匹配正则表达式/!~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (仅限 PG)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (仅限 PG)
[Op.like]: { [Op.any]: ['cat', 'hat']} // 包含任何数组['cat', 'hat'] - 同样适用于 iLike 和 notLike
[Op.overlap]: [1, 2]       // && [1, 2] (PG数组重叠运算符)
[Op.contains]: [1, 2]      // @> [1, 2] (PG数组包含运算符)
[Op.contained]: [1, 2]     // <@ [1, 2] (PG数组包含于运算符)
[Op.any]: [2,3]            // 任何数组[2, 3]::INTEGER (仅限PG)
[Op.col]: 'user.organization_id' // = 'user'.'organization_id', 使用数据库语言特定的列标识符, 

需要在 一对多关系模型

// category与自身表的一对多关系模型
models.category.hasMany(models.category,{foreignKey:'pid', as:'subCategoryList'})
models.category.belongsTo(models.category,{foreignKey:'pid'})

服务层

  card:async() =>{
        let cardList = await DB.product_card.findAll({raw:true})
        let list = cardList.map(async (item) =>{
            item.product_list = await DB.product.findAll({where:{id:item.product_list.split(',')},raw:true})
            return item
        })
        let lastList = await Promise.all(list)
        return BackCode.buildSuccessAndData({data:lastList})
    }

通过选择不同分类+⻚数查询不同课程（难点）

难点：sql写法

SELECT p.id,p.cover_img,p.title,p.course_level,p.buy_num,p.old_amount,p.amount FROM product p LEFT JOIN category_product c ON c.product_id=p.id WHERE c.category_id = 88 ORDER BY p.gmt_create DESC LIMIT 3,5

// 分类查找视频接⼝
    query_by_cid:async (req) =>{
        let {cpid,cid,page,size} = req.body
        if(!(page && size)) return BackCode.buildError({msg:'缺少必要的参数'})

        // 当前从第几个算
        page = (page - 1) * size
        // 判断分类和方向是否为空
        let sqlId = cid || cpid || null

        // 原始的关联查询课程列表
        // $ {} 判断语句 ！！！
        let productListSql = `SELECT p.id,p.cover_img,p.title,p.course_level,p.buy_num,p.old_amount,p.amount FROM product p LEFT JOIN category_product c ON c.product_id=p.id ${sqlId ? 'WHERE c.category_id=?' : ''} ORDER BY p.gmt_create DESC LIMIT ?,?`
        // 传递几个参数进sql判断
        let productListQuery = sqlId ? [sqlId, page, size] : [page, size]

        // sequelize原始查询
        let productList = await DB.sequelize.query(productListSql, {
            replacements: productListQuery,
            type: QueryTypes.SELECT
        })

        // 通过子查询课程总数
        let totalSql = `select count(*) as total_record from (SELECT p.id,p.cover_img,p.title,p.course_level,p.buy_num,p.old_amount,p.amount FROM product p LEFT JOIN category_product c ON c.product_id=p.id ${sqlId ? 'WHERE c.category_id=?' : ''}) temp_table`

        // sequelize原始查询总数
        let totalRes = await DB.sequelize.query(totalSql, {
            replacements: [sqlId],
            type: QueryTypes.SELECT
        })

        // 总数
        let total_record = totalRes[0].total_record

        // 计算总页数
        let total_page = null
        total_record / size == 0 ? (total_page = total_record / size) : (total_page = Math.ceil(total_record / size))

        return BackCode.buildSuccessAndData({ data: { current_data: productList, total_page, total_record } })
    }

SELECT p.id,p.cover_img,p.title,p.course_level,p.buy_num,p.old_amount,p.amount FROM product p LEFT JOIN category_product c ON c.product_id=p.id WHERE c.category_id=88 ORDER BY p.gmt_create DESC LIMIT 0,12

select count(*) as total_record from (SELECT p.id,p.cover_img,p.title,p.course_level,p.buy_num,p.old_amount,p.amount FROM 
product p LEFT JOIN category_product c ON c.product_id=p.id WHERE c.category_id=88) temp_table

课程详情⼀对多关联查询+学员购买动态

// teacher和product表的一对多关系模型
models.Teacher.hasMany(models.Product, { foreignKey: 'teacher_id' })
models.Product.belongsTo(models.Teacher, { foreignKey: 'teacher_id', as: "teacherDetail" })

  detail: async (req) => {
    let { id } = req.query
    let productDetail = await DB.Product.findOne({
      where: { id },
      include: [{ model: DB.Teacher, as: 'teacherDetail' }],
    })
    return BackCode.buildSuccessAndData({ data: { ...productDetail.toJSON(), bd_zip_url: '', note_url: '' } })
  }

查询课程是否购买接口

const OrderService = {
    query_pay:async(req) =>{
        // 本来userId 可以在token获取
        let {id,userId} = req.query
        // 请求头获取token
        //let token = req.headers.authorization.split(' ').pop()
        // 解密
        //let userInfo = SecretTool.jwtVerify(token)
        let orderList = await DB.product_order.findAll({
            where:{product_id:id,account_id:userId,order_state:'PAY'},
            raw:true
        })
        if(orderList.length > 0){
            return BackCode.buildSuccess()
        }else{
            return BackCode.buildError(CodeEnum.PRODUCT_NOY_PAY)
        }

    }
}

查询课程是否最近购买动态接口

lastest:async (req) => {
        let {id} = req.query
        let latestList = await DB.product_order.findAll({
            where:{product_id:id},
            order:[['gmt_create','DESC']],
            limit:20
        })
        return BackCode.buildSuccessAndData({data:latestList})
    }

推荐好课+课程章集模块开发

chapter: async (req) => {
        let { id } = req.query
        if (!id) return BackCode.buildError({ msg: '缺少必要参数' })
        let chapterList = await DB.chapter.findAll({ where: { product_id: id }, order: [['ordered']], raw: true })
        let episodeList = await DB.episode.findAll({ where: { product_id: id }, order: [['ordered']], raw: true })
        // 将课程的集生层对象数组插入到章数据元素中
        chapterList.map((item) => {
          item['episodeList'] = []
          episodeList.map((subItem) => {
            if (subItem.chapter_id === item.id) {
              return item['episodeList'].push(subItem)
            }
          })
        })
        return BackCode.buildSuccessAndData({ data: chapterList })
      },
}

module.exports = ProductService

// 视频资料接口

material_by_id:async(req) =>{
        let {id,userId} = req.query
        if(!id)  return BackCode.buildError({msg:'缺少必要参数'})

        // let token = req.headers.authorization.split(' ').pop()
        // // 判断是否登录
        // if (!token) return BackCode.buildResult(CodeEnum.ACCOUNT_UNLOGIN)
        // let userInfo = SecretTool.jwtVerify(token)
        
        // 判断是否购买
        let orderList = await DB.product_order.findAll({
            where:{product_id:id,account_id:userId,order_state:'PAY'},
            raw:true
        })

        if(orderList.length > 0){
            let productDetail = await DB.product.findOne({
                attributes: ['bd_zip_url', 'note_url'], // 只输出这两个字段
                where: { id }
            })
            return BackCode.buildSuccessAndData({data:productDetail})
        }else{
            return BackCode.buildResult(CodeEnum.PRODUCT_NOY_PAY)
        }
    }

// ⽤户评论列表 分页

const CommentService = {
    page:async (req) => {
        let {page,size,id} = req.body
        if(!(page && size && id)){
            return BackCode.buildError({msg:'缺少必要的参数'})
        }

        // findAndCountAll 方法是结合了 findAll 和 count ,同时获取评论列表和总数
        let {count,rows} = await DB.comment.findAndCountAll({
            where:{product_id:id},
            order:[['gmt_create','DESC']],
            offset:Number((page -1) * size),
            limit:Number(size)
        })
        // 计算总页数
        let total_page = null
        count / size == 0 ? (total_page = count / size) : (total_page = Math.ceil(count / size))
        return BackCode.buildSuccessAndData({data:{current_data:rows,total_page:total_page,total_record:count}})
    }
}

登录接口

"jsonwebtoken": "8.5.1",

封装请求

const md5 = require('blueimp-md5')
const jwt = require('jsonwebtoken')
const { jwtSecretKey } = require('../config/jwtSecretKey')

class SecretTool {
  // md5加密密码
  static md5(query) {
    return md5(query)
  }
  // jwt生成token
  static jwtSign(query, time) {
    return jwt.sign(query, jwtSecretKey, { expiresIn: time })
  }
  // jwt解密token
  static jwtVerify(query) {
    return jwt.verify(query, jwtSecretKey)
  }
}

module.exports = SecretTool

login:async (req) =>{
        let {phone,password} = req.body
        // 参数判空
        if(!phone && password) return BackCode.buildError({msg:'缺少必要参数'})
        // 判断手机号是否注册
        let userInfo = await DB.account.findAll({where:{phone},raw:true})
        if (userInfo.length == 0) return BackCode.buildResult(CodeEnum.ACCOUNT_UNLOGIN)

        // 账号密码方式
        if(password){
            // 判断密码是否正确
            if(!userInfo[0].pwd == SecretTool.md5(password)){
                return BackCode.buildResult(CodeEnum.ACCOUNT_PWD_ERROR)
            }
        }
        // 拼接token用户信息，除去密码
        let user = {...userInfo[0],pwd:''}
        //生成token
        let token = SecretTool.jwtSign(user,'168h')
        return BackCode.buildSuccessAndData({data:`Bearer ${token}`})

    },

  duration_record:async(req)=>{
        let {productId,episodeId,duration } = req.body
        if(!(productId && episodeId && duration)){
            return BackCode.buildError({msg:'缺少必要参数'})
        }

        let token = req.headers.authorization.split(' ').pop()
        //let userInfo = SecretTool.jwtVerify(token)

        // 查询是否该用户的该章该集有上报过学习时长、有则更新学习时长、无则插入
        let isHas = await DB.duration_record.findOne({
            where: { account_id: "6814385", product_id: productId, episode_id: episodeId },
            raw:true
        })
        // 对比最新学习时长和之前的大小
        // if (!(Number(duration) > Number(isHas.duration))) {
        //     return BackCode.buildError({ msg: '最新学习时长较之前小，不做更新' })
        // }
        if(isHas){
            await DB.duration_record.update(
                {duration:Number(duration)},
                { where: { account_id: "6814385", product_id: productId, episode_id: episodeId } }
            )
            return BackCode.buildSuccess()
        }else{
            await DB.duration_record.create({
                account_id: "6814385",
                product_id: productId,
                episode_id: episodeId,
                duration: duration
            })
            return BackCode.buildSuccess()
        }
    },

   play_record: async(req) =>{
        let {page,size,userId} = req.body

        // 关联分页查询用户的播放记录列表
        let recordSql ="SELECT r.id,r.product_id,r.current_episode_id,r.account_id,r.learn_ids,r.pay_status,r.gmt_modified,p.title product_title,p.cover_img,p.episode_num,p.product_type,e.title episode_title FROM play_record r LEFT JOIN product p ON r.product_id = p.id LEFT JOIN episode e ON e.id = r.current_episode_id WHERE r.account_id=? and p.product_type not in('FOREVER_VIP','YEAR_VIP','BACKEND_ONE_TO_ONE','FRONTEND_ONE_TO_ONE')  ORDER BY r.gmt_modified desc limit ?,?"
        // 查询用户的播放记录总数
        let countSql = 'select count(*) as count from play_record where account_id=?'

        let recordList = await DB.sequelize.query(
            recordSql,{
                replacements:[userId,Number((page - 1)*size),Number(size)],
                type:QueryTypes.SELECT
            }
        )
        let count = await DB.sequelize.query(
            countSql,
            {
                replacements:[userId],
                type:QueryTypes.SELECT
            }
        )
        count = count[0].count

        let total_page = null
        count / size == 0 ? (total_page = count / size) : (total_page = Math.ceil(count / size))

        return BackCode.buildSuccessAndData({data:{current_data:recordList,total_page:total_page,total_record:count}})
    }

修改个人资料

 update:async (req) =>{
        let {id,username,slogan,sex,city} = req.body
        if(!(id && username && slogan && city)){
            return BackCode.buildError({msg:'缺少必要参数'})
        }

        let accountItem = {username,slogan,sex,city}
        await DB.account.update(accountItem,{where:{id}})
        return BackCode.buildSuccess()
    }

修改

 deleteUser:async(id) => {
        let data = await DB.account.findOne({where:{id}})
        if(!data){
            return BackCode.buildError(CodeEnum.ACCOUNT_UNREGISTER)
        }
        await data.update({del:1})
        return BackCode.buildSuccess()
    }

// 删除用户
router.delete('/user/:id',AdminController.deleteUser)

修改个人信息

服务层

   updateUser:async(id,updated_details) =>{
        let data = await DB.account.findOne({where:{id}})
        if(!data){
            return BackCode.buildError(CodeEnum.ACCOUNT_UNREGISTER)
        }
        await data.update(updated_details)
        return BackCode.buildSuccess()
    }

控制层

async updateUser(req,res){
        let {id} = req.params
        let updated_details = req.body
        let handleRes = await AdminService.updateUser(id,updated_details)
        res.send(handleRes)
    }

路由

// 修改某个用户接口
router.put('/user/:id',AdminController.updateUser)

查询订单

// product_title

// order_state

 searchOrder:async({condition,page,size,gmt_start,gmt_end}) =>{
        const whereOptions = DBTool.generateWhereOptions({
            condition,
            gmt_start,
            gmt_end,
            searchFields:['product_title','username','order_state'] //查询条件
        })
        let result = await DBTool.paginate(DB.product_order,{where:whereOptions,page,size})
        if(!result.current_data.length === 0){
            return BackCode.buildError(CodeEnum.ACCOUNT_UNLOGIN)
        }
        return BackCode.buildSuccessAndData({data:result})
    }

 async searchOrder(req,res){
        let {condition} = req.params
        let {page,size,gmt_start,gmt_end} = req.query
        let handleRes = await AdminService.searchOrder({condition,page,size,gmt_start,gmt_end})
        res.send(handleRes)
    }

// 查询全部订单
router.get('/order',AdminController.searchOrder)

// 条件查询订单
router.get('/order/:condition',AdminController.searchOrder)