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
;
20 import org
.sqlite
.util
.StringUtils
;
26 public class Utility
{
28 private static final String
[] REPORTS_UNFILTERED
= { "SELECT ReferenceNumber, Description"
29 + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
30 + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT';",
32 "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
33 + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
34 + " WHERE NoticeSent=1 AND NoticeDelivered=0;",
36 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
37 + " INNER JOIN clients ON ClientId=clients.id"
38 + " WHERE DraftCreated=1 AND DraftApproved=0;",
40 "SELECT ReferenceNumber, Description, EntrustmentDate, "
41 + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
42 + " ClientId=clients.id WHERE ClarificationPending=1;",
44 "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
45 + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
46 + "WHERE DraftCreated=0;",
48 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM"
49 + " legalnotices INNER JOIN clients ON ClientId=clients.id WHERE "
50 + " DraftApproved=1 AND NoticeSent=0;"
53 private static final String
[] REPORTS_FILTERED
= { "SELECT ReferenceNumber, Description"
54 + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
55 + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT' "
58 "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
59 + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
60 + " WHERE NoticeSent=1 AND NoticeDelivered=0 AND ClientId=?;",
62 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
63 + " INNER JOIN clients ON ClientId=clients.id"
64 + " WHERE DraftCreated=1 AND DraftApproved=0 AND ClientId=?;",
66 "SELECT ReferenceNumber, Description, EntrustmentDate, "
67 + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
68 + " ClientId=clients.id WHERE ClarificationPending=1 AND ClientId=?;",
70 "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
71 + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
72 + "WHERE DraftCreated=0 AND ClientId=?;",
74 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM"
75 + " legalnotices INNER JOIN clients ON ClientId=clients.id WHERE "
76 + " DraftApproved=1 AND NoticeSent=0 AND ClientId=?;"
79 static void saveStationerySettings (String left_header
,
80 String right_header
, String signatory
) {
81 leftLetterHeader
= left_header
;
82 rightLetterHeader
= right_header
;
83 signatoryName
= signatory
;
84 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
85 myPrefs
.put ("LeftHeader", left_header
);
86 myPrefs
.put ("RightHeader", right_header
);
87 myPrefs
.put ("Signatory", signatory
);
90 static void retrieveStationerySettings () {
91 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
92 leftLetterHeader
= myPrefs
.get("LeftHeader", "Left Header");
93 rightLetterHeader
= myPrefs
.get ("RightHeader", "Right Header");
94 signatoryName
= myPrefs
.get ("Signatory", "Signatory Name");
97 static void saveConnectionURL(String text
) {
99 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
100 myPrefs
.put("ConnectionURL", text
);
103 static ArrayList
<Object
> getClientDetails(int r
) {
104 ArrayList
<Object
> res
= new ArrayList
<>();
106 Connection conn
= DriverManager
.getConnection(JDBC
+connectionURL
);
107 PreparedStatement st
= conn
.prepareStatement("SELECT * FROM clients WHERE id=?;");
109 ResultSet rs
= st
.executeQuery();
111 res
.add (rs
.getString("ClientName"));
112 res
.add(rs
.getString("ClientAddress"));
113 res
.add (rs
.getString("ContactPerson"));
114 res
.add (rs
.getString("MailID"));
115 res
.add (rs
.getString("ContactNumber"));
120 } catch (SQLException ex
) {
121 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
127 static boolean addClient(String client_name
, String client_address
,
128 String contact_person
, String email_id
, String phone_number
) {
129 if ("".equals(client_name
))
132 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
133 PreparedStatement st
= conn
.prepareStatement("INSERT INTO clients (ClientName,"
134 + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
135 st
.setString(1, client_name
);
136 st
.setString(2, client_address
);
137 st
.setString(3, contact_person
);
138 st
.setString (4, email_id
);
139 st
.setString (5, phone_number
);
144 } catch (SQLException ex
) {
145 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
152 static boolean deleteClient(int r
) {
154 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
155 PreparedStatement st
= conn
.prepareStatement("DELETE FROM clients WHERE id=?;");
156 PreparedStatement st2
= conn
.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
163 } catch (SQLException ex
) {
164 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
170 static boolean updateClient(int r
, String client_name
, String client_address
,
171 String contact_person
, String mail_id
, String phone_number
) {
172 if ("".equals(client_name
))
175 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
176 PreparedStatement st
= conn
.prepareStatement("UPDATE clients SET "
177 + "ClientName=?, ClientAddress=?,"
178 + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
179 st
.setString(1, client_name
);
180 st
.setString(2, client_address
);
181 st
.setString(3, contact_person
);
182 st
.setString(4, mail_id
);
183 st
.setString(5, phone_number
);
188 } catch (SQLException ex
) {
189 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
196 static boolean addLegalNotice(String reference_number
,
197 String description
, java
.util
.Date entrustment_date
, DBItem client
) {
198 if ("".equals(reference_number
) || "".equals(description
) ||
199 entrustment_date
== null || client
== null)
202 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
203 PreparedStatement st
= conn
.prepareStatement("INSERT INTO legalnotices"
204 + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
205 + "VALUES (?, ?, ?, ?);");
206 st
.setString(1, reference_number
);
207 st
.setString(2, description
);
208 st
.setLong(3, entrustment_date
.getTime()/1000);
209 st
.setInt (4, client
.getKey());
214 } catch (SQLException ex
) {
215 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
221 static ArrayList
<Object
> getNotices() {
222 ArrayList
<Object
> notices
= new ArrayList
<>();
224 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
225 Statement st
= conn
.createStatement();
226 ResultSet rs
= st
.executeQuery("SELECT id, ReferenceNumber, Description "
227 + "FROM legalnotices;");
229 notices
.add(rs
.getInt("id"));
230 notices
.add(rs
.getString("ReferenceNumber"));
231 notices
.add(rs
.getString("Description"));
235 } catch (SQLException ex
) {
236 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
241 static java
.util
.Date
getValidDate (ResultSet rs
, String datefield
) throws SQLException
{
242 // Since resultset.getDate returns a java.sql.Date, we need a way to get
243 // the date in java.util.Date which is usable in our application. Hence
244 // this helper function. Since we don't want a valid Date object if the
245 // field is null, we are using this if clause
246 if (rs
.getDate(datefield
) == null)
249 return (new java
.util
.Date(rs
.getLong(datefield
)*1000));
252 static ArrayList
<Object
> getNoticeDetails(int selid
) {
253 ArrayList
<Object
> notice
= new ArrayList
<>();
255 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
256 PreparedStatement st
= conn
.prepareStatement("SELECT legalnotices.*, clients.ClientName "
257 + "FROM legalnotices "
258 + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
260 ResultSet rs
= st
.executeQuery();
262 notice
.add(rs
.getString("ReferenceNumber"));
263 notice
.add(rs
.getString("Description"));
264 notice
.add(getValidDate(rs
, "EntrustmentDate"));
265 notice
.add (rs
.getInt ("ClientId"));
266 notice
.add (rs
.getBoolean("DraftCreated"));
267 notice
.add (rs
.getBoolean("DraftApproved"));
268 notice
.add (rs
.getBoolean("NoticeSent"));
269 notice
.add (getValidDate(rs
, "SentDate"));
270 notice
.add (rs
.getString("RPADReference"));
271 notice
.add (rs
.getBoolean("NoticeDelivered"));
272 notice
.add (getValidDate(rs
, "DeliveryDate"));
273 notice
.add (rs
.getString("BillStatus"));
274 notice
.add (rs
.getInt ("BillAmount"));
275 notice
.add (getValidDate (rs
, "BillDate"));
276 notice
.add (rs
.getBoolean ("ClarificationPending"));
277 notice
.add (rs
.getString("ClarificationRemarks"));
278 notice
.add (rs
.getString("ClientName"));
281 } catch (SQLException ex
) {
282 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
288 private static final String JDBC
= "jdbc:sqlite:";
290 static boolean updateNoticeDraftStatus(int selectednotice_id
, boolean created
, boolean approved
) {
292 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
293 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
294 + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
295 st
.setBoolean(1, created
);
296 st
.setBoolean(2, approved
);
297 st
.setInt (3, selectednotice_id
);
301 } catch (SQLException ex
) {
302 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
308 static boolean updateNoticeDescription(int selectednotice_id
, String text
) {
310 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
311 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
312 + "SET Description=? WHERE id=?;");
313 st
.setString(1, text
);
314 st
.setInt(2, selectednotice_id
);
318 } catch (SQLException ex
) {
319 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
325 static boolean updateNoticeDispatchDetails(int selid
, boolean notice_sent
,
326 java
.util
.Date sent_date
, String rpad_reference
,
327 boolean notice_delivered
, java
.util
.Date delivery_date
) {
329 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
330 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
331 + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
332 + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
334 st
.setBoolean(1, notice_sent
);
335 st
.setDate(2, toSqlDate(sent_date
));
336 st
.setString(3, rpad_reference
);
337 st
.setBoolean(4, notice_delivered
);
338 st
.setDate(5, toSqlDate(delivery_date
));
339 st
.setInt (6, selid
);
343 } catch (SQLException ex
) {
344 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
350 private static Date
toSqlDate(java
.util
.Date date
) {
353 Date sqldate
= new Date (date
.getTime()/1000);
357 static boolean updateNoticeBillDetails(int selid
, String bill_status
,
358 java
.util
.Date bill_date
, long bill_amount
) {
360 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
361 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
362 + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
363 st
.setString (1, bill_status
);
364 st
.setDate(2, toSqlDate(bill_date
));
365 st
.setLong(3, bill_amount
);
370 } catch (SQLException ex
) {
371 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
377 static boolean createDatabase(String db_path
) {
378 saveConnectionURL(db_path
);
379 String tblClients
= "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
380 " \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
381 " \"ClientName\" VARCHAR(255) NOT NULL UNIQUE,\n" +
382 " \"ClientAddress\" TEXT,\n" +
383 " \"ContactPerson\" TEXT,\n" +
384 " \"MailID\" VARCHAR(255),\n" +
385 " \"ContactNumber\" VARCHAR(30)\n" +
387 String tblNotices
= "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
388 " \"id\" INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
389 " \"ReferenceNumber\" TEXT NOT NULL UNIQUE,\n" +
390 " \"Description\" TEXT NOT NULL,\n" +
391 " \"EntrustmentDate\" INTEGER NOT NULL,\n" +
392 " \"ClientId\" INTEGER,\n" +
393 " \"DraftCreated\" INTEGER DEFAULT 0,\n" +
394 " \"DraftApproved\" INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
395 " \"NoticeSent\" INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
396 " \"SentDate\" INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
397 " \"RPADReference\" TEXT,\n" +
398 " \"NoticeDelivered\" INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
399 " \"DeliveryDate\" INTEGER CHECK(DeliveryDate>=SentDate),\n" +
400 " \"BillStatus\" TEXT DEFAULT 'PENDING',\n" +
401 " \"BillAmount\" INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
402 " \"BillDate\" INTEGER CHECK(BillDate>=DeliveryDate),\n" +
403 " \"ClarificationPending\" INTEGER DEFAULT 0,\n" +
404 " \"ClarificationRemarks\" TEXT\n" +
408 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
409 Statement st1
= conn
.createStatement();
410 st1
.execute(tblClients
);
411 st1
.execute(tblNotices
);
414 } catch (SQLException ex
) {
415 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
421 static boolean updateNoticeClarificationDetails(int selected_id
, boolean
422 clarification_pending
, String clarification_remarks
) {
424 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
425 PreparedStatement st
= conn
.prepareStatement ("UPDATE legalnotices"
426 + " SET ClarificationPending=?, ClarificationRemarks=? "
428 st
.setBoolean(1, clarification_pending
);
429 st
.setString(2, clarification_remarks
);
430 st
.setInt (3, selected_id
);
434 } catch (SQLException ex
) {
435 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
441 static boolean deleteNotice(int r
) {
443 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
444 PreparedStatement st
= conn
.prepareStatement("DELETE FROM legalnotices"
450 } catch (SQLException ex
) {
451 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
457 static ArrayList
<Object
> getRaisedBills (int client_id
) {
459 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
460 PreparedStatement st
= conn
.prepareStatement("SELECT ReferenceNumber, "
461 + "Description, BillDate, BillAmount"
462 + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
463 st
.setInt (1, client_id
);
465 ResultSet rs
= st
.executeQuery ();
466 ArrayList
<Object
> bills
= new ArrayList
<>();
468 bills
.add (rs
.getString("ReferenceNumber"));
469 bills
.add (rs
.getString("Description"));
470 bills
.add (getValidDate(rs
, "BillDate"));
471 bills
.add (rs
.getInt("BillAmount"));
476 } catch (SQLException ex
) {
477 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
483 static boolean generateRaisedNoticesBill(String fileName
, String clientName
,
484 String clientAddress
, String contactPerson
, ArrayList
<Object
> bills
) {
488 InputStream templ
= Utility
.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
491 DateFormat fmt
= new SimpleDateFormat("dd MMM yyyy");
492 String templateMain
= new String (templ
.readAllBytes());
493 // this is for openoffice ODT - replace normal line breaks with the XML equivalent
494 String left
= leftLetterHeader
.replaceAll("\n", "<text:line-break/>");
495 String right
= rightLetterHeader
.replaceAll("\n", "<text:line-break/>");
496 String client_address
= clientAddress
.replaceAll ("\n", "<text:line-break/>");
497 String rows
= generateBillRows (bills
);
498 System
.out
.println (rows
);
500 String strMain
= MessageFormat
.format(templateMain
,
502 fmt
.format(new Date(System
.currentTimeMillis())),
503 clientName
, client_address
, contactPerson
, signatoryName
, rows
);
505 FileOutputStream f
= new FileOutputStream (fileName
);
506 f
.write (strMain
.getBytes());
509 } catch (IOException ex
) {
510 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
515 private static String
generateBillRows(ArrayList
<Object
> bills
) {
517 InputStream tmpl
= Utility
.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
518 String rowtpl
= new String(tmpl
.readAllBytes());
520 DateFormat fmt
= new SimpleDateFormat("dd/MM/yyyy");
522 StringBuilder bldr
= new StringBuilder ();
523 for (int i
= 0; i
< bills
.size(); i
+= 4) {
524 String row
= MessageFormat
.format (rowtpl
, (String
)bills
.get(i
),
525 (String
)bills
.get(i
+1), fmt
.format((java
.util
.Date
)bills
.get(i
+2)),
526 (int)bills
.get(i
+3));
529 return (bldr
.toString());
530 } catch (IOException ex
) {
531 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
536 static boolean updateNoticeBillStatus(int client_id
, String from_status
,
539 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
540 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
541 + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
542 st
.setString (1, to_status
);
543 st
.setString (2, from_status
);
544 st
.setInt (3, client_id
);
548 } catch (SQLException ex
) {
549 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
555 static ArrayList
<Object
> getReportData(int report_num
, int clientid
) {
556 ArrayList
<Object
> data
= new ArrayList
<>();
558 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
560 // if no client ID is specified i.e. -1 get ll
562 if (clientid
== -1) {
563 Statement st
= conn
.createStatement();
564 rs
= st
.executeQuery(REPORTS_UNFILTERED
[report_num
]);
567 PreparedStatement st
= conn
.prepareStatement(REPORTS_FILTERED
[report_num
]);
568 st
.setInt (1, clientid
);
569 rs
= st
.executeQuery();
573 ResultSetMetaData md
= rs
.getMetaData();
574 SimpleDateFormat fmt
= new SimpleDateFormat ("dd MMM yyyy");
575 for (int i
= 1; i
<= md
.getColumnCount(); i
++ ) {
576 // for INTEGER Columns which are date alone, handle separately
577 if (md
.getColumnName(i
).contains("Date"))
578 data
.add (fmt
.format(getValidDate(rs
, md
.getColumnName(i
))));
580 data
.add (rs
.getObject (i
));
585 } catch (SQLException ex
) {
586 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
592 static boolean saveReportCSV(String filePath
, String
[] reportCols
, ArrayList
<Object
> reportData
) {
594 FileWriter f
= new FileWriter (filePath
);
595 for (int i
= 0; i
< reportCols
.length
; i
++) {
596 f
.append (escapeQuoteCSV(reportCols
[i
]));
600 for (int i
= 0; i
< reportData
.size(); i
+= reportCols
.length
) {
601 for (int j
= 0; j
< reportCols
.length
; j
++) {
602 f
.append (escapeQuoteCSV(reportData
.get(i
+j
)));
610 } catch (IOException ex
) {
611 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
617 private static String
escapeQuoteCSV(Object bareStr
) {
620 else if (bareStr
.getClass() == String
.class) {
621 String str
= (String
)bareStr
;
622 String escapedStr
= str
.replace ("\"", "\"\"");
623 System
.out
.println(escapedStr
);
624 String finalStr
= String
.format ("\"%s\"", escapedStr
);
628 return String
.format("\"%s\"", bareStr
.toString());
631 static boolean saveReportHTML(String filepath
, String report_title
,
632 String
[] report_cols
, ArrayList
<Object
> report_data
) {
635 htmlFile
= new FileWriter(filepath
);
636 htmlFile
.append("<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n<title>");
637 htmlFile
.append(htmlEscape(report_title
));
638 htmlFile
.append("</title>\n</head>\n");
639 htmlFile
.append ("<body>\n");
640 htmlFile
.append("<h1>");
641 htmlFile
.append(htmlEscape(report_title
));
642 htmlFile
.append("</h1>\n");
643 htmlFile
.append ("<table>\n");
644 htmlFile
.append ("\t<tr>\n");
645 for (int i
= 0; i
< report_cols
.length
; i
++) {
646 htmlFile
.append ("\t\t<th>");
647 htmlFile
.append (htmlEscape(report_cols
[i
]));
648 htmlFile
.append("</th>\n");
650 htmlFile
.append ("\t</tr>\n");
651 for (int r
= 0; r
< report_data
.size(); r
+= report_cols
.length
) {
652 htmlFile
.append("\t<tr>\n");
653 for (int c
= 0; c
< report_cols
.length
; c
++) {
654 htmlFile
.append("\t\t<td>");
655 htmlFile
.append(htmlEscape(report_data
.get(r
+c
)));
656 htmlFile
.append("</td>\n");
658 htmlFile
.append ("\t</tr>\n");
660 htmlFile
.append("</table>\n");
661 htmlFile
.append("</body>\n");
662 htmlFile
.append("</html>");
666 } catch (IOException ex
) {
667 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
672 private static String
htmlEscape(Object barestr
) {
675 else if (barestr
.getClass() == String
.class) {
676 String barestrs
= (String
)barestr
;
677 String str
= barestrs
.replace("&", "&").replace("<", "<").
678 replace(">", ">").replace("\"", """).replace("\n", "<br>");
682 return barestr
.toString();
689 public static String connectionURL
;
690 public static String leftLetterHeader
;
691 public static String rightLetterHeader
;
692 public static String signatoryName
;
698 public static ArrayList
<Object
> getClientsNameAndId () {
699 ArrayList
<Object
> data
= new ArrayList
<>();
701 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
702 Statement st
= conn
.createStatement();
703 ResultSet rs
= st
.executeQuery("SELECT id, ClientName from clients;");
705 data
.add(rs
.getInt("id"));
706 data
.add(rs
.getString("ClientName"));
710 } catch (SQLException ex
) {
711 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
717 public static void retrieveConnectionURL () {
718 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
719 connectionURL
= myPrefs
.get("ConnectionURL", "legaldb");