dianwangAdm/src/main/java/com/minpay/reportManage/action/XlgzzReportAction.java

package com.minpay.reportManage.action;

import com.minpay.common.bean.User;
import com.minpay.common.constant.Constant;
import com.minpay.common.format.IFormatService;
import com.minpay.common.service.IReportService;
import com.minpay.common.util.DateUtil;
import com.minpay.common.util.ExportUtils;
import com.minpay.db.table.mapper.DwReportInfMapper;
import com.minpay.db.table.model.DwReportInf;
import com.minpay.db.table.own.mapper.IReportBatchMapper;
import com.minpay.db.table.own.mapper.ReportManageMapper;
import com.minpay.db.table.own.mapper.SequenceMapper;
import com.startup.minpay.frame.business.IMINAction;
import com.startup.minpay.frame.business.res.MINActionResult;
import com.startup.minpay.frame.constant.IMINBusinessConstant;
import com.startup.minpay.frame.constant.IMINTransactionEnum;
import com.startup.minpay.frame.exception.MINBusinessException;
import com.startup.minpay.frame.jdbc.MINRowBounds;
import com.startup.minpay.frame.service.base.IMINDataBaseService;
import com.startup.minpay.frame.service.base.Service;
import com.startup.minpay.frame.session.MINSession;
import com.startup.minpay.frame.target.MINAction;
import com.startup.minpay.frame.target.MINComponent;
import com.startup.minpay.frame.target.MINParam;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.*;

@MINComponent
public class XlgzzReportAction implements IMINAction {
    private IMINDataBaseService db;
    /** 报表查询 */
    public final static String REPORT_INF_QUERY = "reportInfQuery";


    /** 根据不同的报表类型查询对应时间范围类的所需数据 */
    public final static String REPORT_NEED_DATA_DETAIL = "reportNeedDataDetail";

    public final static String REPORT_NEED_DATA_DETAIL2 = "reportNeedDataDetail2";

    /** 生成报表 */
    public final static String FILE_INF_REPORT_SUBMIT = "fileInfReportSubmit";

    /** 报告下载 */
    public final static String REPORT_DOWN_LOAD = "reportDownLoad";

    /** 报告修改 */
    public final static String REPORT_INF_UPDATE = "reportInfUpdate";

