package com.zbooksoft.gdmis.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ruili.wcp.data.entity.config.ImportColumn;
import com.ruili.wcp.data.entity.config.ImportTemplate;
import com.ruili.wcp.engine.form.IFormData;
import com.ruili.wcp.service.config.ImportColumnService;
import com.ruili.wcp.service.config.ImportTemplateService;
import com.ruili.wcp.web.common.LicenseException;
import com.ruili.wcp.web.model.AjaxResponse;
import com.ruili.wcp.web.model.ErrorInfo;
import com.zbooksoft.gdmis.data.entity.CatAjjxx;
import com.zbooksoft.gdmis.service.CatAjjxxService;
import com.zbooksoft.gdmis.service.CatSwajxxService;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresUser;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.ruili.wcp.web.controller.general.DataImportController.getExcelValue;

/**
 * @Description:导入Excel文件
 * @Author: zhai
 * @Date: 2024/9/11
 **/
@Controller
@RequestMapping("/tape")
public class TapeController {

    @Autowired
    ImportTemplateService importTemplateService;
    @Autowired
    ImportColumnService importColumnService;
    @Autowired
    CatSwajxxService catSwajxxService;
    @Autowired
    CatAjjxxService catAjjxxService;
    //    @Autowired
//    CatSwzkxxService catSwzkxxService;
//    @Autowired
//    CatSwyxxxService catSwyxxxService;
    @Autowired
    IFormData iform;
    @Autowired
    JdbcTemplate jdbcTemplate;
    private static final Logger logger = LoggerFactory.getLogger(TapeController.class);


    /**
     * 导入页面
     *
     * @param type 0:从转孔基本信息进入 1:从钻孔图幅进入
     * @return
     */
    @RequestMapping({"/importIndex"})
    @RequiresUser
    public ModelAndView importIndex(Integer type) {
        ModelAndView mv = new ModelAndView("gh/excel/tapeImportExcel");
        return mv;

    }

    /**
     * @param file 0 导入转孔信息  1 导入图幅信息 2 导入实物案卷信息
     * @return
     * @throws LicenseException
     */

