/// <summary>
/// 月 周 日 统计
/// </summary>
/// <param name="btime"></param>
/// <param name="etime"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <param name="pageCount"></param>
/// <param name="sqlwhere"> MONTH(ReportTime)=MONTH(dateadd(month,-1,getdate()))</param>
/// <param name="groupby"> 上月:convert(varchar(7),ReportTime,120) </param>
/// <returns></returns>
public static List <UserReportDay> GetReporTimeList(string sqlwhere, string groupby)
{
string whereSql = " where 1=1 ";
whereSql += " and " + sqlwhere + " group by " + groupby;
string clumstr =
groupby + " as ReportTime,SUM(UserPoint) UserPoint,SUM(TotalPay) TotalPay,0-SUM(TotalDraw) TotalDraw,0-SUM(TotalPayMent) TotalPayMent,SUM(TotalWin) TotalWin,SUM(TotalReturn) TotalReturn, SUM(TotalPayMent)-SUM(UserPoint)-SUM(TotalWin)-SUM(TotalReturn) YL,Sum(TotalActive) TotalActive,Sum(TotalXJ) TotalXJ,Sum(TotalRiGZ) TotalRiGZ,Sum(TotalFenH) TotalFenH ";
DataTable dt = UserOrdersDAL.GetDataTable("SET DATEFIRST 1 select " + clumstr + " from UserReportDay " + whereSql);
List <UserReportDay> list = new List <UserReportDay>();
foreach (DataRow item in dt.Rows)
{
UserReportDay model = new UserReportDay();
model.FillData(item);
list.Add(model);
}
return(list);
}
public static List <OrderReport> GetLotterySumOrder(string cpcode, string userid, string type, int pageSize, int pageIndex, ref int totalCount, ref int pageCount, ref OrderReport listpage, int self = -1, string begintime = "", string endtime = "", string username = "")
{
string sqlwhere = " status in(0,1,2,4) ";
string uwhere = "";
if (!string.IsNullOrEmpty(type))
{
sqlwhere += " and Type like '%" + type + "%'";
}
if (!string.IsNullOrEmpty(cpcode))
{
sqlwhere += " and cpcode='" + cpcode + "'";
}
if (!string.IsNullOrEmpty(username))
{
if (self > 0)
{
if (self == 0)
{
uwhere += " and Userid in (select userid from Userrelation where ParentID='" + userid + "') and LoginName = '" + username + "'";
}
else if (self == 1)
{
uwhere += " and Userid in (select userid from Userrelation where parents like '%" + userid + "%') and Userid in (select userid from Userrelation where Parents like '%'+ (select top 1 UserID from M_Users where LoginName = '" + username + "')+'%')";
}
else
{
uwhere += " and( Userid in (select userid from Userrelation where parents like'%" + userid +
"%') and ( LoginName = '" + username +
"' or Userid in (select userid from Userrelation where parentid in (select top 1 UserID from M_Users where LoginName = '" +
username + "') )) )";
}
}
else
{
uwhere += " and( Userid in (select userid from Userrelation where parents like'%" + userid + "%') and ( LoginName = '" + username +
"' or Userid in (select userid from Userrelation where Parents like '%'+ (select top 1 UserID from M_Users where LoginName ='" + username + "')+'%') ) )";
}
}
else
{
if (!string.IsNullOrEmpty(userid))
{
if (self > 0)
{
if (self == 0)
{
uwhere += " and UserID='" + userid + "' ";
}
if (self == 1)
{
uwhere += " and UserID in(select UserID from UserRelation where ParentID='" + userid + "') ";
}
else
{
uwhere += " and UserID in(select UserID from UserRelation where Parents like '%" + userid + "%')";
}
}
else
{
uwhere += " and (UserID in(select UserID from UserRelation where Parents like '%" + userid + "%') or UserID='" + userid + "' )";
}
}
}
if (!string.IsNullOrEmpty(begintime))
{
sqlwhere += " and CreateTime>='" + begintime + "'";
}
if (!string.IsNullOrEmpty(endtime))
{
sqlwhere += " and CreateTime<='" + endtime + " 23:59:59:999'";
}
bool isdesc = true;
string sql = string.Format(@"
if OBJECT_ID('tempdb..#orderreport') is not null
drop table #orderreport
select row_number() over( order by Autoid asc) Autoid,UserID,LoginName,UserName,cast(0 as decimal(18,4)) as DWDFee,cast(0 as decimal(18,4)) as DXDSFee,cast(0 as decimal(18,4)) as LHHFee
into #orderreport
from M_Users
where Status <>9 and UserID !='993b30f7-a8c4-49f9-b2bc-0b629d34bb76' {1}
update a set DWDFee= b.Num from #orderreport a join (
select SUM(PayFee) as Num,UserID from LotteryOrder where {0} and Type like '%1DWEID_%'
group by UserID
) b on a.UserID=b.userID
update a set DXDSFee= b.Num from #orderreport a join (
select SUM(PayFee) as Num,UserID from LotteryOrder where {0} and Type like '%1QUWEIX_2DXDS_%'
group by UserID
) b on a.UserID=b.userID
update a set LHHFee= b.Num from #orderreport a join (
select SUM(PayFee) as Num,UserID from LotteryOrder where {0} and Type like '%1QUWEIX_2LHDOU_%'
group by UserID
) b on a.UserID=b.userID
declare @totalcount int=0,@pagecount int=0
select @totalcount=count(1) from #orderreport
set @pagecount=CEILING(@totalCount * 1.0/{3})
select @totalcount totalcount ,@pagecount pagecount
select * from #orderreport where Autoid>=({2}-1)*{3} and Autoid<={2}*{3}
select 0 autoid, '' UserName,'' LoginName,
sum(DXDSFee) DXDSFee,SUM(LHHFee) LHHFee,SUM(DWDFee) DWDFee,0 SafeLevel
from #orderreport
", sqlwhere, uwhere, pageIndex, pageSize);
DataSet ds = UserOrdersDAL.GetDataSet(sql);
DataTable dt1 = ds.Tables[0];
totalCount = int.Parse(dt1.Rows[0]["totalcount"].ToString());
pageCount = int.Parse(dt1.Rows[0]["pagecount"].ToString());
List <OrderReport> list = new List <OrderReport>();
foreach (DataRow item in ds.Tables[1].Rows)
{
OrderReport model = new OrderReport();
model.FillData(item);
list.Add(model);
}
listpage = new OrderReport();
foreach (DataRow item in ds.Tables[2].Rows)
{
listpage.FillData(item);
}
return(list);
}
public static UserReportDay GetUserTeamRPT(string userid, string btime, string endtime, string username = "")
{
string whereSql = " ";
if (!string.IsNullOrEmpty(username))
{
whereSql += " and Userid in (select userid from Userrelation where parents like '%" + userid +
"%') and Userid in (select userid from Userrelation where Parents like '%'+ (select top 1 UserID from M_Users where LoginName = '" +
username + "')+'%')";
}
else
{
if (!string.IsNullOrEmpty(userid))
{
whereSql += " and( UserID ='" + userid + "' or Userid in (select userid from Userrelation where Parents like'%" + userid + "%') )";
}
}
string sql = string.Format(@"
declare @today nvarchar(25)='{0}'
declare @endtoday nvarchar(25)='{1}'
if OBJECT_ID('tempdb..#userreport') is not null
drop table #userreport
select UserID,LoginName,cast(0 as decimal(18,4)) UserPoint,cast(0 as decimal(18,4)) TotalPay,
cast(0 as decimal(18,4)) TotalDraw,cast(0 as decimal(18,4)) TotalPayMent, cast(0 as decimal(18,4)) TotalWin,
cast(0 as decimal(18,4)) TotalReturn,cast(0 as decimal(18,4)) TotalZHReturn,cast(0 as decimal(18,4)) TotalActive,
cast(0 as decimal(18,4)) TotalXJ,cast(0 as decimal(18,4)) TotalFenH,cast(0 as decimal(18,4)) TotalRiGZ,cast(0 as decimal(18,4)) SumFee
into #userreport
from M_Users
where Status <>9 and UserID !='993b30f7-a8c4-49f9-b2bc-0b629d34bb76' {2}
update a set TotalPay= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType in(1,14) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
update a set TotalPayMent= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType in(4,5) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
update a set TotalWin= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType=8 and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
update a set TotalDraw= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType=2 and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--下级返点
update a set TotalReturn= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType=17 and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--用户返点
update a set UserPoint= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType=7 and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--追号撤单返款
update a set TotalZHReturn= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType in(6,9) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--活动奖金
update a set TotalActive= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType in(20,21,22) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--转账下级
update a set TotalXJ= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType in(23) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--日工资
update a set TotalRiGZ= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType in(18) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--分红
update a set TotalFenH= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord where PlayType in(19) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--团队总额
update a set SumFee= b.Num from #userreport a join (
select sum(isnull(c.AccountFee,'0'))+min(a.AccountFee) as Num,a.Userid from UserAccount a left join
Userrelation b on charindex(a.userid,Parents)>0
left join UserAccount c on c.UserID=b.UserID
group by a.userid
) b on a.UserID=b.userID
update #userreport set TotalPayMent=TotalPayMent-TotalZHReturn
--统计
if object_id('tempdb..#list') is not null
drop table #list
select row_number() over( order by b.Autoid desc) id,b.Type,b.AutoID,b.UserID,b.SafeLevel,
a.LoginName,AccountFee,UserPoint,TotalPay,TotalDraw,b.CreateTime,SumFee,
TotalPayMent,TotalWin,TotalReturn,YL ,TotalActive ,TotalXJ,TotalRiGZ,TotalFenH
into #list
from
(
select
b.LoginName,min(c.AccountFee) AccountFee,SUM(UserPoint) UserPoint,
SUM(TotalPay) TotalPay,0-SUM(TotalDraw) TotalDraw,SUM(TotalPayMent) TotalPayMent,
SUM(TotalWin) TotalWin,SUM(TotalReturn) TotalReturn, max(isnull(SumFee,0)) SumFee,
0-SUM(TotalPayMent)+SUM(UserPoint)+SUM(TotalWin)+SUM(TotalReturn)+Sum(TotalActive)+Sum(TotalRiGZ)+Sum(TotalFenH) YL ,
Sum(TotalActive) TotalActive ,Sum(TotalXJ) TotalXJ,Sum(TotalRiGZ) TotalRiGZ,Sum(TotalFenH) TotalFenH
from #userreport a
join M_Users b on a.userid=b.userid
join UserAccount c on b.userid=c.userid
group by b.LoginName
) a
join M_Users b on a.LoginName=b.LoginName
select '{3}' as UserID,
sum(AccountFee) AccountFee,SUM(UserPoint) UserPoint,max(SumFee) SumFee,0 SafeLevel,
SUM(TotalPay) TotalPay,SUM(TotalDraw) TotalDraw,SUM(TotalPayMent) TotalPayMent,SUM(TotalWin) TotalWin,
SUM(TotalReturn) TotalReturn, Sum(YL) YL ,Sum(TotalActive) TotalActive ,Sum(TotalXJ) TotalXJ,
Sum(TotalRiGZ) TotalRiGZ,Sum(TotalFenH) TotalFenH
from #list
", btime + " 00:00:00", endtime + " 23:59:59", whereSql, userid);
DataSet ds = UserOrdersDAL.GetDataSet(sql);
UserReportDay listpage = new UserReportDay();
foreach (DataRow item in ds.Tables[0].Rows)
{
listpage.FillData(item);
}
return(listpage);
}
public static List <UserReportDay> GetUserRPTList(string userid, int pageindex, int pagesize, int isxj, ref int totalcount, ref int pagecount, string btime, string endtime,
ref UserReportDay listpage, string username = "")
{
string whereSql = " ";
if (!string.IsNullOrEmpty(username))
{
if (isxj == 0)
{
whereSql += " and Userid in (select userid from Userrelation where ParentID='" + userid + "') and LoginName = '" + username + "'";
}
else if (isxj == 1)
{
whereSql += " and( Userid in (select userid from Userrelation where parents like'%" + userid + "%') and ( LoginName = '" + username + "' or Userid in (select userid from Userrelation where parentid in (select top 1 UserID from M_Users where LoginName = '" + username + "') )) )";
}
else
{
whereSql += " and Userid in (select userid from Userrelation where parents like '%" + userid +
"%') and Userid in (select userid from Userrelation where Parents like '%'+ (select top 1 UserID from M_Users where LoginName = '" +
username + "')+'%')";
}
}
else
{
if (!string.IsNullOrEmpty(userid))
{
if (isxj == 0)
{
whereSql += " and UserID = '" + userid + "'";
}
else if (isxj == 1)
{
whereSql += " and( UserID = '" + userid + "' or Userid in (select userid from Userrelation where ParentID='" + userid + "') ) ";
}
else
{
whereSql += " and Userid in (select userid from Userrelation where Parents like '%" + userid + "%') ";
}
}
}
string sql = string.Format(@"
declare @today nvarchar(25)='{0}'
declare @endtoday nvarchar(25)='{1}'
if OBJECT_ID('tempdb..#userreport') is not null
drop table #userreport
select UserID,LoginName,cast(0 as decimal(18,4)) UserPoint,cast(0 as decimal(18,4)) TotalPay,
cast(0 as decimal(18,4)) TotalDraw,cast(0 as decimal(18,4)) TotalPayMent, cast(0 as decimal(18,4)) TotalWin,
cast(0 as decimal(18,4)) TotalReturn,cast(0 as decimal(18,4)) TotalZHReturn,cast(0 as decimal(18,4)) TotalActive,
cast(0 as decimal(18,4)) TotalXJ,cast(0 as decimal(18,4)) TotalFenH,cast(0 as decimal(18,4)) TotalRiGZ,cast(0 as decimal(18,4)) SumFee
into #userreport
from M_Users
where Status <>9 and UserID !='993b30f7-a8c4-49f9-b2bc-0b629d34bb76' {2}
update a set TotalPay= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType in(1,14) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
update a set TotalPayMent= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType in(4,5) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
update a set TotalWin= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType=8 and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
update a set TotalDraw= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType=2 and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--下级返点
update a set TotalReturn= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType=17 and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--用户返点
update a set UserPoint= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType=7 and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--追号撤单返款
update a set TotalZHReturn= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType in(6,9) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--活动奖金
update a set TotalActive= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType in(20,21,22) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--转账下级
update a set TotalXJ= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType in(23) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--日工资
update a set TotalRiGZ= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType in(18) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--分红
update a set TotalFenH= b.Num from #userreport a join (
select SUM(AccountChange) as Num,UseriD from AccountOperateRecord (nolock) where PlayType in(19) and
CreateTime >=@today and CreateTime<=@endtoday
group by UseriD
) b on a.UserID=b.userID
--团队总额
update a set SumFee= b.Num from #userreport a join (
select sum(isnull(c.AccountFee,'0'))+min(a.AccountFee) as Num,a.Userid from UserAccount (nolock) a left join
Userrelation b on charindex(a.userid,Parents)>0
left join UserAccount c on c.UserID=b.UserID
group by a.userid
) b on a.UserID=b.userID
update #userreport set TotalPayMent=TotalPayMent-TotalZHReturn
--统计
if object_id('tempdb..#list') is not null
drop table #list
select row_number() over( order by b.Autoid asc) id,b.Type,b.AutoID,b.UserID,b.SafeLevel,
a.LoginName,AccountFee,UserPoint,TotalPay,TotalDraw,b.CreateTime,SumFee,
TotalPayMent,TotalWin,TotalReturn,YL ,TotalActive ,TotalXJ,TotalRiGZ,TotalFenH
into #list
from
(
select
b.LoginName,min(c.AccountFee) AccountFee,SUM(UserPoint) UserPoint,
SUM(TotalPay) TotalPay,0-SUM(TotalDraw) TotalDraw,SUM(TotalPayMent) TotalPayMent,
SUM(TotalWin) TotalWin,SUM(TotalReturn) TotalReturn, max(isnull(SumFee,0)) SumFee,
0-SUM(TotalPayMent)+SUM(UserPoint)+SUM(TotalWin)+SUM(TotalReturn)+Sum(TotalActive)+Sum(TotalRiGZ)+Sum(TotalFenH) YL ,
Sum(TotalActive) TotalActive ,Sum(TotalXJ) TotalXJ,Sum(TotalRiGZ) TotalRiGZ,Sum(TotalFenH) TotalFenH
from #userreport a
join M_Users b on a.userid=b.userid
join UserAccount c on b.userid=c.userid
group by b.LoginName
) a
join M_Users b on a.LoginName=b.LoginName
declare @totalcount int=0,@pagecount int=0
select @totalcount=count(1) from #list
set @pagecount=CEILING(@totalCount * 1.0/{4})
select @totalcount totalcount, @pagecount pagecount
select *
from #list where id>({3}-1)*{4} and id<={3}*{4}
select
sum(AccountFee) AccountFee,SUM(UserPoint) UserPoint,max(SumFee) SumFee,0 SafeLevel,
SUM(TotalPay) TotalPay,SUM(TotalDraw) TotalDraw,SUM(TotalPayMent) TotalPayMent,SUM(TotalWin) TotalWin,
SUM(TotalReturn) TotalReturn, Sum(YL) YL ,Sum(TotalActive) TotalActive ,Sum(TotalXJ) TotalXJ,
Sum(TotalRiGZ) TotalRiGZ,Sum(TotalFenH) TotalFenH
from #list
", btime + " 00:00:00", endtime + " 23:59:59", whereSql, pageindex, pagesize);
DataSet ds = UserOrdersDAL.GetDataSet(sql);
DataTable dt1 = ds.Tables[0];
totalcount = int.Parse(dt1.Rows[0]["totalcount"].ToString());
pagecount = int.Parse(dt1.Rows[0]["pagecount"].ToString());
List <UserReportDay> list = new List <UserReportDay>();
foreach (DataRow item in ds.Tables[1].Rows)
{
UserReportDay model = new UserReportDay();
model.FillData(item);
list.Add(model);
}
listpage = new UserReportDay();
foreach (DataRow item in ds.Tables[2].Rows)
{
listpage.FillData(item);
}
return(list);
}
