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 fs = require('fs')
const path = require('path')
const fileEvent = require('../../utils/file')
const execTransection = require('../../utils/dbHelper.js')
const datetimeToTime = 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 
    f.id,
    f.name,
    (SELECT name from user u where f.importer_id = u.id) as importerName,
    f.time,
    f.data_volume
  FROM 
  file_import_t f WHERE f.dataset_id = ?`
  sql = utils.setLike(sql, 'name', obj.name)
  sql = utils.setMoreId(sql, user)
  let { total } = await utils.getSumWhere({ sql: '', val: [obj.dataset_id], name: 'file_import_t f', res, req })
  sql = utils.pageSize(sql, obj.page, obj.size)
  let { result } = await pools({ sql, val: [obj.dataset_id], 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 * FROM mark_data_t WHERE dataset_id=?`
  sql = utils.setLike(sql, 'text', obj.text)
  sql = utils.setMoreId(sql, user)
  let { total } = await utils.getSumWhere({ sql, val: [obj.dataset_id], name: 'mark_data_t', res, req })
  sql = utils.pageSize(sql, obj.page, obj.size)
  let { result } = await pools({ sql, val: [obj.dataset_id], req })
  res.send(utils.returnData({ data: result, total }))
})

// 查看单条数据
router.post('/getData', async (req, res) => {
  let user = await utils.getUserInfo({ req, res })
  obj = req.body
  let sql = `SELECT * FROM mark_data_t WHERE dataset_id=? AND id=?`
  sql = utils.setMoreId(sql, user)
  let { result } = await pools({ sql, val: [obj.dataset_id, obj.id], req })
  res.send(utils.returnData({ data: result }))
})

// 导入数据
router.post('/importData', async (req, res) => {
  let fileArr = await fileEvent(req, res)
  let filename = fileArr[0].filename
  const fileType = req.files[0].mimetype
  let list
  let uploadAddress = 'D:/dataMark/uploadFile'
  //配置获取文件路径
  if (fileType.includes('text')) {
    const path = `D:/dataMark/uploadFile/${filename}`
    fs.readFile(path, 'utf-8', (err, data) => {
      if (err) {
        res.send(utils.returnData({ code: -1, msg: '文件解析失败' }))
      }
      const data1 = data.split(/\r?\n/)
      list = data1.map((line) => [line])
      writeToDatabase(fileArr, list, req, res)
    })
  } else {
    // let xlsxRes = xlsx.parse(`${path.join(__dirname, '../../', 'public/uploadFile/')}${filename}`, { cellDates: true })
    let xlsxRes = xlsx.parse(`${uploadAddress}/${filename}`, { cellDates: true })
    list = xlsxRes[0].data
    list.splice(0, 1)
    writeToDatabase(fileArr, list, req, res)
  }
})
// 写入数据库
const writeToDatabase = (fileArr, list, req, res) => {
  let sqlParamsEntity = []
  let fileImportSql = 'INSERT INTO file_import_t(`id`,`name`,`importer_id`,`time`,`data_volume`,`dataset_id`) VALUES (?,?,?,?,?,?)'
  let params = JSON.parse(fileArr[0].params.listType)
  const uniqueId = uuid.v4()
  let name = fileArr[0].originalname
  let { importer_id: importerId, dataset_id: datasetId } = params
  let currentTime = datetimeToTime()
  let dataVolume = list.length
  var fileImportParam = [uniqueId, name, importerId, currentTime, dataVolume, datasetId]
  sqlParamsEntity.push({
    sql: fileImportSql,
    values: fileImportParam
  })
  let markDataSql = 'INSERT INTO mark_data_t(text,`file_id`,`dataset_id`,is_mark,state) VALUES (?,?,?,?,?)'
  for (let i = 0; i < list.length; i++) {
    let item = list[i]
    var markDataParams = [item[0], uniqueId, datasetId, true, false]
    sqlParamsEntity.push({
      sql: markDataSql,
      values: markDataParams
    })
  }
  execTransection(sqlParamsEntity)
    .then((resp) => {
      res.send(utils.returnData({ code: 1, msg: '导入成功', data: [] }))
    })
    .catch((err) => {
      res.send(utils.returnData({ code: -1, msg: '导入失败', err, req }))
    })
}

