· 6 years ago · May 11, 2019, 05:34 PM
1package database;
2import java.sql.Connection;
3import java.sql.DatabaseMetaData;
4import java.sql.Date;
5import java.sql.DriverManager;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.ArrayList;
10import java.util.List;
11import java.text.DateFormat;
12import java.text.ParseException;
13import java.text.SimpleDateFormat;
14
15import javax.xml.crypto.Data;
16
17import it.polito.latazza.data.Beverage;
18import it.polito.latazza.data.BoxPurchase;
19import it.polito.latazza.data.Consumption;
20import it.polito.latazza.data.DataImpl;
21import it.polito.latazza.data.Employee;
22import it.polito.latazza.data.Recharge;
23import it.polito.latazza.data.Transaction;
24import it.polito.latazza.exceptions.BeverageException;
25import it.polito.latazza.exceptions.EmployeeException;
26
27public class InterfaceDB {
28
29 private static Connection conn;
30 private static String sql;
31 private static Statement statement;
32
33
34 public static void createNewDatabase() {
35
36 String url = "jdbc:sqlite:latazza.db";
37 statement = null;
38
39 try (Connection conn = DriverManager.getConnection(url)) {
40 if (conn != null) {
41 DatabaseMetaData meta = conn.getMetaData();
42 System.out.println("The driver name is " + meta.getDriverName());
43 System.out.println("A new database has been created.");
44
45 statement=conn.createStatement();
46 sql = "CREATE TABLE IF NOT EXISTS EMPLOYEE" +
47 "(ID INTEGER NOT NULL PRIMARY KEY,"+
48 "NAME TEXT NOT NULL,"+
49 "SURNAME TEXT NOT NULL,"+
50 "BALANCE INT NOT NULL)";
51 statement.executeUpdate(sql);
52 statement.close();
53
54
55
56
57 statement=conn.createStatement();
58 sql = "CREATE TABLE IF NOT EXISTS LOGS" +
59 "(ID INT PRIMARY KEY,"+
60 "D STRING NOT NULL,"+
61 "RECHARGE INT NOT NULL,"+
62 "BUY INT NOT NULL,"+
63 "VISITOR INT NOT NULL,"+
64 "CB INT NOT NULL,"+
65 "IDEMP INT NOT NULL,"+
66 "IDBEV INT NOT NULL,"+
67 "AMQU NOT NULL)";
68 statement.executeUpdate(sql);
69 statement.close();
70
71 statement=conn.createStatement();
72 sql = "CREATE TABLE IF NOT EXISTS LATAZZAACCOUNT" +
73 "(ID INT PRIMARY KEY NOT NULL, BALANCE INT)";
74 statement.executeUpdate(sql);
75 statement.close();
76
77 statement=conn.createStatement();
78 sql = "CREATE TABLE IF NOT EXISTS BEVERAGE" +
79 "(ID INT PRIMARY KEY,"+
80 "NAME TEXT NOT NULL,"+
81 "PRICE INT NOT NULL,"+
82 "CAPSULES_BOX INT NOT NULL,"+
83 "BOX_PRICE INT NOT NULL,"+
84 "QUANTITY INT NOT NULL)";
85 statement.executeUpdate(sql);
86 statement.close();
87
88
89
90
91
92 }
93
94 } catch (SQLException e) {
95 System.out.println(e.getMessage());
96 }
97 }
98
99 public static void connect() {
100 conn = null;
101 try {
102 // db parameters
103 String url = "jdbc:sqlite:latazza.db";
104 // create a connection to the database
105 conn = DriverManager.getConnection(url);
106
107 System.out.println("Connection to SQLite has been established.");
108
109 } catch (SQLException e) {
110 System.out.println(e.getMessage());
111 } /*finally {
112 try {
113 if (conn != null) {
114 conn.close();
115 }
116 } catch (SQLException ex) {
117 System.out.println(ex.getMessage());
118 }
119 }*/
120 }
121
122 public static List<Employee> getEmployees()
123 {
124 List<Employee> employees = new ArrayList<Employee>();
125
126 try
127 {
128 statement=conn.createStatement();
129 sql="SELECT * FROM EMPLOYEE";
130 ResultSet rs = statement.executeQuery(sql);
131
132 while(rs.next())
133 {
134 Employee employee = new Employee(rs.getInt("ID"),rs.getString("NAME"),rs.getString("SURNAME"),rs.getInt("BALANCE"));
135 employees.add(employee);
136 Employee.setLast(rs.getInt("ID"));
137 }
138
139 statement.close();
140
141 }catch (SQLException e)
142 {
143 System.out.println(e.getMessage());
144 }
145 return employees;
146 }
147
148 public static void insertEmployee(Integer id,String name, String surname, Integer balance)
149 {
150 try
151 {
152 statement=conn.createStatement();
153 sql="INSERT INTO EMPLOYEE (ID,NAME,SURNAME,BALANCE) "+"VALUES("+id+",'"+name+"','"+surname+"',"+balance+");";
154 statement.executeUpdate(sql);
155 statement.close();
156
157 }catch (SQLException e)
158 {
159 System.out.println(e.getMessage());
160 }
161
162
163 }
164
165 public static void updateEmployee(Integer id, String name, String surname, Integer balance)
166 {
167 try
168 {
169 statement=conn.createStatement();
170 sql="UPDATE EMPLOYEE SET NAME='"+name+"',SURNAME='"+surname+"',BALANCE="+balance+" WHERE ID="+id+";";
171 statement.executeUpdate(sql);
172 statement.close();
173
174
175 }catch (SQLException e)
176 {
177 System.out.println(e.getMessage());
178 }
179
180 }
181
182
183 public static void insertBeverage(Integer id,Integer quantity,Integer price, Integer capsulesPerBox, Integer boxPrice, String name)
184 {
185 try
186 {
187 Statement statement=conn.createStatement();
188 String sql="INSERT INTO BEVERAGE (ID,NAME,PRICE,CAPSULES_BOX,BOX_PRICE,QUANTITY)"
189 +"VALUES("+id+",'" +name+"',"+price+","+capsulesPerBox+","+boxPrice+","+quantity+");";
190 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
191 statement.executeUpdate(sql);
192 statement.close();
193
194 }catch (SQLException e)
195 {
196 System.out.println(e.getMessage());
197 }
198
199 }
200
201 public static void updateBeverage(Integer id, Integer capsulesPerBox, Integer boxPrice, String name,Integer quantity)
202 {
203 try
204 {
205 Statement statement=conn.createStatement();
206 String sql="UPDATE BEVERAGE SET NAME = '"+name+"', CAPSULES_BOX = "+capsulesPerBox+", BOX_PRICE = "+boxPrice+", QUANTITY = "+quantity+" WHERE ID = "+id+";";
207 statement.executeUpdate(sql);
208 statement.close();
209
210 }catch (SQLException e)
211 {
212 System.out.println(e.getMessage());
213 }
214
215 }
216
217 public static List<Beverage> getBeverages() {
218 List<Beverage> beverages = new ArrayList<Beverage>();
219 String sql="SELECT * FROM BEVERAGE";
220 Statement statement = null;
221 try{
222
223
224 statement=conn.createStatement();
225 ResultSet rs = statement.executeQuery(sql);
226 while(rs.next())
227 {
228 Beverage b = new Beverage(rs.getInt("ID"),rs.getInt("PRICE"),rs.getInt("QUANTITY"),rs.getInt("CAPSULES_BOX"),rs.getInt("BOX_PRICE"),rs.getString("NAME"));
229 beverages.add(b);
230 Beverage.setLast(rs.getInt("ID"));
231 }
232 statement.close();
233
234 } catch (SQLException e) {
235 System.out.println(e.getMessage());
236 }
237
238 return beverages;
239 }
240
241 public static Integer getBalance() {
242
243
244
245 Statement statement;
246 Statement statement1;
247 Integer balance=0;
248
249 try {
250 statement=conn.createStatement();
251 sql="INSERT INTO LATAZZAACCOUNT (ID, BALANCE) "
252 +"VALUES(0,0);";
253 statement.executeUpdate(sql);
254 statement.close();
255 } catch (SQLException e) {
256 System.out.println("RIGA GIA' PRESENTE");
257 System.out.println(e.getMessage());
258
259 sql="SELECT * FROM LATAZZAACCOUNT";
260 ResultSet rs;
261 try {
262 statement1=conn.createStatement();
263 rs = statement1.executeQuery(sql);
264 while(rs.next())
265 {
266
267 balance=rs.getInt("BALANCE");
268 }
269 statement1.close();
270
271 } catch (SQLException e1) {
272 // TODO Auto-generated catch block
273 e1.printStackTrace();
274 }
275
276
277
278 }
279 return balance;
280
281 }
282
283 public static void updateAccount(Integer amount) {
284
285 String sql;
286 Statement statement;
287
288 try {
289 statement=conn.createStatement();
290 sql="UPDATE LATAZZAACCOUNT SET BALANCE="+amount+" WHERE ID=0;";
291 statement.executeUpdate(sql);
292 statement.close();
293 } catch (SQLException e) {
294 System.out.println(e.getMessage());
295 }
296
297 }
298
299 public static void logBox(Integer id, String date,Integer idb,Integer qnt)
300 {
301 try
302 {
303 Statement statement=conn.createStatement();
304 String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
305 +"VALUES("+id+",'" +date+"',0,1,0,0,0,"+idb+"," + qnt + ");";
306 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
307 statement.executeUpdate(sql);
308 statement.close();
309
310 }catch (SQLException e)
311 {
312 System.out.println(e.getMessage());
313 }
314
315 }
316
317
318 public static void logRecharge(Integer id, String date,Integer amount,Integer idEmp)
319
320
321 {
322 try
323 {
324 Statement statement=conn.createStatement();
325 String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
326 +"VALUES("+id+",'" +date+"',1,0,0,0,"+idEmp+",0," + amount + ");";
327 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
328 statement.executeUpdate(sql);
329 statement.close();
330
331 }catch (SQLException e)
332 {
333 System.out.println(e.getMessage());
334 }
335
336 }
337
338
339public static void logEmployee(Integer id, String date,Integer idEm,Integer idBe, Integer qnt, Integer m)
340
341
342 {
343 try
344 {
345 Statement statement=conn.createStatement();
346 String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
347 +"VALUES("+id+",'" +date+"',0,0,0,"+m+","+idEm+","+idBe+"," + qnt + ");";
348 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
349 statement.executeUpdate(sql);
350 statement.close();
351
352 }catch (SQLException e)
353 {
354 System.out.println(e.getMessage());
355 }
356
357 }
358
359public static void logVisitor(Integer id, String date,Integer idBe, Integer qnt)
360
361
362{
363 try
364 {
365 Statement statement=conn.createStatement();
366 String sql="INSERT INTO LOGS (ID,D,RECHARGE,BUY,VISITOR,CB,IDEMP,IDBEV,AMQU)"
367 +"VALUES("+id+",'" +date+"',0,0,1,0,0,"+idBe+"," + qnt + ");";
368 //statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
369 statement.executeUpdate(sql);
370 statement.close();
371
372 }catch (SQLException e)
373 {
374 System.out.println(e.getMessage());
375 }
376
377}
378
379public static List<Transaction> getLogs(DataImpl d) {
380
381 List<Transaction> transactionDB = new ArrayList<Transaction>();
382 Integer id,idBev,idEmp,qnt,price,quantity,capsulesPerBox,boxPrice,balance,visitor, mode;
383 String date;
384 String name,surname;
385
386 //BOXPURCHASE
387 try
388 {
389 Statement statement=conn.createStatement();
390 String sql="SELECT * FROM LOGS WHERE BUY=1";
391
392 ResultSet rs = statement.executeQuery(sql);
393 while(rs.next())
394 {
395 id=rs.getInt("ID");
396 idBev=rs.getInt("IDBEV");
397 date=rs.getString("D");
398 qnt=rs.getInt("AMQU");
399
400 quantity=d.getBeverageCapsules(idBev);
401 boxPrice=d.getBeverageBoxPrice(idBev);
402 capsulesPerBox=d.getBeverageCapsulesPerBox(idBev);
403 name=d.getBeverageName(idBev);
404 price=boxPrice/capsulesPerBox;
405
406 Beverage b = new Beverage(id,price,quantity,capsulesPerBox,boxPrice,name);
407 BoxPurchase box = new BoxPurchase(id,date,b,qnt);
408 transactionDB.add(box);
409 Transaction.setLast(rs.getInt("ID"));
410 }
411 statement.close();
412
413 }catch (SQLException e)
414 {
415 System.out.println(e.getMessage());
416 } catch (BeverageException e) {
417 // TODO Auto-generated catch block
418 e.printStackTrace();
419 }
420
421
422
423 //RECHARGE
424
425 try
426 {
427 Statement statement=conn.createStatement();
428 String sql="SELECT * FROM LOGS WHERE RECHARGE=1";
429
430 ResultSet rs = statement.executeQuery(sql);
431 while(rs.next())
432 {
433 id=rs.getInt("ID");
434 idEmp=rs.getInt("IDEMP");
435 //date=rs.getDate("D");
436 date=rs.getString("D");
437
438 qnt=rs.getInt("AMQU");
439
440 name=d.getEmployeeName(idEmp);
441 surname=d.getEmployeeSurname(idEmp);
442 balance=d.getEmployeeBalance(idEmp);
443
444 Employee e = new Employee(idEmp,name,surname,balance);
445 Recharge r = new Recharge(id,date,qnt,e);
446 transactionDB.add(r);
447 Transaction.setLast(rs.getInt("ID"));
448 }
449 statement.close();
450
451 }catch (SQLException e)
452 {
453 System.out.println(e.getMessage());
454 } catch (EmployeeException e) {
455 // TODO Auto-generated catch block
456 e.printStackTrace();
457 }
458
459
460
461 try
462 {
463 Statement statement=conn.createStatement();
464 String sql="SELECT * FROM LOGS WHERE BUY=0 AND RECHARGE=0";
465
466 ResultSet rs = statement.executeQuery(sql);
467 while(rs.next())
468 {
469 id=rs.getInt("ID");
470 idEmp=rs.getInt("IDEMP");
471 //date=rs.getDate("D");
472 date= rs.getString("D");
473 qnt=rs.getInt("AMQU");
474 visitor=rs.getInt("VISITOR");
475 mode=rs.getInt("CB");
476 idBev=rs.getInt("IDBEV");
477
478 name=d.getEmployeeName(idEmp);
479 surname=d.getEmployeeSurname(idEmp);
480 balance=d.getEmployeeBalance(idEmp);
481
482 Employee e = new Employee(idEmp,name,surname,balance);
483
484 quantity=d.getBeverageCapsules(idBev);
485 boxPrice=d.getBeverageBoxPrice(idBev);
486 capsulesPerBox=d.getBeverageCapsulesPerBox(idBev);
487 name=d.getBeverageName(idBev);
488 price=boxPrice/capsulesPerBox;
489
490 Beverage b = new Beverage(id,price,quantity,capsulesPerBox,boxPrice,name);
491 Consumption c;
492
493
494 if(visitor==1) {
495 c = new Consumption(id, date,b,null,qnt,"VISITOR");
496
497 } //non è visitor
498 else if(mode==0){
499 //è cash
500 c = new Consumption(id,date,b,e,qnt,"CASH");
501
502 }else {
503 //è balance
504 c = new Consumption(id,date,b,e,qnt,"BALANCE");
505
506 }
507
508 transactionDB.add(c);
509 Transaction.setLast(rs.getInt("ID"));
510 }
511 statement.close();
512
513 }catch (SQLException e)
514 {
515 System.out.println(e.getMessage());
516 } catch (EmployeeException e) {
517 // TODO Auto-generated catch block
518 e.printStackTrace();
519 } catch (BeverageException e1) {
520 // TODO Auto-generated catch block
521 e1.printStackTrace();
522 }
523
524
525
526 return transactionDB;
527}
528
529
530
531}