from tkinter import *
import tkinter as tk
from tkinter import ttk
import sqlite3
from tkinter import messagebox

def enterroom():
    global enterroom
    enterroom = Tk()
    enterroom.title('Enter Room Inventory')
    enterroom.geometry("800x800+500+100")

    global entry1
    global my_box
    global mycombo
    global j

    label1=Label(enterroom, text="Room#")
    label1.place(x=10, y=10)
    entry1 = Entry(enterroom, width=10)
    entry1.place(x=70, y=10)

    options = [
        "Select Room Type",
        "NQ1 - Non Smoking Queen",
        "NQQ1 - Non Smoking Double Queen",
        "Q11 - Smoking Queen",
        "QQ1 - Smoking Double Queen",
        "NK1 - Non Smoking king",
        "K11 - Smoking King",
        "PNQ1 - Non Smoking Handi-Cap",
        "SK1 - Smoking Suit King",
        "SNK1 - Non Smoking Suite King",
    ]

    mycombo = ttk.Combobox(enterroom, width=40, value=options)
    mycombo.place(x=10, y=40)
    mycombo.current(0)

    my_box = Listbox(enterroom, height=30, width=40)
    my_box.place(x=500, y=20)
    boxlabel=Label(enterroom, text="Rooms Already Entered")
    boxlabel.place(x=500, y=0)

    conn = sqlite3.connect('roominventory.db')
    c = conn.cursor()
    c.execute("SELECT * FROM rooms")
    records = c.fetchall()

    for record in records:
        my_box.insert(END, record[0:2])

    conn.commit()
    conn.close()

    entry1.focus()

    btnenterroom = Button(enterroom, text="Enter Room", width=10, command=setroom)
    btnenterroom.place(x=10, y=70)


def setroom():

    conn = sqlite3.connect('roominventory.db')
    c = conn.cursor()
    c.execute("SELECT * FROM rooms")
    records = c.fetchall()

    for record in records:


        if entry1.get() == record[0]:  #IF room is already in database, then don't put into database
            messagebox.showwarning("Warning!", "This room is already entered.", parent=enterroom)

        else:
            #Put room into database===================
            jamin = mycombo.get()[0:4]
            jamin = jamin.strip()
            conn = sqlite3.connect('roominventory.db')
            c = conn.cursor()
            c.execute("INSERT INTO rooms VALUES (:number, :type, :rate, :vacant, :clean, :ooo, :r1, :r2, :r3, :r4, :r5)",

                          {
                              'number': entry1.get(),
                              'type': jamin,
                              'rate': 0,
                              'vacant': "Y",
                              'clean': "Y",
                              'ooo': "N",
                              'r1': "N",
                              'r2': "N",
                              'r3': "N",
                              'r4': "N",
                              'r5': 0,
                          }

                          )

            conn.commit()
            conn.close()

            my_box.delete(0, END)

            conn = sqlite3.connect('roominventory.db')
            c = conn.cursor()
            c.execute("SELECT * FROM rooms")
            records = c.fetchall()

            for record in records:
                my_box.insert(END, record[0:2])

            conn.commit()
            conn.close()

            mycombo.set("Select Room Type")

            entry1.delete(0, END)
            entry1.focus()


root = Tk()
root.state('zoomed')
root.title('Hotel King Ver 1.8')

btn1=Button(root, text="Click to enter room", command=enterroom)
btn1.pack()

conn = sqlite3.connect('roominventory.db')
c = conn.cursor()
c.execute("""CREATE TABLE IF NOT EXISTS rooms (
                  number integer,
                  type text,
                  rate real,
                  vacant text,
                  clean text,
                  ooo text,
                  r1 text,
                  r2 text,
                  r3 text,
                  r4 text,
                  r5 integer
                )""")

conn.commit()
conn.close()




root.mainloop()

if entry1.get() in records:
    print('Room Taken')
else:
    print('Room Available')