    @MINAction(value = REPORT_INF_QUERY)
    public MINActionResult reportInfQuery(
            @MINParam(key = "page", defaultValue = "1") int page,
            @MINParam(key = "limit", defaultValue = "10") int limit,
            @MINParam(key = "date") String date,
            @MINParam(key = "fileName") String fileName,
            @MINParam(key = "timeType") String timeType,
            @MINParam(key = "reportType") String reportType,
            MINSession session
    ) throws MINBusinessException {
        MINActionResult res = new MINActionResult();
        db = Service.lookup(IMINDataBaseService.class);
        //分页
        MINRowBounds rows = new MINRowBounds(page, limit);
        rows.setSeparateSql(true);
        Map<String, Object> param = new HashMap<String, Object>();

        param.put("date", date);
        param.put("fileName", fileName);
        param.put("timeType", timeType);
        param.put("reportType", reportType);

        User user = session.getUser();
        String roleId = user.getRoleId();

        // 非系统管理员
        if (!Constant.ROLE_ID.equals(roleId)) {
            param.put("branchId", user.getBranchid());
        }

        List<Map<String, String>> list = db.getMybatisMapper(ReportManageMapper.class).selectReportInf(param, rows);
        list = Service.lookup(IFormatService.class).formatDate(list, "uploadDate");
        // 设置返回值
        res.set(IMINBusinessConstant.F_PAGING_LAY, list);
        res.set(IMINBusinessConstant.F_PAGING_COUNT, rows.getCount());
        return res;
    }
    @MINAction(value = FILE_INF_REPORT_SUBMIT, transaction = IMINTransactionEnum.CMT)
    public MINActionResult fileInfReportSubmit(
            @MINParam(key = "reportData") String reportData,
            @MINParam(key = "reportInf") String reportInf,
            @MINParam(key = "fileName") String fileName,
            @MINParam(key = "monthNum") String monthNum,
            @MINParam(key = "countNum") String countNum,
            @MINParam(key = "range") String range,
            @MINParam(key = "timeRange") String timeRange,
            @MINParam(key = "firstFileId") String firstFileId,
            @MINParam(key = "secondFileId") String secondFileId,
            @MINParam(key = "ddyFxbId") String ddyFxbId,
            @MINParam(key = "yearChoose") String yearChoose,
            @MINParam(key = "monthChoose") String monthChoose,
            MINSession session
    ) throws MINBusinessException {
        db = Service.lookup(IMINDataBaseService.class);
        MINActionResult res = new MINActionResult();

        String reportId = db.getMybatisMapper(SequenceMapper.class).getSequence("REPORT_INF_NO");

        String reportType = reportInf.split("_")[1];
        String reportTypeId = reportInf.split("_")[0];
        // 不生成报表数据表，实时计算
//        DwReportData reportDataInf = new DwReportData();
//        reportDataInf.setId(reportId);//
//        reportDataInf.setData(reportData);
//        reportDataInf.setType(reportType);
//        db.insertSelective(DwReportDataMapper.class, reportDataInf);

        User user = session.getUser();
        // 生成报告表
        DwReportInf report = new DwReportInf();
        report.setId(reportId);//报表ID
        report.setUploadDate(DateUtil.getCurrentDateString());//更新时间
        report.setFileName(fileName);//文件名
        report.setCreatUser(user.getId());//当前登录用户ID
        report.setBranch(user.getBranchid());//所属结构(班组)
        report.setType(reportTypeId);//dw_branch_report_type.DBRT_ID
        report.setZdttyCompareId(firstFileId + "-" + secondFileId);
        report.setTqtdMonth(monthNum);	// 台区停电【month个月内停电count次及以上台区】的month
        report.setTqtdCount(countNum);	// 台区停电【month个月内停电count次及以上台区】的count
        report.setDdyFxbId(ddyFxbId);

        report.setDayInterval(range.replaceAll(" ", ""));		// 日期时间段
        report.setTimeInterval(timeRange.replaceAll(" ", ""));	// 时分秒时间段

        report.setYearInterval(yearChoose.replaceAll(" ", ""));
        report.setMonthInterval(monthChoose.replaceAll(" ", ""));
        db.insertSelective(DwReportInfMapper.class, report);

        return res;
    }
    @MINAction(value = REPORT_NEED_DATA_DETAIL)
    public MINActionResult reportNeedDataDetail(
            @MINParam(key = "monthChoose") String monthChoose,
            @MINParam(key = "range") String range,
            @MINParam(key = "timeRange") String timeRange,
            @MINParam(key = "reportInf") String reportInf,
            @MINParam(key = "monthNum") String monthNum,
            @MINParam(key = "countNum") String countNum,
            @MINParam(key = "firstFileId") String firstFileId,
            @MINParam(key = "secondFileId") String secondFileId,
            @MINParam(key = "FADateRange") String FADateRange,
            @MINParam(key = "yearChoose") String yearChoose,
            @MINParam(key = "DdyDateRange") String DdyDateRange,
            @MINParam(key = "ddyFxbId") String ddyFxbId
    ) throws MINBusinessException, ParseException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InvalidFormatException, IOException {
        MINActionResult res = new MINActionResult();

        // 报表类型id
        String reportTypeId = reportInf.split("_")[0];

        Map<String, Object> param = new HashMap<String, Object>();
        param.put("monthChoose", monthChoose);
        param.put("range", range);
        param.put("timeRange", timeRange);
        param.put("reportTypeId", reportTypeId);
        param.put("monthNum", monthNum);
        param.put("countNum", countNum);
        param.put("firstFileId", firstFileId);
        param.put("secondFileId", secondFileId);
        param.put("FADateRange", FADateRange);
        param.put("yearChoose", yearChoose);
        param.put("DdyDateRange", DdyDateRange);
        param.put("ddyFxbId", ddyFxbId);
        Map<String, Object> resMap = Service.lookup(IReportService.class).reportNeedDetail(param);
        res.set(IMINBusinessConstant.F_PAGING_LAY, resMap);
        return res;
    }

