-/*SELECT dis_id as '가입총판', id as '아이디', nick_name as '닉네임', account_name as '이름', account_number as '계좌번호', `call` as '연락처',
IFNULL((IFNULL(t_m_ch.charge_total_money,0) + IFNULL(ch.money,0)),0) AS '총입금금액',
IFNULL((IFNULL(t_m_ch.exchange_total_money,0) + IFNULL(ex.money,0)),0) AS '총출금금액',
(IFNULL((IFNULL(t_m_ch.charge_total_money,0) + IFNULL(ch.money,0)), 0)
            - 
             (IFNULL((IFNULL(t_m_ch.exchange_total_money,0) + IFNULL(ex.money,0)),0) + a.money)
            ) AS '차액'
FROM mydb.member 
left join total_member_cash as t_m_ch ON a.idx = t_m_ch.member_idx
where idx = 5245;
*/

SELECT a.id as '아이디', a.nick_name as '닉네임', a.account_name as '이름', a.call as '연락처', last_login as '최근접속',
 IFNULL((IFNULL(t_m_ch.charge_total_money,0) + IFNULL(ch.money,0)),0) AS '총입금금액' , IFNULL((IFNULL(t_m_ch.exchange_total_money,0) + IFNULL(ex.money,0)),0) AS '총출금금액' , (
             IFNULL((IFNULL(t_m_ch.charge_total_money,0) + IFNULL(ch.money,0)), 0)
            - 
             (IFNULL((IFNULL(t_m_ch.exchange_total_money,0) + IFNULL(ex.money,0)),0) + a.money)
            ) AS '차액',
 case when (a.status = 1) then '정상'
 when (a.status = 2) then '정지'
 when (a.status = 3) then '탈퇴'
 when (a.status = 11) then '대기' end as '상태'
FROM member a  left join total_member_cash as t_m_ch ON a.idx = t_m_ch.member_idx  
left join ( SELECT IFNULL(SUM(c.money),0) as money,c.member_idx FROM member_money_charge_history c 
left join member t1 on c.member_idx=t1.idx 
WHERE  c.status=3 AND c.update_dt >= DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and c.update_dt <= NOW()
 group by t1.idx
 ) as ch ON a.idx = ch.member_idx   left join ( SELECT IFNULL(SUM(e.money),0) as money,e.member_idx FROM member_money_exchange_history e 
 left join member t1 on e.member_idx=t1.idx 
 WHERE e.status=3 and e.update_dt >= DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and e.update_dt <= NOW()
 group by t1.idx
 ) as ex ON a.idx = ex.member_idx
 WHERE a.status = 1 and a.u_business = 1 and a.level <> 9;