From 717ff115ee5c57c8df0fd491b40b848090d2c68e Mon Sep 17 00:00:00 2001
From: cy <1664593601@qq.com>
Date: 星期一, 20 十一月 2023 14:48:53 +0800
Subject: [PATCH] feat: 台账库存查询、导出
---
consum-base/pom.xml | 7 +
consum-base/src/main/java/com/consum/base/controller/FinWarehouseLedgerController.java | 104 ++++++++++++++++++++
consum-base/src/main/java/com/consum/base/service/FinWarehouseLedgerServiceImpl.java | 66 +++++++++++++
consum-base/src/main/java/com/consum/base/pojo/query/LWhLedgerQry.java | 45 +++++++++
deploy-jar-single/src/main/resources/import/taiZhangExpTemp.xlsx | 0
consum-base/src/main/java/com/consum/base/service/LWhGoodsService.java | 42 ++++----
6 files changed, 242 insertions(+), 22 deletions(-)
diff --git a/consum-base/pom.xml b/consum-base/pom.xml
index 53c75f1..1efb686 100644
--- a/consum-base/pom.xml
+++ b/consum-base/pom.xml
@@ -60,7 +60,12 @@
<artifactId>consum-model-pojo</artifactId>
<version>1.0.0-SNAPSHOT</version>
</dependency>
-
+ <!--EasyPOI 宸ュ叿绫�,Excel鐨勫揩閫熷鍏ュ鍑�,Excel妯℃澘瀵煎嚭,Word妯℃澘瀵煎嚭 瀹樼綉锛歨ttp://www.wupaas.com/ -->
+ <dependency>
+ <groupId>cn.afterturn</groupId>
+ <artifactId>easypoi-base</artifactId>
+ <version>4.1.0</version>
+ </dependency>
</dependencies>
</project>
diff --git a/consum-base/src/main/java/com/consum/base/controller/FinWarehouseLedgerController.java b/consum-base/src/main/java/com/consum/base/controller/FinWarehouseLedgerController.java
new file mode 100644
index 0000000..2dd853c
--- /dev/null
+++ b/consum-base/src/main/java/com/consum/base/controller/FinWarehouseLedgerController.java
@@ -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();
+ }
+
+ }
+}
diff --git a/consum-base/src/main/java/com/consum/base/pojo/query/LWhLedgerQry.java b/consum-base/src/main/java/com/consum/base/pojo/query/LWhLedgerQry.java
new file mode 100644
index 0000000..7eaf7df
--- /dev/null
+++ b/consum-base/src/main/java/com/consum/base/pojo/query/LWhLedgerQry.java
@@ -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;
+
+}
diff --git a/consum-base/src/main/java/com/consum/base/service/FinWarehouseLedgerServiceImpl.java b/consum-base/src/main/java/com/consum/base/service/FinWarehouseLedgerServiceImpl.java
new file mode 100644
index 0000000..e468793
--- /dev/null
+++ b/consum-base/src/main/java/com/consum/base/service/FinWarehouseLedgerServiceImpl.java
@@ -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());
+ }
+}
diff --git a/consum-base/src/main/java/com/consum/base/service/LWhGoodsService.java b/consum-base/src/main/java/com/consum/base/service/LWhGoodsService.java
index 1881d31..af30c9a 100644
--- a/consum-base/src/main/java/com/consum/base/service/LWhGoodsService.java
+++ b/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());
}
}
diff --git a/deploy-jar-single/src/main/resources/import/taiZhangExpTemp.xlsx b/deploy-jar-single/src/main/resources/import/taiZhangExpTemp.xlsx
new file mode 100644
index 0000000..5d99e2e
--- /dev/null
+++ b/deploy-jar-single/src/main/resources/import/taiZhangExpTemp.xlsx
Binary files differ
--
Gitblit v1.9.1