dataMark-app/router/dataMark/dataset.js

const express = require('express')
const router = express.Router()
const utils = require('../../utils/index.js')
const pools = require('../../utils/pools.js')
const xlsx = require('node-xlsx')
const path = require('path')
const fileEvent = require('../../utils/file')
const execTrans = require('../../utils/dbHelper.js')
const getNewSqlParamEntity = require('../../utils/tool.js')
const uuid = require('uuid')
// 增加数据集
router.post('/addDataset', async (req, res) => {
  let sql = 'INSERT INTO dataset_t(`name`,creator_id) VALUES (?,?)',
    obj = req.body
  await utils.existName({
    sql: 'SELECT name FROM dataset_t WHERE name=?',
    name: obj.name,
    res,
    msg: '该数据集名称已存在！',
    req
  })
  await pools({
    sql,
    val: [obj.name, obj.creatorId],
    run: false,
    res,
    req
  })
})

//查询
router.post('/getDatasetList', async (req, res) => {
  let user = await utils.getUserInfo({ req, res }),
    obj = req.body
  let sql = `SELECT 
    d.id,
    d.name,
    d.creator_id AS creatorId,
    u.name as creatorName,
    (SELECT count(*) from mark_data_t m where m.dataset_id = d.id AND m.state=1) as marked,
    (SELECT count(*) from mark_data_t m where m.dataset_id = d.id) as totalNumber
  FROM 
    dataset_t d JOIN user u ON d.creator_id = u.id WHERE 1=1` // WHERE 1=1是一个始终为真的条件，后续动态添加查询条件的起点 JOIN mark_data_t m ON d.id = m.dataset_id
  sql = utils.setLike(sql, 'd.name', obj.name) // 添加一个基于name字段的模糊匹配条件，匹配的值来自obj.name
  sql = utils.setMoreId(sql, user)
  let { total } = await utils.getSum({ sql, name: 'dataset_t d', res, req }) // 执行SQL查询并获取结果中的总数
  sql = utils.pageSize(sql, obj.page, obj.size) // 函数来修改SQL语句，添加分页功能。它使用obj.page和obj.size来确定返回结果的范围。
  let { result } = await pools({ sql, res, req })
  res.send(utils.returnData({ data: result, total }))
})

// 编辑
router.post('/updataDataset', async (req, res) => {
  let sql = 'UPDATE dataset_t SET name=? WHERE id=?',
    obj = req.body
  await pools({ sql, val: [obj.name, obj.id], run: false, res, req })
})

//删除
router.post('/delDataset', async (req, res) => {
  obj = req.body
  let placeholders = obj.map(() => '?').join(',')
  let sql = `DELETE FROM dataset_t WHERE id in (${placeholders})`
  await pools({ sql, val: obj, run: false, res, req }) // 注意这里直接传递obj数组作为参数
})

// 查询导入记录
router.post('/getImportRecord', async (req, res) => {
  let user = await utils.getUserInfo({ req, res }),
    obj = req.body
  let sql = `SELECT id,name,importer_id,time,data_volume FROM file_import_t WHERE 1=1` // WHERE 1=1是一个始终为真的条件，后续动态添加查询条件的起点
  sql = utils.setLike(sql, 'name', obj.name) // 添加一个基于name字段的模糊匹配条件，匹配的值来自obj.name
  sql = utils.setMoreId(sql, user)
  let { total } = await utils.getSum({ sql, name: 'file_import_t', res, req }) // 执行SQL查询并获取结果中的总数
  sql = utils.pageSize(sql, obj.page, obj.size) // 函数来修改SQL语句，添加分页功能。它使用obj.page和obj.size来确定返回结果的范围。
  let { result } = await pools({ sql, res, req })
  res.send(utils.returnData({ data: result, total }))
})

// 查询数据集数据
router.post('/getDataList', async (req, res) => {
  let user = await utils.getUserInfo({ req, res }),
    obj = req.body
  let sql = `SELECT id,text,is_mark,state FROM mark_data_t WHERE 1=1` // WHERE 1=1是一个始终为真的条件，后续动态添加查询条件的起点
  sql = utils.setLike(sql, 'text', obj.text) // 添加一个基于name字段的模糊匹配条件，匹配的值来自obj.name
  sql = utils.setMoreId(sql, user)
  let { total } = await utils.getSum({ sql, name: 'mark_data_t', res, req }) // 执行SQL查询并获取结果中的总数
  sql = utils.pageSize(sql, obj.page, obj.size) // 函数来修改SQL语句，添加分页功能。它使用obj.page和obj.size来确定返回结果的范围。
  let { result } = await pools({ sql, res, req })
  res.send(utils.returnData({ data: result, total }))
})

// 导入数据
router.post('/importData', async (req, res) => {
  var sqlParamsEntity = []
  let fileImportSql = 'INSERT INTO file_import_t(`id`,`name`,`importer_id`,`data_volume`,`dataset_id`) VALUES ?'
  // let sql1 = 'INSERT INTO mark_data_t(`text`,`file_id`,`time`,`data_volume`,`dataset_id`) VALUES ?'
  let fileArr = await fileEvent(req, res)
  let params = JSON.parse(fileArr[0].params.listType)
  const uniqueId = uuid.v4()
  console.log('fileArr', fileArr)
  let filename = fileArr[0].filename
  let name = fileArr[0].originalname
  //配置获取文件路径
  let xlsxRes = xlsx.parse(`${path.join(__dirname, '../../', 'public/uploadFile/')}${filename}`, { cellDates: true })
  let list = xlsxRes[0].data
  list.splice(0, 1)
  var fileImportParam = { id: uniqueId, name, importer_id: params.importer_id, data_volume: list.length, dataset_id: params.dataset_id }
  sqlParamsEntity.push(getNewSqlParamEntity({ sql: fileImportSql, fileImportParam }))
  let markDataSql = 'INSERT INTO file_import_t(text,`file_id`,`dataset_id`,is_mark,state) VALUES ?'
  for (let i = 0; i < list.length; i++) {
    let item = list[i]
    console.log('item', item)
    // var markDataParams = { id: uniqueId, name, importer_id: params.importer_id, data_volume: list.length, dataset_id: params.dataset_id }
    // sqlParamsEntity.push(getNewSqlParamEntity({ sql: fileImportSql, fileImportParam }))
  }
  // await pools({sql,val:[list],run:false,res,req,msg:"请确认文档导入值没有问题！！！"});
  execTrans(sqlParamsEntity, function (err, info) {
    if (err) {
      console.error('事务执行失败', err)
    } else {
      console.log('done.')
    }
  })
})
module.exports = router