package com.web.jhsop.service.internal;

import com.app.base.data.ApiResponseResult;
import com.web.ftp.service.FileCheckService;
import com.web.jhsop.service.JhSopDataService;

import org.apache.commons.lang3.StringUtils;
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.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Transactional(propagation = Propagation.REQUIRED)

public class JhSopDataServiceImpl implements JhSopDataService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private FileCheckService fileCheckService;

    @Override
    public ApiResponseResult getManufactoryList() throws Exception {
        try {

            List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
                @Override
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    String storedProc = "{call prc_sop_task_re" + "(?,?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs = con.prepareCall(storedProc);
                    cs.setString(1, "100");
                    cs.setString(2, "100");
                    cs.setString(3, "获取工单");
                    cs.registerOutParameter(4, Types.INTEGER);// 输出参数 返回标识
                    cs.registerOutParameter(5, Types.VARCHAR);// 输出参数 错误信息
                    cs.registerOutParameter(6, -10);// 输出参数 追溯数据
                    return cs;
                }
            }, new CallableStatementCallback() {
                @Override
                public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                    List<Object> result = new ArrayList<Object>();
                    cs.execute();
                    result.add(cs.getString(4));
                    result.add(cs.getString(5));
                    if (cs.getString(4).toString().endsWith("0")) {
                        //游标处理
                        ResultSet rs = (ResultSet) cs.getObject(6);
                        List l = new ArrayList();

                        try {
                            l = fitMap(rs);
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                        result.add(l);
                    }
                    return result;
                }
            });
            if (("0").equals(resultList.get(0))) {
                return ApiResponseResult.success().data(resultList.get(2));
            } else {
                return ApiResponseResult.failure().message(resultList.get(1).toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            return ApiResponseResult.failure().message(e.getMessage());
        }
    }

    @Override
    public ApiResponseResult getProcessLine(String workOrder) throws Exception {
        try {
            if (StringUtils.isEmpty(workOrder)) {
                return ApiResponseResult.failure("请选择工单号");
            }
            List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
                @Override
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    String storedProc = "{call PRC_SOP_PROC_RE" + "(?,?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs = con.prepareCall(storedProc);
                    cs.setString(1, "100");
                    cs.setString(2, "100");
                    cs.setString(3, workOrder);
                    cs.registerOutParameter(4, Types.INTEGER);// 输出参数 返回标识
                    cs.registerOutParameter(5, Types.VARCHAR);// 输出参数 错误信息
                    cs.registerOutParameter(6, -10);// 输出参数 追溯数据
                    return cs;
                }
            }, new CallableStatementCallback() {
                @Override
                public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                    List<Object> result = new ArrayList<Object>();
                    cs.execute();
                    result.add(cs.getString(4));
                    result.add(cs.getString(5));
                    if (cs.getString(4).toString().endsWith("0")) {
                        //游标处理
                        ResultSet rs = (ResultSet) cs.getObject(6);
                        List l = new ArrayList();

                        try {
                            l = fitMap(rs);
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                        result.add(l);
                    }
                    return result;
                }
            });
            if (("0").equals(resultList.get(0))) {
                return ApiResponseResult.success().data(resultList.get(2));
            } else {
                return ApiResponseResult.failure().message(resultList.get(1).toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            return ApiResponseResult.failure().message(e.getMessage());
        }
    }

    @Override
    public ApiResponseResult getFile(String manufactory, String processLIne) {
        try {
            if (StringUtils.isEmpty(manufactory)) {
                return ApiResponseResult.failure("请选择工单号");
            }
            if (StringUtils.isEmpty(processLIne)) {
                return ApiResponseResult.failure("请选择工序线体");
            }
            List<Object> resultList = (List<Object>) jdbcTemplate.execute(new CallableStatementCreator() {
                @Override
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    String storedProc = "{call PRC_SOP_PDF_RE" + "(?,?,?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs = con.prepareCall(storedProc);
                    cs.setString(1, "100");
                    cs.setString(2, "100");
                    cs.setString(3, manufactory);
                    cs.setString(4, processLIne);
                    cs.registerOutParameter(5, Types.INTEGER);// 输出参数 返回标识
                    cs.registerOutParameter(6, Types.VARCHAR);// 输出参数 错误信息
                    cs.registerOutParameter(7, -10);// 输出参数 追溯数据
                    return cs;
                }
            }, new CallableStatementCallback() {
                @Override
                public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                    List<Object> result = new ArrayList<Object>();
                    cs.execute();
                    result.add(cs.getString(5)+"");
                    result.add(cs.getString(6));
                    if (cs.getString(5).toString().endsWith("0")) {
                        //游标处理
                        ResultSet rs = (ResultSet) cs.getObject(7);
                        List l = new ArrayList();

                        try {
                            l = fitMap(rs);
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                        result.add(l);
                    }
                    return result;
                }
            });
            if (("0").equals(resultList.get(0))) {
                ArrayList<HashMap> arrayList = (ArrayList<HashMap>) resultList.get(2);
                if (CollectionUtils.isEmpty(arrayList)){
                    return ApiResponseResult.failure().message("没有此文件信息");
                }
//                Map<String,String> ftpConfig=new HashMap<>();
//                System.out.println("arrayList.get(0)"+arrayList.get(0));
//                Object pathObj = arrayList.get(0).get("路径");
//                Object rootObj = arrayList.get(0).get("根目录");
//                if(pathObj!=null){
//                    ftpConfig.put("path",arrayList.get(0).get("路径").toString());
//                }
//                if(rootObj!=null){
//                    ftpConfig.put("root",arrayList.get(0).get("根目录").toString());
//                }
//                ftpConfig.put("account",arrayList.get(0).get("账号").toString());
//                ftpConfig.put("password",arrayList.get(0).get("密码").toString());
////                ftpConfig.put("fileName",arrayList.get(0).get("文件名").toString());
//                ftpConfig.put("url",arrayList.get(0).get("URL").toString());
//                ftpConfig.put("nums",arrayList.get(0).get("顺序号").toString());
//                ApiResponseResult download = fileCheckService.onlineViewPdf(arrayList.get(0).get("URL").toString(), 21, arrayList.get(0).get("账号").toString(), arrayList.get(0).get("密码").toString(), "/20221208", "/20221208110344_87.pdf","/task",response);
//                ApiResponseResult download = fileCheckService.onlineViewPdf(arrayList.get(0).get("URL").toString(), 21, arrayList.get(0).get("账号").toString(), arrayList.get(0).get("密码").toString(), path, arrayList.get(0).get("文件名").toString(),arrayList.get(0).get("根目录").toString(),response);
//                ApiResponseResult download = fileCheckService.onlineViewPdf(arrayList.get(0).get("URL").toString(), 21, arrayList.get(0).get("账号").toString(), arrayList.get(0).get("密码").toString(), "/20221209", "/20221209102241_718.xlsx",response);
                return ApiResponseResult.success().data(resultList.get(2));

            } else {
                return ApiResponseResult.failure().message(resultList.get(1).toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
            return ApiResponseResult.failure().message(e.getMessage());
        }
    }

    @Override
    public ApiResponseResult scanProduct(String line, String proc, String taskNo, String user, String sn, String procOrder, String procName) {
        try {
//            if (StringUtils.isEmpty(line)||StringUtils.isEmpty(proc)||StringUtils.isEmpty(taskNo)||StringUtils.isEmpty(user)||StringUtils.isEmpty(sn)||StringUtils.isEmpty(procOrder)) {
//                return ApiResponseResult.failure("请补全信息");
//            }
            List<Object> list = (List<Object>)jdbcTemplate.execute(new CallableStatementCreator() {
                @Override
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    String storedProc = "{call prc_linchpin_sn" + "(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs = con.prepareCall(storedProc);
                    cs.setString(1, "");
                    cs.setString(2, line);
                    cs.setString(3, proc);
                    cs.setString(4, taskNo);
                    cs.setString(5, user);
                    cs.setString(6, sn);
                    cs.setString(7, procOrder);
                    cs.registerOutParameter(8, Types.INTEGER);// 输出参数 返回标识
                    cs.registerOutParameter(9, Types.VARCHAR);// 输出参数 错误信息
                    cs.registerOutParameter(10, Types.VARCHAR);// 输出参数 空值
                    return cs;
                }
            }, new CallableStatementCallback() {
                @Override
                public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                    List<Object> result = new ArrayList<Object>();
                    cs.execute();
                    result.add(cs.getString(8));
                    result.add(cs.getString(9));
                    return result;
                }
            });
            if(!("0").equals(list.get(0))){
                return ApiResponseResult.failure(list.get(1).toString());
            }
            if(((String) list.get(1)).equalsIgnoreCase("GOTO:BTN1")){
                //执行prc_barcode_collect_01
                List<Object> resList = executePrcBarcode(line,proc,taskNo,user,sn,procOrder,procName);
                if (("0").equals(resList.get(0))) {
                    Map<String,Object> resMap = new HashMap<>();
                    resMap.put("operate","GOTO:BTN1");
                    resMap.put("data",resList.get(1));
                    return ApiResponseResult.success().data(resMap);
                } else {
                    return ApiResponseResult.failure(resList.get(1).toString());
                }
            }else if(((String) list.get(1)).equalsIgnoreCase("GOTO:BARCODE")){
                //执行prc_get_keyitem 返回游标
                List<Object> resultList = executeGetKeyItem(taskNo,proc,sn);
                if (("0").equals(resultList.get(0))) {
                    Map<String,Object> resMap = new HashMap<>();
                    resMap.put("operate","GOTO:BARCODE");
                    resMap.put("data",resultList.get(2));
                    return ApiResponseResult.success().data(resMap);
                } else {
                    return ApiResponseResult.failure(resultList.get(1).toString());
                }
            }
            else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return ApiResponseResult.failure().message(e.getMessage());
        }
    }

    @Override
    public ApiResponseResult scanMaterial(String line, String proc, String user, String taskNo, String barcode, String sn, String procOrder, String procName) {
        try {
            if (StringUtils.isEmpty(line)||StringUtils.isEmpty(proc)||StringUtils.isEmpty(taskNo)||StringUtils.isEmpty(user)||StringUtils.isEmpty(sn)||StringUtils.isEmpty(procOrder)) {
                return ApiResponseResult.failure("请补全信息");
            }
            List<Object> list = (List<Object>)jdbcTemplate.execute(new CallableStatementCreator() {
                @Override
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    String storedProc = "{call PRC_LINCHPIN_ITEM" + "(?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs = con.prepareCall(storedProc);
                    cs.setString(1, line);
                    cs.setString(2, proc);
                    cs.setString(3, "");
                    cs.setString(4, "");
                    cs.setString(5, user);
                    cs.setString(6, taskNo);
                    cs.setString(7, barcode);
                    cs.setString(8, sn);
                    cs.registerOutParameter(9, Types.INTEGER);// 输出参数 返回标识
                    cs.registerOutParameter(10, Types.VARCHAR);// 输出参数 错误信息
                    cs.registerOutParameter(11, Types.VARCHAR);//
                    cs.registerOutParameter(12, Types.VARCHAR);//
                    cs.registerOutParameter(13, Types.VARCHAR);//
                    return cs;
                }
            }, new CallableStatementCallback() {
                @Override
                public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                    List<Object> result = new ArrayList<Object>();
                    cs.execute();
                    result.add(cs.getString(9));
                    result.add(cs.getString(10));
                    return result;
                }
            });
            if(!("0").equals(list.get(0))){
                return ApiResponseResult.failure(list.get(1).toString());
            }
            if(((String) list.get(1)).equalsIgnoreCase("GOTO:BTN1")){
                //执行prc_barcode_collect_01
                List<Object> resList = executePrcBarcode(line,proc,taskNo,user,sn,procOrder,procName);
                if (("0").equals(resList.get(0))) {
                    Map<String,Object> resMap = new HashMap<>();
                    resMap.put("operate","GOTO:BTN1");
                    return ApiResponseResult.success().data(resMap);
                } else {
                    return ApiResponseResult.failure(resList.get(1).toString());
                }
            }else if(((String) list.get(1)).equalsIgnoreCase("GOTO:BARCODE")){
                //执行prc_get_keyitem 返回游标
                List<Object> resultList = executeGetKeyItem(taskNo,proc,sn);
                if (("0").equals(resultList.get(0))) {
                    Map<String,Object> resMap = new HashMap<>();
                    resMap.put("operate","GOTO:BARCODE");
                    resMap.put("data",resultList.get(2));
                    return ApiResponseResult.success().data(resMap);
                } else {
                    return ApiResponseResult.failure(resultList.get(1).toString());
                }
            }
            else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return ApiResponseResult.failure().message(e.getMessage());
        }
    }

    @Override
    public ApiResponseResult queryPass(String proc, String taskNo, String num) {
        final List result = new ArrayList();
        String sql = "SELECT COUNT(1)  AS sumPass FROM MES_BARIF A  WHERE A.IF001='"+
                proc + "'"
                +" AND A.IF005='"
                + taskNo + "'"
                +" AND A.if008='"
                + num + "'"
                + " AND A.IF010='合格' AND A.IF012='Y'";
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                result.add(resultSet.getString("sumPass").toString());
                System.out.println(resultSet);
            }
        });
        System.out.println(result.get(0));
        return ApiResponseResult.success().data(result.get(0));
    }

    @Override
    public ApiResponseResult queryPerson(String user) {
        final List result = new ArrayList();
        String sql = "select Fname as name from SYS_USER where fcode = '" + user + "'";
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                result.add(resultSet.getString("name").toString());
                System.out.println(resultSet);
            }
        });
        return ApiResponseResult.success().data(result.get(0));
    }


    /**
     * 游标处理
     */
    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;
    }
    public List<Object> executePrcBarcode(String line, String proc, String taskNo, String user, String sn, String procOrder,String procName){
        List<Object> resList = (List<Object>)jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call prc_barcode_collect_01" + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, "");
                cs.setString(2, taskNo);
                cs.setString(3, line);
                cs.setString(4, procOrder);
                cs.setString(5, sn);
                cs.setString(6, "0");
                cs.setString(7, user);
                cs.setString(8, proc);
                cs.setString(9, procName);//工序名称
                cs.setString(10, "");
                cs.setString(11, "");
                cs.setString(12, "");
                cs.setString(13, "");
                cs.registerOutParameter(14, Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(15, Types.VARCHAR);// 输出参数 返回标识
                return cs;
            }
        }, new CallableStatementCallback() {
            @Override
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<Object>();
                cs.execute();
                result.add(cs.getString(14));
                result.add(cs.getString(15));
                return result;
            }
        });
        return resList;
    }
    public List<Object> executeGetKeyItem(String taskNo,String proc,String sn){
        List<Object> resultList = (List<Object>)jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call prc_get_keyitem" + "(?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, taskNo);
                cs.setString(2, proc);
                cs.setString(3, sn);
                cs.registerOutParameter(4, Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(5, Types.VARCHAR);// 输出参数 返回标识
                cs.registerOutParameter(6, -10);// 输出参数 返回标识
                return cs;
            }
        }, new CallableStatementCallback() {
            @Override
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<Object>();
                cs.execute();
                result.add(cs.getString(4));
                result.add(cs.getString(5));
                if (cs.getString(4).toString().endsWith("0")) {
                    //游标处理
                    ResultSet rs = (ResultSet) cs.getObject(6);
                    List l = new ArrayList();

                    try {
                        l = fitMap(rs);
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    result.add(l);
                }
                return result;
            }
        });
        return resultList;
    }
}