package cn.sagacloud.server.algorithm.services.excel

import cn.sagacloud.server.algorithm.models.config.MyProperties
import cn.sagacloud.server.algorithm.models.excel.IsPassedEnum
import cn.sagacloud.server.algorithm.models.excel.PreciseCellValue
import cn.sagacloud.server.algorithm.models.excel.report.ModelQualityExcelReport
import cn.sagacloud.server.algorithm.services.http.HttpService
import com.sybotan.base.extensions.toBoolean
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.xssf.usermodel.XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.context.annotation.Scope
import org.springframework.stereotype.Component
import java.io.ByteArrayOutputStream

/**
 *
 *
 * @author jxing
 */
@Component
@Scope("prototype")
class ExcelService {

    @Autowired
    lateinit var template : ExcelTemplate

    @Autowired
    lateinit var properties: MyProperties

    var httpService = HttpService

    var workBook : XSSFWorkbook? = null

    /**
     * 读取阻塞性报告
     */
    fun readBlockingExcel2007() : Boolean{
        return try{
            workBook = template.getExcel2007Template("data_check.xlsx")
            true
        }catch(e : Exception){
            e.printStackTrace()
            System.out.println("exception: ${e.message}")
            false
        }
    }

    /**
     * 读取非阻塞性报告
     */
    fun readNonBlockingExcel2007() : Boolean{
        return try{
            workBook = template.getExcel2007Template("non-blocking-report.xlsx")
            true
        }catch(e : Exception){
            e.printStackTrace()
            System.out.println("exception: ${e.message}")
            false
        }
    }

    /**
     * 插入单个报告
     */
    fun insertReport(modelQualityExcelReport: ModelQualityExcelReport){
        var sheet = workBook!!.getSheetAt(modelQualityExcelReport.sheetIndex)
        if(sheet == null)
            return
        var startRowIndex = modelQualityExcelReport.excelStartRow
        for(row in modelQualityExcelReport.rowValue){
            var declaredFields = row.javaClass.declaredFields
            var columnIndex = 0
            for(field in declaredFields){
                val tmp = field.isAccessible
                field.isAccessible = true
                randomWrite(sheet, startRowIndex, columnIndex++, field.get(row))
                field.isAccessible = tmp
            }
            startRowIndex++
        }
        if(!modelQualityExcelReport.cellValues.isNullOrEmpty())
            randomWrite(modelQualityExcelReport.sheetIndex, modelQualityExcelReport.cellValues)
    }

    /**
     * 保存报告excel文件到文件服务器
     */
    fun saveToFileServer(modelId : String){
        val byteArrayOutputStream = ByteArrayOutputStream()
        workBook!!.write(byteArrayOutputStream)
        httpService.sendFileByte(getUploadUrl(modelId), byteArrayOutputStream.toByteArray())
    }

    /**
     * 获取上传excel的url
     */
    private fun getUploadUrl(modelId : String) : String{
        return "${properties.fileService.reportPath}${modelId}.xlsx"
    }

    /**
     * 获取下载excel的url
     */
    fun getDownloadUrl(modelId : String) : String{
        return "${properties.fileService.report}/${modelId}.xlsx"
    }

    /**
     * 随机写入数据到某个sheet的某个单元格
     */
    fun randomWrite(sheet : XSSFSheet, rowIndex : Int, columnIndex : Int, cellValue : Any?) : Boolean{
        try{
            var row = sheet.getRow(rowIndex)
            if(row == null){
                row = sheet.createRow(rowIndex)
            }
            var cell = row.getCell(columnIndex)
            if(cell == null) {
                cell = row.createCell(columnIndex)
            }
            insertCellValue(cellValue, cell)
        }catch (e : Exception){
            e.printStackTrace()
            return false
        }
        return true
    }

