/**
   * 导出对账日志明细
   *
   * @param mapping
   * @param form
   * @param request
   * @param response
   * @return
   * @throws Exception
   */
  public ActionForward excelExport(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {
    CheckAccountErrorLogForm checkAcountErrorLogForm = (CheckAccountErrorLogForm) form;
    CheckAccountErrorLogBean checkAccountErrorLogBean = new CheckAccountErrorLogBean();
    PageAttribute page =
        new PageAttribute(checkAcountErrorLogForm.getCurPage(), Constant.PAGE_SIZE);
    String[] totalFee =
        checkAccountErrorLogBean.checkAccountErrorLogCount(
            "wht_accountErrorLog", checkAcountErrorLogForm);
    page.setRsCount(Integer.parseInt(totalFee[0]));
    List list =
        checkAccountErrorLogBean.listCheckAccountErrorLog_EXCEL(
            "wht_accountErrorLog", checkAcountErrorLogForm, page);
    // 导出excel
    String[][] colTitles = new String[][] {{"编号", "接口商", "账目日期", "任务执行日期", "对账结果"}};
    int size = colTitles[0].length;
    List body = new ArrayList();
    // 只取其中某些字段
    int count = 0;
    int sum1 = 0;
    for (Object tmp : list) {
      String[] temp = (String[]) tmp;
      String[] recode = new String[size];
      // 取其中某些字段
      int i = 0;
      recode[i++] = ++count + "";
      recode[i++] = temp[0];
      recode[i++] = temp[1];
      recode[i++] = temp[2];
      recode[i++] = temp[3];
      body.add(recode);
    }
    Map rsMap = null;
    // 转换成excel格式数据
    if (body.size() > 0) {
      rsMap = DataUtil.toNestedStringsListMap(1, body);
    }

    Excel excel = new Excel();
    excel.setCols(colTitles[0].length);
    excel.createCaption("对账日志明细报表");
    excel.createColCaption(colTitles);
    if (rsMap != null) {
      excel.createBody(rsMap);
    }
    // excel.createRemarks("交易明细");
    String excelFileName = URLEncoder.encode("对账日志明细报表" + ".xls", "UTF-8");
    response.addHeader("Content-Disposition", "attachment; filename=" + excelFileName);
    OutputStream out = null;
    try {
      out = response.getOutputStream();
      excel.createFile(out);
    } finally {
      if (out != null) {
        try {
          out.close();
        } catch (IOException e) {
        }
      }
      checkAccountErrorLogBean = null;
      checkAcountErrorLogForm = null;
    }
    return mapping.findForward(null);
  }
Esempio n. 2
0
  /**
   * @return 获取报表主体内容
   * @throws Exception
   */
  public Object getBodyData() throws Exception {
    String city = form.getCity();
    String service = form.getService();
    String tradetype = form.getTradetype();
    String startdate = Formatter.format(form.getStartdate(), Formatter.YMD);
    String enddate = Formatter.format(form.getEnddate(), Formatter.YMD);

    List monthList = Tools.getAllMonth(startdate, enddate);
    StringBuffer sql = new StringBuffer();
    DBService db = new DBService(Constants.DBNAME_SMP);
    db.setAutoCommit(false);
    Map rsMap = null;
    try {
      for (int i = 0; i < monthList.size(); i++) {
        String str = (String) monthList.get(i);
        sql.append("select b.areaname, c.name, a.tradeplat, a.fee, a.duefee, a.tradenum")
            .append(" from 	em_termtrade_")
            .append(str.substring(2, 6))
            .append(" a, em_area b, em_service c")
            .append(" where a.areacode=b.areacode and a.service=c.code")
            .append(" and a.district in(")
            .append(" ")
            .append(")"); // Area.getDistrictByLoginSMP(user.getLogin())
        if (city != null && !city.equals("")) {
          sql.append(" and b.areaid=").append(city);
        }
        if (service != null && !service.equals("")) {
          sql.append(" and a.service=").append(service);
        }
        if (tradetype != null && !tradetype.equals("")) {
          sql.append(" and a.paytype=").append(tradetype);
        }
        if (!form.getState().equals("")) {
          sql.append(" and a.tradeplat=").append(form.getState());
        }
        if (form.getStartdate().length() != 0) {
          sql.append(" and a.day>='").append(startdate).append("'");
        }
        if (form.getEnddate().length() != 0) {
          sql.append(" and a.day<='").append(enddate).append("'");
        }
        if (i != monthList.size() - 1) {
          sql.append(" union all ");
        }
      }
      sql.append(" into temp aa with no log");
      db.update(sql.toString());

      sql.delete(0, sql.length());
      sql.append("select areaname, name, tradeplat, sum(fee), sum(duefee), sum(tradenum) from aa")
          .append(" group by areaname, name, tradeplat")
          .append(" order by areaname, name, tradeplat");
      List rsList = db.getList(sql.toString());
      db.commit();
      if (rsList.size() > 0) {
        Map tradeStateMap = Common.getTradeStateMap();
        for (int i = 0; i < rsList.size(); i++) {
          String[] str = (String[]) rsList.get(i);
          str[2] = (String) tradeStateMap.get(str[2]);
        }

        rsMap = DataUtil.toNestedStringsListMap(2, rsList);
        rsMap = DataUtil.sumColToRow(rsMap, 1);
        Format[] format = {null, Formatter.D100F2, Formatter.D100F2, Formatter.INT};
        rsMap = Formatter.format(rsMap, format);
      }
    } catch (Exception e) {
      db.rollback();
      throw e;
    } finally {
      if (db != null) db.close();
    }

    return rsMap;
  }