package yangtz.cs.liu.campus.service.impl.schoolbudget;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.List;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.mapper.SysDeptMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import yangtz.cs.liu.campus.domain.schoolbudget.SchoolBudget;
import yangtz.cs.liu.campus.domain.schoolbudget.SchoolBudgetDetails;
import yangtz.cs.liu.campus.mapper.schoolbudget.SchoolBudgetDetailsMapper;
import yangtz.cs.liu.campus.mapper.schoolbudget.SchoolBudgetMapper;
import yangtz.cs.liu.campus.service.schoolbudget.ISchoolBudgetDetailsService;
import yangtz.cs.liu.campus.util.TemplateExcelUtils;


/**
 * 【请填写功能名称】Service业务层处理
 *
 * @author wengxiu
 * @date 2023-01-06
 */
@Service
public class SchoolBudgetDetailsServiceImpl extends ServiceImpl<SchoolBudgetDetailsMapper, SchoolBudgetDetails> implements ISchoolBudgetDetailsService
{
    @Autowired
    private SchoolBudgetDetailsMapper schoolBudgetDetailsMapper;
    @Autowired
    private SchoolBudgetMapper schoolBudgetMapper;
    @Autowired
    private SysDeptMapper sysDeptMapper;


    /**
     * 查询【请填写功能名称】列表
     *
     * @param schoolBudgetDetails 【请填写功能名称】
     * @return 【请填写功能名称】
     */
    @Override
    public List<SchoolBudgetDetails> selectSchoolBudgetDetailsList(SchoolBudgetDetails schoolBudgetDetails)
    {
        return schoolBudgetDetailsMapper.selectSchoolBudgetDetailsList(schoolBudgetDetails);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean importListFromExcel(MultipartFile file) throws Exception{

            //获取文件
            Workbook workbook = TemplateExcelUtils.getWorkbook(file);
            InputStream in = file.getInputStream();
            XSSFWorkbook sheets = new XSSFWorkbook(in);  //读取整个excel
            XSSFSheet sheet = sheets.getSheetAt(0);  //获取第一个sheet

            // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
            int rows = sheet.getLastRowNum();
            if (1 == rows) {
                throw new ServiceException("Excel内没有数据");
            }
            XSSFCell cell1 = null;
            XSSFCell cell2 = null;
            XSSFCell cell3 = null;
            XSSFCell cell4 = null;
            XSSFCell cell5 = null;
            XSSFCell cell6 = null;
            XSSFCell cell7 = null;
            XSSFCell cell8 = null;
            String budgetYear = "";
            String deptName = "";
            Long deptId = null;
            String budgetName = "";
            String giveMoney = "";
            String isGov = "";
            String remark = "";

            int rowNum = 0;
            //存在样式的空行,会被统计进来,判断是否是空行。
            for (int num = 0; num <=sheet.getLastRowNum(); num++) {
                XSSFRow row = sheet.getRow(num);
                if (StringUtils.isNotNull(row)) {
                    rowNum++;
                }
            }
            Long budgetId = null;
            Boolean isSameCell = false;  //是否为同一单元格
            for(int i=2;i<rowNum;i++){
                SchoolBudget schoolBudget = new SchoolBudget();
                SchoolBudgetDetails schoolBudgetDetails = new SchoolBudgetDetails();
                XSSFRow row = sheet.getRow(i);
                if(StringUtils.isNull(row.getCell(0))){
                    row.createCell(0).setCellValue("");
                }else{
                    cell1 = row.getCell(0);
                }
                if(StringUtils.isNull(row.getCell(1))){
                    row.createCell(1).setCellValue("");
                }else{
                    cell2 = row.getCell(1);
                }
                if(StringUtils.isNull(row.getCell(2))){
                    row.createCell(2).setCellValue("");
                }else{
                    cell3 = row.getCell(2);
                }
                if(StringUtils.isNull(row.getCell(3))){
                    row.createCell(3).setCellValue(0);
                }else{
                    cell4 = row.getCell(3);
                }
                if(StringUtils.isNull(row.getCell(4))){
                    row.createCell(4).setCellValue("");
                }else{
                    cell5 = row.getCell(4);
                }
                if(StringUtils.isNull(row.getCell(5))){
                    row.createCell(5).setCellValue("");
                }else{
                    cell6 = row.getCell(5);
                }
                if(StringUtils.isNull(row.getCell(6))){
                    row.createCell(6).setCellValue("");
                }else{
                    cell7 = row.getCell(6);
                }
                if(StringUtils.isNull(row.getCell(7))){
                    row.createCell(7).setCellValue(0);
                }else{
                    cell8 = row.getCell(7);
                }
                boolean isMerge = isMergedRegion(sheet, i, 0);
                //schoolBudgetDetails.setBudgetDetailed(row.getCell(6).getStringCellValue());
                schoolBudgetDetails.setBudgetDetailed(TemplateExcelUtils.getCellValue(row.getCell(7), workbook));
                schoolBudgetDetails.setMoneyDetailed(new BigDecimal(new DataFormatter().formatCellValue(row.getCell(8))));
                if(isMerge){  //如果是合并单元格
                    isSameCell = isSameMergeRegion(sheet,i,0); //判断与上一行是否为同一单元格
                    if(!isSameCell){
                        budgetYear = getMergedRegionValue(sheet, row.getRowNum(), 0);
                        schoolBudget.setBudgetYear(budgetYear);
                        deptName = getMergedRegionValue(sheet, row.getRowNum(), 1);
                        schoolBudget.setDeptName(deptName);
                        deptId = sysDeptMapper.selectIdByDeptName(deptName);
                        if(StringUtils.isNull(deptId)){
                            throw new ServiceException("申请部门名称有误");
                        }
                        schoolBudget.setDeptId(deptId);
                        budgetName = getMergedRegionValue(sheet, row.getRowNum(), 2);
                        schoolBudget.setBudgetName(budgetName);
                        giveMoney = getMergedRegionValue(sheet, row.getRowNum(), 3);
                        schoolBudget.setGiveMoney(new BigDecimal(giveMoney));
                        isGov = getMergedRegionValue(sheet, row.getRowNum(), 5);
                        if(isGov.equals("是")){
                            schoolBudget.setIsGov("0");
                        }
                        if(isGov.equals("否")){
                            schoolBudget.setIsGov("1");
                        }
                        remark = getMergedRegionValue(sheet, row.getRowNum(), 6);
                        schoolBudget.setRemark(remark);
                        schoolBudgetMapper.insert(schoolBudget);
                        budgetId = schoolBudget.getId();
                    }
                    schoolBudgetDetails.setBudgetId(budgetId);
                    schoolBudgetDetailsMapper.insert(schoolBudgetDetails);
                }else{
                    schoolBudget.setBudgetYear(new DataFormatter().formatCellValue(row.getCell(0)));
                    deptName = new DataFormatter().formatCellValue(row.getCell(1));
                    schoolBudget.setDeptName(deptName);
                    deptId = sysDeptMapper.selectIdByDeptName(deptName);
                    if(StringUtils.isNull(deptId)){
                        throw new ServiceException("科室名称" + deptName + "有误，请检查");
                    }
                    schoolBudget.setDeptId(deptId);
                    schoolBudget.setBudgetName(new DataFormatter().formatCellValue(row.getCell(2)));
                    schoolBudget.setGiveMoney(new BigDecimal(new DataFormatter().formatCellValue(row.getCell(3))));
                    isGov = new DataFormatter().formatCellValue(row.getCell(5));
                    if(isGov.equals("是")){
                        schoolBudget.setIsGov("0");
                    }
                    if(isGov.equals("否")){
                        schoolBudget.setIsGov("1");
                    }
                    schoolBudget.setRemark(new DataFormatter().formatCellValue(row.getCell(6)));
                    schoolBudgetMapper.insert(schoolBudget);
                    schoolBudgetDetails.setBudgetId(schoolBudget.getId());
                    schoolBudgetDetailsMapper.insert(schoolBudgetDetails);
                }
            }
            return true;
    }

    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        //获取该sheet所有合并的单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        //循环判断 该单元格属于哪个合并单元格， 如果能找到对应的，就表示该单元格是合并单元格
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
//            int firstColumn = range.getFirstColumn();
//            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                return true;
            }
        }
        return false;
    }

    private static boolean isSameMergeRegion(Sheet sheet, int row,int column) {
        //获取该sheet所有合并的单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        //循环判断 该单元格属于哪个合并单元格， 如果能找到对应的，就表示该单元格是合并单元格
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
//            int firstColumn = range.getFirstColumn();
//            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow && row-1 >= firstRow && row-1 <= lastRow) {
                return true;
            }
        }
        return false;
    }

    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
//            int firstColumn = ca.getFirstColumn();
//            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                Row row2 = sheet.getRow(firstRow);
                Cell fCell = row2.getCell(column);
                return getCellValue(fCell);
            }
        }
        return null;
    }

    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }else{
//            return cell.getStringCellValue();
            return new DataFormatter().formatCellValue(cell);
        }
    }
}
