package com.web.kanban.service.internal;
|
|
import java.io.File;
|
import java.io.IOException;
|
import java.io.InputStream;
|
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.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import org.apache.commons.io.FileUtils;
|
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 com.app.base.data.ApiResponseResult;
|
import com.web.kanban.service.KanbanService;
|
import org.springframework.util.ClassUtils;
|
|
@Service(value = "KanbanService")
|
@Transactional(propagation = Propagation.REQUIRED)
|
public class KanbanImpl implements KanbanService {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
//获取看板目录
|
|
|
@Override
|
public ApiResponseResult getIQCKanbanData(String floor) throws Exception {
|
Map map = new HashMap();
|
List<Object> list_1 = getKanbanDataPrc("Prc_KB_IQCDJ", floor);
|
if (!list_1.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
map.put("TABEL", list_1.get(1).toString());
|
} else {
|
map.put("TABEL", list_1.get(2));
|
}
|
List<Object> list_2 = getKanbanDataPrc("Prc_KB_IQCDJ_HZ", floor);
|
if (!list_2.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
map.put("OK_VALUE", list_2.get(1).toString());
|
} else {
|
map.put("OK_VALUE", list_2.get(2));
|
}
|
return ApiResponseResult.success().data(map);
|
}
|
|
@Override
|
public ApiResponseResult getMaterialAllSetData(String floor) throws Exception {
|
List<Object> list = getKanbanDataPrc("PRC_KB_CK_QT", floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
public ApiResponseResult getZPQTData2(String floor) throws Exception {
|
List<Object> list = getKanbanDataPrc("PRC_KB_ZPBLQK", floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
public ApiResponseResult getZhiJaData(String floor) throws Exception {
|
List<Object> list = getKanbanDataPrc("PRC_KB_BZBLQK", floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
public ApiResponseResult getZhiJaBlData(String floor) throws Exception {
|
List<Object> list = getKanbanDataPrc("PRC_KB_ZhiJaBLQK", floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
//包装
|
public ApiResponseResult getBZData(String floor) throws Exception {
|
List<Object> list = getKanbanDataPrc("PRC_KB_CK_QT", floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
public ApiResponseResult getBZBlData(String floor) throws Exception {
|
List<Object> list = getKanbanDataPrc("PRC_KB_BZBLQK", floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
public ApiResponseResult getZhiJaQTData(String floor) throws Exception {
|
List<Object> list = getKanbanDataPrc("PRC_KB_ZhiJaQT", floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
@Override
|
public ApiResponseResult getZPQTData(String floor) throws Exception {
|
List<Object> list = getKanbanDataPrc("PRC_KB_ZPQT", floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
@Override
|
public ApiResponseResult getLineHeadData(String floor) throws Exception {
|
String[] prcList = {"PRC_KB_Line_A", "PRC_KB_Line_B", "PRC_KB_Line_C", "PRC_KB_Line_D", "PRC_KB_Line_E", "PRC_KB_LINE_F", "PRC_KB_AD_YCB_LINE"};
|
//String[] prcList = { "PRC_KB_Line_A" };
|
|
Map map = new HashMap();
|
for (int i = 0; i < prcList.length; i++) {
|
List<Object> list = getKanbanDataPrc(prcList[i], floor);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
map.put("DATA_" + i, list.get(1).toString());
|
} else {
|
map.put("DATA_" + i, list.get(2));
|
}
|
}
|
return ApiResponseResult.success().data(map);
|
}
|
|
public Boolean getUserImage(String code) throws Exception {
|
try {
|
getUserImg(code);
|
return true;
|
} catch (Exception e) {
|
e.printStackTrace();
|
return false;
|
}
|
}
|
|
public InputStream getUserImg(String fcode) {
|
String sql = "select u.EXP_FIELD3 from sys_user u where u.fcode=?";
|
InputStream inn = null;
|
//将结果集数据行中的数据抽取到forum对象中
|
jdbcTemplate.query(sql, new Object[]{fcode}, new RowCallbackHandler() {
|
public void processRow(ResultSet rs) throws SQLException {
|
InputStream in = rs.getBinaryStream("EXP_FIELD3");
|
saveImg(in, fcode);
|
}
|
});
|
return inn;
|
}
|
|
public void saveImg(InputStream inputStream, String fcode) {
|
String location = System.getProperty("user.dir");
|
//获取项目classes/static的地址
|
//String staticPath = ClassUtils.getDefaultClassLoader().getResource("static").getPath();
|
System.out.println(File.separator);
|
// 图片保存路径-测试地址
|
//String savePath = new String("src"+ File.separator+"main"+ File.separator+"resources"+ File.separator+"static"+ File.separator+"downImages" + File.separator+fcode+ ".png");
|
|
//获取项目classes/static的地址-tomcat正式使用地址
|
String staticPath = ClassUtils.getDefaultClassLoader().getResource("static").getPath();
|
String fileName = fcode + ".png"; //获取文件名
|
|
// 图片存储目录及图片名称
|
String url_path = "downImages" + File.separator + fileName;
|
//图片保存路径
|
String savePath = staticPath + File.separator + url_path;
|
|
System.out.println("图片保存地址：" + savePath);
|
|
File saveFile = new File(savePath);
|
|
try {
|
FileUtils.copyInputStreamToFile(inputStream, saveFile);
|
} catch (IOException e) {
|
System.out.println(e.toString());
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
}
|
|
|
public List getKanbanDataPrc(String prcName, String floor) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call " + prcName + "(?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, "1");
|
cs.setString(2, floor);
|
cs.registerOutParameter(3, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(4, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(5, -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(3));
|
result.add(cs.getString(4));
|
if (cs.getString(3).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(5);
|
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 ApiResponseResult getKanbanMenu(String factory, String company) throws Exception {
|
List<Object> list = getKanbanMenuPrc(factory, company);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
//以往记录-未使用（不确定，忘了）
|
public ApiResponseResult getKanbanData(String kanbanNo, String lingNo, String dataType) throws Exception {
|
List<Object> list = getKanbanDataPrc(kanbanNo, lingNo);
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
return ApiResponseResult.failure(list.get(1).toString());
|
}
|
return ApiResponseResult.success().data(list.get(2));
|
}
|
|
/**
|
* 模板调用的存储过程 kanbanNo:看板号，1-表示车间看板 lineNo:线号，没有线号时可为空 frequency:表示调用过程总次数
|
* date:2021-10-18
|
**/
|
public ApiResponseResult getTemplateData(String kanbanNo, String lineNo, int frequency) throws Exception {
|
//Map<String, Object> map = new LinkedHashMap<String, Object>();
|
List<Object> resultList = new ArrayList<>();
|
for (int i = 0; i < frequency; i++) {
|
List<Object> list = getKanbanDataPrc(kanbanNo, lineNo);
|
Map<String, Object> m = new LinkedHashMap<String, Object>();
|
|
if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
|
m.put("result", list.get(0));
|
m.put("msg", list.get(1).toString());
|
m.put("data", "");
|
} else {
|
m.put("result", list.get(0));
|
m.put("msg", "");
|
m.put("data", list.get(2));
|
}
|
//map.put("DATA_" + i, m);
|
resultList.add(m);
|
}
|
return ApiResponseResult.success().data(resultList);
|
}
|
|
public List getKanbanMenuPrc(String factory, String company) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call PRC_APP_KANBAN_MENU(?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, company);
|
cs.registerOutParameter(3, java.sql.Types.INTEGER);// 输出参数 返回标识
|
cs.registerOutParameter(4, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(5, -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(3));
|
result.add(cs.getString(4));
|
if (cs.getString(3).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(5);
|
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;
|
}
|
|
|
private 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 LinkedHashMap<String, Object>();
|
for (String columnName : columnNames) {
|
map.put(columnName, rs.getString(columnName));
|
}
|
list.add(map);
|
}
|
}
|
return list;
|
}
|
}
|
