class admin_roles(db.Model):
    __tablename__ = "admin_roles"
    r_id = db.Column(db.Integer,primary_key=True,comment="角色id,表示员工权限,1普通员工,2组长?,3主管,9老板-- 123都有增删改查权限.3能注册12权限员工,9(我全都要)")
    r_name = db.Column(db.String(10), comment="角色名字")

    admin_users = db.relationship(Admin_Users, backref="role", lazy="dynamic")



    auth_lever1 = 1
    auth_lever2 = 2
    auth_lever3 = 3
    auth_lever4 = 9

    def __repr__(self):
        return "数据库{}".format(self.__tablename__)

    def __init__(self,r_id=None,r_name=None):
        self.r_id = r_id
        self.r_name = r_name if r_name == None else self.auth_lever1












    def get_rid(self):
        return self.r_id
    def set_rid(self):
        pass
    def set_name(self,name):
        self.r_name = name
    def get_name(self):
        return self.r_name
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 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 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 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 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 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