package com.web.kanban.service.internal;
|
|
import com.utils.EasyExcelUtils;
|
import com.utils.ExcelExport;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
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.web.bind.annotation.GetMapping;
|
|
import javax.servlet.http.HttpServletResponse;
|
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;
|
|
public class HgReportPrcUtils {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
|
/**
|
* 2023-03-25 获取仓库待入库看板
|
**/
|
public List getPRC_KB_DEPOTS_Prc( String prc_name,Integer size, Integer page) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call " + prc_name + " (?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
|
cs.registerOutParameter(1, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(2, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(3,-10);// 输出参数 追溯数据
|
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();
|
List<Object> cols = new ArrayList();
|
List<Map<String, Object>> l2 = new ArrayList();
|
List<Object> cols2 = new ArrayList();
|
cs.execute();
|
result.add(cs.getString(1));
|
result.add(cs.getString(2));
|
if (cs.getString(1).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(3);
|
ResultSet rs2 = (ResultSet) cs.getObject(4);
|
try {
|
l = fitMap(rs);
|
cols=fitMapCols(rs);
|
l2 = fitMap(rs2);
|
cols2=fitMapCols(rs2);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(cols);
|
result.add(l2);
|
result.add(cols2);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
|
|
/**
|
* 2023-03-25 获取IQC待检看板存储过程
|
**/
|
public List getPRC_KB_IQC_prc( String prc_name,Integer size, Integer page) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call " + prc_name + " (?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
|
cs.registerOutParameter(1, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(2, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(3, -10);// 输出参数 追溯数据
|
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();
|
List<Object> cols = new ArrayList();
|
List<Map<String, Object>> l2 = new ArrayList();
|
List<Object> cols2 = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(1));
|
result.add(cs.getString(2));
|
if (cs.getString(1).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(3);
|
ResultSet resultSet = (ResultSet) cs.getObject(4);
|
|
try {
|
l = fitMap(rs);
|
cols=fitMapCols(rs);
|
|
l2 = fitMap(resultSet);
|
cols2=fitMapCols(resultSet);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(cols);
|
result.add(l2);
|
result.add(cols2);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
|
public List getPRC_APP_KANBAN_DATA_DCDC_prc(String prc_name, int pageSize, int pageNumber,String bsCode) {
|
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call " + prc_name + " (?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
// cs.setString(1, "V21");
|
cs.setString(1, bsCode);
|
cs.registerOutParameter(2, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(3, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(4, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(5, -10);// 输出参数 追溯数据
|
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<Object> cols = new ArrayList();
|
List<Map<String, Object>> l2 = new ArrayList();
|
List<Object> cols2 = new ArrayList();
|
List<Map<String, Object>> l3 = new ArrayList();
|
List<Map<String, Object>> l4 = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(2));
|
result.add(cs.getString(3));
|
if (cs.getString(2).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs4 = (ResultSet) cs.getObject(4);
|
ResultSet rs5 = (ResultSet) cs.getObject(5);
|
ResultSet rs6 = (ResultSet) cs.getObject(6);
|
ResultSet rs7 = (ResultSet) cs.getObject(7);
|
try {
|
l = fitMap(rs4);
|
// cols=fitMapCols(rs);
|
|
l2 = fitMap(rs5);
|
l3 = fitMap(rs6);
|
l4 = fitMap(rs7);
|
// cols2=fitMapCols(resultSet);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
// result.add(cols);
|
result.add(l2);
|
// result.add(cols2);
|
result.add(l3);
|
result.add(l4);
|
}
|
System.out.println(l);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
|
|
public List getPRC_APP_KANBAN_DATA_ZKB_prc(String prc_name, int pageSize, int pageNumber,String bsCode) {
|
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call " + prc_name + " (?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, bsCode);
|
cs.registerOutParameter(2, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(3, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(4, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(5, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(6, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(7, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(8, -10);// 输出参数 追溯数据
|
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();
|
List<Object> cols = new ArrayList();
|
List<Map<String, Object>> l2 = new ArrayList();
|
List<Object> cols2 = new ArrayList();
|
List<Map<String, Object>> l3 = new ArrayList();
|
List<Map<String, Object>> l4 = new ArrayList();
|
List<Map<String, Object>> l5 = new ArrayList();
|
List<Map<String, Object>> l6 = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(2));
|
result.add(cs.getString(3));
|
if (cs.getString(2).toString().equals("0")) {
|
// 游标处理
|
|
ResultSet rs4 = (ResultSet) cs.getObject(4);
|
ResultSet rs5 = (ResultSet) cs.getObject(5);
|
ResultSet rs6 = (ResultSet) cs.getObject(6);
|
ResultSet rs7 = (ResultSet) cs.getObject(7);
|
ResultSet rs8 = (ResultSet) cs.getObject(8);
|
ResultSet rs9 = (ResultSet) cs.getObject(9);
|
try {
|
l = fitMap(rs4);
|
cols=fitMapCols(rs4);
|
|
l2 = fitMap(rs5);
|
cols2=fitMapCols(rs5);
|
l3 = fitMap(rs6);
|
l4 = fitMap(rs7);
|
l5 = fitMap(rs8);
|
l6 = fitMap(rs9);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
result.add(l);
|
// result.add(cols);
|
result.add(l2);
|
// result.add(cols2);
|
result.add(l3);
|
result.add(l4);
|
result.add(l5);
|
result.add(l6);
|
}
|
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 HashMap<String, Object>();
|
for (String columnName : columnNames) {
|
map.put(columnName, rs.getString(columnName));
|
}
|
list.add(map);
|
}
|
}
|
return list;
|
}
|
|
/**
|
* 2023-3-25 返回一个数组
|
* @param rs
|
* @return
|
* @throws Exception
|
*/
|
private List<List<String>> fitMap2(ResultSet rs) throws Exception {
|
List<List<String>> list = new ArrayList<>();
|
|
if (null != rs) {
|
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()) {
|
List<String> list2 = new ArrayList<>();
|
for (String columnName : columnNames) {
|
list2.add(rs.getString(columnName));
|
|
}
|
list.add(list2);
|
}
|
}
|
return list;
|
}
|
|
/**
|
* 2021-01-29 lst 只返回列名
|
**/
|
private List<Object> fitMapCols(ResultSet rs) throws Exception {
|
List<Object> list = new ArrayList<>();
|
if (null != rs) {
|
int colNum = rs.getMetaData().getColumnCount();
|
List<Object> columnNames = new ArrayList<Object>();
|
for (int i = 1; i <= colNum; i++) {
|
columnNames.add(rs.getMetaData().getColumnName(i));
|
}
|
list = columnNames;
|
}
|
return list;
|
}
|
}
|
