# WorkOrderDaoImpl.java

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

    import com.mycim.framework.jdbc.BatchPreparedStatementSetter;
    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.CollectionUtils;
    import com.mycim.framework.utils.lang.collections.MapUtils;
    import com.mycim.server.workorder.dao.WorkOrderDao;
    import com.mycim.valueobject.erp.workOrder.SdsWorkOrderInfo;
    import com.mycim.valueobject.erp.workOrder.SdsWorkOrderMaterialInfo;
    import com.mycim.valueobject.erp.workOrder.WorkOrderImpSourceWaferDb;
    import com.mycim.valueobject.prp.WorkOrder;
    import com.mycim.valueobject.prp.WorkOrderLine;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.stereotype.Repository;

    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.stream.Collectors;

    @Repository
    public class WorkOrderDaoImpl implements WorkOrderDao {

        private final JdbcTemplate jdbcTemplate;

        public WorkOrderDaoImpl(JdbcTemplate jdbcTemplate) {
            this.jdbcTemplate = jdbcTemplate;
        }

        @Override
        public WorkOrder getWorkOrderById(String orderId) {
            String sql = "SELECT WORKORDER_RRN,WORKORDER_ID,PRODUCT_ID,PLANNED_YIELD,STATUS,COMMENTS,CREATE_USER," +
                    "CUSTOMER,PLAN_TIME," + "WORKORDER_PRIORITY,ORDER_DIE_QTY,START_DAY,REQUIRE_DAY," +
                    "DAY_DAY,DUE_DATE,CUSTOMER_ORDER,CUSTOMER_ORDER_NO,SHIPPED_QTY,WORKORDER_TYPE,ERP_CLIENT,ERP_FACTORY," +
                    "START_QTY" +
                    " FROM WORKORDER WHERE WORKORDER_ID=? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{orderId}, new RowMapper<WorkOrder>() {
                @Override
                public WorkOrder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    WorkOrder order = new WorkOrder();
                    order.mapRow(rs, rowNum);
                    return order;
                }
            });
        }

        @Override
        public void insertWorkOrder(WorkOrder workOrder) {

            String sql = "INSERT INTO WORKORDER (WORKORDER_RRN,WORKORDER_ID,PRODUCT_ID,PLANNED_YIELD,STATUS," +
                    "COMMENTS,CREATE_USER," + " CUSTOMER,PLAN_TIME,WORKORDER_PRIORITY,ORDER_DIE_QTY," + "START_DAY," +
                    "REQUIRE_DAY,DAY_DAY,DUE_DATE,CUSTOMER_ORDER,CUSTOMER_ORDER_NO,WORKORDER_TYPE,ERP_CLIENT," +
                    "ERP_FACTORY)" +
                    " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            Object[] args = {workOrder.getWorkorderRrn(), workOrder.getWorkorderId(), workOrder.getProductId(),
                    workOrder.getPlannedYield(), workOrder.getStatus(), workOrder.getComments(),
                    workOrder.getCreateUser(), workOrder.getCustomer(), workOrder.getPlanTime(),
                    workOrder.getWorkorderPriority(), workOrder.getOrderDieQty(), workOrder.getStartDay(), workOrder.getRequireDay(), workOrder.getDayDay(), workOrder.getDueDate(), workOrder.getCustomerOrder(), workOrder.getCustomerOrderNo(), workOrder.getWorkorderType(), workOrder.getErpClient(), workOrder.getErpFactory()};
            jdbcTemplate.update(sql, args);

        }

        @Override
        public void updateWorkOrder(WorkOrder workOrder) {
            String sql = "UPDATE WORKORDER SET PLANNED_YIELD=?,STATUS=?,COMMENTS=?,CUSTOMER=?,PLAN_TIME=?," + "PRODUCT_ID" +
                    "=?,WORKORDER_PRIORITY=?,ORDER_DIE_QTY=?," + "START_DAY=? ,REQUIRE_DAY=?,DAY_DAY=?,DUE_DATE=? WHERE " +
                    "WORKORDER_RRN=?";
            Object[] args = {workOrder.getPlannedYield(), workOrder.getStatus(), workOrder.getComments(),
                    workOrder.getCustomer(), workOrder.getPlanTime(), workOrder.getProductId(),
                    workOrder.getWorkorderPriority(), workOrder.getOrderDieQty(), workOrder.getStartDay(),
                    workOrder.getRequireDay(), workOrder.getDayDay(), workOrder.getDueDate(), workOrder.getWorkorderRrn()};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public List<WorkOrder> getWorkOrderByIdLike(String orderId) {
            String sql = "SELECT WORKORDER_RRN,WORKORDER_ID,PRODUCT_ID,PLANNED_YIELD,STATUS,COMMENTS,CREATE_USER," +
                    "CUSTOMER,PLAN_TIME,WORKORDER_PRIORITY,ORDER_DIE_QTY,START_DAY," +
                    "REQUIRE_DAY,DAY_DAY,DUE_DATE,CUSTOMER_ORDER,CUSTOMER_ORDER_NO,SHIPPED_QTY,WORKORDER_TYPE,ERP_CLIENT," +
                    "ERP_FACTORY,START_QTY " +
                    "FROM" +
                    "  WORKORDER WHERE 1=1 ";
            Object[] args = new Object[]{};
            if (StringUtils.isNotEmpty(orderId)) {
                sql = sql + "  AND WORKORDER_ID LIKE ? ";
                args = new Object[]{"%" + orderId + "%"};
            }
            return jdbcTemplate.query(sql, args, new RowMapper<WorkOrder>() {
                @Override
                public WorkOrder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    WorkOrder order = new WorkOrder();
                    order.mapRow(rs, rowNum);
                    return order;
                }
            });
        }

        @Override
        public Page getWorkOrderImpDBByOrderRrn(Page page, long orderRrn) {
            String sql =
                    "SELECT * FROM WORKORDER_IMPO_WAFER WHERE WORKORDER_RRN=? ORDER BY CARRIER_RRN," + "SOURCE_WAFER_ID";
            return jdbcTemplate.queryForPage(page, sql, new Object[]{orderRrn},
                                             new BeanPropertyRowMapper<>(WorkOrderImpSourceWaferDb.class));
        }

        @Override
        public WorkOrderImpSourceWaferDb getImportWkByImpRrn(String uniqueNum) {
            String sql = "SELECT * FROM WORKORDER_IMPO_WAFER WHERE UNIQUE_NUM=?";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{uniqueNum},
                                                       new BeanPropertyRowMapper<>(WorkOrderImpSourceWaferDb.class));
        }

        @Override
        public void workOrderImpCreateLotSuccess(List<WorkOrderImpSourceWaferDb> list) {
            String sql = "UPDATE WORKORDER_IMPO_WAFER SET CARRIER_RRN=?,CARRIER_ID=?,LOT_ID=?,LOT_RRN=?," + "STATUS=? " +
                    "WHERE UNIQUE_NUM=?";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setLong(1, list.get(i).getCarrierRrn());
                    ps.setString(2, list.get(i).getCarrierId());
                    ps.setString(3, list.get(i).getLotId());
                    ps.setLong(4, list.get(i).getLotRrn());
                    ps.setString(5, WorkOrderImpSourceWaferDb.COMPLETED);
                    ps.setString(6, list.get(i).getUniqueNum());
                }

                @Override
                public int getBatchSize() {
                    return list.size();
                }
            });
        }

        @Override
        public WorkOrder getWorkOrderByRrn(Long workOrderRrn) {
            String sql = "SELECT WORKORDER_RRN,WORKORDER_ID,PRODUCT_ID,PLANNED_YIELD,STATUS,COMMENTS,CREATE_USER," +
                    "CUSTOMER,PLAN_TIME,WORKORDER_PRIORITY,ORDER_DIE_QTY,START_DAY,REQUIRE_DAY," +
                    "DAY_DAY,DUE_DATE,CUSTOMER_ORDER,CUSTOMER_ORDER_NO,SHIPPED_QTY,WORKORDER_TYPE,ERP_CLIENT,ERP_FACTORY,START_QTY FROM" +
                    " WORKORDER WHERE WORKORDER_RRN=? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{workOrderRrn}, new RowMapper<WorkOrder>() {
                @Override
                public WorkOrder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    WorkOrder order = new WorkOrder();
                    order.mapRow(rs, rowNum);
                    return order;
                }
            });
        }

        @Override
        public List<WorkOrder> getWorkOrderByCondition(Map<String, Object> condition) {
            StringBuffer sql = new StringBuffer(
                    "SELECT WORKORDER_RRN,WORKORDER_ID,PRODUCT_ID,PLANNED_YIELD,STATUS,COMMENTS,CREATE_USER," +
                            "CUSTOMER," + "PLAN_TIME,WORKORDER_PRIORITY,ORDER_DIE_QTY,START_DAY," + "REQUIRE_DAY,DAY_DAY," +
                            "DUE_DATE,CUSTOMER_ORDER,CUSTOMER_ORDER_NO,SHIPPED_QTY,WORKORDER_TYPE,ERP_CLIENT,ERP_FACTORY,START_QTY " +
                            "FROM  WORKORDER WHERE 1=1 ");
            List<Object> args = new ArrayList<Object>();
            if (StringUtils.isNotEmpty(MapUtils.getString(condition, "workOrderId"))) {
                sql.append("  AND WORKORDER_ID = ? ");
                args.add(MapUtils.getString(condition, "workOrderId"));
            }
            if (StringUtils.isNotEmpty(MapUtils.getString(condition, "status"))) {
                sql.append("  AND STATUS = ? ");
                args.add(MapUtils.getString(condition, "status"));
            }
            if (StringUtils.isNotEmpty(MapUtils.getString(condition, "startDay"))) {
                sql.append("  AND START_DAY > ? ");
                args.add(condition.get("startDay"));
            }
            sql.append("  ORDER BY WORKORDER_RRN DESC ");
            return jdbcTemplate.query(sql.toString(), args.toArray(), new RowMapper<WorkOrder>() {
                @Override
                public WorkOrder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    WorkOrder order = new WorkOrder();
                    order.mapRow(rs, rowNum);
                    return order;
                }
            });
        }

        @Override
        public int getCompleteDieQtyByWorkOrderRrn(Long workOrderRrn) {
            String sql = "SELECT SUM(PU.SOURCE_QUANTITY) FROM ERP_PACKAGE P JOIN ERP_PACKAGE_UNIT PU ON P" + ".TRANS_RRN" +
                    "=PU.TRANS_RRN" + " JOIN WORKORDER W ON W.WORKORDER_ID=P.WORK_ORDER_ID WHERE W.WORKORDER_RRN=?";
            return jdbcTemplate.queryForObject(sql, new Object[]{workOrderRrn}, Integer.class);
        }

        @Override
        public void completeWorkOrder(Long workOrderRrn) {
            String sql = "UPDATE WORKORDER SET STATUS=? WHERE WORKORDER_RRN=?";
            Object[] args = {WorkOrderImpSourceWaferDb.COMPLETED, workOrderRrn};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public List<String> getMesProductIdBySdsProductId(String sdsProductId) {
            String sql = "SELECT T.MES_PRODUCT_ID FROM T_SDS_MES_PRODUCT_MAPPING T WHERE T.SDS_PRODUCT_ID = ? ";
            return jdbcTemplate.query(sql, new Object[]{sdsProductId}, String.class);
        }

        @Override
        public boolean checkMesProSdsProMapping(String productId, String sdsProductId) {
            String sql = "SELECT count(*) FROM t_sds_mes_product_mapping WHERE mes_product_id = ? and " + "sds_product_id" +
                    "=?";
            int count = jdbcTemplate.queryForObjectWithNull(sql, new Object[]{productId, sdsProductId}, int.class);
            if (count > 0) {
                return true;
            } else {
                return false;
            }
        }

        @Override
        public void storeWorkOrderImpDBList(List<WorkOrderImpSourceWaferDb> dataList) {
            String sql = "INSERT INTO WORKORDER_IMPO_WAFER " + " (UNIQUE_NUM,WORKORDER_RRN,CARRIER_RRN,CARRIER_ID,LOT_ID," +
                    "LOT_RRN,MATERIAL_ID," + "	MATERIAL_LOT_NUM,MATERIAL_LOT_BIN,SOURCE_WAFER_ID," + " " +
                    "SOURCE_WAFER_DIE_QTY,STATUS,sds_product)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setString(1, dataList.get(i).getUniqueNum());
                    ps.setLong(2, dataList.get(i).getWorkorderRrn());
                    ps.setLong(3, dataList.get(i).getCarrierRrn());
                    ps.setString(4, dataList.get(i).getCarrierId());
                    ps.setString(5, dataList.get(i).getLotId());
                    ps.setLong(6, dataList.get(i).getLotRrn());
                    ps.setString(7, dataList.get(i).getMaterialId());
                    ps.setString(8, dataList.get(i).getMaterialLotNum());
                    ps.setString(9, dataList.get(i).getMaterialLotBin());
                    ps.setString(10, dataList.get(i).getSourceWaferId());
                    ps.setInt(11, dataList.get(i).getSourceWaferDieQty());
                    ps.setString(12, dataList.get(i).getStatus());
                    ps.setString(13, dataList.get(i).getSdsProduct());
                }

                @Override
                public int getBatchSize() {
                    return dataList.size();
                }
            });

        }

        @Override
        public WorkOrderImpSourceWaferDb getWorkOrderImpDBByRrn(String importRrn) {
            String sql = "SELECT * FROM WORKORDER_IMPO_WAFER WHERE UNIQUE_NUM=?";
            Object[] args = new Object[]{importRrn};
            return jdbcTemplate.queryForObjectWithNull(sql, args, new BeanPropertyRowMapper<WorkOrderImpSourceWaferDb>(
                    WorkOrderImpSourceWaferDb.class));
        }

        @Override
        public WorkOrderImpSourceWaferDb getWorkOrderImp(String lotId) {
            String sql = "SELECT * FROM WORKORDER_IMPO WHERE SOURCE_LOT_ID=?";
            Object[] args = new Object[]{lotId};
            //用list返回第一个，暂时先这样防止多个结果
            List<WorkOrderImpSourceWaferDb> list = jdbcTemplate.query(sql, args,
                                                                      new BeanPropertyRowMapper<WorkOrderImpSourceWaferDb>(
                                                                              WorkOrderImpSourceWaferDb.class));
            if (list == null || list.size() == 0) {
                return null;
            } else {
                return list.get(0);
            }

        }

        @Override
        public List<WorkOrderImpSourceWaferDb> getWorkOrderImpWaferByOrderRrn(long workOrderRrn) {
            String sql = "SELECT * FROM WORKORDER_IMPO_WAFER WHERE WORKORDER_RRN=?";
            Object[] args = new Object[]{workOrderRrn};
            return jdbcTemplate.query(sql, args, new BeanPropertyRowMapper<WorkOrderImpSourceWaferDb>(
                    WorkOrderImpSourceWaferDb.class));
        }

        @Override
        public void insertProductMapping(String productId, String sdsProductId, String userId) {
            StringBuffer sql = new StringBuffer();
            sql.append(" insert into t_sds_mes_product_mapping  		     				");
            sql.append("   (sds_product_id, mes_product_id, create_time, create_user)   ");
            sql.append(" values                                                         ");
            sql.append("   (?, ?, sysdate, ?)                                           ");
            jdbcTemplate.update(sql.toString(), new Object[]{sdsProductId, productId, userId});
        }

        @Override
        public void updateProductMapping(String productId, String sdsProductId, String userId) {
            StringBuffer sql = new StringBuffer();
            sql.append(" update t_sds_mes_product_mapping 										");
            sql.append("    set sds_product_id = ?, update_time = sysdate, update_user = ?    ");
            sql.append("  where mes_product_id = ?                                                ");
            jdbcTemplate.update(sql.toString(), new Object[]{sdsProductId, userId, productId});
        }

        @Override
        public List<SdsWorkOrderInfo> getSdsWorkOrderInfoByWorkOrderId(String workorderId) {
            String sql = "SELECT * FROM T_SDS_WORKORDER_INFO WHERE RECEIVE_STATUS = '0' ";
            if (StringUtils.isNotBlank(workorderId)) {
                sql += " AND  WORKORDER_ID = '" + workorderId + "'";
            }
            sql += " order by WORKORDER_ID ";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<SdsWorkOrderInfo>(SdsWorkOrderInfo.class));
        }

        @Override
        public List<SdsWorkOrderMaterialInfo> getSdsWorkOrderMaterialInfoByWorkOrderRrn(long workorderRrn) {
            String sql = "SELECT * FROM T_SDS_WORKORDER_MATERIAL_INFO T WHERE T.WORKORDER_RRN = ? ";
            return jdbcTemplate.query(sql, new Object[]{workorderRrn},
                                      new BeanPropertyRowMapper<SdsWorkOrderMaterialInfo>(SdsWorkOrderMaterialInfo.class));
        }

        @Override
        public void updateProductMappingStatus(long workorderRrn, String receiveStatus) {
            String sql = " UPDATE T_SDS_WORKORDER_INFO  SET RECEIVE_STATUS = ? WHERE WORKORDER_RRN = ? ";
            jdbcTemplate.update(sql, new Object[]{receiveStatus, workorderRrn});
        }

        @Override
        public List<Map> getSdsMesMappingInfoByProductId(String productId) {
            StringBuffer sql = new StringBuffer();
            sql.append(" select t.sds_product_id,               ");
            sql.append("        t.mes_product_id,               ");
            sql.append("        t.create_user,                  ");
            sql.append("        t.create_time,                  ");
            sql.append("        t.update_user,                  ");
            sql.append("        t.update_time                   ");
            sql.append("   from t_sds_mes_product_mapping t     ");
            if (StringUtils.isNotBlank(productId)) {
                sql.append("  where t.mes_product_id = '" + productId + "'");
            }

            return jdbcTemplate.query(sql.toString(), new Object[]{}, new RowMapper() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> dataMap = new HashMap<>();
                    dataMap.put("productId", rs.getString("mes_product_id"));
                    dataMap.put("sdsProductId", rs.getString("sds_product_id"));
                    dataMap.put("createUser", rs.getString("create_user"));
                    dataMap.put("createTime", rs.getTimestamp("create_time"));
                    dataMap.put("updateUser", rs.getString("update_user"));
                    dataMap.put("updateTime", rs.getTimestamp("update_time"));
                    return dataMap;
                }
            });
        }

        @Override
        public List<String> getWorkOrderImpWaferByWaferId(List<String> collect) {
            if (CollectionUtils.isEmpty(collect)) {
                return new ArrayList<String>();
            }
            String sql = "SELECT source_wafer_id FROM WORKORDER_IMPO_WAFER WHERE source_wafer_id " + "in (:ids)";
            Map<String, Object> regs = new HashMap<String, Object>();
            regs.put("ids", collect);
            return jdbcTemplate.query(sql, regs, String.class);
        }

        @Override
        public int getAreadySdsProReceiveCount(String deleteProductMapping) {
            String sql = "SELECT count(*) FROM workorder_impo_wafer WHERE sds_product=?";
            Object[] args = new Object[]{deleteProductMapping};
            return jdbcTemplate.queryForObjectWithNull(sql, args, int.class);
        }

        @Override
        public void deleteProductMapping(String mesproduct, String sdsproduct) {
            String sql = "DELETE FROM t_sds_mes_product_mapping WHERE mes_product_id=? and sds_product_id=?";
            Object[] args = new Object[]{mesproduct, sdsproduct};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public List<SdsWorkOrderMaterialInfo> getSdsMaterialInfoByWorkOrderId(long workorderRrn) {
            String sql = "SELECT * from t_sds_workorder_material_info WHERE workorder_rrn=?";
            return jdbcTemplate
                    .query(sql, new Object[]{workorderRrn}, new BeanPropertyRowMapper(SdsWorkOrderMaterialInfo.class));
        }

        @Override
        public SdsWorkOrderInfo getNotReceiveSdsWorkOrderByWorkOrderRrn(long sdsWorkorderRrn) {
            String sql = "SELECT * FROM T_SDS_WORKORDER_INFO WHERE RECEIVE_STATUS = '0' and workorder_rrn=?";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{sdsWorkorderRrn},
                                                       new BeanPropertyRowMapper<SdsWorkOrderInfo>(SdsWorkOrderInfo.class));
        }

        @Override
        public List<SdsWorkOrderInfo> getSdsWorkOrderTmpById(String workorderId) {
            String sql = "SELECT * FROM T_SDS_WORKORDER_INFO WHERE workorder_id=?";
            return jdbcTemplate.query(sql, new Object[]{workorderId},
                                      new BeanPropertyRowMapper<SdsWorkOrderInfo>(SdsWorkOrderInfo.class));
        }

        @Override
        public void insertWorkOrderLines(List<WorkOrderLine> workOrderLines) {

            String sql = "INSERT INTO WORKORDER_LINE (WORKORDER_LINE_RRN, WORKORDER_RRN ," +
                    " MATERIAL_ID, MATERIAL_TYPE , REQUIRE_QTY) VALUES(?,?,?,?,?)";

            List<Object[]> args = workOrderLines.stream().map(workOrderLine -> {
                Object[] arg = {workOrderLine.getWorkorderLineRrn(), workOrderLine.getWorkorderRrn(),
                        workOrderLine.getMaterialId(), workOrderLine.getMaterialType(), workOrderLine.getRequireQty()};

                return arg;
            }).collect(Collectors.toList());

            jdbcTemplate.batchUpdate(sql, args);

        }

        @Override
        public void updateShipQty4WorkOrder(Long workorderRrn, Integer shipQty) {
            String sql = "UPDATE WORKORDER SET SHIPPED_QTY = ? WHERE WORKORDER_RRN=?";
            Object[] args = {shipQty, workorderRrn};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void updateStartQty4WorkOrder(Long workorderRrn, Integer startQty) {
            String sql = "UPDATE WORKORDER SET START_QTY = ? WHERE WORKORDER_RRN=?";
            Object[] args = {startQty, workorderRrn};
            jdbcTemplate.update(sql, args);
        }

    }