Reporting Functionality - added some report
[habeas.git] / src / habeas / Utility.java
1 /*
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.
5 */
6 package habeas;
7
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;
14 import java.sql.*;
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
21 /**
22 *
23 * @author hari
24 */
25 public class Utility {
26
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';",
30
31 "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
32 + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
33 + " WHERE NoticeSent=1 AND NoticeDelivered=0;",
34
35 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
36 + " INNER JOIN clients ON ClientId=clients.id"
37 + " WHERE DraftCreated=1 AND DraftApproved=0;",
38
39 "SELECT ReferenceNumber, Description, EntrustmentDate, "
40 + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
41 + " ClientId=clients.id WHERE ClarificationPending=1;",
42
43 "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
44 + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
45 + "WHERE DraftCreated=0;",
46
47 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM"
48 + " legalnotices INNER JOIN clients ON ClientId=clients.id WHERE "
49 + " DraftApproved=1 AND NoticeSent=0;"
50 } ;
51
52 private static final String[] REPORTS_FILTERED = { "SELECT ReferenceNumber, Description"
53 + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
54 + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT' "
55 + "AND ClientId=?;",
56
57 "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
58 + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
59 + " WHERE NoticeSent=1 AND NoticeDelivered=0 AND ClientId=?;",
60
61 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
62 + " INNER JOIN clients ON ClientId=clients.id"
63 + " WHERE DraftCreated=1 AND DraftApproved=0 AND ClientId=?;",
64
65 "SELECT ReferenceNumber, Description, EntrustmentDate, "
66 + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
67 + " ClientId=clients.id WHERE ClarificationPending=1 AND ClientId=?;",
68
69 "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
70 + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
71 + "WHERE DraftCreated=0 AND ClientId=?;",
72
73 "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM"
74 + " legalnotices INNER JOIN clients ON ClientId=clients.id WHERE "
75 + " DraftApproved=1 AND NoticeSent=0 AND ClientId=?;"
76 } ;
77
78 static void saveStationerySettings (String left_header,
79 String right_header, String signatory) {
80 leftLetterHeader = left_header;
81 rightLetterHeader = right_header;
82 signatoryName = signatory;
83 Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
84 myPrefs.put ("LeftHeader", left_header);
85 myPrefs.put ("RightHeader", right_header);
86 myPrefs.put ("Signatory", signatory);
87 }
88
89 static void retrieveStationerySettings () {
90 Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
91 leftLetterHeader = myPrefs.get("LeftHeader", "Left Header");
92 rightLetterHeader = myPrefs.get ("RightHeader", "Right Header");
93 signatoryName = myPrefs.get ("Signatory", "Signatory Name");
94 }
95
96 static void saveConnectionURL(String text) {
97 connectionURL = text;
98 Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
99 myPrefs.put("ConnectionURL", text);
100 }
101
102 static ArrayList<Object> getClientDetails(int r) {
103 ArrayList<Object> res = new ArrayList <>();
104 try {
105 Connection conn = DriverManager.getConnection(JDBC+connectionURL);
106 PreparedStatement st = conn.prepareStatement("SELECT * FROM clients WHERE id=?;");
107 st.setInt(1, r);
108 ResultSet rs = st.executeQuery();
109 while (rs.next()) {
110 res.add (rs.getString("ClientName"));
111 res.add(rs.getString("ClientAddress"));
112 res.add (rs.getString("ContactPerson"));
113 res.add (rs.getString("MailID"));
114 res.add (rs.getString("ContactNumber"));
115 }
116 conn.close ();
117 return res;
118
119 } catch (SQLException ex) {
120 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
121 return null;
122 }
123
124 }
125
126 static boolean addClient(String client_name, String client_address,
127 String contact_person, String email_id, String phone_number) {
128 if ("".equals(client_name))
129 return false;
130 try {
131 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
132 PreparedStatement st = conn.prepareStatement("INSERT INTO clients (ClientName,"
133 + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
134 st.setString(1, client_name);
135 st.setString(2, client_address);
136 st.setString(3, contact_person);
137 st.setString (4, email_id);
138 st.setString (5, phone_number);
139 st.execute();
140 conn.close();
141 return true;
142
143 } catch (SQLException ex) {
144 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
145 return false;
146 }
147
148
149 }
150
151 static boolean deleteClient(int r) {
152 try {
153 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
154 PreparedStatement st = conn.prepareStatement("DELETE FROM clients WHERE id=?;");
155 PreparedStatement st2 = conn.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
156 st.setInt(1, r);
157 st2.setInt (1, r);
158 st.execute();
159 st2.execute();
160 conn.close();
161 return true;
162 } catch (SQLException ex) {
163 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
164 return false;
165 }
166
167 }
168
169 static boolean updateClient(int r, String client_name, String client_address,
170 String contact_person, String mail_id, String phone_number) {
171 if ("".equals(client_name))
172 return false;
173 try {
174 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
175 PreparedStatement st = conn.prepareStatement("UPDATE clients SET "
176 + "ClientName=?, ClientAddress=?,"
177 + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
178 st.setString(1, client_name);
179 st.setString(2, client_address);
180 st.setString(3, contact_person);
181 st.setString(4, mail_id);
182 st.setString(5, phone_number);
183 st.setInt (6, r);
184 st.execute();
185 conn.close();
186 return true;
187 } catch (SQLException ex) {
188 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
189 return false;
190 }
191
192
193 }
194
195 static boolean addLegalNotice(String reference_number,
196 String description, java.util.Date entrustment_date, DBItem client) {
197 if ("".equals(reference_number) || "".equals(description) ||
198 entrustment_date == null || client == null)
199 return false;
200 try {
201 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
202 PreparedStatement st = conn.prepareStatement("INSERT INTO legalnotices"
203 + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
204 + "VALUES (?, ?, ?, ?);");
205 st.setString(1, reference_number);
206 st.setString(2, description);
207 st.setLong(3, entrustment_date.getTime()/1000);
208 st.setInt (4, client.getKey());
209 st.execute();
210 conn.close();
211 return true;
212
213 } catch (SQLException ex) {
214 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
215 return false;
216 }
217
218 }
219
220 static ArrayList<Object> getNotices() {
221 ArrayList<Object> notices = new ArrayList<>();
222 try {
223 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
224 Statement st = conn.createStatement();
225 ResultSet rs = st.executeQuery("SELECT id, ReferenceNumber, Description "
226 + "FROM legalnotices;");
227 while (rs.next()) {
228 notices.add(rs.getInt("id"));
229 notices.add(rs.getString("ReferenceNumber"));
230 notices.add(rs.getString("Description"));
231 }
232 conn.close();
233 return notices;
234 } catch (SQLException ex) {
235 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
236 return null;
237 }
238
239 }
240 static java.util.Date getValidDate (ResultSet rs, String datefield) throws SQLException {
241 // Since resultset.getDate returns a java.sql.Date, we need a way to get
242 // the date in java.util.Date which is usable in our application. Hence
243 // this helper function. Since we don't want a valid Date object if the
244 // field is null, we are using this if clause
245 if (rs.getDate(datefield) == null)
246 return null;
247 else
248 return (new java.util.Date(rs.getLong(datefield)*1000));
249 }
250
251 static ArrayList<Object> getNoticeDetails(int selid) {
252 ArrayList<Object> notice = new ArrayList<>();
253 try {
254 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
255 PreparedStatement st = conn.prepareStatement("SELECT legalnotices.*, clients.ClientName "
256 + "FROM legalnotices "
257 + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
258 st.setInt(1, selid);
259 ResultSet rs = st.executeQuery();
260 while (rs.next()) {
261 notice.add(rs.getString("ReferenceNumber"));
262 notice.add(rs.getString("Description"));
263 notice.add(getValidDate(rs, "EntrustmentDate"));
264 notice.add (rs.getInt ("ClientId"));
265 notice.add (rs.getBoolean("DraftCreated"));
266 notice.add (rs.getBoolean("DraftApproved"));
267 notice.add (rs.getBoolean("NoticeSent"));
268 notice.add (getValidDate(rs, "SentDate"));
269 notice.add (rs.getString("RPADReference"));
270 notice.add (rs.getBoolean("NoticeDelivered"));
271 notice.add (getValidDate(rs, "DeliveryDate"));
272 notice.add (rs.getString("BillStatus"));
273 notice.add (rs.getInt ("BillAmount"));
274 notice.add (getValidDate (rs, "BillDate"));
275 notice.add (rs.getBoolean ("ClarificationPending"));
276 notice.add (rs.getString("ClarificationRemarks"));
277 notice.add (rs.getString("ClientName"));
278 }
279 return notice;
280 } catch (SQLException ex) {
281 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
282 return null;
283 }
284
285
286 }
287 private static final String JDBC = "jdbc:sqlite:";
288
289 static boolean updateNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) {
290 try {
291 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
292 PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
293 + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
294 st.setBoolean(1, created);
295 st.setBoolean(2, approved);
296 st.setInt (3, selectednotice_id);
297 st.execute();
298 conn.close();
299 return true;
300 } catch (SQLException ex) {
301 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
302 return false;
303 }
304
305 }
306
307 static boolean updateNoticeDescription(int selectednotice_id, String text) {
308 try {
309 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
310 PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
311 + "SET Description=? WHERE id=?;");
312 st.setString(1, text);
313 st.setInt(2, selectednotice_id);
314 st.execute();
315 conn.close();
316 return true;
317 } catch (SQLException ex) {
318 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
319 return false;
320 }
321
322 }
323
324 static boolean updateNoticeDispatchDetails(int selid, boolean notice_sent,
325 java.util.Date sent_date, String rpad_reference,
326 boolean notice_delivered, java.util.Date delivery_date) {
327 try {
328 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
329 PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
330 + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
331 + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
332
333 st.setBoolean(1, notice_sent);
334 st.setDate(2, toSqlDate(sent_date));
335 st.setString(3, rpad_reference);
336 st.setBoolean(4, notice_delivered);
337 st.setDate(5, toSqlDate(delivery_date));
338 st.setInt (6, selid);
339 st.execute();
340 conn.close();
341 return true;
342 } catch (SQLException ex) {
343 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
344 return false;
345 }
346
347 }
348
349 private static Date toSqlDate(java.util.Date date) {
350 if (date == null)
351 return null;
352 Date sqldate = new Date (date.getTime()/1000);
353 return sqldate;
354 }
355
356 static boolean updateNoticeBillDetails(int selid, String bill_status,
357 java.util.Date bill_date, int bill_amount) {
358 try {
359 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
360 PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
361 + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
362 st.setString (1, bill_status);
363 st.setDate(2, toSqlDate(bill_date));
364 st.setInt(3, bill_amount);
365 st.setInt(4, selid);
366 st.execute();
367 conn.close();
368 return true;
369 } catch (SQLException ex) {
370 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
371 return false;
372 }
373
374 }
375
376 static boolean createDatabase(String db_path) {
377 saveConnectionURL(db_path);
378 String tblClients = "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
379 " \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
380 " \"ClientName\" VARCHAR(255) NOT NULL UNIQUE,\n" +
381 " \"ClientAddress\" TEXT,\n" +
382 " \"ContactPerson\" TEXT,\n" +
383 " \"MailID\" VARCHAR(255),\n" +
384 " \"ContactNumber\" VARCHAR(30)\n" +
385 " );";
386 String tblNotices = "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
387 " \"id\" INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
388 " \"ReferenceNumber\" TEXT NOT NULL UNIQUE,\n" +
389 " \"Description\" TEXT NOT NULL,\n" +
390 " \"EntrustmentDate\" INTEGER NOT NULL,\n" +
391 " \"ClientId\" INTEGER,\n" +
392 " \"DraftCreated\" INTEGER DEFAULT 0,\n" +
393 " \"DraftApproved\" INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
394 " \"NoticeSent\" INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
395 " \"SentDate\" INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
396 " \"RPADReference\" TEXT,\n" +
397 " \"NoticeDelivered\" INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
398 " \"DeliveryDate\" INTEGER CHECK(DeliveryDate>=SentDate),\n" +
399 " \"BillStatus\" TEXT DEFAULT 'PENDING',\n" +
400 " \"BillAmount\" INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
401 " \"BillDate\" INTEGER CHECK(BillDate>=DeliveryDate),\n" +
402 " \"ClarificationPending\" INTEGER DEFAULT 0,\n" +
403 " \"ClarificationRemarks\" TEXT\n" +
404 ");";
405
406 try {
407 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
408 Statement st1 = conn.createStatement();
409 st1.execute(tblClients);
410 st1.execute(tblNotices);
411 conn.close();
412 return true;
413 } catch (SQLException ex) {
414 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
415 return false;
416 }
417
418 }
419
420 static boolean updateNoticeClarificationDetails(int selected_id, boolean
421 clarification_pending, String clarification_remarks) {
422 try {
423 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
424 PreparedStatement st = conn.prepareStatement ("UPDATE legalnotices"
425 + " SET ClarificationPending=?, ClarificationRemarks=? "
426 + "WHERE id=?;");
427 st.setBoolean(1, clarification_pending);
428 st.setString(2, clarification_remarks);
429 st.setInt (3, selected_id);
430 st.execute();
431 conn.close ();
432 return true;
433 } catch (SQLException ex) {
434 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
435 return false;
436 }
437
438 }
439
440 static boolean deleteNotice(int r) {
441 try {
442 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
443 PreparedStatement st = conn.prepareStatement("DELETE FROM legalnotices"
444 + " WHERE id=?;");
445 st.setInt(1, r);
446 st.execute();
447 conn.close();
448 return true;
449 } catch (SQLException ex) {
450 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
451 return false;
452 }
453
454 }
455
456 static ArrayList<Object> getRaisedBills (int client_id) {
457 try {
458 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
459 PreparedStatement st = conn.prepareStatement("SELECT ReferenceNumber, "
460 + "Description, BillDate, BillAmount"
461 + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
462 st.setInt (1, client_id);
463
464 ResultSet rs = st.executeQuery ();
465 ArrayList<Object> bills = new ArrayList<>();
466 while (rs.next()) {
467 bills.add (rs.getString("ReferenceNumber"));
468 bills.add (rs.getString("Description"));
469 bills.add (getValidDate(rs, "BillDate"));
470 bills.add (rs.getInt("BillAmount"));
471 }
472 conn.close();
473 return bills;
474
475 } catch (SQLException ex) {
476 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
477 return null;
478 }
479
480 }
481
482 static boolean generateRaisedNoticesBill(String fileName, String clientName,
483 String clientAddress, String contactPerson, ArrayList<Object> bills) {
484 if (bills == null)
485 return false;
486 try {
487 InputStream templ = Utility.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
488 if (templ == null)
489 return false;
490 DateFormat fmt = new SimpleDateFormat("dd MMM yyyy");
491 String templateMain = new String (templ.readAllBytes());
492 // this is for openoffice ODT - replace normal line breaks with the XML equivalent
493 String left = leftLetterHeader.replaceAll("\n", "<text:line-break/>");
494 String right = rightLetterHeader.replaceAll("\n", "<text:line-break/>");
495 String client_address = clientAddress.replaceAll ("\n", "<text:line-break/>");
496 String rows = generateBillRows (bills);
497 System.out.println (rows);
498
499 String strMain = MessageFormat.format(templateMain,
500 left, right,
501 fmt.format(new Date(System.currentTimeMillis())),
502 clientName, client_address, contactPerson, signatoryName, rows);
503
504 FileOutputStream f = new FileOutputStream (fileName);
505 f.write (strMain.getBytes());
506 f.close();
507 return true;
508 } catch (IOException ex) {
509 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
510 return false;
511 }
512 }
513
514 private static String generateBillRows(ArrayList<Object> bills) {
515 try {
516 InputStream tmpl = Utility.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
517 String rowtpl = new String(tmpl.readAllBytes());
518
519 DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");
520
521 StringBuilder bldr = new StringBuilder ();
522 for (int i = 0; i < bills.size(); i += 4) {
523 String row = MessageFormat.format (rowtpl, (String)bills.get(i),
524 (String)bills.get(i+1), fmt.format((java.util.Date)bills.get(i+2)),
525 (int)bills.get(i+3));
526 bldr.append(row);
527 }
528 return (bldr.toString());
529 } catch (IOException ex) {
530 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
531 return null;
532 }
533 }
534
535 static boolean updateNoticeBillStatus(int client_id, String from_status,
536 String to_status) {
537 try {
538 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
539 PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
540 + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
541 st.setString (1, to_status);
542 st.setString (2, from_status);
543 st.setInt (3, client_id);
544 st.execute();
545 conn.close();
546 return true;
547 } catch (SQLException ex) {
548 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
549 return false;
550 }
551
552 }
553
554 static ArrayList<Object> getReportData(int report_num, int clientid) {
555 ArrayList<Object> data = new ArrayList<>();
556 try {
557 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
558
559 // if no client ID is specified i.e. -1 get ll
560 ResultSet rs;
561 if (clientid == -1) {
562 Statement st = conn.createStatement();
563 rs = st.executeQuery(REPORTS_UNFILTERED[report_num]);
564 }
565 else {
566 PreparedStatement st = conn.prepareStatement(REPORTS_FILTERED[report_num]);
567 st.setInt (1, clientid);
568 rs = st.executeQuery();
569 }
570
571 while (rs.next()) {
572 ResultSetMetaData md = rs.getMetaData();
573 SimpleDateFormat fmt = new SimpleDateFormat ("dd MMM yyyy");
574 for (int i = 1; i <= md.getColumnCount(); i ++ ) {
575 // for INTEGER Columns which are date alone, handle separately
576 if (md.getColumnName(i).contains("Date"))
577 data.add (fmt.format(getValidDate(rs, md.getColumnName(i))));
578 else
579 data.add (rs.getObject (i));
580 }
581 }
582 conn.close ();
583 return data;
584 } catch (SQLException ex) {
585 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
586 return null;
587 }
588
589 }
590
591 static boolean saveReportCSV(String filePath, String[] reportCols, ArrayList<Object> reportData) {
592 try {
593 FileWriter f = new FileWriter (filePath);
594 for (int i = 0; i < reportCols.length; i ++) {
595 f.append (escapeQuote(reportCols[i]));
596 f.append (",");
597 }
598 f.append("\n");
599 for (int i = 0; i < reportData.size(); i += reportCols.length) {
600 for (int j = 0; j < reportCols.length; j ++) {
601 f.append (escapeQuote(reportData.get(i+j)));
602 f.append (",");
603 }
604 f.append ("\n");
605 }
606 f.flush();
607 f.close();
608 return true;
609 } catch (IOException ex) {
610 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
611 return false;
612 }
613
614 }
615
616 private static String escapeQuote(Object bareStr) {
617 if (bareStr.getClass() == String.class) {
618 String str = (String)bareStr;
619 String escapedStr = str.replace ("\"", "\"\"");
620 System.out.println(escapedStr);
621 String finalStr = String.format ("\"%s\"", escapedStr);
622 return finalStr;
623 }
624 else
625 return String.format("\"%s\"", bareStr.toString());
626 }
627
628
629 public Utility () {
630
631 }
632 public static String connectionURL ;
633 public static String leftLetterHeader;
634 public static String rightLetterHeader;
635 public static String signatoryName;
636
637 /**
638 *
639 * @return
640 */
641 public static ArrayList<Object> getClientsNameAndId () {
642 ArrayList<Object> data = new ArrayList<>();
643 try {
644 Connection conn = DriverManager.getConnection(JDBC + connectionURL);
645 Statement st = conn.createStatement();
646 ResultSet rs = st.executeQuery("SELECT id, ClientName from clients;");
647 while (rs.next()) {
648 data.add(rs.getInt("id"));
649 data.add(rs.getString("ClientName"));
650 }
651 conn.close();
652 return data;
653 } catch (SQLException ex) {
654 Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
655 return null;
656 }
657
658 }
659
660 public static void retrieveConnectionURL () {
661 Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
662 connectionURL = myPrefs.get("ConnectionURL", "legaldb");
663 }
664
665 }