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