class Login: def __init__(self): self.db = DBConnect() # self.conn = pymysql.connect('localhost', 'root', 'SqlAccount!23', 'DMP', charset='utf8') # self.c = self.conn.cursor() print('połączenie ustanowione!') self.menu() def menu(self): print("Witamy Cię w Programie zarządzania rysunkami DMP:\n") loop = True while loop: option = input( "Wybierz opcję: Z-zaloguj, N-nowy użytkownik,Q-wyjście:" ).upper() while option == "": option = input( "Wybierz opcję działania: Z-zaloguj, N-nowy użytkownik,Q-wyjście: " ).upper() if option == "Q": loop = False elif option == "N": login = input("Podaj login dla nowego użutkowsnika: ") password = input("Podaj hasło dla nowego użytkownika: ") query = "INSERT INTO USERS (login_users, pass_users) values ('" + login + "', '" + password + "');" self.db.execute(query) self.db.conn.commit() # self.c.execute( # "INSERT INTO USERS (login_users, pass_users) values ('" + login + "', '" + password + "');") self.logIn(login, password) elif option == "Z": login = input("Podaj login: "******"Podaj hasło: ") self.logIn(login, password) elif option == "Q": loop = False else: print("Brak takiej opcji!") def logIn(self, _login, _password): query = "SELECT * FROM USERS WHERE login_users = '" + _login + "' AND pass_users = '" + _password + "';" # self.db.fetchAll(query) # self.c.execute() # logRes = self.c.fetchall() logRes = self.db.fetchAll(query) if len(logRes) == 1: print("Zalogowano!") # print(logRes) logRes_id = logRes[0] currentUser = User(logRes[0][0]) else: print("Niepoprawne login lub/i hasło!")
class Project: def __init__(self, _project_id, _user_id): self.project_id = _project_id self.user_id = _user_id self.db = DBConnect() self.menu() def menu(self): loop = True while loop: self.availableOptions() option = input("Wybierz opcję").upper() while option == "": option = input("Wybierz opcję").upper() if option == "D": self.addDrawing() # self.deleteDrawing() elif option == "P": self.showDrawings() elif option == "Z": self.showPaidDrawings() elif option == "N": self.showUnpaidDrawings() elif option == "M": self.modifyDrawingData() elif option == "F": self.findDrawing() # elif option == "W": # self.selectDrawingsByDate() elif option == "Q": loop = False else: print("Nieznana opcja!") def availableOptions(self): print("Dostępne opcje:") print("D - dodaj nowy rysunek") # print("U - usuń rysunek") print("P - pokaż wszystkie rysunki") print("Z - pokaż listę rozliczonych rysunków") print("N - pokaż listę nierozliczonych rysunków") print("M - modyfikuj dane o rysunku") print("F - szukaj rysunku") # print("W - wybierz rysunki po dacie") print("Q-wyjście") def showDrawings(self): print("Lista rysunków.") drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \ " from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \ "join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \ "where Users_id_Users = " + str(self.user_id) + ";" result = self.db.fetchAll(drawingsQuery) print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' % ("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu")) for row in result: print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6])) def addDrawing(self): drawing_name = self.nonOptionalInput("Podaj nazwę rysunku: ") drawing_num = self.nonOptionalInput("Podaj numer rysunku: ") width = self.nonOptionalInput("Podaj szerokość rysunku: ") height = self.nonOptionalInput("Podaj wysokość rysunku: ") individual_rate = self.nonOptionalInput("Podaj indywidualną stawkę: ") paid = self.nonOptionalInput( "Czy rysunek jest rozliczony? [Tak=>1|Nie=>0]: ") insertQuery = "INSERT INTO drawings (drawing_name, drawing_num, width, height, individual_rate, id_projects," \ " paid, id_Users) values ('" + drawing_name + "', '" + drawing_num + "', " + width + "" \ ", " + height + ", "+ individual_rate+", "+str(self.project_id)+", "+paid+", "+str(self.user_id)+")" self.db.execute(insertQuery) self.db.conn.commit() # id_drawings, drawing_name, drawing_num, width, height, individual_rate, id_projects, paid, id_Users # print("Dodawanie rysunku") # def deleteDrawing(self): # print("Usuwanie rysunku") def showPaidDrawings(self): print("Lista rozliczonych rysunków") drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \ " from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \ "join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \ "where (Users_id_Users = " + str(self.user_id) + " AND " \ "paid =1 AND p.id_projects = " + str(self.project_id) + ");" result = self.db.fetchAll(drawingsQuery) print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' % ("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu")) for row in result: print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6])) def showUnpaidDrawings(self): print("Lista nierozliczonych rysunków") drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \ " from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \ "join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \ "where (Users_id_Users = " + str(self.user_id) + " AND " \ "paid =0 AND p.id_projects = " + str(self.project_id) + ");" result = self.db.fetchAll(drawingsQuery) print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' % ("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu")) for row in result: print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6])) def modifyDrawingData(self): print("Modyfikacja rysunku") self.showDrawings() drawingId = self.nonOptionalInput( "Wybierz ID rysunku do modyfikacji: ") query = "Select id_drawings, drawing_name, drawing_num, width, height, individual_rate, id_projects," \ " paid, id_Users from projects where id_drawings ="+drawingId+" " result = self.db.fetchAll(query) drawing_name = result[1] drawing_num = result[2] width = result[3] height = result[4] rate = result[5] paid = result[7] drawing_name = self.optionalInput( "Podaj nową nazwę rysunku lub naciśnij ENTER, aby nie zmieniać: ", drawing_name) drawing_num = self.optionalInput( "Podaj nowy numer rysunku lub naciśnij ENTER, aby nie zmieniać: ", drawing_num) width = self.optionalInput( "Podaj nową szerokość lub naciśnij ENTER, aby nie zmieniać: ", height) height = self.optionalInput( "Podaj nową wysokość lub naciśnij ENTER, aby nie zmieniać: ", width) rate = self.optionalInput( "Podaj nową stawkę lub naciśnij ENTER, aby nie zmieniać: ", rate) paid = self.optionalInput( "Podaj status [rozliczony=>1|nierozliczony=>0] lub naciśnij ENTER, aby nie zmieniać: ", paid) query = "UPDATE drawings SET drawing_name ='"+drawing_name+"', drawing_num = '"+drawing_num+"', width ="+\ width+", height= "+height+", individual_rate="+rate+", paid="+paid+" where id_drawings="+drawingId+";" self.db.execute(query) print("Zmodyfikowano!") # id_drawings, drawing_name, drawing_num, width, height, individual_rate, id_projects, paid, id_Users def findDrawing(self): print("Wyszukiwanie rysunku") phrase = self.nonOptionalInput("Podaj frazę do wyszukania: ") findQuery = "Select * from drawings where drawing_name" \ " like '%" + phrase + "%' or drawing_num like '%" + phrase + "%' " result = self.db.fetchAll(findQuery) print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' % ("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu")) for row in result: print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6])) # def selectDrawingsByDate(self): # print("wybierz rysunki po dacie") @staticmethod def nonOptionalInput(napis): result = input(napis) while result == "": result = input(napis) return result @staticmethod def optionalInput(napis, data): userInput = input(napis) if userInput != "": result = userInput else: result = data return result
class User: def __init__(self, _id): self.user_id = _id self.db = DBConnect() # print("user ID: "+str(self.user_id)) self.conn = pymysql.connect('localhost', 'root', 'SqlAccount!23', 'DMP', charset='utf8') self.c = self.conn.cursor() self.menu() def availableOptions(self): print("Dostępne opcje:") print("D-dodaj nowego klienta") print("K-pokaż klientów") print("P-pokaż projekty") print("A-dodaj projekt") # print("U-usuń projekt") print("M-zarządzaj wybranym projektem") print("R - Pokaż wszystkie rysunki") print("Z - Pokaż listę rozliczonych rysunków") print("N - Pokaż listę nierozliczonych rysunków") print("Q-wyjście") def menu(self): loop = True while loop: self.availableOptions() option = input("Wybierz opcję: ").upper() while option == "": option = input("Wybierz opcję: ").upper() if option == "D": self.addClient() elif option == "K": self.showClients() elif option == "P": self.showProjects() elif option == "A": self.addProject() # elif option == "U": # self.deleteProject() elif option == "R": self.showDrawings() elif option == "M": self.manageProject() elif option == "Z": self.showPaidDrawings() elif option == "N": self.showUnpaidDrawings() elif option == "Q": loop = False else: print("Nieznana opcja!") def addClient(self): print("Dodawanie klienta: ") clientName = input("Podaj nazwę klienta [pole obowiązkowe]: ") while clientName == "": clientName = input("Podaj nazwę klienta [pole obowiązkowe]: ") clientFirstName = input("Podaj imię klienta [pole opcjonalne]: ") clientLastName = input("Podaj nazwisko klienta [pole opcjonalne]: ") clientPhoneNumber = input("Podaj numer telefonu klienta [pole opcjonalne]: ") clientEmail = input("Podaj adres email klienta [pole obowiązkowe]: ") while clientEmail == "": clientEmail = input("Podaj adres email klienta [pole obowiązkowe]: ") query = "INSERT INTO CUSTOMERS (customers_name, first_name, last_name, phone_number, email, id_Users)" \ " values ('" + clientName + "', '" + clientFirstName + "', '" + clientLastName + "', " + \ clientPhoneNumber.replace(" ", "") + ", '" + clientEmail + "'," + str(self.user_id) + ")" self.c.execute(query) self.conn.commit() def showProjects(self): print("Lista projektów\n") query = "select id_projects, project_name, customers_name, project_date, project_deadline,rate_per_drawing " \ "from projects" \ " as p join customers as c on (c.id_customers= p.id_customers) " \ "where id_users = " + str(self.user_id) + " order by project_name" self.c.execute(query) result = self.c.fetchall() print('|%3s|%20s|%20s|%20s|%20s|%20s' % ( "ID", "Nazwa projektu", "Nazwa klienta", "start projektu", "termin projektu", "stawka za A0 [PLN]")) for row in result: print('|%3i|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5])) def showClients(self): # pokazanie listy projektów print("Lista klientów\n") query = "select * from customers where id_users = " + str(self.user_id) + " order by customers_name" self.c.execute(query) result = self.c.fetchall() print('|%3s|%20s|%20s|%20s|%12s|%20s' % ( "ID", "Nazwa klienta", "Imię klienta", "Nazwisko klienta", "nr telefonu", "adres e-mail")) for row in result: print('|%3i|%20s|%20s|%20s|%12s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5])) def addProject(self): # pokazanie listy projektów self.showClients() client = input("Wybierz ID klienta do dodania projektu: ") while client == "": print("Musisz wybrać ID klienta") client = input("Wybierz ID klienta do dodania projektu: ") projectName = input("Podaj nazwę projektu: ") while projectName == "": print("Musisz podać nazwę projektu!") projectName = input("Podaj nazwę projektu: ") projectDate = input("Podaj datę przyjęcia projektu [YYYY-MM-DD]: ") projectDeadline = input("Podaj termin wykonania projektu [YYYY-MM-DD]: ") projectRate = input("Podaj stawkę za A0: ") self.c.execute("SET FOREIGN_KEY_CHECKS=0") insertQueryToProjects = "INSERT INTO projects (project_name, rate_per_drawing, project_date, " \ "project_deadline, id_customers) values ('" + projectName + "', " + projectRate +\ ", '"+projectDate+"', '"+projectDeadline+"', " + client+")" self.c.execute(insertQueryToProjects) self.conn.commit() query = "INSERT INTO projects_has_users (Users_id_Users, customers_id_customers) " \ "values (" + str(self.user_id) + ", " + client + ")" self.c.execute(query) self.conn.commit() print("Dodano projekt!") print(" ") # def deleteProject(self): # print("1") def manageProject(self): self.showProjects() selectedProject = input("Podaj ID projektu do zarządzania: ") projectManage = Project(selectedProject, self.user_id) projectManage.menu() def showDrawings(self): drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \ " from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \ "join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \ "where Users_id_Users = "+ str(self.user_id) + ";" result = self.db.fetchAll(drawingsQuery) print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' % ("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu")) for row in result: print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6])) def showUnpaidDrawings(self): drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \ " from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \ "join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \ "where (Users_id_Users = " + str(self.user_id) + " AND paid =0);" result = self.db.fetchAll(drawingsQuery) print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' % ("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu")) for row in result: print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6])) def showPaidDrawings(self): drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \ " from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \ "join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \ "where (Users_id_Users = " + str(self.user_id) + " AND paid =1);" result = self.db.fetchAll(drawingsQuery) print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' % ("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu")) for row in result: print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6]))