package com.yc.sdk.miniapp.service; import java.io.File; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import java.util.UUID; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.context.annotation.Scope; import org.springframework.dao.DataAccessException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.stereotype.Service; import com.yc.sdk.miniapp.entity.EducationEntity; import com.yc.sdk.miniapp.entity.LikeActionEntity; import com.yc.sdk.weixincp.entity.MyWxCpUser; import com.yc.sdk.weixincp.service.ERPUserImpl; import com.yc.sdk.weixincp.util.AvatarFile; import com.yc.sdk.weixincp.util.FileExtensionName; import com.yc.sdk.weixincp.util.RandomString; import com.yc.sdk.weixincp.util.UploadFiles; import com.yc.sdk.weixinmp.entity.MyWxMpUser; import com.yc.service.BaseService; import com.yc.utils.SessionKey; import cn.binarywang.wx.miniapp.bean.WxMaUserInfo; @Service("MaUserImpl") @Scope("prototype") public class MaUserImpl extends BaseService implements MaUserIfc { private final Logger log = LoggerFactory.getLogger(this.getClass()); @Override public MyWxMpUser saveUserOpenId(String openId,String fromUserId,String sessionId,String sceneCode,String fromOpenId) { String sql = "set nocount on \n" + " declare @myrowcount int = 0,@OpenId varchar(200) = ? , @FromUserId varchar(50) = ? ,@FromUserName varchar(50) \n" + " declare @ReferralsType varchar(50) = '企业微信',@ReferralsName varchar(50) \n" + " declare @ShopCcCode varchar(50) ,@ShopCcName varchar(80) \n" + " declare @SessionId varchar(80) = ?,@SceneCode varchar(80) = ? ,@FromOpenId varchar(200) = ? \n" + " declare @isFound int = 0 , @NickName varchar(200) \n" + " declare @CltCode varchar(20),@Today datetime = convert(varchar(10),getdate(),120) \n" + " if isnull(@OpenId,'') <> '' \n" + " begin \n" + " select @NickName = NickName from t730102 where openid = @OpenId \n" + " if @@ROWCOUNT > 0 set @isFound = 1 \n" + " end \n" + " if isnull(@isFound,0) = 0\n" + " begin \n" + " if isnull(@FromUserId,'') <> '' \n" + " begin \n" + " select @ReferralsName = a.Name,@ShopCcCode = a.ShopCcCode,@ShopCcName = b.CcName \n" + " from t700107 a left join t110601 b on a.ShopCcCode = b.CcCode \n" + " where a.UserId = @FromUserId \n" + " end \n" + " insert into t730102(OpenId,ReferralsType,ReferralsCode,ReferralsName,LastUserId,LastSelectShopCcCode,LastSceneCode,LastVisitDate,LastFromOpenId,LastFromOpenIdVisitDate,LastSessionId) \n" + " values(@OpenId,@ReferralsType,@FromUserId,@ReferralsName,@FromUserId,@ShopCcCode,@SceneCode,getdate(),@FromOpenId,getdate(),@SessionId) \n" + " if isnull(@ShopCcCode,'') <> '' and exists(select 1 from t714001 where isnull(isStartupLeagueShopCcCode,0) = 1) \n" + " begin \n" + " if not exists(select 1 from t730121 a where a.ShopCcCode = isnull(@ShopCcCode,'') and a.OpenId = isnull(@OpenId,'') ) \n" + " begin \n" + " insert into t730121(OpenId,NickName,WeiXinAvatarUnid,ShopCcCode,ShopCcName,CreateDate,LastUserId,LastUserName) \n" + " values(isnull(@OpenId,''),null,null,isnull(@ShopCcCode,''),@ShopCcName,getdate(),@FromUserId,@FromUserName) \n" + " end \n" + " end \n" + " select @myrowcount = @@rowcount \n" + " end else \n" + " begin\n" + " update a set LastFromOpenId =case when isnull(@FromOpenId,'') <> '' then @FromOpenId else LastFromOpenId end,\n" + " LastFromOpenIdVisitDate = case when isnull(@FromOpenId,'') <> '' then getdate() else LastFromOpenIdVisitDate end,\n" + " LastSessionId = @SessionId\n" + " from t730102 a where a.OpenId = @OpenId\n" + " select @myrowcount = @@rowcount \n" + " end \n" + " if isnull(@SceneCode,'') <> '' and not exists(select top 1 1 from t735008 where OpenId = @OpenId and SessionId = @SessionId and SceneCode = @SceneCode ) \n" + " begin \n" + " insert into t735008 (OpenId,SessionId,SceneCode,DateAdded) values(@OpenId,@SessionId,@SceneCode,getdate()) \n" + " update a set LastSceneCode = @SceneCode,LastVisitDate = getdate() from t730102 a where a.OpenId = @OpenId \n" + " end \n" + getUserSql(); try { Map map = this.jdbcTemplate.queryForMap(sql,new Object[] {openId,fromUserId,sessionId,sceneCode,fromOpenId}); MyWxMpUser wxMpUser = getMyWxMpUser( map) ; return wxMpUser ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public MyWxMpUser saveUser(String openId,WxMaUserInfo userInfo, HttpServletRequest request,String fromUserId,boolean isUpdateAvatar) throws Exception { HttpSession session = request.getSession(); //同步用户时，同时更新头像 //String isAvatar = (String)request.getAttribute("isAvatar"); String avatarMediaIDUri = userInfo.getAvatarUrl(); //获取微信图像 uri String filePathFile = request.getSession().getServletContext().getRealPath("/") + "uploads"+File.separator+"smallpic"+File.separator ; String fileName = filePathFile + RandomString.getRandomString(4) +".jpg"; File file = null; UploadFiles uploadFiles = null ; MyWxMpUser wxMpUser=null; try { MyWxMpUser erpMpUser = getUser(openId) ; if (isUpdateAvatar || erpMpUser == null) { file = AvatarFile.getAvatarFile (avatarMediaIDUri,fileName,filePathFile) ; //获取微信图像 File 对象 } int formid = 730102; String rowid = openId ; String doccode = openId ; String fieldid = "WeiXinAvatarUnid" ; Integer dbid = (session.getAttribute(SessionKey.DATA_BASE_ID) == null ? null : Integer.parseInt( session.getAttribute(SessionKey.DATA_BASE_ID).toString())) ; if (dbid == null) { dbid = (session.getAttribute(SessionKey.SHOPPING_DBID) == null ? null : Integer.parseInt( session.getAttribute(SessionKey.SHOPPING_DBID).toString())) ; } String uuid = UUID.randomUUID().toString().toUpperCase();//生成uuid //String filePath = session.getServletContext().getRealPath("/") + "uploads"+File.separator+"smallpic"+File.separator ; // Square_logo_url 源文件二进制 文件和缩略图 String fileExName = null ; if (file != null) { uploadFiles = AvatarFile.getImages(file) ; fileExName = FileExtensionName.getFileExtensionName(file.getName()); } //以下SQL语句用于更新或新建微信用户 String sql = "set nocount on ; \n" + " declare @FromUserId varchar(50) = ?,@FromUserName varchar(50),@Subscribe int = ?,@OpenId varchar(200) = ? ; \n" + " declare @NickName nvarchar(200) = ?,@Sex varchar(10) =? ; \n" + " declare @City varchar(50) = ?,@Country varchar(50) = ?; \n" + " declare @Province varchar(50) = ?,@Language varchar(50) = ? ; \n" + " declare @Headimgurl varchar(200) = ?,@SubscribeTime datetime = ? ; \n" + " declare @Unionid varchar(50) = ?,@Remark varchar(200) = ? ;\n" + " declare @ReferralsCode varchar(200) = ?,@ReferralsCodeOpenId varchar(200) ;\n" + " declare @ReferralsName varchar(200) ,@FromWx varchar(20) = ? ; \n" + " declare @ReferralsType varchar(50) = '企业微信' \n" + " declare @GroupId int ,@GroupSeq int, @Ret int,@Telephone varchar(50) ; \n" + " declare @seq int ,@ShopCcCode varchar(50),@ShopCcName varchar(80),@isStartupLeagueShopCcCode int \n" + " declare @CltCode varchar(50) ,@Today datetime = convert(varchar(10),getdate(),120) \n" //输出 user 结果时用 + " select @GroupSeq = GroupSeq from t720101 where GroupId = @GroupId ; \n" //+ " select top 1 @isStartupLeagueShopCcCode = isStartupLeagueShopCcCode from t714001 \n" + " if isnull(@FromUserId,'') <> '' \n" + " begin \n" + " select @ShopCcCode = a.ShopCcCode,@ShopCcName = b.CcName , \n" + " @FromUserName = a.Name,@ReferralsName = a.Name ,@ReferralsCode = a.UserId,@FromWx = '1' \n" + " from t700107 a left join t110601 b on a.ShopCcCode = b.CcCode \n" + " where a.UserId = @FromUserId \n" + " end \n" /*+ " if isnull(@ReferralsCode,'') <> '' \n" + " begin \n" + " select @ReferralsCodeOpenId = '' ; \n" + " select @ReferralsCodeOpenId = MiniAppOpenId from t110203 where CltCode = @ReferralsCode ; \n" + " if isnull(@ReferralsCodeOpenId,'') <> '' select @ReferralsCode = @ReferralsCodeOpenId ; \n" + " select @ReferralsName = case when isnull(Remark,'') <> '' then Remark else NickName end, \n" + " @FromWx = '3' \n" + " from t730102 where OpenId = @ReferralsCode \n" + " if @@RowCount = 0 \n" + " begin \n" + " select @ReferralsName = UserName ,@FromWx = '1' \n" + " from _sys_LoginUser where UserCode =@ReferralsCode \n" + " end \n" + " end ; \n"*/ + " select @Telephone = Tel from t110203 where MiniAppOpenId = @OpenId \n" //如果存在于客户表中，则从客户表中找回电话 + " if exists(select 1 from t730102 where OpenId = @OpenId ) \n" + " update a set Subscribe = @Subscribe,NickName = @NickName,Sex = @Sex, \n" + " City = @City,Country = @Country ,Province = @Province,Language = @Language, \n" + " Headimgurl = @Headimgurl,SubscribeTime = @SubscribeTime, \n" + " Unionid = @Unionid, \n" + " ReferralsCode = case when isnull(a.ReferralsCode,'') <> '' then a.ReferralsCode else @ReferralsCode end , \n" + " ReferralsName= case when isnull(@ReferralsCode,'') <> '' \n" + " then @ReferralsName else ReferralsName end, \n" //只需要更新 介绍人姓名即可，介绍人编号不用更新 ReferralsCode = @ReferralsCode, + " UnSubscribeTime = null ,Tel = @Telephone, \n" + " LastSelectShopCcCode = case when isnull(a.LastSelectShopCcCode,'') <> '' then LastSelectShopCcCode else @ShopCcCode end \n" + " from t730102 a \n" + " where OpenId = @OpenId ; \n" + " else \n" + " begin \n" + " if @GroupSeq is null select @GroupSeq = GroupSeq from t730101 where GroupId = 0 ; \n" + " insert into t730102(Subscribe,OpenId,NickName,Sex,City,Country,Province,Language,\n" + " Headimgurl,SubscribeTime,Unionid,Remark,GroupSeq,ReferralsCode,ReferralsName,FromWx,Tel,LastSelectShopCcCode ) \n " + " values(@Subscribe,@OpenId,@NickName,@Sex,@City,@Country,@Province,@Language,\n" + " @Headimgurl,@SubscribeTime,@Unionid,@Remark, @GroupSeq,@ReferralsCode,@ReferralsName,@FromWx,@Telephone,@ShopCcCode ) ; \n" + " end ; \n" //补齐 客户表 t110203 中的名称 CltName ，Added by Johns Wang,2020-04-18 + " if exists(select 1 from t110203 a where a.MiniAppOpenId = @OpenId ) \n" + " begin \n" + " update a set CltName = case when isnull(a.CltName,'') = '' then @NickName else a.CltName end ,\n" + " ReferralsCode = case when isnull(a.ReferralsCode,'') = '' then @ReferralsCode else a.ReferralsCode end , \n" + " ReferralsName = case when isnull(a.ReferralsCode,'') = '' then @ReferralsName else a.ReferralsName end , \n" + " ReferralsType = case when isnull(a.ReferralsCode,'') = '' then @ReferralsType else a.ReferralsType end \n" + " from t110203 a where a.MiniAppOpenId = @OpenId \n" + " end \n" ; //下面的SQL语句用于处理微信个人图像 sql += " declare @unid varchar(200), @newUnid varchar(200) ; \n" + " declare @formid int , @doccode varchar(50),@rowid varchar(50),@fieldid varchar(50) ;\n" + " declare @isFound int =0, @SmallPicture varbinary(max),@OriginalPicture varbinary(max) \n" + " select @newUnid = ?,@formid = ?,@doccode = ?, @rowid = ?,@fieldid = ? ; \n" + " declare @table table (unid varchar(50),seq int identity(1,1) primary key) --去重处理 \n" + " declare @maxSeq int "; if (uploadFiles!= null) { sql += " select @SmallPicture = ?,@OriginalPicture = ? \n"; } sql += " declare @PhysicalFile varchar(200) = ?,@OriginalFileName varchar(200) = ?,@FileSize bigint = ?,@FileType varchar(50) = ? \n" + " if isnull(@rowid,'') = '' exec getXXXX @rowid output ; \n" + " if isnull(@doccode,'') = '' set @doccode = @rowid ; \n" + " select @unid = WeiXinAvatarUnid from t730102 where OpenId = @OpenId ; \n" + " if @@rowcount > 0 select @isFound = 1; \n" + " if @isFound = 1 \n" + " begin \n" + " insert into @table (unid) select list from getinstr(@unid) \n" + " select @maxSeq = max(seq) from @table \n" + " select @unid = unid from @table where seq = 1 \n" + " if isnull(@maxSeq,0) > 1 \n" + " begin\n" + " select @seq = unid from @table where seq = @maxSeq \n" + " end \n" + " if isnull(@unid,'') <> '' and exists (select top 1 1 from _sys_Attachment9 where unid= @unid and seq = @seq ) \n" + " begin \n" + " update a set SmallPicture = case when @SmallPicture is not null then @SmallPicture else SmallPicture end , \n" + " OriginalPicture = case when @OriginalPicture is not null then @OriginalPicture else OriginalPicture end, \n" + " LastUpdateTime = getdate(),FileSize = @FileSize,FileType=@FileType,LastFileSize=FileSize,LastFileType=FileType \n" + " from _sys_Attachment9 a where unid = @unid and seq = @seq ; \n" + " end else \n" + " begin \n" + " select @unid = null,@seq = null \n" + " select top 1 @unid = UNID ,@seq = seq 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 = case when @SmallPicture is not null then @SmallPicture else SmallPicture end , \n" + " OriginalPicture = case when @OriginalPicture is not null then @OriginalPicture else OriginalPicture end, \n" + " LastUpdateTime = getdate(),FileSize = @FileSize,FileType=@FileType,LastFileSize=FileSize,LastFileType=FileType \n" + " from _sys_Attachment9 a where unid = @unid and seq = @seq ; \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" + " select @seq = @@IDENTITY ; \n" + " end \n" + " end ; \n" //+ " select @seq = seq from _sys_Attachment9 where unid = @unid \n" + " update a set WeiXinAvatarUnid = @unid+';' + cast(isnull(@seq,0) as varchar(20)),WeiXinAvatarUrl = [dbo].f710819v1(@unid+';' + cast(isnull(@seq,0) as varchar(20))) \n" + " from t730102 a where OpenId = @OpenId ; \n" //+ " if isnull(@isStartupLeagueShopCcCode,0) = 1 \n" + " begin \n" + " update a set NickName = @NickName,WeiXinAvatarUnid= @unid+';' + cast(isnull(@seq,0) as varchar(20)) , \n" + " ShopCcName = @ShopCcName , \n" + " LastUserId = case when isnull(a.LastUserId,'') <> '' then a.LastUserId else @FromUserId end ,\n" + " LastUserName = case when isnull(a.LastUserName,'') <> '' then a.LastUserName else @FromUserName end \n" + " from t730121 a where a.OpenId = isnull(@OpenId,'') and a.ShopCcCode = isnull(@ShopCcCode,'') \n" + " if @@rowcount = 0 \n" + " begin \n" + " insert into t730121(OpenId,NickName,WeiXinAvatarUnid,ShopCcCode,ShopCcName,CreateDate,LastUserId,LastUserName) \n" + " values(isnull(@OpenId,''),@NickName,@unid+';' + cast(isnull(@seq,0) as varchar(20)),\n" + " isnull(@ShopCcCode,''),@ShopCcName,getdate(),@FromUserId,@FromUserName) \n" + " end \n" + " end \n" + " end ; \n" + getUserSql(); Date subscribeDateTime = null ; if (userInfo.getWatermark() != null&&userInfo.getWatermark().getTimestamp()!=null) { subscribeDateTime = new Date(Integer.valueOf(userInfo.getWatermark().getTimestamp()) *1000L); } else { subscribeDateTime = new Date(); } Map map = null; if (uploadFiles==null) { map = this.jdbcTemplate.queryForMap(sql,new Object[]{fromUserId,1, openId,userInfo.getNickName(),(userInfo.getGender() ==null?"未知":(userInfo.getGender().equals("1")?"男":"女")), userInfo.getCity(),userInfo.getCountry(),userInfo.getProvince(), userInfo.getLanguage(),userInfo.getAvatarUrl(),subscribeDateTime, "","","","", uuid,formid, doccode ,rowid ,fieldid, (file==null?null:file.getName()), (file==null?null:file.getName()), (file==null?null:file.length()), (fileExName==null?null:fileExName) }); }else { map = this.jdbcTemplate.queryForMap(sql,new Object[]{fromUserId,1, openId,userInfo.getNickName(),(userInfo.getGender() ==null?"未知":(userInfo.getGender().equals("1")?"男":"女")), userInfo.getCity(),userInfo.getCountry(),userInfo.getProvince(), userInfo.getLanguage(),userInfo.getAvatarUrl(),subscribeDateTime, "","","","", uuid,formid, doccode ,rowid ,fieldid, (uploadFiles==null?null:uploadFiles.getSmallBinaryimages()), //附件不为空时 (uploadFiles==null?null:uploadFiles.getOrgBinaryimages()), //附件不为空时 (file==null?null:file.getName()), (file==null?null:file.getName()), (file==null?null:file.length()), (fileExName==null?null:fileExName) }); } if (map != null ) { wxMpUser = getMyWxMpUser( map) ; } }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { e.printStackTrace(); throw e ; } }catch (Exception e) { e.printStackTrace(); throw e; }finally { if (file != null && file.exists()&&file.isFile()) { log.info("del>>userCode:"+request.getSession().getAttribute(SessionKey.USERCODE)+"|dbid:"+request.getSession().getAttribute(SessionKey.DATA_BASE_ID)+"|"+file.getAbsolutePath()); file.delete(); } if (uploadFiles != null && uploadFiles.getSmallFile().exists()&&uploadFiles.getSmallFile().isFile()) { log.info("del>>userCode:"+request.getSession().getAttribute(SessionKey.USERCODE)+"|dbid:"+request.getSession().getAttribute(SessionKey.DATA_BASE_ID)+"|"+uploadFiles.getSmallFile().getAbsolutePath()); uploadFiles.getSmallFile().delete(); } } return wxMpUser; } @Override public Integer updateWeiXinAvatarUnid(String openId,String weiXinAvatarUnid) { String sql = " set nocount on \n" + " declare @OpenId varchar(200) = ?, @WeiXinAvatarUnid varchar(200) = ? \n" + " update a set WeiXinAvatarUnid = @WeiXinAvatarUnid from t730102 a where a.OpenId = @OpenId \n" + " select @@rowcount \n" ; try { return this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {openId,weiXinAvatarUnid}) ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public List getNotExistsAvatorList() { String sql = " set nocount on \n" + " declare @OpenId varchar(200),@Headimgurl varchar(500),@WeiXinAvatarUnid varchar(500)\n" + " declare @unid varchar(50),@seq bigint ,@maxSeq int,@OriginalPicture varbinary(max) ,@myrowcount int \n" + " declare @table table(OpenId varchar(200),Headimgurl varchar(500),WeiXinAvatarUnid varchar(500),isExistsAvator int default 1)\n" + " declare @table2 table (unid varchar(50),seq int identity(1,1) primary key) --去重处理\n" + " insert into @table (OpenId ,Headimgurl,WeiXinAvatarUnid,isExistsAvator)\n" + " select OpenId ,Headimgurl,WeiXinAvatarUnid,1\n" + " from t730102 a \n" + " where isnull(Headimgurl,'') <> '' \n" + " declare mycur cursor for \n" + " select OpenId ,Headimgurl,WeiXinAvatarUnid from @table \n" + " open mycur \n" + " fetch next from mycur into @OpenId ,@Headimgurl,@WeiXinAvatarUnid\n" + " while @@FETCH_STATUS = 0 \n" + " begin\n" + " select @unid = null,@seq = null ,@maxSeq = null ,@OriginalPicture = null\n" + " insert into @table2 (unid) select list from getinstr(@WeiXinAvatarUnid)\n" + " set @myrowcount = @@ROWCOUNT \n" + " select @maxSeq = max(seq) from @table2\n" + " select top 1 @unid = unid from @table2 \n" + " if isnull(@myrowcount,0) > 1 \n" + " begin\n" + " select @seq = cast( unid as bigint) from @table2 where seq = @maxSeq \n" + " end \n" + " delete from @table2 \n" + " select @OriginalPicture = OriginalPicture from _sys_Attachment9 \n" + " where unid =@unid and seq = @seq \n" + " if @@rowcount = 0 or @OriginalPicture is null --isnull(@WeiXinAvatarUnid,'') = '' \n" + " begin\n" + " -- select @unid as unid ,@seq as seq \n" + " update a set isExistsAvator = 0 from @table a where OpenId = @OpenId \n" + " end \n" + " fetch next from mycur into @OpenId ,@Headimgurl,@WeiXinAvatarUnid\n" + " end \n" + " close mycur \n" + " deallocate mycur \n" + " select OpenId ,Headimgurl,WeiXinAvatarUnid \n" + " from @table \n" + " where isnull(isExistsAvator,0) = 0 \n" ; List wxMpUserList = null ; try { List> list = this.jdbcTemplate.queryForList(sql); for (int i = 0 ;list != null && i < list.size();i++) { if (wxMpUserList == null) wxMpUserList = new ArrayList() ; wxMpUserList.add(getMyWxMpUser( list.get(i))) ; } return wxMpUserList ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public List getKeFuUserList(String openId,String shopCcCode) { String sql = " set nocount on ; \n" + " declare @OpenId varchar(200) = ?,@ShopCcCode varchar(50) = ?,@CardImage varchar(50) \n" + " select top 1 @CardImage = a.CardImage from t730118 a \n" + " select a.userid,b.name as username, b.mobile,b.departids,\n" + " dbo.f700108v1(b.departids) as departnames ,\n" + " b.[order] as [order],b.position,b.gender, \n" + " b.email,b.isleader,b.avatar,b.telephone,\n" + " b.english_name,b.extattr,b.status,b.enable,\n" + " b.AvatarUnid,b.WeiXinUserCode,@CardImage as CardImage \n" + " from t700115 a join t700107 b on a.UserId = b.UserId \n" //+ " join _sysuser c on b.UserId = c.WeiXinCorpUserId \n" + " where a.OpenId = @OpenId \n" + " and isnull(b.isAiRadarUser,0) = 1 \n" + " and (isnull(@ShopCcCode,'') ='' or b.ShopCcCode = @ShopCcCode ) \n"; List userList = null ; try { List> list = this.jdbcTemplate.queryForList(sql,new Object[] {openId,shopCcCode}); for (int i =0;list !=null&&i< list.size();i++) { if (userList == null ) { userList = new ArrayList() ; } userList.add(ERPUserImpl.getWorkAppUserByMap(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 userList; } @Override public MyWxCpUser getSendAIMessageUser(String fromUserId,String systemName,String openId) { String sql = " set nocount on ; \n" + " declare @FromUserId varchar(50) = ? ,@SystemName varchar(200) = ?,@OpenId varchar(200) = ? \n" + " declare @userid varchar(50),@username varchar(50),@mobile varchar(50),@departids varchar(200),\n" + " @departnames varchar(50),@order varchar(50),@position varchar(50),@gender int,\n" + " @email varchar(200),@isleader int,@avatar varchar(500),@telephone varchar(50),\n" + " @english_name varchar(200),@extattr varchar(200),@status int,@enable int,@AvatarUnid varchar(50),\n" + " @WeiXinUserCode varchar(50),@CardImage varchar(50),@isAiRadarUser int,@Description varchar(max) \n" + " declare @ShopCcCode varchar(50),@ShopCcName varchar(80) ,@MiniAppTrialQrCode varchar(50),@RowId varchar(20) \n" + " declare @DefaultShopCcCode varchar(50),@isStartupLeagueShopCcCode int , @LeagueUserId varchar(50) \n" + " declare @ReferralsCode varchar(50) ,@LastUserId varchar(50) \n" + " declare @Hometown varchar(80),@HometownGreetings varchar(500),@MyTag varchar(500),@MyVoice varchar(50) \n" + " declare @MyVideo varchar(50),@MyVideoCover varchar(50),@MyPhoto varchar(500),@AboutUs varchar(50) \n" + " declare @LastSelectShopCcCode varchar(50),@KeFuTelephone varchar(50) \n" + " select top 1 @DefaultShopCcCode = DefaultShopCcCode ,@isStartupLeagueShopCcCode = isStartupLeagueShopCcCode from t714001 \n" + " select top 1 @CardImage = a.CardImage from t730118 a \n" //名片背景图 + " select @ReferralsCode = ReferralsCode,@Telephone = Tel,@LastSelectShopCcCode = LastSelectShopCcCode \n" + " from t730102 a where a.OpenId = @OpenId\n" + " if isnull(@Telephone,'') <> '' \n" //如果当前用户自己就是导购，则始终取自己的 userid ，如果不是，则取介绍人 + " begin\n" + " select @userid = a.userid,@username = a.name,@mobile = a.mobile,@departids = a.departids,\n" + " @departnames = dbo.f700108v1(a.departids) ,\n" + " @order = a.[order],@position = a.position,@gender = a.gender, \n" + " @email = a.email,@isleader = a.isleader,@avatar = a.avatar,@telephone = a.telephone,\n" + " @english_name = a.english_name,@extattr = a.extattr,@status = a.status,@enable = a.enable,\n" + " @AvatarUnid = a.AvatarUnid,@WeiXinUserCode = a.WeiXinUserCode,@isAiRadarUser = a.isAiRadarUser, \n" + " @ShopCcCode = a.ShopCcCode,@MiniAppTrialQrCode = a.MiniAppTrialQrCode,@RowId = a.RowId,@Description = a.Description, \n" + " @Hometown = a.Hometown,@HometownGreetings = a.HometownGreetings,@MyTag = a.MyTag,@MyVoice = a.MyVoice, \n" + " @MyVideo = a.MyVideo,@MyVideoCover = a.MyVideoCover,@MyPhoto = a.MyPhoto,@AboutUs = a.AboutUs \n" + " from t700107 a where (a.mobile = @telephone or a.telephone = @telephone) and isnull(a.isAiRadarUser,0) = 1 \n" + " if @@rowcount > 0 \n" + " begin \n" + " if isnull(@ShopCcCode,'') <> isnull(@LastSelectShopCcCode,'') \n" + " begin \n" + " update a set LastSelectShopCcCode = @ShopCcCode from t730102 a where a.OpenId = @OpenId \n" + " end \n" + " if isnull(@ShopCcCode,'') <> '' \n" + " begin\n" + " select @ShopCcName = CcName,@KeFuTelephone = case when isnull(Tel,'') <> '' then Tel else Tel2 end \n" + " from t110601 where cccode = @ShopCcCode\n" + " end else \n" + " begin \n" + " select top 1 @KeFuTelephone = case when isnull(Tel1,'') <> '' then Tel1 else Tel2 end from oCompany \n" + " end \n" + " select @userid as userid,@username as username,@mobile as mobile,@departids as departids,\n" + " @departnames as departnames ,@order as [order],@position as position,@gender as gender, \n" + " @email as email,@isleader as isleader,@avatar as avatar,@telephone as telephone, \n" + " @english_name as english_name,@extattr as extattr,@status as status,@enable as enable,\n" + " @AvatarUnid as AvatarUnid,@WeiXinUserCode as WeiXinUserCode,@CardImage as CardImage,@isAiRadarUser as isAiRadarUser, \n" + " @ShopCcCode as ShopCcCode,@ShopCcName as ShopCcName,@MiniAppTrialQrCode as MiniAppTrialQrCode,@RowId as RowId, \n" + " @Description as Description, \n" + " @Hometown as Hometown,@HometownGreetings as HometownGreetings,@MyTag as MyTag,@MyVoice as MyVoice, \n" + " @MyVideo as MyVideo,@MyVideoCover as MyVideoCover,@MyPhoto as MyPhoto,@AboutUs as AboutUs, \n" + " case when isnull(@KeFuTelephone,'') <> '' then @KeFuTelephone else @mobile end as KeFuTelephone \n" + " return \n" //结束 + " end \n" + " end \n" + " if isnull(@isStartupLeagueShopCcCode,0) = 0 and isnull(@ReferralsCode,'') <> '' \n" + " begin\n" + " set @FromUserId = @ReferralsCode \n" + " end \n" + " if isnull(@FromUserId,'') <> '' \n" + " begin \n" + " select @userid = a.userid,@username = a.name,@mobile = a.mobile,@departids = a.departids,\n" + " @departnames = dbo.f700108v1(a.departids) ,\n" + " @order = a.[order],@position = a.position,@gender = a.gender, \n" + " @email = a.email,@isleader = a.isleader,@avatar = a.avatar,@telephone = a.telephone,\n" + " @english_name = a.english_name,@extattr = a.extattr,@status = a.status,@enable = a.enable,\n" + " @AvatarUnid = a.AvatarUnid,@WeiXinUserCode = a.WeiXinUserCode,@isAiRadarUser = a.isAiRadarUser, \n" + " @ShopCcCode = a.ShopCcCode,@MiniAppTrialQrCode = a.MiniAppTrialQrCode,@RowId = a.RowId,@Description = a.Description, \n" + " @Hometown = a.Hometown,@HometownGreetings = a.HometownGreetings,@MyTag = a.MyTag,@MyVoice = a.MyVoice, \n" + " @MyVideo = a.MyVideo,@MyVideoCover = a.MyVideoCover,@MyPhoto = a.MyPhoto,@AboutUs = a.AboutUs \n" + " from t700107 a \n" + " where a.userid = @FromUserId and isnull(a.isAiRadarUser,0) = 1 \n" + " if isnull(@isStartupLeagueShopCcCode,0) = 1 and isnull(@userid,'') <> '' \n" // ----取联盟客服人员开始 ---- + " begin \n" + " select @LeagueUserId = a.LastUserId from t730121 a where a.ShopCcCode = isnull(@ShopCcCode,'') and a.OpenId = isnull(@OpenId,'') \n" + " if isnull(@LeagueUserId,'') = '' \n" + " begin \n" + " update a set LastUserId = @userid,LastUserName = @username \n" + " from t730121 a where a.ShopCcCode = isnull(@ShopCcCode,'') and a.OpenId = isnull(@OpenId,'') \n" + " if @@rowcount = 0 \n" + " begin \n" + " select @ShopCcName = CcName from t110601 where cccode = @ShopCcCode \n" + " insert into t730121(OpenId,NickName,WeiXinAvatarUnid,ShopCcCode,ShopCcName,CreateDate,LastUserId,LastUserName) \n" + " select a.OpenId,a.NickName,a.WeiXinAvatarUnid,isnull(@ShopCcCode,''),@ShopCcName,getdate(),@FromUserId,@username \n" + " from t730102 a where a.OpenId = isnull(@OpenId,'') \n" + " end \n" + " set @LeagueUserId = @userid \n" + " end \n" + " if isnull(@LeagueUserId,'') <> '' and isnull(@LeagueUserId,'') <> isnull(@FromUserId,'') \n" + " begin \n" + " select @userid = a.userid,@username = a.name,@mobile = a.mobile,@departids = a.departids,\n" + " @departnames = dbo.f700108v1(a.departids) ,\n" + " @order = a.[order],@position = a.position,@gender = a.gender, \n" + " @email = a.email,@isleader = a.isleader,@avatar = a.avatar,@telephone = a.telephone,\n" + " @english_name = a.english_name,@extattr = a.extattr,@status = a.status,@enable = a.enable,\n" + " @AvatarUnid = a.AvatarUnid,@WeiXinUserCode = a.WeiXinUserCode,@isAiRadarUser = a.isAiRadarUser, \n" + " @ShopCcCode = a.ShopCcCode,@MiniAppTrialQrCode = a.MiniAppTrialQrCode,@RowId = a.RowId,@Description = a.Description, \n" + " @Hometown = a.Hometown,@HometownGreetings = a.HometownGreetings,@MyTag = a.MyTag,@MyVoice = a.MyVoice, \n" + " @MyVideo = a.MyVideo,@MyVideoCover = a.MyVideoCover,@MyPhoto = a.MyPhoto,@AboutUs = a.AboutUs \n" + " from t700107 a \n" + " where a.userid = @LeagueUserId and isnull(a.isAiRadarUser,0) = 1 \n" + " end \n" // ----取联盟客服人员结束 ---- + " end \n" + " end \n" + " if isnull(@userid,'') = '' \n" + " begin \n" + " select top 1 @userid = a.userid,@username = a.name,@mobile = a.mobile,@departids = a.departids,\n" + " @departnames = dbo.f700108v1(a.departids) ,\n" + " @order = a.[order],@position = a.position,@gender = a.gender, \n" + " @email = a.email,@isleader = a.isleader,@avatar = a.avatar,@telephone = a.telephone,\n" + " @english_name = a.english_name,@extattr = a.extattr,@status = a.status,@enable = a.enable,\n" + " @AvatarUnid = a.AvatarUnid,@WeiXinUserCode = a.WeiXinUserCode ,@isAiRadarUser = a.isAiRadarUser,\n" + " @ShopCcCode = a.ShopCcCode,@MiniAppTrialQrCode = a.MiniAppTrialQrCode,@RowId = a.RowId,@Description = a.Description, \n" + " @Hometown = a.Hometown,@HometownGreetings = a.HometownGreetings,@MyTag = a.MyTag,@MyVoice = a.MyVoice, \n" + " @MyVideo = a.MyVideo,@MyVideoCover = a.MyVideoCover,@MyPhoto = a.MyPhoto,@AboutUs = a.AboutUs,@LastUserId = b.LastUserId \n" + " from t700107 a \n" + " join t730102 b on a.UserId = b.LastUserId \n" + " where b.OpenId = @OpenId and isnull(a.isAiRadarUser,0) = 1 \n" + " if @@rowcount = 0 \n" + " begin \n" + " select @userid = a.userid,@username = a.name,@mobile = a.mobile,@departids = a.departids,\n" + " @departnames = dbo.f700108v1(a.departids) ,\n" + " @order = a.[order],@position = a.position,@gender = a.gender, \n" + " @email = a.email,@isleader = a.isleader,@avatar = a.avatar,@telephone = a.telephone,\n" + " @english_name = a.english_name,@extattr = a.extattr,@status = a.status,@enable = a.enable,\n" + " @AvatarUnid = a.AvatarUnid,@WeiXinUserCode = a.WeiXinUserCode ,@isAiRadarUser = a.isAiRadarUser,\n" + " @ShopCcCode = a.ShopCcCode,@MiniAppTrialQrCode = a.MiniAppTrialQrCode,@RowId = a.RowId,@Description = a.Description, \n" + " @Hometown = a.Hometown,@HometownGreetings = a.HometownGreetings,@MyTag = a.MyTag,@MyVoice = a.MyVoice, \n" + " @MyVideo = a.MyVideo,@MyVideoCover = a.MyVideoCover,@MyPhoto = a.MyPhoto,@AboutUs = a.AboutUs \n" + " from t700107 a \n" + " where isnull(a.isAiRadarUser,0) = 1 and isnull( a.isAiRadarUserForDefault,0) = 1 \n" + " if @@rowcount = 0 \n" + " begin \n" + " raiserror('没有找到默认的名片用户信息，请在【%s】系统【700107】中为企业号设置至少一个【默认名片】用户,且该用户必须启用【AI雷达和名片】',16,1,@SystemName) \n" + " return \n" + " end \n" + " if isnull(@LastUserId,'') = '' \n" + " begin \n" + " update a set LastUserId = @userid from t730102 a where a.OpenId = @OpenId and isnull(a.LastUserId,'') = '' \n" + " end \n" + " update a set ReferralsCode = @userid ,ReferralsName = @username, ReferralsType = '企业微信' \n" + " from t730102 a where a.OpenId = @OpenId and isnull(a.ReferralsCode,'') = '' \n" + " end \n" + " end \n" + " if isnull(@ShopCcCode,'') <> isnull(@LastSelectShopCcCode,'') \n" + " begin \n" + " update a set LastSelectShopCcCode = @ShopCcCode from t730102 a where a.OpenId = @OpenId \n" + " end \n" + " if isnull(@ShopCcCode,'') <> '' \n" + " begin\n" + " select @ShopCcName = CcName,@KeFuTelephone = case when isnull(Tel,'') <> '' then Tel else Tel2 end \n" + " from t110601 where cccode = @ShopCcCode\n" + " end else \n" + " begin \n" + " select top 1 @KeFuTelephone = case when isnull(Tel1,'') <> '' then Tel1 else Tel2 end from oCompany \n" + " end \n" + " select @userid as userid,@username as username,@mobile as mobile,@departids as departids,\n" + " @departnames as departnames ,@order as [order],@position as position,@gender as gender, \n" + " @email as email,@isleader as isleader,@avatar as avatar,@telephone as telephone, \n" + " @english_name as english_name,@extattr as extattr,@status as status,@enable as enable,\n" + " @AvatarUnid as AvatarUnid,@WeiXinUserCode as WeiXinUserCode,@CardImage as CardImage,@isAiRadarUser as isAiRadarUser, \n" + " @ShopCcCode as ShopCcCode,@ShopCcName as ShopCcName,@MiniAppTrialQrCode as MiniAppTrialQrCode,@RowId as RowId,\n" + " @Description as Description, \n" + " @Hometown as Hometown,@HometownGreetings as HometownGreetings,@MyTag as MyTag,@MyVoice as MyVoice, \n" + " @MyVideo as MyVideo,@MyVideoCover as MyVideoCover,@MyPhoto as MyPhoto,@AboutUs as AboutUs, \n" + " case when isnull(@KeFuTelephone,'') <> '' then @KeFuTelephone else @mobile end as KeFuTelephone \n"; MyWxCpUser wxCpUser = null ; try { Map map = this.jdbcTemplate.queryForMap(sql,new Object[] {fromUserId,systemName,openId}); if (map != null ) { wxCpUser = ERPUserImpl.getWorkAppUserByMap(map) ; } }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } return wxCpUser; } @Override public Integer getUserActionLastActiveDay(String userId,String openId) { String sql = " set nocount on \n" + " declare @UserId varchar(50) = ? ,@OpenId varchar(200) = ?,@LastCreateDate datetime \n" + " select top 1 @LastCreateDate = CreateDate from t735005 \n" + " where userid = @UserId \n" + " and OpenId = @OpenId \n" + " order by a.CreateDate desc \n" + " select DATEDIFF(day,@LastCreateDate,getdate()) as Days \n" ; try { return this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId}); }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer getUserActionTimes(String userId,String openId,int days ) { String sql = " set nocount on \n" + " declare @UserId varchar(50) = ? ,@OpenId varchar(200) = ?,@days int = ? ,@Times int \n" + " declare @EndDay datetime \n" + " select @EndDay = convert(datetime,convert(varchar(10),getdate(),120)) \n" + " declare @BeginDay datetime \n" + " select @BeginDay = DATEADD(day,0 - isnull(@days,0),@EndDay) \n" + " select @Times = count(1) from t735005 \n" + " where userid = @UserId \n" + " and OpenId = @OpenId \n" + " and CreateDate between @BeginDay and @EndDay \n" + " select isnull(@Times,0) as Times \n" ; try { return this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId,days}); }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer updateLastUserIdAndShopCcCode(String userId,String shopCcCode,String openId ) { String sql = " set nocount on \n" + " declare @LastUserId varchar(50) = ? ,@LastSelectShopCcCode varchar(50) = ?, @OpenId varchar(200) = ? \n" + " declare @Tel varchar(50) ,@Name varchar(50) \n" + " select @Tel = case when isnull(a.mobile,'') <> '' then a.mobile else a.Telephone end," + " @Name = a.Name \n" + " from t700107 a where UserId = @LastUserId \n" + " if @@rowcount = 0 \n" + " begin \n" + " raiserror('名片用户【%s】不存在，它来自于企业微信用户，请【功能号：700107】核实',16,1,@LastUserId) \n" + " return \n" + " end \n" + " update a set LastUserId = @LastUserId ,LastSelectShopCcCode = @LastSelectShopCcCode ,\n" + " ReferralsType = case when isnull(a.ReferralsType,'') <> '' then a.ReferralsType else '企业微信' end ,\n" + " ReferralsCode = case when isnull(a.ReferralsCode,'') <> '' then a.ReferralsCode else @LastUserId end , \n" + " ReferralsName = case when isnull(a.ReferralsName,'') <> '' then a.ReferralsName else @Name end \n" + " from t730102 a where a.openid = @OpenId \n" + " select @@rowcount \n" ; try { return this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,shopCcCode,openId}); }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer updateTelephone(String telephone,String openId ) { String sql = " set nocount on \n" + " declare @telephone varchar(50) = ? ,@OpenId varchar(200) = ? \n" + " update a set Tel = @telephone from t730102 a where a.openid = @OpenId \n" + " select @@rowcount \n" ; try { return this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {telephone,openId}); }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public List getUserLastVisitList(String userId) { return getUserLastVisitList( userId,null) ; } @Override public List getUserLastVisitList(String userId,Integer showCount) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ? , @ViewMyCardTimes int,@i int = 0,@CltCode varchar(50) \n" + " declare @table table(OpenId varchar(200) Primary Key ,CreateTime datetime)\n" + " while isnull(@i,0) < " + (showCount == null ? 6: showCount) + " \n" + " begin \n" + " insert into @table (OpenId,CreateTime)\n" + " select top 1 a.OpenId ,a.CreateTime \n" + " from t735005 a \n" + " join t730102 b on a.OpenId = b.OpenId \n" + " where a.ActionType = 'ViewMyCard' and a.UserId = @UserId\n" + " and not exists(select 1 from @table b where a.OpenId = b.OpenId )\n" + " and isnull(b.WeiXinAvatarUnid,'') <> '' \n" + " order by a.CreateTime desc\n" + " set @i = isnull(@i,0) + 1\n" + " end \n" + " select @ViewMyCardTimes = count(1) from t735005 a \n" + " where a.ActionType = 'ViewMyCard' and a.UserId = @UserId\n" //+ " select top 1 @CltCode = CltCode from t110203 where OpenId in (select list from getinstr( @OpenIds)) \n" + " select a.Subscribe,a.OpenId,a.NickName,a.Sex,a.City,a.Country,a.Province,a.Language,\n" + " a.WeiXinAvatarUnid,a.Headimgurl,a.SubscribeTime,a.Unionid,a.Remark,a.GroupSeq,\n" + " b.GroupId,b.GroupName,a.ReferralsCode,a.ReferralsName,a.ReferralsGroup,a.FromWx, \n" + " a.PermanentQrCode,@CltCode as CltCode,a.ActualName,a.Position,a.CorporationName,\n" + " a.Address,a.Tel,c.ShopCcCode,d.ccname as ShopCcName,@ViewMyCardTimes as ViewMyCardTimes \n" + " from t730102 a left join t730101 b on a.GroupSeq = b.GroupSeq \n" + " left join t110601 c on a.ReferralsCcCode = c.cccode \n" + " left join t110601 d on c.ShopCcCode = d.cccode \n" + " join @table g on a.OpenId = g.OpenId \n" + " order by g.CreateTime desc \n"; List wxMpUserList = null ; try { List> list = this.jdbcTemplate.queryForList(sql,userId); for (int i = 0 ;list != null && i < list.size();i++) { if (wxMpUserList == null) wxMpUserList = new ArrayList() ; wxMpUserList.add(getMyWxMpUser( list.get(i))) ; } return wxMpUserList ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public List getUserByOpenIds(String openIds) { String sql = "set nocount on ; \n" + " declare @OpenIds varchar(max) = ? ,@CltCode varchar(50) \n" + " select top 1 @CltCode = CltCode from t110203 where MiniAppOpenId in (select list from getinstr( @OpenIds)) \n" + " select a.Subscribe,a.OpenId,a.NickName,a.Sex,a.City,a.Country,a.Province,a.Language,\n" + " a.WeiXinAvatarUnid,a.Headimgurl,a.SubscribeTime,a.Unionid,a.Remark,a.GroupSeq,\n" + " b.GroupId,b.GroupName,a.ReferralsCode,a.ReferralsName,a.ReferralsGroup,a.FromWx, \n" + " a.PermanentQrCode,@CltCode as CltCode,a.ActualName,a.Position,a.CorporationName,\n" + " a.Address,a.Tel,c.ShopCcCode,d.ccname as ShopCcName \n" + " from t730102 a left join t730101 b on a.GroupSeq = b.GroupSeq \n" + " left join t110601 c on a.ReferralsCcCode = c.cccode \n" + " left join t110601 d on c.ShopCcCode = d.cccode \n" + " where (isnull(@OpenIds,'') = '' or a.OpenId in (select list from getinstr( @OpenIds))) ; \n"; List wxMpUserList = null ; try { List> list = this.jdbcTemplate.queryForList(sql,openIds); for (int i = 0 ;list != null && i < list.size();i++) { if (wxMpUserList == null) wxMpUserList = new ArrayList() ; wxMpUserList.add(getMyWxMpUser( list.get(i))) ; } return wxMpUserList ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public MyWxMpUser getUserByTelephone(String telephone) { String sql = "set nocount on ; \n" + " declare @Telephone varchar(200) = ? ,@CltCode varchar(50) \n" + " select top 1 @CltCode = CltCode from t110203 where Tel = @Telephone \n" + " select top 1 a.Subscribe,a.OpenId,a.NickName,a.Sex,a.City,a.Country,a.Province,a.Language,\n" + " a.WeiXinAvatarUnid,a.Headimgurl,a.SubscribeTime,a.Unionid,a.Remark,a.GroupSeq,\n" + " b.GroupId,b.GroupName,a.ReferralsType,a.ReferralsCode,a.ReferralsName,a.ReferralsGroup,a.FromWx, \n" + " a.PermanentQrCode,@CltCode as CltCode,a.ActualName,a.Position,a.CorporationName,\n" + " a.Address,a.Tel,c.ShopCcCode,d.ccname as ShopCcName \n" + " from t730102 a left join t730101 b on a.GroupSeq = b.GroupSeq \n" + " left join t110601 c on a.ReferralsCcCode = c.cccode \n" + " left join t110601 d on c.ShopCcCode = d.cccode \n" + " where a.Tel = @Telephone ; \n"; MyWxMpUser wxMpUser = null ; try { Map map = this.jdbcTemplate.queryForMap(sql,telephone); if (map != null ) { wxMpUser = getMyWxMpUser( map) ; } }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } return wxMpUser; } @Override public boolean isShopGuide(String openId) { String sql = "set nocount on ; \n" + " declare @OpenId varchar(200) = ? ,@Telephone varchar(50) , @isFound int = 0 \n" + " select top 1 @Telephone = a.Tel from t730102 a where a.OpenId = @OpenId \n" + " if isnull(@Telephone,'') <> '' and exists(select top 1 1 from t700107 a where a.Mobile = @Telephone and isnull(a.isAiRadarUser,0) = 1 ) \n" + " begin \n" + " set @isFound = 1 \n" + " end \n" + " select @isFound as isFound \n"; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {openId}); return (ret != null && ret.intValue() == 1?true:false ) ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return false ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } private String getUserSql() { String sql = //获取币种 " declare @Currency varchar(20),@CurrencyName nvarchar(80),@meno varchar(20),@Domestic int \n" + " select top 1 @CltCode = CltCode,@Currency = Currency from t110203 where MiniAppOpenId = @OpenId \n" + " if isnull(@Currency,'') = '' \n" + " begin \n " + " select top 1 @Currency = Currency from t714001 ; \n" + " end \n " + " if isnull(@Currency,'') = '' \n" + " begin \n" + " select top 1 @Currency = Currency,@CurrencyName = CurrencyName,@meno = meno,@Domestic = Domestic \n" + " from t110703 where isnull(Domestic,0) = 1 order by Currency asc; \n" + " end else \n" + " begin \n" + " select top 1 @Currency = Currency,@CurrencyName = CurrencyName,@meno = meno,@Domestic = Domestic " + " from t110703 where Currency = @Currency order by Currency asc; \n" + " end \n" + " select a.Subscribe,a.OpenId,a.NickName,a.Sex,a.City,a.Country,a.Province,a.Language,\n" + " a.WeiXinAvatarUnid,a.Headimgurl,a.SubscribeTime,a.Unionid,a.Remark,a.GroupSeq,\n" + " b.GroupId,b.GroupName,a.ReferralsType,a.ReferralsCode,a.ReferralsName,a.ReferralsGroup,a.FromWx, \n" + " a.PermanentQrCode,@CltCode as CltCode,a.ActualName,a.Position,a.CorporationName,\n" + " a.Address,a.Tel,c.ShopCcCode,d.ccname as ShopCcName , \n" + " case when isnull(a.NickName,'') = '' then 1 else 0 end as isPullUserInfo , \n" //是否需要拉取用户头像 + " a.EffectiveStartDate,a.EffectiveEndDate, \n" + " case when @Today between a.EffectiveStartDate and a.EffectiveEndDate then 0 else 1 end as Expired, \n" + " case when a.EffectiveEndDate is null then null else DATEDIFF(day,a.EffectiveEndDate,@Today) end ExpiryDays, \n" + " @Currency as Currency,@CurrencyName as CurrencyName,@meno as CurrencySign \n" + " from t730102 a left join t730101 b on a.GroupSeq = b.GroupSeq \n" + " left join t110601 c on a.ReferralsCcCode = c.cccode \n" + " left join t110601 d on c.ShopCcCode = d.cccode \n" + " where a.OpenId = @OpenId ; \n"; return sql ; } @Override public MyWxMpUser getUser(String openId) { String sql = "set nocount on ; \n" + " declare @OpenId varchar(200) = ? \n" + " declare @CltCode varchar(50) ,@Today datetime = convert(varchar(10),getdate(),120) \n" + getUserSql(); MyWxMpUser wxMpUser = null ; try { Map map = this.jdbcTemplate.queryForMap(sql,openId); if (map != null ) { wxMpUser = getMyWxMpUser( map) ; } }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } return wxMpUser; } private MyWxMpUser getMyWxMpUser(Map map){ if (map == null ) return null ; MyWxMpUser wxMpUser = new MyWxMpUser() ; wxMpUser.setSubscribe(map.get("Subscribe") == null||((Integer) map.get("Subscribe")).equals(0) ?false: true); wxMpUser.setOpenId(map.get("OpenId") == null?"": (String) map.get("OpenId")); wxMpUser.setNickname(map.get("NickName") == null?"": (String) map.get("NickName")); wxMpUser.setSex(map.get("Sex") == null?1: (map.get("Sex").equals("男")?1:2)); wxMpUser.setCity(map.get("City") == null?"": (String) map.get("City")); wxMpUser.setCountry(map.get("Country") == null?"": (String) map.get("Country")); wxMpUser.setProvince(map.get("Province") == null?"": (String) map.get("Province")); wxMpUser.setLanguage(map.get("Language") == null?"": (String) map.get("Language")); wxMpUser.setWeiXinAvatarUnid(map.get("WeiXinAvatarUnid") == null ? "": (String)map.get("WeiXinAvatarUnid")); wxMpUser.setHeadImgUrl(map.get("Headimgurl") == null?"": (String) map.get("Headimgurl")); Date date = map.get("SubscribeTime") == null?null: (Date) map.get("SubscribeTime") ; if (date != null) wxMpUser.setSubscribeTime(date.getTime()/1000L); //转换为时间戳 wxMpUser.setUnionId(map.get("Unionid") == null?"": (String) map.get("Unionid")); wxMpUser.setRemark(map.get("Remark") == null?"": (String) map.get("Remark")); wxMpUser.setGroupId(map.get("GroupId") == null?0: (Integer) map.get("GroupId")); wxMpUser.setGroupSeq(map.get("GroupSeq") == null?0: (Integer) map.get("GroupSeq")); wxMpUser.setGroupName(map.get("GroupName") == null?"": (String) map.get("GroupName")); wxMpUser.setReferralsType(map.get("ReferralsType") == null?"": (String)map.get("ReferralsType")); wxMpUser.setReferralsCode(map.get("ReferralsCode") == null?"": (String) map.get("ReferralsCode")); wxMpUser.setReferralsName(map.get("ReferralsName") == null?"": (String) map.get("ReferralsName")); wxMpUser.setReferralsGroup(map.get("ReferralsGroup") == null ? "": (String)map.get("ReferralsGroup")); wxMpUser.setFromWx(map.get("FromWx") == null?"": (String) map.get("FromWx")); wxMpUser.setPermanentQrCode(map.get("PermanentQrCode") == null?"": (String) map.get("PermanentQrCode")); wxMpUser.setCltCode(map.get("CltCode") == null?"": (String) map.get("CltCode")); wxMpUser.setActualName(map.get("ActualName") == null?"": (String) map.get("ActualName")); wxMpUser.setPosition(map.get("Position") == null?"": (String) map.get("Position")); wxMpUser.setCorporationName(map.get("CorporationName") == null?"": (String) map.get("CorporationName")); wxMpUser.setAddress(map.get("Address") == null?"": (String) map.get("Address")); wxMpUser.setTel(map.get("Tel") == null?"": (String) map.get("Tel")); wxMpUser.setShopCcCode(map.get("ShopCcCode") == null?"": (String) map.get("ShopCcCode")); wxMpUser.setShopCcName(map.get("ShopCcName") == null?"": (String) map.get("ShopCcName")); wxMpUser.setViewMyCardTimes(map.get("ViewMyCardTimes") == null?0: (Integer) map.get("ViewMyCardTimes")); wxMpUser.setPullUserInfo(map.get("isPullUserInfo") != null&&map.get("isPullUserInfo").equals(1) ?true:false); wxMpUser.setEffectiveStartDate(map.get("EffectiveStartDate")==null?null:(Date)map.get("EffectiveStartDate")); wxMpUser.setEffectiveStartDate(map.get("EffectiveEndDate")==null?null:(Date)map.get("EffectiveEndDate")); wxMpUser.setExpired(map.get("Expired")!=null&&map.get("Expired").equals(1)?true:false); wxMpUser.setExpiryDays(map.get("ExpiryDays")==null?null:(Integer)map.get("ExpiryDays")); wxMpUser.setCurrency(map.get("Currency") == null?"": (String) map.get("Currency")); wxMpUser.setCurrencyName(map.get("CurrencyName") == null?"": (String) map.get("CurrencyName")); wxMpUser.setCurrencySign(map.get("CurrencySign") == null?"": (String) map.get("CurrencySign")); return wxMpUser ; } @Override public Integer getLikeActionTimes(String userId,String openId,String actionType,String actionName) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ? ,@ActionType varchar(50) = ?, @ActionName varchar(50) = ? \n" + " select count(1) \n" + " from t735007 a \n" + " where (isnull(@UserId,'') = '' or a.UserId = @UserId)\n" + " and (isnull(@OpenId,'') = '' or a.OpenId = @OpenId)\n" + " and (isnull(@ActionType,'') = '' or a.ActionType = @ActionType)" + " and (isnull(@ActionName,'') = '' or a.ActionName = @ActionName ) \n"; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId,actionType,actionName}); return ret ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public List getLikeActionList(String userId,String openId,String actionType,String actionName,Integer rowCount) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ? ,@ActionType varchar(50) = ?, @ActionName varchar(50) = ? \n" + " select top " + rowCount + " a.OpenId,b.NickName,b.WeiXinAvatarUnid ,a.UserId ,c.Name as UserName,a.ActionType ,a.ActionName ,\n" + " a.CreateTime,a.CreateDate ,\n" + " a.MatCode,d.MatName,d.Special,a.Title,e.FormName,a.RefCode,a.RefFormId,a.RefFormType,c.ShopCcCode\n" + " from t735007 a \n" + " join t730102 b on a.OpenId = b.OpenId\n" + " join t700107 c on a.UserId = c.userid\n" + " left join t110503 d on a.MatCode = d.MatCode \n" + " left join gform e on a.RefFormId = e.formId \n" + " where (isnull(@UserId,'') = '' or a.UserId = @UserId)\n" + " and (isnull(@OpenId,'') = '' or a.OpenId = @OpenId)\n" + " and (isnull(@ActionType,'') = '' or a.ActionType = @ActionType)" + " and (isnull(@ActionName,'') = '' or a.ActionName = @ActionName ) \n" + " order by a.CreateTime desc \n" ; List likeActionList = new ArrayList() ; try { List> list = this.jdbcTemplate.queryForList(sql,new Object[] {userId,openId,actionType,actionName}); for (int i = 0 ;list != null && i < list.size() ; i ++) { Map map = list.get(i) ; LikeActionEntity likeActionEntity = new LikeActionEntity() ; likeActionEntity.setOpenId(map.get("OpenId")==null?"":(String)map.get("OpenId")) ; likeActionEntity.setNickName(map.get("NickName")==null?"":(String)map.get("NickName")) ; likeActionEntity.setWeiXinAvatarUnid(map.get("WeiXinAvatarUnid")==null?"":(String)map.get("WeiXinAvatarUnid")) ; likeActionEntity.setUserId(map.get("UserId")==null?"":(String)map.get("UserId")) ; likeActionEntity.setUserName(map.get("UserName")==null?"":(String)map.get("UserName")) ; likeActionEntity.setActionType(map.get("ActionType")==null?"":(String)map.get("ActionType")) ; likeActionEntity.setActionName(map.get("ActionName")==null?"":(String)map.get("ActionName")) ; likeActionEntity.setCreateTime(map.get("CreateTime")==null?null:(Date)map.get("CreateTime")) ; likeActionEntity.setCreateDate(map.get("CreateDate")==null?null:(Date)map.get("CreateDate")) ; likeActionEntity.setMatCode(map.get("MatCode")==null?"":(String)map.get("MatCode")) ; likeActionEntity.setMatName(map.get("MatName")==null?"":(String)map.get("MatName")) ; likeActionEntity.setSpecial(map.get("Special")==null?"":(String)map.get("Special")) ; likeActionEntity.setTitle(map.get("Title")==null?"":(String)map.get("Title")) ; likeActionEntity.setFormName(map.get("FormName")==null?"":(String)map.get("FormName")) ; likeActionEntity.setRefCode(map.get("RefCode")==null?"":(String)map.get("RefCode")) ; likeActionEntity.setRefFormId(map.get("RefFormId")==null?null:(Integer)map.get("RefFormId")) ; likeActionEntity.setRefFormType(map.get("RefFormType")==null?null:(Integer)map.get("RefFormType")) ; likeActionEntity.setShopCcCode(map.get("ShopCcCode")==null?"":(String)map.get("ShopCcCode")) ; likeActionList.add(likeActionEntity) ; } return likeActionList ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer addLikeAction(String userId,String openId,String actionType,String actionName) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ? ,@ActionType varchar(50) = ?, @ActionName varchar(50) = ? \n" + " declare @CreateTime datetime ,@CreateDate datetime,@MatCode varchar(50) \n" + " declare @Title varchar(80),@FormName varchar(80),@RefCode varchar(50),@RefFormId int,@RefFormType int \n" + " declare @myrowcount int = 0 \n" + " if not exists(select 1 from t735007 a " + " where (isnull(@UserId,'') = '' or a.UserId = @UserId)\n" + " and (isnull(@OpenId,'') = '' or a.OpenId = @OpenId)\n" + " and (isnull(@ActionType,'') = '' or a.ActionType = @ActionType)" + " and (isnull(@ActionName,'') = '' or a.ActionName = @ActionName )) \n" + " begin \n" + " select @CreateTime = getdate() \n" + " select @CreateDate = convert(datetime,convert(varchar(10),@CreateTime,120)) \n" + " if isnull(@ActionType,'') = 'LikeProductDetail' set @MatCode = @ActionName \n" + " if isnull(@ActionType,'') = 'LikeInformation' \n" + " begin \n" + " set @RefCode = @ActionName \n" + " select @Title = a.Title,@RefFormId = a.FormId from t714031H a where a.DocCode = @RefCode \n" + " select @RefFormType = a.FormType from _sysmenu a where a.formid = @RefFormId \n" + " end \n" + " insert into t735007(OpenId,UserId,ActionType,ActionName,CreateTime,MatCode,Title,FormName,RefCode,RefFormId,RefFormType,CreateDate) \n" + " values(@OpenId,@UserId,@ActionType,@ActionName ,@CreateTime,@MatCode,@Title,@FormName,@RefCode,@RefFormId,@RefFormType,@CreateDate ) \n" + " set @myrowcount = @@rowcount \n" + " end \n" + " select @myrowcount \n" ; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId,actionType,actionName}); return ret ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer deleteLikeAction(String userId,String openId,String actionType,String actionName) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ? ,@ActionType varchar(50) = ?, @ActionName varchar(50) = ?,@myrowcount int \n" + " delete a from t735007 a \n" + " where (isnull(@UserId,'') = '' or a.UserId = @UserId)\n" + " and (isnull(@OpenId,'') = '' or a.OpenId = @OpenId)\n" + " and (isnull(@ActionType,'') = '' or a.ActionType = @ActionType)" + " and (isnull(@ActionName,'') = '' or a.ActionName = @ActionName ) \n" + " set @myrowcount = @@rowcount \n" + " select @myrowcount \n" ; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId,actionType,actionName}); return ret ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public List getEducationList(String userId,String openId) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ? ,@OpenId varchar(200) = ? \n" + " select a.DocCode,a.RowId,a.DocItem,a.SchoolCode,a.SchoolName,a.Education,a.Major,\n" + " a.LearningStartDate,a.LearningEndDate,a.SchoolmateGreetings, \n" + " case when exists(select 1 from t700129 c where a.DocCode = c.SchoolDocCode \n" + " and a.RowId = c.SchoolRowId and c.OpenId = @OpenId) then 1 else 0 end as isAlumni \n" + " from t700125 a join t700107 b on a.DocCode = b.DocCode \n" + " where b.UserId = @UserId \n" + " order by a.DocItem asc \n" ; try { List educationList = new ArrayList() ; List> list = this.jdbcTemplate.queryForList(sql,new Object[] {userId,openId}); for (int i = 0 ;list != null && i < list.size() ; i ++) { Map map = list.get(i) ; EducationEntity educationEntity = new EducationEntity() ; educationEntity.setDocCode(map.get("DocCode")==null?"":(String)map.get("DocCode")) ; educationEntity.setRowId(map.get("RowId")==null?"":(String)map.get("RowId")) ; educationEntity.setDocItem(map.get("DocItem")==null?null:(Integer)map.get("DocItem")) ; educationEntity.setSchoolCode(map.get("SchoolCode")==null?"":(String)map.get("SchoolCode")) ; educationEntity.setSchoolName(map.get("SchoolName")==null?"":(String)map.get("SchoolName")) ; educationEntity.setEducation(map.get("Education")==null?"":(String)map.get("Education")) ; educationEntity.setMajor(map.get("Major")==null?"":(String)map.get("Major")) ; educationEntity.setLearningStartDate(map.get("LearningStartDate")==null?null:(Date)map.get("LearningStartDate")) ; educationEntity.setLearningEndDate(map.get("LearningEndDate")==null?null:(Date)map.get("LearningEndDate")) ; educationEntity.setSchoolmateGreetings(map.get("SchoolmateGreetings")==null?"":(String)map.get("SchoolmateGreetings")) ; educationEntity.setSchoolfellow(map.get("isAlumni")!=null&&map.get("isAlumni").equals(1)?true:false) ; educationList.add(educationEntity) ; } return educationList ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public boolean isHometown(String userId,String openId) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ?,@Found int \n" + " if exists(select 1 from t700128 a join t700107 b on a.DocCode = b.DocCode \n" + " where a.OpenId = @OpenId and b.UserId = @UserId ) \n" + " set @Found = 1 else set @Found = 0 \n" + " select isnull(@Found,0) \n" ; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId}); return (ret!=null&&ret.intValue()==1?true:false) ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return false ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer addHometown(String userId,String openId) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ?,@MyRowCount int = 0 ,@DocCode varchar(20) \n" + " declare @RowId varchar(20),@NickName nvarchar(200),@WeiXinAvatarUnid varchar(200),@Telephone varchar(50) \n" + " if not exists(select 1 from t700128 a join t700107 b on a.DocCode = b.DocCode \n" + " where a.OpenId = @OpenId and b.UserId = @UserId ) \n" + " begin \n" + " exec getXXXX @RowId output \n" + " select @NickName = NickName ,@WeiXinAvatarUnid = WeiXinAvatarUnid,@Telephone = Tel from t730102 a where a.OpenId = @Openid \n" + " select @DocCode = DocCode from t700107 where UserId = @UserId \n" + " if isnull(@DocCode,'') <> '' \n" + " begin \n" + " insert into t700128 (DocCode,RowId,OpenId,NickName ,WeiXinAvatarUnid ,CreateTime,Telephone) \n" + " values(@DocCode,@RowId,@OpenId,@NickName ,@WeiXinAvatarUnid,getdate(),@Telephone ) \n" + " set @MyRowCount = @@rowcount \n" + " end \n" + " end \n" + " select isnull(@MyRowCount,0) \n"; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId}); return ret ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer deleteHometown(String userId,String openId) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ?,@MyRowCount int = 0 ,@DocCode varchar(20) \n" + " select @DocCode = DocCode from t700107 where UserId = @UserId \n" + " delete from t700128 where DocCode = @DocCode and OpenId = @OpenId \n" + " set @MyRowCount = @@rowcount \n" + " select isnull(@MyRowCount,0) \n"; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId}); return ret ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer addSchoolfellow(String userId,String openId,String schoolDocCode,String schoolRowId) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ?,@SchoolDocCode varchar(20) = ?,@SchoolRowId varchar(20) = ? \n" + " declare @MyRowCount int = 0 ,@DocCode varchar(20),@SchoolName varchar(80),@Telephone varchar(50) \n" + " declare @RowId varchar(20),@NickName nvarchar(200),@WeiXinAvatarUnid varchar(200) \n" + " if not exists(select 1 from t700129 a join t700107 b on a.DocCode = b.DocCode \n" + " where a.OpenId = @OpenId and b.UserId = @UserId and a.SchoolDocCode = @SchoolDocCode and a.SchoolRowId = @SchoolRowId ) \n" + " begin \n" + " exec getXXXX @RowId output \n" + " select @NickName = NickName ,@WeiXinAvatarUnid = WeiXinAvatarUnid,@Telephone = Tel from t730102 a where a.OpenId = @Openid \n" + " select @DocCode = DocCode from t700107 where UserId = @UserId \n" + " select @SchoolName = SchoolName from t700125 where DocCode = @SchoolDocCode and RowId = @SchoolRowId \n" + " if isnull(@DocCode,'') <> '' \n" + " begin \n" + " insert into t700129 (DocCode,RowId,OpenId,NickName ,WeiXinAvatarUnid ,SchoolDocCode,SchoolRowId,CreateTime,SchoolName,Telephone) \n" + " values(@DocCode,@RowId,@OpenId,@NickName ,@WeiXinAvatarUnid,@SchoolDocCode,@SchoolRowId,getdate(),@SchoolName ,@Telephone) \n" + " set @MyRowCount = @@rowcount \n" + " end \n" + " end \n" + " select isnull(@MyRowCount,0) \n"; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId,schoolDocCode,schoolRowId}); return ret ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } @Override public Integer deleteSchoolfellow(String userId,String openId,String schoolDocCode,String schoolRowId) { String sql = "set nocount on ; \n" + " declare @UserId varchar(50) = ?,@OpenId varchar(200) = ?,@SchoolDocCode varchar(20) = ?,@SchoolRowId varchar(20) = ? \n" + " declare @MyRowCount int = 0 ,@DocCode varchar(20) \n" + " select @DocCode = DocCode from t700107 where UserId = @UserId \n" + " delete from t700129 where DocCode = @DocCode and OpenId = @OpenId and SchoolDocCode = @SchoolDocCode and SchoolRowId = @SchoolRowId \n" + " set @MyRowCount = @@rowcount \n" + " select isnull(@MyRowCount,0) \n"; try { Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class,new Object[] {userId,openId,schoolDocCode,schoolRowId}); return ret ; }catch (DataAccessException e ) { if (e instanceof EmptyResultDataAccessException) { return null ; } else { //e.printStackTrace(); throw e ; } } catch (Exception e) { //e.printStackTrace(); throw e ; } } }