// 修改text数据
router.post('/updataData', async (req, res) => {
  let sql = 'UPDATE mark_data_t SET text=? WHERE id=?',
    obj = req.body
  await pools({ sql, val: [obj.text, obj.id], run: false, res, req })
})

//删除
router.post('/delData', async (req, res) => {
  let sqlParams = []
  obj = req.body
  let placeholders = obj.map(() => '?').join(',')
  let sql = `DELETE FROM mark_data_t WHERE id in (${placeholders})`
  // console.log('obj', obj, obj.length)
  // let sqlUpdata = `UPDATE file_import_t SET data_volume = data_volume - ${obj.length} WHERE dataset_id=?`
  sqlParams.push(
    {
      sql: sql,
      values: obj
    },
    {
      sql: sqlUpdata,
      values: []
    }
  )
  execTransection(sqlParams)
    .then((resp) => {
      res.send(utils.returnData({ code: 1, msg: '删除成功', data: [] }))
    })
    .catch((err) => {
      res.send(utils.returnData({ code: -1, msg, err, req }))
    })
  // await pools({ sql, val: obj, run: false, res, req }) // 注意这里直接传递obj数组作为参数
})

// 上一条、下一条数据
router.post('/PNData', async (req, res) => {
  let user = await utils.getUserInfo({ req, res })
  obj = req.body
  let sql = ``
  if (obj.type === 'previous') {
    sql = ` SELECT * FROM mark_data_t m WHERE m.id < ? AND m.dataset_id=? ORDER BY m.id DESC LIMIT 0,1;`
  } else {
    sql = ` SELECT * FROM mark_data_t m WHERE m.id > ? AND m.dataset_id=? ORDER BY m.id LIMIT 0,1;`
  }
  sql = utils.setLike(sql, 'text', obj.text)
  sql = utils.setMoreId(sql, user)
  let { result } = await pools({ sql, val: [obj.id, obj.dataset_id], req })
  if (result.length === 0 && obj.type === 'previous') {
    res.send(utils.returnData({ data: result, code: 2, msg: '已第一条数据' }))
  } else if (result.length === 0 && obj.type === 'next') {
    res.send(utils.returnData({ data: result, code: 2, msg: '已是最后一条数据' }))
  } else {
    res.send(utils.returnData({ data: result }))
  }
})

//导出数据
router.post('/exportDataset', async (req, res) => {
  let user = await utils.getUserInfo({ req, res }),
    obj = req.body
  let sql = `SELECT id,text,markInfo FROM mark_data_t m WHERE m.dataset_id=?`
  sql += ` ORDER BY id ASC`
  let { result } = await pools({ sql, val: [obj.dataset_id], res, req })

  // 查询数据集名称
  let datasetSql = `SELECT name FROM dataset_t d WHERE d.id=?`
  let { datasetName } = await pools({ sql: datasetSql, val: [obj.dataset_id], res, req })

  let data = [['id', '文本', '标注信息']]
  result.map((t) => {
    data.push(Object.values(t))
  })
  let buffer = xlsx.build([{ name: 'sheet1', data }])
  const filename = `标注数据.xlsx`
  const encodedFilename = encodeURI(filename)
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;filename=' + encodedFilename)
  res.send(buffer)
})

//下载模板
router.post('/downloadTemplate', async (req, res) => {
  let data = [['文本']]
  let buffer = xlsx.build([{ name: 'sheet1', data }])
  const filename = '标注数据模板.xlsx'
  const encodedFilename = encodeURI(filename)
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;filename=' + encodedFilename)
  res.send(buffer)
})

module.exports = router