import pymysql


def db_get_user111(name,passwd):
    db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    try:

        sql = """select user_id,user_password from user"""
        entry1 = name.get()

        entry2 = passwd.get()

        cursor.execute(sql)
        results = cursor.fetchall()

        for row in results:
            uid=row[0]
            pwd=row[1]
            if entry1==uid and entry2==pwd:
                db.close()
                return 1
        return 0
    except:
        db.rollback()
        db.close()
        return 0

def db_get_food():
    db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")
    cursor = db.cursor()
    food = {}
    sql = """select id_food,category,name_food,price from food"""
    try:
        # 执行sql
        cursor.execute(sql)
        # 处理结果集
        results = cursor.fetchall()

        for row in results:
            food[str(row[0])]=[str(row[0]),row[1],row[2],str(row[3])]
        db.close()
        return food
    except Exception as e:
        # print(e)
        print('查询所有数据失败')
        db.rollback()
        db.close()
        return 0


def db_get_all_categories(category):
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()

    sql = """select category from categories"""
    try:
        category = category.get()
        cursor.execute(sql)
        results = cursor.fetchall()

        for row in results:
            if category == row[0]:
                return 1
        return 0
    except:
        print("wrong:db_get_all_categories")
        db.rollback()
        db.close()
        return 0


def db_get_add(id,category,name,price):
    db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")
    cursor = db.cursor()


    try:

        sql = """insert into food(id_food,category,name_food,introduction,price,url) values(%s,%s,%s,%s,%s,%s)"""
        value = (id, category, name, 'null', price, 'NULL')
        # 执行sql
        cursor.execute(sql,value)
        db.commit()


        db.close()
        return 1
    except Exception as e:
        print(e)
        db.rollback()
        db.close()
        return 0


def db_alter(name,price):
    db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")
    cursor = db.cursor()
    try:
        price = price.get()
        name = name.get()
        sql = """update food set price = %s where name_food = %s"""
        value = ( price , name )
        # 执行sql
        cursor.execute(sql,value)
        db.commit()

        db.close()
        return 1
    except Exception as e:
        print(e)
        db.rollback()
        db.close()
        return 0


def db_delete(name):
    db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")
    cursor = db.cursor()
    try:
        name = name.get()
        sql = """delete from food where name_food=%s"""
        #value = (name)
        # 执行sql
        cursor.execute(sql, name)
        db.commit()

        db.close()
        return 1
    except Exception as e:
        print(e)
        db.rollback()
        db.close()
        return 0

def db_add_member(member,name,sex,phone):
    db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")
    cursor = db.cursor()
    try:
        member=member.get()
        name = name.get()
        sex=sex.get()
        phone=phone.get()
        sql = """insert into member(id_member,name_member,points,sex,phone_num) values(%s,%s,0,%s,%s) """
        value = (member,name,sex,phone)
        # 执行sql
        cursor.execute(sql, value)
        db.commit()

        db.close()
        return 1
    except Exception as e:
        print(e)
        db.rollback()
        db.close()
        return 0