fj-dc/.svn/pristine/c0/c0f455650c12b7180c398d0f83aed930e184ccf1.svn-base

package cn.com.goldenwater.dcproj.utils.export.template;

import cn.com.goldenwater.dcproj.dao.AttRsBaseCrrctDao;
import cn.com.goldenwater.dcproj.dto.BisInspRsvrRgstrPcDto;
import cn.com.goldenwater.dcproj.dto.BisInspRsvrRgstrPcDtoVo;
import cn.com.goldenwater.dcproj.model.*;
import cn.com.goldenwater.dcproj.param.*;
import cn.com.goldenwater.dcproj.service.*;
import cn.com.goldenwater.dcproj.utils.ZipUtil;
import cn.com.goldenwater.dcproj.utils.export.CreateExcelHelper;
import cn.com.goldenwater.dcproj.utils.export.ExcelFieldUtil;
import cn.com.goldenwater.dcproj.utils.export.TableEnum;
import cn.com.goldenwater.util.common.FileUtils;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 * 创建水库的表格通过模板
 * </p>
 *
 * @author liyz
 * @date 2019/3/19 9:20
 */

@Component
public class CreateSkTablesByTemplate {

    private final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Value("${export.basePath}")
    public String exportBasePath;
    @Autowired
    private ExcelFieldUtil excelFieldUtil;
    @Autowired
    private CreateExcelHelper createExcelHelper;

    @Value("${export.templatePath}")
    private String templatePath;

    /**
     * 8.小水库督查情况Service
     */
    @Autowired
    private BisInspRsvrRgstrService bisInspRsvrRgstrService;

    /* 5张表合成1张表 */

    /**
     * 5-1 基础信息
     **/
    @Autowired
    private AttRsBaseCrrctDao attRsBaseCrrctDao;
    @Autowired
    private AttRsBaseService attRsBaseService;

    /**
     * 5-2 重点环节落实情况
     **/
    @Autowired
    private BisInspBaseExtService bisInspBaseExtService;

    /**
     * 5-3 责任人落实情况
     **/
    @Autowired
    private BisInspBasePresExtService bisInspBasePresExtService;
    /**
     * 5-4 水库工程实体和运行情况
     **/
    @Autowired
    private BisInspSafeExtManageService bisInspSafeExtManageService;
    /**
     * 5-5 走访情况
     **/
    @Autowired
    private BisInspBaseViewService bisInspBaseViewService;
    @Autowired
    private BisInspPreListService bisInspPreListService;

    @Autowired
    private BisInspRsvrProjectService bisInspRsvrProjectService;
    @Autowired
    private BisInspRsvrBenefitsService bisInspRsvrBenefitsService;

    /**
     * 8.创建小水库督查情况表
     */
    @Deprecated
    public void createBisInspRsvrRgstrPcDtoExcel(TypeExportParam typeExportParam, PrintWriter printWriter) {

        // 一页查200条数据
        typeExportParam.setPageNum(0);
        typeExportParam.setPageSize(40000);
        PageInfo<BisInspRsvrRgstrPcDto> data = bisInspRsvrRgstrService.findPcPage(typeExportParam);

        // 如果没有数据，则直接返回
        if (data.getList().size() == 0) {
            printWriter.println("暂无数据可导出。<br>");
            printWriter.flush();
            return;
        }

        OutputStream os = null;

        printWriter.println("准备导出，开始创建导出目录<br>");
        printWriter.flush();

        String fileName = TableEnum.T8.getName();
        printWriter.println(sdf.format(new Date()) + " 开始创建 " + fileName + ".xls" + "<br>");
        printWriter.flush();

        //导出
        try {

            String filePath = UUID.randomUUID().toString();
            String dir = exportBasePath + File.separator + filePath + File.separator;
            String zipFile = exportBasePath + File.separator + filePath + ".zip";
            FileUtils.mkFolder(dir);


            // 加载模板
            FileInputStream fis = new FileInputStream(templatePath + File.separator + "t8.xls");
            HSSFWorkbook fisWorkBook = new HSSFWorkbook(fis);
            HSSFWorkbook workBook = new HSSFWorkbook();
            workBook = fisWorkBook;
            workBook.setSheetName(0, "小水库督查情况表");
            HSSFSheet sheet = workBook.getSheetAt(0);
            HSSFCellStyle style = workBook.createCellStyle(); // 样式对象
            style.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font = workBook.createFont();
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HorizontalAlignment.CENTER);
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 10);// 设置字体大小
            style.setFont(font);

