eCoWorksV3.git - Gitblit

xinyb

2024-07-18 80430d52f7716e3eb3e517dc96f94c4a6f9657bf

提交 \| 用户 \| age
a6a76f	1	package com.yc.sdk.shopping.service;
F	2
	3	import java.util.ArrayList;
	4	import java.util.List;
	5	import java.util.Map;
	6
	7	import org.springframework.context.annotation.Scope;
	8	import org.springframework.dao.DataAccessException;
	9	import org.springframework.dao.EmptyResultDataAccessException;
	10	import org.springframework.stereotype.Service;
	11
a92f52	12	import com.yc.action.grid.GridUtils;
a6a76f	13	import com.yc.exception.ApplicationException;
F	14	import com.yc.sdk.shopping.entity.TransCostsEntity;
	15	import com.yc.sdk.shopping.entity.FreeEntity;
	16	import com.yc.service.BaseService;
	17	@Service("FreeImpl")
	18	@Scope("prototype")
	19	public class FreeImpl extends BaseService implements FreeIfc {
	20
	21	@Override
	22	public List<FreeEntity> getFreeList() {
	23	String sql = " set nocount on ; \n"
32e2ad	24	+ " select a.FreeId,a.FreeName,a.FlatCost, a.FreeTotal,a.SortOrder,a.isRequiredAddress \n"
a6a76f	25	+ " from t714034 a where ISNULL(a.Status,0) = 1 \n"
F	26	+ " order by a.SortOrder,a.FreeName,a.FreeId \n" ;
	27	List<Map<String,Object>> list = null ;
	28	//Map<String,Object> map = null ;
	29	List<FreeEntity> freeList = new ArrayList<FreeEntity>() ;
	30	try{
	31	list = this.jdbcTemplate.queryForList(sql) ;
	32	for (Map<String,Object> map:list) {
	33	FreeEntity freeEntity = new FreeEntity() ;
	34	freeEntity.setFreeId(map.get("FreeId") == null ? 0 : (Integer)map.get("FreeId"));
	35	freeEntity.setFreeName(map.get("FreeName") == null ? "" : (String)map.get("FreeName"));
	36	freeEntity.setFlatCost(map.get("FlatCost") == null ? 0 : (Double.parseDouble(map.get("FlatCost").toString())));
	37	freeEntity.setFreeTotal(map.get("FreeTotal") == null ? 0 : (Double.parseDouble(map.get("FreeTotal").toString())));
	38	freeEntity.setSortOrder(map.get("SortOrder") == null ? 0 : (Integer)map.get("SortOrder"));
	39	freeEntity.setRequiredAddress(map.get("isRequiredAddress") != null&& map.get("isRequiredAddress").equals(1)? true:false);
	40
	41	freeList.add(freeEntity);
	42	}
	43	}catch(DataAccessException e ) {
	44	if (e instanceof EmptyResultDataAccessException){
	45	return null ;
	46	}else {
	47	e.printStackTrace();
	48	throw e;
	49	}
	50	}catch(Exception e){
	51	e.printStackTrace();
	52	throw e;
	53	}
	54
	55	return freeList;
	56	}
	57
	58	@Override
7313e3	59	public FreeEntity getFreeEntity(Integer freeId) {
J	60	String sql = " set nocount on ; \n"
a92f52	61	+ " declare @freeId int = "+freeId+"\n"
7313e3	62	+ " select top 1 a.FreeId,a.FreeName,a.FlatCost, a.FreeTotal,a.SortOrder,a.isRequiredAddress \n"
4284f7	63	+ " from t714034 a where a.FreeId = @FreeId \n"
7313e3	64	+ " order by a.SortOrder,a.FreeName,a.FreeId \n" ;
J	65	try{
a92f52	66	Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ;
7313e3	67	if (map != null) {
J	68	FreeEntity freeEntity = new FreeEntity() ;
	69	freeEntity.setFreeId(map.get("FreeId") == null ? 0 : (Integer)map.get("FreeId"));
	70	freeEntity.setFreeName(map.get("FreeName") == null ? "" : (String)map.get("FreeName"));
	71	freeEntity.setFlatCost(map.get("FlatCost") == null ? 0 : (Double.parseDouble(map.get("FlatCost").toString())));
	72	freeEntity.setFreeTotal(map.get("FreeTotal") == null ? 0 : (Double.parseDouble(map.get("FreeTotal").toString())));
	73	freeEntity.setSortOrder(map.get("SortOrder") == null ? 0 : (Integer)map.get("SortOrder"));
	74	freeEntity.setRequiredAddress(map.get("isRequiredAddress") != null&& map.get("isRequiredAddress").equals(1)? true:false);
	75
	76	return freeEntity;
	77	}
	78	}catch(DataAccessException e ) {
	79	if (e instanceof EmptyResultDataAccessException){
	80	return null ;
	81	}else {
	82	e.printStackTrace();
	83	throw e;
	84	}
	85	}catch(Exception e){
	86	e.printStackTrace();
	87	throw e;
	88	}
	89
	90	return null;
	91	}
	92
	93	@Override
a6a76f	94	public List<FreeEntity> getFree(String userCode,String sessionId,Integer countryId, Integer zoneId, String postcode) {
F	95	String sql = " set nocount on ; \n"
a92f52	96	+ " declare @UserCode varchar(50)="+GridUtils.prossSqlParm(userCode)+",@SessionId varchar(200)="+GridUtils.prossSqlParm(sessionId)+", @TotalAmount money ,@CountryId int="+countryId+",@ZoneId int="+zoneId+" \n"
a6a76f	97	+ " select @TotalAmount = SUM(isnull(Amount,0)) \n"
F	98	+ " from t710205 \n"
	99	+ " where UserCode=@UserCode or SessionId = @SessionId \n"
	100
	101	+ " select a.FreeId,a.FreeName, case when isnull(@TotalAmount,0) - ISNULL(a.FreeTotal,0) >= 0 then 0 else FlatCost end as FlatCost, a.FreeTotal,a.SortOrder \n"
	102	+ " from t714034 a where isnull(a.GroupId,0) = 0 and ISNULL(a.Status,0) = 1 \n"
	103
	104	+ " union all \n"
	105	+ " select a.FreeId,a.FreeName, case when isnull(@TotalAmount,0) - ISNULL(a.FreeTotal,0) >= 0 then 0 else FlatCost end as FlatCost,a.FreeTotal,a.SortOrder \n"
	106	+ " from t714034 a join t714033 b on a.GroupId = b.GroupId \n"
	107	+ " where b.CountryId = @CountryId and b.ZoneId = 0 and ISNULL(a.Status,0) = 1 \n"
	108
	109	+ " union all \n"
	110	+ " select a.FreeId,a.FreeName,case when isnull(@TotalAmount,0) - ISNULL(a.FreeTotal,0) >= 0 then 0 else FlatCost end as FlatCost,a.FreeTotal,a.SortOrder \n"
	111	+ " from t714034 a join t714033 b on a.GroupId = b.GroupId \n"
	112	+ " where b.CountryId = @CountryId and b.ZoneId = @ZoneId and ISNULL(a.Status,0) = 1 \n"
	113	+ " order by a.SortOrder,a.FreeName,a.FreeId \n" ;
	114
	115	List<Map<String,Object>> list = null ;
	116	//Map<String,Object> map = null ;
	117	List<FreeEntity> freeList = new ArrayList<FreeEntity>() ;
	118	try{
a92f52	119	list = this.jdbcTemplate.queryForList(sql) ;
a6a76f	120	for (Map<String,Object> map:list) {
F	121	FreeEntity freeEntity = new FreeEntity() ;
	122	freeEntity.setFreeId(map.get("FreeId") == null ? 0 : (Integer)map.get("FreeId"));
	123	freeEntity.setFreeName(map.get("FreeName") == null ? "" : (String)map.get("FreeName"));
	124	freeEntity.setFlatCost(map.get("FlatCost") == null ? 0 : (Double.parseDouble(map.get("FlatCost").toString())));
	125	freeEntity.setFreeTotal(map.get("FreeTotal") == null ? 0 : (Double.parseDouble(map.get("FreeTotal").toString())));
	126	freeEntity.setSortOrder(map.get("SortOrder") == null ? 0 : (Integer)map.get("SortOrder"));
	127
	128	freeList.add(freeEntity);
	129	}
	130	}catch(DataAccessException e ) {
	131	if (e instanceof EmptyResultDataAccessException){
	132	return null ;
	133	}else {
	134	e.printStackTrace();
	135	throw e;
	136	}
	137	}catch(Exception e){
	138	e.printStackTrace();
	139	throw e;
	140	}
	141
	142	return freeList;
	143	}
	144
	145	@Override
	146	public TransCostsEntity getTransCostsEntity(Integer paymentAddressSeq,String cltCode,Integer freeId,double selectCartAmount) {
	147	String sql = " set nocount on ; \n"
a92f52	148	+ " declare @paymentAddressSeq int = "+paymentAddressSeq+" ,@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+",@FreeId int = "+freeId+" , @SelectCartAmount money = "+selectCartAmount +" \n"
a6a76f	149	+ " declare @Longitude varchar(50),@Latitude varchar(50),@Longitude2 varchar(50),@Latitude2 varchar(50) \n"
F	150	+ " declare @TransDistance int,@TransCosts money,@FreeName varchar(50) \n"
	151	+ " if isnull(@paymentAddressSeq,0) = 0 \n"
	152	+ " begin \n"
	153	+ " select @Longitude = Longitude,@Latitude = Latitude from t110203 where cltcode = @cltcode \n"
	154	+ " end else \n"
	155	+ " begin \n"
	156	+ " select @Longitude = Longitude,@Latitude = Latitude from t110209 where cltcode = @cltcode and seq = @paymentAddressSeq \n"
	157	+ " end \n"
	158	+ " select top 1 @Longitude2 = Longitude,@Latitude2 = Latitude from ocompany \n"
	159	+ " if isnull(@Longitude,'') <> '' and isnull(@Latitude,'') <> '' \n"
	160	+ " and isnull(@Longitude2,'') <> '' and isnull(@Latitude2,'') <> '' \n"
	161	+ " begin \n"
	162	+ " select @TransDistance = dbo.f180251v100(cast(@Longitude as decimal(15,9)),cast(@Latitude as decimal(15,9)) ,cast(@Longitude2 as decimal(15,9)),cast(@Latitude2 as decimal(15,9))) \n"
	163	+ " end \n"
	164	+ " if exists(select top 1 1 from t714035 where FreeId = @FreeId) \n"
	165	+ " begin \n"
	166	+ " select @TransCosts = case when isnull(@SelectCartAmount,0)> isnull(b.FreeTotal,0) then 0 else a.TransCosts end , \n"
	167	+ " @FreeName = b.FreeName \n"
	168	+ " from t714035 a join t714034 b on a.FreeId = b.FreeId \n"
	169	+ " where a.FreeId = @FreeId and @TransDistance between a.DistanceStart and a.DistanceEnd \n"
	170	+ " end else \n"
	171	+ " begin \n"
	172	+ " select @TransCosts = case when isnull(@SelectCartAmount,0) - ISNULL(a.FreeTotal,0) >= 0 then 0 else FlatCost end, \n"
	173	+ " @FreeName = a.FreeName \n"
	174	+ " from t714034 a \n"
	175	+ " where a.FreeId = @FreeId \n"
	176	+ " end \n"
	177	+ " select isnull(@FreeName,'') as FreeName, isnull(@TransDistance,0) as TransDistance,isnull(@TransCosts,0) as TransCosts \n";
	178	try {
a92f52	179	Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ;
a6a76f	180	TransCostsEntity deliveryFeeEntity = new TransCostsEntity();
F	181	if (map != null) {
	182	deliveryFeeEntity.setTransCosts(map.get("TransCosts")== null?0L: Double.parseDouble( map.get("TransCosts").toString() ) );
	183	deliveryFeeEntity.setTransDistance(map.get("TransDistance")== null?0:(Integer)map.get("TransDistance") );
	184	deliveryFeeEntity.setFreeId(freeId);
	185	deliveryFeeEntity.setFreeName(map.get("FreeName")== null?"":(String)map.get("FreeName") );
	186	}
	187
	188	return deliveryFeeEntity ;
	189	}catch(DataAccessException e ) {
	190	if (e instanceof EmptyResultDataAccessException){
	191	return null ;
	192	}else {
	193	e.printStackTrace();
	194	throw e;
	195	}
	196	}catch(Exception e){
	197	e.printStackTrace();
	198	throw e;
	199	}
	200
	201	}
	202
	203	@Override
	204	public List<FreeEntity> getFreeEntityList(String freeIds,Integer paymentAddressSeq,String cltCode,double selectCartAmount) {
	205	if (freeIds == null\|\|"".equals(freeIds)) return null ;
	206	String sql = " set nocount on ; \n"
a92f52	207	+ " declare @FreeIds varchar(200) = "+GridUtils.prossSqlParm(freeIds)+" \n"
J	208	+ " declare @paymentAddressSeq int = "+paymentAddressSeq+" ,@CltCode varchar(20) = "+GridUtils.prossSqlParm(cltCode)+", @SelectCartAmount money = " +selectCartAmount +" \n"
a6a76f	209	+ " declare @Longitude varchar(50),@Latitude varchar(50),@Longitude2 varchar(50),@Latitude2 varchar(50) \n"
F	210	+ " declare @TransDistance int,@TransCosts money\n"
	211	+ " declare @FreeId int,@FreeTotal money,@FlatCost money \n"
	212	+ " declare @table table(FreeId int Primary Key,FreeName varchar(50),FreeTotal money,\n"
	213	+ " FlatCost money,SortOrder int,TransDistance int,TransCosts money,isRequiredAddress int) \n"
	214
	215	//取配送列表
	216	+ " insert into @table(FreeId,FreeName,FreeTotal,FlatCost,SortOrder,isRequiredAddress) \n"
	217	+ " select a.FreeId,a.FreeName, a.FreeTotal,a.FlatCost,a.SortOrder,a.isRequiredAddress \n"
	218	+ " from t714034 a \n"
	219	+ " where a.FreeId in (select list from getinstr( @FreeIds)) \n"
	220	+ " and ISNULL(a.Status,0) = 1 \n"
	221	+ " order by a.SortOrder asc \n"
	222
	223	//计算距离
	224	+ " if isnull(@paymentAddressSeq,0) = 0 \n"
	225	+ " begin \n"
	226	+ " select @Longitude = a.Longitude,@Latitude = a.Latitude \n"
	227	+ " from t110203 a where a.cltcode = @cltcode \n"
	228	+ " end else \n"
	229	+ " begin \n"
	230	+ " select @Longitude = a.Longitude,@Latitude = a.Latitude \n"
	231	+ " from t110209 a where a.cltcode = @cltcode and a.seq = @paymentAddressSeq \n"
	232	+ " end \n"
	233	+ " select top 1 @Longitude2 = Longitude,@Latitude2 = Latitude from ocompany \n"
	234	+ " if isnull(@Longitude,'') <> '' and isnull(@Latitude,'') <> '' \n"
	235	+ " and isnull(@Longitude2,'') <> '' and isnull(@Latitude2,'') <> '' \n"
	236	+ " begin \n"
	237	+ " select @TransDistance = dbo.f180251v100(cast(@Longitude as decimal(15,9)),cast(@Latitude as decimal(15,9)) ,cast(@Longitude2 as decimal(15,9)),cast(@Latitude2 as decimal(15,9))) \n"
	238	+ " end \n"
	239
	240	//计算配送费
	241	+ " declare mycurFreeId cursor for \n"
	242	+ " select FreeId,FreeTotal,FlatCost from @table \n"
	243	+ " open mycurFreeId \n"
	244	+ " fetch next from mycurFreeId into @FreeId,@FreeTotal,@FlatCost \n"
	245	+ " while @@fetch_status = 0 \n"
	246	+ " begin \n"
	247	+ " set @TransCosts = 0 \n"
	248	+ " if exists(select top 1 1 from t714035 a where a.FreeId = @FreeId) \n"
	249	+ " begin \n"
b237f3	250	+ " select @TransCosts = case when isnull(@FreeTotal,0) <> 0 and isnull(@SelectCartAmount,0)> isnull(@FreeTotal,0) then 0 else a.TransCosts end \n"
a6a76f	251	+ " from t714035 a \n"
F	252	+ " where a.FreeId = @FreeId and @TransDistance between a.DistanceStart and a.DistanceEnd \n"
	253	+ " end else \n"
	254	+ " begin \n"
b237f3	255	+ " select @TransCosts = case when isnull(@FreeTotal,0) <> 0 and isnull(@SelectCartAmount,0) - ISNULL(@FreeTotal,0) >= 0 then 0 else @FlatCost end \n"
a6a76f	256	+ " end \n"
F	257	+ " update a set TransCosts = @TransCosts,TransDistance = @TransDistance from @table a where a.FreeId = @FreeId \n"
	258	+ " fetch next from mycurFreeId into @FreeId,@FreeTotal,@FlatCost \n"
	259	+ " end \n"
	260	+ " close mycurFreeId \n"
	261	+ " deallocate mycurFreeId \n"
	262
	263	+ " select a.FreeId ,a.FreeName ,a.FreeTotal, a.FlatCost,a.SortOrder ,\n"
	264	+ " a.TransDistance ,a.TransCosts,a.isRequiredAddress \n"
	265	+ " from @table a \n"
	266	+ " order by a.SortOrder asc \n" ;
	267	List<FreeEntity> freeList = new ArrayList<FreeEntity>() ;
	268	List<Map<String,Object>> list = null ;
	269	try{
a92f52	270	list = this.jdbcTemplate.queryForList(sql) ;
a6a76f	271	for (int i = 0; list != null && i < list.size(); i++) {
F	272	Map<String,Object> map = list.get(i) ;
	273	FreeEntity freeEntity = new FreeEntity() ;
	274	freeEntity.setFreeId(map.get("FreeId") == null ? 0 : (Integer)map.get("FreeId"));
	275	freeEntity.setFreeName(map.get("FreeName") == null ? "" : (String)map.get("FreeName"));
	276	//freeEntity.setFlatCost(map.get("FlatCost") == null ? 0 : (Double.parseDouble(map.get("FlatCost").toString())));
	277	freeEntity.setFreeTotal(map.get("FreeTotal") == null ? 0 : (Double.parseDouble(map.get("FreeTotal").toString())));
	278	freeEntity.setSortOrder(map.get("SortOrder") == null ? 0 : (Integer)map.get("SortOrder"));
	279	freeEntity.setTransCosts(map.get("TransCosts")== null?0L: Double.parseDouble( map.get("TransCosts").toString() ) );
	280	freeEntity.setTransDistance(map.get("TransDistance")== null?0:(Integer)map.get("TransDistance") );
	281	freeEntity.setRequiredAddress(map.get("isRequiredAddress")!= null&&map.get("isRequiredAddress").equals(1) ?true:false );
	282
	283	freeList.add(freeEntity);
	284	}
	285	return freeList ;
	286	}catch(DataAccessException e ) {
	287	if (e instanceof EmptyResultDataAccessException){
	288	return null ;
	289	}else {
	290	e.printStackTrace();
	291	throw new ApplicationException(e.getMessage());
	292	}
	293	}catch(Exception e){
	294	e.printStackTrace();
	295	throw e;
	296	}
	297	}
	298
	299	@Override
	300	public FreeEntity getFreeEntity(String userCode, String sessionId, Integer freeId) {
	301	if (freeId == null) return null ;
	302
	303	String sql = " set nocount on ; \n"
a92f52	304	+ " declare @UserCode varchar(50)="+GridUtils.prossSqlParm(userCode)+",@SessionId varchar(200)="+GridUtils.prossSqlParm(sessionId)+", @TotalAmount money ,@FreeId int="+freeId+" \n"
a6a76f	305	+ " select @TotalAmount = SUM(isnull(Amount,0)) \n"
F	306	+ " from t710205 \n"
	307	+ " where UserCode=@UserCode or SessionId = @SessionId \n"
	308
	309	+ " select a.FreeId,a.FreeName, case when isnull(@TotalAmount,0) - ISNULL(a.FreeTotal,0) >= 0 then 0 else FlatCost end as FlatCost, a.FreeTotal,a.SortOrder \n"
	310	+ " from t714034 a where a.FreeId = @FreeId and ISNULL(a.Status,0) = 1 \n" ;
	311
	312	Map<String,Object> map = null ;
	313	try{
a92f52	314	map = this.jdbcTemplate.queryForMap(sql) ;
a6a76f	315	if (map != null ) {
F	316	FreeEntity freeEntity = new FreeEntity() ;
	317	freeEntity.setFreeId(map.get("FreeId") == null ? 0 : (Integer)map.get("FreeId"));
	318	freeEntity.setFreeName(map.get("FreeName") == null ? "" : (String)map.get("FreeName"));
	319	freeEntity.setFlatCost(map.get("FlatCost") == null ? 0 : (Double.parseDouble(map.get("FlatCost").toString())));
	320	freeEntity.setFreeTotal(map.get("FreeTotal") == null ? 0 : (Double.parseDouble(map.get("FreeTotal").toString())));
	321	freeEntity.setSortOrder(map.get("SortOrder") == null ? 0 : (Integer)map.get("SortOrder"));
	322	return freeEntity ;
	323	}
	324	}catch(DataAccessException e ) {
	325	if (e instanceof EmptyResultDataAccessException){
	326	return null ;
	327	}else {
	328	e.printStackTrace();
	329	throw new ApplicationException(e.getMessage());
	330	}
	331	}catch(Exception e){
	332	e.printStackTrace();
	333	throw e;
	334	}
	335
	336	return null;
	337	}
	338
	339	}