1. 新增网格化数据统计功能

riku

2021-10-09 01ac08186355e85cab4a7c6f4403180184894f23

1. 新增网格化数据统计功能

 pom.xml

@@ -119,6 +119,14 @@
            <version>4.1.2</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>

 src/main/kotlin/com/flightfeather/uav/common/utils/ExcelUtil.kt

@@ -2,6 +2,7 @@

import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import java.time.LocalDate
import java.util.*
import kotlin.math.max
@@ -24,7 +25,7 @@
    /**
     * è‡ªåŠ¨å¤„理行合并数据
     */
    fun write2(heads: List<String>, contents: List<Array<Any>>, workbook: HSSFWorkbook, sheetName:String) {
    fun write2(heads: List<String>, contents: List<Array<Any>>, workbook: SXSSFWorkbook, sheetName:String) {

        val sheet = workbook.createSheet(sheetName)

@@ -152,12 +153,12 @@
    /**
     * è‡ªåŠ¨å¤„理行合并数据
     */
    fun write(heads: List<Array<String>>, contents: List<Array<Any>>, workbook: HSSFWorkbook, sheetName: String = "sheet1") {
    fun write(heads: List<Array<String>>, contents: List<Array<Any>>, workbook: SXSSFWorkbook, sheetName: String = "sheet1", row: Int = 0): Int {

        val sheet = workbook.createSheet(sheetName)
        println("sheet: $sheetName")
        val sheet = workbook.getSheet(sheetName)?: workbook.createSheet(sheetName)
//        println("sheet: $sheetName")

        var rowIndex = 0
        var rowIndex = row

        heads.forEach {
            val rows = sheet.createRow(rowIndex)
@@ -202,7 +203,7 @@
                            cell[0]
                        }
                    } else {
                        //当数据不是数组时，需要按最大行数合并单元格
                        //当数据不是数组时，根据设置合并
                        if (cell is MyCell) {
                            rowspan = cell.rowSpan
                            colSpan = cell.colSpan
@@ -299,6 +300,7 @@
//        workbook.close()
//        out.flush()
//        out.close()
        return rowIndex
    }

    private fun getMaxRows(rowArray: Array<Any>): Int {

 src/main/kotlin/com/flightfeather/uav/common/utils/FileExchange.kt

@@ -4,6 +4,7 @@
import com.flightfeather.uav.domain.entity.RealTimeData
import com.flightfeather.uav.socket.bean.AirData
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import java.io.File
import java.io.FileInputStream
import java.io.FileOutputStream
@@ -153,7 +154,7 @@
        }


        val newWorkBook = HSSFWorkbook()
        val newWorkBook = SXSSFWorkbook(10000)
        ExcelUtil.write2(heads, contents, newWorkBook, "data")

        newWorkBook.write(out)
@@ -242,7 +243,7 @@
            contents.add(cList.toTypedArray())
        }

        val newWorkBook = HSSFWorkbook()
        val newWorkBook = SXSSFWorkbook(10000)
        ExcelUtil.write2(heads, contents, newWorkBook, "data")

        newWorkBook.write(out)

 src/main/kotlin/com/flightfeather/uav/common/utils/FileUtil.kt

@@ -36,7 +36,7 @@
        }
    }

    fun saveObdData(str: String) {
    fun saveObdData(str: String, newFile: Boolean = false) {
        //检查文档是否存在
        if (!file.exists()) {
            file.parentFile.mkdirs()
@@ -45,7 +45,7 @@
            println("----创建文件：${file.absolutePath}")
        }
        //文件最大512Kb,超过后新建文档
        if (file.length() + str.toByteArray().size > 512 * 1024 || TimeUtil.isNextDay(oldTime, Date())) {
        if (newFile || file.length() + str.toByteArray().size > 512 * 1024 || TimeUtil.isNextDay(oldTime, Date())) {
            //超过一天后，更新当前时间
            oldTime = Date()

@@ -59,10 +59,10 @@
                flush()
                close()
            }
            fw?.run {
                flush()
                close()
            }
//            fw?.run {
//                flush()
//                close()
//            }
            //新建输出流
            fw = FileWriter(file, true)
            bw = BufferedWriter(fw)
@@ -79,7 +79,7 @@
            flush()
        }

        readyToShutDownStream(bw, fw)
//        readyToShutDownStream(bw, fw)
        println("----写入完成")

    }

 src/main/kotlin/com/flightfeather/uav/dataprocess/DataProcess.kt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,176 @@
package com.flightfeather.uav.dataprocess

import com.flightfeather.uav.common.utils.DateUtil
import com.flightfeather.uav.common.utils.ExcelUtil
import com.flightfeather.uav.lightshare.bean.DataVo
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import java.io.File
import java.io.FileInputStream
import java.io.FileOutputStream
import java.util.*

/**
 * æ•°æ®å¤„理
 * ç›®æ ‡ï¼šå°æ—¶æ•°æ®ã€æ—¥æ•°æ®å’Œæ—¥å˜åŒ–3ç±»
 * æŒ‡æ ‡ï¼šå¯¹æ¯ç±»æ•°æ®ï¼Œè®¡ç®—记录数、均值、标准差、最小值、最大值；如果计算量可接受，计算10/25/50/75/90  %分位值。
 * è¾“出样式：（列数据）时间、记录数、均值、标准差、最小值、最大值，分位值；时间可以是yyyymmddhh…简化格式或标准时间格式；小时数据标记后记（时间记在后，如17时表示16~17时之间的结果）或前记方式，日变化的小时亦同。
 */
class DataProcess(
    private val outPutPath: String
) {
    companion object {
        const val SHEET1 = "小时数据"
        const val SHEET2 = "日数据"
        const val SHEET3 = "日变化"
    }
    //每日小时数据
    private val dateData = mutableMapOf<String?, PreData>()
    //每日数据
    private val dayData = mutableMapOf<String?, PreData>()
    //日小时变化数据
    private val dailyVariationData = mutableMapOf<String?, PreData>()

    //写入excel文件
    private var workBook = SXSSFWorkbook(1000)
    //输出流
    private lateinit var out: FileOutputStream
    //输入流
    private lateinit var input: FileInputStream
    //excel æ¯å¼ sheet当前写入的行数
    private val sheetRow = mutableMapOf<String, Int>().apply {
        put(SHEET1, 0)
        put(SHEET2, 0)
        put(SHEET3, 0)
    }

    init {
        outPutHead()
    }

    /**
     * æ‰§è¡Œæ•°æ®é¢„处理
     * 1. å•æ¬¡è¾“入的数据要求为一天的数据，输入后会直接计算“每日小时数据”和“每日数据”的结果并写入excel
     * 2. æ—¥å°æ—¶å˜åŒ–数据由于跨日统计，需要手动调用
     */
    fun process(dataList: List<DataVo>) {
        dateData.clear()
        dayData.clear()
//        dailyVariationData.clear()
        dataList.forEach {
            val date = it.time?.substring(0, 13)
            val day = it.time?.substring(0, 10)
            val hour = it.time?.substring(11, 13)

            if (!dateData.containsKey(date)) {
                dateData[date] = PreData(date)
            }
            dateData[date]?.add(it)

            if (!dayData.containsKey(day)) {
                dayData[day] = PreData(day)
            }
            dayData[day]?.add(it)

            if (!dailyVariationData.containsKey(hour)) {
                dailyVariationData[hour] = PreData(hour, false)
            }
            dailyVariationData[hour]?.add(it)
        }

        outPut()
    }

    /**
     * è¾“出文档标题
     */
    private fun outPutHead() {
        val file = File(outPutPath)
        out = FileOutputStream(file)

        val contents = mutableListOf<Array<Any>>()
        //第一行为时间以及监测因子名称，并且需要根据下一行的统计项合并列
        val firstRow = mutableListOf<ExcelUtil.MyCell>()
        firstRow.add(ExcelUtil.MyCell("时间", 2))
        PreData.needFactor.forEach { firstRow.add(ExcelUtil.MyCell(it.des, colSpan = PreData.items.size)) }
        contents.add(firstRow.toTypedArray())
        //第二行为每种监测因子对应的统计项，具体为记录数、均值、标准差、最小值、最大值、10/25/50/75/90%分位值，每种监测因子有10列
        val secondRow = mutableListOf<String>()
        secondRow.add("")
        repeat(PreData.needFactor.size) { secondRow.addAll(PreData.items) }
        contents.add(secondRow.toTypedArray())
        //分sheet建立标题
        sheetRow.forEach { (s, i) ->
            val row = ExcelUtil.write(emptyList(), contents, workBook, s, i)
            sheetRow[s] = row
        }
        workBook.write(out)
        workBook.close()
        out.flush()
        out.close()
    }

    /**
     * å†™å…¥æ¯æ—¥å°æ—¶æ•°æ®å’Œæ¯æ—¥æ•°æ®ç»“æžœ
     * è‡ªåŠ¨è°ƒç”¨ï¼Œ è¦æ±‚输入的数据以每日为一组
     */
    private fun outPut() {
        input = FileInputStream(outPutPath)
        workBook = SXSSFWorkbook(XSSFWorkbook(input), 1000)
        out = FileOutputStream(outPutPath)
        //第三行开始为计算结果，每种监测因子都有对应的一系列统计项

        //每日小时数据
        val contents = mutableListOf<Array<Any>>()
        dateData.forEach { (_, pData) ->
            contents.add(pData.getOutPutContent())
        }
        sheetRow[SHEET1] = ExcelUtil.write(emptyList(), contents, workBook, SHEET1, sheetRow[SHEET1] ?: 0)

        //每日数据
        contents.clear()
        dayData.forEach { (_, pData) ->
            contents.add(pData.getOutPutContent())
        }
        sheetRow[SHEET2] = ExcelUtil.write(emptyList(), contents, workBook, SHEET2, sheetRow[SHEET2] ?: 0)

        workBook.write(out)
        workBook.close()
        input.close()
        out.flush()
        out.close()
    }

    /**
     * å†™å…¥æ—¥å°æ—¶å˜åŒ–数据
     * æ‰‹åŠ¨è°ƒç”¨
     */
    fun outPutDailyVariation() {
        input = FileInputStream(outPutPath)
        workBook = SXSSFWorkbook(XSSFWorkbook(input), 1000)
        out = FileOutputStream(outPutPath)
        //日小时变化数据
        val contents = mutableListOf<Array<Any>>()
        dailyVariationData.forEach { (_, pData) ->
            contents.add(pData.getOutPutContent())
        }
        sheetRow[SHEET3] = ExcelUtil.write(emptyList(), contents, workBook, SHEET3, sheetRow[SHEET3] ?: 0)

        workBook.write(out)
        workBook.close()
        input.close()
        out.flush()
        out.close()
    }

    /**
     * æ•°æ®å¤„理结束
     * è°ƒç”¨ä»¥å…³é—­è¾“出流，释放内存
     */
    fun done() {
        workBook.close()
        input.close()
        out.flush()
        out.close()
    }
}

 src/main/kotlin/com/flightfeather/uav/dataprocess/PreData.kt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,143 @@
package com.flightfeather.uav.dataprocess

import com.flightfeather.uav.lightshare.bean.DataVo
import com.flightfeather.uav.socket.eunm.FactorType
import java.math.BigDecimal
import kotlin.math.max
import kotlin.math.min
import kotlin.math.round
import kotlin.math.sqrt

/**
 * é¢„处理数据
 */
class PreData(
    //统计时间
    private val time: String?,
    //是否需要计算分位值
    private val needQuartile: Boolean = true
) {

    inner class TempData {
        //数据平方和（用于计算标准差）
        var sumOfSquares: BigDecimal = BigDecimal.ZERO
        //数据和
        var total: BigDecimal = BigDecimal.ZERO

        //计数
        var count: Int = 0
        var minV: Double = Double.MAX_VALUE
        var maxV: Double = Double.MIN_VALUE

        //数据值记录，按照升序排列，用于获取分位值
        // "10%分位值",
        // "25%分位值",
        // "50%分位值",
        // "75%分位值",
        // "90%分位值"
        val dataList = mutableListOf<Double>()
    }

    companion object {
        //需要统计的监测因子
        val needFactor = listOf(
//            FactorType.NO2, FactorType.CO, FactorType.H2S, FactorType.SO2, FactorType.O3, FactorType.PM25, FactorType.PM10,
            FactorType.VOC
        )
        //需要计算的项目
        val items = listOf(
            "记录数", "均值", "标准差", "最小值", "最大值", "10%分位值", "25%分位值", "50%分位值", "75%分位值", "90%分位值"
        )
    }

    //按照每种监测因子记录当前时间下的统计中间数据
    private val dataMap = mutableMapOf<String?, TempData>()

    fun add(data: DataVo) {
        data.values?.forEach {
            if (!dataMap.containsKey(it.factorName)) {
                dataMap[it.factorName] = TempData()
            }
            dataMap[it.factorName]?.apply {
                sumOfSquares = sumOfSquares.plus(BigDecimal.valueOf(
                    (it.factorData ?: .0) * (it.factorData ?: .0)
                ))
                total = total.plus(BigDecimal.valueOf(it.factorData ?: .0))
                count++
                minV = min(minV, it.factorData?: .0)
                maxV = max(maxV, it.factorData?: .0)

                if (needQuartile) {
                    dataList.add(it.factorData ?: .0)
                }
            }
        }
    }

    /**
     * è¾“出当前统计时间下的一行统计数据
     */
    fun getOutPutContent(): Array<Any> {
        //每种监测因子都有对应的一系列统计项
        val content = mutableListOf<Any>()
        //第一列是统计时间
        content.add(time ?: "错误时间")
        needFactor.forEach {
            dataMap[it.name]?.let {t ->
                content.apply {
                    //"记录数",
                    val c = BigDecimal("${t.count}")
                    add(t.count)
                    // "均值",
                    val avg = t.total.div(c)
                    add(avg.toString())
                    // "标准差",
                    add(
                        sqrt(
                            t.sumOfSquares.minus(
                                avg.times(BigDecimal(2)).times(t.total)
                            ).plus(
                                avg.times(avg).times(c)
                            ).div(c).toDouble()
                        ).toString()
                    )
                    // "最小值",
                    add(t.minV)
                    // "最大值",
                    add(t.maxV)
                    //分位值
                    if (needQuartile) {
                        t.dataList.sortBy { d -> d }
                        val size = t.dataList.size
                        // "10%分位值",
                        add(t.dataList[round(size * .1).toInt()])
                        // "25%分位值",
                        add(t.dataList[round(size * .25).toInt()])
                        // "50%分位值",
                        add(t.dataList[round(size * .5).toInt()])
                        // "75%分位值",
                        add(t.dataList[round(size * .75).toInt()])
                        // "90%分位值"
                        add(t.dataList[round(size * .9).toInt()])
                    } else {
                        add("")
                        add("")
                        add("")
                        add("")
                        add("")
                    }
                }
            }
        }

        clear()
        return content.toTypedArray()
    }

    /**
     * é‡Šæ”¾å†…å­˜
     */
    private fun clear() {
        dataMap.clear()
    }
}

 src/main/kotlin/com/flightfeather/uav/domain/entity/ExpandFun.kt

@@ -3,6 +3,7 @@
import com.flightfeather.uav.common.utils.DateUtil
import com.flightfeather.uav.common.utils.GsonUtils
import com.flightfeather.uav.socket.bean.AirData
import com.flightfeather.uav.socket.eunm.FactorType

/**
 * æ•°æ®åº“表实体扩展方法
@@ -15,9 +16,11 @@
    list.add("纬度")
    val values = GsonUtils.parserJsonToArrayBeans(factors, AirData::class.java)
    values.forEach {
        if (FactorType.outputFactor(it.factorName)) {
        val name = it.factorName ?: ""
        list.add(name)
        list.add("$name(物理量)")
//            list.add("$name(物理量)")
        }
    }
    return list.toTypedArray()
}
@@ -38,8 +41,10 @@
    }
    val values = GsonUtils.parserJsonToArrayBeans(factors, AirData::class.java)
    values.forEach {
        if (FactorType.outputFactor(it.factorName)) {
        row.add(it.factorData ?: -1.0)
        row.add(it.physicalQuantity ?: -1.0)
//            row.add(it.physicalQuantity ?: -1.0)
        }
    }
    return row.toTypedArray()
}

 src/main/kotlin/com/flightfeather/uav/lightshare/service/RealTimeDataService.kt

@@ -4,6 +4,7 @@
import com.flightfeather.uav.lightshare.bean.DataImportResult
import com.flightfeather.uav.lightshare.bean.DataVo
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.springframework.web.multipart.MultipartFile
import javax.servlet.http.HttpServletResponse

@@ -15,7 +16,7 @@

    fun importData(file: MultipartFile): BaseResponse<DataImportResult>

    fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): HSSFWorkbook
    fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): SXSSFWorkbook

    fun outToExcel(deviceCode: String, startTime: String, endTime: String, response: HttpServletResponse): HttpServletResponse
}

 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.utils.DateUtil
import com.flightfeather.uav.common.utils.ExcelUtil
import com.flightfeather.uav.common.utils.FileExchange
import com.flightfeather.uav.common.utils.GsonUtils
@@ -11,15 +12,13 @@
import com.flightfeather.uav.lightshare.service.RealTimeDataService
import com.flightfeather.uav.socket.bean.AirData
import com.github.pagehelper.PageHelper
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.springframework.stereotype.Service
import org.springframework.web.multipart.MultipartFile
import tk.mybatis.mapper.entity.Example
import java.io.ByteArrayInputStream
import java.io.FileInputStream
import java.io.InputStream
import java.text.DateFormat
import java.text.SimpleDateFormat
import java.util.*
import javax.servlet.http.HttpServletResponse

@Service
@@ -48,11 +47,14 @@
                }
            }
        }).forEach {
            if (it.longitude == null || it.latitude == null) {
                return@forEach
            }
            result.add(DataVo(
                    dateFormatter.format(it.dataTime),
                    it.deviceCode,
                    GsonUtils.parserJsonToArrayBeans(it.factors, AirData::class.java),
                    it.longitude.toDouble(), it.latitude.toDouble()
                    it.longitude?.toDouble(), it.latitude?.toDouble()
            ))
        }
        if (startTime == null && endTime == null) {
@@ -89,9 +91,16 @@
        return BaseResponse(true, data = DataImportResult(""))
    }

    override fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): HSSFWorkbook {
    override fun outToWorkbook(deviceCode: String, startTime: String, endTime: String): SXSSFWorkbook {
        val sTime = dateFormatter.parse(startTime)
        val eTime = dateFormatter.parse(endTime)
        var page = 1
        var totalPage = 1
        val pageSize = 10000
        val workbook = SXSSFWorkbook()
        var rowIndex = 0
        while (page <= totalPage) {
            val pageInfo = PageHelper.startPage<RealTimeData>(page, pageSize)
        val r = realTimeDataMapper.selectByExample(Example(RealTimeData::class.java).apply {
            createCriteria().andEqualTo("deviceCode", deviceCode)
                .apply {
@@ -99,12 +108,20 @@
                    eTime?.let { andLessThanOrEqualTo("dataTime", it) }
                }
        })
        val workbook = HSSFWorkbook()
        if (r.isNotEmpty()) {
            val heads = getTableTitle(r[0])
                val heads = if (page == 1) {
                    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)

            ExcelUtil.write(heads, contents, workbook)
                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")
            }
            totalPage = pageInfo.pages
            page++
        }
        return workbook
    }

 src/main/kotlin/com/flightfeather/uav/model/BaseModel.kt

@@ -4,6 +4,7 @@
import com.flightfeather.uav.common.utils.ExcelUtil
import com.flightfeather.uav.socket.eunm.FactorType
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import java.io.File
import java.io.FileOutputStream
import java.util.*
@@ -88,14 +89,14 @@

    fun outputToExcel(
        fName: String? = null,
        _workbook: HSSFWorkbook? = null,
        _workbook: SXSSFWorkbook? = null,
        _out: FileOutputStream? = null,
        sheetName: String = "sheet1",
        done: Boolean = true
    ): Pair<HSSFWorkbook, FileOutputStream>? {
    ): Pair<SXSSFWorkbook, FileOutputStream>? {
//        val rMap = formatConversion()

        val workbook = _workbook ?: HSSFWorkbook()
        val workbook = _workbook ?: SXSSFWorkbook()
        val fileName = fName ?: "污染溯源权重模型${DateUtil.instance.dateToString(Date(), "yyyy-MM-ddHHmmss")}.xls"
//        val filePath = "E:\\work\\export\\$fileName"
//        val filePath = "E:\\工作\\开发\\走航监测\\算法相关\\自动输出\\$fileName"

 src/main/resources/application.yml

@@ -7,9 +7,9 @@
#    password: cn.FLIGHTFEATHER

#    çº¿ä¸ŠæœåŠ¡å™¨
    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
#    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

#    å¼€å‘本地服务器
#    url: jdbc:mysql://localhost:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
@@ -17,9 +17,9 @@
#    password: 123456

#   å¼€å‘远程服务器
#    url: jdbc:mysql://47.100.191.150:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
#    username: remoteU1
#    password: eSoF8DnzfGTlhAjE
    url: jdbc:mysql://47.100.191.150:3306/dronemonitor?serverTimezone=Asia/Shanghai&prepStmtCacheSize=517&cachePrepStmts=true&autoReconnect=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
    username: remoteU1
    password: eSoF8DnzfGTlhAjE
    hikari:
      maximum-pool-size: 500
      minimum-idle: 20

 src/test/kotlin/com/flightfeather/uav/Test.kt

@@ -1,6 +1,7 @@
package com.flightfeather.uav

import com.flightfeather.uav.common.utils.FileExchange
import com.flightfeather.uav.common.utils.FileUtil
import com.flightfeather.uav.domain.entity.Company
import com.flightfeather.uav.socket.bean.DataUnit
import com.flightfeather.uav.socket.decoder.AirDataDecoder
@@ -10,6 +11,7 @@
import java.io.File
import java.io.FileOutputStream
import java.io.OutputStreamWriter
import java.math.BigDecimal
import java.text.SimpleDateFormat
import java.util.*

@@ -69,7 +71,7 @@

    @Test
    fun get_crc16(){
        val dataSegment = "QN=20210713115502858;ST=22;CN=2011;PW=555555;MN=FYHB0MH0300045;Flag=1;CP=&&DataTime=20210713115000;a34001-Avg=0.025,a34001-CPM=5.9,a34001-Flag=N;a50001-Avg=0.0,a50001-Flag=D;a01001-Avg=33.0,a01001-Flag=N;a01002-Avg=68.2,a01002-Flag=N;a01007-Avg=0.0,a01007-Flag=N;a01008-Avg=0.0,a01008-Flag=N;Period=5;Scale=0.0;SelfTemp=0.0;SelfHum=0.0;IsReplacement=N&&"
        val dataSegment = "QN=20210713133901044;ST=22;CN=2011;PW=555555;MN=FYHB0DT0100001;Flag=1;CP=&&DataTime=20210713133800;a34001-Avg=0.017,a34001-CPM=3.9,a34001-Flag=N;a50001-Avg=71.1,a50001-Flag=N;a01001-Avg=34.0,a01001-Flag=N;a01002-Avg=59.3,a01002-Flag=N;a01007-Avg=0.6,a01007-Flag=N;a01008-Avg=256.3,a01008-Flag=N;Period=1;Scale=1.0;SelfTemp=0.0;SelfHum=0.0;IsReplacement=N&&"
        var CRC = 0x0000ffff
        val POLYNOMIAL = 0x0000a001
        var i: Int
@@ -100,4 +102,27 @@
        }
        println(strCRC)
    }

    @Test
    fun foo15() {
        var i = 0
        do {
            if (i == 3) {
                FileUtil.instance?.saveObdData("msg", true)
            } else {
                FileUtil.instance?.saveObdData("msg")
            }
            i++
        } while (i < 10)
    }

    @Test
    fun foo16() {
        val l = mutableListOf<BigDecimal>().apply {
            add(BigDecimal.valueOf(6.23))
            add(BigDecimal("6.23"))
            add(BigDecimal(6.23))
        }
        l.forEach { println(it) }
    }
}

 src/test/kotlin/com/flightfeather/uav/dataprocess/DataProcessTest.kt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,67 @@
package com.flightfeather.uav.dataprocess

import com.flightfeather.uav.common.utils.DateUtil
import com.flightfeather.uav.common.utils.ExcelUtil
import com.flightfeather.uav.domain.entity.RealTimeData
import com.flightfeather.uav.domain.mapper.RealTimeDataMapper
import com.flightfeather.uav.lightshare.service.RealTimeDataService
import com.github.pagehelper.PageHelper
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.junit.Assert.*
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringRunner
import tk.mybatis.mapper.entity.Example
import java.text.SimpleDateFormat
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.*

@RunWith(SpringRunner::class)
@SpringBootTest
class DataProcessTest {

    @Autowired
    lateinit var realTimeDataMapper: RealTimeDataMapper

    @Autowired
    lateinit var realTimeDataService: RealTimeDataService

    @Test
    fun dataProcess() {
        val fileName = "网格化监测数据预处理${Date().time}.xls"
        val filePath = "C:\\work\\工作\\走航监测\\走航监测\\$fileName"
//        val filePath = "E:\\工作\\开发\\走航监测\\数据预处理\\$fileName"
        val process = DataProcess(filePath)

        /**
         * æŒ‰æ¯æ—¥é—´éš”循环获取数据，进行数处理
         * æ•°æ®ä¸º4秒钟1个值，因此一天的数据量最多为21600个
         */
        //截止时间
        val endDateTime = LocalDateTime.of(2021, 10, 5, 10, 0, 0)
        var sTime = LocalDateTime.of(2021, 6, 19, 0, 0, 0)
        var eTime = LocalDateTime.of(2021, 6, 19, 23, 59, 59)

        while (sTime.isBefore(endDateTime)) {
            if (eTime.isAfter(endDateTime)) {
                eTime = endDateTime
            }
            val deviceCode = "0d0000000001"
            val dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
            val s = sTime.format(dateFormatter)
            val e = eTime.format(dateFormatter)
            val data = realTimeDataService.getSecondData(deviceCode, s, e, 1, 30000).data
            data?.let {
                process.process(it)
            }
            sTime = sTime.plusDays(1)
            eTime = eTime.plusDays(1)
        }

        process.outPutDailyVariation()
        process.done()
    }
}

 src/test/kotlin/com/flightfeather/uav/lightshare/service/impl/RealTimeDataServiceImplTest.kt

