def get_random_lots(cls):
db = DbManager().db
sql = 'select * from {table} as t1 join(select round(rand() * ((select max(id) from {table}) - ' \
'(select min(id) from {table})) + (select min(id) from {table})) as id) as t2 where ' \
't1.id >= t2.id ORDER BY t1.id LIMIT 1'.format(table=cls.table)
rst = db.get(sql)
return rst and cls(**rst)
def daily_login_integral_enough(cls, user_id):
db = DbManager().db
date = datetime.now().date()
sql = 'select id from {table} where user_id=%s and source=%s and create_date=%s'.format(
table=cls.table)
rst = db.get(sql, user_id, USER_INTEGRAL_DAILY_LOGIN, date)
return bool(rst)
def add_user_integral(user_id, source, integral, redis_key=None, extra=None):
db = DbManager().db
create_time = datetime.now()
date = create_time.date()
try:
db.execute('begin;')
sql = 'update {table} set integral=integral+%s where user_id=%s'.format(table=UserIntegral.table)
db.execute(sql, integral, user_id)
sql = 'select integral from {table} where user_id=%s'.format(table=UserIntegral.table)
amount = db.get(sql, user_id).get('integral')
sql = 'insert into {table} (user_id, source, amount, integral, date, create_time, extra) values ' \
'(%s, %s, %s, %s, %s, %s)'.format(table=UserIntegralRecord.table)
db.execute(sql, user_id, source, amount, integral, date, create_time, extra)
db.execute('commit;')
if redis_key:
cache.set(redis_key, 1)
except:
db.execute('rollback;')
raise
level = handle_integral_level_point(integral)
if level:
if level == 3:
text = u'恭喜您有%s积分，升级至LV3啦，赶紧去创建房间和聊天组吧！' % integral
Notify.add(user_id, user_id, NOTIFY_ACTION_LEVEL_UP_THREE, None, extra_info=text)
else:
text = u'恭喜您有%s积分，升级至LV%s啦，查看积分攻略更快升级！' % (integral, level)
Notify.add(user_id, user_id, NOTIFY_ACTION_LEVEL_UP, None, extra_info=text)
def get_user_count(cls):
db = DbManager().db
sql = 'select count(id) from {table}'.format(table=cls.table)
count = db.get(sql)
if count and count.get('count(id)'):
return count.get('count(id)')
else:
return 0
def get_user_receive_reward_amount(cls, user_id):
db = DbManager().db
sql = 'select sum(amount) from {table} where status=%s and receiver_id=%s'.format(table=cls.table)
rst = db.get(sql, STATUS_COMPLETE, user_id)
if rst and rst.get('sum(amount)'):
return float(rst.get('sum(amount)'))
else:
return 0
def follow_each_other(cls, user_id, follow_id):
db = DbManager().db
sql = 'select follow_type from {table} where user_id=%s and follow_id=%s'
rst = db.get(sql, user_id, follow_id)
if rst:
return rst.get('follow_type') == FOLLOW_BOTH_TYPE
return False
def is_room_admin(cls, room_id, user_id):
db = DbManager().db
sql = 'select status from {table} where room_id=%s and user_id=%s'.format(table=cls.table)
rst = db.get(sql, room_id, user_id)
try:
status = rst.get('status')
return int(status) == ROOM_USER_ADMIN
except:
return False
def is_flower_identity(cls, user_id):
db = DbManager().db
sql = 'select * from {table} where user_id=%s'.format(table=cls.table)
rst = db.get(sql, user_id)
if rst:
status = cls(**rst).status
return int(status) == CHAT_FLOWER_IDENTITY
return False
def get_random_list(cls, count=6):
db = DbManager().db
sql = 'select * from {table} as t1 join(select round(rand() * ((select max(id) from {table}) - ' \
'(select min(id) from {table})) + (select min(id) from {table})) as id) as t2 where ' \
't1.id >= t2.id and status="{status}" ORDER BY t1.id LIMIT 1'.format(table=cls.table, status=CHECK_STATUS_PASS)
rst = db.get(sql)
if rst:
rst = set()
while len(rst) < count:
id = int(db.get(sql).get('id'))
rst.add(id)
tlist = []
for r in rst:
tag = TagProverbs.get(r)
tlist.append(tag)
return tlist
return []
def get_push_count_by_room_and_date(cls, room_id, date):
db = DbManager().db
date = date.date()
start = date.strftime('%Y-%m-%d')
end = (date + timedelta(days=1)).strftime('%Y-%m-%d')
sql = 'select count(id) from {table} where room_id=%s and %s < push_time and push_time < %s and status!=%s'.format(
table=cls.table)
rst = db.get(sql, room_id, start, end, ROOM_PUSH_FAIL)
return rst and rst.get('count(id)')
def get_user_amount_by_room(cls, room_id):
cache = CacheManager().cache
cache_key = ROOM_USER_CACHE_KEY % room_id
if cache.exists(cache_key):
return cache.zcard(cache_key)
else:
db = DbManager().db
sql = 'select count(id) from {table} where room_id=%s'.format(table=cls.table)
return db.get(sql, room_id).get('count(id)')
def get_fund_amount_by_room_and_month(cls, room_id, date):
db = DbManager().db
sql = 'select sum(amount) from {table} where status=%s and room_id=%s and pay_time < %s'.format(
table=cls.table)
rst = db.get(sql, STATUS_COMPLETE, room_id, date)
if rst and rst.get('sum(amount)'):
return float(rst.get('sum(amount)'))
else:
return 0
def get_seeds_amount_by_user(cls, user_id):
db = DbManager().db
sql = 'select sum(amount) from {table} where receiver_id=%s'.format(
table=cls.table)
rst = db.get(sql, user_id)
amount = rst.get('sum(amount)')
if not amount:
return 0
else:
return int(amount)
def unfollow(cls, user_id, follow_id):
cache = CacheManager().cache
db = DbManager().db
user_follow_key = USER_FOLLOW_KEY % user_id
user_fans_key = USER_FANS_KEY % follow_id
cache.zrem(user_follow_key, follow_id)
cache.zrem(user_fans_key, user_id)
db.execute('begin;')
try:
sql = 'select follow_type from {table} where user_id=%s and follow_id=%s'.format(table=cls.table)
follow_type = int(db.get(sql, user_id, follow_id).get('follow_type'))
sql = 'delete from {table} where user_id=%s and follow_id=%s'.format(table=cls.table)
db.execute(sql, user_id, follow_id)
sql = 'delete from {table} where user_id=%s and fans_id=%s'.format(table=UserFans.table)
db.execute(sql, follow_id, user_id)
if follow_type == FOLLOW_BOTH_TYPE:
sql = 'update {table} set type=%s where user_id=%s and follow_id=%s'.format(table=cls.table)
db.execute(sql, FOLLOW_SINGLE_TYPE, follow_id, user_id)
db.execute('commit;')
except:
db.execute('rollback;')
raise
def get_by_user(cls, user_id):
db = DbManager().db
sql = 'select * from {table} where user_id=%s'.format(table=cls.table)
device_info = db.get(sql, user_id)
return device_info and cls(**device_info)
def get(cls, id):
db = DbManager().db
sql = 'select * from {table} where id=%s'.format(table=cls.table)
room_order_info = db.get(sql, id)
return room_order_info and cls(**room_order_info)
def get_by_room_and_user(cls, room_id, user_id):
db = DbManager().db
sql = 'select * from {table} where room_id=%s and user_id=%s'.format(
table=cls.table)
room_order_info = db.get(sql, room_id, user_id)
return room_order_info and cls(**room_order_info)
def get_user_unread_notify_count_by_type(cls, user_id, notify_type):
db = DbManager().db
sql = 'select count(id) from {table} where receiver_id=%s and notify_type=%s and status=%s'.format(
table=cls.table)
rst = db.get(sql, user_id, notify_type, NOTIFY_UNREAD)
return rst and rst.get('count(id)')
def get_question_by_room(cls, room_id):
db = DbManager().db
sql = 'select * from {table} where room_id=%s'.format(table=cls.table)
question_info = db.get(sql, room_id)
return question_info and cls(**question_info)
def get_amount(cls):
db = DbManager().db
sql = 'select count(id) from {table}'.format(table=cls.table)
count = db.get(sql)
return count and count.get('count(id)')
def get_user_latest_notify_by_type(cls, user_id, notify_type):
db = DbManager().db
sql = 'select * from {table} where receiver_id=%s and notify_type=%s order by create_time desc limit 1'.format(
table=cls.table)
rst = db.get(sql, user_id, notify_type)
return rst and cls(**rst)
def get_by_user_room(cls, room_id, user_id):
db = DbManager().db
sql = 'select * from {table} where room_id=%s and user_id=%s'.format(
table=cls.table)
rst = db.get(sql, room_id, user_id)
return rst and cls(**rst)
def get_by_option_and_user(cls, option_id, user_id):
db = DbManager().db
sql = 'select * from {table} where option_id=%s and user_id=%s'.format(
table=cls.table)
order_info = db.get(sql, option_id, user_id)
return order_info and cls(**order_info)
def get_by_wx(cls, wx_id):
db = DbManager().db
sql = 'select * from {table} where wx_id=%s'.format(table=cls.table)
user_bind = db.get(sql, wx_id)
return user_bind and cls(**user_bind)
def exists_user_room(cls, user_id, room_id):
db = DbManager().db
sql = 'select id from {table} where user_id=%s and room_id=%s'.format(
table=cls.table)
rst = db.get(sql, user_id, room_id)
return bool(rst)
def get(cls, id):
db = DbManager().db
sql = 'select * from {table} where id=%s'.format(table=cls.table)
rst = db.get(sql, id)
return rst and cls(**rst)
def get_rong_token_by_user(cls, user_id):
db = DbManager().db
sql = 'select * from {table} where user_id=%s'.format(table=cls.table)
rst = db.get(sql, user_id)
return rst and cls(**rst)
def get_room_admin_count(cls, room_id):
db = DbManager().db
sql = 'select count(id) from {table} where room_id=%s and status=%s'.format(table=cls.table)
rst = db.get(sql, room_id, ROOM_USER_ADMIN)
return rst and rst.get('count(id)')
def get(cls, id):
db = DbManager().db
sql = 'select * from {table} where id=%s'.format(table=cls.table)
comment_info = db.get(sql, id)
return comment_info and cls(**comment_info)
def get_by_user(cls, user_id):
db = DbManager().db
sql = 'select * from {table} where user_id=%s'.format(table=cls.table)
user_background = db.get(sql, user_id)
return user_background and cls(**user_background)
