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