· 6 years ago · Jun 04, 2019, 03:30 PM
1/**
2 *
3 */
4package it.polito.latazza.data;
5import java.sql.Connection;
6import java.sql.DriverManager;
7import java.sql.PreparedStatement;
8import java.sql.ResultSet;
9import java.sql.SQLException;
10import java.sql.Statement;
11import java.text.DateFormat;
12import java.text.ParseException;
13import java.text.SimpleDateFormat;
14import java.util.ArrayList;
15import java.util.Date;
16import java.util.LinkedList;
17import java.util.List;
18
19import it.polito.latazza.exceptions.BeverageException;
20import it.polito.latazza.exceptions.EmployeeException;
21
22/**
23 * @author elia
24 *
25 */
26
27public class Database {
28
29 Connection connection = null;
30
31 private void connect() throws Exception {
32 try {
33 if (connection != null) {
34 connection.close();
35 String msg = "Exception, called connect on a non void connect object";
36 throw new Exception(msg);
37 }
38
39 //System.out.println("before connection");
40
41 Class.forName("org.sqlite.JDBC");
42 connection = DriverManager.getConnection("jdbc:sqlite:./db/db_se.sqlite");
43 connection.createStatement().execute("PRAGMA foreign_keys=ON");
44
45 //System.out.println("Database connection opened.");
46 } catch(SQLException | ClassNotFoundException e) {
47 System.err.println("erroreeeeee");
48 throw new Exception();
49 }
50 }
51
52 private void closeConnection() throws Exception {
53 //System.out.println("before close connection");
54 connection.close();
55 connection = null;
56 //System.out.println("Database connection closed.");
57 }
58
59 public List<Employee> getListEmployee() throws Exception {
60 //System.out.println("before get list employee");
61 connect();
62
63 List<Employee> returnList = new LinkedList<Employee>();
64
65 Statement stmt = connection.createStatement();
66 String sql = "SELECT * FROM Employee";
67 ResultSet rs = stmt.executeQuery(sql);
68 while (rs.next()) {
69 int id = rs.getInt("id");
70 String name = rs.getString("name");
71 String surname = rs.getString("surname");
72 float credit = rs.getFloat("credit");
73
74 returnList.add(new Employee(id,name,surname,credit));
75
76 }
77
78 rs.close();
79 stmt.close();
80
81
82 //System.out.println("end get list employee");
83
84 closeConnection();
85
86 return returnList;
87 }
88
89 public Employee getEmployeeData(int i) throws Exception {
90 connect();
91
92 //System.out.println("before get employee");
93
94 String sql = "SELECT * FROM Employee WHERE id = ?";
95
96 PreparedStatement prep = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
97 prep.setInt(1, i);
98 ResultSet rs = prep.executeQuery();
99
100 int id = 0;
101 String name = null,surname = null;
102 float credit = 0;
103 boolean result = false;
104
105 if (rs.next()) {
106 id = rs.getInt("id");
107 name = rs.getString("name");
108 surname = rs.getString("surname");
109 credit = rs.getFloat("credit");
110 result = true;
111 }
112
113
114 //System.out.println("end get employee");
115
116 closeConnection();
117
118 if (!result)
119 throw new EmployeeException();
120
121 return new Employee(id,name,surname,credit);
122 }
123
124
125 public void updateBalance(double d) throws Exception {
126
127 //System.out.println("before update balance");
128
129 String sql = "update LaTazza set balance=?";
130 connect();
131
132 PreparedStatement prep = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
133 prep.setDouble(1, d);
134 prep.execute();
135
136 //System.out.println("end update balance");
137
138 closeConnection();
139 }
140
141 public List<Beverage> getListOfBeverages() throws Exception {
142 List<Beverage> returnList = new ArrayList<Beverage>();
143
144 //System.out.println("before list of beverage");
145
146 connect();
147
148 String sql = "SELECT * FROM Beverage";
149
150 Statement stmt = connection.createStatement();
151 ResultSet rs = stmt.executeQuery(sql);
152
153 while (rs.next()) {
154 int id = rs.getInt("id");
155 String name = rs.getString("name");
156 int capsulePerBox = rs.getInt("capsulePerBox");
157 int quantityAvaiable = rs.getInt("quantityAvaiable");
158 float price = rs.getFloat("price");
159 float priceNew = rs.getFloat("priceNew");
160 int capsulePerBoxNew = rs.getInt("capsulePerBoxNew");
161 int quantityAvaiableNew = rs.getInt("quantityAvaiableNew");
162
163 returnList.add(new Beverage(id,quantityAvaiable,price,capsulePerBox,name,priceNew,capsulePerBoxNew,quantityAvaiableNew));
164
165 }
166
167 rs.close();
168 stmt.close();
169
170
171 //System.out.println("end list of beverage");
172
173 closeConnection();
174
175 return returnList;
176 }
177
178 public Beverage getBeverageData(int id) throws Exception {
179 connect();
180
181 String sql = "SELECT * FROM Beverage WHERE id = ?";
182
183 PreparedStatement prep = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
184 prep.setInt(1, id);
185 ResultSet rs = prep.executeQuery();
186
187
188 int id_u = 0;
189 String name = null;
190 int capsulePerBox = 0;
191 int quantityAvaiable = 0;
192 float price = 0;
193 float priceNew = 0;
194 int capsulePerBoxNew=0;
195 boolean result=false;
196 int quantityAvaiableNew=0;
197
198 if (rs.next()) {
199 id_u = rs.getInt("id");
200 name = rs.getString("name");
201 capsulePerBox = rs.getInt("capsulePerBox");
202 quantityAvaiable = rs.getInt("quantityAvaiable");
203 price = rs.getFloat("price");
204 priceNew = rs.getFloat("priceNew");
205 capsulePerBoxNew = rs.getInt("capsulePerBoxNew");
206 quantityAvaiableNew = rs.getInt("quantityAvaiableNew");
207
208 result=true;
209 }
210
211 closeConnection();
212
213 if (!result)
214 throw new BeverageException();
215
216 return new Beverage(id_u,quantityAvaiable,price,capsulePerBox,name,priceNew,capsulePerBoxNew,quantityAvaiableNew);
217 }
218
219 public double getBalance() throws Exception {
220 connect();
221
222 String sql = "SELECT * FROM LaTazza";
223
224 PreparedStatement prep = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
225 ResultSet rs = prep.executeQuery();
226
227 double balance = 0;
228
229 while (rs.next()) {
230 balance = rs.getDouble("balance");
231 }
232
233 closeConnection();
234
235 return balance;
236
237 }
238
239 public List<Transaction> getReport(Date date_init, Date date_final) throws Exception {
240 String sql = "SELECT * FROM Transactions WHERE transactionDate BETWEEN ? and ?";
241
242 List<Transaction> returnList = new ArrayList<Transaction>();
243
244 connect();
245
246 PreparedStatement stmt = connection.prepareStatement(sql);
247 stmt.setString(1, convDate(date_init));
248 stmt.setString(2, convDate(date_final));
249 ResultSet rs = stmt.executeQuery();
250
251 while (rs.next()) {
252 int id_m = rs.getInt("id");
253 String transactionDate = rs.getString("transactionDate");
254 Date myDate = parseDate(transactionDate);
255 char type = rs.getString("type").charAt(0);
256 int boxQuantity = rs.getInt("boxQuantity");
257 int employeeID = rs.getInt("employeeID");
258 int beverageID = rs.getInt("beverageID");
259 float amount = rs.getFloat("amount");
260 int fr_account = rs.getInt("fromAccount");
261 int numberOfCapsules = rs.getInt("numberOfCapsules");
262 boolean from_account = fr_account==1;
263
264 returnList.add(new Transaction(id_m, myDate, type, boxQuantity, employeeID, beverageID,numberOfCapsules, amount, from_account));
265
266 }
267
268 rs.close();
269 stmt.close();
270
271 closeConnection();
272
273 return returnList;
274 }
275
276 public List<Transaction> getEmployeeReport(int id, Date date_init, Date date_final) throws Exception {
277 String sql = "SELECT * FROM Transactions WHERE employeeID=? AND transactionDate BETWEEN ? and ?";
278
279 List<Transaction> returnList = new ArrayList<Transaction>();
280
281 connect();
282
283 PreparedStatement stmt = connection.prepareStatement(sql);
284 stmt.setInt(1, id);
285 stmt.setString(2, convDate(date_init));
286 stmt.setString(3, convDate(date_final));
287 ResultSet rs = stmt.executeQuery();
288
289 while (rs.next()) {
290 int id_m = rs.getInt("id");
291 String transactionDate = rs.getString("transactionDate");
292 Date myDate = parseDate(transactionDate);
293 char type = rs.getString("type").charAt(0);
294 int boxQuantity = rs.getInt("boxQuantity");
295 int employeeID = rs.getInt("employeeID");
296 int beverageID = rs.getInt("beverageID");
297 float amount = rs.getFloat("amount");
298 int fr_account = rs.getInt("fromAccount");
299 int numberOfCapsules = rs.getInt("numberOfCapsules");
300 boolean from_account = fr_account==1;
301
302 returnList.add(new Transaction(id_m, myDate, type, boxQuantity, employeeID, beverageID,numberOfCapsules, amount, from_account));
303
304 }
305
306 rs.close();
307 stmt.close();
308
309 closeConnection();
310
311 return returnList;
312 }
313
314 public static Date parseDate(String datePassed) throws ParseException {
315 ////System.out.println("converting: " + datePassed);
316
317 java.util.Date temp = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")
318 .parse(datePassed);
319
320 return temp;
321 }
322
323 public static String convDate(Date date_init) {
324 DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
325 String newDate = dateFormat.format(date_init);
326 ////System.out.println("created: " + newDate);
327 return newDate;
328 }
329
330 public int registerTransaction(Transaction transaction) throws Exception {
331 // TODO Auto-generated method stub
332 connect();
333
334 int last_inserted_id = -1;
335
336 String sql = "INSERT INTO Transactions VALUES (NULL,?,?,?,?,?,?,?,?);";
337
338 PreparedStatement prep = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
339 prep.setString(1, convDate(transaction.getTransactionDate()));
340 prep.setString(2, String.valueOf(transaction.getType()));
341 prep.setInt(3, transaction.getBoxQuantity());
342 prep.setInt(4, transaction.getEmployeeID());
343 prep.setInt(5, transaction.getBeverageID());
344 prep.setDouble(6, transaction.getNumberOfCapsules());
345 prep.setDouble(7, transaction.getAmount());
346 prep.setBoolean(8, transaction.isFromAccount());
347
348
349 prep.executeUpdate();
350
351 ResultSet rs = prep.getGeneratedKeys();
352 if(rs.next())
353 {
354 last_inserted_id = rs.getInt(1);
355 }
356
357 prep.close();
358
359 closeConnection();
360
361 return last_inserted_id;
362 }
363
364
365
366 public int addBeverage(Beverage beverage) throws Exception {
367
368 connect();
369 int last_inserted_id = -1;
370 String sql = "INSERT INTO Beverage VALUES (NULL,?,?,?,?,0,0,0);";
371
372
373
374 PreparedStatement prep = connection.prepareStatement(sql);
375 prep.setInt(1, beverage.getQuantityAvailable());
376 prep.setDouble(2, beverage.getBoxPrice());
377 prep.setInt(3, beverage.getCapsulePerBox());
378 prep.setString(4, beverage.getName());
379 prep.executeUpdate();
380
381 ResultSet rs = prep.getGeneratedKeys();
382 if(rs.next())
383 {
384 last_inserted_id = rs.getInt(1);
385 }
386
387
388 prep.close();
389
390 closeConnection();
391
392 return last_inserted_id;
393
394 }
395
396 public int addEmployee(Employee employee) throws Exception {
397 connect();
398
399 int last_inserted_id=-1;
400
401 String sql = "INSERT INTO Employee VALUES (NULL,?,?,?);";
402
403 PreparedStatement prep = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
404 prep.setString(1, employee.getName());
405 prep.setString(2, employee.getSurname());
406 prep.setDouble(3, employee.getCredit());
407 prep.executeUpdate();
408
409 ResultSet rs = prep.getGeneratedKeys();
410 if(rs.next())
411 {
412 last_inserted_id = rs.getInt(1);
413 }
414
415 prep.close();
416
417 closeConnection();
418
419 return last_inserted_id;
420 }
421
422 public void truncateTables() throws Exception {
423 // TODO Auto-generated method stub
424 String sql_create_1 = "CREATE TABLE IF NOT EXISTS `Transactions` (\n" +
425 " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
426 " `transactionDate` TEXT NOT NULL ,\n" + // CHECK(date ( transactionDate ) IS NOT NULL),\n" +
427 " `type` CHAR NOT NULL CHECK(type = 'R' OR type = 'C' OR type = 'P'),\n" +
428 " `boxQuantity` INTEGER NOT NULL,\n" +
429 " `employeeID` INTEGER NOT NULL,\n" +
430 " `beverageID` INTEGER NOT NULL,\n" +
431 " `numberOfCapsules` INTEGER NOT NULL," +
432 " `amount` REAL NOT NULL,\n" +
433 " `fromAccount` NUMERIC NOT NULL CHECK(fromAccount = 0 OR fromAccount = 1)\n" +
434 ");";
435
436 String sql_create_2 = "CREATE TABLE IF NOT EXISTS `Employee` (\n" +
437 " `id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
438 " `name` TEXT NOT NULL,\n" +
439 " `surname` TEXT NOT NULL,\n" +
440 " `credit` REAL NOT NULL DEFAULT 0\n" + // CHECK(credit >= 0) removed for incompatibility with the requirement
441 ");";
442 String sql_create_3 = "CREATE TABLE IF NOT EXISTS `Beverage` (\n" +
443 " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
444 " `quantityAvaiable` INTEGER NOT NULL DEFAULT 0 CHECK(quantityAvaiable >= 0),\n" +
445 " `price` REAL NOT NULL CHECK(price > 0),\n" +
446 " `capsulePerBox` INTEGER NOT NULL CHECK(capsulePerBox > 0),\n" +
447 " `name` TEXT NOT NULL UNIQUE,\n" +
448 " `capsulePerBoxNew` INTEGER NOT NULL DEFAULT 0 CHECK(capsulePerBoxNew >= 0)," +
449 " `priceNew` REAL NOT NULL DEFAULT 0 CHECK(priceNew >= 0), " +
450 " `quantityAvaiableNew` INTEGER NOT NULL DEFAULT 0 CHECK(quantityAvaiableNew >= 0)" +
451 ");";
452
453 String sql_create_4 = "insert into LaTazza values(0.0)";
454
455 String sqlDelete_1 = "drop table IF EXISTS `Transactions`;";
456 String sqlDelete_2 = "drop table IF EXISTS `Employee`;";
457 String sqlDelete_3 = "drop table IF EXISTS `Beverage`;";
458
459 connect();
460
461 Statement stmt_drop_tables = connection.createStatement();
462 stmt_drop_tables.addBatch(sqlDelete_1);
463 stmt_drop_tables.addBatch(sqlDelete_2);
464 stmt_drop_tables.addBatch(sqlDelete_3);
465 try {
466 stmt_drop_tables.executeBatch();
467 } catch(Exception e) {}
468
469 Statement stmt_create_tables = connection.createStatement();
470 stmt_create_tables.addBatch(sql_create_1);
471 stmt_create_tables.addBatch(sql_create_2);
472 stmt_create_tables.addBatch(sql_create_3);
473 stmt_create_tables.addBatch(sql_create_4);
474 stmt_create_tables.executeBatch();
475
476
477 closeConnection();
478
479 }
480
481 public void updateBeverage(Beverage beverage) throws Exception {
482 connect();
483 //int last_inserted_id = -1;
484 String sql = "UPDATE `Beverage` SET `quantityAvaiable`=?,`price`=?,`capsulePerBox`=?,`name`=?,`capsulePerBoxNew`=?,`priceNew`=?,`quantityAvaiableNew`=? WHERE id=?;";
485
486
487 PreparedStatement prep = connection.prepareStatement(sql);
488 prep.setInt(1, beverage.getQuantityAvailable());
489 prep.setDouble(2, beverage.getBoxPrice());
490 prep.setInt(3, beverage.getCapsulePerBox());
491 prep.setString(4, beverage.getName());
492 prep.setInt(5, beverage.getCapsulePerBoxNew());
493 prep.setDouble(6, beverage.getBoxPriceNew());
494 prep.setInt(7, beverage.getQuantityAvailableNew());
495 prep.setInt(8, beverage.getId());
496 int count = prep.executeUpdate();
497
498 prep.close();
499
500 closeConnection();
501
502 if (count <= 0)
503 throw new BeverageException();
504 }
505
506 public void updateEmployee(Employee employee) throws Exception {
507 connect();
508
509 //int last_inserted_id=-1;
510
511 String sql = "UPDATE `Employee` SET `name`=?,`surname`=?,`credit`=? WHERE id=?;";
512
513 PreparedStatement prep = connection.prepareStatement(sql);
514 prep.setString(1, employee.getName());
515 prep.setString(2, employee.getSurname());
516 prep.setDouble(3, employee.getCredit());
517 prep.setDouble(4, employee.getId());
518 int count = prep.executeUpdate();
519
520 prep.close();
521
522 closeConnection();
523
524
525 if (count <= 0)
526 throw new EmployeeException();
527 }
528
529
530
531}