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;
	}
}