class Basedata(db.Model):
__bind_key__ = 'default'
__tablename__ = 'base_data'
id = db.Column(db.Integer, primary_key = True, autoincrement = True)
code = db.Column(db.String(20), nullable = False, unique = True)
name = db.Column(db.String(20), nullable = False)
value = db.Column(db.String(255), nullable = True)
type = db.Column(db.Integer, nullable = False, default = 0)
create_at = db.Column(db.DateTime, nullable = False, default = lambda: get_now())
update_at = db.Column(db.DateTime, nullable = False, default = lambda: get_now(), onupdate = lambda: get_now())
remark = db.Column(db.String(255), nullable = True)
@staticmethod
def findall():
return Basedata.query.all()
@staticmethod
def find_by_type(type):
return Basedata.query.filter_by(type = type).all()
@staticmethod
def find_by_code(code):
return Basedata.query.filter_by(code = code).first()
@staticmethod
def find(id):
return Basedata.query.get(id)
class Bot(db.Model):
__bind_key__ = 'default'
__tablename__ = 'bot'
id = db.Column(db.String(20), primary_key=True)
name = db.Column(db.String(20), nullable=False)
active = db.Column(db.Integer, nullable=False, index=True)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
@staticmethod
def findall():
return Bot.query.all()
@staticmethod
def find(botid):
return Bot.query.get(botid)
@staticmethod
def find_by_name(name):
return Bot.query.filter_by(name=name).first()
@staticmethod
def destroy(botid):
from common.apikey import APIKey
return BotAssign.destroy(botid) and APIKey.destroy(botid)
def get_statistics_data(request):
'''
获取dashboard的统计数据
:param request: HTTP请求对象l
:return: 统计数据
'''
from datetime import timedelta
from common.util import output_datetime, get_now
if request.method == 'GET':
type = request.args.get('type')
botid = request.args.get('botid')
target = request.args.get('target')
days = request.args.get('days')
elif request.method == 'POST':
type = request.form.get('type')
botid = request.form.get('botid')
target = request.form.get('target')
days = request.form.get('days')
today = output_datetime(get_now(), True, False)
if int(type) == 1:
# 汇总数据
return _get_counts(botid)
elif int(type) == 2:
# 查询发言统计
if int(days) == 7 or int(days) == 30 or int(days) == 60:
return _get_speak_statistics(
botid, target,
output_datetime(get_now() - timedelta(days=int(days)), True,
False), today)
else:
return {'success': 0}
elif int(type) == 3:
# 重新计算发言统计
return _do_speak_statistics(botid, target, today, today)
elif int(type) == 4:
# 发言排行榜
if int(days) == 0 or int(days) == 1:
# 0：查询今天
# 1：查询昨天
return _get_speak_tops(
botid, target,
output_datetime(get_now() - timedelta(days=int(days)), True,
False),
output_datetime(get_now() - timedelta(days=int(days)), True,
False))
elif int(days) == 7 or int(days) == 99999:
# 7：查询7天内
# 99999：查询全部
return _get_speak_tops(
botid, target,
output_datetime(get_now() - timedelta(days=int(days)), True,
False), today)
else:
return {'success': 0}
else:
return {'success': 0}
class ScoreMember(db.Model):
__bind_key__ = 'score'
__tablename__ = 'score_member'
account = db.Column(db.String(20), primary_key=True)
member_id = db.Column(db.String(20), primary_key=True)
member_name = db.Column(db.String(20), nullable=True)
income = db.Column(db.Integer, nullable=False, default=0)
outgo = db.Column(db.Integer, nullable=False, default=0)
balance = db.Column(db.Integer, nullable=False, default=0)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
@staticmethod
def get_member(account, member_id, **kwargs):
member = ScoreMember.query.filter_by(account=account,
member_id=member_id)
if member.first() is None:
member = ScoreMember(
account=account,
member_id=member_id,
income=0,
outgo=0,
balance=0,
member_name='' if kwargs.get('member_name') is None else
kwargs.get('member_name'),
remark=''
if kwargs.get('remark') is None else kwargs.get('remark'))
ScoreMember.query.session.add(member)
ScoreMember.query.session.commit()
return member
else:
return member.first()
@staticmethod
def increase(account: str, member_id: str, amount: int, **kwargs):
member = ScoreMember.get_member(account, member_id, **kwargs)
member.income += amount
member.balance += amount
ScoreMember.query.session.commit()
@staticmethod
def reduce(account: str, member_id: str, amount: int, **kwargs):
member = ScoreMember.get_member(account, member_id, **kwargs)
member.outgo += amount
member.balance -= amount
ScoreMember.query.session.commit()
class APIKey(db.Model):
__bind_key__ = 'default'
__tablename__ = 'apikey'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
botid = db.Column(db.String(20), nullable=False, unique=True)
key = db.Column(db.String(255),
nullable=False,
unique=True,
default=lambda: generate_key(32, True, False, True))
secret = db.Column(db.String(255),
nullable=False,
default=lambda: generate_key(12, False, True, True))
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
@staticmethod
def find_by_key(key):
return APIKey.query.filter_by(key=key).first()
@staticmethod
def find_by_bot(botid):
return APIKey.query.filter_by(bitid=botid).first()
@staticmethod
def destroy(botid):
'''
销毁机器人的API Key
:param botid: 机器人id
:return: 是否成功
'''
APIKey.query.filter_by(botid=botid).delete()
APIKey.query.session.commit()
return True
def refresh(self):
self.key = generate_key(32, True, False, True)
self.secret = generate_key(12, False, True, True)
self.query.session.commit()
class ScoreRule(db.Model):
__bind_key__ = 'score'
__tablename__ = 'score_rule'
account = db.Column(db.String(20), primary_key=True)
code = db.Column(db.String(20), primary_key=True)
description = db.Column(db.String(20), nullable=True, index=True)
type = db.Column(db.String(20), nullable=False, index=True)
amount = db.Column(db.Integer, nullable=True, default=0)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
@staticmethod
def get_rule(account, code):
return ScoreRule.query.filter_by(account=account, code=code).first()
def create(botid, target_type, target_account, member_id, message,
**kwargs):
sign = Sign.find_by_date(
botid,
target_type,
target_account,
member_id,
get_now().strftime('%Y-%m-%d'),
get_now().strftime('%Y-%m-%d'),
)
if len(sign) > 0:
raise Exception(member_id + ':' +
('' if kwargs.get('member_name') is None else
kwargs.get('member_name')) + '今天已经签过到了')
target = get_target_composevalue(target_type, target_account)
record = Sign(
botid=botid,
target=target,
member_id=member_id,
member_name='' if kwargs.get('member_name') is None else
kwargs.get('member_name'),
# date = datetime.now(tz = timezone('Asia/Shanghai')).strftime('%Y-%m-%d') if kwargs.get(
# 'date') is None else kwargs.get('date'),
# time = datetime.now(tz = timezone('Asia/Shanghai')).strftime('%H:%M') if kwargs.get(
# 'time') is None else kwargs.get('time'),
# timemark = int(datetime.now(tz = utc).timestamp()) if kwargs.get(
# 'timemark') is None else kwargs.get('timemark'),
message=message)
record.query.session.add(record)
record.query.session.commit()
sign_code = BotParam.find(botid, 'sign_code')
if sign_code is not None:
ScoreRecord.create_change(sign_code.value,
member_id,
member_name=kwargs.get('member_name'),
botid=botid)
return record
class PointConfirm(db.Model):
__bind_key__ = 'score'
__tablename__ = 'point_confirm'
id = db.Column(db.Integer, primary_key = True, autoincrement = True)
botid = db.Column(db.String(20), nullable = False)
target = db.Column(db.String(20), nullable = False)
point_id = db.Column(db.Integer, nullable = False)
member_id = db.Column(db.String(20), nullable = False)
confirm_code = db.Column(db.String(4), nullable = False, default = lambda: generate_key(4, False, False, True))
expire_at = db.Column(db.DateTime, nullable = False, default = lambda: get_now() + timedelta(minutes = 30))
@staticmethod
def create(botid, target_type, target_account, point_id, member_id, confirm_code):
PointConfirm.clear(botid, target_type, target_account)
target = get_target_composevalue(target_type, target_account)
record = PointConfirm(botid = botid,
target = target,
point_id = point_id,
member_id = member_id,
confirm_code = confirm_code)
PointConfirm.query.session.add(record)
PointConfirm.query.session.commit()
return record
@staticmethod
def clear(botid, target_type, target_account):
target = get_target_composevalue(target_type, target_account)
PointConfirm.query.filter(PointConfirm.botid == botid,
PointConfirm.target == target,
PointConfirm.expire_at <= get_now()).delete()
PointConfirm.query.session.commit()
@staticmethod
def confirm(botid, target_type, target_account, member_id, confirm_code, is_newbie: bool = False):
PointConfirm.clear(botid, target_type, target_account)
target = get_target_composevalue(target_type, target_account)
records = PointConfirm.query.filter(PointConfirm.botid == botid,
PointConfirm.target == target,
PointConfirm.member_id == member_id,
PointConfirm.confirm_code == confirm_code).all()
if len(records) > 0:
# for record in records:
record = records[0]
point = Point.confirm(record.point_id, is_newbie)
PointConfirm.query.session.delete(record)
PointConfirm.query.session.commit()
return point
else:
raise Exception('确认码[' + confirm_code + ']无效，请检查是否已经过期')
class Role(db.Model):
__bind_key__ = 'default'
__tablename__ = 'roles'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(255), nullable=False, unique=True)
description = db.Column(db.String(255), nullable=True)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
def __init__(self, **kwargs):
if kwargs is not None:
if kwargs.get('name', None) is not None:
self.name = kwargs['name']
if kwargs.get('description', None) is not None:
self.description = kwargs['description']
def __repr__(self):
# return "<Model Role `{}`>".format(self.name)
return self.name
@staticmethod
def is_admin(roles):
for role in roles:
if role.name == 'admin':
return True
return False
@staticmethod
def find_by_name(name):
return Role.query.filter_by(name=name).first()
def create(botid, target_type, target_account, member_id, reporter_id, point: int, message, is_newbie: bool = False,
**kwargs):
target = get_target_composevalue(target_type, target_account)
Point.check_limit(botid, target, member_id, reporter_id, point, get_now().strftime('%Y-%m-%d'), is_newbie)
record = Point(botid = botid,
target = target,
member_id = member_id,
member_name = '' if kwargs.get('member_name') is None else kwargs.get('member_name'),
reporter_id = reporter_id,
reporter_name = '' if kwargs.get('reporter_name') is None else kwargs.get('reporter_name'),
point = point,
is_newbie = 1 if is_newbie else 0,
message = message)
record.query.session.add(record)
record.query.session.commit()
return PointConfirm.create(botid, target_type, target_account, record.id, record.member_id, record.confirm_code)
def _get_counts(botid):
from plugins.setting import TargetRule
from common import login
from common.util import output_datetime, get_now
if not login.current_user.is_authenticated:
abort(401)
targets = TargetRule.find_allow_by_user(login.current_user.username)
speak_today_count = 0
sign_today_count = 0
point_today_total = 0
score_today_total = 0
today = output_datetime(get_now(), True, False)
for target in targets:
speak_today_count += _get_speak_today_count(botid, target, today,
today)
sign_today_count += _get_sign_today_count(botid, target, today, today)
point_today_total += _get_point_today_total(botid, target, today,
today)
score_today_total += _get_score_today_total(botid, target, today,
today)
return {
'success': 1,
'data': {
'statistics_data': {
'speak_today_count': speak_today_count,
'sign_today_count': sign_today_count,
'point_today_total': point_today_total,
'score_today_total': score_today_total
}
}
}
def get_report_status(botid, target_type, target_account, reporter_id, ):
target = get_target_composevalue(target_type, target_account)
result = Point.get_report_count(botid, target, '0', reporter_id, output_datetime(get_now(), True, False))
status = {}
status['botid'] = botid
status['target'] = target
status['reporter_id'] = reporter_id
status['reporter_confirmed_total'] = result.reporter_confirmed_total
status['reporter_unconfirm_total'] = result.reporter_unconfirm_total
return status
class Sign(db.Model):
__bind_key__ = 'score'
__tablename__ = 'sign'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
botid = db.Column(db.String(20), nullable=False)
target = db.Column(db.String(20), nullable=False)
member_id = db.Column(db.String(20), nullable=False)
member_name = db.Column(db.String(20), nullable=False)
date = db.Column(db.Date,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now().date,
index=True)
time = db.Column(db.Time,
nullable=False,
default=lambda: datetime.time(get_now()),
onupdate=lambda: datetime.time(get_now()),
index=True)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
message = db.Column(db.String(20), nullable=False)
@staticmethod
def create(botid, target_type, target_account, member_id, message,
**kwargs):
sign = Sign.find_by_date(
botid,
target_type,
target_account,
member_id,
get_now().strftime('%Y-%m-%d'),
get_now().strftime('%Y-%m-%d'),
)
if len(sign) > 0:
raise Exception(member_id + ':' +
('' if kwargs.get('member_name') is None else
kwargs.get('member_name')) + '今天已经签过到了')
target = get_target_composevalue(target_type, target_account)
record = Sign(
botid=botid,
target=target,
member_id=member_id,
member_name='' if kwargs.get('member_name') is None else
kwargs.get('member_name'),
# date = datetime.now(tz = timezone('Asia/Shanghai')).strftime('%Y-%m-%d') if kwargs.get(
# 'date') is None else kwargs.get('date'),
# time = datetime.now(tz = timezone('Asia/Shanghai')).strftime('%H:%M') if kwargs.get(
# 'time') is None else kwargs.get('time'),
# timemark = int(datetime.now(tz = utc).timestamp()) if kwargs.get(
# 'timemark') is None else kwargs.get('timemark'),
message=message)
record.query.session.add(record)
record.query.session.commit()
sign_code = BotParam.find(botid, 'sign_code')
if sign_code is not None:
ScoreRecord.create_change(sign_code.value,
member_id,
member_name=kwargs.get('member_name'),
botid=botid)
return record
@staticmethod
def find_by_date(botid, target_type, target_account, member_id, date_from,
date_to):
target = get_target_composevalue(target_type, target_account)
return Sign.query.filter(Sign.botid == botid, Sign.target == target,
Sign.member_id == member_id,
Sign.date >= date_from,
Sign.date <= date_to).all()
@staticmethod
def find_first_by_member_name(member_name):
return Sign.query.filter_by(member_name=member_name).first()
@staticmethod
def get_count(botid,
target_type,
target_account,
date_from,
date_to,
member=None):
target = get_target_composevalue(target_type, target_account)
if member is None:
member_id = None
elif not member.isdigit():
record = Sign.find_first_by_member_name(member)
member_id = record.member_id
else:
member_id = member
return Sign.query.session.query(func.sum(1).label('cnt')).filter(
Sign.botid == botid, Sign.target == target, Sign.date >= date_from,
Sign.date <= date_to, Sign.member_id == member_id
if member_id is not None else 1 == 1).first()
class BotParam(db.Model):
__bind_key__ = 'score'
__tablename__ = 'bot_param'
botid = db.Column(db.String(20), primary_key=True)
name = db.Column(db.String(20), primary_key=True)
value = db.Column(db.String(100), nullable=False)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
@staticmethod
def create(botid, name, value, remark=None, update=False):
param = BotParam.find(botid, name)
if param is None:
param = BotParam(botid=botid,
name=name,
value=value,
remark=remark if remark is not None else '')
param.query.session.add(param)
param.query.session.commit()
else:
if update:
param = BotParam.update(botid, name, value, remark)
param.query.session.commit()
return param
@staticmethod
def update(botid, name, value, remark=None):
param = BotParam.find(botid, name)
if param:
param.value = value
if remark: param.remark = remark
param.query.session.commit()
return param
@staticmethod
def findall(botid):
return BotParam.query.filter_by(botid=botid).all()
@staticmethod
def find(botid, name):
return BotParam.query.filter_by(botid=botid, name=name).first()
@staticmethod
def delete(botid, name):
BotParam.query.filter_by(botid=botid, name=name).delete()
BotParam.query.session.commit()
return True
@staticmethod
@br.register_init()
def init(botid):
for r in Basedata.find_by_type(1):
BotParam.create(botid, r.value, '请更新设置', r.name)
return True
@staticmethod
@br.register_destroy()
def destroy(botid):
for r in BotParam.findall(botid):
BotParam.delete(botid, r.name)
return True
class TargetRule(db.Model):
__bind_key__ = 'score'
__tablename__ = 'target_rule'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
botid = db.Column(db.String(20), nullable=False)
type = db.Column(db.String(10), nullable=False)
target = db.Column(db.String(100), nullable=False)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
@staticmethod
def create(botid, type, target, remark=None):
rule = TargetRule.find(botid, type, target)
if not rule:
rule = TargetRule(botid=botid,
type=type,
target=target,
remark=remark if remark else '')
rule.query.session.add(rule)
rule.query.session.commit()
return rule
@staticmethod
def findall(botid, type=None):
if type is not None:
return TargetRule.query.filter_by(botid=botid, type=type).all()
else:
return TargetRule.query.filter_by(botid=botid).all()
@staticmethod
def find(botid, type, target):
return TargetRule.query.filter_by(botid=botid,
type=type,
target=target).first()
@staticmethod
def find_by_id(id):
return TargetRule.query.get(id)
@staticmethod
def find_allow_by_user(username):
from common.bot import BotAssign
return TargetRule.query.filter(
TargetRule.botid.in_(
(r.botid for r in BotAssign.find_by_user(username))),
TargetRule.type == 'allow').all()
@staticmethod
def delete(botid, type, target):
TargetRule.query.filter_by(botid=botid, type=type,
target=target).delete()
TargetRule.query.session.commit()
return True
@staticmethod
@br.register_destroy()
def destroy(botid):
for r in TargetRule.findall(botid):
TargetRule.delete(botid, r.type, r.target)
return True
class ScoreRecord(db.Model):
__bind_key__ = 'score'
__tablename__ = 'score_record'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
account = db.Column(db.String(20), nullable=False, index=True)
biz_type = db.Column(db.String(20), nullable=False, index=True)
trans_type = db.Column(db.String(20), nullable=False, index=True)
transfer_id = db.Column(db.String(32), nullable=True)
member_id = db.Column(db.String(20), nullable=True, index=True)
member_name = db.Column(db.String(20), nullable=True, index=True)
amount = db.Column(db.Integer, nullable=True, default=0)
before = db.Column(db.Integer, nullable=True, default=0)
after = db.Column(db.Integer, nullable=True, default=0)
date = db.Column(db.Date,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now().date,
index=True)
time = db.Column(db.Time,
nullable=False,
default=lambda: datetime.time(get_now()),
onupdate=lambda: datetime.time(get_now()),
index=True)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
@staticmethod
def create_change(biz_type: str,
member_id: str,
amount: int = None,
account: str = None,
**kwargs):
if account is None:
act = ScoreAccount.get_default(kwargs.get('botid'))
if act is not None:
account = act.name
if account is None: return None
rule = ScoreRule.get_rule(account, biz_type)
if rule is None or not (rule.type == 'income' or rule.type == 'outgo'):
return None
amount = amount if amount is not None else rule.amount
before = ScoreMember.get_member(account,
member_id,
member_name=kwargs.get(
'member_name', member_id),
remark='积分变更自动创建').balance
after = before + amount if rule.type == 'income' else before - amount
record = ScoreRecord(
account=account,
biz_type=biz_type,
trans_type=rule.type,
member_id=member_id,
amount=amount,
before=before,
after=after,
member_name='' if kwargs.get('member_name') is None else
kwargs.get('member_name'),
remark=''
if kwargs.get('remark') is None else kwargs.get('remark'))
record.query.session.add(record)
record.query.session.commit()
if rule.type == 'outgo':
ScoreMember.reduce(account, member_id, amount, **kwargs)
ScoreAccount.reduce(account, member_id, amount)
else:
ScoreMember.increase(account, member_id, amount, **kwargs)
ScoreAccount.increase(account, member_id, amount)
return record
@staticmethod
def create_transfer(biz_type: str,
outgo_member_id: str,
income_member_id: str,
amount: int,
account: str = None,
**kwargs):
if account is None:
act = ScoreAccount.get_default(kwargs.get('botid'))
if act is not None:
account = act.name
if account is None: return None
rule = ScoreRule.get_rule(account, biz_type)
if rule is None or rule.type != 'transfer': return None
transfer_id = uuid.uuid1().hex
before = ScoreMember.get_member(account,
outgo_member_id,
member_name=kwargs.get(
'outgo_member_name',
outgo_member_id),
remark='积分转账自动创建').balance
record = ScoreRecord(
account=account,
biz_type=biz_type,
trans_type=rule.type,
transfer_id=transfer_id,
member_id=outgo_member_id,
amount=amount * -1,
before=before,
after=before - amount,
member_name='' if kwargs.get('outgo_member_name') is None else
kwargs.get('outgo_member_name'),
remark=''
if kwargs.get('remark') is None else kwargs.get('remark'))
record.query.session.add(record)
before = ScoreMember.get_member(account,
income_member_id,
member_name=kwargs.get(
'income_member_name',
income_member_id),
remark='积分转账自动创建').balance
record = ScoreRecord(
account=account,
biz_type=biz_type,
trans_type=rule.type,
transfer_id=transfer_id,
member_id=income_member_id,
amount=amount,
before=before,
after=before + amount,
member_name='' if kwargs.get('income_member_name') is None else
kwargs.get('income_member_name'),
remark=''
if kwargs.get('remark') is None else kwargs.get('remark'))
record.query.session.add(record)
record.query.session.commit()
ScoreMember.reduce(account, outgo_member_id, amount, **kwargs)
ScoreAccount.reduce(account, outgo_member_id, amount)
ScoreMember.increase(account, income_member_id, amount, **kwargs)
ScoreAccount.increase(account, income_member_id, amount)
return record
@staticmethod
def find_by_member(member_id):
session = sessionmaker(bind=db.get_engine(bind='score'))()
cnts = session.execute(
'SELECT count(1) cnt FROM score_record WHERE member_id = :id',
{'id': member_id})
if cnts is not None:
return cnts.first()
return None
@staticmethod
def find_first_by_member_name(member_name):
return ScoreRecord.query.filter_by(member_name=member_name).first()
@staticmethod
def get_flow(botid,
target_type,
target_account,
date_from,
date_to,
member=None):
if member is None:
member_id = None
elif not member.isdigit():
record = ScoreRecord.find_first_by_member_name(member)
member_id = record.member_id
else:
member_id = member
accounts = ScoreAccount.find_by_target(botid, target_type,
target_account)
return ScoreRecord.query.session.query(
func.sum(func.abs(ScoreRecord.amount)).label('total')).filter(
ScoreRecord.account.in_(
(r.name
for r in accounts)) if len(accounts) > 0 else 1 == 1,
ScoreRecord.date >= date_from, ScoreRecord.date <= date_to,
ScoreRecord.member_id == member_id
if member_id is not None else 1 == 1).first()
class Point(db.Model):
__bind_key__ = 'score'
__tablename__ = 'point_record'
id = db.Column(db.Integer, primary_key = True, autoincrement = True)
botid = db.Column(db.String(20), nullable = False)
target = db.Column(db.String(20), nullable = False)
member_id = db.Column(db.String(20), nullable = False)
member_name = db.Column(db.String(20), nullable = False)
reporter_id = db.Column(db.String(20), nullable = False)
reporter_name = db.Column(db.String(20), nullable = False)
point = db.Column(db.Integer, nullable = False, default = 1)
confirm_code = db.Column(db.String(4), nullable = False, default = lambda: generate_key(4, False, False, True))
has_confirmed = db.Column(db.Integer, nullable = True, default = 0)
is_newbie = db.Column(db.Integer, nullable = True, default = 0)
date = db.Column(db.Date, nullable = False, default = lambda: get_now(), onupdate = lambda: get_now(),
index = True)
time = db.Column(db.Time, nullable = False, default = lambda: datetime.time(get_now()),
onupdate = lambda: datetime.time(get_now()),
index = True)
create_at = db.Column(db.DateTime, nullable = False, default = lambda: get_now())
update_at = db.Column(db.DateTime, nullable = False, default = lambda: get_now(), onupdate = lambda: get_now())
message = db.Column(db.String(20), nullable = False)
@staticmethod
def create(botid, target_type, target_account, member_id, reporter_id, point: int, message, is_newbie: bool = False,
**kwargs):
target = get_target_composevalue(target_type, target_account)
Point.check_limit(botid, target, member_id, reporter_id, point, get_now().strftime('%Y-%m-%d'), is_newbie)
record = Point(botid = botid,
target = target,
member_id = member_id,
member_name = '' if kwargs.get('member_name') is None else kwargs.get('member_name'),
reporter_id = reporter_id,
reporter_name = '' if kwargs.get('reporter_name') is None else kwargs.get('reporter_name'),
point = point,
is_newbie = 1 if is_newbie else 0,
message = message)
record.query.session.add(record)
record.query.session.commit()
return PointConfirm.create(botid, target_type, target_account, record.id, record.member_id, record.confirm_code)
@staticmethod
def check_limit(botid, target, member_id, reporter_id, point: int, date, is_newbie: bool = False):
member_limit = int(BotParam.find(botid, 'point_accept_limit').value)
if is_newbie:
report_limit = int(BotParam.find(botid, 'point_newbie_limit').value)
else:
report_limit = int(BotParam.find(botid, 'point_normal_limit').value)
result = Point.get_report_count(botid, target, member_id, reporter_id, date)
if int(result.reporter_confirmed_total) + point > report_limit:
raise Exception('报点数超过了报点人今天累计的上限')
elif int(result.member_confirmed_total) + point > member_limit:
raise Exception('报点数超过了受报人接受累计的上限')
@staticmethod
def get_report_count(botid, target, member_id, reporter_id, date):
return Point.query.session.query(
func.ifnull(
func.sum(
case(
[(Point.has_confirmed == 1, Point.point)],
else_ = 0
)
), 0
).label('reporter_confirmed_total'),
func.ifnull(
func.sum(
case(
[(
and_(Point.has_confirmed == 1, Point.member_id == member_id),
Point.point
)],
else_ = 0
)
), 0
).label("member_confirmed_total"),
func.ifnull(
func.sum(
case(
[(Point.has_confirmed == 0, Point.point)],
else_ = 0
)
), 0
).label('reporter_unconfirm_total'),
func.ifnull(
func.sum(
case(
[(
and_(Point.has_confirmed == 0, Point.member_id == member_id),
Point.point
)],
else_ = 0
)
), 0
).label("member_unconfirm_total")
).filter(
Point.botid == botid,
Point.target == target,
Point.reporter_id == reporter_id,
Point.date == date
).first()
@staticmethod
def get_report_status(botid, target_type, target_account, reporter_id, ):
target = get_target_composevalue(target_type, target_account)
result = Point.get_report_count(botid, target, '0', reporter_id, output_datetime(get_now(), True, False))
status = {}
status['botid'] = botid
status['target'] = target
status['reporter_id'] = reporter_id
status['reporter_confirmed_total'] = result.reporter_confirmed_total
status['reporter_unconfirm_total'] = result.reporter_unconfirm_total
return status
@staticmethod
def confirm(id, is_newbie: bool = False):
point = Point.query.get(id)
Point.check_limit(point.botid, point.target, point.member_id, point.reporter_id, point.point, point.date,
is_newbie)
point.has_confirmed = 1
Point.query.session.commit()
return point
@staticmethod
def find_first_by_member_name(member_name):
return Point.query.filter_by(member_name = member_name).first()
@staticmethod
def get_total(botid, target_type, target_account, date_from, date_to, member = None):
target = get_target_composevalue(target_type, target_account)
if member is None:
member_id = None
elif not member.isdigit():
record = Point.find_first_by_member_name(member)
member_id = record.member_id
else:
member_id = member
return Point.query.session.query(
func.sum(Point.point).label('total_full'),
func.sum(case([(Point.has_confirmed == 1 , Point.point)], else_ = 0)).label('total_success'),
).filter(
Point.botid == botid,
Point.target == target,
Point.date >= date_from,
Point.date <= date_to,
Point.member_id == member_id if member_id is not None else 1 == 1
).first()
def clear(botid, target_type, target_account):
target = get_target_composevalue(target_type, target_account)
PointConfirm.query.filter(PointConfirm.botid == botid,
PointConfirm.target == target,
PointConfirm.expire_at <= get_now()).delete()
PointConfirm.query.session.commit()
class BotAssign(db.Model):
__bind_key__ = 'default'
__tablename__ = 'bot_assign'
botid = db.Column(db.String(20), primary_key=True)
username = db.Column(db.String(255), primary_key=True)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
@staticmethod
def find_by_user(username):
return BotAssign.query.filter_by(username=username).all()
@staticmethod
def find_by_bot(botid):
return BotAssign.query.filter_by(botid=botid).all()
@staticmethod
def delete(botid):
BotAssign.query.filter_by(botid=botid).delete()
BotAssign.query.session.commit()
return True
@staticmethod
def init_relation(botid):
'''
初始化已分配机器人关联的业务数据
:param botid: 机器人id
:return: 是否成功
'''
for (func_k, func_v) in bothub.init_map.items():
func_v(botid)
return True
@staticmethod
def destroy_relation(botid):
'''
销毁已分配机器人关联的业务数据
:param botid: 机器人id
:return: 是否成功
'''
for (func_k, func_v) in bothub.destroy_map.items():
func_v(botid)
return True
@staticmethod
def destroy(botid):
'''
销毁已分配的机器人
:param botid: 机器人id
:return: 是否成功
'''
BotAssign.delete(botid)
BotAssign.destroy_relation(botid)
return True
class User(db.Model):
__bind_key__ = 'default'
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(255), nullable=False, unique=True)
password = db.Column(db.String(64), nullable=True)
email = db.Column(db.String(120), nullable=True)
active = db.Column(db.Integer, nullable=False, default=1, index=True)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
roles = db.relationship('Role',
secondary=users_roles,
backref=db.backref('users', lazy='dynamic'))
def __init__(self, **kwargs):
if kwargs is not None:
if kwargs.get('username', None) is not None:
self.username = kwargs['username']
if kwargs.get('password', None) is None:
kwargs['password'] = '******'
self.password = kwargs['password']
if kwargs.get('rolename', None) is None:
kwargs['rolename'] = 'user'
role = Role.query.filter_by(name=kwargs['rolename']).one()
self.roles.append(role)
if kwargs.get('remark', None) is not None:
self.remark = kwargs['remark']
def __repr__(self):
"""Define the string format for instance of User."""
return "<Model User `{}`>".format(self.username)
# Flask-Login integration
def is_authenticated(self):
return True
def is_active(self):
return self.active
def is_anonymous(self):
return False
def is_admin(self):
for role in self.roles:
if role.name == 'admin':
return True
return False
def get_id(self):
return self.id
# Required for administrative interface
def __unicode__(self):
return self.username
@staticmethod
def find_by_name(username):
# return User.query.filter_by(username = username).first()
return User.query.get(username)
@staticmethod
def find_by_role(rolename):
role = Role.find_by_name(rolename)
if role is not None:
session = db.session
cnts = session.execute(
'SELECT count(1) cnt FROM users_roles WHERE role_id = :id',
{'id': role.id})
if cnts is not None:
return cnts.first()
return None
# return User.query.get(username)
@staticmethod
def findall():
return User.query.filter_by(active=1).all()
class ScoreAccount(db.Model):
"""
积分账户说明：
1.每个机器人可以拥有多个积分账户，这些积分账户可以分布在同一个目标中，也可以分布在不同目标中
2.无论归属哪个机器人，所有积分账户的账户名不能重复
3.在未指定账户时，积分记账记入机器人的缺省账户
"""
__bind_key__ = 'score'
__tablename__ = 'score_account'
botid = db.Column(db.String(20), primary_key=True)
name = db.Column(db.String(20), primary_key=True, unique=True)
description = db.Column(db.String(20), nullable=True)
# type = db.Column(db.String(20), nullable = True, index = True)
is_default = db.Column(db.Integer, nullable=True, index=True)
target = db.Column(db.String(20), nullable=True)
income = db.Column(db.Integer, nullable=True, default=0)
outgo = db.Column(db.Integer, nullable=True, default=0)
balance = db.Column(db.Integer, nullable=True, default=0)
create_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now())
update_at = db.Column(db.DateTime,
nullable=False,
default=lambda: get_now(),
onupdate=lambda: get_now())
remark = db.Column(db.String(255), nullable=True)
@staticmethod
def find_by_user(username):
from common.bot import BotAssign
return ScoreAccount.query.filter(
ScoreAccount.botid.in_(
(r.botid for r in BotAssign.find_by_user(username)))).all()
@staticmethod
def find_by_id(id):
return ScoreAccount.query.filter_by(botid=id.split(',')[0],
name=id.split(',')[1]).first()
@staticmethod
def find_by_target(botid, target_type, target_account):
target = get_target_composevalue(target_type, target_account)
return ScoreAccount.query.filter_by(botid=botid, target=target).all()
@staticmethod
def get_acount(name):
return ScoreAccount.query.filter_by(name=name).first()
@staticmethod
def increase(account: str, member_id: str, amount: int):
act = ScoreAccount.get_acount(account)
if act is None: return None
act.income += amount
act.balance += amount
ScoreAccount.query.session.commit()
@staticmethod
def reduce(account: str, member_id: str, amount: int):
act = ScoreAccount.get_acount(account)
if act is None: return None
act.outgo += amount
act.balance -= amount
ScoreAccount.query.session.commit()
@staticmethod
def get_default(botid):
return ScoreAccount.query.filter_by(botid=botid,
is_default=True).first()
