潘钊 / bsth_control · Files

package com.bsth.service.schedule.timetable;

import com.bsth.entity.Line;
import com.bsth.entity.LsStationRoute;
import com.bsth.entity.schedule.GuideboardInfo;
import com.bsth.entity.schedule.TTInfo;
import com.bsth.entity.schedule.TTInfoDetail;
import com.bsth.repository.schedule.TTInfoDetailRepository;
import com.bsth.repository.schedule.TTInfoRepository;
import com.bsth.service.LineService;
import com.bsth.service.StationRouteService;
import com.bsth.service.schedule.GuideboardInfoService;
import com.bsth.service.schedule.exception.ScheduleException;
import com.bsth.service.schedule.timetable.strategy.TimetableExcelDataImportStrategy;
import com.bsth.service.schedule.timetable.strategy.TimetableExcelDataValidateStrategy;
import com.bsth.service.schedule.timetable.strategy.TimetableExcelPVDataExportStrategy;
import com.bsth.service.schedule.utils.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 时刻表Excel数据类，用于验证，导入，导出时刻表数据。
 * 混合Builder和Strategy模式。
 */
public class TimetableExcelData {
    /** 日志记录器 */
    private final static Logger LOG = LoggerFactory.getLogger(TimetableExcelData.class);

    // ------------------ 验证，导入用公共属性及方法，如下：------------------- //
    /** 待导入excel workbook对象 */
    private Workbook excelWorkBook;
    /** 待导入excel workbook 工作区对象 */
    private Sheet excelWorkBookSheet;
    /** 时刻表excel格式类型 */
    private TimetableExcelFormatType timetableExcelFormatType;

    // 内部标识excel单元格类
    public static class TimetableExcelInternalCell {
        /** 是否可用（默认不可用） */
        public Boolean isEnable = false;
        /** 单元格内容 */
        public String cellContent;

        //------------- 班次类型（发车时间单元格 isFcsjCell = true）-----------//
        /** 是否是班次 */
        public Boolean isBc = false;
        /** 是否是出场班次 */
        public Boolean isOutBc = false;
        /** 是否是进场班次 */
        public Boolean isInBc = false;

        //------------- 单元格类型 -------------//
        /** 是否表头Cell */
        public Boolean isHeadCell = false;
        /** 是否路牌名字Cell */
        public Boolean isLpNameCell = false;
        /** 是否发车时间Cell */
        public Boolean isFcsjCell = false;
        /** 是否工时Cell */
        public Boolean isGsCell = false;
    }
    /** 内部Excel单元格二维数组 */
    private TimetableExcelInternalCell[][] internalExcelCells;
    // 初始化内部标识excel单元格
    private void initInternalExcelCells() {
        int rowCount = this.excelWorkBookSheet.getLastRowNum() + 1; // 基于0 base的，长度加1
        Assert.isTrue(rowCount > 1, String.format("%s 工作区没有数据！", this.excelWorkBookSheet.getSheetName()));
        int colCount = this.excelWorkBookSheet.getRow(0).getLastCellNum(); // 不需要加1，就是长度
        Assert.isTrue(colCount > 0, String.format("%s 工作区第一行没有数据！", this.excelWorkBookSheet.getSheetName()));

        this.internalExcelCells = new TimetableExcelInternalCell[rowCount][colCount];
        for (int rowNum = 0; rowNum < rowCount; rowNum ++) {
            for (int colNum = 0; colNum < colCount; colNum ++) {
                TimetableExcelInternalCell cell = new TimetableExcelInternalCell();
                String cell_con = StringUtils.trimToEmpty(PoiUtils.getStringValueFromCell(
                        this.excelWorkBookSheet.getRow(rowNum).getCell(colNum))
                        .replaceAll("\\s*", "")); // trimToEmpty

                // 如果站名中有类似->{数字}，使用正则表达式过滤掉
                cell_con = cell_con.replaceAll("(->\\d+)", "");

                cell.cellContent = cell_con;
                cell.isEnable = true;
                this.internalExcelCells[rowNum][colNum] = cell;
            }
        }

    }

