/* Navicat Premium Dump SQL Source Server : game_grade Source Server Type : MySQL Source Server Version : 80036 (8.0.36) Source Host : rm-bp1749tfxu2rpq670lo.mysql.rds.aliyuncs.com:3306 Source Schema : grade_db Target Server Type : MySQL Target Server Version : 80036 (8.0.36) File Encoding : 65001 Date: 15/03/2026 19:29:34 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for coin_rebate -- ---------------------------- DROP TABLE IF EXISTS `coin_rebate`; CREATE TABLE `coin_rebate` ( `idx` int NOT NULL AUTO_INCREMENT, `core_agentid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '代理商id', `core_gameid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '游戏id', `core_playerid` int NOT NULL COMMENT '玩家id', `core_isrobot` tinyint(1) NULL DEFAULT 0, `core_roomcode` int NOT NULL COMMENT '房号', `core_createtime` datetime NULL DEFAULT NULL COMMENT '创建时间', `core_roomtype` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '房间类型', `core_roommode` tinyint(1) NOT NULL COMMENT '0-普通星星场房间 1-短号星星场房间 2-系统无限局星星场房间', `core_ownerid` int NOT NULL COMMENT '房主id', `core_deduct` tinyint(1) NULL DEFAULT NULL COMMENT '0-房主扣卡 1-每人扣卡 2-免房卡', `core_roomcard` int NULL DEFAULT NULL COMMENT '扣的房卡数量', `core_datatype` tinyint(1) NULL DEFAULT NULL COMMENT '0-大局系统抽成数据 1-小局系统抽成数据', `core_asetcount` int NULL DEFAULT NULL COMMENT '总局数', `core_asetnum` int NULL DEFAULT NULL COMMENT '当前小局号 从1开始计数', `core_grade` int NOT NULL COMMENT '玩家得分', `core_mode` tinyint NOT NULL COMMENT '抽成模式 1-按比例 2-按固定值', `core_setting` int NOT NULL COMMENT '抽成设置的值', `core_value` int NOT NULL COMMENT '实际抽成数量', `core_time` datetime NOT NULL COMMENT '抽成时间', PRIMARY KEY (`idx`) USING BTREE, INDEX `coin_rebate`(`core_agentid` ASC, `core_playerid` ASC, `core_gameid` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 93059 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '金币抽成表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for error -- ---------------------------- DROP TABLE IF EXISTS `error`; CREATE TABLE `error` ( `idx` int NOT NULL AUTO_INCREMENT, `erro_agentid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '代理商id', `erro_playerid` int NOT NULL COMMENT '玩家id', `erro_gameid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '游戏id', `erro_msg` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '错误信息', `erro_packet` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '收到的包', `erro_time` datetime NULL DEFAULT NULL COMMENT '时间', PRIMARY KEY (`idx`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 772430 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '错误日志表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for gold_account -- ---------------------------- DROP TABLE IF EXISTS `gold_account`; CREATE TABLE `gold_account` ( `idx` int NOT NULL AUTO_INCREMENT, `goac_id` varchar(23) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT 'id，时间戳+房号+4位随机码', `goac_agentid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '代理商id', `goac_gameid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '游戏id', `goac_roomcode` int NOT NULL COMMENT '房间号', `goac_roommode` tinyint(1) NOT NULL COMMENT '0-星星场玩家房间 1-星星场系统房间 2-非星星场(星星限进)', `goac_createtime` datetime NOT NULL COMMENT '房间的创建时间', `goac_beanmult` int NOT NULL COMMENT '倍数', `goac_ownerid` int NULL DEFAULT NULL COMMENT '房主的玩家id', `goac_shortcode` int NULL DEFAULT NULL COMMENT '短号', `goac_deduct` tinyint(1) NULL DEFAULT NULL COMMENT '0-房主扣卡 1-每人扣卡 2-大赢家扣卡', `goac_roomcard` int NULL DEFAULT NULL COMMENT '扣的房卡数量', `goac_datatype` tinyint(1) NOT NULL COMMENT '0-大局结算数据 1-小局结算数据', `goac_asetcount` int NULL DEFAULT NULL COMMENT '总局数', `goac_asetnum` int NULL DEFAULT NULL COMMENT '当前小局号，从1开始计数', `goac_playerid` int NULL DEFAULT NULL COMMENT '玩家id', `goac_isrobot` tinyint(1) NULL DEFAULT 0 COMMENT '0-真实玩家 1-机器人', `goac_grade` int NOT NULL COMMENT '玩家得分', `goac_rebatefrom` tinyint(1) NOT NULL COMMENT '0-不抽成 1-对大赢家进行抽成 2-对所有赢家抽成 3-对所有玩家抽成', `goac_rebateto` tinyint(1) NULL DEFAULT NULL COMMENT '0-抽成给系统 1-抽成给房主 2-抽成给指定玩家 3-抽成给指定玩家的代理账号', `goac_toplayerid` int NULL DEFAULT NULL COMMENT '抽成给到的玩家id', `goac_rebatemode` tinyint(1) NULL DEFAULT NULL COMMENT '1-按得分的百分比抽成 2-按倍数的百分比抽成 3-按固定值抽成', `goac_setvalue` int NULL DEFAULT NULL COMMENT '抽成设置的值', `goac_rebatevalue` int NULL DEFAULT NULL COMMENT '实际抽成数量', `goac_time` datetime NOT NULL COMMENT '发生时间', `goac_state` tinyint(1) NULL DEFAULT 0 COMMENT '代理后台处理状态 0:未处理 1:已处理', `goac_statetime` datetime NULL DEFAULT NULL COMMENT '代理后台处理时间', `goac_invitecode` int NULL DEFAULT NULL, `goac_inviteid` int NULL DEFAULT NULL, `goac_marketid` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL, `goac_i1` int NULL DEFAULT NULL, `goac_i2` int NULL DEFAULT NULL, `goac_i3` int NULL DEFAULT NULL, PRIMARY KEY (`idx`) USING BTREE, INDEX `gold_account_index2`(`goac_agentid` ASC, `goac_playerid` ASC) USING BTREE, INDEX `gold_account_index1`(`goac_id` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 20865676 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '金币变动表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for match -- ---------------------------- DROP TABLE IF EXISTS `match`; CREATE TABLE `match` ( `idx` int NOT NULL AUTO_INCREMENT, `matc_agentid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '代理商id', `matc_gameid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '游戏id', `matc_matchid` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '比赛id', `matc_matchidx` tinyint NOT NULL COMMENT '比赛循环号', `matc_matchname` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '比赛名称', `matc_beginTime` datetime NOT NULL COMMENT '开始时间', `matc_endTime` datetime NOT NULL COMMENT '结束时间', `matc_roomtype` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '房间类型', `matc_imageurl` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '比赛图片地址', `matc_playercount` int NOT NULL, `matc_playercount2` int NOT NULL, `matc_bean` int NOT NULL, `matc_roomcard` int NOT NULL, `matc_topcount` int NOT NULL, `matc_circulation` int NOT NULL, `matc_interval` int NOT NULL, `matc_gradename` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, `matc_memo1` varchar(800) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, `matc_memo2` varchar(800) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, `matc_memo3` varchar(800) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, `matc_ranking` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, PRIMARY KEY (`idx`) USING BTREE, INDEX `match_index`(`matc_agentid` ASC, `matc_gameid` ASC, `matc_matchid` ASC, `matc_matchidx` ASC) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '比赛表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for match_ranking -- ---------------------------- DROP TABLE IF EXISTS `match_ranking`; CREATE TABLE `match_ranking` ( `idx` int NOT NULL AUTO_INCREMENT, `mara_agentid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '代理商id', `mara_gameid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '游戏id', `mara_matchid` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '比赛id', `mara_matchidx` tinyint NOT NULL COMMENT '比赛循环号', `mara_playerid` int NOT NULL COMMENT '玩家id', `mara_nickname` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '玩家昵称', `mara_avatar` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '玩家头像', `mara_roomcode` int NOT NULL COMMENT '房间号', `mara_overtime` datetime NOT NULL COMMENT '房间结束时间', `mara_grade` int NOT NULL COMMENT '得分', `mara_isrobot` tinyint(1) NULL DEFAULT 0 COMMENT '0-玩家 1-机器人', PRIMARY KEY (`idx`) USING BTREE, INDEX `match_ranking_index`(`mara_agentid` ASC, `mara_gameid` ASC, `mara_matchid` ASC, `mara_matchidx` ASC, `mara_playerid` ASC) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '比赛排行表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for player_grade -- ---------------------------- DROP TABLE IF EXISTS `player_grade`; CREATE TABLE `player_grade` ( `idx` int NOT NULL AUTO_INCREMENT, `plgr_agentid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '代理商id', `plgr_playerid` int NOT NULL COMMENT '玩家id', `plgr_gameid` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '游戏id', `plgr_roomcode` int NOT NULL COMMENT '房号', `plgr_ownerid` int NULL DEFAULT NULL COMMENT '房主id', `plgr_roomtype` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '房间类型', `plgr_createtime` datetime NOT NULL COMMENT '开房时间', `plgr_makewartime` datetime NOT NULL COMMENT '开战时间', `plgr_overtime` datetime NOT NULL COMMENT '结束时间', `plgr_roomcard` int NULL DEFAULT NULL, `plgr_ownercard` int NULL DEFAULT NULL, `plgr_gameinfo1` varchar(10000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT '' COMMENT '一局游戏的完整数据 json格式', `plgr_gameinfo2` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '一局游戏的完整数据 json格式', `plgr_shortcode` int NULL DEFAULT NULL, `plgr_deducttype` tinyint(1) NULL DEFAULT NULL, `plgr_deductcard` int NULL DEFAULT NULL, `plgr_asetcount` int NULL DEFAULT NULL, `plgr_roommode` tinyint(1) NULL DEFAULT NULL, `plgr_winner` tinyint(1) NOT NULL DEFAULT 0 COMMENT '大赢家', `plgr_score` int NOT NULL DEFAULT 0 COMMENT '得分', PRIMARY KEY (`idx`) USING BTREE, INDEX `player_grade2_index`(`plgr_agentid` ASC, `plgr_playerid` ASC, `plgr_gameid` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 36393310 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '玩家战绩表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for process_log -- ---------------------------- DROP TABLE IF EXISTS `process_log`; CREATE TABLE `process_log` ( `idx` int NOT NULL AUTO_INCREMENT, `prlo_optid` int NOT NULL COMMENT '代理后台接口数据optid', `prlo_result` tinyint(1) NOT NULL COMMENT '-1:处理失败 1:处理成功', `prlo_time` datetime NOT NULL COMMENT '处理时间', `prlo_ip` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '子游戏服务器IP和端口', `prlo_error` varchar(2000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '错误信息', PRIMARY KEY (`idx`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4924417 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '代理后台接口数据处理日志表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Procedure structure for cp_game_grade_del -- ---------------------------- DROP PROCEDURE IF EXISTS `cp_game_grade_del`; delimiter ;; CREATE PROCEDURE `cp_game_grade_del`() SQL SECURITY INVOKER COMMENT '删除超时战绩' begin -- 删除24小时之前的战绩记录 delete from player_grade where (unix_timestamp(now()) - unix_timestamp(plgr_makewartime)) > 24 * 60 * 60; -- 删除72小时之前的金币记录 delete from gold_account where (unix_timestamp(now()) - unix_timestamp(goac_time)) > 8 * 24 * 60 * 60; -- 删除72小时之前的代理后台接口数据处理日志 delete from process_log where (unix_timestamp(now()) - unix_timestamp(prlo_time)) > 72 * 60 * 60; end ;; delimiter ; -- ---------------------------- -- Procedure structure for cp_game_grade_get1 -- ---------------------------- DROP PROCEDURE IF EXISTS `cp_game_grade_get1`; delimiter ;; CREATE PROCEDURE `cp_game_grade_get1`(IN `agentid` varchar(32) charset utf8 collate utf8_general_ci ,IN `playerid` int(8) ,IN `gameid` varchar(32) charset utf8 collate utf8_general_ci ,IN `ownertype` tinyint(1) ,IN `direction` tinyint(1) ,IN `gradeidx` int(11)) SQL SECURITY INVOKER COMMENT '获取战绩gameinfo1' begin if ifnull(ownertype, 0) = 0 then -- 获取最近10条战绩记录 select idx, plgr_roomcode as roomcode, plgr_roomtype as roomtype, DATE_FORMAT(plgr_createtime, "%m.%d %H:%i") as createtime, DATE_FORMAT(plgr_makewartime,"%m.%d %H:%i") as makewartime, DATE_FORMAT(plgr_overtime,"%m.%d %H:%i") as overtime, plgr_roomcard as roomcard, plgr_gameinfo1 as gameinfo1 from player_grade where plgr_agentid = agentid and plgr_playerid = playerid and plgr_gameid = gameid order by idx desc limit 10; elseif isnull(direction) or isnull(gradeidx) then select idx, plgr_roomcode as roomcode, plgr_roomtype as roomtype, DATE_FORMAT(plgr_createtime, "%m.%d %H:%i") as createtime, DATE_FORMAT(plgr_makewartime,"%m.%d %H:%i") as makewartime, DATE_FORMAT(plgr_overtime,"%m.%d %H:%i") as overtime, plgr_ownercard as roomcard, plgr_gameinfo1 as gameinfo1 from player_grade where plgr_agentid = agentid and plgr_ownerid = playerid and plgr_gameid = gameid and plgr_ownercard is not null and (unix_timestamp(now()) - unix_timestamp(plgr_overtime)) < 24 * 60 * 60 order by idx desc limit 10; elseif direction = 1 then -- 上一页 select idx, plgr_roomcode as roomcode, plgr_roomtype as roomtype, DATE_FORMAT(plgr_createtime, "%m.%d %H:%i") as createtime, DATE_FORMAT(plgr_makewartime,"%m.%d %H:%i") as makewartime, DATE_FORMAT(plgr_overtime,"%m.%d %H:%i") as overtime, plgr_ownercard as roomcard, plgr_gameinfo1 as gameinfo1 from player_grade where plgr_agentid = agentid and plgr_ownerid = playerid and plgr_gameid = gameid and plgr_ownercard is not null and (unix_timestamp(now()) - unix_timestamp(plgr_overtime)) < 24 * 60 * 60 and idx > gradeidx order by idx desc limit 10; elseif direction = 2 then -- 下一页 select idx, plgr_roomcode as roomcode, plgr_roomtype as roomtype, DATE_FORMAT(plgr_createtime, "%m.%d %H:%i") as createtime, DATE_FORMAT(plgr_makewartime,"%m.%d %H:%i") as makewartime, DATE_FORMAT(plgr_overtime,"%m.%d %H:%i") as overtime, plgr_ownercard as roomcard, plgr_gameinfo1 as gameinfo1 from player_grade where plgr_agentid = agentid and plgr_ownerid = playerid and plgr_gameid = gameid and plgr_ownercard is not null and (unix_timestamp(now()) - unix_timestamp(plgr_overtime)) < 24 * 60 * 60 and idx < gradeidx order by idx desc limit 10; end if; end ;; delimiter ; -- ---------------------------- -- Procedure structure for cp_game_grade_get2 -- ---------------------------- DROP PROCEDURE IF EXISTS `cp_game_grade_get2`; delimiter ;; CREATE PROCEDURE `cp_game_grade_get2`(IN `agentid` varchar(32) charset utf8mb4 collate utf8mb4_unicode_ci ,IN `playerid` int(8) ,IN `gameid` varchar(32) charset utf8mb4 collate utf8mb4_unicode_ci ,IN `gradeidx` int(11)) SQL SECURITY INVOKER COMMENT '获取战绩gameinfo1' begin select plgr_roomtype as roomtype, plgr_gameinfo2 as gameinfo2 from player_grade where plgr_agentid = agentid and plgr_playerid = playerid and plgr_gameid = gameid and idx = gradeidx; end ;; delimiter ; -- ---------------------------- -- Procedure structure for cp_game_grade_save -- ---------------------------- DROP PROCEDURE IF EXISTS `cp_game_grade_save`; delimiter ;; CREATE PROCEDURE `cp_game_grade_save`(IN `agentid` varchar(32) charset utf8 collate utf8_general_ci ,IN `playerids` varchar(800) charset utf8 collate utf8_general_ci ,IN `gameid` varchar(32) charset utf8 collate utf8_general_ci ,IN `roomcode` int(8) ,IN `roomtype` varchar(200) charset utf8 collate utf8_general_ci ,IN `createtime` datetime ,IN `makewartime` datetime ,IN `overtime` datetime ,IN `gameinfo1` varchar(10000) charset utf8 collate utf8_general_ci ,IN `gameinfo2` mediumtext charset utf8 collate utf8_general_ci ,IN `ownerid` int(8) ,IN `grades` varchar(800) charset utf8 collate utf8_general_ci ,IN `roommode` tinyint(1) ,IN `beanmult` int(4) ,IN `shortcode` int(8) ,IN `isrobots` varchar(800) charset utf8 collate utf8_general_ci) SQL SECURITY INVOKER COMMENT '保存战绩' begin declare ary_playerid varchar(800); declare ary_grade varchar(800); declare ary_isrobot varchar(800); declare pos_playerid int(10); declare pos_grade int(10); declare pos_isrobot int(10); declare playerid varchar(8); declare grade varchar(8); declare isrobot varchar(8); -- 如果出现异常，自动退出并rollback declare exit handler for sqlexception ROLLBACK; -- 启动事务 start transaction; set ary_playerid = playerids; set ary_grade = grades; set ary_isrobot = isrobots; while ary_playerid <> '' do set pos_playerid = instr(ary_playerid, ','); set pos_grade = instr(ary_grade, ','); set pos_isrobot = instr(ary_isrobot, ','); if pos_playerid = 0 then set playerid = ary_playerid; set grade = ary_grade; set isrobot = ary_isrobot; set ary_playerid = ''; set ary_grade = ''; set ary_isrobot = ''; else set playerid = left(ary_playerid, pos_playerid - 1); set grade = left(ary_grade, pos_grade - 1); set isrobot = left(ary_isrobot, pos_isrobot - 1); set ary_playerid = right(ary_playerid, length(ary_playerid) - pos_playerid); set ary_grade = right(ary_grade, length(ary_grade) - pos_grade); set ary_isrobot = right(ary_isrobot, length(ary_isrobot) - pos_isrobot); end if; -- 累计游戏局数（大局） if roommode = 0 then -- 非星星场 if isrobot = '0' then update agent_game_player set agpl_playset = agpl_playset + 1, agpl_winlose = agpl_winlose + grade where agpl_agentid = agentid and agpl_gameid = gameid and agpl_playerid = playerid limit 1; end if; else -- 星星场 if isrobot = '0' then update agent_game_player set agpl_playset_xx = agpl_playset_xx + 1, agpl_winlose_xx = agpl_winlose_xx + (grade div beanmult), agpl_winlose_x2 = agpl_winlose_x2 + grade where agpl_agentid = agentid and agpl_gameid = gameid and agpl_playerid = playerid limit 1; else call cp_game_grade_robot(agentid, gameid, grade, beanmult); end if; end if; -- 统计房主的邀请新人奖励 if ownerid <> playerid then if exists(select 1 from agent_game_player where agpl_agentid = agentid and agpl_gameid = gameid and agpl_playerid = playerid and agpl_playset = 1) then call cp_game_task_finish(agentid, ownerid, 'ZVlpZweovbeQ68g8xdjcxaXHfbfIvvu7', 1, 0); end if; -- 累加积分（玩一局积分加1） /* update player set play_score = play_score + 1 where play_agentid = agentid and play_playerid = playerid; else -- 累加积分（开房积分加2） update player set play_score = play_score + 3 where play_agentid = agentid and play_playerid = playerid; */ end if; end while; -- 按日期统计玩家一共玩了多少游戏局（大局） call cp_report_game_day(agentid, gameid, now(), 2, 1, null); -- 运行没有异常，提交事务 commit; end ;; delimiter ; -- ---------------------------- -- Procedure structure for cp_game_grade_save2 -- ---------------------------- DROP PROCEDURE IF EXISTS `cp_game_grade_save2`; delimiter ;; CREATE PROCEDURE `cp_game_grade_save2`(IN `agentid` varchar(32) charset utf8 collate utf8_general_ci ,IN `playerids` varchar(800) charset utf8 collate utf8_general_ci ,IN `gameid` varchar(32) charset utf8 collate utf8_general_ci ,IN `roomcode` int(8) ,IN `roomtype` varchar(200) charset utf8 collate utf8_general_ci ,IN `createtime` datetime ,IN `makewartime` datetime ,IN `overtime` datetime ,IN `gameinfo1` varchar(10000) charset utf8 collate utf8_general_ci ,IN `gameinfo2` mediumtext charset utf8 collate utf8_general_ci ,IN `ownerid` int(8) ,IN `grades` varchar(800) charset utf8 collate utf8_general_ci ,IN `roomcards` varchar(100) charset utf8 collate utf8_general_ci ,IN `ownercard` int(8) ,IN `shortcode` int(8) ,IN `deducttype` tinyint(1) ,IN `deductcard` int(8) ,IN `asetcount` int(11) ,IN `roommode` tinyint(1) ,IN `isrobots` varchar(800) charset utf8 collate utf8_general_ci) SQL SECURITY INVOKER COMMENT '保存战绩' begin declare ary_playerid varchar(800); declare ary_roomcard varchar(800); declare ary_isrobot varchar(800); declare pos_playerid int(10); declare pos_roomcard int(10); declare pos_isrobot int(10); declare playerid varchar(8); declare roomcard int(10); declare isrobot int(10); declare pos_ownerid int(10); declare ownerplay tinyint(1); declare ownertype int(11); set ary_playerid = playerids; set ary_roomcard = roomcards; set ary_isrobot = isrobots; set pos_ownerid = instr(ary_playerid, ownerid); if pos_ownerid = 0 then set ownerplay = 0; else set ownerplay = 1; end if; while ary_playerid <> '' do set pos_playerid = instr(ary_playerid, ','); set pos_isrobot = instr(ary_isrobot, ','); if pos_playerid = 0 then set playerid = ary_playerid; set isrobot = ary_isrobot; -- if ownerplay = 0 then set ownertype = ownercard; -- else -- set ownertype = null; -- end if; set ary_playerid = ''; set ary_isrobot = ''; else set playerid = left(ary_playerid, pos_playerid - 1); set isrobot = left(ary_isrobot, pos_isrobot - 1); set ownertype = null; set ary_playerid = right(ary_playerid, length(ary_playerid) - pos_playerid); set ary_isrobot = right(ary_isrobot, length(ary_isrobot) - pos_isrobot); end if; set pos_roomcard = instr(ary_roomcard, ','); if pos_roomcard = 0 then set roomcard = ary_roomcard; set ary_roomcard = ''; else set roomcard = left(ary_roomcard, pos_roomcard - 1); set ary_roomcard = right(ary_roomcard, length(ary_roomcard) - pos_roomcard); end if; if isrobot = '0' then -- 保存当前战绩 insert into player_grade(plgr_agentid, plgr_playerid, plgr_gameid, plgr_roomcode, plgr_ownerid, plgr_roomtype, plgr_createtime, plgr_makewartime, plgr_overtime, plgr_roomcard, plgr_gameinfo1, plgr_gameinfo2, plgr_ownercard, plgr_shortcode, plgr_deducttype, plgr_deductcard, plgr_asetcount, plgr_roommode) values(agentid, playerid, gameid, roomcode, ownerid, roomtype, createtime, makewartime, overtime, roomcard, gameinfo1, gameinfo2, ownertype, shortcode, deducttype, deductcard, asetcount, roommode); end if; end while; end ;; delimiter ; -- ---------------------------- -- Procedure structure for cp_game_grade_save3 -- ---------------------------- DROP PROCEDURE IF EXISTS `cp_game_grade_save3`; delimiter ;; CREATE PROCEDURE `cp_game_grade_save3`(IN `agentid` varchar(32) charset utf8 collate utf8_general_ci ,IN `playerids` varchar(800) charset utf8 collate utf8_general_ci ,IN `gameid` varchar(32) charset utf8 collate utf8_general_ci ,IN `roomcode` int(8) ,IN `roomtype` varchar(200) charset utf8 collate utf8_general_ci ,IN `createtime` datetime ,IN `makewartime` datetime ,IN `overtime` datetime ,IN `gameinfo1` varchar(10000) charset utf8 collate utf8_general_ci ,IN `gameinfo2` mediumtext charset utf8 collate utf8_general_ci ,IN `ownerid` int(8) ,IN `grades` varchar(800) charset utf8 collate utf8_general_ci ,IN `roomcards` varchar(100) charset utf8 collate utf8_general_ci ,IN `ownercard` int(8) ,IN `shortcode` int(8) ,IN `deducttype` tinyint(1) ,IN `deductcard` int(8) ,IN `asetcount` int(11) ,IN `roommode` tinyint(1) ,IN `isrobots` varchar(800) charset utf8 collate utf8_general_ci ,IN `iswinners` varchar(800) charset utf8 collate utf8_general_ci) SQL SECURITY INVOKER COMMENT '保存战绩' begin declare ary_playerid varchar(800); declare ary_roomcard varchar(800); declare ary_isrobot varchar(800); declare ary_iswinner varchar(800); declare pos_playerid int(10); declare pos_roomcard int(10); declare pos_isrobot int(10); declare pos_iswinner int(10); declare playerid varchar(8); declare roomcard int(10); declare isrobot int(10); declare iswinner int(10); declare pos_ownerid int(10); declare ownerplay tinyint(1); declare ownertype int(11); set ary_playerid = playerids; set ary_roomcard = roomcards; set ary_isrobot = isrobots; set ary_iswinner = iswinners; set pos_ownerid = instr(ary_playerid, ownerid); if pos_ownerid = 0 then set ownerplay = 0; else set ownerplay = 1; end if; while ary_playerid <> '' do set pos_playerid = instr(ary_playerid, ','); set pos_isrobot = instr(ary_isrobot, ','); set pos_iswinner = instr(ary_iswinner, ','); if pos_playerid = 0 then set playerid = ary_playerid; set isrobot = ary_isrobot; set iswinner = ary_iswinner; -- if ownerplay = 0 then set ownertype = ownercard; -- else -- set ownertype = null; -- end if; set ary_playerid = ''; set ary_isrobot = ''; set ary_iswinner = ''; else set playerid = left(ary_playerid, pos_playerid - 1); set isrobot = left(ary_isrobot, pos_isrobot - 1); set iswinner = left(ary_iswinner, pos_iswinner - 1); set ownertype = null; set ary_playerid = right(ary_playerid, length(ary_playerid) - pos_playerid); set ary_isrobot = right(ary_isrobot, length(ary_isrobot) - pos_isrobot); set ary_iswinner = right(ary_iswinner, length(ary_iswinner) - pos_iswinner); end if; set pos_roomcard = instr(ary_roomcard, ','); if pos_roomcard = 0 then set roomcard = ary_roomcard; set ary_roomcard = ''; else set roomcard = left(ary_roomcard, pos_roomcard - 1); set ary_roomcard = right(ary_roomcard, length(ary_roomcard) - pos_roomcard); end if; if isrobot = '0' then -- 保存当前战绩 insert into player_grade(plgr_agentid, plgr_playerid, plgr_gameid, plgr_roomcode, plgr_ownerid, plgr_roomtype, plgr_createtime, plgr_makewartime, plgr_overtime, plgr_roomcard, plgr_gameinfo1, plgr_gameinfo2, plgr_ownercard, plgr_shortcode, plgr_deducttype, plgr_deductcard, plgr_asetcount, plgr_roommode,plgr_winner) values(agentid, playerid, gameid, roomcode, ownerid, roomtype, createtime, makewartime, overtime, roomcard, gameinfo1, gameinfo2, ownertype, shortcode, deducttype, deductcard, asetcount, roommode,iswinner); end if; end while; end ;; delimiter ; -- ---------------------------- -- Procedure structure for cp_game_grade_save4 -- ---------------------------- DROP PROCEDURE IF EXISTS `cp_game_grade_save4`; delimiter ;; CREATE PROCEDURE `cp_game_grade_save4`(IN `agentid` varchar(32) charset utf8 collate utf8_general_ci ,IN `playerids` varchar(800) charset utf8 collate utf8_general_ci ,IN `gameid` varchar(32) charset utf8 collate utf8_general_ci ,IN `roomcode` int(8) ,IN `roomtype` varchar(200) charset utf8 collate utf8_general_ci ,IN `createtime` datetime ,IN `makewartime` datetime ,IN `overtime` datetime ,IN `gameinfo1` varchar(10000) charset utf8 collate utf8_general_ci ,IN `gameinfo2` mediumtext charset utf8 collate utf8_general_ci ,IN `ownerid` int(8) ,IN `grades` varchar(800) charset utf8 collate utf8_general_ci ,IN `roomcards` varchar(100) charset utf8 collate utf8_general_ci ,IN `ownercard` int(8) ,IN `shortcode` int(8) ,IN `deducttype` tinyint(1) ,IN `deductcard` int(8) ,IN `asetcount` int(11) ,IN `roommode` tinyint(1) ,IN `isrobots` varchar(800) charset utf8 collate utf8_general_ci ,IN `iswinners` varchar(800) charset utf8 collate utf8_general_ci ,IN `scores` varchar(800) charset utf8 collate utf8_general_ci) SQL SECURITY INVOKER COMMENT '保存战绩' begin declare ary_playerid varchar(800); declare ary_roomcard varchar(800); declare ary_isrobot varchar(800); declare ary_iswinner varchar(800); declare ary_score varchar(800); declare pos_playerid int(10); declare pos_roomcard int(10); declare pos_isrobot int(10); declare pos_iswinner int(10); declare pos_score int(10); declare playerid varchar(8); declare roomcard int(10); declare isrobot int(10); declare iswinner int(10); declare score int(10); declare pos_ownerid int(10); declare ownerplay tinyint(1); declare ownertype int(11); set ary_playerid = playerids; set ary_roomcard = roomcards; set ary_isrobot = isrobots; set ary_iswinner = iswinners; set ary_score = scores; set pos_ownerid = instr(ary_playerid, ownerid); if pos_ownerid = 0 then set ownerplay = 0; else set ownerplay = 1; end if; while ary_playerid <> '' do set pos_playerid = instr(ary_playerid, ','); set pos_isrobot = instr(ary_isrobot, ','); set pos_iswinner = instr(ary_iswinner, ','); set pos_score = instr(ary_score, ','); if pos_playerid = 0 then set playerid = ary_playerid; set isrobot = ary_isrobot; set iswinner = ary_iswinner; set score = ary_score; -- if ownerplay = 0 then set ownertype = ownercard; -- else -- set ownertype = null; -- end if; set ary_playerid = ''; set ary_isrobot = ''; set ary_iswinner = ''; set ary_score = ''; else set playerid = left(ary_playerid, pos_playerid - 1); set isrobot = left(ary_isrobot, pos_isrobot - 1); set iswinner = left(ary_iswinner, pos_iswinner - 1); set score = left(ary_score, pos_score - 1); set ownertype = null; set ary_playerid = right(ary_playerid, length(ary_playerid) - pos_playerid); set ary_isrobot = right(ary_isrobot, length(ary_isrobot) - pos_isrobot); set ary_iswinner = right(ary_iswinner, length(ary_iswinner) - pos_iswinner); set ary_score = right(ary_score, length(ary_score) - pos_score); end if; set pos_roomcard = instr(ary_roomcard, ','); if pos_roomcard = 0 then set roomcard = ary_roomcard; set ary_roomcard = ''; else set roomcard = left(ary_roomcard, pos_roomcard - 1); set ary_roomcard = right(ary_roomcard, length(ary_roomcard) - pos_roomcard); end if; if isrobot = '0' then -- 保存当前战绩 insert into player_grade(plgr_agentid, plgr_playerid, plgr_gameid, plgr_roomcode, plgr_ownerid, plgr_roomtype, plgr_createtime, plgr_makewartime, plgr_overtime, plgr_roomcard, plgr_gameinfo1, plgr_gameinfo2, plgr_ownercard, plgr_shortcode, plgr_deducttype, plgr_deductcard, plgr_asetcount, plgr_roommode,plgr_winner,plgr_score) values(agentid, playerid, gameid, roomcode, ownerid, roomtype, createtime, makewartime, overtime, roomcard, gameinfo1, gameinfo2, ownertype, shortcode, deducttype, deductcard, asetcount, roommode,iswinner,score); end if; end while; end ;; delimiter ; -- ---------------------------- -- Procedure structure for cp_game_save_matchranking -- ---------------------------- DROP PROCEDURE IF EXISTS `cp_game_save_matchranking`; delimiter ;; CREATE PROCEDURE `cp_game_save_matchranking`(IN `agentid` varchar(32) charset utf8 collate utf8_general_ci ,IN `gameid` varchar(32) charset utf8 collate utf8_general_ci ,IN `matchid` varchar(10) charset utf8 collate utf8_general_ci ,IN `matchidx` tinyint(2) ,IN `matchname` varchar(100) charset utf8 collate utf8_general_ci ,IN `beginTime` varchar(20) charset utf8 collate utf8_general_ci ,IN `endTime` varchar(20) charset utf8 collate utf8_general_ci ,IN `roomtype` varchar(200) charset utf8 collate utf8_general_ci ,IN `imageurl` varchar(200) charset utf8 collate utf8_general_ci ,IN `playercount` int(8) ,IN `playercount2` int(8) ,IN `bean` int(8) ,IN `roomcard` int(8) ,IN `topcount` int(8) ,IN `circulation` int(8) ,IN `iinterval` int(8) ,IN `gradename` varchar(20) charset utf8 collate utf8_general_ci ,IN `memo1` varchar(800) charset utf8 collate utf8_general_ci ,IN `memo2` varchar(800) charset utf8 collate utf8_general_ci ,IN `memo3` varchar(800) charset utf8 collate utf8_general_ci ,IN `ranking` mediumtext charset utf8 collate utf8_general_ci ,IN `playeridlist` varchar(1000) charset utf8 collate utf8_general_ci ,IN `nicknamelist` varchar(4000) charset utf8 collate utf8_general_ci ,IN `avatarlist` varchar(20000) charset utf8 collate utf8_general_ci ,IN `roomcodelist` varchar(1000) charset utf8 collate utf8_general_ci ,IN `overtimelist` varchar(4000) charset utf8 collate utf8_general_ci ,IN `gradelist` varchar(4000) charset utf8 collate utf8_general_ci ,IN `isrobotlist` varchar(400) charset utf8 collate utf8_general_ci) SQL SECURITY INVOKER COMMENT '保存比赛排行榜' begin declare ary_playerid varchar(1000); declare ary_nickname varchar(4000); declare ary_avatar varchar(20000); declare ary_roomcode varchar(1000); declare ary_overtime varchar(4000); declare ary_grade varchar(4000); declare ary_isrobot varchar(400); declare pos_playerid int(8); declare pos_nickname int(8); declare pos_avatar int(8); declare pos_roomcode int(8); declare pos_overtime int(8); declare pos_grade int(8); declare pos_isrobot int(8); declare s_playerid varchar(8); declare s_nickname varchar(100); declare s_avatar varchar(200); declare s_roomcode varchar(8); declare s_overtime varchar(30); declare s_grade varchar(10); declare s_isrobot varchar(1); delete from `match` where matc_agentid = agentid and matc_gameid = gameid and matc_matchid = matchid and matc_matchidx = matchidx; insert into `match`(matc_agentid, matc_gameid, matc_matchid, matc_matchidx, matc_matchname, matc_beginTime, matc_endTime, matc_roomtype, matc_imageurl, matc_playercount, matc_playercount2, matc_bean, matc_roomcard, matc_topcount, matc_circulation, matc_interval, matc_gradename, matc_memo1, matc_memo2, matc_memo3, matc_ranking) values(agentid, gameid, matchid, matchidx, matchname, beginTime, endTime, roomtype, imageurl, playercount, playercount2, bean, roomcard, topcount, circulation, iinterval, gradename, memo1, memo2, memo3, ranking); delete from match_ranking where mara_agentid = agentid and mara_gameid = gameid and mara_matchid = matchid and mara_matchidx = matchidx; set ary_playerid = playeridlist; set ary_nickname = nicknamelist; set ary_avatar = avatarlist; set ary_roomcode = roomcodelist; set ary_overtime = overtimelist; set ary_grade = gradelist; set ary_isrobot = isrobotlist; while ary_playerid <> '' do set pos_playerid = instr(ary_playerid, ','); set pos_nickname = instr(ary_nickname, ','); set pos_avatar = instr(ary_avatar, ','); set pos_roomcode = instr(ary_roomcode, ','); set pos_overtime = instr(ary_overtime, ','); set pos_grade = instr(ary_grade, ','); set pos_isrobot = instr(ary_isrobot, ','); if pos_playerid = 0 then set s_playerid = ary_playerid; set s_nickname = ary_nickname; set s_avatar = ary_avatar; set s_roomcode = ary_roomcode; set s_overtime = ary_overtime; set s_grade = ary_grade; set s_isrobot = ary_isrobot; set ary_playerid = ''; set ary_nickname = ''; set ary_avatar = ''; set ary_roomcode = ''; set ary_overtime = ''; set ary_grade = ''; set ary_isrobot = ''; else set s_playerid = left(ary_playerid, pos_playerid - 1); set s_nickname = left(ary_nickname, pos_nickname - 1); set s_avatar = left(ary_avatar, pos_avatar - 1); set s_roomcode = left(ary_roomcode, pos_roomcode - 1); set s_overtime = left(ary_overtime, pos_overtime - 1); set s_grade = left(ary_grade, pos_grade - 1); set s_isrobot = left(ary_isrobot, pos_isrobot - 1); set ary_playerid = substring(ary_playerid, 1 + pos_playerid); set ary_nickname = substring(ary_nickname, 1 + pos_nickname); set ary_avatar = substring(ary_avatar, 1 + pos_avatar); set ary_roomcode = substring(ary_roomcode, 1 + pos_roomcode); set ary_overtime = substring(ary_overtime, 1 + pos_overtime); set ary_grade = substring(ary_grade, 1 + pos_grade); set ary_isrobot = substring(ary_isrobot, 1 + pos_isrobot); end if; insert into match_ranking(mara_agentid, mara_gameid, mara_matchid, mara_matchidx, mara_playerid, mara_nickname, mara_avatar, mara_roomcode, mara_overtime, mara_grade, mara_isrobot) values (agentid, gameid, matchid, matchidx, s_playerid, s_nickname, s_avatar, s_roomcode, s_overtime, s_grade, s_isrobot); end while; end ;; delimiter ; SET FOREIGN_KEY_CHECKS = 1;