-- declare @dt_start datetime;
-- declare @dt_end datetime;

set @dt_start = '2023-01-01 00:00:00';
set @dt_end = '2023-08-03 23:59:59';

SELECT 
         mb.idx AS member_idx,
         IFNULL(mb_ch_his.ch_cnt,0) AS charge_total_count,
         IFNULL(mb_ch_his.ch_sum,0) AS charge_total_money,
         IFNULL(mb_ex_his.ex_cnt,0) AS exchange_total_count,
         IFNULL(mb_ex_his.ex_sum, 0) AS exchange_total_money, 
         IFNULL(loock.loock_cnt,0) AS loock_up_total_count,
	     IFNULL(mini_bet.mini_count,0) + IFNULL(mb_cnt_bet.mb_cnt_count,0) + IFNULL(mb_casino.cs_cnt_count,0) + IFNULL(mb_slot.sl_cnt_count,0) + IFNULL(mb_espt.esp_cnt_count,0) + IFNULL(mb_hash.hs_cnt_count,0) + IFNULL(mb_holdem.holdem_cnt_count,0) AS bet_total_count,
         IFNULL(bet.pre_bet_sum_s, 0) + IFNULL(bet.pre_bet_sum_d, 0) +  IFNULL(bet.real_bet_sum_s, 0) + IFNULL(bet.real_bet_sum_d, 0) 
         AS bet_total_money,
         IFNULL(bet.pre_bet_sum_s, 0) AS pre_s_total_money,
         IFNULL(bet.pre_bet_sum_d, 0) AS pre_m_total_money,
         IFNULL(bet.real_bet_sum_s, 0) AS real_s_total_money,
         IFNULL(bet.real_bet_sum_d, 0) AS real_m_total_money,
         IFNULL(mg_bet.mini_bet_sum_d, 0) AS mini_total_money,
		 IFNULL(bet.pre_take_sum_s, 0) +  IFNULL(bet.pre_take_sum_d, 0)  +  IFNULL(bet.real_take_sum_s, 0) + IFNULL(bet.real_take_sum_d, 0)
         AS take_total_money,
		 IFNULL(mg_bet.mini_take_sum_d, 0) AS take_mini_total_money,
         IFNULL(bet.real_take_sum_d, 0) AS take_real_m_total_money,
	     IFNULL(bet.real_take_sum_s, 0) AS take_real_s_total_money,
         IFNULL(bet.pre_take_sum_d, 0) AS take_pre_m_total_money,
         IFNULL(bet.pre_take_sum_s, 0) AS take_pre_s_total_money,
         
         IFNULL(mb_casino.casino_total_bet_money, 0) AS casino_total_bet_money,
         IFNULL(mb_casino.casino_total_win_money, 0) AS casino_total_win_money,
         
         IFNULL(mb_slot.slot_total_bet_money, 0) AS slot_total_bet_money,
         IFNULL(mb_slot.slot_total_win_money, 0) AS slot_total_win_money,

         IFNULL(mb_espt.espt_total_bet_money, 0) AS espt_total_bet_money,
         IFNULL(mb_espt.espt_total_win_money, 0) AS espt_total_win_money,
         IFNULL(mb_hash.hash_total_bet_money, 0) AS hash_total_bet_money,
         IFNULL(mb_hash.hash_total_win_money, 0) AS hash_total_win_money,
         
         IFNULL(mb_holdem.holdem_total_bet_money, 0) AS holdem_total_bet_money,
         IFNULL(mb_holdem.holdem_total_win_money, 0) AS holdem_total_win_money
		FROM (SELECT m.idx, m.id, m.nick_name from member AS m ) AS mb
    LEFT JOIN 
        (SELECT IFNULL(sum(ch_his.money), 0) AS ch_sum,
            count(idx) AS ch_cnt,
            ch_his.member_idx AS member_idx
            FROM member_money_charge_history AS ch_his 
            WHERE ch_his.status = 3
             AND ch_his.update_dt >= @dt_start
             AND ch_his.update_dt <= @dt_end
            GROUP BY ch_his.member_idx) AS mb_ch_his 
    ON 
    mb.idx =  mb_ch_his.member_idx
    LEFT JOIN 
        (SELECT IFNULL(sum(ex_his.money), 0) AS ex_sum,
            count(idx) AS ex_cnt,
            ex_his.member_idx AS member_idx
            FROM member_money_exchange_history AS ex_his 
            WHERE ex_his.status = 3
            AND ex_his.update_dt >= @dt_start
            AND ex_his.update_dt <= @dt_end
            GROUP BY ex_his.member_idx) AS mb_ex_his
    ON mb.idx =  mb_ex_his.member_idx
    LEFT JOIN 
	    (SELECT 
        mb_bet.member_idx AS member_idx,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0) AS pre_bet_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS pre_take_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0)
            - 	IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS pre_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0) AS pre_bet_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS pre_take_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0)
            - 	IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS pre_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0) AS real_bet_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS real_take_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0)
            - 	IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS real_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0) AS real_bet_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS real_take_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0)
            - 	IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS real_sum_d
	FROM member_bet AS mb_bet
        WHERE mb_bet.bet_status = 3
        AND mb_bet.calculate_dt >= @dt_start
        AND mb_bet.calculate_dt <= @dt_end
        AND total_bet_money != take_money
	GROUP BY mb_bet.member_idx) AS bet
    ON 
    mb.idx =  bet.member_idx
 LEFT JOIN 
	(SELECT mg.member_idx AS member_idx,
	    IFNULL(sum(mg.total_bet_money), 0) AS mini_bet_sum_d,
        IFNULL(sum(mg.take_money), 0) AS mini_take_sum_d,
        IFNULL(sum(mg.total_bet_money), 0) - IFNULL(sum(mg.take_money), 0) AS mini_sum_d
	FROM mini_game_member_bet AS mg
	WHERE mg.bet_status = 3  
        AND mg.calculate_dt >= @dt_start
        AND mg.calculate_dt <= @dt_end
        AND mg.total_bet_money != take_money
    GROUP BY mg.member_idx) AS mg_bet
    ON 
    mb.idx =  mg_bet.member_idx  
 LEFT JOIN
  (SELECT count(idx) as loock_cnt
  ,member_idx
  FROM t_log_cash
  WHERE   ac_code IN(103) 
         AND t_log_cash.reg_time >= @dt_start
         AND t_log_cash.reg_time <= @dt_end
        ) as loock
   ON mb.idx = loock.member_idx      
 LEFT JOIN 
	(SELECT count(idx) as mini_count
    ,member_idx
	FROM mini_game_member_bet AS mg
	WHERE mg.bet_status = 3  
         AND mg.calculate_dt >= @dt_start
         AND mg.calculate_dt <= @dt_end
    
    GROUP BY mg.member_idx) AS mini_bet
    ON 
    mb.idx =  mini_bet.member_idx    
 LEFT JOIN 
	(SELECT count(idx) as mb_cnt_count
    ,member_idx
	FROM member_bet AS mg
	WHERE mg.bet_status = 3  
         AND mg.calculate_dt >= @dt_start
         AND mg.calculate_dt <= @dt_end
    
    GROUP BY mg.member_idx) AS mb_cnt_bet
    ON 
    mb.idx =  mb_cnt_bet.member_idx 
 LEFT JOIN  
    (SELECT count(CSN_BET_IDX) as cs_cnt_count,
		MBR_IDX,
        IFNULL(SUM(`BET_MNY`), 0) AS `casino_total_bet_money`,
        IFNULL(SUM(CASE
                    WHEN `TYPE` = 'W' THEN `BET_MNY` + `RSLT_MNY`
                END),
                0) AS `casino_total_win_money`
    FROM `KP_CSN_BET_HIST` 
    WHERE 
         `CALC_DTM` >= @dt_start
         AND `CALC_DTM` <= @dt_end
      GROUP BY MBR_IDX) AS mb_casino
    ON 
    mb.idx =  mb_casino.MBR_IDX 
    LEFT JOIN  
    (SELECT count(SLOT_BET_IDX) as sl_cnt_count,
		MBR_IDX,
        IFNULL(SUM(`BET_MNY`), 0) AS `slot_total_bet_money`,
        IFNULL(SUM(CASE
                    WHEN `TYPE` = 'W' THEN `BET_MNY` + `RSLT_MNY`
                END),
                0) AS `slot_total_win_money`
    FROM `KP_SLOT_BET_HIST` 
    WHERE 
         `CALC_DTM` >= @dt_start
         AND `CALC_DTM` <= @dt_end
      GROUP BY MBR_IDX) AS mb_slot
    ON 
    mb.idx =  mb_slot.MBR_IDX
    LEFT JOIN  
    (SELECT count(SLOT_BET_IDX) as esp_cnt_count,
		MBR_IDX,
        IFNULL(SUM(`BET_MNY`), 0) AS `espt_total_bet_money`,
        IFNULL(SUM(CASE
                    WHEN `TYPE` = 'W' THEN `BET_MNY` + `RSLT_MNY`
                END),
                0) AS `espt_total_win_money`
    FROM `KP_ESPT_BET_HIST`
    WHERE
         `CALC_DTM` >= @dt_start
         AND `CALC_DTM` <= @dt_end
      GROUP BY MBR_IDX) AS mb_espt
    ON
    mb.idx =  mb_espt.MBR_IDX
    LEFT JOIN  
    (SELECT count(HASH_BET_IDX) as hs_cnt_count,
		MBR_IDX,
        IFNULL(SUM(`BET_MNY`), 0) AS `hash_total_bet_money`,
        IFNULL(SUM(CASE
                    WHEN `TYPE` = 'W' THEN `BET_MNY` + `RSLT_MNY`
                END),
                0) AS `hash_total_win_money`
    FROM `OD_HASH_BET_HIST`
    WHERE
         `CALC_DTM` >= @dt_start
         AND `CALC_DTM` <= @dt_end
      GROUP BY MBR_IDX) AS mb_hash
    ON
    mb.idx =  mb_hash.MBR_IDX
    LEFT JOIN  
    (SELECT count(HOLDEM_BET_IDX) as holdem_cnt_count,
		MBR_IDX,
        IFNULL(SUM(`BET_MONEY`), 0) AS `holdem_total_bet_money`,
        IFNULL(SUM(`WIN_MONEY`), 0) AS `holdem_total_win_money`
    FROM `HOLDEM_BET_HIST` 
    WHERE 
         `REG_DTM` >= @dt_start
         AND `REG_DTM` <= @dt_end
      GROUP BY MBR_IDX) AS mb_holdem
    ON 
    mb.idx =  mb_holdem.MBR_IDX
    
    -- WHERE mb.idx = 1732
     
   GROUP BY mb.idx;