    // ------------------ 验证相关属性及方法，如下：---------------- //
    /** 线路信息 */
    private Line line;
    /** 线路站点路由列表（包含上下行的起终点站） */
    private List<LsStationRoute> lsStationRouteList;
    /** 路牌列表 */
    private List<GuideboardInfo> guideboardInfoList;
    /** 时刻表excel验证策略 */
    private TimetableExcelDataValidateStrategy timetableExcelDataValidateStrategy;
    /**
     * 验证excel时刻表。
     */
    public void doValidate() throws ScheduleException {
        this.timetableExcelDataValidateStrategy.doValidate(
                this.internalExcelCells,
                this.timetableExcelFormatType,
                this.line,
                this.lsStationRouteList,
                this.guideboardInfoList);
    }

    // ------------------ 导入相关属性及方法，如下：---------------- //
    /** 导入的Excel文件路径 */
    private String excelFilePath;
    /** 数据工具服务 */
    private DataToolsService dataToolsService;
    /** 配置数据导入导出用到的配置信息 */
    private DataToolsProperties dataToolsProperties;
    /** ktr转换所需参数对象 */
    private Map<String, Object> ktrParams = new HashMap<>();
    /** 时刻表excel导入策略 */
    private TimetableExcelDataImportStrategy timetableExcelDataImportStrategy;
    /**
     * 导入excel时刻表。
     */
    public void doImport() throws ScheduleException {
        this.timetableExcelDataImportStrategy.doImport(
                this.excelWorkBookSheet,
                this.excelFilePath,
                this.dataToolsService,
                this.dataToolsProperties,
                this.ktrParams,
                this.internalExcelCells,
                this.timetableExcelFormatType);
    }

    // ------------------ 导出用公共属性及方法，如下：------------------- //
    /** 时刻表 */
    private TTInfo ttInfo;
    /** 时刻表明细 */
    private List<TTInfoDetail> ttInfoDetailList;
    /** 导出的文件路径 */
    private String pvExportFilePath;

    // ------------------ 导出预览数据相关属性及方法，如下：---------------- //
    /** 时刻表预览excel导出策略 */
    private TimetableExcelPVDataExportStrategy timetableExcelPVDataExportStrategy;
    /**
     * 导出excel预览数据。
     */
    public DataToolsFile doPVExport() throws ScheduleException {
        return this.timetableExcelPVDataExportStrategy.doPVExport(
                this.ttInfo,
                this.ttInfoDetailList,
                this.pvExportFilePath);
    }

    // ----------- 构造函数 ---------- //
    public TimetableExcelData(ValidateBuilder validateBuilder) {
        // 公共属性
        this.excelWorkBook = validateBuilder.excelWorkBook;
        this.excelWorkBookSheet = validateBuilder.excelWorkBookSheet;
        this.timetableExcelFormatType = validateBuilder.timetableExcelFormatType;
        // 验证用属性
        this.line = validateBuilder.line;
        this.lsStationRouteList = validateBuilder.lsStationRouteList;
        this.guideboardInfoList = validateBuilder.guideboardInfoList;
        this.timetableExcelDataValidateStrategy = validateBuilder.timetableExcelDataValidateStrategy;

        // 构造内部Excel单元格二维数组
        this.initInternalExcelCells();
    }
    public TimetableExcelData(ImportBuilder importBuilder) {
        // 公共属性
        this.excelWorkBook = importBuilder.excelWorkBook;
        this.excelWorkBookSheet = importBuilder.excelWorkBookSheet;
        this.timetableExcelFormatType = importBuilder.timetableExcelFormatType;
        // 导入用属性
        this.excelFilePath = importBuilder.excelFilePath;
        this.dataToolsService = importBuilder.dataToolsService;
        this.dataToolsProperties = importBuilder.dataToolsProperties;
        this.ktrParams = importBuilder.ktrParams;
        this.timetableExcelDataImportStrategy = importBuilder.timetableExcelDataImportStrategy;

        LOG.info("参数组1, xls文件名={},sheet名字={}",
                this.excelFilePath, this.ktrParams.get("sheetname"));
        LOG.info("参数组2, 线路id={},线路名字={},线路版本={}",
                this.ktrParams.get("xlid"), this.ktrParams.get("xlname"), this.ktrParams.get("lineversion"));
        LOG.info("参数组3, 时刻表id={},时刻表名字={}",
                this.ktrParams.get("ttid"), this.ktrParams.get("ttinfoname"));

        // 构造内部Excel单元格二维数组
        this.initInternalExcelCells();
    }
    public TimetableExcelData(PVExcelExportBuilder pvExcelExportBuilder) {
        // 公共属性
        this.ttInfo = pvExcelExportBuilder.ttInfo;
        this.ttInfoDetailList = pvExcelExportBuilder.ttInfoDetailList;
        this.pvExportFilePath = pvExcelExportBuilder.pvExportFilePath;
        // 预览excel数据导出策略
        this.timetableExcelPVDataExportStrategy = pvExcelExportBuilder.timetableExcelPVDataExportStrategy;
    }

