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