-- CREATE DATABASE  IF NOT EXISTS `db_exchange_option` /*!40100 DEFAULT CHARACTER SET utf8 */;
-- USE `db_exchange_option`;
--
-- Host: 142.93.91.123    Database: db_exchange_option
-- ------------------------------------------------------


--
-- Dumping events for database 'db_exchange_option'
--

--
-- Dumping routines for database 'db_exchange_option'
--
/*!50003 DROP PROCEDURE IF EXISTS `check_server` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `check_server`(
 IN ip VARCHAR(32)
)
main : BEGIN

	declare ret_code int(11) default 1;
    declare ret_status tinyint(4) default 0;
    declare ret_start_date datetime default '0000-00-00 00:00:00';
    declare ret_end_date datetime default '0000-00-00 00:00:00';
    declare ret_now_date datetime default '0000-00-00 00:00:00';
    declare ret_message varchar(90) default '';
    
    select status,start_date,end_date,now(),message 
    into ret_status,ret_start_date,ret_end_date,ret_now_date,ret_message from pg_web_server_status;
        
    -- if ret_status = 2 or (ret_status = 1 and '59.6.184.215' <> ip) 
    if ret_status = 2 or (ret_status = 1 and not exists (select unq_id from pg_unique_id where unq_ip = ip)) 
     and ret_start_date <= ret_now_date and ret_now_date <  ret_end_date 
    then
    	set ret_code = -105;
       
        -- leave main;
    end if;
    
     select ret_code,ret_message,ret_start_date,ret_end_date;
     -- select * from pg_web_notice where start_date < ret_now_date and ret_now_date <= end_date;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_notice` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `get_notice`()
BEGIN
 declare ret_now_date datetime default '0000-00-00 00:00:00';
 select now() into ret_now_date;
 select * from pg_web_notice where start_date < ret_now_date and ret_now_date <= end_date;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_into_all` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `insert_into_all`(
	in tableName varchar(64),
    in columnList varchar(124),
    in tableValue varchar(1024)
)
BEGIN

	SET @tempQuery = CONCAT("insert into ",tableName, " ",columnList, " values ",tableValue);

	PREPARE stmt FROM @tempQuery;
	EXECUTE stmt;

	DEALLOCATE PREPARE stmt;
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_set_member_point_balance_action` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `proc_set_member_point_balance_action`(
	IN `PARAM_MB_NO` INT,
	IN `PARAM_MARKET_TYPE` VARCHAR(10),
	IN `PARAM_ACTION` VARCHAR(20)
)
    COMMENT '총 합산 - 데이터가 많아질 경우를 대비하여 마지막 삽입포인트 번호로 SUM '
BEGIN
		START TRANSACTION;
		
	
		-- -----------------------		
		-- btc
		-- -----------------------
		IF (PARAM_MARKET_TYPE = 'btc' OR PARAM_MARKET_TYPE = 'ALL') THEN
		
			-- 사용중인 금액
			SET @total_btc_on = 0;-- IFNULL((SELECT po_point_on FROM web_point_on_trade_latest WHERE po_type='btc' AND mb_no=PARAM_MB_NO),0.00000000);
			SET @total_btc_on_withdraw = IFNULL((SELECT SUM(od_temp_amount) FROM pg_web_wallet_withdrawals WHERE mb_no=PARAM_MB_NO AND po_type='btc' AND (od_status<>'OK' AND od_status<>'CAN')), 0);
			SET @total_btc_on_lend = 0;
			
		
			SET @btc_po_no = NULL;
			SET @btc_last_po_no = NULL;
			SET @btc_last_po_on_trade = NULL;
		
			SELECT po_no,po_last_sum_po_no,po_last_sum_po_point INTO 
			@btc_po_no,
			@btc_last_po_no,
			@btc_last_sum_po_point
			FROM pg_trade_member_point_balance WHERE po_type = 'btc' AND mb_no = PARAM_MB_NO;
			IF @btc_last_po_no IS NULL THEN
				SET @btc_last_po_no = 0;
				SET @btc_last_sum_po_point = 0;
			END IF;
			
			SET @btc_max_pono = (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = 'payment' AND TABLE_NAME = 'pg_web_point_btc') - 1;
			IF @btc_max_pono IS NOT NULL AND @btc_max_pono > @btc_last_po_no THEN
				SET @total_btc		= @btc_last_sum_po_point + (SELECT TRUNCATE(IFNULL(SUM(po_point), 0.00000000), 8) FROM pg_web_point_btc WHERE po_no > @btc_last_po_no AND po_no <= @btc_max_pono AND mb_no = PARAM_MB_NO);
			ELSE
				SET @total_btc		= @btc_last_sum_po_point;
			END IF;
			IF (@btc_po_no IS NOT NULL) THEN
				UPDATE pg_trade_member_point_balance SET po_total=@total_btc, po_on_trade=@total_btc_on, po_on_etc=@total_btc_on_withdraw, po_on_lend=@total_btc_on_lend, po_action=PARAM_ACTION
					,po_reg_dt= NOW(),po_last_sum_po_no=@btc_max_pono,po_last_sum_po_point=@total_btc
					WHERE po_type = 'btc' AND mb_no = PARAM_MB_NO;
			ELSE
				INSERT INTO pg_trade_member_point_balance (po_type,mb_no,po_total,po_on_trade,po_on_etc,po_on_lend,po_action,po_last_sum_po_no,po_last_sum_po_point) VALUES
					('btc',PARAM_MB_NO,@total_btc,@total_btc_on,@total_btc_on_withdraw,@total_btc_on_lend,PARAM_ACTION,@btc_max_pono,@total_btc);
			END IF;
			
			INSERT INTO pg_trade_member_point_balance_his (po_type,mb_no,po_total,po_on_trade,po_on_etc,po_on_lend,po_action,po_last_sum_po_no,po_last_sum_po_point) VALUES
				('btc',PARAM_MB_NO,@total_btc,@total_btc_on,@total_btc_on_withdraw,@total_btc_on_lend,PARAM_ACTION,@btc_max_pono,@total_btc);
			
		END IF;
        
			-- -----------------------
		-- eth
		-- -----------------------
		IF (PARAM_MARKET_TYPE = 'eth' OR PARAM_MARKET_TYPE = 'ALL') THEN
		
			-- 사용중인 금액
			SET @total_eth_on = 0;-- IFNULL((SELECT po_point_on FROM web_point_on_trade_latest WHERE po_type='eth' AND mb_no=PARAM_MB_NO),0.00000000);
			SET @total_eth_on_withdraw = IFNULL((SELECT SUM(od_temp_amount) FROM pg_web_wallet_withdrawals WHERE mb_no=PARAM_MB_NO AND po_type='eth' AND (od_status<>'OK' AND od_status<>'CAN')), 0);
			SET @total_eth_on_lend = 0;
			
			
			SET @eth_po_no = NULL;
			SET @eth_last_po_no = NULL;
			SET @eth_last_po_on_trade = NULL;
		
			SELECT po_no,po_last_sum_po_no,po_last_sum_po_point INTO 
			@eth_po_no,
			@eth_last_po_no,
			@eth_last_sum_po_point
			FROM pg_trade_member_point_balance WHERE po_type = 'eth' AND mb_no = PARAM_MB_NO;
			IF @eth_last_po_no IS NULL THEN
				SET @eth_last_po_no = 0;
				SET @eth_last_sum_po_point = 0;
			END IF;
			
			SET @eth_max_pono = (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = 'payment' AND TABLE_NAME = 'pg_web_point_eth') - 1;
			IF @eth_max_pono IS NOT NULL AND @eth_max_pono > @eth_last_po_no THEN
				SET @total_eth		= @eth_last_sum_po_point + (SELECT TRUNCATE(IFNULL(SUM(po_point), 0.00000000), 8) FROM pg_web_point_eth WHERE po_no > @eth_last_po_no AND po_no <= @eth_max_pono AND mb_no = PARAM_MB_NO);
			ELSE
				SET @total_eth		= @eth_last_sum_po_point;
			END IF;
			
			
			
			IF (@eth_po_no IS NOT NULL) THEN
				UPDATE pg_trade_member_point_balance SET po_total=@total_eth, po_on_trade=@total_eth_on, po_on_etc=@total_eth_on_withdraw, po_on_lend=@total_eth_on_lend, po_action=PARAM_ACTION
					,po_reg_dt= NOW(),po_last_sum_po_no=@eth_max_pono,po_last_sum_po_point=@total_eth
					WHERE po_type = 'eth' AND mb_no = PARAM_MB_NO;
			ELSE
				INSERT INTO pg_trade_member_point_balance (po_type,mb_no,po_total,po_on_trade,po_on_etc,po_on_lend,po_action,po_last_sum_po_no,po_last_sum_po_point) VALUES
					('eth',PARAM_MB_NO,@total_eth,@total_eth_on,@total_eth_on_withdraw,@total_eth_on_lend,PARAM_ACTION,@eth_max_pono,@total_eth);
			END IF;
			
			INSERT INTO pg_trade_member_point_balance_his (po_type,mb_no,po_total,po_on_trade,po_on_etc,po_on_lend,po_action,po_last_sum_po_no,po_last_sum_po_point) VALUES
				('eth',PARAM_MB_NO,@total_eth,@total_eth_on,@total_eth_on_withdraw,@total_eth_on_lend,PARAM_ACTION,@eth_max_pono,@total_eth);
			
		END IF;
        
        
        	-- -----------------------
		-- tai
		-- -----------------------
		IF (PARAM_MARKET_TYPE = 'tai' OR PARAM_MARKET_TYPE = 'ALL') THEN
			-- 사용중인 금액
			SET @total_tai_on = 0; -- IFNULL((SELECT po_point_on FROM pg_web_point_on_trade_latest WHERE po_type='tai' AND mb_no=PARAM_MB_NO),0.00000000);
			SET @total_tai_on_withdraw = IFNULL((SELECT SUM(od_temp_amount) FROM pg_web_wallet_withdrawals WHERE mb_no=PARAM_MB_NO AND po_type='tai' AND (od_status<>'OK' AND od_status<>'CAN')), 0);
			SET @total_tai_on_lend = 0;
			
		
			SET @tai_po_no = NULL;
			SET @tai_last_po_no = NULL;
			SET @tai_last_sum_po_point = NULL;
		
			SELECT po_no,po_last_sum_po_no,po_last_sum_po_point INTO 
			@tai_po_no,
			@tai_last_po_no,
			@tai_last_sum_po_point
			FROM pg_trade_member_point_balance WHERE po_type = 'tai' AND mb_no = PARAM_MB_NO;
			IF @tai_last_po_no IS NULL THEN
				SET @tai_last_po_no = 0;
				SET @tai_last_sum_po_point = 0;
			END IF;
			
			SET @tai_max_pono = (SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = 'payment' AND TABLE_NAME = 'pg_web_point_tai') - 1;
			IF @tai_max_pono IS NOT NULL AND @tai_max_pono > @tai_last_po_no THEN
				SET @total_tai		= @tai_last_sum_po_point + (SELECT TRUNCATE(IFNULL(SUM(po_point), 0.00000000), 8) FROM pg_web_point_tai WHERE po_no > @tai_last_po_no AND po_no <= @tai_max_pono AND mb_no = PARAM_MB_NO);
			ELSE
				SET @total_tai		= @tai_last_sum_po_point;
			END IF;
			
			IF (@tai_po_no IS NOT NULL) THEN
				UPDATE pg_trade_member_point_balance SET po_total=@total_tai, po_on_trade=@total_tai_on, po_on_etc=@total_tai_on_withdraw, po_on_lend=@total_tai_on_lend, po_action=PARAM_ACTION
					,po_reg_dt= NOW(),po_last_sum_po_no=@tai_max_pono,po_last_sum_po_point=@total_tai
					WHERE po_type = 'tai' AND mb_no = PARAM_MB_NO;
			ELSE
				INSERT INTO pg_trade_member_point_balance (po_type,mb_no,po_total,po_on_trade,po_on_etc,po_on_lend,po_action,po_last_sum_po_no,po_last_sum_po_point) VALUES
					('tai',PARAM_MB_NO,@total_tai,@total_tai_on,@total_tai_on_withdraw,@total_tai_on_lend,PARAM_ACTION,@tai_max_pono,@total_tai);
			END IF;
			
			INSERT INTO pg_trade_member_point_balance_his (po_type,mb_no,po_total,po_on_trade,po_on_etc,po_on_lend,po_action,po_last_sum_po_no,po_last_sum_po_point) VALUES
				('tai',PARAM_MB_NO,@total_tai,@total_tai_on,@total_tai_on_withdraw,@total_tai_on_lend,PARAM_ACTION,@tai_max_pono,@total_tai);
		END IF;
        
        
        COMMIT;
		
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_set_point` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `proc_set_point`(

	IN `PARAM_PO_TYPE` VARCHAR(10),

	IN `PARAM_MB_NO` BIGINT,

	IN `PARAM_CONTENT` VARCHAR(100),

	IN `PARAM_POINT` DECIMAL(30,8),

	IN `PARAM_REL_ID` VARCHAR(50),

	IN `PARAM_REL_ACTION` VARCHAR(50),

	IN `PARAM_REG_IP` VARCHAR(30)



)
    COMMENT '관리자 페이지에서 포인트 삽입'
BEGIN



		DECLARE errno INT;

		DECLARE EXIT HANDLER FOR SQLEXCEPTION

		BEGIN

		GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;

		#SELECT errno AS MYSQL_ERROR;

		ROLLBACK;

		END;



		START TRANSACTION;

		

		

		SET @TBNAME = concat("web_point_",PARAM_PO_TYPE);

		SET @MEM_STMT = concat("INSERT INTO ",@TBNAME,"(mb_no,is_tracker,po_content,po_point, po_rel_id, po_rel_action,po_reg_ip) VALUES ");

		SET @MEM_STMT = concat(@MEM_STMT,"(" ,PARAM_MB_NO,",'N','", PARAM_CONTENT, "',", PARAM_POINT,",'", PARAM_REL_ID, "','", PARAM_REL_ACTION, "','" ,PARAM_REG_IP, "') ");





		PREPARE stmt1 FROM @MEM_STMT;

 		EXECUTE stmt1;

		

		SELECT LAST_INSERT_ID() INTO @PO_LAST_NO;

 		SELECT @PO_LAST_NO AS result;



		COMMIT;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spChangePassword` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spChangePassword`(
	IN IaccountID VARCHAR(100)
    , IN Ipassword VARCHAR(255)
    , IN Inewpassword VARCHAR(255)
)
main: BEGIN
	DECLARE ret_password varchar(255) default 0;

	SELECT `mem_password` into ret_password FROM `pg_member` WHERE mem_userid = IaccountID;
    if ret_password = Ipassword then
		update `pg_member` set `mem_password` = Inewpassword where mem_userid = IaccountID;
        select 0 as retcode;
    else
		select -1 as retcode;
    end if;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spEndRoundBattingInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spEndRoundBattingInfo`()
main : BEGIN 

	declare ret_idx bigint default 0;
    declare ret_start_line_time datetime(6) default '0000-00-00 00:00:00.000';
    declare ret_start_line_point decimal(10,4) default 0;
    declare ret_end_line_time datetime(6) default '0000-00-00 00:00:00.000';
    declare ret_end_line_point decimal(10,4) default 0;
    declare ret_fee float default 0;
    declare ret_total_point decimal(10,4) default 0;
    declare ret_over_point decimal(10,4) default 0;
    declare ret_under_point decimal(10,4) default 0;
    declare ret_result varchar(10) default '';     
    declare ret_create_date datetime default '1970-01-01 00:00:00';
    declare ret_curent_round_time datetime default '0000-00-00 00:00:00';
    declare ret_round_date datetime default '0000-00-00 00:00:00';
    declare ret_win_point decimal(10,4) default 0;
    declare retcode int default 1;
    declare ret_status varchar(10);
    declare ret_bonus_count int default 0;
    declare ret_bonus_round int default 0;
    declare ret_bonus_point decimal(30,8) default 0;
    
	select time_stamp,last_price into ret_end_line_time,ret_end_line_point from btc_last_price order by time_stamp desc limit 1;
    
    SELECT DATE_ADD(NOW(), INTERVAL -10 SECOND) into ret_curent_round_time;

	select date_format(ret_curent_round_time, '%Y-%m-%d %H:%i') into ret_round_date;
    
	update round_batting_info set end_line_time = ret_end_line_time,end_line_point = ret_end_line_point where type = 'BTC_USD' and round_date = ret_round_date;
     
    select idx
    ,start_line_point
    ,end_line_point
    ,total_point,over_point,under_point,create_date 
    into ret_idx
    ,ret_start_line_point
    ,ret_end_line_point
    ,ret_total_point,ret_over_point,ret_under_point,ret_create_date
    from round_batting_info where type = 'BTC_USD' and round_date = ret_round_date;
      
    select wa_tx_fee into ret_fee from pg_web_config_wallet_server where po_type = 'BTC';
           
   /*if ret_start_line_point < ret_end_line_point then -- OVER
		set ret_result = 'OVER';
        set ret_win_point = ret_under_point;
    elseif  ret_start_line_point > ret_end_line_point then -- UNDER
        set ret_result = 'UNDER';
        set ret_win_point = ret_over_point;
    else 
		set ret_result = 'TIE';
        set retcode = 10;
    end if;*/
    
    -- if ret_under_point = 0 or ret_over_point = 0 then
	-- set ret_win_point = 0;
    -- set retcode = 10;
    -- end if;
    
    if ret_start_line_point <= ret_end_line_point then -- OVER
		set ret_result = 'OVER';
        set ret_win_point = ret_under_point;
    else  -- UNDER
        set ret_result = 'UNDER';
        set ret_win_point = ret_over_point;
   end if;
	    
    update batting_user_info set result = ret_result where round_idx = ret_idx;
    
    if retcode <> 10 then
		update batting_user_info set batting_user_info.take_point = -batting_user_info.amount where round_idx = ret_idx and batting_user_info.batting_type <> ret_result ;
    end if;
    
    -- event
    select bonus_count, bonus_round, bonus_point into ret_bonus_count, ret_bonus_round, ret_bonus_point from event_config where idx = 0;
    if 0 = ret_bonus_round or ret_idx = ret_bonus_round then
		update event_config set bonus_round = ret_idx + ret_bonus_count, bonus_point = 0 where idx  = 0;
	end if;
    
    select retcode,ret_idx,ret_win_point,'BTC_USD' as type
    ,ret_end_line_time
    ,ret_start_line_point
    ,ret_end_line_point
    ,ret_total_point
    ,ret_over_point
    ,ret_under_point
    ,ret_fee,ret_result,ret_create_date
    ,ret_bonus_round
    ,ret_bonus_point;
    
    select mem_userid, event_user.mem_id from event_user join pg_member on event_user.mem_id = pg_member.mem_id where round_idx = 72492;
        		  
    select batting_user_info.*, pg_member.mem_userid from batting_user_info join pg_member on batting_user_info.mb_no = pg_member.mem_id where round_idx = ret_idx and type = 'BTC_USD'; -- and batting_type = ret_result;
    
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spGetCurrentRoundInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spGetCurrentRoundInfo`()
BEGIN
	    
    declare ret_round_date datetime default '0000-00-00 00:00:00';
    select date_format(now(), '%Y-%m-%d %H:%i') into ret_round_date;
    select total_point,over_point,under_point,bot_total_over,bot_total_under  from round_batting_info where round_batting_info.type = 'BTC_USD' and round_date = ret_round_date;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spGetRoundStartPointInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spGetRoundStartPointInfo`(
in add_amount decimal(30,4)
)
BEGIN
      
      declare ret_start_line_point decimal(10,4) default 0;
      declare ret_start_line_time datetime(6) default '0000-00-00 00:00:00.000';
	  declare ret_round_date datetime default '0000-00-00 00:00:00';
      declare ret_day_date datetime default '0000-00-00 00:00:00';
      declare ret_idx bigint(20) default 0;
      
      select date_format(now(), '%Y-%m-%d %H:%i') into ret_round_date;
      
      select date_format(now(), '%Y-%m-%d') into ret_day_date;
      
	  select time_stamp,last_price into ret_start_line_time,ret_start_line_point from btc_last_price order by time_stamp desc limit 1;
      
      update round_batting_info set 
      start_line_time = ret_start_line_time
      ,start_line_point = ret_start_line_point
      where type = 'BTC_USD' and round_date = ret_round_date;
      		
      update batting_day_info set batting_day_info.add_amount = batting_day_info.add_amount + add_amount  
      where batting_day_info.type = 'BTC_USD' and create_date =  ret_day_date;  
     
      select 'BTC_USD' as type,ret_start_line_point,ret_start_line_time;
      
      -- event
      select idx into ret_idx from round_batting_info where type = 'BTC_USD' and round_date = ret_round_date;
	  delete from event_user;
	  insert into event_user(round_idx,mem_id)
	  select ret_idx, mb_no from batting_user_info where round_idx = ret_idx order by rand() limit 1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spInitDataBaseData` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spInitDataBaseData`()