    @MINAction(value = REPORT_NEED_DATA_DETAIL2)
    public MINActionResult reportNeedDataDetail2(
            @MINParam(key = "reportId") String reportId,
            @MINParam(key = "reportType") String reportType
    ) throws MINBusinessException, ParseException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InvalidFormatException, IOException {
        MINActionResult res = new MINActionResult();
        db = Service.lookup(IMINDataBaseService.class);

        // 报表类型id
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("reportId", reportId);
        param.put("reportTypeId",reportType);
        if("XLGZZRB".equals(reportType)){
            param.put("timeType","00");
        }else{
            param.put("timeType","01");
        }
        String fileName = "";
        List<Map<String,String>> list = db.getMybatisMapper(IReportBatchMapper.class).selectReportInfo(param);
        if(list != null && !list.isEmpty()){
            param.put("range",list.get(0).get("DAYINTERVAL")) ;
            param.put("timeRange",list.get(0).get("TIMEINTERVAL"));
            fileName = list.get(0).get("FILENAME");
        }else{
            param.put("range","19000101-19000101") ;
            param.put("timeRange","000000-235959");
        }
        Map<String, Object> resMap = Service.lookup(IReportService.class).reportNeedDetail(param);
        resMap.put("fileName",fileName);
        res.set(IMINBusinessConstant.F_PAGING_LAY, resMap);
        return res;
    }

