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