def checkup(self):
db_live = False
mc_live = False
with catch_error():
db_client1 = yield self.get_db_client(True)
db_client2 = yield self.get_db_client(False)
records1 = (yield db_client1.query(r'show processlist')).fetchall()
records2 = (yield db_client2.query(r'show processlist')).fetchall()
db_live = (len(records1) > 0) or (len(records2) > 0)
with catch_error():
cache = self.get_cache_client()
ckey = r'system_checkup'
cval = self.timestamp()
yield cache.set(ckey, cval)
mc_live = (cval == (yield cache.get(ckey)))
return db_live and mc_live
def search(self):
result = None
with catch_error():
cache = self.get_cache_client()
ckey = cache.key(r'account_infos')
cval = yield cache.get(ckey)
if (cval is None):
db_client = yield self.get_db_client(True)
records = yield db_client.select(r'account', limit=100)
if (records):
cval = [dict(val) for val in records]
yield cache.set(ckey, cval)
result = cval
return result
def get_role_purview(self):
with catch_error():
cache = self.get_cache_client()
ckey = cache.key(r'role_purview')
cval = yield cache.get(ckey)
if (cval is not None):
return cval
db_client = yield self.get_db_client(True)
records = yield db_client.select(r'rbac_role')
if (records is None):
return None
role_infos = {}
for row in records:
role_infos[row[r'id']] = {}
records = yield db_client.select(r'rbac_purview')
if (records):
for row in records:
if (row[r'role'] in role_infos):
role_infos[row[r'role']][
row[r'module']] = row[r'purview']
yield cache.set(ckey, role_infos)
return role_infos
def get_role_purview(self):
with catch_error():
ckey = self._mc.key(r'role_purview')
cval = yield self.get_cache(ckey)
if(cval is not None):
self.Return(cval)
records = yield self._dbs.select(r'rbac_role')
if(records is None):
self.Return()
role_infos = {}
for row in records:
role_infos[row[r'id']] = {}
records = yield self._dbs.select(r'rbac_purview')
if(records):
for row in records:
if(row[r'role'] in role_infos):
role_infos[row[r'role']][row[r'module']] = row[r'purview']
yield self.set_cache(ckey, role_infos)
self.Return(role_infos)
def get_prize_info(self):
result = [False, ErrorCode.Unknown]
with catch_error():
cache_client = self.get_cache_client() # 先从缓存中获取
prize_list = yield cache_client.get("prize_list")
if prize_list:
result[0] = True
result[1] = prize_list
return result
else:
prize_info = yield self.init_prize_info() # 没有的话初始化一下
if not prize_info[0]:
result[1] = prize_info[1]
self.Break()
prize_list, prize_need_list = yield self.deal_info(
prize_info[1]) # 处理奖品的数据结构
set_prize_list_status = yield cache_client.set(
"prize_list", prize_list, expire=Config.RedisExpires)
set_prize_need_list_status = yield cache_client.set(
"prize_need_list", prize_need_list, expire=Config.RedisExpires)
if not set_prize_list_status or not set_prize_need_list_status:
result[1] = ErrorCode.Redis.set_extra('设置缓存失败')
else:
result[0] = True
result[1] = prize_list
return result
def today_used_info(self, uid):
result = [False, ErrorCode.NotFoundUser]
with catch_error():
mssql_account_conn = self.get_mssql_conn(Config.MssqlRecordDbName)
today_zero = Utils.today_zero()
with mssql_account_conn.cursor() as cur:
cur.execute(r"""select
sum(CostValue) as today_used_count
from RecordPrivateCost
where UserID={0}
and DATEDIFF(s, '1970-01-01 00:00:00', CostDate) > {1}
and CostFrom in (1,2)""".format(
uid, today_zero))
_infos = cur.fetchall()
mssql_account_conn.close()
if not _infos[0]['today_used_count']:
return result
if _infos[0]['today_used_count'] > 0:
result[0] = True
result[1] = _infos
if _infos[0]['today_used_count'] < 0:
result[0] = True
result[1] = 0
return result
def get_access_cache(self, key):
"""获取redis"""
with catch_error():
cache_client = self.get_cache_client()
res = yield cache_client.get(key)
if not res:
return None
return res
def get_sign_url(self, filename):
result = None
with catch_error():
result = self._bucket.sign_url(r'GET', filename, 3600)
return result
def test(self):
with catch_error():
db_client = self.get_db_transaction()
print(db_client)
db_client = self.get_db_client()
print(db_client)
res = yield db_client.insert('game_score_info',
user_id=127,
insure_score=19)
def read_log(self, uid):
result = None
with catch_error():
db_client = self.get_db_client()
result = yield db_client.select('prize_log left join prize_info on prize_log.prize_id = prize_info.prize_id',
what="FROM_UNIXTIME(create_time, '%%Y-%%m-%%d %%H:%%i:%%S') as date_info, prize_info.prize_name",
where={'user_id': uid}, order='create_time desc', limit=5)
if not result:
return False
return result
def set_access_cache(self, key, value):
"""添加微信access_token验证相关redis"""
with catch_error():
cache_client = self.get_cache_client()
res = yield cache_client.set(key,
value,
expire=self._expire_access_token)
if not res:
return None
return True
def all_today_prize_count(self, prize_id):
num = None
with catch_error():
db_client = self.get_db_client()
today_zero = Utils.today_zero()
sql_where = 'prize_id = %s and create_time > %d' % (prize_id, today_zero)
res = yield db_client.select(r'prize_log', what='count(1) as all_today_prize_count', where=sql_where)
if res:
num = res[0]['all_today_prize_count']
return num
def write_log(self, uid, prize_id, plus_num):
result = [False, ErrorCode.Unknown]
param = {
'user_id': uid,
'create_time': Utils.timestamp(),
'prize_id': prize_id
}
with catch_error():
db_client = self.get_db_client()
if plus_num: # 插入获奖日志，并且增加房卡
if plus_num > 0: # 需要增加房卡的
'''增加房卡'''
update_score_statu = yield db_client.increase_update(r'game_score_info',
where={'user_id': uid},
fields1={'insure_score': plus_num})
# print(2, update_score_statu) # 0
if not update_score_statu > 0:
result[1] = ErrorCode.Database.set_extra('增加房卡失败！')
self.Break()
# mssql_score_conn = self.get_mssql_conn(Config.MssqlTreasureDbName)
# with mssql_score_conn.cursor() as cur:
# cur.execute(r"""update GameScoreInfo set InsureScore =
# InsureScore + {} where UserID = {}""".format(plus_num, uid))
# rowcount = cur.rowcount
# if not rowcount > 0:
# result[1] = ErrorCode.Database.set_extra(r'添加房卡失败！')
# self.Break()
# mssql_score_conn.commit()
# mssql_score_conn.close()
'''更新获奖日志，需要加京东卡或红包的'''
insert_statu = yield db_client.insert('prize_log', **param)
# print(1, insert_statu) # 0
if insert_statu < 0:
result[1] = ErrorCode.Database.set_extra('插入日志失败！')
self.Break()
'''更新领奖次数'''
update_statu = yield db_client.increase_update('make_prize',
where={'user_id': uid},
fields1={'left_chance': -1},
fields2={'used_chance': 1})
# print(3, update_statu) # 1
if not update_statu > 0:
result[1] = ErrorCode.Database.set_extra('更新领取表失败！')
self.Break()
result[0] = True
return result
def get_left_chance(self, uid):
with catch_error():
db_client = self.get_db_client()
res = yield db_client.select('make_prize',
what='left_chance',
where={'user_id': uid})
if res:
if res[0]['left_chance'] <= 0:
return False
res = res[0]['left_chance']
return res
def __query(self, url, **params):
result = None
with catch_error():
response = yield self.fetch_url(r''.join([self._api_url, url]), params)
if(response):
result = self.json_decode(response)
return result
def __query(self, url, **params):
result = None
with catch_error():
response = yield self.fetch_url(r''.join([self._api_url, url]), params)
if(response):
result = self.json_decode(response)
self.Return(result)
def del_file(self, filename):
result = False
with catch_error():
response = self._bucket.delete_object(filename)
self.debug(response)
result = (response.status == 204)
return result
def checkup(self):
db_live = False
mc_live = False
with catch_error():
records1 = (yield self._dbm.query(r'show processlist')).fetchall()
records2 = (yield self._dbs.query(r'show processlist')).fetchall()
db_live = (len(records1) > 0) or (len(records2) > 0)
with catch_error():
ckey = r'system_checkup'
cval = self.timestamp()
yield self.set_cache(ckey, cval)
mc_live = (cval == (yield self.get_cache(ckey)))
return db_live and mc_live
def put_file(self, filename, filedata):
result = False
with catch_error():
response = self._bucket.put_object(filename, filedata)
self.debug(response)
result = (response.status == 200)
return result
def init_prize_info(self):
result = [False, ErrorCode.Unknown]
with catch_error():
db_client = self.get_db_client()
prize_info = yield db_client.select(r'prize_info',
what='*',
where={'prize_status': 1})
if not prize_info:
result[1] = ErrorCode.Search.set_extra("获取奖品信息失败")
self.Break()
result[0] = True
result[1] = prize_info
return result
def login(self, username, password):
with catch_error():
record = yield self._dbs.where(r'account', username=username)
if(not record):
self.Return()
info = dict(record)
if(info[r'password'] == self.md5(password)):
self.Return(info)
else:
self.Return()
def fetch_url(self, url, params=None, method=r'GET', headers=None, body=None):
if(params):
url = url_concat(url, params)
result = None
with catch_error():
client = AsyncHTTPClient()
response = yield client.fetch(HTTPRequest(url, method, headers, body))
result = utf8(response.body)
self.Return(result)
def search_card_info(self, union_id):
"""
从本地的领取记录表中查找是否有该用户信息
:param union_id:
:return:
"""
result = False
with catch_error():
db_client = self.get_db_client()
res = yield db_client.select('draw_card_info',
what='union_id',
where={'union_id': union_id})
print(res)
if not res:
self.Break()
result = True
return result
def __query(self, url, **kwargs):
kwargs[r'key'] = self._api_key
url = url_concat(r''.join([self._api_url, url]), kwargs)
self.debug(url)
result = None
with catch_error():
client = AsyncHTTPClient()
response = yield client.fetch(HTTPRequest(url))
result = json_decode(utf8(response.body))
self.Return(result)
def login(self, username, password):
result = None
with catch_error():
db_client = yield self.get_db_client(True)
record = yield db_client.where(r'account', username=username)
if (not record):
self.Break()
info = dict(record)
if (info[r'password'] == self.md5(password)):
result = info
return result
def search(self):
with catch_error():
ckey = self._mc.key(r'account_infos')
cval = yield self.get_cache(ckey)
if(cval is not None):
self.Return(cval)
records = yield self._dbs.select(r'account', limit=100)
if(records is None):
self.Return()
cval = [dict(val) for val in records]
yield self.set_cache(ckey, cval)
self.Return(cval)
def auth(self, role_id, module, method):
with catch_error():
if(method not in HttpMethod):
self.Return(False)
role_purview = yield self.get_role_purview()
if(not role_purview):
self.Return(False)
if(role_id not in role_purview):
self.Return(False)
if(module not in role_purview[role_id]):
self.Return(False)
result = bool(HttpMethod[method] & role_purview[role_id][module])
self.Return(result)
def auth(self, role_id, module, method):
with catch_error():
if (method not in HttpMethod):
return False
role_purview = yield self.get_role_purview()
if (not role_purview):
return False
if (role_id not in role_purview):
return False
if (module not in role_purview[role_id]):
return False
result = bool(HttpMethod[method] & role_purview[role_id][module])
return result
def get_mssql_user_info_by_user_id(self, user_id):
'''
抽奖活动用，用来获取数据，添加到本地的mysql数据库中作为关联
:param user_id:
:return:
'''
result = [False, ErrorCode.Unknown]
with catch_error():
mssql_account_conn = self.get_mssql_conn(Config.MssqlAccountDbName)
with mssql_account_conn.cursor() as cur:
cur.execute(
r"select top 1 UserID,NickName from AccountsInfo where UserID = {}"
.format(user_id))
_infos = cur.fetchall()
mssql_account_conn.close()
if not _infos:
result[1] = ErrorCode.AccountNotFound.set_extra(r'该用户不存在！')
self.Break()
result[0] = True
result[1] = _infos
return result
def fetch_url(self,
url,
params=None,
method=r'GET',
headers=None,
body=None):
if (params):
url = url_concat(url, params)
result = None
with catch_error():
client = AsyncHTTPClient()
response = yield client.fetch(
HTTPRequest(url, method, headers, body))
result = self.utf8(response.body)
return result
def __query(self, url, **params):
args = [str(val) for val in sorted(params.items(), key=lambda x: x[0])]
cache = self.get_cache_client()
ckey = cache.key(r'baidu_map', url, *args)
cval = yield cache.get(ckey)
if (cval is None):
params[r'ak'] = self._api_key
params[r'output'] = r'json'
with catch_error():
response = yield self.fetch_url(r''.join([self._api_url, url]),
params)
if (response):
cval = self.json_decode(response)
cache.set(ckey, cval)
return cval
def initial_draw_card_info(self, account_info):
"""
插入本地创建的领取记录表,并且对房卡进行增加
:param account_info:
:return:
"""
result = False
with catch_error():
insert_map = {}
insert_map['user_id'] = account_info['UserID']
insert_map['union_id'] = account_info['unionid']
insert_map['create_time'] = Utils.timestamp()
db_client = self.get_db_client()
res_insert_draw = yield db_client.insert('draw_card_info',
**insert_map)
if res_insert_draw < 0:
self.Break()
uid = account_info['UserID']
'''本地数据库试验'''
# res_insert_score = yield db_client.increase_update(r'game_score_info', where={'user_id': uid},
# fields1={'insure_score': 5})
# if not res_insert_score > 0:
# self.Break()
'''mssql数据库正式操作'''
# mssql_score_conn = self.get_mssql_conn(Config.MssqlTreasureDbName)
# with mssql_score_conn.cursor() as cur:
# cur.execute(r"update GameScoreInfo set InsureScore = InsureScore + {} where UserID = {}".format(
# 5,
# uid))
# rowcount = cur.rowcount
# if not rowcount > 0:
# self.Break()
# mssql_score_conn.commit()
# mssql_score_conn.close()
result = True
return result
def get_mssql_user_info_by_union_id(self, union_id):
'''
微信用，从sql Server中查找该用户是否存在
不存在的话让其注册关联，返回None
1、存在的话返回相应的用户信息 Accounts, NickName
2、写到mysql数据库的weixin_account_draw
3、返回NickName
'''
result = [False, ErrorCode.Unknown]
with catch_error():
mssql_account_conn = self.get_mssql_conn(Config.MssqlAccountDbName)
with mssql_account_conn.cursor() as cur:
cur.execute(
r"select top 1 UserID, unionid from AccountsInfo where unionid = '{}'"
.format(union_id, ))
_infos = cur.fetchall()
mssql_account_conn.close()
if not _infos:
result[1] = ErrorCode.AccountNotFound.set_extra(
r'account not found')
self.Break()
result[0] = True
result[1] = _infos[0]
return result
def initial_chance_info(self, uid):
result = [False, ErrorCode.Unknown]
with catch_error():
db_client = self.get_db_client()
info = yield db_client.select(r'make_prize',
what='first_time,left_chance,used_chance',
where={'user_id': uid}) # 查找本地账户是否有该人
total_chance = Config.TotalChance # 如果有的话，拿到该人的最后一次领取时间
# print(1, info) # [{'first_time': 150993117, 'left_chance': 0, 'used_chance': 1}]
if info:
first_time = info[0]['first_time']
if not info: # 如果没有的话，初始化本地该人账户
'''先从sqlserver中查找该人信息'''
m_account_info = AccountInfo() # 从账户中查找该人信息
user_info = yield m_account_info.get_mssql_user_info_by_user_id(uid)
# print(2, user_info) # [True, [{'UserID': 120, 'NickName': '游客7486118'}]]
if user_info[0]: # 如果有该用户的话，写入本地账户
insert_map = {}
insert_map['create_time'] = Utils.timestamp()
insert_map['user_id'] = user_info[1][0]['UserID']
insert_map['nick_name'] = user_info[1][0]['NickName']
insert_account_statu = yield db_client.insert('account_info',
**insert_map)
# print(3, insert_account_statu) # 120
if insert_account_statu < 0: # 如果没有插入成功的话
result[1] = ErrorCode.Database.set_extra(r'初始化时插入用户表中信息失败！')
self.Break()
else: #插入成功的话，再往make_prize中插
param = {
'user_id': uid,
'first_time': Utils.timestamp(),
'left_chance': 1,
'used_chance': 0,
'added_chance': 1,
}
insert_make_prize_statu = yield db_client.insert('make_prize',
**param)
# print(4, insert_make_prize_statu) # 0
if insert_make_prize_statu < 0: # 成功的话返回None
result[1] = ErrorCode.Database.set_extra(r'插入本地奖品次数表失败！')
self.Break()
first_time = param['first_time']
else: # 没有该用户信息的话
result[1] = user_info[1]
self.Break()
if first_time < Utils.today_zero(): # 如果领取时间是昨天，就刷领取信息
param = {
'first_time': Utils.timestamp(),
'left_chance': 1,
'used_chance': 0,
'added_chance': 1,
}
flush_status = yield db_client.update('make_prize',
**param,
where={'user_id': uid})
# print(5, flush_status) # 1
if flush_status < 0: # 成功的话返回1， row_count
result[1] = ErrorCode.Database.set_extra(r'刷新用户信息失败！')
self.Break()
info = yield db_client.select(r'make_prize',
what='left_chance,used_chance,added_chance',
where={'user_id': uid})
# print(6, info) # [{'left_chance': 1, 'used_chance': 0, 'added_chance': 1}]
left_chance = info[0]['left_chance']
added_chance = info[0]['added_chance']
today_used_count = yield CardUsedInfoModel().today_used_info(uid)
# print(7, today_used_count) # [False, -20017 ]
if not today_used_count[0]:
today_used = 0
if today_used_count[0]:
today_used = today_used_count[1][0]['today_used_count']
if today_used <= 0:
today_used = 0
all_time = divmod(today_used, Config.EveryChanceRequire)[0]
all_times = all_time if all_time < total_chance else total_chance
add_time = all_times - added_chance
if add_time < 0:
add_time = 0
add_time_status = yield db_client.increase_update(r'make_prize',
where={'user_id': uid},
fields1={'left_chance': add_time},
fields2={'added_chance': add_time})
# print(8, add_time_status) # 0
if add_time_status < 0: # 受影响的行数 0
result[1] = ErrorCode.Database.set_extra("更新奖品次数失败！")
self.Break()
result[1] = left_chance + add_time
result[0] = True
return result