package com.web.pda.lyt.lytPda.service.internal;
|
|
import com.app.base.data.ApiResponseResult;
|
import com.web.pda.lyt.lytPda.service.HoPatrolCheckService;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.jdbc.core.CallableStatementCallback;
|
import org.springframework.jdbc.core.CallableStatementCreator;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Propagation;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.io.Console;
|
import java.sql.CallableStatement;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
@Service
|
@Transactional(propagation = Propagation.REQUIRED)
|
public class HoPatrolCheckImpl implements HoPatrolCheckService {
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
@Override
|
public ApiResponseResult getLineNo(String userNo) throws Exception {
|
List<Object> list = getLineNooPrc(userNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
@Override
|
public ApiResponseResult getProccInfoT(String userNo) throws Exception {
|
List<Object> list = getProccPrc(userNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
public List getProccPrc(String userNo) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_Procc_INFO(?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.registerOutParameter(2, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(3, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(4, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(2));
|
result.add(cs.getString(3));
|
if (cs.getString(2).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(4);
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
|
|
@Override
|
public ApiResponseResult getLineSelect(String userNo, String lineNo, String taskNo) throws Exception {
|
List<Object> list = getLineSelectPrc(userNo,lineNo,taskNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
@Override
|
public ApiResponseResult getBillSelect(String userNo, String lineNo, String taskNo) throws Exception {
|
List<Object> list = getBillSelectPrc(userNo,lineNo,taskNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));
|
map.put("cursor2", list.get(3));
|
return ApiResponseResult.success().data(map);
|
}
|
|
@Override
|
public ApiResponseResult getprocessBillSelect(String userNo, String lineNo, String taskNo) throws Exception {
|
List<Object> list = getprocessBillSelectPrc(userNo,lineNo,taskNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));
|
map.put("cursor2", list.get(3));
|
return ApiResponseResult.success().data(map);
|
}
|
|
public List getprocessBillSelectPrc(String userNo, String lineNo, String taskNo) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_process_SELECT(?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, lineNo);
|
cs.setString(3, taskNo);
|
cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(6, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(7, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
List<Map<String, Object>> l_2 = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(4));
|
result.add(cs.getString(5));
|
if (cs.getString(4).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(6);
|
ResultSet rs2 = (ResultSet) cs.getObject(7);
|
try {
|
l = fitMap(rs);
|
l_2 = fitMap(rs2);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l_2);
|
}
|
System.out.println(l);
|
System.out.println(l_2);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
@Override
|
public ApiResponseResult getDetails(String userNo, String checkNo, int pid) throws Exception {
|
List<Object> list = getDetailsPrc(userNo,checkNo,pid);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));
|
map.put("cursor2", list.get(3));
|
return ApiResponseResult.success().data(map);
|
}
|
|
@Override
|
public ApiResponseResult checkDetailSave(String userNo, String checkNo, int pid, String checkResult, String checkDemo) throws Exception {
|
List<Object> list = checkDetailSaveProc(userNo, checkNo, pid, checkResult, checkDemo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success();
|
}
|
|
@Override
|
public ApiResponseResult checkDetailDel(String userNo, String checkNo, int pid, String checkResult, String checkDemo, String type) throws Exception {
|
List<Object> list = checkDetailDelProc(userNo, checkNo, pid, checkResult, checkDemo, type);
|
if (!list.get(0).toString().equals("0")) { // 存储过程调用失败
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success();
|
}
|
|
|
@Override
|
public ApiResponseResult checkDetailPass(String userNo, String checkNo) throws Exception {
|
List<Object> list = checkDetailPassProc(userNo, checkNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success();
|
}
|
|
@Override
|
public ApiResponseResult submitData(String userNo, String checkNo, int type) throws Exception {
|
List<Object> list = submitDataProc(userNo, checkNo,type);
|
if (!list.get(1).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(2).toString());
|
}
|
return ApiResponseResult.success(list.get(0).toString());
|
}
|
|
public List getLineNooPrc(String userNo) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_LINE_INFO(?,?,?,?)}";// 调用的sql
|
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.registerOutParameter(2, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(3, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(4, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(2));
|
result.add(cs.getString(3));
|
if (cs.getString(2).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(4);
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
public List getLineSelectPrc(String userNo, String lineNo, String taskNo) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_LINE_SELECT(?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, lineNo);
|
cs.setString(3, taskNo);
|
cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(6, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(4));
|
result.add(cs.getString(5));
|
if (cs.getString(4).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(6);
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
public List getBillSelectPrc(String userNo, String lineNo, String taskNo) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_BILL_SELECT(?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, lineNo);
|
cs.setString(3, taskNo);
|
cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(6, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(7, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
List<Map<String, Object>> l_2 = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(4));
|
result.add(cs.getString(5));
|
if (cs.getString(4).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(6);
|
ResultSet rs2 = (ResultSet) cs.getObject(7);
|
try {
|
l = fitMap(rs);
|
l_2 = fitMap(rs2);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l_2);
|
}
|
System.out.println(l);
|
System.out.println(l_2);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
public List getDetailsPrc(String userNo, String checkNo, int pid) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_DETAIL_SELECT(?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, checkNo);
|
cs.setInt(3, pid);
|
cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(6, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(7, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
List<Map<String, Object>> l_2 = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(4));
|
result.add(cs.getString(5));
|
if (cs.getString(4).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(6);
|
ResultSet rs2 = (ResultSet) cs.getObject(7);
|
try {
|
l = fitMap(rs);
|
l_2 = fitMap(rs2);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l_2);
|
}
|
System.out.println(l);
|
System.out.println(l_2);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
|
public List checkDetailSaveProc(String userNo, String checkNo, int pid, String checkResult, String checkDemo)
|
throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_DETAIL_SAVE(?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2,checkNo);
|
cs.setInt(3, pid);
|
cs.setString(4, checkResult);
|
cs.setString(5, checkDemo);
|
cs.registerOutParameter(6, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(7, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
cs.execute();
|
result.add(cs.getInt(6));
|
result.add(cs.getString(7));
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
public List checkDetailDelProc(String userNo, String checkNo, int pid, String checkResult, String checkDemo, String type)
|
throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_DETAIL_DEL(?,?,?,?,?,?)}"; // 修改存储过程，增加 type 参数
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, checkNo);
|
cs.setInt(3, pid);
|
cs.setString(4, type); // 传入 type，区分不同的检验类型
|
cs.registerOutParameter(5, java.sql.Types.INTEGER); // 输出参数：状态码
|
cs.registerOutParameter(6, java.sql.Types.VARCHAR); // 输出参数：错误信息
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
cs.execute();
|
result.add(cs.getInt(5)); // 获取状态码
|
result.add(cs.getString(6)); // 获取错误信息
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
|
public List checkDetailPassProc(String userNo, String checkNo)
|
throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_DETAIL_ALLOK(?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2,checkNo);
|
cs.registerOutParameter(3, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(4, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
cs.execute();
|
result.add(cs.getInt(3));
|
result.add(cs.getString(4));
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
public List submitDataProc(String userNo, String checkNo, int type)
|
throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_PAD_PQC_FSUBMIT(?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2,checkNo);
|
cs.setInt(3,type);
|
cs.registerOutParameter(4, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(5, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(6, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
cs.execute();
|
result.add(cs.getString(4));
|
result.add(cs.getInt(5));
|
result.add(cs.getString(6));
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
public List<Map<String, Object>> fitMap(ResultSet rs) throws Exception {
|
List<Map<String, Object>> list = new ArrayList<>();
|
if (null != rs) {
|
Map<String, Object> map;
|
int colNum = rs.getMetaData().getColumnCount();
|
List<String> columnNames = new ArrayList<String>();
|
for (int i = 1; i <= colNum; i++) {
|
columnNames.add(rs.getMetaData().getColumnName(i));
|
}
|
while (rs.next()) {
|
map = new HashMap<String, Object>();
|
for (String columnName : columnNames) {
|
map.put(columnName, rs.getString(columnName));
|
}
|
list.add(map);
|
}
|
}
|
return list;
|
}
|
}
|
