package com.consum.base.service;
|
|
import com.consum.base.core.utils.MapperUtil;
|
import com.consum.base.pojo.query.LWhGoodsStatisQry;
|
import com.walker.infrastructure.utils.StringUtils;
|
import com.walker.jdbc.service.BaseServiceImpl;
|
import org.springframework.stereotype.Service;
|
|
import java.util.HashMap;
|
import java.util.List;
|
|
/**
|
* @ClassName LWhGoodsStatisticsServiceImpl
|
* @Author cy
|
* @Date 2023/11/21
|
* @Description
|
* @Version 1.0
|
**/
|
@Service
|
public class LWhGoodsStatisticsServiceImpl extends BaseServiceImpl {
|
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";
|
|
|
public List getWhGoodsStatisList(LWhGoodsStatisQry param) {
|
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);
|
|
return select(sql.toString(), paramts, new MapperUtil());
|
}
|
}
|
