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