            // 如果有数据，则导出到excel
            File outputFile = new File(dir + fileName + (System.currentTimeMillis()) + ".xls");
            os = new FileOutputStream(outputFile);
            // 导出到excel
            // 处理一下，加入5张表的数据
            List<Map<String, Object>> list = excelFieldUtil.getBisInspRsvrRgstrPcDtoDataList(getBisVo(data.getList(), typeExportParam));
            for (int i = 0; i < list.size(); i++) {
                HSSFRow creRow = sheet.createRow(4 + i);

                creRow.createCell(0).setCellValue(list.get(i).get("province").toString()); // 省
                creRow.createCell(1).setCellValue(list.get(i).get("city").toString()); // 市
                creRow.createCell(2).setCellValue(list.get(i).get("county").toString()); // 县

                creRow.createCell(3).setCellValue(list.get(i).get("nm").toString());
                creRow.createCell(4).setCellValue(list.get(i).get("rsCode").toString());
                creRow.createCell(5).setCellValue(list.get(i).get("rsLoc").toString());
                creRow.createCell(6).setCellValue(list.get(i).get("compDate").toString());
                creRow.createCell(7).setCellValue(list.get(i).get("engScal1").toString());
                creRow.createCell(8).setCellValue(list.get(i).get("totCap").toString());
                creRow.createCell(9).setCellValue(list.get(i).get("damSizeHig").toString());
                creRow.createCell(10).setCellValue(list.get(i).get("damTypeStr1").toString());
                creRow.createCell(11).setCellValue(list.get(i).get("regCode").toString());
                creRow.createCell(12).setCellValue(list.get(i).get("regCode2").toString());
                // 三项基本要求落实情况
                creRow.createCell(13).setCellValue(list.get(i).get("emerPlanSameExta").toString());
                creRow.createCell(14).setCellValue(list.get(i).get("emerPlanSameAppr").toString());
                creRow.createCell(15).setCellValue(list.get(i).get("emerPlanSameSpeed").toString());
                creRow.createCell(16).setCellValue(list.get(i).get("emerPlanSameEme").toString());
                creRow.createCell(17).setCellValue(list.get(i).get("schPlanSameExta").toString());
                creRow.createCell(18).setCellValue(list.get(i).get("schPlanSameAppr").toString());
                creRow.createCell(19).setCellValue(list.get(i).get("schPlanSameSpeed").toString());
                creRow.createCell(20).setCellValue(list.get(i).get("rainForc").toString());
                // 履行责任情况总体评价
                creRow.createCell(21).setCellValue("");
                // 管理机构
                creRow.createCell(22).setCellValue(list.get(i).get("hasSpeManage").toString()); // 是否有专门管理机构
                creRow.createCell(23).setCellValue(list.get(i).get("manaOrg").toString()); //管理机构名称
                creRow.createCell(24).setCellValue(list.get(i).get("orgProp").toString()); //管理机构性质
                creRow.createCell(25).setCellValue(list.get(i).get("fundSour").toString());// 管理机构经费来源
                creRow.createCell(26).setCellValue(list.get(i).get("fundSourCount").toString());
                creRow.createCell(27).setCellValue(list.get(i).get("hasManagePers").toString()); // 是否有管护人员

                //行政责任人情况
                creRow.createCell(28).setCellValue(list.get(i).get("hasWiunWaoLegPers").toString());
                creRow.createCell(29).setCellValue(list.get(i).get("wiunWaoLegPers").toString());
                creRow.createCell(30).setCellValue(list.get(i).get("wiunWaoLegPersTel").toString());
                creRow.createCell(31).setCellValue(list.get(i).get("wiunWaoLegPersDuty").toString());
                creRow.createCell(32).setCellValue(list.get(i).get("wiunWaoLegPersResu").toString());

                //技术责任人情况
                creRow.createCell(33).setCellValue(list.get(i).get("hasTechPers").toString());
                creRow.createCell(34).setCellValue(list.get(i).get("techPers").toString());
                creRow.createCell(35).setCellValue(list.get(i).get("techPersTel").toString());
                creRow.createCell(36).setCellValue(list.get(i).get("techPersDuty").toString());
                creRow.createCell(37).setCellValue(list.get(i).get("techPersResu").toString());

                //巡查责任人情况
                creRow.createCell(38).setCellValue(list.get(i).get("hasPatrolPers").toString());
                creRow.createCell(39).setCellValue(list.get(i).get("patrolPers").toString());
                creRow.createCell(40).setCellValue(list.get(i).get("patrolPersDuty").toString());
                creRow.createCell(41).setCellValue(list.get(i).get("hasAttendTrain").toString()); // 是否参加过培训
                creRow.createCell(42).setCellValue(list.get(i).get("patrolPersResu").toString());
                creRow.createCell(43).setCellValue(list.get(i).get("patrolReward").toString()); //管护报酬（万元/年）
                creRow.createCell(44).setCellValue(list.get(i).get("patrolFundSour").toString()); //经费来源

                // 履责情况
                creRow.createCell(45).setCellValue(""); //管理制度制定情况
                creRow.createCell(46).setCellValue(list.get(i).get("hasExtPatrol").toString()); //是否开展巡查
                creRow.createCell(47).setCellValue(list.get(i).get("protalRate").toString());//巡视检查频次（次/月）
                creRow.createCell(48).setCellValue(list.get(i).get("isPatrolMsgComp").toString()); // 巡视检查记录是否完整
                creRow.createCell(49).setCellValue(list.get(i).get("hasRepDangReal").toString());//有险情是否及时报告
                creRow.createCell(50).setCellValue(list.get(i).get("dangReportObj").toString()); //险情报告对象
                creRow.createCell(51).setCellValue(list.get(i).get("hasReport").toString()); //是否有报告记录
                creRow.createCell(52).setCellValue(list.get(i).get("hasFlLowLimLev").toString()); //是否知晓防洪限制水位
                creRow.createCell(53).setCellValue(list.get(i).get("hasKnowEmerPlanSame").toString()); //是否知晓安全应急（防汛）应急预案

                //安全运行管理情况
                creRow.createCell(54).setCellValue(list.get(i).get("safeComment").toString()); //水库安全总体评价
                creRow.createCell(55).setCellValue(list.get(i).get("hasRetainRegular").toString()); //目前是否正常蓄水运行
                creRow.createCell(56).setCellValue(list.get(i).get("noRetainRegular").toString());//有缺陷基本能正常蓄水
                creRow.createCell(57).setCellValue(list.get(i).get("canNotRetainWater").toString()); // 不具备正常蓄水条件
                // 挡水建筑物运行状况
                creRow.createCell(58).setCellValue(list.get(i).get("dikeHasLeak").toString());//是否明显漏水
                creRow.createCell(59).setCellValue(list.get(i).get("leakMakeSafe").toString()); //漏水是否影响安全
                creRow.createCell(60).setCellValue(list.get(i).get("hasAstable").toString()); //是否有变形或不稳定情况
                // 泄洪建筑物运行状况
                creRow.createCell(61).setCellValue(list.get(i).get("hasSetMainFl").toString()); //是否设置泄洪建筑物
                creRow.createCell(62).setCellValue(list.get(i).get("isMainFlNormal").toString()); //泄洪建筑物是否运行正常
                creRow.createCell(63).setCellValue(""); //能运行有缺陷
                creRow.createCell(64).setCellValue(""); //是否设置泄洪闸门
                creRow.createCell(65).setCellValue(""); //泄洪闸门启闭是否正常
                creRow.createCell(66).setCellValue(""); //未设置泄洪建筑物原因
                // 放水建筑物运行状况
                creRow.createCell(67).setCellValue(list.get(i).get("hasSetPourFl").toString()); //是否设置放水建筑物
                creRow.createCell(68).setCellValue(""); //放水建筑物是否运行正常
                creRow.createCell(69).setCellValue(""); //能运行有缺陷
                creRow.createCell(70).setCellValue(""); //是否设置放水闸门

                //安全运行管理情况
                //放水建筑物运行状况
                creRow.createCell(71).setCellValue(""); //放水闸门启闭是否正常
                creRow.createCell(72).setCellValue(""); //未设置放水建筑物原因

                //是否设置防洪限制水位
                creRow.createCell(73).setCellValue(list.get(i).get("havFlLowLimLev").toString()); //是否设置防洪限制水位
                creRow.createCell(74).setCellValue(list.get(i).get("gtFlLowLimLev").toString()); //是否超防洪限制水位运行
                //大坝安全鉴定
                creRow.createCell(75).setCellValue(list.get(i).get("extDamSafeAppr").toString()); //是否开展过大坝安全鉴定
                creRow.createCell(76).setCellValue(""); //最近一次鉴定时间
                creRow.createCell(77).setCellValue("");//最近一次鉴定结论是否  三类坝
                creRow.createCell(78).setCellValue(""); //安全鉴定是否符合要求
                creRow.createCell(79).setCellValue(""); //未开展安全鉴定原因

                //水库除险加固
                creRow.createCell(80).setCellValue(list.get(i).get("ifReinForce").toString()); //是否进行过除险加固
                creRow.createCell(81).setCellValue(""); //完成除险加固时间
                creRow.createCell(82).setCellValue("");//是否通过蓄水验收
                creRow.createCell(83).setCellValue(""); //验收时间
                creRow.createCell(84).setCellValue(""); //是否全面完成加固任务
                creRow.createCell(85).setCellValue("");//未完成的加固任务是否   影响安全度汛
                creRow.createCell(86).setCellValue(""); //未开展除险加固原因
                creRow.createCell(87).setCellValue(""); //目前是否仍存在险情

                creRow.createCell(88).setCellValue(""); //水面是否出租
                creRow.createCell(89).setCellValue(""); //水面出租是否影响防洪
                creRow.createCell(90).setCellValue(""); //其他影响安全的因素

                // 走访情况
                creRow.createCell(91).setCellValue(list.get(i).get("viewPreNum").toString()); //走访人数
                creRow.createCell(92).setCellValue(list.get(i).get("viewPreName").toString()); //走访人姓名
                // 走访内容
                creRow.createCell(93).setCellValue(list.get(i).get("knowResManager").toString()); //是否知晓水库管护人员
                creRow.createCell(94).setCellValue(list.get(i).get("managerComment").toString()); //对管护责任人的评价

                creRow.createCell(95).setCellValue(list.get(i).get("groupLeader").toString()); //组长
                creRow.createCell(96).setCellValue(list.get(i).get("groupLeaderTel").toString()); //组长电话
                creRow.createCell(97).setCellValue(list.get(i).get("liaisonMan").toString()); //联络员
                creRow.createCell(98).setCellValue(list.get(i).get("liaisonManTel").toString()); //联络员电话


                creRow.setRowStyle(style);


            }


