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

package com.minpay.reportManage.action;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;

import com.minpay.common.bean.User;
import com.minpay.common.constant.Constant;
import com.minpay.common.format.IFormatService;
import com.minpay.common.service.IDianwangService;
import com.minpay.common.service.IReportService;
import com.minpay.common.util.CommonUtil;
import com.minpay.common.util.DateUtil;
import com.minpay.common.util.FilesUtil;
import com.minpay.db.table.mapper.DwBranchReportTypeMapper;
import com.minpay.db.table.mapper.DwDdyFxbDetailMapper;
import com.minpay.db.table.mapper.DwDdyFxbMapper;
import com.minpay.db.table.mapper.DwReportDataMapper;
import com.minpay.db.table.mapper.DwReportInfMapper;
import com.minpay.db.table.model.DwBranchReportType;
import com.minpay.db.table.model.DwDdyFxb;
import com.minpay.db.table.model.DwDdyFxbDetail;
import com.minpay.db.table.model.DwDdyFxbDetailExample;
import com.minpay.db.table.model.DwDdyFxbExample;
import com.minpay.db.table.model.DwReportData;
import com.minpay.db.table.model.DwReportInf;
import com.minpay.db.table.own.mapper.ReportManageMapper;
import com.minpay.db.table.own.mapper.SequenceMapper;
import com.minpay.dianwang.util.ReportExcelUtil;
import com.startup.minpay.frame.business.IMINAction;
import com.startup.minpay.frame.business.MINHttpServletRequestContext;
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 net.sf.json.JSONArray;

@MINComponent
public class ReportManageAction implements IMINAction {

    private IMINDataBaseService db;
    /** 报表查询 */
    public final static String REPORT_INF_QUERY = "reportInfQuery";
    
    
    /** 生成报表的数据查询 */
    public final static String FILE_INF_REPORT_QUERY = "fileInfReportQuery";
    
    
    /** 根据不同的报表类型查询对应时间范围类的所需数据 */
    public final static String REPORT_NEED_DATA_DETAIL = "reportNeedDataDetail";
    
    
    /** 生成报表 */
    public final static String FILE_INF_REPORT_SUBMIT = "fileInfReportSubmit";
    
    
    /** 报告数据详情 */
    public final static String REPORT_INF_DETAIL = "reportInfDetail";
    
    
    /** 报告下载 */
    public final static String REPORT_DOWN_LOAD = "reportDownLoad";
    
    
    /** 报告删除 */
    public final static String REPORT_INF_DELETE = "reportInfDelete";
    
    
    /** 报告修改 */
    public final static String REPORT_INF_UPDATE = "reportInfUpdate";
    
    
    /** 低电压分析表提交 */
    public final static String DDY_REPORT_INF_SUBMIT = "ddyReportInfSubmit";
    
    /** 低电压分析表详情 */
    public final static String DDY_REPORT_INF_DETAIL = "ddyReportInfDetail";
    
    /** 低电压分析表编辑 */
    public final static String DDY_REPORT_INF_UPDATE = "ddyReportInfUpdate";
    
    /** 低电压分析表下载*/
    public final static String DDY_REPORT_INF_DOWNLOAD = "ddyReportInfDownload";
    
    /** 低电压分析表上传*/
    public final static String DDY_REPORT_INF_UPLOAD = "ddyReportInfUpload";
    
