package com.web.pda.lyt.lytPda.service.internal;
|
|
import com.app.base.data.ApiResponseResult;
|
import com.web.pda.lyt.lytPda.service.PQCSamplingService;
|
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.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(value = "PQCSamplingService")
|
@Transactional(propagation = Propagation.REQUIRED)
|
public class PQCSamplinglmpl extends AppUtills implements PQCSamplingService {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
/**
|
* 获取批号列表据源
|
**/
|
public ApiResponseResult getBatchNum(String factory, String company,String ftype,
|
String procno,String modelno,String keyword) throws Exception {
|
List<Object> list = getBatchNumPrc(factory,company,ftype,procno,modelno,keyword);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
/**
|
* 获取送检单号下拉数据源
|
**/
|
public ApiResponseResult getInspectOrder(String factory, String company,String batchNum) throws Exception {
|
List<Object> list = getInspectOrderPrc(factory,company,batchNum);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
/**
|
* 获取送检的单详细信息
|
*
|
* **/
|
public ApiResponseResult getInspectOrderInfo(String factory, String company, String userNo,
|
String batchNum,String classNo, String order) throws Exception {
|
List<Object> list = getInspectOrderInfoPrc(factory, company, userNo, batchNum, classNo, order);
|
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);
|
}
|
|
/**
|
* 获取检验项目:
|
* 0-检验项目下拉框数据源，
|
* 1-检验项目列表数据源
|
**/
|
public ApiResponseResult getCheckItem(String factory, String company, String pid, String dataType) throws Exception {
|
List<Object> list = getCheckItemPrc(factory,company,pid,dataType);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
/**
|
* 执行修改操作
|
**/
|
public ApiResponseResult modifyPQCValue(String factory, String company, String userNo, String mid,
|
String tableName, String changeName, String changeValue) throws Exception {
|
List<Object> list = modifyPQCValuePrc(factory, company, userNo, mid, tableName, changeName,changeValue);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success();
|
}
|
|
public List modifyPQCValuePrc(String factory, String company, String userNo, String mid,
|
String tableName, String changeName, String changeValue) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_pqc_insert_value_field(?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, userNo);
|
cs.setString(4, mid);
|
cs.setString(5, tableName);
|
cs.setString(6, changeName);
|
cs.setString(7, changeValue);
|
cs.registerOutParameter(8, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(9, java.sql.Types.VARCHAR);// 输出参数
|
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(8));
|
result.add(cs.getString(9));
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
/**
|
* 获取送检单号下拉数据源
|
*/
|
public List getCheckItemPrc(String factory, String company, String pid, String dataType) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_pqc_app_samplebill_item(?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, pid);
|
cs.setString(4, dataType);
|
cs.registerOutParameter(5, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(6, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
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();
|
cs.execute();
|
result.add(cs.getInt(5));
|
result.add(cs.getString(6));
|
if (cs.getString(5).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(7);
|
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 getInspectOrderInfoPrc(String factory, String company, String userNo, String batchNum,
|
String classNo, String order) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_pqc_app_samplebill_insert(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, userNo);
|
cs.setString(4, batchNum);
|
cs.setString(5, classNo);
|
cs.setString(6, order);
|
cs.registerOutParameter(7, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(8, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(9, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(10, -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(7));
|
result.add(cs.getString(8));
|
if (cs.getString(7).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(9);
|
ResultSet rs2 = (ResultSet) cs.getObject(10);
|
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 getInspectOrderPrc(String factory,String company,String batchNum) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_pqc_app_samplebill_inspno(?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, batchNum);
|
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 getBatchNumPrc(String factory,String company,String ftype,
|
String procno,String modelno,String keyword) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call sp_pqc_app_samplebill_lot(?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.setString(3, ftype);
|
cs.setString(4, procno);
|
cs.setString(5, modelno);
|
cs.setString(6, keyword);
|
cs.registerOutParameter(7, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(8, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(9, -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(7));
|
result.add(cs.getString(8));
|
if (cs.getString(7).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(9);
|
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;
|
}
|
}
|
