class Curriculums(db.Model):
__tablename__ = "curriculums"
cid = db.Column(db.Integer, primary_key=True, comment="课程id")
cname = db.Column(db.String(60), nullable=False, comment="课程名字")
aid = db.Column(db.Integer,
db.ForeignKey("accounts.aid"),
comment="外键 课程老师id")
price = db.Column(db.Float(10, 2), default=0.0, comment=" 价格")
info = db.Column(db.Text, comment=" 课程介绍")
cimage = db.Column(db.String(250), comment="课程封面 阿里云oos直传")
create_at = db.Column(db.DateTime,
default=datetime.datetime.now(),
comment="创建时间")
delete_at = db.Column(db.DateTime, comment="删除时间")
admin_id = db.Column(db.Integer,
db.ForeignKey("admins_user.aid"),
comment="操作员工id")
open_at = db.Column(db.DateTime, comment="操作时间")
catalogs = db.relationship(Catalog, backref="curriculum")
comments = db.relationship(CurriculumComments, backref="curriculum")
# purchase_records = db.relationship(ShoppingCarts,backref="curriculum")
_use_collections = db.relationship(Use_collections, backref="_curriculum_")
def __repr__(self):
return "数据库{} {}-{}-{}-{}".format(self.__tablename__, self.aid,
self.cid, self.cname, self.cimage)
def __init__(self,
cid=None,
cname=None,
aid=None,
price=None,
info=None,
cimage=None,
admin_aid=None):
self.cname = cname
self.aid = aid
self.price = price
self.info = info
self.cimage = cimage
self.cid = cid
self.admin_aid = admin_aid
def completion_oss_img_url(self):
_img = get_img_oss_url(self.cimage, 86400 / 2)
return _img
def save(self):
self.create_at = datetime.datetime.now()
return self.is_commit()
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def up_commit(self):
try:
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
# 查询多个课程并返回dict
def query_curriculums_is_not_del(self, page=None, number=None):
if page == None:
page = 1
if number == None:
number = 10
items = {}
list_item = []
cs = self.query.filter_by(delete_at=None).paginate(
int(page), int(number), False)
for c in cs.items:
item = {
"aid": c.aid,
"cid": c.cid,
"cname": c.cname,
"price": str(c.price),
"info": c.info,
"cimage": c.completion_oss_img_url(),
"create_at": c.json_time_is_null(),
"delete_at": c.json_time_is_null()
}
list_item.append(item)
items["datas"] = list_item
items["len"] = len(cs.items)
items["pages"] = cs.pages
items["total"] = cs.total
return items
# 查询单个课程并返回dict
def query_also_serialize(self):
cc = self.query_curriculum_is_not_del()
if not cc:
return None
return cc.serialize_item()
def query_curriculum_is_not_del(self):
cc = self.query.filter_by(cid=self.cid, delete_at=None).first()
if cc == None:
return False
return cc
def serialize_item(self):
item = {
"aid": self.aid,
"nickname": self.json_relation_user_nickname_is_null(),
"cid": self.cid,
"cname": self.cname,
"price": str(self.price),
"info": self.info,
"cimage": self.completion_oss_img_url(),
"create_at": self.json_time_is_null(),
"delete_at": self.json_time_is_null()
}
return item
def json_relation_user_nickname_is_null(self):
if self.user == None:
return None
return self.user.nickname
def json_time_is_null(self):
if self.create_at == None:
return None
return self.create_at.strftime('%Y-%m-%d %H:%M:%S')
def query_modify_curriculum_people(self):
c = self.query.filter_by(cid=self.cid, delete_at=None).first()
if c == None:
return None
return c
def modify_curriculum_info(self, name, price, info, cimage):
if len(name) != 0:
self.cname = name
if len(price) != 0:
self.price = price
if len(info) != 0:
self.info = info
if len(cimage) != 0:
self.cimage = cimage
return self.up_commit()
def recommend_curriculums(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
currents = self.query.filter_by(aid=self.aid).paginate(
int(page), int(number), False)
items = {}
list_item = []
for c in currents.items:
list_item.append(c.serialize_item())
items["datas"] = list_item
items["len"] = len(currents.items)
items["pages"] = currents.pages
items["total"] = currents.total
return items
def is_identity(self, aid=None):
cc = self.query.filter_by(cid=self.cid).first()
if cc == None:
return False
return int(cc.aid) == int(aid)
def del_curriculums(self):
c = self.query.filter_by(cid=self.cid).first()
c.delete_at = datetime.datetime.now()
return c.up_commit()
def query_del_videos(self, page=1, number=20):
if page == None:
page = 1
if number == None:
number = 10
ccs = self.query.filter(Curriculums.aid == self.aid,
Curriculums.delete_at != None).paginate(
int(page), int(number), False)
items = {}
list_item = []
for cc in ccs.items:
list_item.append(cc.serialize_item())
items['datas'] = list_item
items['len'] = len(ccs.items)
items["pages"] = ccs.pages
items["total"] = ccs.total
return items
def recovery_curriculum(self):
cc = self.query.filter_by(cid=self.cid, aid=self.aid).first()
if cc == None:
return False
cc.delete_at = None
return cc.up_commit()
def get_purchase_records(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
c = self.query.filter_by(aid=self.aid).first()
sql = "select u.aid as uid,u.nickname as nk,u.status as st,c.aid as c_aid,c.cname as name,c.price as price,c.cimage as img,c.create_at as at " \
"from " \
"shopping_carts as shop " \
"join " \
"accounts as u " \
"on " \
"u.aid = shop.aid " \
"join curriculums as c " \
"on " \
"shop.cid = c.cid " \
"where shop.cid in (select cid from curriculums where aid = {}) limit {},{}}".format(c.aid,page-1,page*number)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
# order 默认查询排序 hot为(购买)热门排序,new 为最新排序
def query_like_field_all(self, key, page=1, number=20, order=None):
if key == None:
key = ""
if page == None or page <= 0:
page = 1
if number == None or number <= 0:
number = 10
items = {}
list_item = []
if order == "hot":
results = self.sql_order_hot_results(key=key,
page=page,
number=number)
for result in results:
list_item.append(result)
count = int(self.sql_search_hot_key_data_total(key=key))
items["pages"] = int(count / number)
items["len"] = results.__len__()
items["total"] = count
elif order == "new":
results = self.sql_order_new_results(key=key,
page=page,
number=number)
for result in results:
list_item.append(result)
count = int(self.sql_search_new_key_data_total(key=key))
items["pages"] = int(count / number)
items["len"] = results.__len__()
items["total"] = count
else:
results = self.order_default_results(key=key,
page=page,
number=number)
for result in results.items:
list_item.append(result.serialize_item())
items["len"] = len(results.items)
items["pages"] = results.pages
items["total"] = results.total
items["datas"] = list_item
return items
def order_default_results(self, key, page, number):
results = self.query.filter(
Curriculums.cname.like("%" + key + "%")).paginate(
int(page), int(number), False)
return results
def sql_order_hot_results(self, key, page, number):
sql = """
SELECT
count(*) AS count ,sp.cid AS s_cid ,c.*
FROM
shopping_carts AS sp
join
curriculums AS c
ON
sp.cid = c.cid
WHERE
c.delete_at IS NULL AND c.cname like '%{}%'
GROUP BY s_cid
ORDER BY s_cid DESC
limit {},{}
""".format(key, (page - 1) * number, number)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results, "cimage")
return items
def sql_order_new_results(self, key, page, number):
sql = """
SELECT
*
FROM
curriculums as c
WHERE
delete_at IS NULL AND cname LIKE '%{}%'
ORDER BY create_at
DESC limit {},{}
""".format(key, (page - 1) * number, number)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results, "cimage")
return items
def sql_search_new_key_data_total(self, key):
sql = """
SELECT count(*) AS total FROM curriculums AS c WHERE c.delete_at IS NULL AND c.cname LIKE '%{}%'
""".format(key)
results = db.session.execute(sql).fetchall()
_total = sql_result_to_dict(results)
return _total[0].get("total")
def sql_search_hot_key_data_total(self, key):
sql = """
SELECT
count(distinct c.cid) AS total
FROM
shopping_carts AS sp
JOIN
curriculums AS c
ON
sp.cid = c.cid
WHERE
c.delete_at is null and c.cname LIKE '%{}%'
""".format(key)
results = db.session.execute(sql).fetchall()
_total = sql_result_to_dict(results)
return _total[0].get("total")
def get_arbitrarily_curriculum_count(self, day):
# 处理一下,当前时间减去传递进来参数的天数时间,得到大于day_time的数据个数
if day == None:
day = 10
query_time = datetime.datetime.now() - datetime.timedelta(
days=int(day))
count = self.query.filter(Curriculums.create_at >= query_time).count()
return {"count": count}
def get_arbitrarily_curriculum_is_del_count(self, day):
if day == None:
day = 10
query_time = datetime.datetime.now() - datetime.timedelta(
days=int(day))
count = self.query.filter(Curriculums.create_at >= query_time,
Curriculums.delete_at != None).count()
return {"count": count}
def get_day_up_curriculums_count(self, day=10):
sql = "select " \
"DATE_FORMAT(c.create_at,'%Y-%m-%d') as dateDay,count(*) as countDay " \
"from " \
"curriculums as c " \
"group by dateDay order by dateDay desc limit 0,{}".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def get_curriculums(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
cs = self.query.filter().paginate(int(page), int(number), False)
items = {}
list_item = []
for c in cs.items:
list_item.append(c.serialize_item())
items['datas'] = list_item
items['len'] = len(cs.items)
items["pages"] = cs.pages
items["total"] = cs.total
return items
def get_del_curriculums(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
cs = self.query.filter(Curriculums.delete_at != None).paginate(
int(page), int(number), False)
items = {}
list_item = []
for c in cs.items:
item = {
"aid": c.aid,
"nickname": c.json_relation_user_nickname_is_null(),
"cid": c.cid,
"cname": c.cname,
"price": str(c.price),
"info": c.info,
"cimage": c.completion_oss_img_url(),
"create_at": c.json_time_is_null(),
"delete_at": c.json_time_is_null(),
"admin_aid": c.admin_id,
"open_at": c.json_time_is_null()
}
list_item.append(item)
items['datas'] = list_item
items['len'] = len(cs.items)
items["pages"] = cs.pages
items["total"] = cs.total
return items
def admin_del_video(self, admin_aid):
c = self.query.filter_by(cid=self.cid).first()
if c == None:
return False
c.delete_at = datetime.datetime.now()
c.admin_id = admin_aid
c.open_at = datetime.datetime.now()
return c.up_commit()
def admin_adopt_video(self, admin_aid):
c = self.query.filter_by(cid=self.cid).first()
print("\n\n\n", c)
if c == None:
return False
c.delete_at = None
c.admin_id = admin_aid
c.open_at = datetime.datetime.now()
return c.up_commit()
def get_curriculums_count(self):
count = self.query.filter(Curriculums.delete_at == None).count()
return {"count": count}
class Admin_Users(db.Model):
__tablename__ = "admins_user"
aid = db.Column(db.Integer, primary_key=True, comment="内部员工id")
username = db.Column(db.String(20), comment="账号")
pswd = db.Column(db.String(255), comment="密码")
status = db.Column(db.Integer,
comment="身份状态 0为未激活,1激活成功可使用,9离职(其他)") # tinyint
create_at = db.Column(db.DateTime, default=datetime.datetime.now())
r_id = db.Column(db.Integer,
db.ForeignKey("admin_roles.r_id"),
comment="权限id")
status_lever_1 = 0
status_lever_2 = 1
status_lever_3 = 9
default_pswd = "1234567890"
def __repr__(self):
return "数据库{}".format(self.__tablename__)
def __init__(self,
aid=None,
username=None,
pswd=None,
status=None,
r_id=None):
self.aid = aid
self.username = username
if status == None:
self.pswd = self.default_pswd
if status == None:
self.status = self.status_lever_1
self.create_at = datetime.datetime.now()
if r_id:
self.r_id = 1
def serializetion_json(self):
item = {
"aid": self.aid,
"username": self.username,
"status": self.status,
"ct": self.exis_time_is_null(),
"rid": self.r_id
}
return item
def exis_time_is_null(self):
if self.create_at == None:
return ""
return self.create_at.strftime("%Y-%m-%d %H:%M:%S")
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def up_commit(self):
try:
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def save(self):
if self.r_id == None:
self.r_id = 1
self.set_pswd(self.pswd)
return self.is_commit()
def get_user_activation(self):
u = self.query.filter_by(username=self.username,
status=self.status_lever_2).first()
if u == None:
return None
return u
def get_user_unactivation(self):
u = self.query.filter_by(username=self.username,
status=self.status_lever_1).first()
if u == None:
return None
return u
def get_user_aid_unactivation(self):
u = self.query.filter_by(aid=self.aid,
status=self.status_lever_1).first()
if u == None:
return None
return u
def get_user_aid_activation(self):
u = self.query.filter_by(aid=self.aid,
status=self.status_lever_2).first()
if u == None:
return None
return u
def check_login_user_pswd(self, input_pswd):
u = self.get_user_activation()
if u == None:
return u, False
return u, u.check_pswd(input_pswd)
def modify_pswd(self, now_pswd):
self.set_pswd(now_pswd)
return self.up_commit()
def exis_activation_user_status(self):
list_status = [
self.status_lever_2, self.status_lever_3, self.status_lever_1
]
return self.status in list_status
def modift_auth_status(self, r_id):
if int(r_id) not in [1, 2, 3]:
return False
if int(r_id) == self.r_id:
return False
self.r_id = r_id
return self.up_commit()
def get_unactivation_accounts(self, page=None, number=None, r_id=None):
if page == None:
page = 1
if number == None:
number = 10
if r_id == None:
r_id = 9
elif r_id == 9:
r_id = 10
us = self.query.filter(Admin_Users.status == self.status_lever_1,
Admin_Users.r_id < r_id).paginate(
int(page), int(number), False)
items = {}
list_item = []
for u in us.items:
list_item.append(u.serializetion_json())
items["datas"] = list_item
items["pages"] = us.pages
items["total"] = us.total
items["len"] = len(us.items)
return items
def get_leave_accounts(self, page=None, number=None, r_id=None):
if page == None:
page = 1
if number == None:
number = 10
if r_id == None:
r_id = 1
elif r_id == 9:
r_id = 10
us = self.query.filter(Admin_Users.status == self.status_lever_3,
Admin_Users.r_id < r_id).paginate(
int(page), int(number), False)
items = {}
list_item = []
for u in us.items:
list_item.append(u.serializetion_json())
items["datas"] = list_item
items["pages"] = us.pages
items["total"] = us.total
items["len"] = len(us.items)
return items
def get_all_accounts(self, page=None, number=None, r_id=None):
if page == None:
page = 1
if number == None:
number = 10
if r_id == None:
r_id = 1
elif r_id == 9:
r_id = 10
us = self.query.filter(Admin_Users.status == self.status_lever_2,
Admin_Users.r_id < r_id).paginate(
int(page), int(number), False)
items = {}
list_item = []
for u in us.items:
list_item.append(u.serializetion_json())
items["datas"] = list_item
items["pages"] = us.pages
items["total"] = us.total
items["len"] = len(us.items)
return items
def get_all_unaccounts(self, page=None, number=None, r_id=None):
if page == None:
page = 1
if number == None:
number = 10
if r_id == None:
r_id = 1
elif r_id == 9:
r_id = 10
us = self.query.filter(Admin_Users.status == self.status_lever_1,
Admin_Users.r_id < r_id).paginate(
int(page), int(number), False)
items = {}
list_item = []
for u in us.items:
list_item.append(u.serializetion_json())
items["datas"] = list_item
items["pages"] = us.pages
items["total"] = us.total
items["len"] = len(us.items)
return items
def exis_in_default_status(self):
list_status = [
self.status_lever_1, self.status_lever_2, self.status_lever_3
]
return self.status in list_status
def set_status_lever1(self):
self.status = self.status_lever_1
def set_status_lever2(self):
self.status = self.status_lever_2
def set_status_lever3(self):
self.status = self.status_lever_3
def get_status_lever(self):
return self.status
def set_username(self, username):
self.username = username
def get_username(self):
return self.username
def set_create_At(self):
self.create_at = datetime.datetime.now()
def get_create_At(self):
return self.create_at.strftime('%Y-%m-%d %H:%M:%S')
def set_pswd(self, pswd):
self.pswd = encryption(pswd)
def check_pswd(self, input):
return check_pswd(self.pswd, input)
def get_rid(self):
return self.r_id
def set_rid(self, rid):
self.r_id = rid
def get_aid(self):
return self.aid
def set_aid(self):
pass
class Account(db.Model):
__tablename__ = "accounts"
aid = db.Column(db.Integer, primary_key=True, comment="用户id")
nickname = db.Column(db.String(20),
nullable=False,
unique=True,
comment="昵称")
username = db.Column(db.String(20),
nullable=False,
unique=True,
comment="账号")
pswd = db.Column(db.String(255), nullable=False, comment="密码")
status = db.Column(db.Integer, comment="身份状态")
info = db.Column(db.TEXT, comment="一些额外的信息")
create_at = db.Column(db.DateTime,
default=datetime.datetime.now(),
comment="创建时间")
admin_id = db.Column(db.Integer,
db.ForeignKey("admins_user.aid"),
comment="操作员工id")
open_at = db.Column(db.DateTime, comment="操作时间")
# sqlalchemy orm特有的关系条件,不存在数据库表中,只是在存在实例中
# 第一个参数为对应模型的class类名,第二个参数在对应的类中生成一个属性,关联到这个表
# 这种关系只存在 一对多的 '一' 中
# PS 如果多个模型不在同一个文件中会发生错误,"找不到模型" 所以import 模型
curriculum = db.relationship(Curriculums, backref="user", lazy="dynamic")
comments = db.relationship(CurriculumComments,
backref="user",
lazy="dynamic")
extracts = db.relationship(Extracts, backref="user", lazy="dynamic")
money = db.relationship(Money, backref="user", lazy="dynamic")
purchases = db.relationship(Purchases, backref="user", lazy="dynamic")
shops = db.relationship(ShoppingCarts, backref="user", lazy="dynamic")
use_collections = db.relationship(Use_collections,
backref="_user",
lazy="dynamic")
DefaultUserAccountStatus = 0 # 用户状态 0为未注册用户
RegisteredUsersTeacherStatus = 1 # 注册用户(老師)
RegisteredUsersStudentStatus = 2 # 注册用户(學生)
BannedUsersStatus = 10 # 封禁用户
def __init__(self,
nickname=None,
username=None,
pswd=None,
info=None,
aid=None):
self.nickname = nickname
self.username = username
self.pswd = pswd
self.status = self.RegisteredUsersStudentStatus
self.info = info
self.aid = aid
def __repr__(self):
return "数据库{} {} --- {} ----- {}".format(self.__tablename__,
self.nickname, self.username,
self.aid)
def EncryptionPassword(self):
self.pswd = encryption(self.pswd) # 简单的加密,没有加盐值
@classmethod
def SetEncryptionPassword(self, pswd):
return encryption(pswd)
def CheckPassword(self, pswd):
return check_pswd(self.pswd, pswd)
# 学生注册
def registryStudentAccount(self):
self.EncryptionPassword()
return self.is_commit()
# 老师注册
def registryTeacherAccount(self):
self.status = self.RegisteredUsersTeacherStatus
self.EncryptionPassword()
return self.is_commit()
# 修改信息
def modify_user_info(self, nickname, info):
if len(nickname) != 0:
self.nickname = nickname
if len(info) != 0:
self.info = info
return self.up_commit()
# 修改密码
def modify_pswd(self, old, new):
if not self.CheckPassword(old):
return False
self.pswd = self.SetEncryptionPassword(new)
return self.up_commit()
# commit
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def up_commit(self):
try:
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
# 是否为老师身份
def is_UsersTeacherStatus(self):
return self.status == self.RegisteredUsersTeacherStatus
# 是否为学生身份
def is_UsersStudentStatus(self):
return self.status == self.RegisteredUsersStudentStatus
def serializetion_item(self):
item = {
"aid": self.aid,
"nickname": self.nickname,
"username": self.username,
"info": self.info,
"status": self.status,
"create_at": self.exis_time_is_null()
}
return item
def exis_time_is_null(self):
if self.create_at == None:
return ""
return self.create_at.strftime('%Y-%m-%d %H:%M:%S')
def query_Teachers(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
teachers = self.query.filter_by(
status=self.RegisteredUsersTeacherStatus).paginate(
int(page), int(number), False)
items = {}
list_item = []
for teacher in teachers.items:
list_item.append(teacher.serializetion_item())
items["datas"] = list_item
items["len"] = len(teachers.items)
items["nexts"] = teachers.pages
items["total"] = teachers.total
return items
def get_aid_user_to_serializetion(self):
u = self.query.filter(Account.aid == self.aid).first()
if u == None:
return ""
return u.serializetion_item()
def get_aid_user(self):
u = self.query.filter(Account.aid == self.aid).first()
if u == None:
return None
return u
def prohibit_user(self, admin_aid):
u = self.get_aid_user()
if u == None:
return False
u.status = self.BannedUsersStatus
u.admin_id = admin_aid
u.open_at = datetime.datetime.now()
return u.up_commit()
def reduction_prohibit_user(self, admin_aid):
u = self.query.filter_by(aid=self.aid).first()
if u == None:
return False
u.status = self.RegisteredUsersStudentStatus
u.admin_id = admin_aid
u.open_at = datetime.datetime.now()
return u.up_commit()
def get_prohibit_users(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
users = self.query.filter(
Account.status == self.BannedUsersStatus).paginate(
int(page), int(number), False)
items = {}
list_item = []
for user in users.items:
list_item.append(user.serializetion_item())
items["datas"] = list_item
items["len"] = len(users.items)
items["nexts"] = users.pages
items["total"] = users.total
return items
def get_aid_is_UsersTeacherStatus(self):
u = self.query.filter_by(aid=self.aid).first()
if u == None:
return None
return u.is_UsersTeacherStatus()
def get_day_registry_count(self, day=10):
sql = "select " \
"DAte_format(u.create_at,'%Y-%m-%d') as dateDay,count(*) as countDay " \
"from accounts as u where u.status != 0 and u.status !=10 " \
"group by dateDay order by dateDay desc " \
"limit 0,{}".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def get_registry_counts(self):
sql = """
select count(*) as count from accounts
"""
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items[0]
def get_users(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
users = self.query.filter().paginate(int(page), int(number), False)
items = {}
list_item = []
for user in users.items:
list_item.append(user.serializetion_item())
items["datas"] = list_item
items["len"] = len(users.items)
items["nexts"] = users.pages
items["total"] = users.total
return items
class CurriculumComments(db.Model):
__tablename__ = "curriculum_comments"
id = db.Column(db.Integer, primary_key=True, comment="主键")
cid = db.Column(db.Integer,
db.ForeignKey("curriculums.cid"),
comment="外键 课程id") # primary_key=True
aid = db.Column(db.Integer,
db.ForeignKey("accounts.aid"),
comment="外键 用户id") # primary_key=True
number = db.Column(db.Integer, comment="评价分数")
comment = db.Column(db.String(300), comment="评价")
create_at = db.Column(db.DateTime, default=datetime.datetime.utcnow())
delete_at = db.Column(db.DateTime)
admin_id = db.Column(db.Integer,
db.ForeignKey("admins_user.aid"),
comment="操作员工id")
open_at = db.Column(db.DateTime, comment="操作时间")
def __repr__(self):
return "数据库{}".format(self.__tablename__)
def __init__(self, cid=None, aid=None, number=None, comment=None, id=None):
self.cid = cid
self.aid = aid
self.number = number
self.comment = comment
self.id = id
def get_cid_comment_all(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
comments = self.query.filter_by(cid=self.cid, delete_at=None).paginate(
int(page), int(number), False)
items = {}
list_item = []
for comment in comments.items:
item = comment.serializetion_item()
list_item.append(item)
items["datas"] = list_item
items["len"] = len(comments.items)
items["pages"] = comments.pages
items["total"] = comments.total
return items
def get_aid_comment_all(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
comments = self.query.filter_by(aid=self.aid, delete_at=None).paginate(
int(page), int(number), False)
items = {}
list_item = []
for comment in comments.items:
list_item.append(comment.serializetion_item())
items["datas"] = list_item
items["len"] = len(comments.items)
items["pages"] = comments.pages
items["total"] = comments.total
return items
def serializetion_item(self):
item = {
"id": self.id,
"aid": self.aid,
"cid": self.cid,
"name": self.user.nickname,
"c_name": self.curriculum.cname,
"number": self.number,
"comment": self.comment,
"ct": self.serializetion_time_json_is_null(self.create_at),
"dt": self.serializetion_time_json_is_null(self.delete_at)
}
return item
def serializetion_time_json_is_null(self, time):
if time == None:
return ""
if isinstance(time, datetime.datetime) or isinstance(
time, datetime.time):
return time.strftime('%Y-%m-%d %H:%M:%S')
return ""
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def up_commit(self):
try:
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def save(self):
if self.number == None:
self.number = 0
self.create_at = datetime.datetime.utcnow()
return self.is_commit()
def query_user_comments(self, page=1, number=20):
if page == None:
page = 1
if number == None:
number = 10
comments = self.query.filter_by(aid=self.aid, delete_at=None).paginate(
int(page), int(number), False)
items = {}
list_item = []
for comment in comments.items:
list_item.append(comment.serializetion_item())
items["datas"] = list_item
items["len"] = len(comments.items)
items["pages"] = comments.pages
items["total"] = comments.total
return items
def del_comment(self):
comment = self.query.filter_by(id=self.id, aid=self.aid).first()
comment.delete_at = datetime.datetime.utcnow()
return comment.up_commit()
def get_commnets(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
comments = self.query.filter().paginate(int(page), int(number), False)
items = {}
list_item = []
for comment in comments.items:
item = {
"id": comment.id,
"aid": comment.aid,
"cid": comment.cid,
"name": comment.user.nickname,
"c_name": comment.curriculum.cname,
"number": comment.number,
"comment": comment.comment,
"ct":
comment.serializetion_time_json_is_null(comment.create_at),
"dt":
comment.serializetion_time_json_is_null(comment.delete_at)
}
list_item.append(item)
items["datas"] = list_item
items["len"] = len(comments.items)
items["pages"] = comments.pages
items["total"] = comments.total
return items
def admin_del_comment(self, admin_aid):
comment = self.query.filter_by(id=self.id).first()
if comment == None:
return False
comment.delete_at = datetime.datetime.now()
comment.open_at = datetime.datetime.now()
comment.admin_id = admin_aid
return comment.up_commit()
def admin_recovery_comment(self, admin_aid):
comment = self.query.filter_by(id=self.id).first()
comment.delete_at = None
comment.open_at = datetime.datetime.now()
comment.admin_id = admin_aid
return comment.up_commit()
class Catalog(db.Model):
__tablename__ = "catalog"
id = db.Column(db.Integer, primary_key=True, comment="目录id")
cat_id = db.Column(db.Integer,
db.ForeignKey("curriculums.cid"),
comment="外键 课程id")
name = db.Column(db.String(50), nullable=False, comment="课程目录名称")
url = db.Column(db.String(255), nullable=False, comment="目录地址")
create_at = db.Column(db.DateTime,
default=datetime.datetime.utcnow(),
comment="一个课程多个目录,根据时间排序")
delete_at = db.Column(db.DateTime, comment="删除时间,软删除")
def __repr__(self):
return "数据库{}".format(self.__tablename__)
def __init__(self, id=None, cat_id=None, name=None, url=None):
self.id = id
self.cat_id = cat_id
self.name = name
self.url = url
self.create_at = datetime.datetime.now()
def query_catalogs(self):
catalogs = self.query.filter_by(cat_id=self.cat_id,
delete_at=None).all()
items = {}
list_time = []
for catalog in catalogs:
list_time.append(catalog.serializetion_itme())
items["datas"] = list_time
items["len"] = len(catalogs)
return items
def query_catalog_object(self):
cat = self.query.filter_by(cat_id=self.cat_id,
id=self.id,
delete_at=None).first()
if cat == None:
return None
return cat
def del_catalog(self):
self.delete_at = datetime.datetime.now()
return self.up_commit()
def up_commit(self):
try:
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def completion_oss_img_url(self):
_url = get_img_oss_url(self.url, 86400 / 2)
return _url
def serializetion_itme(self):
item = {
"id": self.id,
"cat_id": self.cat_id,
"name": self.name,
"url": self.completion_oss_img_url(),
"ct": self.serializetion_time_json_is_null(self.create_at),
"dt": self.serializetion_time_json_is_null(self.delete_at)
}
return item
def serializetion_time_json_is_null(self, time):
if time == None:
return ""
if isinstance(time, datetime.datetime) or isinstance(
time, datetime.time):
return time.strftime('%Y-%m-%d %H:%M:%S')
return ""
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
class Money(db.Model):
__tablename__ = "money"
aid = db.Column(db.Integer,
db.ForeignKey("accounts.aid"),
primary_key=True,
comment="外键用户表id,唯一")
money = db.Column(db.Float(10, 2), default=0.00, comment="金钱")
version = db.Column(db.Integer, comment="乐观锁,版本控制")
def __repr__(self):
return "数据库{} {}_{}_{}".format(self.__tablename__, self.aid,
self.money, self.version)
def __init__(self, aid=None, money=None):
self.aid = aid
self.money = money
def add_user_money(self, add_money):
m = self.query.filter_by(aid=self.aid).first()
if m == None:
ok = self.is_query_is_null_new_account()
if not ok:
return False
up_money = float(m.money) + float(add_money)
m.money = up_money
m.version += 1
return m.up_commit()
def is_query_is_null_new_account(self):
self.aid = self.aid
self.money = 0.00
self.version = 1
return self.is_commit()
# commit
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def up_commit(self):
try:
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
# test_ commit
def test_with_is_commit(self):
with db.session.commit():
pass
def get_user_money(self):
m = self.query.filter_by(aid=self.aid).first()
return m.money
class ShoppingCarts(db.Model):
__tablename__ = "shopping_carts"
aid = db.Column(db.Integer,
db.ForeignKey("accounts.aid"),
primary_key=True,
comment="外键 用户id")
cid = db.Column(db.Integer,
db.ForeignKey("curriculums.cid"),
primary_key=True,
comment="外键 课程id")
number = db.Column(db.Integer, default=1)
price = db.Column(db.Float(10, 2), default=0.00)
create_at = db.Column(db.DateTime,
default=datetime.datetime.utcnow(),
comment="创建时间")
_curriculum = db.relationship(Curriculums, backref="shop")
# user = db.relationship(Account,backref="shops")
def __repr__(self):
return "数据库{} {}_{}_{}_{}".format(self.__tablename__, self.cid,
self.aid, self.number, self.price)
def __init__(self, aid=None, cid=None, price=None):
self.aid = aid
self.cid = cid
self.number = 1
self.price = price
self.create_at = datetime.datetime.now()
# 查询是否购买或者价格为0
def is_record(self):
shop = self.query.filter_by(aid=self.aid, cid=self.cid).first()
if shop == None:
cu = Curriculums.query.filter_by(cid=self.cid).first()
if cu.price <= float(0):
return True
else:
if shop.number != 1:
if not shop.curriculum.price <= float(0):
return False
return True
elif shop.number == 1:
return True
def get_curriculum__catalog(self):
_catalogs = Catalog.query.filter_by(cat_id=self.cid).all()
items = {}
list_time = []
for catalog in _catalogs:
list_time.append(catalog.serializetion_itme())
items["datas"] = list_time
items["len"] = len(_catalogs)
return items
def is_purchase(self):
shop = self.query.filter(ShoppingCarts.aid == self.aid,
ShoppingCarts.cid == self.cid).first()
if shop == None:
return False
return shop.number == 1
def save(self):
self.number = 1
self.create_at = datetime.datetime.now()
return self.is_commit()
# commit
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def serialize_item(self):
item = {
"aid": self.aid,
"nickname": self.aid.user.nickname,
"cid": self.cid,
"number": self.number,
"price": self.price,
"create_at": self.create_at
}
return item
def get_purchase_curriculums(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
items = {}
list_item = []
shops = self.query.filter_by(aid=self.aid).paginate(
int(page), int(number), False)
for shop in shops.items:
list_item.append(shop._curriculum.serialize_item())
items["datas"] = list_item
items["len"] = len(shops.items)
items["nexts"] = shops.pages
items["total"] = shops.total
return items
def get_days_shop_effective_sum(self, day):
sql = """
select
date_format(s.create_at,'%Y-%m-%d %H:%i:%S')as dateDay,sum(s.price)as moneyDay
FROM shopping_carts as s
group by dateDay
order by dateDay desc limit 0,{}
""".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def get_month_shop_effective_sum(self, month):
sql = """
select
date_format(s.create_at,'%Y-%m')as dateMonth,sum(s.price)as moneyMonth
FROM shopping_carts as s
group by dateMonth
order by dateMonth desc limit 0,{}
""".format(month)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def get_monerys(self):
sql = """
select sum(s.price)as moneys FROM shopping_carts as s
"""
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items[0]
class Use_collections(db.Model):
__tablename__ = "use_collections"
aid = db.Column(db.Integer,db.ForeignKey("accounts.aid"),primary_key=True, comment="外键 用户id")
cid = db.Column(db.Integer, db.ForeignKey("curriculums.cid"),primary_key=True, comment="外键 课程id")
create_at = db.Column(db.DateTime, default=datetime.datetime.now(), comment="一个课程多个目录,根据时间排序")
delete_at = db.Column(db.DateTime, comment="删除时间,软删除")
curriculums = db.relationship("Curriculums", backref="use_collections")
def __repr__(self):
return "数据库{}".format(self.__tablename__)
def __init__(self,aid=None,cid=None):
self.aid = aid
self.cid = cid
self.create_at = datetime.datetime.now()
def add_use_collection(self):
return self.is_commit()
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
def up_commit(self):
try:
db.session.commit()
return True
except Exception as e:
db.session.rollback()
return False
# 查询收藏课程
# todo 测试一下
def query_use_curriculms(self):
datas = {}
list_items = []
for cc in self.curriculums:
list_items.append(cc)
datas["data"] = list_items
# 查询个数和总个数
total = 0
datas["total"] = total
current_number = 0
datas["current_number"] = current_number
return datas
def del_collection(self):
c = self.query.filter_by(aid=self.aid,cid=self.cid).first()
c.delete_at = datetime.datetime.now()
return c.up_commit()
def get_user_collections(self,page=1,number=10):
if page == None:
page = 1
if number == None:
number = 10
colls = self.query.filter_by(aid=self.aid,delete_at=None).paginate(int(page),int(number),False)
items = {}
list_item = []
for c in colls.items:
list_item.append(c._curriculum_.serialize_item())
items["datas"] = list_item
items["len"] = len(colls.items)
items["pages"] = colls.pages
items["total"] = colls.total
return items
def serializetion_json(self):
item = {
"aid":self.aid,
"cid": self.cid,
"name":self._user.nickname,
"c_title":self._curriculum_.cname,
"create_at": self.serializetion_time_json_is_null(self.create_at),
"delete_at": self.serializetion_time_json_is_null(self.delete_at),
}
return item
def serializetion_time_json_is_null(self,time):
if time == None:
return ""
if isinstance(time,datetime.datetime) or isinstance(time,datetime.time):
return time.strftime('%Y-%m-%d %H:%M:%S')
return ""
class Extracts(db.Model):
__tablename__ = "extracts"
eid = db.Column(db.Integer, primary_key=True, comment="外键 课程id")
create_at = db.Column(db.DateTime,
default=datetime.datetime.utcnow(),
comment="创建时间")
t_money = db.Column(db.Float(10, 2), comment="提取金额")
divide = db.Column(db.Float(10, 2), default=0.05, comment="站点分成,默认为5%")
actual_money = db.Column(db.Float(10, 2), comment="实际提成")
aid = db.Column(db.Integer,
db.ForeignKey("accounts.aid"),
comment="外键 用户id")
number = db.Column(db.String(35), comment="流水号")
status = db.Column(db.Integer, comment="转账交易状态 0 为显示转账但是未到达,1为转账成功,2为转账失败")
status_not_complete = 0
status_complete = 1
status_fail = 2
money_divide = 0.05
def __repr__(self):
return "数据库{}".format(self.__tablename__)
def __init__(self, aid=None, t_money=None, actual_money=None, number=None):
self.aid = aid
self.t_money = t_money
self.divide = self.money_divide
self.actual_money = actual_money
self.number = number
self.status = self.status_not_complete
self.create_at = datetime.datetime.utcnow()
def new_extract(self, aid=None, t_money=None, actual_money=None):
self.aid = aid
self.t_money = t_money
self.actual_money = actual_money
self.number = get_uuid()
def save(self, m):
_m = Money.query.filter_by(aid=self.aid).first()
_m.money = float(_m.money) - float(m)
_m.up_commit()
return self.is_commit()
def up_commit(self):
try:
db.session.commit()
return True
except Extracts as e:
db.session.rollback()
return False
def is_commit(self):
try:
db.session.add(self)
db.session.commit()
return True
except Extracts as e:
db.session.rollback()
return False
def get_number(self):
return self.number
def serializetion_item(self):
item = {
"aid": self.aid,
"eid": self.eid,
"t_money": str(self.t_money),
"divide": str(self.divide),
"actual_money": str(self.actual_money),
"number": str(self.number),
"status": self.status,
"create_at": self.json_create_at_is_null()
}
return item
def json_create_at_is_null(self):
if self.create_at == None:
return ""
return self.create_at.strftime('%Y-%m-%d %H:%M:%S')
def query_user_extracts(self, page=1, number=10):
if page == None:
page = 1
if number == None:
number = 10
exs = self.query.filter_by(aid=self.aid).paginate(
int(page), int(number), False)
items = {}
list_item = []
for ex in exs.items:
list_item.append(ex.serializetion_item())
items["datas"] = list_item
items["len"] = len(exs.items)
items["pages"] = exs.pages
items["total"] = exs.total
return items
def get_day_sum_money(self, day):
sql = """
select date_format(create_at,'%Y-%m-%d')as dateDay,sum(t_money) as moneyDay from extracts group by dateDay order by dateDay limit 0,{}
""".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items
def get_teacher_royalty_money(self, day):
sql = """
select date_format(create_at,'%Y-%m-%d') as dateDay ,sum(actual_money) as money_total from extracts group by dateDay order by dateDay desc limit 0,{}
""".format(day)
results = db.session.execute(sql).fetchall()
items = sql_result_to_dict(results)
return items