# Lehe

**Repository Path**: sd3560531/Lehe

## Basic Information

- **Project Name**: Lehe
- **Description**: No description available
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2017-02-21
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

url: jdbc:sqlserver://120.76.201.239:1433;DatabaseName=QPGameUserDB
-- 查询一个人的下级代理所冲金额（包括子孙代里冲值的金额）
select B.f2,sum(B.a) from (
select f3,f2,f1,userId,a from (
select isnull(a3.groupid,0) as f3,isnull(a2.groupId,0) as f2,a1.GroupID as f1,a1.userId,10 as a  from QPGameUserDB.dbo.AccountsInfo a1
left join QPGameUserDB.dbo.AccountsInfo a2 on a2.userid = a1.groupId
left join QPGameUserDB.dbo.AccountsInfo a3 on a3.userid = a2.groupId
where a1.groupId = 100500 or a2.groupId = 100500 or a3.GroupID = 100500 or a1.userid = 100500
) A where f3 <> 0
union all
select f2,f1,userId,0,a from (
select isnull(a3.groupid,0) as f3,isnull(a2.groupId,0) as f2,a1.GroupID as f1,a1.userId,10 as a  from QPGameUserDB.dbo.AccountsInfo a1
left join QPGameUserDB.dbo.AccountsInfo a2 on a2.userid = a1.groupId
left join QPGameUserDB.dbo.AccountsInfo a3 on a3.userid = a2.groupId
where a1.groupId = 100500 or a2.groupId = 100500 or a3.GroupID = 100500 or a1.userid = 100500
) A where f3 = 0 and f2 <> 0
union all
select f1,userId,0,0,a from (
select isnull(a3.groupid,0) as f3,isnull(a2.groupId,0) as f2,a1.GroupID as f1,a1.userId,10 as a  from QPGameUserDB.dbo.AccountsInfo a1
left join QPGameUserDB.dbo.AccountsInfo a2 on a2.userid = a1.groupId
left join QPGameUserDB.dbo.AccountsInfo a3 on a3.userid = a2.groupId
where a1.groupId = 100500 or a2.groupId = 100500 or a3.GroupID = 100500 or a1.userid = 100500
) A where f2 = 0 and f1 <> 0
) B group by B.f2

-- 查询一个人的子孙代理
select a1.userId,a1.AgentID,a1.GroupID from AccountsInfo a1
left join AccountsInfo a2 on a2.userid = a1.groupId
left join AccountsInfo a3 on a3.userid = a2.groupId
where a1.groupId = 100509 or a2.groupId = 100509 or a3.GroupID = 100509;

-- 查询自己及子孙代理
WITH t1( AgentID, FAgentID, UserID, LoginRight) AS
(	SELECT AgentID, FAgentID, UserID, LoginRight
	FROM AgentInfo t
	WHERE AgentID = 100500
	UNION ALL
	SELECT t2.AgentID, t2.FAgentID, t2.UserID, t2.LoginRight
	FROM AgentInfo t2 JOIN t1 ON t2.FAgentID= t1.AgentID
)
SELECT * FROM t1 -- where t1.AgentID != 100500

-- 充值汇总
select t.userid, sum(t.total_fee) as total_fee from (
select t.userid, t.total_fee as total_fee from QPGameRefillDB.dbo.AlipayTradeInfo t
where t.processflag = 1
union all
select t.userid, t.total_fee as total_fee from QPGameRefillDB.dbo.WXTradeInfo t
where t.processflag = 1 ) t group by t.userid

--后台权限初始化
insert into AdminMenu values ('用户管理',null,0,1,'01');
insert into AdminMenu values ('财务管理',null,0,2,'02');
insert into AdminMenu values ('平台管理',null,0,3,'03');
insert into AdminMenu values ('玩家管理',null,1,1,'0101');
insert into AdminMenu values ('代理管理',null,1,2,'0102');
insert into AdminMenu values ('我的玩家','admin/usermanager/myPlayer',4,1,'010101');
insert into AdminMenu values ('玩家充值','admin/usermanager/myPlayerPay',4,2,'010102');

insert into AdminMenu values ('系统用户管理','admin/systemManager/userManager',3,1,'0301');
insert into AdminMenu values ('权限管理','admin/systemManager/roleManager',3,2,'0302');
insert into AdminMenu values ('菜单管理','admin/systemManager/menuManager',3,3,'0303');

insert into AdminRole values ('管理员','系统管理员','6,7,8,9,10,11,12,13,14,15,16');

insert into AdminUser values ('admin','admin','超级管理员', '18888888888', 1, 0);

-- 本人充值
select isnull(sum(total_fee),0) from QPGameRefillDB.dbo.LEHE_CHARGE_TOTAL where userId in (100540 )
-- 本人充值笔数
select isnull(count(total_fee),0) from QPGameRefillDB.dbo.LEHE_CHARGE_TOTAL where userId in (100630 ,100540)
-- 所属玩家充值
select isnull(sum(total_fee),0) from QPGameRefillDB.dbo.LEHE_CHARGE_TOTAL where userId in (
	select userid from QPGameUserDB.dbo.AccountsInfo where groupid in (
		select agentId from QPGameUserDB.dbo.AccountsInfo where userId in (100540 ) and agentId != 0
	) and agentId = 0
)
-- 下属代理和下属代理玩家，以及下属代理的子孙代理及子孙代理的所属玩家(不包括本人，以及本人的下属玩家)
select isnull(sum(total_fee),0) from QPGameRefillDB.dbo.LEHE_CHARGE_TOTAL where userId in (
	-- 1级下属代理，不包括下属玩家，因为下属代理的玩家属于“所属玩家”充值查询
	select userid from QPGameUserDB.dbo.AccountsInfo where groupid in (
		select agentId from QPGameUserDB.dbo.AccountsInfo where userId in (100540 ) and agentId != 0
	) and agentId != 0
	
	union all 
	-- 2级下属代理及其玩家
	select userid from QPGameUserDB.dbo.AccountsInfo where groupid in (
		select agentId from QPGameUserDB.dbo.AccountsInfo where groupid in (
			select agentId from QPGameUserDB.dbo.AccountsInfo where userId in (100540) and agentId != 0
		) and agentId != 0
	)
	
	union all
	-- 3级下属代理及其玩家
	select userid from QPGameUserDB.dbo.AccountsInfo where groupid in (
		select agentId from QPGameUserDB.dbo.AccountsInfo where groupid in (
			select agentId from QPGameUserDB.dbo.AccountsInfo where groupid in (
				select agentId from QPGameUserDB.dbo.AccountsInfo where userId in (100540) and agentId != 0
			) and agentId != 0
		) and agentId != 0
	)
)

-- 点击下属玩家就是我的玩家里面的查询
-- 点击下属代理就是我的代理里面的查询
-- 点击充值明细，就用现有用户查询里面的明细