package com.ishop.merchant.service;
|
|
import com.ishop.merchant.Constants;
|
import com.ishop.merchant.ProductConstants;
|
import com.ishop.merchant.pojo.ProductParam;
|
import com.ishop.model.po.EbMerchantInfo;
|
import com.ishop.model.po.EbProduct;
|
import com.ishop.model.po.EbProductAttr;
|
import com.ishop.model.po.EbProductAttrValue;
|
import com.ishop.model.po.EbProductCoupon;
|
import com.ishop.model.po.EbProductDescription;
|
import com.ishop.model.vo.ProductTabsHeaderVo;
|
import com.walker.db.page.GenericPager;
|
import com.walker.infrastructure.utils.StringUtils;
|
import com.walker.infrastructure.utils.UrlUtils;
|
import com.walker.jdbc.service.BaseServiceImpl;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
@Service
|
public class ProductServiceImpl extends BaseServiceImpl {
|
|
private ProductDescriptionServiceImpl productDescriptionService;
|
private ProductRelationServiceImpl productRelationService;
|
private ProductAttrServiceImpl productAttrService;
|
private ProductAttrValueServiceImpl productAttrValueService;
|
|
@Autowired
|
public ProductServiceImpl(ProductDescriptionServiceImpl productDescriptionService
|
, ProductRelationServiceImpl productRelationService
|
, ProductAttrServiceImpl productAttrService, ProductAttrValueServiceImpl productAttrValueService){
|
this.productDescriptionService = productDescriptionService;
|
this.productRelationService = productRelationService;
|
this.productAttrService = productAttrService;
|
this.productAttrValueService = productAttrValueService;
|
}
|
|
/**
|
* 判断给定'平台分类'下面是否存在关联商品
|
* @param platformCategoryId
|
* @return 存在返回true，不存在返回false
|
* @date 2023-07-31
|
*/
|
public boolean queryExistCategoryProductRef(int platformCategoryId){
|
int total = this.sqlMathQuery("select count(id) total from eb_product where category_id=?", new Object[]{platformCategoryId}, Integer.class);
|
return total > 0;
|
}
|
|
/**
|
* 分页查询商户商品列表，移动端使用
|
* @param merId 商户ID
|
* @param keyword 关键词
|
* @param cid 分类
|
* @param maxPrice 最大价格
|
* @param minPrice 最小价格
|
* @param salesOrder 销量排序
|
* @param priceOrder 价格排序
|
* @return
|
* @date 2023-07-05
|
*/
|
public GenericPager<EbProduct> queryPageMerchantProductH5List(int merId
|
, String keyword, Integer cid, Double maxPrice, Double minPrice, String salesOrder, String priceOrder){
|
Map<String, Object> param = new HashMap<>(4);
|
StringBuilder sql = new StringBuilder(SQL_PRODUCT_H5_LIST);
|
sql.append(" where mer_id=:merId and is_del=0 and is_recycle=0 and is_show=1 and (audit_status=2 or audit_status=0)");
|
param.put("merId", merId);
|
if(StringUtils.isNotEmpty(keyword)){
|
sql.append(" and (name like :keyword or keyword like :keyword)");
|
param.put("keyword", StringUtils.CHAR_PERCENT + keyword + StringUtils.CHAR_PERCENT);
|
}
|
if(cid != null && cid.intValue() > 0){
|
|
}
|
if(maxPrice != null){
|
sql.append(" and price<=:maxPrice");
|
param.put("maxPrice", maxPrice);
|
}
|
if(minPrice != null){
|
sql.append(" and price>=:minPrice");
|
param.put("minPrice", minPrice);
|
}
|
if(StringUtils.isNotEmpty(salesOrder)){
|
if(salesOrder.equals(Constants.SORT_ASC)){
|
sql.append(" order by sales asc, sort desc");
|
} else {
|
sql.append(" order by sales desc, sort desc");
|
}
|
} else if(StringUtils.isNotEmpty(priceOrder)){
|
if(priceOrder.equals(Constants.SORT_ASC)){
|
sql.append(" order by price asc, sort desc");
|
} else {
|
sql.append(" order by price desc, sort desc");
|
}
|
}
|
return this.selectSplit(sql.toString(), param, new EbProduct());
|
}
|
private static final String SQL_PRODUCT_H5_LIST = "select id, mer_id,image,name,price,ot_price,stock,sales,intro,ficti,browse,unit_name,brand_id,category_id from eb_product";
|
|
/**
|
* 商品下架
|
* @param productId
|
* @date 2023-08-02
|
*/
|
public void execProductDown(long productId){
|
// 1:购物车中商品状态更新：不可用
|
this.execute("update eb_cart set status=0 where product_id=? and status=1", new Object[]{productId});
|
// 2:用户收藏表，删除商品
|
this.productRelationService.execDeleteProduct(productId);
|
// 3:更新商品状态
|
this.execute("update eb_product set is_show=0 where id=?", new Object[]{productId});
|
}
|
|
/**
|
* 商品上架
|
* @param productId 商品id
|
* @param skuIdList 购物车中更新（sku）状态为可用
|
* @date 2023-08-02
|
*/
|
public void execProductUp(long productId, List<Integer> skuIdList){
|
if(skuIdList != null){
|
Map<String, Object> parameter = new HashMap<>(2);
|
parameter.put("status", 1);
|
parameter.put("skuIds", skuIdList);
|
this.execute(SQL_CART_STATUS, parameter);
|
}
|
this.execute("update eb_product set is_show=1 where id=?", new Object[]{productId});
|
}
|
private static final String SQL_CART_STATUS = "update eb_cart set status=:status where product_attr_unique in (:skuIds)";
|
|
/**
|
* 操作库存
|
* @param id 商品ID
|
* @param num 数量
|
* @param type 类型：add—添加，sub—扣减
|
* @return
|
*/
|
public int execOperateStock(long id, Integer num, String type){
|
Map<String, Object> param = new HashMap<>(4);
|
StringBuilder sql = new StringBuilder("update eb_product");
|
if(type.equals(Constants.OPERATION_TYPE_QUICK_ADD)){
|
sql.append(" set stock=stock+:num");
|
param.put("num", num);
|
} else if(type.equals(Constants.OPERATION_TYPE_ADD)){
|
sql.append(" set stock=stock+:num");
|
sql.append(",sales=sales-:num");
|
param.put("num", num);
|
} else if(type.equals(Constants.OPERATION_TYPE_SUBTRACT)){
|
sql.append(" set stock=stock-:num");
|
sql.append(",sales=sales+:num");
|
param.put("num", num);
|
} else {
|
throw new UnsupportedOperationException("不支持的库存操作类型：" + type);
|
}
|
sql.append(" where id=:id");
|
param.put("id", id);
|
return this.execute(sql.toString(), param);
|
}
|
|
/**
|
* 更新商品信息。
|
* @param product 商品
|
* @param addAttrList 添加的规格
|
* @param updateAttrList 更新的规格
|
* @param addAttrValueList 添加的自定义属性值
|
* @param updateAttrValueList 更新的自定义属性值
|
* @param spd 描述信息
|
* @param couponList 优惠券集合
|
* @date 2023-08-02
|
*/
|
public void execUpdateProduct(EbProduct product
|
, List<EbProductAttr> addAttrList, List<EbProductAttr> updateAttrList
|
, List<EbProductAttrValue> addAttrValueList, List<EbProductAttrValue> updateAttrValueList
|
, EbProductDescription spd, List<EbProductCoupon> couponList){
|
|
// attr
|
this.productAttrService.execDeleteByProductIdAndType(product.getId(), ProductConstants.PRODUCT_TYPE_NORMAL);
|
if(!StringUtils.isEmptyList(addAttrList)){
|
int attrId = this.queryAttrNextId();
|
for(EbProductAttr attr: addAttrList){
|
attr.setId(attrId++);
|
}
|
this.insertBatch(addAttrList);
|
}
|
if(!StringUtils.isEmptyList(updateAttrList)){
|
this.updateBatch(updateAttrList);
|
}
|
|
// attr value
|
this.productAttrValueService.execDeleteByProductIdAndType(product.getId(), ProductConstants.PRODUCT_TYPE_NORMAL);
|
if(!StringUtils.isEmptyList(addAttrValueList)){
|
int attrValueId = this.queryAttrValueNextId();
|
for(EbProductAttrValue attrValue: addAttrValueList){
|
attrValue.setId(attrValueId++);
|
}
|
this.insertBatch(addAttrValueList);
|
}
|
if(!StringUtils.isEmptyList(updateAttrValueList)){
|
this.updateBatch(updateAttrValueList);
|
}
|
|
if(spd != null){
|
this.deleteProductDescription(product.getId(), ProductConstants.PRODUCT_TYPE_NORMAL);
|
this.insert(spd);
|
}
|
|
// 先删除商品关联优惠券，如果存在添加的在加入。
|
this.execDeleteProductCouponList(product.getId());
|
if(!StringUtils.isEmptyList(couponList)){
|
this.insertBatch(couponList);
|
}
|
this.save(product);
|
}
|
|
/**
|
* 根据商品ID，删除关联的商品优惠券集合
|
* @param productId
|
* @date 2023-08-02
|
*/
|
public void execDeleteProductCouponList(long productId){
|
this.execute("delete from eb_product_coupon where product_id=?", new Object[]{productId});
|
}
|
|
/**
|
* 商户新增一个商品信息。
|
* @param product
|
* @param productAttrList
|
* @param productAttrValueList
|
* @param spd
|
* @param couponList
|
*/
|
public void execInsertProduct(EbProduct product, List<EbProductAttr> productAttrList
|
, List<EbProductAttrValue> productAttrValueList, EbProductDescription spd, List<EbProductCoupon> couponList){
|
if(!StringUtils.isEmptyList(productAttrList)){
|
int attrId = this.queryAttrNextId();
|
for(EbProductAttr attr: productAttrList){
|
attr.setId(attrId++);
|
}
|
this.insertBatch(productAttrList);
|
}
|
if(!StringUtils.isEmptyList(productAttrValueList)){
|
int attrValueId = this.queryAttrValueNextId();
|
for(EbProductAttrValue attrValue: productAttrValueList){
|
attrValue.setId(attrValueId++);
|
}
|
this.insertBatch(productAttrValueList);
|
}
|
if(spd != null){
|
this.deleteProductDescription(product.getId(), ProductConstants.PRODUCT_TYPE_NORMAL);
|
this.insert(spd);
|
}
|
if(!StringUtils.isEmptyList(couponList)){
|
this.insertBatch(couponList);
|
}
|
this.insert(product);
|
}
|
|
private void deleteProductDescription(long productId, int type){
|
this.execute("delete from eb_product_description where product_id=? and type=?", new Object[]{productId, type});
|
}
|
|
/**
|
* 查询商家推荐的商品。
|
* @param merId
|
* @param limit 限制条数
|
* @return
|
* @date 2023-07-04
|
*/
|
public List queryRecommendedProductsByMerId(int merId, int limit){
|
return this.select(SQL_PRODUCT_RECOMMEND, new Object[]{merId,limit}, new EbProduct());
|
}
|
private static final String SQL_PRODUCT_RECOMMEND = "select id,mer_id,image,name,price,sales,ficti from eb_product where mer_id=? and is_del=0 and is_recycle=0 and is_show=1 and (audit_status=2 or audit_status=0) limit ?";
|
|
/**
|
* 返回H5移动端商品详情。
|
* @param id 商品ID
|
* @return
|
* @date 2023-07-04
|
*/
|
public EbProduct queryH5Detail(long id){
|
EbProduct product = this.get(SQL_PRODUCT_DETAIL_H5, new Object[]{id}, new EbProduct());
|
EbProductDescription description = this.productDescriptionService.queryProductDescription(ProductConstants.PRODUCT_TYPE_NORMAL, id);
|
if(description != null){
|
product.setParameterString("content", StringUtils.isNotEmpty(description.getDescription())? description.getDescription():StringUtils.EMPTY_STRING);
|
}
|
return product;
|
}
|
|
private static final String SQL_PRODUCT_DETAIL_H5 = "select id, mer_id,image,name,slider_image,ot_price,stock,sales,price,intro,ficti,browse,unit_name,guarantee_ids,brand_id,category_id from eb_product where id=?";
|
|
/**
|
* 商户端，商品管理列表。
|
* @param productParam
|
* @return
|
* @date 2023-06-15
|
*/
|
public GenericPager<EbProduct> queryPageMerchantProductList(ProductParam productParam){
|
Map<String, Object> parameters = new HashMap<>(4);
|
StringBuilder sql = new StringBuilder("select * from eb_product where mer_id=:merId");
|
parameters.put("merId", productParam.getMerId());
|
this.searchConditionByType(sql, productParam.getType(), productParam.getMerId());
|
// 2023-07-31 平台商品分类
|
if(productParam.getCategoryId() != null && productParam.getCategoryId() > 0){
|
sql.append(" and category_id=:categoryId");
|
parameters.put("categoryId", productParam.getCategoryId());
|
}
|
if(StringUtils.isNotEmpty(productParam.getKeywords())){
|
sql.append(" and (keyword like :keywords or name like :keywords)");
|
parameters.put("keywords", StringUtils.CHAR_PERCENT + UrlUtils.decode(productParam.getKeywords()) + StringUtils.CHAR_PERCENT);
|
}
|
if(StringUtils.isNotEmpty(productParam.getCateId())){
|
sql.append(" and cate_id like :cateId");
|
parameters.put("cateId", StringUtils.CHAR_PERCENT + productParam.getCateId() + StringUtils.CHAR_PERCENT);
|
}
|
|
sql.append(" order by create_time desc");
|
return this.selectSplit(sql.toString(), parameters, new EbProduct());
|
}
|
|
private void searchConditionByType(StringBuilder sql, Integer type, Integer merId){
|
if(type.intValue() == ProductConstants.PRODUCT_STATUS_UP){
|
sql.append(" and is_show=1 and is_recycle=0 and is_del=0");
|
sql.append(" and audit_status in (2,0)");
|
} else if(type.intValue() == ProductConstants.PRODUCT_STATUS_DOWN){
|
//仓库中（未上架）
|
sql.append(" and is_show=0 and is_recycle=0 and is_del=0 and is_audit=0");
|
sql.append(" and audit_status in (1,2,0)");
|
} else if(type.intValue() == ProductConstants.PRODUCT_STATUS_OVER){
|
//已售罄
|
sql.append(" and stock<=0 and is_recycle=0 and is_del=0");
|
sql.append(" and audit_status in (2,0)");
|
} else if(type.intValue() == ProductConstants.PRODUCT_STATUS_WARN){
|
//警戒库存
|
EbMerchantInfo merchantInfo = new EbMerchantInfo();
|
merchantInfo.setMerId(merId);
|
merchantInfo = this.get(merchantInfo);
|
Integer alertStock = 0;
|
if(merchantInfo != null){
|
alertStock = merchantInfo.getAlertStock();
|
}
|
sql.append(" and stock=").append(alertStock==null? 0 : alertStock);
|
sql.append(" and is_recycle=0 and is_del=0");
|
sql.append(" and audit_status in (2,0)");
|
} else if(type.intValue() == ProductConstants.PRODUCT_STATUS_CYCLE){
|
//回收站
|
sql.append(" and is_recycle=1 and is_del=0");
|
} else if(type.intValue() == ProductConstants.PRODUCT_STATUS_WAIT_AUDIT){
|
//待审核
|
sql.append(" and audit_status=1 and is_audit=1 and is_recycle=0 and is_del=0");
|
} else if(type.intValue() == ProductConstants.PRODUCT_STATUS_AUDIT_FAILED){
|
//审核失败
|
sql.append(" and audit_status=3 and is_audit=0 and is_recycle=0 and is_del=0");
|
}
|
}
|
|
/**
|
* 平台商品分类统计，暂时先不实现。
|
* @return
|
* @date 2023-06-11
|
*/
|
public List<ProductTabsHeaderVo> queryTabsHeader() {
|
List<ProductTabsHeaderVo> headers = new ArrayList<>();
|
ProductTabsHeaderVo header1 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_UP);
|
ProductTabsHeaderVo header2 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_DOWN);
|
ProductTabsHeaderVo header3 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_OVER);
|
ProductTabsHeaderVo header4 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_WARN);
|
ProductTabsHeaderVo header5 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_CYCLE);
|
ProductTabsHeaderVo header6 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_WAIT_AUDIT);
|
ProductTabsHeaderVo header7 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_AUDIT_FAILED);
|
headers.add(header1);
|
headers.add(header2);
|
headers.add(header3);
|
headers.add(header4);
|
headers.add(header5);
|
headers.add(header6);
|
headers.add(header7);
|
return headers;
|
}
|
|
/**
|
* 平台分页查询商品信息列表
|
* @param productParam
|
* @return
|
* @date 2023-06-11
|
*/
|
public GenericPager<EbProduct> queryPageProductList(ProductParam productParam){
|
StringBuilder sql = new StringBuilder(SQL_PAGE_LIST);
|
if(productParam == null){
|
sql.append(SQL_PAGE_ORDER);
|
return this.selectSplit(sql.toString(), new Object[]{}, new EbProduct());
|
}
|
|
Map<String, Object> parameters = new HashMap<>(4);
|
|
int type = productParam.getType();
|
if(type == ProductConstants.PRODUCT_STATUS_UP){
|
sql.append(" and p.is_show = 1");
|
sql.append(" and (p.audit_status=0 or p.audit_status=2)");
|
} else if(type == ProductConstants.PRODUCT_STATUS_DOWN){
|
sql.append(" and p.is_show = 0");
|
sql.append(" and p.is_audit = 0");
|
sql.append(" and p.audit_status in (0,1,2)");
|
} else if(type == ProductConstants.PRODUCT_STATUS_WAIT_AUDIT){
|
sql.append(" and p.audit_status = 1");
|
sql.append(" and p.is_audit = 1");
|
} else if (type == ProductConstants.PRODUCT_STATUS_AUDIT_FAILED) {
|
sql.append(" and p.audit_status = 3");
|
sql.append(" and p.is_audit = 0");
|
}
|
|
if(productParam.getMerId() != null && productParam.getMerId().intValue() > 0){
|
sql.append(" and p.mer_id=:merId");
|
parameters.put("merId", productParam.getMerId());
|
}
|
if(productParam.getIsSelf() != null){
|
sql.append(" and m.is_self=:isSelf");
|
parameters.put("isSelf", productParam.getIsSelf());
|
}
|
if(productParam.getCategoryId() != null){
|
sql.append(" and p.category_id=:categoryId");
|
parameters.put("categoryId", productParam.getCategoryId());
|
}
|
if(StringUtils.isNotEmpty(productParam.getKeywords())){
|
sql.append(" and (p.name like :keywords or p.keyword like :keywords)");
|
parameters.put("keywords", StringUtils.CHAR_PERCENT + productParam.getKeywords() + StringUtils.CHAR_PERCENT);
|
}
|
sql.append(SQL_PAGE_ORDER);
|
return this.selectSplit(sql.toString(), parameters, new EbProduct());
|
}
|
|
public int queryAttrNextId(){
|
int maxId = this.queryForInt(ATTR_NEXT_ID, new Object[]{});
|
return maxId+1;
|
}
|
public int queryAttrValueNextId(){
|
int maxId = this.queryForInt(ATTR_VALUE_NEXT_ID, new Object[]{});
|
return maxId+1;
|
}
|
|
private static final String ATTR_NEXT_ID = "select max(id) from eb_product_attr";
|
private static final String ATTR_VALUE_NEXT_ID = "select max(id) from eb_product_attr_value";
|
private static final String SQL_PAGE_ORDER = " ORDER BY p.rank desc, p.id desc";
|
private static final String SQL_PAGE_LIST = "SELECT p.id,p.mer_id,p.image,p.name,p.keyword,p.category_id,p.price,p.sales" +
|
",p.stock,p.ficti,p.audit_status,p.reason,p.rank FROM eb_product p right join eb_merchant m on p.mer_id = m.id " +
|
"where p.is_del = 0 and p.is_recycle = 0";
|
}
|