    @MINAction(value = REPORT_DOWN_LOAD)
    public MINActionResult reportDownLoad(
            @MINParam(key = "reportId") String reportId,
            @MINParam(key = "timeType") String timeType,
            @MINParam(key = "reportType") String reportType,
            HttpServletResponse response
    ) throws Exception {
        MINActionResult res = new MINActionResult();
        db = Service.lookup(IMINDataBaseService.class);
        // 报表类型id
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("reportId", reportId);
        param.put("reportTypeId",reportType);
        param.put("timeType",timeType);
        List<Map<String,String>> list = db.getMybatisMapper(IReportBatchMapper.class).selectReportInfo(param);
        String fileName = "";
        if(list != null && !list.isEmpty()){
            param.put("range",list.get(0).get("DAYINTERVAL")) ;
            param.put("timeRange",list.get(0).get("TIMEINTERVAL"));
            fileName = list.get(0).get("FILENAME");
        }else{
            param.put("range","19000101-19000101") ;
            param.put("timeRange","000000-235959");
        }
        if("".equals(fileName)){
            fileName = "线路重过载日报";
        }
        Map<String, Object> resMap = Service.lookup(IReportService.class).reportNeedDetail(param);
        //导出Excel
        OutputStream out = null;
        out = response.getOutputStream();
        XSSFWorkbook wb = export(response, fileName, resMap);
        try {
            if(wb != null){
                wb.write(out);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(wb != null){

            }
            if(out != null){
                out.flush();
                out.close();
            }
        }
        return res;
    }
    public <T> XSSFWorkbook export(HttpServletResponse response, String fileName, Map<String, Object> map) throws Exception {
        // 设置请求
        response.setContentType("application/application/vnd.ms-excel");
        response.setHeader("Content-disposition",
                "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        // 创建一个Workbook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        //******************************************线路重载明细******************************************//
        // 标题数组
        String[] titleArray = new String[]{"单位","供电所","线路名称","重载时长(h)","平均负载率(%)"};
        // 字段名数组
        String[] fieldArray = new String[]{"DFD17_XGSMC","DFD17_BZMC","DFD17_XLMC","sum","avg"};
        Map<String,Object> temp = (Map<String, Object>) map.get("resList");
        List<String> tempList = (List<String>) temp.get("descStr");
        StringBuffer descxl = new StringBuffer();
        descxl
                .append("  ")
                .append(tempList.get(0))
                .append("\r\n")
                .append("  ")
                .append(tempList.get(1))
                .append("\n")
                .append("  ")
                .append("注：线路负载率持续1小时在70%-100%为重载，持续1小时在100%以上为过载。");
        List<Map<String,String>> dataList = (List<Map<String, String>>) temp.get("result");
        int index = 0;
        ExportUtils.XlzgzCreateSheet(wb,index,"线路重载明细",titleArray,fieldArray,descxl.toString(),dataList,new HashMap<String,String>());

        //******************************************线路过载明细******************************************//
        titleArray[3] = "过载时长(h)";
        List<Map<String,String>> dataList2 = (List<Map<String, String>>) temp.get("result2");
        ExportUtils.XlzgzCreateSheet(wb,index,"线路过载明细",titleArray,fieldArray,"",dataList2,new HashMap<String,String>());

        //******************************************公变过载明细******************************************//
        String[] titleArray2 = new String[]{"单位","供电所","公变名称","重载时长(h)","平均负载率(%)","原因","解决措施"};
        String[] fieldArray2 = new String[]{"DFD13_UNIT","DFD13_BZMC","DFD13_GBMC","DFD13_ZZSC","avg","DFD13_JTYY","DFD13_JTJJCS"};
        List<String> tempList2 = (List<String>) temp.get("descStr2");
        StringBuffer descxl2 = new StringBuffer();
        descxl2
                .append("  ")
                .append(tempList2.get(0))
                .append("\r\n")
                .append("  ")
                .append(tempList2.get(1))
                .append("\r\n")
                .append("  ")
                .append("注：配变负载率连续2小时在80%-100%之间为重载，连续2小时在100%以上为过载。");
        List<Map<String,String>> dataList4 = (List<Map<String, String>>) temp.get("result4");
        ExportUtils.XlzgzCreateSheet(wb,index,"公变重载明细",titleArray2,fieldArray2,descxl2.toString(),dataList4,new HashMap<String,String>());

        //******************************************公变重载明细******************************************//
        titleArray2[3] = "过载时长(h)";
        fieldArray2[3] = "DFD13_GZSC";
        List<Map<String,String>> dataList3 = (List<Map<String, String>>) temp.get("result3");
        index = ExportUtils.XlzgzCreateSheet(wb,index,"公变过载明细",titleArray2,fieldArray2,"",dataList3,new HashMap<String,String>());

//        //******************************************抢修类工单明细******************************************//
//        String[] titleArray3 = new String[]{"单位","客户总量（户）","平均到达现场时间（分）","工单处理平均时长（分）","到达现场超时数",
//                "工单时长超70分钟数","故障类型#客户内部故障","故障类型#低压故障","故障类型#非电力故障","故障类型#电能质量等故障","疑似虚假回单","工单总数","万户报修率"};
//        String[] fieldArray3 = new String[]{"DFD11_GDDW","KHZS","DDXCSJ","GDCLSJ","DDXCCSS","GDCSZS","GZLS-A","GZLS-B","GZLS-C","GZLS-D","YSXJHD","GDZS","WH","","","",""};
//        List<Map<String,String>> dataList5 = (List<Map<String, String>>) temp.get("result5");
//        String tempList3 =  String.valueOf(temp.get("descStr3"));
//        ExportUtils.XlzgzCreateSheet(wb,index,"抢修类工单报表明细",titleArray3,fieldArray3,tempList3,dataList5,new HashMap<String,String>());
        return wb;
    }
}