class ToDoList(db.Model):
__tablename__ = 'todolist'
__table_args__ = {"extend_existing": True}
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(120), nullable=False)
taskname = db.Column(db.String(120), nullable=False)
accomplish = db.Column(db.Boolean, nullable=False)
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def find_task_by_username(cls, username):
result = cls.query.filter_by(username=username)
task_list = list()
for i in result:
task_list.append(i)
return task_list
@classmethod
def update_task(cls, id, new_task_name, accomplish):
result = cls.query.filter(cls.id == id).first()
result.taskname = new_task_name
result.accomplish = accomplish
db.session.commit()
@classmethod
def del_task(cls, id):
result = cls.query.filter_by(id=id).first()
db.session.delete(result)
db.session.commit()
class User(db.Model):
"""This class represent user model."""
__tablename__ = 'user'
guid = db.Column(postgresql.UUID, index=True, unique=True, server_default=func.uuid_generate_v4(), primary_key=True)
email = db.Column(db.String(255), index=True, unique=True, nullable=False)
password = db.Column(db.String(255), nullable=False)
first_name = db.Column(db.String(32), nullable=True)
last_name = db.Column(db.String(32), nullable=True)
created_at = db.Column(db.DateTime, server_default=func.now(), nullable=False)
updated_at = db.Column(db.DateTime, nullable=True)
def set_password(self, password):
"""Set user password"""
self.password = generate_password_hash(password)
def check_password(self, password):
"""Check user password"""
return check_password_hash(self.password, password)
def __iter__(self):
yield 'guid', self.guid
yield 'email', self.email
yield 'first_name', self.first_name
yield 'last_name', self.last_name
yield 'created_at', self.created_at
yield 'updated_at', self.updated_at
def __repr__(self):
return '<User %s>' % self.email
class Optimization(db.Model, ModelBase):
"""
门店优化结果关系表，根据生成的对应优化结果保存基本信息
"""
__tablename__ = "optimization"
store_id = db.Column(db.Integer, nullable=False)
# 对应 forecast.version
version = db.Column(db.String(64), nullable=False)
# 当前库存周转天数
current_inventory_turnover_days = db.Column(db.Integer, nullable=False)
# 补货后库存周转天数（门店维度）
optimized_inventory_turnover_days = db.Column(db.Integer, nullable=False)
# 安全库存天数 默认为 7 天
safe_inventory_days = db.Column(db.Integer,
nullable=False,
server_default=db.text("7"))
order_amount_total = db.Column(db.Float)
# 预估的货损率信息和业务配置目标预设的结果，json 字段保存信息.
target_info = db.Column(db.Text(), nullable=True)
# 当前优化的状态：初始化，修改中，完成
status = db.Column(TINYINT(display_width=1))
# 订单 id，提交订单时自动生成：store_id+年+月+日+当天第几个订单 eg. 1023432_20200531002
order_id = db.Column(db.String(64), nullable=True, index=True)
class User(db.Model, UserMixin):
user_id = db.Column('id', db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True)
password = db.Column(db.String(50))
role = db.Column(db.String(200))
__tablename__ = 'tb_user'
def __init__(self,
user_id=None,
role=None,
password=None,
name="anonymous"):
self.user_id = user_id
self.role = role
self.password = password
self.name = name
def is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return unicode(self.user_id)
def __repr__(self):
return '<User %r>' % (self.user_id)
class Contact(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(25), nullable=False, unique=True)
email = db.Column(db.String(50), nullable=True)
phone = db.Column(db.String(10), nullable=True)
birthday = db.Column(db.Date, nullable=True)
balance = db.Column(db.Integer, nullable=False, default=0)
class User(db.Model, UserMixin):
user_id = db.Column('user_id', db.Integer, primary_key=True)
accountNumber = db.Column(db.String(200), unique=True)
password = db.Column(db.String(50), unique=True)
__tablename__ = 'data_user'
def __init__(self,
user_id=None,
accountNumber=None,
password=None,
name="anonymous"):
self.user_id = user_id
self.accountNumber = accountNumber
self.password = password
def is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return str(self.user_id)
def __repr__(self):
return '<User %r>' % (self.accountNumber)
class User(db.Model, UserMixin, ModelBase):
__tablename__ = "user"
name = db.Column(db.String(255), unique=True, nullable=False)
email = db.Column(db.String(255), unique=True, nullable=False)
password_hash = db.Column(db.String(255), nullable=False)
# true: 为正常账号用户, false: 为禁用账号用户
active = db.Column(db.Boolean())
# 账号申请审批状态
status = db.Column(db.Integer)
is_admin = db.Column(db.Boolean(), default=False)
roles = db.relationship(
"Role",
secondary=roles_users,
backref=db.backref("users", lazy="dynamic"),
)
@property
def is_active(self):
return self.active
@property
def is_authenticated(self):
return True
@property
def is_anonymous(self):
return False
def get_id(self):
return str(self.id)
@property
def password(self):
raise AttributeError("password is not readable")
@password.setter
def password(self, password):
self.password_hash = hash_password(password)
def validate_password(self, password):
return verify_password(password, self.password_hash)
# 有效期为 24 小时
def generate_confirmation_token(self, expiration=24 * 3600):
"""
生成 confirm 链接校验 token
"""
serializer = Serializer(current_app.config["SECRET_KEY"], expiration)
return serializer.dumps({"confirm": self.id}).decode("utf-8")
@staticmethod
def get_id_by_token(token):
serializer = Serializer(current_app.config["SECRET_KEY"])
try:
data = serializer.loads(token)
return data.get("confirm")
except (SignatureExpired, BadSignature):
return None
class FacebookCredential(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(4096))
password = db.Column(db.String(4096))
logged_timestamp = db.Column(db.DateTime, default=datetime.utcnow)
class Tool(BaseModel):
__tablename__ = 'tools'
name = db.Column(db.String(255), nullable=False)
path = db.Column(db.String(255), nullable=False)
description = db.Column(db.Text, nullable=False)
def __repr__(self):
return "<Tool '{}'>".format(self.name)
class Contact(db.Model):
id = db.Column(db.Integer, primary_key=True)
# Ici, on souhaite que chaque Contact dispose des champs:
# name : un nom unique et obligatoire (type String)
name = db.Column(db.String(25), unique=True, nullable=False)
# email : une adresse mail optionnelle (type String)
email = db.Column(db.String(50), nullable=True)
# phone : un numero de telephone optionnel (type String)
phone = db.Column(db.String(10), nullable=True)
class Chat(db.Model):
id = db.Column(db.Integer, primary_key=True)
user1 = db.Column(db.String(64), index=True)
user2 = db.Column(db.String(64), index=True)
chat = db.Column(db.String(640000), index=True)
def __repr__(self):
return '<Chat {0}:{1}>'.format(self.user1, self.user2)
def to_dict(self):
return {"user1": self.user1, "user2": self.user2, "chat": self.chat}
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
def __repr__(self):
return '<User {}>'.format(self.username)
def to_dict(self):
return {"username": self.username, "id": self.id, "email": self.email}
class Record(db.Model):
__tablename__ = 'tb_record'
index = db.Column(db.Integer, primary_key=True)
timestamp = db.Column(db.String(200), nullable=False) #时间戳
pre_describe = db.Column(db.String(200), nullable=False)
describe = db.Column(db.String(200), nullable=False) #区块描述对象
def __init__(self, index=None, timestamp=None, pre_describe=None, describe=None):
self.index = index
self.timestamp = timestamp
self.pre_describe=pre_describe
self.describe = describe
class Hub(db.Model, DBBase):
"""
仓库数据表 / 补货关系表
"""
__tablename__ = "hub"
hub_id = db.Column(db.String(16),
primary_key=True,
autoincrement=False,
nullable=False)
store_id = db.Column(db.String(16), nullable=False)
hub_name = db.Column(db.String(16), nullable=False)
class User(db.Model, UserMixin):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(100), index=True, unique=True)
password_hash = db.Column(db.String(128))
email = db.Column(db.String(100), index=True, unique=True)
# posts = db.relationship('Post', backref='author', lazy='dynamic')
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def verify_password(self, password):
return check_password_hash(self.password_hash, password)
def list(cls, **kwargs):
page, per_page = kwargs.pop("page"), kwargs.pop("per_page")
args = [
cls._model.updated_at,
cls._model.order_id,
(db.cast(cls._model.store_id, db.String(10)) +
Store.store_name).label("store"),
cls._model.current_inventory_turnover_days,
cls._model.optimized_inventory_turnover_days,
]
# # 订单列表只展示已完成状态的订单
filter_spec = [(cls._model.status, OPTIMIZATION_STATUS.FINISHED.value)]
df = cls._model.model_query(
args=args,
join_ons=[(Store, Store.store_id == cls._model.store_id)],
order_keys=cls._model.updated_at.desc(),
filter_spec=filter_spec,
df=True,
)
df["updated_at"] = df.apply(
lambda row: row.updated_at.strftime("%Y年%m月%d"), axis=1)
return {
"currentPageNum":
page,
"totalNum":
df.__len__(),
"optimizations":
df[(page - 1) * per_page:page * per_page].to_dict("record"),
}
class Post(db.Model):
"""This class represent post model."""
__tablename__ = 'post'
guid = db.Column(postgresql.UUID,
index=True,
unique=True,
server_default=func.uuid_generate_v4(),
primary_key=True)
blog_guid = db.Column(postgresql.UUID, index=True, nullable=False)
title = db.Column(db.String(255), index=True, nullable=False)
body = db.Column(db.Text, nullable=True)
created_at = db.Column(db.DateTime,
server_default=func.now(),
nullable=False)
updated_at = db.Column(db.DateTime, nullable=True)
def __iter__(self):
yield 'guid', self.guid
yield 'blog_guid', self.blog_guid
yield 'title', self.title
yield 'body', self.body
yield 'created_at', self.created_at
yield 'updated_at', self.updated_at
def __repr__(self):
return '<Post %s>' % self.title
class HubInventory(db.Model, InventoryModelBase):
__tablename__ = "hub_inventory"
__table_args__ = (db.Index("ix_location_id_date", "location_id", "date"), )
# location_id is Foreign hub.hub_id
location_id = db.Column(db.String(16), nullable=False)
class Tool(BaseModel):
__tablename__ = 'tools'
name = db.Column(db.String(255), nullable=False)
path = db.Column(db.String(255), nullable=False)
description = db.Column(db.Text, nullable=False)
def serialize(self):
return {
'id': self.id,
'name': self.name,
'path': self.path,
'description': self.description,
}
def __repr__(self):
return "<Tool '{}'>".format(self.name)
class Post(db.Model):
__tablename__ = 'post'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(140))
content = db.Column(db.Text)
pub_date = db.Column(db.DateTime)
class SKU(db.Model, DBBase):
"""
商品主数据表
"""
__tablename__ = "sku"
sku_id = db.Column(
db.String(16), primary_key=True, autoincrement=False, nullable=False
)
category = db.Column(db.String(64), nullable=False)
brand = db.Column(db.String(64), nullable=False)
sub_brand = db.Column(db.String(64), nullable=False)
barcode = db.Column(db.BigInteger, nullable=False)
sku_name = db.Column(db.String(128), nullable=False)
name_en = db.Column(db.String(128), nullable=False)
cost = db.Column(db.Float, nullable=False)
shelf_life = db.Column(db.Integer, nullable=False)
pack_size = db.Column(db.String(64), nullable=False)
net_weight = db.Column(db.Float, nullable=False)
moq = db.Column(db.Float, nullable=False)
moa = db.Column(db.Float, nullable=False)
price = db.Column(db.Float, nullable=False)
is_seasonal = db.Column(db.Boolean, nullable=False)
status = db.Column(db.Boolean, nullable=False)
class Classroom(db.Model):
__tablename__ = 'classroom'
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(100), nullable=False)
time = db.Column(db.Time, nullable=False)
start_date = db.Column(db.Date, nullable=False)
end_date = db.Column(db.Date, nullable=True)
volunteer_id = db.Column(db.Integer, nullable=False)
student_id = db.Column(db.Integer, nullable=False)
def insert(self):
try:
db.session.add(self)
db.session.commit()
except:
db.session.rollback()
raise DBException(f"Failed to create Classroom {self.description}")
finally:
db.session.close()
def update(self):
try:
db.session.commit()
except:
db.session.rollback()
raise DBException(f"Failed to update Classroom {self.name}")
finally:
db.session.close()
def delete(self):
try:
db.session.delete(self)
db.session.commit()
except:
db.session.rollback()
raise DBException(f"Failed to remove Classroom {self.name}")
finally:
db.session.close()
def long(self):
classroom_info = {
"id": self.id,
"description": self.description,
"time": str(self.time),
"start_date": str(self.start_date),
"end_date": str(self.end_date),
"volunteer_id": self.volunteer_id,
"student_id": self.student_id
}
return classroom_info
def __repr__(self):
return f'<Volunteer {self.volunteer_id}: Student {self.student_id}>'
def __hash__(self):
return self.id
class Bug(BaseModel):
__tablename__ = 'bugs'
title = db.Column(db.String(255), nullable=False)
vuln_id = db.Column(db.Integer, nullable=False, default=0)
severity = db.Column(db.Integer, nullable=False, default=0)
description = db.Column(db.Text, nullable=False)
impact = db.Column(db.Text, nullable=False)
status = db.Column(db.Integer, nullable=False, default=0)
submitter_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
reviewer_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
@property
def vulnerability_as_string(self):
return VULNERABILITIES[self.vuln_id][0]
@property
def severity_as_string(self):
return SEVERITY[self.severity]
@property
def status_as_string(self):
return BUG_STATUSES[self.status]
@property
def bounty(self):
return VULNERABILITIES[self.vuln_id][1] * self.severity
@staticmethod
def is_unique(signature):
for bug in Bug.query.all():
s = ' '.join((bug.title, bug.description, bug.impact))
js = get_jaccard_sim(signature, s)
if js > 0.5:
return False
return True
@property
def is_validated(self):
# includes any validation result
# rejected, confirmed, and fixed
if self.status > 0:
return True
return False
@property
def is_accepted(self):
# includes confirmed and fixed
if self.status > 1:
return True
return False
def __repr__(self):
return "<Bug '{}'>".format(self.title)
class Score(BaseModel):
__tablename__ = 'scores'
player = db.Column(db.String(255), nullable=False)
score = db.Column(db.Integer, nullable=False)
recid = db.Column(db.Integer)
recording = db.Column(db.Text, nullable=False)
def __repr__(self):
return "<Score '{}:{}'>".format(self.player, self.score)
class Scan(BaseModel):
__tablename__ = 'scans'
id = db.Column(db.String(36), primary_key=True)
command = db.Column(db.String(255), nullable=False)
results = db.Column(db.Text)
complete = db.Column(db.Boolean, default=False, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
def serialize(self, include_results=False):
return {
'id': self.id,
'created': self.created_as_string,
'modified': self.modified_as_string,
'command': self.command,
'complete': self.complete,
}
def __repr__(self):
return "<Scan '{}'>".format(self.name)
class Store(db.Model, DBBase):
"""
门店主数据表
"""
__tablename__ = "store"
store_id = db.Column(db.Integer,
primary_key=True,
autoincrement=False,
nullable=False)
store_name = db.Column(db.String(128), nullable=False)
# 年销售量
gsv = db.Column(db.Float, nullable=True)
province = db.Column(db.String(128), nullable=False)
city = db.Column(db.String(64), nullable=False)
region = db.Column(db.String(64), nullable=False)
# 开店日期
opening_dte = db.Column(db.DateTime)
class UserModel(db.Model):
__tablename__ = 'users'
__table_agrs__ = {"extend_eisting": True} # 如果表已经被创建过,需要加这个参数提供扩展
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(120), nullable=False)
def save_to_db(self):
'''创建实例，调用save_to_db保存数据'''
db.session.add(self)
db.session.commit()
@classmethod
def find_by_username(cls, username):
'''调用类方法，按username查询用户信息'''
return cls.query.filter_by(username=username).first()
@classmethod
def return_all(cls):
'''返回所有的用户'''
def to_json(x):
return {'username': x.username, 'password': x.password}
return {
"user": list(map(lambda x: to_json(x), UserModel.query.all()))
} # 直接用to_json
@classmethod
def delete_all(cls):
try:
num_rows_delete = db.session.query(cls)
db.session.commit()
return {'message': f"{num_rows_delete} row(s) deleted"}
except:
return {'message': "Someting went wrong"}
@staticmethod
def generate_hash(password):
return sha256.hash(password)
@staticmethod
def verify_hash(password, hash):
return sha256.verify(password, hash)
class Url(db.Model):
__tablename_ = 'urls'
id = db.Column(db.INTEGER,primary_key=True,autoincrement=True)
name = db.Column(db.String(64), unique=True, index=True)
url = db.Column(db.TEXT)
mode = db.Column(db.String(10))
method = db.Column(db.String(10))
timeout = db.Column(db.INTEGER)
def to_dict(self):
return {
'id':self.id,
'name':self.name,
'url': self.url,
'mode': self.mode,
'method':self.method,
'timeout': self.timeout,
}
def __repr__(self):
return '\r\n'.join(['{}={}'.format(key,val) for key,val in self.__dict__.items()])
class Forecast(db.Model, ModelBase):
"""
预测结果输出表
"""
__tablename__ = "forecast"
__table_args__ = (db.Index(
"ix_version_store_id_forecast_week",
"version",
"store_id",
"predict_week",
), )
version = db.Column(db.String(36), nullable=False)
store_id = db.Column(db.Integer, nullable=False)
predict_week = db.Column(db.String(8), nullable=False)
sku_id = db.Column(db.String(16), nullable=False)
run_week = db.Column(db.String(8), nullable=False)
price = db.Column(db.Float,
nullable=False) # 预测结果实际用到的 sku price，可能与产品主数据中不同
qty_mean = db.Column(db.Float, nullable=False)
qty_std = db.Column(db.Float, nullable=False)
class OptimizedOrder(db.Model, ModelBase):
"""
优化结果数据表
"""
__tablename__ = "optimized_order"
sku_id = db.Column(db.String(16), nullable=False)
# 当时可用库存
store_inventory = db.Column(db.Float)
# 优化算法根据商品主数据中对应 SKU 单价值
unit_price = db.Column(db.Float)
order_id = db.Column(db.String(64), nullable=True, index=True)
"""
优化算法根据预测结果给出的第一版 AI 优化补货结果
包括：
- 补货量
- 库存周转天数（门店下 SKU 维度）
"""
optimized_replenishment = db.Column(db.Float)
optimized_inventory_turnover_days = db.Column(db.Float)
"""
人工修改过之后的确认版本优化补货结果
"""
# 人工修改数据 可以 +/-
modify = db.Column(db.Float, nullable=False, server_default=db.text("0"))
"""
这里默认 default 值不需要在建表时候自动创建
所以不用使用 server_default 方法，动态生成对应值即可.
"""
replenishment = db.Column(
db.Float,
nullable=False,
default=cal_replenishment,
onupdate=cal_replenishment,
)
inventory_turnover_days = db.Column(
db.Float, nullable=False, default=default_inventory_turnover_days)