BEGIN
	truncate batting_user_info;
	truncate btc_last_price;
	truncate btc_last_price_minute;
	truncate btc_last_price_ten_minute;
	truncate log_take_batting;
	truncate pg_log_point_balance;
	truncate pg_user_point_balance;
	truncate pg_web_member_desposit_wallet;
	truncate pg_web_wallet_deposits; 
	truncate pg_web_wallet_withdrawals; 
	truncate round_batting_info; 
    truncate batting_round_user_info;
    truncate log_admin_history;
    truncate pg_member;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spInsertBattingOrder` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spInsertBattingOrder`(
  IN IaccountID VARCHAR(100)
,in type varchar(10)
,in batting_type varchar(10)
, in amount int
, in ratio float
)
main : BEGIN
 
	DECLARE retcode int default 1;
    DECLARE ret_mem_id int default 0;
	declare ret_po_amount decimal(30,8);
    DECLARE ret_round_total_point decimal(10,4) default 0;
    DECLARE ret_over_point decimal(10,4) default 0;
    DECLARE ret_under_point decimal(10,4) default 0;
	DECLARE ret_day_total_point decimal(10,4) default 0;
    DECLARE ret_idx bigint default 0;
    DECLARE ret_fee decimal(10,8) default 0;
    declare ret_round_date datetime default '0000-00-00 00:00:00';
	declare ret_day_date datetime default '0000-00-00 00:00:00';
    declare ret_current_date datetime default '0000-00-00 00:00:00';
    
    declare ret_add_amount decimal(30,4) default 0;
    
     
    declare ret_batting_round_user_idx bigint default 0;
    declare ret_my_total_over decimal(10,4) default 0;
    declare ret_my_total_under decimal(10,4) default 0;
    declare ret_mem_denied tinyint(4) default 0;
    -- declare ret_sum_my_total_over decimal(10,4) default 0;
 
    declare ret_status tinyint(4) default 0;
    declare ret_start_date datetime default '0000-00-00 00:00:00';
    declare ret_end_date datetime default '0000-00-00 00:00:00';
	declare ret_now_date datetime default '0000-00-00 00:00:00';
    declare ret_mem_level mediumint(6) default 0;
    
    declare ret_roof_parents int(11) default 0;
    
    declare ret_parents int(11) default 0;
    DECLARE ret_mem_id_parents int default 0;
    declare ret_first_parents int(11) default 0;
    declare ret_two_parents int(11) default 0;
    declare ret_third_parents int(11) default 0;
   
	declare ret_rate int(11) default 0;
    declare ret_ihead_rate int(11) default 0;
    declare ret_first_rate int(11) default 0;
    declare ret_two_rate int(11) default 0;
    declare ret_third_rate int(11) default 0;
    
	declare ret_last_betting_idx bigint default 0;
    
    declare ret_ihead_mem_level mediumint(6) default 0;
    declare ret_first_mem_level mediumint(6) default 0;
    declare ret_two_mem_level mediumint(6) default 0;
    declare ret_third_mem_level mediumint(6) default 0;
    
    DECLARE ret_ihead_mem_id int default 0;
    DECLARE ret_first_mem_id int default 0;
    DECLARE ret_two_mem_id int default 0;
    DECLARE ret_third_mem_id int default 0;
    
    declare ihead_amount decimal(10,4) default 0;
    declare first_amount decimal(10,4) default 0;
    declare two_amount decimal(10,4) default 0;
    declare third_amount decimal(10,4) default 0;
    declare ret_tree_count int default 0;
    declare ret_day varchar(2) default '';
    declare ret_bonus_round int default 0;
    
    SELECT mem_id,mem_denied,mem_level,parents into ret_mem_id,ret_mem_denied,ret_mem_level,ret_parents FROM `pg_member` WHERE mem_userid = IaccountID;
    
	if ret_mem_id = 0 or ret_mem_id is null then
		set retcode = -101;
		select retcode;
        leave main;
    end if;
    
    if ret_mem_denied <> 0 then
        set retcode = -106;
        select retcode;
        leave main;
    end if;
        
     select status,end_date,end_date,now() into ret_status,ret_end_date,ret_end_date,ret_now_date from pg_web_server_status;
    
    -- if ret_status = 2 or (ret_status = 1 and 100 <> ret_mem_level) 
     -- and ret_start_date <= ret_now_date and ret_now_date <  ret_end_date 
    -- then
    	-- set retcode = -105;
        -- select retcode;
        -- leave main;
    -- end if;
    
	if 1 <> ret_mem_level and 10 <> ret_mem_level then
    	set retcode = -301;
        select retcode;
        leave main;
    end if;
    select now() into ret_current_date;
	select date_format(ret_current_date, '%Y-%m-%d %H:%i') into ret_round_date;
	select po_amount into ret_po_amount from pg_user_point_balance where mb_no = ret_mem_id and po_type  = type;
	select wa_tx_fee into ret_fee from pg_web_config_wallet_server where po_type = 'BTC';
	if ret_po_amount < amount then
		SET retcode = -130; -- 보유금액이 배팅 금액보다 작습ㄴ다.
		SELECT retcode;
		leave main;
	end if;
	
	set ret_po_amount = ret_po_amount - amount;
	
	select idx,total_point,over_point,under_point into ret_idx,ret_round_total_point,ret_over_point,ret_under_point  
    from round_batting_info where round_batting_info.type = type and round_date = ret_round_date;
   
	set ret_round_total_point = ret_round_total_point + amount;
	if 'OVER' = batting_type then
		set ret_over_point = ret_over_point + amount;
	
	else 
		set ret_under_point = ret_under_point + amount;
	end if;
	
	select date_format(ret_current_date, '%Y-%m-%d') into ret_day_date;
	
	update pg_user_point_balance set  po_amount = po_amount - amount where  mb_no = ret_mem_id and po_type  = type;
	
    update round_batting_info set  total_point = ret_round_total_point,over_point = ret_over_point,under_point = ret_under_point where idx = ret_idx;
    
	INSERT INTO batting_user_info (round_idx,mb_no, type, batting_type, amount,ratio,fee,bf_balance) VALUES (ret_idx,ret_mem_id, type, batting_type, amount,ratio,ret_fee,ret_po_amount);
	set ret_last_betting_idx = last_insert_id();
    
    select total_amount,add_amount into ret_day_total_point,ret_add_amount from batting_day_info where batting_day_info.type = type and create_date =  ret_day_date;
    set ret_day_total_point = ret_day_total_point + amount;
        
    SELECT mem_id,mem_level,vip_rate into ret_ihead_mem_id,ret_ihead_mem_level,ret_ihead_rate FROM `pg_member` WHERE mem_level = 100;    -- ihead
	
    if ret_parents <> 1  then 
	  
        SELECT mem_id,mem_level,parents,vip_rate into ret_mem_id_parents,ret_mem_level,ret_roof_parents,ret_rate FROM `pg_member` WHERE mem_id = ret_parents; -- silver
             
        SELECT mem_id,mem_level,parents,vip_rate into ret_first_mem_id,ret_first_mem_level,ret_first_parents,ret_first_rate FROM `pg_member` WHERE mem_id = ret_roof_parents; -- gold
        
        SELECT mem_id,mem_level,parents,vip_rate into ret_two_mem_id,ret_two_mem_level,ret_two_parents,ret_two_rate FROM `pg_member` WHERE mem_id = ret_first_parents; -- vip
        
        SELECT mem_id,mem_level,parents,vip_rate into ret_third_mem_id,ret_third_mem_level,ret_third_parents,ret_third_rate FROM `pg_member` WHERE mem_id = ret_two_parents;
        
        if ret_first_mem_id = ret_ihead_mem_id then -- 
            set ret_tree_count = 1;
		    set ihead_amount = amount * ((100 - ret_rate)*0.01);
            set first_amount = (amount - ihead_amount);
            update shop_betting_day_info set total_amount = total_amount + first_amount,total_fee = total_fee + first_amount *( ret_fee*0.01) 
            where mem_id = ret_mem_id_parents and shop_betting_day_info.type = type and create_date =  ret_day_date;
            insert into pg_user_point_balance(mb_no,po_type,po_amount) values(ret_mem_id_parents,type,first_amount *( ret_fee*0.01)) 
            ON DUPLICATE KEY UPDATE po_amount = po_amount + first_amount *( ret_fee*0.01),update_date = ret_current_date;
                                    
            SELECT date_format(ret_day_date, '%d') into ret_day;
            insert into pg_log_betting_info(pkey,create_date,betting_id,mem_id,pg_log_betting_info.type,pg_log_betting_info.batting_type,amount,take_poin,fee)values
            (ret_day,ret_current_date,ret_last_betting_idx,ret_ihead_mem_id,type,batting_type,amount,ihead_amount,ret_fee),
            (ret_day,ret_current_date,ret_last_betting_idx,ret_mem_id_parents,type,batting_type,amount,first_amount,ret_fee);
        elseif ret_two_mem_id = ret_ihead_mem_id then
			set ret_tree_count = 2;
		    set ihead_amount = amount * ((100 - ret_first_rate)*0.01);
			set first_amount = (amount-ihead_amount) * ((100 - ret_rate)*0.01); -- vip 
			set two_amount =  (amount-ihead_amount-first_amount); -- gold
			            
			update shop_betting_day_info set total_amount = total_amount + first_amount,total_fee = total_fee + first_amount *( ret_fee*0.01) 
			where mem_id = ret_first_mem_id and shop_betting_day_info.type = type and create_date =  ret_day_date;
			
            insert into pg_user_point_balance(mb_no,po_type,po_amount) values(ret_first_mem_id,type,first_amount *( ret_fee*0.01)) 
            ON DUPLICATE KEY UPDATE po_amount = po_amount + first_amount *( ret_fee*0.01),update_date = ret_current_date;
            
			update shop_betting_day_info set total_amount = total_amount + two_amount,total_fee = total_fee + two_amount *( ret_fee*0.01) 
			where mem_id = ret_mem_id_parents and shop_betting_day_info.type = type and create_date =  ret_day_date;
			
            insert into pg_user_point_balance(mb_no,po_type,po_amount) values(ret_mem_id_parents,type,two_amount *( ret_fee*0.01)) 
            ON DUPLICATE KEY UPDATE po_amount = po_amount + two_amount *( ret_fee*0.01),update_date = ret_current_date;
            
			SELECT date_format(ret_day_date, '%d') into ret_day;
			insert into pg_log_betting_info(pkey,create_date,betting_id,mem_id,pg_log_betting_info.type,pg_log_betting_info.batting_type,amount,take_poin,fee)values
			(ret_day,ret_current_date,ret_last_betting_idx,ret_ihead_mem_id,type,batting_type,amount,ihead_amount,ret_fee),
			(ret_day,ret_current_date,ret_last_betting_idx,ret_first_mem_id,type,batting_type,amount,first_amount,ret_fee),
			(ret_day,ret_current_date,ret_last_betting_idx,ret_mem_id_parents,type,batting_type,amount,two_amount,ret_fee);
        elseif ret_third_mem_id = ret_ihead_mem_id then
            set ret_tree_count = 3;
            set ihead_amount = amount * ((100 - ret_two_rate)*0.01);
            set first_amount = (amount-ihead_amount) * ((100 - ret_first_rate)*0.01);
            set two_amount =  (amount-ihead_amount-first_amount) * ((100 - ret_rate)*0.01);
            set third_amount =  (amount-ihead_amount-first_amount-two_amount);
                        
            update shop_betting_day_info set total_amount = total_amount + first_amount,total_fee = total_fee + first_amount *( ret_fee*0.01) 
            where mem_id = ret_two_mem_id and shop_betting_day_info.type = type and create_date =  ret_day_date;
            
            insert into pg_user_point_balance(mb_no,po_type,po_amount) values(ret_two_mem_id,type,first_amount *( ret_fee*0.01)) 
            ON DUPLICATE KEY UPDATE po_amount = po_amount + first_amount *( ret_fee*0.01),update_date = ret_current_date;
            
            update shop_betting_day_info set total_amount = total_amount + two_amount,total_fee = total_fee + two_amount *( ret_fee*0.01) 
            where mem_id = ret_first_mem_id and shop_betting_day_info.type = type and create_date =  ret_day_date;
            
            insert into pg_user_point_balance(mb_no,po_type,po_amount) values(ret_first_mem_id,type,two_amount *( ret_fee*0.01)) 
            ON DUPLICATE KEY UPDATE po_amount = po_amount + two_amount *( ret_fee*0.01),update_date = ret_current_date;
            
            update shop_betting_day_info set total_amount = total_amount + third_amount,total_fee = total_fee + third_amount *( ret_fee*0.01) 
            where mem_id = ret_mem_id_parents and shop_betting_day_info.type = type and create_date =  ret_day_date;
			
            insert into pg_user_point_balance(mb_no,po_type,po_amount) values(ret_mem_id_parents,type,third_amount *( ret_fee*0.01)) 
            ON DUPLICATE KEY UPDATE po_amount = po_amount + third_amount *( ret_fee*0.01),update_date = ret_current_date;
            
            SELECT date_format(ret_day_date, '%d') into ret_day;
            insert into pg_log_betting_info(pkey,create_date,betting_id,mem_id,pg_log_betting_info.type,pg_log_betting_info.batting_type,amount,take_poin,fee)values
            (ret_day,ret_current_date,ret_last_betting_idx,ret_ihead_mem_id,type,batting_type,amount,ihead_amount,ret_fee),
            (ret_day,ret_current_date,ret_last_betting_idx,ret_first_mem_id,type,batting_type,amount,two_amount,ret_fee),
            (ret_day,ret_current_date,ret_last_betting_idx,ret_two_mem_id,type,batting_type,amount,first_amount,ret_fee),
            (ret_day,ret_current_date,ret_last_betting_idx,ret_mem_id_parents,type,batting_type,amount,third_amount,ret_fee);
        end if;
    else -- main shop 
		set ihead_amount = amount;
    end if;
    
    -- set ihead_amount = amount;
    update shop_betting_day_info set total_amount = total_amount + ihead_amount,total_fee = total_fee + ihead_amount *( ret_fee*0.01) 
            where mem_id = ret_ihead_mem_id and shop_betting_day_info.type = type and create_date =  ret_day_date;
            
	-- event
    select bonus_round into ret_bonus_round from event_config where idx = 0;
    if ret_idx = ret_bonus_round then
		update pg_user_point_balance set po_amount = po_amount + ret_bonus_point where mb_no in (select mem_id from event_user where round_idx = ret_idx and mem_id in(SELECT mem_id FROM db_exchange_option.batting_user_info where round_idx = ret_idx and batting_type != result));
		update event_config set bonus_point = bonus_point + (ihead_amount * ( ret_fee*0.01)) where idx = 0;
	end if;
    
    insert into pg_user_point_balance(mb_no,po_type,po_amount) values(ret_ihead_mem_id,type,ihead_amount *( ret_fee*0.01)) 
	ON DUPLICATE KEY UPDATE po_amount = po_amount + ihead_amount *( ret_fee*0.01),update_date = ret_current_date;
            
	update batting_day_info set total_amount = ret_day_total_point,total_fee = total_fee + ihead_amount *( ret_fee*0.01) where batting_day_info.type = type and create_date =  ret_day_date;
	
	select round_idx,total_over,total_under into ret_batting_round_user_idx,ret_my_total_over,ret_my_total_under from batting_round_user_info where round_idx = ret_idx and mb_no = ret_mem_id;

	if ret_batting_round_user_idx is null or ret_batting_round_user_idx = 0 then
	
		if 'OVER' = batting_type then
			set ret_my_total_over = amount;
			set ret_my_total_under = 0;
		else 
			set ret_my_total_under = amount;
			set ret_my_total_over =0;
		end if;
	
		INSERT INTO batting_round_user_info (round_idx,mb_no,total_over, total_under) VALUES (ret_idx,ret_mem_id, ret_my_total_over,ret_my_total_under);
	
	else 
		if 'OVER' = batting_type then
			set ret_my_total_over = ret_my_total_over + amount;
		
		else 
			set ret_my_total_under = ret_my_total_under + amount;
			
		end if;
		
		update batting_round_user_info set total_over = ret_my_total_over,total_under = ret_my_total_under  where round_idx = ret_idx and mb_no = ret_mem_id;
	
	end if;
	 -- select sum(amount) into ret_sum_my_total_over from batting_user_info 
	 -- where round_idx = ret_idx and mb_no = ret_mem_id and batting_user_info.batting_type = batting_type;
	
   -- select sum(amount) into ret_my_total_under from batting_user_info 
   -- where round_idx = ret_idx and batting_user_info.batting_type = batting_type;
	
	-- last_login update
    UPDATE `pg_member` SET mem_lastlogin_datetime = now() WHERE mem_id = ret_mem_id;
	
    select retcode,amount,batting_type,ret_mem_id,ret_idx,ret_po_amount,type,ret_round_total_point,ret_over_point,ret_under_point,ret_day_total_point
    ,ret_add_amount,ret_my_total_over,ret_my_total_under,ret_day_date,ret_fee;
    
    
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spInsertLastPriceData` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `spInsertLastPriceData`(
IN type VARCHAR(10),
IN time_stamp varchar(32),
IN last_price DECIMAL(10,4),
IN last_price_real DECIMAL(10,4),
in current_user_count int


)
BEGIN

IF type = 'XBTUSD' OR type = 'BTC' THEN
	INSERT INTO `btc_last_price` (time_stamp, last_price,last_price_real)
		VALUES (time_stamp, last_price,last_price_real);
END IF;
	
    update system_info set system_info.current_user_count = current_user_count;
select 1 as retcode;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spInsertRoundBattingInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `spInsertRoundBattingInfo`()
BEGIN
	  
      declare ret_time_stamp datetime(6) default '0000-00-00 00:00:00.000';
      declare ret_last_price decimal(10,4) default 0;
      declare ret_start_line_time datetime default '0000-00-00 00:00:00';
      declare ret_end_line_time datetime default '0000-00-00 00:00:00';
      declare ret_time_diff int default 0;
      START TRANSACTION;
		  select start_line_time,end_line_time into ret_start_line_time,ret_end_line_time from round_batting_info order by idx desc limit 1 for update;
		  
		  
		  select time_stamp,last_price into ret_time_stamp,ret_last_price from btc_last_price order by time_stamp desc limit 1;
		  
		  -- SELECT TIMESTAMPDIFF(second,ret_time_stamp, ret_start_line_time)  into ret_time_diff;
		  
		  if  ret_end_line_time is not null then
			INSERT INTO round_batting_info (type,start_line_point, start_line_time) VALUES ('BTC_USD',ret_last_price,ret_time_stamp);
		  end if;
		  
      COMMIT;
      select 'BTC_USD' as type,ret_time_stamp,ret_last_price,ret_start_line_time;
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spLogin` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spLogin`(
    IN IaccountID VARCHAR(100)
    , IN IuserIP VARCHAR(50)
    -- , IN nPo_type varchar(10)
)
main: BEGIN
	declare ret_last_price_real decimal(10,4) default 10000;
	
    declare ret_code int(11) default 1;
	
    declare ret_mem_denied tinyint(4) default 0;
    declare ret_mem_id int(11) default 0;
    declare ret_status tinyint(4) default 0;
    declare ret_start_date datetime default '0000-00-00 00:00:00';
    declare ret_end_date datetime default '0000-00-00 00:00:00';
    declare ret_now_date datetime default '0000-00-00 00:00:00';
    declare ret_mem_level mediumint(6) default 0; 
    declare ret_parents int default 0; 
    
    declare vip_id varchar(100) default ''; 
   -- declare ret_now_date datetime default '0000-00-00 00:00:00';
        
    SELECT mem_id,mem_denied,mem_level,parents into ret_mem_id,ret_mem_denied,ret_mem_level,ret_parents FROM `pg_member` WHERE mem_userid = IaccountID;
    if ret_mem_id = 0 or ret_mem_id is null then
		set ret_code = -101;
        select ret_code;
        leave main;
    end if;
           
    if ret_mem_denied <> 0 then
		set ret_code = -106;
        select ret_code;
        leave main;
    end if;
        
    select status,end_date,end_date,now() into ret_status,ret_end_date,ret_end_date,ret_now_date from pg_web_server_status;
    
    -- if ret_status = 2 or (ret_status = 1 and 100 <> ret_mem_level) 
    -- and ret_start_date <= ret_now_date and ret_now_date <  ret_end_date 
    -- then
    	-- set ret_code = -105;
        -- select ret_code;
        -- leave main;
    -- end if;
    
    UPDATE `pg_member` SET mem_lastlogin_ip = IuserIP, mem_lastlogin_datetime = now() WHERE mem_userid = IaccountID;
	
    
    -- if ret_mem_level = 80 then
		-- SELECT mem_userid into vip_id FROM `pg_member` WHERE mem_id = ret_parents;
    -- end if;
    -- select last_price_real into ret_last_price_real from btc_last_price order by time_stamp desc limit 1;
     
    SELECT ret_code,vip_id,pg_member.mem_id,pg_user_point_balance.po_type, pg_user_point_balance.po_amount, pg_member.mem_password, pg_member.otpKey, ret_last_price_real,ret_mem_level
		FROM pg_member left join pg_user_point_balance  on pg_member.mem_id = pg_user_point_balance.mb_no 
        WHERE pg_member.mem_userid = IaccountID;

    -- select now() into ret_now_date;
    select * from pg_web_notice where mem_id = ret_parents and start_date < ret_now_date and ret_now_date <= end_date;

END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSelectBattingInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `spSelectBattingInfo`(
 IN IaccountID VARCHAR(100)
,in type varchar(10)
)
BEGIN

	declare ret_mem_id int default 0;
    select mem_id into ret_mem_id from pg_member where mem_userid = IaccountID;
    select * from batting_user_info where mb_no = ret_mem_id and batting_user_info.type = type order by create_date desc limit 0,99;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSelectCurrentRoundInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `spSelectCurrentRoundInfo`(
in type varchar(10)
,in accountID varchar(100)
)
BEGIN
 declare ret_my_total_point decimal(10,4) default 0;
 declare ret_day_total_amount decimal(10,4) default 0;
 declare ret_idx int default 0;
 declare ret_mem_id int default 0;
  
 declare ret_start_line_point decimal(10,4) default 0;
 declare ret_end_line_point decimal(10,4) default 0;
 declare ret_pre_start_line_point decimal(10,4) default 0;
 declare ret_pre_end_line_point decimal(10,4) default 0;
 
 declare ret_total_point decimal(10,4) default 0;
 declare ret_over_point decimal(10,4) default 0;
 declare ret_under_point decimal(10,4) default 0;
 
 
 
 declare ret_my_over_point decimal(10,4) default 0;
 declare ret_my_under_point decimal(10,4) default 0;
 
 
 declare ret_round_date datetime default '0000-00-00 00:00:00';
 declare ret_now_date datetime default '0000-00-00 00:00:00';
 declare ret_curent_round_time datetime default '0000-00-00 00:00:00';
 declare ret_add_amount decimal(30,4) default 0;
 declare ret_day_date datetime default '0000-00-00 00:00:00';
 DECLARE ret_fee decimal(10,8) default 0;
  
 select wa_tx_fee into ret_fee from pg_web_config_wallet_server where po_type = 'BTC';
 
 select date_format(now(), '%Y-%m-%d') into ret_day_date;
 
 select now() into ret_now_date;
 select date_format(ret_now_date, '%Y-%m-%d %H:%i') into ret_round_date;
    
 select total_amount,add_amount into ret_day_total_amount,ret_add_amount from batting_day_info where batting_day_info.type = type and create_date = ret_day_date; 	
    
 select idx, start_line_point,end_line_point,total_point,over_point,under_point 
 into ret_idx,ret_start_line_point,ret_end_line_point,ret_total_point,ret_over_point,ret_under_point
 from round_batting_info where round_batting_info.type = type and round_date = ret_round_date;
 
 SELECT DATE_ADD(ret_now_date, INTERVAL -59 SECOND) into ret_curent_round_time;
 select date_format(ret_curent_round_time, '%Y-%m-%d %H:%i') into ret_round_date;
 
 select start_line_point,end_line_point
 into ret_pre_start_line_point,ret_pre_end_line_point
 from round_batting_info where round_batting_info.type = type and round_date = ret_curent_round_time;
   
 select mem_id into ret_mem_id from pg_member where mem_userid = accountID;
 
 select sum(amount) into ret_my_total_point 
 from batting_user_info 
 where round_idx =  ret_idx and mb_no = ret_mem_id and batting_user_info.type = type;
 
 select total_over,total_under into ret_my_over_point,ret_my_under_point from  batting_round_user_info where round_idx = ret_idx and mb_no = ret_mem_id;
 
 select ret_my_total_point,ret_day_total_amount,ret_add_amount,ret_start_line_point,ret_end_line_point,ret_total_point,ret_over_point,ret_under_point
 ,ret_pre_start_line_point,ret_pre_end_line_point,ret_my_over_point,ret_my_under_point,ret_fee;
 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSelectLastPriceInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `spSelectLastPriceInfo`(
in type varchar(10)
,in minute int
)
BEGIN
	
    declare ret_now datetime(6) default '0000-00-00 00:00:00.000';
        
    SELECT DATE_ADD(NOW(), INTERVAL -minute MINUTE) into ret_now;

	select time_stamp,last_price from btc_last_price where  time_stamp > ret_now;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSelectUserPointInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `spSelectUserPointInfo`(
in accountID varchar(100)
,in type varchar(10)
)
BEGIN

declare ret_last_price_real decimal(10,4) default 0;
declare ret_mem_id int default 0;
declare ret_po_amount decimal(30,8) default 0;
declare ret_withdraw_fee decimal(30,8) default 0;

declare ret_mw_address varchar(200) default '';
declare ret_qr_code text;
declare ret_mem_level mediumint(6) default 0;


select wa_withdraw_fee into ret_withdraw_fee from pg_web_config_wallet_server where po_type = "btc";

select mem_id,mem_level into ret_mem_id,ret_mem_level from pg_member where mem_userid = accountID;

if 90 = ret_mem_level or 80 = ret_mem_level or 70 = ret_mem_level then
	set ret_withdraw_fee = 0;
end if;
-- select last_price_real into ret_last_price_real from btc_last_price order by time_stamp desc limit 1;
select po_amount into ret_po_amount from pg_user_point_balance where mb_no = ret_mem_id and po_type = type;

select mw_address,qr_code into ret_mw_address,ret_qr_code from pg_web_member_desposit_wallet where mb_no = ret_mem_id and po_type = 'BTC';

set ret_last_price_real = 10000;

select ret_withdraw_fee,ret_mw_address as mw_address,ret_qr_code as qr_code,ret_po_amount,ret_last_price_real,type,'BTC' as po_type;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSelectWalletHistory` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spSelectWalletHistory`(
	in nMem_id 	int
    ,in nType	varchar(10)
)
BEGIN

SELECT po_type, od_reg_dt, 'Deposits' as type, od_amount, od_fee, od_status, 0 as point_fee FROM pg_web_wallet_deposits where mb_no = nMem_id and po_type = nType
union
SELECT po_type, od_reg_dt, 'Withdraw' as type, po_amount as od_amount, od_fee, od_status, point_fee FROM pg_web_wallet_withdrawals where mb_no = nMem_id and po_type = nType
order by od_reg_dt desc limit 100;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSetTempPassword` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spSetTempPassword`(
	IN IaccountID VARCHAR(100)
    , IN Itemppassword VARCHAR(255)
)
BEGIN
	DECLARE ret_password varchar(255) default 0;

	if exists(SELECT mem_id FROM `pg_member` WHERE mem_userid = IaccountID) then
		update `pg_member` set `mem_password` = Itemppassword where mem_userid = IaccountID;
        select 0 as retcode;
    else
		select -201 as retcode;
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSignup` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spSignup`(
    IN IaccountID VARCHAR(100)
    , IN Ipassword VARCHAR(255)
    , IN IotpKey VARCHAR(64)
    , IN IuserIP VARCHAR(50)
    , in Iparents int(11)
)
main: BEGIN

	DECLARE retcode int default 0;
 	declare ret_mem_denied tinyint(4) default 0;
    declare ret_mem_id int(11) default 0;
    declare ret_status tinyint(4) default 0;
    declare ret_start_date datetime default '0000-00-00 00:00:00';
    declare ret_end_date datetime default '0000-00-00 00:00:00';
	declare ret_now_date datetime default '0000-00-00 00:00:00';
    
    declare ret_mem_level mediumint(6) default 0; 
        
    SELECT mem_id into ret_mem_id FROM `pg_member` WHERE mem_userid = IaccountID;
    
    if ret_mem_id > 0 /*or ret_mem_id is not null*/ then
		SET retcode = -11; -- 같은 계정이 있다
        select retcode,ret_mem_id;
        leave main;
    end if;
 
    select status,start_date,end_date,now() into ret_status,ret_start_date,ret_end_date,ret_now_date from pg_web_server_status;
    
    if ret_status <> 0 and ret_start_date <= ret_now_date and ret_now_date <  ret_end_date then
    	set retcode = -105;
        select retcode;
        leave main;
    end if;
    
	-- IF NOT EXISTS (SELECT `mem_id` FROM `pg_member` WHERE mem_userid = IaccountID) THEN	
        		
		INSERT INTO `pg_member` (mem_userid, mem_password, otpKey, mem_register_ip,mem_level,mem_register_datetime,parents)
		VALUES (IaccountID, Ipassword, IotpKey, IuserIP, 1,now(),Iparents);
		
        SET ret_mem_id = LAST_INSERT_ID(); 
        
        -- 코인 추가시 추가해줘야 한다.
        INSERT INTO pg_user_point_balance(mb_no, po_type, update_date, create_date) values (ret_mem_id, 'BTC_USD', now(), now());
        
		SELECT retcode, ret_mem_id;
		
    -- ELSE
		-- SET retcode = -11; -- 같은 계정이 있다
		-- SELECT retcode;
    -- END IF;

END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spStaticTable` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `spStaticTable`(
)
main: BEGIN

	SELECT systemKey1, systemKey2, systemKey3, fee FROM `system_info`;
	-- SELECT `index`, accountID, nickName, `password`, otpKey, parentName, money, bitcoin  FROM `user_info`;
