def add(cls, vote_id, content):
db = DbManager().db
create_time = datetime.now()
sql = 'insert into {table} (vote_id, content, create_time) values (%s, %s, %s)'.format(
table=cls.table)
option_id = db.execute(sql, vote_id, content, create_time)
return option_id and cls.get(option_id)
def is_room_black_user(cls, room_id, user_id):
db = DbManager().db
sql = 'select id from {table} where room_id=%s and user_id=%s'.format(table=cls.table)
try:
return db.query(sql, room_id, user_id)[0]
except:
return None
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 remove_and_black_user(cls, room_id, user_id):
remove_time = datetime.now()
db = DbManager().db
db.execute('begin;')
try:
sql = 'delete from {table} where room_id=%s and user_id=%s'.format(table=cls.table)
db.execute(sql, room_id, user_id)
sql = 'insert into {table} (room_id, user_id, create_time) values (%s, %s, %s)'.format(table=RoomBlackUser.table)
db.execute(sql, room_id, user_id, remove_time)
db.execute('commit;')
logger.info(u'在mysql中从房间中移除用户成功。Room:[%s], User:[%s]' % (room_id, user_id))
except:
db.execute('rollback;')
logger.error(u'在mysql中从房间中移除用户失败。Room:[%s], User:[%s]' % (room_id, user_id))
raise
cache = CacheManager().cache
room_user_key = ROOM_USER_CACHE_KEY % room_id
user_room_key = USER_ROOM_CACHE_KEY % user_id
try:
cache.zrem(room_user_key, user_id)
cache.zrem(user_room_key, room_id)
logger.info(u'在redis中从房间中移除用户成功。Room:[%s], User:[%s]' % (room_id, user_id))
except Exception as e:
logger.error(u'从redis中删除房间用户关系失败。Error:[%s]' % e)
raise
cache.zincrby(ROOM_USER_COUNT_KEY, room_id, -1)
room_increase_rank_key = ROOM_INCREASE_RANK_CACHE_KEY % (remove_time.year, remove_time.month)
cache.zincrby(room_increase_rank_key, room_id, -1)
def is_room_silent_user(cls, room_id, user_id):
db = DbManager().db
sql = 'select id from {table} where room_id=%s and user_id=%s and status=%s'.format(table=cls.table)
try:
return db.query(sql, room_id, user_id, ROOM_USER_SILENT)[0]
except:
return None
def receive_trade_payment(self):
db = DbManager().db
trades = self.trades
now = datetime.now()
all_trade_payed = True
amount = float(self.amount)
for trade in trades:
if trade.status != STATUS_COMPLETE:
all_trade_payed = False
if all_trade_payed:
db.execute('begin;')
try:
sql = 'update {table} set balance=balance+%s where user_id=%s'.format(
table=Account.table)
db.execute(sql, amount, self.creator_id)
sql = 'update {table} set status=%s, pay_time=%s where id=%s'.format(
table=self.table)
db.execute(sql, STATUS_COMPLETE, now, self.id)
sql = 'insert into {table} (user_id, source, order_type, order_id, amount, create_time) values ' \
'(%s, %s, %s, %s, %s, %s)'.format(table=WalletRecord.table)
db.execute(sql, self.creator_id, WALLET_RECORD_CHARGE,
ORDER_CHARGE, self.id, amount, now)
db.execute('commit;')
except:
db.execute('rollback;')
raise
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 daily_create_content_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.query(sql, user_id, USER_INTEGRAL_DAILY_CREATE_CONTENT, date)
return len(rst) >= 3
def daily_rob_welfare_integral_enough(cls, user_id):
db = DbManager().db
date = datetime.now()
sql = 'select id from {table} where user_id=%s and source=%s and create_date=%s'.format(
table=cls.table)
rst = db.query(sql, user_id, USER_INTEGRAL_DAILY_ROB_WELFARE, date)
return len(rst) >= 3
def add(cls, user_id, source, amount, integral, extra=None):
db = DbManager().db
now = datetime.now()
date = now.date()
sql = 'insert into {table} (user_id, source, amount, integral, create_date, create_time, extra) values ' \
'(%s, %s, %s, %s, %s, %s)'.format(table=cls.table)
db.execute(sql, user_id, source, amount, integral, date, now, extra)
def daily_send_reward_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.query(sql, user_id, USER_INTEGRAL_DAILY_SEND_REWARD, date)
return len(rst) >= 10
def add(cls, creator_id, room_id, welfare_type, price, count, name, start_time, deadline, images, room_user_status,
amount, content_status=CONTENT_PUBLIC, welfare_status=WELFARE_ROB_UNDER_WAY):
create_time = datetime.now()
room = Room.get(room_id)
star_fund = StarFund.get_by_room(room_id)
if float(star_fund.balance) < amount:
raise StarFundInsufficient
db = DbManager().db
db.execute('begin;')
try:
sql = 'insert into {table} (creator_id, room_id, content_type, status, create_time, last_comment_time, room_user_status) values' \
' (%s, %s, %s, %s, %s, %s, %s)'.format(table=Content.table)
content_id = db.execute(sql, creator_id, room_id, WELFARE_TYPE, content_status, create_time, create_time, room_user_status)
sql = 'insert into {table} (id, creator_id, room_id, welfare_type, price, count, name, start_time, deadline, images, status, ' \
'create_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'.format(table=cls.table)
db.execute(sql, content_id, creator_id, room_id, welfare_type, price, count, name, start_time, deadline, images,
welfare_status, create_time)
sql = 'update {table} set balance=balance-%s where room_id=%s'.format(table=StarFund.table)
db.execute(sql, amount, room_id)
sql = 'insert into {table} (room_id, source, amount, create_time, extra, extra_info) values (%s, %s, %s, %s, %s, %s)'.\
format(table=StarFundRecord.table)
db.execute(sql, room_id, STAR_FUND_WELFARE_EXPAND, -amount, create_time, content_id, name)
db.execute('commit;')
except:
db.execute('rollback;')
raise
room.update(last_content_updated=create_time, update_time=create_time)
return cls.get(content_id)
def add(cls,
creator_id,
room_id,
name,
deadline,
options,
room_user_status,
status=CONTENT_PUBLIC):
room = Room.get(room_id)
db = DbManager().db
create_time = datetime.now()
db.execute('begin;')
try:
sql = 'insert into {table} (creator_id, room_id, content_type, status, create_time, last_comment_time, room_user_status) values ' \
'(%s, %s, %s, %s, %s, %s, %s)'.format(table=Content.table)
content_id = db.execute(sql, creator_id, room_id, VOTE_TYPE,
status, create_time, create_time,
room_user_status)
sql = 'insert into {table} (id, creator_id, room_id, name, deadline, create_time) values ' \
'(%s, %s, %s, %s, %s, %s)'.format(table=cls.table)
db.execute(sql, content_id, creator_id, room_id, name, deadline,
create_time)
for option in options:
sql = 'insert into {table} (vote_id, content, create_time) values (%s, %s, %s)'.format(
table=VoteOption.table)
db.execute(sql, content_id, option, create_time)
db.execute('commit;')
except:
db.execute('rollback;')
raise
cls.save_created_content_to_redis(creator_id, content_id, create_time)
room.update(last_content_updated=create_time, update_time=create_time)
return content_id and cls.get(content_id)
def update_device_token(self, device_token, os):
db = DbManager().db
update_time = datetime.now()
sql = 'update {table} set device_token=%s, os=%s, update_time=%s where id=%s'.format(
table=self.table)
db.execute(sql, device_token, os, update_time, self.id)
self.flush_device_info()
def add(cls, user_id, balance=0.00):
db = DbManager().db
sql = 'insert into {table} (user_id, balance) values (%s, %s)'.format(
table=cls.table)
account_id = db.execute(sql, user_id, balance)
return account_id and cls(
id=account_id, user_id=user_id, balance=balance)
def gets_all(cls):
db = DbManager().db
sql = 'select * from {table}'.format(table=cls.table)
charge_orders = db.query(sql)
return charge_orders and [
cls(**charge_order) for charge_order in charge_orders
]
def add(cls, user_id, device_token, os):
db = DbManager().db
create_time = datetime.now()
sql = 'insert into {table} (user_id, device_token, os, create_time) values (%s, %s, %s, %s)'.format(
table=cls.table)
device_id = db.execute(sql, user_id, device_token, os, create_time)
return device_id and cls.get(device_id)
def add(cls,
creator_id,
room_id,
text,
images,
video,
room_user_status,
status=CONTENT_PUBLIC):
room = Room.get(room_id)
db = DbManager().db
create_time = datetime.now()
db.execute('begin;')
try:
sql = 'insert into {table} (creator_id, room_id, content_type, status, create_time, last_comment_time, room_user_status) values' \
' (%s, %s, %s, %s, %s, %s, %s)'.format(table=Content.table)
content_id = db.execute(sql, creator_id, room_id, POSTS_TYPE,
status, create_time, create_time,
room_user_status)
sql = 'insert into {table} (id, creator_id, room_id, text, images, video, create_time) values ' \
'(%s, %s, %s, %s, %s, %s, %s)'.format(table=cls.table)
db.execute(sql, content_id, creator_id, room_id, text, images,
video, create_time)
db.execute('commit;')
except:
db.execute('rollback;')
raise
room.update(last_content_updated=create_time, update_time=create_time)
cls.save_created_content_to_redis(creator_id, content_id, create_time)
return content_id and cls.get(content_id)
def add(cls, id, creator_id, group_id, object_name, channel_type, content,
create_time):
db = DbManager().db
sql = 'insert into {table} (id, creator_id, group_id, object_name, channel_type, content, create_time) values ' \
'(%s, %s, %s, %s, %s, %s, %s)'.format(table=cls.table)
db.execute(sql, id, creator_id, group_id, object_name, channel_type,
content, create_time)
def gets_all(cls):
db = DbManager().db
sql = 'select * from {table}'.format(table=cls.table)
reward_orders = db.query(sql)
return reward_orders and [
cls(**reward_order) for reward_order in reward_orders
]
def receive_trade_payment(self):
db = DbManager().db
trades = self.trades
now = datetime.now()
all_trades_complete = True
for trade in trades:
if trade.status != STATUS_COMPLETE:
return
if all_trades_complete:
try:
db.execute('begin;')
for trade in trades:
if trade.pay_method == PAYMETHOD_ACCOUNT:
sql = 'update {table} set balance=balance-%s where user_id=%s'.format(
table=Account.table)
db.execute(sql, float(trade.amount), self.creator_id)
sql = 'update {table} set balance=balance+%s where room_id=%s'.format(
table=StarFund.table)
db.execute(sql, float(self.amount), self.room_id)
sql = 'update {table} set status=%s, pay_time=%s where id=%s'.format(
table=self.table)
db.execute(sql, STATUS_COMPLETE, now, self.id)
sql = 'insert into {table} (user_id, source, order_type, order_id, amount, create_time) values ' \
'(%s, %s, %s, %s, %s, %s)'.format(table=WalletRecord.table)
db.execute(sql, self.creator_id, WALLET_RECORD_ROOM_RENT,
ORDER_ROOM, self.id, -self.amount, now)
db.execute('commit;')
except:
db.execute('rollback;')
raise
RoomUser.add(self.room_id, self.creator_id)
self.flush_fund_cache()
def add(cls,
creator_id,
room_id,
amount,
trades_info,
status=STATUS_PENDING):
db = DbManager().db
create_time = datetime.now()
db.execute('begin;')
try:
sql = 'insert into {table} (creator_id, room_id, amount, status, create_time) values (%s, %s, %s, %s, %s)'\
.format(table=cls.table)
room_order_id = db.execute(sql, creator_id, room_id, amount,
status, create_time)
for trade_info in trades_info:
trade_id = init_trade_id()
amount = trade_info.get('amount')
pay_method = trade_info.get('pay_method')
sql = 'insert into {table} (id, order_id, order_type, amount, pay_method, status, create_time) ' \
'values (%s, %s, %s, %s, %s, %s, %s)'.format(table=Trade.table)
db.execute(sql, trade_id, room_order_id, ORDER_ROOM, amount,
pay_method, status, create_time)
db.execute('commit;')
return cls(room_order_id, creator_id, room_id, amount, status,
create_time, None, None)
except Exception:
db.execute('rollback;')
raise
def search_by_name_or_tag(cls, query):
db = DbManager().db
rooms = set()
sql = 'select * from {table} where name like %s'.format(
table=cls.table)
query = "%%%s%%" % query
rooms_info = db.query(sql, query)
for room_info in rooms_info:
rooms.add(cls(**room_info))
# sql = 'select * from room a, room_tag b, tag c where a.id=b.room_id and b.tag_id=c.id and c.name like %s'
# rooms_info = db.query(sql, query)
# for room_info in rooms_info:
# rooms.add(cls(
# id=room_info.room_id,
# creator_id=room_info.creator_id,
# name=room_info.name,
# intro=room_info.intro,
# rent=room_info.rent,
# avatar=init_pic_url(room_info.avatar),
# limit_user_number=room_info.limit_user_number,
# status=room_info.status,
# create_time=room_info.create_time,
# update_time=room_info.update_time,
# ))
return rooms
def exist_room(cls, name):
db = DbManager().db
sql = 'select id from {table} where name=%s'.format(table=cls.table)
try:
return db.query(sql, name)[0]
except:
return None
def get_lists(cls, start, count):
db = DbManager().db
end = start * count - 1
sql = 'select * from {table} order by id desc limit %s, %s'.format(
table=cls.table)
withdraws = db.query(sql, start, end)
return withdraws and [cls(**withdraw) for withdraw in withdraws]
def get_hot_messages(cls, group_id, count):
db = DbManager().db
now = datetime.now()
limit_time = now - timedelta(days=1)
sql = 'select * from {table} where group_id=%s and create_time>%s and liked_amount>%s order by liked_amount desc limit ' \
'0, %s'.format(table=cls.table)
rst = db.query(sql, group_id, limit_time, 0, count)
return rst and [cls(**d) for d in rst]
def get_tag_ids_by_room(cls, room_id):
db = DbManager().db
sql = 'select tag_id from {table} where room_id=%s'.format(
table=cls.table)
tag_ids_dict = db.query(sql, room_id)
return tag_ids_dict and [
tag_id_dict.get('tag_id') for tag_id_dict in tag_ids_dict
]
def update(self, **kwargs):
db = DbManager().db
params = ['%s="%s"' % (key, kwargs.get(key)) for key in kwargs]
update_sql = ', '.join(params)
sql = 'update {table} set %s where id=%s'.format(
table=self.table) % (update_sql, self.id)
r = db.execute(sql)
return r
def gets_complete_order_by_activity(cls, activity_id):
db = DbManager().db
sql = 'select * from {table} where activity_id=%s and status=%s'.format(
table=cls.table)
orders_info = db.query(sql, activity_id, STATUS_COMPLETE)
return orders_info and [
cls(**order_info) for order_info in orders_info
]