import tkinter as tk

from tkinter import messagebox, StringVar
from functools import partial
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, select, Date, update
metadata = MetaData()

engine = create_engine('sqlite:///sql_database:', echo=True)
conn = engine.connect()

btnCRUDROW = 1
ACCOUNTROW = 2
PASSWORDROW = 3
btnLOGINROW = 4



user_Info = {'admin':'pass01', 'manage':'pass02'}
product_data = []
class product:
    def __init__(self, name, price, effectivedate, origin):
        self.name = name
        self.price = price
        self.effectivedate = effectivedate
        self.origin = origin

    def create(self):
        pass
    def edit(self, name, stock, price, purchase, effectivedate, origin, returns):
        self.name = name
        self.stock = stock
        self.price = price
        self.purchase = purchase
        self.effectivedate = effectivedate
        self.origin = origin
        self.returns = returns
    def remove(self):
        pass


product_table = Table(
    'product_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    # Column('stock', String),
    Column('price', Integer),
    Column('effectivedate', String),
    Column('origin', String),
    # Column('returns', String),
)

purchase_table = Table(
    'purchase_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('product_id', None, ForeignKey('product_table.id')),
    Column('time', String),
    Column('count', Integer),
    Column('Manufacturers', String),

)

stock_table = Table(
    'stock_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('product_id', None, ForeignKey('product_table.id')),
    Column('count', Integer)
)

returns_table = Table(
    'returns_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('product_id', None, ForeignKey('product_table.id')),
    Column('time', String),
    Column('count', Integer),
    Column('money', String),
    Column('Manufacturers', String)
)

employee_table = Table(
    'employee_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('part_id', None, ForeignKey('part_table.id')),
    Column('branch_id', None, ForeignKey('branch_table.id')),
    Column('name', String),
    Column('gender', Integer)
)

part_table = Table(
    'part_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('number', Integer),
)

branch_table = Table(
    'branch_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('address', String),
    Column('phone', Integer)
)
leave_table = Table(
    'leave_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('employee_id', None, ForeignKey('employee_table.id')),
    Column('kind', String),
    Column('date', String)
)
attendance_table = Table(
    'attendance_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('employee_id', None, ForeignKey('employee_table.id')),
    Column('place', String),
    Column('date', String)
)

metadata.create_all(engine)

def open_main_window(old_page):
    # btn pixel
    X_PIXEL01 = 0
    X_PIXEL02 = 80
    X_PIXEL03 = 160
    Y_PIXEL01 = 20
    Y_PIXEL02 = 50
    Y_PIXEL03 = 80
    global G_user_name

    old_page.destroy()
    win_main = tk.Tk()
    win_main.title("main page")
    win_main.geometry('300x200')
    label_Lusername = tk.Label(win_main, text="User Name:").place(x=0,y=0)
    label_username = tk.Label(win_main,text=G_user_name).place(x=80, y=0)

    if G_user_name == 'admin':
        btn_open_page01 = tk.Button(win_main, text='products', command=lambda: select_product_window(win_main)).place(
            x=X_PIXEL01, y=Y_PIXEL01)
        btn_open_page02 = tk.Button(win_main, text='purchase', command=lambda: select_purchase_window(win_main)).place(
            x=X_PIXEL02, y=Y_PIXEL01)
        btn_open_page04 = tk.Button(win_main, text='returns', command=lambda: select_returns_window(win_main)).place(
            x=X_PIXEL01, y=Y_PIXEL02)
        btn_open_page05 = tk.Button(win_main, text='employee', command=lambda: select_employee_window(win_main)).place(
            x=X_PIXEL02, y=Y_PIXEL02)
        btn_open_page06 = tk.Button(win_main, text='part', command=lambda: select_part_window(win_main)).place(
            x=X_PIXEL03, y=Y_PIXEL02)
        btn_open_page07 = tk.Button(win_main, text='branch', command=lambda: select_branch_window(win_main)).place(
            x=X_PIXEL01, y=Y_PIXEL03)
        btn_open_page08 = tk.Button(win_main, text='leave', command=lambda: select_leave_window(win_main)).place(
            x=X_PIXEL02, y=Y_PIXEL03)
        btn_open_page09 = tk.Button(win_main, text='attendance',
                                    command=lambda: select_attendance_window(win_main)).place(x=X_PIXEL03, y=Y_PIXEL03)
    else:
        btn_open_page01 = tk.Button(win_main, text='products', command=lambda: select_product_window(win_main)).place(
            x=X_PIXEL01, y=Y_PIXEL01)
        btn_open_page02 = tk.Button(win_main, text='purchase', command=lambda: select_purchase_window(win_main)).place(
            x=X_PIXEL02, y=Y_PIXEL01)
        btn_open_page03 = tk.Button(win_main, text='stock', command=lambda: select_stock_window(win_main)).place(
            x=X_PIXEL03, y=Y_PIXEL01)
        btn_open_page04 = tk.Button(win_main, text='returns', command=lambda: select_returns_window(win_main)).place(
            x=X_PIXEL01, y=Y_PIXEL02)
        btn_open_page05 = tk.Button(win_main, text='employee', command=lambda: select_employee_window(win_main)).place(
            x=X_PIXEL02, y=Y_PIXEL02)
        btn_open_page06 = tk.Button(win_main, text='part', command=lambda: select_part_window(win_main)).place(
            x=X_PIXEL03, y=Y_PIXEL02)
        btn_open_page07 = tk.Button(win_main, text='branch', command=lambda: select_branch_window(win_main)).place(
            x=X_PIXEL01, y=Y_PIXEL03)
        btn_open_page08 = tk.Button(win_main, text='leave', command=lambda: select_leave_window(win_main)).place(
            x=X_PIXEL02, y=Y_PIXEL03)
        btn_open_page09 = tk.Button(win_main, text='attendance',
                                    command=lambda: select_attendance_window(win_main)).place(x=X_PIXEL03, y=Y_PIXEL03)

# def open_page01_window(old_page):
#     LABEL_YPIXEL = 20
#     NAME_XPIXEL = 0
#     STOCK_XPIXEL = 80
#     PRICE_XPIXEL = 160
#     PURCHASE_XPIXEL = 240
#     EFFECT_XPIXEL = 350
#     ORIGIN_XPIXEL = 430
#     RETURNS_XPIXEL = 500
#     line = 0
#     global product_data
#     old_page.destroy()
#     win_products = tk.Tk()
#     win_products.title("page01")
#     win_products.geometry('600x400')
#
#     label_Lusername = tk.Label(win_products, text="User Name:").place(x=0, y=0)
#     label_username = tk.Label(win_products, text=G_user_name).place(x=80, y=0)
#     label_Lname = tk.Label(win_products, text="Name").place(x=NAME_XPIXEL, y=LABEL_YPIXEL)
#     label_Lstock = tk.Label(win_products, text="Stock").place(x=STOCK_XPIXEL, y=LABEL_YPIXEL)
#     label_Lprice = tk.Label(win_products, text="Price").place(x=PRICE_XPIXEL, y=LABEL_YPIXEL)
#     label_Lpurchase = tk.Label(win_products, text="Purchase").place(x=PURCHASE_XPIXEL, y=LABEL_YPIXEL)
#     label_Leffectivedate = tk.Label(win_products, text="Date").place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL)
#     label_Lorigin = tk.Label(win_products, text="Origin").place(x=ORIGIN_XPIXEL, y=LABEL_YPIXEL)
#     label_Lreturns = tk.Label(win_products, text="Returns").place(x=RETURNS_XPIXEL, y=LABEL_YPIXEL)
#     btn_new_object = tk.Button(win_products, text='Insert', command=lambda: Product_insert_window(win_products)).pack(
#         side="bottom")
#     btn_open_main = tk.Button(win_products, text='return', command=lambda:open_main_window(win_products)).pack(
#         side="bottom")
#
#     selectsql_product = select([product_table])
#     result = conn.execute(selectsql_product)
#
#     for row in result:
#
#         # print('row:', row)
#         label_prd_name = tk.Label(win_products, text=row[1]).place(x=NAME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
#         label_prd_stock = tk.Label(win_products, text=row[2]).place(x=STOCK_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
#         label_prd_price = tk.Label(win_products, text=row[3]).place(x=PRICE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
#         label_prd_purchase = tk.Label(win_products, text=row[4]).place(x=PURCHASE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
#         label_prd_effectivedate = tk.Label(win_products, text=row[4]).place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
#         label_prd_origin = tk.Label(win_products, text=row[5]).place(x=ORIGIN_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
#         label_prd_returns = tk.Label(win_products, text=row[6]).place(x=RETURNS_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
#
#         line+=1


