kpt-pasture/backend/src/router/api/mysql.ts

import * as mysql from "mysql2"
import secret from "../../config"
import * as jwt from "jsonwebtoken"
import { createHash } from "crypto"
import Logger from "../../loaders/logger"
import { Request, Response } from "express"
import { createMathExpr } from "svg-captcha"
import getFormatDate from "../../utils/date"
import { connection } from "../../utils/initMysql"
export interface dataModel {
  length: number
}

// 保存验证码
let generateVerify: number

/**
 * @typedef Error
 * @property {string} code.required
 */

/**
 * @typedef Response
 * @property {[integer]} code
 */

/**
 * @typedef Login
 * @property {string} username.required - 用户名 - eg: admin
 * @property {string} password.required - 密码 - eg: 123456
 * @property {integer} verify.required - 验证码
 */

/**
 * 登录
 * @route POST /login
 * @param {Login.model} point.body.required - the new point
 * @produces application/json application/xml
 * @consumes application/json application/xml
 * @summary 登录
 * @group 用户登录、注册相关
 * @returns {Response.model} 200 
 * @returns {Array.<Login>} Login
 * @headers {integer} 200.X-Rate-Limit 
 * @headers {string} 200.X-Expires-After 
 * @security JWT
 */

const login = async (req: Request, res: Response) => {
  const { username, password, verify } = req.body
  if (generateVerify !== verify) return res.json({
    code: -1,
    info: "请输入正确的验证码"
  })
  let sql: string = 'select * from users where username=' + "'" + username + "'"
  connection.query(sql, async function (err, data: dataModel) {
    if (data.length == 0) {
      await res.json({
        code: -1,
        info: "账号尚未被注册"
      })
    } else {
      if (createHash('md5').update(password).digest('hex') == data[0].password) {
        const accessToken = jwt.sign({
          accountId: data[0].id
        }, secret.jwtSecret, { expiresIn: 3600 })
        await res.json({
          code: 0,
          info: "登录成功",
          accessToken
        })
      } else {
        await res.json({
          code: -1,
          info: "密码错误"
        })
      }
    }
  })
}

/**
 * @typedef Register
 * @property {string} username.required - 用户名 - eg: admin
 * @property {string} password.required - 密码 - eg: 123456
 * @property {integer} verify.required - 验证码
 */

/**
* 注册
* @route POST /register
* @param {Register.model} point.body.required - the new point
* @produces application/json application/xml
* @consumes application/json application/xml
* @summary 注册
* @group 用户登录、注册相关
* @returns {Response.model} 200
* @returns {Array.<Register>} Register
* @headers {integer} 200.X-Rate-Limit
* @headers {string} 200.X-Expires-After
* @security JWT
*/

const register = async (req: Request, res: Response) => {
  const { username, password, verify } = req.body
  if (generateVerify !== verify) return res.json({
    code: -1,
    info: "请输入正确的验证码"
  })
  if (password.length < 6) return res.json({
    code: -1,
    info: "密码长度不能小于6位"
  })
  let sql: string = 'select * from users where username=' + "'" + username + "'"
  connection.query(sql, async (err, data: dataModel) => {
    if (data.length > 0) {
      await res.json({
        code: -1,
        info: "账号已被注册"
      })
    } else {
      let time = await getFormatDate()
      let sql: string = 'insert into users (username,password,time) value(' + "'" + username + "'" + ',' + "'" + createHash('md5').update(password).digest('hex') +
        "'" + ',' + "'" + time + "'" + ')'
      connection.query(sql, async function (err) {
        if (err) {
          Logger.error(err)
        } else {
          await res.json({
            code: 0,
            info: "账号注册成功"
          })
        }
      })
    }
  })
}

/**
 * 列表更新
 * @route GET /updateList
 * @summary 列表更新
 * @group 用户管理相关
 * @returns {object} 200 
 * @security JWT
 */

const updateList = async (req: Request, res: Response) => {
  res.json({ code: 1, msg: "成功" })
}

/**
 * 列表删除
 * @route GET /deleteList
 * @summary 列表删除
 * @group 用户管理相关
 * @returns {object} 200 
 * @security JWT
 */

const deleteList = async (req: Request, res: Response) => {
  res.json({ code: 1, msg: "成功" })
}

/**
 * @typedef SearchPage
 * @property {integer} page.required - 第几页 - eg: 1
 * @property {integer} size.required - 数据量（条）- eg: 5
 */

/**
* 分页查询
* @route POST /searchPage
* @param {SearchPage.model} point.body.required - the new point
* @produces application/json application/xml
* @consumes application/json application/xml
* @summary 分页查询
* @group 用户管理相关
* @returns {Response.model} 200
* @returns {Array.<SearchPage>} SearchPage
* @headers {integer} 200.X-Rate-Limit
* @headers {string} 200.X-Expires-After
* @security JWT
*/

const searchPage = async (req: Request, res: Response) => {
  const { page, size } = req.body
  let payload = null
  try {
    const authorizationHeader = req.get("Authorization")
    const accessToken = authorizationHeader.substr("Bearer ".length)
    payload = jwt.verify(accessToken, secret.jwtSecret)
  } catch (error) {
    return res.status(401).end()
  }
  let sql = 'select * from users limit ' + size + ' offset ' + size * (page - 1)
  connection.query(sql, async function (err, data) {
    if (err) {
      Logger.error(err)
    } else {
      await res.json({
        code: 0,
        info: data
      })
    }
  })
}

/**
 * @typedef SearchVague
 * @property {string} username.required - 用户名  - eg: admin
 */

/**
* 模糊查询（根据用户名）
* @route POST /searchVague
* @param {SearchVague.model} point.body.required - the new point
* @produces application/json application/xml
* @consumes application/json application/xml
* @summary 模糊查询
* @group 用户管理相关
* @returns {Response.model} 200
* @returns {Array.<SearchVague>} SearchVague
* @headers {integer} 200.X-Rate-Limit
* @headers {string} 200.X-Expires-After
* @security JWT
*/

const searchVague = async (req: Request, res: Response) => {
  const { username } = req.body
  let payload = null
  try {
    const authorizationHeader = req.get("Authorization")
    const accessToken = authorizationHeader.substr("Bearer ".length)
    payload = jwt.verify(accessToken, secret.jwtSecret)
  } catch (error) {
    return res.status(401).end()
  }
  if (username === "" || username === null) return res.json({
    code: -1,
    info: "搜索信息不能为空"
  })
  let sql = 'select * from users'
  sql += " WHERE username LIKE " + mysql.escape("%" + username + "%")
  connection.query(sql, function (err, data) {
    connection.query(sql, async function (err) {
      if (err) {
        Logger.error(err)
      } else {
        await res.json({
          code: 0,
          info: data
        })
      }
    })
  })
}

/**
 * 图形验证码
 * @route GET /captcha
 * @summary 图形验证码
 * @group captcha - 图形验证码
 * @returns {object} 200 
 * @security JWT
 */

const captcha = async (req: Request, res: Response) => {
  const create = createMathExpr({
    mathMin: 1,
    mathMax: 4,
    mathOperator: "+"
  })
  generateVerify = Number(create.text)
  res.type('svg') // 响应的类型
  res.json({ code: 1, msg: create.text, svg: create.data })
}

export {
  login,
  register,
  updateList,
  deleteList,
  searchPage,
  searchVague,
  captcha,
}