/*    
    SELECT ip FROM `web_master`.`block_ip_info`;
    SELECT accountID, nickName FROM `web_master`.`block_id_info`;
    SELECT accountID, nickName FROM `web_master`.`blocK_chat_info`;
*/
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spUpdateDayBettingInfo` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spUpdateDayBettingInfo`(
 in add_amount decimal(30,4),
 in type varchar(45)
 ,in bot_total_under decimal(10,4)
 ,in bot_total_over decimal(10,4)
)
BEGIN

	declare ret_day_date datetime default '0000-00-00 00:00:00';
    DECLARE ret_fee decimal(10,8) default 0;
	declare ret_round_date datetime default '0000-00-00 00:00:00';
	select date_format(now(), '%Y-%m-%d') into ret_day_date;
    select date_format(now(), '%Y-%m-%d %H:%i') into ret_round_date;
    update batting_day_info set batting_day_info.add_amount =  batting_day_info.add_amount + add_amount * 65
    where batting_day_info.type = type and create_date =  ret_day_date;
    
    update round_batting_info set round_batting_info.bot_total_over = bot_total_over,round_batting_info.bot_total_under = bot_total_under where round_date = ret_round_date;
    
    select wa_tx_fee into ret_fee from pg_web_config_wallet_server where po_type = 'BTC';
    
    select total_amount,batting_day_info.add_amount,ret_fee from batting_day_info  where batting_day_info.type = type and create_date =  ret_day_date;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spUpdateQrCode` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spUpdateQrCode`(
in accountID varchar(100)
,in qrcode text
)
BEGIN

	declare ret_mem_id int default 0;
    
    select mem_id into ret_mem_id from pg_member where mem_userid = accountID;
    
    update pg_web_member_desposit_wallet set qr_code = qrcode where mb_no = ret_mem_id;
    
    select 1 as retcode;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spUpdateUserBalance` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spUpdateUserBalance`(
in idx bigint
,in type varchar(10)
,in mb_no int
,in take_point decimal(10,4)
,in round_idx bigint
,in batting_type varchar(10)
,in amount decimal(10,4)
,in fee decimal(10,4)
,in result varchar(10)
,in batting_date datetime
,in ratio float
,in total_amount decimal(20,4)
,in my_batting_amount decimal(10,4)
,in bonus_round int
,in bonus_point decimal(30,4)

)
BEGIN
	
    declare ret_amount decimal(30,8) default 0;
    declare ret_bonus_point decimal(30,8) default 0;
    
    select po_amount into ret_amount from pg_user_point_balance where pg_user_point_balance.mb_no = mb_no and po_type = type;
    
    if bonus_round = round_idx then
		if exists(select mem_id from event_user where round_idx = round_idx) then
			set ret_bonus_point = bonus_point;
        end if;
    end if;
    
    set ret_amount = ret_amount + take_point + ret_bonus_point;
    -- set ret_amount = ret_amount + take_point;
    update pg_user_point_balance set po_amount = po_amount + take_point + ret_bonus_point, update_date = now() where pg_user_point_balance.mb_no = mb_no and po_type = type;
    
    update batting_user_info set batting_user_info.take_point = take_point,batting_user_info.ratio=ratio,af_balance = ret_amount where batting_user_info.idx = idx;
    
   -- INSERT INTO log_take_batting (round_idx,type,batting_type,mb_no,take_point,amount,fee,result,batting_date,ratio,total_amount,my_batting_amount) 
   -- VALUES (round_idx,type,batting_type,mb_no,take_point,amount,fee,result,batting_date,ratio,idx,my_batting_amount);
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spUserOtpChange` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `spUserOtpChange`(
    IN IaccountID VARCHAR(100)
    , IN IotpSecretKey VARCHAR(64)
)
main: BEGIN
	
	-- DECLARE retcode INT DEFAULT 0;

	UPDATE `pg_membermem_userid` SET otpKey = IotpSecretKey WHERE mem_userid = IaccountID;

	-- SELECT retcode, `index`, nickName, `password`, money, bitcoin  FROM `user_info` WHERE accountID = IaccountID;
	 SELECT pg_member.mem_password,pg_member.mem_id,pg_user_point_balance.po_type, pg_user_point_balance.po_amount  
	 FROM pg_member left join pg_user_point_balance  on pg_member.mem_id = pg_user_point_balance.mb_no 
     WHERE pg_member.mem_userid = IaccountID;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test_call` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`localhost` PROCEDURE `test_call`(IN param int,in id int ,in str varchar(8))
