package com.web.pda.lyt.lytPda.service.internal;
|
|
import com.app.base.data.ApiResponseResult;
|
import com.web.pda.lyt.lytPda.service.SJPatrolService;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.CallableStatementCallback;
|
import org.springframework.jdbc.core.CallableStatementCreator;
|
import org.springframework.jdbc.core.ConnectionCallback;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.sql.*;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
|
@Service
|
@Transactional(rollbackFor = Exception.class)
|
public class SJPatrolServiceImpl implements SJPatrolService {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
|
@Override
|
public ApiResponseResult
|
getBillSelect(String userNo, String taskNo) throws Exception {
|
List<Object> list = getBillSelectPrc(userNo, 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 getDetails(String userNo, String checkNo, int pid) {
|
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, String checkNum) {
|
List<Object> list = checkDetailSaveProc(userNo, checkNo, pid, checkResult, checkDemo, checkNum);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success();
|
}
|
|
@Override
|
public ApiResponseResult checkDetailSaveSJ(String userNo, String checkNo, int pid, String checkResult, String checkDemo, String checkNum) {
|
List<Object> list = checkDetailSaveProcSJ(userNo, checkNo, pid, checkResult, checkDemo, checkNum);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success();
|
}
|
|
private List<Object> checkDetailSaveProcSJ(String userNo, String checkNo, int pid, String checkResult, String checkDemo, String checkNum) {
|
String storedProc = "{call PRC_PAD_SJ_DETAIL_SAVE(?,?,?,?,?,?,?,?)}";
|
CallableStatementCreator csc = con -> {
|
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.setString(6, checkNum);
|
cs.registerOutParameter(7, Types.INTEGER);
|
cs.registerOutParameter(8, Types.VARCHAR);
|
return cs;
|
};
|
|
CallableStatementCallback<List<Object>> cscb = cs -> {
|
List<Object> result = new ArrayList<>();
|
cs.execute();
|
result.add(cs.getInt(7));
|
result.add(cs.getString(8));
|
return result;
|
};
|
return jdbcTemplate.execute(csc, cscb);
|
}
|
|
@Override
|
public ApiResponseResult submitData(String userNo, String checkNo, int type) {
|
List<Object> list = submitDataProc(userNo, checkNo, type);
|
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));
|
return ApiResponseResult.success().data(map);
|
}
|
|
@Override
|
public ApiResponseResult getSJBillSelect(String userNo, String taskNo) {
|
List<Object> list = getSJBillSelectPrc(userNo, 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 getSJDetails(String userNo, String checkNo, int pid) {
|
List<Object> list = getSJDetailsPrc(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);
|
}
|
|
private List<Object> getSJBillSelectPrc(String userNo, String taskNo) {
|
String storedProc = "{call PRC_PAD_SJ_BILL_SELECT(?,?,?,?,?,?)}";
|
return jdbcTemplate.execute((ConnectionCallback<List<Object>>) con -> {
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, taskNo);
|
cs.registerOutParameter(3, Types.INTEGER);
|
cs.registerOutParameter(4, Types.VARCHAR);
|
cs.registerOutParameter(5, -10);
|
cs.registerOutParameter(6, -10);
|
cs.execute();
|
|
List<Map<String, Object>> l = new ArrayList<>();
|
List<Map<String, Object>> l_2 = new ArrayList<>();
|
if (cs.getString(3).equals("0")) {
|
ResultSet rs = (ResultSet) cs.getObject(5);
|
ResultSet rs2 = (ResultSet) cs.getObject(6);
|
l = fitMap(rs);
|
l_2 = fitMap(rs2);
|
}
|
|
List<Object> resultList = new ArrayList<>();
|
resultList.add(cs.getInt(3));
|
resultList.add(cs.getString(4));
|
resultList.add(l);
|
resultList.add(l_2);
|
return resultList;
|
});
|
}
|
|
private List<Object> submitDataProc(String userNo, String checkNo, int type) {
|
String storedProc = "{call PRC_PAD_SJ_FSUBMIT(?,?,?,?,?,?)}";// 调用的sql
|
CallableStatementCreator csc = con -> {
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, checkNo);
|
cs.setInt(3, type);
|
cs.registerOutParameter(4, -10);// 输出参数 返回标识
|
cs.registerOutParameter(5, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(6, Types.VARCHAR);// 输出参数 返回标识
|
return cs;
|
};
|
return getObjects(csc, 5, 6, 4);
|
|
}
|
|
private List<Object> checkDetailSaveProc(String userNo, String checkNo, int pid, String checkResult, String checkDemo, String checkNum) {
|
String storedProc = "{call PRC_PAD_SJ_DETAIL_SAVE(?,?,?,?,?,?,?,?)}";
|
CallableStatementCreator csc = con -> {
|
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.setString(6, checkNum);
|
cs.registerOutParameter(7, Types.INTEGER);
|
cs.registerOutParameter(8, Types.VARCHAR);
|
return cs;
|
};
|
|
CallableStatementCallback<List<Object>> cscb = cs -> {
|
List<Object> result = new ArrayList<>();
|
cs.execute();
|
result.add(cs.getInt(7));
|
result.add(cs.getString(8));
|
return result;
|
};
|
return jdbcTemplate.execute(csc, cscb);
|
}
|
|
private List<Object> getDetailsPrc(String userNo, String checkNo, int pid) {
|
String storedProc = "{call PRC_PAD_SJ_DETAIL_SELECT(?,?,?,?,?,?,?)}";
|
CallableStatementCreator csc = con -> {
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, checkNo);
|
cs.setInt(3, pid);
|
cs.registerOutParameter(4, Types.INTEGER);
|
cs.registerOutParameter(5, Types.VARCHAR);
|
cs.registerOutParameter(6, -10);
|
cs.registerOutParameter(7, -10);
|
return cs;
|
};
|
CallableStatementCallback<List<Object>> cscb = cs -> {
|
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).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) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l_2);
|
}
|
return result;
|
};
|
|
return jdbcTemplate.execute(csc, cscb);
|
|
}
|
|
private List<Object> getSJDetailsPrc(String userNo, String checkNo, int pid) {
|
String storedProc = "{call PRC_PAD_SJ_DETAIL_SELECT(?,?,?,?,?,?,?)}";
|
CallableStatementCreator csc = con -> {
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, checkNo);
|
cs.setInt(3, pid);
|
cs.registerOutParameter(4, Types.INTEGER);
|
cs.registerOutParameter(5, Types.VARCHAR);
|
cs.registerOutParameter(6, -10);
|
cs.registerOutParameter(7, -10);
|
return cs;
|
};
|
CallableStatementCallback<List<Object>> cscb = cs -> {
|
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).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) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l_2);
|
}
|
return result;
|
};
|
|
return jdbcTemplate.execute(csc, cscb);
|
|
}
|
|
private List<Object> getBillSelectPrc(String userNo, String taskNo) {
|
String storedProc = "{call PRC_PAD_SJ_BILL_SELECT(?,?,?,?,?,?)}";
|
return jdbcTemplate.execute((ConnectionCallback<List<Object>>) con -> {
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, taskNo);
|
cs.registerOutParameter(3, Types.INTEGER);
|
cs.registerOutParameter(4, Types.VARCHAR);
|
cs.registerOutParameter(5, -10);
|
cs.registerOutParameter(6, -10);
|
cs.execute();
|
|
List<Map<String, Object>> l = new ArrayList<>();
|
List<Map<String, Object>> l_2 = new ArrayList<>();
|
if (cs.getString(3).equals("0")) {
|
ResultSet rs = (ResultSet) cs.getObject(5);
|
ResultSet rs2 = (ResultSet) cs.getObject(6);
|
l = fitMap(rs);
|
l_2 = fitMap(rs2);
|
}
|
|
List<Object> resultList = new ArrayList<>();
|
resultList.add(cs.getInt(3));
|
resultList.add(cs.getString(4));
|
resultList.add(l);
|
resultList.add(l_2);
|
return resultList;
|
});
|
}
|
|
private List<Map<String, Object>> fitMap(ResultSet rs) throws SQLException {
|
List<Map<String, Object>> list = new ArrayList<>();
|
ResultSetMetaData rsmd = rs.getMetaData();
|
int columnCount = rsmd.getColumnCount();
|
while (rs.next()) {
|
Map<String, Object> row = new HashMap<>();
|
for (int i = 1; i <= columnCount; i++) {
|
String columnName = rsmd.getColumnName(i);
|
Object columnValue = rs.getObject(i);
|
row.put(columnName, columnValue);
|
}
|
list.add(row);
|
}
|
return list;
|
}
|
|
@Override
|
public ApiResponseResult submitDataSJ(String userNo, String checkNo, int type, String conformity) {
|
List<Object> list = submitDataSJProc(userNo, checkNo, type, conformity);
|
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
Map map = new HashMap();
|
map.put("cursor1", list.get(2));
|
return ApiResponseResult.success().data(map);
|
}
|
|
private List<Object> submitDataSJProc(String userNo, String checkNo, int type, String conformity) {
|
String storedProc = "{call PRC_PAD_SJ_FSUBMIT(?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatementCreator csc = con -> {
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, userNo);
|
cs.setString(2, checkNo);
|
cs.setInt(3, type);
|
cs.setString(4, conformity);
|
cs.registerOutParameter(5, -10);// 输出参数 返回标识
|
cs.registerOutParameter(6, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(7, Types.VARCHAR);// 输出参数 返回标识
|
return cs;
|
};
|
return getObjects(csc, 6, 7, 5);
|
|
}
|
|
private List<Object> getObjects(CallableStatementCreator csc, int i, int i2, int i3) {
|
CallableStatementCallback<List<Object>> cscb = cs -> {
|
List<Object> result = new ArrayList<>();
|
List<Map<String, Object>> l = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(i));
|
result.add(cs.getString(i2));
|
if (cs.getString(i).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(i3);
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
return result;
|
};
|
|
return jdbcTemplate.execute(csc, cscb);
|
}
|
}
|
