package com.web.kanban.service.internal;

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 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 NingboEvePrcUtils {


    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List getPRC_APP_KANBAN_NZSDT_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);// 输出参数 追溯数据
                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();
                List<Object> cols = new ArrayList();
                List<Object> l2 = new ArrayList();
                List<Object> cols2 = new ArrayList();
                List<Map<String, Object>> l3 = 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);
                    ResultSet rs3 = (ResultSet) cs.getObject(5);
                    try {
                        l = fitMap(rs);
//                        cols=fitMapCols(rs);
                        l2 = fitMap2(rs2);
//                        cols2=fitMapCols(rs2);
                        l3 = fitMap(rs3);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    result.add(l);
//                    result.add(cols);
                    result.add(l2);
                    result.add(l3);
//                    result.add(cols2);
                }
                System.out.println(l);
                return result;
            }
        });
        return resultList;
    }



    public List getPRC_APP_KANBAN_DYSDT_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);// 输出参数 追溯数据
                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();
                List<Object> cols = new ArrayList();
                List<Object> l2 = new ArrayList();
                List<Object> cols2 = new ArrayList();
                List<Map<String, Object>> l3 = 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);
                    ResultSet rs3 = (ResultSet) cs.getObject(5);
                    try {
                        l = fitMap(rs);
//                        cols=fitMapCols(rs);
                        l2 = fitMap2(rs2);
//                        cols2=fitMapCols(rs2);
                        l3 = fitMap(rs3);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    result.add(l);
//                    result.add(cols);
                    result.add(l2);
                    result.add(l3);
//                    result.add(cols2);
                }
                System.out.println(l);
                return result;
            }
        });
        return resultList;
    }

    public List getPRC_APP_KANBAN_K1SDT_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);// 输出参数 追溯数据
                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();
                List<Object> cols = new ArrayList();
                List<Object> l2 = new ArrayList();
                List<Object> cols2 = new ArrayList();
                List<Map<String, Object>> l3 = 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);
                    ResultSet rs3 = (ResultSet) cs.getObject(5);
                    try {
                        l = fitMap(rs);
//                        cols=fitMapCols(rs);
                        l2 = fitMap2(rs2);
                        l3 = fitMap(rs3);
//                        cols2=fitMapCols(rs2);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    result.add(l);
//                    result.add(cols);
                    result.add(l2);
                    result.add(l3);
//                    result.add(cols2);
                }
                System.out.println(l);
                return result;
            }
        });
        return resultList;
    }



    public List getPRC_APP_KANBAN_K2SDT_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);// 输出参数 追溯数据
                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();
                List<Object> cols = new ArrayList();
                List<Object> l2 = new ArrayList();
                List<Object> cols2 = new ArrayList();
                List<Map<String, Object>> l3 = 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);
                    ResultSet rs3 = (ResultSet) cs.getObject(5);
                    try {
                        l = fitMap(rs);
//                        cols=fitMapCols(rs);
                        l2 = fitMap2(rs2);
//                        cols2=fitMapCols(rs2);
                        l3 = fitMap(rs3);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    result.add(l);

                    result.add(l2);
                    result.add(l3);
                }
                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));
                }
                System.out.println(map);
                list.add(map);
            }
        }
        return list;
    }

    /**
     * 2023-3-25   返回一个数组
     * @param rs
     * @return
     * @throws Exception
     */
    private List<Object> fitMap2(ResultSet rs) throws Exception {
       List<Object> 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()) {
                for (String columnName : columnNames) {
                    if ("Y_DATA".equals(columnName))
                    {
                        list.add(Float.valueOf(rs.getString(columnName)));
                    }


                }
                if (list.size() > 400000)
                {
                    break;

                }

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