package com.minpay.reportManage.action; import java.io.BufferedOutputStream; import java.io.IOException; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.InvocationTargetException; import java.net.URLEncoder; import java.text.ParseException; import java.text.SimpleDateFormat; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.*; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import com.minpay.common.util.*; import com.minpay.db.table.mapper.*; import com.minpay.db.table.model.*; import com.minpay.db.table.own.mapper.IReportBatchMapper; import com.startup.minpay.frame.data.AbstractMINBean; import org.apache.commons.fileupload.FileItem; import org.apache.ibatis.transaction.Transaction; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import com.min.util.DateUtil; 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.IPropertiesService; import com.minpay.db.table.own.mapper.DataWareManageMapper; import com.minpay.db.table.own.mapper.SequenceMapper; 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 org.apache.poi.ss.formula.functions.T; import org.springframework.transaction.annotation.Transactional; @MINComponent public class DataWareManageAction implements IMINAction { private IMINDataBaseService db; /** 原始数据查询 */ public final static String FILE_INF_QUERY = "fileInfQuery"; /** 原始文件上传 */ public final static String FILE_INF_UPLOAD = "fileInfUpload"; /** 原始文件下载 */ public final static String FILE_INF_DOWNLOAD = "fileInfDownload"; /** 原始文件删除 */ public final static String FILE_INF_DELETE = "fileInfDelete"; /** 原始文件详情 */ public final static String FILE_INF_DETAIL = "fileInfDetail"; /** 原始数据下载 */ public final static String FILE_DATA_DOWNLOAD = "fileDataDownload"; /** * 原始数据查询 * @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 = FILE_INF_QUERY) public MINActionResult fileInfQuery( @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 = "notInData") String notInData, @MINParam(key = "reportType") String reportType, @MINParam(key = "type") String type, MINSession session ) throws MINBusinessException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException { MINActionResult res = new MINActionResult(); db = Service.lookup(IMINDataBaseService.class); //分页 MINRowBounds rows = new MINRowBounds(page, limit); rows.setSeparateSql(true); Map param = new HashMap(); param.put("date", StringUtil.ObjectToString(date).replaceAll("-","")); param.put("fileName", fileName); param.put("type", type); if (!CommonUtil.isEmpty(notInData)) { JSONArray notInArray = JSONArray.fromObject(notInData); if (notInArray.size() != 0) { param.put("notInArray", notInArray); } } // 查询生成报表所需的文件类型 if (!CommonUtil.isEmpty(reportType)) { DwReporttypeFiletypeRelExample reporttypeFiletypeRelExample = new DwReporttypeFiletypeRelExample(); reporttypeFiletypeRelExample.createCriteria().andReporttypeIdEqualTo(reportType); List selectByExample = db.selectByExample(DwReporttypeFiletypeRelMapper.class, reporttypeFiletypeRelExample); if (selectByExample.size() > 0) { param.put("fileTypeArray", selectByExample); } } User user = session.getUser(); String roleId = user.getRoleId(); // 非系统管理员 if (!Constant.ROLE_ID.equals(roleId)) { param.put("branchId", user.getBranchid()); } List> list = db.getMybatisMapper(DataWareManageMapper.class) .selectFileInf(param, rows); if (list.size() == 0) { return res; } List creatUserIdList = CommonUtil.getIdFromList(list, "creatUser"); ImUserExample userExam = new ImUserExample(); userExam.createCriteria().andIdIn(creatUserIdList); List userList = db.selectByExample(ImUserMapper.class, userExam); String[] str1 = {"creatUser"}; String[] str2 = {"id"}; String[] str3 = {"name"}; String[] str4 = {"userName"}; list = ListUtil.mergeList(list, userList, str1, str2, str3, str4); 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 request * @param fileType * @param chongfuFlag 重复上传标志 true:重复数据确认上传 * @param session * @return * @throws MINBusinessException * @throws InvalidFormatException * @throws IOException */ @MINAction(value = FILE_INF_UPLOAD, transaction = IMINTransactionEnum.CMT) public MINActionResult fileInfUpload( MINHttpServletRequestContext request, @MINParam(key = "fileType") String fileType, @MINParam(key = "chongfuFlag") boolean chongfuFlag, MINSession session ) throws MINBusinessException, InvalidFormatException, IOException, ParseException { 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 fileId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_INF_NO"); List> list = null; // 台区停电 if (Constant.FILE_DETAIL_TYPE_00.equals(fileType)) { // 获取表格信息 String[] titleKey = new String[]{"number","addressNum","addressName","date", "area","comonpanyName","terminalName","terminalNum","terminalAddress","startTime","endTime"}; list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if (list.size() == 0) { throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } // 详情数据存入数据库 for (Map excelMap : list) { // 日期 String date = excelMap.get("date").replaceAll("-", "").replaceAll(":", "").replaceAll(" ", "").substring(0,8); String startTime = excelMap.get("startTime").replaceAll("-", "").replaceAll(":", "").replaceAll(" ", ""); String endTime = excelMap.get("endTime").replaceAll("-", "").replaceAll(":", "").replaceAll(" ", ""); // 页面确认有重复数据,且点击确认上传,不再判断数据库是否有数据 if (!chongfuFlag) { // 验证是否有重复数据 DwFileDetail00Example fileDetail00Example = new DwFileDetail00Example(); fileDetail00Example.createCriteria().andAddressNumEqualTo(excelMap.get("addressNum")) .andAddressNameEqualTo(excelMap.get("addressName")) .andDateEqualTo(date) .andAreaEqualTo(excelMap.get("area")) .andComonpanyNameEqualTo(excelMap.get("comonpanyName")) .andTerminalNameEqualTo(excelMap.get("terminalName")) .andTerminalNumEqualTo(excelMap.get("terminalNum")) .andTerminalAddressEqualTo(excelMap.get("terminalAddress")) .andStartTimeEqualTo(startTime) .andEndTimeEqualTo(endTime); List selectByExample = db.selectByExample(DwFileDetail00Mapper.class, fileDetail00Example); if (selectByExample.size() != 0) { throw new MINBusinessException("chongfu|" + selectByExample.get(0).getAddressNum() + "|" + selectByExample.get(0).getAddressName() + "|" + selectByExample.get(0).getDate()); } } String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_00_NO"); DwFileDetail00 fileDetail00 = new DwFileDetail00(); fileDetail00.setId(fileDetailId); fileDetail00.setNumber(excelMap.get("number")); fileDetail00.setAddressNum(excelMap.get("addressNum")); fileDetail00.setAddressName(excelMap.get("addressName")); fileDetail00.setDate(date); fileDetail00.setArea(excelMap.get("area")); fileDetail00.setComonpanyName(excelMap.get("comonpanyName")); fileDetail00.setTerminalName(excelMap.get("terminalName")); fileDetail00.setTerminalNum(excelMap.get("terminalNum")); fileDetail00.setTerminalAddress(excelMap.get("terminalAddress")); fileDetail00.setStartTime(startTime); fileDetail00.setEndTime(endTime); fileDetail00.setFileId(fileId); db.insertSelective(DwFileDetail00Mapper.class, fileDetail00); } // 终端投托运 } else if (Constant.FILE_DETAIL_TYPE_01.equals(fileType)) { String[] titleKey = new String[]{"id","tname","cmode","value", "facname","type","fdid","yxstatus","tytime"}; list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if (list.size() == 0) { throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } // 详情数据存入数据库 for (Map excelMap : list) { DwFileDetail01Example dwFileDetail01 = new DwFileDetail01Example(); dwFileDetail01.createCriteria().andExcelIdEqualTo(excelMap.get("id")) .andExcelTnameEqualTo(excelMap.get("tname")) .andExcelCmodeEqualTo(excelMap.get("cmode")) .andExcelValueEqualTo(excelMap.get("value")) .andExcelFacnameEqualTo(excelMap.get("facname")) .andExcelTypeEqualTo(excelMap.get("type")) .andExcelFdidEqualTo(excelMap.get("fdid")) .andExcelYxstatusEqualTo(excelMap.get("yxstatus")); String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_01_NO"); DwFileDetail01 fileDetail01 = new DwFileDetail01(); fileDetail01.setId(fileDetailId); fileDetail01.setExcelId(excelMap.get("id")); fileDetail01.setExcelTname(excelMap.get("tname")); fileDetail01.setExcelCmode(excelMap.get("cmode")); fileDetail01.setExcelValue(excelMap.get("value")); fileDetail01.setExcelFacname(excelMap.get("facname")); fileDetail01.setExcelType(excelMap.get("type")); fileDetail01.setExcelFdid(excelMap.get("fdid")); fileDetail01.setExcelYxstatus(excelMap.get("yxstatus")); String tytime = excelMap.get("tytime"); tytime = tytime.replaceAll("-", "").replaceAll(" ", "").replaceAll(":", ""); if (!CommonUtil.isEmpty(tytime) && tytime.length() != 14) { throw new MINBusinessException(excelMap.get("tytime") + "--时间格式错误"); } fileDetail01.setExcelTytime(tytime); fileDetail01.setFileId(fileId); db.insertSelective(DwFileDetail01Mapper.class, fileDetail01); } // FA动作 } else if (Constant.FILE_DETAIL_TYPE_02.equals(fileType)) { String[] titleKey = new String[]{"no","beginTime","endTime","gdlx", "cxbdz","xlmc","xllx","qdfa","gzqjpd","gzqhdhf","gzgl"}; list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if (list.size() == 0) { throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } // 详情数据存入数据库 for (Map excelMap : list) { String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_02_NO"); DwFileDetail02 fileDetail02 = new DwFileDetail02(); fileDetail02.setId(fileDetailId); fileDetail02.setNo(excelMap.get("no")); fileDetail02.setBeginTime(excelMap.get("beginTime")); fileDetail02.setEndTime(excelMap.get("endTime")); fileDetail02.setGdlx(excelMap.get("gdlx")); fileDetail02.setCxbdz(excelMap.get("cxbdz")); fileDetail02.setXlmc(excelMap.get("xlmc")); fileDetail02.setXllx(excelMap.get("xllx")); fileDetail02.setQdfa(excelMap.get("qdfa")); fileDetail02.setGzqjpd(excelMap.get("gzqjpd")); fileDetail02.setGzqhdhf(excelMap.get("gzqhdhf")); fileDetail02.setGzgl(excelMap.get("gzgl")); fileDetail02.setFileId(fileId); db.insertSelective(DwFileDetail02Mapper.class, fileDetail02); } //低电压 } else if(Constant.FILE_DETAIL_TYPE_03.equals(fileType)) { String[] titleKey = new String[]{"no","sgsmc","xgsmc","bdzmc","xlmc","tqid","byqmc","yhgldw1","yhid","yhmc","yhgldw2","dymax","dymin","dyhgl","csxl","cxxl","dysc","gdlx","ljts","sjrq","gddw"}; list = FilesUtil.readExecleasy(fileItem, titleKey, 3); if (list.size() == 0) { throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } // 详情数据存入数据库 for (Map excelMap : list) { String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_03_NO"); String sjrq = excelMap.get("sjrq"); try { if(sjrq.length()>10){ sjrq = sjrq.substring(0, 8); }else{ SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); Date date = sdf.parse(sjrq); SimpleDateFormat todf = new SimpleDateFormat("yyyyMMdd");// 设置日期格式 sjrq = todf.format(date); } } catch (Exception ex) { } DwFileDetail03 fileDetail03 = new DwFileDetail03(); fileDetail03.setId(fileDetailId); fileDetail03.setNo(excelMap.get("no")); fileDetail03.setSgsmc(excelMap.get("sgsmc")); fileDetail03.setXgsmc(excelMap.get("xgsmc")); fileDetail03.setBdzmc(excelMap.get("bdzmc")); fileDetail03.setXlmc(excelMap.get("xlmc")); fileDetail03.setTqid(excelMap.get("tqid")); fileDetail03.setByqmc(excelMap.get("byqmc")); fileDetail03.setYhgldw1(excelMap.get("yhgldw1")); fileDetail03.setYhid(excelMap.get("yhid")); fileDetail03.setYhmc(excelMap.get("yhmc")); fileDetail03.setYhgldw2(excelMap.get("yhgldw2")); fileDetail03.setDymax(excelMap.get("dymax")); fileDetail03.setDymin(excelMap.get("dymin")); fileDetail03.setDyhgl(excelMap.get("dyhgl")); fileDetail03.setCsxl(excelMap.get("csxl")); fileDetail03.setCxxl(excelMap.get("cxxl")); fileDetail03.setDysc(excelMap.get("dysc")); fileDetail03.setGdlx(excelMap.get("gdlx")); fileDetail03.setLjts(excelMap.get("ljts")); fileDetail03.setSjrq(sjrq); fileDetail03.setGddw(excelMap.get("gddw")); fileDetail03.setFileId(fileId); db.insertSelective(DwFileDetail03Mapper.class, fileDetail03); } //过电压 } else if(Constant.FILE_DETAIL_TYPE_04.equals(fileType)) { String[] titleKey = new String[]{"no","sgsmc","xgsmc","bdzmc","xlmc","tqid","byqmc","yhgldw1","yhid","yhmc","yhgldw2","dymax","dymin","dyhgl","csxl","cxxl","gdysc","gdlx","ljts","gddw","sjrq"}; list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if (list.size() == 0) { throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } // 详情数据存入数据库 for (Map excelMap : list) { String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_04_NO"); String sjrq = excelMap.get("sjrq"); try { if(sjrq.length()>10){ sjrq = sjrq.substring(0, 8); }else{ SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); Date date = sdf.parse(sjrq); SimpleDateFormat todf = new SimpleDateFormat("yyyyMMdd");// 设置日期格式 sjrq = todf.format(date); } } catch (Exception ex) { } // DateFormat formater = new SimpleDateFormat("yyyyMMdd"); // String sjrq = formater.format(excelMap.get("sjrq")); DwFileDetail04 fileDetail04 = new DwFileDetail04(); fileDetail04.setId(fileDetailId); fileDetail04.setNo(excelMap.get("no")); fileDetail04.setSgsmc(excelMap.get("sgsmc")); fileDetail04.setXgsmc(excelMap.get("xgsmc")); fileDetail04.setBdzmc(excelMap.get("bdzmc")); fileDetail04.setXlmc(excelMap.get("xlmc")); fileDetail04.setTqid(excelMap.get("tqid")); fileDetail04.setByqmc(excelMap.get("byqmc")); fileDetail04.setYhgldw1(excelMap.get("yhgldw1")); fileDetail04.setYhid(excelMap.get("yhid")); fileDetail04.setYhmc(excelMap.get("yhmc")); fileDetail04.setYhgldw2(excelMap.get("yhgldw2")); fileDetail04.setDymax(excelMap.get("dymax")); fileDetail04.setDymin(excelMap.get("dymin")); fileDetail04.setDyhgl(excelMap.get("dyhgl")); fileDetail04.setCsxl(excelMap.get("csxl")); fileDetail04.setCxxl(excelMap.get("cxxl")); fileDetail04.setGdysc(excelMap.get("gdysc")); fileDetail04.setGdlx(excelMap.get("gdlx")); fileDetail04.setLjts(excelMap.get("ljts")); fileDetail04.setSjrq(sjrq); fileDetail04.setGddw(excelMap.get("gddw")); fileDetail04.setFileId(fileId); db.insertSelective(DwFileDetail04Mapper.class, fileDetail04); } // 95598工单原始数据上传 } else if(Constant.FILE_DETAIL_TYPE_05.equals(fileType)) { String[] titleKey = new String[]{"NO","GDBH","GWGDBH","ZT","YWLX","DQBZ","SLRY","SLSJ","SLLR","YHBH","YHMC","LXDZ","LXDH","HFNR","YJFL","EJFL", "YWZL","SJDW","GDDW","CBD","CLJG","CLDW","CLBM","HDS","BZ","GLGD","WTYYFL","GDFJ","ZRBM","GDSJ","GQSJ","GQYY","FWQD","SJHTZRYY"}; list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if (list.size() == 0) { throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } // 详情数据存入数据库 for (Map excelMap : list) { String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_05_NO"); DwFileDetail05 fileDetail05 = new DwFileDetail05(); fileDetail05.setId(fileDetailId); fileDetail05.setNo(excelMap.get("NO")); fileDetail05.setGdbh(excelMap.get("GDBH")); fileDetail05.setGwgdbh(excelMap.get("GWGDBH")); fileDetail05.setZt(excelMap.get("ZT")); fileDetail05.setYwlx(excelMap.get("YWLX")); fileDetail05.setDqbz(excelMap.get("DQBZ")); fileDetail05.setSlry(excelMap.get("SLRY")); fileDetail05.setSlsj(excelMap.get("SLSJ")); fileDetail05.setSllr(excelMap.get("SLLR")); fileDetail05.setYhbh(excelMap.get("YHBH")); fileDetail05.setYhmc(excelMap.get("YHMC")); fileDetail05.setLxdz(excelMap.get("LXDZ")); fileDetail05.setLxdh(excelMap.get("LXDH")); fileDetail05.setHfnr(excelMap.get("HFNR")); fileDetail05.setYjfl(excelMap.get("YJFL")); fileDetail05.setEjfl(excelMap.get("EJFL")); fileDetail05.setYwzl(excelMap.get("YWZL")); fileDetail05.setSjdw(excelMap.get("SJDW")); fileDetail05.setGddw(excelMap.get("GDDW")); fileDetail05.setCbd(excelMap.get("CBD")); fileDetail05.setCljg(excelMap.get("CLJG")); fileDetail05.setCldw(excelMap.get("CLDW")); fileDetail05.setClbm(excelMap.get("CLBM")); fileDetail05.setGds(excelMap.get("GDS")); fileDetail05.setBz(excelMap.get("BZ")); fileDetail05.setGlgd(excelMap.get("GLGD")); fileDetail05.setWtyyfl(excelMap.get("WTYYFL")); fileDetail05.setGdfj(excelMap.get("GDFJ")); fileDetail05.setZrbm(excelMap.get("ZRBM")); fileDetail05.setGdsj(excelMap.get("GDSJ")); fileDetail05.setGqyy(excelMap.get("GQYY")); fileDetail05.setFwqd(excelMap.get("FWQD")); fileDetail05.setSjhtzryy(excelMap.get("SJHTZRYY")); fileDetail05.setFileId(fileId); db.insertSelective(DwFileDetail05Mapper.class, fileDetail05); } //计停非计停 }else if(Constant.FILE_DETAIL_TYPE_06.equals(fileType)) { String[] titleKey = new String[]{"NO","TYPE","UNIT","DEVICE","TIME","EVENTTYPE","FA","TRIPTYPE","CAUSE","CHZDZQK","HANDLE","ERROBRIEF"}; list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if (list.size() == 0) { throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } // 详情数据存入数据库 for (Map excelMap : list) { String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_05_NO"); DwFileDetail07 fileDetail07 = new DwFileDetail07(); fileDetail07.setId(fileDetailId); fileDetail07.setNo(excelMap.get("NO")); fileDetail07.setType(excelMap.get("TYPE")); fileDetail07.setUnit(excelMap.get("UNIT")); fileDetail07.setDevice(excelMap.get("DEVICE")); fileDetail07.setTime(excelMap.get("TIME")); fileDetail07.setEventtype(excelMap.get("EVENTTYPE")); fileDetail07.setFa(excelMap.get("FA")); fileDetail07.setTriptype(excelMap.get("TRIPTYPE")); fileDetail07.setCause(excelMap.get("CAUSE")); fileDetail07.setChzdzqk(excelMap.get("CHZDZQK")); fileDetail07.setHandle(excelMap.get("HANDLE")); fileDetail07.setErrobrief(excelMap.get("ERROBRIEF")); db.insertSelective(DwFileDetail07Mapper.class, fileDetail07); } //客户信息明细 }else if(Constant.FILE_DETAIL_TYPE_07.equals(fileType)){ //标题对应key String[] titleKey = new String[]{ }; String[] titleValue = new String[]{ "所属文件id" }; //验证模板是否正确 Map header = new HashMap(); List headerTitle = new ArrayList(); headerTitle.add(titleKey); headerTitle.add(titleValue); if(headerTitle == null || headerTitle.isEmpty()){ System.out.println("标题列为空"); } else { header.put("headerRows",headerTitle.size() - 1); header.put("header",headerTitle); header.put("cols",headerTitle.get(headerTitle.size() -1).length); boolean b = ExcelTemplateUtil.verificationExcelHeadLine(fileItem,header); if (!b) { System.out.println("导入模板不一致"); } //读取excel数据 list = FilesUtil.readExecleasy(fileItem, titleKey, 1); // 详情数据存入数据库 for (Map excelMap : list) { String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_05_NO"); DwFileDetail07 fileDetail07 = new DwFileDetail07(); fileDetail07.setId(fileDetailId); } } //配变过重载 }else if(Constant.FILE_DETAIL_TYPE_08.equals(fileType)){ //标题对应key String[] titleKey = new String[]{"SEQU","MONTH","UNIT","BZMC","XLMC","GBMC","FZL","ZDFZL","ZDFZLFSSK","EDRL","GZZYYFL","JTYY", "JJCSFL","JTJJCS","WCSJ","SXBPHL","AXDL","BXDL","CXDL","YHSL","GZSC","ZZSC","PMSID","YXID"}; String[] titleValue = new String[]{ "序号","月份","单位","维护班组","所属线路","公变名称","负载率(%)", "最大负载率(%)","最大负载率发生时刻时间","额定容量(kVA)","重载原因分类", "具体原因\n" + "（详细说明原因情况）","解决措施分类","具体解决措施\n" + "（详细说明解决措施）", "完成时间\n" + "（格式：2020/01/01）","三相不平衡率(%)(最大负荷)","A相电流(A)","B相电流(A)", "C相电流(A)","用户数量","过载时长","重载时长","PMSID","营销ID" }; //验证模板是否正确 Map header = new HashMap(); List headerTitle = new ArrayList(); headerTitle.add(titleValue); if(headerTitle == null || headerTitle.isEmpty()){ System.out.println("标题列为空"); } else { header.put("headerRows",headerTitle.size() - 1); header.put("header",headerTitle); header.put("cols",headerTitle.get(headerTitle.size() -1).length); boolean b = ExcelTemplateUtil.verificationExcelHeadLine(fileItem,header); if (!b) { throw new MINBusinessException("导入模板不一致,请使用正确模板");//无数据，请添加数据 } //读取excel数据 list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if(list == null || list.isEmpty()){ System.out.println(""); throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");//注意月份是MM DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyyMMddHHmmss"); DateTimeFormatter fa = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); List ll = new ArrayList(); // 详情数据存入数据库 for (Map excelMap : list) { DwFileDetail09 fileDetail09 = new DwFileDetail09(); fileDetail09.setSequ(Integer.valueOf(StringUtil.ObjToInt(excelMap.get("SEQU")))); fileDetail09.setId(UUID.randomUUID().toString().replace("-", "")); fileDetail09.setMonth(Integer.valueOf(StringUtil.ObjToInt(excelMap.get("MONTH")))); fileDetail09.setUnit(excelMap.get("UNIT")); fileDetail09.setBzmc(excelMap.get("BZMC")); fileDetail09.setGbmc(excelMap.get("GBMC")); fileDetail09.setXlmc(excelMap.get("XLMC")); fileDetail09.setFzl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("FZL")))); fileDetail09.setZdfzl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("ZDFZL")))); fileDetail09.setZdfzlfssk(excelMap.get("ZDFZLFSSK")); fileDetail09.setEdrl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("EDRL")))); fileDetail09.setGzzyyfl(excelMap.get("GZZYYFL")); fileDetail09.setJtyy(excelMap.get("JTYY")); fileDetail09.setJjcsfl(excelMap.get("JJCSFL")); fileDetail09.setJtjjcs(excelMap.get("JTJJCS")); String wcsj = excelMap.get("WCSJ"); if(wcsj != null && !"".equals(wcsj)){ LocalDateTime ldt = LocalDateTime.parse(wcsj,dtf); String datetime = ldt.format(fa); fileDetail09.setWcsj(simpleDateFormat.parse(datetime.replaceAll("/","-"))); } fileDetail09.setSxbphl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("SXBPHL")))); fileDetail09.setAxdl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("AXDL")))); fileDetail09.setBxdl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("BXDL")))); fileDetail09.setCxdl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("CXDL")))); System.out.println("负载率："+excelMap.get("FZL")+",最大负载率："+excelMap.get("ZDFZL")+",A相："+excelMap.get("AXDL")+",B相："+excelMap.get("BXDL")+",C相："+excelMap.get("CXDL")); fileDetail09.setGzsc(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("GZSC")))); fileDetail09.setYhsl(Integer.valueOf(StringUtil.ObjToInt(excelMap.get("YHSL")))); fileDetail09.setZzsc(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("ZZSC")))); fileDetail09.setYxid(excelMap.get("YXID")); fileDetail09.setPmsid(excelMap.get("PMSID")); fileDetail09.setFileId(fileId); ll.add(fileDetail09); } this.batchReportData(ll,1); } //线路过重载 }else if(Constant.FILE_DETAIL_TYPE_09.equals(fileType)){ //标题对应key String[] titleKey = new String[]{ "SEQU","MONTH","XGSMC","BZMC","BDZMC","XLMC","EDDL","FZL","GZZYY", "GZZJTYY","JJCSFL","JTJJCS","WCSJ","GZSC","YXDL", "FSSK","ZDDL","ZDZFSSK","PBSL","PBRL","SFZDH","PMSID"}; String[] titleValue = new String[]{ "序号","月份","所属县公司","管理班组","变电站名称", "线路名称","额定电流(A)","负载率","过载原因分类","具体原因\n" + "（详细说明原因情况）", "解决措施分类","具体解决措施\n" + "（详细说明解决措施）","完成时间\n" + "（格式：2020/01/01）", "过载时长(小时)","运行电流(A)","发生时刻","最大电流(A)","最大值发生时刻", "配变数量(台)","配变容量(kVA)","是否自动化","PMSID" }; //验证模板是否正确 Map header = new HashMap(); List headerTitle = new ArrayList(); headerTitle.add(titleValue); if(headerTitle == null || headerTitle.isEmpty()){ System.out.println("标题列为空"); } else { header.put("headerRows",headerTitle.size() - 1); header.put("header",headerTitle); header.put("cols",headerTitle.get(headerTitle.size() -1).length); boolean b = ExcelTemplateUtil.verificationExcelHeadLine(fileItem,header); if (!b) { throw new MINBusinessException("导入模板不一致,请使用正确模板");//无数据，请添加数据 } //读取excel数据 list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if(list == null || list.isEmpty()){ throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } List ll = new ArrayList(); // 详情数据存入数据库 for (Map excelMap : list) { DwFileDetail10 fileDetail10 = new DwFileDetail10(); fileDetail10.setId(UUID.randomUUID().toString().replace("-", "")); fileDetail10.setSequ(Integer.valueOf(StringUtil.ObjToInt(excelMap.get("SEQU")))); fileDetail10.setMonth(Integer.valueOf(StringUtil.ObjToInt(excelMap.get("MONTH")))); fileDetail10.setXgsmc(excelMap.get("XGSMC")); fileDetail10.setBzmc(excelMap.get("BZMC")); fileDetail10.setBdzmc(excelMap.get("BDZMC")); fileDetail10.setXlmc(excelMap.get("XLMC")); fileDetail10.setEddl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("EDDL")))); fileDetail10.setFzl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("FZL")))); fileDetail10.setGzzyy(excelMap.get("GZZYY")); fileDetail10.setGzzjtyy(excelMap.get("GZZJTYY")); fileDetail10.setJjcsfl(excelMap.get("JJCSFL")); fileDetail10.setJtjjcs(excelMap.get("JTJJCS")); String wcsj = excelMap.get("WCSJ"); if(wcsj != null && !"".equals(wcsj)){ fileDetail10.setWcsj(StringUtil.ObjToDate(wcsj,"1")); } fileDetail10.setGzsc(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("GZSC")))); fileDetail10.setYxdl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("YXDL")))); String fssk = excelMap.get("FSSK"); if(fssk != null && !"".equals(fssk)){ fileDetail10.setFssk(StringUtil.ObjToDate(fssk,"2")); } fileDetail10.setZddl(Float.valueOf(StringUtil.ObjToFloat(excelMap.get("ZDDL")))); String zdzfssk = excelMap.get("ZDZFSSK"); if(zdzfssk != null && !"".equals(zdzfssk)){ fileDetail10.setZdzfssk(StringUtil.ObjToDate(zdzfssk,"2")); } fileDetail10.setPbsl(Integer.valueOf(StringUtil.ObjToInt(excelMap.get("PBSL")))); fileDetail10.setPbrl(Integer.valueOf(StringUtil.ObjToInt(excelMap.get("PBRL")))); fileDetail10.setSfzdh(excelMap.get("SFZDH")); fileDetail10.setPmsid(excelMap.get("PMSID")); fileDetail10.setFileId(fileId); ll.add(fileDetail10); } this.batchXlgzzReportData(ll,2); } // 故障工单信息 }else if(Constant.FILE_DETAIL_TYPE_11.equals(fileType)){ //标题对应key String[] titleKey = new String[]{ "GDBH","GWGDBH","YHBH","GDDW","DWXCSJ", "GZPCSJ", "YJFL", "EJFL", "SJFL", "YWLXMC", "SLSH", "PGR", "PCRY", "SDR", "YHMC", "LXR", "LXDZ", "XCFL", "JJCD", "WHCD", "CQSX", "GZGS", "SLNR", "GDZT", "GZYY", "GZXX", "DYDJ", "ZDCL", "GZDZ", "LXDH", "CLJG", "HBZD", "QXZSC", "DGSC", "GZPCSC", "GZYPJG", "XCQXJL", "SCPFQXDSJ", "ZCPFQXDSJ", "ZDJDSJ", "HFSDSJ", "TJSHSJ", "GDSHSJ", "GDSJ", "ZDBM"}; String[] titleValue = new String[]{ "工单编号" , "国网工单号" , "用户编号" , "供电单位" , "到达现场时间" , "故障排除时间" , "一级分类" , "二级分类" , "三级分类" , "业务类型名称" , "受理时间" , "派工人 " , "派出人员" , "锁定人 " , "用户名称" , "联系人 " , "联系地址" , "现场分类" , "紧急程度" , "危害程度" , "产权属性" , "故障归属" , "受理内容" , "工单状态" , "故障原因" , "故障现象" , "电压等级" , "终端处理" , "故障地址" , "联系电话" , "处理结果" , "合并主单" , "抢修总时长(分)" , "到岗时长(分) " , "故障排除时长(分) " , "故障研判结果" , "现场抢修记录" , "首次派发抢修队时间 " , "再次派发抢修队时间 " , "终端接单时间" , "恢复送电时间" , "提交审核时间" , "工单审核时间" ,"归档时间" , "终端编码" }; //验证模板是否正确 Map header = new HashMap(); List headerTitle = new ArrayList(); headerTitle.add(titleValue); if(headerTitle == null || headerTitle.isEmpty()){ System.out.println("标题列为空"); } else { header.put("headerRows",headerTitle.size() - 1); header.put("header",headerTitle); header.put("cols",headerTitle.get(headerTitle.size() -1).length); boolean b = ExcelTemplateUtil.verificationExcelHeadLine(fileItem,header); if (!b) { throw new MINBusinessException("导入模板不一致,请使用正确模板");//无数据，请添加数据 } //读取excel数据 list = FilesUtil.readExecleasy(fileItem, titleKey, 1); if(list == null || list.isEmpty()){ throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据 } List ll = new ArrayList(); // 详情数据存入数据库 for (Map excelMap : list) { DwFileDetail11 fileDetail11 = new DwFileDetail11(); fileDetail11.setId(UUID.randomUUID().toString().replace("-", "")); fileDetail11.setGdbh(excelMap.get("GDBH")); fileDetail11.setGwgdbh(excelMap.get("GWGDBH")); fileDetail11.setYhbh(excelMap.get("YHBH")); fileDetail11.setGddw(excelMap.get("GDDW")); String dwxcsj = excelMap.get("DWXCSJ"); if(dwxcsj != null && !"".equals(dwxcsj)){ fileDetail11.setDwxcsj(StringUtil.ObjToDate(dwxcsj,"2")); } String gzpcsj = excelMap.get("GZPCSJ"); if(gzpcsj != null && !"".equals(gzpcsj)){ fileDetail11.setGzpcsj(StringUtil.ObjToDate(gzpcsj,"2")); } // fileDetail11.setDwxcsj(excelMap.get("DWXCSJ")); // fileDetail11.setGzpcsj(excelMap.get("GZPCSJ")); fileDetail11.setYjfl(excelMap.get("YJFL")); fileDetail11.setEjfl(excelMap.get("EJFL")); fileDetail11.setSjfl(excelMap.get("SJFL")); fileDetail11.setYwlxmc(excelMap.get("YWLXMC")); String slsh = excelMap.get("SLSH"); if(slsh != null && !"".equals(slsh)){ fileDetail11.setSlsh(StringUtil.ObjToDate(slsh,"2")); } // fileDetail11.setSlsh(excelMap.get("SLSH")); fileDetail11.setPgr(excelMap.get("PGR")); fileDetail11.setPcry(excelMap.get("PCRY")); fileDetail11.setSdr(excelMap.get("SDR")); fileDetail11.setYhmc(excelMap.get("YHMC")); fileDetail11.setLxr(excelMap.get("LXR")); fileDetail11.setLxdz(excelMap.get("LXDZ")); fileDetail11.setXcfl(excelMap.get("XCFL")); fileDetail11.setJjcd(excelMap.get("JJCD")); fileDetail11.setWhcd(excelMap.get("WHCD")); fileDetail11.setCqsx(excelMap.get("CQSX")); fileDetail11.setGzgs(excelMap.get("GZGS")); fileDetail11.setSlnr(excelMap.get("SLNR")); fileDetail11.setGdzt(excelMap.get("GDZT")); fileDetail11.setGzyy(excelMap.get("GZYY")); fileDetail11.setGzxx(excelMap.get("GZXX")); fileDetail11.setDydj(excelMap.get("DYDJ")); fileDetail11.setZdcl(excelMap.get("ZDCL")); fileDetail11.setGzdz(excelMap.get("GZDZ")); fileDetail11.setLxdh(excelMap.get("LXDH")); fileDetail11.setCljg(excelMap.get("CLJG")); fileDetail11.setHbzd(excelMap.get("HBZD")); fileDetail11.setQxzsc(StringUtil.ObjToFloat(excelMap.get("QXZSC"))); fileDetail11.setDgsc(StringUtil.ObjToFloat(excelMap.get("DGSC"))); fileDetail11.setGzpcsc(StringUtil.ObjToFloat(excelMap.get("GZPCSC"))); fileDetail11.setGzypjg(excelMap.get("GZYPJG")); fileDetail11.setXcqxjl(excelMap.get("XCQXJL")); String scpfqxdsj = excelMap.get("SCPFQXDSJ"); if(scpfqxdsj != null && !"".equals(scpfqxdsj)){ fileDetail11.setScpfqxdsj(StringUtil.ObjToDate(scpfqxdsj,"2")); } // fileDetail11.setScpfqxdsj(excelMap.get("SCPFQXDSJ")); String zcpfqxdsj = excelMap.get("ZCPFQXDSJ"); if(zcpfqxdsj != null && !"".equals(zcpfqxdsj)){ fileDetail11.setZcpfqxdsj(StringUtil.ObjToDate(zcpfqxdsj,"2")); } // fileDetail11.setZcpfqxdsj(excelMap.get("ZCPFQXDSJ")); String zdjdsj = excelMap.get("ZDJDSJ"); if(zdjdsj != null && !"".equals(zdjdsj)){ fileDetail11.setZdjdsj(StringUtil.ObjToDate(zdjdsj,"2")); } // fileDetail11.setZdjdsj(excelMap.get("ZDJDSJ")); String hfsdsj = excelMap.get("HFSDSJ"); if(hfsdsj != null && !"".equals(hfsdsj)){ fileDetail11.setHfsdsj(StringUtil.ObjToDate(hfsdsj,"2")); } // fileDetail11.setHfsdsj(excelMap.get("HFSDSJ")); String tjshsj = excelMap.get("TJSHSJ"); if(tjshsj != null && !"".equals(tjshsj)){ fileDetail11.setTjshsj(StringUtil.ObjToDate(tjshsj,"2")); } // fileDetail11.setTjshsj(excelMap.get("TJSHSJ")); String gdshsj = excelMap.get("GDSHSJ"); if(gdshsj != null && !"".equals(gdshsj)){ fileDetail11.setGdshsj(StringUtil.ObjToDate(gdshsj,"2")); } // fileDetail11.setGdshsj(excelMap.get("GDSHSJ")); String gdsj = excelMap.get("GDSJ"); if(gdsj != null && !"".equals(gdsj)){ fileDetail11.setGdsj(StringUtil.ObjToDate(gdsj,"2")); } // fileDetail11.setGdsj(excelMap.get("GDSJ")); fileDetail11.setZdbm(excelMap.get("ZDBM")); fileDetail11.setFileId(fileId); ll.add(fileDetail11); } this.batchGzgdReportData(ll,2); } } // 文件名 String filePath = Service.lookup(IPropertiesService.class).getSystemProperties().get("FILE_SERVER_BASE_PATH").getKey(); String saveName = UUID.randomUUID().toString().replaceAll("-", ""); String[] fileNameArray = fileName.split("\\."); saveName = saveName + "." + fileNameArray[fileNameArray.length - 1]; // 保存文件数据 String currentDate = DateUtil.getCurrentDateString(); User user = (User)session.getUser(); DwFileInf fileInf = new DwFileInf(); fileInf.setId(fileId); fileInf.setUploadDate(currentDate); fileInf.setFileName(fileName); fileInf.setCreatUser(user.getId()); fileInf.setBranch(user.getBranchid()); fileInf.setDataNum(list.size() + ""); fileInf.setType(fileType); fileInf.setSavePath(filePath + saveName); db.insertSelective(DwFileInfMapper.class, fileInf); // 保存文件到本地 FilesUtil.uploadFile(fileItem, filePath, "", saveName, 0); return res; } /** * @param list * @param type */ private void batchGzgdReportData(List list, int type) { if(list != null && list.size() > 0){ List tempList = new ArrayList(); int cou = 0; for (int i = 0; i < list.size() ; i++) { if((list.size() - i) >= 1000){ tempList.add((DwFileDetail11) list.get(i)); cou += 1; if(cou == 1000){ try { db.getMybatisMapper(IReportBatchMapper.class).insertGzgdReport(tempList); } catch (MINBusinessException e) { e.printStackTrace(); } cou = 0; tempList = new ArrayList(); } } if((list.size() - i) < 1000){ tempList.add((DwFileDetail11) list.get(i)); } } try { db.getMybatisMapper(IReportBatchMapper.class).insertGzgdReport(tempList); } catch (MINBusinessException e) { e.printStackTrace(); } } } /** * 批处理-配变过重载 * @param list */ @Transactional protected void batchReportData(List list,int type) throws MINBusinessException { if(list != null && list.size() > 0){ List tempList = new ArrayList(); int cou = 0; for (int i = 0; i < list.size() ; i++) { if((list.size() - i) >= 1000){ tempList.add( list.get(i)); cou += 1; if(cou == 1000){ try { db.getMybatisMapper(IReportBatchMapper.class).insertPbgzzReport(tempList); } catch (MINBusinessException e) { e.printStackTrace(); } cou = 0; tempList = new ArrayList(); } } if((list.size() - i) < 1000){ tempList.add(list.get(i)); } } try { db.getMybatisMapper(IReportBatchMapper.class).insertPbgzzReport(tempList); } catch (MINBusinessException e) { e.printStackTrace(); } } } /** * 批处理 线路过重载 * @param list */ @Transactional protected void batchXlgzzReportData(List list,int type) throws MINBusinessException { if(list != null && list.size() > 0){ List tempList = new ArrayList(); int cou = 0; for (int i = 0; i < list.size() ; i++) { if((list.size() - i) >= 1000){ tempList.add( list.get(i)); cou += 1; if(cou == 1000){ try { db.getMybatisMapper(IReportBatchMapper.class).insertXlgzzReport(tempList); } catch (MINBusinessException e) { e.printStackTrace(); } cou = 0; tempList = new ArrayList(); } } if((list.size() - i) < 1000){ tempList.add(list.get(i)); } } try { db.getMybatisMapper(IReportBatchMapper.class).insertXlgzzReport(tempList); } catch (MINBusinessException e) { e.printStackTrace(); } } } /** * 原始数据文件下载 * @param fileId * @param response * @return * @throws MINBusinessException * @throws IOException */ @MINAction(value = FILE_INF_DOWNLOAD) public HttpServletResponse fileInfDownload( @MINParam(key = "fileId") String fileId, HttpServletResponse response ) throws MINBusinessException, IOException { db = Service.lookup(IMINDataBaseService.class); DwFileInf fileInf = db.selectByPrimaryKey(DwFileInfMapper.class, fileId); String type = fileInf.getType(); // FilesUtil.writeFile2Response(response, fileInf.getSavePath(), fileInf.getFileName()); OutputStream os = null; ServletOutputStream ros = null; ros = response.getOutputStream(); os = new BufferedOutputStream(ros); // 台区停电 if(Constant.FILE_DETAIL_TYPE_00.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("台区停电数据.xls", "UTF-8")); DwFileDetail00Example example = new DwFileDetail00Example(); example.createCriteria().andFileIdEqualTo(fileId); List selectByExample = db.getMybatisMapper(DwFileDetail00Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","台区编号","台区名称","数据日期","单位","供电所名称","终端名称","终端编号","终端地址码","停电时间","来电时间","所属文件id"}; util.exportExcel("台区停电", selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_01.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("终端投退运数据.xls", "UTF-8")); DwFileDetail01Example example = new DwFileDetail01Example(); example.createCriteria().andFileIdEqualTo(fileId); List selectByExample = db.getMybatisMapper(DwFileDetail01Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","ID","TNAME","CMODE","VALUE","facname","type","fdid","yxstatus","tytime","所属文件id"}; util.exportExcel("终端投退运数据", selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_02.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("FA动作数据.xls", "UTF-8")); DwFileDetail02Example example = new DwFileDetail02Example(); example.createCriteria().andFileIdEqualTo(fileId); List selectByExample = db.getMybatisMapper(DwFileDetail02Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","故障停电开始时间","故障停电结束时间","供电类型","出线变电站","线路名称","线路类型","启动FA","故障区间判断","故障前后段恢复","故障隔离","所属文件id"}; util.exportExcel("FA动作数据", selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_03.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("低电压数据.xls", "UTF-8")); DwFileDetail03Example example = new DwFileDetail03Example(); example.createCriteria().andFileIdEqualTo(fileId); List selectByExample = db.getMybatisMapper(DwFileDetail03Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","市公司名称","县公司名称","变电站名称","线路名称","台区id","变压器名称","用户管理单位名称1","用户编号","用户名称","用户管理单位名称2","电压最大值","电压最小值","电压合格率","超上限率","超下限率","低压时长","供电类型","累计天数","数据日期","供电单位","所属文件id"}; util.exportExcel("低电压数据", selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_04.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("过电压数据.xls", "UTF-8")); DwFileDetail04Example example = new DwFileDetail04Example(); example.createCriteria().andFileIdEqualTo(fileId); List selectByExample = db.getMybatisMapper(DwFileDetail04Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","市公司名称","县公司名称","变电站名称","线路名称","台区id","变压器名称","用户管理单位名称1","用户编号","用户名称","用户管理单位名称2","电压最大值","电压最小值","电压合格率","超上限率","超下限率","过电压时长","供电类型","累计天数","供电单位","数据日期","所属文件id"}; util.exportExcel("过电压数据", selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_05.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("95598工单数据.xls", "UTF-8")); DwFileDetail05Example example = new DwFileDetail05Example(); example.createCriteria().andFileIdEqualTo(fileId); List selectByExample = db.getMybatisMapper(DwFileDetail05Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","工单编号","国网工单编号","状态","业务类型","当前步骤","受理人员","受理时间","受理内容","用户编号","用户名称","联系地址","联系电话","回访内容","一级分类","二级分类","业务子类","上级单位","供电单位","抄表段","处理结果","处理单位","处理部门","关联工单","问题原因分类","工单分级","责任部门","归档时间","挂起时间","挂起原因","服务渠道","省级回退责任原因","所属文件id"}; util.exportExcel("95598工单数据", selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_06.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("计停非计停数据.xls", "UTF-8")); DwFileDetail07Example example = new DwFileDetail07Example(); example.createCriteria().andFileIdEqualTo(fileId); List selectByExample = db.getMybatisMapper(DwFileDetail07Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","类型","单位","设备名称","发生时间","事项类型","馈线自动化动作情况","跳闸类型","故障原因","重合闸动作情况","处理纪事","异常概况","所属文件id","原EXCEL编号"}; util.exportExcel("计停非计停数据", selectByExample, os, "2007", header); }else if (Constant.FILE_DETAIL_TYPE_07.equals(type)) {//客户明细 }else if (Constant.FILE_DETAIL_TYPE_08.equals(type)) {//配变过重载 response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("配变过重载数据.xls", "UTF-8")); DwFileDetail09Example example = new DwFileDetail09Example(); example.createCriteria().andFileIdEqualTo(fileId); example.setOrderByClause("dfd9_sequ"); List selectByExample = db.getMybatisMapper(DwFileDetail09Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","月份", "单位", "班组名称", "公变名称", "线路名称", "负载率(%) ", "最大负载率(%)", "最大负载率发生时刻时间", "额定容量(kVA) ", "过(重)载原因分类 ", "具体原因（详细说明原因情况）", "解决措施分类 ", "具体解决措施（详细说明解决措施）", "完成时间", "三相不平衡率(%)(最大负荷) ", "A相电流(A)", "B相电流(A)", "C相电流(A)", "过载时长(小时)", "用户数量", "重载时长", "营销ID", "PMSID", "所属文件ID",""}; util.exportExcel("配变过重载数据", selectByExample, os, "2007", header); }else if (Constant.FILE_DETAIL_TYPE_09.equals(type)) {//线路过重载 response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("线路过重载数据.xls", "UTF-8")); DwFileDetail10Example example = new DwFileDetail10Example(); example.createCriteria().andFileIdEqualTo(fileId); example.setOrderByClause("dfd10_sequ"); List selectByExample = db.getMybatisMapper(DwFileDetail10Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号", "原EXCEL编号","月份", "所属县公司", "班组名称 ", "变电站名称", "线路名称 ", "额定电流(A)", "负载率(%) ", "过(重)载原因分类", "具体原因（详细说明原因情况） ", "解决措施分类 ", "具体解决措施（详细说明解决措施） ", "完成时间 ", "过载时长(小时) ", "运行电流(A)", "发生时刻 ", "最大电流(A)", "最大值发生时刻", "配变数量(台)", "配变容量(kVA)", "是否自动化", "PMSID", "所属文件ID", ""}; util.exportExcel("线路过重载数据", selectByExample, os, "2007", header); } return response; } /** * 原始文件删除 * @param fileId * @param fileType * @return * @throws MINBusinessException */ @MINAction(value = FILE_INF_DELETE, transaction = IMINTransactionEnum.CMT) public MINActionResult fileInfDelete( @MINParam(key = "fileId") String fileId, @MINParam(key = "fileType") String fileType ) throws MINBusinessException { MINActionResult res = new MINActionResult(); db = Service.lookup(IMINDataBaseService.class); // 删除原始文件--逻辑删除 DwFileInf fileInf = new DwFileInf(); fileInf.setState(Constant.FILE_INF_STATE_1); fileInf.setId(fileId); db.updateByPrimaryKeySelective(DwFileInfMapper.class, fileInf); /** 删除明细 */ if (Constant.FILE_DETAIL_TYPE_00.equals(fileType)) {// 台区停电 DwFileDetail00Example fileDetail00Example = new DwFileDetail00Example(); fileDetail00Example.createCriteria().andFileIdEqualTo(fileId); db.deleteByExample(DwFileDetail00Mapper.class, fileDetail00Example); } else if (Constant.FILE_DETAIL_TYPE_01.equals(fileType)) {// 低电压 } else if (Constant.FILE_DETAIL_TYPE_02.equals(fileType)) { } else if(Constant.FILE_DETAIL_TYPE_03.equals(fileType)){//低电压 DwFileDetail03Example DwFileDetail03Example = new DwFileDetail03Example(); DwFileDetail03Example.createCriteria().andFileIdEqualTo(fileId); db.deleteByExample(DwFileDetail03Mapper.class,DwFileDetail03Example); } else if(Constant.FILE_DETAIL_TYPE_04.equals(fileType)){//过电压 DwFileDetail04Example DwFileDetail04Example = new DwFileDetail04Example(); DwFileDetail04Example.createCriteria().andFileIdEqualTo(fileId); db.deleteByExample(DwFileDetail04Mapper.class,DwFileDetail04Example); } else if(Constant.FILE_DETAIL_TYPE_05.equals(fileType)){//95598工单 DwFileDetail05Example DwFileDetail05Example = new DwFileDetail05Example(); DwFileDetail05Example.createCriteria().andFileIdEqualTo(fileId); db.deleteByExample(DwFileDetail05Mapper.class,DwFileDetail05Example); } else if(Constant.FILE_DETAIL_TYPE_06.equals(fileType)){//计停非计停 DwFileDetail07Example DwFileDetail07Example = new DwFileDetail07Example(); DwFileDetail07Example.createCriteria().andFileIdEqualTo(fileId); db.deleteByExample(DwFileDetail07Mapper.class,DwFileDetail07Example); } else if(Constant.FILE_DETAIL_TYPE_07.equals(fileType)){//客户信息明细 } else if(Constant.FILE_DETAIL_TYPE_08.equals(fileType)){//配变过重载 DwFileDetail09Example DwFileDetail09Example = new DwFileDetail09Example(); DwFileDetail09Example.createCriteria().andFileIdEqualTo(fileId); db.deleteByExample(DwFileDetail09Mapper.class,DwFileDetail09Example); } else if(Constant.FILE_DETAIL_TYPE_09.equals(fileType)){//线路过重载 DwFileDetail10Example DwFileDetail10Example = new DwFileDetail10Example(); DwFileDetail10Example.createCriteria().andFileIdEqualTo(fileId); db.deleteByExample(DwFileDetail10Mapper.class,DwFileDetail10Example); } return res; } /** * 原始文件明细查询 * @param fileId * @param fileIdListStr * @param fileType * @return * @throws MINBusinessException */ @MINAction(value = FILE_INF_DETAIL) public MINActionResult fileInfDetail( @MINParam(key = "fileId") String fileId, @MINParam(key = "fileIdListStr") String fileIdListStr, @MINParam(key = "fileType") String fileType, @MINParam(key = "page", defaultValue = "1") int page, @MINParam(key = "limit", defaultValue = "100") int limit ) throws MINBusinessException { MINActionResult res = new MINActionResult(); db = Service.lookup(IMINDataBaseService.class); MINRowBounds rows = new MINRowBounds(page, limit); rows.setSeparateSql(true); Map param = new HashMap(); param.put("fileId", fileId); param.put("fileIdListStr", fileIdListStr); param.put("fileType", fileType); // 查询明细信息 List fileInfDetailList = Service.lookup(IDianwangService.class).queryFileInfDetail(param, rows); res.set(IMINBusinessConstant.F_PAGING_LAY, fileInfDetailList); res.set(IMINBusinessConstant.F_PAGING_COUNT, rows.getCount()); return res; } /** * 原始数据文件下载 * @param type * @param response * @return * @throws MINBusinessException * @throws IOException */ @MINAction(value = FILE_DATA_DOWNLOAD) public HttpServletResponse fileDataDownload( @MINParam(key = "type") String type, @MINParam(key = "date") String date, HttpServletResponse response ) throws MINBusinessException, IOException, ParseException { db = Service.lookup(IMINDataBaseService.class); date = date.replaceAll(" ", ""); date = date.replaceAll("-", ""); OutputStream os = null; ServletOutputStream ros = null; ros = response.getOutputStream(); os = new BufferedOutputStream(ros); // 台区停电 if(Constant.FILE_DETAIL_TYPE_00.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode(date + "台区停电数据.xls", "UTF-8")); DwFileDetail00Example example = new DwFileDetail00Example(); example.createCriteria().andDateGreaterThanOrEqualTo(date.substring(0, 8)).andDateLessThanOrEqualTo(date.substring(8, 16)); List selectByExample = db.getMybatisMapper(DwFileDetail00Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","台区编号","台区名称","数据日期","单位","供电所名称","终端名称","终端编号","终端地址码","停电时间","来电时间","所属文件id"}; util.exportExcel(date, selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_01.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode(date + "终端投退运数据.xls", "UTF-8")); DwFileDetail01Example example = new DwFileDetail01Example(); example.createCriteria().andExcelTytimeGreaterThanOrEqualTo(date.substring(0, 8) + "000000").andExcelTytimeLessThanOrEqualTo(date.substring(8, 16) + "235959"); List selectByExample = db.getMybatisMapper(DwFileDetail01Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","ID","TNAME","CMODE","VALUE","facname","type","fdid","yxstatus","tytime","所属文件id"}; util.exportExcel(date, selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_02.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode(date + "FA动作数据.xls", "UTF-8")); DwFileDetail02Example example = new DwFileDetail02Example(); example.createCriteria().andBeginTimeGreaterThanOrEqualTo(date.substring(0, 8) + "000000").andEndTimeLessThanOrEqualTo(date.substring(8, 16) + "235959"); List selectByExample = db.getMybatisMapper(DwFileDetail02Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","故障停电开始时间","故障停电结束时间","供电类型","出线变电站","线路名称","线路类型","启动FA","故障区间判断","故障前后段恢复","故障隔离","所属文件id"}; util.exportExcel(date, selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_03.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode(date + "低电压数据.xls", "UTF-8")); DwFileDetail03Example example = new DwFileDetail03Example(); example.createCriteria().andSjrqGreaterThanOrEqualTo(date.substring(0, 8)).andSjrqLessThanOrEqualTo(date.substring(8, 16)); List selectByExample = db.getMybatisMapper(DwFileDetail03Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","市公司名称","县公司名称","变电站名称","线路名称","台区id","变压器名称","用户管理单位名称1","用户编号","用户名称","用户管理单位名称2","电压最大值","电压最小值","电压合格率","超上限率","超下限率","低压时长","供电类型","累计天数","数据日期","供电单位","所属文件id"}; util.exportExcel(date, selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_04.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode(date + "过电压数据.xls", "UTF-8")); DwFileDetail04Example example = new DwFileDetail04Example(); example.createCriteria().andSjrqGreaterThanOrEqualTo(date.substring(0, 8)).andSjrqLessThanOrEqualTo(date.substring(8, 16)); List selectByExample = db.getMybatisMapper(DwFileDetail04Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","市公司名称","县公司名称","变电站名称","线路名称","台区id","变压器名称","用户管理单位名称1","用户编号","用户名称","用户管理单位名称2","电压最大值","电压最小值","电压合格率","超上限率","超下限率","过电压时长","供电类型","累计天数","供电单位","数据日期","所属文件id"}; util.exportExcel(date, selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_05.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode(date + "95598工单数据.xls", "UTF-8")); DwFileDetail05Example example = new DwFileDetail05Example(); example.createCriteria().andSlsjGreaterThanOrEqualTo(date.substring(0, 8)).andSlsjLessThanOrEqualTo(date.substring(8, 16)); List selectByExample = db.getMybatisMapper(DwFileDetail05Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","工单编号","国网工单编号","状态","业务类型","当前步骤","受理人员","受理时间","受理内容","用户编号","用户名称","联系地址","联系电话","回访内容","一级分类","二级分类","业务子类","上级单位","供电单位","抄表段","处理结果","处理单位","处理部门","关联工单","问题原因分类","工单分级","责任部门","归档时间","挂起时间","挂起原因","服务渠道","省级回退责任原因","所属文件id"}; util.exportExcel(date, selectByExample, os, "2007", header); } else if (Constant.FILE_DETAIL_TYPE_06.equals(type)) { response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode(date + "计停非计停数据.xls", "UTF-8")); DwFileDetail07Example example = new DwFileDetail07Example(); example.createCriteria().andTimeGreaterThanOrEqualTo(date.substring(0, 8) + "000000").andTimeLessThanOrEqualTo(date.substring(8, 16) + "235959"); List selectByExample = db.getMybatisMapper(DwFileDetail07Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","类型","单位","设备名称","发生时间","事项类型","馈线自动化动作情况","跳闸类型","故障原因","重合闸动作情况","处理纪事","异常概况","所属文件id"}; util.exportExcel(date, selectByExample, os, "2007", header); }else if (Constant.FILE_DETAIL_TYPE_07.equals(type)) {//客户明细 }else if (Constant.FILE_DETAIL_TYPE_08.equals(type)) {//配变过重载 response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("配变过重载数据.xls", "UTF-8")); DwFileDetail09Example example = new DwFileDetail09Example(); example.createCriteria().andWcsjGreaterThanOrEqualTo(StringUtil.ObjNumToDate(date.substring(0, 8) + "000000")) .andWcsjLessThanOrEqualTo(StringUtil.ObjNumToDate(date.substring(8, 16) + "235959")); example.setOrderByClause("dfd9_sequ"); List selectByExample = db.getMybatisMapper(DwFileDetail09Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","月份", "单位","班组名称","公变名称","线路名称","负载率(%) ","最大负载率(%)","最大负载率发生时刻时间", "额定容量(kVA) ","过(重)载原因分类 ","具体原因（详细说明原因情况）","解决措施分类 ","具体解决措施（详细说明解决措施）", "完成时间","三相不平衡率(%)(最大负荷) ","A相电流(A)","B相电流(A)","C相电流(A)","过载时长(小时)", "用户数量","重载时长","营销ID","PMSID","所属文件ID",""}; util.exportExcel("配变过重载数据", selectByExample, os, "2007", header); }else if (Constant.FILE_DETAIL_TYPE_09.equals(type)) {//线路过重载 response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("线路过重载数据.xls", "UTF-8")); DwFileDetail10Example example = new DwFileDetail10Example(); example.createCriteria().andWcsjGreaterThanOrEqualTo(StringUtil.ObjNumToDate(date.substring(0, 8) + "000000")) .andWcsjLessThanOrEqualTo(StringUtil.ObjNumToDate(date.substring(8, 16) + "235959")); example.setOrderByClause("dfd10_sequ"); List selectByExample = db.getMybatisMapper(DwFileDetail10Mapper.class).selectByExample(example); ExportExcelUtil util = new ExportExcelUtil(); String[] header = {"系统编号","原EXCEL编号","月份", "所属县公司","班组名称 ","变电站名称","线路名称 ","额定电流(A)","负载率(%) ","过(重)载原因分类", "具体原因（详细说明原因情况） ","解决措施分类 ","具体解决措施（详细说明解决措施） ","完成时间 ", "过载时长(小时) ","运行电流(A)","发生时刻 ","最大电流(A)","最大值发生时刻","配变数量(台)", "配变容量(kVA)","是否自动化","PMSID","所属文件ID",""}; util.exportExcel("线路过重载数据", selectByExample, os, "2007", header); } return response; } }