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
.io
.FileNotFoundException
;
9 import java
.io
.FileOutputStream
;
10 import java
.io
.FileWriter
;
11 import java
.io
.IOException
;
12 import java
.io
.InputStream
;
13 import java
.util
.ArrayList
;
14 import java
.util
.prefs
.Preferences
;
16 import java
.text
.DateFormat
;
17 import java
.text
.MessageFormat
;
18 import java
.text
.SimpleDateFormat
;
19 import java
.util
.Arrays
;
20 import java
.util
.logging
.Level
;
21 import java
.util
.logging
.Logger
;
22 import jdk
.dynalink
.linker
.support
.Guards
;
28 public class Utility
{
30 static void saveStationerySettings (String left_header
,
31 String right_header
, String signatory
) {
32 leftLetterHeader
= left_header
;
33 rightLetterHeader
= right_header
;
34 signatoryName
= signatory
;
35 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
36 myPrefs
.put ("LeftHeader", left_header
);
37 myPrefs
.put ("RightHeader", right_header
);
38 myPrefs
.put ("Signatory", signatory
);
41 static void retrieveStationerySettings () {
42 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
43 leftLetterHeader
= myPrefs
.get("LeftHeader", "Left Header");
44 rightLetterHeader
= myPrefs
.get ("RightHeader", "Right Header");
45 signatoryName
= myPrefs
.get ("Signatory", "Signatory Name");
48 static void saveConnectionURL(String text
) {
50 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
51 myPrefs
.put("ConnectionURL", text
);
54 static ArrayList
<Object
> getClientDetails(int r
) {
55 ArrayList
<Object
> res
= new ArrayList
<>();
57 Connection conn
= DriverManager
.getConnection(JDBC
+connectionURL
);
58 PreparedStatement st
= conn
.prepareStatement("SELECT * FROM clients WHERE id=?;");
60 ResultSet rs
= st
.executeQuery();
62 res
.add (rs
.getString("ClientName"));
63 res
.add(rs
.getString("ClientAddress"));
64 res
.add (rs
.getString("ContactPerson"));
65 res
.add (rs
.getString("MailID"));
66 res
.add (rs
.getString("ContactNumber"));
71 } catch (SQLException ex
) {
72 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
78 static boolean addClient(String client_name
, String client_address
,
79 String contact_person
, String email_id
, String phone_number
) {
80 if ("".equals(client_name
))
83 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
84 PreparedStatement st
= conn
.prepareStatement("INSERT INTO clients (ClientName,"
85 + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
86 st
.setString(1, client_name
);
87 st
.setString(2, client_address
);
88 st
.setString(3, contact_person
);
89 st
.setString (4, email_id
);
90 st
.setString (5, phone_number
);
95 } catch (SQLException ex
) {
96 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
103 static boolean deleteClient(int r
) {
105 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
106 PreparedStatement st
= conn
.prepareStatement("DELETE FROM clients WHERE id=?;");
107 PreparedStatement st2
= conn
.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
114 } catch (SQLException ex
) {
115 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
121 static boolean updateClient(int r
, String client_name
, String client_address
,
122 String contact_person
, String mail_id
, String phone_number
) {
123 if ("".equals(client_name
))
126 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
127 PreparedStatement st
= conn
.prepareStatement("UPDATE clients SET "
128 + "ClientName=?, ClientAddress=?,"
129 + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
130 st
.setString(1, client_name
);
131 st
.setString(2, client_address
);
132 st
.setString(3, contact_person
);
133 st
.setString(4, mail_id
);
134 st
.setString(5, phone_number
);
139 } catch (SQLException ex
) {
140 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
147 static boolean addLegalNotice(String reference_number
,
148 String description
, java
.util
.Date entrustment_date
, DBItem client
) {
149 if ("".equals(reference_number
) || "".equals(description
) ||
150 entrustment_date
== null || client
== null)
153 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
154 PreparedStatement st
= conn
.prepareStatement("INSERT INTO legalnotices"
155 + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
156 + "VALUES (?, ?, ?, ?);");
157 st
.setString(1, reference_number
);
158 st
.setString(2, description
);
159 st
.setLong(3, entrustment_date
.getTime()/1000);
160 st
.setInt (4, client
.getKey());
165 } catch (SQLException ex
) {
166 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
172 static ArrayList
<Object
> getNotices() {
173 ArrayList
<Object
> notices
= new ArrayList
<>();
175 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
176 Statement st
= conn
.createStatement();
177 ResultSet rs
= st
.executeQuery("SELECT id, ReferenceNumber, Description "
178 + "FROM legalnotices;");
180 notices
.add(rs
.getInt("id"));
181 notices
.add(rs
.getString("ReferenceNumber"));
182 notices
.add(rs
.getString("Description"));
186 } catch (SQLException ex
) {
187 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
192 static java
.util
.Date
getValidDate (ResultSet rs
, String datefield
) throws SQLException
{
193 // Since resultset.getDate returns a java.sql.Date, we need a way to get
194 // the date in java.util.Date which is usable in our application. Hence
195 // this helper function. Since we don't want a valid Date object if the
196 // field is null, we are using this if clause
197 if (rs
.getDate(datefield
) == null)
200 return (new java
.util
.Date(rs
.getLong(datefield
)*1000));
203 static ArrayList
<Object
> getNoticeDetails(int selid
) {
204 ArrayList
<Object
> notice
= new ArrayList
<>();
206 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
207 PreparedStatement st
= conn
.prepareStatement("SELECT legalnotices.*, clients.ClientName "
208 + "FROM legalnotices "
209 + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
211 ResultSet rs
= st
.executeQuery();
213 notice
.add(rs
.getString("ReferenceNumber"));
214 notice
.add(rs
.getString("Description"));
215 notice
.add(getValidDate(rs
, "EntrustmentDate"));
216 notice
.add (rs
.getInt ("ClientId"));
217 notice
.add (rs
.getBoolean("DraftCreated"));
218 notice
.add (rs
.getBoolean("DraftApproved"));
219 notice
.add (rs
.getBoolean("NoticeSent"));
220 notice
.add (getValidDate(rs
, "SentDate"));
221 notice
.add (rs
.getString("RPADReference"));
222 notice
.add (rs
.getBoolean("NoticeDelivered"));
223 notice
.add (getValidDate(rs
, "DeliveryDate"));
224 notice
.add (rs
.getString("BillStatus"));
225 notice
.add (rs
.getInt ("BillAmount"));
226 notice
.add (getValidDate (rs
, "BillDate"));
227 notice
.add (rs
.getBoolean ("ClarificationPending"));
228 notice
.add (rs
.getString("ClarificationRemarks"));
229 notice
.add (rs
.getString("ClientName"));
232 } catch (SQLException ex
) {
233 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
239 private static final String JDBC
= "jdbc:sqlite:";
241 static boolean updateNoticeDraftStatus(int selectednotice_id
, boolean created
, boolean approved
) {
243 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
244 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
245 + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
246 st
.setBoolean(1, created
);
247 st
.setBoolean(2, approved
);
248 st
.setInt (3, selectednotice_id
);
252 } catch (SQLException ex
) {
253 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
259 static boolean updateNoticeDescription(int selectednotice_id
, String text
) {
261 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
262 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
263 + "SET Description=? WHERE id=?;");
264 st
.setString(1, text
);
265 st
.setInt(2, selectednotice_id
);
269 } catch (SQLException ex
) {
270 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
276 static boolean updateNoticeDispatchDetails(int selid
, boolean notice_sent
,
277 java
.util
.Date sent_date
, String rpad_reference
,
278 boolean notice_delivered
, java
.util
.Date delivery_date
) {
280 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
281 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
282 + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
283 + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
285 st
.setBoolean(1, notice_sent
);
286 st
.setDate(2, toSqlDate(sent_date
));
287 st
.setString(3, rpad_reference
);
288 st
.setBoolean(4, notice_delivered
);
289 st
.setDate(5, toSqlDate(delivery_date
));
290 st
.setInt (6, selid
);
294 } catch (SQLException ex
) {
295 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
301 private static Date
toSqlDate(java
.util
.Date date
) {
304 Date sqldate
= new Date (date
.getTime()/1000);
308 static boolean updateNoticeBillDetails(int selid
, String bill_status
,
309 java
.util
.Date bill_date
, int bill_amount
) {
311 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
312 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
313 + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
314 st
.setString (1, bill_status
);
315 st
.setDate(2, toSqlDate(bill_date
));
316 st
.setInt(3, bill_amount
);
321 } catch (SQLException ex
) {
322 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
328 static boolean createDatabase(String db_path
) {
329 saveConnectionURL(db_path
);
330 String tblClients
= "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
331 " \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
332 " \"ClientName\" VARCHAR(255) NOT NULL UNIQUE,\n" +
333 " \"ClientAddress\" TEXT,\n" +
334 " \"ContactPerson\" TEXT,\n" +
335 " \"MailID\" VARCHAR(255),\n" +
336 " \"ContactNumber\" VARCHAR(30)\n" +
338 String tblNotices
= "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
339 " \"id\" INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
340 " \"ReferenceNumber\" TEXT NOT NULL UNIQUE,\n" +
341 " \"Description\" TEXT NOT NULL,\n" +
342 " \"EntrustmentDate\" INTEGER NOT NULL,\n" +
343 " \"ClientId\" INTEGER,\n" +
344 " \"DraftCreated\" INTEGER DEFAULT 0,\n" +
345 " \"DraftApproved\" INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
346 " \"NoticeSent\" INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
347 " \"SentDate\" INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
348 " \"RPADReference\" TEXT,\n" +
349 " \"NoticeDelivered\" INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
350 " \"DeliveryDate\" INTEGER CHECK(DeliveryDate>=SentDate),\n" +
351 " \"BillStatus\" TEXT DEFAULT 'PENDING',\n" +
352 " \"BillAmount\" INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
353 " \"BillDate\" INTEGER CHECK(BillDate>=DeliveryDate),\n" +
354 " \"ClarificationPending\" INTEGER DEFAULT 0,\n" +
355 " \"ClarificationRemarks\" TEXT\n" +
359 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
360 Statement st1
= conn
.createStatement();
361 st1
.execute(tblClients
);
362 st1
.execute(tblNotices
);
365 } catch (SQLException ex
) {
366 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
372 static boolean updateNoticeClarificationDetails(int selected_id
, boolean
373 clarification_pending
, String clarification_remarks
) {
375 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
376 PreparedStatement st
= conn
.prepareStatement ("UPDATE legalnotices"
377 + " SET ClarificationPending=?, ClarificationRemarks=? "
379 st
.setBoolean(1, clarification_pending
);
380 st
.setString(2, clarification_remarks
);
381 st
.setInt (3, selected_id
);
385 } catch (SQLException ex
) {
386 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
392 static boolean deleteNotice(int r
) {
394 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
395 PreparedStatement st
= conn
.prepareStatement("DELETE FROM legalnotices"
401 } catch (SQLException ex
) {
402 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
408 static ArrayList
<Object
> getRaisedBills (int client_id
) {
410 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
411 PreparedStatement st
= conn
.prepareStatement("SELECT ReferenceNumber, "
412 + "Description, BillDate, BillAmount"
413 + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
414 st
.setInt (1, client_id
);
416 ResultSet rs
= st
.executeQuery ();
417 ArrayList
<Object
> bills
= new ArrayList
<>();
419 bills
.add (rs
.getString("ReferenceNumber"));
420 bills
.add (rs
.getString("Description"));
421 bills
.add (getValidDate(rs
, "BillDate"));
422 bills
.add (rs
.getInt("BillAmount"));
427 } catch (SQLException ex
) {
428 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
434 static boolean generateRaisedNoticesBill(String fileName
, String clientName
,
435 String clientAddress
, String contactPerson
, ArrayList
<Object
> bills
) {
439 InputStream templ
= Utility
.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
442 DateFormat fmt
= new SimpleDateFormat("dd MMM yyyy");
443 String templateMain
= new String (templ
.readAllBytes());
444 // this is for openoffice ODT - replace normal line breaks with the XML equivalent
445 String left
= leftLetterHeader
.replaceAll("\n", "<text:line-break/>");
446 String right
= rightLetterHeader
.replaceAll("\n", "<text:line-break/>");
447 String client_address
= clientAddress
.replaceAll ("\n", "<text:line-break/>");
448 String rows
= generateBillRows (bills
);
449 System
.out
.println (rows
);
451 String strMain
= MessageFormat
.format(templateMain
,
453 fmt
.format(new Date(System
.currentTimeMillis())),
454 clientName
, client_address
, contactPerson
, signatoryName
, rows
);
456 FileOutputStream f
= new FileOutputStream (fileName
);
457 f
.write (strMain
.getBytes());
460 } catch (IOException ex
) {
461 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
466 private static String
generateBillRows(ArrayList
<Object
> bills
) {
468 InputStream tmpl
= Utility
.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
469 String rowtpl
= new String(tmpl
.readAllBytes());
471 DateFormat fmt
= new SimpleDateFormat("dd/MM/yyyy");
473 StringBuilder bldr
= new StringBuilder ();
474 for (int i
= 0; i
< bills
.size(); i
+= 4) {
475 String row
= MessageFormat
.format (rowtpl
, (String
)bills
.get(i
),
476 (String
)bills
.get(i
+1), fmt
.format((java
.util
.Date
)bills
.get(i
+2)),
477 (int)bills
.get(i
+3));
480 return (bldr
.toString());
481 } catch (IOException ex
) {
482 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
487 static boolean updateNoticeBillStatus(int client_id
, String from_status
,
490 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
491 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
492 + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
493 st
.setString (1, to_status
);
494 st
.setString (2, from_status
);
495 st
.setInt (3, client_id
);
499 } catch (SQLException ex
) {
500 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
506 static ArrayList
<Object
> getPendingPaymentBills(int clientid
) {
507 ArrayList
<Object
> data
= new ArrayList
<>();
509 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
511 // if no client ID is specified i.e. -1 get ll
513 if (clientid
== -1) {
514 Statement st
= conn
.createStatement();
515 rs
= st
.executeQuery("SELECT ReferenceNumber, Description, "
516 + "BillDate, BillAmount, ClientName"
517 + " FROM legalnotices INNER JOIN clients ON ClientId=clients.id "
518 + "WHERE BillStatus='AWAITING PAYMENT';");
521 PreparedStatement st
= conn
.prepareStatement("SELECT ReferenceNumber, "
522 + "Description, BillDate, BillAmount, ClientName"
523 + " FROM legalnotices INNER JOIN clients ON ClientId=clients.id "
524 + "WHERE BillStatus='AWAITING PAYMENT' AND ClientId=?;");
525 st
.setInt (1, clientid
);
526 rs
= st
.executeQuery();
528 DateFormat fmt
= new SimpleDateFormat ("dd/MM/yyyy");
530 data
.add (rs
.getString("ReferenceNumber"));
531 data
.add (rs
.getString("Description"));
532 data
.add (fmt
.format (getValidDate(rs
, "BillDate")));
533 data
.add (rs
.getInt ("BillAmount"));
534 data
.add (rs
.getString("ClientName"));
538 } catch (SQLException ex
) {
539 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
545 static boolean saveReportCSV(String filePath
, String
[] reportCols
, ArrayList
<Object
> reportData
) {
547 FileWriter f
= new FileWriter (filePath
);
548 for (int i
= 0; i
< reportCols
.length
; i
++) {
549 f
.append (escapeQuote(reportCols
[i
]));
553 for (int i
= 0; i
< reportData
.size(); i
+= reportCols
.length
) {
554 for (int j
= 0; j
< reportCols
.length
; j
++) {
555 f
.append (escapeQuote(reportData
.get(i
+j
)));
563 } catch (IOException ex
) {
564 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
570 private static String
escapeQuote(Object bareStr
) {
571 if (bareStr
.getClass() == String
.class) {
572 String str
= (String
)bareStr
;
573 String escapedStr
= str
.replace ("\"", "\"\"");
574 System
.out
.println(escapedStr
);
575 String finalStr
= String
.format ("\"%s\"", escapedStr
);
579 return String
.format("\"%s\"", bareStr
.toString());
584 public static String connectionURL
;
585 public static String leftLetterHeader
;
586 public static String rightLetterHeader
;
587 public static String signatoryName
;
593 public static ArrayList
<Object
> getClientsNameAndId () {
594 ArrayList
<Object
> data
= new ArrayList
<>();
596 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
597 Statement st
= conn
.createStatement();
598 ResultSet rs
= st
.executeQuery("SELECT id, ClientName from clients;");
600 data
.add(rs
.getInt("id"));
601 data
.add(rs
.getString("ClientName"));
605 } catch (SQLException ex
) {
606 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
612 public static void retrieveConnectionURL () {
613 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
614 connectionURL
= myPrefs
.get("ConnectionURL", "legaldb");