1 /*
  2  * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
  3  * Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Class.java to edit this template
  4  */
  5 package idania;
  6 
  7 import java.io.File;
  8 import java.sql.*;
  9 import java.util.ArrayList;
 10 import java.util.HashMap;
 11 
 12 /**
 13  *
 14  * @author zeida
 15  */
 16 public class DatabaseManager {
 17     private String DatabasePath = "jdbc:sqlite:" + getDBPath();
 18 
 19     private String getDBPath() {
 20         String seperator = Character.toString(File.separatorChar);
 21         String cwd = System.getProperty("user.dir");
 22         String dbPath = cwd.concat("*src*idania*Database*sqlite-tools-win32-x86-3340100*Database.db".replace("*", seperator));
 23         return dbPath;
 24     }
 25 
 26     public DatabaseManager() {
 27     }
 28 
 29     public void writeToDB (String query) {
 30         Connection conn;
 31         Statement statement;
 32         try {
 33             conn = DriverManager.getConnection(DatabasePath);
 34             statement = conn.createStatement();
 35 
 36             // Execute Query
 37             statement.execute(query);
 38         } catch (SQLException exception) {
 39             System.out.println(exception);
 40         }
 41     }
 42 
 43     public HashMap<Integer, ArrayList<Object>> getPayments() {
 44         HashMap<Integer, ArrayList<Object>> payments = new HashMap<>();
 45 
 46         Connection conn;
 47         Statement statement;
 48         try {
 49             conn = DriverManager.getConnection(DatabasePath);
 50             statement = conn.createStatement();
 51 
 52             // Execute Query
 53             ResultSet resultSet = statement.executeQuery("SELECT * FROM paymentsTable;");
 54 
 55             while (resultSet.next()) {
 56                 ArrayList<Object> currentData = new ArrayList<>();
 57                 currentData.add(resultSet.getString("paymentDate"));
 58                 currentData.add(resultSet.getBoolean("isPaid"));
 59                 // Convert boolean to int
 60                 payments.put(resultSet.getInt("clientID"), currentData);
 61             }
 62         } catch (SQLException exception) {
 63             System.out.println(exception);
 64         }
 65         
 66         return payments;
 67     }
 68 
 69     public HashMap<Integer, Integer> getSessions() {
 70         HashMap<Integer, Integer> sessions = new HashMap<>();
 71 
 72 
 73         Connection conn;
 74         Statement statement;
 75         try {
 76             conn = DriverManager.getConnection(DatabasePath);
 77             statement = conn.createStatement();
 78 
 79             // Execute Query
 80             ResultSet resultSet = statement.executeQuery("SELECT * FROM sessionsTable;");
 81 
 82             while (resultSet.next()) {
 83                 // Convert boolean to int
 84                 sessions.put(resultSet.getInt("clientID"), resultSet.getInt("sessionsCount"));
 85             }
 86         } catch (SQLException exception) {
 87             System.out.println(exception);
 88         }
 89         
 90 
 91         return sessions;
 92     }
 93 
 94     public String getSessionsUpdateQuery(int clientID, int sessions) {
 95         String check = "SELECT * FROM sessionsTable WHERE clientID = " + clientID + ";";
 96         String update = "UPDATE sessionsTable SET sessionsCount = " + sessions + " WHERE clientID = " + clientID + ";";
 97         String insert = "INSERT INTO sessionsTable (clientID, sessionsCount) VALUES("+ clientID + ", " +  sessions + ");";
 98         
 99         boolean exists = false;
100 
101         // Call sessions query
102         Connection conn;
103         Statement statement;
104         try {
105             conn = DriverManager.getConnection(DatabasePath);
106             statement = conn.createStatement();
107 
108             // Execute Query
109             ResultSet resultSet = statement.executeQuery(check);
110 
111             while(resultSet.next()) {
112                 exists = true;
113             }
114         } catch (SQLException exception) {
115             System.out.println(exception);
116         }
117 
118         return exists ? update : insert;
119     }
120     
121     public String getUpdatePaymentQuery(int clientID, String date, boolean isPaid) {
122         String check = "SELECT * FROM paymentsTable WHERE clientID = " + clientID + " AND paymentDate = \""+ date + "\";";
123         String update = "UPDATE paymentsTable SET isPaid = " + isPaid + " WHERE clientID = " + clientID + " AND paymentDate = \""+ date + "\";";
124         String insert = "INSERT INTO paymentsTable (clientID, paymentDate, isPaid) VALUES(" + clientID + ", \"" + java.time.LocalDate.now().toString() + "\", " + isPaid +");";
125 
126         boolean exists = false;
127 
128         // Call sessions query
129         Connection conn;
130         Statement statement;
131         try {
132             conn = DriverManager.getConnection(DatabasePath);
133             statement = conn.createStatement();
134 
135             // Execute Query
136             ResultSet resultSet = statement.executeQuery(check);
137 
138             while(resultSet.next()) {
139                 exists = true;
140             }
141         } catch (SQLException exception) {
142             System.out.println(exception);
143         }
144 
145         return exists ? update : insert;
146     }
147 
148     public String getAddPaymentQuery(int clientID, String date) {
149         String query = "INSERT INTO paymentsTable (clientID, paymentDate, isPaid) VALUES(" + clientID + ", \"" + date + "\", 0);";
150         return query;
151     }
152 
153     public String getDeleteClientQuery(int clientID) {
154         String query = "DELETE FROM clientsTable WHERE id = " + clientID + ";";
155         return query;
156     }
157 
158     public ArrayList<Client> getClients() {
159         String query = "SELECT * FROM clientsTable;";
160         ArrayList<Client> clients = new ArrayList<>();
161 
162         Connection conn;
163         Statement statement;
164         try {
165             conn = DriverManager.getConnection(DatabasePath);
166             statement = conn.createStatement();
167 
168             // Execute Query
169             ResultSet resultSet = statement.executeQuery(query);
170 
171             while (resultSet.next()) {
172                 // Convert boolean to int
173                 boolean isStudent = false;
174                 boolean isTeacher = false;
175 
176                 if (resultSet.getInt("isTeacher") == 1) {
177                     isTeacher = true;
178                 } else if (resultSet.getInt("isStudent") == 1) {
179                     isStudent = true;
180                 }
181                 
182                 Client client = new Client(
183                         resultSet.getString("firstName"),
184                         resultSet.getString("lastName"),
185                         isTeacher,
186                         isStudent,
187                         resultSet.getInt("Age"),
188                         resultSet.getString("emailAddress"),
189                         resultSet.getString("dateJoined")
190                 );
191                 
192                 client.clientID = resultSet.getInt("id");
193 
194                 clients.add(client);
195 
196             }
197         } catch (SQLException exception) {
198             System.out.println(exception);
199         }
200 
201         return clients;
202 
203     }
204 
205     public String getInsertClientQuery(Client client) {
206 
207         // Convert boolean to int
208         int isStudent = 0;
209         int isTeacher = 0;
210 
211         if (client.isStudent) {
212             isStudent = 1;
213         } else if (client.isTeacher) {
214             isTeacher = 1;
215         }
216 
217         String query = "INSERT INTO clientsTable (firstName, lastName, isStudent, isTeacher, age, emailAddress, dateJoined)" +
218                 String.format(" VALUES ('%s', '%s', %o, %o, %o, '%s', '%s');",
219                     client.getFirstName(),
220                     client.getLastName(),
221                     isStudent,
222                     isTeacher,
223                     client.getAge(),
224                     client.getEmailAddress(),
225                     client.getDateJoined()
226                 );
227 
228         return query;
229     }
230 }
231