DROP procedure IF EXISTS `insert_total_member_cash`;

DELIMITER $$
CREATE PROCEDURE `insert_total_member_cash`(IN dt_start DATETIME, IN dt_end DATETIME)
BEGIN

		 DECLARE v_member_idx,v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money, 
         v_loock_up_total_count,v_bet_total_count,v_bet_total_money,v_pre_s_total_money,v_pre_m_total_money,
         v_real_s_total_money,v_real_m_total_money,v_mini_total_money,v_take_total_money,v_take_mini_total_money,
         v_take_real_m_total_money,v_take_real_s_total_money,v_take_pre_m_total_money,v_take_pre_s_total_money,
         v_casino_total_bet_money,v_casino_total_win_money,v_slot_total_bet_money,v_slot_total_win_money,
		 v_espt_total_bet_money, v_espt_total_win_money, v_hash_total_bet_money, v_hash_total_win_money  int;
                          
         DECLARE done INTEGER DEFAULT 0;
         
         DEClARE openCursor CURSOR FOR 
         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) 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
		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(idx) as cs_cnt_count,
		member_idx as MBR_IDX,
        IFNULL(SUM(`bet_money`), 0) AS `casino_total_bet_money`,
        IFNULL(SUM(take_money),
                0) AS `casino_total_win_money`
    FROM `tb_betting_info` 
    WHERE 
         `calc_dt` >= dt_start
         AND `calc_dt` <= dt_end
         AND product_type_id = 1
      GROUP BY member_idx) AS mb_casino
    ON 
    mb.idx =  mb_casino.MBR_IDX 
    LEFT JOIN  
    (SELECT count(idx) as sl_cnt_count,
		member_idx as MBR_IDX,
        IFNULL(SUM(`bet_money`), 0) AS `slot_total_bet_money`,
        IFNULL(SUM(take_money),
                0) AS `slot_total_win_money`
    FROM `tb_betting_info` 
    WHERE 
         `calc_dt` >= dt_start
         AND `calc_dt` <= dt_end
         AND product_type_id = 2
      GROUP BY member_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
      ) 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
      ) AS mb_hash
    ON
    mb.idx =  mb_hash.MBR_IDX
     
   GROUP BY mb.idx; 
         
         
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

         
         OPEN openCursor;

                  
                  read_loop: LOOP
                           
                           FETCH openCursor INTO v_member_idx,v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money, 
												 v_loock_up_total_count,v_bet_total_count,v_bet_total_money,v_pre_s_total_money,v_pre_m_total_money,
												 v_real_s_total_money,v_real_m_total_money,v_mini_total_money,v_take_total_money,v_take_mini_total_money,
												 v_take_real_m_total_money,v_take_real_s_total_money,v_take_pre_m_total_money,v_take_pre_s_total_money,v_casino_total_bet_money,
                                                 v_casino_total_win_money,v_slot_total_bet_money,v_slot_total_win_money,v_espt_total_bet_money,v_espt_total_win_money,
                                                 v_hash_total_bet_money,v_hash_total_win_money;
                           
                           IF done THEN
                                   LEAVE read_loop;
                           END IF;
                       
                           
                           insert into `total_member_cash` ( member_idx,charge_total_count,charge_total_money,exchange_total_count,exchange_total_money, 
												 loock_up_total_count,bet_total_count,bet_total_money,pre_s_total_money,pre_m_total_money,
												 real_s_total_money,real_m_total_money,mini_total_money,take_total_money,take_mini_total_money,
												 take_real_m_total_money,take_real_s_total_money,take_pre_m_total_money,take_pre_s_total_money,casino_total_money,
                                                 take_casino_total_money,slot_total_money,take_slot_total_money,espt_total_money,take_espt_total_money,
                                                 hash_total_money,take_hash_total_money)
						   values(v_member_idx,v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money, 
												 v_loock_up_total_count,v_bet_total_count,v_bet_total_money,v_pre_s_total_money,v_pre_m_total_money,
												 v_real_s_total_money,v_real_m_total_money,v_mini_total_money,v_take_total_money,v_take_mini_total_money,
												 v_take_real_m_total_money,v_take_real_s_total_money,v_take_pre_m_total_money,v_take_pre_s_total_money,v_casino_total_bet_money,
                                                 v_casino_total_win_money,v_slot_total_bet_money,v_slot_total_win_money,v_espt_total_bet_money,v_espt_total_win_money,
                                                 v_hash_total_bet_money,v_hash_total_win_money)
                                                 ON DUPLICATE KEY UPDATE
                                                 charge_total_count  = charge_total_count + VALUES(charge_total_count),
                                                 charge_total_money = charge_total_money + VALUES(charge_total_money),
                                                 exchange_total_count = exchange_total_count + VALUES(exchange_total_count),
                                                 exchange_total_money  = exchange_total_money + VALUES(exchange_total_money), 
												 loock_up_total_count  = loock_up_total_count + VALUES(loock_up_total_count),
                                                 bet_total_count  = bet_total_count + VALUES(bet_total_count),
                                                 bet_total_money  = bet_total_money + VALUES(bet_total_money),
                                                 pre_s_total_money  = pre_s_total_money + VALUES(pre_s_total_money),
                                                 pre_m_total_money  = pre_m_total_money + VALUES(pre_m_total_money),
												 real_s_total_money  = real_s_total_money + VALUES(real_s_total_money),
                                                 real_m_total_money  = real_m_total_money + VALUES(real_m_total_money),
                                                 mini_total_money  = mini_total_money + VALUES(mini_total_money),
                                                 take_total_money  = take_total_money + VALUES(take_total_money),
                                                 take_mini_total_money  = take_mini_total_money + VALUES(take_mini_total_money),
												 take_real_m_total_money  = take_real_m_total_money + VALUES(take_real_m_total_money),
                                                 take_real_s_total_money  = take_real_s_total_money + VALUES(take_real_s_total_money),
                                                 take_pre_m_total_money  = take_pre_m_total_money + VALUES(take_pre_m_total_money),
                                                 take_pre_s_total_money   = take_pre_s_total_money + VALUES(take_pre_s_total_money),

                                                 casino_total_money  = casino_total_money + VALUES(casino_total_money),
                                                 take_casino_total_money  = take_casino_total_money + VALUES(take_casino_total_money),
                                                 slot_total_money  = slot_total_money + VALUES(slot_total_money),
                                                 take_slot_total_money   = take_slot_total_money + VALUES(take_slot_total_money),
                                                 espt_total_money  = espt_total_money + VALUES(espt_total_money),
                                                 take_espt_total_money  = take_espt_total_money + VALUES(take_espt_total_money),
                                                 hash_total_money  = hash_total_money + VALUES(hash_total_money),
                                                 take_hash_total_money   = take_hash_total_money + VALUES(take_hash_total_money)
                                                 ;

             

                  END LOOP read_loop;

         

         CLOSE openCursor;
END$$

DELIMITER ;