class GameVersionManage(ModelBase):
__tablename__ = 'game_version_manage'
# 游戏名称
game = db.Column(db.String(256), index=True, nullable=False)
# 游戏版本
version = db.Column(db.String(32), nullable=False)
# 游戏文件的 md5
md5 = db.Column(db.String(128), nullable=False)
# 创建联合索引
__table_args__ = (
# 第一句与第二句是同义的，但是第二句需要多加一个参数index=True， UniqueConstraint 唯一性索引创建方式
db.Index('game_index_key', 'game', 'version', unique=True),
)
def to_dict(self):
return {
'id': self.id,
'game': self.game,
'version': self.version,
'md5': self.md5,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
class User(ModelBase):
__tablename__ = 'user'
# 使用状态
STATUS_VALUES = ('unused', 'using')
# 用户微信唯一ID
openid = db.Column(db.String(512), index=True, unique=True, nullable=True)
# 用户昵称 通过微信端获取
nick_name = db.Column(db.String(63), default="")
# 微信头像链接
head_img_url = db.Column(db.String(256), default="")
# 电话号码
mobile = db.Column(db.String(64), unique=True, index=True, nullable=False)
# 总游戏使用时长 分钟为单位
total_cost_time = db.Column(db.Integer, nullable=False, default=0)
# 总充值金额
total_account = db.Column(db.Integer, nullable=False, default=0)
# 总的消费金额
used_account = db.Column(db.Integer, nullable=False, default=0)
# 总余额，余额不一定是总充值金额 减去 总消费金额，有可能活动获得奖励金额
balance_account = db.Column(db.Integer, nullable=False, default=0)
# 当前用户使用状态信息 unused 禁用 using 使用
state = db.Column(db.Enum(*STATUS_VALUES), index=True, default='using')
# 删除用户
deleted = db.Column(db.Boolean, default=False)
# 改变用户使用状态
def change_state(self, state):
if state not in self.STATUS_VALUES:
return False
self.state = state
return self.save()
def to_dict(self):
return {
'id': self.id,
'nick_name': self.nick_name,
'head_img_url': self.head_img_url,
'mobile': self.mobile,
'total_account': self.total_account,
'used_account': self.used_account,
'balance_account': self.balance_account,
'state': self.state,
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'total_cost_time': self.total_cost_time,
}
def __repr__(self):
return '<User {}>'.format(self.mobile)
class Maintain(ModelBase):
# 所有地点
ALL_ADDRESS_ID = -1
# 所有大厅
ALL_ADDRESS_STR = u'所有大厅'
__tablename__ = 'maintain'
# 用户启用
STATUS_USING = 'using'
# 用户禁用
STATUS_FORBID = 'forbid'
# 使用状态
STATUS_VALUES = (STATUS_FORBID, STATUS_USING)
# 用户名
username = db.Column(db.String(64), unique=True, nullable=False, index=True)
# 密码
hashed_password = db.Column(db.String(256), nullable=False)
# 姓名
name = db.Column(db.String(64), nullable=False)
# 启用状态 using 启用 unused 停用
state = db.Column(db.Enum(*STATUS_VALUES), index=True, default=STATUS_USING)
# 管理地址ID
address_id = db.Column(db.Integer, default=ALL_ADDRESS_ID)
@property
def password(self): # 设置属性不可读.
raise AttributeError("Password is not a readable attribute.")
@password.setter
def password(self, password): # 写入密码
self.hashed_password = self.generate_password(password)
@classmethod
def generate_password(cls, password):
return generate_password_hash(password)
def verify_password(self, password): # 认证密码
return check_password_hash(self.hashed_password, password)
def to_dict(self):
return {
'id': self.id,
'username': self.username,
'name': self.name,
'state': self.state,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
'address_id': self.address_id,
}
class Recharge(ModelBase):
__tablename__ = 'recharge'
# 用户ID
user_id = db.Column(db.Integer, index=True, nullable=False)
# 充值流水账号 每一次充值带时间戳的唯一ID
transaction_id = db.Column(db.String(64),
unique=True,
index=True,
nullable=False)
# 充值金额
amount = db.Column(db.Integer, index=True, nullable=False)
# 付款时间
pay_time = db.Column(db.DateTime, default=datetime.now)
def __repr__(self):
return '<Recharge {} {}>'.format(self.username, self.dev_name)
def to_dict(self):
data = {
'id': self.id,
'user_id': self.user_id,
'amount': self.amount,
'transaction_id': self.transaction_id,
'pay_time': self.pay_time.strftime('%Y-%m-%d %H:%M:%S'),
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
return data
class DeviceGame(ModelBase):
__tablename__ = 'device_game'
# 设备ID
device_id = db.Column(db.Integer, index=True, nullable=False)
# 游戏名称
name = db.Column(db.String(128), index=True, nullable=False)
# 当前版本
current_version = db.Column(db.String(32), default='')
# 最新版本
newest_version = db.Column(db.String(32), nullable=False)
# 是否可以更新
need_update = db.Column(db.Boolean, default=False)
# 创建联合索引
__table_args__ = (
# 第一句与第二句是同义的，但是第二句需要多加一个参数index=True， UniqueConstraint 唯一性索引创建方式
# db.UniqueConstraint('province', 'city', 'area', 'location', name='location_index'),
db.Index('name_index_key', 'device_id', 'name', unique=True), )
def to_dict(self):
return {
'id': self.id,
'device_id': self.device_id,
'name': self.name,
'current_version': self.current_version,
'newest_version': self.newest_version,
'need_update': self.need_update,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
def to_full_dict(self):
game_dict = self.to_dict()
device = Device.get(self.device_id)
if device is None:
game_dict['device'] = {}
else:
game_dict['device'] = device.to_dict()
return game_dict
def __repr__(self):
return '<DeviceGame {}>'.format(self.id)
class GameList(ModelBase):
__tablename__ = 'game_list'
# 游戏名称
game = db.Column(db.String(256), index=True, nullable=False)
# 游戏版本
version = db.Column(db.String(32), nullable=False)
def to_dict(self):
return {
'id': self.id,
'game': self.game,
'version': self.version,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
class Charge(ModelBase):
__tablename__ = 'charge'
# 费率模板名称
name = db.Column(db.String(64), unique=True, nullable=False, index=True)
# 费率 分钱/分钟
charge_mode = db.Column(db.Integer, nullable=False)
# 反向指向设备列表信息
device_query = db.relationship('Device', backref='charge', lazy='dynamic')
def to_dict(self):
return {
'id': self.id,
'name': self.name,
'charge_mode': self.charge_mode,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
class Address(ModelBase):
__tablename__ = 'address'
# 省份信息
province = db.Column(db.String(16), nullable=False)
# 市级信息
city = db.Column(db.String(64), index=True, nullable=False)
# 区域信息
area = db.Column(db.String(64), index=True, nullable=False)
# 详细地址信息
location = db.Column(db.String(128), index=True, nullable=False)
# 统计设备数目
device_num = db.Column(db.Integer, index=True, nullable=False)
# 反向指向设备列表信息
device_query = db.relationship('Device', backref='address', lazy='dynamic')
# 创建联合索引
__table_args__ = (
# 第一句与第二句是同义的，但是第二句需要多加一个参数index=True， UniqueConstraint 唯一性索引创建方式
# db.UniqueConstraint('province', 'city', 'area', 'location', name='location_index'),
db.Index('location_index_key', 'province', 'city', 'area', 'location', unique=True),
)
def __repr__(self):
return '<Address {}>'.format(self.id)
@classmethod
def create(cls, province, city, area, location, device_num=1):
address = cls(province=province,
city=city,
area=area,
location=location,
device_num=device_num)
try:
db.session.add(address)
db.session.commit()
except IntegrityError:
log.error("主键重复: province = {} city = {} area = {} location = {}".format(
province, city, area, location))
db.session.rollback()
return None, False
except Exception as e:
log.error("未知插入错误: province = {} city = {} area = {} location = {}".format(
province, city, area, location))
log.exception(e)
return None, False
return address, True
# 查找是否有相同地址
@classmethod
def find_address(cls, province, city, area, location):
return cls.query.filter_by(province=province, city=city, area=area, location=location).first()
# 通过详细地址进行查询
@classmethod
def find_address_by_location(cls, location):
result_list = []
item_list = cls.query.filter_by(location=location)
if item_list is None:
return result_list
return [item.to_dict() for item in item_list]
# # 根据城市名称与区域名称查询数据信息
# @classmethod
# def find_address_by_city_and_area(cls, city, area, page, size=10):
# result_list = []
# query = cls.query
#
# # 先过滤城市信息
# query = query.filter(cls.city == city)
#
# # 再过滤区域信息
# if area is not None:
# query = query.filter(cls.area == area)
#
# # 获得数目信息
# total = query.count()
#
# pagination = query.paginate(
# page=page,
# per_page=size,
# error_out=False,
# )
#
# if pagination is None:
# log.error("地址信息查询异常...")
# return package_result(total, result_list)
#
# item_list = pagination.items
# if not isinstance(item_list, list):
# log.error("地址信息查询异常...")
# return package_result(total, result_list)
#
# return package_result(total, [item.to_dict() for item in item_list])
# # 根据时间查询地址信息
# @classmethod
# def find_address_by_time(cls, start_time, end_time, page, size=10):
# result_list = []
# query = cls.query
#
# # 根据创建时间范围进行过滤
# query = query.filter(cls.ctime.between(start_time, end_time))
#
# # 获取数据总数目
# total = query.count()
#
# # 根据时间进行排序
# query = query.order_by(cls.ctime)
#
# pagination = query.paginate(
# page=page,
# per_page=size,
# error_out=False,
# )
#
# if pagination is None:
# log.error("时间信息查询异常...")
# return package_result(total, result_list)
#
# item_list = pagination.items
# if not isinstance(item_list, list):
# log.error("时间信息查询异常...")
# return package_result(total, result_list)
#
# return package_result(total, [item.to_dict() for item in item_list])
# # 获得所有列表信息
# @classmethod
# def get_address_list(cls, page, size=10):
# result_list = []
#
# # 获取数据总数目
# total = cls.query.count()
#
# item_paginate = cls.query.paginate(page=page, per_page=size, error_out=False)
# if item_paginate is None:
# log.warn("地址信息分页查询失败: page = {} size = {}".format(page, size))
# return package_result(total, result_list)
#
# item_list = item_paginate.items
# if item_list is None:
# log.warn("地址信息分页查询失败: page = {} size = {}".format(page, size))
# return package_result(total, result_list)
#
# return package_result(total, [item.to_dict() for item in item_list])
# 增加设备数目
def add_device_num(self, device_num):
if self.device_num + device_num >= 0:
self.device_num += device_num
return self.save()
# 获得全路径地址信息
def get_full_address(self):
return self.province + self.city + self.area + self.location
def to_dict(self):
return {
'id': self.id,
'province': self.province,
'city': self.city,
'area': self.area,
'location': self.location,
'device_num': self.device_num,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
class Role(ModelBase):
__tablename__ = 'role'
# 超级管理员
SUPER_ADMIN = u"superadmin"
# 角色名称
name = db.Column(db.String(64), unique=True, nullable=False, index=True)
# 反向指向用户信息
admin_query = db.relationship('Admin', backref='role', lazy='dynamic')
# todo 角色权限 这里需要角色的权限管理，目前没想好先预留
@classmethod
def create(cls, name):
role = cls(name=name)
try:
db.session.add(role)
db.session.commit()
except IntegrityError:
log.error("主键重复: name = {}".format(name))
db.session.rollback()
return None, False
except Exception as e:
log.error("未知插入错误: name = {}".format(name))
log.exception(e)
return None, False
return role, True
# 获得角色列表
@classmethod
def find_role_list(cls, page, size=10):
# 获取数据总数目
total = 0
result_list = []
item_paginate = cls.query.paginate(page=page,
per_page=size,
error_out=False)
if item_paginate is None:
log.warn("角色信息分页查询失败: page = {} size = {}".format(page, size))
return package_result(total, result_list)
item_list = item_paginate.items
if item_list is None:
log.warn("角色信息分页查询失败: page = {} size = {}".format(page, size))
return package_result(total, result_list)
return package_result(item_paginate.total,
[item.to_dict() for item in item_list])
@classmethod
def get_by_name(cls, name):
return cls.query.filter_by(name=name).first()
def __repr__(self):
return '<Role {}>'.format(self.role_name)
def to_dict(self):
return {
'id': self.id,
'name': self.name,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
class Deploy(ModelBase):
__tablename__ = 'deploy'
# 设备名称
device_id = db.Column(db.Integer, index=True, nullable=False)
# 省份信息
province = db.Column(db.String(16), nullable=False)
# 市级信息
city = db.Column(db.String(64), nullable=False)
# 区域信息
area = db.Column(db.String(64), nullable=False)
# 详细地址信息
location = db.Column(db.String(128), nullable=False)
#
# # 创建联合索引
# __table_args__ = (
# # 第一句与第二句是同义的，但是第二句需要多加一个参数index=True， UniqueConstraint 唯一性索引创建方式
# # db.UniqueConstraint('province', 'city', 'area', 'location', name='location_index'),
# db.Index('deploy_index_key', 'device_id', 'province', 'city', 'area', 'location'),
# )
@classmethod
def create(cls, device_id, province, city, area, location):
deploy = cls(
device_id=device_id,
province=province,
city=city,
area=area,
location=location)
try:
db.session.add(deploy)
db.session.commit()
except IntegrityError:
log.error("主键重复: device_id = {} province = {} city = {} area = {} location = {}".format(
device_id, province, city, area, location))
db.session.rollback()
return None, False
except Exception as e:
log.error("未知插入错误: device_id = {} province = {} city = {} area = {} location = {}".format(
device_id, province, city, area, location))
log.exception(e)
return None, False
return deploy, True
def to_dict(self):
device = Device.get(self.device_id)
if device is None:
device_dict = {}
else:
device_dict = device.to_dict()
return {
'id': self.id,
'device': device_dict,
'province': self.province,
'city': self.city,
'area': self.area,
'location': self.location,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
def __repr__(self):
return '<Deploy {}>'.format(self.dev_name)
class Device(ModelBase):
__tablename__ = 'device'
# 当前设备存活状态
ALIVE_OFFLINE = 'offline'
ALIVE_ONLINE = 'online'
# 使用状态
STATUS_VALUES = (DeviceStatus.STATUE_FREE, DeviceStatus.STATUE_BUSY,
DeviceStatus.STATUE_LOCK, DeviceStatus.STATUS_MAINTAIN)
# 更新状态
UPDATE_STATUS_VALUES = (DeviceUpdateStatus.UPDATE_WAIT,
DeviceUpdateStatus.UPDATE_FINISH,
DeviceUpdateStatus.UPDATE_ING,
DeviceUpdateStatus.UPDATE_CHECK)
# 存活状态
ALIVE_VALUES = (ALIVE_OFFLINE, ALIVE_ONLINE)
# 设备机器码
device_code = db.Column(db.String(128), unique=True, index=True)
# 投放ID
address_id = db.Column(db.Integer, db.ForeignKey('address.id'))
# 费率模板ID
charge_id = db.Column(db.Integer, db.ForeignKey('charge.id'))
# 设备收入
income = db.Column(db.Integer, nullable=False, default=0)
# 设备当前使用状态 free 空闲 busy 忙碌 lock 锁定
state = db.Column(db.Enum(*STATUS_VALUES),
index=True,
default=DeviceStatus.STATUE_FREE)
# 状态版本信息 乐观锁
state_version = db.Column(db.Integer, default=0)
# 存活状态
alive = db.Column(db.Enum(*ALIVE_VALUES),
index=True,
default=ALIVE_OFFLINE)
# 更新状态
update_state = db.Column(db.Enum(*UPDATE_STATUS_VALUES),
default=DeviceUpdateStatus.UPDATE_FINISH)
# 更新状态版本信息
update_state_version = db.Column(db.Integer, default=0)
# 最后更新成功时间
last_update_time = db.Column(db.DateTime,
default=datetime.now,
nullable=False)
def __repr__(self):
return '<Device {}>'.format(self.name)
# # 获得部署列表
# def get_device_deploy_list(self, page, size):
# # 获取数据总数目
# total = 0
# result_list = list()
#
# # 获取部署信息列表
# item_paginate = self.deploy_query.paginate(page=page, per_page=size, error_out=False)
# if item_paginate is None:
# log.warn("获取部署信息翻页查询失败: device = {} page = {} size = {}".format(self.id, page, size))
# return package_result(total, result_list)
#
# item_list = item_paginate.items
# if item_list is None:
# log.warn("部署信息分页查询失败: device = {} page = {} size = {}".format(self.id, page, size))
# return package_result(total, result_list)
#
# return package_result(item_paginate.total, [item.to_dict() for item in item_list])
# 删除设备需要事务控制
def delete(self):
try:
db.session.delete(self)
self.address.device_num -= 1 if self.address.device_num >= 1 else 0
db.session.add(self.address)
db.session.commit()
except Exception as e:
db.session.rollback()
log.error("未知删除错误: {}".format(
json.dumps(self.to_dict(), ensure_ascii=False)))
log.exception(e)
return False
return True
def to_dict(self):
return {
'id': self.id,
# 费率
'charge': self.charge.to_dict(),
'device_code': self.device_code,
'address': self.address.to_dict(),
'income': self.income,
'state': self.state,
'alive': self.alive,
'update_state': self.update_state,
'last_update_time':
self.last_update_time.strftime('%Y-%m-%d %H:%M:%S'),
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
class UseRecord(ModelBase):
__tablename__ = 'use_record'
# 用户名
user_id = db.Column(db.Integer, index=True, nullable=False)
# 设备ID
device_id = db.Column(db.Integer, index=True, nullable=False)
# 省份信息
province = db.Column(db.String(16), nullable=False)
# 市级信息
city = db.Column(db.String(64), nullable=False)
# 区域信息
area = db.Column(db.String(64), nullable=False)
# 详细地址信息
location = db.Column(db.String(128), nullable=False)
# 花费的金额
cost_money = db.Column(db.Integer, nullable=False, index=True, default=0)
# 下机时间 数据初始化时以创建时间为结束时间
end_time = db.Column(db.DateTime, nullable=False, default=datetime.now)
# 花费时间 分钟为单位
cost_time = db.Column(db.Integer, nullable=False, index=True, default=0)
# # 生效时间 创建时间
# ctime = db.Column(db.DateTime, default=datetime.now, index=True, nullable=False)
#
# # 数据更新时间
# utime = db.Column(db.DateTime, default=datetime.now, index=True, nullable=False)
@classmethod
def create(cls, user_id, device_id, province, city, area, location):
use_record = cls(user_id=user_id,
device_id=device_id,
province=province,
city=city,
area=area,
location=location)
try:
db.session.add(use_record)
db.session.commit()
except IntegrityError:
log.error("未知错误: {} {}".format(user_id, device_id))
db.session.rollback()
return None, False
except Exception as e:
log.error("未知插入错误: {} {}".format(user_id, device_id))
log.exception(e)
return None, False
return use_record, True
def __repr__(self):
return '<UseRecord {} {}>'.format(self.user_id, self.device_id)
# 得到上机数据
def to_charging(self):
return {
'id': self.id,
'user_id': self.user_id,
'device_id': self.device_id,
# 花费金额数目
'cost_money': self.cost_money,
# 上机时间
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
# 更新时间，主要用户同步计费
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
# 已经上机时间
'cost_time': self.cost_time
}
# 获得全路径地址信息
def get_full_address(self):
return self.province + self.city + self.area + self.location
def to_dict(self):
to_json = {
'id': self.id,
'user_id': self.user_id,
'device_id': self.device_id,
'province': self.province,
'city': self.city,
'area': self.area,
'location': self.location,
'cost_money': self.cost_money,
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'end_time': self.end_time.strftime('%Y-%m-%d %H:%M:%S'),
'cost_time': self.cost_time # 分钟
}
item = User.get(self.user_id)
if item is not None:
to_json['user'] = item.to_dict()
item = DeviceService.get_device_by_id(self.device_id)
if item is not None:
to_json['device'] = item.to_dict()
return to_json
class Admin(UserMixin, ModelBase):
__tablename__ = 'admin'
# 接管查询类
query_class = AdminQuery
# 使用状态
STATUS_VALUES = ('unused', 'using')
# 用户名
username = db.Column(db.String(64),
unique=True,
nullable=False,
index=True)
# 密码
hashed_password = db.Column(db.String(256), nullable=False)
# 姓名
name = db.Column(db.String(64), nullable=False)
# 角色 外键
# role_id = db.Column(db.Integer, nullable=False)
role_id = db.Column(db.Integer, db.ForeignKey('role.id'))
# 管理员启用状态 using 启用 unused 停用
state = db.Column(db.Enum(*STATUS_VALUES), index=True, default='using')
@classmethod
def create(cls, username, password, name, role_id):
admin = cls(username=username, name=name, role_id=role_id)
admin.password = password
try:
db.session.add(admin)
db.session.commit()
except IntegrityError:
log.error("主键重复: username = {} name = {} role_id = {}".format(
username, name, role_id))
db.session.rollback()
return None, False
except Exception as e:
log.error("未知插入错误: username = {} name = {} role_id = {}".format(
username, name, role_id))
log.exception(e)
return None, False
return admin, True
# 获得管理员列表信息
@classmethod
def get_admin_list(cls, page, size=10):
total = 0
result_list = []
item_paginate = cls.query.paginate(page=page,
per_page=size,
error_out=False)
if item_paginate is None:
log.warn("管理员信息分页查询失败: page = {} size = {}".format(page, size))
return package_result(total, result_list)
item_list = item_paginate.items
if item_list is None:
log.warn("管理员信息分页查询失败: page = {} size = {}".format(page, size))
return package_result(total, result_list)
return package_result(item_paginate.total,
[item.to_dict() for item in item_list])
@classmethod
def get_by_username(cls, username):
return cls.query.filter_by(username=username).first()
# 是否启用状态
def is_active(self):
return self.state == 'using'
@property
def password(self): # 设置属性不可读.
raise AttributeError("Password is not a readable attribute.")
@password.setter
def password(self, password): # 写入密码
self.hashed_password = generate_password_hash(password)
def verify_password(self, password): # 认证密码
return check_password_hash(self.hashed_password, password)
def __repr__(self):
return '<Admin {}>'.format(self.addr_id)
def to_dict(self):
return {
'id': self.id,
'username': self.username,
'name': self.name,
'role': self.role.to_dict(),
'state': self.state,
'utime': self.utime.strftime('%Y-%m-%d %H:%M:%S'),
'ctime': self.ctime.strftime('%Y-%m-%d %H:%M:%S'),
}
