package com.web.supplier.service.internal;
|
|
import java.io.IOException;
|
import java.net.MalformedURLException;
|
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 javax.servlet.http.HttpServletResponse;
|
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.data.domain.PageRequest;
|
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 com.alibaba.excel.EasyExcel;
|
import com.utils.EasyExcelUtils;
|
import com.utils.ExcelExport;
|
|
public class PrcUtils {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
|
/**
|
* 查询-获取采购订单列表-2022-6-8
|
* factory:工厂
|
* billNo:采购订单号
|
* suppNo:供应商编码
|
* materialNo:物料编号
|
* begTime/endTime:开始/结束时间
|
* size:记录数
|
* page:页码
|
*/
|
public List getPurchaseOrderPrc(String factory, String billNo, String suppNo,String materialNo,
|
String begTime, String endTime, 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_Wms_Pur_Result (?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, billNo);
|
cs.setString(3, suppNo);
|
cs.setString(4, begTime);
|
cs.setString(5, endTime);
|
cs.setString(6, materialNo);
|
cs.setInt(7, size );
|
cs.setInt(8, page + 1);
|
cs.registerOutParameter(9, java.sql.Types.INTEGER);// 输出参数 返回标识-总记录数
|
cs.registerOutParameter(10, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(11, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(12, -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(10));
|
result.add(cs.getString(11));
|
if (cs.getString(10).toString().equals("0")) {
|
result.add(cs.getInt(9));
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(12);
|
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;
|
}
|
|
/**
|
* 查询-获取送货计划列表，通过采购单带出-2022-6-8
|
* factory:工厂
|
* materialNo:物料编号
|
* size:记录数
|
* page:页码
|
*/
|
public List getDeliveryPlanByOrderPrc(String factory, String materialNo, 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_Wms_Pur_Deliv_Result (?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, materialNo);
|
cs.setInt(3, size);
|
cs.setInt(4, page + 1);
|
cs.registerOutParameter(5, java.sql.Types.INTEGER);// 输出参数 返回标识-总记录数
|
cs.registerOutParameter(6, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(7, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(8, -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(6));
|
result.add(cs.getString(7));
|
if (cs.getString(6).toString().equals("0")) {
|
result.add(cs.getInt(5));
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(8);
|
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;
|
}
|
|
/**
|
* 查询-送货明细列表-2022-6-20
|
* factory:工厂
|
* billNo:采购订单号
|
* lineNo:行号
|
* materialNo:物料编号
|
* size:记录数
|
* page:页码
|
*/
|
public List getDeliveryDetPrc(String factory, String billNo,String lineNo,String materialNo, 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_Wms_Pur_Items (?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, billNo);
|
cs.setString(3, lineNo);
|
cs.setString(4, materialNo);
|
cs.setInt(5, size);
|
cs.setInt(6, page + 1);
|
cs.registerOutParameter(7, java.sql.Types.INTEGER);// 输出参数 返回标识-总记录数
|
cs.registerOutParameter(8, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(9, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
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();
|
cs.execute();
|
result.add(cs.getInt(8));
|
result.add(cs.getString(9));
|
if (cs.getString(8).toString().equals("0")) {
|
result.add(cs.getInt(7));
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(10);
|
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;
|
}
|
|
/**
|
* 查询-获取条码历史记录-2022-6-20
|
* factory:工厂
|
* billNo:采购订单号
|
* suppNo:供应商编号
|
* begTime/endTime:开始/结束时间
|
* materialNo:物料编号
|
* materialName:物料名称
|
* barcode:物料条码
|
* size:记录数
|
* page:页码
|
*/
|
public List getBarcodeHistoryPrc(String factory, String billNo,String suppNo,String begTime,String endTime,
|
String materialNo,String materialName ,String barcode,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_Wms_Pur_Barcode (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, billNo);
|
cs.setString(3, suppNo);
|
cs.setString(4, begTime);
|
cs.setString(5, endTime);
|
cs.setString(6, materialNo);
|
cs.setString(7, materialName);
|
cs.setString(8, barcode);
|
cs.setInt(9, size);
|
cs.setInt(10, page + 1);
|
cs.registerOutParameter(11, java.sql.Types.INTEGER);// 输出参数 返回标识-总记录数
|
cs.registerOutParameter(12, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(13, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(14, -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(12));
|
result.add(cs.getString(13));
|
if (cs.getString(12).toString().equals("0")) {
|
result.add(cs.getInt(11));
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(14);
|
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;
|
}
|
|
/**
|
* 查询-获取送货单-2022-6-20
|
* factory:工厂
|
* deliveryNo:送货单号
|
* billNo:采购订单号
|
* suppNo:供应商编号
|
* begTime/endTime:开始/结束时间
|
* materialNo:物料编号
|
* materialName:物料名称
|
* size:记录数
|
* page:页码
|
*/
|
public List getDeliveryRecordPrc(String factory,String deliveryNo, String billNo,String suppNo,String begTime,String endTime,
|
String materialNo,String materialName ,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_Wms_Pur_Send_Locate (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, deliveryNo);
|
cs.setString(3, billNo);
|
cs.setString(4, suppNo);
|
cs.setString(5, begTime);
|
cs.setString(6, endTime);
|
cs.setString(7, materialNo);
|
cs.setString(8, materialName);
|
cs.setInt(9, size);
|
cs.setInt(10, page + 1);
|
cs.registerOutParameter(11, java.sql.Types.INTEGER);// 输出参数 返回标识-总记录数
|
cs.registerOutParameter(12, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(13, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(14, -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(12));
|
result.add(cs.getString(13));
|
if (cs.getString(12).toString().equals("0")) {
|
result.add(cs.getInt(11));
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(14);
|
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;
|
}
|
|
/**
|
* 查询-获取送货单-2022-6-21
|
* factory:工厂
|
* suppNo:供应商编号
|
* planOrder:计划单号
|
* begTime/endTime:开始/结束时间
|
* materialNo:物料编号
|
* materialName:物料名称
|
* size:记录数
|
* page:页码
|
*/
|
public List getDeliveryPlanPrc(String factory,String suppNo,String planOrder,String begTime,String endTime,
|
String materialNo,String materialName ,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_Wms_Pur_Deliv_Locate (?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, suppNo);
|
cs.setString(3, planOrder);
|
cs.setString(4, begTime);
|
cs.setString(5, endTime);
|
cs.setString(6, materialNo);
|
cs.setString(7, materialName);
|
cs.setInt(8, size);
|
cs.setInt(9, page + 1);
|
cs.registerOutParameter(10, java.sql.Types.INTEGER);// 输出参数 返回标识-总记录数
|
cs.registerOutParameter(11, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(12, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(13, -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(11));
|
result.add(cs.getString(12));
|
if (cs.getString(11).toString().equals("0")) {
|
result.add(cs.getInt(10));
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(13);
|
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;
|
}
|
|
/**
|
* 打印-标签-2022-6-14
|
* factory:工厂
|
* barcodeType:标签类型
|
* suppNo:供应商号
|
* ebeln:采购订单号
|
* remainQty:剩余数量
|
* materialNo:物料编号
|
* qty:数量
|
* prodDate:生产日期
|
* shipDate:出货日期
|
* envirTag:环境标识
|
* flevel:档位
|
* printQty:标签打印数量
|
* remark:特记事项
|
* actionType:动作标识 0：校验数据/1：生成条码
|
*/
|
public List printLabelPrc(String factory,String barcodeType, String suppNo, String ebeln, String remainQty,String materialNo, String qty,
|
String prodDate, String shipDate, String envirTag, String shelfLife, String flevel, String printQty,
|
String remark,String actionType)throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call Prc_Wms_Pur_Prn (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, barcodeType);
|
cs.setString(3, suppNo);
|
cs.setString(4, ebeln);
|
cs.setString(5, remainQty);
|
cs.setString(6, materialNo);
|
cs.setString(7, qty);
|
cs.setString(8, prodDate);
|
cs.setString(9, shipDate);
|
cs.setString(10, envirTag);
|
cs.setString(11, shelfLife);
|
cs.setString(12, flevel);
|
cs.setString(13, printQty);
|
cs.setString(14, remark);
|
cs.setString(15, actionType);
|
cs.registerOutParameter(16, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(17, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(18, -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(16));
|
result.add(cs.getString(17));
|
if (cs.getString(16).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(18);
|
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;
|
}
|
|
/**
|
* 打印 重打标签-2022-6-24
|
* factory:工厂
|
* suppNo:供应商编号
|
* labelId:标签ID
|
*/
|
public List rePrintLabelPrc(String factory,String suppNo,String labelId,String actionType) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call Prc_Wms_Pur_Barcode_Re (?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, suppNo);
|
cs.setString(3, labelId);
|
cs.setString(4, actionType);
|
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;
|
}
|
|
/**
|
* 送货单-获取送货单列表-2022-6-16
|
* factory:工厂
|
* suppNo:供应商编号
|
*/
|
public List getDeliveryNotePrc(String factory,String suppNo) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call Prc_Wms_Send_No (?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, suppNo);
|
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;
|
}
|
|
|
|
/**
|
* 送货单--送货单信息操作-2022-6-16
|
* factory:工厂
|
* suppNo:供应商编号
|
* mid:主表id
|
* activeFlag:操作标识（1 带出供应商信息， 2、修改/新增，根据有无ID判断， 3、删除, 4、查询,）
|
* fmemo：备注信息
|
* furgent：急料标识
|
*/
|
public List doDeliveryInfoPrc(String factory,String suppNo,String mid, String activeFlag,String fmemo,String furgent) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call Prc_Wms_Send (?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, suppNo);
|
cs.setString(3, mid);
|
cs.setString(4, activeFlag);
|
cs.setString(5, fmemo);
|
cs.setString(6, furgent);
|
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;
|
}
|
|
|
/**
|
* 送货单--送货单明细操作-2022-6-16
|
* factory:工厂
|
* deliveryNo:送货单号
|
* suppNo:供应商编号
|
* purchaseNo:送采购订单
|
* purchaseLine:行号
|
* purchaseQty:采购数量
|
* materialNo:物料编号
|
* deliveryQty:送货数量
|
* fmemo：备注信息
|
* furgent：急料标识
|
* detId：从表ID
|
* dataType：操作标识（ 1 查询， 2、修改/新增，根据有无传ID判断， 3、删除）
|
*/
|
public List doDeliveryDetPrc(String factory,String deliveryNo,String suppNo, String purchaseNo,
|
String purchaseLine,String purchaseQty,String materialNo,String deliveryQty,
|
String fmemo,String furgent,String detId,String dataType) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call Prc_Wms_Send_Items (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, deliveryNo);
|
cs.setString(3, suppNo);
|
cs.setString(4, purchaseNo);
|
cs.setString(5, purchaseLine);
|
cs.setString(6, purchaseQty);
|
cs.setString(7, materialNo);
|
cs.setString(8, deliveryQty);
|
cs.setString(9, fmemo);
|
cs.setString(10, furgent);
|
cs.setString(11, detId);
|
cs.setString(12, dataType);
|
cs.registerOutParameter(13, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(14, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(15, -10);// 输出参数 追溯数据
|
cs.registerOutParameter(16, -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>> l2 = new ArrayList();
|
cs.execute();
|
result.add(cs.getInt(13));
|
result.add(cs.getString(14));
|
if (cs.getString(13).toString().equals("0")) {
|
// 游标处理
|
ResultSet rs = (ResultSet) cs.getObject(15);
|
ResultSet rs2 = (ResultSet) cs.getObject(16);
|
try {
|
l = fitMap(rs);
|
l2 = fitMap(rs2);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l2);
|
}
|
System.out.println(l);
|
System.out.println(l2);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
/**
|
* 送货单--打印操作-2022-6-18
|
* factory:工厂
|
* suppNo:供应商编号
|
*/
|
public List printDeliveryPrc(String factory,String deliveryNo) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call Prc_Wms_Send_Prn (?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, deliveryNo);
|
cs.registerOutParameter(3, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(4, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
cs.registerOutParameter(5, -10);// 输出参数 追溯数据
|
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();
|
List<Map<String, Object>> l2 = 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);
|
ResultSet rs2 = (ResultSet) cs.getObject(6);
|
try {
|
l = fitMap(rs);
|
l2 = fitMap(rs2);
|
} catch (Exception e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
result.add(l);
|
result.add(l2);
|
}
|
System.out.println(l);
|
System.out.println(l2);
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
/**
|
* 送货单--送货单审核/反审核-2022-6-18
|
* factory:工厂
|
* deliveryNo:送货单号
|
* suppNo:供应商编号
|
* activeFlag:操作标识1/0 （1 审核/ 0 反审核）
|
*/
|
public List checkDeliveryPrc(String factory,String suppNo,String deliveryNo,String activeFlag) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call Prc_Wms_Send_Check (?,?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, suppNo);
|
cs.setString(3, deliveryNo);
|
cs.setString(4, activeFlag);
|
cs.registerOutParameter(5, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(6, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
cs.execute();
|
result.add(cs.getInt(5));
|
result.add(cs.getString(6));
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
/**
|
* 送货单--送货单作废-2022-7-15
|
* factory:工厂
|
* deliveryNo:送货单号
|
* suppNo:供应商编号
|
*/
|
public List cancelDeliveryPrc(String factory,String suppNo,String deliveryNo) throws Exception {
|
List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
|
@Override
|
public CallableStatement createCallableStatement(Connection con) throws SQLException {
|
String storedProc = "{call Prc_Wms_Send_Cancel (?,?,?,?,?)}";// 调用的sql
|
CallableStatement cs = con.prepareCall(storedProc);
|
cs.setString(1, factory);
|
cs.setString(2, suppNo);
|
cs.setString(3, deliveryNo);
|
cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识-标识
|
cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
|
return cs;
|
}
|
}, new CallableStatementCallback() {
|
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
|
List<Object> result = new ArrayList<>();
|
cs.execute();
|
result.add(cs.getInt(4));
|
result.add(cs.getString(5));
|
return result;
|
}
|
});
|
return resultList;
|
}
|
|
|
/**
|
* 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;
|
}
|
|
/**
|
* 2021-01-29 数据导出【导出大量数据会卡死】 param: response, obj-存储过程结果 cols-表头列
|
**/
|
public void Export1(HttpServletResponse response, Object obj, Object cols) throws Exception {
|
Map<String, Object> param = new HashMap<String, Object>();
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
// String filePath = "static/excelFile/导出数据.xlsx";
|
String filePath = "导出数据.xlsx";
|
// List<Object> objList=(List<Object>) obj;//结果集
|
|
String cString = cols.toString();
|
cString = cString.substring(1, cString.length() - 1);// 头尾去掉'['&']'
|
String[] map_arr = cString.split(", ");// 列名数据处理
|
|
List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();// 最终数据
|
listMap = (List<Map<String, Object>>) obj;
|
|
ExcelExport.export(response, listMap, workbook, map_arr, filePath, "导出数据.xlsx");
|
}
|
|
/**
|
* 2021-01-30 数据导出 easyexcel
|
* lst
|
* param: response, obj-存储过程结果 cols-表头列
|
**/
|
@GetMapping("download")
|
public void Export3(HttpServletResponse response, Object obj, Object cols) throws Exception {
|
// 处理表头数组
|
String cString = cols.toString();
|
cString = cString.substring(1, cString.length() - 1);// 头尾去掉'['&']'
|
String[] tit_arr = cString.split(", ");// 列名数据处理
|
// 查询出的数据
|
List<Map<String, Object>> list = (List<Map<String, Object>>) obj;
|
EasyExcelUtils.download(response, "导出数据", tit_arr, tit_arr, list);
|
}
|
|
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;
|
}
|
}
|
