/**
* 事实表和关联报表属于当前传入数组的交叉报表
*
* @param request
* @param tables elements are table.id
* @return elements are ArrayList, first is cxtab id, second is cxtab name
*/
public List getCxtabs(HttpServletRequest request, List<Integer> tables) {
TableManager manager = TableManager.getInstance();
UserWebImpl userWeb =
((UserWebImpl)
WebUtils.getSessionContextManager(request.getSession())
.getActor(nds.util.WebKeys.USER));
StringBuffer sb = new StringBuffer();
for (int i = 0; i < tables.size(); i++) {
// Table t= tables.get(i);
if (i > 0) sb.append(",");
sb.append(tables.get(i));
}
String ts = sb.toString();
try {
Table cxtabTable = manager.getTable("AD_CXTAB");
QueryRequestImpl queryData;
// only pk,dk will be selected, order by ak asc
queryData = QueryEngine.getInstance().createRequest(userWeb.getSession());
queryData.setMainTable(cxtabTable.getId());
queryData.addSelection(cxtabTable.getPrimaryKey().getId());
queryData.addSelection(cxtabTable.getDisplayKey().getId());
Column colOrderNo = cxtabTable.getColumn("orderno");
queryData.setOrderBy(new int[] {colOrderNo.getId()}, true);
queryData.setRange(0, Integer.MAX_VALUE);
Expression expr =
new Expression(
null,
"(AD_CXTAB.AD_TABLE_ID in ("
+ ts
+ ") or exists (select 1 from ad_cxtab_reftable r where r.ad_cxtab_id=AD_CXTAB.id and r.ad_table_id in ("
+ ts
+ ")))",
null);
// set reporttype to "S"
expr =
expr.combine(
new Expression(new ColumnLink("AD_CXTAB.REPORTTYPE"), "=S", null),
SQLCombination.SQL_AND,
null);
expr =
expr.combine(
new Expression(new ColumnLink("AD_CXTAB.ISACTIVE"), "=Y", null),
SQLCombination.SQL_AND,
null);
expr =
expr.combine(
new Expression(new ColumnLink("AD_CXTAB.ISPUBLIC"), "=Y", null),
SQLCombination.SQL_AND,
null);
expr =
expr.combine(
userWeb.getSecurityFilter(cxtabTable.getName(), 1), SQLCombination.SQL_AND, null);
queryData.addParam(expr); // read permission
return QueryEngine.getInstance().doQueryList(queryData.toSQL());
} catch (Throwable t) {
logger.error(
"Fail to load reports for user " + userWeb.getUserId() + " with table ids: " + ts, t);
}
return Collections.EMPTY_LIST;
}
/**
* Perrform process.
*
* @return Message that would be set to process infor summary (no use currently)
* @throws Exception if not successful
*/
protected String doIt() throws Exception {
// load query into cache file directly
// int userId= this.getAD_User_ID();
// User user= SecurityUtils.getUser(userId);
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
QueryEngine engine = QueryEngine.getInstance();
conn = engine.getConnection();
Configurations conf =
(Configurations)
WebUtils.getServletContextManager().getActor(nds.util.WebKeys.CONFIGURATIONS);
String folder = conf.getProperty("ahyy.payment.folder.download", "e:/act/ahyy/download");
File fd = new File(folder);
if (!fd.exists()) fd.mkdirs();
ahyyCode = conf.getProperty("ahyy.payment.code");
if (ahyyCode == null || ahyyCode.length() != 8)
throw new NDSException("Wrong code for bank payment interface");
SimpleDateFormat df = new SimpleDateFormat("yyMMdd");
String ftpFile = "MPAYREP" + ahyyCode + df.format(new java.util.Date());
String file = folder + "/" + ftpFile;
StringBuffer sb = new StringBuffer();
String lineSep = Tools.LINE_SEPARATOR;
try {
// 下载文件
CommandExecuter cmd = new CommandExecuter(folder + "/log/" + ftpFile + ".log");
String exec = conf.getProperty("ahyy.payment.download", "e:/act/bin/download.cmd");
int err = cmd.run(exec + " " + ftpFile);
if (err != 0) {
throw new NDSException("Error(code=" + err + ") when doing " + exec + " " + ftpFile);
}
if (!(new File(file)).exists()) {
throw new NDSException("File not downloaded when doing " + exec + " " + ftpFile);
}
BufferedReader in = new BufferedReader(new FileReader(file));
in.readLine(); // skip first line, which is summary
String line = in.readLine();
pstmt =
conn.prepareStatement(
"update b_pay_sum set state=?, err_code =? where billno=? and state='R'");
while (line != null) {
log.debug(line);
if (line.length() < 34) {
log.debug("line not has length: 34");
}
/*
String billType= line.substring(0,2);
String billNo= line.substring(0, 22);// billno
String amt=line.substring(22,34);
String ack=line.substring(34,36);*/
String billNo = line.substring(0, 20); // billno
String amt = line.substring(20, 32);
String ack = line.substring(32, 34);
if ("00".equals(ack)) {
java.math.BigDecimal bd = new java.math.BigDecimal(amt);
pstmt.setString(1, "Y");
pstmt.setNull(2, java.sql.Types.VARCHAR);
pstmt.setString(3, billNo);
int ret = pstmt.executeUpdate();
if (ret != 1) {
sb.append(line + "(updated " + ret + " lines)").append(lineSep);
}
} else {
pstmt.setString(1, "P");
pstmt.setString(2, ack);
pstmt.setString(3, billNo);
int ret = pstmt.executeUpdate();
if (ret != 1) {
sb.append(line + "(updated " + ret + " lines)").append(lineSep);
}
}
line = in.readLine();
}
in.close();
try {
log.debug("Move " + file + " to " + folder + "/log/" + ftpFile);
(new File(file)).renameTo(new File(folder + "/log/" + ftpFile));
} catch (Throwable t) {
log.error("Fail to move " + file + " to " + folder + "/log/" + ftpFile, t);
}
Vector vet = new Vector();
String log = sb.toString();
this.log.debug(log);
this.addLog(log);
return "完成";
} finally {
if (rs != null)
try {
rs.close();
} catch (Throwable t) {
}
if (pstmt != null)
try {
pstmt.close();
} catch (Throwable t) {
}
if (conn != null)
try {
conn.close();
} catch (Throwable t) {
}
}
}