    // ----------- builder类 ----------- //
    public static ValidateBuilder withValidateBuilder() {
        return new ValidateBuilder();
    }
    /**
     * 验证excel时刻表数据用builder。
     */
    public static class ValidateBuilder {
        private ValidateBuilder() {}

        /** 导入的Excel文件路径 */
        private String excelFilePath;
        /** 导入的Excel sheet名字 */
        private String excelSheetName;
        /** 线路Id */
        private Integer lineId;
        /** 线路路由版本 */
        private Integer lineRouteVersion;
        /** 线路信息service */
        private LineService lineService;
        /** 站点路由信息service */
        private StationRouteService stationRouteService;
        /** 路牌信息service */
        private GuideboardInfoService guideboardInfoService;
        /** 时刻表excel格式类型 */
        private TimetableExcelFormatType timetableExcelFormatType;
        /** 时刻表excel验证策略 */
        private TimetableExcelDataValidateStrategy timetableExcelDataValidateStrategy;

        public ValidateBuilder setExcelFilePath(String excelFilePath) {
            this.excelFilePath = excelFilePath;
            return this;
        }

        public ValidateBuilder setExcelSheetName(String excelSheetName) {
            this.excelSheetName = excelSheetName;
            return this;
        }

        public ValidateBuilder setLineId(Integer lineId) {
            this.lineId = lineId;
            return this;
        }

        public ValidateBuilder setLineRouteVersion(Integer lineRouteVersion) {
            this.lineRouteVersion = lineRouteVersion;
            return this;
        }

        public ValidateBuilder setLineService(LineService lineService) {
            this.lineService = lineService;
            return this;
        }

        public ValidateBuilder setStationRouteService(StationRouteService stationRouteService) {
            this.stationRouteService = stationRouteService;
            return this;
        }

        public ValidateBuilder setGuideboardInfoService(GuideboardInfoService guideboardInfoService) {
            this.guideboardInfoService = guideboardInfoService;
            return this;
        }

        public ValidateBuilder setTimetableExcelFormatType(TimetableExcelFormatType timetableExcelFormatType) {
            this.timetableExcelFormatType = timetableExcelFormatType;
            return this;
        }

        public ValidateBuilder setTimetableExcelDataValidateStrategy(TimetableExcelDataValidateStrategy timetableExcelDataValidateStrategy) {
            this.timetableExcelDataValidateStrategy = timetableExcelDataValidateStrategy;
            return this;
        }

        // ---------------- 内部生成的属性 ------------------ //
        /** 待导入excel workbook对象 */
        private Workbook excelWorkBook;
        /** 待导入excel workbook工作区对象 */
        private Sheet excelWorkBookSheet;
        /** 线路信息 */
        private Line line;
        /** 线路站点路由列表（包含上下行的起终点站） */
        private List<LsStationRoute> lsStationRouteList;
        /** 路牌列表 */
        private List<GuideboardInfo> guideboardInfoList;

