package com.qianhe.controller;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import javax.servlet.http.HttpServletResponse;

import com.qianhe.common.core.domain.BaseEntity;
import com.qianhe.common.exception.BusinessException;
import com.qianhe.common.utils.bean.BeanUtils;
import com.qianhe.domain.SjGfjsYdjcjl;
import com.qianhe.domain.SjGfjsZsdwwtcj;
import com.qianhe.domain.Vo.SjfybpVo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.http.MediaType;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.qianhe.common.annotation.Log;
import com.qianhe.common.core.controller.BaseController;
import com.qianhe.common.core.domain.AjaxResult;
import com.qianhe.common.enums.BusinessType;
import com.qianhe.domain.SjFybp;
import com.qianhe.service.ISjFybpService;
import com.qianhe.common.utils.poi.ExcelUtil;
import com.qianhe.common.core.page.TableDataInfo;

/**
 * 三基-费用报批Controller
 *
 * @author qianhe
 * @date 2024-07-05
 */
@RestController
@RequestMapping("/system/sjFybp")
public class SjFybpController extends BaseController
{
    @Autowired
    private ISjFybpService sjFybpService;

    /**
     * 查询三基-费用报批列表
     */
    @GetMapping("/list")
    public TableDataInfo list(SjFybp sjFybp)
    {
        startPage();
        List<SjFybp> list = sjFybpService.selectSjFybpList(sjFybp);
        return getDataTable(list);
    }

