package com.yc.sdk.shopping.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.sdk.shopping.entity.*;
|
import com.yc.sdk.weixincp.util.AvatarFile;
|
import com.yc.sdk.weixincp.util.FileExtensionName;
|
import com.yc.sdk.weixincp.util.UploadFiles;
|
import com.yc.service.BaseService;
|
import org.apache.commons.lang3.StringUtils;
|
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.io.File;
|
import java.math.BigDecimal;
|
import java.util.*;
|
|
@Service("MatCodeImpl")
|
@Scope("prototype")
|
public class MatCodeImpl extends BaseService implements MatCodeIfc {
|
private String orderBy = null ;
|
private boolean isShowSubMatGroup = false ; //是否显示子物料组的数据
|
private String whereOthers = null ; //其它where条件
|
|
@Override
|
public String getWhereOthers() {
|
return whereOthers;
|
}
|
@Override
|
public void setWhereOthers(String whereOthers) {
|
this.whereOthers = whereOthers;
|
}
|
@Override
|
public boolean isShowSubMatGroup() {
|
return isShowSubMatGroup;
|
}
|
@Override
|
public void setShowSubMatGroup(boolean isShowSubMatGroup) {
|
this.isShowSubMatGroup = isShowSubMatGroup;
|
}
|
|
@Override
|
public String getOrderBy() {
|
return orderBy;
|
}
|
|
@Override
|
public void setOrderBy(String orderBy) {
|
if (orderBy != null && orderBy.indexOf("a.SortOrder") > 0 ){
|
this.orderBy = orderBy.replace("a.SortOrder", "case when a.SortOrder is null then 999999 else a.SortOrder end");
|
} else {
|
this.orderBy = orderBy;
|
}
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByMatGroup(String matGroup,int limit,int page,String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
//if (matGroup == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatGroup varchar(50) =" +GridUtils.prossSqlParm(matGroup)+" \n"
|
+ " declare @Limit int = "+limit+" , @Page int = " + page+",@StartRowNo int ,@EndRowNo int ; \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" ,@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+" ,@OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" \n"
|
+ " declare @TotalRowCount int ; \n";
|
if (!this.isShowSubMatGroup()) {
|
sql += " declare @ShoppingMatGroups varchar(200) ;\n"
|
+ " select @ShoppingMatGroups = isnull(@ShoppingMatGroups,'') + ';' +list \n"
|
+ " from GetInStr(@MatGroup) a \n"
|
+ " order by list \n"
|
+ " select @ShoppingMatGroups = @ShoppingMatGroups + ';' \n";
|
|
}
|
sql += " select @TotalRowCount = count(1) \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
|
if (!this.isShowSubMatGroup()) {
|
sql += " and (isnull(@ShoppingMatGroups,'') = '' or a.ShoppingMatGroups like '%' + @ShoppingMatGroups + '%') \n";
|
} else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select matgroup from f110501(@MatGroup)) \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode )) \n";
|
}
|
|
|
sql += " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,@TotalRowCount as TotalRowCount, \n"
|
+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
+ " a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
if (!this.isShowSubMatGroup()) {
|
sql += " and (isnull(@ShoppingMatGroups,'') = '' or a.ShoppingMatGroups like '%' + @ShoppingMatGroups + '%') \n";
|
} else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select matgroup from f110501(@MatGroup)) \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode )) \n";
|
}
|
sql += " and isnull(a.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity+" \n";
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n"
|
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return matCodeEntityList;
|
}
|
|
/**
|
* 根据state值处理商品是否为上架或下架,拼接相关sql
|
* @param state
|
* 0显示全部,1显示上架,2显示售罄,3显示未上架
|
* @return
|
*/
|
private String prossProductState(int state){
|
switch (state){
|
case 0:
|
return "";
|
case 1:
|
return " and 1=case when isnull(f.DateAvailable,'')<>'' and datediff(minute,isnull(f.DateAvailable,0),GETDATE())>0 then 1 else 0 end ";
|
case 2:
|
//库存为0
|
return "";
|
case 3:
|
return " and 1=case when isnull(f.DateAvailable,'')='' and datediff(minute,isnull(f.DateAvailable,0),GETDATE())<0 then 1 else 0 end ";
|
default:
|
return "";
|
}
|
|
}
|
@Override
|
public List<MatCodeEntity> getMatCodesByMatGroup_MinApp(String matGroup, String matName, int state, int limit, int page, String shopCcCode) {
|
|
String sql = "set nocount on ; \n"
|
+ " declare @MatGroup varchar(50) = "+GridUtils.prossSqlParm(matGroup)+" \n"
|
+ " declare @MatName varchar(50) = "+GridUtils.prossSqlParm(matName)+" \n"
|
|
+ " declare @Limit int = " + limit+" , @Page int = "+page+" ,@StartRowNo int ,@EndRowNo int ; \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" \n"
|
+ " declare @TotalRowCount int ; \n"
|
+ " select @TotalRowCount = count(1) \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join v710123 f on a.doccode = f.doccode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@MatName,'') = '' or a.MatName like '%'+@MatName+'%' ) \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ prossProductState(state) ;
|
if (!this.isShowSubMatGroup()) {
|
sql += " and (isnull(@MatGroup,'') = '' or a.ShoppingMatGroups like '%;' + @MatGroup + ';%' ) \n" ;
|
}else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select matgroup from f110501(@MatGroup)) \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode )) \n";
|
}
|
sql += " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
|
+ " SELECT totalRowCount=@TotalRowCount,* FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,@TotalRowCount as TotalRowCount, \n"
|
+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
+ " a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join v710123 f on a.doccode = f.doccode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@MatName,'') = '' or a.MatName like '%'+@MatName+'%' ) \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
+ prossProductState(state) ;
|
if (!this.isShowSubMatGroup()) {
|
sql += " and (isnull(@MatGroup,'') = '' or a.ShoppingMatGroups like '%;' + @MatGroup + ';%' ) \n" ;
|
}else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select matgroup from f110501(@MatGroup)) \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode )) \n";
|
}
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n"
|
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return matCodeEntityList;
|
}
|
@Override
|
public List<MatCodeEntity> getMatCodesBySearch(String search,String matGroup ,
|
String isSubMatGroup ,String isDescription,int limit,int page,String couponCode,String shopCcCode,boolean isShowAllWhenBlankSearch,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
if (!isShowAllWhenBlankSearch && (search == null || "".equals(search)) && (couponCode ==null || "".equals(couponCode)) ) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @Search varchar(200) ="+GridUtils.prossSqlParm(search)+" ,@MatGroup varchar(50) ="+GridUtils.prossSqlParm(matGroup)+" ; \n"
|
|
+ " declare @Limit int ="+limit+" , @Page int ="+page+" ,@StartRowNo int ,@EndRowNo int ; \n"
|
|
+ " declare @CouponCode varchar(50) ="+GridUtils.prossSqlParm(couponCode)+",@CouponBrands varchar(max),@CouponMatCodes varchar(max),@CouponMatGroups varchar(max) ; \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+",@CltCode varchar(20) ="+GridUtils.prossSqlParm(cltCode)+",@OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" \n"
|
+ " if isnull(@CouponCode,'') <> '' \n"
|
+ " begin \n"
|
+ " select @CouponBrands = case when isnull(@CouponBrands,'') = '' then '' else @CouponBrands+',' end + g.Brand \n"
|
+ " from t710301H f join t710302 g on f.doccode = g.doccode \n"
|
+ " join t110504 h on g.brand = h.brand \n"
|
+ " where f.couponcode = @CouponCode \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or (f.ShopCcCode = @ShopCcCode and h.ShopCcCode = @ShopCcCode) ) \n"
|
|
+ " select @CouponMatGroups = case when isnull(@CouponMatGroups,'') = '' then '' else @CouponMatGroups+',' end + g.MatGroup \n"
|
+ " from t710301H f join t710303 g on f.doccode = g.doccode \n"
|
+ " join t110501 h on g.MatGroup = h.MatGroup \n"
|
+ " where f.couponcode = @CouponCode \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or (f.ShopCcCode = @ShopCcCode and h.ShopCcCode = @ShopCcCode) ) \n"
|
|
+ " select @CouponMatCodes = case when isnull(@CouponMatCodes,'') = '' then '' else @CouponMatCodes+',' end + g.MatCode \n"
|
+ " from t710301H f join t710304 g on f.doccode = g.doccode \n"
|
+ " join t110503 h on g.MatCode = h.MatCode \n"
|
+ " where f.couponcode = @CouponCode \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or (f.ShopCcCode = @ShopCcCode and h.ShopCcCode = @ShopCcCode) ) \n"
|
+ " end \n"
|
+ " "
|
+ " declare @TotalRowCount int ; \n"
|
+ " select @TotalRowCount = count(1) \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and ( " + (isShowAllWhenBlankSearch?"isnull(@Search,'') = '' or ":"") + "\n"
|
+ " a.MatCode like '%'+@Search+'%' \n"
|
+ " or a.MatName like '%' + @Search + '%' \n"
|
+ " or a.Special like '%' + @Search + '%' \n" ;
|
if (isDescription != null && "1".equals(isDescription)) {
|
sql += " or a.Description like '%' + @Search + '%' \n" ;
|
}
|
sql += " ) \n" ;
|
sql += " and (isnull(@CouponBrands,'') = '' or a.brand in (select list from getinstr(@CouponBrands))) \n"
|
+ " and (isnull(@CouponMatGroups,'') = '' or a.matgroup in (select list from getinstr(@CouponMatGroups))) \n"
|
+ " and (isnull(@CouponMatCodes,'') = '' or a.matcode in (select list from getinstr(@CouponMatCodes))) \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n "
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
|
if (matGroup != null && ! "".equals(matGroup) ) {
|
if (isSubMatGroup != null ) {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select MatGroup from f110501(@MatGroup) a ) \n"
|
+ " and isnull(e.Status,0) = 1 ) \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) \n" ;
|
}else {
|
sql += " and (isnull(@MatGroup,'') = '' or isnull(a.ShoppingMatGroups,'') like '%;'+ @MatGroup + ';%' ) \n" ;
|
//sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
// + " where a.DocCode = b.doccode and b.matgroup = @MatGroup \n"
|
// + " and isnull(e.Status,0) = 1 ) \n"
|
// + " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) \n" ;
|
}
|
} /* else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode)) \n" ;
|
}*/
|
|
if (this.whereOthers != null && !"".equals(this.whereOthers)) {
|
sql += " and (" + this.whereOthers + ") \n" ;
|
}
|
|
|
sql += " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,@TotalRowCount as TotalRowCount, \n"
|
+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
+ " a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+" and (" + (isShowAllWhenBlankSearch?"isnull(@Search,'') = '' or ":"") + "\n"
|
+ " a.MatCode like '%'+@Search+'%' \n"
|
+ " or a.MatName like '%' + @Search + '%' \n"
|
+ " or a.Special like '%' + @Search + '%' \n" ;
|
if (isDescription != null && "1".equals(isDescription)) {
|
sql += " or a.Description like '%' + @Search + '%' \n" ;
|
}
|
sql += ") \n" ;
|
sql += " and (isnull(@CouponBrands,'') = '' or a.brand in (select list from getinstr(@CouponBrands))) \n"
|
+ " and (isnull(@CouponMatGroups,'') = '' or a.matgroup in (select list from getinstr(@CouponMatGroups))) \n"
|
+ " and (isnull(@CouponMatCodes,'') = '' or a.matcode in (select list from getinstr(@CouponMatCodes))) \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n" ;
|
|
if (matGroup != null && ! "".equals(matGroup) ) {
|
if (isSubMatGroup != null ) {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select MatGroup from f110501(@MatGroup) a ) \n"
|
+ " and isnull(e.Status,0) = 1 ) \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) \n" ;
|
}else {
|
sql += " and (isnull(@MatGroup,'') = '' or isnull(a.ShoppingMatGroups,'') like '%;'+ @MatGroup + ';%' ) \n" ;
|
//sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
// + " where a.DocCode = b.doccode and b.matgroup = @MatGroup and isnull(e.Status,0) = 1 ) \n"
|
// + " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) \n" ;
|
}
|
} /*else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) ) \n" ;
|
} */
|
|
if (this.whereOthers != null && !"".equals(this.whereOthers)) {
|
sql += " and (" + this.whereOthers + ") \n" ;
|
}
|
sql += " and isnull(a.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity+"\n";
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n"
|
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByWishList(String userCode,String sessionId,String openId,String cltCode,String shopCcCode) {
|
if (cltCode == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @UserCode varchar(50) = "+GridUtils.prossSqlParm(userCode)+" ,@SessionId varchar(200) = "+GridUtils.prossSqlParm(sessionId)+" \n"
|
+ " declare @OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+",@CltCode varchar(50) = "+GridUtils.prossSqlParm(cltCode)+"; \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" \n"
|
+ " declare @table table(MatCode varchar(40)) \n"
|
|
+ " insert into @table( MatCode) \n"
|
+ " select a.MatCode from t710206 a join t110503 b on a.MatCode = b.Matcode \n"
|
+ " where isnull(a.UserCode,'') <> '' and isnull(a.UserCode,'') = isnull(@UserCode,'') \n"
|
+ " and not exists(select 1 from @table c where a.MatCode = c.MatCode)\n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode) \n"
|
|
|
+ " insert into @table( MatCode) "
|
+ " select a.MatCode from t710206 a join t110503 b on a.MatCode = b.Matcode \n"
|
+ " where isnull(a.SessionId,'') <> '' and isnull(a.SessionId,'') = isnull(@SessionId,'') \n"
|
+ " and not exists(select 1 from @table c where a.MatCode = c.MatCode)\n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode) \n"
|
|
+ " insert into @table( MatCode) \n"
|
+ " select a.MatCode from t710206 a join t110503 b on a.MatCode = b.Matcode \n"
|
+ " where isnull(a.OpenId,'') <> '' and isnull(a.OpenId,'') = isnull(@OpenId,'') \n"
|
+ " and not exists(select 1 from @table c where a.MatCode = c.MatCode)\n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode) \n"
|
|
+ " insert into @table( MatCode) \n"
|
+ " select a.MatCode from t710206 a join t110503 b on a.MatCode = b.Matcode \n"
|
+ " where isnull(a.CltCode,'') <> '' and isnull(a.CltCode,'') = isnull(@CltCode,'') \n"
|
+ " and not exists(select 1 from @table c where a.MatCode = c.MatCode)\n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode) \n"
|
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " join @table x on a.MatCode = x.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) ) \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode)"
|
+ " order by case when a.SortOrder is null then 999999 else a.SortOrder end asc ,a.MatCode asc; \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i)));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByCompare(String userCode,String sessionId,String openId,String cltCode,String shopCcCode,Integer isAllowExchangePointsQuantity) {
|
if (userCode == null ) return null ;
|
|
String sql = "set nocount on ; \n"
|
+ " declare @UserCode varchar(50) = "+GridUtils.prossSqlParm(userCode)+" ,@SessionId varchar(200) = "+GridUtils.prossSqlParm(sessionId)+" \n"
|
+ " declare @OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+",@CltCode varchar(50) = "+GridUtils.prossSqlParm(cltCode)+"; \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" \n"
|
+ " declare @table table(MatCode varchar(40)) \n"
|
|
+ " insert into @table( MatCode) \n"
|
+ " select a.MatCode from t710207 a join t110503 b on a.MatCode = b.Matcode \n"
|
+ " where isnull(a.UserCode,'') <> '' and isnull(a.UserCode,'') = isnull(@UserCode,'') \n"
|
+ " and not exists(select 1 from @table c where a.MatCode = c.MatCode)\n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode) \n"
|
|
|
|
+ " insert into @table( MatCode) \n"
|
+ " select a.MatCode from t710207 a join t110503 b on a.MatCode = b.Matcode \n"
|
+ " where isnull(a.SessionId,'') <> '' and isnull(a.SessionId,'') = isnull(@SessionId,'') \n"
|
+ " and not exists(select 1 from @table c where a.MatCode = c.MatCode)\n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode) \n"
|
|
+ " insert into @table( MatCode) \n"
|
+ " select a.MatCode from t710207 a join t110503 b on a.MatCode = b.Matcode \n"
|
+ " where isnull(a.OpenId,'') <> '' and isnull(a.OpenId,'') = isnull(@OpenId,'') \n"
|
+ " and not exists(select 1 from @table c where a.MatCode = c.MatCode)\n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode) \n"
|
|
+ " insert into @table( MatCode) \n"
|
+ " select a.MatCode from t710207 a join t110503 b on a.MatCode = b.Matcode \n"
|
+ " where isnull(a.CltCode,'') <> '' and isnull(a.CltCode,'') = isnull(@CltCode,'') \n"
|
+ " and not exists(select 1 from @table c where a.MatCode = c.MatCode)\n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode) \n"
|
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " join @table x on a.MatCode = x.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) ) \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n"
|
+ " order by case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ; \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i)));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByCompareByPicture(String cltCode) {
|
if (cltCode == null ) return null ;
|
|
String sql = "set nocount on ; \n"
|
+ " declare @CltCode varchar(50) = "+GridUtils.prossSqlParm(cltCode)+" ; \n"
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " join t710403 x on a.MatCode = x.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 ) \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and x.cltcode = @CltCode \n"
|
+ " order by case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ; \n";
|
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i)));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByWishListByPicture(String cltCode) {
|
if (cltCode == null ) return null ;
|
|
String sql = "set nocount on ; \n"
|
+ " declare @CltCode varchar(50) = "+GridUtils.prossSqlParm(cltCode)+" ; \n"
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " join t710402 x on a.MatCode = x.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 ) \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and x.cltcode = @CltCode \n"
|
+ " order by case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ; \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i)));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
|
@Override
|
public List<MatCodeEntity> getMatCodesAll(String matGroup,String shopMatCode,String shopCcCode,String brand) {
|
|
String sql = "set nocount on ; \n"
|
+ " declare @MatGroup varchar(200) = "+GridUtils.prossSqlParm(matGroup)+", @ShopMatCode varchar(200) = "+GridUtils.prossSqlParm(shopMatCode)+" \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" ,@Brand varchar(200) = "+GridUtils.prossSqlParm(brand)+" \n"
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4 ,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " where (isnull(@ShopMatCode,'') = '' or a.ShopMatcode in (select list from getinstr( @ShopMatCode)) ) \n"
|
+ " and (isnull(@MatGroup,'') = '' or a.MatGroup in (select matgroup from f110501(@MatGroup))) \n"
|
+ " and isnull(a.inActive,0) = 0 \n"
|
//+ " and isnull(a.Status,0) = 1 \n"
|
//+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode in (select list from getinstr( @ShopCcCode))) \n"
|
+ " and (isnull(@Brand,'') = '' or a.Brand in (select list from getinstr(@brand))) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) ) \n"
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList ;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodes(String matGroup,String shopMatCode,String shopCcCode,String brand,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
|
String sql = "set nocount on ; \n"
|
+ " declare @MatGroup varchar(200) = "+GridUtils.prossSqlParm(matGroup)+", @ShopMatCode varchar(200) = "+GridUtils.prossSqlParm(shopMatCode)+" \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+", @Brand varchar(200) = "+GridUtils.prossSqlParm(brand)+",@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+" ,@OpenId varchar(200) ="+GridUtils.prossSqlParm(openId)+" \n"
|
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4 ,h.CartQuantity, a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where (isnull(@ShopMatCode,'') = '' or a.ShopMatcode in (select list from getinstr( @ShopMatCode)) ) \n"
|
+ " and (isnull(@MatGroup,'') = '' or a.MatGroup in (select matgroup from f110501(@MatGroup))) \n"
|
+ " and isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode in (select list from getinstr( @ShopCcCode))) \n"
|
+ " and (isnull(@Brand,'') = '' or a.Brand in (select list from getinstr(@brand))) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
+ " and isnull(a.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity+" \n";
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) ) \n";
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList ;
|
}
|
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByFromUserId(String fromUserId,String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
|
String sql = "set nocount on ; \n"
|
+ " declare @UserId varchar(200) = "+GridUtils.prossSqlParm(fromUserId)+" \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" ,@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+" ,@OpenId varchar(200) ="+GridUtils.prossSqlParm(openId)+" \n"
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc,a.PurchasePrice, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4 ,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " join t700121 x on a.MatCode = x.MatCode \n"
|
+ " join t700107 y on x.DocCode = y.DocCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where (isnull(@UserId,'') = '' or y.UserId in (select list from getinstr( @UserId)) ) \n"
|
+ " and isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode in (select list from getinstr( @ShopCcCode))) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
+ " and isnull(a.isAllowExchangePointsQuantity,0) ="+isAllowExchangePointsQuantity+" \n";
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) ) \n";
|
sql += " order by x.DocItem asc \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList ;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByExchangePoints(String search,String matGroup,int limit,int page,String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
String munt = "1,2";
|
if (isAllowExchangePointsQuantity > 0) {
|
munt = String.valueOf(isAllowExchangePointsQuantity);
|
}
|
//if (matGroup == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatGroup varchar(50) =" +GridUtils.prossSqlParm(matGroup)+" \n"
|
+ " declare @Limit int = "+limit+" , @Page int = " + page+",@StartRowNo int ,@EndRowNo int ; \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" ,@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+" ,@OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" \n"
|
+ " declare @TotalRowCount int ; \n";
|
if (!this.isShowSubMatGroup()) {
|
sql += " declare @ShoppingMatGroups varchar(200) ;\n"
|
+ " select @ShoppingMatGroups = isnull(@ShoppingMatGroups,'') + ';' +list \n"
|
+ " from GetInStr(@MatGroup) a \n"
|
+ " order by list \n"
|
+ " select @ShoppingMatGroups = @ShoppingMatGroups + ';' \n";
|
|
}
|
sql += " select @TotalRowCount = count(1) \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
|
if (!this.isShowSubMatGroup()) {
|
sql += " and (isnull(@ShoppingMatGroups,'') = '' or a.ShoppingMatGroups like '%' + @ShoppingMatGroups + '%') \n";
|
} else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select matgroup from f110501(@MatGroup)) \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode )) \n";
|
}
|
|
|
sql += " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,@TotalRowCount as TotalRowCount, \n"
|
+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
+ " a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
if (!this.isShowSubMatGroup()) {
|
sql += " and (isnull(@ShoppingMatGroups,'') = '' or a.ShoppingMatGroups like '%' + @ShoppingMatGroups + '%') \n";
|
} else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select matgroup from f110501(@MatGroup)) \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode )) \n";
|
}
|
sql += " and isnull(a.isAllowExchangePointsQuantity,0) in ("+munt+") \n";
|
if (StringUtils.isNotBlank(search)) {
|
sql += " and (a.MatCode like '%" + search + "%' \n"
|
+ " or a.MatName like '%" + search + "%' \n"
|
+ " or a.Special like '%" + search + "%') \n";
|
}
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n"
|
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesBySearchByExchangePoints(String search, String matGroup, int limit, int page, String shopCcCode, String cltCode, String openId, Integer isAllowExchangePointsQuantity, Integer maxUserPoints) {
|
String munt = "1,2";
|
if (isAllowExchangePointsQuantity > 0) {
|
munt = String.valueOf(isAllowExchangePointsQuantity);
|
}
|
//if (matGroup == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatGroup varchar(50) =" +GridUtils.prossSqlParm(matGroup)+" \n"
|
+ " declare @Limit int = "+limit+" , @Page int = " + page+",@StartRowNo int ,@EndRowNo int ; \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" ,@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+" ,@OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" \n"
|
+ " declare @TotalRowCount int ; \n";
|
if (!this.isShowSubMatGroup()) {
|
sql += " declare @ShoppingMatGroups varchar(200) ;\n"
|
+ " select @ShoppingMatGroups = isnull(@ShoppingMatGroups,'') + ';' +list \n"
|
+ " from GetInStr(@MatGroup) a \n"
|
+ " order by list \n"
|
+ " select @ShoppingMatGroups = @ShoppingMatGroups + ';' \n";
|
|
}
|
sql += " select @TotalRowCount = count(1) \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
|
if (!this.isShowSubMatGroup()) {
|
sql += " and (isnull(@ShoppingMatGroups,'') = '' or a.ShoppingMatGroups like '%' + @ShoppingMatGroups + '%') \n";
|
} else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select matgroup from f110501(@MatGroup)) \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode )) \n";
|
}
|
|
|
sql += " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,@TotalRowCount as TotalRowCount, \n"
|
+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
+ " a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
if (!this.isShowSubMatGroup()) {
|
sql += " and (isnull(@ShoppingMatGroups,'') = '' or a.ShoppingMatGroups like '%' + @ShoppingMatGroups + '%') \n";
|
} else {
|
sql += " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
+ " where a.DocCode = b.doccode \n"
|
+ " and b.matgroup in (select matgroup from f110501(@MatGroup)) \n"
|
+ " and isnull(e.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode )) \n";
|
}
|
sql += " and isnull(a.isAllowExchangePointsQuantity,0) in ("+munt+") \n";
|
if (maxUserPoints != null) {
|
sql += " and isnull(a.ExchangePointsQuantity,0) <= " + maxUserPoints + " \n";
|
}
|
if (StringUtils.isNotBlank(search)) {
|
sql += " and (a.MatCode like '%" + search + "%' \n"
|
+ " or a.MatName like '%" + search + "%' \n"
|
+ " or a.Special like '%" + search + "%') \n";
|
}
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n"
|
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
return matCodeEntityList;
|
}
|
|
|
@Override
|
public SkuMatCodeEntity getSkuMatCodeByMatCode(String matCode,String openId) {
|
String sql = "set nocount on ; \n"
|
+ " declare @MatCode varchar(20) = " + GridUtils.prossSqlParm(matCode) + ",@OpenId varchar(200) = " + GridUtils.prossSqlParm(openId) + ", @CartId int,@CartQuantity money\n"
|
+ " declare @skuId1 int ,@skuId2 int ,@skuId3 int ,@skuId4 int ,@skuId5 int , \n"
|
+ " @skuId6 int ,@skuId7 int ,@skuId8 int ,@skuId9 int ,@skuId10 int \n"
|
+ " declare @table table(DocItem int,skuId1 int,skuName1 varchar(50),skuId2 int,skuName2 varchar(50),\n"
|
+ " skuId3 int,skuName3 varchar(50),skuId4 int,skuName4 varchar(50),skuId5 int,skuName5 varchar(50),\n"
|
+ " skuId6 int,skuName6 varchar(50),skuId7 int,skuName7 varchar(50),skuId8 int,skuName8 varchar(50),\n"
|
+ " skuId9 int,skuName9 varchar(50),skuId10 int,skuName10 varchar(50),\n"
|
+ " MatCode varchar(50),MatName varchar(80),Special varchar(80),PhotoPath varchar(50),PhotoPathUrl varchar(2000), \n"
|
+ " CartId int,CartQuantity money ) \n"
|
+ " insert into @table(DocItem,skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5, \n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10,\n"
|
+ " MatCode,MatName,Special,PhotoPath,PhotoPathUrl) \n"
|
+ " select top 1 a.DocItem,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.MatCode,c.MatName,c.Special,c.PhotoPath,c.PhotoPathUrl \n"
|
+ " from t710175D a join t710175H b on a.DocCode = b.DocCode \n"
|
+ " join t110503 c on a.MatCode = c.MatCode \n"
|
+ " where a.MatCode = @MatCode and b.Status = 1 \n"
|
|
+ " select @skuId1=a.skuId1,@skuId2=a.skuId2,@skuId3=a.skuId3,@skuId4=a.skuId4,@skuId5=a.skuId5, \n"
|
+ " @skuId6=a.skuId6,@skuId7=a.skuId7,@skuId8=a.skuId8,@skuId9=a.skuId9,@skuId10=a.skuId10 \n"
|
+ " from @table a \n"
|
+ " select @CartId = a.CartId ,@CartQuantity = a.Quantity \n"
|
+ " from t710205 a \n"
|
+ " where a.OpenId = @OpenId and a.MatCode = @MatCode \n"
|
+ " and (a.skuId1 is null or a.skuId1 = @skuId1) and (a.skuId2 is null or a.skuId2 = @skuId2) \n"
|
+ " and (a.skuId3 is null or a.skuId3 = @skuId3) and (a.skuId4 is null or a.skuId4 = @skuId4)\n"
|
+ " and (a.skuId5 is null or a.skuId5 = @skuId5) and (a.skuId6 is null or a.skuId6 = @skuId6)\n"
|
+ " and (a.skuId7 is null or a.skuId7 = @skuId7) and (a.skuId8 is null or a.skuId8 = @skuId8)\n"
|
+ " and (a.skuId9 is null or a.skuId9 = @skuId9) and (a.skuId10 is null or a.skuId10 = @skuId10) \n"
|
//+ " and a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 = @skuId4 and a.skuId5 = @skuId5 \n"
|
//+ " and a.skuId6 = @skuId6 and a.skuId7= @skuId7 and a.skuId8 = @skuId8 and a.skuId9 = @skuId9 and a.skuId10 = @skuId10 \n"
|
|
//更新购物车数量
|
+ " update a set CartId = @CartId ,CartQuantity = @CartQuantity \n"
|
+ " from @table a \n"
|
|
+ " select DocItem,skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5, \n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10, \n"
|
+ " MatCode,MatName,Special,PhotoPath,PhotoPathUrl,CartId ,CartQuantity \n"
|
+ " from @table \n";
|
|
try {
|
Map<String, Object> map = this.jdbcTemplate.queryForMap(sql) ;
|
SkuMatCodeEntity skuMatCodeEntity = null ;
|
List<SkuMatCodeEntity> skuMatCodeList = new ArrayList<SkuMatCodeEntity>();
|
if (map != null) {
|
skuMatCodeEntity = new SkuMatCodeEntity();
|
|
skuMatCodeEntity.setDocItem(map.get("DocItem")==null?null:(Integer)map.get("DocItem"));
|
skuMatCodeEntity.setSkuId1(map.get("skuId1")==null?null:(Integer)map.get("skuId1"));
|
skuMatCodeEntity.setSkuName1(map.get("skuName1")==null?"":(String)map.get("skuName1"));
|
skuMatCodeEntity.setSkuId2(map.get("skuId2")==null?null:(Integer)map.get("skuId2"));
|
skuMatCodeEntity.setSkuName2(map.get("skuName2")==null?"":(String)map.get("skuName2"));
|
skuMatCodeEntity.setSkuId3(map.get("skuId3")==null?null:(Integer)map.get("skuId3"));
|
skuMatCodeEntity.setSkuName3(map.get("skuName3")==null?"":(String)map.get("skuName3"));
|
skuMatCodeEntity.setSkuId4(map.get("skuId4")==null?null:(Integer)map.get("skuId4"));
|
skuMatCodeEntity.setSkuName4(map.get("skuName4")==null?"":(String)map.get("skuName4"));
|
skuMatCodeEntity.setSkuId5(map.get("skuId5")==null?null:(Integer)map.get("skuId5"));
|
skuMatCodeEntity.setSkuName5(map.get("skuName5")==null?"":(String)map.get("skuName5"));
|
skuMatCodeEntity.setSkuId6(map.get("skuId6")==null?null:(Integer)map.get("skuId6"));
|
skuMatCodeEntity.setSkuName6(map.get("skuName6")==null?"":(String)map.get("skuName6"));
|
skuMatCodeEntity.setSkuId7(map.get("skuId7")==null?null:(Integer)map.get("skuId7"));
|
skuMatCodeEntity.setSkuName7(map.get("skuName7")==null?"":(String)map.get("skuName7"));
|
skuMatCodeEntity.setSkuId8(map.get("skuId8")==null?null:(Integer)map.get("skuId8"));
|
skuMatCodeEntity.setSkuName8(map.get("skuName8")==null?"":(String)map.get("skuName8"));
|
skuMatCodeEntity.setSkuId9(map.get("skuId9")==null?null:(Integer)map.get("skuId9"));
|
skuMatCodeEntity.setSkuName9(map.get("skuName9")==null?"":(String)map.get("skuName9"));
|
skuMatCodeEntity.setSkuId10(map.get("skuId10")==null?null:(Integer)map.get("skuId10"));
|
skuMatCodeEntity.setSkuName10(map.get("skuName10")==null?"":(String)map.get("skuName10"));
|
skuMatCodeEntity.setMatCode(map.get("MatCode")==null?"":(String)map.get("MatCode"));
|
skuMatCodeEntity.setMatName(map.get("MatName")==null?"":(String)map.get("MatName"));
|
skuMatCodeEntity.setSpecial(map.get("Special")==null?"":(String)map.get("Special"));
|
skuMatCodeEntity.setPhotoPath(map.get("PhotoPath")==null?"":(String)map.get("PhotoPath"));
|
skuMatCodeEntity.setPhotoPathUrl(map.get("PhotoPathUrl")==null?"":(String)map.get("PhotoPathUrl"));
|
skuMatCodeEntity.setCartId(map.get("CartId")==null?null:(Integer)map.get("CartId"));
|
skuMatCodeEntity.setCartQuantity(map.get("CartQuantity") == null ? 0.00: Double.parseDouble(map.get("CartQuantity").toString()));
|
skuMatCodeList.add(skuMatCodeEntity);
|
}
|
return skuMatCodeEntity ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
@Override
|
public SkuMatCodeEntity getSkuMatCodeBySkuId(Integer skuId1, Integer skuId2,
|
Integer skuId3, Integer skuId4,
|
Integer skuId5, Integer skuId6,
|
Integer skuId7, Integer skuId8,
|
Integer skuId9, Integer skuId10,String openId) {
|
String sql = "set nocount on ; \n"
|
+ " declare @skuId1 int = " + skuId1 +",@skuId2 int = " + skuId2 +",@skuId3 int = " + skuId3 +",@skuId4 int = " + skuId4 +", \n"
|
+ " @skuId5 int = " + skuId5 +",@skuId6 int = " + skuId6+",@skuId7 int = " + skuId7+",@skuId8 int = " + skuId8 +
|
",@skuId9 int = " + skuId9 +",@skuId10 int = " + skuId10 +" \n"
|
|
+ " declare @OpenId varchar(200) = " + GridUtils.prossSqlParm(openId) + "\n"
|
+ " declare @MatCode varchar(50) ,@CartId int,@CartQuantity money \n"
|
+ " declare @table table(DocItem int,skuId1 int,skuName1 varchar(50),skuId2 int,skuName2 varchar(50),\n"
|
+ " skuId3 int,skuName3 varchar(50),skuId4 int,skuName4 varchar(50),skuId5 int,skuName5 varchar(50),\n"
|
+ " skuId6 int,skuName6 varchar(50),skuId7 int,skuName7 varchar(50),skuId8 int,skuName8 varchar(50),\n"
|
+ " skuId9 int,skuName9 varchar(50),skuId10 int,skuName10 varchar(50),\n"
|
+ " MatCode varchar(50),MatName varchar(80),Special varchar(80),PhotoPath varchar(50),PhotoPathUrl varchar(2000), \b"
|
+ " CartId int,CartQuantity money ) \n"
|
+ " insert into @table(DocItem,skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5, \n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10,\n"
|
+ " MatCode,MatName,Special,PhotoPath,PhotoPathUrl) \n"
|
+ " select top 1 a.DocItem,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.MatCode,c.MatName,c.Special,c.PhotoPath,c.PhotoPathUrl \n"
|
+ " from t710175D a join t710175H b on a.DocCode = b.DocCode \n"
|
+ " join t110503 c on a.MatCode = c.MatCode \n"
|
+ " where b.Status = 1 \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"
|
|
+ " select @MatCode = a.MatCode from @table a \n"
|
+ " select @CartId = a.CartId ,@CartQuantity = a.Quantity \n"
|
+ " from t710205 a \n"
|
+ " where a.OpenId = @OpenId and a.MatCode = @MatCode \n"
|
+ " and (a.skuId1 is null or a.skuId1 = @skuId1) and (a.skuId2 is null or a.skuId2 = @skuId2) \n"
|
+ " and (a.skuId3 is null or a.skuId3 = @skuId3) and (a.skuId4 is null or a.skuId4 = @skuId4)\n"
|
+ " and (a.skuId5 is null or a.skuId5 = @skuId5) and (a.skuId6 is null or a.skuId6 = @skuId6)\n"
|
+ " and (a.skuId7 is null or a.skuId7 = @skuId7) and (a.skuId8 is null or a.skuId8 = @skuId8)\n"
|
+ " and (a.skuId9 is null or a.skuId9 = @skuId9) and (a.skuId10 is null or a.skuId10 = @skuId10) \n"
|
//+ " and a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 = @skuId4 and a.skuId5 = @skuId5 \n"
|
//+ " and a.skuId6 = @skuId6 and a.skuId7= @skuId7 and a.skuId8 = @skuId8 and a.skuId9 = @skuId9 and a.skuId10 = @skuId10 \n"
|
//更新购物车数量
|
+ " update a set CartId = @CartId ,CartQuantity = @CartQuantity \n"
|
+ " from @table a \n"
|
|
|
/*
|
+ " declare @CltCode varchar(20) = " + GridUtils.prossSqlParm(cltCode)+" \n"
|
+ " select a.BarCode,a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc,a.PurchasePrice, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " where a.matcode = @MatCode \n"
|
+ " and isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
*/
|
|
|
|
|
+ " select DocItem,skuId1,skuName1,skuId2,skuName2,skuId3,skuName3,skuId4,skuName4,skuId5,skuName5, \n"
|
+ " skuId6,skuName6,skuId7,skuName7,skuId8,skuName8,skuId9,skuName9,skuId10,skuName10, \n"
|
+ " MatCode,MatName,Special,PhotoPath,PhotoPathUrl,CartId ,CartQuantity \n"
|
+ " from @table \n";
|
|
try {
|
Map<String, Object> map = this.jdbcTemplate.queryForMap(sql) ;
|
SkuMatCodeEntity skuMatCodeEntity = null ;
|
List<SkuMatCodeEntity> skuMatCodeList = new ArrayList<SkuMatCodeEntity>();
|
if (map != null) {
|
skuMatCodeEntity = new SkuMatCodeEntity();
|
|
skuMatCodeEntity.setDocItem(map.get("DocItem")==null?null:(Integer)map.get("DocItem"));
|
skuMatCodeEntity.setSkuId1(map.get("skuId1")==null?null:(Integer)map.get("skuId1"));
|
skuMatCodeEntity.setSkuName1(map.get("skuName1")==null?"":(String)map.get("skuName1"));
|
skuMatCodeEntity.setSkuId2(map.get("skuId2")==null?null:(Integer)map.get("skuId2"));
|
skuMatCodeEntity.setSkuName2(map.get("skuName2")==null?"":(String)map.get("skuName2"));
|
skuMatCodeEntity.setSkuId3(map.get("skuId3")==null?null:(Integer)map.get("skuId3"));
|
skuMatCodeEntity.setSkuName3(map.get("skuName3")==null?"":(String)map.get("skuName3"));
|
skuMatCodeEntity.setSkuId4(map.get("skuId4")==null?null:(Integer)map.get("skuId4"));
|
skuMatCodeEntity.setSkuName4(map.get("skuName4")==null?"":(String)map.get("skuName4"));
|
skuMatCodeEntity.setSkuId5(map.get("skuId5")==null?null:(Integer)map.get("skuId5"));
|
skuMatCodeEntity.setSkuName5(map.get("skuName5")==null?"":(String)map.get("skuName5"));
|
skuMatCodeEntity.setSkuId6(map.get("skuId6")==null?null:(Integer)map.get("skuId6"));
|
skuMatCodeEntity.setSkuName6(map.get("skuName6")==null?"":(String)map.get("skuName6"));
|
skuMatCodeEntity.setSkuId7(map.get("skuId7")==null?null:(Integer)map.get("skuId7"));
|
skuMatCodeEntity.setSkuName7(map.get("skuName7")==null?"":(String)map.get("skuName7"));
|
skuMatCodeEntity.setSkuId8(map.get("skuId8")==null?null:(Integer)map.get("skuId8"));
|
skuMatCodeEntity.setSkuName8(map.get("skuName8")==null?"":(String)map.get("skuName8"));
|
skuMatCodeEntity.setSkuId9(map.get("skuId9")==null?null:(Integer)map.get("skuId9"));
|
skuMatCodeEntity.setSkuName9(map.get("skuName9")==null?"":(String)map.get("skuName9"));
|
skuMatCodeEntity.setSkuId10(map.get("skuId10")==null?null:(Integer)map.get("skuId10"));
|
skuMatCodeEntity.setSkuName10(map.get("skuName10")==null?"":(String)map.get("skuName10"));
|
skuMatCodeEntity.setMatCode(map.get("MatCode")==null?"":(String)map.get("MatCode"));
|
skuMatCodeEntity.setMatName(map.get("MatName")==null?"":(String)map.get("MatName"));
|
skuMatCodeEntity.setSpecial(map.get("Special")==null?"":(String)map.get("Special"));
|
skuMatCodeEntity.setPhotoPath(map.get("PhotoPath")==null?"":(String)map.get("PhotoPath"));
|
skuMatCodeEntity.setPhotoPathUrl(map.get("PhotoPathUrl")==null?"":(String)map.get("PhotoPathUrl"));
|
skuMatCodeEntity.setCartId(map.get("CartId")==null?null:(Integer)map.get("CartId"));
|
skuMatCodeEntity.setCartQuantity(map.get("CartQuantity") == null ? 0.00: Double.parseDouble(map.get("CartQuantity").toString()));
|
skuMatCodeList.add(skuMatCodeEntity);
|
}
|
return skuMatCodeEntity ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
|
@Override
|
public List<SkuParameterEntity> getSkuParameter(String matCode,String shopCcCode,
|
Integer skuId1, Integer skuId2,
|
Integer skuId3, Integer skuId4,
|
Integer skuId5, Integer skuId6,
|
Integer skuId7, Integer skuId8,
|
Integer skuId9, Integer skuId10) {
|
if (matCode == null||"".equals(matCode)) return null ;
|
/*
|
String sql = "set nocount on ; \n"
|
+ " declare @skuCategoryId int ,@MatCode varchar(20) = " + GridUtils.prossSqlParm(matCode) + " ,@ShopCcCode varchar(50) = " + GridUtils.prossSqlParm(shopCcCode) +" ;\n"
|
+ " declare @skuSetTable table(skuSetSort int,skuSetId int,skuSetName varchar(50),\n"
|
+ " skuSort int,skuId int,skuName varchar(50),isEnableSelection int)\n"
|
+ " declare @skuListTable table(DocItem int,skuId1 int,skuName1 varchar(50),skuId2 int,skuName2 varchar(50),\n"
|
+ " skuId3 int,skuName3 varchar(50),skuId4 int,skuName4 varchar(50),skuId5 int,skuName5 varchar(50), \n"
|
+ " skuId6 int,skuName6 varchar(50),skuId7 int,skuName7 varchar(50),skuId8 int,skuName8 varchar(50),\n"
|
+ " skuId9 int,skuName9 varchar(50),skuId10 int,skuName10 varchar(50), \n"
|
+ " MatCode varchar(20),MatName varchar(80),Special varchar(80),PhotoPath varchar(500),PhotoPathUrl varchar(2000))\n"
|
+ " declare @skuIdsTable table (skuId int Primary Key) \n"
|
|
|
+ " declare @skuId1 int = " + skuId1 +",@skuId2 int = " + skuId2 +",@skuId3 int = " + skuId3 +",@skuId4 int = " + skuId4 +", \n"
|
+ " @skuId5 int = " + skuId5 +",@skuId6 int = " + skuId6+",@skuId7 int = " + skuId7+",@skuId8 int = " + skuId8 +
|
",@skuId9 int = " + skuId9 +",@skuId10 int = " + skuId10 +" \n"
|
|
+ " declare @isFound int = 0 \n"
|
+ " select @skuCategoryId = a.skuCategoryId \n"
|
+ " from t710175H a \n"
|
+ " where a.ShopCcCode = isnull(@ShopCcCode,'') and isnull(a.Status,0) = 1 \n"
|
+ " and exists(select 1 from t710175D b where a.DocCode = b.DocCode and b.MatCode = @MatCode ) \n"
|
|
+ " insert into @skuSetTable(skuSetSort,skuSetId,skuSetName,skuSort,skuId,skuName,isEnableSelection)\n"
|
+ " select b.DocItem as skuSetSort ,b.skuSetId,b.skuSetName,c.DocItem as skuSort,c.skuId,c.skuName ,0 \n"
|
+ " from t710173H a join t710173D b on a.DocCode = b.DocCode \n"
|
+ " join t710173D3 c on b.DocCode = c.DocCode and b.RowId = c.RowId \n"
|
+ " where a.skuCategoryId = @skuCategoryId and a.ShopCcCode = isnull(@ShopCcCode,'') \n"
|
+ " and isnull(a.Status,0) = 1 and isnull(b.Status,0) = 1 and isnull(c.Status,0) = 1 \n"
|
+ " order by b.DocItem asc,c.DocItem asc ;\n"
|
|
+ " insert into @skuListTable(DocItem ,skuId1 ,skuName1 ,skuId2 ,skuName2 ,\n"
|
+ " skuId3 ,skuName3 ,skuId4 ,skuName4 ,skuId5 ,skuName5 , \n"
|
+ " skuId6 ,skuName6,skuId7 ,skuName7 ,skuId8 ,skuName8 ,\n"
|
+ " skuId9 ,skuName9 ,skuId10 ,skuName10 , \n"
|
+ " MatCode ,MatName ,Special ,PhotoPath,PhotoPathUrl)\n"
|
+ " select a.DocItem,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.MatCode,c.MatName,c.Special,c.PhotoPath,c.PhotoPathUrl \n"
|
+ " from t710175D a \n"
|
+ " join t110503 c on a.MatCode = c.MatCode \n"
|
+ " where isnull(a.MatCode,'') <> '' \n"
|
+ " and exists(select 1 from t710175D b \n"
|
+ " join t110503 d on b.MatCode = d.MatCode \n"
|
+ " join t110504 e on d.Brand = e.Brand \n"
|
+ " where isnull(d.inActive,0) = 0 and isnull(d.Status,0) = 1 \n"
|
+ " and isnull(e.Status,0) = 1 and isnull(d.MatGroupStatus,0) = 1 \n"
|
+ " and a.DocCode = b.DocCode and b.MatCode = @MatCode) \n"
|
+ " order by a.DocItem asc \n"
|
|
+ " if isnull(@matCode,'') <> '' and @skuId1 is null\n"
|
+ " begin\n"
|
+ " select top 1 @skuId1 = a.skuId1,@skuId2=a.skuId2,@skuId3=a.skuId3,@skuId4=a.skuId4,@skuId5=a.skuId5,\n"
|
+ " @skuId6=a.skuId6,@skuId7=a.skuId7,@skuId8=a.skuId8,@skuId9=a.skuId9,@skuId10 = a.skuId10 \n"
|
+ " from @skuListTable a \n"
|
+ " where a.MatCode = @MatCode\n"
|
+ " order by a.docitem asc \n"
|
+ " set @isFound = @@ROWCOUNT \n"
|
+ "end \n"
|
|
+ " if isnull(@isFound,0) = 0 and @skuId1 is not null \n"
|
+ " begin \n"
|
+ " select top 1 @skuId1 = a.skuId1,@skuId2=a.skuId2,@skuId3=a.skuId3,@skuId4=a.skuId4,@skuId5=a.skuId5,\n"
|
+ " @skuId6=a.skuId6,@skuId7=a.skuId7,@skuId8=a.skuId8,@skuId9=a.skuId9,@skuId10 = a.skuId10 \n"
|
+ " from @skuListTable a \n"
|
+ " where (@skuId1 is null or isnull(a.skuId1,0) = isnull(@skuId1,0)) \n"
|
+ " and (@skuId2 is null or isnull(a.skuId2,0) = isnull(@skuId2,0)) \n"
|
+ " and (@skuId3 is null or isnull(a.skuId3,0) = isnull(@skuId3,0)) \n"
|
+ " and (@skuId4 is null or isnull(a.skuId4,0) = isnull(@skuId4,0)) \n"
|
+ " and (@skuId5 is null or isnull(a.skuId5,0) = isnull(@skuId5,0)) \n"
|
+ " and (@skuId6 is null or isnull(a.skuId6,0) = isnull(@skuId6,0))\n"
|
+ " and (@skuId7 is null or isnull(a.skuId7,0) = isnull(@skuId7,0))\n"
|
+ " and (@skuId8 is null or isnull(a.skuId8,0) = isnull(@skuId8,0))\n"
|
+ " and (@skuId9 is null or isnull(a.skuId9,0) = isnull(@skuId9,0))\n"
|
+ " and (@skuId10 is null or isnull(a.skuId10,0) = isnull(@skuId10,0)) \n"
|
+ " order by a.docitem asc \n"
|
+ " end \n"
|
|
+ " if @skuId9 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId10\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 = @skuId4\n"
|
+ " and a.skuId5 = @skuId5 and a.skuId6 = a.skuId6 and a.skuId7 = @skuId7 and a.skuId8 = @skuId8\n"
|
+ " and a.skuId9 = @skuId9 and a.skuId10 is not null\n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId10 = b.skuId)\n"
|
+ " end \n"
|
|
+ " if @skuId8 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId9\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 = @skuId4\n"
|
+ " and a.skuId5 = @skuId5 and a.skuId6 = a.skuId6 and a.skuId7 = @skuId7 and a.skuId8 = @skuId8\n"
|
+ " and a.skuId9 is not null\n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId9 = b.skuId)\n"
|
+ " end \n"
|
|
+ " if @skuId7 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId8\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 = @skuId4\n"
|
+ " and a.skuId5 = @skuId5 and a.skuId6 = a.skuId6 and a.skuId7 = @skuId7 and a.skuId8 is not null\n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId8 = b.skuId)\n"
|
+ " end \n"
|
|
+ " if @skuId6 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId7\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 = @skuId4\n"
|
+ " and a.skuId5 = @skuId5 and a.skuId6 = a.skuId6 and a.skuId7 is not null\n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId7 = b.skuId)\n"
|
+ " end \n"
|
|
+ " if @skuId5 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId6\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 = @skuId4\n"
|
+ " and a.skuId5 = @skuId5 and a.skuId6 is not null\n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId6 = b.skuId)\n"
|
+ " end \n"
|
|
+ " if @skuId4 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId5\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 = @skuId4\n"
|
+ " and a.skuId5 is not null\n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId5 = b.skuId)\n"
|
+ " end \n"
|
|
+ " if @skuId3 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId4\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 = @skuId3 and a.skuId4 is not null\n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId4 = b.skuId)\n"
|
+ " end \n"
|
+ " if @skuId2 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId3\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 = @skuId2 and a.skuId3 is not null \n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId3 = b.skuId)\n"
|
+ " end \n"
|
|
+ " if @skuId1 is not null\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId2\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId1 = @skuId1 and a.skuId2 is not null\n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId2 = b.skuId)\n"
|
+ " if not exists(select 1 from @skuIdsTable)\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId1\n"
|
+ " from @skuListTable a \n"
|
+ " end \n"
|
+ " end \n"
|
|
+ " if @skuId2 is not null --补齐第一级\n"
|
+ " begin\n"
|
+ " insert into @skuIdsTable(skuId) \n"
|
+ " select distinct a.skuId1\n"
|
+ " from @skuListTable a \n"
|
+ " where a.skuId2 = @skuId2 and a.skuId2 is not null \n"
|
+ " and not exists(select 1 from @skuIdsTable b where a.skuId1 = b.skuId)\n"
|
+ " end \n"
|
|
+ " update a set isEnableSelection = 1 \n"
|
+ " from @skuSetTable a join @skuIdsTable b on a.skuId = b.skuId \n"
|
|
+ " select skuSetSort ,skuSetId ,skuSetName ,\n"
|
+ " skuSort ,skuId ,skuName ,isEnableSelection\n"
|
+ " from @skuSetTable a\n"
|
+ " order by a.skuSetSort asc,a.skuSort asc ; \n";
|
*/
|
String sql = " set nocount on ;\n"
|
+ " select a.skuSetSort ,a.skuSetId ,a.skuSetName,a.skuSort ,a.skuId ,a.skuName ,a.isEnableSelection \n"
|
+ " from f710175v1("+GridUtils.prossSqlParm(matCode)+","+GridUtils.prossSqlParm(shopCcCode)+","+skuId1+","+skuId2+","+skuId3+","+skuId4+","+skuId5+","+skuId6+","+skuId7+","+skuId8+","+skuId9+","+skuId10+") a \n"
|
+ " order by a.skuSetSort asc,a.skuSort asc ; \n";
|
try {
|
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql) ;
|
List<SkuParameterEntity> skuParameterList = new ArrayList<SkuParameterEntity>();
|
for (int i = 0; list !=null && i < list.size(); i++) {
|
Map<String, Object> map = list.get(i) ;
|
SkuParameterEntity skuParameterEntity = new SkuParameterEntity();
|
skuParameterEntity.setSkuSetSort(map.get("skuSetSort")==null?null:(Integer)map.get("skuSetSort"));
|
skuParameterEntity.setSkuSetId(map.get("skuSetId")==null?null:(Integer)map.get("skuSetId"));
|
skuParameterEntity.setSkuSetName(map.get("skuSetName")==null?"":(String)map.get("skuSetName"));
|
skuParameterEntity.setSkuSort(map.get("skuSort")==null?null:(Integer)map.get("skuSort"));
|
skuParameterEntity.setSkuId(map.get("skuId")==null?null:(Integer)map.get("skuId"));
|
skuParameterEntity.setSkuName(map.get("skuName")==null?"":(String)map.get("skuName"));
|
skuParameterEntity.setEnableSelection(map.get("isEnableSelection")!=null&&map.get("isEnableSelection").equals(1) ?true:false);
|
skuParameterList.add(skuParameterEntity);
|
}
|
return skuParameterList ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
}
|
|
|
@Override
|
public MatCodeEntity getMatCodeForGenerationQrCode(String matCode) {
|
if (matCode == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatCode varchar(50) = "+GridUtils.prossSqlParm(matCode)+" \n"
|
+ " select a.DocCode,a.BarCode,a.MatCode,a.MatName,a.Special,a.ShopMatCode,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl \n"
|
+ " from t110503 a \n"
|
+ " where a.matcode = @MatCode \n";
|
|
Map<String,Object> map = null ;
|
try {
|
map = this.jdbcTemplate.queryForMap(sql) ;
|
return getMatCodeEntity( map);
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
@Override
|
public MatCodeEntity getMatCode(String matCode) {
|
if (matCode == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatCode varchar(50) = "+GridUtils.prossSqlParm(matCode)+" \n"
|
+ " select a.BarCode,a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " a.SalesPrice as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc,a.PurchasePrice, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a \n"
|
+ " left join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " where a.matcode = @MatCode \n";
|
|
Map<String,Object> map = null ;
|
try {
|
map = this.jdbcTemplate.queryForMap(sql) ;
|
return getMatCodeEntity( map);
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
|
@Override
|
public MatCodeEntity getMatCode(String matCode,String cltCode) {
|
return getMatCode(matCode,null,cltCode) ;
|
}
|
|
@Override
|
public MatCodeEntity getMatCode(String matCode,String shopCcCode,String cltCode) {
|
if (matCode == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatCode varchar(50) = " + GridUtils.prossSqlParm(matCode) +" \n"
|
+ " declare @ShopCcCode varchar(50) = " + GridUtils.prossSqlParm(shopCcCode) + ",@CltCode varchar(20) = " + GridUtils.prossSqlParm(cltCode) + " \n"
|
+ " select a.BarCode,a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc,a.PurchasePrice, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " where a.matcode = @MatCode \n"
|
+ " and isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode) ) \n";
|
|
Map<String,Object> map = null ;
|
try {
|
map = this.jdbcTemplate.queryForMap(sql) ;
|
return getMatCodeEntity( map);
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
|
@Override
|
public MatCodeEntity getMatCodeByPicture(String matCode) {
|
if (matCode == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatCode varchar(50) = "+GridUtils.prossSqlParm(matCode)+" \n"
|
+ " select a.BarCode,a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc,a.PurchasePrice, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " where matcode = @MatCode \n"
|
+ " and isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = b.doccode and isnull(e.Status,0) = 1 ) \n";
|
|
Map<String,Object> map = null ;
|
try {
|
map = this.jdbcTemplate.queryForMap(sql) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return getMatCodeEntity( map);
|
}
|
|
public static MatCodeEntity getMatCodeEntity(Map<String,Object> map) {
|
MatCodeEntity matCodeEntity = null ;
|
if (map != null) {
|
matCodeEntity = new MatCodeEntity() ;
|
matCodeEntity.setBarCode(map.get("BarCode") == null ? "": (String)map.get("BarCode"));
|
matCodeEntity.setMatCode(map.get("MatCode") == null ? "": (String)map.get("MatCode"));
|
matCodeEntity.setMatName(map.get("MatName") == null ? "": (String)map.get("MatName"));
|
matCodeEntity.setSpecial(map.get("Special") == null ? "": (String)map.get("Special"));
|
matCodeEntity.setDescription(map.get("Description") == null ? "": (String)map.get("Description"));
|
matCodeEntity.setMetaTitle(map.get("MetaTitle") == null ? "": (String)map.get("MetaTitle"));
|
matCodeEntity.setMetaDescription(map.get("MetaDescription") == null ? "": (String)map.get("MetaDescription"));
|
matCodeEntity.setMetaKeyword(map.get("MetaKeyword") == null ? "": (String)map.get("MetaKeyword"));
|
matCodeEntity.setTag(map.get("Tag") == null ? "": (String)map.get("Tag"));
|
matCodeEntity.setPhotoPath(map.get("PhotoPath") == null ? "": (String)map.get("PhotoPath"));
|
matCodeEntity.setPhotoPathUrl(map.get("PhotoPathUrl") == null ? "": (String)map.get("PhotoPathUrl"));
|
matCodeEntity.setImages(map.get("Images") == null ? "": (String)map.get("Images"));
|
matCodeEntity.setImagesUrl(map.get("ImagesUrl") == null ? "": (String)map.get("ImagesUrl"));
|
matCodeEntity.setBaseUOM(map.get("BaseUOM") == null ? "": (String)map.get("BaseUOM"));
|
matCodeEntity.setSalesUOM(map.get("SalesUOM") == null ? "": (String)map.get("SalesUOM"));
|
matCodeEntity.setLocation(map.get("Location") == null ? "": (String)map.get("Location"));
|
matCodeEntity.setSalesPrice(map.get("SalesPrice") == null ? null: Double.parseDouble(map.get("SalesPrice").toString()));
|
matCodeEntity.setPurchasePrice(map.get("PurchasePrice") == null ? null: Double.parseDouble(map.get("PurchasePrice").toString()));
|
matCodeEntity.setPrice(map.get("Price") == null ? null: Double.parseDouble(map.get("Price").toString()));
|
matCodeEntity.setSalesPriceByPicture(map.get("SalesPriceByPicture") == null ? null: Double.parseDouble(map.get("SalesPriceByPicture").toString()));
|
matCodeEntity.setIsRestrictQuantity(map.get("isRestrictQuantity") == null? 0: (Integer)map.get("isRestrictQuantity"));
|
matCodeEntity.setQuantity(map.get("Quantity") == null ? 0.00: Double.parseDouble(map.get("Quantity").toString()));
|
matCodeEntity.setRestrictBuyingQuantity(map.get("RestrictBuyingQuantity") == null ? 0.00: Double.parseDouble(map.get("RestrictBuyingQuantity").toString()));
|
matCodeEntity.setStartupPanicBuying(map.get("isStartupPanicBuying") != null && map.get("isStartupPanicBuying").equals(1)?true:false);
|
matCodeEntity.setPanicBuyingStartTime(map.get("PanicBuyingStartTime") == null ? null: (Date)map.get("PanicBuyingStartTime"));
|
matCodeEntity.setPanicBuyingEndTime(map.get("PanicBuyingEndTime") == null ? null: (Date)map.get("PanicBuyingEndTime"));
|
matCodeEntity.setPanicBuyingStartTimeBalance(map.get("PanicBuyingStartTimeBalance") == null ? 0L: Long.valueOf( map.get("PanicBuyingStartTimeBalance").toString()));
|
matCodeEntity.setPanicBuyingEndTimeBalance(map.get("PanicBuyingEndTimeBalance") == null ? 0L: Long.valueOf( map.get("PanicBuyingEndTimeBalance").toString()));
|
matCodeEntity.setStartupGroupBuying(map.get("isStartupGroupBuying") != null && map.get("isStartupGroupBuying").equals(1)?true:false);
|
matCodeEntity.setGroupBuyingMembers(map.get("GroupBuyingMembers") == null ? 0: (Integer)map.get("GroupBuyingMembers"));
|
matCodeEntity.setGroupBuyingPrice(map.get("GroupBuyingPrice") == null ? 0.00: Double.parseDouble(map.get("GroupBuyingPrice").toString()));
|
matCodeEntity.setGroupBuyingStartTime(map.get("GroupBuyingStartTime") == null ? null: (Date)map.get("GroupBuyingStartTime"));
|
matCodeEntity.setGroupBuyingEndTime(map.get("GroupBuyingEndTime") == null ? null: (Date)map.get("GroupBuyingEndTime"));
|
matCodeEntity.setGroupBuyingStartTimeBalance(map.get("GroupBuyingStartTimeBalance") == null ? 0L: Long.valueOf( map.get("GroupBuyingStartTimeBalance").toString()));
|
matCodeEntity.setGroupBuyingEndTimeBalance(map.get("GroupBuyingEndTimeBalance") == null ? 0L: Long.valueOf( map.get("GroupBuyingEndTimeBalance").toString()));
|
matCodeEntity.setGroupBuyingFailedToBacktrack(map.get("isGroupBuyingFailedToBacktrack") != null && map.get("isGroupBuyingFailedToBacktrack").equals(1)?true:false);
|
matCodeEntity.setMininum(map.get("Mininum") == null ? 0.00: Double.parseDouble(map.get("Mininum").toString()));
|
matCodeEntity.setSubtract(map.get("Subtract") == null ? 0: (Integer)map.get("Subtract"));
|
matCodeEntity.setStockStatusID(map.get("StockStatusID") == null ? 0: (Integer)map.get("StockStatusID"));
|
matCodeEntity.setStockStatusName(map.get("StockStatusName") == null ? "": (String)map.get("StockStatusName"));
|
matCodeEntity.setShipping(map.get("Shipping") == null ? 0: (Integer)map.get("Shipping"));
|
matCodeEntity.setKeyword(map.get("Keyword") == null ? "": (String)map.get("Keyword"));
|
matCodeEntity.setDateAvailable(map.get("DateAvailable") == null ? null: (Date)map.get("DateAvailable"));
|
matCodeEntity.setBaseUomLength(map.get("BaseUomLength") == null ? 0: (Integer)map.get("BaseUomLength"));
|
matCodeEntity.setBaseUomWidth(map.get("BaseUomWidth") == null ? 0: (Integer)map.get("BaseUomWidth"));
|
matCodeEntity.setBaseUomHeight(map.get("BaseUomHeight") == null ? 0: (Integer)map.get("BaseUomHeight"));
|
matCodeEntity.setLengthUom(map.get("LengthUom") == null ? "": (String)map.get("LengthUom"));
|
matCodeEntity.setBaseUomWeight(map.get("BaseUomWeight") == null ? 0.00 : Double.parseDouble(map.get("BaseUomWeight").toString()));
|
matCodeEntity.setWeightUom(map.get("WeightUom") == null ? "": (String)map.get("WeightUom"));
|
matCodeEntity.setStatus(map.get("Status") == null ? 0: (Integer)map.get("Status"));
|
matCodeEntity.setSortOrder(map.get("SortOrder") == null ? 0: (Integer)map.get("SortOrder"));
|
matCodeEntity.setPoints(map.get("Points") == null ? 0: (Integer)map.get("Points"));
|
matCodeEntity.setBrand(map.get("Brand") == null ? "": (String)map.get("Brand"));
|
matCodeEntity.setBrandId(map.get("BrandId") == null ? 0: (Integer)map.get("BrandId"));
|
matCodeEntity.setRatingAvg(map.get("RatingAvg") == null ? 0: (Integer)map.get("RatingAvg"));
|
matCodeEntity.setRatingCount(map.get("RatingCount") == null ? 0: (Integer)map.get("RatingCount"));
|
matCodeEntity.setTotalRowCount(map.get("TotalRowCount") == null ? 0: (Integer)map.get("TotalRowCount"));
|
matCodeEntity.setTotalPages(map.get("TotalPages") == null ? 0: (Integer)map.get("TotalPages"));
|
matCodeEntity.setShopMatCode(map.get("ShopMatCode") == null ? "": (String)map.get("ShopMatCode"));
|
matCodeEntity.setShopCcCode(map.get("ShopCcCode") == null ? "": (String)map.get("ShopCcCode"));
|
matCodeEntity.setShopCcName(map.get("ShopCcName") == null ? "": (String)map.get("ShopCcName"));
|
matCodeEntity.setShopBrand(map.get("ShopBrand") == null ? "": (String)map.get("ShopBrand"));
|
matCodeEntity.setExternalURL(map.get("ExternalURL") == null ? "": (String)map.get("ExternalURL"));
|
matCodeEntity.setEnterDate(map.get("EnterDate") == null ? null: (Date)map.get("EnterDate"));
|
matCodeEntity.setMatName2(map.get("MatName2") == null ? "": (String)map.get("MatName2"));
|
matCodeEntity.setMatName3(map.get("MatName3") == null ? "": (String)map.get("MatName3"));
|
matCodeEntity.setMatName4(map.get("MatName4") == null ? "": (String)map.get("MatName4"));
|
matCodeEntity.setQrCode(map.get("QrCode") == null ? "": (String)map.get("QrCode"));
|
matCodeEntity.setDepositAmount(map.get("DepositAmount") == null ? 0.00: Double.parseDouble(map.get("DepositAmount").toString()));
|
matCodeEntity.setItemMemo(map.get("ItemMemo") == null ? "": (String)map.get("ItemMemo"));
|
matCodeEntity.setDepositDocCode(map.get("DepositDocCode") == null ? "": (String)map.get("DepositDocCode"));
|
matCodeEntity.setDepositRowId(map.get("DepositRowId") == null ? "": (String)map.get("DepositRowId"));
|
matCodeEntity.setPaidDeposit(map.get("isPaidDeposit") != null && map.get("isPaidDeposit").equals(1)?true:false);
|
matCodeEntity.setPreSendMinutes(map.get("PreSendMinutes") == null ? 0: (Integer)map.get("PreSendMinutes"));
|
matCodeEntity.setPreSendMinutesDesc(map.get("PreSendMinutesDesc") == null ? "": (String)map.get("PreSendMinutesDesc"));
|
|
matCodeEntity.setGroupSortOrder(map.get("GroupSortOrder") == null ? "": (String)map.get("GroupSortOrder"));
|
matCodeEntity.setGroupDocCode(map.get("GroupDocCode") == null ? "": (String)map.get("GroupDocCode"));
|
matCodeEntity.setGroupName(map.get("GroupName") == null ? "": (String)map.get("GroupName"));
|
matCodeEntity.setGroupPhoto(map.get("GroupPhoto") == null ? "": (String)map.get("GroupPhoto"));
|
matCodeEntity.setGroupPhotoUrl(map.get("GroupPhotoUrl") == null ? "": (String)map.get("GroupPhotoUrl"));
|
matCodeEntity.setHorizontalScrolling(map.get("isHorizontalScrolling") != null&&map.get("isHorizontalScrolling").equals(1)?true:false);
|
matCodeEntity.setImageWidthWhenScrolling(map.get("ImageWidthWhenScrolling") == null?null: (Integer)map.get("ImageWidthWhenScrolling"));
|
matCodeEntity.setDocCode(map.get("DocCode") == null ? "": (String)map.get("DocCode"));
|
matCodeEntity.setCartQuantity(map.get("CartQuantity") == null ? 0: Double.parseDouble(map.get("CartQuantity").toString()));
|
matCodeEntity.setStockDigitForShoppingSaleable(map.get("StockDigitForShoppingSaleable") == null ? 0: Double.parseDouble(map.get("StockDigitForShoppingSaleable").toString()));
|
matCodeEntity.setStockDigitForSaleable(map.get("StockDigitForSaleable") == null ? 0: Double.parseDouble(map.get("StockDigitForSaleable").toString()));
|
matCodeEntity.setStockDigitForUsable(map.get("StockDigitForUsable") == null ? 0: Double.parseDouble(map.get("StockDigitForUsable").toString()));
|
matCodeEntity.setStartupSkuParameters(map.get("isStartupSkuParameters") != null&&map.get("isStartupSkuParameters").equals(1)?true:false);
|
matCodeEntity.setIsAllowExchangePointsQuantity(map.get("isAllowExchangePointsQuantity") == null ? 0 : (Integer) map.get("isAllowExchangePointsQuantity"));
|
if(map.get("ExchangePointsQuantity") == null){
|
matCodeEntity.setExchangePointsQuantity(0);
|
} else {
|
BigDecimal bigDecimal = (BigDecimal) map.get("ExchangePointsQuantity");
|
matCodeEntity.setExchangePointsQuantity(bigDecimal.intValue());
|
}
|
}
|
return matCodeEntity;
|
}
|
|
@Override
|
public int getMatCodeCountByMatGroup(String matGroup,String shopCcCode) {
|
if (matGroup == null) return 0 ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatGroup varchar(50) = "+GridUtils.prossSqlParm(matGroup)+" \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" \n"
|
+ " select count(1) as mycount \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and (isnull(@MatGroup,'') = '' or a.ShoppingMatGroups like '%;' + @MatGroup + ';%' ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
//+ " and exists (select 1 from t710104 b join t110501 e on b.MatGroup = e.MatGroup\n"
|
//+ " where a.DocCode = b.doccode \n"
|
//+ " and b.matgroup = @MatGroup and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode ) ) \n";
|
Integer ret = null ;
|
try {
|
ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return 0 ;
|
}else {
|
e.printStackTrace();
|
throw e ;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
if (ret == null)
|
return 0 ;
|
else
|
return ret ;
|
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByRelMat(String matCode,String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
if (matCode == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @MatCode varchar(50) = " +GridUtils.prossSqlParm(matCode) +" \n"
|
+ " declare @ShopCcCode varchar(50) =" +GridUtils.prossSqlParm(shopCcCode)+" ,@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+",@OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" \n"
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle,a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId , \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc,a.PurchasePrice, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and a.MatCode in (select b.RelMatCode from t710105 b \n"
|
+ " join t110503 c on b.doccode = c.doccode \n"
|
+ " where isnull(c.Status,0) = 1 \n"
|
+ " and c.MatCode = @MatCode \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or c.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(c.MatGroupStatus,0) = 1 ) \n";
|
//+ " and exists (select 1 from t710104 h join t110501 e on h.MatGroup = e.MatGroup \n"
|
//+ " where c.DocCode = h.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode ) ) ) \n" ;
|
if (this.whereOthers != null && !"".equals(this.whereOthers)) {
|
sql += " and (" + this.whereOthers + ") \n" ;
|
}
|
sql += " and isnull(a.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity+" \n";
|
sql += " order by case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i)));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByBestSeller(String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
String sql = "set nocount on ; \n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+",@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+" ,@OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" \n"
|
+ " select top 10 a.MatCode, b.MatName, b.Special, b.Description,b.MetaTitle, "
|
+ " b.MetaDescription, b.MetaKeyword, \n"
|
+ " b.Tag, b.PhotoPath,b.PhotoPathUrl,b.Images,b.ImagesUrl, b.BaseUOM, b.SalesUOM, b.Location, b.SalesPrice,\n"
|
+ " dbo.f710109(b.DocCode,@CltCode,b.SalesPrice) as Price, b.Quantity, b.Mininum, \n"
|
+ " b.isRestrictQuantity,b.RestrictBuyingQuantity,b.isStartupPanicBuying,b.PanicBuyingStartTime,b.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),b.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),b.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " b.isStartupGroupBuying,b.GroupBuyingMembers,b.GroupBuyingPrice,b.GroupBuyingStartTime,b.GroupBuyingEndTime,b.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),b.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),b.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " b.Subtract, b.StockStatusID, e.StockStatusName,b.Shipping, b.Keyword, b.DateAvailable, b.BaseUomLength, \n"
|
+ " b.BaseUomWidth, b.BaseUomHeight, b.LengthUom, b.BaseUomWeight, b.WeightUom, \n"
|
+ " b.Status, b.SortOrder, b.Points ,SUM(isnull(a.digit,0)) as SalesDigit , \n"
|
+ " b.Brand,d.BrandId, \n"
|
+ " b.RatingAvg,b.RatingCount,b.ShopMatCode,b.ShopCcCode,d.ShopBrand,b.ExternalURL,"
|
+ " f.CcName as ShopCcName,g.EnterDate,b.SalesPriceByPicture, \n"
|
+ " b.PreSendMinutes,dbo.f110503v2 (b.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " b.MatName2,b.MatName3,b.MatName4,h.CartQuantity,b.QrCode,\n"
|
+ " b.StockDigitForShoppingSaleable,b.StockDigitForSaleable,b.StockDigitForUsable,b.isStartupSkuParameters, \n"
|
+ " b.isAllowExchangePointsQuantity,b.ExchangePointsQuantity \n"
|
+ " from t120201D a join t110503 b on a.MatCode = b.MatCode \n"
|
+ " join t110504 d on a.Brand = d.Brand \n"
|
+ " left join t710119 e on b.StockStatusID = e.StockStatusID \n"
|
+ " left join t110601 f on b.ShopCcCode = f.CcCode \n"
|
+ " join t120201H g on a.DocCode = g.DocCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(b.inActive,0) = 0 \n"
|
+ " and ISNULL( b.Status,0) = 1 \n"
|
+ " and (b.DateAvailable is null or b.DateAvailable <= GETDATE() ) \n"
|
+ " and ISNULL(d.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(b.MatGroupStatus,0) = 1 \n"
|
+ " and isnull(b.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity+" \n"
|
//+ " and exists (select 1 from t710104 h join t110501 e on h.MatGroup = e.MatGroup \n"
|
//+ " where b.DocCode = h.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode ) ) \n"
|
+ " group by a.MatCode, b.MatName, b.Special, b.Description,b.MetaTitle, b.MetaDescription, b.MetaKeyword, \n"
|
+ " b.Tag, b.PhotoPath,b.PhotoPathUrl,b.Images,b.ImagesUrl, b.BaseUOM, b.SalesUOM, b.Location, b.SalesPrice,\n"
|
+ " dbo.f710109(b.DocCode,@CltCode,b.SalesPrice), b.Quantity, b.Mininum, \n"
|
+ " b.isRestrictQuantity,b.RestrictBuyingQuantity,b.isStartupPanicBuying,b.PanicBuyingStartTime,b.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),b.PanicBuyingStartTime) ,\n"
|
+ " DATEDIFF(second,getdate(),b.PanicBuyingEndTime) ,\n"
|
+ " b.isStartupGroupBuying,b.GroupBuyingMembers,b.GroupBuyingPrice,b.GroupBuyingStartTime,b.GroupBuyingEndTime,b.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),b.GroupBuyingStartTime) ,\n"
|
+ " DATEDIFF(second,getdate(),b.GroupBuyingEndTime) ,\n"
|
+ " b.Subtract, b.StockStatusID, e.StockStatusName,b.Shipping, b.Keyword, b.DateAvailable, b.BaseUomLength, \n"
|
+ " b.BaseUomWidth, b.BaseUomHeight, b.LengthUom, b.BaseUomWeight, b.WeightUom, \n"
|
+ " b.Status, b.SortOrder, b.Points , \n"
|
+ " b.Brand,d.BrandId, \n"
|
+ " b.RatingAvg,b.RatingCount,b.ShopMatCode,b.ShopCcCode,d.ShopBrand,b.ExternalURL, f.CcName ,g.EnterDate,b.SalesPriceByPicture, \n"
|
+ " b.PreSendMinutes,dbo.f110503v2 (b.PreSendMinutes) , \n"
|
+ " b.MatName2,b.MatName3,b.MatName4,h.CartQuantity,b.QrCode, \n"
|
+ " b.StockDigitForShoppingSaleable,b.StockDigitForSaleable,b.StockDigitForUsable,b.isStartupSkuParameters \n"
|
+ " order by SUM(isnull(a.digit,0)) desc ,a.MatCode asc \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql,shopCcCode,cltCode,openId) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
matCodeEntityList.add(getMatCodeEntity( list.get(i) ));
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByBrand(String brandId,int limit,int page,String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
if (brandId == null) return null ;
|
String sql = "set nocount on ; \n"
|
+ " declare @BrandId int = " + brandId +" ; \n"
|
+ " declare @Limit int = " + limit +" , @Page int ="+page+" ,@StartRowNo int ,@EndRowNo int ; \n"
|
+ " declare @TotalRowCount int ; \n"
|
+ " declare @ShopCcCode varchar(50) = " + GridUtils.prossSqlParm(shopCcCode) + " ,@CltCode varchar(20) = " +GridUtils.prossSqlParm(cltCode)+ ",@OpenId varchar(200) = " + GridUtils.prossSqlParm(openId) +" \n"
|
+ " select @TotalRowCount = count(1) \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and c.BrandId = @BrandId \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
//+ " and exists (select 1 from t710104 h join t110501 e on h.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = h.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode ) ) \n" ;
|
|
sql += " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,@TotalRowCount as TotalRowCount, \n"
|
+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
|
+ " a.MatCode,a.MatName,a.Special,a.Description, \n"
|
+ " a.MetaTitle,a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,\n"
|
+ " a.Points,a.Brand,c.BrandId , \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode ,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and c.BrandId = @BrandId \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
+ " and isnull(a.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity+" \n";
|
//+ " and exists (select 1 from t710104 h join t110501 e on h.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = h.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode ) ) \n" ;
|
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc ,a.MatCode asc") + " \n"
|
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
MatCodeEntity matCodeEntity = getMatCodeEntity( map);
|
matCodeEntityList.add(matCodeEntity);
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
|
@Override
|
public List<MatCodeEntity> getMatCodesBySpecialOffer(int limit,int page,String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity) {
|
String sql = "set nocount on ; \n"
|
+ " declare @Limit int =" + limit+" , @Page int =" + page +" ,@StartRowNo int ,@EndRowNo int ; \n"
|
+ " declare @ShopCcCode varchar(50) = " + GridUtils.prossSqlParm(shopCcCode) +" ,@CltCode varchar(20) = " + GridUtils.prossSqlParm(cltCode) +",@OpenId varchar(200) = " + GridUtils.prossSqlParm(openId) +" \n"
|
+ " declare @TotalRowCount int ; \n"
|
+ " select @TotalRowCount = count(1) \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and exists(select 1 from t710109 e where a.DocCode = e.DocCode \n"
|
+ " and convert(datetime,convert(varchar(10),getdate(),120)) between e.DateStart and e.DateEnd ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n";
|
//+ " and exists (select 1 from t710104 h join t110501 e on h.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = h.doccode and isnull(e.Status,0) = 1 "
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode ) ) \n" ;
|
|
sql += " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,@TotalRowCount as TotalRowCount, \n"
|
+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
|
+ " a.MatCode,a.MatName,a.Special,a.Description, \n"
|
+ " a.MetaTitle,a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,\n"
|
+ " a.Points,a.Brand,c.BrandId , \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t110503 a join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and exists(select 1 from t710109 e where a.DocCode = e.DocCode \n"
|
+ " and convert(datetime,convert(varchar(10),getdate(),120)) between e.DateStart and e.DateEnd ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
+ " and isnull(a.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity +"\n";
|
//+ " and exists (select 1 from t710104 h join t110501 e on h.MatGroup = e.MatGroup \n"
|
//+ " where a.DocCode = h.doccode and isnull(e.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or e.ShopCcCode = @ShopCcCode ) ) \n" ;
|
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n"
|
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
MatCodeEntity matCodeEntity = getMatCodeEntity( map);
|
matCodeEntityList.add(matCodeEntity);
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByShowGroup(String groupDocCode,String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity){
|
String sql = "set nocount on ; \n"
|
//+ " declare @Limit int =?, @Page int =? ,@StartRowNo int ,@EndRowNo int ; \n"
|
+ " declare @GroupDocCode varchar(50) = " +GridUtils.prossSqlParm(groupDocCode) + " \n"
|
+ " declare @ShopCcCode varchar(50) = "+ GridUtils.prossSqlParm(shopCcCode) + ",@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+",@OpenId varchar(200) = " + GridUtils.prossSqlParm(openId) +" \n"
|
|
|
/*
|
+ " declare @TotalRowCount int ; \n"
|
+ " select @TotalRowCount = count(1) \n"
|
+ " from t710126D e join t710126H f on e.DocCode = f.DocCode \n"
|
+ " join t110503 a on e.MatCode = a.MatCode \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
//+ " and f.DocCode = @GroupDocCode \n"
|
+ " and isnull(f.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and exists (select 1 from t710104 h join t110501 i on h.MatGroup = i.MatGroup \n"
|
+ " where a.DocCode = h.doccode and isnull(i.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or i.ShopCcCode = @ShopCcCode ) ) \n"
|
|
|
+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" */
|
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " cast(isnull(f.SortOrder,0) as varchar(10)) +'.'+ f.DocCode asc,case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,\n"
|
//+ " @TotalRowCount as TotalRowCount, \n"
|
//+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
+ " cast(isnull(f.SortOrder,0) as varchar(10)) +'.'+ f.DocCode as GroupSortOrder,f.DocCode as GroupDocCode, f.Name as GroupName,f.Photo as GroupPhoto,f.PhotoUrl as GroupPhotoUrl,f.isHorizontalScrolling,f.ImageWidthWhenScrolling , \n"
|
+ " a.MatCode,a.MatName,a.Special,a.Description, \n"
|
+ " a.MetaTitle,a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,\n"
|
+ " dbo.f710109(a.DocCode,@CltCode,a.SalesPrice) as Price,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,\n"
|
+ " a.Points,a.Brand,c.BrandId , \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " g.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t710126D e join t710126H f on e.DocCode = f.DocCode \n"
|
+ " join t110503 a on e.MatCode = a.MatCode \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 g on a.ShopCcCode = g.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@GroupDocCode,'') = '' or f.DocCode = @GroupDocCode ) \n"
|
+ " and isnull(f.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
+ " and isnull(a.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity +"\n";
|
//+ " and exists (select 1 from t710104 h join t110501 i on h.MatGroup = i.MatGroup \n"
|
//+ " where a.DocCode = h.doccode and isnull(i.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or i.ShopCcCode = @ShopCcCode ) ) \n" ;
|
|
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " cast(isnull(f.SortOrder,0) as varchar(10)) +'.'+ f.DocCode asc,case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n"
|
+ " ) t \n" ;
|
//+ " WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo ; \n";
|
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
MatCodeEntity matCodeEntity = getMatCodeEntity( map);
|
matCodeEntityList.add(matCodeEntity);
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByPrepaidDepositGroup(String prepaidDepositGroupDocCode,String shopCcCode,String cltCode,String openId,Integer isAllowExchangePointsQuantity){
|
String sql = "set nocount on ; \n"
|
//+ " declare @Limit int = ?, @Page int = ? ,@StartRowNo int ,@EndRowNo int ; \n"
|
+ " declare @GroupDocCode varchar(50) = " + GridUtils.prossSqlParm(prepaidDepositGroupDocCode) +" \n"
|
+ " declare @ShopCcCode varchar(50) = "+ GridUtils.prossSqlParm(shopCcCode) + ",@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+",@OpenId varchar(200) = " + GridUtils.prossSqlParm(openId) +" \n"
|
+ " declare @PostDocStatus int ; \n"
|
+ " select @PostDocStatus = PostDocStatus from gform where formid = 120238 \n"
|
/*+ " declare @TotalRowCount int ; \n"
|
+ " select @TotalRowCount = count(1) \n"
|
+ " from t710170D e join t710170H f on e.DocCode = f.DocCode \n"
|
+ " join t110503 a on e.MatCode = a.MatCode \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and f.DocCode = @GroupDocCode \n"
|
+ " and isnull(f.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and exists (select 1 from t710104 h join t110501 i on h.MatGroup = i.MatGroup \n"
|
+ " where a.DocCode = h.doccode and isnull(i.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or i.ShopCcCode = @ShopCcCode ) ) \n" ;
|
+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
*/
|
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY "+ (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " cast(isnull(f.SortOrder,0) as varchar(10)) +'.'+ f.DocCode asc,case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + ") AS NO,\n"
|
//+ " @TotalRowCount as TotalRowCount, \n"
|
//+ " case when isnull(@Limit,0) = 0 then 0 else cast(ceiling(isnull(@TotalRowCount,0)*1.0000 / isnull(@Limit,0)) as int) end as TotalPages, \n"
|
+ " cast(isnull(f.SortOrder,0) as varchar(10)) +'.'+ f.DocCode as GroupSortOrder,f.DocCode as GroupDocCode, f.Name as GroupName,f.Photo as GroupPhoto,f.PhotoUrl as GroupPhotoUrl,f.isHorizontalScrolling,f.ImageWidthWhenScrolling , \n"
|
+ " a.MatCode,a.MatName,a.Special,a.Description, \n"
|
+ " a.MetaTitle,a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum,\n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status, \n"
|
+ " case when a.SortOrder is null then 999999 else a.SortOrder end as SortOrder,\n"
|
+ " a.Points,a.Brand,c.BrandId , \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " g.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture ,\n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,e.DepositAmount,e.ItemMemo,\n"
|
+ " e.DocCode as DepositDocCode , e.RowId as DepositRowId, \n"
|
+ " case when exists(select 1 from t120238H t join t120238D p on t.doccode = p.doccode \n"
|
+ " where t.docstatus = @PostDocStatus and t.CltCode = @CltCode \n"
|
+ " and e.doccode = p.DepositDocCode and e.rowid = p.DepositRowId) \n"
|
+ " then 1 else 0 end isPaidDeposit,h.CartQuantity,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters, \n"
|
+ " a.isAllowExchangePointsQuantity,a.ExchangePointsQuantity \n"
|
+ " from t710170D e join t710170H f on e.DocCode = f.DocCode \n"
|
+ " join t110503 a on e.MatCode = a.MatCode \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 g on a.ShopCcCode = g.CcCode \n"
|
+ " left join (select MatCode,sum(isnull(Quantity,0)) as CartQuantity from t710205 where OpenId = @OpenId group by MatCode ) h on a.MatCode = h.MatCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@GroupDocCode,'') = '' or f.DocCode = @GroupDocCode ) \n"
|
+ " and isnull(f.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(a.MatGroupStatus,0) = 1 \n"
|
+ " and isnull(a.isAllowExchangePointsQuantity,0) = "+isAllowExchangePointsQuantity+"\n";
|
//+ " and exists (select 1 from t710104 h join t110501 i on h.MatGroup = i.MatGroup \n"
|
//+ " where a.DocCode = h.doccode and isnull(i.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or i.ShopCcCode = @ShopCcCode ) ) \n" ;
|
|
|
sql += " order by " + (this.getOrderBy()!=null&&! "".equals(this.getOrderBy())?this.getOrderBy()
|
: " cast(isnull(f.SortOrder,0) as varchar(10)) +'.'+ f.DocCode asc, case when a.SortOrder is null then 999999 else a.SortOrder end asc,a.MatCode asc ") + " \n"
|
+ " ) t \n" ;
|
//+ " WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo ; \n";
|
|
List<Map<String,Object>> list = null;
|
List<MatCodeEntity> matCodeEntityList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
matCodeEntityList = new ArrayList<MatCodeEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
MatCodeEntity matCodeEntity = getMatCodeEntity( map);
|
matCodeEntityList.add(matCodeEntity);
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return matCodeEntityList;
|
}
|
|
@Override
|
public List<ShowGroupNameEntity> getShowGroupNames(String shopCcCode) {
|
String sql = "set nocount on ; \n"
|
+ " declare @ShopCcCode varchar(50) = " + GridUtils.prossSqlParm(shopCcCode) + " \n"
|
+ " select distinct a.DocCode, a.Name,a.SortOrder,a.Photo,\n"
|
+ " a.isHorizontalScrolling,a.ImageWidthWhenScrolling \n"
|
+ " from t710126H a join t710126D b on a.DocCode= b.DocCode \n"
|
+ " join t110503 c on b.MatCode = c.MatCode \n"
|
+ " join t110504 d on c.Brand = d.Brand \n"
|
+ " where isnull(c.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and isnull(d.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and isnull(c.MatGroupStatus,0) = 1 \n"
|
//+ " and exists (select 1 from t710104 h join t110501 i on h.MatGroup = i.MatGroup \n"
|
//+ " where c.DocCode = h.doccode and isnull(i.Status,0) = 1 \n"
|
//+ " and (isnull(@ShopCcCode,'') = '' or i.ShopCcCode = @ShopCcCode ) ) \n"
|
+ " order by a.SortOrder asc,a.Name asc ; \n";
|
|
List<Map<String,Object>> list = null;
|
List<ShowGroupNameEntity> showGroupNameList = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
showGroupNameList = new ArrayList<ShowGroupNameEntity>() ;
|
for(int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
ShowGroupNameEntity showGroupNameEntity = new ShowGroupNameEntity( ) ;
|
showGroupNameEntity.setGroupName(map.get("Name") == null ? "": (String)map.get("Name"));
|
showGroupNameEntity.setPhoto(map.get("Photo") == null ? "": (String)map.get("Photo"));
|
showGroupNameEntity.setDocCode(map.get("DocCode") == null ? "": (String)map.get("DocCode"));
|
showGroupNameEntity.setHorizontalScrolling(map.get("isHorizontalScrolling")!=null&&((Integer)map.get("isHorizontalScrolling")).equals(1)?true:false);
|
showGroupNameEntity.setImageWidthWhenScrolling(map.get("ImageWidthWhenScrolling") == null ? 320: (Integer)map.get("ImageWidthWhenScrolling"));
|
showGroupNameList.add(showGroupNameEntity);
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return showGroupNameList;
|
}
|
|
@Override
|
public MatCodeEntity getMatCodeByBarcode(String barcode,String shopCcCode) {
|
String sql = " set nocount on ; \n"
|
+ " declare @barcode varchar(50) = "+ GridUtils.prossSqlParm(barcode) + " \n"
|
+ " declare @ShopCcCode varchar(50) = "+ GridUtils.prossSqlParm(shopCcCode) +" \n"
|
|
|
+ " select a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a \n"
|
+ " join t110501 b on a.MatGroup = b.MatGroup \n"
|
+ " join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and isnull(a.Status,0) = 1 \n"
|
+ " and isnull(b.Status,0) = 1 \n"
|
+ " and isnull(c.Status,0) = 1 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode ) \n"
|
+ " and (isnull(a.MatCode,'') = @barcode \n"
|
+ " or isnull(a.barcode,'') = @barcode ) \n" ;
|
Map<String,Object> map = null ;
|
try {
|
map = this.jdbcTemplate.queryForMap(sql) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
return getMatCodeEntity( map);
|
}
|
|
@Override
|
public String getQrCodeTypeCode(String action) {
|
String sql = " set nocount on ;\n"
|
+ " declare @QrCodeTypeCode varchar(50) , @Action varchar(50) = " + GridUtils.prossSqlParm(action) +" \n"
|
+ " if isnull(@Action,'') = '' \n"
|
+ " begin \n"
|
+ " raiserror('调用【getQrCodeTypeCode】方法时,必须传递Action参数',16,1) \n"
|
+ " return \n"
|
+ " end \n"
|
+ " select top 1 @QrCodeTypeCode = QrCodeTypeCode from t112001 where Action = @Action \n"
|
+ " if @@rowcount = 0 \n"
|
+ " begin \n"
|
+ " raiserror('没有找到二维码类别编号[%s],请在 112001 功能号中维护',16,1,@Action) \n"
|
+ " end \n"
|
+ " select @QrCodeTypeCode as QrCodeTypeCode \n" ;
|
try {
|
return this.jdbcTemplate.queryForObject(sql,String.class) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
@Override
|
public QrCodeTypeCodeForAppEntity getQrCodeForApp(QrCodeForAppEntity qrCodeForAppEntity) {
|
String sql = " set nocount on ;\n"
|
+ " declare @QrCode varchar(50) ,@QrCodeTypeCode varchar(20) ,@QrCodeUnid varchar(50),@BarCode varchar(50),@BarCodeUnid varchar(50) \n"
|
+ " declare @MatCode varchar(50) = " + GridUtils.prossSqlParm(qrCodeForAppEntity.getMatCode()) +",@RefDocCode varchar(20) = "+ GridUtils.prossSqlParm(qrCodeForAppEntity.getRefDocCode()) +" \n"
|
+ " declare @URL varchar(500) = "+ GridUtils.prossSqlParm(qrCodeForAppEntity.getUrl()) +" ,@SessionId varchar(50) = "+ GridUtils.prossSqlParm(qrCodeForAppEntity.getSessionId()) +" , @Action varchar(50) = "+ GridUtils.prossSqlParm(qrCodeForAppEntity.getAction()) +" \n"
|
// Action : ViewMaterial 物料编号二维码; ViewDocument 原始单据二维码 ;URL 打开URL网址 ;Login 登录二维码
|
+ " if isnull(@Action,'') = '' \n"
|
+ " begin \n"
|
+ " raiserror('调用【getQrCodeForApp】方法时,必须传递Action参数',16,1) \n"
|
+ " return \n"
|
+ " end \n"
|
+ " if isnull(@Action,'') = 'ViewMaterial' \n"
|
+ " begin \n"
|
+ " select @QrCode = a.QrCode,@QrCodeTypeCode = a.QrCodeTypeCode,@QrCodeUnid = a.QrCodeUnid,@BarCode =a.BarCode,@BarCodeUnid=a.BarCodeUnid from t112002 a where a.MatCode = @MatCode \n"
|
+ " end else \n"
|
+ " if isnull(@Action,'') = 'ViewDocument' \n"
|
+ " begin \n"
|
+ " select @QrCode = a.QrCode,@QrCodeTypeCode = a.QrCodeTypeCode,@QrCodeUnid = a.QrCodeUnid,@BarCode =a.BarCode,@BarCodeUnid=a.BarCodeUnid from t112002 a where a.RefDocCode = @RefDocCode \n"
|
+ " end else \n"
|
+ " if isnull(@Action,'') = 'URL' \n"
|
+ " begin \n"
|
+ " select @QrCode = a.QrCode,@QrCodeTypeCode = a.QrCodeTypeCode,@QrCodeUnid = a.QrCodeUnid,@BarCode =a.BarCode,@BarCodeUnid=a.BarCodeUnid from t112002 a where a.URL = @URL \n"
|
+ " end \n"
|
+ " else if isnull(@Action,'') = 'Login' \n"
|
+ " begin \n"
|
+ " select @QrCode = a.QrCode,@QrCodeTypeCode = a.QrCodeTypeCode,@QrCodeUnid = a.QrCodeUnid,@BarCode =a.BarCode,@BarCodeUnid=a.BarCodeUnid from t112002 a where a.SessionId = @SessionId \n"
|
+ " end \n"
|
+ " select isnull(@QrCode,'') as QrCode,@QrCodeTypeCode as QrCodeTypeCode,@QrCodeUnid as QrCodeUnid,@BarCode as BarCode,@BarCodeUnid as BarCodeUnid \n" ;
|
try {
|
Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ;
|
QrCodeTypeCodeForAppEntity qrCodeTypeCodeForAppEntity = new QrCodeTypeCodeForAppEntity() ;
|
if (map != null) {
|
qrCodeTypeCodeForAppEntity.setQrCode((String)map.get("QrCode"));
|
qrCodeTypeCodeForAppEntity.setQrCodeTypeCode((String)map.get("QrCodeTypeCode"));
|
qrCodeTypeCodeForAppEntity.setQrCodeUnid((String)map.get("QrCodeUnid"));
|
qrCodeTypeCodeForAppEntity.setBarCode((String)map.get("BarCode"));
|
qrCodeTypeCodeForAppEntity.setBarCodeUnid((String)map.get("BarCodeUnid"));
|
}
|
return qrCodeTypeCodeForAppEntity ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
|
@Override
|
public QrCodeForAppEntity getQrCodeEntity(String qrCode) {
|
String sql = " set nocount on ;\n"
|
+ " declare @QrCode varchar(50) = "+GridUtils.prossSqlParm(qrCode)+" \n"
|
+ " declare @table table(QrCode varchar(50),QrCodeTypeCode varchar(20),QrCodeUnid varchar(50), \n"
|
+ " MatCode varchar(50),RefDocCode varchar(20),URL varchar(500),Action varchar(50)) \n"
|
+ " insert into @table(QrCode,QrCodeTypeCode,QrCodeUnid,MatCode,RefDocCode,URL,Action) \n"
|
+ " select a.QrCode,a.QrCodeTypeCode,a.QrCodeUnid,a.MatCode,a.RefDocCode,a.URL,b.Action \n"
|
+ " from t112002 a \n"
|
+ " join t112001 b on a.QrCodeTypeCode = b.QrCodeTypeCode \n"
|
+ " where a.QrCode = @QrCode \n" //二维码
|
+ " if @@rowcount = 0 \n"
|
+ " begin \n"
|
+ " insert into @table(QrCode,QrCodeTypeCode,QrCodeUnid,MatCode,RefDocCode,URL,Action) \n"
|
+ " select a.QrCode,a.QrCodeTypeCode,a.QrCodeUnid,a.MatCode,a.RefDocCode,a.URL,b.Action \n"
|
+ " from t112002 a \n"
|
+ " join t112001 b on a.QrCodeTypeCode = b.QrCodeTypeCode \n"
|
+ " where a.BarCode = @QrCode \n"
|
+ " if @@rowcount = 0 \n"
|
+ " begin \n" //条码
|
+ " insert into @table(QrCode,QrCodeTypeCode,QrCodeUnid,MatCode,RefDocCode,URL,Action) \n"
|
+ " select a.SerialNumber,'2001' as QrCodeTypeCode,'' as QrCodeUnid,a.MatCode,'' as RefDocCode,'' as URL,'' as Action \n"
|
+ " from t140906 a \n"
|
+ " where a.SerialNumber = @QrCode \n"
|
+ " end \n" //序列号码
|
+ " end \n"
|
+ " select top 1 QrCode,QrCodeTypeCode,QrCodeUnid,MatCode,RefDocCode,URL,Action \n"
|
+ " from @table \n";
|
|
try {
|
Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ;
|
QrCodeForAppEntity qrCodeForAppEntity = new QrCodeForAppEntity() ;
|
if (map != null) {
|
qrCodeForAppEntity.setQrCode((String)map.get("QrCode"));
|
qrCodeForAppEntity.setQrCodeTypeCode((String)map.get("QrCodeTypeCode"));
|
qrCodeForAppEntity.setQrCodeUnid((String)map.get("QrCodeUnid"));
|
qrCodeForAppEntity.setMatCode((String)map.get("MatCode"));
|
qrCodeForAppEntity.setRefDocCode((String)map.get("RefDocCode"));
|
qrCodeForAppEntity.setUrl((String)map.get("URL"));
|
qrCodeForAppEntity.setAction((String)map.get("Action"));
|
}
|
return qrCodeForAppEntity ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
@Override
|
public Integer saveMatCodeQrCodeForApp(QrCodeForAppEntity matCodeQrCodeForAppEntity) throws Exception {
|
String sql = " set nocount on ;\n"
|
+ " declare @QrCode varchar(50) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getQrCode())+",@QrCodeTypeCode varchar(20) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getQrCodeTypeCode())+",@QrCodeUnid varchar(50) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getQrCodeUnid())+" ,@BarCode varchar(50) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getBarCode())+",@BarCodeUnid varchar(50) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getBarCodeUnid())+" \n"
|
+ " declare @MatCode varchar(50) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getMatCode())+",@RefFormId int = "+matCodeQrCodeForAppEntity.getRefFormId()+",@RefFormType int = "+matCodeQrCodeForAppEntity.getRefFormType()+",@RefDocCode varchar(20) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getRefDocCode())+" \n"
|
+ " declare @URL varchar(500) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getUrl())+",@AuthorCode varchar(50) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getAuthorCode())+",@AuthorName varchar(50) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getAuthorName())+" ,@Action varchar(50) = "+GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getAction())+"\n"
|
+ " declare @myrowcount int \n"
|
|
+ " declare @UserCode varchar(50) = " +GridUtils.prossSqlParm(matCodeQrCodeForAppEntity.getAuthorCode())+",@QueryString nvarchar(max),@DocStatusName nvarchar(100) ,@DocType nvarchar(1000)\n"
|
+ " declare @Curchecker varchar(4000),@CurcheckerName varchar(4000),@DstTable varchar(80),@DocCode varchar(50) ,@FormId int\n"
|
+ " declare @sql varchar(2000) \n"
|
|
+ " if isnull(@Action,'') = 'ViewDocument' \n"
|
+ " begin \n"
|
+ " exec p112002V1 @UserCode = @UserCode,@FormId = @RefFormId,@DocCode = @RefDocCode,@QueryString =@QueryString output,\n"
|
+ " @DocStatusName = @DocStatusName output,@DocType = @DocType output,@Curchecker = @Curchecker output , \n"
|
+ " @CurcheckerName = @CurcheckerName output,@DstTable = @DstTable output \n"
|
+ " end \n"
|
|
+ " update a set QrCodeUnid = @QrCodeUnid,QueryString = @QueryString,BarCode = @BarCode,BarCodeUnid = @BarCodeUnid from t112002 a where QrCode = @QrCode \n"
|
+ " set @myrowcount = @@rowcount \n"
|
+ " if isnull(@myrowcount,0) = 0 \n"
|
+ " begin \n"
|
+ " insert into t112002 (QrCode,QrCodeTypeCode,QrCodeUnid,MatCode,RefFormId,RefFormType,RefDocCode,URL,CreateTime,AuthorCode,AuthorName,QueryString,BarCode,BarCodeUnid) \n"
|
+ " values(@QrCode,@QrCodeTypeCode,@QrCodeUnid,@MatCode,@RefFormId,@RefFormType,@RefDocCode,@URL,getdate(),@AuthorCode,@AuthorName,@QueryString ,@BarCode,@BarCodeUnid) \n"
|
+ " set @myrowcount = @@rowcount \n"
|
+ " if isnull(@Action,'') = 'ViewDocument' and isnull(@DstTable,'')<> '' \n"
|
+ " begin \n"
|
+ " if not exists(select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @DstTable and COLUMN_NAME = 'QrCode') \n"
|
+ " begin \n"
|
+ " set @sql = ' alter table ' + @DstTable + ' add QrCode varchar(50) '\n"
|
+ " exec(@sql) \n"
|
+ " end \n"
|
+ " set @sql = ' update a set QrCode = ''' + @QrCode + ''' from ' + @DstTable + ' a where DocCode = ''' + @RefDocCode + '''' \n"
|
+ " exec(@sql) \n"
|
+ " end \n"
|
+ " if isnull(@Action,'') = 'ViewMaterial' and isnull(@MatCode,'') <> '' \n"
|
+ " begin \n"
|
+ " update a set QrCode = @QrCode from t110503 a where a.MatCode = @MatCode \n"
|
+ " end \n"
|
+ " end\n"
|
+ " select isnull(@myrowcount,0) as myrowcount \n";
|
try {
|
return this.jdbcTemplate.queryForObject(sql,Integer.class) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
|
}
|
|
@Override
|
public Integer saveMatCodeQrCode(int dbId,File file,String matCode,HttpServletRequest request) throws Exception {
|
//HttpSession session = request.getSession(true);
|
Integer ret = null ;
|
String sql = "" ;
|
int formid = 710103;
|
String rowid = matCode ;
|
String doccode = matCode ;
|
String fieldid = "QrCode" ; //永久二维码图片
|
String uuid = UUID.randomUUID().toString().toUpperCase();//生成uuid
|
//String filePath = session.getServletContext().getRealPath("/") + "uploads"+File.separator+"smallpic"+File.separator ;
|
//String filePath=System.getProperty("java.io.tmpdir"); //临时目录
|
// Square_logo_url 源文件二进制 文件和缩略图
|
UploadFiles uploadFiles = null ;
|
String fileExName = null ;
|
|
if (file != null) {
|
uploadFiles = AvatarFile.getImages(file) ;
|
fileExName = FileExtensionName.getFileExtensionName(file.getName());
|
}
|
|
//下面的SQL语句用于处理微信个人图像
|
sql += " set nocount on ;\n"
|
+ " declare @matcode varchar(80)="+GridUtils.prossSqlParm(matCode)+", @unid varchar(200), @newUnid varchar(200)="+GridUtils.prossSqlParm(uuid)+" ,@seq int ; \n"
|
+ " declare @formid int ="+formid+", @doccode varchar(50)="+GridUtils.prossSqlParm(doccode)+",@rowid varchar(50)="+GridUtils.prossSqlParm(rowid)+",@fieldid varchar(50)="+GridUtils.prossSqlParm(fieldid) +" ;\n"
|
+ " declare @isFound int =0 , @SmallPicture varbinary(max),@OriginalPicture varbinary(max) \n"
|
+ " declare @myrowcount int ,@myerror int \n"
|
+ " declare @PhysicalFile varchar(200)="+GridUtils.prossSqlParm((file==null?null:file.getName()))+",@OriginalFileName varchar(200) ="+GridUtils.prossSqlParm((file==null?null:file.getName())) +" ; \n"
|
+ " declare @FileSize bitint="+(file==null?null:file.length())+" ,@FileType varchar(50) ="+GridUtils.prossSqlParm((fileExName==null?null:fileExName)) +" ; \n";
|
if (uploadFiles!= null) {
|
sql += " set @SmallPicture = "+UploadFiles.byteToHexString(uploadFiles.getSmallBinaryimages())+" \n"
|
+ " set @OriginalPicture = "+UploadFiles.byteToHexString(uploadFiles.getOrgBinaryimages())+" \n";
|
}
|
|
sql += " select @unid = QrCode,@doccode = doccode ,@rowid = doccode from t110503 where matcode = @matcode \n"
|
+ " select @myrowcount = @@rowcount,@myerror = @@error \n"
|
+ " if isnull(@doccode,'') = '' set @doccode = @rowid ; \n"
|
+ " if @myrowcount > 0 select @isFound = 1; \n"
|
+ " if @isFound = 1 \n"
|
+ " begin \n"
|
+ " if isnull(@unid,'') <> '' and exists (select top 1 1 from _sys_Attachment9 where unid= @unid ) \n"
|
+ " begin \n"
|
+ " update a set SmallPicture = @SmallPicture , OriginalPicture = @OriginalPicture, \n"
|
+ " LastUpdateTime = getdate(),FileSize = @FileSize,FileType=@FileType,LastFileSize=FileSize,LastFileType=FileType \n"
|
+ " from _sys_Attachment9 a where unid = @unid ; \n"
|
+ " end else \n"
|
+ " begin \n"
|
+ " select top 1 @unid = UNID from _sys_Attachment9 \n"
|
+ " where formid = @formid and doccode = @doccode and fieldid = @fieldid and rowid = @rowid \n"
|
+ " if @@rowcount > 0 \n"
|
+ " begin \n"
|
+ " update a set SmallPicture = @SmallPicture , OriginalPicture = @OriginalPicture ,\n"
|
+ " LastUpdateTime = getdate(),FileSize = @FileSize,FileType=@FileType,LastFileSize=FileSize,LastFileType=FileType \n"
|
+ " from _sys_Attachment9 a where unid = @unid ; \n"
|
+ " end else \n"
|
+ " begin \n"
|
+ " select @unid = @newUnid ; \n"
|
+ " insert into _sys_Attachment9 (UNID,formid,fieldid,DocCode,rowid, \n"
|
+ " PhysicalFile,OriginalFileName,FileSize,FileType, \n"
|
+ " AuthorCode,AuthorName, \n"
|
+ " UploadTime,LastUpdateTime,SmallPicture,OriginalPicture) \n"
|
+ " values (@unid,@formid,@fieldid,@doccode,@rowid, \n"
|
+ " @PhysicalFile,@OriginalFileName,@FileSize,@FileType, \n"
|
+ " '','', \n"
|
+ " getdate(),getdate(),@SmallPicture,@OriginalPicture) ; \n"
|
+ " end \n"
|
+ " end ; \n"
|
+ " select @seq = seq from _sys_Attachment9 where unid = @unid \n"
|
+ " update a set QrCode = @unid + ';' + cast(isnull(@seq,0) as varchar(20)) \n"
|
+ " from t110503 a where matcode = @matcode ; \n"
|
+ " end ; \n"
|
+ " select @isFound ; \n";
|
return this.jdbcTemplate.queryForObject(sql,Integer.class);
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesForGenerationQrCode(String shopCcCode,String brand, String matGroup,String matCode) {
|
String sql = " set nocount on ;\n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+",@Brand varchar(50) = "+GridUtils.prossSqlParm(brand)+",@MatGroup varchar(50) = "+GridUtils.prossSqlParm(matGroup)+",@MatCode varchar(50) = "+GridUtils.prossSqlParm(matCode)+" \n"
|
+ " select a.BarCode,a.MatCode,a.MatName,a.Special,a.ShopMatCode,a.QrCode \n"
|
+ " from t110503 a \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode or a.ShopCcCode in (select list from getinstr(@ShopCcCode)) ) \n"
|
+ " and (isnull(@Brand,'') = '' or a.Brand= @Brand or a.Brand in (select list from getinstr(@Brand)) ) \n"
|
+ " and (isnull(@MatGroup,'') = '' or a.MatGroup= @MatGroup or a.MatGroup in (select list from getinstr(@MatGroup))) \n"
|
+ " and (isnull(@MatCode,'') = '' or a.MatCode= @MatCode or a.MatCode in (select list from getinstr(@MatCode)) ) \n" ;
|
List<Map<String,Object>> list = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
List<MatCodeEntity> matCodeList = new ArrayList<MatCodeEntity>() ;
|
for (int i = 0 ;list != null && i < list.size();i++) {
|
matCodeList.add(getMatCodeEntity( list.get(i)));
|
}
|
return matCodeList ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
@Override
|
public List<MatCodeEntity> getMatCodesByMatCode(String shopCcCode,String brand, String matGroup,String matCode) {
|
String sql = " set nocount on ;\n"
|
+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+",@Brand varchar(50) = "+GridUtils.prossSqlParm(brand)+",@MatGroup varchar(50) = "+GridUtils.prossSqlParm(matGroup)+",@MatCode varchar(50) = "+GridUtils.prossSqlParm(matCode)+" \n"
|
|
+ " select a.BarCode,a.MatCode,a.MatName,a.Special,a.Description,a.MetaTitle, \n"
|
+ " a.MetaDescription,a.MetaKeyword, \n"
|
+ " a.Tag,a.PhotoPath,a.PhotoPathUrl,a.Images,a.ImagesUrl,a.BaseUOM,a.SalesUOM,a.Location,a.SalesPrice,a.Quantity,a.Mininum, \n"
|
+ " a.isRestrictQuantity,a.RestrictBuyingQuantity,a.isStartupPanicBuying,a.PanicBuyingStartTime,a.PanicBuyingEndTime,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingStartTime) as PanicBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.PanicBuyingEndTime) as PanicBuyingEndTimeBalance,\n"
|
+ " a.isStartupGroupBuying,a.GroupBuyingMembers,a.GroupBuyingPrice,a.GroupBuyingStartTime,a.GroupBuyingEndTime,a.isGroupBuyingFailedToBacktrack,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingStartTime) as GroupBuyingStartTimeBalance,\n"
|
+ " DATEDIFF(second,getdate(),a.GroupBuyingEndTime) as GroupBuyingEndTimeBalance,\n"
|
+ " a.Subtract,a.StockStatusID,d.StockStatusName,a.Shipping,a.Keyword,a.DateAvailable,a.BaseUomLength, \n"
|
+ " a.BaseUomWidth,a.BaseUomHeight,a.LengthUom,a.BaseUomWeight,a.WeightUom,a.Status,\n"
|
+ " a.SortOrder, \n"
|
+ " a.Points,a.Brand,c.BrandId, \n"
|
+ " a.RatingAvg,a.RatingCount,a.ShopMatCode,a.ShopCcCode,c.ShopBrand,a.ExternalURL,\n"
|
+ " e.CcName as ShopCcName,a.EnterDate,a.SalesPriceByPicture, \n"
|
+ " a.PreSendMinutes,dbo.f110503v2 (a.PreSendMinutes) as PreSendMinutesDesc, \n"
|
+ " a.MatName2,a.MatName3,a.MatName4,a.QrCode,\n"
|
+ " a.StockDigitForShoppingSaleable,a.StockDigitForSaleable,a.StockDigitForUsable,a.isStartupSkuParameters \n"
|
+ " from t110503 a \n"
|
+ " join t110501 b on a.MatGroup = b.MatGroup \n"
|
+ " left join t110504 c on a.Brand = c.Brand \n"
|
+ " left join t710119 d on a.StockStatusID = d.StockStatusID \n"
|
+ " left join t110601 e on a.ShopCcCode = e.CcCode \n"
|
+ " where isnull(a.inActive,0) = 0 \n"
|
+ " and (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode or a.ShopCcCode in (select list from getinstr(@ShopCcCode)) ) \n"
|
+ " and (isnull(@Brand,'') = '' or a.Brand= @Brand or a.Brand in (select list from getinstr(@Brand)) ) \n"
|
+ " and (isnull(@MatGroup,'') = '' or a.MatGroup= @MatGroup or a.MatGroup in (select list from getinstr(@MatGroup))) \n"
|
+ " and (isnull(@MatCode,'') = '' or a.MatCode= @MatCode or a.MatCode in (select list from getinstr(@MatCode)) ) \n" ;
|
List<Map<String,Object>> list = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
List<MatCodeEntity> matCodeList = new ArrayList<MatCodeEntity>() ;
|
for (int i = 0 ;list != null && i < list.size();i++) {
|
matCodeList.add(getMatCodeEntity( list.get(i)));
|
}
|
return matCodeList ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
}
|