/// <summary> /// 获取首页数据 /// </summary> /// <returns></returns> public DataTable HomeData() { string sql = string.Format(@" select (select COUNT(1) from owzx_users where admingid=1) users,(select isnull(sum(luckresult),0) from owzx_bettprofitloss where luckresult>0) money "); return(RDBSHelper.ExecuteTable(sql, null)[0]); }
/// <summary> ///获取用户转账记录记录(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize">-1 取全部</param> /// <param name="condition">没有where</param> /// <returns></returns> public DataTable GetUserRemitList(int pageIndex, int pageSize, string condition = "") { DbParameter[] parms = { GenerateInParam("@pagesize", SqlDbType.Int, 4, pageSize), GenerateInParam("@pageindex", SqlDbType.Int, 4, pageIndex) }; string commandText = string.Format(@" begin try if OBJECT_ID('tempdb..#list') is not null drop table #list SELECT ROW_NUMBER() over(order by a.remitid desc) id ,a.[remitid] ,a.[uid] ,a.[type] ,a.[name] ,a.[account] ,a.[money],a.realmoney ,a.[bankname] ,a.[status] ,a.[remark] ,a.[addtime] ,a.[updateuid] ,a.[updatetime],b.mobile mobile,a.chargeremark,b.username into #list FROM owzx_userremit a join owzx_users b on a.uid=b.uid {0} declare @total int select @total=(select count(1) from #list) if(@pagesize=-1) begin select *,@total TotalCount from #list end else begin select *,@total TotalCount from #list where id>@pagesize*(@pageindex-1) and id <=@pagesize*@pageindex end end try begin catch select ERROR_MESSAGE() state end catch ", condition); return(RDBSHelper.ExecuteTable(CommandType.Text, commandText, parms)[0]); }
/// <summary> ///获取用户消息记录(分页) /// </summary> /// <param name="pageNumber"></param> /// <param name="pageSize">-1 取全部</param> /// <param name="condition">没有where</param> /// <returns></returns> public DataTable GetMessageList(int pageNumber, int pageSize, string condition = "") { DbParameter[] parms = { GenerateInParam("@pagesize", SqlDbType.Int, 4, pageSize), GenerateInParam("@pageindex", SqlDbType.Int, 4, pageNumber) }; string commandText = string.Format(@" begin try if OBJECT_ID('tempdb..#list') is not null drop table #list SELECT ROW_NUMBER() over(order by a.msgid desc) id ,a.[msgid] ,a.[uid] ,a.[title] ,a.[body] ,a.[addtime],b.mobile account into #list FROM owzx_message a join owzx_users b on a.uid=b.uid {0} declare @total int select @total=(select count(1) from #list) if(@pagesize=-1) begin select *,@total TotalCount from #list end else begin select *,@total TotalCount from #list where id>@pagesize*(@pageindex-1) and id <=@pagesize*@pageindex end end try begin catch select ERROR_MESSAGE() state end catch ", condition); return(RDBSHelper.ExecuteTable(CommandType.Text, commandText, parms)[0]); }
/// <summary> ///获取系统设置记录(分页) /// </summary> /// <param name="pageNumber"></param> /// <param name="pageSize">-1 取全部</param> /// <param name="condition">没有where</param> /// <returns></returns> public DataTable GetSetList(int pageNumber, int pageSize, string condition = "") { DbParameter[] parms = { GenerateInParam("@pagesize", SqlDbType.Int, 4, pageSize), GenerateInParam("@pageindex", SqlDbType.Int, 4, pageNumber) }; string commandText = string.Format(@" begin try if OBJECT_ID('tempdb..#list') is not null drop table #list SELECT ROW_NUMBER() over(order by a.setid ) id ,a.[setid] ,a.[parentid] ,a.[name] ,a.[invalue] ,a.[addtime] into #list FROM owzx_sys_set a {0} declare @total int select @total=(select count(1) from #list) if(@pagesize=-1) begin select *,@total TotalCount from #list end else begin select *,@total TotalCount from #list where id>@pagesize*(@pageindex-1) and id <=@pagesize*@pageindex end end try begin catch select ERROR_MESSAGE() state end catch ", condition); return(RDBSHelper.ExecuteTable(CommandType.Text, commandText, parms)[0]); }
/// <summary> ///获取验证码记录(分页) /// </summary> /// <param name="pageNumber"></param> /// <param name="pageSize">-1 取全部</param> /// <param name="condition">没有where</param> /// <returns></returns> public DataTable GetSMSCodeList(int pageNumber, int pageSize, string condition = "") { DbParameter[] parms = { GenerateInParam("@pagesize", SqlDbType.Int, 4, pageSize), GenerateInParam("@pageindex", SqlDbType.Int, 4, pageNumber) }; string commandText = string.Format(@" begin try if OBJECT_ID('tempdb..#list') is not null drop table #list SELECT ROW_NUMBER() over(order by a.codeid desc) id ,a.[codeid] ,a.[account] ,a.[code] ,a.[expiretime] ,a.[addtime] into #list FROM owzx_usersmscode a {0} if(@pagesize=-1) begin select * from #list end else begin select * from #list where id>@pagesize*(@pageindex-1) and id <=@pagesize*@pageindex end end try begin catch select ERROR_MESSAGE() state end catch ", condition); return(RDBSHelper.ExecuteTable(CommandType.Text, commandText, parms)[0]); }
/// <summary> /// 获取虚拟用户 /// </summary> /// <returns></returns> public DataTable GetDummyUser(int pageNumber, int pageSize, string condition = "") { DbParameter[] parms = { GenerateInParam("@pagesize", SqlDbType.Int, 4, pageSize), GenerateInParam("@pageindex", SqlDbType.Int, 4, pageNumber) }; string sql = string.Format(@" begin try if OBJECT_ID('tempdb..#list') is not null drop table #list select ROW_NUMBER() over(order by dummyid) id,[dummyid] ,[username] ,[nickname],money,bettime,roomname,bettype,isnull(avatar,'') avatar,vip,isnull(start,'00:00')start,isnull([end],'00:00') [end] into #list FROM [owzx_dummyinfo] {0} declare @total int select @total=(select count(1) from #list) if(@pagesize=-1) begin select *,@total totalCount from #list end else begin select *,@total totalCount from #list where id>@pagesize*(@pageindex-1) and id <=@pagesize*@pageindex end end try begin catch select ERROR_MESSAGE() state end catch ", condition); return(RDBSHelper.ExecuteTable(CommandType.Text, sql, parms)[0]); }
/// <summary> ///获取彩票记录(分页) /// </summary> /// <param name="pageNumber">页索引</param> /// <param name="pageSize">-1 取全部</param> /// <param name="account">账号</param> /// <param name="condition">没有where</param> /// <returns></returns> public DataTable GetUserBettList(int pageNumber, int pageSize, string account, string condition = "") { DbParameter[] parms = { GenerateInParam("@pagesize", SqlDbType.Int, 4, pageSize), GenerateInParam("@pageindex", SqlDbType.Int, 4, pageNumber) }; string commandText = string.Format(@" begin try if OBJECT_ID('tempdb..#listpage') is not null drop table #listpage if OBJECT_ID('tempdb..#listresult') is not null drop table #listresult if OBJECT_ID('tempdb..#listuser') is not null drop table #listuser select a.uid,a.bttypeid, a.bettid,a.lotterynum,a.money,c.lotteryid, c.first,c.second,c.three,c.type,c.resulttype,c.result,c.resultnum, d.item uitem,a.addtime into #listuser from owzx_bett a join owzx_users b on a.uid=b.uid and rtrim(b.mobile)='{0}' join owzx_lotteryrecord c on a.lotterynum=c.expect join owzx_lotteryset d on a.bttypeid=d.bttypeid {1} select a.type,a.lotterynum,b.item into #listresult from (select distinct lotterynum,resultnum,result,type,first,second,three from #listuser ) a join owzx_lotteryset b on CHARINDEX(','+rtrim(a.resultnum)+',',','+rtrim(b.nums)+',')>0 and a.result is not null and b.roomtype=20 union all select a.type,a.lotterynum,'豹子' from (select distinct lotterynum,resultnum,result,type,first,second,three from #listuser ) a where a.first=a.second and a.first=a.three if OBJECT_ID('tempdb..#list') is not null drop table #list select ROW_NUMBER() over(order by bettid desc) id,* into #listpage from ( select a.bettid,c.type ,a.lotterynum expect,a.result,left(d.items,LEN(d.items)-1)items, '大小单双' as bttype,a.uitem userbttype,a.money, case when b.luckresult>0 then b.luckresult else 0.00 end lkmoney,case when b.luckresult>0 then b.luckresult-a.money else b.luckresult end winmoney,a.addtime FROM #listuser a join owzx_bettprofitloss b on a.uid=b.uid and a.lotteryid=b.lotteryid and a.bettid=b.bettid join owzx_sys_basetype c on a.type=c.systypeid join ( SELECT a.type,a.lotterynum, [items]=( SELECT item +',' FROM #listresult AS b WHERE b.type = a.type and a.lotterynum=b.lotterynum FOR XML PATH('') ) FROM #listresult AS a GROUP BY type,lotterynum ) d on a.type=d.type and a.lotterynum=d.lotterynum union all select a.bettid,c.type ,a.lotterynum expect,'等待开奖' result,'等待开奖' items, '大小单双' as bttype,a.uitem userbttype,a.money, 0.00 lkmoney,0.00 winmoney,a.addtime FROM #listuser a join owzx_sys_basetype c on a.type=c.systypeid and a.result is null ) a declare @total int select @total=(select count(1) from #listpage) if(@pagesize=-1) begin select id,type+' -- '+expect+'期' type,winmoney,result,items,bttype,userbttype,money,lkmoney,addtime, @total TotalCount from #listpage end else begin select id,type+' -- '+expect+'期' type,winmoney,result,items,bttype,userbttype,money,lkmoney,addtime,@total TotalCount from #listpage where id>@pagesize*(@pageindex-1) and id <=@pagesize*@pageindex end end try begin catch select ERROR_MESSAGE() state end catch ", account, condition); return(RDBSHelper.ExecuteTable(CommandType.Text, commandText, parms)[0]); }
/// <summary> /// 添加/修改用户 /// </summary> /// <param name="info"></param> /// <returns></returns> public DataTable UpdateDummyUser(MD_Dummy info) { DbParameter[] parms = { GenerateInParam("@dummyid", SqlDbType.Int, 4, info.Dummyid), GenerateInParam("@nickname", SqlDbType.VarChar, 30, info.Nickname), GenerateInParam("@money", SqlDbType.VarChar, 500, info.Money), GenerateInParam("@bettime", SqlDbType.VarChar, 500, info.Bettime), GenerateInParam("@bettype", SqlDbType.VarChar, 500, info.BetType), GenerateInParam("@roomname", SqlDbType.VarChar, 50, info.RoomName), GenerateInParam("@avatar", SqlDbType.VarChar, 50, info.Avatar), GenerateInParam("@vip", SqlDbType.Int, 4, info.Vip), GenerateInParam("@start", SqlDbType.VarChar, 5, info.Start), GenerateInParam("@end", SqlDbType.VarChar, 5, info.End) }; string sql = string.Format(@" begin try begin tran t1 if exists(select 1 from owzx_dummyinfo where dummyid=@dummyid) begin if not exists(select 1 from owzx_dummyinfo where dummyid !=@dummyid and nickname=@nickname) begin update a set a.nickname=@nickname,a.money=@money,a.bettime=@bettime, a.bettype=@bettype,a.roomname=@roomname,avatar=@avatar,vip=@vip, a.start=@start,a.[end]=@end from owzx_dummyinfo a where dummyid=@dummyid select '成功' state,'' username commit tran t1 end else begin select '昵称已存在' state commit tran t1 end end else begin if not exists(select 1 from owzx_dummyinfo where nickname=@nickname) begin INSERT INTO [owzx_dummyinfo]([username],[nickname],money,bettime,roomname,bettype,avatar,vip,start,[end]) VALUES('usn'+rtrim(cast((select count(1)+1 from owzx_dummyinfo) as varchar(10))),@nickname,@money,@bettime,@roomname,@bettype,@avatar,@vip,@start,@end) select '成功' state,'usn'+rtrim(cast((select count(1) from owzx_dummyinfo) as varchar(10))) username commit tran t1 end else begin select '昵称已存在' state commit tran t1 end end end try begin catch rollback tran t1 select error_message() state end catch ", info.Dummyid, info.Nickname); return(RDBSHelper.ExecuteTable(sql, parms)[0]); }