CREATE DATABASE  IF NOT EXISTS `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mydb`;
--
-- Host: 110.45.244.234    Database: mydb
-- ------------------------------------------------------

 SET NAMES utf8 ;

--
-- Table structure for table `TB_CTBX_SEQ`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `TB_CTBX_SEQ` (
  `TR_DATE` char(8) NOT NULL DEFAULT '' COMMENT '거래일자',
  `TR_SEQ` varchar(20) DEFAULT '' COMMENT '일자의 거래순번',
  `SEND_ID` varchar(10) NOT NULL DEFAULT '' COMMENT '기관코드',
  PRIMARY KEY (`TR_DATE`,`SEND_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `TB_RVAS_LIST`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `TB_RVAS_LIST` (
  `TR_DATE` char(8) NOT NULL DEFAULT '' COMMENT '거래일자',
  `TR_SEQ` varchar(20) NOT NULL DEFAULT '' COMMENT '일자의 거래순번',
  `ORG_BANK` char(3) NOT NULL DEFAULT '' COMMENT '가입은행코드',
  `ORG_CD` varchar(10) NOT NULL DEFAULT '' COMMENT '가입기관코드',
  `VACCT_NO` varchar(20) NOT NULL DEFAULT '' COMMENT '가상계좌번호',
  `STAT_CD` char(1) NOT NULL DEFAULT '1' COMMENT '0:미사용, 1:사용, 2:해지',
  `CUST_CD` varchar(20) NOT NULL DEFAULT '' COMMENT '고객코드',
  `CUST_NM` varchar(20) NOT NULL DEFAULT '' COMMENT '고객명',
  `ERROR_CD` char(4) NOT NULL DEFAULT '0' COMMENT '응답코드',
  `TR_TIME` char(6) NOT NULL DEFAULT '' COMMENT '거래시간',
  `TR_AMT` int NOT NULL DEFAULT '0' COMMENT '입금금액',
  `IN_BANK_CD` char(3) NOT NULL DEFAULT '' COMMENT '입금은행',
  `IN_BANK_BRANCH` varchar(50) NOT NULL DEFAULT '' COMMENT '입금지점',
  `IN_NAME` varchar(20) NOT NULL DEFAULT '' COMMENT '입금자',
  `ENTRY_DATE` char(14) DEFAULT '' COMMENT '등록일시',
  `ENTRY_IDNO` varchar(20) DEFAULT '' COMMENT '등록자',
  `ERP_PROC_YN` char(1) DEFAULT '' COMMENT 'N: 미처리, Y: ERP처리 완료',
  `ERP_PROC_DT` varchar(14) DEFAULT '' COMMENT '처리일시',
  PRIMARY KEY (`TR_DATE`,`TR_SEQ`,`ORG_BANK`,`ORG_CD`,`VACCT_NO`,`STAT_CD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `TB_RVAS_MAST`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `TB_RVAS_MAST` (
  `ORG_BANK` char(3) NOT NULL DEFAULT '' COMMENT '가입은행코드',
  `ORG_CD` varchar(10) NOT NULL DEFAULT '' COMMENT '가입기관코드',
  `VACCT_NO` varchar(20) NOT NULL DEFAULT '' COMMENT '가상계좌번호',
  `STAT_CD` char(1) NOT NULL DEFAULT '1' COMMENT '0:미사용, 1:사용, 2:해지',
  `IN_GB` char(1) NOT NULL DEFAULT '2' COMMENT '1:자유, 2:정액',
  `PAY_AMT` int DEFAULT '0' COMMENT '자유:0원, 정액:입금예정금액',
  `IN_DEADLINE` char(8) NOT NULL DEFAULT '' COMMENT '입금마감일',
  `CUST_CD` varchar(20) NOT NULL DEFAULT '0' COMMENT '고객코드',
  `CUST_NM` varchar(20) NOT NULL DEFAULT '' COMMENT '고객명',
  `ENTRY_DATE` char(14) DEFAULT '' COMMENT '등록일시',
  `ENTRY_IDNO` varchar(20) DEFAULT '' COMMENT '등록자',
  `SEND_YN` char(1) DEFAULT '' COMMENT '등록전문 사용여부',
  `ID` varchar(20) DEFAULT '',
  PRIMARY KEY (`ORG_BANK`,`ORG_CD`,`VACCT_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `account`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `account` (
  `idx` int NOT NULL,
  `account_code` varchar(45) DEFAULT NULL,
  `account_name` varchar(45) DEFAULT NULL,
  `bank_id` int DEFAULT '0',
  `bank_code` varchar(45) DEFAULT '',
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='금융기관 기본 코드 테이블 ';

--
-- Table structure for table `account_check_number`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `account_check_number` (
  `serial_number` int NOT NULL DEFAULT '0' COMMENT '거래일련번호',
  PRIMARY KEY (`serial_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='계좌조회 거래일련번호';

--
-- Table structure for table `account_inquiry_verification`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `account_inquiry_verification` (
  `call` varchar(20) NOT NULL DEFAULT '',
  `account_number` varchar(45) NOT NULL DEFAULT '',
  `account_name` varchar(45) NOT NULL DEFAULT '',
  `account_bank` varchar(45) NOT NULL DEFAULT '',
  `result` varchar(2) NOT NULL DEFAULT 'N',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`call`,`account_number`,`account_name`,`account_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `account_level_list`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `account_level_list` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `level` int DEFAULT NULL COMMENT '전용 계좌 레벨 (member 레벨) ',
  `account_name` varchar(45) DEFAULT NULL,
  `account_bank` varchar(45) DEFAULT NULL COMMENT '은행명',
  `account_number` varchar(45) DEFAULT NULL COMMENT '계좌 번호 ',
  `display_account_bank` varchar(45) DEFAULT '',
  `bank_id` int DEFAULT '0',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  UNIQUE KEY `UQ_ACCOUNT_LEVEL_01` (`level`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3 COMMENT='회원 레벨 별 전용 계좌 목록 ';

--
-- Table structure for table `charge_event`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `charge_event` (
  `level` int unsigned NOT NULL COMMENT '레벨 (1 ~ 10)',
  `bonus` int unsigned NOT NULL DEFAULT '1000000' COMMENT '보너스(%)',
  `max_bonus` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`level`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='돌발충전 이벤트';

--
-- Table structure for table `charge_type`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `charge_type` (
  `level` int NOT NULL,
  `charge_type` int NOT NULL COMMENT '0-캐쉬, 1-코인, 2-가상계좌',
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='레벨별 충전방식';

--
-- Table structure for table `charge_type_list`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `charge_type_list` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `type` tinyint NOT NULL DEFAULT '0' COMMENT '0 : NONE, 1 : 쿠콘 가상계좌, 2: 페이레터 가상계좌 100 : 법인계좌, 200 : 코인',
  `name` varchar(20) DEFAULT '',
  `acnpy_mthd` tinyint DEFAULT NULL COMMENT '0 : 대행사 api 로 앱으로 전송 : 1 : 문자전송 : 2 직접 지급',
  `account_number` varchar(45) DEFAULT NULL,
  `account_holder` varchar(45) DEFAULT NULL,
  `bank_code` varchar(45) DEFAULT NULL,
  `bank_name` varchar(45) DEFAULT NULL,
  `is_use` tinyint DEFAULT '0' COMMENT '0 : 미사용 ,1 : 사용',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COMMENT='레벨별 충전방식';

--
-- Table structure for table `confirm_agreement`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `confirm_agreement` (
  `call` varchar(20) NOT NULL DEFAULT '',
  `result` char(2) DEFAULT 'N',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`call`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Temporary view structure for view `day_ch_ex`
--

/*!50001 DROP VIEW IF EXISTS `day_ch_ex`*/;
/*!50001 CREATE VIEW `day_ch_ex` AS SELECT 
 1 AS `stype`,
 1 AS `status`,
 1 AS `cnt`,
 1 AS `s_money`*/;

--
-- Temporary view structure for view `day_now_call_check`
--

/*!50001 DROP VIEW IF EXISTS `day_now_call_check`*/;
/*!50001 CREATE VIEW `day_now_call_check` AS SELECT 
 1 AS `stype`,
 1 AS `status`,
 1 AS `cnt`,
 1 AS `s_money`*/;

--
-- Table structure for table `events`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `events` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `a_id` varchar(45) DEFAULT NULL,
  `title` varchar(45) DEFAULT '',
  `thumbnail` varchar(1024) DEFAULT '',
  `detail` text,
  `popup_onoff` varchar(2) DEFAULT 'N',
  `del_flag` tinyint DEFAULT '0' COMMENT '0-존재, 1-삭제됨',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `start_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `end_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  KEY `thumbnail` (`thumbnail`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `fee_setting`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `fee_setting` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `member_idx` bigint DEFAULT '0',
  `level` int DEFAULT '0' COMMENT '1: 회원 , 2 : 정회원 ,3 : VIP ,4 : VVIP, 5 : SVIP',
  `name` varchar(20) DEFAULT '',
  `charge_limit_amount` int DEFAULT '0',
  `refund_limit_amount` int DEFAULT '0',
  `send_limit_amount` int DEFAULT '0',
  `chare_fee` decimal(5,2) DEFAULT '0.00',
  `refund_fee_min` int DEFAULT '0',
  `refund_fee_max` decimal(5,2) DEFAULT '0.00',
  `send_limit_fee_min` int DEFAULT '0',
  `send_limit_fee_max` decimal(5,2) DEFAULT '0.00',
  `refund_is_use` varchar(4) DEFAULT 'max',
  `send_is_use` varchar(4) DEFAULT 'max',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `money` int DEFAULT NULL,
  `refund_type` int DEFAULT NULL,
  `send_limit_amount_min` int DEFAULT '0',
  `send_limit_amount_max` int DEFAULT '0',
  `refund_limit_amount_min` int DEFAULT '0',
  `refund_limit_amount_max` int DEFAULT '0',
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COMMENT='수수료 설정 테이블 ';

--
-- Table structure for table `friend_group`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `friend_group` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `member_idx` bigint DEFAULT '0',
  `group_nick_name` varchar(20) DEFAULT '',
  `is_favorites` varchar(2) DEFAULT 'N',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `friend_group_list`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `friend_group_list` (
  `group_idx` bigint NOT NULL,
  `member_idx` bigint NOT NULL,
  `friend_member_idx` varchar(45) NOT NULL,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`group_idx`,`member_idx`,`friend_member_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `friend_list`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `friend_list` (
  `member_idx` bigint NOT NULL DEFAULT '0',
  `friend_member_idx` bigint NOT NULL DEFAULT '0',
  `nick_name` varchar(20) DEFAULT '',
  `is_favorites` varchar(2) DEFAULT 'N',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_idx`,`friend_member_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `help`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `help` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `member_idx` int DEFAULT NULL,
  `admin_idx` int DEFAULT NULL,
  `title` varchar(45) DEFAULT NULL,
  `contents` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT '대기' COMMENT '‘대기’, ‘완료’',
  `is_delete` varchar(45) DEFAULT NULL,
  `create_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `identity_verification`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `identity_verification` (
  `call` varchar(20) NOT NULL DEFAULT '',
  `result` char(2) DEFAULT 'N',
  `t_uu_id` varchar(50) NOT NULL DEFAULT '',
  `tid` varchar(50) DEFAULT NULL,
  `code` int DEFAULT NULL,
  `message` varchar(256) DEFAULT NULL,
  `auth_info` mediumtext,
  `depositor` varchar(20) DEFAULT NULL,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`t_uu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `inspection`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `inspection` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `title` varchar(45) DEFAULT '',
  `contents` varchar(1024) DEFAULT '',
  `start_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `end_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COMMENT='점검메세지';

--
-- Table structure for table `join_message`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `join_message` (
  `a_id` varchar(45) NOT NULL COMMENT '작성자',
  `title` varchar(100) DEFAULT NULL,
  `contents` text,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='가입메세지';

--
-- Table structure for table `member`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `id` varchar(20) NOT NULL COMMENT '회원가입시 입력하는 아이디 ',
  `password` varchar(512) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
  `second_password` varchar(4) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL COMMENT '환전 비밀 번호 ',
  `money` int DEFAULT '0' COMMENT '충전한 현금 ',
  `status` tinyint DEFAULT '1' COMMENT '회원상태 ( 1:정상, 2:정지, 3:탈퇴, 11:승인 대기회원 )',
  `level` tinyint DEFAULT '1' COMMENT '1: 회원 , 2 : 정회원 ,3 : VIP ,4 : VVIP, 5 : SVIP',
  `u_business` tinyint DEFAULT '0',
  `session_key` varchar(50) DEFAULT NULL,
  `last_login` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '최종 로그인',
  `check_login` datetime DEFAULT CURRENT_TIMESTAMP,
  `leave_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `reg_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '등록일',
  `keep_login_access_token` varchar(50) DEFAULT NULL COMMENT '자동로그인 토큰',
  `recommend_code` varchar(45) DEFAULT '' COMMENT '본인 추천코드',
  `recommend_member_code` varchar(45) DEFAULT '' COMMENT '추천한 상대방 코드',
  `recommend_member` int DEFAULT '0',
  `recommend_code_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `push_key` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
  `pc_password` varchar(4) DEFAULT NULL COMMENT 'PC 인증 패스워드',
  `pc_password_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `keep_login_access_token_UNIQUE` (`keep_login_access_token`),
  KEY `recom_idx` (`recommend_code`)
) ENGINE=InnoDB AUTO_INCREMENT=10265 DEFAULT CHARSET=utf8mb3 COMMENT='회원 테이블';

--
-- Table structure for table `member_ip_block_history`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member_ip_block_history` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `admin_id` varchar(50) DEFAULT NULL,
  `member_idx` int DEFAULT NULL,
  `login_history_idx` int DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `country` varchar(45) DEFAULT NULL,
  `memo` varchar(45) DEFAULT NULL,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  UNIQUE KEY `UQ_IP_BLOCK_U01` (`ip`),
  KEY `idx_create` (`create_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `member_level`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member_level` (
  `level` tinyint NOT NULL DEFAULT '0',
  `money` int DEFAULT '0',
  PRIMARY KEY (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `member_login_history`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member_login_history` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `member_idx` int DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `country` varchar(45) DEFAULT NULL,
  `block` varchar(45) DEFAULT NULL,
  `login_domain` varchar(100) DEFAULT NULL COMMENT '접속 도메인',
  `agent` text COMMENT 'user agent 정보',
  `agent_hash` varchar(50) DEFAULT NULL,
  `agent_device` varchar(50) DEFAULT NULL COMMENT 'user agent 정보, 디바이스',
  `agent_browser` varchar(50) DEFAULT NULL COMMENT 'user agent 정보, 브라우저',
  `login_yn` enum('Y','N') DEFAULT NULL COMMENT 'login Y: 성공, N:실패',
  `login_datetime` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  KEY `idx_mb_idx` (`member_idx`,`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='로그인 내역 ';

--
-- Table structure for table `member_money_charge_history`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member_money_charge_history` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `u_key` varchar(64) DEFAULT NULL COMMENT 't_log_cash u_key 와 연결 ',
  `member_idx` int DEFAULT NULL COMMENT '충전 신청자 멤버 idx ',
  `admin_idx` int DEFAULT NULL COMMENT '관리자 idx ',
  `deposit_name` varchar(45) DEFAULT NULL COMMENT '입금자 명 ',
  `bank_id` int DEFAULT '0',
  `bank_name` varchar(45) DEFAULT '',
  `account_number` varchar(45) DEFAULT '',
  `account_name` varchar(45) DEFAULT '',
  `money` int DEFAULT NULL COMMENT '충전 금액 ',
  `result_money` int DEFAULT '0',
  `bonus_money` int DEFAULT NULL,
  `bonus_point` int DEFAULT '0',
  `set_type` varchar(50) DEFAULT '',
  `status` int DEFAULT NULL COMMENT '1: 입금 전 (신청) 2: 입금 확인 (대기) 3: 충전 완료 (완료) 4: 취소 10: 배팅적중 11:관리자 취소  \\\\n999: 오류',
  `comment` varchar(100) DEFAULT NULL COMMENT 'Status comment ',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '관리자 처리 일자',
  `delete_dt` datetime DEFAULT NULL,
  `referenceId` varchar(45) DEFAULT '',
  `sms_status` varchar(45) DEFAULT '',
  `is_sound` enum('Y','N') DEFAULT 'Y',
  `sports_bet_s_money` int DEFAULT '0',
  `sports_bet_d_money` int DEFAULT '0',
  `real_bet_s_money` int DEFAULT '0',
  `real_bet_d_money` int DEFAULT '0',
  `mini_bet_money` int DEFAULT '0',
  `casino_bet_money` int DEFAULT '0',
  `slot_bet_money` int DEFAULT '0',
  PRIMARY KEY (`idx`),
  KEY `status_member_idx` (`status`,`member_idx`),
  KEY `idx_create_dt` (`create_dt`),
  KEY `idx_mb_idx` (`member_idx`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=39406 DEFAULT CHARSET=utf8mb3 COMMENT='충전 / 환전 내역 ';

--
-- Table structure for table `member_money_charge_sms`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member_money_charge_sms` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `deposit_name` varchar(45) DEFAULT NULL COMMENT '입금자 명 ',
  `bank_code` varchar(3) DEFAULT '0' COMMENT '은행코드',
  `bank_name` varchar(45) DEFAULT '0' COMMENT '은행명',
  `money` int DEFAULT NULL COMMENT '충전 금액 ',
  `status` int DEFAULT NULL COMMENT '1: 입금 전 (신청) 2: 시간초과(완료) 3: 자동충전 완료 (완료) 4: 운영자 완료 \\\\n999: 오류',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT NULL COMMENT '처리 일자',
  `ori` text COMMENT '원문',
  PRIMARY KEY (`idx`),
  KEY `st_bk_idx` (`deposit_name`,`bank_code`,`money`,`create_dt`)
) ENGINE=InnoDB AUTO_INCREMENT=61623 DEFAULT CHARSET=utf8mb3 COMMENT='입금sms 내역';

--
-- Table structure for table `member_money_exchange_history`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member_money_exchange_history` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `u_key` varchar(64) DEFAULT NULL COMMENT 't_log_cash u_key 와 연결 ',
  `member_idx` int DEFAULT NULL COMMENT '환전 신청자 멤버 idx ',
  `admin_idx` int DEFAULT NULL COMMENT '관리자 idx ',
  `money` int DEFAULT NULL COMMENT '환전 금액 ',
  `result_money` int DEFAULT '0',
  `comment` varchar(100) DEFAULT NULL COMMENT 'Status comment ',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `delete_dt` datetime DEFAULT NULL,
  `set_type` varchar(50) DEFAULT '',
  `is_sound` enum('Y','N') DEFAULT 'Y',
  `status` int DEFAULT NULL COMMENT '1: 환전 신청 (신청)  \\\\n2: 환전 대기 (대기)  \\\\n3: 환전 완료 (완료)  \\\\n4: 관리자 취소  \\\\n999: 오류',
  PRIMARY KEY (`idx`),
  KEY `idx_s_u_m` (`member_idx`),
  KEY `status_member_idx` (`status`,`member_idx`) USING BTREE,
  KEY `idx_create_dt` (`create_dt`)
) ENGINE=InnoDB AUTO_INCREMENT=12194 DEFAULT CHARSET=utf8mb3 COMMENT='환전 내역 ';

--
-- Table structure for table `member_notice`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member_notice` (
  `member_idx` bigint NOT NULL,
  `service_use_notice` varchar(2) DEFAULT 'Y',
  `event_adv_use_notice` varchar(2) DEFAULT 'Y',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='회원 알람 관리 테이블';

--
-- Table structure for table `member_update_history`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `member_update_history` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `a_id` varchar(50) DEFAULT '',
  `member_idx` int DEFAULT NULL,
  `update_type` varchar(45) DEFAULT NULL COMMENT '변경 항목 ',
  `before_data` text,
  `after_data` text,
  `create_dt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 COMMENT='로그인 내역 ';

--
-- Table structure for table `menu_board`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `menu_board` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `member_idx` int DEFAULT NULL COMMENT '글쓴이 (회원) ',
  `a_id` varchar(45) DEFAULT NULL COMMENT '담당자 (관리자) ',
  `nick_name` varchar(45) DEFAULT '',
  `title` varchar(100) DEFAULT NULL,
  `contents` text,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `display` tinyint NOT NULL DEFAULT '0' COMMENT '노출여부 가 아니라 공지여부',
  PRIMARY KEY (`idx`),
  KEY `member_idx_create_dt` (`member_idx`,`create_dt`),
  KEY `member_idx` (`member_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='메뉴 상단 ‘게시판’';

--
-- Table structure for table `menu_board_comment`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `menu_board_comment` (
  `idx` int NOT NULL AUTO_INCREMENT COMMENT '댓글 idx',
  `board_idx` int NOT NULL COMMENT '글번호 idx',
  `member_idx` int NOT NULL COMMENT '작성자 idx',
  `nick_name` varchar(45) NOT NULL COMMENT '닉네임',
  `comment` text NOT NULL COMMENT '댓글',
  `display` tinyint(1) NOT NULL DEFAULT '1' COMMENT '노출 여부 (0: 미노출, 1: 노출)',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '작성일',
  PRIMARY KEY (`idx`),
  KEY `IDX_BOARD_IDX` (`board_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='게시판 댓글';

--
-- Table structure for table `menu_qna`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `menu_qna` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `member_idx` bigint DEFAULT '0' COMMENT '글쓴이 (회원) ',
  `a_id` varchar(45) DEFAULT '' COMMENT '담당자 (관리자) ',
  `title` varchar(100) DEFAULT '',
  `contents` text,
  `answer` text,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  KEY `member_idx` (`member_idx`),
  KEY `member_idx_create_dt` (`create_dt`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb3 COMMENT='메뉴 상단 ‘고객센터’';

--
-- Table structure for table `menu_qna_set`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `menu_qna_set` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `contents` text,
  `create_dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COMMENT='1:1문의 템플릿';

--
-- Table structure for table `message`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `message` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `member_send_idx` int DEFAULT NULL,
  `member_rec_idx` int DEFAULT NULL,
  `content` varchar(1024) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `reg_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `read_yn` enum('Y','N') DEFAULT 'N',
  `send_del_yn` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT 'N',
  `rec_del_yn` enum('Y','N') DEFAULT 'N',
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `money_order_history`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `money_order_history` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `type` int DEFAULT NULL COMMENT '1:kwin, 2:asbet, 3:tiger, 4:onoff, 5"bitup',
  `member_idx` int DEFAULT NULL COMMENT '입금 신청자 멤버번호 ',
  `order_name` varchar(45) DEFAULT NULL COMMENT '입금자명(가상계좌 입금자명으로 노출)',
  `order_bank_id` int DEFAULT '0' COMMENT '입금자 은행번호',
  `order_bank_code` varchar(45) DEFAULT '' COMMENT '입금자 은행코드',
  `order_account_number` varchar(45) DEFAULT '' COMMENT '입금할 통장의 계좌번호',
  `with_hp` varchar(45) DEFAULT '' COMMENT '입금자 은행에 등록된 휴대번호',
  `money` int DEFAULT NULL COMMENT '입금액',
  `status` int DEFAULT NULL COMMENT '1: 신청, 2: 완료',
  `virtual_account_bank` varchar(90) DEFAULT '' COMMENT '가상계좌은행명',
  `virtual_account_number` varchar(45) DEFAULT '' COMMENT '가상계좌번호',
  `moid` varchar(60) DEFAULT '' COMMENT '주문번호',
  `tid` varchar(60) DEFAULT '' COMMENT '거래번호',
  `deposit_money` int DEFAULT NULL COMMENT '정산예정금액',
  `consume` int DEFAULT '0' COMMENT '유저계정 입금처리여부',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `deposit_dt` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '입금일시',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '처리일자',
  PRIMARY KEY (`idx`),
  KEY `type` (`type`),
  KEY `idx_status` (`status`),
  KEY `idx_create_dt` (`create_dt`),
  KEY `idx_mb_idx` (`member_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='결제현황';

--
-- Table structure for table `no_member_pin_recv_send_info`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `no_member_pin_recv_send_info` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `member_idx` bigint DEFAULT '0',
  `call` varchar(20) DEFAULT '',
  `type` tinyint DEFAULT '0',
  `amount` int DEFAULT '0',
  `status` tinyint DEFAULT '0',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  KEY `call_idx` (`call`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `no_member_pin_req_recv_send_info`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `no_member_pin_req_recv_send_info` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `member_idx` bigint DEFAULT '0',
  `call` varchar(20) DEFAULT '',
  `type` tinyint DEFAULT '0',
  `amount` int DEFAULT '0',
  `status` tinyint DEFAULT '0',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `notices`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `notices` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `a_id` varchar(45) DEFAULT NULL,
  `thumbnail` varchar(1024) DEFAULT '',
  `title` varchar(45) DEFAULT '',
  `detail` varchar(1024) DEFAULT '',
  `popup_onoff` varchar(2) DEFAULT 'Y' COMMENT '0-미사용, 1-사용',
  `del_flag` tinyint DEFAULT '0' COMMENT '0-존재, 1-삭제됨',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COMMENT='공지테이블';

--
-- Table structure for table `personal_account_number`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `personal_account_number` (
  `id` varchar(20) NOT NULL DEFAULT '',
  `account_number` varchar(45) NOT NULL DEFAULT '',
  `account_name` varchar(45) NOT NULL DEFAULT '',
  `account_bank` varchar(45) NOT NULL DEFAULT '',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`account_number`,`account_name`,`account_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `pin_charge_exchange_history`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `pin_charge_exchange_history` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `type` tinyint NOT NULL DEFAULT '0' COMMENT '0 : 충전 ,1 : 환불, 2: 전체 ',
  `charge_type` tinyint DEFAULT NULL COMMENT '0 : NONE, 1 : 쿠콘 가상계좌, 2: 페이레터 가상계좌 100 : 법인계좌, 200 : 코인',
  `member_idx` int DEFAULT NULL COMMENT '충전 신청자 멤버 idx ',
  `admin_idx` int DEFAULT NULL COMMENT '관리자 idx ',
  `deposit_name` varchar(45) DEFAULT NULL COMMENT '입금자 명 ',
  `bank_code` varchar(45) DEFAULT '',
  `account_number` varchar(45) DEFAULT '',
  `account_name` varchar(45) DEFAULT '',
  `money` int DEFAULT NULL COMMENT '충전 금액 ',
  `result_money` int DEFAULT '0',
  `bonus_money` int DEFAULT NULL,
  `set_type` varchar(50) DEFAULT '',
  `status` int DEFAULT NULL COMMENT '1: (신청) 2: 확인 (대기) 3: 완료 (완료) 4: 취소 11:관리자 취소  \\\\n999: 오류',
  `comment` varchar(100) DEFAULT NULL COMMENT 'Status comment ',
  `fee` decimal(10,2) DEFAULT '0.00' COMMENT '입출금 수수료 ',
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '관리자 처리 일자',
  `TR_SEQ` varchar(20) DEFAULT '' COMMENT '거래일련번호',
  PRIMARY KEY (`idx`),
  KEY `status_member_idx` (`status`,`member_idx`),
  KEY `idx_create_dt` (`create_dt`),
  KEY `idx_mb_idx` (`member_idx`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8mb3 COMMENT='충전 / 환전 내역 ';

--
-- Table structure for table `pin_recv_send_info`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `pin_recv_send_info` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `member_idx` bigint DEFAULT '0',
  `other_member_idx` bigint DEFAULT '0',
  `type` tinyint DEFAULT '0',
  `amount` int DEFAULT '0',
  `status` tinyint DEFAULT '1',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=1474 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `pin_req_recv_send_info`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `pin_req_recv_send_info` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `member_idx` bigint DEFAULT '0',
  `other_member_idx` bigint DEFAULT '0',
  `amount` int DEFAULT '0',
  `status` tinyint DEFAULT '0',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=653 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `pin_shoping_content`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `pin_shoping_content` (
  `brand_id` int NOT NULL,
  `content` varchar(512) DEFAULT NULL,
  `brand_name` varchar(100) DEFAULT NULL,
  `brand_url` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `pin_shoping_info`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `pin_shoping_info` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `member_idx` bigint DEFAULT '0',
  `shop_idx` varchar(100) DEFAULT '0',
  `price` int DEFAULT '0',
  `amount` int DEFAULT '0',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `state` int DEFAULT NULL,
  `shop_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=241 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `sms_auth_history`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `sms_auth_history` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `phone_number` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
  `auth_number` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` int DEFAULT '1' COMMENT '1-요청, 2-인증완료',
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb3 COMMENT='아이코드 문자 인증 이력';

--
-- Table structure for table `t_adm_config`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_adm_config` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `a_id` varchar(50) NOT NULL,
  `alarm_name` varchar(50) DEFAULT NULL,
  `alarm_use` enum('Y','N') NOT NULL DEFAULT 'Y',
  `alarm_kind` varchar(50) DEFAULT NULL,
  `alarm_file` varchar(300) DEFAULT NULL,
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `t_adm_ip`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_adm_ip` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `a_ip` varchar(30) DEFAULT NULL,
  `a_memo` text,
  `isuse` enum('Y','N') DEFAULT 'Y',
  `reg_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  UNIQUE KEY `UQ_t_adm_ip_01` (`a_ip`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `t_adm_log`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_adm_log` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `a_id` varchar(50) NOT NULL,
  `a_ip` varchar(30) DEFAULT NULL,
  `a_country` varchar(50) DEFAULT NULL,
  `is_login` enum('Y','N','') DEFAULT NULL,
  `u_id` varchar(50) DEFAULT NULL,
  `u_nick` varchar(50) DEFAULT NULL,
  `log_data` text,
  `log_type` tinyint unsigned DEFAULT '99' COMMENT '1 운영툴 로그인\n2. 관리자 ip관리, 개별 ip 차단관리\n3. 도메인 코드 관리\n4. 레벨별 계좌설정\n5. 가입첫충\n6. 등급별 베팅금액 설정\n7. 사이트 설정\n11. 관리자 아이디 관리',
  `reg_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  KEY `idx_log_type` (`log_type`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb3 COMMENT='관리자 로그';

--
-- Table structure for table `t_adm_user`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_adm_user` (
  `a_id` varchar(50) NOT NULL,
  `a_pw` varchar(200) NOT NULL,
  `a_nick` varchar(100) DEFAULT NULL,
  `a_memo` text,
  `session_key` varchar(100) DEFAULT NULL,
  `last_login` timestamp NULL DEFAULT NULL,
  `a_status` enum('Y','N') DEFAULT 'Y',
  `a_ip` varchar(30) DEFAULT NULL,
  `a_before_ip` varchar(30) DEFAULT NULL,
  `a_reg_id` varchar(30) DEFAULT NULL,
  `a_del_id` varchar(30) DEFAULT NULL,
  `reg_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_main_stat` varchar(45) DEFAULT '1' COMMENT '상황판 값을 보여줄지 말지 여부(0-off, 1-on)',
  `grade` tinyint DEFAULT '1' COMMENT '1 : 일반관리자  ,2 : 슈퍼관리자 ',
  PRIMARY KEY (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `t_domain_code`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_domain_code` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `domain` varchar(100) DEFAULT NULL,
  `domain_code` varchar(100) DEFAULT NULL,
  `is_use` enum('Y','N') DEFAULT 'Y',
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  UNIQUE KEY `UQ_DOMAIN_01` (`domain_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `t_game_config`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_game_config` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `u_level` tinyint unsigned NOT NULL DEFAULT '0',
  `set_type` varchar(50) NOT NULL,
  `set_type_val` varchar(200) NOT NULL,
  `title` varchar(200) NOT NULL,
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`),
  UNIQUE KEY `UQ_GAME_CONFIG_01` (`u_level`,`set_type`) USING BTREE,
  KEY `idx_type` (`set_type`)
) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `t_ip_country`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_ip_country` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `country_code` varchar(10) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `ip_start` int unsigned NOT NULL,
  `ip_end` int unsigned NOT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=190352 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `t_log_cash`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_log_cash` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `u_key` varchar(64) DEFAULT NULL,
  `member_idx` int NOT NULL,
  `ac_code` smallint DEFAULT '0' COMMENT '    /*1:충전,2:환전,3:베팅,4:베팅취소,5:포인트전환(추가),6:포인트차감,7:베팅결과처리,8:이벤트충전,9:이벤트차감,10:포인트충전,11 : 낙첨 포인트 지급,101:충전요청,102:환전요청,103:계좌조회,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n             * 111:충전요청취소,112:환전요청취소,113:충전취소,114:환전취소,121:관리자충전,122:관리자회수,123:관리자 포인트 충전, 124:관리자 포인트 회수,125 : 관리자 환전 회수,998:데이터복구,999:기타 \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n     \\\\\\\\n201 : 정산 취소,202 : 정산 포인트 취소,203 : 정산 추천인 포인트 취소  ,126 : 재정산으로 인한 정산 ->적특시 이미 지급된 포인트 관리자 회수\\n,127 : 재정산으로 인한 적특 ->정산 시 지급으로 인한 관리자포인트 충전  */',
  `ac_idx` int DEFAULT NULL,
  `r_money` int NOT NULL DEFAULT '0',
  `be_r_money` int NOT NULL DEFAULT '0',
  `af_r_money` int NOT NULL DEFAULT '0',
  `point` int NOT NULL DEFAULT '0',
  `be_point` int NOT NULL DEFAULT '0',
  `af_point` int NOT NULL DEFAULT '0',
  `m_kind` enum('R','P','M') NOT NULL DEFAULT 'R',
  `title_kind` varchar(30) DEFAULT NULL,
  `coment` text,
  `u_ip` varchar(30) DEFAULT NULL,
  `a_id` varchar(30) DEFAULT NULL,
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `charge_total_count` int DEFAULT '0',
  `loock_up_total_count` int DEFAULT '0',
  `charge_total_money` int DEFAULT '0',
  `day` int NOT NULL,
  PRIMARY KEY (`idx`,`day`),
  KEY `ac_code` (`ac_code`),
  KEY `member_idx` (`member_idx`),
  KEY `reg_time` (`reg_time`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `t_member_memo`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_member_memo` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `member_idx` int unsigned NOT NULL DEFAULT '0',
  `m_type` tinyint NOT NULL DEFAULT '1',
  `content` varchar(300) DEFAULT NULL,
  `a_id` varchar(50) DEFAULT NULL,
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=37958 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `t_message`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_message` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `msg_idx` int unsigned NOT NULL,
  `member_idx` int unsigned NOT NULL,
  `read_yn` enum('Y','N') NOT NULL DEFAULT 'N',
  `u_ip` varchar(30) DEFAULT NULL,
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일',
  `read_time` datetime DEFAULT NULL,
  `is_delete` int DEFAULT '0',
  PRIMARY KEY (`idx`),
  KEY `idx_mb_idx` (`member_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='쪽지';

--
-- Table structure for table `t_message_list`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_message_list` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `idx_key` char(25) NOT NULL,
  `title` varchar(200) NOT NULL,
  `content` longtext NOT NULL,
  `a_id` varchar(50) DEFAULT NULL,
  `a_view_nick` varchar(100) DEFAULT NULL,
  `w_type` enum('A','H') NOT NULL DEFAULT 'H',
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일',
  PRIMARY KEY (`idx`) USING BTREE,
  UNIQUE KEY `UQ_MESSAGE_LIST_U01` (`idx_key`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `t_message_set`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `t_message_set` (
  `idx` int unsigned NOT NULL AUTO_INCREMENT,
  `a_id` varchar(50) NOT NULL,
  `title_view` varchar(200) NOT NULL,
  `title` varchar(200) NOT NULL,
  `content` text NOT NULL,
  `use_kind` enum('Y','N') NOT NULL DEFAULT 'N',
  `send_cnt` int unsigned NOT NULL DEFAULT '0',
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COMMENT='쪽지 템플릿';

--
-- Table structure for table `terms`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `terms` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `type` tinyint DEFAULT '0',
  `name` varchar(20) DEFAULT '',
  `desc` text,
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `total_system`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `total_system` (
  `current_count` int NOT NULL DEFAULT '0',
  `update_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`current_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='집계용 테이블';

--
-- Table structure for table `x_total_member_cash`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `x_total_member_cash` (
  `member_idx` int NOT NULL DEFAULT '0',
  `charge_total_count` int NOT NULL DEFAULT '0' COMMENT '충전횟수',
  `charge_total_money` bigint NOT NULL DEFAULT '0' COMMENT '조회횟수',
  `exchange_total_count` int NOT NULL DEFAULT '0' COMMENT '충전금액',
  `exchange_total_money` bigint DEFAULT '0',
  `max_charge` bigint DEFAULT '0',
  `bf_max_charge` bigint DEFAULT '0',
  `max_exchange` bigint DEFAULT '0',
  `bf_max_exchange` bigint DEFAULT '0',
  `loock_up_total_count` int DEFAULT '0' COMMENT '계좌 조회 카운트',
  `bet_total_count` int DEFAULT '0',
  `bet_total_money` bigint DEFAULT '0',
  `pre_s_total_money` bigint DEFAULT '0',
  `pre_m_total_money` bigint DEFAULT '0',
  `real_s_total_money` bigint DEFAULT '0',
  `real_m_total_money` bigint DEFAULT '0',
  `mini_total_money` bigint DEFAULT '0',
  `casino_total_money` bigint DEFAULT '0',
  `slot_total_money` bigint DEFAULT '0',
  `take_total_money` bigint DEFAULT '0',
  `take_slot_total_money` bigint DEFAULT '0',
  `take_casino_total_money` bigint DEFAULT '0',
  `take_mini_total_money` bigint DEFAULT '0',
  `take_real_m_total_money` bigint DEFAULT '0',
  `take_real_s_total_money` bigint DEFAULT '0',
  `take_pre_m_total_money` bigint DEFAULT '0',
  `take_pre_s_total_money` bigint DEFAULT '0',
  `update_dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping events for database 'mydb'
--

--
-- Dumping routines for database 'mydb'
--
/*!50003 DROP FUNCTION IF EXISTS `FN_ADM_MSG_SEND_ALL` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` FUNCTION `FN_ADM_MSG_SEND_ALL`(`$p_msg_idx` INT
) RETURNS int
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 DROP FUNCTION IF EXISTS `FN_GET_IP_COUNTRY` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `FN_GET_IP_COUNTRY`(`$p_ip` VARCHAR(30)
) RETURNS varchar(50) CHARSET utf8mb3
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 DROP FUNCTION IF EXISTS `FN_STAT_CHARGE_TOTAL` */;
DELIMITER ;;
CREATE DEFINER=`kwin`@`%` FUNCTION `FN_STAT_CHARGE_TOTAL`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$member_idx` INT

) RETURNS bigint
    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 DROP FUNCTION IF EXISTS `FN_STAT_DIFF_TOTAL` */;
DELIMITER ;;
CREATE DEFINER=`kwin`@`%` FUNCTION `FN_STAT_DIFF_TOTAL`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$member_idx` INT

) RETURNS bigint
    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 DROP FUNCTION IF EXISTS `FN_STAT_EXCHANGE_TOTAL` */;
DELIMITER ;;
CREATE DEFINER=`kwin`@`%` FUNCTION `FN_STAT_EXCHANGE_TOTAL`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$member_idx` INT

) RETURNS bigint
    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 DROP FUNCTION IF EXISTS `FN_STAT_USERS_BET_SUM` */;
DELIMITER ;;
CREATE DEFINER=`kwin`@`%` FUNCTION `FN_STAT_USERS_BET_SUM`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$folder_type` CHAR(1),

	`$bet_type` INT,

	`$member_idx` INT

) RETURNS bigint
    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 DROP FUNCTION IF EXISTS `FN_STAT_USERS_TAKE_SUM` */;
DELIMITER ;;
CREATE DEFINER=`kwin`@`%` FUNCTION `FN_STAT_USERS_TAKE_SUM`(`$start_date` DATETIME,

	`$end_date` DATETIME,

	`$folder_type` CHAR(1),

	`$bet_type` INT,

	`$member_idx` INT

) RETURNS bigint
    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 DROP PROCEDURE IF EXISTS `backupDataLsports` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `backupDataLsports`(
in in_type varchar(10)
)
BEGIN

IF 'go_bet' = in_type THEN
    truncate lsports_bet_InnoDB;
	INSERT INTO lsports_bet_InnoDB
	SELECT * FROM lsports_bet;
ELSEIF 'go_policy' = in_type THEN
    truncate dividend_policy_innodb;
	INSERT INTO dividend_policy_innodb
	SELECT * FROM dividend_policy;
ELSEIF 'go_bet_sum' = in_type THEN
	truncate fixtures_bet_sum_innodb;
	INSERT INTO fixtures_bet_sum_innodb
	SELECT * FROM fixtures_bet_sum;
ELSEIF 'go_fixtures' = in_type THEN
    truncate lsports_fixtures_innodb;
	INSERT INTO lsports_fixtures_innodb
	SELECT * FROM lsports_fixtures;   
ELSEIF 'go_leagues' = in_type THEN
    truncate lsports_leagues_innodb;
	INSERT INTO lsports_leagues_innodb
	SELECT * FROM lsports_leagues;
ELSEIF 'go_locations' = in_type THEN
    truncate lsports_locations_innodb;
	INSERT INTO lsports_locations_innodb
	SELECT * FROM lsports_locations;
ELSEIF 'go_markets' = in_type THEN
    truncate lsports_markets_innodb;
	INSERT INTO lsports_markets_innodb
	SELECT * FROM lsports_markets;   
ELSEIF 'go_participant' = in_type THEN
    truncate lsports_participant_innodb;
	INSERT INTO lsports_participant_innodb
	SELECT * FROM lsports_participant;   
ELSEIF 'go_sports' = in_type THEN
    truncate lsports_sports_innodb;
	INSERT INTO lsports_sports_innodb
	SELECT * FROM lsports_sports;     
ELSEIF 'go_all' = in_type THEN
	truncate lsports_bet_InnoDB;
	INSERT INTO lsports_bet_InnoDB
	SELECT * FROM lsports_bet;

    truncate dividend_policy_innodb;
	INSERT INTO dividend_policy_innodb
	SELECT * FROM dividend_policy;
  
	truncate fixtures_bet_sum_innodb;
	INSERT INTO fixtures_bet_sum_innodb
	SELECT * FROM fixtures_bet_sum;

    truncate lsports_fixtures_innodb;
	INSERT INTO lsports_fixtures_innodb
	SELECT * FROM lsports_fixtures;   
 
    truncate lsports_leagues_innodb;
	INSERT INTO lsports_leagues_innodb
	SELECT * FROM lsports_leagues;
 
    truncate lsports_locations_innodb;
	INSERT INTO lsports_locations_innodb
	SELECT * FROM lsports_locations;
 
    truncate lsports_markets_innodb;
	INSERT INTO lsports_markets_innodb
	SELECT * FROM lsports_markets;   
 
    truncate lsports_participant_innodb;
	INSERT INTO lsports_participant_innodb
	SELECT * FROM lsports_participant;   

    truncate lsports_sports_innodb;
	INSERT INTO lsports_sports_innodb
	SELECT * FROM lsports_sports;     
 END IF;
 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `backupInnoDBSportsData` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `backupInnoDBSportsData`(
in in_type varchar(10)
)
BEGIN
-- 백업용 테이블에 데이터를 넣는데 쓴다 ... 
IF 'go_bet' = in_type THEN
   DROP TABLE  if exists `lsports_bet_backup`;
   CREATE TABLE IF NOT EXISTS `lsports_bet_backup` SELECT * FROM `lsports_bet_InnoDB`;
ELSEIF 'go_policy' = in_type THEN
   DROP TABLE  if exists `dividend_policy_backup`;
   CREATE TABLE IF NOT EXISTS `dividend_policy_backup` SELECT * FROM `dividend_policy_innodb`;
ELSEIF 'go_bet_sum' = in_type THEN
	DROP TABLE  if exists `fixtures_bet_sum_backup`;
   CREATE TABLE IF NOT EXISTS `fixtures_bet_sum_backup` SELECT * FROM `fixtures_bet_sum_innodb`;
ELSEIF 'go_fixtures' = in_type THEN
	DROP TABLE  if exists `lsports_fixtures_backup`;
   CREATE TABLE IF NOT EXISTS `lsports_fixtures_backup` SELECT * FROM `lsports_fixtures_innodb`;
ELSEIF 'go_leagues' = in_type THEN
	DROP TABLE  if exists `lsports_leagues_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_leagues_backup` SELECT * FROM `lsports_leagues_innodb`;
ELSEIF 'go_locations' = in_type THEN
	DROP TABLE  if exists `lsports_locations_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_locations_backup` SELECT * FROM `lsports_locations_innodb`;
ELSEIF 'go_markets' = in_type THEN
	DROP TABLE  if exists `lsports_markets_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_markets_backup` SELECT * FROM `lsports_markets_innodb`;
ELSEIF 'go_participant' = in_type THEN
	DROP TABLE  if exists `lsports_participant_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_participant_backup` SELECT * FROM `lsports_participant_innodb`;
ELSEIF 'go_sports' = in_type THEN
    DROP TABLE  if exists `lsports_sports_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_sports_backup` SELECT * FROM `lsports_sports_innodb`;
ELSEIF 'go_all' = in_type THEN
	DROP TABLE  if exists `lsports_bet_backup`;
	CREATE TABLE IF NOT EXISTS `lsports_bet_backup` SELECT * FROM `lsports_bet_InnoDB`;

	DROP TABLE  if exists `dividend_policy_backup`;
	CREATE TABLE IF NOT EXISTS `dividend_policy_backup` SELECT * FROM `dividend_policy_innodb`;

	DROP TABLE  if exists `fixtures_bet_sum_backup`;
	CREATE TABLE IF NOT EXISTS `fixtures_bet_sum_backup` SELECT * FROM `fixtures_bet_sum_innodb`;

	DROP TABLE  if exists `lsports_fixtures_backup`;
	CREATE TABLE IF NOT EXISTS `lsports_fixtures_backup` SELECT * FROM `lsports_fixtures_innodb`;

	DROP TABLE  if exists `lsports_leagues_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_leagues_backup` SELECT * FROM `lsports_leagues_innodb`;

	DROP TABLE  if exists `lsports_locations_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_locations_backup` SELECT * FROM `lsports_locations_innodb`;

	DROP TABLE  if exists `lsports_markets_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_markets_backup` SELECT * FROM `lsports_markets_innodb`;

	DROP TABLE  if exists `lsports_participant_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_participant_backup` SELECT * FROM `lsports_participant_innodb`;

    DROP TABLE  if exists `lsports_sports_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_sports_backup` SELECT * FROM `lsports_sports_innodb`;
 END IF;
 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `backupSportsData` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `backupSportsData`(
in in_type varchar(10)
)
BEGIN
 -- 백업용 테이블에 데이터를 넣는데 쓴다 ... 
IF 'go_bet' = in_type THEN
   DROP TABLE  if exists `lsports_bet_backup`;
   CREATE TABLE IF NOT EXISTS `lsports_bet_backup` SELECT * FROM `lsports_bet`;
ELSEIF 'go_policy' = in_type THEN
   DROP TABLE  if exists `dividend_policy_backup`;
   CREATE TABLE IF NOT EXISTS `dividend_policy_backup` SELECT * FROM `dividend_policy`;
ELSEIF 'go_bet_sum' = in_type THEN
	DROP TABLE  if exists `fixtures_bet_sum_backup`;
   CREATE TABLE IF NOT EXISTS `fixtures_bet_sum_backup` SELECT * FROM `fixtures_bet_sum`;
ELSEIF 'go_fixtures' = in_type THEN
	DROP TABLE  if exists `lsports_fixtures_backup`;
   CREATE TABLE IF NOT EXISTS `lsports_fixtures_backup` SELECT * FROM `lsports_fixtures`;
ELSEIF 'go_leagues' = in_type THEN
	DROP TABLE  if exists `lsports_leagues_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_leagues_backup` SELECT * FROM `lsports_leagues`;
ELSEIF 'go_locations' = in_type THEN
	DROP TABLE  if exists `lsports_locations_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_locations_backup` SELECT * FROM `lsports_locations`;
ELSEIF 'go_markets' = in_type THEN
	DROP TABLE  if exists `lsports_markets_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_markets_backup` SELECT * FROM `lsports_markets`;
ELSEIF 'go_participant' = in_type THEN
	DROP TABLE  if exists `lsports_participant_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_participant_backup` SELECT * FROM `lsports_participant`;
ELSEIF 'go_sports' = in_type THEN
    DROP TABLE  if exists `lsports_sports_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_sports_backup` SELECT * FROM `lsports_sports`;
ELSEIF 'go_day_cash' = in_type THEN
    DROP TABLE  if exists `total_day_cash_backup`;
    CREATE TABLE IF NOT EXISTS `total_day_cash_backup` SELECT * FROM `total_day_cash`;    
ELSEIF 'go_all' = in_type THEN
	DROP TABLE  if exists `lsports_bet_backup`;
	CREATE TABLE IF NOT EXISTS `lsports_bet_backup` SELECT * FROM `lsports_bet`;

	DROP TABLE  if exists `dividend_policy_backup`;
	CREATE TABLE IF NOT EXISTS `dividend_policy_backup` SELECT * FROM `dividend_policy`;

	DROP TABLE  if exists `fixtures_bet_sum_backup`;
	CREATE TABLE IF NOT EXISTS `fixtures_bet_sum_backup` SELECT * FROM `fixtures_bet_sum`;

	DROP TABLE  if exists `lsports_fixtures_backup`;
	CREATE TABLE IF NOT EXISTS `lsports_fixtures_backup` SELECT * FROM `lsports_fixtures`;

	DROP TABLE  if exists `lsports_leagues_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_leagues_backup` SELECT * FROM `lsports_leagues`;

	DROP TABLE  if exists `lsports_locations_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_locations_backup` SELECT * FROM `lsports_locations`;

	DROP TABLE  if exists `lsports_markets_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_markets_backup` SELECT * FROM `lsports_markets`;

	DROP TABLE  if exists `lsports_participant_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_participant_backup` SELECT * FROM `lsports_participant`;

    DROP TABLE  if exists `lsports_sports_backup`;
    CREATE TABLE IF NOT EXISTS `lsports_sports_backup` SELECT * FROM `lsports_sports`;
    
    DROP TABLE  if exists `total_day_cash_backup`;
    CREATE TABLE IF NOT EXISTS `total_day_cash_backup` SELECT * FROM `total_day_cash`;    
 END IF;
 


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `channel_roof` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `channel_roof`()
main : BEGIN

	DECLARE ret_code INT;
	DECLARE errno INT;
    DECLARE v_txt_error_msg text;
    
    DECLARE $next TEXT;
	DECLARE $value TEXT;
	DECLARE $DEL char(1) DEFAULT '/';
    
    declare v_idx int;
    DECLARE v_id varchar(20);
    DECLARE v_nick varchar(20);
    DECLARE v_account_bank varchar(20);
    DECLARE v_account_number varchar(20);
    DECLARE v_account_name varchar(20);
    DECLARE v_hp varchar(20);
    DECLARE v_memo varchar(2048);
   
    DECLARE done INTEGER DEFAULT 0;
    DEClARE openCursor CURSOR FOR SELECT id,nick,account_bank,account_number,account_name,hp,memo FROM mydb.temp_member;
 
    -- 반복문핸들러선언
	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_id,v_nick,v_account_bank,v_account_number,v_account_name,v_hp,v_memo;
                           -- 반복문종료시조건
                           IF done THEN
                                   LEAVE read_loop;
                           END IF;
                       
						   -- id 가 겹치는지 검색 한다
                           
						   IF (select 1 = 1 from mydb.`member` where id = v_id) then
							   begin
								ITERATE read_loop;
							   end;
                           end if;
                           
                           IF (select 1 = 1 from mydb.`member` where account_bank = v_account_bank AND account_number = v_account_number) then
							   begin
								ITERATE read_loop;
							   end;
                           end if;
                           
                           
                           IF (select 1 = 1 from mydb.`member` where `call` = v_hp) then
							   begin
								ITERATE read_loop;
							   end;
                           end if;
                           
                           IF (select 1 = 1 from mydb.`member` where `nick_name` = v_nick ) then
							   begin
								 set v_nick = concat(v_nick,'_1');
							   end;
                           end if;
                                                      
                           insert into `mydb`.`member`(`id`,`nick_name`,`password`,`recommend_member`,`dis_id`,`call`,`account_bank`,`account_number`,`account_name`,`point`,`channel`,`status`) 
                           values(v_id,v_nick,'$2y$10$B7sJbgrhzpos38KneWa5W.TEDn.2g2ADRMggAQ2icWEsHGdHCG4.e',2849,'te01',v_hp,v_account_bank,v_account_number,v_account_name,20000,1,1);
                           set v_idx = LAST_INSERT_ID();
                           -- 테이블에저장
                           insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 1),(v_idx, 2),(v_idx, 3),(v_idx, 4),(v_idx, 5),(v_idx, 6),(v_idx, 7),(v_idx, 8);
                         
													 
							MAIN_BLOCK:
							LOOP
							  -- 문자열이 비워있으면 LOO에서 나간다l;
							  IF CHAR_LENGTH(TRIM(v_memo)) = 0 OR v_memo IS NULL THEN
								LEAVE MAIN_BLOCK;
							  END IF;
							 
							  -- 첫번째 구분자까지 다음 문자열을 찾는다
							  SET $next = SUBSTRING_INDEX(v_memo, $DEL, 1);

							  -- 문자열 앞뒤의 공백을 자른다.
							  SET $value = TRIM($next);

							  -- 목표테이블에 데이타를 입력한다.
							  INSERT INTO t_member_memo (member_idx,content) VALUES (v_idx,$value);

							  -- 지금계산한 $next의 길이만큼의 문자열을 $list에서 삭제한다.
							  SET v_memo = INSERT(v_memo, 1, CHAR_LENGTH($next) + 1, '');
							END LOOP;

             -- 반복문끝

                  END LOOP read_loop;

         -- 커서해제

         CLOSE openCursor;
    
    
	COMMIT;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `create_account` */;
DELIMITER ;;
CREATE DEFINER=`kwin`@`%` PROCEDURE `create_account`()
BEGIN
	-- 랭킹 테스트용 계정생성, 추후 삭제필요
	DECLARE i INT DEFAULT 3;
    WHILE (i <= 100) DO 
        insert into member(id, nick_name, u_business, password, recommend_member, dis_id, `call`, account_bank, account_number, account_name, money) 
			values (concat('test_', i), concat('test_', i), 1, '$2y$10$SyAQNrQA/TQOjn1z/j4csONQ6/WA5cEA/U6S2OS9vk/525bOjCKlm', 1, 'test',
					concat('call_', i), concat('test_', i), concat('test_', i), concat('test_', i), 3000000);
        
        -- select concat('test_', i);
        SET i = i + 1;
    END WHILE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_account_number` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `insert_account_number`()
BEGIN
  -- 커서에서사용할Select 테이블선언
		 DECLARE v_id varchar(45);
         DECLARE v_idx int;
         DECLARE v_account_number varchar(45);
         DECLARE v_account_name varchar(45);
         DECLARE done INTEGER DEFAULT 0;
         DEClARE openCursor CURSOR FOR SELECT id,account_number,account_name FROM mydb.temp_account_info;
 
         -- 반복문핸들러선언
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

         -- 커서오픈
         OPEN openCursor;

                  -- 반복문시작
                  read_loop: LOOP
                           -- 커서에서데이터가져옴
                           FETCH openCursor INTO v_id,v_account_number,v_account_name;
                           -- 반복문종료시조건
                           IF done THEN
                                   LEAVE read_loop;
                           END IF;
                           
                           IF (SELECT 1 = 1 FROM `member` where id = v_id limit 1) THEN
							BEGIN
								SELECT idx INTO v_idx from `member` where id = v_id limit 1;
                                -- 테이블에저장
								insert into `personal_account_number` (`member_idx`, `account_number_1`, `account_name_1`) values(v_idx, v_account_number,v_account_name);
                            END;
                           END IF;
                          
				-- 반복문끝
                  END LOOP read_loop;

         -- 커서해제
         CLOSE openCursor;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_game_type` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `insert_game_type`()
BEGIN
  -- 커서에서사용할Select 테이블선언
		 DECLARE v_idx int;
         DECLARE done INTEGER DEFAULT 0;
         DEClARE openCursor CURSOR FOR SELECT idx FROM mydb.member;
 
         -- 반복문핸들러선언
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

         -- 커서오픈
         OPEN openCursor;

                  -- 반복문시작
                  read_loop: LOOP
                           -- 커서에서데이터가져옴
                           FETCH openCursor INTO v_idx;

                           -- 반복문종료시조건

                           IF done THEN

                                   LEAVE read_loop;

                           END IF;
                       
                           -- 테이블에저장
                           insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 1),(v_idx, 2),(v_idx, 3),(v_idx, 4),(v_idx, 5),(v_idx, 6),(v_idx, 7),(v_idx, 8);
                           -- insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 2);
                           -- insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 3);
                           -- insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 4);
                           -- insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 5);
                           -- insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 6);
                           -- insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 7);
                           -- insert into `member_game_type` (`member_idx`, `game_type`) values(v_idx, 8);

             -- 반복문끝

                  END LOOP read_loop;

         -- 커서해제

         CLOSE openCursor;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_total_member_cash` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `insert_total_member_cash`(
in dt_start datetime
,in dt_end datetime
)
BEGIN
-- 커서에서사용할Select 테이블선언
		 DECLARE v_member_idx,v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money, 
         v_loock_up_total_count,v_bet_total_count,v_bet_total_money,v_pre_s_total_money,v_pre_m_total_money,
         v_real_s_total_money,v_real_m_total_money,v_mini_total_money,v_take_total_money,v_take_mini_total_money,
         v_take_real_m_total_money,v_take_real_s_total_money,v_take_pre_m_total_money,v_take_pre_s_total_money,
         v_casino_total_bet_money,v_casino_total_win_money,v_slot_total_bet_money,v_slot_total_win_money int;
         
                 
         DECLARE done INTEGER DEFAULT 0;
         
         DEClARE openCursor CURSOR FOR 
         SELECT 
         mb.idx AS member_idx,
         IFNULL(mb_ch_his.ch_cnt,0) AS charge_total_count,
         IFNULL(mb_ch_his.ch_sum,0) AS charge_total_money,
         IFNULL(mb_ex_his.ex_cnt,0) AS exchange_total_count,
         IFNULL(mb_ex_his.ex_sum, 0) AS exchange_total_money, 
         IFNULL(loock.loock_cnt,0) AS loock_up_total_count,
	     IFNULL(mini_bet.mini_count,0) + IFNULL(mb_cnt_bet.mb_cnt_count,0) AS bet_total_count,
         IFNULL(bet.pre_bet_sum_s, 0) + IFNULL(bet.pre_bet_sum_d, 0) +  IFNULL(bet.real_bet_sum_s, 0) + IFNULL(bet.real_bet_sum_d, 0) 
         AS bet_total_money,
         IFNULL(bet.pre_bet_sum_s, 0) AS pre_s_total_money,
         IFNULL(bet.pre_bet_sum_d, 0) AS pre_m_total_money,
         IFNULL(bet.real_bet_sum_s, 0) AS real_s_total_money,
         IFNULL(bet.real_bet_sum_d, 0) AS real_m_total_money,
         IFNULL(mg_bet.mini_bet_sum_d, 0) AS mini_total_money,
		 IFNULL(bet.pre_take_sum_s, 0) +  IFNULL(bet.pre_take_sum_d, 0)  +  IFNULL(bet.real_take_sum_s, 0) + IFNULL(bet.real_take_sum_d, 0)
         AS take_total_money,
		 IFNULL(mg_bet.mini_take_sum_d, 0) AS take_mini_total_money,
         IFNULL(bet.real_take_sum_d, 0) AS take_real_m_total_money,
	     IFNULL(bet.real_take_sum_s, 0) AS take_real_s_total_money,
         IFNULL(bet.pre_take_sum_d, 0) AS take_pre_m_total_money,
         IFNULL(bet.pre_take_sum_s, 0) AS take_pre_s_total_money,
         
         IFNULL(mb_casino.casino_total_bet_money, 0) AS casino_total_bet_money,
         IFNULL(mb_casino.casino_total_win_money, 0) AS casino_total_win_money,
         
         IFNULL(mb_slot.slot_total_bet_money, 0) AS slot_total_bet_money,
         IFNULL(mb_slot.slot_total_win_money, 0) AS slot_total_win_money
		FROM (SELECT m.idx, m.id, m.nick_name from member AS m ) AS mb
    LEFT JOIN 
        (SELECT IFNULL(sum(ch_his.money), 0) AS ch_sum,
            count(idx) AS ch_cnt,
            ch_his.member_idx AS member_idx
            FROM member_money_charge_history AS ch_his 
            WHERE ch_his.status = 3
             AND ch_his.update_dt >= dt_start
             AND ch_his.update_dt <= dt_end
            GROUP BY ch_his.member_idx) AS mb_ch_his 
    ON 
    mb.idx =  mb_ch_his.member_idx
    LEFT JOIN 
        (SELECT IFNULL(sum(ex_his.money), 0) AS ex_sum,
            count(idx) AS ex_cnt,
            ex_his.member_idx AS member_idx
            FROM member_money_exchange_history AS ex_his 
            WHERE ex_his.status = 3
            AND ex_his.update_dt >= dt_start
            AND ex_his.update_dt <= dt_end
            GROUP BY ex_his.member_idx) AS mb_ex_his
    ON mb.idx =  mb_ex_his.member_idx
    LEFT JOIN 
	    (SELECT 
        mb_bet.member_idx AS member_idx,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0) AS pre_bet_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS pre_take_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0)
            - 	IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS pre_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0) AS pre_bet_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS pre_take_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0)
            - 	IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 1 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS pre_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0) AS real_bet_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS real_take_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0)
            - 	IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'S'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS real_sum_s,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0) AS real_bet_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS real_take_sum_d,
            IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.total_bet_money ELSE 0 END), 0)
            - 	IFNULL(SUM(CASE 
                        WHEN mb_bet.bet_type = 2 AND mb_bet.folder_type = 'D'
                        THEN mb_bet.take_money ELSE 0 END), 0) AS real_sum_d
	FROM member_bet AS mb_bet
        WHERE mb_bet.bet_status = 3
        AND mb_bet.calculate_dt >= dt_start
        AND mb_bet.calculate_dt <= dt_end
        AND total_bet_money != take_money
	GROUP BY mb_bet.member_idx) AS bet
    ON 
    mb.idx =  bet.member_idx
 LEFT JOIN 
	(SELECT mg.member_idx AS member_idx,
	    IFNULL(sum(mg.total_bet_money), 0) AS mini_bet_sum_d,
        IFNULL(sum(mg.take_money), 0) AS mini_take_sum_d,
        IFNULL(sum(mg.total_bet_money), 0) - IFNULL(sum(mg.take_money), 0) AS mini_sum_d
	FROM mini_game_member_bet AS mg
	WHERE mg.bet_status = 3  
        AND mg.calculate_dt >= dt_start
        AND mg.calculate_dt <= dt_end
        AND mg.total_bet_money != take_money
    GROUP BY mg.member_idx) AS mg_bet
    ON 
    mb.idx =  mg_bet.member_idx  
 LEFT JOIN
  (SELECT count(idx) as loock_cnt
  ,member_idx
  FROM t_log_cash
  WHERE   ac_code IN(103) 
         AND t_log_cash.reg_time >= dt_start
         AND t_log_cash.reg_time <= dt_end
        ) as loock
   ON mb.idx = loock.member_idx      
 LEFT JOIN 
	(SELECT count(idx) as mini_count
    ,member_idx
	FROM mini_game_member_bet AS mg
	WHERE mg.bet_status = 3  
         AND mg.calculate_dt >= dt_start
         AND mg.calculate_dt <= dt_end
    
    GROUP BY mg.member_idx) AS mini_bet
    ON 
    mb.idx =  mini_bet.member_idx    
 LEFT JOIN 
	(SELECT count(idx) as mb_cnt_count
    ,member_idx
	FROM member_bet AS mg
	WHERE mg.bet_status = 3  
         AND mg.calculate_dt >= dt_start
         AND mg.calculate_dt <= dt_end
    
    GROUP BY mg.member_idx) AS mb_cnt_bet
    ON 
    mb.idx =  mb_cnt_bet.member_idx 
 LEFT JOIN  -- 카지노 
    (SELECT 
		MBR_IDX,
        IFNULL(SUM(`BET_MNY`), 0) AS `casino_total_bet_money`,
        IFNULL(SUM(CASE
                    WHEN `TYPE` = 'W' THEN `BET_MNY` + `RSLT_MNY`
                END),
                0) AS `casino_total_win_money`
    FROM `KP_CSN_BET_HIST` 
    WHERE 
         `CALC_DTM` >= dt_start
         AND `CALC_DTM` <= dt_end
      ) AS mb_casino
    ON 
    mb.idx =  mb_casino.MBR_IDX 
    LEFT JOIN  -- 슬롯 
    (SELECT 
		MBR_IDX,
        IFNULL(SUM(`BET_MNY`), 0) AS `slot_total_bet_money`,
        IFNULL(SUM(CASE
                    WHEN `TYPE` = 'W' THEN `BET_MNY` + `RSLT_MNY`
                END),
                0) AS `slot_total_win_money`
    FROM `KP_SLOT_BET_HIST` 
    WHERE 
         `CALC_DTM` >= dt_start
         AND `CALC_DTM` <= dt_end
      ) AS mb_slot
    ON 
    mb.idx =  mb_slot.MBR_IDX 
     
   GROUP BY mb.idx; 
         
         -- 반복문핸들러선언
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

         -- 커서오픈
         OPEN openCursor;

                  -- 반복문시작
                  read_loop: LOOP
                           -- 커서에서데이터가져옴
                           FETCH openCursor INTO v_member_idx,v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money, 
												 v_loock_up_total_count,v_bet_total_count,v_bet_total_money,v_pre_s_total_money,v_pre_m_total_money,
												 v_real_s_total_money,v_real_m_total_money,v_mini_total_money,v_take_total_money,v_take_mini_total_money,
												 v_take_real_m_total_money,v_take_real_s_total_money,v_take_pre_m_total_money,v_take_pre_s_total_money,
                                                 v_casino_total_bet_money,v_casino_total_win_money,v_slot_total_bet_money,v_slot_total_win_money;
                           -- 반복문종료시조건
                           IF done THEN
                                   LEAVE read_loop;
                           END IF;
                       
                           -- 테이블에저장
                           insert into `total_member_cash` ( member_idx,charge_total_count,charge_total_money,exchange_total_count,exchange_total_money, 
												 loock_up_total_count,bet_total_count,bet_total_money,pre_s_total_money,pre_m_total_money,
												 real_s_total_money,real_m_total_money,mini_total_money,take_total_money,take_mini_total_money,
												 take_real_m_total_money,take_real_s_total_money,take_pre_m_total_money,take_pre_s_total_money,
                                                 casino_total_money,take_casino_total_money,slot_total_money,take_slot_total_money) 
						   values(v_member_idx,v_charge_total_count,v_charge_total_money,v_exchange_total_count,v_exchange_total_money, 
												 v_loock_up_total_count,v_bet_total_count,v_bet_total_money,v_pre_s_total_money,v_pre_m_total_money,
												 v_real_s_total_money,v_real_m_total_money,v_mini_total_money,v_take_total_money,v_take_mini_total_money,
												 v_take_real_m_total_money,v_take_real_s_total_money,v_take_pre_m_total_money,v_take_pre_s_total_money,
                                                 v_casino_total_bet_money,v_casino_total_win_money,v_slot_total_bet_money,v_slot_total_win_money)
                                                 ON DUPLICATE KEY UPDATE
                                                 charge_total_count  = charge_total_count + VALUES(charge_total_count),
                                                 charge_total_money = charge_total_money + VALUES(charge_total_money),
                                                 exchange_total_count = exchange_total_count + VALUES(exchange_total_count),
                                                 exchange_total_money  = exchange_total_money + VALUES(exchange_total_money), 
												 loock_up_total_count  = loock_up_total_count + VALUES(loock_up_total_count),
                                                 bet_total_count  = bet_total_count + VALUES(bet_total_count),
                                                 bet_total_money  = bet_total_money + VALUES(bet_total_money),
                                                 pre_s_total_money  = pre_s_total_money + VALUES(pre_s_total_money),
                                                 pre_m_total_money  = pre_m_total_money + VALUES(pre_m_total_money),
												 real_s_total_money  = real_s_total_money + VALUES(real_s_total_money),
                                                 real_m_total_money  = real_m_total_money + VALUES(real_m_total_money),
                                                 mini_total_money  = mini_total_money + VALUES(mini_total_money),
                                                 take_total_money  = take_total_money + VALUES(take_total_money),
                                                 take_mini_total_money  = take_mini_total_money + VALUES(take_mini_total_money),
												 take_real_m_total_money  = take_real_m_total_money + VALUES(take_real_m_total_money),
                                                 take_real_s_total_money  = take_real_s_total_money + VALUES(take_real_s_total_money),
                                                 take_pre_m_total_money  = take_pre_m_total_money + VALUES(take_pre_m_total_money),
                                                 take_pre_s_total_money   = take_pre_s_total_money + VALUES(take_pre_s_total_money),

                                                 casino_total_money  = casino_total_money + VALUES(casino_total_money),
                                                 take_casino_total_money  = take_casino_total_money + VALUES(take_casino_total_money),
                                                 slot_total_money  = slot_total_money + VALUES(slot_total_money),
                                                 take_slot_total_money   = take_slot_total_money + VALUES(take_slot_total_money)
                                                 ;

             -- 반복문끝

                  END LOOP read_loop;

         -- 커서해제

         CLOSE openCursor;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rollbackBetInfo` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `rollbackBetInfo`(
in in_idx int
)
main: BEGIN
	-- member_bet,member_bet_detail,member_money_charge_history,fixtures_bet_sum,t_log_cash,total_member_cash
    declare ret_idx int default 0;
    declare ret_member_idx int default 0;
    declare ret_bet_status int default 0;
    declare ret_bet_type int default 0;
    declare ret_total_bet_money int default 0;
    declare ret_folder_type int default 0;
    declare ret_take_money int default 0;
    declare ret_take_point int default 0;
    declare ret_create_dt datetime default null;
    
   	DECLARE ret_code INT;
	DECLARE errno INT;
    DECLARE v_txt_error_msg text;


    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;
    
    select idx     , member_idx,    bet_status,     bet_type,     total_bet_money,     folder_type,     take_money,     take_point,     create_dt
    into   ret_idx, ret_member_idx, ret_bet_status, ret_bet_type, ret_total_bet_money, ret_folder_type, ret_take_money, ret_take_point, ret_create_dt
    from member_bet where idx = in_idx;
    
    if 3 <> ret_bet_status OR ret_take_money <> 0 then
		leave main;
    end if;
    
    INSERT INTO `member_bet_bakup` SELECT * FROM `member_bet` where idx = ret_idx;
    INSERT INTO `member_bet_detail_backup` SELECT * FROM `member_bet_detail` where bet_idx = ret_idx;
    INSERT INTO `t_log_cash_backup` SELECT * FROM `t_log_cash` where ac_idx = ret_idx;
    
    DELETE FROM `member_bet` WHERE idx = ret_idx;
    DELETE FROM `member_bet_detail` where bet_idx = ret_idx;
    DELETE FROM `t_log_cash` where ac_idx = ret_idx;
    
    UPDATE member SET money = money + ret_total_bet_money WHERE idx = ret_member_idx;
    
    COMMIT;
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rollbackDataSports` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `rollbackDataSports`(
in in_type varchar(10)
)
BEGIN

IF 'go_bet' = in_type THEN
    truncate lsports_bet;
	INSERT INTO lsports_bet
	SELECT * FROM lsports_bet_InnoDB;
ELSEIF 'go_policy' = in_type THEN
    truncate dividend_policy;
	INSERT INTO dividend_policy
	SELECT * FROM dividend_policy_innodb;
ELSEIF 'go_bet_sum' = in_type THEN
	truncate fixtures_bet_sum;
	INSERT INTO fixtures_bet_sum
	SELECT * FROM fixtures_bet_sum_innodb;
ELSEIF 'go_fixtures' = in_type THEN
    truncate lsports_fixtures;
	INSERT INTO lsports_fixtures
	SELECT * FROM lsports_fixtures_innodb;   
ELSEIF 'go_leagues' = in_type THEN
    truncate lsports_leagues;
	INSERT INTO lsports_leagues
	SELECT * FROM lsports_leagues_innodb;
ELSEIF 'go_locations' = in_type THEN
    truncate lsports_locations;
	INSERT INTO lsports_locations
	SELECT * FROM lsports_locations_innodb;
ELSEIF 'go_markets' = in_type THEN
    truncate lsports_markets;
	INSERT INTO lsports_markets
	SELECT * FROM lsports_markets_innodb;   
ELSEIF 'go_participant' = in_type THEN
    truncate lsports_participant;
	INSERT INTO lsports_participant
	SELECT * FROM lsports_participant_innodb;   
ELSEIF 'go_sports' = in_type THEN
    truncate lsports_sports;
	INSERT INTO lsports_sports
	SELECT * FROM lsports_sports_innodb;     
ELSEIF 'go_all' = in_type THEN
	truncate lsports_bet;
	INSERT INTO lsports_bet
	SELECT * FROM lsports_bet_InnoDB;

    truncate dividend_policy;
	INSERT INTO dividend_policy
	SELECT * FROM dividend_policy_innodb;
  
	truncate fixtures_bet_sum;
	INSERT INTO fixtures_bet_sum
	SELECT * FROM fixtures_bet_sum_innodb;

    truncate lsports_fixtures;
	INSERT INTO lsports_fixtures
	SELECT * FROM lsports_fixtures_innodb;   
 
    truncate lsports_leagues;
	INSERT INTO lsports_leagues
	SELECT * FROM lsports_leagues_innodb;
 
    truncate lsports_locations;
	INSERT INTO lsports_locations
	SELECT * FROM lsports_locations_innodb;
 
    truncate lsports_markets;
	INSERT INTO lsports_markets
	SELECT * FROM lsports_markets_innodb;   
 
    truncate lsports_participant;
	INSERT INTO lsports_participant
	SELECT * FROM lsports_participant_innodb;   

    truncate lsports_sports;
	INSERT INTO lsports_sports
	SELECT * FROM lsports_sports_innodb;     
 END IF;
 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rollbackToMemoryDB` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `rollbackToMemoryDB`(
in in_type varchar(10)
)
BEGIN
	
IF 'go_bet' = in_type THEN
    truncate lsports_bet;
	truncate lsports_bet_InnoDB;
	INSERT INTO lsports_bet
	SELECT * FROM lsports_bet_backup;
	-- DROP TABLE  if exists `lsports_bet_backup`;
ELSEIF 'go_policy' = in_type THEN
    truncate dividend_policy;
	truncate dividend_policy_innodb;
	INSERT INTO dividend_policy
	SELECT * FROM dividend_policy_backup;
    -- DROP TABLE  if exists `dividend_policy_backup`;
ELSEIF 'go_bet_sum' = in_type THEN
	truncate fixtures_bet_sum;
	truncate fixtures_bet_sum_innodb;
	INSERT INTO fixtures_bet_sum
	SELECT * FROM fixtures_bet_sum_backup;
    -- DROP TABLE  if exists `fixtures_bet_sum_backup`;
ELSEIF 'go_fixtures' = in_type THEN
    truncate lsports_fixtures;
    truncate lsports_fixtures_innodb;
	INSERT INTO lsports_fixtures
	SELECT * FROM lsports_fixtures_backup;   
    -- DROP TABLE  if exists `lsports_fixtures_backup`;
ELSEIF 'go_leagues' = in_type THEN
    truncate lsports_leagues;
	INSERT INTO lsports_leagues
	SELECT * FROM lsports_leagues_innodb;
    -- DROP TABLE  if exists `lsports_leagues_innodb`;
ELSEIF 'go_locations' = in_type THEN
    truncate lsports_locations;
	truncate lsports_locations_innodb;
	INSERT INTO lsports_locations
	SELECT * FROM lsports_locations_backup;
    -- DROP TABLE  if exists `lsports_locations_backup`;
ELSEIF 'go_markets' = in_type THEN
    truncate lsports_markets;
	truncate lsports_markets_innodb;
	INSERT INTO lsports_markets
	SELECT * FROM lsports_markets_backup;   
    -- DROP TABLE  if exists `lsports_markets_backup`;
ELSEIF 'go_participant' = in_type THEN
    truncate lsports_participant;
	truncate lsports_participant_innodb;
	INSERT INTO lsports_participant
	SELECT * FROM lsports_participant_backup;   
    -- DROP TABLE  if exists `lsports_participant_backup`;
ELSEIF 'go_sports' = in_type THEN
    truncate lsports_sports;
	truncate lsports_sports_innodb;
	INSERT INTO lsports_sports
	SELECT * FROM lsports_sports_backup;     
    -- DROP TABLE  if exists `lsports_sports_backup`;
    
ELSEIF 'go_day_cash' = in_type THEN
    truncate total_day_cash;
	INSERT INTO total_day_cash
	SELECT * FROM total_day_cash_backup;     
    -- DROP TABLE  if exists `total_day_cash_backup`;    
ELSEIF 'go_all' = in_type THEN
	truncate lsports_bet;
    truncate lsports_bet_InnoDB;
	INSERT INTO lsports_bet
	SELECT * FROM lsports_bet_backup;
	-- DROP TABLE  if exists `lsports_bet_backup`;
    
    truncate dividend_policy;
    truncate dividend_policy_innodb;
	INSERT INTO dividend_policy
	SELECT * FROM dividend_policy_backup;
    -- DROP TABLE  if exists `dividend_policy_backup`;
    
	truncate fixtures_bet_sum;
    truncate fixtures_bet_sum_innodb;
	INSERT INTO fixtures_bet_sum
	SELECT * FROM fixtures_bet_sum_backup;
    -- DROP TABLE  if exists `fixtures_bet_sum_backup`;
    
    truncate lsports_fixtures;
    truncate lsports_fixtures_innodb;
	INSERT INTO lsports_fixtures
	SELECT * FROM lsports_fixtures_backup;   
    -- DROP TABLE  if exists `lsports_fixtures_backup`;
    
    truncate lsports_leagues;
	truncate lsports_leagues_innodb;
	INSERT INTO lsports_leagues
	SELECT * FROM lsports_leagues_backup;
    -- DROP TABLE  if exists `lsports_leagues_backup`;
    
    truncate lsports_locations;
    truncate lsports_locations_innodb;
	INSERT INTO lsports_locations
	SELECT * FROM lsports_locations_backup;
    -- DROP TABLE  if exists `lsports_locations_backup`;
    
    truncate lsports_markets;
    truncate lsports_markets_innodb;
	INSERT INTO lsports_markets
	SELECT * FROM lsports_markets_backup;   
    -- DROP TABLE  if exists `lsports_markets_backup`;
    
    truncate lsports_participant;
    truncate lsports_participant_innodb;
	INSERT INTO lsports_participant
	SELECT * FROM lsports_participant_backup;   
    -- DROP TABLE  if exists `lsports_participant_backup`;
    
    truncate lsports_sports;
    truncate lsports_sports_innodb;
	INSERT INTO lsports_sports
	SELECT * FROM lsports_sports_backup;     
    -- DROP TABLE  if exists `lsports_sports_backup`;
    
	truncate total_day_cash;
	INSERT INTO total_day_cash
	SELECT * FROM total_day_cash_backup;     
    -- DROP TABLE  if exists `total_day_cash_backup`;    
 END IF;
 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spInsertUpdateODDLeagueInfo` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `spInsertUpdateODDLeagueInfo`(
	IN Iid INT
    , Iname VARCHAR(45)
    , IlocationId INT
    , IsportId INT
    , Iseason VARCHAR(45)
)
main: BEGIN

	INSERT INTO `mydb`.`lsports_leagues` (id, bet_type, `name`, location_id, sport_id, season, lang)
	VALUES (Iid, 1, Iname, IlocationId, IsportId, Iseason, 'ko')
	ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), location_id = VALUES(location_id), sport_id = VALUES(sport_id), season = VALUES(season), lang = VALUES(lang);
		
	INSERT INTO `mydb`.`lsports_leagues` (id, bet_type, `name`, location_id, sport_id, season, lang)
	VALUES (Iid, 2, Iname, IlocationId, IsportId, Iseason, 'ko')
	ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), location_id = VALUES(location_id), sport_id = VALUES(sport_id), season = VALUES(season), lang = VALUES(lang);
     
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSelectFixtureMarketsBetType3Table` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `spSelectFixtureMarketsBetType3Table`(
	IN IbetType TINYINT
    , IfixtureId INT
)
main: BEGIN
/*
	SELECT markets.id, markets.sport_id, markets.main_book_maker, sub_book_maker, markets.limit_bet_price, markets.max_bet_price, markets.input_refund_rate as mk_input_refund_rate 
	FROM mydb.lsports_markets as markets 
	WHERE markets.bet_group = IbetType AND markets.is_delete = 0;
*/
    
	-- sports, league 정보 가져 온다
    IF 1 = IbetType THEN
		SELECT fix.display_status, fix.fixture_start_date,
        fix.fixture_sport_id, fix.fixture_league_id,
        sports.deduction_refund_rate as sp_deduction_refund_rate, leagues.deduction_refund_rate as lg_deduction_refund_rate
		FROM lsports_fixtures as fix
		LEFT JOIN   lsports_sports as sports
		ON   fix.fixture_sport_id = sports.id   
		LEFT JOIN   lsports_leagues as leagues
		ON   fix.fixture_league_id = leagues.id   
		WHERE fix.fixture_id = IfixtureId AND fix.bet_type = IbetType
		AND sports.is_use = 1 AND sports.bet_type = IbetType AND leagues.bet_type = IbetType AND leagues.is_use = 1;
    ELSE
		SELECT fix.display_status, fix.fixture_start_date, fix.fixture_sport_id,
        fix.fixture_league_id,
        sports.deduction_refund_rate as sp_deduction_refund_rate, leagues.deduction_refund_rate as lg_deduction_refund_rate
		FROM lsports_fixtures as fix
		LEFT JOIN   lsports_sports as sports
		ON   fix.fixture_sport_id = sports.id   
		LEFT JOIN   lsports_leagues as leagues
		ON   fix.fixture_league_id = leagues.id   
		WHERE fix.fixture_id = IfixtureId AND fix.bet_type = IbetType
		AND sports.is_use = 1 AND sports.bet_type = IbetType AND leagues.bet_type = IbetType;
    END IF;
     
END main ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spUpdateFixturesDataType2` */;
DELIMITER ;;
CREATE DEFINER=`kadminwin`@`%` PROCEDURE `spUpdateFixturesDataType2`(
	IN IbetType TINYINT
    , IfixtureId INT
    , IliveTime VARCHAR(45)
    , IliveCurrentPeriod INT
    , Istatus VARCHAR(2)
    , IdisplayStatus INT
    , Ivalue1 INT
    , Ivalue2 INT
    , IliveScore MEDIUMTEXT -- VARCHAR(16384)
)
main: BEGIN

	DECLARE retcode INT DEFAULT 0;
	DECLARE tDisplayStatus INT DEFAULT 0;
	DECLARE tFixtureStartDate DATETIME DEFAULT 0;
	DECLARE tFixtureSportsId INT DEFAULT 0;
    
	IF 1 = IbetType THEN
		SELECT fixtures.display_status, fixtures.fixture_start_date, fixtures.fixture_sport_id INTO tDisplayStatus, tFixtureStartDate, tFixtureSportsId from `mydb`.`lsports_fixtures` as fixtures
		LEFT JOIN `mydb`.`lsports_leagues` as leagues ON fixtures.fixture_league_id = leagues.id AND fixtures.fixture_sport_id = leagues.sport_id
		WHERE fixtures.fixture_id = IfixtureId AND fixtures.display_status IN (1,2) AND fixtures.bet_type = IbetType AND leagues.is_use = 1 ORDER BY fixtures.fixture_start_date DESC LIMIT 1;
    ELSE
		SELECT fixtures.display_status,fixtures.fixture_start_date,fixtures.fixture_sport_id INTO tDisplayStatus, tFixtureStartDate, tFixtureSportsId from `mydb`.`lsports_fixtures` as fixtures
		WHERE fixtures.fixture_id = IfixtureId AND fixtures.display_status IN (1,2) AND fixtures.bet_type = IbetType ORDER BY fixtures.fixture_start_date DESC LIMIT 1;
    END IF;
    
    IF 0 != IdisplayStatus THEN
		SET tDisplayStatus = IdisplayStatus;
    END IF;

-- 예외처리    
    IF 0 = tDisplayStatus AND 0 = tFixtureStartDate AND 0 = tFixtureSportsId THEN
		SET retcode = -1;
		SELECT retcode;
		LEAVE main;
    END IF;

    IF (1 = IdisplayStatus OR 2 = IdisplayStatus) AND 3 = tDisplayStatus THEN
		SET retcode = -2;
		SELECT retcode;
		LEAVE main;
    END IF;
    
    IF '2' = Istatus OR '3' = Istatus THEN
		IF now() < tFixtureStartDate THEN
			SET retcode = -3;
			SELECT retcode;
			LEAVE main;
		END IF;
    END IF;
--
    
	UPDATE `mydb`.`lsports_fixtures` 
    SET live_time_x = IliveTime, live_current_period_x = IliveCurrentPeriod, display_status = tDisplayStatus, fixture_status = Istatus, live_results_p1 = Ivalue1, live_results_p2 = Ivalue2,
	livescore = IliveScore
    WHERE fixture_id = IfixtureId AND bet_type = IbetType;
    
    SELECT retcode;
     
END main ;;
DELIMITER ;

--
-- Final view structure for view `day_ch_ex`
--

/*!50001 DROP VIEW IF EXISTS `day_ch_ex`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`pinkey`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `day_ch_ex` AS select 'ch' AS `stype`,`mch`.`status` AS `status`,count(0) AS `cnt`,sum(`mch`.`money`) AS `s_money` from (`member_money_charge_history` `mch` left join `member` on((`mch`.`member_idx` = `member`.`idx`))) where ((`mch`.`update_dt` >= date_format(now(),'%Y-%m-%d 00:00:00')) and (`mch`.`update_dt` <= date_format(now(),'%Y-%m-%d 23:59:59')) and (`member`.`level` <> 9)) group by `mch`.`status` union all select 'ex' AS `stype`,`meh`.`status` AS `status`,count(0) AS `cnt`,sum(`meh`.`money`) AS `s_money` from (`member_money_exchange_history` `meh` left join `member` on((`meh`.`member_idx` = `member`.`idx`))) where ((cast(`meh`.`update_dt` as date) >= cast(now() as date)) and (`member`.`level` <> 9)) group by `meh`.`status` union all select 'ch_tot' AS `stype`,`mch`.`status` AS `status`,count(0) AS `cnt`,sum(`mch`.`money`) AS `s_money` from (`member_money_charge_history` `mch` left join `member` on((`mch`.`member_idx` = `member`.`idx`))) where ((`mch`.`update_dt` >= date_format(now(),'%Y-%m-%d 00:00:00')) and (`mch`.`update_dt` <= date_format(now(),'%Y-%m-%d 23:59:59')) and (`member`.`level` <> 9)) group by `mch`.`status` union all select 'ex_tot' AS `stype`,`meh`.`status` AS `status`,count(0) AS `cnt`,sum(`meh`.`money`) AS `s_money` from (`member_money_exchange_history` `meh` left join `member` on((`meh`.`member_idx` = `member`.`idx`))) where ((`meh`.`update_dt` >= date_format(now(),'%Y-%m-%d 00:00:00')) and (`meh`.`update_dt` <= date_format(now(),'%Y-%m-%d 23:59:59')) and (`member`.`level` <> 9)) group by `meh`.`status` */;

--
-- Final view structure for view `day_now_call_check`
--

/*!50001 DROP VIEW IF EXISTS `day_now_call_check`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`pinkey`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `day_now_call_check` AS select 'mem' AS `stype`,`member`.`status` AS `status`,count(0) AS `cnt`,'0' AS `s_money` from `member` where ((`member`.`reg_time` >= date_format(now(),'%Y-%m-%d 00:00:00')) and (`member`.`reg_time` <= date_format(now(),'%Y-%m-%d 23:59:59')) and (`member`.`status` in (1,11))) group by `member`.`status` union all select 'mem_leave' AS `stype`,`member`.`status` AS `status`,count(0) AS `cnt`,'0' AS `s_money` from `member` where ((`member`.`leave_time` >= date_format(now(),'%Y-%m-%d 00:00:00')) and (`member`.`leave_time` <= date_format(now(),'%Y-%m-%d 23:59:59')) and (`member`.`status` = 3)) union all select 'mem_sound' AS `stype`,`member`.`status` AS `status`,count(0) AS `cnt`,'0' AS `s_money` from `member` where ((`member`.`reg_time` >= date_format(now(),'%Y-%m-%d 00:00:00')) and (`member`.`reg_time` <= date_format(now(),'%Y-%m-%d 23:59:59')) and (`member`.`status` = 11)) */;


