package com.web.report.service.internal;

import com.app.base.data.ApiResponseResult;
import com.web.report.service.ReportService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.data.domain.PageRequest;
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 javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.Color;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
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(value = "ReportService")
@Transactional(propagation = Propagation.REQUIRED)
public class ReportImpl  implements ReportService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional
    public List<Object> getTreeList(String factory, String company, String in_str, String usercode, String ip, int page,int size) throws Exception{

        return this.doPrc("app_prc_rwd_retrospect", factory, company, in_str, usercode, ip, page, size);
    }

    //执行存储获取数据
    //itemname 物料名称,lifno 供应商编码， qty 数量, lot_no 物料批次，proc_name 工序名（追溯树索引），task_no 生产工单，parent_id  父ID ，child_id 子ID
    public List doPrc(String prc_name, String param, String param2, String param3, String param4, String param5, int param6, int param7){
        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, param);// 工厂
                cs.setString(2, param2);// 公司
                cs.setString(3, param3);// 批次条码
                cs.setString(4, param4);// 用户
                cs.setString(5, param5);// IP 地址
                cs.setInt(6, param6);// 每页记录数
                cs.setInt(7, param7);// 当前页码
                cs.registerOutParameter(8,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(9,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(10,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                //result.add(cs.getInt(8));
                //result.add(cs.getString(9));
                if(cs.getString(8).toString().endsWith("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(10);
                   
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("ITEMNAME", getEmpty(rs.getString("ITEMNAME")));
                        m.put("ITEM_BARCODE", getEmpty(rs.getString("ITEM_BARCODE")));
                        m.put("ITEM_NO", getEmpty(rs.getString("ITEM_NO")));
                        m.put("LIFNO", getEmpty(rs.getString("LIFNO")));
                        m.put("LOT_NO", getEmpty(rs.getString("LOT_NO")));
                        m.put("PROC_NAME", getEmpty(rs.getString("PROC_NAME")));
                        m.put("QTY", getEmpty(rs.getString("QTY")));
                        m.put("TASK_NO", getEmpty(rs.getString("TASK_NO")));
                        if(rs.getString("PARENT_ID").equals("1")){
                        	m.put("parentId", "0");
                        }else{
                        	m.put("parentId", getEmpty(rs.getString("PARENT_ID")));
                        }
                        
                        m.put("id", getEmpty(rs.getString("CHILD_ID")));
                        l.add(m);
                    }
                    result.add(l);
                }
                System.out.println(l);
                return l;
            }
        });

        return resultList;
    }

    

	@Override
	public ApiResponseResult getMaterialsList(String keyword, PageRequest pageRequest)
			throws Exception {
		// TODO Auto-generated method stub
		List<Object> list = this.doMaterialsListPrc("10000", "1000", keyword, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
		if(!list.get(0).toString().equals("0")){
			return ApiResponseResult.failure(list.get(1).toString());
		}
		//封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	//执行存储获取数据
    private List doMaterialsListPrc( String factory , String company, String lotno, int page, int size){
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call App_Zhuisu_Wuliao_Info(?,?,?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, lotno);// 批次条码
                cs.setInt(4, page);// 每页记录数
                cs.setInt(5, size);// 当前页码
                cs.registerOutParameter(6,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(7,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(8,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(9,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(7));
                result.add(cs.getString(8));
                if(cs.getString(7).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(9);
                  
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("PROC_NAME", getEmpty(rs.getString("PROC_NAME")));//工序
                        m.put("ITEM_BARCODE", getEmpty(rs.getString("ITEM_BARCODE")));
                        m.put("ITEM_NO", getEmpty(rs.getString("ITEM_NO")));
                        m.put("ITEM_NAME", getEmpty(rs.getString("ITEM_NAME")));
                        m.put("TR_LOTNO", getEmpty(rs.getString("TR_LOTNO")));
                        m.put("SUPP_NO", getEmpty(rs.getString("SUPP_NO")));
                        m.put("QUANTITY", getEmpty(rs.getString("QUANTITY")));
                        m.put("LOT_DATE", getEmpty(rs.getString("LOT_DATE")));
                        m.put("PROC_NO", getEmpty(rs.getString("PROC_NO")));
                        m.put("PLAN_QTY", getEmpty(rs.getString("PLAN_QTY")));
                        m.put("FPUT_QTY", getEmpty(rs.getString("FPUT_QTY")));
                        m.put("UNIT", getEmpty(rs.getString("UNIT")));
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(6));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

	@Override
	public ApiResponseResult getQualityList(String keyword, PageRequest pageRequest) throws Exception {
		// TODO Auto-generated method stub
		List<Object> list = this.doQualityListPrc("10000", "1000", keyword, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
		if(!list.get(0).toString().equals("0")){
			return ApiResponseResult.failure(list.get(1).toString());
		}
		//封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	//执行存储获取数据
    private List doQualityListPrc( String factory , String company, String lotno, int page, int size){
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call App_Zhuisu_Pinzhi_Info(?,?,?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, lotno);// 批次条码
                cs.setInt(4, page);// 每页记录数
                cs.setInt(5, size);// 当前页码
                cs.registerOutParameter(6,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(7,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(8,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(9,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(7));
                result.add(cs.getString(8));
                if(cs.getString(7).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(9);
                  
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("LOT_NO", getEmpty(rs.getString("LOT_NO")));//工序
                        m.put("PROC_NO", getEmpty(rs.getString("PROC_NO")));
                        m.put("PROC_NAME", getEmpty(rs.getString("PROC_NAME")));
                        m.put("FCHECK_ITEM", getEmpty(rs.getString("FCHECK_ITEM")));
                        m.put("VCHECK_RESU", getEmpty(rs.getString("VCHECK_RESU")));
                        m.put("FSECOND_RESU", getEmpty(rs.getString("FSECOND_RESU")));
                        m.put("FSTAND", getEmpty(rs.getString("FSTAND")));
                        m.put("FDOWN_ALLOW", getEmpty(rs.getString("FDOWN_ALLOW")));
                        m.put("FUP_ALLOW", getEmpty(rs.getString("FUP_ALLOW")));
                        m.put("FSPEC_REQU", getEmpty(rs.getString("FSPEC_REQU")));
                        
                        m.put("FCHECK_BY", getEmpty(rs.getString("FCHECK_BY")));
                        m.put("FCHECK_DATE", getEmpty(rs.getString("FCHECK_DATE")));
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(6));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

	@Override
	public ApiResponseResult getDeviceList(String keyword, PageRequest pageRequest) throws Exception {
		// TODO Auto-generated method stub
		List<Object> list = this.doDeviceListPrc("10000", "1000", keyword, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
		if(!list.get(0).toString().equals("0")){
			return ApiResponseResult.failure(list.get(1).toString());
		}
		//封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	//执行存储获取数据
    private List doDeviceListPrc( String factory , String company, String lotno, int page, int size){
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call App_Zhuisu_Shebei_Info(?,?,?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, lotno);// 批次条码
                cs.setInt(4, page);// 每页记录数
                cs.setInt(5, size);// 当前页码
                cs.registerOutParameter(6,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(7,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(8,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(9,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(7));
                result.add(cs.getString(8));
                if(cs.getString(7).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(9);
                  
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("PROC_NAME", getEmpty(rs.getString("PROC_NAME")));//工序
                        m.put("PROC_NO", getEmpty(rs.getString("PROC_NO")));
                        m.put("MACHINE_CODE", getEmpty(rs.getString("MACHINE_CODE")));
                        m.put("CREATE_BY", getEmpty(rs.getString("CREATE_BY")));
                        m.put("CREATE_DATE", getEmpty(rs.getString("CREATE_DATE")));
                        m.put("EQ_NAME", getEmpty(rs.getString("EQ_NAME")));
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(6));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

	@Override
	public ApiResponseResult getFixtureList(String keyword, PageRequest pageRequest) throws Exception {
		// TODO Auto-generated method stub
		List<Object> list = this.doFixtureListPrc("10000", "1000", keyword, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
		if(!list.get(0).toString().equals("0")){
			return ApiResponseResult.failure(list.get(1).toString());
		}
		//封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	//执行存储获取数据
    private List doFixtureListPrc( String factory , String company, String lotno, int page, int size){
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call App_Zhuisu_Gongzhuang_Info(?,?,?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, lotno);// 批次条码
                cs.setInt(4, page);// 每页记录数
                cs.setInt(5, size);// 当前页码
                cs.registerOutParameter(6,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(7,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(8,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(9,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(7));
                result.add(cs.getString(8));
                if(cs.getString(7).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(9);
                  
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("CREATE_DATE", getEmpty(rs.getString("CREATE_DATE")));//工序
                        m.put("CREATE_BY", getEmpty(rs.getString("CREATE_BY")));
                        m.put("M_CODE", getEmpty(rs.getString("M_CODE")));
                        m.put("EQ_NAME", getEmpty(rs.getString("EQ_NAME")));
                        m.put("S_CODE", getEmpty(rs.getString("S_CODE")));
                        m.put("CUT_NAME", getEmpty(rs.getString("CUT_NAME")));
                        m.put("DOWN_DATE", getEmpty(rs.getString("DOWN_DATE")));
                        m.put("DOWN_USER_BY", getEmpty(rs.getString("DOWN_USER_BY")));
                        m.put("NOW_TIME", getEmpty(rs.getString("NOW_TIME")));
                        m.put("AVAILABLE_TIME", getEmpty(rs.getString("AVAILABLE_TIME")));
                        m.put("TOTAL_TIME", getEmpty(rs.getString("TOTAL_TIME")));
                        m.put("LOTNO", getEmpty(rs.getString("LOTNO")));
                        m.put("PROC_NO", getEmpty(rs.getString("PROC_NO")));
                        m.put("PROC_NAME", getEmpty(rs.getString("PROC_NAME")));
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(6));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

	@Override
	public ApiResponseResult getHcfrList(String keyword, String ptype, PageRequest pageRequest) throws Exception {
		// TODO Auto-generated method stub
		List<Object> list = this.doHcfrListPrc("10000", "1000", keyword, ptype, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
		if(!list.get(0).toString().equals("0")){
			return ApiResponseResult.failure(list.get(1).toString());
		}
		//封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	//执行存储获取数据
    private List doHcfrListPrc( String factory , String company, String lotno, String type,int page, int size){
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call APP_HCFR_DETAIL(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
                CallableStatement cs = con.prepareCall(storedProc);
                cs.setString(1, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, lotno);// 批次条码
                cs.setString(4, type);// 类型(HC表示化成/FR表示分容)
                cs.setInt(5, page);// 每页记录数
                cs.setInt(6, size);// 当前页码
                cs.registerOutParameter(7,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(8,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(9,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(10,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(8));
                result.add(cs.getString(9));
                if(cs.getString(8).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(10);
                  
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("STARTTIME", getEmpty(rs.getString("STARTDATE")) + " "+getEmpty(rs.getString("STARTTIME")));
                        m.put("ENDTIME", getEmpty(rs.getString("ENDDATE")) + " "+getEmpty(rs.getString("ENDTIME")));
                        m.put("SSMODE", getEmpty(rs.getString("SSMODE")));
                        m.put("DEVID", getEmpty(rs.getString("DEVID")));
                        m.put("WOINDEX", getEmpty(rs.getString("WOINDEX")));
                        m.put("WONO", getEmpty(rs.getString("WONO")));
                        m.put("CYCNO", getEmpty(rs.getString("CYCNO")));
                        m.put("DEVDESC", getEmpty(rs.getString("DEVDESC")));
                        m.put("PALLETNUM", getEmpty(rs.getString("PALLETNUM")));
                        m.put("LOCATION", getEmpty(rs.getString("LOCATION")));
                        m.put("PASSAGEWAY", getEmpty(rs.getString("PASSAGEWAY")));
                        m.put("OPENV", getEmpty(rs.getString("OPENV")));
                        m.put("AVRV", getEmpty(rs.getString("AVRV")));
                        m.put("ENDV", getEmpty(rs.getString("ENDV")));
                        m.put("TVALUE", getEmpty(rs.getString("TVALUE")));
                        m.put("CVALUE", getEmpty(rs.getString("CVALUE")));
                        m.put("CHARGEI", getEmpty(rs.getString("CHARGEI")));
                        m.put("ENDI", getEmpty(rs.getString("ENDI")));
                        m.put("DISCHARGEI", getEmpty(rs.getString("DISCHARGEI")));
                        
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(7));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

	@Override
	public ApiResponseResult getFRList(String keyword, String stime, String etime,String ptype, PageRequest pageRequest)
			throws Exception {
		// TODO Auto-generated method stub
		if(getEmpty(ptype).equals("")){
			//封装数据
	        return ApiResponseResult.success().data(emtyMap());
		}
		List<Object> list = this.doFRListPrc("10000", "1000", keyword, stime,etime,ptype, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
		if(!list.get(0).toString().equals("0")){
			return ApiResponseResult.failure(list.get(1).toString());
		}
		//封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	private Map<String, Object> emtyMap(){
		Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", new ArrayList());
        map.put("page", 1);
        map.put("pageSize", 10);
        map.put("total", 0);
        return map;
	}
	
	//执行存储获取数据
    private List doFRListPrc( String factory , String company, String lotno, String stime,String etime,String ptype,int page, int size){
    	String prc_name = ptype.equals("HC")?"APP_HC_DATA":"APP_FR_DATA";
        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, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, lotno);// 批次条码
                cs.setString(4, stime);
                cs.setString(5, etime);
                cs.setInt(6, page);// 每页记录数
                cs.setInt(7, size);// 当前页码
                cs.registerOutParameter(8,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(9,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(10,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(11,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(9));
                result.add(cs.getString(10));
                if(cs.getString(9).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(11);
                  
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("BARCODE", getEmpty(rs.getString("BARCODE")));
                        if( ptype.equals("FR")){
                        	m.put("CAPACITY", getEmpty(rs.getString("CAPACITY")));
                        }
                        m.put("OPENV", getEmpty(rs.getString("OPENV")));
                        m.put("AVGV", getEmpty(rs.getString("AVGV")));
                        m.put("ENDV", getEmpty(rs.getString("ENDV")));
                        m.put("STARTDATE", getEmpty(rs.getString("STARTDATE")));
                        m.put("ENDDATE", getEmpty(rs.getString("ENDDATE")));
                        m.put("PASSAGEWAY", getEmpty(rs.getString("PASSAGEWAY")));
                        m.put("FIP", getEmpty(rs.getString("FIP")));
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(8));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

	@Override
	public ApiResponseResult getOCVList(String prc_name,String keyword, String stime, String etime, String lineno,String  pmodel,
			PageRequest pageRequest) throws Exception {
		// TODO Auto-generated method stub
		if(getEmpty(stime).equals("") &&getEmpty(etime).equals("")){
			//封装数据
	        return ApiResponseResult.success().data(emtyMap());
		}
		//"APP_OCV1_DATA"
		List<Object> list = this.doOCVListPrc(prc_name,"10000", "1000", keyword, stime,etime,lineno, pmodel,pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
		System.out.println(list);
		if(!list.get(0).toString().equals("0")){
			return ApiResponseResult.failure(list.get(1).toString()).data(emtyMap());
		}
		//封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}

	@Override
	public ApiResponseResult getOCV2List(String keyword, String stime, String etime, String lineno,String  pmodel,
			PageRequest pageRequest) throws Exception {
		// TODO Auto-generated method stub
		if(getEmpty(stime).equals("") &&getEmpty(etime).equals("")){
			//封装数据
	        return ApiResponseResult.success().data(emtyMap());
		}
		List<Object> list = this.doOCVListPrc("APP_OCV2_DATA","10000", "1000", keyword, stime,etime,lineno,pmodel, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
		if(!list.get(0).toString().equals("0")){
			return ApiResponseResult.failure(list.get(1).toString()).data(emtyMap());
		}
		//封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	//执行存储获取数据
    private List doOCVListPrc(String prc_name, String factory , String company, String lotno, String stime,String etime,String lineno,String pmodel,int page, int size){
        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, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, lotno);// 批次条码
                cs.setString(4, lineno);
                cs.setString(5, pmodel);
                cs.setString(6, stime);
                cs.setString(7, etime);
                cs.setInt(8, page);// 每页记录数
                cs.setInt(9, size);// 当前页码
                cs.registerOutParameter(10,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(11,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(12,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(13,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(11));
                result.add(cs.getString(12));
                if(cs.getString(11).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(13);
                  
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("PRO_MODEL", getEmpty(rs.getString("PRO_MODEL")));
                        m.put("MODEL", getEmpty(rs.getString("MODEL")));
                        m.put("SN", getEmpty(rs.getString("SN")));
                        if(prc_name.equals("APP_OCV1_DATA")){
                        	 m.put("OCT1", getEmpty(rs.getString("OCT1")));
                        	 m.put("OCV1RESULT", getEmpty(rs.getString("OCV1RESULT")));
                            m.put("OCV1", getEmpty(rs.getString("OCV1")));
                            m.put("OCV1MIN", getEmpty(rs.getString("OCV1MIN")));
                            m.put("OCV1MAX", getEmpty(rs.getString("OCV1MAX")));
                            m.put("VOL1RESULT", getEmpty(rs.getString("VOL1RESULT")));
                            m.put("OCR1", getEmpty(rs.getString("OCR1")));
                            m.put("IMP1MIN", getEmpty(rs.getString("IMP1MIN")));
                            m.put("IMP1MAX", getEmpty(rs.getString("IMP1MAX")));
                            m.put("IMP1RESULT", getEmpty(rs.getString("IMP1RESULT")));
                            m.put("THICKNESS1", getEmpty(rs.getString("THICKNESS1")));
                            m.put("THICKNESS1MIN", getEmpty(rs.getString("THICKNESS1MIN")));
                            m.put("THICKNESS1MAX", getEmpty(rs.getString("THICKNESS1MAX")));
                            m.put("THICRESULT1", getEmpty(rs.getString("THICRESULT1")));
                            m.put("LINENO", getEmpty(rs.getString("LINENO")));
                        }else if(prc_name.equals("APP_OCV2_DATA")){
                        	m.put("OCT2", getEmpty(rs.getString("OCT2")));
                        	m.put("OCV2RESULT", getEmpty(rs.getString("OCV2RESULT")));
                        	m.put("OCV2", getEmpty(rs.getString("OCV2")));
                        	m.put("OCV2MIN", getEmpty(rs.getString("OCV2MIN")));
                        	m.put("OCV2MAX", getEmpty(rs.getString("OCV2MAX")));
                        	m.put("VOL2RESULT", getEmpty(rs.getString("VOL2RESULT")));
                        	m.put("OCR2", getEmpty(rs.getString("OCR2")));
                        	m.put("IMP2MIN", getEmpty(rs.getString("IMP2MIN")));
                        	m.put("IMP2MAX", getEmpty(rs.getString("IMP2MAX")));
                        	m.put("IMP2RESULT", getEmpty(rs.getString("IMP2RESULT")));
                        	m.put("K12", getEmpty(rs.getString("K12")));
                        	m.put("K2MIN", getEmpty(rs.getString("K2MIN")));
                        	m.put("K2MAX", getEmpty(rs.getString("K2MAX")));
                        	m.put("K12RESULT", getEmpty(rs.getString("K12RESULT")));
                        	m.put("THICKNESS2", getEmpty(rs.getString("THICKNESS2")));
                        	m.put("THICKNESS2MIN", getEmpty(rs.getString("THICKNESS2MIN")));
                        	m.put("THICKNESS2MAX", getEmpty(rs.getString("THICKNESS2MAX")));
                        	m.put("THICRESULT2", getEmpty(rs.getString("THICRESULT2")));
                        	m.put("LINENO", getEmpty(rs.getString("LINENO")));
                        }else if(prc_name.equals("APP_OCV3_DATA")){
                        	m.put("OCT3", getEmpty(rs.getString("OCT3")));
                        	m.put("OCV3RESULT", getEmpty(rs.getString("OCV3RESULT")));
                        	m.put("OCV3", getEmpty(rs.getString("OCV3")));
                        	m.put("OCV3MIN", getEmpty(rs.getString("OCV3MIN")));
                        	m.put("OCV3MAX", getEmpty(rs.getString("OCV3MAX")));
                        	m.put("VOL3RESULT", getEmpty(rs.getString("VOL3RESULT")));
                        	m.put("OCR3", getEmpty(rs.getString("OCR3")));
                        	m.put("IMP3MIN", getEmpty(rs.getString("IMP3MIN")));
                        	m.put("IMP3MAX", getEmpty(rs.getString("IMP3MAX")));
                        	m.put("IMP3RESULT", getEmpty(rs.getString("IMP3RESULT")));
                        	m.put("K13", getEmpty(rs.getString("K13")));
                        	m.put("K3MIN", getEmpty(rs.getString("K3MIN")));
                        	m.put("K3MAX", getEmpty(rs.getString("K3MAX")));
                        	m.put("K13RESULT", getEmpty(rs.getString("K13RESULT")));
                        	m.put("THICKNESS3", getEmpty(rs.getString("THICKNESS3")));
                        	m.put("THICKNESS3MIN", getEmpty(rs.getString("THICKNESS3MIN")));
                        	m.put("THICKNESS3MAX", getEmpty(rs.getString("THICKNESS3MAX")));
                        	m.put("THICRESULT3", getEmpty(rs.getString("THICRESULT3")));
                        	m.put("LINENO", getEmpty(rs.getString("LINENO")));
                        }else if(prc_name.equals("APP_OCV4_DATA")){
                        	m.put("OCT4", getEmpty(rs.getString("OCT4")));
                        	m.put("OCV4RESULT", getEmpty(rs.getString("OCV4RESULT")));
                        	m.put("OCV4", getEmpty(rs.getString("OCV4")));
                        	m.put("OCV4MIN", getEmpty(rs.getString("OCV4MIN")));
                        	m.put("OCV4MAX", getEmpty(rs.getString("OCV4MAX")));
                        	m.put("VOL4RESULT", getEmpty(rs.getString("VOL4RESULT")));
                        	m.put("OCR4", getEmpty(rs.getString("OCR4")));
                        	m.put("IMP4MIN", getEmpty(rs.getString("IMP4MIN")));
                        	m.put("IMP4MAX", getEmpty(rs.getString("IMP4MAX")));
                        	m.put("IMP4RESULT", getEmpty(rs.getString("IMP4RESULT")));
                        	m.put("K14", getEmpty(rs.getString("K14")));
                        	m.put("K4MIN", getEmpty(rs.getString("K4MIN")));
                        	m.put("K4MAX", getEmpty(rs.getString("K4MAX")));
                        	m.put("K14RESULT", getEmpty(rs.getString("K14RESULT")));
                        	m.put("THICKNESS4", getEmpty(rs.getString("THICKNESS4")));
                        	m.put("THICKNESS4MIN", getEmpty(rs.getString("THICKNESS4MIN")));
                        	m.put("THICKNESS4MAX", getEmpty(rs.getString("THICKNESS4MAX")));
                        	m.put("THICRESULT4", getEmpty(rs.getString("THICRESULT4")));
                        	m.put("LINENO", getEmpty(rs.getString("LINENO")));
                        }
                        
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(10));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

	@Override
	public ApiResponseResult getLineList() throws Exception {
		// TODO Auto-generated method stub
		return ApiResponseResult.success().data(this.doLineListPrc("10000", "1000").get(2));
	}
	
	//执行存储获取数据
    private List doLineListPrc(String factory , String company){
        List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{call  app_lineno_info(?,?,?,?,?)}";// 调用的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 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);
                  
                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("LINE_NO", getEmpty(rs.getString("LINE_NO")));
                        l.add(m);
                    }
                    result.add(l);
                }

                return result;
            }
        });

        return resultList;
    }

    //获取物料条码交易明细
    @Override
    @Transactional
    public ApiResponseResult getJYList(String keyword,String stime,String etime, PageRequest pageRequest) throws Exception{
        //获取数据
        List<Object> list = this.doJYListPrc("APP_WULIAO_JIAOYI_JL","10000", "1000", keyword, stime, etime, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
        if(!list.get(0).toString().equals("0")){
            return ApiResponseResult.failure(list.get(1).toString());
        }
        //封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
    }
    //执行存储获取数据——获取物料条码交易明细
    private List doJYListPrc(String prc_name, String factory, String company, String barcode, String stime,String etime,int page, int size){
        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, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, barcode);// 物料或者条码
                cs.setString(4, stime);// 物料或者条码
                cs.setString(5, etime);// 物料或者条码
                cs.setInt(6, page);// 每页记录数
                cs.setInt(7, size);// 当前页码
                cs.registerOutParameter(8,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(9,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(10,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(11,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(9));
                result.add(cs.getString(10));
                if(cs.getString(9).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(11);

                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("BILL_NO", getEmpty(rs.getString("BILL_NO")));//单号
                        m.put("ITEM_BARCODE", getEmpty(rs.getString("ITEM_BARCODE")));//物料条码
                        m.put("ITEM_NO", getEmpty(rs.getString("ITEM_NO")));//物料编码
                        m.put("ITEM_DESCRIPTION", getEmpty(rs.getString("ITEM_DESCRIPTION")));//物料描述
                        m.put("QUANTITY", getEmpty(rs.getString("QUANTITY")));//物料数量
                        m.put("ITEM_UNIT", getEmpty(rs.getString("ITEM_UNIT")));//单位
                        m.put("LOT_NO", getEmpty(rs.getString("LOT_NO")));//批次
                        m.put("TO_DEPOTNO", getEmpty(rs.getString("TO_DEPOTNO")));//入库仓库
                        m.put("FROM_DEPOTNO", getEmpty(rs.getString("FROM_DEPOTNO")));//出库仓库
                        m.put("BILL_TYPE_NAME", getEmpty(rs.getString("BILL_TYPE_NAME")));//单据类型
                        m.put("TRANSACTION_NAME", getEmpty(rs.getString("TRANSACTION_NAME")));//事务类型
                        m.put("CREATE_BY", getEmpty(rs.getString("CREATE_BY")));//操作人
                        m.put("CREATE_DATE", getEmpty(rs.getString("CREATE_DATE")));//操作日期
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(8));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

    //获取物料条码库存明细
    @Override
    @Transactional
    public ApiResponseResult getKCList(String keyword, PageRequest pageRequest) throws Exception{
        //获取数据
        List<Object> list = this.doKCListPrc("APP_WULIAO_KUCUN_JL","10000", "1000", keyword, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
        if(!list.get(0).toString().equals("0")){
            return ApiResponseResult.failure(list.get(1).toString());
        }
        //封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
    }
    //执行存储获取数据——获取物料条码库存明细
    private List doKCListPrc(String prc_name, String factory, String company, String barcode, int page, int size){
        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, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, barcode);// 物料或者条码
                cs.setInt(4, page);// 每页记录数
                cs.setInt(5, size);// 当前页码
                cs.registerOutParameter(6,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(7,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(8,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(9,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(7));
                result.add(cs.getString(8));
                if(cs.getString(7).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(9);

                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("ITEM_BARCODE", getEmpty(rs.getString("ITEM_BARCODE")));//物料条码
                        m.put("ITEM_NO", getEmpty(rs.getString("ITEM_NO")));//物料编码
                        m.put("LOT_NO", getEmpty(rs.getString("LOT_NO")));//批次
                        m.put("QUANTITY", getEmpty(rs.getString("QUANTITY")));//物料数量
                        m.put("TR_LOTNO", getEmpty(rs.getString("TR_LOTNO")));//供应商批次
                        m.put("DEPOT_NAME", getEmpty(rs.getString("DEPOT_NAME")));//仓库
                        m.put("PROD_DATE", getEmpty(rs.getString("PROD_DATE")));//生产日期
                        m.put("INDEP_DATE", getEmpty(rs.getString("INDEP_DATE")));//入库日期
                        m.put("ITEM_DESCRIPTION", getEmpty(rs.getString("ITEM_DESCRIPTION")));//物料名称
                        m.put("SUPP_NAME", getEmpty(rs.getString("SUPP_NAME")));//供应商
                        m.put("DAYS", getEmpty(rs.getString("DAYS")));//在库时间
                        m.put("TO_EXTDATE", getEmpty(rs.getString("TO_EXTDATE")));//到期日期
                        m.put("UNIT", getEmpty(rs.getString("UNIT")));//单位
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(6));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

    //获取半成品条码库存明细
    @Override
    @Transactional
    public ApiResponseResult getBCPList(String keyword, PageRequest pageRequest) throws Exception{
        //获取数据
        List<Object> list = this.doBCPListPrc("APP_BANCHENGPING_KUCUN","10000", "1000", keyword, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
        if(!list.get(0).toString().equals("0")){
            return ApiResponseResult.failure(list.get(1).toString());
        }
        //封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
    }
    //执行存储获取数据——获取半成品条码库存明细
    private List doBCPListPrc(String prc_name, String factory, String company, String barcode, int page, int size){
        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, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, barcode);// 物料或者条码
                cs.setInt(4, page);// 每页记录数
                cs.setInt(5, size);// 当前页码
                cs.registerOutParameter(6,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(7,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(8,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(9,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(7));
                result.add(cs.getString(8));
                if(cs.getString(7).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(9);

                    while(rs.next()){
                        Map m = new HashMap();
                        m.put("ITEM_BARCODE", getEmpty(rs.getString("ITEM_BARCODE")));//批次条码
                        m.put("ITEM_NO", getEmpty(rs.getString("ITEM_NO")));//产品编码
                        m.put("LOT_NO", getEmpty(rs.getString("LOT_NO")));//批次
                        m.put("QUANTITY", getEmpty(rs.getString("QUANTITY")));//条码数量
                        m.put("OLDQTY", getEmpty(rs.getString("OLDQTY")));//剩余数量
                        m.put("MOCODE", getEmpty(rs.getString("MOCODE")));//状态
                        m.put("LIFNO", getEmpty(rs.getString("LIFNO")));//投料单
                        m.put("PROC_NO", getEmpty(rs.getString("PROC_NO")));//工序
                        m.put("LINE_NO", getEmpty(rs.getString("LINE_NO")));//线体
                        m.put("FBZ", getEmpty(rs.getString("FBZ")));//备注
                        m.put("ITEM_NAME", getEmpty(rs.getString("ITEM_NAME")));//产品名称
                        m.put("ITEM_MODEL", getEmpty(rs.getString("ITEM_MODEL")));//产品规格
                        m.put("CREATE_DATE", getEmpty(rs.getString("CREATE_DATE")));//入库日期
                        m.put("PROC_NAME", getEmpty(rs.getString("PROC_NAME")));//工序名称
                        m.put("HH", getEmpty(rs.getString("HH")));//在线时长
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(6));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }

	@Override
	public ApiResponseResult getCom1List(String prc_name,String pname, String keyword, String stime, String etime,
			PageRequest pageRequest) throws Exception {
		// TODO Auto-generated method stub
		//获取数据
		//获取数据
        List<Object> list = this.doCom1ListPrc(prc_name,pname,"10000", "1000", keyword, stime, etime, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
        if(!list.get(0).toString().equals("0")){
            return ApiResponseResult.failure(list.get(1).toString());
        }
        //封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	//执行存储获取数据
    private List doCom1ListPrc(String prc_name,String pname, String factory, String company, String keyword, String stime,String etime,int page, int size){
        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, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, stime);// 开始时间
                cs.setString(4, etime);// 结束时间
                cs.setString(5, keyword);// 
                cs.setString(6, pname);// 工序
                cs.setInt(7, page);// 每页记录数
                cs.setInt(8, size);// 当前页码
                cs.registerOutParameter(9,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(10,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(11,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(12,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(10));
                result.add(cs.getString(11));
                if(cs.getString(10).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(12);

                    while(rs.next()){
                        Map m = ImpUtils.getMapByPro(prc_name, rs);
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(9));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }
    
    @Override
	public ApiResponseResult getComList(String prc_name,String pname,  String stime, String etime,
			PageRequest pageRequest) throws Exception {
		// TODO Auto-generated method stub
		//获取数据
		//获取数据
        List<Object> list = this.doComListPrc(prc_name,pname,"10000", "1000",  stime, etime, pageRequest.getPageSize(), pageRequest.getPageNumber()+1);
        if(!list.get(0).toString().equals("0")){
            return ApiResponseResult.failure(list.get(1).toString());
        }
        //封装数据
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.get(2));
        map.put("page", pageRequest.getPageNumber() + 1);
        map.put("pageSize", pageRequest.getPageSize());
        map.put("total", list.get(3));

        return ApiResponseResult.success().data(map);
	}
	//执行存储获取数据
    private List doComListPrc(String prc_name,String pname, String factory, String company,  String stime,String etime,int page, int size){
        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, factory);// 工厂
                cs.setString(2, company);// 公司
                cs.setString(3, stime);// 开始时间
                cs.setString(4, etime);// 结束时间
                cs.setString(5, pname);// 
                cs.setInt(6, page);// 每页记录数
                cs.setInt(7, size);// 当前页码
                cs.registerOutParameter(8,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(9,java.sql.Types.INTEGER);// 输出参数 返回标识
                cs.registerOutParameter(10,java.sql.Types.VARCHAR);// 输出参数 返回错误信息
                cs.registerOutParameter(11,-10);// 输出参数 追溯数据
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                List<Object> result = new ArrayList<>();
                List l = new ArrayList();
                cs.execute();
                result.add(cs.getInt(9));
                result.add(cs.getString(10));
                if(cs.getString(9).toString().equals("0")){
                    //游标处理
                    ResultSet rs = (ResultSet)cs.getObject(11);

                    while(rs.next()){
                        Map m = ImpUtils.getMapByPro(prc_name, rs);
                        l.add(m);
                    }
                    result.add(l);
                }
                result.add(cs.getString(8));
                System.out.println(l);
                return result;
            }
        });

        return resultList;
    }
    
    //值为"null"或者null转换成""
    private String getEmpty(String str){
        if(str == null){
            return "";
        }
        if(StringUtils.equals("null", str)){
            return "";
        }

        String[] strs = str.split("\\.");
        if(strs.length > 0){
        	if(strs[0].equals("") || strs[0]==null){
        		return "0"+str;
        	}
        }
        return str;
    }

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

	@Override
    public ApiResponseResult getExcel(String keyword, HttpServletResponse response) throws Exception{
        try{
            //1.获取信息
            //1.1获取物料信息
            List<Map<String, Object>> list1 = new ArrayList<>();
            try{
                List<Object> mateList = this.doMaterialsListPrc("10000", "1000", keyword, 10000, 1);
                list1 = (List<Map<String, Object>>) mateList.get(2);
            }catch (Exception e){
            }

            //1.2获取品质信息
            List<Map<String, Object>> list2 = new ArrayList<>();
            try{
                List<Object> qualityList = this.doQualityListPrc("10000", "1000", keyword, 10000, 1);
                list2 = (List<Map<String, Object>>) qualityList.get(2);
            }catch (Exception e){
            }

            //1.3获取人员设备信息
            List<Map<String, Object>> list3 = new ArrayList<>();
            try{
                List<Object> deviceList = this.doDeviceListPrc("10000", "1000", keyword, 10000, 1);
                list3 = (List<Map<String, Object>>) deviceList.get(2);
            }catch(Exception e){
            }

            //1.4获取工装夹具信息
            List<Map<String, Object>> list4 = new ArrayList<>();
            try{
                List<Object> fixtureList = this.doFixtureListPrc("10000", "1000", keyword, 10000, 1);
                list4 = (List<Map<String, Object>>) fixtureList.get(2);
            }catch (Exception e){
            }

            //2.创建Excel文件
            OutputStream outputStream = response.getOutputStream();
            XSSFWorkbook workbook = new XSSFWorkbook();   //创建一个工作簿
            //Sheet sheet = workbook.createSheet("物料信息");
            List<XSSFCellStyle> cellStyleList = this.getStyle(workbook);
            //List<String> headerList = new ArrayList<String>(); //初始化
            //List<List<String>> bodyList = new ArrayList<>();//初始化

            //2.1创建物料信息Sheet页
            workbook = this.getMaterialsSheet(workbook, list1, cellStyleList);

            //2.2创建品质信息Sheet页
            workbook = this.getQualitySheet(workbook, list2, cellStyleList);

            //2.3创建人员设备信息Sheet页
            workbook = this.getDeviceSheet(workbook, list3, cellStyleList);

            //2.4创建工装夹具信息Sheet页
            workbook = this.getFixtureSheet(workbook, list4, cellStyleList);

            response.reset();
            response.setContentType("multipart/form-data");
            String fileName = URLEncoder.encode("全流程追溯报表" + keyword, "UTF-8")+ ".xlsx";
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);
            workbook.write(outputStream);

            return ApiResponseResult.success("导出成功！");
        }catch (Exception e){
            return ApiResponseResult.failure("导出失败！");
        }
    }

    //获取物料信息Sheet页
    private XSSFWorkbook getMaterialsSheet(XSSFWorkbook workbook, List<Map<String, Object>> list1, List<XSSFCellStyle> cellStyleList){
        Sheet sheet = workbook.createSheet("物料信息");
        List<String> headerList = new ArrayList<String>(); //初始化
        List<List<String>> bodyList = new ArrayList<>();//初始化

        if(list1.size() > 0){
            for(int i = 0; i < list1.size(); i++){
                Map<String, Object> map = list1.get(i);
                List<String> body = new ArrayList<>();
                if(map != null){
                    body.add(String.valueOf(i + 1));//序号
                    body.add(map.get("PROC_NO")!=null ? map.get("PROC_NO").toString() : "");//工序编号
                    body.add(map.get("PROC_NAME")!=null ? map.get("PROC_NAME").toString() : "");//工序
                    body.add(map.get("ITEM_BARCODE")!=null ? map.get("ITEM_BARCODE").toString() : "");//物料条码
                    body.add(map.get("ITEM_NO")!=null ? map.get("ITEM_NO").toString() : "");//物料编号
                    body.add(map.get("ITEM_NAME")!=null ? map.get("ITEM_NAME").toString() : "");//物料名称
                    body.add(map.get("TR_LOTNO")!=null ? map.get("TR_LOTNO").toString() : "");//物料批次
                    body.add(map.get("SUPP_NO")!=null ? map.get("SUPP_NO").toString() : "");//供应商
                    body.add(map.get("QUANTITY")!=null ? map.get("QUANTITY").toString() : "");//物料用量
                    body.add(map.get("UNIT")!=null ? map.get("UNIT").toString() : "");//单位
                    body.add(map.get("LOT_DATE")!=null ? map.get("LOT_DATE").toString() : "");//物料生产日期
                    body.add(map.get("PLAN_QTY")!=null ? map.get("PLAN_QTY").toString() : "");//工单计划数量
                    body.add(map.get("FPUT_QTY")!=null ? map.get("FPUT_QTY").toString() : "");//本次生产数量
                    bodyList.add(body);
                }
            }
        }

        //创建表头信息
        headerList.add("序号");//1
        headerList.add("工序编号");//2
        headerList.add("工序");//3
        headerList.add("物料条码");//4
        headerList.add("物料编号");//5
        headerList.add("物料名称");//6
        headerList.add("物料批次");//7
        headerList.add("供应商");//8
        headerList.add("物料用量");//9
        headerList.add("单位");//10
        headerList.add("物料生产日期");//11
        headerList.add("工单计划数量");//12
        headerList.add("本次生产数量");//13

        //创建行（标题）
        Row createRow = sheet.createRow(0);
        for(int i = 0; i < headerList.size(); i++){
            createRow.createCell(i);
        }
        //设置行高
        sheet.getRow(0).setHeightInPoints((float) 25);
        //添加样式和数据
        for(int i = 0; i < 1; i++){
            Cell cell = sheet.getRow(0).getCell(0);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("物料信息");
            cell.setCellStyle(cellStyleList.get(2));
        }
        //合并单元格
        CellRangeAddress region1 = new CellRangeAddress(0,0,0,headerList.size()-1);
        sheet.addMergedRegion(region1);
        RegionUtil.setBorderTop(CellStyle.BORDER_THIN, region1, sheet, workbook);
        RegionUtil.setBorderRight(CellStyle.BORDER_THIN, region1, sheet, workbook);
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, region1, sheet, workbook);
        RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, region1, sheet, workbook);

        //创建行（表头）
        Row createRow1 = sheet.createRow(1);
        for(int i = 0; i < headerList.size(); i++){
            createRow1.createCell(i);
        }
        //设置列宽
        for(int i = 0; i < headerList.size(); i++){
            if(headerList.get(i).equals("物料条码")){
                sheet.setColumnWidth(i, 30*256);
            }else if(headerList.get(i).equals("物料编号")|| headerList.get(i).equals("物料名称")){
                sheet.setColumnWidth(i, 20*256);
            }else if( headerList.get(i).equals("物料批次") || headerList.get(i).equals("工单计划数量") || headerList.get(i).equals("本次生产数量")){
                sheet.setColumnWidth(i, 15*256);
            }else{
                sheet.setColumnWidth(i, 12*256);
            }
        }
        //添加样式和数据
        for(int i = 0; i < headerList.size(); i++){
            Cell cell = sheet.getRow(1).getCell(i);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(headerList.get(i));
            cell.setCellStyle(cellStyleList.get(0));
        }

        //创建表内容信息
        for(int i = 0; i < bodyList.size(); i++){
            Row createRow2 = sheet.createRow(i + 2);
            for(int j = 0; j < headerList.size(); j++){
                createRow2.createCell(j);
            }
            //设置行高
            //sheet.getRow(i + 2).setHeightInPoints((float) 15.8);
            //添加样式和数据
            for(int k = 0; k < headerList.size(); k++){
                Cell cell = sheet.getRow(i + 2).getCell(k);
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(bodyList.size() <= 0 ? "" : bodyList.get(i).get(k));
                cell.setCellStyle(cellStyleList.get(1));
            }
        }

        return workbook;
    }

    //获取品质信息Sheet页
    private XSSFWorkbook getQualitySheet(XSSFWorkbook workbook, List<Map<String, Object>> list2, List<XSSFCellStyle> cellStyleList){
        Sheet sheet2 = workbook.createSheet("品质信息");
        List<String> headerList = new ArrayList<String>(); //初始化
        List<List<String>> bodyList = new ArrayList<>();//初始化

        if(list2.size() > 0){
            for(int i = 0; i < list2.size(); i++){
                Map<String, Object> map = list2.get(i);
                List<String> body = new ArrayList<>();
                if(map != null){
                    body.add(String.valueOf(i + 1));//序号
                    body.add(map.get("LOT_NO")!=null ? map.get("LOT_NO").toString() : "");//批次号
                    body.add(map.get("PROC_NO")!=null ? map.get("PROC_NO").toString() : "");//工序编号
                    body.add(map.get("PROC_NAME")!=null ? map.get("PROC_NAME").toString() : "");//工序
                    body.add(map.get("FCHECK_ITEM")!=null ? map.get("FCHECK_ITEM").toString() : "");//首件项目
                    body.add(map.get("FSTAND")!=null ? map.get("FSTAND").toString() : "");//标准值
                    body.add(map.get("FDOWN_ALLOW")!=null ? map.get("FDOWN_ALLOW").toString() : "");//下公差
                    body.add(map.get("FUP_ALLOW")!=null ? map.get("FUP_ALLOW").toString() : "");//上公差
                    body.add(map.get("FSPEC_REQU")!=null ? map.get("FSPEC_REQU").toString() : "");//单位
                    body.add(map.get("VCHECK_RESU")!=null ? map.get("VCHECK_RESU").toString() : "");//检验值
                    body.add(map.get("FSECOND_RESU")!=null ? map.get("FSECOND_RESU").toString() : "");//检验结果
                    body.add(map.get("FCHECK_BY")!=null ? map.get("FCHECK_BY").toString() : "");//检验人员
                    body.add(map.get("FCHECK_DATE")!=null ? map.get("FCHECK_DATE").toString() : "");//检验时间
                    bodyList.add(body);
                }
            }
        }

        //创建表头信息
        headerList.add("序号");//1
        headerList.add("批次号");//2
        headerList.add("工序编号");//3
        headerList.add("工序");//4
        headerList.add("首件项目");//5
        headerList.add("标准值");//6
        headerList.add("下公差");//7
        headerList.add("上公差");//8
        headerList.add("单位");//9
        headerList.add("检验值");//10
        headerList.add("检验结果");//11
        headerList.add("检验人员");//12
        headerList.add("检验时间");//13

        //创建行（标题）
        Row createRow = sheet2.createRow(0);
        for(int i = 0; i < headerList.size(); i++){
            createRow.createCell(i);
        }
        //设置行高
        sheet2.getRow(0).setHeightInPoints((float) 25);
        //添加样式和数据
        for(int i = 0; i < 1; i++){
            Cell cell = sheet2.getRow(0).getCell(0);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("品质信息");
            cell.setCellStyle(cellStyleList.get(2));
        }
        //合并单元格
        CellRangeAddress region1 = new CellRangeAddress(0,0,0,headerList.size()-1);
        sheet2.addMergedRegion(region1);
        RegionUtil.setBorderTop(CellStyle.BORDER_THIN, region1, sheet2, workbook);
        RegionUtil.setBorderRight(CellStyle.BORDER_THIN, region1, sheet2, workbook);
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, region1, sheet2, workbook);
        RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, region1, sheet2, workbook);
        //创建行（表头）
        Row createRow1 = sheet2.createRow(1);
        for(int i = 0; i < headerList.size(); i++){
            createRow1.createCell(i);
        }
        //设置列宽
        for(int i = 0; i < headerList.size(); i++){
            if(headerList.get(i).equals("批次号") || headerList.get(i).equals("检验时间")){
                sheet2.setColumnWidth(i, 20*256);
            }else{
                sheet2.setColumnWidth(i, 12*256);
            }
        }
        //添加样式和数据
        for(int i = 0; i < headerList.size(); i++){
            Cell cell = sheet2.getRow(1).getCell(i);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(headerList.get(i));
            cell.setCellStyle(cellStyleList.get(0));
        }
        //创建表内容信息
        for(int i = 0; i < bodyList.size(); i++){
            Row createRow2 = sheet2.createRow(i + 2);
            for(int j = 0; j < headerList.size(); j++){
                createRow2.createCell(j);
            }
            //设置行高
            //sheet2.getRow(i + 2).setHeightInPoints((float) 15.8);
            //添加样式和数据
            for(int k = 0; k < headerList.size(); k++){
                Cell cell = sheet2.getRow(i + 2).getCell(k);
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(bodyList.size() <= 0 ? "" : bodyList.get(i).get(k));
                cell.setCellStyle(cellStyleList.get(1));
            }
        }

        return workbook;
    }

    //获取人员设备信息Sheet页
    private XSSFWorkbook getDeviceSheet(XSSFWorkbook workbook, List<Map<String, Object>> list3, List<XSSFCellStyle> cellStyleList){
        Sheet sheet3 = workbook.createSheet("人员设备信息");
        List<String> headerList = new ArrayList<String>(); //初始化
        List<List<String>> bodyList = new ArrayList<>();//初始化

        if(list3.size() > 0){
            for(int i = 0; i < list3.size(); i++){
                Map<String, Object> map = list3.get(i);
                List<String> body = new ArrayList<>();
                if(map != null){
                    body.add(String.valueOf(i + 1));//序号
                    body.add(map.get("PROC_NAME")!=null ? map.get("PROC_NAME").toString() : "");//工序
                    body.add(map.get("PROC_NO")!=null ? map.get("PROC_NO").toString() : "");//工序编号
                    body.add(map.get("MACHINE_CODE")!=null ? map.get("MACHINE_CODE").toString() : "");//设备编号
                    body.add(map.get("EQ_NAME")!=null ? map.get("EQ_NAME").toString() : "");//设备名称
                    body.add(map.get("CREATE_BY")!=null ? map.get("CREATE_BY").toString() : "");//作业员
                    body.add(map.get("CREATE_DATE")!=null ? map.get("CREATE_DATE").toString() : "");//生产时间
                    bodyList.add(body);
                }
            }
        }

        //创建表头信息
        headerList.add("序号");//1
        headerList.add("工序");//2
        headerList.add("工序编号");//3
        headerList.add("设备编号");//4
        headerList.add("设备名称");//5
        headerList.add("作业员");//6
        headerList.add("生产时间");//7

        //创建行（标题）
        Row createRow = sheet3.createRow(0);
        for(int i = 0; i < headerList.size(); i++){
            createRow.createCell(i);
        }
        //设置行高
        sheet3.getRow(0).setHeightInPoints((float) 25);
        //添加样式和数据
        for(int i = 0; i < 1; i++){
            Cell cell = sheet3.getRow(0).getCell(0);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("人员设备信息");
            cell.setCellStyle(cellStyleList.get(2));
        }
        //合并单元格
        CellRangeAddress region1 = new CellRangeAddress(0,0,0,headerList.size()-1);
        sheet3.addMergedRegion(region1);
        RegionUtil.setBorderTop(CellStyle.BORDER_THIN, region1, sheet3, workbook);
        RegionUtil.setBorderRight(CellStyle.BORDER_THIN, region1, sheet3, workbook);
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, region1, sheet3, workbook);
        RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, region1, sheet3, workbook);
        //创建行（表头）
        Row createRow1 = sheet3.createRow(1);
        for(int i = 0; i < headerList.size(); i++){
            createRow1.createCell(i);
        }
        //设置列宽
        for(int i = 0; i < headerList.size(); i++){
            if(headerList.get(i).equals("设备名称") || headerList.get(i).equals("生产时间")){
                sheet3.setColumnWidth(i, 20*256);
            }else if(headerList.get(i).equals("工序") || headerList.get(i).equals("设备编号")){
                sheet3.setColumnWidth(i, 15*256);
            }else{
                sheet3.setColumnWidth(i, 12*256);
            }
        }
        //添加样式和数据
        for(int i = 0; i < headerList.size(); i++){
            Cell cell = sheet3.getRow(1).getCell(i);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(headerList.get(i));
            cell.setCellStyle(cellStyleList.get(0));
        }
        //创建表内容信息
        for(int i = 0; i < bodyList.size(); i++){
            Row createRow2 = sheet3.createRow(i + 2);
            for(int j = 0; j < headerList.size(); j++){
                createRow2.createCell(j);
            }
            //设置行高
            //sheet2.getRow(i + 2).setHeightInPoints((float) 15.8);
            //添加样式和数据
            for(int k = 0; k < headerList.size(); k++){
                Cell cell = sheet3.getRow(i + 2).getCell(k);
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(bodyList.size() <= 0 ? "" : bodyList.get(i).get(k));
                cell.setCellStyle(cellStyleList.get(1));
            }
        }

        return workbook;
    }

    //获取工装夹具信息Sheet页
    private XSSFWorkbook getFixtureSheet(XSSFWorkbook workbook, List<Map<String, Object>> list4, List<XSSFCellStyle> cellStyleList){
        Sheet sheet4 = workbook.createSheet("工装夹具信息");
        List<String> headerList = new ArrayList<String>(); //初始化
        List<List<String>> bodyList = new ArrayList<>();//初始化

        if(list4.size() > 0){
            for(int i = 0; i < list4.size(); i++){
                Map<String, Object> map = list4.get(i);
                List<String> body = new ArrayList<>();
                if(map != null){
                    body.add(String.valueOf(i + 1));//序号
                    body.add(map.get("CREATE_DATE")!=null ? map.get("CREATE_DATE").toString() : "");//上机时间
                    body.add(map.get("CREATE_BY")!=null ? map.get("CREATE_BY").toString() : "");//上机操作人
                    body.add(map.get("M_CODE")!=null ? map.get("M_CODE").toString() : "");//设备编号
                    body.add(map.get("EQ_NAME")!=null ? map.get("EQ_NAME").toString() : "");//设备名称
                    body.add(map.get("S_CODE")!=null ? map.get("S_CODE").toString() : "");//工装编号
                    body.add(map.get("CUT_NAME")!=null ? map.get("CUT_NAME").toString() : "");//工装名称
                    body.add(map.get("DOWN_DATE")!=null ? map.get("DOWN_DATE").toString() : "");//下机时间
                    body.add(map.get("DOWN_USER_BY")!=null ? map.get("DOWN_USER_BY").toString() : "");//下机操作人
                    body.add(map.get("NOW_TIME")!=null ? map.get("NOW_TIME").toString() : "");//本次使用次数/米数
                    body.add(map.get("AVAILABLE_TIME")!=null ? map.get("AVAILABLE_TIME").toString() : "");//可用次/米数
                    body.add(map.get("TOTAL_TIME")!=null ? map.get("TOTAL_TIME").toString() : "");//总使用次/米数
                    body.add(map.get("LOTNO")!=null ? map.get("LOTNO").toString() : "");//批次号
                    body.add(map.get("PROC_NO")!=null ? map.get("PROC_NO").toString() : "");//工序编号
                    body.add(map.get("PROC_NAME")!=null ? map.get("PROC_NAME").toString() : "");//工序名称
                    bodyList.add(body);
                }
            }
        }

        //创建表头信息
        headerList.add("序号");//1
        headerList.add("上机时间");//2
        headerList.add("上机操作人");//3
        headerList.add("设备编号");//4
        headerList.add("设备名称");//5
        headerList.add("工装编号");//6
        headerList.add("工装名称");//7
        headerList.add("下机时间");//8
        headerList.add("下机操作人");//9
        headerList.add("本次使用次数/米数");//10
        headerList.add("可用次/米数");//11
        headerList.add("总使用次/米数");//12
        headerList.add("批次号");//13
        headerList.add("工序编号");//14
        headerList.add("工序名称");//15

        //创建行（标题）
        Row createRow = sheet4.createRow(0);
        for(int i = 0; i < headerList.size(); i++){
            createRow.createCell(i);
        }
        //设置行高
        sheet4.getRow(0).setHeightInPoints((float) 25);
        //添加样式和数据
        for(int i = 0; i < 1; i++){
            Cell cell = sheet4.getRow(0).getCell(0);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("工装夹具信息");
            cell.setCellStyle(cellStyleList.get(2));
        }
        //合并单元格
        CellRangeAddress region1 = new CellRangeAddress(0,0,0,headerList.size()-1);
        sheet4.addMergedRegion(region1);
        RegionUtil.setBorderTop(CellStyle.BORDER_THIN, region1, sheet4, workbook);
        RegionUtil.setBorderRight(CellStyle.BORDER_THIN, region1, sheet4, workbook);
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, region1, sheet4, workbook);
        RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, region1, sheet4, workbook);

        //创建行（表头）
        Row createRow1 = sheet4.createRow(1);
        for(int i = 0; i < headerList.size(); i++){
            createRow1.createCell(i);
        }
        //设置列宽
        for(int i = 0; i < headerList.size(); i++){
            if(headerList.get(i).equals("上机时间") || headerList.get(i).equals("下机时间") || headerList.get(i).equals("本次使用次数/米数")){
                sheet4.setColumnWidth(i, 20*256);
            }else if(headerList.get(i).equals("上机操作人") || headerList.get(i).equals("下机操作人") || headerList.get(i).equals("可用次/米数") || headerList.get(i).equals("总使用次/米数")){
                sheet4.setColumnWidth(i, 15*256);
            }else{
                sheet4.setColumnWidth(i, 12*256);
            }
        }
        //添加样式和数据
        for(int i = 0; i < headerList.size(); i++){
            Cell cell = sheet4.getRow(1).getCell(i);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(headerList.get(i));
            cell.setCellStyle(cellStyleList.get(0));
        }

        //创建表内容信息
        for(int i = 0; i < bodyList.size(); i++){
            Row createRow2 = sheet4.createRow(i + 2);
            for(int j = 0; j < headerList.size(); j++){
                createRow2.createCell(j);
            }
            //设置行高
            //sheet.getRow(i + 2).setHeightInPoints((float) 15.8);
            //添加样式和数据
            for(int k = 0; k < headerList.size(); k++){
                Cell cell = sheet4.getRow(i + 2).getCell(k);
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(bodyList.size() <= 0 ? "" : bodyList.get(i).get(k));
                cell.setCellStyle(cellStyleList.get(1));
            }
        }

        return workbook;
    }

    //Excel样式
    public List<XSSFCellStyle> getStyle(XSSFWorkbook workbook) {
        List<XSSFCellStyle> cellStyleList = new ArrayList<XSSFCellStyle>();

        //添加字体
        //0.
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  //字体加粗

        //1.
        XSSFFont font1 = workbook.createFont();
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 11);

        //2.
        XSSFFont font2 = workbook.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 18);
        font2.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  //字体加粗
        font2.setColor(HSSFColor.WHITE.index);//设置excel数据字体颜色

        //添加样式
        //0.实线边框 + 宋体 + 加粗 + 左对齐 + 垂直居中
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);  //上边框
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);  //右边框
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);  //下边框
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);  //左边框
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);  //左对齐
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直居中
        cellStyle.setWrapText(true);  //自动换行
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(184, 204, 228)));//背景颜色
        //cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);//背景颜色
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cellStyleList.add(cellStyle);

        //1.实线边框 + 宋体 + 左对齐 + 垂直居中
        XSSFCellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setFont(font1);
        cellStyle1.setBorderTop(CellStyle.BORDER_THIN);  //上边框
        cellStyle1.setBorderRight(CellStyle.BORDER_THIN);  //右边框
        cellStyle1.setBorderBottom(CellStyle.BORDER_THIN);  //下边框
        cellStyle1.setBorderLeft(CellStyle.BORDER_THIN);  //左边框
        cellStyle1.setAlignment(CellStyle.ALIGN_LEFT);  //左对齐
        cellStyle1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直居中
        cellStyle1.setWrapText(true);  //自动换行
        cellStyleList.add(cellStyle1);

        //2.实线边框 + 宋体 + 水平居中 + 垂直居中
        XSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setFont(font2);
        cellStyle2.setBorderTop(CellStyle.BORDER_THIN);  //上边框
        cellStyle2.setBorderRight(CellStyle.BORDER_THIN);  //右边框
        cellStyle2.setBorderBottom(CellStyle.BORDER_THIN);  //下边框
        cellStyle2.setBorderLeft(CellStyle.BORDER_THIN);  //左边框
        cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);  //水平居中
        cellStyle2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直居中
        cellStyle2.setWrapText(true);  //自动换行
        cellStyle2.setFillForegroundColor(new XSSFColor(new Color(31, 73, 125)));//背景颜色
        //cellStyle2.setFillForegroundColor(HSSFColor.DARK_TEAL.index);//背景颜色
        cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cellStyleList.add(cellStyle2);

        return cellStyleList;
    }
}