dataset.js 6.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193
  1. const express = require('express')
  2. const router = express.Router()
  3. const utils = require('../../utils/index.js')
  4. const pools = require('../../utils/pools.js')
  5. const xlsx = require('node-xlsx')
  6. const fs = require('fs')
  7. const path = require('path')
  8. const fileEvent = require('../../utils/file')
  9. const execTransection = require('../../utils/dbHelper.js')
  10. const datetimeToTime = require('../../utils/tool.js')
  11. const uuid = require('uuid')
  12. // 增加数据集
  13. router.post('/addDataset', async (req, res) => {
  14. let sql = 'INSERT INTO dataset_t(`name`,creator_id) VALUES (?,?)',
  15. obj = req.body
  16. await utils.existName({
  17. sql: 'SELECT name FROM dataset_t WHERE name=?',
  18. name: obj.name,
  19. res,
  20. msg: '该数据集名称已存在!',
  21. req
  22. })
  23. await pools({
  24. sql,
  25. val: [obj.name, obj.creatorId],
  26. run: false,
  27. res,
  28. req
  29. })
  30. })
  31. //查询
  32. router.post('/getDatasetList', async (req, res) => {
  33. let user = await utils.getUserInfo({ req, res }),
  34. obj = req.body
  35. let sql = `SELECT
  36. d.id,
  37. d.name,
  38. d.creator_id AS creatorId,
  39. u.name as creatorName,
  40. (SELECT count(*) from mark_data_t m where m.dataset_id = d.id AND m.state=1) as marked,
  41. (SELECT count(*) from mark_data_t m where m.dataset_id = d.id) as totalNumber
  42. FROM
  43. 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
  44. sql = utils.setLike(sql, 'd.name', obj.name) // 添加一个基于name字段的模糊匹配条件,匹配的值来自obj.name
  45. sql = utils.setMoreId(sql, user)
  46. let { total } = await utils.getSum({ sql, name: 'dataset_t d', res, req }) // 执行SQL查询并获取结果中的总数
  47. sql = utils.pageSize(sql, obj.page, obj.size) // 函数来修改SQL语句,添加分页功能。它使用obj.page和obj.size来确定返回结果的范围。
  48. let { result } = await pools({ sql, res, req })
  49. res.send(utils.returnData({ data: result, total }))
  50. })
  51. // 编辑
  52. router.post('/updataDataset', async (req, res) => {
  53. let sql = 'UPDATE dataset_t SET name=? WHERE id=?',
  54. obj = req.body
  55. await pools({ sql, val: [obj.name, obj.id], run: false, res, req })
  56. })
  57. //删除
  58. router.post('/delDataset', async (req, res) => {
  59. obj = req.body
  60. let placeholders = obj.map(() => '?').join(',')
  61. let sql = `DELETE FROM dataset_t WHERE id in (${placeholders})`
  62. await pools({ sql, val: obj, run: false, res, req }) // 注意这里直接传递obj数组作为参数
  63. })
  64. // 查询导入记录
  65. router.post('/getImportRecord', async (req, res) => {
  66. let user = await utils.getUserInfo({ req, res })
  67. obj = req.body
  68. console.log('req.body', req.body)
  69. let sql = `SELECT
  70. f.id,
  71. f.name,
  72. (SELECT name from user u where f.importer_id = u.id) as importerName,
  73. f.time,
  74. f.data_volume
  75. FROM
  76. file_import_t f WHERE f.dataset_id = ?`
  77. sql = utils.setLike(sql, 'name', obj.name)
  78. sql = utils.setMoreId(sql, user)
  79. let { total } = await utils.getSumWhere({ sql: '', val: [obj.dataset_id], name: 'file_import_t f', res, req })
  80. sql = utils.pageSize(sql, obj.page, obj.size)
  81. let { result } = await pools({ sql, val: [obj.dataset_id], req })
  82. res.send(utils.returnData({ data: result, total }))
  83. })
  84. // 查询数据集数据
  85. router.post('/getDataList', async (req, res) => {
  86. let user = await utils.getUserInfo({ req, res }),
  87. obj = req.body
  88. let sql = `SELECT id,text,is_mark,state,marked_text,marker_id FROM mark_data_t WHERE dataset_id=?`
  89. sql = utils.setLike(sql, 'text', obj.text)
  90. sql = utils.setMoreId(sql, user)
  91. let { total } = await utils.getSumWhere({ sql, val: [obj.dataset_id], name: 'mark_data_t', res, req })
  92. sql = utils.pageSize(sql, obj.page, obj.size)
  93. let { result } = await pools({ sql, val: [obj.dataset_id], req })
  94. res.send(utils.returnData({ data: result, total }))
  95. })
  96. // 导入数据
  97. router.post('/importData', async (req, res) => {
  98. let fileArr = await fileEvent(req, res)
  99. let filename = fileArr[0].filename
  100. const fileType = req.files[0].mimetype
  101. let list
  102. //配置获取文件路径
  103. if (fileType.includes('text')) {
  104. const path = `public/uploadFile/${filename}`
  105. fs.readFile(path, 'utf-8', (err, data) => {
  106. if (err) {
  107. res.send(utils.returnData({ code: -1, msg: '文件解析失败' }))
  108. }
  109. const data1 = data.split(/\r?\n/)
  110. list = data1.map((line) => [line])
  111. writeToDatabase(fileArr, list, req, res)
  112. })
  113. } else {
  114. let xlsxRes = xlsx.parse(`${path.join(__dirname, '../../', 'public/uploadFile/')}${filename}`, { cellDates: true })
  115. list = xlsxRes[0].data
  116. list.splice(0, 1)
  117. writeToDatabase(fileArr, list, req, res)
  118. }
  119. })
  120. // 写入数据库
  121. const writeToDatabase = (fileArr, list, req, res) => {
  122. let sqlParamsEntity = []
  123. let fileImportSql = 'INSERT INTO file_import_t(`id`,`name`,`importer_id`,`time`,`data_volume`,`dataset_id`) VALUES (?,?,?,?,?,?)'
  124. let params = JSON.parse(fileArr[0].params.listType)
  125. const uniqueId = uuid.v4()
  126. let name = fileArr[0].originalname
  127. let { importer_id: importerId, dataset_id: datasetId } = params
  128. let currentTime = datetimeToTime()
  129. let dataVolume = list.length
  130. var fileImportParam = [uniqueId, name, importerId, currentTime, dataVolume, datasetId]
  131. sqlParamsEntity.push({
  132. sql: fileImportSql,
  133. values: fileImportParam
  134. })
  135. let markDataSql = 'INSERT INTO mark_data_t(text,`file_id`,`dataset_id`,is_mark,state) VALUES (?,?,?,?,?)'
  136. console.log('list.length', list.length)
  137. for (let i = 0; i < list.length; i++) {
  138. let item = list[i]
  139. var markDataParams = [item[0], uniqueId, datasetId, true, false]
  140. sqlParamsEntity.push({
  141. sql: markDataSql,
  142. values: markDataParams
  143. })
  144. }
  145. execTransection(sqlParamsEntity)
  146. .then((resp) => {
  147. res.send(utils.returnData({ code: 1, msg: '导入成功', data: [] }))
  148. })
  149. .catch((err) => {
  150. res.send(utils.returnData({ code: -1, msg, err, req }))
  151. })
  152. }
  153. // 修改text数据
  154. router.post('/updataData', async (req, res) => {
  155. let sql = 'UPDATE mark_data_t SET text=? WHERE id=?'
  156. console.log('obj', obj)
  157. await pools({ sql, val: [obj.text, obj.id], run: false, res, req })
  158. })
  159. //删除
  160. router.post('/delData', async (req, res) => {
  161. let sqlParams = []
  162. obj = req.body
  163. let placeholders = obj.map(() => '?').join(',')
  164. let sql = `DELETE FROM mark_data_t WHERE id in (${placeholders})`
  165. let sqlUpdata = `UPDATE file_import_t SET data_volume = data_volume - ${obj.length}`
  166. sqlParams.push(
  167. {
  168. sql: sql,
  169. values: obj
  170. },
  171. {
  172. sql: sqlUpdata,
  173. values: []
  174. }
  175. )
  176. execTransection(sqlParams)
  177. .then((resp) => {
  178. res.send(utils.returnData({ code: 1, msg: '删除成功', data: [] }))
  179. })
  180. .catch((err) => {
  181. res.send(utils.returnData({ code: -1, msg, err, req }))
  182. })
  183. // await pools({ sql, val: obj, run: false, res, req }) // 注意这里直接传递obj数组作为参数
  184. })
  185. module.exports = router