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