            int pages = data.getPages();
            // 如果超过一页
            workBook.write(os);
            fis.close();
            os.flush();
            os.close();

            printWriter.println(sdf.format(new Date()) + " 完成创建 " + fileName + ".xls" + "<br>");
            printWriter.flush();


            // 指定压缩源，可以是目录或文件
            String src = dir;
            // 压缩包路径
            String archive = zipFile;
            // 压缩包注释
            String comment = "Java Zip SLDC";
            // 压缩文件或目录
            ZipUtil.zip(src, archive, comment);

            printWriter.write("创建完成。压缩完成。 <a href='/api/dc/gd/base/download/" + filePath + "' download>点击下载</a>");
            printWriter.flush();
        } catch (Exception e) {
            try {
                os.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }

    }

    List<BisInspRsvrRgstrPcDtoVo> getBisVo(List<BisInspRsvrRgstrPcDto> dataList, TypeExportParam typeExportParam) {
        List<BisInspRsvrRgstrPcDtoVo> dataListVo = new ArrayList<>();
        dataList.forEach(x -> {
            BisInspRsvrRgstrPcDtoVo bisVo = new BisInspRsvrRgstrPcDtoVo();
            // 放入督查情况表
            bisVo.setBisInspRsvrRgstrPcDto(x);
            // 1.放入基础信息
            bisVo.setAttRsBaseCrrct(getAttRsBaseCrrctBy(x, typeExportParam));
            bisVo.setAttRsBase(getAttRsBaseBy(x));
            // 2.重点环节落实情况
            bisVo.setBisInspBaseExt(getBisInspBaseExtBy(x));
            // 3.责任人落实情况
            bisVo.setBisInspBasePresExt(getBisInspBasePresExtBy(x));
            // 4.水库工程实体和运行情况
            bisVo.setBisInspSafeExtManage(getBisInspSafeExtManageBy(x));
            // 5.走访情况
            bisVo.setBisInspPreList(getBisInspPreListBy(x));
            // 6.省 市 县 其他
            String adCode;
            if (bisVo.getAttRsBaseCrrct() != null) {
                adCode = bisVo.getAttRsBaseCrrct().getAdmDiv();
            } else {
                adCode = bisVo.getAttRsBase().getAdmDiv();
            }
            bisVo.setHashMap(getHashMap(adCode));
            dataListVo.add(bisVo);
        });
        return dataListVo;
    }

    AttRsBaseCrrct getAttRsBaseCrrctBy(BisInspRsvrRgstrPcDto bisInspRsvrRgstrPcDto, TypeExportParam typeExportParam) {
        String rsCode = bisInspRsvrRgstrPcDto.getCode();
        String persId = typeExportParam.getPresId();
        return attRsBaseCrrctDao.get(rsCode, persId);
    }

    AttRsBase getAttRsBaseBy(BisInspRsvrRgstrPcDto bisInspRsvrRgstrPcDto) {
        String rsCode = bisInspRsvrRgstrPcDto.getCode();
        AttRsBase att = attRsBaseService.get(rsCode);
        return att == null ? new AttRsBase() : att;
    }

    BisInspBaseExt getBisInspBaseExtBy(BisInspRsvrRgstrPcDto bisInspRsvrRgstrPcDto) {
        BisInspBaseExtParam bisInspBaseExtParam = new BisInspBaseExtParam();
        String rgstrId = bisInspRsvrRgstrPcDto.getRgstrId();
        BisInspBaseExt bisInspBaseExt = new BisInspBaseExt();
        if (rgstrId != null) {
            bisInspBaseExtParam.setRgstrId(rgstrId);
            bisInspBaseExt = bisInspBaseExtService.getBy(bisInspBaseExtParam);
        }
        return bisInspBaseExt == null ? new BisInspBaseExt() : bisInspBaseExt;
    }

    /**
     * 3 根据督查登记表ID获取三个责任人基本信息
     */
    BisInspBasePresExt getBisInspBasePresExtBy(BisInspRsvrRgstrPcDto bisInspRsvrRgstrPcDto) {
        BisInspBasePresExtParam bisInspBasePresExtParam = new BisInspBasePresExtParam();
        String rgstrId = bisInspRsvrRgstrPcDto.getRgstrId();
        BisInspBasePresExt inspBaseExt = new BisInspBasePresExt();
        if (rgstrId != null) {
            bisInspBasePresExtParam.setRgstrId(rgstrId);
            try {
                // 可能会返回多条，脏数据问题
                inspBaseExt = bisInspBasePresExtService.getBy(bisInspBasePresExtParam);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }

        return inspBaseExt == null ? new BisInspBasePresExt() : inspBaseExt;
    }

    /**
     * 4 水库工程实体和运行管理情况
     */
    BisInspSafeExtManage getBisInspSafeExtManageBy(BisInspRsvrRgstrPcDto bisInspRsvrRgstrPcDto) {
        BisInspSafeExtManageParam safeExtManageParam = new BisInspSafeExtManageParam();
        String rgstrId = bisInspRsvrRgstrPcDto.getRgstrId();
        BisInspSafeExtManage bisInspSafeExtManage = new BisInspSafeExtManage();
        if (rgstrId != null) {
            safeExtManageParam.setRgstrId(rgstrId);
            try {
                // 可能会返回多条，脏数据问题
                List<BisInspSafeExtManage> bisInspSafeExtManageList = bisInspSafeExtManageService.findList(safeExtManageParam);
                if (bisInspSafeExtManageList.size() > 0) {
                    bisInspSafeExtManage = bisInspSafeExtManageList.get(0);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return bisInspSafeExtManage == null ? new BisInspSafeExtManage() : bisInspSafeExtManage;
    }

    /**
     * 水库工程实体
     */
    BisInspRsvrProject getBisInspRsvrProjectBy(BisInspRsvrRgstrPcDto bisInspRsvrRgstrPcDto) {
        BisInspRsvrProjectParam bisInspRsvrProjectParam = new BisInspRsvrProjectParam();
        String rgstrId = bisInspRsvrRgstrPcDto.getRgstrId();
        BisInspRsvrProject bisInspRsvrProject = new BisInspRsvrProject();
        if (rgstrId != null) {
            bisInspRsvrProjectParam.setRgstrId(rgstrId);
            try {
                // 可能会返回多条，脏数据问题
                List<BisInspRsvrProject> bisInspRsvrProjectList = bisInspRsvrProjectService.findList(bisInspRsvrProjectParam);
                if (bisInspRsvrProjectList.size() > 0) {
                    bisInspRsvrProject = bisInspRsvrProjectList.get(0);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return bisInspRsvrProject == null ? new BisInspRsvrProject() : bisInspRsvrProject;
    }

    /**
     * 6 效益发挥情况
     **/
    BisInspRsvrBenefits getBisInspRsvrBenefitsBy(BisInspRsvrRgstrPcDto bisInspRsvrRgstrPcDto) {
        BisInspRsvrBenefitsParam bisInspRsvrBenefitsParam = new BisInspRsvrBenefitsParam();
        String rgstrId = bisInspRsvrRgstrPcDto.getRgstrId();
        BisInspRsvrBenefits bisInspRsvrBenefits = new BisInspRsvrBenefits();
        if (rgstrId != null) {
            bisInspRsvrBenefitsParam.setRgstrId(rgstrId);
            try {
                // 可能会返回多条，脏数据问题
                List<BisInspRsvrBenefits> bisInspRsvrBenefitsList = bisInspRsvrBenefitsService.findList(bisInspRsvrBenefitsParam);
                if (bisInspRsvrBenefitsList.size() > 0) {
                    bisInspRsvrBenefits = bisInspRsvrBenefitsList.get(0);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return bisInspRsvrBenefits == null ? new BisInspRsvrBenefits() : bisInspRsvrBenefits;
    }

    /**
     * 5 走访情况
     */
    List<BisInspPreList> getBisInspPreListBy(BisInspRsvrRgstrPcDto bisInspRsvrRgstrPcDto) {
        BisInspBaseViewParam bisInspBaseViewParam = new BisInspBaseViewParam();
        String rgstrId = bisInspRsvrRgstrPcDto.getRgstrId();
        BisInspBaseView inspBaseView = new BisInspBaseView();
        if (rgstrId != null) {
            bisInspBaseViewParam.setRgstrId(rgstrId);
            inspBaseView = bisInspBaseViewService.getBy(bisInspBaseViewParam);
        }
        List<BisInspPreList> bisInspPreList = new ArrayList<>();
        if (inspBaseView != null) {
            BisInspPreListParam inspPreListParam = new BisInspPreListParam();
            inspPreListParam.setViewId(inspBaseView.getViewId());
            bisInspPreList = bisInspPreListService.findList(inspPreListParam);
        }
        return bisInspPreList;
    }


    /**
     * 一个HashMap对象存放其他信息
     */
    HashMap getHashMap(String adCode) {
        return createExcelHelper.getAdNames(adCode);
    }
}