@@ -1,7 +1,12 @@
package com.flightfeather.uav.lightshare.service.impl

import com.flightfeather.uav.common.utils.DateUtil
import com.flightfeather.uav.common.utils.ExcelUtil
import com.flightfeather.uav.dataprocess.DataProcess
import com.flightfeather.uav.domain.entity.RealTimeData
import com.flightfeather.uav.lightshare.service.RealTimeDataService
import com.github.pagehelper.PageHelper
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.junit.Test

import org.junit.Assert.*
@@ -9,6 +14,7 @@
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringRunner
import tk.mybatis.mapper.entity.Example
import java.io.File
import java.io.FileOutputStream
import java.util.*
@@ -23,8 +29,84 @@
    @Test
    fun outToExcel() {
        if (realTimeDataService is RealTimeDataServiceImpl) {
            val workbook = realTimeDataService.outToWorkbook("0a0000000001", "2021-07-05 00:00:00", "2021-07-06 23:59:59")
            val fileName = "污染溯源权重模型${DateUtil.instance.dateToString(Date(), "yyyy-MM-ddHHmmss")}.xls"
//            val deviceCode = "0a0000000001"
//            val deviceCode = "0a0000000002"
            val deviceCode = "0d0000000001"
            val timeSet = listOf(
//                Pair("2021-06-10 00:00:00", "2021-06-30 23:59:59"),
                Pair("2021-07-01 00:00:00", "2021-07-31 23:59:59"),
                Pair("2021-08-01 00:00:00", "2021-08-31 23:59:59"),
                Pair("2021-09-01 00:00:00", "2021-09-30 23:59:59"),
//            ç½‘格化监测每日
//                Pair("2021-06-02 00:00:00", "2021-06-03 23:59:59"),
//                Pair("2021-06-04 00:00:00", "2021-06-05 23:59:59"),
//                Pair("2021-06-06 00:00:00", "2021-06-07 23:59:59"),
//                Pair("2021-06-08 00:00:00", "2021-06-09 23:59:59"),
//                Pair("2021-06-10 00:00:00", "2021-06-11 23:59:59"),
//                Pair("2021-06-12 00:00:00", "2021-06-13 23:59:59"),
//                Pair("2021-06-14 00:00:00", "2021-06-15 23:59:59"),
//                Pair("2021-06-16 00:00:00", "2021-06-17 23:59:59"),
//                Pair("2021-06-18 00:00:00", "2021-06-19 23:59:59"),
//                Pair("2021-06-20 17:00:00", "2021-06-21 23:59:59"),
//                Pair("2021-06-22 00:00:00", "2021-06-23 23:59:59"),
//                Pair("2021-06-24 00:00:00", "2021-06-25 23:59:59"),
//                Pair("2021-06-26 00:00:00", "2021-06-27 23:59:59"),
//                Pair("2021-06-28 00:00:00", "2021-06-29 23:59:59"),
//                Pair("2021-06-30 00:00:00", "2021-06-30 23:59:59"),

//                Pair("2021-07-01 00:00:00", "2021-07-02 23:59:59"),
//                Pair("2021-07-03 00:00:00", "2021-07-04 23:59:59"),
//                Pair("2021-07-05 00:00:00", "2021-07-06 23:59:59"),
//                Pair("2021-07-07 00:00:00", "2021-07-08 23:59:59"),
//                Pair("2021-07-09 00:00:00", "2021-07-10 23:59:59"),
//                Pair("2021-07-11 00:00:00", "2021-07-12 23:59:59"),
//                Pair("2021-07-13 00:00:00", "2021-07-14 23:59:59"),
//                Pair("2021-07-15 00:00:00", "2021-07-16 23:59:59"),
//                Pair("2021-07-17 00:00:00", "2021-07-18 23:59:59"),
//                Pair("2021-07-19 17:00:00", "2021-07-20 23:59:59"),
//                Pair("2021-07-21 00:00:00", "2021-07-22 23:59:59"),
//                Pair("2021-07-23 00:00:00", "2021-07-24 23:59:59"),
//                Pair("2021-07-25 00:00:00", "2021-07-26 23:59:59"),
//                Pair("2021-07-27 00:00:00", "2021-07-28 23:59:59"),
//                Pair("2021-07-29 00:00:00", "2021-07-30 23:59:59"),
//                Pair("2021-07-31 00:00:00", "2021-07-31 23:59:59"),

//                Pair("2021-08-01 00:00:00", "2021-08-02 23:59:59"),
//                Pair("2021-08-03 00:00:00", "2021-08-04 23:59:59"),
//                Pair("2021-08-05 00:00:00", "2021-08-06 23:59:59"),
//                Pair("2021-08-07 00:00:00", "2021-08-08 23:59:59"),
//                Pair("2021-08-09 00:00:00", "2021-08-10 23:59:59"),
//                Pair("2021-08-11 00:00:00", "2021-08-12 23:59:59"),
//                Pair("2021-08-13 00:00:00", "2021-08-14 23:59:59"),
//                Pair("2021-08-15 00:00:00", "2021-08-16 23:59:59"),
//                Pair("2021-08-17 00:00:00", "2021-08-18 23:59:59"),
//                Pair("2021-08-19 17:00:00", "2021-08-20 23:59:59"),
//                Pair("2021-08-21 00:00:00", "2021-08-22 23:59:59"),
//                Pair("2021-08-23 00:00:00", "2021-08-24 23:59:59"),
//                Pair("2021-08-25 00:00:00", "2021-08-26 23:59:59"),
//                Pair("2021-08-27 00:00:00", "2021-08-28 23:59:59"),
//                Pair("2021-08-29 00:00:00", "2021-08-30 23:59:59"),
//                Pair("2021-08-31 00:00:00", "2021-08-31 23:59:59"),

//                Pair("2021-09-01 00:00:00", "2021-09-02 23:59:59"),
//                Pair("2021-09-03 00:00:00", "2021-09-04 23:59:59"),
//                Pair("2021-09-05 00:00:00", "2021-09-06 23:59:59"),
//                Pair("2021-09-07 00:00:00", "2021-09-08 23:59:59"),
//                Pair("2021-09-09 00:00:00", "2021-09-10 23:59:59"),
//                Pair("2021-09-11 00:00:00", "2021-09-12 23:59:59"),
//                Pair("2021-09-13 00:00:00", "2021-09-14 23:59:59"),
//                Pair("2021-09-15 00:00:00", "2021-09-16 23:59:59"),
//                Pair("2021-09-17 00:00:00", "2021-09-18 23:59:59"),
//                Pair("2021-09-19 17:00:00", "2021-09-20 23:59:59"),
//                Pair("2021-09-21 00:00:00", "2021-09-22 23:59:59"),
//                Pair("2021-09-23 00:00:00", "2021-09-24 23:59:59"),
//                Pair("2021-09-25 00:00:00", "2021-09-26 23:59:59"),
//                Pair("2021-09-27 00:00:00", "2021-09-28 23:59:59"),
//                Pair("2021-09-29 00:00:00", "2021-09-30 23:59:59"),
            )
            timeSet.forEach {(s, e) ->
                val workbook = realTimeDataService.outToWorkbook(deviceCode, s, e)
                val fileName = "网格化监测_${deviceCode}_${s.substring(0, 10).replace("-", ".")}-${e.substring(8, 10)}.xls"
            val filePath = "E:\\工作\\开发\\走航监测\\格式化数据\\$fileName"
            val out = FileOutputStream(File(filePath))
            workbook.write(out)
@@ -34,3 +116,4 @@
        }
    }
}
}

 src/test/kotlin/com/flightfeather/uav/model/epw/EPWDataPrepTest.kt

