From 170c89b9b8e6d5c3117d53e7b38f782651ebfcde Mon Sep 17 00:00:00 2001
From: futian.liu <liufutianyoo@163.com>
Date: 星期五, 22 十二月 2023 14:04:04 +0800
Subject: [PATCH] 物品统计表中总数量改为在库数量+报废数量
---
consum-base/src/main/java/com/consum/base/service/impl/LWhGoodsStatisticsServiceImpl.java | 120 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 120 insertions(+), 0 deletions(-)
diff --git a/consum-base/src/main/java/com/consum/base/service/impl/LWhGoodsStatisticsServiceImpl.java b/consum-base/src/main/java/com/consum/base/service/impl/LWhGoodsStatisticsServiceImpl.java
new file mode 100644
index 0000000..f4792e9
--- /dev/null
+++ b/consum-base/src/main/java/com/consum/base/service/impl/LWhGoodsStatisticsServiceImpl.java
@@ -0,0 +1,120 @@
+package com.consum.base.service.impl;
+
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.commons.compress.utils.Lists;
+import org.springframework.stereotype.Service;
+
+import com.consum.base.core.utils.MapUtils;
+import com.consum.base.core.utils.MapperUtil;
+import com.consum.base.pojo.query.LWhGoodsStatisQry;
+import com.consum.base.pojo.response.GoodsStatisticsInfoVO;
+import com.consum.base.service.LWhGoodsStatisticsService;
+import com.walker.infrastructure.utils.StringUtils;
+import com.walker.jdbc.service.BaseServiceImpl;
+
+/**
+ * @ClassName LWhGoodsStatisticsServiceImpl
+ * @Author cy
+ * @Date 2023/11/21
+ * @Description
+ * @Version 1.0
+ **/
+@Service
+public class LWhGoodsStatisticsServiceImpl extends BaseServiceImpl implements LWhGoodsStatisticsService {
+ private String GET_WHGOODS_STATIS_LIST_COLUMN =
+ "SELECT tCangKu.org_name,tCangKu.WAREHOUSE_NAME,goodsTemp.GOODS_CODE,tKuCun.GOODS_TEMPLATE_NAME,tKuCun.BASE_GOODS_MODELS_NAME,CASE tKuCun.COST_TYPE WHEN 1 THEN 'A绫�' WHEN 2 THEN 'B绫�' WHEN 3 THEN 'C绫�' END AS cost_Type,tKuCun.total_num,tKuCun.zai_ku_num,tKuCun.diao_bo_num,tKuCun.bao_fei_num FROM ";
+ private String GET_WHGOODS_STATIS_LIST_T_CANGKU =
+ "(SELECT tenant.id tenantId,tenant.NAME AS org_name,ware.*FROM fin_sys_tenant tenant right JOIN (SELECT 0 WAREHOUSE_TYPE,id,AGENCY_ID,WAREHOUSE_NAME FROM BASE_WAREHOUSE UNION ALL SELECT 1 WAREHOUSE_TYPE,id,TENANT_ID AGENCY_ID,`NAME` WAREHOUSE_NAME FROM fin_sys_tenant_department) ware ON tenant.id=ware.AGENCY_ID ";
+ private String GET_WHGOODS_STATIS_LIST_T_CANGKU_WHERE = "where 1=1";
+ private String GET_WHGOODS_STATIS_LIST_T_CANGKU_END = ") tCangKu LEFT JOIN";
+ private String GET_WHGOODS_STATIS_LIST_T_KUCUN =
+ "(SELECT WAREHOUSE_TYPE,WAREHOUSE_ID,BASE_GOODS_TEMPLATE_ID,GOODS_TEMPLATE_NAME,COST_TYPE,BASE_GOODS_MODELS_ID,BASE_GOODS_MODELS_NAME,COUNT(CASE WHEN goods.STATES BETWEEN 0 AND 2 THEN 1 END) AS total_num,count(CASE WHEN goods.STATES=1 OR goods.STATES=2 THEN 1 END) zai_ku_num,count(CASE WHEN goods.STATES=0 THEN 1 END) diao_bo_num,count(CASE WHEN goods.STATES=3 THEN 1 END) bao_fei_num FROM l_wh_goods goods ";
+ private String GET_WHGOODS_STATIS_LIST_T_KUCUN_WHERE = "where 1=1";
+ private String GET_WHGOODS_STATIS_LIST_T_KUCUN_GROUP_BY =
+ " GROUP BY WAREHOUSE_TYPE,WAREHOUSE_ID,BASE_GOODS_TEMPLATE_ID,GOODS_TEMPLATE_NAME,BASE_GOODS_MODELS_ID,BASE_GOODS_MODELS_NAME,COST_TYPE) tKuCun ON tCangKu.WAREHOUSE_TYPE=tKuCun.WAREHOUSE_TYPE and tCangKu.id = tKuCun.WAREHOUSE_ID LEFT JOIN BASE_GOODS_TEMPLATE goodsTemp ON goodsTemp.id=tKuCun.BASE_GOODS_TEMPLATE_ID ";
+ private String GET_WHGOODS_STATIS_LIST_T_END_WHERE = " where 1=1";
+
+ @Override
+ public List getWhGoodsStatisList(LWhGoodsStatisQry param) {
+
+ List<GoodsStatisticsInfoVO> result = Lists.newArrayList();
+
+ StringBuilder sqlColumn = new StringBuilder(GET_WHGOODS_STATIS_LIST_COLUMN);
+ StringBuilder sqlCangKu = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_CANGKU);
+ StringBuilder sqlCangKuWhere = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_CANGKU_WHERE);
+ StringBuilder sqlCangKuEnd = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_CANGKU_END);
+ StringBuilder sqlKuCun = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_KUCUN);
+ StringBuilder sqlKuCunWhere = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_KUCUN_WHERE);
+ StringBuilder sqlKuCunGoupBy = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_KUCUN_GROUP_BY);
+ StringBuilder sqlEndWhere = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_END_WHERE);
+
+ HashMap<String, Object> paramts = new HashMap<>();
+ // 鏈烘瀯
+ if (param.getAgencyId() != null) {
+ // sqlCangKuWhere.append(" AND left(tenant.id, length(:lengthAgencyId)) = :agencyId");
+ sqlCangKuWhere.append(" AND tenant.id = :agencyId");
+ // paramts.put("lengthAgencyId", param.getAgencyId());
+ paramts.put("agencyId", param.getAgencyId());
+ }
+ // 閮ㄩ棬
+ if (param.getDepartmentId() != null) {
+ sqlCangKuWhere.append(" AND ware.WAREHOUSE_TYPE=1 and ware.id = :departmentId");
+ paramts.put("departmentId", param.getDepartmentId());
+ }
+ // 浠撳簱
+ if (param.getBaseWarehouseId() != null) {
+ sqlCangKuWhere.append(" AND ware.WAREHOUSE_TYPE =0 and ware.id = :warehouseId");
+ paramts.put("warehouseId", param.getBaseWarehouseId());
+ }
+ // 鐗╁搧鍚嶇О
+ if (StringUtils.isNotEmpty(param.getGoodsTemplateName())) {
+ sqlKuCunWhere.append(" AND goods.GOODS_TEMPLATE_NAME like :goodsTemplateName");
+ paramts.put("goodsTemplateName",
+ StringUtils.CHAR_PERCENT + param.getGoodsTemplateName() + StringUtils.CHAR_PERCENT);
+ }
+ if (param.getGoodsTemplateId() != null) {
+ sqlKuCunWhere.append(" AND goods.BASE_GOODS_TEMPLATE_ID=:goodsTemplateId");
+ paramts.put("goodsTemplateId", param.getGoodsTemplateId());
+ }
+ // 瑙勬牸鍨嬪彿
+ if (param.getBaseGoodsModelsId() != null) {
+ sqlKuCunWhere.append(" AND goods.BASE_GOODS_MODELS_ID=:baseGoodsModelsId");
+ paramts.put("baseGoodsModelsId", param.getBaseGoodsModelsId());
+ }
+ // 浠峰�肩被鍨�
+ if (param.getCostType() != null) {
+ // 灏嗘暟瀛楄浆鎹负瀵瑰簲鐨勫瓧绗�
+ char costType = (char)('A' + param.getCostType() - 1);
+ sqlKuCunWhere.append(" AND goods.COST_TYPE=':costType'");
+ paramts.put("costType", costType);
+ }
+
+ // // 鎿嶄綔鏃堕棿
+ // if (param.getDealTimeStart() != null) {
+ // sqlEnd.append(" and flow.DEAL_TIME >=:dealTimeStart ");
+ // paramts.put("dealTimeStart", param.getDealTimeStart() * 1000000);
+ // }
+ // if (param.getDealTimeEnd() != null) {
+ // sqlEnd.append(" and flow.DEAL_TIME <:dealTimeEnd ");
+ // paramts.put("dealTimeEnd", param.getDealTimeEnd() * 1000000 + 240000);
+ // }
+ sqlEndWhere.append(" ORDER BY tKuCun.total_num desc,tCangKu.tenantId asc");
+
+ StringBuilder sql = new StringBuilder();
+ sql.append(sqlColumn).append(sqlCangKu).append(sqlCangKuWhere).append(sqlCangKuEnd).append(sqlKuCun)
+ .append(sqlKuCunWhere).append(sqlKuCunGoupBy).append(sqlEndWhere);
+
+ List<Map<String, Object>> select = select(sql.toString(), paramts, new MapperUtil());
+ select.forEach(item -> {
+ GoodsStatisticsInfoVO goodsStatisticsInfoVO = MapUtils.convertMapToObj(item, GoodsStatisticsInfoVO.class);
+ // 缁熻鎬绘暟鏀逛负锛氬湪搴撴暟閲�+鎶ュ簾鏁伴噺
+ goodsStatisticsInfoVO
+ .setTotalNum(goodsStatisticsInfoVO.getZaiKuNum() + goodsStatisticsInfoVO.getBaoFeiNum());
+ result.add(goodsStatisticsInfoVO);
+ });
+ return result;
+ }
+}
--
Gitblit v1.9.1