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;"
36 private static final String
[] REPORTS_FILTERED
= { "SELECT ReferenceNumber, Description"
37 + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
38 + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT' "
41 "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
42 + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
43 + " WHERE NoticeSent=1 AND NoticeDelivered=0 AND ClientId=?;"
46 static void saveStationerySettings (String left_header
,
47 String right_header
, String signatory
) {
48 leftLetterHeader
= left_header
;
49 rightLetterHeader
= right_header
;
50 signatoryName
= signatory
;
51 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
52 myPrefs
.put ("LeftHeader", left_header
);
53 myPrefs
.put ("RightHeader", right_header
);
54 myPrefs
.put ("Signatory", signatory
);
57 static void retrieveStationerySettings () {
58 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
59 leftLetterHeader
= myPrefs
.get("LeftHeader", "Left Header");
60 rightLetterHeader
= myPrefs
.get ("RightHeader", "Right Header");
61 signatoryName
= myPrefs
.get ("Signatory", "Signatory Name");
64 static void saveConnectionURL(String text
) {
66 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
67 myPrefs
.put("ConnectionURL", text
);
70 static ArrayList
<Object
> getClientDetails(int r
) {
71 ArrayList
<Object
> res
= new ArrayList
<>();
73 Connection conn
= DriverManager
.getConnection(JDBC
+connectionURL
);
74 PreparedStatement st
= conn
.prepareStatement("SELECT * FROM clients WHERE id=?;");
76 ResultSet rs
= st
.executeQuery();
78 res
.add (rs
.getString("ClientName"));
79 res
.add(rs
.getString("ClientAddress"));
80 res
.add (rs
.getString("ContactPerson"));
81 res
.add (rs
.getString("MailID"));
82 res
.add (rs
.getString("ContactNumber"));
87 } catch (SQLException ex
) {
88 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
94 static boolean addClient(String client_name
, String client_address
,
95 String contact_person
, String email_id
, String phone_number
) {
96 if ("".equals(client_name
))
99 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
100 PreparedStatement st
= conn
.prepareStatement("INSERT INTO clients (ClientName,"
101 + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
102 st
.setString(1, client_name
);
103 st
.setString(2, client_address
);
104 st
.setString(3, contact_person
);
105 st
.setString (4, email_id
);
106 st
.setString (5, phone_number
);
111 } catch (SQLException ex
) {
112 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
119 static boolean deleteClient(int r
) {
121 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
122 PreparedStatement st
= conn
.prepareStatement("DELETE FROM clients WHERE id=?;");
123 PreparedStatement st2
= conn
.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
130 } catch (SQLException ex
) {
131 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
137 static boolean updateClient(int r
, String client_name
, String client_address
,
138 String contact_person
, String mail_id
, String phone_number
) {
139 if ("".equals(client_name
))
142 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
143 PreparedStatement st
= conn
.prepareStatement("UPDATE clients SET "
144 + "ClientName=?, ClientAddress=?,"
145 + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
146 st
.setString(1, client_name
);
147 st
.setString(2, client_address
);
148 st
.setString(3, contact_person
);
149 st
.setString(4, mail_id
);
150 st
.setString(5, phone_number
);
155 } catch (SQLException ex
) {
156 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
163 static boolean addLegalNotice(String reference_number
,
164 String description
, java
.util
.Date entrustment_date
, DBItem client
) {
165 if ("".equals(reference_number
) || "".equals(description
) ||
166 entrustment_date
== null || client
== null)
169 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
170 PreparedStatement st
= conn
.prepareStatement("INSERT INTO legalnotices"
171 + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
172 + "VALUES (?, ?, ?, ?);");
173 st
.setString(1, reference_number
);
174 st
.setString(2, description
);
175 st
.setLong(3, entrustment_date
.getTime()/1000);
176 st
.setInt (4, client
.getKey());
181 } catch (SQLException ex
) {
182 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
188 static ArrayList
<Object
> getNotices() {
189 ArrayList
<Object
> notices
= new ArrayList
<>();
191 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
192 Statement st
= conn
.createStatement();
193 ResultSet rs
= st
.executeQuery("SELECT id, ReferenceNumber, Description "
194 + "FROM legalnotices;");
196 notices
.add(rs
.getInt("id"));
197 notices
.add(rs
.getString("ReferenceNumber"));
198 notices
.add(rs
.getString("Description"));
202 } catch (SQLException ex
) {
203 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
208 static java
.util
.Date
getValidDate (ResultSet rs
, String datefield
) throws SQLException
{
209 // Since resultset.getDate returns a java.sql.Date, we need a way to get
210 // the date in java.util.Date which is usable in our application. Hence
211 // this helper function. Since we don't want a valid Date object if the
212 // field is null, we are using this if clause
213 if (rs
.getDate(datefield
) == null)
216 return (new java
.util
.Date(rs
.getLong(datefield
)*1000));
219 static ArrayList
<Object
> getNoticeDetails(int selid
) {
220 ArrayList
<Object
> notice
= new ArrayList
<>();
222 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
223 PreparedStatement st
= conn
.prepareStatement("SELECT legalnotices.*, clients.ClientName "
224 + "FROM legalnotices "
225 + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
227 ResultSet rs
= st
.executeQuery();
229 notice
.add(rs
.getString("ReferenceNumber"));
230 notice
.add(rs
.getString("Description"));
231 notice
.add(getValidDate(rs
, "EntrustmentDate"));
232 notice
.add (rs
.getInt ("ClientId"));
233 notice
.add (rs
.getBoolean("DraftCreated"));
234 notice
.add (rs
.getBoolean("DraftApproved"));
235 notice
.add (rs
.getBoolean("NoticeSent"));
236 notice
.add (getValidDate(rs
, "SentDate"));
237 notice
.add (rs
.getString("RPADReference"));
238 notice
.add (rs
.getBoolean("NoticeDelivered"));
239 notice
.add (getValidDate(rs
, "DeliveryDate"));
240 notice
.add (rs
.getString("BillStatus"));
241 notice
.add (rs
.getInt ("BillAmount"));
242 notice
.add (getValidDate (rs
, "BillDate"));
243 notice
.add (rs
.getBoolean ("ClarificationPending"));
244 notice
.add (rs
.getString("ClarificationRemarks"));
245 notice
.add (rs
.getString("ClientName"));
248 } catch (SQLException ex
) {
249 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
255 private static final String JDBC
= "jdbc:sqlite:";
257 static boolean updateNoticeDraftStatus(int selectednotice_id
, boolean created
, boolean approved
) {
259 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
260 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
261 + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
262 st
.setBoolean(1, created
);
263 st
.setBoolean(2, approved
);
264 st
.setInt (3, selectednotice_id
);
268 } catch (SQLException ex
) {
269 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
275 static boolean updateNoticeDescription(int selectednotice_id
, String text
) {
277 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
278 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
279 + "SET Description=? WHERE id=?;");
280 st
.setString(1, text
);
281 st
.setInt(2, selectednotice_id
);
285 } catch (SQLException ex
) {
286 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
292 static boolean updateNoticeDispatchDetails(int selid
, boolean notice_sent
,
293 java
.util
.Date sent_date
, String rpad_reference
,
294 boolean notice_delivered
, java
.util
.Date delivery_date
) {
296 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
297 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
298 + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
299 + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
301 st
.setBoolean(1, notice_sent
);
302 st
.setDate(2, toSqlDate(sent_date
));
303 st
.setString(3, rpad_reference
);
304 st
.setBoolean(4, notice_delivered
);
305 st
.setDate(5, toSqlDate(delivery_date
));
306 st
.setInt (6, selid
);
310 } catch (SQLException ex
) {
311 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
317 private static Date
toSqlDate(java
.util
.Date date
) {
320 Date sqldate
= new Date (date
.getTime()/1000);
324 static boolean updateNoticeBillDetails(int selid
, String bill_status
,
325 java
.util
.Date bill_date
, int bill_amount
) {
327 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
328 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
329 + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
330 st
.setString (1, bill_status
);
331 st
.setDate(2, toSqlDate(bill_date
));
332 st
.setInt(3, bill_amount
);
337 } catch (SQLException ex
) {
338 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
344 static boolean createDatabase(String db_path
) {
345 saveConnectionURL(db_path
);
346 String tblClients
= "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
347 " \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
348 " \"ClientName\" VARCHAR(255) NOT NULL UNIQUE,\n" +
349 " \"ClientAddress\" TEXT,\n" +
350 " \"ContactPerson\" TEXT,\n" +
351 " \"MailID\" VARCHAR(255),\n" +
352 " \"ContactNumber\" VARCHAR(30)\n" +
354 String tblNotices
= "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
355 " \"id\" INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
356 " \"ReferenceNumber\" TEXT NOT NULL UNIQUE,\n" +
357 " \"Description\" TEXT NOT NULL,\n" +
358 " \"EntrustmentDate\" INTEGER NOT NULL,\n" +
359 " \"ClientId\" INTEGER,\n" +
360 " \"DraftCreated\" INTEGER DEFAULT 0,\n" +
361 " \"DraftApproved\" INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
362 " \"NoticeSent\" INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
363 " \"SentDate\" INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
364 " \"RPADReference\" TEXT,\n" +
365 " \"NoticeDelivered\" INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
366 " \"DeliveryDate\" INTEGER CHECK(DeliveryDate>=SentDate),\n" +
367 " \"BillStatus\" TEXT DEFAULT 'PENDING',\n" +
368 " \"BillAmount\" INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
369 " \"BillDate\" INTEGER CHECK(BillDate>=DeliveryDate),\n" +
370 " \"ClarificationPending\" INTEGER DEFAULT 0,\n" +
371 " \"ClarificationRemarks\" TEXT\n" +
375 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
376 Statement st1
= conn
.createStatement();
377 st1
.execute(tblClients
);
378 st1
.execute(tblNotices
);
381 } catch (SQLException ex
) {
382 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
388 static boolean updateNoticeClarificationDetails(int selected_id
, boolean
389 clarification_pending
, String clarification_remarks
) {
391 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
392 PreparedStatement st
= conn
.prepareStatement ("UPDATE legalnotices"
393 + " SET ClarificationPending=?, ClarificationRemarks=? "
395 st
.setBoolean(1, clarification_pending
);
396 st
.setString(2, clarification_remarks
);
397 st
.setInt (3, selected_id
);
401 } catch (SQLException ex
) {
402 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
408 static boolean deleteNotice(int r
) {
410 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
411 PreparedStatement st
= conn
.prepareStatement("DELETE FROM legalnotices"
417 } catch (SQLException ex
) {
418 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
424 static ArrayList
<Object
> getRaisedBills (int client_id
) {
426 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
427 PreparedStatement st
= conn
.prepareStatement("SELECT ReferenceNumber, "
428 + "Description, BillDate, BillAmount"
429 + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
430 st
.setInt (1, client_id
);
432 ResultSet rs
= st
.executeQuery ();
433 ArrayList
<Object
> bills
= new ArrayList
<>();
435 bills
.add (rs
.getString("ReferenceNumber"));
436 bills
.add (rs
.getString("Description"));
437 bills
.add (getValidDate(rs
, "BillDate"));
438 bills
.add (rs
.getInt("BillAmount"));
443 } catch (SQLException ex
) {
444 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
450 static boolean generateRaisedNoticesBill(String fileName
, String clientName
,
451 String clientAddress
, String contactPerson
, ArrayList
<Object
> bills
) {
455 InputStream templ
= Utility
.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
458 DateFormat fmt
= new SimpleDateFormat("dd MMM yyyy");
459 String templateMain
= new String (templ
.readAllBytes());
460 // this is for openoffice ODT - replace normal line breaks with the XML equivalent
461 String left
= leftLetterHeader
.replaceAll("\n", "<text:line-break/>");
462 String right
= rightLetterHeader
.replaceAll("\n", "<text:line-break/>");
463 String client_address
= clientAddress
.replaceAll ("\n", "<text:line-break/>");
464 String rows
= generateBillRows (bills
);
465 System
.out
.println (rows
);
467 String strMain
= MessageFormat
.format(templateMain
,
469 fmt
.format(new Date(System
.currentTimeMillis())),
470 clientName
, client_address
, contactPerson
, signatoryName
, rows
);
472 FileOutputStream f
= new FileOutputStream (fileName
);
473 f
.write (strMain
.getBytes());
476 } catch (IOException ex
) {
477 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
482 private static String
generateBillRows(ArrayList
<Object
> bills
) {
484 InputStream tmpl
= Utility
.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
485 String rowtpl
= new String(tmpl
.readAllBytes());
487 DateFormat fmt
= new SimpleDateFormat("dd/MM/yyyy");
489 StringBuilder bldr
= new StringBuilder ();
490 for (int i
= 0; i
< bills
.size(); i
+= 4) {
491 String row
= MessageFormat
.format (rowtpl
, (String
)bills
.get(i
),
492 (String
)bills
.get(i
+1), fmt
.format((java
.util
.Date
)bills
.get(i
+2)),
493 (int)bills
.get(i
+3));
496 return (bldr
.toString());
497 } catch (IOException ex
) {
498 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
503 static boolean updateNoticeBillStatus(int client_id
, String from_status
,
506 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
507 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
508 + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
509 st
.setString (1, to_status
);
510 st
.setString (2, from_status
);
511 st
.setInt (3, client_id
);
515 } catch (SQLException ex
) {
516 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
522 static ArrayList
<Object
> getReportData(int report_num
, int clientid
) {
523 ArrayList
<Object
> data
= new ArrayList
<>();
525 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
527 // if no client ID is specified i.e. -1 get ll
529 if (clientid
== -1) {
530 Statement st
= conn
.createStatement();
531 rs
= st
.executeQuery(REPORTS_UNFILTERED
[report_num
]);
534 PreparedStatement st
= conn
.prepareStatement(REPORTS_FILTERED
[report_num
]);
535 st
.setInt (1, clientid
);
536 rs
= st
.executeQuery();
540 ResultSetMetaData md
= rs
.getMetaData();
541 SimpleDateFormat fmt
= new SimpleDateFormat ("dd MMM yyyy");
542 for (int i
= 1; i
<= md
.getColumnCount(); i
++ ) {
543 // for INTEGER Columns which are date alone, handle separately
544 if (md
.getColumnName(i
).contains("Date"))
545 data
.add (fmt
.format(getValidDate(rs
, md
.getColumnName(i
))));
547 data
.add (rs
.getObject (i
));
552 } catch (SQLException ex
) {
553 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
559 static boolean saveReportCSV(String filePath
, String
[] reportCols
, ArrayList
<Object
> reportData
) {
561 FileWriter f
= new FileWriter (filePath
);
562 for (int i
= 0; i
< reportCols
.length
; i
++) {
563 f
.append (escapeQuote(reportCols
[i
]));
567 for (int i
= 0; i
< reportData
.size(); i
+= reportCols
.length
) {
568 for (int j
= 0; j
< reportCols
.length
; j
++) {
569 f
.append (escapeQuote(reportData
.get(i
+j
)));
577 } catch (IOException ex
) {
578 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
584 private static String
escapeQuote(Object bareStr
) {
585 if (bareStr
.getClass() == String
.class) {
586 String str
= (String
)bareStr
;
587 String escapedStr
= str
.replace ("\"", "\"\"");
588 System
.out
.println(escapedStr
);
589 String finalStr
= String
.format ("\"%s\"", escapedStr
);
593 return String
.format("\"%s\"", bareStr
.toString());
600 public static String connectionURL
;
601 public static String leftLetterHeader
;
602 public static String rightLetterHeader
;
603 public static String signatoryName
;
609 public static ArrayList
<Object
> getClientsNameAndId () {
610 ArrayList
<Object
> data
= new ArrayList
<>();
612 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
613 Statement st
= conn
.createStatement();
614 ResultSet rs
= st
.executeQuery("SELECT id, ClientName from clients;");
616 data
.add(rs
.getInt("id"));
617 data
.add(rs
.getString("ClientName"));
621 } catch (SQLException ex
) {
622 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
628 public static void retrieveConnectionURL () {
629 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
630 connectionURL
= myPrefs
.get("ConnectionURL", "legaldb");