    @RequestMapping({"/importExcel"})
    @ResponseBody
    @RequiresUser
    public Object importExcel(@RequestParam(value = "file", required = false) MultipartFile file, String ajId) throws LicenseException {
        try {
            readExcelByTape(file, ajId);
            return new AjaxResponse(true);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo("导入失败"), false);
        }
    }

    /**
     * @Description 全宗统计数据
     * @Date${DATE} 2023/5/16
     **/
    @RequestMapping(value = "/getProject")
    @ResponseBody
    @RequiresUser
    public List<Map<String, Object>> getProject() {
        String sql = "SELECT XMKYQMC, ID FROM CAT_FILE_AJJXX";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

    /**
     * 处理选择的数据(装盒)
     *
     * @param fromId
     * @param idList
     * @return
     */
    @RequestMapping(value = "/addTapeDetail")
    @ResponseBody
    public Object addBoxList(Long fromId, @RequestBody List<Long> idList) {
        for (Long longId : idList) {
            String countSql = "SELECT COUNT(1) AS count FROM BUS_TAPE_DETAIL WHERE RELATION_ID = " + longId + " AND FORM_ID = " + fromId;
            int count = jdbcTemplate.queryForObject(countSql, Integer.class);
            if (count > 0) {
                continue;
            }
            String selectSql = "SELECT * FROM  BUS_TAPE_MANAGE WHERE ID = " + longId;
            Map<String, Object> tapeManageMap = jdbcTemplate.queryForMap(selectSql);
            HashMap<String, Object> insertMap = new HashMap<>();
            insertMap.put("GQM", tapeManageMap.get("GQM"));
            insertMap.put("LBDZ", tapeManageMap.get("LBDZ"));
            insertMap.put("WJM", tapeManageMap.get("WJM"));
            insertMap.put("PH", tapeManageMap.get("PH"));
            insertMap.put("WJH", tapeManageMap.get("WJH"));
            insertMap.put("GZDW", tapeManageMap.get("GZDW"));
            insertMap.put("GZRQ", tapeManageMap.get("GZRQ"));
            insertMap.put("FORM_ID", fromId);
            insertMap.put("RELATION_ID", longId);
            iform.insert(1982633467650830338L, insertMap);
        }
        return new AjaxResponse(true);
    }


    private void readExcelByTape(MultipartFile file, String ajId) throws IOException {

        String originalFilename = file.getOriginalFilename();
        CatAjjxx catAjjxx = catAjjxxService.getById(ajId);
        HashMap<String, Object> map = new HashMap<>();
        map.put("AJ_ID", ajId);
        map.put("XMMC", catAjjxx.getXmkyqmc());
        map.put("XMBH", catAjjxx.getXmkyqbh());
        map.put("ZLZT", 0);
        //获取最大批次号
        String sql = "SELECT MAX(PCH) as pch FROM BUS_TAPE_MANAGE WHERE AJ_ID =" + ajId;
        Map<String, Object> mapPch = jdbcTemplate.queryForMap(sql);
        Integer pch = mapPch.get("pch") == null ? 0 : Integer.parseInt(mapPch.get("pch").toString());
        map.put("PCH", pch + 1);
        InputStream inputStream = file.getInputStream();
        if (originalFilename.matches("^.+\\.(?i)(xls)$")) {
            //创建工作簿对象
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
            readExcel2003(hssfWorkbook);
        }
        if (originalFilename.matches("^.+\\.(?i)(xlsx)$")) {
            //创建工作簿对象
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            readExcel2007(xssfWorkbook, map);
        }
    }


    private String getValue(HSSFSheet sheet, int rowIndex, int cellIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            Cell cell = row.getCell(cellIndex);
            if (cell != null) {
                String excelValue = getExcelValue(cell) == null ? "" : getExcelValue(cell).toString();
                return subString(excelValue);
            }
        }
        return null;
    }

    private String subString(String cellValue) {
        if (cellValue != null && cellValue.contains("：")) {
            int index = cellValue.indexOf("：");
            return cellValue.substring(index + 1).trim();
        }
        return cellValue;
    }


    //Excel2003 xls格式
    private void readExcel2003(HSSFWorkbook hssfWorkbook) {
        ImportTemplate importTemplate = importTemplateService.getByIdImportTemplate(1966024810830041089L);
        QueryWrapper<ImportColumn> query = new QueryWrapper();
        query.eq("import_template_id", importTemplate.getImportTemplateId());
        List<ImportColumn> columnList = importColumnService.list(query);
        List<String> excelColumns = new ArrayList();

        //读取第i个工作表
        HSSFSheet sheet = hssfWorkbook.getSheetAt(1);
        //获取最后一行的num，即总行数。此处从6行开始
        int maxRow = sheet.getLastRowNum();
        Row row = sheet.getRow(3);
//        Row row = sheet.getRow(4);
        int totalCells = row.getPhysicalNumberOfCells();
        for (int j = 0; j < totalCells; ++j) {
            Cell cell = row.getCell(j);
            String cellValue = getExcelValue(cell) == null ? "" : getExcelValue(cell).toString();

            if (StringUtils.isNotEmpty(cellValue) && "站位位置".equals(cellValue)) {
                Row rowJd = sheet.getRow(4);
                Cell cellJd = rowJd.getCell(j);
                String excelValueJd = getExcelValue(cellJd);
                excelColumns.add(excelValueJd);

                Cell cellWd = rowJd.getCell(j + 1);
                String excelValueWd = getExcelValue(cellWd);
                excelColumns.add(excelValueWd);
            } else if (StringUtils.isNotEmpty(cellValue)) {
                excelColumns.add(cellValue.toString());
            }
        }
        for (int j = 6; j <= maxRow; j++) {
            Row rowValue = sheet.getRow(j);
            Map<String, Object> map = new HashMap();

            for (int z = 0; z < columnList.size(); z++) {
                ImportColumn column = columnList.get(z);
                int columnIndex = excelColumns.indexOf(column.getExcelColunm());
                if (columnIndex >= 0) {
                    Cell cell = rowValue.getCell(columnIndex);
                    Object cellValue = getExcelValue(cell);
                    map.put(column.getColumnName(), cellValue);
                }
            }
            iform.insert(importTemplate.getTableId(), map);
        }

    }

    // Excel2007 xlsx
    private void readExcel2007(XSSFWorkbook xssfWorkbook, Map<String, Object> mapData) {
        ImportTemplate importTemplate = importTemplateService.getByIdImportTemplate(1966024810830041089L);
        QueryWrapper<ImportColumn> query = new QueryWrapper();
        query.eq("import_template_id", importTemplate.getImportTemplateId());
        List<ImportColumn> columnList = importColumnService.list(query);
        List<String> excelColumns = new ArrayList();
        //读取第i个工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        //获取最后一行的num，即总行数。此处从0开始
        int maxRow = sheet.getLastRowNum();
        Row row = sheet.getRow(0);
        int totalCells = row.getPhysicalNumberOfCells();
        for (int j = 0; j < totalCells; ++j) {
            Cell cell = row.getCell(j);
            Object cellValue = getExcelValue(cell);
            excelColumns.add(cellValue.toString());
        }
        for (int j = 1; j <= maxRow; j++) {
            try {
                Row rowValue = sheet.getRow(j);
                Map<String, Object> map = new HashMap();
                map.putAll(mapData);
                for (int z = 0; z < columnList.size(); z++) {
                    ImportColumn column = columnList.get(z);
                    int columnIndex = excelColumns.indexOf(column.getExcelColunm());
                    if (columnIndex >= 0) {
                        Cell cell = rowValue.getCell(columnIndex);
                        Object cellValue = getExcelValue(cell);
                        map.put(column.getColumnName(), cellValue);
                    }
                }
                iform.insert(importTemplate.getTableId(), map);
            } catch (Exception e) {
                logger.info(e.getMessage(), e);
            }
        }
    }
}