/**
* 导出对账日志明细
*
* @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);
}
/**
* @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;
}