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