2 * To change this license header, choose License Headers in Project Properties.
3 * To change this template file, choose Tools | Templates
4 * and open the template in the editor.
8 import java
.util
.ArrayList
;
9 import java
.util
.prefs
.Preferences
;
11 import java
.util
.logging
.Level
;
12 import java
.util
.logging
.Logger
;
18 public class Utility
{
20 static void saveConnectionURL(String text
) {
22 Preferences
.userRoot().put("ConnectionURL", text
);
25 static ArrayList
<Object
> getClientDetails(int r
) {
26 ArrayList
<Object
> res
= new ArrayList
<>();
28 Connection conn
= DriverManager
.getConnection(JDBC
+connectionURL
);
29 PreparedStatement st
= conn
.prepareStatement("SELECT * FROM clients WHERE id=?;");
31 ResultSet rs
= st
.executeQuery();
33 res
.add (rs
.getString("ClientName"));
34 res
.add(rs
.getString("ClientAddress"));
35 res
.add (rs
.getString("ContactPerson"));
36 res
.add (rs
.getString("MailID"));
37 res
.add (rs
.getString("ContactNumber"));
42 } catch (SQLException ex
) {
43 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
49 static boolean addClient(String client_name
, String client_address
,
50 String contact_person
, String email_id
, String phone_number
) {
51 if ("".equals(client_name
))
54 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
55 PreparedStatement st
= conn
.prepareStatement("INSERT INTO clients (ClientName,"
56 + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
57 st
.setString(1, client_name
);
58 st
.setString(2, client_address
);
59 st
.setString(3, contact_person
);
60 st
.setString (4, email_id
);
61 st
.setString (5, phone_number
);
66 } catch (SQLException ex
) {
67 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
74 static boolean deleteClient(int r
) {
76 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
77 PreparedStatement st
= conn
.prepareStatement("DELETE FROM clients WHERE id=?;");
78 PreparedStatement st2
= conn
.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
85 } catch (SQLException ex
) {
86 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
92 static boolean updateClient(int r
, String client_name
, String client_address
,
93 String contact_person
, String mail_id
, String phone_number
) {
94 if ("".equals(client_name
))
97 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
98 PreparedStatement st
= conn
.prepareStatement("UPDATE clients SET "
99 + "ClientName=?, ClientAddress=?,"
100 + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
101 st
.setString(1, client_name
);
102 st
.setString(2, client_address
);
103 st
.setString(3, contact_person
);
104 st
.setString(4, mail_id
);
105 st
.setString(5, phone_number
);
110 } catch (SQLException ex
) {
111 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
118 static boolean addLegalNotice(String reference_number
,
119 String description
, java
.util
.Date entrustment_date
, DBItem client
) {
120 if ("".equals(reference_number
) || "".equals(description
) ||
121 entrustment_date
== null || client
== null)
124 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
125 PreparedStatement st
= conn
.prepareStatement("INSERT INTO legalnotices"
126 + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
127 + "VALUES (?, ?, ?, ?);");
128 st
.setString(1, reference_number
);
129 st
.setString(2, description
);
130 st
.setLong(3, entrustment_date
.getTime()/1000);
131 st
.setInt (4, client
.getKey());
136 } catch (SQLException ex
) {
137 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
143 static ArrayList
<Object
> getNotices() {
144 ArrayList
<Object
> notices
= new ArrayList
<>();
146 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
147 Statement st
= conn
.createStatement();
148 ResultSet rs
= st
.executeQuery("SELECT id, ReferenceNumber, Description "
149 + "FROM legalnotices;");
151 notices
.add(rs
.getInt("id"));
152 notices
.add(rs
.getString("ReferenceNumber"));
153 notices
.add(rs
.getString("Description"));
157 } catch (SQLException ex
) {
158 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
163 static java
.util
.Date
getValidDate (ResultSet rs
, String datefield
) throws SQLException
{
164 // Since resultset.getDate returns a java.sql.Date, we need a way to get
165 // the date in java.util.Date which is usable in our application. Hence
166 // this helper function. Since we don't want a valid Date object if the
167 // field is null, we are using this if clause
168 if (rs
.getDate(datefield
) == null)
171 return (new java
.util
.Date(rs
.getLong(datefield
)*1000));
174 static ArrayList
<Object
> getNoticeDetails(int selid
) {
175 ArrayList
<Object
> notice
= new ArrayList
<>();
177 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
178 PreparedStatement st
= conn
.prepareStatement("SELECT legalnotices.*, clients.ClientName "
179 + "FROM legalnotices "
180 + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
182 ResultSet rs
= st
.executeQuery();
184 notice
.add(rs
.getString("ReferenceNumber"));
185 notice
.add(rs
.getString("Description"));
186 notice
.add(getValidDate(rs
, "EntrustmentDate"));
187 notice
.add (rs
.getInt ("ClientId"));
188 notice
.add (rs
.getBoolean("DraftCreated"));
189 notice
.add (rs
.getBoolean("DraftApproved"));
190 notice
.add (rs
.getBoolean("NoticeSent"));
191 notice
.add (getValidDate(rs
, "SentDate"));
192 notice
.add (rs
.getString("RPADReference"));
193 notice
.add (rs
.getBoolean("NoticeDelivered"));
194 notice
.add (getValidDate(rs
, "DeliveryDate"));
195 notice
.add (rs
.getString("BillStatus"));
196 notice
.add (rs
.getInt ("BillAmount"));
197 notice
.add (getValidDate (rs
, "BillDate"));
198 notice
.add (rs
.getBoolean ("ClarificationPending"));
199 notice
.add (rs
.getString("ClarificationRemarks"));
200 notice
.add (rs
.getString("ClientName"));
203 } catch (SQLException ex
) {
204 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
210 private static final String JDBC
= "jdbc:sqlite:";
212 static boolean updateNoticeDraftStatus(int selectednotice_id
, boolean created
, boolean approved
) {
214 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
215 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
216 + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
217 st
.setBoolean(1, created
);
218 st
.setBoolean(2, approved
);
219 st
.setInt (3, selectednotice_id
);
223 } catch (SQLException ex
) {
224 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
230 static boolean updateNoticeDescription(int selectednotice_id
, String text
) {
232 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
233 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
234 + "SET Description=? WHERE id=?;");
235 st
.setString(1, text
);
236 st
.setInt(2, selectednotice_id
);
240 } catch (SQLException ex
) {
241 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
247 static boolean updateNoticeDispatchDetails(int selid
, boolean notice_sent
,
248 java
.util
.Date sent_date
, String rpad_reference
,
249 boolean notice_delivered
, java
.util
.Date delivery_date
) {
251 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
252 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
253 + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
254 + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
256 st
.setBoolean(1, notice_sent
);
257 st
.setDate(2, toSqlDate(sent_date
));
258 st
.setString(3, rpad_reference
);
259 st
.setBoolean(4, notice_delivered
);
260 st
.setDate(5, toSqlDate(delivery_date
));
261 st
.setInt (6, selid
);
265 } catch (SQLException ex
) {
266 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
272 private static Date
toSqlDate(java
.util
.Date date
) {
275 Date sqldate
= new Date (date
.getTime()/1000);
279 static boolean updateNoticeBillDetails(int selid
, String bill_status
,
280 java
.util
.Date bill_date
, int bill_amount
) {
282 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
283 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
284 + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
285 st
.setString (1, bill_status
);
286 st
.setDate(2, toSqlDate(bill_date
));
287 st
.setInt(3, bill_amount
);
292 } catch (SQLException ex
) {
293 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
299 static boolean createDatabase(String db_path
) {
300 saveConnectionURL(db_path
);
301 String tblClients
= "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
302 " \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
303 " \"ClientName\" VARCHAR(255) NOT NULL UNIQUE,\n" +
304 " \"ClientAddress\" TEXT,\n" +
305 " \"ContactPerson\" TEXT,\n" +
306 " \"MailID\" VARCHAR(255),\n" +
307 " \"ContactNumber\" VARCHAR(30)\n" +
309 String tblNotices
= "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
310 " \"id\" INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
311 " \"ReferenceNumber\" TEXT NOT NULL UNIQUE,\n" +
312 " \"Description\" TEXT NOT NULL,\n" +
313 " \"EntrustmentDate\" INTEGER NOT NULL,\n" +
314 " \"ClientId\" INTEGER,\n" +
315 " \"DraftCreated\" INTEGER DEFAULT 0,\n" +
316 " \"DraftApproved\" INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
317 " \"NoticeSent\" INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
318 " \"SentDate\" INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
319 " \"RPADReference\" TEXT,\n" +
320 " \"NoticeDelivered\" INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
321 " \"DeliveryDate\" INTEGER CHECK(DeliveryDate>=SentDate),\n" +
322 " \"BillStatus\" TEXT DEFAULT 'PENDING',\n" +
323 " \"BillAmount\" INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
324 " \"BillDate\" INTEGER CHECK(BillDate>=DeliveryDate),\n" +
325 " \"ClarificationPending\" INTEGER DEFAULT 0,\n" +
326 " \"ClarificationRemarks\" TEXT\n" +
328 String tblSettings
= "CREATE TABLE IF NOT EXISTS \"settings\" (\n" +
329 " \"key\" TEXT UNIQUE,\n" +
330 " \"value\" TEXT,\n" +
331 " PRIMARY KEY(\"key\")\n" +
334 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
335 Statement st1
= conn
.createStatement();
336 st1
.execute(tblSettings
);
337 st1
.execute(tblClients
);
338 st1
.execute(tblNotices
);
341 } catch (SQLException ex
) {
342 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
348 static boolean updateNoticeClarificationDetails(int selected_id
, boolean
349 clarification_pending
, String clarification_remarks
) {
351 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
352 PreparedStatement st
= conn
.prepareStatement ("UPDATE legalnotices"
353 + " SET ClarificationPending=?, ClarificationRemarks=? "
355 st
.setBoolean(1, clarification_pending
);
356 st
.setString(2, clarification_remarks
);
357 st
.setInt (3, selected_id
);
361 } catch (SQLException ex
) {
362 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
368 static boolean deleteNotice(int r
) {
370 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
371 PreparedStatement st
= conn
.prepareStatement("DELETE FROM legalnotices"
377 } catch (SQLException ex
) {
378 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
386 public static String connectionURL
;
392 public static ArrayList
<Object
> getClientsNameAndId () {
393 ArrayList
<Object
> data
= new ArrayList
<>();
395 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
396 Statement st
= conn
.createStatement();
397 ResultSet rs
= st
.executeQuery("SELECT id, ClientName from clients;");
399 data
.add(rs
.getInt("id"));
400 data
.add(rs
.getString("ClientName"));
404 } catch (SQLException ex
) {
405 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
411 public static void retrieveConnectionURL () {
412 connectionURL
= Preferences
.userRoot().get("ConnectionURL", "legaldb");