/**
* 从R3获取数据写到WMS数据库 这里只限制输入参数只能为一个内表的字段,
*
* @param functionTemplateName
* 函数名
* @param jcoCode
* @param tableNames
* 内表参数名字数组
* @param culumns
* 要获取的内表参数的字段数组
* @param Connection
* @return
*/
public Map<String, String> importDateFromR3ToWms(
String functionTemplateName, String jcoCode, String[] tableNames,
String[][] culumns, String[][] fieldInputs, String tableInput,
String[][] tableInputParams,String[][] otherInputParams) {
Connection con = this.getConnection();
Map<String, String> result = new HashMap<String, String>();
JCoDestination destination;
JCoRepository repository = null;
try {
destination = JCoDestinationManager
.getDestination(WMSABAP_AS_POOLED);
} catch (Exception e) {
logger.error("Get sap connection pool error:can't find "
+ WMSABAP_AS_POOLED + ".jcoDestination file .");
logger.error(e.toString());
result.put("STATUS", "F");
result.put("ERRORTP", "1");
result.put("ERROR", e.toString());
return result;
}
try {
repository = destination.getRepository();
} catch (Exception e) {
logger.error("Get sap Repository error!");
logger.error(e.toString());
result.put("STATUS", "F");
result.put("ERRORTP", "2");
result.put("ERROR", e.toString());
return result;
}
JCoFunction function;
try {
function = repository.getFunction(functionTemplateName);
} catch (Exception e) {
logger.error(functionTemplateName + " not found in SAP.");
logger.error(e.toString());
result.put("STATUS", "F");
result.put("ERRORTP", "3");
result.put("ERROR", e.toString());
return result;
}
int inputSize = 0;
int fieldInputSize = 0;
int tableInputParamsSize = 0;
if (fieldInputs != null && fieldInputs.length != 0) {
fieldInputSize=fieldInputs.length;
inputSize = inputSize + fieldInputs.length;
JCoParameterList input = function.getImportParameterList();
for (int i = 0; i < fieldInputs.length; i++) {
input.setValue(fieldInputs[i][0], fieldInputs[i][1]);
}
}
if (tableInput != null && tableInputParams != null
&& tableInputParams.length > 0) {
tableInputParamsSize=tableInputParams.length;
inputSize = inputSize + tableInputParams.length;
JCoParameterList inputResultParams = function
.getTableParameterList();
JCoTable inputTable = inputResultParams.getTable(tableInput);
inputTable.appendRow();
for (int i = 0; i < tableInputParams.length; i++) {
inputTable.setValue(tableInputParams[i][0],
tableInputParams[i][1]);
}
}
if (otherInputParams != null && otherInputParams.length != 0) {
inputSize = inputSize + otherInputParams.length;
}
try {
logger.debug("开始从R3查询获取数据......");
function.execute(destination);
logger.debug("数据获取完毕......");
} catch (Exception e) {
logger.error("execute " + functionTemplateName + " error.");
logger.error(e.toString());
result.put("STATUS", "F");
result.put("ERRORTP", "4");
result.put("ERROR", e.toString());
return result;
}
JCoParameterList resultParams = function.getTableParameterList();
JCoTable[] statList = new JCoTable[tableNames.length];
int[] statTotal = new int[tableNames.length];// 记录从SAP获取的表的记录数
int[] statlistRows = new int[tableNames.length];// 记录从SAP获取的表的记录数
for (int i = 0; i < statList.length; i++) {
statList[i] = resultParams.getTable(tableNames[i]);
statlistRows[i] = statList[i].getNumRows();
logger.debug("statlistRows=" + statlistRows);
}
int jcoCodetRank = geJcoCodetRank(con, jcoCode);
String[] sql = new String[tableNames.length];
String[] inputValues = null;
if (inputSize > 0) {
inputValues = new String[inputSize];
for (int i = 0; i < inputSize; i++) {
logger.debug("i is "+i);
logger.debug("inputSize is "+inputSize);
logger.debug("fieldInputSize is "+fieldInputSize);
logger.debug("fieldInputSize+tableInputParamsSize is "+fieldInputSize+tableInputParamsSize);
if (i < fieldInputSize) {
inputValues[i] = fieldInputs[i][1];
} else if(i < fieldInputSize+tableInputParamsSize){
inputValues[i] = tableInputParams[i - fieldInputSize][1];
}else {
inputValues[i] = otherInputParams[i -fieldInputSize-tableInputParamsSize][1];
}
}
}
for (int i = 0; i < sql.length; i++) {
sql[i] = this.makeJcoSql(jcoCode, jcoCodetRank, tableNames[i],
TABLE_JCO_DATE_RECEIVE, culumns[i].length, inputValues);
logger.debug("sql["+i+"] is " + sql[i]);
}
int[] batchNums = new int[tableNames.length];
PreparedStatement[] pss = new PreparedStatement[tableNames.length];
boolean commit;
try {
commit = con.getAutoCommit();
for (int i = 0; i < tableNames.length; i++) {
con.setAutoCommit(false);
pss[i] = con.prepareStatement(sql[i]);
batchNums[i] = pss[i].getFetchSize();
pss[i].setFetchSize(SQLFETCCHSIZE);
}
} catch (SQLException e1) {
logger.error("DB Error:", e1);
result.put("STATUS", "F");
result.put("ERRORTP", "5");
result.put("ERROR", e1.toString());
return result;
}
for (int i = 0; i < tableNames.length; i++) {
logger.debug("开始向WMS插入数据数据......");
if (statlistRows[i] > 0) {
do {
statTotal[i]++;
try {
this.excutesetSqlPs(pss[i], culumns[i], statList[i]);
pss[i].addBatch();
if (statTotal[i] % batchNums[i] == 0) {// 执行物理批量插入
pss[i].executeBatch();
// con.commit();
}
} catch (SQLException e) {
logger.error("insert error:", e);
result.put("STATUS", "F");
result.put("ERRORTP", "5");
result.put("ERROR", e.toString());
return result;
}
} while (statList[i].nextRow());
}
}
try {
logger.debug("tableNames.length is " + tableNames.length);
logger.debug("pss.length is " + pss.length);
for (int i = 0; i < tableNames.length; i++) {
logger.debug("i is " + i);
pss[i].executeBatch();
}
con.commit();
logger.debug("完成向WMS插入数据数据......");
con.setAutoCommit(commit);
} catch (SQLException e) {
logger.debug(e.toString());
result.put("STATUS", "F");
result.put("ERRORTP", "5");
result.put("ERROR", e.toString());
} finally {
for (int i = 0; i < tableNames.length; i++) {
DbUtils.closeQuietly(pss[i]);
}
DbUtils.closeQuietly(con);
}
String totalResult = "";
for (int i = 1; i <= tableNames.length; i++) {
if (i == tableNames.length) {
totalResult = totalResult + statTotal[i - 1];
} else {
totalResult = statTotal[i - 1] + ",";
}
}
result.put("STATUS", "S");
result.put("ERRORTP", "0");
result.put("TOTATL", totalResult);
return result;
}