# ErpDaoImpl.java

    package com.mycim.server.erp.dao.impl;

    import com.fa.sesa.threadlocal.LocalContext;
    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.collections.MapUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.erp.dao.ErpDao;
    import com.mycim.server.erp.dao.mapper.ErpMaterialRowMapper;
    import com.mycim.server.erp.dao.mapper.ErpShipLotRowMapper;
    import com.mycim.server.erp.dao.mapper.ErpWorkOrderLineRowMapper;
    import com.mycim.server.erp.dao.mapper.ErpWorkOrderRowMapper;
    import com.mycim.server.erp.dto.*;
    import com.mycim.valueobject.bas.TransactionLog;
    import com.mycim.valueobject.erp.ErpMesProductBindBean;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Timestamp;
    import java.time.format.DateTimeFormatter;
    import java.util.*;
    import java.util.stream.Collectors;

    /**
     * @author Luopeng.Wang
     * @version 6.0.0
     * @date 2021/5/21
     **/
    @Repository
    public class ErpDaoImpl implements ErpDao {

        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public List<ErpMaterialDto> getAvaliableErpMaterial() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" MANDT, PLANT, MATNR, MES_MATNR, MEINS, MTART, LVORM, ERSDA, LAEDA, STATUS, ERDAT, ERZET, ZSEMI ");
            sql.append(" FROM ZMES_MATERIAL M ");
            sql.append(" WHERE STATUS IS NULL ");

            return jdbcTemplate.query(sql.toString(), new ErpMaterialRowMapper());
        }

        @Override
        public void changeErpMaterialStatus(ErpMaterialDto erpMaterialDto, String status) {
            StringBuilder sql = new StringBuilder("UPDATE ZMES_MATERIAL M ");
            sql.append(" SET M.STATUS = ? ");
            sql.append(" WHERE MANDT = ? AND PLANT = ? AND MATNR = ? ");

            jdbcTemplate.update(sql.toString(),
                                new Object[]{status, erpMaterialDto.getMandt(), erpMaterialDto.getPlant(),
                                             erpMaterialDto.getMatnr()});
        }

        @Override
        public List<Map<String, String>> getCanReceiveMaterialList() {
            String sql = " SELECT MANDT, MJAHR, MBLNR, ZEILE, PLANT, ORDERID, MOVE_TYPE, " +
                    " MATERIAL, BATCH, ENTRY_QNT, ENTRY_UOM, STATUS, ERDAT, ERZET " +
                    " , to_date(PSTNG_DATE , ?) PSTNG_DATE  FROM ZMES_GDISSUE" +
                    " WHERE ( STATUS IS NULL OR  STATUS <> ?) AND  MOVE_TYPE = ? ";
            return jdbcTemplate.query(sql,
                                      new Object[]{ErpConstants.DATE_FORMAT4DAY_NOSPLICING, ErpConstants.STATUS_SUCCESS,
                                                   ErpConstants.ORDER_ISSUE},
                                      (RowMapper<Map<String, String>>) (rs, rowNum) -> {
                                          Map<String, String> map = new HashMap<>();
                                          buildReceiveMaterialResult(rs, map);
                                          return map;
                                      });
        }

        @Override
        public Map<String, String> getCanReceiveMaterial(String materialCertificateYear, String materialCertificateId,
                                                         String materialCertificateProject) {
            String sql = " SELECT ZG.MANDT, ZG.MJAHR, ZG.MBLNR, ZG.ZEILE, ZG.PLANT, ZG.ORDERID, ZG.MOVE_TYPE," +
                    " ZG.MATERIAL, ZG.BATCH, ZG.ENTRY_QNT, ZG.ENTRY_UOM, ZG.STATUS, ZG.ERDAT, ZG.ERZET " +
                    " , to_date(ZG.PSTNG_DATE , ?) PSTNG_DATE " + " FROM ZMES_GDISSUE ZG" +
                    " WHERE ZG.MJAHR = ? AND ZG.MBLNR = ? AND  ZG.ZEILE = ? ";
            return jdbcTemplate.queryForObjectWithNull(sql,
                                                       new Object[]{ErpConstants.DATE_FORMAT4DAY_NOSPLICING,
                                                                    materialCertificateYear, materialCertificateId,
                                                                    materialCertificateProject},
                                                       (RowMapper<Map<String, String>>) (rs, rowNum) -> {
                                                           Map<String, String> map = new HashMap<>();
                                                           buildReceiveMaterialResult(rs, map);
                                                           return map;
                                                       });
        }

        @Override
        public List<ErpWorkOrderDto> getAvaliableErpWorkOrder() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" MANDT  , WERKS  , AUFNR  , PLNBEZ , AUART  , MTART  , GAMNG  , GMEIN  , GSTRP  , GLTRP  , ");
            sql.append(" ETDAT  , ZDIEIT , LOEKZ  , KDAUF  , KDPOS  , STATUS , ERDAT  , ERZET   ");
            sql.append(" FROM ZMES_POREL_H");
            sql.append(" WHERE STATUS IS NULL  ");

            return jdbcTemplate.query(sql.toString(), new ErpWorkOrderRowMapper());
        }

        @Override
        public void changeErpWorkOrderStatus(ErpWorkOrderDto erpWorkOrderDto, String status) {
            StringBuilder sql = new StringBuilder("UPDATE ZMES_POREL_H PH ");
            sql.append(" SET PH.STATUS = ? ");
            sql.append(" WHERE MANDT = ? AND WERKS = ? AND AUFNR = ? ");

            jdbcTemplate.update(sql.toString(),
                                new Object[]{status, erpWorkOrderDto.getMandt(), erpWorkOrderDto.getWerks(),
                                             erpWorkOrderDto.getAufnr()});
        }

        @Override
        public List<ErpWorkOrderLineDto> getAvaliableErpWorkOrderLine() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" MANDT , AUFNR , MATNR , MTART , IDNRK , BDMNG , STATUS, ERDAT , ERZET  ");
            sql.append(" FROM ZMES_POREL_I");
            sql.append(" WHERE STATUS IS NULL ");

            return jdbcTemplate.query(sql.toString(), new ErpWorkOrderLineRowMapper());
        }

        @Override
        public void changeErpWorkOrderLineStatus(List<ErpWorkOrderLineDto> erpWorkOrderLineDtos, String status) {
            StringBuilder sql = new StringBuilder("UPDATE ZMES_POREL_I PI ");
            sql.append(" SET PI.STATUS = ? ");
            sql.append(" WHERE MANDT = ? AND AUFNR = ? AND MATNR = ? ");

            List<Object[]> args = erpWorkOrderLineDtos.stream().map(erpWorkOrderLineDto -> {
                return new Object[]{status, erpWorkOrderLineDto.getMandt(), erpWorkOrderLineDto.getAufnr(),
                                    erpWorkOrderLineDto.getMatnr()};
            }).collect(Collectors.toList());

            jdbcTemplate.batchUpdate(sql.toString(), args);
        }

        @Override
        public int checkMaterialInBom(String pOrderId, String materialId) {
            String sql = "SELECT COUNT(*) FROM ZMES_POREL_I WHERE AUFNR=? and MATNR=? ";
            return jdbcTemplate.queryForObject(sql, new Object[]{pOrderId, materialId}, int.class);
        }

        @Override
        public void updateMaterialReceiveStatus(String materialCertificateYear, String materialCertificateId,
                                                String materialCertificateProject) {
            String[] dateArr = getErpNeedTypeDate();
            String sql = "UPDATE ZMES_GDISSUE SET STATUS=?,ERDAT=?,ERZET=? WHERE MJAHR=? AND MBLNR=? AND ZEILE=? ";
            jdbcTemplate.update(sql, ErpConstants.STATUS_SUCCESS, dateArr[0], dateArr[1], materialCertificateYear,
                                materialCertificateId, materialCertificateProject);
        }

        @Override
        public void recordMaterialReturnLog(Map<String, String> pm) {
            String sql = " INSERT INTO ZMES_GDRTN(MANDT,PLANT,ORDERID,MOVE_TYPE,MATERIAL,BATCH,ENTRY_QNT,ENTRY_UOM, " +
                    " RTN_FLG,PSTNG_DATE,ERDAT,ERZET) VALUES(?,?,?,?,?,?,?,?,?,?,?,?) ";
            String[] arr = getErpNeedTypeDate();
            Object[] args = new Object[]{pm.get("mandt"), pm.get("plant"), pm.get("orderId"), pm.get("moveType"), pm.get(
                    "materialId"), pm.get("lotNumber"), pm.get("qty"), pm.get("uom"), pm.get(
                    "flag"), arr[0], arr[0], arr[1]};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void insertOrUpdateErpWaferStart(ErpWaferStartDto erpWaferStartDto) {
            StringBuilder sql = new StringBuilder(" MERGE INTO ZWFSTART T USING  (SELECT * FROM DUAL) S ");
            sql.append(" ON (T.MANDT = ? AND T.VBELN = ? AND T.POSNR = ?) ");
            sql.append(" WHEN MATCHED THEN ");
            sql.append(" UPDATE SET T.MATNR= ? , T.AUFNR = ? , T.ERDAT = ? , T.ERZET = ? ");
            sql.append(" WHEN NOT MATCHED THEN ");
            sql.append(" INSERT (MANDT, VBELN, POSNR, MATNR, AUFNR, ERDAT, ERZET) VALUES(?,?,?,?,?,?,?) ");

            Object[] args = {erpWaferStartDto.getMandt(), erpWaferStartDto.getVbeln(), erpWaferStartDto.getPosnr(),
                             erpWaferStartDto.getMatnr(), erpWaferStartDto.getAufnr(), erpWaferStartDto.getErdat(),
                             erpWaferStartDto.getErzet(), erpWaferStartDto.getMandt(), erpWaferStartDto.getVbeln(),
                             erpWaferStartDto.getPosnr(), erpWaferStartDto.getMatnr(), erpWaferStartDto.getAufnr(),
                             erpWaferStartDto.getErdat(), erpWaferStartDto.getErzet()};
            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public void markMaterialLotReturn(Long itemRrn, long warehouseRrn, String lotNumber, String mark) {
            String sql = "UPDATE LOT_INVENTORY_EXT SET ATTRIBUTE_DATA5=? WHERE ITEM_RRN=? AND WAREHOUSE_RRN=? AND " +
                    "LOT_NUMBER=?";
            jdbcTemplate.update(sql, mark, itemRrn, warehouseRrn, lotNumber);
        }

        @Override
        public List<Map<String, String>> getConfirmedReturnRequest() {
            String sql = "SELECT MANDT,PLANT,ORDERID,MOVE_TYPE,MATERIAL," + "BATCH,ENTRY_QNT,ENTRY_UOM,RTN_FLG " +
                    "FROM ZMES_GDRTN WHERE STATUS=? ";
            return jdbcTemplate.query(sql, new Object[]{"1"}, (RowMapper<Map<String, String>>) (rs, rowNum) -> {
                Map<String, String> m = new HashMap<>();
                m.put("mandt", rs.getString("MANDT"));
                m.put("plant", rs.getString("PLANT"));
                m.put("orderId", rs.getString("ORDERID"));
                m.put("moveType", rs.getString("MOVE_TYPE"));
                m.put("sapMaterialId", rs.getString("MATERIAL"));
                m.put("materialId", rs.getString("MATERIAL"));
                m.put("lotNumber", rs.getString("BATCH"));
                m.put("entryQnt", rs.getString("ENTRY_QNT"));
                m.put("UOM", rs.getString("ENTRY_UOM"));
                m.put("rtnFlg", rs.getString("RTN_FLG"));
                return m;
            });
        }

        @Override
        public void updateReturnRequestStatus(Map<String, String> pm) {
            String sql = "UPDATE ZMES_GDRTN SET STATUS=? WHERE MANDT=? AND PLANT=? AND ORDERID=? AND MOVE_TYPE=? AND " +
                    "MATERIAL=? AND BATCH=?";
            jdbcTemplate.update(sql, ErpConstants.STATUS_SUCCESS, pm.get("mandt"), pm.get("plant"), pm.get("orderId"),
                                pm.get("moveType"), pm.get("sapMaterialId"), pm.get("lotNumber"));
        }

        @Override
        public void updateReturnRequestStatusForBom(Map<String, String> pm) {
            String sql = "UPDATE ZMES_BOMISSUE SET STATUS=? WHERE MANDT=? AND MBLNR=? AND ZEILE=? AND " +
                    "MATERIAL=? AND BATCH=?";
            List args = new ArrayList();
            args.add(ErpConstants.STATUS_SUCCESS);
            args.add(pm.get("sapClintId"));
            args.add(pm.get("materialCertificateId"));
            args.add(pm.get("zeile"));
            args.add(pm.get("sapMaterialId"));
            args.add(pm.get("lotNumber"));
            jdbcTemplate.update(sql, args.toArray());
        }

        @Override
        public List<Map<String, String>> getReceiveOrReturnBomList(String moveType) {
            StringBuilder sql = new StringBuilder();
            sql.append(" SELECT MANDT, MBLNR, MATERIAL, PLANT, MATERIAL_TYPE, MOVE_TYPE, ZEILE, ");
            sql.append(" RELATE_MATERIAL, BATCH, ENTRY_QNT, ENTRY_UOM, STATUS, ERDAT, ERZET ");
            sql.append(" , to_date(PSTNG_DATE , ?) PSTNG_DATE  FROM ZMES_BOMISSUE ");
            sql.append(" WHERE ( STATUS IS NULL OR  STATUS = ?) AND  MOVE_TYPE = ? ");
            List args = new ArrayList();
            args.add(ErpConstants.DATE_FORMAT4DAY_NOSPLICING);
            args.add(ErpConstants.STATUS_ERP_SUCCESS);
            args.add(moveType);
            return jdbcTemplate.query(sql.toString(), args.toArray(), (RowMapper<Map<String, String>>) (rs, rowNum) -> {
                                          Map<String, String> map = new HashMap<>();
                                          buildReceiveBomResult(rs, map);
                                          return map;
                                      });
        }

        @Override
        public List<String> queryMaterialIdByWorkOrderId(String workOrderId) {
            String sql = "SELECT DISTINCT WL.MATERIAL_ID FROM WORKORDER_LINE WL, MATERIAL M "
                    + " WHERE WL.WORKORDER_RRN = (SELECT WORKORDER_RRN FROM WORKORDER WHERE WORKORDER_ID = ?) "
                    + " AND WL.MATERIAL_ID = M.MATERIAL_ID AND M.SUBPRODUCT_FLAG = ?  ";
            return jdbcTemplate.query(sql, new Object[]{workOrderId, ErpConstants.SEMI_FINISH_FLAG}, String.class);
        }

        @Override
        public void insertOrUpdateErpShipLot(ErpShipLotDto erpShipLotDto) {
            StringBuilder sql = new StringBuilder(" MERGE INTO ZMES_GDRECV T USING  (SELECT * FROM DUAL) S ");
            sql.append(" ON (T.MANDT = ? AND T.PLANT = ? AND T.ORDERID = ? ");
            sql.append(" AND T.MOVE_TYPE = ? AND T.MATERIAL = ? AND T.BATCH = ? )");
            sql.append(" WHEN MATCHED THEN ");
            sql.append(" UPDATE SET T.ENTRY_QNT= ? , T.ENTRY_UOM = ? , T.YEILD = ? , T.LPC_UOM = ? , T.HSDAT = ? ");
            sql.append(" , T.LWEDT= ? , T.VFDAT = ? , T.ERDAT = ? , T.ERZET = ? ");
            sql.append(" WHEN NOT MATCHED THEN ");
            sql.append(
                    " INSERT (MANDT, PLANT, ORDERID, MOVE_TYPE, MATERIAL, BATCH, ENTRY_QNT, ENTRY_UOM, YEILD, LPC_UOM, " +
                            "HSDAT, LWEDT, VFDAT, STATUS, ERDAT, ERZET) ");
            sql.append(" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

            Object[] args = {erpShipLotDto.getMandt(), erpShipLotDto.getPlant(), erpShipLotDto.getOrderId(),
                             erpShipLotDto.getMoveType(), erpShipLotDto.getMaterial(), erpShipLotDto.getBatch(),
                             erpShipLotDto.getEntryQnt(), erpShipLotDto.getEntryUom(), erpShipLotDto.getYeild(),
                             erpShipLotDto.getLpcUom(), erpShipLotDto.getHsdat(), erpShipLotDto.getLwedt(),
                             erpShipLotDto.getVfdat(), erpShipLotDto.getErdat(), erpShipLotDto.getErzet(),
                             erpShipLotDto.getMandt(), erpShipLotDto.getPlant(), erpShipLotDto.getOrderId(),
                             erpShipLotDto.getMoveType(), erpShipLotDto.getMaterial(), erpShipLotDto.getBatch(),
                             erpShipLotDto.getEntryQnt(), erpShipLotDto.getEntryUom(), erpShipLotDto.getYeild(),
                             erpShipLotDto.getLpcUom(), erpShipLotDto.getHsdat(), erpShipLotDto.getLwedt(),
                             erpShipLotDto.getVfdat(), erpShipLotDto.getStatus(), erpShipLotDto.getErdat(),
                             erpShipLotDto.getErzet()};
            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public void changeErpShipLotStatus(ErpShipLotDto erpShipLotDto, String status) {
            StringBuilder sql = new StringBuilder("UPDATE ZMES_GDRECV T ");
            sql.append(" SET T.STATUS = ? ");
            sql.append(" WHERE T.MANDT = ? AND T.PLANT = ? AND T.ORDERID = ? AND T.MOVE_TYPE = ? AND T.MATERIAL = ? ");
            sql.append(" AND T.BATCH = ?  ");

            jdbcTemplate.update(sql.toString(),
                                new Object[]{status, erpShipLotDto.getMandt(), erpShipLotDto.getPlant(),
                                             erpShipLotDto.getOrderId(), erpShipLotDto.getMoveType(),
                                             erpShipLotDto.getMaterial(), erpShipLotDto.getBatch()});
        }

        @Override
        public List<ErpShipLotDto> getAvaliableErpShipLot() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" MANDT, PLANT, ORDERID, MOVE_TYPE, MATERIAL, BATCH, ENTRY_QNT, ENTRY_UOM, ");
            sql.append(" YEILD, LPC_UOM, HSDAT, LWEDT, VFDAT, STATUS, ERDAT, ERZET ");
            sql.append(" FROM ZMES_GDRECV M ");
            sql.append(" WHERE STATUS = ? ");

            return jdbcTemplate
                    .query(sql.toString(), new Object[]{ErpConstants.STATUS_ERP_SUCCESS}, new ErpShipLotRowMapper());
        }

        @Override
        public void insertOrUpdateErpCloseWorkorder(ErpCloseWorkOrderDto erpCloseWorkorderDto) {
            StringBuilder sql = new StringBuilder(" MERGE INTO ZMES_POTECO T USING  (SELECT * FROM DUAL) S ");
            sql.append(" ON (T.MANDT = ? AND T.WERKS = ? AND T.AUFNR = ?) ");
            sql.append(" WHEN MATCHED THEN ");
            sql.append(" UPDATE SET T.BUDAT= ? , T.ERDAT = ? , T.ERZET = ? ");
            sql.append(" WHEN NOT MATCHED THEN ");
            sql.append(" INSERT (MANDT, WERKS, AUFNR, BUDAT, STATUS, ERDAT, ERZET ) ");
            sql.append(" VALUES(?,?,?,?,?,?,?) ");

            Object[] args = {erpCloseWorkorderDto.getMandt(), erpCloseWorkorderDto.getWerks(),
                             erpCloseWorkorderDto.getAufnr(), erpCloseWorkorderDto.getBudat(),
                             erpCloseWorkorderDto.getErdat(), erpCloseWorkorderDto.getErzet(),
                             erpCloseWorkorderDto.getMandt(), erpCloseWorkorderDto.getWerks(),
                             erpCloseWorkorderDto.getAufnr(), erpCloseWorkorderDto.getBudat(),
                             erpCloseWorkorderDto.getStatus(), erpCloseWorkorderDto.getErdat(),
                             erpCloseWorkorderDto.getErzet()};
            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public void insertOrUpdateErpWafer(List<ErpWaferRptDto> erpWaferRptDtos) {
            List<Object[]> args = erpWaferRptDtos.stream().map(erpWaferRptDto -> new Object[]{
                    erpWaferRptDto.getMandt(), erpWaferRptDto.getMatnr(), erpWaferRptDto.getCharg(), erpWaferRptDto.getZwfid(),
                    erpWaferRptDto.getErdat(), erpWaferRptDto.getErzet(), erpWaferRptDto.getCtmltid(), erpWaferRptDto.getCtmwfid(),
                    erpWaferRptDto.getMandt(), erpWaferRptDto.getMatnr(), erpWaferRptDto.getCharg(), erpWaferRptDto.getZwfid(),
                    erpWaferRptDto.getErdat(), erpWaferRptDto.getErzet(), erpWaferRptDto.getCtmltid(), erpWaferRptDto.getCtmwfid()})
                                                 .collect(Collectors.toList());
            String sql = " MERGE INTO ZLTWAFER T USING  (SELECT * FROM DUAL) S "
                    + " ON (T.MANDT = ? AND T.MATNR = ? AND T.CHARG = ?  AND T.ZWFID = ?) "
                    + " WHEN MATCHED THEN UPDATE SET T.ERDAT=?, T.ERZET=?, T.CTMLTID=?, T.CTMWFID=? "
                    + " WHEN NOT MATCHED THEN INSERT (MANDT, MATNR, CHARG, ZWFID, ERDAT, ERZET, CTMLTID, CTMWFID) "
                    + " VALUES(?,?,?,?,?,?,?,?) ";
            jdbcTemplate.batchUpdate(sql, args);
        }

        @Override
        public Page queryBindMesProduct(Page page, ErpMesProductBindBean empbb) {
            String sql = getMesProductBindSql();
            List<Object> args = new ArrayList<>();
            String whereSql = "";
            if (StringUtils.isNotBlank(empbb.getErpOrderId())) {
                whereSql += "AND ERP_ORDER_ID = ? ";
                args.add(empbb.getErpOrderId());
            }
            if (StringUtils.isNotBlank(empbb.getMesProductId())) {
                whereSql += "AND MES_PRODUCT_ID = ? ";
                args.add(empbb.getMesProductId());
            }
            if (!args.isEmpty()) {
                sql = sql + " WHERE " + whereSql.substring(3);
            }
            page = jdbcTemplate.queryForPage(page, sql, args.toArray(), (RowMapper<ErpMesProductBindBean>) (rs, rowNum) -> {
                ErpMesProductBindBean empbb1 = new ErpMesProductBindBean();
                empbb1.setMesProductId(rs.getString("MES_PRODUCT_ID"));
                empbb1.setMesProductRrn(rs.getLong("MES_PRODUCT_RRN"));
                empbb1.setErpProductId(rs.getString("ERP_PRODUCT_ID"));
                empbb1.setErpOrderId(rs.getString("ERP_ORDER_ID"));
                empbb1.setErpMandt(rs.getString("ERP_MANDT"));
                empbb1.setErpWerks(rs.getString("ERP_WERKS"));
                empbb1.setCreatedUserId(rs.getString("CREATE_USER"));
                empbb1.setLastUpdateUserId(rs.getString("UPDATE_USER"));
                empbb1.setCreatedTimestamp(rs.getTimestamp("CREATE_TIME"));
                empbb1.setLastUpdateTimestamp(rs.getTimestamp("UPDATE_TIME"));
                return empbb1;
            });
            return page;
        }

        @Override
        public ErpMesProductBindBean getBindMesProduct(ErpMesProductBindBean empbb) {
            String sql = getMesProductBindSql();
            List<Object> args = new ArrayList<>();
            String whereSql = " WHERE ERP_PRODUCT_ID = ? AND ERP_ORDER_ID = ? AND MES_PRODUCT_ID = ?";
            args.add(empbb.getErpProductId());
            args.add(empbb.getErpOrderId());
            args.add(empbb.getMesProductId());

            sql = sql + whereSql;

            List<ErpMesProductBindBean> list = jdbcTemplate
                    .query(sql, args.toArray(), (RowMapper<ErpMesProductBindBean>) (rs, rowNum) -> {
                        ErpMesProductBindBean empbb1 = new ErpMesProductBindBean();
                        empbb1.setMesProductId(rs.getString("MES_PRODUCT_ID"));
                        empbb1.setMesProductRrn(rs.getLong("MES_PRODUCT_RRN"));
                        empbb1.setErpProductId(rs.getString("ERP_PRODUCT_ID"));
                        empbb1.setErpOrderId(rs.getString("ERP_ORDER_ID"));
                        empbb1.setErpMandt(rs.getString("ERP_MANDT"));
                        empbb1.setErpWerks(rs.getString("ERP_WERKS"));
                        empbb1.setCreatedUserId(rs.getString("CREATE_USER"));
                        empbb1.setLastUpdateUserId(rs.getString("UPDATE_USER"));
                        empbb1.setCreatedTimestamp(rs.getTimestamp("CREATE_TIME"));
                        empbb1.setLastUpdateTimestamp(rs.getTimestamp("UPDATE_TIME"));
                        return empbb1;
                    });

            return list == null || list.isEmpty() ? null : list.get(0);
        }

        private String getMesProductBindSql() {
            return " SELECT MES_PRODUCT_ID, MES_PRODUCT_RRN, ERP_PRODUCT_ID, ERP_ORDER_ID, ERP_MANDT, ERP_WERKS, " +
                    " CREATE_USER, UPDATE_USER, CREATE_TIME, UPDATE_TIME " + " FROM ERP_PRODUCT_MAPPING ";
        }

        @Override
        public void insertBindMesProduct(ErpMesProductBindBean empbb) {
            String sql =
                    " INSERT INTO ERP_PRODUCT_MAPPING(MES_PRODUCT_ID, MES_PRODUCT_RRN, ERP_PRODUCT_ID, ERP_ORDER_ID, " +
                            "ERP_MANDT, ERP_WERKS, " +
                            " CREATE_USER,UPDATE_USER,CREATE_TIME,UPDATE_TIME) VALUES(?,?,?,?,?,?,?,?,?,?) ";

            Object[] args = {empbb.getMesProductId(), empbb.getMesProductRrn(), empbb.getErpProductId(),
                             empbb.getErpOrderId(), empbb.getErpMandt(), empbb.getErpWerks(), empbb.getCreatedUserId(),
                             empbb.getLastUpdateUserId(), empbb.getCreatedTimestamp(), empbb.getLastUpdateTimestamp()};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void updateBindMesProduct(ErpMesProductBindBean empbb) {
            String sql =
                    " UPDATE ERP_PRODUCT_MAPPING SET MES_PRODUCT_RRN = ? ,MES_PRODUCT_ID = ? , UPDATE_USER = ?, " +
                            "UPDATE_TIME = ? " +
                            " WHERE ERP_PRODUCT_ID = ? and  ERP_ORDER_ID = ? and ERP_MANDT = ? and ERP_WERKS = ? ";
            jdbcTemplate.update(sql, empbb.getMesProductRrn(), empbb.getMesProductId(), empbb.getLastUpdateUserId(),
                                empbb.getLastUpdateTimestamp(), empbb.getErpProductId(), empbb.getErpOrderId(),
                                empbb.getErpMandt(), empbb.getErpWerks());
        }

        @Override
        public void deleteBindMesProduct(ErpMesProductBindBean empbb) {
            String sql = " DELETE FROM ERP_PRODUCT_MAPPING WHERE ERP_PRODUCT_ID = ? and  ERP_ORDER_ID = ? and ERP_MANDT =" +
                    " ? and ERP_WERKS = ? ";
            jdbcTemplate
                    .update(sql, empbb.getErpProductId(), empbb.getErpOrderId(), empbb.getErpMandt(), empbb.getErpWerks());
        }

        @Override
        public void insertBindMesProductHistory(ErpMesProductBindBean empbb, TransactionLog tl) {
            String sql = " INSERT INTO ERP_PRODUCT_MAPPING_H (TRANS_RRN, TRANS_ID, MES_PRODUCT_ID, MES_PRODUCT_RRN, " +
                    " ERP_PRODUCT_ID, ERP_ORDER_ID, ERP_MANDT, ERP_WERKS, TRANS_USER_RRN, TRANS_TIME)" +
                    " SELECT ?, ?, MES_PRODUCT_ID, MES_PRODUCT_RRN, ERP_PRODUCT_ID, ERP_ORDER_ID, ERP_MANDT, ERP_WERKS, " +
                    "?, ?" +
                    " FROM ERP_PRODUCT_MAPPING " +
                    " WHERE MES_PRODUCT_RRN = ? AND ERP_PRODUCT_ID = ? AND  ERP_ORDER_ID = ? AND ERP_MANDT = ? AND " +
                    "ERP_WERKS = ?";
            jdbcTemplate.update(sql, tl.getTransRrn(), tl.getTransId(), empbb.getLastUpdateUserRrn(),
                                tl.getTransStartTimestamp(), empbb.getMesProductRrn(), empbb.getErpProductId(),
                                empbb.getErpOrderId(), empbb.getErpMandt(), empbb.getErpWerks());
        }

        @Override
        public List<ErpMesProductBindBean> queryNotBindMesProduct() {
            String sql = "SELECT ZPH.MANDT,ZPH.WERKS,ZPH.AUFNR,ZPH.PLNBEZ " + " FROM ZMES_POREL_H ZPH " +
                    " WHERE ZPH.STATUS= ? " +
                    " AND NOT EXISTS (SELECT 1 FROM ERP_PRODUCT_MAPPING WHERE ERP_ORDER_ID=ZPH.AUFNR )" +
                    " ORDER BY  ZPH.AUFNR";
            return jdbcTemplate.query(sql, new Object[]{ErpConstants.STATUS_SUCCESS},
                                      (RowMapper<ErpMesProductBindBean>) (rs, rowNum) -> {
                                          ErpMesProductBindBean empdd = new ErpMesProductBindBean();
                                          empdd.setErpProductId(rs.getString("PLNBEZ"));
                                          empdd.setErpOrderId(rs.getString("AUFNR"));
                                          empdd.setErpMandt(rs.getString("MANDT"));
                                          empdd.setErpWerks(rs.getString("WERKS"));
                                          return empdd;
                                      });
        }

        @Override
        public ErpMaterialDto getMesProductId(String materialId, String mandt, String plant) {
            String sql = "SELECT MANDT, PLANT, MATNR, MES_MATNR, MEINS, MTART, LVORM, ERSDA, LAEDA, STATUS, ERDAT, ERZET, ZSEMI " +
                    " FROM ZMES_MATERIAL  WHERE MATNR = ? AND MANDT=? AND PLANT=? ";
            List<ErpMaterialDto> list = jdbcTemplate
                    .query(sql, new Object[]{materialId, mandt, plant}, new ErpMaterialRowMapper());
            return list == null || list.isEmpty() ? null : list.get(0);
        }

        @Override
        public Map<String, String> getCanReceiveBom(String mandt, String mblnr, String material, String batch, String zeile) {
            String sql = " SELECT MANDT, MBLNR, MATERIAL, PLANT, MATERIAL_TYPE, MOVE_TYPE," +
                    " RELATE_MATERIAL, BATCH, ENTRY_QNT, ENTRY_UOM, STATUS, ERDAT, ERZET,ZEILE " +
                    " , to_date(PSTNG_DATE , ?) PSTNG_DATE  FROM ZMES_BOMISSUE" +
                    " WHERE  MANDT = ? AND MBLNR = ? AND MATERIAL = ? AND BATCH = ? AND ZEILE = ?";
            List args = new ArrayList();
            args.add(ErpConstants.DATE_FORMAT4DAY_NOSPLICING);
            args.add(mandt);
            args.add(mblnr);
            args.add(material);
            args.add(batch);
            args.add(zeile);
            return jdbcTemplate
                    .queryForObjectWithNull(sql, args.toArray(), (RowMapper<Map<String, String>>) (rs, rowNum) -> {
                        Map<String, String> map = new HashMap<>();
                        buildReceiveBomResult(rs, map);
                        return map;
                    });
        }

        @Override
        public void updateBomReceiveStatus(String mandt, String mblnr, String material, String batch, String zeile) {
            String[] dateArr = getErpNeedTypeDate();
            String sql = "UPDATE ZMES_BOMISSUE SET STATUS=?,ERDAT=?,ERZET=? WHERE MANDT=?  " +
                    "AND MBLNR = ? AND MATERIAL = ? AND BATCH = ? AND ZEILE = ?";
            List args = new ArrayList();
            args.add(ErpConstants.STATUS_SUCCESS);
            args.add(dateArr[0]);
            args.add(dateArr[1]);
            args.add(mandt);
            args.add(mblnr);
            args.add(material);
            args.add(batch);
            args.add(zeile);
            jdbcTemplate.update(sql, args.toArray());
        }

        private void buildReceiveMaterialResult(ResultSet rs, Map<String, String> map) throws SQLException {
            map.put("sapClintId", rs.getString("MANDT"));
            map.put("materialCertificateYear", rs.getString("MJAHR"));
            map.put("materialCertificateId", rs.getString("MBLNR"));
            map.put("materialCertificateProject", rs.getString("ZEILE"));
            map.put("factory", rs.getString("PLANT"));
            map.put("productOrderId", rs.getString("ORDERID"));
            map.put("moveType", rs.getString("MOVE_TYPE"));
            map.put("materialId", rs.getString("MATERIAL"));
            map.put("sapMaterialId", rs.getString("MATERIAL"));
            map.put("lotNumber", rs.getString("BATCH"));
            map.put("qty", rs.getString("ENTRY_QNT"));
            map.put("uom", rs.getString("ENTRY_UOM"));
            Timestamp ts = rs.getTimestamp("PSTNG_DATE");
            map.put("pickingDate", ts != null ? ts.toLocalDateTime().toLocalDate().
                    format(DateTimeFormatter.ofPattern(DateUtils.DATE_FORMAT4DAY)) : rs.getString("PSTNG_DATE"));
            map.put("status", rs.getString("STATUS"));
            map.put("checkUser", LocalContext.getUserId());
            map.put("comments", map.get("materialCertificateYear") + "," + map.get("materialCertificateId") + "," +
                    map.get("materialCertificateProject"));
        }

        private void buildReceiveBomResult(ResultSet rs, Map<String, String> map) throws SQLException {
            map.put("sapClintId", rs.getString("MANDT"));
            map.put("materialCertificateId", rs.getString("MBLNR"));
            map.put("factory", rs.getString("PLANT"));
            map.put("moveType", rs.getString("MOVE_TYPE"));
            map.put("materialId", rs.getString("MATERIAL"));
            map.put("sapMaterialId", rs.getString("MATERIAL"));
            map.put("materialType", rs.getString("MATERIAL_TYPE"));
            map.put("relateMaterial", rs.getString("RELATE_MATERIAL"));
            map.put("lotNumber", rs.getString("BATCH"));
            map.put("qty", rs.getString("ENTRY_QNT"));
            map.put("uom", rs.getString("ENTRY_UOM"));
            Timestamp ts = rs.getTimestamp("PSTNG_DATE");
            map.put("pickingDate", ts != null ? ts.toLocalDateTime().toLocalDate().
                    format(DateTimeFormatter.ofPattern(DateUtils.DATE_FORMAT4DAY)) : rs.getString("PSTNG_DATE"));
            map.put("status", rs.getString("STATUS"));
            map.put("checkUser", LocalContext.getUserId());
            map.put("zeile", rs.getString("ZEILE"));
            map.put("comments",MapUtils.getString(map, "sapClintId")  + "," +
                    MapUtils.getString(map, "materialCertificateId") + "," + MapUtils.getString(map, "sapMaterialId")
                    + "," + MapUtils.getString(map, "lotNumber") + "," + MapUtils.getString(map, "zeile"));

        }

        private String[] getErpNeedTypeDate() {
            String[] timeArr = new String[2];
            String date = DateUtils.getNowTime(DateUtils.DATE_FORMAT4NOSPLICING);
            timeArr[0] = StringUtils.substring(date, 0, 8);
            timeArr[1] = StringUtils.substring(date, 8, 14);
            return timeArr;
        }

    }