/** * 查询通讯费支付信息 * * @return String */ public String comsPayCommunicationSearch() { // 需要先取得pageBean实例 pageBean = (PageBean) request.getSession().getAttribute("pageBean"); if (pageBean == null) { pageBean = new PageBean(); request.getSession().setAttribute("pageBean", pageBean); } String sql = ""; if (this.getPageNo() <= 0) pageBean.setCurrentPage(1); else pageBean.setCurrentPage(this.getPageNo()); if (getItemUids() != null && (!getItemUids().trim().equals("")) && (!getItemUids().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG)) && (!getItemUids().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) { sql += " and v.itemUid like '" + getItemUids().trim() + "%'"; } if (getOrgCode() != null && !getOrgCode().trim().equals("")) { Organization orgSelected = orgDao.findByOrgId(getOrgCode()); String innercodeSelected = (String) orgDao.getOrgInnerInfo(orgSelected)[0]; if (orgSelected.getOrgType().trim().equals("00")) sql += " and v.orgInnerCode01 like '" + innercodeSelected + "%'"; else if (orgSelected.getOrgType().trim().equals("01")) sql += " and v.orgInnerCode02 like '" + innercodeSelected + "%'"; else if (orgSelected.getOrgType().trim().equals("02")) sql += " and v.orgInnerCode03 like '" + innercodeSelected + "%'"; } if (getTermSeqs() != null && (!getTermSeqs().trim().equals("")) && (!getTermSeqs().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG)) && (!getTermSeqs().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) { sql += " and v.termseq = '" + getTermSeqs().trim() + "'"; } if (getTermnos() != null && (!getTermnos().trim().equals("")) && (!getTermnos().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG)) && (!getTermnos().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) { sql += " and v.termno = '" + getTermnos().trim() + "'"; } if (this.getSearchStartDate() != null && !getSearchStartDate().equals("")) { sql = sql + " and v.costDate >='" + DateConvert.convertAtoB(getSearchStartDate(), "yyyy-MM", "yyyyMM") + "'"; } if (this.getSearchEndDate() != null && !getSearchEndDate().equals("")) { sql = sql + " and v.costDate <='" + DateConvert.convertAtoB(getSearchEndDate(), "yyyy-MM", "yyyyMM") + "'"; } String state = this.getSearchPayState(); if (state != null && !state.equals("")) { if (state != "0" && !state.equals("0")) { sql = sql + " and v.payState ='" + state + "'"; } } // 构造查询 String queryStr = " select max(v.batchNum) as batchNum,max(v.termno) as termno," + " max(v.termseq) as termseq,max(v.address) as address,max(v.payTotalMoney) as money," + "(select min(f.costDate) from ComsPayCommunication f where f.termBatchNum = v.termBatchNum) as startCostDate," + "(select max(f.costDate) from ComsPayCommunication f where f.termBatchNum = v.termBatchNum) as endCostDate " + "from ComsPayCommunication v where 1=1 "; queryStr = queryStr + sql + " group by v.termBatchNum order by max(v.batchNum)"; String countStr = " select count(distinct v.termBatchNum) from ComsPayCommunication v where 1=1 "; countStr = countStr + sql; payList = (List<Object[]>) comsPayCommunicationDao.findByPageSupport(countStr, queryStr, pageBean); if (payList.size() > 0) { for (Object[] cpf : payList) { String startCostDate = cpf[5].toString(); String endCostDate = cpf[6].toString(); cpf[5] = DateConvert.convertAtoB(startCostDate, "yyyyMM", "yyyy-MM"); cpf[6] = DateConvert.convertAtoB(endCostDate, "yyyyMM", "yyyy-MM"); } } return SUCCESS; }
@SuppressWarnings("unchecked") public String makeReport() { String sql = ""; if (getItemUids() != null && (!getItemUids().trim().equals("")) && (!getItemUids().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG)) && (!getItemUids().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) { sql += " and v.itemUid like '" + getItemUids().trim() + "%'"; } Organization orgSelected = null; if (getOrgCode() != null && !getOrgCode().trim().equals("")) { orgSelected = orgDao.findByOrgId(getOrgCode()); String innercodeSelected = (String) orgDao.getOrgInnerInfo(orgSelected)[0]; if (orgSelected.getOrgType().trim().equals("00")) sql += " and v.orgInnerCode01 like '" + innercodeSelected + "%'"; else if (orgSelected.getOrgType().trim().equals("01")) sql += " and v.orgInnerCode02 like '" + innercodeSelected + "%'"; else if (orgSelected.getOrgType().trim().equals("02")) sql += " and v.orgInnerCode03 like '" + innercodeSelected + "%'"; } if (getTermSeqs() != null && (!getTermSeqs().trim().equals("")) && (!getTermSeqs().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG)) && (!getTermSeqs().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) { sql += " and v.termseq = '" + getTermSeqs().trim() + "'"; } if (getTermnos() != null && (!getTermnos().trim().equals("")) && (!getTermnos().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG)) && (!getTermnos().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) { sql += " and v.termno = '" + getTermnos().trim() + "'"; } if (this.getSearchStartDate() != null && !getSearchStartDate().equals("")) { sql = sql + " and v.costDate >='" + DateConvert.convertAtoB(getSearchStartDate(), "yyyy-MM", "yyyyMM") + "'"; } if (this.getSearchEndDate() != null && !getSearchEndDate().equals("")) { sql = sql + " and v.costDate <='" + DateConvert.convertAtoB(getSearchEndDate(), "yyyy-MM", "yyyyMM") + "'"; } String state = this.getSearchPayState(); if (state != null && !state.equals("")) { if (state != "0" && !state.equals("0")) { sql = sql + " and v.payState ='" + state + "'"; } } // 构造查询 String queryStr = " select v from ComsPayCommunication v where 1=1 " + sql + " order by v.termno,v.costDate"; List<ComsPayCommunication> dataList = new ArrayList<ComsPayCommunication>(); dataList.addAll(comsPayCommunicationDao.getAllDataByQuery(queryStr)); if (dataList != null && dataList.size() > 0) { // 翻译 for (ComsPayCommunication pay : dataList) { pay.setCostDate( AppCommonVarRegiter.changeTimeStyle("yyyyMM", pay.getCostDate(), "yyyy-MM")); // 运营机构 pay.setOrgInnerCode01(orgDao.findByInnerCode(pay.getOrgInnerCode01()).getOrgNameCn()); // 项目 pay.setItemUid(comsProjectDao.findById(pay.getItemUid()).getItemName()); } Authuser authuser = (Authuser) request.getSession().getAttribute("currentUser"); filename = "通讯费支付历史明细报表.xls"; Map beans = new HashMap(); beans.put("orgName", orgSelected.getOrgNameCn()); beans.put("authorUser", authuser.getRealName()); Date createDate = new Date(); beans.put("createDate", createDate); beans.put("dataList", dataList); HSSFWorkbook wb = new HSSFWorkbook(); Configuration config = new Configuration(); XLSTransformer transformer = new XLSTransformer(config); InputStream is = null; try { is = new FileInputStream(request.getRealPath("/WEB-INF") + filepath); wb = transformer.transformXLS(is, beans); // 产生POI输出流 ByteArrayOutputStream os = new ByteArrayOutputStream(); wb.write(os); excelStream = new ByteArrayInputStream(os.toByteArray()); } catch (IOException e) { e.printStackTrace(); } message = "生成报表:" + filename; new LogAssistant( authuser.getUsername().trim(), request.getRemoteAddr() + ":" + TrackOpterationEnum.MAKEREPORT.getOperation(), message) .logUserOpt(); return "excel"; } else { message = "没有通讯费支付记录!"; } return SUCCESS; }