from sqlconnection import SqlCon
import numpy as np


class User:
    """
    用户的类，其基本属性包括：
    user_id（用户id，整数，数据库作为主键，自动增加，不为空）
    user_name（用户名，文本，不为空，不重复）
    user_card（用户工号，文本，不为空）
    user_status（用户的状态，文本，只有“有效”和“无效”两种状态）
    """

    _sql_table = 'user'

    def __init__(self, **kwargs):
        self.user_id = None
        self.user_name = None
        self.user_card = None
        self.user_status = None
        for key, value in kwargs.items():
            setattr(self, key, value)

    def user_register(self):
        """
        创建新用户
        :return:
        """
        self.user_status = '有效'
        if self.user_name and self.user_card and not self.user_id:
            sql = "INSERT INTO {} (user_name, user_card, user_status) VALUES ('{}', '{}', '{}')" \
                .format(User._sql_table, self.user_name, self.user_card, self.user_status)
            sql_con = SqlCon()
            sql_con.db_cursor.execute(sql)
            sql_con.db_con.commit()
        else:
            raise Exception('人名或卡号为空或者指定了loc_id!')

    def user_search(self):
        """
        通过用户id来搜索用户信息并更新类的属性
        :return:
        """
        sql = "SELECT * FROM {} WHERE user_id={}".format(User._sql_table, self.user_id)
        sql_con = SqlCon()
        sql_con.db_cursor.execute(sql)
        result = sql_con.db_cursor.fetchone()
        if result:
            self.user_id, self.user_name, self.user_card, self.user_status = result
        else:
            raise Exception('没有该用户！')

    @staticmethod
    def user_active():
        """
        有效状态下的用户信息
        :return: 列依次为user_id, user_name, user_card的数组
        """
        sql = "SELECT user_id, user_name, user_card FROM user WHERE user_status='有效'"
        sql_con = SqlCon()
        sql_con.db_cursor.execute(sql)
        results = sql_con.db_cursor.fetchall()
        return np.array(results)

    @staticmethod
    def user_all():
        """
        所有用户信息
        :return: 列依次为user_id, user_name, user_card的数组
        """
        sql = "SELECT user_id, user_name, user_card, user_status FROM user"
        sql_con = SqlCon()
        sql_con.db_cursor.execute(sql)
        results = sql_con.db_cursor.fetchall()
        return np.array(results)

    @staticmethod
    def user_update(args):
        """
        更新用户信息
        :param args: 传入4个数据的元组，依次包括user_name, user_card, user_status, user_id
        :return:
        """
        sql = "UPDATE {} SET user_name = '{}', user_card='{}', user_status = '{}' WHERE user_id = {}"\
            .format(User._sql_table, *args)
        sql_con = SqlCon()
        sql_con.db_cursor.execute(sql)
        sql_con.db_con.commit()