package com.web.jhsop.service.internal;
|
|
import com.app.base.data.ApiResponseResult;
|
import com.web.ftp.service.FileCheckService;
|
import com.web.jhsop.service.JhSopDataService;
|
|
import org.apache.commons.lang3.StringUtils;
|
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.jdbc.core.RowCallbackHandler;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Propagation;
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.util.CollectionUtils;
|
|
import java.sql.*;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
@Service
|
@Transactional(propagation = Propagation.REQUIRED)
|
|
public class JhSopDataServiceImpl implements JhSopDataService {
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
@Autowired
|
private FileCheckService fileCheckService;
|
|
@Override
|
public ApiResponseResult getManufactoryList() throws Exception {
|
try {
|
|
List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call prc_sop_task_re" + "(?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, "100");
|
cs.setString(2, "100");
|
cs.setString(3, "获取工单");
|
cs.registerOutParameter(4, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(5, Types.VARCHAR);// 输出参数 错误信息
|
cs.registerOutParameter(6, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
@Override
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<Object>();
|
cs.execute();
|
result.add(cs.getString(4));
|
result.add(cs.getString(5));
|
if (cs.getString(4).toString().endsWith("0")) {
|
//游标处理
|
ResultSet rs = (ResultSet) cs.getObject(6);
|
List l = new ArrayList();
|
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
return result;
|
}
|
});
|
if (("0").equals(resultList.get(0))) {
|
return ApiResponseResult.success().data(resultList.get(2));
|
} else {
|
return ApiResponseResult.failure().message(resultList.get(1).toString());
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
return ApiResponseResult.failure().message(e.getMessage());
|
}
|
}
|
|
@Override
|
public ApiResponseResult getProcessLine(String workOrder) throws Exception {
|
try {
|
if (StringUtils.isEmpty(workOrder)) {
|
return ApiResponseResult.failure("请选择工单号");
|
}
|
List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_SOP_PROC_RE" + "(?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, "100");
|
cs.setString(2, "100");
|
cs.setString(3, workOrder);
|
cs.registerOutParameter(4, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(5, Types.VARCHAR);// 输出参数 错误信息
|
cs.registerOutParameter(6, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
@Override
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<Object>();
|
cs.execute();
|
result.add(cs.getString(4));
|
result.add(cs.getString(5));
|
if (cs.getString(4).toString().endsWith("0")) {
|
//游标处理
|
ResultSet rs = (ResultSet) cs.getObject(6);
|
List l = new ArrayList();
|
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
return result;
|
}
|
});
|
if (("0").equals(resultList.get(0))) {
|
return ApiResponseResult.success().data(resultList.get(2));
|
} else {
|
return ApiResponseResult.failure().message(resultList.get(1).toString());
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
return ApiResponseResult.failure().message(e.getMessage());
|
}
|
}
|
|
@Override
|
public ApiResponseResult getFile(String manufactory, String processLIne) {
|
try {
|
if (StringUtils.isEmpty(manufactory)) {
|
return ApiResponseResult.failure("请选择工单号");
|
}
|
if (StringUtils.isEmpty(processLIne)) {
|
return ApiResponseResult.failure("请选择工序线体");
|
}
|
List<Object> resultList = (List<Object>) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_SOP_PDF_RE" + "(?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, "100");
|
cs.setString(2, "100");
|
cs.setString(3, manufactory);
|
cs.setString(4, processLIne);
|
cs.registerOutParameter(5, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(6, Types.VARCHAR);// 输出参数 错误信息
|
cs.registerOutParameter(7, -10);// 输出参数 追溯数据
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
@Override
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<Object>();
|
cs.execute();
|
result.add(cs.getString(5)+"");
|
result.add(cs.getString(6));
|
if (cs.getString(5).toString().endsWith("0")) {
|
//游标处理
|
ResultSet rs = (ResultSet) cs.getObject(7);
|
List l = new ArrayList();
|
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
return result;
|
}
|
});
|
if (("0").equals(resultList.get(0))) {
|
ArrayList<HashMap> arrayList = (ArrayList<HashMap>) resultList.get(2);
|
if (CollectionUtils.isEmpty(arrayList)){
|
return ApiResponseResult.failure().message("没有此文件信息");
|
}
|
// Map<String,String> ftpConfig=new HashMap<>();
|
// System.out.println("arrayList.get(0)"+arrayList.get(0));
|
// Object pathObj = arrayList.get(0).get("路径");
|
// Object rootObj = arrayList.get(0).get("根目录");
|
// if(pathObj!=null){
|
// ftpConfig.put("path",arrayList.get(0).get("路径").toString());
|
// }
|
// if(rootObj!=null){
|
// ftpConfig.put("root",arrayList.get(0).get("根目录").toString());
|
// }
|
// ftpConfig.put("account",arrayList.get(0).get("账号").toString());
|
// ftpConfig.put("password",arrayList.get(0).get("密码").toString());
|
//// ftpConfig.put("fileName",arrayList.get(0).get("文件名").toString());
|
// ftpConfig.put("url",arrayList.get(0).get("URL").toString());
|
// ftpConfig.put("nums",arrayList.get(0).get("顺序号").toString());
|
// ApiResponseResult download = fileCheckService.onlineViewPdf(arrayList.get(0).get("URL").toString(), 21, arrayList.get(0).get("账号").toString(), arrayList.get(0).get("密码").toString(), "/20221208", "/20221208110344_87.pdf","/task",response);
|
// ApiResponseResult download = fileCheckService.onlineViewPdf(arrayList.get(0).get("URL").toString(), 21, arrayList.get(0).get("账号").toString(), arrayList.get(0).get("密码").toString(), path, arrayList.get(0).get("文件名").toString(),arrayList.get(0).get("根目录").toString(),response);
|
// ApiResponseResult download = fileCheckService.onlineViewPdf(arrayList.get(0).get("URL").toString(), 21, arrayList.get(0).get("账号").toString(), arrayList.get(0).get("密码").toString(), "/20221209", "/20221209102241_718.xlsx",response);
|
return ApiResponseResult.success().data(resultList.get(2));
|
|
} else {
|
return ApiResponseResult.failure().message(resultList.get(1).toString());
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
return ApiResponseResult.failure().message(e.getMessage());
|
}
|
}
|
|
@Override
|
public ApiResponseResult scanProduct(String line, String proc, String taskNo, String user, String sn, String procOrder, String procName) {
|
try {
|
// if (StringUtils.isEmpty(line)||StringUtils.isEmpty(proc)||StringUtils.isEmpty(taskNo)||StringUtils.isEmpty(user)||StringUtils.isEmpty(sn)||StringUtils.isEmpty(procOrder)) {
|
// return ApiResponseResult.failure("请补全信息");
|
// }
|
List<Object> list = (List<Object>)jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call prc_linchpin_sn" + "(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, "");
|
cs.setString(2, line);
|
cs.setString(3, proc);
|
cs.setString(4, taskNo);
|
cs.setString(5, user);
|
cs.setString(6, sn);
|
cs.setString(7, procOrder);
|
cs.registerOutParameter(8, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(9, Types.VARCHAR);// 输出参数 错误信息
|
cs.registerOutParameter(10, Types.VARCHAR);// 输出参数 空值
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
@Override
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<Object>();
|
cs.execute();
|
result.add(cs.getString(8));
|
result.add(cs.getString(9));
|
return result;
|
}
|
});
|
if(!("0").equals(list.get(0))){
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
if(((String) list.get(1)).equalsIgnoreCase("GOTO:BTN1")){
|
//执行prc_barcode_collect_01
|
List<Object> resList = executePrcBarcode(line,proc,taskNo,user,sn,procOrder,procName);
|
if (("0").equals(resList.get(0))) {
|
Map<String,Object> resMap = new HashMap<>();
|
resMap.put("operate","GOTO:BTN1");
|
resMap.put("data",resList.get(1));
|
return ApiResponseResult.success().data(resMap);
|
} else {
|
return ApiResponseResult.failure(resList.get(1).toString());
|
}
|
}else if(((String) list.get(1)).equalsIgnoreCase("GOTO:BARCODE")){
|
//执行prc_get_keyitem 返回游标
|
List<Object> resultList = executeGetKeyItem(taskNo,proc,sn);
|
if (("0").equals(resultList.get(0))) {
|
Map<String,Object> resMap = new HashMap<>();
|
resMap.put("operate","GOTO:BARCODE");
|
resMap.put("data",resultList.get(2));
|
return ApiResponseResult.success().data(resMap);
|
} else {
|
return ApiResponseResult.failure(resultList.get(1).toString());
|
}
|
}
|
else {
|
return null;
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
return ApiResponseResult.failure().message(e.getMessage());
|
}
|
}
|
|
@Override
|
public ApiResponseResult scanMaterial(String line, String proc, String user, String taskNo, String barcode, String sn, String procOrder, String procName) {
|
try {
|
if (StringUtils.isEmpty(line)||StringUtils.isEmpty(proc)||StringUtils.isEmpty(taskNo)||StringUtils.isEmpty(user)||StringUtils.isEmpty(sn)||StringUtils.isEmpty(procOrder)) {
|
return ApiResponseResult.failure("请补全信息");
|
}
|
List<Object> list = (List<Object>)jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_LINCHPIN_ITEM" + "(?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, line);
|
cs.setString(2, proc);
|
cs.setString(3, "");
|
cs.setString(4, "");
|
cs.setString(5, user);
|
cs.setString(6, taskNo);
|
cs.setString(7, barcode);
|
cs.setString(8, sn);
|
cs.registerOutParameter(9, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(10, Types.VARCHAR);// 输出参数 错误信息
|
cs.registerOutParameter(11, Types.VARCHAR);//
|
cs.registerOutParameter(12, Types.VARCHAR);//
|
cs.registerOutParameter(13, Types.VARCHAR);//
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
@Override
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<Object>();
|
cs.execute();
|
result.add(cs.getString(9));
|
result.add(cs.getString(10));
|
return result;
|
}
|
});
|
if(!("0").equals(list.get(0))){
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
if(((String) list.get(1)).equalsIgnoreCase("GOTO:BTN1")){
|
//执行prc_barcode_collect_01
|
List<Object> resList = executePrcBarcode(line,proc,taskNo,user,sn,procOrder,procName);
|
if (("0").equals(resList.get(0))) {
|
Map<String,Object> resMap = new HashMap<>();
|
resMap.put("operate","GOTO:BTN1");
|
return ApiResponseResult.success().data(resMap);
|
} else {
|
return ApiResponseResult.failure(resList.get(1).toString());
|
}
|
}else if(((String) list.get(1)).equalsIgnoreCase("GOTO:BARCODE")){
|
//执行prc_get_keyitem 返回游标
|
List<Object> resultList = executeGetKeyItem(taskNo,proc,sn);
|
if (("0").equals(resultList.get(0))) {
|
Map<String,Object> resMap = new HashMap<>();
|
resMap.put("operate","GOTO:BARCODE");
|
resMap.put("data",resultList.get(2));
|
return ApiResponseResult.success().data(resMap);
|
} else {
|
return ApiResponseResult.failure(resultList.get(1).toString());
|
}
|
}
|
else {
|
return null;
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
return ApiResponseResult.failure().message(e.getMessage());
|
}
|
}
|
|
@Override
|
public ApiResponseResult queryPass(String proc, String taskNo, String num) {
|
final List result = new ArrayList();
|
String sql = "SELECT COUNT(1) AS sumPass FROM MES_BARIF A WHERE A.IF001='"+
|
proc + "'"
|
+" AND A.IF005='"
|
+ taskNo + "'"
|
+" AND A.if008='"
|
+ num + "'"
|
+ " AND A.IF010='合格' AND A.IF012='Y'";
|
jdbcTemplate.query(sql, new RowCallbackHandler() {
|
@Override
|
public void processRow(ResultSet resultSet) throws SQLException {
|
result.add(resultSet.getString("sumPass").toString());
|
System.out.println(resultSet);
|
}
|
});
|
System.out.println(result.get(0));
|
return ApiResponseResult.success().data(result.get(0));
|
}
|
|
@Override
|
public ApiResponseResult queryPerson(String user) {
|
final List result = new ArrayList();
|
String sql = "select Fname as name from SYS_USER where fcode = '" + user + "'";
|
jdbcTemplate.query(sql, new RowCallbackHandler() {
|
@Override
|
public void processRow(ResultSet resultSet) throws SQLException {
|
result.add(resultSet.getString("name").toString());
|
System.out.println(resultSet);
|
}
|
});
|
return ApiResponseResult.success().data(result.get(0));
|
}
|
|
|
/**
|
* 游标处理
|
*/
|
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;
|
}
|
public List<Object> executePrcBarcode(String line, String proc, String taskNo, String user, String sn, String procOrder,String procName){
|
List<Object> resList = (List<Object>)jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call prc_barcode_collect_01" + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, "");
|
cs.setString(2, taskNo);
|
cs.setString(3, line);
|
cs.setString(4, procOrder);
|
cs.setString(5, sn);
|
cs.setString(6, "0");
|
cs.setString(7, user);
|
cs.setString(8, proc);
|
cs.setString(9, procName);//工序名称
|
cs.setString(10, "");
|
cs.setString(11, "");
|
cs.setString(12, "");
|
cs.setString(13, "");
|
cs.registerOutParameter(14, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(15, Types.VARCHAR);// 输出参数 返回标识
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
@Override
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<Object>();
|
cs.execute();
|
result.add(cs.getString(14));
|
result.add(cs.getString(15));
|
return result;
|
}
|
});
|
return resList;
|
}
|
public List<Object> executeGetKeyItem(String taskNo,String proc,String sn){
|
List<Object> resultList = (List<Object>)jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call prc_get_keyitem" + "(?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, taskNo);
|
cs.setString(2, proc);
|
cs.setString(3, sn);
|
cs.registerOutParameter(4, Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(5, Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(6, -10);// 输出参数 返回标识
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
@Override
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<Object>();
|
cs.execute();
|
result.add(cs.getString(4));
|
result.add(cs.getString(5));
|
if (cs.getString(4).toString().endsWith("0")) {
|
//游标处理
|
ResultSet rs = (ResultSet) cs.getObject(6);
|
List l = new ArrayList();
|
|
try {
|
l = fitMap(rs);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
}
|
return result;
|
}
|
});
|
return resultList;
|
}
|
}
|