def select_product_window(old_page):
    LABEL_YPIXEL = 20
    NAME_XPIXEL = 0
    PRICE_XPIXEL = 130
    EFFECT_XPIXEL = 280
    ORIGIN_XPIXEL = 350
    EDIT_XPIXEL = 480
    REMOVE_XPIXEL = 530
    line = 0
    global product_data
    old_page.destroy()
    win_products = tk.Tk()
    win_products.title("product")
    win_products.geometry('600x400')

    label_Lusername = tk.Label(win_products, text="User Name:").place(x=0, y=0)
    label_username = tk.Label(win_products, text=G_user_name).place(x=80, y=0)
    label_Lname = tk.Label(win_products, text="Name").place(x=NAME_XPIXEL, y=LABEL_YPIXEL)
    label_Lprice = tk.Label(win_products, text="Price").place(x=PRICE_XPIXEL, y=LABEL_YPIXEL)
    label_Leffectivedate = tk.Label(win_products, text="Date").place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL)
    label_Lorigin = tk.Label(win_products, text="Origin").place(x=ORIGIN_XPIXEL, y=LABEL_YPIXEL)
    btn_new_object = tk.Button(win_products, text='Insert', command=lambda: Product_insert_window(win_products)).pack(
        side="bottom")
    btn_open_main = tk.Button(win_products, text='return', command=lambda:open_main_window(win_products)).pack(
        side="bottom")

    selectsql_product = select([product_table])
    result = conn.execute(selectsql_product)

    for row in result:
        label_prd_name = tk.Label(win_products, text=row[1]).place(x=NAME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_prd_price = tk.Label(win_products, text=row[2]).place(x=PRICE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_prd_effectivedate = tk.Label(win_products, text=row[3]).place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_prd_origin = tk.Label(win_products, text=row[4]).place(x=ORIGIN_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        btn_edit_prd = tk.Button(win_products, text='edit', command=partial(edit_product_window, win_products, row[0]))\
            .place(x=EDIT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        btn_delete_prd = tk.Button(win_products, text='delete', command=partial(delete_product, win_products, row[0])) \
            .place(x=REMOVE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        line+=1

def Product_insert_window(old_page):
    old_page.destroy()
    win_products_insert = tk.Tk()
    win_products_insert.title("Insert product")
    win_products_insert.geometry('450x300')

    label_Lname = tk.Label(win_products_insert, text="Name").grid(column=0, row=0)
    entry_name = tk.Entry(win_products_insert)
    entry_name.grid(column=1, row=0)
    label_Lprice = tk.Label(win_products_insert, text="price").grid(column=0, row=1)
    entry_price = tk.Entry(win_products_insert)
    entry_price.grid(column=1, row=1)
    label_Leffectivedate = tk.Label(win_products_insert, text="effectivedate").grid(column=0, row=2)
    entry_effectivedate = tk.Entry(win_products_insert)
    entry_effectivedate.grid(column=1, row=2)
    label_Lorigin = tk.Label(win_products_insert, text="origin").grid(column=0, row=3)
    entry_origin = tk.Entry(win_products_insert)
    entry_origin.grid(column=1, row=3)
    btn_insert = tk.Button(win_products_insert, text="insert", command= lambda:insert_product(win_products_insert,
        entry_name.get(), entry_price.get(), entry_effectivedate.get(), entry_origin.get())).place(x=200, y=250)
    btn_return = tk.Button(win_products_insert, text="return", command= lambda:select_product_window(win_products_insert))\
        .place(x=300, y=250)

def edit_product_window(old_page, input_id):
    print('id:', input_id)
    old_page.destroy()
    win_products_insert = tk.Tk()
    win_products_insert.title("Edit product")
    win_products_insert.geometry('450x300')

    selectsql_product = select([product_table]).\
        where(product_table.c.id == input_id)
    result = conn.execute(selectsql_product)
    print('result:', result)

    string_name = StringVar()
    string_price = StringVar()
    string_effectivedate = StringVar()
    string_origin = StringVar()

    for row in result:
        string_name.set(row[1])
        string_price.set(row[2])
        string_effectivedate.set(row[3])
        string_origin.set(row[4])

    label_Lname = tk.Label(win_products_insert, text="Name").grid(column=0, row=0)
    entry_name = tk.Entry(win_products_insert, textvariable = string_name)
    entry_name.grid(column=1, row=0)
    label_Lprice = tk.Label(win_products_insert, text="price").grid(column=0, row=1)
    entry_price = tk.Entry(win_products_insert, textvariable = string_price)
    entry_price.grid(column=1, row=1)
    label_Leffectivedate = tk.Label(win_products_insert, text="effectivedate").grid(column=0, row=2)
    entry_effectivedate = tk.Entry(win_products_insert, textvariable = string_effectivedate)
    entry_effectivedate.grid(column=1, row=2)
    label_Lorigin = tk.Label(win_products_insert, text="origin").grid(column=0, row=3)
    entry_origin = tk.Entry(win_products_insert, textvariable = string_origin)
    entry_origin.grid(column=1, row=3)


    btn_edit = tk.Button(win_products_insert, text="insert", command= lambda:edit_product(win_products_insert,
        entry_name.get(), entry_price.get(), entry_effectivedate.get(), entry_origin.get(), input_id)).place(x=200, y=250)
    btn_return = tk.Button(win_products_insert, text="return", command= lambda:select_product_window(win_products_insert))\
        .place(x=300, y=250)

def insert_product(old_page, name, price, effect, origin):
    global product_data
    test_product = product(name, price, effect, origin)

    selectsql_product = select([product_table])
    result = conn.execute(selectsql_product)
    print (result)
    id = 1
    for row in result:
        if  not row[0] < id:
            id = row[0]+1
    ins = product_table.insert().values(
        id= id,
        name = test_product.name,
        price = test_product.price,
        effectivedate = test_product.effectivedate,
        origin = test_product.origin)
    conn.execute(ins)
    select_product_window(old_page)

def edit_product(old_page, name, price, effect, origin, input_id):
    editsql_product = product_table.update().where(product_table.c.id == input_id).values(
        name = name,
        price = price,
        effectivedate = effect,
        origin = origin
    )
    conn.execute(editsql_product)
    select_product_window(old_page)

def delete_product(old_page, input_id):
    deletesql_product = product_table.delete().where(product_table.c.id == input_id)
    conn.execute(deletesql_product)
    select_product_window(old_page)


# employee
def select_employee_window(old_page):
    LABEL_YPIXEL = 20
    PART_XPIXEL = 0
    BRANCH_XPIXEL = 130
    NAME_XPIXEL = 200
    GENDER_XPIXEL = 270

    EDIT_XPIXEL = 480
    REMOVE_XPIXEL = 530
    line = 0
    global product_data
    old_page.destroy()
    win_employee = tk.Tk()
    win_employee.title("employee")
    win_employee.geometry('600x400')

    tk.Label(win_employee, text="User Name:").place(x=0, y=0)
    tk.Label(win_employee, text=G_user_name).place(x=80, y=0)
    tk.Label(win_employee, text="part").place(x=PART_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_employee, text="branch").place(x=BRANCH_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_employee, text="Name").place(x=NAME_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_employee, text="gender").place(x=GENDER_XPIXEL, y=LABEL_YPIXEL)

    tk.Button(win_employee, text='Insert', command=lambda:employee_insert_window(win_employee)).pack(
        side="bottom")
    tk.Button(win_employee, text='return', command=lambda:open_main_window(win_employee)).pack(
        side="bottom")

    selectsql_employee = select([employee_table])
    result = conn.execute(selectsql_employee)


    for row in result:
        part_name = ''
        branch_name = ''
        selectsql_part_by_part_id = select([part_table]).where(part_table.c.id == row[1])
        result_part= conn.execute(selectsql_part_by_part_id)
        selectsql_branch_by_braanch_id = select([branch_table]).where(branch_table.c.id == row[2])
        result_branch= conn.execute(selectsql_branch_by_braanch_id)
        for part in result_part:
            part_name = part[1]
        for branch in result_branch:
            branch_name = branch[1]
        tk.Label(win_employee, text=part_name).place(x=PART_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_employee, text=branch_name).place(x=BRANCH_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_employee, text=row[3]).place(x=NAME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_employee, text=row[4]).place(x=GENDER_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_employee, text='edit', command=partial(edit_employee_window, win_employee, row[0]))\
            .place(x=EDIT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_employee, text='delete', command=partial(delete_employee, win_employee, row[0])) \
            .place(x=REMOVE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        line+=1

def employee_insert_window(old_page):
    old_page.destroy()
    win_employee_insert = tk.Tk()
    win_employee_insert.title("Insert product")
    win_employee_insert.geometry('450x300')

    tk.Label(win_employee_insert, text="part").grid(column=0, row=0)
    entry_part = tk.Entry(win_employee_insert)
    entry_part.grid(column=1, row=0)
    tk.Label(win_employee_insert, text="branch").grid(column=0, row=1)
    entry_branch = tk.Entry(win_employee_insert)
    entry_branch.grid(column=1, row=1)
    tk.Label(win_employee_insert, text="Name").grid(column=0, row=2)
    entry_name = tk.Entry(win_employee_insert)
    entry_name.grid(column=1, row=2)
    tk.Label(win_employee_insert, text="gender").grid(column=0, row=3)
    entry_gender = tk.Entry(win_employee_insert)
    entry_gender.grid(column=1, row=3)
    tk.Button(win_employee_insert, text="insert", command= lambda:insert_employee(win_employee_insert,
        entry_part.get(), entry_branch.get(), entry_name.get(), entry_gender.get())).place(x=200, y=250)
    tk.Button(win_employee_insert, text="return", command= lambda:select_employee_window(win_employee_insert))\
        .place(x=300, y=250)

def edit_employee_window(old_page, input_id):
    old_page.destroy()
    win_employee_insert = tk.Tk()
    win_employee_insert.title("Edit employee")
    win_employee_insert.geometry('450x300')

    selectsql_employee = select([employee_table]).\
        where(employee_table.c.id == input_id)
    result = conn.execute(selectsql_employee)

    string_part_id = StringVar()
    string_branch_id = StringVar()
    string_name = StringVar()
    string_gender = StringVar()

    for row in result:
        part_name =''
        branch_name =''
        selectsql_part_by_part_id = select([part_table]). \
            where(part_table.c.id == row[1])
        result_part = conn.execute(selectsql_part_by_part_id)
        for part in result_part:
            part_name = part[1]
        selectsql_branch_by_branch_id = select([branch_table]). \
            where(branch_table.c.id == row[2])
        result_branch = conn.execute(selectsql_branch_by_branch_id)
        for branch in result_branch:
            branch_name = branch[2]
        string_part_id.set(part_name)
        string_branch_id.set(branch_name)
        string_name.set(row[3])
        string_gender.set(row[4])

    tk.Label(win_employee_insert, text="part").grid(column=0, row=0)
    entry_part = tk.Entry(win_employee_insert, textvariable = string_part_id)
    entry_part.grid(column=1, row=0)
    tk.Label(win_employee_insert, text="branch").grid(column=0, row=1)
    entry_branch = tk.Entry(win_employee_insert, textvariable = string_branch_id)
    entry_branch.grid(column=1, row=1)
    tk.Label(win_employee_insert, text="Name").grid(column=0, row=3)
    entry_name = tk.Entry(win_employee_insert, textvariable=string_name)
    entry_name.grid(column=1, row=3)
    tk.Label(win_employee_insert, text="price").grid(column=0, row=4)
    entry_price = tk.Entry(win_employee_insert, textvariable=string_gender)
    entry_price.grid(column=1, row=4)


    tk.Button(win_employee_insert, text="insert", command= lambda:edit_employee(win_employee_insert,
        entry_part.get(),entry_branch.get(),entry_name.get(), entry_price.get(), input_id)).place(x=200, y=250)
    tk.Button(win_employee_insert, text="return", command= lambda:select_employee_window(win_employee_insert))\
        .place(x=300, y=250)

def insert_employee(old_page, part, branch, name, gender):

    part_id = 0
    branch_id = 0
    part_exist = 0
    branch_exist = 0
    selectsql_part_id_by_part = select([part_table]).where(part_table.c.name == part)
    result_part_id = conn.execute(selectsql_part_id_by_part)
    for part in result_part_id:
        part_id = part[0]
    selectsql_branch_id_by_branch = select([branch_table]).where(branch_table.c.name == branch)
    result_branch_id = conn.execute(selectsql_branch_id_by_branch)
    for part in result_branch_id:
        branch_id = part[0]
    selectsql_employee = select([employee_table])
    result = conn.execute(selectsql_employee)
    id = 1
    if part_id and branch_id:
        for row in result:
            if  not row[0] < id:
                id = row[0]+1
        ins = employee_table.insert().values(
            id= id,
            part_id = part_id,
            branch_id = branch_id,
            name = name,
            gender = gender
        )
        conn.execute(ins)
    else:
        messagebox.showerror(message='Error, your part or branch is wrong, try again.')
        pass
    select_employee_window(old_page)

def edit_employee(old_page, part, branch, name, gender, input_id):
    part_id = 0
    branch_id = 0
    selectsql_part_id_by_part = select([part_table]).where(part_table.c.name == part)
    result_part_id = conn.execute(selectsql_part_id_by_part)
    for part in result_part_id:
        part_id = part[0]
    selectsql_branch_id_by_branch = select([branch_table]).where(branch_table.c.name == branch)
    result_branch_id = conn.execute(selectsql_branch_id_by_branch)
    for branch in result_branch_id:
        branch_id = branch[0]
    if part_id and branch_id:
        editsql_employee = employee_table.update().where(employee_table.c.id == input_id).values(
            part_id = part_id,
            branch_id = branch_id,
            name = name,
            gender = gender
        )
        conn.execute(editsql_employee)
    else:
        messagebox.showerror(message='Error, your part or branch is wrong, try again.')
    select_employee_window(old_page)

def delete_employee(old_page, input_id):
    deletesql_employee = employee_table.delete().where(employee_table.c.id == input_id)
    conn.execute(deletesql_employee)
    select_employee_window(old_page)

# part
def select_part_window(old_page):
    LABEL_YPIXEL = 20
    NAME_XPIXEL = 0
    NUMBER_XPIXEL = 130
    EDIT_XPIXEL = 480
    REMOVE_XPIXEL = 530
    line = 0
    old_page.destroy()
    win_part = tk.Tk()
    win_part.title("part")
    win_part.geometry('600x400')

    tk.Label(win_part, text="User Name:").place(x=0, y=0)
    tk.Label(win_part, text=G_user_name).place(x=80, y=0)
    tk.Label(win_part, text="Name").place(x=NAME_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_part, text="number").place(x=NUMBER_XPIXEL, y=LABEL_YPIXEL)
    tk.Button(win_part, text='Insert', command=lambda: part_insert_window(win_part)).pack(
        side="bottom")
    tk.Button(win_part, text='return', command=lambda:open_main_window(win_part)).pack(
        side="bottom")

    selectsql_part = select([part_table])
    result = conn.execute(selectsql_part)

    for row in result:
        tk.Label(win_part, text=row[1]).place(x=NAME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_part, text=row[2]).place(x=NUMBER_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_part, text='edit', command=partial(edit_part_window, win_part, row[0]))\
            .place(x=EDIT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_part, text='delete', command=partial(delete_part, win_part, row[0])) \
            .place(x=REMOVE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        line+=1

def part_insert_window(old_page):
    old_page.destroy()
    win_part_insert = tk.Tk()
    win_part_insert.title("Insert part")
    win_part_insert.geometry('450x300')

    tk.Label(win_part_insert, text="Name").grid(column=0, row=0)
    entry_name = tk.Entry(win_part_insert)
    entry_name.grid(column=1, row=0)
    tk.Label(win_part_insert, text="number").grid(column=0, row=1)
    entry_number = tk.Entry(win_part_insert)
    entry_number.grid(column=1, row=1)
    tk.Button(win_part_insert, text="insert", command= lambda:insert_part(win_part_insert,entry_name.get(),
                                                                             entry_number.get())).place(x=200, y=250)
    tk.Button(win_part_insert, text="return", command= lambda:select_part_window(win_part_insert)).place(x=300, y=250)

def edit_part_window(old_page, input_id):
    old_page.destroy()
    win_part_insert = tk.Tk()
    win_part_insert.title("Edit product")
    win_part_insert.geometry('450x300')

    selectsql_part = select([part_table]).\
        where(part_table.c.id == input_id)
    result = conn.execute(selectsql_part)

    string_name = StringVar()
    string_number = StringVar()

    for row in result:
        string_name.set(row[1])
        string_number.set(row[2])

    label_Lname = tk.Label(win_part_insert, text="Name").grid(column=0, row=0)
    entry_name = tk.Entry(win_part_insert, textvariable = string_name)
    entry_name.grid(column=1, row=0)
    label_Lprice = tk.Label(win_part_insert, text="price").grid(column=0, row=2)
    entry_number = tk.Entry(win_part_insert, textvariable = string_number)
    entry_number.grid(column=1, row=2)

    btn_edit = tk.Button(win_part_insert, text="insert", command= lambda:edit_part(win_part_insert,
        entry_name.get(), entry_number.get(), input_id)).place(x=200, y=250)
    btn_return = tk.Button(win_part_insert, text="return", command= lambda:select_part_window(win_part_insert))\
        .place(x=300, y=250)

def insert_part(old_page, name, number):

    selectsql_part = select([part_table])
    result = conn.execute(selectsql_part)
    id = 1
    for row in result:
        if  not row[0] < id:
            id = row[0]+1
    ins = part_table.insert().values(
        id= id,
        name = name,
        number = number)
    conn.execute(ins)
    select_part_window(old_page)

def edit_part(old_page, name, number, input_id):
    editsql_product = part_table.update().where(part_table.c.id == input_id).values(
        name = name,
        number = number
    )
    conn.execute(editsql_product)
    select_part_window(old_page)

def delete_part(old_page, input_id):
    deletesql_part = part_table.delete().where(part_table.c.id == input_id)
    conn.execute(deletesql_part)
    select_part_window(old_page)
# branch
def select_branch_window(old_page):
    LABEL_YPIXEL = 20
    NAME_XPIXEL = 0
    PRICE_XPIXEL = 130
    EFFECT_XPIXEL = 280
    EDIT_XPIXEL = 480
    REMOVE_XPIXEL = 530
    line = 0

    old_page.destroy()
    win_branch = tk.Tk()
    win_branch.title("branch")
    win_branch.geometry('600x400')

    tk.Label(win_branch, text="User Name:").place(x=0, y=0)
    tk.Label(win_branch, text=G_user_name).place(x=80, y=0)
    tk.Label(win_branch, text="Name").place(x=NAME_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_branch, text="address").place(x=PRICE_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_branch, text="phone").place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL)
    tk.Button(win_branch, text='Insert', command=lambda: branch_insert_window(win_branch)).pack(side="bottom")
    tk.Button(win_branch, text='return', command=lambda:open_main_window(win_branch)).pack(side="bottom")

    selectsql_branch = select([branch_table])
    result = conn.execute(selectsql_branch)

    for row in result:
        tk.Label(win_branch, text=row[1]).place(x=NAME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_branch, text=row[2]).place(x=PRICE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_branch, text=row[3]).place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_branch, text='edit', command=partial(edit_branch_window, win_branch, row[0]))\
            .place(x=EDIT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_branch, text='delete', command=partial(delete_branch, win_branch, row[0])) \
            .place(x=REMOVE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        line+=1

def branch_insert_window(old_page):
    old_page.destroy()
    win_branch_insert = tk.Tk()
    win_branch_insert.title("Insert product")
    win_branch_insert.geometry('450x300')

    tk.Label(win_branch_insert, text="Name").grid(column=0, row=0)
    entry_name = tk.Entry(win_branch_insert)
    entry_name.grid(column=1, row=0)
    tk.Label(win_branch_insert, text="address").grid(column=0, row=1)
    entry_address = tk.Entry(win_branch_insert)
    entry_address.grid(column=1, row=1)
    tk.Label(win_branch_insert, text="phone").grid(column=0, row=2)
    entry_phone = tk.Entry(win_branch_insert)
    entry_phone.grid(column=1, row=2)
    btn_insert = tk.Button(win_branch_insert, text="insert", command= lambda:insert_branch(win_branch_insert,
        entry_name.get(), entry_address.get(), entry_phone.get())).place(x=200, y=250)
    btn_return = tk.Button(win_branch_insert, text="return", command= lambda:select_branch_window(win_branch_insert))\
        .place(x=300, y=250)

def edit_branch_window(old_page, input_id):
    print('id:', input_id)
    old_page.destroy()
    win_branch_insert = tk.Tk()
    win_branch_insert.title("Edit product")
    win_branch_insert.geometry('450x300')

    selectsql_product = select([branch_table]).\
        where(branch_table.c.id == input_id)
    result = conn.execute(selectsql_product)
    print('result:', result)
    purchase_product_id = select([purchase_table]). \
        where(purchase_table.c.product_id == input_id)
    selectsql_purchase_result = conn.execute(selectsql_product)

    string_name = StringVar()
    string_address = StringVar()
    string_phone = StringVar()


    for row in result:
        string_name.set(row[1])
        string_address.set(row[2])
        string_phone.set(row[3])

    tk.Label(win_branch_insert, text="Name").grid(column=0, row=0)
    entry_name = tk.Entry(win_branch_insert, textvariable = string_name)
    entry_name.grid(column=1, row=0)
    tk.Label(win_branch_insert, text="address").grid(column=0, row=1)
    entry_address = tk.Entry(win_branch_insert, textvariable = string_address)
    entry_address.grid(column=1, row=1)
    tk.Label(win_branch_insert, text="phone").grid(column=0, row=2)
    entry_phone = tk.Entry(win_branch_insert, textvariable = string_phone)
    entry_phone.grid(column=1, row=2)


    btn_edit = tk.Button(win_branch_insert, text="insert", command= lambda:edit_branch(win_branch_insert,
        entry_name.get(), entry_address.get(),entry_phone.get(), input_id)).place(x=200, y=250)
    btn_return = tk.Button(win_branch_insert, text="return", command= lambda:select_branch_window(win_branch_insert))\
        .place(x=300, y=250)

def insert_branch(old_page, name, address, phone):

    selectsql_branch = select([branch_table])
    result = conn.execute(selectsql_branch)
    id = 1
    for row in result:
        if  not row[0] < id:
            id = row[0]+1
    ins = branch_table.insert().values(
        id= id,
        name = name,
        address = address,
        phone = phone
    )
    conn.execute(ins)
    select_branch_window(old_page)

def edit_branch(old_page, name, address, phone, input_id):
    editsql_branch = branch_table.update().where(branch_table.c.id == input_id).values(
        name = name,
        address = address,
        phone = phone
    )
    conn.execute(editsql_branch)
    select_branch_window(old_page)

def delete_branch(old_page, input_id):
    deletesql_branch = branch_table.delete().where(branch_table.c.id == input_id)
    conn.execute(deletesql_branch)
    select_product_window(old_page)
# leave
def select_leave_window(old_page):
    LABEL_YPIXEL = 20
    NAME_XPIXEL = 0
    PRICE_XPIXEL = 130
    EFFECT_XPIXEL = 280
    EDIT_XPIXEL = 480
    REMOVE_XPIXEL = 530
    line = 0

    old_page.destroy()
    win_leave = tk.Tk()
    win_leave.title("leave")
    win_leave.geometry('600x400')

    tk.Label(win_leave, text="User Name:").place(x=0, y=0)
    tk.Label(win_leave, text=G_user_name).place(x=80, y=0)
    tk.Label(win_leave, text="employee").place(x=NAME_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_leave, text="kind").place(x=PRICE_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_leave, text="Date").place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL)
    tk.Button(win_leave, text='Insert', command=lambda:leave_insert_window(win_leave)).pack(
        side="bottom")
    tk.Button(win_leave, text='return', command=lambda:open_main_window(win_leave)).pack(
        side="bottom")

    employee_name = ''

    selectsql_leave = select([leave_table])
    result = conn.execute(selectsql_leave)

    for row in result:
        select_employee_name_by_employee_id = select([employee_table]).where(employee_table.c.id == row[1])
        result_employee_name = conn.execute(select_employee_name_by_employee_id)
        for employee in result_employee_name:
            employee_name = employee[3]
        tk.Label(win_leave, text=employee_name).place(x=NAME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_leave, text=row[2]).place(x=PRICE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_leave, text=row[3]).place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_leave, text='edit', command=partial(edit_leave_window, win_leave, row[0]))\
            .place(x=EDIT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_leave, text='delete', command=partial(delete_leave, win_leave, row[0])) \
            .place(x=REMOVE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        line+=1

def leave_insert_window(old_page):
    old_page.destroy()
    win_leave_insert = tk.Tk()
    win_leave_insert.title("Insert product")
    win_leave_insert.geometry('450x300')

    tk.Label(win_leave_insert, text="employee").grid(column=0, row=0)
    entry_employee = tk.Entry(win_leave_insert)
    entry_employee.grid(column=1, row=0)
    tk.Label(win_leave_insert, text="kind").grid(column=0, row=1)
    entry_kind = tk.Entry(win_leave_insert)
    entry_kind.grid(column=1, row=1)
    tk.Label(win_leave_insert, text="Date").grid(column=0, row=2)
    entry_date = tk.Entry(win_leave_insert)
    entry_date.grid(column=1, row=2)
    tk.Button(win_leave_insert, text="insert", command= lambda:insert_leave(win_leave_insert,
        entry_employee.get(), entry_kind.get(), entry_date.get())).place(x=200, y=250)
    tk.Button(win_leave_insert, text="return", command= lambda:select_leave_window(win_leave_insert)).place(x=300, y=250)

def edit_leave_window(old_page, input_id):
    old_page.destroy()
    win_leave_insert = tk.Tk()
    win_leave_insert.title("Edit leave")
    win_leave_insert.geometry('450x300')

    selectsql_leave = select([leave_table]).where(leave_table.c.id == input_id)
    result = conn.execute(selectsql_leave)

    string_name = StringVar()
    string_kind = StringVar()
    string_date = StringVar()

    for row in result:
        employee_name = ''
        selectsql_employee_by_leave_id = select([employee_table]).where(employee_table.c.id == row[1])
        result_employee = conn.execute(selectsql_employee_by_leave_id)
        for employee in result_employee:
            employee_name = employee[3]
        string_name.set(employee_name)
        string_kind.set(row[2])
        string_date.set(row[3])

    tk.Label(win_leave_insert, text="employee").grid(column=0, row=0)
    entry_name = tk.Entry(win_leave_insert, textvariable = string_name)
    entry_name.grid(column=1, row=0)
    tk.Label(win_leave_insert, text="kind").grid(column=0, row=1)
    entry_kind = tk.Entry(win_leave_insert, textvariable = string_kind)
    entry_kind.grid(column=1, row=1)
    tk.Label(win_leave_insert, text="date").grid(column=0, row=2)
    entry_date = tk.Entry(win_leave_insert, textvariable = string_date)
    entry_date.grid(column=1, row=2)

    btn_edit = tk.Button(win_leave_insert, text="insert", command= lambda:edit_leave(win_leave_insert,
        entry_name.get(), entry_kind.get(), entry_date.get(), input_id)).place(x=200, y=250)
    btn_return = tk.Button(win_leave_insert, text="return", command= lambda:select_leave_window(win_leave_insert))\
        .place(x=300, y=250)

def insert_leave(old_page, name, kind, date):
    employee_id = 0
    selectsql_leave = select([leave_table])
    result = conn.execute(selectsql_leave)

    selectsql_employee_id_by_name = select([employee_table]).where(employee_table.c.name == name)
    result_employee = conn.execute(selectsql_employee_id_by_name)
    for employee in result_employee:
        employee_id = employee[0]
    id = 1
    if employee_id:
        for row in result:
            if  not row[0] < id:
                id = row[0]+1
        ins = leave_table.insert().values(
            id= id,
            employee_id = employee_id,
            kind = kind,
            date = date,
        )
        conn.execute(ins)
    else:
        messagebox.showerror(message='Error, your employee is wrong, try again.')
    select_leave_window(old_page)

def edit_leave(old_page, name, kind, date, input_id):
    employee_id = 0
    selectsql_employee_id_by_name = select([employee_table]).where(employee_table.c.name == name)
    result_employee = conn.execute(selectsql_employee_id_by_name)
    for employee in result_employee:
        employee_id = employee[0]
    editsql_leave = leave_table.update().where(leave_table.c.id == input_id).values(
        employee_id = employee_id,
        kind = kind,
        date = date
    )
    conn.execute(editsql_leave)
    select_leave_window(old_page)

def delete_leave(old_page, input_id):
    deletesql_leave = leave_table.delete().where(leave_table.c.id == input_id)
    conn.execute(deletesql_leave)
    select_leave_window(old_page)
# attendance
def select_attendance_window(old_page):
    LABEL_YPIXEL = 20
    NAME_XPIXEL = 0
    PRICE_XPIXEL = 130
    EFFECT_XPIXEL = 280
    EDIT_XPIXEL = 480
    REMOVE_XPIXEL = 530
    line = 0

    old_page.destroy()
    win_leave = tk.Tk()
    win_leave.title("attendance")
    win_leave.geometry('600x400')

    tk.Label(win_leave, text="User Name:").place(x=0, y=0)
    tk.Label(win_leave, text=G_user_name).place(x=80, y=0)
    tk.Label(win_leave, text="employee").place(x=NAME_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_leave, text="place").place(x=PRICE_XPIXEL, y=LABEL_YPIXEL)
    tk.Label(win_leave, text="Date").place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL)
    tk.Button(win_leave, text='Insert', command=lambda:attendance_insert_window(win_leave)).pack(
        side="bottom")
    tk.Button(win_leave, text='return', command=lambda:open_main_window(win_leave)).pack(
        side="bottom")

    employee_name = ''

    selectsql_attendance = select([attendance_table])
    result = conn.execute(selectsql_attendance)

    for row in result:
        select_employee_name_by_employee_id = select([employee_table]).where(employee_table.c.id == row[1])
        result_employee_name = conn.execute(select_employee_name_by_employee_id)
        for employee in result_employee_name:
            employee_name = employee[3]
        tk.Label(win_leave, text=employee_name).place(x=NAME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_leave, text=row[2]).place(x=PRICE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Label(win_leave, text=row[3]).place(x=EFFECT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_leave, text='edit', command=partial(edit_attendance_window, win_leave, row[0]))\
            .place(x=EDIT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        tk.Button(win_leave, text='delete', command=partial(delete_attendance, win_leave, row[0])) \
            .place(x=REMOVE_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        line+=1

def attendance_insert_window(old_page):
    old_page.destroy()
    win_leave_insert = tk.Tk()
    win_leave_insert.title("Insert attendance")
    win_leave_insert.geometry('450x300')

    tk.Label(win_leave_insert, text="employee").grid(column=0, row=0)
    entry_employee = tk.Entry(win_leave_insert)
    entry_employee.grid(column=1, row=0)
    tk.Label(win_leave_insert, text="place").grid(column=0, row=1)
    entry_kind = tk.Entry(win_leave_insert)
    entry_kind.grid(column=1, row=1)
    tk.Label(win_leave_insert, text="Date").grid(column=0, row=2)
    entry_date = tk.Entry(win_leave_insert)
    entry_date.grid(column=1, row=2)
    tk.Button(win_leave_insert, text="insert", command= lambda:insert_attendance(win_leave_insert,
        entry_employee.get(), entry_kind.get(), entry_date.get())).place(x=200, y=250)
    tk.Button(win_leave_insert, text="return", command= lambda:select_attendance_window(win_leave_insert)).place(x=300, y=250)

def edit_attendance_window(old_page, input_id):
    old_page.destroy()
    win_leave_insert = tk.Tk()
    win_leave_insert.title("Edit attendance")
    win_leave_insert.geometry('450x300')

    selectsql_attendance = select([attendance_table]).where(attendance_table.c.id == input_id)
    result = conn.execute(selectsql_attendance)

    string_name = StringVar()
    string_kind = StringVar()
    string_date = StringVar()

    for row in result:
        employee_name = ''
        selectsql_employee_by_attendance_id = select([employee_table]).where(employee_table.c.id == row[1])
        result_employee = conn.execute(selectsql_employee_by_attendance_id)
        for employee in result_employee:
            employee_name = employee[3]
        string_name.set(employee_name)
        string_kind.set(row[2])
        string_date.set(row[3])

    tk.Label(win_leave_insert, text="employee").grid(column=0, row=0)
    entry_name = tk.Entry(win_leave_insert, textvariable = string_name)
    entry_name.grid(column=1, row=0)
    tk.Label(win_leave_insert, text="place").grid(column=0, row=1)
    entry_kind = tk.Entry(win_leave_insert, textvariable = string_kind)
    entry_kind.grid(column=1, row=1)
    tk.Label(win_leave_insert, text="date").grid(column=0, row=2)
    entry_date = tk.Entry(win_leave_insert, textvariable = string_date)
    entry_date.grid(column=1, row=2)

    btn_edit = tk.Button(win_leave_insert, text="insert", command= lambda:edit_attendance(win_leave_insert,
        entry_name.get(), entry_kind.get(), entry_date.get(), input_id)).place(x=200, y=250)
    btn_return = tk.Button(win_leave_insert, text="return", command= lambda:select_attendance_window(win_leave_insert))\
        .place(x=300, y=250)

def insert_attendance(old_page, name, place, date):
    employee_id = 0
    selectsql_attendance = select([attendance_table])
    result = conn.execute(selectsql_attendance)

    selectsql_employee_id_by_name = select([employee_table]).where(employee_table.c.name == name)
    result_employee = conn.execute(selectsql_employee_id_by_name)
    for employee in result_employee:
        employee_id = employee[0]
    id = 1
    if employee_id:
        for row in result:
            if  not row[0] < id:
                id = row[0]+1
        ins = attendance_table.insert().values(
            id= id,
            employee_id = employee_id,
            place = place,
            date = date,
        )
        conn.execute(ins)
    else:
        messagebox.showerror(message='Error, your employee is wrong, try again.')
    select_attendance_window(old_page)

def edit_attendance(old_page, name, place, date, input_id):
    employee_id = 0
    selectsql_employee_id_by_name = select([employee_table]).where(employee_table.c.name == name)
    result_employee = conn.execute(selectsql_employee_id_by_name)
    for employee in result_employee:
        employee_id = employee[0]
    if employee_id:
        editsql_attendance = attendance_table.update().where(attendance_table.c.id == input_id).values(
            employee_id = employee_id,
            place = place,
            date = date
        )
        conn.execute(editsql_attendance)
    else:
        messagebox.showerror(message='Error, your employee is wrong, try again.')
    select_attendance_window(old_page)

def delete_attendance(old_page, input_id):
    deletesql_leave = attendance_table.delete().where(attendance_table.c.id == input_id)
    conn.execute(deletesql_leave)
    select_attendance_window(old_page)
#

def select_purchase_window(old_page):
    LABEL_YPIXEL = 20
    PRODUCT_XPIXEL = 0
    TIME_XPIXEL = 80
    COUNT_XPIXEL = 160
    MANUFACTURES_XPIXEL = 240
    EDIT_XPIXEL = 480
    DELETE_XPIXEL = 530
    line = 0

    old_page.destroy()
    win_purchase = tk.Tk()
    win_purchase.title("purchase")
    win_purchase.geometry('600x400')

    label_Lusername = tk.Label(win_purchase, text="User Name:").place(x=0, y=0)
    label_username = tk.Label(win_purchase, text=G_user_name).place(x=80, y=0)
    label_Lproduct = tk.Label(win_purchase, text="Product").place(x=PRODUCT_XPIXEL, y=LABEL_YPIXEL)
    label_Ltime = tk.Label(win_purchase, text="Time").place(x=TIME_XPIXEL, y=LABEL_YPIXEL)
    label_Lcount = tk.Label(win_purchase, text="Count").place(x=COUNT_XPIXEL, y=LABEL_YPIXEL)
    label_Lmanafacture = tk.Label(win_purchase, text="Manufactures").place(x=MANUFACTURES_XPIXEL, y=LABEL_YPIXEL)
    btn_new_object = tk.Button(win_purchase, text='Insert', command=lambda: purchase_insert_window(win_purchase)).pack(
        side="bottom")
    btn_open_main = tk.Button(win_purchase, text='return', command=lambda:open_main_window(win_purchase)).pack(
        side="bottom")

    selectsql_purchase = select([purchase_table])
    result = conn.execute(selectsql_purchase)

    for row in result:
        selectsql_product_by_product_id = select([product_table]).where(product_table.c.id == row[1])
        result_product_name = conn.execute(selectsql_product_by_product_id)
        product_name = ''
        for name in result_product_name:
            product_name = name[1]

        label_pur_name = tk.Label(win_purchase, text=product_name).place(x=PRODUCT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_pur_stock = tk.Label(win_purchase, text=row[2]).place(x=TIME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_pur_price = tk.Label(win_purchase, text=row[3]).place(x=COUNT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_pur_purchase = tk.Label(win_purchase, text=row[4]).place(x=MANUFACTURES_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)

        btn_delete_pur = tk.Button(win_purchase, text='delete', command=partial(delete_purchase, win_purchase, row[0])) \
            .place(x=DELETE_XPIXEL, y=LABEL_YPIXEL + (line + 1) * 30)
        line+=1

def purchase_insert_window(old_page):
    old_page.destroy()
    win_purchase_insert = tk.Tk()
    win_purchase_insert.title("Insert product")
    win_purchase_insert.geometry('450x300')

    label_Lname = tk.Label(win_purchase_insert, text="Product").grid(column=0, row=0)
    entry_name = tk.Entry(win_purchase_insert)
    entry_name.grid(column=1, row=0)
    label_Lstock = tk.Label(win_purchase_insert, text="Time").grid(column=0, row=1)
    entry_stock = tk.Entry(win_purchase_insert)
    entry_stock.grid(column=1, row=1)
    label_Lprice = tk.Label(win_purchase_insert, text="Count").grid(column=0, row=2)
    entry_price = tk.Entry(win_purchase_insert)
    entry_price.grid(column=1, row=2)
    label_Lpurchase = tk.Label(win_purchase_insert, text="Manufactures").grid(column=0, row=3)
    entry_purchase = tk.Entry(win_purchase_insert)
    entry_purchase.grid(column=1, row=3)

    btn_insert = tk.Button(win_purchase_insert, text="insert", command= lambda:insert_purchase(win_purchase_insert,
        entry_name.get(), entry_stock.get(), entry_price.get(), entry_purchase.get())).place(x=200, y=250)
    btn_return = tk.Button(win_purchase_insert, text="return", command= lambda:select_purchase_window(win_purchase_insert))\
        .place(x=300, y=250)

def insert_purchase(old_page, product, time, count, manufactures):
    print('\nproduct:', product)
    selectsql_purchase = select([purchase_table])
    result = conn.execute(selectsql_purchase)

    product_id = 0
    selectsql_product_by_product_id = select([product_table]).where(product_table.c.name == product)
    result_product_id = conn.execute(selectsql_product_by_product_id)
    for row in result_product_id:
        product_id = row[0]

    id = 0
    for row in result:
        if not row[0] < id:
            id = row[0] + 1

    if product_id:
        ins = purchase_table.insert().values(
            id= id,
            product_id = product_id,
            time= time,
            count = count,
            Manufacturers = manufactures
            )
        conn.execute(ins)

        edit_stock(product_id, count, 1)
    else:
        messagebox.showerror(message='Error, the product is not exist, try again.')
        pass
    select_purchase_window(old_page)

def delete_purchase(old_page, input_id):
    selectsql_purchase = purchase_table.select().where(purchase_table.c.id == input_id)
    result_purchase = conn.execute(selectsql_purchase)

    purchase_product_id = 0
    purchase_count = 0
    for row in result_purchase:
        purchase_product_id = row[1]
        purchase_count = row[3]
        print('purchase_product_id:',purchase_product_id)
        print('purchase_count:', purchase_count)
    edit_stock(purchase_product_id, purchase_count, 0)

    deletesql_purchase = purchase_table.delete().where(purchase_table.c.id == input_id)
    conn.execute(deletesql_purchase)
    select_purchase_window(old_page)


def select_stock_window(old_page):
    LABEL_YPIXEL = 20
    NAME_XPIXEL = 0
    STOCK_XPIXEL = 100

    line = 0
    global product_data
    old_page.destroy()
    win_stock = tk.Tk()
    win_stock.title("stock")
    win_stock.geometry('600x400')

    label_Lusername = tk.Label(win_stock, text="User Name:").place(x=0, y=0)
    label_username = tk.Label(win_stock, text=G_user_name).place(x=80, y=0)
    label_Lproductname = tk.Label(win_stock, text="Product").place(x=NAME_XPIXEL, y=LABEL_YPIXEL)
    label_Lcount = tk.Label(win_stock, text="Count").place(x=STOCK_XPIXEL, y=LABEL_YPIXEL)

    btn_open_main = tk.Button(win_stock, text='return', command=lambda:open_main_window(win_stock)).pack(
        side="bottom")

    selectsql_stock = select([stock_table])
    result = conn.execute(selectsql_stock)

    for row in result:
        product_id = row[1]
        product_name = ''
        selectsql_name_by_product_id = select([product_table]).where(product_table.c.id == product_id)
        result_product_id = conn.execute(selectsql_name_by_product_id)
        for id in result_product_id:
            product_name = id[1]
        label_stk_name = tk.Label(win_stock, text=product_name).place(x=NAME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_stk_count = tk.Label(win_stock, text=row[2]).place(x=STOCK_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)

        line+=1

def edit_stock(product_id, count, add):
    # print('\nedit stock\n')
    if add:
        # print('\nadd \n')
        product_exist = 0
        selectsql_stock_table = select([stock_table])
        result_stock_table = conn.execute(selectsql_stock_table)
        id = 0
        pre_count = 0
        for row in result_stock_table:
            if product_id == row[1]:
                product_exist = 1
                pre_count = row[2]
            if not row[0] < id:
                id = row[0] + 1

        if product_exist:
            upd = stock_table.update().where(stock_table.c.product_id == product_id).values(
                count= int(pre_count)+int(count)
            )
            conn.execute(upd)
        else:
            ins = stock_table.insert().values(
                product_id=product_id,
                count=count
            )
            conn.execute(ins)
    else:
        selectsql_stock_table = select([stock_table])
        result_stock_table = conn.execute(selectsql_stock_table)
        pre_count = 0
        for row in result_stock_table:
            if product_id == row[1]:
                pre_count = row[2]
        upd = stock_table.update().where(stock_table.c.product_id == product_id).values(
            count=int(pre_count) - int(count)
        )
        conn.execute(upd)
        pass


def select_returns_window(old_page):
    LABEL_YPIXEL = 20
    PRODUCT_XPIXEL = 0
    TIME_XPIXEL = 80
    COUNT_XPIXEL = 160
    MONEY_XPIXEL = 240
    MANUFACTURES_XPIXEL = 300

    DELETE_XPIXEL = 530
    line = 0

    old_page.destroy()
    win_returns = tk.Tk()
    win_returns.title("returns")
    win_returns.geometry('600x400')

    label_Lusername = tk.Label(win_returns, text="User Name:").place(x=0, y=0)
    label_username = tk.Label(win_returns, text=G_user_name).place(x=80, y=0)
    label_Lproduct = tk.Label(win_returns, text="Product").place(x=PRODUCT_XPIXEL, y=LABEL_YPIXEL)
    label_Ltime = tk.Label(win_returns, text="Time").place(x=TIME_XPIXEL, y=LABEL_YPIXEL)
    label_Lcount = tk.Label(win_returns, text="Count").place(x=COUNT_XPIXEL, y=LABEL_YPIXEL)
    label_Lmoney = tk.Label(win_returns, text="Money").place(x=MONEY_XPIXEL, y=LABEL_YPIXEL)
    label_Lmanafacture = tk.Label(win_returns, text="Manufactures").place(x=MANUFACTURES_XPIXEL, y=LABEL_YPIXEL)
    btn_new_object = tk.Button(win_returns, text='Insert', command=lambda: returns_insert_window(win_returns)).pack(
        side="bottom")
    btn_open_main = tk.Button(win_returns, text='return', command=lambda:open_main_window(win_returns)).pack(
        side="bottom")

    selectsql_returns = select([returns_table])
    result = conn.execute(selectsql_returns)

    for row in result:
        selectsql_product_by_product_id = select([product_table]).where(product_table.c.id == row[1])
        result_product_name = conn.execute(selectsql_product_by_product_id)
        product_name = ''
        for name in result_product_name:
            product_name = name[1]

        label_pur_name = tk.Label(win_returns, text=product_name).place(x=PRODUCT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_pur_stock = tk.Label(win_returns, text=row[2]).place(x=TIME_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_pur_price = tk.Label(win_returns, text=row[3]).place(x=COUNT_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_pur_purchase = tk.Label(win_returns, text=row[4]).place(x=MONEY_XPIXEL, y=LABEL_YPIXEL+(line+1)*30)
        label_pur_manafacture  = tk.Label(win_returns, text=row[4]).place(x=MANUFACTURES_XPIXEL, y=LABEL_YPIXEL+(line+1) * 30)
        btn_delete_pur = tk.Button(win_returns, text='delete', command=partial(delete_returns, win_returns, row[0])) \
            .place(x=DELETE_XPIXEL, y=LABEL_YPIXEL + (line + 1) * 30)
        line+=1

def returns_insert_window(old_page):
    old_page.destroy()
    win_returns_insert = tk.Tk()
    win_returns_insert.title("Insert returns")
    win_returns_insert.geometry('450x300')

    label_Lname = tk.Label(win_returns_insert, text="Product").grid(column=0, row=0)
    entry_name = tk.Entry(win_returns_insert)
    entry_name.grid(column=1, row=0)
    label_Lstock = tk.Label(win_returns_insert, text="Time").grid(column=0, row=1)
    entry_stock = tk.Entry(win_returns_insert)
    entry_stock.grid(column=1, row=1)
    label_Lprice = tk.Label(win_returns_insert, text="Count").grid(column=0, row=2)
    entry_price = tk.Entry(win_returns_insert)
    entry_price.grid(column=1, row=2)
    label_Lpurchase = tk.Label(win_returns_insert, text="Manufactures").grid(column=0, row=3)
    entry_purchase = tk.Entry(win_returns_insert)
    entry_purchase.grid(column=1, row=3)

    btn_insert = tk.Button(win_returns_insert, text="insert", command= lambda:insert_returns(win_returns_insert,
        entry_name.get(), entry_stock.get(), entry_price.get(), entry_purchase.get())).place(x=200, y=250)
    btn_return = tk.Button(win_returns_insert, text="return", command= lambda:select_returns_window(win_returns_insert))\
        .place(x=300, y=250)

def insert_returns(old_page, product, time, count, manufactures):
    print('\nproduct:', product)
    selectsql_returns = select([returns_table])
    result = conn.execute(selectsql_returns)

    product_id = 0
    product_price = 0
    stock_count = 0
    selectsql_product_by_product_name = select([product_table]).where(product_table.c.name == product)
    result_product_id = conn.execute(selectsql_product_by_product_name)
    for row in result_product_id:
        product_id = row[0]
        product_price = row[2]
    selectsql_stock_by_product_id = select([stock_table]).where(stock_table.c.product_id == product_id)
    result_stock = conn.execute(selectsql_stock_by_product_id)
    for row in result_stock:
        stock_count = row[2]
    id = 0
    for row in result:
        if not row[0] < id:
            id = row[0] + 1

    if product_id:
        if int(stock_count) > int(count):
            ins = returns_table.insert().values(
                id= id,
                product_id = product_id,
                time= time,
                count = count,
                money = (int(product_price) * int(count)),
                Manufacturers = manufactures
                )
            conn.execute(ins)

            edit_stock(product_id, count, 0)
        else:
            messagebox.showerror(message='Error, the stock count is not enough, try again.')
    else:
        messagebox.showerror(message='Error, the product is not exist, try again.')
        pass
    select_returns_window(old_page)

def delete_returns(old_page, input_id):
    selectsql_returns = returns_table.select().where(returns_table.c.id == input_id)
    result_returns = conn.execute(selectsql_returns)

    returns_product_id = 0
    returns_count = 0
    for row in result_returns:
        returns_product_id = row[1]
        returns_count = -row[3]

    edit_stock(returns_product_id, returns_count, 0)
    deletesql_returns = returns_table.delete().where(returns_table.c.id == input_id)
    conn.execute(deletesql_returns)
    select_returns_window(old_page)




def usr_login(usr_name, usr_pwd):
    global win_login
    global G_user_name

    if usr_name in user_Info:
        if usr_pwd == user_Info[usr_name]:
            G_user_name = usr_name
            messagebox.showinfo(title='Welcome', message='How are you?' + usr_name)
            open_main_window(win_login)
        else:
            messagebox.showerror(message='Error, your password is wrong, try again.')
    else:
        messagebox.askyesno('Welcome','You have not sign up yet. Sign up today?')

def login_frame():
    global var_account
    global var_password
    global win_login
    win_login = tk.Tk()
    win_login.title("Log in")
    var_account = tk.StringVar()
    var_password = tk.StringVar()

    label=tk.Label(win_login, text="Log in!")
    label_account=tk.Label(win_login, text="account")
    label_password=tk.Label(win_login, text="password")
    entry_account=tk.Entry(win_login)
    entry_password=tk.Entry(win_login, show='*')
    btn_login=tk.Button(win_login, text="Log in", command=lambda :usr_login(entry_account.get(), entry_password.get()))
    label.grid(column=0,row=0)


    label_account.grid(column=0,row =ACCOUNTROW)
    entry_account.grid(column=1,row =ACCOUNTROW)
    label_password.grid(column=0,row =PASSWORDROW)
    entry_password.grid(column=1,row =PASSWORDROW)
    btn_login.grid(row = btnLOGINROW)

    win_login.mainloop()

login_frame()