· 7 years ago · Dec 02, 2018, 03:32 PM
1import tkinter as tk
2from tkinter import messagebox as ms
3from PIL import ImageTk, Image
4import sqlite3
5import datetime
6import openpyxl
7import sys, os
8
9
10with sqlite3.connect('my.db') as db:
11 c = db.cursor()
12
13
14c.execute("CREATE TABLE IF NOT EXISTS user(username TEXT NOT NULL, name TEXT NOT NULL, surname TEXT NOT NULL, password TEXT NOT NULL, employee_id INTEGER PRIMARY KEY);")
15db.commit()
16db.close()
17
18
19mycolor = '#%02x%02x%02x' %(0, 173, 239)
20
21
22
23
24
25class Main:
26
27 def __init__(self, master):
28 # Window
29 self.master = master
30 # Some Usefull variables
31 self.username = tk.StringVar()
32 self.name = tk.StringVar()
33 self.surname = tk.StringVar()
34 self.password = tk.StringVar()
35 self.n_username = tk.StringVar()
36 self.n_password = tk.StringVar()
37 # Create Widgets
38 self.widgets()
39
40 def generate_report(self):
41
42 with sqlite3.connect('my.db') as db:
43 c = db.cursor()
44 name_select = ("SELECT * FROM shifts WHERE name = ? AND surname = ?;")
45 db_to_xls = c.execute(name_select, [(self.name.get()), (self.surname.get())])
46
47 wb = openpyxl.Workbook()
48 sheet = wb.get_sheet_by_name('Sheet')
49
50 for row in db_to_xls:
51 sheet.append(row)
52
53 wb.save('my_shifts.xls')
54
55 #todo everytime after generating - drop table before next month
56
57 def register_day(self):
58
59 date = str(datetime.date.today())
60
61
62 with sqlite3.connect('my.db') as db:
63 c = db.cursor()
64
65 c.execute("""CREATE TABLE IF NOT EXISTS shifts
66 (name TEXT,
67 surname TEXT,
68 date TEXT,
69 FOREIGN KEY (name) REFERENCES user(name),
70 FOREIGN KEY (surname) REFERENCES user(surname));""")
71
72
73 saving_date = ("""INSERT INTO shifts (name, surname, date) VALUES (?, ?, ?)""")
74 c.execute(saving_date, ((self.name.get()), (self.surname.get()), date))
75 db.commit()
76 self.generate_report()
77 c.close()
78 db.close()
79
80
81 ms.showinfo('Zapis zmiany', 'Data zapisana')
82 self.master.destroy()
83
84 # Login Function
85 def login(self):
86 # Establish Connection
87 with sqlite3.connect('my.db') as db:
88 c = db.cursor()
89
90 # Find user If there is any take proper action
91 find_user = ('SELECT * FROM user WHERE username = ? and password = ?')
92 c.execute(find_user, [(self.username.get()), (self.password.get())])
93 result = c.fetchall()
94 if result:
95 [x.destroy() for x in self.master.slaves()]
96 temp1 = tk.Label(self.master.geometry('250x125'), text='Czy dziÅ› jest twoja zmiana ' + self.username.get())
97 temp2 = tk.Button(self.master, text='OK', command= self.register_day)
98 temp1.pack(), temp2.pack()
99 else:
100 ms.showerror('Oops!', 'Username Not Found.')
101
102 def new_user(self):
103 # Establish Connection
104 with sqlite3.connect('my.db') as db:
105 c = db.cursor()
106
107 # Find Existing username if any take proper action
108 find_user = ("SELECT DISTINCT username, name, surname FROM user WHERE username = ? and name = ? and surname = ? ")
109 c.execute(find_user, [(self.n_username.get()), (self.name.get()), (self.surname.get())])
110 if c.fetchall():
111 ms.showerror('Error!', 'Username Taken Try a Diffrent One.')
112 else:
113 ms.showinfo('Success!', 'Account Created!')
114 self.log()
115 # Create New Account
116 insert = ("INSERT INTO user (username, name, surname, password) VALUES(?, ?, ?, ?)")
117 c.execute(insert, [(self.n_username.get()), (self.name.get()), (self.surname.get()), (self.n_password.get())])
118 db.commit()
119
120
121
122 def log(self):
123 self.username.set('')
124 self.password.set('')
125 self.crf.pack_forget()
126 self.head['text'] = 'LOGIN'
127 self.logf.pack()
128
129 def cr(self):
130 self.n_username.set('')
131 self.name.set('')
132 self.surname.set('')
133 self.n_password.set('')
134 self.logf.pack_forget()
135 self.head['text'] = 'Create Account'
136 self.crf.pack()
137
138 # Draw Widgets
139 def widgets(self):
140 self.head = tk.Label(self.master, text='LOGIN', font=('', 35), pady=10)
141 self.head.pack()
142 self.logf = tk.Frame(self.master, padx=10, pady=10)
143 tk.Label(self.logf, text='Username: ', font=('', 20), pady=5, padx=5).grid(sticky='W')
144 tk.Entry(self.logf, textvariable=self.username, bd=5, font=('', 15)).grid(row=0, column=1)
145 tk.Label(self.logf, text='Password: ', font=('', 20), pady=5, padx=5).grid(sticky='W')
146 tk.Entry(self.logf, textvariable=self.password, bd=5, font=('', 15), show='*').grid(row=1, column=1)
147 tk.Button(self.logf, text=' Login ', bd=3, font=('', 15), padx=5, pady=5, command=self.login).grid()
148 tk.Button(self.logf, text=' Create Account ', bd=3, font=('', 15), padx=5, pady=5, command=self.cr).grid(row=2,
149 column=1)
150 self.logf.pack()
151
152 self.crf = tk.Frame(self.master, padx=10, pady=10)
153 tk.Label(self.crf, text='Username: ', font=('', 20), pady=5, padx=5).grid(sticky='W')
154 tk.Entry(self.crf, textvariable=self.n_username, bd=5, font=('', 15)).grid(row=0, column=1)
155 tk.Label(self.crf, text='Name: ', font=('', 20), pady=5, padx=5).grid(sticky='W')
156 tk.Entry(self.crf, textvariable=self.name, bd=5, font=('', 15)).grid(row=1, column=1)
157 tk.Label(self.crf, text='Surname: ', font=('', 20), pady=5, padx=5).grid(sticky='W')
158 tk.Entry(self.crf, textvariable=self.surname, bd=5, font=('', 15)).grid(row=2, column=1)
159 tk.Label(self.crf, text='Password: ', font=('', 20), pady=5, padx=5).grid(sticky='W')
160 tk.Entry(self.crf, textvariable=self.n_password, bd=5, font=('', 15), show='*').grid(row=3, column=1)
161 tk.Button(self.crf, text='Create Account', bd=3, font=('', 15), padx=5, pady=5, command=self.new_user).grid()
162 tk.Button(self.crf, text='Go to Login', bd=3, font=('', 15), padx=5, pady=5, command=self.log).grid(row=4,
163 column=1)
164
165
166
167
168if __name__ == '__main__':
169 root = tk.Tk()
170 root.title('Login Form')
171 Main(root)
172 root.mainloop()