package com.yc.sdk.shopping.service;
|
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Map;
|
|
import org.springframework.context.annotation.Scope;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.dao.EmptyResultDataAccessException;
|
import org.springframework.stereotype.Service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.sdk.shopping.entity.CountryEntity;
|
import com.yc.sdk.shopping.entity.RegionEntity;
|
import com.yc.service.BaseService;
|
|
@Service("CountryImpl")
|
@Scope("prototype")
|
public class CountryImpl extends BaseService implements CountryIfc {
|
|
@Override
|
public List<CountryEntity> getCountrys() {
|
String sql = " set nocount on ; \n"
|
+ " select CountryId,CountryName,ISOCode2,ISOCode3,AddressFormat,PostcodeRequired \n"
|
+ " from t714030 \n"
|
+ " where isnull(Status,0) = 1 order by CountryName ; \n" ;
|
List<Map<String,Object>> list = null ;
|
try {
|
list = this.jdbcTemplate.queryForList(sql) ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
//e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
//e.printStackTrace();
|
throw e;
|
}
|
|
List<CountryEntity> countryList = new ArrayList<CountryEntity>() ;
|
for (int i = 0 ; list != null && i < list.size();i++) {
|
Map<String,Object> map = list.get(i) ;
|
CountryEntity countryEntity = getCountryEntity(map);
|
countryList.add(countryEntity);
|
}
|
return countryList;
|
}
|
|
@Override
|
public CountryEntity getCountry(Integer countryId) {
|
String sql = " set nocount on ; \n"
|
+ " select CountryId,CountryName,ISOCode2,ISOCode3,AddressFormat,PostcodeRequired \n"
|
+ " from t714030 \n"
|
+ " where CountryId = "+countryId+" and isnull(Status,0) = 1 \n "
|
+ " order by CountryName ; \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 getCountryEntity(map);
|
}
|
|
public CountryEntity getCountryEntity(Map<String,Object> map) {
|
|
CountryEntity countryEntity = new CountryEntity() ;
|
countryEntity.setCountryId((Integer)map.get("CountryId") == null ? null: (Integer)map.get("CountryId"));
|
countryEntity.setCountryName((String)map.get("CountryName") == null ? "": (String)map.get("CountryName"));
|
countryEntity.setIsoCode2((String)map.get("ISOCode2") == null ? "": (String)map.get("ISOCode2"));
|
countryEntity.setIsoCode3((String)map.get("ISOCode3") == null ? "": (String)map.get("ISOCode3"));
|
countryEntity.setAddressFormat((String)map.get("AddressFormat") == null ? "": (String)map.get("AddressFormat"));
|
countryEntity.setPostcodeRequired((Integer)map.get("PostcodeRequired") == null ? 0: (Integer)map.get("PostcodeRequired"));
|
return countryEntity ;
|
|
}
|
|
@Override
|
public boolean hasShopCcCode(String areaId) {
|
String sql = " set nocount on ; \n"
|
+ " if exists( select top 1 1 from t110601 a \n"
|
+ " where ISNULL(a.isIndependentAccountAbility,0) = 1 \n"
|
+ " and a.AreaId in (select AreaId from f110202("+GridUtils.prossSqlParm(areaId)+",'') )\n"
|
+ " and isnull(a.ShopCcCode,'') <> '' ) \n"
|
+ " select 1 \n"
|
+ " else \n"
|
+ " select 0 \n" ;
|
try{
|
Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ;
|
if (ret != null && ret.intValue() == 1 ) {
|
return true ;
|
}else {
|
return false ;
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return false ;
|
}else {
|
// e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
//e.printStackTrace();
|
throw e;
|
}
|
}
|
|
@Override
|
public List<RegionEntity> getRegionsByCountryId(Integer countryId) {
|
String sql = " set nocount on ; \n"
|
+ " select CountryId, ZoneId,EnAreaName,AreaId,AreaName,Status \n"
|
+ " from t110202 \n"
|
+ " where CountryId = "+countryId+" and isnull(Status,0) = 1 and isnull(ParentRowid,'') = '' \n "
|
+ " order by AreaName asc ; \n" ;
|
List<Map<String,Object>> list = null ;
|
//Map<String,Object> map = null ;
|
List<RegionEntity> regionList = new ArrayList<RegionEntity>() ;
|
try{
|
list = this.jdbcTemplate.queryForList(sql) ;
|
if (list == null ) return null ;
|
|
for (Map<String,Object> map:list) {
|
RegionEntity regionEntity = new RegionEntity() ;
|
regionEntity.setCountryId(countryId);
|
regionEntity.setZoneId(map.get("ZoneId") == null ? 0 : (Integer)map.get("ZoneId"));
|
regionEntity.setCode(map.get("EnAreaName") == null ? "" : (String)map.get("EnAreaName"));
|
regionEntity.setAreaId(map.get("AreaId") == null ? "" : (String)map.get("AreaId"));
|
regionEntity.setName(map.get("AreaName") == null ? "" : (String)map.get("AreaName"));
|
regionEntity.setStatus(map.get("Status") == null ? 0 : (Integer)map.get("Status"));
|
|
regionList.add(regionEntity);
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
// e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
//e.printStackTrace();
|
throw e;
|
}
|
|
return regionList;
|
}
|
|
@Override
|
public List<RegionEntity> getRegionsByZoneId(Integer zoneId) {
|
|
String sql = " set nocount on ; \n"
|
+ " select CountryId, ZoneId,EnAreaName,AreaId,AreaName,Status \n"
|
+ " from t110202 \n"
|
+ " where isnull(ParentRowid,'') in (select Rowid from t110202 where ZoneId = "+zoneId+" ) \n "
|
+ " and isnull(Status,0) = 1 \n "
|
+ " order by AreaName asc ; \n" ;
|
List<Map<String,Object>> list = null ;
|
//Map<String,Object> map = null ;
|
List<RegionEntity> regionList = new ArrayList<RegionEntity>() ;
|
try{
|
list = this.jdbcTemplate.queryForList(sql) ;
|
if (list == null ) return null ;
|
|
for (Map<String,Object> map:list) {
|
RegionEntity regionEntity = new RegionEntity() ;
|
regionEntity.setCountryId(null);
|
regionEntity.setZoneId(map.get("ZoneId") == null ? 0 : (Integer)map.get("ZoneId"));
|
regionEntity.setCode(map.get("EnAreaName") == null ? "" : (String)map.get("EnAreaName"));
|
regionEntity.setAreaId(map.get("AreaId") == null ? "" : (String)map.get("AreaId"));
|
regionEntity.setName(map.get("AreaName") == null ? "" : (String)map.get("AreaName"));
|
regionEntity.setStatus(map.get("Status") == null ? 0 : (Integer)map.get("Status"));
|
|
regionList.add(regionEntity);
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
//e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
//e.printStackTrace();
|
throw e;
|
}
|
|
return regionList;
|
}
|
|
@Override
|
public RegionEntity getRegionsByAreaName(String areaName) {
|
|
String sql = " set nocount on ; \n"
|
+ " declare @areaid varchar(50) , @areaname varchar(50) = "+GridUtils.prossSqlParm(areaName)+" \n"
|
+ " declare @found int = 0 ,@parentrowid varchar(20) \n"
|
+ " declare @FullAreaName varchar(500) ,@ZoneId int, \n"
|
+ " @ParentName varchar(50),@ParentZoneId int,@ParentAreaId varchar(50) \n"
|
+ " select top 1 @areaid = a.areaid,@ZoneId = a.ZoneId,@areaname = a.AreaName,\n"
|
+ " @parentrowid = a.parentrowid,@FullAreaName = a.AreaName, \n"
|
+ " @ParentName = b.AreaName,@ParentZoneId = b.ZoneId,@ParentAreaId = b.AreaId \n"
|
+ " from t110202 a left join t110202 b on a.parentrowid = b.rowid \n"
|
+ "where a.areaname like '%' + isnull(@areaname,'') + '%' \n"
|
+ " if @@ROWCOUNT > 0 set @found = 1 else set @found = 0\n"
|
+ " while ISNULL(@found,0) = 1\n"
|
+ " begin\n"
|
+ " select @FullAreaName = ISNULL(areaname,'') + ISNULL(@FullAreaName,'') ,\n"
|
+ " @parentrowid = a.parentrowid\n"
|
+ " from t110202 a \n"
|
+ " where rowid = @parentrowid \n"
|
+ " if @@ROWCOUNT > 0 set @found = 1 else set @found = 0\n"
|
+ " end \n"
|
+ " select @ZoneId as ZoneId,@areaid as AreaId,@areaname as AreaName,@FullAreaName as FullAreaName,\n"
|
+ " @ParentName as ParentName,@ParentZoneId as ParentZoneId ,@ParentAreaId as ParentAreaId \n" ;
|
Map<String,Object> map = null ;
|
//Map<String,Object> map = null ;
|
|
try{
|
map = this.jdbcTemplate.queryForMap(sql) ;
|
if (map != null) {
|
RegionEntity regionEntity = new RegionEntity() ;
|
regionEntity.setCountryId(null);
|
regionEntity.setZoneId(map.get("ZoneId") == null ? 0 : (Integer)map.get("ZoneId"));
|
regionEntity.setCode(map.get("EnAreaName") == null ? "" : (String)map.get("EnAreaName"));
|
regionEntity.setAreaId(map.get("AreaId") == null ? "" : (String)map.get("AreaId"));
|
regionEntity.setName(map.get("AreaName") == null ? "" : (String)map.get("AreaName"));
|
regionEntity.setStatus(map.get("Status") == null ? 0 : (Integer)map.get("Status"));
|
regionEntity.setFullAreaName(map.get("FullAreaName") == null ? "" : (String)map.get("FullAreaName"));
|
regionEntity.setParentName(map.get("ParentName") == null ? "" : (String)map.get("ParentName"));
|
regionEntity.setParentZoneId(map.get("ParentZoneId") == null ? 0 : (Integer)map.get("ParentZoneId"));
|
regionEntity.setParentAreaId(map.get("ParentAreaId") == null ? "" : (String)map.get("ParentAreaId"));
|
|
return regionEntity ;
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
//e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
//e.printStackTrace();
|
throw e;
|
}
|
|
return null;
|
}
|
|
@Override
|
public RegionEntity getRegionsByAreaId(String areaId) {
|
|
String sql = " set nocount on ; \n"
|
+ " declare @areaid varchar(50) = "+GridUtils.prossSqlParm(areaId)+" \n" +
|
" declare @found int = 0 ,@areaname varchar(50) ,@parentrowid varchar(20) \n" +
|
" declare @FullAreaName varchar(500) ,@ZoneId int \n" +
|
" select @ZoneId = ZoneId,@areaname = a.areaname,@parentrowid = a.parentrowid,@FullAreaName = a.areaname \n" +
|
" from t110202 a where a.areaid = @areaid\n" +
|
" if @@ROWCOUNT > 0 set @found = 1 else set @found = 0\n" +
|
" while ISNULL(@found,0) = 1\n" +
|
" begin\n" +
|
" select @FullAreaName = ISNULL(areaname,'') + ISNULL(@FullAreaName,'') ,\n" +
|
" @parentrowid = a.parentrowid\n" +
|
" from t110202 a \n" +
|
" where rowid = @parentrowid \n" +
|
" if @@ROWCOUNT > 0 set @found = 1 else set @found = 0\n" +
|
" end \n" +
|
" select @ZoneId as ZoneId,@areaid as AreaId,@areaname as AreaName,@FullAreaName as FullAreaName\n" ;
|
Map<String,Object> map = null ;
|
//Map<String,Object> map = null ;
|
|
try{
|
map = this.jdbcTemplate.queryForMap(sql) ;
|
if (map != null) {
|
RegionEntity regionEntity = new RegionEntity() ;
|
regionEntity.setCountryId(null);
|
regionEntity.setZoneId(map.get("ZoneId") == null ? 0 : (Integer)map.get("ZoneId"));
|
regionEntity.setCode(map.get("EnAreaName") == null ? "" : (String)map.get("EnAreaName"));
|
regionEntity.setAreaId(map.get("AreaId") == null ? "" : (String)map.get("AreaId"));
|
regionEntity.setName(map.get("AreaName") == null ? "" : (String)map.get("AreaName"));
|
regionEntity.setStatus(map.get("Status") == null ? 0 : (Integer)map.get("Status"));
|
regionEntity.setFullAreaName(map.get("FullAreaName") == null ? "" : (String)map.get("FullAreaName"));
|
return regionEntity ;
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
// e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
//e.printStackTrace();
|
throw e;
|
}
|
|
return null;
|
}
|
|
|
@Override
|
public List<RegionEntity> getRegionsByHasShopCcCode(Integer zoneId) {
|
|
String sql = " set nocount on ; \n"
|
+ " select CountryId, ZoneId,EnAreaName,AreaId,AreaName,Status \n"
|
+ " from t110202 a \n"
|
+ " where isnull(ParentRowid,'') in (select Rowid from t110202 where ZoneId = "+zoneId+" ) \n "
|
+ " and isnull(a.Status,0) = 1 \n "
|
+ " order by a.AreaName asc ; \n" ;
|
List<Map<String,Object>> list = null ;
|
//Map<String,Object> map = null ;
|
List<RegionEntity> regionList = new ArrayList<RegionEntity>() ;
|
try{
|
list = this.jdbcTemplate.queryForList(sql) ;
|
if (list == null ) return null ;
|
for (Map<String,Object> map:list) {
|
RegionEntity regionEntity = new RegionEntity() ;
|
regionEntity.setCountryId(null);
|
regionEntity.setZoneId(map.get("ZoneId") == null ? 0 : (Integer)map.get("ZoneId"));
|
regionEntity.setCode(map.get("EnAreaName") == null ? "" : (String)map.get("EnAreaName"));
|
regionEntity.setAreaId(map.get("AreaId") == null ? "" : (String)map.get("AreaId"));
|
regionEntity.setName(map.get("AreaName") == null ? "" : (String)map.get("AreaName"));
|
regionEntity.setStatus(map.get("Status") == null ? 0 : (Integer)map.get("Status"));
|
|
regionList.add(regionEntity);
|
}
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
// e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
//e.printStackTrace();
|
throw e;
|
}
|
|
return regionList;
|
}
|
|
|
@Override
|
public RegionEntity getRegionsByShopCcCode(String cccode) {
|
|
String sql = " set nocount on ; \n"
|
+ " declare @cccode varchar(50) = "+GridUtils.prossSqlParm(cccode)+",@AreaId varchar(50) \n"
|
+ " select @AreaId = a.AreaId from t110601 a where a.cccode = @cccode \n"
|
+ " select CountryId, ZoneId,EnAreaName,AreaId,AreaName,Status \n"
|
+ " from t110202 a \n"
|
+ " where a.AreaId = @AreaId \n "
|
+ " and isnull(a.Status,0) = 1 \n "
|
+ " order by a.AreaName asc ; \n" ;
|
Map<String,Object> map = null ;
|
|
try{
|
map = this.jdbcTemplate.queryForMap(sql) ;
|
if (map == null ) return null ;
|
|
RegionEntity regionEntity = new RegionEntity() ;
|
regionEntity.setCountryId(null);
|
regionEntity.setZoneId(map.get("ZoneId") == null ? 0 : (Integer)map.get("ZoneId"));
|
regionEntity.setCode(map.get("EnAreaName") == null ? "" : (String)map.get("EnAreaName"));
|
regionEntity.setAreaId(map.get("AreaId") == null ? "" : (String)map.get("AreaId"));
|
regionEntity.setName(map.get("AreaName") == null ? "" : (String)map.get("AreaName"));
|
regionEntity.setStatus(map.get("Status") == null ? 0 : (Integer)map.get("Status"));
|
return regionEntity ;
|
}catch(DataAccessException e ) {
|
if (e instanceof EmptyResultDataAccessException){
|
return null ;
|
}else {
|
// e.printStackTrace();
|
throw e;
|
}
|
}catch(Exception e){
|
//e.printStackTrace();
|
throw e;
|
}
|
|
}
|
}
|
