PrototypeApplication: app.py

File app.py, 14.7 KB (added by 212054, 3 weeks ago)
Line 
1import pandas as pd
2from sqlalchemy import create_engine, text
3
4def connect_to_database():
5 # Create SQLAlchemy engine (adjust connection string as needed)
6 connection_string = "postgresql://db_202425z_va_prj_izgradi_owner:36cb70bb0e32@localhost:9999/db_202425z_va_prj_izgradi"
7 return create_engine(connection_string)
8
9def login(engine):
10 username = input("Enter username: ").strip()
11 password = input("Enter password: ").strip()
12 try:
13 query = f"SELECT uid, privilegii FROM korisnik WHERE username = '{username}' AND pwd = '{password}'"
14 df = pd.read_sql(query, engine)
15
16 if not df.empty:
17 return df.iloc[0]['uid'], df.iloc[0]['privilegii']
18 else:
19 print("Invalid username or password.")
20 return None, None
21 except Exception as e:
22 print(f"Error during login: {e}")
23 return None, None
24
25def menu_admin():
26 print("\nAdmin Menu")
27 print("1. Nabavki za objekt")
28 print("2. Insert into Nabavka")
29 print("3. Get General Objekt Details")
30 print("4. Get Dobavuvac Details")
31 print("5. Get Podizveduvac Details")
32 print("6. Insert into Dobavuvac")
33 print("7. Custom Query")
34 print("0. Exit")
35
36def menu_1(): #supervisor menu
37 print("\nStored Procedure Menu")
38 print("1. Unapproved Nabavki")
39 print("2. Approved Nabavki")
40 print("3. Approve Nabavka")
41 print("4. Get Object Details")
42 print("0. Exit")
43
44def menu_2(): #subcontractor menu
45 print("\nMenu")
46 print("1. Insert into Nabavka")
47 print("2. Get Object Details")
48 print("0. Exit")
49
50def call_inline_query(engine, sql_query):
51 """Execute a query and print results if any."""
52 try:
53 df = pd.read_sql(text(sql_query), engine)
54 if not df.empty:
55 print("\nResults:")
56 print(df.to_string(index=False))
57 else:
58 print("No results found.")
59 except Exception as e:
60 print(f"Error: {e}")
61
62def available_dobavuvaci():
63 engine = connect_to_database()
64 query = "SELECT dobavuvac_ime FROM dobavuvac"
65 df = pd.read_sql(query, engine)
66 print(df)
67 return df['dobavuvac_ime'].tolist()
68
69def main():
70 engine = connect_to_database()
71 uid, privileges = login(engine)
72 if uid is None or privileges is None:
73 print("Login failed. Exiting...")
74 return
75
76 while True:
77 if privileges == 0: # ADMIN
78 menu_admin()
79 choice = input("Enter your choice: ").strip()
80 if choice == "0":
81 print("Exiting...")
82 break
83
84 elif choice == "1":
85 objektid = input("Enter Objekt ID: ").strip()
86 sql_query = f"""
87 SELECT
88 o.objekt_id,
89 nb.nabavka_id,
90 nb.datum AS datum_na_nabavka,
91 nb.predmet,
92 nb.kolicina,
93 nb.cena
94 FROM objekt o
95 JOIN nameneta_za nz ON nz.objekt_id = o.objekt_id
96 JOIN nabavka nb ON nb.nabavka_id = nz.nabavka_id
97 WHERE o.objekt_id = {objektid};
98 """
99 df = pd.read_sql(sql_query, con=engine)
100 print(df)
101 elif choice == "2":
102 dobavuvaci = available_dobavuvaci()
103 # Inlined procedure: InsertOrderWithRelations(...)
104 try:
105 pod_id = int(input("Enter Podizveduvac ID: ").strip())
106 dobav_ime = input("Enter Dobavuvac Name: ").strip()
107 datum = input("Enter Date (YYYY-MM-DD): ").strip()
108 cena = float(input("Enter Price: ").strip())
109 kolicina = float(input("Enter Quantity: ").strip())
110 predmet = input("Enter Item Name: ").strip()
111 except Exception as e:
112 print(f"Invalid input: {e}")
113 continue
114
115 sql_query = f"""
116 WITH new_order AS (
117 INSERT INTO nabavka(datum, cena, kolicina, predmet)
118 VALUES ('{datum}', {cena}, {kolicina}, '{predmet}')
119 RETURNING nabavka_id
120 ),
121 pobaruva_insert AS (
122 INSERT INTO pobaruva(nabavka_id, uid_p)
123 SELECT nabavka_id, {pod_id}
124 FROM new_order
125 ),
126 ispolnuva_insert AS (
127 INSERT INTO ispolnuva(nabavka_id, dobavuvac_ime)
128 SELECT nabavka_id, '{dobav_ime}'
129 FROM new_order
130 )
131 SELECT nabavka_id FROM new_order;
132 """
133
134 with engine.begin() as conn:
135 result = conn.execute(text(sql_query)).fetchone()
136 if result is None:
137 print("Error inserting order.")
138 continue
139 print("Order inserted successfully.")
140
141 elif choice == "3":
142 # Inlined procedure: GetObjektDetails(input_Objekt_ID)
143 # For simplicity only the first SELECT (general details) is inlined.
144 try:
145 objekt_id = int(input("Enter Objekt ID: ").strip())
146 except Exception as e:
147 print(f"Invalid Objekt ID: {e}")
148 continue
149 sql_query = f"""
150 SELECT
151 o.objekt_id,
152 o.tip AS objekt_type,
153 o.adresa AS objekt_address,
154 vi.budzet AS objekt_budget,
155 n.nadzor_ime
156 FROM objekt o
157 LEFT JOIN vo_izgradba vi ON o.objekt_id = vi.objekt_id
158 LEFT JOIN nadgleduva nd ON o.objekt_id = nd.objekt_id
159 LEFT JOIN nadzor n ON nd.uid_n = n.uid
160 WHERE o.objekt_id = {objekt_id}
161 GROUP BY o.objekt_id, o.tip, o.adresa, vi.budzet, n.nadzor_ime;
162 """
163 call_inline_query(engine, sql_query)
164
165 elif choice == "4":
166 # Inlined procedure: GetDobavuvacDetails(input_Dobavuvac_Ime)
167 # Only the basic supplier info is inlined.
168 dobavuvac_name = input("Enter Dobavuvac Name: ").strip()
169 sql_query = f"""
170 SELECT
171 d.dobavuvac_ime,
172 d.klasa AS klasa_na_dobavuvac,
173 d.tip AS tip_na_dobavuvac,
174 d.poteklo AS poteklo_na_dobavuvac
175 FROM dobavuvac d
176 WHERE d.dobavuvac_ime = '{dobavuvac_name}';
177 """
178 call_inline_query(engine, sql_query)
179
180
181 elif choice == "5":
182 sql_query = f"""
183 SELECT
184 p.podizveduvac_ime,
185 p.struka,
186 p.br_vraboteni,
187 p.saatnica
188 FROM podizveduvac p;
189 """
190 call_inline_query(engine, sql_query)
191
192 elif choice == "6":
193 dobavuvac_name = input("Enter Dobavuvac Name: ").strip()
194 dobavuvac_class = input("Enter Dobavuvac Class (A/B/C): ").strip()
195 dobavuvac_type = input("Enter Dobavuvac Type: ").strip()
196 dobavuvac_origin = input("Enter Dobavuvac Origin: ").strip()
197
198 sql_query = f"""
199 INSERT INTO Dobavuvac (Dobavuvac_Ime, Klasa, Tip, Poteklo)
200 VALUES ('{dobavuvac_name}', '{dobavuvac_class}', '{dobavuvac_type}', '{dobavuvac_origin}');
201 """
202 try:
203 with engine.begin() as conn:
204 conn.execute(text(sql_query))
205 print("Dobavuvac inserted successfully.")
206 except Exception as e:
207 print(f"Error inserting dobavuvac: {e}")
208
209
210 elif choice == "7":
211 query = input("Enter query: ")
212
213 if query.startswith("SELECT"):
214 call_inline_query(engine, query)
215 else:
216 try:
217 with engine.begin() as conn:
218 conn.execute(text(query))
219 except Exception as e:
220 print(f"Error executing query: {e}")
221
222
223 else:
224 print("Invalid choice. Please try again.")
225
226
227
228 elif privileges == 1: # SUPERVISOR
229 menu_1()
230 choice = input("Enter your choice: ").strip()
231 if choice == "0":
232 print("Exiting...")
233 break
234
235 elif choice == "1":
236 # Inlined: Display_Nabavki_For_Nadzor (all unapproved orders)
237 sql_query = f"""
238 WITH select_objekt_nabavki AS (
239 SELECT
240 o.objekt_id,
241 nb.nabavka_id,
242 nb.datum AS datum_na_nabavka,
243 nb.predmet,
244 nb.kolicina,
245 nb.cena
246 FROM objekt o
247 JOIN nadgleduva n ON o.objekt_id = n.objekt_id
248 JOIN nameneta_za nz ON nz.objekt_id = o.objekt_id
249 JOIN nabavka nb ON nb.nabavka_id = nz.nabavka_id
250 WHERE n.uid_n = {uid}
251 )
252 SELECT *
253 FROM select_objekt_nabavki son
254 LEFT JOIN odobruva o ON son.nabavka_id = o.nabavka_id;
255 """
256 call_inline_query(engine, sql_query)
257
258 elif choice == "2":
259 # Inlined (the second part of Display_Nabavki_For_Nadzor)
260 sql_query = f"""
261 WITH select_objekt_nabavki AS (
262 SELECT
263 o.objekt_id,
264 nb.nabavka_id,
265 nb.datum AS datum_na_nabavka,
266 nb.predmet,
267 nb.kolicina,
268 nb.cena
269 FROM objekt o
270 JOIN nadgleduva n ON o.objekt_id = n.objekt_id
271 JOIN nameneta_za nz ON nz.objekt_id = o.objekt_id
272 JOIN nabavka nb ON nb.nabavka_id = nz.nabavka_id
273 WHERE n.uid_n = {uid}
274 )
275 SELECT *
276 FROM select_objekt_nabavki son
277 LEFT JOIN odobruva o ON son.nabavka_id = o.nabavka_id
278 WHERE o.nabavka_id IS NOT NULL;
279 """
280 call_inline_query(engine, sql_query)
281
282 elif choice == "3":
283 nabavka_id = input("Enter Nabavka ID: ").strip()
284 sql_query = f"""
285 INSERT INTO odobruva (nabavka_id, uid_n)
286 VALUES ({nabavka_id}, {uid});
287 """
288 call_inline_query(engine, sql_query)
289
290 elif choice == "4":
291
292 objekt_id = input("Enter Objekt ID: ").strip()
293 sql_query = f"""
294 SELECT
295 o.objekt_id,
296 o.tip AS objekt_type,
297 o.adresa AS objekt_address,
298 vi.budzet AS objekt_budget,
299 n.nadzor_ime
300 FROM objekt o
301 LEFT JOIN vo_izgradba vi ON o.objekt_id = vi.objekt_id
302 LEFT JOIN nadgleduva nd ON o.objekt_id = nd.objekt_id
303 LEFT JOIN nadzor n ON nd.uid_n = n.uid
304 WHERE o.objekt_id = {objekt_id}
305 GROUP BY o.objekt_id, o.tip, o.adresa, vi.budzet, n.nadzor_ime;
306 """
307 call_inline_query(engine, sql_query)
308
309
310 else:
311 print("Invalid choice. Please try again.")
312
313 elif privileges == 2: # SUBCONTRACTOR
314 menu_2()
315 choice = input("Enter your choice: ").strip()
316 if choice == "0":
317 print("Exiting...")
318 break
319
320 elif choice == "1":
321 # Inlined InsertOrderWithRelations procedure for subcontractor
322 dobavuvaci = available_dobavuvaci()
323 # Inlined procedure: InsertOrderWithRelations(...)
324 try:
325 pod_id = int(input("Enter Podizveduvac ID: ").strip())
326 dobav_ime = input("Enter Dobavuvac Name: ").strip()
327 datum = input("Enter Date (YYYY-MM-DD): ").strip()
328 cena = float(input("Enter Price: ").strip())
329 kolicina = float(input("Enter Quantity: ").strip())
330 predmet = input("Enter Item Name: ").strip()
331 except Exception as e:
332 print(f"Invalid input: {e}")
333 continue
334
335 sql_query = f"""
336 WITH new_order AS (
337 INSERT INTO nabavka(datum, cena, kolicina, predmet)
338 VALUES ('{datum}', {cena}, {kolicina}, '{predmet}')
339 RETURNING nabavka_id
340 ),
341 pobaruva_insert AS (
342 INSERT INTO pobaruva(nabavka_id, uid_p)
343 SELECT nabavka_id, {pod_id}
344 FROM new_order
345 ),
346 ispolnuva_insert AS (
347 INSERT INTO ispolnuva(nabavka_id, dobavuvac_ime)
348 SELECT nabavka_id, '{dobav_ime}'
349 FROM new_order
350 )
351 SELECT nabavka_id FROM new_order;
352 """
353
354 with engine.begin() as conn:
355 result = conn.execute(text(sql_query)).fetchone()
356 if result is None:
357 print("Error inserting order.")
358 continue
359 print("Order inserted successfully.")
360
361 elif choice == "2":
362
363 objekt_id = int(input("Enter Objekt ID: ").strip())
364 sql_query = f"""
365 SELECT
366 o.objekt_id,
367 o.tip AS objekt_type,
368 o.adresa AS objekt_address,
369 vi.budzet AS objekt_budget,
370 n.nadzor_ime
371 FROM objekt o
372 LEFT JOIN vo_izgradba vi ON o.objekt_id = vi.objekt_id
373 LEFT JOIN nadgleduva nd ON o.objekt_id = nd.objekt_id
374 LEFT JOIN nadzor n ON nd.uid_n = n.uid
375 WHERE o.objekt_id = {objekt_id};
376 """
377 call_inline_query(engine, sql_query)
378
379 else:
380 print("Invalid choice. Please try again.")
381
382 else:
383 print("Invalid permissions level.")
384 break
385
386if __name__ == "__main__":
387 main()