-- G008-Staffwise Center List
select
	st.id,
	concat(st.first_name, ' ', st.last_name) as staffname,
	c.code,
	c.name as center_name,
	ci.meeting_place,
	ci.meetingType,
	ct.name,
	concat(c2.code, " ", c2.name) as centerChief,
	t1.groupCount,
	t2.memberCount
from center_info ci
left join staff st ON st.id = ci.meeting_by
inner join client c ON c.id = ci.id
left join center_type ct on ct.id=ci.center_type
left join client c2 on c2.id=ci.c_u_chief_id
left join
(
	SELECT COUNT(*) AS groupCount, c.code
	FROM member_group
	inner join client c on member_group.center_info_id=c.id
	GROUP BY center_info_id
) as t1 on c.code=t1.code
left join
(
	SELECT COUNT(*) AS memberCount, c.code
	FROM member_information AS mi
	INNER JOIN member_group AS mg ON mi.group_id = mg.id
	INNER JOIN client c on mg.center_info_id=c.id
	WHERE mi.member_status_id = 1
	GROUP BY mg.center_info_id
) as t2 on c.code=t2.code
where ci.organization=${3_Man_Branch} and ci.status=1
order by st.id;