					INSERT INTO log.log_info (u_key,pkey,member_idx,id,nick_name,`level`,partner_type,pt_member_idx,pt_id
                       ,pt_nick_name,pt_partner_type,main_type,sub_type,domain,ip,country,detail_info,`status`,value_type) 
                       VALUES (l_u_key, l_pkey, l_idx,v_id, v_nick_name, v_level,l_partner_type
                       ,l_pt_idx ,l_pt_id,l_pt_nick_name,l_pt_partner_type
                       ,41,2003,'' ,'' ,'',JSON_OBJECT('desc','gamble에서 획득한 머니', 'money',v_money,'partner_id',in_head_office_id,'after_money',v_money
                       ,'partner_idx',h_idx,'before_money',0,'partner_nick_name',in_head_office_id,'parent_partner_type',201,'partner_type_classification',201)
                       ,1,2);    
                       
                   IF EXISTS(SELECT * FROM mydb_noble.member where id = v_id) THEN
                    SELECT `point`,money INTO l_pt_noble_point,l_pt_noble_money FROM mydb_noble.member where id = v_id;
                    set l_u_key = UUID();
					INSERT INTO log.log_info (u_key,pkey,member_idx,id,nick_name,`level`,partner_type,pt_member_idx,pt_id
                       ,pt_nick_name,pt_partner_type,main_type,sub_type,domain,ip,country,detail_info,`status`,value_type) 
                       VALUES (l_u_key, l_pkey, l_idx,v_id, v_nick_name, v_level,l_partner_type
                       ,l_pt_idx ,l_pt_id,l_pt_nick_name,l_pt_partner_type
                       ,41,3001,'' ,'' ,'',JSON_OBJECT('desc','noble에서 획득한 포인트', 'point',l_pt_noble_point,'partner_id',in_head_office_id,'after_point',v_point + l_pt_noble_point
                       ,'partner_idx',h_idx,'before_point',v_point,'partner_nick_name',in_head_office_id,'parent_partner_type',201,'partner_type_classification',201)
                       ,1,2);
                       
					INSERT INTO log.log_info (u_key,pkey,member_idx,id,nick_name,`level`,partner_type,pt_member_idx,pt_id
                       ,pt_nick_name,pt_partner_type,main_type,sub_type,domain,ip,country,detail_info,`status`,value_type) 
                       VALUES (l_u_key, l_pkey, l_idx,v_id, v_nick_name, v_level,l_partner_type
                       ,l_pt_idx ,l_pt_id,l_pt_nick_name,l_pt_partner_type
                       ,41,2003,'' ,'' ,'',JSON_OBJECT('desc','noble에서 획득한 머니', 'money',l_pt_noble_money,'partner_id',in_head_office_id,'after_money',v_money + l_pt_noble_money
                       ,'partner_idx',h_idx,'before_money',v_money,'partner_nick_name',in_head_office_id,'parent_partner_type',201,'partner_type_classification',201)
                       ,1,2);    
				   END IF;
                   
				   IF EXISTS(SELECT * FROM mydb_nova.member where id = v_id) THEN
                    SELECT `point`,money INTO l_pt_nova_point,l_pt_nova_money FROM mydb_nova.member where id = v_id;
					set l_u_key = UUID();
					INSERT INTO log.log_info (u_key,pkey,member_idx,id,nick_name,`level`,partner_type,pt_member_idx,pt_id
                       ,pt_nick_name,pt_partner_type,main_type,sub_type,domain,ip,country,detail_info,`status`,value_type) 
                       VALUES (l_u_key, l_pkey, l_idx,v_id, v_nick_name, v_level,l_partner_type
                       ,l_pt_idx ,l_pt_id,l_pt_nick_name,l_pt_partner_type
                       ,41,3001,'' ,'' ,'',JSON_OBJECT('desc','bunker에서 획득한 포인트', 'point',l_pt_nova_point,'partner_id',in_head_office_id,'after_point',v_point + l_pt_noble_point + l_pt_nova_point
                       ,'partner_idx',h_idx,'before_point',v_point + l_pt_noble_point,'partner_nick_name',in_head_office_id,'parent_partner_type',201,'partner_type_classification',201)
                       ,1,2);
                       
					INSERT INTO log.log_info (u_key,pkey,member_idx,id,nick_name,`level`,partner_type,pt_member_idx,pt_id
                       ,pt_nick_name,pt_partner_type,main_type,sub_type,domain,ip,country,detail_info,`status`,value_type) 
                       VALUES (l_u_key, l_pkey, l_idx,v_id, v_nick_name, v_level,l_partner_type
                       ,l_pt_idx ,l_pt_id,l_pt_nick_name,l_pt_partner_type
                       ,41,2003,'' ,'' ,'',JSON_OBJECT('desc','bunker에서 획득한 머니', 'money',l_pt_nova_money,'partner_id',in_head_office_id,'after_money',v_money + l_pt_noble_money + l_pt_nova_money
                       ,'partner_idx',h_idx,'before_money',v_money + l_pt_noble_money,'partner_nick_name',in_head_office_id,'parent_partner_type',201,'partner_type_classification',201)
                       ,1,2);    
				   END IF;
                   
                   set l_bank_id = 0;
				   IF EXISTS(SELECT * FROM main.static_account where account_code = v_account_bank) THEN
					   SELECT bank_id into l_bank_id FROM main.static_account where account_code = v_account_bank;

					   IF v_account_name is null THEN
						set v_account_name = '';
					   END IF;
					   
					   IF v_account_number is null THEN
						set v_account_number = '';
					   END IF;
					   
					   IF v_exchange_password is null THEN
						set v_exchange_password = '';
					   END IF;
					   
					   insert into partner_bank(partner_idx,account_bank_id,account_name,account_number,exchange_password)
						value(l_idx,l_bank_id,v_account_name,v_account_number,v_exchange_password);
				   END IF;
                   -- 메모
                    IF EXISTS(SELECT * FROM mydb_gamble.t_member_memo where member_idx = v_idx) THEN
					   insert into member_memo(member_idx,type,content,a_id,reg_time) 
						SELECT l_idx,m_type,content,a_id,reg_time FROM mydb_gamble.t_member_memo where member_idx = v_idx;
					END IF;
                   -- 도메인 정보 등록
                   IF h_domain is null THEN
					set h_domain = '';
                   END IF;
                   
                   insert into member_admin_domain(partner_idx,domain,id) value (l_idx,h_domain,v_id);
                   
				   -- 요율 정보 
                   
                   IF EXISTS(SELECT * from mydb_gamble.shop_config where member_idx = v_idx) THEN
					   select  bet_pre_s_fee,bet_pre_d_fee,bet_real_s_fee
					   ,bet_real_d_fee,bet_mini_fee,pre_s_fee
					   ,bet_casino_fee,bet_slot_fee,bet_win_mini_fee
					   ,bet_win_casino_fee,bet_win_slot_fee,bet_lose_mini_fee
					   ,bet_lose_casino_fee,bet_lose_slot_fee
					   into v_bet_pre_s_fee,v_bet_pre_d_fee,v_bet_real_s_fee
					   ,v_bet_real_d_fee,v_bet_mini_fee,v_pre_s_fee
					   ,v_bet_casino_fee,v_bet_slot_fee,v_bet_win_mini_fee
					   ,v_bet_win_casino_fee,v_bet_win_slot_fee,v_bet_lose_mini_fee
					   ,v_bet_lose_casino_fee,v_bet_lose_slot_fee from mydb_gamble.shop_config where member_idx = v_idx;
					   -- 
					   insert into partner_rates(partner_idx,rate_type,value) select l_idx,type,0 from static_rates; 
					   
					   update partner_rates set value = v_bet_pre_s_fee where partner_idx = l_idx and rate_type = 2;
					   update partner_rates set value = v_bet_pre_d_fee where partner_idx = l_idx and rate_type = 3;
					   update partner_rates set value = v_bet_real_s_fee where partner_idx = l_idx and rate_type = 4;
					   update partner_rates set value = v_bet_real_d_fee where partner_idx = l_idx and rate_type = 5;
					   update partner_rates set value = v_bet_mini_fee where partner_idx = l_idx and rate_type = 6;
					   update partner_rates set value = v_bet_mini_fee where partner_idx = l_idx and rate_type = 7;
					   
					   update partner_rates set value = v_pre_s_fee where partner_idx = l_idx and rate_type = 1;
					   
					   update partner_rates set value = v_bet_casino_fee where partner_idx = l_idx and rate_type = 8;
					   update partner_rates set value = v_bet_slot_fee where partner_idx = l_idx and rate_type = 9;
										  
					   update partner_rates set value = v_bet_win_mini_fee where partner_idx = l_idx and rate_type = 205;
					   update partner_rates set value = v_bet_win_mini_fee where partner_idx = l_idx and rate_type = 206;
					   
					   update partner_rates set value = v_bet_win_casino_fee where partner_idx = l_idx and rate_type = 207;
					   update partner_rates set value = v_bet_win_slot_fee where partner_idx = l_idx and rate_type = 208;
										  
					   update partner_rates set value = v_bet_lose_mini_fee where partner_idx = l_idx and rate_type = 104;
					   update partner_rates set value = v_bet_lose_mini_fee where partner_idx = l_idx and rate_type = 105;
					   
					   update partner_rates set value = v_bet_lose_casino_fee where partner_idx = l_idx and rate_type = 106;
					   update partner_rates set value = v_bet_lose_slot_fee where partner_idx = l_idx and rate_type = 107;
                   END IF;
                   -- 각 파트너에 맞게 하부를 생성해준다. 매장정보를 저장한다.
                    -- 파트너 정보를 생성한다
				   IF 10 = v_u_business THEN
                       ##########################부본########################
						set l_low_sales_idx = create_partner(h_idx
						,v_id
						,'b'
						,l_idx
						,401
						,h_domain 
						,h_password 
						,h_second_password
						,'none'
						);
                        
                        set l_dist_idx = create_partner(h_idx
						,v_id
						,'c'
						,l_low_sales_idx
						,501
						,h_domain 
						,h_password 
						,h_second_password
						,'none'
						);
                        
					    set l_shop_idx = create_partner(h_idx
						,v_id
						,'d'
						,l_dist_idx
						,601
						,h_domain 
						,h_password 
						,h_second_password
                        ,'none'
						);
					
				   ELSEIF 11 = v_u_business THEN
                     set l_dist_idx = create_partner(h_idx
						,v_id
						,'c'
						,l_idx
						,501
						,h_domain 
						,h_password 
						,h_second_password
						,'none'
						);
                        
					    set l_shop_idx = create_partner(h_idx
						,v_id
						,'d'
						,l_dist_idx
						,601
						,h_domain 
						,h_password 
						,h_second_password
						,'none'
						);
				   ELSEIF 12 = v_u_business THEN
					    set l_shop_idx = create_partner(h_idx
						,v_id
						,'d'
						,l_idx
						,601
						,h_domain 
						,h_password 
						,h_second_password
						,'none'
						);
				   END IF;
                   
                   update member set temp_shop_idx = l_shop_idx where idx = l_idx;
			              
			  END LOOP read_loop;
	    CLOSE openCursor;
        select 'end_partner';
        END main_partner;
        -- 일반유저 이관
      
        main_member : BEGIN -- gamble 정보 이관
        DECLARE v_idx bigint;
		DECLARE v_id varchar(45);
		declare v_charge_first_per int;
		DECLARE v_password varchar(200);
		DECLARE v_nick_name varchar(45);
		DECLARE v_u_business int;
		declare v_money int;
		declare v_point int;
		declare v_call varchar(45);
		
		DECLARE v_is_recommend enum('Y','N');
		DECLARE v_status int;
		DECLARE v_level int;
		DECLARE v_auto_level enum('Y','N');
		
		DECLARE v_last_login datetime;
		DECLARE v_recommend_code varchar(45);
		DECLARE v_recommend_member int;
		
		DECLARE v_account_number varchar(45);
		DECLARE v_account_name varchar(45);
		DECLARE v_account_bank varchar(45);
		
		DECLARE v_exchange_password varchar(200);
		
		DECLARE v_is_monitor enum('Y','N');
		DECLARE v_is_monitor_charge enum('Y','N');
		DECLARE v_is_monitor_security enum('Y','N');
		DECLARE v_is_monitor_bet enum('Y','N');
		
		DECLARE v_dis_id varchar(45);
		
		DECLARE v_reg_time varchar(45);
		DECLARE v_reg_first_charge varchar(45);
		DECLARE v_birth varchar(45);
		DECLARE v_mobile_carrier int;
		DECLARE v_g_point int;
		DECLARE v_g_money int;
        	    
		declare l_idx bigint default 0;
		declare l_partner_type int default 0;
		declare l_bank_id int default 0;
	   	        
		declare l_pt_idx bigint default 0;
        declare l_charge_first_per enum('Y','N') default 'N';
		declare l_reg_first_charge enum('Y','N') default 'N';
		declare l_member_type varchar(20) default 'player';
        
        declare l_temp_recommend_id varchar(64) default '';
        
        DECLARE done INTEGER DEFAULT 0;
        
        DEClARE openCursor_mb CURSOR FOR SELECT bg_mb.idx,bg_mb.id,bg_mb.charge_first_per,bg_mb.`password`
		,bg_mb.nick_name,bg_mb.u_business,bg_mb.money
		,bg_mb.`point`,bg_mb.`call`,bg_mb.is_recommend
		,bg_mb.`status`,bg_mb.`level`,bg_mb.auto_level
		,bg_mb.last_login,bg_mb.recommend_code,bg_mb.recommend_member
		,bg_mb.account_number,bg_mb.account_name,bg_mb.account_bank
		,bg_mb.exchange_password,bg_mb.is_monitor,bg_mb.is_monitor_charge
		,bg_mb.is_monitor_security,bg_mb.is_monitor_bet,bg_mb.dis_id
		,bg_mb.reg_time,bg_mb.reg_first_charge,bg_mb.birth
		,bg_mb.mobile_carrier,bg_mb.g_point,bg_mb.g_money
		FROM mydb_gamble.member as bg_mb where bg_mb.u_business = 1 and bg_mb.`status` in (1,2,11) and bg_mb.nick_name is not null and bg_mb.call is not null;-- and level <> 9;
    
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
        
        select 'start main_member_gamble';
        OPEN openCursor_mb;
			  mb_read_loop: LOOP
				  FETCH openCursor_mb INTO  v_idx,v_id,v_charge_first_per,v_password
                    ,v_nick_name,v_u_business,v_money
                    ,v_point,v_call,v_is_recommend
                    ,v_status,v_level,v_auto_level
                    ,v_last_login,v_recommend_code,v_recommend_member
					,v_account_number,v_account_name,v_account_bank
                    ,v_exchange_password,v_is_monitor,v_is_monitor_charge
                    ,v_is_monitor_security,v_is_monitor_bet,v_dis_id
                    ,v_reg_time,v_reg_first_charge,v_birth
					,v_mobile_carrier,v_g_point,v_g_money;
                                      
				   IF done THEN
                    -- SELECT 'mb_leave';    
					LEAVE mb_read_loop;
				   END IF;
                   
				   set l_idx = 0;
				   set l_partner_type = 0;
				   set l_bank_id = 0;
	   	           set l_pt_idx  =  0;
				   set l_charge_first_per  =  'N';
				   set l_reg_first_charge  = 'N';
				   set l_member_type  =  'player';
        
				   IF EXISTS(SELECT * from member where id = v_dis_id and head_office_idx = h_idx) THEN
					select temp_shop_idx into l_pt_idx from member where id = v_dis_id and head_office_idx = h_idx;
                   END IF;
                   IF v_birth is null then
					set v_birth = '';
                   END IF;
                   
                   IF v_last_login is null THEN
                    set v_last_login = '';
                   END IF; 
                   -- password 는 temp_password, dis_id 는 temp_dis_id에 저장한다.,#646464
                   
                   if 9 = v_level then
					set l_member_type = 'tester';
                   end if;
                                      
                   IF EXISTS(SELECT * from mydb_gamble.member where idx = v_recommend_member) THEN
					select id into l_temp_recommend_id from mydb_gamble.member where idx = v_recommend_member;
                   END IF;
                   
                   set v_money = 0;
                   set v_point = 0;
                   
                  
                   insert into member (
                   id,nick_name,temp_password,second_password,partner_type
                   ,money,`point`,hp,is_recommend,`status`
                   ,`level`,is_auto_level_up,last_login,temp_dis_id,reg_time
                   ,birth,mobile_carrier,member_type,id_color,temp_recommend_idx,parent_partner_idx,g_money,temp_recommend_id,temp_mg)
					value(v_id,v_nick_name,v_password,h_second_password,10001
                    ,v_money,v_point,v_call,v_is_recommend,v_status
                    ,v_level,v_auto_level,v_last_login,v_dis_id,v_reg_time
                    ,v_birth,v_mobile_carrier,l_member_type,'#646464',v_recommend_member,l_pt_idx,v_g_money,l_temp_recommend_id,'gamble'
                    );
					
				   set l_idx = last_insert_id();
				   -- select 'gamble',l_idx;	
				   update member set head_office_idx = h_idx,unique_id = concat(v_id,'_',h_idx) where idx = l_idx;
                
                   set l_bank_id = 0;
                   IF EXISTS(SELECT * FROM main.static_account where account_code = v_account_bank) THEN
					   SELECT bank_id into l_bank_id FROM main.static_account where account_code = v_account_bank;

					   IF v_account_name is null THEN
						set v_account_name = '';
					   END IF;
					   
					   IF v_account_number is null THEN
						set v_account_number = '';
					   END IF;
					   
					   IF v_exchange_password is null THEN
						set v_exchange_password = '';
					   END IF;
					   
					   
					   insert into member_bank(member_idx,account_bank_id,account_name,account_number,exchange_password)
						value(l_idx,l_bank_id,v_account_name,v_account_number,v_exchange_password);
				   END IF;
                   -- 메모
                    IF EXISTS(SELECT * FROM mydb_gamble.t_member_memo where member_idx = v_idx) THEN
					   insert into member_memo(member_idx,type,content,a_id,reg_time) 
						SELECT l_idx,m_type,content,a_id,reg_time FROM mydb_gamble.t_member_memo where member_idx = v_idx;
                    END IF;
                   -- 이벤트
                   IF 0 = v_charge_first_per THEN
					set l_charge_first_per = 'N';
                   END IF;
                   
                   IF 0 = v_reg_first_charge THEN
					set l_reg_first_charge = 'N';
                   END IF;
                   
                   IF null = v_charge_first_per THEN
					set l_charge_first_per = 'N';
                   END IF;
                   
                   IF null = v_reg_first_charge THEN
					set l_reg_first_charge = 'N';
                   END IF;
                                      
				   insert into member_event_charge(member_idx,first_register_charge,first_charge)
					value(l_idx,l_reg_first_charge,l_charge_first_per);
                  
                   -- 베팅제한
                   main_2 : BEGIN
                        
                        DECLARE v_game_type int DEFAULT 0;
						DECLARE v_status enum('ON','OFF');
                        DECLARE gt_done INTEGER DEFAULT 0;
                        
                        DECLARE l_name varchar(64);
                        declare l_type int ;
						DEClARE openCursor_bet CURSOR FOR 
						SELECT bg_mb_gt.game_type,bg_mb_gt.`status`
						FROM mydb_gamble.member_game_type as bg_mb_gt 
                        WHERE bg_mb_gt.member_idx = v_idx and bg_mb_gt.game_type not in(5,11,12,13,16,21);
                        
    					DECLARE CONTINUE HANDLER FOR NOT FOUND SET gt_done = true;
						
                        OPEN openCursor_bet;
							bet_read_loop: LOOP
								FETCH  openCursor_bet INTO  v_game_type,v_status;
													  
								IF gt_done THEN
                                    -- select 'bet_read_loop';
									LEAVE bet_read_loop;
							    END IF;
                                
                                IF 1 = v_game_type then
									set l_type = 1;
                                ELSEIF 2 = v_game_type then
									set l_type = 2;
								ELSEIF 3 = v_game_type then
									set l_type = 10;                        
                                ELSEIF 4 = v_game_type then
									set l_type = 11;        
                                ELSEIF 6 = v_game_type then
									set l_type = 207;
                                ELSEIF 7 = v_game_type then
									set l_type = 208;    
							    ELSEIF 8 = v_game_type then
									set l_type = 101;    
                                ELSEIF 9 = v_game_type then
									set l_type = 300;
                                ELSEIF 10 = v_game_type then
									set l_type = 301;      

                                ELSEIF 14 = v_game_type then
									set l_type = 12;      
								ELSEIF 15 = v_game_type then
									set l_type = 201;      
                                    
                                ELSEIF 50 = v_game_type then
									set l_type = 105;       
                                ELSEIF 51 = v_game_type then
									set l_type = 108;       
                                ELSEIF 52 = v_game_type then
									set l_type = 202;       
                                ELSEIF 53 = v_game_type then
									set l_type = 203;           
                                ELSEIF 54 = v_game_type then
									set l_type = 204;       
                                ELSEIF 55 = v_game_type then
									set l_type = 205;       
                                ELSEIF 56 = v_game_type then
									set l_type = 206;
                                    
                                ELSEIF 57 = v_game_type then
									set l_type = 209;       
                                ELSEIF 58 = v_game_type then
									set l_type = 210;       
                                    
                                ELSEIF 59 = v_game_type then
									set l_type = 211;       
                                ELSEIF 60 = v_game_type then
									set l_type = 212;       
                                ELSEIF 61 = v_game_type then
									set l_type = 213;      
                                    
                                ELSEIF 62 = v_game_type then
									set l_type = 217;       
                                    
                                ELSEIF 63 = v_game_type then
									set l_type = 214;       
                                ELSEIF 64 = v_game_type then
									set l_type = 215;       
                                ELSEIF 65 = v_game_type then
									set l_type = 216;  
                                    
                                ELSEIF 66 = v_game_type then
									set l_type = 102;     
                                    
                                ELSEIF 67 = v_game_type then
									set l_type = 104;       
                                ELSEIF 68 = v_game_type then
									set l_type = 103;
                                    
                                ELSEIF 69 = v_game_type then
									set l_type = 109;
                                    
                                ELSEIF 70 = v_game_type then
									set l_type = 106;
                                    
							    ELSEIF 71 = v_game_type then
									set l_type = 107; 
                                    
							    ELSEIF 72 = v_game_type then
									set l_type = 13;
                                ELSEIF 73 = v_game_type then
									set l_type = 3;           
                                end if;
                                
                                IF 'ON' = v_status then
									set v_status = 'OFF';
                                else
									set v_status = 'ON';
                                end if;
                                
                             
                                -- SELECT l_type,l_idx;
								select name into l_name from static_game_betting_type_mgr where id = l_type;
								insert into member_game_betting_type_info(id,member_idx,name,status) value(l_type,l_idx,l_name,v_status);
                                
							END LOOP bet_read_loop;
					    CLOSE openCursor_bet;
                   END main_2;
                   
                   -- 모니터링 정보
                   insert into member_status_security(member_idx,type,is_monitor)
					values(l_idx,1,v_is_monitor)
                    ,(l_idx,2,v_is_monitor_charge)
                    ,(l_idx,3,v_is_monitor_security)
                    ,(l_idx,4,v_is_monitor_bet);
                   -- 개인개좌
                   
                    IF EXISTS(SELECT * FROM mydb_gamble.personal_account_number where member_idx = v_idx) THEN
						insert into personal_account_number(member_idx,account_number_1,account_name_1,account_bank_id_1
						,account_number_2,account_name_2,account_bank_id_2) 
						SELECT l_idx
						,mydb_gamble.personal_account_number.account_number_1 ,mydb_gamble.personal_account_number.account_name_1, sa_1.bank_id
						,mydb_gamble.personal_account_number.account_number_2 ,mydb_gamble.personal_account_number.account_name_2, sa_2.bank_id
						FROM mydb_gamble.personal_account_number 
						left join static_account as sa_1 on sa_1.account_code = mydb_gamble.personal_account_number.account_bank_1
						left join static_account as sa_2 on sa_2.account_code = mydb_gamble.personal_account_number.account_bank_2
						where member_idx = v_idx;
                    END IF;
                    -- 종합정보 이관 
                    main_total_info : BEGIN
						declare v_charge_total_count int;
                        declare v_charge_total_money bigint;
                        declare v_exchange_total_count int;
                        declare v_exchange_total_money bigint;
                        declare v_bet_total_count int; 
						declare	v_bet_total_money bigint;
                        declare v_pre_s_total_money bigint;
						declare v_pre_m_total_money bigint;
						declare v_real_s_total_money bigint;
						declare v_real_m_total_money bigint; 
						declare v_mini_total_money bigint; 
						declare v_casino_total_money bigint; 
						declare v_slot_total_money bigint; 
						declare v_take_total_money bigint; 
						declare v_take_slot_total_money bigint ;
						declare v_take_casino_total_money bigint; 
						declare v_take_mini_total_money bigint; 
						declare v_take_real_m_total_money bigint; 
						declare v_take_real_s_total_money bigint; 
						declare v_take_pre_m_total_money bigint; 
						declare v_take_pre_s_total_money bigint;
                        declare v_vir_total_money bigint; 
						declare v_take_vir_total_money bigint;
						IF EXISTS(SELECT * from mydb_gamble.total_member_cash where member_idx = v_idx) THEN
							select charge_total_count,charge_total_money ,exchange_total_count,exchange_total_money
							,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,casino_total_money
							,slot_total_money,take_total_money,take_slot_total_money,take_casino_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,vir_total_money,take_vir_total_money
							into 
							v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money
							,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_casino_total_money
							,v_slot_total_money,v_take_total_money,v_take_slot_total_money,v_take_casino_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_vir_total_money,v_take_vir_total_money
							from mydb_gamble.total_member_cash where member_idx = v_idx;
							
							insert into statistic_member_comprehensive_details(member_idx,total_charge,total_exchange,total_charge_count,total_exchange_count
							,total_bet_count,total_bet,total_win_bet,total_one_prematch_bet,total_one_prematch_win_bet
							,total_multi_prematch_bet ,total_multi_prematch_win_bet,total_one_inplay_bet ,total_one_inplay_win_bet 
							,total_multi_inplay_bet ,total_multi_inplay_win_bet,total_casino_bet ,total_casino_win_bet
							,total_slot_bet ,total_slot_win_bet ) 
							value(l_idx,v_charge_total_money,v_exchange_total_money,v_charge_total_count,v_exchange_total_count
							,v_bet_total_count,v_bet_total_money,v_take_total_money,v_pre_s_total_money,v_take_pre_s_total_money
							,v_pre_m_total_money,v_take_pre_m_total_money,v_real_s_total_money,v_take_real_s_total_money
							,v_real_m_total_money,v_take_real_m_total_money,v_casino_total_money,v_take_casino_total_money
							,v_slot_total_money,v_take_slot_total_money);
                        END IF;
                        
                    END main_total_info;
                   
                  -- SELECT 'end';
			  END LOOP mb_read_loop;
        CLOSE openCursor_mb;
		select 'end main_member_gamble';
        END main_member;
                
		main_member_noble : BEGIN -- noble 정보 이관
        DECLARE v_idx bigint;
		DECLARE v_id varchar(45);
		declare v_charge_first_per int;
		DECLARE v_password varchar(200);
		DECLARE v_nick_name varchar(45);
		DECLARE v_u_business int;
		declare v_money int;
		declare v_point int;
		declare v_call varchar(45);
		
		DECLARE v_is_recommend enum('Y','N');
		DECLARE v_status int;
		DECLARE v_level int;
		DECLARE v_auto_level enum('Y','N');
		
		DECLARE v_last_login datetime;
		DECLARE v_recommend_code varchar(45);
		DECLARE v_recommend_member int;
		
		DECLARE v_account_number varchar(45);
		DECLARE v_account_name varchar(45);
		DECLARE v_account_bank varchar(45);
		
		DECLARE v_exchange_password varchar(200);
		
		DECLARE v_is_monitor enum('Y','N');
		DECLARE v_is_monitor_charge enum('Y','N');
		DECLARE v_is_monitor_security enum('Y','N');
		DECLARE v_is_monitor_bet enum('Y','N');
		
		DECLARE v_dis_id varchar(45);
		
		DECLARE v_reg_time varchar(45);
		DECLARE v_reg_first_charge varchar(45);
		DECLARE v_birth varchar(45);
		DECLARE v_mobile_carrier int;
		DECLARE v_g_point int;
		DECLARE v_g_money int;
        	    
		declare l_idx bigint default 0;
		declare l_partner_type int default 0;
		declare l_bank_id int default 0;
	   	        
		declare l_pt_idx bigint default 0;
        declare l_charge_first_per enum('Y','N') default 'N';
		declare l_reg_first_charge enum('Y','N') default 'N';
		declare l_member_type varchar(20) default 'player';
        declare l_temp_recommend_id varchar(64) default '';

        DECLARE done INTEGER DEFAULT 0;
        
        DEClARE openCursor_mb CURSOR FOR SELECT bg_mb.idx,bg_mb.id,bg_mb.charge_first_per,bg_mb.`password`
		,bg_mb.nick_name,bg_mb.u_business,bg_mb.money
		,bg_mb.`point`,bg_mb.`call`,bg_mb.is_recommend
		,bg_mb.`status`,bg_mb.`level`,bg_mb.auto_level
		,bg_mb.last_login,bg_mb.recommend_code,bg_mb.recommend_member
		,bg_mb.account_number,bg_mb.account_name,bg_mb.account_bank
		,bg_mb.exchange_password,bg_mb.is_monitor,bg_mb.is_monitor_charge
		,bg_mb.is_monitor_security,bg_mb.is_monitor_bet,bg_mb.dis_id
		,bg_mb.reg_time,bg_mb.reg_first_charge,bg_mb.birth
		,bg_mb.mobile_carrier,bg_mb.g_money
		FROM mydb_noble.member as bg_mb 
        where bg_mb.u_business = 1 and level <> 9 and `status` = 1 and bg_mb.nick_name is not null and bg_mb.call is not null and bg_mb.dis_id is not null;
    
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
        select 'start main_member_noble';
        OPEN openCursor_mb;
			  mb_read_loop: LOOP
				  FETCH openCursor_mb INTO  v_idx,v_id,v_charge_first_per,v_password
                    ,v_nick_name,v_u_business,v_money
                    ,v_point,v_call,v_is_recommend
                    ,v_status,v_level,v_auto_level
                    ,v_last_login,v_recommend_code,v_recommend_member
					,v_account_number,v_account_name,v_account_bank
                    ,v_exchange_password,v_is_monitor,v_is_monitor_charge
                    ,v_is_monitor_security,v_is_monitor_bet,v_dis_id
                    ,v_reg_time,v_reg_first_charge,v_birth
					,v_mobile_carrier,v_g_money;
                                      
				   IF done THEN
                    -- SELECT 'mb_leave';    
					LEAVE mb_read_loop;
				   END IF;
                   
				   IF EXISTS(SELECT * from member where id = v_id and head_office_idx = h_idx) THEN
					ITERATE mb_read_loop;
                   END IF;
                   
                   IF EXISTS(SELECT * from member where id = v_nick_name and head_office_idx = h_idx) THEN
					set v_nick_name = concat(v_nick_name,'_',h_idx);
                   END IF;
                   
				   set l_idx = 0;
				   set l_partner_type = 0;
				   set l_bank_id = 0;
	   	           set l_pt_idx  =  0;
				   set l_charge_first_per  =  'N';
				   set l_reg_first_charge  = 'N';
				   set l_member_type  =  'player';
        
				   IF EXISTS(SELECT * from member where id = v_dis_id and head_office_idx = h_idx) THEN
					select temp_shop_idx into l_pt_idx from member where id = v_dis_id and head_office_idx = h_idx;
                   END IF;
                   
                   IF l_pt_idx is null then
                    ITERATE mb_read_loop;
                   END IF;
                   
                   IF v_birth is null then
					set v_birth = '';
                   END IF;
                   
                   IF v_last_login is null THEN
                    set v_last_login = '';
                   END IF; 
                   -- password 는 temp_password, dis_id 는 temp_dis_id에 저장한다.,#646464
                   
                   if 9 = v_level then
					set l_member_type = 'tester';
                   end if;
                   
                   IF EXISTS(SELECT * from mydb_noble.member where idx = v_recommend_member) THEN
					select id into l_temp_recommend_id from mydb_noble.member where idx = v_recommend_member;
                   END IF;
                   
                   set v_money = 0;
                   set v_point = 0;
                   
                   insert into member (
                   id,nick_name,temp_password,second_password,partner_type
                   ,money,`point`,hp,is_recommend,`status`
                   ,`level`,is_auto_level_up,last_login,temp_dis_id,reg_time
                   ,birth,mobile_carrier,member_type,id_color,temp_recommend_idx,parent_partner_idx,g_money,temp_recommend_id,temp_mg)
					value(v_id,v_nick_name,v_password,h_second_password,10001
                    ,v_money,v_point,v_call,v_is_recommend,v_status
                    ,v_level,v_auto_level,v_last_login,v_dis_id,v_reg_time
                    ,v_birth,v_mobile_carrier,l_member_type,'#646464',v_recommend_member,l_pt_idx,v_g_money,l_temp_recommend_id,'noble'
                    );
					
				   set l_idx = last_insert_id();
					
				   update member set head_office_idx = h_idx,unique_id = concat(v_id,'_',h_idx) where idx = l_idx;
                
                   set l_bank_id = 0;
                   IF EXISTS(SELECT * FROM main.static_account where account_code = v_account_bank) THEN
					   SELECT bank_id into l_bank_id FROM main.static_account where account_code = v_account_bank;

					   IF v_account_name is null THEN
						set v_account_name = '';
					   END IF;
					   
					   IF v_account_number is null THEN
						set v_account_number = '';
					   END IF;
					   
					   IF v_exchange_password is null THEN
						set v_exchange_password = '';
					   END IF;
					   
					   
					   insert into member_bank(member_idx,account_bank_id,account_name,account_number,exchange_password)
						value(l_idx,l_bank_id,v_account_name,v_account_number,v_exchange_password);
				   END IF;
                   -- 메모
                    IF EXISTS(SELECT * FROM mydb_noble.t_member_memo where member_idx = v_idx) THEN
					   insert into member_memo(member_idx,type,content,a_id,reg_time) 
						SELECT l_idx,m_type,content,a_id,reg_time FROM mydb_noble.t_member_memo where member_idx = v_idx;
                    END IF;
                   -- 이벤트
                   IF 0 = v_charge_first_per THEN
					set l_charge_first_per = 'N';
                   END IF;
                   
                   IF 0 = v_reg_first_charge THEN
					set l_reg_first_charge = 'N';
                   END IF;
                   
                   IF null = v_charge_first_per THEN
					set l_charge_first_per = 'N';
                   END IF;
                   
                   IF null = v_reg_first_charge THEN
					set l_reg_first_charge = 'N';
                   END IF;
                                      
				   insert into member_event_charge(member_idx,first_register_charge,first_charge)
					value(l_idx,l_reg_first_charge,l_charge_first_per);
                  
                   -- 베팅제한
                   main_2 : BEGIN
                        
                        DECLARE v_game_type int DEFAULT 0;
						DECLARE v_status enum('ON','OFF');
                        DECLARE gt_done INTEGER DEFAULT 0;
                        
                        DECLARE l_name varchar(64);
                        declare l_type int ;
						DEClARE openCursor_bet CURSOR FOR 
						SELECT bg_mb_gt.game_type,bg_mb_gt.`status`
						FROM mydb_noble.member_game_type as bg_mb_gt 
                        WHERE bg_mb_gt.member_idx = v_idx and bg_mb_gt.game_type not in(5,11,12,13,16,21);
                        
    					DECLARE CONTINUE HANDLER FOR NOT FOUND SET gt_done = true;
						
                        OPEN openCursor_bet;
							bet_read_loop: LOOP
								FETCH  openCursor_bet INTO  v_game_type,v_status;
													  
								IF gt_done THEN
                                    -- select 'bet_read_loop';
									LEAVE bet_read_loop;
							    END IF;
                                
                                IF 1 = v_game_type then
									set l_type = 1;
                                ELSEIF 2 = v_game_type then
									set l_type = 2;
								ELSEIF 3 = v_game_type then
									set l_type = 10;                        
                                ELSEIF 4 = v_game_type then
									set l_type = 11;        
                                ELSEIF 6 = v_game_type then
									set l_type = 207;
                                ELSEIF 7 = v_game_type then
									set l_type = 208;    
							    ELSEIF 8 = v_game_type then
									set l_type = 101;    
                                ELSEIF 9 = v_game_type then
									set l_type = 300;
                                ELSEIF 10 = v_game_type then
									set l_type = 301;      

                                ELSEIF 14 = v_game_type then
									set l_type = 12;      
								ELSEIF 15 = v_game_type then
									set l_type = 201;      
                                    
                                ELSEIF 50 = v_game_type then
									set l_type = 105;       
                                ELSEIF 51 = v_game_type then
									set l_type = 108;       
                                ELSEIF 52 = v_game_type then
									set l_type = 202;       
                                ELSEIF 53 = v_game_type then
									set l_type = 203;           
                                ELSEIF 54 = v_game_type then
									set l_type = 204;       
                                ELSEIF 55 = v_game_type then
									set l_type = 205;       
                                ELSEIF 56 = v_game_type then
									set l_type = 206;
                                    
                                ELSEIF 57 = v_game_type then
									set l_type = 209;       
                                ELSEIF 58 = v_game_type then
									set l_type = 210;       
                                    
                                ELSEIF 59 = v_game_type then
									set l_type = 211;       
                                ELSEIF 60 = v_game_type then
									set l_type = 212;       
                                ELSEIF 61 = v_game_type then
									set l_type = 213;      
                                    
                                ELSEIF 62 = v_game_type then
									set l_type = 217;       
                                    
                                ELSEIF 63 = v_game_type then
									set l_type = 214;       
                                ELSEIF 64 = v_game_type then
									set l_type = 215;       
                                ELSEIF 65 = v_game_type then
									set l_type = 216;  
                                    
                                ELSEIF 66 = v_game_type then
									set l_type = 102;     
                                    
                                ELSEIF 67 = v_game_type then
									set l_type = 104;       
                                ELSEIF 68 = v_game_type then
									set l_type = 103;
                                    
                                ELSEIF 69 = v_game_type then
									set l_type = 109;
                                    
                                ELSEIF 70 = v_game_type then
									set l_type = 106;
                                    
							    ELSEIF 71 = v_game_type then
									set l_type = 107; 
                                    
							    ELSEIF 72 = v_game_type then
									set l_type = 13;
                                ELSEIF 73 = v_game_type then
									set l_type = 3;           
                                end if;
                                
                                IF 'ON' = v_status then
									set v_status = 'OFF';
                                else
									set v_status = 'ON';
                                end if;
                                
                             
                                -- SELECT l_type,l_idx;
								select name into l_name from static_game_betting_type_mgr where id = l_type;
								insert into member_game_betting_type_info(id,member_idx,name,status) value(l_type,l_idx,l_name,v_status);
                                
							END LOOP bet_read_loop;
					    CLOSE openCursor_bet;
                   END main_2;
                   
                   -- 모니터링 정보
                   insert into member_status_security(member_idx,type,is_monitor)
					values(l_idx,1,v_is_monitor)
                    ,(l_idx,2,v_is_monitor_charge)
                    ,(l_idx,3,v_is_monitor_security)
                    ,(l_idx,4,v_is_monitor_bet);
                   -- 개인개좌
                   
                    IF EXISTS(SELECT * FROM mydb_noble.personal_account_number where member_idx = v_idx) THEN
						insert into personal_account_number(member_idx,account_number_1,account_name_1,account_bank_id_1
						,account_number_2,account_name_2,account_bank_id_2) 
						SELECT l_idx
						,mydb_noble.personal_account_number.account_number_1 ,mydb_noble.personal_account_number.account_name_1, sa_1.bank_id
						,mydb_noble.personal_account_number.account_number_2 ,mydb_noble.personal_account_number.account_name_2, sa_2.bank_id
						FROM mydb_noble.personal_account_number 
						left join static_account as sa_1 on sa_1.account_code = mydb_noble.personal_account_number.account_bank_1
						left join static_account as sa_2 on sa_2.account_code = mydb_noble.personal_account_number.account_bank_2
						where member_idx = v_idx;
                    END IF;
                    -- 종합정보 이관 
                    main_total_info : BEGIN
						declare v_charge_total_count int;
                        declare v_charge_total_money bigint;
                        declare v_exchange_total_count int;
                        declare v_exchange_total_money bigint;
                        declare v_bet_total_count int; 
						declare	v_bet_total_money bigint;
                        declare v_pre_s_total_money bigint;
						declare v_pre_m_total_money bigint;
						declare v_real_s_total_money bigint;
						declare v_real_m_total_money bigint; 
						declare v_mini_total_money bigint; 
						declare v_casino_total_money bigint; 
						declare v_slot_total_money bigint; 
						declare v_take_total_money bigint; 
						declare v_take_slot_total_money bigint ;
						declare v_take_casino_total_money bigint; 
						declare v_take_mini_total_money bigint; 
						declare v_take_real_m_total_money bigint; 
						declare v_take_real_s_total_money bigint; 
						declare v_take_pre_m_total_money bigint; 
						declare v_take_pre_s_total_money bigint;
                        declare v_vir_total_money bigint; 
						declare v_take_vir_total_money bigint;
						IF EXISTS(SELECT * from mydb_noble.total_member_cash where member_idx = v_idx) THEN
							select charge_total_count,charge_total_money ,exchange_total_count,exchange_total_money
							,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,casino_total_money
							,slot_total_money,take_total_money,take_slot_total_money,take_casino_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,vir_total_money,take_vir_total_money
							into 
							v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money
							,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_casino_total_money
							,v_slot_total_money,v_take_total_money,v_take_slot_total_money,v_take_casino_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_vir_total_money,v_take_vir_total_money
							from mydb_noble.total_member_cash where member_idx = v_idx;
							
							insert into statistic_member_comprehensive_details(member_idx,total_charge,total_exchange,total_charge_count,total_exchange_count
							,total_bet_count,total_bet,total_win_bet,total_one_prematch_bet,total_one_prematch_win_bet
							,total_multi_prematch_bet ,total_multi_prematch_win_bet,total_one_inplay_bet ,total_one_inplay_win_bet 
							,total_multi_inplay_bet ,total_multi_inplay_win_bet,total_casino_bet ,total_casino_win_bet
							,total_slot_bet ,total_slot_win_bet ) 
							value(l_idx,v_charge_total_money,v_exchange_total_money,v_charge_total_count,v_exchange_total_count
							,v_bet_total_count,v_bet_total_money,v_take_total_money,v_pre_s_total_money,v_take_pre_s_total_money
							,v_pre_m_total_money,v_take_pre_m_total_money,v_real_s_total_money,v_take_real_s_total_money
							,v_real_m_total_money,v_take_real_m_total_money,v_casino_total_money,v_take_casino_total_money
							,v_slot_total_money,v_take_slot_total_money);
                        END IF;
                        
                    END main_total_info;
                   
                  -- SELECT 'end';
			  END LOOP mb_read_loop;
        CLOSE openCursor_mb;
        select 'end main_member_noble';
        END main_member_noble;
                
        
        main_member_bunker : BEGIN -- bunker 정보 이관
        DECLARE v_idx bigint;
		DECLARE v_id varchar(45);
		declare v_charge_first_per int;
		DECLARE v_password varchar(200);
		DECLARE v_nick_name varchar(45);
		DECLARE v_u_business int;
		declare v_money int;
		declare v_point int;
		declare v_call varchar(45);
		
		DECLARE v_is_recommend enum('Y','N');
		DECLARE v_status int;
		DECLARE v_level int;
		DECLARE v_auto_level enum('Y','N');
		
		DECLARE v_last_login datetime;
		DECLARE v_recommend_code varchar(45);
		DECLARE v_recommend_member int;
		
		DECLARE v_account_number varchar(45);
		DECLARE v_account_name varchar(45);
		DECLARE v_account_bank varchar(45);
		
		DECLARE v_exchange_password varchar(200);
		
		DECLARE v_is_monitor enum('Y','N');
		DECLARE v_is_monitor_charge enum('Y','N');
		DECLARE v_is_monitor_security enum('Y','N');
		DECLARE v_is_monitor_bet enum('Y','N');
		
		DECLARE v_dis_id varchar(45);
		
		DECLARE v_reg_time varchar(45);
		DECLARE v_reg_first_charge varchar(45);
		DECLARE v_birth varchar(45);
		DECLARE v_mobile_carrier int;
		DECLARE v_g_point int;
		DECLARE v_g_money int;
        	    
		declare l_idx bigint default 0;
		declare l_partner_type int default 0;
		declare l_bank_id int default 0;
	   	        
		declare l_pt_idx bigint default 0;
        declare l_charge_first_per enum('Y','N') default 'N';
		declare l_reg_first_charge enum('Y','N') default 'N';
		declare l_member_type varchar(20) default 'player';
        declare l_temp_recommend_id varchar(64) default '';
        
        DECLARE done INTEGER DEFAULT 0;
        
        DEClARE openCursor_mb CURSOR FOR SELECT bg_mb.idx,bg_mb.id,bg_mb.charge_first_per,bg_mb.`password`
		,bg_mb.nick_name,bg_mb.u_business,bg_mb.money
		,bg_mb.`point`,bg_mb.`call`,bg_mb.is_recommend
		,bg_mb.`status`,bg_mb.`level`,bg_mb.auto_level
		,bg_mb.last_login,bg_mb.recommend_code,bg_mb.recommend_member
		,bg_mb.account_number,bg_mb.account_name,bg_mb.account_bank
		,bg_mb.exchange_password,bg_mb.is_monitor,bg_mb.is_monitor_charge
		,bg_mb.is_monitor_security,bg_mb.is_monitor_bet,bg_mb.dis_id
		,bg_mb.reg_time,bg_mb.reg_first_charge,bg_mb.birth
		,bg_mb.mobile_carrier,bg_mb.g_money
		FROM mydb_nova.member as bg_mb 
        where bg_mb.u_business = 1 and level <> 9 and `status` = 1 and bg_mb.nick_name is not null and bg_mb.call is not null and bg_mb.dis_id is not null;
    
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
		select 'start main_member_bunker';
        OPEN openCursor_mb;
			  mb_read_loop: LOOP
				  FETCH openCursor_mb INTO  v_idx,v_id,v_charge_first_per,v_password
                    ,v_nick_name,v_u_business,v_money
                    ,v_point,v_call,v_is_recommend
                    ,v_status,v_level,v_auto_level
                    ,v_last_login,v_recommend_code,v_recommend_member
					,v_account_number,v_account_name,v_account_bank
                    ,v_exchange_password,v_is_monitor,v_is_monitor_charge
                    ,v_is_monitor_security,v_is_monitor_bet,v_dis_id
                    ,v_reg_time,v_reg_first_charge,v_birth
					,v_mobile_carrier,v_g_money;
                                      
				   IF done THEN
                    -- SELECT 'mb_leave';    
					LEAVE mb_read_loop;
				   END IF;
                   
				   IF EXISTS(SELECT * from member where id = v_id and head_office_idx = h_idx) THEN
					ITERATE mb_read_loop;
                   END IF;
                   
                   IF EXISTS(SELECT * from member where id = v_nick_name and head_office_idx = h_idx) THEN
					set v_nick_name = concat(v_nick_name,'_',h_idx);
                   END IF;
                   
				   set l_idx = 0;
				   set l_partner_type = 0;
				   set l_bank_id = 0;
	   	           set l_pt_idx  =  0;
				   set l_charge_first_per  =  'N';
				   set l_reg_first_charge  = 'N';
				   set l_member_type  =  'player';
        
				   IF EXISTS(SELECT * from member where id = v_dis_id and head_office_idx = h_idx) THEN
					select temp_shop_idx into l_pt_idx from member where id = v_dis_id and head_office_idx = h_idx;
                   END IF;
                   
                   IF l_pt_idx is null then
                    ITERATE mb_read_loop;
                   END IF;
                   
                   IF v_birth is null then
					set v_birth = '';
                   END IF;
                   
                   IF v_last_login is null THEN
                    set v_last_login = '';
                   END IF; 
                   -- password 는 temp_password, dis_id 는 temp_dis_id에 저장한다.,#646464
                   
                   if 9 = v_level then
					set l_member_type = 'tester';
                   end if;
                   
                   IF EXISTS(SELECT * from mydb_nova.member where idx = v_recommend_member) THEN
					select id into l_temp_recommend_id from mydb_nova.member where idx = v_recommend_member;
                   END IF;
                   
                   set v_money = 0;
                   set v_point = 0;
                   
                   insert into member (
                   id,nick_name,temp_password,second_password,partner_type
                   ,money,`point`,hp,is_recommend,`status`
                   ,`level`,is_auto_level_up,last_login,temp_dis_id,reg_time
                   ,birth,mobile_carrier,member_type,id_color,temp_recommend_idx,parent_partner_idx,g_money,temp_recommend_id,temp_mg)
					value(v_id,v_nick_name,v_password,h_second_password,10001
                    ,v_money,v_point,v_call,v_is_recommend,v_status
                    ,v_level,v_auto_level,v_last_login,v_dis_id,v_reg_time
                    ,v_birth,v_mobile_carrier,l_member_type,'#646464',v_recommend_member,l_pt_idx,v_g_money,l_temp_recommend_id,'bunker'
                    );
					
				   set l_idx = last_insert_id();
					
				   update member set head_office_idx = h_idx,unique_id = concat(v_id,'_',h_idx) where idx = l_idx;
                
                   set l_bank_id = 0;
                   IF EXISTS(SELECT * FROM main.static_account where account_code = v_account_bank) THEN
					   SELECT bank_id into l_bank_id FROM main.static_account where account_code = v_account_bank;

					   IF v_account_name is null THEN
						set v_account_name = '';
					   END IF;
					   
					   IF v_account_number is null THEN
						set v_account_number = '';
					   END IF;
					   
					   IF v_exchange_password is null THEN
						set v_exchange_password = '';
					   END IF;
					   
					   
					   insert into member_bank(member_idx,account_bank_id,account_name,account_number,exchange_password)
						value(l_idx,l_bank_id,v_account_name,v_account_number,v_exchange_password);
				   END IF;
                   -- 메모
                    IF EXISTS(SELECT * FROM mydb_nova.t_member_memo where member_idx = v_idx) THEN
					   insert into member_memo(member_idx,type,content,a_id,reg_time) 
						SELECT l_idx,m_type,content,a_id,reg_time FROM mydb_nova.t_member_memo where member_idx = v_idx;
                    END IF;
                   -- 이벤트
                   IF 0 = v_charge_first_per THEN
					set l_charge_first_per = 'N';
                   END IF;
                   
                   IF 0 = v_reg_first_charge THEN
					set l_reg_first_charge = 'N';
                   END IF;
                   
                   IF null = v_charge_first_per THEN
					set l_charge_first_per = 'N';
                   END IF;
                   
                   IF null = v_reg_first_charge THEN
					set l_reg_first_charge = 'N';
                   END IF;
                                      
				   insert into member_event_charge(member_idx,first_register_charge,first_charge)
					value(l_idx,l_reg_first_charge,l_charge_first_per);
                  
                   -- 베팅제한
                   main_2 : BEGIN
                        
                        DECLARE v_game_type int DEFAULT 0;
						DECLARE v_status enum('ON','OFF');
                        DECLARE gt_done INTEGER DEFAULT 0;
                        
                        DECLARE l_name varchar(64);
                        declare l_type int ;
						DEClARE openCursor_bet CURSOR FOR 
						SELECT bg_mb_gt.game_type,bg_mb_gt.`status`
						FROM mydb_nova.member_game_type as bg_mb_gt 
                        WHERE bg_mb_gt.member_idx = v_idx and bg_mb_gt.game_type not in(5,11,12,13,16,21);
                        
    					DECLARE CONTINUE HANDLER FOR NOT FOUND SET gt_done = true;
						
                        OPEN openCursor_bet;
							bet_read_loop: LOOP
								FETCH  openCursor_bet INTO  v_game_type,v_status;
													  
								IF gt_done THEN
                                    -- select 'bet_read_loop';
									LEAVE bet_read_loop;
							    END IF;
                                
                                IF 1 = v_game_type then
									set l_type = 1;
                                ELSEIF 2 = v_game_type then
									set l_type = 2;
								ELSEIF 3 = v_game_type then
									set l_type = 10;                        
                                ELSEIF 4 = v_game_type then
									set l_type = 11;        
                                ELSEIF 6 = v_game_type then
									set l_type = 207;
                                ELSEIF 7 = v_game_type then
									set l_type = 208;    
							    ELSEIF 8 = v_game_type then
									set l_type = 101;    
                                ELSEIF 9 = v_game_type then
									set l_type = 300;
                                ELSEIF 10 = v_game_type then
									set l_type = 301;      

                                ELSEIF 14 = v_game_type then
									set l_type = 12;      
								ELSEIF 15 = v_game_type then
									set l_type = 201;      
                                    
                                ELSEIF 50 = v_game_type then
									set l_type = 105;       
                                ELSEIF 51 = v_game_type then
									set l_type = 108;       
                                ELSEIF 52 = v_game_type then
									set l_type = 202;       
                                ELSEIF 53 = v_game_type then
									set l_type = 203;           
                                ELSEIF 54 = v_game_type then
									set l_type = 204;       
                                ELSEIF 55 = v_game_type then
									set l_type = 205;       
                                ELSEIF 56 = v_game_type then
									set l_type = 206;
                                    
                                ELSEIF 57 = v_game_type then
									set l_type = 209;       
                                ELSEIF 58 = v_game_type then
									set l_type = 210;       
                                    
                                ELSEIF 59 = v_game_type then
									set l_type = 211;       
                                ELSEIF 60 = v_game_type then
									set l_type = 212;       
                                ELSEIF 61 = v_game_type then
									set l_type = 213;      
                                    
                                ELSEIF 62 = v_game_type then
									set l_type = 217;       
                                    
                                ELSEIF 63 = v_game_type then
									set l_type = 214;       
                                ELSEIF 64 = v_game_type then
									set l_type = 215;       
                                ELSEIF 65 = v_game_type then
									set l_type = 216;  
                                    
                                ELSEIF 66 = v_game_type then
									set l_type = 102;     
                                    
                                ELSEIF 67 = v_game_type then
									set l_type = 104;       
                                ELSEIF 68 = v_game_type then
									set l_type = 103;
                                    
                                ELSEIF 69 = v_game_type then
									set l_type = 109;
                                    
                                ELSEIF 70 = v_game_type then
									set l_type = 106;
                                    
							    ELSEIF 71 = v_game_type then
									set l_type = 107; 
                                    
							    ELSEIF 72 = v_game_type then
									set l_type = 13;
                                ELSEIF 73 = v_game_type then
									set l_type = 3;           
                                end if;
                                
                                IF 'ON' = v_status then
									set v_status = 'OFF';
                                else
									set v_status = 'ON';
                                end if;
                                
                             
                                -- SELECT l_type,l_idx;
								select name into l_name from static_game_betting_type_mgr where id = l_type;
								insert into member_game_betting_type_info(id,member_idx,name,status) value(l_type,l_idx,l_name,v_status);
                                
							END LOOP bet_read_loop;
					    CLOSE openCursor_bet;
                   END main_2;
                   
                   -- 모니터링 정보
                   insert into member_status_security(member_idx,type,is_monitor)
					values(l_idx,1,v_is_monitor)
                    ,(l_idx,2,v_is_monitor_charge)
                    ,(l_idx,3,v_is_monitor_security)
                    ,(l_idx,4,v_is_monitor_bet);
                   -- 개인개좌
                   
                    IF EXISTS(SELECT * FROM mydb_nova.personal_account_number where member_idx = v_idx) THEN
						insert into personal_account_number(member_idx,account_number_1,account_name_1,account_bank_id_1
						,account_number_2,account_name_2,account_bank_id_2) 
						SELECT l_idx
						,mydb_nova.personal_account_number.account_number_1 ,mydb_nova.personal_account_number.account_name_1, sa_1.bank_id
						,mydb_nova.personal_account_number.account_number_2 ,mydb_nova.personal_account_number.account_name_2, sa_2.bank_id
						FROM mydb_nova.personal_account_number 
						left join static_account as sa_1 on sa_1.account_code = mydb_nova.personal_account_number.account_bank_1
						left join static_account as sa_2 on sa_2.account_code = mydb_nova.personal_account_number.account_bank_2
						where member_idx = v_idx;
                    END IF;
                    -- 종합정보 이관 
                    main_total_info : BEGIN
						declare v_charge_total_count int;
                        declare v_charge_total_money bigint;
                        declare v_exchange_total_count int;
                        declare v_exchange_total_money bigint;
                        declare v_bet_total_count int; 
						declare	v_bet_total_money bigint;
                        declare v_pre_s_total_money bigint;
						declare v_pre_m_total_money bigint;
						declare v_real_s_total_money bigint;
						declare v_real_m_total_money bigint; 
						declare v_mini_total_money bigint; 
						declare v_casino_total_money bigint; 
						declare v_slot_total_money bigint; 
						declare v_take_total_money bigint; 
						declare v_take_slot_total_money bigint ;
						declare v_take_casino_total_money bigint; 
						declare v_take_mini_total_money bigint; 
						declare v_take_real_m_total_money bigint; 
						declare v_take_real_s_total_money bigint; 
						declare v_take_pre_m_total_money bigint; 
						declare v_take_pre_s_total_money bigint;
                        declare v_vir_total_money bigint; 
						declare v_take_vir_total_money bigint;
						IF EXISTS(SELECT * from mydb_nova.total_member_cash where member_idx = v_idx) THEN
							select charge_total_count,charge_total_money ,exchange_total_count,exchange_total_money
							,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,casino_total_money
							,slot_total_money,take_total_money,take_slot_total_money,take_casino_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,vir_total_money,take_vir_total_money
							into 
							v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money
							,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_casino_total_money
							,v_slot_total_money,v_take_total_money,v_take_slot_total_money,v_take_casino_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_vir_total_money,v_take_vir_total_money
							from mydb_nova.total_member_cash where member_idx = v_idx;
							
							insert into statistic_member_comprehensive_details(member_idx,total_charge,total_exchange,total_charge_count,total_exchange_count
							,total_bet_count,total_bet,total_win_bet,total_one_prematch_bet,total_one_prematch_win_bet
							,total_multi_prematch_bet ,total_multi_prematch_win_bet,total_one_inplay_bet ,total_one_inplay_win_bet 
							,total_multi_inplay_bet ,total_multi_inplay_win_bet,total_casino_bet ,total_casino_win_bet
							,total_slot_bet ,total_slot_win_bet ) 
							value(l_idx,v_charge_total_money,v_exchange_total_money,v_charge_total_count,v_exchange_total_count
							,v_bet_total_count,v_bet_total_money,v_take_total_money,v_pre_s_total_money,v_take_pre_s_total_money
							,v_pre_m_total_money,v_take_pre_m_total_money,v_real_s_total_money,v_take_real_s_total_money
							,v_real_m_total_money,v_take_real_m_total_money,v_casino_total_money,v_take_casino_total_money
							,v_slot_total_money,v_take_slot_total_money);
                        END IF;
                        
                    END main_total_info;
                   
                  -- SELECT 'end';
			  END LOOP mb_read_loop;
        CLOSE openCursor_mb;
        select 'end main_member_bunker';
        END main_member_bunker;
        
        
        main_recommend : BEGIN
		 
            declare v_member_id varchar(45);
            declare v_recommend_id varchar(45);
            
			declare l_member_idx bigint;
            declare l_recommend_idx bigint;
            
			DECLARE recommend_done INTEGER DEFAULT 0;
						
			DEClARE openCursor_recommend CURSOR FOR 
			SELECT mb.idx,mb.id,mb.temp_recommend_id as recommend_id
			FROM member as mb
            left join member as rm on mb.temp_recommend_id = rm.id
			WHERE rm.partner_type = 10001 and (mb.temp_recommend_id is not null and mb.temp_recommend_id <> '') and mb.partner_type = 10001 and mb.head_office_idx = h_idx;
                        
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET recommend_done = true;
            
            select 'start main_recommend';
            OPEN openCursor_recommend;
			recommend_loop: LOOP
				FETCH  openCursor_recommend INTO  l_member_idx,v_member_id,v_recommend_id;
									  
				IF recommend_done THEN
					LEAVE recommend_loop;
				END IF;
                
                IF  NOT EXISTS(select idx from member where id = v_recommend_id and head_office_idx = h_idx) then
                    ITERATE recommend_loop;
                END IF;
                
                select idx into l_recommend_idx from member where id = v_recommend_id and head_office_idx = h_idx;
                          
                update member set recommend_idx =  l_recommend_idx where idx = l_member_idx;
			END LOOP recommend_loop;
			CLOSE openCursor_recommend;
        select 'end main_recommend';           
        END  main_recommend;
    
      
	COMMIT;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `modify_recommend_wow` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `modify_recommend_wow`(
in_head_office_id varchar(45)
)
main : BEGIN
	DECLARE ret_code INT;
	DECLARE errno INT;
	DECLARE v_txt_error_msg text;
	declare h_idx bigint;
        
    START TRANSACTION;
		select member.idx
		into h_idx
		from member 
			left join partner_detail_info on member.idx = partner_detail_info.partner_idx
        where id = in_head_office_id;
        
        update member set recommend_idx = 0 where head_office_idx = h_idx;
        main_recommend : BEGIN
		 
            declare v_member_id varchar(45);
            declare v_recommend_id varchar(45);
            
			declare l_member_idx bigint;
            declare l_recommend_idx bigint;
            
			DECLARE recommend_done INTEGER DEFAULT 0;
						
			DEClARE openCursor_recommend CURSOR FOR 
			SELECT mb.idx,mb.id,mb.temp_recommend_id as recommend_id
			FROM member as mb
            left join member as rm on mb.temp_recommend_id = rm.id
			WHERE rm.partner_type = 10001 and (mb.temp_recommend_id is not null and mb.temp_recommend_id <> '') and mb.partner_type = 10001 and mb.head_office_idx = h_idx;
                        
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET recommend_done = true;
            
            select 'start main_recommend';
            OPEN openCursor_recommend;
			recommend_loop: LOOP
				FETCH  openCursor_recommend INTO  l_member_idx,v_member_id,v_recommend_id;
									  
				IF recommend_done THEN
					LEAVE recommend_loop;
				END IF;
                
				IF  NOT EXISTS(select idx from member where id = v_recommend_id and head_office_idx = h_idx) then
                    ITERATE recommend_loop;
                END IF;
                
                select idx into l_recommend_idx from member where id = v_recommend_id and head_office_idx = h_idx;
                
                update member set recommend_idx =  l_recommend_idx where idx = l_member_idx;
			END LOOP recommend_loop;
			CLOSE openCursor_recommend;
        select 'end main_recommend';           
        END  main_recommend;
        
	COMMIT;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SplitString` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `SplitString`(str VARCHAR(500), delim CHAR(1))
BEGIN
  DECLARE value VARCHAR(500);
  
  CREATE TEMPORARY TABLE temp_result (split_value VARCHAR(500));

  WHILE CHAR_LENGTH(str) > 0 DO
    SET value = SUBSTRING_INDEX(str, delim, 1);
    INSERT INTO temp_result (split_value) VALUES (value);
    SET str = TRIM(BOTH delim FROM SUBSTRING(str, CHAR_LENGTH(value) + 2));
  END WHILE;

  
  SELECT split_value FROM temp_result;

  
  DROP TEMPORARY TABLE temp_result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_fetch_lsports_leagues` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_fetch_lsports_leagues`(
	IN `p_head_office_idx` INT,
	IN `p_page` INT,
	IN `p_pageSize` INT,
	IN `p_sports_id` INT,
	IN `p_location_id` INT,
	IN `p_league_name` VARCHAR(100),
	IN `p_league_id` INT,
	IN `p_game_type` INT,
	IN `p_status` INT
)
BEGIN DECLARE offsetValue INT;

DECLARE totalRows INT;

DECLARE totalRegisteredLeagues INT;

DECLARE totalWaitLeagues INT;

DECLARE totalNewLeagues INT;

DECLARE totalUsedLeagues INT;


SET offsetValue = (p_page - 1) * p_pageSize;


SELECT DISTINCT COUNT(*) INTO totalRows
FROM lsports_leagues ll
LEFT JOIN lsports_sports ss ON ll.sports_id = ss.id
LEFT JOIN lsports_locations sl ON ll.location_id = sl.id
LEFT JOIN static_head_office_leagues shol ON ll.id = shol.id 

AND shol.head_office_idx = p_head_office_idx
AND shol.sports_id = ll.sports_id
AND shol.location_id = ll.location_id
AND shol.game_type = p_game_type 

WHERE shol.id IS NULL 

  AND (p_sports_id = 0
       OR ll.sports_id = p_sports_id)
  AND (p_location_id = 0
       OR ll.location_id = p_location_id)
  AND (p_league_id IS NULL
       OR ll.id = p_league_id)
  AND (p_league_name = ''
       OR LOWER(ll.name) LIKE LOWER(CONCAT('%', p_league_name, '%')))
  AND (p_status IS NULL
       OR ll.is_use = p_status);


SELECT COUNT(*) INTO totalRegisteredLeagues
FROM static_head_office_leagues shol
WHERE shol.head_office_idx = p_head_office_idx
  AND shol.game_type = p_game_type;


SELECT COUNT(*) INTO totalWaitLeagues
FROM lsports_leagues lp
WHERE NOT EXISTS
    (SELECT 1
     FROM static_head_office_leagues sh
     WHERE sh.id = lp.id
       AND sh.head_office_idx = p_head_office_idx
       AND sh.game_type = p_game_type )
  AND (p_sports_id = 0
       OR lp.sports_id = p_sports_id)
  AND (p_location_id = 0
       OR lp.location_id = p_location_id)
  AND (p_league_id IS NULL
       OR lp.id = p_league_id)
  AND (p_league_name = ''
       OR lp.name LIKE CONCAT('%', p_league_name, '%'))
  AND lp.is_use = 2;


SELECT COUNT(*) INTO totalUsedLeagues
FROM lsports_leagues lp
WHERE NOT EXISTS
    (SELECT 1
     FROM static_head_office_leagues sh
     WHERE sh.id = lp.id
       AND sh.head_office_idx = p_head_office_idx
       AND sh.game_type = p_game_type )
  AND (p_sports_id = 0
       OR lp.sports_id = p_sports_id)
  AND (p_location_id = 0
       OR lp.location_id = p_location_id)
  AND (p_league_id IS NULL
       OR lp.id = p_league_id)
  AND (p_league_name = ''
       OR lp.name LIKE CONCAT('%', p_league_name, '%'))
  AND lp.is_use = 1;


SELECT COUNT(*) INTO totalNewLeagues
FROM lsports_leagues lp
WHERE NOT EXISTS
    (SELECT 1
     FROM static_head_office_leagues sh
     WHERE sh.id = lp.id
       AND sh.head_office_idx = p_head_office_idx
       AND sh.game_type = p_game_type )
  AND (p_sports_id = 0
       OR lp.sports_id = p_sports_id)
  AND (p_location_id = 0
       OR lp.location_id = p_location_id)
  AND (p_league_id IS NULL
       OR lp.id = p_league_id)
  AND (p_league_name = ''
       OR lp.name LIKE CONCAT('%', p_league_name, '%'))
  AND lp.is_use = 0;


SELECT DISTINCT ll.*,
                ss.name AS sports_name,
                sl.name AS location_name,
                shol.image_path AS location_image_path
FROM lsports_leagues ll
LEFT JOIN lsports_sports ss ON ll.sports_id = ss.id
LEFT JOIN lsports_locations sl ON ll.location_id = sl.id
LEFT JOIN static_head_office_leagues shol ON ll.id = shol.id 

AND shol.head_office_idx = p_head_office_idx
AND shol.sports_id = ll.sports_id
AND shol.location_id = ll.location_id
AND shol.game_type = p_game_type 

WHERE shol.id IS NULL 

  AND (p_sports_id = 0
       OR ll.sports_id = p_sports_id)
  AND (p_location_id = 0
       OR ll.location_id = p_location_id)
  AND (p_league_id IS NULL
       OR ll.id = p_league_id)
  AND (p_league_name = ''
       OR LOWER(ll.name) LIKE LOWER(CONCAT('%', p_league_name, '%')))
  AND (p_status IS NULL
       OR ll.is_use = p_status)
LIMIT p_pageSize
OFFSET offsetValue;


SELECT totalRows AS totalRecords,
       totalRegisteredLeagues,
       totalWaitLeagues,
       totalUsedLeagues,
       totalNewLeagues;


SELECT shos.*,
       ls.name AS name,
       shos.display_name AS display_name
FROM main.static_head_office_sports shos
LEFT JOIN lsports_sports ls ON ls.id = shos.id
WHERE shos.id IN
    (SELECT shl.sports_id
     FROM main.static_head_office_leagues shl
     WHERE shl.head_office_idx = p_head_office_idx
       AND (p_game_type = 0
            OR shl.game_type = p_game_type) )
  AND shos.head_office_idx = p_head_office_idx
  AND (p_game_type = 0
       OR shos.game_type = p_game_type)
ORDER BY ls.id ASC;


SELECT DISTINCT sl.*
FROM lsports_locations sl
WHERE EXISTS
    (SELECT 1
     FROM lsports_leagues ll
     INNER JOIN static_head_office_leagues shol ON shol.location_id = ll.location_id
     WHERE shol.head_office_idx = p_head_office_idx
       AND shol.game_type = p_game_type
       AND ll.location_id = sl.id )
ORDER BY id ASC;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_fetch_lsports_participant` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_fetch_lsports_participant`(

	IN `p_head_office_idx` INT,

	IN `p_page` INT,

	IN `p_pageSize` INT,

	IN `p_sports_id` INT,

	IN `p_location_id` INT,

	IN `p_league_id` INT,

	IN `p_team_name` VARCHAR(100),

	IN `p_status` INT

)
BEGIN



DECLARE offsetValue INT;

DECLARE totalRows INT;

DECLARE totalNewParticipants INT;

DECLARE totalOldParticipants INT;

DECLARE totalRegisteredCount INT;  





SET offsetValue = (p_page - 1) * p_pageSize;





SELECT COUNT(*) INTO totalRows

FROM lsports_participant lp

WHERE NOT EXISTS (

    SELECT 1

    FROM static_head_office_participants sh 

    WHERE sh.fp_id = lp.fp_id 

      AND sh.head_office_idx = p_head_office_idx  

)

AND (p_sports_id = 0 OR lp.sports_id = p_sports_id)

AND (p_league_id = 0 OR lp.league_id = p_league_id)  

AND (p_location_id = 0 OR lp.location_id = p_location_id)  

AND (p_team_name IS NULL OR p_team_name = '' OR LOWER(lp.name) LIKE CONCAT('%', LOWER(p_team_name), '%'))

AND (p_status = lp.is_use);

  



SELECT COUNT(*) INTO totalNewParticipants

FROM lsports_participant lp

WHERE NOT EXISTS (

    SELECT 1

    FROM static_head_office_participants sh 

    WHERE sh.fp_id = lp.fp_id 

      AND sh.head_office_idx = p_head_office_idx  

)

AND (p_sports_id = 0 OR lp.sports_id = p_sports_id)

AND (p_league_id = 0 OR lp.league_id = p_league_id)  

AND (p_location_id = 0 OR lp.location_id = p_location_id)  

AND (p_team_name IS NULL OR p_team_name = '' OR LOWER(lp.name) LIKE CONCAT('%', LOWER(p_team_name), '%'))

AND lp.is_use = 1;  





SELECT COUNT(*) INTO totalOldParticipants

FROM lsports_participant lp

WHERE NOT EXISTS (

    SELECT 1

    FROM static_head_office_participants sh 

    WHERE sh.fp_id = lp.fp_id 

      AND sh.head_office_idx = p_head_office_idx  

)

AND (p_sports_id = 0 OR lp.sports_id = p_sports_id)

AND (p_league_id = 0 OR lp.league_id = p_league_id)  

AND (p_location_id = 0 OR lp.location_id = p_location_id)  

AND (p_team_name IS NULL OR p_team_name = '' OR LOWER(lp.name) LIKE CONCAT('%', LOWER(p_team_name), '%'))

AND lp.is_use = 0;  





SELECT COUNT(*) INTO totalRegisteredCount

FROM static_head_office_participants sh

WHERE sh.head_office_idx = p_head_office_idx;  



SELECT lp.*, ll.`name` AS league_name

FROM lsports_participant lp

LEFT JOIN lsports_leagues ll ON ll.id = lp.league_id

WHERE NOT EXISTS (

    SELECT 1

    FROM static_head_office_participants sh 

    WHERE sh.fp_id = lp.fp_id 

      AND sh.head_office_idx = p_head_office_idx  

)

AND (p_sports_id = 0 OR lp.sports_id = p_sports_id)

AND (p_league_id = 0 OR lp.league_id = p_league_id)  

AND (p_location_id = 0 OR lp.location_id = p_location_id)  

AND (p_team_name IS NULL OR p_team_name = '' OR LOWER(lp.name) LIKE CONCAT('%', LOWER(p_team_name), '%'))

AND (p_status = lp.is_use)  

ORDER BY lp.fp_id ASC

LIMIT p_pageSize

OFFSET offsetValue;



SELECT totalRows AS totalRecords, 

       totalNewParticipants AS newParticipantsCount, 

       totalOldParticipants AS oldParticipantsCount,

       totalRegisteredCount AS registeredCount;  





SELECT DISTINCT ls.*

FROM lsports_sports ls

JOIN (

    SELECT lp.sports_id

    FROM lsports_participant lp

    WHERE NOT EXISTS (

        SELECT 1

        FROM static_head_office_participants sh 

        WHERE sh.fp_id = lp.fp_id 

          AND sh.head_office_idx = p_head_office_idx

    ) AND EXISTS (

	 	SELECT 1 FROM static_head_office_participants sh WHERE sh.sports_id = lp.sports_id

	 )

) filtered_sports ON ls.id = filtered_sports.sports_id

ORDER BY ls.id ASC;





SELECT DISTINCT ll.*

FROM lsports_leagues ll

JOIN (

    SELECT lp.league_id

    FROM lsports_participant lp

    WHERE NOT EXISTS (

        SELECT 1

        FROM static_head_office_participants sh 

        WHERE sh.fp_id = lp.fp_id 

          AND sh.head_office_idx = p_head_office_idx

    )

) filtered_leagues ON ll.id = filtered_leagues.league_id

WHERE (p_sports_id = 0 OR ll.sports_id = p_sports_id)

ORDER BY ll.id ASC;





SELECT DISTINCT ll.*

FROM lsports_locations ll

JOIN (

    SELECT lp.location_id

    FROM lsports_participant lp

    WHERE NOT EXISTS (

        SELECT 1

        FROM static_head_office_participants sh 

        WHERE sh.fp_id = lp.fp_id 

          AND sh.head_office_idx = p_head_office_idx  

    )

) filtered_locations ON ll.id = filtered_locations.location_id

ORDER BY ll.id ASC;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_fetch_static_head_office_leagues` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_fetch_static_head_office_leagues`(
	IN `p_head_office_idx` INT,
	IN `p_page` INT,
	IN `p_pageSize` INT,
	IN `p_sports_id` INT,
	IN `p_region_id` INT,
	IN `p_league_name` VARCHAR(100),
	IN `p_league_id` INT,
	IN `p_grade` INT,
	IN `p_game_type` INT
)
BEGIN DECLARE offsetValue INT; DECLARE totalRows INT;
SET
	offsetValue = (p_page - 1) * p_pageSize;
SELECT
	COUNT(DISTINCT shl.id) INTO totalRows
FROM
	main.static_head_office_leagues shl
	LEFT JOIN lsports_leagues lgl ON shl.id = lgl.id
WHERE
	shl.head_office_idx = p_head_office_idx
	AND (
		p_game_type = 0
		OR shl.game_type = p_game_type
	)
	AND (
		p_sports_id = 0
		OR shl.sports_id = p_sports_id
	)
	AND (
		p_region_id = 0
		OR shl.location_id = p_region_id
	)
	AND (
		p_league_id IS NULL
		OR shl.id = p_league_id
	)
	AND (
		p_grade = 0
		OR shl.dividend_rank = p_grade
	)
	AND (
		p_league_name = ''
		OR LOWER(shl.display_name) LIKE LOWER(
			CONCAT('%', p_league_name, '%')
		)
		OR LOWER(lgl.name) LIKE LOWER(
			CONCAT('%', p_league_name, '%')
		)
	);
SELECT
	shl.id,
	shl.*,
	shl.display_name AS display_league_name,
	shos.display_name AS display_sports_name,
	ll.name AS original_location_name,
	shl.image_path AS original_location_image_path,
	lgl.name AS original_league_name,
	ls.name AS original_sports_name
FROM
	main.static_head_office_leagues shl
	LEFT JOIN lsports_locations ll ON shl.location_id = ll.id
	LEFT JOIN lsports_leagues lgl ON shl.id = lgl.id
	LEFT JOIN lsports_sports ls ON shl.sports_id = ls.id
	LEFT JOIN static_head_office_sports shos ON shl.sports_id = shos.id
	AND shos.head_office_idx = p_head_office_idx
	AND (
		p_game_type = 0
		OR shos.game_type = p_game_type
	)
WHERE
	shl.head_office_idx = p_head_office_idx
	AND (
		p_game_type = 0
		OR shl.game_type = p_game_type
	)
	AND (
		p_sports_id = 0
		OR shl.sports_id = p_sports_id
	)
	AND (
		p_region_id = 0
		OR shl.location_id = p_region_id
	)
	AND (
		p_league_id IS NULL
		OR shl.id = p_league_id
	)
	AND (
		p_grade = 0
		OR shl.dividend_rank = p_grade
	)
	AND (
		p_league_name = ''
		OR LOWER(shl.display_name) LIKE LOWER(
			CONCAT('%', p_league_name, '%')
		)
		OR LOWER(lgl.name) LIKE LOWER(
			CONCAT('%', p_league_name, '%')
		)
	)
LIMIT
	offsetValue,
	p_pageSize;
SELECT
	totalRows AS totalRecords;
SELECT
	shos.*,
	ls.name AS NAME,
	shos.display_name AS display_name
FROM
	main.static_head_office_sports shos
	LEFT JOIN lsports_sports ls ON ls.id = shos.id
WHERE
	shos.id IN (
		SELECT
			shl.sports_id
		FROM
			main.static_head_office_leagues shl
		WHERE
			shl.head_office_idx = p_head_office_idx
			AND (
				p_game_type = 0
				OR shl.game_type = p_game_type
			)
			AND (
				p_grade = 0
				OR shl.dividend_rank = p_grade
			)
	)
	AND shos.head_office_idx = p_head_office_idx
	AND (
		p_game_type = 0
		OR shos.game_type = p_game_type
	)
ORDER BY
	ls.id ASC;
SELECT
	DISTINCT 
		ll.*,
		ll.`name` AS display_name
FROM
	lsports_locations ll
WHERE
	ll.id IN (
		SELECT
			DISTINCT shl.location_id
		FROM
			main.static_head_office_leagues shl
		WHERE
			shl.head_office_idx = p_head_office_idx
			AND (
				p_game_type = 0
				OR shl.game_type = p_game_type
			)
			AND (
				p_grade = 0
				OR shl.dividend_rank = p_grade
			)
			AND (
				p_league_id IS NULL
				OR shl.id = p_league_id
			)
	); END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_fetch_static_head_office_participants` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_fetch_static_head_office_participants`(
	IN `p_head_office_idx` INT,
	IN `p_page` INT,
	IN `p_pageSize` INT,
	IN `p_sports_id` INT,
	IN `p_region_id` INT,
	IN `p_league_id` INT,
	IN `p_team_name` VARCHAR(100)
)
BEGIN DECLARE offsetValue INT; DECLARE totalRows INT;
SET
	offsetValue = (p_page - 1) * p_pageSize;
SELECT
	COUNT(DISTINCT shp.fp_id) INTO totalRows
FROM
	main.static_head_office_participants shp
	LEFT JOIN lsports_participant lp ON shp.fp_id = lp.fp_id
WHERE
	shp.head_office_idx = p_head_office_idx
	AND (
		p_sports_id = 0
		OR shp.sports_id = p_sports_id
	)
	AND (
		p_region_id = 0
		OR shp.location_id = p_region_id
	)
	AND (
		p_league_id = 0
		OR shp.league_id = p_league_id
	)
	AND (
		p_team_name = ''
		OR LOWER(shp.display_name) LIKE LOWER(
			CONCAT('%', p_team_name, '%')
		)
		OR LOWER(lp.`name`) LIKE LOWER(
			CONCAT('%', p_team_name, '%')
		)
	);
SELECT
	DISTINCT shp.fp_id,
	shp.display_name,
	shp.sports_id,
	shp.league_id,
	shp.location_id,
	lp.name,
	ll.name AS location_name,
	lgl.name AS league_name,
	ls.name AS sports_name,
	shp.image_path AS image_path
FROM
	main.static_head_office_participants shp
	LEFT JOIN lsports_participant lp ON shp.fp_id = lp.fp_id
	LEFT JOIN lsports_locations ll ON shp.location_id = ll.id
	LEFT JOIN lsports_leagues lgl ON shp.league_id = lgl.id
	LEFT JOIN lsports_sports ls ON shp.sports_id = ls.id
WHERE
	shp.head_office_idx = p_head_office_idx
	AND (
		p_sports_id = 0
		OR shp.sports_id = p_sports_id
	)
	AND (
		p_region_id = 0
		OR shp.location_id = p_region_id
	)
	AND (
		p_league_id = 0
		OR shp.league_id = p_league_id
	)
	AND (
		p_team_name = ''
		OR LOWER(shp.display_name) LIKE LOWER(
			CONCAT('%', p_team_name, '%')
		)
		OR LOWER(lp.`name`) LIKE LOWER(
			CONCAT('%', p_team_name, '%')
		)
	)
LIMIT
	offsetValue,
	p_pageSize;
SELECT
	totalRows AS totalRecords;
SELECT
	shos.*,
	ls.name AS sports_name,
	shos.display_name AS display_name
FROM
	main.static_head_office_sports shos
	LEFT JOIN lsports_sports ls ON ls.id = shos.id
WHERE
	shos.id IN (
		SELECT
			shp.sports_id
		FROM
			main.static_head_office_participants shp
		WHERE
			shp.head_office_idx = shos.head_office_idx
	)
	AND shos.head_office_idx = p_head_office_idx
	AND shos.game_type = 1
ORDER BY
	id ASC;
SELECT
	DISTINCT shol.*
FROM
	static_head_office_leagues shol
WHERE
	shol.id IN (
		SELECT
			DISTINCT shp.league_id
		FROM
			main.static_head_office_participants shp
		WHERE
			shol.id = shp.league_id
	)
	AND (
		p_sports_id = 0
		OR shol.sports_id = p_sports_id
	)
	AND shol.head_office_idx = p_head_office_idx
	AND shol.game_type = 1;
SELECT
	DISTINCT 
		ll.*,
		ll.`name` AS display_name
FROM
	lsports_locations ll
WHERE
	ll.id IN (
		SELECT
			DISTINCT shp.location_id
		FROM
			main.static_head_office_participants shp
		WHERE
			shp.head_office_idx = p_head_office_idx
	); END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_insert_update_partnerheaderSound` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_insert_update_partnerheaderSound`(
	IN `partnerIdx` INT,
	IN `header_table_name` VARCHAR(120),
	IN `sound_state` JSON
)
BEGIN
	DECLARE existing_count INT;

    
    SELECT COUNT(*) INTO existing_count
    FROM tt_header_sound
    WHERE partner_idx = partnerIdx;

    
    IF existing_count = 0 THEN
        INSERT INTO tt_header_sound (partner_idx, `key`, value)
        VALUES (partnerIdx, header_table_name, sound_state);
    ELSE
        
        UPDATE tt_header_sound
        SET value = sound_state
        WHERE partner_idx = partnerIdx;
    END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_marketing_insert_update_partnerheaderSound` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_marketing_insert_update_partnerheaderSound`(
	IN `p_id` VARCHAR(50),
	IN `header_table_name` VARCHAR(120),
	IN `sound_state` JSON
)
BEGIN
	DECLARE existing_count INT;

    
    SELECT COUNT(*) INTO existing_count
    FROM marketing_tt_header_sound
    WHERE id = p_id;

    
    IF existing_count = 0 THEN
        INSERT INTO marketing_tt_header_sound (id, `key`, value)
        VALUES (p_id, header_table_name, sound_state);
    ELSE
        
        UPDATE marketing_tt_header_sound
        SET value = sound_state
        WHERE id = p_id;
    END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_member_message_sending` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_member_message_sending`(
	IN `headOfficeIdx` INT,
	IN `memberIdx` VARCHAR(500),
	IN `shopLevel` INT,
	IN `titleData` VARCHAR(1000),
	IN `detailData` VARCHAR(10000),
	IN `templateType` VARCHAR(100),
	IN `messageType` VARCHAR(100),
	IN `parentIdx` INT
)
BEGIN

	
	IF messageType = 'message selection' OR messageType = 'message to all' THEN
		
			IF shopLevel = 0 THEN
			
			
			IF memberIdx != 0 THEN
					INSERT INTO member_message_sending (
					    member_idx, head_office_idx, parent_partner_idx, shop_level, title, detail, 
					    message_type, read_status, delete_status, date_created
							)
					
						SELECT 
							mem.idx AS member_idx, 
							mem.head_office_idx AS head_office_idx,
							mem.parent_partner_idx,
							mem.level AS shop_level, 
							titleData AS title, 
							detailData AS detail, 
							messageType AS message_type, 
							0 as read_status, 
							0 AS delete_status, 
							NOW() AS date_created  
							
							FROM member AS mem 
							WHERE mem.head_office_idx = headOfficeIdx AND mem.partner_type = 10001 
							AND STATUS = 1 AND mem.idx IN (SELECT temp.value FROM JSON_TABLE(
						    CONCAT('["', REPLACE(memberIdx, ',', '","'), '"]'),
						    '$[*]' COLUMNS (value VARCHAR(500) PATH '$')
						) AS temp);
			ELSE 
				IF parentIdx = 0 THEN 
					
					INSERT INTO member_message_sending (
				    member_idx, head_office_idx, parent_partner_idx, shop_level, title, detail, 
				    message_type, read_status, delete_status, date_created
						)
					SELECT 
						mem.idx AS member_idx, 
						mem.head_office_idx AS head_office_idx,
						mem.parent_partner_idx,
						mem.level AS shop_level, 
						titleData AS title, 
						detailData AS detail, 
						messageType AS message_type, 
						0 as read_status, 
						0 AS delete_status, 
						NOW() AS date_created  
						
						FROM member AS mem 
						WHERE mem.head_office_idx = headOfficeIdx AND mem.partner_type = 10001 AND STATUS = 1;
				ELSE 
					
					INSERT INTO member_message_sending (
				    member_idx, head_office_idx, parent_partner_idx, shop_level, title, detail, 
				    message_type, read_status, delete_status, date_created
						)
					SELECT 
						mem.idx AS member_idx, 
						mem.head_office_idx AS head_office_idx,
						mem.parent_partner_idx,
						mem.level AS shop_level, 
						titleData AS title, 
						detailData AS detail, 
						messageType AS message_type, 
						0 as read_status, 
						0 AS delete_status, 
						NOW() AS date_created  
						
						FROM member AS mem 
						WHERE mem.head_office_idx = headOfficeIdx AND mem.partner_type = 10001 AND STATUS = 1 AND mem.parent_partner_idx = parentIdx;
				END IF;
			END IF;
				
			
			ELSE
		
				IF memberIdx = 0 THEN
						IF parentIdx = 0 THEN
							
						
							INSERT INTO member_message_sending (
					    member_idx, head_office_idx,parent_partner_idx, shop_level, title, detail, 
					    message_type, read_status, delete_status, date_created
							)
					
						SELECT 
							mem.idx AS member_idx, 
							mem.head_office_idx AS head_office_idx,
							mem.parent_partner_idx,
							mem.level AS shop_level, 
							titleData AS title, 
							detailData AS detail, 
							messageType AS message_type, 
							0 as read_status, 
							0 AS delete_status, 
							NOW() AS date_created  
							
							FROM member AS mem 
							WHERE mem.head_office_idx = headOfficeIdx AND mem.partner_type = 10001 
							AND mem.level = shopLevel AND STATUS = 1;
							
							
						ELSE 
							
						
							INSERT INTO member_message_sending (
					    member_idx, head_office_idx,parent_partner_idx, shop_level, title, detail, 
					    message_type, read_status, delete_status, date_created
							)
						
						SELECT 
							mem.idx AS member_idx, 
							mem.head_office_idx AS head_office_idx,
							mem.parent_partner_idx,
							mem.level AS shop_level, 
							titleData AS title, 
							detailData AS detail, 
							messageType AS message_type, 
							0 as read_status, 
							0 AS delete_status, 
							NOW() AS date_created  
							
							FROM member AS mem 
							WHERE mem.head_office_idx = headOfficeIdx AND mem.partner_type = 10001 
							AND mem.level = shopLevel AND STATUS = 1 AND mem.parent_partner_idx = parentIdx;
						END IF;				
				ELSE 
						
				IF CHAR_LENGTH(TRIM(memberIdx)) >= 1 THEN
						
						
							INSERT INTO member_message_sending (
					    member_idx, head_office_idx, parent_partner_idx, shop_level, title, detail, 
					    message_type, read_status, delete_status, date_created
							)
					
						SELECT 
							mem.idx AS member_idx, 
							mem.head_office_idx AS head_office_idx,
							mem.parent_partner_idx,
							mem.level AS shop_level, 
							titleData AS title, 
							detailData AS detail, 
							messageType AS message_type, 
							0 as read_status, 
							0 AS delete_status, 
							NOW() AS date_created  
							
							FROM member AS mem 
							WHERE mem.head_office_idx = headOfficeIdx AND mem.partner_type = 10001 
							AND mem.level = shopLevel AND STATUS = 1 AND mem.idx IN (SELECT temp.value FROM JSON_TABLE(
						    CONCAT('["', REPLACE(memberIdx, ',', '","'), '"]'),
						    '$[*]' COLUMNS (value VARCHAR(500) PATH '$')
						) AS temp);
							
				ELSE
						
						INSERT INTO member_message_sending (
				    member_idx, head_office_idx, parent_partner_idx, shop_level, title, detail, 
				    message_type, read_status, delete_status, date_created
						)
					
						SELECT 
						    value AS memberIdx, 
						    mem.head_office_idx AS head_office_idx,
						    mem.parent_partner_idx,
						    mem.level AS shop_level, 
						    titleData AS title, 
						    detailData AS detail, 
						    messageType AS message_type, 
						    0 AS read_status, 
						    0 AS delete_status, 
						    NOW() AS date_created
						FROM JSON_TABLE(
						    CONCAT('["', REPLACE(memberIdx, ',', '","'), '"]'),
						    '$[*]' COLUMNS (value VARCHAR(500) PATH '$')
						) AS temp
						INNER JOIN member AS mem ON mem.idx = temp.value
						AND mem.level = shopLevel AND mem.partner_type = 10001;
				END IF;
					END IF;
	END IF;	
	
	
	ELSE IF messageType = 'recommended' THEN
		
		
		IF shopLevel = 0 THEN
			INSERT INTO member_message_sending (
			head_office_idx, 
			parent_partner_idx, 
			member_idx, 
			shop_level,
		   title, 
			detail, 
			message_type, 
			read_status, 
			delete_status,
			date_created)
			
			SELECT head_office_idx, parent_partner_idx, idx AS member_idx, 
			LEVEL AS shop_level, titleData AS title, detailData AS detail, templateType AS message_type, 0, 0, NOW() FROM member
			WHERE partner_type = 10001 AND is_recommend = 'Y' AND status = 1
			AND head_office_idx = headOfficeIdx;
		
		
		ELSE 
			INSERT INTO member_message_sending (
			head_office_idx, 
			parent_partner_idx, 
			member_idx, 
			shop_level,
			title, 
			detail, 
			message_type, 
			read_status, 
			delete_status,
			date_created)
			
		SELECT head_office_idx, parent_partner_idx, idx AS member_idx, 
		LEVEL AS shop_level, titleData AS title, detailData AS detail, templateType AS message_type, 0, 0, NOW() FROM member
		WHERE partner_type = 10001 AND is_recommend = 'Y' AND status = 1
		AND LEVEL = shopLevel 
		AND head_office_idx = headOfficeIdx;
		
		END IF;
		
		
		
	ELSE 
			INSERT INTO member_message_sending (
			head_office_idx, 
			parent_partner_idx, 
			member_idx, 
			shop_level,
			title, 
			detail, 
			message_type, 
			read_status, 
			delete_status,
			date_created)
			
		SELECT head_office_idx, parent_partner_idx, idx AS member_idx, mem.level, titleData AS title, detailData AS detail, messageType AS message_type, 0 AS read_status , 0 AS delete_status, NOW() AS date_created
		 FROM member AS mem WHERE FIND_IN_SET(idx, REPLACE(memberIdx, ' ', '')) > 0 AND head_office_idx = headOfficeIdx;
	END IF;	
	
	END IF;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_member_message_sending_history_list` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_member_message_sending_history_list`(

	IN `headOfficeIdx` INT,

	IN `dateFrom` VARCHAR(50),

	IN `dateTo` VARCHAR(50),

	IN `shopLevel` INT,

	IN `searchKey` VARCHAR(250),

	IN `searchString` VARCHAR(250),

	IN `orderByKey` VARCHAR(20),

	IN `orderByType` VARCHAR(20),

	IN `currentPage` INT,

	IN `pageSize` INT,

	IN `searchParams` JSON

)
BEGIN

    SET @totalPage = 0,

        @limitFrom = IF(currentPage <= 0, 0, IF(currentPage = 1, pageSize, (pageSize * currentPage))),

        @limitTo = pageSize, 

        @totalRows = 0;



    CREATE TEMPORARY TABLE IF NOT EXISTS temp_count_table (total_count INT);



    -- Base Query Construction

    /**

    SET @baseQuery = CONCAT('SELECT COUNT(*) INTO @totalRows 

        FROM member_message_sending AS mms 

        INNER JOIN member AS mbr ON mbr.idx = mms.member_idx 

        INNER JOIN member AS prnt ON mbr.parent_partner_idx = prnt.idx

        INNER JOIN main.member_bank AS membank ON mms.member_idx = membank.member_idx

        WHERE mms.date_created BETWEEN CAST("', dateFrom, '" AS DATETIME) 

        AND CAST("', dateTo, '" AS DATETIME)

        AND ', searchKey, ' LIKE "%', searchString, '%"

        AND mms.head_office_idx = ', headOfficeIdx);

		  

		 */

      

      SET @baseQuery = CONCAT('SELECT COUNT(*) INTO @totalRows 

	    FROM member_message_sending AS mms 

	    INNER JOIN member AS mbr ON mbr.idx = mms.member_idx 

	    INNER JOIN member AS prnt ON mbr.parent_partner_idx = prnt.idx

	    INNER JOIN main.member_bank AS membank ON mms.member_idx = membank.member_idx

	    WHERE mms.date_created BETWEEN CAST("', dateFrom, '" AS DATETIME) 

	    AND CAST("', dateTo, '" AS DATETIME)

	    AND (mbr.id LIKE "%', searchString, '%" OR mbr.nick_name LIKE "%', searchString, '%")

	    AND mms.head_office_idx = ', headOfficeIdx);

      



    -- Apply shopLevel filter only if it is greater than 0

    IF shopLevel > 0 THEN

        SET @baseQuery = CONCAT(@baseQuery, ' AND mms.shop_level = ', shopLevel);

    END IF;



    -- Apply additional dynamic filtering if provided

    SET @dynamicWhereClause = fn_generate_where_clause(searchParams);

    IF @dynamicWhereClause != '' THEN

        SET @baseQuery = CONCAT(@baseQuery, ' AND ', @dynamicWhereClause);

    END IF;



    SET @baseQuery = CONCAT(@baseQuery, ' ORDER BY mms.date_created DESC');



    -- Query for fetching data

    /** 

    SET @query = CONCAT(

        'SELECT 

            mms.message_idx, 

            mms.member_idx, 

            mms.shop_level, 

            mms.title, 

            mms.detail, 

            mms.date_created, 

            mms.read_status, 

            mms.delete_status,

            mbr.id AS member_id,

            mbr.nick_name AS member_nickname,

            mbr.partner_type AS member_type,

            prnt.idx AS pt_idx,

            prnt.id AS pt_id,

            prnt.nick_name AS pt_nickname,

            prnt.partner_type AS pt_type

        FROM 

            member_message_sending AS mms

        INNER JOIN 

            member AS mbr ON mbr.idx = mms.member_idx

        INNER JOIN 

            member AS prnt ON mbr.parent_partner_idx = prnt.idx

        INNER JOIN 

            main.member_bank AS membank ON mms.member_idx = membank.member_idx

        WHERE 

            mms.date_created BETWEEN CAST("', dateFrom, '" AS DATETIME) 

            AND CAST("', dateTo, '" AS DATETIME) 

            AND ', searchKey, ' LIKE "%', searchString, '%"

            AND mms.head_office_idx = ', headOfficeIdx);

            

   	*/

   	

   	SET @query = CONCAT(

	    'SELECT 

	        mms.message_idx, 

	        mms.member_idx, 

	        mms.shop_level, 

	        mms.title, 

	        mms.detail, 

	        mms.date_created, 

	        mms.read_status, 

	        mms.delete_status,

	        mbr.id AS member_id,

	        mbr.nick_name AS member_nickname,

	        mbr.partner_type AS member_type,

	        prnt.idx AS pt_idx,

	        prnt.id AS pt_id,

	        prnt.nick_name AS pt_nickname,

	        prnt.partner_type AS pt_type

	    FROM 

	        member_message_sending AS mms

	    INNER JOIN 

	        member AS mbr ON mbr.idx = mms.member_idx

	    INNER JOIN 

	        member AS prnt ON mbr.parent_partner_idx = prnt.idx

	    INNER JOIN 

	        main.member_bank AS membank ON mms.member_idx = membank.member_idx

	    WHERE 

	        mms.date_created BETWEEN CAST("', dateFrom, '" AS DATETIME) 

	        AND CAST("', dateTo, '" AS DATETIME) 

	        AND (mbr.id LIKE "%', searchString, '%" OR mbr.nick_name LIKE "%', searchString, '%")

	        AND mms.head_office_idx = ', headOfficeIdx);



    -- Apply shopLevel filter only if it is greater than 0

    IF shopLevel > 0 THEN

        SET @query = CONCAT(@query, ' AND mms.shop_level = ', shopLevel);

    END IF;



    -- Apply additional dynamic filtering if provided

    IF @dynamicWhereClause != '' THEN

        SET @query = CONCAT(@query, ' AND ', @dynamicWhereClause);

    END IF;



    -- Add ordering and pagination

    SET @query = CONCAT(@query, ' ORDER BY ', IFNULL(orderByKey, 'mms.date_created'), ' ', IFNULL(orderByType, 'DESC'), 

        ' LIMIT ', @limitFrom, ', ', @limitTo);



    -- Execute main query

    PREPARE stmt FROM @query;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;



    -- Execute count query

    PREPARE totalStmt FROM @baseQuery;

    EXECUTE totalStmt;

    INSERT INTO temp_count_table (total_count) VALUES (@totalRows);

    SELECT * INTO @totalPage FROM temp_count_table;

    DROP TEMPORARY TABLE IF EXISTS temp_count_table;

    SELECT CEIL(@totalPage / pageSize) AS total_page;

    DEALLOCATE PREPARE totalStmt;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_member_message_sending_update_delete_status` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_member_message_sending_update_delete_status`(
	IN `memberIdx` INT,
	IN `messageIdx` INT
)
BEGIN
	
	IF messageIdx = 0 THEN
		UPDATE member_message_sending SET delete_status = 1 WHERE member_idx = memberIdx;
	
	ELSE 
		UPDATE member_message_sending SET delete_status = 1 WHERE member_idx = memberIdx AND message_idx = messageIdx;
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_member_message_sending_update_read_status` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_member_message_sending_update_read_status`(
	IN `memberIdx` INT,
	IN `messageIdx` INT
)
BEGIN

	IF messageIdx = 0 THEN
		UPDATE member_message_sending SET read_status = 1, date_read = NOW() WHERE member_idx = memberIdx;
	
	ELSE 
		UPDATE member_message_sending SET read_status = 1, date_read = NOW()
		WHERE member_idx = memberIdx AND message_idx = messageIdx;
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_mini_game_by_head_office` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_mini_game_by_head_office`(
	IN `p_headoffice_idx` INT,
	IN `p_sdate` VARCHAR(50),
	IN `p_edate` VARCHAR(50),
	IN `p_cnt` INT,
	IN `p_id` INT,
	IN `p_sub_type` INT,
	IN `p_third_type` INT,
	IN `p_pkey` INT,
	IN `p_pageSize` INT,
	IN `p_currentPage` INT,
	IN `p_return_total` INT,
	IN `p_order_by_sdate` INT,
	IN `p_return_old_result` INT,
	IN `p_uid` INT,
	IN `p_return_changed` INT
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_totalCount INT;
    DECLARE v_totalPages INT;

    SET v_offset = (p_currentPage - 1) * p_pageSize;

    
    SELECT * FROM (
        
        SELECT 
            mgph.uid,
            mgph.pkey,
            mgph.id,
            mgph.game,
            mgph.cnt,
            mgph.sub_type,
            mgph.third_type,
            mgph.sdate AS sdate,  
            mgph.edate,
            mgph.league,
            mgph.result,
            mgph.is_onoff
        FROM 
            main.mini_game_per_head_office mgph
        WHERE 
            mgph.headoffice_idx = p_headoffice_idx
            AND (p_sdate = ' ' OR ( DATE(mgph.sdate) BETWEEN DATE(p_sdate) AND DATE(p_edate)))
            AND (p_cnt = -1 OR (mgph.cnt = p_cnt AND p_cnt <> -1))
            AND (p_id = -1 OR (mgph.id = p_id AND p_id <> -1))
            AND (p_sub_type = -1 OR (mgph.sub_type = p_sub_type AND p_sub_type <> -1))
            AND (p_third_type = -1 OR (mgph.third_type = p_third_type AND p_third_type <> -1))
            AND (p_pkey = -1 OR (mgph.pkey = p_pkey AND p_pkey <> -1))
            AND NOT (mgph.sub_type = 1 AND JSON_UNQUOTE(JSON_EXTRACT(mgph.result, '$.type')) = 'ah')  

        UNION ALL

        
        SELECT 
            mg.uid,
            mg.pkey,
            mg.id,
            mg.game,
            mg.cnt,
            mg.sub_type,
            mg.third_type,
            mg.sdate AS sdate,  
            mg.edate,
            mg.league,
            mg.result,
            mg.is_onoff
        FROM 
            main.mini_game mg
        WHERE 
            mg.uid NOT IN (
                SELECT DISTINCT uid 
                FROM main.mini_game_per_head_office 
                WHERE headoffice_idx = p_headoffice_idx
            )
            AND (p_sdate = ' ' OR ( DATE(mg.sdate) BETWEEN DATE(p_sdate) AND DATE(p_edate)))
            AND (p_cnt = -1 OR (mg.cnt = p_cnt AND p_cnt <> -1))
            AND (p_id = -1 OR (mg.id = p_id AND p_id <> -1))
            AND (p_sub_type = -1 OR (mg.sub_type = p_sub_type AND p_sub_type <> -1))
            AND (p_third_type = -1 OR (mg.third_type = p_third_type AND p_third_type <> -1))
            AND (p_pkey = -1 OR (mg.pkey = p_pkey AND p_pkey <> -1))
            AND NOT (mg.sub_type = 1 AND JSON_UNQUOTE(JSON_EXTRACT(mg.result, '$.type')) = 'ah')  
    ) AS combined_results
    
    ORDER BY 
        CASE WHEN p_order_by_sdate = -1 THEN sdate END DESC
    LIMIT p_pageSize OFFSET v_offset;

    
    SELECT COUNT(*) INTO v_totalCount FROM (
        
        SELECT 
            1
        FROM 
            main.mini_game_per_head_office mgph
        WHERE 
            mgph.headoffice_idx = p_headoffice_idx
            AND (p_sdate = ' ' OR ( DATE(mgph.sdate) BETWEEN DATE(p_sdate) AND DATE(p_edate)))
            AND (p_cnt = -1 OR (mgph.cnt = p_cnt AND p_cnt <> -1))
            AND (p_id = -1 OR (mgph.id = p_id AND p_id <> -1))
            AND (p_sub_type = -1 OR (mgph.sub_type = p_sub_type AND p_sub_type <> -1))
            AND (p_third_type = -1 OR (mgph.third_type = p_third_type AND p_third_type <> -1))
            AND (p_pkey = -1 OR (mgph.pkey = p_pkey AND p_pkey <> -1))
            AND NOT (mgph.sub_type = 1 AND JSON_UNQUOTE(JSON_EXTRACT(mgph.result, '$.type')) = 'ah')  

        UNION ALL
        

        
        SELECT 
            1
        FROM 
            main.mini_game mg
        WHERE 
            mg.uid NOT IN (
                SELECT DISTINCT uid 
                FROM main.mini_game_per_head_office 
                WHERE headoffice_idx = p_headoffice_idx
            )
            AND (p_sdate = ' ' OR ( DATE(mg.sdate) BETWEEN DATE(p_sdate) AND DATE(p_edate)))
            AND (p_cnt = -1 OR (mg.cnt = p_cnt AND p_cnt <> -1))
            AND (p_id = -1 OR (mg.id = p_id AND p_id <> -1))
            AND (p_sub_type = -1 OR (mg.sub_type = p_sub_type AND p_sub_type <> -1))
            AND (p_third_type = -1 OR (mg.third_type = p_third_type AND p_third_type <> -1))
            AND (p_pkey = -1 OR (mg.pkey = p_pkey AND p_pkey <> -1))
            AND NOT (mg.sub_type = 1 AND JSON_UNQUOTE(JSON_EXTRACT(mg.result, '$.type')) = 'ah')  
    ) AS total_count_results;

    
    SET v_totalPages = CEIL(v_totalCount / p_pageSize);

    
    IF p_return_total = 1 THEN
        SELECT v_totalPages AS total_page;
    END IF;
    
    IF p_return_old_result = 1 THEN
        SELECT *
        FROM 
            main.mini_game_per_head_office 
        WHERE 
            headoffice_idx = p_headoffice_idx AND uid = p_uid;
    END IF;
    
    IF p_return_changed = 1 THEN
        SELECT id, uid
        FROM 
            main.mini_game_per_head_office 
        WHERE 
            headoffice_idx = p_headoffice_idx AND (p_sub_type = -1 OR (sub_type = p_sub_type AND p_sub_type <> -1))
            AND (p_third_type = -1 OR (third_type = p_third_type AND p_third_type <> -1)) AND old_result IS NOT NULL ;
    END IF;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_mini_game_get_soccer_uid` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_mini_game_get_soccer_uid`(
	IN `p_cnt` INT
)
BEGIN
  SELECT uid FROM main.mini_game WHERE cnt = p_cnt;
  SELECT result FROM main.mini_game WHERE cnt = p_cnt;
  SELECT id FROM main.mini_game WHERE cnt = p_cnt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_mini_game_left_join` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_mini_game_left_join`(

	IN `p_headoffice_idx` INT,

	IN `p_sdate` VARCHAR(50),

	IN `p_edate` VARCHAR(50),

	IN `p_cnt` INT,

	IN `p_sub_type` INT,

	IN `p_third_type` INT,

	IN `p_page_size` INT,

	IN `p_current_page` INT,

	IN `p_key1` VARCHAR(50),

	IN `p_key2` VARCHAR(50)

)
BEGIN

  DECLARE v_offset INT;

   DECLARE v_totalCount INT;

   DECLARE v_totalPages INT;



  SET v_offset = (p_current_page - 1) * p_page_size;

SELECT mg.*,

    mgh.is_onoff AS mgh_is_onoff,

    mgh.result AS mgh_result,

    mgh.headoffice_idx AS headoffice_idx



    FROM main.mini_game AS mg



        LEFT JOIN main.mini_game_per_head_office AS mgh



        ON mg.uid = mgh.uid

            AND mgh.headoffice_idx = p_headoffice_idx



       WHERE (p_sdate = ' ' OR (sdate_date  BETWEEN p_sdate AND p_edate))

                AND (p_cnt = -1 OR (mg.cnt = p_cnt AND p_cnt <> -1))

                AND (p_sub_type = -1 OR (mg.sub_type = p_sub_type AND p_sub_type <> -1))

                AND (p_third_type = -1 OR (mg.third_type = p_third_type AND p_third_type <> -1))



        ORDER BY sdate DESC



        LIMIT p_page_size OFFSET v_offset;



SELECT COUNT(*) INTO v_totalCount



    FROM main.mini_game AS mg



        LEFT JOIN main.mini_game_per_head_office AS mgh



        ON mg.uid = mgh.uid

            AND mgh.headoffice_idx = p_headoffice_idx



        WHERE (p_sdate = ' ' OR (sdate_date  BETWEEN p_sdate AND p_edate))

                AND (p_cnt = -1 OR (mg.cnt = p_cnt AND p_cnt <> -1))

                AND (p_sub_type = -1 OR (mg.sub_type = p_sub_type AND p_sub_type <> -1))

                AND (p_third_type = -1 OR (mg.third_type = p_third_type AND p_third_type <> -1));





  SET v_totalPages = CEIL(v_totalCount / p_page_size);



  SELECT v_totalPages AS total_page;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_mini_game_results_by_id_and_head_office` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_mini_game_results_by_id_and_head_office`(
    IN `p_headoffice_idx` INT,
    IN `p_uid` INT 
)
    COMMENT 'Procedure to select specific minigame by uid and head office idx'
BEGIN
    SELECT *
        FROM 
            main.mini_game_per_head_office 
        WHERE 
            headoffice_idx = p_headoffice_idx 
        AND uid = p_uid;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_mini_game_update_old_result` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_mini_game_update_old_result`(
	IN `p_game` VARCHAR(50),
	IN `p_id` INT,
	IN `p_result` VARCHAR(2000)
)
BEGIN
    
    UPDATE main.mini_game_per_head_office
    SET old_result = p_result  
    WHERE game = p_game
      AND id = p_id
      AND old_result IS NOT NULL;
      
   	UPDATE main.mini_game_per_head_office
    SET result = p_result  
    WHERE game = p_game
      AND id = p_id
      AND old_result IS NULL;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_mini_game_update_on_off` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_mini_game_update_on_off`(
	IN `p_uid` INT,
	IN `p_is_onoff` VARCHAR(50),
	IN `p_head_office` INT
)
BEGIN
    DECLARE existing_count INT;
    DECLARE v_id INT;          
    DECLARE v_pkey VARCHAR(255);
    DECLARE v_game VARCHAR(255);
    DECLARE v_cnt INT;
    DECLARE v_sub_type INT;
    DECLARE v_third_type INT;
    DECLARE v_sdate VARCHAR(255);
    DECLARE v_edate VARCHAR(255);
    DECLARE v_league VARCHAR(255);
    DECLARE v_result JSON;

    
    SELECT 
        id,                
        pkey,
        game,
        cnt,
        sub_type,
        third_type,
        sdate,
        edate,
        league,
        result
    INTO 
        v_id,             
        v_pkey, 
        v_game, 
        v_cnt, 
        v_sub_type, 
        v_third_type, 
        v_sdate, 
        v_edate, 
        v_league, 
        v_result
    FROM 
        main.mini_game
    WHERE 
        uid = p_uid;  

    
    SELECT COUNT(*)
    INTO existing_count
    FROM main.mini_game_per_head_office
    WHERE headoffice_idx = p_head_office AND uid = p_uid;  

    
    IF existing_count > 0 THEN
        UPDATE main.mini_game_per_head_office
        SET 
            is_onoff = p_is_onoff,
            pkey = v_pkey,
            game = v_game,
            cnt = v_cnt,
            sub_type = v_sub_type,
            third_type = v_third_type,
            sdate = v_sdate,
            edate = v_edate,
            league = v_league
           
        WHERE headoffice_idx = p_head_office AND uid = p_uid;  
    ELSE
        
        INSERT INTO main.mini_game_per_head_office (
            id,               
            headoffice_idx,
            uid,             
            is_onoff,
            pkey,
            game,
            cnt,
            sub_type,
            third_type,
            sdate,
            edate,
            league,
            result,
            old_result
        ) VALUES (
            v_id,            
            p_head_office,
            p_uid,          
            p_is_onoff,
            v_pkey,
            v_game,
            v_cnt,
            v_sub_type,
            v_third_type,
            v_sdate,
            v_edate,
            v_league,
            v_result,
            NULL
            
        );
    END IF;
    
    SELECT v_game AS game_name; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_mini_game_update_score` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_mini_game_update_score`(
	IN `p_uid` INT,
	IN `p_result` VARCHAR(2000),
	IN `p_head_office` INT
)
BEGIN
    DECLARE existing_count INT;
    DECLARE v_id INT;          
    DECLARE v_pkey VARCHAR(255);
    DECLARE v_game VARCHAR(255);
    DECLARE v_cnt INT;
    DECLARE v_sub_type INT;
    DECLARE v_third_type INT;
    DECLARE v_sdate VARCHAR(255);
    DECLARE v_edate VARCHAR(255);
    DECLARE v_league VARCHAR(255);
    DECLARE v_is_onoff VARCHAR(3); 
    DECLARE v_result JSON;     

    
    SELECT 
        id,                
        pkey,
        game,
        cnt,
        sub_type,
        third_type,
        sdate,
        edate,
        league,
        is_onoff,          
        result             
    INTO 
        v_id,             
        v_pkey, 
        v_game, 
        v_cnt, 
        v_sub_type, 
        v_third_type, 
        v_sdate, 
        v_edate, 
        v_league, 
        v_is_onoff,        
        v_result          
    FROM 
        main.mini_game
    WHERE 
        uid = p_uid;  

    
    SELECT COUNT(*)
    INTO existing_count
    FROM main.mini_game_per_head_office
    WHERE headoffice_idx = p_head_office AND uid = p_uid;  

    
    IF existing_count > 0 THEN
        UPDATE main.mini_game_per_head_office
        SET 
             
            pkey = v_pkey,
            game = v_game,
            cnt = v_cnt,
            sub_type = v_sub_type,
            third_type = v_third_type,
            sdate = v_sdate,
            edate = v_edate,
            league = v_league,
            result = p_result,
				old_result = v_result  
        WHERE headoffice_idx = p_head_office AND uid = p_uid;  
    ELSE
        
        INSERT INTO main.mini_game_per_head_office (
            id,               
            headoffice_idx,
            uid,             
            is_onoff,        
            pkey,
            game,
            cnt,
            sub_type,
            third_type,
            sdate,
            edate,
            league,
            result,
            old_result
        ) VALUES (
            v_id,            
            p_head_office,
            p_uid,          
            v_is_onoff,     
            v_pkey,
            v_game,
            v_cnt,
            v_sub_type,
            v_third_type,
            v_sdate,
            v_edate,
            v_league,
            p_result,        
            v_result
        );
    END IF;

    
    SELECT v_result AS result_value;
	 SELECT v_game AS game_name;   
	 SELECT v_sub_type AS sub_type;
	 SELECT v_third_type AS third_type;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_noticeboard_create_template` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_noticeboard_create_template`(
	IN `headOfficeIdx` INT,
	IN `partnerType` INT,
	IN `titleData` VARCHAR(1000),
	IN `detailData` VARCHAR(10000),
	IN `templateType` VARCHAR(500)
)
BEGIN

	SET @totalCount = 0,
		 @totalTemplateType = 0,
		 @currentTemplateTypeIdx = 0;
	
	
	SELECT COUNT(*) INTO @totalCount FROM noticeboard_template AS nt
	INNER JOIN noticeboard_template_types AS ntt ON ntt.head_office_idx = headOfficeIdx 
		AND ntt.template_type = templateType AND ntt.partner_type = partnerType
	WHERE nt.title = titleData AND nt.detail = detailData;
		
	
	IF @totalCount = 0 THEN
	
		SELECT COUNT(*) INTO @totalTemplateType FROM noticeboard_template_types WHERE head_office_idx = headOfficeIdx  
		AND partner_type = partnerType AND template_type = templateType;
		
	  		
  		IF @totalTemplateType = 0 THEN
  			
			INSERT INTO noticeboard_template_types(head_office_idx, partner_type, template_type) VALUES (headOfficeIdx, partnerType, templateType);
			
			
			SELECT template_type_idx INTO @currentTemplateTypeIdx FROM noticeboard_template_types 
			WHERE head_office_idx = headOfficeIdx AND partner_type = partnerType AND template_type = templateType;
			  			
  			
  			INSERT INTO noticeboard_template (template_type_idx, title, detail, date_created) 
  			VALUES (@currentTemplateTypeIdx, titleData, detailData, NOW());	
  			
  			SELECT 1 AS resultCode, 'Template successfully created!' AS resultDescription;
  		ELSE 
  			   
		 	
			SELECT template_type_idx INTO @currentTemplateTypeIdx FROM noticeboard_template_types 
			WHERE head_office_idx = headOfficeIdx AND partner_type = partnerType AND template_type = templateType;
			
				
  			INSERT INTO noticeboard_template (template_type_idx, title, detail, date_created) 
  			VALUES (@currentTemplateTypeIdx, titleData, detailData, NOW());	
			
		   	SELECT 1 AS resultCode, 'Template successfully created!' AS resultDescription;
  		END IF;
  		 		
  	
	ELSE 
		SELECT 0 AS resultCode, 'Duplicate template were found!' AS resultDescription;
	END IF;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_noticeboard_delete_template` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_noticeboard_delete_template`(
	IN `headOfficeIdx` INT,
	IN `partnerType` INT,
	IN `templateIdxs` VARCHAR(1000)
)
BEGIN
		
		SET @totalCount = 0;
				
			SELECT COUNT(*) INTO @totalCount FROM noticeboard_template 
			WHERE FIND_IN_SET(template_idx, REPLACE(templateIdxs, ' ', '')) > 0;

		
		IF @totalCount >= 1 THEN
			
				
				DELETE FROM noticeboard_template 
				WHERE FIND_IN_SET(template_idx, REPLACE(templateIdxs, ' ', '')) > 0;	
				SELECT 1 AS resultCode, 'Success!' AS resultDescription;
				
				
				DELETE ntt FROM noticeboard_template_types ntt
				LEFT JOIN noticeboard_template nt 
				ON ntt.template_type_idx = nt.template_type_idx
				WHERE nt.template_idx IS NULL;
				
					
		ELSE
				SELECT 0 AS resultCode, 'No template(s) were found!' AS resultDescription;
		END IF;
		
		

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_noticeboard_fetch_template` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_noticeboard_fetch_template`(
	IN `headOfficeIdx` INT,
	IN `partnerType` INT,
	IN `templateType` VARCHAR(100),
	IN `currentPage` INT,
	IN `pageSize` INT
)
BEGIN
	SET @totalCount = 0,
	 @limitFrom = IF(currentPage <= 1, 0, (pageSize * currentPage) - pageSize),
	 @limitTo = pageSize;
	 
	IF LOWER(templateType) = 'all' THEN
		
	
				SELECT COUNT(*) INTO @totalCount FROM noticeboard_template AS nt
				INNER JOIN noticeboard_template_types AS ntt ON ntt.head_office_idx = headOfficeIdx AND partner_type = partnerType
				AND nt.template_type_idx = ntt.template_type_idx;
	
		
		IF @totalCount >= 1 THEN
		
			SET @query = CONCAT(
		    'SELECT nt.template_idx, nt.title, nt.detail, ntt.template_type, nt.date_created FROM noticeboard_template AS nt ',
		    'INNER JOIN noticeboard_template_types AS ntt ON ntt.partner_type = "', partnerType, '" ',
		    'AND ntt.head_office_idx = "', headOfficeIdx, '" AND ntt.template_type_idx = nt.template_type_idx ',
		    'ORDER BY nt.date_created DESC LIMIT ', @limitFrom, ',', @limitTo, ';'
			);


			
			PREPARE stmt FROM @query;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
					
			SELECT 1 AS resultCode, 'Success!' AS resultDescription, CEIL(@totalCount / pageSize) AS totalPage;
		ELSE
			SELECT 0 AS resultCode, 'No template(s) were found!' AS resultDescription, CEIL(@totalCount / pageSize) AS totalPage;
		END IF;
		
		
	ELSE 
	

			SELECT COUNT(*) INTO @totalCount FROM noticeboard_template AS nt
			INNER JOIN noticeboard_template_types AS ntt ON ntt.template_type_idx = ntt.template_type_idx 
			AND LOWER(ntt.template_type) = templateType AND ntt.partner_type = partnerType AND ntt.head_office_idx = headOfficeIdx;

		
		IF @totalCount >= 1 THEN
		
			SET @query = CONCAT('SELECT nt.template_idx, nt.title, nt.detail, ntt.template_type, nt.date_created 
	      FROM noticeboard_template as nt INNER JOIN noticeboard_template_types as ntt
	      ON nt.template_type_idx = ntt.template_type_idx AND
			LOWER(ntt.template_type) = ''', templateType, ''' 
	      AND ntt.partner_type = ', partnerType, ' 
	      AND ntt.head_office_idx = ', headOfficeIdx, ' 
	      ORDER BY nt.date_created DESC 
	      LIMIT ', @limitFrom, ', ', @limitTo, ';');

			
			
			PREPARE stmt FROM @query;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
			
			SELECT 1 AS resultCode, 'success' AS resultDescription, CEIL(@totalCount / pageSize) AS totalPage;
		ELSE
			SELECT 0 AS resultCode, 'No template(s) were found!' AS resultDescription, CEIL(@totalCount / pageSize) AS totalPage;	
		END IF;
		
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_noticeboard_fetch_template_types` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_noticeboard_fetch_template_types`(
	IN `headOfficeIdx` INT,
	IN `partnerType` INT
)
BEGIN
		SET @totalCount = 0;
		
		
		
	 	SELECT COUNT(*) INTO @totalCount FROM noticeboard_template_types 
		 WHERE partner_type = partnerType AND head_office_idx = headOfficeIdx;
		
		IF @totalCount >= 1 THEN 
				
				
				
				SELECT template_type_idx, template_type FROM noticeboard_template_types 
				WHERE partner_type = partnerType AND head_office_idx = headOfficeIdx;
				
				SELECT 1 AS resultCode, 'Success!' AS resultDescription;
		ELSE 
				SELECT 0 AS resultCode, 'No template(s) were found!' AS resultDescription;
		END IF;

		
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_noticeboard_update_template` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_noticeboard_update_template`(
	IN `templateIdx` INT,
	IN `partnerType` INT,
	IN `titleData` VARCHAR(1000),
	IN `detailData` VARCHAR(10000)
)
BEGIN
	SET @totalCount = 0;
	 
	SELECT COUNT(*) INTO @totalCount FROM noticeboard_template AS nt
	INNER JOIN noticeboard_template_types AS ntt ON nt.template_idx = templateIdx 
	AND nt.template_type_idx = ntt.template_type_idx  AND ntt.partner_type = partnerType;

	 	
	IF @totalCount = 1 THEN
		UPDATE noticeboard_template SET title = titleData, detail = detailData, date_updated = NOW()
		WHERE template_idx = templateIdx; 
		
		SELECT 1 AS resultCode, 'Success!' AS resultDescription;
	ELSE
		
		SELECT 0 AS resultCode, 'No template(s) were found!' AS resultDescription;
	 END IF;
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_old_point_payment_event` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_old_point_payment_event`(
in in_start_date datetime
,in in_end_date datetime
,in in_event_type int
)
main : BEGIN

	DECLARE ret_code INT;
	DECLARE errno INT;
    DECLARE v_txt_error_msg text;
    DECLARE $next TEXT;
	DECLARE $value TEXT;
	   
    DECLARE v_idx bigint;
    DECLARE v_id varchar(45);
    DECLARE v_nick_name varchar(45);
    DECLARE v_before_point int;
    DECLARE v_charge_total_money bigint;
    DECLARE v_exchange_total_money bigint;
    
    DECLARE v_level int;
    DECLARE v_partner_type int;
	DECLARE v_pt_idx bigint;
    DECLARE v_pt_id varchar(45);
    DECLARE v_pt_nick_name varchar(45);
    DECLARE v_pt_partner_type int;
    DECLARE v_head_office_idx bigint;           
    
    DECLARE l_calculate bigint;
    DECLARE l_give_point int;
    DECLARE l_u_key varchar(64);
    DECLARE l_pkey varchar(2);
    DECLARE l_day int;
    
    DECLARE done INTEGER DEFAULT 0;
    DEClARE openCursor CURSOR FOR SELECT mb.idx,mb.`point`,mb.id,mb.nick_name,mb.`level`,mb.partner_type
    ,pt.idx as pt_Idx,pt.id as pt_id,pt.nick_name as pt_nick_name,pt.partner_type as pt_partner_type
    ,mb.head_office_idx
    
    FROM member as mb 
    left join member as pt on mb.parent_partner_idx = pt.idx
    where mb.member_type = 'player' and mb.`status` = 1;
             
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
		GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO
		, v_txt_error_msg = MESSAGE_TEXT;
        set ret_code = -1000;
		SELECT ret_code,errno AS MYSQL_ERROR,v_txt_error_msg as MESSAGE_TEXT;
		ROLLBACK;
    END;
           
    START TRANSACTION;
        
        OPEN openCursor;
			  read_loop: LOOP
					   
			   FETCH openCursor INTO v_idx,v_before_point,v_id,v_nick_name,v_level,v_partner_type
               ,v_pt_idx,v_pt_id,v_pt_nick_name,v_pt_partner_type
               ,v_head_office_idx;
			   
			   IF done THEN
					   LEAVE read_loop;
			   END IF;
			                  
			   
               IF EXISTS(SELECT * from statistic_member_comprehensive_details where member_idx = v_idx and total_charge > 0) THEN
						SELECT total_charge,total_exchange into v_charge_total_money,v_exchange_total_money from statistic_member_comprehensive_details where member_idx = v_idx;
						IF NOT EXISTS(SELECT * from member_event_point where member_idx = v_idx and `event_type` = 1) THEN
							set l_calculate = v_charge_total_money - v_exchange_total_money;
							IF l_calculate <= 1000000 then
								set l_give_point = 20000;
							ELSEIF 1000000 < l_calculate and l_calculate <= 10000000 THEN
								set l_give_point = 30000;
							ELSEIF 10000000 < l_calculate and l_calculate <= 30000000 THEN
								set l_give_point = 100000;
							ELSEIF 30000000 < l_calculate and l_calculate <= 50000000 THEN
								set l_give_point = 200000;
							ELSEIF 50000000 < l_calculate and l_calculate <= 100000000 THEN
								set l_give_point = 300000;
							ELSEIF 100000000 < l_calculate THEN
								set l_give_point = 500000;
							END IF;
						
						
							update member set `point` = `point` + l_give_point where idx = v_idx;
							
							set l_u_key = UUID();
							SELECT DAY(NOW()) into l_day;
							IF  9 < l_day THEN
							  set l_pkey = CAST(l_day AS CHAR) ;
							ELSE
							  set l_pkey = CONCAT('0',CAST(l_day AS CHAR)) ;
							END IF;
										
						   
						   
						   INSERT INTO log.log_info (u_key,pkey,member_idx,id,nick_name,`level`,partner_type,pt_member_idx,pt_id
						   ,pt_nick_name,pt_partner_type,main_type,sub_type,domain,ip,country,detail_info,`status`,value_type) 
						   VALUES (l_u_key, l_pkey, v_idx,v_id, v_nick_name, v_level,v_partner_type
						   ,v_pt_idx ,v_pt_id,v_pt_nick_name,v_pt_partner_type
						   ,41,1003,'' ,'' ,'',JSON_OBJECT('desc','구정이벤트', 'point',l_give_point,'partner_id','hpBetgo','after_point',v_before_point + l_give_point
						   ,'partner_idx',v_head_office_idx,'before_point',v_before_point,'partner_nick_name','hpBetgo','parent_partner_type',201,'partner_type_classification',201)
						   ,1,2);
						 
							INSERT INTO statistics.statistics_source_data 
							(u_key, pkey, member_idx, id, nick_name, partner_type, parent_idx, parent_id,
							parent_nick_name, parent_partner_type, member_type, type, value, head_office_idx)
							VALUES (l_u_key, l_pkey, v_idx, v_id,v_nick_name, v_partner_type
							,v_pt_idx, v_pt_id,v_pt_nick_name, v_pt_partner_type
							, 'player', 100, l_give_point, v_head_office_idx);
							
							insert into member_event_point(member_idx,event_type,total_charge,total_exchange,calculate,reward_point) 
							value (v_idx,in_event_type,v_charge_total_money,v_exchange_total_money,l_calculate,l_give_point);
                     END IF;
              END IF;
              
			  END LOOP read_loop;

         CLOSE openCursor;
        
	COMMIT;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_point_payment_event` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_point_payment_event`(
in in_start_date datetime
,in in_end_date datetime
,in in_event_type int
)
main : BEGIN
	DECLARE ret_code INT;
	DECLARE errno INT;
    DECLARE v_txt_error_msg text;
    DECLARE $next TEXT;
	DECLARE $value TEXT;
	   
    DECLARE v_idx bigint;
    DECLARE v_id varchar(45);
    DECLARE v_nick_name varchar(45);
    DECLARE v_before_point int;
    DECLARE v_charge_total_money bigint;
    DECLARE v_exchange_total_money bigint;
    
    DECLARE v_level int;
    DECLARE v_partner_type int;
	DECLARE v_pt_idx bigint;
    DECLARE v_pt_id varchar(45);
    DECLARE v_pt_nick_name varchar(45);
    DECLARE v_pt_partner_type int;
    DECLARE v_head_office_idx bigint;           
    
    DECLARE l_calculate bigint;
    DECLARE l_give_point int;
    DECLARE l_u_key varchar(64);
    DECLARE l_pkey varchar(2);
    DECLARE l_day int;
    
    DECLARE done INTEGER DEFAULT 0;
    DEClARE openCursor CURSOR FOR SELECT mb.idx,mb.`point`,mb.id,mb.nick_name,mb.`level`,mb.partner_type
    ,pt.idx as pt_Idx,pt.id as pt_id,pt.nick_name as pt_nick_name,pt.partner_type as pt_partner_type
    ,mb.head_office_idx
    
    FROM member as mb 
    left join member as pt on mb.parent_partner_idx = pt.idx
    where mb.member_type = 'player' and mb.`status` = 1;
             
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
		GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO
		, v_txt_error_msg = MESSAGE_TEXT;
        set ret_code = -1000;
		SELECT ret_code,errno AS MYSQL_ERROR,v_txt_error_msg as MESSAGE_TEXT;
		ROLLBACK;
    END;
           
    START TRANSACTION;
        
        OPEN openCursor;
			  read_loop: LOOP
					   
			   FETCH openCursor INTO v_idx,v_before_point,v_id,v_nick_name,v_level,v_partner_type
               ,v_pt_idx,v_pt_id,v_pt_nick_name,v_pt_partner_type
               ,v_head_office_idx;
			   
			   IF done THEN
					   LEAVE read_loop;
			   END IF;
			                  
			   IF EXISTS(SELECT * from member_charge_info where member_idx = v_idx and `status` = 3 and update_dt between in_start_date and in_end_date) THEN
						SELECT total_charge,total_exchange into v_charge_total_money,v_exchange_total_money from statistic_member_comprehensive_details where member_idx = v_idx;
						
						set l_calculate = v_charge_total_money - v_exchange_total_money;
                        IF l_calculate <= 1000000 then
							set l_give_point = 20000;
                        ELSEIF 1000000 < l_calculate and l_calculate <= 10000000 THEN
							set l_give_point = 30000;
                        ELSEIF 10000000 < l_calculate and l_calculate <= 30000000 THEN
							set l_give_point = 100000;
                        ELSEIF 30000000 < l_calculate and l_calculate <= 50000000 THEN
							set l_give_point = 200000;
                        ELSEIF 50000000 < l_calculate and l_calculate <= 100000000 THEN
							set l_give_point = 300000;
                        ELSEIF 100000000 < l_calculate THEN
							set l_give_point = 500000;
                        END IF;
                        						
						update member set `point` = `point` + l_give_point where idx = v_idx;
                        
                     	set l_u_key = UUID();
						SELECT DAY(NOW()) into l_day;
                        IF  9 < l_day THEN
                          set l_pkey = CAST(l_day AS CHAR) ;
                        ELSE
						  set l_pkey = CONCAT('0',CAST(l_day AS CHAR)) ;
                        END IF;
									
                       
                       
                       INSERT INTO log.log_info (u_key,pkey,member_idx,id,nick_name,`level`,partner_type,pt_member_idx,pt_id
                       ,pt_nick_name,pt_partner_type,main_type,sub_type,domain,ip,country,detail_info,`status`,value_type) 
                       VALUES (l_u_key, l_pkey, v_idx,v_id, v_nick_name, v_level,v_partner_type
                       ,v_pt_idx ,v_pt_id,v_pt_nick_name,v_pt_partner_type
                       ,41,1003,'' ,'' ,'',JSON_OBJECT('desc','구정이벤트', 'point',l_give_point,'partner_id','hpBetgo','after_point',v_before_point + l_give_point
                       ,'partner_idx',v_head_office_idx,'before_point',v_before_point,'partner_nick_name','hpBetgo','parent_partner_type',201,'partner_type_classification',201)
                       ,1,2);
                     
						INSERT INTO statistics.statistics_source_data 
						(u_key, pkey, member_idx, id, nick_name, partner_type, parent_idx, parent_id,
						parent_nick_name, parent_partner_type, member_type, type, value, head_office_idx)
						VALUES (l_u_key, l_pkey, v_idx, v_id,v_nick_name, v_partner_type
						,v_pt_idx, v_pt_id,v_pt_nick_name, v_pt_partner_type
						, 'player', 100, l_give_point, v_head_office_idx);
                        
						insert into member_event_point(member_idx,event_type,total_charge,total_exchange,calculate,reward_point) 
                        value (v_idx,in_event_type,v_charge_total_money,v_exchange_total_money,l_calculate,l_give_point);
                     
              END IF;
              
			  END LOOP read_loop;

         CLOSE openCursor;
        
	COMMIT;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_sports_betting_cancel` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_sports_betting_cancel`(
	IN `i_u_key` VARCHAR(50),
    IN `i_settlement_type` INT,
    IN `i_take_money` INT,
    IN `i_detail_info` JSON,
    IN `i_cal_count` INT
)
BEGIN
    DECLARE t_member_idx BIGINT;
    DECLARE t_head_office_idx BIGINT;          
    DECLARE t_u_key VARCHAR(64);
    DECLARE t_p_key VARCHAR(2);
    DECLARE t_game_type INT;
    DECLARE t_total_bet_money INT;
    DECLARE t_detail_info JSON;
    
    DECLARE t_member_id VARCHAR(20);
    DECLARE t_member_nickname VARCHAR(20);
    DECLARE t_member_partner_type INT;
    DECLARE t_member_before_money INT;
    DECLARE t_member_before_point INT;
    DECLARE t_member_before_coupon INT;
    DECLARE t_member_before_level INT;

    DECLARE t_member_parent_partner_id VARCHAR(20);
    DECLARE t_member_parent_partner_idx BIGINT;
    DECLARE t_member_parent_partner_nick_name VARCHAR(20);
    DECLARE t_member_parent_partner_type INT;

    UPDATE main.member_toto_betting_info SET settlement = i_settlement_type, take_money = take_money + total_bet_money - i_take_money, detail_info = i_detail_info, cal_count = i_cal_count WHERE u_key = i_u_key;
    
	SELECT u_key, pkey, member_idx, head_office_idx, game_type, total_bet_money, detail_info 
    INTO t_u_key, t_p_key, t_member_idx, t_head_office_idx, t_game_type, t_total_bet_money, t_detail_info
    FROM main.member_toto_betting_info WHERE u_key = i_u_key;    
    
	SELECT id, nick_name, partner_type, money, `point`, coupon, `level`, parent_partner_idx
    INTO t_member_id, t_member_nickname, t_member_partner_type, t_member_before_money, t_member_before_point, t_member_before_coupon, t_member_before_level, t_member_parent_partner_idx
    FROM main.`member` WHERE idx = t_member_idx;    
    
	SELECT id, nick_name, partner_type 
    INTO t_member_parent_partner_id, t_member_parent_partner_nick_name, t_member_parent_partner_type
    FROM main.`member` WHERE idx = t_member_parent_partner_idx;    
    
    UPDATE main.`member` SET money = money + t_total_bet_money - i_take_money WHERE idx = t_member_idx;
    
	SELECT mem.money as t_member_after_money, t_member_idx, t_u_key as u_key, t_p_key as pkey, t_head_office_idx, t_game_type, t_total_bet_money, t_detail_info, t_member_id, t_member_nickname, t_member_partner_type, t_member_before_money, t_member_before_point, t_member_before_coupon, t_member_before_level,
    t_member_parent_partner_idx, t_member_parent_partner_id, t_member_parent_partner_nick_name, t_member_parent_partner_type
    FROM main.`member` mem WHERE mem.idx = t_member_idx;

END ;;
DELIMITER ;


