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()