-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.py
227 lines (197 loc) · 6.76 KB
/
database.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
import sqlite3
conn = sqlite3.connect('libdata.db')
# boot program
def create_db(db_name,t_h):
c = conn.cursor()
c.execute("CREATE TABLE IF NOT EXISTS \
'{}'({})".format(db_name,t_h))
c.close()
print("Menghubungkan ke Database")
create_db("lock","activation INTEGER, password TEXT")
create_db("nama_perpus","nama TEXT")
create_db("daftar_buku","title TEXT,writer TEXT, genre TEXT, width INTEGER, qty INTEGER, location TEXT, idbook TEXT" )
create_db("daftar_anggota","name TEXT,address TEXT, usia INTEGER, idmember TEXT, dt TEXT")
create_db("peminjaman","tanggal_peminjaman TEXT, judul_buku TEXT, peminjam TEXT, batas_pengembalian TEXT, status INTEGER, id_buku TEXT, id_peminjam TEXT")
print("Succes")
# Set default lock
print ("Menghubungkan ke Keamanan")
c = conn.cursor()
c.execute('''SELECT * FROM "lock"''')
lock=c.fetchall()
if lock == []:
c.execute("INSERT INTO 'lock' VALUES(0,NULL)")
conn.commit()
print("Succes")
# Set default Name
print("Menghubungkan ke Profil")
c.execute('''SELECT * FROM "nama_perpus"''')
l=c.fetchall()
if l == []:
c.execute("INSERT INTO 'nama_perpus' VALUES ('X')")
conn.commit()
c.close()
print("Succes")
# lock
def lock():
c = conn.cursor()
c.execute('''SELECT * FROM "lock"''')
lock = c.fetchall()[0][0]
c.close()
return lock
def pw():
c = conn.cursor()
c.execute('''SELECT * FROM "lock"''')
lock = c.fetchall()[0][1]
c.close()
return lock
def set_pw(x):
print(x)
c = conn.cursor()
c.execute(f'''UPDATE "main"."lock" SET "password"='{x}' WHERE "_rowid_"="1"''')
conn.commit()
c.execute('''UPDATE "main"."lock" SET "activation"='1' WHERE "_rowid_"="1"''')
conn.commit()
c.close()
def off_lock():
c= conn.cursor()
c.execute('''UPDATE "main"."lock" SET "activation"='0' WHERE "_rowid_"="1"''')
conn.commit()
c.close()
# library name
def l_n():
c = conn.cursor()
c.execute('''SELECT * FROM "nama_perpus"''')
l=c.fetchall()
l_n=l[0][0]
c.close()
return l_n
def update_ln(x):
c = conn.cursor()
c.execute("UPDATE nama_perpus SET nama = '%s' WHERE _rowid_ IN ('1')"%(x))
conn.commit()
c.close
# katalog
def insert_book(nama_buku, pengarang, jenis_buku, w_buku, q_buku, letak_buku, id_buku):
c = conn.cursor()
c.execute("INSERT INTO daftar_buku VALUES('{}', '{}', '{}', '{}', '{}', '{}', '{}')".format(nama_buku, pengarang, jenis_buku, w_buku, q_buku, letak_buku, id_buku))
conn.commit()
c.close()
def load_book():
c = conn.cursor()
c.execute('''SELECT * FROM "daftar_buku" ORDER BY "title" ASC''')
katalog=c.fetchall()
c.close()
return katalog
def select_book(x):
c = conn.cursor()
c.execute(f"SELECT title FROM daftar_buku WHERE idbook = '{x}'")
select=c.fetchall()
c.close()
return select
def delete_book(x):
c = conn.cursor()
c.execute(f"DELETE FROM daftar_buku WHERE idbook = '{x}'")
conn.commit()
c.close()
def jumlah_buku():
c = conn.cursor()
c.execute('''SELECT qty FROM "daftar_buku"''')
q_b= c.fetchall()
q_buku=0
for i in range(len(q_b)):
q_buku = q_buku+q_b[i][0]
return q_buku
def filter_book(x):
c = conn.cursor()
c.execute('''SELECT "_rowid_",* FROM "main"."daftar_buku" WHERE "title" LIKE '%{}%' ORDER by title ASC'''.format(x))
l=c.fetchall()
c.close()
return l
def clear_book():
c = conn.cursor()
c.execute('''DELETE FROM "daftar_buku"''')
conn.commit()
c.close()
def width_book(x):
c = conn.cursor()
c.execute(f"SELECT width FROM daftar_buku WHERE idbook = '{x}'")
l = c.fetchall()[0][0]
c.close()
return l
def q_buku(x):
c =conn.cursor()
c.execute(f"SELECT qty FROM daftar_buku WHERE idbook = '{x}'")
l = c.fetchall()[0][0]-1
c.close()
return l
# keanggotaan
def insert_members(nama_anggota, alamat_anggota, umur_anggota, id_anggota, tanggal):
c = conn.cursor()
c.execute('''INSERT INTO daftar_anggota VALUES('{}', '{}', '{}', '{}', "{}")'''.format(nama_anggota, alamat_anggota, umur_anggota, id_anggota, tanggal))
print('ya')
conn.commit()
print('ya')
c.close()
def members():
c = conn.cursor()
c.execute('''SELECT * FROM "daftar_anggota" ORDER BY "title" ASC''')
l=c.fetchall()
c.close()
return l
def filter_member(x):
c = conn.cursor()
c.execute(f'''SELECT "_rowid_",* FROM "main"."daftar_anggota" WHERE "idmember" LIKE '%{x}%' ORDER by name ASC''')
l=c.fetchall()
c.close()
return l
def select_member(x):
c = conn.cursor()
c.execute(f"SELECT name FROM daftar_anggota WHERE idmember = '{x}'")
l=c.fetchall()
c.close
return l
def delete_members(x):
c = conn.cursor()
c.execute(f"DELETE FROM daftar_anggota WHERE idmember = '{x}'")
conn.commit()
c.close()
def clear_members():
c = conn.cursor()
c.execute('''DELETE FROM "daftar_anggota"''')
conn.commit()
c.close()
def jumlah_anggota():
c = conn.cursor()
c.execute('''SELECT idmember FROM "daftar_anggota"''')
q_a = c.fetchall()
q_member=len(q_a)
c.close()
return q_member
# peminjaman
def load_peminjaman():
c = conn.cursor()
c.execute('''SELECT * FROM "peminjaman" ORDER BY "tanggal_peminjaman" ASC''')
l=c.fetchall()
c.close()
return l
def insert_peminjaman(tanggal,n_b,n_a,batas_p,k_b,k_m):
c = conn.cursor()
c.execute('''INSERT INTO peminjaman VALUES("{}", "{}", "{}", "{}","{}","{}","{}")'''.format(tanggal,n_b,n_a,batas_p,1,k_b,k_m))
conn.commit()
c.close
def update_qbook(x,y):
c= conn.cursor()
c.execute(f"UPDATE daftar_buku SET qty = {x} WHERE idbook = '{y}'")
conn.commit()
c.close()
def kembali(k_b):
c = conn.cursor()
c.execute(f"UPDATE peminjaman SET status =2 WHERE id_buku = '{k_b}'")
conn.commit()
c.execute(f"SELECT qty FROM daftar_buku WHERE idbook = '{k_b}'")
q_buku = c.fetchall()
qty = q_buku[0][0]+1
c.execute(f"UPDATE daftar_buku SET qty = {qty} WHERE idbook = '{k_b}'")
conn.commit()
c.close()
# ^_^