    /**
     * 报表查询 
     * @param page
     * @param limit
     * @param date
     * @param fileName
     * @return
     * @throws MINBusinessException
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     * @throws SecurityException 
     * @throws NoSuchMethodException 
     * @throws InstantiationException 
     */
    @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;
    }
    
    /**
     * 报告明细信息组装
     * @param chooseFileListStr
     * @param session
     * @return
     * @throws MINBusinessException
     */
    @MINAction(value = FILE_INF_REPORT_QUERY)
    public MINActionResult fileInfReportQuery(
    		@MINParam(key = "chooseFileListStr") String chooseFileListStr,
    		@MINParam(key = "page", defaultValue = "1") int page,
            @MINParam(key = "limit", defaultValue = "200") int limit,
    		MINSession session
    		) throws MINBusinessException {
    	MINActionResult res = new MINActionResult();
    	MINRowBounds rows = new MINRowBounds(page, limit);
		rows.setSeparateSql(true);
    	
    	JSONArray chooseFileList = JSONArray.fromObject(chooseFileListStr);
    	
    	List<Map<String, Object>> fileTypeList = new ArrayList<Map<String, Object>>();
    	// 处理选中数据 将同一文件类型的数据放在一起,方便分表查询数据
    	for (Object object : chooseFileList) {
			Map<String, String> chooseFile = (Map<String, String>)object;
			
			boolean flag = false;
			for (Map<String, Object> fileType : fileTypeList) {
				if (chooseFile.get("type").equals(fileType.get("type"))) {
					List<String> fileIdList = (List<String>)fileType.get("fileIdList");
					fileIdList.add(chooseFile.get("id"));
				}
				flag = true;
			}
			
			if (!flag) {
				Map<String, Object> fileType = new HashMap<String, Object>();
				List<String> fileIdList = new ArrayList<String>();
				fileIdList.add(chooseFile.get("id"));
				fileType.put("type", chooseFile.get("type"));
				fileType.put("fileIdList", fileIdList);
				fileTypeList.add(fileType);
			}
		}
    	
    	// 查询对应明细信息
    	for (Map<String, Object> fileType : fileTypeList) {
			String type = fileType.get("type").toString();
			List<String> fileIdList = (List<String>)fileType.get("fileIdList");
			
			Map<String, String> param = new HashMap<String, String>();
			param.put("fileType", type);
			param.put("fileIdListStr", JSONArray.fromObject(fileIdList).toString());
			List fileInfDetailList = Service.lookup(IDianwangService.class).queryFileInfDetail(param, rows);
			fileType.put("fileInfDetailList", fileInfDetailList);
		}
 
    	// 设置返回值
    	res.set(IMINBusinessConstant.F_PAGING_LAY, fileTypeList);
    	res.set(IMINBusinessConstant.F_PAGING_COUNT, rows.getCount());
    	return res;
    }
    
    /**
     * 报表信息组装
     * @param monthChoose	月范围
     * @param range			日期范围
     * @param timeRange		时分秒范围
     * @param reportInf		报表信息 
     * @param monthNum		台区停电   monthNum个月内停电countNum次
     * @param countNum		台区停电   monthNum个月内停电countNum次
     * @param firstFileId	终端投托运报表选择的第一个原始数据文件
     * @param secondFileId	终端投托运报表选择的对比原始数据文件
     * @param FADateRange	FA动作日期区间
     * @param yearChoose	FA动作年份
     * @param DdyDateRange	低电压动日期区间
     * @param ddyFxbId		低电压分析表id
     * @return
     * @throws MINBusinessException
     * @throws ParseException
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     * @throws SecurityException 
     * @throws NoSuchMethodException 
     * @throws IOException 
     * @throws InvalidFormatException 
     */
    @MINAction(value = REPORT_NEED_DATA_DETAIL)
    public MINActionResult reportNeedDataDetail(
    		MINHttpServletRequestContext request,
    		@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);
    	param.put("request", request);
    	Map<String, Object> resMap = Service.lookup(IReportService.class).reportNeedDetail(param);
    	res.set(IMINBusinessConstant.F_PAGING_LAY, resMap);
    	return res;
    }
    
    /**
     * 
     * @param reportData
     * @param reportInf
     * @param fileName
     * @param monthNum			台区停电参数	台区停电【month个月内停电count次及以上台区】的month
     * @param countNum			台区停电参数	台区停电【month个月内停电count次及以上台区】的count
     * @param range				台区停电参数
     * @param timeRange			台区停电参数
     * @param firstFileId		终端投退运参数
     * @param secondFileId		终端投退运参数
     * @param session
     * @return
     * @throws MINBusinessException
     */
    @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);
    	report.setUploadDate(DateUtil.getCurrentDateString());
    	report.setFileName(fileName);
    	report.setCreatUser(user.getId());
    	report.setBranch(user.getBranchid());
    	report.setType(reportTypeId);
    	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;
    }
    
    /**
     * 报告详情
     * @param reportId
     * @param session
     * @return
     * @throws MINBusinessException
     */
    @MINAction(value = REPORT_INF_DETAIL)
    public MINActionResult reportInfDetail(
    		@MINParam(key = "reportId") String reportId,
    		MINSession session
    		) throws MINBusinessException {
    	db = Service.lookup(IMINDataBaseService.class);
    	MINActionResult res = new MINActionResult();
    	
    	DwReportData reportDataInf = db.selectByPrimaryKey(DwReportDataMapper.class, reportId);
    	res.set(IMINBusinessConstant.F_PAGING_LAY, reportDataInf);
    	
    	return res;
    }
    
    /**
     * 报表下载
     * @param reportId
     * @param response
     * @return
     * @throws MINBusinessException
     */
    @MINAction(value = REPORT_DOWN_LOAD)
    public HttpServletResponse reportDownLoad(
    		@MINParam(key = "reportId") String reportId,
    		HttpServletResponse response
    		) throws MINBusinessException {
    	db = Service.lookup(IMINDataBaseService.class);
    	
    	DwReportInf reportInf = db.selectByPrimaryKey(DwReportInfMapper.class, reportId);
    	
    	DwBranchReportType branchReportType = db.selectByPrimaryKey(DwBranchReportTypeMapper.class, reportInf.getType());
    	
    	DwReportData reportDataInf = db.selectByPrimaryKey(DwReportDataMapper.class, reportId);
    	
    	// excel类型
    	if ("00".equals(branchReportType.getType())) {
    		//导出Excel
    		OutputStream out=null;
    		try {
    			out = response.getOutputStream();
    			HSSFWorkbook wb = ReportExcelUtil.export(response, reportInf.getFileName(), reportDataInf.getData());
    			if(wb != null){
    				wb.write(out);
    			}
    			out.flush();
    			out.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    	
    	return response;
    }
    
    /**
     * 报告删除
     * @param reportId
     * @param session
     * @return
     * @throws MINBusinessException
     */
    @MINAction(value = REPORT_INF_DELETE)
    public MINActionResult reportInfDelete(
    		@MINParam(key = "reportId") String reportId,
    		MINSession session
    		) throws MINBusinessException {
    	db = Service.lookup(IMINDataBaseService.class);
    	MINActionResult res = new MINActionResult();
    	
    	DwReportInf reportInf = new DwReportInf();
    	reportInf.setId(reportId);
    	reportInf.setState(Constant.REPORT_STATE_1);
    	db.updateByPrimaryKeySelective(DwReportInfMapper.class, reportInf);
    	
    	return res;
    }
    
    /**
     * 修改
     * @param reportId
     * @param excelList
     * @param reportName
     * @param session
     * @return
     * @throws MINBusinessException
     */
    @MINAction(value = REPORT_INF_UPDATE)
    public MINActionResult reportInfUpdate(
    		@MINParam(key = "reportId") String reportId,
    		@MINParam(key = "excelList") String excelList,
    		@MINParam(key = "reportName") String reportName,
    		MINSession session
    		) throws MINBusinessException {
    	db = Service.lookup(IMINDataBaseService.class);
    	MINActionResult res = new MINActionResult();
    	
    	DwReportInf reportInf = new DwReportInf();
    	reportInf.setId(reportId);
    	reportInf.setFileName(reportName);
    	db.updateByPrimaryKeySelective(DwReportInfMapper.class, reportInf);
    	
    	DwReportData reportData = new DwReportData();
    	reportData.setId(reportId);
    	reportData.setData(excelList);
    	db.updateByPrimaryKeySelective(DwReportDataMapper.class, reportData);
    	
    	return res;
    }
    
    /**
     * 低电压分析表提交
     * @param reportId
     * @param excelList
     * @param reportName
     * @param session
     * @return
     * @throws MINBusinessException
     * @throws ParseException 
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     * @throws SecurityException 
     * @throws NoSuchMethodException 
     */
    @MINAction(value = DDY_REPORT_INF_SUBMIT, transaction = IMINTransactionEnum.CMT)
    public MINActionResult ddyReportInfSubmit(
    		@MINParam(key = "range") String range,
    		@MINParam(key = "fileName") String fileName,
    		@MINParam(key = "reportInf") String reportInf,
    		MINSession session
    		) throws MINBusinessException, ParseException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
    	db = Service.lookup(IMINDataBaseService.class);
    	MINActionResult res = new MINActionResult();
    	
    	String reportType = reportInf.split("_")[0];
    	
    	User user = session.getUser();
    	
    	String reportId = db.getMybatisMapper(SequenceMapper.class).getSequence("REPORT_INF_NO");
    	// 生成报告表
    	DwReportInf report = new DwReportInf();
    	report.setId(reportId);
    	report.setUploadDate(DateUtil.getCurrentDateString());
    	report.setFileName(fileName);
    	report.setCreatUser(user.getId());
    	report.setBranch(user.getBranchid());
    	report.setType(reportType);
    	report.setDayInterval(range.replaceAll(" ", ""));		// 日期时间段
    	db.insertSelective(DwReportInfMapper.class, report);
    	
    	int page = 1;
    	int limit = 1000;
    	// 循环生成分析表数据
    	while (true) {
    		Map<String, Object> resMap = Service.lookup(IReportService.class).reportTypeDDYZLFXB(report.getDayInterval(), reportType, page, limit);
    		// 需处理总条数
    		Integer count = (Integer)resMap.get("count");
    		
    		List<List<String>> resList = (List<List<String>>)resMap.get("resList");
        	for (int i = 2; i < resList.size(); i ++) {
        		List<String> childList = (List<String>)resList.get(i);
        		String id = db.getMybatisMapper(SequenceMapper.class).getSequence("DW_DDY_FXB_NO");
        		DwDdyFxb ddyFxb = new DwDdyFxb();
        		ddyFxb.setId(id);
        		ddyFxb.setXh(childList.get(0));
        		ddyFxb.setXgsmc(childList.get(1));
        		ddyFxb.setYhgldwmc(childList.get(2));
        		ddyFxb.setXlmc(childList.get(3));
        		ddyFxb.setTqid(childList.get(4));
        		ddyFxb.setByqmc(childList.get(5));
        		ddyFxb.setYhbh(childList.get(6));
        		ddyFxb.setDdysj(Integer.parseInt(childList.get(7)));
        		ddyFxb.setLjts(Integer.parseInt(childList.get(8)));
        		ddyFxb.setDdyzymc(childList.get(9));
        		ddyFxb.setJtyy(childList.get(10));
        		ddyFxb.setZlcs(childList.get(11));
        		ddyFxb.setSfzl(childList.get(12));
        		ddyFxb.setZlwcsj(childList.get(13));
        		ddyFxb.setSfxc(childList.get(14));
        		ddyFxb.setZrxz(childList.get(15));
        		ddyFxb.setQnljts(Integer.parseInt(childList.get(16)));
        		ddyFxb.setReportId(reportId);
        		db.insertSelective(DwDdyFxbMapper.class, ddyFxb);
        		
        		String beginDate = report.getDayInterval().split("-")[0];
        		String endDate = report.getDayInterval().split("-")[1];
        		int index = 17;
        		while (CommonUtil.compare(endDate, beginDate) != -1) {
        			if (CommonUtil.isEmpty(childList.get(index))) {
        				// 天数减1
        				endDate = DateUtil.dateAddDay(endDate, -1);
        				index++;
        				continue;
        			}
        			DwDdyFxbDetail ddyFxbDetail = new DwDdyFxbDetail();
        			String detailId = db.getMybatisMapper(SequenceMapper.class).getSequence("DW_DDY_FXB_DETAIL_NO");
        			ddyFxbDetail.setId(detailId);
        			ddyFxbDetail.setDate(endDate);
        			ddyFxbDetail.setFxbId(id);
        			ddyFxbDetail.setSc(childList.get(index));
        			index++;
        			db.insertSelective(DwDdyFxbDetailMapper.class, ddyFxbDetail);
        			// 天数减1
        			endDate = DateUtil.dateAddDay(endDate, -1);
        		}
        	}
        	if (count <= page * limit) {
        		break;
        	}
        	page ++;
    	}
    	
    	return res;
    }
    
    /**
     * 低电压报告详情
     * @param reportId
     * @param session
     * @return
     * @throws MINBusinessException
     * @throws ParseException 
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     * @throws SecurityException 
     * @throws NoSuchMethodException 
     */
    @MINAction(value = DDY_REPORT_INF_DETAIL)
    public MINActionResult ddyReportInfDetail(
    		@MINParam(key = "reportId") String reportId,
    		@MINParam(key = "page") int page,
    		@MINParam(key = "limit") int limit,
    		MINSession session
    		) throws MINBusinessException, ParseException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
    	db = Service.lookup(IMINDataBaseService.class);
    	MINActionResult res = new MINActionResult();
		
		List<Map<String, String>> resList = new ArrayList<Map<String, String>>();
		
		MINRowBounds rows = new MINRowBounds(page, limit);
		rows.setSeparateSql(true);
		
		DwDdyFxbExample ddyFxbExample = new DwDdyFxbExample();
		ddyFxbExample.createCriteria().andReportIdEqualTo(reportId);
		ddyFxbExample.setOrderByClause("DDF_ID");
		List<DwDdyFxb> ddyFxbList = db.selectByExample(DwDdyFxbMapper.class, ddyFxbExample, rows);
		
		List<String> fxbIdList = CommonUtil.getIdFromList(ddyFxbList, "id");
		
		DwDdyFxbDetailExample ddyFxbDetailExample = new DwDdyFxbDetailExample();
		ddyFxbDetailExample.createCriteria().andFxbIdIn(fxbIdList);
		ddyFxbDetailExample.setOrderByClause("DDFD_FXB_ID");
		List<DwDdyFxbDetail> detailList = db.selectByExample(DwDdyFxbDetailMapper.class, ddyFxbDetailExample);
		
		for (DwDdyFxb ddyFxb : ddyFxbList) {
			Map<String, String> child = new HashMap<String, String>();
			child.put("id", ddyFxb.getId());
			child.put("xh", ddyFxb.getXh());
			child.put("xgsmc", ddyFxb.getXgsmc());
			child.put("yhgldwmc", ddyFxb.getYhgldwmc());
			child.put("xlmc", ddyFxb.getXlmc());
			child.put("tqid", ddyFxb.getTqid());
			child.put("byqmc", ddyFxb.getByqmc());
			child.put("yhbh", ddyFxb.getYhbh());
			child.put("ddysj", String.valueOf(ddyFxb.getDdysj()));
			child.put("ljts", String.valueOf(ddyFxb.getLjts()));
			child.put("ddyzymc", ddyFxb.getDdyzymc());
			child.put("jtyy", ddyFxb.getJtyy());
			child.put("zlcs", ddyFxb.getZlcs());
			child.put("sfzl", ddyFxb.getSfzl());
			child.put("zlwcsj", ddyFxb.getZlwcsj());
			child.put("sfxc", ddyFxb.getSfxc());
			
			for (int i = 0; i < detailList.size(); i++) {
				// 分析表id一致
				if (ddyFxb.getId().equals(detailList.get(i).getFxbId())) {
					String sjrq = detailList.get(i).getDate();
					child.put(sjrq, detailList.get(i).getSc());
					detailList.remove(i);
					i --;
				} else {
					break;
				}
			}
			resList.add(child);
		}
		res.set(IMINBusinessConstant.F_PAGING_LAY, resList);
		res.set(IMINBusinessConstant.F_PAGING_COUNT, rows.getCount());
    	return res;
    }
    
    /**
     * 低电压编辑
     * @param id
     * @param ddyzymc
     * @param jtyy
     * @param zlcs
     * @param sfzl
     * @param zlwcsj
     * @param sfxc
     * @param session
     * @return
     * @throws MINBusinessException
     * @throws ParseException
     */
    @MINAction(value = DDY_REPORT_INF_UPDATE, transaction = IMINTransactionEnum.CMT)
    public MINActionResult ddyReportInfUpdate(
    		@MINParam(key = "id") String id,
    		@MINParam(key = "ddyzymc") String ddyzymc,
    		@MINParam(key = "jtyy") String jtyy,
    		@MINParam(key = "zlcs") String zlcs,
    		@MINParam(key = "sfzl") String sfzl,
    		@MINParam(key = "zlwcsj") String zlwcsj,
    		@MINParam(key = "sfxc") String sfxc,
    		MINSession session
    		) throws MINBusinessException, ParseException {
    	db = Service.lookup(IMINDataBaseService.class);
    	MINActionResult res = new MINActionResult();
    	DwDdyFxb ddyFxb = new DwDdyFxb();
    	ddyFxb.setId(id);
    	if (!CommonUtil.isEmpty(ddyzymc)) {
    		ddyFxb.setDdyzymc(ddyzymc);
    	}
    	if (!CommonUtil.isEmpty(jtyy)) {
    		ddyFxb.setJtyy(jtyy);
    	}
    	if (!CommonUtil.isEmpty(zlcs)) {
    		ddyFxb.setZlcs(zlcs);
    	}
    	if (!CommonUtil.isEmpty(sfzl)) {
    		ddyFxb.setSfzl(sfzl);
    	}
    	if (!CommonUtil.isEmpty(zlwcsj)) {
    		ddyFxb.setZlwcsj(zlwcsj);
    	}
    	if (!CommonUtil.isEmpty(sfxc)) {
    		ddyFxb.setSfxc(sfxc);
    	}
    	db.updateByPrimaryKeySelective(DwDdyFxbMapper.class, ddyFxb);
    	
    	return res;
    }
    
    @MINAction(value = DDY_REPORT_INF_DOWNLOAD)
    public HttpServletResponse ddyReportInfDownload(
    		@MINParam(key = "reportId") String reportId,
    		HttpServletResponse response
    		) throws Exception {
    	
    	db = Service.lookup(IMINDataBaseService.class);
    	
    	DwReportInf reportInf = db.selectByPrimaryKey(DwReportInfMapper.class, reportId);
    	
		
    	int page = 1;
    	int limit = 1000;
    	
    	int count = 0;
    	List<Map<String, String>> resList = new ArrayList<Map<String, String>>();
    	while (true) {
    		
    		MINRowBounds rows = new MINRowBounds(page, limit);
    		rows.setSeparateSql(true);
    		
    		DwDdyFxbExample ddyFxbExample = new DwDdyFxbExample();
    		ddyFxbExample.createCriteria().andReportIdEqualTo(reportId);
    		ddyFxbExample.setOrderByClause("DDF_ID");
    		List<DwDdyFxb> ddyFxbList = db.selectByExample(DwDdyFxbMapper.class, ddyFxbExample, rows);
    		
    		count = rows.getCount();
    		
    		List<String> fxbIdList = CommonUtil.getIdFromList(ddyFxbList, "id");
    		
    		DwDdyFxbDetailExample ddyFxbDetailExample = new DwDdyFxbDetailExample();
    		ddyFxbDetailExample.createCriteria().andFxbIdIn(fxbIdList);
    		ddyFxbDetailExample.setOrderByClause("DDFD_FXB_ID");
    		List<DwDdyFxbDetail> detailList = db.selectByExample(DwDdyFxbDetailMapper.class, ddyFxbDetailExample);
    		
    		for (DwDdyFxb ddyFxb : ddyFxbList) {
    			Map<String, String> child = new HashMap<String, String>();
    			child.put("id", ddyFxb.getId());
    			child.put("xh", ddyFxb.getXh());
    			child.put("xgsmc", ddyFxb.getXgsmc());
    			child.put("yhgldwmc", ddyFxb.getYhgldwmc());
    			child.put("xlmc", ddyFxb.getXlmc());
    			child.put("tqid", ddyFxb.getTqid());
    			child.put("byqmc", ddyFxb.getByqmc());
    			child.put("yhbh", ddyFxb.getYhbh());
    			child.put("ddysj", String.valueOf(ddyFxb.getDdysj()));
    			child.put("ljts", String.valueOf(ddyFxb.getLjts()));
    			child.put("ddyzymc", ddyFxb.getDdyzymc());
    			child.put("jtyy", ddyFxb.getJtyy());
    			child.put("zlcs", ddyFxb.getZlcs());
    			child.put("sfzl", ddyFxb.getSfzl());
    			child.put("zlwcsj", ddyFxb.getZlwcsj());
    			child.put("sfxc", ddyFxb.getSfxc());
    			
    			for (int i = 0; i < detailList.size(); i++) {
    				// 分析表id一致
    				if (ddyFxb.getId().equals(detailList.get(i).getFxbId())) {
    					String sjrq = detailList.get(i).getDate();
    					child.put(sjrq, detailList.get(i).getSc());
    					detailList.remove(i);
    					i --;
    				} else {
    					break;
    				}
    			}
    			resList.add(child);
    		}
    		if (limit*page >= count) {
    			break;
    		}
    		page ++;
    	}
    	
    	//导出Excel
		OutputStream out=null;
		try {
			String beginTime = reportInf.getDayInterval().split("-")[0];
	    	String endTime = reportInf.getDayInterval().split("-")[1];
	    	int dayNum = DateUtil.diffDate(beginTime, endTime);
	    	String[] excelHeader = new String[dayNum + 16];
	    	excelHeader[0] = "系统编号#id";
	    	excelHeader[1] = "县公司名称#xgsmc";
	    	excelHeader[2] = "用户管理单位名称#yhgldwmc";
	    	excelHeader[3] = "线路名称#xlmc";
	    	excelHeader[4] = "台区ID#tqid";
	    	excelHeader[5] = "变压器名称#byqmc";
	    	excelHeader[6] = "用户编号#yhbh";
	    	excelHeader[7] = "低电压时长#ddysj";
	    	excelHeader[8] = "累计天数#ljts";
	    	excelHeader[9] = "低电压主要原因#ddyzymc";
	    	excelHeader[10] = "具体原因#jtyy";
	    	excelHeader[11] = "治理措施#zlcs";
	    	excelHeader[12] = "是否治理#sfzl";
	    	excelHeader[13] = "治理完成时间#zlwcsj";
	    	excelHeader[14] = "是否消除#sfxc";
	    	
	    	int index = 15;
	    	while (CommonUtil.compare(endTime, beginTime) != -1) {
	    		excelHeader[index] = endTime + "#" + endTime;
	    		index ++;
	    		endTime = DateUtil.dateAddDay(endTime, -1);
	    	}
			out = response.getOutputStream();
			HSSFWorkbook wb = export(response, reportInf.getFileName(), excelHeader, resList);
			if(wb != null){
				wb.write(out);
			}
			out.flush();
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
    	
    	return response;
    }
    
    @MINAction(value = DDY_REPORT_INF_UPLOAD)
    public MINActionResult ddyReportInfUpload(
    		MINHttpServletRequestContext request,
    		@MINParam(key = "id") String id,
    		MINSession session
    		) throws MINBusinessException, InvalidFormatException, IOException {
    	MINActionResult res = new MINActionResult();
    	db = Service.lookup(IMINDataBaseService.class);
    	// 获取文件
		FileItem fileItem = request.getFile("file");
		String fileName = fileItem.getName();
		if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")) {
			throw new MINBusinessException("文件格式错误!");
		}
		// 获取表格信息
		String[] titleKey = new String[]{"NO","ID","XGSMC","YHGLDWMC", "XLMC","TQID","BYQMC","YHBH","DDYSJ","LJTS","DDYZYMC","JTYY","ZLCS","SFZL","ZLWCSJ","SFXC"};
		List<Map<String,String>> list = FilesUtil.readExecleasy(fileItem, titleKey, 1);
		
		for (int i = 0; i < list.size(); i ++) {
			Map<String, String> map = list.get(i);
			DwDdyFxb ddyFxb = new DwDdyFxb();
			ddyFxb.setId(map.get("ID"));
			ddyFxb.setDdyzymc(map.get("DDYZYMC"));
			ddyFxb.setJtyy(map.get("JTYY"));
			ddyFxb.setZlcs(map.get("ZLCS"));
			ddyFxb.setSfzl(map.get("SFZL"));
			ddyFxb.setZlwcsj(map.get("ZLWCSJ"));
			ddyFxb.setSfxc(map.get("SFXC"));
			db.updateByPrimaryKeySelective(DwDdyFxbMapper.class, ddyFxb);
		}
		
		return res;
    }
    
    
    public static <T> HSSFWorkbook export(HttpServletResponse response, String fileName, String[] excelHeader,
			   Collection<T> dataList) throws Exception {
			  // 设置请求
			  response.setContentType("application/application/vnd.ms-excel");
			  response.setHeader("Content-disposition",
			    "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
			  // 创建一个Workbook，对应一个Excel文件
			  HSSFWorkbook wb = new HSSFWorkbook();
			  // 设置标题样式
			  HSSFCellStyle titleStyle = wb.createCellStyle();
			  // 设置字体样式
			  Font titleFont = wb.createFont();
			  titleFont.setFontHeightInPoints((short) 12); // 字体高度
			  titleFont.setFontName("黑体"); // 字体样式
			  titleStyle.setFont(titleFont);
			  titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
			  // 在Workbook中添加一个sheet,对应Excel文件中的sheet
			  HSSFSheet sheet = wb.createSheet(fileName);
			  // 标题数组
			  String[] titleArray = new String[excelHeader.length];
			  // 字段名数组
			  String[] fieldArray = new String[excelHeader.length];
			  for (int i = 0; i < excelHeader.length; i++) {
				  String[] tempArray = excelHeader[i].split("#");// 临时数组 分割#
				  titleArray[i] = tempArray[0];
				  fieldArray[i] = tempArray[1];
			  }
			  // 在sheet中添加标题行
			  HSSFRow row = sheet.createRow((int) 0);// 行数从0开始
			  HSSFCell sequenceCell = row.createCell(0);// cell列 从0开始 第一列添加序号
			  sequenceCell.setCellValue("序号");
			  sequenceCell.setCellStyle(titleStyle);
			  sheet.autoSizeColumn(0);// 自动设置宽度
			  // 为标题行赋值
			  for (int i = 0; i < titleArray.length; i++) {
				  HSSFCell titleCell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
				  titleCell.setCellValue(titleArray[i]);
				  titleCell.setCellStyle(titleStyle);
				  sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
				  sheet.setColumnWidth(i+1,titleArray[i].getBytes().length*256+500);
			  }
			  // 数据样式 因为标题和数据样式不同 需要分开设置 不然会覆盖
			  HSSFCellStyle dataStyle = wb.createCellStyle();
			  // 设置数据字体
			  Font dataFont = wb.createFont();
			  dataFont.setFontHeightInPoints((short) 10); // 字体高度
			  dataFont.setFontName("宋体"); // 字体
			  dataStyle.setFont(dataFont);
			  dataStyle.setAlignment(CellStyle.ALIGN_CENTER);
			  
			  // 自定义颜色
			  HSSFPalette palette = wb.getCustomPalette();
			  palette.setColorAtIndex((short)9, (byte) (0xff & 240), (byte) (0xff & 165), (byte) (0xff & 172));
			  palette.setColorAtIndex((short)10, (byte) (0xff & 255), (byte) (0xff & 192), (byte) (0xff & 0));
			  
			  HSSFCellStyle dataStyle2 = wb.createCellStyle();
			  dataStyle2.setFont(dataFont);
			  dataStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND);
			  dataStyle2.setFillForegroundColor((short)9);
			  dataStyle2.setAlignment(CellStyle.ALIGN_CENTER);
			  
			  HSSFCellStyle dataStyle3 = wb.createCellStyle();
			  dataStyle3.setFont(dataFont);
			  dataStyle3.setFillPattern(CellStyle.SOLID_FOREGROUND);
			  dataStyle3.setFillForegroundColor((short)10);
			  dataStyle3.setAlignment(CellStyle.ALIGN_CENTER);
			  
			  // 遍历集合数据，产生数据行
			  Iterator<T> it = dataList.iterator();
			  int index = 0;
			  while (it.hasNext()) {
				  index++;
				  row = sheet.createRow(index);
				  // 为序号赋值
				  HSSFCell sequenceCellValue = row.createCell(0);// 序号值永远是第0列
				  sequenceCellValue.setCellValue(index);
				  sequenceCellValue.setCellStyle(dataStyle);
//					  sheet.autoSizeColumn(0);
				  T t = (T) it.next();
				  Map<String, Object> map = (Map<String, Object> )t;
				  HSSFCell dataCell = null;
				  for (int i = 0; i < fieldArray.length; i++) {
					  dataCell = row.createCell(i + 1);
					  String fieldName = fieldArray[i];
					  String value = CommonUtil.objToString(map.get(fieldName));
					  // 每日时长列
					  if (i >= 15) {
						  if (CommonUtil.isEmpty(value)) {
							  dataCell.setCellValue("");
							  dataCell.setCellStyle(dataStyle);
						  } else {
							  dataCell.setCellValue(value);// 为当前列赋值
							  dataCell.setCellStyle(dataStyle2);
						  }
					  //用户编号列
					  } else if (i == 6) {
						  // 低电压时长
						  String sj = String.valueOf(map.get(fieldArray[i + 1]));
						  if (CommonUtil.compare(sj, "48") != -1) {
							  dataCell.setCellValue(value);// 为当前列赋值
							  dataCell.setCellStyle(dataStyle3);
						  } else {
							  dataCell.setCellValue(value);// 为当前列赋值
							  dataCell.setCellStyle(dataStyle);
						  }
					  } else {
						  dataCell.setCellStyle(dataStyle);
						  if (CommonUtil.isEmpty(value)) {
							  dataCell.setCellValue("");					// 为当前列赋值
						  } else {
							  dataCell.setCellValue(CommonUtil.objToString(value));// 为当前列赋值
						  }
					  }
				  }
			  }
			  sheet.setColumnWidth(fieldArray.length, 20 * 256);
			  return wb;
		 }
}