    /**
     * 随机写入数据到某个sheet的某个单元格
     */
    fun randomWrite(sheetIndex : Int, rowIndex : Int, columnIndex : Int, cellValue : Any?) : Boolean{
        try {
            val sheet = workBook!!.getSheetAt(sheetIndex)
            return randomWrite(sheet, rowIndex, columnIndex, cellValue)
        }catch (e : Exception){
            e.printStackTrace()
            return false
        }
    }

    /**
     * 随机写入数据到某个sheet的某个单元格
     */
    fun randomWrite(sheetIndex : Int, cellValue: PreciseCellValue) : Boolean{
        return randomWrite(sheetIndex, cellValue.rowIndex, cellValue.columnIndex, cellValue.obj)
    }

    /**
     * 插入数据到单元格
     */
    private fun insertCellValue(cellValue: Any?, cell: XSSFCell) {
        when {
            cellValue is String ->
                cell.setCellValue(cellValue)
            cellValue is Double ->
                cell.setCellValue(cellValue)
            cellValue is Int ->
                cell.setCellValue(cellValue.toDouble())
            cellValue is IsPassedEnum ->
                cell.setCellValue(cellValue.toString())
        }
    }

    /**
     * 根据id获取Sheet对象
     */
    fun getSheetAtIndex(index : Int) : ArrayList<ArrayList<Any>>{
        try {
            val rowList = ArrayList<ArrayList<Any>>()
            var colList : ArrayList<Any>
            var row: XSSFRow?
            var cell: XSSFCell?
            var value: Any
            val sheet = workBook!!.getSheetAt(index)
            var i = 0
            var rowCount = 0
            val maxRowNum = sheet.getPhysicalNumberOfRows()
            while (rowCount < maxRowNum) {
                row = sheet.getRow(i)
                colList = ArrayList()
                if (row == null) {
                    //当读取行为空时
                    rowList.add(colList)
                    ++i
                    continue
                } else {
                    rowCount++
                }
                var j : Short = 0
                val maxCellNum = row.getLastCellNum()
                while (j < maxCellNum) {
                    cell = row.getCell(j.toInt())
                    if (cell == null || cell.getCellType() == CellType._NONE) {
                        //当该单元格为空
                        colList.add("")
                        ++j
                        continue
                    }
                    when (cell.getCellType()) {
                        CellType.STRING -> {
//                            System.out.println("${i}行 ${j} 列 is String type")
                            value = cell.stringCellValue
                        }
                        CellType.NUMERIC -> {
                            value = cell.numericCellValue
//                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {
//                                value = df.format(cell.getNumericCellValue());
//                            } else if ("General".equals(cell.getCellStyle()
//                                            .getDataFormatString())) {
//                                value = nf.format(cell.getNumericCellValue());
//                            } else {
//                                value = sdf.format(HSSFDateUtil.getJavaDate(cell
//                                        .getNumericCellValue()));
//                            }
//                            System.out.println("${i}行 ${j} 列 is Number type ; DateFormt:" + value)
                        }
                        CellType.BOOLEAN -> {
//                            System.out.println("${i}行 ${j} 列 is Boolean type");
                            value = cell.booleanCellValue.toBoolean()!!
                        }
                        CellType.BLANK -> {
//                            System.out.println("${i}行 ${j} 列 is Blank type");
                            value = ""
                        }
                        else -> {
//                            System.out.println("${i}行 ${j} 列 is default type");
                            value = cell.toString()
                        }
                    }// end switch
                    colList.add(value)
                    ++j
                }//end for j
                rowList.add(colList)
                ++i
            }//end for i

            return rowList;
        }catch(e : Exception){
            e.printStackTrace()
            System.out.println("exception: ${e.message}")
            return ArrayList<ArrayList<Any>>()
        }
    }

    /**
     * 随机写入数据到某个sheet的某个单元格
     */
    fun randomWrite(sheetIndex: Int, cellValues: ArrayList<PreciseCellValue>) {
        for(cellValue in cellValues){
            randomWrite(sheetIndex, cellValue)
        }
    }
}