BEGIN
SELECT mem_id,param,str FROM payment.pg_member where mem_id = id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test_end` */;
DELIMITER ;;
CREATE DEFINER=`bitcore`@`%` PROCEDURE `test_end`()
BEGIN
  -- declare ret_code int default 1;
    declare ret_idx bigint default 0;
    declare ret_start_line_time datetime(6) default '0000-00-00 00:00:00.000';
    declare ret_start_line_point decimal(10,4) default 0;
    declare ret_end_line_time datetime(6) default '0000-00-00 00:00:00.000';
    declare ret_end_line_point decimal(10,4) default 0;
    
    declare ret_fee float default 0;
    
    declare ret_total_point decimal(10,4) default 0;
    declare ret_over_point decimal(10,4) default 0;
    declare ret_under_point decimal(10,4) default 0;
    declare ret_result varchar(10) default '';     
        
    declare ret_create_date datetime default '1970-01-01 00:00:00';
    declare ret_curent_round_time datetime default '0000-00-00 00:00:00';
    declare ret_round_date datetime default '0000-00-00 00:00:00';
    declare ret_win_point decimal(10,4) default 0;
    declare retcode int default 1;
    
	select time_stamp,last_price into ret_end_line_time,ret_end_line_point from btc_last_price order by time_stamp desc limit 1;
    
    SELECT DATE_ADD(NOW(), INTERVAL -10 SECOND) into ret_curent_round_time;

	select date_format(ret_curent_round_time, '%Y-%m-%d %H:%i') into ret_round_date;
    	     
    select idx
    ,start_line_point
    ,end_line_point
    ,total_point,over_point,under_point,create_date 
    into ret_idx
    ,ret_start_line_point
    ,ret_end_line_point
    ,ret_total_point,ret_over_point,ret_under_point,ret_create_date
    from round_batting_info where type = 'BTC_USD' and round_date = ret_round_date;
      
    select wa_tx_fee into ret_fee from pg_web_config_wallet_server where po_type = 'btc';
      
 
    set retcode = 10;
           
          
    
    select retcode,ret_idx,ret_win_point,'BTC_USD' as type,ret_end_line_time,ret_start_line_point,ret_end_line_point,ret_total_point,ret_over_point,ret_under_point,ret_fee,ret_result,ret_create_date;
    
  
    
END ;;
DELIMITER ;