        public TimetableExcelData build() throws ScheduleException {
            // 0、检测时刻表excel业务格式
            if (this.timetableExcelFormatType == null) {
                throw new ScheduleException("导入的Excel类型为空！");
            }

            // 1、检测excel文件，创建excel workbook对象
            // 1-1、检测文件是否存在
            Assert.hasText(this.excelFilePath, "导入的Excel文件路径为空！");
            File file = new File(this.excelFilePath);
            if (!file.exists()) {
                throw new ScheduleException("导入的Excel文件[" + this.excelFilePath + "]不存在！");
            }
            // 1-2、检测文件类型
            DataToolsFile dataToolsFile = new DataToolsFile();
            dataToolsFile.setFile(file);
            if (DataToolsFileType.XLS.isThisType(file)) {
                dataToolsFile.setFileType(DataToolsFileType.XLS);
            } else if (DataToolsFileType.XLSX.isThisType(file)) {
                dataToolsFile.setFileType(DataToolsFileType.XLSX);
            } else {
                throw new ScheduleException("导入的Excel文件[" + this.excelFilePath + "]内部不是xls,xlsx文件！");
            }
            // 1-3、创建workbook
            this.excelWorkBook = dataToolsFile.getFileType().getWorkBook(dataToolsFile.getFile());
            // 1-4、检测sheet名字
            Assert.hasText(this.excelSheetName, "导入的Excel sheet名字为空！");
            this.excelWorkBookSheet = this.excelWorkBook.getSheet(this.excelSheetName);
            if (this.excelWorkBookSheet == null) {
                throw new ScheduleException("导入的Excel文件sheet[" + this.excelSheetName + "]不存在！");
            }

            // 2、获取线路信息，站点路由信息
            // 2-1、线路信息
            Assert.notNull(this.lineService, "线路信息service为空！");
            Assert.notNull(this.lineId, "线路Id为空！");
            this.line = this.lineService.findById(this.lineId);
            if (this.line == null) {
                throw new ScheduleException("线路[id=" + this.lineId + "]不存在！");
            }
            // 2-2、站点路由信息
            Assert.notNull(this.stationRouteService, "站点路由信息service为空！");
            Assert.notNull(this.lineRouteVersion, "线路站点路由版本为空！");
            Map<String, Object> p1 = new HashMap<>();
            p1.put("line.id_eq", this.lineId);
            p1.put("stationMark_in", "B,E"); // 起点站
            p1.put("destroy_eq", 0); // 未撤销
            p1.put("versions_eq", this.lineRouteVersion); // 带线路版本
            this.lsStationRouteList = (List<LsStationRoute>) this.stationRouteService.list_ls(p1);
            if (CollectionUtils.isEmpty(this.lsStationRouteList)) {
                throw new ScheduleException("线路[" + this.line.getName() + "]，站点路由[版本=" + this.lineRouteVersion + "]信息为空");
            }

            // 3、获取路牌信息
            Assert.notNull(this.guideboardInfoService, "路牌信息service为空！");
            p1.clear();
            p1.put("xl.id_eq", this.lineId);
            p1.put("isCancel_eq", false);
            this.guideboardInfoList = guideboardInfoService.list(p1);
            if (CollectionUtils.isEmpty(this.guideboardInfoList)) {
                throw new ScheduleException("线路[" + this.line.getName() + "]路牌信息为空！");
            }

            // 4、验证策略类
            Assert.notNull(this.timetableExcelDataValidateStrategy, "时刻表excel验证策略类为空！");

            return new TimetableExcelData(this);
        }
    }

    public static ImportBuilder withImportBuilder() {
        return new ImportBuilder();
    }
    public static class ImportBuilder {
        private ImportBuilder() {}