    /**
     * 导出三基-费用报批列表
     */
    @Log(title = "三基-费用报批", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(HttpServletResponse response, SjFybp sjFybp)
    {
        List<SjFybp> list = sjFybpService.selectSjFybpList(sjFybp);
        ExcelUtil<SjFybp> util = new ExcelUtil<SjFybp>(SjFybp.class);
        util.exportExcel(response, list, "三基-费用报批数据");
    }

    /**
     * 获取三基-费用报批详细信息
     */
    @GetMapping(value = "/{id}")
    public AjaxResult getInfo(@PathVariable("id") Long id)
    {
        return success(sjFybpService.selectSjFybpById(id));
    }

    /**
     * 新增三基-费用报批
     */
    @Log(title = "三基-费用报批", businessType = BusinessType.INSERT)
    @PostMapping
    public AjaxResult add(@RequestBody SjFybp sjFybp)
    {
        return toAjax(sjFybpService.insertSjFybp(sjFybp));
    }

    /**
     * 修改三基-费用报批
     */
    @Log(title = "三基-费用报批", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult edit(@RequestBody SjFybp sjFybp)
    {
        return toAjax(sjFybpService.updateSjFybp(sjFybp));
    }

    /**
     * 删除三基-费用报批
     */
    @Log(title = "三基-费用报批", businessType = BusinessType.DELETE)
	@DeleteMapping("/{ids}")
    public AjaxResult remove(@PathVariable Long[] ids)
    {
        return toAjax(sjFybpService.deleteSjFybpByIds(ids));
    }


    /**
     * 提交
     */
    @Log(title = "三基-费用报批", businessType = BusinessType.UPDATE)
    @PostMapping("/tj")
    public AjaxResult tj(@RequestBody SjFybp sjFybp)
    {
        return toAjax(sjFybpService.tj(sjFybp));
    }


//    @Log(title = "三基-费用报批", businessType = BusinessType.UPDATE)

    /**
     * 审批
     * @param sjFybp
     * @return
     */
    @PostMapping("/sp")
    public AjaxResult sp(@RequestBody SjFybp sjFybp)
    {
        return toAjax(sjFybpService.tj(sjFybp));
    }

    /**
     * 查询三基-费用报批列表
     */
    @GetMapping("/splist")
    public TableDataInfo splist(SjFybp sjFybp)
    {
        startPage();
        List<SjFybp> list = sjFybpService.selectSjFybpList(sjFybp);
        return getDataTable(list);
    }

    /**
     * 查询三基-费用报批列表
     */
    @GetMapping("/zglist")
    public TableDataInfo zglist(SjFybp sjFybp)
    {
        startPage();
        List<SjFybp> list = sjFybpService.getFygz(sjFybp);
        return getDataTable(list);
    }


    /**
     * 查询三基-费用报批列表
     */
    @GetMapping("/getFygzById/{id}")
    public AjaxResult getFygz(@PathVariable Long id)
    {

        return AjaxResult.success(sjFybpService.getFygzById(id));
    }

    /**
     * 费用维护
     */
    @Log(title = "三基-费用报批", businessType = BusinessType.UPDATE)
    @PostMapping("fywh")
    public AjaxResult fywh(@RequestBody SjFybp sjFybp)
    {
        return toAjax(sjFybpService.fywh(sjFybp));
    }


    //tjtbByDept

    /**
     * 统计（根据单位）
     * @param sjFybp
     * @return
     */
    @GetMapping("/tjtbByDept")
    public AjaxResult tjtbByDept(SjFybp sjFybp){
        return AjaxResult.success(sjFybpService.tjtbByDept(sjFybp));
    }

    /**
     * 统计（根据费用类型）
     * @param sjFybp
     * @return
     */
    @GetMapping("/tjtbByfylx")
    public AjaxResult tjtbByfylx(SjFybp sjFybp){
        return AjaxResult.success(sjFybpService.tjtbByfylx(sjFybp));
    }

    /**
     * 穿透
     * @param sjFybp
     * @return
     */
    @GetMapping("/tjtbct")
    public TableDataInfo tjtbct(SjFybp sjFybp)
    {
        startPage();
        List<SjFybp> list = sjFybpService.selectSjFybpCtList(sjFybp);
        return getDataTable(list);
    }

    /**
     * 导出三基-费用报批列表
     */
    @Log(title = "三基-费用报批", businessType = BusinessType.EXPORT)
    @PostMapping("/exportTj")
    public void exportTj(HttpServletResponse response, SjFybp sjFybp)
    {
//        List<SjFybp> sjFybps = sjFybpService.tjtbByDept(sjFybp);
//        List<SjfybpVo> voList=new ArrayList<>();
//        sjFybps.forEach(item->{
//            SjfybpVo vo=new SjfybpVo();
//            BeanUtils.copyProperties(item,vo);
//            voList.add(vo);
//        });
//        ExcelUtil<SjfybpVo> util = new ExcelUtil<SjfybpVo>(SjfybpVo.class);
//        util.exportExcel(response, voList, "统计");
    }


    /** 直属单位三基建设费用统计表 */
    @GetMapping("/zsdwNdfytj")
    public List<SjfybpVo> zsdwNdfytj(SjFybp sjFybp)
    {
        List<SjfybpVo> list = sjFybpService.zsdwNdfytj(sjFybp);
        return list;
    }

    //导出 直属单位三基建设费用统计表
    @Log(title = "导出 直属单位三基建设费用统计表", businessType = BusinessType.EXPORT)
    @PostMapping("/exportZsdwNdfytj")
    public void exportZsdwNdfytj(HttpServletResponse response, SjFybp sjFybp)
    {
        List<SjfybpVo> list = sjFybpService.zsdwNdfytj(sjFybp);

        Workbook exl = null;
        OutputStream out = null;
        InputStream in  = null;
        try {
            in =this.getClass().getResourceAsStream("/static/txgt_fy_zsdwfytj.xlsx");
            exl = WorkbookFactory.create(in);
            out = response.getOutputStream();
            response.reset();
            String filename = URLEncoder.encode(sjFybp.getFyrq() + "年 直属单位三基建设费用统计表.xlsx", "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + filename);
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

            dcExcelCb(exl, list);

            exl.write(out);
            exl.close();
        }catch(Exception e){
            e.printStackTrace();
            throw new BusinessException("导出Excel失败，请联系网站管理员！");
        }finally{
            if (exl != null) {
                try {
                    exl.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }
    //导出excel 动态设置从表数据
    private void dcExcelCb(Workbook exl, List<SjfybpVo> cbList){
        Sheet sheet = exl.getSheet("三基建设费用统计表");
//            sheet.getRow(0).getCell(0).setCellValue(sjZdbfZsdwkhpj.getNd()+"年"+sjZdbfZsdwkhpj.getZqfw()+sjZdbfZsdwkhpj.getDeptName());
        CellStyle cellStyle = exl.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        int lastRow = sheet.getLastRowNum()+1;
        for(SjfybpVo tmp:cbList){
            Row row = sheet.createRow(lastRow);
            row.setHeightInPoints(18);
            int m = 0;
            int xh = 1;//第一列的序号
            Cell cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(xh);
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getDeptName());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getAbztqzded());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getYsyje().toString());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getJd());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzc1());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzcmx1());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzc2());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzcmx2());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzc3());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzcmx3());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzc4());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzcmx4());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzc5());
            cell = row.createCell(m++);cell.setCellStyle(cellStyle); cell.setCellValue(tmp.getXmzcmx5());
            lastRow++;
            xh++;
        }

        //合并行
