feat: 台账库存查询、导出

cy

2023-11-20 717ff115ee5c57c8df0fd491b40b848090d2c68e

feat: 台账库存查询、导出

 consum-base/pom.xml

@@ -60,7 +60,12 @@
            <artifactId>consum-model-pojo</artifactId>
            <version>1.0.0-SNAPSHOT</version>
        </dependency>

        <!--EasyPOI 工具类,Excel的快速导入导出,Excel模板导出,Word模板导出 官网：http://www.wupaas.com/ -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
    </dependencies>

</project>

 consum-base/src/main/java/com/consum/base/controller/FinWarehouseLedgerController.java

New file
@@ -0,0 +1,104 @@
package com.consum.base.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.consum.base.BaseController;
import com.consum.base.core.utils.CommonUtil;
import com.consum.base.pojo.query.LWhLedgerQry;
import com.consum.base.service.FinWarehouseLedgerServiceImpl;
import com.consum.model.po.FinSysTenantUser;
import com.iplatform.model.po.S_user_core;
import com.walker.db.page.GenericPager;
import com.walker.web.ResponseValue;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName FinWarehouseLedgerController
 * @Author cy
 * @Date 2023/11/20
 * @Description 仓库台账
 * @Version 1.0
 **/
@RestController
@RequestMapping("/pc/fin/warehouse/ledger")
public class FinWarehouseLedgerController extends BaseController {
    @Resource
    private FinWarehouseLedgerServiceImpl finWarehouseLedgerService;

    /**
     * @Description 分页列表查询
     * @Author wh
     * @Date 2023/7/11 13:59
     */
    @RequestMapping("/getList")
    public ResponseValue getLedgerList() {
        LWhLedgerQry param = CommonUtil.getObjFromReq(LWhLedgerQry.class);
        S_user_core currentUser = this.getCurrentUser();
        if (currentUser == null) {
            return ResponseValue.error("登录用户信息不存在");
        }
        /*当前登录人只能看到自己机构下的列表*/
        FinSysTenantUser sysInfo = this.getSysInfo();
        String tenantId = sysInfo.getTenantId();
        Long paramAgencyId = param.getAgencyId();
        if (paramAgencyId == null || !paramAgencyId.toString().startsWith(tenantId)) {
            param.setAgencyId(Long.valueOf(tenantId));
        }
        param.setStates((short) 1);
        GenericPager pager = finWarehouseLedgerService.ledgerList(param);
        return ResponseValue.success(pager);
    }

    @RequestMapping("/getListExport")
    public void getListExport(HttpServletResponse response) throws Exception {
        LWhLedgerQry param = CommonUtil.getObjFromReq(LWhLedgerQry.class);
        S_user_core currentUser = this.getCurrentUser();
        if (currentUser == null) {
            return;
        }
        /*当前登录人只能看到自己机构下的列表*/
        FinSysTenantUser sysInfo = this.getSysInfo();
        String tenantId = sysInfo.getTenantId();
        Long paramAgencyId = param.getAgencyId();
        if (paramAgencyId == null || !paramAgencyId.toString().startsWith(tenantId)) {
            param.setAgencyId(Long.valueOf(tenantId));
        }
        param.setStates((short) 1);
        param.setPageNum(1);
        param.setPageSize(Integer.MAX_VALUE);
        GenericPager<Map<String, Object>> pager = finWarehouseLedgerService.ledgerList(param);
        List<Map<String, Object>> datas = pager.getDatas();
        org.springframework.core.io.Resource resource = new ClassPathResource("import/taiZhangExpTemp.xlsx");
        // 获取文件输入流
        InputStream inputStream = resource.getInputStream();
        Workbook wb = new XSSFWorkbook(inputStream);
        TemplateExportParams params = new TemplateExportParams();
        params.setTemplateWb(wb);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("dataList", datas);

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("库存查询.xlsx", "utf-8"));
        try (OutputStream outputStream = response.getOutputStream()) {
            workbook.write(outputStream);
            workbook.close();
        }

    }
}

 consum-base/src/main/java/com/consum/base/pojo/query/LWhLedgerQry.java

New file
@@ -0,0 +1,45 @@
package com.consum.base.pojo.query;

import com.walker.web.param.ParamRequest;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @ClassName LWhLedgerQry
 * @Author cy
 * @Date 2023/11/20
 * @Description
 * @Version 1.0
 **/
@Data
@ApiModel(value = "机构台账参数")
public class LWhLedgerQry extends ParamRequest {
    @ApiModelProperty("机构")
    private Long agencyId;

    @ApiModelProperty("仓库类型")
    private Long warehouseType;

    @ApiModelProperty("仓库id")
    private Long warehouseId;

    @ApiModelProperty("物品名称")
    private String goodsTemplateName;

    @ApiModelProperty("分类")
    private Long categoryId;

    @ApiModelProperty("价值类型")
    private Long costType;

    @ApiModelProperty("状态（0=在途调拨；1=入库未分发；2=已下发；3=报废 4 零星出库）")
    private Short states;

    @ApiModelProperty(value = "每页显示条数")
    private Integer pageSize = 10;

    @ApiModelProperty(value = "当前页数")
    private Integer pageNum = 1;

}

 consum-base/src/main/java/com/consum/base/service/FinWarehouseLedgerServiceImpl.java

New file
@@ -0,0 +1,66 @@
package com.consum.base.service;

import com.consum.base.core.utils.MapperUtil;
import com.consum.base.pojo.query.LWhLedgerQry;
import com.walker.db.page.GenericPager;
import com.walker.infrastructure.utils.StringUtils;
import com.walker.jdbc.service.BaseServiceImpl;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.Map;

