-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsql.py
executable file
·218 lines (189 loc) · 8.48 KB
/
sql.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
# -*- coding: utf-8 -*-
from fetch import *
restaurant_list = get_info()
conn = sqlite3.connect('bookings.db')
c = conn.cursor()
def create_table():
c.execute("CREATE TABLE IF NOT EXISTS bord(table_id TEXT PRIMARY KEY, chairs INTEGER)")
c.execute("CREATE TABLE IF NOT EXISTS reservations(id INTEGER PRIMARY KEY, table_id TEXT, db_booking_date TEXT, pax INTEGER, customer TEXT, db_booking_start DATETIME, db_booking_end DATETIME)")
def data_entry():
c.execute('''INSERT INTO bord(table_id, chairs)
VALUES
("Restaurant Eld 5", 2),
("Restaurant Eld 6", 2),
("Restaurant Eld 7", 2),
("Restaurant Eld 8", 2),
("Restaurant Eld 9", 2),
("Restaurant Eld 10", 8),
("Restaurant Eld 11", 2),
("Restaurant Eld 12", 2),
("Restaurant Eld 13", 2),
("Restaurant Eld 14", 2),
("Restaurant Eld 17", 8),
("Restaurant Eld 18", 6),
("Restaurant Eld 19", 6),
("Restaurant Eld 20", 8),
("Restaurant Eld 21", 2),
("Restaurant Eld 22", 2),
("Restaurant Eld 23", 2),
("Restaurant Eld 24", 2),
("Restaurant Eld 25", 4),
("Restaurant Eld 26", 4),
("Restaurant Eld 27", 4),
("Restaurant Eld 28", 4),
("Restaurant Eld 29", 6),
("Restaurant Eld 30", 6),
("Restaurant Eld 31", 2),
("Restaurant Eld 32", 4),
("Restaurant Eld 40", 16),
("Restaurant Eld 43", 8),
("Restaurant Eld 44", 10),
("Frati/ØX 10", 4),
("Frati/ØX 11", 4),
("Frati/ØX 13", 4),
("Frati/ØX 14", 4),
("Frati/ØX 16", 4),
("Frati/ØX 18", 4),
("Frati/ØX 19", 4),
("Frati/ØX 22", 4),
("Frati/ØX 23", 4),
("Frati/ØX 26", 4),
("Frati/ØX 27", 4),
("Frati/ØX 30", 4),
("Frati/ØX 36", 4),
("Frati/ØX 37", 4),
("Frati/ØX 43", 4),
("Frati/ØX 50", 4),
("Frati/ØX 51", 4),
("Frati/ØX 52", 4),
("Frati/ØX 53", 4),
("Frati/ØX 12", 6),
("Frati/ØX 33", 6),
("Frati/ØX 45", 6),
("Frati/ØX 46", 6),
("Frati/ØX 20", 8),
("Frati/ØX 21", 8),
("Frati/ØX 24", 8),
("Frati/ØX 25", 8),
("Frati/ØX 31", 8),
("Frati/ØX 32", 8),
("Frati/ØX 35", 8),
("Frati/ØX 60", 8)
''')
conn.commit()
c.close()
conn.close()
# Fetches all existing reservations from bookatable server and creates a new entry if it does not already exsist.
def add_new_reservations():
conn = sqlite3.connect('bookings.db')
c = conn.cursor()
counter = 0
for restaurant in restaurant_list:
for thing in restaurant:
for reservation in thing:
for table in reservation.get('TableNrs'):
while counter < len(reservation.get('TableNrs')):
restaurant = reservation.get('RestaurantName')
start_temp = (reservation.get('StartDateTime')[6:16])
db_booking_start = datetime.fromtimestamp(float(start_temp))
end_temp = (reservation.get('EndDateTime')[6:16])
db_booking_end = datetime.fromtimestamp(float(end_temp))
db_booking_date = datetime.fromtimestamp(int(start_temp)).strftime('%Y-%m-%d')
customer = reservation.get('CustomerName')
pax = reservation.get('NrOfGuest')
table_id = restaurant + ' ' + str(reservation.get('TableNrs')[counter])
new_reservation = [table_id, db_booking_start, db_booking_end, pax, customer]
counter+=1
c.execute("SELECT table_id FROM reservations WHERE table_id=? AND db_booking_start = ? AND db_booking_end = ? AND pax = ? AND customer = ?",(table_id, db_booking_start, db_booking_end, pax, customer))
#create new reservation if reservation in check but not in database
data = c.fetchone()
if data is None:
print ("Creating new reservation.")
c.execute('''INSERT INTO reservations(table_id, db_booking_start, db_booking_end, db_booking_date, pax, customer ) VALUES(?,?,?,?,?,?)''', (table_id, db_booking_start, db_booking_end, db_booking_date, pax, customer))
else:
pass
counter=0
conn.commit()
c.close()
conn.close()
# Loops over all reservations in local database and deletes them if they are noe longer found in bookatables database.
def delete_old():
# connect to database
conn = sqlite3.connect('bookings.db')
c = conn.cursor()
c.execute('''SELECT table_id, db_booking_start, db_booking_end, db_booking_date, pax, customer FROM reservations''')
old = c.fetchall()
new = []
counter = 0
for restaurant in restaurant_list:
for thing in restaurant:
for reservation in thing:
for table in reservation.get('TableNrs'):
while counter < len(reservation.get('TableNrs')):
restaurant = reservation.get('RestaurantName')
start_temp = (reservation.get('StartDateTime')[6:16])
db_booking_start = str(datetime.fromtimestamp(float(start_temp)))
end_temp = (reservation.get('EndDateTime')[6:16])
db_booking_end = str(datetime.fromtimestamp(float(end_temp)))
db_booking_date = datetime.fromtimestamp(int(start_temp)).strftime('%Y-%m-%d')
customer = reservation.get('CustomerName')
pax = reservation.get('NrOfGuest')
table_id = restaurant + ' ' + str(reservation.get('TableNrs')[counter])
new_reservation = (table_id, db_booking_start, db_booking_end, db_booking_date, pax, customer)
new.append(new_reservation)
counter+=1
counter = 0
c.execute('''SELECT id FROM reservations ORDER BY ROWID ASC LIMIT 1 ''')
rowid_get = c.fetchall()
rowid_loop = [x[0] for x in rowid_get]
rowid_counter = rowid_loop[0] -1
for row in old:
rowid_counter += 1
if row not in new:
print ("Reservation not found. Deleting")
c.execute("DELETE FROM reservations WHERE id = ?", (rowid_counter,));
conn.commit()
c.close()
conn.close()
# Iterates over all restaurants over a timeperiod to identify all tables in restaurant.
# We guess at chairs by keeping the largest recorded value up to a limit. Skip reservations with multiple tables
def find_all_tables():
conn = sqlite3.connect('bookings.db')
c = conn.cursor()
for restaurant in restaurant_list:
for object in restaurant:
for reservation in object:
for table in reservation.get('TableNrs'):
if len(reservation.get('TableNrs')) < 2:
restaurant = reservation.get('RestaurantName')
# start_temp = (reservation.get('StartDateTime')[6:16])
# db_booking_start = datetime.fromtimestamp(float(start_temp))
# end_temp = (reservation.get('EndDateTime')[6:16])
# db_booking_end = datetime.fromtimestamp(float(end_temp))
# db_booking_date = datetime.fromtimestamp(int(start_temp)).strftime('%Y-%m-%d')
# customer = reservation.get('CustomerName')
# new_reservation = [table_id, db_booking_start, db_booking_end, pax, customer]
pax = reservation.get('NrOfGuest')
table_id = restaurant + ' ' + str(reservation.get('TableNrs')[0])
# update or insert query for every table and their chairs
# c.execute('''INSERT OR REPLACE INTO bord(table_id, chairs) VALUES(?,?)''', (table_id, pax))
# conn.commit()
c.execute('''SELECT table_id, chairs from bord WHERE table_id = ?''', (table_id, ))
conn.commit()
data = c.fetchone()
if data:
if data[1] < pax:
c.execute('''INSERT OR REPLACE INTO bord(table_id, chairs) VALUES(?,?)''', (table_id, pax))
else:
pass
else:
pass
# c.close()
# conn.close()
#create_table()
find_all_tables()
#data_entry()
#add_new_reservations()
#delete_old()
# c.close()
# conn.close()