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