def update_car_status(self): sql = "UPDATE samochody SET zarezerwowany = '1' WHERE id_samochodu=" + str( self.get_car_id()) query = UpdateQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all()
def add_new_client(self): sql = "INSERT INTO klienci(imie, nazwisko, ulica, nr_domu, miasto, kod_pocztowy, nr_telefonu, email) " \ "VALUES('" + str(self.name.get()) + "', '" + str(self.surname.get()) + "', '" + \ str(self.ulica.get()) + "','" + str(self.home.get()) + "', '" + str(self.city.get()) + "', '" + \ str(self.pc.get()) + "', '" + str(self.phone.get()) + "', '" + str(self.mail.get()) + "')" query = InsertQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all()
def get_client_id(self): sql = "SELECT id_klienta FROM klienci WHERE email = '" + str( self.mail.get()) + "'" query = SelectQuery() self.connection = ConnectToSQLdb(query) client_id = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() return client_id[0][0]
def check_client(self): sql = "SELECT * FROM klienci WHERE email = '" + str( self.mail.get()) + "'" query = SelectQuery() self.connection = ConnectToSQLdb(query) client = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() if len(client) < 1: return True else: return False
def add_reservation(self): if self.next_step(): if self.check_client(): self.add_new_client() sql = "INSERT INTO rezerwacje(id_klienta, id_samochodu, start_rezerwacji) " \ "VALUES('" + str(self.get_client_id()) + "', '" + str(self.get_car_id()) + "', '" \ + str(date.today()) + "')" query = InsertQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() self.update_car_status() else: Tk().wm_withdraw() # to hide the main window messagebox.showinfo('Error', 'Uzupełnij Brakujące pola')
def example_cars(self): sql = 'SELECT marka, model, rok_produkcji, cena_wypozyczenia FROM Samochody WHERE zarezerwowany = 0 LIMIT 2' query_type = SelectQuery() connection = ConnectToSQLdb(query_type) cars = connection.execute_query(connection.get_cursor(), sql) result = '' for car in cars: it = Iterator(car) it.set_first() while it.is_done(): result += str(it.get_current_item()) + '\n' it.next() result += '\n\n' result_label = Label(self.window, text=result) result_label.place(x=730, y=150)
def check_reservation(self): email = str(self.mail.get()) car_id = self.get_car_id() sql = "SELECT rezerwacje.id_rezerwacji, klienci.email, samochody.id_samochodu FROM samochody INNER JOIN " \ "(klienci INNER JOIN rezerwacje on klienci.id_klienta = rezerwacje.id_klienta) on " \ "samochody.id_samochodu = rezerwacje.id_samochodu where klienci.email = '" + email + \ "' and samochody.id_samochodu = '" + car_id + "'" query_type = SelectQuery() self.connection = ConnectToSQLdb(query_type) table_of_result = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() if len(table_of_result) > 0: self.res_id = table_of_result[0][0] return True else: return False
def calculate_cost(self): sql = "select rezerwacje.start_rezerwacji, samochody.cena_wypozyczenia FROM rezerwacje INNER JOIN " \ "samochody ON rezerwacje.id_samochodu = samochody.id_samochodu WHERE rezerwacje.id_rezerwacji = '" + \ str(self.res_id) + "'" query_type = SelectQuery() self.connection = ConnectToSQLdb(query_type) table_of_result = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() day_cost = table_of_result[0][1] (rok, mies, day) = str(table_of_result[0][0]).split('-') (a_rok, a_mies, a_day) = str(date.today()).split('-') diff = abs(date(int(a_rok), int(a_mies), int(a_day)) - date(int(rok), int(mies), int(day))).days if diff == 0: return day_cost else: return int(day_cost) * int(diff)
def add_values_to_combobox(self): sql = 'SELECT id_samochodu, marka, model, rok_produkcji, cena_wypozyczenia FROM Samochody WHERE zarezerwowany = 0' tmp_table = [] query_type = SelectQuery() self.connection = ConnectToSQLdb(query_type) table_of_result = self.connection.execute_query( self.connection.get_cursor(), sql) self.close_all() for res in table_of_result: tmp = '' it = Iterator(res) it.set_first() while it.is_done(): tmp += str(it.get_current_item()) + ' ' it.next() tmp_table.append(tmp) return tmp_table
def delete_reservation(self): if self.next_step(): if self.check_reservation(): self.create_free_car() sql = "UPDATE rezerwacje SET koniec_rezerwacji = '" + str(date.today()) + \ "' WHERE id_rezerwacji = '" + str(self.res_id) + "'" query = UpdateQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() Tk().wm_withdraw() # to hide the main window messagebox.showinfo('Done', 'Usunięto rezerwacje, do zapłaty: ' + str(self.calculate_cost())) else: Tk().wm_withdraw() # to hide the main window messagebox.showinfo('Error', 'Brak rezerwacji dla tego klienta') else: Tk().wm_withdraw() # to hide the main window messagebox.showinfo('Error', 'Uzupełnij Brakujące pola')
def add_values_to_combobox(self): sql = "SELECT samochody.id_samochodu, samochody.marka, samochody.model from rezerwacje INNER JOIN samochody " \ "on rezerwacje.id_samochodu = samochody.id_samochodu WHERE samochody.zarezerwowany = '1' " \ "and rezerwacje.koniec_rezerwacji IS NULL" tmp_table = [] query_type = SelectQuery() self.connection = ConnectToSQLdb(query_type) table_of_result = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() for res in table_of_result: tmp = '' it = Iterator(res) it.set_first() while it.is_done(): tmp += str(it.get_current_item()) + ' ' it.next() tmp_table.append(tmp) print(tmp_table) return tmp_table
def create_list_of_car(self): sql = 'SELECT id_samochodu, marka, model, rok_produkcji FROM Samochody WHERE zarezerwowany = 1' query_type = SelectQuery() connection = ConnectToSQLdb(query_type) self.list_of_car = connection.execute_query(connection.get_cursor(), sql)
class CreateMainWindow(CreateWindow): def __init__(self): super().__init__() self.connection = None def add_buttons(self): add_button = Button(self.window, text="Dodaj nową rezerwację", command=self.add_reservation, width=40, height=3) add_button.place(x=145, y=250) search_button = Button(self.window, text="Sprawdź zarezerwowane auta", command=self.show_free_cars, width=40, height=3) search_button.place(x=615, y=30) delete_button = Button(self.window, text="Usuń rezerwacje", command=self.delete_window, width=40, height=3) delete_button.place(x=145, y=350) def delete_window(self): window = CreateDeleteWindow() window.start_app() def show_free_cars(self): window = CreateResultWindow.get_instance() window.start_app() def add_reservation(self): if self.next_step(): if self.check_client(): self.add_new_client() sql = "INSERT INTO rezerwacje(id_klienta, id_samochodu, start_rezerwacji) " \ "VALUES('" + str(self.get_client_id()) + "', '" + str(self.get_car_id()) + "', '" \ + str(date.today()) + "')" query = InsertQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() self.update_car_status() else: Tk().wm_withdraw() # to hide the main window messagebox.showinfo('Error', 'Uzupełnij Brakujące pola') def get_client_id(self): sql = "SELECT id_klienta FROM klienci WHERE email = '" + str( self.mail.get()) + "'" query = SelectQuery() self.connection = ConnectToSQLdb(query) client_id = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() return client_id[0][0] def check_client(self): sql = "SELECT * FROM klienci WHERE email = '" + str( self.mail.get()) + "'" query = SelectQuery() self.connection = ConnectToSQLdb(query) client = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() if len(client) < 1: return True else: return False def add_new_client(self): sql = "INSERT INTO klienci(imie, nazwisko, ulica, nr_domu, miasto, kod_pocztowy, nr_telefonu, email) " \ "VALUES('" + str(self.name.get()) + "', '" + str(self.surname.get()) + "', '" + \ str(self.ulica.get()) + "','" + str(self.home.get()) + "', '" + str(self.city.get()) + "', '" + \ str(self.pc.get()) + "', '" + str(self.phone.get()) + "', '" + str(self.mail.get()) + "')" query = InsertQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() def get_car_id(self): car = str(self.car.get()) return car[:car.index(" ")] def update_car_status(self): sql = "UPDATE samochody SET zarezerwowany = '1' WHERE id_samochodu=" + str( self.get_car_id()) query = UpdateQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() def next_step(self): if self.name.get() == '' or self.surname.get() == '' or self.ulica.get() == '' or \ self.home.get() == '' or self.city.get() == '' or self.pc.get() == '' or self.phone.get() == '' or \ self.mail.get() == '' or self.car.get() == '': return False else: return True def add_labels(self): warn = Label(self.window, text="Przykładowe wolne auta") warn.place(x=650, y=100) warn.config(fg="red", font="bold") self.example_cars() def example_cars(self): sql = 'SELECT marka, model, rok_produkcji, cena_wypozyczenia FROM Samochody WHERE zarezerwowany = 0 LIMIT 2' query_type = SelectQuery() connection = ConnectToSQLdb(query_type) cars = connection.execute_query(connection.get_cursor(), sql) result = '' for car in cars: it = Iterator(car) it.set_first() while it.is_done(): result += str(it.get_current_item()) + '\n' it.next() result += '\n\n' result_label = Label(self.window, text=result) result_label.place(x=730, y=150) def close_all(self): self.connection.close_cursor() self.connection.close_connection() def add_values_to_combobox(self): sql = 'SELECT id_samochodu, marka, model, rok_produkcji, cena_wypozyczenia FROM Samochody WHERE zarezerwowany = 0' tmp_table = [] query_type = SelectQuery() self.connection = ConnectToSQLdb(query_type) table_of_result = self.connection.execute_query( self.connection.get_cursor(), sql) self.close_all() for res in table_of_result: tmp = '' it = Iterator(res) it.set_first() while it.is_done(): tmp += str(it.get_current_item()) + ' ' it.next() tmp_table.append(tmp) return tmp_table def add_other_components(self): value = StringVar() name_label = Label(self.window, text="Imie: ") name_label.place(x=75, y=21) self.name = Entry(self.window, width=50) self.name.place(x=165, y=20) surname_label = Label(self.window, text="Nazwisko: ") surname_label.place(x=75, y=45) self.surname = Entry(self.window, width=50) self.surname.place(x=165, y=45) ulica_label = Label(self.window, text="Ulica: ") ulica_label.place(x=75, y=70) self.ulica = Entry(self.window, width=50) self.ulica.place(x=165, y=70) home_label = Label(self.window, text="Nr. Domu: ") home_label.place(x=75, y=95) self.home = Entry(self.window, width=50) self.home.place(x=165, y=95) city_label = Label(self.window, text="Miasto: ") city_label.place(x=75, y=120) self.city = Entry(self.window, width=50) self.city.place(x=165, y=120) pc_label = Label(self.window, text="Kod pocztowy: ") pc_label.place(x=75, y=145) self.pc = Entry(self.window, width=50) self.pc.place(x=165, y=145) phone_label = Label(self.window, text="Nr. Telefonu: ") phone_label.place(x=75, y=170) self.phone = Entry(self.window, width=50) self.phone.place(x=165, y=170) mail_label = Label(self.window, text="E-mail: ") mail_label.place(x=75, y=195) self.mail = Entry(self.window, width=50) self.mail.place(x=165, y=195) car_label = Label(self.window, text="Jaki Samochód: ") car_label.place(x=75, y=220) self.car = ttk.Combobox(self.window, textvariable=value, width=50) self.car.place(x=165, y=220) self.car['values'] = self.add_values_to_combobox()
class CreateDeleteWindow(CreateWindow): def __init__(self): super().__init__() self.connection = None def add_buttons(self): delete_button = Button(self.window, text="Usuń rezerwacje", command=self.delete_reservation, width=40, height=3) delete_button.place(x=145, y=150) def add_labels(self): pass def delete_reservation(self): if self.next_step(): if self.check_reservation(): self.create_free_car() sql = "UPDATE rezerwacje SET koniec_rezerwacji = '" + str(date.today()) + \ "' WHERE id_rezerwacji = '" + str(self.res_id) + "'" query = UpdateQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() Tk().wm_withdraw() # to hide the main window messagebox.showinfo('Done', 'Usunięto rezerwacje, do zapłaty: ' + str(self.calculate_cost())) else: Tk().wm_withdraw() # to hide the main window messagebox.showinfo('Error', 'Brak rezerwacji dla tego klienta') else: Tk().wm_withdraw() # to hide the main window messagebox.showinfo('Error', 'Uzupełnij Brakujące pola') def calculate_cost(self): sql = "select rezerwacje.start_rezerwacji, samochody.cena_wypozyczenia FROM rezerwacje INNER JOIN " \ "samochody ON rezerwacje.id_samochodu = samochody.id_samochodu WHERE rezerwacje.id_rezerwacji = '" + \ str(self.res_id) + "'" query_type = SelectQuery() self.connection = ConnectToSQLdb(query_type) table_of_result = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() day_cost = table_of_result[0][1] (rok, mies, day) = str(table_of_result[0][0]).split('-') (a_rok, a_mies, a_day) = str(date.today()).split('-') diff = abs(date(int(a_rok), int(a_mies), int(a_day)) - date(int(rok), int(mies), int(day))).days if diff == 0: return day_cost else: return int(day_cost) * int(diff) def create_free_car(self): sql = "UPDATE samochody SET zarezerwowany = '0' WHERE id_samochodu = " + str(self.get_car_id()) query = UpdateQuery() self.connection = ConnectToSQLdb(query) self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() def next_step(self): if self.mail.get() == '' or self.car.get() == '': return False else: return True def check_reservation(self): email = str(self.mail.get()) car_id = self.get_car_id() sql = "SELECT rezerwacje.id_rezerwacji, klienci.email, samochody.id_samochodu FROM samochody INNER JOIN " \ "(klienci INNER JOIN rezerwacje on klienci.id_klienta = rezerwacje.id_klienta) on " \ "samochody.id_samochodu = rezerwacje.id_samochodu where klienci.email = '" + email + \ "' and samochody.id_samochodu = '" + car_id + "'" query_type = SelectQuery() self.connection = ConnectToSQLdb(query_type) table_of_result = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() if len(table_of_result) > 0: self.res_id = table_of_result[0][0] return True else: return False def get_car_id(self): car = str(self.car.get()) return car[:car.index(" ")] def add_values_to_combobox(self): sql = "SELECT samochody.id_samochodu, samochody.marka, samochody.model from rezerwacje INNER JOIN samochody " \ "on rezerwacje.id_samochodu = samochody.id_samochodu WHERE samochody.zarezerwowany = '1' " \ "and rezerwacje.koniec_rezerwacji IS NULL" tmp_table = [] query_type = SelectQuery() self.connection = ConnectToSQLdb(query_type) table_of_result = self.connection.execute_query(self.connection.get_cursor(), sql) self.close_all() for res in table_of_result: tmp = '' it = Iterator(res) it.set_first() while it.is_done(): tmp += str(it.get_current_item()) + ' ' it.next() tmp_table.append(tmp) print(tmp_table) return tmp_table def close_all(self): self.connection.close_cursor() self.connection.close_connection() def add_other_components(self): value = StringVar() mail_label = Label(self.window, text="E-mail: ") mail_label.place(x=75, y=21) self.mail = Entry(self.window, width=50) self.mail.place(x=165, y=20) car_label = Label(self.window, text="Jaki Samochód: ") car_label.place(x=75, y=45) self.car = ttk.Combobox(self.window, textvariable=value, width=50) self.car.place(x=165, y=45) self.car['values'] = self.add_values_to_combobox()