dataMark-app/router/dataMark/entity.js

const express = require('express')
const router = express.Router()
const utils = require('../../utils/index.js')
const pool = require('../../pool.js')
const pools = require('../../utils/pools.js')
const xlsx = require('node-xlsx')
const path = require('path')
const fileEvent = require('../../utils/file')
const getNewSqlParamEntity = require('../../utils/tool.js')
const execTrans = require('../../utils/dbHelper.js')

// 增加实体
router.post('/addEntity', async (req, res) => {
  let sql = 'INSERT INTO entity_t(`name`,`introduce`,is_show,parent_id,color) VALUES (?,?,?,?,?)',
    obj = req.body
  await pools({
    sql,
    val: [obj.name, obj.introduce, obj.is_show, obj.parentId, obj.color],
    run: false,
    res,
    req
  })
})

//查询
router.post('/getEntityList', async (req, res) => {
  let user = await utils.getUserInfo({ req, res }),
    obj = req.body
  let sql = `SELECT id AS entityId,name,introduce,is_show,parent_id AS parentId,color FROM entity_t WHERE 1=1` // WHERE 1=1是一个始终为真的条件，后续动态添加查询条件的起点
  sql = utils.setLike(sql, 'name', obj.name) // 添加一个基于name字段的模糊匹配条件，匹配的值来自obj.name
  sql = utils.setMoreId(sql, user)
  sql = utils.setOr(sql, 'name', obj.name)
  let { total } = await utils.getSum({ sql, name: 'entity_t', res, req }) // 执行SQL查询并获取结果中的总数
  sql += ` ORDER BY id ASC` // 在SQL查询语句的末尾添加了一个ORDER BY子句，用于根据id字段排序结果 ASC升序、DESC降序
  // sql = utils.pageSize(sql, obj.page, obj.size) // 函数来修改SQL语句，添加分页功能。它使用obj.page和obj.size来确定返回结果的范围。
  let { result } = await pools({ sql, res, req })
  result = result.map((item) => ({
    ...item,
    is_show: item.is_show === 1
    // color: item.color.substring(1)
  }))
  res.send(utils.returnData({ data: result, total }))
})

//修改
router.post('/updataEntity', async (req, res) => {
  let sql = 'UPDATE entity_t SET name=?,introduce=?,is_show=?,parent_id=?,color=? WHERE id=?',
    obj = req.body
  await pools({ sql, val: [obj.name, obj.introduce, obj.is_show, obj.parentId, obj.color, obj.entityId], run: false, res, req })
})

//删除
router.post('/delEntity', async (req, res) => {
  // await utils.checkPermi({ req, res, role: [systemSettings.menus.menuDelte] })  //检查是否有删除权限
  let obj = req.body // 假设这是一个包含所有要删除的entityId的数组
  let hasChildren = false
  let selectSql = 'SELECT id FROM entity_t WHERE parent_id=?'
  // 检查是否有子项
  for (let id of obj) {
    let { result } = await pools({ sql: selectSql, val: [id], res, req })
    if (result.length !== 0) {
      hasChildren = true
      break
    }
  }
  if (hasChildren) {
    return res.send(utils.returnData({ code: -1, msg: '删除失败，请先删除子级', req }))
  } else {
    // 构建参数化查询的参数
    let placeholders = obj.map(() => '?').join(',')
    let sql = `DELETE FROM entity_t WHERE id in (${placeholders})`
    await pools({ sql, val: obj, run: false, res, req }) // 注意这里直接传递obj数组作为参数
  }
})

// 导入
router.post('/importEntity', async (req, res) => {
  let sql = 'INSERT INTO entity_t(`name`,`introduce`,is_show,parent_id,color) VALUES ?'
  let fileArr = await fileEvent(req, res)
  let filename = fileArr[0].filename
  //配置获取文件路径
  let xlsxRes = xlsx.parse(`${path.join(__dirname, '../../', 'public/uploadFile/')}${filename}`, { cellDates: true })
  let list = xlsxRes[0].data
  list.splice(0, 1)
  console.log('list', list)
  for (let i = 0; i < list.length; i++) {
    let item = list[i]
    if (item[item.length - 1] === 0) {
      let res1 = await pools({ sql, val: [[item]], run: true, res, req, msg: '请确认导入的一级节点的值没有问题！！！' })
      console.log('res1', res1)
    } else {
      let parentName = item[item.length - 1]
      let parentSql = 'SELECT id FROM entity_t WHERE `name` = ?'
      let parentId
      try {
        let result = await pools({ sql: parentSql, val: [parentName], run: true, res, req })
        console.log('result', result)
        parentId = result.result[0].id
        console.log('parentId', parentId)
      } catch (error) {
        console.log('获取父节点id失败', error)
        res.status(500).send('获取父节点id失败')
        return
      }
      // 将父节点id添加到item中替换父节点名称
      item[item.length - 1] = parentId
      // 插入数据库
      try {
        if (i === list.length - 1) {
          await pools({ sql, val: [[item]], run: false, res, req, msg: '请确认导入的节点的值没有问题！！！' })
        } else {
          await pools({ sql, val: [[item]], run: true, res, req, msg: '请确认导入的节点的值没有问题！！！' })
        }
      } catch (error) {
        console.log('插入数据失败', error)
        res.status(500).send('插入数据失败')
        return
      }
    }
  }
})
module.exports = router