-- MySQL dump 10.13  Distrib 8.0.13, for Win64 (x86_64)
--
-- Host: 3.113.11.142    Database: mydb
-- ------------------------------------------------------
-- Server version	5.5.5-10.4.21-MariaDB-1:10.4.21+maria~bionic-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 SET NAMES utf8 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping routines for database 'mydb'
--
/*!50003 DROP FUNCTION IF EXISTS `FN_ADM_MSG_SEND_ALL` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
CREATE FUNCTION `FN_ADM_MSG_SEND_ALL`(`$p_msg_idx` INT
) RETURNS int(11)
BEGIN

	DECLARE $ret 			VARCHAR(50);
	DECLARE $no_more_rows 		BOOL DEFAULT FALSE;

	DECLARE $db_user_idx			INTEGER DEFAULT 0;

	DECLARE cursor_1 CURSOR FOR
		SELECT idx FROM mydb.member WHERE status=1;
		
	DECLARE CONTINUE HANDLER FOR NOT FOUND
		SET $no_more_rows := true;

	OPEN cursor_1;
	LOOP1: loop
		FETCH cursor_1 INTO $db_user_idx;

		IF $no_more_rows THEN 
			CLOSE cursor_1;
			LEAVE LOOP1;
		END IF;
		
					
		INSERT INTO mydb.t_message(msg_idx,member_idx) VALUES($p_msg_idx,$db_user_idx);
		
	END loop LOOP1;
	
	SET $ret = 1000;

	RETURN $ret;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `FN_GET_IP_COUNTRY` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE FUNCTION `FN_GET_IP_COUNTRY`(`$p_ip` VARCHAR(30)
) RETURNS varchar(50) CHARSET utf8
BEGIN

	DECLARE $ret 			VARCHAR(50);
	DECLARE $v_ip 			INT UNSIGNED DEFAULT 0;
	
	SET $ret = 'local';
	
	SELECT INET_ATON($p_ip) INTO $v_ip;
	SELECT country INTO $ret FROM t_ip_country WHERE $v_ip >= ip_start and $v_ip <= ip_end LIMIT 1;

	RETURN $ret;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `FN_STAT_CHARGE_TOTAL` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES' */ ;
DELIMITER ;;
CREATE FUNCTION `FN_STAT_CHARGE_TOTAL`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$member_idx` INT

) RETURNS bigint(20)
    COMMENT '이번달 1일 부터 현재까지 충전합계 반환'
BEGIN

	DECLARE $charge_total BIGINT DEFAULT 0;

	DECLARE $start_time DATETIME;

	DECLARE $end_time DATETIME;

	

	SET $start_time = IFNULL($start_date, LAST_DAY(NOW() - interval 1 month) + interval 1 DAY);

	SET $end_time = IFNULL($end_date, NOW());



	SELECT IFNULL(SUM(MC.money),0)

	FROM member_money_charge_history AS MC WHERE status = 3 

	AND MC.update_dt >= $start_time

	AND MC.update_dt <= $end_time

	AND MC.member_idx = CASE

								WHEN $member_idx IS NULL THEN MC.member_idx

								ELSE $member_idx END

	INTO $charge_total;

	

	RETURN $charge_total;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `FN_STAT_DIFF_TOTAL` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES' */ ;
DELIMITER ;;
CREATE FUNCTION `FN_STAT_DIFF_TOTAL`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$member_idx` INT

) RETURNS bigint(20)
    COMMENT '이번달 1일 부터 현재까지  차액 합계 반환'
BEGIN

	DECLARE $diff_total BIGINT DEFAULT 0;

	DECLARE $start_time DATETIME;

	DECLARE $end_time DATETIME;

	

	SET $start_time = IFNULL($start_date, LAST_DAY(NOW() - interval 1 month) + interval 1 DAY);

	SET $end_time = IFNULL($end_date, NOW());



	SELECT IFNULL(SUM(MC.money),0) -

	(SELECT IFNULL(SUM(ME.money),0)

	FROM member_money_exchange_history AS ME 

	WHERE status = 3 

	AND update_dt >= $start_time

	AND update_dt <= $end_time

	AND ME.member_idx = CASE

					WHEN $member_idx IS NULL THEN ME.member_idx

					ELSE $member_idx END)  AS diff_sum

	FROM member_money_charge_history AS MC

	WHERE status = 3 

	AND update_dt >= $start_time

	AND update_dt <= $end_time

	AND MC.member_idx = CASE

						WHEN $member_idx IS NULL THEN MC.member_idx

						ELSE $member_idx END

						

	INTO $diff_total;

	

	RETURN $diff_total;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `FN_STAT_EXCHANGE_TOTAL` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES' */ ;