/**
 * @ClassName FinWarehouseLedgerServiceImpl
 * @Author cy
 * @Date 2023/11/20
 * @Description
 * @Version 1.0
 **/
@Service
public class FinWarehouseLedgerServiceImpl extends BaseServiceImpl {

    private static String LEDGER_LIST_QUERY_PREFIX = "SELECT ware.AGENCY_NAME ,ware.WAREHOUSE_NAME ,goodsTemp.CATEGORY_NAME ,CASE goods.COST_TYPE WHEN 1 THEN 'A类' WHEN 2 THEN 'B类' WHEN 3 THEN 'C类' END AS cost_Type,goods.GOODS_TEMPLATE_NAME ,goods.BASE_GOODS_MODELS_NAME ,goods.UNIT unit,COUNt(goods.id) kucun FROM L_WH_GOODS goods LEFT JOIN BASE_GOODS_TEMPLATE goodsTemp ON goods.BASE_GOODS_TEMPLATE_ID=goodsTemp.id LEFT JOIN BASE_WAREHOUSE ware ON ware.id=goods.WAREHOUSE_ID where 1=1 ";

    public GenericPager<Map<String, Object>> ledgerList(LWhLedgerQry param) {
        StringBuilder sql = new StringBuilder(LEDGER_LIST_QUERY_PREFIX);
        Map<String, Object> paramts = new HashMap<>();
        //状态（0=在途调拨；1=入库未分发；2=已下发；3=报废 4 零星出库）
        if (param.getStates() != null) {
            sql.append(" and goods.states = :states");
            paramts.put("states", param.getStates());
        }
        //机构
        if (param.getAgencyId() != null) {
            sql.append(" and left(ware.AGENCY_ID, length(:lengthAgencyId)) = :agencyId");
            paramts.put("lengthAgencyId", param.getAgencyId());
            paramts.put("agencyId", param.getAgencyId());
        }
        //仓库类型
        if (param.getWarehouseType() != null) {
            sql.append(" AND goods.WAREHOUSE_TYPE = :warehouseType");
            paramts.put("warehouseType", param.getWarehouseType());
        }
        if (param.getWarehouseId() != null) {
            sql.append(" AND goods.WAREHOUSE_ID = :warehouseId");
            paramts.put("warehouseId", param.getWarehouseId());
        }
        //物品名称
        if (StringUtils.isNotEmpty(param.getGoodsTemplateName())) {
            sql.append(" AND goods.GOODS_TEMPLATE_NAME like :goodsTemplateName");
            paramts.put("goodsTemplateName", StringUtils.CHAR_PERCENT + param.getGoodsTemplateName() + StringUtils.CHAR_PERCENT);
        }
        //分类
        if (param.getCategoryId() != null) {
            sql.append(" AND goodsTemp.CATEGORY_ID=:categoryId");
            paramts.put("categoryId", param.getCategoryId());
        }
        //价值类型
        if (param.getCostType() != null) {
            sql.append(" AND goods.COST_TYPE=:costType");
            paramts.put("costType", param.getCostType());
        }
        sql.append(" GROUP BY ware.AGENCY_NAME,ware.WAREHOUSE_NAME,goodsTemp.CATEGORY_NAME,goods.COST_TYPE,goods.GOODS_TEMPLATE_NAME,goods.BASE_GOODS_MODELS_NAME,goods.UNIT");
        return selectSplit(sql.toString(), paramts, param.getPageNum(), param.getPageSize(), new MapperUtil());
    }
}

 consum-base/src/main/java/com/consum/base/service/LWhGoodsService.java

@@ -1,6 +1,5 @@
package com.consum.base.service;

import com.consum.model.po.LWhGoods;
import com.walker.infrastructure.utils.CollectionUtils;
import com.walker.jdbc.service.BaseServiceImpl;
import org.apache.commons.lang3.StringUtils;
@@ -238,26 +237,27 @@
    private static String MOD_GOODS_TRANSFERING_PREFIX = "update L_WH_GOODS set ";

    public int modGoodsTransfering(List<Long> whGoods, Integer warehouseType, Long toWarehouseId, String toWarehouseName, Integer states) {

        List<LWhGoods> params = new ArrayList<>();
        whGoods.stream().forEach(id -> {
            LWhGoods lWhGoods = new LWhGoods();
            lWhGoods.setId(id);
            if (toWarehouseId != null) {
                lWhGoods.setWarehouseType(warehouseType);
            }
            if (toWarehouseId != null) {
                lWhGoods.setWarehouseId(toWarehouseId);
            }
            if (toWarehouseName != null) {
                lWhGoods.setWarehouseName(toWarehouseName);
            }
            if (states != null) {
                lWhGoods.setStates(states);
            }
            params.add(lWhGoods);
        });
        return updateBatch(params);
        StringBuilder sql = new StringBuilder(MOD_GOODS_TRANSFERING_PREFIX);
        ArrayList<Object> params = new ArrayList<>();
        if (toWarehouseId != null) {
            sql.append(", WAREHOUSE_TYPE=?");
            params.add(warehouseType);
        }
        if (toWarehouseId != null) {
            sql.append(", WAREHOUSE_ID=?");
            params.add(toWarehouseId);
        }
        if (toWarehouseName != null) {
            sql.append(", WAREHOUSE_NAME=?");
            params.add(toWarehouseName);
        }
        if (states != null) {
            sql.append(", STATES=?");
            params.add(states);
        }
        sql.deleteCharAt(22);
        sql.append(" where id in(").append(StringUtils.join(whGoods, ",")).append(")");
        return update(sql.toString(), params.toArray());
    }

}

 deploy-jar-single/src/main/resources/import/taiZhangExpTemp.xlsx

Binary files differ