uas-eis/src/main/java/com/uas/eis/service/Impl/ERPServiceImpl.java

package com.uas.eis.service.Impl;

import com.uas.eis.dao.*;
import com.uas.eis.sdk.dto.ListResp;
import com.uas.eis.sdk.dto.QueryStockListReq;
import com.uas.eis.sdk.dto.StockListDTO;
import com.uas.eis.beans.result.Result;
import com.uas.eis.service.ERPService;
import com.uas.eis.utils.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;


/**
 * @author wuyx
 * @email wuyx@usoftchina.com
 * @date 2025-04-25
 */
@Service
public class ERPServiceImpl implements ERPService {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    @Autowired
    private BaseDao baseDao;

    @Override
    public Result getStockList(QueryStockListReq queryStockListReq) {
        ListResp<StockListDTO> listResp = new ListResp<>();
        listResp.setPageNum(queryStockListReq.getPageNum());
        listResp.setPageSize(queryStockListReq.getPageSize());
        int page = queryStockListReq.getPageNum();
        int pageSize = queryStockListReq.getPageSize();
        int start = ((page - 1) * pageSize + 1);
        int end = page * pageSize;
        StringBuffer cond_sql = new StringBuffer();
        if(StringUtil.hasText(queryStockListReq.getMaterialNo())){
            cond_sql.append(" and upper(pw_prodcode) like '%").append(queryStockListReq.getMaterialNo().toUpperCase()).append("%' ");
        }
        if(StringUtil.hasText(queryStockListReq.getMaterialName())){
            cond_sql.append(" and upper(pr_detail) like '%").append(queryStockListReq.getMaterialName().toUpperCase()).append("%' ");
        }
        if(StringUtil.hasText(queryStockListReq.getSpecs())){
            cond_sql.append(" and upper(pr_spec) like '%").append(queryStockListReq.getSpecs().toUpperCase()).append("%' ");
        }
        if(StringUtil.hasText(queryStockListReq.getSpecsRules())){
            cond_sql.append(" and upper(pr_specrule) like '%").append(queryStockListReq.getSpecsRules().toUpperCase()).append("%' ");
        }
        if(StringUtil.hasText(queryStockListReq.getWarehouse())){
            cond_sql.append(" and upper(warehouse) like '%").append(queryStockListReq.getWarehouse().toUpperCase()).append("%' ");
        }

        //获取总数
        StringBuffer searchSql = new StringBuffer();
        searchSql.append("select ")
                .append("pw_avprice,pr_location,pw_prodcode,pr_orispeccode,pr_brand,pr_spec,pr_specrule pr_spec_rule,pw_prodid,pr_unit,pw_id,pw_whcode,pw_onhand,pw_movedate,pr_buyername,pw_amount,pr_serial,wh_description,pw_lastdate,pr_detail")
                .append(",nvl(purc_inprogress,0) purc_inprogress  ")
                .append(" from ")
                .append("productWH tab left join Product on pw_prodcode=pr_code left join warehouse on pw_whcode=wh_code ")
                .append(" left join (")
                .append(" select pd_prodcode ,sum(pd_qty-nvl(pd_acceptqty,0)) purc_inprogress ")
                .append(" from purchasedetail left join purchase on pu_id = pd_puid ")
                .append(" where pu_statuscode = 'AUDITED' and pd_qty-nvl(pd_acceptqty,0)>0 and nvl(pd_mrpstatuscode,0) not in('FINISH','NULLIFIED','FREEZE') " )
                .append(" group by pd_prodcode ")
                .append(") on pd_prodcode = pw_prodcode " )
                .append("WHERE nvl(pw_onhand,0) > 0 ").append(cond_sql);
        StringBuffer countSql = new StringBuffer("select count(1) from ( ");
        countSql.append(searchSql);
        countSql.append(" )");
        logger.info("countSql:{}",countSql.toString());
        Integer total = baseDao.getJdbcTemplate().queryForObject(countSql.toString(), Integer.class);
        listResp.setTotal(total);
        List<StockListDTO> stockListDTOS = new ArrayList<>();
        if(total > 0){
            StringBuffer querySql = new StringBuffer("select * from (select TT.*, ROWNUM rn from ( ");
            querySql.append(searchSql)
                    .append("order by pw_id desc");
            querySql.append(" )TT where ROWNUM <= ");
            querySql.append(end);
            querySql.append(") where rn >= ");
            querySql.append(start);
            logger.info("querySql:{}",querySql.toString());
            stockListDTOS = baseDao.getJdbcTemplate().query(querySql.toString(),
                    new BeanPropertyRowMapper<>(StockListDTO.class));
        }
        listResp.setList(stockListDTOS);
        return Result.success(listResp);
    }
}