package com.web.pda.lyt.lytPda.service.internal;

import com.app.base.data.ApiResponseResult;
import com.web.pda.lyt.lytPda.service.HoPatrolCheckService;
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.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.Console;
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;

@Service
@Transactional(propagation = Propagation.REQUIRED)
public class HoPatrolCheckImpl implements HoPatrolCheckService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public ApiResponseResult getLineNo(String userNo) throws Exception {
        List<Object> list = getLineNooPrc(userNo);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        return ApiResponseResult.success().data(list.get(2));
    }

    @Override
    public ApiResponseResult getProccInfoT(String userNo) throws Exception {
        List<Object> list = getProccPrc(userNo);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        return ApiResponseResult.success().data(list.get(2));
    }

    public List getProccPrc(String userNo) throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call PRC_PAD_PQC_Procc_INFO(?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.registerOutParameter(2, java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(3, java.sql.Types.VARCHAR);// 输出参数 返回标识
                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();
                cs.execute();
                result.add(cs.getInt(2));
                result.add(cs.getString(3));
                if (cs.getString(2).toString().equals("0")) {
                    // 游标处理
                    ResultSet rs = (ResultSet) cs.getObject(4);
                    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;
    }



    @Override
    public ApiResponseResult getLineSelect(String userNo, String lineNo, String taskNo) throws Exception {
        List<Object> list = getLineSelectPrc(userNo,lineNo,taskNo);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        return ApiResponseResult.success().data(list.get(2));
    }

    @Override
    public ApiResponseResult getBillSelect(String userNo, String lineNo, String taskNo) throws Exception {
        List<Object> list = getBillSelectPrc(userNo,lineNo,taskNo);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        Map map = new HashMap();
        map.put("cursor1", list.get(2));
        map.put("cursor2", list.get(3));
        return ApiResponseResult.success().data(map);
    }

    @Override
    public ApiResponseResult getprocessBillSelect(String userNo, String lineNo, String taskNo) throws Exception {
        List<Object> list = getprocessBillSelectPrc(userNo,lineNo,taskNo);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        Map map = new HashMap();
        map.put("cursor1", list.get(2));
        map.put("cursor2", list.get(3));
        return ApiResponseResult.success().data(map);
    }

    public List getprocessBillSelectPrc(String userNo, String lineNo, String taskNo) throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call PRC_PAD_PQC_process_SELECT(?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.setString(2, lineNo);
                cs.setString(3, taskNo);
                cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
                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<Map<String, Object>> l_2 = new ArrayList();
                cs.execute();
                result.add(cs.getInt(4));
                result.add(cs.getString(5));
                if (cs.getString(4).toString().equals("0")) {
                    // 游标处理
                    ResultSet rs = (ResultSet) cs.getObject(6);
                    ResultSet rs2 = (ResultSet) cs.getObject(7);
                    try {
                        l = fitMap(rs);
                        l_2 = fitMap(rs2);
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    result.add(l);
                    result.add(l_2);
                }
                System.out.println(l);
                System.out.println(l_2);
                return result;
            }
        });
        return resultList;
    }

    @Override
    public ApiResponseResult getDetails(String userNo, String checkNo, int pid) throws Exception {
        List<Object> list = getDetailsPrc(userNo,checkNo,pid);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        Map map = new HashMap();
        map.put("cursor1", list.get(2));
        map.put("cursor2", list.get(3));
        return ApiResponseResult.success().data(map);
    }

    @Override
    public ApiResponseResult checkDetailSave(String userNo, String checkNo, int pid, String checkResult, String checkDemo) throws Exception {
        List<Object> list = checkDetailSaveProc(userNo, checkNo, pid, checkResult, checkDemo);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        return ApiResponseResult.success();
    }

    @Override
    public ApiResponseResult checkDetailDel(String userNo, String checkNo, int pid, String checkResult, String checkDemo, String type) throws Exception {
        List<Object> list = checkDetailDelProc(userNo, checkNo, pid, checkResult, checkDemo, type);
        if (!list.get(0).toString().equals("0")) { // 存储过程调用失败
            return ApiResponseResult.failure(list.get(1).toString());
        }
        return ApiResponseResult.success();
    }


    @Override
    public ApiResponseResult checkDetailPass(String userNo, String checkNo) throws Exception {
        List<Object> list = checkDetailPassProc(userNo, checkNo);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        return ApiResponseResult.success();
    }

    @Override
    public ApiResponseResult submitData(String userNo, String checkNo, int type) throws Exception {
        List<Object> list = submitDataProc(userNo, checkNo,type);
        if (!list.get(1).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(2).toString());
        }
        return ApiResponseResult.success(list.get(0).toString());
    }

    public List getLineNooPrc(String userNo) throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call PRC_PAD_PQC_LINE_INFO(?,?,?,?)}";// 调用的sql

                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.registerOutParameter(2, java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(3, java.sql.Types.VARCHAR);// 输出参数 返回标识
                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();
                cs.execute();
                result.add(cs.getInt(2));
                result.add(cs.getString(3));
                if (cs.getString(2).toString().equals("0")) {
                    // 游标处理
                    ResultSet rs = (ResultSet) cs.getObject(4);
                    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;
    }

    public List getLineSelectPrc(String userNo, String lineNo, String taskNo) throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call PRC_PAD_PQC_LINE_SELECT(?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.setString(2, lineNo);
                cs.setString(3, taskNo);
                cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
                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();
                cs.execute();
                result.add(cs.getInt(4));
                result.add(cs.getString(5));
                if (cs.getString(4).toString().equals("0")) {
                    // 游标处理
                    ResultSet rs = (ResultSet) cs.getObject(6);
                    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;
    }

    public List getBillSelectPrc(String userNo, String lineNo, String taskNo) throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call PRC_PAD_PQC_BILL_SELECT(?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.setString(2, lineNo);
                cs.setString(3, taskNo);
                cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
                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<Map<String, Object>> l_2 = new ArrayList();
                cs.execute();
                result.add(cs.getInt(4));
                result.add(cs.getString(5));
                if (cs.getString(4).toString().equals("0")) {
                    // 游标处理
                    ResultSet rs = (ResultSet) cs.getObject(6);
                    ResultSet rs2 = (ResultSet) cs.getObject(7);
                    try {
                        l = fitMap(rs);
                        l_2 = fitMap(rs2);
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    result.add(l);
                    result.add(l_2);
                }
                System.out.println(l);
                System.out.println(l_2);
                return result;
            }
        });
        return resultList;
    }

    public List getDetailsPrc(String userNo, String checkNo, int pid) throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call PRC_PAD_PQC_DETAIL_SELECT(?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.setString(2, checkNo);
                cs.setInt(3, pid);
                cs.registerOutParameter(4, java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(5, java.sql.Types.VARCHAR);// 输出参数 返回标识
                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<Map<String, Object>> l_2 = new ArrayList();
                cs.execute();
                result.add(cs.getInt(4));
                result.add(cs.getString(5));
                if (cs.getString(4).toString().equals("0")) {
                    // 游标处理
                    ResultSet rs = (ResultSet) cs.getObject(6);
                    ResultSet rs2 = (ResultSet) cs.getObject(7);
                    try {
                        l = fitMap(rs);
                        l_2 = fitMap(rs2);
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    result.add(l);
                    result.add(l_2);
                }
                System.out.println(l);
                System.out.println(l_2);
                return result;
            }
        });
        return resultList;
    }


    public List checkDetailSaveProc(String userNo, String checkNo, int pid, String checkResult, String checkDemo)
            throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call  PRC_PAD_PQC_DETAIL_SAVE(?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.setString(2,checkNo);
                cs.setInt(3, pid);
                cs.setString(4, checkResult);
                cs.setString(5, checkDemo);
                cs.registerOutParameter(6, java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(7, 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(6));
                result.add(cs.getString(7));
                return result;
            }
        });
        return resultList;
    }

    public List checkDetailDelProc(String userNo, String checkNo, int pid, String checkResult, String checkDemo, String type)
            throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call PRC_PAD_PQC_DETAIL_DEL(?,?,?,?,?,?)}"; // 修改存储过程，增加 type 参数
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.setString(2, checkNo);
                cs.setInt(3, pid);
                cs.setString(4, type); // 传入 type，区分不同的检验类型
                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;
    }


    public List checkDetailPassProc(String userNo, String checkNo)
            throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call  PRC_PAD_PQC_DETAIL_ALLOK(?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.setString(2,checkNo);
                cs.registerOutParameter(3, java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(4, 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(3));
                result.add(cs.getString(4));
                return result;
            }
        });
        return resultList;
    }

    public List submitDataProc(String userNo, String checkNo, int type)
            throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call  PRC_PAD_PQC_FSUBMIT(?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, userNo);
                cs.setString(2,checkNo);
                cs.setInt(3,type);
                cs.registerOutParameter(4, java.sql.Types.VARCHAR);// 输出参数 返回标识
                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.getString(4));
                result.add(cs.getInt(5));
                result.add(cs.getString(6));
                return result;
            }
        });
        return resultList;
    }

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