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 com.alibaba.fastjson.JSON;
import com.minpay.common.util.StringUtil;
import com.minpay.db.table.mapper.*;
import com.minpay.db.table.model.*;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

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.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;
import net.sf.json.JSONObject;

@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";
	/** test*/
	public final static String test = "test";
	/**
	 * 报表查询
	 * @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", StringUtil.ObjectToString(date).replaceAll("-",""));
		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 Exception
	 */
	@MINAction(value = REPORT_DOWN_LOAD)
	public HttpServletResponse reportDownLoad(
			@MINParam(key = "reportId") String reportId,
			HttpServletResponse response
	) throws Exception {
		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);
		Map<String, Object> dataJSONMap = new HashMap<String,Object>();
		List<List<String>> dataList = new ArrayList<List<String>>();
		String descStr = "";
		try {
			dataJSONMap = JSONObject.fromObject(reportDataInf.getData());//数据格式不对应
			dataList =  (List<List<String>>)dataJSONMap.get("excelList");
			descStr = (String)dataJSONMap.get("descStr");
			descStr = descStr.replaceAll("<p>", "");
			descStr = descStr.replaceAll("</p>", System.getProperty("line.separator"));
			if (dataJSONMap.get("dataMap") != null) {
				Map<String, Object> dataMap = (Map<String, Object>)dataJSONMap.get("dataMap");
				for(Map.Entry<String, Object> entry : dataMap.entrySet()){
					String mapKey = entry.getKey();
					Object mapValue = entry.getValue();
					descStr = descStr.replaceAll("\\$\\{" + mapKey + "\\}", String.valueOf(mapValue));
				}
			}
		} catch (Exception e){
			try {
				String[][] temp = JSON.parseObject(reportDataInf.getData(),String[][].class);
				String[] temp2 = null;
				List<String> tempList = null;
				if (temp != null) {
					for (int i = 0; i < temp.length; i++) {
						tempList = new ArrayList<String>();
						temp2 = temp[i];
						for (int j = 0; j < temp2.length; j++) {
							tempList.add(temp2[j]);
						}
						dataList.add(tempList);
					}
				}
			} catch (Exception ee){
				dataList = new ArrayList<List<String>>();
			}
		}
		if( dataList == null){
			dataList = new ArrayList<List<String>>();
		}
		// excel类型
		if ("00".equals(branchReportType.getType())) {
			//导出Excel
			OutputStream out=null;
			try {
				out = response.getOutputStream();
				HSSFWorkbook wb = ReportExcelUtil.export(response, reportInf.getFileName(), dataList, descStr);
				if(wb != null){
					wb.write(out);
				}
				out.flush();
				out.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			ReportExcelUtil.write2Docx(response, reportInf.getFileName(), descStr, "\\home\\temp", reportInf.getFileName() + ".docx");
		}

		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 range
	 * @param fileName
	 * @param reportInf
	 * @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];//DDYZLFXB_00_03

		User user = session.getUser();

		String reportId = db.getMybatisMapper(SequenceMapper.class).getSequence("REPORT_INF_NO");
		// 生成报表数据表
		String[] date = range.replaceAll(" ", "").split("-");
		//日期格式化
		String bbStartDate =  CommonUtil.dateFormat(date[0],"yyyy年MM月dd日");
		String bbEndDate = CommonUtil.dateFormat(date[1],"yyyy年MM月dd日");
		DwReportData reportDataInf = new DwReportData();
		reportDataInf.setId(reportId);
		reportDataInf.setData("{\"descStr\":\"<p>1.数据来源：智能化供电服务指挥系统；因系统数据滞后，统计时间为："+bbStartDate+"—"+bbEndDate+"，每日发生低电压客户已标红，受考核用户已标黄，累计时长≥15小时的预警用户标褐红色需重点治理。每日新增预警及考核用户编号已标红。</p><p>2.反馈要求：请各单位照实填写，按时反馈，具体原因请填写详细，治理完成的填写治理完成时间。未填写或填写不详细的一律列入考核，请知悉。</p><p>3.低电压是否消除：以供电服务指挥系统数据为依据，连续7天未发生低电压视为低电压消除。</P>\"}");
		reportDataInf.setType(reportInf.split("_")[1]);
		db.insertSelective(DwReportDataMapper.class, reportDataInf);
		// 生成报告表
		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");
		//查询电网低电压分析表数据-DDF_ID
		List<DwDdyFxb> ddyFxbList = db.selectByExample(DwDdyFxbMapper.class, ddyFxbExample, rows);

		List<String> fxbIdList = CommonUtil.getIdFromList(ddyFxbList, "id");
		if(fxbIdList == null || fxbIdList.isEmpty()){
			fxbIdList = new ArrayList<String>();
			fxbIdList.add("XXXXXXXX");
		}
		DwDdyFxbDetailExample ddyFxbDetailExample = new DwDdyFxbDetailExample();
		ddyFxbDetailExample.createCriteria().andFxbIdIn(fxbIdList);
		ddyFxbDetailExample.setOrderByClause("DDFD_FXB_ID");
		List<DwDdyFxbDetail> detailList = db.selectByExample(DwDdyFxbDetailMapper.class, ddyFxbDetailExample);
		System.out.println("当前页数据遍历：共"+ddyFxbList.size()+"条数据");
		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;
	}

	/**
	 * 报表查询--测试
	 * @param page
	 * @param limit
	 */
	@MINAction(value = test)
	public MINActionResult test(
			@MINParam(key = "page", defaultValue = "1") int page,
			@MINParam(key = "limit", defaultValue = "10") int limit,
			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>();
		//用户信息
		User user = session.getUser();
		String roleId = user.getRoleId();

		// 非系统管理员
		if (!Constant.ROLE_ID.equals(roleId)) {
			param.put("branchId", user.getBranchid());
		}
		//查询
		DwFileDetail08Example ddyFxbExample = new DwFileDetail08Example();
		List<DwFileDetail08> list = db.selectByExample(DwFileDetail08Mapper.class,ddyFxbExample,rows);
		// 设置返回值
		res.set(IMINBusinessConstant.F_PAGING_LAY, list);
		res.set(IMINBusinessConstant.F_PAGING_COUNT, rows.getCount());
		return res;
	}
}