package com.yc.sdk.shopping.service;
|
|
import com.google.gson.Gson;
|
import com.google.gson.reflect.TypeToken;
|
import com.yc.sdk.shopping.entity.*;
|
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;
|
import com.yc.sdk.shopping.util.SettingKey;
|
import com.yc.service.BaseService;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.context.annotation.Scope;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.dao.EmptyResultDataAccessException;
|
import org.springframework.stereotype.Service;
|
|
import javax.servlet.http.HttpServletRequest;
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Map.Entry;
|
|
@Service("CartImpl")
|
@Scope("prototype")
|
public class CartImpl extends BaseService implements CartIfc {
|
static Gson gson=new Gson();
|
@Autowired
|
MatPriceIfc matPriceIfc ;
|
@Autowired
|
SettingIfc settingIfc;
|
@Autowired
|
CurrencyIfc currencyIfc;
|
@Autowired ShoppingImageDataIfc shoppingImageDataIfc;
|
|
@Override
|
public Integer addToCart(CartEntity cart) {
|
String sql = " set nocount on ; \n"
|
+ " declare @UserCode varchar(20) = ?,@UserName varchar(50) = ? ,@SessionId varchar(200) = ?,@OpenId varchar(200) = ?; \n"
|
+ " declare @CltCode varchar(50) = ?,@CltName varchar(100) = ? ; \n"
|
+ " declare @MatCode varchar(50) = ?,@MatName varchar(200),@Special varchar(200); \n "
|
+ " declare @PhotoPath varchar(200) ,@WeightUom varchar(50) ;\n"
|
+ " declare @RecurringId int = ? ,@OptionJson varchar(2000) = ?,@OptionText varchar(2000) = ?;\n"
|
+ " declare @Quantity money = ?,@Price money = ?,@manualPrice money=? \n" +
|
" declare @Amount money = round(isnull(@Quantity,0) * isnull(@manualPrice,0),2),@VoucherDocCode varchar(50) = ?,@CartId int ; \n"
|
+ " declare @found int = 0 ;"
|
+ " declare @refDocCode varchar(50) = ?,@refFormId int = ?,@refFormType int = ?,@refRowId varchar(50) = ? \n"
|
+ " declare @isGoShop int = ? \n"
|
+ " declare @skuId1 int = ?,@skuId2 int = ?,@skuId3 int = ?,@skuId4 int = ?, \n"
|
+ " @skuId5 int = ?,@skuId6 int = ?,@skuId7 int = ?,@skuId8 int = ?,@skuId9 int = ?,@skuId10 int = ? \n"
|
+ " declare @skuName1 varchar(50),@skuName2 varchar(50),@skuName3 varchar(50),@skuName4 varchar(50),@skuName5 varchar(50), \n"
|
+ " @skuName6 varchar(50),@skuName7 varchar(50),@skuName8 varchar(50),@skuName9 varchar(50),@skuName10 varchar(50) \n"
|
|
+ " select @CartId = CartId from t710205 a \n"
|
+ " where isnull(a.UserCode,'') = isnull(@UserCode,'') \n"
|
+ " and isnull(a.SessionId,'') = isnull(@SessionId,'')\n"
|
+ " and isnull(a.CltCode,'') = isnull(@CltCode,'') \n"
|
+ " and isnull(a.OpenId,'') = isnull(@OpenId,'') \n"
|
+ " and isnull(a.MatCode,'') = isnull(@MatCode,'') \n"
|
+ " and ISNULL(a.OptionJson,'') = ISNULL(@OptionJson,'') \n"
|
+ " and ISNULL(a.refDocCode,'') = ISNULL(@refDocCode,'') \n"
|
+ " and isnull(a.skuId1,0) = isnull(@skuId1,0) and isnull(a.skuId2,0) = isnull(@skuId2,0) and isnull(a.skuId3,0) = isnull(@skuId3,0) and isnull(a.skuId4,0) = isnull(@skuId4,0) and isnull(a.skuId5,0) = isnull(@skuId5,0) \n"
|
+ " and isnull(a.skuId6,0) = isnull(@skuId6,0) and isnull(a.skuId7,0) = isnull(@skuId7,0) and isnull(a.skuId8,0) = isnull(@skuId8,0) and isnull(a.skuId9,0) = isnull(@skuId9,0) and isnull(a.skuId10,0) = isnull(@skuId10,0) ; \n"
|
+ " if isnull(@@rowcount,0) > 0 select @found = 1 \n"
|
+ " if isnull(@found,0) = 0 \n"
|
+ " begin \n"
|
+ " select @CartId = CartId from t710205 a \n"
|
+ " where isnull(@UserCode,'') <> '' and isnull(a.UserCode,'') = isnull(@UserCode,'') \n"
|
+ " and isnull(a.MatCode,'') = isnull(@MatCode,'') \n"
|
+ " and ISNULL(a.OptionJson,'') = ISNULL(@OptionJson,'') \n"
|
+ " and ISNULL(a.refDocCode,'') = ISNULL(@refDocCode,'') \n"
|
+ " and isnull(a.skuId1,0) = isnull(@skuId1,0) and isnull(a.skuId2,0) = isnull(@skuId2,0) and isnull(a.skuId3,0) = isnull(@skuId3,0) and isnull(a.skuId4,0) = isnull(@skuId4,0) and isnull(a.skuId5,0) = isnull(@skuId5,0) \n"
|
+ " and isnull(a.skuId6,0) = isnull(@skuId6,0) and isnull(a.skuId7,0) = isnull(@skuId7,0) and isnull(a.skuId8,0) = isnull(@skuId8,0) and isnull(a.skuId9,0) = isnull(@skuId9,0) and isnull(a.skuId10,0) = isnull(@skuId10,0) ; \n"
|
+ " end \n"
|
|
+ " if isnull(@@rowcount,0) > 0 select @found = 1 \n"
|
|
+ " if isnull(@found,0) = 0 \n"
|
+ " begin \n"
|
+ " select @CartId = CartId from t710205 a \n"
|
+ " where isnull(@SessionId,'') <> '' and isnull(a.SessionId,'') = isnull(@SessionId,'') \n"
|
+ " and isnull(a.MatCode,'') = isnull(@MatCode,'') \n"
|
+ " and ISNULL(a.refDocCode,'') = ISNULL(@refDocCode,'') \n"
|
+ " and ISNULL(a.OptionJson,'') = ISNULL(@OptionJson,'') \n"
|
+ " and isnull(a.skuId1,0) = isnull(@skuId1,0) and isnull(a.skuId2,0) = isnull(@skuId2,0) and isnull(a.skuId3,0) = isnull(@skuId3,0) and isnull(a.skuId4,0) = isnull(@skuId4,0) and isnull(a.skuId5,0) = isnull(@skuId5,0) \n"
|
+ " and isnull(a.skuId6,0) = isnull(@skuId6,0) and isnull(a.skuId7,0) = isnull(@skuId7,0) and isnull(a.skuId8,0) = isnull(@skuId8,0) and isnull(a.skuId9,0) = isnull(@skuId9,0) and isnull(a.skuId10,0) = isnull(@skuId10,0) ; \n"
|
+ " if isnull(@@rowcount,0) > 0 select @found = 1 \n"
|
+ " end \n"
|
|
+ " if isnull(@found,0) = 0 \n"
|
+ " begin \n"
|
+ " select @CartId = CartId from t710205 a \n"
|
+ " where isnull(@CltCode,'') <> '' and isnull(a.CltCode,'') = isnull(@CltCode,'') \n"
|
+ " and isnull(a.MatCode,'') = isnull(@MatCode,'') \n"
|
+ " and ISNULL(a.refDocCode,'') = ISNULL(@refDocCode,'') \n"
|
+ " and ISNULL(a.OptionJson,'') = ISNULL(@OptionJson,'') \n"
|
+ " and isnull(a.skuId1,0) = isnull(@skuId1,0) and isnull(a.skuId2,0) = isnull(@skuId2,0) and isnull(a.skuId3,0) = isnull(@skuId3,0) and isnull(a.skuId4,0) = isnull(@skuId4,0) and isnull(a.skuId5,0) = isnull(@skuId5,0) \n"
|
+ " and isnull(a.skuId6,0) = isnull(@skuId6,0) and isnull(a.skuId7,0) = isnull(@skuId7,0) and isnull(a.skuId8,0) = isnull(@skuId8,0) and isnull(a.skuId9,0) = isnull(@skuId9,0) and isnull(a.skuId10,0) = isnull(@skuId10,0) ; \n"
|
|
+ " if isnull(@@rowcount,0) > 0 select @found = 1 \n"
|
+ " end \n"
|
|
+ " if isnull(@found,0) = 0 \n"
|
+ " begin \n"
|
+ " select @CartId = CartId from t710205 a \n"
|
+ " where isnull(@OpenId,'') <> '' and isnull(a.OpenId,'') = isnull(@OpenId,'') \n"
|
+ " and isnull(a.MatCode,'') = isnull(@MatCode,'') \n"
|
+ " and ISNULL(a.refDocCode,'') = ISNULL(@refDocCode,'') \n"
|
+ " and ISNULL(a.OptionJson,'') = ISNULL(@OptionJson,'') \n"
|
+ " and isnull(a.skuId1,0) = isnull(@skuId1,0) and isnull(a.skuId2,0) = isnull(@skuId2,0) and isnull(a.skuId3,0) = isnull(@skuId3,0) and isnull(a.skuId4,0) = isnull(@skuId4,0) and isnull(a.skuId5,0) = isnull(@skuId5,0) \n"
|
+ " and isnull(a.skuId6,0) = isnull(@skuId6,0) and isnull(a.skuId7,0) = isnull(@skuId7,0) and isnull(a.skuId8,0) = isnull(@skuId8,0) and isnull(a.skuId9,0) = isnull(@skuId9,0) and isnull(a.skuId10,0) = isnull(@skuId10,0) ; \n"
|
|
+ " if isnull(@@rowcount,0) > 0 select @found = 1 \n"
|
+ " end \n"
|
|
+ " if isnull(@found,0) = 1 \n"
|
+ " begin \n"
|
+ " update a set Quantity = case when isnull(@isGoShop,0) = 0 then isnull(a.Quantity,0) + isnull(@Quantity,0) else isnull(@Quantity,0) end,isSelected=1 \n"
|
+ " from t710205 a where CartId = @CartId ; \n "
|
//+ " select @CartId as 'Identity' \n"
|
+ " end \n"
|
+ " else begin \n"
|
+ " select @MatName = MatName ,@Special = Special,@PhotoPath=PhotoPath,@WeightUom = WeightUom \n"
|
+ " from t110503 where MatCode = @MatCode; \n "
|
+ " if @@rowcount = 0 begin \n"
|
+ " raiserror('商品编号【%s】不存在,请在110503功能号中设置该商品信息',16,1,@MatCode) ; \n"
|
+ " return ; \n"
|
+ " end \n"
|
+ " if isnull(@WeightUom,'') = '' select @WeightUom = WeightClassId from t714001 ; \n"
|
|
+ " select top 1 @skuName1=a.skuName1,@skuName2=a.skuName2,@skuName3=a.skuName3,@skuName4=a.skuName4,@skuName5=a.skuName5, \n"
|
+ " @skuName6=a.skuName6,@skuName7=a.skuName7,@skuName8=a.skuName8,@skuName9=a.skuName9,@skuName10=a.skuName10 \n"
|
+ " from t710175D a \n"
|
+ " join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where a.MatCode = isnull(@MatCode,'') and isnull(a.skuId1,0) = isnull(@skuId1,0) and isnull(a.skuId2,0) = isnull(@skuId2,0) and isnull(a.skuId3,0) = isnull(@skuId3,0) and isnull(a.skuId4,0) = isnull(@skuId4,0) and isnull(a.skuId5,0) = isnull(@skuId5,0) \n"
|
+ " and isnull(a.skuId6,0) = isnull(@skuId6,0) and isnull(a.skuId7,0) = isnull(@skuId7,0) and isnull(a.skuId8,0) = isnull(@skuId8,0) and isnull(a.skuId9,0) = isnull(@skuId9,0) and isnull(a.skuId10,0) = isnull(@skuId10,0) \n"
|
|
+ " insert into t710205 (UserCode,UserName,SessionId,OpenId,CltCode,CltName, \n"
|
+ " MatCode,MatName,Special,PhotoPath,RecurringId, \n"
|
+ " OptionJson,OptionText,Quantity,Price,manualPrice,Amount,WeightUom,DateAdded,VoucherDocCode,\n"
|
+ " refDocCode,refFormId,refFormType,refRowId,isSelected,MatGroupStatus,\n"
|
+ " skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5,\n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10) \n"
|
+ " values(isnull(@UserCode,''),isnull(@UserName,''),isnull(@SessionId,''),isnull(@OpenId,''), \n"
|
+ " isnull(@CltCode,''),isnull(@CltName,''), isnull(@MatCode,''),isnull(@MatName,''), \n"
|
+ " @Special,@PhotoPath,@RecurringId, isnull(@OptionJson,''),isnull(@OptionText,''),@Quantity,@Price,@manualPrice,@Amount, \n"
|
+ " @WeightUom,getdate(),isnull(@VoucherDocCode,''),isnull(@refDocCode,''),@refFormId,@refFormType,@refRowId,1,1,"
|
+ " @skuId1,@skuName1,@skuId2,@skuName2,@skuId3,@skuName3,@skuId4,@skuName4,@skuId5,@skuName5,"
|
+ " @skuId6,@skuName6,@skuId7,@skuName7,@skuId8,@skuName8,@skuId9,@skuName9,@skuId10,@skuName10) ; \n"
|
+ " SELECT @CartId = @@IDENTITY ; \n"
|
+ " end \n"
|
+ " exec p710205v8 @CartId = @CartId \n" //刷新购物车中的价格、积分和重量
|
+ " select @CartId as 'Identity' \n";
|
Integer cartId = null ;
|
try{
|
cartId = this.jdbcTemplate.queryForObject(sql, Integer.class,
|
new Object[]{cart.getUserCode(), cart.getUserName(), cart.getSessionId(), cart.getOpenId(),
|
cart.getCltCode(), cart.getCltName(),
|
cart.getMatCode(), cart.getRecurringId(),
|
cart.getOptionJson(), cart.getOptionText(), cart.getQuantity(),
|
cart.getPrice(), cart.getManualPrice(),
|
cart.getVoucherDocCode(), cart.getRefDocCode(),
|
cart.getRefFormId(), cart.getRefFormType(), cart.getRefRowId(), cart.isGoShop() ? 1 : 0,
|
cart.getSkuId1(), cart.getSkuId2(), cart.getSkuId3(), cart.getSkuId4(), cart.getSkuId5(),
|
cart.getSkuId6(), cart.getSkuId7(), cart.getSkuId8(), cart.getSkuId9(), cart.getSkuId10()});
|
// if (cart.isRefreshPrice()) {
|
// //刷新购物车中的价格、积分和重量
|
// this.refrshCart(cartId,cart.getCltCode());
|
// }
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return cartId ;
|
}
|
|
|
@Override
|
public Integer saveCartByPicture(CartEntity cart) {
|
String sql = " set nocount on ; \n"
|
+ " declare @CltCode varchar(50) = ?,@CltName varchar(100) = ?; \n"
|
+ " declare @MatCode varchar(50) = ?,@MatName varchar(200),@Special varchar(200); \n "
|
+ " declare @PhotoPath varchar(200) ;\n"
|
+ " declare @Quantity money = ?,@Price money = ?,@Amount money = ?,@CartId int ; \n"
|
+ " declare @found int = 0 ;"
|
|
+ " select @CartId = CartId from t710401 a \n"
|
+ " where isnull(a.CltCode,'') = isnull(@CltCode,'') \n"
|
+ " and isnull(a.MatCode,'') = isnull(@MatCode,'') \n"
|
|
+ " if isnull(@@rowcount,0) > 0 select @found = 1 \n"
|
|
|
+ " if isnull(@found,0) = 1 begin \n"
|
+ " update a set Quantity = isnull(Quantity,0) + isnull(@Quantity,0), \n"
|
+ " Amount = round((isnull(Quantity,0) + isnull(@Quantity,0)) * isnull(Price,0),2) "
|
+ " from t710401 a where CartId = @CartId ; \n "
|
+ " select @CartId as 'Identity' \n"
|
+ " end \n"
|
+ " else begin \n"
|
+ " select @MatName = MatName ,@Special = Special,@PhotoPath=PhotoPath \n"
|
+ " from t110503 where MatCode = @MatCode; \n "
|
+ " if @@rowcount = 0 begin \n"
|
+ " raiserror('商品编号【%s】不存在,请在110503功能号中设置该商品信息',16,1,@MatCode) ; \n"
|
+ " return ; \n"
|
+ " end \n"
|
|
+ " insert into t710401 (CltCode,CltName, \n"
|
+ " MatCode,MatName,Special,PhotoPath,\n"
|
+ " Quantity,Price,Amount,DateAdded) \n"
|
+ " values(isnull(@CltCode,''),isnull(@CltName,''), isnull(@MatCode,''),isnull(@MatName,''), \n"
|
+ " @Special,@PhotoPath,@Quantity,@Price,round(isnull(@Quantity,0) * isnull(@Price,0),2) , \n"
|
+ " getdate()) ; \n"
|
+ " SELECT @@IDENTITY AS 'Identity' ; \n"
|
+ " end \n";
|
Integer cartId = null ;
|
try{
|
cartId = this.jdbcTemplate.queryForObject(sql, Integer.class,
|
new Object[] {cart.getCltCode(),cart.getCltName(),
|
cart.getMatCode(),cart.getQuantity(),
|
cart.getPrice(),cart.getAmount()}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return cartId ;
|
}
|
|
@Deprecated
|
@Override
|
public void refrshCart(Integer cartId,String cltCode) {
|
CartEntity myCart = this.getCart(cartId);
|
if (myCart == null) return ;
|
|
Double price = null;
|
double points = 0,weight = 0 ;
|
//取价格
|
MatPriceEntity matPriceEntity = matPriceIfc.getOrderPrice(myCart.getMatCode(), cltCode, myCart.getDateAdded(), myCart.getQuantity());
|
if (matPriceEntity != null) {
|
price = matPriceEntity.getPrice();
|
points = matPriceEntity.getPoints();
|
weight = matPriceEntity.getWeight();
|
}
|
//取出选项附加的价格
|
MatOptionPriceEntity matOptionPrice = matPriceIfc.getOptionPriceAndWeight(cartId);
|
if (matOptionPrice != null) {
|
if (matOptionPrice.getPrice()!=null) {
|
if (price != null) {
|
price += matOptionPrice.getPrice();
|
}else {
|
price = matOptionPrice.getPrice();
|
}
|
}
|
points += matOptionPrice.getPoints();
|
weight += matOptionPrice.getWeight();
|
}
|
|
//四舍五入后汇总
|
Double amount = null ;
|
if (price!=null) {
|
BigDecimal a = new BigDecimal(myCart.getQuantity() * price);
|
amount = a.setScale(2,RoundingMode.HALF_UP).doubleValue();
|
}
|
|
//四舍五入后汇总
|
BigDecimal b = new BigDecimal(points*myCart.getQuantity());
|
points = b.setScale(2,RoundingMode.HALF_UP).doubleValue();
|
|
//四舍五入后汇总
|
BigDecimal c = new BigDecimal(weight*myCart.getQuantity());
|
weight = c.setScale(2,RoundingMode.HALF_UP).doubleValue();
|
|
//再次更新价格,因为有可能数量变化后,会引起价格变动
|
this.updateCartPrice(cartId, price, amount,points,weight);
|
|
}
|
|
@Override
|
public List<CartEntity> getMatCodesByGroupBuying(String matCode,Double quantity,SkuMatCodeEntity skuMatCodeEntity) {
|
if (matCode==null||"".equals(matCode)) return null ;
|
String sql = " set nocount on ; \n"
|
+ " declare @MatCode varchar(20) = ? ,@Quantity money = ? \n;"
|
+ " declare @TotalQuantity money,@TotalAmount money,@TotalWeight money \n"
|
+ " declare @MaxPreSendDateTime datetime = getdate() ,@MaxPreSendMinutes int; \n"
|
+ " declare @table table(CartId int,UserCode varchar(20),UserName varchar(50), \n"
|
+ " SessionId varchar(200),CltCode varchar(50),CltName varchar(200),\n"
|
+ " MatCode varchar(50),MatName varchar(200), \n"
|
+ " Special varchar(200),PhotoPath varchar(200),BaseUomWeight money,\n"
|
+ " RecurringId int,OptionJson varchar(200),OptionText varchar(200),\n"
|
+ " Quantity money,Price money,Amount money,DateAdded datetime,\n"
|
+ " TotalQuantity money,TotalAmount money,TotalWeight money, \n"
|
+ " ShopMatCode varchar(50),ShopCcCode varchar(50),Brand varchar(50),isSelected int,\n"
|
+ " PreSendMinutes int,PreSendMinutesDesc varchar(50), \n"
|
+ " MaxPreSendMinutes int, MaxPreSendMinutesDesc varchar(50),MaxPreSendDateTime datetime , isStartupGroupBuying int,\n"
|
+ " skuId1 int,skuName1 varchar(50),skuId2 int,skuName2 varchar(50),skuId3 int,skuName3 varchar(50),skuId4 int,skuName4 varchar(50),"
|
+ " skuId5 int,skuName5 varchar(50),skuId6 int,skuName6 varchar(50),skuId7 int,skuName7 varchar(50),skuId8 int,skuName8 varchar(50),"
|
+ " skuId9 int,skuName9 varchar(50),skuId10 int,skuName10 varchar(50)) ; \n"
|
|
+ " declare @skuId1 int = ?,@skuId2 int = ?,@skuId3 int = ?,@skuId4 int = ?, \n"
|
+ " @skuId5 int = ?,@skuId6 int = ?,@skuId7 int = ?,@skuId8 int = ?,@skuId9 int = ?,@skuId10 int = ? \n"
|
+ " declare @skuName1 varchar(50),@skuName2 varchar(50),@skuName3 varchar(50),@skuName4 varchar(50),@skuName5 varchar(50), \n"
|
+ " @skuName6 varchar(50),@skuName7 varchar(50),@skuName8 varchar(50),@skuName9 varchar(50),@skuName10 varchar(50) \n"
|
|
+ " select top 1 @skuName1=a.skuName1,@skuName2=a.skuName2,@skuName3=a.skuName3,@skuName4=a.skuName4,@skuName5=a.skuName5, \n"
|
+ " @skuName6=a.skuName6,@skuName7=a.skuName7,@skuName8=a.skuName8,@skuName9=a.skuName9,@skuName10=a.skuName10 \n"
|
+ " from t710175D a \n"
|
+ " join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where a.MatCode = isnull(@MatCode,'') and isnull(a.skuId1,0) = isnull(@skuId1,0) and isnull(a.skuId2,0) = isnull(@skuId2,0) and isnull(a.skuId3,0) = isnull(@skuId3,0) and isnull(a.skuId4,0) = isnull(@skuId4,0) and isnull(a.skuId5,0) = isnull(@skuId5,0) \n"
|
+ " and isnull(a.skuId6,0) = isnull(@skuId6,0) and isnull(a.skuId7,0) = isnull(@skuId7,0) and isnull(a.skuId8,0) = isnull(@skuId8,0) and isnull(a.skuId9,0) = isnull(@skuId9,0) and isnull(a.skuId10,0) = isnull(@skuId10,0) \n"
|
|
+ " insert into @table( CartId,UserCode,UserName,SessionId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,BaseUomWeight,RecurringId,OptionJson,OptionText,\n"
|
+ " Quantity,Price,Amount,DateAdded,ShopMatCode,ShopCcCode,Brand,isSelected,PreSendMinutes ,PreSendMinutesDesc ,isStartupGroupBuying, \n"
|
+ " skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5,\n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10) \n"
|
+ " select null as CartId,null as UserCode,null as UserName,null as SessionId,null as CltCode,null as CltName,b.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.BaseUomWeight,null as RecurringId,null as OptionJson,null as OptionText,\n "
|
+ " @Quantity as Quantity,b.GroupBuyingPrice as Price,round(isnull(@Quantity,0) * isnull(b.GroupBuyingPrice,0),2) as Amount,\n"
|
+ " getdate() as DateAdded,b.ShopMatCode,b.ShopCcCode,b.Brand,1 as isSelected, \n"
|
+ " b.PreSendMinutes,dbo.f110503v2 (b.PreSendMinutes) as PreSendMinutesDesc , b.isStartupGroupBuying,\n"
|
+ " @skuId1,@skuName1,@skuId2,@skuName2,@skuId3,@skuName3,@skuId4,@skuName4,@skuId5,@skuName5,\n"
|
+ " @skuId6,@skuName6,@skuId7,@skuName7,@skuId8,@skuName8,@skuId9,@skuName9,@skuId10,@skuName10 \n"
|
+ " from t110503 b \n"
|
+ " join t110504 c on b.Brand = c.Brand \n"
|
//+ " left join t710175D d on b.MatCode = d.MatCode \n"
|
+ " where b.MatCode = @MatCode\n"
|
+" and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and exists (select 1 from t710104 g join t110501 e on g.MatGroup = e.MatGroup \n"
|
+ " where b.DocCode = g.doccode \n"
|
+ " and isnull(e.Status,0) = 1 ) \n"
|
+ " order by b.MatName asc ; \n "
|
+ " select @TotalQuantity = SUM(isnull(Quantity,0)),@TotalAmount = SUM(isnull(Amount,0)), \n"
|
+ " @TotalWeight = sum(isnull(Quantity,0) * isnull(BaseUomWeight,0)), \n"
|
+ " @MaxPreSendMinutes = max(PreSendMinutes) \n"
|
+ " from @table \n"
|
+ " update a set TotalQuantity= @TotalQuantity,TotalAmount = @TotalAmount,TotalWeight=@TotalWeight, \n"
|
+ " MaxPreSendMinutes = @MaxPreSendMinutes,MaxPreSendMinutesDesc = dbo.f110503v2 (@MaxPreSendMinutes), \n"
|
+ " MaxPreSendDateTime = dateadd(minute, @MaxPreSendMinutes, @MaxPreSendDateTime) \n"
|
+ " from @table a \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,a.MatName, \n"
|
+ " a.Special,a.PhotoPath,a.BaseUomWeight,a.RecurringId,a.OptionJson,a.OptionText, \n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded ,a.TotalQuantity,a.TotalAmount ,a.TotalWeight, \n"
|
+ " a.ShopMatCode,a.ShopCcCode,a.Brand,a.isSelected, \n"
|
+ " a.PreSendMinutes,a.PreSendMinutesDesc,a.MaxPreSendMinutes,a.MaxPreSendMinutesDesc,a.MaxPreSendDateTime,a.isStartupGroupBuying, \n"
|
+ " a.skuId1,a.skuName1,a.skuId2,a.skuName2,a.skuId3,a.skuName3,a.skuId4,a.skuName4,a.skuId5,a.skuName5, \n"
|
+ " a.skuId6,a.skuName6,a.skuId7,a.skuName7,a.skuId8,a.skuName8,a.skuId9,a.skuName9,a.skuId10,a.skuName10 \n"
|
+ " from @table a \n";
|
|
List<Map<String,Object>> list = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql,new Object[] {matCode,quantity,
|
skuMatCodeEntity.getSkuId1(),skuMatCodeEntity.getSkuId2(),skuMatCodeEntity.getSkuId3(),skuMatCodeEntity.getSkuId4(),
|
skuMatCodeEntity.getSkuId5(),skuMatCodeEntity.getSkuId6(),skuMatCodeEntity.getSkuId7(),skuMatCodeEntity.getSkuId8(),
|
skuMatCodeEntity.getSkuId9(),skuMatCodeEntity.getSkuId10()}) ;
|
|
List<CartEntity> cartList = new ArrayList<CartEntity>() ;
|
for (int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
CartEntity cart = getCartEntity(map);
|
cartList.add(cart);
|
}
|
return cartList;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
}
|
|
@Override
|
public List<CartEntity> getCarts(String cartIds) {
|
if ( cartIds==null|| "".equals(cartIds)) return null ;
|
String sql = " set nocount on ; \n"
|
+ " declare @cartIds varchar(max) = ? \n;"
|
+ " declare @TotalQuantity money,@TotalAmount money,@TotalWeight money \n"
|
+ " declare @MaxPreSendDateTime datetime = getdate() ,@MaxPreSendMinutes int; \n"
|
+ " declare @table table(CartId int,UserCode varchar(20),UserName varchar(50), \n"
|
+ " SessionId varchar(200),CltCode varchar(50),CltName varchar(200),\n"
|
+ " MatCode varchar(50),MatName varchar(200), \n"
|
+ " Special varchar(200),PhotoPath varchar(200),BaseUomWeight money,\n"
|
+ " RecurringId int,OptionJson varchar(200),OptionText varchar(200),\n"
|
+ " Quantity money,Price money,Amount money,DateAdded datetime,\n"
|
+ " TotalQuantity money,TotalAmount money,TotalWeight money, \n"
|
+ " ShopMatCode varchar(50),ShopCcCode varchar(50),Brand varchar(50),isSelected int,\n"
|
+ " PreSendMinutes int,PreSendMinutesDesc varchar(50), \n"
|
+ " MaxPreSendMinutes int, MaxPreSendMinutesDesc varchar(50),MaxPreSendDateTime datetime,isStartupGroupBuying int,\n"
|
+ " skuId1 int,skuName1 varchar(50),skuId2 int,skuName2 varchar(50),skuId3 int,skuName3 varchar(50),skuId4 int,skuName4 varchar(50),"
|
+ " skuId5 int,skuName5 varchar(50),skuId6 int,skuName6 varchar(50),skuId7 int,skuName7 varchar(50),skuId8 int,skuName8 varchar(50),"
|
+ " skuId9 int,skuName9 varchar(50),skuId10 int,skuName10 varchar(50)) ; \n"
|
+ " insert into @table( CartId,UserCode,UserName,SessionId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,BaseUomWeight,RecurringId,OptionJson,OptionText,\n"
|
+ " Quantity,Price,Amount,DateAdded,ShopMatCode,ShopCcCode,Brand,isSelected,PreSendMinutes ,PreSendMinutesDesc ,isStartupGroupBuying,\n"
|
+ " skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5,\n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10) \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.BaseUomWeight,a.RecurringId,a.OptionJson,a.OptionText,\n "
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode,b.Brand,a.isSelected, \n"
|
+ " b.PreSendMinutes,dbo.f110503v2 (b.PreSendMinutes) as PreSendMinutesDesc ,b.isStartupGroupBuying, \n"
|
+ " a.skuId1,a.skuName1,a.skuId2,a.skuName2,a.skuId3,a.skuName3,a.skuId4,a.skuName4,a.skuId5,a.skuName5,\n"
|
+ " a.skuId6,a.skuName6,a.skuId7,a.skuName7,a.skuId8,a.skuName8,a.skuId9,a.skuName9,a.skuId10,a.skuName10 \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " join t110504 c on b.Brand = c.Brand \n"
|
//+ " left join t710175D d on a.MatCode = d.MatCode \n"
|
+ " where (isnull(@cartIds,'') <> '' and a.CartId in (select list from getinstr(@cartIds))) \n"
|
+" and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and exists (select 1 from t710104 g join t110501 e on g.MatGroup = e.MatGroup \n"
|
+ " where b.DocCode = g.doccode \n"
|
+ " and isnull(e.Status,0) = 1 ) \n"
|
+ " order by b.MatName asc ; \n "
|
+ " select @TotalQuantity = SUM(isnull(Quantity,0)),@TotalAmount = SUM(isnull(Amount,0)), \n"
|
+ " @TotalWeight = sum(isnull(Quantity,0) * isnull(BaseUomWeight,0)), \n"
|
+ " @MaxPreSendMinutes = max(PreSendMinutes) \n"
|
+ " from @table \n"
|
+ " update a set TotalQuantity= @TotalQuantity,TotalAmount = @TotalAmount,TotalWeight=@TotalWeight, \n"
|
+ " MaxPreSendMinutes = @MaxPreSendMinutes,MaxPreSendMinutesDesc = dbo.f110503v2 (@MaxPreSendMinutes), \n"
|
+ " MaxPreSendDateTime = dateadd(minute, @MaxPreSendMinutes, @MaxPreSendDateTime) \n"
|
+ " from @table a \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,a.MatName, \n"
|
+ " a.Special,a.PhotoPath,a.BaseUomWeight,a.RecurringId,a.OptionJson,a.OptionText, \n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded ,a.TotalQuantity,a.TotalAmount ,a.TotalWeight, \n"
|
+ " a.ShopMatCode,a.ShopCcCode,a.Brand,a.isSelected, \n"
|
+ " a.PreSendMinutes,a.PreSendMinutesDesc,a.MaxPreSendMinutes,a.MaxPreSendMinutesDesc,a.MaxPreSendDateTime ,a.isStartupGroupBuying,\n"
|
+ " a.skuId1,a.skuName1,a.skuId2,a.skuName2,a.skuId3,a.skuName3,a.skuId4,a.skuName4,a.skuId5,a.skuName5, \n"
|
+ " a.skuId6,a.skuName6,a.skuId7,a.skuName7,a.skuId8,a.skuName8,a.skuId9,a.skuName9,a.skuId10,a.skuName10 \n"
|
+ " from @table a \n";
|
|
List<Map<String,Object>> list = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql,cartIds) ;
|
|
List<CartEntity> cartList = new ArrayList<CartEntity>() ;
|
for (int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
CartEntity cart = getCartEntity(map);
|
cartList.add(cart);
|
}
|
return cartList;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
|
}
|
|
@Override
|
public List<CartEntity> getCartsByPicture(String cartIds,String cltCode) {
|
String sql = " set nocount on ; \n"
|
+ " declare @cartIds varchar(max) = ? , @CltCode varchar(50) = ? \n;"
|
+ " declare @TotalQuantity money,@TotalAmount money; \n"
|
+ " declare @table table(CartId int,UserCode varchar(20),UserName varchar(50), \n"
|
+ " SessionId varchar(200),CltCode varchar(50),CltName varchar(200),\n"
|
+ " MatCode varchar(50),MatName varchar(200), \n"
|
+ " Special varchar(200),PhotoPath varchar(200),BaseUomWeight money,\n"
|
+ " RecurringId int,OptionJson varchar(200),OptionText varchar(200),\n"
|
+ " Quantity money,Price money,Amount money,DateAdded datetime,\n"
|
+ " TotalQuantity money,TotalAmount money,TotalWeight money, \n"
|
+ " ShopMatCode varchar(50),ShopCcCode varchar(50),Mininum bigint) ; \n"
|
+ " insert into @table( CartId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,BaseUomWeight,\n"
|
+ " Quantity,Price,Amount,DateAdded,ShopMatCode,ShopCcCode,Mininum ) \n"
|
+ " select a.CartId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.BaseUomWeight,\n "
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode,b.Mininum \n"
|
+ " from t710401 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " join t110504 c on b.Brand = c.Brand \n"
|
+ " join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where (isnull(@cartIds,'') <> '' and a.CartId in (select list from getinstr(@cartIds))) \n"
|
+" and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and isnull(a.CltCode,'') = @CltCode \n"
|
+ " order by b.MatName asc ; \n "
|
+ " select @TotalQuantity = SUM(isnull(Quantity,0)),@TotalAmount = SUM(isnull(Amount,0)) \n"
|
+ " from @table \n"
|
+ " update a set TotalQuantity= @TotalQuantity,TotalAmount = @TotalAmount \n"
|
+ " from @table a \n"
|
+ " select CartId,UserCode,UserName,SessionId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,BaseUomWeight,RecurringId,OptionJson,OptionText, \n"
|
+ " Quantity,Price,Amount,DateAdded ,TotalQuantity,TotalAmount ,TotalWeight, \n"
|
+ " ShopMatCode,ShopCcCode,Mininum \n"
|
+ " from @table \n";
|
|
List<Map<String,Object>> list = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql,cartIds ,cltCode) ;
|
|
List<CartEntity> cartList = new ArrayList<CartEntity>() ;
|
for (int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
CartEntity cart = getCartEntity(map);
|
cartList.add(cart);
|
}
|
return cartList;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
@Override
|
public Integer saveSelectedCartIds(String userCode, String sessionId,String openId,String cltCode,String shopCcCode,String selectedCartIds) {
|
//if (selectedCartIds == null || "".equals(selectedCartIds)) return null ; //这句话必须注释掉,因为 selectedCartIds 值为空值时,表示去掉全选的意思,Modified by Johns Wang,2021-11-09
|
String sql = " set nocount on ; \n"
|
+ " declare @UserCode varchar(20) = ? ,@SessionId varchar(200) = ? ; \n"
|
+ " declare @OpenId varchar(200) = ?, @CltCode varchar(50) = ? \n;"
|
+ " declare @ShopCcCode varchar(50) = ? \n"
|
+ " declare @SelectedCartIds varchar(max) = ? ,@myRowCount int \n"
|
+ " update a set isSelected = 0 \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where isnull(a.UserCode,'') <> '' and isnull(a.UserCode,'') = isnull(@UserCode,'') \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
+ " update a set isSelected = 0 \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where isnull(a.SessionId,'') <> '' and isnull(a.SessionId,'') = isnull(@SessionId,'') \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
+ " update a set isSelected = 0 \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where isnull(a.CltCode,'') <> '' and isnull(a.CltCode,'') = isnull(@CltCode,'') \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
+ " update a set isSelected = 0 \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where isnull(a.OpenId,'') <> '' and isnull(a.OpenId,'') = isnull(@OpenId,'') \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
+ " if isnull(@SelectedCartIds,'') <> '' \n"
|
+ " begin \n"
|
+ " update a set isSelected = 1 \n"
|
+ " from t710205 a \n"
|
+ " where a.CartId in (select list from getinstr(@SelectedCartIds)) \n"
|
+ " select @myRowCount = @@rowcount \n"
|
+ " end \n"
|
+ " select @myRowCount \n";
|
try {
|
return this.jdbcTemplate.queryForObject(sql, Integer.class,new Object[] {userCode,sessionId,openId,cltCode,shopCcCode,selectedCartIds}) ;
|
} catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
@Override
|
public Integer addSelectedCartId(Integer cartId,boolean isSelected) {
|
String sql = " set nocount on ; \n"
|
+ " declare @CartId int = ? , @isSelected int = ? \n"
|
+ " update a set isSelected = @isSelected \n"
|
+ " from t710205 a where CartId = @CartId \n"
|
+ " select @@rowcount \n";
|
try {
|
return this.jdbcTemplate.queryForObject(sql, Integer.class,new Object[] {cartId,isSelected}) ;
|
} catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
/**
|
* 此过程是生成订单前显示的购物车列表,与 p120230v3 过程中获取购物车的SQL语句必须一致,否则可能导致页面显示的记录条数与实现生成的记录条数不一致
|
* @author JohnsWang , 2016-07-22
|
*/
|
@Override
|
public List<CartEntity> getCartList(String userCode, String sessionId,String openId,String cltCode,String shopCcCode) {
|
String sql = " set nocount on ; \n"
|
+ " declare @MaxPreSendDateTime datetime = getdate() ,@MaxPreSendMinutes int; \n"
|
+ " declare @UserCode varchar(20) = ? ,@SessionId varchar(200) = ? ; \n"
|
+ " declare @OpenId varchar(200) = ?, @CltCode varchar(50) = ? \n;"
|
+ " declare @ShopCcCode varchar(50) = ? \n"
|
+ " declare @TotalQuantity money,@SelectedTotalQuantity money,@TotalAmount money ; \n"
|
+ " declare @SelectedTotalAmount money,@TotalWeight money,@SelectedTotalWeight money ; \n"
|
|
+ " declare @table table(CartId int Primary Key,UserCode varchar(20),UserName varchar(50), \n"
|
+ " SessionId varchar(200),CltCode varchar(50),CltName varchar(200),\n"
|
+ " MatCode varchar(50),MatName varchar(200), \n"
|
+ " Special varchar(200),PhotoPath varchar(200),PhotoPathUrl varchar(200),BaseUomWeight money,\n"
|
+ " RecurringId int,OptionJson varchar(200),OptionText varchar(200),\n"
|
+ " Quantity money,Price money,Amount money,DateAdded datetime,\n"
|
+ " TotalQuantity money,SelectedTotalQuantity money,TotalAmount money,SelectedTotalAmount money,\n"
|
+ " TotalWeight money,SelectedTotalWeight money,\n"
|
+ " ShopMatCode varchar(50),ShopCcCode varchar(50),Mininum bigint,\n"
|
+ " MatName2 varchar(80),MatName3 varchar(80),MatName4 varchar(80),Brand varchar(50),isSelected int ,\n"
|
+ " PreSendMinutes int,PreSendMinutesDesc varchar(50), \n"
|
+ " MaxPreSendMinutes int, MaxPreSendMinutesDesc varchar(50),MaxPreSendDateTime datetime, isStartupGroupBuying int,\n"
|
+ " skuId1 int,skuName1 varchar(50),skuId2 int,skuName2 varchar(50),skuId3 int,skuName3 varchar(50),skuId4 int,skuName4 varchar(50),"
|
+ " skuId5 int,skuName5 varchar(50),skuId6 int,skuName6 varchar(50),skuId7 int,skuName7 varchar(50),skuId8 int,skuName8 varchar(50),"
|
+ " skuId9 int,skuName9 varchar(50),skuId10 int,skuName10 varchar(50),isRestrictQuantity int,RestrictQuantity money, \n"
|
+ " CartRowCountForMatCode int,CartQuantitySumForMatCode money, \n"
|
+ " index index1 (matcode) ) ; \n"
|
|
+ " insert into @table( CartId,UserCode,UserName,SessionId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,PhotoPathUrl,BaseUomWeight,RecurringId,OptionJson,OptionText,\n"
|
+ " Quantity,Price,Amount,DateAdded,ShopMatCode,ShopCcCode ,Mininum,\n"
|
+ " MatName2 ,MatName3 ,MatName4 ,Brand ,isSelected,PreSendMinutes,PreSendMinutesDesc,isStartupGroupBuying, \n"
|
+ " skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5,\n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10, \n"
|
+ " isRestrictQuantity,RestrictQuantity) \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.PhotoPathUrl,b.BaseUomWeight,a.RecurringId,a.OptionJson,a.OptionText,\n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode,b.Mininum , \n"
|
+ " b.MatName2 ,b.MatName3 ,b.MatName4 ,b.Brand, a.isSelected, \n"
|
+ " b.PreSendMinutes,dbo.f110503v2 (b.PreSendMinutes) as PreSendMinutesDesc,b.isStartupGroupBuying, \n"
|
+ " a.skuId1,a.skuName1,a.skuId2,a.skuName2,a.skuId3,a.skuName3,a.skuId4,a.skuName4,a.skuId5,a.skuName5,\n"
|
+ " a.skuId6,a.skuName6,a.skuId7,a.skuName7,a.skuId8,a.skuName8,a.skuId9,a.skuName9,a.skuId10,a.skuName10, \n"
|
+ " b.isRestrictQuantity ,case isnull(b.isRestrictQuantity,0) when 0 then 0 when 1 then b.Quantity when 2 then b.StockDigitForShoppingSaleable else 0 end as RestrictQuantity \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " join t110504 c on b.Brand = c.Brand \n"
|
//+ " left join t710175D d on a.MatCode = d.MatCode \n"
|
+ " where isnull(a.UserCode,'') <> '' and isnull(a.UserCode,'') = isnull(@UserCode,'') \n"
|
+" and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
// + " and exists (select 1 from t710104 t join t110501 e on t.MatGroup = e.MatGroup \n"
|
// + " where b.DocCode = t.doccode and isnull(e.Status,0) = 1 ) \n"
|
// + " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
|
+ " insert into @table(CartId,UserCode,UserName,SessionId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,PhotoPathUrl,BaseUomWeight,RecurringId,OptionJson,OptionText,\n"
|
+ " Quantity,Price,Amount,DateAdded ,ShopMatCode,ShopCcCode,Mininum, \n"
|
+ " MatName2 ,MatName3 ,MatName4 ,Brand ,isSelected,PreSendMinutes,PreSendMinutesDesc, isStartupGroupBuying,\n"
|
+ " skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5,\n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10, \n"
|
+ " isRestrictQuantity ,RestrictQuantity) \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.PhotoPathUrl,b.BaseUomWeight,a.RecurringId,a.OptionJson,a.OptionText,\n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode,b.Mininum ,\n"
|
+ " b.MatName2 ,b.MatName3 ,b.MatName4 ,b.Brand ,a.isSelected, \n"
|
+ " b.PreSendMinutes,dbo.f110503v2 (b.PreSendMinutes) as PreSendMinutesDesc,b.isStartupGroupBuying, \n"
|
+ " a.skuId1,a.skuName1,a.skuId2,a.skuName2,a.skuId3,a.skuName3,a.skuId4,a.skuName4,a.skuId5,a.skuName5,\n"
|
+ " a.skuId6,a.skuName6,a.skuId7,a.skuName7,a.skuId8,a.skuName8,a.skuId9,a.skuName9,a.skuId10,a.skuName10 ,\n"
|
+ " b.isRestrictQuantity ,case isnull(b.isRestrictQuantity,0) when 0 then 0 when 1 then b.Quantity when 2 then b.StockDigitForShoppingSaleable else 0 end as RestrictQuantity \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " join t110504 c on b.Brand = c.Brand \n"
|
//+ " left join t710175D d on a.MatCode = d.MatCode \n"
|
+ " where isnull(a.SessionId,'') <> '' and isnull(a.SessionId,'') = isnull(@SessionId,'')\n"
|
+ " and not exists(select 1 from @table c where a.CartId = c.CartId)\n"
|
+" and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
// + " and exists (select 1 from t710104 t join t110501 e on t.MatGroup = e.MatGroup \n"
|
// + " where b.DocCode = t.doccode and isnull(e.Status,0) = 1 ) \n"
|
// + " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
|
+ " insert into @table(CartId,UserCode,UserName,SessionId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,PhotoPathUrl,BaseUomWeight,RecurringId,OptionJson,OptionText,\n"
|
+ " Quantity,Price,Amount,DateAdded ,ShopMatCode,ShopCcCode,Mininum ,\n"
|
+ " MatName2 ,MatName3 ,MatName4 ,Brand ,isSelected,PreSendMinutes,PreSendMinutesDesc, isStartupGroupBuying, \n"
|
+ " skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5,\n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10 ,\n"
|
+ " isRestrictQuantity ,RestrictQuantity) \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.PhotoPathUrl,b.BaseUomWeight,a.RecurringId,a.OptionJson,a.OptionText,\n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode ,b.Mininum, \n"
|
+ " b.MatName2 ,b.MatName3 ,b.MatName4 ,b.Brand ,a.isSelected, \n"
|
+ " b.PreSendMinutes,dbo.f110503v2 (b.PreSendMinutes) as PreSendMinutesDesc ,b.isStartupGroupBuying,\n"
|
+ " a.skuId1,a.skuName1,a.skuId2,a.skuName2,a.skuId3,a.skuName3,a.skuId4,a.skuName4,a.skuId5,a.skuName5,\n"
|
+ " a.skuId6,a.skuName6,a.skuId7,a.skuName7,a.skuId8,a.skuName8,a.skuId9,a.skuName9,a.skuId10,a.skuName10, \n"
|
+ " b.isRestrictQuantity ,case isnull(b.isRestrictQuantity,0) when 0 then 0 when 1 then b.Quantity when 2 then b.StockDigitForShoppingSaleable else 0 end as RestrictQuantity \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " join t110504 c on b.Brand = c.Brand \n"
|
// + " left join t710175D d on a.MatCode = d.MatCode \n"
|
+ " where isnull(a.CltCode,'') <> '' and isnull(a.CltCode,'') = isnull(@CltCode,'') \n"
|
+ " and not exists(select 1 from @table c where a.CartId = c.CartId)\n"
|
+" and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
// + " and exists (select 1 from t710104 t join t110501 e on t.MatGroup = e.MatGroup \n"
|
// + " where b.DocCode = t.doccode and isnull(e.Status,0) = 1 ) \n"
|
// + " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
|
+ " insert into @table(CartId,UserCode,UserName,SessionId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,PhotoPathUrl,BaseUomWeight,RecurringId,OptionJson,OptionText,\n"
|
+ " Quantity,Price,Amount,DateAdded ,ShopMatCode,ShopCcCode,Mininum ,\n"
|
+ " MatName2 ,MatName3 ,MatName4 ,Brand ,isSelected,PreSendMinutes,PreSendMinutesDesc,isStartupGroupBuying, \n"
|
+ " skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5,\n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10 ,\n"
|
+ " isRestrictQuantity ,RestrictQuantity) \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.PhotoPathUrl,b.BaseUomWeight,a.RecurringId,a.OptionJson,a.OptionText,\n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode,b.Mininum , \n"
|
+ " b.MatName2 ,b.MatName3 ,b.MatName4 ,b.Brand ,a.isSelected, \n"
|
+ " b.PreSendMinutes,dbo.f110503v2 (b.PreSendMinutes) as PreSendMinutesDesc,b.isStartupGroupBuying, \n"
|
+ " a.skuId1,a.skuName1,a.skuId2,a.skuName2,a.skuId3,a.skuName3,a.skuId4,a.skuName4,a.skuId5,a.skuName5,\n"
|
+ " a.skuId6,a.skuName6,a.skuId7,a.skuName7,a.skuId8,a.skuName8,a.skuId9,a.skuName9,a.skuId10,a.skuName10, \n"
|
+ " b.isRestrictQuantity ,case isnull(b.isRestrictQuantity,0) when 0 then 0 when 1 then b.Quantity when 2 then b.StockDigitForShoppingSaleable else 0 end as RestrictQuantity \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " join t110504 c on b.Brand = c.Brand \n"
|
// + " left join t710175D d on a.MatCode = d.MatCode \n"
|
+ " where isnull(a.OpenId,'') <> '' and isnull(a.OpenId,'') = isnull(@OpenId,'') \n"
|
+ " and not exists(select 1 from @table c where a.CartId = c.CartId)\n"
|
+" and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
// + " and exists (select 1 from t710104 t join t110501 e on t.MatGroup = e.MatGroup \n"
|
// + " where b.DocCode = t.doccode and isnull(e.Status,0) = 1 ) \n"
|
// + " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
|
+ " select @TotalQuantity = SUM(isnull(Quantity,0)), \n"
|
+ " @SelectedTotalQuantity = SUM(case when isnull(isSelected,0) = 1 then isnull(Quantity,0) else 0 end),\n"
|
+ " @TotalAmount = SUM(isnull(Amount,0)), \n"
|
+ " @SelectedTotalAmount = SUM(case when isnull(isSelected,0) = 1 then isnull(Amount,0) else 0 end), \n"
|
+ " @TotalWeight = sum(isnull(Quantity,0) * isnull(BaseUomWeight,0)), \n"
|
+ " @SelectedTotalWeight = SUM(case when isnull(isSelected,0) = 1 then isnull(Quantity,0) * isnull(BaseUomWeight,0) else 0 end), \n"
|
+ " @MaxPreSendMinutes = max(PreSendMinutes) \n"
|
+ " from @table \n"
|
|
//更新所有商品总数量
|
+ " update a set TotalQuantity= @TotalQuantity,SelectedTotalQuantity= @SelectedTotalQuantity,\n"
|
+ " TotalAmount = @TotalAmount,SelectedTotalAmount = @SelectedTotalAmount,\n"
|
+ " TotalWeight=@TotalWeight,SelectedTotalWeight=@SelectedTotalWeight, \n"
|
+ " MaxPreSendMinutes = @MaxPreSendMinutes,MaxPreSendMinutesDesc = dbo.f110503v2 (@MaxPreSendMinutes), \n"
|
+ " MaxPreSendDateTime = dateadd(minute, @MaxPreSendMinutes, @MaxPreSendDateTime) \n"
|
+ " from @table a \n"
|
|
//更新每个商品条数和每个商品总数量
|
+ " update a set CartRowCountForMatCode = b.CartRowCount,CartQuantitySumForMatCode = b.CartQuantitySum \n"
|
+ " from @table a \n"
|
+ " join (select MatCode,count(MatCode) as CartRowCount,sum(isnull(Quantity,0)) as CartQuantitySum \n"
|
+ " from @table group by MatCode ) b on a.MatCode = b.MatCode \n"
|
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,a.MatName, \n"
|
+ " a.Special,a.PhotoPath,a.PhotoPathUrl,a.BaseUomWeight,a.RecurringId,a.OptionJson,a.OptionText, \n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded ,a.TotalQuantity,a.SelectedTotalQuantity,\n"
|
+ " a.TotalAmount,a.SelectedTotalAmount ,a.TotalWeight,a.SelectedTotalWeight,\n"
|
+ " a.ShopMatCode,a.ShopCcCode,a.Mininum ,a.MatName2 ,a.MatName3 ,a.MatName4 ,a.Brand ,a.isSelected, \n"
|
+ " a.PreSendMinutes,a.PreSendMinutesDesc,a.MaxPreSendMinutes,a.MaxPreSendMinutesDesc,a.MaxPreSendDateTime ,a.isStartupGroupBuying, \n"
|
+ " a.skuId1,a.skuName1,a.skuId2,a.skuName2,a.skuId3,a.skuName3,a.skuId4,a.skuName4,a.skuId5,a.skuName5, \n"
|
+ " a.skuId6,a.skuName6,a.skuId7,a.skuName7,a.skuId8,a.skuName8,a.skuId9,a.skuName9,a.skuId10,a.skuName10, \n"
|
+ " a.isRestrictQuantity ,a.RestrictQuantity,a.CartRowCountForMatCode,a.CartQuantitySumForMatCode \n"
|
+ " from @table a \n"
|
+ " order by a.MatName asc ; ";
|
|
List<Map<String,Object>> list = null ;
|
try {
|
//System.out.println(getClass() + " sql: " + sql ) ;
|
list = this.jdbcTemplate.queryForList(sql,userCode,sessionId,openId,cltCode,shopCcCode) ;
|
|
List<CartEntity> cartList = new ArrayList<CartEntity>() ;
|
for (int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
CartEntity cart = getCartEntity(map);
|
cartList.add(cart);
|
}
|
return cartList;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
|
}
|
|
|
@Override
|
public List<CartEntity> getCartsByPicture(String cltCode) {
|
String sql = " set nocount on ; \n"
|
|
+ " declare @CltCode varchar(50) = ? \n;"
|
+ " declare @TotalQuantity money,@TotalAmount money,@TotalWeight money ; \n"
|
+ " declare @table table(CartId int,CltCode varchar(50),CltName varchar(200),\n"
|
+ " MatCode varchar(50),MatName varchar(200), \n"
|
+ " Special varchar(200),PhotoPath varchar(200),\n"
|
+ " Quantity money,Price money,Amount money,DateAdded datetime,\n"
|
+ " TotalQuantity money,TotalAmount money,\n"
|
+ " ShopMatCode varchar(50),ShopCcCode varchar(50),ShopCcName varchar(50)) ; \n"
|
|
+ " insert into @table( CartId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath,\n"
|
+ " Quantity,Price,Amount,DateAdded ,ShopMatCode,ShopCcCode,ShopCcName ) \n"
|
+ " select a.CartId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,\n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode ,d.ccname as ShopCcName \n"
|
+ " from t710401 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " join t110504 c on b.Brand = c.Brand \n"
|
+ " join t110601 d on b.ShopCcCode = d.CcCode \n"
|
//+ " join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where isnull(a.CltCode,'') <> '' and isnull(a.CltCode,'') = isnull(@CltCode,'') \n"
|
+ " and not exists(select 1 from @table c where a.CartId = c.CartId)\n"
|
+" and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and exists (select 1 from t710104 t join t110501 e on t.MatGroup = e.MatGroup \n"
|
+ " where b.DocCode = t.doccode and isnull(e.Status,0) = 1 ) \n"
|
|
|
+ " select @TotalQuantity = SUM(isnull(Quantity,0)),@TotalAmount = SUM(isnull(Amount,0)) \n"
|
+ " from @table \n"
|
+ " update a set TotalQuantity= @TotalQuantity,TotalAmount = @TotalAmount \n"
|
+ " from @table a \n"
|
+ " select CartId,CltCode,CltName,MatCode,MatName, \n"
|
+ " Special,PhotoPath, \n"
|
+ " Quantity,Price,Amount,DateAdded ,TotalQuantity,TotalAmount ,\n"
|
+ " ShopMatCode,ShopCcCode,ShopCcName \n"
|
+ " from @table a \n"
|
+ " order by a.MatName asc ; ";
|
|
List<Map<String,Object>> list = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql,cltCode) ;
|
|
List<CartEntity> cartList = new ArrayList<CartEntity>() ;
|
for (int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
CartEntity cart = getCartEntity(map);
|
cartList.add(cart);
|
}
|
return cartList;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
|
}
|
|
@Override
|
public CartEntity getCartEntity(Map<String,Object> map) {
|
CartEntity cart = new CartEntity() ;
|
if (map != null && map.size() > 0 ) {
|
cart.setCartId(map.get("CartId") == null ? null: (Integer)map.get("CartId"));
|
cart.setUserCode(map.get("UserCode") == null ? "": (String)map.get("UserCode"));
|
cart.setUserName(map.get("UserName") == null ? "": (String)map.get("UserName"));
|
cart.setSessionId(map.get("SessionId") == null ? "": (String)map.get("SessionId"));
|
cart.setCltCode(map.get("CltCode") == null ? "": (String)map.get("CltCode"));
|
cart.setCltName(map.get("CltName") == null ? "": (String)map.get("CltName"));
|
cart.setMatCode(map.get("MatCode") == null ? "": (String)map.get("MatCode"));
|
cart.setMatName(map.get("MatName") == null ? "": (String)map.get("MatName"));
|
cart.setSpecial(map.get("Special") == null ? "": (String)map.get("Special"));
|
cart.setRecurringId(map.get("RecurringId") == null ?null: (Integer)map.get("RecurringId"));
|
cart.setOptionJson(map.get("OptionJson") == null ? "": (String)map.get("OptionJson"));
|
cart.setOptionText(map.get("OptionText") == null ? "": (String)map.get("OptionText"));
|
cart.setQuantity(map.get("Quantity") == null ? 0: Double.parseDouble(map.get("Quantity").toString()));
|
cart.setPrice(map.get("Price") == null ? 0: Double.parseDouble(map.get("Price").toString()));
|
cart.setAmount(map.get("Amount") == null ? 0: Double.parseDouble(map.get("Amount").toString()));
|
cart.setWeight(map.get("BaseUomWeight") == null ? 0: Double.parseDouble(map.get("BaseUomWeight").toString()));
|
cart.setDateAdded(map.get("DateAdded") == null ? null: (Date)map.get("DateAdded"));
|
cart.setPhotoPath(map.get("PhotoPath") == null ? "": (String)map.get("PhotoPath"));
|
cart.setPhotoPathUrl(map.get("PhotoPathUrl") == null ? "": (String)map.get("PhotoPathUrl"));
|
cart.setTotalQuantity(map.get("TotalQuantity") == null ? 0: Double.parseDouble(map.get("TotalQuantity").toString()));
|
cart.setSelectedTotalQuantity(map.get("SelectedTotalQuantity") == null ? 0: Double.parseDouble(map.get("SelectedTotalQuantity").toString()));
|
cart.setTotalAmount(map.get("TotalAmount") == null ? 0: Double.parseDouble(map.get("TotalAmount").toString()));
|
cart.setSelectedTotalAmount(map.get("SelectedTotalAmount") == null ? 0: Double.parseDouble(map.get("SelectedTotalAmount").toString()));
|
cart.setTotalWeight(map.get("TotalWeight") == null ? 0: Double.parseDouble(map.get("TotalWeight").toString()));
|
cart.setSelectedTotalWeight(map.get("SelectedTotalWeight") == null ? 0: Double.parseDouble(map.get("SelectedTotalWeight").toString()));
|
cart.setShopMatCode(map.get("ShopMatCode") == null ? "": (String)map.get("ShopMatCode"));
|
cart.setShopCcCode(map.get("ShopCcCode") == null ? "": (String)map.get("ShopCcCode"));
|
cart.setShopCcName(map.get("ShopCcName") == null ? "": (String)map.get("ShopCcName"));
|
|
cart.setMininum(map.get("Mininum") == null ? 0.00: Double.parseDouble(map.get("Mininum").toString()));
|
cart.setMatName2(map.get("MatName2") == null ? "": (String)map.get("MatName2"));
|
cart.setMatName3(map.get("MatName3") == null ? "": (String)map.get("MatName3"));
|
cart.setMatName4(map.get("MatName4") == null ? "": (String)map.get("MatName4"));
|
cart.setBrand(map.get("Brand") == null ? "": (String)map.get("Brand"));
|
cart.setSelected(map.get("isSelected") != null &&map.get("isSelected").equals(1)?true:false);
|
|
cart.setPreSendMinutes(map.get("PreSendMinutes") == null ? null: (Integer)map.get("PreSendMinutes"));
|
cart.setPreSendMinutesDesc(map.get("PreSendMinutesDesc") == null ? "": (String)map.get("PreSendMinutesDesc"));
|
cart.setMaxPreSendMinutes(map.get("MaxPreSendMinutes") == null ? null: (Integer)map.get("MaxPreSendMinutes"));
|
cart.setMaxPreSendMinutesDesc(map.get("MaxPreSendMinutesDesc") == null ? "": (String)map.get("MaxPreSendMinutesDesc"));
|
cart.setMaxPreSendDateTime(map.get("MaxPreSendDateTime") == null ? null: (Date)map.get("MaxPreSendDateTime"));
|
cart.setStartupGroupBuying(map.get("isStartupGroupBuying") != null&&map.get("isStartupGroupBuying").equals(1) ? true:false);
|
|
cart.setSkuId1(map.get("skuId1")==null?null:(Integer)map.get("skuId1"));
|
cart.setSkuName1(map.get("skuName1")==null?"":(String)map.get("skuName1"));
|
cart.setSkuId2(map.get("skuId2")==null?null:(Integer)map.get("skuId2"));
|
cart.setSkuName2(map.get("skuName2")==null?"":(String)map.get("skuName2"));
|
cart.setSkuId3(map.get("skuId3")==null?null:(Integer)map.get("skuId3"));
|
cart.setSkuName3(map.get("skuName3")==null?"":(String)map.get("skuName3"));
|
cart.setSkuId4(map.get("skuId4")==null?null:(Integer)map.get("skuId4"));
|
cart.setSkuName4(map.get("skuName4")==null?"":(String)map.get("skuName4"));
|
cart.setSkuId5(map.get("skuId5")==null?null:(Integer)map.get("skuId5"));
|
cart.setSkuName5(map.get("skuName5")==null?"":(String)map.get("skuName5"));
|
cart.setSkuId6(map.get("skuId6")==null?null:(Integer)map.get("skuId6"));
|
cart.setSkuName6(map.get("skuName6")==null?"":(String)map.get("skuName6"));
|
cart.setSkuId7(map.get("skuId7")==null?null:(Integer)map.get("skuId7"));
|
cart.setSkuName7(map.get("skuName7")==null?"":(String)map.get("skuName7"));
|
cart.setSkuId8(map.get("skuId8")==null?null:(Integer)map.get("skuId8"));
|
cart.setSkuName8(map.get("skuName8")==null?"":(String)map.get("skuName8"));
|
cart.setSkuId9(map.get("skuId9")==null?null:(Integer)map.get("skuId9"));
|
cart.setSkuName9(map.get("skuName9")==null?"":(String)map.get("skuName9"));
|
cart.setSkuId10(map.get("skuId10")==null?null:(Integer)map.get("skuId10"));
|
cart.setSkuName10(map.get("skuName10")==null?"":(String)map.get("skuName10"));
|
cart.setCartRowCount(map.get("CartRowCount") == null ?0: (Integer)map.get("CartRowCount"));
|
cart.setCartQuantitySum(map.get("CartQuantitySum") == null ? 0: Double.parseDouble(map.get("CartQuantitySum").toString()));
|
cart.setIsRestrictQuantity(map.get("isRestrictQuantity") == null? 0: (Integer)map.get("isRestrictQuantity"));
|
cart.setRestrictQuantity(map.get("RestrictQuantity") == null ? 0: Double.parseDouble(map.get("RestrictQuantity").toString()));
|
cart.setCartRowCountForMatCode(map.get("CartRowCountForMatCode") == null ?0: (Integer)map.get("CartRowCountForMatCode"));
|
cart.setCartQuantitySumForMatCode(map.get("CartQuantitySumForMatCode") == null ? 0: Double.parseDouble(map.get("CartQuantitySumForMatCode").toString()));
|
}
|
return cart;
|
}
|
|
@Override
|
public String getCartsHtml(String userCode,String sessionId,String openId,String cltCode,String hostUrl,
|
Integer freeId,double freeCost,String freeName,
|
String couponCode,double couponCost,String couponName,
|
String voucherCode ,double voucherCost,String wxQueryString,String shopCcCode,HttpServletRequest request) {
|
SettingEntity settingEntity = settingIfc.getSettingEntity() ;
|
CurrencyEntity currencyEntity = currencyIfc.getUserCurrency(userCode);
|
List<CartEntity> cartList = this.getCartList(userCode, sessionId,openId,cltCode,shopCcCode);
|
boolean isMoblieBrowser = SettingKey.isMoblieBrowser(request);
|
double totalQuantity = (cartList!=null&&cartList.size()>0?cartList.get(0).getTotalQuantity():0);
|
double totalAmount = (cartList!=null&&cartList.size()>0?cartList.get(0).getTotalAmount():0);
|
String ret = "";
|
ret +=" <div id=\"cart\" class=\"btn-group btn-block\">\n";
|
ret +=" <button type=\"button\" data-toggle=\"dropdown\" data-loading-text=\"正在加载...\" class=\"btn btn-inverse btn-block btn-lg dropdown-toggle\"><i class=\"fa fa-shopping-cart\"></i> <span id=\"cart-total\">" +cartList.size() +" 种商品(共" + (new Double(totalQuantity)).intValue() +"件)"+(settingEntity.isShowPrice()? " - "+currencyEntity.getCurrencySign()+""+totalAmount : "") +"</span></button>\n";
|
ret +=" <ul class=\"dropdown-menu pull-right\">\n";
|
if (cartList != null && cartList.size() > 0 ) {
|
ret +=" <li>\n";
|
ret +=" <table class=\"table table-striped\">\n";
|
|
for (int i = 0 ;cartList != null && i < cartList.size();i++) {
|
CartEntity cart = cartList.get(i);
|
ret +=" <tr>\n";
|
ret +=" <td class=\"text-center\"><a href=\""+hostUrl +SettingKey.getMatCodeUrl(null,cart.getMatCode(),isMoblieBrowser) +(wxQueryString == null||"".equals(wxQueryString)?"":"&" + wxQueryString) + "\"> \n";
|
ret +=" <img src=\"" + shoppingImageDataIfc.getImageUrl(cart.getPhotoPath(), settingEntity.getImageCartWidth(), settingEntity.getImageCartHeight(),settingEntity.isShowCartOrgImage(),settingEntity.isFromCached(),request)+ "\" alt=\""+(settingEntity.isShowMatName()? cart.getMatName():"")+"\" title=\""+(settingEntity.isShowMatName()? cart.getMatName():"")+"\" class=\"img-thumbnail\" /></a>\n";
|
ret +=" </td>\n";
|
ret +=" <td class=\"text-left\"><a href=\""+hostUrl +SettingKey.getMatCodeUrl(null,cart.getMatCode(),isMoblieBrowser) +(wxQueryString == null||"".equals(wxQueryString)?"":"&" + wxQueryString)+"\">"+(settingEntity.isShowMatName()? cart.getMatName():"")+"</a>\n";
|
ret +=" <br/>";
|
|
Map<String,Object> lists=null ;
|
if (cart.getOptionJson() != null && !"".equals(cart.getOptionJson())) {
|
lists = gson.fromJson(cart.getOptionJson(),new TypeToken<Map<String,Object>>(){}.getType());
|
for (Entry<String, Object> entry : lists.entrySet()) {
|
ret +=" - <small>" + this.getOptionStr(Integer.parseInt(entry.getKey()), (String)entry.getValue()) +"</small><br/>\n" ;
|
}
|
}
|
ret +=" </td>\n";
|
ret +=" <td class=\"text-right\">x "+ (new Double( cart.getQuantity())).intValue()+"</td>\n";
|
if (settingEntity.isShowPrice()) {
|
ret +=" <td class=\"text-right\">"+currencyEntity.getCurrencySign()+""+cart.getAmount()+"</td>\n";
|
}
|
ret +=" <td class=\"text-center\"><button type=\"button\" onclick=\"cart.remove('"+ cart.getCartId()+"');\" title=\"移除\" class=\"btn btn-danger btn-xs\"><i class=\"fa fa-times\"></i></button></td>\n";
|
ret +=" </tr>\n";
|
}
|
ret +=" </table>\n";
|
ret +=" </li>\n";
|
|
|
ret +=" <li>\n";
|
ret +=" <div>\n";
|
if (settingEntity.isShowPrice()) {
|
ret +=" <table class=\"table table-bordered\">\n";
|
ret +=" <tr>\n";
|
ret +=" <td class=\"text-right\"><strong>商品总额</strong></td>\n";
|
ret +=" <td class=\"text-right\">"+currencyEntity.getCurrencySign()+""+ totalAmount+"</td>\n";
|
ret +=" </tr>\n";
|
|
//预优配送费用
|
if (freeId != null) {
|
ret +=" <tr>";
|
ret +=" <td class=\"text-right\"><strong>"+freeName + "</strong></td>";
|
ret +=" <td class=\"text-right\">" + currencyEntity.getCurrencySign() + freeCost + "</td>";
|
ret +=" </tr>";
|
}
|
|
//优惠劵
|
if (couponCode != null) {
|
ret +=" <tr>";
|
ret +=" <td class=\"text-right\"><strong>优惠券("+ couponCode +")</strong></td>";
|
ret +=" <td class=\"text-right\">"+currencyEntity.getCurrencySign() +"" + ( 0L - couponCost) + "</td>";
|
ret +=" </tr>";
|
}
|
|
//礼品劵
|
if (voucherCode != null) {
|
ret +=" <tr>";
|
ret +=" <td class=\"text-right\"><strong>礼品劵("+ voucherCode + ")</strong></td>";
|
ret +=" <td class=\"text-right\">"+currencyEntity.getCurrencySign() +""+ ( 0L - voucherCost ) +"</td>";
|
ret +=" </tr>";
|
}
|
|
ret +=" <tr>\n";
|
ret +=" <td class=\"text-right\"><strong>订单总额</strong></td>\n";
|
ret +=" <td class=\"text-right\">"+currencyEntity.getCurrencySign()+""+ ( totalAmount + freeCost - couponCost - voucherCost )+"</td>\n";
|
ret +=" </tr>\n";
|
ret +=" </table>\n";
|
}
|
ret +=" <p class=\"text-right\"><a href=\""+hostUrl+"/shopping/cart/" + (wxQueryString == null||"".equals(wxQueryString)?"":"?" + wxQueryString) + "\"><strong><i class=\"fa fa-shopping-cart\"></i> 查看购物车</strong></a> <a href=\""+hostUrl + "/shopping/checkoutnew/" + (wxQueryString == null||"".equals(wxQueryString)?"":"?" + wxQueryString) + "\"><strong><i class=\"fa fa-share\"></i> 去结账</strong></a></p>\n";
|
ret +=" </div>\n";
|
ret +=" </li>\n";
|
|
|
} else {
|
ret +=" <li><p class=\"text-center\">您的购物车内没有商品!</p></li>\n";
|
}
|
ret +=" </ul>\n";
|
ret +=" </div>\n";
|
return ret ;
|
}
|
|
|
@Override
|
public String getOptionStr(Integer optionId, String value) {
|
String sql = "set nocount on ; \n"
|
+ " select OptionName,Type from t710116 where OptionId = ? \n" ;
|
Map<String,Object> map = null ;
|
try {
|
map = this.jdbcTemplate.queryForMap(sql,new Object[] { optionId } ) ;
|
String ret = "" ;
|
if (map != null ) {
|
String optionName = (map.get("OptionName") == null?"":(String) map.get("OptionName") );
|
String type = (map.get("Type") == null ?"": (String) map.get("Type"));
|
if (type!=null && "file".equals(type))
|
ret = optionName;
|
else
|
ret = optionName + ": " + (value == null || "".equals(value)?"" : value) ;
|
}
|
return ret.trim();
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
}
|
|
@Override
|
public String delCart(String cartIds) {
|
String sql = " set nocount on ; \n"
|
+ " declare @cartIds varchar(max) = ?, @matCodes varchar(max) \n"
|
+ " select @matCodes = isnull(@matCodes,'') + case when isnull(@matCodes,'') = '' then '' else ',' end + isnull(matcode,'') \n"
|
+ " from t710205 where CartId in (select list from getinstr(@cartIds) as a ) \n"
|
+ " delete from t710205 where CartId in (select list from getinstr(@cartIds) as a ) ; \n"
|
+ " select isnull(@matCodes,'') as MatCodes ; \n";
|
try {
|
return this.jdbcTemplate.queryForObject(sql, String.class, new Object[] { cartIds}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
|
@Override
|
public Integer delCartByPicture(Integer cartId) {
|
String sql = " set nocount on ; \n"
|
+ " delete from t710401 where CartId = ? ; \n"
|
+ " select @@rowcount ; \n";
|
|
Integer ret = null ;
|
try {
|
ret = this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { cartId}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return ret ;
|
}
|
|
@Override
|
public Integer delAllCart(String selectCartId) {
|
String sql = " set nocount on ; \n"
|
+ " delete from t710205 where CartId in (select list from getinstr(?) ) ; \n"
|
+ " select @@rowcount ; \n";
|
|
Integer ret = null ;
|
try {
|
ret = this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { selectCartId}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return ret ;
|
}
|
|
@Override
|
public Integer delAllCartByPicture(String selectCartId) {
|
String sql = " set nocount on ; \n"
|
+ " delete from t710401 where CartId in (select list from getinstr(?) ) ; \n"
|
+ " select @@rowcount ; \n";
|
|
Integer ret = null ;
|
try {
|
ret = this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { selectCartId}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return ret ;
|
}
|
|
@Override
|
public Integer delAllCart(String userCode,String sessionId,String openId) {
|
String sql = " set nocount on ; \n"
|
+ " delete from t710205 where UserCode = ? or SessionId = ? or OpenId = ? ; \n"
|
+ " select @@rowcount ; \n";
|
|
Integer ret = null ;
|
try {
|
ret = this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { userCode,sessionId,openId}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return ret ;
|
}
|
|
@Override
|
public CartEntity getCart(Integer cartId,String openId) {
|
String sql = " set nocount on ; \n"
|
+ " declare @CartId int = ?, @OpenId varchar(200) = ? , @CartRowCount int,@CartQuantitySum money \n"
|
+ " select @CartRowCount = count(1) , @CartQuantitySum = sum(isnull(Quantity,0)) \n"
|
+ " from t710205 where OpenId = @OpenId \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.BaseUomWeight,a.RecurringId,a.OptionJson, \n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode, \n"
|
+ " @CartRowCount as CartRowCount,@CartQuantitySum as CartQuantitySum \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where a.CartId = @CartId \n";
|
Map<String,Object> map = null ;
|
try {
|
map = this.jdbcTemplate.queryForMap(sql,new Object[] {cartId,openId}) ;
|
}catch(DataAccessException e){
|
if (e instanceof EmptyResultDataAccessException){
|
map = null ;
|
} else {
|
e.printStackTrace();
|
throw e ;
|
}
|
}
|
|
return getCartEntity(map);
|
}
|
|
@Override
|
public CartEntity getCart(Integer cartId) {
|
String sql = " set nocount on ; \n"
|
+ " select a.CartId,a.UserCode,a.UserName,a.SessionId,a.CltCode,a.CltName,a.MatCode,b.MatName, \n"
|
+ " b.Special,b.PhotoPath,b.BaseUomWeight,a.RecurringId,a.OptionJson, \n"
|
+ " a.Quantity,a.Price,a.Amount,a.DateAdded,b.ShopMatCode,b.ShopCcCode \n"
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where a.CartId = ? \n";
|
Map<String,Object> map = null ;
|
try {
|
map = this.jdbcTemplate.queryForMap(sql,cartId) ;
|
}catch(DataAccessException e){
|
if (e instanceof EmptyResultDataAccessException){
|
map = null ;
|
} else {
|
e.printStackTrace();
|
throw e ;
|
}
|
}
|
|
return getCartEntity(map);
|
}
|
@Override
|
public Integer updateCartPrice(Integer cartId,Double price,Double amount,double points,double weight) {
|
String sql = " set nocount on ; \n"
|
+ " update a set price = ? ,ManualPrice=?,Amount = ?,Points = ?,Weight = ? from t710205 a where CartId = ? \n"
|
+ " select @@rowcount ; \n";
|
|
Integer ret = null ;
|
try {
|
ret = this.jdbcTemplate.queryForObject(sql, Integer.class,new Object[] {price,price,amount,points,weight,cartId}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return ret ;
|
}
|
@Override
|
public String updateCartQuantity(Integer cartId,double quantity,String cltCode) {
|
String sql = " set nocount on ; \n"
|
+ " declare @myrowcount int ,@Quantity money = ?,@CartId int = ? ,@MatCode varchar(50) \n"
|
+ " update a set Quantity = @Quantity from t710205 a where CartId = @CartId \n"
|
+ " select @myrowcount = @@rowcount \n"
|
+ " exec p710205v8 @CartId = @CartId \n" //刷新购物车中的价格、积分和重量
|
+ " select @MatCode = MatCode from t710205 where CartId = @CartId ; \n"
|
+ " select isnull(@MatCode,'') as MatCode \n";
|
|
try {
|
//System.out.println(this.getClass()+ " start updateCartQuantity: SpObserver.getCurrentInstance():" +SpObserver.getCurrentInstance()+" SessionKey.SHOPPING_DBID:"+dbId );
|
return this.jdbcTemplate.queryForObject(sql, String.class,new Object[] {quantity,cartId}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
//刷新购物车中的价格、积分和重量
|
//this.refrshCart(cartId,cltCode);
|
}
|
|
@Override
|
public Integer updateCartQuantityByPicture(Integer cartId,double quantity) {
|
String sql = " set nocount on ; \n"
|
+ " declare @Quantity money = ? \n"
|
+ " update a set Quantity = @Quantity,Amount = round(isnull(@Quantity,0) * isnull(Price,0),2) from t710401 a where CartId = ? \n"
|
+ " select @@rowcount ; \n";
|
|
Integer ret = null ;
|
try {
|
//System.out.println(this.getClass()+ " start updateCartQuantity: SpObserver.getCurrentInstance():" +SpObserver.getCurrentInstance()+" SessionKey.SHOPPING_DBID:"+dbId );
|
ret = this.jdbcTemplate.queryForObject(sql, Integer.class,new Object[] {quantity,cartId}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return ret ;
|
}
|
|
@Override
|
public boolean isShipping(String userCode, String sessionId,String openId,String cltCode) {
|
String sql = " set nocount on ; \n"
|
+ " declare @UserCode varchar(20) = ? ,@SessionId varchar(200) = ? ; \n"
|
+ " declare @OpenId varchar(200) = ?, @CltCode varchar(50) = ? \n; "
|
+ " declare @found int = 0 ; \n"
|
+ " if isnull(@found,0) = 0 and exists (select top 1 1 \n "
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where a.UserCode = @UserCode and isnull(b.Shipping,0) = 1 ) \n"
|
+ " begin select @found = 1 ; end \n"
|
+ " if isnull(@found,0) = 0 and exists (select top 1 1 \n "
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where a.SessionId = @SessionId and isnull(b.Shipping,0) = 1 ) \n"
|
+ " begin select @found = 1 ; end \n"
|
+ " if isnull(@found,0) = 0 and exists (select top 1 1 \n "
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where a.OpenId = @OpenId and isnull(b.Shipping,0) = 1 ) \n"
|
+ " begin select @found = 1 ; end \n"
|
+ " if isnull(@found,0) = 0 and exists (select top 1 1 \n "
|
+ " from t710205 a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " where a.CltCode = @CltCode and isnull(b.Shipping,0) = 1 ) \n"
|
+ " begin select @found = 1 ; end \n"
|
+ " select @found ; \n";
|
|
|
Integer ret = null ;
|
try {
|
ret = this.jdbcTemplate.queryForObject(sql, Integer.class,new Object[] {userCode,sessionId,openId,cltCode}) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return false ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return (ret == null || ret == 0 ? false : true ) ;
|
}
|
|
}
|