src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt
@@ -1,6 +1,7 @@ package com.flightfeather.uav.common.utils import com.flightfeather.uav.common.exception.ResponseErrorException import org.apache.poi.hssf.usermodel.HSSFWorkbook import org.apache.poi.ss.usermodel.Row import org.apache.poi.ss.util.CellRangeAddress import org.apache.poi.xssf.streaming.SXSSFWorkbook src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt
@@ -3,6 +3,7 @@ import com.alibaba.fastjson.JSONObject import com.flightfeather.uav.common.exception.ResponseErrorException import com.flightfeather.uav.domain.entity.RealTimeData import com.flightfeather.uav.domain.entity.RealTimeDataVehicle import com.flightfeather.uav.socket.bean.AirData import org.apache.poi.hssf.usermodel.HSSFWorkbook import org.apache.poi.xssf.streaming.SXSSFWorkbook @@ -28,11 +29,11 @@ private val format = SimpleDateFormat("yyyy-MM-dd HH:mm:ss") } fun exchangeJinanData(file: File) { fun exchangeJinanData(deviceCode: String, file: InputStream): List<RealTimeDataVehicle> { val headers = listOf( "data_time", "longitude", "latitude", "data_time", "NO2", "CO", "H2S", @@ -48,6 +49,7 @@ "wind_speed", "wind_direction" ) val result = mutableListOf<RealTimeDataVehicle>() try { ExcelUtil.read(file, headerCheck = { val cellIterator = it.cellIterator() @@ -65,13 +67,33 @@ } true }, onRow = { it.cellIterator().forEach { it.numericCellValue val data = RealTimeDataVehicle().apply { this.deviceCode = deviceCode dataTime = it.getCell(0).dateCellValue longitude = it.getCell(1).numericCellValue.toBigDecimal() latitude = it.getCell(2).numericCellValue.toBigDecimal() no2 = it.getCell(3).numericCellValue.toFloat() co = it.getCell(4).numericCellValue.toFloat() h2s = it.getCell(5).numericCellValue.toFloat() so2 = it.getCell(6).numericCellValue.toFloat() o3 = it.getCell(7).numericCellValue.toFloat() pm25 = it.getCell(8).numericCellValue.toFloat() pm10 = it.getCell(9).numericCellValue.toFloat() temperature = it.getCell(10).numericCellValue.toFloat() humidity = it.getCell(11).numericCellValue.toFloat() voc = it.getCell(12).numericCellValue.toFloat() noi = it.getCell(13).numericCellValue.toFloat() velocity = it.getCell(14).numericCellValue.toFloat() windSpeed = it.getCell(15).numericCellValue.toFloat() windDirection = it.getCell(16).numericCellValue.toFloat() } result.add(data) }) } catch (e: Exception) { e.printStackTrace() throw ResponseErrorException("excel文件内容错误，数据转换失败！", e) } return result } /** src/main/kotlin/com/flightfeather/uav/lightshare/service/MissionService.kt
@@ -10,4 +10,6 @@ fun createMission(mission: Mission): BaseResponse<Boolean> fun deleteMission(missionCode: String): BaseResponse<Boolean> fun deleteMissionAndData(missionCode: String): Boolean } src/main/kotlin/com/flightfeather/uav/lightshare/service/RealTimeDataService.kt
@@ -16,7 +16,9 @@ fun importData(file: MultipartFile): BaseResponse<DataImportResult> fun importJinanData(file: MultipartFile): BaseResponse<DataImportResult> fun importJinanData(code:String, file: MultipartFile): DataImportResult fun downloadTemplate(response: HttpServletResponse): Boolean fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): SXSSFWorkbook
@@ -1,7 +1,10 @@ package com.flightfeather.uav.lightshare.service.impl import com.flightfeather.uav.common.exception.ResponseErrorException import com.flightfeather.uav.domain.entity.Mission import com.flightfeather.uav.domain.entity.RealTimeDataVehicle import com.flightfeather.uav.domain.mapper.MissionMapper import com.flightfeather.uav.domain.mapper.RealTimeDataVehicleMapper import com.flightfeather.uav.lightshare.bean.BaseResponse import com.flightfeather.uav.lightshare.bean.DataHead import com.flightfeather.uav.lightshare.service.MissionService @@ -10,7 +13,10 @@ import tk.mybatis.mapper.entity.Example @Service class MissionServiceImpl(private val missionMapper: MissionMapper) : MissionService { class MissionServiceImpl( private val missionMapper: MissionMapper, private val realTimeDataVehicleMapper: RealTimeDataVehicleMapper, ) : MissionService { override fun getMission(type: String?, page: Int?, perPage: Int?): BaseResponse<List<Mission>> { val _perPage = perPage ?: 60 val _page = page ?: 1 @@ -39,4 +45,14 @@ return BaseResponse(it == 1) } } override fun deleteMissionAndData(missionCode: String): Boolean { val mission = missionMapper.selectByPrimaryKey(missionCode) ?: throw ResponseErrorException("任务不存在") missionMapper.deleteByPrimaryKey(missionCode) realTimeDataVehicleMapper.deleteByExample(Example(RealTimeDataVehicle::class.java).apply { createCriteria().andEqualTo("deviceCode", mission.deviceCode) .andBetween("dataTime", mission.startTime, mission.endTime) }) return true } } src/main/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImpl.kt
@@ -1,5 +1,6 @@ package com.flightfeather.uav.lightshare.service.impl import com.flightfeather.uav.common.exception.ResponseErrorException import com.flightfeather.uav.common.scaleMap import com.flightfeather.uav.common.utils.DateUtil import com.flightfeather.uav.common.utils.ExcelUtil @@ -17,10 +18,12 @@ import com.github.pagehelper.PageHelper import org.apache.poi.xssf.streaming.SXSSFWorkbook import org.springframework.beans.BeanUtils import org.springframework.beans.factory.annotation.Value import org.springframework.stereotype.Service import org.springframework.web.multipart.MultipartFile import tk.mybatis.mapper.entity.Example import java.io.ByteArrayInputStream import java.io.File import java.text.SimpleDateFormat import java.time.LocalDateTime import java.time.ZoneId @@ -38,14 +41,25 @@ private val realTimeDataUavMapper: RealTimeDataUavMapper, private val realTimeDataGridMapper: RealTimeDataGridMapper, private val realTimeDataGridOptMapper: RealTimeDataGridOptMapper, private val realTimeDataGridMinMapper: RealTimeDataGridMinMapper private val realTimeDataGridMinMapper: RealTimeDataGridMinMapper, private val missionMapper: MissionMapper, ) : RealTimeDataService { @Value("\${filePath}") lateinit var filePath: String private var dateFormatter = SimpleDateFormat("yyyy-MM-dd HH:mm:ss") private var dateFormatter2 = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm") private val fileExchange = FileExchange() override fun getSecondData(deviceCode: String?, startTime: String?, endTime: String?, type: Int?, page: Int?, perPage: Int?): BaseResponse<List<DataVo>> { override fun getSecondData( deviceCode: String?, startTime: String?, endTime: String?, type: Int?, page: Int?, perPage: Int?, ): BaseResponse<List<DataVo>> { val _perPage = perPage ?: 60 val _page = page ?: 1 val sTime = startTime?.let { dateFormatter.parse(it) } @@ -128,7 +142,12 @@ } } override fun getNextData(deviceCode: String, updateTime: String, page: Int?, perPage: Int?): BaseResponse<List<DataVo>> { override fun getNextData( deviceCode: String, updateTime: String, page: Int?, perPage: Int?, ): BaseResponse<List<DataVo>> { val _perPage = perPage ?: 60 val _page = page ?: 1 val pageInfo = PageHelper.startPage<RealTimeData>(_page, _perPage) @@ -170,11 +189,52 @@ fileExchange.exchangeBoatData("0c0000000001", f).forEach { realTimeDataMapper.insert(it) } return BaseResponse(true, data = DataImportResult("")) return BaseResponse(true, data = DataImportResult("成功")) } override fun importJinanData(file: MultipartFile): BaseResponse<DataImportResult> { TODO("Not yet implemented") override fun importJinanData(code: String, file: MultipartFile): DataImportResult { val f = ByteArrayInputStream(file.bytes) val result = fileExchange.exchangeJinanData(code, f) if (result.isNotEmpty()) { val first = result.first() val t = DateUtil.instance.dateToString(first.dataTime, DateUtil.DateStyle.YYYY_MM_DD) val last = result.last() val mission = Mission().apply { val tag = code.substring(0, 2) + "-" + code.substring(code.length - 2, code.length) missionCode = "SH-JA-${tag}-${t}" deviceType = UWDeviceType.getType(code)?.value deviceCode = code } missionMapper.selectOne(mission)?.run { throw ResponseErrorException("该设备该时段任务已存在，无法重复导入") } mission.apply { startTime = first.dataTime endTime = last.dataTime } missionMapper.insert(mission) realTimeDataVehicleMapper.insertList(result) } return DataImportResult("成功") } override fun downloadTemplate(response: HttpServletResponse): Boolean { val fileName = "JinAn-Template.xlsx" val path = (Thread.currentThread().contextClassLoader?.getResource("/")?.path ?: "src/main/resources") + "/templates/" + fileName val file = File(path) if (file.exists()) { val fName = Base64.getEncoder().encodeToString(fileName.toByteArray()) response.apply { setHeader("Content-Disposition", "attachment;filename=$fName") setHeader("fileName", fName) addHeader("Access-Control-Expose-Headers", "fileName") contentType = "application/vnd.ms-excel;charset=UTF-8" setHeader("Pragma", "no-cache") setHeader("Cache-Control", "no-cache") setDateHeader("Expires", 0) } response.outputStream.write(file.readBytes()) } return true } override fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): SXSSFWorkbook { @@ -196,13 +256,19 @@ }) if (r.isNotEmpty()) { val heads = if (page == 1) { println("[${DateUtil.instance.dateToString(Date(), DateUtil.DateStyle.YYYY_MM_DD_HH_MM_SS)}] totalPage: ${pageInfo.pages}") println("[${ DateUtil.instance.dateToString(Date(), DateUtil.DateStyle.YYYY_MM_DD_HH_MM_SS) }] totalPage: ${pageInfo.pages}") getTableTitle(r[0]) } else { emptyList() } val contents = getTableContents(r) print("[${DateUtil.instance.dateToString(Date(), DateUtil.DateStyle.YYYY_MM_DD_HH_MM_SS)}] currentPage: ${pageInfo.pageNum}......") print("[${ DateUtil.instance.dateToString(Date(), DateUtil.DateStyle.YYYY_MM_DD_HH_MM_SS) }] currentPage: ${pageInfo.pageNum}......") rowIndex = ExcelUtil.write(heads, contents, workbook, row = rowIndex) println("output done") } @@ -212,7 +278,12 @@ return workbook } override fun outToExcel(deviceCode: String, startTime: String, endTime: String, response: HttpServletResponse): HttpServletResponse { override fun outToExcel( deviceCode: String, startTime: String, endTime: String, response: HttpServletResponse, ): HttpServletResponse { val workbook = outToWorkbook(deviceCode, startTime, endTime) val out = response.outputStream @@ -236,7 +307,13 @@ return contents } override fun getOriginData(deviceCode: String?, startTime: String?, endTime: String?, page: Int?, perPage: Int?): BaseResponse<List<DataVo>> { override fun getOriginData( deviceCode: String?, startTime: String?, endTime: String?, page: Int?, perPage: Int?, ): BaseResponse<List<DataVo>> { val _perPage = perPage ?: 60 val _page = page ?: 1 val sTime = startTime?.let { dateFormatter.parse(it) } @@ -296,9 +373,9 @@ var count = 0 val minFormatter = SimpleDateFormat("yyyy-MM-dd HH:mm") val averageUtil = AverageUtil<RealTimeDataGridOpt, RealTimeDataGridMin>({d -> val averageUtil = AverageUtil<RealTimeDataGridOpt, RealTimeDataGridMin>({ d -> minFormatter.format(d.dataTime) },{list -> }, { list -> list.avg() })
@@ -26,4 +26,9 @@ fun deleteMission( @RequestParam("missionCode") missionCode: String ) = missionService.deleteMission(missionCode) @PostMapping("/delete/data/vehicle") fun deleteMissionAndData( @RequestParam("missionCode") missionCode: String ) = resPack { missionService.deleteMissionAndData(missionCode) } } src/main/kotlin/com/flightfeather/uav/lightshare/web/RealTimeDataController.kt
@@ -6,6 +6,8 @@ import io.swagger.annotations.ApiParam import org.springframework.web.bind.annotation.* import org.springframework.web.multipart.MultipartFile import springfox.documentation.annotations.ApiIgnore import javax.servlet.http.HttpServletResponse @Api(tags = ["走航监测数据API接口"]) @RestController @@ -17,9 +19,10 @@ @RequestParam(value = "deviceCode", required = false) deviceCode: String?, @RequestParam(value = "startTime", required = false) startTime: String?, @RequestParam(value = "endTime", required = false) endTime: String?, @ApiParam(value = "0: 秒级值; 1:分钟值", defaultValue = "0") @RequestParam(value = "type", required = false) type: Int?, @ApiParam(value = "0: 秒级值; 1:分钟值", defaultValue = "0") @RequestParam(value = "type", required = false) type: Int?, @RequestParam(value = "page", required = false) page: Int?, @RequestParam(value = "perPage", required = false) perPage: Int? @RequestParam(value = "perPage", required = false) perPage: Int?, ) = realTimeDataService.getSecondData(deviceCode, startTime, endTime, type, page, perPage) @GetMapping("/sec/next") @@ -27,17 +30,22 @@ @RequestParam(value = "deviceCode") deviceCode: String, @RequestParam(value = "updateTime") updateTime: String, @RequestParam(value = "page", required = false) page: Int?, @RequestParam(value = "perPage", required = false) perPage: Int? @RequestParam(value = "perPage", required = false) perPage: Int?, ) = realTimeDataService.getNextData(deviceCode, updateTime, page, perPage) @PostMapping("/import") fun importData( @RequestPart("excel") file: MultipartFile @RequestPart("excel") file: MultipartFile, ) = realTimeDataService.importData(file) @ApiOperation(value = "导入静安区生态环境监测站的走行数据") @PostMapping("/import/jinan") fun importJinanData( @RequestPart("excel") file: MultipartFile ) = realTimeDataService.importJinanData(file) @ApiParam("设备id") @RequestParam("code") code: String, @RequestPart("excel") file: MultipartFile, ) = resPack { realTimeDataService.importJinanData(code, file) } @ApiOperation(value = "下载静安区生态环境监测站走行数据导入模板") @PostMapping("/import/jinan/download/template") fun downloadTemplate(@ApiIgnore response: HttpServletResponse) = realTimeDataService.downloadTemplate(response) } src/main/resources/application-dev.yml
@@ -11,4 +11,5 @@ v2: enabled: true imgPath: target/ filePath: target/ src/main/resources/application-pro.yml
@@ -1,6 +1,11 @@ spring: datasource: # 线上服务器 # 线上服务器 47 # url: jdbc:mysql://localhost:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false # username: dronemonitor # password: dronemonitor_hackxrnomxm # 线上服务器 114 url: jdbc:mysql://localhost:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false username: dronemonitor password: dronemonitor_hackxrnomxm @@ -11,4 +16,5 @@ v2: enabled: false imgPath: D:/02product/10underway/images/ filePath: D:/02product/10underway/files/ src/main/resources/application-test.yml
@@ -17,4 +17,5 @@ v2: enabled: true imgPath: D:/02product/10underway/images/ filePath: D:/02product/10underway/files/ src/main/resources/application.yml
@@ -6,6 +6,8 @@ minimum-idle: 20 idle-timeout: 60000 connection-timeout: 60000 profiles: active: @profileActive@ jmx: enabled: false Binary files differ
