package com.yc.sdk.miniapp.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Service;

import com.yc.sdk.miniapp.entity.LivePushMessageApiNotifyEntity;
import com.yc.sdk.miniapp.entity.LiveReplayVideoEntity;
import com.yc.sdk.miniapp.entity.LiveReplayVideoEntity.ReplayVideoEntity;
import com.yc.sdk.miniapp.entity.LiveRoomEntity;
import com.yc.sdk.miniapp.entity.LiveRoomEntity.RoomInfo;
import com.yc.sdk.shopping.entity.CreateLiveRoomParameterStep1Entity;
import com.yc.sdk.shopping.entity.CreateLiveRoomParameterStep2Entity;
import com.yc.sdk.shopping.entity.CreateLiveRoomParameterStep3Entity;
import com.yc.sdk.shopping.entity.GoodsSortInRoomEntity;
import com.yc.sdk.shopping.entity.GoodsSortInRoomPamaraterEntity;
import com.yc.sdk.shopping.entity.LiveSubscribeUserEntity;
import com.yc.sdk.miniapp.entity.LiveRoomImageEntity;
import com.yc.action.grid.GridUtils;
import com.yc.sdk.miniapp.entity.LiveAssistantEntity;
import com.yc.sdk.miniapp.entity.LiveAssistantEntity.AssistantItemEntity;
import com.yc.sdk.miniapp.entity.LiveGoodsEntity;
import com.yc.service.BaseService;

import cn.binarywang.wx.miniapp.bean.live.WxMaAssistantResult;
import cn.binarywang.wx.miniapp.bean.live.WxMaLiveAssistantInfo;
import cn.binarywang.wx.miniapp.bean.live.WxMaLiveResult;
import cn.binarywang.wx.miniapp.bean.live.WxMaLiveResult.LiveReplay;

@Service("MaLiveImpl")
public class MaLiveImpl  extends BaseService implements MaLiveIfc {

	@Override
	public List<LiveRoomImageEntity> updateLiveRoom(WxMaLiveResult wxMaLiveResult) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try{
			List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
			String sql = " set nocount on ; \n"
					+ " declare @myRowCount int = 0 \n"
					+ " declare @table table(RoomId int,FieldId varchar(50), RoomImg varchar(500) ) \n"
					+ " declare @CoverImg varchar(500),@ShareImg varchar(500),@AnchorImg varchar(500),@FeedsImg varchar(500) \n"
					+ " declare @CoverImgUnid varchar(500),@ShareImgUnid varchar(500),@AnchorImgUnid varchar(500),@FeedsImgUnid varchar(500) \n"
					+ " declare @DocCode varchar(20),@Type int,@UserCode varchar(50),@UserName varchar(50) \n"
					+ " declare @Today datetime = convert(varchar(10),getdate(),120) \n"
					+ " declare @FormId int = 710701, @DocStatus int = 0   \n"
					+ " declare @PeriodId varchar(20),@CompanyId varchar(20),@CompanyName varchar(80),@CcCode varchar(20),@CcName varchar(50) \n"
					+ " declare @GoodsId bigint,@MatCode varchar(50) , @DepositDocCode varchar(50) , @DepositRowId varchar(20) \n";

			for (int i = 0 ;wxMaLiveResult!=null&& wxMaLiveResult.getRoomInfos()!= null  && i < wxMaLiveResult.getRoomInfos().size();i++) {
				WxMaLiveResult.RoomInfo roomInfo = wxMaLiveResult.getRoomInfos().get(i) ;
				Date startTime = new Date(roomInfo.getStartTime()*1000) ;
				Date endTime = new Date(roomInfo.getEndTime()*1000) ;
				
				sql += " select @DocCode = a.DocCode,@CoverImg = a.CoverImg,@ShareImg = a.ShareImg,@AnchorImg = AnchorImg,@FeedsImg = a.FeedsImg, \n"
						+ "    @CoverImgUnid = a.CoverImgUnid,@ShareImgUnid = a.ShareImgUnid,@AnchorImgUnid = a.AnchorImgUnid,@FeedsImgUnid = a.FeedsImgUnid \n"
						+ " from t710701 a where a.RoomId = "+ roomInfo.getRoomId() + " \n"
						+ " if @@rowcount > 0 \n"
						+ " begin \n"
						+ "    if isnull(@CoverImgUnid,'') = '' or isnull(@CoverImg,'') <> '"  + (roomInfo.getCoverImg()==null?"":roomInfo.getCoverImg()) + "' \n"
						+ "    begin  \n"
						+ "        insert into @table(RoomId,FieldId,RoomImg) \n"
						+ "        values (" + roomInfo.getRoomId() + ",'CoverImgUnid','" + (roomInfo.getCoverImg()==null?"":roomInfo.getCoverImg()) + "')  \n"
						+ "    end \n"
						+ "    if isnull(@ShareImgUnid,'') = '' or isnull(@ShareImg,'') <> '"  + (roomInfo.getShareImg() ==null?"":roomInfo.getShareImg()) + "' \n"
						+ "    begin  \n"
						+ "        insert into @table(RoomId,FieldId,RoomImg) \n"
						+ "        values (" + roomInfo.getRoomId() + ",'ShareImgUnid','" + (roomInfo.getShareImg()==null?"":roomInfo.getShareImg()) + "')  \n"
						+ "    end \n"
						+ "    if isnull(@AnchorImgUnid ,'') = '' or isnull(@AnchorImg,'') <> '"  + (roomInfo.getAnchorImg()==null?"": roomInfo.getAnchorImg()) + "' \n"
						+ "    begin  \n"
						+ "        insert into @table(RoomId,FieldId,RoomImg) \n"
						+ "        values (" + roomInfo.getRoomId() + ",'AnchorImgUnid','" + (roomInfo.getAnchorImg()==null?"":roomInfo.getAnchorImg()) + "')  \n"
						+ "    end \n"
						+ "    if isnull(@FeedsImgUnid ,'') = '' or isnull(@FeedsImg,'') <> '"  + (roomInfo.getFeedsImg()==null?"":roomInfo.getFeedsImg()) + "' \n"
						+ "    begin  \n"
						+ "        insert into @table(RoomId,FieldId,RoomImg) \n"
						+ "        values (" + roomInfo.getRoomId() + ",'FeedsImgUnid','" + (roomInfo.getAnchorImg()==null?"":roomInfo.getAnchorImg()) + "')  \n"
						+ "    end \n"
						+ "    update a set RoomName = '" + (roomInfo.getName()==null?"":roomInfo.getName()) + "',CoverImg = '" + (roomInfo.getCoverImg()==null?"":roomInfo.getCoverImg()) + "',LiveStatus=" + roomInfo.getLiveStatus()  + " \n"
						+ "     ,StartTime = '" + format.format(startTime) + "',EndTime = '" + format.format(endTime) + "',AnchorName='" + (roomInfo.getAnchorName()==null?"":roomInfo.getAnchorName()) +"' \n" 
						+ "     ,AnchorImg='" + (roomInfo.getAnchorImg()==null?"":roomInfo.getAnchorImg()) + "',ShareImg='" + (roomInfo.getShareImg()==null?"":roomInfo.getShareImg()) + "' \n"
						+ "     ,OpenId = case when isnull(a.OpenId,'') <> '' then a.OpenId else '" + roomInfo.getCreaterOpenid() + "' end \n"
						+ "     ,FeedsImg='" + (roomInfo.getFeedsImg()==null?"":roomInfo.getFeedsImg()) + "', isCloseLike = " +roomInfo.getCloseLike() + ",isCloseGoods=" + roomInfo.getCloseGoods()
						+ "     ,isCloseComment=" +  roomInfo.getCloseComment() + ",isCloseReplay=" +roomInfo.getCloseReplay() 
						//+ "     ,isCloseShare="+roomInfo.getCloseShare() 
						+ "     ,isCloseKf=" + roomInfo.getCloseKf() 
						+ "     ,isFeedsPublic=" + roomInfo.getIsFeedsPublic() + "\n"
					    + "    from t710701 a where a.RoomId = " + roomInfo.getRoomId() + " \n"
					    + "    set @myRowCount = isnull(@myRowCount,0) + @@rowcount \n" 
					    + " end \n" ;
				sql += " else \n"
						+ " begin \n"
						+ "    insert into @table(RoomId,FieldId,RoomImg) \n"
						+ "    values (" + roomInfo.getRoomId() + ",'CoverImgUnid','" + (roomInfo.getCoverImg()==null?"":roomInfo.getCoverImg()) + "')  \n"
						+ "    insert into @table(RoomId,FieldId,RoomImg) \n"
						+ "    values (" + roomInfo.getRoomId() + ",'ShareImgUnid','" + (roomInfo.getShareImg()==null?"":roomInfo.getShareImg()) + "')  \n"
						+ "    insert into @table(RoomId,FieldId,RoomImg) \n"
						+ "    values (" + roomInfo.getRoomId() + ",'AnchorImgUnid','" + (roomInfo.getAnchorImg()==null?"":roomInfo.getAnchorImg()) + "')  \n"
						+ "    insert into @table(RoomId,FieldId,RoomImg) \n"
						+ "    values (" + roomInfo.getRoomId() + ",'FeedsImgUnid','" + (roomInfo.getFeedsImg()==null?"":roomInfo.getFeedsImg()) + "')  \n"
						+ "    select top 1 @CompanyId = CompanyId,@CompanyName=CompanyName \n"
						+ "    from ocompany \n"
						+ "    select @DocStatus = PreDocStatus from gform where formid = 710701 \n"
						+ "    select @PeriodId = dbo.GetPeriodID(@FormId,@CompanyID,@Today) \n"
						+ "    exec sp_newdoccode @FormId,@UserCode,@DocCode output \n"
						+ "    insert into t710701(DocCode,FormId,DocDate,CompanyID,CompanyName,DocStatus,EnterCode,EnterName,\n" 
						+ "       EnterDate,ModifyName,ModifyDate,CcCode,CcName,PeriodId,\n"
						+ "       Type,RoomId,RoomName,CoverImg,LiveStatus,StartTime,EndTime,AnchorName,AnchorImg ,ShareImg,FeedsImg,OpenId, \n"
						+ "       isCloseLike,isCloseGoods,isCloseComment,isCloseReplay,isCloseShare,isCloseKf,isFeedsPublic) \n"
						+ "    values(@DocCode,@FormId,@Today,@CompanyID,@CompanyName,@DocStatus,@UserCode,@UserName,\n" 
						+ "	      getdate(),@UserName,getdate(),@CcCode,@CcName,@PeriodId ,\n"
						+         roomInfo.getType()+ "," + roomInfo.getRoomId() + ",'" + (roomInfo.getName()==null?"":roomInfo.getName()) + "','" + (roomInfo.getCoverImg()==null?"":roomInfo.getCoverImg()) + "'," + roomInfo.getLiveStatus()
						+ "       ,'" + format.format(startTime)   + "','" + format.format(endTime) + "','" + (roomInfo.getAnchorName()==null?"":roomInfo.getAnchorName()) +"',"
						+ "       '" + (roomInfo.getAnchorImg()==null?"":roomInfo.getAnchorImg())  + "','" + (roomInfo.getShareImg()==null?"": roomInfo.getShareImg()) + "','" + (roomInfo.getFeedsImg()==null?"":roomInfo.getFeedsImg()) +"','" + (roomInfo.getCreaterOpenid()==null?"":roomInfo.getCreaterOpenid()) + "', \n"
						+        roomInfo.getCloseLike() + "," + roomInfo.getCloseGoods() + ","  
						+        roomInfo.getCloseComment()  + "," + roomInfo.getCloseReplay() + ","
						+        1 + "," + roomInfo.getCloseKf() + ","
						+        roomInfo.getIsFeedsPublic()+ ") \n"
						+ "    set @myRowCount = isnull(@myRowCount,0) + @@rowcount \n" 
						+ " end \n"
						+ " exec p710701Save @DocCode = @DocCode \n";
				
				//回放视频
				for (int j = 0; wxMaLiveResult.getLiveReplay()!= null && j < wxMaLiveResult.getLiveReplay().size();j++) {
					LiveReplay liveReplay = wxMaLiveResult.getLiveReplay().get(j);
					String createTime = liveReplay.getCreateTime().replaceAll("T"," ").replaceAll("Z","") ;
					String expireTime = liveReplay.getExpireTime().replaceAll("T"," ").replaceAll("Z","") ;
					sql += " if not exists(select 1 from t710702 where RoomId = " +roomInfo.getRoomId() + " and CreateTime = '" + createTime + "' ) \n"
							+ " begin \n"
							+ "    insert into t710702 (RoomId,CreateTime,ExpireTime,MediaUrl) \n"
							+ "    values(" + roomInfo.getRoomId() + ",'" +createTime + "','" + expireTime + "','" + (liveReplay.getMediaUrl()==null?"":liveReplay.getMediaUrl())+"' )\n"
							+ "    set @myRowCount = isnull(@myRowCount,0) + @@rowcount \n"
							+ " end \n";
				}
				
				//维护直播间所属商品
				//sql += " delete from t710704 where RoomId = " + roomInfo.getRoomId() ;
				for (int j = 0 ;roomInfo.getGoods()!=null && j < roomInfo.getGoods().size();j++) {
					
					sql += "  select @GoodsId = " + roomInfo.getGoods().get(j).getGoodsId() + "  ;  \n"
							+ "    update a set MatName = '" + roomInfo.getGoods().get(j).getName() + "',\n"
							+ "       Price = "+ roomInfo.getGoods().get(j).getPrice() + ",Price2= " + roomInfo.getGoods().get(j).getPrice2() + ",\n"
							+ "       Url='" + roomInfo.getGoods().get(j).getUrl() + "',CoverImg='" + roomInfo.getGoods().get(j).getCoverImg() + "' \n"
							+ "    from t710704 a \n"
							+ "    where a.RoomId = "+ roomInfo.getRoomId() + " and a.GoodsId = @GoodsId \n"
							+ "    if @@rowcount = 0 \n"
							+ "    begin \n"
							+ "       insert into t710704(RoomId,MatName,Price,Price2,Url,CoverImg,GoodsId) \n"
							+ "       values (" + roomInfo.getRoomId() + ",'" + roomInfo.getGoods().get(j).getName() + "',"
							+         roomInfo.getGoods().get(j).getPrice() + "," 
							+         roomInfo.getGoods().get(j).getPrice2()+",'" + roomInfo.getGoods().get(j).getUrl() + "','" + roomInfo.getGoods().get(j).getCoverImg()+"',@GoodsId ) \n" 
							+ "    end \n"
							+ "    set @myRowCount = isnull(@myRowCount,0) + @@rowcount \n";
					
					/*
					 * 下列代码导致 t710704 有百万条记录，数据库占用急剧膨胀 ， 记录 by Johns Wang,2021-07-14
					定金单号 100元的做法？？ ，注释 by Johns Wang,2021-07-14
					
					//url 样式： pages/detailEvents/detailEvents?matcode=K-36944T-L-AF&DepositDocCode=QGDJ200413001&DepositRowId=003RXI
					String url = roomInfo.getGoods().get(j).getUrl();
					int pos = url.indexOf("?");   //取 ？ 分隔符
					String matCode = "",depositDocCode = "",depositRowId = "";
					Map<String, String> queryStringMap = new HashMap<String, String>() ;
					if (pos > 0) {
						String queryString = url.substring(pos+1, url.length()) ;
						if (queryString != null && !"".equals(queryString)) {
							String tempStr[] = queryString.split("&") ;   // 取& 分隔符 
							for(int k = 0;k < tempStr.length;k++ ) {
								if (tempStr[k]!=null&& !"".equals(tempStr[k])) {
									String subTempStr[] = tempStr[k].split("=") ;   //取 = 分隔符
									if (subTempStr.length >1) {
										queryStringMap.put(subTempStr[0], subTempStr[1]);
									}
								}
							}
						}
					}
					matCode = queryStringMap.get(SettingKey.MATCODE) ;   //商品编号
					depositDocCode = queryStringMap.get("DepositDocCode") ;  //定金单号
					depositRowId = queryStringMap.get("DepositRowId") ;   //定金单行号
					sql += " select @GoodsId = null,@MatCode = '" + (matCode==null?"":matCode) + "', @DepositDocCode = '" + (depositDocCode==null?"":depositDocCode)+"',@DepositRowId ='" + (depositRowId == null?"":depositRowId) + "' \n"
							+ " select @GoodsId = GoodsId from t710703 where isnull(MatCode,'') = isnull(@MatCode,'') \n"
							+ " and isnull(DepositDocCode,'') = isnull(@DepositDocCode,'') \n"
							+ " and isnull(DepositRowId,'') = isnull(@DepositRowId,'') \n"
							+ " if @@rowcount > 0 and @GoodsId is not null \n"
							+ " begin \n"
							+ "    update a set MatName = '" + roomInfo.getGoods().get(j).getName() + "',\n"
							+ "       Price = "+ roomInfo.getGoods().get(j).getPrice() + ",Price2= " + roomInfo.getGoods().get(j).getPrice2() + ",\n"
							+ "       Url='" + roomInfo.getGoods().get(j).getUrl() + "',CoverImg='" + roomInfo.getGoods().get(j).getCoverImg() + "' \n"
							+ "    from t710704 a \n"
							+ "    where a.RoomId = "+ roomInfo.getRoomId() + " and a.GoodsId = @GoodsId \n"
							+ "    if @@rowcount = 0 \n"
							+ "    begin \n"
							+ "       insert into t710704(RoomId,MatName,Price,Price2,Url,CoverImg,GoodsId) \n"
							+ "       values (" + roomInfo.getRoomId() + ",'" + roomInfo.getGoods().get(j).getName() + "',"
							+         roomInfo.getGoods().get(j).getPrice() + "," 
							+         roomInfo.getGoods().get(j).getPrice2()+",'" + roomInfo.getGoods().get(j).getUrl() + "','" + roomInfo.getGoods().get(j).getCoverImg()+"',@GoodsId ) \n" 
							+ "    end \n"
							+ "    set @myRowCount = isnull(@myRowCount,0) + @@rowcount \n"
							+ " end else \n"
							+ " begin \n"
							+ "    insert into t710704(RoomId,MatName,Price,Price2,Url,CoverImg,GoodsId) \n"
							+ "    values (" + roomInfo.getRoomId() + ",'" + roomInfo.getGoods().get(j).getName() + "'," 
							+      roomInfo.getGoods().get(j).getPrice() + "," 
							+      roomInfo.getGoods().get(j).getPrice2()+",'" + roomInfo.getGoods().get(j).getUrl() + "','" + roomInfo.getGoods().get(j).getCoverImg()+"',null ) \n" 
							+ "    set @myRowCount = isnull(@myRowCount,0) + @@rowcount \n"
							+ " end \n";
							*/
				}  
			}
			
			sql += " select RoomId ,FieldId , RoomImg, @myRowCount as MyRowCount from @table ;\n" ;
			list = this.jdbcTemplate.queryForList(sql) ;
			List<LiveRoomImageEntity> roomInfoList = new ArrayList<LiveRoomImageEntity>() ;
			for (int i = 0 ;list != null && i < list.size() ; i ++) {
				Map<String,Object> map = list.get(i) ;
				LiveRoomImageEntity roomInfo = new LiveRoomImageEntity() ; 
				roomInfo.setRoomId(map.get("RoomId")==null?null:(Integer)map.get("RoomId")) ;
				roomInfo.setFieldId(map.get("FieldId")==null?null:(String)map.get("FieldId")) ;
				roomInfo.setRoomImg(map.get("RoomImg")==null?null:(String)map.get("RoomImg")) ;
				roomInfo.setRowCount(map.get("MyRowCount")==null?null:(Integer)map.get("MyRowCount")) ;
				roomInfoList.add(roomInfo) ;
			}
			return roomInfoList ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
	}
	
