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