def getChatByid(fid, tid): '''获取两人私聊信息 @param fid:int 其中一人id @param tid:int 另一人角色id ''' gdid = 0 if fid > tid: #保证fid比tid小 gdid = fid fid = tid tid = gdid sql = "SELECT * FROM tb_friend_chat_log WHERE fromid=" + str( fid ) + "AND toid=" + str( tid ) + "AND (DATE_ADD(times,INTERVAL 24 HOUR)>=CURRENT_TIMESTAMP) ORDER BY times" conn = dbpool.connection() cursor = conn.cursor(cursorclass=DictCursor) cursor.execute(sql) data = cursor.fetchall() cursor.close() conn.close() if not data: return None return data
def getCharacterArenaInfo(characterId): '''获取角色竞技场信息 @param characterId: int 角色的ID ''' sql = "SELECT * FROM tb_arena where characterId =%d" % characterId conn = dbpool.connection() cursor = conn.cursor(DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() if not result: insertCharacterArenaInfo(characterId) result = { 'characterId': characterId, 'score': 0, 'liansheng': 0, 'lastresult': 0, 'lasttime': datetime.datetime(2012, 6, 20, 12), 'ranking': 0, 'surplustimes': 15, 'buytimes': 0, 'receive': 0, 'recorddate': datetime.date.today() } return result
def getPlayerAllMailList(characterId): '''获取角色邮件列表 @param characterId: int 角色的id ''' filedList = ['id', 'sender', 'title', 'type', 'isReaded', 'sendTime'] sqlstr = '' sqlstr = forEachQueryProps(sqlstr, filedList) sql = "select %s from `tb_mail` where receiverId = %d and isSaved = 0\ order by isReaded ,sendTime desc" % (sqlstr, characterId) conn = dbpool.connection() cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() data = [] for mail in result: mailInfo = {} for i in range(len(mail)): if filedList[i] == 'sendTime': mailInfo['sendTime'] = str(mail[i]) else: mailInfo[filedList[i]] = mail[i] data.append(mailInfo) return data
def creatNewCharacter(nickname ,profession ,userId,sex=1): '''创建新的角色 @param nickname: str 角色的昵称 @param profession: int 角色的职业编号 @param userId: int 用户的id @param fieldname: str 用户角色关系表中的字段名,表示用户的第几个角色 ''' nowdatetime = str(datetime.datetime.today()) sql = "insert into `tb_character`(nickName,profession,sex,createtime) \ values('%s',%d,%d,'%s')"%(nickname ,profession,sex,nowdatetime) sql2 = "SELECT @@IDENTITY" conn = dbpool.connection() cursor = conn.cursor() count = cursor.execute(sql) conn.commit() cursor.execute(sql2) result = cursor.fetchone() cursor.close() conn.close() if result and count: characterId = result[0] InsertUserCharacter(userId,characterId) return characterId else: return 0
def getStatistics(): """获取单服总数据 """ sql1 = "SELECT COUNT(id) FROM tb_register;" #总注册数 sql2 = "SELECT COUNT(id) FROM tb_character;" #总创建数 sql3 = "SELECT COUNT(DISTINCT uid) FROM tb_recharge WHERE boo=1;" #付费人数 sql4 = "SELECT SUM(rbm) FROM tb_recharge WHERE boo=1;" #总付费人数 conn = dbpool.connection() cursor = conn.cursor() cursor.execute(sql1) result1 = cursor.fetchone()[0] cursor.execute(sql2) result2 = cursor.fetchone()[0] cursor.execute(sql3) result3 = cursor.fetchone()[0] cursor.execute(sql4) result4 = cursor.fetchone()[0] cursor.close() conn.close() return { 'reg_cnt': 0 if not result1 else result1, 'role_cnt': 0 if not result2 else result2, 'fu_cnt': 0 if not result3 else result3, 'income': 0.0 if not result4 else float(result4) }
def getDayRecordList(index, limit=10): '''获取每日的纪录''' sql = "SELECT * FROM tb_statistics ORDER BY recorddate DESC LIMIT %s,%s;" % ( (index - 1) * limit, index * limit) conn = dbpool.connection() cursor = conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() recordlist = [] for daterecord in result: recorddate = daterecord['recorddate'] IncomeInfo = getIncomByData(recorddate) info = daterecord info['f_arpu'] = 0 if not IncomeInfo[ 'cnt'] else IncomeInfo['goal'] / IncomeInfo['cnt'] info['z_arpu'] = 0 if not info[ 'createrole'] else IncomeInfo['goal'] / info['createrole'] info['pay_rate'] = 0 if not info[ 'loginuser'] else IncomeInfo['cnt'] * 100 / info['loginuser'] info['r_rate'] = 0 if not info[ 'createrole'] else info['loginuser'] * 100 / info['createrole'] info['recorddate'] = str(info['recorddate']) info['pay_cnt'] = IncomeInfo['cnt'] info['pay_goal'] = IncomeInfo['goal'] info.update(getDayConsume(recorddate)) recordlist.append(info) return recordlist
def getAllSkill(): '''初始化技能信息 #职业技能组 #技能池 #技能组 ''' global ALL_SKILL_INFO,SKILL_GROUP,PROFESSION_SKILLGROUP sql = "SELECT * FROM tb_skill_info" conn = dbpool.connection() cursor = conn.cursor(DictCursor) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() for skill in result: effectInfo = getSkillEffectByID(skill['effect']) skill['effect'] = effectInfo ALL_SKILL_INFO[skill['skillId']] = skill if not SKILL_GROUP.has_key(skill['skillGroup']): SKILL_GROUP[skill['skillGroup']] = {} SKILL_GROUP[skill['skillGroup']][skill['level']] = skill #初始化职业技能组ID for groupID in SKILL_GROUP: skillInfo = SKILL_GROUP[groupID].get(1) profession = skillInfo.get('profession',0) if not PROFESSION_SKILLGROUP.has_key(profession): PROFESSION_SKILLGROUP[profession] = [] PROFESSION_SKILLGROUP[profession].append(groupID)
def getShopInfo(shopcategory): sql="select * from tb_shop where shopcategory=%d and begintime<now() and endtime>now()"%(shopcategory) conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() return result
def getTrainbaseInfo(characterId): '''获取玩家训练基地信息''' sql="SELECT * FROM tb_character_trainbase where characterId=%s"%characterId conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() return result
def getAllProcessInfo(characterId): '''获取所有的在进行中的任务列表''' sql = "SELECT * FROM tb_task_process WHERE characterId=%d" % characterId conn = dbpool.connection() cursor = conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() return result
def getGuyongRecord(characterId): sql = "SELECT * FROM tb_guyong_record WHERE \ characterId = %d ORDER BY reocrddate DESC LIMIT 0,10;"%(characterId) conn = dbpool.connection() cursor = conn.cursor(DictCursor) cursor.execute(sql) result= cursor.fetchall() cursor.close() conn.close() return result
def CheckUserInfo(Uid): '''''' sql="Select * from tb_register where username='******'"%Uid conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() return result
def getMailInfo(mailId): '''获取邮件详细信息''' sql = "select * from `tb_mail` where id = %d" % (mailId) conn = dbpool.connection() cursor = conn.cursor(DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() return result
def getPlayerFriMailCnd(characterId): '''获取角色玩家邮件的数量''' sql = "SELECT COUNT(id) FROM tb_mail WHERE receiverId = %d AND `type`=1 and isSaved = 0" % characterId conn = dbpool.connection() cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() return result[0]
def getShopInfo(shopcategory): sql = "select * from tb_shop where shopcategory=%d and begintime<now() and endtime>now()" % ( shopcategory) conn = dbpool.connection() cursor = conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() return result
def getAll_ShieldWord(): global SHIELDWORD sql = "SELECT sword FROM tb_shieldword;" conn = dbpool.connection() cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() SHIELDWORD = result
def getAll_ShieldWord(): global SHIELDWORD sql="SELECT sword FROM tb_shieldword;" conn=dbpool.connection() cursor=conn.cursor() cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() SHIELDWORD=result
def getGamerAllMailCnd(characterId): '''获取玩家所有邮件的数量''' sql = "SELECT COUNT(`id`) FROM tb_mail WHERE receiverId=%d and isSaved =0" % characterId conn = dbpool.connection() cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() return result[0]
def getGamerSavMailCnd(characterId): '''获取保存邮件的数量''' sql = "SELECT COUNT(id) FROM tb_mail where receiverId=%d and `isSaved`=1" % characterId conn = dbpool.connection() cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() return result[0]
def getGamerSysMailCnd(characterId): '''获取角色系统邮件数量''' sql = "SELECT COUNT(id) FROM tb_mail WHERE receiverId=%d and `type`=0 and isSaved=0" % characterId conn = dbpool.connection() cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() return result[0]
def getSkillEffectByID(skillEffectID): '''获取技能效果ID''' sql = "SELECT * FROM tb_skill_effect where effectId=%d"%skillEffectID conn = dbpool.connection() cursor = conn.cursor(DictCursor) cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() return result
def getAllProcessInfo(characterId): '''获取所有的在进行中的任务列表''' sql="SELECT * FROM tb_task_process WHERE characterId=%d"%characterId conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() return result
def getBuffEffect(buffEffectID): '''获取buff效果''' sql = "SELECT * FROM tb_buff_effect where buffEffectID = %d"%buffEffectID conn = dbpool.connection() cursor = conn.cursor(DictCursor) cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() return result
def getIncomByData(onedata): '''获取每天的缴费情况''' sql="SELECT SUM(rmb) AS goal,COUNT(DISTINCT uid) AS cnt FROM tb_recharge WHERE DATE(rtime)=DATE('%s') and boo=1;"%onedata conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() result['goal']=float(result.get('goal')) if result.get('goal') else 0 return result
def _init_(self): self.dbConfig = json.load(open('config.json', \ 'r')).get('db') dbpool.initPool(host = self.dbConfig['host'], \ user = self.dbConfig['user'], \ passwd = self.dbConfig['passwd'], \ port = self.dbConfig['port'], \ db = self.dbConfig['db'], \ char = self.dbConfig['charset']) self.conn = dbpool.connection() self.cursor = self.conn.cursor()
def getALLTask(): '''获取所有任务''' sql="SELECT * FROM tb_task where enable=1" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for taskInfo in result: all_TaskTemplate[taskInfo['taskId']]=taskInfo
def getCharacterInfoByUserId(userId): ''' ''' sql="select * from tb_character where userid=%d"%(userId) conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() return result
def getVIPExp(): global VIPEXP sql="select * from tb_vipexp" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for vipp in result: VIPEXP[vipp['viplevel']]=vipp['maxexp']
def getVIPExp(): global VIPEXP sql = "select * from tb_vipexp" conn = dbpool.connection() cursor = conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() for vipp in result: VIPEXP[vipp['viplevel']] = vipp['maxexp']
def deleteMail(mailId): '''删除邮件''' sql = "DELETE FROM tb_mail WHERE id=%d" % mailId conn = dbpool.connection() cursor = conn.cursor() count = cursor.execute(sql) conn.commit() cursor.close() if (count >= 1): return True return False
def getALLTask(): '''获取所有任务''' sql = "SELECT * FROM tb_task where enable=1" conn = dbpool.connection() cursor = conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() for taskInfo in result: all_TaskTemplate[taskInfo['taskId']] = taskInfo
def getIncomByData(onedata): '''获取每天的缴费情况''' sql = "SELECT SUM(rmb) AS goal,COUNT(DISTINCT uid) AS cnt FROM tb_recharge WHERE DATE(rtime)=DATE('%s') and boo=1;" % onedata conn = dbpool.connection() cursor = conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() result['goal'] = float(result.get('goal')) if result.get('goal') else 0 return result
def getExperience_Config(): '''获取经验配置表信息''' global tb_Experience_config sql = "select * from tb_experience" conn = dbpool.connection() cursor = conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchall() cursor.close() conn.close() for _item in result: tb_Experience_config[_item['level']] = _item
def UpdateTaskProcess(characterId,taskId,props): '''更新任务进度''' sql=util.forEachUpdateProps('tb_task_process',props,{'characterId':characterId,'taskId':taskId}) conn=dbpool.connection() cursor=conn.cursor() count=cursor.execute(sql) conn.commit() cursor.close() conn.close() if count: return True return False
def initTrainbaseInfo(characterId): '''初始化玩家训练基地信息''' sql="INSERT INTO `tb_character_trainbase`(characterId) VALUES (%d)"%characterId conn=dbpool.connection() cursor=conn.cursor() count=cursor.execute(sql) conn.commit() cursor.close() conn.close() if count: return True return False
def addMail(title, senderId, sender, receiverId, content, mailtype): '''添加邮件''' sql = "INSERT INTO tb_mail(title,senderId,sender,receiverId,`type`,content,sendTime) VALUES ('%s',%d,'%s',%d,%d,'%s',CURRENT_TIMESTAMP())" % ( title, senderId, sender, receiverId, mailtype, content) conn = dbpool.connection() cursor = conn.cursor() count = cursor.execute(sql) conn.commit() cursor.close() if (count >= 1): return True return False
def getAll_ItemTemplate(): '''获取所有的物品信息''' global all_ItemTemplate sql="select * from `tb_item_template`" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for _item in result: all_ItemTemplate[_item['ID']]=_item
def updateTrainbaseInfo(characterId,props): '''修改玩家训练基地纪录''' sql=util.forEachUpdateProps('tb_character_trainbase',props,{'characterId':characterId}) conn=dbpool.connection() cursor=conn.cursor() count=cursor.execute(sql) conn.commit() cursor.close() conn.close() if count: return True return False
def getAllsetInfo(): '''获取所有的套装信息''' global ALL_SETINFO sql="SELECT * from tb_equipmentset;" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for setinfo in result: ALL_SETINFO[setinfo['id']]=setinfo
def getInfoByid(characterid): '''根据角色id获取角色信息''' sql="SELECT id,nickname,`level` FROM tb_character WHERE id="+str(characterid) conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) data=cursor.fetchone() cursor.close() conn.close() if data: return data return None
def delAllChat(): '''删除所有过时信息''' sql="DELETE FROM tb_friend_chat_log WHERE DATE_ADD(times,INTERVAL 24 HOUR)< CURRENT_TIMESTAMP" conn=dbpool.connection() cursor=conn.cursor() cursor.execute(sql) conn.commit() cursor.close() conn.close() if count: return True return False
def getCharacterIdByNickName(nickname): '''根据昵称获取角色的id @param nickname:string 角色的昵称 ''' sql="SELECT id from `tb_character` where nickname='%s'"%nickname conn=dbpool.connection() cursor=conn.cursor() cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() return result
def InsertTaskProcess(characterId,taskId): '''添加任务进度信息''' sql="INSERT INTO tb_task_process(taskId,characterId) VALUES(%d,%d)"%(taskId,characterId) conn=dbpool.connection() cursor=conn.cursor() count=cursor.execute(sql) conn.commit() cursor.close() conn.close() if count: return True return False
def updateMailInfo(mailId, prop): '''更新邮件信息''' sql = util.forEachUpdateProps('tb_mail', prop, {'id': mailId}) conn = dbpool.connection() cursor = conn.cursor() count = cursor.execute(sql) conn.commit() cursor.close() conn.close() if (count >= 1): return True return False
def getInitPlayer(): '''获取初始球员的模板信息''' global PLAYER_TEMPLATE sql="SELECT * FROM tb_player_template WHERE `tClubID`=1001" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for player in result: PLAYER_TEMPLATE_BEGIN[player['id']]=player
def getAllPlayerTemplate(): '''获取球员的模板信息''' global PLAYER_TEMPLATE sql="SELECT * FROM tb_player_template" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for player in result: PLAYER_TEMPLATE[player['id']]=player
def getUserInfo(uid): ''' @param id:int ''' sql="select * from tb_user_character where id =%d"%(uid) conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() return result
def getExperience_Config(): '''获取经验配置表信息''' global tb_Experience_config sql="select * from tb_experience" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for _item in result: tb_Experience_config[_item['level']]=_item
def getAllInfo(): '''获取所有角色信息''' sql="SELECT * FROM tb_character" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) data=cursor.fetchall() cursor.close() conn.close() if data: return data return None
def getByid(characterId): '''根据角色id获取寻找球员信息''' sql="SELECT * FROM tb_player_inner where characterId=%s"%characterId conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() if result: return result return None
def getPlayerExp(): '''获取球员的经验表''' global PLAYER_EXP sql="select * from tb_player_experience" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for exp in result: PLAYER_EXP[exp['quality']]={1:exp['lv1'],2:exp['lv2'],3:exp['lv3'],4:exp['lv4'],5:exp['lv5'],6:exp['lv6'],7:exp['lv7'],8:exp['lv8'],9:exp['lv9']}
def getZenconfigRole(zenid,playerpos): '''根据战术ID和球员位置确定球员角色''' sql="select role from tb_zen_config where zenid=%d and playerpos='%s'"%(zenid,playerpos) conn=dbpool.connection() cursor=conn.cursor() cursor.execute(sql) result=cursor.fetchone() cursor.close() conn.close() role=0 if result: role=result[0] return role
def getChallengeNpc(): '''获取挑战赛NPC球队信息 ''' global CHALLENGENPC sql="SELECT * FROM tb_npcclub where clubcategory=1 order by leagueindex,powerindex" conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() for npcclub in result: CHALLENGENPC[npcclub['ID']]=npcclub
def creatUserInfo(username,password): ''' ''' sql="insert into tb_register(username,`password`) values ('%s','%s')"%(username,password) conn=dbpool.connection() cursor=conn.cursor() count=cursor.execute(sql) conn.commit() cursor.close() conn.close() if(count>=1): return True return False
def getDayConsume(onedata): '''获取某天的消费情况''' sql="SELECT * FROM tb_bill WHERE DATE(recordDate)=DATE('%s');"%onedata conn=dbpool.connection() cursor=conn.cursor(cursorclass=DictCursor) cursor.execute(sql) result=cursor.fetchall() cursor.close() conn.close() info={} info['cons_goal']=sum([record['spendGold'] for record in result]) info['user_cnt']=len(set([record['characterId'] for record in result])) return info
def InsertUserCharacter(userId,characterId): '''''' sql="update tb_character set userid=%d where `id`=%d"%(userId,characterId) conn=dbpool.connection() cursor=conn.cursor() count=cursor.execute(sql) conn.commit() cursor.close() conn.close() if count >-1: return True else: return False