DELIMITER ;;
CREATE FUNCTION `FN_STAT_EXCHANGE_TOTAL`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$member_idx` INT

) RETURNS bigint(20)
    COMMENT '이번달 1일 부터 현재까지 환전합계 반환'
BEGIN

	DECLARE $exchange_total BIGINT DEFAULT 0;

	DECLARE $start_time DATETIME;

	DECLARE $end_time DATETIME;

	

	SET $start_time = IFNULL($start_date, LAST_DAY(NOW() - interval 1 month) + interval 1 DAY);

	SET $end_time = IFNULL($end_date, NOW());



	SELECT IFNULL(SUM(ME.money),0)

	FROM member_money_exchange_history AS ME WHERE status = 3 

	AND ME.update_dt >= $start_time

	AND ME.update_dt <= $end_time

	AND ME.member_idx = CASE

							WHEN $member_idx IS NULL THEN ME.member_idx

							ELSE $member_idx END

	INTO $exchange_total;

	

	RETURN $exchange_total;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `FN_STAT_USERS_BET_SUM` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES' */ ;
DELIMITER ;;
CREATE FUNCTION `FN_STAT_USERS_BET_SUM`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$folder_type` CHAR(1),

	`$bet_type` INT,

	`$member_idx` INT

) RETURNS bigint(20)
    COMMENT '1일부터 현재까지 베팅 합계 (프리매치)'
BEGIN

	/* 총합 */

	DECLARE $val_sum BIGINT DEFAULT 0;

	/* 인풋 */

	DECLARE $start_time DATETIME;

	DECLARE $end_time DATETIME;

	

	

	SET $start_time = IFNULL($start_date, LAST_DAY(NOW() - interval 1 month) + interval 1 DAY);

	SET $end_time = IFNULL($end_date, NOW());



	SELECT IFNULL(SUM(total_bet_money),0)

	FROM member_bet AS MB 

	WHERE bet_status = 3 

	AND MB.update_dt >= $start_time

	AND MB.update_dt <= $end_time

	AND MB.folder_type = CASE

				WHEN $folder_type IS NULL THEN MB.folder_type

				ELSE $folder_type END

	AND MB.bet_type = $bet_type

	AND MB.member_idx = CASE

						WHEN $member_idx IS NULL THEN MB.member_idx

						ELSE $member_idx END

	INTO $val_sum;

	

	RETURN $val_sum;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `FN_STAT_USERS_TAKE_SUM` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES' */ ;
DELIMITER ;;
CREATE FUNCTION `FN_STAT_USERS_TAKE_SUM`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$folder_type` CHAR(1),

	`$bet_type` INT,

	`$member_idx` INT

) RETURNS bigint(20)
    COMMENT '1일부터 현재까지 당첨 합계 (프리매치)'
BEGIN

	/* 총합 */

	DECLARE $val_sum BIGINT DEFAULT 0;

	/* 인풋 */

	DECLARE $start_time DATETIME;

	DECLARE $end_time DATETIME;

	

	SET $start_time = IFNULL($start_date, LAST_DAY(NOW() - interval 1 month) + interval 1 DAY);

	SET $end_time = IFNULL($end_date, NOW());



	SELECT IFNULL(SUM(take_money),0)

	FROM member_bet AS MB 

	WHERE bet_status = 3 

	AND MB.update_dt >= $start_time

	AND MB.update_dt <= $end_time

	AND MB.folder_type = CASE

				WHEN $folder_type IS NULL THEN MB.folder_type

				ELSE $folder_type END

	AND MB.bet_type = $bet_type

	AND MB.member_idx = CASE

					WHEN $member_idx IS NULL THEN MB.member_idx

					ELSE $member_idx END

	INTO $val_sum;

	

	RETURN $val_sum;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-08-11 17:27:33