@@ -5,6 +5,8 @@
import com.flightfeather.uav.lightshare.bean.DataVo
import com.flightfeather.uav.lightshare.service.RealTimeDataService
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.junit.Assert.*
import org.junit.Test
import org.junit.runner.RunWith
@@ -12,7 +14,10 @@
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringRunner
import java.io.File
import java.io.FileInputStream
import java.io.FileOutputStream
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.*

@RunWith(SpringRunner::class)
@@ -81,7 +86,7 @@
            }

            if (dataList.isNotEmpty()) {
                val workbook = HSSFWorkbook()
                val workbook = SXSSFWorkbook()
                val heads = mutableListOf<Array<String>>()
                heads.add(dataList[0].toRowTitle())
                val contents = mutableListOf<Array<Any>>()
@@ -100,4 +105,72 @@
            }
        }
    }

    @Test
    fun foo2() {
        var fileName = "网格化监测预处理连续数据${Date().time}.xls"
        var filePath = "C:\\work\\工作\\走航监测\\预处理连续数据\\$fileName"
        var workBook = SXSSFWorkbook(1000)
        var out: FileOutputStream
        var input: FileInputStream
        var row = 0
        val maxRow = 100000
        var hasHead = false

        val ePWDataPrep = EPWDataPrep()

        val endDateTime = LocalDateTime.now()
        var sTime = LocalDateTime.of(2021, 6, 19, 0, 0, 0)
        var eTime = LocalDateTime.of(2021, 6, 19, 23, 59, 59)

        while (sTime.isBefore(endDateTime)) {
            val deviceCode = "0d0000000001"
            val dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
            val s = sTime.format(dateFormatter)
            val e = eTime.format(dateFormatter)
            val data = realTimeDataService.getSecondData(deviceCode, s, e, 1, 30000).data

            data?.let {
                val dataList = ePWDataPrep.mDataPrep(it)

                if (row + dataList.size > maxRow) {
                    fileName = "网格化监测预处理连续数据${Date().time}.xls"
                    filePath = "C:\\work\\工作\\走航监测\\预处理连续数据\\$fileName"
                    workBook = SXSSFWorkbook(1000)
                    row = 0
                    hasHead = false
                }

                if (!hasHead) {
                    out = FileOutputStream(filePath)
                    val heads = mutableListOf<Array<String>>()
                    heads.add(dataList[0].toRowTitle())
                    row = ExcelUtil.write(heads, emptyList(), workBook, row = row)
                    workBook.write(out)
                    workBook.close()
                    out.flush()
                    out.close()
                    hasHead = true
                }

                input = FileInputStream(filePath)
                workBook = SXSSFWorkbook(XSSFWorkbook(input), 1000)
                out = FileOutputStream(filePath)

                val contents = mutableListOf<Array<Any>>()
                dataList.forEach {d ->
                    contents.add(d.toRowContent())
                }
                row = ExcelUtil.write(emptyList(), contents, workBook, row = row)

                workBook.write(out)
                workBook.close()
                input.close()
                out.flush()
                out.close()
            }
            sTime = sTime.plusDays(1)
            eTime = eTime.plusDays(1)
        }
    }
}

 src/test/kotlin/com/flightfeather/uav/model/epw/EPWModelTest.kt

@@ -6,6 +6,7 @@
import com.flightfeather.uav.lightshare.bean.DataVo
import com.flightfeather.uav.lightshare.service.RealTimeDataService
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.beans.BeanUtils
@@ -86,7 +87,7 @@
        val deviceCode = "0d0000000001"

        val epwModel = EPWModel()
        var workbook: HSSFWorkbook? = null
        var workbook: SXSSFWorkbook? = null
        var out: FileOutputStream? = null

        for (i in timeSet.indices) {