/// <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]);
}