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