	@Override
	public Integer addGoodsToRoom(Integer roomId,String goodsIds) {
		Integer ret = null;
		try {
			String sql = " set nocount on ; \n"
					+ " declare @RoomId int = "+roomId+",@GoodsIds varchar(max) = "+GridUtils.prossSqlParm(goodsIds)+",@myRowCount int \n"
					+ " update a set MatName = b.MatName,Price = b.Price,Price2=b.Price2,Url = b.Url,CoverImg = b.CoverImgUrl \n"
					+ " from t710704 a join t710703 b on a.GoodsId = b.GoodsId \n"
					+ " where a.RoomId = @RoomId and a.GoodsId in (select list from getinstr(@GoodsIds)) \n"
					+ " select @myRowCount = isnull(@myRowCount,0) + @@rowcount \n"
					+ " insert into t710704(RoomId,MatName,Price,Price2,Url,CoverImg,GoodsId) \n"
					+ " select @RoomId as RoomId,a.MatName,a.Price,a.Price2,a.Url,a.CoverImgUrl,a.GoodsId \n"
					+ " from t710703 a \n"
					+ " where a.GoodsId in (select list from getinstr(@GoodsIds)) \n"
					+ " and not exists(select 1 from t710704 b where b.RoomId = @RoomId and a.GoodsId = b.GoodsId ) \n"
					+ " select @myRowCount = isnull(@myRowCount,0) + @@rowcount \n"
					+ " select @myRowCount \n";
					ret = this.jdbcTemplate.queryForObject(sql, Integer.class) ;
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return 0 ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
		return ret ;
	}
	
	
	@Override
	public Integer deleteGoodsInRoom(Integer roomId,Integer goodsId) {
		Integer ret = null;
		try {
			String sql = " set nocount on ; \n"
					+ " declare @RoomId int = "+roomId+",@GoodsId int = "+goodsId+" \n"
					+ " delete from t710704 where RoomId = @RoomId and GoodsId = @GoodsId \n"
					
					+ " select @@rowcount \n";
					ret = this.jdbcTemplate.queryForObject(sql, Integer.class) ;
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return 0 ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
		return ret ;
	}
	
	@Override
	public Integer deleteGoods(Long goodsId) {
		Integer ret = null;
		try {
			String sql = " set nocount on ; \n"
					+ " declare @GoodsId bigint = "+goodsId+" ,@DocCode varchar(20) \n"
					+ " select @DocCode = MatCode from t710703 where GoodsId = @GoodsId \n"
					+ " delete from t710704 where GoodsId = @GoodsId \n"
					+ " delete from t710703 where GoodsId = @GoodsId \n"
					+ " update a set GoodsId = null from t710802H a where a.DocCode = @DocCode \n"
					
					+ " select @@rowcount \n";
					ret = this.jdbcTemplate.queryForObject(sql, Integer.class) ;
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return 0 ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
		return ret ;
	}
	
	@Override
	public Integer deleteRoom(Integer roomId) {
		Integer ret = null;
		try {
			String sql = " set nocount on ; \n"
					+ " declare @RoomId int = "+roomId+" \n"
					+ " delete from t710704 where RoomId = @RoomId  \n"
					+ " delete from t710701 where RoomId = @RoomId  \n"
					
					+ " select @@rowcount \n";
					ret = this.jdbcTemplate.queryForObject(sql, Integer.class) ;
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return 0 ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
		return ret ;
	}
	
	
	
	@Override
	public List<LiveSubscribeUserEntity> getSubscribeUsers(Integer roomId) {
		try {
			String sql = " set nocount on ; \n"
					+ " declare @RoomId int = "+roomId+" \n"
					+ " select a.RoomId,a.OpenId,b.NickName,b.WeiXinAvatarUnid,b.Headimgurl, \n"
					+ "    a.SubscribeTime,SubscribeDateTime,a.RoomStatus \n"
					+ " from t710708 a \n"
					+ " left join t730102 b on a.OpenId = b.OpenId \n"
					+ " where RoomId = @RoomId  \n" ;
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ;
			List<LiveSubscribeUserEntity> liveSubscribeUserList = new ArrayList<LiveSubscribeUserEntity>();
			for (int i = 0; list !=null&& i < list.size(); i++) {
				Map<String,Object> map = list.get(i);
				LiveSubscribeUserEntity liveSubscribeUserEntity = LiveSubscribeUserEntity.builder().openId((String)map.get("OpenId"))
						.roomId(roomId).roomStatus((Integer)map.get("RoomStatus"))
						.subscribeTime(map.get("SubscribeTime")== null?0L:Long.parseLong(map.get("SubscribeTime").toString()))
						.subscribeDateTime(map.get("SubscribeDateTime")==null?null:(Date)map.get("SubscribeDateTime"))
						.build();
				liveSubscribeUserList.add(liveSubscribeUserEntity);
			}
			return liveSubscribeUserList;
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
	}
	
	

	
	@Override
	public Integer savePushMessageId(Integer roomId,String messageId) {
		try {
			String sql = " set nocount on ; \n"
					+ " declare @RoomId int = "+roomId+" , @MessageId varchar(50) = " +GridUtils.prossSqlParm(messageId)+" \n"
					+ " update a set SendDateTime = getdate() from t710709 a where a.RoomId = @RoomId and a.MessageId = @MessageId \n"
					+ " if @@rowcount = 0 \n"
					+ " begin \n"
					+ "    insert into t710709 (RoomId,MessageId,SendDateTime) \n"
					+ "    values(@RoomId,@MessageId,getdate()) \n"
					+ " end \n"
					+ " select @@rowcount as myrowcount \n" ;
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
	}
	
	
	@Override
	public Integer savePushMessageResult(LivePushMessageApiNotifyEntity livePushMessageApiNotifyEntity) {
		try {
			String sql = " set nocount on ; \n"
					+ " declare @RoomId int = "+livePushMessageApiNotifyEntity.getRoomId()+" , @MessageId varchar(50) = "+GridUtils.prossSqlParm(livePushMessageApiNotifyEntity.getMessageId())
					+",@TotalCount int = "+livePushMessageApiNotifyEntity.getTotalCount()+",@SuccessCount int = "+livePushMessageApiNotifyEntity.getSuccessCount()+" \n"
					+ " declare @OpenIdErrorCount int = "+livePushMessageApiNotifyEntity.getOpenidErrorCount()
					+",@RelationErrorCount int = "+livePushMessageApiNotifyEntity.getRelationErrorCount()
					+" ,@UserRecvLimitCount int = "+livePushMessageApiNotifyEntity.getUserRecvLimitCount()+" \n"
					+ " declare @InternalErrorCount int = "+livePushMessageApiNotifyEntity.getInternalErrorCount()+" \n"
					+ " update a set TotalCount = @TotalCount,SuccessCount = @SuccessCount,OpenIdErrorCount = @OpenIdErrorCount, \n"
					+ "    RelationErrorCount = @RelationErrorCount,UserRecvLimitCount = @UserRecvLimitCount,InternalErrorCount = @InternalErrorCount \n"
					+ " from t710709 a where a.RoomId = @RoomId and a.MessageId = @MessageId \n"
					+ " if @@rowcount = 0 \n"
					+ " begin \n"
					+ "    insert into t710709 (RoomId,MessageId,TotalCount ,SuccessCount ,OpenIdErrorCount ,RelationErrorCount ,UserRecvLimitCount,InternalErrorCount,SendDateTime) \n"
					+ "    values(@RoomId,@MessageId,@TotalCount ,@SuccessCount ,@OpenIdErrorCount ,@RelationErrorCount ,@UserRecvLimitCount,@InternalErrorCount,getdate()) \n"
					+ " end \n"
					+ " select @@rowcount as myrowcount \n" ;
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
	}
	
	@Override
	public Integer deleteRoom(String docCode) {
		Integer ret = null;
		try {
			String sql = " set nocount on ; \n"
					+ " declare @DocCode varchar(20)  = "+GridUtils.prossSqlParm(docCode)+" \n"
					+ " delete a from t710704 a where exists(select 1 from t710701 b where b.DocCode = @DocCode and a.RoomId = b.RoomId )  \n"
					+ " delete a from t710701 a where a.DocCode = @DocCode \n"
					
					+ " select @@rowcount \n";
					ret = this.jdbcTemplate.queryForObject(sql, Integer.class) ;
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return 0 ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
		return ret ;
	}
	
	@Override
	public Integer addAssistant(Integer roomId,List<WxMaLiveAssistantInfo> users) {
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @myrowcount int \n";
		try {
			for (int i = 0;users!=null&& i <users.size(); i++) {
				WxMaLiveAssistantInfo wxMaLiveAssistantInfo = users.get(i);
				//AssistantItemEntity assistantItemEntity = liveAssistantEntity.getList().get(i) ;
				Date timeStamp = null ;
				if (wxMaLiveAssistantInfo.getTimestamp() != null) {
					timeStamp = new Date(wxMaLiveAssistantInfo.getTimestamp()*1000);
				} else {
					timeStamp = new Date(); 
				}
				sql += " if not exists(select 1 from t710707 a where a.RoomId = @RoomId and UserName = '" + wxMaLiveAssistantInfo.getUsername()+"' )\n"
					+ " begin \n"
					+ "    insert into t710707(RoomId,UserName,NickName,CreateTime,TimeStamp) \n"
					+ "    values(@RoomId,'" + wxMaLiveAssistantInfo.getUsername()+"','"+wxMaLiveAssistantInfo.getNickname()+"','" + sdf2.format(timeStamp)+"'," +wxMaLiveAssistantInfo.getTimestamp()+") \n"
					+ "    set @myrowcount = isnull(@myrowcount,0) + 1 \n"
					+ " end \n";
			}
			sql += " select @myrowcount \n" ;
			
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
			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 updateAssistantOpenId(Integer roomId ,List<WxMaAssistantResult.Assistant> assistantList) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @myrowcount int \n";
		try {
			for (int i = 0;assistantList!=null&& i <assistantList.size(); i++) {
				WxMaAssistantResult.Assistant assistant = assistantList.get(i);
				
				
				sql += " update a set OpenId ="+GridUtils.prossSqlParm(assistant.getOpenid()) + " from  t710707 a where a.RoomId = @RoomId and NickName = " + GridUtils.prossSqlParm(assistant.getNickname()) +" \n"
					+ " set @myrowcount = isnull(@myrowcount,0) + 1 \n";
			}
			sql += " select @myrowcount \n" ;
			
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
			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 updateAssistant(Integer roomId,String userName,String nickName) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @UserName nvarchar(200) = "+GridUtils.prossSqlParm(userName)+",@NickName nvarchar(400) = "+GridUtils.prossSqlParm(nickName) +" \n"
				+ " update a set NickName = @NickName from t710707 a where a.RoomId = RoomId and a.UserName = @UserName \n"
				+ " select @@rowcount \n";
		try {
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			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 deleteAssistant(Integer roomId,String userName) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @UserName varchar(50) = "+GridUtils.prossSqlParm(userName)+ " \n"
				+ " delete a  from t710707 a where a.RoomId = @RoomId and a.UserName = @UserName \n"
				+ " select @@rowcount \n";
		try {
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			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 LiveAssistantEntity getAssistantList(Integer roomId) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" \n"
				+ " select a.RoomId,a.UserName,a.NickName,a.CreateTime,a.OpenId,a.TimeStamp \n"
				+ " from t710707 a where RoomId = @RoomId order by a.CreateTime asc \n";
		try {
			LiveAssistantEntity liveAssistantEntity = new LiveAssistantEntity() ;	
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ;
			List<AssistantItemEntity> assistantItemList = new ArrayList<AssistantItemEntity>() ;
			for (int i = 0;list!=null&& i < list.size(); i++) {
				Map<String,Object> map = list.get(i) ;
				AssistantItemEntity assistantItemEntity = liveAssistantEntity.new AssistantItemEntity() ;
				assistantItemEntity.setUserName(map.get("UserName")== null?"":(String)map.get("UserName"));
				assistantItemEntity.setNickName(map.get("NickName")== null?"":(String)map.get("NickName"));
				assistantItemEntity.setCreateTime(map.get("CreateTime")== null?null:(Date)map.get("CreateTime"));
				assistantItemEntity.setOpenId(map.get("OpenId")== null?"":(String)map.get("OpenId"));
				assistantItemEntity.setTimeStamp(map.get("TimeStamp")== null?0L:Long.parseLong(map.get("TimeStamp").toString()));
				
				assistantItemList.add(assistantItemEntity);
			}
			liveAssistantEntity.setList(assistantItemList);
			liveAssistantEntity.setRoomId(roomId);
			return liveAssistantEntity ;
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
	}
	
	
	@Override
	public Integer updateSubAnchor(Integer roomId,String userName) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @UserName varchar(50) = "+GridUtils.prossSqlParm(userName)+" \n"
				+ " update a set SubAnchorWechat = @UserName from t710701 a where a.RoomId = @RoomId \n"
				+ " select @@rowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer updateFeedsPublic(Integer roomId,Integer isFeedsPublic ) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @isFeedsPublic int = "+isFeedsPublic+" \n"
				+ " update a set isFeedsPublic = @isFeedsPublic from t710701 a where a.RoomId = @RoomId \n"
				+ " select @@rowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer updateCloseReplay(Integer roomId,Integer isCloseReplay ) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @isCloseReplay int = "+isCloseReplay+" \n"
				+ " update a set isCloseReplay = @isCloseReplay from t710701 a where a.RoomId = @RoomId \n"
				+ " select @@rowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer updateCloseKf(Integer roomId,Integer isCloseKf ) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @isCloseKf int = "+isCloseKf+" \n"
				+ " update a set isCloseKf = @isCloseKf from t710701 a where a.RoomId = @RoomId \n"
				+ " select @@rowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}

	@Override
	public Integer updateCloseComment(Integer roomId,Integer isCloseComment ) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @isCloseComment int = "+isCloseComment+" \n"
				+ " update a set isCloseComment = @isCloseComment from t710701 a where a.RoomId = @RoomId \n"
				+ " select @@rowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer updateGoodsToOnSaleInRoom(Integer roomId,Long goodsId,Integer onSale ) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" ,@GoodsId bigint = "+goodsId+", @onSale int = "+onSale+" \n"
				+ " update a set onSale = @onSale from t710704 a where a.RoomId = @RoomId and a.GoodsId = @GoodsId \n"
				+ " select @@rowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	
	@Override
	public Integer updateRoomImg(LiveRoomImageEntity liveRoomImageEntity) {
		Integer ret = null;
		try {
			String sql = " set nocount on ; \n"
					+ " declare @RoomImgUnid varchar(50) = "+GridUtils.prossSqlParm(liveRoomImageEntity.getRoomImgUnid())+" ,@RoomId int = "+liveRoomImageEntity.getRoomId()+" ,@myRowCount int = 0 \n"
					+ " update a set " + liveRoomImageEntity.getFieldId() + " = @RoomImgUnid  from  t710701 a where a.RoomId = @RoomId  \n"
					+ " set @myRowCount = @@rowcount \n" 
			 		+ " select  @myRowCount ;" ;
			ret = this.jdbcTemplate.queryForObject(sql, Integer.class) ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return 0 ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
		return ret ;
	}
	
	@Override
	public Integer updateRoomQrCodeUrl(Integer roomId,String roomQrCodeUrl,String roomQrCodeUrlUnid) {
		Integer ret = null;
		try {
			String sql = " set nocount on ; \n"
					+ " declare @RoomId int = "+roomId+",@RoomQrCodeUrl varchar(500) = "+GridUtils.prossSqlParm(roomQrCodeUrl)+" ,@RoomQrCodeUrlUnid varchar(50) = "+GridUtils.prossSqlParm(roomQrCodeUrlUnid)+" , @myRowCount int = 0 \n"
					+ " update a set RoomQrCodeUrl = @RoomQrCodeUrl,RoomQrCodeUrlUnid = @RoomQrCodeUrlUnid from  t710701 a where a.RoomId = @RoomId  \n"
					+ " set @myRowCount = @@rowcount \n" 
			 		+ " select  @myRowCount ;" ;
			ret = this.jdbcTemplate.queryForObject(sql, Integer.class) ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return 0 ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
		return ret ;
	}

	@Override
	public Integer updateReplayVideo(Integer roomId,List<LiveReplay> liveReplayList) {
		Integer ret = null;
		try {
			String sql = " set nocount on ; \n"
					+ " declare @myRowCount int = 0 \n" ;
			for (int i =0; liveReplayList != null &&  i <liveReplayList.size();i++ ) {
				LiveReplay liveReplay = liveReplayList.get(i) ;
				
				
				sql += " if not exists(select 1 from t710702 where RoomId = " +roomId + " and CreateTime = '" + liveReplay.getCreateTime() + "' ) \n"
						+ " begin \n"
						+ "    insert into t710702 (RoomId,CreateTime,ExpireTime,MediaUrl) \n"
						+ "    values(" + roomId + ",'" + liveReplay.getCreateTime() + "','" + liveReplay.getExpireTime() + "','" +liveReplay.getMediaUrl()+"' )\n"
						+ "    set @myRowCount = isnull(@myRowCount,0) + 1 \n"
						+ " end \n";
			}
			sql += " select  @myRowCount ;" ;
			ret = this.jdbcTemplate.queryForObject(sql, Integer.class) ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return 0 ;
			} else {
				//e.printStackTrace();
				throw e ;
			}
		}catch(Exception e){
			//e.printStackTrace();
			throw e ;
		}
		return ret ;
	}

	@Override
	public Integer saveRoomStep2(RoomInfo roomInfo) {
		String sql = " set nocount on ; \n"
				+ " declare @DocCode varchar(20) = "+GridUtils.prossSqlParm(roomInfo.getDocCode())+",@isCloseLike int ="+roomInfo.getCloseLike()+",@isCloseGoods int ="+roomInfo.getCloseGoods()+" \n"
				+ " declare @myrowcount int \n"
				+ " declare @isCloseComment int ="+roomInfo.getCloseComment()+",@isCloseReplay int = "+roomInfo.getCloseReplay()+",@isCloseShare int = "+roomInfo.getCloseShare()+",@isCloseKf int = "+roomInfo.getCloseKf()+",@isFeedsPublic int = "+roomInfo.getIsFeedsPublic()+" \n"
				+ " update a set isCloseLike = @isCloseLike,isCloseGoods=@isCloseGoods,isCloseComment=@isCloseComment, \n"
				+ "     isCloseReplay=@isCloseReplay,isCloseShare=@isCloseShare,isCloseKf=@isCloseKf,isFeedsPublic = @isFeedsPublic \n"
				+ " from t710701 a where a.DocCode = @DocCode \n"
				+ " select @myrowcount = @@rowcount \n"
				+ " exec p710701Save @DocCode = @DocCode \n"
				+ " select @myrowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public String saveRoomStep1(RoomInfo roomInfo) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		 
		String sql = " set nocount on ; \n"
				+ " declare @DocCode varchar(20) = "+GridUtils.prossSqlParm(roomInfo.getDocCode())+",@Type int = "+roomInfo.getType()
				+",@RoomName varchar(80) = "+GridUtils.prossSqlParm(roomInfo.getName())
				+",@StartTime datetime = "+GridUtils.prossSqlParm(roomInfo.getStartDateTime()==null?null:sdf.format(roomInfo.getStartDateTime()))
				+",@EndTime datetime = "+GridUtils.prossSqlParm(roomInfo.getEndDateTime()==null?null:sdf.format(roomInfo.getEndDateTime()))+" \n"
				+ " declare @AnchorName varchar(50) = "+GridUtils.prossSqlParm(roomInfo.getAnchorName())
				+",@AnchorWechat varchar(200) ="+GridUtils.prossSqlParm(roomInfo.getAnchorWechat())
				+",@SubAnchorWechat varchar(200) ="+GridUtils.prossSqlParm(roomInfo.getSubAnchorWechat())
				+",@isFeedsPublic int = "+roomInfo.getIsFeedsPublic()+" \n"
				+ " declare @ScreenType int = "+roomInfo.getScreenType()+",@UserCode varchar(50) = "+GridUtils.prossSqlParm(roomInfo.getUserCode())
				+", @UserName varchar(50) = "+GridUtils.prossSqlParm(roomInfo.getUserName())+",@ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(roomInfo.getShopCcCode())+" \n"
				+ " declare @isShowBannerPhotoOnHomePage int = "+(roomInfo.isShowBannerPhotoOnHomePage()?1:0)+" ,@OpenId varchar(200) = "+GridUtils.prossSqlParm(roomInfo.getCreaterWechat()) +" \n"
				+ " declare @Today datetime = convert(varchar(10),getdate(),120) \n"
				+ " declare @FormId int = 710701, @DocStatus int = 0 ,@isFound int = 0 \n"
				
				+ " declare @DefaultBrand varchar(50)  \n"
				+ " select top 1 @DefaultBrand = DefaultBrand from _sys_LoginUser where UserCode = @UserCode \n"
				
				+ " declare @PeriodId varchar(20),@CompanyId varchar(20),@CompanyName varchar(80),@CcCode varchar(20),@CcName varchar(50) \n"
				+ " if isnull(@DocCode,'') <> '' \n"
				+ " begin \n"
				+ "    update a set Type=@Type,RoomName = @RoomName,StartTime = @StartTime,EndTime=@EndTime,AnchorName=@AnchorName,\n"
				+ "       AnchorWechat=@AnchorWechat,SubAnchorWechat=@SubAnchorWechat,isFeedsPublic=@isFeedsPublic,ScreenType=@ScreenType,ShopCcCode=@ShopCcCode, \n"
				+ "       ModifyName = @UserName,ModifyDate = getdate(),Brand = @DefaultBrand,isShowBannerPhotoOnHomePage = @isShowBannerPhotoOnHomePage \n"
				+ "    from t710701 a where a.DocCode = @DocCode \n"
				+ "    set @isFound = @@rowcount \n"
				+ " end \n"
				+ " if isnull(@isFound,0) = 0 \n"
				+ " begin \n"
				+ "    select @CcCode = CcCode,@CompanyId = CompanyId,@CompanyName=CompanyName,@UserName = UserName \n"
				+ "    from _sys_loginuser a where usercode = @UserCode \n"
				+ "    select @DocStatus = PreDocStatus from gform where formid = 710701 \n"
				+ "    select @PeriodId = dbo.GetPeriodID(@FormId,@CompanyID,@Today) \n"
				+ "    exec sp_newdoccode @FormId,@UserCode,@DocCode output \n"
				+ "    insert into t710701(DocCode,FormId,DocDate,CompanyID,CompanyName,DocStatus,EnterCode,EnterName,\n"
				+ "        EnterDate,ModifyName,ModifyDate,CcCode,CcName,PeriodId, \n"
				+ "        Type,RoomId,RoomName,StartTime,EndTime,AnchorName,AnchorWechat,SubAnchorWechat,isFeedsPublic, \n"
				+ "        ScreenType,ShopCcCode,Brand,isShowBannerPhotoOnHomePage,OpenId) \n"
				+ "    values(@DocCode,@FormId,@Today,@CompanyID,@CompanyName,@DocStatus,@UserCode,@UserName,\n"
				+ "        getdate(),@UserName,getdate(),@CcCode,@CcName,@PeriodId ,\n"
				+ "        @Type,null,@RoomName,@StartTime,@EndTime,@AnchorName,@AnchorWechat,@SubAnchorWechat,@isFeedsPublic, \n"
				+ "        @ScreenType,@ShopCcCode,@DefaultBrand,@isShowBannerPhotoOnHomePage,@OpenId) \n"
				+ " end \n"
				+ " exec p710701Save @DocCode = @DocCode \n"
				+ " select @DocCode as DocCode \n" ;
		try {
			Map<String, Object> map = this.jdbcTemplate.queryForMap(sql) ;
			String docCode = null ;
			if (map != null) {
				docCode= (String)map.get("DocCode") ;
			}
			return docCode;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
				 
	}
	
	
	@Override
	public String createRoomStep1(CreateLiveRoomParameterStep1Entity createLiveRoomParameterStep1Entity) {
		String sql = " set nocount on ; \n"
				+ " declare @DocCode varchar(20) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getDocCode())+",@Type int = "+createLiveRoomParameterStep1Entity.getType()+",@RoomName varchar(80) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getName())+" \n"
				+ " declare @AnchorName varchar(50) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getAnchorName())
				+",@AnchorWechat varchar(200) ="+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getAnchorWechat())
				+",@SubAnchorWechat varchar(200) ="+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getSubAnchorWechat())+" \n"
				+ " declare @ScreenType int = "+createLiveRoomParameterStep1Entity.getScreenType()
				+",@UserCode varchar(50) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getUserCode())
				+", @UserName varchar(50) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getUserName())
				+",@ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getShopCcCode()) +" \n"
				+ " declare @isShowBannerPhotoOnHomePage int = 0 ,@OpenId varchar(200) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getOpenId())+" \n"
				+ " declare @ShareImgUnid varchar(50) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getShareImgUnid())
				+",@FeedsImgUnid varchar(50) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getFeedsImgUnid())
				+",@CoverImgUnid varchar(50) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep1Entity.getCoverImgUnid()) +" \n"
				+ " declare @Today datetime = convert(varchar(10),getdate(),120) \n"
				+ " declare @FormId int = 710701, @DocStatus int = 0 ,@isFound int = 0 \n"
				
				+ " declare @DefaultBrand varchar(50)  \n"
				+ " select top 1 @DefaultBrand = DefaultBrand from _sys_LoginUser where UserCode = @UserCode \n"
				
				+ " declare @PeriodId varchar(20),@CompanyId varchar(20),@CompanyName varchar(80),@CcCode varchar(20),@CcName varchar(50) \n"
				+ " if isnull(@DocCode,'') <> '' \n"
				+ " begin \n"
				+ "    update a set Type=@Type,RoomName = @RoomName,AnchorName=@AnchorName,\n"
				+ "       AnchorWechat=@AnchorWechat,SubAnchorWechat=@SubAnchorWechat,ScreenType=@ScreenType,ShopCcCode=@ShopCcCode, \n"
				+ "       ModifyName = @UserName,ModifyDate = getdate(),Brand = @DefaultBrand,isShowBannerPhotoOnHomePage = @isShowBannerPhotoOnHomePage, \n"
				+ "       ShareImgUnid = @ShareImgUnid,FeedsImgUnid = @FeedsImgUnid,CoverImgUnid = @CoverImgUnid \n"
				+ "    from t710701 a where a.DocCode = @DocCode \n"
				+ "    set @isFound = @@rowcount \n"
				+ " end \n"
				+ " if isnull(@isFound,0) = 0 \n"
				+ " begin \n"
				+ "    select @CcCode = CcCode,@CompanyId = CompanyId,@CompanyName=CompanyName,@UserName = UserName \n"
				+ "    from _sys_loginuser a where usercode = @UserCode \n"
				+ "    select @DocStatus = PreDocStatus from gform where formid = 710701 \n"
				+ "    select @PeriodId = dbo.GetPeriodID(@FormId,@CompanyID,@Today) \n"
				+ "    exec sp_newdoccode @FormId,@UserCode,@DocCode output \n"
				+ "    insert into t710701(DocCode,FormId,DocDate,CompanyID,CompanyName,DocStatus,EnterCode,EnterName,\n"
				+ "        EnterDate,ModifyName,ModifyDate,CcCode,CcName,PeriodId, \n"
				+ "        Type,RoomId,RoomName,AnchorName,AnchorWechat,SubAnchorWechat,\n"
				+ "        ScreenType,ShopCcCode,Brand,isShowBannerPhotoOnHomePage,OpenId, \n"
				+ "        ShareImgUnid ,FeedsImgUnid ,CoverImgUnid) \n"
				+ "    values(@DocCode,@FormId,@Today,@CompanyID,@CompanyName,@DocStatus,@UserCode,@UserName,\n"
				+ "        getdate(),@UserName,getdate(),@CcCode,@CcName,@PeriodId ,\n"
				+ "        @Type,null,@RoomName,@AnchorName,@AnchorWechat,@SubAnchorWechat, \n"
				+ "        @ScreenType,@ShopCcCode,@DefaultBrand,@isShowBannerPhotoOnHomePage,@OpenId , \n"
				+ "        @ShareImgUnid ,@FeedsImgUnid ,@CoverImgUnid ) \n"
				+ " end \n"
				+ " exec p710701Save @DocCode = @DocCode \n"
				+ " select @DocCode as DocCode \n" ;
		try {
			Map<String, Object> map = this.jdbcTemplate.queryForMap(sql) ;
			String docCode = null ;
			if (map != null) {
				docCode= (String)map.get("DocCode") ;
			}
			return docCode;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
				 
	}

	
	@Override
	public Integer createRoomStep2(CreateLiveRoomParameterStep2Entity createLiveRoomParameterStep2Entity) {
		String sql = " set nocount on ; \n"
				+ " declare @DocCode varchar(20) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep2Entity.getDocCode())
				+",@isCloseComment int ="+createLiveRoomParameterStep2Entity.getCloseComment()
				+",@isCloseLike int ="+createLiveRoomParameterStep2Entity.getCloseLike()
				+",@isCloseShare int = "+createLiveRoomParameterStep2Entity.getCloseShare()+" \n"
				+ " declare @isCloseReplay int = "+createLiveRoomParameterStep2Entity.getCloseReplay()
				+",@isCloseKf int = "+createLiveRoomParameterStep2Entity.getCloseKf()
				+",@isCloseGoods int ="+createLiveRoomParameterStep2Entity.getCloseGoods()
				+",@isFeedsPublic int = "+createLiveRoomParameterStep2Entity.getIsFeedsPublic()+" \n"
				+ " declare @myrowcount int \n"
				+ " update a set isCloseComment=@isCloseComment,isCloseLike = @isCloseLike,isCloseShare=@isCloseShare, \n"
				+ "     isCloseReplay=@isCloseReplay,isCloseKf=@isCloseKf,isCloseGoods=@isCloseGoods,isFeedsPublic = @isFeedsPublic \n"
				+ " from t710701 a where a.DocCode = @DocCode \n"
				+ " select @myrowcount = @@rowcount \n"
				+ " exec p710701Save @DocCode = @DocCode \n"
				+ " select @myrowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer createRoomStep3(CreateLiveRoomParameterStep3Entity createLiveRoomParameterStep3Entity) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = " set nocount on ; \n"
				+ " declare @DocCode varchar(20) = "+GridUtils.prossSqlParm(createLiveRoomParameterStep3Entity.getDocCode())
				+",@StartTime DateTime = "+GridUtils.prossSqlParm(createLiveRoomParameterStep3Entity.getStartDateTime()==null?null:sdf.format(createLiveRoomParameterStep3Entity.getStartDateTime()))
				+",@EndTime datetime = "+GridUtils.prossSqlParm(createLiveRoomParameterStep3Entity.getEndDateTime()==null?null:sdf.format(createLiveRoomParameterStep3Entity.getEndDateTime())) +" \n"
				+ " declare @myrowcount int \n"
				+ " update a set StartTime = @StartTime,EndTime = @EndTime \n"
				+ " from t710701 a where a.DocCode = @DocCode \n"
				+ " select @myrowcount = @@rowcount \n"
				+ " exec p710701Save @DocCode = @DocCode \n"
				+ " select @myrowcount \n";
		try {
			return  this.jdbcTemplate.queryForObject(sql,Integer.class) ;
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer saveAttachment(String docCode,String fieldId,String unid,Integer seq) {
		String sql = " set nocount on ; \n"
				+ " declare @DocCode varchar(20) = "+GridUtils.prossSqlParm(docCode)+",@FieldId varchar(50) = "+GridUtils.prossSqlParm(fieldId)
				+",@Unid varchar(50) = "+GridUtils.prossSqlParm(unid)+",@Seq int = "+seq+" \n"
				+ " declare @isFound int = 0 \n"
				+ " if isnull(@FieldId,'') = 'coverImg' \n"
				+ " begin \n"
				+ "    update a set CoverImgUnid = isnull(@Unid,'') + ';' + cast(@Seq as varchar(20)) from t710701 a where a.DocCode = @DocCode \n"
				+ "    set @isFound = @@rowcount \n"
				+ " end \n"
				+ " if isnull(@FieldId,'') = 'shareImg' \n"
				+ " begin \n"
				+ "    update a set ShareImgUnid = isnull(@Unid,'') + ';' + cast(@Seq as varchar(20)) from t710701 a where a.DocCode = @DocCode \n"
				+ "    set @isFound = @@rowcount \n"
				+ " end \n"
				+ " if isnull(@FieldId,'') = 'feedsImg' \n"
				+ " begin \n"
				+ "    update a set FeedsImgUnid = isnull(@Unid,'') + ';' + cast(@Seq as varchar(20)) from t710701 a where a.DocCode = @DocCode \n"
				+ "    set @isFound = @@rowcount \n"
				+ " end \n"
				+ " if isnull(@FieldId,'') = 'bannerPhoto' \n"
				+ " begin \n"
				+ "    update a set BannerPhoto = isnull(@Unid,'') + ';' + cast(@Seq as varchar(20)) from t710701 a where a.DocCode = @DocCode \n"
				+ "    set @isFound = @@rowcount \n"
				+ " end \n"
				+ " select isnull(@isFound,0) \n" ;
		try {
			return this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
		} catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public LiveRoomEntity getLiveRoom(Integer roomId) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" \n"
				+ " select RoomId,RoomName,CoverImg,LiveStatus,StartTime,EndTime,AnchorName,AnchorImg,ShopCcCode, \n"
				+ "    DATEDIFF(SECOND,getdate(),  a.StartTime)  as LiveBalanceSecond, \n"
				+ "    a.isShowBannerPhotoOnHomePage ,a.BannerPhoto,a.ShareImg,a.CoverImgUnid,a.CoverImgUnidUrl,a.ShareImgUnid,a.ShareImgUnidUrl,a.AnchorImgUnid,a.AnchorImgUnidUrl, \n"
				+ "    a.Type,a.AnchorWechat,a.isFeedsPublic,a.FeedsImgUnid,a.FeedsImgUnidUrl,a.ScreenType,a.isCloseLike,a.isCloseGoods,a.isCloseComment,\n"
				+ "    a.isCloseReplay,a.isCloseShare,a.isCloseKf, \n"
				+ "    a.DocCode,a.DocDate,a.FormId,a.RoomQrCodeUrl,a.RoomQrCodeUrlUnid \n"
				+ " from t710701 a \n"
				+ " where  a.RoomId = @RoomId  \n";
		LiveRoomEntity liveRoomEntity = new LiveRoomEntity() ;
		try {
			Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ; 
			if (map != null) {
				RoomInfo roomInfo = getRoomInfo(map, liveRoomEntity) ;
				List<RoomInfo> roomInfoList = new ArrayList<RoomInfo>(1) ;
				roomInfoList.add(roomInfo);
				liveRoomEntity.setRoomInfo(roomInfoList);
				
				return liveRoomEntity; 
			}
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
		return liveRoomEntity;
	}
	
	@Override
	public Integer saveRoomIdByRoomDocCode(String docCode,Integer roomId,String qrCodeUrl,String qrCodeUrlUnid) {
		String sql = " set nocount on ; \n"
				+ " declare @DocCode varchar(20) = "+GridUtils.prossSqlParm(docCode)+" ,@RoomId int = "+roomId
				+",@QrCodeUrl varchar(500)="+GridUtils.prossSqlParm(qrCodeUrl)+" ,@QrCodeUrlUnid varchar(50) = "+GridUtils.prossSqlParm(qrCodeUrlUnid)+" \n"
				+ " update a set RoomId = @RoomId,QrCodeUrl = @QrCodeUrl,QrCodeUrlUnid = @QrCodeUrlUnid  \n"
				+ " from t710701 a \n"
				+ " where  a.DocCode = @DocCode  \n"
				+ " select @@rowcount \n";
		try {
			return this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public LiveRoomEntity getLiveRoom(String docCode) {
		String sql = " set nocount on ; \n"
				+ " declare @DocCode varchar(20) = "+GridUtils.prossSqlParm(docCode)+" \n"
				+ " select a.RoomId,a.RoomName,a.CoverImg,a.LiveStatus,a.StartTime,a.EndTime,\n"
				+ "    a.AnchorWechat,a.AnchorName,a.AnchorImg,a.AnchorImgUnid,a.AnchorImgUnidUrl,a.SubAnchorWechat,a.ShopCcCode, \n"
				+ "    DATEDIFF(SECOND,getdate(),  a.StartTime)  as LiveBalanceSecond, \n"
				+ "    a.isShowBannerPhotoOnHomePage ,a.BannerPhoto,a.BannerPhotoUrl,a.ShareImg,a.CoverImgUnid,a.CoverImgUnidUrl,a.ShareImgUnid,a.ShareImgUnidUrl, \n"
				+ "    a.Type,a.isFeedsPublic,a.FeedsImgUnid,a.FeedsImgUnidUrl,a.ScreenType,a.isCloseLike,a.isCloseGoods,a.isCloseComment,\n"
				+ "    a.isCloseReplay,a.isCloseShare,a.isCloseKf,a.QrCodeUrl,a.QrCodeUrlUnid, \n"
				+ "    a.DocCode,a.DocDate,a.FormId,a.SubscribeTimes \n"
				+ " from t710701 a \n"
				+ " where  a.DocCode = @DocCode  \n";
		LiveRoomEntity liveRoomEntity = new LiveRoomEntity() ;
		try {
			Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ; 
			if (map != null) {
				RoomInfo roomInfo = getRoomInfo(map, liveRoomEntity) ;
				List<RoomInfo> roomInfoList = new ArrayList<RoomInfo>(1) ;
				roomInfoList.add(roomInfo);
				liveRoomEntity.setRoomInfo(roomInfoList);
				return liveRoomEntity; 
			}
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
		return liveRoomEntity;
	}
	
	@Override
	public LiveRoomEntity getLiveRoomBannerPhotoList(String shopCcCode) {
		String sql = " set nocount on ; \n"
				+ " declare @ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+"  \n"
				
				+ " select a.RoomId,a.RoomName,a.CoverImg,a.LiveStatus,a.StartTime,a.EndTime,\n"
				+ "    a.AnchorWechat,a.AnchorName,a.AnchorImg,a.AnchorImgUnid,a.AnchorImgUnidUrl,a.SubAnchorWechat,a.ShopCcCode, \n"
				+ "    DATEDIFF(SECOND,getdate(),  a.StartTime)  as LiveBalanceSecond, \n"
				+ "    a.isShowBannerPhotoOnHomePage ,a.BannerPhoto,a.BannerPhotoUrl,a.CoverImg,a.CoverImgUnid,a.CoverImgUnidUrl,a.ShareImg,a.ShareImgUnid,a.ShareImgUnidUrl, \n"
				+ "    a.Type,a.isFeedsPublic,a.FeedsImgUnid,a.FeedsImgUnidUrl,a.ScreenType,a.isCloseLike,a.isCloseGoods,a.isCloseComment,\n"
				+ "    a.isCloseReplay,a.isCloseShare,a.isCloseKf,a.QrCodeUrl,QrCodeUrlUnid, \n"
				+ "    a.DocCode,a.DocDate,a.FormId,a.EnterCode,a.EnterName,a.EnterDate \n"
				+ " from t710701 a \n"
				+ " where (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n"
				+ " and isnull(a.RoomId,0)  <> 0 \n"
				+ " order by a.LiveStatus asc,a.StartTime desc,a.EndTime desc \n" ;
		LiveRoomEntity liveRoomEntity = new LiveRoomEntity() ;
		try {
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ; 
			if (list != null) {
				List<RoomInfo> roomInfoList = new ArrayList<RoomInfo>(list.size()) ;
				for (int i = 0;  i < list.size() ; i ++) {	        
			        roomInfoList.add(getRoomInfo(list.get(i), liveRoomEntity) ) ;
				}
				liveRoomEntity.setTotal(list.size());
				liveRoomEntity.setRoomInfo(roomInfoList);
			}
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
		return liveRoomEntity;
	}
	
	@Override
	public LiveRoomEntity getLiveRoomListForMe(Integer roomId,String shopCcCode,String userCode,String docCode) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+",@ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+"  \n"
				+ " declare @UserCode varchar(50) = "+GridUtils.prossSqlParm(userCode)+",@DocCode varchar(20) = "+GridUtils.prossSqlParm(docCode)+" \n"
				
				+ " declare @DefaultBrand varchar(50),@isStartupLeagueShopCcCode int \n"
				//+ " select @isStartupLeagueShopCcCode = a.isStartupLeagueShopCcCode from t714001 a \n"
				
				+ " select top 1 @DefaultBrand = DefaultBrand \n"   
				+ " from _sys_LoginUser where UserCode = @UserCode \n"
				
				+ " select a.RoomId,a.RoomName,a.CoverImg,a.LiveStatus,a.StartTime,a.EndTime,\n"
				+ "    a.AnchorWechat,a.AnchorName,a.AnchorImg,a.AnchorImgUnid,a.AnchorImgUnidUrl,a.SubAnchorWechat,a.ShopCcCode, \n"
				+ "    DATEDIFF(SECOND,getdate(),  a.StartTime)  as LiveBalanceSecond, \n"
				+ "    a.isShowBannerPhotoOnHomePage ,a.BannerPhoto,a.BannerPhotoUrl,a.ShareImg,a.CoverImgUnid,a.CoverImgUnidUrl,a.ShareImgUnid,a.ShareImgUnidUrl, \n"
				+ "    a.Type,a.isFeedsPublic,a.FeedsImgUnid,a.FeedsImgUnidUrl,a.ScreenType,a.isCloseLike,a.isCloseGoods,a.isCloseComment,\n"
				+ "    a.isCloseReplay,a.isCloseShare,a.isCloseKf,a.QrCodeUrl,a.QrCodeUrlUnid, \n"
				+ "    a.DocCode,a.DocDate,a.FormId,a.EnterCode,a.EnterName,a.EnterDate \n"
				+ " from t710701 a \n"
				+ " where (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n"
				+ " and (isnull(@RoomId,0) = 0 or a.RoomId = @RoomId ) \n"
				+ " and (isnull(@DocCode,'') = '' or a.DocCode = @DocCode ) \n"
				
				+ " and ((isnull(@DefaultBrand,'') = '' and isnull(a.Brand,'')= '') or (isnull(@DefaultBrand,'') <> '' and a.Brand = @DefaultBrand or a.EnterCode = @UserCode)) \n"
				+ " order by a.LiveStatus asc,a.StartTime desc,a.EndTime desc \n" ;
		LiveRoomEntity liveRoomEntity = new LiveRoomEntity() ;
		try {
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ; 
			if (list != null) {
				List<RoomInfo> roomInfoList = new ArrayList<RoomInfo>(list.size()) ;
				for (int i = 0;  i < list.size() ; i ++) {	        
			        roomInfoList.add(getRoomInfo(list.get(i), liveRoomEntity) ) ;
				}
				liveRoomEntity.setTotal(list.size());
				liveRoomEntity.setRoomInfo(roomInfoList);
			}
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
		return liveRoomEntity;
	}
	
	@Override
	public LiveRoomEntity getLiveRoomListForOpenId(String openId) {
		String sql = " set nocount on ; \n"
				+ " declare @OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" \n"
				+ " select a.RoomId,a.RoomName,a.CoverImg,a.LiveStatus,a.StartTime,a.EndTime,\n"
				+ "    a.AnchorWechat,a.AnchorName,a.AnchorImg,a.AnchorImgUnid,a.AnchorImgUnidUrl,a.SubAnchorWechat,a.ShopCcCode, \n"
				+ "    DATEDIFF(SECOND,getdate(),  a.StartTime)  as LiveBalanceSecond, \n"
				+ "    a.isShowBannerPhotoOnHomePage ,a.BannerPhoto,a.BannerPhotoUrl,a.CoverImg,a.CoverImgUnid,a.CoverImgUnidUrl,a.ShareImg,a.ShareImgUnid,a.ShareImgUnidUrl, \n"
				+ "    a.Type,a.isFeedsPublic,a.FeedsImgUnid,a.FeedsImgUnidUrl,a.ScreenType,a.isCloseLike,a.isCloseGoods,a.isCloseComment,\n"
				+ "    a.isCloseReplay,a.isCloseShare,a.isCloseKf,a.QrCodeUrl,a.QrCodeUrlUnid, \n"
				+ "    a.DocCode,a.DocDate,a.FormId,a.EnterCode,a.EnterName,a.EnterDate,a.SubscribeTimes \n"
				+ " from t710701 a \n"
				+ " where  a.OpenId = @OpenId  \n"
				+ " order by a.EnterDate desc,a.StartTime desc,a.EndTime desc \n" ;
		LiveRoomEntity liveRoomEntity = new LiveRoomEntity() ;
		try {
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ; 
			if (list != null) {
				List<RoomInfo> roomInfoList = new ArrayList<RoomInfo>(list.size()) ;
				for (int i = 0;  i < list.size() ; i ++) {	        
			        roomInfoList.add(getRoomInfo(list.get(i), liveRoomEntity) ) ;
				}
				liveRoomEntity.setTotal(list.size());
				liveRoomEntity.setRoomInfo(roomInfoList);
			}
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
		return liveRoomEntity;
	}
	
	
	@Override
	public LiveRoomEntity getLiveRoomList(Integer roomId,String shopCcCode,boolean isShowBannerPhotoOnHomePage,
			boolean isShowOwnerLiveRoom,String userCode,String docCode) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+",@ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+" ,@isShowBannerPhotoOnHomePage int = "+(isShowBannerPhotoOnHomePage?1:0) +" \n"
				+ " declare @isShowOwnerLiveRoom int = "+(isShowOwnerLiveRoom?1:0)+", @UserCode varchar(50) = "+GridUtils.prossSqlParm(userCode)+",@DocCode varchar(20) = "+GridUtils.prossSqlParm(docCode)+" \n"
				
				+ " declare @DefaultBrand varchar(50),@isStartupLeagueShopCcCode int \n"
				//+ " select @isStartupLeagueShopCcCode = a.isStartupLeagueShopCcCode from t714001 a \n"
				
				+ " select top 1 @DefaultBrand = DefaultBrand \n"   
				+ " from _sys_LoginUser where UserCode = @UserCode \n"
				+ " select a.RoomId,a.RoomName,a.CoverImg,a.LiveStatus,a.StartTime,a.EndTime,\n"
				+ "    a.AnchorWechat,a.AnchorName,a.AnchorImg,a.AnchorImgUnid,a.AnchorImgUnidUrl,a.SubAnchorWechat,a.ShopCcCode, \n"
				+ "    DATEDIFF(SECOND,getdate(),  a.StartTime)  as LiveBalanceSecond, \n"
				+ "    a.isShowBannerPhotoOnHomePage ,a.BannerPhoto,a.BannerPhotoUrl,a.ShareImg,a.CoverImgUnid,a.CoverImgUnidUrl,a.ShareImgUnid,a.ShareImgUnidUrl, \n"
				+ "    a.Type,a.isFeedsPublic,a.FeedsImgUnid,a.FeedsImgUnidUrl,a.ScreenType,a.isCloseLike,a.isCloseGoods,a.isCloseComment,\n"
				+ "    a.isCloseReplay,a.isCloseShare,a.isCloseKf,a.QrCodeUrl,a.QrCodeUrlUnid, \n"
				+ "    a.DocCode,a.DocDate,a.FormId,a.EnterCode,a.EnterName,a.EnterDate \n"
				+ " from t710701 a \n"
				+ " where (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n"
				+ " and (isnull(@RoomId,0) = 0 or a.RoomId = @RoomId ) \n"
				+ " and (isnull(@DocCode,'') = '' or a.DocCode = @DocCode ) \n"
				+ " and (isnull(@isShowBannerPhotoOnHomePage,0) = 0 or a.isShowBannerPhotoOnHomePage = @isShowBannerPhotoOnHomePage ) \n"
				+ " and (isnull(@isShowOwnerLiveRoom,0) = 0 or (isnull(@isShowOwnerLiveRoom,0) = 1 and (isnull(@DefaultBrand,'') = '' and isnull(a.Brand,'')= '' or isnull(@DefaultBrand,'') <> '' and a.Brand = @DefaultBrand or a.EnterCode = @UserCode)  )) \n"
				+ " order by a.LiveStatus asc,a.StartTime desc,a.EndTime desc \n" ;
		LiveRoomEntity liveRoomEntity = new LiveRoomEntity() ;
		try {
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ; 
			if (list != null) {
				List<RoomInfo> roomInfoList = new ArrayList<RoomInfo>(list.size()) ;
				for (int i = 0;  i < list.size() ; i ++) {	        
			        roomInfoList.add(getRoomInfo(list.get(i), liveRoomEntity) ) ;
				}
				liveRoomEntity.setTotal(list.size());
				liveRoomEntity.setRoomInfo(roomInfoList);
			}
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
		return liveRoomEntity;
	}
	
	private RoomInfo getRoomInfo(Map<String , Object> map,LiveRoomEntity liveRoomEntity) {
		if (map==null ) return null ;
		RoomInfo roomInfo = liveRoomEntity.new RoomInfo() ;
		roomInfo.setName(map.get("RoomName") == null ? "" : (String)map.get("RoomName")) ;
        roomInfo.setId(map.get("RoomId") == null ? null : (Integer)map.get("RoomId")) ;
        roomInfo.setCoverImg(map.get("CoverImg") == null ? "" : (String)map.get("CoverImg")) ;
        roomInfo.setCoverImgUnid(map.get("CoverImgUnid") == null ? "" : (String)map.get("CoverImgUnid")) ;
        roomInfo.setCoverImgUnidUrl(map.get("CoverImgUnidUrl") == null ? "" : (String)map.get("CoverImgUnidUrl")) ;
        roomInfo.setLiveStatus(map.get("LiveStatus") == null ? null : (Integer)map.get("LiveStatus")) ;
        roomInfo.setStartDateTime(map.get("StartTime") == null ? null : (Date)map.get("StartTime")) ;
        roomInfo.setEndDateTime(map.get("EndTime") == null ? null: (Date)map.get("EndTime")) ;
        roomInfo.setStartTime(roomInfo.getStartDateTime()!=null? roomInfo.getStartDateTime().getTime()/1000:null) ;
        roomInfo.setEndTime(roomInfo.getEndDateTime()!=null? roomInfo.getEndDateTime().getTime()/1000:null) ;
        roomInfo.setAnchorName(map.get("AnchorName") == null ? "" : (String)map.get("AnchorName")) ;
        roomInfo.setAnchorImg(map.get("AnchorImg") == null ? "" : (String)map.get("AnchorImg")) ;
        roomInfo.setAnchorImgUnid(map.get("AnchorImgUnid") == null ? "" : (String)map.get("AnchorImgUnid")) ;
        roomInfo.setAnchorImgUnidUrl(map.get("AnchorImgUnidUrl") == null ? "" : (String)map.get("AnchorImgUnidUrl")) ;
        roomInfo.setSubAnchorWechat(map.get("SubAnchorWechat") == null ? "" : (String)map.get("SubAnchorWechat")) ;
        roomInfo.setShareImg(map.get("ShareImg") == null ? "" : (String)map.get("ShareImg")) ;
        roomInfo.setShareImgUnid(map.get("ShareImgUnid") == null ? "" : (String)map.get("ShareImgUnid")) ;
        roomInfo.setShareImgUnidUrl(map.get("ShareImgUnidUrl") == null ? "" : (String)map.get("ShareImgUnidUrl")) ;
        roomInfo.setShopCcCode(map.get("ShopCcCode") == null ? "" : (String)map.get("ShopCcCode")) ;
        roomInfo.setLiveBalanceSecond(map.get("LiveBalanceSecond") == null ? 0 : (Integer)map.get("LiveBalanceSecond")) ;
        roomInfo.setShowBannerPhotoOnHomePage(map.get("isShowBannerPhotoOnHomePage") != null &&map.get("isShowBannerPhotoOnHomePage").equals(1)?true:false) ;
        roomInfo.setBannerPhoto(map.get("BannerPhoto") == null ? "" : (String)map.get("BannerPhoto")) ;
        roomInfo.setBannerPhotoUrl(map.get("BannerPhotoUrl") == null ? "" : (String)map.get("BannerPhotoUrl")) ;
        roomInfo.setType(map.get("Type") == null ? 0 : (Integer)map.get("Type")) ;
        roomInfo.setAnchorWechat(map.get("AnchorWechat") == null ? "" : (String)map.get("AnchorWechat")) ;
        roomInfo.setIsFeedsPublic(map.get("isFeedsPublic") == null?0:(Integer)map.get("isFeedsPublic")) ;
        roomInfo.setFeedsImgUnid(map.get("FeedsImgUnid") == null ? "" : (String)map.get("FeedsImgUnid")) ;
        roomInfo.setFeedsImgUnidUrl(map.get("FeedsImgUnidUrl") == null ? "" : (String)map.get("FeedsImgUnidUrl")) ;
        roomInfo.setScreenType(map.get("ScreenType") == null ? 0 : (Integer)map.get("ScreenType")) ;
        roomInfo.setCloseLike(map.get("isCloseLike") == null?0:(Integer)map.get("isCloseLike")) ;
        roomInfo.setCloseGoods(map.get("isCloseGoods") == null?0:(Integer)map.get("isCloseGoods")) ;
        roomInfo.setCloseComment(map.get("isCloseComment") == null?0:(Integer)map.get("isCloseComment")) ;
        roomInfo.setCloseReplay(map.get("isCloseReplay") == null?0:(Integer)map.get("isCloseReplay")) ;
        roomInfo.setCloseShare(map.get("isCloseShare") == null?0:(Integer)map.get("isCloseShare")) ;
        roomInfo.setCloseKf(map.get("isCloseKf") == null?0:(Integer)map.get("isCloseKf")) ;
        roomInfo.setQrCodeUrl(map.get("QrCodeUrl") == null?"":(String)map.get("QrCodeUrl")) ;
        roomInfo.setQrCodeUrlUnid(map.get("QrCodeUrlUnid") == null?"":(String)map.get("QrCodeUrlUnid")) ;
        roomInfo.setDocCode(map.get("DocCode") == null?"":(String)map.get("DocCode")) ;
        roomInfo.setDocDate(map.get("DocDate") == null?null:(Date)map.get("DocDate")) ;
        roomInfo.setFormId(map.get("FormId") == null?null:(Integer)map.get("FormId")) ;
        roomInfo.setEnterCode(map.get("EnterCode") == null?null:(String)map.get("EnterCode")) ;
        roomInfo.setEnterName(map.get("EnterName") == null?null:(String)map.get("EnterName")) ;
        roomInfo.setEnterDate(map.get("EnterDate") == null?null:(Date)map.get("EnterDate")) ;
        roomInfo.setLiveStatus(map.get("LiveStatus") == null?null:(Integer)map.get("LiveStatus")) ;
        roomInfo.setSubscribeTimes(map.get("SubscribeTimes") == null?0:(Integer)map.get("SubscribeTimes")) ;
        roomInfo.setRoomQrCodeUrl(map.get("RoomQrCodeUrl") == null?"":(String)map.get("RoomQrCodeUrl")) ;
        roomInfo.setRoomQrCodeUrlUnid(map.get("RoomQrCodeUrlUnid") == null?"":(String)map.get("RoomQrCodeUrlUnid")) ;
        return roomInfo;
	}

	@Override
	public LiveReplayVideoEntity getReplayVideo(Integer roomId) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" \n"
				+ " select RoomId,CreateTime,ExpireTime,MediaUrl \n"
				+ " from t710702 a \n"
				+ " order by a.CreateTime asc \n" ;
		LiveReplayVideoEntity liveReplayVideoEntity = new LiveReplayVideoEntity() ;
		try {
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ; 
			if (list != null) {
				List<ReplayVideoEntity> liveReplayList = new ArrayList<ReplayVideoEntity>(list.size()) ;
				for (int i = 0;  i < list.size() ; i ++) {
					Map<String , Object> map = list.get(i);
					ReplayVideoEntity roomInfo = liveReplayVideoEntity.new ReplayVideoEntity() ;
			        roomInfo.setRoomId(map.get("RoomId") == null ? null : (Integer)map.get("RoomId")) ;
			        roomInfo.setCreateDateTime(map.get("CreateTime") == null ? null : (Date)map.get("CreateTime")) ;
			        roomInfo.setExpireDateTime(map.get("ExpireTime") == null ? null : (Date)map.get("ExpireTime")) ;
			        roomInfo.setCreateTime(roomInfo.getCreateDateTime()!=null?format.format(roomInfo.getCreateDateTime()):"") ;
			        roomInfo.setExpireTime(roomInfo.getExpireDateTime()!=null?format.format(roomInfo.getExpireDateTime()):"") ;
			        
			        roomInfo.setMediaUrl(map.get("MediaUrl") == null ? "" : (String)map.get("MediaUrl")) ;
			       
			        liveReplayList.add(roomInfo) ;
				}
				liveReplayVideoEntity.setTotal(list.size());
				liveReplayVideoEntity.setLiveReplay(liveReplayList);
			}
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
		return liveReplayVideoEntity;
	}
	
	@Override
	public Integer saveAddGoods(LiveGoodsEntity goodsEntity) {
		String sql = " set nocount on ; \n"
				+ " declare @MatCode varchar(50) = "+GridUtils.prossSqlParm(goodsEntity.getMatCode())+",@MatName varchar(80),@Special varchar(80),@CoverImgUrl varchar(500) = "+GridUtils.prossSqlParm(goodsEntity.getCoverImgUrl())+" \n"
				+ " declare @PriceType int = "+goodsEntity.getPriceType()+", @Price money = "+goodsEntity.getPrice()+",@Price2 money = "+goodsEntity.getPrice2()+",@URL varchar(200) = "+GridUtils.prossSqlParm(goodsEntity.getUrl())+" \n"
				+ " declare @AuditId bigint = "+goodsEntity.getAuditId()+",@GoodsId bigint = "+goodsEntity.getGoodsId()+",@AuditStatus int = "+goodsEntity.getAuditStatus()+",@ThirdPartyTag int = "+goodsEntity.getThirdPartyTag()+" \n"
				+ " declare @DepositDocCode varchar(50) = "+GridUtils.prossSqlParm(goodsEntity.getDepositDocCode())+", @DepositRowId varchar(20) = "+GridUtils.prossSqlParm(goodsEntity.getDepositRowId())+" \n"
				+ " declare @OpenId varchar(200) = "+GridUtils.prossSqlParm(goodsEntity.getOpenId())+" \n"
				+ " select @MatName = MatName ,@Special = Special from t110503 a where a.MatCode = @MatCode \n"
				+ " if not exists(select 1 from t710703 where MatCode = isnull(@MatCode,'') and DepositDocCode = isnull(@DepositDocCode,'') and DepositRowId = isnull(@DepositRowId,'')) \n"
				+ " begin \n"
				+ "    insert into t710703(GoodsId,MatCode,MatName,Special,CoverImgUrl,PriceType,Price,Price2,URL,AuditId,AuditStatus,ThirdPartyTag,DepositDocCode,DepositRowId,OpenId) \n"
				+ "    values(@GoodsId,isnull(@MatCode,''),isnull(@MatName,''),isnull(@Special,''),@CoverImgUrl,@PriceType,@Price,@Price2,@URL,@AuditId,@AuditStatus,@ThirdPartyTag,isnull(@DepositDocCode,''),isnull(@DepositRowId,''),@OpenId) \n"
				+ " end \n"
				+ " select @@rowcount \n";
		try {
			return this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	
	@Override
	public Integer updateGoodsAuditStatus(LiveGoodsEntity goodsEntity) {
		String sql = " set nocount on ; \n"
				+ " declare @CoverImgUrl varchar(500) = "+GridUtils.prossSqlParm(goodsEntity.getCoverImgUrl())+" \n"
				+ " declare @PriceType int = "+goodsEntity.getPriceType()+", @Price money = "+goodsEntity.getPrice()+",@Price2 money = "+goodsEntity.getPrice2()+",@URL varchar(200) = "+GridUtils.prossSqlParm(goodsEntity.getUrl())+" \n"
				+ " declare @AuditId bigint = "+goodsEntity.getAuditId()+",@GoodsId bigint = "+goodsEntity.getGoodsId()+",@AuditStatus int = "+goodsEntity.getAuditStatus()+",@ThirdPartyTag int = "+goodsEntity.getThirdPartyTag()+" \n"
				
				+ " update a set GoodsId = @GoodsId,CoverImgUrl=@CoverImgUrl,PriceType=@PriceType,Price=@Price,\n"
				+ "    Price2=@Price2,URL=@URL,AuditId=@AuditId,AuditStatus=@AuditStatus,ThirdPartyTag=@ThirdPartyTag \n"
				+ " from t710703 a where a.GoodsId = @GoodsId \n"
				+ " select @@rowcount \n";
		try {
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
			return ret ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer saveUpdateGoods(LiveGoodsEntity goodsEntity) {
		String sql = " set nocount on ; \n"
				+ " declare @MatCode varchar(50) = "+GridUtils.prossSqlParm(goodsEntity.getMatCode())+",@MatName varchar(80),@Special varchar(80),@CoverImgUrl varchar(500) = "+GridUtils.prossSqlParm(goodsEntity.getCoverImgUrl())+" \n"
				+ " declare @PriceType int = "+goodsEntity.getPriceType()+", @Price money = "+goodsEntity.getPrice()+",@Price2 money = "+goodsEntity.getPrice2()+",@URL varchar(200) = "+GridUtils.prossSqlParm(goodsEntity.getUrl())+" \n"
				+ " declare @AuditId bigint = "+goodsEntity.getAuditId()+",@GoodsId bigint = "+goodsEntity.getGoodsId()+",@AuditStatus int = "+goodsEntity.getAuditStatus()+",@ThirdPartyTag int = "+goodsEntity.getThirdPartyTag()+" \n"
				+ " declare @DepositDocCode varchar(50) = "+GridUtils.prossSqlParm(goodsEntity.getDepositDocCode())+", @DepositRowId varchar(20) = "+GridUtils.prossSqlParm(goodsEntity.getDepositRowId())+" \n"
				+ " declare @OpenId varchar(200) = "+GridUtils.prossSqlParm(goodsEntity.getOpenId())+" \n"
				+ " select @MatName = MatName ,@Special = Special from t110503 a where a.MatCode = @MatCode \n"
				+ " update a set GoodsId = @GoodsId,CoverImgUrl=@CoverImgUrl,PriceType=@PriceType,Price=@Price,\n"
				+ "    Price2=@Price2,URL=@URL,AuditId=@AuditId,AuditStatus=@AuditStatus,ThirdPartyTag=@ThirdPartyTag \n"
				+ " from t710703 a where a.GoodsId = @GoodsId \n"
				+ " if @@rowcount = 0 \n"
				+ " begin \n"
				+ "    insert into t710703(GoodsId,MatCode,MatName,Special,CoverImgUrl,PriceType,Price,Price2,URL,AuditId,AuditStatus,ThirdPartyTag,DepositDocCode,DepositRowId,OpenId) \n"
				+ "    values(@GoodsId,isnull(@MatCode,''),isnull(@MatName,''),isnull(@Special,''),@CoverImgUrl,@PriceType,@Price,@Price2,@URL,@AuditId,@AuditStatus,@ThirdPartyTag,isnull(@DepositDocCode,''),isnull(@DepositRowId,''),isnull(@OpenId,'')) \n"
				+ " end \n"
				+ " select @@rowcount \n";
		try {
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
			return ret ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer saveResetGoods(Long goodsId) {
		String sql = " set nocount on ; \n"
				+ " declare  @GoodsId bigint = "+goodsId+" \n"
				+ " update a set AuditId= null,AuditStatus = 0 \n"
				+ " from t710703 a \n"
				+ " where a.GoodsId = @GoodsId \n"
				+ " select @@rowcount \n" ;
		try {
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
			return ret ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer saveRepostGoods(Long goodsId,Long auditId) {
		String sql = " set nocount on ; \n"
				+ " declare  @GoodsId bigint = "+goodsId+", @AuditId bigint = "+auditId+" \n"
				+ " update a set AuditId= @AuditId,AuditStatus = 1 \n"   //audit_status	0：未审核，1：审核中，2:审核通过，3审核失败
				+ " from t710703 a \n"
				+ " where a.GoodsId = @GoodsId \n"
				+ " select @@rowcount \n" ;
		try {
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
			return ret ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer saveDeleteGoods(Long goodsId) {
		String sql = " set nocount on ; \n"
				+ " declare @GoodsId bigint = "+goodsId+" \n"
				+ " delete from t710703 where GoodsId = @GoodsId \n"
				+ " select @@rowcount \n" ;
		try {
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
			return ret ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	
	
	@Override
	public List<LiveGoodsEntity> getGoodsListByExpiredDays(Integer expiredDays) {
		String sql = " set nocount on \n"
				+ " declare @now datetime = getdate(),@ExpiredDays int = "+expiredDays+" \n"
				+ " select a.GoodsId,a.MatCode,a.MatName,a.Special,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag, \n"
				+ "   a.DepositDocCode,a.DepositRowId, \n"
				+ "   case when exists(select 1 from t710704 g where a.GoodsId = g.GoodsId) then 1 else 0 end as isAddedToRoom, \n"
				+ "   b.DocCode,b.OriginalPrice,b.SalesPrice,b.Title ,b.CoverImage,b.CoverImageUrl,b.FormId,\n"
				+ "   b.EffectiveStartDate,b.EffectiveEndDate,b.Status as PanicBuyingStatus,b.VisiteTimes,b.SharedTimes,b.OrderTimes \n"
				+ " from t710703 a  \n"
				+ " join t710802H b on a.MatCode = b.DocCode \n"
				+ " where  DATEDIFF(day,b.EffectiveEndDate,@now) >= @ExpiredDays  and b.Status = 3 \n"
				+ " order by a.MatName asc \n" ;
		try {		
			List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql) ;
			List<LiveGoodsEntity> liveGoodsEntities = new ArrayList<LiveGoodsEntity>();
			for (int i = 0; list != null&& i < list.size(); i++) {
				liveGoodsEntities.add(getGoodsEntity(list.get(i)));
			}
			return liveGoodsEntities ;
		}catch(DataAccessException e ) {
			if (e instanceof EmptyResultDataAccessException){
				return null ;
			}else {
				 throw e ; 
			}
		}catch(Exception e){
			e.printStackTrace();
		    throw e;
		}
		
	}
	
	@Override
	public List<LiveGoodsEntity> getGoodsListForMe(String openId) {
		String sql = " set nocount on ; \n"
				+ " declare @OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+"  \n"
				+ " select a.GoodsId,a.MatCode,a.MatName,a.Special,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag, \n"
				+ "   a.DepositDocCode,a.DepositRowId, \n"
				+ "   case when exists(select 1 from t710704 g where a.GoodsId = g.GoodsId) then 1 else 0 end as isAddedToRoom, \n"
				+ "   b.DocCode,b.OriginalPrice,b.SalesPrice,b.Title ,b.CoverImage,b.CoverImageUrl,b.FormId,\n"
				+ "   b.EffectiveStartDate,b.EffectiveEndDate,b.Status as PanicBuyingStatus,b.VisiteTimes,b.SharedTimes,b.OrderTimes \n"
				+ " from t710703 a  \n"
				+ " join t710802H b on a.MatCode = b.DocCode \n"
				+ " where  a.OpenId = @OpenId \n"
				+ " order by a.MatName asc \n" ;
		try {
			List<Map<String,Object>> list  = this.jdbcTemplate.queryForList(sql) ; 
			List<LiveGoodsEntity> liveGoodsEntities = new ArrayList<LiveGoodsEntity>();
			for (int i = 0; list != null&& i < list.size(); i++) {
				liveGoodsEntities.add(getGoodsEntity(list.get(i)));
			}
			return liveGoodsEntities ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	
	@Override
	public LiveGoodsEntity getLiveGoodsEntity(Long goodsId) {
		String sql = " set nocount on ; \n"
				+ " declare @GoodsId bigint = "+goodsId+"  \n"
				+ " select top 1 a.GoodsId,a.MatCode,a.MatName,a.Special,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag, \n"
				+ "   a.DepositDocCode,a.DepositRowId, \n"
				+ "   case when exists(select 1 from t710704 g where a.GoodsId = g.GoodsId) then 1 else 0 end as isAddedToRoom, \n"
				+ "   b.DocCode,b.OriginalPrice,b.SalesPrice,b.Title ,b.CoverImage,b.CoverImageUrl,b.FormId, \n"
				+ "   b.EffectiveStartDate,b.EffectiveEndDate,b.Status as PanicBuyingStatus,b.VisiteTimes,b.SharedTimes,b.OrderTimes \n"
				+ " from t710703 a  \n"
				+ " join t710802H b on a.MatCode = b.DocCode \n"
				+ " where  a.GoodsId = @GoodsId \n"
				+ " order by a.MatName asc \n" ;
		try {
			Map<String,Object> map  = this.jdbcTemplate.queryForMap(sql) ; 
			if (map!=null) {
				return getGoodsEntity(map);
			}else {
				return null;
			}
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public List<LiveGoodsEntity> getGoodsListForImportRoom(Integer roomId,String openId) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+",@OpenId varchar(200) = "+ GridUtils.prossSqlParm(openId)+"  \n"
				+ " select a.GoodsId,a.MatCode,a.MatName,a.Special,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag, \n"
				+ "   a.DepositDocCode,a.DepositRowId, \n"
				+ "   case when exists(select 1 from t710704 g where a.GoodsId = g.GoodsId) then 1 else 0 end as isAddedToRoom, \n"
				+ "   b.DocCode,b.OriginalPrice,b.SalesPrice,b.Title ,b.CoverImage,b.CoverImageUrl,b.FormId, \n"
				+ "   b.EffectiveStartDate,b.EffectiveEndDate,b.Status as PanicBuyingStatus,b.VisiteTimes,b.SharedTimes,b.OrderTimes \n"
				+ " from t710703 a  \n"
				+ " join t710802H b on a.MatCode = b.DocCode \n"
				+ " where a.OpenId = @OpenId and a.AuditStatus = 2 \n"
				+ " and not exists(select 1 from t710704 c where c.RoomId = @RoomId and a.GoodsId = c.GoodsId) \n"
				+ " order by a.MatName asc \n" ;
		try {
			List<Map<String,Object>> list  = this.jdbcTemplate.queryForList(sql) ; 
			List<LiveGoodsEntity> liveGoodsEntities = new ArrayList<LiveGoodsEntity>();
			for (int i = 0; list != null&& i < list.size(); i++) {
				liveGoodsEntities.add(getGoodsEntity(list.get(i)));
			}
			return liveGoodsEntities ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	
	@Override
	public List<LiveGoodsEntity> getGoodsList(Integer roomId,String shopCcCode,String openId) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+",@ShopCcCode varchar(50) = "+GridUtils.prossSqlParm(shopCcCode)+",@OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" ,@DefaultBrand varchar(50) ,@Telephone varchar(50) \n"
				+ " declare @UserId varchar(50) ,@isStartupLeagueShopCcCode int \n"
				+ " select @isStartupLeagueShopCcCode = a.isStartupLeagueShopCcCode from t714001 a \n"
				+ " select top 1 @Telephone = a.Tel from t730102 a where a.OpenId = @OpenId \n"
				+ " if isnull(@Telephone,'') <> ''  \n"
				+ " begin \n"
				+ "     select top 1  @UserId = UserId from t700107 a where a.Mobile = @Telephone and isnull(a.isAiRadarUser,0) = 1"
				+ "     if isnull(@UserId,'') <> '' \n"
				+ "     begin \n"
				+ "        select top 1 @DefaultBrand = DefaultBrand \n"   
    			+ "        from _sys_LoginUser  \n"
    			+ "        where isnull(WeiXinCorpUserId,'') <> '' and WeiXinCorpUserId  = @UserId  and inActive<>1 \n"
				+ "     end \n"
				+ " end \n"
				+ " select a.GoodsId,a.MatCode,b.MatName,b.Special,b.PhotoPath,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag, \n"
				+ "   a.DepositDocCode,a.DepositRowId, \n"
				+ "   case when exists(select 1 from t710704 g where g.RoomId = @RoomId and a.GoodsId = g.GoodsId) then 1 else 0 end as isAddedToRoom \n"
				+ " from t710703 a join t110503 b on a.MatCode = b.MatCode \n"
				+ " where (isnull(@ShopCcCode,'') = '' or b.ShopCcCode = @ShopCcCode ) \n"
				+ " and (isnull(@isStartupLeagueShopCcCode,0) = 0  \n"
				+ "     or (isnull(@isStartupLeagueShopCcCode,0) = 1 and (isnull(@DefaultBrand,'') = '' or b.Brand = @DefaultBrand or b.Brand in (select list from getinstr(@DefaultBrand))))) \n"
				+ " and a.AuditStatus = 2 \n"   //只取审核通过的商品
				+ " order by a.MatName asc \n" ;
		try {
			List<Map<String,Object>> list  = this.jdbcTemplate.queryForList(sql) ; 
			List<LiveGoodsEntity> liveGoodsEntities = new ArrayList<LiveGoodsEntity>();
			for (int i = 0; list != null&& i < list.size(); i++) {
				liveGoodsEntities.add(getGoodsEntity(list.get(i)));
			}
			return liveGoodsEntities ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	
	@Override
	public Integer updateGoodsSortInRoom(GoodsSortInRoomPamaraterEntity goodsSortInRoomPamaraterEntity) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+goodsSortInRoomPamaraterEntity.getRoomId()+" , @isFound int = 0 \n";
		for (int i = 0; goodsSortInRoomPamaraterEntity!=null&&goodsSortInRoomPamaraterEntity.getGoodsSortList()!=null&& i < goodsSortInRoomPamaraterEntity.getGoodsSortList().size(); i++) {
			GoodsSortInRoomEntity goodsSortInRoomEntity = goodsSortInRoomPamaraterEntity.getGoodsSortList().get(i);
			sql += " update a set DocItem = " + goodsSortInRoomEntity.getDocItem() + " from t710704 a where a.RoomId = @RoomId and a.GoodsId = " + goodsSortInRoomEntity.getGoodsId() + " \n"
					+ " set @isFound = isnull(@isFound,0) + 1 \n";
		}
		sql += " select @isFound as isFound \n";
		try {
			return this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public List<LiveGoodsEntity> getGoodsListInRoom(Integer roomId) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" \n"
				+ " select a.DocItem,a.GoodsId,b.MatCode,a.MatName,a.CoverImg,a.Url,\n"
				+ "   b.PriceType,a.Price,a.Price2,a.RoomId,a.OnSale, \n"
				+ "   c.DocCode,c.OriginalPrice,c.SalesPrice,c.Title ,c.CoverImage,c.CoverImageUrl,c.FormId,\n"
				+ "   c.EffectiveStartDate,c.EffectiveEndDate,c.Status as PanicBuyingStatus,c.VisiteTimes,c.SharedTimes,c.OrderTimes \n"
				+ " from t710704 a join t710703 b on a.GoodsId = b.GoodsId \n"
				+ " join t710802H c on b.MatCode = c.DocCode \n"   //关联活动表
				+ " where a.RoomId = @RoomId \n" 
				+ " order by a.DocItem asc,a.MatName asc \n" ;
		try {
			List<Map<String,Object>> list  = this.jdbcTemplate.queryForList(sql) ; 
			List<LiveGoodsEntity> liveGoodsEntities = new ArrayList<LiveGoodsEntity>();
			for (int i = 0; list != null&& i < list.size(); i++) {
				liveGoodsEntities.add(getGoodsEntity(list.get(i)));
			}
			return liveGoodsEntities ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	
	
	@Override
	public LiveGoodsEntity getGoodsInRoom(Integer roomId,Long goodsId) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" , @GoodsId bigint = "+goodsId+" \n"
				+ " select a.DocItem,a.GoodsId,b.MatCode,a.MatName,a.CoverImg,a.Url,\n"
				+ "   b.PriceType,a.Price,a.Price2,a.RoomId,a.OnSale, \n"
				+ "   c.DocCode,c.OriginalPrice,c.SalesPrice,c.Title ,c.CoverImage,c.CoverImageUrl,c.FormId,\n"
				+ "   c.EffectiveStartDate,c.EffectiveEndDate,c.Status as PanicBuyingStatus,c.VisiteTimes,c.SharedTimes,c.OrderTimes \n"
				+ " from t710704 a join t710703 b on a.GoodsId = b.GoodsId \n"
				+ " join t710802H c on b.MatCode = c.DocCode \n"   //关联活动表
				+ " where a.RoomId = @RoomId and a.GoodsId = @GoodsId \n" 
				+ " order by a.DocItem asc,a.MatName asc \n" ;
		try {
			Map<String,Object> map  = this.jdbcTemplate.queryForMap(sql) ; 
			if (map !=null) {
				return getGoodsEntity(map) ;
			}else {
				return null ;
			}
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public LiveGoodsEntity getGoodsEntity(Long goodsId) {
		String sql = " set nocount on ; \n"
				+ " declare @GoodsId bigint = "+goodsId+" \n"
				+ " select top 1 a.GoodsId,a.MatCode,b.MatName,b.Special,b.PhotoPath,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag, \n"
				+ "   a.DepositDocCode,a.DepositRowId \n"
				+ " from t710703 a join t110503 b on a.MatCode = b.MatCode \n"
				+ " where a.GoodsId = @GoodsId \n" ;
		try {
			Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ; 
			return getGoodsEntity(map) ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public List<LiveGoodsEntity> getGoodsEntityForPanicBuyingByAuditing() {
		String sql = " set nocount on ; \n"
				+ " select a.GoodsId,a.MatCode,a.MatName,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag,\n"
				+ "   a.DepositDocCode,a.DepositRowId \n"
				+ " from t710703 a  \n"
				+ " where a.AuditStatus =  0 \n" 
				+ " union  \n"
				+ " select a.GoodsId,a.MatCode,a.MatName,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag,\n"
				+ "   a.DepositDocCode,a.DepositRowId \n"
				+ " from t710703 a  \n"
				+ " where a.AuditStatus =  1 \n" ;
		
		try {
			List<LiveGoodsEntity> liveGoodsList = new ArrayList<LiveGoodsEntity>();
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ; 
			for (int i = 0; list!= null&& i < list.size(); i++) {
				liveGoodsList.add(getGoodsEntity(list.get(i))) ;
			}
			return liveGoodsList ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public LiveGoodsEntity getGoodsEntityForPanicBuying(String docCode ) {
		String sql = " set nocount on ; \n"
				+ " declare @MatCode varchar(50) = "+GridUtils.prossSqlParm(docCode)+" \n"
				+ " select top 1 a.GoodsId,a.MatCode,a.MatName,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag,\n"
				+ "   a.DepositDocCode,a.DepositRowId \n"
				+ " from t710703 a  \n"
				+ " where a.MatCode = isnull(@MatCode,'')  \n" ;
		try {
			Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ; 
			return getGoodsEntity(map) ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public LiveGoodsEntity getGoodsEntity(String matCode ,String depositDocCode,String depositRowId) {
		String sql = " set nocount on ; \n"
				+ " declare @MatCode varchar(50) = "+GridUtils.prossSqlParm(matCode)+",@DepositDocCode varchar(50) = "+GridUtils.prossSqlParm(depositDocCode)+", @DepositRowId varchar(20) = "+GridUtils.prossSqlParm(depositRowId)+" \n"
				+ " select top 1 a.GoodsId,a.MatCode,b.MatName,b.Special,b.PhotoPath,a.CoverImgUrl,\n"
				+ "   a.PriceType,a.Price,a.Price2,a.URL,a.AuditId,a.AuditStatus,a.ThirdPartyTag,a.DepositDocCode,a.DepositRowId \n"
				+ " from t710703 a join t110503 b on a.MatCode = b.MatCode \n"
				+ " where a.MatCode = isnull(@MatCode,'') and a.DepositDocCode = isnull(@DepositDocCode,'')  and a.DepositRowId = isnull( @DepositRowId,'') \n" ;
		try {
			Map<String,Object> map = this.jdbcTemplate.queryForMap(sql) ; 
			return getGoodsEntity(map) ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	private LiveGoodsEntity getGoodsEntity(Map<String,Object> map) {
		if (map == null)  return null ;
		LiveGoodsEntity goodsEntity = new LiveGoodsEntity() ;
		goodsEntity.setRoomId((Integer)map.get("RoomId")) ;
		goodsEntity.setGoodsId((Long)map.get("GoodsId")) ;
		goodsEntity.setMatCode((String)map.get("MatCode")) ;
		goodsEntity.setMatName((String)map.get("MatName")) ;
		goodsEntity.setSpecial((String)map.get("Special")) ;
		goodsEntity.setPhotoPath((String)map.get("PhotoPath")) ;
		goodsEntity.setCoverImg((String)map.get("CoverImg")) ;
		goodsEntity.setCoverImgUrl((String)map.get("CoverImgUrl")) ;
		goodsEntity.setPriceType((Integer)map.get("PriceType")) ;
		goodsEntity.setPrice(map.get("Price")==null?null:Double.valueOf(map.get("Price").toString())) ;
		goodsEntity.setPrice2(map.get("Price2")==null?null:Double.valueOf(map.get("Price2").toString())) ;
		goodsEntity.setUrl((String)map.get("URL")) ;
		goodsEntity.setAuditId((Long)map.get("AuditId")) ;
		goodsEntity.setAuditStatus((Integer)map.get("AuditStatus")) ;
		goodsEntity.setThirdPartyTag((Integer)map.get("ThirdPartyTag")) ;
		goodsEntity.setDepositAmount(map.get("DepositAmount")==null?null:Double.valueOf(map.get("DepositAmount").toString())) ;
		goodsEntity.setDepositDocCode((String)map.get("DepositDocCode")) ;
		goodsEntity.setDepositRowId((String)map.get("DepositRowId")) ;
		goodsEntity.setAddedToRoom(map.get("isAddedToRoom")!=null&&map.get("isAddedToRoom").equals(1)?true:false) ;
		goodsEntity.setOnSale(map.get("OnSale")==null?0:(Integer)map.get("OnSale")) ;
		goodsEntity.setDocItem(map.get("DocItem")==null?0:(Integer)map.get("DocItem")) ;
		
		//增加一堆与活动相关的参数 
		goodsEntity.setDocCode(map.get("DocCode")==null?"":(String)map.get("DocCode"));
		goodsEntity.setFormId(map.get("FormId")==null?null:(Integer)map.get("FormId"));
		goodsEntity.setTitle(map.get("Title")==null?"":(String)map.get("Title"));
		goodsEntity.setCoverImage(map.get("CoverImage")==null?"":(String)map.get("CoverImage"));
		goodsEntity.setCoverImageUrl(map.get("CoverImageUrl")==null?"":(String)map.get("CoverImageUrl"));
		goodsEntity.setOriginalPrice(map.get("OriginalPrice")== null?0L:Double.parseDouble(map.get("OriginalPrice").toString()));
		goodsEntity.setSalesPrice(map.get("SalesPrice")== null?0L:Double.parseDouble(map.get("SalesPrice").toString()));
		goodsEntity.setNumberOfTeam(map.get("NumberOfTeam")== null?0:(Integer)map.get("NumberOfTeam"));
		goodsEntity.setEffectiveStartDate(map.get("EffectiveStartDate")==null?null:(Date)map.get("EffectiveStartDate"));
		goodsEntity.setEffectiveEndDate(map.get("EffectiveEndDate")==null?null:(Date)map.get("EffectiveEndDate"));
		goodsEntity.setPanicBuyingStatus(map.get("PanicBuyingStatus")==null?null:(Integer)map.get("PanicBuyingStatus"));
		goodsEntity.setVisiteTimes(map.get("VisiteTimes")==null?0:(Integer)map.get("VisiteTimes"));
		goodsEntity.setSharedTimes(map.get("SharedTimes")==null?0:(Integer)map.get("SharedTimes"));
		goodsEntity.setOrderTimes(map.get("OrderTimes")==null?0:(Integer)map.get("OrderTimes"));
		
		return goodsEntity; 
	}

	@Override
	public Integer updateLiveStatus(Integer roomId ,Integer liveStatus) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+" ,@LiveStatus int = "+liveStatus+" \n"
				+ " update a set LiveStatus = @LiveStatus \n"
				+ " from t710701 a \n"
				+ " where a.RoomId = @RoomId \n"
				+ " select @@rowcount \n" ;
		try {
			Integer ret = this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
			return ret ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public LiveRoomEntity getLiveStatus(String shopCcCode ) {
		String sql = " set nocount on ; \n"
				+ " declare @ShopCcCode varchar(2000) = "+GridUtils.prossSqlParm(shopCcCode)+"  \n"
				+ " select RoomId,RoomName,CoverImg,LiveStatus,StartTime,EndTime,AnchorName,AnchorImg,ShopCcCode, \n"
				+ "    DATEDIFF(SECOND,getdate(),  a.StartTime)  as LiveBalanceSecond, \n"
				+ "    a.isShowBannerPhotoOnHomePage , a.BannerPhoto,a.BannerPhotoUrl \n"
				+ " from t710701 a \n"
				+ " where (isnull(@ShopCcCode,'') = '' or a.ShopCcCode = @ShopCcCode) \n"
				+ " and a.LiveStatus in (101,102) \n" ; // 只查询状态：  101: 直播中, 102: 未开始 
		LiveRoomEntity liveRoomEntity = new LiveRoomEntity() ; 
		try {
			List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql) ; 
			if (list != null) {
				List<RoomInfo> roomInfoList = new ArrayList<RoomInfo>(list.size()) ;
				for (int i = 0;  i < list.size() ; i ++) {	        
			        roomInfoList.add(getRoomInfo(list.get(i), liveRoomEntity) ) ;
				}
				liveRoomEntity.setTotal(list.size());
				liveRoomEntity.setRoomInfo(roomInfoList);
			}
			return liveRoomEntity ;
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	
	@Override
	public Integer addLiveSubscribeUser(LiveSubscribeUserEntity liveSubscribeUserEntity ) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		try {
			Date subscribeDateTime = null ;
			if (liveSubscribeUserEntity.getSubscribeTime() != null) {
				subscribeDateTime = new Date(liveSubscribeUserEntity.getSubscribeTime()*1000);
			} else {
				subscribeDateTime = new Date(); 
			}
			
			String sql = " set nocount on ; \n"
					+ " declare @RoomId int = "+liveSubscribeUserEntity.getRoomId()+",@OpenId varchar(200) = "+GridUtils.prossSqlParm(liveSubscribeUserEntity.getOpenId())
					+",@SubscribeTime bigint = "+liveSubscribeUserEntity.getSubscribeTime()
					+",@SubscribeDateTime datetime = "+GridUtils.prossSqlParm(subscribeDateTime==null?null:sdf.format(subscribeDateTime))
					+",@RoomStatus int = "+liveSubscribeUserEntity.getRoomStatus()+" \n"
					+ " declare @NickName nvarchar(400),@WeiXinAvatarUnid varchar(50) \n"
					+ " declare @myrowcount int \n"
					+ " update a set SubscribeTime = @SubscribeTime,SubscribeDateTime=@SubscribeDateTime,RoomStatus = @RoomStatus \n"
					+ " from t710708 a where a.RoomId = @RoomId and a.OpenId = @OpenId \n"
					+ " select @myrowcount = @@rowcount \n"
					+ " if @myrowcount = 0 \n"
					+ " begin \n"
					+ "    select @NickName = a.NickName,@WeiXinAvatarUnid = a.WeiXinAvatarUnid from t730102 a where a.OpenId = @OpenId \n"
					+ "    insert into t710708(RoomId,OpenId,NickName,WeiXinAvatarUnid,SubscribeTime,SubscribeDateTime,RoomStatus) \n"
					+ "    values(@RoomId,@OpenId,@NickName,@WeiXinAvatarUnid,@SubscribeTime,@SubscribeDateTime,@RoomStatus ) \n"
					+ "    select @myrowcount = @@rowcount \n"
					+ " end \n"
					+ " update a set SubscribeTimes = isnull(a.SubscribeTimes,0) + 1 from t710701 a where a.RoomId = @RoomId \n"
					+ " select @myrowcount as myrowcount \n" ; //  订阅或者取消订阅时直播间状态，取值：101（直播中），102（未开始），103（已结束）
			
			return this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
	
	@Override
	public Integer deleteLiveSubscribeUser(Integer roomId,String openId) {
		String sql = " set nocount on ; \n"
				+ " declare @RoomId int = "+roomId+",@OpenId varchar(200) = "+GridUtils.prossSqlParm(openId)+" \n"
				+ " declare @myrowcount int \n"
				+ " delete a  \n"
				+ " from t710708 a where a.RoomId = @RoomId and a.OpenId = @OpenId \n"
				+ " select @myrowcount = @@rowcount \n"
				+ " update a set SubscribeTimes = isnull(a.SubscribeTimes,0) - 1 from t710701 a where a.RoomId = @RoomId \n"
				+ " select @myrowcount as myrowcount \n" ; //  订阅或者取消订阅时直播间状态，取值：101（直播中），102（未开始），103（已结束）
		try {
			
			return this.jdbcTemplate.queryForObject(sql,Integer.class) ; 
			
		}catch(DataAccessException e){
			if (e  instanceof EmptyResultDataAccessException) {
				return null ;
			} else {
				throw e ;
			}
		}catch(Exception e){
			throw e ;
		}
	}
}