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