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
.FileOutputStream
;
9 import java
.io
.FileWriter
;
10 import java
.io
.IOException
;
11 import java
.io
.InputStream
;
12 import java
.util
.ArrayList
;
13 import java
.util
.prefs
.Preferences
;
15 import java
.text
.DateFormat
;
16 import java
.text
.MessageFormat
;
17 import java
.text
.SimpleDateFormat
;
18 import java
.util
.logging
.Level
;
19 import java
.util
.logging
.Logger
;
25 public class Utility
{
27 private static final String
[] REPORTS_UNFILTERED
= { "SELECT ReferenceNumber, Description"
28 + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
29 + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT';",
31 "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
32 + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
33 + " WHERE NoticeSent=1 AND NoticeDelivered=0;",
35 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
36 + " INNER JOIN clients ON ClientId=clients.id"
37 + " WHERE DraftCreated=1 AND DraftApproved=0;",
39 "SELECT ReferenceNumber, Description, EntrustmentDate, "
40 + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
41 + " ClientId=clients.id WHERE ClarificationPending=1;",
43 "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
44 + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
45 + "WHERE DraftCreated=0;"
48 private static final String
[] REPORTS_FILTERED
= { "SELECT ReferenceNumber, Description"
49 + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
50 + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT' "
53 "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
54 + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
55 + " WHERE NoticeSent=1 AND NoticeDelivered=0 AND ClientId=?;",
57 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
58 + " INNER JOIN clients ON ClientId=clients.id"
59 + " WHERE DraftCreated=1 AND DraftApproved=0 AND ClientId=?;",
61 "SELECT ReferenceNumber, Description, EntrustmentDate, "
62 + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
63 + " ClientId=clients.id WHERE ClarificationPending=1 AND ClientId=?;",
65 "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
66 + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
67 + "WHERE DraftCreated=0 AND ClientId=?;"
70 static void saveStationerySettings (String left_header
,
71 String right_header
, String signatory
) {
72 leftLetterHeader
= left_header
;
73 rightLetterHeader
= right_header
;
74 signatoryName
= signatory
;
75 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
76 myPrefs
.put ("LeftHeader", left_header
);
77 myPrefs
.put ("RightHeader", right_header
);
78 myPrefs
.put ("Signatory", signatory
);
81 static void retrieveStationerySettings () {
82 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
83 leftLetterHeader
= myPrefs
.get("LeftHeader", "Left Header");
84 rightLetterHeader
= myPrefs
.get ("RightHeader", "Right Header");
85 signatoryName
= myPrefs
.get ("Signatory", "Signatory Name");
88 static void saveConnectionURL(String text
) {
90 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
91 myPrefs
.put("ConnectionURL", text
);
94 static ArrayList
<Object
> getClientDetails(int r
) {
95 ArrayList
<Object
> res
= new ArrayList
<>();
97 Connection conn
= DriverManager
.getConnection(JDBC
+connectionURL
);
98 PreparedStatement st
= conn
.prepareStatement("SELECT * FROM clients WHERE id=?;");
100 ResultSet rs
= st
.executeQuery();
102 res
.add (rs
.getString("ClientName"));
103 res
.add(rs
.getString("ClientAddress"));
104 res
.add (rs
.getString("ContactPerson"));
105 res
.add (rs
.getString("MailID"));
106 res
.add (rs
.getString("ContactNumber"));
111 } catch (SQLException ex
) {
112 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
118 static boolean addClient(String client_name
, String client_address
,
119 String contact_person
, String email_id
, String phone_number
) {
120 if ("".equals(client_name
))
123 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
124 PreparedStatement st
= conn
.prepareStatement("INSERT INTO clients (ClientName,"
125 + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
126 st
.setString(1, client_name
);
127 st
.setString(2, client_address
);
128 st
.setString(3, contact_person
);
129 st
.setString (4, email_id
);
130 st
.setString (5, phone_number
);
135 } catch (SQLException ex
) {
136 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
143 static boolean deleteClient(int r
) {
145 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
146 PreparedStatement st
= conn
.prepareStatement("DELETE FROM clients WHERE id=?;");
147 PreparedStatement st2
= conn
.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
154 } catch (SQLException ex
) {
155 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
161 static boolean updateClient(int r
, String client_name
, String client_address
,
162 String contact_person
, String mail_id
, String phone_number
) {
163 if ("".equals(client_name
))
166 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
167 PreparedStatement st
= conn
.prepareStatement("UPDATE clients SET "
168 + "ClientName=?, ClientAddress=?,"
169 + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
170 st
.setString(1, client_name
);
171 st
.setString(2, client_address
);
172 st
.setString(3, contact_person
);
173 st
.setString(4, mail_id
);
174 st
.setString(5, phone_number
);
179 } catch (SQLException ex
) {
180 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
187 static boolean addLegalNotice(String reference_number
,
188 String description
, java
.util
.Date entrustment_date
, DBItem client
) {
189 if ("".equals(reference_number
) || "".equals(description
) ||
190 entrustment_date
== null || client
== null)
193 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
194 PreparedStatement st
= conn
.prepareStatement("INSERT INTO legalnotices"
195 + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
196 + "VALUES (?, ?, ?, ?);");
197 st
.setString(1, reference_number
);
198 st
.setString(2, description
);
199 st
.setLong(3, entrustment_date
.getTime()/1000);
200 st
.setInt (4, client
.getKey());
205 } catch (SQLException ex
) {
206 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
212 static ArrayList
<Object
> getNotices() {
213 ArrayList
<Object
> notices
= new ArrayList
<>();
215 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
216 Statement st
= conn
.createStatement();
217 ResultSet rs
= st
.executeQuery("SELECT id, ReferenceNumber, Description "
218 + "FROM legalnotices;");
220 notices
.add(rs
.getInt("id"));
221 notices
.add(rs
.getString("ReferenceNumber"));
222 notices
.add(rs
.getString("Description"));
226 } catch (SQLException ex
) {
227 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
232 static java
.util
.Date
getValidDate (ResultSet rs
, String datefield
) throws SQLException
{
233 // Since resultset.getDate returns a java.sql.Date, we need a way to get
234 // the date in java.util.Date which is usable in our application. Hence
235 // this helper function. Since we don't want a valid Date object if the
236 // field is null, we are using this if clause
237 if (rs
.getDate(datefield
) == null)
240 return (new java
.util
.Date(rs
.getLong(datefield
)*1000));
243 static ArrayList
<Object
> getNoticeDetails(int selid
) {
244 ArrayList
<Object
> notice
= new ArrayList
<>();
246 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
247 PreparedStatement st
= conn
.prepareStatement("SELECT legalnotices.*, clients.ClientName "
248 + "FROM legalnotices "
249 + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
251 ResultSet rs
= st
.executeQuery();
253 notice
.add(rs
.getString("ReferenceNumber"));
254 notice
.add(rs
.getString("Description"));
255 notice
.add(getValidDate(rs
, "EntrustmentDate"));
256 notice
.add (rs
.getInt ("ClientId"));
257 notice
.add (rs
.getBoolean("DraftCreated"));
258 notice
.add (rs
.getBoolean("DraftApproved"));
259 notice
.add (rs
.getBoolean("NoticeSent"));
260 notice
.add (getValidDate(rs
, "SentDate"));
261 notice
.add (rs
.getString("RPADReference"));
262 notice
.add (rs
.getBoolean("NoticeDelivered"));
263 notice
.add (getValidDate(rs
, "DeliveryDate"));
264 notice
.add (rs
.getString("BillStatus"));
265 notice
.add (rs
.getInt ("BillAmount"));
266 notice
.add (getValidDate (rs
, "BillDate"));
267 notice
.add (rs
.getBoolean ("ClarificationPending"));
268 notice
.add (rs
.getString("ClarificationRemarks"));
269 notice
.add (rs
.getString("ClientName"));
272 } catch (SQLException ex
) {
273 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
279 private static final String JDBC
= "jdbc:sqlite:";
281 static boolean updateNoticeDraftStatus(int selectednotice_id
, boolean created
, boolean approved
) {
283 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
284 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
285 + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
286 st
.setBoolean(1, created
);
287 st
.setBoolean(2, approved
);
288 st
.setInt (3, selectednotice_id
);
292 } catch (SQLException ex
) {
293 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
299 static boolean updateNoticeDescription(int selectednotice_id
, String text
) {
301 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
302 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
303 + "SET Description=? WHERE id=?;");
304 st
.setString(1, text
);
305 st
.setInt(2, selectednotice_id
);
309 } catch (SQLException ex
) {
310 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
316 static boolean updateNoticeDispatchDetails(int selid
, boolean notice_sent
,
317 java
.util
.Date sent_date
, String rpad_reference
,
318 boolean notice_delivered
, java
.util
.Date delivery_date
) {
320 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
321 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
322 + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
323 + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
325 st
.setBoolean(1, notice_sent
);
326 st
.setDate(2, toSqlDate(sent_date
));
327 st
.setString(3, rpad_reference
);
328 st
.setBoolean(4, notice_delivered
);
329 st
.setDate(5, toSqlDate(delivery_date
));
330 st
.setInt (6, selid
);
334 } catch (SQLException ex
) {
335 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
341 private static Date
toSqlDate(java
.util
.Date date
) {
344 Date sqldate
= new Date (date
.getTime()/1000);
348 static boolean updateNoticeBillDetails(int selid
, String bill_status
,
349 java
.util
.Date bill_date
, int bill_amount
) {
351 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
352 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
353 + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
354 st
.setString (1, bill_status
);
355 st
.setDate(2, toSqlDate(bill_date
));
356 st
.setInt(3, bill_amount
);
361 } catch (SQLException ex
) {
362 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
368 static boolean createDatabase(String db_path
) {
369 saveConnectionURL(db_path
);
370 String tblClients
= "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
371 " \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
372 " \"ClientName\" VARCHAR(255) NOT NULL UNIQUE,\n" +
373 " \"ClientAddress\" TEXT,\n" +
374 " \"ContactPerson\" TEXT,\n" +
375 " \"MailID\" VARCHAR(255),\n" +
376 " \"ContactNumber\" VARCHAR(30)\n" +
378 String tblNotices
= "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
379 " \"id\" INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
380 " \"ReferenceNumber\" TEXT NOT NULL UNIQUE,\n" +
381 " \"Description\" TEXT NOT NULL,\n" +
382 " \"EntrustmentDate\" INTEGER NOT NULL,\n" +
383 " \"ClientId\" INTEGER,\n" +
384 " \"DraftCreated\" INTEGER DEFAULT 0,\n" +
385 " \"DraftApproved\" INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
386 " \"NoticeSent\" INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
387 " \"SentDate\" INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
388 " \"RPADReference\" TEXT,\n" +
389 " \"NoticeDelivered\" INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
390 " \"DeliveryDate\" INTEGER CHECK(DeliveryDate>=SentDate),\n" +
391 " \"BillStatus\" TEXT DEFAULT 'PENDING',\n" +
392 " \"BillAmount\" INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
393 " \"BillDate\" INTEGER CHECK(BillDate>=DeliveryDate),\n" +
394 " \"ClarificationPending\" INTEGER DEFAULT 0,\n" +
395 " \"ClarificationRemarks\" TEXT\n" +
399 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
400 Statement st1
= conn
.createStatement();
401 st1
.execute(tblClients
);
402 st1
.execute(tblNotices
);
405 } catch (SQLException ex
) {
406 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
412 static boolean updateNoticeClarificationDetails(int selected_id
, boolean
413 clarification_pending
, String clarification_remarks
) {
415 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
416 PreparedStatement st
= conn
.prepareStatement ("UPDATE legalnotices"
417 + " SET ClarificationPending=?, ClarificationRemarks=? "
419 st
.setBoolean(1, clarification_pending
);
420 st
.setString(2, clarification_remarks
);
421 st
.setInt (3, selected_id
);
425 } catch (SQLException ex
) {
426 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
432 static boolean deleteNotice(int r
) {
434 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
435 PreparedStatement st
= conn
.prepareStatement("DELETE FROM legalnotices"
441 } catch (SQLException ex
) {
442 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
448 static ArrayList
<Object
> getRaisedBills (int client_id
) {
450 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
451 PreparedStatement st
= conn
.prepareStatement("SELECT ReferenceNumber, "
452 + "Description, BillDate, BillAmount"
453 + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
454 st
.setInt (1, client_id
);
456 ResultSet rs
= st
.executeQuery ();
457 ArrayList
<Object
> bills
= new ArrayList
<>();
459 bills
.add (rs
.getString("ReferenceNumber"));
460 bills
.add (rs
.getString("Description"));
461 bills
.add (getValidDate(rs
, "BillDate"));
462 bills
.add (rs
.getInt("BillAmount"));
467 } catch (SQLException ex
) {
468 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
474 static boolean generateRaisedNoticesBill(String fileName
, String clientName
,
475 String clientAddress
, String contactPerson
, ArrayList
<Object
> bills
) {
479 InputStream templ
= Utility
.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
482 DateFormat fmt
= new SimpleDateFormat("dd MMM yyyy");
483 String templateMain
= new String (templ
.readAllBytes());
484 // this is for openoffice ODT - replace normal line breaks with the XML equivalent
485 String left
= leftLetterHeader
.replaceAll("\n", "<text:line-break/>");
486 String right
= rightLetterHeader
.replaceAll("\n", "<text:line-break/>");
487 String client_address
= clientAddress
.replaceAll ("\n", "<text:line-break/>");
488 String rows
= generateBillRows (bills
);
489 System
.out
.println (rows
);
491 String strMain
= MessageFormat
.format(templateMain
,
493 fmt
.format(new Date(System
.currentTimeMillis())),
494 clientName
, client_address
, contactPerson
, signatoryName
, rows
);
496 FileOutputStream f
= new FileOutputStream (fileName
);
497 f
.write (strMain
.getBytes());
500 } catch (IOException ex
) {
501 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
506 private static String
generateBillRows(ArrayList
<Object
> bills
) {
508 InputStream tmpl
= Utility
.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
509 String rowtpl
= new String(tmpl
.readAllBytes());
511 DateFormat fmt
= new SimpleDateFormat("dd/MM/yyyy");
513 StringBuilder bldr
= new StringBuilder ();
514 for (int i
= 0; i
< bills
.size(); i
+= 4) {
515 String row
= MessageFormat
.format (rowtpl
, (String
)bills
.get(i
),
516 (String
)bills
.get(i
+1), fmt
.format((java
.util
.Date
)bills
.get(i
+2)),
517 (int)bills
.get(i
+3));
520 return (bldr
.toString());
521 } catch (IOException ex
) {
522 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
527 static boolean updateNoticeBillStatus(int client_id
, String from_status
,
530 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
531 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
532 + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
533 st
.setString (1, to_status
);
534 st
.setString (2, from_status
);
535 st
.setInt (3, client_id
);
539 } catch (SQLException ex
) {
540 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
546 static ArrayList
<Object
> getReportData(int report_num
, int clientid
) {
547 ArrayList
<Object
> data
= new ArrayList
<>();
549 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
551 // if no client ID is specified i.e. -1 get ll
553 if (clientid
== -1) {
554 Statement st
= conn
.createStatement();
555 rs
= st
.executeQuery(REPORTS_UNFILTERED
[report_num
]);
558 PreparedStatement st
= conn
.prepareStatement(REPORTS_FILTERED
[report_num
]);
559 st
.setInt (1, clientid
);
560 rs
= st
.executeQuery();
564 ResultSetMetaData md
= rs
.getMetaData();
565 SimpleDateFormat fmt
= new SimpleDateFormat ("dd MMM yyyy");
566 for (int i
= 1; i
<= md
.getColumnCount(); i
++ ) {
567 // for INTEGER Columns which are date alone, handle separately
568 if (md
.getColumnName(i
).contains("Date"))
569 data
.add (fmt
.format(getValidDate(rs
, md
.getColumnName(i
))));
571 data
.add (rs
.getObject (i
));
576 } catch (SQLException ex
) {
577 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
583 static boolean saveReportCSV(String filePath
, String
[] reportCols
, ArrayList
<Object
> reportData
) {
585 FileWriter f
= new FileWriter (filePath
);
586 for (int i
= 0; i
< reportCols
.length
; i
++) {
587 f
.append (escapeQuote(reportCols
[i
]));
591 for (int i
= 0; i
< reportData
.size(); i
+= reportCols
.length
) {
592 for (int j
= 0; j
< reportCols
.length
; j
++) {
593 f
.append (escapeQuote(reportData
.get(i
+j
)));
601 } catch (IOException ex
) {
602 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
608 private static String
escapeQuote(Object bareStr
) {
609 if (bareStr
.getClass() == String
.class) {
610 String str
= (String
)bareStr
;
611 String escapedStr
= str
.replace ("\"", "\"\"");
612 System
.out
.println(escapedStr
);
613 String finalStr
= String
.format ("\"%s\"", escapedStr
);
617 return String
.format("\"%s\"", bareStr
.toString());
624 public static String connectionURL
;
625 public static String leftLetterHeader
;
626 public static String rightLetterHeader
;
627 public static String signatoryName
;
633 public static ArrayList
<Object
> getClientsNameAndId () {
634 ArrayList
<Object
> data
= new ArrayList
<>();
636 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
637 Statement st
= conn
.createStatement();
638 ResultSet rs
= st
.executeQuery("SELECT id, ClientName from clients;");
640 data
.add(rs
.getInt("id"));
641 data
.add(rs
.getString("ClientName"));
645 } catch (SQLException ex
) {
646 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
652 public static void retrieveConnectionURL () {
653 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
654 connectionURL
= myPrefs
.get("ConnectionURL", "legaldb");