        /** 导入的Excel文件路径 */
        private String excelFilePath;
        /** 导入的Excel sheet名字 */
        private String excelSheetName;
        /** 时刻表Id */
        private Long ttInfoId;
        /** 线路Id */
        private Integer lineId;
        /** 线路标准信息Id */
        private Integer lineInfoId;
        /** 线路名字 */
        private String lineName;
        /** 时刻表名字 */
        private String ttInfoName;
        /** 线路路由版本 */
        private Integer lineRouteVersion;
        /** 时刻表excel格式类型 */
        private TimetableExcelFormatType timetableExcelFormatType;
        /** 数据工具服务 */
        private DataToolsService dataToolsService;
        /** 配置数据导入导出用到的配置信息 */
        private DataToolsProperties dataToolsProperties;
        /** 时刻表excel导入策略 */
        private TimetableExcelDataImportStrategy timetableExcelDataImportStrategy;

        public ImportBuilder setExcelFilePath(String excelFilePath) {
            this.excelFilePath = excelFilePath;
            return this;
        }

        public ImportBuilder setExcelSheetName(String excelSheetName) {
            this.excelSheetName = excelSheetName;
            return this;
        }

        public ImportBuilder setTtInfoId(Long ttInfoId) {
            this.ttInfoId = ttInfoId;
            return this;
        }

        public ImportBuilder setLineId(Integer lineId) {
            this.lineId = lineId;
            return this;
        }

        public ImportBuilder setLineInfoId(Integer lineInfoId) {
            this.lineInfoId = lineInfoId;
            return this;
        }

        public ImportBuilder setLineName(String lineName) {
            this.lineName = lineName;
            return this;
        }

        public ImportBuilder setTtInfoName(String ttInfoName) {
            this.ttInfoName = ttInfoName;
            return this;
        }

        public ImportBuilder setLineRouteVersion(Integer lineRouteVersion) {
            this.lineRouteVersion = lineRouteVersion;
            return this;
        }

        public ImportBuilder setTimetableExcelFormatType(TimetableExcelFormatType timetableExcelFormatType) {
            this.timetableExcelFormatType = timetableExcelFormatType;
            return this;
        }

        public ImportBuilder setDataToolsService(DataToolsService dataToolsService) {
            this.dataToolsService = dataToolsService;
            return this;
        }

        public ImportBuilder setDataToolsProperties(DataToolsProperties dataToolsProperties) {
            this.dataToolsProperties = dataToolsProperties;
            return this;
        }

        public ImportBuilder setTimetableExcelDataImportStrategy(TimetableExcelDataImportStrategy timetableExcelDataImportStrategy) {
            this.timetableExcelDataImportStrategy = timetableExcelDataImportStrategy;
            return this;
        }

        // ---------------- 内部生成的属性 ------------------ //
        /** 待导入excel workbook对象 */
        private Workbook excelWorkBook;
        /** 待导入excel workbook工作区对象 */
        private Sheet excelWorkBookSheet;
        /** ktr转换所需参数对象 */
        private Map<String, Object> ktrParams = new HashMap<>();

        public TimetableExcelData build() throws ScheduleException {
            // 0、验证策略类
            Assert.notNull(this.timetableExcelDataImportStrategy, "时刻表excel导入策略类为空！");

            // 注意：这里不做其他验证，业务上导入前必须验证的（调用验证用的builder）
            File file = new File(this.excelFilePath);
            DataToolsFile dataToolsFile = new DataToolsFile();
            dataToolsFile.setFile(file);
            if (DataToolsFileType.XLS.isThisType(file)) {
                dataToolsFile.setFileType(DataToolsFileType.XLS);
            } else if (DataToolsFileType.XLSX.isThisType(file)) {
                dataToolsFile.setFileType(DataToolsFileType.XLSX);
            }
            this.excelWorkBook = dataToolsFile.getFileType().getWorkBook(dataToolsFile.getFile());
            this.excelWorkBookSheet = this.excelWorkBook.getSheet(this.excelSheetName);

            // 设置ktr转换参数
            this.ktrParams.put("erroroutputdir", this.dataToolsProperties.getTransErrordir()); // 错误文件输出路径
            this.ktrParams.put("sheetname", this.excelSheetName); // sheet工作区的名字
            this.ktrParams.put("lineinfoid", this.lineInfoId); // 线路标准id
            this.ktrParams.put("xlname", this.lineName); // 线路名称
            this.ktrParams.put("xlid", this.lineId); // 线路id
            this.ktrParams.put("ttinfoname", this.ttInfoName); // 时刻表名称
            this.ktrParams.put("ttid", this.ttInfoId.intValue()); // 时刻表id
            this.ktrParams.put("lineversion", this.lineRouteVersion); // 站点路由版本

            return new TimetableExcelData(this);
        }
    }