//        mergeCellsByColumn(sheet,0);
        mergeCellsByColumn(sheet,1, 5);
        mergeCellsByColumn(sheet,2, 5);
        mergeCellsByColumn(sheet,3, 5);

        if(cbList.size()>0){
            sheet.getRow(1).getCell(2).setCellValue(cbList.get(0).getFyrq() + "年");

        }

    }

    /**
     * 合并指定列中相同值的单元格
     * @param sheet Excel工作表
     * @param columnIndex 要合并的列索引
     * @param startRowIndex 数据起始行索引(从0开始)，之前的行将被忽略
     */
    private static void mergeCellsByColumn(Sheet sheet, int columnIndex, int startRowIndex) {
        // 清除已存在的合并区域(可选)
        // clearMergedRegions(sheet, columnIndex);

        if (sheet.getLastRowNum() <= startRowIndex) {
            return; // 没有可合并的数据行
        }

        int startRow = startRowIndex;
        String currentValue = getCellValue(sheet.getRow(startRow).getCell(columnIndex));

        for (int i = startRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            Cell cell = row.getCell(columnIndex);
            String cellValue = getCellValue(cell);

            // 当前值与起始值不同，合并前面的行
            if (!cellValue.equals(currentValue)) {
                // 检查合并区域是否重叠
                if (!isRegionOverlapping(sheet, startRow, i - 1, columnIndex)) {
                    mergeCellsInRange(sheet, columnIndex, startRow, i - 1);
                } else {
                    // 处理重叠情况，可记录日志或抛出异常
                    //log.warn("合并区域重叠: 行[{}:{}]列[{}]", startRow, i - 1, columnIndex);
                }
                startRow = i;
                currentValue = cellValue;
            }
        }

        // 合并最后一组单元格
        if (!isRegionOverlapping(sheet, startRow, sheet.getLastRowNum(), columnIndex)) {
            mergeCellsInRange(sheet, columnIndex, startRow, sheet.getLastRowNum());
        } else {
            //log.warn("合并区域重叠: 行[{}:{}]列[{}]", startRow, sheet.getLastRowNum(), columnIndex);
        }
    }

    // 获取单元格值的工具方法，处理不同类型和null情况
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    // 合并指定范围内的单元格
    private static void mergeCellsInRange(Sheet sheet, int columnIndex, int startIndex, int endIndex) {
        if (startIndex < endIndex) { // 只有当起始行小于结束行时才合并
            sheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, columnIndex, columnIndex));
        }
    }

    /**
     * 检查合并区域是否与现有区域重叠
     */
    private static boolean isRegionOverlapping(Sheet sheet, int startRow, int endRow, int columnIndex) {
        CellRangeAddress newRegion = new CellRangeAddress(startRow, endRow, columnIndex, columnIndex);

        // 检查是否与现有合并区域重叠
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress existingRegion = sheet.getMergedRegion(i);

            // 检查是否为同一列
            if (existingRegion.getFirstColumn() != columnIndex || existingRegion.getLastColumn() != columnIndex) {
                continue;
            }

            // 检查行范围是否重叠
            if (!(newRegion.getLastRow() < existingRegion.getFirstRow() ||
                    newRegion.getFirstRow() > existingRegion.getLastRow())) {
                return true; // 重叠
            }
        }
        return false;
    }

    /**
     * 清除指定列的所有合并区域
     */
    private static void clearMergedRegions(Sheet sheet, int columnIndex) {
        List<Integer> regionsToRemove = new ArrayList<>();

        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (region.getFirstColumn() == columnIndex && region.getLastColumn() == columnIndex) {
                regionsToRemove.add(i);
            }
        }

        // 从后往前删除，避免索引变化
        Collections.reverse(regionsToRemove);
        for (int index : regionsToRemove) {
            sheet.removeMergedRegion(index);
        }
    }
}
