package com.web.kanban.service.internal;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
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 org.apache.commons.io.FileUtils;
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 com.app.base.data.ApiResponseResult;
import com.web.kanban.service.KanbanService;
import org.springframework.util.ClassUtils;

@Service(value = "KanbanService")
@Transactional(propagation = Propagation.REQUIRED)
public class KanbanImpl implements KanbanService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    //获取看板目录


    @Override
    public ApiResponseResult getIQCKanbanData(String floor) throws Exception {
        Map map = new HashMap();
        List<Object> list_1 = getKanbanDataPrc("Prc_KB_IQCDJ", floor);
        if (!list_1.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            map.put("TABEL", list_1.get(1).toString());
        } else {
            map.put("TABEL", list_1.get(2));
        }
        List<Object> list_2 = getKanbanDataPrc("Prc_KB_IQCDJ_HZ", floor);
        if (!list_2.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            map.put("OK_VALUE", list_2.get(1).toString());
        } else {
            map.put("OK_VALUE", list_2.get(2));
        }
        return ApiResponseResult.success().data(map);
    }

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

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

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

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

    //包装
    public ApiResponseResult getBZData(String floor) throws Exception {
        List<Object> list = getKanbanDataPrc("PRC_KB_CK_QT", floor);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        return ApiResponseResult.success().data(list.get(2));
    }

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

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

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

    @Override
    public ApiResponseResult getLineHeadData(String floor) throws Exception {
        String[] prcList = {"PRC_KB_Line_A", "PRC_KB_Line_B", "PRC_KB_Line_C", "PRC_KB_Line_D", "PRC_KB_Line_E", "PRC_KB_LINE_F", "PRC_KB_AD_YCB_LINE"};
        //String[] prcList = { "PRC_KB_Line_A" };

        Map map = new HashMap();
        for (int i = 0; i < prcList.length; i++) {
            List<Object> list = getKanbanDataPrc(prcList[i], floor);
            if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
                map.put("DATA_" + i, list.get(1).toString());
            } else {
                map.put("DATA_" + i, list.get(2));
            }
        }
        return ApiResponseResult.success().data(map);
    }

    public Boolean getUserImage(String code) throws Exception {
        try {
            getUserImg(code);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public InputStream getUserImg(String fcode) {
        String sql = "select u.EXP_FIELD3 from sys_user u where u.fcode=?";
        InputStream inn = null;
        //将结果集数据行中的数据抽取到forum对象中
        jdbcTemplate.query(sql, new Object[]{fcode}, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                InputStream in = rs.getBinaryStream("EXP_FIELD3");
                saveImg(in, fcode);
            }
        });
        return inn;
    }

    public void saveImg(InputStream inputStream, String fcode) {
        String location = System.getProperty("user.dir");
        //获取项目classes/static的地址
        //String staticPath = ClassUtils.getDefaultClassLoader().getResource("static").getPath();
        System.out.println(File.separator);
        // 图片保存路径-测试地址
        //String savePath = new String("src"+ File.separator+"main"+ File.separator+"resources"+ File.separator+"static"+ File.separator+"downImages" + File.separator+fcode+ ".png");

        //获取项目classes/static的地址-tomcat正式使用地址
        String staticPath = ClassUtils.getDefaultClassLoader().getResource("static").getPath();
        String fileName = fcode + ".png";  //获取文件名

        // 图片存储目录及图片名称
        String url_path = "downImages" + File.separator + fileName;
        //图片保存路径
        String savePath = staticPath + File.separator + url_path;

        System.out.println("图片保存地址：" + savePath);

        File saveFile = new File(savePath);

        try {
            FileUtils.copyInputStreamToFile(inputStream, saveFile);
        } catch (IOException e) {
            System.out.println(e.toString());
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }


    public List getKanbanDataPrc(String prcName, String floor) throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call  " + prcName + "(?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, "1");
                cs.setString(2, floor);
                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;
    }


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

    //以往记录-未使用（不确定，忘了）
    public ApiResponseResult getKanbanData(String kanbanNo, String lingNo, String dataType) throws Exception {
        List<Object> list = getKanbanDataPrc(kanbanNo, lingNo);
        if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
            return ApiResponseResult.failure(list.get(1).toString());
        }
        return ApiResponseResult.success().data(list.get(2));
    }

    /**
     * 模板调用的存储过程 kanbanNo:看板号，1-表示车间看板 lineNo:线号，没有线号时可为空 frequency:表示调用过程总次数
     * date:2021-10-18
     **/
    public ApiResponseResult getTemplateData(String kanbanNo, String lineNo, int frequency) throws Exception {
        //Map<String, Object> map = new LinkedHashMap<String, Object>();
        List<Object> resultList = new ArrayList<>();
        for (int i = 0; i < frequency; i++) {
            List<Object> list = getKanbanDataPrc(kanbanNo, lineNo);
            Map<String, Object> m = new LinkedHashMap<String, Object>();

            if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标
                m.put("result", list.get(0));
                m.put("msg", list.get(1).toString());
                m.put("data", "");
            } else {
                m.put("result", list.get(0));
                m.put("msg", "");
                m.put("data", list.get(2));
            }
            //map.put("DATA_" + i, m);
            resultList.add(m);
        }
        return ApiResponseResult.success().data(resultList);
    }

    public List getKanbanMenuPrc(String factory, String company) throws Exception {
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call  PRC_APP_KANBAN_MENU(?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, factory);
                cs.setString(2, company);
                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;
    }


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