    public static PVExcelExportBuilder withPVExcelExportBuilder() {
        return new PVExcelExportBuilder();
    }
    public static class PVExcelExportBuilder {
        private PVExcelExportBuilder() {}

        /** 时刻表Id */
        private Long ttInfoId;
        /** 时刻表repo */
        private TTInfoRepository ttInfoRepository;
        /** 时刻表明细repo */
        private TTInfoDetailRepository ttInfoDetailRepository;
        /** 配置文件 */
        private DataToolsProperties dataToolsProperties;
        /** 时刻表预览数据Excel导出策略 */
        private TimetableExcelPVDataExportStrategy timetableExcelPVDataExportStrategy;

        public PVExcelExportBuilder setTtInfoId(Long ttInfoId) {
            this.ttInfoId = ttInfoId;
            return this;
        }

        public PVExcelExportBuilder setTtInfoRepository(TTInfoRepository ttInfoRepository) {
            this.ttInfoRepository = ttInfoRepository;
            return this;
        }

        public PVExcelExportBuilder setTtInfoDetailRepository(TTInfoDetailRepository ttInfoDetailRepository) {
            this.ttInfoDetailRepository = ttInfoDetailRepository;
            return this;
        }

        public PVExcelExportBuilder setDataToolsProperties(DataToolsProperties dataToolsProperties) {
            this.dataToolsProperties = dataToolsProperties;
            return this;
        }

        public PVExcelExportBuilder setTimetableExcelPVDataExportStrategy(TimetableExcelPVDataExportStrategy timetableExcelPVDataExportStrategy) {
            this.timetableExcelPVDataExportStrategy = timetableExcelPVDataExportStrategy;
            return this;
        }

        // ---------------- 内部生成的属性 ------------------ //
        /** 时刻表 */
        private TTInfo ttInfo;
        /** 时刻表明细 */
        private List<TTInfoDetail> ttInfoDetailList;
        /** 导出的文件路径 */
        private String pvExportFilePath;

        public TimetableExcelData build() {
            // 1、参数验证
            Assert.notNull(this.ttInfoId, "时刻表Id为空！");
            Assert.notNull(ttInfoRepository, "时刻表repo为空！");
            Assert.notNull(ttInfoDetailRepository, "时刻表明细repo为空！");
            Assert.notNull(timetableExcelPVDataExportStrategy, "时刻表预览excel数据导出策略为空！");

            // 2、获取时刻表数据
            this.ttInfo = this.ttInfoRepository.findOneExtend(this.ttInfoId);
            Assert.notNull(this.ttInfo, "时刻表[id=" + this.ttInfoId + "]未找到！");

            // 3、获取时刻表明细数据
            this.ttInfoDetailList = this.ttInfoDetailRepository.findByTtinfoId(this.ttInfoId);

            // 4、计算导出文件路径
            Assert.notNull(this.dataToolsProperties, "dataTools配置文件类为空！");
            this.pvExportFilePath = dataToolsProperties.getFileoutputDir() +
                    File.separator +
                    ttInfo.getName() + "预览信息-" +
                    new DateTime().toString("yyyyMMddHHmmss") + ".xlsx";

            return new TimetableExcelData(this);
        }
    }

}